-- Create table
CREATE TABLE ERP_I_REPLACEMENT_WO
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL,
  BUSINESSUNIT    VARCHAR2(6) NOT NULL,
  PRIMARYID       VARCHAR2(24) NOT NULL,
  RAGREASONCODE   VARCHAR2(6) NOT NULL,
  WORKORDERNUMBER NUMBER NOT NULL,
  CUSTOMERNUMBER  NUMBER NOT NULL,
  ENTRYDATE       DATE NOT NULL,
  REMARK          VARCHAR2(1000) NULL,
  REASONCODE      VARCHAR2(6) NOT NULL,
  REQUESTEDGRADE  VARCHAR2(1) NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);
CREATE INDEX INDX_ERP_I_REPLACEMENT_WO_TRANS ON ERP_I_REPLACEMENT_WO (TRANSFER_STATUS);
CREATE INDEX XIE1_ERP_I_REPLACEMENT_WO ON ERP_I_REPLACEMENT_WO (BUSINESSUNIT, PRIMARYID, WORKORDERNUMBER);
ALTER TABLE ERP_I_REPLACEMENT_WO ADD CONSTRAINT PK_ERP_I_REPLACEMENT_WO PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_REPLACEMENT_WO ADD CONSTRAINT CHK_ERP_I_REPLACEMENT_WO_STATUS CHECK (TRANSFER_STATUS IN (-1,0,1));
COMMENT ON COLUMN ERP_I_REPLACEMENT_WO.TRANSFER_STATUS IS '-1 = staging, 0 = ready to be read, 1 = read correctly';

CREATE TABLE ERP_I_REPLACEMENT_WO_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_REPLACEMENT_WO_LOG.TRANSFER_STATUS IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_REPLACEMENT_WO_LOG.PROCESS_STATUS IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_I_REPLACEMENT_WO_LOG ADD CONSTRAINT PK_ERP_I_REPLACEMENT_WO_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_REPLACEMENT_WO_LOG ADD CONSTRAINT FK_ERP_I_REPLACEMENT_WO_LOG FOREIGN KEY (TRANSFER_ID) REFERENCES ERP_I_REPLACEMENT_WO (TRANSFER_ID) ON DELETE CASCADE;
ALTER TABLE ERP_I_REPLACEMENT_WO_LOG ADD CONSTRAINT CHK_ERP_I_REPLM_WO_LOG_STATUS CHECK (TRANSFER_STATUS IN (-1,0,1));
