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

create table {p_mydt10tbl0}
            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 ,
                         sum(mydt11.quantity)    as  quantity   ,
                         mydt11.munit       as  munit      ,
                         mydt11.invdtype    as  invdtype   ,
                         sum(mydt11.netvalue)    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.wthhld_am)   as  wthhld_am  ,
                         mydt11.stamppcat   as  stamppcat  ,
                         mydt11.feestype    as  feestype   ,
                         sum(mydt11.fees_am)     as  fees_am    ,
                         mydt11.otaxpcat    as  otaxpcat   ,
                         sum(mydt11.otax_am)     as  otax_am    ,
                         mydt11.deductionp  as  deductionp ,
                         group_concat(distinct mydt11.comments)    as  comments   
                  from mydt11, mydt10
                  where mydt10.nr01=mydt11.mydt10
                     && {peg_arr[1]}
                  group by mydt11.mydt10, 
                           mydt11.mydt57_p00, 
                           mydt11.mydt58_p00,
                           mydt11.mydt61_p00,
                           mydt11.mydt62_p00,
                           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.deductionp 
            ) as tbl01
            WHERE (    tbl01.quantity<>0
                    || tbl01.netvalue<>0
                    || tbl01.wthhld_am<>0
                    || tbl01.fees_am<>0
                    || tbl01.otax_am<>0
                  )
            ORDER BY tbl01.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*/            
