Estimated Landed Cost (AveragePO)

From iDempiere en

according to

Landed cost

NF2.0 Estimated Landed Cost

Jira ticket


Issue with Average PO + Landed Cost in iDempiere

Background

The NF2.0 Estimated Landed Cost documentation mainly describes the process of handling landed cost under Average Invoice Costing.

That flow is officially supported and works correctly: estimated landed cost is defined on the Purchase Order, allocated during material receipt, cleared with invoices, and finally consolidated into product cost.

However, when switching the costing method to Average PO (Purchase Order Price) + Landed Cost, the current implementation in iDempiere shows inconsistent and incorrect behavior.

Problem Description

When using Average PO + Landed Cost, the system calculates product cost and landed cost separately in MCostDetail.

  • Product Cost: derived directly from the purchase order line amount.
  • Landed Cost: allocated via MLandedCostAllocation and posted as additional MCostDetail.

The issue is that the landed cost allocation does not only consider the current receipt quantity, but instead relies on the cumulative total quantity of the product.

As a result:

land cost setting and purchase order
  1. First receipt works correctly:
    • Example: Product A, PO = 20 units @ 10 = 400, Estimated Landed = 100 → Total 300.
    • Receiving 2 units → cost is 10 + 5 = 15 each (OK).

[[File:MR1.png|thumb|Material Receipt 1[[File:ProductCost1.png|thumb|show cost detail ( after Material Receipt 1)

show product cost (after Material Receipt 1)

]]]]

  1. Second receipt for the same product misbehaves:
    • Receiving another 2 units should also be 15 each.
    • Instead, landed cost is recalculated using total 4 units, leading to 20 each (incorrect).

[[File:MR2.png|thumb|Material Receipt 2[[File:ProductCost2.png|thumb|

Product Cost ( after Material Receipt 2)

cost detail (after Material Receipt 2 ,incorrect)]]]]

  1. Reversing a receipt does not fully revert landed cost allocation:
    • Product cost is reversed, but landed cost remains partially applied.
    • Reverse receipt cost
      This leaves inconsistent cost history and wrong average cost.

for second receipt issue

modify these place can work for me

package org.compiere.model;

MCostHistory.java

	protected static MCostHistory get(Properties ctx, int AD_Org_ID,
			int M_CostType_ID, int C_AcctSchema_ID, String costingMethod, int M_CostElement_ID,
			int M_AttributeSetInstance_ID, MCostDetail cd, 
			String trxName)
	{
		if (cd == null)
			return null;
		
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT c.* ");
		sql.append("FROM M_CostHistory c ");
//		sql.append("JOIN M_CostDetail cd ON (cd.M_CostDetail_ID = c.M_CostDetail_ID AND cd.Processed=?) ");
		sql.append("JOIN M_CostDetail cd ON (cd.M_CostDetail_ID = c.M_CostDetail_ID ) ");
		sql.append("LEFT JOIN M_CostDetail refcd ON (refcd.M_CostDetail_ID=cd.Ref_CostDetail_ID) ");
		sql.append("LEFT OUTER JOIN M_CostElement ce ON (c.M_CostElement_ID=ce.M_CostElement_ID) ");
		sql.append("WHERE c.AD_Client_ID=? AND c.AD_Org_ID=? ");
		sql.append(" AND c.M_Product_ID=? ");
		sql.append(" AND (c.M_AttributeSetInstance_ID=? OR c.M_AttributeSetInstance_ID=0) ");
		sql.append(" AND c.M_CostType_ID=? AND cd.C_AcctSchema_ID=? ");
		sql.append(" AND (ce.CostingMethod IS NULL OR ce.CostingMethod=?) ");
		if (M_CostElement_ID > 0)
			sql.append(" AND c.M_CostElement_ID=? ");
//		sql.append(" AND c.M_CostDetail_ID IN (?,?) ");
		sql.append(" AND c.M_CostDetail_ID IN (?,?,?) ");
		sql.append(" AND c.DateAcct=? ");
		sql.append("ORDER BY c.DateAcct DESC, ");
//		sql.append("CASE WHEN COALESCE(refcd.DateAcct,cd.DateAcct) = cd.DateAcct THEN COALESCE(cd.Ref_CostDetail_ID, c.M_CostDetail_ID) ELSE c.M_CostDetail_ID END DESC, ");
		sql.append("CASE WHEN COALESCE(refcd.DateAcct,cd.DateAcct) = cd.DateAcct THEN COALESCE(cd.Ref_CostDetail_ID, c.M_CostHistory_ID) END DESC NULLS LAST, ");
		sql.append("c.M_CostHistory_ID DESC ");
		String sqlStr = DB.getDatabase().addPagingSQL(sql.toString(), 1, 1);
		 
		List<Object> params = new ArrayList<Object>();
//		params.add(cd.isDelta() ? "N" : "Y"); // cost detail is set to processed=N when it is a delta record
		params.add(cd.getAD_Client_ID());
		params.add(AD_Org_ID);
		params.add(cd.getM_Product_ID());
		params.add(M_AttributeSetInstance_ID);
		params.add(M_CostType_ID);
		params.add(C_AcctSchema_ID);
		params.add(costingMethod);
		if (M_CostElement_ID > 0)
			params.add(M_CostElement_ID);
		params.add(cd.getM_CostDetail_ID());
		params.add(cd.getM_CostDetail_ID()-1); // find averagepo line in costElement line
 		params.add(cd.getRef_CostDetail_ID());
 		params.add(cd.getDateAcct());
		
		MCostHistory costHistory = null;
		PreparedStatement pstmt = null;
    	ResultSet rs = null;
    	try
    	{
    		pstmt = DB.prepareStatement(sqlStr, trxName);
    		DB.setParameters(pstmt, params);
    		rs = pstmt.executeQuery();
    		if (rs.next()) {
    			costHistory = new MCostHistory(ctx, rs, trxName);
    			costHistory.makeImmutable();
    		}
    	}
    	catch (SQLException e)
		{
			throw new DBException(e, sql.toString());
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}		
		return costHistory;
	}


MMatchPO.java

	public static MMatchPO[] getOrderLine (Properties ctx, int C_OrderLine_ID, String trxName)
	{
		if (C_OrderLine_ID == 0)
			return new MMatchPO[]{};
		//
		String sql = "SELECT * FROM M_MatchPO WHERE C_OrderLine_ID=? order by M_MatchPO_id asc";

......

MCostDetail.java

	protected boolean process (MAcctSchema as, MProduct product, MCostElement ce, 
		int Org_ID, int M_ASI_ID)
	{

........
boolean costAdjustment = false;
		if (this.getM_CostElement_ID() > 0 && this.getM_CostElement_ID() != ce.getM_CostElement_ID())
		{
			MCostElement thisCostElement = MCostElement.get(getCtx(), getM_CostElement_ID());
			if (thisCostElement.getCostingMethod() == null && ce.getCostingMethod() != null)
			{
				qty = BigDecimal.ZERO;
				costAdjustment = true;
				amt=getAmt();
			}
		}


........


reverse problem has not been resolved , if use these code don't use reverse in Material Receipt

it is work for me


MCostDetail.java

	public static boolean createOrder (MAcctSchema as, int AD_Org_ID, 
		int M_Product_ID, int M_AttributeSetInstance_ID,
		int C_OrderLine_ID, int M_CostElement_ID, 
		BigDecimal Amt, BigDecimal Qty,
		String Description, Timestamp DateAcct, int Ref_CostDetail_ID, String trxName)
	{

........

			if (cd.isDelta()&& Amt.compareTo( new BigDecimal(0))>=0)
			{
				cd.setProcessed(false);
				cd.setAmt(Amt);
				cd.setQty(Qty);
			}
			else if (cd.isProcessed())
				return true;	//	nothing to do

........


MCostHistory.java

add this

if(costingMethod.equals("A"))

sql.append(" AND cd.M_CostElement_ID IS NULL ");  // add 

protected static MCostHistory get(Properties ctx, int AD_Org_ID,
			int M_CostType_ID, int C_AcctSchema_ID, String costingMethod, int M_CostElement_ID,
			int M_AttributeSetInstance_ID, MCostDetail cd, 
			String trxName)
	{
		if (cd == null)
			return null;
		
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT c.* ");
		sql.append("FROM M_CostHistory c ");
//		sql.append("JOIN M_CostDetail cd ON (cd.M_CostDetail_ID = c.M_CostDetail_ID AND cd.Processed=?) ");
		sql.append("JOIN M_CostDetail cd ON (cd.M_CostDetail_ID = c.M_CostDetail_ID ) ");
		sql.append("LEFT JOIN M_CostDetail refcd ON (refcd.M_CostDetail_ID=cd.Ref_CostDetail_ID) ");
		sql.append("LEFT OUTER JOIN M_CostElement ce ON (c.M_CostElement_ID=ce.M_CostElement_ID) ");
		sql.append("WHERE c.AD_Client_ID=? AND c.AD_Org_ID=? ");
		sql.append(" AND c.M_Product_ID=? ");
		sql.append(" AND (c.M_AttributeSetInstance_ID=? OR c.M_AttributeSetInstance_ID=0) ");
		sql.append(" AND c.M_CostType_ID=? AND cd.C_AcctSchema_ID=? ");
		sql.append(" AND (ce.CostingMethod IS NULL OR ce.CostingMethod=?) ");
		if (M_CostElement_ID > 0)
			sql.append(" AND c.M_CostElement_ID=? ");
		if(costingMethod.equals("A"))
			sql.append(" AND cd.M_CostElement_ID IS NULL ");  // add
//		sql.append(" AND c.M_CostDetail_ID IN (?,?) ");
		sql.append(" AND c.M_CostDetail_ID IN (?,?,?) ");
		sql.append(" AND c.DateAcct=? ");
		sql.append("ORDER BY c.DateAcct DESC, ");
//		sql.append("CASE WHEN COALESCE(refcd.DateAcct,cd.DateAcct) = cd.DateAcct THEN COALESCE(cd.Ref_CostDetail_ID, c.M_CostDetail_ID) ELSE c.M_CostDetail_ID END DESC, ");
		sql.append("CASE WHEN COALESCE(refcd.DateAcct,cd.DateAcct) = cd.DateAcct THEN COALESCE(cd.Ref_CostDetail_ID, c.M_CostHistory_ID) END DESC NULLS LAST, ");
		sql.append("c.M_CostHistory_ID DESC ");
		String sqlStr = DB.getDatabase().addPagingSQL(sql.toString(), 1, 1);
		 
		List<Object> params = new ArrayList<Object>();
//		params.add(cd.isDelta() ? "N" : "Y"); // cost detail is set to processed=N when it is a delta record
		params.add(cd.getAD_Client_ID());
		params.add(AD_Org_ID);
		params.add(cd.getM_Product_ID());
		params.add(M_AttributeSetInstance_ID);
		params.add(M_CostType_ID);
		params.add(C_AcctSchema_ID);
		params.add(costingMethod);
		if (M_CostElement_ID > 0)
			params.add(M_CostElement_ID);
		params.add(cd.getM_CostDetail_ID());
		params.add(cd.getM_CostDetail_ID()-1); // add
 		params.add(cd.getRef_CostDetail_ID());
 		params.add(cd.getDateAcct());
		
		MCostHistory costHistory = null;
		PreparedStatement pstmt = null;
    	ResultSet rs = null;
    	try
    	{
    		pstmt = DB.prepareStatement(sqlStr, trxName);
    		DB.setParameters(pstmt, params);
    		rs = pstmt.executeQuery();
    		if (rs.next()) {
    			costHistory = new MCostHistory(ctx, rs, trxName);
    			costHistory.makeImmutable();
    		}
    	}
    	catch (SQLException e)
		{
			throw new DBException(e, sql.toString());
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}		
		return costHistory;
	}
Retrieved from "https://wiki.idempiere.org/w-en/index.php?title=Estimated_Landed_Cost_(AveragePO)&oldid=23485"
Cookies help us deliver our services. By using our services, you agree to our use of cookies.
GNU-Lizenz für freie Dokumentation 1.3 oder höher
Powered by MediaWiki