/*ORDER 502295*/      
      /*
      CHANGE REPORT for Table TRUCKRELOAD
          - Adding column BASELINETYPE
          - Adding column BASELINETABLE_LINK_ID
          - Adding column BASELINETABLE
          - Changing definition of index XAK1TRUCKRELOAD
      ACTION is ALTER or modify Table TRUCKRELOAD
      */

DROP INDEX XAK1TRUCKRELOAD;

     
      /*
      ACTION is CREATE Table PICKLISTLINEAS
      */

CREATE SEQUENCE SEQ_PICKLISTLINEAS
START WITH 1
INCREMENT BY 1;

CREATE TABLE PICKLISTLINEAS (
       PICKLISTLINEAS_ID    NUMBER NOT NULL,
       PICKLIST_ID          NUMBER NOT NULL,
       PRODUCT_LINK_ID      NUMBER NOT NULL,
       SIZEDEFINITION_LINK_ID NUMBER NOT NULL,
       STOCKTYPE            NUMBER DEFAULT 1 NOT NULL,
       QTYREQUIRED          NUMBER DEFAULT 0 NOT NULL,
       QTYTOPICK            NUMBER DEFAULT 0 NOT NULL,
       QTYTRUCKDIFFERENCE   NUMBER DEFAULT 0 NOT NULL,
       QTYPICKED            NUMBER DEFAULT 0 NOT NULL,
       CONFIRMEDCOMPLETELY  VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew132
                                          CHECK (CONFIRMEDCOMPLETELY IN ('Y', 'N')),
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

COMMENT ON COLUMN PICKLISTLINEAS.STOCKTYPE IS '1 = Non Unique
2 = Unique
3 = Unique with Modifications';
CREATE UNIQUE INDEX XAK1PICKLISTLINEAS ON PICKLISTLINEAS
(
       PICKLIST_ID                  ASC,
       PRODUCT_LINK_ID              ASC,
       SIZEDEFINITION_LINK_ID       ASC,
       STOCKTYPE                    ASC  
);

CREATE INDEX XIF1PICKLISTLINEAS ON PICKLISTLINEAS
(
       PICKLIST_ID                    ASC
);

CREATE INDEX XIF2PICKLISTLINEAS ON PICKLISTLINEAS
(
       PRODUCT_LINK_ID                ASC
);

CREATE INDEX XIF3PICKLISTLINEAS ON PICKLISTLINEAS
(
       SIZEDEFINITION_LINK_ID         ASC
);


ALTER TABLE PICKLISTLINEAS
       ADD  ( CONSTRAINT XPKPICKLISTLINEAS PRIMARY KEY (
              PICKLISTLINEAS_ID) ) ;

      
      /*
      ACTION is CREATE Table PICKLISTLINEORIGINAS
      */

CREATE SEQUENCE SEQ_PICKLISTLINEORIGINAS
START WITH 1
INCREMENT BY 1;

CREATE TABLE PICKLISTLINEORIGINAS (
       PICKLISTLINEORIGINAS_ID NUMBER NOT NULL,
       PICKLISTLINEAS_ID    NUMBER NOT NULL,
       BASELINETYPE         NUMBER DEFAULT 1 NOT NULL,
       BASELINETABLE_LINK_ID NUMBER NOT NULL,
       BASELINETABLE        VARCHAR2(30) NOT NULL,
       QTYTOPICK            NUMBER DEFAULT 0 NOT NULL,
       QTYPICKED            NUMBER DEFAULT 0 NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

COMMENT ON COLUMN PICKLISTLINEORIGINAS.BASELINETYPE IS '1 = Work Order
2 = Work Order For Pool
3 = Web Work Order
4 = Route Assistant
5 = Conversion
6 = Sales Note
8 = Stock Delivery Order (Internal PO)
9 = Direct Stock Delivery Order
90 = Packing Note';
CREATE UNIQUE INDEX XAK1PICKLISTLINEORIGINAS ON PICKLISTLINEORIGINAS
(
       PICKLISTLINEAS_ID           ASC,
       BASELINETYPE                ASC,
       BASELINETABLE_LINK_ID       ASC
);

CREATE INDEX XIF1PICKLISTLINEORIGINAS ON PICKLISTLINEORIGINAS
(
       PICKLISTLINEAS_ID              ASC
);


ALTER TABLE PICKLISTLINEORIGINAS
       ADD  ( CONSTRAINT XPKPICKLISTLINEORIGINAS PRIMARY KEY (
              PICKLISTLINEORIGINAS_ID) ) ;

      
      /*
      CHANGE REPORT for Table PRODUCT
          - Adding column DELIVERFROMONPICKLISTRSC
      ACTION is ALTER or modify Table PRODUCT
      */

ALTER TABLE PRODUCT ADD (DELIVERFROMONPICKLISTRSC NUMBER DEFAULT 0 NOT NULL
                                   CONSTRAINT DeliverFromOnPickList_Valid
                                          CHECK (DELIVERFROMONPICKLISTRSC IN (0,
     1)));

COMMENT ON COLUMN PRODUCT.DELIVERFROMONPICKLISTRSC IS '0 = Active Stock
1 = Passive Stock';

      /*
      CHANGE REPORT for Table ROUTE
          - Adding column PRINTPICKLISTRSC
          - Adding column PRINTMOUNTINGORDER
          - Adding column PRINTDISMOUNTINGORDER
          - Adding column PRINTSALESNOTES
      ACTION is ALTER or modify Table ROUTE
      */

ALTER TABLE ROUTE ADD (PRINTPICKLISTRSC     VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew133
                                          CHECK (PRINTPICKLISTRSC IN ('Y', 'N'))
    );


ALTER TABLE ROUTE ADD (PRINTMOUNTINGORDER   VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew134
                                          CHECK (PRINTMOUNTINGORDER IN ('Y', 
    'N')));


ALTER TABLE ROUTE ADD (PRINTDISMOUNTINGORDER VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew135
                                          CHECK (PRINTDISMOUNTINGORDER IN ('Y', 
    'N')));


ALTER TABLE ROUTE ADD (PRINTSALESNOTES      VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew136
                                          CHECK (PRINTSALESNOTES IN ('Y', 'N')))
    ;


     /*
      CHANGE REPORT for Table TRUCKRELOAD
          - Adding column BASELINETYPE
          - Adding column BASELINETABLE_LINK_ID
          - Adding column BASELINETABLE
      ACTION is ALTER or modify Table TRUCKRELOAD
      */

ALTER TABLE TRUCKRELOAD ADD (BASELINETYPE         NUMBER DEFAULT 0 NOT NULL);


ALTER TABLE TRUCKRELOAD ADD (BASELINETABLE_LINK_ID NUMBER NULL);


ALTER TABLE TRUCKRELOAD ADD (BASELINETABLE        VARCHAR2(30) NULL);

COMMENT ON COLUMN TRUCKRELOAD.BASELINETYPE IS '0 = Not processed yet
1 = Processed by Pick List Line Active Stock
2 = Processed by Pick List Line Passive Stock';

CREATE UNIQUE INDEX XAK1TRUCKRELOAD ON TRUCKRELOAD
(
       TRUCK_ID                       ASC,
       PRODUCT_ID                     ASC,
       SIZEDEFINITION_ID              ASC,
       BASELINETYPE                   ASC,
       BASELINETABLE_LINK_ID          ASC
);

      
      /*
      CHANGE REPORT for Table WORKORDER
          - Adding column STOPNUMBER
      ACTION is ALTER or modify Table WORKORDER
      */

ALTER TABLE WORKORDER ADD (STOPNUMBER           NUMBER NULL);




ALTER TABLE PICKLISTLINEAS
       ADD  ( CONSTRAINT PICKLISTLINEAS_SIZEDEFINITION_
              FOREIGN KEY (SIZEDEFINITION_LINK_ID)
                             REFERENCES SIZEDEFINITION_LINK ) ;


ALTER TABLE PICKLISTLINEAS
       ADD  ( CONSTRAINT PICKLISTLINEAS_PRODUCT_LINK_ID
              FOREIGN KEY (PRODUCT_LINK_ID)
                             REFERENCES PRODUCT_LINK
                             ON DELETE CASCADE ) ;


ALTER TABLE PICKLISTLINEAS
       ADD  ( CONSTRAINT PICKLISTLINEAS_PICKLIST_ID
              FOREIGN KEY (PICKLIST_ID)
                             REFERENCES PICKLIST
                             ON DELETE CASCADE ) ;


ALTER TABLE PICKLISTLINEORIGINAS
       ADD  ( CONSTRAINT PICKLISTLINEORIGINAS_PICKLISTL
              FOREIGN KEY (PICKLISTLINEAS_ID)
                             REFERENCES PICKLISTLINEAS
                             ON DELETE CASCADE ) ;







