Queries: Current Cost

Below is a PostgreSQL PL/pgSQL function to obtain the total Current Cost for a Product given the M_Product_ID, AD_Org_ID & M_AttributeSetInstance_ID. The Costing Method and Costing Level used are those defined in the Product Category of the Product or if empty the Primary Accounting Schema of the the Client. The result is rounded using the Unit of Measure Costing Precision.

-- Function: product_cost(numeric, numeric, numeric)

-- DROP FUNCTION product_cost(numeric, numeric, numeric);

CREATE OR REPLACE FUNCTION product_cost(
    p_product_id numeric,
    p_org_id numeric,
    p_attributesetinstance_id numeric)
RETURNS numeric AS
$BODY$
DECLARE
    v_CurrentCostPrice   	numeric;
    v_CostPrecision			int;

--	Get total cost of product using Costing Method and Costing Level defined in product category 
--	or if empty the client accounting schema. Round result using UOM costing precision.
BEGIN
	SELECT 	COALESCE(SUM(c.CurrentCostPrice), 0), MAX(u.costingprecision)
	INTO	v_CurrentCostPrice, v_CostPrecision
	FROM 	M_Cost c 
	INNER JOIN AD_ClientInfo ci ON c.AD_Client_ID = ci.AD_Client_ID 
	INNER JOIN C_AcctSchema acct ON ci.C_AcctSchema1_ID = acct.C_AcctSchema_ID 
	INNER JOIN M_CostElement e ON (e.M_CostElement_ID=c.M_CostElement_ID) 
	INNER JOIN M_Product p ON (p.M_Product_ID = p_Product_ID) 
	INNER JOIN M_Product_Category_Acct pca ON (pca.M_Product_Category_ID = p.M_Product_Category_ID) 
	INNER JOIN C_UOM u ON (u.C_UOM_ID = p.C_UOM_ID)
	WHERE e.CostingMethod = COALESCE(pca.CostingMethod, acct.CostingMethod) AND c.M_Product_ID=p_Product_ID 
	AND c.C_AcctSchema_ID = ci.C_AcctSchema1_ID 
	AND pca.C_AcctSchema_ID = ci.C_AcctSchema1_ID 
	AND CASE WHEN COALESCE(pca.CostingLevel, acct.CostingLevel) = 'C' THEN c.AD_Org_ID = 0 
	  WHEN COALESCE(pca.CostingLevel, acct.CostingLevel) = 'O' THEN c.AD_Org_ID = p_org_id  
	  WHEN COALESCE(pca.CostingLevel, acct.CostingLevel) = 'B' THEN c.M_AttributeSetInstance_ID = p_attributesetinstance_id END;

RETURN ROUND(v_CurrentCostPrice,v_CostPrecision);

END;
$BODY$
  LANGUAGE plpgsql STABLE;