Skip to main content

Providers, PPO Networks & Repricing

Libraries covered: provider (provider/vendor/HCN/panel/contract maintenance, 1099 printing, PPO census exports, EDI partner setup, client segments), pricing (pricing plans, methods, rules, fee/DRG/per-diem/unit/conversion schedules, UCR lookup, RBRVS multipliers), moon (claim negotiations — moon_negotiations — OON products, and review of inbound EDI repricing responses). Claim-side consumption of this domain lives in clmentry, clmshare, clmsproc, claims and clmutils and is cited where relevant.

Purpose​

This domain maintains who renders and bills care (providers), who gets paid (vendors), which PPO/HMO networks they belong to (HCNs, panels, contracts), and how claimed charges are turned into allowed amounts (pricing plans, fee schedules, discounts, per diems, negotiations). It is the reference-data backbone for claim repricing:

  • Provider master with TINs, NPIs, Medicaid IDs, specialties, addresses and payable vendors (provider/w_provider_maintenance).
  • Vendor (payee) master with 1099 flag, payment rule and holds (provider/w_provider_vendor_maintenance), plus annual 1099 form printing (provider/w_1099_print).
  • Health Care Network (HCN) master and network-level pricing/withholds (provider/w_provider_hcn_maintenance); panels within an HCN (provider/w_provider_panel_maintenance); per-provider network contracts (provider/w_provider_contract_maintenance).
  • Pricing plans made of dated methods and ordered rules, referencing fee schedules, DRG weights, tiered per-diem schedules and unit/conversion (RBRVS-style) schedules (pricing/w_pricing_plan_maintenance, pricing/w_pricing_fee_schedule, pricing/w_pricing_drg_schedule, pricing/w_pricing_per_diem_schedule, pricing/w_pricing_unit_schedule, pricing/w_pricing_conversion_schedule, pricing/w_pricing_rbrvs).
  • Out-of-network claim negotiation queue ("MOON") and OON negotiation products purchased by groups (moon/w_moon_claims, moon/w_moon_oon_product_maintenance).
  • Outbound EDI of claims to repricing trading partners and review of their responses (clmutils/u_edi_outbound_edi, moon/w_moon_in_repricing).
  • PPO eligibility census exports to networks ("HCN Enrollment Dump", provider/w_hcn_enrollment_dump).

Actors​

Grounded in department checks and window code (no explicit role model beyond department groups):

  • Provider department user — full create/edit/delete rights in provider maintenance; window mode PROVIDER is set when gnv_app.inv_DeptGroups.of_IsDeptUser('PROVIDER') (provider/w_provider_maintenance pfc_postopen).
  • UM (utilization management) nurse — may create and edit only providers in Potential status; window mode UM (provider/w_provider_maintenance pfc_postopen, wf_set_address_menu). UM users also submit MOON negotiations as NURSE (moon/w_moon_claims dw_moon_negs pfc_addrow).
  • Claims examiner — read-only in provider maintenance (OTHER mode) unless the user's claims security group is "SFPI Examiner", which grants PROVIDER mode (provider/w_provider_maintenance pfc_postopen). Examiners submit MOON rows as EXAMINER (moon/w_moon_claims).
  • MIS / background processing — MOON rows submitted as BACKGROUND; only UM and MIS users may edit negotiator, bill status, negotiated/discount amounts on MOON rows (moon/w_moon_claims dw_moon_negs constructor).
  • Client segment as tenancy boundary — every provider window validates the user's current client segment against client_segment_users before opening and appends the segment name to the window title (provider/u_provider_sql.of_validate_client_segment, provider/w_provider_maintenance pfc_postopen). Segment membership is administered in provider/w_provider_segment_user_maintenance ("Client Segment User Maintenance") and switched via provider/w_provider_change_segment.

Key workflows​

provider/w_provider_maintenance ("Provider Maintenance") is a tabbed sheet: Provider (demographics + TINs + Medicaid IDs + NPIs + specialties), Addresses (addresses + address vendors + contacts), Panels (HCNs + panels), Holds, Group Holds. Search criteria are TIN (EIN or SSN radio), name (with optional Oracle SOUNDEX "Sounds Like" matching, on last/company name or lookup name), provider type, specialty, city/state, and status checkboxes Active/Inactive/Potential (provider/w_provider_maintenance cb_search clicked; SQL built in provider/u_provider_sql.of_count_providers_urcm). Zero matches with Active included and PROVIDER mode flows directly into new-provider creation; more than one match opens the pick list provider/w_provider_select_urcm (a warning is shown above 200 matches).

Saving is one transaction across all tabs: each tab validates (ue_validate events), addresses are standardized (duplicate addresses block the save), then provider → contacts → addresses → address vendors → TINs → NPIs → Medicaid IDs → specialties → panels → holds → group holds are updated and committed; any failure rolls everything back (provider/w_provider_maintenance pfc_save).

Default vendor auto-creation: when the user first moves to the Addresses tab of a provider with no default vendor, the window creates a vendor from the provider name and TIN (prompting via provider/w_provider_tin_enter if no TIN, or provider/w_provider_tin_select if several), inserts it into vendors with irs_1099_flg='Y' and status_cd='Active', and links it as providers.default_vendor_sys_key (provider/w_provider_maintenance dw_provider ue_create_default_vendor, tab_dw selectionchanging). On save, if the default vendor has no address, the provider's first address is copied to vendor_addresses (provider/w_provider_maintenance pfc_save).

Automatic provider matching during claim intake​

Claim processing resolves the claim's service provider with a cascade (clmshare/n_provider_maintenance.of_find_provider):

  1. Resolve the claim's group to its master group and client segment (CONNECT BY up groups, then client_segment_groups).
  2. Fetch all providers holding the claim's billing TIN + TIN type in that segment (d_claims_processing_prov_tin).
  3. Filter by performing-provider name; failing that (and when no degree was given) by billing-provider person name; failing that by billing facility vs company_name; failing that try aliases (of_find_provider_alias).
  4. Candidates are then disambiguated by billing address (d_claims_processing_prov_addr).

If no provider is found, of_create_provider can auto-create one from claim data — but for repricing sheets only when the claim's HCN has add_providers_flg='Y' (clmshare/n_provider_maintenance.of_create_provider, d_claims_processing_hcn_contracts_flag). Vendors are similarly matched on TIN + name + full address before being created (clmshare/n_provider_maintenance.of_find_vendor). Unresolved providers surface as "cannot find provider" claim messages reviewed in provider/w_provider_open_cant_find_provider_msgs ("Open Provider Messages"). TINs known to be un-matchable are held in unsearchable_tins, maintained in provider/w_unsearchable_tins.

PPO network assignment​

Three levels attach a provider to networks and pricing:

  • HCN membership via panels — on the Panels tab the user searches HCNs by name (provider/u_provider_sql.of_count_hcns, pick list provider/w_provider_hcn_select), then adds dated panel rows (provider_panels: panel, eff/exp date, exp reason) under the selected HCN (provider/w_provider_maintenance panels tab). Panels themselves belong to an HCN and carry pricing-plan and withhold assignments (provider/w_provider_panel_maintenance, tables panels, panel_pricing, panel_withholds).
  • Contracts — provider/w_provider_contract_maintenance creates per-provider contracts (contracts) against an HCN with contract type (Payable/Capitated/Staff/Fund), remittance-advice format (EOB, EOB/EOB, EOB/RA, EDI, Custom, None), PCP flag, new-patients flag, withholds-allowed flag, network provider ID, enrollment limit, DRG hospital multiplier and DRG cap rate, capitation vendor, and dated pricing plans / withholds / holds per contract (provider/d_contracts, contract_pricing, contract_withholds, contract_holds).
  • HCN-level pricing — the HCN itself carries dated pricing-plan assignments and withholds (hcn_pricing, hcn_withholds; provider/w_provider_hcn_maintenance).

HCN master data (health_care_networks) includes ID, name, type (HMO/HMO/PPO/PPO), EOB description (a reason_codes reference), eligibility-file (census) format, code-review flag, automatic contract insertion flag (add_contracts_flg), automatic provider insertion flag (add_providers_flg), reject-non-reprice-sheets flag, and electronically-paid flag (provider/d_hcn).

Group benefit plans point at an HCN through plan_pkgs.hcn_sys_key with a pricing_logic_cd; the report provider/w_provider_rpt_hcn_pricing_methods (d_provider_rpt_hcn_pricing_methods) joins group_coverages → plan_pkgs → health_care_networks to show which groups price through which network as of a date.

Claim repricing flow​

  • Repricing sheets: claim forms UB92_REPRICE_IN, UB92_REPRICE_OUT, HCFA-reprice and generic-reprice capture network paper repricing (clmentry/u_claims_tab_ub92_reprice_in ue_getclaimformtype; DataWindows clmentry/d_claims_reprice_info_ub92_reprice_in, d_claims_reprice_info_hcfa, d_claims_reprice_info_generic). The entry defaults received_from to HCN (clmentry/u_claims_tab_ub92_reprice_in ue_new). Header-level results are stored in claims_reprice_info (HCN key, dates entered/printed/received, discount %, max discount %, per-diem breakdown, totals allowed/discounted, patient/payer owes, provider-out-of-network and member-not-on-file indicators) and day-level per-diem detail in claims_reprice_detail (clmshare/d_claims_reprice_info_master, schema.json). A non-reprice claim can be converted to a reprice form via claims/w_convert_to_reprice_form.
  • Line pricing: claim_entries carries hcn_sys_key, repricing_method, repriced_amt and discount_pct at claim level (clmentry/d_claim_entries_common); each priced line writes a claim_line_pricing_rules row linking the line to the pricing rule applied, the savings type and the savings amount (clmshare/d_claim_line_pricing_rules_master). Examiners can manually record a line's pricing rule/savings via claims/w_claim_line_pricing_rules ("Enter Pricing Information": savings type, savings, reason).
  • Discount application order: in adjudication, a claim-level PPO discount takes precedence — repriced amount = claimed − discount (detail-line discount used when the claim has detail lines); only when there is no PPO discount is the UCR value used as the repriced amount, and if UCR is zero the claimed amount stands. The allowed amount is then the lesser of claimed and repriced (clmsproc/n_claim_adjudication repriced-amount computation, ~lines 4761–4880).
  • Whole-claim pricing: a claim is treated as whole-claim priced only when repricing_method = 'WHOLE' and it is a repricing sheet (clmsproc/n_claim_adjudication ~line 6573). Per-diem totals are recomputed from claims_reprice_detail day rows, with error returns for missing or duplicate per-diem amounts per day (clmsproc/n_claim_adjudication per-diem summation, ~lines 7917+, DataWindow clmsproc/d_adjd_reprice_detail).

Outbound EDI repricing round trip​

Claims destined for an external repricing partner are decomposed into staging tables outbound_edi_claim_table, outbound_edi_line_table, outbound_edi_provider_table (billing/attending/referring/performing provider name, degree, address, phone, TIN and the resolved bill_provider_sys_key), outbound_edi_patient_table, outbound_edi_subscriber_table, outbound_edi_ub92_table, plus conditions/occurrences/procedures/values/dental tables (clmutils/u_edi_outbound_edi constructor, of_load_claim, of_load_provider_info). Files are cut per HCN/trading partner from transactions with processed_dt set and no file_created (clmutils/d_edi_outbound_claims_to_file, clmutils/w_edi_outbound_edi_processor). Trading partners and clearinghouses are maintained in provider/w_edi_partners and provider/w_edi_clearinghouse (tables outbound_edi_trading_partners — id, name, type, clearinghouse, routing number, allowance/pay-by/retention days, no-reply action, status — and outbound_edi_clearinghouses, with contacts). Partner responses land in edi_in_repricing (claim id/line, claimed amt, line pricing amt, whole-claim pricing amt, reject code, processed indicator) and are reviewed/edited in moon/w_moon_in_repricing ("Review responses to outbound EDI requests for repricing"), filterable by claim, partner, active (processed_ind='N') vs complete, and processed date; the screen warns when one claim has lines in both active and complete statuses.

Negotiation flow (MOON)​

moon/w_moon_claims ("MOON Claims") manages moon_negotiations, one row per claim under negotiation:

  1. A claim is added by claim ID (a claim may appear only once — checked against the table and unsaved rows). The window auto-fills examiner, original claimed amount, group, provider name/specialty/phone from claims, providers, provider_addresses/pm_contacts and claim_entries.bill_provider_phone (moon/w_moon_claims dw_moon_negs ue_afteritemchanged).
  2. submitted_by is stamped from the user's department: BACKGROUND (MIS), EXAMINER (Claims), NURSE (UM); bill_status_cd defaults to PENDING and bill_dt to today (moon/w_moon_claims dw_moon_negs pfc_addrow).
  3. Negotiators record status, negotiated amount, discount amount, notes (information), the involved PPOs (ppo_field) and a resulting bill claim id (bill_claim_id); these fields are editable only for UM/MIS users (moon/d_moon_negs, dw_moon_negs constructor). Assigning an examiner stamps sent_to_examiner_dt.
  4. The work queue filters by bill status codes from code table MOONSTATUS (moon/d_moon_keys) with per-user saved filter preferences; the row list can be printed via report moon/d_moon_negs_rpt, and double-clicking a claim locks it and opens the claim editor (moon/w_moon_claims doubleclicked). A distinct provider/phone list of negotiated providers is available in moon/w_moon_provider_list (d_moon_neg_providers).

OON products (moon/w_moon_oon_product_maintenance, "OON Products") define negotiation products a group can purchase: product id/description/comments (oon_products), dated group subscriptions (oon_product_groups), dated HCN exclusions (oon_product_hcn_exclusions), and per-claim-type parameters — minimum claimed amount, threshold claimed amount, allowance/pay-by/retention days, and override flags for case management, CM negotiated rates and manual price (oon_product_claim_types, moon/d_moon_product_claim_types). Date ranges per group and per HCN must not overlap (window validations).

PPO census (eligibility) exports​

provider/w_hcn_enrollment_dump ("Health Care Network Enrollment Dump") extracts group and member eligibility into network-specific fixed-format files selected by the HCN's census_format; per-format writers exist for generic, Cigna, Emerald, HFN, HS, MMO and VSP (provider/n_hcn_dump_generic, n_hcn_dump_cigna, n_hcn_dump_emerald, n_hcn_dump_hfn, n_hcn_dump_hs, n_hcn_dump_mmo, n_hcn_dump_vsp; staging DataWindows d_ppo_census, d_ppo_group_selected, d_ppo_enrollment_selected). Related PBM flag-file transfers (Caremark, Optum) and HMS reporting also live in this library (provider/w_provider_caremark_flag_transfer, provider/w_provider_optum_flag_transfer, provider/w_hms_reporting).

1099 production​

Claim payments accumulate per group + vendor + tax year into print_1099s (group and vendor name/address/TIN, tax_amt_box_6, tax_amt_box_7, printed indicator, original/last print dates, reprint reason). provider/w_1099_print ("Print 1099 Forms") filters by group, tax year, vendor TIN, print dates and not-yet-printed; printing selected rows renders d_1099_form (or d_1099nec_form for one hard-coded group, 501-069Z), then marks rows printed with dates; reprints require a reason captured in provider/w_1099_reason (provider/w_1099_print pfc_print).

Vendor consolidation (dedup)​

provider/w_provider_consolidate_vendors merges duplicate vendors sharing a TIN: the survivor is chosen as "main vendor"; each duplicate is archived to consolidated_vendors, its withholds (hcn_withholds, panel_withholds, contract_withholds), addresses, contacts and holds are deleted, contracts.vendor_sys_key, provider_address_vendors.vendor_sys_key, fund_register.payee_sys_key (payee type VENDOR) and providers.default_vendor_sys_key are repointed to the survivor, and the duplicate vendors row is deleted (provider/w_provider_consolidate_vendors ue_consolidate).

Business rules​

Provider master​

  • PRV-1 — A provider must have a company name, or both a first and last name (provider/w_provider_maintenance dw_provider ue_validate).
  • PRV-2 — A provider must have a lookup name (provider/w_provider_maintenance dw_provider ue_validate).
  • PRV-3 — A provider must have at least one TIN, and no TIN row may be blank/zero (provider/w_provider_maintenance dw_tin ue_validate).
  • PRV-4 — Provider status is one of Active / Inactive / Potential (search checkboxes and status IN-list in provider/u_provider_sql.of_count_providers_urcm; code table PROVSTATUS, provider/dddw_provider_status_cd). New providers default to Active; providers created by UM users are forced to Potential with the status protected (provider/w_provider_maintenance dw_provider pfc_new).
  • PRV-5 — Medicaid IDs require a state and a non-blank ID; NPIs and specialties cannot be blank (provider/w_provider_maintenance dw_mcaid/dw_npi/dw_specialty ue_validate).
  • PRV-6 — Every provider address must have at least one vendor associated; the current (first) vendor row must have an effective date and no expiration date; every prior vendor row must have expiration date and expiration reason; expiration must follow effective date (provider/w_provider_maintenance dw_vendors ue_validate). Address-vendor expiration reasons come from code table VNDRADDRXR (provider/dddw_provider_vendor_address_exp_codes).
  • PRV-7 — Provider addresses are standardized on save; duplicate addresses for one provider block the save (provider/w_provider_maintenance pfc_save ue_standardize_address handling).
  • PRV-8 — Provider records are partitioned by client segment; a user may only open maintenance in a segment listed for them in client_segment_users (provider/u_provider_sql.of_validate_client_segment).
  • PRV-9 — Deleting a provider is a hard delete that first removes TINs, Medicaid IDs, NPIs, specialties, address vendors, addresses and contacts, after an explicit confirmation (provider/w_provider_maintenance dw_provider pfc_deleterow).
  • PRV-10 — Provider holds require a start date and a hold reason (code table PRVDHOLD); an end date requires an end-date type (code table PRVDHOLDDT); end date must follow start date (provider/w_provider_maintenance dw_holds ue_validate). Group-specific holds additionally require a group id and reason (code table PROVGRPHLD) (provider/w_provider_maintenance dw_group_holds ue_validate).

Vendors and 1099​

  • PRV-11 — A vendor must have a name, a lookup name and a TIN (provider/w_provider_vendor_maintenance validations), a TIN type of EIN or SSN, a payment rule of Claimed / Incurred / Received with rule date, and a Produce-1099 flag (provider/d_provider_vendor).
  • PRV-12 — A vendor must have at least one address; addresses need an effective date, expiration after effective, and an expiration reason when expired (provider/w_provider_vendor_maintenance address validations). Vendor holds follow the same start/reason/end-type rules as provider holds (code table VNDRHOLD; provider/w_provider_vendor_maintenance hold validations).
  • PRV-13 — Every provider gets a default vendor (auto-created from name + TIN with irs_1099_flg='Y', status Active) used as payee default; if that vendor has no address, the provider's first address is used (provider/w_provider_maintenance ue_create_default_vendor, pfc_save).
  • PRV-14 — 1099 reprints require a reason; first prints stamp printed indicator and original print date, every print stamps last print date; box 6 and box 7 amounts come from accumulated print_1099s (provider/w_1099_print pfc_print). One specific group (501-069Z) prints on the 1099-NEC layout (provider/w_1099_print pfc_print — see Open questions).

HCNs, panels, contracts​

  • PRV-15 — An HCN requires ID, name, type code (HMO / HMO-PPO / PPO), code-review flag and an EOB description reason; new HCNs default to type PPO, code review N, and the first HCN reason code (provider/w_provider_hcn_maintenance dw_hcn ue_validate, pfc_new).
  • PRV-16 — HCN flags drive claim intake behavior: add_providers_flg gates auto-creation of providers from repricing sheets (clmshare/n_provider_maintenance.of_create_provider); add_contracts_flg, reject_non_reprice_flg and electronically_paid_flg are configuration on the HCN record (provider/d_hcn — consumption of the latter two not located in extracted source; see Open questions).
  • PRV-17 — Deleting an HCN removes its withholds, pricing and contacts first; deletion is refused when other records still reference the HCN (Oracle FK error 2292 is translated to a user message) (provider/w_provider_hcn_maintenance pfc_deleterow).
  • PRV-18 — Withholds (at HCN, panel and contract level) require a vendor, a percentage (0–100), a type of Allowed or Paid, and an effective date; expiration must follow effective and requires an expiration reason (code table WTHLDXRSN) (provider/w_provider_hcn_maintenance dw_withholds ue_validate; provider/d_hcn_withhold values; same rules in provider/w_provider_panel_maintenance and provider/w_provider_contract_maintenance). Panel withhold dates must additionally lie within the panel's own date range (provider/w_provider_panel_maintenance withhold validations).
  • PRV-19 — Pricing-plan assignments (HCN, panel, contract) require a plan and an effective date; expiration must not precede effective (provider/w_provider_hcn_maintenance dw_pricing ue_validate; parallel validations in panel and contract windows). Plans are chosen/edited through pricing/w_pricing_plan_maintenance_single.
  • PRV-20 — A panel requires ID, name and effective date; expiration must follow effective and requires a reason (code table PANELXRSN) (provider/w_provider_panel_maintenance validations; provider/d_panels).
  • PRV-21 — A provider-panel membership requires panel, effective date; expiration requires a reason and must follow effective; the same HCN cannot be added twice to one provider (provider/w_provider_maintenance dw_panels ue_validate, dw_hcns pfc_new "Duplicate HCN").
  • PRV-22 — A contract requires an HCN, a remittance-advice format and an effective date; expiration must follow effective and requires an expiration reason (code table CNTRCTXRSN); new contracts default PCP=N, withholds allowed=Y, new patients=N (provider/w_provider_contract_maintenance dw_contract ue_validate, pfc_new). Contract holds use reason code table CNTRCTHOLD (provider/dddw_provider_contract_hold_reason_codes).
  • PRV-23 — Contract types are Payable, Capitated, Staff, Fund; RA formats are EOB, EOB/EOB, EOB/RA, EDI, Custom, None; a capitation vendor can be attached via vendor search (provider/d_contracts, provider/w_provider_contract_maintenance buttonclicked).

Provider matching / dedup​

  • PRV-24 — Claim provider matching proceeds TIN-first, then performing-provider name, then billing person name, then billing facility name against company name, then aliases, with final disambiguation by billing address; only providers of the claim's client segment are considered (clmshare/n_provider_maintenance.of_find_provider).
  • PRV-25 — Vendor matching for payment requires TIN + name match plus an exact address match (addr1, addr2, city, state, zip) before an existing vendor is reused; otherwise a vendor is created (clmshare/n_provider_maintenance.of_find_vendor).
  • PRV-26 — Interactive duplicate checking is soundex-capable: name searches may use SOUNDEX on last/company name or lookup name, combined with TIN and TIN type (provider/u_provider_sql.of_count_providers, of_get_provider; vendor equivalents of_count_vendors, of_get_vendor).
  • PRV-27 — TINs that cannot be used for matching are maintained as an exclusion list (unsearchable_tins; provider/w_unsearchable_tins).

Pricing plans and rules​

  • PRV-28 — A pricing plan is a named set of dated, ordered methods (pricing_plans, pricing_methods: method, from/thru dates, sequence, detail indicator, rollover indicator, EOB reason, savings type, default DRG multiplier/cap rate) each holding ordered rules (pricing_rules) (pricing/w_pricing_plan_maintenance, pricing/d_pricing_plan_methods).
  • PRV-29 — Valid methods come from valid_methods (method + pricing type WHOLE or DETAIL) (pricing/dddw_pricing_plan_methods). Observed methods: PER DIEM, CASE RATE, PCT BILL (no schedule allowed) and FEE, DRG, TIERED PD, UNIT (require a schedule) (pricing/w_pricing_plan_maintenance dw_rule_edit buttonclicked; pricing/f_pricing_schedule_name).
  • PRV-30 — Method validation: effective date required; thru date must be after from date; EOB description (reason) required; savings type required. WHOLE-type PER DIEM rules must have per-diem amount > 0 (pricing/w_pricing_plan_maintenance wf_check_row). Changing a method's type deletes all its rules after confirmation (pricing/w_pricing_plan_maintenance "Method Change" prompt).
  • PRV-31 — Rule match criteria are stored as SQL-like criterion strings built by a guided editor over procedure codes/groups, diagnosis codes/groups, DRG numbers, revenue codes, length of stay, specialties and place-of-service codes, with IN / BETWEEN / EQUAL TO / NOT operators; per-code-type procedure and diagnosis criteria live in child tables pricing_rule_procs / pricing_rule_diags, the rest on pricing_rules columns (*_criterion) (pricing/w_pricing_plan_maintenance wf_encode/wf_decode; pricing/d_pricing_plan_rule_edit).
  • PRV-32 — Rule pricing parameters include allow %, apply-allow-% code, allow additional amount, case rate amount, per-diem amount and type (Day/Case), day-from/day-thru bands, rule type (Base/Inlier/Outlier), inpatient/outpatient indicator, anesthesia indicator, replace-with-claimed ("Force Repriced Amt") indicator, benefit definition link and schedule reference (pricing/d_pricing_plan_rule_edit, schema.json pricing_rules).
  • PRV-33 — pricing_rules.schedule_sys_key resolves per method: FEE → fee_schedules, DRG → drg_weights, TIERED PD → tiered_per_diem_schedules, UNIT → conversion_factors (pricing/f_pricing_schedule_name).
  • PRV-34 — Rules and methods are re-orderable by drag & drop (sequence numbers seq_no) (pricing/w_pricing_plan_maintenance ue_drag_start handling); deleting a rule cascades its proc/diag criteria rows (pricing/w_pricing_plan_maintenance pfc_update).

Schedules​

  • PRV-35 — Fee schedule detail rows require effective date, fee amount and a procedure code that exists in procedure_codes for the code type; expiration must follow effective; rows can vary by modifier, specialty, state and zip (pricing/w_pricing_fee_schedule validation; pricing/d_pricing_fee_schedule).
  • PRV-36 — Conversion (RBRVS-style) schedules hold procedure-code-range rows with base/time/work/practice/malpractice multipliers, optional zip, dated; start and end procedure codes must be valid (pricing/w_pricing_conversion_schedule; pricing/d_pricing_conversion_schedule). Unit schedules hold per-procedure base/time/work/practice/malpractice units with optional modifier/zip (pricing/d_pricing_unit_schedule). DRG weight schedules hold dated DRG-code weights and reject invalid DRG codes (pricing/w_pricing_drg_schedule). Tiered per-diem schedules hold day-band rows (days from/thru, per-diem amount and type) with date validation (pricing/w_pricing_per_diem_schedule).
  • PRV-37 — UCR unit price = requested units × Σ(multiplierᵢ × unitᵢ) over base/time(anesthesia)/work/practice/malpractice, where the anesthesia time unit is 1 + ⌊minutes / 15⌋; when the unit schedule's unit type is RBRVS, a dated master multiplier from rbrvs_master_multipliers must exist (pricing/w_pricing_lookup_ucr ue_retrieve). Modifier matching prefers an exact modifier row and falls back to the no-modifier row (pricing/w_pricing_lookup_ucr ue_retrieve). RBRVS master multipliers are maintained per unit type and date range in pricing/w_pricing_rbrvs (master_multipliers).

Repricing consumption​

  • PRV-38 — On reprice-in entry, repriced amount and discount percent are required unless the provider is flagged out-of-network or the member is not on file (clmentry/u_claims_tab_ub92_reprice_in of_resetrequiredfields, ue_postretrieve).
  • PRV-39 — Adjudication applies a PPO discount in preference to UCR: repriced = claimed − discount when a discount exists (line-level discount for detailed claims); otherwise repriced = UCR value (claimed when UCR is zero); allowed = min(claimed, repriced) (clmsproc/n_claim_adjudication ~4761–4880).
  • PRV-40 — Whole-claim per-diem repricing sums claims_reprice_detail day rows; days with no per-diem row or with conflicting amounts are errors (clmsproc/n_claim_adjudication per-diem summation ~7917+).
  • PRV-41 — Each priced claim line records its pricing rule, savings type and savings amount in claim_line_pricing_rules; new rows receive keys at save time and are persisted with the claim (clmshare/n_cst_claims update sequence ~lines 405–416).

Negotiations and OON products​

  • PRV-42 — A claim may be in MOON negotiations only once (moon/w_moon_claims dw_moon_negs ue_afteritemchanged duplicate checks) and must exist in claims.
  • PRV-43 — MOON rows default to bill status PENDING, bill date today, and a submitter derived from department (MIS→BACKGROUND, Claims→EXAMINER, UM→NURSE); negotiation outcome fields are restricted to UM/MIS users (moon/w_moon_claims dw_moon_negs pfc_addrow, constructor). Bill statuses come from code table MOONSTATUS (moon/d_moon_keys).
  • PRV-44 — An OON product requires product ID and description; its group and HCN-exclusion date ranges must not overlap per group/HCN; claim-type rows require claim type, valid minimum claimed and threshold amounts, and override CM / override rates settings (moon/w_moon_oon_product_maintenance validations).

Pricing model — structure and consumption​

  • A plan (pricing_plans) is assigned, with date ranges, at HCN, panel and contract level (hcn_pricing, panel_pricing, contract_pricing; maintained in provider/w_provider_hcn_maintenance, provider/w_provider_panel_maintenance, provider/w_provider_contract_maintenance). The precedence among the three levels at claim time is not visible in the extracted client source (see Open questions).
  • A plan's methods carry pricing_type WHOLE or DETAIL from valid_methods — whole-claim vs line-level pricing (pricing/d_pricing_plan_methods; clmsproc/n_claim_adjudication treats repricing_method='WHOLE' as whole-claim priced).
  • Rules select claim lines via criterion strings (procedure/diagnosis/DRG/revenue/LOS/specialty/POS, see PRV-31) and price them via percent-of-billed (allow_pct + apply_allow_pct_cd), case rate, per diem (amount, Day/Case type, day bands), or a schedule lookup per PRV-33. replace_with_claimed_ind renders as "Force Repriced Amt" (pricing/d_pricing_plan_rule_edit).
  • Fee schedules price per procedure code (+type, modifier, specialty, state, zip, date range) with a flat fee_amt (fee_schedule_details).
  • Conversion schedules (UCR/RBRVS) pair a unit schedule (unit_schedules → unit_schedule_details units per procedure) with conversion_factor_details multipliers per procedure-code range; price = units × Σ(multiplier × unit) with an rbrvs_master_multipliers gate for RBRVS unit types (pricing/w_pricing_lookup_ucr).
  • DRG pricing uses drg_weight_details weights per DRG code plus contract-level drg_base_multiplier / drg_cap_rate and method-level defaults (contracts, pricing_methods.default_drg_multiplier/default_drg_cap_rate).
  • Applied results are recorded per line in claim_line_pricing_rules (rule, savings type from code list dddw_pricing_plan_savings_types, savings amount) and per claim in claims_reprice_info / claims_reprice_detail.

Provider data model​

  • providers (30 cols): names (plus upper-case shadow columns for search), lookup name, status, type (provider_types), primary specialty, educational degree, UPIN, Medicare number, clearinghouse number, birth date, languages 1–3, after-hours info, covering provider (self-reference), default vendor, client segment (schema.json; labels in provider/d_provider).
  • Addresses and contacts are shared infrastructure: provider_addresses links a provider to pm_addresses rows (with std_addr_flg standardization flag) and optional pm_contacts (name, phone, fax, email, position, comments) (provider/d_provider_address).
  • vendors (payees): name, lookup name, TIN + type, client segment, irs_1099_flg, payment rule (+date), status; addresses/contacts/holds as child tables (schema.json; provider/d_provider_vendor).
  • 1099 linkage: the default vendor created per provider carries irs_1099_flg='Y'; payment accumulation lands in print_1099s keyed by group, vendor TIN and tax year (schema.json; provider/w_1099_print). (The accumulation writer is in the funding/check-run domain, not this library.)
  • specialties carries an anesthesia indicator per specialty (anes_ind, schema.json) used by anesthesia-aware pricing; provider_add_log records providers auto-added from claims (claim id, provider, secondary id, type) (schema.json).

Data & CRUD​

From _re/analysis/schema.json (crud section):

LibraryCreates/updates (CRUD or CUD)Reads only
providerproviders, provider_tins, provider_npis, provider_medicaid_ids, provider_specialties, provider_addresses, provider_address_vendors, provider_panels, provider_holds, provider_group_holds, panels, panel_pricing, panel_withholds, contracts, contract_pricing, contract_withholds, contract_holds, health_care_networks, hcn_contacts, hcn_pricing, hcn_withholds, vendors, vendor_addresses, vendor_contacts, vendor_holds, consolidated_vendors, unsearchable_tins, print_1099s, provider_types, specialties, client_segments, client_segment_users, security_users, pm_addresses, pm_contacts, outbound_edi_clearinghouses, outbound_edi_trading_partners, outb_edi_clearhouse_contacts, outb_edi_tradingpart_contacts, ppo_census_ids, ppo_enrollment_work, ppo_group_work, mmo_enrollment_work, caremark_report_audit, codesclaims, claim_messages, members, member_eligibilities, groups, group_coverages, plan_pkgs, reason_codes, fund_register, security tables
pricingpricing_plans, pricing_methods, pricing_rules, pricing_rule_procs, pricing_rule_diags, fee_schedules, fee_schedule_details, drg_weights, drg_weight_details, drg_codes, tiered_per_diem_schedules, tiered_per_diem_details, unit_schedules, unit_schedule_details, conversion_factors, conversion_factor_details, valid_methods, master_multipliers, procedure_codes, diagnosis_codes, specialties, reason_codes, benefit_defs, codesrbrvs_master_multipliers, place_of_service_codes, procedure_groups, diagnosis_groups
moonmoon_negotiations, oon_products, oon_product_groups, oon_product_claim_types, oon_product_hcn_exclusions, edi_in_repricingclaims, claim_entries, providers, provider_addresses, pm_contacts, groups, health_care_networks, outbound_edi_trading_partners, codes

Key sequences observed: provider_sys_key.nextval, vendor_sys_key.nextval, hcn_sys_key.nextval, contract_sys_key.nextval, pm_contacts_seq.nextval, all_purpose_seq.nextval (pricing plans) (provider/w_provider_maintenance, provider/w_provider_hcn_maintenance, provider/w_provider_contract_maintenance, pricing/w_pricing_plan_maintenance).

Code tables (table codes, by code_key): PROVSTATUS, PRVDHOLD, PRVDHOLDDT, PROVGRPHLD, CNTRCTHOLD, CNTRCTXRSN, PANELXRSN, WTHLDXRSN, VNDRHOLD, VNDRADDRXR, EDIPARTNER, EDIACTION, MOONSTATUS (provider/moon dddw_* DataWindows). HCN EOB descriptions and pricing EOB reasons come from reason_codes (provider/dddw_hcn_reason_codes, pricing crud on reason_codes).

EDI​

  • Outbound claim EDI for repricing: claims are staged into eleven outbound_edi_* tables and cut to files per HCN/trading partner (clmutils/u_edi_outbound_edi constructor is_tables[], clmutils/d_edi_outbound_claims_to_file, driven by clmutils/w_edi_outbound_edi_processor). outbound_edi_provider_table denormalizes billing / attending / referring / performing provider identity, address, phone and TIN per claim, keyed by claim_id, and carries the resolved bill_provider_sys_key (schema.json; populated by clmutils/u_edi_outbound_edi.of_load_provider_info from d_edi_provider_info).
  • Trading partner / clearinghouse setup: outbound_edi_trading_partners (id, name, partner type EDIPARTNER, clearinghouse, routing number, allowance/pay-by/retention day counts, no-reply action EDIACTION, status) and outbound_edi_clearinghouses, each with contact children, maintained in provider/w_edi_partners and provider/w_edi_clearinghouse (segment-validated like other provider windows).
  • Inbound repricing responses: edi_in_repricing rows per claim line (claimed amount, line pricing amount, whole-claim pricing amount, reject code, trading partner, processed indicator/date) reviewed in moon/w_moon_in_repricing.

Migration notes​

  1. Criterion strings are a mini-language. pricing_rules criteria (proc_group_criterion, diag_group_criterion, drg_code_criterion, revenue_criterion, los_criterion, specialty_criterion, pos_code_criterion, inpat_outpat_criterion plus pricing_rule_procs/pricing_rule_diags) hold SQL-like text (IN (...), BETWEEN, NOT, <>) generated by the editor (pricing/w_pricing_plan_maintenance wf_encode). A migration must parse these into a structured rule model rather than carry opaque strings; the editor's decode function (wf_decode) documents the exact grammar actually produced.
  2. The batch pricing engine is not in the client. The UI maintains rules and records results, and adjudication comments say "Writeoff now done in pricing process" (clmsproc/n_claim_adjudication ~5967), but no extracted object evaluates rule criteria against claim lines. Expect the rule-evaluation engine in database procedures or a background executable; it must be located and reverse-engineered separately.
  3. Effective-dating everywhere. Plans-to-network assignments, withholds, panels, contracts, addresses, schedules and OON products are all dated ranges with expiration reasons. Overlap is validated only in some windows (OON products, addresses); several assignment tables (e.g. hcn_pricing) have no overlap check in the UI — data cleanup should expect overlapping rows.
  4. Client segment is a hard multi-tenancy key on providers, vendors, HCNs, panels, addresses (client_segment columns; of_validate_client_segment gate). Auto-creation paths connect as a service account (sfpiback) (clmshare/n_provider_maintenance.of_create_provider).
  5. Hard deletes with manual cascades. Provider, HCN and vendor deletes issue explicit child DELETEs and rely on FK error 2292 to block referenced rows (provider/w_provider_maintenance pfc_deleterow, provider/w_provider_hcn_maintenance pfc_deleterow). Vendor consolidation deletes duplicate history (withholds, addresses, contacts, holds) irrecoverably except for a snapshot in consolidated_vendors (provider/w_provider_consolidate_vendors).
  6. Dynamic SQL with string concatenation builds provider search (u_provider_sql.of_count_providers_urcm), 1099 filters (provider/w_1099_print ue_retrieve) and MOON queue filters (moon/w_moon_claims pfc_retrieve) — inputs are not parameterized; re-implementations should use parameterized queries.
  7. Per-format census writers are code, not config. Each network census format (Cigna, MMO, VSP, HS, HFN, Emerald, generic) is a hand-written fixed-width serializer (provider/n_hcn_dump_*); several Cigna DataWindow variants are date-stamped forks (provider/d_hcn_cigna_pre12222025, d_hcn_cigna_pre_mh_auth_to_change_to_01012026) implying active, dated format changes through 2025–2026.
  8. Hard-coded values to externalize: group 501-069Z selects the 1099-NEC form (provider/w_1099_print); anesthesia minutes-per-unit is fixed at 15 (pricing/w_pricing_lookup_ucr); MOON queue placeholder filter ('Ohio') when nothing selected (moon/w_moon_claims pfc_retrieve); user "SFPI Examiner" group grants provider-edit rights (provider/w_provider_maintenance pfc_postopen); segment literal 'SFPI' in the HCN pricing-methods report (provider/d_provider_rpt_hcn_pricing_methods).
  9. 1099 printing mutates state per printed page (printed_ind/orig_print_dt/last_print_dt row by row, then one update+commit) — reprint governance (reason capture) must be preserved (provider/w_1099_print pfc_print).
  10. MOON is claim-editor-coupled: double-click locks the claim through the claim-control service before opening the editor (moon/w_moon_claims doubleclicked) — the negotiation queue is not standalone.

Open questions​

  1. Pricing plan resolution precedence — contract_pricing vs panel_pricing vs hcn_pricing: which wins when several dated assignments cover one claim? No extracted client code performs this resolution (only maintenance windows touch these tables); presumed to live in the non-extracted pricing/adjudication batch (see Migration note 2).
  2. health_care_networks.reject_non_reprice_flg and electronically_paid_flg consumption — flags are maintained on provider/d_hcn but no consuming logic was found in extracted source.
  3. valid_methods full contents — only the seven method literals visible in window code are documented (PRV-29); the table itself may hold more methods and display data.
  4. MOONSTATUS code values — bill statuses beyond the PENDING default are data in codes and not enumerable from source; negotiation "letters" (if any) were not found in the moon library — the only outputs located are the queue report moon/d_moon_negs_rpt and provider list moon/d_moon_neg_providers.
  5. Where claim_entries.repriced_amt/discount_pct become claims_reprice_info totals — form entry DataWindows write both areas, but the recomputation/rollup path (e.g. grand_total_allow_amt) is inside claim-entry form objects not fully traced here.
  6. print_1099s accumulation source — which process writes box 6/7 amounts per tax year (presumed check-run/funding domain).
  7. Withhold settlement — withhold percentages (Allowed/Paid) are captured at HCN/panel/contract level, but the payment-time withholding and later release/settlement process is outside these libraries (fund register touched only via vendor consolidation).
  8. oon_product_claim_types.pay_by_days/allowance_days/retention_days semantics — mirrored on trading partners; the enforcement point (claim aging/auto-release) was not located.
  9. w_hms_reporting/w_hms2 (HMS) — large ad-hoc reporting screens over HCN enrollment data; business owner and continued relevance unknown.
  10. moon_negotiations has no negotiation-fee/invoice link other than bill_claim_id; how negotiation fees are billed (the "MOON Claim" column suggests a separate claim is created) needs confirmation with the business.
  11. RBRVS multiplier table pair — the maintenance window writes master_multipliers (pricing/d_pricing_rbrvs) while the UCR lookup reads rbrvs_master_multipliers (pricing/w_pricing_lookup_ucr); whether these are synonyms/views of one table must be confirmed against the database.