provider_seen

A unique list of every provider we've ever seen

Primary Key:

• provider_seen_id

Checks:

• provider_seen_check - ((npi <> 0) OR (tax_id <> ''::text) OR (state_license_number <> ''::text) OR (provider_commercial_number <> ''::text) OR (location_number <> ''::text))
• provider_seen_location_number_check - (location_number = upper(location_number))
• provider_seen_name_first_check - (name_first = upper(name_first))
• provider_seen_name_last_or_organization_check - (name_last_or_organization = upper(name_last_or_organization))
• provider_seen_name_middle_check - (name_middle = upper(name_middle))
• provider_seen_name_prefix_check - (name_prefix = upper(name_prefix))
• provider_seen_name_suffix_check - (name_suffix = upper(name_suffix))
• provider_seen_provider_commercial_number_check - (provider_commercial_number = upper(provider_commercial_number))
• provider_seen_state_license_number_check - (state_license_number = upper(state_license_number))
• provider_seen_tax_id_check - (tax_id ~ '^[0-9]*$'::text)

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
• pay_to_address_id => address_seen.address_id
• phone_number_id => phone.phone_id
• work_phone_number_id => phone.phone_id

Indexes:

• provider_seen_address_id_idx - address_id
• provider_seen_alternate_phone_number_id_idx - alternate_phone_number_id
• provider_seen_beeper_number_id_idx - beeper_number_id
• provider_seen_cell_phone_number_id_idx - cell_phone_number_id
• provider_seen_email_address_id_idx - email_address_id
• provider_seen_fax_number_id_idx - fax_number_id
• provider_seen_home_phone_number_id_idx - home_phone_number_id
• provider_seen_location_number_idx - location_number
• provider_seen_name_first_idx - name_first
• provider_seen_name_last_or_organization_idx - name_last_or_organization
• provider_seen_npi_idx - npi
• provider_seen_pay_to_address_id_idx - pay_to_address_id
• provider_seen_phone_number_id_idx - phone_number_id
• provider_seen_provider_commercial_number_idx - provider_commercial_number
• provider_seen_state_license_number_idx - state_license_number
• provider_seen_tax_id_idx - tax_id
• provider_seen_uq (Unique) - npi, tax_id, state_license_number, provider_commercial_number, location_number, entity_type, name_last_or_organization, name_first, name_middle, name_prefix, name_suffix, gender_or_sex, address_id, pay_to_address_id, contact_name, 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
• provider_seen_work_phone_number_id_idx - work_phone_number_id

Column	Data Type	Nullable	Indexed	Description
provider_seen_id	bigint	No	Yes	-
address_id	bigint	Yes	Yes	Provider Address (From N3/N4)
pay_to_address_id	bigint	Yes	Yes	Pay to address (From Loop 2010AB)
npi	bigint	Yes	Yes	National Provider Identifier (From NM1 XX)
tax_id	text	No	Yes	Employer Identification Number (From REF EI or REF SY)
state_license_number	text	No	Yes	State License Number (From REF 0B)
provider_commercial_number	text	No	Yes	Provider Commercial Number (From REF G2)
location_number	text	No	Yes	Location Number (From REF LU)
entity_type	entity_type	Yes	Yes	Identifies whether row is a person or organization (From NM102)
name_last_or_organization	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)
gender_or_sex	gender_or_sex_type	Yes	Yes	May be either the preferred gender or the sex assigned at birth of the individual (non-EDI)
contact_name	text	No	Yes	Name of the contact (From PER02)
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)