1 CREATE OR REPLACE FUNCTION calcInvoiceAmt(pInvcheadid 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 calcInvoiceAmt(pInvcheadid, 'T');
11 CREATE OR REPLACE FUNCTION calcInvoiceAmt(pInvcheadid INTEGER,
12 pType TEXT) RETURNS NUMERIC STABLE AS $$
13 -- Copyright (c) 1999-2012 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 _amount NUMERIC := 0.0;
25 -- pType: S = line item subtotal
30 SELECT COALESCE(SUM(ROUND((invcitem_billed * invcitem_qty_invuomratio) *
31 (invcitem_price / COALESCE(invcitem_price_invuomratio, 1.0)), 2)), 0.0),
32 COALESCE(SUM(ROUND((invcitem_billed * invcitem_qty_invuomratio) *
33 COALESCE(coitem_unitcost, itemCost(itemsite_id), 0.0), 2)), 0.0)
35 FROM invcitem LEFT OUTER JOIN coitem ON (coitem_id=invcitem_coitem_id)
36 LEFT OUTER JOIN itemsite ON (itemsite_item_id=invcitem_item_id AND itemsite_warehous_id=invcitem_warehous_id)
37 WHERE (invcitem_invchead_id=pInvcheadid);
39 IF (pType IN ('T', 'X')) THEN
40 SELECT COALESCE(SUM(tax), 0.0) INTO _tax
41 FROM (SELECT COALESCE(ROUND(SUM(taxdetail_tax), 2), 0.0) AS tax
43 JOIN calculateTaxDetailSummary('I', pInvcheadid, 'T')ON (taxdetail_tax_id=tax_id)
44 GROUP BY tax_id) AS data;
48 SELECT COALESCE(invchead_freight, 0), COALESCE(invchead_misc_amount, 0)
51 WHERE (invchead_id=pinvcheadid);
54 _amount := CASE pType WHEN 'S' THEN (_subtotal)
55 WHEN 'T' THEN (_subtotal + _tax + _freight + _misc)
57 WHEN 'M' THEN (_subtotal - _cost)
64 $$ LANGUAGE 'plpgsql';