Solved

SQL Query Help - Stored Procedure

Posted on 2009-07-13
4
194 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
[X]
Welcome to Experts Exchange

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

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 9 30
t-sql left join 2 34
Access Query To Find The Average Date Between Two Dates 3 25
SQL join ...want to return one row 4 15
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

710 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