Medical Provisions

Medical provisions capture the core reimbursement terms for inpatient and outpatient hospital services — the rates that apply to standard stays and procedures before any stoploss or carveout clause modifies the payment.

---

Classification

Provision Type	Setting	Description	Valid Range
IP Base Rate	Inpatient	Dollar base rate multiplied by the CMS DRG weight to produce the final payment	$1,500 –$50,000
IP Per Diem	Inpatient	Fixed dollar amount per day of admission	$500 –$5,000
IP Percentage	Inpatient	Percentage of charges applied to the total bill	0% – 100%
OP Surg Group	Outpatient	Dollar base rate for a specific Outpatient Procedure Group (OPG)	—
OP Percentage	Outpatient	Percentage of charges applied to outpatient services	0% – 100%

Records outside the valid range for each rate type are dropped.

---

Architecture

SOURCE SYSTEMS
├─ Hospital Rates (MRF)       glue.hospital_data.hospital_rates
└─ Clear Rates & Spines       {clear_rates_schema}.tmp_int_combined_no_whisp_{subversion}

        ↓

INPATIENT
└─ provisions_medical_inpatient_{subversion}
   IP Base Rate · IP Per Diem · IP Percentage

OUTPATIENT
└─ provisions_medical_outpatient_base_rates_{subversion}
   OP Surg Group · OP Percentage

        ↓

provisions_final_{subversion}

---

Inpatient

Inpatient provisions are extracted from MS-DRG hospital rate records in the Clear Rates schema. Each record can yield up to three provision types for a given (provider, payer, network) combination.

Input: {clear_rates_schema}.tmp_int_combined_no_whisp_{clear_rates_subversion}

Output: {target_schema}.provisions_medical_inpatient_{subversion}

Each input row that has at least one valid rate produces one or more output rows — one per non-null rate type. A (payer, provider, network) that has a base rate, a percentage, and a per diem will produce three rows in the final table.

Base rate selection logic

When multiple base rate candidates are available, the following priority order is applied — each candidate is validated against the [$1,500,$50,000] range before selection:

1. canonical_rate_type = 'impute: msdrg_mrf_base_rate_mult_cms_weight' AND score > 1 → use msdrg_mrf_base_rate
2. canonical_rate_type = 'impute: msdrg_base_rate_mult_cms_weight' AND score > 1 → use msdrg_base_rate
3. msdrg_mrf_base_rate_available > 0 → use msdrg_mrf_base_rate
4. msdrg_base_rate_available > 0 → use msdrg_base_rate
5. Otherwise NULL

MRF-sourced rates (options 1 and 3) are preferred over imputed rates when available.

Percentage selection logic

The percentage-of-charges rate is selected from two candidate sources in priority order:

1. canonical_rate_type LIKE 'impute: msdrg_gc_%' AND score > 1 → use msdrg_percentage_candidate_base_rate
2. canonical_rate_type LIKE 'impute: rc_global_gc_%' AND score > 1 → use rc_global_inpatient_base_rate
3. Any msdrg_gc_*_validation_score > 1 → use msdrg_percentage_candidate_base_rate
4. Any rc_global_gc_*_validation_score > 1 → use rc_global_inpatient_base_rate

MS-DRG–sourced percentages are preferred over RC-global rates. Validation scores are checked against hospital charges, Komodo claims, CBSA benchmarks, and state benchmarks (six total score fields for MS-DRG, six for RC-global). At least one must exceed 1 for the rate to qualify.

Per diem selection logic

The per diem is drawn from rc_global_inpatient_per_diem_base_rate — a single candidate source:

1. canonical_rate_type = 'impute: rc_global_per_diem_mult_glos' AND score > 1 → selected
2. rc_global_per_diem_mult_glos_validation_score > 1 → selected as fallback
3. Otherwise NULL

The per diem is validated against the [$500,$5,000] range.

Output schema

Column	Type	Description
payer_id	INT
network_id	BIGINT
provider_id	VARCHAR
base_rate	INT	Validated inpatient base rate (NULL if not available or out of range)
base_rate_n	INT	Number of MS-DRG rows that informed the base rate
percentage	INT	Validated percentage of charges (NULL if not available or out of range)
percentage_n	INT	Number of rows that informed the percentage
per_diem	INT	Validated per diem amount (NULL if not available or out of range)
per_diem_n	INT	Number of rows that informed the per diem

---

Outpatient

Outpatient provisions are organized around Outpatient Procedure Groups (OPGs) — procedure groupings that share the same reimbursement structure. Each provision captures one of two rate types.

Input: {clear_rates_schema}.tmp_int_combined_no_whisp_{clear_rates_subversion}

Output: {target_schema}.provisions_medical_outpatient_base_rates_{subversion}

OPG base rates and outpatient percentages are extracted per (provider, payer, network, OPG). The opg code becomes provision_subtype in the final provisions table.

Quality Filters

Rows must pass at least one of the following frequency thresholds to be included:

Rate Type	Filter
OP Surg Group	opg_n_freq / opg_n_total > 0.80 — the OPG base rate must be the dominant rate for this group in more than 80% of observations
OP Percentage	op_percentage_n_freq > 200 AND op_percentage_n_freq / op_percentage_n_total > 0.90 — the percentage must appear more than 200 times and be dominant in more than 90% of observations

A row that passes either threshold is included. Rows that fail both are dropped.

Output schema

Column	Type	Description
payer_id	INT
provider_id	VARCHAR
network_id	BIGINT
network_type	VARCHAR
opg	VARCHAR	Outpatient Procedure Group code (becomes provision_subtype in final table)
opg_base_rate	DOUBLE	Base rate for this OPG
opg_candidate_base_rate	DOUBLE	Candidate base rate before final selection
opg_n_freq	INT	Number of observations of this OPG rate
opg_n_total	INT	Total observations for this OPG across all rates
opg_n_total_possible	INT	Total possible observations for this OPG
op_percentage_candidate_base_rate	DOUBLE	Percentage of charges rate
op_percentage_n_freq	INT	Number of observations of this outpatient percentage
op_percentage_n_total	INT	Total observations for this outpatient percentage