-- Group: creditMemoCreditCards
-- Name: detail
-- Notes: used by arWorkBench, returnAuthorization, returnAuthorizationWorkbench
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.

SELECT cmhead_number, cmhead_freight, cmhead_curr_id,
       SUM(ROUND((cmitem_qtycredit * cmitem_qty_invuomratio) * 
                 (cmitem_unitprice / cmitem_price_invuomratio), 2)) +
       cmhead_freight + cmhead_misc +
       COALESCE( (SELECT SUM(tax) * -1 AS tax FROM
                 (SELECT ROUND(SUM(taxdetail_tax), 2) AS tax
                  FROM tax
                   JOIN calculateTaxDetailSummary('CM', <? value("cmhead_id") ?>, 'T') ON (taxdetail_tax_id=tax_id)
                  GROUP BY tax_id) AS data), 0) AS total,
       COALESCE( (SELECT SUM(tax) * -1 AS tax FROM
                 (SELECT ROUND(SUM(taxdetail_tax), 2) AS tax
                  FROM tax
                   JOIN calculateTaxDetailSummary('CM', <? value("cmhead_id") ?>, 'T') ON (taxdetail_tax_id=tax_id)
                  GROUP BY tax_id) AS data), 0) AS tax_in_cmcurr,
       ccard_id, ccard_seq, ccpay_id,
       cohead_number
FROM ccard, cmitem, cmhead LEFT OUTER JOIN
     rahead ON (cmhead_rahead_id=rahead_id) LEFT OUTER JOIN
     cohead ON (rahead_orig_cohead_id=cohead_id) LEFT OUTER JOIN
     ccpay  ON (ccpay_order_number=cohead_number AND ccpay_type='C')
WHERE ((cmitem_cmhead_id=cmhead_id)
  AND  (cmhead_cust_id=ccard_cust_id)
  AND  (ccard_active)
  AND  (ccpay_ccard_id=ccard_id OR ccpay_ccard_id IS NULL)
  AND  (cmhead_id=<? value("cmhead_id") ?>))
GROUP BY cmhead_number, cmhead_curr_id, cmhead_freight,
         cmhead_misc, ccard_id, ccard_seq, ccpay_id,
	 cohead_number
ORDER BY ccpay_id DESC, ccard_seq 
LIMIT 1;
