-- ABS-32100

--------- PLPCFIXEDWEARERS ---------
DROP TABLE PLPCFIXEDWEARERS CASCADE CONSTRAINTS;


CREATE TABLE PLPCFIXEDWEARERS (
  PLPCFIXEDWEARERS_ID NUMBER NOT NULL,	
  PRODUCTLISTPERCUSTOMER_ID NUMBER NOT NULL,
  COSTCENTER_ID NUMBER NOT NULL,
  NROFWEARERS NUMBER NOT NULL CONSTRAINT NROFWEARERS_VALID CHECK (NROFWEARERS BETWEEN 0 AND 9999),
  NROFCHANGES NUMBER NOT NULL CONSTRAINT NROFCHANGES_VALID CHECK (NROFCHANGES BETWEEN 0 AND 9999),
  SYSTEMUSER_ID NUMBER NOT NULL,
  TIMESTAMP DATE NOT NULL);
  
ALTER TABLE PLPCFIXEDWEARERS ADD CONSTRAINT XPKPLPCFIXEDWEARERS PRIMARY KEY (PLPCFIXEDWEARERS_ID);

CREATE UNIQUE INDEX XAK1PLPCFIXEDWEARERS ON PLPCFIXEDWEARERS (COSTCENTER_ID, NROFWEARERS, NROFCHANGES);

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) ON DELETE CASCADE;
