USE BILLING
GO


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'spBilling_ReportDailyRoomCharges_Gross')
EXEC ('CREATE PROC dbo.spBilling_ReportDailyRoomCharges_Gross AS SELECT 1')
GO


ALTER PROCEDURE [dbo].[spBilling_ReportDailyRoomCharges_Gross]

	@ReportDate VARCHAR(10) ,
	@RevenueID VARCHAR(3) ,
	@PatientType VARCHAR(1),
	@EndDate varchar(10),
	@ItemId varchar(10)

AS

--SET @ReportDate = '10/22/2012'
--set @EndDate = '10/22/2012'
--set @RevenueID = 'ALL'
--set @PatientType = 'I' 
--set @ItemID = ''

Declare @Date as varchar(22)
set @Date = @EndDate + ' 23:59:59.99'

IF @PatientType = 'I'
BEGIN
	SELECT Z.RefNum, 
		Z.RevenueID ,
		Z.IDNum, 
		ISNULL( B.LastName, '' ) + ', ' + ISNULL( B.FirstName, '' ) as PatientName,
		Case when isnull(Z.DepartmentIncome, '') <> '' then Z.DepartmentIncome
			 when isnull(Z.RevenueID,'') in ('PC','PH') then 'MEDICINES'
			 when isnull(Z.RevenueID,'') in ('CC','CS') then 'SUPPLIES'
			 else
				Case when (Z.RevenueID = 'MD' and Z.Amount < 0) then C.ItemName + '- CREDIT MEMO'  
					 else C.ItemName 
				End
		End as [Description],
		ISNULL( Z.Quantity, 0 ) AS Quantity ,
		CAST( Z.Amount AS Money ) as Amount ,
		CASE WHEN C.Lgrp = 'D' THEN 'B' 
			ELSE 'A'
		END AS ReportGroup, Z.TransDate, Z.UserID, Z.ItemID, 
		B.AccountNum, 		
		Case when isnull(Z.DepartmentIncome, '') <> '' then Z.DepartmentIncome 
			 else 
					Case when Z.RevenueID = 'RA' then isnull(G.Station, Z.Location)
						 else Z.Location 
					End
		End as Location,
		Z.DrCr, Z.MainGroup,
		Charges, Credit
	FROM (
	  SELECT A.RefNum, 
		A.RevenueID ,
		A.IDNum, A.ItemID,
		ISNULL( A.Quantity, 0 ) AS Quantity ,
		CAST( A.Amount AS Money ) as Amount, 
		A.TransDate as TransDate, dbo.fncGetUserInitials(A.UserID) as UserID, 
		Location = B.ItemName,
		DRCR = Case When A.RevenueID in ('AF','RV','BB','AA','TB') then 'OTHER TRANSACTIONS'
					Else
						Case When B.DrCr = 'D' and Credit = 0 then 'CHARGES'
							 When (B.DrCr = 'C' or (A.Credit <> 0 and B.DrCr <> 'P')) then 'DEDUCTIONS/PAYMENTS/CREDIT MEMO'
							 When B.DrCr = 'P' then 'PROFESSIONAL SERVICES'
						End
			   End,
		MainGroup = Case When A.RevenueID in ('AF','RV','BB','AA', 'TB') then 'OTHER TRANSACTIONS'
						 else ''
					End,
		Charges, Credit, isnull(A.PrepaidPackage, '') as PrepaidPackage, 
		D.ItemName as DepartmentIncome, 
		isnull(A.RoomID, '') as RoomID, isnull(A.Description, '') as Description
	  FROM BillingOthersSOA A
				Left Outer Join Build_File..tbCoRevenueCode B
					on A.RevenueID = B.RevenueID
				Left Outer Join Build_File..tbCoAdmPackage C
					on A.PrepaidPackage = C.PackageID
				Left Outer Join Build_File..tbCoRevenueCode D
					on C.DepartmentIncome = D.RevenueID
	  WHERE (A.Transdate BETWEEN @ReportDate and @Date) AND
			(A.RevenueID = @RevenueID OR @RevenueID = 'ALL') AND
		A.RevenueID NOT IN ('PH', 'CS', 'PC', 'CC') AND 
		isnull(A.Adjust,'') <> 'X' AND isnull(A.OPDIDNum,'') = ''
		AND (A.ItemID = @Itemid OR @Itemid = '')
	  UNION ALL
	
	  SELECT A.RefNum, 
		A.RevenueID as RevenueID,
		A.IDNum, A.ItemID,
		ISNULL( A.Quantity, 0 ) AS Quantity ,
		CAST( A.Amount AS Money ) as Amount, 
		A.TransDate as TransDate, dbo.fncGetUserInitials(A.UserID) as UserID,
		Case When B.DrCr = 'C' Then B.ItemName + ' - ' + B.ItemName 
			 else E.Location 
		End as Location,
		DRCR = Case	When (B.DrCr = 'D' and Credit = 0) then 'CHARGES'
					When (B.DrCr = 'C' or Credit <> 0) then 'DEDUCTIONS/PAYMENTS/CREDIT MEMO'
					When B.DRCR = 'P' then 'PROFESSIONAL SERVICES'
			   End,
		MainGroup = Case When A.RevenueID in ('AF','RV','BB','AA','TB') then 'OTHER TRANSACTIONS'
						 else ''
					End,
		Charges, Credit, '' as PrepaidPackage,	
		'' as DepartmentIncome,
		'' as RoomID, isnull(A.Description, '') as Description
	  FROM BillingDrugsSOA A
			Left Outer Join Build_File..tbCoRevenueCode B
					on A.LocationID = B.LocationID
			Left Outer Join Inventory..tbInvLocation E
					on A.LocationID = E.LocationID
	  WHERE ( B.RevenueID = @RevenueID OR @RevenueID = 'ALL' ) AND
		(A.Transdate BETWEEN @ReportDate and @Date) and
		isnull(A.OPDIDNum,'') = ''
		AND (A.ItemID = @Itemid OR @Itemid = '')
	) Z
	Left Outer Join PATIENT_DATA..tbPatient P ON Z.IdNum = P.IDNum
	Left Outer Join PATIENT_DATA..tbMaster B ON P.HospNum = B.HospNum
	Left Outer Join BUILD_FILE..tbCoRevenueCode C ON Z.RevenueID = C.RevenueID
	Left Outer Join Build_File..tbCoRoom F on Z.RoomID = F.RoomID
	Left Outer Join Build_File..tbCoStation G on F.StationID = G.StationID
	where Z.Amount <> 0 
END
ELSE
IF @PatientType = 'O'
BEGIN
	SELECT Z.RefNum, 
		Z.RevenueID ,
		Z.IDNum, 
		Case when isnull(Z.IDNum,'CASH') = 'CASH' then
				isnull(D.PaymentFrom,'')	
			 else
				ISNULL( B.LastName, '' ) + ', ' + ISNULL( B.FirstName, '' ) 
		End as PatientName,
		Case when isnull(Z.DepartmentIncome, '') <> '' then Z.DepartmentIncome
			 when isnull(Z.RevenueID,'') in ('PC','PH') then 'MEDICINES'
			 when isnull(Z.RevenueID,'') in ('CC','CS') then 'SUPPLIES'
			 else
				Case when (Z.RevenueID = 'MD' and Z.Amount < 0) then C.ItemName + '- CREDIT MEMO'  
					 else C.ItemName 
				End
		End as [Description],
		ISNULL( Z.Quantity, 0 ) AS Quantity ,
		CAST( Z.Amount AS Money ) as Amount ,
		CASE WHEN C.Lgrp = 'D' THEN 'B' 
			ELSE 'A'
		END AS ReportGroup, Z.TransDate, Z.UserID, Z.ItemID, 
		B.AccountNum,
		Case when isnull(Z.DepartmentIncome, '') <> '' then Z.DepartmentIncome 
			 else 
					Case when Z.RevenueID = 'RA' then isnull(G.Station, Z.Location)
						 when Z.RevenueID = 'LB' then isnull(I.LabSection, Z.Location)
						 when Z.RevenueID = 'MH' then isnull(Z.Description, Z.Location) 
						 else
							Z.Location 
					End
		End as Location,
	    Z.DrCr, Z.MainGroup,
		Charges, Credit
	FROM (
	  SELECT A.RefNum, 
		A.RevenueID ,
		A.IDNum, A.ItemID,
		ISNULL( A.Quantity, 0 ) AS Quantity ,
		CAST( A.Amount AS Money ) as Amount, 
		A.TransDate as TransDate, dbo.fncGetUserInitials(A.UserID) as UserID,
		Location =  B.ItemName,
		DRCR = Case When A.RevenueID in ('AF','RV','BB','AA','TB') then 'OTHER TRANSACTIONS'
					Else
						Case When B.DrCr = 'D' and Credit = 0 then 'CHARGES'
							 When (B.DrCr = 'C' or (A.Credit <> 0 and B.DrCr <> 'P')) then 'DEDUCTIONS/PAYMENTS/CREDIT MEMO'
							 When B.DrCr = 'P' then 'PROFESSIONAL SERVICES'
						End
			   End,
		MainGroup = Case When A.RevenueID in ('AF','RV','BB','AA', 'TB') then 'OTHER TRANSACTIONS'
						 else ''
					End,
		Charges, Credit, isnull(A.PrepaidPackage, '') as PrepaidPackage, 
		D.ItemName as DepartmentIncome, 
		isnull(A.RoomID, '') as RoomID, isnull(A.Description, '') as Description
	  FROM OPBillingOthersSOA A
			Left Outer Join Build_File..tbCoRevenueCode B
				on A.RevenueID = B.RevenueID
			Left Outer Join Build_File..tbCoAdmPackage C
				on A.PrepaidPackage = C.PackageID
			Left Outer Join Build_File..tbCoRevenueCode D
				on C.DepartmentIncome = D.RevenueID
	  WHERE ( A.RevenueID = @RevenueID OR @RevenueID = 'ALL' ) AND
		A.RevenueID NOT IN ('PH', 'CS', 'PC', 'CC', 'OA') AND
		A.Transdate BETWEEN @ReportDate and @Date 
		and isnull(A.IDNum,'CASH') <> 'CASH' and A.Adjust <> 'X'
		AND (A.ItemID = @Itemid OR @Itemid = '')
	  UNION ALL
	
	  SELECT A.RefNum, 
		A.RevenueID as RevenueID,
		A.IDNum, A.ItemID,
		ISNULL( A.Quantity, 0 ) AS Quantity ,
		CAST( A.Amount AS Money ) as Amount, 
		A.TransDate as TransDate, dbo.fncGetUserInitials(A.UserID) as UserID,
		Case When C.DrCr = 'C' Then C.ItemName + ' - ' + B.ItemName 
			 else 
					Case when isnull(A.TargetLocationID,'') <> '' then 
								D.Location
						 else
								E.Location 
					End
		End as Location,
		DRCR = Case When (C.DrCr = 'D' and Credit = 0) then 'CHARGES'
					When (C.DrCr = 'C' or Credit <> 0) then 'DEDUCTIONS/PAYMENTS/CREDIT MEMO'
					When C.DrCr = 'P' then 'PROFESSIONAL SERVICES'
			   End,
		MainGroup = Case When A.RevenueID in ('AF','RV','BB','AA') then 'OTHER TRANSACTIONS'
						 else ''
					End,
		Charges, Credit, '' as PrepaidPackage, '' as DepartmentIncome,
		'' as RoomID, isnull(A.Description, '') as Description
	  FROM OPBillingDrugsSOA A
			Left Outer Join Build_File..tbCoRevenueCode B
					on A.LocationID = B.LocationID
			Left Outer Join Build_File..tbCoRevenueCode C
					on A.SummaryCode = C.RevenueID
			Left Outer Join Inventory..tbInvLocation D
					on A.TargetLocationID = D.LocationID
			Left Outer Join Inventory..tbInvLocation E
					on A.LocationID = E.LocationID
	  WHERE ( B.RevenueID = @RevenueID OR @RevenueID = 'ALL' ) AND
		A.Transdate BETWEEN @ReportDate and @Date
		AND ISNUMERIC( A.IDNum ) = 0 and isnull(A.IDNum,'CASH') <> 'CASH'
		AND (A.ItemID = @Itemid OR @Itemid = '')
	) Z
	left Outer Join PATIENT_DATA..tbOutPatient P ON Z.IdNum = P.IDNum
	Left Outer Join PATIENT_DATA..tbMaster B ON P.HospNum = B.HospNum
	Left Outer Join BUILD_FILE..tbCoRevenueCode C ON Z.RevenueID = C.RevenueID
	Left Outer Join Billing..tbCashORMaster D on Z.RefNum = D.RefNum
	Left Outer Join Build_File..tbCoRoom F on Z.RoomID = F.RoomID
	Left Outer Join Build_File..tbCoStation G on F.StationID = G.StationID
	Left Outer Join Build_File..tbCoLabExam H on Z.ItemID = H.LabExamID 
	Left Outer Join Build_File..tbCoLabSection I on H.LabSectionID = I.LabSectionID
	where Z.Amount <> 0
END
ELSE
IF @PatientType = 'A'
BEGIN
	SELECT Z.RefNum, 
		Z.RevenueID ,
		Z.IDNum,
		Case when isnull(Z.IDNum,'CASH') = 'CASH' then
				isnull(D.PaymentFrom,'')	
			 else
				ISNULL( B.LastName, '' ) + ', ' + ISNULL( B.FirstName, '' ) 
		End as PatientName,
		Case when isnull(Z.DepartmentIncome, '') <> '' then Z.DepartmentIncome
			 when isnull(Z.RevenueID,'') in ('PC','PH') then 'MEDICINES'
			 when isnull(Z.RevenueID,'') in ('CC','CS') then 'SUPPLIES'
			 else
				Case when (Z.RevenueID = 'MD' and Z.Amount < 0) then C.ItemName + '- CREDIT MEMO'  
					 else C.ItemName 
				End
		End as [Description],
		ISNULL( Z.Quantity, 0 ) AS Quantity ,
		CAST( Z.Amount AS Money ) as Amount ,
		CASE WHEN C.Lgrp = 'D' THEN 'B' 
			ELSE 'A'
		END AS ReportGroup, Z.TransDate, Z.UserID, Z.ItemID, 
		B.Accountnum, 
		Case when isnull(Z.DepartmentIncome, '') <> '' then Z.DepartmentIncome 
			 else 
					Case when Z.RevenueID = 'RA' then isnull(G.Station, Z.Location)
						 when Z.RevenueID = 'LB' then isnull(I.LabSection, Z.Location)
						 when Z.RevenueID = 'MH' then isnull(Z.Description, Z.Location) 
						 else
							Z.Location 
					End
		End as Location,
        Z.DrCr, Z.MainGroup,
		Charges, Credit
	FROM (
	  SELECT A.RefNum, 
		A.RevenueID ,
		A.IDNum, A.ItemID,
		ISNULL( A.Quantity, 0 ) AS Quantity ,
		CAST( A.Amount AS Money ) as Amount, 
		A.TransDate as TransDate, dbo.fncGetUserInitials(A.UserID) as UserID,
		Location = B.ItemName,
		DRCR = Case When A.RevenueID in ('AF','RV','BB','AA','TB') then 'OTHER TRANSACTIONS'
					Else
						Case When B.DrCr = 'D' and Credit = 0 then 'CHARGES'
							 When (B.DrCr = 'C' or (A.Credit <> 0 and B.DrCr <> 'P')) then 'DEDUCTIONS/PAYMENTS/CREDIT MEMO'
							 When B.DrCr = 'P' then 'PROFESSIONAL SERVICES'
						End
			   End,
		MainGroup = Case When A.RevenueID in ('AF','RV','BB','AA', 'TB') then 'OTHER TRANSACTIONS'
						 else ''
					End,
		Charges, Credit, isnull(A.PrepaidPackage, '') as PrepaidPackage, 
		D.ItemName as DepartmentIncome,
		isnull(A.RoomID, '') as RoomID, isnull(A.Description, '') as Description
	  FROM BillingOthersSOA A
				Left Outer Join Build_File..tbCoRevenueCode B
					on A.RevenueID = B.RevenueID
				Left Outer Join Build_File..tbCoAdmPackage C
					on A.PrepaidPackage = C.PackageID
				Left Outer Join Build_File..tbCoRevenueCode D
					on C.DepartmentIncome = D.RevenueID
	  WHERE ( A.RevenueID = @RevenueID OR @RevenueID = 'ALL' ) AND
		A.RevenueID NOT IN ('PH', 'CS', 'PC', 'CC') AND
		A.Transdate BETWEEN @ReportDate and @Date and A.Adjust <> 'X' and
		isnull(A.OPDIDNum,'') = ''
		AND (A.ItemID = @Itemid OR @Itemid = '')
	  UNION ALL

	  SELECT A.RefNum, 
		A.RevenueID ,
		A.IDNum, A.ItemID,
		ISNULL( A.Quantity, 0 ) AS Quantity ,
		CAST( A.Amount AS Money ) as Amount, 
		A.TransDate as TransDate, dbo.fncGetUserInitials(A.UserID) as UserID,
		Location = B.ItemName,
		DRCR = Case When A.RevenueID in ('AF','RV','BB','AA','TB') then 'OTHER TRANSACTIONS'
					Else
						Case When B.DrCr = 'D' and Credit = 0 then 'CHARGES'
							 When (B.DrCr = 'C' or (A.Credit <> 0 and B.DrCr <> 'P')) then 'DEDUCTIONS/PAYMENTS/CREDIT MEMO'
							 When B.DrCr = 'P' then 'PROFESSIONAL SERVICES'
						End
			   End,
		MainGroup = Case When A.RevenueID in ('AF','RV','BB','AA', 'TB') then 'OTHER TRANSACTIONS'
						 else ''
					End,
		Charges, Credit, isnull(A.PrepaidPackage, '') as PrepaidPackage, 
		D.ItemName as DepartmentIncome,
		isnull(A.RoomID,'') as RoomID, isnull(A.Description, '') as Description
	  FROM OPBillingOthersSOA A
				Left Outer Join Build_File..tbCoRevenueCode B
					on A.RevenueID = B.RevenueID
				Left Outer Join Build_File..tbCoAdmPackage C
					on A.PrepaidPackage = C.PackageID
				Left Outer Join Build_File..tbCoRevenueCode D
					on C.DepartmentIncome = D.RevenueID
	  WHERE ( A.RevenueID = @RevenueID OR @RevenueID = 'ALL' ) AND
		A.RevenueID NOT IN ('PH', 'CS', 'PC', 'CC','OA') AND
		A.Transdate BETWEEN @ReportDate and @Date and isnull(A.IDNum,'CASH') <> 'CASH'
		and A.Adjust <> 'X'
		AND (A.ItemID = @Itemid OR @Itemid = '')

	  UNION ALL
	
	  SELECT A.RefNum, 
		A.RevenueID as RevenueID,
		A.IDNum, A.ItemID,
		ISNULL( A.Quantity, 0 ) AS Quantity ,
		CAST( A.Amount AS Money ) as Amount, 
		A.TransDate as TransDate, dbo.fncGetUserInitials(A.UserID) as UserID,
		Case When C.DrCr = 'C' Then C.ItemName + ' - ' + B.ItemName 
			 else 
					Case when isnull(A.TargetLocationID,'') <> '' then 
								D.Location
						 else
								E.Location 
					End
		End as Location,
		DRCR = Case When (C.DrCr = 'D' and Credit = 0) then 'CHARGES'
					When (C.DrCr = 'C' or Credit <> 0) then 'DEDUCTIONS/PAYMENTS/CREDIT MEMO'
					When C.DrCr = 'P' then 'PROFESSIONAL SERVICES'
			   End,
		MainGroup = Case When A.RevenueID in ('AF','RV','BB','AA') then 'OTHER TRANSACTIONS'
						 else ''
					End,
		Charges, Credit, '' as PrepaidPackage, '' as DepartmentIncome,
		'' as RoomID, isnull(A.Description,'') as Description
	  FROM BillingDrugsSOA A
			Left Outer Join Build_File..tbCoRevenueCode B
					on A.LocationID = B.LocationID
			Left Outer Join Build_File..tbCoRevenueCode C
					on A.SummaryCode = C.RevenueID
			Left Outer Join Inventory..tbInvLocation D
					on A.TargetLocationID = D.LocationID
			Left Outer Join Inventory..tbInvLocation E
					on A.LocationID = E.LocationID
	  WHERE ( B.RevenueID = @RevenueID OR @RevenueID = 'ALL' ) AND
		A.Transdate BETWEEN @ReportDate and @Date and isnull(A.IDNum,'CASH') <> 'CASH' and
		isnull(A.OPDIDNum,'') = ''
		AND (A.ItemID = @Itemid OR @Itemid = '')

	  UNION ALL
	
	  SELECT A.RefNum, 
		A.RevenueID as RevenueID,
		A.IDNum, A.ItemID,
		ISNULL( A.Quantity, 0 ) AS Quantity ,
		CAST( A.Amount AS Money ) as Amount, 
		A.TransDate as TransDate, dbo.fncGetUserInitials(A.UserID) as UserID,
		Case When C.DrCr = 'C' Then C.ItemName + ' - ' + B.ItemName 
			 else 
					Case when isnull(A.TargetLocationID,'') <> '' then 
								D.Location
						 else
								E.Location 
					End
		End as Location,
		DRCR = Case When (C.DrCr = 'D' and Credit = 0) then 'CHARGES'
					When (C.DrCr = 'C' or Credit <> 0) then 'DEDUCTIONS/PAYMENTS/CREDIT MEMO'
					When C.DrCr = 'P' then 'PROFESSIONAL SERVICES'
			   End,
		MainGroup = Case When A.RevenueID in ('AF','RV','BB','AA','TB') then 'OTHER TRANSACTIONS'
						 else ''
					End,
		Charges, Credit, '' as PrepaidPackage, '' as DepartmentIncome, 
		'' as RoomID, isnull(A.Description,'') as Description
	  FROM OPBillingDrugsSOA A
			Left Outer Join Build_File..tbCoRevenueCode B
					on A.LocationID = B.LocationID
			Left Outer Join Build_File..tbCoRevenueCode C
					on A.SummaryCode = C.RevenueID
			Left Outer Join Inventory..tbInvLocation D
					on A.TargetLocationID = D.LocationID
			Left Outer Join Inventory..tbInvLocation E
					on A.LocationID = E.LocationID
	  WHERE ( B.RevenueID = @RevenueID OR @RevenueID = 'ALL' ) AND
		      A.Transdate BETWEEN @ReportDate and @Date and isnull(A.IDNum,'CASH') <> 'CASH'
			AND (A.ItemID = @Itemid OR @Itemid = '')	
	) Z
	Left Outer Join PATIENT_DATA..tbPatient P ON Z.IdNum = P.IDNum
	Left Outer Join PATIENT_DATA..tbOutPatient OP ON Z.IdNum = OP.IDNum
	Left Outer Join PATIENT_DATA..tbMaster B ON B.HospNum = IsNull( P.HospNum, OP.HospNum ) 
	Left Outer Join BUILD_FILE..tbCoRevenueCode C ON Z.RevenueID = C.RevenueID
	Left Outer Join Billing..tbCashORMaster D on Z.RefNum = D.RefNum
	Left Outer Join Build_File..tbCoRoom F on Z.RoomID = F.RoomID
	Left Outer Join Build_File..tbCoStation G on F.StationID = G.StationID
	Left Outer Join Build_File..tbCoLabExam H on Z.ItemID = H.LabExamID 
	Left Outer Join Build_File..tbCoLabSection I on H.LabSectionID = I.LabSectionID
	where Z.Amount <> 0
END
ELSE
IF @PatientType = 'C'
BEGIN
	SELECT Z.RefNum, 
		Z.RevenueID ,
		Z.IDNum, 
		isnull(D.PaymentFrom,'') as PatientName,
		Case when isnull(Z.DepartmentIncome, '') <> '' then Z.DepartmentIncome
			 when isnull(Z.RevenueID,'') in ('PC','PH') then 'MEDICINES'
			 when isnull(Z.RevenueID,'') in ('CC','CS') then 'SUPPLIES'
			 else
				Case when (Z.RevenueID = 'MD' and Z.Amount < 0) then C.ItemName + '- CREDIT MEMO'  
					 else C.ItemName 
				End
		End as [Description],
		ISNULL( Z.Quantity, 0 ) AS Quantity ,
		CAST( Z.Amount AS Money ) as Amount ,
		CASE WHEN C.Lgrp = 'D' THEN 'B' 
			ELSE 'A'
		END AS ReportGroup, Z.TransDate, Z.UserID, Z.ItemID, 
		'' as AccountNum, 
		Case when isnull(Z.DepartmentIncome, '') <> '' then Z.DepartmentIncome 
			 else 
					Case when Z.RevenueID = 'RA' then isnull(G.Station, Z.Location)
						 when Z.RevenueID = 'LB' then isnull(I.LabSection, Z.Location)
						 when Z.RevenueID = 'MH' then isnull(Z.Description, Z.Location) 
						 else
							Z.Location 
					End
		End as Location,
	    Z.DrCr, Z.MainGroup,
		Charges, Credit, isnull(D.Particulars,'') as OncoSlip		
	FROM (
	  SELECT A.RefNum, 
		A.RevenueID ,
		A.IDNum, A.ItemID,
		ISNULL( A.Quantity, 0 ) AS Quantity ,
		CAST( A.Amount AS Money ) as Amount, 
		A.TransDate as TransDate, dbo.fncGetUserInitials(A.UserID) as UserID,
		Location = B.ItemName,
		DRCR = Case When A.RevenueID in ('AF','RV','BB','AA','TB') then 'OTHER TRANSACTIONS'
					Else
						Case When B.DrCr = 'D' and Credit = 0 then 'CHARGES'
							 When (B.DrCr = 'C' or (A.Credit <> 0 and B.DrCr <> 'P')) then 'DEDUCTIONS/PAYMENTS/CREDIT MEMO'
							 When B.DrCr = 'P' then 'PROFESSIONAL SERVICES'
						End
			   End,
		MainGroup = Case When A.RevenueID in ('AF','RV','BB','AA', 'TB') then 'OTHER TRANSACTIONS'
						 else ''
					End,
		Charges, Credit, isnull(A.PrepaidPackage, '') as PrepaidPackage, 
		D.ItemName as DepartmentIncome, 
		'' as RoomID, isnull(A.Description, '') as Description
	  FROM CashBillingOthersSOA A
			Left Outer Join Build_File..tbCoRevenueCode B
				on A.RevenueID = B.RevenueID
			Left Outer Join Build_File..tbCoAdmPackage C
				on A.PrepaidPackage = C.PackageID
			Left Outer Join Build_File..tbCoRevenueCode D
				on C.DepartmentIncome = D.RevenueID
	  WHERE ( A.RevenueID = @RevenueID OR @RevenueID = 'ALL' ) AND
		A.RevenueID NOT IN ('PH', 'CS', 'PC', 'CC') AND
		A.Transdate BETWEEN @ReportDate and @Date 
		and (isnull(A.IDNum,'CASH') = 'CASH' or A.IDNum = '') and A.Adjust <> 'X'
		AND (A.ItemID = @Itemid OR @Itemid = '')

	  UNION ALL
	
	  SELECT A.RefNum, 
		A.RevenueID as RevenueID,
		A.IDNum, A.ItemID,
		ISNULL( A.Quantity, 0 ) AS Quantity ,
		CAST( A.Amount AS Money ) as Amount, 
		A.TransDate as TransDate, dbo.fncGetUserInitials(A.UserID) as UserID,
		Case When C.DrCr = 'C' Then C.ItemName + ' - ' + B.ItemName 
			 else 
					Case when isnull(A.TargetLocationID,'') <> '' then 
								D.Location
						 else
								E.Location 
					End
		End as Location,
		DRCR = Case When (C.DrCr = 'D' and Credit = 0) then 'CHARGES'
					When (C.DrCr = 'C' or Credit <> 0) then 'DEDUCTIONS/PAYMENTS/CREDIT MEMO'
					When C.DrCr = 'P' then 'PROFESSIONAL SERVICES'
			   End,
		MainGroup = Case When A.RevenueID in ('AF','RV','BB','AA') then 'OTHER TRANSACTIONS'
						 else ''
					End,
		Charges, Credit, '' as PrepaidPackage, '' as DepartmentIncome,
		'' as RoomID, isnull(A.Description,'') as Description
	  FROM CashBillingDrugsSOA A
			Left Outer Join Build_File..tbCoRevenueCode B
					on A.LocationID = B.LocationID
			Left Outer Join Build_File..tbCoRevenueCode C
					on A.SummaryCode = C.RevenueID
			Left Outer Join Inventory..tbInvLocation D
					on A.TargetLocationID = D.LocationID
			Left Outer Join Inventory..tbInvLocation E
					on A.LocationID = E.LocationID
	  WHERE ( B.RevenueID = @RevenueID OR @RevenueID = 'ALL' ) AND
		A.Transdate BETWEEN @ReportDate and @Date
		and (isnull(A.IDNum,'CASH') = 'CASH' or A.IDNum = '')
		AND (A.ItemID = @Itemid OR @Itemid = '')
	) Z
	INNER Join BUILD_FILE..tbCoRevenueCode C ON Z.RevenueID = C.RevenueID
	Left Outer Join Billing..tbCashORMaster D on Z.RefNum = D.RefNum
	Left Outer Join Build_File..tbCoRoom F on Z.RoomID = F.RoomID
	Left Outer Join Build_File..tbCoStation G on F.StationID = G.StationID
	Left Outer Join Build_File..tbCoLabExam H on Z.ItemID = H.LabExamID 
	Left Outer Join Build_File..tbCoLabSection I on H.LabSectionID = I.LabSectionID
	where Z.Amount <> 0
END