# $Id$ # # Authors: # Jeff Buchbinder # # FreeMED Electronic Medical Record and Practice Management System # Copyright (C) 1999-2012 FreeMED Software Foundation # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; either version 2 of the License, or # (at your option) any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. DROP TABLE IF EXISTS npi; CREATE TABLE npi ( id CHAR (12) NOT NULL UNIQUE , entity_type_code ENUM ( '1', '2' ) NOT NULL , replacement_npi CHAR (12) , employer_identification_number VARCHAR (9) , provider_organization_name VARCHAR (200) , provider_last_name VARCHAR (100) , provider_first_name VARCHAR (100) , provider_middle_name VARCHAR (100) , provider_name_prefix_text VARCHAR (7) , provider_name_suffix_text VARCHAR (7) , provider_credential_text VARCHAR (100) , provider_other_organization_name VARCHAR (200) , provider_other_organization_name_type_code ENUM ( '1', '2', '3', '4', '5' ) , provider_other_last_name VARCHAR (100) , provider_other_first_name VARCHAR (100) , provider_other_middle_name VARCHAR (100) , provider_other_name_prefix_text VARCHAR (7) , provider_other_name_suffix_text VARCHAR (7) , provider_other_credential_text VARCHAR (100) , provider_other_last_name_type_code ENUM ( '1', '2', '3', '4', '5' ) , provider_first_line_business_mailing_address VARCHAR (200) , provider_second_line_business_mailing_address VARCHAR (200) , provider_business_mailing_address_city_name VARCHAR (100) , provider_business_mailing_address_state_name CHAR (2) , provider_business_mailing_address_postal_code CHAR (6) , provider_business_mailing_address_country_code CHAR(2) NOT NULL DEFAULT 'US' , provider_business_mailing_address_telephone_number VARCHAR (30) , provider_business_mailing_address_fax_number VARCHAR (30) , provider_first_line_business_practice_location_address VARCHAR (200) , provider_second_line_business_practice_location_address VARCHAR (200) , provider_business_practice_location_address_city_name VARCHAR (100) , provider_business_practice_location_address_state_name CHAR (2) , provider_business_practice_location_address_postal_code CHAR (10) , provider_business_practice_location_address_country_code CHAR(2) NOT NULL DEFAULT 'US' , provider_business_practice_location_address_telephone_number VARCHAR (30) , provider_business_practice_location_address_fax_number VARCHAR (30) , provider_enumeration_date CHAR (10) , last_update_date CHAR (10) , npi_deactivation_reason_code ENUM ('DT', 'DB', 'FR', 'OT' ) , npi_deactivation_date CHAR (10) , npi_reactivation_date CHAR (10) , provider_gender_code ENUM ( 'M', 'F' ) , authorized_official_last_name VARCHAR (100) , authorized_official_first_name VARCHAR (100) , authorized_official_middle_name VARCHAR (100) , authorized_official_title_or_position VARCHAR (100) , authorized_official_telephone_number VARCHAR (30) , healthcare_provider_taxonomy_code_1 CHAR (10) , provider_license_number_1 CHAR (10) , provider_license_number_state_code_1 CHAR(2) , healthcare_provider_primary_taxonomy_switch_1 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X' , healthcare_provider_taxonomy_code_2 CHAR (10) , provider_license_number_2 CHAR (10) , provider_license_number_state_code_2 CHAR(2) , healthcare_provider_primary_taxonomy_switch_2 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X' , healthcare_provider_taxonomy_code_3 CHAR (10) , provider_license_number_3 CHAR (10) , provider_license_number_state_code_3 CHAR(2) , healthcare_provider_primary_taxonomy_switch_3 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X' , healthcare_provider_taxonomy_code_4 CHAR (10) , provider_license_number_4 CHAR (10) , provider_license_number_state_code_4 CHAR(2) , healthcare_provider_primary_taxonomy_switch_4 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X' , healthcare_provider_taxonomy_code_5 CHAR (10) , provider_license_number_5 CHAR (10) , provider_license_number_state_code_5 CHAR(2) , healthcare_provider_primary_taxonomy_switch_5 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X' , healthcare_provider_taxonomy_code_6 CHAR (10) , provider_license_number_6 CHAR (10) , provider_license_number_state_code_6 CHAR(2) , healthcare_provider_primary_taxonomy_switch_6 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X' , healthcare_provider_taxonomy_code_7 CHAR (10) , provider_license_number_7 CHAR (10) , provider_license_number_state_code_7 CHAR(2) , healthcare_provider_primary_taxonomy_switch_7 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X' , healthcare_provider_taxonomy_code_8 CHAR (10) , provider_license_number_8 CHAR (10) , provider_license_number_state_code_8 CHAR(2) , healthcare_provider_primary_taxonomy_switch_8 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X' , healthcare_provider_taxonomy_code_9 CHAR (10) , provider_license_number_9 CHAR (10) , provider_license_number_state_code_9 CHAR(2) , healthcare_provider_primary_taxonomy_switch_9 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X' , healthcare_provider_taxonomy_code_10 CHAR (10) , provider_license_number_10 CHAR (10) , provider_license_number_state_code_10 CHAR(2) , healthcare_provider_primary_taxonomy_switch_10 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X' , healthcare_provider_taxonomy_code_11 CHAR (10) , provider_license_number_11 CHAR (10) , provider_license_number_state_code_11 CHAR(2) , healthcare_provider_primary_taxonomy_switch_11 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X' , healthcare_provider_taxonomy_code_12 CHAR (10) , provider_license_number_12 CHAR (10) , provider_license_number_state_code_12 CHAR(2) , healthcare_provider_primary_taxonomy_switch_12 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X' , healthcare_provider_taxonomy_code_13 CHAR (10) , provider_license_number_13 CHAR (10) , provider_license_number_state_code_13 CHAR(2) , healthcare_provider_primary_taxonomy_switch_13 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X' , healthcare_provider_taxonomy_code_14 CHAR (10) , provider_license_number_14 CHAR (10) , provider_license_number_state_code_14 CHAR(2) , healthcare_provider_primary_taxonomy_switch_14 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X' , healthcare_provider_taxonomy_code_15 CHAR (10) , provider_license_number_15 CHAR (10) , provider_license_number_state_code_15 CHAR(2) , healthcare_provider_primary_taxonomy_switch_15 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X' , other_provider_identifier_1 VARCHAR (30) , other_provider_identifier_type_code_1 CHAR (2) , other_provider_identifier_state_1 CHAR (2) , other_provider_identifier_issuer_1 CHAR (2) , other_provider_identifier_2 VARCHAR (30) , other_provider_identifier_type_code_2 CHAR (2) , other_provider_identifier_state_2 CHAR (2) , other_provider_identifier_issuer_2 CHAR (2) , other_provider_identifier_3 VARCHAR (30) , other_provider_identifier_type_code_3 CHAR (2) , other_provider_identifier_state_3 CHAR (2) , other_provider_identifier_issuer_3 CHAR (2) , other_provider_identifier_4 VARCHAR (30) , other_provider_identifier_type_code_4 CHAR (2) , other_provider_identifier_state_4 CHAR (2) , other_provider_identifier_issuer_4 CHAR (2) , other_provider_identifier_5 VARCHAR (30) , other_provider_identifier_type_code_5 CHAR (2) , other_provider_identifier_state_5 CHAR (2) , other_provider_identifier_issuer_5 CHAR (2) , other_provider_identifier_6 VARCHAR (30) , other_provider_identifier_type_code_6 CHAR (2) , other_provider_identifier_state_6 CHAR (2) , other_provider_identifier_issuer_6 CHAR (2) , other_provider_identifier_7 VARCHAR (30) , other_provider_identifier_type_code_7 CHAR (2) , other_provider_identifier_state_7 CHAR (2) , other_provider_identifier_issuer_7 CHAR (2) , other_provider_identifier_8 VARCHAR (30) , other_provider_identifier_type_code_8 CHAR (2) , other_provider_identifier_state_8 CHAR (2) , other_provider_identifier_issuer_8 CHAR (2) , other_provider_identifier_9 VARCHAR (30) , other_provider_identifier_type_code_9 CHAR (2) , other_provider_identifier_state_9 CHAR (2) , other_provider_identifier_issuer_9 CHAR (2) , other_provider_identifier_10 VARCHAR (30) , other_provider_identifier_type_code_10 CHAR (2) , other_provider_identifier_state_10 CHAR (2) , other_provider_identifier_issuer_10 CHAR (2) , other_provider_identifier_11 VARCHAR (30) , other_provider_identifier_type_code_11 CHAR (2) , other_provider_identifier_state_11 CHAR (2) , other_provider_identifier_issuer_11 CHAR (2) , other_provider_identifier_12 VARCHAR (30) , other_provider_identifier_type_code_12 CHAR (2) , other_provider_identifier_state_12 CHAR (2) , other_provider_identifier_issuer_12 CHAR (2) , other_provider_identifier_13 VARCHAR (30) , other_provider_identifier_type_code_13 CHAR (2) , other_provider_identifier_state_13 CHAR (2) , other_provider_identifier_issuer_13 CHAR (2) , other_provider_identifier_14 VARCHAR (30) , other_provider_identifier_type_code_14 CHAR (2) , other_provider_identifier_state_14 CHAR (2) , other_provider_identifier_issuer_14 CHAR (2) , other_provider_identifier_15 VARCHAR (30) , other_provider_identifier_type_code_15 CHAR (2) , other_provider_identifier_state_15 CHAR (2) , other_provider_identifier_issuer_15 CHAR (2) , other_provider_identifier_16 VARCHAR (30) , other_provider_identifier_type_code_16 CHAR (2) , other_provider_identifier_state_16 CHAR (2) , other_provider_identifier_issuer_16 CHAR (2) , other_provider_identifier_17 VARCHAR (30) , other_provider_identifier_type_code_17 CHAR (2) , other_provider_identifier_state_17 CHAR (2) , other_provider_identifier_issuer_17 CHAR (2) , other_provider_identifier_18 VARCHAR (30) , other_provider_identifier_type_code_18 CHAR (2) , other_provider_identifier_state_18 CHAR (2) , other_provider_identifier_issuer_18 CHAR (2) , other_provider_identifier_19 VARCHAR (30) , other_provider_identifier_type_code_19 CHAR (2) , other_provider_identifier_state_19 CHAR (2) , other_provider_identifier_issuer_19 CHAR (2) , other_provider_identifier_20 VARCHAR (30) , other_provider_identifier_type_code_20 CHAR (2) , other_provider_identifier_state_20 CHAR (2) , other_provider_identifier_issuer_20 CHAR (2) , other_provider_identifier_21 VARCHAR (30) , other_provider_identifier_type_code_21 CHAR (2) , other_provider_identifier_state_21 CHAR (2) , other_provider_identifier_issuer_21 CHAR (2) , other_provider_identifier_22 VARCHAR (30) , other_provider_identifier_type_code_22 CHAR (2) , other_provider_identifier_state_22 CHAR (2) , other_provider_identifier_issuer_22 CHAR (2) , other_provider_identifier_23 VARCHAR (30) , other_provider_identifier_type_code_23 CHAR (2) , other_provider_identifier_state_23 CHAR (2) , other_provider_identifier_issuer_23 CHAR (2) , other_provider_identifier_24 VARCHAR (30) , other_provider_identifier_type_code_24 CHAR (2) , other_provider_identifier_state_24 CHAR (2) , other_provider_identifier_issuer_24 CHAR (2) , other_provider_identifier_25 VARCHAR (30) , other_provider_identifier_type_code_25 CHAR (2) , other_provider_identifier_state_25 CHAR (2) , other_provider_identifier_issuer_25 CHAR (2) , other_provider_identifier_26 VARCHAR (30) , other_provider_identifier_type_code_26 CHAR (2) , other_provider_identifier_state_26 CHAR (2) , other_provider_identifier_issuer_26 CHAR (2) , other_provider_identifier_27 VARCHAR (30) , other_provider_identifier_type_code_27 CHAR (2) , other_provider_identifier_state_27 CHAR (2) , other_provider_identifier_issuer_27 CHAR (2) , other_provider_identifier_28 VARCHAR (30) , other_provider_identifier_type_code_28 CHAR (2) , other_provider_identifier_state_28 CHAR (2) , other_provider_identifier_issuer_28 CHAR (2) , other_provider_identifier_29 VARCHAR (30) , other_provider_identifier_type_code_29 CHAR (2) , other_provider_identifier_state_29 CHAR (2) , other_provider_identifier_issuer_29 CHAR (2) , other_provider_identifier_30 VARCHAR (30) , other_provider_identifier_type_code_30 CHAR (2) , other_provider_identifier_state_30 CHAR (2) , other_provider_identifier_issuer_30 CHAR (2) , other_provider_identifier_31 VARCHAR (30) , other_provider_identifier_type_code_31 CHAR (2) , other_provider_identifier_state_31 CHAR (2) , other_provider_identifier_issuer_31 CHAR (2) , other_provider_identifier_32 VARCHAR (30) , other_provider_identifier_type_code_32 CHAR (2) , other_provider_identifier_state_32 CHAR (2) , other_provider_identifier_issuer_32 CHAR (2) , other_provider_identifier_33 VARCHAR (30) , other_provider_identifier_type_code_33 CHAR (2) , other_provider_identifier_state_33 CHAR (2) , other_provider_identifier_issuer_33 CHAR (2) , other_provider_identifier_34 VARCHAR (30) , other_provider_identifier_type_code_34 CHAR (2) , other_provider_identifier_state_34 CHAR (2) , other_provider_identifier_issuer_34 CHAR (2) , other_provider_identifier_35 VARCHAR (30) , other_provider_identifier_type_code_35 CHAR (2) , other_provider_identifier_state_35 CHAR (2) , other_provider_identifier_issuer_35 CHAR (2) , other_provider_identifier_36 VARCHAR (30) , other_provider_identifier_type_code_36 CHAR (2) , other_provider_identifier_state_36 CHAR (2) , other_provider_identifier_issuer_36 CHAR (2) , other_provider_identifier_37 VARCHAR (30) , other_provider_identifier_type_code_37 CHAR (2) , other_provider_identifier_state_37 CHAR (2) , other_provider_identifier_issuer_37 CHAR (2) , other_provider_identifier_38 VARCHAR (30) , other_provider_identifier_type_code_38 CHAR (2) , other_provider_identifier_state_38 CHAR (2) , other_provider_identifier_issuer_38 CHAR (2) , other_provider_identifier_39 VARCHAR (30) , other_provider_identifier_type_code_39 CHAR (2) , other_provider_identifier_state_39 CHAR (2) , other_provider_identifier_issuer_39 CHAR (2) , other_provider_identifier_40 VARCHAR (30) , other_provider_identifier_type_code_40 CHAR (2) , other_provider_identifier_state_40 CHAR (2) , other_provider_identifier_issuer_40 CHAR (2) , other_provider_identifier_41 VARCHAR (30) , other_provider_identifier_type_code_41 CHAR (2) , other_provider_identifier_state_41 CHAR (2) , other_provider_identifier_issuer_41 CHAR (2) , other_provider_identifier_42 VARCHAR (30) , other_provider_identifier_type_code_42 CHAR (2) , other_provider_identifier_state_42 CHAR (2) , other_provider_identifier_issuer_42 CHAR (2) , other_provider_identifier_43 VARCHAR (30) , other_provider_identifier_type_code_43 CHAR (2) , other_provider_identifier_state_43 CHAR (2) , other_provider_identifier_issuer_43 CHAR (2) , other_provider_identifier_44 VARCHAR (30) , other_provider_identifier_type_code_44 CHAR (2) , other_provider_identifier_state_44 CHAR (2) , other_provider_identifier_issuer_44 CHAR (2) , other_provider_identifier_45 VARCHAR (30) , other_provider_identifier_type_code_45 CHAR (2) , other_provider_identifier_state_45 CHAR (2) , other_provider_identifier_issuer_45 CHAR (2) , other_provider_identifier_46 VARCHAR (30) , other_provider_identifier_type_code_46 CHAR (2) , other_provider_identifier_state_46 CHAR (2) , other_provider_identifier_issuer_46 CHAR (2) , other_provider_identifier_47 VARCHAR (30) , other_provider_identifier_type_code_47 CHAR (2) , other_provider_identifier_state_47 CHAR (2) , other_provider_identifier_issuer_47 CHAR (2) , other_provider_identifier_48 VARCHAR (30) , other_provider_identifier_type_code_48 CHAR (2) , other_provider_identifier_state_48 CHAR (2) , other_provider_identifier_issuer_48 CHAR (2) , other_provider_identifier_49 VARCHAR (30) , other_provider_identifier_type_code_49 CHAR (2) , other_provider_identifier_state_49 CHAR (2) , other_provider_identifier_issuer_49 CHAR (2) , other_provider_identifier_50 VARCHAR (30) , other_provider_identifier_type_code_50 CHAR (2) , other_provider_identifier_state_50 CHAR (2) , other_provider_identifier_issuer_50 CHAR (2) , is_sole_proprietor ENUM ('X', 'Y', 'N' ) NOT NULL DEFAULT 'X' , is_organization_subpart ENUM ('X', 'Y', 'N' ) NOT NULL DEFAULT 'X' , parent_organization_lbn VARCHAR (100) , parent_organization_tin CHAR (11) , authorized_official_name_prefix_text VARCHAR (7) , authorized_official_name_suffix_text VARCHAR (7) , authorized_official_credential_text VARCHAR (200) , PRIMARY KEY ( id ) , INDEX ( provider_organization_name ) , INDEX ( provider_last_name, provider_first_name ) ); LOAD DATA LOCAL INFILE "data/source/npi/npi.csv" INTO TABLE npi FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;