/******************************************************************
   set line number =0 and marks as to be deleted lines not exists
******************************************************************/
update mydt11, 
       (SELECT mydt11.nr01
        FROM {p_mydt10tbl1} as tbl1
        LEFT JOIN mydt11 ON mydt11.mydt10=tbl1.nr01
        LEFT JOIN {p_mydt10tbl2} as tbl2 ON tbl2.nr01=mydt11.nr01
        WHERE mydt11.nr01 is not null
           && tbl2.nr01 is null
       ) as tbl01
SET mydt11.remguc='{p_mydt10mark}'
WHERE mydt11.nr01=tbl01.nr01;
/*peg_end*/          


/*****************************************************
       update old records 
*****************************************************/
update mydt10, mydt11, {p_mydt10tbl2} as tbl1
set mydt11.tbl            = tbl1.tbl          ,
    mydt11.tbl_nr01       = tbl1.tbl_nr01     ,
    mydt11.mydt10         = tbl1.mydt10       ,
    mydt11.mydt57_p00     = tbl1.mydt57_p00   ,
    mydt11.mydt58_p00     = tbl1.mydt58_p00   ,
    mydt11.mydt61_p00     = tbl1.mydt61_p00   ,
    mydt11.mydt62_p00     = tbl1.mydt62_p00   ,
    mydt11.mydt62_p01     = tbl1.mydt62_p01   ,
    mydt11.linenumber     = tbl1.linenumber   ,
    mydt11.quantity       = tbl1.quantity     ,
    mydt11.munit          = tbl1.munit        ,
    mydt11.invdtype       = tbl1.invdtype     ,
    mydt11.netvalue       = tbl1.netvalue     ,
    mydt11.vatcat         = tbl1.vatcat       ,
    mydt11.vatexcat       = tbl1.vatexcat     ,
    mydt11.fpavalue       = tbl1.fpavalue     ,
    mydt11.appid          = tbl1.appid        ,
    mydt11.appdate        = tbl1.appdate      ,
    mydt11.doy            = tbl1.doy          ,
    mydt11.shipid         = tbl1.shipid       ,
    mydt11.discoption     = tbl1.discoption   ,
    mydt11.wthhldpcat     = tbl1.wthhldpcat   ,
    mydt11.wthhld_am      = tbl1.wthhld_am    ,
    mydt11.stampvalue     = tbl1.stampvalue   ,
    mydt11.stamppcat      = tbl1.stamppcat    ,
    mydt11.feestype       = tbl1.feestype     ,
    mydt11.fees_am        = tbl1.fees_am      ,
    mydt11.otaxpcat       = tbl1.otaxpcat     ,
    mydt11.otax_am        = tbl1.otax_am      ,
    mydt11.deducvalue     = tbl1.deducvalue   ,
    mydt11.comments       = tbl1.comments     ,

    mydt11.taxtype        = tbl1.taxtype      ,
    mydt11.taxcat         = tbl1.taxcat       ,
    mydt11.taxamount      = tbl1.taxamount    ,
    mydt11.taxunder       = tbl1.taxunder     ,

    /*v1.18*/
    mydt11.p101           = tbl1.p101         ,
    mydt11.p101f          = tbl1.p101f        ,
    mydt11.p102           = tbl1.p102         ,
    mydt11.p102f          = tbl1.p102f        ,
    mydt11.p103           = tbl1.p103         ,
    mydt11.p103f          = tbl1.p103f        ,
    
    mydt11.remdate        = curdate()         , 
    mydt11.remtime        = curtime()         , 
    mydt11.remuser        = '{user_pswd}'     , 
    
    mydt10.p30='', 
    mydt10.remdate        = curdate()         , 
    mydt10.remtime        = curtime()         , 
    mydt10.remuser        = '{user_pswd}'
    
where mydt11.nr01=tbl1.nr01
   && mydt10.nr01=mydt11.mydt10
   && (   mydt11.tbl            <> tbl1.tbl             
       || mydt11.tbl_nr01       <> tbl1.tbl_nr01     
       || mydt11.mydt10         <> tbl1.mydt10
       || mydt11.mydt57_p00     <> tbl1.mydt57_p00   
       || mydt11.mydt58_p00     <> tbl1.mydt58_p00   
       || mydt11.mydt61_p00     <> tbl1.mydt61_p00   
       || mydt11.mydt62_p00     <> tbl1.mydt62_p00   
       || mydt11.mydt62_p01     <> tbl1.mydt62_p01   
       || mydt11.linenumber     <> tbl1.linenumber   
       || mydt11.quantity       <> tbl1.quantity     
       || mydt11.munit          <> tbl1.munit        
       || mydt11.invdtype       <> tbl1.invdtype     
       || mydt11.netvalue       <> tbl1.netvalue     
       || mydt11.vatcat         <> tbl1.vatcat       
       || mydt11.vatexcat       <> tbl1.vatexcat     
       || mydt11.fpavalue       <> tbl1.fpavalue     
       || mydt11.appid          <> tbl1.appid        
       || mydt11.appdate        <> tbl1.appdate      
       || mydt11.doy            <> tbl1.doy          
       || mydt11.shipid         <> tbl1.shipid       
       || mydt11.discoption     <> tbl1.discoption   
       || mydt11.wthhldpcat     <> tbl1.wthhldpcat   
       || mydt11.wthhld_am      <> tbl1.wthhld_am    
       || mydt11.stampvalue     <> tbl1.stampvalue   
       || mydt11.stamppcat      <> tbl1.stamppcat    
       || mydt11.feestype       <> tbl1.feestype     
       || mydt11.fees_am        <> tbl1.fees_am      
       || mydt11.otaxpcat       <> tbl1.otaxpcat     
       || mydt11.otax_am        <> tbl1.otax_am      
       || mydt11.deducvalue     <> tbl1.deducvalue   
       || mydt11.comments       <> tbl1.comments     
       || mydt11.taxtype        <> tbl1.taxtype
       || mydt11.taxcat         <> tbl1.taxcat
       || mydt11.taxamount      <> tbl1.taxamount
       || mydt11.taxunder       <> tbl1.taxunder
       /*v1.18*/
       || mydt11.p101           <> tbl1.p101
       || mydt11.p101f          <> tbl1.p101f
       || mydt11.p102           <> tbl1.p102
       || mydt11.p102f          <> tbl1.p102f
       || mydt11.p103           <> tbl1.p103
       || mydt11.p103f          <> tbl1.p103f
     );
/*peg_end*/     

INSERT INTO mydt11
(nr01, tbl, tbl_nr01, mydt57_p00, mydt58_p00, mydt61_p00, mydt62_p00, mydt62_p01, linenumber, quantity, munit, 
 invdtype, netvalue, vatcat, vatexcat, fpavalue,  appid, appdate, doy, shipid, discoption, wthhldpcat, wthhld_am, 
 stampvalue, stamppcat, feestype, fees_am, otaxpcat, otax_am, deducvalue, comments, mydt10, 
 taxtype, taxcat, taxamount, taxunder,
 /*v1.18*/
 p101, p101f, p102, p102f, p103, p103f, 
 remfdate, remftime, remfuser, remdate, remtime, remuser)

SELECT tbl1.nr01,  
       tbl1.tbl        ,
       tbl1.tbl_nr01   ,
       tbl1.mydt57_p00 ,
       tbl1.mydt58_p00 ,
       tbl1.mydt61_p00 ,
       tbl1.mydt62_p00 ,
       tbl1.mydt62_p01 ,
       tbl1.linenumber ,
       tbl1.quantity   ,
       tbl1.munit      ,
       tbl1.invdtype   ,
       tbl1.netvalue   ,
       tbl1.vatcat     ,
       tbl1.vatexcat   ,
       tbl1.fpavalue   ,
       tbl1.appid      ,
       tbl1.appdate    ,
       tbl1.doy        ,
       tbl1.shipid     ,
       tbl1.discoption ,
       tbl1.wthhldpcat ,
       tbl1.wthhld_am  ,
       tbl1.stampvalue ,
       tbl1.stamppcat  ,
       tbl1.feestype   ,
       tbl1.fees_am    ,
       tbl1.otaxpcat   ,
       tbl1.otax_am    ,
       tbl1.deducvalue ,
       tbl1.comments   ,
       tbl1.mydt10     ,
       tbl1.taxtype    , 
       tbl1.taxcat     , 
       tbl1.taxamount  , 
       tbl1.taxunder   ,
       /*v 1.18*/
       tbl1.p101       ,
       tbl1.p101f      ,
       tbl1.p102       ,
       tbl1.p102f      ,
       tbl1.p103       ,
       tbl1.p103f      ,

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