DROP TABLE ppsnepl.epl_ndf_update_file CASCADE CONSTRAINTS;
DROP TABLE ppsnepl.epl_status CASCADE CONSTRAINTS;

CREATE TABLE ppsnepl.epl_ndf_update_file
(
   ndf_update_file_id       NUMERIC (30) NOT NULL
  ,file_name                VARCHAR (200)
  ,comments                 VARCHAR (2000)
  ,status_id_fk             NUMERIC (30) NOT NULL
  ,prod_transmission_date   TIMESTAMP
  ,test_transmission_date   TIMESTAMP
  ,status_modified_date     TIMESTAMP  
  ,file_directory_path      VARCHAR (500)
  ,created_by               VARCHAR (50) NOT NULL
  ,created_dtm              TIMESTAMP NOT NULL
  ,last_modified_by         VARCHAR (50)
  ,last_modified_dtm        TIMESTAMP
);
--   TABLESPACE ppsnepl_data;

CREATE TABLE ppsnepl.epl_status
(
   status_id           NUMERIC (30) NOT NULL
  ,status_name         VARCHAR (200)
  ,status_desc         VARCHAR (1000)
  ,created_by          VARCHAR (50) NOT NULL
  ,created_dtm         TIMESTAMP NOT NULL
  ,last_modified_by    VARCHAR (50)
  ,last_modified_dtm   TIMESTAMP
);
--   TABLESPACE ppsnepl_data;

CREATE UNIQUE INDEX ppsnepl.pkndfupdatefile
   ON ppsnepl.epl_ndf_update_file (ndf_update_file_id);
--   TABLESPACE ppsnepl_data;

CREATE INDEX ppsnepl.idxstatus_id
   ON ppsnepl.epl_ndf_update_file (status_id_fk);
--   TABLESPACE ppsnepl_data;

CREATE UNIQUE INDEX ppsnepl.pkstatus
   ON ppsnepl.epl_status (status_id);
--   TABLESPACE ppsnepl_data;

GRANT SELECT ON ppsnepl.epl_ndf_update_file TO ppsnepl_read_only_role;
GRANT SELECT ON ppsnepl.epl_status TO ppsnepl_read_only_role;