/********************************************
        link records
********************************************/
update {p_mydt10tbl0} set nr01=0;
/*peg_end*/

update {p_mydt10tbl0} as tbl1, mydt12 
set tbl1.nr01=mydt12.nr01
where tbl1.mydt10=mydt12.mydt10
   && tbl1.linenumber=mydt12.linenumber;
/*peg_end*/

/********************************************************
    mark mydt12 records to be deleted
********************************************************/
update (
       select mydt10.*
       from mydt10, mydt11
       where mydt10.nr01=mydt11.mydt10
          && {peg_arr[1]}
       group by mydt10.nr01   
       ) as tbl2, mydt12
left join {p_mydt10tbl0} as tbl1 on tbl1.nr01=mydt12.nr01
set mydt12.remguc='{peg_str1}'
where tbl1.nr01 is null
   && mydt12.mydt10=tbl2.nr01;
/*peg_end*/   

/********************************************************
          update old records
********************************************************/
update {p_mydt10tbl0} as tbl1, mydt12 
set mydt12.linenumber  = tbl1.linenumber  ,
    mydt12.mydt10      = tbl1.mydt10      ,
    mydt12.mydt57_p00  = tbl1.mydt57_p00  ,
    mydt12.mydt58_p00  = tbl1.mydt58_p00  ,
    mydt12.mydt61_p00  = tbl1.mydt61_p00  ,
    mydt12.mydt62_p00  = tbl1.mydt62_p00  ,
    mydt12.mydt62_p01  = tbl1.mydt62_p01  ,
    mydt12.quantity    = tbl1.quantity    ,
    mydt12.munit       = tbl1.munit       ,
    mydt12.invdtype    = tbl1.invdtype    ,
    mydt12.netvalue    = tbl1.netvalue    ,
    mydt12.vatcat      = tbl1.vatcat      ,
    mydt12.vatexcat    = tbl1.vatexcat    ,
    mydt12.appid       = tbl1.appid       ,
    mydt12.appdate     = tbl1.appdate     ,
    mydt12.doy         = tbl1.doy         ,
    mydt12.shipid      = tbl1.shipid      ,
    mydt12.discoption  = tbl1.discoption  ,
    mydt12.wthhldpcat  = tbl1.wthhldpcat  ,
    mydt12.wthhld_am   = tbl1.wthhld_am   ,
    mydt12.stamppcat   = tbl1.stamppcat   ,
    mydt12.feestype    = tbl1.feestype    ,
    mydt12.fees_am     = tbl1.fees_am     ,
    mydt12.otaxpcat    = tbl1.otaxpcat    ,
    mydt12.otax_am     = tbl1.otax_am     ,
    mydt12.fpavalue    = tbl1.fpavalue    ,
    mydt12.stampvalue  = tbl1.stampvalue  ,
    mydt12.deducvalue  = tbl1.deducvalue  ,
    /*v 1.8*/
    mydt12.taxtype     = tbl1.taxtype     ,
    mydt12.taxcat      = tbl1.taxcat      ,
    mydt12.taxamount   = tbl1.taxamount   ,
    mydt12.taxunder    = tbl1.taxunder    ,
    
    mydt12.remdate = curdate(),        
    mydt12.remtime = curtime(),        
    mydt12.remuser = '{user_pswd}'
where mydt12.nr01=tbl1.nr01
   && tbl1.nr01<>0
   &&(    mydt12.linenumber  <> tbl1.linenumber 
       || mydt12.mydt10      <> tbl1.mydt10     
       || mydt12.mydt57_p00  <> tbl1.mydt57_p00 
       || mydt12.mydt58_p00  <> tbl1.mydt58_p00 
       || mydt12.mydt61_p00  <> tbl1.mydt61_p00 
       || mydt12.mydt62_p00  <> tbl1.mydt62_p00 
       || mydt12.mydt62_p01  <> tbl1.mydt62_p01 
       || mydt12.quantity    <> tbl1.quantity   
       || mydt12.munit       <> tbl1.munit      
       || mydt12.invdtype    <> tbl1.invdtype   
       || mydt12.netvalue    <> tbl1.netvalue   
       || mydt12.vatcat      <> tbl1.vatcat     
       || mydt12.vatexcat    <> tbl1.vatexcat   
       || mydt12.appid       <> tbl1.appid      
       || mydt12.appdate     <> tbl1.appdate    
       || mydt12.doy         <> tbl1.doy        
       || mydt12.shipid      <> tbl1.shipid     
       || mydt12.discoption  <> tbl1.discoption 
       || mydt12.wthhldpcat  <> tbl1.wthhldpcat 
       || mydt12.wthhld_am   <> tbl1.wthhld_am  
       || mydt12.stamppcat   <> tbl1.stamppcat  
       || mydt12.feestype    <> tbl1.feestype   
       || mydt12.fees_am     <> tbl1.fees_am    
       || mydt12.otaxpcat    <> tbl1.otaxpcat   
       || mydt12.otax_am     <> tbl1.otax_am    
       || mydt12.fpavalue    <> tbl1.fpavalue    
       || mydt12.stampvalue  <> tbl1.stampvalue  
       || mydt12.deducvalue  <> tbl1.deducvalue
       /*v 1.8*/
       || mydt12.taxtype     <> tbl1.taxtype
       || mydt12.taxcat      <> tbl1.taxcat
       || mydt12.taxamount   <> tbl1.taxamount
       || mydt12.taxunder    <> tbl1.taxunder
     ); 
/*peg_end*/
        
        
/*************************************************************
     insert new records
*************************************************************/
insert into mydt12
(nr01, linenumber, mydt10, mydt57_p00, mydt58_p00, mydt61_p00, mydt62_p00, mydt62_p01, quantity, munit, invdtype, 
 netvalue, vatcat, vatexcat, appid, appdate, doy, shipid, discoption, 
 wthhldpcat, wthhld_am, stamppcat, feestype, fees_am, otaxpcat, otax_am, 
 fpavalue, stampvalue, deducvalue, 
 /*v 1.8*/ 
 taxtype, taxcat, taxamount, taxunder,
 remfdate, remftime, remfuser, remdate, remtime, remuser)
        
    select peg_new_nr01('mydt12', 1),
           tbl1.linenumber  ,    
           tbl1.mydt10      ,    
           tbl1.mydt57_p00  ,    
           tbl1.mydt58_p00  ,    
           tbl1.mydt61_p00  ,    
           tbl1.mydt62_p00  ,    
           tbl1.mydt62_p01  ,    
           tbl1.quantity    ,    
           tbl1.munit       ,    
           tbl1.invdtype    ,    
           tbl1.netvalue    ,    
           tbl1.vatcat      ,    
           tbl1.vatexcat    ,    
           tbl1.appid       ,    
           tbl1.appdate     ,    
           tbl1.doy         ,    
           tbl1.shipid      ,    
           tbl1.discoption  ,    
           tbl1.wthhldpcat  ,    
           tbl1.wthhld_am   ,    
           tbl1.stamppcat   ,    
           tbl1.feestype    ,    
           tbl1.fees_am     ,    
           tbl1.otaxpcat    ,    
           tbl1.otax_am     ,    
           tbl1.fpavalue    , 
           tbl1.stampvalue  , 
           tbl1.deducvalue  , 
           /*v 1.8*/ 
           tbl1.taxtype     , 
           tbl1.taxcat      , 
           tbl1.taxamount   , 
           tbl1.taxunder    ,

           curdate() as remfdate, 
           curtime() as remftime, 
           '{user_pswd}' as remfuser, 
           curdate() as remdate, 
           curtime() as remtime, 
           '{user_pswd}' as remuser
    from {p_mydt10tbl0} as tbl1
    where tbl1.nr01=0;
/*peg_end*/    
           