--ABS-98834
CREATE TABLE ERP_I_ROUTECHANGETRANSACTION
(
TRANSFER_ID              NUMBER NOT NULL,
TRANSFER_STATUS          NUMBER DEFAULT 0 NOT NULL 
 CONSTRAINT CHK_ERP_ROUTECHNGT_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
TRANSACTIONNUMBER	     NUMBER NOT NULL
 CONSTRAINT CHK_ERP_ROUTECHNGT_V2 CHECK (TRANSACTIONNUMBER IN (1,2,3,4,5,6,7)),
CHANGESTARTDATE	         DATE NOT NULL,
PLANTCODE	             VARCHAR2(6) NOT NULL,
ROUTENUMBER	             NUMBER,
DRIVINGDAY	             NUMBER,
STOPNUMBER               NUMBER,
CUSTOMERNUMBER	         NUMBER,
DEPOTCODE	             VARCHAR2(12),
DELIVERYSCHEMECODE	     VARCHAR2(6),
OLD_ROUTELASTDRIVINGDATE DATE,
NEW_ROUTENUMBER	         NUMBER,
NEW_DRIVINGDAY	         NUMBER,
NEW_STOPNUMBER	         NUMBER,
NEW_DELIVERYSCHEMECODE	 VARCHAR2(6),
TIMESTAMP	             DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_ROUTECHANGETRANSACTION.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
ALTER TABLE ERP_I_ROUTECHANGETRANSACTION
 ADD CONSTRAINT PK_ERP_I_ROUTECHNGT PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIF1ERP_I_ROUTECHNGT ON ERP_I_ROUTECHANGETRANSACTION(TRANSFER_STATUS);
CREATE INDEX XIF2ERP_I_ROUTECHNGT ON ERP_I_ROUTECHANGETRANSACTION(TRANSACTIONNUMBER,CHANGESTARTDATE,PLANTCODE, ROUTENUMBER);

CREATE TABLE ERP_I_ROUTECHANGETRANSACT_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL  CONSTRAINT CHK_ERP_I_ROUTECHNGT_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_ROUTECHANGETRANSACT_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_ROUTECHANGETRANSACT_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_I_ROUTECHANGETRANSACT_LOG
  ADD CONSTRAINT PK_ERP_I_ROUTECHNGT_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_ROUTECHANGETRANSACT_LOG
  ADD CONSTRAINT FK_ERP_I_ROUTECHNGT_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_ROUTECHANGETRANSACTION(TRANSFER_ID) ON DELETE CASCADE;