--order 502335      
     
      /*
      ACTION is CREATE Table CARTTYPE
      */

CREATE SEQUENCE SEQ_CARTTYPE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CARTTYPE (
       CARTTYPE_ID          NUMBER NOT NULL,
       CODE                 VARCHAR2(6) NOT NULL,
       FACTORYDESCRIPTION   VARCHAR2(40) NOT NULL,
       ACTIVE               VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_ValidationNew175
                                          CHECK (ACTIVE IN ('Y', 'N')),
       MAXVOLUME            NUMBER(6) DEFAULT 999999 NOT NULL 
                                   CONSTRAINT MaxVolume_Valid
                                          CHECK (MAXVOLUME BETWEEN 1 AND 999999),
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE UNIQUE INDEX XAK1CARTTYPE ON CARTTYPE
(
       CODE                           ASC
);


ALTER TABLE CARTTYPE
       ADD CONSTRAINT XPKCARTTYPE PRIMARY KEY (CARTTYPE_ID);

      /*
      ACTION is CREATE Table CARTTYPE_DESC
      */

CREATE TABLE CARTTYPE_DESC (
       CARTTYPE_ID          NUMBER NOT NULL,
       LANGUAGE_ID          NUMBER NOT NULL,
       DESCRIPTION          VARCHAR2(40) NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1CARTTYPE_DESC ON CARTTYPE_DESC
(
       CARTTYPE_ID                    ASC
);

CREATE INDEX XIF2CARTTYPE_DESC ON CARTTYPE_DESC
(
       LANGUAGE_ID                    ASC
);


ALTER TABLE CARTTYPE_DESC
       ADD CONSTRAINT XPKCARTTYPE_DESC PRIMARY KEY (CARTTYPE_ID, 
              LANGUAGE_ID);
      
      /*
      ACTION is CREATE Table CART
      */

CREATE SEQUENCE SEQ_CART
START WITH 1
INCREMENT BY 1;

CREATE TABLE CART (
       CART_ID              NUMBER NOT NULL,
       PLANT_ID             NUMBER NOT NULL,
       CARTNUMBER           NUMBER(6) NOT NULL
                                   CONSTRAINT CartNumber_Valid
                                          CHECK (CARTNUMBER BETWEEN 1 AND 999999),
       CARTTYPE_ID          NUMBER NOT NULL,
       STATUS_ID            NUMBER NOT NULL,
       LASTUSEDTIMESTAMP    DATE NULL,
       LASTWORKSTATION_ID   NUMBER NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE UNIQUE INDEX XAK1CART ON CART
(
       PLANT_ID                       ASC,
       CARTNUMBER                     ASC
);

CREATE INDEX XIF1CART ON CART
(
       PLANT_ID                       ASC
);

CREATE INDEX XIF2CART ON CART
(
       CARTTYPE_ID                    ASC
);

CREATE INDEX XIF3CART ON CART
(
       STATUS_ID                      ASC
);

CREATE INDEX XIF4CART ON CART
(
       LASTWORKSTATION_ID             ASC
);


ALTER TABLE CART
       ADD CONSTRAINT XPKCART PRIMARY KEY (CART_ID);

     
      /*
      ACTION is CREATE Table CARTUNIQUEID
      */

CREATE TABLE CARTUNIQUEID (
       CART_ID              NUMBER NOT NULL,
       PLANT_ID             NUMBER NOT NULL,
       PRIMARYID            VARCHAR2(24) NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE UNIQUE INDEX XAK1CARTUNIQUEID ON CARTUNIQUEID
(
       PLANT_ID                       ASC,
       PRIMARYID                      ASC
);

CREATE INDEX XIF2CARTUNIQUEID ON CARTUNIQUEID
(
       PLANT_ID                       ASC
);


ALTER TABLE CARTUNIQUEID
       ADD CONSTRAINT XPKCARTUNIQUEID PRIMARY KEY (CART_ID);

      
      /*
      ACTION is CREATE Table EXPIRATIONCODE
      */

CREATE SEQUENCE SEQ_EXPIRATIONCODE
START WITH 1
INCREMENT BY 1;

CREATE TABLE EXPIRATIONCODE (
       EXPIRATIONCODE_ID    NUMBER NOT NULL,
       CODE                 VARCHAR2(6) NOT NULL,
       DESCRIPTION          VARCHAR2(40) NOT NULL,
       DAYSSTERILE          NUMBER(4) NOT NULL
                                   CONSTRAINT DaysSterile_Valid
                                          CHECK (DAYSSTERILE BETWEEN 1 AND 9999),
       DAYSSTOCK            NUMBER(4) NOT NULL
                                   CONSTRAINT DaysStock_Valid
                                          CHECK (DAYSSTOCK BETWEEN 1 AND 9999),
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE UNIQUE INDEX XAK1EXPIRATIONCODE ON EXPIRATIONCODE
(
       CODE                           ASC
);


ALTER TABLE EXPIRATIONCODE
       ADD CONSTRAINT XPKEXPIRATIONCODE PRIMARY KEY (
              EXPIRATIONCODE_ID);

      
      /*
      ACTION is CREATE Table STERILIZER
      */

CREATE SEQUENCE SEQ_STERILIZER
START WITH 1
INCREMENT BY 1;

CREATE TABLE STERILIZER (
       STERILIZER_ID        NUMBER NOT NULL,
       PLANT_ID             NUMBER NOT NULL,
       CODE                 VARCHAR2(6) NOT NULL,
       ACTIVE               VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_ValidationNew176
                                          CHECK (ACTIVE IN ('Y', 'N')),
       DESCRIPTION          VARCHAR2(40) NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE UNIQUE INDEX XAK1STERILIZER ON STERILIZER
(
       PLANT_ID                       ASC,
       CODE                           ASC
);

CREATE INDEX XIF1STERILIZER ON STERILIZER
(
       PLANT_ID                       ASC
);


ALTER TABLE STERILIZER
       ADD CONSTRAINT XPKSTERILIZER PRIMARY KEY (STERILIZER_ID);

      
      /*
      ACTION is CREATE Table STERILIZERPROCESS
      */

CREATE TABLE STERILIZERPROCESS (
       STERILIZER_ID        NUMBER NOT NULL,
       TREATMENTTYPE_ID     NUMBER NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1STERILIZERPROCESS ON STERILIZERPROCESS
(
       STERILIZER_ID                  ASC
);

CREATE INDEX XIF2STERILIZERPROCESS ON STERILIZERPROCESS
(
       TREATMENTTYPE_ID               ASC
);

ALTER TABLE STERILIZERPROCESS
       ADD CONSTRAINT XPKSTERILIZERPROCESS PRIMARY KEY (STERILIZER_ID, 
              TREATMENTTYPE_ID);

      /*
      ACTION is CREATE Table UNIQUEORPACK
      */

CREATE TABLE UNIQUEORPACK (
       UNIQUEITEM_ID        NUMBER NOT NULL,
       TREATMENTTYPE_ID     NUMBER NULL,
       STOCKEXPIRATIONDATE  DATE NULL,
       CREATIONTIMESTAMP    DATE NOT NULL,
       CREATIONEMPLOYEE_ID  NUMBER NOT NULL,
       LABELLAYOUT_ID	    NUMBER NOT NULL,
       ASSEMBLETIMESTAMP    DATE NULL,
       ASSEMBLEEMPLOYEE_ID  NUMBER NULL,
       LOADTIMESTAMP        DATE NULL,
       LOADEMPLOYEE_ID      NUMBER NULL,
       DELIVERYTIMESTAMP    DATE NULL,
       DELIVERYEMPLOYEE_ID  NUMBER NULL,
       CANCELTIMESTAMP      DATE NULL,
       CANCELEMPLOYEE_ID    NUMBER NULL,
       RECALLTIMESTAMP      DATE NULL,
       RECALLEMPLOYEE_ID    NUMBER NULL,
       REASONCODE_ID        NUMBER NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF2UNIQUEORPACK ON UNIQUEORPACK
(
       TREATMENTTYPE_ID               ASC
);

CREATE INDEX XIF3UNIQUEORPACK ON UNIQUEORPACK
(
       CREATIONEMPLOYEE_ID            ASC
);

CREATE INDEX XIF10UNIQUEORPACK ON UNIQUEORPACK
(
       LABELLAYOUT_ID            ASC
);

CREATE INDEX XIF4UNIQUEORPACK ON UNIQUEORPACK
(
       ASSEMBLEEMPLOYEE_ID            ASC
);

CREATE INDEX XIF5UNIQUEORPACK ON UNIQUEORPACK
(
       LOADEMPLOYEE_ID                ASC
);

CREATE INDEX XIF6UNIQUEORPACK ON UNIQUEORPACK
(
       DELIVERYEMPLOYEE_ID            ASC
);

CREATE INDEX XIF7UNIQUEORPACK ON UNIQUEORPACK
(
       CANCELEMPLOYEE_ID              ASC
);

CREATE INDEX XIF8UNIQUEORPACK ON UNIQUEORPACK
(
       RECALLEMPLOYEE_ID              ASC
);

CREATE INDEX XIF9UNIQUEORPACK ON UNIQUEORPACK
(
       REASONCODE_ID                  ASC
);


ALTER TABLE UNIQUEORPACK
       ADD CONSTRAINT XPKUNIQUEORPACK PRIMARY KEY (UNIQUEITEM_ID);
      
      /*
      ACTION is CREATE Table UNIQUEORPACKCART
      */

CREATE TABLE UNIQUEORPACKCART (
       UNIQUEITEM_ID        NUMBER NOT NULL,
       CART_ID              NUMBER NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF2UNIQUEORPACKCART ON UNIQUEORPACKCART
(
       CART_ID                        ASC
);


ALTER TABLE UNIQUEORPACKCART
       ADD CONSTRAINT XPKUNIQUEORPACKCART PRIMARY KEY (UNIQUEITEM_ID);

      /*
      ACTION is CREATE Table UNIQUEORPACKSTERILE
      */

CREATE TABLE UNIQUEORPACKSTERILE (
       UNIQUEITEM_ID        NUMBER NOT NULL,
       CHARGENUMBER         NUMBER(15) NOT NULL,
       STERILIZER_ID        NUMBER NOT NULL,
       REJECTREASONCODE_ID  NUMBER NULL,
       STERILIZATIONTIMESTAMP DATE NOT NULL,
       STERILIZATIONEMPLOYEE_ID NUMBER NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL,
       ORBUSINESSUNIT_ID    NUMBER NOT NULL
);

CREATE INDEX XIF1UNIQUEORPACKSTERILE ON UNIQUEORPACKSTERILE
(
       UNIQUEITEM_ID                  ASC
);

CREATE INDEX XIF2UNIQUEORPACKSTERILE ON UNIQUEORPACKSTERILE
(
       STERILIZER_ID                  ASC
);

CREATE INDEX XIF3UNIQUEORPACKSTERILE ON UNIQUEORPACKSTERILE
(
       REJECTREASONCODE_ID            ASC
);

CREATE INDEX XIF4UNIQUEORPACKSTERILE ON UNIQUEORPACKSTERILE
(
       STERILIZATIONEMPLOYEE_ID       ASC
);

CREATE INDEX XIF5UNIQUEORPACKSTERILE ON UNIQUEORPACKSTERILE
(
       ORBUSINESSUNIT_ID       ASC
);

CREATE INDEX XIE1UNIQUEORPACKSTERILE ON UNIQUEORPACKSTERILE
(
       CHARGENUMBER                   ASC
);

CREATE INDEX XIE2UNIQUEORPACKSTERILE ON UNIQUEORPACKSTERILE
(
       UNIQUEITEM_ID                  ASC,
       STERILIZATIONTIMESTAMP         ASC
);


ALTER TABLE UNIQUEORPACKSTERILE
       ADD CONSTRAINT XPKUNIQUEORPACKSTERILE PRIMARY KEY (
              UNIQUEITEM_ID, CHARGENUMBER, ORBUSINESSUNIT_ID);
      
      /*
      ACTION is CREATE Table UNIQUEORPACKCONTENTS
      */

CREATE SEQUENCE SEQ_UNIQUEORPACKCONTENTS
START WITH 1
INCREMENT BY 1;

CREATE TABLE UNIQUEORPACKCONTENTS (
       UNIQUEORPACKCONTENTS_ID NUMBER NOT NULL,
       UNIQUEITEM_ID        NUMBER NOT NULL,
       PACKSEQUENCE         NUMBER(4) NOT NULL,
       PRODUCT_ID           NUMBER NOT NULL,
       SIZEDEFINITION_ID    NUMBER NOT NULL,
       FINISHINGMETHOD_ID   NUMBER NOT NULL,
       REMARK               VARCHAR2(80) NULL,
       LOTNUMBER            VARCHAR2(20) NULL,
       QTYNONUNIQUE         NUMBER(4) DEFAULT 0 NOT NULL
                                   CONSTRAINT QtyNonUnique_Valid
                                          CHECK (QTYNONUNIQUE BETWEEN 0 AND 9999),
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE UNIQUE INDEX XAK1UNIQUEORPACKCONTENTS ON UNIQUEORPACKCONTENTS
(
       UNIQUEITEM_ID                  ASC,
       PACKSEQUENCE                   ASC,
       PRODUCT_ID                     ASC,
       SIZEDEFINITION_ID              ASC,
       FINISHINGMETHOD_ID             ASC,
       LOTNUMBER                      ASC
);

CREATE INDEX XIF1UNIQUEORPACKCONTENTS ON UNIQUEORPACKCONTENTS
(
       UNIQUEITEM_ID                  ASC
);

CREATE INDEX XIF2UNIQUEORPACKCONTENTS ON UNIQUEORPACKCONTENTS
(
       PRODUCT_ID                     ASC
);

CREATE INDEX XIF3UNIQUEORPACKCONTENTS ON UNIQUEORPACKCONTENTS
(
       SIZEDEFINITION_ID              ASC
);

CREATE INDEX XIF4UNIQUEORPACKCONTENTS ON UNIQUEORPACKCONTENTS
(
       FINISHINGMETHOD_ID             ASC
);

CREATE INDEX XIE1UNIQUEORPACKCONTENTS ON UNIQUEORPACKCONTENTS
(
       PRODUCT_ID                     ASC,
       SIZEDEFINITION_ID              ASC,
       LOTNUMBER                      ASC
);


ALTER TABLE UNIQUEORPACKCONTENTS
       ADD CONSTRAINT XPKUNIQUEORPACKCONTENTS PRIMARY KEY (
              UNIQUEORPACKCONTENTS_ID);

     
      /*
      ACTION is CREATE Table UNIQUEORPACKUNIQUECONTENTS
      */

CREATE TABLE UNIQUEORPACKUNIQUECONTENTS (
       UNIQUEORPACKCONTENTS_ID NUMBER NOT NULL,
       UNIQUEITEM_ID        NUMBER NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1UNIQUEORPACKUNIQUECONTENTS ON UNIQUEORPACKUNIQUECONTENTS
(
       UNIQUEORPACKCONTENTS_ID        ASC
);

CREATE INDEX XIF2UNIQUEORPACKUNIQUECONTENTS ON UNIQUEORPACKUNIQUECONTENTS
(
       UNIQUEITEM_ID                  ASC
);


ALTER TABLE UNIQUEORPACKUNIQUECONTENTS
       ADD CONSTRAINT XPKUNIQUEORPACKUNIQUECONTENTS PRIMARY KEY (
              UNIQUEORPACKCONTENTS_ID, UNIQUEITEM_ID);

      
      /*
      CHANGE REPORT for Table STATUS
          - Changing column definition of STATUSTYPE
      ACTION is ALTER or modify Table STATUS
      */

COMMENT ON COLUMN STATUS.STATUSTYPE IS 'PN=Packing/delivery note
WN=WeighingNote
IN=Invoice
ST=Statement
RB=RemitBatch
WO=WorkOrder
UI=Unique item
SR=Straight Even Return
GD=Garment Dispenser
PO=Purchase Order
BN=Unique Bundle
UR=Unique Residential Item
PL=Pick List
CA=Cart
OR=OR Pack';


ALTER TABLE UNIQUEORPACK
       ADD (CONSTRAINT UNIQUEORPACK_REASONCODE_ID
              FOREIGN KEY (REASONCODE_ID)
                             REFERENCES REASONCODE  (REASONCODE_ID));


ALTER TABLE UNIQUEORPACK
       ADD (CONSTRAINT UNIQUEORPACK_RECALLEMPLOYEE_ID
              FOREIGN KEY (RECALLEMPLOYEE_ID)
                             REFERENCES EMPLOYEE  (EMPLOYEE_ID));


ALTER TABLE UNIQUEORPACK
       ADD (CONSTRAINT UNIQUEORPACK_CANCELEMPLOYEE_ID
              FOREIGN KEY (CANCELEMPLOYEE_ID)
                             REFERENCES EMPLOYEE  (EMPLOYEE_ID));


ALTER TABLE UNIQUEORPACK
       ADD (CONSTRAINT UNIQUEORPACK_DELIVERYEMPLOYEE_
              FOREIGN KEY (DELIVERYEMPLOYEE_ID)
                             REFERENCES EMPLOYEE  (EMPLOYEE_ID));


ALTER TABLE UNIQUEORPACK
       ADD (CONSTRAINT UNIQUEORPACK_LOADEMPLOYEE_ID
              FOREIGN KEY (LOADEMPLOYEE_ID)
                             REFERENCES EMPLOYEE  (EMPLOYEE_ID));


ALTER TABLE UNIQUEORPACK
       ADD (CONSTRAINT UNIQUEORPACK_ASSEMBLEEMPLOYEE_
              FOREIGN KEY (ASSEMBLEEMPLOYEE_ID)
                             REFERENCES EMPLOYEE  (EMPLOYEE_ID));

ALTER TABLE UNIQUEORPACK
       ADD (CONSTRAINT UNIQUEORPACK_LABELLAYOUT_
              FOREIGN KEY (LABELLAYOUT_ID)
                             REFERENCES LABELLAYOUT (LABELLAYOUT_ID));

ALTER TABLE UNIQUEORPACK
       ADD (CONSTRAINT UNIQUEORPACK_CREATIONEMPLOYEE_
              FOREIGN KEY (CREATIONEMPLOYEE_ID)
                             REFERENCES EMPLOYEE  (EMPLOYEE_ID));


ALTER TABLE UNIQUEORPACK
       ADD (CONSTRAINT UNIQUEORPACK_TREATMENTTYPE_ID
              FOREIGN KEY (TREATMENTTYPE_ID)
                             REFERENCES TREATMENTTYPE  (TREATMENTTYPE_ID));


ALTER TABLE UNIQUEORPACK
       ADD (CONSTRAINT UNIQUEORPACK_UNIQUEITEM_ID
              FOREIGN KEY (UNIQUEITEM_ID)
                             REFERENCES UNIQUEITEM  (UNIQUEITEM_ID)
                             ON DELETE CASCADE);


ALTER TABLE CART
       ADD (CONSTRAINT CART_LASTWORKSTATION_ID
              FOREIGN KEY (LASTWORKSTATION_ID)
                             REFERENCES WORKSTATION  (WORKSTATION_ID));


ALTER TABLE CART
       ADD (CONSTRAINT CART_STATUS_ID
              FOREIGN KEY (STATUS_ID)
                             REFERENCES STATUS  (STATUS_ID));


ALTER TABLE CART
       ADD (CONSTRAINT CART_CARTTYPE_ID
              FOREIGN KEY (CARTTYPE_ID)
                             REFERENCES CARTTYPE  (CARTTYPE_ID));


ALTER TABLE CART
       ADD (CONSTRAINT CART_PLANT_ID
              FOREIGN KEY (PLANT_ID)
                             REFERENCES PLANT  (PLANT_ID));


ALTER TABLE CARTTYPE_DESC
       ADD (CONSTRAINT CARTTYPE_D_LANGUAGE_ID
              FOREIGN KEY (LANGUAGE_ID)
                             REFERENCES LANGUAGES  (LANGUAGE_ID)
			       ON DELETE CASCADE);


ALTER TABLE CARTTYPE_DESC
       ADD (CONSTRAINT CARTTYPE_D_CARTTYPE_ID
              FOREIGN KEY (CARTTYPE_ID)
                             REFERENCES CARTTYPE  (CARTTYPE_ID)
			       ON DELETE CASCADE);


ALTER TABLE CARTUNIQUEID
       ADD (CONSTRAINT CARTUNIQUEID_PLANT_ID
              FOREIGN KEY (PLANT_ID)
                             REFERENCES PLANT  (PLANT_ID));


ALTER TABLE CARTUNIQUEID
       ADD (CONSTRAINT CARTUNIQUEID_CART_ID
              FOREIGN KEY (CART_ID)
                             REFERENCES CART  (CART_ID)
                             ON DELETE CASCADE);


ALTER TABLE STERILIZER
       ADD (CONSTRAINT STERILIZER_PLANT_ID
              FOREIGN KEY (PLANT_ID)
                             REFERENCES PLANT  (PLANT_ID));


ALTER TABLE STERILIZERPROCESS
       ADD (CONSTRAINT STERILIZERPROCESS_TREATMENTTYP
              FOREIGN KEY (TREATMENTTYPE_ID)
                             REFERENCES TREATMENTTYPE  (TREATMENTTYPE_ID));


ALTER TABLE STERILIZERPROCESS
       ADD (CONSTRAINT STERILIZERPROCESS_STERILIZER_I
              FOREIGN KEY (STERILIZER_ID)
                             REFERENCES STERILIZER  (STERILIZER_ID)
                             ON DELETE CASCADE);


ALTER TABLE UNIQUEORPACKCART
       ADD (CONSTRAINT UNIQUEORPACKCART_CART_ID
              FOREIGN KEY (CART_ID)
                             REFERENCES CART  (CART_ID));


ALTER TABLE UNIQUEORPACKCART
       ADD (CONSTRAINT UNIQUEORPACKCART_UNIQUEITEM_ID
              FOREIGN KEY (UNIQUEITEM_ID)
                             REFERENCES UNIQUEORPACK  (UNIQUEITEM_ID)
                             ON DELETE CASCADE);


ALTER TABLE UNIQUEORPACKCONTENTS
       ADD (CONSTRAINT UNIQUEORPACKCONTENTS_FINISHING
              FOREIGN KEY (FINISHINGMETHOD_ID)
                             REFERENCES FINISHINGMETHOD  (FINISHINGMETHOD_ID));


ALTER TABLE UNIQUEORPACKCONTENTS
       ADD (CONSTRAINT UNIQUEORPACKCONTENTS_SIZEDEFIN
              FOREIGN KEY (SIZEDEFINITION_ID)
                             REFERENCES SIZEDEFINITION  (SIZEDEFINITION_ID));


ALTER TABLE UNIQUEORPACKCONTENTS
       ADD (CONSTRAINT UNIQUEORPACKCONTENTS_PRODUCT_I
              FOREIGN KEY (PRODUCT_ID)
                             REFERENCES PRODUCT  (PRODUCT_ID));


ALTER TABLE UNIQUEORPACKCONTENTS
       ADD (CONSTRAINT UNIQUEORPACKCONTENTS_UNIQUEITE
              FOREIGN KEY (UNIQUEITEM_ID)
                             REFERENCES UNIQUEORPACK  (UNIQUEITEM_ID)
                             ON DELETE CASCADE);

ALTER TABLE UNIQUEORPACKSTERILE
       ADD (CONSTRAINT UNIQUEORPACKSTERILE_ORBUSINESS
              FOREIGN KEY (ORBUSINESSUNIT_ID)
                             REFERENCES BUSINESSUNIT  (BUSINESSUNIT_ID));

ALTER TABLE UNIQUEORPACKSTERILE
       ADD (CONSTRAINT UNIQUEORPACKSTERILE_STERILIZAT
              FOREIGN KEY (STERILIZATIONEMPLOYEE_ID)
                             REFERENCES EMPLOYEE  (EMPLOYEE_ID));


ALTER TABLE UNIQUEORPACKSTERILE
       ADD (CONSTRAINT UNIQUEORPACKSTERILE_REJECTREAS
              FOREIGN KEY (REJECTREASONCODE_ID)
                             REFERENCES REASONCODE  (REASONCODE_ID));


ALTER TABLE UNIQUEORPACKSTERILE
       ADD (CONSTRAINT UNIQUEORPACKSTERILE_STERILIZER
              FOREIGN KEY (STERILIZER_ID)
                             REFERENCES STERILIZER  (STERILIZER_ID));


ALTER TABLE UNIQUEORPACKSTERILE
       ADD (CONSTRAINT UNIQUEORPACKSTERILE_UNIQUEITEM
              FOREIGN KEY (UNIQUEITEM_ID)
                             REFERENCES UNIQUEORPACK  (UNIQUEITEM_ID)
                             ON DELETE CASCADE);


ALTER TABLE UNIQUEORPACKUNIQUECONTENTS
       ADD (CONSTRAINT UNIQUEORPACKUNIQUECONTENTS_UNI
              FOREIGN KEY (UNIQUEITEM_ID)
                             REFERENCES UNIQUEITEM  (UNIQUEITEM_ID)
                             ON DELETE CASCADE);


ALTER TABLE UNIQUEORPACKUNIQUECONTENTS
       ADD (CONSTRAINT UNIQUEORPACKUNIQUECONTENTS_UN
              FOREIGN KEY (UNIQUEORPACKCONTENTS_ID)
                             REFERENCES UNIQUEORPACKCONTENTS  (UNIQUEORPACKCONTENTS_ID));

insert into status
  (status_id,
   statustype,
   code,
   legacystatuscode,
   systemuser_id,
   timestamp,
   factorydescription)
values
  (seq_status.nextval,
   'CA',
   '10',
   '',
   getminsystemuser(),
   sysdate,
   'Available');
insert into status
  (status_id,
   statustype,
   code,
   legacystatuscode,
   systemuser_id,
   timestamp,
   factorydescription)
values
  (seq_status.nextval,
   'CA',
   '20',
   '',
   getminsystemuser(),
   sysdate,
   'Loaded');
insert into status
  (status_id,
   statustype,
   code,
   legacystatuscode,
   systemuser_id,
   timestamp,
   factorydescription)
values
  (seq_status.nextval,
   'CA',
   '30',
   '',
   getminsystemuser(),
   sysdate,
   'Ready for Sterilization');
insert into status
  (status_id,
   statustype,
   code,
   legacystatuscode,
   systemuser_id,
   timestamp,
   factorydescription)
values
  (seq_status.nextval,
   'OR',
   '10',
   '',
   getminsystemuser(),
   sysdate,
   'Generated(label)');
insert into status
  (status_id,
   statustype,
   code,
   legacystatuscode,
   systemuser_id,
   timestamp,
   factorydescription)
values
  (seq_status.nextval,
   'OR',
   '20',
   '',
   getminsystemuser(),
   sysdate,
   'Assembled');
insert into status
  (status_id,
   statustype,
   code,
   legacystatuscode,
   systemuser_id,
   timestamp,
   factorydescription)
values
  (seq_status.nextval,
   'OR',
   '25',
   '',
   getminsystemuser(),
   sysdate,
   'Loaded on Cart');
insert into status
  (status_id,
   statustype,
   code,
   legacystatuscode,
   systemuser_id,
   timestamp,
   factorydescription)
values
  (seq_status.nextval,
   'OR',
   '30',
   '',
   getminsystemuser(),
   sysdate,
   'Sterilized');
insert into status
  (status_id,
   statustype,
   code,
   legacystatuscode,
   systemuser_id,
   timestamp,
   factorydescription)
values
  (seq_status.nextval,
   'OR',
   '40',
   '',
   getminsystemuser(),
   sysdate,
   'Delivered');
insert into status
  (status_id,
   statustype,
   code,
   legacystatuscode,
   systemuser_id,
   timestamp,
   factorydescription)
values
  (seq_status.nextval,
   'OR',
   '50',
   '',
   getminsystemuser(),
   sysdate,
   'Used');
insert into status
  (status_id,
   statustype,
   code,
   legacystatuscode,
   systemuser_id,
   timestamp,
   factorydescription)
values
  (seq_status.nextval,
   'OR',
   '60',
   '',
   getminsystemuser(),
   sysdate,
   'Cancelled');
insert into status
  (status_id,
   statustype,
   code,
   legacystatuscode,
   systemuser_id,
   timestamp,
   factorydescription)
values
  (seq_status.nextval,
   'OR',
   '70',
   '',
   getminsystemuser(),
   sysdate,
   'Recalled');
insert into status_desc
  (status_id, language_id, description, systemuser_id, timestamp)
  select status_id,
         l.language_id,
         factorydescription,
         s.systemuser_id,
         s.timestamp
    from status s,languages l
    where s.statustype in ('CA','OR') and s.code in (10,20,25,30,40,50,60,70);


























