claim_member

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

Primary Key:

• claim_member_id

Checks:

• claim_member_alternate_member_identification_code_check - (alternate_member_identification_code = upper(alternate_member_identification_code))
• 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_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_by_idx - member_canonical_id_chosen_by
• 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 / NCPDP 306-C6) Generated through expression:
group_or_policy_number	text	No	Yes	Subscriber group or policy number (From SBR03 / UB04 FL62 / HCFA item 11 / NCPDP 301-C1) 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 / NCPDP 302-C2 for subscriber, 332-CY for patient) 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	Yes	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:
person_code	text	No	No	Used to identify a specific person within a family (from NCPDP 303-C3) Generated through expression:
rx_patient_identifier_qualifier	rx_patient_identifier_type	Yes	No	Specifies the type of ID stored in member_identification_code when it is a patient identifier (from NCPDP 331-CX) Generated through expression:
alternate_member_identification_code	text	No	No	Alternate member identifier (non-EDI) Generated through expression:
home_plan_identifier	text	No	No	Identifier for the Blue Cross or Blue Shield plan which designates the member's assigned coverage location (from NCPDP 314-CE) Generated through expression:
plan_identifier	text	No	No	Identifies parameters or criteria used to adjudicate a claim, or TPA's adjudicated plan ID for medical claims (from NCPDP 524-FO) Generated through expression:
rx_eligibility_clarification	rx_eligibility_clarification_type	Yes	No	Eligibility exception or clarification being applied to the claim (from NCPDP 309-C9) Generated through expression:
medigap_identifier	text	No	No	ID assigned by the Medigap insurer (from NCPDP 359-2A) Generated through expression:
medicaid_coverage_state_or_province	text	No	No	The state or province where Medicaid coverage exists (from NCPDP 360-2B) Generated through expression:
medicaid_identifier	text	No	No	Member identifier assigned by the Medicaid agency (from NCPDP 115-N5) Generated through expression:
rx_employer_identifier	text	No	No	Employer identifier (from NCPDP 333-CZ) Generated through expression:
rx_patient_residence	rx_patient_residence_type	Yes	No	Patient's residential or care environment (from NCPDP 384-4X) Generated through expression:
rx_adjudicated_member_identification_code	text	No	No	Member identifier assigned to the subscriber as determined during adjudication (from NCPDP 302-C2) Generated through expression:
rx_network_reimbursement_identifier	text	No	No	Processor-defined network identifier, used to calculate reimbursement to the pharmacy (from NCPDP 545-2F) Generated through expression: