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
Solved

SQL 2005 SubQuery help.

Posted on 2009-07-03
1
248 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
1 Comment
 
LVL 12

Accepted Solution

by:
kevin_u earned 500 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

860 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