Open Source ERP Academy

Product Cost (Costing)

The purpose of this page is to help you understand, establish and manipulate product costing.

Product Costing Introduction

Product Costing Methods

NOTE: do not use Average Invoice costing method (contrary to what he video says). Use Average PO if you with to use average costing. The reason that you should not use Average Invoice is because it waits until the Invoice is created to create/update the costing records. By then , you might have already moved, scrapped, and/or shipped the product. Any document that created completed before the costing record will result in a posting error. Creating/updating the costing record at the time of receipt (in the case of Average PO) is a much more common practice. It is unfortunate that iDempiere's GardenWorld sample client defaults to Average Invoice.

Average Cost Calculation

iDempiere maintains costing records in the Product window => Costs sub-tab. If you look at a Average PO costing record, you will notice that the costing system maintains the current qty and accumulated value. The Average PO cost is simply the average (accumulated value / qty).

Product Costing - Cost Element

References:

Product Costing Level

References:

Product Costing - Cost Type

Product - Cost Sub-Tab - Fields Explained

Product Costing - Establish Costs

Correction: See resolution (more detail) to the above mentioned bug

Product Costing - Standard Future Cost Review Process

References:

Product Cost Reporting

Code from video:

select il.priceentered, il.c_invoiceline_id, iol.m_inoutline_id, cd.currentcostprice
from c_invoiceline il
join c_invoice i on il.c_invoice_id = i.c_invoice_id
left outer join m_inoutline iol on il.m_inoutline_id = iol.m_inoutline_id
left outer join m_costdetail cd on iol.m_inoutline_id = cd.m_inoutline_id
where i.issotrx= 'Y'

PostgreSQL Function to Find Cost

There are times when you need to find a product cost from the M_Cost table (as opposed to M_CostDetail - historical cost). Finding the cost is surprisingly complex due to the nature of how costs are managed. Below is function definition to find a cost.

CREATE OR REPLACE FUNCTION adempiere.chuboe_product_cost(p_client_id numeric, p_org_id numeric, p_acctschema_id numeric, p_product_id numeric)
 RETURNS numeric
 LANGUAGE sql
AS $function$
 ( SELECT sum(coalesce(cost.currentcostprice,0) + coalesce(cost.currentcostpricell,0)) AS cost
                   FROM m_product p
             JOIN m_product_category pc ON p.m_product_category_id = pc.m_product_category_id
             JOIN m_product_category_acct pca ON pc.m_product_category_id = pca.m_product_category_id and pca. c_acctschema_id = p_acctschema_id
             JOIN c_acctschema asch on asch.c_acctschema_id = p_acctschema_id
             JOIN m_costelement ce on ce.costingmethod = COALESCE(pca.costingmethod, asch.costingmethod)
             JOIN m_cost cost on cost.m_product_id = p_product_id and cost.m_costelement_id = ce.m_costelement_id and cost.ad_org_id = p_org_id and cost.C_AcctSchema_ID = asch.C_AcctSchema_ID
                  WHERE p.m_product_id = p_product_id)
$function$

How to Manually Create Costs

When you create or import a product into iDempiere, it does not create all the cost records for you automatically. This situation can cause issues if you want to immediately perform a Cost Adjustment on a newly created product. This section helps you create the zero-cost costing records for all products in your system. Please read the below comments before executing this query.

Client Level Costing:

insert into m_cost
select
p.ad_client_id,
0 as ad_org_id,
p.m_product_id,
ct.m_costtype_id,
acct.c_acctschema_id,
ce. m_costelement_id,
0 as m_attributesetinstance_id,
'Y' as isactive,
now() as created,
100 as createdby,
now() as updated,
100 as updatedby,
0 as currentcostprice,
0 as currentqty,
0 as cumulatedamt,
0 as cumulatedqty,
0 as futurecostprice,
null as description,
0 as percent,
0 as currentcostpricell,
0 as futurecostpricell,
'N' as iscostfrozen,
generate_uuid() as m_cost_uu
from m_product p
join c_acctschema acct on p.ad_client_id = acct.ad_client_id
join m_costtype ct on p.ad_client_id = ct.ad_client_id
join m_costelement ce on p.ad_client_id = ce.ad_client_id
where (p.m_product_id, ct.m_costtype_id, acct.c_acctschema_id, ce.m_costelement_id)
not in (select m_product_id, m_costtype_id, c_acctschema_id, m_costelement_id from m_cost)
--and ad_client_id = 11

Org Level Costing:

insert into m_cost
select
p.ad_client_id,
o.ad_org_id,
p.m_product_id,
ct.m_costtype_id,
acct.c_acctschema_id,
ce. m_costelement_id,
0 as m_attributesetinstance_id,
'Y' as isactive,
now() as created,
100 as createdby,
now() as updated,
100 as updatedby,
0 as currentcostprice,
0 as currentqty,
0 as cumulatedamt,
0 as cumulatedqty,
0 as futurecostprice,
null as description,
0 as percent,
0 as currentcostpricell,
0 as futurecostpricell,
'N' as iscostfrozen,
generate_uuid() as m_cost_uu
from m_product p
join c_acctschema acct on p.ad_client_id = acct.ad_client_id
join m_costtype ct on p.ad_client_id = ct.ad_client_id
join m_costelement ce on p.ad_client_id = ce.ad_client_id
join ad_org o on p.ad_client_id = o.ad_client_id and o.issummary='N' and o.isactive='Y'
where (o.ad_org_id, p.m_product_id, ct.m_costtype_id, acct.c_acctschema_id, ce.m_costelement_id)
not in (select ad_org_id, m_product_id, m_costtype_id, c_acctschema_id, m_costelement_id from m_cost)
--and p.ad_client_id <> 11

There are some topics to be aware of before executing the above query: