DROP TABLE ERX.PHARMACY CASCADE CONSTRAINTS ;

CREATE TABLE ERX.PHARMACY
  (
    PHARMACY_ID                  NUMBER NOT NULL ,
    VA_STATION_ID                VARCHAR2 (10) NOT NULL ,
    VISN						 NUMBER NOT NULL,
    DIVISION_NAME				 VARCHAR2 (35) NOT NULL,
    SERVICE_LEVEL                VARCHAR2(50) NOT NULL ,
    ACTIVE_START_TIME            DATE NOT NULL ,
    ACTIVE_END_TIME              DATE NOT NULL ,
    SPECIALTY_ID                 NUMBER NOT NULL ,
    NCPDP_ID                     VARCHAR2(7) NOT NULL ,
    NPI                          NUMBER ,
    FILE_ID                      VARCHAR2 (35) ,
    STATE_LICENSE_NUMBER         VARCHAR2 (35) ,
    MEDICARE_NUMBER              VARCHAR2 (35) ,
    MEDICAID_NUMBER              VARCHAR2 (35) ,
    PPO_NUMBER                   VARCHAR2 (35) ,
    PAYER_ID                     VARCHAR2 (35) ,
    BIN_LOCATION_NUMBER          VARCHAR2 (35) ,
    DEA_NUMBER                   VARCHAR2 (35) ,
    HIN                          VARCHAR2 (35) ,
    SECONDARY_COVERAGE           VARCHAR2 (35) ,
    NAIC_CODE                    VARCHAR2 (35) ,
    PROMOTION_NUMBER             VARCHAR2 (35) ,
    STORE_NAME                   VARCHAR2 (35) NOT NULL ,
    STORE_NUMBER                 VARCHAR2 (35) ,
    PHARMACIST_LAST_NAME         VARCHAR2 (35) ,
    PHARMACIST_FIRST_NAME        VARCHAR2 (35) ,
    PHARMACIST_MIDDLE_NAME       VARCHAR2 (35) ,
    PHARMACIST_SUFFIX            VARCHAR2 (35) ,
    PHARMACIST_PREFIX            VARCHAR2 (35) ,
    PHARMACIST_AGENT_LAST_NAME   VARCHAR2 (35) ,
    PHARMACIST_AGENT_FIRST_NAME  VARCHAR2 (35) ,
    PHARMACIST_AGENT_MIDDLE_NAME VARCHAR2 (35) ,
    PHARMACIST_AGENT_SUFFIX      VARCHAR2 (35) ,
    PHARMACIST_AGENT_PREFIX      VARCHAR2 (35) ,
    PHARMACY_ADDRESS_LINE_1      VARCHAR2 (35) NOT NULL ,
    PHARMACY_ADDRESS_LINE_2      VARCHAR2 (35) ,
    PHARMACY_CITY                VARCHAR2 (35) NOT NULL ,
    PHARMACY_STATE               VARCHAR2 (2) NOT NULL ,
    PHARMACY_ZIPCODE             VARCHAR2 (10) NOT NULL ,
    TWENTY_FOUR_HOUR_FLAG        VARCHAR2 (35) ,
    CROSS_STREET                 VARCHAR2 (35) ,
    EMAIL                        VARCHAR2 (35) ,
    PHARMACY_PHONE_NUMBER        VARCHAR2 (25) NOT NULL ,
    PHARMACY_FAX_NUMBER        	 VARCHAR2 (25) NOT NULL ,
    INBOUND_ERX_ENABLED          NUMBER NOT NULL
	CONSTRAINT check_inbound_erx_enabled CHECK (inbound_erx_enabled IN (0, 1))	,
    UPDATED_DATE                 DATE ,
    CREATED_DATE                 DATE ,
    CREATED_BY                   VARCHAR2(20) ,
    UPDATED_BY                   VARCHAR2(20)
  )
  LOGGING ;
COMMENT ON COLUMN ERX.PHARMACY.VA_STATION_ID
IS
  'VA STATION ID' ;
CREATE UNIQUE INDEX ERX.PHARMACY_PK ON ERX.PHARMACY
  (
    PHARMACY_ID ASC
  )
  LOGGING ;
  
  CREATE UNIQUE INDEX ERX.NCPDP_ID_UK ON ERX.PHARMACY
  (
    NCPDP_ID ASC
  )
  LOGGING ;
  
   CREATE UNIQUE INDEX ERX.NPI_UK ON ERX.PHARMACY
  (
    NPI ASC
  )
  LOGGING ;
  
   CREATE UNIQUE INDEX ERX.DIVISION_NAME_UK ON ERX.PHARMACY
  (
    DIVISION_NAME ASC
  )
  LOGGING ;
