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 
RR.RequestNum [Request #], RR.RefNum [Slip #], CA.IdNum [Adm #], PM.LastName [Last Name], PM.FirstName[First Name], PM.MiddleName [Middle Name], CA.Quantity, CA.ItemID, CAST(CA.Amount as Money)[Amount], CASE
			WHEN ISNULL(RR.revenueid,RR.RevenueID) IN ('MD', 'RS', 'RD') THEN '(PF) ' + CD.lastname + ' ' + CD.firstname 
			ELSE RR.Description
		END AS [Description], convert(varchar, CA.transdate, 101) [Date], CA.HospNum, CA.RevenueID

 from
BILLING..tbCashAssessment CA
inner join RADIOLOGY..tbRadiologyRequests RR on CA.IdNum = RR.IDNum and CA.ORNumber = RR.RefNum
inner join PATIENT_DATA..tbmaster PM on CA.HospNum = PM.HospNum
inner join BILLING..tbBillExamListing EL on CA.ItemID = EL.ItemID and CA.RevenueID = EL.RevenueID
inner join BUILD_FILE..tbCoDoctor CD on CA.ItemID = CD.DoctorID
where
CA.ORNumber like @SearchCriteria
and RR.RefNum like 'OR%'

	Union all 
	
Select 
SC.RequestNum, SC.RefNum, CA.IdNum, 
PM.LastName, PM.FirstName, PM.MiddleName, CA.Quantity, CA.ItemID, CAST(SC.Amount as Money)[Amount], IM.ItemName, convert(varchar, CA.transdate, 101) [Date], CA.HospNum, 
CA.RevenueID

 from
BILLING..tbCashAssessment CA
inner join INVENTORY..tbInvStockCard SC on CA.ORNumber = SC.RefNum and CA.ItemID = SC.ItemID
inner join PATIENT_DATA..tbmaster PM on CA.HospNum = PM.HospNum
inner join Inventory..tbInvMaster IM on IM.ItemID = SC.ItemID
where
SC.RefNum like @SearchCriteria
	and SC.RefNum Like 'OR%'
 and SC.Amount > 0