SELECT tbl01.*, 
       if(tbl01.classificationType_1 ='*', '', tbl01.classificationType_1)  as classificationType, 
       if(tbl01.vclassificationType_1='*', '', tbl01.vclassificationType_1) as vclassificationType, 
       
       ifnull(mydt50.p101, 0) as mydt50_p101, 
       ifnull(mydt50.p102, 0) as mydt50_p102, 
       ifnull(mydt50.p103, 0) as mydt50_p103, 
       ifnull(mydt50.p104, 0) as mydt50_p104 
FROM
(
  SELECT mydt10.nr01, 
         mydt10.uid as uid,
         mydt10.mark as mark,
         ifnull(mydt10.p04, '')  as p04,
         mydt10.tbl  as tbl,
         mydt10.tbl_nr01  as tbl_nr01,
         
         mydt10.is_vat as is_vatNumber,
         mydt10.is_country as is_country,
         concat(mydt10.is_branch, '') as is_branch,
         if( mydt10.is_country='' || mydt10.is_country='GR', '', mydt10.is_name) as is_name,
         mydt10.is_address as is_address,
         mydt10.is_city as is_city,
         mydt10.is_zip as is_zip,
         
         mydt10.cp_vat as cp_vatNumber,
         mydt10.cp_country as cp_country,
         concat(mydt10.cp_branch, '') as cp_branch,
         if( mydt10.cp_country='' || mydt10.cp_country='GR', '', mydt10.cp_name) as cp_name,
         mydt10.cp_address as cp_address,
         mydt10.cp_city as cp_city,
         mydt10.cp_zip as cp_zip,
         
         if(mydt10.series='', '0', mydt10.series) as series,
         mydt10.aa as aa,
         mydt10.issuedate as issueDate,
         mydt10.invtype as invoiceType,
         mydt10.paytype as payType,
         mydt10.vatpaysusp as vatPaymentSuspension,
         mydt10.currency as currency,
         mydt10.exchrate as exchangeRate,
         ifnull(mydt10.correlinv, '') as correlatedInvoices,
         ifnull(mydt10.pcor, '') as pcor,
         mydt10.slfpricing as selfPricing,
         mydt10.dispdate as dispatchDate,
         mydt10.disptime as dispatchTime,
         mydt10.vehiclenum as vehicleNumber,
         mydt10.mvpurpose as movePurpose,
         mydt10.tnetvalue as totalNetValue,
         mydt10.tvat_am as totalVatAmount,
         mydt10.twthhld_am as totalWithheldAmount,
         mydt10.tfees_am as totalFeesAmount,
         mydt10.tstamp_am as totalStampDutyAmount,
         mydt10.ttax_am as totalOtherTaxesAmount,
         mydt10.tdeduction as totalDeductionsAmount,
         mydt10.tgross_val as totalGrossValue, 
         
         mydt12.linenumber as lineNumber,
         mydt12.quantity as quantity,
         mydt12.munit as measurementUnit,
         mydt12.invdtype as invoiceDetailType,
         mydt12.netvalue as netValue,
         mydt12.vatcat as vatCategory,
         mydt12.vatexcat as vatExemptionCategory,
         mydt12.appid as applicationId,
         mydt12.appdate as applicationDate,
         mydt12.doy as doy,
         mydt12.shipid as shipId,
         mydt12.discoption as discountOption,
         mydt12.wthhld_am as withheldAmount,
         mydt12.wthhldpcat as withheldPercentCategory,
         mydt12.stamppcat as stampDutyPercentCategory,
         mydt12.fees_am as feesAmount,
         mydt12.feestype as feesPercentCategory,
         mydt12.otaxpcat as otherTaxesPercentCategory,
         mydt12.otax_am as otherTaxesAmount,
         /*mydt12.deductionp as deductionsPercentage,*/
         mydt12.fpavalue as vatAmount,
         mydt12.stampvalue as stampDutyAmount,
         mydt12.deducvalue as deductionsAmount,
         
         /** classification***/
         mydt10.p00 as mydt10_p00, 
         if(mydt10.p00=1, mydt12.mydt61_p00, mydt12.mydt62_p00) as classificationType_1,
         if(mydt10.p00=1, mydt12.mydt57_p00, mydt12.mydt58_p00) as classificationCategory,
         mydt12.netvalue as class_amount, 
         if(mydt10.p00=1, '', mydt12.mydt62_p01) as vclassificationType_1,
         
         mydt12.comments as lineComments, 
         if(mydt10.p00=1, ifnull( (select sum(if(mydt12_in.mydt57_p00='' && mydt12_in.mydt61_p00='', 1, 0))
                                   from mydt12 as mydt12_in
                                   where mydt12_in.mydt10=mydt10.nr01
                                   group by mydt12_in.mydt10), 0), 0)
         +
         /*v1.11*/                          
         if(mydt10.p00=2, ifnull( (select sum(if(mydt12_in.mydt58_p00='' && mydt12_in.mydt62_p00='', 1, 0))
                                   from mydt12 as mydt12_in
                                   where mydt12_in.mydt10=mydt10.nr01
                                   group by mydt12_in.mydt10), 0), 0)
                                   as nomarked, 
                                   
         if(mydt10.p00=1, ifnull( (select sum(if(mydt12_in.mydt57_p00='' && mydt12_in.mydt61_p00='', 0, 1))
                                   from mydt12 as mydt12_in
                                   where mydt12_in.mydt10=mydt10.nr01
                                   group by mydt12_in.mydt10), 0), 0) 
         +
         /*v1.11*/                          
         if(mydt10.p00=2, ifnull( (select sum(if(mydt12_in.mydt58_p00='' && mydt12_in.mydt62_p00='', 0, 1))
                                   from mydt12 as mydt12_in
                                   where mydt12_in.mydt10=mydt10.nr01
                                   group by mydt12_in.mydt10), 0), 0)                  
                                   as marked, 
         
         mydt12.taxtype   as taxtype,
         mydt12.taxcat    as taxcat,
         mydt12.taxamount as taxamount,
         mydt12.taxunder  as taxunder
         
   FROM mydt12, mydt10
   LEFT JOIN t07 ON t07.p01=mydt10.p03
   LEFT JOIN t05 ON t05.p01=t07.p07
   WHERE mydt12.mydt10=mydt10.nr01
      && mydt10.mark=''
      && mydt10.p01=1
      && mydt10.p02=0
      && mydt10.p05=0
      && mydt10.p09=0
      && (    ifnull(t05.p01, -999999)={sme_t05p01}
           || {sme_t05p01}=-1
          ) 
      && ( {np_where} 
         )
   ORDER BY mydt10.issueDate, mydt10.nr01, mydt12.linenumber
) as tbl01
LEFT JOIN mydt50 ON mydt50.p00=tbl01.invoiceType 
WHERE nomarked=0
   && marked<>0
   && ifnull(mydt50.p103, 0)={sme_t50p103};
