-- order 501898 (critical problem list)

  
      /*
      CHANGE REPORT for Table STOCKROOMSSERVICINGBU
          - Adding index XIE2STOCKROOMSSERVICINGBU
      ACTION is ALTER or modify Table STOCKROOMSSERVICINGBU
      */

CREATE INDEX XIE2STOCKROOMSSERVICINGBU ON STOCKROOMSSERVICINGBU
(
       PRIORITY                       
);

 
      /*
      CHANGE REPORT for Table PACKCONTENTSALTERNATIVE
          - Adding index XAK1PACKCONTENTSALTERNATIVE
      ACTION is ALTER or modify Table PACKCONTENTSALTERNATIVE
      */
CREATE UNIQUE INDEX XAK1PACKCONTENTSALTERNATIVE ON PACKCONTENTSALTERNATIVE
(
       PACKCONTENTS_ID                ,
       SEQUENCENUMBER                 
);



      /*
      CHANGE REPORT for Table PLANTCOMPANY
          - Adding index XAK1PLANTCOMPANY
      ACTION is ALTER or modify Table PLANTCOMPANY
      */
CREATE UNIQUE INDEX XAK1PLANTCOMPANY ON PLANTCOMPANY
(
       PLANT_ID                       ,
       CODE                           
);

ALTER TABLE PLANTCOMPANY MODIFY ACTIVE DEFAULT 'Y';

      
      /*
      CHANGE REPORT for Table POSTALCODE
          - Adding index XAK1POSTALCODE
      ACTION is ALTER or modify Table POSTALCODE
      */
CREATE UNIQUE INDEX XAK1POSTALCODE ON POSTALCODE
(
       COUNTRY_ID                     ,
       POSTALCODE                     
);


 /*
      CHANGE REPORT for Table QUALITYTESTTYPEPERBU
          - Changing definition of index XAK1QUALITYTESTTYPEPERBU
      ACTION is ALTER or modify Table QUALITYTESTTYPEPERBU
      */

DROP INDEX XAK1QUALITYTESTTYPEPERBU;

CREATE UNIQUE INDEX XAK1QUALITYTESTTYPEPERBU ON QUALITYTESTTYPEPERBU
(
       BUSINESSUNIT_ID                ASC,
       QUALITYTESTTYPE_ID             
);


ALTER TABLE LEDGERACCNTTYPE_DESC DROP CONSTRAINT LEDGERACCNTTYPE_D_LANGUAGE_ID;


ALTER TABLE LEDGERACCNTTYPE_DESC DROP CONSTRAINT LEDGERACCNTTYPE_D_LEDGERACCNTT;

   
      /*
      CHANGE REPORT for Table LEDGERACCNTTYPE_DESC
          - Changing definition of relation LEDGERACCNTTYPE_D_LANGUAGE_ID
          - Changing definition of relation LEDGERACCNTTYPE_D_LEDGERACCNTT
      ACTION is ALTER or modify Table LEDGERACCNTTYPE_DESC
      */


ALTER TABLE LEDGERACCNTTYPE_DESC
       ADD  ( CONSTRAINT LEDGERACCNTTYPE_D_LANGUAGE_ID
              FOREIGN KEY (LANGUAGE_ID)
                             REFERENCES LANGUAGES
                             ON DELETE CASCADE ) ;


ALTER TABLE LEDGERACCNTTYPE_DESC
       ADD  ( CONSTRAINT LEDGERACCNTTYPE_D_LEDGERACCNTT
              FOREIGN KEY (LEDGERACCNTTYPE_ID)
                             REFERENCES LEDGERACCNTTYPE
                             ON DELETE CASCADE ) ;


ALTER TABLE CLEANUPSCANCONFIGURATION DROP CONSTRAINT 
    CLEANUPSCANCONFIGURATION_PLANT;


      /*
      CHANGE REPORT for Table CLEANUPSCANCONFIGURATION
          - Changing definition of relation CLEANUPSCANCONFIGURATION_PLANT
      ACTION is ALTER or modify Table CLEANUPSCANCONFIGURATION
      */

ALTER TABLE CLEANUPSCANCONFIGURATION
       ADD  ( CONSTRAINT CLEANUPSCANCONFIGURATION_PLANT
              FOREIGN KEY (PLANT_ID)
                             REFERENCES PLANT
                             ON DELETE CASCADE ) ;


update statementline sl
   set sl.artransaction_id = null
 where sl.statementline_id in
       (select sla.statementline_id from STATEMENTLINE SLa
        minus
        select slb.statementline_id from STATEMENTLINE SLb, artransaction a
         where slb.artransaction_id = a.artransaction_id);

commit;


ALTER TABLE STATEMENTLINE ADD(CONSTRAINT STATEMENTLINE_ARTRANSACTION_ID
                              FOREIGN KEY(ARTRANSACTION_ID) 
                              REFERENCES ARTRANSACTION 
                              ON DELETE SET NULL);


   /*
      CHANGE REPORT for Table EMBLEMHISTORY
          - modify validation rule from (empty) to STATUS IN (0, 1, 2) of column STATUS
          WARNING : Load data statement for table EMBLEMHISTORY or the statement to add a validation rule may fail (existing data may violate the new validation rule for column STATUS).
          - Changing column definition of STATUS
      ACTION is ALTER or modify Table EMBLEMHISTORY
      */

COMMENT ON COLUMN EMBLEMHISTORY.STATUS IS '0 = Charge
1 = Free of Charge
2 = Charge Cancelled';


ALTER TABLE EMBLEMHISTORY 
ADD CONSTRAINT EMBLEMHISTORYSTATUS_VALID
               CHECK (STATUS IN (0, 1, 2));



update RENTALQTYADJUSTMENT a
   set a.deliveryvariations_id = null
 where a.rentalqtyadjustment_id in
       (select rqa1.rentalqtyadjustment_id
          FROM RENTALQTYADJUSTMENT RQA1
         where rqa1.deliveryvariations_id is not null
        minus
        select rqa2.rentalqtyadjustment_id
          FROM RENTALQTYADJUSTMENT RQA2, DELIVERYVARIATIONS d
         where d.deliveryvariations_id = rqa2.deliveryvariations_id);

commit;


/*
      CHANGE REPORT for Table RENTALQTYADJUSTMENT
          - Adding a new relationship RENTQTYADJ_DELIVERYVARIATIONS_
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK RENTQTYADJ_DELIVERYVARIATIONS_ of table RENTALQTYADJUSTMENT, which references table DELIVERYVARIATIONS).
          - Adding index XIF7RENTALQTYADJUSTMENT
      ACTION is ALTER or modify Table RENTALQTYADJUSTMENT
      */


ALTER TABLE RENTALQTYADJUSTMENT ADD(CONSTRAINT RENTQTYADJ_DELIVERYVARIATIONS_
                              FOREIGN KEY(DELIVERYVARIATIONS_ID ) 
                              REFERENCES DELIVERYVARIATIONS 
                              ON DELETE SET NULL);

DROP INDEX XIE2RENTALQTYADJUSTMENT;

CREATE INDEX XIF7RENTALQTYADJUSTMENT ON RENTALQTYADJUSTMENT
(
       DELIVERYVARIATIONS_ID          ASC
);
