[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

Microsoft, SQL, SQL 2000, Stored Procedure

Variable I need assistance with:
-- WJY 04/07 If Attending Doctor is NULL, use DoctorID
@AttendingId = ISNULL(pv.AttendingDoctorId,pv.DoctorID),

Issue is with the pv.DoctorID. I am throwing a twist into this variable. The PatientVisit (pv alias) needs to join to the DoctorFacility table and then in turn to the DFIds table.

In the Database, a physician can be selected as "Doctor" or "Other Provider". If the user sets to "Other Provider" , they then in turn can set a "Filing Doctor" to a "Specified Doctor". So, In my DB, I set up my test Doctor (Nancy Smith MD) as an "Other Provider" and set the "Filing Doctor" to William Francis MD. I want the following section to pull William Francis versus Nancy Smith.

Problem Section pulling Variable:


  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

In the DFIDs table, I was hoping to use the following field (DFIds.DoctorForClaimId) to possibly replace the pv.DoctorID, however Im not too sure thats what I need. I assume anyone attempting to help me will need more input. It really looks like its down to 3 tables ... (PatientVisit, DoctorFacility and DFIDs). Please let me know if I can answer any questions. Thanks in advance ... full SP below.

FULL SP:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

-- WJY 08/07/2007 Changed for Medi-Cal. Look first for whether it is secondary and if it is look for the '02'  or '18' code
-- WJY 7/24/2007 Changes to Estimated Amount Due for FQHC
-- 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


CREATE    PROCEDURE cusMPMUB92FQHCMCAL @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
-- WJY 7/24/2007 Add @PrimaryInsuranceCarriersID
DECLARE @CurrentInsuranceCarriersId int, @PrimaryInsuranceCarriersID int, @CurrentCarrier 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

-- WJY 4/07 FQHC COdes and Balance Due for Secondary
-- WJY 7/24/2007 add @CarrierTypePrimary
Declare @FQHCBalance money, @CarrierType varchar(50), @CarrierTypePrimary 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,
      @PrimaryInsuranceCarriersId = pv.PrimaryInsuranceCarriersID,
      @CurrentCarrier = pv.CurrentCarrier
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

-- WJY 7/24/2007
SELECT  @CarrierTypePrimary = ml.FunctionName
FROM    InsuranceCarriers ic LEFT JOIN Medlists ml ON ic.CarrierTypeMId = ml.MedlistsId
WHERE   ic.InsuranceCarriersId = @PrimaryInsuranceCarriersId

/* 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
-- IF this is Medi-Cal We are looking for the 02 or the 18 code
SELECT @FQHCCode = CPTCode, @FQHCFee = TotalFee
    FROM PatientVisitProcs
    WHERE CPTCode IN ('02','18') 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 */
IF @CurrentCarrier = 1
      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,
      -- For Medi-Cal this is blank
      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
ELSE IF @CurrentCarrier = 2 AND ISNULL(@FQHCCode,'') <> ''
      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,
      -- For Medi-Cal this is blank
      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
ELSE
      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,
      -- For Medi-Cal this is blank
      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'
      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
      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),'')
      END

  SELECT @PriorPayments = 0
  SELECT @PriorPayments = SUM(vt.Payments)
  FROM VisitTransactions vt
  WHERE vt.InsuranceCarriersId = @InsuranceCarriersId AND
        vt.PatientVisitId = @pPatientVisitId
  SELECT '78.PriorPayments1' = @PriorPayments
  IF @CurrentCarrier <> 2
      SELECT @RunningTotalCharge = @RunningTotalCharge - ISNULL(@PriorPayments,0)
  SELECT @RunningTotalCharge = @TotalCharge - ISNULL(@PriorPayments,0)
-- WJY 7/24/2007 Primary Medicare secondary Medicaid/Comml, Est Amt is 20% of fee, Comml primary, Allowed less payment is Est Amt.
  IF @CarrierTypePrimary LIKE 'Medicare%' AND @FQHCCode IS NOT NULL AND @CurrentInsuranceCarriersId <> @PrimaryInsuranceCarriersId
  SELECT '79.EstimatedAmountDue1' = @TotalCharge * .20
        ELSE
  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),'')
      END
  SELECT @PriorPayments = 0
  SELECT @PriorPayments = SUM(vt.Payments)
  FROM VisitTransactions vt
  WHERE vt.InsuranceCarriersId = @InsuranceCarriersId AND
        vt.PatientVisitId = @pPatientVisitId
  SELECT '98.PriorPayments2' = @PriorPayments
-- WJY 08/07/2007 Show amount due as Wraparound
  IF @CurrentCarrier <> 2
      SELECT @RunningTotalCharge = @RunningTotalCharge - ISNULL(@PriorPayments,0)
  -- WJY 7/24/2007 Primary Medicare secondary Medicaid/Comml, Est Amt is 20% of fee, Comml primary, Allowed less payment is Est Amt.
  IF @CarrierTypePrimary LIKE 'Medicare%' AND @FQHCCode IS NOT NULL AND @CurrentInsuranceCarriersId <> @PrimaryInsuranceCarriersId
        SELECT '99.EstimatedAmountDue2' = @TotalCharge * .20
  ELSE
        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),'')
      END
  SELECT @PriorPayments = 0
  SELECT @PriorPayments = SUM(vt.Payments)
  FROM VisitTransactions vt
  WHERE vt.InsuranceCarriersId = @InsuranceCarriersId AND
        vt.PatientVisitId = @pPatientVisitId
  SELECT '118.PriorPayments3' = @PriorPayments
-- WJY 08/07/2007
IF @CurrentCarrier <> 2
  SELECT @RunningTotalCharge = @RunningTotalCharge - ISNULL(@PriorPayments,0)
   -- WJY 7/24/2007 Primary Medicare secondary Medicaid/Comml, Est Amt is 20% of fee, Comml primary, Allowed less payment is Est Amt.
  IF @CarrierTypePrimary LIKE 'Medicare%' AND @FQHCCode IS NOT NULL AND @CurrentInsuranceCarriersId <> @PrimaryInsuranceCarriersId
        SELECT '119.EstimatedAmountDue3' = @TotalCharge * .20
  ELSE
        SELECT '199.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(left(CPTCode,5)),
            '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(left(CPTCode,5)),
            '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(left(CPTCode,5)),
            '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(left(CPTCode,5)),
            '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(left(CPTCode,5)),
            '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(left(CPTCode,5)),
            '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(left(CPTCode,5)),
            '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(left(CPTCode,5)),
            '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(left(CPTCode,5)),
            '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(left(CPTCode,5)),
            '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(left(CPTCode,5)),
            '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(left(CPTCode,5)),
            '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(left(CPTCode,5)),
            '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(left(CPTCode,5)),
            '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(left(CPTCode,5)),
            '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(left(CPTCode,5)),
            '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(left(CPTCode,5)),
            '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(left(CPTCode,5)),
            '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(left(CPTCode,5)),

            '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(left(CPTCode,5)),
            '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(left(CPTCode,5)),
            '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(left(CPTCode,5)),
            '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
    '401.DelayedReasonCode' = drc.ANSI,      
    '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 MedLists drc ON pvf.DelayReasonCodeMId = drc.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_SortConditionCodes70 @DBName, @pPatientVisitId, 425

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

  EXEC master..mbcxp_SortValueCodes70 @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)
      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
END

IF ((SELECT count(PatientVisitProcsId) - @pSkipProc - @ProceduresOutput FROM gtProcedures WHERE UniqueIdentifierId = @UniqueIdentifierId) > 0)
BEGIN
      SELECT @ProceduresOutput = @ProceduresOutput + @pSkipProc
      EXEC cusMPMUB92FQHCMCAL @pPatientVisitId, NULL, @pECSFileId, @ProceduresOutput
END

DELETE FROM gtProcedures WHERE UniqueIdentifierId = @UniqueIdentifierId

END









GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

0
Jeff S
Asked:
Jeff S
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I am very sorry, but I don't understand what the problem/question actually is.

especially, I don't get this:

<...>
In the Database, a physician can be selected as "Doctor" or "Other Provider". If the user sets to "Other Provider" , they then in turn can set a "Filing Doctor" to a "Specified Doctor". So, In my DB, I set up my test Doctor (Nancy Smith MD) as an "Other Provider" and set the "Filing Doctor" to William Francis MD. I want the following section to pull William Francis versus Nancy Smith.
 <...>

 how is this exactly stored? full data samples?
0
 
Jeff SAuthor Commented:
Closed issue.
0
 
Jeff SAuthor Commented:
Closed case - not an issue.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now