CL:CHGLIBL LIBL(QTEMP COMUPDDTA SYSTOOLS QGPL);

Set Session_User = 'HAUSER';


--+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
-- Data Change Table Reference
--+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
-- Insert
--****************************************************************************************************
Select ADID, ADLastChg, ADLastUsr, 
        a.* 
   from Address2 a
--   Where CustNo in ('20110', '20111')
--   Order By CustNo;
 Order By Adid;

--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Insert with VALUES
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Insert Into Address2 
       (CustNo,   CustName1,       CustName2, Contact,           Street,                                ZipCode,  City,   Country)
 Values('20110', 'Musée d''Orsay', '',        'Pierre LePieu',   'Esplanade Valéry Giscard d''Estaing', '75007',  'Paris', 'FR'),
       ('20111', 'Louvre',         '',        'Mathilde Mérimé', 'Quai Francois Mitterand',             '75001',  'Paris', 'FR');
Rollback;

Select *
  from Address2
  Where CustNo in ('20110', '20111');

------------------------------------------------------------------------------------------------------
-- Insert with Values in an Data Change Reference Table
------------------------------------------------------------------------------------------------------
Select * 
  From Final Table(Insert Into Address2 
                         (CustNo,   CustName1,       Contact,           Street,                                ZipCode,  City,    Country)
                   VAlues('20110', 'Musée d''Orsay', 'Pierre LePieu',   'Esplanade Valéry Giscard d''Estaing', '75007',  'Paris', 'FR'),
                         ('20111', 'Louvre',         'Mathilde Mérimé', 'Quai Francois Mitterand',             '75001',  'Paris', 'FR'));
Rollback;

--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++                  
-- New Release 7.6 TR1 - Column Names in the VALUES Clause
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Attention: INCLUDE clause is not allowed with this syntax!

-- Insert into Address2
       -- Values(CustNo    => '20110',
              -- CustName1 => 'Musée d''Orsay',
              -- Contact   => 'Pierre LePieu',
              -- Street    => 'Esplanade Valéry Giscard d''Estaing'
              -- ZipCode   => '75007'
              -- City      => 'Paris'
              -- Country   => 'FR');
              
Rollback;                         

------------------------------------------------------------------------------------------------------
-- Insert based on a SELECT-Statement
------------------------------------------------------------------------------------------------------
-- Select count(*)                         
Delete 
  From Address2
  Where CustNo between '20000' and '20050';
  
Commit;

Select *
  from ComUpdDta.AddressN
  Where CustNo between '20000' and '20050'
  Order By CustNo;

Insert into ComUpdDta.Address2
       (CUSTNO, CUSTNAME1, CUSTNAME2, CONTACT, STREET, ZIPCODE, CITY, COUNTRY)
 Select CUSTNO, CUSTNAME1, CUSTNAME2, CONTACT, STREET, ZIPCODE, CITY, COUNTRY
   from AddressN
   Where CustNo between '20000' and '20050';
   
Select *
  from ComUpdDta.Address2
  Where CustNo between '20000' and '20050'
  Order By CustNo;
  -- Order By Try_Cast(CustNo as Integer) ;;  
   
Rollback;   

Select *
  from Final Table(Insert into ComUpdDta.Address2
                               (CUSTNO, CUSTNAME1, CUSTNAME2, CONTACT, STREET, ZIPCODE, CITY, COUNTRY)
                         Select CUSTNO, CUSTNAME1, CUSTNAME2, CONTACT, STREET, ZIPCODE, CITY, COUNTRY
                            from AddressN
                            Where CustNo between '20000' and '20050')
  -- Order By Try_Cast(CustNo as Integer)  
  Order by Input Sequence;
                          ;  
Rollback;   

--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Overriding User Value  / Overriding System Value
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Select ADID, ADLastChg, ADLastUsr, a.*
  from ComUpdDta.Address2 a
  Where CustNo Between '20000' and '20050';
  
Select ADID, ADLastChg, ADLastUsr, a.*
  from ComUpdDta.AddressN a
  Where CustNo Between '20000' and '20050';  
  
-- Insert into a table with Generated Always Columns --> Generated Always Columns cannot be specified
Insert into ComUpdDta.Address2
            (CUSTNO, CUSTNAME1, CUSTNAME2, CONTACT, STREET, ZIPCODE, CITY, COUNTRY)
      Select CUSTNO, CUSTNAME1, CUSTNAME2, CONTACT, STREET, ZIPCODE, CITY, COUNTRY
        from AddressN x
        Where CustNo between '20000' and '20050';
Rollback;        

Select * from Address2;
  
-- Insert including Identity and other Generated Always Columns Specified 
-- --> Overriding User Name: Generated Columnn Values are newly created
-- Insert into ComUpdDta.Address2
       -- Select *
          -- from AddressN x
          -- Where CustNo between '20000' and '20050';
Rollback;      

-- Insert including Identity and other Generated Always Columns Specified 
-- --> Overriding User Name: Generated Columnn Values are newly created
Insert into ComUpdDta.Address2
   --        Overriding User Value
      Select *
        from AddressN x
        Where CustNo between '20000' and '20050'
      ;
Rollback;     

-- Insert including Identity and other Generated Always Columns Specified 
-- --> Overriding System Name: Identity Value from the Base table is kept
--                             other Generated Column (Values) cannot be specified and are newly populated
-- Selecting ALL Columns not allowed, Identity Column must be specified, all other Always Generated NOT
Insert into ComUpdDta.Address2
            Overriding System Value
      Select *
        from AddressN x
        Where CustNo between '20000' and '20050'
      ; 

Insert into ComUpdDta.Address2
            (ADID, CUSTNO, CUSTNAME1, CUSTNAME2, CONTACT, STREET, ZIPCODE, CITY, COUNTRY)
             Overriding System Value
      Select ADID, CUSTNO, CUSTNAME1, CUSTNAME2, CONTACT, STREET, ZIPCODE, CITY, COUNTRY
        from AddressN x
        Where CustNo between '20000' and '20050'
      ; 
Rollback;     

Select n.ADID       Orig_ADID,    a.ADID       After_ADID, 
       n.ADLastChg  Orig_LastChg, a.ADLastChg  After_LastChg,
       n.ADLastUsr  Orig_LastUsr, a.ADLastUsr  After_LastUsr,
       n.CustNo     Orig_CustNo,  a.CustNo     After_CustNo
from comUpdDta.AddressN n full join ComUpdDta.Address2 a on a.CustNo = n.CustNo
Where    a.CustNo between '20000' and '20050'
      or n.CustNo between '20000' and '20050';

--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- SQL Global Variable: REPLICATION_OVERRIDE
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- REPLICATION_OVERRIDE global variable can only be modified by a user who is authorized to the QIBM_DB_GENCOL_OVERRIDE function usage identifier!
Set QSYS2.Replication_Override = 'Y';

Insert into ComUpdDta.Address2
      Select *
        from AddressN x
        Where CustNo between '20000' and '20050'
      ;
Rollback;     

-- Reset Replication_Override to Default --> All generated always columns are set
Set QSYS2.Replication_Override = Default;

Insert into ComUpdDta.Address2
       Overriding User Value
       Select *
          from AddressN x
          Where CustNo between '20000' and '20050'
      ;
Rollback; 
Select n.ADID       Orig_ADID,    a.ADID       After_ADID, 
       n.CustNo     Orig_CustNo,  a.CustNo     After_CustNo,
       n.ADLastChg  Orig_LastChg, a.ADLastChg  After_LastChg,
       n.ADLastUsr  Orig_LastUsr, a.ADLastUsr  After_LastUsr,
       n.*, a.* 
from comUpdDta.AddressN n full join ComUpdDta.Address2 a on a.CustNo = n.CustNo
Where    a.CustNo between '20000' and '20050'
      or n.CustNo between '20000' and '20050';

Select * from ComUpdDta.Address2
Where CustNo between '20000' and '20050';

                     
Select ADID, ADLASTCHG, ADLASTUSR,
       a.*
  from Final Table(Insert into ComUpdDta.Address2
                               (ADID, CUSTNO, CUSTNAME1, CUSTNAME2, CONTACT, STREET, ZIPCODE, CITY, COUNTRY, ADLASTCHG, ADLASTUSR)
                               Overriding User Value                               
                         Select ADID, CUSTNO, CUSTNAME1, CUSTNAME2, CONTACT, STREET, ZIPCODE, CITY, COUNTRY, ADLASTCHG, ADLASTUSR
                            from AddressN x
                            Where CustNo between '20000' and '20050') a;                    
                   
Rollback; 


--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Data change table reference: INSERT - Include
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Insert into ComUpdDta.Address2
            (ADID, CUSTNO, CUSTNAME1, CUSTNAME2, CONTACT, STREET, ZIPCODE, CITY, COUNTRY)
             Overriding System Value
      Select ADID, CUSTNO, CUSTNAME1, CUSTNAME2, CONTACT, STREET, ZIPCODE, CITY, COUNTRY
        from AddressN x
        Where CustNo between '20000' and '20050';
Rollback; 


Select ADID,   Old_ADID,  ADLastChg, Old_ADLastChg,   ADLastUsr, Old_ADLastUsr,
       CUSTNO, CUSTNAME1, CUSTNAME2, CONTACT, STREET, ZIPCODE, CITY, COUNTRY
  from Final Table(Insert into ComUpdDta.Address3
                               (CUSTNO, CUSTNAME1, CUSTNAME2, CONTACT, STREET, ZIPCODE, CITY, COUNTRY)
                                Include (Old_ADID      Integer,
                                         Old_ADLastChg Timestamp(6),
                                         Old_ADLastUsr VarChar(128))   
                                                      
                         Select CUSTNO, CUSTNAME1, CUSTNAME2, CONTACT, STREET, ZIPCODE, CITY, COUNTRY,
                                ADID, ADLastChg, ADLastUsr 
                            from AddressN x
                            Where x.CustNo between '20000' and '20050') a;       
Rollback;   

Set QSYS2.Replication_Override = 'Y';

-- Insert into ComUpdDta.Address3
       -- Select *
         -- from Addressn
         -- Where CustNo between '20000' and '20050'; 
 -- 
   
Select ADID,   Old_ADID,  ADLastChg, Old_ADLastChg,   ADLastUsr, Old_ADLastUsr,
       CUSTNO, CUSTNAME1, CUSTNAME2, CONTACT, STREET, ZIPCODE, CITY, COUNTRY
  from Final Table(Insert into ComUpdDta.Address3
                               (ADID, CUSTNO, CUSTNAME1, CUSTNAME2, CONTACT, STREET, ZIPCODE, CITY, COUNTRY, ADLastChg, ADLastUsr)
                                Include (Old_ADID      Integer,
                                         Old_ADLastChg Timestamp(6),
                                         Old_ADLastUsr VarChar(128))   
                               -- Overriding System Value                           
                         Select ADID, CUSTNO, CUSTNAME1, CUSTNAME2, CONTACT, STREET, ZIPCODE, CITY, COUNTRY, ADLastChg, ADLastUsr,
                                ADID, ADLastChg, AdLastUsr 
                            from AddressN x
                            Where x.CustNo between '20000' and '20050') a;    
Rollback;
Set QSYS2.Replication_Override = Default;  

Select * from Address3
Where CustNo between '20000' and '20050'
;

Delete From Address3
Where CustNo Between '20000' and '20050';
Commit;    

--****************************************************************************************************
-- UpDate
--****************************************************************************************************
-- Update from multiple Tables
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Select * from ItemMast2;
Select * from ItemNoNew;

------------------------------------------------------------------------------------------------------
-- Update All Rows!!!
------------------------------------------------------------------------------------------------------
Update ItemMast2 a
   set a.ItemNo = Coalesce((Select ItemNew
                              from ItemNoNew
                              Where ItemOld = a.ItemNo), a.ItemNo);
;                              
Rollback;

------------------------------------------------------------------------------------------------------
-- Update only the necessary Rows
------------------------------------------------------------------------------------------------------
Update ItemMast2 a
   set a.ItemNo = (Select ItemNew
                     from ItemNoNew b
                     Where b.ItemOld = a.ItemNo)
   Where Exists (Select *
                   From ItemNoNew c
                   Where c.ItemOld = a.ItemNo);    
                                 
Rollback;

Select *
  from ItemMast2;
  
Select Old_ItemNo, x.*
  from New Table(Update ItemMast2 a
                    Include (Old_ItemNo Char(22))
                    
                    set a.ItemNo = (Select ItemNew
                                       from ItemNoNew b
                                       Where b.ItemOld = a.ItemNo),
                        Old_ItemNo = a.ItemNo               
                    Where Exists (Select *
                                     From ItemNoNew c
                                     Where c.ItemOld = a.ItemNo)) x
  Order By Old_ItemNo;     
  
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Data Change Reference
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Select * from ItemMastx;

------------------------------------------------------------------------------------------------------
-- Update Price - Based on the Item Group (Pos 1-2 in ItemNo
------------------------------------------------------------------------------------------------------
-- Item Group = CF             --> +  3%
--              HG             --> + 10 %
--              BS             --> +  5 %
--              51, 52, 53, 54 --> + 20%
--              Other          --> +  7 %
------------------------------------------------------------------------------------------------------
-- 1. Independent Statements
--....................................................................................................
Update ComUpdDta.ItemMast2 
   Set Price = Price * 1,03
   Where Left(ItemNo, 2) = 'CF';
   
Update ComUpdDta.ItemMast2 
   Set Price = Price * 1,10
   Where Left(ItemNo, 2) = 'HG';   

Update ComUpdDta.ItemMast2 
   Set Price = Price * 1,05
   Where Left(ItemNo, 2) = 'BS';      
   
Update ComUpdDta.ItemMast2 
   Set Price = Price * 1,20
   Where Left(ItemNo, 2) in ('51', '52', '53', '54');     
   
Update ComUpdDta.ItemMast2
   Set Price = Price * 1,07
   Where Left(ItemNo, 2) not in ('CF', 'HG', 'BS', '51', '52', '53', '54');       
  
Stop;   
Rollback;

Select Coalesce(o.ItemNo, n.ItemNo) ItemNox, Left(Coalesce(o.ItemNo, n.ItemNo), 2) Item_GRoup,
       o.Price Old_Price, n.Price New_Price, n.Price - o.Price Diff,
       o.IMID Old_IMID,   n.IMID Chg_IMID,
       o.*, n.*
  from ItemMast2 n full join ItemMastN o on n.ItemNo = o.ItemNo
  Order By Item_Group, ItemNox;

--....................................................................................................
-- 2. Single Statement with CASE Clause
--....................................................................................................
Update ItemMast2
   set Price = Price * Case When Left(ItemNo, 2) = 'CF' Then 1,03
                            When Left(ItemNo, 2) = 'HG' Then 1,10
                            When Left(ItemNo, 2) = 'BS' Then 1,05
                            When Left(ItemNo, 2) in ('51', '52', '53', '54') Then 1,20
                            Else 1,07
                       End;   
Rollback;                              
             
--....................................................................................................
-- 3. Data Change Reference - Update
--....................................................................................................
Select Left(ItemNo, 2) Item_Group, ItemNo, 
       Old_Price, Percent, Price as New_Price, 
       Price - Old_Price Diff,
       Cast(Round(Old_Price * (1,00 + Percent/100,00), 2) as Dec(9, 2)) Check_Calc
  from New Table(Update ItemMast2
                    Include (Old_Price Dec(9, 2),
                             Percent   SmallInt)
                    set Price = Round(Price * 
                                      Case When Left(ItemNo, 2) = 'CF' Then 1,03
                                           When Left(ItemNo, 2) = 'HG' Then 1,10
                                           When Left(ItemNo, 2) = 'BS' Then 1,05
                                           When Left(ItemNo, 2) in ('51', '52', '53', '54') Then 1,20
                                           Else 1,07
                                      End, 2),
                        Old_Price = Price,
                        Percent   = Case When Left(ItemNo, 2) = 'CF' Then  3
                                         When Left(ItemNo, 2) = 'HG' Then 10
                                         When Left(ItemNo, 2) = 'BS' Then  5
                                         When Left(ItemNo, 2) in ('51', '52', '53', '54') Then 20
                                         Else 7
                                    End)
  Order By Item_Group, ItemNo;   
  
  Rollback;
Stop;  
--****************************************************************************************************
-- Delete
--****************************************************************************************************  
Select a.*
       -- count(*) 
from Address2 a
Where Try_Cast(CustNo as Integer) between 200000 and 200050;


Delete 
  From Address2
  Where Try_Cast(CustNo as Integer) between 200000 and 200050;
Rollback;

Select ADRRN, x.*
  from Old Table(Delete from Address2 a 
                   Include (ADRRN bigint)
                   Set ADRRN = rrn(a)
                   Where Try_Cast(CustNo as Integer) between 200000 and 200050) x;
                   
Rollback;                   

-- Delete Order Detail for all orders delivered in Dezember 2024
-----------------------------------------------------------------------
Select * 
   from OrderHdrN
   Where DelDate between '2024-12-01' and '2024-12-30';

Select * 
   from OrderDetN;


-- Delivery Date in OrderHdr!!!
-- Retrieve the Order Header Ids for to delete the Order Details
Select Distinct ODOHID
  from OrderDetN Join OrderHdrN on ODOHID = OHID
  Where DelDate between '2024-12-01' and '2024-12-30';

-- unfortunately the syntax above cannot be used in a DELETE statement!
-- a sub-select in the WHERE Conditions must be used
Select * -- Distinct ODOHID 
  from OrderDetN
  Where ODOHID In (Select OHID
                     FRom OrderHdrN
                     Where DelDate between '2024-12-01' and '2024-12-30');
                     
-- Deleting the Order Details
--------------------------------------
-- --> Order Header Ids are returned, so the Order Headers can be also deleted
Select Distinct ODOHID
  from Old Table (Delete From OrderDetN
                    Where ODOHID In (Select OHID
                                        From OrderHdrN
                                        Where DelDate between '2024-12-01' and '2024-12-30'));      
Rollback;     

-- Dynamic Compond Statement = Procdeure for deleting Order Header 
--                             and associated Order Position delivered in December 2024
--               
Begin Atomic
   Declare LocOHID     Integer      Not NULL Default 0;
   Declare LocMsgText  VarChar(256) Not NULL Default '';
   Declare SQLCODE     Integer      Not NULL Default 0;
   
   Declare CsrDltHdr Cursor For
      Select Distinct ODOHID
         from Old Table (Delete From OrderDetN
                            Where ODOHID In (Select OHID
                                                From OrderHdrN
                                                Where DelDate between '2024-12-01' and '2024-12-30'));
   Declare Undo Handler for SQLEXCEPTION 
       Begin Get Diagnostics Condition 1 LocMsgText = MESSAGE_TEXT;
             Call Systools.LPrintF(SQLCODE concat ' ' concat LocMsgText);
             Close CsrDltHdr;
       End;          
                            
   Open CsrDltHdr;
   LeaveLoop:  Loop Fetch Next from CsrDltHdr into LocOHID;
                    If SQLCODE = 100 Then Leave LeaveLoop;
                    End If;
                    Select 'OrderNo '      concat Trim(OrderNo) concat 
                           ' for Company ' concat Company       concat 
                           ' with OHID '   concat OHID          concat
                           ' successfully deleted'
                      into LocMsgText     
                      from Old Table (Delete from OrderHdrN
                                        Where OHID = LocOHID);
                    Call SysTools.LPrintF(LocMsgText);                    
               End Loop;
               
   Close CsrDltHdr;      
                            
End; 

Rollback;   

-- Check Joblog
Select * 
   from Table(Joblog_Info('*'))
   Where Message_Timestamp >= Current_Timestamp - 3 Minutes
         and Message_Id is NULL;    
         
--****************************************************************************************************
-- Instead Of Trigger
--****************************************************************************************************   
-- Attention: Data Change Reference Table cannot be used for a View with an INSTEAD OF TRIGGER!!!
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Select * from ComUpdDta.OrderHdrN;
Select * from ComUpdDta.OrderDetN;

Select * from ComUpdDta.OrderHdr_OrderDet_V01;
Select * from ComUpdDta.OrderHdr_OrderDet_ItemMast_V01;

------------------------------------------------------------------------------------------------------
-- Delete will fail because OrderHdr_OrderDet_V01 is a joined view --> not updateable!!!
Delete from ComUpdDta.OrderHdr_OrderDet_V01
Where DelDate between '2024-12-01' and '2024-12-31';

Rollback;

Create Or Replace Trigger COMUPDDTA.ORDERHDR_ORDERDET_TRG_INDLT
       Instead Of Delete On COMUPDDTA.ORDERHDR_ORDERDET_V01 
       Referencing Old Row as o
       For Each Row 
       Mode Db2row
       -- Secured
   Begin Atomic
       Declare LocNbrRows  Integer Not NULL Default 0;
       Declare LocIsFound  Integer Not NULL Default 0;
       Declare Exit Handler for SQLException 
               Begin Declare LocErrMsg VarChar(256) Not NULL Default '';
                     Get Diagnostics Condition 1 LocErrMsg = MESSAGE_TEXT;
                     Call SysTools.LPrintF(LocErrMsg);
               End;  
       
       -- Delete Order Detail        
       Delete from OrderDetN
         where ODID = o.ODID;  
       Call SysTools.LPrintF('OrderPos '         concat o.OrderPos concat
                             ' for OrderNo '     concat o.OrderNo  concat
                             ' with OrderHdId '  concat o.OHID     concat
                             ' successfully deleted'); 

       -- Check if still positions available
       -- If No --> Delete Order Header Data 
       Set LocIsFound = 0;
       Select 1 into LocIsFound
         from OrderDetN
         Where ODOHID = o.OHID
         Limit 1;
         
       -- Delete Order Header
       If LocIsFound = 0 
          Then Delete from OrderHdrN
                  Where OHID = o.OHID; 
                  Call SysTools.LPrintF('OrderNo: '         concat o.OrderNo  concat 
                                        ' with OrderHdId '  concat o.OHID     concat
                                        ' successfully deleted'); 
       End If;
                            
   End;
                                           
Select *
  from Table(Joblog_Info('*'))
  Where Message_Timestamp >= Current_Timestamp - 3 Minutes
        and Message_Id is NULL;

-- Instead of Trigger ... with save to History File
------------------------------------------------------------ 
Delete from ComUpdDta.OrderHdr_OrderDet_V01
Where DelDate between '2024-12-01' and '2024-12-31';
Rollback;
Commit;
       
Create Or Replace Trigger COMUPDDTA.ORDERHDR_ORDERDET_TRG_INDLT
       Instead Of Delete On COMUPDDTA.ORDERHDR_ORDERDET_V01 
       Referencing Old Row as o
       For Each Row 
       Mode Db2row
       -- Secured
   Begin Atomic
       Declare LocNbrRows  Integer Not NULL Default 0;
       Declare LocIsFound  Integer Not NULL Default 0;
       Declare Exit Handler for SQLException 
               Begin Declare LocErrMsg VarChar(256) Not NULL Default '';
                     Get Diagnostics Condition 1 LocErrMsg = MESSAGE_TEXT;
                     Call SysTools.LPrintF(LocErrMsg);
               End;  
               
       -- Save Order Detail Data into History Tables
       Set QSYS2.Replication_Override = 'Y';
       
       Insert Into OrderDetH
              Select * 
                from OrderDetN
                Where ODID = o.ODID;     
       Get Diagnostics LocNbrRows = Row_Count;  
       Call SysTools.LPrintF(LocNbrRows          concat 
                             ' OrderPos '        concat o.OrderPos concat
                             ' with OrderDetId ' concat o.ODID     concat
                             ' for OrderNo '     concat o.OrderNo  concat
                             ' with OrderHdId '  concat o.OHID     concat
                             ' copied into History File ORDERDETH');             

       Set QSYS2.Replication_Override = Default;                                      
       
       -- Delete Order Detail Data from Order Detail Table                
       Delete from OrderDetN
         where ODID = o.ODID;  
       Get Diagnostics LocNbrRows = Row_Count;  
       Call SysTools.LPrintF(LocNbrRows          concat 
                             ' OrderPos '        concat o.OrderPos concat
                             ' with OrderDetId ' concat o.ODID     concat
                             ' for OrderNo '     concat o.OrderNo  concat
                             ' with OrderHdId '  concat o.OHID     concat
                             ' successfully deleted from ORDERDETN');
                                          
       -- Check if still positions available
       -- If No --> Save Order Header Data into history table
       --       --> Delete Order Header Data 
       Set LocIsFound = 0;
       Select 1 into LocIsFound
         from OrderDetN
         Where ODOHID = o.OHID
         Limit 1;
         
      -- Save Order Header Data into History Tables
       If LocIsFound = 0 
          Then Set QSYS2.Replication_Override = 'Y'; 
             
          Insert Into OrderHdrH
                 Select * 
                    from OrderHdrN
                    Where OHID = o.OHID;  
          Call SysTools.LPrintF('OrderNo: '         concat o.OrderNo  concat 
                                ' with OrderHdId '  concat o.OHID     concat
                                ' copied into History File ORDERHDH');                                          

           Set QSYS2.Replication_Override = Default;       
       
           Delete from OrderHdrN
              Where OHID = o.OHID; 
              Call SysTools.LPrintF('OrderNo: '         concat o.OrderNo  concat 
                                    ' with OrderHdId '  concat o.OHID     concat
                                    ' successfully deleted'); 
       End If;
       
 End; 
   
Select x.Message_Text, x.*
  from Table(Joblog_Info('*')) x
  Where     Message_Timestamp >= Current_Timestamp - 1 Minutes
        and Message_Id is NULL
        ;   
        
Select * from OrderHdrH;
Select * from OrderDetH;       

Select * from COMUPDDTA.ORDERHDR_ORDERDET_V01
Where Deldate between '2024-12-01' and '2024-12-31'; 

-- NOT ALLOWED!!!
-- Select *
  -- from Old Table(Delete from ComUpdDta.OrderHdr_OrderDet_V01
                    -- Where DelDate between '2024-12-01' and '2024-12-31');