Learn how to a build a cloud-first strategyRegister Now

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

Stored Procedure Help Needed - Error converting data type varchar to float.

I made one minor change at line 348 in my stored procedure that has created this error when I try to run a PatientVisitId against it:

my changes:

   Units = --pvp.Units ,
                    CASE WHEN pvp.DrugPricingCompositeUnitMId IS NULL THEN pvp.Units
                                    ELSE  CONVERT(VARCHAR(5) , ISNULL(ml8.ANSI , ''))
                                    END,

subsequent error:

Msg 8114, Level 16, State 5, Procedure cusCMS1500_HeartCity, Line 260
Error converting data type varchar to float.

When I use pvp.Units as it was, it runs, just without my changes obviously. I need to cast or convert to the new value and it continues to bomb out. Any help is appreciated.

Stored Procedure embedded below:
   
USE [demo]
GO

/****** Object:  StoredProcedure [dbo].[cusCMS1500_HeartCity]    Script Date: 09/04/2012 10:38:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- sb 10/12/2006 for NPI
CREATE      PROCEDURE [dbo].[cusCMS1500_HeartCity]
    @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

-- Changed Originalreference number to pull from pvi.ICN. BV
-- @pDummy is a place holder where DMEOnly used to be passed in.  KC

        DECLARE @UniqueIdentifierId UNIQUEIDENTIFIER
        DECLARE @CurrentInsuranceCarriersId INT
        DECLARE @CompanyNPI VARCHAR(25)
        DECLARE @CompanyPIN VARCHAR(25)
        DECLARE @TempInsuranceGroupId INT
        DECLARE @NumProcedureRows SMALLINT
        DECLARE @ProceduresOutput SMALLINT
        DECLARE @Ishomelocation SMALLINT
        DECLARE @LinesOutput SMALLINT
        DECLARE @Counter SMALLINT
        DECLARE @FileVersion VARCHAR(3)
        DECLARE @CurrentPIInsuranceCarriersId INT
        DECLARE @CurrentCarrier INT
        DECLARE @CarrierType VARCHAR(20)
        DECLARE @OtherPIInsuranceCarriersId INT
        DECLARE @PatientProfileId INT
        DECLARE @PatientVisitId INT
        DECLARE @TotalCharge MONEY
-- Fix for 36634 =>
        DECLARE @AmountPaidPerProc MONEY
        DECLARE @AmountPaidAllProc MONEY
        DECLARE @HCFAPaymentAmount MONEY
-- Fix for 36634 <=
        DECLARE @OutsideLabCharge MONEY
        DECLARE @InsuredSameAsPatient SMALLINT
        DECLARE @InsuredSameAsGuarantor SMALLINT
        DECLARE @OtherInsuredSameAsPatient SMALLINT
        DECLARE @OtherInsuredSameAsGuarantor SMALLINT
        DECLARE @OtherInsuranceIsMedigap SMALLINT
        DECLARE @DoctorId INT
        DECLARE @FacilityId INT
        DECLARE @CompanyId INT
        DECLARE @ReferringPhysicianId INT
        DECLARE @SupervisingPhysicianId INT
        DECLARE @FilesFacilityFees SMALLINT
        DECLARE @PurchasedServiceFacilityId INT
        DECLARE @LabFacilityId INT
        DECLARE @LabCount INT
        DECLARE @MammographyCount INT
        DECLARE @WorkComp SMALLINT
        DECLARE @CasesId INT
        DECLARE @HCFADate DATETIME
        DECLARE @Code VARCHAR(10)
        DECLARE @AcceptAssignmentLabOnly SMALLINT
        DECLARE @LastProcLaboratory INT
        DECLARE @CarePlanOversight VARCHAR(50)      -- 5010
        DECLARE
            @FederalTaxId VARCHAR(50) ,          -- 5010
            @FederalTaxIdType VARCHAR(3) ,
            @FileAsGroup SMALLINT ,
            @Id_Facility VARCHAR(50) ,               -- 5010
            @Id_PIN VARCHAR(25) ,
            @Id_PINGRP VARCHAR(25) ,
            @Id_GRP VARCHAR(25) ,
            @Id_EMCProv VARCHAR(25) ,
            @Id_CLIA VARCHAR(50) ,                   -- 5010
            @Id_Referring VARCHAR(25) ,
            @Id_ProviderType VARCHAR(25) ,
            @PlaceOfServiceMId INT ,
            @Id_Office VARCHAR(50) ,                 -- 5010
            @Id_Mammography VARCHAR(50) ,            -- 5010
            @pReferTypeAnsi VARCHAR(25) ,
            @NPI_Facility VARCHAR(80) ,
            @NPI_Doctor VARCHAR(80)

        DECLARE
            @pPINAnsiType VARCHAR(5) ,
            @pCLIAAnsiType VARCHAR(5)
        DECLARE
            @pld_Referring_Id_OfficeTypeAnsi VARCHAR(5) ,
            @pld_ReferPIN VARCHAR(25) ,
            @pld_ReferPIN_AnsiType VARCHAR(5) ,
            @pld_ReferGRP VARCHAR(25) ,
            @pld_ReferGRP_AnsiType VARCHAR(5) ,
            @pld_TaxonomyCode VARCHAR(5) ,
            @pld_EMC VARCHAR(25) ,
            @pld_EMC_AnsiType VARCHAR(5) ,
            @SuprvUPIN VARCHAR(50) ,                 -- 5010
            @SuprvUPIN_Type_Ansi VARCHAR(25) ,
            @SuprvStateLicenseNumber VARCHAR(50) ,   -- 5010
            @SuprvStateLicenseTypeAnsi VARCHAR(25)
        DECLARE
            @FacilTaxID VARCHAR(50) ,            -- 5010
            @FacilTaxIDType VARCHAR(5)

        DECLARE
            @Id_RefOffice VARCHAR(25) ,
            @ld_Id_PINTypeANSI VARCHAR(5) ,
            @ld_Id_FacilityTypeAnsi VARCHAR(5) ,
            @ld_Id_GRPTypeAnsi VARCHAR(5) ,
            @ld_Id_EMCProvTypeAnsi VARCHAR(5) ,
            @ld_Id_ProviderTypeTypeAnsi VARCHAR(5) ,
            @ld_Id_CLIATypeAnsi VARCHAR(5) ,
            @ld_Id_MammographyTypeAnsi VARCHAR(5) ,
            @ld_Id_ReferringTypeAnsi VARCHAR(5) ,
            @ldType_Facility VARCHAR(5) ,
            @ldType_Doc VARCHAR(5) ,
            @ldAddressSameAsCompany VARCHAR(2) ,
            @NPI VARCHAR(80)
        DECLARE @DMEBillingType SMALLINT
        DECLARE @Saved_FacilityTypeAnsi VARCHAR(5)
        DECLARE @Saved_FacilityId VARCHAR(50)   -- 5010
        DECLARE
            @Id_FacilityPINTypeANSI VARCHAR(5) ,
            @Id_DoctorPINTypeANSI VARCHAR(5)
        DECLARE
            @Id_FacilityGRPTypeANSI VARCHAR(5) ,
            @Id_DoctorGRPTypeANSI VARCHAR(5)
        DECLARE @Id_DocPIN VARCHAR(25)
        DECLARE @SecondaryCarrierType VARCHAR(20)
        DECLARE @SecondaryInsuranceCarriersId INT

        SELECT
            @DMEBillingType = 0


        SELECT
            @UniqueIdentifierId = NEWID()
        SELECT
            @FileVersion = '001'
        SELECT
            @NumProcedureRows = 6
        SELECT
            @ProceduresOutput = 0
        SELECT
            @LinesOutput = 0
        SELECT
            @Counter = 0
        SELECT
            @TotalCharge = 0
        SELECT
            @OutsideLabCharge = 0
        SELECT
            @LabCount = 0
        SELECT
            @MammographyCount = 0
        SELECT
            @Ishomelocation = 0
-- Fix for 36634 =>
        SELECT
            @AmountPaidPerProc = 0
        SELECT
            @AmountPaidAllProc = 0
        SELECT
            @HCFAPaymentAmount = 0
-- Fix for 36634 <=
        SELECT
            @LastProcLaboratory = -1 /*special flag*/
        DECLARE @LogonID VARCHAR(30)

-- Fix for 48559
        DECLARE @PayToAddressTest AS VARCHAR(105) -- Used to conslidate PayTo* fields into one concatinated string. Ex: '1313 ElmCulver CityTX77777' The address1, city, state and zip are all run together.
        DECLARE @PayToAddressMarker AS VARCHAR(15)  -- Used in the concatination to indicate an invalid value: a NULL or zero length string.
        DECLARE @PayToAddressCharIndex AS INT          -- Used to look for the index into the test string for the marker string.
        DECLARE @PayToAddressUse AS BIT          -- Used to flip the use of negative logic so that to use the PayTo* fields are indicated by a 1 and to not use them are indicated by a 0.
        SET @PayToAddressMarker = '<N-valid-value>'    -- This is the invalid marker string. It will show up in the concatinated string when there's a problem. EX: '1313 Elm<N-valid-value>TX77777' means that the PayToCity is not valid.

        SELECT
            @CurrentInsuranceCarriersId = pv.CurrentInsuranceCarriersId ,
            @CurrentPIInsuranceCarriersId = pv.CurrentPICarrierId ,
            @PatientProfileId = pv.PatientProfileId ,
            @PatientVisitId = pv.PatientVisitId ,
            @InsuredSameAsPatient = ISNULL(pi.InsuredSameAsPatient , 0) ,
            @InsuredSameAsGuarantor = ISNULL(pi.InsuredSameAsGuarantor , 0) ,
            @CurrentCarrier = pv.CurrentCarrier ,
            @DoctorId = pv.DoctorId ,
            @FacilityId = pv.FacilityId ,
            @CompanyId = pv.CompanyId ,
            @FilesFacilityFees = ISNULL(df.FileFacilityFees , 0) ,
            @ReferringPhysicianId = pv.ReferringDoctorId ,
            @SupervisingPhysicianId = pv.SupervisingDoctorId ,
            @WorkComp = ISNULL(c.WorkersComp , 0) ,
            @CasesId = ISNULL(pv.CasesId , 0) ,
            @HCFADate = CASE WHEN pv.BillStatus = 7
                                  OR pv.BillStatus = 9
                                  OR pv.BillStatus = 13
                                  OR pv.BillStatus = 17 THEN pv.LastFiledDate
                             ELSE GETDATE()
                        END ,
            @AcceptAssignmentLabOnly = CASE WHEN aa.ANSI = 'B' THEN 1
                                            ELSE 0
                                       END ,
            @CarePlanOversight = ISNULL(pvf.CarePlanOversight , '') ,
            @SecondaryInsuranceCarriersId = xpi.InsuranceCarriersId
        FROM
            PatientVisit pv
            LEFT JOIN Cases c ON pv.CasesId = c.CasesId
            LEFT JOIN PatientInsurance pi ON pv.CurrentPICarrierId = pi.PatientInsuranceId
            INNER JOIN DoctorFacility df ON pv.CompanyId = df.DoctorFacilityId
            INNER JOIN PatientVisitFiling pvf ON pv.PatientVisitId = pvf.PatientVisitId
            LEFT JOIN MedLists aa ON pv.AcceptAssignmentMId = aa.MedListsId
            INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
            LEFT JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
            LEFT JOIN PatientVisitInsurance pvi ON pv.PatientVisitId = pvi.PatientVisitId
                                                   AND pvi.OrderForClaims = 2
            LEFT JOIN PatientInsurance xpi ON pvi.PatientInsuranceId = xpi.PatientInsuranceId
        WHERE
            pv.PatientVisitId = @pPatientVisitId


        SELECT
            @LogonID = dbo.GetLogonID()

        INSERT  INTO PatientInfoLog
                (
                  PatientProfileId ,
                  PatientVisitId ,
                  ActionType ,
                  Created ,
                  CreatedBy ,
                  LastModified ,
                  LastModifiedBy
                )
        VALUES
                (
                  @PatientProfileId ,
                  @PatientVisitId ,
                  6 ,
                  GETDATE() ,
                  @LogonID ,
                  GETDATE() ,
                  @LogonID
                )

/* Determine the carrier type */
        SELECT
            @CarrierType = ml.FunctionName
        FROM
            InsuranceCarriers ic
            LEFT JOIN Medlists ml ON ic.CarrierTypeMId = ml.MedlistsId
        WHERE
            ic.InsuranceCarriersId = @CurrentInsuranceCarriersId 

/*Determine the Secondary carrier Type*/
        SELECT
            @SecondaryCarrierType = ml.FunctionName
        FROM
            InsuranceCarriers ic
            LEFT JOIN Medlists ml ON ic.CarrierTypeMId = ml.MedlistsId
        WHERE
            ic.InsuranceCarriersId = @SecondaryInsuranceCarriersId 
	
/*Put the procedures in a temporary table */
        INSERT  gtProcedures
                (
                  UniqueIdentifierId ,
                  Output ,
                  PatientVisitProcsId ,
                  ListOrder ,
                  DateOfServiceFrom ,
                  DateOfServiceTo ,
                  PlaceOfServiceMId ,
                  PlaceOfServiceCode ,
                  TypeOfServiceMId ,
                  TypeOfServiceCode ,
                  CPTCode ,
                  Modifier ,
                  DiagLink ,
                  Charge ,
                  Units ,
                  EPSDT ,
                  EMG ,
                  COB ,
                  ReservedForLocalUse ,
                  InsuranceCarrierId ,
                  Notes ,
                  OutsideLabCharge ,
                  PurchasedServiceFacilityId ,
                  Laboratory ,
                  Anesthesia ,
                  TimeUnits ,
                  CalculatedValueUnits ,
                  Mammography

		        )
                SELECT
                    UniqueIdentifierId = @UniqueIdentifierId ,
                    Output = NULL ,
                    PatientVisitProcsId = pvp.PatientVisitProcsId ,
                    ListOrder = pvp.ListOrder ,
                    DateOfServiceFrom = pvp.DateOfServiceFrom ,
                    DateOfServiceTo = pvp.DateOfServiceTo ,
                    PlaceOfServiceMId = pvp.PlaceOfServiceMId ,
                    PlaceOfServiceCode = ml1.Code ,
                    TypeOfServiceMId = pvp.TypeOfServiceMId ,
                    TypeOfServiceCode = ml2.Code ,
                    CPTCode = pvp.CPTCode ,
                    Modifier = CASE WHEN Modifier1MId IS NULL THEN ''
                                    WHEN Modifier1MId IS NOT NULL
                                         AND Modifier2MId IS NULL
                                         AND Modifier3MId IS NULL
                                         AND Modifier4MId IS NULL THEN SUBSTRING(ml3.Code , 1 , 2)
                                    WHEN Modifier1MId IS NOT NULL
                                         AND Modifier2MId IS NOT NULL
                                         AND Modifier3MId IS NULL
                                         AND Modifier4MId IS NULL THEN SUBSTRING(ml3.Code , 1 , 2) + '  ' + SUBSTRING(ml4.Code , 1 , 2)
                                    WHEN Modifier1MId IS NOT NULL
                                         AND Modifier2MId IS NOT NULL
                                         AND Modifier3MId IS NOT NULL
                                         AND Modifier4MId IS NULL
                                    THEN SUBSTRING(ml3.Code , 1 , 2) + '  ' + SUBSTRING(ml4.Code , 1 , 2) + '  ' + SUBSTRING(ml5.Code , 1 , 2)
                                    WHEN Modifier1MId IS NOT NULL
                                         AND Modifier2MId IS NOT NULL
                                         AND Modifier3MId IS NOT NULL
                                         AND Modifier4MId IS NOT NULL
                                    THEN SUBSTRING(ml3.Code , 1 , 2) + '  ' + SUBSTRING(ml4.Code , 1 , 2) + '  ' + SUBSTRING(ml5.Code , 1 , 2) + ' '
                                         + SUBSTRING(ml6.Code , 1 , 2)
                               END ,
                    DiagLink = CASE WHEN pvp.PatientVisitDiags1 <= 4 THEN CONVERT(VARCHAR , pvp.PatientVisitDiags1)
                                    ELSE ''
                               END + CASE WHEN pvp.PatientVisitDiags2 <= 4 THEN CONVERT(VARCHAR , pvp.PatientVisitDiags2)
                                          ELSE ''
                                     END + CASE WHEN pvp.PatientVisitDiags3 <= 4 THEN CONVERT(VARCHAR , pvp.PatientVisitDiags3)
                                                ELSE ''
                                           END + CASE WHEN pvp.PatientVisitDiags4 <= 4 THEN CONVERT(VARCHAR , pvp.PatientVisitDiags4)
                                                      ELSE ''
                                                 END ,
					
--  Uncomment the following line (and comment the previous lines) to output the diagnosis instead of the diagnosis link
--	DiagLink = CASE WHEN charindex('.',pvd.ICD9Code) = 0 THEN pvd.ICD9Code ELSE substring(pvd.ICD9Code,1,charindex('.',pvd.ICD9Code)-1) + ' ' + substring(pvd.ICD9Code,charindex('.',pvd.ICD9Code)+1,10) END, 
                    Charge = pvp.TotalFee ,
                    Units = --pvp.Units ,
                    CASE WHEN pvp.DrugPricingCompositeUnitMId IS NULL THEN pvp.Units 
						ELSE  CONVERT(VARCHAR(5) , ISNULL(ml8.ANSI , ''))
						END,
                    EPSDT = CONVERT(VARCHAR(1) , pvp.EPSDT) ,--only the first character need show on HCFA
                    EMG = pvp.EMG ,
                    COB = pvp.COB ,
                    ReservedForLocalUse = pvp.ReservedForLocalUse ,
                    InsuranceCarrierId = NULL , 
					/* Uncomment one of the first two WHEN clauses of this case statement to get anesthesia notes with each anesthesia procedure */
                    Notes = CASE /*WHEN isnull(pvp.Anesthesia,0) <> 0 THEN 'anesthesia duration: ' + convert(varchar,pvp.TotalMinutes) + ' minutes'*/
					          /*WHEN isnull(pvp.Anesthesia,0) <> 0 THEN 
							CASE WHEN ISNULL(pvp.StartingTime,0) = 0 THEN ''
							WHEN ISNULL(pvp.StartingTime2,0) = 0 THEN 'Start:' + convert(varchar(5), ISNULL(pvp.StartingTime,0), 8) + ' Stop:' + convert(varchar(5), ISNULL(pvp.EndingTime,0), 8)
							WHEN ISNULL(pvp.StartingTime3,0) = 0 THEN 'Start:' + convert(varchar(5), ISNULL(pvp.StartingTime,0), 8) + ' Stop:' + convert(varchar(5), ISNULL(pvp.EndingTime,0), 8)+' Start:' + convert(varchar(5), ISNULL(pvp.StartingTime2,0), 8) + ' Stop:' + convert(varchar(5), ISNULL(pvp.EndingTime2,0), 8)
							ELSE 'Start:' + convert(varchar(5), ISNULL(pvp.StartingTime,0), 8) + ' Stop:' + convert(varchar(5), ISNULL(pvp.EndingTime,0), 8)+' Start:' + convert(varchar(5), ISNULL(pvp.StartingTime2,0), 8) + ' Stop:' + convert(varchar(5), ISNULL(pvp.EndingTime2,0), 8) +' Start:' + convert(varchar(5), ISNULL(pvp.StartingTime3,0), 8) + ' Stop: ' + convert(varchar(5), ISNULL(pvp.EndingTime3,0), 8)END*/
                                 WHEN pvp.ProductCodeProcedureCodeQualifierMId IS NOT NULL
                                 THEN CASE WHEN ISNULL(@SecondaryCarrierType , '') = 'Medicaid'
                                           THEN --SPR 21657 : BV
                                                CONVERT(VARCHAR(74) , ISNULL(ml7.ANSI , '') + CONVERT(VARCHAR(24) , ISNULL(pvp.ProductCode , ''))
                                                + REPLICATE(' ' , 24 - LEN(RTRIM(CONVERT(VARCHAR(24) , ISNULL(pvp.ProductCode , ''))))) + '   '
                                                + ISNULL(ml8.ANSI , '') + RTRIM(CONVERT(VARCHAR(6) , pvp.Units)) + REPLICATE(' ' ,
                                                                                                                             6
                                                                                                                             - LEN(RTRIM(CONVERT(VARCHAR(6) , pvp.Units))))
                                                + CASE WHEN pvp.Notes IS NULL THEN ''
                                                       ELSE '   ' + CONVERT(VARCHAR(74) , pvp.Notes)
                                                  END)
                                           ELSE CONVERT(VARCHAR(74) , ISNULL(ml7.ANSI , '') + ISNULL(pvp.ProductCode , '') + CASE WHEN pvp.Notes IS NULL THEN ''
                                                                                                                                  ELSE '   '
                                                                                                                                       + CONVERT(VARCHAR(74) , pvp.Notes)
                                                                                                                             END)
                                      END
                                 WHEN Notes IS NULL THEN NULL
                                 ELSE CONVERT(VARCHAR(74) , pvp.Notes)
                            END ,
                    OutsideLabCharge = CASE WHEN ISNULL(pvp.PurchasedServiceFacilityId , 0) <> 0 THEN pvp.Cost
                                            ELSE 0
                                       END ,
                    PurchasedServiceFacilityId = pvp.PurchasedServiceFacilityId ,
                    Laboratory = pvp.Laboratory ,
                    Anesthesia = pvp.Anesthesia ,
                    TimeUnits = pvp.TimeUnits ,
                    CalculatedValueUnits = pvp.CalculatedValueUnits ,
                    Mammography = pvp.Mammography
                FROM
                    PatientVisitProcs pvp
                    LEFT JOIN Medlists ml1 ON pvp.PlaceOfServiceMId = ml1.MedlistsId
                    LEFT JOIN Medlists ml2 ON pvp.TypeOfServiceMId = ml2.MedlistsId
                    LEFT JOIN Medlists ml3 ON pvp.Modifier1MId = ml3.MedlistsId
                    LEFT JOIN Medlists ml4 ON pvp.Modifier2MId = ml4.MedlistsId
                    LEFT JOIN Medlists ml5 ON pvp.Modifier3MId = ml5.MedlistsId
                    LEFT JOIN Medlists ml6 ON pvp.Modifier4MId = ml6.MedlistsId
                    LEFT JOIN Medlists ml7 ON pvp.ProductCodeProcedureCodeQualifierMId = ml7.MedlistsId
                    LEFT JOIN Medlists ml8 ON pvp.DrugPricingCompositeUnitMId = ml8.MedlistsId
                                              AND ml8.Tablename = 'DrugUnit_BasisForMeasurement'
                    LEFT JOIN PatientVisitDiags pvd ON pvd.PatientVisitId = @pPatientVisitId
                                                       AND pvd.ListOrder = pvp.PatientVisitDiags1
                WHERE
                    pvp.PatientVisitId = @pPatientVisitId
                    AND ISNULL(pvp.FileInsurance , 0) <> 0
                ORDER BY
                    pvp.ListOrder

/* If WorkComp then set carrier to the employers carrier */
        IF ( @WorkComp <> 0 ) 
            BEGIN
                SELECT
                    @CurrentInsuranceCarriersId = cas.PrimaryInsuranceCarriersId ,
                    @CurrentPIInsuranceCarriersId = NULL ,
                    @CurrentCarrier = 1
                FROM
                    Cases cas
                WHERE
                    cas.CasesId = @CasesId
            END


/* Determine the carrier type */
        SELECT
            @CarrierType = ml.FunctionName
        FROM
            InsuranceCarriers ic
            LEFT JOIN Medlists ml ON ic.CarrierTypeMId = ml.MedlistsId
        WHERE
            ic.InsuranceCarriersId = @CurrentInsuranceCarriersId 
	

/*Do the override codes for PlaceOfService */
        UPDATE
            gtProcedures
        SET 
            PlaceOfServiceCode = icpos.OverrideCode
        FROM
            InsuranceCarriersPOS icpos
            INNER JOIN gtProcedures p ON p.PlaceOfServiceMId = icpos.PlaceOfServiceCodeMId
        WHERE
            icpos.InsuranceCarriersId = @CurrentInsuranceCarriersId
            AND p.UniqueIdentifierId = @UniqueIdentifierId

/*Do the override codes for TypeOfService */
        UPDATE
            gtProcedures
        SET 
            TypeOfServiceCode = ictos.OverrideCode
        FROM
            InsuranceCarriersTOS ictos
            INNER JOIN gtProcedures p ON p.TypeOfServiceMId = ictos.TypeOfServiceCodeMId
        WHERE
            ictos.InsuranceCarriersId = @CurrentInsuranceCarriersId
            AND p.UniqueIdentifierId = @UniqueIdentifierId
                                           
        DECLARE @PatientVisitProcsId INT
        DECLARE @Notes VARCHAR(75)
        DECLARE @Laboratory INT
        DECLARE @PSFacilityId INT
        DECLARE @Mammography INT
        DECLARE c CURSOR STATIC FORWARD_ONLY LOCAL
        FOR
            SELECT
                PatientVisitProcsId ,
                Notes ,
                PurchasedServiceFacilityId ,
                Laboratory ,
                Mammography
            FROM
                gtProcedures
            WHERE
                UniqueIdentifierId = @UniqueIdentifierId
            ORDER BY
                ListOrder
        OPEN c

        FETCH NEXT FROM c INTO @PatientVisitProcsId , @Notes , @PSFacilityId , @Laboratory , @Mammography

        WHILE ( @@fetch_status <> -1 ) 
            BEGIN
                SELECT
                    @Counter = @Counter + 1
                IF ( @Counter > @pSkipProc ) 
                    BEGIN
		/*The following line of code had to be changed from MPMHCFA to populate box24a: Supplemental Information correctly
		according to CMS1500 form specification. So had to create this new SP CMS1500*/
                        IF ( @LinesOutput >= @NumProcedureRows ) 
                            BREAK

                        IF (
                             @AcceptAssignmentLabOnly <> 0
                             AND @Laboratory <> @LastProcLaboratory
                             AND @LastProcLaboratory <> -1
                           ) 
                            BREAK

		/* Only allow one purchased service procedure on a claim */
                        IF (
                             ISNULL(@PurchasedServiceFacilityId , 0) <> 0
                             AND ISNULL(@PSFacilityID , 0) <> 0
                           ) 
                            BREAK
			
		/* Only allow one laboratory facility on a claim */
                        IF (
                             (
                               ISNULL(@Laboratory , 0) <> 0
                               AND ISNULL(@PSFacilityId , 0) <> 0
                               AND ISNULL(@LabFacilityId , 0) <> 0
                             )
                             OR (
                                  ISNULL(@Laboratory , 0) <> 0
                                  AND ISNULL(@PSFacilityId , 0) = 0
                                  AND ISNULL(@LabFacilityId , @FacilityId) <> @FacilityId
                                )
                           ) 
                            BREAK


                        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

		/*Few lines of code has been removed from MPMHCFA to populate box24a: Supplemental Information correctly
		according to CMS1500 form specification. So had to create this new SP CMS1500*/

		/* Keep various counters for the procedures we are going to output */
                        SELECT
                            @TotalCharge = @TotalCharge + CONVERT(MONEY , Charge) ,
                            @OutsideLabCharge = @OutsideLabCharge + CONVERT(MONEY , OutsideLabCharge) ,
                            @PurchasedServiceFacilityId = CASE WHEN ISNULL(@PurchasedServiceFacilityId , 0) = 0 THEN PurchasedServiceFacilityId
                                                               ELSE @PurchasedServiceFacilityId
                                                          END ,
                            @LabFacilityId = CASE WHEN ISNULL(@Laboratory , 0) <> 0
                                                  THEN CASE WHEN ISNULL(@PSFacilityId , 0) <> 0 THEN PurchasedServiceFacilityId
                                                            ELSE @FacilityId
                                                       END
                                                  ELSE @LabFacilityId
                                             END ,
                            @LabCount = CASE WHEN ISNULL(@Laboratory , 0) <> 0 THEN @LabCount + 1
                                             ELSE @LabCount
                                        END ,
                            @MammographyCount = CASE WHEN ISNULL(@Mammography , 0) <> 0 THEN @MammographyCount + 1
                                                     ELSE @MammographyCount
                                                END ,
                            @LastProcLaboratory = CASE WHEN ISNULL(@Laboratory , 0) <> 0 THEN 1
                                                       ELSE 0
                                                  END
                        FROM
                            gtProcedures p
                        WHERE
                            p.PatientVisitProcsId = @PatientVisitProcsId
                            AND p.UniqueIdentifierId = @UniqueIdentifierId
		-- Fix for 36634 =>	
                        SELECT
                            @AmountPaidPerProc = @AmountPaidPerProc + ISNULL(td.Amount , 0)
                        FROM
                            VisitTransactions vt
                            JOIN Transactions t ON vt.VisitTransactionsId = t.VisitTransactionsId
                            JOIN TransactionDistributions td ON t.TransactionsId = td.TransactionsId
                            JOIN InsuranceCarriers ic ON vt.InsuranceCarriersId = ic.InsuranceCarriersId
                        WHERE
                            td.PatientVisitProcsId = @PatientVisitProcsId
                            AND t.Action = 'P'			
		-- Fix for 36634 <=
                    END

                FETCH NEXT FROM c INTO @PatientVisitProcsId , @Notes , @PSFacilityId , @Laboratory , @Mammography
            END

        CLOSE c
        DEALLOCATE c

-- Fix for 36634 =>
-- Calculate Amount Paid as per Transaction Distribution
        SELECT
            @AmountPaidAllProc = ISNULL(SUM(td.Amount) , 0)
        FROM
            VisitTransactions vt
            JOIN Transactions t ON vt.VisitTransactionsId = t.VisitTransactionsId
            JOIN TransactionDistributions td ON t.TransactionsId = td.TransactionsId
            JOIN InsuranceCarriers ic ON vt.InsuranceCarriersId = ic.InsuranceCarriersId
        WHERE
            td.PatientVisitProcsId IN ( SELECT
                                            PatientVisitProcsId
                                        FROM
                                            gtProcedures
                                        WHERE
                                            UniqueIdentifierId = @UniqueIdentifierId )
            AND t.Action = 'P';
-- Fix for 36634 <=

        IF ( @ProceduresOutput > 0 ) 
            BEGIN		
                IF ( @CurrentCarrier = 1 ) 
                    SELECT
                        @OtherPIInsuranceCarriersId = ( SELECT
                                                            pvi.PatientInsuranceId
                                                        FROM
                                                            PatientVisitInsurance pvi
                                                            JOIN PatientInsurance pi ON pvi.PatientInsuranceId = pi.PatientInsuranceId
                                                            JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
                                                        WHERE
                                                            PatientVisitId = @pPatientVisitId
                                                            AND pvi.OrderForClaims = 2
                                                            AND ISNULL(ic.ReferenceCarrier , 0) = 0 )		      
                ELSE 
                    SELECT
                        @OtherPIInsuranceCarriersId = ( SELECT
                                                            pvi.PatientInsuranceId
                                                        FROM
                                                            PatientVisitInsurance pvi
                                                            JOIN PatientInsurance pi ON pvi.PatientInsuranceId = pi.PatientInsuranceId
                                                            JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
                                                        WHERE
                                                            PatientVisitId = @pPatientVisitId
                                                            AND pvi.OrderForClaims = @CurrentCarrier - 1
                                                            AND ISNULL(ic.ReferenceCarrier , 0) = 0 )

                SELECT
                    @OtherInsuredSameAsPatient = ISNULL(pi.InsuredSameAsPatient , 0) ,
                    @OtherInsuredSameAsGuarantor = ISNULL(pi.InsuredSameAsGuarantor , 0) ,
                    @OtherInsuranceIsMedigap = CASE WHEN ml.Code = 'MG' THEN 1
                                                    ELSE 0
                                               END
                FROM
                    PatientInsurance pi
                    INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
                    LEFT JOIN Medlists ml ON ic.PolicyTypeMId = ml.MedlistsId
                WHERE
                    pi.PatientInsuranceId = @OtherPIInsuranceCarriersId 
		

                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
                    '6.PayorId' = ic.ECSPayorId ,
                    '8.InsuranceCarrierName' = UPPER(ic.Name) ,
                    '9.InsuranceCarrierAddress1' = UPPER(ic.Address1) ,
                    '10.InsuranceCarrierAddress2' = UPPER(ic.Address2) ,
                    '11.InsuranceCarrierCity' = UPPER(ic.City) ,
                    '12.InsuranceCarrierState' = UPPER(ic.State) ,
                    '13.InsuranceCarrierZip' = SUBSTRING(ic.Zip , 1 , 5) + ' ' + SUBSTRING(ic.Zip , 7 , 4) ,
                    '14.InsuranceCarrierCountry' = UPPER(ic.Country) ,
                    '15.InsuranceCarrierType' = UPPER(ml.FunctionName)
                FROM
                    InsuranceCarriers ic
                    LEFT JOIN Medlists ml ON ic.CarrierTypeMId = ml.MedlistsId
                WHERE
                    ic.InsuranceCarriersId = @CurrentInsuranceCarriersId 		

                SELECT
                    '7.VisitId' = CONVERT(VARCHAR , pv.PatientVisitId) ,
                    '60.ConditionRelatedToEmploy' = CASE WHEN ISNULL(pvf.RelatedToEmployment , 0) <> 0 THEN 'Y'
                                                         ELSE 'N'
                                                    END ,
                    '61.ConditionRelatedToAuto' = CASE WHEN ISNULL(pvf.RelatedToAuto , 0) <> 0 THEN 'Y'
                                                       ELSE 'N'
                                                  END ,
                    '62.ConditionRelatedToAutoState' = CASE WHEN ISNULL(pvf.RelatedToAuto , 0) <> 0 THEN pvf.AutoState
                                                            ELSE ''
                                                       END ,
                    '63.ConditionRelatedToOther' = CASE WHEN ISNULL(pvf.RelatedToOther , 0) <> 0 THEN 'Y'
                                                        ELSE 'N'
                                                   END ,
                    '64.ConditionRelatedToAbuse' = CASE WHEN ISNULL(pvf.RelatedToAbuse , 0) <> 0 THEN 'Y'
                                                        ELSE 'N'
                                                   END ,
                    '65.ReleaseMedicalInfoDate' = pv.Visit ,
                    '66.DateOfCurrent' = CASE WHEN ISNULL(pvf.LMP , 0) = 0 THEN pvf.DateOfIllnessCurrent
                                              ELSE pvf.LMP
                                         END ,
                    '67.DateOfSameSimilar' = pvf.DateOfIllnessSimilar ,
                    '68.UnableToWorkFrom' = pvf.DisabilityFrom ,
                    '69.UnableToWorkTo' = pvf.DisabilityTo ,
                    '70.HospitalizationFrom' = pvf.HospitalizationFrom ,
                    '71.HospitalizationTo' = pvf.HospitalizationTo ,
                    '72.OutsideLab' = CASE WHEN ISNULL(pvf.OutsideLab , 0) <> 0
                                                OR @OutsideLabCharge <> 0 THEN 'Y'
                                           ELSE 'N'
                                      END ,
                    '73.OutsideLabCharges' = CASE WHEN ISNULL(pvf.OutsideLab , 0) <> 0 THEN pvf.LabCharges
                                                  ELSE @OutsideLabCharge
                                             END ,
                    '74.ResubmissionCode' = UPPER(CONVERT(VARCHAR(11) , pvf.ResubmissionCode)) ,
--		'75.OriginalReferenceNumber' = upper(pvf.OriginalReferenceNumber),
                    '75.OriginalReferenceNumber' = UPPER(pvi.ICN) ,
                    '76.PriorAuthorizationNumber' = CASE WHEN @WorkComp <> 0 THEN UPPER(pvf.PriorAuthorizationNumber)
                                                         ELSE UPPER(pvi.Number)
                                                    END ,
                    '77.EmergencyIndicator' = UPPER(ml.Code) ,
                    '78.WorkersCompensationClaim' = CASE WHEN ISNULL(cs.WorkersComp , 0) <> 0 THEN 'Y'
                                                         ELSE 'N'
                                                    END ,
                    '79.VisitDateOfService' = pv.Visit ,
                    '80.TicketNumber' = UPPER(CONVERT(VARCHAR(14) , pv.TicketNumber)) ,
                    '81.HCFANotes' = CASE pvf.PrintOnPaperClaims
                                       WHEN 1 THEN UPPER(CONVERT(VARCHAR(255) , pvf.HCFANotes))
                                       ELSE ''
                                     END ,
                    '82.AcceptAssignment' = ISNULL(aa.FunctionName , 'N') ,
                    '83.ReservedForLocalUse10d' = UPPER(LEFT(pvf.LocalUse1 , 19)) ,
                    '84.ReservedForLocalUse19' = UPPER(pvf.LocalUse2) ,
                    '85.LastSeen' = pvf.LastSeen
                FROM
                    PatientVisit pv
                    INNER JOIN PatientVisitFiling pvf ON pv.PatientVisitId = pvf.PatientVisitId
                    LEFT JOIN Medlists ml ON pvf.EmergencyIndicatorMId = ml.MedlistsId
                    LEFT JOIN Cases cs ON pv.CasesId = cs.CasesId
                    LEFT JOIN MedLists aa ON pv.AcceptAssignmentMId = aa.MedListsId
                    LEFT JOIN PatientVisitInsurance pvi ON pv.PatientVisitId = pvi.PatientVisitId
                                                           AND pvi.OrderForClaims = @CurrentCarrier
                WHERE
                    pv.PatientVisitId = @pPatientVisitId 
	
                SELECT
                    @TempInsuranceGroupId = ic.InsuranceGroupId
                FROM
                    InsuranceCarriers ic
                WHERE
                    ic.InsuranceCarriersId = @CurrentInsuranceCarriersId
                SELECT TOP 1
                    @CompanyNPI = CASE WHEN dfid.NPI = '' THEN df.NPI
                                       WHEN dfid.NPI IS NULL THEN df.NPI
                                       ELSE dfid.NPI
                                  END ,
                    @CompanyPIN = UPPER(dfid.Id_PIN)
                FROM
                    DFIDs dfid
                    LEFT JOIN DoctorFacility df ON df.DoctorFacilityId = @CompanyId
                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 = @TempInsuranceGroupId
                          OR dfid.InsuranceGroupId IS NULL
                        )
                ORDER BY
                    dfid.CompanyId DESC ,
                    dfid.FacilityId DESC ,
                    dfid.InsuranceCarriersId DESC ,
                    dfid.InsuranceGroupId DESC
		 
                SELECT
                    '36.FacilityPIN' = ISNULL(UPPER(DFIDs.ID_PIN) , @CompanyPIN)
                    --'37.FacilityNPI'=isnull(upper(DFIDs.NPI),@CompanyNPI)
                FROM
                    DFIDs
                WHERE
                    DoctorFacilityId = @FacilityId
                    AND (
                          companyId = @CompanyId
                          OR companyId IS NULL
                        )
                    AND (
                          FacilityId = @FacilityId
                          OR FacilityId IS NULL
                        )
                    AND (
                          DFIDs.InsuranceCarriersId = @CurrentInsuranceCarriersId
                          OR DFIDs.InsuranceCarriersId IS NULL
                        )
                    AND (
                          DFIDs.InsuranceGroupId = @TempInsuranceGroupId
                          OR DFIDs.InsuranceGroupId IS NULL
                        )   
          

                SELECT
                    '39.CompanyNPI' = UPPER(@CompanyNPI)
                SELECT
                    '40.PatientId' = UPPER(pp.PatientId) ,
                    '41.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 ,
                    '42.PatientAddress' = UPPER(LEFT(ISNULL(pp.Address1 , '') + ' ' + ISNULL(pp.Address2 , '') , 28)) ,
                    '43.PatientCity' = UPPER(LEFT(pp.City , 24)) ,
                    '44.PatientState' = UPPER(pp.State) ,
                    '45.PatientZip' = SUBSTRING(pp.Zip , 1 , 5) + CASE SUBSTRING(pp.Zip , 6 , 4)
                                                                    WHEN '' THEN ''
                                                                    ELSE SUBSTRING(pp.Zip , 6 , 5)
                                                                  END ,
                    '46.PatientCountry' = UPPER(pp.Country) ,
                    '47.PatientPhone' = SUBSTRING(pp.Phone1 , 1 , 10) ,
                    '48.PatientDateOfBirth' = pp.Birthdate ,
                    '49.PatientSex' = UPPER(pp.Sex) ,
                    '50.MaritalStatus' = UPPER(ml1.FunctionName) ,
                    '51.EmploymentStatusDescription' = UPPER(ml2.Description) ,
                    '52.EmploymentStatus' = UPPER(ml2.FunctionName) ,
                    '53.EmploymentEffectiveDate' = EmpStatusDate ,
                    '54.StudentStaus' = UPPER(ml3.FunctionName) ,
                    '55.ReleaseMedicalInfo' = ISNULL(ri.FunctionName , 'N') ,
                    '86.AuthorizePayment' = UPPER(ISNULL(ml4.FunctionName , ''))
                FROM
                    PatientProfile pp
                    LEFT JOIN Medlists ml1 ON pp.MaritalStatusMId = ml1.MedlistsId
                    LEFT JOIN Medlists ml2 ON pp.EmpStatusMId = ml2.MedlistsId
                    LEFT JOIN Medlists ml3 ON pp.StudentStatusMId = ml3.MedlistsId
                    LEFT JOIN Medlists ml4 ON pp.SignatureSourceMId = ml4.MedlistsId
                    LEFT JOIN Medlists ri ON pp.ReleaseOfInformationIndicatorMId = ri.MedlistsId
                WHERE
                    pp.PatientProfileId = @PatientProfileId 

	
                IF ( @InsuredSameAsPatient <> 0 ) 
                    BEGIN
                        SELECT
                            '16.InsuredIdNumber' = CASE WHEN ISNULL(ic.IncludeClaimOfficeNo , 0) <> 0 THEN UPPER(ISNULL(pi.ClaimOfficeNo , ''))
                                                        ELSE ''
                                                   END + UPPER(ISNULL(pi.InsuredId , '')) ,
                            '17.InsuredClaimOfficeNumber' = CASE WHEN ISNULL(ic.IncludeClaimOfficeNo , 0) <> 0 THEN UPPER(pi.ClaimOfficeNo)
                                                                 ELSE ''
                                                            END ,
                            '18.InsuredName' = CONVERT(VARCHAR(64) , 'SAME') ,
                            '19.InsuredAddress' = CONVERT(VARCHAR(101) , 'SAME') ,
                            '20.InsuredCity' = CONVERT(VARCHAR(30) , '') ,
                            '21.InsuredState' = CONVERT(VARCHAR(2) , '') ,
                            '22.InsuredZip' = CONVERT(VARCHAR(10) , '') ,
                            '23.InsuredCountry' = CONVERT(VARCHAR(30) , '') ,
                            '24.InsuredPhone' = CONVERT(VARCHAR(10) , '') ,
                            '25.InsuredPolicyGroup' = UPPER(pi.GroupId) ,
                            '26.InsuredDateOfBirth' = pp.Birthdate ,
                            '27.InsuredSex' = UPPER(pp.Sex) ,
                            '28.InsuredEmployerName' = UPPER(emp.Name) ,
                            '29.InsuredEmploymentStatusDesc' = UPPER(ml1.Description) ,
                            '30.InsuredEmploymentStatus' = UPPER(ml1.FunctionName) ,
                            '31.InsuredEmploymentDate' = pp.EmpStatusDate ,
                            '32.InsuredSchoolName' = UPPER(pp.SchoolName) ,
                            '33.InsuredInsurancePlanName' = UPPER(LEFT(ic.Name , 29)) ,
                            '34.PatientsRelToInsured' = CONVERT(VARCHAR(200) , 'SELF')
                        FROM
                            PatientInsurance pi
                            INNER JOIN PatientProfile pp ON pi.PatientProfileId = pp.PatientProfileId
                            LEFT JOIN Employer emp ON pp.EmployerId = emp.EmployerId
                            LEFT JOIN Medlists ml1 ON pp.EmpStatusMId = ml1.MedlistsId
                            INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
                        WHERE
                            pi.PatientInsuranceId = @CurrentPIInsuranceCarriersId 				
                    END
                ELSE 
                    IF ( @InsuredSameAsGuarantor <> 0 ) 
                        BEGIN
                            SELECT
                                '16.InsuredIdNumber' = CASE WHEN ISNULL(ic.IncludeClaimOfficeNo , 0) <> 0 THEN UPPER(ISNULL(pi.ClaimOfficeNo , ''))
                                                            ELSE ''
                                                       END + UPPER(ISNULL(pi.InsuredId , '')) ,
                                '17.InsuredClaimOfficeNumber' = CASE WHEN ISNULL(ic.IncludeClaimOfficeNo , 0) <> 0 THEN UPPER(pi.ClaimOfficeNo)
                                                                     ELSE ''
                                                                END ,
                                '18.InsuredName' = CASE WHEN ( g.Suffix ) IS NOT NULL
                                                        THEN UPPER(ISNULL(g.Last , '') + ' ' + ISNULL(g.Suffix , '') + ', ' + ISNULL(g.First , '')
                                                                   + CASE WHEN ( g.Middle ) IS NOT NULL THEN ', ' + SUBSTRING(ISNULL(g.Middle , '') , 1 , 1)
                                                                          ELSE ''
                                                                     END)
                                                        ELSE UPPER(ISNULL(g.Last , '') + ', ' + ISNULL(g.First , '')
                                                                   + --', ' + substring(isnull(g.Middle,''),1,1))
										CASE WHEN ( g.Middle ) IS NOT NULL THEN ', ' + SUBSTRING(ISNULL(g.Middle , '') , 1 , 1)
                                             ELSE ''
                                        END)
                                                   END ,
                                '19.InsuredAddress' = UPPER(LEFT(ISNULL(g.Address1 , '') + ' ' + ISNULL(g.Address2 , '') , 29)) ,
                                '20.InsuredCity' = UPPER(LEFT(g.City , 23)) ,
                                '21.InsuredState' = UPPER(g.State) ,
                                '22.InsuredZip' = SUBSTRING(g.Zip , 1 , 5) + CASE SUBSTRING(g.Zip , 6 , 5)
                                                                               WHEN '' THEN ''
                                                                               ELSE SUBSTRING(g.Zip , 6 , 5)
                                                                             END ,
                                '23.InsuredCountry' = UPPER(g.Country) ,
                                '24.InsuredPhone' = SUBSTRING(g.Phone1 , 1 , 10) ,
                                '25.InsuredPolicyGroup' = UPPER(pi.GroupId) ,
                                '26.InsuredDateOfBirth' = g.Birthdate ,
                                '27.InsuredSex' = UPPER(g.Sex) ,
                                '28.InsuredEmployerName' = UPPER(emp.Name) ,
                                '29.InsuredEmploymentStatusDesc' = UPPER(ml1.Description) ,
                                '30.InsuredEmploymentStatus' = UPPER(ml1.FunctionName) ,
                                '31.InsuredEmploymentDate' = g.EmpStatusDate ,
                                '32.InsuredSchoolName' = CONVERT(VARCHAR(30) , '') ,
                                '33.InsuredInsurancePlanName' = UPPER(LEFT(ic.Name , 29)) ,
                                '34.PatientsRelToInsured' = UPPER(ml2.FunctionName)
                            FROM
                                PatientInsurance pi
                                INNER JOIN PatientProfile pp ON pi.PatientProfileId = pp.PatientProfileId
                                INNER JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
                                LEFT JOIN Employer emp ON g.EmployerId = emp.EmployerId
                                LEFT JOIN Medlists ml1 ON g.EmpStatusMId = ml1.MedlistsId
                                LEFT JOIN Medlists ml2 ON pp.PatientRelationToGuarantorMId = ml2.MedlistsId
                                INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
                            WHERE
                                pi.PatientInsuranceId = @CurrentPIInsuranceCarriersId 
                        END
                    ELSE 
                        BEGIN
                            SELECT
                                '16.InsuredIdNumber' = CASE WHEN ISNULL(ic.IncludeClaimOfficeNo , 0) <> 0 THEN UPPER(ISNULL(pi.ClaimOfficeNo , ''))
                                                            ELSE ''
                                                       END + UPPER(ISNULL(pi.InsuredId , '')) ,
                                '17.InsuredClaimOfficeNumber' = CASE WHEN ISNULL(ic.IncludeClaimOfficeNo , 0) <> 0 THEN UPPER(pi.ClaimOfficeNo)
                                                                     ELSE ''
                                                                END ,
                                '18.InsuredName' = CASE WHEN ( pi.Suffix ) IS NOT NULL
                                                        THEN UPPER(ISNULL(pi.Last , '') + ' ' + ISNULL(pi.Suffix , '') + ', ' + ISNULL(pi.First , '')
                                                                   + CASE WHEN ( pi.Middle ) IS NOT NULL THEN ', ' + SUBSTRING(ISNULL(pi.Middle , '') , 1 , 1)
                                                                          ELSE ''
                                                                     END)
                                                        ELSE 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)
                                                   END ,--upper(isnull(pi.Last,'') + ', ' + isnull(pi.First,'') + ' ' + substring(isnull(pi.Middle,''),1,1)),
                                '19.InsuredAddress' = UPPER(LEFT(ISNULL(pi.Address1 , '') + ' ' + ISNULL(pi.Address2 , '') , 29)) ,
                                '20.InsuredCity' = UPPER(LEFT(pi.City , 23)) ,
                                '21.InsuredState' = UPPER(pi.State) ,
                                '22.InsuredZip' = SUBSTRING(pi.Zip , 1 , 5) + CASE SUBSTRING(pi.Zip , 6 , 5)
                                                                                WHEN '' THEN ''
                                                                                ELSE SUBSTRING(pi.Zip , 6 , 5)
                                                                              END ,
                                '23.InsuredCountry' = UPPER(pi.Country) ,
                                '24.InsuredPhone' = SUBSTRING(pi.Phone1 , 1 , 10) ,
                                '25.InsuredPolicyGroup' = UPPER(pi.GroupId) ,
                                '26.InsuredDateOfBirth' = pi.Birthdate ,
                                '27.InsuredSex' = UPPER(pi.Sex) ,
                                '28.InsuredEmployerName' = UPPER(emp.Name) ,
                                '29.InsuredEmploymentStatusDesc' = UPPER(ml1.Description) ,
                                '30.InsuredEmploymentStatus' = UPPER(ml1.FunctionName) ,
                                '31.InsuredEmploymentDate' = pi.EmpStatusDate ,
                                '32.InsuredSchoolName' = CONVERT(VARCHAR(30) , '') ,
                                '33.InsuredInsurancePlanName' = UPPER(LEFT(ic.Name , 29)) ,
                                '34.PatientsRelToInsured' = UPPER(ml2.FunctionName)
                            FROM
                                PatientInsurance pi
                                LEFT JOIN Employer emp ON pi.EmployerId = emp.EmployerId
                                LEFT JOIN Medlists ml1 ON pi.EmpStatusMId = ml1.MedlistsId
                                LEFT JOIN Medlists ml2 ON pi.PatRelToInsuredMId = ml2.MedlistsId
                                INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
                            WHERE
                                pi.PatientInsuranceId = @CurrentPIInsuranceCarriersId 
                        END
                IF ( @OtherInsuredSameAsPatient <> 0 ) 
                    BEGIN
                        SELECT
                            '90.OtherInsuredName' = CONVERT(VARCHAR(64) , 'SAME') ,
                            '91.OtherInsuredIdNumber' = CASE WHEN @OtherInsuranceIsMedigap <> 0 THEN 'MEDIGAP ' + UPPER(ISNULL(pi.GroupId , ''))
                                                             ELSE CASE WHEN ISNULL(ic.IncludeClaimOfficeNo , 0) <> 0 THEN UPPER(ISNULL(pi.ClaimOfficeNo , ''))
                                                                       ELSE ''
                                                                  END + UPPER(ISNULL(pi.InsuredId , ''))
                                                        END ,
                            '92.OtherInsuredClaimOfficeNum' = CASE WHEN ISNULL(ic.IncludeClaimOfficeNo , 0) <> 0 THEN UPPER(pi.ClaimOfficeNo)
                                                                   ELSE ''
                                                              END ,
                            '93.OtherInsuredDateOfBirth' = pp.Birthdate ,
                            '94.OtherInsuredSex' = UPPER(pp.Sex) ,
                            '95.OtherInsuredEmployerName' = CONVERT(VARCHAR(50) , CASE WHEN @OtherInsuranceIsMedigap <> 0
                                                                                            AND ISNULL(ic.Medigapid , '') = ''
                                                                                       THEN LEFT(UPPER(ISNULL(ic.Address1 , '')) + ' '
                                                                                                 + UPPER(ISNULL(ic.Address2 , '')) + ' ' + UPPER(ISNULL(ic.City ,
                                                                                                                                                '')) + ' '
                                                                                                 + UPPER(ISNULL(ic.State , '')) + ' ' + SUBSTRING(ISNULL(ic.Zip ,
                                                                                                                                                '') , 1 , 5)
                                                                                                 + ' ' + SUBSTRING(ISNULL(ic.Zip , '') , 7 , 4) , 28)
                                                                                       WHEN @OtherInsuranceIsMedigap <> 0
                                                                                            AND ISNULL(ic.Medigapid , '') <> '' THEN ''
                                                                                       ELSE UPPER(LEFT(ISNULL(emp.Name , '') , 28))
                                                                                  END) ,
                            '96.OtherInsuredInsuranceName' = CONVERT(VARCHAR(50) , CASE WHEN @OtherInsuranceIsMedigap <> 0 THEN UPPER(LEFT(ic.Medigapid , 28))
                                                                                        ELSE UPPER(LEFT(ic.Name , 28))
                                                                                   END)
                        FROM
                            PatientInsurance pi
                            INNER JOIN PatientProfile pp ON pi.PatientProfileId = pp.PatientProfileId
                            LEFT JOIN Employer emp ON pp.EmployerId = emp.EmployerId
                            INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
                        WHERE
                            pi.PatientInsuranceId = @OtherPIInsuranceCarriersId 
                    END
                ELSE 
                    IF ( @OtherInsuredSameAsGuarantor <> 0 ) 
                        BEGIN
                            SELECT
                                '90.OtherInsuredName' = CASE WHEN ( g.Suffix ) IS NOT NULL
                                                             THEN UPPER(ISNULL(g.Last , '') + ' ' + ISNULL(g.Suffix , '') + ', ' + ISNULL(g.First , '')
                                                                        + CASE WHEN ( g.Middle ) IS NOT NULL
                                                                               THEN ', ' + SUBSTRING(ISNULL(g.Middle , '') , 1 , 1)
                                                                               ELSE ''
                                                                          END)
                                                             ELSE UPPER(ISNULL(g.Last , '') + ', ' + ISNULL(g.First , '')
                                                                        + --', ' + substring(isnull(g.Middle,''),1,1))
								CASE WHEN ( g.Middle ) IS NOT NULL THEN ', ' + SUBSTRING(ISNULL(g.Middle , '') , 1 , 1)
                                     ELSE ''
                                END)
                                                        END ,
                                '91.OtherInsuredIdNumber' = CASE WHEN ISNULL(ic.IncludeClaimOfficeNo , 0) <> 0 THEN UPPER(ISNULL(pi.ClaimOfficeNo , ''))
                                                                 ELSE ''
                                                            END + UPPER(ISNULL(pi.InsuredId , '')) ,
                                '92.OtherInsuredClaimOfficeNum' = CASE WHEN ISNULL(ic.IncludeClaimOfficeNo , 0) <> 0 THEN UPPER(pi.ClaimOfficeNo)
                                                                       ELSE ''
                                                                  END ,
                                '93.OtherInsuredDateOfBirth' = g.Birthdate ,
                                '94.OtherInsuredSex' = UPPER(g.Sex) ,
                                '95.OtherInsuredEmployerName' = CONVERT(VARCHAR(50) , CASE WHEN @OtherInsuranceIsMedigap <> 0
                                                                                                AND ISNULL(ic.Medigapid , '') = ''
                                                                                           THEN LEFT(UPPER(ISNULL(ic.Address1 , '')) + ' '
                                                                                                     + UPPER(ISNULL(ic.Address2 , '')) + ' '
                                                                                                     + UPPER(ISNULL(ic.City , '')) + ' ' + UPPER(ISNULL(ic.State ,
                                                                                                                                                '')) + ' '
                                                                                                     + SUBSTRING(ISNULL(ic.Zip , '') , 1 , 5) + ' '
                                                                                                     + SUBSTRING(ISNULL(ic.Zip , '') , 7 , 4) , 28)
                                                                                           WHEN @OtherInsuranceIsMedigap <> 0
                                                                                                AND ISNULL(ic.Medigapid , '') <> '' THEN ''
                                                                                           ELSE UPPER(LEFT(ISNULL(emp.Name , '') , 28))
                                                                                      END) ,
                                '96.OtherInsuredInsuranceName' = CONVERT(VARCHAR(50) , CASE WHEN @OtherInsuranceIsMedigap <> 0
                                                                                            THEN UPPER(LEFT(ic.Medigapid , 28))
                                                                                            ELSE UPPER(LEFT(ic.Name , 28))
                                                                                       END)
                            FROM
                                PatientInsurance pi
                                INNER JOIN PatientProfile pp ON pi.PatientProfileId = pp.PatientProfileId
                                INNER JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
                                LEFT JOIN Employer emp ON g.EmployerId = emp.EmployerId
                                INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
                            WHERE
                                pi.PatientInsuranceId = @OtherPIInsuranceCarriersId 
                        END
                    ELSE 
                        BEGIN
                            SELECT
                                '90.OtherInsuredName' = CASE WHEN ( pi.Suffix ) IS NOT NULL
                                                             THEN UPPER(ISNULL(pi.Last , '') + ' ' + ISNULL(pi.Suffix , '') + ', ' + ISNULL(pi.First , '')
                                                                        + CASE WHEN ( pi.Middle ) IS NOT NULL
                                                                               THEN ', ' + SUBSTRING(ISNULL(pi.Middle , '') , 1 , 1)
                                                                               ELSE ''
                                                                          END)
                                                             ELSE 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)
                                                        END ,--upper(isnull(pi.Last,'') + ', ' + isnull(pi.First,'') + ' ' + substring(isnull(pi.Middle,''),1,1)),
                                '91.OtherInsuredIdNumber' = CASE WHEN ISNULL(ic.IncludeClaimOfficeNo , 0) <> 0 THEN UPPER(ISNULL(pi.ClaimOfficeNo , ''))
                                                                 ELSE ''
                                                            END + UPPER(ISNULL(pi.InsuredId , '')) ,
                                '92.OtherInsuredClaimOfficeNum' = CASE WHEN ISNULL(ic.IncludeClaimOfficeNo , 0) <> 0 THEN UPPER(pi.ClaimOfficeNo)
                                                                       ELSE ''
                                                                  END ,
                                '93.OtherInsuredDateOfBirth' = pi.Birthdate ,
                                '94.OtherInsuredSex' = UPPER(pi.Sex) ,
                                '95.OtherInsuredEmployerName' = CONVERT(VARCHAR(50) , CASE WHEN @OtherInsuranceIsMedigap <> 0
                                                                                                AND ISNULL(ic.Medigapid , '') = ''
                                                                                           THEN LEFT(UPPER(ISNULL(ic.Address1 , '')) + ' '
                                                                                                     + UPPER(ISNULL(ic.Address2 , '')) + ' '
                                                                                                     + UPPER(ISNULL(ic.City , '')) + ' ' + UPPER(ISNULL(ic.State ,
                                                                                                                                                '')) + ' '
                                                                                                     + SUBSTRING(ISNULL(ic.Zip , '') , 1 , 5) + ' '
                                                                                                     + SUBSTRING(ISNULL(ic.Zip , '') , 7 , 4) , 28)
                                                                                           WHEN @OtherInsuranceIsMedigap <> 0
                                                                                                AND ISNULL(ic.Medigapid , '') <> '' THEN ''
                                                                                           ELSE UPPER(LEFT(ISNULL(emp.Name , '') , 28))
                                                                                      END) ,
                                '96.OtherInsuredInsuranceName' = CONVERT(VARCHAR(50) , CASE WHEN @OtherInsuranceIsMedigap <> 0
                                                                                            THEN UPPER(LEFT(ic.Medigapid , 28))
                                                                                            ELSE UPPER(LEFT(ic.Name , 28))
                                                                                       END)
                            FROM
                                PatientInsurance pi
                                LEFT JOIN Employer emp ON pi.EmployerId = emp.EmployerId
                                INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
                            WHERE
                                pi.PatientInsuranceId = @OtherPIInsuranceCarriersId 				
                        END
                SELECT
                    @Code = NULL
                SELECT
                    @Code = pvd.ICD9Code
                FROM
                    PatientVisitDiags pvd
                WHERE
                    PatientVisitId = @pPatientVisitId
                    AND ListOrder = 1	
                IF CHARINDEX('.' , @Code) <> 0 
                    BEGIN
                        SELECT
                            '135.Diagnosis1' = SUBSTRING(@Code , 1 , CHARINDEX('.' , @Code) - 1) + ' ' + SUBSTRING(@Code , CHARINDEX('.' , @Code) + 1 , 10)

                    END
                ELSE 
                    BEGIN
                        SELECT
                            '135.Diagnosis1' = @Code
                    END
                SELECT
                    @Code = NULL
                SELECT
                    @Code = pvd.ICD9Code
                FROM
                    PatientVisitDiags pvd
                WHERE
                    PatientVisitId = @pPatientVisitId
                    AND ListOrder = 2
                IF CHARINDEX('.' , @Code) <> 0 
                    BEGIN
                        SELECT
                            '136.Diagnosis2' = SUBSTRING(@Code , 1 , CHARINDEX('.' , @Code) - 1) + ' ' + SUBSTRING(@Code , CHARINDEX('.' , @Code) + 1 , 10)
                    END
                ELSE 
                    BEGIN
                        SELECT
                            '136.Diagnosis2' = @Code
                    END
                SELECT
                    @Code = NULL
                SELECT
                    @Code = pvd.ICD9Code
                FROM
                    PatientVisitDiags pvd
                WHERE
                    PatientVisitId = @pPatientVisitId
                    AND ListOrder = 3	
                IF CHARINDEX('.' , @Code) <> 0 
                    BEGIN
                        SELECT
                            '137.Diagnosis3' = SUBSTRING(@Code , 1 , CHARINDEX('.' , @Code) - 1) + ' ' + SUBSTRING(@Code , CHARINDEX('.' , @Code) + 1 , 10)
                    END
                ELSE 
                    BEGIN
                        SELECT
                            '137.Diagnosis3' = @Code
                    END
                SELECT
                    @Code = NULL
                SELECT
                    @Code = pvd.ICD9Code
                FROM
                    PatientVisitDiags pvd
                WHERE
                    PatientVisitId = @pPatientVisitId
                    AND ListOrder = 4	
                IF CHARINDEX('.' , @Code) <> 0 
                    BEGIN
                        SELECT
                            '138.Diagnosis4' = SUBSTRING(@Code , 1 , CHARINDEX('.' , @Code) - 1) + ' ' + SUBSTRING(@Code , CHARINDEX('.' , @Code) + 1 , 10)
                    END

                ELSE 
                    BEGIN
                        SELECT
                            '138.Diagnosis4' = @Code
                    END

                SELECT
                    '100.ReferringPhysicianFirst' = UPPER(df.First) ,
                    '101.ReferringPhysicianMiddle' = UPPER(df.Middle) ,
                    '102.ReferringPhysicianLast' = UPPER(df.Last) ,
                    '103.ReferringPhysicianSuffix' = UPPER(df.Suffix)
                FROM
                    DoctorFacility df
                WHERE
                    DoctorFacilityId = @ReferringPhysicianId
                IF ISNULL(@ReferringPhysicianId , 0) <> 0 
                    BEGIN		
                        EXEC gGetDoctorFacilityIds_Referring 
                            @ReferringPhysicianId ,
                            @FacilityId ,
                            @CompanyId ,
                            @CurrentInsuranceCarriersId ,
                            @Id_Referring OUT ,
                            @Id_ProviderType OUT ,
                            @Id_Office OUT ,
                            @pld_Referring_Id_OfficeTypeAnsi OUT ,
                            @pld_ReferPIN OUT ,
                            @pld_ReferPIN_AnsiType OUT ,
                            @pld_ReferGRP OUT ,
                            @pld_ReferGRP_AnsiType OUT ,
                            @pld_TaxonomyCode OUT ,
                            @pld_EMC OUT ,
                            @pld_EMC_AnsiType OUT ,
                            @FederalTaxId OUT ,
                            @FederalTaxIdType OUT ,
                            @pReferTypeAnsi OUT ,
                            @SuprvUPIN OUT ,
                            @SuprvUPIN_Type_Ansi OUT ,
                            @SuprvStateLicenseNumber OUT ,
                            @SuprvStateLicenseTypeAnsi OUT ,
                            @NPI_Doctor OUT

                        SELECT
                            '104.ReferringPhysicianId' = UPPER(LEFT(@Id_Referring , 17)) ,
                            '296.NPIReferringPhysician' = UPPER(@NPI_Doctor) ,
                            '297.ReferringPhyIdQualifier' = UPPER(@pReferTypeAnsi)
                    END
                ELSE 
                    BEGIN
                        SELECT
                            '104.ReferringPhysicianId' = CONVERT(VARCHAR(25) , '') ,
                            '296.NPIReferringPhysician' = CONVERT(VARCHAR(80) , '') ,
                            '297.ReferringPhyIdQualifier' = CONVERT(VARCHAR(2) , '')

                    END

                SELECT
                    '105.SupervisingPhysicianFirst' = UPPER(df.First) ,
                    '106.SupervisingPhysicianMiddle' = UPPER(df.Middle) ,
                    '107.SupervisingPhysicianLast' = UPPER(df.Last) ,
                    '282.SupervisingPhysicianState' = UPPER(df.State)
                FROM
                    DoctorFacility df
                WHERE
                    DoctorFacilityId = @SupervisingPhysicianId
                IF ISNULL(@SupervisingPhysicianId , 0) <> 0 
                    BEGIN
                        EXEC gGetDoctorFacilityIds_Referring 
                            @SupervisingPhysicianId ,
                            @FacilityId ,
                            @CompanyId ,
                            @CurrentInsuranceCarriersId ,
                            @Id_Referring OUT ,
                            @Id_ProviderType OUT ,
                            @Id_Office OUT ,
                            @pld_Referring_Id_OfficeTypeAnsi OUT ,
                            @pld_ReferPIN OUT ,
                            @pld_ReferPIN_AnsiType OUT ,
                            @pld_ReferGRP OUT ,
                            @pld_ReferGRP_AnsiType OUT ,
                            @pld_TaxonomyCode OUT ,
                            @pld_EMC OUT ,
                            @pld_EMC_AnsiType OUT ,
                            @FederalTaxId OUT ,
                            @FederalTaxIdType OUT ,
                            @pReferTypeAnsi OUT ,
                            @SuprvUPIN OUT ,
                            @SuprvUPIN_Type_Ansi OUT ,
                            @SuprvStateLicenseNumber OUT ,
                            @SuprvStateLicenseTypeAnsi OUT ,
                            @NPI_Doctor OUT

                        SELECT
                            '109.SupervisingPhysicianId' = UPPER(@Id_Referring)
                    END
                ELSE 
                    BEGIN
                        SELECT
                            '109.SupervisingPhysicianId' = CONVERT(VARCHAR(25) , '')
                    END
	/* Get the Facility Id */
                SELECT
                    @ld_Id_FacilityTypeAnsi = '' ,
                    @Id_FacilityPINTypeANSI = '' ,
                    @Id_FacilityGRPTypeANSI = '' ,
                    @Id_DocPIN = ''

                EXEC gGetDoctorFacilityIds_All 
                    @FacilityId ,
                    @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 ,
                    @ld_Id_FacilityTypeAnsi OUT ,
                    @Id_FacilityPINTypeANSI OUT ,
                    @Id_FacilityGRPTypeANSI OUT

--	SELECT @FacilityId as '@FacilityId', @DoctorId as '@DoctorId', @Id_FacilityPINTypeANSI as '@Id_FacilityPINTypeANSI', @Id_FacilityGRPTypeANSI as '@Id_FacilityGRPTypeANSI'--@ld_Id_FacilityTypeAnsi as '@ld_Id_FacilityTypeAnsi', @Id_Facility as '@Id_Facility'

/*Saving this so it can be used where PurchasedFacility, DME or Mammography as placeholders.*/
                SELECT
                    @Saved_FacilityTypeAnsi = @ld_Id_FacilityTypeAnsi ,
                    @Saved_FacilityId = @Id_Facility

	/* Get the other Id numbers */
                DECLARE @TempId_Facility VARCHAR(50) /* use for Facility Id since it was already obtained */    -- 5010
                DECLARE @unused VARCHAR(5)

                SELECT
                    @Id_DoctorPINTypeANSI = '' ,
                    @Id_DoctorGRPTypeANSI = '' ,
                    @unused = NULL

                EXEC gGetDoctorFacilityIds_All 
                    @DoctorId OUT ,
                    @FacilityId ,
                    @CompanyId ,
                    @CurrentInsuranceCarriersId ,
                    @FederalTaxId OUT ,
                    @FederalTaxIdType OUT ,
                    @FileAsGroup OUT ,
                    @TempId_Facility OUT ,
                    @Id_DocPIN 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_Doctor OUT ,
                    @unused OUT ,
                    @Id_DoctorPINTypeANSI OUT ,
                    @Id_DoctorGRPTypeANSI OUT
--SELECT @Id_DoctorPINTypeANSI as '@Id_DoctorPINTypeANSI', @Id_DoctorGRPTypeANSI as '@Id_DoctorGRPTypeANSI'
	
                SELECT
                    '37.FacilityNPI' = UPPER(@NPI_Facility)/* Will get the correct NPI for the Facility*/


                IF (
                     ISNULL(@FilesFacilityFees , 0) = 0
                     AND ISNULL(@FileAsGroup , 0) = 0
                   )		/*Doctor fees and not a member of a doctor group*/ 
                    BEGIN

			-- Test for the validity of the PayTo* fields. Only if the PayTo*
			-- Address1, City, State and Zip are not null and they are longer
			-- than zero characters in length will the PayTo* fields be used.
                        SELECT
                            @PayToAddressTest = CASE ISNULL(PayToAddress1 , '')
                                                  WHEN '' THEN @PayToAddressMarker
                                                  ELSE PayToAddress1
                                                END + CASE ISNULL(PayToCity , '')
                                                        WHEN '' THEN @PayToAddressMarker
                                                        ELSE PayToCity
                                                      END + CASE ISNULL(PayToState , '')
                                                              WHEN '' THEN @PayToAddressMarker
                                                              ELSE PayToState
                                                            END + CASE ISNULL(PayToZip , '')
                                                                    WHEN '' THEN @PayToAddressMarker
                                                                    ELSE PayToZip
                                                                  END ,
                            @PayToAddressCharIndex = CHARINDEX(@PayToAddressMarker , @PayToAddressTest , 0) ,
                            @PayToAddressUse = CASE @PayToAddressCharIndex
                                                 WHEN 0 THEN 1
                                                 ELSE 0
                                               END
                        FROM
                            DoctorFacility
                        WHERE
                            DoctorFacilityId = @DoctorId

                        SELECT
                            '110.FederalTaxId' = UPPER(@FederalTaxId) ,
                            '111.FederalTaxIdType' = UPPER(@FederalTaxIdType) ,
                            '112.PayToName' = CASE @PayToAddressUse
                                                WHEN 1 THEN CASE ISNULL(PayToName , '')
                                                              WHEN '' THEN UPPER(OrgName)
                                                              ELSE UPPER(PayToName)
                                                            END
                                                ELSE UPPER(OrgName)
                                              END ,
                            '113.PayToAddress1' = CASE @PayToAddressUse
                                                    WHEN 1 THEN UPPER(PayToAddress1)
                                                    ELSE UPPER(Address1)
                                                  END ,
                            '114.PayToAddress2' = CASE @PayToAddressUse
                                                    WHEN 1 THEN UPPER(PayToAddress2)
                                                    ELSE UPPER(Address2)
                                                  END ,
                            '115.PayToCity' = CASE @PayToAddressUse
                                                WHEN 1 THEN UPPER(PayToCity)
                                                ELSE UPPER(City)
                                              END ,
                            '116.PayToState' = CASE @PayToAddressUse
                                                 WHEN 1 THEN UPPER(PayToState)
                                                 ELSE UPPER(State)
                                               END ,
                            '117.PayToZip' = CASE @PayToAddressUse
                                               WHEN 1 THEN SUBSTRING(PayToZip , 1 , 5) + CASE SUBSTRING(PayToZip , 7 , 4)
                                                                                           WHEN '' THEN ''
                                                                                           ELSE '-' + SUBSTRING(PayToZip , 7 , 4)
                                                                                         END
                                               ELSE SUBSTRING(Zip , 1 , 5) + CASE SUBSTRING(Zip , 7 , 4)
                                                                               WHEN '' THEN ''
                                                                               ELSE '-' + SUBSTRING(Zip , 7 , 4)
                                                                             END
                                             END ,
                            '118.PayToCountry' = CASE @PayToAddressUse
                                                   WHEN 1 THEN UPPER(PayToCountry)
                                                   ELSE UPPER(Country)
                                                 END ,
                            '119.PayToPhone' = Phone1 ,
                            '120.AnesthesiaLicense' = UPPER(AnesthesiaLicenseNo) ,
                            '121.PayToPIN' = UPPER(@Id_DocPIN) ,
                            '122.PayToGRP' = CONVERT(VARCHAR(25) , '') ,
                            '123.ServiceId' = CONVERT(VARCHAR(14) , '') ,
                            '124.NPI' = UPPER(@NPI_Doctor) ,
                            '132.SpecialCase' = 1 ,
                            '133.PurchasedServiceFacilityId' = 0 ,
                            '134.DMEBillingType' = 0 ,
                            '335.Mammography' = 0 ,
                            '336.FacilityQualifier' = UPPER(CONVERT(VARCHAR(2) , @ld_Id_FacilityTypeAnsi)) ,
                            '337.FacilityId' = UPPER(CONVERT(VARCHAR(14) , @Id_Facility)) ,
                            '338.OtherId' = UPPER(CONVERT(VARCHAR(17) , CASE WHEN @Id_DoctorPINTypeANSI IS NOT NULL
                                                                             THEN @Id_DoctorPINTypeANSI + ' ' + ISNULL(@Id_DocPIN , '')
                                                                             ELSE ISNULL(@Id_DocPIN , '')
                                                                        END))--DocPIN + DocPINQualofier
                        FROM
                            DoctorFacility df
                        WHERE
                            DoctorFacilityId = @DoctorId

                        SELECT
                            '125.ServiceName' = UPPER(df.Orgname) ,
                            '126.ServiceAddress1' = UPPER(df.Address1) ,
                            '127.ServiceAddress2' = UPPER(df.Address2) ,
                            '128.ServiceCity' = UPPER(df.City) ,
                            '129.ServiceState' = UPPER(df.State) ,
                            '130.ServiceZip' = SUBSTRING(df.Zip , 1 , 5) + CASE SUBSTRING(df.Zip , 7 , 4)
                                                                             WHEN '' THEN ''
                                                                             ELSE '-' + SUBSTRING(df.Zip , 7 , 4)
                                                                           END ,
                            '131.ServiceCountry' = UPPER(df.Country) ,
                            '295.NPIProvidingFacility' = UPPER(CONVERT(VARCHAR(10) , @NPI_Facility))
                        FROM
                            DoctorFacility df
                        WHERE
                            df.DoctorFacilityId = @FacilityId
                    END
                ELSE 
                    IF (
                         ISNULL(@FilesFacilityFees , 0) = 0
                         AND ISNULL(@FileAsGroup , 0) <> 0
                       )	/*Doctor fees and member of a doctor group*/ 
                        BEGIN
			-- Test for the validity of the PayTo* fields. Only if the PayTo*
			-- Address1, City, State and Zip are not null and they are longer
			-- than zero characters in length will the PayTo* fields be used.
                            SELECT
                                @PayToAddressTest = CASE ISNULL(PayToAddress1 , '')
                                                      WHEN '' THEN @PayToAddressMarker
                                                      ELSE PayToAddress1
                                                    END + CASE ISNULL(PayToCity , '')
                                                            WHEN '' THEN @PayToAddressMarker
                                                            ELSE PayToCity
                                                          END + CASE ISNULL(PayToState , '')
                                                                  WHEN '' THEN @PayToAddressMarker
                                                                  ELSE PayToState
                                                                END + CASE ISNULL(PayToZip , '')
                                                                        WHEN '' THEN @PayToAddressMarker
                                                                        ELSE PayToZip
                                                                      END ,
                                @PayToAddressCharIndex = CHARINDEX(@PayToAddressMarker , @PayToAddressTest , 0) ,
                                @PayToAddressUse = CASE @PayToAddressCharIndex
                                                     WHEN 0 THEN 1
                                                     ELSE 0
                                                   END
                            FROM
                                DoctorFacility
                            WHERE
                                DoctorFacilityId = @CompanyId

                            SELECT
                                '110.FederalTaxId' = UPPER(@FederalTaxId) ,
                                '111.FederalTaxIdType' = UPPER(@FederalTaxIdType) ,
                                '112.PayToName' = CASE @PayToAddressUse
                                                    WHEN 1 THEN CASE ISNULL(PayToName , '')
                                                                  WHEN '' THEN UPPER(OrgName)
                                                                  ELSE UPPER(PayToName)
                                                                END
                                                    ELSE UPPER(OrgName)
                                                  END ,
                                '113.PayToAddress1' = CASE @PayToAddressUse
                                                        WHEN 1 THEN UPPER(PayToAddress1)
                                                        ELSE UPPER(Address1)
                                                      END ,
                                '114.PayToAddress2' = CASE @PayToAddressUse
                                                        WHEN 1 THEN UPPER(PayToAddress2)
                                                        ELSE UPPER(Address2)
                                                      END ,
                                '115.PayToCity' = CASE @PayToAddressUse
                                                    WHEN 1 THEN UPPER(PayToCity)
                                                    ELSE UPPER(City)
                                                  END ,
                                '116.PayToState' = CASE @PayToAddressUse
                                                     WHEN 1 THEN UPPER(PayToState)
                                                     ELSE UPPER(State)
                                                   END ,
                                '117.PayToZip' = CASE @PayToAddressUse
                                                   WHEN 1 THEN SUBSTRING(PayToZip , 1 , 5) + CASE SUBSTRING(PayToZip , 7 , 4)
                                                                                               WHEN '' THEN ''
                                                                                               ELSE '-' + SUBSTRING(PayToZip , 7 , 4)
                                                                                             END
                                                   ELSE SUBSTRING(Zip , 1 , 5) + CASE SUBSTRING(Zip , 7 , 4)
                                                                                   WHEN '' THEN ''
                                                                                   ELSE '-' + SUBSTRING(Zip , 7 , 4)
                                                                                 END
                                                 END ,
                                '118.PayToCountry' = CASE @PayToAddressUse
                                                       WHEN 1 THEN UPPER(PayToCountry)
                                                       ELSE UPPER(Country)
                                                     END ,
                                '119.PayToPhone' = Phone1 ,
                                '121.PayToPIN' = CONVERT(VARCHAR(25) , '') ,
                                '122.PayToGRP' = UPPER(@Id_GRP) ,
                                '123.ServiceId' = CONVERT(VARCHAR(14) , '') ,
                                '124.NPI' = UPPER(@NPI_Doctor) ,
                                '132.SpecialCase' = 2 ,
                                '133.PurchasedServiceFacilityId' = 0 ,
                                '134.DMEBillingType' = 0 ,
                                '335.Mammography' = 0 ,
                                '336.FacilityQualifier' = UPPER(CONVERT(VARCHAR(2) , @ld_Id_FacilityTypeAnsi)) ,
                                '337.FacilityId' = UPPER(CONVERT(VARCHAR(14) , @Id_Facility)) ,
                                '338.OtherId' = UPPER(CONVERT(VARCHAR(17) , CASE WHEN @Id_DoctorGRPTypeANSI IS NOT NULL
                                                                                 THEN @Id_DoctorGRPTypeANSI + ' ' + ISNULL(@Id_GRP , '')
                                                                                 ELSE ISNULL(@Id_GRP , '')
                                                                            END))--DocGRP + DocGRPQualifier
                            FROM
                                DoctorFacility
                            WHERE
                                DoctorFacilityId = @CompanyId

                            SELECT
                                '120.AnesthesiaLicense' = UPPER(AnesthesiaLicenseNo)
                            FROM
                                DoctorFacility
                            WHERE
                                DoctorFacilityId = @DoctorId

                            SELECT
                                '125.ServiceName' = UPPER(df.Orgname) ,
                                '126.ServiceAddress1' = UPPER(df.Address1) ,
                                '127.ServiceAddress2' = UPPER(df.Address2) ,
                                '128.ServiceCity' = UPPER(df.City) ,
                                '129.ServiceState' = UPPER(df.State) ,
                                '130.ServiceZip' = SUBSTRING(df.Zip , 1 , 5) + CASE SUBSTRING(df.Zip , 7 , 4)
                                                                                 WHEN '' THEN ''
                                                                                 ELSE '-' + SUBSTRING(df.Zip , 7 , 4)
                                                                               END ,
                                '131.ServiceCountry' = UPPER(df.Country) ,
                                '295.NPIProvidingFacility' = UPPER(CONVERT(VARCHAR(10) , @NPI_Facility))
                            FROM
                                DoctorFacility df
                            WHERE
                                df.DoctorFacilityId = @FacilityId

                            UPDATE
                                gtProcedures
                            SET 
                                ReservedForLocalUse = UPPER(@Id_DocPIN)--upper(@Id_PIN)
                            WHERE
                                PatientVisitProcsId IS NOT NULL
                                AND UniqueIdentifierId = @UniqueIdentifierId

                        END
                    ELSE 
                        IF (
                             ISNULL(@FilesFacilityFees , 0) <> 0
                             AND ISNULL(@FileAsGroup , 0) = 0
                           )	/*Facility fees and not a member of a doctor group*/ 
                            BEGIN
			-- Test for the validity of the PayTo* fields. Only if the PayTo*
			-- Address1, City, State and Zip are not null and they are longer
			-- than zero characters in length will the PayTo* fields be used.
                                SELECT
                                    @PayToAddressTest = CASE ISNULL(PayToAddress1 , '')
                                                          WHEN '' THEN @PayToAddressMarker
                                                          ELSE PayToAddress1
                                                        END + CASE ISNULL(PayToCity , '')
                                                                WHEN '' THEN @PayToAddressMarker
                                                                ELSE PayToCity
                                                              END + CASE ISNULL(PayToState , '')
                                                                      WHEN '' THEN @PayToAddressMarker
                                                                      ELSE PayToState
                                                                    END + CASE ISNULL(PayToZip , '')
                                                                            WHEN '' THEN @PayToAddressMarker
                                                                            ELSE PayToZip
                                                                          END ,
                                    @PayToAddressCharIndex = CHARINDEX(@PayToAddressMarker , @PayToAddressTest , 0) ,
                                    @PayToAddressUse = CASE @PayToAddressCharIndex
                                                         WHEN 0 THEN 1
                                                         ELSE 0
                                                       END
                                FROM
                                    DoctorFacility
                                WHERE
                                    DoctorFacilityId = @CompanyId
			
                                SELECT
                                    '110.FederalTaxId' = UPPER(@FederalTaxId) ,
                                    '111.FederalTaxIdType' = UPPER(@FederalTaxIdType) ,
                                    '112.PayToName' = CASE @PayToAddressUse
                                                        WHEN 1 THEN CASE ISNULL(PayToName , '')
                                                                      WHEN '' THEN UPPER(OrgName)
                                                                      ELSE UPPER(PayToName)
                                                                    END
                                                        ELSE UPPER(OrgName)
                                                      END ,
                                    '113.PayToAddress1' = CASE @PayToAddressUse
                                                            WHEN 1 THEN UPPER(PayToAddress1)
                                                            ELSE UPPER(Address1)
                                                          END ,
                                    '114.PayToAddress2' = CASE @PayToAddressUse
                                                            WHEN 1 THEN UPPER(PayToAddress2)
                                                            ELSE UPPER(Address2)
                                                          END ,
                                    '115.PayToCity' = CASE @PayToAddressUse
                                                        WHEN 1 THEN UPPER(PayToCity)
                                                        ELSE UPPER(City)
                                                      END ,
                                    '116.PayToState' = CASE @PayToAddressUse
                                                         WHEN 1 THEN UPPER(PayToState)
                                                         ELSE UPPER(State)
                                                       END ,
                                    '117.PayToZip' = CASE @PayToAddressUse
                                                       WHEN 1 THEN SUBSTRING(PayToZip , 1 , 5) + CASE SUBSTRING(PayToZip , 7 , 4)
                                                                                                   WHEN '' THEN ''
                                                                                                   ELSE '-' + SUBSTRING(PayToZip , 7 , 4)
                                                                                                 END
                                                       ELSE SUBSTRING(Zip , 1 , 5) + CASE SUBSTRING(Zip , 7 , 4)
                                                                                       WHEN '' THEN ''
                                                                                       ELSE '-' + SUBSTRING(Zip , 7 , 4)
                                                                                     END
                                                     END ,
                                    '118.PayToCountry' = CASE @PayToAddressUse
                                                           WHEN 1 THEN UPPER(PayToCountry)
                                                           ELSE UPPER(Country)
                                                         END ,
                                    '119.PayToPhone' = Phone1 ,
                                    '120.AnesthesiaLicense' = UPPER(AnesthesiaLicenseNo) ,
                                    '121.PayToPIN' = UPPER(@Id_PIN) ,--this is Facility PIN
                                    '122.PayToGRP' = CONVERT(VARCHAR(25) , '') ,
                                    '123.ServiceId' = CONVERT(VARCHAR(14) , '') ,
                                    '124.NPI' = UPPER(@NPI_Facility) ,
                                    '132.SpecialCase' = 3 ,
                                    '133.PurchasedServiceFacilityId' = 0 ,
                                    '134.DMEBillingType' = 0 ,
                                    '335.Mammography' = 0 ,
                                    '336.FacilityQualifier' = UPPER(CONVERT(VARCHAR(2) , @ld_Id_FacilityTypeAnsi)) ,
                                    '337.FacilityId' = UPPER(CONVERT(VARCHAR(14) , @Id_Facility)) ,
                                    '338.OtherId' = UPPER(CONVERT(VARCHAR(17) , CASE WHEN @Id_FacilityPINTypeANSI IS NOT NULL
                                                                                     THEN @Id_FacilityPINTypeANSI + ' ' + ISNULL(@Id_PIN , '')
                                                                                     ELSE ISNULL(@Id_PIN , '')
                                                                                END))--FacilityPIN + FacilityPINQualifier
                                FROM
                                    DoctorFacility
                                WHERE
                                    DoctorFacilityId = @CompanyId--@FacilityId

                                SELECT
                                    '125.ServiceName' = UPPER(df.Orgname) ,
                                    '126.ServiceAddress1' = UPPER(df.Address1) ,
                                    '127.ServiceAddress2' = UPPER(df.Address2) ,
                                    '128.ServiceCity' = UPPER(df.City) ,
                                    '129.ServiceState' = UPPER(df.State) ,
                                    '130.ServiceZip' = SUBSTRING(df.Zip , 1 , 5) + CASE SUBSTRING(df.Zip , 7 , 4)
                                                                                     WHEN '' THEN ''
                                                                                     ELSE '-' + SUBSTRING(df.Zip , 7 , 4)
                                                                                   END ,
                                    '131.ServiceCountry' = UPPER(df.Country) ,
                                    '295.NPIProvidingFacility' = UPPER(CONVERT(VARCHAR(10) , @NPI_Facility))
                                FROM
                                    DoctorFacility df
                                WHERE
                                    df.DoctorFacilityId = @FacilityId
                            END
                        ELSE 
                            IF (
                                 ISNULL(@FilesFacilityFees , 0) <> 0
                                 AND ISNULL(@FileAsGroup , 0) <> 0
                               )	/*Facility fees and member of a doctor group*/ 
                                BEGIN
			-- Test for the validity of the PayTo* fields. Only if the PayTo*
			-- Address1, City, State and Zip are not null and they are longer
			-- than zero characters in length will the PayTo* fields be used.
                                    SELECT
                                        @PayToAddressTest = CASE ISNULL(PayToAddress1 , '')
                                                              WHEN '' THEN @PayToAddressMarker
                                                              ELSE PayToAddress1
                                                            END + CASE ISNULL(PayToCity , '')
                                                                    WHEN '' THEN @PayToAddressMarker
                                                                    ELSE PayToCity
                                                                  END + CASE ISNULL(PayToState , '')
                                                                          WHEN '' THEN @PayToAddressMarker
                                                                          ELSE PayToState
                                                                        END + CASE ISNULL(PayToZip , '')
                                                                                WHEN '' THEN @PayToAddressMarker
                                                                                ELSE PayToZip
                                                                              END ,
                                        @PayToAddressCharIndex = CHARINDEX(@PayToAddressMarker , @PayToAddressTest , 0) ,
                                        @PayToAddressUse = CASE @PayToAddressCharIndex
                                                             WHEN 0 THEN 1
                                                             ELSE 0
                                                           END
                                    FROM
                                        DoctorFacility
                                    WHERE
                                        DoctorFacilityId = @CompanyId
			
                                    SELECT
                                        '110.FederalTaxId' = UPPER(@FederalTaxId) ,
                                        '111.FederalTaxIdType' = UPPER(@FederalTaxIdType) ,
                                        '112.PayToName' = CASE @PayToAddressUse
                                                            WHEN 1 THEN CASE ISNULL(PayToName , '')
                                                                          WHEN '' THEN UPPER(OrgName)
                                                                          ELSE UPPER(PayToName)
                                                                        END
                                                            ELSE UPPER(OrgName)
                                                          END ,
                                        '113.PayToAddress1' = CASE @PayToAddressUse
                                                                WHEN 1 THEN UPPER(PayToAddress1)
                                                                ELSE UPPER(Address1)
                                                              END ,
                                        '114.PayToAddress2' = CASE @PayToAddressUse
                                                                WHEN 1 THEN UPPER(PayToAddress2)
                                                                ELSE UPPER(Address2)
                                                              END ,
                                        '115.PayToCity' = CASE @PayToAddressUse
                                                            WHEN 1 THEN UPPER(PayToCity)
                                                            ELSE UPPER(City)
                                                          END ,
                                        '116.PayToState' = CASE @PayToAddressUse
                                                             WHEN 1 THEN UPPER(PayToState)
                                                             ELSE UPPER(State)
                                                           END ,
                                        '117.PayToZip' = CASE @PayToAddressUse
                                                           WHEN 1 THEN SUBSTRING(PayToZip , 1 , 5) + CASE SUBSTRING(PayToZip , 7 , 4)
                                                                                                       WHEN '' THEN ''
                                                                                                       ELSE '-' + SUBSTRING(PayToZip , 7 , 4)
                                                                                                     END
                                                           ELSE SUBSTRING(Zip , 1 , 5) + CASE SUBSTRING(Zip , 7 , 4)
                                                                                           WHEN '' THEN ''
                                                                                           ELSE '-' + SUBSTRING(Zip , 7 , 4)
                                                                                         END
                                                         END ,
                                        '118.PayToCountry' = CASE @PayToAddressUse
                                                               WHEN 1 THEN UPPER(PayToCountry)
                                                               ELSE UPPER(Country)
                                                             END ,
                                        '119.PayToPhone' = Phone1 ,
                                        '120.AnesthesiaLicense' = UPPER(AnesthesiaLicenseNo) ,
                                        '121.PayToPIN' = CONVERT(VARCHAR(25) , '') ,
                                        '122.PayToGRP' = UPPER(@Id_GRP) ,--this is facilityGRP
                                        '123.ServiceId' = CONVERT(VARCHAR(14) , '') ,
                                        '124.NPI' = UPPER(@NPI_Facility) ,
                                        '132.SpecialCase' = 4 ,
                                        '133.PurchasedServiceFacilityId' = 0 ,
                                        '134.DMEBillingType' = 0 ,
                                        '335.Mammography' = 0 ,
                                        '336.FacilityQualifier' = UPPER(CONVERT(VARCHAR(2) , @ld_Id_FacilityTypeAnsi)) ,
                                        '337.FacilityId' = UPPER(CONVERT(VARCHAR(14) , @Id_Facility)) ,
                                        '338.OtherId' = UPPER(CONVERT(VARCHAR(17) , CASE WHEN @Id_FacilityGRPTypeANSI IS NOT NULL
                                                                                         THEN @Id_FacilityGRPTypeANSI + ' ' + ISNULL(@Id_GRP , '')
                                                                                         ELSE ISNULL(@Id_GRP , '')
                                                                                    END))--FacilityGRP + FacilityGRPQualifier
                                    FROM
                                        DoctorFacility
                                    WHERE
                                        DoctorFacilityId = @CompanyId--@FacilityId

                                    SELECT
                                        '125.ServiceName' = UPPER(df.Orgname) ,
                                        '126.ServiceAddress1' = UPPER(df.Address1) ,
                                        '127.ServiceAddress2' = UPPER(df.Address2) ,
                                        '128.ServiceCity' = UPPER(df.City) ,
                                        '129.ServiceState' = UPPER(df.State) ,
                                        '130.ServiceZip' = SUBSTRING(df.Zip , 1 , 5) + CASE SUBSTRING(df.Zip , 7 , 4)
                                                                                         WHEN '' THEN ''
                                                                                         ELSE '-' + SUBSTRING(df.Zip , 7 , 4)
                                                                                       END ,
                                        '131.ServiceCountry' = UPPER(df.Country) ,
                                        '295.NPIProvidingFacility' = UPPER(CONVERT(VARCHAR(10) , @NPI_Facility))
                                    FROM
                                        DoctorFacility df
                                    WHERE
                                        df.DoctorFacilityId = @FacilityId
				
                                END
                SELECT
                    '140.DoctorsFirstName' = UPPER(First) ,
                    '141.DoctorsMiddleName' = UPPER(Middle) ,
                    '142.DoctorsLastName' = UPPER(Last) ,
                    '143.DoctorsSuffix' = UPPER(Suffix) ,
                    '144.DoctorsPIN' = UPPER(@Id_DocPIN) ,
                    '35.DoctorsNPI' = UPPER(@NPI_Doctor) ,
                    '38.DoctorsPINANSI' = @Id_DoctorPINTypeANSI
                FROM
                    DoctorFacility
                WHERE
                    DoctorFacilityId = @DoctorId
	
                SELECT
                    '150.Proc1DateOfServiceFrom' = DateOfServiceFrom ,
                    '151.Proc1DateOfServiceTo' = CASE WHEN ISNULL(Anesthesia , 0) <> 0
                                                           AND ( @CarrierType = 'medicare' ) THEN DateOfServiceFrom
                                                      ELSE DateOfServiceTo
                                                 END ,
                    '152.Proc1PlaceOfServiceCode' = UPPER(PlaceOfServiceCode) ,
                    '153.Proc1TypeOfServiceCode' = UPPER(TypeOfServiceCode) ,
                    '154.Proc1CPTCode' = UPPER(CPTCode) ,
                    '155.Proc1Modifier' = UPPER(Modifier) ,
                    '156.Proc1DiagLink' = UPPER(DiagLink) ,
                    '157.Proc1Charge' = Charge ,
                    '158.Proc1Units' = Units ,
/*  Uncomment one of the following lines (and comment the previous line) to put time units or minutes or calculated value units in the units field		*/
/*		'158.Proc1Units' = CASE WHEN isnull(Anesthesia,0) <> 0 THEN TimeUnits ELSE Units END,	*/
/*		'158.Proc1Units' = CASE WHEN isnull(Anesthesia,0) <> 0 THEN datediff(minute,DateOfServiceFrom,DateOfServiceTo) ELSE Units END, 	*/
/*		'158.Proc1Units' = CASE WHEN isnull(Anesthesia,0) <> 0 THEN CalculatedValueUnits ELSE Units END,   */
                    '159.Proc1EPSDT' = UPPER(EPSDT) ,
                    '160.Proc1EMG' = UPPER(EMG) ,
                    '161.Proc1COB' = UPPER(COB) ,
                    '162.Proc1ReservedForLocalUse' = UPPER(ReservedForLocalUse) ,
                    '163.Proc1Notes' = UPPER(Notes) ,
                    '164.Proc1AnesthesiaMinutes' = CASE WHEN ISNULL(Anesthesia , 0) <> 0 THEN DATEDIFF(minute , DateOfServiceFrom , DateOfServiceTo)
                                                        ELSE 0
                                                   END ,
                    '165.Proc1TimeUnits' = TimeUnits
                FROM
                    gtProcedures
                WHERE
                    Output = 1
                    AND UniqueIdentifierId = @UniqueIdentifierId

                SELECT
                    '170.Proc2DateOfServiceFrom' = DateOfServiceFrom ,
                    '171.Proc2DateOfServiceTo' = CASE WHEN ISNULL(Anesthesia , 0) <> 0
                                                           AND ( @CarrierType = 'medicare' ) THEN DateOfServiceFrom
                                                      ELSE DateOfServiceTo
                                                 END ,
                    '172.Proc2PlaceOfServiceCode' = UPPER(PlaceOfServiceCode) ,
                    '173.Proc2TypeOfServiceCode' = UPPER(TypeOfServiceCode) ,
                    '174.Proc2CPTCode' = UPPER(CPTCode) ,
                    '175.Proc2Modifier' = UPPER(Modifier) ,
                    '176.Proc2DiagLink' = UPPER(DiagLink) ,
                    '177.Proc2Charge' = Charge ,
                    '178.Proc2Units' = Units ,
/*  Uncomment one of the following lines (and comment the previous line) to put time units or minutes or calculated value units in the units field		*/
/*		'178.Proc2Units' = CASE WHEN isnull(Anesthesia,0) <> 0 THEN TimeUnits ELSE Units END,	*/
/*		'178.Proc2Units' = CASE WHEN isnull(Anesthesia,0) <> 0 THEN datediff(minute,DateOfServiceFrom,DateOfServiceTo) ELSE Units END, 	*/
/*		'178.Proc2Units' = CASE WHEN isnull(Anesthesia,0) <> 0 THEN CalculatedValueUnits ELSE Units END,   */
                    '179.Proc2EPSDT' = UPPER(EPSDT) ,
                    '180.Proc2EMG' = UPPER(EMG) ,
                    '181.Proc2COB' = UPPER(COB) ,
                    '182.Proc2ReservedForLocalUse' = UPPER(ReservedForLocalUse) ,
                    '183.Proc2Notes' = UPPER(Notes) ,
                    '184.Proc2AnesthesiaMinutes' = CASE WHEN ISNULL(Anesthesia , 0) <> 0 THEN DATEDIFF(minute , DateOfServiceFrom , DateOfServiceTo)
                                                        ELSE 0
                                                   END ,
                    '185.Proc2TimeUnits' = TimeUnits
                FROM
                    gtProcedures
                WHERE
                    Output = 2
                    AND UniqueIdentifierId = @UniqueIdentifierId

                SELECT
                    '190.Proc3DateOfServiceFrom' = DateOfServiceFrom ,
                    '191.Proc3DateOfServiceTo' = CASE WHEN ISNULL(Anesthesia , 0) <> 0
                                                           AND ( @CarrierType = 'medicare' ) THEN DateOfServiceFrom
                                                      ELSE DateOfServiceTo
                                                 END ,
                    '192.Proc3PlaceOfServiceCode' = UPPER(PlaceOfServiceCode) ,
                    '193.Proc3TypeOfServiceCode' = UPPER(TypeOfServiceCode) ,
                    '194.Proc3CPTCode' = UPPER(CPTCode) ,
                    '195.Proc3Modifier' = UPPER(Modifier) ,
                    '196.Proc3DiagLink' = UPPER(DiagLink) ,
                    '197.Proc3Charge' = Charge ,
                    '198.Proc3Units' = Units ,
/*  Uncomment one of the following lines (and comment the previous line) to put time units or minutes or calculated value units in the units field		*/
/*		'198.Proc3Units' = CASE WHEN isnull(Anesthesia,0) <> 0 THEN TimeUnits ELSE Units END,	*/
/*		'198.Proc3Units' = CASE WHEN isnull(Anesthesia,0) <> 0 THEN datediff(minute,DateOfServiceFrom,DateOfServiceTo) ELSE Units END, 	*/
/*		'198.Proc3Units' = CASE WHEN isnull(Anesthesia,0) <> 0 THEN CalculatedValueUnits ELSE Units END,   */
                    '199.Proc3EPSDT' = UPPER(EPSDT) ,
                    '200.Proc3EMG' = UPPER(EMG) ,
                    '201.Proc3COB' = UPPER(COB) ,
                    '202.Proc3ReservedForLocalUse' = UPPER(ReservedForLocalUse) ,
                    '203.Proc3Notes' = UPPER(Notes) ,
                    '204.Proc3AnesthesiaMinutes' = CASE WHEN ISNULL(Anesthesia , 0) <> 0 THEN DATEDIFF(minute , DateOfServiceFrom , DateOfServiceTo)
                                                        ELSE 0
                                                   END ,
                    '205.Proc3TimeUnits' = TimeUnits
                FROM
                    gtProcedures
                WHERE
                    Output = 3
                    AND UniqueIdentifierId = @UniqueIdentifierId

                SELECT
                    '210.Proc4DateOfServiceFrom' = DateOfServiceFrom ,
                    '211.Proc4DateOfServiceTo' = CASE WHEN ISNULL(Anesthesia , 0) <> 0
                                                           AND ( @CarrierType = 'medicare' ) THEN DateOfServiceFrom
                                                      ELSE DateOfServiceTo
                                                 END ,
                    '212.Proc4PlaceOfServiceCode' = UPPER(PlaceOfServiceCode) ,
                    '213.Proc4TypeOfServiceCode' = UPPER(TypeOfServiceCode) ,
                    '214.Proc4CPTCode' = UPPER(CPTCode) ,
                    '215.Proc4Modifier' = UPPER(Modifier) ,
                    '216.Proc4DiagLink' = UPPER(DiagLink) ,
                    '217.Proc4Charge' = Charge ,
                    '218.Proc4Units' = Units ,
/*  Uncomment one of the following lines (and comment the previous line) to put time units or minutes or calculated value units in the units field		*/
/*		'218.Proc4Units' = CASE WHEN isnull(Anesthesia,0) <> 0 THEN TimeUnits ELSE Units END,	*/
/*		'218.Proc4Units' = CASE WHEN isnull(Anesthesia,0) <> 0 THEN datediff(minute,DateOfServiceFrom,DateOfServiceTo) ELSE Units END, 	*/
/*		'218.Proc4Units' = CASE WHEN isnull(Anesthesia,0) <> 0 THEN CalculatedValueUnits ELSE Units END,   */
                    '219.Proc4EPSDT' = UPPER(EPSDT) ,
                    '220.Proc4EMG' = UPPER(EMG) ,
                    '221.Proc4COB' = UPPER(COB) ,
                    '222.Proc4ReservedForLocalUse' = UPPER(ReservedForLocalUse) ,
                    '223.Proc4Notes' = UPPER(Notes) ,
                    '224.Proc4AnesthesiaMinutes' = CASE WHEN ISNULL(Anesthesia , 0) <> 0 THEN DATEDIFF(minute , DateOfServiceFrom , DateOfServiceTo)
                                                        ELSE 0
                                                   END ,
                    '225.Proc4TimeUnits' = TimeUnits
                FROM
                    gtProcedures
                WHERE
                    Output = 4
                    AND UniqueIdentifierId = @UniqueIdentifierId

                SELECT
                    '230.Proc5DateOfServiceFrom' = DateOfServiceFrom ,
                    '231.Proc5DateOfServiceTo' = CASE WHEN ISNULL(Anesthesia , 0) <> 0
                                                           AND ( @CarrierType = 'medicare' ) THEN DateOfServiceFrom
                                                      ELSE DateOfServiceTo
                                                 END ,
                    '232.Proc5PlaceOfServiceCode' = UPPER(PlaceOfServiceCode) ,
                    '233.Proc5TypeOfServiceCode' = UPPER(TypeOfServiceCode) ,
                    '234.Proc5CPTCode' = UPPER(CPTCode) ,
                    '235.Proc5Modifier' = UPPER(Modifier) ,
                    '236.Proc5DiagLink' = UPPER(DiagLink) ,
                    '237.Proc5Charge' = Charge ,
                    '238.Proc5Units' = Units ,
/*  Uncomment one of the following lines (and comment the previous line) to put time units or minutes or calculated value units in the units field		*/
/*		'238.Proc5Units' = CASE WHEN isnull(Anesthesia,0) <> 0 THEN TimeUnits ELSE Units END,	*/
/*		'238.Proc5Units' = CASE WHEN isnull(Anesthesia,0) <> 0 THEN datediff(minute,DateOfServiceFrom,DateOfServiceTo) ELSE Units END, 	*/
/*		'238.Proc5Units' = CASE WHEN isnull(Anesthesia,0) <> 0 THEN CalculatedValueUnits ELSE Units END,   */
                    '239.Proc5EPSDT' = UPPER(EPSDT) ,
                    '240.Proc5EMG' = UPPER(EMG) ,
                    '241.Proc5COB' = UPPER(COB) ,
                    '242.Proc5ReservedForLocalUse' = UPPER(ReservedForLocalUse) ,
                    '243.Proc5Notes' = UPPER(Notes) ,
                    '244.Proc5AnesthesiaMinutes' = CASE WHEN ISNULL(Anesthesia , 0) <> 0 THEN DATEDIFF(minute , DateOfServiceFrom , DateOfServiceTo)
                                                        ELSE 0
                                                   END ,
                    '245.Proc5TimeUnits' = TimeUnits
                FROM
                    gtProcedures
                WHERE
                    Output = 5
                    AND UniqueIdentifierId = @UniqueIdentifierId

                SELECT
                    '250.Proc6DateOfServiceFrom' = DateOfServiceFrom ,
                    '251.Proc6DateOfServiceTo' = CASE WHEN ISNULL(Anesthesia , 0) <> 0
                                                           AND ( @CarrierType = 'medicare' ) THEN DateOfServiceFrom
                                                      ELSE DateOfServiceTo
                                                 END ,
                    '252.Proc6PlaceOfServiceCode' = UPPER(PlaceOfServiceCode) ,
                    '253.Proc6TypeOfServiceCode' = UPPER(TypeOfServiceCode) ,
                    '254.Proc6CPTCode' = UPPER(CPTCode) ,
                    '255.Proc6Modifier' = UPPER(Modifier) ,
                    '256.Proc6DiagLink' = UPPER(DiagLink) ,
                    '257.Proc6Charge' = Charge ,
                    '258.Proc6Units' = Units ,
/*  Uncomment one of the following lines (and comment the previous line) to put time units or minutes or calculated value units in the units field		*/
/*		'258.Proc6Units' = CASE WHEN isnull(Anesthesia,0) <> 0 THEN TimeUnits ELSE Units END,	*/
/*		'258.Proc6Units' = CASE WHEN isnull(Anesthesia,0) <> 0 THEN datediff(minute,DateOfServiceFrom,DateOfServiceTo) ELSE Units END, 	*/
/*		'258.Proc6Units' = CASE WHEN isnull(Anesthesia,0) <> 0 THEN CalculatedValueUnits ELSE Units END,   */
                    '259.Proc6EPSDT' = UPPER(EPSDT) ,
                    '260.Proc6EMG' = UPPER(EMG) ,
                    '261.Proc6COB' = UPPER(COB) ,
                    '262.Proc6ReservedForLocalUse' = UPPER(ReservedForLocalUse) ,
                    '263.Proc6Notes' = UPPER(Notes) ,
                    '264.Proc6AnesthesiaMinutes' = CASE WHEN ISNULL(Anesthesia , 0) <> 0 THEN DATEDIFF(minute , DateOfServiceFrom , DateOfServiceTo)
                                                        ELSE 0
                                                   END ,
                    '265.Proc6TimeUnits' = TimeUnits
                FROM
                    gtProcedures
                WHERE
                    Output = 6
                    AND UniqueIdentifierId = @UniqueIdentifierId

                SELECT
                    '270.TotalCharge' = @TotalCharge

-- Fix for 36634 =>
	/* Don't show amount paid on pages 2,3,4,etc.. of a HCFA */
	--IF (@pSkipProc <> 0) 
	--BEGIN
	--	SELECT 	'271.AmountPaid' = convert(money, 0.00),
	--		'272.BalanceDue' = @TotalCharge
	--	FROM 	PatientVisit pv
	--	WHERE	pv.PatientVisitId = @pPatientVisitId

	--END
	--ELSE
	--BEGIN   
	--	SELECT 	'271.AmountPaid' = case
	--								when isnull(ShowPmtOnHCFA, 0) = 0
	--									then  convert(money, 0.00)
	--									else ISNULL(pv.HCFAPaymentAmount, 0.0) 
	--								end,
	--		'272.BalanceDue' = @TotalCharge - ISNULL(pv.HCFAPaymentAmount, 0.0) 
	--	FROM 	PatientVisit pv
	--	WHERE	pv.PatientVisitId = @pPatientVisitId
	--END
	
                SELECT
                    @AmountPaidPerProc = CASE WHEN ISNULL(ShowPmtOnHCFA , 0) = 0 THEN CONVERT(MONEY , 0.00)
                                              ELSE @AmountPaidPerProc
                                         END
                FROM
                    PatientVisit pv
                WHERE
                    pv.PatientVisitId = @pPatientVisitId
		
	
                SELECT
                    @HCFAPaymentAmount = CASE WHEN ISNULL(ShowPmtOnHCFA , 0) = 0 THEN CONVERT(MONEY , 0.00)
                                              ELSE ISNULL(pv.HCFAPaymentAmount , 0.0)
                                         END
                FROM
                    PatientVisit pv
                WHERE
                    pv.PatientVisitId = @pPatientVisitId	
		
                IF ( @HCFAPaymentAmount <> @AmountPaidAllProc ) 
                    BEGIN
                        IF ( @pSkipProc <> 0 ) 
                            BEGIN
                                SELECT
                                    '271.AmountPaid' = CONVERT(MONEY , 0.00)
                                SELECT
                                    '272.BalanceDue' = @TotalCharge
                            END
                        ELSE 
                            BEGIN
                                SELECT
                                    '271.AmountPaid' = @HCFAPaymentAmount
                                SELECT
                                    '272.BalanceDue' = @TotalCharge - @HCFAPaymentAmount
                            END
                    END
                ELSE 
                    BEGIN
                        SELECT
                            '271.AmountPaid' = @AmountPaidPerProc
                        SELECT
                            '272.BalanceDue' = @TotalCharge - @AmountPaidPerProc
                    END
-- Fix for 36634 <=		

                SELECT
                    '273.CurrentCarrier' = @CurrentCarrier

/* --- 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)

                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 --- */

	/* A unique row identifier for Crystal to page break on */
                DECLARE @RowIdSuffix VARCHAR(2) --Fix to SPR 14649
                SET @RowIdSuffix = CASE WHEN ( LEN(CONVERT(VARCHAR , @pSkipProc)) = 1 ) THEN '0' + CONVERT(VARCHAR , @pSkipProc)
                                        ELSE CONVERT(VARCHAR , @pSkipProc)
                                   END
                SELECT
                    '274.RowId' = ic.Name + CONVERT(VARCHAR , @pPatientVisitId) + @RowIdSuffix
                FROM
                    InsuranceCarriers ic
                WHERE
                    ic.InsuranceCarriersId = @CurrentInsuranceCarriersId  
	
                SELECT
                    '280.HCFADate' = @HCFADate
                SELECT
                    '281.Reserved2' = NULL ,
                    '283.Reserved8' = NULL ,
                    '284.Reserved9' = NULL ,
                    '285.ReservedA' = NULL ,
                    '286.ReservedB' = NULL ,
                    '287.ReservedC' = NULL ,
                    '288.ReservedD' = NULL ,
                    '289.ReservedE' = NULL ,
                    '290.ReservedF' = NULL ,
                    '291.ReservedG' = NULL ,
                    '292.ReservedH' = NULL ,
                    '293.ReservedI' = NULL ,
                    '294.ReservedJ' = NULL
		--'295.ReservedK' = NULL

                IF ( ISNULL(@PurchasedServiceFacilityId , 0) <> 0 ) 
                    BEGIN  
                        SELECT
                            '125.ServiceName' = UPPER(df.Orgname) ,
                            '126.ServiceAddress1' = UPPER(df.Address1) ,
                            '127.ServiceAddress2' = UPPER(df.Address2) ,
                            '128.ServiceCity' = UPPER(df.City) ,
                            '129.ServiceState' = UPPER(df.State) ,
                            '130.ServiceZip' = SUBSTRING(df.Zip , 1 , 5) + CASE SUBSTRING(df.Zip , 7 , 4)
                                                                             WHEN '' THEN ''
                                                                             ELSE '-' + SUBSTRING(df.Zip , 7 , 4)
                                                                           END ,
                            '131.ServiceCountry' = UPPER(df.Country)
                        FROM
                            DoctorFacility df
                        WHERE
                            df.DoctorFacilityId = @PurchasedServiceFacilityId
		
	--	EXEC gGetDoctorFacilityIds_CLIA @PurchasedServiceFacilityId, @CompanyId, @CurrentInsuranceCarriersId, @Id_PIN OUT, @Id_CLIA OUT, @pPINAnsiType OUT, @pCLIAAnsiType OUT, @FacilTaxID OUT, @FacilTaxIDType OUT
		
		/* Get the Facility Id  and @ld_Id_FacilityTypeAnsi*/
                        SELECT
                            @ld_Id_FacilityTypeAnsi = ''
                        EXEC gGetDoctorFacilityIds_All 
                            @PurchasedServiceFacilityId ,
                            @PurchasedServiceFacilityId ,
                            @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 ,
                            @ld_Id_FacilityTypeAnsi OUT

		--Select @FacilityId as '@FacilityId', @PurchasedServiceFacilityId as '@PurchasedServiceFacilityId', @Id_Facility as '@Id_Facility', @ld_Id_FacilityTypeAnsi as '@ld_Id_FacilityTypeAnsi'

	
                        SELECT
                            '123.ServiceId' = UPPER(CONVERT(VARCHAR(14) , CASE WHEN @ld_Id_FacilityTypeAnsi IS NOT NULL
                                                                               THEN @ld_Id_FacilityTypeAnsi + ' ' + ISNULL(@Id_Facility , '')
                                                                               ELSE ISNULL(@Id_Facility , '')
                                                                          END)) ,
                            '133.PurchasedServiceFacilityId' = @PurchasedServiceFacilityId ,
                            '134.DMEBillingType' = 0 ,
                            '335.Mammography' = 0 ,
                            '336.FacilityQualifier' = UPPER(CONVERT(VARCHAR(2) , @Saved_FacilityTypeAnsi)) ,
                            '337.FacilityId' = CONVERT(VARCHAR(14) , @Saved_FacilityId)


                    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
                            @DMEBillingType = ISNULL(pvp.DMEBillingType , 0)
                        FROM
                            PatientVisitProcs pvp
                        WHERE
                            pvp.PatientVisitProcsId = @PatientVisitProcsId

	/* If there is atleast one DME procedure display the FacilityIdQualifier and Facilityid in box 32b*/
                        IF @DMEBillingType <> 0 
                            BEGIN
			/* Get the Facility Id  and @ld_Id_FacilityTypeAnsi*/
                                SELECT
                                    @ld_Id_FacilityTypeAnsi = ''
                                EXEC gGetDoctorFacilityIds_All 
                                    @FacilityId ,
                                    @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 ,
                                    @ld_Id_FacilityTypeAnsi OUT

	
                                SELECT
                                    '123.ServiceId' = UPPER(CONVERT(VARCHAR(14) , CASE WHEN @ld_Id_FacilityTypeAnsi IS NOT NULL
                                                                                       THEN @ld_Id_FacilityTypeAnsi + ' ' + ISNULL(@Id_Facility , '')
                                                                                       ELSE ISNULL(@Id_Facility , '')
                                                                                  END)) ,
                                    '133.PurchasedServiceFacilityId' = 0 ,
                                    '134.DMEBillingType' = @DMEBillingType ,
                                    '335.Mammography' = 0 ,
                                    '336.FacilityQualifier' = UPPER(CONVERT(VARCHAR(2) , @Saved_FacilityTypeAnsi)) ,
                                    '337.FacilityId' = CONVERT(VARCHAR(14) , @Saved_FacilityId)

		
                                BREAK
                            END

                        FETCH NEXT FROM c INTO @PatientVisitProcsId

                    END
                CLOSE c
                DEALLOCATE c
/*************************************************************************************************/

                IF ( @LabCount <> 0 ) 
                    BEGIN
                        EXEC gGetDoctorFacilityIds_CLIA 
                            @LabFacilityId ,
                            @CompanyId ,
                            @CurrentInsuranceCarriersId ,
                            @Id_PIN OUT ,
                            @Id_CLIA OUT ,
                            @pPINAnsiType OUT ,
                            @pCLIAAnsiType OUT ,
                            @FacilTaxID OUT ,
                            @FacilTaxIDType OUT
                        SELECT
                            '76.PriorAuthorizationNumber' = UPPER(@Id_CLIA)
                    END

                IF ( @CarePlanOversight <> '' ) 
                    BEGIN
                        SELECT
                            '76.PriorAuthorizationNumber' = UPPER(@CarePlanOversight)
                    END

                IF ( @MammographyCount <> 0 ) 
                    BEGIN
                        SELECT
                            '123.ServiceId' = UPPER(CONVERT(VARCHAR(14) , @Id_Mammography)) ,
                            '133.PurchasedServiceFacilityId' = 0 ,
                            '134.DMEBillingType' = 0 ,
                            '335.Mammography' = @MammographyCount ,
                            '336.FacilityQualifier' = UPPER(CONVERT(VARCHAR(2) , @Saved_FacilityTypeAnsi)) ,
                            '337.FacilityId' = CONVERT(VARCHAR(14) , @Saved_FacilityId)


                    END

                IF ( @CarrierType = 'medicare' ) 
                    BEGIN
                        SELECT
                            '25.InsuredPolicyGroup' = CONVERT(VARCHAR(20) , 'NONE') ,
                            '26.InsuredDateOfBirth' = CONVERT(DATETIME , NULL) ,
                            '27.InsuredSex' = CONVERT(VARCHAR(1) , '') ,			
			--When medicare, Enter the employer name if applicable. If there is a change in the insured's insurance status to 'N'
			--enter the empstatus date preceded by the Employment status in upper case.
			--InsuredEmployerName and InsuredEmploymentStatus is retrieved based on the conditions (@InsuredSameAsPatient, @InsuredSameAsGuarantor, InsuredParty)
			--Fix to SPR #CISDB3257
			--'28.InsuredEmployerName' = convert(varchar(50),''),
			--'29.InsuredEmploymentStatusDesc' = convert(varchar(200),''), Fix to SPR #CISDB3257
                            '32.InsuredSchoolName' = CONVERT(VARCHAR(30) , '') ,
                            '33.InsuredInsurancePlanName' = CONVERT(VARCHAR(50) , '') ,
                            '18.InsuredName' = CONVERT(VARCHAR(64) , '') ,
                            '19.InsuredAddress' = CONVERT(VARCHAR(101) , '')

                        IF @OtherInsuranceIsMedigap = 0 
                            BEGIN
                                SELECT
                                    '90.OtherInsuredName' = CONVERT(VARCHAR(64) , '') ,
                                    '91.OtherInsuredIdNumber' = CONVERT(VARCHAR(29) , '') ,
                                    '92.OtherInsuredClaimOfficeNum' = CONVERT(VARCHAR(4) , '') ,
                                    '93.OtherInsuredDateOfBirth' = CONVERT(DATETIME , NULL) ,
                                    '94.OtherInsuredSex' = CONVERT(VARCHAR(1) , '') ,
                                    '95.OtherInsuredEmployerName' = CONVERT(VARCHAR(50) , '') ,
                                    '96.OtherInsuredInsuranceName' = CONVERT(VARCHAR(50) , '')
                            END
                        IF @CurrentCarrier = 2
                            AND @OtherPIInsuranceCarriersId <> 0 
                            BEGIN
                                SELECT
                                    '18.InsuredName' = CASE WHEN @OtherInsuredSameAsPatient <> 0 THEN 'SAME'
                                                            WHEN @OtherInsuredSameAsGuarantor <> 0
                                                            THEN UPPER(ISNULL(g.Last , '') + ', ' + ISNULL(g.First , '') + ' ' + SUBSTRING(ISNULL(g.Middle , '') ,
                                                                                                                                           1 , 1))
                                                            ELSE UPPER(ISNULL(pi.Last , '') + ', ' + ISNULL(pi.First , '') + ' ' + SUBSTRING(ISNULL(pi.Middle ,
                                                                                                                                                '') , 1 , 1))
                                                       END ,
                                    '19.InsuredAddress' = CASE WHEN @OtherInsuredSameAsPatient <> 0 THEN 'SAME'
                                                               WHEN @OtherInsuredSameAsGuarantor <> 0
                                                               THEN UPPER(LEFT(ISNULL(g.Address1 , '') + ' ' + ISNULL(g.Address2 , '') , 29))
                                                               ELSE UPPER(LEFT(ISNULL(pi.Address1 , '') + ' ' + ISNULL(pi.Address2 , '') , 29))
                                                          END ,
                                    '20.InsuredCity' = CASE WHEN @OtherInsuredSameAsPatient <> 0 THEN ''
                                                            WHEN @OtherInsuredSameAsGuarantor <> 0 THEN UPPER(LEFT(g.City , 23))
                                                            ELSE UPPER(LEFT(pi.City , 23))
                                                       END ,
                                    '21.InsuredState' = CASE WHEN @OtherInsuredSameAsPatient <> 0 THEN ''
                                                             WHEN @OtherInsuredSameAsGuarantor <> 0 THEN UPPER(g.State)
                                                             ELSE UPPER(pi.State)
                                                        END ,
                                    '22.InsuredZip' = CASE WHEN @OtherInsuredSameAsPatient <> 0 THEN ''
                                                           WHEN @OtherInsuredSameAsGuarantor <> 0
                                                           THEN SUBSTRING(g.Zip , 1 , 5) + CASE SUBSTRING(g.Zip , 6 , 5)
                                                                                             WHEN '' THEN ''
                                                                                             ELSE SUBSTRING(g.Zip , 6 , 5)
                                                                                           END
                                                           ELSE SUBSTRING(pi.Zip , 1 , 5) + CASE SUBSTRING(pi.Zip , 6 , 5)
                                                                                              WHEN '' THEN ''
                                                                                              ELSE SUBSTRING(pi.Zip , 6 , 5)
                                                                                            END
                                                      END ,
                                    '23.InsuredCountry' = CASE WHEN @OtherInsuredSameAsPatient <> 0 THEN ''
                                                               WHEN @OtherInsuredSameAsGuarantor <> 0 THEN UPPER(g.Country)
                                                               ELSE UPPER(pi.Country)
                                                          END ,
                                    '24.InsuredPhone' = CASE WHEN @OtherInsuredSameAsPatient <> 0 THEN ''
                                                             WHEN @OtherInsuredSameAsGuarantor <> 0 THEN SUBSTRING(g.Phone1 , 1 , 10)
                                                             ELSE SUBSTRING(pi.Phone1 , 1 , 10)
                                                        END ,
                                    '25.InsuredPolicyGroup' = UPPER(pi.InsuredId) ,
                                    '26.InsuredDateOfBirth' = CASE WHEN @OtherInsuredSameAsPatient <> 0 THEN CONVERT(DATETIME , NULL)
                                                                   WHEN @OtherInsuredSameAsGuarantor <> 0 THEN g.Birthdate
                                                                   ELSE pi.Birthdate
                                                              END ,
                                    '27.InsuredSex' = CASE WHEN @OtherInsuredSameAsPatient <> 0 THEN ''
                                                           WHEN @OtherInsuredSameAsGuarantor <> 0 THEN UPPER(g.Sex)
                                                           ELSE UPPER(pi.Sex)
                                                      END ,
                                    '28.InsuredEmployerName' = CASE WHEN @OtherInsuredSameAsPatient <> 0 THEN UPPER(ppemp.Name)
                                                                    WHEN @OtherInsuredSameAsGuarantor <> 0 THEN UPPER(ggemp.Name)
                                                                    ELSE UPPER(piemp.Name)
                                                               END ,
                                    '29.InsuredEmploymentStatusDesc' = CONVERT(VARCHAR(200) , '') ,
                                    '30.InsuredEmploymentStatus' = 'Y' ,
                                    '33.InsuredInsurancePlanName' = UPPER(LEFT(ic.Name , 29)) ,
                                    '34.PatientsRelToInsured' = CASE WHEN @OtherInsuredSameAsPatient <> 0 THEN 'SELF'
                                                                     ELSE UPPER(ml.FunctionName)
                                                                END
                                FROM
                                    PatientInsurance pi
                                    INNER JOIN PatientProfile pp ON pi.PatientProfileId = pp.PatientProfileId
                                    LEFT JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
                                    LEFT JOIN Employer ppemp ON pp.EmployerId = ppemp.EmployerId
                                    LEFT JOIN Employer ggemp ON g.EmployerId = ggemp.EmployerId
                                    LEFT JOIN Employer piemp ON pi.EmployerId = piemp.EmployerId
                                    LEFT JOIN Medlists ml ON pi.PatRelToInsuredMId = ml.MedlistsId
                                    INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
                                WHERE
                                    pi.PatientInsuranceId = @OtherPIInsuranceCarriersId 							
                            END

                        IF @CurrentCarrier > 1 
                            BEGIN
                                DECLARE @NextPIInsuranceCarriersId INT
                                DECLARE @NextInsuranceIsMedigap SMALLINT

                                SELECT
                                    @NextPIInsuranceCarriersId = ( SELECT
                                                                    pvi.PatientInsuranceId
                                                                   FROM
                                                                    PatientVisitInsurance pvi
                                                                    JOIN PatientInsurance pi ON pvi.PatientInsuranceId = pi.PatientInsuranceId
                                                                    JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
                                                                   WHERE
                                                                    PatientVisitId = @pPatientVisitId
                                                                    AND pvi.OrderForClaims = @CurrentCarrier + 1
                                                                    AND ISNULL(ic.ReferenceCarrier , 0) = 0 )

                                SELECT
                                    @NextInsuranceIsMedigap = CASE WHEN ml.Code = 'MG' THEN 1
                                                                   ELSE 0
                                                              END
                                FROM
                                    PatientInsurance pi
                                    INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
                                    LEFT JOIN Medlists ml ON ic.PolicyTypeMId = ml.MedlistsId
                                WHERE
                                    pi.PatientInsuranceId = @NextPIInsuranceCarriersId 

                                IF @NextInsuranceIsMedigap <> 0 
                                    SELECT
                                        '90.OtherInsuredName' = CONVERT(VARCHAR(64) , 'SAME') ,
                                        '91.OtherInsuredIdNumber' = 'MEDIGAP ' + UPPER(ISNULL(pi.GroupId , '')) ,
                                        '92.OtherInsuredClaimOfficeNum' = UPPER(pi.ClaimOfficeNo) ,
                                        '93.OtherInsuredDateOfBirth' = pp.Birthdate ,
                                        '94.OtherInsuredSex' = UPPER(pp.Sex) ,
                                        '95.OtherInsuredEmployerName' = CONVERT(VARCHAR(50) , CASE WHEN ISNULL(ic.Medigapid , '') = ''
                                                                                                   THEN LEFT(UPPER(ISNULL(ic.Address1 , '')) + ' '
                                                                                                             + UPPER(ISNULL(ic.Address2 , '')) + ' '
                                                                                                             + UPPER(ISNULL(ic.City , '')) + ' '
                                                                                                             + UPPER(ISNULL(ic.State , '')) + ' '
                                                                                                             + SUBSTRING(ISNULL(ic.Zip , '') , 1 , 5) + ' '
                                                                                                             + SUBSTRING(ISNULL(ic.Zip , '') , 7 , 4) , 28)
                                                                                                   ELSE ''
                                                                                              END) ,
                                        '96.OtherInsuredInsuranceName' = UPPER(LEFT(ic.Medigapid , 28))
                                    FROM
                                        PatientInsurance pi
                                        INNER JOIN PatientProfile pp ON pi.PatientProfileId = pp.PatientProfileId
                                        LEFT JOIN Employer emp ON pp.EmployerId = emp.EmployerId
                                        INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
                                    WHERE
                                        pi.PatientInsuranceId = @NextPIInsuranceCarriersId
                            END
                    END

                SELECT
                    '108.WorkersComp' = CASE WHEN @WorkComp <> 0 THEN 'Y'
                                             ELSE 'N'
                                        END

                IF ( @WorkComp <> 0 ) 
                    BEGIN
                        SELECT
                            '16.InsuredIdNumber' = UPPER(pp.SSN)
                        FROM
                            PatientProfile pp
                        WHERE
                            pp.PatientProfileId = @PatientProfileId
		
                        SELECT
                            '17.InsuredClaimOfficeNumber' = CONVERT(VARCHAR(4) , '') ,
                            '18.InsuredName' = UPPER(emp.Name) ,
                            '19.InsuredAddress' = UPPER(LEFT(ISNULL(emp.Address1 , '') + ' ' + ISNULL(emp.Address2 , '') , 29)) ,
                            '20.InsuredCity' = UPPER(LEFT(emp.City , 23)) ,
                            '21.InsuredState' = UPPER(emp.State) ,
                            '22.InsuredZip' = SUBSTRING(emp.Zip , 1 , 5) + CASE SUBSTRING(emp.Zip , 6 , 5)
                                                                             WHEN '' THEN ''
                                                                             ELSE SUBSTRING(emp.Zip , 6 , 5)
                                                                           END ,
                            '23.InsuredCountry' = UPPER(emp.Country) ,
                            '24.InsuredPhone' = SUBSTRING(emp.Phone1 , 1 , 10) ,
                            '25.InsuredPolicyGroup' = UPPER(ISNULL(c.ClaimNumber , 'N/A')) ,
                            '26.InsuredDateOfBirth' = CONVERT(DATETIME , NULL) ,
                            '27.InsuredSex' = CONVERT(VARCHAR(1) , '') ,
                            '28.InsuredEmployerName' = CONVERT(VARCHAR(50) , '') ,
                            '29.InsuredEmploymentStatusDesc' = CONVERT(VARCHAR(200) , '') ,
                            '30.InsuredEmploymentStatus' = CONVERT(VARCHAR(200) , '') ,
                            '31.InsuredEmploymentDate' = CONVERT(DATETIME , NULL) ,
                            '32.InsuredSchoolName' = CONVERT(VARCHAR(30) , '') ,
                            '33.InsuredInsurancePlanName' = CONVERT(VARCHAR(50) , '') ,
                            '66.DateOfCurrent' = c.DateOfInjury
                        FROM
                            Cases c
                            JOIN Employer emp ON emp.EmployerId = c.EmployerId
                        WHERE
                            c.CasesId = @CasesId
			

                        SELECT
                            '34.PatientsRelToInsured' = CONVERT(VARCHAR(200) , 'OTHER') ,
                            '90.OtherInsuredName' = CONVERT(VARCHAR(64) , '') ,
                            '91.OtherInsuredIdNumber' = CONVERT(VARCHAR(29) , '') ,
                            '92.OtherInsuredClaimOfficeNum' = CONVERT(VARCHAR(4) , '') ,
                            '93.OtherInsuredDateOfBirth' = CONVERT(DATETIME , NULL) ,
                            '94.OtherInsuredSex' = CONVERT(VARCHAR(1) , '') ,
                            '95.OtherInsuredEmployerName' = CONVERT(VARCHAR(50) , '') ,
                            '96.OtherInsuredInsuranceName' = CONVERT(VARCHAR(50) , '')

                        SELECT
                            '67.DateOfSameSimilar' = pvf.DateOfIllnessSimilar
                        FROM
                            PatientVisitFiling pvf
                        WHERE
                            PatientVisitId = @pPatientVisitId

                        IF ( @FilesFacilityFees = 0 )		/*Doctor fees*/ 
                            BEGIN

                                SELECT
                                    '121.PayToPIN' = UPPER(StateLicenseNo)
                                FROM
                                    DoctorFacility
                                WHERE
                                    DoctorFacilityId = @DoctorId
                            END
                        ELSE 					/*Facility fees*/ 
                            BEGIN
                                SELECT
                                    '121.PayToPIN' = UPPER(StateLicenseNo)
                                FROM
                                    DoctorFacility
                                WHERE
                                    DoctorFacilityId = @FacilityId
                            END
                    END
            END

        IF ( ( SELECT
                COUNT(PlaceOfServiceCode)
               FROM
                gtProcedures
               WHERE
                UniqueIdentifierId = @UniqueIdentifierId
                AND PlaceOfServiceCode = 12 ) > 0 ) 
            BEGIN
                SELECT
                    '339.Ishomelocation' = 1
            END
        ELSE 
            BEGIN
                SELECT
                    '339.Ishomelocation' = 0
            END
	
        IF ( ( SELECT
                COUNT(PatientVisitProcsId) - @pSkipProc - @ProceduresOutput
               FROM
                gtProcedures
               WHERE
                UniqueIdentifierId = @UniqueIdentifierId ) > 0 ) 
            BEGIN
                SELECT
                    @ProceduresOutput = @ProceduresOutput + @pSkipProc
                EXEC CMS1500 
                    @pPatientVisitId ,
                    NULL ,
                    @pECSFileId ,
                    @ProceduresOutput
            END

        DELETE FROM
            gtProcedures
        WHERE
            UniqueIdentifierId = @UniqueIdentifierId

    END




GO

Open in new window

0
Jeff S
Asked:
Jeff S
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Scott PletcherSenior DBACommented:
What is the data type of ml8.ANSI?

varchar(5)?

If so, it could of course contain data that is not valid for a float value.
0
 
oleggoldCommented:
declare @@tb_name varchar(277);
declare @datet datetime ;
set @datet= GETDATE();
declare @username varchar(277);
set @username= ''+USER_NAME()+'';
declare @date varchar(20);
declare @d1 varchar(1);
set @date=substring(replace(cast( CONVERT(varchar(35), GETDATE(), 127) as varchar),'-','_'),0,14)--':','_')
set @d1=substring(@date,0,1)
--cast(CONVERT(DATETIME,GETDATE(),8)  as varchar)
--'20101219 201119'
declare @m_float  float
 set @m_float=cast(CONVERT(integer,@d1) as float)
0
 
Jeff SAuthor Commented:
ANSI is actually VARCHAR 200 when I run sp_help 'MedLists'.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
oleggoldCommented:
namely ,substr to get valid integer, You can always convert integer back to float
0
 
Jesus RodriguezIT ManagerCommented:
CASE
WHEN pvp.DrugPricingCompositeUnitMId IS NULL THEN pvp.Units
ELSE  CONVERT(VARCHAR(5) , ISNULL(ml8.ANSI , ''))
END,


It's returning a float one side of the fuction and a varchar on the other for the same value. You need to convert the first one to varchar too

CASE WHEN pvp.DrugPricingCompositeUnitMId IS NULL THEN CONVERT(VARCHAR(5),pvp.Units )
                                    ELSE  CONVERT(VARCHAR(5) , ISNULL(ml8.ANSI , ''))
                                    END,
0
 
Jeff SAuthor Commented:
k-designers,

I am still getting that pesky error with your recommended changes. I have to be missing something else I think ... not sure what.
0
 
Jesus RodriguezIT ManagerCommented:
Try this

CASE WHEN isnull(pvp.DrugPricingCompositeUnitMId,0)=0 THEN pvp.Units
       ELSE  CONVERT(VARCHAR(5) , ISNULL(ml8.ANSI , ''))
       END

If not try to convert to vartchar(200) like this

CASE WHEN isnull(pvp.DrugPricingCompositeUnitMId,0)=0 THEN convert(varchar(200),pvp.Units )
       ELSE  CONVERT(VARCHAR(200) , ISNULL(ml8.ANSI , ''))
       END,
0
 
Jeff SAuthor Commented:
k-designers -

I ended up creating a temp table in my SP with your recommendations and joined into that. I thank you for you help and suggestions.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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