Epic Data Specification

[Deleted User]

Version: v7.2

Release Date: October 3, 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.

ControlCheck_Epic_Query_V7.2.txt

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_MRN_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_ID	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 in subquery
• Version 7.1: Add PROV_ID to query
• Version 7.2: Adds SELECT DISTINCT as part of main query