ALTER TABLE CUSTOMER ADD (ERPCLEANROOMEXPORT VARCHAR2(1) DEFAULT 'N');
COMMENT ON COLUMN CUSTOMER.ERPCLEANROOMEXPORT IS 'Applicable for ERP CleanRoom Plants';

CREATE TABLE ERP_O_CLEANROOMCUSTOMER
( TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_O_CLEANROOMCUSTOMER_TRANSFER_STATUS CHECK(TRANSFER_STATUS IN (0,1)),
  CUSTOMERNUMBER      NUMBER NOT NULL,
  BUSINESSUNIT        VARCHAR2(6) NOT NULL,
  CUSTOMERNAME        VARCHAR2(40) NOT NULL,
  NATIONALACCOUNTCODE VARCHAR2(15) NULL,
  ROUTENUMBER         NUMBER  NULL,
  SEARCHNAME          VARCHAR2(10) NULL,
  DAY_MON             NUMBER NOT NULL CONSTRAINT CHK_ERP_O_CLEANROOMCUSTOMER_DAY_MON CHECK(DAY_MON IN (0,1)),
  DAY_TUE             NUMBER NOT NULL CONSTRAINT CHK_ERP_O_CLEANROOMCUSTOMER_DAY_TUE CHECK(DAY_TUE IN (0,1)),
  DAY_WED             NUMBER NOT NULL CONSTRAINT CHK_ERP_O_CLEANROOMCUSTOMER_DAY_WED CHECK(DAY_WED IN (0,1)),
  DAY_THU             NUMBER NOT NULL CONSTRAINT CHK_ERP_O_CLEANROOMCUSTOMER_DAY_THU CHECK(DAY_THU IN (0,1)),
  DAY_FRI             NUMBER NOT NULL CONSTRAINT CHK_ERP_O_CLEANROOMCUSTOMER_DAY_FRI CHECK(DAY_FRI IN (0,1)),
  DAY_SAT             NUMBER NOT NULL CONSTRAINT CHK_ERP_O_CLEANROOMCUSTOMER_DAY_SAT CHECK(DAY_SAT IN (0,1)),
  DAY_SUN             NUMBER NOT NULL CONSTRAINT CHK_ERP_O_CLEANROOMCUSTOMER_DAY_SUN CHECK(DAY_SUN IN (0,1)),
  VISITADDRESS        VARCHAR2(40) ,
  VISITADDRESS2       VARCHAR2(40) ,
  VISITCITY           VARCHAR2(30) NOT NULL,
  VISITSTATE          VARCHAR2(20) ,
  VISITZIPCODE        VARCHAR2(15) ,
  PHONENUMBER         VARCHAR2(30) NOT NULL,
  NUMBERDELDAYS       NUMBER(1) DEFAULT 0 NOT NULL  CONSTRAINT CHK_ERP_O_CLEANROOMCUSTOMER_NUMBERDELDAYS CHECK(NUMBERDELDAYS BETWEEN 0 AND 7),
  PREPCHARGE          VARCHAR2(1) NOT NULL CONSTRAINT CHK_ERP_O_PREPCHARGE CHECK(PREPCHARGE IN ('Y','N')),
  CUSTOMERSTATUS      VARCHAR2(1) NOT NULL CONSTRAINT CHK_ERP_O_CLEANROOMCUSTOMER_CUSTOMERSTATUS CHECK (CUSTOMERSTATUS IN ('A','I','D')),
  PURCHASE_ORDER      VARCHAR2(40),
  NONBUSINESSSTARTDATE  DATE,
  NONBUSINESSENDDATE    DATE
);

COMMENT ON COLUMN ERP_O_CLEANROOMCUSTOMER.TRANSFER_ID
  IS 'Will use the transfer status id from the ERP_O_CLEANROOMCUSTOMER table';
COMMENT ON COLUMN ERP_O_CLEANROOMCUSTOMER.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_O_CLEANROOMCUSTOMER.DAY_MON
  IS 'IF SERVICE ON MONDAY GET 1, IF NOT 0';
COMMENT ON COLUMN ERP_O_CLEANROOMCUSTOMER.DAY_TUE
  IS 'IF SERVICE ON TUESDAY GET 1, IF NOT 0';
COMMENT ON COLUMN ERP_O_CLEANROOMCUSTOMER.DAY_WED
  IS 'IF SERVICE ON WEDNESDAY GET 1, IF NOT 0';
COMMENT ON COLUMN ERP_O_CLEANROOMCUSTOMER.DAY_THU
  IS 'IF SERVICE ON THURSDAY GET 1, IF NOT 0';
COMMENT ON COLUMN ERP_O_CLEANROOMCUSTOMER.DAY_FRI
  IS 'IF SERVICE ON FRIDAY GET 1, IF NOT 0';
COMMENT ON COLUMN ERP_O_CLEANROOMCUSTOMER.DAY_SAT
  IS 'IF SERVICE ON SATURDAY GET 1, IF NOT 0';
COMMENT ON COLUMN ERP_O_CLEANROOMCUSTOMER.DAY_SUN
  IS 'IF SERVICE ON SUNDAY GET 1, IF NOT 0';
COMMENT ON COLUMN ERP_O_CLEANROOMCUSTOMER.CUSTOMERSTATUS
  IS 'A= ACTIVE CHECKED, DATE INACTIVE >= SYSDATE OR 12/31/2099.
  I= DATEINACTIVE <= SYSDATE
  D= ACTIVE BOX UNCHECKED';
COMMENT ON COLUMN ERP_O_CLEANROOMCUSTOMER.NUMBERDELDAYS
  IS 'SUM OF ACTIVE DELIVERY (D) AND BILL (B) DAYS FOR CUSTOMER IN ONE WEEK';

ALTER TABLE ERP_O_CLEANROOMCUSTOMER
ADD CONSTRAINT XPKERP_O_CLEANROOMCUSTOMER PRIMARY KEY(TRANSFER_ID);

CREATE TABLE ERP_O_CLEANROOMCUSTOMER_LOG
(
TRANSFER_ID NUMBER NOT NULL,
TRANSFER_STATUS NUMBER DEFAULT 0  NOT NULL CONSTRAINT CHK_ERP_O_CUSTOMER_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_O_CLEANROOMCUSTOMER_LOG.TRANSFER_STATUS
 IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_O_CLEANROOMCUSTOMER_LOG.PROCESS_STATUS 
IS '0 = PROCESSED CORRECTLY, 1 = PROCESS STOPPED WITH ERRORS';

ALTER TABLE ERP_O_CLEANROOMCUSTOMER_LOG
ADD CONSTRAINT XPKERP_O_CLEANROOMCUSTOMER_LOG PRIMARY KEY(TRANSFER_ID);

ALTER TABLE ERP_O_CLEANROOMCUSTOMER_LOG
ADD CONSTRAINT FK_ERP_O_CLEANROOMCUSTOMER_LOG FOREIGN KEY(TRANSFER_ID) 
REFERENCES ERP_O_CLEANROOMCUSTOMER(TRANSFER_ID) ON DELETE CASCADE;
