Solved

SQL Query Help - Stored Procedure

Posted on 2009-07-13
4
186 Views
Last Modified: 2012-05-07
I need help on the SQL Query below.

What I need this query to do is look at the Doctor on the visit along with what the current carrier is and then look at the DFIDs table match them up and pull the Fee Schedule tied with the Insurance Carrier from the Visit up with the matching record is on the DFIDs table - then pull the appropriate Revenue Code from the Fee Schedule tied to the DFIDs table.

I am going cross eyed just writing this one.
SELECT

    pvp.CPTCode ,

    pvp.RevenueCode ,

    fs.FeeScheduleName

FROM

    PatientVisitProcs pvp

    INNER JOIN FeeSchedule fs ON pvp.FeeScheduleId = fs.FeeScheduleId

    INNER JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId

    INNER JOIN DFIds

    INNER JOIN DoctorFacility df ON DFIds.DoctorFacilityId = df.DoctorFacilityId ON pv.DoctorId = df.DoctorFacilityId

    INNER JOIN InsuranceCarriers ic ON pv.CurrentInsuranceCarriersId = ic.InsuranceCarriersId

                                    AND DFIds.InsuranceCarriersId = ic.InsuranceCarriersId

WHERE

    ( pvp.PatientVisitId = 49581 )

Open in new window

0
Comment
Question by:Jeff S
  • 2
  • 2
4 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 24844931
Something like this?
SELECT

    pvp.CPTCode ,

    pvp.RevenueCode ,

    fs.FeeScheduleName

--,      whatever column you need to add

FROM

    PatientVisitProcs pvp

    INNER JOIN FeeSchedule fs ON pvp.FeeScheduleId = fs.FeeScheduleId

    INNER JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId

    INNER JOIN DFIds ON pv.DoctorId = DFIds.DoctorFacilityId

    INNER JOIN DoctorFacility df ON DFIds.DoctorFacilityId = df.DoctorFacilityId 

    INNER JOIN InsuranceCarriers ic ON pv.CurrentInsuranceCarriersId = ic.InsuranceCarriersId

                                    AND DFIds.InsuranceCarriersId = ic.InsuranceCarriersId
 

WHERE   ( pvp.PatientVisitId = 49581 )

Open in new window

0
 
LVL 7

Author Comment

by:Jeff S
ID: 24845811
ralmada -
When I write this SQL Query, I get the results I expect to see in my RevenueCode field.

SELECT
p.CPTCode ,
p.Code ,
pfs.RevenueCode AS fsRevenueCode,
fs.FeeScheduleName
FROM
(Procedures p CROSS JOIN FeeSchedule fs)
LEFT JOIN ProceduresFeeSchedule pfs ON p.ProceduresId = pfs.ProceduresId AND fs.FeeScheduleId = pfs.FeeScheduleId
WHERE
( p.CPTCode = '99214' )
AND fs.FeeScheduleName Like '%Medicaid FQ 2009'
I've attached my full query below. The section this SP I need help with is this section:
 
SELECT
UniqueIdentifierId = @UniqueIdentifierId ,
Output = NULL ,
PatientVisitProcsId = pvp.PatientVisitProcsId ,
ListOrder = pvp.ListOrder ,
ServiceDate = pvp.DateOfServiceFrom ,
CPTCode = pvp.CPTCode + CASE WHEN Modifier1MId IS NULL THEN ''
WHEN Modifier1MId IS NOT NULL
AND Modifier2MId IS NULL THEN ml1.Code
WHEN Modifier1MId IS NOT NULL
AND Modifier2MId IS NOT NULL THEN ml1.Code + ml2.Code
END ,
RevenueCode = pvp.RevenueCode ,
Charge = pvp.TotalFee ,
Units = pvp.Units ,
Description = CONVERT(VARCHAR(75) , pvp.Description) ,
NonCoveredAmount = pvp.NonCoveredAmount
FROM
PatientVisitProcs pvp
LEFT JOIN Medlists ml1 ON pvp.Modifier1MId = ml1.MedlistsId
LEFT JOIN Medlists ml2 ON pvp.Modifier2MId = ml2.MedlistsId
WHERE
pvp.PatientVisitId = @pPatientVisitId
AND ISNULL(pvp.FileInsurance , 0) <> 0
-- WJY 4/07 Filter out FQHC Code
AND pvp.CPTCode <> ISNULL(@FQHCCode , '')
-- END WJY 4/07
ORDER BY
pvp.ListOrder
Again, what I need to do is look at the Insurance Carrier on Visit along with the Doctor and then take the applicable Fee Schedule and pull the Revenue Code from that applicable Procedure Fee schedule.

USE [CentricityPS]

GO

/****** Object:  StoredProcedure [dbo].[cusMPMUB92ExFQHC_WYMC]    Script Date: 07/13/2009 18:46:56 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO
 

-- JAS 03/04/08 - modify to fix page break

-- WJY 4/07 FQHC Addins

-- sb 10/12/2006 for NPI

-- 04/15/2004 Changed Originalreference number to pull from pvi.ICN. BV

-- 04/23/2003 BinuV: Suppress notes on the remarks field on UB92ex when pvf.PrintOnPaperClaims <> 1  and display only the ins carrier address. Fix to OFI #17060

/****** Object:  Stored Procedure dbo.MPMUB92Ex    Script Date: 1/16/2003 3:25:55 PM ******/
 
 

-- 01/16/2003 KC Changed AcceptAssignment to AcceptAssignmentMId
 
 

ALTER PROCEDURE [dbo].[cusMPMUB92ExFQHC_WYMC]

      @pPatientVisitId INT ,

      @pDummy INT = NULL ,

      @pECSFileId INT = NULL ,

      @pSkipProc INT = 0

AS 

      BEGIN

            SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --to eliminate connection blocks.

            SET NOCOUNT ON
 

/*Variables*/
 

-- @pDummy is a place holder where DMEOnly used to be passed in.  KC
 
 

            DECLARE @UniqueIdentifierId UNIQUEIDENTIFIER

            DECLARE @InsuranceCarriersId INT

            DECLARE @PatientInsuranceId INT

            DECLARE @NumProcedureRows SMALLINT

            DECLARE @ProceduresOutput SMALLINT

            DECLARE @LinesOutput SMALLINT

            DECLARE @Counter SMALLINT

            DECLARE @FileVersion VARCHAR(3)

            DECLARE @PatientProfileId INT

            DECLARE @PatientVisitId INT

            DECLARE @PatientSameAsGuarantor SMALLINT

            DECLARE @DBName VARCHAR(255)

            DECLARE @PriorPayments MONEY

            DECLARE @TotalCharge MONEY

            DECLARE @RunningTotalCharge MONEY

            DECLARE @TotalUnits FLOAT

            DECLARE @TotalNonCoveredCharge MONEY

            DECLARE @DoctorId INT

            DECLARE @SupervisingPhysicianId INT

            DECLARE @FacilityId INT

            DECLARE @CompanyId INT

            DECLARE @ReferringId INT

            DECLARE @AttendingId INT

            DECLARE @AdmittingId INT

            DECLARE @OperatingId INT

            DECLARE @SupervisingId INT

            DECLARE @FilesFacilityFees INT

            DECLARE @InsuredSameAsPatient SMALLINT

            DECLARE @InsuredSameAsGuarantor SMALLINT

            DECLARE @CurrentInsuranceCarriersId INT

            DECLARE

                    @FederalTaxId VARCHAR(25) ,

                    @FederalTaxIdType VARCHAR(3) ,

                    @FileAsGroup SMALLINT ,

                    @Id_Facility VARCHAR(25) ,

                    @Id_PIN VARCHAR(25) ,

                    @Id_PINGRP VARCHAR(25) ,

                    @Id_GRP VARCHAR(25) ,

                    @Id_EMCProv VARCHAR(25) ,

                    @Id_CLIA VARCHAR(25) ,

                    @Id_Referring VARCHAR(25) ,

                    @Id_RefOffice VARCHAR(25) ,

                    @Id_ProviderType VARCHAR(25) ,

                    @PlaceOfServiceMId INT ,

                    @Id_Office VARCHAR(25) ,

                    @Id_Mammography VARCHAR(25) ,

                    @pld_Id_PINTypeANSI VARCHAR(5) ,

                    @pld_Id_FacilityTypeAnsi VARCHAR(5) ,

                    @pld_Id_GRPTypeAnsi VARCHAR(5) ,

                    @pld_Id_EMCProvTypeAnsi VARCHAR(5) ,

                    @pld_Id_ProviderTypeTypeAnsi VARCHAR(5) ,

                    @pld_Id_CLIATypeAnsi VARCHAR(5) ,

                    @pld_Id_MammographyTypeAnsi VARCHAR(5) ,

                    @pld_Id_ReferringTypeAnsi VARCHAR(5) ,

                    @pldType_Facility VARCHAR(5) ,

                    @pldType_Doc VARCHAR(5) ,

                    @pldAddressSameAsCompany VARCHAR(5) ,

                    @NPI VARCHAR(80)
 

            DECLARE @unused VARCHAR(30)

            DECLARE @LogonID VARCHAR(30)

            DECLARE @AttendingFileAsGroup SMALLINT

            DECLARE @InsuranceGroupId INT

            DECLARE @IsHCFAPlanId SMALLINT

            DECLARE @HCFAPlanId VARCHAR(25)

            DECLARE @NPI_Provider VARCHAR(80)

            DECLARE @NPI_Facility VARCHAR(80)

            DECLARE @IsAttendingDocRefPhy SMALLINT

-- JAS 03/04/08 

            DECLARE @PageBreak SMALLINT

            SELECT

                @PageBreak = 0

            DECLARE @TotalPagesPerClaim INT

            SELECT

                @TotalPagesPerClaim = 0

            DECLARE @TotalProceduresPerClaim NUMERIC
 

-- WJY 4/07 FQHC COdes and Balance Due for Secondary

            DECLARE

                    @FQHCBalance MONEY ,

                    @CarrierType VARCHAR(50)

--END WJY 4/07 FQHC Variable
 

            SELECT

                @DBName = DB_NAME()

            SELECT

                @UniqueIdentifierId = NEWID()

            SELECT

                @FileVersion = '000'

            SELECT

                @NumProcedureRows = 22

            SELECT

                @ProceduresOutput = 0

            SELECT

                @LinesOutput = 0

            SELECT

                @Counter = 0

            SELECT

                @TotalCharge = 0

            SELECT

                @RunningTotalCharge = 0

            SELECT

                @TotalNonCoveredCharge = 0

            SELECT

                @TotalUnits = 0

            SELECT

                @PatientProfileId = pv.PatientProfileId ,

                @PatientVisitId = pv.PatientVisitId ,

                @DoctorId = pv.DoctorId ,

                @SupervisingPhysicianId = pv.SupervisingDoctorId ,

                @FacilityId = pv.FacilityId ,

                @CompanyId = pv.CompanyId ,

                @ReferringId = pv.ReferringDoctorId ,

	-- WJY 04/07 If Attending Doctor is NULL, use DoctorID

                @AttendingId = ISNULL(pv.AttendingDoctorId , pv.DoctorID) ,

	-- END WJY 4/07

                @AdmittingId = pv.AdmittingDoctorId ,

                @OperatingId = pv.OperatingDoctorId ,

                @SupervisingId = pv.SupervisingDoctorId ,

                @FilesFacilityFees = ISNULL(df.FileFacilityFees , 0) ,

                @PatientSameAsGuarantor = ISNULL(pp.PatientSameAsGuarantor , 0) ,

                @CurrentInsuranceCarriersId = pv.CurrentInsuranceCarriersId

            FROM

                PatientVisit pv ,

                PatientProfile pp ,

                DoctorFacility df

            WHERE

                PatientVisitId = @pPatientVisitId

                AND pv.PatientProfileId = pp.PatientProfileId

                AND pv.CompanyId = df.DoctorFacilityId
 

            SELECT

                @IsAttendingDocRefPhy = CASE df.Type

                                          WHEN 3 THEN 1

                                          ELSE 0

                                        END

            FROM

                DoctorFacility df

            WHERE

                df.DoctorFacilityId = @AttendingId
 
 

            SELECT

                @LogonID = dbo.GetLogonID()
 

            INSERT INTO

                PatientInfoLog

                (

                  PatientProfileId ,

                  PatientVisitId ,

                  ActionType ,

                  Created ,

                  CreatedBy ,

                  LastModified ,

                  LastModifiedBy

                )

            VALUES

                (

                  @PatientProfileId ,

                  @PatientVisitId ,

                  6 ,

                  GETDATE() ,

                  @LogonID ,

                  GETDATE() ,

                  @LogonID

                )
 

-- WJY 04/07 

/* Determine the carrier type */

            SELECT

                @CarrierType = ml.FunctionName

            FROM

                InsuranceCarriers ic

                LEFT JOIN Medlists ml ON ic.CarrierTypeMId = ml.MedlistsId

            WHERE

                ic.InsuranceCarriersId = @CurrentInsuranceCarriersId 
 

/* Need to see if FQHC Code is in here, get the copay amount and delete the code from the gtProcedures table */

/* To arrive at the FQHCBalance, we need to take 25% of the net FQHC Payment rate as it represents 80% of the Gross Encounter rate */            

            DECLARE

                    @FQHCCode VARCHAR(10) ,

                    @FQHCFee MONEY

            SELECT

                @FQHCCode = CPTCode ,

                @FQHCFee = TotalFee

            FROM

                PatientVisitProcs

            WHERE

                CPTCode IN ( '521' , '0521' , '520' , '0520' , '522' , '0522' , '529' , '0529' , 'MA' )

                AND PatientVisitID = @pPatientVisitID
 

            IF @FQHCCode IS NOT NULL 

               BEGIN

                     SELECT

                        @FQHCBalance = @FQHCFee * .25

               END

            ELSE 

               BEGIN

                     SELECT

                        @FQHCBalance = 0.00

               END

-- END WJY 4/07
 
 

/*Put the procedures in a temporary table */

            INSERT

                gtProcedures

                (

                  UniqueIdentifierId ,

                  Output ,

                  PatientVisitProcsId ,

                  ListOrder ,

                  DateOfServiceFrom ,

                  CPTCode ,

                  RevenueCode ,

                  Charge ,

                  Units ,

                  Description ,

                  NonCoveredAmount

		    )

                SELECT

                    UniqueIdentifierId = @UniqueIdentifierId ,

                    Output = NULL ,

                    PatientVisitProcsId = pvp.PatientVisitProcsId ,

                    ListOrder = pvp.ListOrder ,

                    ServiceDate = pvp.DateOfServiceFrom ,

                    CPTCode = pvp.CPTCode + CASE WHEN Modifier1MId IS NULL THEN ''

                                                 WHEN Modifier1MId IS NOT NULL

                                                      AND Modifier2MId IS NULL THEN ml1.Code

                                                 WHEN Modifier1MId IS NOT NULL

                                                      AND Modifier2MId IS NOT NULL THEN ml1.Code + ml2.Code

                                            END ,

                    RevenueCode = pvp.RevenueCode ,

                    Charge = pvp.TotalFee ,

                    Units = pvp.Units ,

                    Description = CONVERT(VARCHAR(75) , pvp.Description) ,

                    NonCoveredAmount = pvp.NonCoveredAmount

                FROM

                    PatientVisitProcs pvp

                    LEFT JOIN Medlists ml1 ON pvp.Modifier1MId = ml1.MedlistsId

                    LEFT JOIN Medlists ml2 ON pvp.Modifier2MId = ml2.MedlistsId

                WHERE

                    pvp.PatientVisitId = @pPatientVisitId

                    AND ISNULL(pvp.FileInsurance , 0) <> 0 

-- WJY 4/07 Filter out FQHC Code

                    AND pvp.CPTCode <> ISNULL(@FQHCCode , '')

-- END WJY 4/07

                ORDER BY

                    pvp.ListOrder
 

            IF EXISTS ( SELECT

                            CPTCode

                        FROM

                            gtProcedures

                        WHERE

                            CPTCode = 'T1015' ) 

               BEGIN	

                     SELECT

                        @TotalCharge = SUM(Charge)

                     FROM

                        gtProcedures

                     WHERE

                        CPTCode <> 'T1015'

                     UPDATE

                        gtProcedures

                     SET

                        Charge = @TotalCharge

                     WHERE

                        CPTCode = 'T1015'

--                      DELETE FROM

--                        gtProcedures

--                      WHERE

--                        CPTCode <> 'T1015'

                     UPDATE

                        gtProcedures

                     SET

                        Charge = '0.00'

                     WHERE

                        CPTCode <> 'T1015'

                     SELECT

                        @TotalCharge = 0

               END
 

            DECLARE @PatientVisitProcsId INT

            DECLARE c CURSOR STATIC FORWARD_ONLY LOCAL

                    FOR SELECT

                            PatientVisitProcsId

                        FROM

                            gtProcedures

                        WHERE

                            UniqueIdentifierId = @UniqueIdentifierId

                        ORDER BY

                            ListOrder

            OPEN c

            FETCH NEXT FROM c INTO @PatientVisitProcsId

            WHILE ( @@fetch_status <> -1 )

                  BEGIN

                        SELECT

                            @Counter = @Counter + 1

                        IF ( @Counter > @pSkipProc ) 

                           BEGIN

                                 IF ( @LinesOutput < @NumProcedureRows ) 

                                    BEGIN

                                          SELECT

                                            @LinesOutput = @LinesOutput + 1	

                                          SELECT

                                            @ProceduresOutput = @ProceduresOutput + 1

			

			/* Mark that we want to output this procedure */

                                          UPDATE

                                            gtProcedures

                                          SET

                                            Output = @LinesOutput

                                          WHERE

                                            PatientVisitProcsId = @PatientVisitProcsId

                                            AND UniqueIdentifierId = @UniqueIdentifierId
 

			/* Keep various counters for the procedures we are going to output */

                                          SELECT

                                            @TotalCharge = @TotalCharge + CONVERT(MONEY , Charge) ,

                                            @TotalUnits = @TotalUnits + Units ,

                                            @TotalNonCoveredCharge = @TotalNonCoveredCharge + CONVERT(MONEY , NonCoveredAmount)

                                          FROM

                                            gtProcedures p

                                          WHERE

                                            p.PatientVisitProcsId = @PatientVisitProcsId

                                            AND UniqueIdentifierId = @UniqueIdentifierId

                                    END

                           END

                        FETCH NEXT FROM c INTO @PatientVisitProcsId

                  END

            DEALLOCATE c

            IF ( @ProceduresOutput > 0 ) 

               BEGIN

-- JAS 03/04/08

                     SET @pDummy = ISNULL(@pDummy , 0)

                     SELECT

                        @PageBreak = @PageBreak + @pDummy + 1

                     SELECT

                        '0.BeginningOfClaim' = 'BeginningOfClaim'

		

                     SELECT

                        '1.ServerVersion' = LTRIM(STR(OtherFloat , 2 , 2)) + '.' + CONVERT(VARCHAR , OtherLong)

                     FROM

                        Medlists

                     WHERE

                        TableName = 'Version String'

                        AND Description = 'Server'

                     SELECT

                        '2.ClientVersion' = LTRIM(STR(OtherFloat , 2 , 2)) + '.' + CONVERT(VARCHAR , OtherLong)

                     FROM

                        Medlists

                     WHERE

                        TableName = 'Version String'

                        AND Description = 'Client'

	

                     SELECT

                        '3.FileVersion' = @FileVersion ,

                        '4.FileCreationDate' = GETDATE()

                     SELECT

                        '5.PayToName' = UPPER(OrgName) ,

                        '6.PayToAddress1' = UPPER(ISNULL(Address1 , '')) ,

                        '7.PayToAddress2' = UPPER(ISNULL(Address2 , '')) ,

                        '8.PayToCity' = UPPER(ISNULL(City , '')) ,

                        '9.PayToState' = UPPER(ISNULL(State , '')) ,

                        '10.PayToZip' = ISNULL(SUBSTRING(Zip , 1 , 5) + ' ' + SUBSTRING(Zip , 7 , 4) , '') ,

                        '11.PayToCountry' = UPPER(Country) ,

                        '12.PayToPhone' = Phone1

                     FROM

                        DoctorFacility

                     WHERE

                        DoctorFacilityId = @CompanyId

	

                     SELECT

                        '15.PatientControlNumber' = UPPER(TicketNumber)

                     FROM

                        PatientVisit

                     WHERE

                        PatientVisitId = @pPatientVisitId
 

                     SELECT

                        '16.PatientName' = CASE WHEN ( pp.Suffix ) IS NOT NULL

                                                THEN UPPER(ISNULL(pp.Last , '') + ' ' + ISNULL(pp.Suffix , '') + ', ' + ISNULL(pp.First , '')

                                                           + CASE WHEN ( pp.Middle ) IS NOT NULL THEN ', ' + SUBSTRING(ISNULL(pp.Middle , '') , 1 , 1)

                                                                  ELSE ''

                                                             END)

                                                ELSE UPPER(ISNULL(pp.Last , '') + ', ' + ISNULL(pp.First , '') + --', ' + substring(isnull(pp.Middle,''),1,1))

					CASE WHEN ( pp.Middle ) IS NOT NULL THEN ', ' + SUBSTRING(ISNULL(pp.Middle , '') , 1 , 1)

                                   ELSE ''

                              END)

                                           END ,

                        '17.PatientAddress1' = UPPER(ISNULL(pp.Address1 , '')) ,

                        '18.PatientAddress2' = UPPER(ISNULL(pp.Address2 , '')) ,

                        '19.PatientCity' = UPPER(ISNULL(pp.City , '')) ,

                        '20.PatientState' = UPPER(ISNULL(pp.State , '')) ,

                        '21.PatientZip' = ISNULL(SUBSTRING(pp.Zip , 1 , 5) + ' ' + SUBSTRING(pp.Zip , 7 , 4) , '') ,

                        '22.PatientCountry' = pp.Country ,

                        '23.PatientDateOfBirth' = pp.Birthdate ,

                        '24.PatientSex' = UPPER(pp.Sex) ,

                        '25.MaritalStatus' = UPPER(ml.Code) ,

                        '26.MedicalRecordNumber' = UPPER(pp.MedicalRecordNumber)

                     FROM

                        PatientProfile pp

                        LEFT JOIN Medlists ml ON pp.MaritalStatusMId = ml.MedlistsId

                     WHERE

                        pp.PatientProfileId = @PatientProfileId 
 
 

                     SELECT

                        '30.TypeOfBill' = UPPER(pvf.TypeOfBill)

                     FROM

                        PatientVisitFiling pvf

                     WHERE

                        pvf.PatientVisitId = @pPatientVisitId
 

                     SELECT

                        '50.GuarantorName' = UPPER(ISNULL(g.Last , '') + ', ' + ISNULL(g.First , '') + ' ' + SUBSTRING(ISNULL(g.Middle , '') , 1 , 1)) ,

                        '51.GuarantorAddress1' = UPPER(ISNULL(g.Address1 , '')) ,

                        '52.GuarantorAddress2' = UPPER(ISNULL(g.Address2 , '')) ,

                        '53.GuarantorCity' = UPPER(ISNULL(g.City , '')) ,

                        '54.GuarantorState' = UPPER(ISNULL(g.State , '')) ,

                        '55.GuarantorZip' = ISNULL(SUBSTRING(g.Zip , 1 , 5) + ' ' + SUBSTRING(g.Zip , 7 , 4) , '') ,

                        '56.GuarantorCountry' = UPPER(g.Country)

                     FROM

                        Guarantor g ,

                        PatientProfile pp

                     WHERE

                        g.GuarantorId = pp.GuarantorId

                        AND pp.PatientProfileId = @PatientProfileId
 

/*Current Insured Information*/

                     SELECT

                        @PatientInsuranceId = pi.PatientInsuranceId ,

                        @InsuredSameAsPatient = ISNULL(pi.InsuredSameAsPatient , 0) ,

                        @InsuredSameAsGuarantor = ISNULL(pi.InsuredSameAsGuarantor , 0)

                     FROM

                        PatientVisitInsurance pvi

                        INNER JOIN PatientInsurance pi ON pvi.PatientInsuranceId = pi.PatientInsuranceId

                                                          AND pi.InsuranceCarriersId = @CurrentInsuranceCarriersId

                     WHERE

                        pvi.PatientVisitId = @pPatientVisitId

--select @CurrentInsuranceCarriersId as '@CurrentInsuranceCarriersId', @PatientInsuranceId as '@PatientInsuranceId', @InsuredSameAsPatient as '@InsuredSameAsPatient',@InsuredSameAsGuarantor as '@InsuredSameAsGuarantor' 
 

                     IF ( @InsuredSameAsPatient <> 0 ) 

                        BEGIN

                              SELECT

                                '700.InsuredLast' = UPPER(pp.Last) ,

                                '701.InsuredFirst' = UPPER(pp.First) ,

                                '702.InsuredMiddle' = UPPER(ISNULL(pp.Middle , '')) ,

                                '703.InsuredId' = UPPER(ISNULL(pi.ClaimOfficeNo , '')) + UPPER(ISNULL(pi.InsuredId , '')) ,

                                '704.InsuredClaimOffice' = UPPER(pi.ClaimOfficeNo) ,

                                '705.InsuredGroup' = UPPER(pi.GroupId) ,

                                '707.InsuredAddress' = UPPER(ISNULL(pp.Address1 , '') + ' ' + ISNULL(pp.Address2 , '')) ,

                                '708.InsuredCity' = UPPER(pp.City) ,

                                '709.InsuredState' = UPPER(pp.State) ,

                                '710.InsuredZip' = SUBSTRING(pp.Zip , 1 , 5) + ' ' + SUBSTRING(pp.Zip , 7 , 4) ,

                                '711.InsuredCountry' = UPPER(pp.Country) ,

                                '712.InsuredPhone' = SUBSTRING(pp.Phone1 , 1 , 10) ,

                                '713.InsuredName' = UPPER(ISNULL(pp.Last , '') + ', ' + ISNULL(pp.First , '') + CASE WHEN ( pp.Middle ) IS NOT NULL

                                                                                                                     THEN ', ' + SUBSTRING(ISNULL(pp.Middle , '') ,

                                                                                                                                           1 , 1)

                                                                                                                     ELSE ''

                                                                                                                END)

                              FROM

                                PatientInsurance pi

                                INNER JOIN PatientProfile pp ON pi.PatientProfileId = pp.PatientProfileId

                              WHERE

                                pi.PatientInsuranceId = @PatientInsuranceId 

                        END

                     ELSE 

                        IF ( @InsuredSameAsGuarantor <> 0 ) 

                           BEGIN

                                 SELECT

                                    '700.InsuredLast' = UPPER(g.Last) ,

                                    '701.InsuredFirst' = UPPER(g.First) ,

                                    '702.InsuredMiddle' = UPPER(ISNULL(g.Middle , '')) ,

                                    '703.InsuredId' = UPPER(ISNULL(pi.ClaimOfficeNo , '')) + UPPER(ISNULL(pi.InsuredId , '')) ,

                                    '704.InsuredClaimOffice' = UPPER(pi.ClaimOfficeNo) ,

                                    '705.InsuredGroup' = UPPER(pi.GroupId) ,

                                    '707.InsuredAddress' = UPPER(ISNULL(g.Address1 , '') + ' ' + ISNULL(g.Address2 , '')) ,

                                    '708.InsuredCity' = UPPER(g.City) ,

                                    '709.InsuredState' = UPPER(g.State) ,

                                    '710.InsuredZip' = SUBSTRING(g.Zip , 1 , 5) + ' ' + SUBSTRING(g.Zip , 7 , 4) ,

                                    '711.InsuredCountry' = UPPER(g.Country) ,

                                    '712.InsuredPhone' = SUBSTRING(g.Phone1 , 1 , 10) ,

                                    '713.InsuredName' = UPPER(ISNULL(g.Last , '') + ', ' + ISNULL(g.First , '') + CASE WHEN ( g.Middle ) IS NOT NULL

                                                                                                                       THEN ', ' + SUBSTRING(ISNULL(g.Middle , '') ,

                                                                                                                                             1 , 1)

                                                                                                                       ELSE ''

                                                                                                                  END)

                                 FROM

                                    PatientInsurance pi

                                    INNER JOIN PatientProfile pp ON pi.PatientProfileId = pp.PatientProfileId

                                    INNER JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId

                                 WHERE

                                    pi.PatientInsuranceId = @PatientInsuranceId 

                           END

                        ELSE 

                           BEGIN

                                 SELECT

                                    '700.InsuredLast' = UPPER(pi.Last) ,

                                    '701.InsuredFirst' = UPPER(pi.First) ,

                                    '702.InsuredMiddle' = UPPER(ISNULL(pi.Middle , '')) ,

                                    '703.InsuredId' = UPPER(ISNULL(pi.ClaimOfficeNo , '')) + UPPER(ISNULL(pi.InsuredId , '')) ,

                                    '704.InsuredClaimOffice' = UPPER(pi.ClaimOfficeNo) ,

                                    '705.InsuredGroup' = UPPER(pi.GroupId) ,

                                    '707.InsuredAddress' = UPPER(ISNULL(pi.Address1 , '') + ' ' + ISNULL(pi.Address2 , '')) ,

                                    '708.InsuredCity' = UPPER(pi.City) ,

                                    '709.InsuredState' = UPPER(pi.State) ,

                                    '710.InsuredZip' = SUBSTRING(pi.Zip , 1 , 5) + ' ' + SUBSTRING(pi.Zip , 7 , 4) ,

                                    '711.InsuredCountry' = UPPER(pi.Country) ,

                                    '712.InsuredPhone' = SUBSTRING(pi.Phone1 , 1 , 10) ,

                                    '713.InsuredName' = UPPER(ISNULL(pi.Last , '') + ', ' + ISNULL(pi.First , '')

                                                              + --', ' + substring(isnull(pi.Middle,''),1,1))

										CASE WHEN ( pi.Middle ) IS NOT NULL THEN ', ' + SUBSTRING(ISNULL(pi.Middle , '') , 1 , 1)

                                                                 ELSE ''

                                                            END)

                                 FROM

                                    PatientInsurance pi

                                 WHERE

                                    pi.PatientInsuranceId = @PatientInsuranceId
 

                           END
 
 

/* PAYER 1 */

                     SELECT

                        @InsuranceCarriersId = 0 ,

                        @PatientInsuranceId = 0

                     SELECT

                        @InsuranceCarriersId = ISNULL(ic.InsuranceCarriersId , 0) ,

                        @PatientInsuranceId = pi.PatientInsuranceId ,

                        @InsuredSameAsPatient = ISNULL(pi.InsuredSameAsPatient , 0) ,

                        @InsuredSameAsGuarantor = ISNULL(pi.InsuredSameAsGuarantor , 0)

                     FROM

                        PatientVisitInsurance pvi

                        INNER JOIN PatientInsurance pi ON pvi.PatientInsuranceId = pi.PatientInsuranceId

                        INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId

                                                           AND (

                                                                 ic.ReferenceCarrier = 0

                                                                 OR ic.ReferenceCarrier IS NULL

                                                               )

                     WHERE

                        pvi.PatientVisitId = @pPatientVisitId

                        AND pvi.OrderForClaims = 1
 

                     IF ( @InsuranceCarriersId <> 0 ) 

                        BEGIN

                              SELECT

                                '60.Payer1' = UPPER(ic.Name)

                              FROM

                                InsuranceCarriers ic

                              WHERE

                                ic.InsuranceCarriersId = @InsuranceCarriersId
 

		--EXEC gGetDoctorFacilityIds_Doctor	@DoctorId, @FacilityId, @CompanyId, @InsuranceCarriersId, @FederalTaxId OUT,

			--				 @Id_PIN OUT, @Id_Referring OUT, @Id_ProviderType OUT, @SupervisingPhysicianId
 

	/* Get the Facility PIN or Doctor PIN based on @FilesFacilityFees */

                              EXEC gGetDoctorFacilityIds_All @DoctorId OUT , @FacilityId , @CompanyId , @InsuranceCarriersId , @FederalTaxId OUT ,

                                   @FederalTaxIdType OUT , @FileAsGroup OUT , @Id_Facility OUT , @Id_PIN OUT , @Id_PINGRP OUT , @Id_GRP OUT , @Id_EMCProv OUT ,

                                   @Id_CLIA OUT , @Id_Referring OUT , @Id_ProviderType OUT , @PlaceOfServiceMId OUT , @Id_Mammography OUT ,

                                   @SupervisingPhysicianId , @FilesFacilityFees , @NPI OUT
 
 

		--BV:we may need to change the provider value based on fileAsGroup or FileasIndividual for doctor fees.

		--at this time (06/07/04) its nor clear. It has always been outputting DOCPIN when filing doctor fees.

		--so this IF statement is written for future need.

                              IF ( ISNULL(@FilesFacilityFees , 0) = 0 ) --AND ISNULL(@FileAsGroup,0) <> 0)

                                 BEGIN

			--EXEC gGetDoctorFacilityIds_All will output the DOCTOR PIN when @FilesFacilityFees = 0

                                       SELECT

                                        '61.Provider1' = UPPER(@Id_PIN) ,

                                        '57.FilingDoctorNPI1' = ISNULL(UPPER(@NPI) , '') ,

                                        '13.FeeSpecialCase' = 0

                                 END

                              ELSE 

                                 BEGIN

			--EXEC gGetDoctorFacilityIds_All will get the FACILITY PIN when @FilesFacilityFees <> 0. Fix to SPR #20953

                                       SELECT

                                        '61.Provider1' = UPPER(@Id_PIN) ,

                                        '57.FilingDoctorNPI1' = ISNULL(UPPER(@NPI) , '') ,

                                        '13.FeeSpecialCase' = 1  

                                 END

                              SELECT

                                '62.ReleaseInfo1' = CASE WHEN ISNULL(ri.FunctionName , 'N') = 'N' THEN 'I'

                                                         ELSE 'Y'

                                                    END--CASE WHEN isnull(pp.ReleaseInfo,0) <> 0 THEN 'Y' ELSE 'N' END

                              FROM

                                PatientProfile pp

                                LEFT JOIN Medlists ri ON pp.ReleaseOfInformationIndicatorMId = ri.MedlistsId

                              WHERE

                                pp.PatientProfileId = @PatientProfileId
 

                              SELECT

                                '63.AcceptAssignment1' = CASE WHEN ISNULL(aa.FunctionName , 'N') = 'N' THEN 'N'

                                                              ELSE 'Y'

                                                         END--CASE WHEN aa.ANSI = 'A' THEN 'Y' ELSE 'N' END

                              FROM

                                PatientVisit pv

                                LEFT JOIN MedLists aa ON pv.AcceptAssignmentMId = aa.MedListsId

                              WHERE

                                pv.PatientVisitId = @pPatientVisitId
 

		/********************************************HEALTH PLAN ID1*************************************************************************/

                              SELECT

                                @InsuranceGroupId = ISNULL(ic.InsuranceGroupId , 0)

                              FROM

                                InsuranceCarriers ic

                              WHERE

                                ic.InsuranceCarriersId = @InsuranceCarriersId
 

                              SELECT

                                @IsHCFAPlanId = iids.IdType ,

                                @HCFAPlanId = CASE iids.IdType

                                                WHEN 1 THEN -- selects the HCFAPlanID

                                                     iids.IdNumber

                                                ELSE ''

                                              END

                              FROM

                                InsuranceCarriersIds iids

                                INNER JOIN PatientInsurance pi ON pi.InsuranceCarriersId = iids.InsuranceCarriersId

                              WHERE

                                pi.PatientProfileId = @PatientProfileId

                                AND pi.orderforclaims = 1

                                AND (

                                      ( iids.LocalInsuranceCarriersId = pi.InsuranceCarriersId )

                                      OR ( iids.LocalInsuranceCarriersId IS NULL )

                                    )

                                AND ISNULL(iids.InsuranceGroupId , @InsuranceGroupId) = @InsuranceGroupId
 

                              IF @IsHCFAPlanId <> 0 -- its HCFAPlanID: select HCFAPlanID

                                 BEGIN

                                       SELECT

                                        '146.HCFAPlanID1' = UPPER(@HCFAPlanId)

                                 END

                              ELSE-- its PayerID: Select the PayerID based on following condition

                                 BEGIN

                                       IF ( @FilesFacilityFees <> 0 )-- when company files facilities fees, get the NPI of the Facility

                                          BEGIN
 

                                                SELECT

                                                    @NPI_Facility = ''

                                                EXEC gGetDoctorFacilityIds_All @FacilityId OUT , @FacilityId , @CompanyId , @InsuranceCarriersId ,

                                                     @FederalTaxId OUT , @FederalTaxIdType OUT , @FileAsGroup OUT , @Id_Facility OUT , @Id_PIN OUT ,

                                                     @Id_PINGRP OUT , @Id_GRP OUT , @Id_EMCProv OUT , @Id_CLIA OUT , @Id_Referring OUT , @Id_ProviderType OUT ,

                                                     @PlaceOfServiceMId OUT , @Id_Mammography OUT , @SupervisingPhysicianId , @FilesFacilityFees ,

                                                     @NPI_Facility OUT

--				Select @NPI_Facility as '@NPI_Facility'

                                                SELECT

                                                    '146.HCFAPlanID1' = UPPER(@NPI_Facility)
 

                                          END

                                       ELSE --when company files Provider fees, get the attending provider NPI or Company NPI

                                          BEGIN
 

				--Find out if the Attending Doc is Filling Individual or as a Group for this InsuranceCarrier.

				-- WJY 04/07 If Attending Doctor is blank, is same as DoctorID FOR FQHC

                                                SELECT

                                                    @AttendingFileAsGroup = 0

                                                SELECT

                                                    @AttendingFileAsGroup = ISNULL(dfid.FileAsGroup , 0)

                                                FROM

                                                    DoctorFacility df

                                                    LEFT JOIN DFIDS dfid ON dfid.DoctorFacilityId = @AttendingId

                                                                            AND --@pDoctorFacilityId AND

                                                                            (

                                                                              dfid.CompanyId = @CompanyId

                                                                              OR (

                                                                                   dfid.CompanyId IS NULL

                                                                                   AND @CompanyId = @CompanyId

                                                                                 )

                                                                            )

                                                                            AND (

                                                                                  dfid.FacilityId = @FacilityId

                                                                                  OR (

                                                                                       dfid.FacilityId IS NULL

                                                                                       AND @FacilityId = @FacilityId

                                                                                     )

                                                                                )

                                                                            AND ISNULL(dfid.InsuranceCarriersId , ISNULL(@InsuranceCarriersId , 0)) = ISNULL(@InsuranceCarriersId , 0)

                                                                            AND ISNULL(dfid.InsuranceGroupId , ISNULL(@InsuranceGroupId , 0)) = ISNULL(@InsuranceGroupId , 0)

                                                WHERE

                                                    df.DoctorFacilityId = @AttendingId

--				SELECT @AttendingFileAsGroup as '@AttendingFileAsGroup', @FilesFacilityFees as '@FilesFacilityFees'

				

                                                IF @AttendingFileAsGroup <> 1--Filing as Individual: get NPI of the Attending Provider

                                                   BEGIN
 

                                                         SELECT

                                                            @NPI_Provider = ''

                                                         IF @IsAttendingDocRefPhy = 1 --Attending Doc is a Referring Physician and not a Responsible Provider

                                                            BEGIN

                                                                  EXEC gGetDoctorFacilityIds_Referring @AttendingId , @FacilityId , @CompanyId ,

                                                                       @InsuranceCarriersId , @Id_Referring OUT , @Id_ProviderType OUT , @Id_Office OUT ,

                                                                       @unused OUT , @unused OUT , @pld_Id_PINTypeANSI OUT , @unused OUT , @unused OUT ,

                                                                       @unused OUT , @unused OUT , @unused OUT , @FederalTaxId OUT , @FederalTaxIdType OUT ,

                                                                       @unused OUT , @unused OUT , @unused OUT , @unused OUT , @unused OUT , @NPI_Provider OUT
 

                                                            END

                                                         ELSE --Attending Doc is a Responsible Provider

                                                            BEGIN

                                                                  EXEC gGetDoctorFacilityIds_All @AttendingId OUT , @FacilityId , @CompanyId ,

                                                                       @InsuranceCarriersId , @FederalTaxId OUT , @FederalTaxIdType OUT , @FileAsGroup OUT ,

                                                                       @Id_Facility OUT , @Id_PIN OUT , @Id_PINGRP OUT , @Id_GRP OUT , @Id_EMCProv OUT ,

                                                                       @Id_CLIA OUT , @Id_Referring OUT , @Id_ProviderType OUT , @PlaceOfServiceMId OUT ,

                                                                       @Id_Mammography OUT , @SupervisingPhysicianId , @FilesFacilityFees , @NPI_Provider OUT

                                                            END

--					Select @NPI_Provider as '@NPI_Provider'

                                                         SELECT

                                                            '146.HCFAPlanID1' = UPPER(@NPI_Provider)
 

                                                   END

                                                ELSE ---Filing as Group: Get NPI of Company

                                                   BEGIN

                                                         SELECT TOP 1

                                                            '146.HCFAPlanID1' = UPPER(CASE WHEN dfid.NPI = '' THEN df.NPI

                                                                                           WHEN dfid.NPI IS NULL THEN df.NPI

                                                                                           ELSE dfid.NPI

                                                                                      END)

                                                         FROM

                                                            DFIDS dfid

                                                            LEFT JOIN DoctorFacility df ON df.DoctorFacilityID = @CompanyId --AND	

                                                         WHERE

                                                            dfid.DoctorFacilityId = @CompanyId

                                                            AND (

                                                                  dfid.FacilityId = @FacilityId

                                                                  OR dfid.FacilityId IS NULL

                                                                )

                                                            AND (

                                                                  dfid.InsuranceCarriersId = @InsuranceCarriersId

                                                                  OR dfid.InsuranceCarriersId IS NULL

                                                                )

                                                            AND (

                                                                  dfid.InsuranceGroupId = @InsuranceGroupId

                                                                  OR dfid.InsuranceGroupId IS NULL

                                                                )

                                                         ORDER BY

                                                            dfid.CompanyId DESC ,

                                                            dfid.FacilityId DESC ,

                                                            dfid.InsuranceCarriersId DESC ,

                                                            dfid.InsuranceGroupId DESC

                                                   END
 

                                          END

                                 END
 

	/*********************************************************************************************************************/
 

                              IF ( @InsuredSameAsPatient <> 0 ) 

                                 BEGIN

                                       SELECT

                                        '670.Insured1Last' = UPPER(pp.Last) ,

                                        '671.Insured1First' = UPPER(pp.First) ,

                                        '672.Insured1Middle' = UPPER(ISNULL(pp.Middle , '')) ,

                                        '673.Insured1Id' = UPPER(ISNULL(pi.ClaimOfficeNo , '')) + UPPER(ISNULL(pi.InsuredId , '')) ,

                                        '674.Insured1ClaimOffice' = UPPER(pi.ClaimOfficeNo) ,

                                        '675.Insured1Group' = UPPER(pi.GroupId) ,

                                        '677.Insured1Address' = UPPER(ISNULL(pp.Address1 , '') + ' ' + ISNULL(pp.Address2 , '')) ,

                                        '678.Insured1City' = UPPER(pp.City) ,

                                        '679.Insured1State' = UPPER(pp.State) ,

                                        '680.Insured1Zip' = SUBSTRING(pp.Zip , 1 , 5) + ' ' + SUBSTRING(pp.Zip , 7 , 4) ,

                                        '681.Insured1Country' = UPPER(pp.Country) ,

                                        '682.Insured1Phone' = SUBSTRING(pp.Phone1 , 1 , 10) ,

                                        '683.Insured1Name' = UPPER(ISNULL(pp.Last , '') + ', ' + ISNULL(pp.First , '')

                                                                   + CASE WHEN ( pp.Middle ) IS NOT NULL THEN ', ' + SUBSTRING(ISNULL(pp.Middle , '') , 1 , 1)

                                                                          ELSE ''

                                                                     END) ,

                                        '684.Insured1GroupName' = UPPER(pi.GroupName)

                                       FROM

                                        PatientInsurance pi

                                        INNER JOIN PatientProfile pp ON pi.PatientProfileId = pp.PatientProfileId

                                       WHERE

                                        pi.PatientInsuranceId = @PatientInsuranceId 

				

                                       SELECT

                                        '676.RelationshipToInsured1' = UPPER(CONVERT(VARCHAR(2) , ml.ANSI)) --convert(varchar(200),'01')

                                       FROM

                                        Medlists ml

                                       WHERE

                                        ml.TableName = 'PatientRelations'

                                        AND ml.Description = 'Self'

                                 END

                              ELSE 

                                 IF ( @InsuredSameAsGuarantor <> 0 ) 

                                    BEGIN

                                          SELECT

                                            '670.Insured1Last' = UPPER(g.Last) ,

                                            '671.Insured1First' = UPPER(g.First) ,

                                            '672.Insured1Middle' = UPPER(ISNULL(g.Middle , '')) ,

                                            '673.Insured1Id' = UPPER(ISNULL(pi.ClaimOfficeNo , '')) + UPPER(ISNULL(pi.InsuredId , '')) ,

                                            '674.Insured1ClaimOffice' = UPPER(pi.ClaimOfficeNo) ,

                                            '675.Insured1Group' = UPPER(pi.GroupId) ,

                                            '677.Insured1Address' = UPPER(ISNULL(g.Address1 , '') + ' ' + ISNULL(g.Address2 , '')) ,

                                            '678.Insured1City' = UPPER(g.City) ,

                                            '679.Insured1State' = UPPER(g.State) ,

                                            '680.Insured1Zip' = SUBSTRING(g.Zip , 1 , 5) + ' ' + SUBSTRING(g.Zip , 7 , 4) ,

                                            '681.Insured1Country' = UPPER(g.Country) ,

                                            '682.Insured1Phone' = SUBSTRING(g.Phone1 , 1 , 10) ,

                                            '683.Insured1Name' = UPPER(ISNULL(g.Last , '') + ', ' + ISNULL(g.First , '')

                                                                       + CASE WHEN ( g.Middle ) IS NOT NULL THEN ', ' + SUBSTRING(ISNULL(g.Middle , '') , 1 , 1)

                                                                              ELSE ''

                                                                         END) ,

                                            '684.Insured1GroupName' = UPPER(pi.GroupName)

                                          FROM

                                            PatientInsurance pi

                                            INNER JOIN PatientProfile pp ON pi.PatientProfileId = pp.PatientProfileId

                                            INNER JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId

                                          WHERE

                                            pi.PatientInsuranceId = @PatientInsuranceId 

				

                                          SELECT

                                            '676.RelationshipToInsured1' = UPPER(ml.ANSI)

                                          FROM

                                            Medlists ml

                                            LEFT JOIN PatientProfile pp ON pp.PatientRelationToGuarantorMId = ml.MedlistsId

                                          WHERE

                                            pp.PatientProfileId = @PatientProfileId				

			

                                    END

                                 ELSE 

                                    BEGIN

                                          SELECT

                                            '670.Insured1Last' = UPPER(pi.Last) ,

                                            '671.Insured1First' = UPPER(pi.First) ,

                                            '672.Insured1Middle' = UPPER(ISNULL(pi.Middle , '')) ,

                                            '673.Insured1Id' = UPPER(ISNULL(pi.ClaimOfficeNo , '')) + UPPER(ISNULL(pi.InsuredId , '')) ,

                                            '674.Insured1ClaimOffice' = UPPER(pi.ClaimOfficeNo) ,

                                            '675.Insured1Group' = UPPER(pi.GroupId) ,

                                            '677.Insured1Address' = UPPER(ISNULL(pi.Address1 , '') + ' ' + ISNULL(pi.Address2 , '')) ,

                                            '678.Insured1City' = UPPER(pi.City) ,

                                            '679.Insured1State' = UPPER(pi.State) ,

                                            '680.Insured1Zip' = SUBSTRING(pi.Zip , 1 , 5) + ' ' + SUBSTRING(pi.Zip , 7 , 4) ,

                                            '681.Insured1Country' = UPPER(pi.Country) ,

                                            '682.Insured1Phone' = SUBSTRING(pi.Phone1 , 1 , 10) ,

                                            '683.Insured1Name' = UPPER(ISNULL(pi.Last , '') + ', ' + ISNULL(pi.First , '')

                                                                       + --', ' + substring(isnull(pi.Middle,''),1,1))

							CASE WHEN ( pi.Middle ) IS NOT NULL THEN ', ' + SUBSTRING(ISNULL(pi.Middle , '') , 1 , 1)

                                               ELSE ''

                                          END) ,

                                            '684.Insured1GroupName' = UPPER(pi.GroupName)

                                          FROM

                                            PatientInsurance pi

                                          WHERE

                                            pi.PatientInsuranceId = @PatientInsuranceId
 

                                          SELECT

                                            '676.RelationshipToInsured1' = UPPER(ml.ANSI)

                                          FROM

                                            PatientInsurance pi

                                            LEFT JOIN Medlists ml ON pi.PatRelToInsuredMId = ml.MedlistsId

                                          WHERE

                                            pi.PatientInsuranceId = @PatientInsuranceId 				

                                    END		

                        END

                     ELSE 

                        BEGIN

                              SELECT

                                '60.Payer1' = CONVERT(VARCHAR(50) , '') ,

                                '61.Provider1' = CONVERT(VARCHAR(25) , '') ,

                                '57.FilingDoctorNPI1' = CONVERT(VARCHAR(25) , '') ,

                                '13.FeeSpecialCase' = -1 ,

                                '62.ReleaseInfo1' = CONVERT(VARCHAR(1) , '') ,

                                '63.AcceptAssignment1' = CONVERT(VARCHAR(1) , '') ,

                                '670.Insured1Last' = CONVERT(VARCHAR(30) , '') ,

                                '671.Insured1First' = CONVERT(VARCHAR(30) , '') ,

                                '672.Insured1Middle' = CONVERT(VARCHAR(30) , '') ,

                                '673.Insured1Id' = CONVERT(VARCHAR(29) , '') ,

                                '674.Insured1ClaimOffice' = CONVERT(VARCHAR(4) , '') ,

                                '675.Insured1Group' = CONVERT(VARCHAR(20) , '') ,

                                '677.Insured1Address' = CONVERT(VARCHAR(100) , '') ,

                                '678.Insured1City' = CONVERT(VARCHAR(30) , '') ,

                                '679.Insured1State' = CONVERT(VARCHAR(3) , '') ,

                                '680.Insured1Zip' = CONVERT(VARCHAR(10) , '') ,

                                '681.Insured1Country' = CONVERT(VARCHAR(30) , '') ,

                                '682.Insured1Phone' = CONVERT(VARCHAR(15) , '') ,

                                '683.Insured1Name' = CONVERT(VARCHAR(110) , '') ,

                                '684.Insured1GroupName' = CONVERT(VARCHAR(20) , '') ,

                                '676.RelationshipToInsured1' = CONVERT(VARCHAR(200) , '') ,

                                '146.HCFAPlanID1' = CONVERT(VARCHAR(80) , '')

                        END
 

                     SELECT

                        @PriorPayments = 0

                     SELECT

                        @PriorPayments = SUM(vt.Payments)

                     FROM

                        VisitTransactions vt

                     WHERE

                        vt.InsuranceCarriersId = @InsuranceCarriersId

                        AND vt.PatientVisitId = @pPatientVisitId

                     SELECT

                        '78.PriorPayments1' = @PriorPayments

                     SELECT

                        @RunningTotalCharge = @TotalCharge - ISNULL(@PriorPayments , 0)

                     SELECT

                        '79.EstimatedAmountDue1' = @RunningTotalCharge
 

/* PAYOR 2 */

                     SELECT

                        @InsuranceCarriersId = 0

                     SELECT

                        @InsuranceCarriersId = ISNULL(ic.InsuranceCarriersId , 0) ,

                        @PatientInsuranceId = pi.PatientInsuranceId ,

                        @InsuredSameAsPatient = ISNULL(pi.InsuredSameAsPatient , 0) ,

                        @InsuredSameAsGuarantor = ISNULL(pi.InsuredSameAsGuarantor , 0)

                     FROM

                        PatientVisitInsurance pvi

                        INNER JOIN PatientInsurance pi ON pvi.PatientInsuranceId = pi.PatientInsuranceId

                        INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId

                                                           AND (

                                                                 ic.ReferenceCarrier = 0

                                                                 OR ic.ReferenceCarrier IS NULL

                                                               )

                     WHERE

                        pvi.PatientVisitId = @pPatientVisitId

                        AND pvi.OrderForClaims = 2

                     IF ( @InsuranceCarriersId <> 0 ) 

                        BEGIN

                              SELECT

                                '80.Payer2' = UPPER(ic.Name)

                              FROM

                                InsuranceCarriers ic

                              WHERE

                                ic.InsuranceCarriersId = @InsuranceCarriersId
 

	/* Get the Facility PIN or Doctor PIN based on @FilesFacilityFees */

                              EXEC gGetDoctorFacilityIds_All @DoctorId OUT , @FacilityId , @CompanyId , @InsuranceCarriersId , @FederalTaxId OUT ,

                                   @FederalTaxIdType OUT , @FileAsGroup OUT , @Id_Facility OUT , @Id_PIN OUT , @Id_PINGRP OUT , @Id_GRP OUT , @Id_EMCProv OUT ,

                                   @Id_CLIA OUT , @Id_Referring OUT , @Id_ProviderType OUT , @PlaceOfServiceMId OUT , @Id_Mammography OUT ,

                                   @SupervisingPhysicianId , @FilesFacilityFees , @NPI OUT
 
 

		--BV:we may need to change the provider value based on fileAsGroup or FileasIndividual for doctor fees.

		--at this time (06/07/04) its nor clear. It has always been outputting DOCPIN when filing doctor fees.

		--so this IF statement is written for future need.

                              IF ( ISNULL(@FilesFacilityFees , 0) = 0 ) --AND ISNULL(@FileAsGroup,0) <> 0)

                                 BEGIN

			--EXEC gGetDoctorFacilityIds_All will output the DOCTOR PIN when @FilesFacilityFees = 0

                                       SELECT

                                        '81.Provider2' = UPPER(@Id_PIN)

                                 END

                              ELSE 

                                 BEGIN

			--EXEC gGetDoctorFacilityIds_All will get the FACILITY PIN when @FilesFacilityFees <> 0. Fix to SPR #20953

                                       SELECT

                                        '81.Provider2' = UPPER(@Id_PIN)

                                 END

                              SELECT

                                '82.ReleaseInfo2' = CASE WHEN ISNULL(ri.FunctionName , 'N') = 'N' THEN 'I'

                                                         ELSE 'Y'

                                                    END--CASE WHEN isnull(pp.ReleaseInfo,0) <> 0 THEN 'Y' ELSE 'N' END

                              FROM

                                PatientProfile pp

                                LEFT JOIN Medlists ri ON pp.ReleaseOfInformationIndicatorMId = ri.MedlistsId

                              WHERE

                                pp.PatientProfileId = @PatientProfileId

                              SELECT

                                '83.AcceptAssignment2' = CASE WHEN ISNULL(aa.FunctionName , 'N') = 'N' THEN 'N'

                                                              ELSE 'Y'

                                                         END--CASE WHEN aa.ANSI = 'A' THEN 'Y' ELSE 'N' END

                              FROM

                                PatientVisit pv

                                LEFT JOIN MedLists aa ON pv.AcceptAssignmentMId = aa.MedListsId

                              WHERE

                                pv.PatientVisitId = @pPatientVisitId
 

		/********************HEALTH PLAN ID2*************************************************************************************************/

                              SELECT

                                @InsuranceGroupId = 0

                              SELECT

                                @IsHCFAPlanId = 0

                              SELECT

                                @HCFAPlanId = ''

                              SELECT

                                @InsuranceGroupId = ISNULL(ic.InsuranceGroupId , 0)

                              FROM

                                InsuranceCarriers ic

                              WHERE

                                ic.InsuranceCarriersId = @InsuranceCarriersId
 

                              SELECT

                                @IsHCFAPlanId = iids.IdType ,

                                @HCFAPlanId = CASE iids.IdType

                                                WHEN 1 THEN -- selects the HCFAPlanID

                                                     iids.IdNumber

                                                ELSE ''

                                              END

                              FROM

                                InsuranceCarriersIds iids

                                INNER JOIN PatientInsurance pi ON pi.InsuranceCarriersId = iids.InsuranceCarriersId

                              WHERE

                                pi.PatientProfileId = @PatientProfileId

                                AND pi.orderforclaims = 2

                                AND (

                                      ( iids.LocalInsuranceCarriersId = pi.InsuranceCarriersId )

                                      OR ( iids.LocalInsuranceCarriersId IS NULL )

                                    )

                                AND ISNULL(iids.InsuranceGroupId , @InsuranceGroupId) = @InsuranceGroupId
 

--SELECT @InsuranceCarriersId as '@InsuranceCarriersId', @InsuranceGroupId as '@InsuranceGroupId'

                              IF @IsHCFAPlanId <> 0 -- its HCFAPlanID: select HCFAPlanID

                                 BEGIN

                                       SELECT

                                        '147.HCFAPlanID2' = UPPER(@HCFAPlanId)

                                 END

                              ELSE-- its PayerID: Select the PayerID based on following condition

                                 BEGIN

                                       IF ( @FilesFacilityFees <> 0 )-- when company files facilities fees, get the NPI of the Facility

                                          BEGIN
 

                                                SELECT

                                                    @NPI_Facility = ''

                                                EXEC gGetDoctorFacilityIds_All @FacilityId OUT , @FacilityId , @CompanyId , @InsuranceCarriersId ,

                                                     @FederalTaxId OUT , @FederalTaxIdType OUT , @FileAsGroup OUT , @Id_Facility OUT , @Id_PIN OUT ,

                                                     @Id_PINGRP OUT , @Id_GRP OUT , @Id_EMCProv OUT , @Id_CLIA OUT , @Id_Referring OUT , @Id_ProviderType OUT ,

                                                     @PlaceOfServiceMId OUT , @Id_Mammography OUT , @SupervisingPhysicianId , @FilesFacilityFees ,

                                                     @NPI_Facility OUT

--				Select @NPI_Facility as '@NPI_Facility'

                                                SELECT

                                                    '147.HCFAPlanID2' = UPPER(@NPI_Facility)
 

                                          END

                                       ELSE --when company files Provider fees, get the attending provider NPI or Company NPI

                                          BEGIN
 

				--Find out if the Attending Doc is Filling Individual or as a Group for this InsuranceCarrier.

                                                SELECT

                                                    @AttendingFileAsGroup = 0

                                                SELECT

                                                    @AttendingFileAsGroup = ISNULL(dfid.FileAsGroup , 0)

                                                FROM

                                                    DoctorFacility df

                                                    LEFT JOIN DFIDS dfid ON dfid.DoctorFacilityId = @AttendingId

                                                                            AND --@pDoctorFacilityId AND

                                                                            (

                                                                              dfid.CompanyId = @CompanyId

                                                                              OR (

                                                                                   dfid.CompanyId IS NULL

                                                                                   AND @CompanyId = @CompanyId

                                                                                 )

                                                                            )

                                                                            AND (

                                                                                  dfid.FacilityId = @FacilityId

                                                                                  OR (

                                                                                       dfid.FacilityId IS NULL

                                                                                       AND @FacilityId = @FacilityId

                                                                                     )

                                                                                )

                                                                            AND ISNULL(dfid.InsuranceCarriersId , ISNULL(@InsuranceCarriersId , 0)) = ISNULL(@InsuranceCarriersId , 0)

                                                                            AND ISNULL(dfid.InsuranceGroupId , ISNULL(@InsuranceGroupId , 0)) = ISNULL(@InsuranceGroupId , 0)

                                                WHERE

                                                    df.DoctorFacilityId = @AttendingId

--				SELECT @AttendingFileAsGroup as '@AttendingFileAsGroup', @FilesFacilityFees as '@FilesFacilityFees'

				

                                                IF @AttendingFileAsGroup <> 1--Filing as Individual: get NPI of the Attending Provider

                                                   BEGIN
 

                                                         SELECT

                                                            @NPI_Provider = ''

                                                         IF @IsAttendingDocRefPhy = 1 --Attending Doc is a Referring Physician and not a Responsible Provider

                                                            BEGIN

                                                                  EXEC gGetDoctorFacilityIds_Referring @AttendingId , @FacilityId , @CompanyId ,

                                                                       @InsuranceCarriersId , @Id_Referring OUT , @Id_ProviderType OUT , @Id_Office OUT ,

                                                                       @unused OUT , @unused OUT , @pld_Id_PINTypeANSI OUT , @unused OUT , @unused OUT ,

                                                                       @unused OUT , @unused OUT , @unused OUT , @FederalTaxId OUT , @FederalTaxIdType OUT ,

                                                                       @unused OUT , @unused OUT , @unused OUT , @unused OUT , @unused OUT , @NPI_Provider OUT
 

                                                            END

                                                         ELSE --Attending Doc is a Responsible Provider

                                                            BEGIN

                                                                  EXEC gGetDoctorFacilityIds_All @AttendingId OUT , @FacilityId , @CompanyId ,

                                                                       @InsuranceCarriersId , @FederalTaxId OUT , @FederalTaxIdType OUT , @FileAsGroup OUT ,

                                                                       @Id_Facility OUT , @Id_PIN OUT , @Id_PINGRP OUT , @Id_GRP OUT , @Id_EMCProv OUT ,

                                                                       @Id_CLIA OUT , @Id_Referring OUT , @Id_ProviderType OUT , @PlaceOfServiceMId OUT ,

                                                                       @Id_Mammography OUT , @SupervisingPhysicianId , @FilesFacilityFees , @NPI_Provider OUT

                                                            END

--					Select @NPI_Provider as '@NPI_Provider'

                                                         SELECT

                                                            '147.HCFAPlanID2' = UPPER(@NPI_Provider)
 

                                                   END

                                                ELSE ---Filing as Group: Get NPI of Company

                                                   BEGIN

                                                         SELECT TOP 1

                                                            '147.HCFAPlanID2' = UPPER(CASE WHEN dfid.NPI = '' THEN df.NPI

                                                                                           WHEN dfid.NPI IS NULL THEN df.NPI

                                                                                           ELSE dfid.NPI

                                                                                      END)

                                                         FROM

                                                            DFIDS dfid

                                                            LEFT JOIN DoctorFacility df ON df.DoctorFacilityID = @CompanyId --AND	

                                                         WHERE

                                                            dfid.DoctorFacilityId = @CompanyId

                                                            AND (

                                                                  dfid.FacilityId = @FacilityId

                                                                  OR dfid.FacilityId IS NULL

                                                                )

                                                            AND (

                                                                  dfid.InsuranceCarriersId = @InsuranceCarriersId

                                                                  OR dfid.InsuranceCarriersId IS NULL

                                                                )

                                                            AND (

                                                                  dfid.InsuranceGroupId = @InsuranceGroupId

                                                                  OR dfid.InsuranceGroupId IS NULL

                                                                )

                                                         ORDER BY

                                                            dfid.CompanyId DESC ,

                                                            dfid.FacilityId DESC ,

                                                            dfid.InsuranceCarriersId DESC ,

                                                            dfid.InsuranceGroupId DESC

                                                   END
 

                                          END

                                 END
 

	/*********************************************************************************************************************/
 

                              IF ( @InsuredSameAsPatient <> 0 ) 

                                 BEGIN

                                       SELECT

                                        '90.Insured2Last' = UPPER(pp.Last) ,

                                        '91.Insured2First' = UPPER(pp.First) ,

                                        '92.Insured2Middle' = UPPER(ISNULL(pp.Middle , '')) ,

                                        '93.Insured2Id' = UPPER(pi.InsuredId) ,

                                        '94.Insured2ClaimOffice' = UPPER(pi.ClaimOfficeNo) ,

                                        '95.Insured2Group' = UPPER(pi.GroupId) ,

                                        '96.Insured2GroupName' = UPPER(pi.GroupName)

                                       FROM

                                        PatientInsurance pi

                                        INNER JOIN PatientProfile pp ON pi.PatientProfileId = pp.PatientProfileId

                                       WHERE

                                        pi.PatientInsuranceId = @PatientInsuranceId 

				

                                       SELECT

                                        '97.RelationshipToInsured2' = UPPER(CONVERT(VARCHAR(2) , ml.ANSI)) --convert(varchar(200),'01')

                                       FROM

                                        Medlists ml

                                       WHERE

                                        ml.TableName = 'PatientRelations'

                                        AND ml.Description = 'Self'

                                 END

                              ELSE 

                                 IF ( @InsuredSameAsGuarantor <> 0 ) 

                                    BEGIN

                                          SELECT

                                            '90.Insured2Last' = UPPER(g.Last) ,

                                            '91.Insured2First' = UPPER(g.First) ,

                                            '92.Insured2Middle' = UPPER(ISNULL(g.Middle , '')) ,

                                            '93.Insured2Id' = UPPER(pi.InsuredId) ,

                                            '94.Insured2ClaimOffice' = UPPER(pi.ClaimOfficeNo) ,

                                            '95.Insured2Group' = UPPER(pi.GroupId) ,

                                            '96.Insured2GroupName' = UPPER(pi.GroupName)

                                          FROM

                                            PatientInsurance pi

                                            INNER JOIN PatientProfile pp ON pi.PatientProfileId = pp.PatientProfileId

                                            INNER JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId

                                          WHERE

                                            pi.PatientInsuranceId = @PatientInsuranceId 

				

                                          SELECT

                                            '97.RelationshipToInsured2' = UPPER(ml.ANSI)

                                          FROM

                                            Medlists ml

                                            LEFT JOIN PatientProfile pp ON pp.PatientRelationToGuarantorMId = ml.MedlistsId

                                          WHERE

                                            pp.PatientProfileId = @PatientProfileId				
 

                                    END

                                 ELSE 

                                    BEGIN

                                          SELECT

                                            '90.Insured2Last' = UPPER(pi.Last) ,

                                            '91.Insured2First' = UPPER(pi.First) ,

                                            '92.Insured2Middle' = UPPER(ISNULL(pi.Middle , '')) ,

                                            '93.Insured2Id' = UPPER(pi.InsuredId) ,

                                            '94.Insured2ClaimOffice' = UPPER(pi.ClaimOfficeNo) ,

                                            '95.Insured2Group' = UPPER(pi.GroupId) ,

                                            '96.Insured2GroupName' = UPPER(pi.GroupName)

                                          FROM

                                            PatientInsurance pi

                                          WHERE

                                            pi.PatientInsuranceId = @PatientInsuranceId

			

                                          SELECT

                                            '97.RelationshipToInsured2' = UPPER(ml.ANSI)

                                          FROM

                                            PatientInsurance pi

                                            LEFT JOIN Medlists ml ON pi.PatRelToInsuredMId = ml.MedlistsId

                                          WHERE

                                            pi.PatientInsuranceId = @PatientInsuranceId 

                                    END		

                        END

                     ELSE 

                        BEGIN

                              SELECT

                                '80.Payer2' = CONVERT(VARCHAR(50) , '') ,

                                '81.Provider2' = CONVERT(VARCHAR(25) , '') ,

                                '82.ReleaseInfo2' = CONVERT(VARCHAR(1) , '') ,

                                '83.AcceptAssignment2' = CONVERT(VARCHAR(1) , '') ,

                                '90.Insured2Last' = CONVERT(VARCHAR(30) , '') ,

                                '91.Insured2First' = CONVERT(VARCHAR(30) , '') ,

                                '92.Insured2Middle' = CONVERT(VARCHAR(30) , '') ,

                                '93.Insured2Id' = CONVERT(VARCHAR(29) , '') ,

                                '94.Insured2ClaimOffice' = CONVERT(VARCHAR(4) , '') ,

                                '95.Insured2Group' = CONVERT(VARCHAR(20) , '') ,

                                '96.Insured2GroupName' = CONVERT(VARCHAR(20) , '') ,

                                '97.RelationshipToInsured2' = CONVERT(VARCHAR(200) , '') ,

                                '147.HCFAPlanID2' = CONVERT(VARCHAR(80) , '')

                        END

                     SELECT

                        @PriorPayments = 0

                     SELECT

                        @PriorPayments = SUM(vt.Payments)

                     FROM

                        VisitTransactions vt

                     WHERE

                        vt.InsuranceCarriersId = @InsuranceCarriersId

                        AND vt.PatientVisitId = @pPatientVisitId

                     SELECT

                        '98.PriorPayments2' = @PriorPayments

                     SELECT

                        @RunningTotalCharge = @RunningTotalCharge - ISNULL(@PriorPayments , 0)

                     SELECT

                        '99.EstimatedAmountDue2' = @RunningTotalCharge
 

/* PAYOR 3 */

                     SELECT

                        @InsuranceCarriersId = 0
 

                     SELECT

                        @InsuranceCarriersId = ISNULL(ic.InsuranceCarriersId , 0) ,

                        @PatientInsuranceId = pi.PatientInsuranceId ,

                        @InsuredSameAsPatient = ISNULL(pi.InsuredSameAsPatient , 0) ,

                        @InsuredSameAsGuarantor = ISNULL(pi.InsuredSameAsGuarantor , 0)

                     FROM

                        PatientVisitInsurance pvi

                        INNER JOIN PatientInsurance pi ON pvi.PatientInsuranceId = pi.PatientInsuranceId

                        INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId

                                                           AND (

                                                                 ic.ReferenceCarrier = 0

                                                                 OR ic.ReferenceCarrier IS NULL

                                                               )

                     WHERE

                        pvi.PatientVisitId = @pPatientVisitId

                        AND pvi.OrderForClaims = 3

                     IF ( @InsuranceCarriersId <> 0 ) 

                        BEGIN

                              SELECT

                                '100.Payer3' = UPPER(ic.Name)

                              FROM

                                InsuranceCarriers ic

                              WHERE

                                ic.InsuranceCarriersId = @InsuranceCarriersId
 

	/* Get the Facility PIN or Doctor PIN based on @FilesFacilityFees */

                              EXEC gGetDoctorFacilityIds_All @DoctorId OUT , @FacilityId , @CompanyId , @InsuranceCarriersId , @FederalTaxId OUT ,

                                   @FederalTaxIdType OUT , @FileAsGroup OUT , @Id_Facility OUT , @Id_PIN OUT , @Id_PINGRP OUT , @Id_GRP OUT , @Id_EMCProv OUT ,

                                   @Id_CLIA OUT , @Id_Referring OUT , @Id_ProviderType OUT , @PlaceOfServiceMId OUT , @Id_Mammography OUT ,

                                   @SupervisingPhysicianId , @FilesFacilityFees , @NPI OUT
 
 

		--BV:we may need to change the provider value based on fileAsGroup or FileasIndividual for doctor fees.

		--at this time (06/07/04) its nor clear. It has always been outputting DOCPIN when filing doctor fees.

		--so this IF statement is written for future need.

                              IF ( ISNULL(@FilesFacilityFees , 0) = 0 ) --AND ISNULL(@FileAsGroup,0) <> 0)

                                 BEGIN

			--EXEC gGetDoctorFacilityIds_All will output the DOCTOR PIN when @FilesFacilityFees = 0

                                       SELECT

                                        '101.Provider3' = UPPER(@Id_PIN)

                                 END

                              ELSE 

                                 BEGIN

			--EXEC gGetDoctorFacilityIds_All will get the FACILITY PIN when @FilesFacilityFees <> 0. Fix to SPR #20953

                                       SELECT

                                        '101.Provider3' = UPPER(@Id_PIN)

                                 END

                              SELECT

                                '102.ReleaseInfo3' = CASE WHEN ISNULL(ri.FunctionName , 'N') = 'N' THEN 'I'

                                                          ELSE 'Y'

                                                     END--CASE WHEN isnull(pp.ReleaseInfo,0) <> 0 THEN 'Y' ELSE 'N' END

                              FROM

                                PatientProfile pp

                                LEFT JOIN Medlists ri ON pp.ReleaseOfInformationIndicatorMId = ri.MedlistsId

                              WHERE

                                pp.PatientProfileId = @PatientProfileId

		

                              SELECT

                                '103.AcceptAssignment3' = CASE WHEN ISNULL(aa.FunctionName , 'N') = 'N' THEN 'N'

                                                               ELSE 'Y'

                                                          END--CASE WHEN aa.ANSI = 'A' THEN 'Y' ELSE 'N' END

                              FROM

                                PatientVisit pv

                                LEFT JOIN MedLists aa ON pv.AcceptAssignmentMId = aa.MedListsId

                              WHERE

                                pv.PatientVisitId = @pPatientVisitId
 

		/********************HEALTH PLAN ID3*************************************************************************************************/

                              SELECT

                                @InsuranceGroupId = 0

                              SELECT

                                @IsHCFAPlanId = 0

                              SELECT

                                @HCFAPlanId = ''

                              SELECT

                                @InsuranceGroupId = ISNULL(ic.InsuranceGroupId , 0)

                              FROM

                                InsuranceCarriers ic

                              WHERE

                                ic.InsuranceCarriersId = @InsuranceCarriersId
 

                              SELECT

                                @IsHCFAPlanId = iids.IdType ,

                                @HCFAPlanId = CASE iids.IdType

                                                WHEN 1 THEN -- selects the HCFAPlanID

                                                     iids.IdNumber

                                                ELSE ''

                                              END

                              FROM

                                InsuranceCarriersIds iids

                                INNER JOIN PatientInsurance pi ON pi.InsuranceCarriersId = iids.InsuranceCarriersId

                              WHERE

                                pi.PatientProfileId = @PatientProfileId

                                AND pi.orderforclaims = 3

                                AND (

                                      ( iids.LocalInsuranceCarriersId = pi.InsuranceCarriersId )

                                      OR ( iids.LocalInsuranceCarriersId IS NULL )

                                    )

                                AND ISNULL(iids.InsuranceGroupId , @InsuranceGroupId) = @InsuranceGroupId
 

--SELECT @InsuranceCarriersId as '@InsuranceCarriersId', @InsuranceGroupId as '@InsuranceGroupId'

                              IF @IsHCFAPlanId <> 0 -- its HCFAPlanID: select HCFAPlanID

                                 BEGIN

                                       SELECT

                                        '148.HCFAPlanID3' = UPPER(@HCFAPlanId)

                                 END

                              ELSE-- its PayerID: Select the PayerID based on following condition

                                 BEGIN

                                       IF ( @FilesFacilityFees <> 0 )-- when company files facilities fees, get the NPI of the Facility

                                          BEGIN
 

                                                SELECT

                                                    @NPI_Facility = ''

                                                EXEC gGetDoctorFacilityIds_All @FacilityId OUT , @FacilityId , @CompanyId , @InsuranceCarriersId ,

                                                     @FederalTaxId OUT , @FederalTaxIdType OUT , @FileAsGroup OUT , @Id_Facility OUT , @Id_PIN OUT ,

                                                     @Id_PINGRP OUT , @Id_GRP OUT , @Id_EMCProv OUT , @Id_CLIA OUT , @Id_Referring OUT , @Id_ProviderType OUT ,

                                                     @PlaceOfServiceMId OUT , @Id_Mammography OUT , @SupervisingPhysicianId , @FilesFacilityFees ,

                                                     @NPI_Facility OUT

--				Select @NPI_Facility as '@NPI_Facility'

                                                SELECT

                                                    '148.HCFAPlanID3' = UPPER(@NPI_Facility)
 

                                          END

                                       ELSE --when company files Provider fees, get the attending provider NPI or Company NPI

                                          BEGIN
 

				--Find out if the Attending Doc is Filling Individual or as a Group for this InsuranceCarrier.

                                                SELECT

                                                    @AttendingFileAsGroup = 0

                                                SELECT

                                                    @AttendingFileAsGroup = ISNULL(dfid.FileAsGroup , 0)

                                                FROM

                                                    DoctorFacility df

                                                    LEFT JOIN DFIDS dfid ON dfid.DoctorFacilityId = @AttendingId

                                                                            AND --@pDoctorFacilityId AND

                                                                            (

                                                                              dfid.CompanyId = @CompanyId

                                                                              OR (

                                                                                   dfid.CompanyId IS NULL

                                                                                   AND @CompanyId = @CompanyId

                                                                                 )

                                                                            )

                                                                            AND (

                                                                                  dfid.FacilityId = @FacilityId

                                                                                  OR (

                                                                                       dfid.FacilityId IS NULL

                                                                                       AND @FacilityId = @FacilityId

                                                                                     )

                                                                                )

                                                                            AND ISNULL(dfid.InsuranceCarriersId , ISNULL(@InsuranceCarriersId , 0)) = ISNULL(@InsuranceCarriersId , 0)

                                                                            AND ISNULL(dfid.InsuranceGroupId , ISNULL(@InsuranceGroupId , 0)) = ISNULL(@InsuranceGroupId , 0)

                                                WHERE

                                                    df.DoctorFacilityId = @AttendingId

--				SELECT @AttendingFileAsGroup as '@AttendingFileAsGroup', @FilesFacilityFees as '@FilesFacilityFees'

				

                                                IF @AttendingFileAsGroup <> 1--Filing as Individual: get NPI of the Attending Provider

                                                   BEGIN
 

                                                         SELECT

                                                            @NPI_Provider = ''

                                                         IF @IsAttendingDocRefPhy = 1 --Attending Doc is a Referring Physician and not a Responsible Provider

                                                            BEGIN

                                                                  EXEC gGetDoctorFacilityIds_Referring @AttendingId , @FacilityId , @CompanyId ,

                                                                       @InsuranceCarriersId , @Id_Referring OUT , @Id_ProviderType OUT , @Id_Office OUT ,

                                                                       @unused OUT , @unused OUT , @pld_Id_PINTypeANSI OUT , @unused OUT , @unused OUT ,

                                                                       @unused OUT , @unused OUT , @unused OUT , @FederalTaxId OUT , @FederalTaxIdType OUT ,

                                                                       @unused OUT , @unused OUT , @unused OUT , @unused OUT , @unused OUT , @NPI_Provider OUT
 

                                                            END

                                                         ELSE --Attending Doc is a Responsible Provider

                                                            BEGIN

                                                                  EXEC gGetDoctorFacilityIds_All @AttendingId OUT , @FacilityId , @CompanyId ,

                                                                       @InsuranceCarriersId , @FederalTaxId OUT , @FederalTaxIdType OUT , @FileAsGroup OUT ,

                                                                       @Id_Facility OUT , @Id_PIN OUT , @Id_PINGRP OUT , @Id_GRP OUT , @Id_EMCProv OUT ,

                                                                       @Id_CLIA OUT , @Id_Referring OUT , @Id_ProviderType OUT , @PlaceOfServiceMId OUT ,

                                                                       @Id_Mammography OUT , @SupervisingPhysicianId , @FilesFacilityFees , @NPI_Provider OUT

                                                            END

--					Select @NPI_Provider as '@NPI_Provider'

                                                         SELECT

                                                            '148.HCFAPlanID3' = UPPER(@NPI_Provider)
 

                                                   END

                                                ELSE ---Filing as Group: Get NPI of Company

                                                   BEGIN

                                                         SELECT TOP 1

                                                            '148.HCFAPlanID3' = UPPER(CASE WHEN dfid.NPI = '' THEN df.NPI

                                                                                           WHEN dfid.NPI IS NULL THEN df.NPI

                                                                                           ELSE dfid.NPI

                                                                                      END)

                                                         FROM

                                                            DFIDS dfid

                                                            LEFT JOIN DoctorFacility df ON df.DoctorFacilityID = @CompanyId --AND	

                                                         WHERE

                                                            dfid.DoctorFacilityId = @CompanyId

                                                            AND (

                                                                  dfid.FacilityId = @FacilityId

                                                                  OR dfid.FacilityId IS NULL

                                                                )

                                                            AND (

                                                                  dfid.InsuranceCarriersId = @InsuranceCarriersId

                                                                  OR dfid.InsuranceCarriersId IS NULL

                                                                )

                                                            AND (

                                                                  dfid.InsuranceGroupId = @InsuranceGroupId

                                                                  OR dfid.InsuranceGroupId IS NULL

                                                                )

                                                         ORDER BY

                                                            dfid.CompanyId DESC ,

                                                            dfid.FacilityId DESC ,

                                                            dfid.InsuranceCarriersId DESC ,

                                                            dfid.InsuranceGroupId DESC

                                                   END
 

                                          END

                                 END
 

	/*********************************************************************************************************************/
 

                              IF ( @InsuredSameAsPatient <> 0 ) 

                                 BEGIN

                                       SELECT

                                        '110.Insured3Last' = UPPER(pp.Last) ,

                                        '111.Insured3First' = UPPER(pp.First) ,

                                        '112.Insured3Middle' = UPPER(ISNULL(pp.Middle , '')) ,

                                        '113.Insured3Id' = UPPER(pi.InsuredId) ,

                                        '114.Insured3ClaimOffice' = UPPER(pi.ClaimOfficeNo) ,

                                        '115.Insured3Group' = UPPER(pi.GroupId) ,

                                        '116.Insured3GroupName' = UPPER(pi.GroupName)

                                       FROM

                                        PatientInsurance pi

                                        INNER JOIN PatientProfile pp ON pi.PatientProfileId = pp.PatientProfileId

                                       WHERE

                                        pi.PatientInsuranceId = @PatientInsuranceId 

				

                                       SELECT

                                        '117.RelationshipToInsured3' = UPPER(CONVERT(VARCHAR(2) , ml.ANSI)) --convert(varchar(200),'01')

                                       FROM

                                        Medlists ml

                                       WHERE

                                        ml.TableName = 'PatientRelations'

                                        AND ml.Description = 'Self'

                                 END

                              ELSE 

                                 IF ( @InsuredSameAsGuarantor <> 0 ) 

                                    BEGIN

                                          SELECT

                                            '110.Insured3Last' = UPPER(g.Last) ,

                                            '111.Insured3First' = UPPER(g.First) ,

                                            '112.Insured3Middle' = UPPER(ISNULL(g.Middle , '')) ,

                                            '113.Insured3Id' = UPPER(pi.InsuredId) ,

                                            '114.Insured3ClaimOffice' = UPPER(pi.ClaimOfficeNo) ,

                                            '115.Insured3Group' = UPPER(pi.GroupId) ,

                                            '116.Insured3GroupName' = UPPER(pi.GroupName)

                                          FROM

                                            PatientInsurance pi

                                            INNER JOIN PatientProfile pp ON pi.PatientProfileId = pp.PatientProfileId

                                            INNER JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId

                                          WHERE

                                            pi.PatientInsuranceId = @PatientInsuranceId 

				

                                          SELECT

                                            '117.RelationshipToInsured3' = UPPER(ml.ANSI)

                                          FROM

                                            Medlists ml

                                            LEFT JOIN PatientProfile pp ON pp.PatientRelationToGuarantorMId = ml.MedlistsId

                                          WHERE

                                            pp.PatientProfileId = @PatientProfileId				

                                    END

                                 ELSE 

                                    BEGIN

                                          SELECT

                                            '110.Insured3Last' = UPPER(pi.Last) ,

                                            '111.Insured3First' = UPPER(pi.First) ,

                                            '112.Insured3Middle' = UPPER(ISNULL(pi.Middle , '')) ,

                                            '113.Insured3Id' = UPPER(pi.InsuredId) ,

                                            '114.Insured3ClaimOffice' = UPPER(pi.ClaimOfficeNo) ,

                                            '115.Insured3Group' = UPPER(pi.GroupId) ,

                                            '116.Insured3GroupName' = UPPER(pi.GroupName)

                                          FROM

                                            PatientInsurance pi

                                          WHERE

                                            pi.PatientInsuranceId = @PatientInsuranceId

			

                                          SELECT

                                            '117.RelationshipToInsured3' = UPPER(ml.ANSI)

                                          FROM

                                            PatientInsurance pi

                                            LEFT JOIN Medlists ml ON pi.PatRelToInsuredMId = ml.MedlistsId

                                          WHERE

                                            pi.PatientInsuranceId = @PatientInsuranceId 
 

				
 
 

                                    END		

                        END

                     ELSE 

                        BEGIN

                              SELECT

                                '100.Payer3' = CONVERT(VARCHAR(50) , '') ,

                                '101.Provider3' = CONVERT(VARCHAR(25) , '') ,

                                '102.ReleaseInfo3' = CONVERT(VARCHAR(1) , '') ,

                                '103.AcceptAssignment3' = CONVERT(VARCHAR(1) , '') ,

                                '110.Insured3Last' = CONVERT(VARCHAR(30) , '') ,

                                '111.Insured3First' = CONVERT(VARCHAR(30) , '') ,

                                '112.Insured3Middle' = CONVERT(VARCHAR(30) , '') ,

                                '113.Insured3Id' = CONVERT(VARCHAR(28) , '') ,

                                '114.Insured3ClaimOffice' = CONVERT(VARCHAR(4) , '') ,

                                '115.Insured3Group' = CONVERT(VARCHAR(20) , '') ,

                                '116.Insured3GroupName' = CONVERT(VARCHAR(20) , '') ,

                                '117.RelationshipToInsured3' = CONVERT(VARCHAR(200) , '') ,

                                '148.HCFAPlanID3' = CONVERT(VARCHAR(80) , '')

                        END

                     SELECT

                        @PriorPayments = 0

                     SELECT

                        @PriorPayments = SUM(vt.Payments)

                     FROM

                        VisitTransactions vt

                     WHERE

                        vt.InsuranceCarriersId = @InsuranceCarriersId

                        AND vt.PatientVisitId = @pPatientVisitId

                     SELECT

                        '118.PriorPayments3' = @PriorPayments

                     SELECT

                        @RunningTotalCharge = @RunningTotalCharge - ISNULL(@PriorPayments , 0)

                     SELECT

                        '119.EstimatedAmountDue3' = @RunningTotalCharge
 
 

                     DECLARE @CurrentInsuranceCarrierId INT
 

                     SELECT

                        @CurrentInsuranceCarrierId = 0

                     SELECT

                        @CurrentInsuranceCarrierId = ISNULL(pv.CurrentInsuranceCarriersId , 0)

                     FROM

                        PatientVisit pv

                     WHERE

                        pv.PatientVisitId = @pPatientVisitId
 

                     IF ( @CurrentInsuranceCarrierId <> 0 ) 

                        EXEC gGetDoctorForClaimsId @DoctorId OUT , @FacilityId , @CompanyId , @CurrentInsuranceCarrierId , @SupervisingPhysicianId
 
 

                     SELECT

                        '120.Diagnosis1' = UPPER(pvd.ICD9Code)

                     FROM

                        PatientVisitDiags pvd

                     WHERE

                        PatientVisitId = @pPatientVisitId

                        AND ListOrder = 1	

                     SELECT

                        '121.Diagnosis2' = UPPER(pvd.ICD9Code)

                     FROM

                        PatientVisitDiags pvd

                     WHERE

                        PatientVisitId = @pPatientVisitId

                        AND ListOrder = 2

                     SELECT

                        '122.Diagnosis3' = UPPER(pvd.ICD9Code)

                     FROM

                        PatientVisitDiags pvd

                     WHERE

                        PatientVisitId = @pPatientVisitId

                        AND ListOrder = 3

                     SELECT

                        '123.Diagnosis4' = UPPER(pvd.ICD9Code)

                     FROM

                        PatientVisitDiags pvd

                     WHERE

                        PatientVisitId = @pPatientVisitId

                        AND ListOrder = 4

                     SELECT

                        '124.Diagnosis5' = UPPER(pvd.ICD9Code)

                     FROM

                        PatientVisitDiags pvd

                     WHERE

                        PatientVisitId = @pPatientVisitId

                        AND ListOrder = 5

                     SELECT

                        '125.Diagnosis6' = UPPER(pvd.ICD9Code)

                     FROM

                        PatientVisitDiags pvd

                     WHERE

                        PatientVisitId = @pPatientVisitId

                        AND ListOrder = 6

                     SELECT

                        '126.Diagnosis7' = UPPER(pvd.ICD9Code)

                     FROM

                        PatientVisitDiags pvd

                     WHERE

                        PatientVisitId = @pPatientVisitId

                        AND ListOrder = 7

                     SELECT

                        '127.Diagnosis8' = UPPER(pvd.ICD9Code)

                     FROM

                        PatientVisitDiags pvd

                     WHERE

                        PatientVisitId = @pPatientVisitId

                        AND ListOrder = 8

                     SELECT

                        '128.Diagnosis9' = UPPER(pvd.ICD9Code)

                     FROM

                        PatientVisitDiags pvd

                     WHERE

                        PatientVisitId = @pPatientVisitId

                        AND ListOrder = 9  
 

                     SELECT

                        '129.Diagnosis10' = UPPER(pvd.ICD9Code)

                     FROM

                        PatientVisitDiags pvd

                     WHERE

                        PatientVisitId = @pPatientVisitId

                        AND ListOrder = 10

                     SELECT

                        '130.Diagnosis11' = UPPER(pvd.ICD9Code)

                     FROM

                        PatientVisitDiags pvd

                     WHERE

                        PatientVisitId = @pPatientVisitId

                        AND ListOrder = 11 

                     SELECT

                        '131.Diagnosis12' = UPPER(pvd.ICD9Code)

                     FROM

                        PatientVisitDiags pvd

                     WHERE

                        PatientVisitId = @pPatientVisitId

                        AND ListOrder = 12 

                     SELECT

                        '132.Diagnosis13' = UPPER(pvd.ICD9Code)

                     FROM

                        PatientVisitDiags pvd

                     WHERE

                        PatientVisitId = @pPatientVisitId

                        AND ListOrder = 13 

                     SELECT

                        '133.Diagnosis14' = UPPER(pvd.ICD9Code)

                     FROM

                        PatientVisitDiags pvd

                     WHERE

                        PatientVisitId = @pPatientVisitId

                        AND ListOrder = 14 

                     SELECT

                        '134.Diagnosis15' = UPPER(pvd.ICD9Code)

                     FROM

                        PatientVisitDiags pvd

                     WHERE

                        PatientVisitId = @pPatientVisitId

                        AND ListOrder = 15 

                     SELECT

                        '135.Diagnosis16' = UPPER(pvd.ICD9Code)

                     FROM

                        PatientVisitDiags pvd

                     WHERE

                        PatientVisitId = @pPatientVisitId

                        AND ListOrder = 16 

                     SELECT

                        '136.Diagnosis17' = UPPER(pvd.ICD9Code)

                     FROM

                        PatientVisitDiags pvd

                     WHERE

                        PatientVisitId = @pPatientVisitId

                        AND ListOrder = 17 
 

 

                     SELECT

                        '157.Date' = GETDATE()

                     SELECT

                        '158.TotalCharge' = @TotalCharge ,

                        '159.TotalUnits' = @TotalUnits ,

                        '156.TotalNonCoveredCharge' = @TotalNonCoveredCharge
 

                     SELECT

                        '160.Proc1RevenueCode' = UPPER(RevenueCode) ,

                        '161.Proc1Description' = Description ,

                        '162.Proc1CPTCode' = UPPER(CPTCode) ,

                        '163.Proc1ServiceDate' = DateOfServiceFrom ,

                        '164.Proc1Units' = Units ,

                        '165.Proc1Charge' = Charge ,

                        '166.Proc1NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 1

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

                     SELECT

                        '170.Proc2RevenueCode' = UPPER(RevenueCode) ,

                        '171.Proc2Description' = Description ,

                        '172.Proc2CPTCode' = UPPER(CPTCode) ,

                        '173.Proc2ServiceDate' = DateOfServiceFrom ,

                        '174.Proc2Units' = Units ,

                        '175.Proc2Charge' = Charge ,

                        '176.Proc2NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 2

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

                     SELECT

                        '180.Proc3RevenueCode' = UPPER(RevenueCode) ,

                        '181.Proc3Description' = Description ,

                        '182.Proc3CPTCode' = UPPER(CPTCode) ,

                        '183.Proc3ServiceDate' = DateOfServiceFrom ,

                        '184.Proc3Units' = Units ,

                        '185.Proc3Charge' = Charge ,

                        '186.Proc3NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 3

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

                     SELECT

                        '190.Proc4RevenueCode' = UPPER(RevenueCode) ,

                        '191.Proc4Description' = Description ,

                        '192.Proc4CPTCode' = UPPER(CPTCode) ,

                        '193.Proc4ServiceDate' = DateOfServiceFrom ,

                        '194.Proc4Units' = Units ,

                        '195.Proc4Charge' = Charge ,

                        '196.Proc4NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 4

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

                     SELECT

                        '200.Proc5RevenueCode' = UPPER(RevenueCode) ,

                        '201.Proc5Description' = Description ,

                        '202.Proc5CPTCode' = UPPER(CPTCode) ,

                        '203.Proc5ServiceDate' = DateOfServiceFrom ,

                        '204.Proc5Units' = Units ,

                        '205.Proc5Charge' = Charge ,

                        '206.Proc5NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 5

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

                     SELECT

                        '210.Proc6RevenueCode' = UPPER(RevenueCode) ,

                        '211.Proc6Description' = Description ,

                        '212.Proc6CPTCode' = UPPER(CPTCode) ,

                        '213.Proc6ServiceDate' = DateOfServiceFrom ,

                        '214.Proc6Units' = Units ,

                        '215.Proc6Charge' = Charge ,

                        '216.Proc6NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 6

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

                     SELECT

                        '220.Proc7RevenueCode' = UPPER(RevenueCode) ,

                        '221.Proc7Description' = Description ,

                        '222.Proc7CPTCode' = UPPER(CPTCode) ,

                        '223.Proc7ServiceDate' = DateOfServiceFrom ,

                        '224.Proc7Units' = Units ,

                        '225.Proc7Charge' = Charge ,

                        '226.Proc7NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 7

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

                     SELECT

                        '230.Proc8RevenueCode' = UPPER(RevenueCode) ,

                        '231.Proc8Description' = Description ,

                        '232.Proc8CPTCode' = UPPER(CPTCode) ,

                        '233.Proc8ServiceDate' = DateOfServiceFrom ,

                        '234.Proc8Units' = Units ,

                        '235.Proc8Charge' = Charge ,

                        '236.Proc8NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 8

                        AND UniqueIdentifierId = @UniqueIdentifierId
 
 

                     SELECT

                        '240.Proc9RevenueCode' = UPPER(RevenueCode) ,

                        '241.Proc9Description' = Description ,

                        '242.Proc9CPTCode' = UPPER(CPTCode) ,

                        '243.Proc9ServiceDate' = DateOfServiceFrom ,

                        '244.Proc9Units' = Units ,

                        '245.Proc9Charge' = Charge ,

                        '246.Proc9NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 9

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

                     SELECT

                        '250.Proc10RevenueCode' = UPPER(RevenueCode) ,

                        '251.Proc10Description' = Description ,

                        '252.Proc10CPTCode' = UPPER(CPTCode) ,

                        '253.Proc10ServiceDate' = DateOfServiceFrom ,

                        '254.Proc10Units' = Units ,

                        '255.Proc10Charge' = Charge ,

                        '256.Proc10NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 10

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

                     SELECT

                        '260.Proc11RevenueCode' = UPPER(RevenueCode) ,

                        '261.Proc11Description' = Description ,

                        '262.Proc11CPTCode' = UPPER(CPTCode) ,

                        '263.Proc11ServiceDate' = DateOfServiceFrom ,

                        '264.Proc11Units' = Units ,

                        '265.Proc11Charge' = Charge ,

                        '266.Proc11NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 11

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

                     SELECT

                        '270.Proc12RevenueCode' = UPPER(RevenueCode) ,

                        '271.Proc12Description' = Description ,

                        '272.Proc12CPTCode' = UPPER(CPTCode) ,

                        '273.Proc12ServiceDate' = DateOfServiceFrom ,

                        '274.Proc12Units' = Units ,

                        '275.Proc12Charge' = Charge ,

                        '276.Proc12NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 12

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

                     SELECT

                        '280.Proc13RevenueCode' = UPPER(RevenueCode) ,

                        '281.Proc13Description' = Description ,

                        '282.Proc13CPTCode' = UPPER(CPTCode) ,

                        '283.Proc13ServiceDate' = DateOfServiceFrom ,

                        '284.Proc13Units' = Units ,

                        '285.Proc13Charge' = Charge ,

                        '286.Proc13NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 13

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

                     SELECT

                        '290.Proc14RevenueCode' = UPPER(RevenueCode) ,

                        '291.Proc14Description' = Description ,

                        '292.Proc14CPTCode' = UPPER(CPTCode) ,

                        '293.Proc14ServiceDate' = DateOfServiceFrom ,

                        '294.Proc14Units' = Units ,

                        '295.Proc14Charge' = Charge ,

                        '296.Proc14NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 14

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

                     SELECT

                        '300.Proc15RevenueCode' = UPPER(RevenueCode) ,

                        '301.Proc15Description' = Description ,

                        '302.Proc15CPTCode' = UPPER(CPTCode) ,

                        '303.Proc15ServiceDate' = DateOfServiceFrom ,

                        '304.Proc15Units' = Units ,

                        '305.Proc15Charge' = Charge ,

                        '306.Proc15NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 15

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

                     SELECT

                        '310.Proc16RevenueCode' = UPPER(RevenueCode) ,

                        '311.Proc16Description' = Description ,

                        '312.Proc16CPTCode' = UPPER(CPTCode) ,

                        '313.Proc16ServiceDate' = DateOfServiceFrom ,

                        '314.Proc16Units' = Units ,

                        '315.Proc16Charge' = Charge ,

                        '316.Proc16NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 16

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

                     SELECT

                        '320.Proc17RevenueCode' = UPPER(RevenueCode) ,

                        '321.Proc17Description' = Description ,

                        '322.Proc17CPTCode' = UPPER(CPTCode) ,

                        '323.Proc17ServiceDate' = DateOfServiceFrom ,

                        '324.Proc17Units' = Units ,

                        '325.Proc17Charge' = Charge ,

                        '326.Proc17NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 17

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

                     SELECT

                        '330.Proc18RevenueCode' = UPPER(RevenueCode) ,

                        '331.Proc18Description' = Description ,

                        '332.Proc18CPTCode' = UPPER(CPTCode) ,

                        '333.Proc18ServiceDate' = DateOfServiceFrom ,

                        '334.Proc18Units' = Units ,

                        '335.Proc18Charge' = Charge ,

                        '336.Proc18NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 18

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

                     SELECT

                        '340.Proc19RevenueCode' = UPPER(RevenueCode) ,

                        '341.Proc19Description' = Description ,

                        '342.Proc19CPTCode' = UPPER(CPTCode) ,

                        '343.Proc19ServiceDate' = DateOfServiceFrom ,

                        '344.Proc19Units' = Units ,

                        '345.Proc19Charge' = Charge ,

                        '346.Proc19NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 19

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

                     SELECT

                        '350.Proc20RevenueCode' = UPPER(RevenueCode) ,

                        '351.Proc20Description' = Description ,

                        '352.Proc20CPTCode' = UPPER(CPTCode) ,

                        '353.Proc20ServiceDate' = DateOfServiceFrom ,

                        '354.Proc20Units' = Units ,

                        '355.Proc20Charge' = Charge ,

                        '356.Proc20NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 20

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

                     SELECT

                        '360.Proc21RevenueCode' = UPPER(RevenueCode) ,

                        '361.Proc21Description' = Description ,

                        '362.Proc21CPTCode' = UPPER(CPTCode) ,

                        '363.Proc21ServiceDate' = DateOfServiceFrom ,

                        '364.Proc21Units' = Units ,

                        '365.Proc21Charge' = Charge ,

                        '366.Proc21NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 21

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

                     SELECT

                        '370.Proc22RevenueCode' = UPPER(RevenueCode) ,

                        '371.Proc22Description' = Description ,

                        '372.Proc22CPTCode' = UPPER(CPTCode) ,

                        '373.Proc22ServiceDate' = DateOfServiceFrom ,

                        '374.Proc22Units' = Units ,

                        '375.Proc22Charge' = Charge ,

                        '376.Proc22NonCoveredAmount' = NonCoveredAmount

                     FROM

                        gtProcedures

                     WHERE

                        Output = 22

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

/* --- Record the printing on the claims tab of the visit component --- */

                     DECLARE @ProceduresPrinted VARCHAR(255)

                     DECLARE @CarrierName VARCHAR(255)
 

                     SELECT

                        @ProceduresPrinted = ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 1

                        AND UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted) + '  ' + ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 2

                        AND UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted) + '  ' + ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 3

                        AND UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted) + '  ' + ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 4

                        AND UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted) + '  ' + ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 5

                        AND UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted) + '  ' + ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 6

                        AND UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted) + '  ' + ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 7

                        AND UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted) + '  ' + ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 8

                        AND UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted) + '  ' + ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 9

                        AND UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted) + '  ' + ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 10

                        AND UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted) + '  ' + ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 11

                        AND UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted) + '  ' + ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 12

                        AND UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted) + '  ' + ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 13

                        AND UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted) + '  ' + ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 14

                        AND UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted) + '  ' + ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 15

                        AND UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted) + '  ' + ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 16

                        AND UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted) + '  ' + ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 17

                        AND UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted) + '  ' + ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 18

                        AND UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted) + '  ' + ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 19

                        AND UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted) + '  ' + ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 20

                        AND UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted) + '  ' + ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 21

                        AND UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted) + '  ' + ISNULL(CPTCode , '')

                     FROM

                        gtProcedures

                     WHERE

                        Output = 22

                        AND UniqueIdentifierId = @UniqueIdentifierId
 

                     SELECT

                        @ProceduresPrinted = RTRIM(@ProceduresPrinted)
 

                     SELECT

                        @CarrierName = Name

                     FROM

                        InsuranceCarriers

                     WHERE

                        InsuranceCarriersId = @CurrentInsuranceCarriersId
 

                     INSERT

                        PatientVisitPaperClaim

                        (

                          PatientVisitid ,

                          FilingMethodMId ,

                          Charges ,

                          Procedures ,

                          Name ,

                          Created ,

                          CreatedBy ,

                          LastModified ,

                          LastModifiedBy

	                  )

                        SELECT

                            @pPatientVisitId ,

                            NULL ,

                            @TotalCharge ,

                            @ProceduresPrinted ,

                            @CarrierName ,

                            GETDATE() ,

                            ' ' ,

                            GETDATE() ,

                            ' '

/* --- End of recording printing on the claims tab of the visit component --- */
 
 

                     SELECT

                        '410.CoveredPeriodFrom' = pvf.CoveredPeriodFrom ,

                        '411.CoveredPeriodTo' = pvf.CoveredPeriodTo ,

                        '412.CoverageDays' = pvf.CoverageDays ,

                        '413.NonCoveredDays' = pvf.NonCoveredDays ,

                        '414.CoInsuranceDays' = pvf.CoInsuranceDays ,

                        '415.LifetimeReserveDays' = pvf.LifetimeReserveDays ,

                        '416.AdmissionDate' = pvf.HospitalizationFrom ,

                        '417.AdmissionHour' = CONVERT(VARCHAR(2) , pvf.AdmissionTime , 108) ,

                        '418.AdmissionType' = adtype.Code ,

                        '419.AdmissionSource' = adsource.Code ,

                        '420.DischargeHour' = CONVERT(VARCHAR(2) , pvf.DischargeTime , 108) ,

                        '421.PatientStatus' = patstatus.Code ,

                        '422.AdmittingDiagnosis' = d1.ICD9Code ,

                        '423.ExternalInjuryCode' = d2.ICD9Code

                     FROM

                        PatientVisitFiling pvf

                        LEFT JOIN Medlists adtype ON pvf.AdmissionTypeMId = adtype.MedlistsId

                        LEFT JOIN Medlists adsource ON pvf.AdmissionSourceMId = adsource.MedlistsId

                        LEFT JOIN Medlists patstatus ON pvf.PatientStatusMId = patstatus.MedlistsId

                        LEFT JOIN Diagnosis d1 ON pvf.AdmittingDiagnosis = d1.DiagnosisId

                        LEFT JOIN Diagnosis d2 ON pvf.ExternalInjuryCode = d2.DiagnosisId

                     WHERE

                        PatientVisitId = @pPatientVisitId
 

                     EXEC master..mbcxp_SortConditionCodes80 @DBName , @pPatientVisitId , 425
 

                     EXEC master..mbcxp_SortOccurrenceCodes80 @DBName , @pPatientVisitId , 435
 

                     EXEC master..mbcxp_SortValueCodes80 @DBName , @pPatientVisitId , 465
 
 

                     SELECT

                        '451.OccurrenceCodeSpan0' = oc8.Code ,

                        '452.OccurrenceCodeSpan1' = oc9.Code ,

                        '453.OccurrenceCodeSpan0From' = pvf.OccurrenceCodeSpan0From ,

                        '454.OccurrenceCodeSpan0To' = pvf.OccurrenceCodeSpan0To ,

                        '455.OccurrenceCodeSpan1From' = pvf.OccurrenceCodeSpan1From ,

                        '456.OccurrenceCodeSpan1To' = pvf.OccurrenceCodeSpan1To

                     FROM

                        PatientVisitFiling pvf

                        LEFT JOIN Medlists oc8 ON pvf.OccurrenceCodeSpan0MId = oc8.MedlistsId

                        LEFT JOIN Medlists oc9 ON pvf.OccurrenceCodeSpan1MId = oc9.MedlistsId

                     WHERE

                        PatientVisitId = @pPatientVisitId
 

                     SELECT

                        '457.DocumentControlNumber1' = UPPER(pvi.ICN)

                     FROM

                        PatientVisitInsurance pvi

                     WHERE

                        PatientVisitId = @pPatientVisitId

                        AND OrderForClaims = 1
 

                     SELECT

                        '458.DocumentControlNumber2' = UPPER(pvi.ICN)

                     FROM

                        PatientVisitInsurance pvi

                     WHERE

                        PatientVisitId = @pPatientVisitId

                        AND OrderForClaims = 2
 

                     SELECT

                        '459.DocumentControlNumber3' = UPPER(pvi.ICN)

                     FROM

                        PatientVisitInsurance pvi

                     WHERE

                        PatientVisitId = @pPatientVisitId

                        AND OrderForClaims = 3
 
 

                     SELECT

                        '460.OriginalReferenceNumber' = pvi.ICN ,

                        '461.ResubmissionCode' = pvf.ResubmissionCode ,

                        '462.TreatmentAuthorization0' = pvf.TreatmentAuthorization0 ,

                        '463.TreatmentAuthorization1' = pvf.TreatmentAuthorization1 ,

                        '464.TreatmentAuthorization2' = pvf.TreatmentAuthorization2

                     FROM

                        PatientVisitFiling pvf

                        INNER JOIN PatientVisit pv ON pvf.PatientVisitId = pv.PatientVisitId

                        LEFT JOIN PatientVisitInsurance pvi ON pv.PatientVisitId = pvi.PatientVisitId

                                                               AND pvi.OrderForClaims = pv.CurrentCarrier

                     WHERE

                        pvf.PatientVisitId = @pPatientVisitId
 
 
 

                     SELECT

                        '495.OtherProcedure0' = p0.CPTCode ,

                        '496.OtherProcedure1' = p1.CPTCode ,

                        '497.OtherProcedure2' = p2.CPTCode ,

                        '498.OtherProcedure3' = p3.CPTCode ,

                        '499.OtherProcedure4' = p4.CPTCode ,

                        '500.OtherProcedure5' = p5.CPTCode ,

                        '501.OtherProcedure0Date' = pvf.OtherProcedure0Date ,

                        '502.OtherProcedure1Date' = pvf.OtherProcedure1Date ,

                        '503.OtherProcedure2Date' = pvf.OtherProcedure2Date ,

                        '504.OtherProcedure3Date' = pvf.OtherProcedure3Date ,

                        '505.OtherProcedure4Date' = pvf.OtherProcedure4Date ,

                        '506.OtherProcedure5Date' = pvf.OtherProcedure5Date

                     FROM

                        PatientVisitFiling pvf

                        LEFT JOIN Procedures p0 ON pvf.OtherProcedure0 = p0.ProceduresId

                        LEFT JOIN Procedures p1 ON pvf.OtherProcedure1 = p1.ProceduresId

                        LEFT JOIN Procedures p2 ON pvf.OtherProcedure2 = p2.ProceduresId

                        LEFT JOIN Procedures p3 ON pvf.OtherProcedure3 = p3.ProceduresId

                        LEFT JOIN Procedures p4 ON pvf.OtherProcedure4 = p4.ProceduresId

                        LEFT JOIN Procedures p5 ON pvf.OtherProcedure5 = p5.ProceduresId

                     WHERE

                        PatientVisitId = @pPatientVisitId
 

                     EXEC gGetDoctorFacilityIds @DoctorId , @pPatientVisitId , @FederalTaxId OUT , @FederalTaxIdType OUT , @FileAsGroup OUT , @Id_Facility OUT ,

                          @Id_PIN OUT , @Id_PINGRP OUT , @Id_GRP OUT , @Id_EMCProv OUT , @Id_CLIA OUT , @Id_Referring OUT , @Id_RefOffice OUT ,

                          @Id_ProviderType OUT , @PlaceOfServiceMId OUT , @Id_Mammography OUT , @pld_Id_PINTypeANSI OUT , @pld_Id_FacilityTypeAnsi OUT ,

                          @pld_Id_GRPTypeAnsi OUT , @pld_Id_EMCProvTypeAnsi OUT , @pld_Id_ProviderTypeTypeAnsi OUT , @pld_Id_CLIATypeAnsi OUT ,

                          @pld_Id_MammographyTypeAnsi OUT , @pld_Id_ReferringTypeAnsi OUT , @pldType_Facility OUT , @pldType_Doc OUT ,

                          @pldAddressSameAsCompany OUT , @NPI OUT

    
 

                     SELECT

                        '31.FederalTaxId' = UPPER(@FederalTaxId)
 
 

                     SELECT

                        '140.ProviderLast' = UPPER(ISNULL(df.Last , '')) ,

                        '141.ProviderFirst' = UPPER(ISNULL(df.First , '')) ,

                        '142.ProviderMiddle' = UPPER(ISNULL(df.Middle , '')) ,

                        '143.ProviderSuffix' = UPPER(ISNULL(df.Suffix , ''))

                     FROM

                        DoctorFacility df

                     WHERE

                        df.DoctorFacilityId = @DoctorId
 

                     SELECT

                        @NPI_Provider = ''

                     EXEC gGetDoctorFacilityIds_All @DoctorId OUT , @FacilityId , @CompanyId , @CurrentInsuranceCarriersId , @FederalTaxId OUT ,

                          @FederalTaxIdType OUT , @FileAsGroup OUT , @Id_Facility OUT , @Id_PIN OUT , @Id_PINGRP OUT , @Id_GRP OUT , @Id_EMCProv OUT ,

                          @Id_CLIA OUT , @Id_Referring OUT , @Id_ProviderType OUT , @PlaceOfServiceMId OUT , @Id_Mammography OUT , @SupervisingPhysicianId ,

                          @FilesFacilityFees , @NPI_Provider OUT
 

                     SELECT

                        '144.ProviderNPI' = UPPER(@NPI_Provider) 
 

	/* Get the Facility Id */

                     SELECT

                        @NPI_Facility = ''

                     EXEC gGetDoctorFacilityIds_All @FacilityId OUT , @FacilityId , @CompanyId , @CurrentInsuranceCarriersId , @FederalTaxId OUT ,

                          @FederalTaxIdType OUT , @FileAsGroup OUT , @Id_Facility OUT , @Id_PIN OUT , @Id_PINGRP OUT , @Id_GRP OUT , @Id_EMCProv OUT ,

                          @Id_CLIA OUT , @Id_Referring OUT , @Id_ProviderType OUT , @PlaceOfServiceMId OUT , @Id_Mammography OUT , @SupervisingPhysicianId ,

                          @FilesFacilityFees , @NPI_Facility OUT
 

                     SELECT

                        '145.FacilityNPI' = UPPER(@NPI_Facility) 
 
 

  /* Referring */

                     EXEC gGetDoctorFacilityIds_Referring @ReferringId , @FacilityId , @CompanyId , @CurrentInsuranceCarriersId , @Id_Referring OUT ,

                          @Id_ProviderType OUT , @Id_Office OUT , @unused OUT , @unused OUT , @unused OUT , @unused OUT , @unused OUT , @unused OUT ,

                          @unused OUT , @unused OUT , @FederalTaxId OUT , @FederalTaxIdType OUT , @unused OUT , @unused OUT , @unused OUT , @unused OUT ,

                          @unused OUT , @NPI OUT
 

                     SELECT

                        '520.ReferringLast' = UPPER(ISNULL(df.Last , '')) ,

                        '521.ReferringFirst' = UPPER(ISNULL(df.First , '')) ,

                        '522.ReferringMiddle' = UPPER(ISNULL(df.Middle , '')) ,

                        '523.ReferringSuffix' = UPPER(ISNULL(df.Suffix , '')) ,

                        '524.ReferringId' = UPPER(@Id_Referring)

                     FROM

                        DoctorFacility df

                     WHERE

                        df.DoctorFacilityId = @ReferringId
 
 
 

  /* Attending */

                     EXEC gGetDoctorFacilityIds_Referring @AttendingId , @FacilityId , @CompanyId , @CurrentInsuranceCarriersId , @Id_Referring OUT ,

                          @Id_ProviderType OUT , @Id_Office OUT , @unused OUT , @unused OUT , @pld_Id_PINTypeANSI OUT , @unused OUT , @unused OUT , @unused OUT ,

                          @unused OUT , @unused OUT , @FederalTaxId OUT , @FederalTaxIdType OUT , @unused OUT , @unused OUT , @unused OUT , @unused OUT ,

                          @unused OUT , @NPI OUT
 

                     SELECT

                        '535.AttendingLast' = UPPER(ISNULL(df.Last , '')) ,

                        '536.AttendingFirst' = UPPER(ISNULL(df.First , '')) ,

                        '537.AttendingMiddle' = UPPER(ISNULL(df.Middle , '')) ,

                        '538.AttendingSuffix' = UPPER(ISNULL(df.Suffix , '')) ,

                        '539.AttendingId' = UPPER(@Id_Referring) ,

                        '540.AttendingName' = UPPER(dbo.FormatName(df.Prefix , df.First , df.Middle , df.Last , df.Suffix)) ,

                        '541.AttendingNPI' = UPPER(@NPI) ,

                        '542.AttendingANSIPIN' = UPPER(@pld_Id_PINTypeANSI)

                     FROM

                        DoctorFacility df

                     WHERE

                        df.DoctorFacilityId = @AttendingId
 

/*Changes for Box 56*/

	--Find the Current InsuranceGroupId

                     SELECT

                        @InsuranceGroupId = 0

                     SELECT

                        @InsuranceGroupId = ISNULL(ic.InsuranceGroupId , 0)

                     FROM

                        InsuranceCarriers ic

                     WHERE

                        ic.InsuranceCarriersId = @CurrentInsuranceCarriersId
 

	--Find out if the Attending Doc is Filling Individual or as a Group for this InsuranceCarrier.

                     SELECT

                        @AttendingFileAsGroup = 0

                     SELECT

                        @AttendingFileAsGroup = ISNULL(dfid.FileAsGroup , 0)

                     FROM

                        DoctorFacility df

                        LEFT JOIN DFIDS dfid ON dfid.DoctorFacilityId = @AttendingId

                                                AND --@pDoctorFacilityId AND

                                                (

                                                  dfid.CompanyId = @CompanyId

                                                  OR (

                                                       dfid.CompanyId IS NULL

                                                       AND @CompanyId = @CompanyId

                                                     )

                                                )

                                                AND (

                                                      dfid.FacilityId = @FacilityId

                                                      OR (

                                                           dfid.FacilityId IS NULL

                                                           AND @FacilityId = @FacilityId

                                                         )

                                                    )

                                                AND ISNULL(dfid.InsuranceCarriersId , ISNULL(@CurrentInsuranceCarriersId , 0)) = ISNULL(@CurrentInsuranceCarriersId ,

                                                                                                                                        0)

                                                AND ISNULL(dfid.InsuranceGroupId , ISNULL(@InsuranceGroupId , 0)) = ISNULL(@InsuranceGroupId , 0)

                     WHERE

                        df.DoctorFacilityId = @AttendingId
 

                     SELECT

                        '600.FilesFacilityFees' = CASE WHEN @FilesFacilityFees <> 0 THEN 1

                                                       ELSE @FilesFacilityFees

                                                  END ,

                        '601.AttendingFileAsGroup' = @AttendingFileAsGroup

                     SELECT TOP 1

                        '602.CompanyNPI' = UPPER(CASE WHEN dfid.NPI = '' THEN df.NPI

                                                      WHEN dfid.NPI IS NULL THEN df.NPI

                                                      ELSE dfid.NPI

                                                 END) ,

                        '603.CompanyTaxonomy' = UPPER(dfid.Id_AdditionalID1)

                     FROM

                        DFIDS dfid

                        LEFT JOIN DoctorFacility df ON df.DoctorFacilityID = @CompanyId --AND	

                     WHERE

                        dfid.DoctorFacilityId = @CompanyId

                        AND (

                              dfid.FacilityId = @FacilityId

                              OR dfid.FacilityId IS NULL

                            )

                        AND (

                              dfid.InsuranceCarriersId = @CurrentInsuranceCarriersId

                              OR dfid.InsuranceCarriersId IS NULL

                            )

                        AND (

                              dfid.InsuranceGroupId = @InsuranceGroupId

                              OR dfid.InsuranceGroupId IS NULL

                            )

                     ORDER BY

                        dfid.CompanyId DESC ,

                        dfid.FacilityId DESC ,

                        dfid.InsuranceCarriersId DESC ,

                        dfid.InsuranceGroupId DESC
 
 

  /* Admitting */

                     EXEC gGetDoctorFacilityIds_Referring @AdmittingId , @FacilityId , @CompanyId , @CurrentInsuranceCarriersId , @Id_Referring OUT ,

                          @Id_ProviderType OUT , @Id_Office OUT , @unused OUT , @unused OUT , @pld_Id_PINTypeANSI OUT , @unused OUT , @unused OUT , @unused OUT ,

                          @unused OUT , @unused OUT , @FederalTaxId OUT , @FederalTaxIdType OUT , @unused OUT , @unused OUT , @unused OUT , @unused OUT ,

                          @unused OUT , @NPI OUT
 

                     SELECT

                        '545.AdmittingLast' = UPPER(ISNULL(df.Last , '')) ,

                        '546.AdmittingFirst' = UPPER(ISNULL(df.First , '')) ,

                        '547.AdmittingMiddle' = UPPER(ISNULL(df.Middle , '')) ,

                        '548.AdmittingSuffix' = UPPER(ISNULL(df.Suffix , '')) ,

                        '549.AdmittingId' = UPPER(@Id_Referring) ,

                        '550.AdmittingName' = UPPER(dbo.FormatName(df.Prefix , df.First , df.Middle , df.Last , df.Suffix)) ,

                        '551.AdmittingNPI' = UPPER(@NPI) ,

                        '552.AdmittingANSIPIN' = UPPER(@pld_Id_PINTypeANSI)

                     FROM

                        DoctorFacility df

                     WHERE

                        df.DoctorFacilityId = @AdmittingId
 

  /* Operating */

                     EXEC gGetDoctorFacilityIds_Referring @OperatingId , @FacilityId , @CompanyId , @CurrentInsuranceCarriersId , @Id_Referring OUT ,

                          @Id_ProviderType OUT , @Id_Office OUT , @unused OUT , @unused OUT , @pld_Id_PINTypeANSI OUT , @unused OUT , @unused OUT , @unused OUT ,

                          @unused OUT , @unused OUT , @FederalTaxId OUT , @FederalTaxIdType OUT , @unused OUT , @unused OUT , @unused OUT , @unused OUT ,

                          @unused OUT , @NPI OUT
 

                     SELECT

                        '555.OperatingLast' = UPPER(ISNULL(df.Last , '')) ,

                        '556.OperatingFirst' = UPPER(ISNULL(df.First , '')) ,

                        '557.OperatingMiddle' = UPPER(ISNULL(df.Middle , '')) ,

                        '558.OperatingSuffix' = UPPER(ISNULL(df.Suffix , '')) ,

                        '559.OperatingId' = UPPER(@Id_Referring) ,

                        '560.OperatingName' = UPPER(dbo.FormatName(df.Prefix , df.First , df.Middle , df.Last , df.Suffix)) ,

                        '561.OperatingNPI' = UPPER(@NPI) ,

                        '562.OperatingPINANSI' = UPPER(@pld_Id_PINTypeANSI)

                     FROM

                        DoctorFacility df

                     WHERE

                        df.DoctorFacilityId = @OperatingId
 

  /* Supervising */

                     EXEC gGetDoctorFacilityIds_Referring @SupervisingId , @FacilityId , @CompanyId , @CurrentInsuranceCarriersId , @Id_Referring OUT ,

                          @Id_ProviderType OUT , @Id_Office OUT , @unused OUT , @unused OUT , @pld_Id_PINTypeANSI OUT , @unused OUT , @unused OUT , @unused OUT ,

                          @unused OUT , @unused OUT , @FederalTaxId OUT , @FederalTaxIdType OUT , @unused OUT , @unused OUT , @unused OUT , @unused OUT ,

                          @unused OUT , @NPI OUT
 

                     SELECT

                        '565.SupervisingLast' = UPPER(ISNULL(df.Last , '')) ,

                        '566.SupervisingFirst' = UPPER(ISNULL(df.First , '')) ,

                        '567.SupervisingMiddle' = UPPER(ISNULL(df.Middle , '')) ,

                        '568.SupervisingSuffix' = UPPER(ISNULL(df.Suffix , '')) ,

                        '569.SupervisingId' = UPPER(@Id_Referring) ,

                        '570.SupervisingName' = UPPER(dbo.FormatName(df.Prefix , df.First , df.Middle , df.Last , df.Suffix)) ,

                        '571.SupervisingNPI' = UPPER(@NPI) ,

                        '572.SupervisingPINANSI' = UPPER(@pld_Id_PINTypeANSI)

                     FROM

                        DoctorFacility df

                     WHERE

                        df.DoctorFacilityId = @SupervisingId
 

                     IF (

                          SELECT

                            CONVERT(VARCHAR(249) , pvf.HCFANotes)

                          FROM

                            PatientVisitFiling pvf

                          WHERE

                            PatientVisitId = @pPatientVisitId

                        ) IS NOT NULL

                        AND (

                              SELECT

                                pvf.PrintOnPaperClaims

                              FROM

                                PatientVisitFiling pvf

                              WHERE

                                PatientVisitId = @pPatientVisitId

                            ) = 1 

                        BEGIN

                              SELECT

                                '575.Remarks' = '        ' + CONVERT(VARCHAR(249) , pvf.HCFANotes)

                              FROM

                                PatientVisitFiling pvf

                              WHERE

                                PatientVisitId = @pPatientVisitId

                        END

                     ELSE 

                        BEGIN

                              SELECT

                                '575.Remarks' = '        ' + CONVERT(VARCHAR(249) , UPPER(ic.Name) + CHAR(13) + CHAR(10) + UPPER(ISNULL(ic.Address1 , '')) + ' '

                                + UPPER(ISNULL(ic.Address2 , '')) + CHAR(13) + CHAR(10) + UPPER(ISNULL(ic.City , '')) + ' ' + UPPER(ISNULL(ic.State , '')) + ' '

                                + ISNULL(SUBSTRING(ic.Zip , 1 , 5) + ' ' + SUBSTRING(ic.Zip , 7 , 4) , ''))

                              FROM

                                InsuranceCarriers ic

                              WHERE

                                InsuranceCarriersId = @CurrentInsuranceCarriersId

                        END
 
 

                     SELECT

                        '580.InsuranceCarrierName' = UPPER(ic.Name) ,

                        '581.InsuranceCarrierAddress1' = UPPER(ISNULL(ic.Address1 , '')) ,

                        '582.InsuranceCarrierAddress2' = UPPER(ISNULL(ic.Address2 , '')) ,

                        '583.InsuranceCarrierCity' = UPPER(ISNULL(ic.City , '')) ,

                        '584.InsuranceCarrierState' = UPPER(ISNULL(ic.State , '')) ,

                        '585.InsuranceCarrierZip' = ISNULL(SUBSTRING(ic.Zip , 1 , 5) + ' ' + SUBSTRING(ic.Zip , 7 , 4) , '')

                     FROM

                        InsuranceCarriers ic

                     WHERE

                        InsuranceCarriersId = @CurrentInsuranceCarriersId

    

	/* A unique row identifier for Crystal to page break on */

                     SELECT

                        '400.RowId' = ISNULL(ic.Name , '') + CONVERT(VARCHAR , @pPatientVisitId) + CONVERT(VARCHAR , @pSkipProc)

                     FROM

                        PatientVisit pv ,

                        InsuranceCarriers ic

                     WHERE

                        pv.PatientVisitId = @pPatientVisitId

                        AND pv.CurrentInsuranceCarriersId = ic.InsuranceCarriersId

--- JAS 03/04/08

                     SELECT

                        '401.Page_Number' = CONVERT(VARCHAR(8) , @PageBreak)

                     SELECT

                        @TotalProceduresPerClaim = COUNT(PatientVisitProcsId)

                     FROM

                        gtProcedures

                     WHERE

                        UniqueIdentifierId = @UniqueIdentifierId

                     SELECT

                        @TotalPagesPerClaim = CEILING(@TotalProceduresPerClaim / @NumProcedureRows)

                     SELECT

                        '402.TotalPagesPerClaim' = CONVERT(VARCHAR(8) , @TotalPagesPerClaim)

               END
 

            IF ( (

                   SELECT

                    COUNT(PatientVisitProcsId) - @pSkipProc - @ProceduresOutput

                   FROM

                    gtProcedures

                   WHERE

                    UniqueIdentifierId = @UniqueIdentifierId

                 ) > 0 ) 

               BEGIN

                     SELECT

                        @ProceduresOutput = @ProceduresOutput + @pSkipProc

                     EXEC cusMPMUB92ExFQHC_WYMC @pPatientVisitId , @PageBreak , @pECSFileId , @ProceduresOutput

               END
 

            DELETE FROM

                gtProcedures

            WHERE

                UniqueIdentifierId = @UniqueIdentifierId
 

      END

Open in new window

0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 24849308
Hi, Can you post some sample data and the expected result? Also can you explain where "Insurance Carrier", " Doctor ", "applicable Fee Schedule" and "Revenue Code" data are stored?
0
 
LVL 7

Author Closing Comment

by:Jeff S
ID: 31603033
I got this on my own ... put thanks for the efforts.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now