Drug Carveouts

Drug carveout provisions capture the reimbursement terms for high-cost pharmaceuticals billed separately from the base DRG or facility rate. Identified by revenue code 636 (IV solutions and pharmacy charges) in the MRF.

Example: A contract pays MS-DRG rates for the hospitalization but separately reimburses chemotherapy drugs at 80% of billed charges.

See also: Implant Carveouts

---

Classification

Field	Values	Description
provision_type	High Cost Drugs	Fixed value for all drug carveout rows
provision_value	DOUBLE	Reimbursement rate (percentage of charges)
setting	Inpatient, Outpatient	Claim setting

---

Architecture

SOURCE SYSTEMS
├─ Core Rates        tq_production.public_*.core_rates
└─ Hospital Rates    glue.hospital_data.hospital_rates

        ↓

Core Rates path (negotiated_type = 'percentage', rev code 636)
├─ provisions_drugs_core_rates_base_{subversion}
└─ provisions_drugs_aggregated_core_rates_{subversion}

Hospital MRF path (rev code 636)
├─ provisions_drugs_hospital_rates_base_{subversion}
└─ provisions_drugs_aggregated_hospital_rates_{subversion}

        ↓  UNION

provisions_drugs_combined_{subversion}

---

Pipeline

Rates from two sources — core rates and hospital MRF rates — are extracted, aggregated, and unioned into a single output table.

Step	Table	Description
1	provisions_drugs_core_rates_base	Raw core rates filtered to revenue code 636 with negotiated_type = 'percentage'
2	provisions_drugs_hospital_rates_base	Hospital MRF rates filtered to revenue code 636 with contract_methodology = 'percent of total billed charges', Commercial payer class only
3	provisions_drugs_aggregated_core_rates	Core rates aggregated to (payer, provider, network, setting) level — computes min, max, avg, median, mode
4	provisions_drugs_aggregated_hospital_rates	Hospital rates aggregated to (payer, provider, network, setting) level — same statistics
5	provisions_drugs_combined	Sources cross-validated, canonical rate selected, and unioned into one row per contract group; feeds into provisions_final as High Cost Drugs

---

Setting Classification

Setting is derived from the service_code (CMS place-of-service code) on each rate record, not from the revenue code itself. A rate with a NULL or empty service code is emitted into both settings via UNION ALL.

Setting	Service Codes
Outpatient	01, 10, 11, 20, 22, 23, 24, 49, 62, 65, 72, 81, NULL / empty
Inpatient	21, 25, 34, 55, 61, NULL / empty

---

Rate Normalization

Raw percentage values are normalized to a [1, 100] scale before aggregation. Rates outside this range are set to NULL and excluded.

Core rates — negotiated_rate: if the raw value is less than 1, it is multiplied by 100 (decimal form). Result is rounded to 2 decimal places.

Hospital rates — negotiated_percentage: same decimal-to-percent conversion. If negotiated_percentage is NULL but negotiated_dollar and gross_charge are both present and positive, the percentage is derived as (negotiated_dollar / gross_charge) × 100.

---

Aggregation

Each base table is aggregated to the (payer_id, provider_id, network_id, billing_code, setting) grain. For every group the following statistics are computed:

Column	Description
min_rate	Minimum rate across all source rows
max_rate	Maximum rate
avg_rate	Mean rate (rounded to 2 decimal places)
median_rate	Median rate
mode_rate	Most frequently occurring rate
num_rates	Count of distinct rate values
rate_id_count	Count of distinct source rate IDs

---

Canonical Rate Selection

After aggregation the two sources are unioned and a single canonical rate is chosen per contract group (payer_id, provider_id, network_id, setting).

Cross-validation — a rate metric (max or median) is marked as validated when both the payer source and the hospital source agree within 5 percentage points and the rate is below 100.

Best rate priority (applied independently per source, then across both):

1. max_rate — if cross-validated
2. median_rate — if cross-validated
3. max_rate < 100 (not validated, but within expected range)
4. median_rate < 100
5. max_rate (fallback, any value)
6. median_rate (final fallback)

Source ranking — within each source, revenue code 636 rows are preferred over any other code. Within the same code, validated and in-range rows rank higher; higher best-rate values break ties. The top-ranked row across both sources becomes the canonical row.

The canonical_source field records whether the winning row came from 'payer' (core rates) or 'hospital' (MRF rates). canonical_validated indicates whether the rate was cross-validated.

---

unique_id Construction

unique_id is a string key composed as:

payer_id || network_id || provider_id || billing_code (leading zeros stripped) || setting_code || in_clear_rates_flag

Where setting_code is 'OP' or 'IP' and in_clear_rates_flag is '1' if the network was matched in the Clear Rates spine, '0' otherwise.

Combined output schema

Column	Type	Description
payer_id	VARCHAR
payer_name	VARCHAR
provider_id	VARCHAR
provider_name	VARCHAR
network_id	BIGINT
network_name	VARCHAR
setting	VARCHAR	'Inpatient' or 'Outpatient'
in_clear_rates	BOOLEAN	Whether the network was matched in the Clear Rates spine
canonical_unique_id	VARCHAR	unique_id of the winning source row (becomes unique_id in final)
canonical_rate	DOUBLE	Selected reimbursement rate (becomes provision_value in final)
canonical_source	VARCHAR	'payer' or 'hospital' — which source the canonical rate came from
canonical_billing_code	VARCHAR	Revenue code from the canonical source row
canonical_metric	VARCHAR	'max' or 'median' — which statistic was used
canonical_validated	BOOLEAN	Whether the rate was cross-validated across both sources
hospital_best_rate	DOUBLE	Best rate from the hospital source
hospital_metric_used	VARCHAR	Metric used for hospital best rate
payer_best_rate	DOUBLE	Best rate from the payer (core rates) source
payer_metric_used	VARCHAR	Metric used for payer best rate
hospital_source_id	BIGINT	Source hospital rate ID for traceability
payer_source_id	BIGINT	Source payer rate ID for traceability
canonical_source_id	BIGINT	Source ID of the canonical row

---

Revenue Code Reference

Revenue Code	Description
636	Drugs requiring detailed coding — IV solutions, pharmacy charges, high-cost pharmaceuticals