CREATE TABLE ERP_O_PRODUCTLISTPERCUSTOMER
(
TRANSFER_ID  NUMBER NOT NULL ,
TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_O_PRODUCLISTPERCUSTOMER_TRANSFER_STATUS CHECK (TRANSFER_STATUS IN (-1,0,1)),
BUSINESSUNIT VARCHAR2(6) NOT NULL,
CUSTOMERNUMBER NUMBER NOT NULL,
PRODUCTLISTPERCUSTOMER_ID NUMBER NOT NULL,
PRODUCTCODE VARCHAR2(12) NOT NULL,
SIZECODE VARCHAR2(12) NOT NULL,
FINISHINGMETHODCODE VARCHAR2(6) NOT NULL,
PRODUCTDESCRIPTION VARCHAR2(30) NULL,
STARTDATE DATE NULL,
ENDDATE DATE NULL);
COMMENT ON COLUMN ERP_O_PRODUCTLISTPERCUSTOMER.TRANSFER_ID
  IS 'Will use the transfer status id from the ERP_O_PRODUCTLISTPERCUSTOMER table';
COMMENT ON COLUMN ERP_O_PRODUCTLISTPERCUSTOMER.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
ALTER TABLE ERP_O_PRODUCTLISTPERCUSTOMER ADD CONSTRAINT PK_ERP_O_PRODUCTLISTPERCUSTOMER PRIMARY KEY(TRANSFER_ID);
CREATE INDEX INDX01_ERP_O_PRODUCTLISTPERCUSTOMER ON ERP_O_PRODUCTLISTPERCUSTOMER(TRANSFER_STATUS);
CREATE INDEX INDX02_ERP_O_PRODUCTLISTPERCUSTOMER ON ERP_O_PRODUCTLISTPERCUSTOMER(CUSTOMERNUMBER,PRODUCTCODE,SIZECODE,FINISHINGMETHODCODE);


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

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