--ABS-138492
CREATE TABLE ERP_U_ROUTESTOPACTIVEDATE
(
  TRANSFER_ID           NUMBER NOT NULL,
  TRANSFER_STATUS       NUMBER DEFAULT 0 NOT NULL,
  PLANTCODE             VARCHAR2(6) NOT NULL,
  DRIVINGDAY            NUMBER NOT NULL,
  ROUTENUMBER           NUMBER NOT NULL,
  STOPNUMBER            NUMBER NOT NULL,
  CUSTOMERNUMBER        NUMBER NOT NULL,
  DELIVERYPOINT         VARCHAR2(12),
  CURRENT_DATEACTIVE    DATE NOT NULL,
  NEW_DATEACTIVE        DATE NOT NULL,
  TIMESTAMP             DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_U_ROUTESTOPACTIVEDATE.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
ALTER TABLE ERP_U_ROUTESTOPACTIVEDATE
  ADD CONSTRAINT PK_ERP_U_ROUTESTOPACTIVEDATE PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIF1ERP_U_ROUTESTOPACTIVEDATE ON ERP_U_ROUTESTOPACTIVEDATE (TRANSFER_STATUS);
CREATE INDEX XIF2ERP_U_ROUTESTOPACTIVEDATE ON ERP_U_ROUTESTOPACTIVEDATE (PLANTCODE,DRIVINGDAY,ROUTENUMBER,STOPNUMBER,CUSTOMERNUMBER,DELIVERYPOINT);
ALTER TABLE ERP_U_ROUTESTOPACTIVEDATE ADD CONSTRAINT CHK_ERP_U_ROUTESTOPACTIVEDATE_V1 CHECK (TRANSFER_STATUS IN (-1,0,1));

CREATE TABLE ERP_U_ROUTESTOPACTIVEDATE_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 ERP_U_ROUTESTOPACTIVEDATE_LOG.TRANSFER_STATUS IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_U_ROUTESTOPACTIVEDATE_LOG.PROCESS_STATUS IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_U_ROUTESTOPACTIVEDATE_LOG ADD CONSTRAINT PK_ERP_U_ROUTESTOPACTIVEDATE_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_U_ROUTESTOPACTIVEDATE_LOG ADD CONSTRAINT FK_ERP_U_ROUTESTOPACTIVEDATE_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_U_ROUTESTOPACTIVEDATE (TRANSFER_ID) ON DELETE CASCADE;
ALTER TABLE ERP_U_ROUTESTOPACTIVEDATE_LOG ADD CONSTRAINT CHK_ERP_U_ROUTESTOPACTIVEDATE_LOG_V1 CHECK (TRANSFER_STATUS IN (-1,0,1));
