refs/refs/avalon-fhir-omop/omop-views/models/omop_payer_plan_period.sql

 1{{ config(materialized='table') }}
 2
 3-- OMOP CDM 5.4 — PAYER_PLAN_PERIOD
 4-- Maps forge-core ExplanationOfBenefit → OMOP payer_plan_period
 5-- Uses billablePeriod for actual coverage dates (not ingestion_timestamp)
 6-- Uses contained Coverage resource for plan type and payor display
 7
 8WITH eob_coverage AS (
 9  SELECT
10    REPLACE(COALESCE(pat.reference, ''), 'urn:uuid:', '') AS patient_id,
11    ins_org.display AS insurer_display,
12    SAFE.PARSE_DATE('%Y-%m-%d', SUBSTR(bill.start, 1, 10)) AS period_start,
13    SAFE.PARSE_DATE('%Y-%m-%d', SUBSTR(bill.`end`, 1, 10)) AS period_end,
14    cont_type.text AS plan_type,
15    cont_payo.display AS payor_display
16
17  FROM {{ source('forge_eob', 'frg__root') }} r
18
19  {{ forge_join('raw',        'forge_eob', 'eob_raw',              'r',    2) }}
20  {{ forge_join('pat',        'forge_eob', 'eob_patient',          'raw',  3) }}
21  {{ forge_join('ins_org',    'forge_eob', 'eob_insurer',          'raw',  3) }}
22  {{ forge_join('bill',       'forge_eob', 'eob_billable_period',  'raw',  3) }}
23  {{ forge_join('cont',       'forge_eob', 'eob_contained',        'raw',  3) }}
24  {{ forge_join('cont_type',  'forge_eob', 'eob_contained_type',   'cont', 4) }}
25  {{ forge_join('cont_payo',  'forge_eob', 'eob_contained_payor',  'cont', 4) }}
26
27  WHERE bill.start IS NOT NULL
28)
29
30SELECT
31  ROW_NUMBER() OVER (ORDER BY patient_id, insurer_display) AS payer_plan_period_id,
32  ABS(FARM_FINGERPRINT(patient_id)) AS person_id,
33  MIN(period_start) AS payer_plan_period_start_date,
34  MAX(COALESCE(period_end, period_start)) AS payer_plan_period_end_date,
35  CAST(NULL AS INT64) AS payer_concept_id,
36  insurer_display AS payer_source_value,
37  0 AS payer_source_concept_id,
38  CAST(NULL AS INT64) AS plan_concept_id,
39  MAX(plan_type) AS plan_source_value,
40  0 AS plan_source_concept_id,
41  CAST(NULL AS INT64) AS sponsor_concept_id,
42  MAX(payor_display) AS sponsor_source_value,
43  0 AS sponsor_source_concept_id,
44  CAST(NULL AS STRING) AS family_source_value,
45  CAST(NULL AS INT64) AS stop_reason_concept_id,
46  CAST(NULL AS STRING) AS stop_reason_source_value,
47  0 AS stop_reason_source_concept_id
48
49FROM eob_coverage
50WHERE insurer_display IS NOT NULL
51GROUP BY patient_id, insurer_display
52