Commercial Required Files and Fields
The following files are needed to import data on a commercial population.
Member
- Captures a members PCP Affiliation, Population Affiliation, Practice Affiliation and Demographic information.
Claim Header
Captures unique Claim Header Data for claims associated to patients.
- Claim files for Health Endeavors will only reflect paid claims in current paid status.
- Claims may be updated/adjusted subsequently and re reported with full replacement files
Claim Line
- Captures unique Claim Line Data for claims associated to patients.
Claim Diagnosis
- Captures Claim Diagnopsis Data for claims/ claim lines associated to patients.
Claim Procedure ICD
- Captures Claim ICD9/ICD10 Procedure Data for claims/ Claim lines associated to patients.
Provider
- Captures Provider for renderuing providers on claims associated to Patients
Pharmacy
- Captures Pharmacy Claim data associated to Patients
Click on each file name below to see the breakdown of information recommended for each file type. Highlighted rows show required data points.
Member
| Field Name | Field Desc | Required Y/N | Data Type | Functional Requirements/Notes |
|---|---|---|---|---|
| MemberID | Unique Member ID | Y | int | Primary Key |
| LastName | Member last name | Y | varchar(100) | |
| FirstName | Member first name | Y | varchar(100) | |
| MiddleName | Member middle name | N | varchar(100) | |
| Gender | Member gender | N | char(1) | M = Male, F = Female, U = Unknown (I.E. Newborn, gender not reported currently) |
| DOB | Member date of birth | N | date | |
| Address | Member Street Address/PO box | N | varchar(500) | |
| City | Member City | N | varchar(100) | |
| State | Member state | N | char(2) | |
| ZipCode | Member zip code | N | char(5) | |
| SubscriberID | Subscriber ID | N | int | Subscriber ID associated to member during timeframe |
| RelationshipCode | Relationship between the member and | Y | int | 1=Subscriber,1=Spouse,3=Dependent |
| PopulationGrouping | Y | varchar(25) | Examples: HMO = Commercial HMO Population, PREMIER-HMO = Commercial HMO Population Premier Product, PPO = Commercial PPO Population, SCP-HMO = Medicare Advantage HMO Population, SCP-PPO = Medicare Advantage PPO Population | |
| EffectiveDate | Effective Date for member's contract | Y | date | |
| ExpirationDate | Expiration Date for member's contract | Y | date | |
| EmployerGroupID | Unique Employer Group ID | N | int | |
| EmployerGroupName | N | varchar(100) | ||
| PrimaryPracticeTIN | Y | varchar(15) | ||
| PrimaryPracticeName | N | varchar(100) | ||
| PrimaryProviderNPI | Primary Providers NPI number | Y | varchar(15) |
ClaimHeader
Joins to Members by MemberID
| Field Name | Field Desc | Required Y/N | Data Type | Functional Requirements/Notes |
|---|---|---|---|---|
| ClaimID | Unique Claim Number ID | Y | varchar(100) | Primary Key |
| MemberID | Unique Member ID | Y | varchar(100) | Foreign Key Join to Member ID in Members Table |
| FacilityProviderNPI | Identifies the facility associated with the claim | N | varchar(15) | |
| OperatingProviderNPI | Identifies the operating provider associated with the claim | N | varchar(15) | |
| AttendingProviderNPI | Identifies the attending provider associated with the claim | N | varchar(15) | |
| ClaimTypeCode | Signifies the type of claim being submitted | Y | int | Claim type codes are: 10=HHA claim 20=Non swing bed SNF claim 30=Swing bed SNF claim 40=Outpatient claim 50=Hospice claim 60=Inpatient claim 61=Inpatient “FullEncounter” claim |
| BillFacilityTypeCode | The first digit of the type of bill (TOB1) is used to identify the type of facility that provided care to the beneficiary (e.g., hospital or SNF) | N | int | Claim Facility Type Codes are: 1=Hospital 2=SNF 3=HHA 4=Religious non-medical (hospital) 5=Religious non-medical (extended care) 6=Intermediate care 7=Clinic or hospital-based renal dialysis facility 8=Specialty facility or Ambulatory Surgical Center (ASC) surgery 9=Reserved |
| BillClassificationCode | The second digit of the type of bill (TOB2) is used to indicate with greater specificity where the service was provided (e.g., a department within a hospital) | N | int | ResDAC Claim Service Classification Type Code |
| DRGCode | Indicates the diagnostic related group to which a hospital claim belongs for prospective payment purposes. | N | varchar(8) | |
| AdmissionTypeCode | Indicates the type and priority of inpatient services. | N | int | Claim Admission Type Codes are: 0=Blank 1=Emergency 2=Urgent 3=Elective 4=Newborn 5=Trauma Center 6-8=Reserved 9=Unknown |
| AdmissionSourceCode | Indicates the source of the beneficiary’s referral for admission or visit (e.g., a physician or another facility). | N | char(1) | ResDAC Inpatient Admission Code |
| PrincipalDiagnosisCode | Identifies the beneficiary’s principal illness or disability | N | varchar(8) | ICD-9/ICD-10 |
| AdmittingDiagnosisCode | Identifies the illness or disability for which the beneficiary was admitted | N | varchar(8) | ICD-9/ICD-10 |
| ICDVersionTxt | Identifies ICD9 vs ICD10 Code for the principal and admitting diagnosis codes | N | int | 9 = ICD-9 0 = ICD-10 |
| DischargeStatusCode | Indicates the patient's discharge status as of the Claim Through Date. For example, it may indicate where a patient was discharged to (e.g., home, another facility) or the circumstances of a discharge (e.g., against medical advice, or patient death). | N | int | ResDAC Patient Discharge Status Code |
ClaimLine
Joins to ClaimHeader by ClaimID
| Field Name | Field Desc | Required Y/N | Data Type | Functional Requirements/Notes |
|---|---|---|---|---|
| ClaimID | Unique Claim ID | Y | int | Foreign Key Join to Claim Header Table |
| LineNbr | Claim Line Number | Y | int | ServiceBeginDate field in ClaimLine MUST fall between Effective Date and Expiration Date fields of the member |
| RenderingProviderNPI | Identifies the provider rendering the indicated service on the claim line | Y | varchar(15) | |
| ServiceBeginDate | The date the service associated with the line item began | Y | date | |
| ServiceEndDate | The date the service associated with the line item ended | Y | date | |
| TypeOfServiceCode | Indicates the type of service (e.g., consultation, surgery) provided to the beneficiary | N | char(1) | CMS Type of Service Code |
| PlaceOfServiceCode | Indicates the place where the indicated service was provided (e.g., ambulance, school) | N | varchar(8) | CMS Place of Service Code |
| RevenueCode | The number a provider assigns to the cost center to which a particular charge is billed (e.g., accommodations or supplies). A cost center is a division or unit within a hospital (e.g., radiology, emergency room, pathology). | N | varchar(8) | ResDAC Revenue Center Code |
| CPTProcedureCode | CPT / HCPC Codes | N | varchar(8) | |
| ServiceUnitQuantity | Count of total units, at the line-item level, associated with services needing unit reporting (e.g., anesthesia time units and blood units). | N | decimal(12,2) | |
| ModifierCode1 | Modifier 1 | N | varchar(8) | |
| ModifierCode2 | Modifier 2 | N | varchar(8) | |
| ModifierCode3 | Modifier 3 | N | varchar(8) | |
| ModifierCode4 | Modifier 4 | N | varchar(8) | |
| ChargedAmt | Billed Amount | N | money | |
| AllowedAmt | Allowed amount per provider contract | N | money | |
| DeductibleAmt | Patient deductible amount | N | money | |
| CoinsuranceAmt | Patient coinsurance amount | N | money | |
| CoPayAmt | Patient copay amount | N | money | |
| ToPayAmt | Paid amount | Y | money | Utilized for financial reporting paid totals. |
| PaidDate | Date claim line paid | N | date |
ClaimDiagnosis
Joins to ClaimLine by ClaimID
Joins to ClaimLine by LineNbr (Associates DX Code to specific claim line)
| Field Name | Field Desc | Required Y/N | Data Type | Functional Requirements/Notes |
|---|---|---|---|---|
| ClaimID | Unique Claim ID | Y | int | Foreign Key Join to ClaimLine |
| LineNbr | Claim Line Number | Y | int | Foreign Key Join to Claim Line LineNbr field. Associates DX code to specific claim line. |
| DiagnosisOrderNbr | Dx Order Number from Claim Form | Y | int | |
| DiagnosisCode | ICD9 / ICD10 Diagnosis Code | Y | varchar(8) | |
| ICDVersionTxt | Identifies ICD9 vs ICD10 Code | Y | varchar(10) | 9 = ICD-9 0 = ICD-10 |
ClaimHeader
Joins to ClaimHeader by ClaimID
| Field Name | Field Desc | Required Y/N | Data Type | Functional Requirements/Notes |
|---|---|---|---|---|
| ClaimID | Unique Claim ID | Y | int | Foreign Key Join to ClaimHeader |
| ProcOrderNbr | Px Order Number from Claim Form | Y | int | |
| ProcedureCode | ICD9 / ICD10 Procedure Code | Y | varchar(8) | |
| ICDVersionTxt | Identifies ICD9 vs ICD10 Code | Y | varchar(10) | 9 = ICD-9 0 = ICD-10 |
ClaimHeader
| Field Name | Field Desc | Required Y/N | Data Type | Functional Requirements/Notes |
|---|---|---|---|---|
| NPI | Provider NPI | Y | varchar(15) | |
| FullName | Provider Full Name | Y | varchar(100) |
ClaimHeader
| Field Name | Field Desc | Required Y/N | Data Type | Functional Requirements/Notes |
|---|---|---|---|---|
| MemberID | Member ID | Y | int | Foreign Key join to Members Table |
| ClaimID | Unique Pharmacy Claim ID | Y | int | Primary Key |
| NDCDrugCode | NDC Number | Y | varchar(11) | |
| PrescriberNPI | Identifies the prescriber associated with the claim | Y | varchar(15) | |
| PharmacyName | N | varchar(42) | ||
| DispensedQuantityNbr | Count of total units, at the line-item level, associated with services needing unit reporting (e.g., anesthesia time units and blood units) | Y | decimal(12,2) | |
| DaysSupplyNbr | The number of days the supply of medication dispensed by the pharmacy will cover | Y | int | |
| FilledDate | Y | date | ||
| PaidAmt | Y | money | ||
| NewRefillNbr | Designates the sequential order of the original fill or subsequent refills of a prescription. | N | int | 0 represents first fill |