coverage

A unique set of data associated with a member coverage.

Primary Key:

coverage_id

Checks:

coverage_bin_check - (bin = upper(bin))
coverage_class_of_contract_code_check - (class_of_contract_code = upper(class_of_contract_code))
coverage_county_code_check - (county_code = upper(county_code))
coverage_coverage_client_reporting_category_check - (coverage_client_reporting_category = upper(coverage_client_reporting_category))
coverage_coverage_description_check - (coverage_description = upper(coverage_description))
coverage_coverage_mutually_defined_id_check - (coverage_mutually_defined_id = upper(coverage_mutually_defined_id))
coverage_group_name_check - (group_name = upper(group_name))
coverage_group_or_policy_number_check - (group_or_policy_number = upper(group_or_policy_number))
coverage_group_or_policy_number_eligibility_ref1l_check - (group_or_policy_number_eligibility_ref1l = upper(group_or_policy_number_eligibility_ref1l))
coverage_group_or_policy_number_global_ref38_check - (group_or_policy_number_global_ref38 = upper(group_or_policy_number_global_ref38))
coverage_internal_control_number_check - (internal_control_number = upper(internal_control_number))
coverage_issuer_number_check - (issuer_number = upper(issuer_number))
coverage_medical_assistance_category_check - (medical_assistance_category = upper(medical_assistance_category))
coverage_medical_plan_identification_code_check - (medical_plan_identification_code = upper(medical_plan_identification_code))
coverage_medical_plan_name_check - (medical_plan_name = upper(medical_plan_name))
coverage_payment_category_check - (payment_category = upper(payment_category))
coverage_pcn_check - (pcn = upper(pcn))
coverage_prior_coverage_months_check - (prior_coverage_months = upper(prior_coverage_months))
coverage_program_identification_number_check - (program_identification_number = upper(program_identification_number))
coverage_rate_code_number_check - (rate_code_number = upper(rate_code_number))
coverage_rx_group_check - (rx_group = upper(rx_group))
coverage_rx_plan_identification_code_check - (rx_plan_identification_code = upper(rx_plan_identification_code))
coverage_rx_plan_name_check - (rx_plan_name = upper(rx_plan_name))
coverage_service_area_code_check - (service_area_code = upper(service_area_code))
coverage_service_contract_coverage_number_check - (service_contract_coverage_number = upper(service_contract_coverage_number))
coverage_special_program_code_check - (special_program_code = upper(special_program_code))

Indexes:

coverage_uq (Unique) - coverage_hash

Column	Data Type	Nullable	Indexed	Description
`coverage_id`	`bigint`	No	Yes	Snowflake ID for coverage. Generated through expression:
`insurance_line`	`insurance_line_type`	No	No	Identifies the type of insurance (From HD03) Generated through expression:
`coverage_description`	`text`	No	No	Describes the type of plan or coverage (From HD04) Generated through expression:
`coverage_level`	`coverage_level_type`	Yes	No	Identifies the level of coverage (From HD05) Generated through expression:
`is_late_enrollee`	`boolean`	Yes	No	Identifies a late enrollee (From HD09) Generated through expression:
`premium_paid_to_date_end`	`date`	Yes	No	Premium Paid to Date End (From DTP 343) Generated through expression:
`benefit_begin`	`date`	Yes	No	Benefit Begin (From DTP 348) Generated through expression:
`benefit_end`	`date`	Yes	No	Benefit End (From DTP 349) Generated through expression:
`last_premium_paid_date`	`date`	Yes	No	Last Premium Paid Date (From DTP 543) Generated through expression:
`previous_period`	`date`	Yes	No	Previous Period (From DTP 695) Generated through expression:
`coinsurance`	`numeric`	Yes	No	Amount of coinsurance for this particular benefit (From AMT B9) Generated through expression:
`copay`	`numeric`	Yes	No	Amount of copay for this particular benefit (From AMT C1) Generated through expression:
`deductible`	`numeric`	Yes	No	Amount of deductible for this particular benefit (From AMT D2) Generated through expression:
`expected_expenditure_amount`	`numeric`	Yes	No	Expected Expenditure Amount (From AMT EBA) Generated through expression:
`other_unlisted_amount`	`numeric`	Yes	No	Other Unlisted Amount (From AMT FK) Generated through expression:
`premium_amount`	`numeric`	Yes	No	Premium Amount (From AMT P3) Generated through expression:
`spend_down`	`numeric`	Yes	No	Spend Down (From AMT R) Generated through expression:
`coverage_client_reporting_category`	`text`	No	No	Provides information for subscriber reporting to client (From REF 17) Generated through expression:
`group_or_policy_number_global_ref38`	`text`	No	No	Group or policy number that applies to all members in 834 data (From header REF 38) Generated through expression:
`group_or_policy_number_eligibility_ref1l`	`text`	No	No	Member group or policy number (From 834 2000 loop, REF 1L) Generated through expression:
`group_or_policy_number`	`text`	No	No	Group identification. (From REF 1L) Generated through expression:
`group_name`	`text`	No	No	Group name. This is most often the same name as the plan sponsor, but may be a subset of the plan sponsor population (e.g. Intel California) Generated through expression:
`medical_plan_identification_code`	`text`	No	No	Plan identification. No specific location in 834, but may be sent in CSV or other format Generated through expression:
`medical_plan_name`	`text`	No	No	Plan identification. No specific location in 834, but may be sent in CSV or other format Generated through expression:
`bin`	`text`	No	No	Pharmacy bank identification number. This is the top-level identifier for RX claims Generated through expression:
`rx_group`	`text`	No	No	Pharmacy group identification. This is the 2nd level of identification for RX claims Generated through expression:
`pcn`	`text`	No	No	Pharmacy processor control number. This is the 3rd level of identification for RX claims and represents an RX plan Generated through expression:
`rx_plan_identification_code`	`text`	No	No	RX plan identification. No specific location in 834, but may be sent in CSV or other format Generated through expression:
`rx_plan_name`	`text`	No	No	RX plan name. No specific location in 834, but may be sent in CSV or other format Generated through expression:
`payment_category`	`text`	No	No	Payment Category (From REF 9V) Generated through expression:
`class_of_contract_code`	`text`	No	No	Class of Contract Code (From REF CE) Generated through expression:
`service_contract_coverage_number`	`text`	No	No	Service Contract (Coverage) Number (From REF E8) Generated through expression:
`medical_assistance_category`	`text`	No	No	Medical Assistance Category (From REF M7) Generated through expression:
`program_identification_number`	`text`	No	No	Program Identification Number (From REF PID) Generated through expression:
`prior_coverage_months`	`text`	No	No	This is the unit number REF, but is used to mean 'Prior coverage months' in the 834 spec (From REF QQ) Generated through expression:
`rate_code_number`	`text`	No	No	Rate code number (From REF RB) Generated through expression:
`internal_control_number`	`text`	No	No	Internal Control Number (From REF X9) Generated through expression:
`issuer_number`	`text`	No	No	Issuer Number (From REF XM) Generated through expression:
`special_program_code`	`text`	No	No	Special Program Code (From REF XX1) Generated through expression:
`service_area_code`	`text`	No	No	Service Area Code (From REF XX2) Generated through expression:
`county_code`	`text`	No	No	County Code (From REF ZX) Generated through expression:
`coverage_mutually_defined_id`	`text`	No	No	Additional identifier as agreed upon by trading partners (From REF ZZ) Generated through expression:
`coverage_hash`	`uuid`	No	Yes	A hash of the coverage data Generated through expression: