Header

CREATE TABLE EDI_Claims(

ID number(19) NOT NULL,

/*File Level Data*/

Filename varchar2(100) NULL,

Version varchar2(12) NULL,

ImageFilePath varchar2(300) NULL,

ImageFilename varchar2(100) NULL,

TradingPartnerIDType char(2) NULL,

TradingPartnerID varchar2(15) NOT NULL,

TransactionDate date NULL,

ReceiveDate date NULL,

SubmitterName varchar2(60) NULL,

SubmitterID varchar2(30) NULL,

SubmitterContact varchar2(60) NULL,

SubmitterTel varchar2(20) NULL,

SubmitterTelExt varchar2(10) NULL,

SubmitterFax varchar2(20) NULL,

SubmitterEmail varchar2(80) NULL,

ReceiverName varchar2(60) NULL,

ReceiverID varchar2(30) NULL,

TransactionType char(2) NULL,

/*Billing Provider*/

FedTaxID varchar2(15) NULL,

BillProvIDType char(2) NULL,

BillProvID varchar2(80) NULL,

BillProvNPI varchar2(12) NULL,

BillProvLast varchar2(60) NULL,

BillProvFirst varchar2(35) NULL,

BillProvMiddle varchar2(25) NULL,

BillProvSuffix varchar2(10) NULL,

BillProvSpecialty varchar2(10) NULL,

BillProvAddress varchar2(55) NULL,

BillProvAddress2 varchar2(55) NULL,

BillProvCity varchar2(30) NULL,

BillProvState char(2) NULL,

BillProvZip varchar2(15) NULL,

BillProvCountry varchar2(3) NULL,

BillProvSubdivision varchar2(3) NULL,

BillProvContact varchar2(60) NULL,

BillProvTel varchar2(80) NULL,

BillProvTelExt varchar2(80) NULL,

BillProvFax varchar2(80) NULL,

BillProvEmail varchar2(80) NULL,

BillProvOtherIDQual1 char(2) NULL,

BillProvOtherID1 varchar2(30) NULL,

BillProvOtherIDQual2 char(2) NULL,

BillProvOtherID2 varchar2(30) NULL,

BillProvOtherIDQual3 char(2) NULL,

BillProvOtherID3 varchar2(30) NULL,

BillProvOtherIDQual4 char(2) NULL,

BillProvOtherID4 varchar2(30) NULL,

BillProvOtherIDQual5 char(2) NULL,

BillProvOtherID5 varchar2(30) NULL,

/*Pay-to Provider*/

PayToProvIDType char(2) NULL,

PaytoProvID varchar2(30) NULL,

PaytoProvTaxID varchar2(12) NULL,

PaytoProvNPI varchar2(12) NULL,

PaytoProvLast varchar2(60) NULL,

PaytoProvFirst varchar2(35) NULL,

PayToProvMiddle varchar2(25) NULL,

PayToProvSuffix varchar2(10) NULL,

PayToProvSpecialty varchar2(10) NULL,

PaytoProvAddress varchar2(55) NULL,

PayToProvAddress2 varchar2(55) NULL,

PaytoProvCity varchar2(30) NULL,

PaytoProvState char(2) NULL,

PaytoProvZip varchar2(15) NULL,

 /*Pay-to Payer*/

PayToPayerIDType char(2) NULL,

PaytoPayerID varchar2(80) NULL,

PaytoPayerTaxID varchar2(12) NULL,

PaytoPayerNPI varchar2(12) NULL,

PaytoPayerLast varchar2(60) NULL,

PaytoPayerFirst varchar2(35) NULL,

PayToPayerMiddle varchar2(25) NULL,

PayToPayerSuffix varchar2(10) NULL,

PaytoPayerAddress varchar2(55) NULL,

PayToPayerAddress2 varchar2(55) NULL,

PaytoPayerCity varchar2(30) NULL,

PaytoPayerState char(2) NULL,

PaytoPayerZip varchar2(15) NULL,

/*Rendering Provider*/

RendProvIDType char(2) NULL,

RendProvID varchar2(30) NULL,

RendProvNPI varchar2(12) NULL,

RendProvTaxID varchar2(12) NULL,

RendProvLast varchar2(60) NULL,

RendProvFirst varchar2(35) NULL,

RendProvMiddle varchar2(25) NULL,

RendProvSuffix varchar2(10) NULL,

RendProvSpecialty varchar2(10) NULL,

RendProvOtherIDQual1 char(2) NULL,

RendProvOtherID1 varchar2(30) NULL,

RendProvOtherIDQual2 char(2) NULL,

RendProvOtherID2 varchar2(30) NULL,

RendProvOtherIDQual3 char(2) NULL,

RendProvOtherID3 varchar2(30) NULL,

/*Attending Provider*/

AttendProvLast varchar2(60) NULL,

AttendProvFirst varchar2(35) NULL,

AttendProvMiddle varchar2(25) NULL,

AttendProvSuffix varchar2(10) NULL,

AttendProvIDType char(2) NULL,

AttendProvID varchar2(30) NULL,

AttendProvTaxID varchar2(12) NULL,

AttendProvNPI varchar2(12) NULL,

AttendProvOtherIDQual1 char(2) NULL,

AttendProvOtherID1 varchar2(30) NULL,

AttendProvSpecialty varchar2(10) NULL,

/*Operating Provider*/

OperatingProvLast varchar2(60) NULL,

OperatingProvFirst varchar2(35) NULL,

OperatingProvMiddle varchar2(25) NULL,

OperatingProvSuffix varchar2(10) NULL,

OperatingProvIDType char(2) NULL,

OperatingProvID varchar2(30) NULL,

OperatingProvNPI varchar2(12) NULL,

OperatingProvTaxID varchar2(12) NULL,

OperatingProvOtherIDQual1 char(2) NULL,

OperatingProvOtherID1 varchar2(30) NULL,

OperatingProvSpecialty varchar2(10) NULL,

/*Other Provider*/

OtherProvLast varchar2(60) NULL,

OtherProvFirst varchar2(35) NULL,

OtherProvMiddle varchar2(25) NULL,

OtherProvSuffix varchar2(10) NULL,

OtherProvIDType char(2) NULL,

OtherProvID varchar2(30) NULL,

OtherProvTaxID varchar2(12) NULL,

OtherProvNPI varchar2(12) NULL,

OtherProvOtherIDQual1 char(2) NULL,

OtherProvOtherID1 varchar2(30) NULL,

OtherProvSpecialty varchar2(50) NULL,

/*Facility*/

FacilityType char(2) NULL,

FacilityIDType char(2) NULL,

FacilityID varchar2(80) NULL,

FacilityNPI varchar2(12) NULL,

FacilityTaxID varchar2(12) NULL,

FacilityOtherIDQual1 char(2) NULL,

FacilityOtherID1 varchar2(30) NULL,

FacilityName varchar2(80) NULL,

FacilityAddress varchar2(55) NULL,

FacilityAddress2 varchar2(55) NULL,

FacilityCity varchar2(30) NULL,

FacilitySpecialty varchar2(50) NULL,

FacilityState char(2) NULL,

FacilityZip varchar2(15) NULL,

/*Referring Provider*/

RefProvLast varchar2(60) NULL,

RefProvFirst varchar2(35) NULL,

RefProvMiddle varchar2(25) NULL,

RefProvSuffix varchar2(10) NULL,

RefProvIDType char(2) NULL,

RefProvID varchar2(80) NULL,

RefProvTaxID varchar2(12) NULL,

RefProvNPI varchar2(12) NULL,

RefProvOtherIDQual1 char(2) NULL,

RefProvOtherID1 varchar2(300) NULL,

RefProvSpecialty varchar2(20) NULL,

/*Supervising Provider*/

SupervProvLast varchar2(60) NULL,

SupervProvFirst varchar2(35) NULL,

SupervProvMiddle varchar2(25) NULL,

SupervProvSuffix varchar2(10) NULL,

SupervProvIDType char(2) NULL,

SupervProvID varchar2(30) NULL,

SupervProvTaxID varchar2(12) NULL,

SupervProvNPI varchar2(12) NULL,

SupervProvOtherIDQual1 char(2) NULL,

SupervProvOtherID1 varchar2(50) NULL,

SupervProvSpecialty varchar2(20) NULL,

/*Assistant Surgeon*/

AssistSurgLast varchar2(60) NULL,

AssistSurgFirst varchar2(35) NULL,

AssistSurgMiddle varchar2(25) NULL,

AssistSurgSuffix varchar2(10) NULL,

AssistSurgIDType char(2) NULL,

AssistSurgID varchar2(30) NULL,

AssistSurgTaxID varchar2(12) NULL,

AssistSurgNPI varchar2(12) NULL,

AssistSurgOtherIDQual1 varchar2(3) NULL,

AssistSurgOtherID1 varchar2(50) NULL,

AssistSurgSpecialty varchar2(10) NULL,

/*Ambulance Pick-up Location*/

PickUpAddress varchar2(55) NULL,

PickUpAddress2 varchar2(55) NULL,

PickUpCity varchar2(30) NULL,

PickUpState char(2) NULL,

PickUpZip varchar2(15) NULL,

/*Ambulance Drop-off Location*/

DropOffName varchar2(80) NULL,

DropOffAddress varchar2(55) NULL,

DropOffAddress2 varchar2(55) NULL,

DropOffCity varchar2(30) NULL,

DropOffState char(2) NULL,

DropOffZip varchar2(15) NULL,

/*Subscriber*/

SubscriberLast varchar2(60) NULL,

SubscriberFirst varchar2(35) NULL,

SubscriberMiddle varchar2(25) NULL,

SubscriberSuffix varchar2(10) NULL,

SubscriberAddress varchar2(55) NULL,

SubscriberAddress2 varchar2(55) NULL,

SubscriberCity varchar2(30) NULL,

SubscriberState char(2) NULL,

SubscriberZip varchar2(15) NULL,

SubscriberCountry varchar2(3) NULL,

SubscriberSubdivision varchar2(3) NULL,

SubscriberIDType char(2) NULL,

SubscriberID varchar2(30) NULL,

SubscriberSSN varchar2(12) NULL,

SubscriberDOB varchar2(10) NULL,

SubscriberSex char(1) NULL,

SubscriberEthnicity varchar2(55) NULL,

/*Responsible*/

ResponsibleLast varchar2(60) NULL,

ResponsibleFirst varchar2(35) NULL,

ResponsibleMiddle varchar2(25) NULL,

ResponsibleSuffix varchar2(10) NULL,

ResponsibleAddress varchar2(55) NULL,

ResponsibleAddress2 varchar2(55) NULL,

ResponsibleCity varchar2(30) NULL,

ResponsibleState char(2) NULL,

ResponsibleZip varchar2(15) NULL,

ResponsibleIDType char(2) NULL,

ResponsibleID varchar2(80) NULL,

/*Destination Payer*/

PayerResponsibility char(1) NULL,

PayerName varchar2(60) NULL,

PayerIDType char(2) NULL,

PayerID varchar2(30) NULL,

PayerAddress varchar2(55) NULL,

PayerAddress2 varchar2(55) NULL,

PayerCity varchar2(30) NULL,

PayerState char(2) NULL,

PayerZip varchar2(15) NULL,

GroupNo varchar2(50) NULL,

GroupName varchar2(60) NULL,

InsuranceType char(2) NULL,

FilingIndicator char(2) NULL,

COBIndicator char(1) NULL,

/*Patient*/

PatientLast varchar2(60) NULL,

PatientFirst varchar2(35) NULL,

PatientMiddle varchar2(25) NULL,

PatientSuffix varchar2(10) NULL,

PatientAddress varchar2(55) NULL,

PatientAddress2 varchar2(55) NULL,

PatientCity varchar2(30) NULL,

PatientState char(2) NULL,

PatientZip varchar2(15) NULL,

PatientCountry varchar2(3) NULL,

PatientSubdivision varchar2(3) NULL,

PatientIDType char(2) NULL,

PatientID varchar2(30) NULL,

PatientSSN varchar2(12) NULL,

PatientDOB varchar2(50) NULL,

PatientSex char(1) NULL,

PatientEthnicity varchar2(35) NULL,

PatientRelationship char(2) NULL,

PatientPaid varchar2(10) NULL,

PregnancyIndicator char(1) NULL,

/*COB Payer 1*/

OtherInsuredLast varchar2(60) NULL,

OtherInsuredFirst varchar2(35) NULL,

OtherInsuredMiddle varchar2(25) NULL,

OtherInsuredSuffix varchar2(10) NULL,

OtherInsuredIDQual char(2) NULL,

OtherInsuredID varchar2(30) NULL,

OtherInsuredAddress varchar2(55) NULL,

OtherInsuredAddress2 varchar2(55) NULL,

OtherInsuredCity varchar2(30) NULL,

OtherInsuredState char(2) NULL,

OtherInsuredZip varchar2(15) NULL,

OtherInsuredCountry varchar2(3) NULL,

OtherInsuredSubdivision varchar2(3) NULL,

OtherInsuredPolicy varchar2(50) NULL,

OtherInsuredDOB varchar2(10) NULL,

OtherInsuredSex char(1) NULL,

OtherInsuredRelationship char(2) NULL,

OtherPayerResponsibility char(1) NULL,

OtherPayerName varchar2(35) NULL,

OtherPayerIDQual char(2) NULL,

OtherPayerID varchar2(30) NULL,

OtherPayerPaid varchar2(10) NULL,

OtherPayerGroupName varchar2(60) NULL,

OtherPayerGroupNo varchar2(50) NULL,

OtherPayerInsuranceType char(2) NULL,

OtherPayerFilingIndicator char(2) NULL,

OtherPayerAdjReasonGroup varchar2(20) NULL,

OtherPayerAdjReason varchar2(50) NULL,

OtherPayerAdjAmount varchar2(120) NULL,

OtherPayerPaidDate date NULL,

OtherPayerInfoRelease char(1) NULL,

OtherPayerBenefitAssign char(1) NULL,

OtherPayerDocControlNo varchar2(50) NULL,

OtherPayerPriorAuth varchar2(50) NULL,

/*COB Payer 2*/

OtherInsured2Last varchar2(60) NULL,

OtherInsured2First varchar2(35) NULL,

OtherInsured2Middle varchar2(25) NULL,

OtherInsured2Suffix varchar2(10) NULL,

OtherInsured2IDQual char(2) NULL,

OtherInsured2ID varchar2(30) NULL,

OtherInsured2Address varchar2(55) NULL,

OtherInsured2Address2 varchar2(55) NULL,

OtherInsured2City varchar2(30) NULL,

OtherInsured2State char(2) NULL,

OtherInsured2Zip varchar2(15) NULL,

OtherInsured2Country varchar2(3) NULL,

OtherInsured2Subdivision varchar2(3) NULL,

OtherInsured2Policy varchar2(50) NULL,

OtherInsured2DOB varchar2(10) NULL,

OtherInsured2Sex char(1) NULL,

OtherInsured2Relationship char(2) NULL,

OtherPayer2Responsibility char(1) NULL,

OtherPayer2Name varchar2(35) NULL,

OtherPayer2IDQual char(2) NULL,

OtherPayer2ID varchar2(80) NULL,

OtherPayer2Paid varchar2(10) NULL,

OtherPayer2GroupName varchar2(60) NULL,

OtherPayer2GroupNo varchar2(50) NULL,

OtherPayer2InsuranceType char(2) NULL,

OtherPayer2FilingIndicator char(2) NULL,

OtherPayer2AdjReasonGroup char(20) NULL,

OtherPayer2AdjReason varchar2(50) NULL,

OtherPayer2AdjAmount varchar2(120) NULL,

OtherPayer2PaidDate date NULL,

OtherPayer2InfoRelease char(1) NULL,

OtherPayer2BenefitAssign char(1) NULL,

OtherPayer2DocControlNo varchar2(50) NULL,

OtherPayer2PriorAuth varchar2(50) NULL,

/*Claim Data*/

ClaimNo varchar2(50) NOT NULL,

Amount NUMBER(19,4) NULL,

EstimatedAmountDue varchar2(10) NULL,

PlaceOfService char(2) NULL,

SubmitReason char(1) NULL,

ProviderSignature char(1) NULL,

ProviderAcceptsAssignment char(1) NULL,

BenefitAssignment char(1) NULL,

InfoReleaseCode char(1) NULL,

PatientSignatureCode char(1) NULL,

RelatedCauses char(2) NULL,

RelatedCauses2 char(2) NULL,

RelatedCausesState char(2) NULL,

SpecialProgramCode char(3) NULL,

ProviderParticipation char(1) NULL,

EOBIndicator char(1) NULL,

DelayReasonCode char(2) NULL,

ServiceDateFrom date NULL,

ServiceDateTo date NULL,

OnsetDate date NULL,

SimilarSymptomsDate date NULL,

DisabilityBegin date NULL,

DisabilityEnd date NULL,

HospitalizationBegin date NULL,

HospitalizationEnd date NULL,

AccidentDate date NULL,

LastMenstrualPeriod date NULL,

RepricerReceivedDate date NULL,

AdmissionDate date NULL,

AdmissionHour VARCHAR2(10) NULL,

AdmissionType char(1) NULL,

AdmissionSource char(1) NULL,

DischargeHour VARCHAR2(10) NULL,

PatientStatus char(2) NULL,

CoveredDays varchar2(4) NULL,

NonCoveredDays varchar2(4) NULL,

COBDays varchar2(4) NULL,

LifeTimeReserveDays varchar2(4) NULL,

PriorAuthorization varchar2(50) NULL,

ClearingHouseID varchar2(50) NULL,

MedicalRecordNumber varchar2(50) NULL,

ServiceAuthorizationException varchar2(50) NULL,

ReferralNumber varchar2(50) NULL,

PayerClaimControlNumber varchar2(50) NULL,

AdjustedRepricedClaimNumber varchar2(50) NULL,

AutoAccidentState varchar2(50) NULL,

OrigRefNo varchar2(50) NULL,

ClaimType char(1) NULL,

TypeOfBill varchar2(20) NULL,

Remark1 varchar2(90) NULL,

Remark2 varchar2(90) NULL,

Remark3 varchar2(90) NULL,

Remark4 varchar2(90) NULL,

K3_1 varchar2(80) NULL,

K3_2 varchar2(80) NULL,

K3_3 varchar2(80) NULL,

K3_4 varchar2(80) NULL,

K3_5 varchar2(80) NULL,

K3_6 varchar2(80) NULL,

K3_7 varchar2(80) NULL,

K3_8 varchar2(80) NULL,

K3_9 varchar2(80) NULL,

K3_10 varchar2(80) NULL,

OutsideLab char(1) NULL,

LabCharge varchar2(10) NULL,

Test_Prod char(1) NULL,

ReportTypeCode1 char(2) NULL,

ReportTransmissionCode1 varchar2(2) NULL,

AttachmentControlNumber1 varchar2(80) NULL,

/*Dental Claims*/

Predetermination char(2) NULL,

OrthodonticTotal varchar2(5) NULL,

OrthodonticRemaining varchar2(5) NULL,

OrthodonticYesNo char(1) NULL,

ToothStatus varchar2(100) NULL,

AppliancePlacementDate date NULL,

/*Diagnosis Information*/

AdmitDiagnosis varchar2(10) NULL,

ECode varchar2(30) NULL,

ECode2 varchar2(30) NULL,

ECode3 varchar2(30) NULL,

ECode4 varchar2(30) NULL,

ECode5 varchar2(30) NULL,

ECode6 varchar2(30) NULL,

ECode7 varchar2(30) NULL,

ECode8 varchar2(30) NULL,

--ECode9 varchar2(30) NULL,

--ECode10 varchar2(30) NULL,

--ECode11 varchar2(30) NULL,

--ECode12 varchar2(30) NULL,

ReasonForVisit varchar2(30) NULL,

ReasonForVisit2 varchar2(30) NULL,

ReasonForVisit3 varchar2(30) NULL,

PrincipalDiagnosis varchar2(10) NULL,

Diag2 varchar2(10) NULL,

Diag3 varchar2(10) NULL,

Diag4 varchar2(10) NULL,

Diag5 varchar2(10) NULL,

Diag6 varchar2(10) NULL,

Diag7 varchar2(10) NULL,

Diag8 varchar2(10) NULL,

Diag9 varchar2(10) NULL,

Diag10 varchar2(10) NULL,

Diag11 varchar2(10) NULL,

Diag12 varchar2(10) NULL,

Diag13 varchar2(10) NULL,

Diag14 varchar2(10) NULL,

Diag15 varchar2(10) NULL,

Diag16 varchar2(10) NULL,

Diag17 varchar2(10) NULL,

Diag18 varchar2(10) NULL,

Diag19 varchar2(10) NULL,

Diag20 varchar2(10) NULL,

Diag21 varchar2(10) NULL,

Diag22 varchar2(10) NULL,

Diag23 varchar2(10) NULL,

Diag24 varchar2(10) NULL,

Diag25 varchar2(10) NULL,

DRG varchar2(15) NULL,

/*Procedure Information*/

PrincipalProcedure varchar2(10) NULL,

PrincipalProcedureDate date NULL,

Proc2 varchar2(50) NULL,

Proc2Date date NULL,

Proc3 varchar2(10) NULL,

Proc3Date date NULL,

Proc4 varchar2(10) NULL,

Proc4Date date NULL,

Proc5 varchar2(10) NULL,

Proc5Date date NULL,

Proc6 varchar2(10) NULL,

Proc6Date date NULL,

Proc7 varchar2(10) NULL,

Proc7Date date NULL,

Proc8 varchar2(10) NULL,

Proc8Date date NULL,

Proc9 varchar2(10) NULL,

Proc9Date date NULL,

Proc10 varchar2(10) NULL,

Proc10Date date NULL,

Proc11 varchar2(10) NULL,

Proc11Date date NULL,

Proc12 varchar2(10) NULL,

Proc12Date date NULL,

Proc13 varchar2(10) NULL,

Proc13Date date NULL,

Proc14 varchar2(10) NULL,

Proc14Date date NULL,

Proc15 varchar2(10) NULL,

Proc15Date date NULL,

Proc16 varchar2(10) NULL,

Proc16Date date NULL,

Proc17 varchar2(10) NULL,

Proc17Date date NULL,

Proc18 varchar2(10) NULL,

Proc18Date date NULL,

Proc19 varchar2(10) NULL,

Proc19Date date NULL,

Proc20 varchar2(10) NULL,

Proc20Date date NULL,

Proc21 varchar2(10) NULL,

Proc21Date date NULL,

Proc22 varchar2(10) NULL,

Proc22Date date NULL,

Proc23 varchar2(10) NULL,

Proc23Date date NULL,

Proc24 varchar2(10) NULL,

Proc24Date date NULL,

Proc25 varchar2(10) NULL,

Proc25Date date NULL,

/*Value Codes*/

ValueCode1 char(3) NULL,

ValueAmount1 varchar2(10) NULL,

ValueCode2 char(3) NULL,

ValueAmount2 varchar2(10) NULL,

ValueCode3 char(3) NULL,

ValueAmount3 varchar2(10) NULL,

ValueCode4 char(3) NULL,

ValueAmount4 varchar2(10) NULL,

ValueCode5 char(3) NULL,

ValueAmount5 varchar2(10) NULL,

ValueCode6 char(3) NULL,

ValueAmount6 varchar2(10) NULL,

ValueCode7 varchar2(10) NULL,

ValueAmount7 varchar2(10) NULL,

ValueCode8 char(3) NULL,

ValueAmount8 varchar2(10) NULL,

ValueCode9 char(3) NULL,

ValueAmount9 varchar2(10) NULL,

ValueCode10 char(3) NULL,

ValueAmount10 varchar2(10) NULL,

ValueCode11 char(3) NULL,

ValueAmount11 varchar2(10) NULL,

ValueCode12 char(3) NULL,

ValueAmount12 varchar2(10) NULL,

--ValueCode13 char(3) NULL,

--ValueAmount13 varchar2(10) NULL,

--ValueCode14 char(3) NULL,

--ValueAmount14 varchar2(10) NULL,

--ValueCode15 char(3) NULL,

--ValueAmount15 varchar2(10) NULL,

--ValueCode16 char(3) NULL,

--ValueAmount16 varchar2(10) NULL,

--ValueCode17 char(3) NULL,

--ValueAmount17 varchar2(10) NULL,

--ValueCode18 char(3) NULL,

--ValueAmount18 varchar2(10) NULL,

--ValueCode19 char(3) NULL,

--ValueAmount19 varchar2(10) NULL,

--ValueCode20 char(3) NULL,

--ValueAmount20 varchar2(10) NULL,

--ValueCode21 char(3) NULL,

--ValueAmount21 varchar2(10) NULL,

--ValueCode22 char(3) NULL,

--ValueAmount22 varchar2(10) NULL,

--ValueCode23 char(3) NULL,

--ValueAmount23 varchar2(10) NULL,

--ValueCode24 char(3) NULL,

--ValueAmount24 varchar2(10) NULL,

/*Condition Codes*/

ConditionCode1 varchar2(30) NULL,

ConditionCode2 varchar2(30) NULL,

ConditionCode3 varchar2(30) NULL,

ConditionCode4 varchar2(30) NULL,

ConditionCode5 varchar2(30) NULL,

ConditionCode6 varchar2(30) NULL,

ConditionCode7 varchar2(30) NULL,

ConditionCode8 varchar2(30) NULL,

ConditionCode9 varchar2(30) NULL,

ConditionCode10 varchar2(30) NULL,

--ConditionCode11 varchar2(30) NULL,

--ConditionCode12 varchar2(30) NULL,

--ConditionCode13 varchar2(30) NULL,

--ConditionCode14 varchar2(30) NULL,

--ConditionCode15 varchar2(30) NULL,

--ConditionCode16 varchar2(30) NULL,

--ConditionCode17 varchar2(30) NULL,

--ConditionCode18 varchar2(30) NULL,

--ConditionCode19 varchar2(30) NULL,

--ConditionCode20 varchar2(30) NULL,

--ConditionCode21 varchar2(30) NULL,

--ConditionCode22 varchar2(30) NULL,

--ConditionCode23 varchar2(30) NULL,

--ConditionCode24 varchar2(30) NULL,

/*Occurrence Codes*/

OccurranceCode1 char(3) NULL,

OccurranceDate1 date NULL,

OccurranceCode2 char(3) NULL,

OccurranceDate2 date NULL,

OccurranceCode3 char(3) NULL,

OccurranceDate3 date NULL,

OccurranceCode4 char(3) NULL,

OccurranceDate4 date NULL,

OccurranceCode5 char(3) NULL,

OccurranceDate5 date NULL,

OccurranceCode6 char(3) NULL,

OccurranceDate6 date NULL,

OccurranceCode7 char(3) NULL,

OccurranceDate7 date NULL,

OccurranceCode8 char(3) NULL,

OccurranceDate8 date NULL,

--OccurranceCode9 char(3) NULL,

--OccurranceDate9 date NULL,

--OccurranceCode10 char(3) NULL,

--OccurranceDate10 date NULL,

--OccurranceCode11 char(3) NULL,

--OccurranceDate11 date NULL,

--OccurranceCode12 char(3) NULL,

--OccurranceDate12 date NULL,

--OccurranceCode13 char(3) NULL,

--OccurranceDate13 date NULL,

--OccurranceCode14 char(3) NULL,

--OccurranceDate14 date NULL,

--OccurranceCode15 char(3) NULL,

--OccurranceDate15 date NULL,

--OccurranceCode16 char(3) NULL,

--OccurranceDate16 date NULL,

--OccurranceCode17 char(3) NULL,

--OccurranceDate17 date NULL,

--OccurranceCode18 char(3) NULL,

--OccurranceDate18 date NULL,

--OccurranceCode19 char(3) NULL,

--OccurranceDate19 date NULL,

--OccurranceCode20 char(3) NULL,

--OccurranceDate20 date NULL,

--OccurranceCode21 char(3) NULL,

--OccurranceDate21 date NULL,

--OccurranceCode22 char(3) NULL,

--OccurranceDate22 date NULL,

--OccurranceCode23 char(3) NULL,

--OccurranceDate23 date NULL,

--OccurranceCode24 char(3) NULL,

--OccurranceDate24 date NULL,

OccurranceSpanCode1 char(3) NULL,

OccurranceSpanFrom1 date NULL,

OccurranceSpanTo1 date NULL,

OccurranceSpanCode2 char(3) NULL,

OccurranceSpanFrom2 date NULL,

OccurranceSpanTo2 date NULL,

OccurranceSpanCode3 char(3) NULL,

OccurranceSpanFrom3 date NULL,

OccurranceSpanTo3 date NULL,

OccurranceSpanCode4 char(3) NULL,

OccurranceSpanFrom4 date NULL,

OccurranceSpanTo4 date NULL,

/*CR1*/

PatientWeight varchar2(10) NULL,

AmbulanceTransportCode char(1) NULL,

AmbulanceTransportReasonCode char(1) NULL,

TransportDistance varchar2(15) NULL,

RoundTripPurposeDescription varchar2(80) NULL,

StretcherPurposeDescription varchar2(80) NULL,

/* CRC — Ambulance Certification */

AmbulanceConditionIndicator char(1) NULL,

AmbulanceConditionCode1 char(3) NULL,

AmbulanceConditionCode2 char(3) NULL,

AmbulanceConditionCode3 char(3) NULL,

AmbulanceConditionCode4 char(3) NULL,

AmbulanceConditionCode5 char(3) NULL,

/*Repricing*/

RepricedClaimNumber varchar2(50) NULL,

RepricingMethodology varchar2(3) NULL,

RepricedAmount number(19,4) NULL,

SavingsAmount number(19,4) NULL,

RepricerID varchar2(30) NULL,

RepricingRate varchar2(9) NULL,

APG_Code varchar2(30) NULL,

APG_Amount number(19,4) NULL,

ApprovedRevenueCode varchar2 (48) NULL,

ApprovedProcedureCode varchar2 (48) NULL,

ApprovedUnitCode char(2) NULL,

ApprovedUnits number(19) NULL,

RejectReason varchar2(3) NULL,

ComplianceCode varchar2(2) NULL,

ExceptionCode varchar2(2) NULL,

/*SQL To EDI*/

--[ClaimAction] char(1) NULL,

--[ErrorMessage] varchar2(1000) NULL

)NOLOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

create sequence edi_claims_seq start with 1 increment by 1;

create or replace trigger edi_claims_trigger

before insert on EDI_Claims

REFERENCING NEW AS newrow

for each row

begin

select edi_claims_seq.nextval into :newrow.ID from dual;

end;

Detail

CREATE TABLE EDI_ClaimDetail(

ID number(19) NOT NULL,

ClaimID number(19) NOT NULL,

LineNumber number(18, 0) NULL,

LineID varchar2(50) NULL,

ServiceDateFrom varchar2(50) NULL,

ServiceDateTo varchar2(50) NULL,

AssessmentDate varchar2(50) NULL,

FacilityCode varchar2(50) NULL,

RevenueCode varchar2(50) NULL,

ProcedureCode varchar2(50) NULL,

Amount varchar2(50) NULL,

Unit varchar2(50) NULL,

Quantity varchar2(50) NULL,

UnitRate varchar2(50) NULL,

NonCovered varchar2(50) NULL,

Paid varchar2(50) NULL,

PlaceOfService char(3) NULL,

Modifier1 char(3) NULL,

Modifier2 char(3) NULL,

Modifier3 char(3) NULL,

Modifier4 char(3) NULL,

ProcedureDescription varchar2(80) NULL,

OralCavityDesignation1 char(3) NULL,

OralCavityDesignation2 char(3) NULL,

OralCavityDesignation3 char(3) NULL,

OralCavityDesignation4 char(3) NULL,

OralCavityDesignation5 char(3) NULL,

DiagPointer1 char(1) NULL,

DiagPointer2 char(1) NULL,

DiagPointer3 char(1) NULL,

DiagPointer4 char(1) NULL,

ToothNumber char(2) NULL,

Surface varchar2(10) NULL,

EmergencyIndicator char(1) NULL,

ServiceTax number(19,4) NULL,

FacilityTax number(19,4) NULL,

SalesTax number(19,4) NULL,

ApprovedAmount number(19,4) NULL,

LineK3_01 varchar2(80) NULL,

LineK3_02 varchar2(80) NULL,

LineK3_03 varchar2(80) NULL,

LineK3_04 varchar2(80) NULL,

LineK3_05 varchar2(80) NULL,

LineK3_06 varchar2(80) NULL,

LineK3_07 varchar2(80) NULL,

LineK3_08 varchar2(80) NULL,

LineK3_09 varchar2(80) NULL,

LineK3_10 varchar2(80) NULL,

Remark varchar2(100) NULL,

AmbulancePatientCount bigint NULL,

/****PWK****/

ReportType char(2) NULL,

ReportTransmission char(2) NULL,

AttachmentControlNumber varchar2(80) NULL,

/*HCP*/

RepricingMethodology varchar2(3) NULL,

RepricedAmount number(19,4) NULL,

SavingsAmount number(19,4) NULL,

RepricerID varchar2(30) NULL,

RepricingRate varchar2(9) NULL,

APG_Code varchar2(30) NULL,

APG_Amount number(19,4) NULL,

ApprovedRevenueCode varchar2 (48) NULL,

ApprovedProcedureCode varchar2 (48) NULL,

ApprovedUnitCode char(2) NULL,

ApprovedUnits number(19) NULL,

RejectReason varchar2(3) NULL,

ComplianceCode varchar2(2) NULL,

ExceptionCode varchar2(2) NULL,

/*  Drugs  */

DrugCode varchar2(48) NULL,

DrugUnitPrice number(19,4) NULL,

DrugUnitCode char(2) NULL,

DrugUnits float NULL,

PrescriptionNumber varchar2(30) NULL,

/* CR1 */

PatientWeight varchar2(10) NULL,

AmbulanceTransportCode char(1) NULL,

AmbulanceTransportReasonCode char(1) NULL,

TransportDistance varchar2(15) NULL,

RoundTripPurposeDescription varchar2(80) NULL,

StretcherPurposeDescription varchar2(80) NULL,

/* CRC — Ambulance Certification */

AmbulanceConditionIndicator char(1) NULL,

AmbulanceConditionCode1 char(3) NULL,

AmbulanceConditionCode2 char(3) NULL,

AmbulanceConditionCode3 char(3) NULL,

AmbulanceConditionCode4 char(3) NULL,

AmbulanceConditionCode5 char(3) NULL,

/*  Attending Provider */

AttendingProviderLast varchar2(35) NULL,

AttendingProviderFirst varchar2(25) NULL,

AttendingProviderMiddle varchar2(25) NULL,

AttendingProviderSuffix varchar2(10) NULL,

AttendingProviderIDQual char(2) NULL,

AttendingProviderID varchar2(35) NULL,

AttendingProviderOtherIDQual char(2) NULL,

AttendingProviderOtherID varchar2(30) NULL,

/*  Operating Provider */

OperatingProviderLast varchar2(35) NULL,

OperatingProviderFirst varchar2(25) NULL,

OperatingProviderMiddle varchar2(25) NULL,

OperatingProviderSuffix varchar2(10) NULL,

OperatingProviderIDQual char(2) NULL,

OperatingProviderID varchar2(35) NULL,        

OperatingProviderOtherIDQual char(2) NULL,

OperatingProviderOtherID varchar2(30) NULL,

/*  Other Provider */

OtherProviderLast varchar2(35) NULL,

OtherProviderFirst varchar2(25) NULL,

OtherProviderMiddle varchar2(25) NULL,

OtherProviderSuffix varchar2(10) NULL,

OtherProviderIDQual char(2) NULL,

OtherProviderID varchar2(35) NULL,

OtherProviderOtherIDQual char(2) NULL,

OtherProviderOtherID varchar2(30) NULL,

/*  Rendering Provider */

RenderingProviderLast varchar2(35) NULL,

RenderingProviderFirst varchar2(25) NULL,

RenderingProviderMiddle varchar2(25) NULL,

RenderingProviderSuffix varchar2(10) NULL,

RenderingProviderIDQual char(2) NULL,

RenderingProviderID varchar2(35) NULL,

RenderingProviderOtherIDQual char(2) NULL,

RenderingProviderOtherID varchar2(30) NULL,

/*  PurchasedService Provider */

PurchasedSrvProviderLast varchar2(35) NULL,

PurchasedSrvProviderFirst varchar2(25) NULL,

PurchasedSrvProviderMiddle varchar2(25) NULL,

PurchasedSrvProviderSuffix varchar2(10) NULL,

PurchasedSrvProviderIDQual char(2) NULL,

PurchasedSrvProviderID varchar2(35) NULL,

PurchasedSrvProvOtherIDQual char(2) NULL,

PurchasedSrvProvOtherID varchar2(30) NULL,

/*  Facility Provider */

FacilityName varchar2(35) NULL,

FacilityIDQual char(2) NULL,

FacilityID varchar2(35) NULL,

FacilityAddress1 varchar2(25) NULL,

FacilityAddress2 varchar2(25) NULL,

FacilityCity varchar2(30) NULL,

FacilityState char(2) NULL,

FacilityZip varchar2(15) NULL,

FacilityOtherIDQual char(2) NULL,

FacilityOtherID varchar2(30) NULL,

/*  Supervising Provider */

SupervisingProviderLast varchar2(35) NULL,

SupervisingProviderFirst varchar2(25) NULL,

SupervisingProviderMiddle varchar2(25) NULL,

SupervisingProviderSuffix varchar2(10) NULL,

SupervisingProviderIDQual char(2) NULL,

SupervisingProviderID varchar2(35) NULL,

SupervisingProviderOtherIDQual char(2) NULL,

SupervisingProviderOtherID varchar2(30) NULL,

/*  Ordering Provider */

OrderingProviderLast varchar2(35) NULL,

OrderingProviderFirst varchar2(25) NULL,

OrderingProviderMiddle varchar2(25) NULL,

OrderingProviderSuffix varchar2(10) NULL,

OrderingProviderIDQual char(2) NULL,

OrderingProviderID varchar2(35) NULL,

OrderingProviderOtherIDQual char(2) NULL,

OrderingProviderOtherID varchar2(30) NULL,

/*  Referring Provider */

ReferringProviderLast varchar2(35) NULL,

ReferringProviderFirst varchar2(25) NULL,

ReferringProviderMiddle varchar2(25) NULL,

ReferringProviderSuffix varchar2(10) NULL,

ReferringProviderIDQual char(2) NULL,

ReferringProviderID varchar2(35) NULL,

ReferringProviderOtherIDQual char(2) NULL,

ReferringProviderOtherID varchar2(30) NULL,

/*COB 1 */

OtherPayer1ID varchar2(30) NULL,

OtherPayer1Paid number(19,4) NULL,

OtherPayer1PaidProcedure varchar2(30) NULL,

OtherPayer1PaidRevenueCode varchar2(30) NULL,

OtherPayer1PaidQuantity number(19) NULL,

OtherPayer1BundledLine int NULL,

OtherPayer1AdjReasonGroup1 char(2) NULL,

OtherPayer1AdjReason1 varchar2(5) NULL,

OtherPayer1AdjAmount1 number(19,4) NULL,

OtherPayer1AdjQuantity1 number(19) NULL,

OtherPayer1AdjReasonGroup2 char(2) NULL,

OtherPayer1AdjReason2 varchar2(5) NULL,

OtherPayer1AdjAmount2 number(19,4) NULL,

OtherPayer1AdjQuantity2 number(19) NULL,

OtherPayer1AdjReasonGroup3 char(2) NULL,

OtherPayer1AdjReason3 varchar2(5) NULL,

OtherPayer1AdjAmount3 number(19,4) NULL,

OtherPayer1AdjQuantity3 number(19) NULL,

OtherPayer1AdjReasonGroup4 char(2) NULL,

OtherPayer1AdjReason4 varchar2(5) NULL,

OtherPayer1AdjAmount4 number(19,4) NULL,

OtherPayer1AdjQuantity4 number(19) NULL,

--OtherPayer1AdjReasonGrp5Plus varchar2(77) NULL,

--OtherPayer1AdjReason5Plus varchar2(155) NULL,

--OtherPayer1AdjAmount5Plus varchar2(493) NULL,

--OtherPayer1AdjQuantity5Plus varchar2(415) NULL,

OtherPayer1PaidDate date NULL,

/*COB 2 */

OtherPayer2ID varchar2(30) NULL,

OtherPayer2Paid number(19,4) NULL,

OtherPayer2PaidProcedure varchar2(30) NULL,

OtherPayer2PaidRevenueCode varchar2(30) NULL,

OtherPayer2PaidQuantity number(19) NULL,

OtherPayer2BundledLine int NULL,

OtherPayer2AdjReasonGroup1 char(2) NULL,

OtherPayer2AdjReason1 varchar2(5) NULL,

OtherPayer2AdjAmount1 number(19,4) NULL,

OtherPayer2AdjQuantity1 number(19) NULL,

OtherPayer2AdjReasonGroup2 char(2) NULL,

OtherPayer2AdjReason2 varchar2(5) NULL,

OtherPayer2AdjAmount2 number(19,4) NULL,

OtherPayer2AdjQuantity2 number(19) NULL,

OtherPayer2AdjReasonGroup3 char(2) NULL,

OtherPayer2AdjReason3 varchar2(5) NULL,

OtherPayer2AdjAmount3 number(19,4) NULL,

OtherPayer2AdjQuantity3 number(19) NULL,

OtherPayer2AdjReasonGroup4 char(2) NULL,

OtherPayer2AdjReason4 varchar2(5) NULL,

OtherPayer2AdjAmount4 number(19,4) NULL,

OtherPayer2AdjQuantity4 number(19) NULL,

--OtherPayer2AdjReasonGrp5Plus varchar2(77) NULL,

--OtherPayer2AdjReason5Plus varchar2(155) NULL,

--OtherPayer2AdjAmount5Plus varchar2(493) NULL,

--OtherPayer2AdjQuantity5Plus varchar2(415) NULL,

OtherPayer2PaidDate date NULL

)NOLOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

create sequence edi_claimdetail_seq start with 1 increment by 1;

create or replace trigger edi_claimdetail_trigger

before insert on EDI_ClaimDetail

REFERENCING NEW AS newrow

for each row

begin

select edi_claimdetail_seq.nextval into :newrow.ID from dual;

end;