git.roojs.org Git - xtuple/blob - foundation-database/public/tables/metasql/briefEarnedCommission-detail.mql

   1 -- Group: briefEarnedCommission
   2 -- Name: detail
   3 -- Notes: used by dspBriefEarnedCommissions
   4 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
   5 -- See www.xtuple.com/CPAL for the full text of the software license.
   6
   7 SELECT cohist_salesrep_id, salesrep_number, salesrep_name, cust_number, cust_name,
   8        cohist_ordernumber, cohist_invcnumber, cohist_invcdate, currAbbr,
   9        SUM(extprice) AS sumextprice,
  10        SUM(cohist_commission) AS sumcommission,
  11        SUM(baseextprice) AS sumbaseextprice,
  12        SUM(basecommission) AS sumbasecommission,
  13        'curr' AS sumextprice_xtnumericrole,
  14        'curr' AS sumcommission_xtnumericrole,
  15        'curr' AS sumbaseextprice_xtnumericrole,
  16        'curr' AS sumbasecommission_xtnumericrole,
  17        0 AS sumbaseextprice_xttotalrole,
  18        0 AS sumbasecommission_xttotalrole
  19 <? if exists("includeMisc") ?>
  20 FROM saleshistorymisc
  21 <? else ?>
  22 FROM saleshistory
  23 <? endif ?>
  24 WHERE ((cohist_commission <> 0)
  25     AND(cohist_invcdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
  26     <? if exists("includeMisc") ?>
  27     AND (COALESCE(cohist_misc_type, '') <> 'T')
  28     AND (COALESCE(cohist_misc_type, '') <> 'F')
  29     <? endif ?>
  30     <? if exists("salesrep_id") ?>
  31     AND (cohist_salesrep_id=<? value("salesrep_id") ?>)
  32     <? endif ?>
  33       )
  34 GROUP BY cohist_salesrep_id, salesrep_number, salesrep_name, cust_number, cust_name,
  35          cohist_ordernumber, cohist_invcnumber, cohist_invcdate, currAbbr
  36 ORDER BY salesrep_number, cust_number, cohist_invcdate;