SELECT COALESCE(SUM(ROUND((coitem_qtyord * coitem_qty_invuomratio) *
(coitem_price / coitem_price_invuomratio), 2)), 0.0),
COALESCE(SUM(ROUND((coitem_qtyord * coitem_qty_invuomratio) *
- (coitem_unitcost / coitem_price_invuomratio), 2)), 0.0)
+ (CASE WHEN (coitem_subnumber > 0) THEN 0.0 ELSE coitem_unitcost END
+ / coitem_price_invuomratio), 2)), 0.0)
INTO _subtotal, _cost
FROM coitem
WHERE (coitem_cohead_id=pCoheadid)
-- Overload for future costing enhancements
--
DECLARE
+ _r RECORD;
_cost NUMERIC := 0.0;
BEGIN
- IF (fetchMetricBool('WholesalePriceCosting')) THEN
- SELECT item_listcost INTO _cost
- FROM item
- WHERE (item_id=pItemid);
+ -- cache item info
+ SELECT * INTO _r
+ FROM itemsite, item
+ WHERE (itemsite_item_id=pItemid)
+ AND (itemsite_warehous_id=pSiteid)
+ AND (item_id=pItemid);
+
+ IF (_r.item_type = 'K') THEN
+ SELECT SUM(roundQty(itemuomfractionalbyuom(bomitem_item_id, bomitem_uom_id),
+ (bomitem_qtyfxd + bomitem_qtyper) * (1 + bomitem_scrap))
+ * stdCost(bomitem_item_id)) INTO _cost
+ FROM bomitem
+ WHERE (bomitem_parent_item_id=_r.item_id)
+ AND (bomitem_rev_id=getActiveRevid('BOM', _r.item_id))
+ AND (pEffective BETWEEN bomitem_effective AND (bomitem_expires - 1));
+ ELSEIF (fetchMetricBool('WholesalePriceCosting')) THEN
+ _cost := _r.item_listcost;
ELSE
- SELECT itemcost(itemsite_id) INTO _cost
- FROM itemsite
- WHERE (itemsite_item_id=pItemid)
- AND (itemsite_warehous_id=pSiteid);
+ SELECT itemcost(_r.itemsite_id) INTO _cost;
END IF;
RETURN _cost;
ELSE ((1.0 - (quitem_price / quitem_custprice)) * 100.0)
END AS discountfromcust,
quitem_unitcost AS coitem_unitcost,
- ROUND((quitem_qtyord * quitem_qty_invuomratio) *
- ((quitem_price / quitem_price_invuomratio) - (quitem_unitcost / quitem_price_invuomratio)),2) AS margin,
- CASE WHEN (quitem_price = 0.0) THEN 100.0
+ CASE WHEN (quitem_price = 0.0) THEN 0.0
+ ELSE ROUND((quitem_qtyord * quitem_qty_invuomratio) *
+ ((quitem_price / quitem_price_invuomratio) - (quitem_unitcost / quitem_price_invuomratio)),2)
+ END AS margin,
+ CASE WHEN (quitem_price = 0.0) THEN 0.0
ELSE ((quitem_price - quitem_unitcost) / quitem_price)
END AS marginpercent,
CASE WHEN (quitem_custpn != '') THEN quitem_custpn
coitem_qtyord, qtyshipped, balance, qtyatshipping,
extprice, extprice_shipped,
CASE WHEN (discountfromcust=100.0) THEN 'N/A' END AS discountfromcust_qtdisplayrole,
+ CASE WHEN (margin=0.0) THEN 'N/A' END AS margin_qtdisplayrole,
+ CASE WHEN (marginpercent=0.0) THEN 'N/A' END AS marginpercent_qtdisplayrole,
'qty' AS coitem_qtyord_xtnumericrole,
'qty' AS qtyshipped_xtnumericrole,
'qty' AS balance_xtnumericrole,
ELSE ((1.0 - (coitem_price / coitem_custprice)) * 100.0)
END AS discountfromcust,
coitem_unitcost,
- ROUND((coitem_qtyord * coitem_qty_invuomratio) *
- ((coitem_price / coitem_price_invuomratio) - (coitem_unitcost / coitem_price_invuomratio)),2) AS margin,
- CASE WHEN (coitem_price = 0.0) THEN 100.0
+ CASE WHEN (coitem_price = 0.0) THEN 0.0
+ ELSE ROUND((coitem_qtyord * coitem_qty_invuomratio) *
+ ((coitem_price / coitem_price_invuomratio) - (coitem_unitcost / coitem_price_invuomratio)),2)
+ END AS margin,
+ CASE WHEN (coitem_price = 0.0) THEN 0.0
ELSE ((coitem_price - coitem_unitcost) / coitem_price)
END AS marginpercent,
noNeg(coitem_qtyshipped - coitem_qtyreturned) AS qtyshipped,