USE [RADIOLOGY]
GO
/****** Object:  StoredProcedure [dbo].[spRadio_SearchRequestCancel_CM]    Script Date: 11/23/2023 2:31:55 PM ******/
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)='OR239998882'

--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  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 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 --isnull(r.swfin, 'X') in ('X')
      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 Build_File..tbCoRevenueCode where DepartmentCode 
	--										= (select RevenueID from Radiology..tbRadiologyRevenues a where a.RevenueID = r.RevenueID) or revenueid = 'MD'))

	Union all 

	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], l.ItemName
    [Description],
      convert(varchar, r.transdate, 101) [Date],
    r.Hospnum,
    r.RevenueID
    from INVENTORY..tbInvStockCard 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 --isnull(r.swfin, 'X') in ('X')
      r.refnum like @SearchCriteria + '%'
	and (Not R.Refnum like 'C%I' and R.RefNum Like 'OR%')
	 and inv.amount is null