/********************************************************
      create temporary with records to be copied
********************************************************/
drop table  if exists {p_mydt10tbl0};
/*peg_end*/

create table {p_mydt10tbl0}
    SELECT tbl02.*,
           tbl02.deducvalue_1 as deducvalue, 
           tbl02.wthhld_am_1 as wthhld_am, 
           tbl02.fees_am_1 as fees_am, 
           tbl02.otax_am_1 as otax_am 
    FROM
    (       
            SELECT tbl01.*
            FROM 
            ( 
                  /********************************************************
                        totals per category
                  ********************************************************/
                 (select mydt11.nr01 as nr01, 
                         mydt11.linenumber as linenumber, 
                         mydt11.mydt10 as mydt10, 
                         mydt11.mydt57_p00  as  mydt57_p00 ,
                         mydt11.mydt58_p00  as  mydt58_p00 ,
                         mydt11.mydt61_p00  as  mydt61_p00 ,
                         mydt11.mydt62_p00  as  mydt62_p00 ,
                         mydt11.mydt62_p01  as  mydt62_p01 ,
                         sum(mydt11.quantity)    as  quantity,
                         mydt11.munit       as  munit      ,
                         mydt11.invdtype    as  invdtype   ,
                         /*v1.18*/
                         sum(mydt11.netvalue - mydt11.p101 - mydt11.p102 - mydt11.p103)    as  netvalue,
                         mydt11.vatcat      as  vatcat     ,
                         mydt11.vatexcat    as  vatexcat   ,
                         mydt11.appid       as  appid      ,
                         mydt11.appdate     as  appdate    ,
                         mydt11.doy         as  doy        ,
                         mydt11.shipid      as  shipid     ,
                         mydt11.discoption  as  discoption ,
                         mydt11.wthhldpcat  as  wthhldpcat ,
                         /*v1.18*/
                         sum(fpavalue - mydt11.p101f - mydt11.p102f - mydt11.p103f) as fpavalue,
                         sum(stampvalue) as stampvalue,
                         sum(deducvalue) as deducvalue_1,
                         sum(mydt11.wthhld_am) as wthhld_am_1,
                         mydt11.stamppcat as stamppcat,
                         mydt11.feestype  as feestype,
                         sum(mydt11.fees_am) as fees_am_1,
                         mydt11.otaxpcat     as otaxpcat,
                         sum(mydt11.otax_am) as otax_am_1,
                         group_concat(distinct mydt11.comments) as comments, 
                         /*v 1.8*/
                         mydt11.taxtype  as  taxtype ,
                         mydt11.taxcat   as  taxcat ,
                         sum(mydt11.taxamount) as taxamount, 
                         mydt11.taxunder as taxunder 
                         
                         
                  from mydt11, mydt10
                  where mydt10.nr01=mydt11.mydt10
                         /*v1.18*/
                     &&(    mydt11.netvalue - mydt11.p101 - mydt11.p102 - mydt11.p103<>0
                         || mydt11.stamppcat<>''  
                         || mydt11.wthhldpcat<>'' 
                         || mydt11.feestype<>''
                         || mydt11.otaxpcat<>'' 
                         /*v1.18*/
                         || mydt11.fpavalue - mydt11.p101f - mydt11.p102f - mydt11.p103f<>0
                         || mydt11.deducvalue<>0
                       )   
                     && {peg_arr[1]}
                  group by mydt11.mydt10, 
                           mydt11.mydt57_p00, 
                           mydt11.mydt58_p00,
                           mydt11.mydt61_p00,
                           mydt11.mydt62_p00,
                           mydt11.mydt62_p01,
                           mydt11.munit,
                           mydt11.invdtype,
                           mydt11.vatcat,
                           mydt11.vatexcat,
                           mydt11.appid,
                           mydt11.appdate,
                           mydt11.doy,
                           mydt11.shipid,
                           mydt11.discoption,
                           mydt11.wthhldpcat,
                           mydt11.stamppcat,
                           mydt11.feestype,
                           mydt11.otaxpcat,
                           mydt11.taxtype,
                           mydt11.taxcat,
                           mydt11.taxunder
                 )
                 
                 
                 /*v1.18*/           
                 UNION ALL

                  /********************************************************
                        totals per category
                  ********************************************************/
                 (select mydt11.nr01 + 9000000000 as nr01, 
                         mydt11.linenumber as linenumber, 
                         mydt11.mydt10 as mydt10, 
                         if(mydt10.p00=1,  'category1_95', '')  as  mydt57_p00 ,
                         if(mydt10.p00<>1, 'category2_95', '')  as  mydt58_p00 ,
                         ''  as  mydt61_p00 ,
                         ''  as  mydt62_p00 ,
                         mydt11.mydt62_p01  as  mydt62_p01 ,
                         sum(mydt11.quantity)    as  quantity,
                         mydt11.munit       as  munit      ,
                         mydt11.invdtype    as  invdtype   ,
                         sum(mydt11.p101 + mydt11.p102 + mydt11.p103) as  netvalue,
                         mydt11.vatcat      as  vatcat     ,
                         mydt11.vatexcat    as  vatexcat   ,
                         mydt11.appid       as  appid      ,
                         mydt11.appdate     as  appdate    ,
                         mydt11.doy         as  doy        ,
                         mydt11.shipid      as  shipid     ,
                         mydt11.discoption  as  discoption ,
                         mydt11.wthhldpcat  as  wthhldpcat ,
                         sum(mydt11.p101f + mydt11.p102f + mydt11.p103f) as fpavalue,
                         0 as stampvalue,
                         0 as deducvalue_1,
                         0 as wthhld_am_1,
                         '' as stamppcat,
                         ''  as feestype,
                         0 as fees_am_1,
                         ''     as otaxpcat,
                         0 as otax_am_1,
                         group_concat(distinct mydt11.comments) as comments, 
                         ''  as  taxtype ,
                         ''   as  taxcat ,
                         0 as taxamount, 
                         0 as taxunder 
                         
                         
                  from mydt11, mydt10
                  where mydt10.nr01=mydt11.mydt10
                     &&(    mydt11.p101 + mydt11.p102 + mydt11.p103<>0
                         || mydt11.p101f + mydt11.p102f + mydt11.p103f<>0
                       )   
                     && {peg_arr[1]}
                  group by mydt11.mydt10, 
                           mydt11.mydt62_p01,
                           mydt11.munit,
                           mydt11.invdtype,
                           mydt11.vatcat,
                           mydt11.vatexcat,
                           mydt11.appid,
                           mydt11.appdate,
                           mydt11.doy,
                           mydt11.shipid,
                           mydt11.discoption
                 )
                 
                 
                           
            ) as tbl01
    ) as tbl02
    ORDER BY tbl02.mydt10; 
/*peg_end*/            

alter table {p_mydt10tbl0} 
add key (nr01),
add key (mydt10);
/*peg_end*/            

/******************************************
      update linenumber
******************************************/
SET @prnr1=0, @prnr2=0, @n1=0;
/*peg_end*/

update {p_mydt10tbl0} as tbl2, 
      (SELECT tbl2.nr01, 
              tbl2.mydt10, 
              @prnr1:=tbl2.mydt10,
              @n1:=if(@prnr1=@prnr2, @n1+1, 1) as linenumber, 
              @prnr2:=tbl2.mydt10
       FROM {p_mydt10tbl0} as tbl2
       order by mydt10, nr01
      ) as tbl01
set tbl2.linenumber=tbl01.linenumber
where tbl2.nr01=tbl01.nr01;
/*peg_end*/            
