Epic Willow Data Specification
Version: v1
Release Date: September 27, 2024
Introduction
This is the data specification for the information ControlCheck requires for Epic Willow Ambulatory. ControlCheck ingests Epic Willow Ambulatory data from hospitals and invoicing data from the hospital’s Wholesalers. ControlCheck then compares the two sets of data to ensure that all invoices of controlled substances have a corresponding receival / load for an equal amount.
Data Extract
Customers are strongly encouraged to use the template Clarity Reporting query provided as the fastest path to a compliant data feed. The canned query can be found attached to this post below. Customers may opt to author their own query to extract the required data if the output meets the following data specification. The customer is solely responsible for any issues involving a custom query. If your hospital cannot meet the following data specification, please discuss this with your implementation specialist or contact help@bluesight.com.
File Transport
Epic Willow files can be uploaded via the ControlCheck user interface; however, delivery over SFTP is highly preferred. Most customers choose to transfer files once per day. File transfers are typically scheduled to occur after midnight and contain the previous day’s data. Data transfers should be scheduled after the Clarity ETL process has been completed.
Recommended file transfer times are from 0400-0600 to ensure data has migrated to the appropriate servers.
A single file should contain multiple facilities. Discuss with your implementation specialist before finalizing hospital groupings.
File Requirements
- Files must be formatted with pipe-delimiters and as .csv that conform to RFC 4180 and utilize UTF-8 for character encoding
- File size should not exceed 50 mb
- The file name should follow the schema and can only contain letters, numbers, and underscores.
- bluesight_IDNname_hospitalname_epic_retail_date.csv
- Output column headers are case sensitive and must match the values specified below. The order in which those columns appear can be different than the order listed below. However, it is highly recommended that customers follow the order below for consistency and readability.
Data Requirements
Pharmacy Information
This category includes headers that provide details about the pharmacy involved in dispensing medications. It includes:
Header | Clarity Table.Column Name | Data Type | Format | Description of Data Element |
|---|---|---|---|---|
Dispensing Pharmacy NPI | RX_PHR.NPI | VARCHAR | Alphanumeric | National Provider Identifier for the dispensing pharmacy. |
PHARMACY_ID | v_rxinv_summary_levels.PHARMACY_ID | INTEGER | Numeric | Unique identifier for the pharmacy. |
PHARMACY_NAME | RX_PHR.PHARMACY_NAME | VARCHAR | Text | Name of the pharmacy. |
PHARMACY_TYPE | ZC_PRIM_OP_MODE.NAME | VARCHAR | Text | Type of pharmacy (e.g., retail, mail order, specialty). |
Balance and Transaction Information
This category encompasses headers that track inventory balances and transaction details related to medication updates. It includes:
Header | Clarity Table.Column Name | Data Type | Format | Description of Data Element |
|---|---|---|---|---|
BALANCE_NAME | bal_prd_balance.BALANCE_NAME | VARCHAR | Text | Name of the inventory balance or product. |
UPDATE_ORDER_ID | BAL_TRAIL.UPDATE_ORDER_ID | VARCHAR | Alphanumeric | Order ID related to the balance update. |
BAL_UPDATE_DATETIME | BAL_TRAIL.UPDATE_DATETIME | DATETIME | YYYY-MM-DD HH:MM | Date and time when the balance update occurred. |
INTERNAL_USER_ID | BAL_TRAIL.UPDATE_USER_ID | VARCHAR | Alphanumeric | Internal ID of the user who updated the balance. |
USER_EXEC_BALANCE | CLARITY_EMP.NAME | VARCHAR | Text | Name of the user who executed the balance update. |
UPDATE_TYPE | ZC_UPDATE_TYPE.name | VARCHAR | Text | Type of update performed on the balance. |
UPDATE_REASON | ZC_UPDATE_REASON_3.name | VARCHAR | Text | Reason for the balance update. |
UPDATE_AMT_CHG | BAL_TRAIL.UPDATE_AMT_CHG | NUMERIC | Decimal (18,2) | Amount of change in balance due to the update. |
UPDATE_QTY_DIFF | BAL_TRAIL.UPDATE_QTY_DIFF | NUMERIC | Decimal (18,2) | Quantity difference resulting from the update. |
UPDATE_ENDING_BAL | BAL_TRAIL.UPDATE_ENDING_BAL | NUMERIC | Decimal (18,2) | Ending balance after the update. |
Provider Information
This category contains headers that provide information about healthcare providers involved in the patient's care or medication orders. It includes:
Header | Clarity Table.Column Name | Data Type | Format | Description of Data Element |
|---|---|---|---|---|
PROV_ID | CLARITY_SER.PROV_ID | VARCHAR | Alphanumeric | Unique identifier for the provider. |
PROV_NAME | CLARITY_SER.PROV_NAME | VARCHAR | Text | Name of the provider. |
PROV_TYPE | CLARITY_SER.PROV_TYPE | VARCHAR | Text | Type of provider (e.g., physician, nurse, pharmacist). |
ORD_PROV_ID | ORDMED.ORD_PROV_ID | VARCHAR | Alphanumeric | Identifier for the ordering provider. |
ORD_PROV_NPI | ser2.NPI | VARCHAR | Alphanumeric | National Provider Identifier for the ordering provider. |
ORD_PROV_NAME | ser_ord.PROV_NAME | VARCHAR | Text | Name of the ordering provider. |
ORD_PROV_TYPE | ser_ord.PROV_TYPE | VARCHAR | Text | Type of ordering provider. |
Patient Information
This category consists of headers that provide details about the patient involved in the medication order. It includes:
Header | Clarity Table.Column Name | Data Type | Format | Description of Data Element |
|---|---|---|---|---|
PAT_ID | PATIENT.PAT_ID | VARCHAR | Alphanumeric | Unique identifier for the patient. |
PAT_MRN_ID | PATIENT.PAT_MRN_ID | VARCHAR | Alphanumeric | Medical record number of the patient. |
PAT_NAME | PATIENT.PAT_NAME | VARCHAR | Text | Name of the patient. |
PAT_ENC_CSN_ID | ORDMED.PAT_ENC_CSN_ID | VARCHAR | Alphanumeric | Encounter ID for the patient's order. |
ORDER_STATUS | ZC_ORDER_STATUS.NAME | VARCHAR | Text | Status of the patient's order (e.g., active, completed). |
PAT_TYPE | ZC_ORDERING_MODE.NAME | VARCHAR | Text | Type of patient (e.g., inpatient, outpatient). |
NDC Information
This category includes headers related to the National Drug Code (NDC), which uniquely identifies medications. It includes:
Header | Clarity Table.Column Name | Data Type | Format | Description of Data Element |
|---|---|---|---|---|
NDC_CODE | NDC.NDC_CODE | VARCHAR | Alphanumeric | National Drug Code identifying the medication. |
RAW_11_DIGIT_NDC | NDC.RAW_11_DIGIT_NDC | VARCHAR | Alphanumeric | Raw 11-digit format of the National Drug Code. |
Prescription Information
This category contains headers that provide information about the prescribed medications, their details, and administration. It includes:
Header | Clarity Table.Column Name | Data Type | Format | Description of Data Element |
|---|---|---|---|---|
Prescription_Date | ORDMED.ORDERING_DATE | DATETIME | YYYY-MM-DD | Date when the prescription was ordered. |
Prescription_Description | ORDMED.DESCRIPTION | VARCHAR | Text | Description of the prescribed medication. |
Medication_Name | VARCHAR | Text | Name of the medication. | |
STRENGTH_OF_MED | info.STRENGTH | VARCHAR | Text | Strength or dosage of the medication. |
Med_Start_Date | ORDMED.START_DATE | DATETIME | YYYY-MM-DD | Start date of the medication. |
Med_End_Date | ORDMED.END_DATE | DATETIME | YYYY-MM-DD | End date of the medication. |
NUM_OF_REFILLS | ORDMED.REFILLS | INTEGER | Numeric | Number of refills remaining for the prescription. |
MED_FREQ_NAME | freq.DISPLAY_NAME | VARCHAR | Text | Frequency of medication administration. |
MED_ROUTE_NAME | info.ROUTE | VARCHAR | Text | Route of medication administration (e.g., oral, intravenous). |
Coverage Information
This category provides headers that describe insurance coverage details for the medication order. It includes:
Header | Clarity Table.Column Name | Data Type | Format | Description of Data Element |
|---|---|---|---|---|
FIRSTCOVERAGEID | FirstCoverage.RX_COVERAGES_ID | VARCHAR | Alphanumeric | ID of the first coverage. |
PAYOR_1_ID | COVERAGES1.PAYOR_ID | VARCHAR | Alphanumeric | ID of the first payor. |
PAYOR_1_NAME | CLARITY_EPM1.PAYOR_NAME | VARCHAR | Text | Name of the first payor. |
BENEFIT_PLAN_1_ID | COVERAGES1.PLAN_ID | VARCHAR | Alphanumeric | ID of the first benefit plan. |
BENEFIT_PLAN_1_NAME | CLARITY_EPP1.BENEFIT_PLAN_NAME | VARCHAR | Text | Name of the first benefit plan. |
SECONDCOVERAGEID | SecondCoverage.RX_COVERAGES_ID | VARCHAR | Alphanumeric | ID of the second coverage. |
PAYOR_2_ID | COVERAGES2.PAYOR_ID | VARCHAR | Alphanumeric | ID of the second payor. |
PAYOR_2_NAME | CLARITY_EPM2.PAYOR_NAME | VARCHAR | Text | Name of the second payor. |
BENEFIT_PLAN_2_ID | COVERAGES2.PLAN_ID | VARCHAR | Alphanumeric | ID of the second benefit plan. |
BENEFIT_PLAN_2_NAME | CLARITY_EPP2.BENEFIT_PLAN_NAME | VARCHAR | Text | Name of the second benefit plan. |
THIRDCOVERAGEID | ThirdCoverage.RX_COVERAGES_ID | VARCHAR | Alphanumeric | ID of the third coverage. |
PAYOR_3_ID | COVERAGES3.PAYOR_ID | VARCHAR | Alphanumeric | ID of the third payor. |
PAYOR_3_NAME | CLARITY_EPM3.PAYOR_NAME | VARCHAR | Text | Name of the third payor. |
BENEFIT_PLAN_3_ID | COVERAGES3.PLAN_ID | VARCHAR | Alphanumeric | ID of the third benefit plan. |
BENEFIT_PLAN_3_NAME | CLARITY_EPP3.BENEFIT_PLAN_NAME | VARCHAR | Text | Name of the third benefit plan. |
FIRSTPAYORCHARGEAMOUNT | First | VARCHAR | Alphanumeric | Charge amount.0 |