--ABS-72207
CREATE TABLE ERP_I_CONSUMPLOGISTICS
(
TRANSFER_ID NUMBER NOT NULL,
TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL 
 CONSTRAINT CHK_ERP_CONSUMPLOG_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
CUSTOMERNUMBER NUMBER NOT NULL, 
DEPARTMENTNUMBER NUMBER(6) NOT NULL, 
CONSUMPTIONPOINTNUMBER NUMBER NOT NULL, 
PRODUCTCODE VARCHAR2(12) NOT NULL, 
SIZECODE VARCHAR2(12) NOT NULL, 
FINISHINGMETHODCODE VARCHAR2(6) NOT NULL, 
SEASONTYPECODE VARCHAR2(6) NULL, 
SEASONCODE VARCHAR2(6) NULL, 
DATEACTIVE DATE NOT NULL, 
DATEINACTIVE DATE NOT NULL, 
DELIVERYSCHEMECODE VARCHAR2(6) NULL, 
ORDERINGMETHOD NUMBER NULL, 
COUNTSEQUENCENUMBER NUMBER NULL, 
QUANTITY_MO NUMBER NULL, 
QUANTITY_TU NUMBER NULL, 
QUANTITY_WE NUMBER NULL, 
QUANTITY_TH NUMBER NULL, 
QUANTITY_FR NUMBER NULL, 
QUANTITY_SA NUMBER NULL, 
QUANTITY_SU NUMBER NULL, 
STOCKCOUNT_SEQUENCE NUMBER NULL,
STOCKCOUNTQTY_MO NUMBER NULL, 
STOCKCOUNTQTY_TU NUMBER NULL, 
STOCKCOUNTQTY_WE NUMBER NULL, 
STOCKCOUNTQTY_TH NUMBER NULL, 
STOCKCOUNTQTY_FR NUMBER NULL, 
STOCKCOUNTQTY_SA NUMBER NULL, 
STOCKCOUNTQTY_SU NUMBER NULL, 
HOLIDAYUSEAGEFACTOR NUMBER(15,2) NULL, 
RENTALQUANTITY NUMBER NULL, 
CIRCULATINGINVENTORY NUMBER NULL, 
LOCATION VARCHAR2(6) NULL, 
REMARKFORPACKINGINSTRUCTIONS VARCHAR2(80) NULL, 
QTYFREEOFCHARGE NUMBER NULL, 
CONTRACTPERIOD NUMBER NULL, 
PROLONGATIONPERIOD NUMBER NULL, 
CANCELADVANCEPERIOD NUMBER NULL, 
FREEQTYFREQUENCY NUMBER(1) NULL, 
DELIVERYPERCENTAGE NUMBER NULL, 
BILLINGPERCENTAGE NUMBER NULL,
TIMESTAMP DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_CONSUMPLOGISTICS.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
ALTER TABLE ERP_I_CONSUMPLOGISTICS
 ADD CONSTRAINT PK_ERP_I_CONSUMPTPNT PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIF1ERP_I_CONSUMPTPNT ON ERP_I_CONSUMPLOGISTICS(TRANSFER_STATUS);
ALTER TABLE ERP_I_CONSUMPLOGISTICS
  ADD CONSTRAINT CONSTR_ERP_I_CONSUMPTPNT UNIQUE (CUSTOMERNUMBER, DEPARTMENTNUMBER, CONSUMPTIONPOINTNUMBER, PRODUCTCODE, SIZECODE, FINISHINGMETHODCODE, TRANSFER_ID);
ALTER TABLE ERP_I_CONSUMPLOGISTICS
  ADD CONSTRAINT CHK_ERP_CONSUMPLOG_V2
  CHECK (DATEACTIVE <= DATEINACTIVE);

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