

      
      /*
      CHANGE REPORT for Table PURCHASEORDERLINE
          - Adding a new relationship PURCHASEORDERLINE_REQUESTEDGRA
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK PURCHASEORDERLINE_REQUESTEDGRA of table PURCHASEORDERLINE, which references table QUALITYGRADE).
          - modify validation rule from (empty) to PACKING BETWEEN 0 AND 3 of column PACKING
          WARNING : Load data statement for table PURCHASEORDERLINE or the statement to add a validation rule may fail (existing data may violate the new validation rule for column PACKING).
          - Adding column REQUESTEDGRADE_ID
      ACTION is ALTER or modify Table PURCHASEORDERLINE
      */


create sequence SEQ_EXTSTOCKTRANSTYPE start with 1 increment by 1;
      
      /*
      ACTION is CREATE Table EXTSTOCKTRANSTYPE
      */

CREATE TABLE EXTSTOCKTRANSTYPE (
       EXTSTOCKTRANSTYPE_ID NUMBER NOT NULL,
       CODE                 VARCHAR2(4) NOT NULL,
       KEYCODE              VARCHAR2(2) NOT NULL,
       FACTORYDESCRIPTION   VARCHAR2(40) NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL,
       ACTIVE               VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_ValidationNew61
                                          CHECK (ACTIVE IN ('Y', 'N'))
);

CREATE UNIQUE INDEX XAK1EXTSTOCKTRANSTYPE ON EXTSTOCKTRANSTYPE
(
       CODE                           ASC
);

CREATE UNIQUE INDEX XAK2EXTSTOCKTRANSTYPE ON EXTSTOCKTRANSTYPE
(
       KEYCODE                        ASC
);


ALTER TABLE EXTSTOCKTRANSTYPE
       ADD  ( CONSTRAINT XPKEXTSTOCKTRANSTYPE PRIMARY KEY (
              EXTSTOCKTRANSTYPE_ID) ) ;

      
      /*
      ACTION is CREATE Table EXTSTOCKTRANSTYPE_DESC
      */

CREATE TABLE EXTSTOCKTRANSTYPE_DESC (
       EXTSTOCKTRANSTYPE_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 XIF1EXTSTOCKTRANSTYPE_DESC ON EXTSTOCKTRANSTYPE_DESC
(
       EXTSTOCKTRANSTYPE_ID           ASC
);

CREATE INDEX XIF2EXTSTOCKTRANSTYPE_DESC ON EXTSTOCKTRANSTYPE_DESC
(
       LANGUAGE_ID                    ASC
);


ALTER TABLE EXTSTOCKTRANSTYPE_DESC
       ADD  ( CONSTRAINT XPKEXTSTOCKTRANSTYPE_DESC PRIMARY KEY (
              EXTSTOCKTRANSTYPE_ID, LANGUAGE_ID) ) ;



create sequence SEQ_STOCKTYPE start with 1 increment by 1;      
      /*
      ACTION is CREATE Table STOCKTYPE
      */

CREATE TABLE STOCKTYPE (
       STOCKTYPE_ID         NUMBER NOT NULL,
       CODE                 VARCHAR2(2) NOT NULL,
       FACTORYDESCRIPTION   VARCHAR2(40) NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL,
       ACTIVE               VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_ValidationNew62
                                          CHECK (ACTIVE IN ('Y', 'N'))
);

CREATE UNIQUE INDEX XAK1STOCKTYPE ON STOCKTYPE
(
       CODE                           ASC
);


ALTER TABLE STOCKTYPE
       ADD  ( CONSTRAINT XPKSTOCKTYPE PRIMARY KEY (STOCKTYPE_ID) ) ;


create sequence SEQ_LEDGERACCNTTYPE start with 1 increment by 1;
      
      /*
      ACTION is CREATE Table LEDGERACCNTTYPE
      */

CREATE TABLE LEDGERACCNTTYPE (
       LEDGERACCNTTYPE_ID   NUMBER NOT NULL,
       CODE                 VARCHAR2(6) NOT NULL,
       FACTORYDESCRIPTION   VARCHAR2(40) NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL,
       ACTIVE               VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_ValidationNew63
                                          CHECK (ACTIVE IN ('Y', 'N'))
);

CREATE UNIQUE INDEX XAK1LEDGERACCNTTYPE ON LEDGERACCNTTYPE
(
       CODE                           ASC
);


ALTER TABLE LEDGERACCNTTYPE
       ADD  ( CONSTRAINT XPKLEDGERACCNTTYPE PRIMARY KEY (
              LEDGERACCNTTYPE_ID) ) ;

      
      /*
      ACTION is CREATE Table LEDGERACCNTSPERPRODUCT
      */

CREATE TABLE LEDGERACCNTSPERPRODUCT (
       PRODUCT_ID           NUMBER NOT NULL,
       LEDGERACCNTTYPE_ID   NUMBER NOT NULL,
       STOCKTYPE_ID         NUMBER NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL,
       LEDGERACCOUNTS_ID    NUMBER NOT NULL
);

CREATE INDEX XIF1LEDGERACCNTSPERPRODUCT ON LEDGERACCNTSPERPRODUCT
(
       PRODUCT_ID                     ASC
);

CREATE INDEX XIF2LEDGERACCNTSPERPRODUCT ON LEDGERACCNTSPERPRODUCT
(
       LEDGERACCNTTYPE_ID             ASC
);

CREATE INDEX XIF4LEDGERACCNTSPERPRODUCT ON LEDGERACCNTSPERPRODUCT
(
       STOCKTYPE_ID                   ASC
);

CREATE INDEX XIF5LEDGERACCNTSPERPRODUCT ON LEDGERACCNTSPERPRODUCT
(
       LEDGERACCOUNTS_ID              ASC
);


ALTER TABLE LEDGERACCNTSPERPRODUCT
       ADD  ( CONSTRAINT XPKLEDGERACCNTSPERPRODUCT PRIMARY KEY (
              PRODUCT_ID, STOCKTYPE_ID, LEDGERACCNTTYPE_ID) ) ;

      
      /*
      ACTION is CREATE Table LEDGERACCNTTYPE_DESC
      */

CREATE TABLE LEDGERACCNTTYPE_DESC (
       LEDGERACCNTTYPE_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 XIF1LEDGERACCNTTYPE_DESC ON LEDGERACCNTTYPE_DESC
(
       LEDGERACCNTTYPE_ID             ASC
);

CREATE INDEX XIF2LEDGERACCNTTYPE_DESC ON LEDGERACCNTTYPE_DESC
(
       LANGUAGE_ID                    ASC
);


ALTER TABLE LEDGERACCNTTYPE_DESC
       ADD  ( CONSTRAINT XPKLEDGERACCNTTYPE_DESC PRIMARY KEY (
              LEDGERACCNTTYPE_ID, LANGUAGE_ID) ) ;

      
      /*
      ACTION is CREATE Table STOCKTYPE_DESC
      */

CREATE TABLE STOCKTYPE_DESC (
       STOCKTYPE_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 XIF1STOCKTYPE_DESC ON STOCKTYPE_DESC
(
       STOCKTYPE_ID                   ASC
);

CREATE INDEX XIF2STOCKTYPE_DESC ON STOCKTYPE_DESC
(
       LANGUAGE_ID                    ASC
);


ALTER TABLE STOCKTYPE_DESC
       ADD  ( CONSTRAINT XPKSTOCKTYPE_DESC PRIMARY KEY (STOCKTYPE_ID, 
              LANGUAGE_ID) ) ;

      
      /*
      CHANGE REPORT for Table CUSTOMER
          - modify validation rule from (empty) to (MasterAccount < 3 and MasterAccount_ID = Customer_ID) or (MasterAccount = 3 and MasterAccount_ID <> Customer_ID) of column MASTERACCOUNT
          WARNING : Load data statement for table CUSTOMER or the statement to add a validation rule may fail (existing data may violate the new validation rule for column MASTERACCOUNT).
          - Changing column definition of CUSTOMERTYPE
         */

COMMENT ON COLUMN CUSTOMER.CUSTOMERTYPE IS '0 = External
1 = Internal (stockroom)';


      
      /*
      CHANGE REPORT for Table PURCHASEORDERHEADER
          - Adding a new relationship PURCHASEORDERHEADER_STOCKROOM_
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK PURCHASEORDERHEADER_STOCKROOM_ of table PURCHASEORDERHEADER, which references table STOCKROOM).
          - Changing definition of relation PURCHASEORDERHEADER_STOCKROOM_
          - Adding column PURCHASEORDERTYPE
          - Adding column PROCESSEDTOWORKORDER
          - Adding column DELIVERYPOINT_LINK_ID
          - Adding column SUPLYINGSTOCKROOM_ID
          - Adding index XIF8PURCHASEORDERHEADER
      ACTION is ALTER or modify Table PURCHASEORDERHEADER
      */

ALTER TABLE PURCHASEORDERHEADER ADD (PURCHASEORDERTYPE    NUMBER DEFAULT 0 NOT NULL
                                   CONSTRAINT PurchaseOrderType_valid
                                          CHECK (PURCHASEORDERTYPE IN (0, 1)));

COMMENT ON COLUMN PURCHASEORDERHEADER.PURCHASEORDERTYPE IS '0 = External
1 = Internal';

ALTER TABLE PURCHASEORDERHEADER ADD (PROCESSEDTOWORKORDER VARCHAR2(1) DEFAULT 
    'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew64
                                          CHECK (PROCESSEDTOWORKORDER IN ('Y', 
    'N')));


ALTER TABLE PURCHASEORDERHEADER ADD (DELIVERYPOINT_LINK_ID NUMBER NULL);


ALTER TABLE PURCHASEORDERHEADER ADD (SUPLYINGSTOCKROOM_ID NUMBER NULL);

ALTER TABLE PURCHASEORDERLINE ADD (REQUESTEDGRADE_ID    NUMBER DEFAULT 0 NOT NULL);


CREATE INDEX XIF6PURCHASEORDERLINE ON PURCHASEORDERLINE
(
       REQUESTEDGRADE_ID              ASC
);

      
      /*
      CHANGE REPORT for Table PURCHASEREQUEST
          - Adding a new relationship PURCHASEREQUEST_REQUESTEDGRADE
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK PURCHASEREQUEST_REQUESTEDGRADE of table PURCHASEREQUEST, which references table QUALITYGRADE).
          - Adding column REQUESTEDGRADE_ID
          - Adding index XIF6PURCHASEREQUEST
      ACTION is ALTER or modify Table PURCHASEREQUEST
      */

ALTER TABLE PURCHASEREQUEST ADD (REQUESTEDGRADE_ID    NUMBER DEFAULT 0 NOT NULL);


update purchaseorderline p
set p.requestedgrade_id = (select a.qualitygrade_id from qualitygrade a
                           where a.newgarment = 'Y'
                           and a.priority = (select min(m.priority) from qualitygrade m where m.newgarment = 'Y'));

update purchaserequest p
set p.requestedgrade_id = (select a.qualitygrade_id from qualitygrade a
                           where a.newgarment = 'Y'
                           and a.priority = (select min(m.priority) from qualitygrade m where m.newgarment = 'Y'));

CREATE INDEX XIF6PURCHASEREQUEST ON PURCHASEREQUEST
(
       REQUESTEDGRADE_ID              ASC
);

  
    
      /*
      CHANGE REPORT for Table STOCKQUANTITY
          - Adding column QTYNONUNIQUE
      ACTION is ALTER or modify Table STOCKQUANTITY
      */

ALTER TABLE STOCKQUANTITY ADD (QTYNONUNIQUE         NUMBER DEFAULT 0 NOT NULL);

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

COMMENT ON COLUMN SUPPLIER.SUPPLIERTYPE IS '0 = External
1 = Internal (stockroom)
';

      
      /*
      CHANGE REPORT for Table SUPPLIERPRODUCT
          - modify validation rule from (empty) to PACKING BETWEEN 0 AND 3 of column PACKING
          WARNING : Load data statement for table SUPPLIERPRODUCT or the statement to add a validation rule may fail (existing data may violate the new validation rule for column PACKING).
      ACTION is ALTER or modify Table SUPPLIERPRODUCT
      */

ALTER TABLE EXTSTOCKTRANSTYPE_DESC
       ADD  ( CONSTRAINT EXTSTOCKTRANSTYPE_D_LANGUAGE_I
              FOREIGN KEY (LANGUAGE_ID)
                             REFERENCES LANGUAGES
                             ON DELETE CASCADE ) ;


ALTER TABLE EXTSTOCKTRANSTYPE_DESC
       ADD  ( CONSTRAINT EXTSTOCKTRANSTYPE_D_EXTSTOCKTR
              FOREIGN KEY (EXTSTOCKTRANSTYPE_ID)
                             REFERENCES EXTSTOCKTRANSTYPE
                             ON DELETE CASCADE ) ;


ALTER TABLE LEDGERACCNTSPERPRODUCT
       ADD  ( CONSTRAINT LEDGERACCNTSPERPRODUCT_STOCKTY
              FOREIGN KEY (STOCKTYPE_ID)
                             REFERENCES STOCKTYPE
                             ON DELETE CASCADE ) ;


ALTER TABLE LEDGERACCNTSPERPRODUCT
       ADD  ( CONSTRAINT LEDGERACCNTSPERPRODUCT_LEDGERA
              FOREIGN KEY (LEDGERACCOUNTS_ID)
                             REFERENCES LEDGERACCOUNTS ) ;


ALTER TABLE LEDGERACCNTSPERPRODUCT
       ADD  ( CONSTRAINT LEDGERACCNTSPERPRODUCT_LEDGERT
              FOREIGN KEY (LEDGERACCNTTYPE_ID)
                             REFERENCES LEDGERACCNTTYPE
                             ON DELETE CASCADE ) ;


ALTER TABLE LEDGERACCNTSPERPRODUCT
       ADD  ( CONSTRAINT LEDGERACCNTSPERPRODUCT_PRODUCT
              FOREIGN KEY (PRODUCT_ID)
                             REFERENCES PRODUCT
                             ON DELETE CASCADE ) ;


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


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


ALTER TABLE STOCKTYPE_DESC
       ADD  ( CONSTRAINT STOCKTYPE_D_LANGUAGE_ID
              FOREIGN KEY (LANGUAGE_ID)
                             REFERENCES LANGUAGES ) ;


ALTER TABLE STOCKTYPE_DESC
       ADD  ( CONSTRAINT STOCKTYPE_D_STOCKTYPE_ID
              FOREIGN KEY (STOCKTYPE_ID)
                             REFERENCES STOCKTYPE ) ;

ALTER TABLE PURCHASEORDERHEADER
       ADD  ( CONSTRAINT SUPLYINGSTOCKROOM_ID
              FOREIGN KEY (SUPLYINGSTOCKROOM_ID)
                             REFERENCES STOCKROOM ) ;


ALTER TABLE PURCHASEORDERLINE
       ADD  ( CONSTRAINT PURCHASEORDERLINE_REQUESTEDGRA
              FOREIGN KEY (REQUESTEDGRADE_ID)
                             REFERENCES QUALITYGRADE ) ;


ALTER TABLE PURCHASEREQUEST
       ADD  ( CONSTRAINT PURCHASEREQUEST_REQUESTEDGRADE
              FOREIGN KEY (REQUESTEDGRADE_ID)
                             REFERENCES QUALITYGRADE ) ;















