Onboarding Mapping Spec

Every mapping carries two flags. Tier comes from the target spec: required a consumer task or the delta machinery breaks without it; recommended build it whenever OCP can; optional a standard name to use if you need the concept. Evidence is how well we proved the OCP side: row validated against real values; schema inferred from names/structure; ref a KB rule.

The core. Pick the wrong grain for the account and every rollup — balance, denials, AR — is wrong. Each record: the target grain, the OCP source & key, the join spine you'd write, and the trap you must not walk past. ⚠ warn cards are the two that decide the whole onboarding.

-- TransformAccount: one row per bill
SELECT
  b.bill_id                             AS AccountID,     -- already -pod<NN>
  split_part(b.bill_id,'-pod',2)        AS DataSource,    -- 'pod12' -> §2
  b.patient_id                          AS PatientID,
  b.service_date                        AS ServiceDate,   -- non-null in target; assert
  b.bill_status                         AS EncounterStatus,
  TRY_CAST(b.balance       AS DOUBLE)   AS Balance,
  TRY_CAST(b.total_charges AS DOUBLE)   AS AmountCharged,
  b.primary_provider_id                 AS ProviderID,    -- -> Provider (100% FK)
  b.billing_facility_id                 AS FacilityID,    -- -> Location  (100% FK)
  b.last_modified_date                  AS UpdatedOn      -- delta spine; only on real change
FROM bill b;

-- TransformCharge: one row per bill_item line
SELECT
  bi.bill_id                            AS AccountID,     -- parent = the bill
  bi.bill_item_id                       AS ChargeID,
  CAST(bi.position AS INT)              AS ChargeNumber,   -- keep 0 free for zero-charge
  bi.code                               AS ProcedureCode,  -- CPT/HCPCS: 99213, J3301, 20610
  TRY_CAST(bi.charge  AS DOUBLE)        AS AmountCharged,
  TRY_CAST(bi.balance AS DOUBLE)        AS Balance,
  bi.service_date_from                  AS ServiceDate,
  bi.first_modifier                     AS Modifier1,
  CAST(bi.units AS INT)                 AS Units,
  bi.date_modified                      AS UpdatedOn
FROM bill_item bi;

-- TransformTransaction: one row per posted money movement
SELECT
  ps.original_bill_id                   AS AccountID,
  ps.transaction_identifier             AS TransactionID,  -- stable 40-char hash
  ps.bill_item_id                       AS ChargeID,       -- NULL => account-level
  ps.coverage_type                      AS COB,            -- Self-Pay->P, Primary->1...
  ps.activity_type                      AS TransactionCategory,
  COALESCE(TRY_CAST(ps.ledger_payment_amount    AS DOUBLE),
           TRY_CAST(ps.ledger_adjustment_amount AS DOUBLE)) AS Amount, -- SIGN: verify
  ps.ledger_activity_date               AS TransactionDate,
  pa.bill_item_adjustment_group_code    AS GroupCode,      -- CO/PR/OA/PI
  pa.bill_item_adjustment_carc          AS AdjustmentCode, -- CARC 45, 2, 3, 253
  par.rarc_code                         AS RemarkCodes
FROM production_summary ps
LEFT JOIN payer_adjustments      pa  ON pa.bill_item_id = ps.bill_item_id
LEFT JOIN payer_adjustments_rarc par ON par.<key> = pa.<key>;
-- AdjustmentCategory: resolve CARC via CommonData.dbo.AdjustmentCodeRollup (ref)

-- TransformAccountInsurance: payer stack as billed, reconstructed
SELECT DISTINCT
  c.bill_id                             AS AccountID,
  CASE c.coverage_type WHEN 'Primary' THEN 1 WHEN 'Secondary' THEN 2
                       WHEN 'Tertiary' THEN 3 END           AS COB,
  c.payer_id                            AS SourceInsuranceID,
  c.insurance_policy_id                 AS SourcePatientInsuranceID,
  c.responsible_insurance_policy_number AS MemberID,
  c.policy_group_number                 AS GroupNumber,
  cl.claim_identifier                   AS ICN             -- claim control # per COB
FROM charges c
LEFT JOIN claim cl ON cl.bill_insurance_id = c.bill_insurance_id;

-- TransformPatient (excerpt) + enrollment COB
patient.patient_id      -> PatientID
patient.email_opt_in    -> ElectronicCommunicationConsent  -- LoadPatients expects this
insurance_policy.ranking-> COB   -- ENROLLMENT order (1/2/3): 8,348 / 1,577 / 74  [row]

appointment.appointment_id       -> SourceID
appointment.patient_id           -> Patient
appointment.appointment_start_date -> AppointmentTime
appointment.appointment_status   -> AppointmentStatus  -- CHECKED_OUT/CANCELLED/NO_SHOW
-- VisitInsurance: (SourceID, COB) merged HARD-DELETE (stack fully replaced)

"Sometimes it's easy (an encounter id), sometimes it's bill + superbill + whatever." OCP is on the easy end — every table has a clean surrogate *_id PK. The -pod<NN> suffix on every id is the DataSource: MDC keys compose as CONCAT(source_id,'-',DataSource), and OCP ships them pre-composed (13496801-pod10). Keep the suffix or split it — be consistent.

Dimensions first (complete; everything references them), then the account fan-out. Inside LoadAccountData the internal order is fixed: Charge → Account (Account depends on charge sums) → Transaction → AccountInsurance. The Flow lane has its own delta.

The bill ledger obeys the RCM identity (validated on the bill sample):

microworld.json walks a $588 knee-injection encounter through this identity step-by-step (contractual adj → primary pay → coinsurance transfer → secondary pay → $0), plus a denial scenario — use it to sanity-check the sign & category mapping.

The machine-readable source of truth (mdc-crosswalk.csv), filterable. Coverage by evidence: 92 row · 40 schema · 1 ref.

The "do they have X?" ticklist against consumer-required fields. ICN — yes (claim.claim_identifier). Consent — yes (email_opt_in, a column LoadPatients already expects).

Validation query pack — upgrade schema → row

One coherent single-tenant extract (one pod's bill + bill_item + production_summary + claim + claim_bill_item + charges + insurance_policy, sampled together) proves almost everything. Run these:

-- One coherent single-tenant extract upgrades almost everything below to `row`.
-- (one pod's bill + bill_item + production_summary + claim + claim_bill_item + charges + insurance_policy)

-- V1. GRAIN: is bill the account?  (upgrades DR-1)
SELECT bi.bill_id, COUNT(DISTINCT cbi.claim_id) AS claims_per_encounter
FROM bill_item bi JOIN claim_bill_item cbi ON cbi.bill_item_id = bi.bill_item_id
GROUP BY bi.bill_id HAVING COUNT(DISTINCT cbi.claim_id) > 1;   -- non-empty proves the double-count trap

-- V2. BALANCE ROLLUP: identity holds per bill on real rows?  (upgrades DR-1, money model)
SELECT b.bill_id,
       TRY_CAST(b.total_charges AS DOUBLE)
         - (TRY_CAST(b.total_payments AS DOUBLE)
            + TRY_CAST(b.applied_adjustments_total AS DOUBLE)
            + TRY_CAST(b.balance AS DOUBLE)) AS residual
FROM bill b QUALIFY ABS(residual) > 0.01;                      -- empty = identity holds

-- V3. COB FAN-OUT: does bill_insurance_id thread claim 1:1?  (upgrades DR-4)
SELECT COUNT(*) AS claims, COUNT(DISTINCT bill_insurance_id) AS distinct_bi FROM claim; -- expect equal

-- V4. TRANSACTION SIGN: which sign does the legacy family expect?  (resolves open Q1)
SELECT activity_type, coverage_type,
       MIN(TRY_CAST(ledger_payment_amount AS DOUBLE))    AS min_pay,
       MIN(TRY_CAST(ledger_adjustment_amount AS DOUBLE)) AS min_adj
FROM production_summary GROUP BY 1,2;

-- V5. ChargeID NULL rule: when is a txn posted at account level?  (resolves open Q4)
SELECT (bill_item_id IS NULL) AS account_level, activity_type, COUNT(*)
FROM production_summary GROUP BY 1,2;

Open questions for the OCP data owner

1. Transaction amount sign in production_summary — verify vs the legacy family. Do not assume.
2. bill_insurance as a first-class table — can OCP export it? (member/group/ICN per COB live there).
3. Account vs claim rebill chains — does bill_id reuse on rebill, or does a new claim capture it?
4. Charge- vs account-level transactions — confirm when production_summary.bill_item_id is NULL.
5. Chargemaster / fee schedule — can OCP export one separately, or is bill_item the only source?
6. InitialBillDate source — claim_bill_item.first_claim_submission_date vs bill.billed_days_date.