Cost

Destination Field	Source field	Logic
cost_id		Autogenerated
cost_event_id	code	Map by mapping the cdm.procedure_occurrence.procedure_source_value to procedures.code, map to a unique visit_occurrence_id and and pull in the associated visit procedure_occurrence_id
cost_domain_id		Set to ‘Procedure’ for all records
cost_type_concept_id		Set to 32814 (Cost Record) for all records
currency_concept_id		Set to 44818668 (United States Dollars) for all records
total_charge		encounters.total_claim_cost + procedures.base_cost
total_cost		encounters.total_claim_cost + procedures.base_cost
total_paid		encounters.payer_coverage + procedures.base_cost
paid_by_payer		encounters.payer_coverage
paid_by_patient		encounters.total_claim_cost + procedures.base_cost - encounters.payer_coverage
paid_patient_copay		cast(null as numeric)
paid_patient_coinsurance		cast(null as numeric)
paid_patient_deductible		cast(null as numeric)
paid_by_primary		cast(null as numeric)
paid_ingredient_cost		cast(null as numeric)
paid_dispensing_fee		cast(null as numeric)
payer_plan_period_id		Map to cdm.payer_plan_period.payer_plan_period_id using person_id and where payer_plan_period_start_date <= procedure_date and payer_plan_period_end_date >= procedure_date
amount_allowed		cast(null as numeric)
revenue_code_concept_id		Set to 0 for all records.
revenue_code_source_value		Set to ‘UNKNOWN/UKNOWN’ for all records.
drg_concept_id		Set to 0 for all records.
drg_source_value		Set to ‘000’ for all records.

Destination Field	Source field	Logic
cost_id		Autogenerated
cost_event_id	code	Map by mapping the cdm.drug_exposure.drug_source_value to immunizations.code, map to a unique visit_occurrence_id and and pull in the associated visit drug_exposure_id
cost_domain_id		Set to ‘Drug’ for all records
cost_type_concept_id		Set to 32814 (Cost Record) for all records
currency_concept_id		Set to 44818668 (United States Dollars) for all records
total_charge		encounters.total_claim_cost + immunizatons.base_cost
total_cost		encounters.total_claim_cost + immunizatons.base_cost
total_paid		encounters.payer_coverage + immunizatons.base_cost
paid_by_payer		encounters.payer_coverage
paid_by_patient		encounters.total_claim_cost + immunizatons.base_cost - encounters.payer_coverage
paid_patient_copay		cast(null as numeric)
paid_patient_coinsurance		cast(null as numeric)
paid_patient_deductible		cast(null as numeric)
paid_by_primary		cast(null as numeric)
paid_ingredient_cost		cast(null as numeric)
paid_dispensing_fee		cast(null as numeric)
payer_plan_period_id		Map to cdm.payer_plan_period.payer_plan_period_id using person_id and where payer_plan_period_start_date <= drug_exposure_start_date and payer_plan_period_end_date >= drug_exposure_start_date
amount_allowed		cast(null as numeric)
revenue_code_concept_id		Set to 0 for all records.
revenue_code_source_value		Set to ‘UNKNOWN/UKNOWN’ for all records.
drg_concept_id		Set to 0 for all records.
drg_source_value		Set to ‘000’ for all records.

Destination Field	Source field	Logic
cost_id		Autogenerated
cost_event_id	code	Map by mapping the cdm.drug_exposure.drug_source_value to immunizations.code, map to a unique visit_occurrence_id and and pull in the associated visit drug_exposure_id
cost_domain_id		Set to ‘Drug’ for all records
cost_type_concept_id		Set to 32814 (Cost Record) for all records
currency_concept_id		Set to 44818668 (United States Dollars) for all records
total_charge		encounters.total_claim_cost + medications.base_cost
total_cost		encounters.total_claim_cost + medications.base_cost
total_paid		encounters.payer_coverage + medications.base_cost
paid_by_payer		encounters.payer_coverage
paid_by_patient		encounters.total_claim_cost + medications.base_cost - encounters.payer_coverage
paid_patient_copay		cast(null as numeric)
paid_patient_coinsurance		cast(null as numeric)
paid_patient_deductible		cast(null as numeric)
paid_by_primary		cast(null as numeric)
paid_ingredient_cost		cast(null as numeric)
paid_dispensing_fee		cast(null as numeric)
payer_plan_period_id		Map to cdm.payer_plan_period.payer_plan_period_id using person_id and where payer_plan_period_start_date <= drug_exposure_start_date and payer_plan_period_end_date >= drug_exposure_start_date
amount_allowed		cast(null as numeric)
revenue_code_concept_id		Set to 0 for all records.
revenue_code_source_value		Set to ‘UNKNOWN/UKNOWN’ for all records.
drg_concept_id		Set to 0 for all records.
drg_source_value		Set to ‘000’ for all records.

Filter where claims_transactions.transfertype in (‘1’,‘p’)
Group by condition_occurrence_id, payer_plan_period_id
Payer_paid = coalesce(sum(case when claims_transactions.transfertype = ‘1’ then claims_transactions.amount end),0)
Patient_paid = coalesce(sum(case when claims_transactions.transfertype = ‘p’ then claims_transactions.amount end),0)

Destination Field	Source field	Logic
cost_id		Autogenerated
cost_event_id	code	Map by mapping the cdm.condition_occurrence.condition_source_value to conditions.code, map to a unique visit_occurrence_id and pull in the associated visit condition_occurrence_id
cost_domain_id		Set to ‘Condition’ for all records
cost_type_concept_id		Set to 32814 (Cost Record) for all records
currency_concept_id		Set to 44818668 (United States Dollars) for all records
total_charge		payer_paid+patient_paid
total_cost		payer_paid+patient_paid
total_paid		epayer_paid+patient_paid
paid_by_payer		payer_paid
paid_by_patient		patient_paid
paid_patient_copay		cast(null as numeric)
paid_patient_coinsurance		cast(null as numeric)
paid_patient_deductible		cast(null as numeric)
paid_by_primary		payer_paid
paid_ingredient_cost		cast(null as numeric)
paid_dispensing_fee		cast(null as numeric)
payer_plan_period_id		Map to cdm.payer_plan_period.payer_plan_period_id using person_id and where payer_plan_period_start_date <= condition_start_date and payer_plan_period_end_date >= condition_start_date
amount_allowed		cast(null as numeric)
revenue_code_concept_id		Set to 0 for all records.
revenue_code_source_value		Set to ‘UNKNOWN/UKNOWN’ for all records.
drg_concept_id		Set to 0 for all records.
drg_source_value		Set to ‘000’ for all records.