create sequence SEQ_STOCKVALUEAVGPP start with 1 increment by 1;     

      /*
      ACTION is CREATE Table STOCKVALUEAVGPP
      */

CREATE TABLE STOCKVALUEAVGPP (
       STOCKVALUEAVGPP_ID   NUMBER NOT NULL,
       PRODUCT_LINK_ID      NUMBER NOT NULL,
       SIZEDEFINITION_LINK_ID NUMBER NOT NULL,
       QUALITYGRADE_ID      NUMBER NOT NULL,
       CUSTOMEROWNED        VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew67
                                          CHECK (CUSTOMEROWNED IN ('Y', 'N')),
       CUSTOMER_LINK_ID     NUMBER NOT NULL,
       TRANSACTIONDATE      DATE NOT NULL,
       STOCKROOM_ID         NUMBER NOT NULL,       
       TRANSACTIONTYPE      NUMBER NULL,
       ORIGIN               NUMBER NULL,
       QUANTITY             NUMBER NOT NULL,
       PURCHASEPRICE        NUMBER(15,6) NOT NULL,
       PURCHASE_CURRENCY_ID NUMBER NOT NULL,       
       EXCHANGERATE         NUMBER NOT NULL,
       PIECEVALUE           NUMBER NOT NULL,
       PREVSTOCKQTY         NUMBER NOT NULL,
       PREVAVGPURCHASEPRICE NUMBER(15,6) NOT NULL,
       ACTUALSTOCKQUANTITY  NUMBER NOT NULL,
       AVGPURCHASEPRICE     NUMBER(15,6) NULL,
       STOCKVALUE_CURRENCY_ID NUMBER NOT NULL,       
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL,
       REMARK               VARCHAR2(80) NULL,
       PURCHASEORDERNUMBER  NUMBER NULL,
       REASONCODE_ID        NUMBER NULL,
       STOCKTRANSACTION_ID  NUMBER NULL  
);

COMMENT ON COLUMN STOCKVALUEAVGPP.TRANSACTIONTYPE IS '1 = Receive in Stock(stock increase)
2 = Remove from Stock(stock decrease)
3 = Administrative transaction (price change)';
COMMENT ON COLUMN STOCKVALUEAVGPP.ORIGIN IS 'TRANSACTIONTYPE=1(Receive in Stock):
1 = Receive via PO
2 = Receive without PO
3 = Return to Stock
4 = Stock correction
5 = Grade change (Used to New)
6 = Grade changed (Defect to New)
7 = Receive via IPO
TRANSACTIONTYPE=2(Remove from Stock):
1=Return to Supplier
TRANSACTIONTYPE=3(Administrative transaction):
0=Initial fill
9 = Inventory Value Charge';

CREATE INDEX XIF1STOCKVALUEAVGPP ON STOCKVALUEAVGPP
(
       PRODUCT_LINK_ID                ASC
);

CREATE INDEX XIF10STOCKVALUEAVGPP ON STOCKVALUEAVGPP
(
       PURCHASE_CURRENCY_ID           ASC
);

CREATE INDEX XIF2STOCKVALUEAVGPP ON STOCKVALUEAVGPP
(
       SIZEDEFINITION_LINK_ID         ASC
);

CREATE INDEX XIF3STOCKVALUEAVGPP ON STOCKVALUEAVGPP
(
       QUALITYGRADE_ID                ASC
);

CREATE INDEX XIF4STOCKVALUEAVGPP ON STOCKVALUEAVGPP
(
       CUSTOMER_LINK_ID               ASC
);

CREATE INDEX XIF5STOCKVALUEAVGPP ON STOCKVALUEAVGPP
(
       STOCKTRANSACTION_ID            ASC
);

CREATE INDEX XIF6STOCKVALUEAVGPP ON STOCKVALUEAVGPP
(
       REASONCODE_ID                  ASC
);

CREATE INDEX XIF7STOCKVALUEAVGPP ON STOCKVALUEAVGPP
(
       STOCKROOM_ID                   ASC
);

CREATE INDEX XIF9STOCKVALUEAVGPP ON STOCKVALUEAVGPP
(
       STOCKVALUE_CURRENCY_ID           ASC
);


ALTER TABLE STOCKVALUEAVGPP
       ADD  ( CONSTRAINT XPKSTOCKVALUEAVGPP PRIMARY KEY (
              STOCKVALUEAVGPP_ID) ) ;

      
      /*
      CHANGE REPORT for Table STOCKTRANSACTION
          - Adding column EXPORTED
          - Adding column DELAYED
      ACTION is ALTER or modify Table STOCKTRANSACTION
      */

ALTER TABLE STOCKTRANSACTION ADD (EXPORTED             VARCHAR2(1) DEFAULT 'N' 
    NOT NULL
                                   CONSTRAINT YN_ValidationNew68
                                          CHECK (EXPORTED IN ('Y', 'N')));


ALTER TABLE STOCKTRANSACTION ADD (DELAYED              VARCHAR2(1) DEFAULT 'N' 
    NOT NULL
                                   CONSTRAINT YN_ValidationNew69
                                          CHECK (DELAYED IN ('Y', 'N')));

      
      /*
      CHANGE REPORT for Table TRANSACTIONTYPE
          - Adding column LEGACYTRANSACTIONCODE
      ACTION is ALTER or modify Table TRANSACTIONTYPE
      */

ALTER TABLE TRANSACTIONTYPE ADD (LEGACYTRANSACTIONCODE VARCHAR2(12) NULL);

      
      /*
      CHANGE REPORT for Table WORKORDER
          - Adding a new relationship WORKORDER_PROCESSINGSTOCKROOM_
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK WORKORDER_PROCESSINGSTOCKROOM_ of table WORKORDER, which references table STOCKROOM).
          - Changing column definition of ORIGIN
          - Adding column PROCESSINGSTOCKROOM_ID
          - Adding index XIF13WORKORDER
      ACTION is ALTER or modify Table WORKORDER
      */

COMMENT ON COLUMN WORKORDER.ORIGIN IS '1 = Generated from wearer inventory
2 = Manual created work order
3 = Created by web module
4 = Created by route assistant
5 = Conversion
6 = Sales note
7 = Credit note
8 = Internal Purchase Order
9 = Stock Delivery Order';


ALTER TABLE WORKORDER ADD (PROCESSINGSTOCKROOM_ID NUMBER NULL);

CREATE INDEX XIF13WORKORDER ON WORKORDER
(
       PROCESSINGSTOCKROOM_ID         ASC
);


ALTER TABLE STOCKVALUEAVGPP
       ADD  ( CONSTRAINT STOCKVALUEAVGPP_PURCHASE_C
              FOREIGN KEY (PURCHASE_CURRENCY_ID)
                             REFERENCES CURRENCY ) ;


ALTER TABLE STOCKVALUEAVGPP
       ADD  ( CONSTRAINT STOCKVALUEAVGPP_STOCKVALUE_C
              FOREIGN KEY (STOCKVALUE_CURRENCY_ID)
                             REFERENCES CURRENCY ) ;


ALTER TABLE STOCKVALUEAVGPP
       ADD  ( CONSTRAINT STOCKVALUEAVGPP_STOCKROOM_ID
              FOREIGN KEY (STOCKROOM_ID)
                             REFERENCES STOCKROOM ) ;


ALTER TABLE STOCKVALUEAVGPP
       ADD  ( CONSTRAINT STOCKVALUEAVGPP_REASONCODE_ID
              FOREIGN KEY (REASONCODE_ID)
                             REFERENCES REASONCODE ) ;


ALTER TABLE STOCKVALUEAVGPP
       ADD  ( CONSTRAINT STOCKVALUEAVGPP_STOCKTRANSACTI
              FOREIGN KEY (STOCKTRANSACTION_ID)
                             REFERENCES STOCKTRANSACTION ) ;


ALTER TABLE STOCKVALUEAVGPP
       ADD  ( CONSTRAINT STOCKVALUEAVGPP_CUSTOMER_LINK
              FOREIGN KEY (CUSTOMER_LINK_ID)
                             REFERENCES CUSTOMER_LINK ) ;


ALTER TABLE STOCKVALUEAVGPP
       ADD  ( CONSTRAINT STOCKVALUEAVGPP_QUALITYGRADE_I
              FOREIGN KEY (QUALITYGRADE_ID)
                             REFERENCES QUALITYGRADE ) ;


ALTER TABLE STOCKVALUEAVGPP
       ADD  ( CONSTRAINT STOCKVALUEAVGPP_SIZEDEFINITION
              FOREIGN KEY (SIZEDEFINITION_LINK_ID)
                             REFERENCES SIZEDEFINITION_LINK ) ;


ALTER TABLE STOCKVALUEAVGPP
       ADD  ( CONSTRAINT STOCKVALUEAVGPP_PRODUCT_LINK_I
              FOREIGN KEY (PRODUCT_LINK_ID)
                             REFERENCES PRODUCT_LINK ) ;


ALTER TABLE WORKORDER
       ADD  ( CONSTRAINT WORKORDER_PROCESSINGSTOCKROOM
              FOREIGN KEY (PROCESSINGSTOCKROOM_ID)
                             REFERENCES STOCKROOM ) ;



