Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL 2005 SubQuery help.

Posted on 2009-07-03
1
Medium Priority
?
257 Views
Last Modified: 2012-05-07
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).
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

Open in new window

0
Comment
Question by:Jeff S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 12

Accepted Solution

by:
kevin_u earned 2000 total points
ID: 24775357
try:

WHERE   (  
t.action = 't'
or
 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' ) )

)


0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question