1 CREATE OR REPLACE FUNCTION calcSalesOrderAmt(pCoheadid INTEGER) RETURNS NUMERIC STABLE AS $$
2 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
3 -- See www.xtuple.com/CPAL for the full text of the software license.
6 RETURN calcSalesOrderAmt(pCoheadid, 'T');
11 CREATE OR REPLACE FUNCTION calcSalesOrderAmt(pCoheadid INTEGER,
12 pType TEXT) RETURNS NUMERIC STABLE AS $$
13 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
14 -- See www.xtuple.com/CPAL for the full text of the software license.
16 _subtotal NUMERIC := 0.0;
19 _freight NUMERIC := 0.0;
21 _credit NUMERIC := 0.0;
22 _amount NUMERIC := 0.0;
26 -- pType: S = line item subtotal
29 -- C = allocated credits
33 SELECT COALESCE(SUM(ROUND((coitem_qtyord * coitem_qty_invuomratio) *
34 (coitem_price / coitem_price_invuomratio), 2)), 0.0),
35 COALESCE(SUM(ROUND((coitem_qtyord * coitem_qty_invuomratio) *
36 (CASE WHEN (coitem_subnumber > 0) THEN 0.0 ELSE coitem_unitcost END
37 / coitem_price_invuomratio), 2)), 0.0)
40 WHERE (coitem_cohead_id=pCoheadid)
41 AND (coitem_status != 'X');
43 IF (pType IN ('T', 'B', 'X')) THEN
44 SELECT COALESCE(SUM(tax), 0.0) INTO _tax
45 FROM (SELECT COALESCE(ROUND(SUM(taxdetail_tax), 2), 0.0) AS tax
47 JOIN calculateTaxDetailSummary('S', pCoheadid, 'T')ON (taxdetail_tax_id=tax_id)
48 GROUP BY tax_id) AS data;
51 IF (pType IN ('T', 'B', 'C')) THEN
52 SELECT COALESCE(cohead_freight, 0), COALESCE(cohead_misc, 0),
53 COALESCE(SUM(currToCurr(aropenalloc_curr_id, cohead_curr_id,
54 aropenalloc_amount, CURRENT_DATE)),0)
55 INTO _freight, _misc, _credit
57 LEFT OUTER JOIN aropenalloc ON (aropenalloc_doctype='S' AND aropenalloc_doc_id=cohead_id)
58 WHERE (cohead_id=pCoheadid)
59 GROUP BY cohead_freight, cohead_misc, cohead_curr_id;
62 _amount := CASE pType WHEN 'S' THEN (_subtotal)
63 WHEN 'T' THEN (_subtotal + _tax + _freight + _misc)
64 WHEN 'B' THEN (_subtotal + _tax + _freight + _misc - _credit)
65 WHEN 'C' THEN (_credit)
67 WHEN 'M' THEN (_subtotal - _cost)
74 $$ LANGUAGE 'plpgsql';