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_client_reporting_category_check - (coverage_client_reporting_category = upper(coverage_client_reporting_category))
• coverage_county_code_check - (county_code = upper(county_code))
• coverage_coverage_description_check - (coverage_description = upper(coverage_description))
• 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_mutually_defined_id_check - (coverage_mutually_defined_id = upper(coverage_mutually_defined_id))
• 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	-
insurance_line	insurance_line_type	No	No	Identifies the type of insurance (From HD03)
coverage_description	text	No	No	Describes the type of plan or coverage (From HD04)
coverage_level	coverage_level_type	Yes	No	Identifies the level of coverage (From HD05)
is_late_enrollee	boolean	Yes	No	Identifies a late enrollee (From HD09)
premium_paid_to_date_end	date	Yes	No	Premium Paid to Date End (From DTP 343)
benefit_begin	date	Yes	No	Benefit Begin (From DTP 348)
benefit_end	date	Yes	No	Benefit End (From DTP 349)
last_premium_paid_date	date	Yes	No	Last Premium Paid Date (From DTP 543)
previous_period	date	Yes	No	Previous Period (From DTP 695)
coinsurance	numeric	Yes	No	Amount of coinsurance for this particular benefit (From AMT B9)
copay	numeric	Yes	No	Amount of copay for this particular benefit (From AMT C1)
deductible	numeric	Yes	No	Amount of deductible for this particular benefit (From AMT D2)
expected_expenditure_amount	numeric	Yes	No	Expected Expenditure Amount (From AMT EBA)
other_unlisted_amount	numeric	Yes	No	Other Unlisted Amount (From AMT FK)
premium_amount	numeric	Yes	No	Premium Amount (From AMT P3)
spend_down	numeric	Yes	No	Spend Down (From AMT R)
coverage_client_reporting_category	text	No	No	Provides information for subscriber reporting to client (From REF 17)
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)
group_or_policy_number_eligibility_ref1l	text	No	No	Member group or policy number (From 834 2000 loop, REF 1L)
group_or_policy_number	text	No	No	Group identification. (From REF 1L)
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)
medical_plan_identification_code	text	No	No	Plan identification. No specific location in 834, but may be sent in CSV or other format
medical_plan_name	text	No	No	Plan identification. No specific location in 834, but may be sent in CSV or other format
bin	text	No	No	Pharmacy bank identification number. This is the top-level identifier for RX claims
rx_group	text	No	No	Pharmacy group identification. This is the 2nd level of identification for RX claims
pcn	text	No	No	Pharmacy processor control number. This is the 3rd level of identification for RX claims and represents an RX plan
rx_plan_identification_code	text	No	No	RX plan identification. No specific location in 834, but may be sent in CSV or other format
rx_plan_name	text	No	No	RX plan name. No specific location in 834, but may be sent in CSV or other format
payment_category	text	No	No	Payment Category (From REF 9V)
class_of_contract_code	text	No	No	Class of Contract Code (From REF CE)
service_contract_coverage_number	text	No	No	Service Contract (Coverage) Number (From REF E8)
medical_assistance_category	text	No	No	Medical Assistance Category (From REF M7)
program_identification_number	text	No	No	Program Identification Number (From REF PID)
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)
rate_code_number	text	No	No	Rate code number (From REF RB)
internal_control_number	text	No	No	Internal Control Number (From REF X9)
issuer_number	text	No	No	Issuer Number (From REF XM)
special_program_code	text	No	No	Special Program Code (From REF XX1)
service_area_code	text	No	No	Service Area Code (From REF XX2)
county_code	text	No	No	County Code (From REF ZX)
coverage_mutually_defined_id	text	No	No	Additional identifier as agreed upon by trading partners (From REF ZZ)
coverage_hash	uuid	No	Yes	A hash of the coverage data