

USE [ccrsdb]
GO

/****** Object:  Table [pit].[PIT_EXPORT_CARET]    Script Date: 5/24/2018 9:30:25 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [pit].[PIT_EXPORT_CARET](
	[pit_export_caret_key] [int] IDENTITY(1,1) primary key,
	[claim_type] [varchar](20) NULL,
	[pit_batch_send_log_key] [int] NULL,
	[generate_timestamp] [datetime2](0) NULL,
	[pit_record] [varchar](8000) NULL,
	[status] [varchar](20) NULL,
	[update_timestamp] [datetime2](7) NULL,
	[update_user] [varchar](50) NULL);
go

;
-- We need to seed one line into this new table, required for information export

insert into [pit].[PIT_EXPORT_CARET]
	([claim_type], [pit_batch_send_log_key], [generate_timestamp], [status]) VALUES
	('HCFA', -1, '2018-01-01', 'HEADER');


update [pit].[PIT_EXPORT_CARET] set [pit_record] =
'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'
where [claim_type] = 'HCFA' and [pit_batch_send_log_key] = -1 and [status] = 'HEADER';







