claim_member

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

Primary Key:

• claim_member_id

Checks:

• claim_member_canonical_complete_check - ((member_match_status = 'Complete'::claims.match_status_type) = ((member_canonical_id_chosen IS NOT NULL) AND (member_canonical_id_chosen_at IS NOT NULL) AND (member_canonical_id_chosen_by IS NOT NULL)))
• claim_member_check - ((member_identification_code <> ''::text) OR (member_ssn <> ''::text))
• claim_member_group_name_check - (group_name = upper(group_name))
• claim_member_group_or_policy_number_check - (group_or_policy_number = upper(group_or_policy_number))
• claim_member_individual_health_identifier_check - (individual_health_identifier = upper(individual_health_identifier))
• claim_member_member_identification_code_check - (member_identification_code = upper(member_identification_code))

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_canonical_id
• member_subscriber_or_patient_id => subscriber_or_patient.subscriber_or_patient_id
• patient_subscriber_or_patient_id => subscriber_or_patient.subscriber_or_patient_id

Indexes:

• claim_member_insurer_payer_id_idx - insurer_payer_id
• claim_member_member_canonical_id_chosen_idx - member_canonical_id_chosen
• claim_member_member_match_status_idx - member_match_status
• claim_member_member_ssn_idx - member_ssn
• claim_member_member_subscriber_or_patient_id_idx - member_subscriber_or_patient_id
• claim_member_patient_subscriber_or_patient_id_idx - patient_subscriber_or_patient_id
• claim_member_uq (Unique) - member_subscriber_or_patient_id, patient_subscriber_or_patient_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	Snowflake ID for the claim member. Generated through expression:
payer_responsibility_sequence	payer_responsibility_sequence_type	Yes	Yes	Payer responsibility order for Coordination of Benefits (From SBR01) Generated through expression:
relationship_to_subscriber	individual_relationship_type	Yes	Yes	Relation of individual to the subscriber (From SBR02 / PAT01 / UB04 FL59 / HCFA item 6) Generated through expression:
group_or_policy_number	text	No	Yes	Subscriber group or policy number (From SBR03 / UB04 FL62 / HCFA item 11) Generated through expression:
group_name	text	No	Yes	Subscriber Group Name (From SBR04 / UB04 FL61 / HCFA item 11c) Generated through expression:
insurance_type	insurance_type	Yes	Yes	Type of insurance (From SBR05) Generated through expression:
claim_filing_indicator	claim_filing_indicator_type	Yes	Yes	Type of claim (From SBR09 / HCFA item 1 - because 837 values don't map cleanly to HCFA checkboxes, we can only fill 'Medicaid', 'Medicare', and 'Other') Generated through expression:
patient_subscriber_or_patient_id	bigint	Yes	Yes	Points to the patient when both subscriber and patient are given in the claim (from Loop 2000C / UB04 FL08-11 / HCFA items 2,3,5) Generated through expression:
insurer_payer_id	bigint	Yes	Yes	Link to insurer_payer table (From NM1 loop 2010BB / UB04 FL50-51) Generated through expression:
member_subscriber_or_patient_id	bigint	No	Yes	Points to the uniquely identifiable member (could be either the subscriber or dependent [if the dependent has a unique member id]) (From NM1 loop 2010BA / UB04 FL38 for responsible party, FL58 for subscriber / HCFA items 4,7,11a) Generated through expression:
member_ssn	text	No	Yes	Social Security Number (From NM109 code 34 or REF SY / HCFA item 1a) Generated through expression:
member_identification_code	text	No	Yes	Member identifier (From NM109 code MI [837 claim] or ZZ [834 eligibility] / UB04 FL60 for subscriber, FL08a for patient if different from subscriber identifier / HCFA item 1a) Generated through expression:
member_canonical_id_chosen	bigint	Yes	Yes	Member ID chosen through manual or automated member matching Generated through expression:
member_canonical_id_chosen_at	date	Yes	No	Date when the member ID was selected Generated through expression:
member_canonical_id_chosen_by	bigint	Yes	No	Indicates who (manual or automated) chose the member ID Generated through expression:
individual_health_identifier	text	No	No	Unique Health Identifier for each individual in the United States (From NM109 code II) Generated through expression:
member_match_status	match_status_type	No	Yes	Indicates the matching state of the member Generated through expression: