DROP table ppsnepl.epl_rematch_suggestion;
DROP table ppsnepl.epl_match_type;

CREATE TABLE ppsnepl.epl_rematch_suggestion
(
   epl_rematch_suggestion_id       NUMBER (38, 0) NOT NULL
  ,old_ien                         VARCHAR2 (50) NOT NULL
  ,new_ien                         VARCHAR2 (50) 
  ,match_type_id_fk                NUMBER (38, 0) NOT NULL
  ,ndc_update_file_fk              NUMBER (30, 0) 
  ,match_scope                     VARCHAR2 (50)
  ,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_match_type
(
   match_type_id           NUMBER (38, 0) NOT NULL
  ,match_name         	   VARCHAR (200)
  ,match_desc          	   VARCHAR (1000)
  ,created_by          	   VARCHAR (50) NOT NULL
  ,created_dtm         	   TIMESTAMP NOT NULL
  ,last_modified_by        VARCHAR (50)
  ,last_modified_dtm   	   TIMESTAMP
);

CREATE UNIQUE INDEX ppsnepl.pkeplrematchsuggestion
   ON ppsnepl.epl_rematch_suggestion (epl_rematch_suggestion_id);
--   TABLESPACE ppsnepl_data;

CREATE INDEX ppsnepl.idxmatch_type_id
   ON ppsnepl.epl_rematch_suggestion (match_type_id_fk);
--   TABLESPACE ppsnepl_data;

CREATE INDEX ppsnepl.idxndc_update_file
   ON ppsnepl.epl_rematch_suggestion (ndc_update_file_fk);
--   TABLESPACE ppsnepl_data;

CREATE UNIQUE INDEX ppsnepl.pkmatch_type
   ON ppsnepl.epl_match_type (match_type_id );
--   TABLESPACE ppsnepl_data;

ALTER TABLE ppsnepl.epl_rematch_suggestion ADD (
  CONSTRAINT eplrematchsuggestion_pk
  PRIMARY KEY
  (epl_rematch_suggestion_id)
  USING INDEX ppsnepl.pkeplrematchsuggestion
  ENABLE VALIDATE);
  
ALTER TABLE ppsnepl.epl_match_type ADD (
  CONSTRAINT eplmatchtype_pk
  PRIMARY KEY
  (match_type_id )
  USING INDEX ppsnepl.pkmatch_type
  ENABLE VALIDATE);

ALTER TABLE ppsnepl.epl_rematch_suggestion
  ADD CONSTRAINT fk_match_type
  FOREIGN KEY (match_type_id_fk)
  REFERENCES ppsnepl.epl_match_type (match_type_id);

ALTER TABLE ppsnepl.epl_rematch_suggestion
  ADD CONSTRAINT fk_ndc_update_file
  FOREIGN KEY (ndc_update_file_fk)
  REFERENCES ppsnepl.epl_ndf_update_file(ndf_update_file_id);


