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 = 'sp_PARS_RepSOACompleteSummarized')
EXEC ('CREATE PROC dbo.sp_PARS_RepSOACompleteSummarized AS SELECT 1')
GO

ALTER PROCEDURE [dbo].[sp_PARS_RepSOACompleteSummarized]
	@IDNum as varchar(10),
	@AccountNum as varchar(12)

 AS

IF @AccountNum = ''
BEGIN
	-----------------------------ALL------------------------------
	----------------------OUT PATIENT-----------------------------
	IF isNumeric(@IDNum) = 0
	BEGIN
	SELECT Max(RevenueID) AS RevenueID,
		   Max(Type) AS Type,
		   Max(DrCr) AS DrCr,
		   Description, 
		   Sum(Charges) AS Charges,
		   Sum(Credit) AS Credit,
		   Max(PaymentType) AS PaymentType,
		   Sum(Discount) AS Discount
	FROM 
	(Select Distinct
		   isnull(max(R.RevenueID),'') as RevenueID, 
		   isnull(max(LGRP),'') as Type,
		   max(isnull(R.DrCr,'D'))  as DrCr, 
		   L.Department as Description,
		   Sum(Charges) as Charges,
		   Sum(Credit) as Credit,
		   '' as  PaymentType	, 0 as Discount
	from OPBillingDrugsSOA L
		 LEFT OUTER JOIN BUILD_FILE..tbCoRevenueCode R on R.LocationID = L.LocationID
	Where IDNum = @IDNum  
	Group by L.Department

	UNION ALL

	Select Distinct
		   isnull(max(L.RevenueID),'') as RevenueID, 
		   isnull(max(R.LGRP),'')  as Type,
		   max(isnull(R.DrCr,'D')) as DrCr, 
		   Case 
				when max(L.RevenueID) = 'GS' or max(L.RevenueID) = 'SS' then 
				   Case IsNull(L.RevenueID,'')
					  When 'SS' Then ' ' + max(A.ItemName)
					  When 'GS' Then ' ' + max(A.ItemName)
					  Else max(A.ItemName)
				   End
				else max( IsNull( LEFT(SP.Specification, 40), L.Department))
			end as Description,
			Sum(Charges) as Charges,
			Sum(Credit) as Credit,
			'' as PaymentType	, 0 as Discount
	from OPBillingOthersSOA L 
		 LEFT OUTER JOIN Build_File..tbCoRevenueCode R on L.RevenueID = R.RevenueID
		 LEFT OUTER JOIN Build_File..tbCoRevenueCode A on L.ItemID = A.RevenueID and L.RevenueID in ('GS','SS')
		 Left Outer Join Billing..tbBillSpecialProceduresSpecification SP on SP.IDNum = L.IDNum and SP.RevenueID = L.RevenueID and SP.RefNum = L.RefNum and SP.RequestNum = L.RequestNum
	Where L.IDNum = @IDNum and L.DrCr <> 'P'   and  L.RevenueID  not in ('PH','PC','CS','CC', 'TB', 'BB')
	Group by L.RevenueID, A.RevenueID, SP.Specification

	UNION ALL

	Select max( RevenueID ) as RevenueID ,
		Max( Type ) as Type,
		Max( DrCr ) as DrCr,
		Max( Description ) as Description ,
		Sum( Cast(Charges as money)) as Charges ,
		Sum( Cast(Credit as money)) as Credit ,
		Max( PaymentType ) as PaymentType ,
		Sum( Cast(Discount as money)) as Discount
	From (
		Select 
			   isnull(RevenueID,'') as RevenueID, 
			L.ItemID ,
			   isnull(LGRP,'')  as Type,
			   isnull(DrCr,'D') as DrCr, 
			 --  L.Description as Description ,  testing lang
			   case when L.Revenueid = 'MD' then L.Description end as Description ,
			   Cast( Case When L.Lgrp = 'D' Then 0 Else isnull(Charges,0) End as Money) as Charges,
			   Cast( Case When L.Lgrp = 'D' Then 0 Else isnull(Credit,0) End as Money) as Credit,
			   D.Description as PaymentType ,
			   Cast( Case When L.Lgrp = 'D' Then isnull(Credit,0) Else 0 End as Money) as Discount
		from OPBillingOthersSOA L 
			Left Outer Join Build_File..tbCoDoctorsCode D on D.DoctorCode = L.RefNum 
		Where IDNum = @IDNum and DrCr = 'P'   and RevenueID  not in ('PH','PC','CS','CC')
		
		UNION ALL
		
		Select 
			   isnull(RevenueID,'') as RevenueID, 
			L.ItemID ,
			   isnull(LGRP,'')  as Type,
			   isnull(DrCr,'D') as DrCr, 
				L.Description as Description ,
			   Cast( Case When L.Lgrp = 'D' Then 0 Else isnull(Charges,0) End as Money) as Charges,
			   Cast( Case When L.Lgrp = 'D' Then 0 Else isnull(Credit,0) End as Money) as Credit,
			   D.Description as PaymentType ,
			   Cast( Case When L.Lgrp = 'D' Then isnull(Credit,0) Else 0 End as Money) as Discount
		from Billing..vwHistoryOut L 
			Left Outer Join Build_File..tbCoDoctorsCode D on D.DoctorCode = L.RefNum 
		Where IDNum = @IDNum and DrCr = 'P'   and RevenueID  not in ('PH','PC','CS','CC')
	) A
	Group by ItemID

	UNION ALL
	Select Distinct
		   isnull(max(L.RevenueID),'') as RevenueID, 
		   isnull(max(R.LGRP),'')  as Type,
		   max(isnull(R.DrCr,'D')) as DrCr, 
		   Case 
				when max(L.RevenueID) = 'GS' or max(L.RevenueID) = 'SS' then 
				   Case IsNull(L.RevenueID,'')
					  When 'SS' Then ' ' + max(A.ItemName)
					  When 'GS' Then ' ' + max(A.ItemName)
					  Else max(A.ItemName)
				   End
				else max( IsNull( SP.Specification, L.Department))
			end as Description,
			Sum(Charges) as Charges,
			Sum(Credit) as Credit,
			'' as PaymentType   , 0 as Discount
	from Billing..vwHistoryOut L 
		 LEFT OUTER JOIN Build_File..tbCoRevenueCode R on L.RevenueID = R.RevenueID
		 LEFT OUTER JOIN Build_File..tbCoRevenueCode A on L.ItemID = A.RevenueID and L.RevenueID in ('GS','SS')
		 Left Outer Join Billing..tbBillSpecialProceduresSpecification SP on SP.IDNum = L.IDNum and SP.RevenueID = L.RevenueID and SP.RefNum = L.RefNum and SP.RequestNum = L.RequestNum
	Where L.IDNum = @IDNum and L.DrCr <> 'P'   and  L.RevenueID  not in ('PH','PC','CS','CC', 'TB', 'BB')
	Group by L.RevenueID, A.RevenueID, SP.Specification

	UNION ALL

	Select Distinct
		   isnull(max(R.RevenueID),'') as RevenueID, 
		   isnull(max(LGRP),'') as Type,
		   max(isnull(R.DrCr,'D'))  as DrCr, 
		   L.Department as Description,
		   Sum(Charges) as Charges,
		   Sum(Credit) as Credit,
		   '' as  PaymentType	, 0 as Discount
	from vwHistoryOutDrugs L
		 LEFT OUTER JOIN BUILD_FILE..tbCoRevenueCode R on R.LocationID = L.LocationID
	Where IDNum = @IDNum  
	Group by L.Department) Detail
	Group By Description
	Order by Description

	END;

	ELSE


	----------------------IN PATIENT-----------------------------
	BEGIN

	SELECT Max(RevenueID) AS RevenueID,
		   Max(Type) AS Type,
		   Max(DrCr) AS DrCr,
		   Description, 
		   Sum(Charges) AS Charges,
		   Sum(Credit) AS Credit,
		   Max(PaymentType) AS PaymentType,
		   Sum(Discount) AS Discount
	FROM 
	(Select --Distinct
		   isnull(max(R.RevenueID),'') as RevenueID, 
			isnull(max(LGRP),'') as Type,
		   max(isnull(R.DrCr,'D'))  as DrCr, 
		   L.Department as Description,
		   Sum(Charges) as Charges,
		   Sum(Credit) as Credit,
		   '' as  PaymentType	, 0 as Discount
	from BillingDrugsSOA L
		 LEFT OUTER JOIN BUILD_FILE..tbCoRevenueCode R on R.LocationID = L.LocationID
	Where IDNum = @IDNum  
	Group by L.Department

	UNION ALL

	Select --Distinct
		   isnull(max(L.RevenueID),'') as RevenueID, 
		   isnull(max(R.LGRP),'')  as Type,
		   max(isnull(R.DrCr,'D')) as DrCr, 
				  Case 
				when max(L.RevenueID) = 'GS' or max(L.RevenueID) = 'SS' then 
				   Case IsNull(L.RevenueID,'')
					  When 'SS' Then ' ' + max(A.ItemName)
					  When 'GS' Then ' ' + max(A.ItemName)
					  Else max(A.ItemName)
				   End
				else max( IsNull( LEFT(SP.Specification, 40), L.Department))
			end as Description,
			Sum(Charges) as Charges,
			Sum(Credit) as Credit,
		   '' as  PaymentType	, 0 as Discount
	from BillingOthersSOA L 
		  LEFT OUTER JOIN Build_File..tbCoRevenueCode R on L.RevenueID = R.RevenueID
		  LEFT OUTER JOIN Build_File..tbCoRevenueCode A on L.ItemID = A.RevenueID and L.RevenueID in ('GS','SS')
		 Left Outer Join Billing..tbBillSpecialProceduresSpecification SP on SP.IDNum = L.IDNum and SP.RevenueID = L.RevenueID and SP.RefNum = L.RefNum and SP.RequestNum = L.RequestNum
	Where L.IDNum = @IDNum and L.DrCr <> 'P'  and L.RevenueID  not in ('PH','PC','CS','CC', 'TB', 'BB')
	Group by L.RevenueID, A.RevenueID, SP.Specification

	UNION ALL

	Select max( RevenueID ) as RevenueID ,
		Max( Type ) as Type,
		Max( DrCr ) as DrCr,
		Max( Description ) as Description ,
		Sum( Cast(Charges as MOney)) as Charges ,
		Sum( Cast(Credit as money)) as Credit ,
		Max( PaymentType ) as PaymentType ,
		Sum( Cast( Discount as money)) as Discount
	From (
		Select 
			   isnull(RevenueID,'') as RevenueID, 
			L.ItemID ,
			   isnull(LGRP,'')  as Type,
			   isnull(DrCr,'D') as DrCr, 
			 --  L.Description as Description ,
			   case when L.Revenueid = 'MD' then L.Description end as Description ,
			   Cast( Case When L.Lgrp = 'D' Then 0 Else isnull(Charges,0) End as Money) as Charges,
			   Cast( Case When L.Lgrp = 'D' Then 0 Else isnull(Credit,0) End as Money) as Credit,
			   D.Description as PaymentType ,
			   Cast( Case When L.Lgrp = 'D' Then isnull(Credit,0) Else 0 End as Money) as Discount
		from BillingOthersSOA L 
			Left Outer Join Build_File..tbCoDoctorsCode D on D.DoctorCode = L.RefNum 
		Where IDNum = @IDNum and DrCr = 'P'   and RevenueID  not in ('PH','PC','CS','CC')
		
		UNION ALL
		
		Select 
			   isnull(RevenueID,'') as RevenueID, 
			L.ItemID ,
			   isnull(LGRP,'')  as Type,
			   isnull(DrCr,'D') as DrCr, 
				L.Description as Description ,
			   Cast( Case When L.Lgrp = 'D' Then 0 Else isnull(Charges,0) End as Money) as Charges,
			   Cast( Case When L.Lgrp = 'D' Then 0 Else isnull(Credit,0) End as Money) as Credit,
			   D.Description as PaymentType ,
			   Cast( Case When L.Lgrp = 'D' Then isnull(Credit,0) Else 0 End as Money) as Discount
		from Billing..vwHistoryIn L 
			Left Outer Join Build_File..tbCoDoctorsCode D on D.DoctorCode = L.RefNum 
		Where IDNum = @IDNum and DrCr = 'P'   and RevenueID  not in ('PH','PC','CS','CC')
	) A
	Group by ItemID

	UNION ALL

	Select --Distinct
		   isnull(max(L.RevenueID),'') as RevenueID, 
		   isnull(max(R.LGRP),'')  as Type,
		   max(isnull(R.DrCr,'D')) as DrCr, 
				  Case 
				when max(L.RevenueID) = 'GS' or max(L.RevenueID) = 'SS' then 
				   Case IsNull(L.RevenueID,'')
					  When 'SS' Then ' ' + max(A.ItemName)
					  When 'GS' Then ' ' + max(A.ItemName)
					  Else max(A.ItemName)
				   End
				else max( IsNull(LEFT(SP.Specification, 40), L.Department))
			end as Description,
			Sum(Charges) as Charges,
			Sum(Credit) as Credit,
		   '' as  PaymentType	, 0 as Discount
	from Billing..vwHistoryIn L 
		  LEFT OUTER JOIN Build_File..tbCoRevenueCode R on L.RevenueID = R.RevenueID
		  LEFT OUTER JOIN Build_File..tbCoRevenueCode A on L.ItemID = A.RevenueID and L.RevenueID in ('GS','SS')
		 Left Outer Join Billing..tbBillSpecialProceduresSpecification SP on SP.IDNum = L.IDNum and SP.RevenueID = L.RevenueID and SP.RefNum = L.RefNum and SP.RequestNum = L.RequestNum
	Where L.IDNum = @IDNum and L.DrCr <> 'P'  and L.RevenueID  not in ('PH','PC','CS','CC', 'TB', 'BB')
	Group by L.RevenueID, A.RevenueID, SP.Specification

	UNION ALL

	Select --Distinct
		   isnull(max(R.RevenueID),'') as RevenueID, 
			isnull(max(LGRP),'') as Type,
		   max(isnull(R.DrCr,'D'))  as DrCr, 
		   L.Department as Description,
		   Sum(Charges) as Charges,
		   Sum(Credit) as Credit,
		   '' as  PaymentType	, 0 as Discount
	from vwHistoryInDrugs L
		 LEFT OUTER JOIN BUILD_FILE..tbCoRevenueCode R on R.LocationID = L.LocationID
	Where IDNum = @IDNum 
	Group by L.Department) Details
	Group By Description
	Order By Description
	END;
END
Else
BEGIN
	-------------PER ACCOUNT NUMBER FOR MULTIPLE------------------
	----------------------OUT PATIENT-----------------------------
--	select B.AccountNum from billing..tbparsmultiplecompany A
--	left join Build_File..tbCoCompany B on B.AccountNum = A.AccountNum
--	where A.IDNum = '15174B' and B.Company like '%'+@AccountNum+'%' 

	Select @AccountNum = A.AccountNum from Build_File..tbCoCompany A
		Left Join Billing..tbParsMultipleCompany B on B.AccountNum = A.AccountNum
	Where A.Company Like '%'+@AccountNum+'%' and B.IDNum = @IDNum
	-- ~ Added By JonRoss Viewing Per Company

	IF isNumeric(@IDNum) = 0
	BEGIN
	SELECT Max(RevenueID) AS RevenueID,
		   Max(Type) AS Type,
		   Max(DrCr) AS DrCr,
		   Description, 
		   Sum(Charges) AS Charges,
		   Sum(Credit) AS Credit,
		   Max(PaymentType) AS PaymentType,
		   Sum(Discount) AS Discount
	FROM 
	(Select Distinct
		   isnull(max(R.RevenueID),'') as RevenueID, 
		   isnull(max(LGRP),'') as Type,
		   max(isnull(R.DrCr,'D'))  as DrCr, 
		   L.Department as Description,
		   Sum(Charges) as Charges,
		   Sum(Credit) as Credit,
		   '' as  PaymentType	, 0 as Discount
	from OPBillingDrugsSOA L
		 LEFT OUTER JOIN BUILD_FILE..tbCoRevenueCode R on R.LocationID = L.LocationID
	Where IDNum = @IDNum  AND AccountNum = @AccountNum
	Group by L.Department

	UNION ALL

	Select Distinct
		   isnull(max(L.RevenueID),'') as RevenueID, 
		   isnull(max(R.LGRP),'')  as Type,
		   max(isnull(R.DrCr,'D')) as DrCr, 
		   Case 
				when max(L.RevenueID) = 'GS' or max(L.RevenueID) = 'SS' then 
				   Case IsNull(L.RevenueID,'')
					  When 'SS' Then ' ' + max(A.ItemName)
					  When 'GS' Then ' ' + max(A.ItemName)
					  Else max(A.ItemName)
				   End
				else max( IsNull( LEFT(SP.Specification, 40), L.Department))
			end as Description,
			Sum(Charges) as Charges,
			Sum(Credit) as Credit,
			'' as PaymentType	, 0 as Discount
	from OPBillingOthersSOA L 
		 LEFT OUTER JOIN Build_File..tbCoRevenueCode R on L.RevenueID = R.RevenueID
		 LEFT OUTER JOIN Build_File..tbCoRevenueCode A on L.ItemID = A.RevenueID and L.RevenueID in ('GS','SS')
		 Left Outer Join Billing..tbBillSpecialProceduresSpecification SP on SP.IDNum = L.IDNum and SP.RevenueID = L.RevenueID and SP.RefNum = L.RefNum and SP.RequestNum = L.RequestNum
	Where L.IDNum = @IDNum and L.DrCr <> 'P'   and  L.RevenueID  not in ('PH','PC','CS','CC', 'TB', 'BB') and L.AccountNum = @AccountNum
	Group by L.RevenueID, A.RevenueID, SP.Specification

	UNION ALL

	Select max( RevenueID ) as RevenueID ,
		Max( Type ) as Type,
		Max( DrCr ) as DrCr,
		Max( Description ) as Description ,
		Sum( Cast(Charges as money)) as Charges ,
		Sum( Cast(Credit as money)) as Credit ,
		Max( PaymentType ) as PaymentType ,
		Sum( Cast(Discount as money)) as Discount
	From (
		Select 
			   isnull(RevenueID,'') as RevenueID, 
			L.ItemID ,
			   isnull(LGRP,'')  as Type,
			   isnull(DrCr,'D') as DrCr, 
			 --  L.Description as Description ,  testing lang
			   case when L.Revenueid = 'MD' then L.Description end as Description ,
			   Cast( Case When L.Lgrp = 'D' Then 0 Else isnull(Charges,0) End as Money) as Charges,
			   Cast( Case When L.Lgrp = 'D' Then 0 Else isnull(Credit,0) End as Money) as Credit,
			   D.Description as PaymentType ,
			   Cast( Case When L.Lgrp = 'D' Then isnull(Credit,0) Else 0 End as Money) as Discount
		from OPBillingOthersSOA L 
			Left Outer Join Build_File..tbCoDoctorsCode D on D.DoctorCode = L.RefNum 
		Where IDNum = @IDNum and DrCr = 'P'   and RevenueID  not in ('PH','PC','CS','CC') and AccountNum = @AccountNum
		
		UNION ALL
		
		Select 
			   isnull(RevenueID,'') as RevenueID, 
			L.ItemID ,
			   isnull(LGRP,'')  as Type,
			   isnull(DrCr,'D') as DrCr, 
				L.Description as Description ,
			   Cast( Case When L.Lgrp = 'D' Then 0 Else isnull(Charges,0) End as Money) as Charges,
			   Cast( Case When L.Lgrp = 'D' Then 0 Else isnull(Credit,0) End as Money) as Credit,
			   D.Description as PaymentType ,
			   Cast( Case When L.Lgrp = 'D' Then isnull(Credit,0) Else 0 End as Money) as Discount
		from Billing..vwHistoryOut L 
			Left Outer Join Build_File..tbCoDoctorsCode D on D.DoctorCode = L.RefNum 
		Where IDNum = @IDNum and DrCr = 'P'   and RevenueID  not in ('PH','PC','CS','CC') and AccountNum = @AccountNum
	) A
	Group by ItemID

	UNION ALL
	Select Distinct
		   isnull(max(L.RevenueID),'') as RevenueID, 
		   isnull(max(R.LGRP),'')  as Type,
		   max(isnull(R.DrCr,'D')) as DrCr, 
		   Case 
				when max(L.RevenueID) = 'GS' or max(L.RevenueID) = 'SS' then 
				   Case IsNull(L.RevenueID,'')
					  When 'SS' Then ' ' + max(A.ItemName)
					  When 'GS' Then ' ' + max(A.ItemName)
					  Else max(A.ItemName)
				   End
				else max( IsNull( SP.Specification, L.Department))
			end as Description,
			Sum(Charges) as Charges,
			Sum(Credit) as Credit,
			'' as PaymentType   , 0 as Discount
	from Billing..vwHistoryOut L 
		 LEFT OUTER JOIN Build_File..tbCoRevenueCode R on L.RevenueID = R.RevenueID
		 LEFT OUTER JOIN Build_File..tbCoRevenueCode A on L.ItemID = A.RevenueID and L.RevenueID in ('GS','SS')
		 Left Outer Join Billing..tbBillSpecialProceduresSpecification SP on SP.IDNum = L.IDNum and SP.RevenueID = L.RevenueID and SP.RefNum = L.RefNum and SP.RequestNum = L.RequestNum
	Where L.IDNum = @IDNum and L.DrCr <> 'P'   and  L.RevenueID  not in ('PH','PC','CS','CC', 'TB', 'BB') and AccountNum = @AccountNum
	Group by L.RevenueID, A.RevenueID, SP.Specification

	UNION ALL

	Select Distinct
		   isnull(max(R.RevenueID),'') as RevenueID, 
		   isnull(max(LGRP),'') as Type,
		   max(isnull(R.DrCr,'D'))  as DrCr, 
		   L.Department as Description,
		   Sum(Charges) as Charges,
		   Sum(Credit) as Credit,
		   '' as  PaymentType	, 0 as Discount
	from vwHistoryOutDrugs L
		 LEFT OUTER JOIN BUILD_FILE..tbCoRevenueCode R on R.LocationID = L.LocationID
	Where IDNum = @IDNum  and AccountNum = @AccountNum
	Group by L.Department) Detail
	Group By Description
	Order by Description

	END;

	ELSE


	----------------------IN PATIENT-----------------------------
	BEGIN

	SELECT Max(RevenueID) AS RevenueID,
		   Max(Type) AS Type,
		   Max(DrCr) AS DrCr,
		   Description, 
		   Sum(Charges) AS Charges,
		   Sum(Credit) AS Credit,
		   Max(PaymentType) AS PaymentType,
		   Sum(Discount) AS Discount
	FROM 
	(Select --Distinct
		   isnull(max(R.RevenueID),'') as RevenueID, 
			isnull(max(LGRP),'') as Type,
		   max(isnull(R.DrCr,'D'))  as DrCr, 
		   L.Department as Description,
		   Sum(Charges) as Charges,
		   Sum(Credit) as Credit,
		   '' as  PaymentType	, 0 as Discount
	from BillingDrugsSOA L
		 LEFT OUTER JOIN BUILD_FILE..tbCoRevenueCode R on R.LocationID = L.LocationID
	Where IDNum = @IDNum  and AccountNum = @AccountNum
	Group by L.Department

	UNION ALL

	Select --Distinct
		   isnull(max(L.RevenueID),'') as RevenueID, 
		   isnull(max(R.LGRP),'')  as Type,
		   max(isnull(R.DrCr,'D')) as DrCr, 
				  Case 
				when max(L.RevenueID) = 'GS' or max(L.RevenueID) = 'SS' then 
				   Case IsNull(L.RevenueID,'')
					  When 'SS' Then ' ' + max(A.ItemName)
					  When 'GS' Then ' ' + max(A.ItemName)
					  Else max(A.ItemName)
				   End
				else max( IsNull( LEFT(SP.Specification, 40), L.Department))
			end as Description,
			Sum(Charges) as Charges,
			Sum(Credit) as Credit,
		   '' as  PaymentType	, 0 as Discount
	from BillingOthersSOA L 
		  LEFT OUTER JOIN Build_File..tbCoRevenueCode R on L.RevenueID = R.RevenueID
		  LEFT OUTER JOIN Build_File..tbCoRevenueCode A on L.ItemID = A.RevenueID and L.RevenueID in ('GS','SS')
		 Left Outer Join Billing..tbBillSpecialProceduresSpecification SP on SP.IDNum = L.IDNum and SP.RevenueID = L.RevenueID and SP.RefNum = L.RefNum and SP.RequestNum = L.RequestNum
	Where L.IDNum = @IDNum and L.DrCr <> 'P'  and L.RevenueID  not in ('PH','PC','CS','CC', 'TB', 'BB') and AccountNum = @AccountNum
	Group by L.RevenueID, A.RevenueID, SP.Specification

	UNION ALL

	Select max( RevenueID ) as RevenueID ,
		Max( Type ) as Type,
		Max( DrCr ) as DrCr,
		Max( Description ) as Description ,
		Sum( Cast(Charges as MOney)) as Charges ,
		Sum( Cast(Credit as money)) as Credit ,
		Max( PaymentType ) as PaymentType ,
		Sum( Cast( Discount as money)) as Discount
	From (
		Select 
			   isnull(RevenueID,'') as RevenueID, 
			L.ItemID ,
			   isnull(LGRP,'')  as Type,
			   isnull(DrCr,'D') as DrCr, 
			 --  L.Description as Description ,
			   case when L.Revenueid = 'MD' then L.Description end as Description ,
			   Cast( Case When L.Lgrp = 'D' Then 0 Else isnull(Charges,0) End as Money) as Charges,
			   Cast( Case When L.Lgrp = 'D' Then 0 Else isnull(Credit,0) End as Money) as Credit,
			   D.Description as PaymentType ,
			   Cast( Case When L.Lgrp = 'D' Then isnull(Credit,0) Else 0 End as Money) as Discount
		from BillingOthersSOA L 
			Left Outer Join Build_File..tbCoDoctorsCode D on D.DoctorCode = L.RefNum 
		Where IDNum = @IDNum and DrCr = 'P'   and RevenueID  not in ('PH','PC','CS','CC') and AccountNum = @AccountNum
		
		UNION ALL
		
		Select 
			   isnull(RevenueID,'') as RevenueID, 
			L.ItemID ,
			   isnull(LGRP,'')  as Type,
			   isnull(DrCr,'D') as DrCr, 
				L.Description as Description ,
			   Cast( Case When L.Lgrp = 'D' Then 0 Else isnull(Charges,0) End as Money) as Charges,
			   Cast( Case When L.Lgrp = 'D' Then 0 Else isnull(Credit,0) End as Money) as Credit,
			   D.Description as PaymentType ,
			   Cast( Case When L.Lgrp = 'D' Then isnull(Credit,0) Else 0 End as Money) as Discount
		from Billing..vwHistoryIn L 
			Left Outer Join Build_File..tbCoDoctorsCode D on D.DoctorCode = L.RefNum 
		Where IDNum = @IDNum and DrCr = 'P'   and RevenueID  not in ('PH','PC','CS','CC') and AccountNum = @AccountNum
	) A
	Group by ItemID

	UNION ALL

	Select --Distinct
		   isnull(max(L.RevenueID),'') as RevenueID, 
		   isnull(max(R.LGRP),'')  as Type,
		   max(isnull(R.DrCr,'D')) as DrCr, 
				  Case 
				when max(L.RevenueID) = 'GS' or max(L.RevenueID) = 'SS' then 
				   Case IsNull(L.RevenueID,'')
					  When 'SS' Then ' ' + max(A.ItemName)
					  When 'GS' Then ' ' + max(A.ItemName)
					  Else max(A.ItemName)
				   End
				else max( IsNull(LEFT(SP.Specification, 40), L.Department))
			end as Description,
			Sum(Charges) as Charges,
			Sum(Credit) as Credit,
		   '' as  PaymentType	, 0 as Discount
	from Billing..vwHistoryIn L 
		  LEFT OUTER JOIN Build_File..tbCoRevenueCode R on L.RevenueID = R.RevenueID
		  LEFT OUTER JOIN Build_File..tbCoRevenueCode A on L.ItemID = A.RevenueID and L.RevenueID in ('GS','SS')
		 Left Outer Join Billing..tbBillSpecialProceduresSpecification SP on SP.IDNum = L.IDNum and SP.RevenueID = L.RevenueID and SP.RefNum = L.RefNum and SP.RequestNum = L.RequestNum
	Where L.IDNum = @IDNum and L.DrCr <> 'P'  and L.RevenueID  not in ('PH','PC','CS','CC', 'TB', 'BB') and AccountNum = @AccountNum
	Group by L.RevenueID, A.RevenueID, SP.Specification

	UNION ALL

	Select --Distinct
		   isnull(max(R.RevenueID),'') as RevenueID, 
			isnull(max(LGRP),'') as Type,
		   max(isnull(R.DrCr,'D'))  as DrCr, 
		   L.Department as Description,
		   Sum(Charges) as Charges,
		   Sum(Credit) as Credit,
		   '' as  PaymentType	, 0 as Discount
	from vwHistoryInDrugs L
		 LEFT OUTER JOIN BUILD_FILE..tbCoRevenueCode R on R.LocationID = L.LocationID
	Where IDNum = @IDNum and AccountNum = @AccountNum
	Group by L.Department) Details
	Group By Description
	Order By Description
	END;
END





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[sp_BILLING_LoadPatientForConsolidation]

AS

SELECT 
	max(D.HospNum) as [Hosp. No.], 
	max(D.OPDNum) as [OPD. No.], 
	max(D.AdmNum) as [ADM. No.],
	max(isnull(C.LastName,'') + ', ' + isnull(C.FirstName,'') + ' ' + isnull(C.MiddleName,'')) as [Patient Name] ,
	CONVERT( VARCHAR(10), max(D.AdmDate), 101 ) AS [Adm. Date], 
	CONVERT( VARCHAR(10), max(D.AdmDate), 108 ) AS [Adm. Time], 
	CONVERT( VARCHAR(10), max(D.DcrDate), 101 ) AS [Dcr. Date], 
	CONVERT( VARCHAR(10), max(D.DcrDate), 108 ) AS [Dcr. Time]
FROM
	(SELECT 
		A.IDNum as OPDNum,
		A.AdmDate,
		A.DcrDate,
		B.IDNum as AdmNum,
		A.HospNum,
		CASE WHEN isnull(L.Amount, 0) > 0 AND R.DrCr = 'D' THEN Cast(isnull(L.Amount, 0) AS Money) 
		          WHEN isnull(L.Amount, 0) < 0 AND R.DrCr = 'C' THEN Cast(Abs(isnull(L.Amount, 0)) AS money) 
		          WHEN R.LGRP = 'F' AND R.DrCr = 'P' THEN Cast(isnull(L.Amount, 0) AS Money) 
		          WHEN isnull(L.Amount, 0) > 0 AND R.DrCr = 'C' THEN Cast(isnull(L.Amount, 0) AS Money) * - 1 
		          WHEN isnull(L.Amount, 0) < 0 AND R.DrCr = 'D' THEN Cast(Abs(isnull(L.Amount, 0)) AS money) * - 1 
		          WHEN R.LGRP <> 'F' AND R.DrCr = 'P' THEN Cast(isnull(L.Amount, 0) AS Money) * - 1 
		          ELSE 0 
		END AS Amount
	FROM 
		tbBillOpDailyOut L
		INNER JOIN PATIENT_DATA..tbOutPatient A ON A.IDNum = L.IDNum
	  	INNER JOIN PATIENT_DATA..tbPatient B ON A.HOSPNUM = B.HOSPNUM 
		LEFT OUTER JOIN Build_File..tbCoRevenueCode R ON R.RevenueID = L.RevenueID
	WHERE 
		B.DcrDate IS NULL
		AND DAY( B.AdmDate ) - 2 <= DAY( A.AdmDate ) and Year( B.AdmDate ) = Year( A.AdmDate )
		AND ISNULL( B.OPDIDNum , '' ) = ''
		AND LEFT( A.IDNum, 1 ) <> 'P'
	
	UNION ALL
	
	SELECT 
		A.IDNum as OPDNum,
		A.AdmDate,
		A.DcrDate,
		B.IDNum as AdmNum,
		A.HospNum,
	              CASE WHEN isnull(L.Amount, 0) > 0 AND (L.RevenueID = 'PH' OR L.RevenueID = 'CS') THEN Cast(isnull(L.Amount, 0) AS Money) 
	                        WHEN isnull(L.Amount, 0) < 0 AND (L.RevenueID = 'PC' OR L.RevenueID = 'CC') THEN Cast(Abs(isnull(L.Amount, 0)) AS money)  
	                        WHEN isnull(L.Amount, 0) > 0 AND (L.RevenueID = 'PC' OR L.RevenueID = 'CC') THEN Cast(isnull(L.Amount, 0) AS Money) * -1
	                        WHEN isnull(L.Amount, 0) < 0 AND (L.RevenueID = 'PH' OR L.RevenueID = 'CS') THEN Cast(Abs(isnull(L.Amount, 0)) AS money)  * -1
	                        ELSE 0 
	              END AS Amount
	FROM 
		Inventory..tbInvStockCard L
		INNER JOIN PATIENT_DATA..tbOutPatient A ON A.IDNum = L.IDNum
	  	INNER JOIN PATIENT_DATA..tbPatient B ON A.HOSPNUM = B.HOSPNUM 
	WHERE 
		B.DcrDate IS NULL
		AND DAY( B.AdmDate ) - 2 <= DAY( A.AdmDate ) and Year( B.AdmDate ) = Year( A.AdmDate )
		AND ISNULL( B.OPDIDNum , '' ) = ''
		AND LEFT( A.IDNum, 1 ) <> 'P'
	) D

	INNER JOIN PATIENT_DATA..tbMaster C ON D.HOSPNUM = C.HOSPNUM

GROUP BY 
	D.OPDNum
HAVING 
	SUM(D.Amount) > 0 
ORDER BY [Patient Name]


GO

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


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 = 'Cashier_LoadDiscount')
EXEC ('CREATE PROC dbo.Cashier_LoadDiscount AS SELECT 1')
GO

alter Procedure [dbo].[Cashier_LoadDiscount]

as

select * 
from Build_File..tbCoRevenuecode 
where lgrp = 'D' and cashieractive = 'Y' 
and RevenueID not in ('VR','DL')
order by itemname

GO