provider_seen

A unique list of every way we've ever seen a provider identified. Table includes values for UB04 FL 01, 02, 05, 56, 57, 76, 77, 78, and 79 / HCFA items 17, 24j, 25, 32, 33.

Primary Key:

• provider_seen_id

Checks:

• provider_seen_check - (((npi <> 0) AND (npi IS NOT NULL)) OR (tax_identifier <> ''::text) OR (address_id IS NOT NULL) OR (name_last_or_organization <> ''::text) OR (state_license_number <> ''::text) OR (unique_provider_identification_number <> ''::text) OR (blue_cross_identifier <> ''::text) OR (blue_shield_identifier <> ''::text) OR (medicare_identifier <> ''::text) OR (medicaid_identifier <> ''::text) OR (ncpdp_identifier <> ''::text) OR (tricare_identifier <> ''::text) OR (hin <> ''::text) OR (dea_number <> ''::text) OR (state_issued_identifier <> ''::text) OR (plan_specific_identifier <> ''::text) OR (hcidea_identifier <> ''::text) OR (cmea_identifier <> ''::text) OR (other_identifier <> ''::text))
• provider_seen_check1 - (((relationship_to_npi = 'Corrected'::claims.provider_relationship_type) AND (corrected_npi IS NOT NULL)) OR ((relationship_to_npi <> 'Corrected'::claims.provider_relationship_type) AND (corrected_npi IS NULL)))
• provider_seen_check2 - ((tax_identifier <> ''::text) OR ((tax_identifier = ''::text) AND (tax_identifier_type IS NULL)))
• 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_identifier_check - (tax_identifier ~ '^[0-9]*$'::text)

Foreign Keys:

• address_id => address_seen.address_id
• email_address_id => email.email_id
• fax_number_id => fax.fax_id
• phone_number_id => phone.phone_id

Indexes:

• provider_seen_address_id_idx - address_id
• provider_seen_email_address_id_idx - email_address_id
• provider_seen_fax_number_id_idx - fax_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_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_identifier_idx - tax_identifier
• provider_seen_uq (Unique) - npi, tax_identifier, state_license_number, unique_provider_identification_number, provider_commercial_number, location_number, blue_cross_identifier, blue_shield_identifier, medicare_identifier, medicaid_identifier, ncpdp_identifier, tricare_identifier, hin, dea_number, state_issued_identifier, plan_specific_identifier, hcidea_identifier, cmea_identifier, foreign_prescriber_identifier, other_identifier, name_last_or_organization, name_first, name_middle, name_prefix, name_suffix, address_id, contact_name, email_address_id, fax_number_id, phone_number_id, phone_extension

Column	Data Type	Nullable	Indexed	Description
provider_seen_id	bigint	No	Yes	Snowflake ID for the provider seen record.
address_id	bigint	Yes	Yes	Provider Address (From N3/N4 / UB04 FL01 when provider_type is 'Billing Provider', FL02 when 'Pay-to Provider' / HCFA item 32 when 'Service Location', item 33 when 'Billing Provider' / NCPDP 365-2K [street address], 366-2M [city], 367-2N [state], 368-2P [ZIP] when 'Prescriber'; 386-3U [street address], 388-5J [city], 387-3V [state], 389-6D [ZIP] when 'Facility').
npi	bigint	Yes	Yes	National Provider Identifier (From NM1 XX / UB04 FL56 when provider_type is 'Billing Provider' or 'Pay-to Provider', FL76 when 'Attending Physician', FL77 when 'Operating Physician', FL78/FL79 when 'Referring Provider', 'Other Operating Physician', or 'Rendering Provider' / HCFA item 17b when 'Referring Provider', 'Ordering Physician', or 'Supervising Physician', item 24j when 'Rendering Provider', item 32a when 'Service Location', item 33a when 'Billing Provider' / NCPDP 201-B1 when 'Billing Provider', 444-E9 when 'Pharmacy', 411-DB when 'Prescriber', 421-DL when 'Primary Care Provider', 336-8C when 'Facility').
tax_identifier	text	No	Yes	EIN or SSN (From REF EI or REF SY/ UB04 FL05 when provider_type is 'Billing Provider' / HCFA item 25 when provider_type is 'Billing Provider' / NM1 34 or NM1 FI from X12 EDI 834 / NCPDP 201-B1 when 'Billing Provider', 411-DB when 'Prescriber', 421-DL when 'Primary Care Provider', 444-E9 when 'Pharmacy')
state_license_number	text	No	Yes	State License Number (From REF 0B / UB04 FL57 when provider_type is 'Billing Provider', FL76 when 'Attending Physician', FL77 when 'Operating Physician', FL78/FL79 when 'Referring Provider', 'Other Operating Physician', or 'Rendering Provider' / HCFA item 17a when 'Referring Provider', 'Ordering Physician', or 'Supervising Physician', item 24j when 'Rendering Provider', 32b when 'Service Location', 33b when 'Billing Provider' / NCPDP 201-B1 when 'Billing Provider', 444-E9 when 'Pharmacy', 411-DB when 'Prescriber', 421-DL when 'Primary Care Provider')
provider_commercial_number	text	No	Yes	Provider Commercial Number (From REF G2 / UB04 FL57 when provider_type is 'Billing Provider', FL76 when 'Attending Physician', FL77 when 'Operating Physician', FL78/FL79 when 'Referring Provider', 'Other Operating Physician', or 'Rendering Provider' / HCFA item 17a when 'Referring Provider', 'Ordering Physician', or 'Supervising Physician', item 24j when 'Rendering Provider', 32b when 'Service Location', 33b when 'Billing Provider')
location_number	text	No	Yes	Location Number (From REF LU / UB04 FL57 when provider_type is 'Billing Provider', FL76 when 'Attending Physician', FL77 when 'Operating Physician', FL78/FL79 when 'Referring Provider', 'Other Operating Physician', or 'Rendering Provider' / HCFA item 17a when 'Referring Provider', 'Ordering Physician', or 'Supervising Physician', item 24j when 'Rendering Provider', 32b when 'Service Location', 33b when 'Billing Provider')
entity_type	entity_type	Yes	No	Identifies whether row is a person or organization (From NM102)
name_last_or_organization	text	No	Yes	Last name or organization name (From NM103 / UB04 FL01 when provider_type is 'Billing Provider', FL02 when 'Pay-to Provider', FL76 when 'Attending Physician', FL77 when 'Operating Physician', FL78/FL79 when 'Referring Provider', 'Other Operating Physician', or 'Rendering Provider' / HCFA item 17 when 'Referring Provider', 'Ordering Physician', or 'Supervising Physician', item 32 when 'Service Location', item 33 when 'Billing Provider' / NCPDP 444-E9 when 'Pharmacy', 427-DR when 'Prescriber', 470-4E when 'Primary Care Provider', 385-3Q when 'Facility')
name_first	text	No	Yes	First / given name (From NM104 / UB04 FL76 when provider_type is 'Attending Physician', FL77 when 'Operating Physician', FL78/FL79 when 'Referring Provider', 'Other Operating Physician', or 'Rendering Provider' / HCFA item 17 when 'Referring Provider', 'Ordering Physician', or 'Supervising Physician', item 32 when 'Service Location', item 33 when 'Billing Provider' / NCPDP 364-2J when 'Prescriber')
name_middle	text	No	Yes	Middle name (From NM105 / HCFA item 17 when provider_type is 'Referring Provider', 'Ordering Physician', or 'Supervising Physician', item 32 when 'Service Location', item 33 when 'Billing Provider')
name_prefix	text	No	Yes	Prefix to the name (From NM106)
name_suffix	text	No	Yes	Suffix to the name (From NM107)
contact_name	text	No	Yes	Name of the contact (From PER02)
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)
phone_number_id	bigint	Yes	Yes	Telephone number ID (From PER03/5/7 TE / UB04 FL01 when provider_type is 'Billing Provider' / HCFA item 33 when 'Billing Provider' / NCPDP 498-PM when 'Prescriber')
phone_extension	text	No	Yes	Telephone extension (From PER03/5/7 EX)
unique_provider_identification_number	text	No	Yes	Unique Provider Identification Number (UPIN) (From REF 1G / UB04 FL57 when provider_type is 'Billing Provider', FL76 when 'Attending Physician', FL77 when 'Operating Physician', FL78/FL79 when 'Referring Provider', 'Other Operating Physician', or 'Rendering Provider' / HCFA item 17a when 'Referring Provider', 'Ordering Physician', or 'Supervising Physician', item 24j when 'Rendering Provider', 33b when 'Billing Provider' / NCPDP 201-B1 when 'Billing Provider', 411-DB when 'Prescriber', 421-DL when 'Primary Care Provider')
primary_id_qualifier	identification_type	Yes	No	Data type of the primary identification code (From NM108)
relationship_to_npi	provider_relationship_type	Yes	No	Relationship of the data in this row to the NPI (non-EDI)
corrected_npi	bigint	Yes	No	The corrected NPI that matches with the other data in the row as identified through provider matching (non-EDI)
blue_cross_identifier	text	No	Yes	Identifier assigned by a Blue Cross health plan (From NCPDP 201-B1 when 'Billing Provider', 411-DB when 'Prescriber', 421-DL when 'Primary Care Provider')
blue_shield_identifier	text	No	Yes	Identifier assigned by a Blue Shield health plan (From NCPDP 201-B1 when 'Billing Provider', 411-DB when 'Prescriber', 421-DL when 'Primary Care Provider')
medicare_identifier	text	No	Yes	Identifier assigned by a carrier/intermediary which administers Medicare (From NCPDP 201-B1 when 'Billing Provider', 411-DB when 'Prescriber', 421-DL when 'Primary Care Provider')
medicaid_identifier	text	No	Yes	Identifier assigned by a state Medicaid agency (From NCPDP 201-B1 when 'Billing Provider', 411-DB when 'Prescriber', 421-DL when 'Primary Care Provider')
ncpdp_identifier	text	No	Yes	NCPDP Provider ID Number (From NCPDP 201-B1 when 'Billing Provider', 411-DB when 'Prescriber', 421-DL when 'Primary Care Provider')
tricare_identifier	text	No	Yes	TRICARE provider identifier (From NCPDP 201-B1 when 'Billing Provider', 411-DB when 'Prescriber', 421-DL when 'Primary Care Provider')
hin	text	No	Yes	Health Industry Number (From NCPDP 201-B1 when 'Billing Provider', 444-E9 when 'Pharmacy', 411-DB when 'Prescriber', 421-DL when 'Primary Care Provider')
dea_number	text	No	Yes	Identifier assigned by the Drug Enforcement Administration to providers that deal with controlled pharmaceuticals (From NCPDP 201-B1 when 'Billing Provider', 444-E9 when 'Pharmacy', 411-DB when 'Prescriber', 421-DL when 'Primary Care Provider')
state_issued_identifier	text	No	Yes	Identifier issued by a state program/organization other than Medicaid (From NCPDP 201-B1 when 'Billing Provider', 444-E9 when 'Pharmacy', 411-DB when 'Prescriber', 421-DL when 'Primary Care Provider')
plan_specific_identifier	text	No	Yes	Proprietary identifier assigned by a plan (From NCPDP 201-B1 when 'Billing Provider', 411-DB when 'Prescriber', 421-DL when 'Primary Care Provider')
hcidea_identifier	text	No	Yes	Identifier assigned by NCPDP to identify authorized prescribers of drugs (From NCPDP 201-B1 when 'Billing Provider', 411-DB when 'Prescriber', 421-DL when 'Primary Care Provider')
cmea_identifier	text	No	Yes	Combat Methamphetamine Epidemic Act (CMEA) Certificate ID (From NCPDP 201-B1 when 'Billing Provider')
foreign_prescriber_identifier	text	No	Yes	Identifies a prescriber practicing outside of the United States (From NCPDP 411-DB when 'Prescriber')
other_identifier	text	No	Yes	Other provider identifier (From NCPDP 201-B1 when 'Billing Provider', 444-E9 when 'Pharmacy', 411-DB when 'Prescriber', 421-DL when 'Primary Care Provider', 336-8C when 'Facility')
tax_identifier_type	tax_identifier_type	Yes	No	Identifies whether tax_identifier is an EIN or SSN