ECLIPSE File Layouts for ODBC Users

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

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

LBL_THELASTFAMILY

LBL_MRANDMRSFAMILY

5 LBL_ONLYIFFILTERED
6 LBL_DONOTPRINT

MaritalStatus [I] Possible values:

0 MS_SINGLE
1 MS_MARRIED
2 MS_WIDOWED

MS_DIVORCED

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

REL_SPOUSE

REL_NAT_CHILD_R

REL_NAT_CHILD_NR

REL_STEP_CHILD

REL_FOSTER_CHILD

REL_WARD_OF_COURT

REL_EMPLOYEE

REL_UNKNOWN

REL_HANDICAPPED

REL_ORGAN_DONOR

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

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

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