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

   1 -- Group: billingSelections
   2 -- Name: detail
   3 -- Notes: used by dspBillingSelections
   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 cobmisc_id, cohead_id,
   8        cohead_number, cust_number, cust_name,
   9        sum(round(coitem_price*cobill_qty,2)) AS subtotal,
  10        cobmisc_misc, cobmisc_freight, calcCobmiscTax(cobmisc_id) AS cobmisc_tax, cobmisc_payment,
  11        (sum(round(coitem_price * cobill_qty, 2)) +
  12                   cobmisc_misc + cobmisc_freight +
  13                   calcCobmiscTax(cobmisc_id)) AS total,
  14        'curr' AS subtotal_xtnumericrole,
  15        'curr' AS total_xtnumericrole,
  16        'curr' AS cobmisc_misc_xtnumericrole,
  17        'curr' AS cobmisc_freight_xtnumericrole,
  18        'curr' AS cobmisc_tax_xtnumericrole,
  19        'curr' AS cobmisc_payment_xtnumericrole
  20 FROM cobmisc, cohead, custinfo, coitem, cobill
  21 WHERE ((cobmisc_cohead_id=cohead_id)
  22    AND (cohead_cust_id=cust_id)
  23    AND (coitem_cohead_id=cohead_id)
  24    AND (cobill_coitem_id=coitem_id)
  25    AND (NOT cobmisc_posted))
  26 GROUP BY cobmisc_id, cohead_id, cohead_number, cust_number,
  27          cust_name, cobmisc_misc, cobmisc_freight, cobmisc_tax, cobmisc_payment
  28 ORDER BY cohead_number;