claim_member

Member information for a claim. Includes subscriber and patient (when provided) information

Primary Key:

• claim_member_id

Checks:

• claim_member_check - ((member_identification_code <> ''::text) OR (member_ssn <> ''::text))

Foreign Keys:

• insurer_payer_id => insurer_payer.insurer_payer_id
• member_canonical_id_chosen_by => login.login_id
• member_canonical_id_chosen => member_canonical.member_id
• member_person_or_org_id => person_or_org.person_or_org_id
• patient_person_or_org_id => person_or_org.person_or_org_id

Indexes:

• claim_member_insurer_payer_id_idx - insurer_payer_id
• claim_member_member_canonical_id_chosen_idx - member_canonical_id_chosen
• claim_member_patient_person_or_org_id_idx - patient_person_or_org_id
• claim_member_person_or_org_id_idx - member_person_or_org_id
• claim_member_uq (Unique) - member_person_or_org_id, patient_person_or_org_id, payer_responsibility_sequence, relationship_to_subscriber, group_or_policy_number, group_name, insurance_type, claim_filing_indicator, member_ssn, member_identification_code, insurer_payer_id

Column	Data Type	Nullable	Indexed	Description
claim_member_id	bigint	No	Yes	-
payer_responsibility_sequence	payer_responsibility_sequence_type	Yes	Yes	Payer responsibility order for Coordination of Benefits (From SBR01)
relationship_to_subscriber	individual_relationship_code	Yes	Yes	Relation of individual to the subscriber (From SBR02 / PAT01)
group_or_policy_number	text	No	Yes	Subscriber group or policy number (From SBR03)
group_name	text	No	Yes	Subscriber Group Name (From SBR04)
insurance_type	insurance_type	Yes	Yes	Type of insurance (From SBR05)
claim_filing_indicator	claim_filing_indicator_type	Yes	Yes	Type of claim (From SBR09)
patient_person_or_org_id	bigint	Yes	Yes	Points to the patient when both subscriber and patient are given in the claim (from Loop 2000C)
insurer_payer_id	bigint	Yes	Yes	Link to insurer_payer table (From NM1 loop 2010BB)
member_person_or_org_id	bigint	No	Yes	Link to person_or_org table (From NM1 loop 2010BA)
member_ssn	text	No	Yes	Social Security Number (From NM109 code 34 or REF SY)
member_identification_code	text	No	Yes	Member identifier (From NM109 code MI [837 claim] or ZZ [834 eligibility])
member_canonical_id_chosen	bigint	Yes	Yes	Member ID chosen through manual or automated member matching
member_canonical_id_chosen_at	date	Yes	No	Date when the member ID was selected
member_canonical_id_chosen_by	bigint	Yes	No	Indicates who (manual or automated) chose the member ID