This is a free, industrial strength Open Source database available at www.MySQL.comHeaderCREATE TABLE `EDI_Claims`( `ID` int(11) NOT NULL auto_increment, /*File Level Data*/ `Filename` varchar(100) default NULL, `ImageFilePath` varchar(300) default NULL, `ImageFilename` varchar(100) default NULL, `Version` varchar(12) default NULL, `TradingPartnerIDType` char(2) default NULL, `TradingPartnerID` varchar(15) NOT NULL, `TransactionDate` varchar(15) default NULL, `ReceiveDate` varchar(15) default NULL, `SubmitterName` varchar(60) default NULL, `SubmitterID` varchar(30) default NULL, `SubmitterContact` varchar(60) default NULL, `SubmitterTel` varchar(20) default NULL, `SubmitterTelExt` varchar(10) default NULL, `SubmitterFax` varchar(20) default NULL, `SubmitterEmail` varchar(80) default NULL, `ReceiverName` varchar(60) default NULL, `ReceiverID` varchar(30) default NULL, `TransactionType` char(2) default NULL, /*Billing Provider*/ `FedTaxID` varchar(15) default NULL, `BillProvIDType` char(2) default NULL, `BillProvID` varchar(80) default NULL, `BillProvNPI` varchar(12) default NULL, `BillProvLast` varchar(60) default NULL, `BillProvFirst` varchar(35) default NULL, `BillProvMiddle` varchar(25) default NULL, `BillProvSuffix` varchar(10) default NULL, `BillProvSpecialty` varchar(10) default NULL, `BillProvAddress` varchar(55) default NULL, `BillProvAddress2` varchar(55) default NULL, `BillProvCity` varchar(30) default NULL, `BillProvState` char(2) default NULL, `BillProvZip` varchar(15) default NULL, `BillProvCountry` varchar(3) default NULL, `BillProvSubdivision` varchar(3) default NULL, `BillProvContact` varchar(60) default NULL, `BillProvTel` varchar(80) default NULL, `BillProvTelExt` varchar(80) default NULL, `BillProvFax` varchar(80) default NULL, `BillProvEmail` varchar(80) default NULL, `BillProvOtherIDQual1` varchar(100) default NULL, `BillProvOtherID1` varchar(100) default NULL, `BillProvOtherIDQual2` varchar(100) default NULL, `BillProvOtherID2` varchar(100) default NULL, `BillProvOtherIDQual3` varchar(100) default NULL, `BillProvOtherID3` varchar(100) default NULL, `BillProvOtherIDQual4` varchar(100) default NULL, `BillProvOtherID4` varchar(100) default NULL, `BillProvOtherIDQual5` varchar(100) default NULL, `BillProvOtherID5` varchar(100) default NULL, /*Pay-to Provider*/ `PayToProvIDType` char(2) default NULL, `PayToProvID` varchar(80) default NULL, `PayToProvTaxID` varchar(12) default NULL, `PayToProvNPI` varchar(12) default NULL, `PayToProvLast` varchar(60) default NULL, `PayToProvFirst` varchar(35) default NULL, `PayToProvMiddle` varchar(25) default NULL, `PayToProvSuffix` varchar(10) default NULL, `PayToProvSpecialty` varchar(10) default NULL, `PayToProvAddress` varchar(55) default NULL, `PayToProvAddress2` varchar(55) default NULL, `PayToProvCity` varchar(30) default NULL, `PayToProvState` char(2) default NULL, `PayToProvZip` varchar(15) default NULL, /*Pay-to Payer*/ `PayToPayerIDType` char(2) default NULL, `PayToPayerID` varchar(80) default NULL, `PayToPayerTaxID` varchar(12) default NULL, `PayToPayerLast` varchar(60) default NULL, `PayToPayerFirst` varchar(35) default NULL, `PayToPayerMiddle` varchar(25) default NULL, `PayToPayerSuffix` varchar(10) default NULL, `PayToPayerAddress` varchar(55) default NULL, `PayToPayerAddress2` varchar(55) default NULL, `PayToPayerCity` varchar(30) default NULL, `PayToPayerState` char(2) default NULL, `PayToPayerZip` varchar(15) default NULL, `PayToPayerOtherIDQual` char(2) default NULL, `PayToPayerOtherID` varchar(30) default NULL, /*Subscriber*/ `SubscriberLast` varchar(60) default NULL, `SubscriberFirst` varchar(35) default NULL, `SubscriberMiddle` varchar(25) default NULL, `SubscriberSuffix` varchar(10) default NULL, `SubscriberIDType` char(2) default NULL, `SubscriberID` varchar(80) default NULL, `SubscriberAddress` varchar(55) default NULL, `SubscriberAddress2` varchar(55) default NULL, `SubscriberCity` varchar(30) default NULL, `SubscriberState` char(2) default NULL, `SubscriberZip` varchar(15) default NULL, `SubscriberCountry` varchar(3) default NULL, `SubscriberLocation` varchar(30) default NULL, `SubscriberSubdivision` varchar(3) default NULL, `SubscriberDOB` varchar(10) default NULL, `SubscriberSex` char(1) default NULL, `SubscriberEthnicity` varchar(75) default NULL, `SubscriberMaritalStatus` char(1) default NULL, `SubscriberCollectionMethod` varchar(30) default NULL, `SubscriberSSN` varchar(12) default NULL, `SubscriberAgencyClaimNo` varchar(50) default NULL, `SubscriberMemberID` varchar(50) default NULL, `SubscriberPersonalID` varchar(50) default NULL, `SubscriberContact` varchar(60) default NULL, `SubscriberTel` varchar(15) default NULL, `SubscriberTelExt` varchar(10) default NULL, /*Destination Payer*/ `PayerName` varchar(60) default NULL, `PayerIDType` char(2) default NULL, `PayerID` varchar(30) default NULL, `PayerAddress` varchar(55) default NULL, `PayerAddress2` varchar(55) default NULL, `PayerCity` varchar(30) default NULL, `PayerState` char(2) default NULL, `PayerZip` varchar(15) default NULL, `PayerResponsibility` char(1) default NULL, `PayerOtherIDQual1` varchar(3) default NULL, `PayerOtherID1` varchar(50) default NULL, `PayerOtherIDQual2` varchar(3) default NULL, `PayerOtherID2` varchar(50) default NULL, `PayerOtherIDQual3` varchar(3) default NULL, `PayerOtherID3` varchar(50) default NULL, `GroupNo` varchar(50) default NULL, `GroupName` varchar(60) default NULL, `InsuranceType` char(2) default NULL, `FilingIndicator` char(2) default NULL, `COBIndicator` char(1) default NULL, /*Responsible*/ `ResponsibleLast` varchar(60) default NULL, `ResponsibleFirst` varchar(35) default NULL, `ResponsibleMiddle` varchar(25) default NULL, `ResponsibleSuffix` varchar(10) default NULL, `ResponsibleAddress` varchar(55) default NULL, `ResponsibleAddress2` varchar(55) default NULL, `ResponsibleCity` varchar(30) default NULL, `ResponsibleState` char(2) default NULL, `ResponsibleZip` varchar(15) default NULL, `ResponsibleIDType` char(2) default NULL, `ResponsibleID` varchar(80) default NULL, /*Patient*/ `PatientLast` varchar(60) default NULL, `PatientFirst` varchar(35) default NULL, `PatientMiddle` varchar(25) default NULL, `PatientSuffix` varchar(10) default NULL, `PatientAddress` varchar(55) default NULL, `PatientAddress2` varchar(55) default NULL, `PatientCity` varchar(30) default NULL, `PatientState` char(2) default NULL, `PatientZip` varchar(15) default NULL, `PatientCountry` varchar(3) default NULL, `PatientLocation` varchar(30) default NULL, `PatientSubdivision` varchar(3) default NULL, `PatientIDType` char(2) default NULL, `PatientID` varchar(80) default NULL, `PatientSSN` varchar(12) default NULL, `PatientMemberID` varchar(50) default NULL, `PatientPersonalID` varchar(50) default NULL, `PatientInsurancePolicy` varchar(50) default NULL, `PatientDOB` varchar(50) default NULL, `PatientSex` char(1) default NULL, `PatientEthnicity` varchar(75) default NULL, `PatientMaritalStatus` char(1) default NULL, `PatientCollectionMethod` varchar(30) default NULL, `PatientRelationship` char(2) default NULL, `PatientPaid` varchar(10) default NULL, `PregnancyIndicator` char(1) default NULL, /*Rendering Provider*/ `RendProvIDType` char(2) default NULL, `RendProvID` varchar(80) default NULL, `RendProvNPI` varchar(12) default NULL, `RendProvTaxID` varchar(12) default NULL, `RendProvLast` varchar(60) default NULL, `RendProvFirst` varchar(35) default NULL, `RendProvMiddle` varchar(25) default NULL, `RendProvSuffix` varchar(10) default NULL, `RendProvSpecialty` varchar(10) default NULL, `RendProvOtherIDQual1` varchar(3) default NULL, `RendProvOtherID1` varchar(50) default NULL, `RendProvOtherIDQual2` varchar(3) default NULL, `RendProvOtherID2` varchar(50) default NULL, `RendProvOtherIDQual3` varchar(3) default NULL, `RendProvOtherID3` varchar(50) default NULL, /*Attending Provider*/ `AttendProvLast` varchar(60) default NULL, `AttendProvFirst` varchar(35) default NULL, `AttendProvMiddle` varchar(25) default NULL, `AttendProvSuffix` varchar(10) default NULL, `AttendProvIDType` char(2) default NULL, `AttendProvID` varchar(80) default NULL, `AttendProvTaxID` varchar(12) default NULL, `AttendProvNPI` varchar(12) default NULL, `AttendProvOtherIDQual1` varchar(100) default NULL, `AttendProvOtherID1` varchar(100) default NULL, `AttendProvSpecialty` varchar(10) default NULL, /*Operating Provider*/ `OperatingProvLast` varchar(60) default NULL, `OperatingProvFirst` varchar(35) default NULL, `OperatingProvMiddle` varchar(25) default NULL, `OperatingProvSuffix` varchar(10) default NULL, `OperatingProvIDType` char(2) default NULL, `OperatingProvID` varchar(80) default NULL, `OperatingProvNPI` varchar(12) default NULL, `OperatingProvTaxID` varchar(12) default NULL, `OperatingProvOtherIDQual1` varchar(100) default NULL, `OperatingProvOtherID1` varchar(100) default NULL, `OperatingProvSpecialty` varchar(10) default NULL, /*Other Provider*/ `OtherProvLast` varchar(60) default NULL, `OtherProvFirst` varchar(35) default NULL, `OtherProvMiddle` varchar(25) default NULL, `OtherProvSuffix` varchar(10) default NULL, `OtherProvIDType` varchar(100) default NULL, `OtherProvID` varchar(100) default NULL, `OtherProvTaxID` varchar(12) default NULL, `OtherProvNPI` varchar(12) default NULL, `OtherProvOtherIDQual1` varchar(100) default NULL, `OtherProvOtherID1` varchar(100) default NULL, `OtherProvSpecialty` varchar(100) default NULL, /*Facility*/ `FacilityType` char(2) default NULL, `FacilityIDType` char(2) default NULL, `FacilityID` varchar(80) default NULL, `FacilityNPI` varchar(12) default NULL, `FacilityTaxID` varchar(12) default NULL, `FacilityOtherIDQual1` varchar(100) default NULL, `FacilityOtherID1` varchar(100) default NULL, `FacilityName` varchar(80) default NULL, `FacilityAddress` varchar(55) default NULL, `FacilityAddress2` varchar(55) default NULL, `FacilityCity` varchar(30) default NULL, `FacilitySpecialty` varchar(100) default NULL, `FacilityState` char(2) default NULL, `FacilityZip` varchar(15) default NULL, /*Referring Provider*/ `RefProvLast` varchar(60) default NULL, `RefProvFirst` varchar(35) default NULL, `RefProvMiddle` varchar(25) default NULL, `RefProvSuffix` varchar(10) default NULL, `RefProvIDType` char(2) default NULL, `RefProvID` varchar(80) default NULL, `RefProvTaxID` varchar(12) default NULL, `RefProvNPI` varchar(12) default NULL, `RefProvOtherIDQual1` varchar(100) default NULL, `RefProvOtherID1` varchar(100) default NULL, `RefProvSpecialty` varchar(10) default NULL, /*Supervising Provider*/ `SupervProvLast` varchar(60) default NULL, `SupervProvFirst` varchar(35) default NULL, `SupervProvMiddle` varchar(25) default NULL, `SupervProvSuffix` varchar(10) default NULL, `SupervProvIDType` char(2) default NULL, `SupervProvID` varchar(80) default NULL, `SupervProvTaxID` varchar(12) default NULL, `SupervProvNPI` varchar(12) default NULL, `SupervProvOtherIDQual1` varchar(3) default NULL, `SupervProvOtherID1` varchar(50) default NULL, `SupervProvSpecialty` varchar(10) default NULL, /*Assistant Surgeon*/ `AssistSurgLast` varchar(60) default NULL, `AssistSurgFirst` varchar(35) default NULL, `AssistSurgMiddle` varchar(25) default NULL, `AssistSurgSuffix` varchar(10) default NULL, `AssistSurgIDType` char(2) default NULL, `AssistSurgID` varchar(80) default NULL, `AssistSurgTaxID` varchar(12) default NULL, `AssistSurgNPI` varchar(12) default NULL, `AssistSurgOtherIDQual1` varchar(3) default NULL, `AssistSurgOtherID1` varchar(50) default NULL, `AssistSurgSpecialty` varchar(10) default NULL, /*Ambulance Pick-up Location*/ `PickUpAddress` varchar(55) default NULL, `PickUpAddress2` varchar(55) default NULL, `PickUpCity` varchar(30) default NULL, `PickUpState` char(2) default NULL, `PickUpZip` varchar(15) default NULL, /*Ambulance Drop-off Location*/ `DropOffName` varchar(80) default NULL, `DropOffAddress` varchar(55) default NULL, `DropOffAddress2` varchar(55) default NULL, `DropOffCity` varchar(30) default NULL, `DropOffState` char(2) default NULL, `DropOffZip` varchar(15) default NULL, /*COB Payer 1*/ `OtherInsuredLast` varchar(60) default NULL, `OtherInsuredFirst` varchar(35) default NULL, `OtherInsuredMiddle` varchar(25) default NULL, `OtherInsuredSuffix` varchar(10) default NULL, `OtherInsuredIDQual` char(2) default NULL, `OtherInsuredID` varchar(10) default NULL, `OtherInsuredAddress` varchar(55) default NULL, `OtherInsuredAddress2` varchar(55) default NULL, `OtherInsuredCity` varchar(30) default NULL, `OtherInsuredState` char(2) default NULL, `OtherInsuredZip` varchar(15) default NULL, `OtherInsuredCountry` varchar(3) default NULL, `OtherInsuredSubdivision` varchar(3) default NULL, `OtherInsuredPolicy` varchar(50) default NULL, `OtherInsuredDOB` varchar(10) default NULL, `OtherInsuredSex` char(1) default NULL, `OtherInsuredRelationship` char(2) default NULL, `OtherInsuredOtherIDQual1` char(3) default NULL, `OtherInsuredOtherID1` varchar(50) default NULL, `OtherInsuredOtherIDQual2` char(3) default NULL, `OtherInsuredOtherID2` varchar(50) default NULL, `OtherInsuredOtherIDQual3` char(3) default NULL, `OtherInsuredOtherID3` varchar(50) default NULL, `OtherPayerResponsibility` varchar(100) default NULL, `OtherPayerName` varchar(35) default NULL, `OtherPayerIDQual` char(2) default NULL, `OtherPayerID` varchar(80) default NULL, `OtherPayerAddress1` varchar(55) default NULL, `OtherPayerAddress2` varchar(55) default NULL, `OtherPayerCity` varchar(30) default NULL, `OtherPayerState` char(2) default NULL, `OtherPayerZip` varchar(15) default NULL, `OtherPayerPaid` varchar(10) default NULL, `OtherPayerGroupName` varchar(100) default NULL, `OtherPayerGroupNo` varchar(100) default NULL, `OtherPayerInsuranceType` char(2) default NULL, `OtherPayerFilingIndicator` char(2) default NULL, `OtherPayerAdjustmentReasonGroup` varchar(100) default NULL, `OtherPayerAdjustmentReason` varchar(100) default NULL, `OtherPayerAdjustmentAmount` varchar(100) default NULL, `OtherPayerPaidDate` varchar(15) default NULL, `OtherPayerInfoRelease` varchar(100) default NULL, `OtherPayerBenefitAssign` varchar(100) default NULL, `OtherPayerDocControlNo` varchar(100) default NULL, `OtherPayerPriorAuth` varchar(100) default NULL, `OtherPayerOtherIDQual1` char(3) default NULL, `OtherPayerOtherID1` varchar(50) default NULL, `OtherPayerOtherIDQual2` char(3) default NULL, `OtherPayerOtherID2` varchar(50) default NULL, /*COB Payer 2*/ `OtherInsured2Last` varchar(60) default NULL, `OtherInsured2First` varchar(35) default NULL, `OtherInsured2Middle` varchar(25) default NULL, `OtherInsured2Suffix` varchar(10) default NULL, `OtherInsured2IDQual` char(2) default NULL, `OtherInsured2ID` varchar(10) default NULL, `OtherInsured2Address` varchar(55) default NULL, `OtherInsured2Address2` varchar(55) default NULL, `OtherInsured2City` varchar(30) default NULL, `OtherInsured2State` char(2) default NULL, `OtherInsured2Zip` varchar(15) default NULL, `OtherInsured2Country` varchar(3) default NULL, `OtherInsured2Subdivision` varchar(3) default NULL, `OtherInsured2Policy` varchar(50) default NULL, `OtherInsured2DOB` varchar(10) default NULL, `OtherInsured2Sex` char(1) default NULL, `OtherInsured2Relationship` char(2) default NULL, `OtherInsured2OtherIDQual1` char(3) default NULL, `OtherInsured2OtherID1` varchar(50) default NULL, `OtherInsured2OtherIDQual2` char(3) default NULL, `OtherInsured2OtherID2` varchar(50) default NULL, `OtherInsured2OtherIDQual3` char(3) default NULL, `OtherInsured2OtherID3` varchar(50) default NULL, `OtherPayer2Responsibility` varchar(100) default NULL, `OtherPayer2Name` varchar(35) default NULL, `OtherPayer2IDQual` char(2) default NULL, `OtherPayer2ID` varchar(80) default NULL, `OtherPayer2Address1` varchar(55) default NULL, `OtherPayer2Address2` varchar(55) default NULL, `OtherPayer2City` varchar(30) default NULL, `OtherPayer2State` char(2) default NULL, `OtherPayer2Zip` varchar(15) default NULL, `OtherPayer2Paid` varchar(10) default NULL, `OtherPayer2GroupName` varchar(100) default NULL, `OtherPayer2GroupNo` varchar(100) default NULL, `OtherPayer2InsuranceType` char(2) default NULL, `OtherPayer2FilingIndicator` char(2) default NULL, `OtherPayer2AdjustmentReasonGroup` varchar(100) default NULL, `OtherPayer2AdjustmentReason` varchar(100) default NULL, `OtherPayer2AdjustmentAmount` varchar(100) default NULL, `OtherPayer2PaidDate` varchar(15) default NULL, `OtherPayer2InfoRelease` varchar(100) default NULL, `OtherPayer2BenefitAssign` varchar(100) default NULL, `OtherPayer2DocControlNo` varchar(100) default NULL, `OtherPayer2PriorAuth` varchar(100) default NULL, `OtherPayer2OtherIDQual1` char(3) default NULL, `OtherPayer2OtherID1` varchar(50) default NULL, `OtherPayer2OtherIDQual2` char(3) default NULL, `OtherPayer2OtherID2` varchar(50) default NULL, /*Claim Data*/ `ClaimNo` varchar(50) NOT NULL, `Amount` decimal(10,2) default NULL, `EstimatedAmountDue` decimal(10,2) default NULL, `PatientEstimatedAmountDue` decimal(10,2) default NULL, `PlaceOfService` char(2) default NULL, `ClaimFrequency` char(1) default NULL, `SubmitReason` char(1) default NULL, `ProviderSignature` char(1) default NULL, `ProviderAcceptsAssignment` char(1) default NULL, `BenefitAssignment` char(1) default NULL, `InfoReleaseCode` char(1) default NULL, `PatientSignatureCode` char(1) default NULL, `RelatedCauses` char(2) default NULL, `RelatedCauses2` char(2) default NULL, `RelatedCausesState` char(2) default NULL, `SpecialProgramCode` char(3) default NULL, `ProviderParticipation` char(1) default NULL, `EOBIndicator` char(1) default NULL, `DelayReasonCode` char(2) default NULL, `ServiceDateFrom` varchar(15) default NULL, `ServiceDateTo` varchar(15) default NULL, `OnsetDate` varchar(15) default NULL, `SimilarSymptomsDate` varchar(10) default NULL, `DisabilityBegin` varchar(10) default NULL, `DisabilityEnd` varchar(10) default NULL, `HospitalizationBegin` varchar(20) default NULL, `HospitalizationEnd` varchar(10) default NULL, `AccidentDate` varchar(20) default NULL, `LastMenstrualPeriod` varchar(15) default NULL, `RepricerReceivedDate` varchar(15) default NULL, `AdmissionDate` varchar(15) default NULL, `AdmissionHour` varchar(10) default NULL, `AdmissionType` char(1) default NULL, `AdmissionSource` char(1) default NULL, `DischargeHour` varchar(10) default NULL, `PatientStatus` char(2) default NULL, `CoveredDays` varchar(4) default NULL, `NonCoveredDays` varchar(4) default NULL, `COBDays` varchar(4) default NULL, `LifeTimeReserveDays` varchar(4) default NULL, `PriorAuthorization` varchar(50) default NULL, `ClearingHouseID` varchar(100) default NULL, `MedicalRecordNumber` varchar(50) default NULL, `MothersMedicalRecordNumber` varchar(50) default NULL, `ServiceAuthorizationException` varchar(50) default NULL, `ReferralNumber` varchar(50) default NULL, `PayerClaimControlNumber` varchar(50) default NULL, `RepricedClaimNumber` varchar(50) default NULL, `AdjustedRepricedClaimNumber` varchar(50) default NULL, `AutoAccidentState` varchar(50) default NULL, `OrigRefNo` varchar(50) default NULL, `ClaimType` char(1) default NULL, `TypeOfBill` varchar(20) default NULL, `Remark1` varchar(90) default NULL, `Remark2` varchar(90) default NULL, `Remark3` varchar(90) default NULL, `Remark4` varchar(90) default NULL, `K3_1` varchar(80) default NULL, `K3_2` varchar(80) default NULL, `K3_3` varchar(80) default NULL, `K3_4` varchar(80) default NULL, `K3_5` varchar(80) default NULL, `K3_6` varchar(80) default NULL, `K3_7` varchar(80) default NULL, `K3_8` varchar(80) default NULL, `K3_9` varchar(80) default NULL, `K3_10` varchar(80) default NULL, `OutsideLab` char(1) default NULL, `LabCharge` varchar(10) default NULL, `Test_Prod` char(1) default NULL, `ReportTypeCode1` varchar(100) default NULL, `ReportTransmissionCode1` varchar(100) default NULL, `AttachmentControlNumber1` varchar(100) default NULL, /*Dental Claims*/ `Predetermination` varchar(100) default NULL, `OrthodonticTotal` varchar(100) default NULL, `OrthodonticRemaining` varchar(100) default NULL, `OrthodonticYesNo` varchar(100) default NULL, `ToothStatus` varchar(100) default NULL, `AppliancePlacementDate` varchar(100) default NULL, /*Diagnosis Information*/ `AdmitDiagnosis` varchar(10) default NULL, `ECode` varchar(100) default NULL, `ECode2` varchar(100) default NULL, `ECode3` varchar(100) default NULL, `ECode4` varchar(100) default NULL, `ECode5` varchar(100) default NULL, `ECode6` varchar(100) default NULL, `ECode7` varchar(100) default NULL, `ECode8` varchar(100) default NULL, #`ECode9` varchar(100) default NULL, #`ECode10` varchar(100) default NULL, #`ECode11` varchar(100) default NULL, #`ECode12` varchar(100) default NULL, `ReasonForVisit` varchar(100) default NULL, `ReasonForVisit2` varchar(100) default NULL, `ReasonForVisit3` varchar(100) default NULL, `PrincipalDiagnosis` varchar(10) default NULL, `Diag2` varchar(10) default NULL, `Diag3` varchar(10) default NULL, `Diag4` varchar(10) default NULL, `Diag5` varchar(10) default NULL, `Diag6` varchar(10) default NULL, `Diag7` varchar(10) default NULL, `Diag8` varchar(10) default NULL, `Diag9` varchar(10) default NULL, `Diag10` varchar(10) default NULL, `Diag11` varchar(10) default NULL, `Diag12` varchar(10) default NULL, `Diag13` varchar(10) default NULL, `Diag14` varchar(10) default NULL, `Diag15` varchar(10) default NULL, `Diag16` varchar(10) default NULL, `Diag17` varchar(10) default NULL, `Diag18` varchar(10) default NULL, `Diag19` varchar(10) default NULL, `Diag20` varchar(10) default NULL, `Diag21` varchar(10) default NULL, `Diag22` varchar(10) default NULL, `Diag23` varchar(10) default NULL, `Diag24` varchar(10) default NULL, `Diag25` varchar(10) default NULL, `DRG` varchar(15) default NULL, /*Procedure Information*/ `PrincipalProcedure` varchar(10) default NULL, `PrincipalProcedureDate` datetime default NULL, `Proc2` varchar(50) default NULL, `Proc2Date` datetime default NULL, `Proc3` varchar(10) default NULL, `Proc3Date` datetime default NULL, `Proc4` varchar(10) default NULL, `Proc4Date` datetime default NULL, `Proc5` varchar(10) default NULL, `Proc5Date` datetime default NULL, `Proc6` varchar(10) default NULL, `Proc6Date` datetime default NULL, `Proc7` varchar(10) default NULL, `Proc7Date` datetime default NULL, `Proc8` varchar(10) default NULL, `Proc8Date` datetime default NULL, `Proc9` varchar(10) default NULL, `Proc9Date` datetime default NULL, `Proc10` varchar(10) default NULL, `Proc10Date` datetime default NULL, `Proc11` varchar(10) default NULL, `Proc11Date` datetime default NULL, `Proc12` varchar(10) default NULL, `Proc12Date` datetime default NULL, `Proc13` varchar(10) default NULL, `Proc13Date` datetime default NULL, `Proc14` varchar(10) default NULL, `Proc14Date` datetime default NULL, `Proc15` varchar(10) default NULL, `Proc15Date` datetime default NULL, `Proc16` varchar(10) default NULL, `Proc16Date` datetime default NULL, `Proc17` varchar(10) default NULL, `Proc17Date` datetime default NULL, `Proc18` varchar(10) default NULL, `Proc18Date` datetime default NULL, `Proc19` varchar(10) default NULL, `Proc19Date` datetime default NULL, `Proc20` varchar(10) default NULL, `Proc20Date` datetime default NULL, `Proc21` varchar(10) default NULL, `Proc21Date` datetime default NULL, `Proc22` varchar(10) default NULL, `Proc22Date` datetime default NULL, `Proc23` varchar(10) default NULL, `Proc23Date` datetime default NULL, `Proc24` varchar(10) default NULL, `Proc24Date` datetime default NULL, `Proc25` varchar(10) default NULL, `Proc25Date` datetime default NULL, /*Value Codes*/ `ValueCode1` char(3) default NULL, `ValueAmount1` varchar(10) default NULL, `ValueCode2` char(3) default NULL, `ValueAmount2` varchar(10) default NULL, `ValueCode3` char(3) default NULL, `ValueAmount3` varchar(10) default NULL, `ValueCode4` char(3) default NULL, `ValueAmount4` varchar(10) default NULL, `ValueCode5` char(3) default NULL, `ValueAmount5` varchar(10) default NULL, `ValueCode6` char(3) default NULL, `ValueAmount6` varchar(10) default NULL, `ValueCode7` varchar(10) default NULL, `ValueAmount7` varchar(10) default NULL, `ValueCode8` char(3) default NULL, `ValueAmount8` varchar(10) default NULL, `ValueCode9` char(3) default NULL, `ValueAmount9` varchar(10) default NULL, `ValueCode10` char(3) default NULL, `ValueAmount10` varchar(10) default NULL, `ValueCode11` char(3) default NULL, `ValueAmount11` varchar(10) default NULL, `ValueCode12` char(3) default NULL, `ValueAmount12` varchar(10) default NULL, #`ValueCode13` char(3) default NULL, #`ValueAmount13` varchar(10) default NULL, #`ValueCode14` char(3) default NULL, #`ValueAmount14` varchar(10) default NULL, #`ValueCode15` char(3) default NULL, #`ValueAmount15` varchar(10) default NULL, #`ValueCode16` char(3) default NULL, #`ValueAmount16` varchar(10) default NULL, #`ValueCode17` varchar(10) default NULL, #`ValueAmount17` varchar(10) default NULL, #`ValueCode18` char(3) default NULL, #`ValueAmount18` varchar(10) default NULL, #`ValueCode19` char(3) default NULL, #`ValueAmount19` varchar(10) default NULL, #`ValueCode20` char(3) default NULL, #`ValueAmount20` varchar(10) default NULL, #`ValueCode21` char(3) default NULL, #`ValueAmount21` varchar(10) default NULL, #`ValueCode22` char(3) default NULL, #`ValueAmount22` varchar(10) default NULL, #`ValueCode23` char(3) default NULL, #`ValueAmount23` varchar(10) default NULL, #`ValueCode24` char(3) default NULL, #`ValueAmount24` varchar(10) default NULL, /*Condition Codes*/ `ConditionCode1` varchar(30) default NULL, `ConditionCode2` varchar(30) default NULL, `ConditionCode3` varchar(30) default NULL, `ConditionCode4` varchar(30) default NULL, `ConditionCode5` varchar(30) default NULL, `ConditionCode6` varchar(30) default NULL, `ConditionCode7` varchar(30) default NULL, `ConditionCode8` varchar(30) default NULL, `ConditionCode9` varchar(30) default NULL, `ConditionCode10` varchar(30) default NULL, #`ConditionCode11` varchar(30) default NULL, #`ConditionCode12` varchar(30) default NULL, #`ConditionCode13` varchar(30) default NULL, #`ConditionCode14` varchar(30) default NULL, #`ConditionCode15` varchar(30) default NULL, #`ConditionCode16` varchar(30) default NULL, #`ConditionCode17` varchar(30) default NULL, #`ConditionCode18` varchar(30) default NULL, #`ConditionCode19` varchar(30) default NULL, #`ConditionCode20` varchar(30) default NULL, #`ConditionCode21` varchar(30) default NULL, #`ConditionCode22` varchar(30) default NULL, #`ConditionCode23` varchar(30) default NULL, #`ConditionCode24` varchar(30) default NULL, /*Occurrence Codes*/ `OccurranceCode1` char(3) default NULL, `OccurranceDate1` varchar(10) default NULL, `OccurranceCode2` char(3) default NULL, `OccurranceDate2` varchar(10) default NULL, `OccurranceCode3` char(3) default NULL, `OccurranceDate3` varchar(10) default NULL, `OccurranceCode4` char(3) default NULL, `OccurranceDate4` varchar(10) default NULL, `OccurranceCode5` char(3) default NULL, `OccurranceDate5` varchar(10) default NULL, `OccurranceCode6` char(3) default NULL, `OccurranceDate6` varchar(10) default NULL, `OccurranceCode7` char(3) default NULL, `OccurranceDate7` varchar(10) default NULL, `OccurranceCode8` char(3) default NULL, `OccurranceDate8` varchar(10) default NULL, #`OccurranceCode9` char(3) default NULL, #`OccurranceDate9` varchar(10) default NULL, #`OccurranceCode10` char(3) default NULL, #`OccurranceDate10` varchar(10) default NULL, #`OccurranceCode11` char(3) default NULL, #`OccurranceDate11` varchar(10) default NULL, #`OccurranceCode12` char(3) default NULL, #`OccurranceDate12` varchar(10) default NULL, #`OccurranceCode13` char(3) default NULL, #`OccurranceDate13` varchar(10) default NULL, #`OccurranceCode14` char(3) default NULL, #`OccurranceDate14` varchar(10) default NULL, #`OccurranceCode15` char(3) default NULL, #`OccurranceDate15` varchar(10) default NULL, #`OccurranceCode16` char(3) default NULL, #`OccurranceDate16` varchar(10) default NULL, #`OccurranceCode17` char(3) default NULL, #`OccurranceDate17` varchar(10) default NULL, #`OccurranceCode18` char(3) default NULL, #`OccurranceDate18` varchar(10) default NULL, #`OccurranceCode19` char(3) default NULL, #`OccurranceDate19` varchar(10) default NULL, #`OccurranceCode20` char(3) default NULL, #`OccurranceDate20` varchar(10) default NULL, #`OccurranceCode21` char(3) default NULL, #`OccurranceDate21` varchar(10) default NULL, #`OccurranceCode22` char(3) default NULL, #`OccurranceDate22` varchar(10) default NULL, #`OccurranceCode23` char(3) default NULL, #`OccurranceDate23` varchar(10) default NULL, #`OccurranceCode24` char(3) default NULL, #`OccurranceDate24` varchar(10) default NULL, `OccurranceSpanCode1` char(3) default NULL, `OccurranceSpanFrom1` varchar(10) default NULL, `OccurranceSpanTo1` varchar(10) default NULL, `OccurranceSpanCode2` char(3) default NULL, `OccurranceSpanFrom2` varchar(10) default NULL, `OccurranceSpanTo2` varchar(10) default NULL, `OccurranceSpanCode3` char(3) default NULL, `OccurranceSpanFrom3` varchar(10) default NULL, `OccurranceSpanTo3` varchar(10) default NULL, `OccurranceSpanCode4` char(3) default NULL, `OccurranceSpanFrom4` varchar(10) default NULL, `OccurranceSpanTo4` varchar(10) default NULL, /* CR1 */ `PatientWeight` varchar(10) NULL, `AmbulanceTransportCode` char(1) NULL, `AmbulanceTransportReasonCode` char(1) NULL, `TransportDistance` varchar(15) NULL, `RoundTripPurposeDescription` varchar(80) NULL, `StretcherPurposeDescription` varchar(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*/ `RepricingMethodology` varchar(3) NULL, `RepricedAmount` decimal(10,2) NULL, `SavingsAmount` decimal(10,2) NULL, `RepricerID` varchar(30) NULL, `RepricingRate` varchar(9) NULL, `APG_Code` varchar(30) NULL, `APG_Amount` decimal(10,2) NULL, `ApprovedRevenueCode` varchar (48) NULL, `ApprovedProcedureCode` varchar (48) NULL, `ApprovedUnitCode` char(2) NULL, `ApprovedUnits` bigint NULL, `RejectReason` varchar(3) NULL, `ComplianceCode` varchar(2) NULL, `ExceptionCode` varchar(2) NULL, /*SQL To EDI*/ #`ClaimAction` char(1) NULL, #`ErrorMessage` varchar(1000) NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DetailCREATE TABLE `edi_ClaimDetail`( `ID` int(11) NOT NULL auto_increment, `ClaimID` int(11) NOT NULL, `LineNumber` decimal(18, 0) NULL, `LineID` varchar(50) NULL, `ServiceDateFrom` datetime NULL, `ServiceDateTo` date NULL, `AssessmentDate` date NULL, `FacilityCode` char(2) NULL, `RevenueCode` varchar(48) NULL, `ProcedureCode` varchar(48) NULL, `Amount` decimal(10,2) NULL, `Unit` varchar(50) NULL, `Quantity` float NULL, `UnitRate` decimal(10,2) NULL, `NonCovered` decimal(10,2) NULL, `Paid` decimal(10,2) NULL, `PlaceOfService` char(3) NULL, `Modifier1` char(3) NULL, `Modifier2` char(3) NULL, `Modifier3` char(3) NULL, `Modifier4` char(3) NULL, `ProcedureDescription` varchar(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` varchar(10) NULL, `EmergencyIndicator` char(1) NULL, `ServiceTax` decimal(10,2) NULL, `FacilityTax` decimal(10,2) NULL, `SalesTax` decimal(10,2) NULL, `ApprovedAmount` decimal(10,2) NULL, `LineK3_01` varchar(80) NULL, `LineK3_02` varchar(80) NULL, `LineK3_03` varchar(80) NULL, `LineK3_04` varchar(80) NULL, `LineK3_05` varchar(80) NULL, `LineK3_06` varchar(80) NULL, `LineK3_07` varchar(80) NULL, `LineK3_08` varchar(80) NULL, `LineK3_09` varchar(80) NULL, `LineK3_10` varchar(80) NULL, `Remark` varchar(100) NULL, `AmbulancePatientCount` bigint NULL, /****PWK****/ `ReportType` char(2) NULL, `ReportTransmission` char(2) NULL, `AttachmentControlNumber` varchar(80) NULL, /*HCP*/ `RepricingMethodology` varchar(3) NULL, `RepricedAmount` decimal(10,2) NULL, `SavingsAmount` decimal(10,2) NULL, `RepricerID` varchar(30) NULL, `RepricingRate` varchar(9) NULL, `APG_Code` varchar(30) NULL, `APG_Amount` decimal(10,2) NULL, `ApprovedRevenueCode` varchar (48) NULL, `ApprovedProcedureCode` varchar (48) NULL, `ApprovedUnitCode` char(2) NULL, `ApprovedUnits` bigint NULL, `RejectReason` varchar(3) NULL, `ComplianceCode` varchar(2) NULL, `ExceptionCode` varchar(2) NULL, /* Drugs */ `DrugCode` varchar(48) NULL, `DrugUnitPrice` decimal(10,2) NULL, `DrugUnitCode` char(2) NULL, `DrugUnits` double NULL, `PrescriptionNumber` varchar(30) NULL, /* CR1 */ `PatientWeight` varchar(10) NULL, `AmbulanceTransportCode` char(1) NULL, `AmbulanceTransportReasonCode` char(1) NULL, `TransportDistance` varchar(15) NULL, `RoundTripPurposeDescription` varchar(80) NULL, `StretcherPurposeDescription` varchar(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` varchar(35) NULL, `AttendingProviderFirst` varchar(25) NULL, `AttendingProviderMiddle` varchar(25) NULL, `AttendingProviderSuffix` varchar(10) NULL, `AttendingProviderIDQual` char(2) NULL, `AttendingProviderID` varchar(35) NULL, `AttendingProviderOtherIDQual` char(2) NULL, `AttendingProviderOtherID` varchar(30) NULL, /* Operating Provider */ `OperatingProviderLast` varchar(35) NULL, `OperatingProviderFirst` varchar(25) NULL, `OperatingProviderMiddle` varchar(25) NULL, `OperatingProviderSuffix` varchar(10) NULL, `OperatingProviderIDQual` char(2) NULL, `OperatingProviderID` varchar(35) NULL, `OperatingProviderOtherIDQual` char(2) NULL, `OperatingProviderOtherID` varchar(30) NULL, /* Other Provider */ `OtherProviderLast` varchar(35) NULL, `OtherProviderFirst` varchar(25) NULL, `OtherProviderMiddle` varchar(25) NULL, `OtherProviderSuffix` varchar(10) NULL, `OtherProviderIDQual` char(2) NULL, `OtherProviderID` varchar(35) NULL, `OtherProviderOtherIDQual` char(2) NULL, `OtherProviderOtherID` varchar(30) NULL, /* Rendering Provider */ `RenderingProviderLast` varchar(35) NULL, `RenderingProviderFirst` varchar(25) NULL, `RenderingProviderMiddle` varchar(25) NULL, `RenderingProviderSuffix` varchar(10) NULL, `RenderingProviderIDQual` char(2) NULL, `RenderingProviderID` varchar(35) NULL, `RenderingProviderOtherIDQual` char(2) NULL, `RenderingProviderOtherID` varchar(30) NULL, /* PurchasedService Provider */ `PurchasedServiceProviderLast` varchar(35) NULL, `PurchasedServiceProviderFirst` varchar(25) NULL, `PurchasedServiceProviderMiddle` varchar(25) NULL, `PurchasedServiceProviderSuffix` varchar(10) NULL, `PurchasedServiceProviderIDQual` char(2) NULL, `PurchasedServiceProviderID` varchar(35) NULL, `PurchasedServiceProviderOtherIDQual` char(2) NULL, `PurchasedServiceProviderOtherID` varchar(30) NULL, /* Facility Provider */ `FacilityName` varchar(35) NULL, `FacilityIDQual` char(2) NULL, `FacilityID` varchar(35) NULL, `FacilityAddress1` varchar(25) NULL, `FacilityAddress2` varchar(25) NULL, `FacilityCity` varchar(30) NULL, `FacilityState` char(2) NULL, `FacilityZip` varchar(15) NULL, `FacilityOtherIDQual` char(2) NULL, `FacilityOtherID` varchar(30) NULL, /* Supervising Provider */ `SupervisingProviderLast` varchar(35) NULL, `SupervisingProviderFirst` varchar(25) NULL, `SupervisingProviderMiddle` varchar(25) NULL, `SupervisingProviderSuffix` varchar(10) NULL, `SupervisingProviderIDQual` char(2) NULL, `SupervisingProviderID` varchar(35) NULL, `SupervisingProviderOtherIDQual` char(2) NULL, `SupervisingProviderOtherID` varchar(30) NULL, /* Ordering Provider */ `OrderingProviderLast` varchar(35) NULL, `OrderingProviderFirst` varchar(25) NULL, `OrderingProviderMiddle` varchar(25) NULL, `OrderingProviderSuffix` varchar(10) NULL, `OrderingProviderIDQual` char(2) NULL, `OrderingProviderID` varchar(35) NULL, `OrderingProviderOtherIDQual` char(2) NULL, `OrderingProviderOtherID` varchar(30) NULL, /* Referring Provider */ `ReferringProviderLast` varchar(35) NULL, `ReferringProviderFirst` varchar(25) NULL, `ReferringProviderMiddle` varchar(25) NULL, `ReferringProviderSuffix` varchar(10) NULL, `ReferringProviderIDQual` char(2) NULL, `ReferringProviderID` varchar(35) NULL, `ReferringProviderOtherIDQual` char(2) NULL, `ReferringProviderOtherID` varchar(30) NULL, /*COB 1 */ `OtherPayer1ID` varchar(30) NULL, `OtherPayer1Paid` decimal(10,2) NULL, `OtherPayer1PaidProcedure` varchar(30) NULL, `OtherPayer1PaidRevenueCode` varchar(30) NULL, `OtherPayer1PaidQuantity` bigint NULL, `OtherPayer1BundledLine` int NULL, `OtherPayer1AdjustmentReasonGroup1` char(2) NULL, `OtherPayer1AdjustmentReason1` varchar(5) NULL, `OtherPayer1AdjustmentAmount1` decimal(10,2) NULL, `OtherPayer1AdjustmentQuantity1` bigint NULL, `OtherPayer1AdjustmentReasonGroup2` char(2) NULL, `OtherPayer1AdjustmentReason2` varchar(5) NULL, `OtherPayer1AdjustmentAmount2` decimal(10,2) NULL, `OtherPayer1AdjustmentQuantity2` bigint NULL, `OtherPayer1AdjustmentReasonGroup3` char(2) NULL, `OtherPayer1AdjustmentReason3` varchar(5) NULL, `OtherPayer1AdjustmentAmount3` decimal(10,2) NULL, `OtherPayer1AdjustmentQuantity3` bigint NULL, `OtherPayer1AdjustmentReasonGroup4` char(2) NULL, `OtherPayer1AdjustmentReason4` varchar(5) NULL, `OtherPayer1AdjustmentAmount4` decimal(10,2) NULL, `OtherPayer1AdjustmentQuantity4` bigint NULL, #`OtherPayer1AdjustmentReasonGroup5Plus` varchar(77) NULL, #`OtherPayer1AdjustmentReason5Plus` varchar(155) NULL, #`OtherPayer1AdjustmentAmount5Plus` varchar(493) NULL, #`OtherPayer1AdjustmentQuantity5Plus` varchar(415) NULL, `OtherPayer1PaidDate` varchar(15) default NULL, /*COB 2 */ `OtherPayer2ID` varchar(30) NULL, `OtherPayer2Paid` decimal(10,2) NULL, `OtherPayer2PaidProcedure` varchar(30) NULL, `OtherPayer2PaidRevenueCode` varchar(30) NULL, `OtherPayer2PaidQuantity` bigint NULL, `OtherPayer2BundledLine` int NULL, `OtherPayer2AdjustmentReasonGroup1` char(2) NULL, `OtherPayer2AdjustmentReason1` varchar(5) NULL, `OtherPayer2AdjustmentAmount1` decimal(10,2) NULL, `OtherPayer2AdjustmentQuantity1` bigint NULL, `OtherPayer2AdjustmentReasonGroup2` char(2) NULL, `OtherPayer2AdjustmentReason2` varchar(5) NULL, `OtherPayer2AdjustmentAmount2` decimal(10,2) NULL, `OtherPayer2AdjustmentQuantity2` bigint NULL, `OtherPayer2AdjustmentReasonGroup3` char(2) NULL, `OtherPayer2AdjustmentReason3` varchar(5) NULL, `OtherPayer2AdjustmentAmount3` decimal(10,2) NULL, `OtherPayer2AdjustmentQuantity3` bigint NULL, `OtherPayer2AdjustmentReasonGroup4` char(2) NULL, `OtherPayer2AdjustmentReason4` varchar(5) NULL, `OtherPayer2AdjustmentAmount4` decimal(10,2) NULL, `OtherPayer2AdjustmentQuantity4` bigint NULL, #`OtherPayer2AdjustmentReasonGroup5Plus` varchar(77) NULL, #`OtherPayer2AdjustmentReason5Plus` varchar(155) NULL, #`OtherPayer2AdjustmentAmount5Plus` varchar(493) NULL, #`OtherPayer2AdjustmentQuantity5Plus` varchar(415) NULL, `OtherPayer2PaidDate` date NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|