USE [RADIOLOGY]
GO
/****** Object:  StoredProcedure [dbo].[spRadio_SearchRequestCancel_CM]    Script Date: 12/1/2023 9:35:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spRadio_SearchRequestCancel_CM]
	@SearchCriteria as varchar(15)
AS
--DECLARE
--@SearchCriteria as varchar(15)='OR000080'

--Radiology..spRadio_SearchRequestCancel_CM 'OR200129A'

select DISTINCT
      r.RequestNum [Request #], r.refnum [Slip #], r.idnum [Adm #],
      m.Lastname [Last Name], m.Firstname [First Name], m.Middlename [Middle Name],
      R.Quantity,
      r.ItemID,
      CAST(r.Amount as Money)[Amount],
		CASE
			WHEN r.revenueid NOT IN (SELECT RevenueID FROM RADIOLOGY..tbRadiologyRevenues) THEN l.ItemName
			WHEN ISNULL(r.revenueid, r.RevenueID) = r.RevenueID THEN c.Description
			WHEN ISNULL(r.revenueid, r.RevenueID) IN ('MD', 'RS', 'RD') THEN '(PF) ' + d.lastname + ' ' + d.firstname 
			ELSE l.ItemName
		END AS [Description],
 --     case  isnull(r.revenueid, r.RevenueID) 
	--when r.RevenueID then c.Description
	--when 'MD' then '(PF) ' + d.lastname + ' ' + d.firstname 
	--when 'RS' then '(PF) ' + d.lastname + ' ' + d.firstname 
	--when 'RD' then '(PF) ' + d.lastname + ' ' + d.firstname 
	--ELSE c.Description --l.ItemName
 --   END [Description],
      convert(varchar, r.transdate, 101) [Date],
    r.Hospnum,
    r.RevenueID
    from tbRadiologyRequests_all r
      left join vwpatientmaster m
        on r.hospnum = m.hospnum
      left join tbBillExamListing c
        on r.ItemID = c.ItemID  and C.RevenueID = r.RevenueID
      left join INVENTORY..tbInvMaster l
        on r.ItemID = l.ItemID
      outer apply (select amount from inventory..tbinvstockcard inv where refnum = r.refnum and inv.itemid = r.itemid and amount < 0) inv
      left join BUILD_FILE..tbCoDoctor d
        on r.ItemID = d.doctorid
    where r.refnum like @SearchCriteria --+ '%'
	and (Not R.Refnum like 'C%I' and R.RefNum Like 'OR%')
	 and inv.amount is null
	 --and (r.RevenueID IN (select RevenueID from RADIOLOGY..tbRadiologyRevenues))
	
	
	--and (r.RevenueID IN (select revenueid from Build_File..tbCoRevenueCode where DepartmentCode 
	--										= (select RevenueID from Radiology..tbRadiologyRevenues a where a.RevenueID = r.RevenueID) or revenueid = 'MD'))