--ABS-128259
CREATE TABLE ERP_I_DELIVERYVARIATIONS
(
  TRANSFER_ID            NUMBER NOT NULL,
  TRANSFER_STATUS        NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_I_DELIVERYVARIATIONS_TS CHECK (TRANSFER_STATUS IN (-1,0,1)),
  DELIVERYDATE           DATE DEFAULT TRUNC(SYSDATE) NOT NULL,
  CUSTOMERNUMBER         NUMBER NOT NULL,
  DEPARTMENTNUMBER       NUMBER NOT NULL,
  CONSUMPTIONPOINTNUMBER NUMBER NOT NULL,
  PRODUCTCODE            VARCHAR2(12) NOT NULL,
  SIZECODE               VARCHAR2(12) NOT NULL,
  FINISHINGMETHODCODE    VARCHAR2(6) NOT NULL,
  AMOUNT                 NUMBER NOT NULL,
  REFERENCE              VARCHAR2(30) NULL,
  REMARK                 VARCHAR2(1000) NULL,
  REASONCODE             VARCHAR2(6) NULL,
  TIMESTAMP              DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_DELIVERYVARIATIONS.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_DELIVERYVARIATIONS.CUSTOMERNUMBER
 IS 'Must be an existing customer number';
ALTER TABLE ERP_I_DELIVERYVARIATIONS
  ADD CONSTRAINT PK_ERP_I_DELIVERYVARIATIONS PRIMARY KEY (TRANSFER_ID);
CREATE INDEX INDX1_ERP_I_DELIVERYVARIATIONS ON ERP_I_DELIVERYVARIATIONS (TRANSFER_STATUS); 
CREATE INDEX INDX2_ERP_I_DELIVERYVARIATIONS ON ERP_I_DELIVERYVARIATIONS (DELIVERYDATE);
CREATE INDEX INDX3_ERP_I_DELIVERYVARIATIONS ON ERP_I_DELIVERYVARIATIONS (CUSTOMERNUMBER, DEPARTMENTNUMBER, CONSUMPTIONPOINTNUMBER, PRODUCTCODE, SIZECODE);


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