--ABS-98595
ALTER TABLE ERP_I_CUSTOMER ADD (COMMONCUSTOMERNUMBER NUMBER NULL,
                                RETURNLINEN NUMBER(1) NULL
 CONSTRAINT CHK_ERP_I_CUST_RETLINEN CHECK (RETURNLINEN IN (0,1,2,3,4,5,6,7,8)),
                                SERVICETEAMCODE VARCHAR2(6) NULL,
                                RETURNGARMENTS NUMBER(1) NULL
 CONSTRAINT CHK_ERP_I_CUST_RETGARMT CHECK (RETURNGARMENTS IN (0,1,2,3,4,5,6,7,8)),
                                CUSTOMERMARKINGGARMENT VARCHAR2(30) NULL,
                                CODELABELLAYOUT VARCHAR2(6) NULL,
                                NAMELABELLAYOUT VARCHAR2(6) NULL,
                                GARMENTINSURANCE NUMBER(1) NULL
 CONSTRAINT CHK_ERP_I_CUST_GARMINSUR CHECK (GARMENTINSURANCE IN (0,1,2,3,4,5,6,7)),
                                INVOICECOPIES NUMBER(1) NULL
 CONSTRAINT CHK_ERP_I_CUST_INVCOPIES CHECK (INVOICECOPIES IN (1,2,3,4,5,6,7,8,9)),
                                BILLINGCOMMENTINVOICE VARCHAR2(300) NULL,
                                BILLINGCOMMENTSTATEMENT VARCHAR2(300) NULL,
                                PAYBYCUSTOMER NUMBER NULL,
                                PAYBYCUSTOMERNUMBER NUMBER NULL,
                                COMMUNICATIONMETHOD VARCHAR2(6) NULL,
                                DELIVERYNOTELAYOUT VARCHAR2(6) NULL,
                                SENDSTATEMENTBYEMAIL VARCHAR2(1) NULL
 CONSTRAINT CHK_ERPI_CUS_SNDSTATBMAIL CHECK (SENDSTATEMENTBYEMAIL IN ('Y','N')));
ALTER TABLE ERP_I_CUSTOMER ADD CONSTRAINT CHK_ERP_I_CUST_PAYBY 
CHECK ((NVL(PAYBYCUSTOMER,0) = 0) OR (PAYBYCUSTOMER != 0 AND PAYBYCUSTOMERNUMBER IS NULL));

CREATE TABLE ERP_I_CUSTOMERROUTESTOP
(
TRANSFER_ID           NUMBER NOT NULL,
TRANSFER_STATUS       NUMBER DEFAULT 0 NOT NULL 
 CONSTRAINT CHK_ERP_ICUSTSTOPNR_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
PLANTCODE             VARCHAR2(6) NOT NULL,
DRIVINGDAY            NUMBER NOT NULL,
DEPOTCODE             VARCHAR2(12) NULL,
ROUTENUMBER           NUMBER NOT NULL,
ROUTEDESCRIPTION      VARCHAR2(30) NULL,
TYPEOFMERCHANDISE     NUMBER DEFAULT 2 NOT NULL
 CONSTRAINT CHK_ERP_ICUSTSTOPNR_V2 CHECK (TYPEOFMERCHANDISE IN (1,2,5)),
STOPNUMBER            NUMBER NOT NULL,
CUSTOMERNUMBER        NUMBER NOT NULL,
DELIVERYPOINT         VARCHAR2(12) NULL,
DATEACTIVE            DATE NOT NULL,
DATEINACTIVE          DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
DELIVERYTYPE          VARCHAR2(1) NOT NULL
 CONSTRAINT CHK_ERP_ICUSTSTOPNR_V4 CHECK (DELIVERYTYPE IN ('D','B','P')),
STOPTYPE              NUMBER DEFAULT 3 NOT NULL
 CONSTRAINT CHK_ERP_ICUSTSTOPNR_V5 CHECK (STOPTYPE IN (1,2,3,4)),
SOILTICKETS           NUMBER DEFAULT 0 NOT NULL,
DELIVERYSCHEMECODE    VARCHAR2(6) NOT NULL,
RETURNROUTENUMBER     NUMBER NOT NULL,
RETURNROUTEDAY        NUMBER NOT NULL,
ACTIVE                VARCHAR2(1) DEFAULT 'Y' NOT NULL
 CONSTRAINT CHK_ERP_ICUSTSTOPNR_V6 CHECK (ACTIVE IN ('Y','N')),
ROUTELISTINSTRUCTIONS VARCHAR2(1000) NULL, 
TIMESTAMP             DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_CUSTOMERROUTESTOP.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CUSTOMERROUTESTOP.TYPEOFMERCHANDISE
  IS  '1=linen, 2=garments(+linen), 5=residential/domestic';
COMMENT ON COLUMN ERP_I_CUSTOMERROUTESTOP.DELIVERYTYPE
  IS  'D=Delivery, B=Delivery+Billing, P=Pick up';
COMMENT ON COLUMN ERP_I_CUSTOMERROUTESTOP.STOPTYPE
  IS  '1=Bring, 2=Get, 3=Mixed, 4=Incidental';
COMMENT ON COLUMN ERP_I_CUSTOMERROUTESTOP.SOILTICKETS
  IS  'Number of soiltickets default 0';
ALTER TABLE ERP_I_CUSTOMERROUTESTOP
 ADD CONSTRAINT PK_ERP_I_CUSTSTOPNUMBER PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIF1ERP_I_CUSTOMERROUTESTOP ON ERP_I_CUSTOMERROUTESTOP(TRANSFER_STATUS);
ALTER TABLE ERP_I_CUSTOMERROUTESTOP
  ADD CONSTRAINT UK_ERP_I_CUSTSTOPNUMBER UNIQUE (TRANSFER_ID, PLANTCODE, DRIVINGDAY, ROUTENUMBER, STOPNUMBER, CUSTOMERNUMBER);
ALTER TABLE ERP_I_CUSTOMERROUTESTOP ADD CONSTRAINT CHK_ERP_ICUSTSTOPNR_V3 CHECK (DATEINACTIVE>=DATEACTIVE);

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