-- ABS-83278 / ABS-89103

-------------CLEANCOUNTDELIVERY----------------------

ALTER TABLE CLEANCOUNTDELIVERY ADD (STERILIZATIONRUN_ID NUMBER NULL);

CREATE INDEX XIF8CLEANCOUNTDELIVERY ON CLEANCOUNTDELIVERY (STERILIZATIONRUN_ID ASC);

DROP INDEX XAK1CLEANCOUNTDELIVERY;

CREATE UNIQUE INDEX XAK1CLEANCOUNTDELIVERY ON CLEANCOUNTDELIVERY (DISTRIBUTIONPOINT_ID ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, FINISHINGMETHOD_ID ASC, PACKINGNOTE_ID ASC, DELIVERYDATE ASC, WORKSTATION_ID ASC, CLEANROOMBATCH_ID ASC, NVL(STERILIZATIONRUN_ID,0) ASC );

ALTER TABLE CLEANCOUNTDELIVERY
  ADD CONSTRAINT CLEANCOUNTDELIVERY_STRRUN FOREIGN KEY (STERILIZATIONRUN_ID)
  REFERENCES STERILIZATIONRUN (STERILIZATIONRUN_ID);

CREATE OR REPLACE PROCEDURE TEMPCOPYSTERILIZATION IS
DUMMY number := 0;
STMT varchar2(10000);
BEGIN
  SELECT COUNT(1) INTO DUMMY FROM USER_TAB_COLUMNS
  WHERE UPPER(TABLE_NAME) = 'CLEANROOMBATCHCOUNTCONTENT' AND UPPER(COLUMN_NAME) = 'STERILIZATIONRUN_ID';
  IF (DUMMY > 0) THEN
  STMT :='MERGE INTO CLEANCOUNTDELIVERY CCD
    USING (SELECT CCC.STERILIZATIONRUN_ID, CD.CLEANCOUNTDELIVERY_ID
      FROM CLEANROOMBATCHCOUNTCONTENT CCC,
          CLEANROOMBATCHCUSTOMER     CBC,
          CLEANCOUNTDELIVERY         CD
      WHERE CCC.CLEANROOMBATCHCUSTOMER_ID = CBC.CLEANROOMBATCHCUSTOMER_ID
      AND CBC.CLEANROOMBATCH_ID = CD.CLEANROOMBATCH_ID
      AND CBC.DISTRIBUTIONPOINT_LINK_ID = CD.DISTRIBUTIONPOINT_ID
      AND CCC.PRODUCT_LINK_ID = CD.PRODUCT_ID
      AND CCC.SIZEDEFINITION_LINK_ID = CD.SIZEDEFINITION_ID
      AND CCC.FINISHINGMETHOD_ID = CD.FINISHINGMETHOD_ID
      AND CCC.STERILIZATIONRUN_ID IS NOT NULL) TT
    ON (CCD.CLEANCOUNTDELIVERY_ID = TT.CLEANCOUNTDELIVERY_ID)
  WHEN MATCHED THEN
  UPDATE SET CCD.STERILIZATIONRUN_ID = TT.STERILIZATIONRUN_ID';
  EXECUTE IMMEDIATE(STMT);
  COMMIT;
  END IF;
END TEMPCOPYSTERILIZATION;
/
EXECUTE TEMPCOPYSTERILIZATION;

DROP PROCEDURE TEMPCOPYSTERILIZATION;

------CLEANROOMBATCHCOUNTCONTENT-----------

DROP INDEX XAK1CLEANROOMBATCHCOUNTCONTENT;

CREATE UNIQUE INDEX XAK1CLEANROOMBATCHCOUNTCONTENT ON CLEANROOMBATCHCOUNTCONTENT (PRODUCT_LINK_ID  ASC, FINISHINGMETHOD_ID ASC, SIZEDEFINITION_LINK_ID ASC, CLEANROOMBATCHCUSTOMER_ID ASC, CUSTOMERCONTAINER ASC);

ALTER TABLE CLEANROOMBATCHCOUNTCONTENT DROP COLUMN STERILIZATIONRUN_ID;
