Deriving & verifying claims mappings with agents

-- silver.claims_conformed
-- Conforms the legacy Platform A extract to the Gold claims_fact contract.
-- Owner: EDW / claims-data-eng.  (undocumented mapping — lives only in this job)
CREATE OR REPLACE TABLE silver.claims_conformed AS
SELECT
    CLM_ID                                          AS claim_id,
    CASE TX_STAT_CD
        WHEN '1' THEN 'PAID'
        WHEN '2' THEN 'REVERSED'
        WHEN '3' THEN 'REJECTED'
    END                                             AS claim_status,
    CRDHLDR_ID                                      AS member_id,
    to_date(CAST(SVC_DT AS STRING), 'yyyyMMdd')     AS date_of_service,
    PROD_SVC_ID                                     AS ndc,
    DRUG_LBL_NM                                     AS drug_name,
    QTY_DISP / 1000.0                               AS quantity_dispensed,
    DAYS_SUP                                        AS days_supply,
    RX_NBR                                          AS rx_number,
    FILL_NUM                                        AS fill_number,
    DAW_CD                                          AS daw_code,
    PRSCRBR_NPI                                     AS prescriber_npi,
    PHARM_NPI                                       AS pharmacy_npi,
    ING_CST_SUB / 100.0                             AS ingredient_cost,
    DISP_FEE_SUB / 100.0                            AS dispensing_fee,
    SLS_TAX_AMT / 100.0                             AS sales_tax,
    GRS_AMT_DUE / 100.0                             AS gross_amount_due,
    PAT_PAY_AMT / 100.0                             AS patient_pay_amount,
    PLAN_PD_AMT / 100.0                             AS plan_paid_amount,
    PHARM_REIMB_AMT / 100.0                         AS amount_reimbursed_pharmacy,
    FRMLRY_TIER                                     AS formulary_tier,
    CMPND_CD                                        AS compound_code
FROM bronze.platform_a_claims
WHERE TX_STAT_CD IN ('1', '2', '3');