Solved

SQL 2005 Stored Procedure Assistance Needed - ASAP

Posted on 2009-07-01
9
208 Views
Last Modified: 2012-05-07
My new boss dumped this on me this morning and promised it to a client today. Gotta love that. Anyhow, any help i can get is deeply appreciated! Below, I have pasted the entire Stored Procedure, just in case its needed. The specific section below this is what I need help on specifically. I need to delete or omit any transactions where the payment applies to a set of specific CPTCodes. I tried omitting them via my WHERE clause, but it seems to not care and still reports them back. I added this to my WHERE clause: "AND pvp.CPTCode NOT IN ( '520', '521', '0521', '0900', '900', 'T1015' )". I don't understand why I get my codes back in my return when I am omitting them.

Stored Procedure section I need help on:

/* 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 5/31/2006 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 ,
            '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
                               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
    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
      -- CYS 5/31/2006 Add join for Carrier type
            LEFT JOIN MedLists ct ON ic.CarrierTypeMID = ct.MedListsID
                  LEFT JOIN PatientVisitProcs pvp ON tv.PatientVisitId = pvp.PatientVisitId and vt.PatientVisitId = pvp.PatientVisitId
    WHERE   tv.UniqueIdentifierId = @UniqueId
                  AND pvp.CPTCode NOT IN ( '520', '521', '0521', '0900', '900', 'T1015' )
            AND pm.DateOfEntry >= CASE WHEN @BalanceForward = 1
                                       THEN ISNULL(tv.LastStatement,
                                                   '1/1/1900')
                                       ELSE pm.DateOfEntry
                                  END
   
    SET FORCEPLAN OFF
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
-- Add in CPS06 Payment Plan coding JAS - CYS 07/10/2008
-- CYS 5/31/2006 Filter out FQHC visits use with FQHC Statement report
-- CYS 09/15/2006 Create Items 136: LastPayment 137:LastPaymentDate
-- CYS 09/19/2006 Add 136 and 137 to Statement by Doctor and Statement by System. Correct @TransactionThrough determination for Last Payment
-- MO 12/27/2006 Add 138 for first Resource randomly on visit
 
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 = cusMPMStatement_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 5/31/2006 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 ,
            '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
                               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
    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
	-- CYS 5/31/2006 Add join for Carrier type
            LEFT JOIN MedLists ct ON ic.CarrierTypeMID = ct.MedListsID
			LEFT JOIN PatientVisitProcs pvp ON tv.PatientVisitId = pvp.PatientVisitId and vt.PatientVisitId = pvp.PatientVisitId
    WHERE   tv.UniqueIdentifierId = @UniqueId
			AND pvp.CPTCode NOT IN ( '520', '521', '0521', '0900', '900', 'T1015' )
            AND pm.DateOfEntry >= CASE WHEN @BalanceForward = 1
                                       THEN ISNULL(tv.LastStatement,
                                                   '1/1/1900')
                                       ELSE pm.DateOfEntry
                                  END
    
    SET FORCEPLAN OFF
    
    /* Get the standard statement messages */
    SELECT  '0.BeginningOfRow' = 'BeginningOfRow' ,
            '1.Type' = 4 ,
            '2.GuarantorId' = @pGuarantorId ,
            '3.PatientProfileId' = tv.PatientProfileId ,
            '4.PatientVisitId' = tv.PatientVisitId ,
            '5.DoctorId' = tv.DoctorId ,
            '6.FacilityId' = tv.FacilityId ,
            '7.CompanyId' = tv.CompanyId ,
            '8.GuarantorName_Sort' = tv.GuarantorName ,
            '9.GuarantorZip_Sort' = tv.GuarantorZip ,
            '10.SortOrder' = tv.SortOrder ,
            '11.LastStatementDate' = tv.LastStatement ,
            '50.Budget' = /*g.Budget,*/ ISNULL(pt.MonthlyPayment, g.Budget) ,
            '90.StatementMessage_0_30' = sc.StatementMessage_0_30 ,
            '91.StatementMessage_31_60' = sc.StatementMessage_31_60 ,
            '92.StatementMessage_61_90' = sc.StatementMessage_61_90 ,
            '93.StatementMessage_91_120' = sc.StatementMessage_91_120 ,
            '94.StatementMessage_121' = sc.StatementMessage_121 ,
            '95.StatementNotesPrintStatus' = g.StatementNotesPrintStatus ,
            '96.StatementNotes' = g.StatementNotes ,
    	-- CYS 7/24/2007 - eliminate insurance balances so that we can get the FQHC Proc Codes out of the statement.
            '97.InsDeposit' = 0 ,
            '98.PatDeposit' = ISNULL(ta.PatDeposit, 0) ,
            '99.InsBalance0' = 0 ,
            '100.PatBalance0' = ISNULL(ta.PatBalance0, 0) ,
            '101.InsBalance30' = 0 ,
            '102.PatBalance30' = ISNULL(ta.PatBalance30, 0) ,
            '103.InsBalance60' = 0 ,
            '104.PatBalance60' = ISNULL(ta.PatBalance60, 0) ,
            '105.InsBalance90' = 0 ,
            '106.PatBalance90' = ISNULL(ta.PatBalance90, 0) ,
            '107.InsBalance120' = 0 ,
            '108.PatBalance120' = ISNULL(ta.PatBalance120, 0) ,
    	/*JAS 07/10/2008 CYS Payment Plan Mod for CPS06*/
            '150.PaymentPlanMonthlyPayment' = ISNULL(pt.MonthlyPayment, 0) ,
            '151.PaymentPlanNextDueDate' = CONVERT(VARCHAR(10), dbo.NextDueDate(pt.PlanStart,
                                                              GETDATE()), 101)
    FROM    gtVisits tv
            LEFT JOIN StatementsCriteria sc ON sc.StatementsCriteriaId = ISNULL(@pStatementsCriteriaId,
                                                              sc.StatementsCriteriaId)
                                               AND ISNULL(sc.GlobalIndicator,
                                                          0) = CASE
                                                              WHEN @pStatementsCriteriaId IS NULL
                                                              THEN 1
                                                              ELSE 0
                                                              END
            INNER JOIN Guarantor g ON g.GuarantorId = @pGuarantorId
    	/*Add in CPS06 Payment Plan coding JAS - CYS 07/10/2008*/
            LEFT JOIN PaymentPlan pt ON pt.GuarantorId = @pGuarantorId
                                        AND ISNULL(pt.Active, 0) <> 0
            LEFT JOIN gtAging ta ON tv.DoctorId = ISNULL(ta.DoctorId,
                                                         tv.DoctorId)
                                    AND tv.CompanyId = ISNULL(ta.CompanyId,
                                                              tv.CompanyId)
                                    AND tv.UniqueIdentifierId = ta.UniqueIdentifierId
    WHERE   tv.UniqueIdentifierId = @UniqueId
            AND ( @BalanceForward = 0
                  OR tv.TransactionsSinceLastStatement = 1
                )
    
    
    
    IF @IncludeFilingHistory <> 0 
        BEGIN
            SELECT  '0.BeginningOfRow' = 'BeginningOfRow' ,
                    '1.Type' = 2 ,
                    '2.GuarantorId' = @pGuarantorId ,
                    '3.PatientProfileId' = tv.PatientProfileId ,
                    '4.PatientVisitId' = tv.PatientVisitId ,
                    '5.DoctorId' = tv.DoctorId ,
                    '6.FacilityId' = tv.FacilityId ,
                    '7.CompanyId' = tv.CompanyId ,
                    '8.GuarantorName_Sort' = tv.GuarantorName ,
                    '9.GuarantorZip_Sort' = tv.GuarantorZip ,
                    '10.SortOrder' = tv.SortOrder ,
                    '11.LastStatementDate' = tv.LastStatement ,
                    '117.Description' = 'Filed charges of '
                    + CONVERT(VARCHAR, ec.Charges) + ' to ' + ic.Name ,
                    '118.TransactionService' = ef.FileTransmitted
            FROM    gtVisits tv
                    INNER JOIN EDIClaim ec ON tv.PatientVisitId = ec.PatientVisitId
                    INNER JOIN InsuranceCarriers ic ON ec.InsuranceCarriersId = ic.InsuranceCarriersId
                    INNER JOIN EDIClaimFile ef ON ec.EDIClaimFileId = ef.EDIClaimFileId
            WHERE   tv.UniqueIdentifierId = @UniqueId
                    AND ef.FileTransmitted >= CASE WHEN @BalanceForward = 1
                                                   THEN ISNULL(tv.LastStatement,
                                                              '1/1/1900')
                                                   ELSE ef.FileTransmitted
                                              END
    
            SELECT  '0.BeginningOfRow' = 'BeginningOfRow' ,
                    '1.Type' = 2 ,
                    '2.GuarantorId' = @pGuarantorId ,
                    '3.PatientProfileId' = tv.PatientProfileId ,
                    '4.PatientVisitId' = tv.PatientVisitId ,
                    '5.DoctorId' = tv.DoctorId ,
                    '6.FacilityId' = tv.FacilityId ,
                    '7.CompanyId' = tv.CompanyId ,
                    '8.GuarantorName_Sort' = tv.GuarantorName ,
                    '9.GuarantorZip_Sort' = tv.GuarantorZip ,
                    '10.SortOrder' = tv.SortOrder ,
                    '11.LastStatementDate' = tv.LastStatement ,
                    '117.Description' = 'Filed charges of '
                    + CONVERT(VARCHAR, pvc.Charges) + ' to ' + pvc.Name ,
                    '118.TransactionService' = pvc.Created
            FROM    gtVisits tv
                    INNER JOIN PatientVisitPaperClaim pvc ON tv.PatientVisitId = pvc.PatientVisitId
            WHERE   tv.UniqueIdentifierId = @UniqueId
                    AND pvc.Created >= CASE WHEN @BalanceForward = 1
                                            THEN ISNULL(tv.LastStatement,
                                                        '1/1/1900')
                                            ELSE pvc.Created
                                       END
        END
    
    DELETE  FROM gtVisits
    WHERE   UniqueIdentifierId = @UniqueId
    DELETE  FROM gtAging
    WHERE   UniqueIdentifierId = @UniqueId
    DELETE  FROM gtPaymentsNCO
    WHERE   UniqueIdentifierId = @UniqueId
GO
 
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Open in new window

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

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

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 24754694
it is because of LEFT OUTER JOIN  - -that will give you all
 
try
MedLists ct ON ic.CarrierTypeMID = ct.MedListsID
                  INNER JOIN PatientVisitProcs pvp
 
0
 
LVL 7

Author Comment

by:Jeff S
ID: 24754712
I tried the INNER JOIN and still got back the results in my data set. Im stumped.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 24754857
I do not see  in your select output CPTCode
How do you know it was selected?
did you get data from the query below?
select * from PatientVisitProcs pvp
    WHERE  
                  AND pvp.CPTCode  IN ( '520', '521', '0521', '0900', '900', 'T1015' )
 
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 43

Expert Comment

by:Eugene Z
ID: 24754860
oops

select * from PatientVisitProcs pvp
    WHERE  
                pvp.CPTCode  IN ( '520', '521', '0521', '0900', '900', 'T1015' )
 
0
 
LVL 7

Author Comment

by:Jeff S
ID: 24754885
when I run

SELECT *
FROM PatientVisitProcs pvp
WHERE pvp.CPTCode IN ( '520', '521', '0521', '0900', '900', 'T1015' )
I do get results. I need to omit these in my Stored Procedure.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 24755005
when you run the query below
how do you know that  a result has included the
pvp.CPTCode    -'520', '521', '0521', '0900', '900', 'T1015'

    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 5/31/2006 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 ,
            '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
                               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
    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
      -- CYS 5/31/2006 Add join for Carrier type
            LEFT JOIN MedLists ct ON ic.CarrierTypeMID = ct.MedListsID
                  INNER JOIN PatientVisitProcs pvp ON tv.PatientVisitId = pvp.PatientVisitId and vt.PatientVisitId = pvp.PatientVisitId
    WHERE   tv.UniqueIdentifierId = @UniqueId
                  AND pvp.CPTCode NOT IN ( '520', '521', '0521', '0900', '900', 'T1015' )
            AND pm.DateOfEntry >= CASE WHEN @BalanceForward = 1
                                       THEN ISNULL(tv.LastStatement,
                                                   '1/1/1900')
                                       ELSE pm.DateOfEntry
                                  END

Open in new window

0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 500 total points
ID: 24755039
also search your proc (code snippet)
for pvp.CPTCode
there is a query that does not have restriction
/* Get the procedures for this visit */
--
also what values are you using for the proc

[cusMPMStatementFQHC_Fargo]
for variables
@pGuarantorId INT ,
@pStatementsCriteriaId
0
 
LVL 7

Author Closing Comment

by:Jeff S
ID: 31598782
I fixed it on my own, but thanks for some tips and help. Cheers!
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 24756818
you are very welcome!
what was it?
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

688 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