/*order 502403*/

ALTER TABLE STOCKTRANSACTION  ADD (TRANSACTIONTIMESTAMP  DATE NULL);
ALTER TABLE STOCKTRANSACTION  ADD (FROMSTOCKROOMLOCATION_ID  NUMBER NULL);
ALTER TABLE STOCKTRANSACTION  ADD (TOSTOCKROOMLOCATION_ID  NUMBER NULL);

UPDATE STOCKTRANSACTION ST SET ST.TRANSACTIONTIMESTAMP=TO_DATE(ST.TRANSACTIONDATE, 'DD/MM/YYYY HH:MI:SS')+ 7/24;
COMMIT;


-- ADD/MODIFY COLUMNS 
ALTER TABLE STOCKTRANSACTION MODIFY (TRANSACTIONTIMESTAMP NOT NULL);

ALTER TABLE STOCKTRANSACTION
       ADD  ( CONSTRAINT STOCKTRANSACTION_TOSTOCKROOM
              FOREIGN KEY (TOSTOCKROOMLOCATION_ID)
                             REFERENCES STOCKROOMLOCATION ) ;


ALTER TABLE STOCKTRANSACTION
       ADD  ( CONSTRAINT STOCKTRANSACTION_FROMSTOCKROOM
              FOREIGN KEY (FROMSTOCKROOMLOCATION_ID)
                             REFERENCES STOCKROOMLOCATION ) ;

DROP INDEX XAK1STOCKTRANSACTION;

CREATE UNIQUE INDEX XAK1STOCKTRANSACTION ON STOCKTRANSACTION
(
       STOCKRESERVATIONPERGRADE_ID    ASC,
       TRANSACTIONDATE                ASC,
       REASONCODE_ID                  ASC,
       TRANSACTIONTYPE_ID             ASC,
       UNIQUESTOCK                    ASC,
       TRANSACTIONTIMESTAMP           
);

CREATE INDEX XIF4STOCKTRANSACTION ON STOCKTRANSACTION
(
       FROMSTOCKROOMLOCATION_ID       ASC
);

CREATE INDEX XIF5STOCKTRANSACTION ON STOCKTRANSACTION
(
       TOSTOCKROOMLOCATION_ID       ASC
);

      /*
      ACTION is CREATE Table PACKINGNOTESTOCKTRANSACTION
      */

CREATE TABLE PACKINGNOTESTOCKTRANSACTION (
       STOCKTRANSACTION_ID  NUMBER NOT NULL,
       PACKINGNOTELINE_ID   NUMBER NOT NULL,
       QUANTITY             NUMBER NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1PACKINGNOTESTOCKTRANSACTIO ON PACKINGNOTESTOCKTRANSACTION
(
       STOCKTRANSACTION_ID            ASC
);

CREATE INDEX XIF2PACKINGNOTESTOCKTRANSACTIO ON PACKINGNOTESTOCKTRANSACTION
(
       PACKINGNOTELINE_ID             ASC
);


ALTER TABLE PACKINGNOTESTOCKTRANSACTION
       ADD  ( CONSTRAINT XPKPACKINGNOTESTOCKTRANSACTION PRIMARY KEY (
              STOCKTRANSACTION_ID, PACKINGNOTELINE_ID) ) ;


ALTER TABLE PACKINGNOTESTOCKTRANSACTION
       ADD  ( CONSTRAINT PACKINGNOTESTOCKTRANSACTION_PA
              FOREIGN KEY (PACKINGNOTELINE_ID)
                             REFERENCES PACKINGNOTELINE ) ;


ALTER TABLE PACKINGNOTESTOCKTRANSACTION
       ADD  ( CONSTRAINT PACKINGNOTESTOCKTRANSACTION_ST
              FOREIGN KEY (STOCKTRANSACTION_ID)
                             REFERENCES STOCKTRANSACTION ) ;
      
      /*
      CHANGE REPORT for Table REASONCODE
          - Adding column SERVICETRUCK
      ACTION is ALTER or modify Table REASONCODE
      */

ALTER TABLE REASONCODE ADD (SERVICETRUCK         VARCHAR2(1) DEFAULT 'N' NOT 
    NULL
                                   CONSTRAINT YN_ValidationNew161
                                          CHECK (SERVICETRUCK IN ('Y', 'N')));

      
      /*
      ACTION is CREATE Table ISAPEXPORT
      */

CREATE SEQUENCE SEQ_ISAPEXPORT
START WITH 1
INCREMENT BY 1;

CREATE TABLE ISAPEXPORT (
       ISAPEXPORT_ID        NUMBER NOT NULL,
       BUSINESSUNIT_ID      NUMBER NULL,
       CREATIONTIMESTAMP    DATE NOT NULL,
       TIMESTAMP            DATE NOT NULL,
       EXPORTTIMESTAMP      DATE NOT NULL,
       EXPORTED             VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew162
                                          CHECK (EXPORTED IN ('Y', 'N')),
       ERRORMSG             VARCHAR2(2000) NULL,
       F#BU                 VARCHAR2(3) NOT NULL,
       JJBU                 VARCHAR2(4) NULL,
       MMBU                 VARCHAR2(2) NULL,
       VARBU                VARCHAR2(2) NULL,
       PCBU                 VARCHAR2(2) NULL,
       KTBU                 VARCHAR2(10) NULL,
       PKTBU                VARCHAR2(10) NULL,
       DKBU                 VARCHAR2(1) NULL
                                   CONSTRAINT DKBU_Validation3
                                          CHECK (DKBU IN ('D', 'K')),
       BABU                 VARCHAR2(3) NULL,
       B#BU                 VARCHAR2(10) NULL,
       OPBU                 VARCHAR2(1) NULL
                                   CONSTRAINT OPBU_Validation6
                                          CHECK (OPBU IN ('J', 'N')),
       OP#BU                VARCHAR2(10) NULL,
       DTBU                 VARCHAR2(8) NULL,
       VADTBU               VARCHAR2(8) NULL,
       BTBU                 VARCHAR2(13) NULL,
       SHBU                 VARCHAR2(1) NULL
                                   CONSTRAINT SH_Validation2
                                          CHECK (SHBU IN ('S', 'H')),
       MVOBU                VARCHAR2(1) NULL
                                   CONSTRAINT MVOBU_Validation2
                                          CHECK (MVOBU IN ('M', 'V', '0', 'E')),
       MWCBU                VARCHAR2(2) NULL,
       BMWBU                VARCHAR2(13) NULL,
       WHBU                 VARCHAR2(3) NULL,
       KUBU                 VARCHAR2(11) NULL,
       KUFBU                VARCHAR2(1) NULL,
       BWHBU                VARCHAR2(15) NULL,
       KTGBU                VARCHAR2(10) NULL,
       PKTGBU               VARCHAR2(10) NULL,
       DKGBU                VARCHAR2(1) NULL
                                   CONSTRAINT DKBU_Validation4
                                          CHECK (DKGBU IN ('D', 'K')),
       T1BU                 VARCHAR2(30) NULL,
       T2BU                 VARCHAR2(30) NULL,
       KOABU                VARCHAR2(5) NULL,
       KSTBU                VARCHAR2(5) NULL,
       O#1BU                VARCHAR2(7) NULL,
       O#2BU                VARCHAR2(5) NULL,
       MEBU                 VARCHAR2(3) NULL,
       MGBU                 VARCHAR2(9) NULL,
       ZF1BU                VARCHAR2(10) NULL,
       ZF2BU                VARCHAR2(10) NULL,
       ZF3BU                VARCHAR2(10) NULL,
       ZF4BU                VARCHAR2(10) NULL,
       ZF5BU                VARCHAR2(10) NULL,
       ZF6BU                VARCHAR2(10) NULL,
       EULIBU               VARCHAR2(1) NULL
                                   CONSTRAINT OPBU_Validation7
                                          CHECK (EULIBU IN ('J', 'N')),
       EULABU               VARCHAR2(1) NULL,
       EUBU                 VARCHAR2(3) NULL,
       EUKUBU               VARCHAR2(11) NULL,
       EUBTBU               VARCHAR2(15) NULL,
       INBU                 VARCHAR2(10) NULL,
       PNRBU                VARCHAR2(5) NULL,
       KZ1BU                VARCHAR2(1) NULL,
       KZ2BU                VARCHAR2(2) NULL,
       KZ3BU                VARCHAR2(3) NULL,
       KZ4BU                VARCHAR2(4) NULL,
       KZ5BU                VARCHAR2(5) NULL,
       FF1BU                VARCHAR2(5) NULL,
       FF2BU                VARCHAR2(5) NULL,
       FF3BU                VARCHAR2(5) NULL,
       FF4BU                VARCHAR2(5) NULL,
       FF5BU                VARCHAR2(5) NULL,
       FF6BU                VARCHAR2(13) NULL,
       FF7BU                VARCHAR2(13) NULL,
       FF8BU                VARCHAR2(13) NULL,
       FF9BU                VARCHAR2(13) NULL,
       TBBU                 VARCHAR2(2) NULL,
       DIMBU                VARCHAR2(5) NULL,
       JRNBU                VARCHAR2(1) NULL
                                   CONSTRAINT OPBU_Validation8
                                          CHECK (JRNBU IN ('J', 'N')),
       JR#BU                VARCHAR2(10) NULL,
       EJBU                 VARCHAR2(6) NULL,
       EDBU                 VARCHAR2(8) NULL,
       EZBU                 VARCHAR2(6) NULL,
       EUSBU                VARCHAR2(10) NULL,
       E#BU                 VARCHAR2(7) NULL,
       VJBU                 VARCHAR2(6) NULL,
       VDBU                 VARCHAR2(8) NULL,
       VZBU                 VARCHAR2(6) NULL,
       VUSBU                VARCHAR2(10) NULL,
       V#BU                 VARCHAR2(7) NULL,
       FIXBU                VARCHAR2(1) NULL
                                   CONSTRAINT OPBU_Validation9
                                          CHECK (FIXBU IN ('J', 'N')),
       FJBU                 VARCHAR2(6) NULL,
       FDBU                 VARCHAR2(8) NULL,
       FZBU                 VARCHAR2(6) NULL,
       FUSBU                VARCHAR2(10) NULL,
       SABU                 VARCHAR2(1) NULL,
       MOBU                 VARCHAR2(1) NULL
                                   CONSTRAINT OPBU_Validation10
                                          CHECK (MOBU IN ('J', 'N'))
);

CREATE INDEX XIE1ISAPEXPORT ON ISAPEXPORT
(
       BUSINESSUNIT_ID                ,
       EXPORTED                       ,
       EXPORTTIMESTAMP                
);

CREATE INDEX XIF1ISAPEXPORT ON ISAPEXPORT
(
       BUSINESSUNIT_ID                ASC
);


ALTER TABLE ISAPEXPORT
       ADD  ( CONSTRAINT XPKISAPEXPORT PRIMARY KEY (ISAPEXPORT_ID) ) ;


ALTER TABLE ISAPEXPORT
       ADD  ( CONSTRAINT ISAPEXPORT_BUSINESSUNIT_ID
              FOREIGN KEY (BUSINESSUNIT_ID)
                             REFERENCES BUSINESSUNIT ) ;

---------------------------insert into transactiontype table
insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 414,
         'Add To Truck Stock (Correction)',
         getminsystemuser(),
         sysdate
    from dual;
insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 415, 'Move New Item From Truck Stock', getminsystemuser(), sysdate
    from dual;
insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 416,
         'Move Used Item From Truck Stock',
         getminsystemuser(),
         sysdate
    from dual;
insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 422,
         'In Circ Via Issue From Truck Stock (WO)',
         getminsystemuser(),
         sysdate
    from dual;
insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 423,
         'In Circ Via Issue From Truck Stock (MO)',
         getminsystemuser(),
         sysdate
    from dual;
insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 424,
         'In Circ Via Issue From Truck Stock (PN)',
         getminsystemuser(),
         sysdate
    from dual;
insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 444,
         'Ragged From Truck Stock (Correction)',
         getminsystemuser(),
         sysdate
    from dual;
insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 450, 'Sold From Truck Stock', getminsystemuser(), sysdate
    from dual;
insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 453, 'Sold From Truck Stock With MO', getminsystemuser(), sysdate
    from dual;
insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 454, 'Sold From Truck Stock With PN', getminsystemuser(), sysdate
    from dual;

insert into transactiontype_desc
  (transactiontype_id, language_id, description, systemuser_id, timestamp)
  select transactiontype_id,
         getminlanguage(),
         factorydescription,
         systemuser_id,
         timestamp
    from transactiontype
   where transactiontype.transactiontype_id >= 414
     and transactiontype.transactiontype_id <= 454;

-------------
insert into process(process_id,description,active) values ('83','Collect HTS Financial Stock Transactions','Y');
insert into process(process_id,description,active) values ('84','Export HTS Financial Stock Transactions','Y');

-------------
UPDATE ledgeraccnttype lat
SET lat.CODE = 'WESV'
WHERE lat.CODE = 'WES';

insert into ledgeraccnttype
  (ledgeraccnttype_id,
   code,
   factorydescription,
   systemuser_id,
   timestamp,
   active)
select
  seq_ledgeraccnttype.nextval,
   'WESS',
   'Cost of material service contracts',
   getminsystemuser(),
   sysdate,
   'Y'
   from dual;
   
insert into ledgeraccnttype
  (ledgeraccnttype_id,
   code,
   factorydescription,
   systemuser_id,
   timestamp,
   active)
select
  seq_ledgeraccnttype.nextval,
   'WESR',
   'Cost of material spare parts',
   getminsystemuser(),
   sysdate,
   'Y'
   from dual;

insert into ledgeraccnttype_desc
  (ledgeraccnttype_id, language_id, description, systemuser_id, timestamp)
  select ledgeraccnttype_id,
         getminlanguage(),
         factorydescription,
         systemuser_id,
         timestamp
    from ledgeraccnttype lt
   where lt.code in ('WESS', 'WESR');

