-- ABS-6324
--ERP_I_RECEIVE_POL table
CREATE TABLE ERP_I_RECEIVE_POL
(
  TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL,
  PURCHASEORDERNUMBER NUMBER NOT NULL,
  PRODUCTCODE         VARCHAR2(12) NOT NULL,
  SIZECODE            VARCHAR2(12) NOT NULL,
  REQUESTEDGRADE      VARCHAR2(1) NOT NULL,
  QUANTITYRECEIVED    NUMBER DEFAULT 1 NOT NULL,
  RECEIVEDDATE        DATE NOT NULL,
  PRIMARYID           VARCHAR2(24) NULL,
  REASONCODE          VARCHAR2(6) NOT NULL
);

COMMENT ON COLUMN ERP_I_RECEIVE_POL.PURCHASEORDERNUMBER
  IS 'Must be an existing PO number';
COMMENT ON COLUMN ERP_I_RECEIVE_POL.QUANTITYRECEIVED
  IS 'When primary ID is filled it must be 1';

ALTER TABLE ERP_I_RECEIVE_POL
  ADD CONSTRAINT PK_ERP_I_RECEIVE_POL PRIMARY KEY (TRANSFER_ID);

--ERP_I_RECEIVE_POL_LOG table
CREATE TABLE ERP_I_RECEIVE_POL_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID1_1 CHECK (TRANSFER_STATUS IN (0, 1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL CONSTRAINT PROCESS_STATUS_VALID1_1 CHECK (PROCESS_STATUS IN (0, 1)),
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);

COMMENT ON COLUMN ERP_I_RECEIVE_POL_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 
1 = read correctly
';

COMMENT ON COLUMN ERP_I_RECEIVE_POL_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 
1 = process stopped with errors
';

ALTER TABLE ERP_I_RECEIVE_POL_LOG
  ADD CONSTRAINT PK_ERP_I_RECEIVE_POL_LOG PRIMARY KEY (TRANSFER_ID);

ALTER TABLE ERP_I_RECEIVE_POL_LOG
  ADD CONSTRAINT FK_ERP_I_RECEIVE_POL_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_RECEIVE_POL (TRANSFER_ID) ON DELETE CASCADE;
