--ABS-16940

----------------------BUPREWASHDAYSDEFINITION---------------------- 
CREATE SEQUENCE SEQ_BUPREWASHDAYSDEFINITION
START WITH 1
INCREMENT BY 1;

CREATE TABLE BUPREWASHDAYSDEFINITION
(
  BUPREWASHDAYSDEFINITION_ID NUMBER NOT NULL,
  BUSINESSUNIT_ID            NUMBER NOT NULL,
  STARTDATE                  DATE   DEFAULT SYSDATE NOT NULL,
  PREWASH_DAY1               NUMBER DEFAULT 0 NOT NULL,
  PREWASH_DAY2               NUMBER DEFAULT 0 NOT NULL,
  PREWASH_DAY3               NUMBER DEFAULT 0 NOT NULL,
  PREWASH_DAY4               NUMBER DEFAULT 0 NOT NULL,
  PREWASH_DAY5               NUMBER DEFAULT 0 NOT NULL,
  PREWASH_DAY6               NUMBER DEFAULT 0 NOT NULL,
  PREWASH_DAY7               NUMBER DEFAULT 0 NOT NULL,
  SYSTEMUSER_ID              NUMBER NOT NULL,
  TIMESTAMP                  DATE NOT NULL
);

ALTER TABLE BUPREWASHDAYSDEFINITION
  ADD CONSTRAINT PKBUPREWASHDAYSDEFINITION PRIMARY KEY (BUPREWASHDAYSDEFINITION_ID);
  
CREATE UNIQUE INDEX XAK1BUPREWASHDAYSDEFINITION ON BUPREWASHDAYSDEFINITION (BUSINESSUNIT_ID, STARTDATE);

CREATE INDEX XIE1BUPREWASHDAYSDEFINITION ON BUPREWASHDAYSDEFINITION (BUSINESSUNIT_ID);

ALTER TABLE BUPREWASHDAYSDEFINITION
  ADD CONSTRAINT BUPREWASHDEF_BU FOREIGN KEY (BUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE CASCADE;
  
  
----------------------PRODUCT---------------------- 

ALTER TABLE PRODUCT ADD (ALLOWPREWASH NUMBER DEFAULT 0 NOT NULL CONSTRAINT ALLOWPREWASH_VALID1_1 CHECK (ALLOWPREWASH in (0,1,2)));
 
COMMENT ON COLUMN PRODUCT.ALLOWPREWASH
  IS '0 - Not Allowed
1 - Allowed, default 
2 - Allowed, fixed 
';

----------------------PRODUCTLISTPERCUSTOMER---------------------- 
ALTER TABLE PRODUCTLISTPERCUSTOMER ADD (ALLOWPREWASH NUMBER DEFAULT 0 NOT NULL CONSTRAINT ALLOWPREWASH_VALID2_1 CHECK (ALLOWPREWASH IN (0,1)));

COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.ALLOWPREWASH
  IS '0 – Not allowed, 1 – Allowed';


----------------------WORKORDERLINE---------------------- 
ALTER TABLE WORKORDERLINE ADD (PREWASH NUMBER DEFAULT 0 NOT NULL CONSTRAINT PREWASH_VALID1_1 CHECK (PREWASH IN (0,1)));

COMMENT ON COLUMN WORKORDERLINE.PREWASH
  IS '0 - No preWash nedeed, 1 - Prewash needed';