USE [Billing] GO /****** Object: View [dbo].[vwHistoryOutDrugs] Script Date: 04/30/2012 16:00:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [dbo].[vwHistoryOutDrugs] AS SELECT TransDate, M.HospNum, L.IDNum, isnull(L.RefNum, '') AS RefNum, L.Quantity, isnull(TbInvMaster.ItemName, '') + ' ' + isnull(TbInvmaster.ItemDesc, '') AS Description, CASE WHEN isnull(L.Amount, 0) > 0 AND (L.RevenueID = 'PH' OR L.RevenueID = 'CS') THEN Cast(Cast(isnull(L.Amount,0) as Decimal(12,2)) AS Money) WHEN isnull(L.Amount, 0) < 0 AND (L.RevenueID = 'PC' OR L.RevenueID = 'CC') THEN Cast(Abs(Cast(isnull(L.Amount,0) as Decimal(12,2))) AS money) ELSE 0 END AS Charges, CASE WHEN isnull(L.Amount, 0) > 0 AND (L.RevenueID = 'PC' OR L.RevenueID = 'CC') THEN Cast(Cast(isnull(L.Amount,0) as Decimal(12,2)) AS Money) WHEN isnull(L.Amount, 0) < 0 AND (L.RevenueID = 'PH' OR L.RevenueID = 'CS') THEN Cast(Abs(Cast(isnull(L.Amount,0) as Decimal(12,2))) AS money) ELSE 0 END AS Credit, CASE WHEN isnull(L.Amount, 0) > 0 AND (L.RevenueID = 'PH' OR L.RevenueID = 'CS') THEN Cast(Cast(isnull(L.Amount,0) as Decimal(12,2)) AS Money) WHEN isnull(L.Amount, 0) < 0 AND (L.RevenueID = 'PC' OR L.RevenueID = 'CC') THEN Cast(Abs(Cast(isnull(L.Amount,0) as Decimal(12,2))) AS money) WHEN isnull(L.Amount, 0) > 0 AND (L.RevenueID = 'PC' OR L.RevenueID = 'CC') THEN Cast(Cast(isnull(L.Amount,0) as Decimal(12,2)) AS Money) * - 1 WHEN isnull(L.Amount, 0) < 0 AND (L.RevenueID = 'PH' OR L.RevenueID = 'CS') THEN Cast(Abs(Cast(isnull(L.Amount,0) as Decimal(12,2))) AS money) * - 1 ELSE 0 END AS Amount, L.ItemID AS ItemID, L.RevenueID AS RevenueID, P.BillingDate AS BillingDate, L.ItemType AS DrCr, L.locationID, L.UserID, '' as RoomID, Case When L.RevenueID in ('PC', 'CC') then R2.ItemName Else R.ItemName End as Department, L.CutOffDate , isnull(L.Amount,0) as ActualAmount, isnull(tbInvMaster.isSeniorDiscounted, 0) as Senior , isnull(P.AccountNum,'') as AccountNum , ISNULL( P.InvoiceNumber, '' ) as Invoicenumber , P.AdmDate , P.DcrDate, isnull(DosageID,'') as DosageID, isnull(tbInvMaster.MedicareType,'N') as MedicareType, isnull(tbInvMaster.MedicareCategory,'A') as MedicareCategory, R.LGRP, Case When L.TransDate < P.BillingDate then P.BillingDate Else L.TransDate End as PARSDate FROM HISTORY..tbPassInvStock L INNER JOIN Patient_Data..tbOutPatient P ON L.IDnum = P.IDNum LEFT OUTER JOIN Patient_Data..tbMaster M ON M.HospNum = P.HospNum LEFT OUTER JOIN Inventory..tbInvMaster tbInvMaster ON tbInvMaster.ItemID = L.ItemID LEFT OUTER JOIN Build_File..tbCoRevenueCode R on R.LocationID = L.LocationID LEFT OUTER JOIN Build_File..tbCoRevenueCode R2 on R2.RevenueID = L.RevenueID GO ------------------------------------------ USE [Billing] GO /****** Object: View [dbo].[vwHistoryOut] Script Date: 04/30/2012 16:00:54 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [dbo].[vwHistoryOut] AS SELECT L.TransDate, M.HospNum, L.IDNum, isnull(L.RefNum, '') AS RefNum, L.Quantity, CASE WHEN (L.RevenueID = 'PT' or L.RevenueID = 'US' or L.RevenueID = 'AU' or L.RevenueID = 'NU' or L.RevenueID = 'HS' or L.RevenueID = 'LB' or L.RevenueID = 'CT' or L.RevenueID = 'XR' or L.RevenueID = 'PA' or L.RevenueID = 'CT' or L.RevenueID = 'EL' or L.RevenueID = 'SS' or L.RevenueID = 'BA' or L.RevenueID = 'BB' or L.RevenueID = 'GS' or L.RevenueID = 'BD' or L.RevenueID = 'HI' OR L.RevenueID = 'OS') THEN D .Description WHEN R.DrCr = 'P' THEN 'Dr. ' + isnull(Doctor.LastName, '') + ', ' + isnull(Doctor.FirstName, '') + ' ' + isnull(Doctor.MiddleName, '') WHEN R.Rwith = 'Y' then D.Description ELSE R.ItemName END AS Description, CASE WHEN isnull(L.Amount, 0) > 0 AND R.DrCr = 'D' THEN Cast(Cast(isnull(L.Amount,0) as Decimal(12,2)) AS Money) WHEN isnull(L.Amount, 0) < 0 AND R.DrCr = 'C' THEN Cast(Abs(Cast(isnull(L.Amount,0) as Decimal(12,2))) AS money) WHEN R.LGRP = 'F' and R.DrCr = 'P' THEN Cast(Cast(isnull(L.Amount,0) as Decimal(12,2)) as Money) ELSE 0 END AS Charges, CASE WHEN isnull(L.Amount, 0) > 0 AND R.DrCr = 'C' THEN Cast(Cast(isnull(L.Amount,0) as Decimal(12,2)) AS Money) WHEN isnull(L.Amount, 0) < 0 AND R.DrCr = 'D' THEN Cast(Abs(Cast(isnull(L.Amount,0) as Decimal(12,2))) AS money) WHEN R.LGRP <> 'F' and R.DrCr = 'P' THEN Cast(Cast(isnull(L.Amount,0) as Decimal(12,2)) as Money) ELSE 0 END AS Credit, CASE WHEN isnull(L.Amount, 0) > 0 AND R.DrCr = 'D' THEN Cast(Cast(isnull(L.Amount,0) as Decimal(12,2)) AS Money) WHEN isnull(L.Amount, 0) < 0 AND R.DrCr = 'C' THEN Cast(Abs(Cast(isnull(L.Amount,0) as Decimal(12,2))) AS money) WHEN R.LGRP = 'F' and R.DrCr = 'P' THEN Cast(Cast(isnull(L.Amount,0) as Decimal(12,2)) as Money) WHEN isnull(L.Amount, 0) > 0 AND R.DrCr = 'C' THEN Cast(Cast(isnull(L.Amount,0) as Decimal(12,2)) AS Money) * -1 WHEN isnull(L.Amount, 0) < 0 AND R.DrCr = 'D' THEN Cast(Abs(Cast(isnull(L.Amount,0) as Decimal(12,2))) AS money) * -1 WHEN R.LGRP <> 'F' and R.DrCr = 'P' THEN Cast(Cast(isnull(L.Amount,0) as Decimal(12,2)) as Money) * -1 ELSE 0 END AS Amount, L.ItemID AS ItemID, Case When L.RevenueID = 'LB' then isnull(S.RevenueID,'LB') Else L.RevenueID End AS RevenueID, P.BillingDate AS BillingDate, R.DrCr AS DrCr, R.LGRP, L.UserID, '' as RoomID, CASE WHEN L.RevenueID = 'MJ' THEN 'TOTAL DEDUCTION' ELSE R.ItemName END as Department, L.CutOffDate , isnull(L.Amount,0) as ActualAmount, L.RequestDocID, D.Senior, D.SeniorDiscount, D.RateA , (Case When isnull(L.AccountNum,'') = '' then P.AccountNum else L.AccountNum end) as AccountNum , P.InvoiceNumber as InvoiceNumber , IsNull( L.RequestNum, '' ) as RequestNum , P.AdmDate , P.DcrDate, isnull(L.RevenueID,'') as LocationID, isnull(Doctor.RentalWTax,0) as RentalWTax, isnull((CASE WHEN (L.RevenueID = 'PT' OR L.RevenueID = 'US' OR L.RevenueID = 'AU' OR L.RevenueID = 'NU' OR L.RevenueID = 'HS' OR L.RevenueID = 'LB' OR L.RevenueID = 'CT' OR L.RevenueID = 'XR' OR L.RevenueID = 'PA' OR L.RevenueID = 'EL' OR L.RevenueID = 'SS' OR L.RevenueID = 'BA' OR L.RevenueID = 'GS' OR L.RevenueID = 'HS') THEN D.MedicareType WHEN R.Rwith = 'Y' THEN isnull(D.MedicareType,'N') ELSE isnull(R.MedicareType,'N') END), isnull(R.MedicareType,'N')) AS MedicareType, isnull((CASE WHEN (L.RevenueID = 'PT' OR L.RevenueID = 'US' OR L.RevenueID = 'AU' OR L.RevenueID = 'NU' OR L.RevenueID = 'HS' OR L.RevenueID = 'LB' OR L.RevenueID = 'CT' OR L.RevenueID = 'XR' OR L.RevenueID = 'PA' OR L.RevenueID = 'EL' OR L.RevenueID = 'SS' OR L.RevenueID = 'BA' OR L.RevenueID = 'GS' OR L.RevenueID = 'HS') THEN D.MedicareCategory WHEN R.Rwith = 'Y' THEN isnull(D.MedicareCategory,'A') ELSE isnull(R.MedicareCategory,'A') END), isnull(R.MedicareCategory,'A') ) AS MedicareCategory, Case When L.TransDate < P.BillingDate then P.BillingDate Else L.TransDate End as PARSDate FROM History..tbPassDailyOut L INNER JOIN Patient_Data..tbOutPatient P ON L.IDnum = P.IDNum LEFT OUTER JOIN Patient_Data..tbMaster M ON M.HospNum = P.HospNum LEFT OUTER JOIN Billing..tbBillExamListing D ON L.ItemID = D .ItemID AND L.RevenueID = D .RevenueID LEFT OUTER JOIN Build_File..tbCoDoctor Doctor ON Doctor.DoctorID = L.ItemID LEFT OUTER JOIN Build_File..tbCoLabExam Lab on Lab.LabExamID = L.ItemID and L.RevenueID = 'LB' LEFT OUTER JOIN Build_File..tbCoLabSection S on Lab.LabSectionID = S.LabSectionID LEFT OUTER JOIN Build_File..tbCoRevenueCode R ON R.RevenueID = (Case L.RevenueID When 'LB' then isnull(S.RevenueID,'LB') Else L.RevenueID End) GO ---------------------------------------- USE [Billing] GO /****** Object: View [dbo].[OPBillingDrugsSOA] Script Date: 04/30/2012 16:01:22 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [dbo].[OPBillingDrugsSOA] AS SELECT TransDate, M.HospNum, L.IDNum, isnull(L.RefNum, '') AS RefNum, case when isnull(L.Quantity, 0) = 0 then case when isnull(L.Amount,0) > 0 then 1 when isnull(L.Amount,0) = 0 then 0 when isnull(L.Amount,0) < 0 then -1 end Else case when isnull(L.Amount,0) < 0 then ABS(IsNull(L.Quantity,1)) * -1 when isnull(L.Amount,0) = 0 then 0 else ABS(IsNull(L.Quantity,1)) end end as Quantity, Case When isnumeric(L.ItemID) = 1 Then isnull(TbInvMaster.ItemName, '') + ' ' + isnull(TbInvmaster.ItemDesc, '') Else isnull(I.PackageName,'') + ' (Set/Package)' End AS Description, CASE WHEN isnull(L.Amount, 0) > 0 AND (L.RevenueID = 'PH' OR L.RevenueID = 'CS') THEN Cast(Cast(isnull(L.Amount,0) as Decimal(12,2)) as Money) WHEN isnull(L.Amount, 0) < 0 AND (L.RevenueID = 'PC' OR L.RevenueID = 'CC') THEN Abs(Cast(Cast(isnull(L.Amount,0) as Decimal(12,2)) as Money)) ELSE 0 END AS Charges, CASE WHEN isnull(L.Amount, 0) > 0 AND (L.RevenueID = 'PC' OR L.RevenueID = 'CC') THEN Cast(Cast(isnull(L.Amount,0) as Decimal(12,2)) as Money) WHEN isnull(L.Amount, 0) < 0 AND (L.RevenueID = 'PH' OR L.RevenueID = 'CS') THEN Abs(Cast(Cast(isnull(L.Amount,0) as Decimal(12,2)) as Money)) ELSE 0 END AS Credit, CASE WHEN isnull(L.Amount, 0) > 0 AND (L.RevenueID = 'PH' OR L.RevenueID = 'CS') THEN Cast(Cast(isnull(L.Amount,0) as Decimal(12,2)) as Money) WHEN isnull(L.Amount, 0) < 0 AND (L.RevenueID = 'PC' OR L.RevenueID = 'CC') THEN Abs(Cast(Cast(isnull(L.Amount,0) as Decimal(12,2)) as Money)) WHEN isnull(L.Amount, 0) > 0 AND (L.RevenueID = 'PC' OR L.RevenueID = 'CC') THEN Cast(Cast(isnull(L.Amount,0) as Decimal(12,2)) as Money) * -1 WHEN isnull(L.Amount, 0) < 0 AND (L.RevenueID = 'PH' OR L.RevenueID = 'CS') THEN Abs(Cast(Cast(isnull(L.Amount,0) as Decimal(12,2)) as Money)) * -1 ELSE 0 END AS Amount, ISNULL( L.ItemID, '' ) AS ItemID, L.RevenueID AS RevenueID, P.BillingDate AS BillingDate, L.ItemType AS DrCr, Case When L.locationID = '186' Then '20' Else L.LocationID End as LocationID , isnull(tbInvMaster.MedicareType,'N') as MedicareType, isnull(tbInvMaster.MedicareCategory,'A') as MedicareCategory, isnull(R.GroupRevenueID,'') as GroupRevenueID, L.UserID, 'OPD' as RoomID, R.ItemName as Department, L.CutOffDate, isnull(L.Amount,0) as ActualAmount , isnull(tbInvMaster.isSeniorDiscounted, 0) as Senior , ISNULL( L.SummaryCode, '' ) as SummaryCode , P.AccountNum as AccountNum , IsNull( P.InvoiceNumber, '' ) as InvoiceNumber , P.AdmDate , P.DcrDate, isnull(DosageID,'') as DosageID, tbInvMaster.NM_MedicareType, tbInvMaster.ItemClassificationID, ISNULL(tbInvMaster.GroupCode, '') AS GroupType, isnull(TargetLocationID,'') as TargetLocationID, isnull(L.QuantityRec,'') QuantityRec, isnull(L.RequestByID,'') as RequestByID, isnull(X.isSenior,0) as SeniorLocation, R.LGRP FROM INVENTORY..tbInvStockCard L INNER JOIN Patient_Data..tbOutPatient P ON L.IDnum = P.IDNum LEFT OUTER JOIN Patient_Data..tbMaster M ON M.HospNum = P.HospNum LEFT OUTER JOIN Inventory..tbInvMaster tbInvMaster ON tbInvMaster.ItemID = L.ItemID LEFT OUTER JOIN Build_File..tbCoRevenueCode R on R.LocationID = L.LocationID and not isnull(L.LocationID,'') = '' LEFT OUTER JOIN Build_File..tbCoRevenueCode R2 on R2.RevenueID = L.RevenueID LEFT OUTER JOIN Inventory..tbInvPackage I on I.PackageID = L.ItemID Left Outer Join Inventory..tbInvent X on L.ItemID = X.ItemID and L.LocationID = X.LocationID --Where L.PackageID is null -- as per Maya 2007.06.28 to include Package Items GO ----------------------------- USE [Medicare] GO /****** Object: StoredProcedure [dbo].[MedicRep_GetListOfDeniedClaims] Script Date: 04/30/2012 15:34:27 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /****** Object: Stored Procedure dbo.MedicRep_GetListOfDeniedClaims Script Date: 07/01/2000 1:19:55 AM ******/ ALTER PROCEDURE [dbo].[MedicRep_GetListOfDeniedClaims] As Begin Tran Select [Patient Name],[Confinement], DcrDate, MemberNumber,[AdmNum],[Hospital Claim], Payments,Balance, [RA], [Drug], [XLO], [OR],PostDate from ( Select IsNull(B.LastName,'') + ', ' + IsNull(B.FirstName,'') + ' ' + IsNull(B.MiddleName,'') [Patient Name], B.MemberNumber as MemberNumber, B.DcrDate as DcrDate, [Confinement] = Convert(varchar(10),B.AdmDate,101) + ' - ' + Convert(varchar(10),B.DcrDate,101), IsNull(B.IdNum,'') [AdmNum], [Hospital Claim] = Case IsNull(B.IdNum,'') When '' Then 0 Else IsNull( ( Select Cast( Sum( ( IsNull( MedRoomBoard, 0 ) - IsNull( RefundRoomBoard, 0) ) + ( IsNull( MedDrug, 0 ) - IsNull( RefundDrug, 0) ) + ( IsNull( MedOthers, 0 ) - IsNull( RefundOthers, 0) ) + ( IsNull( MedOR, 0 ) - IsNull( RefundOR, 0) ) ) as Decimal( 12, 2 ) ) from Medicare..tbMedActual Where IdNum = B.IdNum ), 0 ) End, [Payments] = Case IsNull(B.IdNum,'') When '' Then 0 Else IsNull( ( Select Sum(Cast( IsNull(Amount,0) as Decimal( 12, 2 ) )) from Medicare..tbMedPayment Where IdNum = B.IdNum ), 0 ) End, ( Case IsNull(B.IdNum,'') When '' Then 0 Else IsNull( ( Select Cast( Sum( ( IsNull( MedRoomBoard, 0 ) - IsNull( RefundRoomBoard, 0) ) + ( IsNull( MedDrug, 0 ) - IsNull( RefundDrug, 0) ) + ( IsNull( MedOthers, 0 ) - IsNull( RefundOthers, 0) ) + ( IsNull( MedOR, 0 ) - IsNull( RefundOR, 0) ) ) as Decimal( 12, 2 ) ) from Medicare..tbMedActual Where IdNum = B.IdNum ), 0 ) End ) - ( Case IsNull(B.IdNum,'') When '' Then 0 Else IsNull( ( Select Cast( Sum( IsNull(Amount,0) + IsNull(Tax,0) ) as Decimal( 12, 2 ) ) from Medicare..tbMedPayment Where IdNum = B.IdNum ), 0 ) End ) [Balance], [RA] = isnull((Select sum(RoomboardAmt) from Medicare..tbMedPayment where IdNum = B.idnum),0), [Drug] = isnull((Select sum(isnull(DrugAmt,0)) from Medicare..tbMedPayment where IdNum = B.idnum),0), [XLO] = isnull((Select sum(isnull(OthersAmt,0)) from Medicare..tbMedPayment where IdNum = B.idnum),0), [OR] = isnull((Select sum(isnull(ORAmt,0)) from Medicare..tbMedPayment where IdNum = B.idnum),0), Convert(Varchar(10), B.PostDate, 101) as PostDate from Medicare..tbMedPatient B -- Left Outer Join Medicare..tbMedPayment B on B.idnum = A.idnum Where ( ( Case IsNull(B.IdNum,'') When '' Then 0 Else IsNull( ( Select Cast( Sum( ( IsNull( MedRoomBoard, 0 ) - IsNull( RefundRoomBoard, 0) ) + ( IsNull( MedDrug, 0 ) - IsNull( RefundDrug, 0) ) + ( IsNull( MedOthers, 0 ) - IsNull( RefundOthers, 0) ) + ( IsNull( MedOR, 0 ) - IsNull( RefundOR, 0) ) ) as Decimal( 12, 2 ) ) from Medicare..tbMedActual Where IdNum = B.IdNum ), 0 ) End ) - ( Case IsNull(B.IdNum,'') When '' Then 0 Else IsNull( ( Select Cast( Sum( IsNull(Amount,0) + IsNull(Tax,0) ) as Decimal( 12, 2 ) ) from Medicare..tbMedPayment Where IdNum = B.IdNum ), 0 ) End ) ) > 0 and B.PaymentDenied = 0 --UNDERPAID UNION ALL --UNPAID CLAIMS Select IsNull(E.LastName,'') + ', ' + IsNull(E.FirstName,'') + ' ' + IsNull(E.MiddleName,'') [Patient Name], E.MemberNumber as MemberNumber, E.DcrDate as DcrDate, [Confinement] = Convert(varchar(10),E.AdmDate,101) + ' - ' + Convert(varchar(10),E.DcrDate,101), IsNull(A.IdNum,'') [AdmNum], [Hospital Claim] = Case IsNull(A.IdNum,'') When '' Then 0 Else IsNull( ( Select Cast( Sum( ( IsNull( MedRoomBoard, 0 ) - IsNull( RefundRoomBoard, 0) ) + ( IsNull( MedDrug, 0 ) - IsNull( RefundDrug, 0) ) + ( IsNull( MedOthers, 0 ) - IsNull( RefundOthers, 0) ) + ( IsNull( MedOR, 0 ) - IsNull( RefundOR, 0) ) ) as Decimal( 12, 2 ) ) from Medicare..tbMedActual Where IdNum = A.IdNum ), 0 ) End, [Payments] = Case IsNull(A.IdNum,'') When '' Then 0 Else IsNull( ( Select Sum(Cast( IsNull(Amount,0) as Decimal( 12, 2 ) )) from Medicare..tbMedPayment Where IdNum = A.IdNum ), 0 ) End, ( Case IsNull(A.IdNum,'') When '' Then 0 Else IsNull( ( Select Cast( Sum( ( IsNull( MedRoomBoard, 0 ) - IsNull( RefundRoomBoard, 0) ) + ( IsNull( MedDrug, 0 ) - IsNull( RefundDrug, 0) ) + ( IsNull( MedOthers, 0 ) - IsNull( RefundOthers, 0) ) + ( IsNull( MedOR, 0 ) - IsNull( RefundOR, 0) ) ) as Decimal( 12, 2 ) ) from Medicare..tbMedActual Where IdNum = A.IdNum ), 0 ) End ) - ( Case IsNull(A.IdNum,'') When '' Then 0 Else IsNull( ( Select Cast( Sum( IsNull(Amount,0) + IsNull(Tax,0) ) as Decimal( 12, 2 ) ) from Medicare..tbMedPayment Where IdNum = A.IdNum ), 0 ) End ) [Balance], [RA] = isnull((Select sum(isnull(RoomboardAmt,0)) from Medicare..tbMedPayment where IdNum = A.idnum),0), [Drug] = isnull((Select sum(isnull(DrugAmt,0)) from Medicare..tbMedPayment where IdNum = A.idnum),0), [XLO] = isnull((Select sum(isnull(OthersAmt,0)) from Medicare..tbMedPayment where IdNum = A.idnum),0), [OR] = isnull((Select sum(isnull(ORAmt,0)) from Medicare..tbMedPayment where IdNum = A.idnum),0), Convert(Varchar(10), E.PostDate, 101) as PostDate from Medicare..tbMedActual A left outer join Medicare..tbMedPatient E on A.idnum = e.idnum Where IsNull( ( Select Cast( Sum( IsNull( MedRoomBoard, 0 ) + IsNull( MedDrug, 0 ) + IsNull( MedOthers, 0 ) + IsNull( MedOR, 0 ) ) as money ) from Medicare..tbMedActual Where IdNum = A.IdNum ),0) > 0 and IsNull( ( Select Cast( Sum( IsNull(Amount,0) + IsNull(Tax,0) ) as money ) from tbMedPayment Where IdNum = A.IdNum ), 0 ) = 0 and E.PaymentDenied = 0 --UNPAID INPATIENT )a where [Payments] > 0 and datediff(y, year(DcrDate),year(getdate())) > 1 Order by DcrDate desc If @@error<>0 Begin goto abort_save End Commit Tran Return 0 abort_save: Rollback Tran Return -10001 ---------------------- GO /****** Object: StoredProcedure [dbo].[Medic_UpdatePaymentDenied] Script Date: 04/30/2012 15:36:54 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO /****** Object: Stored Procedure dbo.Medic_UpdatePaymentDenied Script Date: 07/01/2000 1:19:23 AM ******/ ALTER PROCEDURE [dbo].[Medic_UpdatePaymentDenied] --'534358','0', '', 'jem' @strIdNum as varchar(10), @bitDenied as bit, @strReason as varchar(250), @strUserid as varchar(10) = '' AS declare @PRoomBoardAmt as money declare @PDrugAmt as money declare @POthersAmt as money declare @PORAmt as money declare @BRoomBoardAmt as money declare @BDrugAmt as money declare @BOthersAmt as money declare @BORAmt as money declare @PAmt as money Begin Tran /***** NOT TO DELETE THE PAYMENT BUT TO TRANSFER IT TO PARS*****/ --if exists(Select * from Medicare..tbMedPayment where IdNum = @strIdNum) -- begin --FOR DENIED UNDERPAID CLAIMS set @PRoomBoardAmt = isnull((Select sum(cast(RoomBoardAmt as money)) from Medicare..tbMedPayment where IdNum = @strIdNum),0) set @PDrugAmt = isnull((Select sum(cast(DrugAmt as money)) from Medicare..tbMedPayment where IdNum = @strIdNum),0) set @POthersAmt = isnull((Select sum(cast(OthersAmt as money)) from Medicare..tbMedPayment where IdNum = @strIdNum),0) set @PORAmt = isnull((Select sum(cast(ORAmt as money)) from Medicare..tbMedPayment where IdNum = @strIdNum),0) set @PAmt = isnull((Select sum(cast(Amount as money)) from Medicare..tbMedPayment where IdNum = @strIdNum),0) --end --else -- begin -- --FOR DENIED UNPAID CLAIMS set @BRoomBoardAmt = isnull((select sum(cast(Amount as money)) from Billing..tbBillDailyBill where IdNum = @strIdNum and ItemID ='SS1' and RevenueID = 'SS' and Amount > 0),0) set @BDrugAmt = isnull((select sum(cast(Amount as money)) from Billing..tbBillDailyBill where IdNum = @strIdNum and ItemID ='SS2' and RevenueID = 'SS' and Amount > 0),0) set @BOthersAmt = isnull((select sum(cast(Amount as money)) from Billing..tbBillDailyBill where IdNum = @strIdNum and ItemID ='SS3' and RevenueID = 'SS' and Amount > 0),0) set @BORAmt = isnull((select sum(cast(Amount as money)) from Billing..tbBillDailyBill where IdNum = @strIdNum and ItemID ='SS4' and RevenueID = 'SS' and Amount > 0),0) --end Update Medicare..tbMedPatient Set PaymentDenied = @bitDenied Where IdNum = @stridNum; If @@error <> 0 Begin goto Abort_Save; End; If @bitDenied = 1 Begin If Exists( Select * from Medicare..tbMedReasonsForPaymentDenial Where IdNum = @strIdNum ) Begin Update Medicare..tbMedReasonsForPaymentDenial Set Reason = @strReason Where IdNum = @strIdNum; End Else Begin Insert into Medicare..tbMedReasonsForPaymentDenial( IdNum, Reason ) Values( @strIdNum, @strReason ); End; If isnumeric(@strIDNum) = 1 Begin Insert into Billing..tbBillDailyBill( IdNum, TransDate, RevenueID, DrCr, ItemID, Amount, UserID ) Values( @strIDNum, GetDate(), 'SS', 'C', 'SS1', (@BRoomBoardAmt - @PRoomBoardAmt) * - 1, @strUserID ); Insert into Billing..tbBillDailyBill( IdNum, TransDate, RevenueID, DrCr, ItemID, Amount, UserID ) Values( @strIDNum, GetDate(), 'SS', 'C', 'SS2', (@BDrugAmt - @PDrugAmt) * - 1, @strUserID ); Insert into Billing..tbBillDailyBill( IdNum, TransDate, RevenueID, DrCr, ItemID, Amount, UserID ) Values( @strIDNum, GetDate(), 'SS', 'C', 'SS3', (@BOthersAmt - @POthersAmt) * - 1, @strUserID ); Insert into Billing..tbBillDailyBill( IdNum, TransDate, RevenueID, DrCr, ItemID, Amount, UserID ) Values( @strIDNum, GetDate(), 'SS', 'C', 'SS4', (@BORAmt - @PORAmt) * - 1, @strUserID ); End Else Begin Insert into Billing..tbBillOPDailyOut( IdNum, TransDate, RevenueID, DrCr, ItemID, Amount, UserID ) Values( @strIDNum, GetDate(), 'SS', 'C', 'SS2', (@BDrugAmt - @PDrugAmt) * - 1, @strUserID ); Insert into Billing..tbBillOPDailyOut( IdNum, TransDate, RevenueID, DrCr, ItemID, Amount, UserID ) Values( @strIDNum, GetDate(), 'SS', 'C', 'SS3', (@BOthersAmt - @POthersAmt ) * - 1, @strUserID ); Insert into Billing..tbBillOPDailyOut( IdNum, TransDate, RevenueID, DrCr, ItemID, Amount, UserID ) Values( @strIDNum, GetDate(), 'SS', 'C', 'SS4', (@BORAmt - @PORAmt ) * - 1, @strUserID ); End --FOR MEDICARE COMPUTATION update Medicare..tbMedActual set PatRoomBoard = (Select cast(ActualRoomboard as money)from Medicare..tbMedActual where IdNum = @strIDNum), PatDrug = (Select cast(ActualDrug as money) from Medicare..tbMedActual where IdNum = @strIDNum), PatOthers = (Select cast(ActualOthers as money) from Medicare..tbMedActual where IdNum = @strIDNum), PatOR = (Select cast(ActualOR as money) from Medicare..tbMedActual where IdNum = @strIDNum), MedRoomBoard = 0, MedDrug = 0, MedOthers =0 , MedOR = 0 where IdNum = @strIDNum --FOR TBMEDSOA Insert into tbMedSOA select E.RevenueID, Case when E.Position = '2' then cast((cast(PHICAmount as float)/ cast(@BDrugAmt as float))*(cast(@BDrugAmt as float) - cast(@PDrugAmt as float)) as float) * -1 when E.Position = '3' then cast((cast(PHICAmount as float)/ cast(@BOthersAmt as float) )*(cast(@BOthersAmt as float) - cast(@POthersAmt as float)) as float) * -1 when E.Position = '4' then cast((cast(PHICAmount as float)/ cast(@BORAmt as float))*(cast(@BORAmt as float) - cast(@PORAmt as float)) as float) * -1 end [PHICAmount], E.IDNum, E.ItemID, E.TransactionDate, E.LocationID from ( Select distinct B.RevenueID,B.IDNum,B.ItemID,B.TransactionDate, B.LocationID, isnull(B.PHICAmount,0) [PHICAmount], G.Position from Medicare..tbMedSOA B left outer join Medicare..Medic_DumpCompensable G on G.Code = B.ItemID and G.IDNum = B.idnum where B.IDNum = @strIDNum ) E --to consider it not as denied but underpaid but many years ago so they will no longer pay it -- Delete Medicare..tbMedPayment Where IdNum = @strIdNum; End Else Begin Delete Medicare..tbMedReasonsForPaymentDenial Where IdNum = @strIdNum; --FOR MEDICARE COMPUTATION update Medicare..tbMedActual set PatRoomBoard = (Select cast(ActualRoomboard as money)from Medicare..tbMedActual where IdNum = @strIDNum) - @BRoomBoardAmt, PatDrug = (Select cast(ActualDrug as money) from Medicare..tbMedActual where IdNum = @strIDNum) - @BDrugAmt, PatOthers = (Select cast(ActualOthers as money) from Medicare..tbMedActual where IdNum = @strIDNum) - @BOthersAmt, PatOR = (Select cast(ActualOR as money) from Medicare..tbMedActual where IdNum = @strIDNum) - @BORAmt, MedRoomBoard = @BRoomBoardAmt, MedDrug = @BDrugAmt, MedOthers =@BOthersAmt , MedOR = @BORAmt where IdNum = @strIDNum Delete from Billing..tbBillDailyBill where RevenueID = 'SS' and ItemID in ('SS1','SS2','SS3','SS4') and Amount <= 0 and IdNum = @strIdNum Delete from Medicare..tbMedSOA where IDNum = @strIdNum and PHICAmount <= 0 End; If @@error <> 0 Begin goto Abort_Save; End; Commit Tran; Return 0; Abort_Save: Rollback Tran; Return -10001 ----------------------------------------------- GO /****** Object: StoredProcedure [dbo].[Medic_UpdateINFODenied] Script Date: 04/22/2012 19:27:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[Medic_UpdateINFODenied] @IDNum as varchar(8), @Denied as varchar(10), @Reason as varchar(100), @Appeal as Varchar(10), @Approved as varchar(10), @DeniedMain as varchar(10), @ReasonMain as varchar(100) AS if Exists(select * from Medicare..tbMedPatient where IDNum = @IDNum) BEGIN Update Medicare..tbMedPatient set ReasonDenied = @Reason, DateDenied = @Denied, DateDeniedMain = @DeniedMain, ReasonDeniedMain = @ReasonMain, DateAppealMain = @Appeal, DateApproved = @Approved where IDNum = @IDNum END -- If @@error<>0 -- Begin -- Goto Abort_saving; -- End; -- End; -- Abort_saving: --RollBack Tran --Return -10001 ------------------------ GO /****** Object: StoredProcedure [dbo].[Medic_SavePHICSOA] Script Date: 04/25/2012 14:46:46 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Medic_SavePHICSOA] --'559394' ,'jem' @strIdNum as varchar(15), @strUserID as varchar(10) as --set @strIDNum = '4613' --set @strUserID = '214' Delete tbMedSOA where IDNum = @StrIdnum If (Select IsNull(PackageID,'1') From Medicare..tbMedActual where IDNum = @strIdNum) = '1' Begin Insert into tbMedSOA select E.RevenueID, Case when E.Position = '2' then case when cast(TotalAmount as float) < cast(MedDrug as float) then cast((cast(Amount as float)/ cast(TotalAmount as float))*(cast(TotalAmount as float)) as float) else cast((cast(Amount as float)/ cast(TotalAmount as float))*(cast(MedDrug as float)) as float) end when E.Position = '4' then case when cast(TotalAmount as float) < cast(MedOR as float) then cast((cast(Amount as float)/ cast(TotalAmount as float))*(cast(TotalAmount as float)) as float) else cast((cast(Amount as float)/ cast(TotalAmount as float))*(cast(MedOR as float)) as float) end else case when cast(TotalAmount as float) < cast(MedOthers as float) then cast((cast(Amount as float)/ cast(TotalAmount as float))*(cast(TotalAmount as float)) as float) else cast((cast(Amount as float)/ cast(TotalAmount as float))*(cast(MedOthers as float)) as float) end End [Phic Amount], E.IDNum, E.Code as ItemID, getdate() as TransactionDate, E.LocationID from ( Select distinct IsNull(A.Code,'') [Code], [Description] = Case Len(IsNull(A.Code,'')) When 0 Then (Select IsNull(ItemName,'') from Build_File..tbCoRevenueCode Where RevenueID = A.RevenueID ) Else IsNull(A.Description,'') End, IsNull(A.Quantity,0) [Quantity], IsNull(A.Amount,0) [Amount], IsNull(A.RevenueID,'') [RevenueID], IsNull(A.Position,'') [Position], IsNull(A.IdNum,'') [IdNum], IsNull(A.UserID,'') [UserID], case when IsNull(B.MedDrug,0) > D.[TOTALAmount] then D.[TOTALAmount] else IsNull(B.MedDrug,0) end [MedDrug], IsNull(B.MedOthers,0) [MedOthers], IsNull(B.MedOR, 0) [MedOR], D.[TOTALAmount], isnull(A.LocationID,'') as LocationID from Medic_DumpCompensable A Left Outer Join tbMedActual B On A.IdNum = B.IdNum Left Outer Join tbMedClaim C On A.IdNum = C.IdNum Left Outer Join ( select distinct sum(IsNull(A.Amount,0)) [TOTALAmount], IsNull(A.Position,'') [Position], A.IdNum from Medic_DumpCompensable A Where A.IdNum = @strIdNum and (A.Position = '2' or A.Position = '3' or A.Position = '4') and A.Quantity > 0 and A.isCompensable = '0' group by A.Position, A.IDNum )D on A.IDnum = D.Idnum AND D.Position = A.Position Where A.IdNum = @strIdNum and (A.Position = '2' or A.Position = '3' or A.Position = '4') and A.Quantity > 0 and A.isCompensable = '0' ) E Order by E.Position, E.RevenueID End Else Begin Insert into tbMedSOA select E.RevenueID, Case When (E.Position = '2' and ActualDrug > 0) then Case When Cast(ActualDrug As Float) < Cast(MedDrug As Float) Then Cast((Cast(Amount As Float)/ cast(ActualDrug As Float))*(cast(ActualDrug as Float)) As Float) Else Cast((Cast(Amount As Float)/ Cast(ActualDrug As Float))*(Cast(MedDrug as Float)) As Float) End when (E.Position = '4' and ActualOR > 0) then Case When Cast(ActualOR As Float) < Cast(MedOR As Float) Then Cast((Cast(Amount As Float)/ cast(ActualOR As Float))*(cast(ActualOR as Float)) As Float) Else Cast((Cast(Amount As Float)/ Cast(ActualOR As Float))*(Cast(MedOR as Float)) As Float) End when (E.Position = '3' and ActualOthers > 0) then Case When Cast(ActualOthers As Float) < Cast(MedOthers As Float) Then Cast((Cast(Amount As Float)/ cast(ActualOthers As Float))*(cast(ActualOthers as Float)) As Float) Else Cast((Cast(Amount As Float)/ Cast(ActualOthers As Float))*(Cast(MedOthers as Float)) As Float) End else 0 End [Phic Amount], E.IDNum, E.Code as ItemID, getdate() as TransactionDate, E.LocationID from ( Select distinct IsNull(A.Code,'') [Code], [Description] = Case Len(IsNull(A.Code,'')) When 0 Then (Select IsNull(ItemName,'') from Build_File..tbCoRevenueCode Where RevenueID = A.RevenueID ) Else IsNull(A.Description,'') End, IsNull(A.Quantity,0) [Quantity], ISNULL(A.Amount,0) [Amount], IsNull(A.RevenueID,'') [RevenueID], case when (A.Position = 'N') then Case When IsNull(A.Position, 'N') = 'N' And A.RevenueID in ('PH', 'CS') then Case When (Select IsNull(ClassificationType,'') from Inventory..tbInvMaster M Left Outer Join Inventory..tbInvClassification N On M.ItemClassificationID = N.ItemClassificationID Where M.ItemID = A.Code) = 'D' Then '2' else '3' end when A.RevenueID not in ('PH', 'CS', 'AA', 'BB', 'TB') and A.Code not in (Select ItemID from Billing..tbBillExamListing B where NM_MedicareType In ('1', '2', '4') and B.RevenueID = A.RevenueID) Then '3' else (Select Case When IsNull(NM_MedicareType,'') = '' Then '3' Else NM_MedicareType End from Billing..tbBillExamListing Where RevenueID = A.RevenueID And ItemID = A.Code) End else IsNull(A.Position,'') end [Position], IsNull(A.IdNum,'') [IdNum], IsNull(A.UserID,'') [UserID], IsNull(B.MedRoomBoard,0) [MedRoomBoard], IsNull(B.MedDrug,0) [MedDrug], IsNull(B.MedOthers,0) [MedOthers], IsNull(B.MedOR, 0) [MedOR], IsNull(B.ActualRoomBoard,0) ActualRoomBoard, IsNull(B.ActualDrug,0) ActualDrug, IsNull(B.ActualOthers,0) ActualOthers, IsNull(B.ActualOR, 0) ActualOR, isnull(A.LocationID,'') as LocationID, D.[TOTALAmount] From Medic_DumpTable A Left Outer Join tbMedActual B On A.IdNum = B.IdNum Left Outer Join tbMedClaim C On A.IdNum = C.IdNum left outer join ( select distinct sum(IsNull(A.Amount,0)) [TOTALAmount], IsNull(A.Position,'') [Position], A.IdNum from Medic_DumpTable A Where A.IdNum = @strIdNum and ( A.Position = '2' or A.Position = '3' or A.Position = '4' or A.Position = 'N') and A.Quantity > 0 and A.Amount > 0 group by A.Position, A.IDNum )D on A.IDnum = D.Idnum AND D.Position = A.Position Where A.IdNum = @strIdNum And A.UserID = @strUserID And ( A.Position = '2' or A.Position = '3' or A.Position = '4' or A.Position = 'N') and A.Quantity > 0 And A.RevenueID IN (Select RevenueID from Build_file..tbCoRevenueCode where LGRP not in ('D', 'S', 'L', 'F')) ) E Order by E.Position, E.RevenueID End --------------------- alter table medicare..medic_dumptable add LocationID varchar(5) null ----------------- GO /****** Object: StoredProcedure [dbo].[Medic_AutoComputeCharges] Script Date: 04/25/2012 14:40:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /****** Object: Stored Procedure dbo.Medic_AutoComputeCharges Script Date: 07/01/2000 1:19:49 AM ******/ ALTER PROCEDURE [dbo].[Medic_AutoComputeCharges] @IdNum as varchar(10), @strUserID as varchar(10) AS /******/ Declare Patient_Charges CURSOR FOR Select Amount, RevenueID, ItemID, Quantity, Description, TransDate, MedicareType, MedicareCategory, LocationID from Billing..BillingDrugsSOA Where IDNum = @IDNum --and (TransDate < = BillingDate or BillingDate is null) UNION ALL Select Amount, RevenueID, ItemID, Quantity, Description, TransDate, MedicareType, MedicareCategory, LocationID from Billing..vwHistoryInDrugs Where IDNum = @IDNum --and (TransDate < = BillingDate or BillingDate is null) UNION ALL Select Amount, RevenueID, ItemID, Quantity, Description, TransDate, MedicareType, MedicareCategory, LocationID from Billing..BillingOthersSOA Where IDNum = @IDNum --and (TransDate < = BillingDate or BillingDate is null) and DrCr <> 'P' and RevenueID not in ('PH','PC','CS','CC') UNION ALL Select Amount, RevenueID, ItemID, Quantity, Description, TransDate, MedicareType, MedicareCategory, LocationID from Billing..vwHistoryIn Where IDNum = @IDNum --and (TransDate < = BillingDate or BillingDate is null) and DrCr <> 'P' and RevenueID not in ('PH','PC','CS','CC') Order by TransDate; /* SELECT (CASE WHEN tbinvstockcard.amount is NULL then 0 WHEN tbinvstockcard.revenueid = 'PC' or tbinvstockcard.revenueid = 'CC' then amount * -1 ELSE TBINVSTOCKCARD.AMOUNT END) as AMOUNT, ltrim(rtrim(isnull(TBcoRevenueCode.ServiceId,''))) as RevenueID, isnull(TBINVSTOCKCARD.itemid,'') as ItemId, tbinvstockcard.quantity, ltrim(rtrim(isnull(tbinvmaster.itemname,'') + ' ' + isnull(tbinvmaster.itemdesc,''))) as ItemDesc, TBINVSTOCKCARD.TransDate FROM Inventory..TBINVSTOCKCARD tbinvstockcard LEFT OUTER JOIN Patient_Data..tbPatient tbPatient ON TBINVSTOCKCARD.IdNum = tbPatient.IdNum LEFT OUTER JOIN Inventory..TBINVMASTER tbInvMaster ON tbinvstockcard.itemid = tbinvmaster.itemid LEFT OUTER JOIN Billing..tbbillInvCode tbcoRevenueCode on tbinvstockcard.revenueid = tbcorevenuecode.revenueid and tbinvstockcard.locationID = tbcorevenuecode.locationID WHERE TBINVSTOCKCARD.IDNUM= @IDNUM Union All SELECT (CASE WHEN tbbilldailybill.drcr = NULL then 0 WHEN tbbilldailybill.drcr = 'D' then tbbilldailybill.amount WHEN tbbilldailybill.drcr = 'C' then (tbbilldailybill.amount * -1) WHEN TBbilldailybill.drcr = 'P' and TBbilldailybill.revenueid = 'MD' then TBbilldailybill.amount WHEN tbbilldailybill.drcr = 'P' and tbbilldailybill.revenueid <> 'MD' then (tbbilldailybill.amount * -1) END) as AMOUNT, isnull(tbbilldailybill.RevenueId,'') as RevenueID, isnull(tbbilldailybill.itemid,'') as ItemId, tbbilldailybill.quantity, (CASE WHEN rtrim(ltrim(isnull(tbbilldailybill.ItemID,''))) = '' then tbcorevenuecode.itemname WHEN TbBillDailyBill.drcr = 'P' then Tbcorevenuecode.ItemName + ' - ' + 'DR. ' + isnull(TbcoDoctor.LastName,'') + ', ' + isnull(TbcoDoctor.Firstname,'') +' ' + isnull(TbcoDoctor.MiddleName,'') WHEN tbBillDailyBill.RevenueID = 'CP' then 'COMPANY PAYMENT - ' + tbCoCompany.Company Else tbBillExamListing.Description End) as ItemDesc, Tbbilldailybill.TransDate FROM billing..tbBillDailyBill tbBillDailyBill LEFT OUTER JOIN Patient_Data..tbPatient tbPatient ON tbBillDailyBill.IdNum = tbPatient.IdNum LEFT OUTER JOIN Billing..tbBillExamListing tbBillExamListing ON tbBillDailyBill.RevenueId = tbBillExamListing.RevenueId and tbBillDailyBill.ItemId = tbBillExamListing.ItemId LEFT OUTER JOIN Billing..tbCoCompany tbCoCompany ON tbBillDailyBill.ItemID = tbCoCompany.CompanyId LEFT OUTER JOIN Billing..tbCoDoctor tbcoDoctor ON tbBillDailyBill.ItemID = tbCoDoctor.DoctorID LEFT OUTER JOIN Billing..tbCoRevenueCode tbCoRevenueCode ON tbBillDailyBill.RevenueID = tbCoRevenueCode.RevenueID WHERE TBBILLDAILYBILL.IDNUM= @IDNUM Order by Transdate, RevenueId, ItemDesc;*/ /******/ declare @strCode as varchar(10) declare @strDescription as varchar(100) declare @fltQuantity as float declare @fltAmount as float declare @strRevenueID as varchar(2) declare @strIdNum as varchar(10) declare @datTransDate as datetime declare @Position as varchar(2) declare @Category as varchar(2) declare @LocationID as varchar(5) declare @strPosition as varchar(1) declare @fltTempQty as float declare @intRecordCount as int declare @intCtr as int Set @strIdNum = @IdNum Open Patient_Charges Fetch Next from Patient_Charges Into @fltAmount, @strRevenueID, @strCode, @fltQuantity, @strDescription, @datTransDate, @Position, @Category, @LocationID Set @intRecordCount = @@Cursor_Rows Set @intCtr = 1 Exec Medicare.dbo.Medic_InsertTempTable @strCode, @strDescription, @fltQuantity, @fltAmount, @strRevenueID, @strIdNum, @strUserID, @Position, @Category, @LocationID Begin Tran While @intCtr < @intRecordCount Begin Fetch Next from Patient_Charges Into @fltAmount, @strRevenueID, @strCode, @fltQuantity, @strDescription, @datTransDate, @Position, @Category, @LocationID Exec Medic_InsertTempTable @strCode, @strDescription, @fltQuantity, @fltAmount, @strRevenueID, @strIdNum, @strUserID, @Position, @Category, @LocationID Set @intCtr = @intCtr + 1 End Commit Tran CLOSE Patient_Charges DEALLOCATE Patient_Charges Return 0 Abort_Insert: Rollback Tran Return -100001 ----------------------- GO /****** Object: StoredProcedure [dbo].[Medic_InsertTempTable] Script Date: 04/25/2012 14:42:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO /****** Object: Stored Procedure dbo.Medic_InsertTempTable Script Date: 07/01/2000 1:19:50 AM ******/ ALTER PROCEDURE [dbo].[Medic_InsertTempTable] @strCode as varchar(10), @strDescription as varchar(100), @fltQuantity as float, @fltAmount as float, @strRevenueID as varchar(2), @strIdNum as varchar(10), @strUserId as varchar(10), @Position as varchar(2), @Category as varchar(2), @LocationID as varchar(5) AS Declare @strRWith as varchar(1); declare @strPosition as varchar(1); declare @strCategory as varchar(1); declare @fltTempQty as float; -- Set @strCode = IsNull(@strCode,''); If @strRevenueID = 'RA' Begin Set @strCode = null; End; Else Begin Set @strCode = IsNull(@strCode,''); End; Set @strDescription = IsNull(@strDescription,''); Set @fltQuantity = IsNull(@fltQuantity,0); Set @fltAmount = IsNull(@fltAmount,0); Set @strRevenueID = IsNull(@strRevenueID,''); Set @strIdNum = IsNull(@strIdNum,''); Set @strUserId = IsNull(@strUserID,''); Begin Tran set @fltTempQty = @fltQuantity; If @fltQuantity <= 0 Begin If not (@strRevenueID = 'PC' or @strRevenueID = 'PH' or @strRevenueID = 'CC' or @strRevenueID = 'CS') Begin set @fltTempQty = 1; End End; Set @strRwith = IsNull((Select Max(IsNull(Rwith,'')) from Build_File..tbCoRevenueCode Where RevenueID = @strRevenueID Group by RevenueID),''); /* Get MedicareType of Item */ Set @strPosition = ( Case @strRevenueID When 'SS' Then 'N' When 'RT' Then '1' When 'RA' Then '1' When 'PT' Then IsNull( ( Select IsNull(MedicareType,'') from Build_File..tbCoPTExam Where PTExamID = @strCode ), '' ) When 'CT' Then IsNull( ( Select IsNull(MedicareType,'') from Build_File..tbCoCTExam Where CTExamID = @strCode ), '' ) When 'AU' Then IsNull( ( Select IsNull(MedicareType,'') from Build_File..tbCoAudExam Where AudExamID = @strCode ), '' ) When 'HS' Then IsNull( ( Select IsNull(MedicareType,'') from Build_File..tbCoHSExam Where HSExamID = @strCode ), '' ) When 'CV' Then IsNull( ( Select IsNull(MedicareType,'') from Build_File..tbCoHSExam Where HSExamID = @strCode ), '' ) When 'NU' Then IsNull( ( Select IsNull(MedicareType,'') from Build_File..tbCoNucExam Where NucExamID = @strCode ), '' ) When 'PA' Then IsNull( ( Select IsNull(MedicareType,'') from Build_File..tbCoRTSExam Where RTSExamID = @strCode ), '' ) When 'XR' Then IsNull( ( Select IsNull(MedicareType,'') from Build_File..tbCoXrayExam Where XrayExamID = @strCode ), '' ) When 'LB' Then IsNull( ( Select IsNull(MedicareType,'') from Build_File..tbCoLabExam Where LabExamID = @strCode ), '' ) When 'HI' Then IsNull( ( Select IsNull(MedicareType,'') from Build_File..tbCoLabExam Where LabExamID = @strCode ), '' ) When 'BB' Then IsNull( ( Select IsNull(MedicareType,'') from Build_File..tbCoLabExam Where LabExamID = @strCode ), '' ) When 'BA' Then IsNull( ( Select IsNull(MedicareType,'') from Build_File..tbCoLabExam Where LabExamID = @strCode ), '' ) When 'US' Then IsNull( ( Select IsNull(MedicareType,'') from Build_File..tbCoUltraExam Where UltraExamID = @strCode ), '' ) When 'PH' Then IsNull( ( Select IsNull(MedicareType,'') from Inventory..tbInvMaster Where ItemID = @strCode ), '' ) When 'PC' Then '2' /* Returned Medicine */ When 'CS' Then IsNull( ( Select IsNull(MedicareType,'') from Inventory..tbInvMaster Where ItemID = @strCode ), '' ) When 'HC' Then IsNull( ( Select IsNull(MedicareType,'') from Inventory..tbInvMaster Where ItemID = @strCode ), '' ) When 'CC' Then '3' /* Returned Supplies */ When 'MD' then 'N' When 'FP' then 'N' Else Case @strRWith When 'Y' then IsNull( ( Select Max(IsNull(MedicareType,'')) from Build_File..tbCoOtherRevenue Where OtherRevenueID = @strCode Group by OtherRevenueID ), '' ) Else IsNull( ( Select Max(IsNull(MedicareType,'')) from Build_File..tbCoRevenueCode Where RevenueID = @strRevenueID Group by RevenueID ), '' ) End End ); Set @strCategory = 'A'; /* Get MedicareCategory of Item */ Set @strCategory = ( Case @strRevenueID When 'PT' Then IsNull( ( Select IsNull(MedicareCategory,'') from Build_File..tbCoPTExam Where PTExamID = @strCode ), '' ) When 'CT' Then IsNull( ( Select IsNull(MedicareCategory,'') from Build_File..tbCoCTExam Where CTExamID = @strCode ), '' ) When 'AU' Then IsNull( ( Select IsNull(MedicareCategory,'') from Build_File..tbCoAudExam Where AudExamID = @strCode ), '' ) When 'HS' Then IsNull( ( Select IsNull(MedicareCategory,'') from Build_File..tbCoHSExam Where HSExamID = @strCode ), '' ) When 'CV' Then IsNull( ( Select IsNull(MedicareCategory,'') from Build_File..tbCoHSExam Where HSExamID = @strCode ), '' ) When 'NU' Then IsNull( ( Select IsNull(MedicareCategory,'') from Build_File..tbCoNucExam Where NucExamID = @strCode ), '' ) When 'PA' Then IsNull( ( Select IsNull(MedicareCategory,'') from Build_File..tbCoRTSExam Where RTSExamID = @strCode ), '' ) When 'XR' Then IsNull( ( Select IsNull(MedicareCategory,'') from Build_File..tbCoXrayExam Where XrayExamID = @strCode ), '' ) When 'LB' Then IsNull( ( Select IsNull(MedicareCategory,'') from Build_File..tbCoLabExam Where LabExamID = @strCode ), '' ) When 'HI' Then IsNull( ( Select IsNull(MedicareCategory,'') from Build_File..tbCoLabExam Where LabExamID = @strCode ), '' ) When 'BB' Then IsNull( ( Select IsNull(MedicareCategory,'') from Build_File..tbCoLabExam Where LabExamID = @strCode ), '' ) When 'BA' Then IsNull( ( Select IsNull(MedicareCategory,'') from Build_File..tbCoLabExam Where LabExamID = @strCode ), '' ) When 'US' Then IsNull( ( Select IsNull(MedicareCategory,'') from Build_File..tbCoUltraExam Where UltraExamID = @strCode ), '' ) When 'PH' Then IsNull( ( Select IsNull(MedicareCategory,'') from Inventory..tbInvMaster Where ItemID = @strCode ), '' ) When 'CS' Then IsNull( ( Select IsNull(MedicareCategory,'') from Inventory..tbInvMaster Where ItemID = @strCode ), '' ) When 'HC' Then IsNull( ( Select IsNull(MedicareCategory,'') from Inventory..tbInvMaster Where ItemID = @strCode ), '' ) Else Case @strRWith When 'Y' Then IsNull( ( Select Max(IsNull(MedicareCategory,'')) from Build_File..tbCoOtherRevenue Where OtherRevenueID = @strCode Group by OtherRevenueID ), '' ) Else IsNull( ( Select Max(IsNull(MedicareCategory,'')) from Build_File..tbCoRevenueCode Where RevenueID = @strCode Group by RevenueID ), '' ) End End ); /* Default Position When code not found */ If Len(@strPosition) = 0 Begin Select @strPosition = ( Select MedicareType from Build_File..tbCoRevenueCode Where RevenueID = @strRevenueID ); End; /* If Code is blank get Position from tbCoRevenueCode */ If Len(@strCode) = 0 and (not @strRevenueID = 'RA' or not @strRevenueID = 'RT') Begin Set @strPosition = ( Select MedicareType from Build_File..tbCoRevenueCode Where RevenueID = @strRevenueID ); End; If @fltTempQty = 0 Begin Set @fltTempQty = 1; End; -- If not @strPosition = 'N' /* Do not include non-medicare items */ Begin If Exists( Select * from Medicare..Medic_DumpTable Where Code = @strCode and RevenueID = @strRevenueID and IdNum = @strIdNum and UserID = @strUserID ) Begin If @fltAmount < 0 Begin Set @fltTempQty = Abs(@fltTempQty) * -1; End; If Len(@strCode) = 0 Begin Update Medicare..Medic_DumpTable Set Quantity = Quantity + @fltTempQty, Amount = Amount + @fltAmount Where RevenueID = @strRevenueID and IdNum = @strIdNum and UserID = @strUserID; End Else Begin Update Medicare..Medic_DumpTable Set Quantity = Quantity + @fltTempQty, Amount = Amount + @fltAmount Where Code = @strCode and RevenueID = @strRevenueID and IdNum = @strIdNum and UserID = @strUserID; End; End; Else Begin If @strRevenueID = 'PC' /* Returned Medicines */ Begin Update Medicare..Medic_DumpTable Set Quantity = Quantity - Abs(@fltTempQty), Amount = Amount - Abs(@fltAmount) Where Code = @strCode and RevenueID = 'PH' and IdNum = @strIdNum and UserID = @strUserID; End; Else If @strRevenueID = 'CC' /* Returned Supplies */ Begin Update Medicare..Medic_DumpTable Set Quantity = Quantity - (Abs(@fltTempQty) ), Amount = Amount - (Abs(@fltAmount) ) Where Code = @strCode and RevenueID = 'CS' and IdNum = @strIdNum and UserID = @strUserID; End; Else Begin If @strRevenueID = 'PH' or @strRevenueID = 'HC' Begin Set @strDescription = ( Select IsNull(B.GenericName,'') + ' ' + IsNull(A.ItemDesc,'') + ' (' + A.ItemName + ')' from Inventory..tbInvMaster A Left Outer Join Inventory..tbForGeneric B On A.GenericID = B.GenericID Where A.ItemID = @strCode ); End; Insert Medicare..Medic_DumpTable( Code, Description, Quantity, Amount, RevenueID, Position, IdNum, UserID, MedicareCategory, LocationID ) Values( @strCode, @strDescription, @fltTempQty, @fltAmount, @strRevenueID, @Position, --@strPosition, @strIdNum, @strUserID, @Category, @LocationID) --@strCategory ); End; End; If @@error<>0 Begin goto Abort_Insert; End; End; /* If not @strPosition = 'N' */ Commit Tran; Return 0; Abort_Insert: Rollback Tran; Return -100001; -------------------------------- GO /****** Object: StoredProcedure [dbo].[Medic_AutoComputeChargesOP] Script Date: 04/25/2012 17:45:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO /****** Object: Stored Procedure dbo.Medic_AutoComputeCharges Script Date: 07/01/2000 1:19:49 AM ******/ /*** 02/14/2002 : Convert condition transdate to ***/ ALTER PROCEDURE [dbo].[Medic_AutoComputeChargesOP] --'498376B' , 'jem' @IdNum as varchar(10), @strUserID as varchar(10) AS /******/ Declare Patient_Charges CURSOR FOR Select Amount, RevenueID, ItemID, Quantity, Description, MedicareType, MedicareCategory, LocationID--, TransDate from Billing..OPBillingDrugsSOA Where IDNum = @IDNum --and (convert(varchar(10),TransDate,101) < = convert(varchar(10),BillingDate,101) or BillingDate is null) --Where IDNum = @IDNum and (TransDate < = ,BillingDate or BillingDate is null) UNION ALL Select Amount, RevenueID, ItemID, Quantity, Description, MedicareType, MedicareCategory, LocationID--, TransDate from Billing..vwHistoryOutDrugs Where IDNum = @IDNum --and (convert(varchar(10),TransDate,101) < = convert(varchar(10),BillingDate,101) or BillingDate is null) UNION ALL Select Amount, RevenueID, ItemID, Quantity, Description, MedicareType, MedicareCategory, LocationID--, TransDate--, TransDate from Billing..OPBillingOthersSOA Where IDNum = @IDNum --and (convert(varchar(10),TransDate,101) < = convert(varchar(10),BillingDate,101) or BillingDate is null) and DrCr <> 'P' and RevenueID not in ('PH','PC','CS','CC') UNION ALL Select Amount, RevenueID, ItemID, Quantity, Description, MedicareType, MedicareCategory, LocationID--, TransDate--, TransDate from Billing..vwHistoryOut Where IDNum = @IDNum --and (convert(varchar(10),TransDate,101) < = convert(varchar(10),BillingDate,101) or BillingDate is null) and DrCr <> 'P' and RevenueID not in ('PH','PC','CS','CC');-- --Order By Transdate; /* SELECT (CASE WHEN TBINVSTOCKCARD.amount is NULL then 0 WHEN tbinvstockcard.revenueid = 'PC' or tbinvstockcard.revenueid = 'CC' then amount * -1 ELSE TBINVSTOCKCARD.AMOUNT END) as AMOUNT, isnull(TBcoRevenueCode.ServiceId,'') as RevenueID, isnull(TBINVSTOCKCARD.itemid,'') as ItemId, tbinvstockcard.quantity, ltrim(rtrim(isnull(tbinvmaster.itemname,'') + ' ' + isnull(tbinvmaster.itemdesc,''))) as ItemDesc FROM Inventory..TBINVSTOCKCARD tbInvStockCard LEFT OUTER JOIN Patient_Data..tbOutPatient tbOutPatient ON TBINVSTOCKCARD.IdNum = tbOutPatient.IdNum LEFT OUTER JOIN Inventory..TBINVMASTER tbInvmaster ON TBINVSTOCKCARD.ItemID = TBINVMASTER.ItemID LEFT OUTER JOIN Billing..tbbillInvCode tbcoRevenueCode on tbinvstockcard.revenueid = tbcorevenuecode.revenueid and tbinvstockcard.locationID = tbcorevenuecode.locationID WHERE TBINVSTOCKCARD.IDNUM= @IDNUM Union All SELECT (CASE WHEN TBBILLOPDAILYOUT.drcr = NULL then 0 WHEN TBBILLOPDAILYOUT.drcr = 'D' then TBBILLOPDAILYOUT.amount WHEN TBBILLOPDAILYOUT.drcr = 'C' then (TBBILLOPDAILYOUT.amount * -1) WHEN TBBILLOPDAILYOUT.drcr = 'P' and TBBILLOPDAILYOUT.revenueid = 'MD' then TBBILLOPDAILYOUT.amount WHEN TBBILLOPDAILYOUT.drcr = 'P' and TBBILLOPDAILYOUT.revenueid <> 'MD' then (TBBILLOPDAILYOUT.amount * -1) END) as AMOUNT, isnull(TBBILLOPDAILYOUT.RevenueId,'') as RevenueID, isnull(TBBILLOPDAILYOUT.itemid,'') as ItemId, TBBILLOPDAILYOUT.quantity, (CASE WHEN rtrim(ltrim(TBBILLOPDAILYOUT.ItemID)) = '' then tbcorevenuecode.itemname WHEN TBBILLOPDAILYOUT.drcr = 'P' then Tbcorevenuecode.ItemName + ' - ' + 'DR. ' + isnull(TbcoDoctor.LastName,'') + ', ' + isnull(TbcoDoctor.Firstname,'') +' ' + isnull(TbcoDoctor.MiddleName,'') WHEN TBBILLOPDAILYOUT.RevenueID = 'CP' then 'COMPANY PAYMENT - ' + tbCoCompany.Company ELSE tbBillExamListing.Description End) as ItemDesc FROM Billing..TBBILLOPDAILYOUT TBBILLOPDAILYOUT LEFT OUTER JOIN Patient_Data..tbOutPatient tbOutpatient ON TBBILLOPDAILYOUT.IdNum = tbOutPatient.IdNum LEFT OUTER JOIN Billing..TbBillExamListing tbBillExamListing ON tbBillOPDailyOut.RevenueId = tbBillExamListing.RevenueId and tbBillOPDailyOut.ItemId = tbBillExamListing.ItemId LEFT OUTER JOIN Billing..tbcoCompany tbcoCompany On tbBillOPDailyOut.ItemID = TbCoCompany.CompanyId LEFT OUTER Join Billing..tbCoDoctor tbcoDoctor ON TBBILLOPDAILYOUT.ItemID = tbCoDoctor.DoctorID LEFT OUTER JOIN Billing..tbCoRevenueCode tbCoRevenueCode ON TBBILLOPDAILYOUT.RevenueID = tbCoRevenueCode.RevenueID WHERE TBBILLOPDAILYOUT.IDNUM= @IDNUM;*/ /******/ declare @strCode as varchar(10) declare @strDescription as varchar(100) declare @fltQuantity as float declare @fltAmount as float declare @strRevenueID as varchar(2) declare @strIdNum as varchar(10) declare @strPosition as varchar(1) declare @fltTempQty as float declare @intRecordCount as int declare @intCtr as int declare @Position as varchar(2) declare @Category as varchar(2) declare @strLocationID as varchar(5) Set @strIdNum = @IdNum Open Patient_Charges Fetch Next from Patient_Charges Into @fltAmount, @strRevenueID, @strCode, @fltQuantity, @strDescription, @Position, @Category, @strLocationID Set @intRecordCount = @@Cursor_Rows Set @intCtr = 1 Exec Medicare..Medic_InsertTempTable @strCode, @strDescription, @fltQuantity, @fltAmount, @strRevenueID, @strIdNum, @strUserID, @Position, @Category, @strLocationID Begin Tran While @intCtr < @intRecordCount Begin Fetch Next from Patient_Charges Into @fltAmount, @strRevenueID, @strCode, @fltQuantity, @strDescription, @Position, @Category, @strLocationID Exec Medicare..Medic_InsertTempTable @strCode, @strDescription, @fltQuantity, @fltAmount, @strRevenueID, @strIdNum, @strUserID, @Position, @Category, @strLocationID Set @intCtr = @intCtr + 1 End Commit Tran CLOSE Patient_Charges DEALLOCATE Patient_Charges Return 0 Abort_Insert: Rollback Tran Return -100001