member_coverage_view

Materialized: false

Definition

 SELECT mc.member_coverage_id,
    me.member_eligibility_id,
    me.subscriber_member_eligibility_id,
    s.trading_partner_submission_id,
    s.trading_partner_id,
    s.reference_number,
    s.submit_time,
    s.receive_time,
    s.file_name,
    e.eligibility_id,
    e.eligibility_plan_sponsor_name,
    e.plan_sponsor_tax_id,
    e.plan_sponsor_other_id,
    e.is_subscriber,
    e.relationship_to_subscriber,
    e.benefit_status,
    e.medicare_plan,
    e.medicare_eligibility_reason,
    e.cobra_qualifying_event,
    e.employment_status,
    e.student_status,
    e.handicap_status,
    e.death_date,
    e.confidential_access,
    e.eligibility_client_reporting_category,
    e.client_number,
    e.case_number,
    e.pin_number,
    e.cross_reference_number,
    e.personal_id_number,
    e.ncpdp_pharmacy_number,
    e.department_or_agency_number,
    e.health_insurance_claim_number,
    e."position",
    e.unit,
    e.eligibility_mutually_defined_id,
    e.enrollment_application_received,
    e.retirement,
    e.initial_disability_period_return_to_work,
    e.initial_disability_period_last_day_worked,
    e.enrollment_signature_date,
    e.cobra_qualifying_event_date,
    e.employment_begin,
    e.employment_end,
    e.medicare_begin,
    e.medicare_end,
    e.cobra_begin,
    e.cobra_end,
    e.education_begin,
    e.education_end,
    e.eligibility_begin,
    e.eligibility_end,
    e.adjusted_hire,
    e.credited_service_begin,
    e.credited_service_end,
    e.plan_participation_suspension,
    e.rehire,
    e.medicaid_begin,
    e.medicaid_end,
    e.marital_status,
    e.citizenship_status,
    e.health_situation,
    e.height,
    e.weight,
    e.disability_status,
    e.disability_begin,
    e.disability_end,
    e.eligibility_hash,
    me.prior_id_number,
    me.eligibility_maintenance_type,
    me.eligibility_maintenance_reason,
    me.eligibility_maintenance_effective_date,
    ms.member_seen_id,
    ms.name_last,
    ms.name_first,
    ms.name_middle,
    ms.name_prefix,
    ms.name_suffix,
    ms.date_of_birth,
    ms.gender_or_sex,
    ms.member_ssn,
    ms.member_identification_code,
    ms.family_identification_code,
    ms.person_code,
    ms.address_id,
    ms.alternate_phone_number_id,
    ms.beeper_number_id,
    ms.cell_phone_number_id,
    ms.email_address_id,
    ms.fax_number_id,
    ms.home_phone_number_id,
    ms.phone_number_id,
    ms.work_phone_number_id,
    ms.phone_extension,
    ms.member_canonical_id_chosen,
    ms.member_canonical_id_chosen_at,
    ms.member_canonical_id_chosen_by,
    ms.member_match_status,
    a.canonical_address_id,
    a.canonical_primary_address_only_id,
    a.is_invalid_address,
    a.address1,
    a.address2,
    a.city,
    a.state_or_province,
    a.postal_code,
    a.country_code,
    a.country_subdivision,
    home.phone_number AS home_phone_number,
    cell.phone_number AS cell_phone_number,
    work.phone_number AS work_phone_number,
    ph.phone_number,
    alt.phone_number AS alternate_phone_number,
    fax.fax_number,
    email.email_address,
    me.prior_incorrect_member_seen_id,
    c.coverage_id,
    c.insurance_line,
    c.coverage_description,
    c.coverage_level,
    c.is_late_enrollee,
    c.premium_paid_to_date_end,
    c.benefit_begin,
    c.benefit_end,
    c.last_premium_paid_date,
    c.previous_period,
    c.coinsurance,
    c.copay,
    c.deductible,
    c.expected_expenditure_amount,
    c.other_unlisted_amount,
    c.premium_amount,
    c.spend_down,
    c.coverage_client_reporting_category,
    c.group_or_policy_number_global_ref38,
    c.group_or_policy_number_eligibility_ref1l,
    c.group_or_policy_number,
    c.group_name,
    c.medical_plan_identification_code,
    c.medical_plan_name,
    c.bin,
    c.rx_group,
    c.pcn,
    c.rx_plan_identification_code,
    c.rx_plan_name,
    c.payment_category,
    c.class_of_contract_code,
    c.service_contract_coverage_number,
    c.medical_assistance_category,
    c.program_identification_number,
    c.prior_coverage_months,
    c.rate_code_number,
    c.internal_control_number,
    c.issuer_number,
    c.special_program_code,
    c.service_area_code,
    c.county_code,
    c.coverage_mutually_defined_id,
    c.coverage_hash,
    mc.coverage_maintenance_type,
    mc.coverage_maintenance_effective_date,
    mc.coverage_group_or_plan_id,
    ps.external_plan_sponsor_id,
    ps.plan_sponsor_name
   FROM claims.member_coverage mc
     JOIN claims.member_eligibility me ON me.member_eligibility_id = mc.member_eligibility_id
     JOIN claims.coverage c ON c.coverage_id = mc.coverage_id
     JOIN claims.trading_partner_submission s ON s.trading_partner_submission_id = me.trading_partner_submission_id
     JOIN claims.eligibility e ON e.eligibility_id = me.eligibility_id
     JOIN claims.member_seen ms ON ms.member_seen_id = me.member_seen_id
     LEFT JOIN claims.address_seen a ON a.address_id = ms.address_id
     LEFT JOIN claims.phone home ON home.phone_id = ms.home_phone_number_id
     LEFT JOIN claims.phone cell ON cell.phone_id = ms.cell_phone_number_id
     LEFT JOIN claims.phone work ON work.phone_id = ms.work_phone_number_id
     LEFT JOIN claims.phone ph ON ph.phone_id = ms.phone_number_id
     LEFT JOIN claims.phone alt ON alt.phone_id = ms.alternate_phone_number_id
     LEFT JOIN claims.fax ON fax.fax_id = ms.fax_number_id
     LEFT JOIN claims.email email ON email.email_id = ms.email_address_id
     JOIN claims.plan_sponsor_match m ON mc.coverage_group_or_plan_id = m.coverage_group_or_plan_id AND s.trading_partner_id = m.trading_partner_id
     JOIN claims.plan_sponsor ps ON ps.plan_sponsor_id = m.plan_sponsor_id;

Column	Data Type	Indexed
member_coverage_id	bigint	No
member_eligibility_id	bigint	No
subscriber_member_eligibility_id	bigint	No
trading_partner_submission_id	bigint	No
trading_partner_id	bigint	No
reference_number	text	No
submit_time	timestamp	No
receive_time	timestamp	No
file_name	text	No
eligibility_id	bigint	No
eligibility_plan_sponsor_name	text	No
plan_sponsor_tax_id	text	No
plan_sponsor_other_id	text	No
is_subscriber	boolean	No
relationship_to_subscriber	individual_relationship_type	No
benefit_status	benefit_status_type	No
medicare_plan	medicare_plan_type	No
medicare_eligibility_reason	medicare_eligibility_reason_type	No
cobra_qualifying_event	cobra_qualifying_event_type	No
employment_status	employment_status_type	No
student_status	student_status_type	No
handicap_status	yes_no_response_type	No
death_date	date	No
confidential_access	confidential_access_type	No
eligibility_client_reporting_category	text	No
client_number	text	No
case_number	text	No
pin_number	text	No
cross_reference_number	text	No
personal_id_number	text	No
ncpdp_pharmacy_number	text	No
department_or_agency_number	text	No
health_insurance_claim_number	text	No
position	text	No
unit	text	No
eligibility_mutually_defined_id	text	No
enrollment_application_received	date	No
retirement	date	No
initial_disability_period_return_to_work	date	No
initial_disability_period_last_day_worked	date	No
enrollment_signature_date	date	No
cobra_qualifying_event_date	date	No
employment_begin	date	No
employment_end	date	No
medicare_begin	date	No
medicare_end	date	No
cobra_begin	date	No
cobra_end	date	No
education_begin	date	No
education_end	date	No
eligibility_begin	date	No
eligibility_end	date	No
adjusted_hire	date	No
credited_service_begin	date	No
credited_service_end	date	No
plan_participation_suspension	date	No
rehire	date	No
medicaid_begin	date	No
medicaid_end	date	No
marital_status	marital_status_type	No
citizenship_status	citizenship_status_type	No
health_situation	health_situation_type	No
height	numeric	No
weight	numeric	No
disability_status	disability_status_type	No
disability_begin	date	No
disability_end	date	No
eligibility_hash	uuid	No
prior_id_number	text	No
eligibility_maintenance_type	maintenance_type	No
eligibility_maintenance_reason	maintenance_reason_type	No
eligibility_maintenance_effective_date	date	No
member_seen_id	bigint	No
name_last	text	No
name_first	text	No
name_middle	text	No
name_prefix	text	No
name_suffix	text	No
date_of_birth	date	No
gender_or_sex	gender_or_sex_type	No
member_ssn	text	No
member_identification_code	text	No
family_identification_code	text	No
person_code	text	No
address_id	bigint	No
alternate_phone_number_id	bigint	No
beeper_number_id	bigint	No
cell_phone_number_id	bigint	No
email_address_id	bigint	No
fax_number_id	bigint	No
home_phone_number_id	bigint	No
phone_number_id	bigint	No
work_phone_number_id	bigint	No
phone_extension	text	No
member_canonical_id_chosen	bigint	No
member_canonical_id_chosen_at	timestamptz	No
member_canonical_id_chosen_by	bigint	No
member_match_status	match_status_type	No
canonical_address_id	bigint	No
canonical_primary_address_only_id	bigint	No
is_invalid_address	boolean	No
address1	text	No
address2	text	No
city	text	No
state_or_province	text	No
postal_code	text	No
country_code	text	No
country_subdivision	text	No
home_phone_number	text	No
cell_phone_number	text	No
work_phone_number	text	No
phone_number	text	No
alternate_phone_number	text	No
fax_number	text	No
email_address	text	No
prior_incorrect_member_seen_id	bigint	No
coverage_id	bigint	No
insurance_line	insurance_line_type	No
coverage_description	text	No
coverage_level	coverage_level_type	No
is_late_enrollee	boolean	No
premium_paid_to_date_end	date	No
benefit_begin	date	No
benefit_end	date	No
last_premium_paid_date	date	No
previous_period	date	No
coinsurance	numeric	No
copay	numeric	No
deductible	numeric	No
expected_expenditure_amount	numeric	No
other_unlisted_amount	numeric	No
premium_amount	numeric	No
spend_down	numeric	No
coverage_client_reporting_category	text	No
group_or_policy_number_global_ref38	text	No
group_or_policy_number_eligibility_ref1l	text	No
group_or_policy_number	text	No
group_name	text	No
medical_plan_identification_code	text	No
medical_plan_name	text	No
bin	text	No
rx_group	text	No
pcn	text	No
rx_plan_identification_code	text	No
rx_plan_name	text	No
payment_category	text	No
class_of_contract_code	text	No
service_contract_coverage_number	text	No
medical_assistance_category	text	No
program_identification_number	text	No
prior_coverage_months	text	No
rate_code_number	text	No
internal_control_number	text	No
issuer_number	text	No
special_program_code	text	No
service_area_code	text	No
county_code	text	No
coverage_mutually_defined_id	text	No
coverage_hash	uuid	No
coverage_maintenance_type	maintenance_type	No
coverage_maintenance_effective_date	date	No
coverage_group_or_plan_id	bigint	No
external_plan_sponsor_id	text	No
plan_sponsor_name	text	No