Solved

SQL 2005 SubQuery help.

Posted on 2009-07-03
1
242 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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In this article I will describe the Copy Database Wizard 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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now