USE [ccrsdb]
GO

/****** Object:  StoredProcedure [dbo].[PIT_DAILY_REPORT_GENERATE]    Script Date: 6/18/2018 1:34:59 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- exec [PIT_DAILY_REPORT_GENERATE]
CREATE  PROCEDURE    [dbo].[PIT_DAILY_REPORT_GENERATE]  
AS
BEGIN
	-- Procedure to generate daily PIT report.
	SET NOCOUNT ON;

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

	declare @int1 int;
	declare @str1 varchar(250);

	declare @pit_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_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), @dvc_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 @fpmcd_claim_detail_key int, @fpmcd_procedure_key int, @fpmcd_pos_key int,
		@fpmcd_charge_amount money, @fpmcd_service_units bigint, @fpmcd_start_date date, 
		@fpmcd_etl_batch_id varchar(50), @fpmcd_line_note varchar(500), -- @fpmcd_date_paid date,
		@fpmcd_service_date_to date, @fpmcd_service_date_from date, @fpmcd_line_item_control_number varchar(50),
		@fpmcd_unit_label varchar(50);

	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 @cprov_billing_provider_npi varchar(50), @cprov_billing_provider_taxonomy int, @cprov_billing_provider_tax_id varchar(50);
	declare @cprov_billing_address varchar(250);
	declare @cprov_referring_provider_first_name varchar(50), @cprov_referring_provider_middle_name varchar(50);
	declare @cprov_referring_provider_last_name varchar(50);
	declare @cprov_referring_provider_full_name varchar(250);
	declare @cprov_render_provider_first_name varchar(50), @cprov_render_provider_npi varchar(50), @cprov_render_provider_taxonomy_key int;
	declare @cprov_billing_provider_first_name varchar(50);
	declare @cprov_servfac_provider_first_name varchar(50), @cprov_servfac_provider_npi varchar(50);

	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 @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_IPPS_Check_Amount money, @iir_Check_Date date;

	declare @diag_codes table (pointer_order int, diag_code varchar(50)); -- varchar(2000);
	
	declare @zline varchar(8000);

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

	declare c01 cursor for 
		select 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
		from DIM_VA_CLAIM dvc
		; -- where claim_key = 169;

	open c01;

	fetch next from c01 into @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;
	while @@FETCH_STATUS = 0
	begin
		-- Add SEND_LOG record if needed
		if (@pit_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, 'LoadUser');

			select @pit_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);

		-- NOTE: We need to set variables to NULL because they may be UNCHANGED if no record matches select statement
		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;
		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
		  from DIM_PATIENT where patient_key = @dvc_patient_key; -- was CLAIM_PATIENT

		set @cprov_billing_provider_npi = NULL;
		set @cprov_billing_provider_taxonomy = NULL;
		set @cprov_billing_provider_tax_id = NULL;
		set @cprov_billing_provider_first_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_first_name = provider_first_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 provider_type = 'Billing';

		set @cprov_render_provider_first_name = NULL;
		set @cprov_render_provider_npi = NULL;
		set @cprov_render_provider_taxonomy_key = NULL;
		select @cprov_render_provider_first_name = provider_first_name, @cprov_render_provider_npi = npi,
		    @cprov_render_provider_taxonomy_key = taxonomy_key
		  from CLAIM_PROVIDER where claim_key = @dvc_claim_key and 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_first_name = NULL;
		set @cprov_servfac_provider_npi = NULL;
		select @cprov_servfac_provider_first_name = provider_first_name, @cprov_servfac_provider_npi = npi
		  from CLAIM_PROVIDER where claim_key = @dvc_claim_key and provider_type = 'Service Facility';

		-- create provider name
		set @cprov_referring_provider_first_name = NULL;
		set @cprov_referring_provider_middle_name = NULL;
		set @cprov_referring_provider_last_name = NULL;
		select @cprov_referring_provider_first_name = provider_first_name, @cprov_referring_provider_middle_name = provider_middle_name,
		  @cprov_referring_provider_last_name = provider_last_name
		  from CLAIM_PROVIDER where claim_key = @dvc_claim_key and provider_type = 'Referring';

		set @cprov_referring_provider_full_name = isnull(@cprov_referring_provider_first_name + ' ', '') + isnull(@cprov_referring_provider_middle_name + ' ', '')
			+ isnull(@cprov_referring_provider_last_name, '');

		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;

		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 = @dvc_station_key;

		declare cl01 cursor for
		select fpmcd.claim_detail_key, fpmcd.procedure_key, fpmcd.pos_key, fpmcd.charge_amount, fpmcd.start_date, 
		 fpmcd.etl_batch_id, fpmcd.line_note, fpmcd.service_date_from,
		 fpmcd.service_date_to, 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 @fpmcd_claim_detail_key, @fpmcd_procedure_key, @fpmcd_pos_key, @fpmcd_charge_amount,
		 @fpmcd_start_date, @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;
		
		while @@FETCH_STATUS = 0
		begin

			-- print  @fpmcd_claim_detail_key;

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

			set @decision_decision = NULL;
			select @decision_decision = decision
			  from decision
			  where claim_key = @dvc_claim_key and claim_detail_key = @fpmcd_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 = @fpmcd_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 = @fpmcd_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 = @fpmcd_pos_key;
			end;

			-- IPPS fetch....this may be another transmit flag

			select @iir_Invoice_Key = iir.Invoice_Key, @iir_Invoice_Number = iir.Invoice_Number, @iir_IPPS_Check_Number = iir.IPPS_Check_Number
			  , @iir_IPPS_Check_Amount = iir.IPPS_Check_Amount, @iir_Check_Date = iir.Check_Date
			 -- select iir.Invoice_Key, iir.Invoice_Number, iir.IPPS_Check_Number
			 -- , iir.IPPS_Check_Amount, iir.Check_Date
			  from ipps.IPPS_INV_RC iir
			  inner join ipps.IPPS_INV_RC_DETAILS iirc on iirc.Invoice_Key = iir.Invoice_Key
			  -- inner join DIM_VA_CLAIM dvc on dvc.claim_key = iirc.claim_key
			  -- inner join F_INSTITUTIONAL_MEDICAL_CLAIM_DETAILS fpmcd on fpmcd.claim_detail_key = iirc.Claim_detail_key
			  where iirc.Claim_type = 'PROFESSIONAL' and  iirc.claim_key = @dvc_claim_key and iirc.Claim_detail_key = @fpmcd_claim_detail_key;
			-- print ' invoice key @iir_Invoice_Key ' + isnull( str(@iir_Invoice_Key) + ' notNULL' , 'is NULL');

	-- exec [PIT_DAILY_REPORT_GENERATE]

	-- 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 += '^';    -- 4 - HCFAUniqueClaimID
    set @zline += '^';    -- 5 - AuthNum
    set @zline += '^';    -- 6 - AuthOK
    set @zline += '^';    -- 7 - BatchCloseDate
    set @zline += '^';    -- 8 - BatchID
    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(str(@cprov_billing_provider_taxonomy)), '');    -- 17 - BILLING_PROVIDER_TAXONOMY
    set @zline += '^';    -- 18 - Box11
    set @zline += '^';    -- 19 - Box11C
    set @zline += '^' + isnull(ltrim(@cprov_referring_provider_full_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(@clajud_paid_amount, '0.00')), '');    -- 26 - Box28
    set @zline += '^';    -- 27 - Box29
    set @zline += '^';    -- 28 - Box30
    set @zline += '^';    -- 29 - Box31
    set @zline += '^' + isnull(ltrim(@cprov_render_provider_first_name), '');    -- 30 - box31A
    set @zline += '^' + isnull(ltrim(@cprov_servfac_provider_first_name), '');    -- 31 - Box32
    set @zline += '^' + isnull(ltrim(@cprov_billing_provider_first_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 += '^';    -- 40 - ClaimEntryType   HACEDI???
    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 += '^';    -- 52 - DTStamp
    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
	-- select format(cast('07/04/2018' as datetime2(7)), 'yyyyMMdd')
    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(@fpmcd_charge_amount, '0.00')), '');    -- 91 - Box24F
    set @zline += '^' + isnull(ltrim(str(@fpmcd_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(@fpmcd_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
    set @zline += '^';    -- 108 - Pay WE MAY HAVE THIS???
    set @zline += '^';    -- 109 - PaymentStatus
    set @zline += '^';    -- 110 - Position
    set @zline += '^';    -- 111 - Reason_Line
    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 += '^';    -- 117 - HCFALines_vain
    set @zline += '^';    -- 118 - HCFALines_vaon
    set @zline += '^';    -- 119 - VistaAllowableAmount
    set @zline += '^';    -- 120 - VistaAllowableType
    set @zline += '^';    -- 121 - VistaID
    set @zline += '^';    -- 122 - VoucherDate
    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
    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 += '^';    -- 190 - VAIN
    set @zline += '^';    -- 191 - VAON
    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(@fpmcd_unit_label, '');    -- 230 - Unit_Code
    set @zline += '^';    -- 231 - ReopenClaimID
    set @zline += '^';    -- 232 - Adjusted
    set @zline += '^';    -- 233 - AdjustedAmount

	-- 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_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 @fpmcd_claim_detail_key, @fpmcd_procedure_key, @fpmcd_pos_key, @fpmcd_charge_amount,
			 @fpmcd_start_date, @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;
		end;

		close cl01;
		deallocate cl01;

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

		set @cpat_icn = NULL;
		set @cpat_gender_code = NULL;
		set @cpat_last_name = NULL;
		set @cpat_first_name = NULL;
		set @cpat_middle_name = NULL;
		set @cprov_billing_provider_npi = NULL;
		set @cprov_billing_provider_tax_id = NULL;
		set @cprov_billing_address = NULL
		set @cprov_referring_provider_first_name = NULL;
		set @cprov_referring_provider_middle_name = NULL;
		set @cprov_referring_provider_last_name = NULL;
		set @cprov_referring_provider_full_name =NULL;
		set @cprov_render_provider_first_name = NULL;
		set @cprov_render_provider_npi = NULL;
		set @cprov_billing_provider_first_name = NULL;
		set @cprov_servfac_provider_first_name = NULL;
		set @cprov_servfac_provider_npi = NULL;
		set @dimtaxon_render_taxonomy_code = NULL;
		set @ccrs_referral_referral_consult_id = NULL;
		set @dvs_station_id = NULL;
		set @place_of_service = NULL;
		set @clm_modifier_code = NULL;
		set @cld_pointer_order = NULL;
		set @dpc_procedure_code = NULL;
		set @decision_decision = NULL;

		set @clajud_paid_amount = NULL;
		set @clajud_paid_date = NULL;
		set @fpmcd_charge_amount = NULL;
		set @dvc_charge_amount = NULL;

		set @cpat_date_of_birth = NULL;
		-- set @fpmcd_date_paid = NULL;
		set @fpmcd_service_date_to = NULL;
		set @fpmcd_service_date_from = NULL;
		set @fpmcd_start_date = NULL;
		set @dvc_claim_receipt_date = NULL;
		set @dvc_claim_created_date = NULL;

		set @dvc_claim_key = NULL;
		set @fpmcd_claim_detail_key = NULL;
		set @fpmcd_procedure_key = NULL;
		set @fpmcd_pos_key = NULL;
		set @fpmcd_service_units = NULL;
		-- set @fpmcd_unit_label = NULL;
		set @fprof_claim_detail_key = NULL;
		set @cprov_render_provider_taxonomy_key = NULL;
		set @cprov_billing_provider_taxonomy = NULL;
		set @dpc_pos_key = NULL;

		set @iir_Invoice_Key = NULL; 
		set @iir_Invoice_Number = NULL; 
		set @iir_IPPS_Check_Number = NULL;
		set @iir_IPPS_Check_Amount = NULL;
		set @iir_Check_Date = NULL;

		fetch next from c01 into @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 c01;
	deallocate c01;

	print 'HEY stopping at HCFA';
	return;

	-- UB04 formatted report:
	

    set @zline = 'AccidentState';    -- 1 - AccidentState
    set @zline += '^AdmissionDate';    -- 2 - AdmissionDate
    set @zline += '^AdmissionReasonID';    -- 3 - AdmissionReasonID
    set @zline += '^AdmittingDiagnosisCode';    -- 4 - AdmittingDiagnosisCode
    set @zline += '^UBL_AllowableType';    -- 5 - UBL_AllowableType
    set @zline += '^UBL_OtherAllowableAmount';    -- 6 - UBL_OtherAllowableAmount
    set @zline += '^UBL_OtherAllowableType';    -- 7 - UBL_OtherAllowableType
    set @zline += '^VistaAllowableType';    -- 8 - VistaAllowableType
    set @zline += '^VistaAllowableAmount';    -- 9 - VistaAllowableAmount
    set @zline += '^CMSAllowableType';    -- 10 - CMSAllowableType
    set @zline += '^CMSAllowableAmount';    -- 11 - CMSAllowableAmount
    set @zline += '^AssignmentBenefitsA';    -- 12 - AssignmentBenefitsA
    set @zline += '^AssignmentBenefitsB';    -- 13 - AssignmentBenefitsB
    set @zline += '^AssignmentBenefitsC';    -- 14 - AssignmentBenefitsC
    set @zline += '^AttendingPhysicianNPI';    -- 15 - AttendingPhysicianNPI
    set @zline += '^AttendingPhysicianTaxonomy';    -- 16 - AttendingPhysicianTaxonomy
    set @zline += '^AuthorizationNumber';    -- 17 - AuthorizationNumber
    set @zline += '^AuthOK';    -- 18 - AuthOK
    set @zline += '^Authorized';    -- 19 - Authorized
    set @zline += '^BatchID';    -- 20 - BatchID
    set @zline += '^BillableInsurance';    -- 21 - BillableInsurance
    set @zline += '^BillingProviderTaxonomy';    -- 22 - BillingProviderTaxonomy
    set @zline += '^bwclaim';    -- 23 - bwclaim
    set @zline += '^CheckNumber';    -- 24 - CheckNumber
    set @zline += '^ClaimEntryType';    -- 25 - ClaimEntryType
    set @zline += '^ClaimMemo';    -- 26 - ClaimMemo
    set @zline += '^codesearch';    -- 27 - codesearch
    set @zline += '^ConditionCode1';    -- 28 - ConditionCode1
    set @zline += '^ConditionCode10';    -- 29 - ConditionCode10
    set @zline += '^ConditionCode11';    -- 30 - ConditionCode11
    set @zline += '^ConditionCode2';    -- 31 - ConditionCode2
    set @zline += '^ConditionCode3';    -- 32 - ConditionCode3
    set @zline += '^ConditionCode4';    -- 33 - ConditionCode4
    set @zline += '^ConditionCode5';    -- 34 - ConditionCode5
    set @zline += '^ConditionCode6';    -- 35 - ConditionCode6
    set @zline += '^ConditionCode7';    -- 36 - ConditionCode7
    set @zline += '^ConditionCode8';    -- 37 - ConditionCode8
    set @zline += '^ConditionCode9';    -- 38 - ConditionCode9
    set @zline += '^ContractClaim';    -- 39 - ContractClaim
    set @zline += '^CreationDate';    -- 40 - CreationDate
    set @zline += '^DatabaseID';    -- 41 - DatabaseID
    set @zline += '^Diag1';    -- 42 - Diag1
    set @zline += '^Diag10';    -- 43 - Diag10
    set @zline += '^Diag10POA';    -- 44 - Diag10POA
    set @zline += '^Diag11';    -- 45 - Diag11
    set @zline += '^Diag11POA';    -- 46 - Diag11POA
    set @zline += '^Diag12';    -- 47 - Diag12
    set @zline += '^Diag12POA';    -- 48 - Diag12POA
    set @zline += '^Diag13';    -- 49 - Diag13
    set @zline += '^Diag13POA';    -- 50 - Diag13POA
    set @zline += '^Diag14';    -- 51 - Diag14
    set @zline += '^Diag14POA';    -- 52 - Diag14POA
    set @zline += '^Diag15';    -- 53 - Diag15
    set @zline += '^Diag15POA';    -- 54 - Diag15POA
    set @zline += '^Diag16';    -- 55 - Diag16
    set @zline += '^Diag16POA';    -- 56 - Diag16POA
    set @zline += '^Diag17';    -- 57 - Diag17
    set @zline += '^Diag17POA';    -- 58 - Diag17POA
    set @zline += '^Diag18';    -- 59 - Diag18
    set @zline += '^Diag18POA';    -- 60 - Diag18POA
    set @zline += '^Diag19';    -- 61 - Diag19
    set @zline += '^Diag19POA';    -- 62 - Diag19POA
    set @zline += '^Diag1POA';    -- 63 - Diag1POA
    set @zline += '^Diag2';    -- 64 - Diag2
    set @zline += '^Diag20';    -- 65 - Diag20
    set @zline += '^Diag20POA';    -- 66 - Diag20POA
    set @zline += '^Diag21';    -- 67 - Diag21
    set @zline += '^Diag21POA';    -- 68 - Diag21POA
    set @zline += '^Diag22';    -- 69 - Diag22
    set @zline += '^Diag22POA';    -- 70 - Diag22POA
    set @zline += '^Diag23';    -- 71 - Diag23
    set @zline += '^Diag23POA';    -- 72 - Diag23POA
    set @zline += '^DIag24';    -- 73 - DIag24
    set @zline += '^Diag24POA';    -- 74 - Diag24POA
    set @zline += '^Diag25';    -- 75 - Diag25
    set @zline += '^Diag25POA';    -- 76 - Diag25POA
    set @zline += '^Diag2POA';    -- 77 - Diag2POA
    set @zline += '^Diag3';    -- 78 - Diag3
    set @zline += '^Diag3POA';    -- 79 - Diag3POA
    set @zline += '^Diag4';    -- 80 - Diag4
    set @zline += '^Diag4POA';    -- 81 - Diag4POA
    set @zline += '^Diag5';    -- 82 - Diag5
    set @zline += '^Diag5POA';    -- 83 - Diag5POA
    set @zline += '^Diag6';    -- 84 - Diag6
    set @zline += '^Diag6POA';    -- 85 - Diag6POA
    set @zline += '^Diag7';    -- 86 - Diag7
    set @zline += '^Diag7POA';    -- 87 - Diag7POA
    set @zline += '^Diag8';    -- 88 - Diag8
    set @zline += '^Diag8POA';    -- 89 - Diag8POA
    set @zline += '^Diag9';    -- 90 - Diag9
    set @zline += '^Diag9POA';    -- 91 - Diag9POA
    set @zline += '^DischargeReasonID';    -- 92 - DischargeReasonID
    set @zline += '^DischargeStatus';    -- 93 - DischargeStatus
    set @zline += '^DOB';    -- 94 - DOB
    set @zline += '^DocumentsAttachedInd';    -- 95 - DocumentsAttachedInd
    set @zline += '^DSS_Inpatient_PSD_BatchNumber';    -- 96 - DSS_Inpatient_PSD_BatchNumber
    set @zline += '^DSS_Inpatient_PSD_UB92ID';    -- 97 - DSS_Inpatient_PSD_UB92ID
    set @zline += '^AdjustmentGroupIEN';    -- 98 - AdjustmentGroupIEN
    set @zline += '^AdjustmentReasonIEN';    -- 99 - AdjustmentReasonIEN
    set @zline += '^DischargeType';    -- 100 - DischargeType
    set @zline += '^DRGAdmissionCode';    -- 101 - DRGAdmissionCode
    set @zline += '^DRGAmountToPay';    -- 102 - DRGAmountToPay
    set @zline += '^DRGDischargeCode';    -- 103 - DRGDischargeCode
    set @zline += '^LastActionID';    -- 104 - LastActionID
    set @zline += '^PickedAmountAfterReprice';    -- 105 - PickedAmountAfterReprice
    set @zline += '^PricerExempt ';    -- 106 - PricerExempt 
    set @zline += '^StatusChangeDate';    -- 107 - StatusChangeDate
    set @zline += '^VendorIEN';    -- 108 - VendorIEN
    set @zline += '^VendorMedicareID';    -- 109 - VendorMedicareID
    set @zline += '^PaymentIEN ';    -- 110 - PaymentIEN 
    set @zline += '^DSS_PSD_Vista_StationIFN';    -- 111 - DSS_PSD_Vista_StationIFN
    set @zline += '^DSS_RB_BatchNumber';    -- 112 - DSS_RB_BatchNumber
    set @zline += '^DSS_RB_DateCreated';    -- 113 - DSS_RB_DateCreated
    set @zline += '^DSS_RepricerReviewedClaimID';    -- 114 - DSS_RepricerReviewedClaimID
    set @zline += '^DSS_RepricerReviewedClaimType';    -- 115 - DSS_RepricerReviewedClaimType
    set @zline += '^DTCalculated';    -- 116 - DTCalculated
    set @zline += '^DTReopened';    -- 117 - DTReopened
    set @zline += '^DTReturnedFromPayment';    -- 118 - DTReturnedFromPayment
    set @zline += '^DTSentToPayment';    -- 119 - DTSentToPayment
    set @zline += '^DTStampClaim';    -- 120 - DTStampClaim
    set @zline += '^ECI';    -- 121 - ECI
    set @zline += '^EdiBillingProv';    -- 122 - EdiBillingProv
    set @zline += '^EDIFacilityToRouteTo';    -- 123 - EDIFacilityToRouteTo
    set @zline += '^EstAmountDueA';    -- 124 - EstAmountDueA
    set @zline += '^EstAmountDueB';    -- 125 - EstAmountDueB
    set @zline += '^EstAmountDueC';    -- 126 - EstAmountDueC
    set @zline += '^Facility';    -- 127 - Facility
    set @zline += '^FederalTaxID';    -- 128 - FederalTaxID
    set @zline += '^FPPS_ID';    -- 129 - FPPS_ID
    set @zline += '^FixBillableInsurance';    -- 130 - FixBillableInsurance
    set @zline += '^FormType';    -- 131 - FormType
    set @zline += '^GroupNameA (Box61a)';    -- 132 - GroupNameA (Box61a)
    set @zline += '^GroupNameB (Box61b)';    -- 133 - GroupNameB (Box61b)
    set @zline += '^GroupNameC (Box61c)';    -- 134 - GroupNameC (Box61c)
    set @zline += '^HACEDI_ID';    -- 135 - HACEDI_ID
    set @zline += '^HACEDI_UpdateSentInd';    -- 136 - HACEDI_UpdateSentInd
    set @zline += '^HealthNetRepricedAmount';    -- 137 - HealthNetRepricedAmount
    set @zline += '^HealthPlanIDA';    -- 138 - HealthPlanIDA
    set @zline += '^HealthPlanIDB';    -- 139 - HealthPlanIDB
    set @zline += '^HealthPlanIDC';    -- 140 - HealthPlanIDC
    set @zline += '^HERO_CLAIM_FLAG';    -- 141 - HERO_CLAIM_FLAG
    set @zline += '^ImageID';    -- 142 - ImageID
    set @zline += '^Imported';    -- 143 - Imported
    set @zline += '^ImportedBy';    -- 144 - ImportedBy
    set @zline += '^ImportedDTStamp';    -- 145 - ImportedDTStamp
    set @zline += '^ImportedNote';    -- 146 - ImportedNote
    set @zline += '^InPatient';    -- 147 - InPatient
    set @zline += '^InsuranceGroupNoA';    -- 148 - InsuranceGroupNoA
    set @zline += '^InsuranceGroupNoB';    -- 149 - InsuranceGroupNoB
    set @zline += '^InsuranceGroupNoC';    -- 150 - InsuranceGroupNoC
    set @zline += '^InsuredUniqueIDA';    -- 151 - InsuredUniqueIDA
    set @zline += '^InsuredUniqueIDB';    -- 152 - InsuredUniqueIDB
    set @zline += '^InsuredUniqueIDC';    -- 153 - InsuredUniqueIDC
    set @zline += '^Box60A';    -- 154 - Box60A
    set @zline += '^Box60B';    -- 155 - Box60B
    set @zline += '^Box60C';    -- 156 - Box60C
    set @zline += '^InsuredsNameA';    -- 157 - InsuredsNameA
    set @zline += '^InsuredsNameB';    -- 158 - InsuredsNameB
    set @zline += '^InsuredsNameC';    -- 159 - InsuredsNameC
    set @zline += '^JobNumber';    -- 160 - JobNumber
    set @zline += '^LastCalcdJobNumber';    -- 161 - LastCalcdJobNumber
    set @zline += '^ModifierSearch';    -- 162 - ModifierSearch
    set @zline += '^OccCode_Box31a';    -- 163 - OccCode_Box31a
    set @zline += '^OccCode_Box31b';    -- 164 - OccCode_Box31b
    set @zline += '^OccCode_Box32a';    -- 165 - OccCode_Box32a
    set @zline += '^OccCode_Box32b';    -- 166 - OccCode_Box32b
    set @zline += '^OccCode_Box33a';    -- 167 - OccCode_Box33a
    set @zline += '^OccCode_Box33b';    -- 168 - OccCode_Box33b
    set @zline += '^OccCode_Box34a';    -- 169 - OccCode_Box34a
    set @zline += '^OccCode_Box34b';    -- 170 - OccCode_Box34b
    set @zline += '^OccDate_Box31a';    -- 171 - OccDate_Box31a
    set @zline += '^OccDate_Box31b';    -- 172 - OccDate_Box31b
    set @zline += '^OccDate_Box32a';    -- 173 - OccDate_Box32a
    set @zline += '^OccDate_Box32b';    -- 174 - OccDate_Box32b
    set @zline += '^OccDate_Box33a';    -- 175 - OccDate_Box33a
    set @zline += '^OccDate_Box33b';    -- 176 - OccDate_Box33b
    set @zline += '^OccDate_Box34a';    -- 177 - OccDate_Box34a
    set @zline += '^OccDate_Box34b';    -- 178 - OccDate_Box34b
    set @zline += '^OccSpanCode_Box35a';    -- 179 - OccSpanCode_Box35a
    set @zline += '^OccSpanCode_Box35b';    -- 180 - OccSpanCode_Box35b
    set @zline += '^OccSpanCode_Box36a';    -- 181 - OccSpanCode_Box36a
    set @zline += '^OccSpanCode_Box36b';    -- 182 - OccSpanCode_Box36b
    set @zline += '^OccSpanFrom_Box35a';    -- 183 - OccSpanFrom_Box35a
    set @zline += '^OccSpanFrom_Box35b';    -- 184 - OccSpanFrom_Box35b
    set @zline += '^OccSpanFrom_Box36a';    -- 185 - OccSpanFrom_Box36a
    set @zline += '^OccSpanFrom_Box36b';    -- 186 - OccSpanFrom_Box36b
    set @zline += '^OccSpanThrough_Box35a';    -- 187 - OccSpanThrough_Box35a
    set @zline += '^OccSpanThrough_Box35b';    -- 188 - OccSpanThrough_Box35b
    set @zline += '^OccSpanThrough_Box36a';    -- 189 - OccSpanThrough_Box36a
    set @zline += '^OccSpanThrough_Box36b';    -- 190 - OccSpanThrough_Box36b
    set @zline += '^OCRCompleted';    -- 191 - OCRCompleted
    set @zline += '^On30DayHold';    -- 192 - On30DayHold
    set @zline += '^OperatingPhysicianName';    -- 193 - OperatingPhysicianName
    set @zline += '^OperationPhysicianNPI';    -- 194 - OperationPhysicianNPI
    set @zline += '^OtherProcedureCodeA (Box74a)';    -- 195 - OtherProcedureCodeA (Box74a)
    set @zline += '^OtherProcedureCodeB (Box74b)';    -- 196 - OtherProcedureCodeB (Box74b)
    set @zline += '^OtherProcedureCodeC (Box74c)';    -- 197 - OtherProcedureCodeC (Box74c)
    set @zline += '^OtherProcedureCodeD (Box74d)';    -- 198 - OtherProcedureCodeD (Box74d)
    set @zline += '^OtherProcedureCodeE (Box74e)';    -- 199 - OtherProcedureCodeE (Box74e)
    set @zline += '^OtherProcedureDateA (Box74a)';    -- 200 - OtherProcedureDateA (Box74a)
    set @zline += '^OtherProcedureDateB (Box74b)';    -- 201 - OtherProcedureDateB (Box74b)
    set @zline += '^OtherProcedureDateC (Box74c)';    -- 202 - OtherProcedureDateC (Box74c)
    set @zline += '^OtherProcedureDateD (Box74d)';    -- 203 - OtherProcedureDateD (Box74d)
    set @zline += '^OtherProcedureDateE (Box74e)';    -- 204 - OtherProcedureDateE (Box74e)
    set @zline += '^OtherPhysicianNameA';    -- 205 - OtherPhysicianNameA
    set @zline += '^OtherPhysicianNameB';    -- 206 - OtherPhysicianNameB
    set @zline += '^OtherPhysicianNPIA';    -- 207 - OtherPhysicianNPIA
    set @zline += '^OtherPhysicianNPIB';    -- 208 - OtherPhysicianNPIB
    set @zline += '^PatientSSN';    -- 209 - PatientSSN
    set @zline += '^PatientControlNumber';    -- 210 - PatientControlNumber
    set @zline += '^PatientName';    -- 211 - PatientName
    set @zline += '^PatientsRelationShipA';    -- 212 - PatientsRelationShipA
    set @zline += '^PatientsRelationShipB';    -- 213 - PatientsRelationShipB
    set @zline += '^PatientsRelationShipC';    -- 214 - PatientsRelationShipC
    set @zline += '^PatientTypeID';    -- 215 - PatientTypeID
    set @zline += '^Pay';    -- 216 - Pay
    set @zline += '^PAY_TO_PROVIDER_TAXONOMY';    -- 217 - PAY_TO_PROVIDER_TAXONOMY
    set @zline += '^PayerNameA';    -- 218 - PayerNameA
    set @zline += '^PayerNameB';    -- 219 - PayerNameB
    set @zline += '^PayerNameC';    -- 220 - PayerNameC
    set @zline += '^PaymentFacilityID';    -- 221 - PaymentFacilityID
    set @zline += '^PaymentStatus';    -- 222 - PaymentStatus
    set @zline += '^PhysicianName';    -- 223 - PhysicianName
    set @zline += '^POS';    -- 224 - POS
    set @zline += '^PPSCode';    -- 225 - PPSCode
    set @zline += '^PreImportedBy';    -- 226 - PreImportedBy
    set @zline += '^PreImportedDTStamp';    -- 227 - PreImportedDTStamp
    set @zline += '^PreviousImportedBy';    -- 228 - PreviousImportedBy
    set @zline += '^PreviousPreImportedBy';    -- 229 - PreviousPreImportedBy
    set @zline += '^PrimaryEligibility';    -- 230 - PrimaryEligibility
    set @zline += '^PrinProcCode';    -- 231 - PrinProcCode
    set @zline += '^PrinProcDate';    -- 232 - PrinProcDate
    set @zline += '^PriorPaymentsA';    -- 233 - PriorPaymentsA
    set @zline += '^PriorPaymentsB';    -- 234 - PriorPaymentsB
    set @zline += '^PriorPaymentsC';    -- 235 - PriorPaymentsC
    set @zline += '^ProviderNPI';    -- 236 - ProviderNPI
    set @zline += '^ProviderID';    -- 237 - ProviderID
    set @zline += '^UBL_Reason';    -- 238 - UBL_Reason
    set @zline += '^ReasonID';    -- 239 - ReasonID
    set @zline += '^RecordType';    -- 240 - RecordType
    set @zline += '^REFERRING_PROVIDER_TAXONOMY';    -- 241 - REFERRING_PROVIDER_TAXONOMY
    set @zline += '^RejectedNote';    -- 242 - RejectedNote
    set @zline += '^RelInfoA';    -- 243 - RelInfoA
    set @zline += '^RelInfoB';    -- 244 - RelInfoB
    set @zline += '^RelInfoC';    -- 245 - RelInfoC
    set @zline += '^ReopenedReason';    -- 246 - ReopenedReason
    set @zline += '^ReopenedUser';    -- 247 - ReopenedUser
    set @zline += '^Repriced';    -- 248 - Repriced
    set @zline += '^RepricedAmount';    -- 249 - RepricedAmount
    set @zline += '^RepricedStatus';    -- 250 - RepricedStatus
    set @zline += '^RepricersReferenceID';    -- 251 - RepricersReferenceID
    set @zline += '^SC_Conditions';    -- 252 - SC_Conditions
    set @zline += '^Scrubbed';    -- 253 - Scrubbed
    set @zline += '^SentToCalculateInd';    -- 254 - SentToCalculateInd
    set @zline += '^SentToPaymentBy';    -- 255 - SentToPaymentBy
    set @zline += '^Sex';    -- 256 - Sex
    set @zline += '^SourceSystemIdentifier';    -- 257 - SourceSystemIdentifier
    set @zline += '^Box6From';    -- 258 - Box6From
    set @zline += '^Box6To';    -- 259 - Box6To
    set @zline += '^StationID';    -- 260 - StationID
    set @zline += '^DSS_RB_StationIFN';    -- 261 - DSS_RB_StationIFN
    set @zline += '^Status';    -- 262 - Status
    set @zline += '^SuspendedNote';    -- 263 - SuspendedNote
    set @zline += '^SuspensionReasonID';    -- 264 - SuspensionReasonID
    set @zline += '^TaxIDClean';    -- 265 - TaxIDClean
    set @zline += '^TaxIDOverride';    -- 266 - TaxIDOverride
    set @zline += '^TotalCharges';    -- 267 - TotalCharges
    set @zline += '^TypeOfBill';    -- 268 - TypeOfBill
    set @zline += '^UB92UniqueClaimID';    -- 269 - UB92UniqueClaimID
    set @zline += '^EditAction';    -- 270 - EditAction
    set @zline += '^EditCode';    -- 271 - EditCode
    set @zline += '^EditData';    -- 272 - EditData
    set @zline += '^EditMSG';    -- 273 - EditMSG
    set @zline += '^EditSubCode';    -- 274 - EditSubCode
    set @zline += '^UBE_ID';    -- 275 - UBE_ID
    set @zline += '^LineItem';    -- 276 - LineItem
    set @zline += '^MsgFrom';    -- 277 - MsgFrom
    set @zline += '^UBE_UB92ID';    -- 278 - UBE_UB92ID
    set @zline += '^UBL_UB92ID';    -- 279 - UBL_UB92ID
    set @zline += '^UBL_ID';    -- 280 - UBL_ID
    set @zline += '^UBL_Box42';    -- 281 - UBL_Box42
    set @zline += '^UBL_Box44';    -- 282 - UBL_Box44
    set @zline += '^Box44Mods';    -- 283 - Box44Mods
    set @zline += '^Box45';    -- 284 - Box45
    set @zline += '^Box46';    -- 285 - Box46
    set @zline += '^Box47';    -- 286 - Box47
    set @zline += '^Box48';    -- 287 - Box48
    set @zline += '^Paid';    -- 288 - Paid
    set @zline += '^Position';    -- 289 - Position
    set @zline += '^UBL_vafcp';    -- 290 - UBL_vafcp
    set @zline += '^UBL_vain';    -- 291 - UBL_vain
    set @zline += '^UBL_vaon';    -- 292 - UBL_vaon
    set @zline += '^UBL_VAFB';    -- 293 - UBL_VAFB
    set @zline += '^VAFCP';    -- 294 - VAFCP
    set @zline += '^VAIN';    -- 295 - VAIN
    set @zline += '^ValueCode_Box39a';    -- 296 - ValueCode_Box39a
    set @zline += '^ValueCode_Box39b';    -- 297 - ValueCode_Box39b
    set @zline += '^ValueCode_Box39c';    -- 298 - ValueCode_Box39c
    set @zline += '^ValueCode_Box39d';    -- 299 - ValueCode_Box39d
    set @zline += '^ValueCode_Box40a';    -- 300 - ValueCode_Box40a
    set @zline += '^ValueCode_Box40b';    -- 301 - ValueCode_Box40b
    set @zline += '^ValueCode_Box40c';    -- 302 - ValueCode_Box40c
    set @zline += '^ValueCode_Box40d';    -- 303 - ValueCode_Box40d
    set @zline += '^ValueCode_Box41a';    -- 304 - ValueCode_Box41a
    set @zline += '^ValueCode_Box41b';    -- 305 - ValueCode_Box41b
    set @zline += '^ValueCode_Box41c';    -- 306 - ValueCode_Box41c
    set @zline += '^ValueCode_Box41d';    -- 307 - ValueCode_Box41d
    set @zline += '^ValueCodeAmt_Box39a';    -- 308 - ValueCodeAmt_Box39a
    set @zline += '^ValueCodeAmt_Box39b';    -- 309 - ValueCodeAmt_Box39b
    set @zline += '^ValueCodeAmt_Box39c';    -- 310 - ValueCodeAmt_Box39c
    set @zline += '^ValueCodeAmt_Box39d';    -- 311 - ValueCodeAmt_Box39d
    set @zline += '^ValueCodeAmt_Box40a';    -- 312 - ValueCodeAmt_Box40a
    set @zline += '^ValueCodeAmt_Box40b';    -- 313 - ValueCodeAmt_Box40b
    set @zline += '^ValueCodeAmt_Box40c';    -- 314 - ValueCodeAmt_Box40c
    set @zline += '^ValueCodeAmt_Box40d';    -- 315 - ValueCodeAmt_Box40d
    set @zline += '^ValueCodeAmt_Box41a';    -- 316 - ValueCodeAmt_Box41a
    set @zline += '^ValueCodeAmt_Box41b';    -- 317 - ValueCodeAmt_Box41b
    set @zline += '^ValueCodeAmt_Box41c';    -- 318 - ValueCodeAmt_Box41c
    set @zline += '^ValueCodeAmt_Box41d';    -- 319 - ValueCodeAmt_Box41d
    set @zline += '^VAON';    -- 320 - VAON
    set @zline += '^VARepricedAmount';    -- 321 - VARepricedAmount
    set @zline += '^Verified';    -- 322 - Verified
    set @zline += '^VerifiedBy';    -- 323 - VerifiedBy
    set @zline += '^VerifiedDTStamp';    -- 324 - VerifiedDTStamp
    set @zline += '^VistaAuthKey';    -- 325 - VistaAuthKey
    set @zline += '^VistaID';    -- 326 - VistaID
    set @zline += '^VistaPatKey';    -- 327 - VistaPatKey
    set @zline += '^VoucherDate';    -- 328 - VoucherDate
    set @zline += '^VAFB';    -- 329 - VAFB
    set @zline += '^SCORE';    -- 330 - SCORE
    set @zline += '^CLM_DIAG_ICD ';    -- 331 - CLM_DIAG_ICD 
    set @zline += '^DRG_Number';    -- 332 - DRG_Number
    set @zline += '^Approve_Dates_From';    -- 333 - Approve_Dates_From
    set @zline += '^Approved_Dates_To';    -- 334 - Approved_Dates_To
    set @zline += '^Disapproved_Dates_From';    -- 335 - Disapproved_Dates_From
    set @zline += '^Disapproved_Dates_To';    -- 336 - Disapproved_Dates_To
    set @zline += '^Number_Of_Approved_Days';    -- 337 - Number_Of_Approved_Days
    set @zline += '^Average_Length_of_Stay';    -- 338 - Average_Length_of_Stay
    set @zline += '^DRG_Price';    -- 339 - DRG_Price
    set @zline += '^CalculatedPricePerDay';    -- 340 - CalculatedPricePerDay
    set @zline += '^PPSPercentage';    -- 341 - PPSPercentage
    set @zline += '^DisapprovedReasons';    -- 342 - DisapprovedReasons
    set @zline += '^Disapproved_Days';    -- 343 - Disapproved_Days
    set @zline += '^DRG_Per_Diem_Rate';    -- 344 - DRG_Per_Diem_Rate
    set @zline += '^DRG_Weight';    -- 345 - DRG_Weight
    set @zline += '^AdmissionScenarioReason';    -- 346 - AdmissionScenarioReason
    set @zline += '^DischargeScenarioReason';    -- 347 - DischargeScenarioReason
    set @zline += '^Minutes';    -- 348 - Minutes
    set @zline += '^Mileage';    -- 349 - Mileage
    set @zline += '^ConsultIEN';    -- 350 - ConsultIEN
    set @zline += '^CatCareDescription';    -- 351 - CatCareDescription
    set @zline += '^AuthStationIFN';    -- 352 - AuthStationIFN
    set @zline += '^CatCareID';    -- 353 - CatCareID
    set @zline += '^Estimated_Cost';    -- 354 - Estimated_Cost
    set @zline += '^Number_of_Visits';    -- 355 - Number_of_Visits
    set @zline += '^FCP';    -- 356 - FCP
    set @zline += '^ReferringServiceID';    -- 357 - ReferringServiceID
    set @zline += '^CatCareIEN';    -- 358 - CatCareIEN
    set @zline += '^ReportableStationNum';    -- 359 - ReportableStationNum
    set @zline += '^IPAC_Indicator';    -- 360 - IPAC_Indicator
    set @zline += '^SpecialProvCat';    -- 361 - SpecialProvCat
    set @zline += '^CLNoteTXT';    -- 362 - CLNoteTXT
    set @zline += '^LLNoteTXT';    -- 363 - LLNoteTXT
    set @zline += '^PreAuth';    -- 364 - PreAuth
    set @zline += '^Discharge_Hour';    -- 365 - Discharge_Hour
    set @zline += '^Admission_Time';    -- 366 - Admission_Time
    set @zline += '^Admission_Source';    -- 367 - Admission_Source
    set @zline += '^Unit_Code';    -- 368 - Unit_Code
    set @zline += '^Pat_Reason_Code';    -- 369 - Pat_Reason_Code
    set @zline += '^ReopenClaimID';    -- 370 - ReopenClaimID
    set @zline += '^Adjusted';    -- 371 - Adjusted
    set @zline += '^AdjustedAmount';    -- 372 - AdjustedAmount

	 print 'length of test UB04 (372 fields) is ' + ltrim(str(len(@zline)));
	print @zline;

	 return;


END
GO

