Creating Tables
Before Creating Tables...
The scripts that are supplied with the HIPAA Claim Master will generate every field that is available for import. These are more than 260 fields in the claim header. You will not need all these fields. You can edit the scripts to only use the fields that your adjudication system requires. System performance will be better if you only select the needed fields.
Not all databases are supported. Most likely only Microsoft SQL server and MYSQL scripts are supplied. And even here different versions of the database behave differently. If you have database expertise, please modify the scripts accordingly. We invite you to submit improvements and requests.
If you encounter persistent errors with the script, we recommend you to work with the scripts within the database environment. SQL Server Enterprise Manager or SQLyog for example have an SQL query analyzer that helps you tweak the scripts until they run.
Also, feel free to contact us if you have any problems.
Â
How to Create Tables
In order to facilitate the development, HIPAA Claim Master allows you to create the staging tables for your database where HIPAA Claim Master will export the claim data.
Before data export, you need to create the tables through SQL table statements. You can do it using the built-in function of the HIPAA Claim Master. Creating these tables is a quite intimidating task. Once you have your connection defined and tested, follow the instructions below.
Note: The tables are created via the "Drop Table" and "Create Table" commands. The scripts come from two files: EDI_Claims.sql and EDI_ClaimDetail.sql that are distributed with the application. Please, contact HIPAAsuite if you need help with the database setup.
| 1. | Select the Data Base   Create SQL Table in the main menu.  The "Create SQL Table" menu |
| 2. | The following window will be displayed. The "Create SQL Tables" window |
| 3. | Define the following options: |
Database
| • | Microsoft SQL Server |
| • | MySQL/Genetic |
| • | Oracle |
Environment
| • | Live Database |
| • | Test Database |
Scripts
On the following tabs there are SQL scripts used to create the appropriate tables:
| • | Claim Header Tables Script |
| • | Claim Detail Table Script |
Tip: You can modify the scripts so that they run on your specific database and to conform to your database's specific SQL syntax. For each script, click "Save."
The "Save" button
For each script, click "Execute Script" to create the corresponding table in the database.
Warning: Executing the scripts will wipe out any previous tables that you created and you will loose all data.
The "Execute Script" button
Once the table has been created successfully, you will see the following notification:
The success message
No questions yet.
Microsoft SQL Server Script to Create Tables
Header
CREATE TABLE [dbo].[EDI_Claims](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
/*File Level Data*/
[Filename] [varchar](100) NULL,
[Version] [varchar](12) NULL,
[ImageFilePath] [varchar](300) NULL,
[ImageFilename] [varchar](100) NULL,
[TradingPartnerIDType] [char](2) NULL,
[TradingPartnerID] [varchar](15) NOT NULL,
[TransactionDate] [date] NULL,
[ReceiveDate] [date] NULL,
[SubmitterName] [varchar](60) NULL,
[SubmitterID] [varchar](30) NULL,
[SubmitterContact] [varchar](60) NULL,
[SubmitterTel] [varchar](20) NULL,
[SubmitterTelExt] [varchar](10) NULL,
[SubmitterFax] [varchar](20) NULL,
[SubmitterEmail] [varchar](80) NULL,
[ReceiverName] [varchar](60) NULL,
[ReceiverID] [varchar](30) NULL,
[TransactionType] [char](2) NULL,
/*Billing Provider*/
[FedTaxID] [varchar](15) NULL,
[BillProvIDType] [char](2) NULL,
[BillProvID] [varchar](80) NULL,
[BillProvNPI] [varchar](12) NULL,
[BillProvLast] [varchar](60) NULL,
[BillProvFirst] [varchar](35) NULL,
[BillProvMiddle] [varchar](25) NULL,
[BillProvSuffix] [varchar](10) NULL,
[BillProvSpecialty] [varchar](10) NULL,
[BillProvAddress] [varchar](55) NULL,
[BillProvAddress2] [varchar](55) NULL,
[BillProvCity] [varchar](30) NULL,
[BillProvState] [char](2) NULL,
[BillProvZip] [varchar](15) NULL,
[BillProvCountry] [varchar](3) NULL,
[BillProvSubdivision] [varchar](3) NULL,
[BillProvContact] [varchar](60) NULL,
[BillProvTel] [varchar](80) NULL,
[BillProvTelExt] [varchar](80) NULL,
[BillProvFax] [varchar](80) NULL,
[BillProvEmail] [varchar](80) NULL,
[BillProvOtherIDQual1] [char](2) NULL,
[BillProvOtherID1] [varchar](30) NULL,
[BillProvOtherIDQual2] [char](2) NULL,
[BillProvOtherID2] [varchar](30) NULL,
[BillProvOtherIDQual3] [char](2) NULL,
[BillProvOtherID3] [varchar](30) NULL,
[BillProvOtherIDQual4] [char](2) NULL,
[BillProvOtherID4] [varchar](30) NULL,
[BillProvOtherIDQual5] [char](2) NULL,
[BillProvOtherID5] [varchar](30) NULL,
/*Pay-to Provider*/
[PayToProvIDType] [char](2) NULL,
[PayToProvID] [varchar](30) NULL,
[PayToProvTaxID] [varchar](12) NULL,
[PayToProvNPI] [varchar](12) NULL,
[PayToProvLast] [varchar](60) NULL,
[PayToProvFirst] [varchar](35) NULL,
[PayToProvMiddle] [varchar](25) NULL,
[PayToProvSuffix] [varchar](10) NULL,
[PayToProvSpecialty] [varchar](10) NULL,
[PayToProvAddress] [varchar](55) NULL,
[PayToProvAddress2] [varchar](55) NULL,
[PayToProvCity] [varchar](30) NULL,
[PayToProvState] [char](2) NULL,
[PayToProvZip] [varchar](15) NULL,
/*Pay-to Payer*/
[PayToPayerIDType] [char](2) NULL,
[PayToPayerID] [varchar](80) NULL,
[PayToPayerTaxID] [varchar](12) NULL,
[PayToPayerLast] [varchar](60) NULL,
[PayToPayerFirst] [varchar](35) NULL,
[PayToPayerMiddle] [varchar](25) NULL,
[PayToPayerSuffix] [varchar](10) NULL,
[PayToPayerAddress] [varchar](55) NULL,
[PayToPayerAddress2] [varchar](55) NULL,
[PayToPayerCity] [varchar](30) NULL,
[PayToPayerState] [char](2) NULL,
[PayToPayerZip] [varchar](15) NULL,
[PayToPayerOtherIDQual] [char](2) NULL,
[PayToPayerOtherID] [varchar](30) NULL,
/*Subscriber*/
[SubscriberLast] [varchar](60) NULL,
[SubscriberFirst] [varchar](35) NULL,
[SubscriberMiddle] [varchar](25) NULL,
[SubscriberSuffix] [varchar](10) NULL,
[SubscriberIDType] [char](2) NULL,
[SubscriberID] [varchar](30) NULL,
[SubscriberAddress] [varchar](55) NULL,
[SubscriberAddress2] [varchar](55) NULL,
[SubscriberCity] [varchar](30) NULL,
[SubscriberState] [char](2) NULL,
[SubscriberZip] [varchar](15) NULL,
[SubscriberCountry] [varchar](3) NULL,
[SubscriberLocation] [varchar](30) NULL,
[SubscriberSubdivision] [varchar](3) NULL,
[SubscriberDOB] [varchar](10) NULL,
[SubscriberSex] [char](1) NULL,
[SubscriberEthnicity] [varchar](75) NULL,
[SubscriberMaritalStatus] [char](1) NULL,
[SubscriberCollectionMethod] [varchar](30) NULL,
[SubscriberSSN] [varchar](12) NULL,
[SubscriberAgencyClaimNo] [varchar](50) NULL,
[SubscriberMemberID] [varchar](50) NULL,
[SubscriberPersonalID] [varchar](50) NULL,
[SubscriberContact] [varchar](60) NULL,
[SubscriberTel] [varchar](15) NULL,
[SubscriberTelExt] [varchar](10) NULL,
/*Destination Payer*/
[PayerName] [varchar](60) NULL,
[PayerIDType] [char](2) NULL,
[PayerID] [varchar](30) NULL,
[PayerAddress] [varchar](55) NULL,
[PayerAddress2] [varchar](55) NULL,
[PayerCity] [varchar](30) NULL,
[PayerState] [char](2) NULL,
[PayerZip] [varchar](15) NULL,
[PayerResponsibility] [char](1) NULL,
[PayerOtherIDQual1] [char](2) NULL,
[PayerOtherID1] [varchar](30) NULL,
[PayerOtherIDQual2] [char](2) NULL,
[PayerOtherID2] [varchar](30) NULL,
[PayerOtherIDQual3] [char](2) NULL,
[PayerOtherID3] [varchar](30) NULL,
[GroupNo] [varchar](50) NULL,
[GroupName] [varchar](60) NULL,
[InsuranceType] [char](2) NULL,
[FilingIndicator] [char](2) NULL,
[COBIndicator] [char](1) NULL,
/*Responsible*/
[ResponsibleLast] [varchar](60) NULL,
[ResponsibleFirst] [varchar](35) NULL,
[ResponsibleMiddle] [varchar](25) NULL,
[ResponsibleSuffix] [varchar](10) NULL,
[ResponsibleAddress] [varchar](55) NULL,
[ResponsibleAddress2] [varchar](55) NULL,
[ResponsibleCity] [varchar](30) NULL,
[ResponsibleState] [char](2) NULL,
[ResponsibleZip] [varchar](15) NULL,
[ResponsibleIDType] [char](2) NULL,
[ResponsibleID] [varchar](80) NULL,
/*Patient*/
[PatientLast] [varchar](60) NULL,
[PatientFirst] [varchar](35) NULL,
[PatientMiddle] [varchar](25) NULL,
[PatientSuffix] [varchar](10) NULL,
[PatientAddress] [varchar](55) NULL,
[PatientAddress2] [varchar](55) NULL,
[PatientCity] [varchar](30) NULL,
[PatientState] [char](2) NULL,
[PatientZip] [varchar](15) NULL,
[PatientCountry] [varchar](3) NULL,
[PatientLocation] [varchar](30) NULL,
[PatientSubdivision] [varchar](3) NULL,
[PatientIDType] [char](2) NULL,
[PatientID] [varchar](30) NULL,
[PatientSSN] [varchar](12) NULL,
[PatientMemberID] [varchar](50) NULL,
[PatientPersonalID] [varchar](50) NULL,
[PatientInsurancePolicy] [varchar](50) NULL,
[PatientDOB] [varchar](50) NULL,
[PatientSex] [char](1) NULL,
[PatientEthnicity] [varchar](75) NULL,
[PatientMaritalStatus] [char](1) NULL,
[PatientCollectionMethod] [varchar](30) NULL,
[PatientRelationship] [char](2) NULL,
[PatientPaid] [varchar](10) NULL,
[PregnancyIndicator] [char](1) NULL,
/*Rendering Provider*/
[RendProvIDType] [char](2) NULL,
[RendProvID] [varchar](30) NULL,
[RendProvNPI] [varchar](12) NULL,
[RendProvTaxID] [varchar](12) NULL,
[RendProvLast] [varchar](60) NULL,
[RendProvFirst] [varchar](35) NULL,
[RendProvMiddle] [varchar](25) NULL,
[RendProvSuffix] [varchar](10) NULL,
[RendProvSpecialty] [varchar](10) NULL,
[RendProvOtherIDQual1] [char](2) NULL,
[RendProvOtherID1] [varchar](30) NULL,
[RendProvOtherIDQual2] [char](2) NULL,
[RendProvOtherID2] [varchar](30) NULL,
[RendProvOtherIDQual3] [char](2) NULL,
[RendProvOtherID3] [varchar](30) NULL,
/*Attending Provider*/
[AttendProvLast] [varchar](60) NULL,
[AttendProvFirst] [varchar](35) NULL,
[AttendProvMiddle] [varchar](25) NULL,
[AttendProvSuffix] [varchar](10) NULL,
[AttendProvIDType] [char](2) NULL,
[AttendProvID] [varchar](30) NULL,
[AttendProvTaxID] [varchar](12) NULL,
[AttendProvNPI] [varchar](12) NULL,
[AttendProvOtherIDQual1] [char](2) NULL,
[AttendProvOtherID1] [varchar](30) NULL,
[AttendProvSpecialty] [varchar](10) NULL,
/*Operating Provider*/
[OperatingProvLast] [varchar](60) NULL,
[OperatingProvFirst] [varchar](35) NULL,
[OperatingProvMiddle] [varchar](25) NULL,
[OperatingProvSuffix] [varchar](10) NULL,
[OperatingProvIDType] [char](2) NULL,
[OperatingProvID] [varchar](30) NULL,
[OperatingProvNPI] [varchar](12) NULL,
[OperatingProvTaxID] [varchar](12) NULL,
[OperatingProvOtherIDQual1] [char](2) NULL,
[OperatingProvOtherID1] [varchar](30) NULL,
[OperatingProvSpecialty] [varchar](10) NULL,
/*Other Provider*/
[OtherProvLast] [varchar](60) NULL,
[OtherProvFirst] [varchar](35) NULL,
[OtherProvMiddle] [varchar](25) NULL,
[OtherProvSuffix] [varchar](10) NULL,
[OtherProvIDType] [char](2) NULL,
[OtherProvID] [varchar](30) NULL,
[OtherProvTaxID] [varchar](12) NULL,
[OtherProvNPI] [varchar](12) NULL,
[OtherProvOtherIDQual1] [char](2) NULL,
[OtherProvOtherID1] [varchar](30) NULL,
[OtherProvSpecialty] [varchar](50) NULL,
/*Facility*/
[FacilityType] [char](2) NULL,
[FacilityIDType] [char](2) NULL,
[FacilityID] [varchar](80) NULL,
[FacilityNPI] [varchar](12) NULL,
[FacilityTaxID] [varchar](12) NULL,
[FacilityOtherIDQual1] [char](2) NULL,
[FacilityOtherID1] [varchar](30) NULL,
[FacilityName] [varchar](80) NULL,
[FacilityAddress] [varchar](55) NULL,
[FacilityAddress2] [varchar](55) NULL,
[FacilityCity] [varchar](30) NULL,
[FacilitySpecialty] [varchar](50) NULL,
[FacilityState] [char](2) NULL,
[FacilityZip] [varchar](15) NULL,
/*Referring Provider*/
[RefProvLast] [varchar](60) NULL,
[RefProvFirst] [varchar](35) NULL,
[RefProvMiddle] [varchar](25) NULL,
[RefProvSuffix] [varchar](10) NULL,
[RefProvIDType] [char](2) NULL,
[RefProvID] [varchar](80) NULL,
[RefProvTaxID] [varchar](12) NULL,
[RefProvNPI] [varchar](12) NULL,
[RefProvOtherIDQual1] [char](2) NULL,
[RefProvOtherID1] [varchar](300) NULL,
[RefProvSpecialty] [varchar](20) NULL,
/*Supervising Provider*/
[SupervProvLast] [varchar](60) NULL,
[SupervProvFirst] [varchar](35) NULL,
[SupervProvMiddle] [varchar](25) NULL,
[SupervProvSuffix] [varchar](10) NULL,
[SupervProvIDType] [char](2) NULL,
[SupervProvID] [varchar](30) NULL,
[SupervProvTaxID] [varchar](12) NULL,
[SupervProvNPI] [varchar](12) NULL,
[SupervProvOtherIDQual1] [char](2) NULL,
[SupervProvOtherID1] [varchar](50) NULL,
[SupervProvSpecialty] [varchar](20) NULL,
/*Assistant Surgeon*/
[AssistSurgLast] [varchar](60) NULL,
[AssistSurgFirst] [varchar](35) NULL,
[AssistSurgMiddle] [varchar](25) NULL,
[AssistSurgSuffix] [varchar](10) NULL,
[AssistSurgIDType] [char](2) NULL,
[AssistSurgID] [varchar](30) NULL,
[AssistSurgTaxID] [varchar](12) NULL,
[AssistSurgNPI] [varchar](12) NULL,
[AssistSurgOtherIDQual1] [varchar](3) NULL,
[AssistSurgOtherID1] [varchar](50) NULL,
[AssistSurgSpecialty] [varchar](10) NULL,
/*Ambulance Pick-up Location*/
[PickUpAddress] [varchar](55) NULL,
[PickUpAddress2] [varchar](55) NULL,
[PickUpCity] [varchar](30) NULL,
[PickUpState] [char](2) NULL,
[PickUpZip] [varchar](15) NULL,
/*Ambulance Drop-off Location*/
[DropOffName] [varchar](80) NULL,
[DropOffAddress] [varchar](55) NULL,
[DropOffAddress2] [varchar](55) NULL,
[DropOffCity] [varchar](30) NULL,
[DropOffState] [char](2) NULL,
[DropOffZip] [varchar](15) NULL,
/*COB Payer 1*/
[OtherInsuredLast] [varchar](60) NULL,
[OtherInsuredFirst] [varchar](35) NULL,
[OtherInsuredMiddle] [varchar](25) NULL,
[OtherInsuredSuffix] [varchar](10) NULL,
[OtherInsuredIDQual] [char](2) NULL,
[OtherInsuredID] [varchar](30) NULL,
[OtherInsuredAddress] [varchar](55) NULL,
[OtherInsuredAddress2] [varchar](55) NULL,
[OtherInsuredCity] [varchar](30) NULL,
[OtherInsuredState] [char](2) NULL,
[OtherInsuredZip] [varchar](15) NULL,
[OtherInsuredCountry] [varchar](3) NULL,
[OtherInsuredSubdivision] [varchar](3) NULL,
[OtherInsuredPolicy] [varchar](50) NULL,
[OtherInsuredDOB] [varchar](10) NULL,
[OtherInsuredSex] [char](1) NULL,
[OtherInsuredRelationship] [char](2) NULL,
[OtherInsuredOtherIDQual1] [char](3) NULL,
[OtherInsuredOtherID1] [varchar](50) NULL,
[OtherInsuredOtherIDQual2] [char](3) NULL,
[OtherInsuredOtherID2] [varchar](50) NULL,
[OtherInsuredOtherIDQual3] [char](3) NULL,
[OtherInsuredOtherID3] [varchar](50) NULL,
[OtherPayerResponsibility] [char](1) NULL,
[OtherPayerName] [varchar](35) NULL,
[OtherPayerIDQual] [char](2) NULL,
[OtherPayerID] [varchar](30) NULL,
[OtherPayerAddress1] [varchar](55) NULL,
[OtherPayerAddress2] [varchar](55) NULL,
[OtherPayerCity] [varchar](30) NULL,
[OtherPayerState] [char](2) NULL,
[OtherPayerZip] [varchar](15) NULL,
[OtherPayerPaid] [varchar](10) NULL,
[OtherPayerGroupName] [varchar](60) NULL,
[OtherPayerGroupNo] [varchar](50) NULL,
[OtherPayerInsuranceType] [char](2) NULL,
[OtherPayerFilingIndicator] [char](2) NULL,
[OtherPayerAdjustmentReasonGroup] [varchar](20) NULL,
[OtherPayerAdjustmentReason] [varchar](50) NULL,
[OtherPayerAdjustmentAmount] [varchar](120) NULL,
[OtherPayerPaidDate] [date] NULL,
[OtherPayerInfoRelease] [char](1) NULL,
[OtherPayerBenefitAssign] [char](1) NULL,
[OtherPayerDocControlNo] [varchar](50) NULL,
[OtherPayerPriorAuth] [varchar](50) NULL,
[OtherPayerOtherIDQual1] [char](3) NULL,
[OtherPayerOtherID1] [varchar](50) NULL,
[OtherPayerOtherIDQual2] [char](3) NULL,
[OtherPayerOtherID2] [varchar](50) NULL,
/*COB Payer 2*/
[OtherInsured2Last] [varchar](60) NULL,
[OtherInsured2First] [varchar](35) NULL,
[OtherInsured2Middle] [varchar](25) NULL,
[OtherInsured2Suffix] [varchar](10) NULL,
[OtherInsured2IDQual] [char](2) NULL,
[OtherInsured2ID] [varchar](30) NULL,
[OtherInsured2Address] [varchar](55) NULL,
[OtherInsured2Address2] [varchar](55) NULL,
[OtherInsured2City] [varchar](30) NULL,
[OtherInsured2State] [char](2) NULL,
[OtherInsured2Zip] [varchar](15) NULL,
[OtherInsured2Country] [varchar](3) NULL,
[OtherInsured2Subdivision] [varchar](3) NULL,
[OtherInsured2Policy] [varchar](50) NULL,
[OtherInsured2DOB] [varchar](10) NULL,
[OtherInsured2Sex] [char](1) NULL,
[OtherInsured2Relationship] [char](2) NULL,
[OtherInsured2OtherIDQual1] [char](3) NULL,
[OtherInsured2OtherID1] [varchar](50) NULL,
[OtherInsured2OtherIDQual2] [char](3) NULL,
[OtherInsured2OtherID2] [varchar](50) NULL,
[OtherInsured2OtherIDQual3] [char](3) NULL,
[OtherInsured2OtherID3] [varchar](50) NULL,
[OtherPayer2Responsibility] [char](1) NULL,
[OtherPayer2Name] [varchar](35) NULL,
[OtherPayer2IDQual] [char](2) NULL,
[OtherPayer2ID] [varchar](80) NULL,
[OtherPayer2Address1] [varchar](55) NULL,
[OtherPayer2Address2] [varchar](55) NULL,
[OtherPayer2City] [varchar](30) NULL,
[OtherPayer2State] [char](2) NULL,
[OtherPayer2Zip] [varchar](15) NULL,
[OtherPayer2Paid] [varchar](10) NULL,
[OtherPayer2GroupName] [varchar](60) NULL,
[OtherPayer2GroupNo] [varchar](50) NULL,
[OtherPayer2InsuranceType] [char](2) NULL,
[OtherPayer2FilingIndicator] [char](2) NULL,
[OtherPayer2AdjustmentReasonGroup] [char](20) NULL,
[OtherPayer2AdjustmentReason] [varchar](50) NULL,
[OtherPayer2AdjustmentAmount] [varchar](120) NULL,
[OtherPayer2PaidDate] [date] NULL,
[OtherPayer2InfoRelease] [char](1) NULL,
[OtherPayer2BenefitAssign] [char](1) NULL,
[OtherPayer2DocControlNo] [varchar](50) NULL,
[OtherPayer2PriorAuth] [varchar](50) NULL,
[OtherPayer2OtherIDQual1] [char](3) NULL,
[OtherPayer2OtherID1] [varchar](50) NULL,
[OtherPayer2OtherIDQual2] [char](3) NULL,
[OtherPayer2OtherID2] [varchar](50) NULL,
/*Claim Data*/
[ClaimNo] [varchar](50) NOT NULL,
[Amount] [money] NULL,
[EstimatedAmountDue] [money] NULL,
[PatientEstimatedAmountDue] [money] NULL,
[PlaceOfService] [char](2) NULL,
[ClaimFrequency] [char](1) 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] [time](7) NULL,
[AdmissionType] [char](1) NULL,
[AdmissionSource] [char](1) NULL,
[DischargeHour] [time](7) NULL,
[PatientStatus] [char](2) NULL,
[CoveredDays] [varchar](4) NULL,
[NonCoveredDays] [varchar](4) NULL,
[COBDays] [varchar](4) NULL,
[LifeTimeReserveDays] [varchar](4) NULL,
[PriorAuthorization] [varchar](50) NULL,
[ClearingHouseID] [varchar](50) NULL,
[MedicalRecordNumber] [varchar](50) NULL,
[MothersMedicalRecordNumber] [varchar](50) NULL,
[ServiceAuthorizationException] [varchar](50) NULL,
[ReferralNumber] [varchar](50) NULL,
[PayerClaimControlNumber] [varchar](50) NULL,
[AdjustedRepricedClaimNumber] [varchar](50) NULL,
[AutoAccidentState] [varchar](50) NULL,
[OrigRefNo] [varchar](50) NULL,
[ClaimType] [char](1) NULL,
[TypeOfBill] [varchar](20) NULL,
[Remark1] [varchar](90) NULL,
[Remark2] [varchar](90) NULL,
[Remark3] [varchar](90) NULL,
[Remark4] [varchar](90) NULL,
[K3_1] [varchar](80) NULL,
[K3_2] [varchar](80) NULL,
[K3_3] [varchar](80) NULL,
[K3_4] [varchar](80) NULL,
[K3_5] [varchar](80) NULL,
[K3_6] [varchar](80) NULL,
[K3_7] [varchar](80) NULL,
[K3_8] [varchar](80) NULL,
[K3_9] [varchar](80) NULL,
[K3_10] [varchar](80) NULL,
[OutsideLab] [char](1) NULL,
[LabCharge] [varchar](10) NULL,
[Test_Prod] [char](1) NULL,
[ReportTypeCode1] [char](2) NULL,
[ReportTransmissionCode1] [varchar](2) NULL,
[AttachmentControlNumber1] [varchar](80) NULL,
/*Dental Claims*/
[Predetermination] [char](2) NULL,
[OrthodonticTotal] [varchar](5) NULL,
[OrthodonticRemaining] [varchar](5) NULL,
[OrthodonticYesNo] [char](1) NULL,
[ToothStatus] [varchar](100) NULL,
[AppliancePlacementDate] [date] NULL,
/*Diagnosis Information*/
[AdmitDiagnosis] [varchar](10) NULL,
[ECode] [varchar](30) NULL,
[ECode2] [varchar](30) NULL,
[ECode3] [varchar](30) NULL,
[ECode4] [varchar](30) NULL,
[ECode5] [varchar](30) NULL,
[ECode6] [varchar](30) NULL,
[ECode7] [varchar](30) NULL,
[ECode8] [varchar](30) NULL,
--[ECode9] [varchar](30) NULL,
--[ECode10] [varchar](30) NULL,
--[ECode11] [varchar](30) NULL,
--[ECode12] [varchar](30) NULL,
[ReasonForVisit] [varchar](30) NULL,
[ReasonForVisit2] [varchar](30) NULL,
[ReasonForVisit3] [varchar](30) NULL,
[PrincipalDiagnosis] [varchar](10) NULL,
[Diag2] [varchar](10) NULL,
[Diag3] [varchar](10) NULL,
[Diag4] [varchar](10) NULL,
[Diag5] [varchar](10) NULL,
[Diag6] [varchar](10) NULL,
[Diag7] [varchar](10) NULL,
[Diag8] [varchar](10) NULL,
[Diag9] [varchar](10) NULL,
[Diag10] [varchar](10) NULL,
[Diag11] [varchar](10) NULL,
[Diag12] [varchar](10) NULL,
[Diag13] [varchar](10) NULL,
[Diag14] [varchar](10) NULL,
[Diag15] [varchar](10) NULL,
[Diag16] [varchar](10) NULL,
[Diag17] [varchar](10) NULL,
[Diag18] [varchar](10) NULL,
[Diag19] [varchar](10) NULL,
[Diag20] [varchar](10) NULL,
[Diag21] [varchar](10) NULL,
[Diag22] [varchar](10) NULL,
[Diag23] [varchar](10) NULL,
[Diag24] [varchar](10) NULL,
[Diag25] [varchar](10) NULL,
[DRG] [varchar](15) NULL,
/*Procedure Information*/
[PrincipalProcedure] [varchar](10) NULL,
[PrincipalProcedureDate] [datetime2](0) NULL,
[Proc2] [varchar](50) NULL,
[Proc2Date] [datetime2](0) NULL,
[Proc3] [varchar](10) NULL,
[Proc3Date] [datetime2](0) NULL,
[Proc4] [varchar](10) NULL,
[Proc4Date] [datetime2](0) NULL,
[Proc5] [varchar](10) NULL,
[Proc5Date] [datetime2](0) NULL,
[Proc6] [varchar](10) NULL,
[Proc6Date] [datetime2](0) NULL,
[Proc7] [varchar](10) NULL,
[Proc7Date] [datetime2](0) NULL,
[Proc8] [varchar](10) NULL,
[Proc8Date] [datetime2](0) NULL,
[Proc9] [varchar](10) NULL,
[Proc9Date] [datetime2](0) NULL,
[Proc10] [varchar](10) NULL,
[Proc10Date] [datetime2](0) NULL,
[Proc11] [varchar](10) NULL,
[Proc11Date] [datetime2](0) NULL,
[Proc12] [varchar](10) NULL,
[Proc12Date] [datetime2](0) NULL,
[Proc13] [varchar](10) NULL,
[Proc13Date] [datetime2](0) NULL,
[Proc14] [varchar](10) NULL,
[Proc14Date] [datetime2](0) NULL,
[Proc15] [varchar](10) NULL,
[Proc15Date] [datetime2](0) NULL,
[Proc16] [varchar](10) NULL,
[Proc16Date] [datetime2](0) NULL,
[Proc17] [varchar](10) NULL,
[Proc17Date] [datetime2](0) NULL,
[Proc18] [varchar](10) NULL,
[Proc18Date] [datetime2](0) NULL,
[Proc19] [varchar](10) NULL,
[Proc19Date] [datetime2](0) NULL,
[Proc20] [varchar](10) NULL,
[Proc20Date] [datetime2](0) NULL,
[Proc21] [varchar](10) NULL,
[Proc21Date] [datetime2](0) NULL,
[Proc22] [varchar](10) NULL,
[Proc22Date] [datetime2](0) NULL,
[Proc23] [varchar](10) NULL,
[Proc23Date] [datetime2](0) NULL,
[Proc24] [varchar](10) NULL,
[Proc24Date] [datetime2](0) NULL,
[Proc25] [varchar](10) NULL,
[Proc25Date] [datetime2](0) NULL,
/*Value Codes*/
[ValueCode1] [char](3) NULL,
[ValueAmount1] [varchar](10) NULL,
[ValueCode2] [char](3) NULL,
[ValueAmount2] [varchar](10) NULL,
[ValueCode3] [char](3) NULL,
[ValueAmount3] [varchar](10) NULL,
[ValueCode4] [char](3) NULL,
[ValueAmount4] [varchar](10) NULL,
[ValueCode5] [char](3) NULL,
[ValueAmount5] [varchar](10) NULL,
[ValueCode6] [char](3) NULL,
[ValueAmount6] [varchar](10) NULL,
[ValueCode7] [varchar](10) NULL,
[ValueAmount7] [varchar](10) NULL,
[ValueCode8] [char](3) NULL,
[ValueAmount8] [varchar](10) NULL,
[ValueCode9] [char](3) NULL,
[ValueAmount9] [varchar](10) NULL,
[ValueCode10] [char](3) NULL,
[ValueAmount10] [varchar](10) NULL,
[ValueCode11] [char](3) NULL,
[ValueAmount11] [varchar](10) NULL,
[ValueCode12] [char](3) NULL,
[ValueAmount12] [varchar](10) NULL,
--[ValueCode13] [char](3) NULL,
--[ValueAmount13] [varchar](10) NULL,
--[ValueCode14] [char](3) NULL,
--[ValueAmount14] [varchar](10) NULL,
--[ValueCode15] [char](3) NULL,
--[ValueAmount15] [varchar](10) NULL,
--[ValueCode16] [char](3) NULL,
--[ValueAmount16] [varchar](10) NULL,
--[ValueCode17] [char](3) NULL,
--[ValueAmount17] [varchar](10) NULL,
--[ValueCode18] [char](3) NULL,
--[ValueAmount18] [varchar](10) NULL,
--[ValueCode19] [char](3) NULL,
--[ValueAmount19] [varchar](10) NULL,
--[ValueCode20] [char](3) NULL,
--[ValueAmount20] [varchar](10) NULL,
--[ValueCode21] [char](3) NULL,
--[ValueAmount21] [varchar](10) NULL,
--[ValueCode22] [char](3) NULL,
--[ValueAmount22] [varchar](10) NULL,
--[ValueCode23] [char](3) NULL,
--[ValueAmount23] [varchar](10) NULL,
--[ValueCode24] [char](3) NULL,
--[ValueAmount24] [varchar](10) NULL,
/*Condition Codes*/
[ConditionCode1] [varchar](30) NULL,
[ConditionCode2] [varchar](30) NULL,
[ConditionCode3] [varchar](30) NULL,
[ConditionCode4] [varchar](30) NULL,
[ConditionCode5] [varchar](30) NULL,
[ConditionCode6] [varchar](30) NULL,
[ConditionCode7] [varchar](30) NULL,
[ConditionCode8] [varchar](30) NULL,
[ConditionCode9] [varchar](30) NULL,
[ConditionCode10] [varchar](30) NULL,
--[ConditionCode11] [varchar](30) NULL,
--[ConditionCode12] [varchar](30) NULL,
--[ConditionCode13] [varchar](30) NULL,
--[ConditionCode14] [varchar](30) NULL,
--[ConditionCode15] [varchar](30) NULL,
--[ConditionCode16] [varchar](30) NULL,
--[ConditionCode17] [varchar](30) NULL,
--[ConditionCode18] [varchar](30) NULL,
--[ConditionCode19] [varchar](30) NULL,
--[ConditionCode20] [varchar](30) NULL,
--[ConditionCode21] [varchar](30) NULL,
--[ConditionCode22] [varchar](30) NULL,
--[ConditionCode23] [varchar](30) NULL,
--[ConditionCode24] [varchar](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] [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*/
[RepricedClaimNumber] [varchar](50) NULL,
[RepricingMethodology] [varchar](3) NULL,
[RepricedAmount] [money] NULL,
[SavingsAmount] [money] NULL,
[RepricerID] [varchar](30) NULL,
[RepricingRate] [varchar](9) NULL,
[APG_Code] [varchar](30) NULL,
[APG_Amount] [money] 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
) ON [PRIMARY]
Detail
CREATE TABLE [dbo].[edi_ClaimDetail](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[ClaimID] [bigint] NOT NULL,
[LineNumber] [decimal](18, 0) NULL,
[LineID] [varchar](50) NULL,
[ServiceDateFrom] [datetime2](0) NULL,
[ServiceDateTo] [date] NULL,
[AssessmentDate] [date] NULL,
[FacilityCode] [char](2) NULL,
[RevenueCode] [varchar](48) NULL,
[ProcedureCode] [varchar](48) NULL,
[Amount] [money] NULL,
[Unit] [char](2) NULL,
[Quantity] [float] NULL,
[UnitRate] [money] NULL,
[NonCovered] [money] NULL,
[Paid] [money] 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] [money] NULL,
[FacilityTax] [money] NULL,
[SalesTax] [money] NULL,
[ApprovedAmount] [money] 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] [money] NULL,
[SavingsAmount] [money] NULL,
[RepricerID] [varchar](30) NULL,
[RepricingRate] [varchar](9) NULL,
[APG_Code] [varchar](30) NULL,
[APG_Amount] [money] 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] [money] NULL,
[DrugUnitCode] [char](2) NULL,
[DrugUnits] [float] 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] [money] NULL,
[OtherPayer1PaidProcedure] [varchar](30) NULL,
[OtherPayer1PaidRevenueCode] [varchar](30) NULL,
[OtherPayer1PaidQuantity] [bigint] NULL,
[OtherPayer1BundledLine] [int] NULL,
[OtherPayer1AdjustmentReasonGroup1] [char](2) NULL,
[OtherPayer1AdjustmentReason1] [varchar](5) NULL,
[OtherPayer1AdjustmentAmount1] [money] NULL,
[OtherPayer1AdjustmentQuantity1] [bigint] NULL,
[OtherPayer1AdjustmentReasonGroup2] [char](2) NULL,
[OtherPayer1AdjustmentReason2] [varchar](5) NULL,
[OtherPayer1AdjustmentAmount2] [money] NULL,
[OtherPayer1AdjustmentQuantity2] [bigint] NULL,
[OtherPayer1AdjustmentReasonGroup3] [char](2) NULL,
[OtherPayer1AdjustmentReason3] [varchar](5) NULL,
[OtherPayer1AdjustmentAmount3] [money] NULL,
[OtherPayer1AdjustmentQuantity3] [bigint] NULL,
[OtherPayer1AdjustmentReasonGroup4] [char](2) NULL,
[OtherPayer1AdjustmentReason4] [varchar](5) NULL,
[OtherPayer1AdjustmentAmount4] [money] NULL,
[OtherPayer1AdjustmentQuantity4] [bigint] NULL,
--[OtherPayer1AdjustmentReasonGroup5Plus] [varchar](77) NULL,
--[OtherPayer1AdjustmentReason5Plus] [varchar](155) NULL,
--[OtherPayer1AdjustmentAmount5Plus] [varchar](493) NULL,
--[OtherPayer1AdjustmentQuantity5Plus] [varchar](415) NULL,
[OtherPayer1PaidDate] [date] NULL,
/*COB 2 */
[OtherPayer2ID] [varchar](30) NULL,
[OtherPayer2Paid] [money] NULL,
[OtherPayer2PaidProcedure] [varchar](30) NULL,
[OtherPayer2PaidRevenueCode] [varchar](30) NULL,
[OtherPayer2PaidQuantity] [bigint] NULL,
[OtherPayer2BundledLine] [int] NULL,
[OtherPayer2AdjustmentReasonGroup1] [char](2) NULL,
[OtherPayer2AdjustmentReason1] [varchar](5) NULL,
[OtherPayer2AdjustmentAmount1] [money] NULL,
[OtherPayer2AdjustmentQuantity1] [bigint] NULL,
[OtherPayer2AdjustmentReasonGroup2] [char](2) NULL,
[OtherPayer2AdjustmentReason2] [varchar](5) NULL,
[OtherPayer2AdjustmentAmount2] [money] NULL,
[OtherPayer2AdjustmentQuantity2] [bigint] NULL,
[OtherPayer2AdjustmentReasonGroup3] [char](2) NULL,
[OtherPayer2AdjustmentReason3] [varchar](5) NULL,
[OtherPayer2AdjustmentAmount3] [money] NULL,
[OtherPayer2AdjustmentQuantity3] [bigint] NULL,
[OtherPayer2AdjustmentReasonGroup4] [char](2) NULL,
[OtherPayer2AdjustmentReason4] [varchar](5) NULL,
[OtherPayer2AdjustmentAmount4] [money] NULL,
[OtherPayer2AdjustmentQuantity4] [bigint] NULL,
--[OtherPayer2AdjustmentReasonGroup5Plus] [varchar](77) NULL,
--[OtherPayer2AdjustmentReason5Plus] [varchar](155) NULL,
--[OtherPayer2AdjustmentAmount5Plus] [varchar](493) NULL,
--[OtherPayer2AdjustmentQuantity5Plus] [varchar](415) NULL,
[OtherPayer2PaidDate] [date] NULL
) ON [PRIMARY]
No questions yet.
MySQL Script to Create Tables
This is a free, industrial strength Open Source database available at www.MySQL.com
Header
CREATE 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;
Detail
CREATE 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;
No questions yet.
Oracle Script to Create Tables
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;
No questions yet.
Microsoft Access Script to Create Tables
We do not recommend Access.
It has limitations and problems that make it unacceptable for business solutions. If price is an issue, we recommend the free MySQL software. This is a free, industrial strength Open Source database available at www.MySQL.com
CREATE TABLE edi_claims (
ID INT NOT NULL AUTO_INCREMENT KEY,
ClaimNo varchar (50) NOT NULL ,
HIPAAsuiteID varchar (9) NULL ,
TradingPartnerIDType char (2) NULL ,
TradingPartnerID varchar (15) NOT NULL ,
SubmitterName varchar (35) NULL ,
SubmitterID varchar (30) NULL ,
SubmitterContact varchar (60) NULL ,
SubmitterTel varchar (20) NULL ,
SubmitterTelExt varchar (10) NULL ,
SubmitterFax varchar (20) NULL ,
SubmitterEmail varchar (80) NULL ,
ReceiverName varchar (35) NULL ,
BillProvIDType char (2) NULL ,
BillProvID varchar (80) NULL ,
BillProvLast varchar (35) NULL ,
BillProvFirst varchar (25) NULL ,
BillProvMiddle varchar (25) NULL ,
BillProvSuffix varchar (10) NULL ,
BillProvSpecialty varchar (10) NULL ,
BillProvAddress varchar (55) NULL ,
BillProvAddress2 varchar (55) NULL ,
BillProvCity varchar (30) NULL ,
BillProvState char (2) NULL ,
BillProvZip varchar (15) NULL ,
BillProvContact varchar (60) NULL ,
BillProvTel varchar (80) NULL ,
BillProvTelExt varchar (80) NULL ,
BillProvFax varchar (80) NULL ,
BillProvEmail varchar (80) NULL ,
PayToProvIDType char (2) NULL ,
PaytoProvID varchar (80) NULL ,
PaytoProvLast varchar (35) NULL ,
PaytoProvFirst varchar (25) NULL ,
PayToProvMiddle varchar (25) NULL ,
PayToProvSuffix varchar (10) NULL ,
PayToProvSpecialty varchar (10) NULL ,
PaytoProvAddress varchar (55) NULL ,
PayToProvAddress2 varchar (55) NULL ,
PaytoProvCity varchar (30) NULL ,
PaytoProvState char (2) NULL ,
PaytoProvZip varchar (15) NULL ,
RendProvIDType char (2) NULL ,
RendProvID varchar (80) NULL ,
RendProvLast varchar (35) NULL ,
RendProvFirst varchar (25) NULL ,
RendProvMiddle varchar (25) NULL ,
RendProvSuffix varchar (10) NULL ,
RendProvSpecialty varchar (10) NULL ,
AttendProvLast varchar (35) NULL ,
AttendProvFirst varchar (25) NULL ,
AttendProvMiddle varchar (25) NULL ,
AttendProvSuffix varchar (10) NULL ,
AttendProvIDType char (2) NULL ,
AttendProvID varchar (80) NULL ,
AttendProvSpecialty varchar (10) NULL ,
OperatingProvLast varchar (35) NULL ,
OperatingProvFirst varchar (25) NULL ,
OperatingProvMiddle varchar (25) NULL ,
OperatingProvSuffix varchar (10) NULL ,
OperatingProvIDType char (2) NULL ,
OperatingProvID varchar (80) NULL ,
OperatingProvSpecialty varchar (10) NULL ,
OtherProvLast varchar (35) NULL ,
OtherProvFirst varchar (25) NULL ,
OtherProvMiddle varchar (25) NULL ,
OtherProvSuffix varchar (10) NULL ,
OtherProvIDType varchar (100) NULL ,
OtherProvID varchar (100) NULL ,
OtherProvSpecialty varchar (100) NULL ,
FacilityType char (2) NULL ,
FacilityIDType char (2) NULL ,
FacilityID varchar (80) NULL ,
FacilityName varchar (80) NULL ,
FacilityAddress varchar (55) NULL ,
FacilityAddress2 varchar (55) NULL ,
FacilityCity varchar (30) NULL ,
FacilitySpecialty varchar (100) NULL ,
FacilityState char (2) NULL ,
FacilityZip varchar (15) NULL ,
OutsideLab char (1) NULL ,
LabCharge varchar (10) NULL ,
SubscriberLast varchar (35) NULL ,
SubscriberFirst varchar (25) NULL ,
SubscriberMiddle varchar (25) NULL ,
SubscriberSuffix varchar (10) NULL ,
SubscriberAddress varchar (55) NULL ,
SubscriberAddress2 varchar (55) NULL ,
SubscriberCity varchar (30) NULL ,
SubscriberState char (2) NULL ,
SubscriberZip varchar (15) NULL ,
SubscriberIDType char (2) NULL ,
SubscriberID varchar (80) NULL ,
SubscriberDOB varchar (10) NULL ,
SubscriberSex char (1) NULL ,
InsuranceType char (2) NULL ,
GroupNo varchar (50) NULL ,
GroupName varchar (60) NULL ,
FilingIndicator char (2) NULL ,
PatientLast varchar (35) NULL ,
PatientFirst varchar (25) NULL ,
PatientMiddle varchar (25) NULL ,
PatientSuffix varchar (10) NULL ,
PatientAddress varchar (55) NULL ,
PatientAddress2 varchar (55) NULL ,
PatientCity varchar (30) NULL ,
PatientState char (2) NULL ,
PatientZip varchar (15) NULL ,
PatientIDType char (2) NULL ,
PatientID varchar (80) NULL ,
PatientDOB varchar (50) NULL ,
PatientSex char (1) NULL ,
PatientRelationship char (2) NULL ,
PatientPaid varchar (10) NULL ,
PregnancyIndicator char (1) NULL ,
OtherInsuredLast varchar (35) NULL ,
OtherInsuredFirst varchar (25) NULL ,
OtherInsuredMiddle varchar (25) NULL ,
OtherInsuredSuffix varchar (10) NULL ,
OtherInsuredPolicy varchar (50) NULL ,
OtherInsuredDOB varchar (10) NULL ,
OtherInsuredSex char (1) NULL ,
OtherInsuredRelationship char (2) NULL ,
OtherPayerName varchar (35) NULL ,
OtherPayerID varchar (80) NULL ,
OtherPayerPaid varchar (10) NULL ,
OtherPayerGroupName varchar (100) NULL ,
OtherPayerGroupNo varchar (100) NULL ,
PayerName varchar (35) NULL ,
PayerIDType char (2) NULL ,
PayerID varchar (80) NULL ,
PayerAddress varchar (55) NULL ,
PayerAddress2 varchar (55) NULL ,
PayerCity varchar (30) NULL ,
PayerState char (2) NULL ,
PayerZip varchar (15) NULL ,
Amount varchar (50) NULL ,
EstimatedAmountDue varchar (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 ,
RelatedCausesState char (2) NULL ,
SpecialProgramCode char (3) NULL ,
ProviderParticipation char (1) NULL ,
ServiceDateFrom varchar (15) NULL ,
ServiceDateTo varchar (15) NULL ,
OnsetDate varchar (15) NULL ,
SimilarSymptomsDate varchar (10) NULL ,
DisabilityBegin varchar (10) NULL ,
DisabilityEnd varchar (10) NULL ,
HospitalizationBegin varchar (20) NULL ,
HospitalizationEnd varchar (10) NULL ,
AccidentDate varchar (20) NULL ,
AdmissionDate varchar (15) NULL ,
AdmissionHour varchar (10) NULL ,
AdmissionType char (1) NULL ,
AdmissionSource char (1) NULL ,
DischargeHour varchar (10) NULL ,
PatientStatus char (2) NULL ,
MedicalRecordNumber varchar (50) NULL ,
CoveredDays varchar (4) NULL ,
NonCoveredDays varchar (4) NULL ,
COBDays varchar (4) NULL ,
LifeTimeReserveDays varchar (4) NULL ,
PriorAuthorization varchar (50) NULL ,
OrigRefNo varchar (50) NULL ,
RefProvLast varchar (35) NULL ,
RefProvFirst varchar (25) NULL ,
RefProvMiddle varchar (25) NULL ,
RefProvSuffix varchar (10) NULL ,
RefProvIDType char (2) NULL ,
RefProvID varchar (80) NULL ,
RefProvSpecialty varchar (10) NULL ,
AdmitDiagnosis varchar (10) NULL ,
ECode varchar (100) NULL ,
PrincipalDiagnosis varchar (10) NULL ,
Diag2 varchar (10) NULL ,
Diag3 varchar (10) NULL ,
Diag4 varchar (10) NULL ,
Diag5 varchar (10) NULL ,
Diag6 varchar (10) NULL ,
Diag7 varchar (10) NULL ,
Diag8 varchar (10) NULL ,
Diag9 varchar (10) NULL ,
PrincipalProcedure varchar (10) NULL ,
PrincipalProcedureDate varchar (10) NULL ,
Proc2 varchar (50) NULL ,
Proc2Date varchar (10) NULL ,
Proc3 varchar (10) NULL ,
Proc3Date varchar (10) NULL ,
Proc4 varchar (10) NULL ,
Proc4Date varchar (10) NULL ,
Proc5 varchar (10) NULL ,
Proc5Date varchar (10) NULL ,
Proc6 varchar (10) NULL ,
Proc6Date varchar (10) NULL ,
DRG varchar (15) NULL ,
ValueCode1 char (3) NULL ,
ValueAmount1 varchar (10) NULL ,
ValueCode2 char (3) NULL ,
ValueAmount2 varchar (10) NULL ,
ValueCode3 char (3) NULL ,
ValueAmount3 varchar (10) NULL ,
ValueCode4 char (3) NULL ,
ValueAmount4 varchar (10) NULL ,
ValueCode5 char (3) NULL ,
ValueAmount5 varchar (10) NULL ,
ValueCode6 char (3) NULL ,
ValueAmount6 varchar (10) NULL ,
ValueCode7 varchar (10) NULL ,
ValueAmount7 varchar (10) NULL ,
ValueCode8 char (3) NULL ,
ValueAmount8 varchar (10) NULL ,
ValueCode9 char (3) NULL ,
ValueAmount9 varchar (10) NULL ,
ValueCode10 char (3) NULL ,
ValueAmount10 varchar (10) NULL ,
ValueCode11 char (3) NULL ,
ValueAmount11 varchar (10) NULL ,
ValueCode12 char (3) NULL ,
ValueAmount12 varchar (10) NULL ,
ConditionCode1 varchar (30) NULL ,
ConditionCode2 varchar (30) NULL ,
ConditionCode3 varchar (30) NULL ,
ConditionCode4 varchar (30) NULL ,
ConditionCode5 varchar (30) NULL ,
ConditionCode6 varchar (30) NULL ,
ConditionCode7 varchar (30) NULL ,
ConditionCode8 varchar (30) NULL ,
FileName varchar (100) NULL ,
OccurranceCode1 char (3) NULL ,
OccurranceDate1 varchar (10) NULL ,
OccurranceCode2 char (3) NULL ,
OccurranceDate2 varchar (10) NULL ,
OccurranceCode3 char (3) NULL ,
OccurranceDate3 varchar (10) NULL ,
OccurranceCode4 char (3) NULL ,
OccurranceDate4 varchar (10) NULL ,
OccurranceCode5 char (3) NULL ,
OccurranceDate5 varchar (10) NULL ,
OccurranceCode6 char (3) NULL ,
OccurranceDate6 varchar (10) NULL ,
OccurranceCode7 char (3) NULL ,
OccurranceDate7 varchar (10) NULL ,
OccurranceCode8 char (3) NULL ,
OccurranceDate8 varchar (10) NULL ,
OccurranceSpanCode1 char (3) NULL ,
OccurranceSpanFrom1 varchar (10) NULL ,
OccurranceSpanTo1 varchar (10) NULL ,
OccurranceSpanCode2 char (3) NULL ,
OccurranceSpanFrom2 varchar (10) NULL ,
OccurranceSpanTo2 varchar (10) NULL ,
Test_Prod char (1) NULL ,
ClaimType char (1) NULL ,
TypeofBill varchar (20) NULL ,
Remark1 varchar (90) NULL ,
Remark2 varchar (90) NULL ,
Remark3 varchar (90) NULL ,
Remark4 varchar (90) NULL ,
Claim_status char (1) NULL ,
TargetTP varchar (30) NULL ,
ClearingHouseID varchar (100) NULL,
RepricedClaimNumber varchar (100) NULL,
RepricingMethodology varchar (3) NULL,
RepricedAmount varchar (15) NULL,
SavingsAmount varchar (15) NULL,
RepricerID varchar (30) NULL,
RejectReason varchar (3) NULL
) ;
CREATE TABLE edi_claimdetail (
ID INT NOT NULL AUTO_INCREMENT KEY,
ClaimID varchar (50) NOT NULL ,
HIPAAsuiteID varchar (10) NULL ,
LineNumber decimal(18, 0) NOT NULL ,
LineID varchar (50) NULL ,
ServiceDateFrom varchar (50) NULL ,
ServiceDateTo varchar (50) NULL ,
FacilityCode varchar (50) NULL ,
RevenueCode varchar (50) NULL ,
ProcedureCode varchar (50) NULL ,
Description varchar (100) NULL,
Amount varchar (50) NULL ,
Unit varchar (50) NULL ,
Quantity varchar (50) NULL ,
UnitRate varchar (50) NULL ,
NonCovered varchar (50) NULL ,
Paid varchar (50) NULL ,
PlaceOfService char (3) NULL ,
Modifier1 char (3) NULL ,
Modifier2 char (3) NULL ,
Modifier3 char (3) NULL ,
Modifier4 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,
RepricingMethodology varchar (3) NULL,
RepricedAmount varchar (15) NULL,
SavingsAmount varchar (15) NULL,
RepricerID varchar (30) NULL,
RejectReason varchar (3) NULL
) ;
No questions yet.
Other Databases Scripts to Create Tables
If you have a database that is not mentioned here, you can do several things:
| 1. | Have a database administrator change the scripts in order for them to function; |
| 2. | Create the fields in you database administration tool according to the specifications in the scripts. |
No questions yet.