insert into {p_mydt10tbl2}
( tbl, tbl_nr01, ptbl, ptbl_nr01, comments, mydt58_p00, 
  vatcat, vatexcat, wthhldpcat, otaxpcat, stamppcat,
  feestype, invdtype, mydt62_p00, netvalue, fpavalue, wthhld_am, otax_am, fees_am, stampvalue, deducvalue, 
  taxunder,
  /*v1.18*/
  p101, p101f, p102, p102f, p103, p103f
)


SELECT tbl01.tbl, 
       tbl01.tbl_nr01, 
       tbl01.ptbl, 
       tbl01.ptbl_nr01, 
       tbl01.comments, 
       tbl01.mydt58_p00,
       tbl01.vatcat,
       if(tbl01.vatexcat1='',tbl01.vatexcat, tbl01.vatexcat1),
       tbl01.wthhldpcat,
       tbl01.otaxpcat,
       if(tbl01.stamppcat='', tbl01.stamppcat_1, tbl01.stamppcat) as stampcat,
       tbl01.feestype,
       tbl01.invdtype,
       tbl01.mydt62_p00,
       if(   wthhldpcat=''
          && otaxpcat=''  
          && feestype=''  
          && stamppcat='' 
          && stamppcat_1='', i01p011, 0) as netvalue, 
       if(vatcat<>''    , i01fp011, 0) as fpavalue, 
       if(wthhldpcat<>'', i01p011, 0) + wthhld_am as wthhld_am, 
       if(otaxpcat<>''  , i01p011, 0) + otax_am as otax_am, 
       if(feestype<>''  , i01p011, 0) as fees_am,
       if(stamppcat<>'' || stamppcat_1<>'', i01p011, 0) as stampvalue, 
       deducvalue as deducvalue, 
       taxunder as taxunder, 
       /*v1.18*/
       p101  as p101, 
       p101f as p101f, 
       p102  as p102, 
       p102f as p102f, 
       p103  as p103, 
       p103f as p103f
FROM  
(
    /*********************************************************
           transactions
    *********************************************************/
    
    /******************************************************
            i01.records
    ******************************************************/
    (
    select i01.p011 as i01p011, 
           i01.fp011 as i01fp011, 
           0 as otax_am, 
           0 as wthhld_am,
           0 as deducvalue,

           'i01' as tbl, 
           i01.nr01 as tbl_nr01,            
           mydt10.tbl as ptbl, 
           mydt10.tbl_nr01 as ptbl_nr01, 
           i00.pno as comments,
           
           ifnull( (select mydt_in.p00 
                    from mydt58 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620021
                       && q31_in.p03=i10.p01
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as mydt58_p00,
                            
           ifnull( (select mydt_in.p00 
                    from mydt51 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620024
                       && q31_in.p03=i13.p01
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as vatcat,
       
           /* vat exception from invoice (a77)*/
           if( i01.fp011<>0, '', 
                   ifnull( (select mydt_in.p00 
                            from mydt52 as mydt_in, 
                                 q31 as q31_in
                            where q31_in.p01=620051
                               && q31_in.p03=a56.p706c
                               && q31_in.p05=mydt_in.p00
                             limit 1), 
                         '') 
               ) as vatexcat,       


           /* vat exception from product type (i10)*/
           if( i01.fp011<>0, '', 
                   ifnull( (select mydt_in.p00 
                            from mydt52 as mydt_in, 
                                 q31 as q31_in
                            where q31_in.p01=620050
                               && q31_in.p03=i10.p01
                               && q31_in.p05=mydt_in.p00
                             limit 1), 
                         '') 
               ) as vatexcat1,       


           ifnull( (select mydt_in.p00 
                    from mydt53 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620026
                       && q31_in.p03=i10.p01
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as wthhldpcat,

           ifnull( (select mydt_in.p00 
                    from mydt54 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620027
                       && q31_in.p03=i10.p01
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as otaxpcat,

           /*** stamp from product ***/
           ifnull( (select mydt_in.p00 
                    from mydt55 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620028
                       && q31_in.p03=i10.p01
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as stamppcat,

           /*** stamp from vat ***/
           ifnull( (select mydt_in.p00 
                    from mydt55 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620025
                       && q31_in.p03=i13.p01
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as stamppcat_1,

           ifnull( (select mydt_in.p00 
                    from mydt56 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620029
                       && q31_in.p03=i10.p01
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as feestype,
       
           '' as invdtype,
       
           ifnull( (select mydt_in.p00 
                    from mydt62 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620023
                       && q31_in.p03=i10.p01
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as mydt62_p00, 
                    
           0 as taxunder,
           /*v1.18*/
           if(ifnull(mydt00.p101, 0)=1, round(ifnull(i01.p03*i01.p041, 0), {peg_dek}), 0) as p101, 
           if(ifnull(mydt00.p101, 0)=1, round(ifnull(i01.p03*i01.p041*i01.p08/100, 0), {peg_dek}), 0) as p101f, 
           if(ifnull(mydt00.p101, 0)=1, round(ifnull(i01.p03*i01.p616, 0), {peg_dek}), 0) as p102, 
           if(ifnull(mydt00.p101, 0)=1, round(ifnull(i01.p03*i01.p616*i01.p08/100, 0), {peg_dek}), 0) as p102f, 
           if(ifnull(mydt00.p101, 0)=1, round(ifnull( i01.p011*i10.p101/(100+i10.p101), 0), {peg_dek}), 0) as p103,
           if(ifnull(mydt00.p101, 0)=1, round(ifnull( i01.p08*i01.p011*i10.p101/(100*(100+i10.p101)), 0), {peg_dek}), 0) as p103f

    from {p_mydt10tbl1} as mydt10
    left join i01 on i01.p06=mydt10.tbl_nr01
                  && i01.p07=1
    left join a56 ON a56.nr01=i01.p06
    left join i00 ON i00.pno=i01.p01
    left join i10 on i10.p01=i00.p62
    left join i13 on i13.p01=i01.p081
    /*v1.18*/
    left join mydt00 on mydt00.nr01=1
    where i00.nr01 is not null
       && ifnull(i10.p106, 0)=0
    group by i01.nr01
    )
    
    
    UNION ALL

    /******************************************************
            a56.vp0112 + a56.vp0113
            total deducvalue
    ******************************************************/
    /*v1.17*/
    /**modified**/
    (
    select 0 as i01p011, 
           0 as i01fp011, 
           /*v1.17*/
           0 as otax_am, 
           0 as wthhld_am,
           
           if( a56.vp0112>0 && ifnull(mydt00.p06, 0)=0, a56.vp0112, 0 ) +
           if( a56.vp0113>0 && ifnull(mydt00.p07, 0)=0, a56.vp0113, 0 ) +
           if( a56.vp0112<0 && ifnull(mydt00.p08, 0)=0, a56.vp0112, 0 ) +
           if( a56.vp0113<0 && ifnull(mydt00.p09, 0)=0, a56.vp0113, 0 ) as deducvalue,
           
           'a56' as tbl, 
           a56.nr01 as tbl_nr01,            
           mydt10.tbl as ptbl, 
           mydt10.tbl_nr01 as ptbl_nr01, 
           '' as comments,
           '' as mydt58_p00,
           '7' as vatcat,
           '21' as vatexcat,       
           '21' as vatexcat1,       
           '' as wthhldpcat,
           '' as otaxpcat,
           '' as stamppcat,
           '' as stamppcat_1,
           '' as feestype,
           '' as invdtype,
           '' as mydt62_p00, 
           0 as taxunder,
           /*v1.18*/
           0  as p101, 
           0 as p101f, 
           0  as p102, 
           0 as p102f, 
           0  as p103, 
           0 as p103f

    from {p_mydt10tbl1} as mydt10
    left join mydt00 on mydt00.nr01=1
    left join a56 on a56.nr01=mydt10.tbl_nr01
                  && (    ( a56.vp0112>0 && ifnull(mydt00.p06, 0)=0 )
                       || ( a56.vp0113>0 && ifnull(mydt00.p07, 0)=0 )
                       || ( a56.vp0112<0 && ifnull(mydt00.p08, 0)=0 )
                       || ( a56.vp0113<0 && ifnull(mydt00.p09, 0)=0 )
                     )
    where a56.nr01 is not null
    group by a56.nr01
    )

    UNION ALL

    /******************************************************
            a56.vp0112
            using i70
    ******************************************************/
    /*v1.17*/
    (
    select abs(a56.vp0112) as i01p011, 
           0 as i01fp011, 
           0 as otax_am, 
           0 as wthhld_am,
           0 as deducvalue,

           'a56_1' as tbl, 
           a56.nr01 as tbl_nr01,            
           mydt10.tbl as ptbl, 
           mydt10.tbl_nr01 as ptbl_nr01, 
           ifnull(i70.p01, '') as comments,
           
           '' mydt58_p00,                            
           '7' as vatcat,       
           '21' as vatexcat,       
           '21' as vatexcat1,

           ifnull( (select mydt_in.p00 
                    from mydt53 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620046
                       && q31_in.p03=i70.nr01
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as wthhldpcat,

           ifnull( (select mydt_in.p00 
                    from mydt54 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620047
                       && q31_in.p03=i70.nr01
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as otaxpcat,

           ifnull( (select mydt_in.p00 
                    from mydt55 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620048
                       && q31_in.p03=i70.nr01
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as stamppcat,
           '' as stamppcat_1,

           ifnull( (select mydt_in.p00 
                    from mydt56 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620049
                       && q31_in.p03=i70.nr01
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as feestype,
       
           '' as invdtype,       
           '' as mydt62_p00, 
           if(i70.p03 in (1, 3), a56.p0100-a56.p0102, 0) as taxunder, 
           /*v1.18*/
           0  as p101, 
           0 as p101f, 
           0  as p102, 
           0 as p102f, 
           0  as p103, 
           0 as p103f

    from {p_mydt10tbl1} as mydt10
    left join mydt00 on mydt00.nr01=1
    left join a56 on a56.nr01=mydt10.tbl_nr01
                  && (    ( a56.vp0112>0 && ifnull(mydt00.p06, 0)<>0 )
                       || ( a56.vp0112<0 && ifnull(mydt00.p08, 0)<>0 )
                     )
    left join i70 ON (    (i70.nr01=ifnull(mydt00.p06,0) && a56.vp0112>0)
                       || (i70.nr01=ifnull(mydt00.p08,0) && a56.vp0112<0)
                     )
    where i70.nr01 is not null
       && i70.nr01<>0 
       && ifnull(i70.p106, 0)=0
       && a56.vp0112<>0
       && a56.nr01 is not null
    group by a56.nr01
    )

    UNION ALL

    /******************************************************
            a56.vp0113
            using i70
    ******************************************************/
    /*v1.17*/
    (
    select abs(a56.vp0113) as i01p011, 
           0 as i01fp011, 
           0 as otax_am, 
           0 as wthhld_am,
           0 as deducvalue,

           'a56_2' as tbl, 
           a56.nr01 as tbl_nr01,            
           mydt10.tbl as ptbl, 
           mydt10.tbl_nr01 as ptbl_nr01, 
           ifnull(i70.p01, '') as comments,
           
           '' mydt58_p00,                            
           '7' as vatcat,       
           '21' as vatexcat,       
           '21' as vatexcat1,

           ifnull( (select mydt_in.p00 
                    from mydt53 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620046
                       && q31_in.p03=i70.nr01
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as wthhldpcat,

           ifnull( (select mydt_in.p00 
                    from mydt54 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620047
                       && q31_in.p03=i70.nr01
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as otaxpcat,

           ifnull( (select mydt_in.p00 
                    from mydt55 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620048
                       && q31_in.p03=i70.nr01
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as stamppcat,
           '' as stamppcat_1,

           ifnull( (select mydt_in.p00 
                    from mydt56 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620049
                       && q31_in.p03=i70.nr01
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as feestype,
       
           '' as invdtype,       
           '' as mydt62_p00, 
           if(i70.p03 in (1, 3), a56.p0100-a56.p0102-a56.vp0112, 0) as taxunder, 
           /*v1.18*/
           0  as p101, 
           0 as p101f, 
           0  as p102, 
           0 as p102f, 
           0  as p103, 
           0 as p103f

    from {p_mydt10tbl1} as mydt10
    left join mydt00 on mydt00.nr01=1
    left join a56 on a56.nr01=mydt10.tbl_nr01
                  && (    ( a56.vp0113>0 && ifnull(mydt00.p07, 0)<>0 )
                       || ( a56.vp0113<0 && ifnull(mydt00.p09, 0)<>0 )
                     )
    left join i70 ON (    (i70.nr01=ifnull(mydt00.p07,0) && a56.vp0113>0)
                       || (i70.nr01=ifnull(mydt00.p09,0) && a56.vp0113<0)
                     )
    where i70.nr01 is not null
       && i70.nr01<>0 
       && ifnull(i70.p106, 0)=0
       && a56.vp0113<>0
       && a56.nr01 is not null
    group by a56.nr01
    )

    
    UNION ALL
    

    /*v1.17*/
    /******************************************************
            a56.p0108
    ******************************************************/
    (
    select a56.p0108 as i01p011, 
           0 as i01fp011, 
           0 as otax_am, 
           0 as wthhld_am,
           0 as deducvalue,
           
           /*v1.17*/
           'a56_3' as tbl, 
           a56.nr01 as tbl_nr01,            
           mydt10.tbl as ptbl, 
           mydt10.tbl_nr01 as ptbl_nr01, 
           '' as comments,
           ifnull(mydt00.p03, '') as mydt58_p00,
           '7' as vatcat,
           '21' as vatexcat,       
           '21' as vatexcat1,       
           '' as wthhldpcat,
           '' as otaxpcat,
           '' as stamppcat,
           '' as stamppcat_1,
           '' as feestype,
           '' as invdtype,
           ifnull(mydt00.p04, '') as mydt62_p00, 
           0 as taxunder,
           /*v1.18*/
           0  as p101, 
           0 as p101f, 
           0  as p102, 
           0 as p102f, 
           0  as p103, 
           0 as p103f

    from {p_mydt10tbl1} as mydt10
    left join a56 on a56.nr01=mydt10.tbl_nr01
                  && a56.p0108<>0
    left join mydt00 on mydt00.nr01=1
    where a56.nr01 is not null
    group by a56.nr01
    )
    
    UNION ALL
    
    
    /******************************************************
            i70.records
    ******************************************************/
    (
    select i71.p100 as i01p011, 
           0 as i01fp011, 
           0 as otax_am, 
           0 as wthhld_am,
           0 as deducvalue,

           'i71' as tbl, 
           i71.nr01 as tbl_nr01,            
           mydt10.tbl as ptbl, 
           mydt10.tbl_nr01 as ptbl_nr01, 
           ifnull(i70.p01, '') as comments,
           
           /*v1.19*/
           ifnull( (select mydt_in.p00 
                    from mydt58 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620054
                       && q31_in.p03=i70.nr01
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as mydt58_p00,

           '7' as vatcat,       
           '' as vatexcat,       
           '' as vatexcat1,       

           ifnull( (select mydt_in.p00 
                    from mydt53 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620046
                       && q31_in.p03=i71.i70
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as wthhldpcat,

           ifnull( (select mydt_in.p00 
                    from mydt54 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620047
                       && q31_in.p03=i71.i70
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as otaxpcat,

           ifnull( (select mydt_in.p00 
                    from mydt55 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620048
                       && q31_in.p03=i71.i70
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as stamppcat,
           '' as stamppcat_1,

           ifnull( (select mydt_in.p00 
                    from mydt56 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620049
                       && q31_in.p03=i71.i70
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as feestype,
       
           '' as invdtype,       

           /*v1.19*/
           ifnull( (select mydt_in.p00 
                    from mydt62 as mydt_in, 
                         q31 as q31_in
                    where q31_in.p01=620055
                       && q31_in.p03=i70.nr01
                       && q31_in.p05=mydt_in.p00
                    limit 1), '') as mydt62_p00, 

           if(i71.p04<>0, i71.p05, 0) as taxunder, 
           /*v1.18*/
           0  as p101, 
           0 as p101f, 
           0  as p102, 
           0 as p102f, 
           0  as p103, 
           0 as p103f

    from {p_mydt10tbl1} as mydt10
    left join i71 on i71.tbl_nr=mydt10.tbl_nr01
                  && i71.tbl='a56'
                  && i71.i70p07=1
    left join i70 ON i70.nr01=i71.i70
    where i71.nr01 is not null
       /*v1.11*/
       && ifnull(i70.p106, 0)=0
    group by i71.nr01
    )
    
) as tbl01    