insert into {p_mydt10tbl1}
(tbl, tbl_nr01, p00, p01, p03, p06, p07, p08d, p08t, 
currency, exchrate, 
/*v1.11*/
slfpricing,
series, aa, issuedate, issuetime, invtype, paytype,  
is_country, is_vat, is_name, is_address, is_city, is_zip, 
cp_country, cp_vat, cp_name, cp_address, cp_city, cp_zip)

SELECT tbl as tbl, 
       tbl_nr01 as tbl_nr01, 
       p00 as p00, 
       p01 as p01, 
       p03 as p03, 
       p06 as p06, 
       p07 as p07, 
       p08d as p08d, 
       p08t as p08t, 
       currency as currency, 
       exchrate as exchrate, 
       /*v1.11*/
       slfpricing as slfpricing,       
       series as series, 
       peg_abschr(peg_abschr(aa, ''), "ABCDEFGHIJKLMNOPQRSTUVWXYZ~`@#$%^&*()-+ /?.>,<'") as aa, 
       issuedate as issuedate, 
       issuetime as issuetime, 
       invtype as invtype, 
       paytype as paytype, 
       
       ''             as cp_country,
       '{nt_p101[1]}' as cp_vat, 
       '{nt_p101[2]}' as cp_name, 
       '{nt_p101[3]}' as cp_address, 
       '{nt_p101[4]}' as cp_city, 
       '{nt_p101[5]}' as cp_zip,
       a_country as is_country,
       a_vat     as is_vat, 
       a_name    as is_name, 
       a_address as is_address, 
       a_city    as is_city, 
       a_zip     as is_zip 

FROM
(
      /***********************************************
             Παραστατικά προς αποστολή
             q30.nr01=620014
      ***********************************************/
      (
      SELECT 'a55' as tbl, 
             a55.nr01 as tbl_nr01, 
             1 as p00,
             1 as p01,
             a55.p0121 as p03, 
             a55.p09 as p06, 
             ifnull(a10.p04, 0) as p07, 
             a55.remdate as p08d, 
             a55.remtime as p08t,             
             a55.pn01 as currency, 
             a55.pn02 as exchrate,
             /*v1.11*/
             ifnull(a10.p013, 0) as slfpricing,                    
             
             /**στοιχεία παραστατικού*/
             a55.p701 as series, 
             a55.p06  as aa, 
             a55.p01 as issuedate, 
             a55.p02 as issuetime, 
             ifnull(mydt50.p00, '') as invtype, 
             
             /**paytype**/           
             ifnull( (select q31_in.p05
                      from q31 as q31_in
                      where q31_in.p01=620042
                         && q31_in.p03=a55.p015
                      limit 1), '') as paytype,
             
             /*αντισυμβαλλόμενος*/
             ifnull(a01.p4_1, '') as a_country, 
             ifnull(a01.p20, '') as a_vat, 
             ifnull(a01.p19, '') as a_name, 
             ifnull(a01.p4, '') as a_address, 
             concat( ifnull(concat(d22.p02, ' '), ''), 
                     ifnull(concat(a01.p5, ' '), ''),
                     ifnull(concat(a01.p6, ' '), '')
                   ) as a_city, 
             ifnull(a01.p7, '') as a_zip 
      FROM q31
      LEFT JOIN a10 ON q31.p03=a10.p01
      LEFT JOIN a55 ON a55.p05=a10.p01
                    && a55.p01>=cast('{odbcdtos(nt_p01)}' as date)
                    && a55.p01<=cast('{odbcdtos(nt_p02)}' as date)
                    && a55.remdate>=cast('{odbcdtos(nt_p07d)}' as date)
                    && a55.remdate<=cast('{odbcdtos(nt_p08d)}' as date)
                    && concat(a55.remdate, a55.remtime)>=concat(cast('{odbcdtos(nt_p07d)}' as date), '{nt_p07t}')
                    && concat(a55.remdate, a55.remtime)<=concat(cast('{odbcdtos(nt_p08d)}' as date), '{nt_p08t}')
                    && a55.p0121>={nt_p03}
                    && a55.p0121<={nt_p04}                    
      LEFT JOIN mydt50 ON mydt50.p00=q31.p05
      LEFT JOIN a01 ON a01.nr01=a55.p03
      LEFT JOIN d22 ON d22.p01=a01.p4_3
      WHERE q31.p01=620014
         && a55.nr01 is not null
         && a10.nr01 is not null
         && a55.p414=0
         && a55.p601a=0
         && {p_mydt10where} 
         && {nt_napost}=1
      GROUP BY a55.nr01
      )
      
      UNION ALL

      /***********************************************
             Παραστατικά προς παραλαβή
             q30.nr01=620015
      ***********************************************/
      (
      SELECT 'a55' as tbl, 
             a55.nr01 as tbl_nr01, 
             1 as p00,
             2 as p01,
             a55.p0121 as p03, 
             a55.p09 as p06, 
             ifnull(a10.p04, 0) as p07, 
             a55.remdate as p08d, 
             a55.remtime as p08t,             
             a55.pn01 as currency, 
             a55.pn02 as exchrate,
             /*v1.11*/
             ifnull(a10.p013, 0) as slfpricing,                    

             /**στοιχεία παραστατικού*/
             a55.p701 as series, 
             a55.p06  as aa, 
             a55.p01 as issuedate, 
             a55.p02 as issuetime, 
             ifnull(mydt50.p00, '') as invtype, 

             /**paytype**/           
             ifnull( (select q31_in.p05
                      from q31 as q31_in
                      where q31_in.p01=620042
                         && q31_in.p03=a55.p015
                      limit 1), '') as paytype,
             
             /*αντισυμβαλλόμενος*/
             ifnull(a01.p4_1, '') as a_country, 
             ifnull(a01.p20, '') as a_vat, 
             ifnull(a01.p19, '') as a_name, 
             ifnull(a01.p4, '') as a_address, 
             concat( ifnull(concat(d22.p02, ' '), ''), 
                     ifnull(concat(a01.p5, ' '), ''),
                     ifnull(concat(a01.p6, ' '), '')
                   ) as a_city, 
             ifnull(a01.p7, '') as a_zip 
      FROM q31
      LEFT JOIN a10 ON q31.p03=a10.p01
      LEFT JOIN a55 ON a55.p05=a10.p01
                    && a55.p01>=cast('{odbcdtos(nt_p01)}' as date)
                    && a55.p01<=cast('{odbcdtos(nt_p02)}' as date)
                    && a55.remdate>=cast('{odbcdtos(nt_p07d)}' as date)
                    && a55.remdate<=cast('{odbcdtos(nt_p08d)}' as date)
                    && concat(a55.remdate, a55.remtime)>=concat(cast('{odbcdtos(nt_p07d)}' as date), '{nt_p07t}')
                    && concat(a55.remdate, a55.remtime)<=concat(cast('{odbcdtos(nt_p08d)}' as date), '{nt_p08t}')
                    && a55.p0121>={nt_p03}
                    && a55.p0121<={nt_p04}                    
      LEFT JOIN mydt50 ON mydt50.p00=q31.p05
      LEFT JOIN a01 ON a01.nr01=a55.p03
      LEFT JOIN d22 ON d22.p01=a01.p4_3
      WHERE q31.p01=620015
         && a55.nr01 is not null
         && a10.nr01 is not null
         && a55.p414=0
         && a55.p601a=0
         && {p_mydt10where} 
         && {nt_nparal}=1
      GROUP BY a55.nr01
      )

) as tbl01;           
/*peg_end*/

/*******************************************
    Συσχετιζόμενα από το hotel
*******************************************/
/**start_module_exist_hot000**/
update 
       {p_mydt10tbl1},
       (select concat('#' , group_concat(DISTINCT(a55_00.nr01) SEPARATOR '#') , '#') as pcor,
               'a55' as tbl, 
                a55.nr01 as tbl_nr01
        from a55
        join i01 on i01.p06=a55.nr01 and i01.p07=2  
        left join hota17 on i01.pd101='hota17' 
                        and i01.pd102=hota17.nr01
        left join hota04 on (i01.pd101='hota04' and i01.pd102=hota04.nr01)   
        join i01 as i01_00 on ( i01_00.pd101='hota17' and i01_00.pd102=hota17.nr01) 
                           or (i01_00.pd101='hota04' and i01_00.pd102=hota04.nr01) 
                           or (i01_00.pd101='hota04' and i01_00.pd102=hota17.pnr01)
        join a55 as a55_00 on i01_00.p06=a55_00.nr01 and i01_00.p07=2  and a55_00.nr01!=a55.nr01 and a55_00.p601a=0 and a55_00.p414=0
        where a55.p01>=cast('{odbcdtos(nt_p01)}' as date)
           && a55.p01<=cast('{odbcdtos(nt_p02)}' as date)
           && a55.remdate>=cast('{odbcdtos(nt_p07d)}' as date)
           && a55.remdate<=cast('{odbcdtos(nt_p08d)}' as date)
           && concat(a55.remdate, a55.remtime)>=concat(cast('{odbcdtos(nt_p07d)}' as date), '{nt_p07t}')
           && concat(a55.remdate, a55.remtime)<=concat(cast('{odbcdtos(nt_p08d)}' as date), '{nt_p08t}')
           && {p_mydt10where}
        group by tbl,tbl_nr01)  as tbl_a55 
set {p_mydt10tbl1}.pcor = tbl_a55.pcor
where {p_mydt10tbl1}.tbl = tbl_a55.tbl 
   && {p_mydt10tbl1}.tbl_nr01 = tbl_a55.tbl_nr01;
/**end_module_exist_hot000**/ 
/*peg_end*/ 



/*******************************************
    Συσχετιζόμενα από το a55
*******************************************/
update {p_mydt10tbl1} as mydt10,
       (select mydt10_in.tbl as tbl,
               mydt10_in.tbl_nr01 as tbl_nr01,
               /*v1.12*/
               concat( if(ifnull(mydt10_in.pcor, '')='','#', mydt10_in.pcor) , group_concat(DISTINCT(a55_00.nr01) SEPARATOR '#') , '#') as pcor 
        from {p_mydt10tbl1} as mydt10_in 
        left join i01 on i01.p06=mydt10_in.tbl_nr01
                      && i01.p07=2  
        /*v1.13*/
        left join a55 as a55_arxiko on i01.p06=a55_arxiko.nr01 
                                    && i01.p07=2  
        left join i01 as i01_00 on i01_00.nr01=i01.p3051
        /*v1.13*/
        left join a55 as a55_00 on (    (i01_00.p06=a55_00.nr01 && i01_00.p07=2) 
                                     ||  a55_arxiko.p601=a55_00.nr01
                                    )  
                                 && a55_00.nr01<>mydt10_in.tbl_nr01 
                                 && a55_00.p601a=0 
                                 && a55_00.p414=0
        where mydt10_in.tbl='a55'      
        group by mydt10_in.tbl, mydt10_in.tbl_nr01
        ) as tbl01   
set mydt10.pcor = tbl01.pcor
where mydt10.tbl = tbl01.tbl 
   && mydt10.tbl_nr01 = tbl01.tbl_nr01 
   && tbl01.pcor is not null 
/*peg_end*/