DROP VIEW EPL_VADF_VALUES_VIEW;

DROP TABLE EPL_NDF_SYNCH_QUEUE;
DROP TABLE EPL_REDUCED_COPAY;
DROP TABLE EPL_NATIONAL_SETTINGS;
DROP TABLE EPL_FDB_GENERIC_NAME;
DROP TABLE EPL_FDB_DOSAGE_FORM;
DROP TABLE EPL_FDB_DRUG_UNITS;
DROP TABLE EPL_FDB_DRUG_INGREDIENT;
DROP TABLE EPL_FDB_DRUG_CLASS;
DROP TABLE EPL_FDB_PRODUCT;
DROP TABLE EPL_FDB_NDC;
DROP TABLE EPL_FDB_ADD;
DROP TABLE EPL_FDB_UPDATE;
DROP TABLE EPL_FDB_AUTO_ADD;
DROP TABLE EPL_FDB_AUTO_UPDATE;
DROP TABLE EPL_FDB_GCNSEQNO_PEM;
DROP TABLE EPL_FDB_MONOGRAPH_PEM;
DROP TABLE EPL_MIGRATION_ERROR;
DROP TABLE EPL_MIGRATION_FILE;
DROP TABLE EPL_MIGRATION_CONTROL;
DROP TABLE EPL_OI_ADMIN_SCHED_ASSOCS;
DROP TABLE EPL_MED_INSTRUCT_WARDS;
DROP TABLE EPL_MEDICATION_INSTRUCTIONS;
DROP TABLE EPL_OI_MED_ROUTE_ASSOCS;
DROP TABLE EPL_DF_MED_RT_DF_ASSOCS;
DROP TABLE EPL_PACKAGE_USE_ASSOCS;
DROP TABLE EPL_LOCAL_MED_ROUTES;
DROP TABLE EPL_LOCAL_POSSIBLE_DOSAGES;
DROP TABLE EPL_SEQ_NUMS;
DROP TABLE EPL_PARTIAL_SAVE_MGT;
DROP TABLE EPL_VADF_EDITABLE_PROPERTIES;
DROP TABLE EPL_MULTI_TEXT;
DROP TABLE EPL_VADF_NONLIST_VALUES;
DROP TABLE EPL_VADF_ASSOC_VALUES;
DROP TABLE EPL_VADF_OWNERS;
DROP TABLE EPL_REQUEST_DETAILS;
DROP TABLE EPL_REQUESTS;
DROP TABLE EPL_NDCS;
DROP TABLE EPL_PACKAGE_TYPES;
DROP TABLE EPL_MANUFACTURERS;
DROP TABLE EPL_PROD_DRUG_TEXT_L_ASSOCS;
DROP TABLE EPL_PROD_WARN_LABEL_N_ASSOCS;
DROP TABLE EPL_PROD_WARN_LABEL_L_ASSOCS;
DROP TABLE EPL_PRODUCT_LABS;
DROP TABLE EPL_PRODUCT_VITALS;
DROP TABLE EPL_PROD_INGREDIENT_ASSOCS;
DROP TABLE EPL_SYNONYMS;
DROP TABLE EPL_PROD_SPEC_HANDLING_ASSOCS;
DROP TABLE EPL_NDC_BY_OUTPATIENT_SITE_NDC;
DROP TABLE EPL_NATIONAL_POSSIBLE_DOSAGES;
DROP TABLE EPL_ATC_CANISTERS;
DROP TABLE EPL_PROD_DRUG_CLASS_ASSOCS;
DROP TABLE EPL_IFCAP_ITEM_NUMBERS;
DROP TABLE EPL_PROD_DRUG_TEXT_N_ASSOCS;
DROP TABLE EPL_PRODUCTS;
DROP TABLE EPL_DOSE_UNIT_SYNONYMS;
DROP TABLE EPL_DOSE_UNITS;
DROP TABLE EPL_VA_DRUG_CLASSES;
DROP TABLE EPL_CLASS_TYPES;
DROP TABLE EPL_PRINT_FIELDS;
DROP TABLE EPL_ITEM_AUDIT_HISTORY_DETAILS;
DROP TABLE EPL_VA_GEN_NAMES;

DROP TABLE EPL_LABS;
DROP TABLE EPL_ORDER_UNITS;
DROP TABLE EPL_DF_UNITS;
DROP TABLE EPL_DF_NOUNS;
DROP TABLE EPL_CMOP_ID_GENERATOR;
DROP TABLE EPL_SITE_UPDATE_SCHEDULES;
DROP TABLE HIBERNATE_UNIQUE_KEY;
DROP TABLE EPL_SEARCH_CRITERIA;
DROP TABLE EPL_WARN_LABELS;
DROP TABLE EPL_OI_DRUG_TEXT_L_ASSOCS;
DROP TABLE EPL_OI_DRUG_TEXT_N_ASSOCS;
DROP TABLE EPL_VITALS;
DROP TABLE EPL_ORDERABLE_ITEMS;
DROP TABLE EPL_VADF_LOVS;
DROP TABLE EPL_VA_DFS;
DROP TABLE EPL_DISPENSE_UNITS_PER_DOSE;
DROP TABLE EPL_DOSAGE_FORMS;
DROP TABLE EPL_SPECIAL_HANDLING;
DROP TABLE EPL_INTERFACE_COUNTERS;
DROP TABLE EPL_SEARCH_TEMPLATES;
DROP TABLE EPL_PRINT_TEMPLATES;
DROP TABLE EPL_NOTIFICATION_STATUS;
DROP TABLE EPL_LOCAL_CONSOLE_INFO;
DROP TABLE EPL_PACKAGE_USAGES;
DROP TABLE EPL_NOTIF_USER_PREFS;
DROP TABLE EPL_DT_SYNONYMS;
DROP TABLE EPL_DRUG_TEXT;
DROP TABLE EPL_INTENDED_USES;
DROP TABLE EPL_VA_DISPENSE_UNITS;
DROP TABLE EPL_SESSION_PREFERENCES;
DROP TABLE EPL_DRUG_UNITS;
DROP TABLE EPL_WARDS;
DROP TABLE EPL_HOSPITAL_LOCATIONS;
DROP TABLE EPL_ADMIN_SCHEDULES;
DROP TABLE EPL_USERS;
DROP TABLE EPL_INGREDIENTS;
DROP TABLE EPL_OI_SCHEDULE_TYPES;
DROP TABLE EPL_ITEM_AUDIT_HISTORY;
DROP TABLE EPL_NOTIFICATIONS;
DROP TABLE EPL_SITE_CONFIGS;
DROP TABLE EPL_SCHEDULE_TYPES;
DROP TABLE EPL_CMOP_ID_HISTORY;
DROP TABLE EPL_CS_FED_SCHEDULES;
DROP TABLE EPL_PHARMACY_SYSTEM;
DROP TABLE EPL_VUID_STATUS_HISTORY;
DROP TABLE EPL_STANDARD_MED_ROUTES;

CREATE TABLE EPL_NATIONAL_SETTINGS
(
       ID                   NUMERIC(30) NOT NULL,
       KEY_NAME             VARCHAR(500) NOT NULL,
       STRING_VALUE         VARCHAR(2000),
       INTEGER_VALUE        NUMERIC(30),
       DECIMAL_VALUE        NUMERIC(30,4),
       BOOLEAN_VALUE        NUMERIC(1),
       DATE_VALUE           TIMESTAMP,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_FDB_GENERIC_NAME (
       FDB_GENERIC_DRUGNAME VARCHAR(60) NOT NULL,
       EPL_GENERIC_NAME_FK  NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_FDB_DOSAGE_FORM (
       DRUG_DOSAGE_FORM_DESC VARCHAR(30) NOT NULL,
       EPL_DOSAGE_FORM_FK   NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_FDB_DRUG_UNITS (
       FDB_DRUG_STRENGTHUNITS VARCHAR(15) NOT NULL,
       EPL_DRUG_UNITS_FK    NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_FDB_DRUG_INGREDIENT (
       FDB_DRUG_INGREDIENT  VARCHAR(100) NOT NULL,
       EPL_DRUG_INGREDIENT_FK NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_FDB_DRUG_CLASS (
       FDB_DRUG_CLASS       VARCHAR(70) NOT NULL,
       EPL_DRUG_CLASS_FK    NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_FDB_PRODUCT (
       DG_GENERIC_DOSAGE_FORM VARCHAR(256),
       DG_GENERIC_DRUG_NAME VARCHAR(256),
       DG_HAS_PACKAGED_DRUGS VARCHAR(1),
       DG_GENERIC_DRUG_ID   NUMERIC(30),
       DG_ROUTE             VARCHAR(256),
       DG_SINGLE_INGREDIENT VARCHAR(1),
       DG_STRENGTH          VARCHAR(256),
       DD_CONCEPT_TYPE      VARCHAR(30),
       DD_DOSAGE_FORM       VARCHAR(256),
       DD_FEDERAL_DEA_CLASS_CODE VARCHAR(256),
       DD_GCN_SEQNO         NUMERIC(30),
       DD_HAS_PACKAGED_DRUGS VARCHAR(1),
       DD_DISPENSE_DRUG_ID  NUMERIC(30),
       DD_MULTISOURCE       VARCHAR(256),
       DD_DISPENSE_DRUG_NAME VARCHAR(256),
       DD_OBSOLETE_DATE     VARCHAR(256),
       DD_REPLACED          VARCHAR(1),
       DD_ROUTE             VARCHAR(256),
       DD_STATUS_CODE       VARCHAR(256),
       DD_STRENGTH          VARCHAR(256),
       DD_STRENGTH_UNIT     VARCHAR(256),
       PRODUCT_ID_FK        NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_FDB_NDC (
       DOSAGE_FORM          VARCHAR(256),
       FEDERAL_DEA_CLASS_CODE VARCHAR(256),
       FEDERAL_LEGEND_CODE  VARCHAR(256),
       FORMAT_CODE          VARCHAR(256),
       GCN_SEQNO            NUMERIC(10),
       LABELER_NAME         VARCHAR(256),
       NDC                  VARCHAR(256),
       NDC_FORMATTED        VARCHAR(256),
       OBSOLETE_DATE        VARCHAR(256),
       PACKAGE_DESCRIPTION  VARCHAR(256),
       PACKAGE_SIZE         NUMERIC(13,4),
       PREVIOUS_NDC         VARCHAR(256),
       REPLACEMENT_NDC      VARCHAR(256),
       STRENGTH_NUMERIC     NUMERIC(13,4),
       STRENGTH_UNIT        VARCHAR(256),
       NDC_ID_FK            NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_FDB_ADD (
       NDC                  VARCHAR(15) NOT NULL,
       PACKAGE_SIZE         VARCHAR(256), 
       PACKAGE_TYPE         VARCHAR(256), 
       MANUFACTURER         VARCHAR(30),
       LABEL_NAME           VARCHAR(256),
       ADD_DESC             VARCHAR(256),
       TRADE_NAME           VARCHAR(256), 
       FDB_PRODUCT_NAME     VARCHAR(256) NOT NULL,
       GCN_SEQNO            NUMERIC(10) NOT NULL, 
       FDB_CREATION_DATE    TIMESTAMP,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_FDB_UPDATE (
       EPL_ID               NUMERIC(30) NOT NULL,
       VA_PRODUCT_NAME      VARCHAR(100),
       NDC                  VARCHAR(13),
       FDB_PRODUCT_NAME     VARCHAR(100),
       GCN_SEQNO            NUMERIC(10),
       MESSAGE              VARCHAR(2000),
       NDC_ID_FK            NUMERIC(30),
       PRODUCT_ID_FK        NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_FDB_AUTO_ADD (
       NDC                  VARCHAR(15) NOT NULL,
       PACKAGE_SIZE         NUMERIC(10,4),
       PACKAGE_TYPE         VARCHAR(256),
       LABEL_NAME           VARCHAR(256),
       GCN_SEQNO            NUMERIC(10),
       VA_PRODUCT_NAME      VARCHAR(100),
       FDB_PRODUCT_NAME     VARCHAR(100),
       ADD_DESC             VARCHAR(256),
       NDC_ID_FK            NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_FDB_AUTO_UPDATE (
       EPL_ID               NUMERIC(30) NOT NULL,
       VA_PRODUCT_NAME      VARCHAR(100),
       FDB_PRODUCT_NAME     VARCHAR(100),
       NDC                  VARCHAR(13),
       GCN_SEQNO            NUMERIC(10),
       MESSAGE              VARCHAR(2000),
       NDC_ID_FK            NUMERIC(30),
       PRODUCT_ID_FK        NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_FDB_MONOGRAPH_PEM (
	   MONOGRAPH_ID  		NUMERIC(10) NOT NULL,
	   ENGLISH_TITLE				VARCHAR(2000),
	   SPANISH_TITLE				VARCHAR(2000),
	   ENGLISH_BRAND_NAME			VARCHAR(2000),
	   SPANISH_BRAND_NAME			VARCHAR(2000),
	   ENGLISH_MISSED_DOSE			VARCHAR(2000),
	   SPANISH_MISSED_DOSE			VARCHAR(2000),
	   ENGLISH_PHONETICS			VARCHAR(2000),
	   SPANISH_PHONETICS			VARCHAR(2000),
	   ENGLISH_HOW_TO_TAKE			VARCHAR(2000),
	   SPANISH_HOW_TO_TAKE			VARCHAR(2000),
	   ENGLISH_DRUG_INTERACTIONS	VARCHAR(2000),
	   SPANISH_DRUG_INTERACTIONS	VARCHAR(2000),
	   ENGLISH_MEDICAL_ALERTS		VARCHAR(2000),
	   SPANISH_MEDICAL_ALERTS		VARCHAR(2000),
	   ENGLISH_NOTES				VARCHAR(2000),
	   SPANISH_NOTES				VARCHAR(2000),
	   ENGLISH_OVERDOSE				VARCHAR(2000),
	   SPANISH_OVERDOSE				VARCHAR(2000),
	   ENGLISH_PRECAUTIONS			VARCHAR(2000),
	   SPANISH_PRECAUTIONS			VARCHAR(2000),
	   ENGLISH_STORAGE				VARCHAR(2000),
	   SPANISH_STORAGE				VARCHAR(2000),
	   ENGLISH_SIDE_EFFECTS			VARCHAR(2000),
	   SPANISH_SIDE_EFFECTS			VARCHAR(2000),
	   ENGLISH_USES					VARCHAR(2000),
	   SPANISH_USES					VARCHAR(2000),
	   ENGLISH_WARNINGS				VARCHAR(2000),
	   SPANISH_WARNINGS				VARCHAR(2000),
	   ENGLISH_DISCLAIMER			VARCHAR(2000),
	   SPANISH_DISCLAIMER			VARCHAR(2000),
	   CREATED_BY         			  VARCHAR(50) NOT NULL,
       CREATED_DTM         			 TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY   		  VARCHAR(50),
       LAST_MODIFIED_DTM   		 TIMESTAMP
);

CREATE TABLE EPL_FDB_GCNSEQNO_PEM (
       EPL_ID				NUMERIC(30) NOT NULL,
	   GCNSEQNO    NUMERIC(10) NOT NULL,
	   MONOGRAPH_ID NUMERIC(10) NOT NULL,
	   CREATED_BY         			  VARCHAR(50) NOT NULL,
       CREATED_DTM         			 TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY   		  VARCHAR(50),
       LAST_MODIFIED_DTM   		 TIMESTAMP
);

CREATE TABLE EPL_MIGRATION_ERROR (
       FILE_ID              NUMERIC(10,4) NOT NULL,
       MIGRATION_ROW_ID     VARCHAR(10),
       MIGRATION_UNIQUE_VALUE VARCHAR(200),
       MIGRATION_FIELD_VALUE VARCHAR(200),
       MIGRATION_FIELD_NAME VARCHAR(200),
       MIGRATION_MULT_ROW_ID VARCHAR(10),
       MIGRATION_MULT_FIELD_NAME VARCHAR(200),
       DETAILED_ERROR_TEXT  VARCHAR(2000) NOT NULL,
       PROCESSED_DTM        TIMESTAMP NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_MIGRATION_FILE (
       FILE_ID              NUMERIC(10,4) NOT NULL,
       MIGRATION_FILE_NAME  VARCHAR(100) NOT NULL,
       ROWS_PROCESSED_QTY   NUMERIC(6),
       ROWS_MIGRATED_QTY    NUMERIC(6),
       ROWS_NOT_MIGRATED_QTY NUMERIC(6),
       ERROR_QTY            NUMERIC(6),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_MIGRATION_CONTROL (
       EPL_ID               NUMERIC(10) NOT NULL,
       USER_NAME            VARCHAR(30),
       START_DTM            TIMESTAMP NOT NULL,
       STOP_DTM             TIMESTAMP,
       THREAD_ID            NUMERIC NOT NULL,
       MIGRATION_STATUS     VARCHAR(30) NOT NULL,
       USER_NDC_FILE        VARCHAR(2000),
       USER_OI_FILE         VARCHAR(2000),
       USER_PRODUCT_FILE    VARCHAR(2000),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_OI_ADMIN_SCHED_ASSOCS (
       EPL_ID_ADMIN_SCHED_FK NUMERIC(30) NOT NULL,
       EPL_ID_OI_FK         NUMERIC(30) NOT NULL,
       DEFAULT_SCHEDULE_YN  VARCHAR(1),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_MED_INSTRUCT_WARDS (
       ID                   NUMERIC(30) NOT NULL,
       WARD_DEFAULT_ADMIN_TIMES VARCHAR(256) NOT NULL,
       WARD                 VARCHAR(256) NOT NULL,
       EPL_ID_MED_INSTRUCT_FK NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_MEDICATION_INSTRUCTIONS (
       EPL_ID               NUMERIC(30) NOT NULL,
       ADDITIONAL_INSTRUCTION VARCHAR(256),
       DEFAULT_ADMIN_TIMES  VARCHAR(100) NOT NULL,
       MED_INSTRUCTION_EXPANSION VARCHAR(50) NOT NULL,
       FREQUENCY_IN_MINUTES NUMERIC(30),
       INSTRUCTIONS         VARCHAR(100),
       INTENDED_USE         VARCHAR(20),
       MED_INSTRUCTION_NAME VARCHAR(256) NOT NULL,
       MED_INSTRUCTION_OTHER_LANG_EXP VARCHAR(100),
       PLURAL               VARCHAR(50),
       MED_INSTRUCTION_SCHEDULE VARCHAR(50),
       MED_INSTRUCTION_SYNONYM VARCHAR(50),
       INACTIVATION_DATE    TIMESTAMP,
       REQUEST_STATUS       VARCHAR(30) NOT NULL,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       MED_ROUTE_ID_FK      NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_OI_MED_ROUTE_ASSOCS (
       EPL_ID_OI_FK         NUMERIC(30) NOT NULL,
       MED_ROUTE_ID_FK      NUMERIC(30) NOT NULL,
       DEFAULT_YN           VARCHAR(1) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP 
);

CREATE TABLE EPL_DF_MED_RT_DF_ASSOCS (
       DOSAGE_FORM_ID_FK    NUMERIC(30) NOT NULL,
       MED_ROUTE_ID_FK      NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_PACKAGE_USE_ASSOCS (
       EPL_ID_PACKAGE_USE_FK NUMERIC(30) NOT NULL,
       EPL_ID_LOCAL_MED_ROUTE_FK NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_LOCAL_MED_ROUTES (
       EPL_ID               NUMERIC(30) NOT NULL,
       MED_ROUTE_ABBREVIATION VARCHAR(50) NOT NULL,
       DISPLAY_ON_IVP_IVPB  VARCHAR(1),
       IV_FLAG              VARCHAR(1),
       MED_ROUTE_NAME       VARCHAR(256) NOT NULL,
       OTHER_LANGUAGE_EXPANSION VARCHAR(50),
       MED_ROUTE_OUTPATIENT_EXPANSION VARCHAR(50),
       PROMPT_FOR_INJECTION_SITE VARCHAR(1),
       INACTIVATION_DATE    TIMESTAMP,
       REQUEST_STATUS       VARCHAR(30) NOT NULL,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       EPL_ID_STANDARD_MED_RT_FK NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_LOCAL_POSSIBLE_DOSAGES (
       ID                   NUMERIC(30) NOT NULL,
       BCMA_UNITS_PER_DOSE  NUMERIC(10,4),
       LOCAL_POSSIBLE_DOSAGE VARCHAR(100) NOT NULL,
       OTHER_LANGUAGE_DOSAGE_NAME VARCHAR(100),
       PACKAGE_NAME         VARCHAR(100) NOT NULL,
       NUMERIC_DOSE         NUMERIC(30,5),
       EPL_ID_PRODUCT_FK    NUMERIC(30) NOT NULL,
       EPL_DOSE_UNIT_ID_FK  NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_SEQ_NUMS (
       TABLE_NAME           VARCHAR(256) NOT NULL,
       NEXT_SEQ_NUM         NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_PARTIAL_SAVE_MGT (
       ID                   NUMERIC(30) NOT NULL,
       ENTITY_TYPE          VARCHAR(100) NOT NULL,
       REQUEST_TYPE         VARCHAR(100) NOT NULL,
       FILE_NAME            VARCHAR(2000) NOT NULL,
       ITEM_REVISION_NUMBER NUMERIC(30) NOT NULL,
       COMMENTS             VARCHAR(2000),
       EPL_ID_OI_FK         NUMERIC(30),
       EPL_ID_PRODUCT_FK    NUMERIC(30),
       EPL_ID_NDC_FK        NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_VADF_EDITABLE_PROPERTIES (
       VADF_OWNER_ID_FK     NUMERIC(30) NOT NULL,
       VADF_ID_FK           NUMERIC(30) NOT NULL,
       EDITABLE_YN          VARCHAR(1) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_MULTI_TEXT (
       VADF_OWNER_ID_FK     NUMERIC(30) NOT NULL,
       VADF_ID_FK           NUMERIC(30) NOT NULL,
       TEXT                 VARCHAR(2000) NOT NULL,
       PK_OWNER_TYPE        VARCHAR(30),
       PK_TABLE_NAME        VARCHAR(50),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_VADF_NONLIST_VALUES (
       VADF_OWNER_ID_FK     NUMERIC(30) NOT NULL,
       VADF_ID_FK           NUMERIC(30) NOT NULL,
       VA_DF_VALUE          VARCHAR(2000) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_VADF_ASSOC_VALUES (
       VADF_OWNER_ID_FK     NUMERIC(30) NOT NULL,
       LIST_VALUE           VARCHAR(2000) NOT NULL,
       VADF_ID_FK           NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_VADF_OWNERS (
       ID                   NUMERIC(30) NOT NULL,
       EPL_ID_OI_FK         NUMERIC(30),
       EPL_ID_PRODUCT_FK    NUMERIC(30),
       EPL_ID_NDC_FK        NUMERIC(30),
       VADF_OWNER_TYPE      VARCHAR(256) NOT NULL,
       VADF_OWNER_ID        NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP 
);

CREATE TABLE EPL_REQUEST_DETAILS (
       ID                   NUMERIC(30) NOT NULL,
       EDIT_AT_LOCAL_YN     VARCHAR(1),
       MODIFICATION_REASON  VARCHAR(2000),
       SITE_NAME            VARCHAR(2000) NOT NULL,
       STATUS               VARCHAR(30) NOT NULL,
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       NOTE                 VARCHAR(2000),
       LAST_REVIEWER_NAME   VARCHAR(100),
       REQUESTER_NAME       VARCHAR(100),
       REQUEST_TO_MODIFY_YN VARCHAR(1),
       REQUEST_ID_FK        NUMERIC(30) NOT NULL,
       DIFFERENCE           BLOB NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_REQUESTS (
       ID                   NUMERIC(30) NOT NULL,
       REQUEST_TYPE         VARCHAR(100) NOT NULL,
       SITE_NAME            VARCHAR(2000) NOT NULL,
       ITEM_TYPE            VARCHAR(50) NOT NULL,
       DOMAIN_ID            NUMERIC(30),
       REQUEST_STATUS       VARCHAR(30) NOT NULL,
       NEW_ITEM_REQUEST_STATUS VARCHAR(30) NOT NULL,
       UNDER_REVIEW_FLAG    VARCHAR(1) NOT NULL,
       REQUESTER_NAME       VARCHAR(100),
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       NOTE                 VARCHAR(2000),
       LAST_REVIEWER_NAME   VARCHAR(100),
       REVISION_NUMBER      NUMERIC(30),
       MARKED_FOR_PSR       VARCHAR(1),
       PSR_NAME             VARCHAR(256),
       PROPOSED_ITEM_ID     VARCHAR(2000),
       EPL_ID_PRODUCT_FK    NUMERIC(30),
       EPL_ID_NDC_FK        NUMERIC(30),
       EPL_ID_OI_FK         NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_NDCS (
       EPL_ID               NUMERIC(30) NOT NULL,
       NDC_NUMBER           VARCHAR(13),
       NDC_NO_DASHES        VARCHAR(11),
       NDF_NDC_IEN          NUMERIC(30),
       SEQUENCE_NUM         NUMERIC(30) NOT NULL,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       REQUEST_STATUS       VARCHAR(30) NOT NULL,
       TRADE_NAME           VARCHAR(256),
       UPC_UPN              VARCHAR(100),
       SHAPE                VARCHAR(100),
       COLOR                VARCHAR(100),
       IMPRINT              VARCHAR(100),
       IMPRINT_2            VARCHAR(100),
       LOCAL_DISPENSE       VARCHAR(1) NOT NULL,
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       OTC_RX               VARCHAR(50) NOT NULL,
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       NDC_DISP_UNITS_PER_ORD_UNIT NUMERIC(10,4),
       PACKAGE_SIZE         NUMERIC(12,4),
       VENDOR               VARCHAR(256),
       VSN                  VARCHAR(256),
       TEN_DIGIT_NDC        VARCHAR(25),
       TEN_DIGIT_FORMAT_INDICATION VARCHAR(25),
       MANUFACTURER_ID_FK   NUMERIC(30),
       PACKAGE_TYPE_ID_FK   NUMERIC(30),
       SOURCE               VARCHAR(256) NOT NULL,
       SINGLE_MULTI_SOURCE  VARCHAR(50),
       INACTIVATION_DATE    TIMESTAMP,
       EPL_ID_PRODUCT_FK    NUMERIC(30) NOT NULL,
       ORDER_UNIT_ID_FK     NUMERIC(30),
       FSS_I                VARCHAR(1),
       FSS_PV               VARCHAR(1),
       FSS_CNT_NO           VARCHAR(50),
       FSS_END_DATE         TIMESTAMP,
       FSS_PRICE            NUMERIC(8,2),
       FSS_VA_PRICE         NUMERIC(8,2),
       FSS_BIG4_PRICE       NUMERIC(8,2),
       FSS_BPA_PRICE        NUMERIC(8,2),
       FSS_BPA_AVAIL        VARCHAR(1),
       FSS_NC_PRICE         NUMERIC(8,2),
       CAT_MEDIC_FLAG       VARCHAR(1) NOT NULL,
       CAT_INVEST_FLAG      VARCHAR(1) NOT NULL,
       CAT_COMPOUND_FLAG    VARCHAR(1) NOT NULL,
       CAT_SUPPLY_FLAG      VARCHAR(1) NOT NULL,
       SUBCAT_HERBAL_FLAG   VARCHAR(1) NOT NULL,
       SUBCAT_CHEMO_FLAG    VARCHAR(1) NOT NULL,
       SUBCAT_OTC_FLAG      VARCHAR(1) NOT NULL,
       SUBCAT_VETER_FLAG    VARCHAR(1) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_PACKAGE_TYPES (
       EPL_ID               NUMERIC(30) NOT NULL,
       PACKAGE_TYPE_NAME    VARCHAR(256) NOT NULL,
       NDF_PACKAGETYPE_IEN  NUMERIC(30),
       INACTIVATION_DATE    TIMESTAMP,
       REQUEST_STATUS       VARCHAR(30) NOT NULL,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_MANUFACTURERS (
       EPL_ID               NUMERIC(30) NOT NULL,
       NAME                 VARCHAR(256) NOT NULL,
       NDF_MANUFACTURER_IEN NUMERIC(30),
       PHONE                VARCHAR(120),
       ADDRESS              VARCHAR(256),
       INACTIVATION_DATE    TIMESTAMP,
       REQUEST_STATUS       VARCHAR(30) NOT NULL,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_PROD_DRUG_TEXT_L_ASSOCS (
       DRUG_TEXT_ID_FK      NUMERIC(30) NOT NULL,
       EPL_ID_PROD_FK       NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_PROD_WARN_LABEL_N_ASSOCS (
       EPL_ID_PROD_FK       NUMERIC(30) NOT NULL,
       EPL_ID_WARN_LABEL_FK NUMERIC(30) NOT NULL,
       SEQ_NUMBER           NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_PROD_WARN_LABEL_L_ASSOCS (
       EPL_ID_PROD_FK       NUMERIC(30) NOT NULL,
       EPL_ID_WARN_LABEL_FK NUMERIC(30) NOT NULL,
       SEQ_NUMBER           NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_PRODUCT_LABS (
       ID                   NUMERIC(30) NOT NULL,
       LAB_DISPLAY_ORDER_ENTRY VARCHAR(2000),
       LAB_DISPLAY_FINISH_AN_ORDER VARCHAR(2000),
       LAB_DISPLAY_ADMINISTRATION VARCHAR(2000),
       EPL_ID_PRODUCT_FK    NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_PRODUCT_VITALS (
       ID                   NUMERIC(30) NOT NULL,
       VITAL_DISPLAY_ORDER_ENTRY VARCHAR(2000),
       VITAL_DISPLAY_FINISH_AN_ORDER VARCHAR(2000),
       VITAL_DISPLAY_ADMINISTRATION VARCHAR(2000),
       EPL_ID_PRODUCT_FK    NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_PROD_INGREDIENT_ASSOCS (
       INGREDIENT_ID_FK     NUMERIC(30) NOT NULL,
       EPL_ID_PRODUCT_FK    NUMERIC(30) NOT NULL,
       ACTIVE_YN            VARCHAR(1) NOT NULL,
       STRENGTH             VARCHAR(256),
       DRUG_UNIT_ID_FK      NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_SYNONYMS (
       ID                   NUMERIC(30) NOT NULL,
       SYNONYM_NAME         VARCHAR(256) NOT NULL,
       NDC_CODE             VARCHAR(256),
       VSN                  VARCHAR(256),
       DISPENSE_UNITS_PER_ORDER_UNIT NUMERIC(10,4),
       PRICE_PER_DISPENSE_UNIT NUMERIC(8,2),
       VENDOR               VARCHAR(256),
       PRICE_PER_ORDER_UNIT NUMERIC(8,2),
       EPL_ID_PRODUCT_FK    NUMERIC(30),
       ORDER_UNIT_ID_FK     NUMERIC(30),
       INTENDED_USE_ID_FK   NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_PROD_SPEC_HANDLING_ASSOCS (
       EPL_ID_PROD_FK       NUMERIC(30) NOT NULL,
       EPL_ID_SPEC_HANDLING_FK NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_NDC_BY_OUTPATIENT_SITE_NDC (
       ID                   NUMERIC(30) NOT NULL,
       OUTPATIENT_SITE      VARCHAR(256),
       LAST_LOCAL_NDC       VARCHAR(15),
       LAST_CMOP_NDC        VARCHAR(15),
       EPL_ID_PRODUCT_FK    NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_NATIONAL_POSSIBLE_DOSAGES (
       ID                   NUMERIC(30) NOT NULL,
       BCMA_UNITS_PER_DOSE  NUMERIC(10,4),
       DISPENSE_UNITS_PER_DOSE NUMERIC(15,4) NOT NULL,
       DOSE                 NUMERIC(10,4) NOT NULL,
       PACKAGE_NAME         VARCHAR(100) NOT NULL,
       EPL_ID_PRODUCT_FK    NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_ATC_CANISTERS (
       ID                   NUMERIC(30) NOT NULL,
       ATC_CANISTER         NUMERIC(30),
       WARD_GROUP_FOR_ATC   VARCHAR(200) NOT NULL,
       EPL_ID_PRODUCT_FK    NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_PROD_DRUG_CLASS_ASSOCS (
       DRUG_CLASS_ID_FK     NUMERIC(30) NOT NULL,
       EPL_ID_PRODUCT_FK    NUMERIC(30) NOT NULL,
       PRIMARY_YN           VARCHAR(1) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_IFCAP_ITEM_NUMBERS (
       ID                   NUMERIC(30) NOT NULL,
       IFCAP_ITEM_NUMBER    VARCHAR(256) NOT NULL,
       PRODUCT_EPL_ID_FK    NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_PROD_DRUG_TEXT_N_ASSOCS (
       EPL_ID_PROD_FK       NUMERIC(30) NOT NULL,
       DRUG_TEXT_ID_FK      NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_PRODUCTS (
       EPL_ID               NUMERIC(30) NOT NULL,
       VUID                 VARCHAR(100),
       MASTER_ENTRY_FOR_VUID VARCHAR(1),
       REQUEST_STATUS       VARCHAR(30) NOT NULL,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       VA_PRODUCT_NAME      VARCHAR(100) NOT NULL,
       LOCAL_USE            VARCHAR(1) NOT NULL,
       GCN_SEQNO            NUMERIC(10),
       SINGLE_MULTI_SOURCE_PRODUCT VARCHAR(50),
       NATIONAL_FORMULARY_NAME VARCHAR(256),
       MAX_DOSE_PER_DAY     NUMERIC(3),
       NEW_WARNING_LABEL_LIST_WARN VARCHAR(256),
       TALLMAN_LETTERING    VARCHAR(256),
       PRODUCT_DISPENSE_UNITS_PER_ORD NUMERIC(10,4),
       NATIONAL_FORMULARY_INDICATOR VARCHAR(1),
       PRODUCT_NDC          VARCHAR(14),
       VA_PRINT_NAME        VARCHAR(2000),
       STRENGTH             VARCHAR(256),
       CMOP_ID              VARCHAR(256),
       SPECIMEN_TYPE        VARCHAR(256),
       INACTIVATION_DATE    TIMESTAMP,
       LAB_TEST_MONITOR     VARCHAR(2000),
       IEN                  NUMERIC(30),
       OVERRIDE_DF_DOSE_CHK_EXCLUSN VARCHAR(5),
       LOCAL_PRINT_NAME     VARCHAR(2000),
       LOCAL_SPECIAL_HANDLING VARCHAR(40),
       ATC_MNEMONIC         VARCHAR(15),
       ATC_CHOICE           VARCHAR(20),
       ONE_ATC_CANISTER     NUMERIC(30),
       CMOP_DISPENSE_YN     VARCHAR(1) NOT NULL,
       PREV_MARKED_FOR_LOCAL_USE_YN VARCHAR(1) NOT NULL,
       CS_FED_SCHED_ID_FK   NUMERIC(30) NOT NULL,
       EPL_ID_OI_FK         NUMERIC(30) NOT NULL,
       ORDER_UNIT_ID_FK     NUMERIC(30),
       VA_GEN_NAME_ID_FK    NUMERIC(30),
       DISPENSE_UNIT_ID_FK  NUMERIC(30),
       DOSE_UNIT_ID_FK      NUMERIC(30),
       DRUG_UNIT_ID_FK      NUMERIC(30),
       CREATE_POSSIBLE_DOSAGE VARCHAR(1) NOT NULL,
       EXCLUDE_INTERACTION_CHECK VARCHAR(1) NOT NULL,
       FDA_MED_GUIDE        VARCHAR(100),
       NDF_PRODUCT_IEN      NUMERIC(30),
       POSSIBLE_DOSAGES_TO_CREATE VARCHAR(30),
       PRODUCT_PACKAGE      VARCHAR(25),
       SERVICE_CODE         NUMERIC(6),
       CAT_MEDIC_FLAG       VARCHAR(1) NOT NULL,
       CAT_INVEST_FLAG      VARCHAR(1) NOT NULL,
       CAT_COMPOUND_FLAG    VARCHAR(1) NOT NULL,
       CAT_SUPPLY_FLAG      VARCHAR(1) NOT NULL,
       SUBCAT_HERBAL_FLAG   VARCHAR(1) NOT NULL,
       SUBCAT_CHEMO_FLAG    VARCHAR(1) NOT NULL,
       SUBCAT_OTC_FLAG      VARCHAR(1) NOT NULL,
       SUBCAT_VETER_FLAG    VARCHAR(1) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_DOSE_UNIT_SYNONYMS (
       DOSE_UNIT_SYNONYM    VARCHAR(256) NOT NULL,
       DOSE_UNIT_EPL_ID_FK  NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_DOSE_UNITS (
       EPL_ID               NUMERIC(30) NOT NULL,
       DOSE_UNIT_NAME       VARCHAR(50) NOT NULL,
       DOSE_UNIT_IND	    VARCHAR(1) NOT NULL,
       FIRST_DATABANK_DOSE_UNIT VARCHAR(30) NOT NULL,
       REQUEST_STATUS       VARCHAR(30) NOT NULL,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       INACTIVATION_DATE    TIMESTAMP,
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       ASSOC_REPLACED_BY_DU_ID_FK NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_VA_DRUG_CLASSES (
       EPL_ID               NUMERIC(30) NOT NULL,
       DESCRIPTION          VARCHAR(2000),
       VUID                 VARCHAR(100),
       MASTER_ENTRY_FOR_VUID VARCHAR(1),
       REQUEST_STATUS       VARCHAR(30) NOT NULL,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       INACTIVATION_DATE    TIMESTAMP,
       CLASSIFICATION_NAME  VARCHAR(100) NOT NULL,
       CODE                 VARCHAR(5) NOT NULL,
       PARENT_CLASS_ID_FK   NUMERIC(30),
       CLASS_TYPE_ID_FK     NUMERIC(30),
       NDF_CLASS_IEN        NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_CLASS_TYPES (
       ID                   NUMERIC(30) NOT NULL,
       CLASS_TYPE           VARCHAR(50) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_PRINT_FIELDS (
       EPL_ID_PRINT_TEMPLATE_FK NUMERIC(30) NOT NULL,
       PRINT_FIELD_NAME     VARCHAR(256) NOT NULL,
       TEMPLATE_FIELD_TYPE  VARCHAR(256) NOT NULL,
       SEQUENCE_NUMBER      NUMERIC(10) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_ITEM_AUDIT_HISTORY_DETAILS (
       EPL_ID               NUMERIC(30) NOT NULL,
       COL_NM             	VARCHAR(256),
       OLD_VALUE            VARCHAR(4000),
       NEW_VALUE            VARCHAR(4000),
       DETAIL_EVENT_CATEGORY VARCHAR(256) NOT NULL,
       DETAIL_REASON        VARCHAR(2000),
       IAH_ID_FK            NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_VA_GEN_NAMES (
       EPL_ID               NUMERIC(30) NOT NULL,
       VUID                 VARCHAR(100),
       MASTER_ENTRY_FOR_VUID VARCHAR(1),
       GENERIC_NAME         VARCHAR(2000) NOT NULL,
       INACTIVATION_DATE    TIMESTAMP,
       REQUEST_STATUS       VARCHAR(30) NOT NULL,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       NDF_GENERIC_IEN      NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_STANDARD_MED_ROUTES (
       EPL_ID               NUMERIC(30) NOT NULL,
       VUID                 VARCHAR(100),
       MASTER_ENTRY_FOR_VUID VARCHAR(1),
       STANDARD_MED_ROUTE_NAME VARCHAR(256) NOT NULL,
       FIRST_DATABANK_MED_ROUTE VARCHAR(256) NOT NULL,
       INACTIVATION_DATE    TIMESTAMP,
       REQUEST_STATUS       VARCHAR(30) NOT NULL,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       ASSOC_REPL_BY_VHA_STD_TERM_FK NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_LABS (
       ID                   NUMERIC(30) NOT NULL,
       LAB_DISPLAY_ORDER_ENTRY VARCHAR(2000),
       LAB_DISPLAY_FINISH_AN_ORDER VARCHAR(2000),
       LAB_DISPLAY_ADMINISTRATION VARCHAR(2000),
       ORDERABLE_ITEM_ID_FK NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_ORDER_UNITS (
       EPL_ID               NUMERIC(30) NOT NULL,
       ORDER_UNIT_ABBREVIATION VARCHAR(3) NOT NULL,
       ORDER_UNIT_EXPANSION VARCHAR(30),
       REQUEST_STATUS       VARCHAR(30) NOT NULL,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       INACTIVATION_DATE    TIMESTAMP,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_DF_UNITS (
       DOSAGE_FORM_EPL_ID_FK NUMERIC(30) NOT NULL,
       DRUG_UNIT_EPL_ID_FK  NUMERIC(30) NOT NULL,
       PACKAGE_CODE         VARCHAR(20),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_DF_NOUNS (
       DOSAGE_FORM_EPL_ID_FK NUMERIC(30) NOT NULL,
       NOUN                 VARCHAR(256) NOT NULL,
       OTHER_LANGUAGE_NOUN  VARCHAR(256),
       PACKAGE_CODE         VARCHAR(20),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_CMOP_ID_GENERATOR (
       PROD_NAME_INITIAL_LETTER VARCHAR(1) NOT NULL,
       LAST_USED_CMOP_ID    VARCHAR(5) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_NDF_SYNCH_QUEUE (
       ID               NUMERIC(30) NOT NULL,
       ID_FK                NUMERIC(30) NOT NULL,
       ITEM_TYPE            VARCHAR(256) NOT NULL,
       ACTION_TYPE          VARCHAR(256) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_SITE_UPDATE_SCHEDULES (
       ID                   NUMERIC(30) NOT NULL,
       SITE_NUMBER          VARCHAR(256) NOT NULL,
       SCHEDULED_INTERVAL   VARCHAR(256),
       SOFTWARE_UPDATE_TYPE VARCHAR(256) NOT NULL,
       SCHEDULED_START_DTM  TIMESTAMP NOT NULL,
       START_DTM            TIMESTAMP,
       END_DTM              TIMESTAMP,
       IN_PROGRESS          VARCHAR(1),
       SOFTWARE_UPDATE_VERSION VARCHAR(50) NOT NULL,
       MD5SUM               VARCHAR(256),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE HIBERNATE_UNIQUE_KEY (
       NEXT_HI              NUMERIC(30)
);

CREATE TABLE EPL_SEARCH_CRITERIA (
       EPL_ID_SEARCH_TEMPLATE_FK NUMERIC(30) NOT NULL,
       SEARCH_FIELD_NAME    VARCHAR(256) NOT NULL,
       SEARCH_FIELD_VALUE   VARCHAR(2000) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_WARN_LABELS (
       EPL_ID               NUMERIC(30) NOT NULL,
       WARNING_LABEL_TYPE   VARCHAR(256) NOT NULL,
       INACTIVATION_DATE    TIMESTAMP,
       NAME                 VARCHAR(256) NOT NULL,
       SPANISH_TRANSLATION  VARCHAR(250),
       CONSULT_TEXT         VARCHAR(2000) NOT NULL,
       REQUEST_STATUS       VARCHAR(30) NOT NULL,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       ASSOC_WARNING_MAPPING_ID_FK NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_OI_DRUG_TEXT_L_ASSOCS (
       EPL_ID_OI_FK         NUMERIC(30) NOT NULL,
       DRUG_TEXT_ID_FK      NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_OI_DRUG_TEXT_N_ASSOCS (
       EPL_ID_OI_FK         NUMERIC(30) NOT NULL,
       DRUG_TEXT_ID_FK      NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_VITALS (
       ID                   NUMERIC(30) NOT NULL,
       VITAL_DISPLAY_ORDER_ENTRY VARCHAR(2000),
       VITAL_DISPLAY_FINISH_AN_ORDER VARCHAR(2000),
       VITAL_DISPLAY_ADMINISTRATION VARCHAR(2000),
       ORDERABLE_ITEM_ID_FK NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_ORDERABLE_ITEMS (
       EPL_ID               NUMERIC(30) NOT NULL,
       OI_TYPE              VARCHAR(1) NOT NULL,
       REQUEST_STATUS       VARCHAR(30) NOT NULL,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       OI_NAME              VARCHAR(256) NOT NULL,
       VISTA_OI_NAME        VARCHAR(2000) NOT NULL,
       LOCAL_USE            VARCHAR(1) NOT NULL,
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       NATIONAL_FORMULARY_INDICATOR VARCHAR(1),
       NON_VA_MED           VARCHAR(1) NOT NULL,
       INACTIVATION_DATE    TIMESTAMP,
       PREV_MARKED_FOR_LOCAL_USE_YN VARCHAR(1) NOT NULL,
       DOSAGE_FORM_ID_FK    NUMERIC(30) NOT NULL,
       ASSOC_NATIONAL_OI_ID_FK NUMERIC(30),
       OI_SCHED_TYPE_ID_FK  NUMERIC(30),
       HIGH_ALERT           VARCHAR(120),
       SPECIAL_INSTRUCTIONS VARCHAR(120),
       STANDARD_MED_ROUTE_ID_FK NUMERIC(30),
       CAT_MEDIC_FLAG       VARCHAR(1) NOT NULL,
       CAT_INVEST_FLAG      VARCHAR(1) NOT NULL,
       CAT_COMPOUND_FLAG    VARCHAR(1) NOT NULL,
       CAT_SUPPLY_FLAG      VARCHAR(1) NOT NULL,
       SUBCAT_HERBAL_FLAG   VARCHAR(1) NOT NULL,
       SUBCAT_CHEMO_FLAG    VARCHAR(1) NOT NULL,
       SUBCAT_OTC_FLAG      VARCHAR(1) NOT NULL,
       SUBCAT_VETER_FLAG    VARCHAR(1) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_VADF_LOVS (
       VADF_ID_FK           NUMERIC(30) NOT NULL,
       LIST_VALUE           VARCHAR(2000) NOT NULL,
       DEFAULT_VALUE        VARCHAR(1) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_VA_DFS (
       ID                   NUMERIC(30) NOT NULL,
       VADF_NAME            VARCHAR(100) NOT NULL,
       VADF_TYPE            VARCHAR(30) NOT NULL,
       MULTI_SELECT_YN      VARCHAR(1) NOT NULL,
       DEFAULT_VALUE        VARCHAR(256),
       VADF_USAGES          VARCHAR(100) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_DISPENSE_UNITS_PER_DOSE (
       DOSAGE_FORM_EPL_ID_FK NUMERIC(30) NOT NULL,
       DISPENSE_UNIT_PER_DOSE NUMERIC(15,4) NOT NULL,
       PACKAGE_CODE         VARCHAR(20),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_DOSAGE_FORMS (
       EPL_ID               NUMERIC(30) NOT NULL,
       DF_NAME              VARCHAR(50) NOT NULL,
       DOSAGEFORM_IEN		NUMERIC(30),
       EXCLUDE_FROM_DOSAGE_CHKS VARCHAR(5),
       INACTIVATION_DATE    TIMESTAMP,
       REQUEST_STATUS       VARCHAR(30) NOT NULL,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_SPECIAL_HANDLING (
       EPL_ID               NUMERIC(30) NOT NULL,
       SPECIAL_HANDLING_CODE VARCHAR(1) NOT NULL,
       SPECIAL_HANDLING_DESCRIPTION VARCHAR(50) NOT NULL,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       INACTIVATION_DATE    TIMESTAMP,
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_INTERFACE_COUNTERS (
       INTERFACE_NAME       VARCHAR(256) NOT NULL,
       COUNTER_NAME         VARCHAR(256) NOT NULL,
       COUNTER_VALUE        NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_SEARCH_TEMPLATES (
       EPL_ID               NUMERIC(30) NOT NULL,
       TEMPLATE_NAME        VARCHAR(256) NOT NULL,
       TEMPLATE_TYPE        VARCHAR(256),
       ADVANCED_SEARCH_YN   VARCHAR(1) NOT NULL,
       AND_SEARCH_YN        VARCHAR(1),
       DEFAULT_YN           VARCHAR(1) NOT NULL,
       NOTES                VARCHAR(2000),
       EPL_ID_PRINT_TEMPLATE_FK NUMERIC(30) NOT NULL,
       USER_ID_FK           NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_PRINT_TEMPLATES (
       EPL_ID               NUMERIC(30) NOT NULL,
       TEMPLATE_LOCATION    VARCHAR(256) NOT NULL,
       TEMPLATE_NAME        VARCHAR(256) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_NOTIFICATION_STATUS (
       NOTIFICATION_ID_FK   NUMERIC(30) NOT NULL,
       USER_ID_FK           NUMERIC(30) NOT NULL,
       STATUS               VARCHAR(256) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_LOCAL_CONSOLE_INFO (
       SITE_NUMBER          VARCHAR(256) NOT NULL,
       SOFTWARE_UPDATE_TYPE VARCHAR(256) NOT NULL,
       SITE_NAME            VARCHAR(256) NOT NULL,
       SERVER_NAME          VARCHAR(256) NOT NULL,
       VERSION_INSTALL_DTM  VARCHAR(256) NOT NULL,
       SOFTWARE_UPDATE_VERSION VARCHAR(50) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_PACKAGE_USAGES (
       ID                   NUMERIC(30) NOT NULL,
       PACKAGE_USE_CODE     VARCHAR(1) NOT NULL,
       DESCRIPTION          VARCHAR(256) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_NOTIF_USER_PREFS (
       NOTIFICATION_TYPE    VARCHAR(50) NOT NULL,
       USER_ID_FK           NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_DT_SYNONYMS (
       EPL_ID_DT_FK         NUMERIC(30) NOT NULL,
       DRUG_TEXT_SYNONYM    VARCHAR(100) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_DRUG_TEXT (
       EPL_ID               NUMERIC(30) NOT NULL,
       INACTIVATION_DATE    TIMESTAMP,
       DRUG_TEXT_TYPE       VARCHAR(256) NOT NULL,
       DRUG_TEXT_NAME       VARCHAR(256) NOT NULL,
       DRUG_TEXT_LOCAL      VARCHAR(2000),
       DRUG_TEXT_NATIONAL   VARCHAR(2000),
       REQUEST_STATUS       VARCHAR(30) NOT NULL,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_INTENDED_USES (
       EPL_ID               NUMERIC(30) NOT NULL,
       CODE                 VARCHAR(1) NOT NULL,
       INTENDED_USE         VARCHAR(256) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_VA_DISPENSE_UNITS (
       EPL_ID               NUMERIC(30) NOT NULL,
       DISPENSE_UNIT_NAME   VARCHAR(200) NOT NULL,
       NDF_DISPENSEUNIT_IEN NUMERIC(30),
       INACTIVATION_DATE    TIMESTAMP,
       REQUEST_STATUS       VARCHAR(30) NOT NULL,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_SESSION_PREFERENCES (
       USER_ID_FK           NUMERIC(30) NOT NULL,
       PREF_NAME            VARCHAR(256) NOT NULL,
       PREF_VALUE           VARCHAR(2000) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_DRUG_UNITS (
       EPL_ID               NUMERIC(30) NOT NULL,
       NAME                 VARCHAR(256) NOT NULL,
       NDF_DRUGUNIT_IEN     NUMERIC(30),
       INACTIVATION_DATE    TIMESTAMP,
       REQUEST_STATUS       VARCHAR(30) NOT NULL,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_WARDS (
       ID                   NUMERIC(30) NOT NULL,
       WARD                 VARCHAR(256) NOT NULL,
       EPL_ID_ADMIN_SCHED_FK NUMERIC(30),
       WARD_ADMIN_TIMES     VARCHAR(256) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_HOSPITAL_LOCATIONS (
       ID                   NUMERIC(30) NOT NULL,
       ADMINISTRATION_TIMES VARCHAR(256),
       HOSPITAL_LOCATION    VARCHAR(256) NOT NULL,
       SHIFTS               VARCHAR(50),
       EPL_ID_ADMIN_SCHED_FK NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_ADMIN_SCHEDULES (
       EPL_ID               NUMERIC(30) NOT NULL,
       FREQUENCY_IN_MINUTES NUMERIC(10),
       SCHEDULE_NAME        VARCHAR(100) NOT NULL,
       OTHER_LANGUAGE_EXPANSION VARCHAR(50),
       SCHEDULE_OUTPATIENT_EXPANSION VARCHAR(50),
       PACKAGE_PREFIX       VARCHAR(4) NOT NULL,
       STANDARD_ADMINISTRATION_TIMES VARCHAR(256),
       STANDARD_SHIFTS      VARCHAR(256),
       INACTIVATION_DATE    TIMESTAMP,
       REQUEST_STATUS       VARCHAR(30) NOT NULL,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       SCHEDULE_TYPE_ID_FK  NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_USERS (
       ID                   NUMERIC(30) NOT NULL,
       DUZ				    NUMERIC(30) NOT NULL,
       FIRST_NAME           VARCHAR(256) NOT NULL,
       LAST_NAME            VARCHAR(256) NOT NULL,
       LOCATION             VARCHAR(256),
       USER_NAME            VARCHAR(256) NOT NULL,
       ROLE_PSR_FLAG        VARCHAR(1) NOT NULL,
       ROLE_PNM_FLAG        VARCHAR(1) NOT NULL,
       ROLE_PNS_FLAG        VARCHAR(1) NOT NULL,
       ROLE_MIG_FLAG        VARCHAR(1) NOT NULL,
       ROLE_PSR_APVD_FLAG   VARCHAR(1) NOT NULL,
       ROLE_PNM_APVD_FLAG   VARCHAR(1) NOT NULL,
       ROLE_PNS_APVD_FLAG   VARCHAR(1) NOT NULL,
       ROLE_MIG_APVD_FLAG   VARCHAR(1) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_INGREDIENTS (
       EPL_ID               NUMERIC(30) NOT NULL,
       VUID                 VARCHAR(100),
       MASTER_ENTRY_FOR_VUID VARCHAR(1),
       NAME                 VARCHAR(256) NOT NULL,
       INACTIVATION_DATE    TIMESTAMP,
       REQUEST_STATUS       VARCHAR(30) NOT NULL,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       EPL_ID_PRIMARY_INGREDIENT_FK NUMERIC(30),
       NDF_INGREDIENT_IEN   NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_OI_SCHEDULE_TYPES (
       ID                   NUMERIC(30) NOT NULL,
       CODE                 VARCHAR(30) NOT NULL,
       OI_SCHEDULE_TYPE     VARCHAR(256) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_ITEM_AUDIT_HISTORY (
       EPL_ID               NUMERIC(30) NOT NULL,
       AUDIT_ITEM_TYPE      VARCHAR(256) NOT NULL,
       AUDIT_ITEM_ID        NUMERIC(30) NOT NULL,
       SITE_NAME            VARCHAR(256) NOT NULL,
       EVENT_CATEGORY       VARCHAR(256) NOT NULL,
       REASON               VARCHAR(2000),
       NOTIFICATION_ID_FK   NUMERIC(30),
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_NOTIFICATIONS (
       ID                   NUMERIC(30) NOT NULL,
       NOTIFICATION_TYPE    VARCHAR(50) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_SITE_CONFIGS (
       SITE_NUMBER          NUMERIC(5) NOT NULL,
       SITE_NAME            VARCHAR(256) NOT NULL,
       SERVER_NAME          VARCHAR(256) NOT NULL,
       EPL_VERSION          VARCHAR(50) NOT NULL,
       EPL_VERSION_DTM      TIMESTAMP NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_SCHEDULE_TYPES (
       ID                   NUMERIC(30) NOT NULL,
       CODE                 VARCHAR(10) NOT NULL,
       SCHEDULE_TYPE_NAME   VARCHAR(256) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_CMOP_ID_HISTORY (
       VA_PRINT_NAME        VARCHAR(2000) NOT NULL,
       DISPENSE_UNIT		VARCHAR(200) NOT NULL,
       CMOP_ID_USED         VARCHAR(5) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_CS_FED_SCHEDULES (
       EPL_ID               NUMERIC(30) NOT NULL,
       SCHEDULE_NAME        VARCHAR(50) NOT NULL,
       REQUEST_STATUS       VARCHAR(30) NOT NULL,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       REJECT_REASON_TEXT   VARCHAR(2000),
       REQUEST_REJECT_REASON VARCHAR(100),
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       INACTIVATION_DATE    TIMESTAMP,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_PHARMACY_SYSTEM (
       SITE_NUMBER          NUMERIC(5) NOT NULL,
       PS_AND               VARCHAR(256),
       PS_CMOP_WARNING_LABEL_SOURCE VARCHAR(256),
       PS_DAYS              VARCHAR(256),
       PS_EXCEPT            VARCHAR(256),
       PS_EIGHT             VARCHAR(256),
       PS_FIVE              VARCHAR(256),
       PS_FOR               VARCHAR(256),
       PS_FOUR              VARCHAR(256),
       PS_HOURS             VARCHAR(256),
       PS_MINUTES           VARCHAR(256),
       PS_MONTHS            VARCHAR(256),
       PS_NINE              VARCHAR(256),
       PS_ONE               VARCHAR(256),
       PS_ONE_FOURTH        VARCHAR(256),
       PS_ONE_HALF          VARCHAR(256),
       PS_ONE_THIRD         VARCHAR(256),
       PS_OPAI_WARNING_LABEL_SOURCE VARCHAR(256) NOT NULL,
       PS_PMIS_LANGUAGE     VARCHAR(256),
       PS_PMIS_PRINTER      VARCHAR(256) NOT NULL,
       PS_PMIS_SECTION_DELETE VARCHAR(256),
       PS_SECONDS           VARCHAR(256),
       PS_SEVEN             VARCHAR(256),
       PS_SITE_NAME         VARCHAR(256) NOT NULL,
       PS_SIX               VARCHAR(256),
       PS_TEN               VARCHAR(256),
       PS_THEN              VARCHAR(256),
       PS_THREE             VARCHAR(256),
       PS_THREE_FOURTHS     VARCHAR(256),
       PS_TWO               VARCHAR(256),
       PS_TWO_THIRDS        VARCHAR(256),
       PS_WARNING_LABEL_SOURCE VARCHAR(256) NOT NULL,
       PS_WEEKS             VARCHAR(256),
       INACTIVATION_DATE    TIMESTAMP,
       ITEM_STATUS          VARCHAR(30) NOT NULL,
       REVISION_NUMBER      NUMERIC(30) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_VUID_STATUS_HISTORY (
       ID                   NUMERIC(30) NOT NULL,
       ITEM_TYPE            NUMERIC(1) NOT NULL,
       VUID                 VARCHAR(100) NOT NULL,
       EFFECTIVE_DTM        TIMESTAMP NOT NULL,
       STATUS               VARCHAR(1) NOT NULL,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);

CREATE TABLE EPL_REDUCED_COPAY (
       ID                   NUMERIC(30) NOT NULL,
       PRODUCT_FK           NUMERIC(30) NOT NULL,
       START_DATE           TIMESTAMP NOT NULL,
       STOP_DATE	        TIMESTAMP,
       CREATED_BY           VARCHAR(50) NOT NULL,
       CREATED_DTM          TIMESTAMP NOT NULL,
       LAST_MODIFIED_BY     VARCHAR(50),
       LAST_MODIFIED_DTM    TIMESTAMP
);


CREATE VIEW EPL_VADF_VALUES_VIEW AS
SELECT D.VADF_OWNER_ID_FK VADF_OWNER_ID_FK,
       D.VADF_NAME VADF_NAME,
       N.VA_DF_VALUE VADF_VALUE
FROM (SELECT O.ID VADF_OWNER_ID_FK,
             V.VADF_NAME VADF_NAME,
             V.ID VADF_ID_FK
      FROM EPL_VADF_OWNERS O,
           EPL_VADF_EDITABLE_PROPERTIES P,
           EPL_VA_DFS V
      WHERE O.ID = P.VADF_OWNER_ID_FK
      AND   P.VADF_ID_FK = V.ID
      AND   V.VADF_TYPE NOT IN ('LIST','PKEY')
      AND   V.MULTI_SELECT_YN = 'N') D 
     LEFT JOIN EPL_VADF_NONLIST_VALUES N ON D.VADF_OWNER_ID_FK = N.VADF_OWNER_ID_FK AND D.VADF_ID_FK = N.VADF_ID_FK
UNION
SELECT D.VADF_OWNER_ID_FK VADF_OWNER_ID_FK,
       D.VADF_NAME VADF_NAME,
       L.LIST_VALUE VADF_VALUE
FROM (SELECT O.ID VADF_OWNER_ID_FK,
             V.VADF_NAME VADF_NAME,
             V.ID VADF_ID_FK
      FROM EPL_VADF_OWNERS O,
           EPL_VADF_EDITABLE_PROPERTIES P,
           EPL_VA_DFS V
      WHERE O.ID = P.VADF_OWNER_ID_FK
      AND   P.VADF_ID_FK = V.ID
      AND   V.VADF_TYPE = 'LIST') D 
     LEFT JOIN EPL_VADF_ASSOC_VALUES L ON D.VADF_OWNER_ID_FK = L.VADF_OWNER_ID_FK AND D.VADF_ID_FK = L.VADF_ID_FK
UNION
SELECT D.VADF_OWNER_ID_FK VADF_OWNER_ID_FK,
       D.VADF_NAME VADF_NAME,
       M.TEXT VADF_VALUE
FROM (SELECT O.ID VADF_OWNER_ID_FK,
             V.VADF_NAME VADF_NAME,
             V.ID VADF_ID_FK
      FROM EPL_VADF_OWNERS O,
           EPL_VADF_EDITABLE_PROPERTIES P,
           EPL_VA_DFS V
      WHERE O.ID = P.VADF_OWNER_ID_FK
      AND   P.VADF_ID_FK = V.ID
      AND   ((V.VADF_TYPE <> 'LIST' AND V.MULTI_SELECT_YN = 'Y') OR V.VADF_TYPE = 'PKEY')) D 
     LEFT JOIN EPL_MULTI_TEXT M ON D.VADF_OWNER_ID_FK = M.VADF_OWNER_ID_FK AND D.VADF_ID_FK = M.VADF_ID_FK;


