Epic Extract Configuration for Research Billing | Engram Clinical

Overview: Epic to Invoice Pipeline

Getting clean charge data from Epic is the foundation of accurate sponsor invoicing. This guide covers the specific report configuration, required columns, filters, and CTMS mappings used by high-performing research billing teams.

Epic Clarity

Source charges

Extract/Report

Filter & format

CTMS Match

Link to subjects

Classification

Apply MCA

Invoice

Sponsor billing

Required Report Columns

Your Epic extract must include these fields for complete research billing workflow support. Required fields are essential; optional fields improve matching accuracy and audit trail.

Column Name	Description	Required
PAT_MRN_ID	Patient identifier for linking to CTMS enrollment
PAT_NAME	Patient name for verification
SERVICE_DATE	Date charge was incurred
PROC_CODE	CPT/HCPCS procedure code
PROC_NAME	Procedure description
QUANTITY	Number of units
CHARGE_AMOUNT	Charge amount in dollars
DEPT_ID	Department where service occurred
ORDERING_PROV	Ordering provider name	Optional
RESEARCH_FLAG	Epic research account indicator	Optional
COVERAGE_ID	Coverage/insurance plan ID	Optional
AUTH_PROV	Authorizing provider	Optional

Recommended Filters

Apply these filters to reduce noise and focus on research-relevant charges:

Date Range

Pull charges for the billing period plus 7 days on each end to catch charges posted late or with adjusted service dates.

Department Filter

Limit to departments with active research protocols. Maintain a department list in your billing system and update quarterly.

Account Type

If your institution uses research-specific account types or coverage classes, filter to those first. This dramatically reduces volume.

Charge Status

Include only "Posted" or "Final" charges. Exclude pending, voided, or adjusted-off charges.

CTMS Field Mapping

To link Epic charges to protocol subjects, you need consistent identifier mapping between systems. Here are the standard mappings for common CTMS platforms:

CTMS	Subject ID Field	Visit Field	Date Field
OnCore	Subject ID	Visit Name	Visit Date
Velos eResearch	Participant ID	Event Name	Event Date
Medidata Rave	Subject Number	Visit	Visit Date
REDCap	record_id	redcap_event_name	Custom date field

Sample SQL Query

Here's a starting point for Clarity-based extraction. Modify table and column names for your instance:

SELECT 
  p.PAT_MRN_ID,
  p.PAT_NAME,
  t.SERVICE_DATE,
  t.PROC_CODE,
  eap.PROC_NAME,
  t.QUANTITY,
  t.TX_AMOUNT as CHARGE_AMOUNT,
  d.DEPARTMENT_NAME as DEPT_ID,
  prov.PROV_NAME as ORDERING_PROV
FROM CLARITY_TDL_TRAN t
JOIN PATIENT p ON t.PAT_ID = p.PAT_ID
JOIN CLARITY_EAP eap ON t.PROC_ID = eap.PROC_ID
JOIN CLARITY_DEP d ON t.DEPT_ID = d.DEPARTMENT_ID
LEFT JOIN CLARITY_SER prov ON t.ORDERING_PROV_ID = prov.PROV_ID
WHERE t.SERVICE_DATE BETWEEN :start_date AND :end_date
  AND t.POST_DATE IS NOT NULL
  AND t.TX_TYPE_C = 1  -- Charges only
  AND d.DEPARTMENT_ID IN (:research_dept_list)
ORDER BY t.SERVICE_DATE, p.PAT_MRN_ID

Common Issues & Solutions

Duplicate Charges

Epic may report the same charge multiple times if there are reversals and rebills. Deduplicate by grouping on (PAT_MRN_ID, SERVICE_DATE, PROC_CODE) and summing QUANTITY and CHARGE_AMOUNT.

Missing Research Flag

Not all institutions use Epic's research account indicator consistently. If your RESEARCH_FLAG field is unreliable, match to CTMS enrollment as the source of truth for research subject identification.

Date Mismatches

SERVICE_DATE and POST_DATE can differ by days or weeks. Always use SERVICE_DATE for protocol visit matching; POST_DATE only for accounting period reconciliation.

Automate your Epic integration

Engram Clinical connects directly to Epic extracts, automatically matching charges to subjects and applying MCA classifications.

Request Demo Download Templates