Jeff S
asked on
SQL 2005 SubQuery help.
I need some assistance with a sub-query.
Currently my sub-query filters out codes (WHERE p.CPTCode IN ( '520', '521', '524', '525', '0521', '0524', '0525', '0900', '900', 'T1015' ) --- however I need to add one twist to this. I have 3 action's in the Transaction table - (A = Adjustment, P = Payment, T = Transfer). I need to only exclude the action types 'A' and 'P' but allow the 'T' action. I can't seem to get this in right.
My Subquery:
WHERE t.transactionsid NOT IN (
SELECT TransactionsId
FROM TransactionDistributions td
INNER JOIN PatientVisitProcs p ON td.PatientVisitProcsId = p.PatientVisitProcsId
WHERE p.CPTCode IN ( '520', '521', '524', '525', '0521', '0524',
'0525', '0900', '900', 'T1015' ) )
Any help is appreciated. Thanks. (Full stored Procedure below).
Currently my sub-query filters out codes (WHERE p.CPTCode IN ( '520', '521', '524', '525', '0521', '0524', '0525', '0900', '900', 'T1015' ) --- however I need to add one twist to this. I have 3 action's in the Transaction table - (A = Adjustment, P = Payment, T = Transfer). I need to only exclude the action types 'A' and 'P' but allow the 'T' action. I can't seem to get this in right.
My Subquery:
WHERE t.transactionsid NOT IN (
SELECT TransactionsId
FROM TransactionDistributions td
INNER JOIN PatientVisitProcs p ON td.PatientVisitProcsId = p.PatientVisitProcsId
WHERE p.CPTCode IN ( '520', '521', '524', '525', '0521', '0524',
'0525', '0900', '900', 'T1015' ) )
Any help is appreciated. Thanks. (Full stored Procedure below).
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[cusMPMStatementFQHC_Fargo]
@pGuarantorId INT ,
@pStatementsCriteriaId INT = NULL
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --to eliminate connection blocks.
SET NOCOUNT ON
/*Table variable for procedures, will add balance forward information if selected*/
DECLARE @bfProcs TABLE
(
BeginningOfRow VARCHAR(255) ,
Type SMALLINT ,
GuarantorId INT ,
PatientProfileId INT ,
PatientVisitId INT ,
DoctorId INT ,
FacilityId INT ,
CompanyId INT ,
GuarantorName_Sort VARCHAR(255) ,
GuarantorZip_Sort VARCHAR(10) ,
SortOrder VARCHAR(20) ,
LastStatementDate DATETIME ,
PatientVisitProcsId INT ,
TransactionService DATETIME NULL ,
TransactionEntry DATETIME NULL ,
Code VARCHAR(10) ,
CPTCode VARCHAR(10) ,
RevenueCode VARCHAR(10) ,
Description VARCHAR(255) ,
Modifier1 VARCHAR(200) ,
Modifier2 VARCHAR(200) ,
Modifier3 VARCHAR(200) ,
Modifier4 VARCHAR(200) ,
PlaceOfService VARCHAR(3) ,
TypeOfService VARCHAR(3) ,
Units FLOAT ,
Fee MONEY ,
PatientAmount MONEY ,
InsuranceAmount MONEY ,
TransactionNote TEXT ,
TotalFee MONEY , -- this is only needed for Electronic Statements DK
ListOrder INT
)
DECLARE @UniqueId UNIQUEIDENTIFIER ,
@DB VARCHAR(128) ,
@ret INT
SELECT @UniqueId = NEWID() ,
@DB = DB_NAME()
DECLARE @TransactionThrough DATETIME
/* Get the standard statement options */
DECLARE @IncludeAging SMALLINT ,
@IncludeInsuranceBalanceOnly SMALLINT ,
@IncludeByDoctor SMALLINT ,
@IncludeCash SMALLINT ,
@IncludePatientWithTransactions SMALLINT ,
@IncludePayToAddress SMALLINT ,
@IncludeReferringDoctor SMALLINT ,
@IncludeTransactionsAfterClosing_Individual SMALLINT ,
@IncludeTransactionsAfterClosing_Batch SMALLINT ,
@IncludeFilingHistory SMALLINT ,
@SystemCompanyId INT ,
@BalanceForward INT
SELECT @IncludeAging = CASE WHEN ISNULL(OtherLong, 0) & 1 <> 0 THEN 1
ELSE 0
END ,
@IncludeInsuranceBalanceOnly = CASE WHEN ISNULL(OtherLong, 0) & 2 <> 0
THEN 1
ELSE 0
END ,
@IncludeByDoctor = CASE WHEN ISNULL(OtherLong, 0) & 8 <> 0 THEN 1
WHEN ISNULL(Otherlong, 0) & 4096 <> 0
THEN 2
ELSE 0
END ,
@IncludeCash = CASE WHEN ISNULL(OtherLong, 0) & 16 <> 0 THEN 1
ELSE 0
END ,
@IncludePatientWithTransactions = CASE WHEN ISNULL(OtherLong, 0)
& 32 <> 0 THEN 1
ELSE 0
END ,
@IncludePayToAddress = CASE WHEN ISNULL(OtherLong, 0) & 128 <> 0
THEN 1
ELSE 0
END ,
@IncludeTransactionsAfterClosing_Individual = CASE
WHEN ISNULL(OtherLong,
0) & 64 <> 0
THEN 1
ELSE 0
END ,
@IncludeTransactionsAfterClosing_Batch = CASE WHEN ISNULL(OtherLong,
0) & 1024 <> 0
THEN 1
ELSE 0
END ,
@IncludeReferringDoctor = CASE WHEN ISNULL(OtherLong, 0) & 512 <> 0
THEN 1
ELSE 0
END ,
@IncludeFilingHistory = CASE WHEN ISNULL(OtherLong, 0) & 2048 <> 0
THEN 1
ELSE 0
END ,
@BalanceForward = CASE WHEN ISNULL(OtherLong, 0) & 8192 <> 0
THEN 1
ELSE 0
END
FROM MedLists
WHERE TableName = 'StatementOptions'
IF @IncludeByDoctor = 2
BEGIN
SELECT TOP 1
@SystemCompanyId = JoinId
FROM Medlists
WHERE TableName = 'StatementOptions'
END
/* Get the LastTransaction date to use */
SELECT @TransactionThrough = CASE WHEN @IncludeTransactionsAfterClosing_Individual = 1
AND @pStatementsCriteriaId IS NULL
THEN NULL
WHEN @IncludeTransactionsAfterClosing_Batch = 1
AND NOT @pStatementsCriteriaId IS NULL
THEN NULL
ELSE CONVERT(DATETIME, cd.FunctionName)
END
FROM MedLists cd
WHERE cd.TableName = 'Administration'
AND cd.Description = 'TransactionClosingDate'
-- CYS 09/15/2006 Last Payment and Last Payment Date
DECLARE @LastPayment MONEY ,
@LastPaymentDate DATETIME
SELECT @LastPayment = NULL ,
@LastPaymentDate = NULL
SELECT TOP 1
@LastPaymentDate = MAX(pm.DateofEntry) ,
@LastPayment = pm.AppliedAmount
FROM VisitTransactions vt
INNER JOIN PatientVisit pv ON vt.PatientVisitId = pv.PatientVisitId
INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId
INNER JOIN Transactions t ON t.VisitTransactionsId = vt.VisitTransactionsId
INNER JOIN Batch b ON pm.BatchId = b.BatchId
INNER JOIN PatientProfile pp ON pv.PatientProfileID = pp.PatientProfileID
WHERE pm.Source = 1
AND t.action = 'P'
AND pm.InsuranceTransfer IS NULL
AND pp.GuarantorId = 400
AND pm.DateofEntry < DATEADD(day, 1,
ISNULL(@TransactionThrough, GETDATE()))
AND pm.AppliedAmount > 0
GROUP BY pm.DateofEntry ,
pm.AppliedAmount
ORDER BY pm.DateofEntry DESC
EXECUTE @ret = MPMStatement_GetVisits @DB, @UniqueId, @pGuarantorId,
@pStatementsCriteriaId, @TransactionThrough,
@IncludeInsuranceBalanceOnly, @IncludePatientWithTransactions,
@IncludeTransactionsAfterClosing_Individual,
@IncludeTransactionsAfterClosing_Batch, @IncludeByDoctor,
@IncludeAging, @BalanceForward
IF @ret = 0 /* No visits for this guarantor */
RETURN ;
/* Get CreditCard Types */
DECLARE @CCT VARCHAR(200) ,
@CreditCard1 VARCHAR(200) ,
@CreditCard2 VARCHAR(200) ,
@CreditCard3 VARCHAR(200) ,
@CreditCard4 VARCHAR(200) ,
@CreditCard5 VARCHAR(200) ,
@CreditCard6 VARCHAR(200)
DECLARE cCCT CURSOR STATIC FORWARD_ONLY LOCAL
FOR SELECT
cct.Description
FROM
MedLists cct
WHERE
cct.TableName = 'CreditCardTypes'
ORDER BY
cct.ListOrder
OPEN cCCT
FETCH NEXT FROM cCCT INTO @CCT
WHILE ( @@fetch_status <> -1
AND @CreditCard6 IS NULL
)
BEGIN
IF @CreditCard1 IS NULL
SELECT @CreditCard1 = @CCT
ELSE
IF @CreditCard2 IS NULL
SELECT @CreditCard2 = @CCT
ELSE
IF @CreditCard3 IS NULL
SELECT @CreditCard3 = @CCT
ELSE
IF @CreditCard4 IS NULL
SELECT @CreditCard4 = @CCT
ELSE
IF @CreditCard5 IS NULL
SELECT @CreditCard5 = @CCT
ELSE
IF @CreditCard6 IS NULL
SELECT @CreditCard6 = @CCT
FETCH NEXT FROM cCCT INTO @CCT
END
DEALLOCATE cCCT
DECLARE @PPId INT
DECLARE @PVId INT
DECLARE @GName VARCHAR(110)
DECLARE @PrevPPId INT
DECLARE @PrevGName VARCHAR(110)
DECLARE @LogonId VARCHAR(30)
SELECT @LogonId = dbo.GetLogonId()
SELECT @PrevPPId = -1
SELECT @PrevGName = ''
DECLARE cTmp CURSOR STATIC FORWARD_ONLY LOCAL
FOR SELECT
PatientProfileId ,
GuarantorName ,
PatientVisitId
FROM
gtVisits
WHERE
UniqueIdentifierId = @UniqueId
OPEN cTmp
FETCH NEXT FROM cTmp INTO @PPId , @GName , @PVId
WHILE ( @@fetch_status <> -1 )
BEGIN
IF @PrevPPId <> @PPID
OR @PrevGName <> @GName
BEGIN
SELECT @PrevPPId = @PPId
SELECT @PrevGName = @GName
INSERT INTO PatientInfoLog
( PatientProfileId ,
PatientVisitId ,
ActionType ,
Created ,
CreatedBy ,
LastModified ,
LastModifiedBy
)
VALUES ( @PPId ,
@PVId ,
13 ,
GETDATE() ,
@LogonId ,
GETDATE() ,
@LogonId
)
END
FETCH NEXT FROM cTmp INTO @PPId , @GName , @PVId
END
DEALLOCATE cTmp
/* Get Visit Information */
SELECT '0.BeginningOfRow' = 'BeginningOfRow' ,
'1.Type' = 0 ,
'2.GuarantorId' = g.GuarantorId ,
'3.PatientProfileId' = tv.PatientProfileId ,
'4.PatientVisitId' = tv.PatientVisitId ,
'5.DoctorId' = tv.DoctorId ,
'6.FacilityId' = tv.FacilityId ,
'7.CompanyId' = tv.CompanyId ,
'8.GuarantorName_Sort' = tv.GuarantorName ,
'9.GuarantorZip_Sort' = tv.GuarantorZip ,
'10.SortOrder' = tv.SortOrder ,
'11.LastStatementDate' = tv.LastStatement ,
'13.GuarantorName' = LTRIM(RTRIM(RTRIM(RTRIM(RTRIM(ISNULL(g.Prefix,
'') + ' '
+ ISNULL(g.First,
'')) + ' '
+ ISNULL(g.Middle, ''))
+ ' ' + ISNULL(g.Last, ''))
+ ' ' + ISNULL(g.Suffix, ''))) ,
'14.GuarantorAddress1' = g.Address1 ,
'15.GuarantorAddress2' = g.Address2 ,
'16.GuarantorCity' = g.City ,
'17.GuarantorState' = g.State ,
'18.GuarantorZip' = g.Zip ,
'19.GuarantorCountry' = g.Country ,
'20.IncludeAging' = @IncludeAging ,
'21.IncludeInsuranceBalanceOnly' = @IncludeInsuranceBalanceOnly ,
'23.IncludeByDoctor' = @IncludeByDoctor ,
'24.IncludeCash' = @IncludeCash ,
'25.IncludePatientWithTransactions' = @IncludePatientWithTransactions ,
'26.IncludePayToAddress' = @IncludePayToAddress ,
'27.IncludeTransactionsAfterClosing_Individual' = @IncludeTransactionsAfterClosing_Individual ,
'28.IncludeTransactionsAfterClosing_Batch' = @IncludeTransactionsAfterClosing_Batch ,
'29.IncludeReferringDoctor' = @IncludeReferringDoctor ,
'30.CreditCard1' = @CreditCard1 ,
'31.CreditCard2' = @CreditCard2 ,
'32.CreditCard3' = @CreditCard3 ,
'33.CreditCard4' = @CreditCard4 ,
'34.CreditCard5' = @CreditCard5 ,
'35.CreditCard6' = @CreditCard6 ,
'39.TransactionThrough' = ISNULL(@TransactionThrough, GETDATE()) ,
'40.PatientId' = pp.PatientId ,
'41.PatientName' = LTRIM(RTRIM(RTRIM(RTRIM(RTRIM(ISNULL(pp.Prefix,
'') + ' '
+ ISNULL(pp.First,
'')) + ' '
+ ISNULL(pp.Middle, ''))
+ ' ' + ISNULL(pp.Last, ''))
+ ' ' + ISNULL(pp.Suffix, ''))) ,
'42.PatientAddress1' = pp.Address1 ,
'43.PatientAddress2' = pp.Address2 ,
'44.PatientCity' = pp.City ,
'45.PatientState' = pp.State ,
'46.PatientZip' = pp.Zip ,
'47.PatientCountry' = pp.Country ,
'50.Budget' = /*g.Budget,*/ ISNULL(pt.MonthlyPayment, g.Budget) ,
'51.Visit' = pv.Visit ,
'52.Entered' = pv.Entered ,
'53.TicketNumber' = pv.TicketNumber ,
'55.DoctorName' = LTRIM(RTRIM(RTRIM(RTRIM(RTRIM(ISNULL(d.Prefix,
'') + ' '
+ ISNULL(d.First,
'')) + ' '
+ ISNULL(d.Middle, ''))
+ ' ' + ISNULL(d.Last, ''))
+ ' ' + ISNULL(d.Suffix, ''))) ,
'56.DoctorAddress1' = d.Address1 ,
'57.DoctorAddress2' = d.Address2 ,
'58.DoctorCity' = d.City ,
'59.DoctorState' = d.State ,
'60.DoctorZip' = d.Zip ,
'61.DoctorCountry' = d.Country ,
'62.ReferringDoctor' = LTRIM(RTRIM(RTRIM(RTRIM(RTRIM(ISNULL(rd.Prefix,
'') + ' '
+ ISNULL(rd.First,
'')) + ' '
+ ISNULL(rd.Middle,
'')) + ' '
+ ISNULL(rd.Last, ''))
+ ' ' + ISNULL(rd.Suffix, ''))) ,
'65.Company' = CASE WHEN @IncludeByDoctor <> 2 THEN fg.OrgName
ELSE c.OrgName
END ,
'66.CompanyAddress1' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.Address1
ELSE c.Address1
END ,
'67.CompanyAddress2' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.Address2
ELSE c.Address2
END ,
'68.CompanyCity' = CASE WHEN @IncludeByDoctor <> 2 THEN fg.City
ELSE c.City
END ,
'69.CompanyState' = CASE WHEN @IncludeByDoctor <> 2 THEN fg.State
ELSE c.State
END ,
'70.CompanyZip' = CASE WHEN @IncludeByDoctor <> 2 THEN fg.Zip
ELSE c.Zip
END ,
'71.CompanyCountry' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.Country
ELSE c.Country
END ,
'72.CompanyPhone1' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.Phone1
ELSE c.Phone1
END ,
'75.Facility' = f.OrgName ,
'76.FacilityAddress1' = f.Address1 ,
'77.FacilityAddress2' = f.Address2 ,
'78.FacilityCity' = f.City ,
'79.FacilityState' = f.State ,
'80.FacilityZip' = f.Zip ,
'81.FacilityCountry' = f.Country ,
'82.FacilityPhone1' = f.Phone1 ,
'136.LastPaymentDate' = CASE WHEN @LastPaymentDate IS NULL
THEN 'None'
ELSE CONVERT(VARCHAR(10), @LastPaymentDate, 101)
END ,
'137.LastPayment' = @LastPayment ,
'138.ResourceName' = ( SELECT TOP 1
ISNULL(r.listname, '')
FROM patientvisitresource pvr
LEFT JOIN doctorfacility r ON pvr.resourceid = r.doctorfacilityid
WHERE pvr.patientvisitid = tv.patientvisitid
)
FROM gtVisits tv
INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId
INNER JOIN PatientProfile pp ON tv.PatientProfileId = pp.PatientProfileId
INNER JOIN DoctorFacility d ON tv.DoctorId = d.DoctorFacilityId
INNER JOIN DoctorFacility f ON tv.FacilityId = f.DoctorFacilityId
INNER JOIN DoctorFacility fg ON tv.CompanyId = fg.DoctorFacilityId
LEFT JOIN DoctorFacility rd ON pv.ReferringDoctorId = rd.DoctorFacilityId
INNER JOIN Guarantor g ON g.GuarantorId = @pGuarantorId
/*Add in CPS06 Payment Plan coding JAS - Cys 07/10/2008*/
LEFT JOIN PaymentPlan pt ON pt.GuarantorId = @pGuarantorId
AND ISNULL(pt.Active, 0) <> 0
LEFT JOIN DoctorFacility c ON @SystemCompanyId = c.DoctorFacilityId
WHERE tv.UniqueIdentifierId = @UniqueId
AND ( @BalanceForward = 0
OR tv.TransactionsSinceLastStatement = 1
)
/* Get the diagnosis for this visit */
SELECT '0.BeginningOfRow' = 'BeginningOfRow' ,
'1.Type' = 1 ,
'2.GuarantorId' = @pGuarantorId ,
'3.PatientProfileId' = tv.PatientProfileId ,
'4.PatientVisitId' = tv.PatientVisitId ,
'5.DoctorId' = tv.DoctorId ,
'6.FacilityId' = tv.FacilityId ,
'7.CompanyId' = tv.CompanyId ,
'8.GuarantorName_Sort' = tv.GuarantorName ,
'9.GuarantorZip_Sort' = tv.GuarantorZip ,
'10.SortOrder' = tv.SortOrder ,
'11.LastStatementDate' = tv.LastStatement ,
'110.PatientVisitDiagsId' = pvd.PatientVisitDiagsId ,
'113.Code' = pvd.Code ,
'114.ICD9Code' = pvd.ICD9Code ,
'117.Description' = pvd.Description
FROM gtVisits tv
INNER JOIN PatientVisitDiags pvd ON tv.PatientVisitId = pvd.PatientVisitId
WHERE tv.UniqueIdentifierId = @UniqueId
AND ( @BalanceForward = 0
OR ( tv.TransactionsSinceLastStatement = 1
AND tv.OnPreviousStatement = 0
)
)
/* Get the procedures for this visit */
INSERT @bfProcs
SELECT 'BeginningOfRow' = 'BeginningOfRow' ,
'Type' = 2 ,
'GuarantorId' = @pGuarantorId ,
'PatientProfileId' = tv.PatientProfileId ,
'PatientVisitId' = tv.PatientVisitId ,
'DoctorId' = tv.DoctorId ,
'FacilityId' = tv.FacilityId ,
'CompanyId' = tv.CompanyId ,
'GuarantorName_Sort' = tv.GuarantorName ,
'GuarantorZip_Sort' = tv.GuarantorZip ,
'SortOrder' = tv.SortOrder ,
'LastStatementDate' = tv.LastStatement ,
'PatientVisitProcsId' = pvp.PatientVisitProcsId ,
'TransactionService' = pvp.DateOfServiceFrom ,
'TransactionEntry' = pvp.DateOfEntry ,
'Code' = pvp.Code ,
'CPTCode' = pvp.CPTCode ,
'RevenueCode' = pvp.RevenueCode ,
'Description' = pvp.Description ,
'Modifier1' = CONVERT(VARCHAR(200), NULL) ,
'Modifier2' = CONVERT(VARCHAR(200), NULL) ,
'Modifier3' = CONVERT(VARCHAR(200), NULL) ,
'Modifier4' = CONVERT(VARCHAR(200), NULL) ,
'PlaceOfService' = CONVERT(VARCHAR(3), NULL) ,
'TypeOfService' = CONVERT(VARCHAR(3), NULL) ,
'Units' = pvp.Units ,
'Fee' = pvp.Fee ,
'PatientAmount' = pvpa.OrigPatAllocation ,
'InsuranceAmount' = pvpa.OrigInsAllocation ,
'TransactionNote' = pvp.Notes ,
'TotalFee' = pvp.TotalFee , -- this is only needed for Electronic Statements DK
'ListOrder' = pvp.ListOrder
FROM gtVisits tv
INNER JOIN PatientVisitProcs pvp ON tv.PatientVisitId = pvp.PatientVisitId
AND pvp.DateOfEntry <= ISNULL(@TransactionThrough,
pvp.DateOfEntry)--------------------*********************
INNER JOIN PatientVisitProcsAgg pvpa ON pvp.PatientVisitProcsId = pvpa.PatientVisitProcsId
WHERE tv.UniqueIdentifierId = @UniqueId
AND pvp.DateOfEntry >= CASE WHEN @BalanceForward = 1
THEN ISNULL(tv.LastStatement,
'1/1/1900')
ELSE pvp.DateOfEntry
END
IF @BalanceForward = 1
BEGIN
INSERT gtPaymentsNCO
SELECT @UniqueId ,
CASE WHEN @IncludeByDoctor = 1 THEN tv.DoctorId
ELSE 0
END AS DoctorId ,
CASE WHEN @IncludeByDoctor = 0 THEN tv.CompanyId
ELSE 0
END AS CompanyId ,
SUM(( CASE WHEN pm.Source = 2 THEN -t.Amount
WHEN t.Action = 'T' THEN t.Amount
ELSE 0
END )) AS InsTransTotal ,
SUM(( CASE WHEN pm.Source = 1 THEN -t.Amount
WHEN t.Action = 'T' THEN t.Amount
ELSE 0
END )) AS PatTransTotal ,
'P'
FROM gtVisits tv
INNER JOIN VisitTransactions vt ON tv.PatientVisitId = vt.PatientVisitid
INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId
AND ( pm.InsuranceTransfer = 0
OR pm.InsuranceTransfer IS NULL
)
INNER JOIN Transactions t ON vt.VisitTransactionsId = t.VisitTransactionsId
AND t.[Action] <> 'N'
LEFT OUTER JOIN MedLists at ON t.ActionTypeMId = at.MedListsId
WHERE pm.DateOfEntry < ISNULL(tv.LastStatement,
'1/1/1900')
AND tv.UniqueIdentifierId = @UniqueId
GROUP BY CASE WHEN @IncludeByDoctor = 1 THEN tv.DoctorId
ELSE 0
END ,
CASE WHEN @IncludeByDoctor = 0 THEN tv.CompanyId
ELSE 0
END
UNION
SELECT @UniqueId ,
CASE WHEN @IncludeByDoctor = 1 THEN tv.DoctorId
ELSE 0
END AS DoctorId ,
CASE WHEN @IncludeByDoctor = 0 THEN tv.CompanyId
ELSE 0
END AS CompanyId ,
SUM(pvpa.OrigInsAllocation) AS InsChargeAmount ,
SUM(pvpa.OrigPatAllocation) AS PatChargeAmount ,
'T'
FROM gtVisits tv
INNER JOIN PatientVisitProcs pvp ON tv.PatientVisitId = pvp.PatientVisitId
INNER JOIN PatientVisitProcsAgg pvpa ON pvp.PatientVisitProcsId = pvpa.PatientVisitProcsId
WHERE pvp.DateOfEntry < ISNULL(tv.LastStatement,
'1/1/1900')
AND tv.UniqueIdentifierId = @UniqueId
GROUP BY CASE WHEN @IncludeByDoctor = 1 THEN tv.DoctorId
ELSE 0
END ,
CASE WHEN @IncludeByDoctor = 0 THEN tv.CompanyId
ELSE 0
END
IF @IncludeByDoctor = 0 --Statement By Company
INSERT @bfProcs
SELECT 'BeginningOfRow' = 'BeginningOfRow' ,
'Type' = 2 ,
'GuarantorId' = @pGuarantorId ,
'PatientProfileId' = 0 ,
'PatientVisitId' = 0 ,
'DoctorId' = 0 ,
'FacilityId' = 0 ,
'CompanyId' = tv.CompanyId ,
'GuarantorName_Sort' = tv.GuarantorName ,
'GuarantorZip_Sort' = tv.GuarantorZip ,
'SortOrder' = tv.SortOrder ,
'LastStatementDate' = tv.LastStatement ,
'PatientVisitProcsId' = 0 ,
'TransactionService' = tv.LastStatement ,
'TransactionEntry' = NULL ,
'Code' = '' ,
'CPTCode' = '' ,
'RevenueCode' = '' ,
'Description' = 'Balance Forward' ,
'Modifier1' = '' ,
'Modifier2' = '' ,
'Modifier3' = '' ,
'Modifier4' = '' ,
'PlaceOfService' = '' ,
'TypeOfService' = '' ,
'Units' = 0 ,
'Fee' = 0 ,
'PatientAmount' = ( SELECT SUM(PatTransTotal)
FROM gtPaymentsNCO
WHERE gtPaymentsNCO.CompanyId = tv.CompanyId
AND TransType = 'T'
AND gtPaymentsNCO.UniqueIdentifierId = @UniqueId
) ,
'InsuranceAmount' = ( SELECT SUM(InsTransTotal)
FROM gtPaymentsNCO
WHERE gtPaymentsNCO.CompanyId = tv.CompanyId
AND TransType = 'T'
AND gtPaymentsNCO.UniqueIdentifierId = @UniqueId
) ,
'TransactionNote' = '' ,
'TotalFee' = 0 , -- this is only needed for Electronic Statements DK
'ListOrder' = 0
FROM gtVisits tv
INNER JOIN PatientVisitProcs pvp ON tv.PatientVisitId = pvp.PatientVisitId
AND pvp.DateOfEntry <= ISNULL(@TransactionThrough,
pvp.DateOfEntry)
INNER JOIN PatientVisitProcsAgg pvpa ON pvp.PatientVisitProcsId = pvpa.PatientVisitProcsId
WHERE tv.UniqueIdentifierId = @UniqueId
AND pvp.DateOfEntry < ISNULL(tv.LastStatement,
'1/1/1900')
GROUP BY tv.UniqueIdentifierId ,
tv.SortOrder ,
tv.CompanyId ,
tv.GuarantorName ,
tv.GuarantorZip ,
tv.LastStatement
-- CYS 04/27/2006 We need to separate the Balance Forward from the Payments so that we can show the payment activity since the last statement on the statement
INSERT @bfProcs
SELECT 'BeginningOfRow' = 'BeginningOfRow' ,
'Type' = 2 ,
'GuarantorId' = @pGuarantorId ,
'PatientProfileId' = 0 ,
'PatientVisitId' = 0 ,
'DoctorId' = 0 ,
'FacilityId' = 0 ,
'CompanyId' = tv.CompanyId ,
'GuarantorName_Sort' = tv.GuarantorName ,
'GuarantorZip_Sort' = tv.GuarantorZip ,
'SortOrder' = tv.SortOrder ,
'LastStatementDate' = tv.LastStatement ,
'PatientVisitProcsId' = 0 ,
'TransactionService' = tv.LastStatement ,
'TransactionEntry' = NULL ,
'Code' = '' ,
'CPTCode' = '' ,
'RevenueCode' = '' ,
'Description' = 'Payments Since '
+ CONVERT(VARCHAR(20), tv.LastStatement, 101) ,
'Modifier1' = '' ,
'Modifier2' = '' ,
'Modifier3' = '' ,
'Modifier4' = '' ,
'PlaceOfService' = '' ,
'TypeOfService' = '' ,
'Units' = 0 ,
'Fee' = 0 ,
'PatientAmount' = ( SELECT SUM(PatTransTotal)
FROM gtPaymentsNCO
WHERE gtPaymentsNCO.CompanyId = tv.CompanyId
AND TransType = 'P'
AND gtPaymentsNCO.UniqueIdentifierId = @UniqueId
) ,
'InsuranceAmount' = ( SELECT SUM(InsTransTotal)
FROM gtPaymentsNCO
WHERE gtPaymentsNCO.CompanyId = tv.CompanyId
AND TransType = 'P'
AND gtPaymentsNCO.UniqueIdentifierId = @UniqueId
) ,
'TransactionNote' = '' ,
'TotalFee' = 0 , -- this is only needed for Electronic Statements DK
'ListOrder' = 0
FROM gtVisits tv
INNER JOIN PatientVisitProcs pvp ON tv.PatientVisitId = pvp.PatientVisitId
AND pvp.DateOfEntry <= ISNULL(@TransactionThrough,
pvp.DateOfEntry)
INNER JOIN PatientVisitProcsAgg pvpa ON pvp.PatientVisitProcsId = pvpa.PatientVisitProcsId
WHERE tv.UniqueIdentifierId = @UniqueId
AND pvp.DateOfEntry < ISNULL(tv.LastStatement,
'1/1/1900')
GROUP BY tv.UniqueIdentifierId ,
tv.SortOrder ,
tv.CompanyId ,
tv.GuarantorName ,
tv.GuarantorZip ,
tv.LastStatement
IF @IncludeByDoctor = 1 --Statement By Doctor
INSERT @bfProcs
SELECT 'BeginningOfRow' = 'BeginningOfRow' ,
'Type' = 2 ,
'GuarantorId' = @pGuarantorId ,
'PatientProfileId' = 0 ,
'PatientVisitId' = 0 ,
'DoctorId' = tv.DoctorId ,
'FacilityId' = 0 ,
'CompanyId' = 0 ,
'GuarantorName_Sort' = tv.GuarantorName ,
'GuarantorZip_Sort' = tv.GuarantorZip ,
'SortOrder' = tv.SortOrder ,
'LastStatementDate' = tv.LastStatement ,
'PatientVisitProcsId' = 0 ,
'TransactionService' = tv.LastStatement ,
'TransactionEntry' = NULL ,
'Code' = '' ,
'CPTCode' = '' ,
'RevenueCode' = '' ,
'Description' = 'Balance Forward' ,
'Modifier1' = '' ,
'Modifier2' = '' ,
'Modifier3' = '' ,
'Modifier4' = '' ,
'PlaceOfService' = '' ,
'TypeOfService' = '' ,
'Units' = 0 ,
'Fee' = 0 ,
'PatientAmount' = ( SELECT SUM(PatTransTotal)
FROM gtPaymentsNCO
WHERE gtPaymentsNCO.DoctorId = tv.DoctorId
AND gtPaymentsNCO.UniqueIdentifierId = @UniqueId
) ,
'InsuranceAmount' = ( SELECT SUM(InsTransTotal)
FROM gtPaymentsNCO
WHERE gtPaymentsNCO.DoctorId = tv.DoctorId
AND gtPaymentsNCO.UniqueIdentifierId = @UniqueId
) ,
'TransactionNote' = '' ,
'TotalFee' = 0 , -- this is only needed for Electronic Statements DK
'ListOrder' = 0
FROM gtVisits tv
INNER JOIN PatientVisitProcs pvp ON tv.PatientVisitId = pvp.PatientVisitId
AND pvp.DateOfEntry <= ISNULL(@TransactionThrough,
pvp.DateOfEntry)
INNER JOIN PatientVisitProcsAgg pvpa ON pvp.PatientVisitProcsId = pvpa.PatientVisitProcsId
WHERE tv.UniqueIdentifierId = @UniqueId
AND pvp.DateOfEntry < ISNULL(tv.LastStatement,
'1/1/1900')
GROUP BY tv.UniqueIdentifierId ,
tv.DoctorId ,
tv.SortOrder ,
tv.GuarantorName ,
tv.GuarantorZip ,
tv.LastStatement
IF @IncludeByDoctor = 2 --Statement By System
INSERT @bfProcs
SELECT 'BeginningOfRow' = 'BeginningOfRow' ,
'Type' = 2 ,
'GuarantorId' = @pGuarantorId ,
'PatientProfileId' = 0 ,
'PatientVisitId' = 0 ,
'DoctorId' = 0 ,
'FacilityId' = 0 ,
'CompanyId' = 0 ,
'GuarantorName_Sort' = tv.GuarantorName ,
'GuarantorZip_Sort' = tv.GuarantorZip ,
'SortOrder' = tv.SortOrder ,
'LastStatementDate' = tv.LastStatement ,
'PatientVisitProcsId' = 0 ,
'TransactionService' = tv.LastStatement ,
'TransactionEntry' = NULL ,
'Code' = '' ,
'CPTCode' = '' ,
'RevenueCode' = '' ,
'Description' = 'Balance Forward' ,
'Modifier1' = '' ,
'Modifier2' = '' ,
'Modifier3' = '' ,
'Modifier4' = '' ,
'PlaceOfService' = '' ,
'TypeOfService' = '' ,
'Units' = 0 ,
'Fee' = 0 ,
'PatientAmount' = ( SELECT SUM(PatTransTotal)
FROM gtPaymentsNCO
WHERE gtPaymentsNCO.UniqueIdentifierId = @UniqueId
) ,
'InsuranceAmount' = ( SELECT SUM(InsTransTotal)
FROM gtPaymentsNCO
WHERE gtPaymentsNCO.UniqueIdentifierId = @UniqueId
) ,
'TransactionNote' = '' ,
'TotalFee' = 0 , -- this is only needed for Electronic Statements DK
'ListOrder' = 0
FROM gtVisits tv
INNER JOIN PatientVisitProcs pvp ON tv.PatientVisitId = pvp.PatientVisitId
AND pvp.DateOfEntry <= ISNULL(@TransactionThrough,
pvp.DateOfEntry)
INNER JOIN PatientVisitProcsAgg pvpa ON pvp.PatientVisitProcsId = pvpa.PatientVisitProcsId
WHERE tv.UniqueIdentifierId = @UniqueId
AND pvp.DateOfEntry < ISNULL(tv.LastStatement,
'1/1/1900')
GROUP BY tv.UniqueIdentifierId ,
tv.SortOrder ,
tv.GuarantorName ,
tv.GuarantorZip ,
tv.LastStatement
IF @IncludeByDoctor = 1 --Statement By Doctor
BEGIN
/* Get Visit Information */
SELECT '0.BeginningOfRow' = 'BeginningOfRow' ,
'1.Type' = 0 ,
'2.GuarantorId' = g.GuarantorId ,
'3.PatientProfileId' = 0 ,
'4.PatientVisitId' = 0 ,
'5.DoctorId' = tv.DoctorId ,
'6.FacilityId' = tv.FacilityId ,
'7.CompanyId' = 0 ,
'8.GuarantorName_Sort' = tv.GuarantorName ,
'9.GuarantorZip_Sort' = tv.GuarantorZip ,
'10.SortOrder' = tv.SortOrder ,
'11.LastStatementDate' = tv.LastStatement ,
'13.GuarantorName' = LTRIM(RTRIM(RTRIM(RTRIM(RTRIM(ISNULL(g.Prefix,
'') + ' '
+ ISNULL(g.First,
'')) + ' '
+ ISNULL(g.Middle,
'')) + ' '
+ ISNULL(g.Last,
'')) + ' '
+ ISNULL(g.Suffix,
''))) ,
'14.GuarantorAddress1' = g.Address1 ,
'15.GuarantorAddress2' = g.Address2 ,
'16.GuarantorCity' = g.City ,
'17.GuarantorState' = g.State ,
'18.GuarantorZip' = g.Zip ,
'19.GuarantorCountry' = g.Country ,
'20.IncludeAging' = @IncludeAging ,
'21.IncludeInsuranceBalanceOnly' = @IncludeInsuranceBalanceOnly ,
'23.IncludeByDoctor' = @IncludeByDoctor ,
'24.IncludeCash' = @IncludeCash ,
'25.IncludePatientWithTransactions' = @IncludePatientWithTransactions ,
'26.IncludePayToAddress' = @IncludePayToAddress ,
'27.IncludeTransactionsAfterClosing_Individual' = @IncludeTransactionsAfterClosing_Individual ,
'28.IncludeTransactionsAfterClosing_Batch' = @IncludeTransactionsAfterClosing_Batch ,
'29.IncludeReferringDoctor' = @IncludeReferringDoctor ,
'30.CreditCard1' = @CreditCard1 ,
'31.CreditCard2' = @CreditCard2 ,
'32.CreditCard3' = @CreditCard3 ,
'33.CreditCard4' = @CreditCard4 ,
'34.CreditCard5' = @CreditCard5 ,
'35.CreditCard6' = @CreditCard6 ,
'39.TransactionThrough' = ISNULL(@TransactionThrough,
GETDATE()) ,
'40.PatientId' = pp.PatientId ,
'41.PatientName' = LTRIM(RTRIM(RTRIM(RTRIM(RTRIM(ISNULL(pp.Prefix,
'') + ' '
+ ISNULL(pp.First,
'')) + ' '
+ ISNULL(pp.Middle,
'')) + ' '
+ ISNULL(pp.Last,
'')) + ' '
+ ISNULL(pp.Suffix,
''))) ,
'42.PatientAddress1' = pp.Address1 ,
'43.PatientAddress2' = pp.Address2 ,
'44.PatientCity' = pp.City ,
'45.PatientState' = pp.State ,
'46.PatientZip' = pp.Zip ,
'47.PatientCountry' = pp.Country ,
'50.Budget' = /*g.Budget,*/ ISNULL(pt.MonthlyPayment,
g.Budget) ,
'51.Visit' = pv.Visit ,
'52.Entered' = pv.Entered ,
'53.TicketNumber' = pv.TicketNumber ,
'55.DoctorName' = LTRIM(RTRIM(RTRIM(RTRIM(RTRIM(ISNULL(d.Prefix,
'') + ' '
+ ISNULL(d.First,
'')) + ' '
+ ISNULL(d.Middle,
'')) + ' '
+ ISNULL(d.Last,
'')) + ' '
+ ISNULL(d.Suffix,
''))) ,
'56.DoctorAddress1' = d.Address1 ,
'57.DoctorAddress2' = d.Address2 ,
'58.DoctorCity' = d.City ,
'59.DoctorState' = d.State ,
'60.DoctorZip' = d.Zip ,
'61.DoctorCountry' = d.Country ,
'62.ReferringDoctor' = LTRIM(RTRIM(RTRIM(RTRIM(RTRIM(ISNULL(rd.Prefix,
'') + ' '
+ ISNULL(rd.First,
'')) + ' '
+ ISNULL(rd.Middle,
'')) + ' '
+ ISNULL(rd.Last,
'')) + ' '
+ ISNULL(rd.Suffix,
''))) ,
'65.Company' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.OrgName
ELSE c.OrgName
END ,
'66.CompanyAddress1' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.Address1
ELSE c.Address1
END ,
'67.CompanyAddress2' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.Address2
ELSE c.Address2
END ,
'68.CompanyCity' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.City
ELSE c.City
END ,
'69.CompanyState' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.State
ELSE c.State
END ,
'70.CompanyZip' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.Zip
ELSE c.Zip
END ,
'71.CompanyCountry' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.Country
ELSE c.Country
END ,
'72.CompanyPhone1' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.Phone1
ELSE c.Phone1
END ,
'75.Facility' = f.OrgName ,
'76.FacilityAddress1' = f.Address1 ,
'77.FacilityAddress2' = f.Address2 ,
'78.FacilityCity' = f.City ,
'79.FacilityState' = f.State ,
'80.FacilityZip' = f.Zip ,
'81.FacilityCountry' = f.Country ,
'82.FacilityPhone1' = f.Phone1 ,
'136.LastPaymentDate' = CASE WHEN @LastPaymentDate IS NULL
THEN 'None'
ELSE CONVERT(VARCHAR(10), @LastPaymentDate, 101)
END ,
'137.LastPayment' = @LastPayment ,
'138.ResourceName' = ( SELECT TOP 1
ISNULL(r.listname,
'')
FROM patientvisitresource pvr
LEFT JOIN doctorfacility r ON pvr.resourceid = r.doctorfacilityid
WHERE pvr.patientvisitid = tv.patientvisitid
)
FROM ( SELECT MIN(PatientVisitId) AS PatientVisitId
FROM gtVisits tv
WHERE tv.UniqueIdentifierId = @UniqueId
AND tv.OnPreviousStatement = 1
GROUP BY DoctorId
) AS dpv
INNER JOIN gtVisits tv ON tv.PatientVisitId = dpv.PatientVisitId
AND tv.UniqueIdentifierId = @UniqueId
INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId
INNER JOIN PatientProfile pp ON tv.PatientProfileId = pp.PatientProfileId
INNER JOIN DoctorFacility d ON tv.DoctorId = d.DoctorFacilityId
INNER JOIN DoctorFacility f ON tv.FacilityId = f.DoctorFacilityId
INNER JOIN DoctorFacility fg ON tv.CompanyId = fg.DoctorFacilityId
LEFT JOIN DoctorFacility rd ON pv.ReferringDoctorId = rd.DoctorFacilityId
INNER JOIN Guarantor g ON g.GuarantorId = @pGuarantorId
/*Add in CPS06 Payment Plan coding JAS - Cys 07/10/2008*/
LEFT JOIN PaymentPlan pt ON pt.GuarantorId = @pGuarantorId
AND ISNULL(pt.Active,
0) <> 0
LEFT JOIN DoctorFacility c ON @SystemCompanyId = c.DoctorFacilityId
WHERE tv.UniqueIdentifierId = @UniqueId
SELECT '0.BeginningOfRow' = 'BeginningOfRow' ,
'1.Type' = 4 ,
'2.GuarantorId' = @pGuarantorId ,
'3.PatientProfileId' = 0 ,
'4.PatientVisitId' = 0 ,
'5.DoctorId' = tv.DoctorId ,
'6.FacilityId' = tv.FacilityId ,
'7.CompanyId' = 0 ,
'8.GuarantorName_Sort' = tv.GuarantorName ,
'9.GuarantorZip_Sort' = tv.GuarantorZip ,
'10.SortOrder' = tv.SortOrder ,
'11.LastStatementDate' = tv.LastStatement ,
'50.Budget' = /*g.Budget,*/ ISNULL(pt.MonthlyPayment,
g.Budget) ,
'90.StatementMessage_0_30' = sc.StatementMessage_0_30 ,
'91.StatementMessage_31_60' = sc.StatementMessage_31_60 ,
'92.StatementMessage_61_90' = sc.StatementMessage_61_90 ,
'93.StatementMessage_91_120' = sc.StatementMessage_91_120 ,
'94.StatementMessage_121' = sc.StatementMessage_121 ,
'95.StatementNotesPrintStatus' = g.StatementNotesPrintStatus ,
'96.StatementNotes' = g.StatementNotes ,
-- CYS 7/24/2007 - eliminate insurance balances so that we can get the FQHC Proc Codes out of the statement.
'97.InsDeposit' = 0 ,
'98.PatDeposit' = ISNULL(ta.PatDeposit, 0) ,
'99.InsBalance0' = 0 ,
'100.PatBalance0' = ISNULL(ta.PatBalance0, 0) ,
'101.InsBalance30' = 0 ,
'102.PatBalance30' = ISNULL(ta.PatBalance30, 0) ,
'103.InsBalance60' = 0 ,
'104.PatBalance60' = ISNULL(ta.PatBalance60, 0) ,
'105.InsBalance90' = 0 ,
'106.PatBalance90' = ISNULL(ta.PatBalance90, 0) ,
'107.InsBalance120' = 0 ,
'108.PatBalance120' = ISNULL(ta.PatBalance120, 0) ,
/*JAS 07/10/2008 CYS Payment Plan Mod for CPS06*/
'150.PaymentPlanMonthlyPayment' = ISNULL(pt.MonthlyPayment,
0) ,
'151.PaymentPlanNextDueDate' = CONVERT(VARCHAR(10), dbo.NextDueDate(pt.PlanStart,
GETDATE()), 101)
FROM ( SELECT MIN(PatientVisitId) AS PatientVisitId
FROM gtVisits tv
WHERE tv.UniqueIdentifierId = @UniqueId
AND tv.OnPreviousStatement = 1
GROUP BY DoctorId
) AS dpv
INNER JOIN gtVisits tv ON tv.PatientVisitId = dpv.PatientVisitId
AND tv.UniqueIdentifierId = @UniqueId
LEFT JOIN StatementsCriteria sc ON sc.StatementsCriteriaId = ISNULL(@pStatementsCriteriaId,
sc.StatementsCriteriaId)
AND ISNULL(sc.GlobalIndicator,
0) = CASE
WHEN @pStatementsCriteriaId IS NULL
THEN 1
ELSE 0
END
INNER JOIN Guarantor g ON g.GuarantorId = @pGuarantorId
/*Add in CPS06 Payment Plan coding JAS - CYS 07/10/2008*/
LEFT JOIN PaymentPlan pt ON pt.GuarantorId = @pGuarantorId
AND ISNULL(pt.Active,
0) <> 0
LEFT JOIN gtAging ta ON tv.DoctorId = ISNULL(ta.DoctorId,
tv.DoctorId)
AND tv.CompanyId = ISNULL(ta.CompanyId,
tv.CompanyId)
AND tv.UniqueIdentifierId = ta.UniqueIdentifierId
WHERE tv.UniqueIdentifierId = @UniqueId
END
IF @IncludeByDoctor = 0 --Statement By Company
BEGIN
/* Get Visit Information */
SELECT '0.BeginningOfRow' = 'BeginningOfRow' ,
'1.Type' = 0 ,
'2.GuarantorId' = g.GuarantorId ,
'3.PatientProfileId' = 0 ,
'4.PatientVisitId' = 0 ,
'5.DoctorId' = 0 ,
'6.FacilityId' = tv.FacilityId ,
'7.CompanyId' = tv.CompanyId ,
'8.GuarantorName_Sort' = tv.GuarantorName ,
'9.GuarantorZip_Sort' = tv.GuarantorZip ,
'10.SortOrder' = tv.SortOrder ,
'11.LastStatementDate' = tv.LastStatement ,
'13.GuarantorName' = LTRIM(RTRIM(RTRIM(RTRIM(RTRIM(ISNULL(g.Prefix,
'') + ' '
+ ISNULL(g.First,
'')) + ' '
+ ISNULL(g.Middle,
'')) + ' '
+ ISNULL(g.Last,
'')) + ' '
+ ISNULL(g.Suffix,
''))) ,
'14.GuarantorAddress1' = g.Address1 ,
'15.GuarantorAddress2' = g.Address2 ,
'16.GuarantorCity' = g.City ,
'17.GuarantorState' = g.State ,
'18.GuarantorZip' = g.Zip ,
'19.GuarantorCountry' = g.Country ,
'20.IncludeAging' = @IncludeAging ,
'21.IncludeInsuranceBalanceOnly' = @IncludeInsuranceBalanceOnly ,
'23.IncludeByDoctor' = @IncludeByDoctor ,
'24.IncludeCash' = @IncludeCash ,
'25.IncludePatientWithTransactions' = @IncludePatientWithTransactions ,
'26.IncludePayToAddress' = @IncludePayToAddress ,
'27.IncludeTransactionsAfterClosing_Individual' = @IncludeTransactionsAfterClosing_Individual ,
'28.IncludeTransactionsAfterClosing_Batch' = @IncludeTransactionsAfterClosing_Batch ,
'29.IncludeReferringDoctor' = @IncludeReferringDoctor ,
'30.CreditCard1' = @CreditCard1 ,
'31.CreditCard2' = @CreditCard2 ,
'32.CreditCard3' = @CreditCard3 ,
'33.CreditCard4' = @CreditCard4 ,
'34.CreditCard5' = @CreditCard5 ,
'35.CreditCard6' = @CreditCard6 ,
'39.TransactionThrough' = ISNULL(@TransactionThrough,
GETDATE()) ,
'40.PatientId' = pp.PatientId ,
'41.PatientName' = LTRIM(RTRIM(RTRIM(RTRIM(RTRIM(ISNULL(pp.Prefix,
'') + ' '
+ ISNULL(pp.First,
'')) + ' '
+ ISNULL(pp.Middle,
'')) + ' '
+ ISNULL(pp.Last,
'')) + ' '
+ ISNULL(pp.Suffix,
''))) ,
'42.PatientAddress1' = pp.Address1 ,
'43.PatientAddress2' = pp.Address2 ,
'44.PatientCity' = pp.City ,
'45.PatientState' = pp.State ,
'46.PatientZip' = pp.Zip ,
'47.PatientCountry' = pp.Country ,
'50.Budget' = /*g.Budget,*/ ISNULL(pt.MonthlyPayment,
g.Budget) ,
'51.Visit' = pv.Visit ,
'52.Entered' = pv.Entered ,
'53.TicketNumber' = pv.TicketNumber ,
'55.DoctorName' = LTRIM(RTRIM(RTRIM(RTRIM(RTRIM(ISNULL(d.Prefix,
'') + ' '
+ ISNULL(d.First,
'')) + ' '
+ ISNULL(d.Middle,
'')) + ' '
+ ISNULL(d.Last,
'')) + ' '
+ ISNULL(d.Suffix,
''))) ,
'56.DoctorAddress1' = d.Address1 ,
'57.DoctorAddress2' = d.Address2 ,
'58.DoctorCity' = d.City ,
'59.DoctorState' = d.State ,
'60.DoctorZip' = d.Zip ,
'61.DoctorCountry' = d.Country ,
'62.ReferringDoctor' = LTRIM(RTRIM(RTRIM(RTRIM(RTRIM(ISNULL(rd.Prefix,
'') + ' '
+ ISNULL(rd.First,
'')) + ' '
+ ISNULL(rd.Middle,
'')) + ' '
+ ISNULL(rd.Last,
'')) + ' '
+ ISNULL(rd.Suffix,
''))) ,
'65.Company' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.OrgName
ELSE c.OrgName
END ,
'66.CompanyAddress1' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.Address1
ELSE c.Address1
END ,
'67.CompanyAddress2' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.Address2
ELSE c.Address2
END ,
'68.CompanyCity' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.City
ELSE c.City
END ,
'69.CompanyState' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.State
ELSE c.State
END ,
'70.CompanyZip' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.Zip
ELSE c.Zip
END ,
'71.CompanyCountry' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.Country
ELSE c.Country
END ,
'72.CompanyPhone1' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.Phone1
ELSE c.Phone1
END ,
'75.Facility' = f.OrgName ,
'76.FacilityAddress1' = f.Address1 ,
'77.FacilityAddress2' = f.Address2 ,
'78.FacilityCity' = f.City ,
'79.FacilityState' = f.State ,
'80.FacilityZip' = f.Zip ,
'81.FacilityCountry' = f.Country ,
'82.FacilityPhone1' = f.Phone1 ,
'136.LastPaymentDate' = CASE WHEN @LastPaymentDate IS NULL
THEN 'None'
ELSE CONVERT(VARCHAR(10), @LastPaymentDate, 101)
END ,
'137.LastPayment' = @LastPayment ,
'138.ResourceName' = ( SELECT TOP 1
ISNULL(r.listname,
'')
FROM patientvisitresource pvr
LEFT JOIN doctorfacility r ON pvr.resourceid = r.doctorfacilityid
WHERE pvr.patientvisitid = tv.patientvisitid
)
FROM ( SELECT MIN(PatientVisitId) AS PatientVisitId
FROM gtVisits tv
WHERE tv.UniqueIdentifierId = @UniqueId
AND tv.OnPreviousStatement = 1
GROUP BY CompanyId
) AS dpv
INNER JOIN gtVisits tv ON tv.PatientVisitId = dpv.PatientVisitId
AND tv.UniqueIdentifierId = @UniqueId
INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId
INNER JOIN PatientProfile pp ON tv.PatientProfileId = pp.PatientProfileId
INNER JOIN DoctorFacility d ON tv.DoctorId = d.DoctorFacilityId
INNER JOIN DoctorFacility f ON tv.FacilityId = f.DoctorFacilityId
INNER JOIN DoctorFacility fg ON tv.CompanyId = fg.DoctorFacilityId
LEFT JOIN DoctorFacility rd ON pv.ReferringDoctorId = rd.DoctorFacilityId
INNER JOIN Guarantor g ON g.GuarantorId = @pGuarantorId
/*Add in CPS06 Payment Plan coding JAS - CYS 07/10/2008*/
LEFT JOIN PaymentPlan pt ON pt.GuarantorId = @pGuarantorId
AND ISNULL(pt.Active,
0) <> 0
LEFT JOIN DoctorFacility c ON @SystemCompanyId = c.DoctorFacilityId
WHERE tv.UniqueIdentifierId = @UniqueId
SELECT '0.BeginningOfRow' = 'BeginningOfRow' ,
'1.Type' = 4 ,
'2.GuarantorId' = @pGuarantorId ,
'3.PatientProfileId' = 0 ,
'4.PatientVisitId' = 0 ,
'5.DoctorId' = 0 ,
'6.FacilityId' = tv.FacilityId ,
'7.CompanyId' = tv.CompanyId ,
'8.GuarantorName_Sort' = tv.GuarantorName ,
'9.GuarantorZip_Sort' = tv.GuarantorZip ,
'10.SortOrder' = tv.SortOrder ,
'11.LastStatementDate' = tv.LastStatement ,
'50.Budget' = /*g.Budget,*/ ISNULL(pt.MonthlyPayment,
g.Budget) ,
'90.StatementMessage_0_30' = sc.StatementMessage_0_30 ,
'91.StatementMessage_31_60' = sc.StatementMessage_31_60 ,
'92.StatementMessage_61_90' = sc.StatementMessage_61_90 ,
'93.StatementMessage_91_120' = sc.StatementMessage_91_120 ,
'94.StatementMessage_121' = sc.StatementMessage_121 ,
'95.StatementNotesPrintStatus' = g.StatementNotesPrintStatus ,
'96.StatementNotes' = g.StatementNotes ,
-- CYS 7/24/2007 - eliminate insurance balances so that we can get the FQHC Proc Codes out of the statement.
'97.InsDeposit' = 0 ,
'98.PatDeposit' = ISNULL(ta.PatDeposit, 0) ,
'99.InsBalance0' = 0 ,
'100.PatBalance0' = ISNULL(ta.PatBalance0, 0) ,
'101.InsBalance30' = 0 ,
'102.PatBalance30' = ISNULL(ta.PatBalance30, 0) ,
'103.InsBalance60' = 0 ,
'104.PatBalance60' = ISNULL(ta.PatBalance60, 0) ,
'105.InsBalance90' = 0 ,
'106.PatBalance90' = ISNULL(ta.PatBalance90, 0) ,
'107.InsBalance120' = 0 ,
'108.PatBalance120' = ISNULL(ta.PatBalance120, 0) ,
/*JAS 07/10/2008 CYS Payment Plan Mod for CPS06*/
'150.PaymentPlanMonthlyPayment' = ISNULL(pt.MonthlyPayment,
0) ,
'151.PaymentPlanNextDueDate' = CONVERT(VARCHAR(10), dbo.NextDueDate(pt.PlanStart,
GETDATE()), 101)
FROM ( SELECT MIN(PatientVisitId) AS PatientVisitId
FROM gtVisits tv
WHERE tv.UniqueIdentifierId = @UniqueId
AND tv.OnPreviousStatement = 1
GROUP BY CompanyId
) AS dpv
INNER JOIN gtVisits tv ON tv.PatientVisitId = dpv.PatientVisitId
AND tv.UniqueIdentifierId = @UniqueId
LEFT JOIN StatementsCriteria sc ON sc.StatementsCriteriaId = ISNULL(@pStatementsCriteriaId,
sc.StatementsCriteriaId)
AND ISNULL(sc.GlobalIndicator,
0) = CASE
WHEN @pStatementsCriteriaId IS NULL
THEN 1
ELSE 0
END
INNER JOIN Guarantor g ON g.GuarantorId = @pGuarantorId
/*Add in CPS06 Payment Plan coding JAS - CYS 07/10/2008*/
LEFT JOIN PaymentPlan pt ON pt.GuarantorId = @pGuarantorId
AND ISNULL(pt.Active,
0) <> 0
LEFT JOIN gtAging ta ON tv.DoctorId = ISNULL(ta.DoctorId,
tv.DoctorId)
AND tv.CompanyId = ISNULL(ta.CompanyId,
tv.CompanyId)
AND tv.UniqueIdentifierId = ta.UniqueIdentifierId
WHERE tv.UniqueIdentifierId = @UniqueId
END
IF @IncludeByDoctor = 2 --Statement By System
BEGIN
/* Get Visit Information */
SELECT '0.BeginningOfRow' = 'BeginningOfRow' ,
'1.Type' = 0 ,
'2.GuarantorId' = g.GuarantorId ,
'3.PatientProfileId' = 0 ,
'4.PatientVisitId' = 0 ,
'5.DoctorId' = 0 ,
'6.FacilityId' = tv.FacilityId ,
'7.CompanyId' = 0 ,
'8.GuarantorName_Sort' = tv.GuarantorName ,
'9.GuarantorZip_Sort' = tv.GuarantorZip ,
'10.SortOrder' = tv.SortOrder ,
'11.LastStatementDate' = tv.LastStatement ,
'13.GuarantorName' = LTRIM(RTRIM(RTRIM(RTRIM(RTRIM(ISNULL(g.Prefix,
'') + ' '
+ ISNULL(g.First,
'')) + ' '
+ ISNULL(g.Middle,
'')) + ' '
+ ISNULL(g.Last,
'')) + ' '
+ ISNULL(g.Suffix,
''))) ,
'14.GuarantorAddress1' = g.Address1 ,
'15.GuarantorAddress2' = g.Address2 ,
'16.GuarantorCity' = g.City ,
'17.GuarantorState' = g.State ,
'18.GuarantorZip' = g.Zip ,
'19.GuarantorCountry' = g.Country ,
'20.IncludeAging' = @IncludeAging ,
'21.IncludeInsuranceBalanceOnly' = @IncludeInsuranceBalanceOnly ,
'23.IncludeByDoctor' = @IncludeByDoctor ,
'24.IncludeCash' = @IncludeCash ,
'25.IncludePatientWithTransactions' = @IncludePatientWithTransactions ,
'26.IncludePayToAddress' = @IncludePayToAddress ,
'27.IncludeTransactionsAfterClosing_Individual' = @IncludeTransactionsAfterClosing_Individual ,
'28.IncludeTransactionsAfterClosing_Batch' = @IncludeTransactionsAfterClosing_Batch ,
'29.IncludeReferringDoctor' = @IncludeReferringDoctor ,
'30.CreditCard1' = @CreditCard1 ,
'31.CreditCard2' = @CreditCard2 ,
'32.CreditCard3' = @CreditCard3 ,
'33.CreditCard4' = @CreditCard4 ,
'34.CreditCard5' = @CreditCard5 ,
'35.CreditCard6' = @CreditCard6 ,
'39.TransactionThrough' = ISNULL(@TransactionThrough,
GETDATE()) ,
'40.PatientId' = pp.PatientId ,
'41.PatientName' = LTRIM(RTRIM(RTRIM(RTRIM(RTRIM(ISNULL(pp.Prefix,
'') + ' '
+ ISNULL(pp.First,
'')) + ' '
+ ISNULL(pp.Middle,
'')) + ' '
+ ISNULL(pp.Last,
'')) + ' '
+ ISNULL(pp.Suffix,
''))) ,
'42.PatientAddress1' = pp.Address1 ,
'43.PatientAddress2' = pp.Address2 ,
'44.PatientCity' = pp.City ,
'45.PatientState' = pp.State ,
'46.PatientZip' = pp.Zip ,
'47.PatientCountry' = pp.Country ,
'50.Budget' = /*g.Budget,*/ ISNULL(pt.MonthlyPayment,
g.Budget) ,
'51.Visit' = pv.Visit ,
'52.Entered' = pv.Entered ,
'53.TicketNumber' = pv.TicketNumber ,
'55.DoctorName' = LTRIM(RTRIM(RTRIM(RTRIM(RTRIM(ISNULL(d.Prefix,
'') + ' '
+ ISNULL(d.First,
'')) + ' '
+ ISNULL(d.Middle,
'')) + ' '
+ ISNULL(d.Last,
'')) + ' '
+ ISNULL(d.Suffix,
''))) ,
'56.DoctorAddress1' = d.Address1 ,
'57.DoctorAddress2' = d.Address2 ,
'58.DoctorCity' = d.City ,
'59.DoctorState' = d.State ,
'60.DoctorZip' = d.Zip ,
'61.DoctorCountry' = d.Country ,
'62.ReferringDoctor' = LTRIM(RTRIM(RTRIM(RTRIM(RTRIM(ISNULL(rd.Prefix,
'') + ' '
+ ISNULL(rd.First,
'')) + ' '
+ ISNULL(rd.Middle,
'')) + ' '
+ ISNULL(rd.Last,
'')) + ' '
+ ISNULL(rd.Suffix,
''))) ,
'65.Company' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.OrgName
ELSE c.OrgName
END ,
'66.CompanyAddress1' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.Address1
ELSE c.Address1
END ,
'67.CompanyAddress2' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.Address2
ELSE c.Address2
END ,
'68.CompanyCity' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.City
ELSE c.City
END ,
'69.CompanyState' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.State
ELSE c.State
END ,
'70.CompanyZip' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.Zip
ELSE c.Zip
END ,
'71.CompanyCountry' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.Country
ELSE c.Country
END ,
'72.CompanyPhone1' = CASE WHEN @IncludeByDoctor <> 2
THEN fg.Phone1
ELSE c.Phone1
END ,
'75.Facility' = f.OrgName ,
'76.FacilityAddress1' = f.Address1 ,
'77.FacilityAddress2' = f.Address2 ,
'78.FacilityCity' = f.City ,
'79.FacilityState' = f.State ,
'80.FacilityZip' = f.Zip ,
'81.FacilityCountry' = f.Country ,
'82.FacilityPhone1' = f.Phone1 ,
'136.LastPaymentDate' = CASE WHEN @LastPaymentDate IS NULL
THEN 'None'
ELSE CONVERT(VARCHAR(10), @LastPaymentDate, 101)
END ,
'137.LastPayment' = @LastPayment ,
'138.ResourceName' = ( SELECT TOP 1
ISNULL(r.listname,
'')
FROM patientvisitresource pvr
LEFT JOIN doctorfacility r ON pvr.resourceid = r.doctorfacilityid
WHERE pvr.patientvisitid = tv.patientvisitid
)
FROM ( SELECT MIN(PatientVisitId) AS PatientVisitId
FROM gtVisits tv
WHERE tv.UniqueIdentifierId = @UniqueId
AND tv.OnPreviousStatement = 1
) AS dpv
INNER JOIN gtVisits tv ON tv.PatientVisitId = dpv.PatientVisitId
AND tv.UniqueIdentifierId = @UniqueId
INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId
INNER JOIN PatientProfile pp ON tv.PatientProfileId = pp.PatientProfileId
INNER JOIN DoctorFacility d ON tv.DoctorId = d.DoctorFacilityId
INNER JOIN DoctorFacility f ON tv.FacilityId = f.DoctorFacilityId
INNER JOIN DoctorFacility fg ON tv.CompanyId = fg.DoctorFacilityId
LEFT JOIN DoctorFacility rd ON pv.ReferringDoctorId = rd.DoctorFacilityId
INNER JOIN Guarantor g ON g.GuarantorId = @pGuarantorId
/*Add in CPS06 Payment Plan coding JAS - CYS 07/10/2008*/
LEFT JOIN PaymentPlan pt ON pt.GuarantorId = @pGuarantorId
AND ISNULL(pt.Active,
0) <> 0
LEFT JOIN DoctorFacility c ON @SystemCompanyId = c.DoctorFacilityId
WHERE tv.UniqueIdentifierId = @UniqueId
SELECT '0.BeginningOfRow' = 'BeginningOfRow' ,
'1.Type' = 4 ,
'2.GuarantorId' = @pGuarantorId ,
'3.PatientProfileId' = 0 ,
'4.PatientVisitId' = 0 ,
'5.DoctorId' = 0 ,
'6.FacilityId' = tv.FacilityId ,
'7.CompanyId' = 0 ,
'8.GuarantorName_Sort' = tv.GuarantorName ,
'9.GuarantorZip_Sort' = tv.GuarantorZip ,
'10.SortOrder' = tv.SortOrder ,
'11.LastStatementDate' = tv.LastStatement ,
'50.Budget' = /*g.Budget,*/ ISNULL(pt.MonthlyPayment,
g.Budget) ,
'90.StatementMessage_0_30' = sc.StatementMessage_0_30 ,
'91.StatementMessage_31_60' = sc.StatementMessage_31_60 ,
'92.StatementMessage_61_90' = sc.StatementMessage_61_90 ,
'93.StatementMessage_91_120' = sc.StatementMessage_91_120 ,
'94.StatementMessage_121' = sc.StatementMessage_121 ,
'95.StatementNotesPrintStatus' = g.StatementNotesPrintStatus ,
'96.StatementNotes' = g.StatementNotes ,
-- CYS 7/24/2007 - eliminate insurance balances so that we can get the FQHC Proc Codes out of the statement.
'97.InsDeposit' = 0 ,
'98.PatDeposit' = ISNULL(ta.PatDeposit, 0) ,
'99.InsBalance0' = 0 ,
'100.PatBalance0' = ISNULL(ta.PatBalance0, 0) ,
'101.InsBalance30' = 0 ,
'102.PatBalance30' = ISNULL(ta.PatBalance30, 0) ,
'103.InsBalance60' = 0 ,
'104.PatBalance60' = ISNULL(ta.PatBalance60, 0) ,
'105.InsBalance90' = 0 ,
'106.PatBalance90' = ISNULL(ta.PatBalance90, 0) ,
'107.InsBalance120' = 0 ,
'108.PatBalance120' = ISNULL(ta.PatBalance120, 0) ,
/*JAS 07/10/2008 CYS Payment Plan Mod for CPS06*/
'150.PaymentPlanMonthlyPayment' = ISNULL(pt.MonthlyPayment,
0) ,
'151.PaymentPlanNextDueDate' = CONVERT(VARCHAR(10), dbo.NextDueDate(pt.PlanStart,
GETDATE()), 101)
FROM ( SELECT MIN(PatientVisitId) AS PatientVisitId
FROM gtVisits tv
WHERE tv.UniqueIdentifierId = @UniqueId
AND tv.OnPreviousStatement = 1
) AS dpv
INNER JOIN gtVisits tv ON tv.PatientVisitId = dpv.PatientVisitId
AND tv.UniqueIdentifierId = @UniqueId
LEFT JOIN StatementsCriteria sc ON sc.StatementsCriteriaId = ISNULL(@pStatementsCriteriaId,
sc.StatementsCriteriaId)
AND ISNULL(sc.GlobalIndicator,
0) = CASE
WHEN @pStatementsCriteriaId IS NULL
THEN 1
ELSE 0
END
INNER JOIN Guarantor g ON g.GuarantorId = @pGuarantorId
/*Add in CPS06 Payment Plan coding JAS - CYS 07/10/2008*/
LEFT JOIN PaymentPlan pt ON pt.GuarantorId = @pGuarantorId
AND ISNULL(pt.Active,
0) <> 0
LEFT JOIN gtAging ta ON tv.DoctorId = ISNULL(ta.DoctorId,
tv.DoctorId)
AND tv.CompanyId = ISNULL(ta.CompanyId,
tv.CompanyId)
AND tv.UniqueIdentifierId = ta.UniqueIdentifierId
WHERE tv.UniqueIdentifierId = @UniqueId
END
END
/*Create Procedure row for balance forward information*/
/*Returns all rows from temporary table, with common naming format for the statement*/
SELECT BeginningOfRow AS '0.BeginningOfRow' ,
Type AS '1.Type' ,
GuarantorId AS '2.GuarantorId' ,
PatientProfileId AS '3.PatientProfileId' ,
PatientVisitId AS '4.PatientVisitId' ,
DoctorId AS '5.DoctorId' ,
FacilityId AS '6.FacilityId' ,
CompanyId AS '7.CompanyId' ,
GuarantorName_Sort AS '8.GuarantorName_Sort' ,
GuarantorZip_Sort AS '9.GuarantorZip_Sort' ,
SortOrder AS '10.SortOrder' ,
LastStatementDate AS '11.LastStatementDate' ,
PatientVisitProcsId AS '111.PatientVisitProcsId' ,
TransactionService AS '118.TransactionService' ,
TransactionEntry AS '119.TransactionEntry' ,
Code AS '113.Code' ,
CPTCode AS '115.CPTCode' ,
RevenueCode AS '116.RevenueCode' ,
Description AS '117.Description' ,
Modifier1 AS '124.Modifier1' ,
Modifier2 AS '125.Modifier2' ,
Modifier3 AS '126.Modifier3' ,
Modifier4 AS '127.Modifier4' ,
PlaceOfService AS '128.PlaceOfService' ,
TypeOfService AS '129.TypeOfService' ,
Units AS '122.Units' ,
Fee AS '123.Fee' ,
PatientAmount AS '120.PatientAmount' ,
InsuranceAmount AS '121.InsuranceAmount' ,
TransactionNote AS '134.TransactionNote' ,
TotalFee AS '135.TotalFee'-- this is only needed for Electronic Statements DK,
FROM @bfProcs
-- CYS 7/24/2007 - Eliminate any FQHC Procedures
WHERE CPTCode NOT IN ( '520', '521', '0521', '0900', '900', 'T1015' )
ORDER BY ListOrder
/* Get the transactions for this visit */
SET FORCEPLAN ON
SELECT '0.BeginningOfRow' = 'BeginningOfRow' ,
'1.Type' = 3 ,
'2.GuarantorId' = @pGuarantorId ,
'3.PatientProfileId' = tv.PatientProfileId ,
'4.PatientVisitId' = tv.PatientVisitId ,
'5.DoctorId' = tv.DoctorId ,
'6.FacilityId' = tv.FacilityId ,
'7.CompanyId' = tv.CompanyId ,
'8.GuarantorName_Sort' = tv.GuarantorName ,
'9.GuarantorZip_Sort' = tv.GuarantorZip ,
'10.SortOrder' = tv.SortOrder ,
'11.LastStatementDate' = tv.LastStatement ,
'112.VisitTransactionId' = vt.VisitTransactionsId ,
'118.TransactionService' = ISNULL(pm.CheckDate, pm.DateOfEntry) ,
'119.TransactionEntry' = pm.DateOfEntry ,
'120.PatientAmount' = CASE WHEN pm.Source = 1 THEN -t.Amount
WHEN t.Action = 'T' THEN t.Amount
ELSE 0
END ,
-- CYS 07/02/2009 Filter out FQHC Payments and Adjustments
'121.InsuranceAmount' = CASE WHEN pm.Source = 2
/*AND ct.Description NOT IN (
'Medicare', 'Medicaid',
'Medicare A' )*/ THEN -t.Amount
WHEN t.Action = 'T'
/*AND ct.Description NOT IN (
'Medicare', 'Medicaid',
'Medicare A' )*/ THEN t.Amount
ELSE 0
END ,
'130.Source' = pm.Source ,
-- '990.PayerOrder' = pvi.OrderForClaims ,
'131.Action' = t.Action ,
'132.ActionType' = CASE WHEN t.Action = 'T'
THEN CASE WHEN pm.Source = 1
THEN 'Transfer from Patient'
ELSE 'Transfer from Insurance'
END
--ELSE at.Description
WHEN pm.Source = 2
AND pvi.OrderForClaims = 1
THEN 'Primary Insurance Payment/Adjustment'
WHEN pm.Source = 2
AND pvi.OrderForClaims = 2
THEN 'Secondary Insurance Payment/Adjustment'
WHEN pm.Source = 2
AND pvi.OrderForClaims = 3
THEN 'Tertiary Insurance Payment/Adjustment'
ELSE 'Insurance Payment/Adjustment'
END ,
--'133.Payer' = pm.PayerName,
'133.Payer' = CASE WHEN pm.Source = 2
AND pm.PayerType = 'insurance'
THEN ic.name
ELSE pm.PayerName
END ,
'134.TransactionNote' = t.Note ,
'140.ShowNoteOnStatement' = t.ShowOnStatement ,
'141.CheckNumber' = CASE WHEN pm.PaymentType = 2
THEN pm.CheckCardNumber
ELSE NULL
END
INTO #data
FROM gtVisits tv
INNER JOIN VisitTransactions vt ON tv.PatientVisitId = vt.PatientVisitId
INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId
AND ( pm.InsuranceTransfer = 0
OR pm.InsuranceTransfer IS NULL
)
AND pm.DateOfEntry <= ISNULL(@TransactionThrough,
pm.DateOfEntry)
INNER JOIN Transactions t ON vt.VisitTransactionsId = t.VisitTransactionsId
AND t.Action <> 'N'
LEFT JOIN MedLists at ON t.ActionTypeMId = at.MedListsId
LEFT JOIN InsuranceCarriers ic ON pm.PayerId = ic.InsuranceCarriersId
LEFT JOIN MedLists ct ON ic.CarrierTypeMID = ct.MedListsID
LEFT JOIN ( SELECT PatientVisitId ,
pvi.OrderForClaims ,
InsuranceCarriersId ,
PatientProfileId
FROM PatientVisitInsurance pvi
INNER JOIN PatientInsurance patins ON pvi.PatientInsuranceId = patins.PatientInsuranceId
) pvi ON pvi.PatientVisitId = tv.patientvisitid
AND pvi.InsuranceCarriersId = ic.insuranceCarriersId
AND pvi.PatientProfileId = tv.PatientProfileId
WHERE t.transactionsid NOT IN (
SELECT TransactionsId
FROM TransactionDistributions td
INNER JOIN PatientVisitProcs p ON td.PatientVisitProcsId = p.PatientVisitProcsId
WHERE p.CPTCode IN ( '520', '521', '524', '525', '0521', '0524',
'0525', '0900', '900', 'T1015' ) )
AND tv.UniqueIdentifierId = @UniqueId
AND pm.DateOfEntry >= CASE WHEN @BalanceForward = 1
THEN ISNULL(tv.LastStatement,
'1/1/1900')
ELSE pm.DateOfEntry
END
SET FORCEPLAN OFF
-- SELECT *
-- FROM #DATA
SELECT
[0.BeginningOfRow] ,
[1.Type] ,
[2.GuarantorId] ,
[3.PatientProfileId] ,
[4.PatientVisitId] ,
[5.DoctorId] ,
[6.FacilityId] ,
[7.CompanyId] ,
[8.GuarantorName_Sort] ,
[9.GuarantorZip_Sort] ,
[10.SortOrder] ,
[11.LastStatementDate] ,
[112.VisitTransactionId] ,
[118.TransactionService] ,
[119.TransactionEntry] ,
[120.PatientAmount] ,
SUM([121.InsuranceAmount]) AS [121.InsuranceAmount] ,
[130.Source] ,
' ' As [131.Action] ,
[132.ActionType] ,
[133.Payer] ,
' ' As [134.TransactionNote] ,
[140.ShowNoteOnStatement] ,
[141.CheckNumber]
FROM #data
GROUP BY
[0.BeginningOfRow] ,
[1.Type] ,
[2.GuarantorId] ,
[3.PatientProfileId] ,
[4.PatientVisitId] ,
[5.DoctorId] ,
[6.FacilityId] ,
[7.CompanyId] ,
[8.GuarantorName_Sort] ,
[9.GuarantorZip_Sort] ,
[10.SortOrder] ,
[11.LastStatementDate] ,
[112.VisitTransactionId] ,
[118.TransactionService] ,
[119.TransactionEntry] ,
[120.PatientAmount] ,
[130.Source] ,
--[131.Action] ,
[132.ActionType] ,
[133.Payer] ,
--[134.TransactionNote] ,
[140.ShowNoteOnStatement] ,
[141.CheckNumber]
/* Get the standard statement messages */
SELECT '0.BeginningOfRow' = 'BeginningOfRow' ,
'1.Type' = 4 ,
'2.GuarantorId' = @pGuarantorId ,
'3.PatientProfileId' = tv.PatientProfileId ,
'4.PatientVisitId' = tv.PatientVisitId ,
'5.DoctorId' = tv.DoctorId ,
'6.FacilityId' = tv.FacilityId ,
'7.CompanyId' = tv.CompanyId ,
'8.GuarantorName_Sort' = tv.GuarantorName ,
'9.GuarantorZip_Sort' = tv.GuarantorZip ,
'10.SortOrder' = tv.SortOrder ,
'11.LastStatementDate' = tv.LastStatement ,
'50.Budget' = /*g.Budget,*/ ISNULL(pt.MonthlyPayment, g.Budget) ,
'90.StatementMessage_0_30' = sc.StatementMessage_0_30 ,
'91.StatementMessage_31_60' = sc.StatementMessage_31_60 ,
'92.StatementMessage_61_90' = sc.StatementMessage_61_90 ,
'93.StatementMessage_91_120' = sc.StatementMessage_91_120 ,
'94.StatementMessage_121' = sc.StatementMessage_121 ,
'95.StatementNotesPrintStatus' = g.StatementNotesPrintStatus ,
'96.StatementNotes' = g.StatementNotes ,
-- CYS 7/24/2007 - eliminate insurance balances so that we can get the FQHC Proc Codes out of the statement.
'97.InsDeposit' = 0 ,
'98.PatDeposit' = ISNULL(ta.PatDeposit, 0) ,
'99.InsBalance0' = 0 ,
'100.PatBalance0' = ISNULL(ta.PatBalance0, 0) ,
'101.InsBalance30' = 0 ,
'102.PatBalance30' = ISNULL(ta.PatBalance30, 0) ,
'103.InsBalance60' = 0 ,
'104.PatBalance60' = ISNULL(ta.PatBalance60, 0) ,
'105.InsBalance90' = 0 ,
'106.PatBalance90' = ISNULL(ta.PatBalance90, 0) ,
'107.InsBalance120' = 0 ,
'108.PatBalance120' = ISNULL(ta.PatBalance120, 0) ,
/*JAS 07/10/2008 CYS Payment Plan Mod for CPS06*/
'150.PaymentPlanMonthlyPayment' = ISNULL(pt.MonthlyPayment, 0) ,
'151.PaymentPlanNextDueDate' = CONVERT(VARCHAR(10), dbo.NextDueDate(pt.PlanStart,
GETDATE()), 101)
FROM gtVisits tv
LEFT JOIN StatementsCriteria sc ON sc.StatementsCriteriaId = ISNULL(@pStatementsCriteriaId,
sc.StatementsCriteriaId)
AND ISNULL(sc.GlobalIndicator,
0) = CASE
WHEN @pStatementsCriteriaId IS NULL
THEN 1
ELSE 0
END
INNER JOIN Guarantor g ON g.GuarantorId = @pGuarantorId
/*Add in CPS06 Payment Plan coding JAS - CYS 07/10/2008*/
LEFT JOIN PaymentPlan pt ON pt.GuarantorId = @pGuarantorId
AND ISNULL(pt.Active, 0) <> 0
LEFT JOIN gtAging ta ON tv.DoctorId = ISNULL(ta.DoctorId,
tv.DoctorId)
AND tv.CompanyId = ISNULL(ta.CompanyId,
tv.CompanyId)
AND tv.UniqueIdentifierId = ta.UniqueIdentifierId
WHERE tv.UniqueIdentifierId = @UniqueId
AND ( @BalanceForward = 0
OR tv.TransactionsSinceLastStatement = 1
)
IF @IncludeFilingHistory <> 0
BEGIN
SELECT '0.BeginningOfRow' = 'BeginningOfRow' ,
'1.Type' = 2 ,
'2.GuarantorId' = @pGuarantorId ,
'3.PatientProfileId' = tv.PatientProfileId ,
'4.PatientVisitId' = tv.PatientVisitId ,
'5.DoctorId' = tv.DoctorId ,
'6.FacilityId' = tv.FacilityId ,
'7.CompanyId' = tv.CompanyId ,
'8.GuarantorName_Sort' = tv.GuarantorName ,
'9.GuarantorZip_Sort' = tv.GuarantorZip ,
'10.SortOrder' = tv.SortOrder ,
'11.LastStatementDate' = tv.LastStatement ,
'117.Description' = 'Filed charges of '
+ CONVERT(VARCHAR, ec.Charges) + ' to ' + ic.Name ,
'118.TransactionService' = ef.FileTransmitted
FROM gtVisits tv
INNER JOIN EDIClaim ec ON tv.PatientVisitId = ec.PatientVisitId
INNER JOIN InsuranceCarriers ic ON ec.InsuranceCarriersId = ic.InsuranceCarriersId
INNER JOIN EDIClaimFile ef ON ec.EDIClaimFileId = ef.EDIClaimFileId
WHERE tv.UniqueIdentifierId = @UniqueId
AND ef.FileTransmitted >= CASE WHEN @BalanceForward = 1
THEN ISNULL(tv.LastStatement,
'1/1/1900')
ELSE ef.FileTransmitted
END
SELECT '0.BeginningOfRow' = 'BeginningOfRow' ,
'1.Type' = 2 ,
'2.GuarantorId' = @pGuarantorId ,
'3.PatientProfileId' = tv.PatientProfileId ,
'4.PatientVisitId' = tv.PatientVisitId ,
'5.DoctorId' = tv.DoctorId ,
'6.FacilityId' = tv.FacilityId ,
'7.CompanyId' = tv.CompanyId ,
'8.GuarantorName_Sort' = tv.GuarantorName ,
'9.GuarantorZip_Sort' = tv.GuarantorZip ,
'10.SortOrder' = tv.SortOrder ,
'11.LastStatementDate' = tv.LastStatement ,
'117.Description' = 'Filed charges of '
+ CONVERT(VARCHAR, pvc.Charges) + ' to ' + pvc.Name ,
'118.TransactionService' = pvc.Created
FROM gtVisits tv
INNER JOIN PatientVisitPaperClaim pvc ON tv.PatientVisitId = pvc.PatientVisitId
WHERE tv.UniqueIdentifierId = @UniqueId
AND pvc.Created >= CASE WHEN @BalanceForward = 1
THEN ISNULL(tv.LastStatement,
'1/1/1900')
ELSE pvc.Created
END
END
DELETE FROM gtVisits
WHERE UniqueIdentifierId = @UniqueId
DELETE FROM gtAging
WHERE UniqueIdentifierId = @UniqueId
DELETE FROM gtPaymentsNCO
WHERE UniqueIdentifierId = @UniqueId
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.