DROP TABLE ppsnepl.epl_vista_update CASCADE CONSTRAINTS;
DROP TABLE ppsnepl.epl_vista_status CASCADE CONSTRAINTS;

CREATE TABLE ppsnepl.epl_vista_update
(
   --   vista_update_status_id   NUMERIC (30) NOT NULL
   vista_update_id     NUMERIC (30) NOT NULL
  ,file_fk             NUMERIC (30) NOT NULL
  ,site                NUMERIC (30) NOT NULL
  ,comments            VARCHAR (2000)
  ,vista_status_id_fk  NUMERIC (30) NOT NULL
  ,transmission_date   TIMESTAMP
);
-- TABLESPACE ppsnepl_data;

CREATE TABLE ppsnepl.epl_vista_status
(
   -- status_id           NUMERIC (30) NOT NULL
   vista_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.pkvistaupdate
   --   ON ppsnepl.epl_vista_update (vista_update_status_id);
   ON ppsnepl.epl_vista_update (vista_update_id);
--   TABLESPACE ppsnepl_data;

CREATE INDEX ppsnepl.idxvistastatusid
  -- ON ppsnepl.epl_vista_update (status_id_fk);
   ON ppsnepl.epl_vista_update (vista_status_id_fk);
--   TABLESPACE ppsnepl_data;

CREATE UNIQUE INDEX ppsnepl.pkvistastatus
   --   ON ppsnepl.epl_status (status_id)
  ON ppsnepl.epl_vista_status (vista_status_id);
--   TABLESPACE ppsnepl_data;

GRANT SELECT ON ppsnepl.epl_vista_update TO ppsnepl_read_only_role;
GRANT SELECT ON ppsnepl.epl_vista_status TO ppsnepl_read_only_role;

ALTER TABLE ppsnepl.epl_vista_update ADD (
  CONSTRAINT pkvistaupdate
  PRIMARY KEY
  (vista_update_id)
  USING INDEX ppsnepl.pkvistaupdate
  ENABLE VALIDATE);

ALTER TABLE ppsnepl.epl_vista_status ADD (
  CONSTRAINT pkvistastatus
  PRIMARY KEY
  (vista_status_id)
  USING INDEX ppsnepl.pkvistastatus
  ENABLE VALIDATE);

ALTER TABLE ppsnepl.epl_vista_update ADD (
  CONSTRAINT fk_vistaupdate_vistastatus
  FOREIGN KEY (vista_status_id_fk)
  REFERENCES ppsnepl.epl_vista_status (vista_status_id));
