--- ABS-14700 - UNIQUEITEMSPERSTERILIZATIONRUN table ---

---UNIQUEITEMSPERSTERILIZATIONRUN table---
CREATE TABLE UNIQUEITEMSPERSTERILIZATIONRUN
(
  STERILIZATIONRUN_ID NUMBER NOT NULL,
  UNIQUEITEM_ID       NUMBER NOT NULL,
  PRODUCT_ID          NUMBER NOT NULL,
  SIZEDEFINITION_ID   NUMBER NOT NULL,
  FINISHINGMETHOD_ID  NUMBER NOT NULL,
  SYSTEMUSER_ID       NUMBER NOT NULL,
  TIMESTAMP           DATE NOT NULL
)
;

ALTER TABLE UNIQUEITEMSPERSTERILIZATIONRUN
  ADD CONSTRAINT XPKUIPERSTERILIZATIONRUN PRIMARY KEY (STERILIZATIONRUN_ID, UNIQUEITEM_ID);
  
CREATE INDEX XIF1UIPERSTERILIZATIONRUN ON UNIQUEITEMSPERSTERILIZATIONRUN (STERILIZATIONRUN_ID ASC);

CREATE INDEX XIF2UIPERSTERILIZATIONRUN ON UNIQUEITEMSPERSTERILIZATIONRUN (UNIQUEITEM_ID ASC);

CREATE INDEX XIF3UIPERSTERILIZATIONRUN ON UNIQUEITEMSPERSTERILIZATIONRUN (PRODUCT_ID ASC);

CREATE INDEX XIF4UIPERSTERILIZATIONRUN ON UNIQUEITEMSPERSTERILIZATIONRUN (SIZEDEFINITION_ID ASC);

CREATE INDEX XIF5UIPERSTERILIZATIONRUN ON UNIQUEITEMSPERSTERILIZATIONRUN (FINISHINGMETHOD_ID ASC);

ALTER TABLE UNIQUEITEMSPERSTERILIZATIONRUN ADD CONSTRAINT UIPERSTERILIZATIONRUN_STERRUN FOREIGN KEY
(STERILIZATIONRUN_ID) REFERENCES STERILIZATIONRUN (STERILIZATIONRUN_ID) ON DELETE CASCADE;

ALTER TABLE UNIQUEITEMSPERSTERILIZATIONRUN ADD CONSTRAINT UIPERSTERILIZATIONRUN_UI FOREIGN KEY
(UNIQUEITEM_ID) REFERENCES UNIQUEITEM (UNIQUEITEM_ID) ON DELETE CASCADE;  

ALTER TABLE UNIQUEITEMSPERSTERILIZATIONRUN ADD CONSTRAINT UIPERSTERILIZATIONRUN_PROD FOREIGN KEY
(PRODUCT_ID) REFERENCES PRODUCT (PRODUCT_ID);  

ALTER TABLE UNIQUEITEMSPERSTERILIZATIONRUN ADD CONSTRAINT UIPERSTERILIZATIONRUN_SIZE FOREIGN KEY
(SIZEDEFINITION_ID) REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID);  

ALTER TABLE UNIQUEITEMSPERSTERILIZATIONRUN ADD CONSTRAINT UIPERSTERILIZATIONRUN_FM FOREIGN KEY
(FINISHINGMETHOD_ID) REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);  

--- TRANSACTIONTYPE table---
insert into transactiontype (transactiontype_id, factorydescription, systemuser_id, timestamp, legacytransactioncode)
values (621,'Cleanroom Charge Scan',getminsystemuser(),sysdate,null);
commit;

insert into transactiontype_desc
  (transactiontype_id, language_id, description, systemuser_id, timestamp)
  select transactiontype_id,
         getminlanguage(),
         factorydescription,
         systemuser_id,
         timestamp
    from transactiontype
   where transactiontype.transactiontype_id = 621;
commit;