ECLIPSE® File Layouts for ODBC Users

THE INFORMATION PROVIDED BELOW IS PROPRIETARY TO MPN SOFTWARE SYSTEMS. IT IS BEING PROVIDED "AS IS". ALL INFORMATION IN THIS DOCUMENT IS SUBJECT TO CHANGE WITHOUT NOTICE.

THIS DOCUMENT ASSUMES SOME DEGREE OF FAMILIARITY WITH ODBC AND TYPICAL DBMS'S. IT IS NOT PART OF ANY SUPPORT CONTRACT THAT COVERS YOUR ECLIPSE SOFTWARE. THE ACCURACY OF ANY RESULTS YOU OBTAIN IS ENTIRELY YOUR RESPONSIBILITY.

TECHNICAL QUESTIONS WITH REGARD TO ODBC IMPLEMENTATION AND TROUBLESHOOTING ARE CONSIDERED BILLABLE AT OUR STANDARD HOURLY RATE.

In order to take advantage of the file layouts and field identifiers explained in this document, you must have the FairCom ODBC Driver loaded on your computer. This inexpensive driver (which is licensed per user by FairCom) can be purchased directly through HNA Computer Systems.

What this document is not

This document does not explain how to use ODBC or your report generator (e.g. Crystal Reports), load the FairCom drivers, or set up Microsoft ODBC Administration on your computer.

What this document is

Our objective is to explain the relationships among the ECLIPSE database files and define database fields so you can intelligently access the ECLIPSE database directly without using the ECLIPSE program.

Before you can access the ECLIPSE database through an ODBC compliant application, you must first purchase and install the Faircom ODBC Driver as noted above.

ECLIPSE Files (Tables) as Defined for ODBC

Typical ODBC Name

Actual File Name

Content

ACCOUNTS

Account.dat

Services & credits

ACCOUNTARCHIVE

AccountArchive.dat

Archived services & credits

APPOINTMENTS

Appointment.dat

Appointments

APPOINTMENTARCHIVE

AppointmentArchive.dat

Archived appointments

ATTORNEYS

Attorney.dat

Attorney names & addresses

BILLS

Bill.dat

Billing records

BILLARCHIVE

BillArchive.dat

Archived bills

CONDITIONS

Condition.dat

Condition info (e.g. diagnoses, dates)

CPTCODES

CPT.DAT

CPT codes, fee structures, etc.

DOCTORS

Doctor.dat

Physician names & addresses, etc.

DOCTORIDS

DoctorID.dat

Payor/Profile specific ID#'s

EMPLOYERS

Employer.dat

Employer names & addresses

FACILITIES

Facility.dat

Hospital/Facility names & addresses

GUARANTORS

Guarantor.dat

Insured information

ICDCODES

ICD.DAT

ICD-9 codes

PATIENTS

Patient.dat

Patient names & addresses

PATIENTSCASES

PatientCase.dat

Svcs/crdts/garntrs/cndtns for one case

PAYORS

Payor.dat

Insurance company info

PERSONALINJURY

PersonalInjury.dat

PI info

PROFILES

Profile.dat

Billing profiles

REFERRALS

Referral.dat

Referral source names & addresses

SOAP

SOAP.DAT

S.OA.P. notes

STATEFORMS

StateForm.dat

State-specific billing info

SUBMITTERS

Submitter.dat

NSF Submitter info

WORKCOMP

WorkComp.dat

Comp info

 

Definitions

Table A file that contains a specific type of data (e.g. ICD codes).

Index A file that provides access to a table in a pre-defined order (e.g. alphabetized by last name).

Database Complete set of data tables used by ECLIPSE.

Member Individual record in a table (e.g. one attorney in ATTORNEYS).

Field Individual data in a member record such as first name, last name, etc.

Primary Keys & Inter-Table Relationships

Before we discuss the table hierarchy, it's important we review the relationships formed when data in one table is assigned to or associated with data in a second table. For example a service may be assigned to a specific doctor in the DOCTORS table and a specific facility in the FACILITIES table. In order to achieve this relationship, the record in the ACCOUNTS table stores the primary key information for the assigned doctor and facility.

Primary keys are unique identifiers which are unique to a specific record in a given data table. ECLIPSE generally uses numeric keys that can be assigned to various records. Thus, a payor such as AETNA may be assigned ID #23 (primary key in PAYORS). Dr. Steve Smith may be assigned ID #17 (primary key in DOCTORS). The Golden Years Nursing Home may be assigned ID #191 (primary key in the FACILITIES).

So, to continue with the above example, a service which is assigned to Dr. Smith when he visited a patient at the Golden Years Nursing Home will reflect the primary key information for the DOCTORS and FACILITIES table within this service record in the ACCOUNTS table.

Primary key relationships among the various files are the only way that data in one table can be associated with unique data in another.

Table Hierarchy

Secondary tables such as ATTORNEYS, CPTCODES, DOCTORS, EMPLOYERS, FACILITIES, GUARANTORS, ICDCODES, etc. are generally indexed in both name and ID# order.

 

The primary patient file is PATIENTS. Each record in this file contains a patient name, address, phone #'s, social security #, etc., and represents a unique patient with a unique

numeric identification #. For each patient, multiple patient cases may exist in PATIENTCASES. In turn each patient case may be associated with a member of DOCTORS, EMPLOYERS, PROFILES, etc. as well as multiple members of ACCOUNTS, BILLS, and REFERRALS. Graphically, the relationship looks something like Figure 1.

Each arrow is a reference to a specific member in another data table. Note that these references are not physical. Thus, the John Smith member of PATIENTS does not carry a map that contains the location of related members in other tables. Rather, it contains the primary key information required to look up the requested member in an index associated with the table. This index is no different than an index you might have used at the local library to determine the location of a book you wanted to read.

Important Notes

All patient related records (e.g. PATIENTCASES, ACCOUNTS, BILLS, etc.) have a primary index that includes a combination of the patient primary key (ECLIPSE assigned ID #) and case ID #. In the following sections, members that are also full or partial index keys are noted as KEY in red if the member is a key for the data in which it appears. A KEY to data in another table will appear in blue. If the member is a KEY for both the member and another table it will appear in green.
Not all fields in the database are made public through ODBC. These fields are generally for internal use only.

The remaining sections of this guide discuss the members of each table alphabetically by table name. The following descriptors will be used within brackets ([ descriptor ]):

C-xx Character based string (e.g. a last name) where xx = the number of characters in the field.

I Numeric field that only handles integer values (e.g. 1, 2, 3, 77, 1292, etc.).

F Numeric field that handles floating point (fractional) numbers such as dollar amounts (e.g. 24.25, 70, 1000.08, etc.).

B Boolean value (true or false).

D Date.

T Time.

** You can update this field. In general, most fields are read-only to avoid potential database corruption. Fields marked with ** can be updated through ODBC.

Data Relationship Examples

Alphabetical patient report with balances

Use PATIENTS with the PatientLNameKey index. The members you might include in the report include PatientID, LastName, FirstName, and Balance.

All services in date order by patient in alphabetical order

Use PATIENTS with the PatientLNameKey index. For each patient, use the PatientID field to search the PatientCaseKey index to retrieve each related patient case in PATIENTCASES. For each case, retrieve, related entries in ACCOUNTS using PatientID and PatientCaseID to search the AccountPatIDKey index and use members where the EntryType = 2 indicating a service.

All credits assigned by payor alphabetically by payor.

Create a temporary index on ACCOUNTS using field PayorID. Traverse PAYORS using PayorNameKey or PayorIDKey. Use the PayorID field of the PAYORS member to search the temporary index and sum the credits for each payor.

 

ODBC Driver Settings

FairCom ODBC driver settings should be as follows:

Max columns 256
Data buffer size 64
Index buffer size 64
Sector size 128
Column order By Table Position
Key padding default
Alignment default
Data padding NULLs
Driver type Multi-User (non-Server)

 

ACCOUNTS

Description

This file contains all services and credits for each patient case.

Data Fields

SerialID. [I KEY] A numeric integer value unique to each member of the table. This value is used to determine links for line item payments. Example: If a payment has been credited to this service, the LinkedTo member will contain the SerialID of this member record.

PatientID [I KEY] The patient ID associated with this transaction.

Date [D KEY] The date set by the user.

ToDate [D] The through date set by the user.

PatientCaseID [I KEY] The patient case associated with this transaction.

EntryType [I] Determines the type of entry. Possible values are as follows:

1 Service

2 Sales tax

3 Interest

4 Late charge

55 Payor payment

56 Cash payment

57 Check payment

58 Credit card payment

59-108 User defined payments

    1. Payor write-off

110 Patient write-off

111-160 User defined write-offs (adjustments)

SubEntryType Not currently used.

OfficeCode [C-15 KEY] If this is a service: corresponds to a member in the CPTCODES table.

CPT Code [C-15] If this is a service

Modifiers [C-15] If this is a service

PlaceOfService [C-3] If this is a service

TypeOfService [C-3] If this is a service

ICD1 [C-10 KEY] If this is a service

ICD2 [C-10 KEY] If this is a service

ICD3 [C-10 KEY] If this is a service

ICD4 [C-10 KEY] If this is a service

ESPDT [C-1] If this is a service

Emergency [B] If this is a service:

COB [B] If this is a service:

CheckNumber [C-27] If this is a service

Units [F] If this is a service

Amount [F] Dollar amount of this transaction.

SalesTax [F] If this is a service

DeductibleApplied [F] If this is a service

PatientOwes [F] If this is a service

PatientPaid [F] If this is a service

PayorPaid [F] If this is a service

BalanceForward [F] Used only during RAM-based operations.

GuarantorID [I KEY]If this is a credit: Assigned guarantor in GUARANTORS.

PayorID [I KEY]If this is a credit: Assigned payor in PAYORS.

DoctorID [I KEY]Assigned physician (part 1) in DOCTORS.

OfficeID [I KEY]Assigned physician (part 2) in DOCTORS.

FacilityID [I KEY]Assigned facility in FACILITIES.

ReferralID [I KEY]If this is a service: Assigned billing referral in REFERRALS.

LinkedTo [I] If this is a credit: contains the SerialID of the service to which it is linked.

FirstUnbilled Not currently used.

BillingCode [I] If this is a service: Filter based on member of the same name in CPTCODES. This filter determines based on category whether this service will be included on non-patient bills.

Condition [I KEY] If this is a service: assigned condition in CONDITIONS based on this patient case.

Bill# [I] The bill number to which this member is currently assigned. "0" (zero) represents unbilled transactions.

Comments [C-31]

Available Indexes

AccountPatIDKey: PatientID / PatientCaseID / Date / SerialID. This index is used to quickly obtain all entries for a given patient case.

AccountDateKey: Date / : PatientID / PatientCaseID. This index is used to traverse the table in date order.

 

Appointments

Data Fields

PatientID [I KEY] The patient ID associated with this appointment.

DoctorID [I KEY]Assigned physician (part 1) in DOCTORS.

OfficeID [I KEY]Assigned physician (part 2) in DOCTORS.

Date [D KEY] The date of this appointment.

DateScheduled [D KEY] Date the user added this appointment.

Time [T KEY] Time this appointment begins

BlockEndTime [T] End time when DefineBlock is TRUE.

TextColor [I] RGB value for color mix as per Microsoft standards.

DurationInMinutes [I] Duration of this appointment in minutes from StartTime.

Room [I] Room number if applicable.

PatientCaseID [I KEY] The patient case associated with this transaction.

Width [I]

NewPatient [B] TRUE if this is a new patient appointment.

Cancelled [B] TRUE if this appointment was cancelled.

Rescheduled [B] TRUE if this appointment was rescheduled.

Visit [B] TRUE if this appointment has been completed.

DefineBlock [B] TRUE if this member defines a blocked time range for a specific date.

DefineReminder [B] TRUE if this member stores reminders / comments.

NewPatientLast [C-26] Last name if patient is not in PATIENTS.

NewPatientFirst [C-15] First name if patient is not in PATIENTS.

NewPatientMI [C-1] Middle initial if patient is not in PATIENTS.

Comments [C-50**] Comments

Available Indexes

AppointmentKey. Date / Time

PatAppointmentKey. PatientID / PatientCaseID / Date / Time

DateScheduledKey. DateScheduled

Attorneys

Description

Attorney information

Data Fields

AttorneyID [I KEY] Primary key for this member for use during assignments.

FirmName [C-30]

FirstName [C-15 KEY]

LastName [C-25 KEY]

Attention [C-35**]

Address [C-35**]

Address2 [C-35**]

City [C-20**]

State [C-2**},

Zip [C-10-**]

Phone [C-12**]

Extension, [C-4**]

Fax [C-12**]

Email [C-30]

Available Indexes

AttorneyIDKey. AttorneyID

AttorneyNameKey. LastName / FirstName / AttorneyID

Bills

Description

Each member contains a summation of charges and credits, responsibility, and amounts owed for a specific bill # attached to a patient's case. Members of this database are created automatically during bill generation as unbilled services are billed.

Data Fields

PatientID [I KEY] The patient ID associated with this transaction.

PatientCase [I KEY] The patient case associated with this transaction.

BillNumber [I KEY] The bill # that was generated automatically at the time this bill was generated.

DoctorID [I KEY]Assigned physician (part 1) in DOCTORS.

OfficeID [I KEY]Assigned physician (part 2) in DOCTORS.

Date [D KEY] Date this bill was originally generated

FromDate [D] First date of service on this bill

ToDate [D] Last date of service on this bill

LastTracer [D**] Last date an insurance tracer was sent.

LastCopy [D**] Last date a copy of this bill was prepared.

LastStatement [D**] Last date a statement was generated.

Condition [I KEY] Condition associated with this bill in CONDITIONS.

TotalVisits [I] Total # of visits (i.e. distinct dates of service) on this bill.

FirstVisit [I] First visit visits (i.e. date of service) on this bill.

Settled [B] Indicates that the balance on this bill is $0.00

ECSFlag [B] If TRUE, this bill was originally generated for electronic claim submission.

Destination Not currently used.

Remarks [C-74**] Comments

Fees [F] Total charges on this bill.

Interest [F] Total interest charges added to this bill.

LateCharge [F] Total late charges added to this bill.

SalesTax [F] Total sales tax added to bill.

PatientPayments [F] Total patient payments.

PayorPayments [F] Total insurance payments.

PatientAdjustments [F] Total patient adjustments.

PayorAdjustments [F] Total payor adjustments.

PatientOwes [F] Total amount owed by patient.

PayorOwes [F] Total amount owed by the insurance company.

Available Indexes

BillPatientIDKey. PatientID / PatientCase / BillNumber / Date

Conditions

Description

Conditions contain extended diagnostic information and are assigned to members of PATIENTCASES. Members of BILLS keep track of the CONDITIONS member that was in use at the time the bill was generated.

Data Fields

PatientID [I KEY] The patient ID associated with this transaction.

ConditionDate [D**] Date this condition becomes effective. When a new condition is added, this date is used to determine which condition unbilled services belong to.

ConditionStopDate [D**] Stop date for billing purposes

FirsConsulted [D**] Date patient originally consulted the doctor for this condition.

FirstOccurrence [D**] Date injury/illness occurred.

PreviousSymptomsDate [D**] Date of any previous symptoms.

HospitalizedFrom [D**]

HospitalizedTo [D**]

DisabilityFrom [D**]

DisabilityTo [D**]

PartialDisabilityFrom [D**]

PartialDisabilityTo [D**]

ReturnToWork [D**]

LastXrayDate [D**] Medicare/chiropractic requirements.

PriClaimAuthorizationDate [D**] Primary payor authorization thru date.

SecClaimAuthorizationDate[D**] Secondary payor authorization thru date.

MaximumDollarAmount [F**] Maximum accumulated charges on this condition.

LabCharges [F**]

PatientCase [I KEY] The patient case associated with this transaction.

PatientConditionID [I KEY] Unique identifier for this condition as determined by patient case.

TreatmentsAllowed [I**] Total # of service dates (based on CPTCODES members that are considered to be office visits) which are allowed by the payor.

TotalVisits [I] Accumulated visits on this condition.

MedicareVisitOverride [I**] Medicare/chiropractic requirements. This member allows user to compensate for prior visits that are not included in the ECLIPSE database.

PrimaryGuarantor [I KEY**] Assigned guarantor from PATIENTCASES: GuarantorID1 = 1, GuarantorID2 = 2, GuarantorID3 = 3, GuarantorID4 = 4.

SecondaryGuarantor [I KEY**] Assigned guarantor from PATIENTCASES: GuarantorID1 = 1, GuarantorID2 = 2, GuarantorID3 = 3, GuarantorID4 = 4.

TertiaryGuarantor [I KEY**] Assigned guarantor from PATIENTCASES: GuarantorID1 = 1, GuarantorID2 = 2, GuarantorID3 = 3, GuarantorID4 = 4.

NatureOfIllness [I] Medicare/chiropractic. ACUTE = 0, CHRONIC = 1, EXACERBATION = 2, PERMANENT = 3.

AccidentType [I] NONE = 0, AUTO = 1, OTHER = 2.

EmploymentStatus [I] NONE = 0, FT_STUDENT = 1, PT_STUDENT = 2, NOT_STUDENT = 3, FT_EMPLOYED = 4, PT_EMPLOYED = 5, NOT_EMPLOYED = 6, RETIRED = 7, SELF_EMPLOYED = 8, ACTIVE_MILITARY = 9.

ICD1 [C-10**]

ICD2 [C-10**]

ICD3 [C-10**]

ICDDescription1 [C-55**]

ICDDescription2 [C-55**]

ICDDescription3 [C-55**]

ICDDescription4 [C-55**]

SubluxationLevel [C-15**] Medicare / chiropractic.

Primary Authorization [C-20**]

SecondaryAuthorization [C-20**]

AccidentState [C-2**]

AcceptAssignment [B**]

LabWorkDone [B**]

WorkRelated [B**]

PreviousSymptoms [B**]

ValueCodeAmount [F**] UB-92/HCFA -1450 Data

ValueCodeAmount2 [F**] UB-92/HCFA -1450 Data

ValueCodeAmount3 [F**] UB-92/HCFA -1450 Data

ValueCodeAmount4 [F**] UB-92/HCFA -1450 Data

ValueCodeAmount5 [F**] UB-92/HCFA -1450 Data

ValueCodeAmount6 [F**] UB-92/HCFA -1450 Data

AdmitDate [D**] UB-92/HCFA -1450 Data

OccurrenceDate [D**] UB-92/HCFA -1450 Data

OccurrenceDate2 [D**] UB-92/HCFA -1450 Data

OccurrenceDate3 [D**] UB-92/HCFA -1450 Data

OccurrenceDate4 [D**] UB-92/HCFA -1450 Data

OccurrenceDate5 [D**] UB-92/HCFA -1450 Data

OccurrenceDate6 [D**] UB-92/HCFA -1450 Data

ValueAutoFill [I**] UB-92/HCFA -1450 Data

ValueAutoFill2 [I**] UB-92/HCFA -1450 Data

ValueAutoFill3 [I**] UB-92/HCFA -1450 Data

ValueAutoFill4 [I**] UB-92/HCFA -1450 Data

ValueAutoFill5 [I**] UB-92/HCFA -1450 Data

ValueAutoFill6 [I**] UB-92/HCFA -1450 Data

AdmitType [C-1**] UB-92/HCFA -1450 Data

AdmitSource [C-1**] UB-92/HCFA -1450 Data

Status [C-2**] UB-92/HCFA -1450 Data

ConditionCode [C-2**] UB-92/HCFA -1450 Data

ConditionCode2 [C-2**] UB-92/HCFA -1450 Data

ConditionCode3 [C-2**] UB-92/HCFA -1450 Data

ConditionCode4 [C-2**] UB-92/HCFA -1450 Data

ConditionCode5 [C-2**] UB-92/HCFA -1450 Data

ConditionCode6 [C-2**] UB-92/HCFA -1450 Data

OccurrenceCode [C-2**] UB-92/HCFA -1450 Data

OccurrenceCode2 [C-2**] UB-92/HCFA -1450 Data

OccurrenceCode3 [C-2**] UB-92/HCFA -1450 Data

OccurrenceCode4 [C-2**] UB-92/HCFA -1450 Data

OccurrenceCode5 [C-2**] UB-92/HCFA -1450 Data

OccurrenceCode6 [C-2**] UB-92/HCFA -1450 Data

ValueCode [C-2**] UB-92/HCFA -1450 Data

ValueCode2 [C-2**] UB-92/HCFA -1450 Data

ValueCode3 [C-2**] UB-92/HCFA -1450 Data

ValueCode4 [C-2**] UB-92/HCFA -1450 Data

ValueCode5 [C-2**] UB-92/HCFA -1450 Data

ValueCode6 [C-2**] UB-92/HCFA -1450 Data

AdmittingICD [C-10**] UB-92/HCFA -1450 Data

TypeOfBill [C-3**] UB-92/HCFA -1450 Data

Available Indexes

ConditionIDKey. PatientID / PatientCase / PatientConditionID

CPTCodes

Description

All services added to ACCOUNTS must exist in CPTCODES. This table holds a great deal more than services. It contains overrides for responsibility, inventory control, etc. To retrieve a CPTCODES member for a specific patient, the OfficeCode must be combined with the ProfileID of the PATIENTCASE member. If none exists for the OfficeCode, there must be an equivalent index entry with a ProfileID of 0.

Data Fields

OfficeCode [C-15 KEY] This member is the primary key used to identify this field in ACCOUNTS when services are added.

Description [C-41 KEY] Description of this service

CPTType [I] If this member is a service, then the value of this field is 0 (zero). If the value is 1 (one), this member is a MACRO that contains multiple OfficeCodes as defined in Macro1 through Macro10.

Macro1 [C-15 KEY] If CPTType = 1, an Office code.

Macro2 [C-15 KEY] If CPTType = 1, an Office code.

Macro3 [C-15 KEY] If CPTType = 1, an Office code.

Macro4 [C-15 KEY] If CPTType = 1, an Office code.

Macro5 [C-15 KEY] If CPTType = 1, an Office code.

Macro6 [C-15 KEY] If CPTType = 1, an Office code.

Macro7 [C-15 KEY] If CPTType = 1, an Office code.

Macro8 [C-15 KEY] If CPTType = 1, an Office code.

Macro9 [C-15 KEY] If CPTType = 1, an Office code.

Macro10 [C-15 KEY] If CPTType = 1, an Office code.

CPT [C-15] CPT-4 code.

Modifiers [C-15]

PlaceOfService [C-3]

NDCPOS [C-3] POS for NDC direct electronic billing.

TypeOfService [C-3].

NDCTOS [C-3] TOS for NDC direct electronic billing.

RDX [C-20] Prescription info

CLIA [C-20] HCFA Laboratory ID

OrderedFrom [C-53] Inventory source.

Revenue Code [C-4] UB-92 info

HCPCS [C-10] UB-92 info. Not currently used.

Fee [F]

NonCoveredCharge [F] UB-92 info. Not currently used.

AutoWriteOff [F] Dollar amount to written off automatically when this service is used.

SalesTaxRate [F] Override config sales tax rate. Not currently used.

Units [F]

UnitPrice [F] Inventory per item price.

CoveredAmount [F] Patient copayment

LastOrdered [D] Last date this inventory item was ordered.

ProfileID [I KEY] ID in PROFILES for this version of OfficeCode.

BarCode [I] Quicknotes identifier for charge import.

CurrentInventory [I] Quantity on hand

MinimumInventory [I] Level at which notification should occur.

BillingCode [I] Filter to determine if this item can be billed to primary payor based on BillingCodeFilter (not available via ODBC) in PAYORS. Possible values are:

BC_NONE 0

BC_DIAGNOSTIC_TEST 1

BC_THERAPY 2

BC_VITAMINS 4

BC_SALES_TAX 8

BC_LATE_CHARGES 16

BC_CONSULTATION 32

BC_LABORATORY 64

CoverageType [I] Possible values are: PCT_USE_PATIENT = 0, PCT_MANUAL_OVERRIDE = 1, PCT_COPAY_PATIENT = 2

TimeIncrement [I] Anesthesia minutes for use with Units.

FeeOverride [B] Overide fee during service entry?

Visit [B] Count as a office visit for statistics?

Taxable [B] Apply sales tax.

AssignAWO2Payor [B] Assign auto write-off to payor.

Available Indexes

CPTOfficeCodeKey. OfficeCode / ProfileID

CPTKey. CPTCode / ProfileID

CPTNameKey. Description

Doctors

Description

Doctor information.

Data Fields

DoctorID [I KEY] Numeric primary key for the DOCTORS table.

OfficeID [I KEY] Numeric primary key for the DOCTORS table.

Clinic [C-34] Business name.

FirstName [C-15 KEY]

LastName [C-25 KEY]

Suffix [C-10]

Degree [I] Possible values are:

DEGREE_NONE 0

DEGREE_DC 1

DEGREE_DDS 2

DEGREE_DO 3

DEGREE_DPM 4

DEGREE_MASSAGE 5

DEGREE_MD 6

DEGREE_PT 7

DEGREE_PHD 8

Address [C-35**]

City [C-20**]

State [C-2**]

Zip [C-10**]

Phone [C-12**]

Extension [C-4**]

Fax [C-12**]

Email [C-30**]

EmployerID [C-20**]

SocialSecurity [I**]

UPIN [C-6**]

NPI [C-10**]

GroupID [C-10**]

Medicare [C-20**]

Medicaid [C-25**]

BlueCross [C-15**]

BlueShield [C-15**]

Comments [C-31**]

Available Indexes

DoctorIDKey. OfficeID / DoctorID

DoctorNameKey. LastName / FirstName / OfficeID / DoctorID

DoctorIDs

Description

This table allows relationships to be created between DOCTORS and either PROFILES or PAYORS for the purposes of adding unique id #'s for the myriad of organizations that assign them.

Data Fields

DoctorID [I KEY]

OfficeID [I KEY]

PayorID [I KEY]

ProfileID [I KEY]

PIN [C-24**]

LOSPIN [C-24**] Line of service PIN.

EMCPIN [C-14**] Direct electronic billing PIN.

Commercial [C-14**]

GroupID [C-24**]

FederalTaxID [C-24**]

Special [C-20**]

Available Indexes

DoctorIDKey1. OfficeID / DoctorID / PayorID / ProfileID

DoctorIDKey2. OfficeID / DoctorID / ProfileID / PayorID

DoctorIDKey3. PayorID / OfficeID / DoctorID

DoctorIDKey4. ProfileID / OfficeID / DoctorID

Employers

Description

Employer information.

Data Fields

EmployerID [I KEY]

Company [C-30 KEY]

Address [C-35**]

Address2 [C-35**]

City [C-20**]

State [C-2**]

Zip [C_10**]

Phone [C-12**]

Extension [C-4**]

Fax [C-12**]

Email [C-30**

Available Indexes

EmployerIDKey. EmployerID

EmployerIDKey. Company / EmployerID

Facilities

Description

This table generally serves as a repository for nursing homes, hospitals, or other treatment centers at which services were rendered.

Data Fields

FacilityID [I KEY]

Facility [C-30 KEY] Name of facility

Address [C-356**]

City [C-20**]

State [C-2**]

Zip [C-10**]

Phone [C-12**]

BillingID [C-15**] Identification # for claim forms.

POS [C-3**] Alternate Place of Sevice code for claims

Available Indexes

FacilityIDKey. FacilityID

FacilityNameKey. Facility / FacilityID

Guarantors

Description

The individual or entity who is ultimately responsible for payment. This may be an insured individual who has third party insurance (such as the patient, a parent, a spouse), or any other responsible party.

Data Fields

GuarantorID [I KEY]

Deductible [F**] This deductible can be overridden at the patient case level.

DeductibleRemaining [F**] How much of the deductible is left for the current period (usually annual).

PayorPercentage [F**] Percentage coverage by payor (see CoverageType).

Copayment [F**] Dollar amount of copayment (see CoverageType)..

PayorID [I KEY]

EmployerID [I KEY]

CoveragePlan [I] Not currently used.

DateOfBirth [D**]

EffectiveDate [D**]

TerminationDate [D**]

SocialSecurity [I**]

HoldToDate [D**]

LastName [C-25 KEY]

FirstName [C-15 KEY]

MiddleInitial [C-1 KEY]

Address [C-35**]

Address2 [C-35**]

City [C-20**]

State [C-2**]

Zip [C-10**]

HomePhone [C-12**]

WorkPhone [C-12**]

WorkPhoneEx [C-4**]

Fax [C-12**]

Email [C-30**]

GroupName [C-20**]

GroupID [C-20**]

PolicyID [C-20**]

PayorContact [C-20**]

Gender [C-1] M = Male, F = Female

CoverageType [C-1] Possible values are:

0 Cash patient (default)

1 Use percentage as patient coverage

2 Distribute copayment all services this date

3 Assign copayment by service

4 Assign a max to payor payment by visit

PaperOnlyFlag [B**] Do not send this guarantor's bills electronically.

HoldStatements [B**]

HoldBilling [B**]

HoldAging [B**]

HoldTracers [B**]

UseDeductible [B**] Not currently used.

Available Indexes

GuarantorKey. GuarantorID

GuarantorNameKey. LastName / FirstName / MiddleInitial / GuarantorID

ICDCodes

Description

ICD-9 database.

Data Fields

ICDCode [C-10 KEY **]

Category [C-25 KEY **]

Description [C-55 KEY **]

Available Indexes

ICDIDKey. ICDCode

ICDDescriptionKey. Description

ICDCategoryKey. Category / ICDCode

Patients

Description

Basic patient information.

Data Fields

PatientID [I KEY]

LastName [C-25 KEY]

FirstName [C-15 KEY]

MiddleInitial [C-1 KEY]

Nickname [C-15**]

Address [C-35**]

Address2 [C-35**]

City [C-20**]

State [C-2**]

Zip [C-10**]

HomePhone [C-12**]

WorkPhone [C-12**]

WorkPhoneExt [C-5**]

Fax [C-12**]

Email [C-30]

Balance [F]

Birthdate [D KEY **]

SocialSecurity [I KEY **]

StartDate [D**]

LastVisit [D]

PrevVisit [D]

ReferralByPatID [I KEY]

GeneralReferralKey [I KEY]

DefaultCase [I]

ReferralSource [I]

MailingLabelType [I] Possible values

0 LBL_FIRSTLAST

1 LBL_TITLEFIRSTLAST

2 LBL_DRFIRSTLAST

    1. LBL_THELASTFAMILY
    2. LBL_MRANDMRSFAMILY

5 LBL_ONLYIFFILTERED

6 LBL_DONOTPRINT

MaritalStatus [I] Possible values:

0 MS_SINGLE

1 MS_MARRIED

2 MS_WIDOWED

    1. MS_DIVORCED
    2. MS_SEPARATED

5 MS_UNKNOWN

ReferralByName [C-35]

Gender [C-1] M = Male, F = Female

Spouse [C-30]

ReleasedFromCare [B]

Deceased [B]

Alerts [B]

UserDefinedKey [C-10 KEY]

BillToOverride [B**]

BillTo [C-30**]

BillToAddress [C-30**]

BillToAddress2 [C-30]

Available Indexes

PatientIDKey. PatientID

PatientLNameKey. LastName / FirstName / MiddleInitial

PatientDOBKey. Birthdate

PatientSocSecKey. SocialSecurity

PatientUserDefKey. UserDefinedKey

PatientFNameKey. FirstName / MiddleInitial / LastName

PatientCases

Description

Each member of this table contains the crucial links for a given set of patient account entries, bill, guarantors, etc.

Data Fields

PatientID [I KEY]

DoctorID [I KEY]

OfficeID [I KEY]

FacilityID [I KEY]

EmployerID [I KEY]

AttorneyID [I KEY]

ProfileID [I KEY]

BillingReferralKey [I KEY]

Deductible1 [F]

DeductibleLeft1 [F]

GuarantorID1 [I KEY]

RelToGuarantor1 [I] Possible values are:

0 REL_NONE

1 REL_SELF

    1. REL_SPOUSE
    2. REL_NAT_CHILD_R
    3. REL_NAT_CHILD_NR
    4. REL_STEP_CHILD
    5. REL_FOSTER_CHILD
    6. REL_WARD_OF_COURT
    7. REL_EMPLOYEE
    8. REL_UNKNOWN
    9. REL_HANDICAPPED
    10. REL_ORGAN_DONOR
    11. REL_CAD_ORGAN_DONOR

UseCaseDeductible1 [B]

Deductible2 [F]

DeductibleLeft2 [F]

GuarantorID2 [I KEY]

RelToGuarantor2 [I] Possible values: see RelToGuarantor1.

UseCaseDeductible2 [B]

Deductible3 [F]

DeductibleLeft3 [F]

GuarantorID3 [I KEY]

RelToGuarantor3 [I] Possible values: see RelToGuarantor1.

UseCaseDeductible3 [B]

Deductible4 [F]

DeductibleLeft4 [F]

GuarantorID4 [I KEY]

RelToGuarantor4 [I] Possible values: see RelToGuarantor1.

UseCaseDeductible4 [B]

CaseBalance [F]

UnbilledCharges [F]

PatientCredits [F]

PatientOwes [F]

PayorOwes [F]

CommentColor [I]

PatientCaseID [I KEY]

HCFAComment [C-40**]

StatementComment [C-70**]

AddServicesComment [C-40]

Occupation [C-35]

CaseDescription [C-25]

Available Indexes

PatientCaseKey. PatientID / PatientCaseID

CaseGuarantorKey. GuarantorID1 / PatientID / PatientCaseID

Payors

Description

Generally, this table contains third party insurance carriers of all types.

Data Fields

PayorID [I KEY]

PayorName [C-35 KEY]

Address [C-35**]

Address2 [C-35**]

City [C-20**]

State [C-2**]

Zip [C-10**]

Phone [C-12**]

Fax [C-12**]

Comments [C-12**]

FeeWaiverComment [C-75**]

ECSPayorID [C-5**]

ECSPayorSubID [C-4**]

NDCRecordCode [C-2**]

Available Indexes

PayorIDKey. PayorID

PayorNameKey. PayorName / PayorID

PersonalInjury

Description

 

Data Fields

PatientID [I KEY]

CaseID [I KEY]

Condition [I KEY]

FormDate [D**]

Adjustor [C-30**]

FileNum [C-20**]

History [C-75**]

History2 [C-75**]

History3 [C-75**]

PrevCondition [B**]

PrevDescription [C-75**]

PrevDescription2 [C-75**]

ThisAccident [B**]

ThisDescription [C-75**]

PermDisability [C-1]

PermDescription [C-75**]

CurrentCare [B**]

MoreServices [B**]

Hospitalized [B**]

ChargesToDate [C-10**]

FutureCharges [C-10**]

IRSID [C-15**]

DoctorName [C-30**]

Duration [C-30**]

Available Indexes

Profiles

Description

 

Data Fields

ProfileID [I KEY]

Description [C-50 KEY **]

Notes [C-60**]

InterestRate [F]

LastInterestDate [D**]

LastBillingDate [D**]

AdvanceWarning [I**]

BillingFrequency [I*]

InterestFrequency [I**]

MaxVisits [I**]

Available Indexes

ProfileIDKey. ProfileID

ProfileNameKey. Description / ProfileID

Referrals

Description

 

Data Fields

ReferralID [I KEY]

FirstName [C-15 KEY]

LastName [C-25 KEY]

Title [C-10**]

Address [C-35**]

City [C-20**]

State [C-2**]

Zip [C-10]

Phone [C-12**]

Extension [C-4**]

Fax [C-12**]

Email [C-30**]

UPIN [C-30**]

PrintOnBills [B**]

Available Indexes

ReferralIDKey. ReferralID

ReferralNameKey LastName / FirstName

SOAP

Description

 

Data Fields

Category [C-1**]

SubCategory [C-1**]

SOAPKey [C-9**]

Key Phrase [C-19**]

Text [C-255**]

Available Indexes

SOAPKey. SOAPKey

SOAPCatKey. Category / SubCategory /SOAPKey

StateForms

Description

 

Data Fields

PatientID [I KEY]

CaseID [I KEY]

MIInjuryCode [C-2**]

MNPriorAuthorization [C-6**]

MNMedicalAssistance [C-15**]

MNTPL [C-2**]

MNInjuryCode [C-2**]

MNBillType [C-1**]

PAResource [C-1**]

PARecipient [C-10**]

Available Indexes

StateFormKey. PatientID / CaseID

Submitters

Description

This NSF header and options file is not available via ODBC.

Data Fields

N/A

Available Indexes

N/A

WorkComp

Description

 

Data Fields

PatientID [I KEY]

CaseID [I KEY]

Condition [I KEY]

FormDate [D**]

Report [C-1**]

WCBCaseNum [C-15**]

CarrierNum [C-15**]

InjuryTime [C-7**]

Location [C-42**]

EmployerPhone [C-12**]

EmployerDescription [C-30**]

SupervisingPhy [C-30**]

SupervisingPhyAdd [C-47**]

SupervisingPhyPhone [C-12**]

PreviousReportDate [D**]

PreviousReport [B**]

PreviousCare [B**]

PreviousDoctor [C-33**]

Xrays [B**]

XrayResults [C-30]

ExamFromDate [D**]

ExamToDate [D**]

FirstTreatmentDate [D**]

NextAppointment [D**]

DateLastWorked [D**]

ResumeLimited [D**]

ResumeFull [D**]

FirstTreatmentTime [C-7**]

MaxImprovement [B**]

PermanentDamage [B**]

PermDamageDescr [C-30]

Working [B**]

DegreeOfDisability [C-1**]

Cause [B**]

CauseDescription [C-50**]

DelayRecovery [B**]

DelayDescription [C-47**]

WorkCapacity [B**]

CapacityDescription [C-47**]

History [C-70**]

SubjectiveComplaints [C-70**]

InjuryLine [C-70**]

InjuryLine2 [C-70**]

Treatment [C-70**]

Treatment2 [C-70**]

Remarks [C-70**]

Remarks2 [C-70**]

Testimony [C-22**]

WCBRating [C-15**]

WCBAuthorization [C-15**]

HospitalStay [C-10**]

WorkStatus [B**]

WorkRestrictions [C-30**]

ToxicCompounds [B**]

TreatmentPlan [C-50**]

PercentageLoss [C-50**]

TreatmentAuthorization [B**]

Disabled [B**]

Available Indexes

WorkCompKey. PatientID / CaseID

 

Send mail to feedback@huntfamily.com with questions or comments about this web site.
Last modified: September 28, 2002