USE [ccrsdb]
GO
/****** Object:  StoredProcedure [dbo].[PIT_DAILY_REPORT_GENERATE]    Script Date: 9/25/2018 11:04:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- exec [PIT_DAILY_REPORT_GENERATE]
ALTER  PROCEDURE    [dbo].[PIT_DAILY_REPORT_GENERATE]  
AS
BEGIN
	-- Procedure to generate daily PIT report.
	SET NOCOUNT ON;

	print 'starting [dbo].[PIT_DAILY_REPORT_GENERATE]';

	-- basically, weave through the various database tables, populating a bunch of variables
	-- then dump the variables in the caret-separated text file format

	-- temp variables used only in the scope of a few close lines
	declare @int1 int;
	declare @str1 varchar(250);
	declare @date1 date;
	declare @money1 money;
	declare @char1 char(1);

	declare @zline varchar(8000); -- buffer for a single output line

	declare @generate_timestamp datetime2(0);
	select @generate_timestamp = CURRENT_TIMESTAMP;

	declare @pit_hcfa_batch_send_log_key int;
	declare @pit_ub_batch_send_log_key int;
	declare @pit_batch_send_log_file_byte_size bigint;
	declare @pit_batch_send_log_file_row_count bigint;
	declare @pbslk table (pit_key bigint);
	set @pit_hcfa_batch_send_log_key = -1;
	set @pit_ub_batch_send_log_key = -1;
	set @pit_batch_send_log_file_byte_size = 0;
	set @pit_batch_send_log_file_row_count = 0;

	declare @dvc_claim_key int, @dvc_claim_id varchar(50), @dvc_charge_amount money, @dvc_claim_created_date date,
		@dvc_claim_patient_key int, @dvc_ccn_region varchar(50), @dvc_etl_batch_id varchar(50), @ERASEdvc_station_key int,
		@dvc_claim_receipt_date date, @dvc_Patient_Account_Number varchar(50), @dvc_referral_number varchar(200),
		@dvc_preauth_number varchar(250), @dvc_patient_key int;

	declare @dcsn_decision varchar(50), @dcsn_decision_date_time datetime2;

	-- fxmcd are common F_xxx_MEDICAL_CLAIM_DETAILS fields
	declare @fxmcd_claim_detail_key int, @fxmcd_procedure_key int, @fxmcd_pos_key int,
		@fxmcd_charge_amount money, @fxmcd_service_units bigint, @fxmcd_start_date date, 
		@fxmcd_etl_batch_id varchar(50), @fxmcd_line_item_control_number varchar(50),
		@fxmcd_unit_label varchar(50);

	-- fpmcf are PROFESSIONAL specific: 
	declare @fpmcd_line_note varchar(500),
		@fpmcd_service_date_to date, @fpmcd_service_date_from date;

	-- fimcd are INSTITUTIONAL specific
	declare @fimcd_revenue_code varchar(50), @ficmd_service_date date;

	declare @cpat_icn varchar(50), @cpat_date_of_birth date, @cpat_gender_code varchar(50);
	declare @cpat_last_name varchar(50), @cpat_first_name varchar(50), @cpat_middle_name varchar(50);
	declare @patient_full_name varchar(250), @patient_ssn varchar(50);
	-- declare @patient_first_name varchar(50), @patient_middle_name varchar(50), @patient_last_name varchar(50);

	declare @cprov_billing_provider_npi varchar(50), @cprov_billing_provider_taxonomy varchar(50), @cprov_billing_provider_tax_id varchar(50);
	declare @cprov_billing_address varchar(250);
	declare @cprov_referring_provider_name varchar(250);
	declare @cprov_render_provider_name varchar(250), @cprov_render_provider_npi varchar(50), @cprov_render_provider_taxonomy_key varchar(50);
	declare @cprov_billing_provider_name varchar(250);
	-- declare @cprov_billing_provider_first_name varchar(50), @cprov_billing_provider_middle_name varchar(50), @cprov_billing_provider_last_name varchar(50);
	declare @cprov_servfac_provider_name varchar(250), @cprov_servfac_provider_npi varchar(50);

	declare @cprov_attending_provider_npi varchar(50), @cprov_attending_provider_taxonomy varchar(50), @cprov_attending_provider_name varchar(250);
	declare @cprov_operating_provider_npi varchar(50), @cprov_operating_provider_taxonomy varchar(50), @cprov_operating_provider_name varchar(250);
	declare @cprov_otheroperating_provider_npi varchar(50), @cprov_otheroperating_provider_taxonomy varchar(50), @cprov_otheroperating_provider_name varchar(250);

	declare @dimtaxon_render_taxonomy_code varchar(50);

	declare @ccrs_referral_referral_consult_id varchar(250);
	declare @dvs_station_id varchar(50);

	declare @dpc_pos_key int;
	declare @place_of_service varchar(250);

	declare @claim_line_number int; -- line number for this claim in invoice

	declare @clm_modifier_code varchar(50);
	declare @cld_pointer_order varchar(50);

	declare @dpc_procedure_code varchar(50);
	declare @decision_decision varchar(50);

	declare @clajud_paid_amount money, @clajud_paid_date date;

	declare @fprof_claim_detail_key int;

	declare @iir_Invoice_Key bigint, @iir_Invoice_Number varchar(25), @iir_IPPS_Check_Number varchar(250);
	declare @iir_Principal_Amount money, @iir_Check_Date date;
	declare @iir_Invoice_Amount money, @iir_adjustment_Amount money, @iir_Created_date date, @iir_Interest_Amount money;
	declare @iir_Invoice_date date, @iir_File_Number int;

	declare @cdiag_eci varchar(250);
	declare @dinstclaim_pos_code varchar(50);
	declare @diagnosis_codes varchar(250);

	declare @total_prof_claims int, @total_inst_claims int;
	set @total_prof_claims = 0;
	set @total_inst_claims = 0;

	-- declare @cocc_occurrence_code varchar(50), @cocc_occurrence_date date;
	-- declare @coccspan_occurrence_span_code varchar(50), @coccspan_occurrence_span_from date,
	--	@coccspan_occurrence_span_to date;

	declare @diag_codes table (pointer_order int, diag_code varchar(50)); -- varchar(2000);
	declare @claim_occurances table (row_num int, occurrence_code varchar(50), occurrence_date date);
	declare @claim_occurance_spans table (row_num int, occurrence_span_code varchar(50), occurrence_span_from date,
		occurrence_span_to date);
	declare @claim_value_codes table (row_num int, value_code varchar(50), value_amt money);
	declare @claim_diagnoses table (row_num int, diagnosis_key int, diagnosis_poa char(1), diagnosis_code varchar(50), qualifier varchar(50),
		start_date date);


	-- c01 determines which claims are selected for transmission to PIT
	-- 2018-07-12 select claims which have been decided in the past 14 days NOT waiting for more data (this generates a small amount of data).
	-- 2018-08-17 handle new algorithm for finding STATTION_ID (via referral number)
	declare c01 cursor for 
		SELECT 
 Decision, CLAIM_KEY, CLAIM_ID, Provider_Billed_Amount, claim_patient_key,
 Patient_Account_Number, CLM_REFERRAL_NUM, CLM_PREAUTH_NUM, VET_ICN, VET_DOB, 
 VET_GENDER, VET_LAST_NAME, VET_FIRST_NAME, VET_MIDDLE_NAME, VET_ID, 
 bill_Provider_NPI, bill_PROV_taxonomy_code, bill_PROV_TIN, bill_provider_name, bill_ProviderAddress1, -- 2
 rend_provider_name, rend_Provider_NPI, rend_PROV_taxonomy_code, serv_provider_name, serv_Provider_NPI,
 serv_provider_name, diagnosis_codes, claim_created_date
  FROM [ccrsdb].[dbo].[Profi_Insti_Dental_Claim_Header]
  where ((decision = 'PAID') or (decision = 'DENIED') or (decision = 'REJECTED'))
  and convert(date, decision_date_time) = convert(date, current_timestamp);
   -- where convert(date, decision_date_time) >= convert(date, DATEADD(day, -2, current_timestamp));
  -- where decision_date_time > DATEADD(day, -2, current_timestamp); 


	open c01;

	fetch next from c01 into 
	@dcsn_decision, @dvc_claim_key, @dvc_claim_id, @dvc_charge_amount, @dvc_claim_patient_key, 
	@dvc_Patient_Account_Number, @dvc_referral_number, @dvc_preauth_number, @cpat_icn, @cpat_date_of_birth,
	@cpat_gender_code, @cpat_last_name, @cpat_first_name, @cpat_middle_name, @patient_ssn,
	@cprov_billing_provider_npi, @cprov_billing_provider_taxonomy, @cprov_billing_provider_tax_id, @cprov_billing_provider_name, @cprov_billing_address, 
	@cprov_render_provider_name, @cprov_render_provider_npi, @cprov_render_provider_taxonomy_key, @cprov_servfac_provider_name, @cprov_servfac_provider_npi,
	@cprov_referring_provider_name, @diagnosis_codes, @dvc_claim_created_date; 

	
	-- @dcsn_decision, @dcsn_decision_date_time, @dvc_claim_key, @dvc_claim_id, @dvc_charge_amount, @dvc_claim_created_date, 
		-- @dvc_claim_patient_key, @dvc_ccn_region,
		-- @dvc_claim_receipt_date, @dvc_Patient_Account_Number, @dvc_referral_number, @dvc_etl_batch_id, @dvc_preauth_number,
		-- @dvc_patient_key;
	while @@FETCH_STATUS = 0
	begin
		-- Add SEND_LOG record if needed (note we should delay this until end of load)
		if (@pit_hcfa_batch_send_log_key = -1)
		begin
		    INSERT pit.PIT_BATCH_SEND_LOG  (claim_type, status, generate_timestamp, update_timestamp, update_user)
			  OUTPUT INSERTED.pit_batch_send_log_key into @pbslk
			  VALUES ('HCFA', 'Loading', @generate_timestamp, CURRENT_TIMESTAMP, CURRENT_USER);

			select @pit_hcfa_batch_send_log_key = pit_key from @pbslk;
			-- print '   adding PIT BATCHSEND LOG key = ' + isnull(str(@pit_batch_send_log_key), 'TILT');
		end;

		-- print 'claim ID ' + rtrim(@dvc_claim_id) + ' for claim_key ' + str(@dvc_claim_key) + ' batch key ' + isnull(str(@pit_batch_send_log_key), 'TILT');

		select @int1 = count(1)
			from [dbo].[F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS]
			where claim_key = @dvc_claim_key;

		-- select  count(1) 	from [dbo].[F_INSTITUTIONAL_MEDICAL_CLAIM_DETAILS] where claim_key = @dvc_claim_key;

		-- print 'count of [F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS] records ' + str(@int1);
		set @total_prof_claims += isnull(@int1, 0);

		-- NOTE: We need to set variables to NULL because they may be UNCHANGED if no record matches select statement
				
		set @cprov_render_provider_name = NULL;
		set @cprov_render_provider_npi = NULL;
		set @cprov_render_provider_taxonomy_key = NULL;
		select @cprov_render_provider_name = rtrim(isnull(provider_first_name + ' ', '') + isnull(provider_middle_name + ' ', '') 
		    + isnull(provider_last_name, '')), @cprov_render_provider_npi = npi,
		    @cprov_render_provider_taxonomy_key = taxonomy_key
		  from CLAIM_PROVIDER where claim_key = @dvc_claim_key and upper(provider_type) = 'RENDERING';
		
		set @dimtaxon_render_taxonomy_code = NULL;
		select @dimtaxon_render_taxonomy_code = taxonomy_code
		  from DIM_TAXONOMY 
		  where taxonomy_key = @cprov_render_provider_taxonomy_key;

		set @clajud_paid_amount = NULL;
		set @clajud_paid_date = NULL;
		select @clajud_paid_amount = paid_amount, @clajud_paid_date = paid_date
		  from CLAIM_LINE_ADJUDICATION where claim_detail_key_in = @dvc_claim_key and payer_id like 'TPA%';
		-- set @cajud_payer_paid_amount = $9.99;

		-- Place of Service. Could be one place, could be a lot. If OK at header level we propogate down, else we report on the line level
		set @dpc_pos_key = NULL;
		select @dpc_pos_key = pos_key
		  from DIM_PROFESSIONAL_CLAIM
		  where claim_key = @dvc_claim_key;

		set @place_of_service = NULL;
		if (@dpc_pos_key <> -1) select @place_of_service = pos_name
		  from DIM_PLACE_OF_SERVICE
		  where pos_key = @dpc_pos_key;


		-- print ' dvc_claim_key is ' +str( @dvc_claim_key);
		-- KLUDGE WARNING -- placing data in temp table -- is there a better way? 
		delete from @diag_codes;
		 insert into @diag_codes(pointer_order, diag_code) select pointer_order, max(ddc.diagnosis_code) as diag_code
		    from F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS fpmcd
		    inner join CLAIM_LINE_DIAGNOSIS cld on fpmcd.claim_detail_key = cld.claim_detail_key
		    inner join DIM_DIAGNOSIS_CODE ddc on ddc.diagnosis_key = cld.diagnosis_key
		    where fpmcd.claim_key = @dvc_claim_key
		    and ddc.is_current = 'Y'
		    group by pointer_order 
			;

		set @ccrs_referral_referral_consult_id = NULL;
		select @ccrs_referral_referral_consult_id = Referral_Consult_Id from ccra.Referral
		  where Referral_Number = @dvc_claim_id;

		set @dvs_station_id = NULL;
		select @dvs_station_id = station_id from DIM_VA_STATION
		  where station_key = left(@ccrs_referral_referral_consult_id, 3);

		declare cl01 cursor for
		select fpmcd.claim_detail_key, fpmcd.procedure_key, fpmcd.pos_key, fpmcd.charge_amount, fpmcd.start_date, 
		 fpmcd.service_line_number, fpmcd.etl_batch_id, fpmcd.line_note, fpmcd.service_date_to,
		 fpmcd.service_date_from, fpmcd.line_item_control_number, fpmcd.service_units, fpmcd.unit_label
			from F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS fpmcd
			where claim_key = @dvc_claim_key;
		open cl01;

		-- Now fetch "per line" data:
		-- exec [PIT_DAILY_REPORT_GENERATE]

		fetch next from cl01 into @fxmcd_claim_detail_key, @fxmcd_procedure_key, @fxmcd_pos_key, @fxmcd_charge_amount,
		 @fxmcd_start_date, @claim_line_number, @fxmcd_etl_batch_id, @fpmcd_line_note, @fpmcd_service_date_to, @fpmcd_service_date_from,
		 @fxmcd_line_item_control_number, @fxmcd_service_units, @fxmcd_unit_label;
		
		while @@FETCH_STATUS = 0
		begin

			-- print  @fxmcd_claim_detail_key;

			set @dpc_procedure_code = NULL;
			select @dpc_procedure_code = procedure_code
			  from DIM_PROCEDURE_CODE
			  where procedure_key = @fxmcd_procedure_key;

			set @decision_decision = NULL;
			select @decision_decision = decision
			  from decision
			  where claim_key = @dvc_claim_key and claim_detail_key = @fxmcd_claim_detail_key;

			-- STRING_AGG not available until SQLServer 2017! select @@VERSION says we are at 2016
			-- use kludge to do a whacked STRING_AGG

			-- STRING_AGG for Modifiers:
			set @str1 = NULL;
			set @clm_modifier_code = NULL;
			set @str1 = (select modifier_code 
			  from CLAIM_LINE_MODIFIER clm where claim_detail_key_pr = @fxmcd_claim_detail_key
			  for json auto);
			set @str1 = replace(replace(@str1, ' ', ''), '{"modifier_code":', '');
			set @clm_modifier_code = replace(replace(replace(replace(@str1, '}', ''), '"', ''), '[', ''), ']', '');


			-- STRING_AGG for procedure pointer order:
			set @str1 = NULL;
			set @cld_pointer_order = NULL;
			set @str1 = (select pointer_order 
			  from CLAIM_LINE_DIAGNOSIS cld where claim_detail_key = @fxmcd_claim_detail_key
			  for json auto); -- 165
			set @str1 = replace(replace(@str1, ' ', ''), '{"pointer_order":', '');
			set @cld_pointer_order = replace(replace(replace(@str1, '}', ''), '[', ''), ']', '');
			-- print '  pointer order:' + @cld_pointer_order ;

			 -- select cld.pointer_order as A
			 -- from CLAIM_LINE_DIAGNOSIS cld where cld.claim_detail_key = 165 for json auto

			 -- Place of Service. Could be one place, could be a lot. If OK at header level we propogate down, else we report on the line level
			if (@dpc_pos_key = -1)
			begin
				set @place_of_service = null;
				select @place_of_service = pos_name
				  from DIM_PLACE_OF_SERVICE
				  where pos_key = @fxmcd_pos_key;
			end;

			-- IPPS fetch....this may be another transmit flag
			set @iir_Invoice_Key = null;
			set @iir_Invoice_Number = null;
			set @iir_IPPS_Check_Number = null;
			set @iir_Principal_Amount = null;
			set @iir_Check_Date = null;
			set @iir_Invoice_Amount = null;
			set @iir_adjustment_Amount = null;
			set @iir_Created_date = null;
			set @iir_Interest_Amount = null;
			set @iir_Invoice_date = null;
			set @iir_File_Number = null;

			select @iir_Invoice_Key = iir.Invoice_Key, @iir_Invoice_Number = iir.Invoice_Number, @iir_IPPS_Check_Number = iir.IPPS_Check_Number,
			  @iir_Principal_Amount = iir.Principal_Amount, @iir_Check_Date = iir.Check_Date, @iir_Invoice_Amount = iir.Invoice_Amount,
			  @iir_adjustment_Amount = iir.adjustment_Amount, @iir_Created_date = iir.Created_date, @iir_Interest_Amount = iir.Interest_Amount,
			  @iir_Invoice_date = iir.Invoice_date, @iir_File_Number = iir.File_Number
			  from ipps.INVOICE iir -- defect 813121 was IPPS_INV_RC
			  inner join ipps.INVOICE_DETAILS iirc on iirc.Invoice_Key = iir.Invoice_Key -- defect 813121 was IPPS_INV_RC_DETAILS
			  where iirc.Claim_type = 'PROFESSIONAL' and  iirc.claim_key = @dvc_claim_key and iirc.Claim_detail_key = @fxmcd_claim_detail_key;
			-- print ' invoice key @iir_Invoice_Key ' + isnull( str(@iir_Invoice_Key) + ' notNULL' , 'is NULL');


	-- HCFA formatted report:

	-- set @zline = '';
	-- if text is NO or Blank then we are not providing; RESEARCH means research; YES means figure this out

    set @zline = '';    -- START ZLINE 1 - assignedpricedate
    set @zline += '^';    -- 2 - AustinCheckNumber
    set @zline += '^';    -- 3 - AustinPaidDate
    set @zline += '^' + isnull(ltrim(str(@dvc_claim_key)), '');    -- 4 - HCFAUniqueClaimID we put CCRS CLAIM_ID here
    set @zline += '^';    -- 5 - AuthNum
    set @zline += '^';    -- 6 - AuthOK
    set @zline += '^';    -- 7 - BatchCloseDate
    set @zline += '^' + isnull(ltrim(@fxmcd_etl_batch_id), '');    -- 8 - BatchID CCRS ETL Batch ID here 
    set @zline += '^';    -- 9 - BatchInternalNumber
    set @zline += '^';    -- 10 - BatchNumber
    set @zline += '^';    -- 11 - BatchOpenDate
    set @zline += '^';    -- 12 - BatchStatus
    set @zline += '^';    -- 13 - BatchTotal
    set @zline += '^';    -- 14 - Billable_Insurance
    set @zline += '^';    -- 15 - Billing_Provider_ID
    set @zline += '^' + isnull(ltrim(@cprov_billing_provider_npi), '');    -- 16 - Billing_Provider_NPI
    set @zline += '^' + isnull(ltrim(@cprov_billing_provider_taxonomy), '');    -- 17 - BILLING_PROVIDER_TAXONOMY
    set @zline += '^';    -- 18 - Box11
    set @zline += '^';    -- 19 - Box11C
    set @zline += '^' + isnull(ltrim(@cprov_referring_provider_name), '');    -- 20 - Box17
    set @zline += '^';    -- 21 - Box17A
    set @zline += '^' + isnull(ltrim(@cpat_icn), '');    -- 22 - Box1A
    set @zline += '^';    -- 23 - Box23
    set @zline += '^' + isnull(ltrim(@cprov_billing_provider_tax_id), '');    -- 24 - Box25
    set @zline += '^' + isnull(ltrim(@dvc_Patient_Account_Number), '');    -- 25 - Box26
    set @zline += '^' + isnull(ltrim(format(@fxmcd_charge_amount, '0.00')), '');    -- 26 - Box28
    set @zline += '^' + isnull(ltrim(format(@clajud_paid_amount, '0.00')), '');    -- 27 - Box29
    set @zline += '^';    -- 28 - Box30
    set @zline += '^';    -- 29 - Box31  ZZZ
    set @zline += '^' + isnull(ltrim(@cprov_render_provider_name), '');    -- 30 - box31A
    set @zline += '^' + isnull(ltrim(@cprov_servfac_provider_name), '');    -- 31 - Box32
    set @zline += '^' + isnull(ltrim(@cprov_billing_provider_name), '');    -- 32 - Box33A
    set @zline += '^' + isnull(ltrim(@cprov_billing_address), '');    -- 33 - Box33B Billing Provider Address RESEARCH
    set @zline += '^' + isnull(format(@cpat_date_of_birth, 'MM/dd/yyyy'), '');    -- 34 - Box3DOB
    set @zline += '^' + isnull(ltrim(@cpat_gender_code), '');    -- 35 - Box3Sex
    set @zline += '^' + isnull(ltrim(@cpat_first_name), '');    -- 36 - Box4
    set @zline += '^';    -- 37 - BoxRenderingProvider 
    set @zline += '^';    -- 38 - bw_claim
    set @zline += '^' + isnull(@iir_IPPS_Check_Number, '');    -- 39 - CheckNumber
    set @zline += '^HACEDI';    -- 40 - ClaimEntryType
    set @zline += '^';    -- 41 - ClaimMemo
    set @zline += '^';    -- 42 - ContractClaim
    set @zline += '^CCRSDB';    -- 43 - DatabaseID always CCRSDB
    set @zline += '^';    -- 44 - DSS_RB_BatchNumber
    set @zline += '^';    -- 45 - DSS_RB_DateCreated
    set @zline += '^';    -- 46 - ClaimID
    set @zline += '^HCFA';    -- 47 - ClaimType
    set @zline += '^';    -- 48 - DTCalculated
    set @zline += '^';    -- 49 - DTReopened - POPULATE IF REOPENED
    set @zline += '^';    -- 50 - DTReturnedFromPayment
    set @zline += '^';    -- 51 - DTSentToPayment
    set @zline += '^' + isnull(format(@dvc_claim_created_date, 'yyyyMMdd'), '');    -- 52 - DTStamp  ZZZ
    set @zline += '^' + isnull(ltrim(@dvc_etl_batch_id), '');    -- 53 - EDIBatchID = CCRS_ETL_BATCH
    set @zline += '^';    -- 54 - EDIBatchUpdateDates
    set @zline += '^';    -- 55 - EdiBillingProv
    set @zline += '^';    -- 56 - EDIClaimCount
    set @zline += '^';    -- 57 - EDIFacilityToRouteTo
    set @zline += '^';    -- 58 - EDIRenderingID
    set @zline += '^';    -- 59 - EDIStatus
    set @zline += '^';    -- 60 - EDIType
    set @zline += '^';    -- 61 - Facility
    set @zline += '^';    -- 62 - FCP
    set @zline += '^';    -- 63 - FeeProgram
    set @zline += '^';    -- 64 - FinalizationDate
    set @zline += '^1500';    -- 65 - FormType = always 1500
    set @zline += '^';    -- 66 - FPPS_ID
    set @zline += '^';    -- 67 - HACEDI_ID
    set @zline += '^';    -- 68 - HACEDI_IDUpdateSent
	-- diagnosis codes; use the temp table from above
	set @str1 = null;
	select @str1 = diag_code from @diag_codes where pointer_order = 1;
    set @zline += '^' + isnull(@str1, '');    -- 69 - HCFABox211 - CLAIM_DIAGNOSE
	set @str1 = null;
	select @str1 = diag_code from @diag_codes where pointer_order = 10;
    set @zline += '^' + isnull(@str1, '');    -- 70 - HCFABox2110
	set @str1 = null;
	select @str1 = diag_code from @diag_codes where pointer_order = 11;
    set @zline += '^' + isnull(@str1, '');    -- 71 - HCFABox2111
	set @str1 = null;
	select @str1 = diag_code from @diag_codes where pointer_order = 12;
    set @zline += '^' + isnull(@str1, '');    -- 72 - HCFABox2112
	set @str1 = null;
	select @str1 = diag_code from @diag_codes where pointer_order = 2;
    set @zline += '^' + isnull(@str1, '');    -- 73 - HCFABox212
	set @str1 = null;
	select @str1 = diag_code from @diag_codes where pointer_order = 3;
    set @zline += '^' + isnull(@str1, '');    -- 74 - HCFABox213
	set @str1 = null;
	select @str1 = diag_code from @diag_codes where pointer_order = 4;
    set @zline += '^' + isnull(@str1, '');    -- 75 - HCFABox214
	set @str1 = null;
	select @str1 = diag_code from @diag_codes where pointer_order = 5;
    set @zline += '^' + isnull(@str1, '');    -- 76 - HCFABox215
	set @str1 = null;
	select @str1 = diag_code from @diag_codes where pointer_order = 6;
    set @zline += '^' + isnull(@str1, '');    -- 77 - HCFABox216
	set @str1 = null;
	select @str1 = diag_code from @diag_codes where pointer_order = 7;
    set @zline += '^' + isnull(@str1, '');    -- 78 - HCFABox217
	set @str1 = null;
	select @str1 = diag_code from @diag_codes where pointer_order = 8;
    set @zline += '^' + isnull(@str1, '');    -- 79 - HCFABox218
	set @str1 = null;
	select @str1 = diag_code from @diag_codes where pointer_order = 9;
    set @zline += '^' + isnull(@str1, '');    -- 80 - HCFABox219 - CLAIM_DIAGNOSE
    set @zline += '^';    -- 81 - HCFAID
    set @zline += '^';    -- 82 - AllowableType
    set @zline += '^';    -- 83 - Authorized
    set @zline += '^' + isnull(format(@fpmcd_service_date_from, 'yyyyMMdd'), '');    -- 84 - Box24AFrom
    set @zline += '^' + isnull(format(@fpmcd_service_date_to, 'yyyyMMdd'), '');    -- 85 - Box24ATo
    set @zline += '^' + isnull(@place_of_service, '');    -- 86 - Box24B
    set @zline += '^';    -- 87 - Box24C
    set @zline += '^' + isnull(@dpc_procedure_code, '');    -- 88 - Box24DCPT
    set @zline += '^' + isnull(@clm_modifier_code, '');    -- 89 - Box24DMods
    set @zline += '^' + isnull(@cld_pointer_order, '');    -- 90 - Box24E
    set @zline += '^' + isnull(ltrim(format(@fxmcd_charge_amount, '0.00')), '');    -- 91 - Box24F
    set @zline += '^' + isnull(ltrim(str(@fxmcd_service_units)), '');    -- 92 - Box24G
    set @zline += '^';    -- 93 - HCFALines_Box24I_IDQualifier
    set @zline += '^' + isnull(@iir_IPPS_Check_Number, '');    -- 94 - CheckNumber
    set @zline += '^';    -- 95 - CMSAllowableAmount
    set @zline += '^';    -- 96 - CMSAllowableType
    set @zline += '^';    -- 97 - CodeSearch
    set @zline += '^' + isnull(ltrim(str(@dvc_claim_key)), '');    -- 98 - HCFALines_HCFAID --- Put our CLAIM_ID Here
    set @zline += '^';    -- 99 - HealthNetRepricedAmount 
    set @zline += '^' + isnull(ltrim(@fxmcd_line_item_control_number), '');    -- 100 - HCFALines_ID
    set @zline += '^';    -- 101 - MedicareFee
    set @zline += '^';    -- 102 - Mileage
    set @zline += '^';    -- 103 - Minutes
    set @zline += '^';    -- 104 - ModifierSearch
    set @zline += '^';    -- 105 - OtherAllowableAmount
    set @zline += '^';    -- 106 - OtherAllowableType
    set @zline += '^' + isnull(str(@iir_IPPS_Check_Number), '');    -- 107 - Paid
	if (ltrim(@decision_decision) = 'APPROVED' ) -- field 108 logic may change: LINE PAID AMOUNT when IPPS active
		set @zline += '^-1';    -- 108 - Pay
	  else
	    set @zline += '^0';    -- 108 - Pay
    set @zline += '^';    -- 109 - PaymentStatus
    set @zline += '^' + isnull(ltrim(str(@claim_line_number)), '');    -- 110 - Position ZZZ
    set @zline += '^';    -- 111 - Reason_Line ZZZ
    set @zline += '^Y' + isnull(ltrim(@cprov_render_provider_npi), '');    -- 112 - RenderingProvider
    set @zline += '^';    -- 113 - Repriced
    set @zline += '^';    -- 114 - RepricedAmount
    set @zline += '^';    -- 115 - VAFB
    set @zline += '^';    -- 116 - VAFCP
    set @zline += '^' + isnull(ltrim(@iir_Invoice_Number), '');    -- 117 - HCFALines_vain ZZZ
    set @zline += '^' + isnull(format(@iir_Check_Date, 'yyyyMMdd'), '');    -- 118 - HCFALines_vaon ZZZ
    set @zline += '^';    -- 119 - VistaAllowableAmount
    set @zline += '^';    -- 120 - VistaAllowableType
    set @zline += '^';    -- 121 - VistaID
    set @zline += '^';    -- 122 - VoucherDate ZZZ
    set @zline += '^';    -- 123 - HealthNetRepricedAmt
    set @zline += '^';    -- 124 - HERO_CLAIM_FLAG
    set @zline += '^';    -- 125 - HCFAEDISENT_ID
    set @zline += '^';    -- 126 - ImageID
    set @zline += '^';    -- 127 - Imported
    set @zline += '^';    -- 128 - ImportedBy
    set @zline += '^';    -- 129 - ImportedDTStamp
    set @zline += '^';    -- 130 - ImportedNote
    set @zline += '^';    -- 131 - inPatient
    set @zline += '^';    -- 132 - Box2
    set @zline += '^';    -- 133 - JobNumber
    set @zline += '^';    -- 134 - LastCalcdJobNumber
    set @zline += '^';    -- 135 - MirrorDateTime
    set @zline += '^';    -- 136 - Obligation
    set @zline += '^';    -- 137 - On30DayHold
    set @zline += '^';    -- 138 - OpenedByClerk
    set @zline += '^';    -- 139 - OpenedByUserID
    set @zline += '^';    -- 140 - PatientTypeID
    set @zline += '^';    -- 141 - PAY_TO_PROVIDER_TAXONOMY
    set @zline += '^';    -- 142 - PreimportedBy
    set @zline += '^';    -- 143 - PreImportedDTStamp
    set @zline += '^';    -- 144 - PreviousImportedBy
    set @zline += '^';    -- 145 - PreviousPreImportedBy
    set @zline += '^';    -- 146 - PrimaryEligibility
    set @zline += '^';    -- 147 - HCFAProvider_ID
    set @zline += '^';    -- 148 - Address
    set @zline += '^';    -- 149 - Line1
    set @zline += '^';    -- 150 - Line2
    set @zline += '^';    -- 151 - City
    set @zline += '^';    -- 152 - ProviderID
    set @zline += '^';    -- 153 - ProviderName
    set @zline += '^';    -- 154 - State
    set @zline += '^';    -- 155 - Zip
    set @zline += '^';    -- 156 - Reason
    set @zline += '^';    -- 157 - ReasonID
    set @zline += '^';    -- 158 - RecordType
    set @zline += '^';    -- 159 - REFERRING_PROVIDER_TAXONOMY
    set @zline += '^' + isnull(ltrim(@cprov_render_provider_npi), '');    -- 160 - ReferringProvider
    set @zline += '^';    -- 161 - ReleaseDate
    set @zline += '^';    -- 162 - Rendering_Facility_ID
    set @zline += '^' + isnull(ltrim(@cprov_servfac_provider_npi), '');    -- 163 - Rendering_Facility_NPI
    set @zline += '^' + isnull(ltrim(@dimtaxon_render_taxonomy_code), '');    -- 164 - RENDERING_PROV_TAXONOMY
    set @zline += '^';    -- 165 - RenderingProviderID
    set @zline += '^';    -- 166 - ReopenedReason
    set @zline += '^';    -- 167 - ReopenedUser
    set @zline += '^';    -- 168 - RepricedAmountDue
    set @zline += '^';    -- 169 - RepricedStatus
    set @zline += '^';    -- 170 - RepricedTotal
    set @zline += '^';    -- 171 - RepricersReferenceID
    set @zline += '^';    -- 172 - SC_Conditions
    set @zline += '^';    -- 173 - Scrubbed
    set @zline += '^';    -- 174 - SentToCalculate
    set @zline += '^';    -- 175 - SentToPaymentBy
    set @zline += '^';    -- 176 - SourceUniqueId
    set @zline += '^';    -- 177 - SourceSystemIdentifier
    set @zline += '^' + isnull(left(@ccrs_referral_referral_consult_id, 3), '') + isnull(@dvs_station_id, '');    -- 178 - StationID ZZZ rework
    set @zline += '^';    -- 179 - StationIFN
    set @zline += '^';    -- 180 - DSS_RB_BatchNumber
    set @zline += '^' + isnull(ltrim(@decision_decision), '');    -- 181 - HCFAStatus
    set @zline += '^';    -- 182 - SupervisorCloseDate
    set @zline += '^';    -- 183 - SuspendedNote
    set @zline += '^';    -- 184 - SuspensionReasonID
    set @zline += '^';    -- 185 - TaxIDClean
    set @zline += '^';    -- 186 - TaxIDOverride
    set @zline += '^';    -- 187 - TotalSC
    set @zline += '^';    -- 188 - va_referringphysician
    set @zline += '^';    -- 189 - VAFB
    set @zline += '^' + isnull(ltrim(@iir_Invoice_Number), '');    -- 190 - VAIN ZZZ
    set @zline += '^';    -- 191 - VAON ZZZ
    set @zline += '^';    -- 192 - VerificationInProgressDTStamp
    set @zline += '^';    -- 193 - verificationinprogressguid
    set @zline += '^';    -- 194 - Verified
    set @zline += '^';    -- 195 - VerifiedBy
    set @zline += '^';    -- 196 - VerifiedDTStamp
    set @zline += '^';    -- 197 - VISN
    set @zline += '^';    -- 198 - VistaAuthKey
    set @zline += '^';    -- 199 - VistaPatKey
    set @zline += '^' + isnull(format(@clajud_paid_date, 'yyyyMMdd'), '');    -- 200 - VoucherDate fpmcd.date_paid
    set @zline += '^';    -- 201 - WrongClaimType
    set @zline += '^';    -- 202 - HCFAEdit_ID
    set @zline += '^';    -- 203 - HCFAEdit_HCFAID
    set @zline += '^';    -- 204 - HCFAEdit_LineItem
    set @zline += '^';    -- 205 - EditCode
    set @zline += '^';    -- 206 - EditSubCode
    set @zline += '^';    -- 207 - EditMSG
    set @zline += '^';    -- 208 - EditAction
    set @zline += '^';    -- 209 - EditData
    set @zline += '^';    -- 210 - OverridenBy
    set @zline += '^';    -- 211 - Msgfrom
    set @zline += '^';    -- 212 - SCORE
    set @zline += '^';    -- 213 - CLM_DIAG_ICD 
    set @zline += '^';    -- 214 - Ambpop
    set @zline += '^';    -- 215 - ConsultIEN
    set @zline += '^';    -- 216 - CatCareDescription
    set @zline += '^';    -- 217 - AuthStationIFN
    set @zline += '^';    -- 218 - CatCareID
    set @zline += '^';    -- 219 - Estimated_Cost
    set @zline += '^';    -- 220 - Number_of_Visits
    set @zline += '^';    -- 221 - FCP
    set @zline += '^';    -- 222 - ReferringServiceID
    set @zline += '^';    -- 223 - CatCareIEN
    set @zline += '^';    -- 224 - ReportableStationNum
    set @zline += '^';    -- 225 - IPAC_Indicator
    set @zline += '^';    -- 226 - SpecialProvCat
    set @zline += '^';    -- 227 - CLNoteTXT
    set @zline += '^';    -- 228 - LLNoteTXT
    set @zline += '^' + isnull(@dvc_preauth_number, '');    -- 229 - PreAuth
    set @zline += '^' + isnull(@fxmcd_unit_label, '');    -- 230 - Unit_Code
    set @zline += '^';    -- 231 - ReopenClaimID
	if (@iir_adjustment_Amount is null)
        set @zline += '^N';    -- 232 - Adjusted
	  else
	    if (@iir_adjustment_Amount > 0.0)
		     set @zline += '^Y';    -- 232 - Adjusted
	      else
		     set @zline += '^N';    -- 232 - Adjusted

    set @zline += '^' + isnull(ltrim(format(@iir_adjustment_Amount, '0.00')), '');    -- 233 - AdjustedAmount ZZZ

	-- print 'length of test HCFA (233 fields) is ' + ltrim(str(len(@zline)));
	-- print @zline;
	insert into pit.PIT_EXPORT_CARET (claim_type, pit_batch_send_log_key, generate_timestamp, pit_record, status, update_timestamp, update_user)
	  values ('HCFA', @pit_hcfa_batch_send_log_key, @generate_timestamp, @zline, 'NEW', current_timestamp, 'Test');

	set @pit_batch_send_log_file_byte_size += len(@zline);
	set @pit_batch_send_log_file_row_count += 1;
	
		fetch next from cl01 into @fxmcd_claim_detail_key, @fxmcd_procedure_key, @fxmcd_pos_key, @fxmcd_charge_amount,
		 @fxmcd_start_date, @claim_line_number, @fxmcd_etl_batch_id, @fpmcd_line_note, @fpmcd_service_date_to, @fpmcd_service_date_from,
		 @fxmcd_line_item_control_number, @fxmcd_service_units, @fxmcd_unit_label;
		end;

		close cl01;
		deallocate cl01;





			-- exec [PIT_DAILY_REPORT_GENERATE_test_UB]

	-- 'Start PIT UB processing';


	-- UB formatted report: -- collect Z0010

	set @pit_batch_send_log_file_byte_size = 0;
	set @pit_batch_send_log_file_row_count = 0;

			-- Add SEND_LOG record if needed
		if (@pit_ub_batch_send_log_key = -1)
		begin
		    INSERT pit.PIT_BATCH_SEND_LOG  (claim_type, status, generate_timestamp, update_timestamp, update_user)
			  OUTPUT INSERTED.pit_batch_send_log_key into @pbslk
			  VALUES ('UB', 'Loading', @generate_timestamp, CURRENT_TIMESTAMP, 'LoadUser');

			select @pit_ub_batch_send_log_key = pit_key from @pbslk;
			-- print '   adding PIT BATCHSEND LOG key = ' + isnull(str(@pit_batch_send_log_key), 'TILT');
		end;

		-- print 'claim ID ' + rtrim(@dvc_claim_id) + ' for claim_key ' + str(@dvc_claim_key) + ' batch key ' + isnull(str(@pit_batch_send_log_key), 'TILT');

		select @int1 = count(1)
			from F_INSTITUTIONAL_MEDICAL_CLAIM_DETAILS
			where claim_key = @dvc_claim_key;

		-- select  count(1) 	from [dbo].[F_INSTITUTIONAL_MEDICAL_CLAIM_DETAILS] where claim_key = @dvc_claim_key;

		-- print 'count of [F_INSTITUTIONAL_MEDICAL_CLAIM_DETAILS] records ' + str(@int1);
		set @total_inst_claims += isnull(@int1, 0);

		-- NEED TO TABLE OUT MULTIPLE SELECTIONS

		delete from @claim_occurances;
		delete from @claim_occurance_spans;
		delete from @claim_value_codes;
		delete from @claim_diagnoses;

		insert into @claim_occurances (row_num, occurrence_code, occurrence_date)
			select ROW_NUMBER() over (order by occurrence_key), occurrence_code, occurrence_date
			from CLAIM_OCCURRENCE where claim_key = @dvc_claim_key;

		insert into @claim_occurance_spans (row_num, occurrence_span_code, occurrence_span_from, occurrence_span_to) 
			select ROW_NUMBER() over (order by occurrence_span_key), occurrence_span_code, occurrence_span_from, occurrence_span_to
			from CLAIM_OCCURRENCE_SPAN where claim_key = @dvc_claim_key;

		insert into @claim_value_codes (row_num, value_code, value_amt)
			select ROW_NUMBER() over (order by value_code_key), value_code, value_amt
			from CLAIM_VALUE_CODE where claim_key = @dvc_claim_key;

		insert into @claim_diagnoses (row_num, diagnosis_key, diagnosis_poa, diagnosis_code, qualifier, start_date)
			select ROW_NUMBER() over (order by claim_diagnosis_key), diagnosis_key, diagnosis_poa, diagnosis_code, qualifier, start_date
			from CLAIM_DIAGNOSIS  where claim_key = @dvc_claim_key
			and qualifier = 'PRIN';


		set @str1 = NULL;
		set @cdiag_eci = NULL;
		set @str1 = (select top (3) diagnosis_code 
			from CLAIM_DIAGNOSIS cld where claim_key = @dvc_claim_key 
			and qualifier in ('ECI1', 'ECI2', 'ECI3')
			order by qualifier
			for json auto);
		set @str1 = replace(replace(@str1, ' ', ''), '{"diagnosis_code":', '');
		set @cdiag_eci = replace(replace(replace(replace(@str1, '}', ''), '"', ''), '[', ''), ']', '');

		set @dinstclaim_pos_code = NULL;
		select @dinstclaim_pos_code = pos_code
			from DIM_PLACE_OF_SERVICE inner join DIM_INSTITUTIONAL_CLAIM on facility_code = pos_key
			where claim_key = @dvc_claim_key;


		declare cl02 cursor for
		select fimcd.claim_detail_key, fimcd.procedure_key, fimcd.service_line_number, -- was pos_key
		fimcd.charge_amount, fimcd.start_date, fimcd.etl_batch_id, 
		 fimcd.line_item_control_number, fimcd.service_units, fimcd.unit_label,
		 fimcd.revenue_code, fimcd.service_date
			from F_INSTITUTIONAL_MEDICAL_CLAIM_DETAILS fimcd
			where claim_key = @dvc_claim_key;
		open cl02;

		-- Now fetch "per line" data:
		-- exec [[PIT_DAILY_REPORT_GENERATE_test_UB]] @fimcd_revenue_code  @ficmd_service_date

		fetch next from cl02 into @fxmcd_claim_detail_key, @fxmcd_procedure_key, @fxmcd_pos_key, @fxmcd_charge_amount,
		 @fxmcd_start_date, @fxmcd_etl_batch_id,
		 @fxmcd_line_item_control_number, @fxmcd_service_units, @fxmcd_unit_label,
		 @fimcd_revenue_code, @ficmd_service_date;


		 while @@FETCH_STATUS = 0
		begin

		set @cpat_icn = NULL;
		set @cpat_date_of_birth = NULL;
		set @cpat_gender_code = NULL;
		set @cpat_last_name = NULL;
		set @cpat_first_name = NULL;
		set @cpat_middle_name = NULL;
		set @patient_ssn = NULL;
		select @cpat_icn = icn, @cpat_date_of_birth = date_of_birth, @cpat_gender_code = gender_code, @cpat_last_name = last_name, 
		  @cpat_first_name = first_name, @cpat_middle_name = middle_name, @patient_ssn = ssn
		  from DIM_PATIENT where patient_key = @dvc_patient_key; -- was CLAIM_PATIENT
		set @patient_full_name = isnull(@cpat_first_name + ' ', '') + isnull(@cpat_middle_name + ' ', '') +
			isnull(@cpat_last_name, '');

		set @cprov_billing_provider_npi = NULL;
		set @cprov_billing_provider_taxonomy = NULL;
		set @cprov_billing_provider_tax_id = NULL;
		set @cprov_billing_provider_name = NULL;
		set @cprov_billing_address = NULL;
		select @cprov_billing_provider_npi = npi, @cprov_billing_provider_taxonomy = taxonomy_key, @cprov_billing_provider_tax_id = tax_id,
		  @cprov_billing_provider_name = ltrim(isnull(provider_first_name + ' ', '') + isnull(provider_middle_name + ' ', '') 
		    + isnull(provider_last_name, '')),
		  @cprov_billing_address = isnull([address1], '') + isnull(' ' + [address2], '') + isnull(' ' + [city], '') 
		    + isnull(', ' + [state_code], '') + isnull(' ' + [postal_code], '') + isnull(' ' + [country_code], '')
		  from CLAIM_PROVIDER where claim_key = @dvc_claim_key and upper(provider_type) = 'BILLING';

		set @cprov_render_provider_name = NULL;
		set @cprov_render_provider_npi = NULL;
		set @cprov_render_provider_taxonomy_key = NULL;
		select @cprov_render_provider_name = ltrim(isnull(provider_first_name + ' ', '') + isnull(provider_middle_name + ' ', '') 
		    + isnull(provider_last_name, '')), @cprov_render_provider_npi = npi,
		    @cprov_render_provider_taxonomy_key = taxonomy_key
		  from CLAIM_PROVIDER where claim_key = @dvc_claim_key and upper(provider_type) = 'RENDERING';
		
		set @dimtaxon_render_taxonomy_code = NULL;
		select @dimtaxon_render_taxonomy_code = taxonomy_code
		  from DIM_TAXONOMY 
		  where taxonomy_key = @cprov_render_provider_taxonomy_key;

		set @cprov_servfac_provider_name = NULL;
		set @cprov_servfac_provider_npi = NULL;
		select @cprov_servfac_provider_name  = ltrim(isnull(provider_first_name + ' ', '') + isnull(provider_middle_name + ' ', '') 
		    + isnull(provider_last_name, '')), @cprov_servfac_provider_npi = npi
		  from CLAIM_PROVIDER where claim_key = @dvc_claim_key and upper(provider_type) = 'SERVICE FACILITY';

		-- create provider name
		set @cprov_referring_provider_name = NULL;
		select @cprov_referring_provider_name = ltrim(isnull(provider_first_name + ' ', '') + isnull(provider_middle_name + ' ', '') 
		    + isnull(provider_last_name, ''))
		  from CLAIM_PROVIDER where claim_key = @dvc_claim_key and upper(provider_type) = 'REFERRING';

	set @cprov_attending_provider_npi = NULL;
	set @cprov_attending_provider_taxonomy = NULL;
	set @cprov_attending_provider_name = NULL;
	select @cprov_attending_provider_name  = ltrim(isnull(provider_first_name + ' ', '') + isnull(provider_middle_name + ' ', '') 
		    + isnull(provider_last_name, '')), @cprov_attending_provider_npi = npi, @cprov_attending_provider_taxonomy = taxonomy_code
		  from CLAIM_PROVIDER where claim_key = @dvc_claim_key and upper(provider_type) = 'ATTENDING';

	set @cprov_operating_provider_npi = NULL;
	set @cprov_operating_provider_taxonomy = NULL;
	set @cprov_operating_provider_name = NULL;
	select @cprov_operating_provider_name  = ltrim(isnull(provider_first_name + ' ', '') + isnull(provider_middle_name + ' ', '') 
		    + isnull(provider_last_name, '')), @cprov_operating_provider_npi = npi, @cprov_operating_provider_taxonomy = taxonomy_code
		  from CLAIM_PROVIDER where claim_key = @dvc_claim_key and upper(provider_type) = 'OPERATING';

	set @cprov_otheroperating_provider_npi = NULL;
	set @cprov_otheroperating_provider_taxonomy = NULL;
	set @cprov_otheroperating_provider_name = NULL;
	select @cprov_otheroperating_provider_name  = ltrim(isnull(provider_first_name + ' ', '') + isnull(provider_middle_name + ' ', '') 
		    + isnull(provider_last_name, '')), @cprov_otheroperating_provider_npi = npi, @cprov_otheroperating_provider_taxonomy = taxonomy_code
		  from CLAIM_PROVIDER where claim_key = @dvc_claim_key and upper(provider_type) = 'OTHER OPERATING';

		set @clajud_paid_amount = NULL;
		set @clajud_paid_date = NULL;
		select @clajud_paid_amount = paid_amount, @clajud_paid_date = paid_date
		  from CLAIM_LINE_ADJUDICATION where claim_detail_key_in = @dvc_claim_key and payer_id like 'TPA%';
		-- set @cajud_payer_paid_amount = $9.99;

		-- Place of Service. Could be one place, could be a lot. If OK at header level we propogate down, else we report on the line level
		set @dpc_pos_key = NULL;
		select @dpc_pos_key = pos_key
		  from DIM_PROFESSIONAL_CLAIM
		  where claim_key = @dvc_claim_key;

		set @place_of_service = NULL;
		if (@dpc_pos_key <> -1) select @place_of_service = pos_name
		  from DIM_PLACE_OF_SERVICE
		  where pos_key = @dpc_pos_key;


		-- print ' dvc_claim_key is ' +str( @dvc_claim_key);
		-- KLUDGE WARNING -- placing data in temp table -- is there a better way? 
		delete from @diag_codes;
		 insert into @diag_codes(pointer_order, diag_code) select pointer_order, max(ddc.diagnosis_code) as diag_code
		    from F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS fpmcd
		    inner join CLAIM_LINE_DIAGNOSIS cld on fpmcd.claim_detail_key = cld.claim_detail_key
		    inner join DIM_DIAGNOSIS_CODE ddc on ddc.diagnosis_key = cld.diagnosis_key
		    where fpmcd.claim_key = @dvc_claim_key
		    and ddc.is_current = 'Y'
		    group by pointer_order 
			;

		set @ccrs_referral_referral_consult_id = NULL;
		select @ccrs_referral_referral_consult_id = Referral_Consult_Id from ccra.Referral
		  where Referral_Number = @dvc_claim_id;

		set @dvs_station_id = NULL;
		select @dvs_station_id = station_id from DIM_VA_STATION
		  where station_key = left(@ccrs_referral_referral_consult_id, 3);

	-- UB

    set @zline = '';    -- 1 - AccidentState
    set @zline += '^';    --y 2 - AdmissionDate
    set @zline += '^';    -- 3 - AdmissionReasonID
    set @zline += '^';    -- 4 - AdmittingDiagnosisCode
    set @zline += '^';    -- 5 - UBL_AllowableType
    set @zline += '^';    -- 6 - UBL_OtherAllowableAmount
    set @zline += '^';    --y 7 - UBL_OtherAllowableType
    set @zline += '^';    -- 8 - VistaAllowableType
    set @zline += '^';    -- 9 - VistaAllowableAmount
    set @zline += '^';    -- 10 - CMSAllowableType
    set @zline += '^';    -- 11 - CMSAllowableAmount
    set @zline += '^';    --y 12 - AssignmentBenefitsA
    set @zline += '^';    -- 13 - AssignmentBenefitsB
    set @zline += '^';    -- 14 - AssignmentBenefitsC
    set @zline += '^' + isnull(@cprov_attending_provider_npi, '');    -- 15 - AttendingPhysicianNPI
    set @zline += '^';    -- 16 - AttendingPhysicianTaxonomy
    set @zline += '^';    -- 17 - AuthorizationNumber ZZZ
    set @zline += '^';    -- 18 - AuthOK ZZZ
    set @zline += '^';    -- 19 - Authorized
    set @zline += '^';    -- 20 - BatchID
    set @zline += '^';    -- 21 - BillableInsurance
    set @zline += '^';    -- 22 - BillingProviderTaxonomy
    set @zline += '^';    -- 23 - bwclaim
    set @zline += '^';    -- 24 - CheckNumber ZZZ
    set @zline += '^';    -- 25 - ClaimEntryType
    set @zline += '^';    -- 26 - ClaimMemo
    set @zline += '^';    -- 27 - codesearch
    set @zline += '^';    -- 28 - ConditionCode1 ZZZ
    set @zline += '^';    -- 29 - ConditionCode10 ZZZ
    set @zline += '^';    -- 30 - ConditionCode11 ZZZ
    set @zline += '^';    -- 31 - ConditionCode2 ZZZ
    set @zline += '^';    -- 32 - ConditionCode3 ZZZ
    set @zline += '^';    -- 33 - ConditionCode4 ZZZ
    set @zline += '^';    -- 34 - ConditionCode5 ZZZ
    set @zline += '^';    -- 35 - ConditionCode6 ZZZ
    set @zline += '^';    -- 36 - ConditionCode7 ZZZ
    set @zline += '^';    -- 37 - ConditionCode8 ZZZ
    set @zline += '^';    -- 38 - ConditionCode9 ZZZ
    set @zline += '^';    -- 39 - ContractClaim
    set @zline += '^';    -- 40 - CreationDate ZZZ
    set @zline += '^' + isnull(ltrim(str(@fxmcd_claim_detail_key)), '');    -- 41 - DatabaseID

		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 1;
    set @zline += '^' + isnull(@str1, '');    -- 42 - Diag1
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 10;
    set @zline += '^' + isnull(@str1, '');    -- 43 - Diag10
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 10;
    set @zline += '^' + isnull(@str1, '');    -- 44 - Diag10POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 11;
    set @zline += '^' + isnull(@str1, '');    -- 45 - Diag11
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 11;
    set @zline += '^' + isnull(@str1, '');    -- 46 - Diag11POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 12;
    set @zline += '^' + isnull(@str1, '');    -- 47 - Diag12
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 12;
    set @zline += '^' + isnull(@str1, '');    -- 48 - Diag12POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 13;
    set @zline += '^' + isnull(@str1, '');    -- 49 - Diag13
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 13;
    set @zline += '^' + isnull(@str1, '');    -- 50 - Diag13POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 14;
    set @zline += '^' + isnull(@str1, '');    -- 51 - Diag14
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 14;
    set @zline += '^' + isnull(@str1, '');    -- 52 - Diag14POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 15;
    set @zline += '^' + isnull(@str1, '');    -- 53 - Diag15
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 15;
    set @zline += '^' + isnull(@str1, '');    -- 54 - Diag15POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 16;
    set @zline += '^' + isnull(@str1, '');    -- 55 - Diag16
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 16;
    set @zline += '^' + isnull(@str1, '');    -- 56 - Diag16POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 17;
    set @zline += '^' + isnull(@str1, '');    -- 57 - Diag17
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 17;
    set @zline += '^' + isnull(@str1, '');    -- 58 - Diag17POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 18;
    set @zline += '^' + isnull(@str1, '');    -- 59 - Diag18
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 18;
    set @zline += '^' + isnull(@str1, '');    -- 60 - Diag18POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 19;
    set @zline += '^' + isnull(@str1, '');    -- 61 - Diag19
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 19;
    set @zline += '^' + isnull(@str1, '');    -- 62 - Diag19POA
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 1;
    set @zline += '^' + isnull(@str1, '');    -- 63 - Diag1POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 2;
    set @zline += '^' + isnull(@str1, '');    -- 64 - Diag2
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 20;
    set @zline += '^' + isnull(@str1, '');    -- 65 - Diag20
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 20;
    set @zline += '^' + isnull(@str1, '');    -- 66 - Diag20POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 21;
    set @zline += '^' + isnull(@str1, '');    -- 67 - Diag21
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 21;
    set @zline += '^' + isnull(@str1, '');    -- 68 - Diag21POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 22;
    set @zline += '^' + isnull(@str1, '');    -- 69 - Diag22
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 22;
    set @zline += '^' + isnull(@str1, '');    -- 70 - Diag22POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 23;
    set @zline += '^' + isnull(@str1, '');    -- 71 - Diag23
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 23;
    set @zline += '^' + isnull(@str1, '');    -- 72 - Diag23POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 24;
    set @zline += '^' + isnull(@str1, '');    -- 73 - DIag24
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 24;
    set @zline += '^' + isnull(@str1, '');    -- 74 - Diag24POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 25;
    set @zline += '^' + isnull(@str1, '');    -- 75 - Diag25
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 25;
    set @zline += '^' + isnull(@str1, '');    -- 76 - Diag25POA
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 2;
    set @zline += '^' + isnull(@str1, '');    -- 77 - Diag2POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 3;
    set @zline += '^' + isnull(@str1, '');    -- 78 - Diag3
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 3;
    set @zline += '^' + isnull(@str1, '');    -- 79 - Diag3POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 4;
    set @zline += '^' + isnull(@str1, '');    -- 80 - Diag4
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 4;
    set @zline += '^' + isnull(@str1, '');    -- 81 - Diag4POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 5;
    set @zline += '^' + isnull(@str1, '');    -- 82 - Diag5
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 5;
    set @zline += '^' + isnull(@str1, '');    -- 83 - Diag5POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 6;
    set @zline += '^' + isnull(@str1, '');    -- 84 - Diag6
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 6;
    set @zline += '^' + isnull(@str1, '');    -- 85 - Diag6POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 7;
    set @zline += '^' + isnull(@str1, '');    -- 86 - Diag7
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 7;
    set @zline += '^' + isnull(@str1, '');    -- 87 - Diag7POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 8;
    set @zline += '^' + isnull(@str1, '');    -- 88 - Diag8
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 8;
    set @zline += '^' + isnull(@str1, '');    -- 89 - Diag8POA
		set @str1 = NULL;
		select @str1 = diagnosis_code from @claim_diagnoses where row_num = 9;
    set @zline += '^' + isnull(@str1, '');    -- 90 - Diag9
		set @str1 = NULL;
		select @str1 = diagnosis_poa from @claim_diagnoses where row_num = 9;
    set @zline += '^' + isnull(@str1, '');    -- 91 - Diag9POA
    set @zline += '^';    -- 92 - DischargeReasonID
    set @zline += '^';    -- 93 - DischargeStatus ZZZ
    set @zline += '^' + isnull(format(@cpat_date_of_birth, 'MM/dd/yyyy'), '');    -- 94 - DOB
    set @zline += '^';    -- 95 - DocumentsAttachedInd
    set @zline += '^';    -- 96 - DSS_Inpatient_PSD_BatchNumber
    set @zline += '^';    -- 97 - DSS_Inpatient_PSD_UB92ID
    set @zline += '^';    -- 98 - AdjustmentGroupIEN
    set @zline += '^';    -- 99 - AdjustmentReasonIEN
    set @zline += '^';    -- 100 - DischargeType
    set @zline += '^';    -- 101 - DRGAdmissionCode
    set @zline += '^';    -- 102 - DRGAmountToPay
    set @zline += '^';    -- 103 - DRGDischargeCode
    set @zline += '^';    -- 104 - LastActionID
    set @zline += '^';    -- 105 - PickedAmountAfterReprice
    set @zline += '^';    -- 106 - PricerExempt 
    set @zline += '^';    -- 107 - StatusChangeDate
    set @zline += '^';    -- 108 - VendorIEN
    set @zline += '^';    -- 109 - VendorMedicareID
    set @zline += '^';    -- 110 - PaymentIEN 
    set @zline += '^';    -- 111 - DSS_PSD_Vista_StationIFN
    set @zline += '^';    -- 112 - DSS_RB_BatchNumber
    set @zline += '^';    -- 113 - DSS_RB_DateCreated
    set @zline += '^';    -- 114 - DSS_RepricerReviewedClaimID
    set @zline += '^';    -- 115 - DSS_RepricerReviewedClaimType
    set @zline += '^';    -- 116 - DTCalculated
    set @zline += '^';    -- 117 - DTReopened
    set @zline += '^';    -- 118 - DTReturnedFromPayment
    set @zline += '^';    -- 119 - DTSentToPayment
    set @zline += '^';    -- 120 - DTStampClaim ZZZ
    set @zline += '^' + isnull(@cdiag_eci, '');    -- 121 - ECI
    set @zline += '^';    -- 122 - EdiBillingProv
    set @zline += '^';    -- 123 - EDIFacilityToRouteTo
    set @zline += '^';    -- 124 - EstAmountDueA
    set @zline += '^';    -- 125 - EstAmountDueB
    set @zline += '^';    -- 126 - EstAmountDueC
    set @zline += '^';    -- 127 - Facility
    set @zline += '^' + isnull(@cprov_billing_provider_tax_id, '');    -- 128 - FederalTaxID
    set @zline += '^';    -- 129 - FPPS_ID
    set @zline += '^';    -- 130 - FixBillableInsurance
    set @zline += '^';    -- 131 - FormType
    set @zline += '^';    -- 132 - GroupNameA (Box61a)
    set @zline += '^';    -- 133 - GroupNameB (Box61b)
    set @zline += '^';    -- 134 - GroupNameC (Box61c)
    set @zline += '^';    -- 135 - HACEDI_ID
    set @zline += '^';    -- 136 - HACEDI_UpdateSentInd
    set @zline += '^';    -- 137 - HealthNetRepricedAmount
    set @zline += '^';    -- 138 - HealthPlanIDA
    set @zline += '^';    -- 139 - HealthPlanIDB
    set @zline += '^';    -- 140 - HealthPlanIDC
    set @zline += '^';    -- 141 - HERO_CLAIM_FLAG
    set @zline += '^';    -- 142 - ImageID
    set @zline += '^';    -- 143 - Imported
    set @zline += '^';    -- 144 - ImportedBy
    set @zline += '^';    -- 145 - ImportedDTStamp
    set @zline += '^';    -- 146 - ImportedNote
    set @zline += '^';    -- 147 - InPatient
    set @zline += '^';    -- 148 - InsuranceGroupNoA
    set @zline += '^';    -- 149 - InsuranceGroupNoB
    set @zline += '^';    -- 150 - InsuranceGroupNoC
    set @zline += '^';    -- 151 - InsuredUniqueIDA
    set @zline += '^';    -- 152 - InsuredUniqueIDB
    set @zline += '^';    -- 153 - InsuredUniqueIDC
    set @zline += '^';    -- 154 - Box60A
    set @zline += '^';    -- 155 - Box60B
    set @zline += '^';    -- 156 - Box60C
    set @zline += '^';    -- 157 - InsuredsNameA
    set @zline += '^';    -- 158 - InsuredsNameB
    set @zline += '^';    -- 159 - InsuredsNameC
    set @zline += '^';    -- 160 - JobNumber
    set @zline += '^';    -- 161 - LastCalcdJobNumber
    set @zline += '^';    -- 162 - ModifierSearch

		set @str1 = NULL;
		select @str1 = occurrence_code from @claim_occurances where row_num = 1;
    set @zline += '^' + isnull(@str1, '');    -- 163 - OccCode_Box31a
		set @str1 = NULL;
		select @str1 = occurrence_code from @claim_occurances where row_num = 2;
    set @zline += '^' + isnull(@str1, '');    -- 164 - OccCode_Box31b
		set @str1 = NULL;
		select @str1 = occurrence_code from @claim_occurances where row_num = 3;
    set @zline += '^' + isnull(@str1, '');    -- 165 - OccCode_Box32a
		set @str1 = NULL;
		select @str1 = occurrence_code from @claim_occurances where row_num = 4;
    set @zline += '^' + isnull(@str1, '');    -- 166 - OccCode_Box32b
		set @str1 = NULL;
		select @str1 = occurrence_code from @claim_occurances where row_num = 5;
    set @zline += '^' + isnull(@str1, '');    -- 167 - OccCode_Box33a
		set @str1 = NULL;
		select @str1 = occurrence_code from @claim_occurances where row_num = 6;
    set @zline += '^' + isnull(@str1, '');    -- 168 - OccCode_Box33b
		set @str1 = NULL;
		select @str1 = occurrence_code from @claim_occurances where row_num = 7;
    set @zline += '^' + isnull(@str1, '');    -- 169 - OccCode_Box34a
		set @str1 = NULL;
		select @str1 = occurrence_code from @claim_occurances where row_num = 8;
    set @zline += '^' + isnull(@str1, '');    -- 170 - OccCode_Box34b

		set @date1 = NULL;
		select @date1 = occurrence_date from @claim_occurances where row_num = 1;
    set @zline += '^' + isnull(format(@date1, 'yyyyMMdd'), '');    -- 171 - OccDate_Box31a
		set @date1 = NULL;
		select @date1 = occurrence_date from @claim_occurances where row_num = 2;
    set @zline += '^' + isnull(format(@date1, 'yyyyMMdd'), '');    -- 172 - OccDate_Box31b
		set @date1 = NULL;
		select @date1 = occurrence_date from @claim_occurances where row_num = 3;
    set @zline += '^' + isnull(format(@date1, 'yyyyMMdd'), '');    -- 173 - OccDate_Box32a
		set @date1 = NULL;
		select @date1 = occurrence_date from @claim_occurances where row_num = 4;
    set @zline += '^' + isnull(format(@date1, 'yyyyMMdd'), '');    -- 174 - OccDate_Box32b
		set @date1 = NULL;
		select @date1 = occurrence_date from @claim_occurances where row_num = 5;
    set @zline += '^' + isnull(format(@date1, 'yyyyMMdd'), '');    -- 175 - OccDate_Box33a
		set @date1 = NULL;
		select @date1 = occurrence_date from @claim_occurances where row_num = 6;
    set @zline += '^' + isnull(format(@date1, 'yyyyMMdd'), '');    -- 176 - OccDate_Box33b	
		set @date1 = NULL;
		select @date1 = occurrence_date from @claim_occurances where row_num = 7;
    set @zline += '^' + isnull(format(@date1, 'yyyyMMdd'), '');    -- 177 - OccDate_Box34a
		set @date1 = NULL;
		select @date1 = occurrence_date from @claim_occurances where row_num = 8;
    set @zline += '^' + isnull(format(@date1, 'yyyyMMdd'), '');    -- 178 - OccDate_Box34b

		set @str1 = NULL;
		select @str1 = occurrence_span_code from @claim_occurance_spans where row_num = 1;
    set @zline += '^' + isnull(@str1, '');    -- 179 - OccSpanCode_Box35a
		set @str1 = NULL;
		select @str1 = occurrence_span_code from @claim_occurance_spans where row_num = 2;
    set @zline += '^' + isnull(@str1, '');    -- 180 - OccSpanCode_Box35b
		set @str1 = NULL;
		select @str1 = occurrence_span_code from @claim_occurance_spans where row_num = 3;
    set @zline += '^' + isnull(@str1, '');    -- 181 - OccSpanCode_Box36a
		set @str1 = NULL;
		select @str1 = occurrence_span_code from @claim_occurance_spans where row_num = 4;
    set @zline += '^' + isnull(@str1, '');    -- 182 - OccSpanCode_Box36b

		set @date1 = NULL;
		select @date1 = occurrence_span_from from @claim_occurance_spans where row_num = 1;
    set @zline += '^' + isnull(format(@date1, 'yyyyMMdd'), '');    -- 183 - OccSpanFrom_Box35a
		set @date1 = NULL;
		select @date1 = occurrence_span_from from @claim_occurance_spans where row_num = 2;
    set @zline += '^' + isnull(format(@date1, 'yyyyMMdd'), '');    -- 184 - OccSpanFrom_Box35b
		set @date1 = NULL;
		select @date1 = occurrence_span_from from @claim_occurance_spans where row_num = 3;
    set @zline += '^' + isnull(format(@date1, 'yyyyMMdd'), '');    -- 185 - OccSpanFrom_Box36a
		set @date1 = NULL;
		select @date1 = occurrence_span_from from @claim_occurance_spans where row_num = 4;
    set @zline += '^' + isnull(format(@date1, 'yyyyMMdd'), '');    -- 186 - OccSpanFrom_Box36b

		set @date1 = NULL;
		select @date1 = occurrence_span_to from @claim_occurance_spans where row_num = 1;
    set @zline += '^' + isnull(format(@date1, 'yyyyMMdd'), '');    -- 187 - OccSpanThrough_Box35a
		set @date1 = NULL;
		select @date1 = occurrence_span_to from @claim_occurance_spans where row_num = 2;
    set @zline += '^' + isnull(format(@date1, 'yyyyMMdd'), '');    -- 188 - OccSpanThrough_Box35b
		set @date1 = NULL;
		select @date1 = occurrence_span_to from @claim_occurance_spans where row_num = 3;
    set @zline += '^' + isnull(format(@date1, 'yyyyMMdd'), '');    -- 189 - OccSpanThrough_Box36a
		set @date1 = NULL;
		select @date1 = occurrence_span_to from @claim_occurance_spans where row_num = 4;
    set @zline += '^' + isnull(format(@date1, 'yyyyMMdd'), '');    -- 190 - OccSpanThrough_Box36b
	
    set @zline += '^';    -- 191 - OCRCompleted
    set @zline += '^';    -- 192 - On30DayHold
    set @zline += '^' + isnull(@cprov_operating_provider_name, '');    -- 193 - OperatingPhysicianName
    set @zline += '^' + isnull(@cprov_operating_provider_npi, '');    -- 194 - OperationPhysicianNPI
    set @zline += '^';    -- 195 - OtherProcedureCodeA (Box74a) ZZZ
    set @zline += '^';    -- 196 - OtherProcedureCodeB (Box74b) ZZZ
    set @zline += '^';    -- 197 - OtherProcedureCodeC (Box74c) ZZZ
    set @zline += '^';    -- 198 - OtherProcedureCodeD (Box74d) ZZZ
    set @zline += '^';    -- 199 - OtherProcedureCodeE (Box74e) ZZZ
    set @zline += '^';    -- 200 - OtherProcedureDateA (Box74a) ZZZ
    set @zline += '^';    -- 201 - OtherProcedureDateB (Box74b) ZZZ
    set @zline += '^';    -- 202 - OtherProcedureDateC (Box74c) ZZZ
    set @zline += '^';    -- 203 - OtherProcedureDateD (Box74d) ZZZ
    set @zline += '^';    -- 204 - OtherProcedureDateE (Box74e) ZZZ
    set @zline += '^' + isnull(@cprov_otheroperating_provider_name, '');    -- 205 - OtherPhysicianNameA
    set @zline += '^';    -- 206 - OtherPhysicianNameB
    set @zline += '^' + isnull(@cprov_otheroperating_provider_npi, '');    -- 207 - OtherPhysicianNPIA
    set @zline += '^';    -- 208 - OtherPhysicianNPIB
    set @zline += '^' + isnull(@patient_ssn, '');    -- 209 - PatientSSN
    set @zline += '^';    -- 210 - PatientControlNumber ZZZ
    set @zline += '^' + isnull(@patient_full_name, '');    -- 211 - PatientName
    set @zline += '^';    -- 212 - PatientsRelationShipA
    set @zline += '^';    -- 213 - PatientsRelationShipB
    set @zline += '^';    -- 214 - PatientsRelationShipC
    set @zline += '^';    -- 215 - PatientTypeID
    set @zline += '^';    -- 216 - Pay ZZZ
    set @zline += '^';    -- 217 - PAY_TO_PROVIDER_TAXONOMY
    set @zline += '^';    -- 218 - PayerNameA
    set @zline += '^';    -- 219 - PayerNameB
    set @zline += '^';    -- 220 - PayerNameC
    set @zline += '^';    -- 221 - PaymentFacilityID
    set @zline += '^';    -- 222 - PaymentStatus
    set @zline += '^' + isnull(@cprov_billing_provider_name, '');    -- 223 - PhysicianName
    set @zline += '^' + isnull(@dinstclaim_pos_code, '');    -- 224 - POS
    set @zline += '^';    -- 225 - PPSCode
    set @zline += '^';    -- 226 - PreImportedBy
    set @zline += '^';    -- 227 - PreImportedDTStamp
    set @zline += '^';    -- 228 - PreviousImportedBy
    set @zline += '^';    -- 229 - PreviousPreImportedBy
    set @zline += '^';    -- 230 - PrimaryEligibility
    set @zline += '^';    -- 231 - PrinProcCode ZZZ
    set @zline += '^';    -- 232 - PrinProcDate ZZZ
    set @zline += '^';    -- 233 - PriorPaymentsA
    set @zline += '^';    -- 234 - PriorPaymentsB
    set @zline += '^';    -- 235 - PriorPaymentsC
    set @zline += '^' + isnull(@cprov_billing_provider_npi, '');    -- 236 - ProviderNPI
    set @zline += '^';    -- 237 - ProviderID
    set @zline += '^';    -- 238 - UBL_Reason ZZZ
    set @zline += '^';    -- 239 - ReasonID ZZZ
    set @zline += '^';    -- 240 - RecordType
    set @zline += '^';    -- 241 - REFERRING_PROVIDER_TAXONOMY
    set @zline += '^';    -- 242 - RejectedNote ZZZ
    set @zline += '^';    -- 243 - RelInfoA
    set @zline += '^';    -- 244 - RelInfoB
    set @zline += '^';    -- 245 - RelInfoC
    set @zline += '^';    -- 246 - ReopenedReason
    set @zline += '^';    -- 247 - ReopenedUser
    set @zline += '^';    -- 248 - Repriced
    set @zline += '^';    -- 249 - RepricedAmount
    set @zline += '^';    -- 250 - RepricedStatus
    set @zline += '^';    -- 251 - RepricersReferenceID
    set @zline += '^';    -- 252 - SC_Conditions
    set @zline += '^';    -- 253 - Scrubbed
    set @zline += '^';    -- 254 - SentToCalculateInd
    set @zline += '^';    -- 255 - SentToPaymentBy
    set @zline += '^' + isnull(@cpat_gender_code, '');    -- 256 - Sex
    set @zline += '^CCRS';    -- 257 - SourceSystemIdentifier
    set @zline += '^';    -- 258 - Box6From ZZZ
    set @zline += '^';    -- 259 - Box6To ZZZ
    set @zline += '^';    -- 260 - StationID ZZZ
    set @zline += '^';    -- 261 - DSS_RB_StationIFN
    set @zline += '^';    -- 262 - Status ZZZ
    set @zline += '^';    -- 263 - SuspendedNote
    set @zline += '^';    -- 264 - SuspensionReasonID
    set @zline += '^';    -- 265 - TaxIDClean ZZZ
    set @zline += '^';    -- 266 - TaxIDOverride
    set @zline += '^';    -- 267 - TotalCharges ZZZ
    set @zline += '^';    -- 268 - TypeOfBill ZZZ
    set @zline += '^' + isnull(ltrim(str(@dvc_claim_key)), '');    -- 269 - UB92UniqueClaimID
    set @zline += '^';    -- 270 - EditAction ZZZ
    set @zline += '^';    -- 271 - EditCode ZZZ
    set @zline += '^';    -- 272 - EditData ZZZ
    set @zline += '^';    -- 273 - EditMSG
    set @zline += '^';    -- 274 - EditSubCode
    set @zline += '^';    -- 275 - UBE_ID
    set @zline += '^';    -- 276 - LineItem
    set @zline += '^';    -- 277 - MsgFrom
    set @zline += '^';    -- 278 - UBE_UB92ID
    set @zline += '^';    -- 279 - UBL_UB92ID
    set @zline += '^' + isnull(ltrim(@fxmcd_line_item_control_number), '');    -- 280 - UBL_ID
    set @zline += '^' + isnull(@fimcd_revenue_code, '');    -- 281 - UBL_Box42
    set @zline += '^';    -- 282 - UBL_Box44
    set @zline += '^';    -- 283 - Box44Mods
    set @zline += '^' + isnull(ltrim(format(@fxmcd_charge_amount, '0.00')), '');    -- 284 - Box45
    set @zline += '^' + isnull(ltrim(str(@fxmcd_service_units)), '');    -- 285 - Box46
    set @zline += '^';    -- 286 - Box47 ZZZ
    set @zline += '^';    -- 287 - Box48
    set @zline += '^';    -- 288 - Paid ZZZ
    set @zline += '^';    -- 289 - Position ZZZ
    set @zline += '^';    -- 290 - UBL_vafcp
    set @zline += '^';    -- 291 - UBL_vain
    set @zline += '^';    -- 292 - UBL_vaon
    set @zline += '^';    -- 293 - UBL_VAFB
    set @zline += '^';    -- 294 - VAFCP
    set @zline += '^';    -- 295 - VAIN ZZZ

		set @str1 = NULL;
		select @str1 = value_code from @claim_value_codes where row_num = 1;
    set @zline += '^' + isnull(@str1, '');    -- 296 - ValueCode_Box39a
		set @str1 = NULL;
		select @str1 = value_code from @claim_value_codes where row_num = 2;
    set @zline += '^';    -- 297 - ValueCode_Box39b
		set @str1 = NULL;
		select @str1 = value_code from @claim_value_codes where row_num = 3;
    set @zline += '^';    -- 298 - ValueCode_Box39c
		set @str1 = NULL;
		select @str1 = value_code from @claim_value_codes where row_num = 4;
    set @zline += '^';    -- 299 - ValueCode_Box39d
		set @str1 = NULL;
		select @str1 = value_code from @claim_value_codes where row_num = 5;
    set @zline += '^';    -- 300 - ValueCode_Box40a
		set @str1 = NULL;
		select @str1 = value_code from @claim_value_codes where row_num = 6;
    set @zline += '^';    -- 301 - ValueCode_Box40b
		set @str1 = NULL;
		select @str1 = value_code from @claim_value_codes where row_num = 7;
    set @zline += '^';    -- 302 - ValueCode_Box40c
		set @str1 = NULL;
		select @str1 = value_code from @claim_value_codes where row_num = 8;
    set @zline += '^';    -- 303 - ValueCode_Box40d
		set @str1 = NULL;
		select @str1 = value_code from @claim_value_codes where row_num = 9;
    set @zline += '^';    -- 304 - ValueCode_Box41a
		set @str1 = NULL;
		select @str1 = value_code from @claim_value_codes where row_num = 10;
    set @zline += '^';    -- 305 - ValueCode_Box41b	
		set @str1 = NULL;
		select @str1 = value_code from @claim_value_codes where row_num = 11;
    set @zline += '^';    -- 306 - ValueCode_Box41c
		set @str1 = NULL;
		select @str1 = value_code from @claim_value_codes where row_num = 12;
    set @zline += '^';    -- 307 - ValueCode_Box41d

		set @money1 = NULL;
		select @money1 = value_amt from @claim_value_codes where row_num = 1;
    set @zline += '^' + isnull(format(@money1, 'N'), '');    -- 308 - ValueCodeAmt_Box39a
    	set @money1 = NULL;
		select @money1 = value_amt from @claim_value_codes where row_num = 2;
    set @zline += '^' + isnull(format(@money1, 'N'), '');    -- 309 - ValueCodeAmt_Box39b
    	set @money1 = NULL;
		select @money1 = value_amt from @claim_value_codes where row_num = 3;
    set @zline += '^' + isnull(format(@money1, 'N'), '');    -- 310 - ValueCodeAmt_Box39c
    	set @money1 = NULL;
		select @money1 = value_amt from @claim_value_codes where row_num = 4;
    set @zline += '^' + isnull(format(@money1, 'N'), '');    -- 311 - ValueCodeAmt_Box39d
    	set @money1 = NULL;
		select @money1 = value_amt from @claim_value_codes where row_num = 5;
    set @zline += '^' + isnull(format(@money1, 'N'), '');    -- 312 - ValueCodeAmt_Box40a
    	set @money1 = NULL;
		select @money1 = value_amt from @claim_value_codes where row_num = 6;
    set @zline += '^' + isnull(format(@money1, 'N'), '');    -- 313 - ValueCodeAmt_Box40b
    	set @money1 = NULL;
		select @money1 = value_amt from @claim_value_codes where row_num = 7;
    set @zline += '^' + isnull(format(@money1, 'N'), '');    -- 314 - ValueCodeAmt_Box40c
    	set @money1 = NULL;
		select @money1 = value_amt from @claim_value_codes where row_num = 8;
    set @zline += '^' + isnull(format(@money1, 'N'), '');    -- 315 - ValueCodeAmt_Box40d
    	set @money1 = NULL;
		select @money1 = value_amt from @claim_value_codes where row_num = 9;
    set @zline += '^' + isnull(format(@money1, 'N'), '');    -- 316 - ValueCodeAmt_Box41a
    	set @money1 = NULL;
		select @money1 = value_amt from @claim_value_codes where row_num = 10;
    set @zline += '^' + isnull(format(@money1, 'N'), '');    -- 317 - ValueCodeAmt_Box41b
    	set @money1 = NULL;
		select @money1 = value_amt from @claim_value_codes where row_num = 11;
    set @zline += '^' + isnull(format(@money1, 'N'), '');    -- 318 - ValueCodeAmt_Box41c
    	set @money1 = NULL;
		select @money1 = value_amt from @claim_value_codes where row_num = 12;
    set @zline += '^' + isnull(format(@money1, 'N'), '');    -- 319 - ValueCodeAmt_Box41d

    set @zline += '^';    -- 320 - VAON ZZZ
    set @zline += '^';    -- 321 - VARepricedAmount
    set @zline += '^';    -- 322 - Verified
    set @zline += '^';    -- 323 - VerifiedBy
    set @zline += '^';    -- 324 - VerifiedDTStamp
    set @zline += '^';    -- 325 - VistaAuthKey
    set @zline += '^';    -- 326 - VistaID
    set @zline += '^';    -- 327 - VistaPatKey
    set @zline += '^';    -- 328 - VoucherDate ZZZ
    set @zline += '^';    -- 329 - VAFB
    set @zline += '^';    -- 330 - SCORE
    set @zline += '^';    -- 331 - CLM_DIAG_ICD 
    set @zline += '^';    -- 332 - DRG_Number
    set @zline += '^';    -- 333 - Approve_Dates_From
    set @zline += '^';    -- 334 - Approved_Dates_To
    set @zline += '^';    -- 335 - Disapproved_Dates_From
    set @zline += '^';    -- 336 - Disapproved_Dates_To
    set @zline += '^';    -- 337 - Number_Of_Approved_Days
    set @zline += '^';    -- 338 - Average_Length_of_Stay
    set @zline += '^';    -- 339 - DRG_Price
    set @zline += '^';    -- 340 - CalculatedPricePerDay
    set @zline += '^';    -- 341 - PPSPercentage
    set @zline += '^';    -- 342 - DisapprovedReasons
    set @zline += '^';    -- 343 - Disapproved_Days
    set @zline += '^';    -- 344 - DRG_Per_Diem_Rate
    set @zline += '^';    -- 345 - DRG_Weight
    set @zline += '^';    -- 346 - AdmissionScenarioReason
    set @zline += '^';    -- 347 - DischargeScenarioReason
    set @zline += '^';    -- 348 - Minutes ZZZ
    set @zline += '^';    -- 349 - Mileage ZZZ
    set @zline += '^';    -- 350 - ConsultIEN ZZZ
    set @zline += '^';    -- 351 - CatCareDescription
    set @zline += '^';    -- 352 - AuthStationIFN
    set @zline += '^';    -- 353 - CatCareID
    set @zline += '^';    -- 354 - Estimated_Cost
    set @zline += '^';    -- 355 - Number_of_Visits
    set @zline += '^';    -- 356 - FCP
    set @zline += '^';    -- 357 - ReferringServiceID
    set @zline += '^';    -- 358 - CatCareIEN
    set @zline += '^';    -- 359 - ReportableStationNum
    set @zline += '^';    -- 360 - IPAC_Indicator
    set @zline += '^';    -- 361 - SpecialProvCat
    set @zline += '^';    -- 362 - CLNoteTXT
    set @zline += '^';    -- 363 - LLNoteTXT
    set @zline += '^';    -- 364 - PreAuth ZZZ
    set @zline += '^';    -- 365 - Discharge_Hour ZZZ
    set @zline += '^';    -- 366 - Admission_Time ZZZ
    set @zline += '^';    -- 367 - Admission_Source
    set @zline += '^' + isnull(@fxmcd_unit_label, '');    -- 368 - Unit_Code
    set @zline += '^';    -- 369 - Pat_Reason_Code ZZZ
    set @zline += '^';    -- 370 - ReopenClaimID
    set @zline += '^';    -- 371 - Adjusted ZZZ
    set @zline += '^';    -- 372 - AdjustedAmount ZZZ

	 -- print 'length of test UB04 (372 fields) is ' + ltrim(str(len(@zline)));
	-- print @zline;
	-- print 'inserting ub for ' + str(@fxmcd_claim_detail_key);
		insert into pit.PIT_EXPORT_CARET (claim_type, pit_batch_send_log_key, generate_timestamp, pit_record, status, update_timestamp, update_user)
	  values ('UB', @pit_ub_batch_send_log_key, @generate_timestamp, @zline, 'NEW', current_timestamp, 'Test');
	-- print '.';

	set @pit_batch_send_log_file_byte_size += len(@zline);
	set @pit_batch_send_log_file_row_count += 1;
	

			fetch next from cl02 into @fxmcd_claim_detail_key, @fxmcd_procedure_key, @fxmcd_pos_key, @fxmcd_charge_amount,
		 @fxmcd_start_date, @fxmcd_etl_batch_id,
		 @fxmcd_line_item_control_number, @fxmcd_service_units, @fxmcd_unit_label,
		 @fimcd_revenue_code, @ficmd_service_date;


		-- fetch next from c02 into @dcsn_decision, @dcsn_decision_date_time, @dvc_claim_key, @dvc_claim_id, @dvc_charge_amount, @dvc_claim_created_date, 
		--	@dvc_claim_patient_key, @dvc_ccn_region,
		--	@dvc_claim_receipt_date, @dvc_Patient_Account_Number, @dvc_referral_number, @dvc_etl_batch_id, @dvc_station_key, @dvc_preauth_number,
		--	@dvc_patient_key;

	end;
	close cl02;
	deallocate cl02;



		-- NULL OUT VARIABLES to prevent leakage between records.
		-- (if a SELECT returns no rows, the local variable is unchanged)
		
		set @dcsn_decision = NULL;
		set @dcsn_decision_date_time = NULL;
		set @dvc_claim_key = NULL;
		set @dvc_claim_id = NULL;
		set @dvc_charge_amount = NULL;
		set @dvc_claim_created_date = NULL;
		set @dvc_claim_patient_key = NULL;
		set @dvc_ccn_region = NULL;
		set @dvc_claim_receipt_date = NULL;
		set @dvc_Patient_Account_Number = NULL;
		set @dvc_referral_number = NULL;
		set @dvc_etl_batch_id = NULL;
		-- set @dvc_station_key = NULL;
		set @dvc_preauth_number = NULL;
		set @dvc_patient_key = NULL;

	fetch next from c01 into 
	@dcsn_decision, @dvc_claim_key, @dvc_claim_id, @dvc_charge_amount, @dvc_claim_patient_key, 
	@dvc_Patient_Account_Number, @dvc_referral_number, @dvc_preauth_number, @cpat_icn, @cpat_date_of_birth,
	@cpat_gender_code, @cpat_last_name, @cpat_first_name, @cpat_middle_name, @patient_ssn,
	@cprov_billing_provider_npi, @cprov_billing_provider_taxonomy, @cprov_billing_provider_tax_id, @cprov_billing_provider_name, @cprov_billing_address, 
	@cprov_render_provider_name, @cprov_render_provider_npi, @cprov_render_provider_taxonomy_key, @cprov_servfac_provider_name, @cprov_servfac_provider_npi,
	@cprov_referring_provider_name, @diagnosis_codes, @dvc_claim_created_date; 


	end;
	close c01; -- zoob 
	deallocate c01;

	-- possibly load up header lines into export table. If it is TRUNCATEd these lines disappear.

	if not exists ( select status from pit.PIT_EXPORT_CARET where claim_type = 'HCFA' and status = 'HEADER') 
	begin
		insert into pit.PIT_EXPORT_CARET (claim_type, pit_batch_send_log_key, generate_timestamp, status, update_timestamp, update_user, pit_record) 
 values ('HCFA', -1, '2050-01-01', 'HEADER', CURRENT_TIMESTAMP, CURRENT_USER,
  'assignedpricedate^AustinCheckNumber^AustinPaidDate^HCFAUniqueClaimID^AuthNum^AuthOK^BatchCloseDate^BatchID^BatchInternalNumber^BatchNumber'
  + '^BatchOpenDate^BatchStatus^BatchTotal^Billable_Insurance^Billing_Provider_ID^Billing_Provider_NPI^BILLING_PROVIDER_TAXONOMY^Box11^Box11C^Box17'
  + '^Box17A^Box1A^Box23^Box25^Box26^Box28^Box29^Box30^Box31^box31A^Box32^Box33A^Box33B^Box3DOB^Box3Sex^Box4^BoxRenderingProvider^bw_claim^CheckNumber'
  + '^ClaimEntryType^ClaimMemo^ContractClaim^DatabaseID^DSS_RB_BatchNumber^DSS_RB_DateCreated^ClaimID^ClaimType^DTCalculated^DTReopened'
  + '^DTReturnedFromPayment^DTSentToPayment^DTStamp^EDIBatchID^EDIBatchUpdateDates^EdiBillingProv^EDIClaimCount^EDIFacilityToRouteTo^EDIRenderingID'
  + '^EDIStatus^EDIType^Facility^FCP^FeeProgram^FinalizationDate^FormType^FPPS_ID^HACEDI_ID^HACEDI_IDUpdateSent^HCFABox211^HCFABox2110^HCFABox2111'
  + '^HCFABox2112^HCFABox212^HCFABox213^HCFABox214^HCFABox215^HCFABox216^HCFABox217^HCFABox218^HCFABox219^HCFAID^AllowableType^Authorized^Box24AFrom'
  + '^Box24ATo^Box24B^Box24C^Box24DCPT^Box24DMods^Box24E^Box24F^Box24G^HCFALines_Box24I_IDQualifier^CheckNumber^CMSAllowableAmount^CMSAllowableType'
  + '^CodeSearch^HCFALines_HCFAID^HealthNetRepricedAmount^HCFALines_ID^MedicareFee^Mileage^Minutes^ModifierSearch^OtherAllowableAmount'
  + '^OtherAllowableType^Paid^Pay^PaymentStatus^Position^Reason_Line^RenderingProvider^Repriced^RepricedAmount^VAFB^VAFCP^HCFALines_vain'
  + '^HCFALines_vaon^VistaAllowableAmount^VistaAllowableType^VistaID^VoucherDate^HealthNetRepricedAmt^HERO_CLAIM_FLAG^HCFAEDISENT_ID^ImageID^Imported'
  + '^ImportedBy^ImportedDTStamp^ImportedNote^inPatient^Box2^JobNumber^LastCalcdJobNumber^MirrorDateTime^Obligation^On30DayHold^OpenedByClerk'
  + '^OpenedByUserID^PatientTypeID^PAY_TO_PROVIDER_TAXONOMY^PreimportedBy^PreImportedDTStamp^PreviousImportedBy^PreviousPreImportedBy^PrimaryEligibility'
  + '^HCFAProvider_ID^Address^Line1^Line2^City^ProviderID^ProviderName^State^Zip^Reason^ReasonID^RecordType^REFERRING_PROVIDER_TAXONOMY^ReferringProvider'
  + '^ReleaseDate^Rendering_Facility_ID^Rendering_Facility_NPI^RENDERING_PROV_TAXONOMY^RenderingProviderID^ReopenedReason^ReopenedUser^RepricedAmountDue'
  + '^RepricedStatus^RepricedTotal^RepricersReferenceID^SC_Conditions^Scrubbed^SentToCalculate^SentToPaymentBy^SourceUniqueId^SourceSystemIdentifier'
  + '^StationID^StationIFN^DSS_RB_BatchNumber^HCFAStatus^SupervisorCloseDate^SuspendedNote^SuspensionReasonID^TaxIDClean^TaxIDOverride^TotalSC'
  + '^va_referringphysician^VAFB^VAIN^VAON^VerificationInProgressDTStamp^verificationinprogressguid^Verified^VerifiedBy^VerifiedDTStamp^VISN^VistaAuthKey'
  + '^VistaPatKey^VoucherDate^WrongClaimType^HCFAEdit_ID^HCFAEdit_HCFAID^HCFAEdit_LineItem^EditCode^EditSubCode^EditMSG^EditAction^EditData^OverridenBy'
  + '^Msgfrom^SCORE^CLM_DIAG_ICD ^Ambpop^ConsultIEN^CatCareDescription^AuthStationIFN^CatCareID^Estimated_Cost^Number_of_Visits^FCP^ReferringServiceID'
  + '^CatCareIEN^ReportableStationNum^IPAC_Indicator^SpecialProvCat^CLNoteTXT^LLNoteTXT^PreAuth^Unit_Code^ReopenClaimID^Adjusted^AdjustedAmount');
		print ' Reloaded HCFA header into export table.';
	end;


	if not exists ( select status from pit.PIT_EXPORT_CARET where claim_type = 'UB' and status = 'HEADER') 
	begin
		insert into pit.PIT_EXPORT_CARET (claim_type, pit_batch_send_log_key, generate_timestamp, status, update_timestamp, update_user, pit_record) 
 values ('UB', -1, '2050-01-01', 'HEADER', CURRENT_TIMESTAMP, CURRENT_USER,
  'AccidentState^AdmissionDate^AdmissionReasonID^AdmittingDiagnosisCode^UBL_AllowableType^UBL_OtherAllowableAmount^UBL_OtherAllowableType^VistaAllowableType'
  + '^VistaAllowableAmount^CMSAllowableType^CMSAllowableAmount^AssignmentBenefitsA^AssignmentBenefitsB^AssignmentBenefitsC^AttendingPhysicianNPI'
  + '^AttendingPhysicianTaxonomy^AuthorizationNumber^AuthOK^Authorized^BatchID^BillableInsurance^BillingProviderTaxonomy^bwclaim^CheckNumber^ClaimEntryType'
  + '^ClaimMemo^codesearch^ConditionCode1^ConditionCode10^ConditionCode11^ConditionCode2^ConditionCode3^ConditionCode4^ConditionCode5^ConditionCode6'
  + '^ConditionCode7^ConditionCode8^ConditionCode9^ContractClaim^CreationDate^DatabaseID^Diag1^Diag10^Diag10POA^Diag11^Diag11POA^Diag12^Diag12POA^Diag13'
  + '^Diag13POA^Diag14^Diag14POA^Diag15^Diag15POA^Diag16^Diag16POA^Diag17^Diag17POA^Diag18^Diag18POA^Diag19^Diag19POA^Diag1POA^Diag2^Diag20^Diag20POA^Diag21'
  + '^Diag21POA^Diag22^Diag22POA^Diag23^Diag23POA^DIag24^Diag24POA^Diag25^Diag25POA^Diag2POA^Diag3^Diag3POA^Diag4^Diag4POA^Diag5^Diag5POA^Diag6^Diag6POA^Diag7'
  + '^Diag7POA^Diag8^Diag8POA^Diag9^Diag9POA^DischargeReasonID^DischargeStatus^DOB^DocumentsAttachedInd^DSS_Inpatient_PSD_BatchNumber^DSS_Inpatient_PSD_UB92ID'
  + '^AdjustmentGroupIEN^AdjustmentReasonIEN^DischargeType^DRGAdmissionCode^DRGAmountToPay^DRGDischargeCode^LastActionID^PickedAmountAfterReprice^PricerExempt'
  + '^StatusChangeDate^VendorIEN^VendorMedicareID^PaymentIEN^DSS_PSD_Vista_StationIFN^DSS_RB_BatchNumber^DSS_RB_DateCreated^DSS_RepricerReviewedClaimID'
  + '^DSS_RepricerReviewedClaimType^DTCalculated^DTReopened^DTReturnedFromPayment^DTSentToPayment^DTStampClaim^ECI^EdiBillingProv^EDIFacilityToRouteTo'
  + '^EstAmountDueA^EstAmountDueB^EstAmountDueC^Facility^FederalTaxID^FPPS_ID^FixBillableInsurance^FormType^GroupNameA (Box61a)^GroupNameB (Box61b)'
  + '^GroupNameC (Box61c)^HACEDI_ID^HACEDI_UpdateSentInd^HealthNetRepricedAmount^HealthPlanIDA^HealthPlanIDB^HealthPlanIDC^HERO_CLAIM_FLAG^ImageID^Imported'
  + '^ImportedBy^ImportedDTStamp^ImportedNote^InPatient^InsuranceGroupNoA^InsuranceGroupNoB^InsuranceGroupNoC^InsuredUniqueIDA^InsuredUniqueIDB^InsuredUniqueIDC'
  + '^Box60A^Box60B^Box60C^InsuredsNameA^InsuredsNameB^InsuredsNameC^JobNumber^LastCalcdJobNumber^ModifierSearch^OccCode_Box31a^OccCode_Box31b^OccCode_Box32a'
  + '^OccCode_Box32b^OccCode_Box33a^OccCode_Box33b^OccCode_Box34a^OccCode_Box34b^OccDate_Box31a^OccDate_Box31b^OccDate_Box32a^OccDate_Box32b^OccDate_Box33a'
  + '^OccDate_Box33b^OccDate_Box34a^OccDate_Box34b^OccSpanCode_Box35a^OccSpanCode_Box35b^OccSpanCode_Box36a^OccSpanCode_Box36b^OccSpanFrom_Box35a'
  + '^OccSpanFrom_Box35b^OccSpanFrom_Box36a^OccSpanFrom_Box36b^OccSpanThrough_Box35a^OccSpanThrough_Box35b^OccSpanThrough_Box36a^OccSpanThrough_Box36b'
  + '^OCRCompleted^On30DayHold^OperatingPhysicianName^OperationPhysicianNPI^OtherProcedureCodeA (Box74a)^OtherProcedureCodeB (Box74b)^OtherProcedureCodeC (Box74c)'
  + '^OtherProcedureCodeD (Box74d)^OtherProcedureCodeE (Box74e)^OtherProcedureDateA (Box74a)^OtherProcedureDateB (Box74b)^OtherProcedureDateC (Box74c)'
  + '^OtherProcedureDateD (Box74d)^OtherProcedureDateE (Box74e)^OtherPhysicianNameA^OtherPhysicianNameB^OtherPhysicianNPIA^OtherPhysicianNPIB^PatientSSN'
  + '^PatientControlNumber^PatientName^PatientsRelationShipA^PatientsRelationShipB^PatientsRelationShipC^PatientTypeID^Pay^PAY_TO_PROVIDER_TAXONOMY^PayerNameA'
  + '^PayerNameB^PayerNameC^PaymentFacilityID^PaymentStatus^PhysicianName^POS^PPSCode^PreImportedBy^PreImportedDTStamp^PreviousImportedBy^PreviousPreImportedBy'
  + '^PrimaryEligibility^PrinProcCode^PrinProcDate^PriorPaymentsA^PriorPaymentsB^PriorPaymentsC^ProviderNPI^ProviderID^UBL_Reason^ReasonID^RecordType'
  + '^REFERRING_PROVIDER_TAXONOMY^RejectedNote^RelInfoA^RelInfoB^RelInfoC^ReopenedReason^ReopenedUser^Repriced^RepricedAmount^RepricedStatus^RepricersReferenceID'
  + '^SC_Conditions^Scrubbed^SentToCalculateInd^SentToPaymentBy^Sex^SourceSystemIdentifier^Box6From^Box6To^StationID^DSS_RB_StationIFN^Status^SuspendedNote'
  + '^SuspensionReasonID^TaxIDClean^TaxIDOverride^TotalCharges^TypeOfBill^UB92UniqueClaimID^EditAction^EditCode^EditData^EditMSG^EditSubCode^UBE_ID^LineItem'
  + '^MsgFrom^UBE_UB92ID^UBL_UB92ID^UBL_ID^UBL_Box42^UBL_Box44^Box44Mods^Box45^Box46^Box47^Box48^Paid^Position^UBL_vafcp^UBL_vain^UBL_vaon^UBL_VAFB^VAFCP^VAIN'
  + '^ValueCode_Box39a^ValueCode_Box39b^ValueCode_Box39c^ValueCode_Box39d^ValueCode_Box40a^ValueCode_Box40b^ValueCode_Box40c^ValueCode_Box40d^ValueCode_Box41a'
  + '^ValueCode_Box41b^ValueCode_Box41c^ValueCode_Box41d^ValueCodeAmt_Box39a^ValueCodeAmt_Box39b^ValueCodeAmt_Box39c^ValueCodeAmt_Box39d^ValueCodeAmt_Box40a'
  + '^ValueCodeAmt_Box40b^ValueCodeAmt_Box40c^ValueCodeAmt_Box40d^ValueCodeAmt_Box41a^ValueCodeAmt_Box41b^ValueCodeAmt_Box41c^ValueCodeAmt_Box41d^VAON'
  + '^VARepricedAmount^Verified^VerifiedBy^VerifiedDTStamp^VistaAuthKey^VistaID^VistaPatKey^VoucherDate^VAFB^SCORE^CLM_DIAG_ICD^DRG_Number^Approve_Dates_From'
  + '^Approved_Dates_To^Disapproved_Dates_From^Disapproved_Dates_To^Number_Of_Approved_Days^Average_Length_of_Stay^DRG_Price^CalculatedPricePerDay^PPSPercentage'
  + '^DisapprovedReasons^Disapproved_Days^DRG_Per_Diem_Rate^DRG_Weight^AdmissionScenarioReason^DischargeScenarioReason^Minutes^Mileage^ConsultIEN'
  + '^CatCareDescription^AuthStationIFN^CatCareID^Estimated_Cost^Number_of_Visits^FCP^ReferringServiceID^CatCareIEN^ReportableStationNum^IPAC_Indicator'
  + '^SpecialProvCat^CLNoteTXT^LLNoteTXT^PreAuth^Discharge_Hour^Admission_Time^Admission_Source^Unit_Code^Pat_Reason_Code^ReopenClaimID^Adjusted^AdjustedAmount');

		print ' Reloaded UB header into export table.';
	end;




	print 'ending [dbo].[PIT_DAILY_REPORT_GENERATE]';
	print ' professional claim lines = ' +
		str(@total_prof_claims) + ', institutional claim lines = ' + str(@total_inst_claims);

	 return;


END
