/*****************************************************
       update old records 
*****************************************************/
update mydt10, {p_mydt10tbl1} as tbl1
set mydt10.is_vat         = tbl1.is_vat     ,
    mydt10.is_country     = tbl1.is_country ,
    mydt10.is_branch      = tbl1.is_branch  ,
    mydt10.is_name        = tbl1.is_name    ,
    mydt10.is_address     = tbl1.is_address ,
    mydt10.is_city        = tbl1.is_city    ,
    mydt10.is_zip         = tbl1.is_zip     ,
    mydt10.cp_vat         = tbl1.cp_vat     ,
    mydt10.cp_country     = tbl1.cp_country ,
    mydt10.cp_branch      = tbl1.cp_branch  ,
    mydt10.cp_name        = tbl1.cp_name    ,
    mydt10.cp_address     = tbl1.cp_address ,
    mydt10.cp_city        = tbl1.cp_city    ,
    mydt10.cp_zip         = tbl1.cp_zip     ,
    mydt10.series         = tbl1.series     ,
    mydt10.aa             = tbl1.aa         ,
    mydt10.issuedate      = tbl1.issuedate  ,
    mydt10.issuetime      = tbl1.issuetime  ,
    mydt10.invtype        = tbl1.invtype    ,
    mydt10.vatpaysusp     = tbl1.vatpaysusp ,
    mydt10.currency       = tbl1.currency   ,
    mydt10.exchrate       = tbl1.exchrate   ,
    mydt10.pcor           = ifnull(tbl1.pcor, ''),
    mydt10.correlinv      = ifnull(tbl1.correlinv, ''),
    mydt10.slfpricing     = tbl1.slfpricing ,
    mydt10.dispdate       = tbl1.dispdate   ,
    mydt10.disptime       = tbl1.disptime   ,
    mydt10.vehiclenum     = tbl1.vehiclenum ,
    mydt10.mvpurpose      = tbl1.mvpurpose  ,
    mydt10.paytype        = tbl1.paytype    ,
    mydt10.tnetvalue      = tbl1.tnetvalue  ,
    mydt10.tvat_am        = tbl1.tvat_am    ,
    mydt10.twthhld_am     = tbl1.twthhld_am ,
    mydt10.tfees_am       = tbl1.tfees_am   ,
    mydt10.tstamp_am      = tbl1.tstamp_am  ,
    mydt10.ttax_am        = tbl1.ttax_am    ,
    mydt10.tdeduction     = tbl1.tdeduction ,
    mydt10.tgross_val     = tbl1.tgross_val ,
    mydt10.p00            = tbl1.p00        ,
    mydt10.tbl            = tbl1.tbl        ,
    mydt10.tbl_nr01       = tbl1.tbl_nr01   ,
    mydt10.p01            = tbl1.p01        ,
    mydt10.p03            = tbl1.p03        ,
    mydt10.p06            = tbl1.p06        ,
    mydt10.p07            = tbl1.p07        ,
    mydt10.p08d           = tbl1.p08d       ,
    mydt10.p08t           = tbl1.p08t       ,
    mydt10.p30            = ''              ,
    mydt10.remdate        =curdate(), 
    mydt10.remtime        =curtime(), 
    mydt10.remuser        ='{user_pswd}'
WHERE mydt10.nr01=tbl1.nr01
   && (   mydt10.is_vat         <> tbl1.is_vat     
       || mydt10.is_country     <> tbl1.is_country 
       || mydt10.is_branch      <> tbl1.is_branch  
       || mydt10.is_name        <> tbl1.is_name    
       || mydt10.is_address     <> tbl1.is_address 
       || mydt10.is_city        <> tbl1.is_city    
       || mydt10.is_zip         <> tbl1.is_zip     
       || mydt10.cp_vat         <> tbl1.cp_vat     
       || mydt10.cp_country     <> tbl1.cp_country 
       || mydt10.cp_branch      <> tbl1.cp_branch  
       || mydt10.cp_name        <> tbl1.cp_name    
       || mydt10.cp_address     <> tbl1.cp_address 
       || mydt10.cp_city        <> tbl1.cp_city    
       || mydt10.cp_zip         <> tbl1.cp_zip     
       || mydt10.series         <> tbl1.series     
       || mydt10.aa             <> tbl1.aa         
       || mydt10.issuedate      <> tbl1.issuedate
       || mydt10.issuetime      <> tbl1.issuetime
       || mydt10.invtype        <> tbl1.invtype    
       || mydt10.vatpaysusp     <> tbl1.vatpaysusp 
       || mydt10.currency       <> tbl1.currency   
       || mydt10.exchrate       <> tbl1.exchrate   
       || ifnull(mydt10.pcor, '')<> ifnull(tbl1.pcor, '')
       || ifnull(mydt10.correlinv, '')<> ifnull(tbl1.correlinv, '')
       || mydt10.slfpricing     <> tbl1.slfpricing 
       || mydt10.dispdate       <> tbl1.dispdate   
       || mydt10.disptime       <> tbl1.disptime   
       || mydt10.vehiclenum     <> tbl1.vehiclenum 
       || mydt10.mvpurpose      <> tbl1.mvpurpose  
       || mydt10.paytype        <> tbl1.paytype  
       || mydt10.tnetvalue      <> tbl1.tnetvalue  
       || mydt10.tvat_am        <> tbl1.tvat_am    
       || mydt10.twthhld_am     <> tbl1.twthhld_am 
       || mydt10.tfees_am       <> tbl1.tfees_am   
       || mydt10.tstamp_am      <> tbl1.tstamp_am  
       || mydt10.ttax_am        <> tbl1.ttax_am    
       || mydt10.tdeduction     <> tbl1.tdeduction 
       || mydt10.tgross_val     <> tbl1.tgross_val 
       || mydt10.p00            <> tbl1.p00
       || mydt10.tbl            <> tbl1.tbl
       || mydt10.tbl_nr01       <> tbl1.tbl_nr01
       || mydt10.p01            <> tbl1.p01
       || mydt10.p03            <> tbl1.p03
       || mydt10.p06            <> tbl1.p06
       || mydt10.p07            <> tbl1.p07
       || mydt10.p08d           <> tbl1.p08d
       || mydt10.p08t           <> tbl1.p08t
      );
/*peg_end*/          

/****************************************************
       insert new records
****************************************************/
INSERT INTO mydt10
(nr01, pnr01, is_vat, is_country, is_branch, is_name, is_address, is_city, is_zip, 
 cp_vat, cp_country, cp_branch, cp_name, cp_address, cp_city,
 cp_zip, series, aa, issuedate, issuetime, invtype, vatpaysusp, currency, exchrate, pcor, correlinv, slfpricing, 
 dispdate, disptime, vehiclenum, mvpurpose, paytype, tnetvalue, 
 tvat_am, twthhld_am, tfees_am, tstamp_am, ttax_am, tdeduction, tgross_val, 
 p00, tbl, tbl_nr01, p01, p03, p06, p07, p08d, p08t, 
 remfdate, remftime, remfuser, remdate, remtime, remuser)

SELECT tbl1.nr01 as nr01,  
       tbl1.pnr01 as pnr01,
       tbl1.is_vat     ,
       tbl1.is_country ,
       tbl1.is_branch  ,
       tbl1.is_name    ,
       tbl1.is_address ,
       tbl1.is_city    ,
       tbl1.is_zip     ,
       tbl1.cp_vat     ,
       tbl1.cp_country ,
       tbl1.cp_branch  ,
       tbl1.cp_name    ,
       tbl1.cp_address ,
       tbl1.cp_city    ,
       tbl1.cp_zip     ,
       tbl1.series     ,
       tbl1.aa         ,
       tbl1.issuedate  ,
       tbl1.issuetime  ,
       tbl1.invtype    ,
       tbl1.vatpaysusp ,
       tbl1.currency   ,
       tbl1.exchrate   ,
       ifnull(tbl1.pcor, ''),
       ifnull(tbl1.correlinv, ''),
       tbl1.slfpricing ,
       tbl1.dispdate   ,
       tbl1.disptime   ,
       tbl1.vehiclenum ,
       tbl1.mvpurpose  ,
       tbl1.paytype    ,
       tbl1.tnetvalue  ,
       tbl1.tvat_am    ,
       tbl1.twthhld_am ,
       tbl1.tfees_am   ,
       tbl1.tstamp_am  ,
       tbl1.ttax_am    ,
       tbl1.tdeduction ,
       tbl1.tgross_val ,
       tbl1.p00        ,
       tbl1.tbl        ,
       tbl1.tbl_nr01   ,
       tbl1.p01        ,
       tbl1.p03        ,
       tbl1.p06        ,
       tbl1.p07        ,
       tbl1.p08d       ,
       tbl1.p08t       ,

       curdate()     as remfdate, 
       curtime()     as remftime, 
       '{user_pswd}' as remfuser, 
       curdate()     as remfdate, 
       curtime()     as remftime, 
       '{user_pswd}' as remfuser
FROM {p_mydt10tbl1} as tbl1
LEFT JOIN mydt10 ON mydt10.nr01=tbl1.nr01
WHERE mydt10.nr01 is null;
/*peg_end*/        
       