-- ABS-46884 / ABS-48178

------------- CLEANROOMBATCHPKBOX ------------- 
CREATE SEQUENCE SEQ_CLEANROOMBATCHPKBOX
START WITH 1
INCREMENT BY 1;

CREATE TABLE CLEANROOMBATCHPKBOX
( 
  CLEANROOMBATCHPKBOX_ID    NUMBER NOT NULL,
  CLEANROOMBATCHCUSTOMER_ID NUMBER NOT NULL,
  BOXNUMBER                 NUMBER NOT NULL,
  DELETED                   VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD362
                                         CHECK (DELETED IN ('Y', 'N')),
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 DATE NOT NULL
);

ALTER TABLE CLEANROOMBATCHPKBOX
  ADD CONSTRAINT XPKCLEANROOMBATCHPKBOX PRIMARY KEY (CLEANROOMBATCHPKBOX_ID);
  
CREATE INDEX XAK1CLEANROOMBATCHPKBOX ON CLEANROOMBATCHPKBOX (CLEANROOMBATCHCUSTOMER_ID ASC, BOXNUMBER ASC); 
CREATE INDEX XIF1CLEANROOMBATCHPKBOX ON CLEANROOMBATCHPKBOX (CLEANROOMBATCHCUSTOMER_ID ASC);

ALTER TABLE CLEANROOMBATCHPKBOX
  ADD CONSTRAINT CRBPKBOX_CRBCUSTOMER FOREIGN KEY (CLEANROOMBATCHCUSTOMER_ID)
  REFERENCES CLEANROOMBATCHCUSTOMER (CLEANROOMBATCHCUSTOMER_ID) ON DELETE CASCADE;


------------- CLEANROOMBATCHPKBOXQTY ------------- 
CREATE SEQUENCE SEQ_CLEANROOMBATCHPKBOXQTY
START WITH 1
INCREMENT BY 1;

CREATE TABLE CLEANROOMBATCHPKBOXQTY
(
  CLEANROOMBATCHPKBOXQTY_ID NUMBER NOT NULL,
  CLEANROOMBATCHPKBOX_ID    NUMBER NOT NULL,
  PRODUCT_LINK_ID           NUMBER NOT NULL,
  SIZEDEFINITION_LINK_ID    NUMBER NOT NULL,
  FINISHINGMETHOD_ID        NUMBER NOT NULL,
  QUANTITY                  NUMBER NOT NULL,
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 DATE NOT NULL
)
;

ALTER TABLE CLEANROOMBATCHPKBOXQTY
  ADD CONSTRAINT XPKCLEANROOMBATCHPKBOXQTY PRIMARY KEY (CLEANROOMBATCHPKBOXQTY_ID);
  
CREATE UNIQUE INDEX XAK1CLEANROOMBATCHPKBOXQTY ON CLEANROOMBATCHPKBOXQTY (CLEANROOMBATCHPKBOX_ID ASC, PRODUCT_LINK_ID ASC, SIZEDEFINITION_LINK_ID ASC, FINISHINGMETHOD_ID ASC);
CREATE INDEX XIF1CLEANROOMBATCHPKBOXQTY ON CLEANROOMBATCHPKBOXQTY (CLEANROOMBATCHPKBOX_ID ASC);
CREATE INDEX XIF2CLEANROOMBATCHPKBOXQTY ON CLEANROOMBATCHPKBOXQTY (PRODUCT_LINK_ID ASC);
CREATE INDEX XIF3CLEANROOMBATCHPKBOXQTY ON CLEANROOMBATCHPKBOXQTY (SIZEDEFINITION_LINK_ID ASC);
CREATE INDEX XIF4CLEANROOMBATCHPKBOXQTY ON CLEANROOMBATCHPKBOXQTY (FINISHINGMETHOD_ID ASC);


ALTER TABLE CLEANROOMBATCHPKBOXQTY
  ADD CONSTRAINT CRBPKBOXQTY_CRBPKBOX FOREIGN KEY (CLEANROOMBATCHPKBOX_ID)
  REFERENCES CLEANROOMBATCHPKBOX (CLEANROOMBATCHPKBOX_ID) ON DELETE CASCADE;
ALTER TABLE CLEANROOMBATCHPKBOXQTY
  ADD CONSTRAINT CRBPKBOXQTY_PRODUCT_LINK FOREIGN KEY (PRODUCT_LINK_ID)
  REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID);
ALTER TABLE CLEANROOMBATCHPKBOXQTY
  ADD CONSTRAINT CRBPKBOXQTY_FM FOREIGN KEY (FINISHINGMETHOD_ID)
  REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);
ALTER TABLE CLEANROOMBATCHPKBOXQTY
  ADD CONSTRAINT CRBPKBOXQTY_SIZEDEF FOREIGN KEY (SIZEDEFINITION_LINK_ID)
  REFERENCES SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID);
