member_seen

Contains unique member information from claims and eligibility files

Primary Key:

• member_seen_id

Checks:

• member_seen_check - ((member_identification_code <> ''::text) OR (member_ssn <> ''::text) OR (family_identification_code <> ''::text) OR ((name_last <> ''::text) AND (name_first <> ''::text) AND (date_of_birth IS NOT NULL)))
• member_seen_family_identification_code_check - (family_identification_code = upper(family_identification_code))
• member_seen_member_identification_code_check - (member_identification_code = upper(member_identification_code))
• member_seen_member_ssn_check - (member_ssn ~ '^[0-9]*$'::text)
• member_seen_name_first_check - (name_first = upper(name_first))
• member_seen_name_last_check - (name_last = upper(name_last))
• member_seen_name_middle_check - (name_middle = upper(name_middle))
• member_seen_name_prefix_check - (name_prefix = upper(name_prefix))
• member_seen_name_suffix_check - (name_suffix = upper(name_suffix))

Foreign Keys:

• address_id => address_seen.address_id
• alternate_phone_number_id => phone.phone_id
• beeper_number_id => phone.phone_id
• cell_phone_number_id => phone.phone_id
• email_address_id => email.email_id
• fax_number_id => fax.fax_id
• home_phone_number_id => phone.phone_id
• phone_number_id => phone.phone_id
• work_phone_number_id => phone.phone_id

Indexes:

• member_seen_address_id_idx - address_id
• member_seen_alternate_phone_number_id_idx - alternate_phone_number_id
• member_seen_beeper_number_id_idx - beeper_number_id
• member_seen_cell_phone_number_id_idx - cell_phone_number_id
• member_seen_date_of_birth_idx - date_of_birth
• member_seen_email_address_id_idx - email_address_id
• member_seen_family_identification_code_idx - family_identification_code
• member_seen_fax_number_id_idx - fax_number_id
• member_seen_home_phone_number_id_idx - home_phone_number_id
• member_seen_member_identification_code_idx - member_identification_code
• member_seen_member_ssn_idx - member_ssn
• member_seen_name_first_idx - name_first
• member_seen_name_last_idx - name_last
• member_seen_phone_number_id_idx - phone_number_id
• member_seen_uq (Unique) - member_ssn, member_identification_code, family_identification_code, person_code, address_id, name_last, name_first, name_middle, name_prefix, name_suffix, date_of_birth, gender_or_sex, alternate_phone_number_id, beeper_number_id, cell_phone_number_id, email_address_id, fax_number_id, home_phone_number_id, phone_number_id, work_phone_number_id, phone_extension
• member_seen_work_phone_number_id_idx - work_phone_number_id

Column	Data Type	Nullable	Indexed	Description
member_seen_id	bigint	No	Yes	-
address_id	bigint	Yes	Yes	Address (From N3, N4)
name_last	text	No	Yes	Last name or organization name (From NM103)
name_first	text	No	Yes	First / given name (From NM104)
name_middle	text	No	Yes	Middle name (From NM105)
name_prefix	text	No	Yes	Prefix to the name (From NM106)
name_suffix	text	No	Yes	Suffix to the name (From NM107)
date_of_birth	date	Yes	Yes	Date of birth (From DMG02)
gender_or_sex	gender_or_sex_type	No	Yes	May be either the preferred gender or the sex assigned at birth of the individual (From DMG03)
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])
family_identification_code	text	No	Yes	Some TPAs send a family identification code which can be used together with the person_code to identify each family member.
person_code	text	No	Yes	Some TPAs send a person code in order to differentiate between different family members when they would all have the same member_identification_id.
alternate_phone_number_id	bigint	Yes	Yes	Alternate telephone number ID (From PER03/5/7 AP)
beeper_number_id	bigint	Yes	Yes	Beeper number ID (From PER03/5/7 BN)
cell_phone_number_id	bigint	Yes	Yes	Cell phone number ID (From PER03/5/7 CP)
email_address_id	bigint	Yes	Yes	Electronic mail address ID (From PER03/5/7 EM)
fax_number_id	bigint	Yes	Yes	Facsimile number ID (From PER03/5/7 FX)
home_phone_number_id	bigint	Yes	Yes	Home phone number ID (From PER03/5/7 HP)
phone_number_id	bigint	Yes	Yes	Telephone number ID (From PER03/5/7 TE)
work_phone_number_id	bigint	Yes	Yes	Work telephone number ID (From PER03/5/7 WP)
phone_extension	text	No	Yes	Telephone extension (From PER03/5/7 EX)