Epic Data Specification
Version: v7
Release Date: July 24, 2024
Introduction
This is the data specification for the information ControlCheck (formally known as Bluesight for Controlled Substances) requires for Epic 2022 release integrations.
ControlCheck ingests Epic data from hospitals and dispensing data from the hospital’s automated dispensing cabinets. ControlCheck then compares the two sets of data to ensure that all dispenses of controlled substances have a corresponding administration 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 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 1) data has migrated to the appropriate servers and 2) that Bluesight is able to process files in a timely manner prior to customer auditing. Additionally, it is recommended that the EMR file be sent at least 30 minutes before the ADC file(s) for optimized processing and data ingestion.
Individual files should be generated per facility, although there are specific use cases that would require including 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 https://www.rfc-editor.org/rfc/rfc4180 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_healthsystem_hospitalname_epic_admindate.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
This information related to drug administrations should originate from administration event information found in the Clarity Report table MAR_ADMIN_INFO.
Facility Information
Facility information includes the specific location and department where the drug was administered. This information is used to filter out locations/departments that will not use ControlCheck and as an input to our algorithms related to pairing events and IRIS scores.
Output Column Name | Clarity Table.Column Name | Data type | Format | Description of Data Element |
---|---|---|---|---|
FACILITY_ID | CLARITY_DEP.REV_LOC_ID | Varchar | The unique id of the facility in which the medication was administered. | |
FACILITY_NAME | CLARITY_LOC.LOC_NAME | Varchar | The unique name of the facility in which the medication was administered. | |
DEPARTMENT_ID | MAR_ADMIN_INFO.MAR_ADMIN_DEPT_ID | Numeric | The unique id of the department in which the medication was administered. | |
DEPARTMENT_NAME | CLARITY_DEP.DEPARTMENT_NAME | Varchar | The unique name of the department in which the medication was administered. |
Patient Information
ControlCheck requires a patient identifier that is shared between Epic and the hospital’s dispensing system. Confirm with your interface team and/or application which patient identifier is shared between systems and share that information with your implementation specialist. As a best practice, customers should provide the patient CSN, HAR, and MRN to increase the efficacy of our matching algorithms. Only valid patients should be included (VALID_PATIENT.IS_VALID_PAT_YN = ‘Y’ and PATIENT_3.IS_TEST_PAT_YN = ‘N’).
Output Column Name | Clarity Table.Column Name | Data Type | Format | Description of Data Element |
---|---|---|---|---|
MAR_CSN_ID | MAR_ADMIN_INFO.MAR_ENC_ID or CLARITY_ADT.PAT_ENC_CSN_ID when first value not available | Numeric | Unique ID of that patient encounter associated with the administration. | |
HAR_CSN_ID | HSP_ACCOUNT.PRIM_ENC_CSN_ID | Numeric | Unique ID for the encounter CSN | |
HAR_ID | PAT_ENC.HSP_ACCOUNT_ID | Numeric | Unique hospital account ID for that patient and that encounter associated with the administration. | |
PAT_MRN_ID | PATIENT.PAT_ID | Numeric | Unique patient identifier for the patient receiving the medication. | |
PAT_NAME | PATIENT.PAT_NAME | Varchar | Name of the patient receiving the medication. |
Drug Administration Information
Output Column Name | Clarity Table.Column Name | Data Type | Format of Data Element | Description of Data Element |
---|---|---|---|---|
ORDER_ID | ORDER_MED.ORDER_MED_ID | Numeric | The unique ID for the order associated with the administration. | |
TAKEN_TIME | MAR_ADMIN_INFO.TAKEN_TIME | DateTime | Calendar dates and times with time zone designators; YYYY-MM-DD hh:mm:ss | Time the medication was administered as indicated by the provider. Based on local time of facility. |
SCHED_TIME | MAR_ADMIN_INFO.SCHEDULED_TIME | DateTime | Calendar dates and times with time zone designators; YYYY-MM-DD hh:mm:ss | Time the medication was originally scheduled to be taken. Based on local time of facility. |
SAVED_TIME | MAR_ADMIN_INFO.SAVED_TIME | DateTime | Calendar dates and times with time zone designators; YYYY-MM-DD hh:mm:ss | Time the administration was documented by the provider. Based on local time of facility. |
ORIG_DUE_TIME | MAR_ADMIN_INFO.MAR_ORIG_DUE_TIME | DateTime | Calendar dates and times with time zone designators; YYYY-MM-DD hh:mm:ss | Time the medication was original due. Based on local time of facility. |
WAS_EDITED | when MAR_ADMIN_INFO.EDITED_LINE is not NULL then ‘Y’ else ‘N’ | Boolean | Y or N | Indicates whether the admin record was later edited. |
MAR_ACTION_ID | ZC_EDIT_MAR_RSLT.INTERNAL_ID | Numeric | Unique ID for the administration action type. | |
MAR_ACTION | ZC_EDIT_MAR_RSLT.NAME | Varchar | Name of the administration action type (e.g. given, new bag, etc.) | |
ORDER_ERX_ID | CLARITY_MEDICATION.NAME | Numeric | Unique medication identifier for the drug administered. | |
ORDER_MED_NAME | CLARITY_MEDICATION.MEDICATION_NAME | Varchar | Drug name for the drug that was administered. | |
ADMIN_AMT | MAR_ADMIN_INFO.SIG | Numeric | The amount of drug administered. | |
ADMIN_UNIT | ZC_MED_UNIT.NAME | Varchar | The unit of measure for the amount of drug documented. | |
ROUTE_ID | ZC_ADMIN_ROUTE.INTERNAL_ID | Numeric | Unique ID for the route of administration. | |
ROUTE | ZC_ADMIN_ROUTE.NAME | Varchar | Name of the route of administration. | |
DRUG_CLASS | ZC_DEA_CLASS_CODE.NAME | Varchar | Pharmaceutical drug class of medication. | |
IS_MIXTURE | when MIX.NAME is not NULL then ‘Y’ else ‘N’ | Boolean | Y or N | Indicates whether the ORDER_ERX_ID is a mixture. |
COMPONENT_ERX_ID | CLARITY_MEDICATION.MEDICATION_ID | Numeric | The unique identifier for the component drug of the mixture. | |
COMPONENT_NAME | CLARITY_MEDICATION.NAME | Varchar | The name of the component drug in the mixture. | |
COMPONENT_ADMIN_AMT | ORDER_MEDMIXINFO.MIN_CALC_DOSE_AMT | Numeric | The amount of the component drug administered. | |
COMPONENT_ADMIN_UNIT | ZC_MED_UNIT.NAME | Varchar | The unit of measure for the amount of component drug documented. | |
COMPONENT_DRUG_CLASS | ZC_DEA_CLASS_CODE.NAME | Varchar | Pharmaceutical drug class of component drug | |
Provider Information
Output Column Name | Clarity Table.Column Name | Data Type | Format | Description of Data Element |
---|---|---|---|---|
USER_ID | CLARITY_EMP.USER_ID | Varchar | Epic user ID of the provider that administered. | |
USER_LOGIN | CLARITY_EMP.SYSTEM_LOGIN | Varchar | Provider user ID shared with ADS. | |
USER_NAME | CLARITY_SER.PROV_NAME | Varchar | Name of the provider that administered. | |
USER_ROLE | ZC_PROV_TYPE.NAME | Varchar | Provider’s role at the time they administered the medication. | |
NPI_NUM | CLARITY_SER2.NPI | Numeric | Provider’s NPI number. | |
DEA_NUM | CLARITY_SER.DEA_NUMBER | Numeric | Provider’s DEA number. | |
SEC_USER_ID | MAR_ADMIN_INFO.SEC_USER_ID | Numeric | Secondary Provider’s user ID. | |
SEC_USER_LOGIN | CLARITY_EMP.SYSTEM_LOGIN | Varchar | Secondary Provider’s user ID shared with ADS. | |
SEC_USER_NAME | CLARITY_EMP.NAME | Varchar | Secondary Provider’s name. If a secondary provider is involved. | |
SEC_USER_ROLE | ZC_USER_TYPES.NAME | Varchar | Secondary Provider’s role. | |
ORD_SIGNED_USER_ID | ORDER_MED_ORD_CREATR_USER_ID | Numeric | Originally signed provider’s user ID. | |
ORD_SIGNED_USER_LOGIN | CLARITY_EMP.SYSTEM_LOGIN | Varchar | Shared identifier with ADS system. | |
ORD_SIGNED_USER_NAME | CLARITY_EMP.NAME | Varchar | Originally signed provider’s name. | |
ORD_SIGNED_USER_ROLE | ZC_USER_TYPES.NAME | Varchar | Originally signed provider’s role. |
Dispense Information
Output Column Name | Clarity Table.Column | Date Type | Format | Description of Data Element |
---|---|---|---|---|
DISP_PHARM_ID | RX_PHR.PHARMACY_OD | Numeric | Unique ID of the pharmacy location from whence the drug was dispensed. | |
DISP_PHARMACY | RX_PHR.PHARMACY_NAME | Varchar | Name of the pharmacy location from whence the drug was dispensed. | |
DISP_TYPE | ZC_ORD_CNTCT_TYPE.NAME | Varchar | Order dispense type | |
Version Change Log
- Version 5: Adjusted query header to include USER_LOGIN
- Version 6: After WHERE clause, adjusted Override column to be MAR.OVRD_LINK_STATUS_C instead of MAR.ORD_LINK_STATUS_C
- Version 7: Reverts SELECT statement to SELECT DISTINCT statement to reduce file size