-- ABS-32100

--------- PRODUCTLISTPERCUSTOMER ---------

ALTER TABLE PRODUCTLISTPERCUSTOMER ADD (BM1FIXEDWEARERS VARCHAR2(1) DEFAULT 'N' NOT NULL 
										CONSTRAINT YN_VALIDATIONFIELD300 CHECK (BM1FIXEDWEARERS IN  ('Y', 'N')));

--------- PLPCFIXEDWEARERS ---------

CREATE TABLE PLPCFIXEDWEARERS (
  PRODUCTLISTPERCUSTOMER_ID NUMBER NOT NULL,
  COSTCENTER_ID NUMBER NOT NULL,
  NROFWEARERS NUMBER NOT NULL,
  NROFCHANGES NUMBER NOT NULL,
  SYSTEMUSER_ID NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP DATE DEFAULT SYSDATE NOT NULL);
  
ALTER TABLE PLPCFIXEDWEARERS ADD CONSTRAINT XPKPLPCFIXEDWEARERS PRIMARY KEY (PRODUCTLISTPERCUSTOMER_ID, COSTCENTER_ID);

CREATE INDEX XIF1PLPCFIXEDWEARERS ON PLPCFIXEDWEARERS (PRODUCTLISTPERCUSTOMER_ID ASC);

CREATE INDEX XIF2PLPCFIXEDWEARERS ON PLPCFIXEDWEARERS (COSTCENTER_ID ASC);

ALTER TABLE PLPCFIXEDWEARERS ADD CONSTRAINT PLPCFIXEDWEARERS_PLPCID FOREIGN KEY (PRODUCTLISTPERCUSTOMER_ID) 
REFERENCES  PRODUCTLISTPERCUSTOMER (PRODUCTLISTPERCUSTOMER_ID) ON DELETE CASCADE;

ALTER TABLE PLPCFIXEDWEARERS ADD CONSTRAINT PLPCFIXEDWEARERS_COSTCENTER FOREIGN KEY (COSTCENTER_ID) 
REFERENCES  COSTCENTER (COSTCENTER_ID);

ALTER TABLE PLPCFIXEDWEARERS ADD CONSTRAINT NROFWEARERS_CHECK CHECK (NROFWEARERS BETWEEN 0 AND 9999);

ALTER TABLE PLPCFIXEDWEARERS ADD CONSTRAINT NROFCHANGES_CHECK CHECK (NROFCHANGES BETWEEN 0 AND 9999);
