eligibility

A unique set of data associated with a member eligibility. (https://www.stedi.com/edi/x12/transaction-set/834)

Primary Key:

• eligibility_id

Checks:

• eligibility_case_number_check - (case_number = upper(case_number))
• eligibility_client_number_check - (client_number = upper(client_number))
• eligibility_cross_reference_number_check - (cross_reference_number = upper(cross_reference_number))
• eligibility_department_or_agency_number_check - (department_or_agency_number = upper(department_or_agency_number))
• eligibility_eligibility_client_reporting_category_check - (eligibility_client_reporting_category = upper(eligibility_client_reporting_category))
• eligibility_eligibility_mutually_defined_id_check - (eligibility_mutually_defined_id = upper(eligibility_mutually_defined_id))
• eligibility_eligibility_plan_sponsor_name_check - (eligibility_plan_sponsor_name = upper(eligibility_plan_sponsor_name))
• eligibility_health_insurance_claim_number_check - (health_insurance_claim_number = upper(health_insurance_claim_number))
• eligibility_ncpdp_pharmacy_number_check - (ncpdp_pharmacy_number = upper(ncpdp_pharmacy_number))
• eligibility_personal_id_number_check - (personal_id_number = upper(personal_id_number))
• eligibility_pin_number_check - (pin_number = upper(pin_number))
• eligibility_plan_sponsor_other_id_check - (plan_sponsor_other_id = upper(plan_sponsor_other_id))
• eligibility_plan_sponsor_tax_id_check - (plan_sponsor_tax_id ~ '^[0-9]*$'::text)
• eligibility_position_check - ("position" = upper("position"))
• eligibility_unit_check - (unit = upper(unit))

Indexes:

• eligibility_uq (Unique) - eligibility_hash

Column	Data Type	Nullable	Indexed	Description
eligibility_id	bigint	No	Yes	Snowflake ID for eligibility Generated through expression:
eligibility_plan_sponsor_name	text	No	No	Plan sponsor. This is typically the employer name (From N102 P5) Generated through expression:
plan_sponsor_tax_id	text	No	No	Plan sponsor Tax ID (From N104 FI or 24) Generated through expression:
plan_sponsor_other_id	text	No	No	Plan sponsor mutually defined ID (From N104 ZZ or 94) Generated through expression:
is_subscriber	boolean	No	No	Indicates whether member is the subscriber (From INS01) Generated through expression:
relationship_to_subscriber	individual_relationship_type	No	No	Describes member relationship with subscriber (From INS02) Generated through expression:
benefit_status	benefit_status_type	Yes	No	Describes the type of coverage. Could be Active, COBRA, Surviving Insured, TEFRA (From INS05) Generated through expression:
medicare_plan	medicare_plan_type	Yes	No	Describes Medicare enrollment for member (From INS06_01) Generated through expression:
medicare_eligibility_reason	medicare_eligibility_reason_type	Yes	No	Explains why member is eligible for Medicare (From INS06_02) Generated through expression:
cobra_qualifying_event	cobra_qualifying_event_type	Yes	No	The event which makes the member eligible for COBRA coverage (From INS07) Generated through expression:
employment_status	employment_status_type	Yes	No	Current employment status of the member (From INS08) Generated through expression:
student_status	student_status_type	Yes	No	Current student status of the member (From INS09) Generated through expression:
handicap_status	yes_no_response_type	Yes	No	Current student status of the member (From INS10) Generated through expression:
death_date	date	Yes	No	Date of death of the member (From INS12) Generated through expression:
confidential_access	confidential_access_type	Yes	No	Confidentiality code for the member (From INS13) Generated through expression:
eligibility_client_reporting_category	text	No	No	Provides information for subscriber reporting to client (From REF 17) Generated through expression:
client_number	text	No	No	To be used to pass a payer specific identifier for a member (From REF 23) Generated through expression:
case_number	text	No	No	Case number (From REF 3H) Generated through expression:
pin_number	text	No	No	Personal identification number (From REF 4A) Generated through expression:
cross_reference_number	text	No	No	Cross reference number (From REF 6O) Generated through expression:
personal_id_number	text	No	No	Personal identification number (From REF ABB) Generated through expression:
ncpdp_pharmacy_number	text	No	No	NCPDP pharmacy number (From REF D3). This is the member's preferred pharmacy Generated through expression:
department_or_agency_number	text	No	No	Department or agency the employee works in (From REF DX) Generated through expression:
health_insurance_claim_number	text	No	No	Used to report Medicare eligibility (From REF F6) Generated through expression:
position	text	No	No	Position the employee works in (From REF P5) Generated through expression:
unit	text	No	No	A unit of members in a coverage group (From REF QQ) Generated through expression:
eligibility_mutually_defined_id	text	No	No	Additional identifier as agreed upon by trading partners (From REF ZZ) Generated through expression:
enrollment_application_received	date	Yes	No	Enrollment application received date (From DTP 050) Generated through expression:
retirement	date	Yes	No	Retirement date (From DTP 286) Generated through expression:
initial_disability_period_return_to_work	date	Yes	No	Initial Disability Period Return To Work date (From DTP 296) Generated through expression:
initial_disability_period_last_day_worked	date	Yes	No	Initial Disability Period Last Day Worked date (From DTP 297) Generated through expression:
enrollment_signature_date	date	Yes	No	Enrollment Signature Date date (From DTP 300) Generated through expression:
cobra_qualifying_event_date	date	Yes	No	cobra Qualifying Event date (From DTP 301) Generated through expression:
employment_begin	date	Yes	No	Employment Begin date (From DTP 336) Generated through expression:
employment_end	date	Yes	No	Employment End date (From DTP 337) Generated through expression:
medicare_begin	date	Yes	No	Medicare Begin date (From DTP 338) Generated through expression:
medicare_end	date	Yes	No	Medicare End date (From DTP 339) Generated through expression:
cobra_begin	date	Yes	No	cobra Begin date (From DTP 340) Generated through expression:
cobra_end	date	Yes	No	cobra End date (From DTP 341) Generated through expression:
education_begin	date	Yes	No	Education Begin date (From DTP 350) Generated through expression:
education_end	date	Yes	No	Education End date (From DTP 351) Generated through expression:
eligibility_begin	date	Yes	No	Eligibility Begin date (From DTP 356) Generated through expression:
eligibility_end	date	Yes	No	Eligibility End date (From DTP 357) Generated through expression:
adjusted_hire	date	Yes	No	Adjusted Hire date (From DTP 383) Generated through expression:
credited_service_begin	date	Yes	No	Credited Service Begin date (From DTP 385) Generated through expression:
credited_service_end	date	Yes	No	Credited Service End date (From DTP 386) Generated through expression:
plan_participation_suspension	date	Yes	No	Plan Participation Suspension date (From DTP 393) Generated through expression:
rehire	date	Yes	No	Rehire date (From DTP 394) Generated through expression:
medicaid_begin	date	Yes	No	Medicaid Begin date (From DTP 473) Generated through expression:
medicaid_end	date	Yes	No	Medicaid End date (From DTP 474) Generated through expression:
marital_status	marital_status_type	Yes	No	Marital status (From DMG04) Generated through expression:
citizenship_status	citizenship_status_type	Yes	No	Citizenship status (From DMG06) Generated through expression:
health_situation	health_situation_type	Yes	No	Indicates a specific health situation (From HLH01) Generated through expression:
height	numeric	Yes	No	Member height in inches (From HLH02) Generated through expression:
weight	numeric	Yes	No	Member weight in pounds (From HLH03) Generated through expression:
disability_status	disability_status_type	Yes	No	Disability status (From DSB01) Generated through expression:
disability_begin	date	Yes	No	Disability begin date (From DTP 360) Generated through expression:
disability_end	date	Yes	No	Disability end date (From DTP 361) Generated through expression:
eligibility_hash	uuid	No	Yes	A hash of the eligibility data Generated through expression: