--ABS-143101
CREATE TABLE EDI_I_RECEIVE_POL
(
  TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL,
  PURCHASEORDERNUMBER NUMBER NOT NULL,
  WORKORDERNUMBER     NUMBER NOT NULL,
  WEARERNUMBER        NUMBER NOT NULL,
  PRODUCTCODE         VARCHAR2(12) NOT NULL,
  SIZECODE            VARCHAR2(12) NOT NULL,
  PRIMARYID           VARCHAR2(24) NOT NULL,
  TIMESTAMP           DATE DEFAULT SYSDATE NOT NULL
);
CREATE INDEX INDX_EDI_I_RECEIVE_POL_TRANS ON EDI_I_RECEIVE_POL (TRANSFER_STATUS);
ALTER TABLE EDI_I_RECEIVE_POL
  ADD CONSTRAINT PK_EDI_I_RECEIVE_POL PRIMARY KEY (TRANSFER_ID);
  
CREATE TABLE EDI_I_RECEIVE_POL_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 EDI_I_RECEIVE_POL_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN EDI_I_RECEIVE_POL_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE EDI_I_RECEIVE_POL_LOG
  ADD CONSTRAINT PK_EDI_I_RECEIVE_POL_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE EDI_I_RECEIVE_POL_LOG
  ADD CONSTRAINT FK_EDI_I_RECEIVE_POL_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES EDI_I_RECEIVE_POL (TRANSFER_ID) ON DELETE CASCADE;
ALTER TABLE EDI_I_RECEIVE_POL_LOG
  ADD CONSTRAINT EDI_I_PROCESS_STATUS_VALID1_1
  CHECK (PROCESS_STATUS IN (0, 1));
ALTER TABLE EDI_I_RECEIVE_POL_LOG
  ADD CONSTRAINT EDI_I_TRANSFER_STATUS_VALID1_1
  CHECK (TRANSFER_STATUS IN (0, 1));