How to migrate reports and processes to new Cost Engine
Objective
The objective of this article is to describe how to migrate existing reports and processes to the new Cost Engine.
Acknowledgments
Since Openbravo 3 MP13 it is available a new Cost Engine. This new engine has many improvements over the old one:
- Costs is calculated by legal entity.
- It is also possible to calculate them by warehouse
- Costs are not recalculated, avoiding the need to reset accounting.
- It is possible to develop new costing algorithm using extension modules.
- Costs are calculated in the legal entity currency
Refer to Costing Server document for extended information.
New Data structure
In the new engine the source of the costs has changed because it has to be independent of the Costing Algorithm used. This means that it cannot be a date range based cost as in the M_Costing table which works for Average or Standard costs. Forced by this requirement a new table M_Transaction_Cost has been created. With the new Costing Engine the cost of each material transaction is calculated, and the result is stored in the new table. Posterior cost adjustments will also be stored on that table. All costs are calculated in the legal entity currency. The currency is stored with each cost calculation to avoid inconsistencies by currency changes in the legal entity. Existing report and processes need to be adjusted to get the cost information from these new tables.
New columns in M_Transaction
- Transaction Process Date (trxprocessdate)
- The date when the material transaction is processed. All transactions are calculated ordered by this date.
- Is Cost Calculated (iscostcalculated)
- Flag that determines that the transaction cost has been calculated. If this is false the cost of the transaction is unknown.
- Costing Algorithm (m_costing_algorithm_id)
- Indicates the algorithm used to calculate the cost of the transaction.
- Transaction cost (transactioncost)
- The total cost amount of the transaction. This cost does not include subsequent cost adjustments. The cost is always calculated in absolute amounts, this means that when it is an outgoing transaction (negative movement quantity) the cost has to be negated.
- Currency
- The currency of the calculated cost amount. It corresponds with the legal entity currency when the cost was calculated.
New table M_Transaction_Cost
Main columns:
- Transaction
- Material transaction that the cost applies to.
- Cost
- Total cost amount related to the transaction.
- Cost Date
- The date when the cost is recognized. Usually is the transaction process date, but cost adjustments might have later dates.
- Currency
- The currency of the calculated cost amount. It corresponds with the legal entity currency when the cost was calculated.
New database function M_Get_Transaction_Cost
This function returns the cost of a transaction summing all its transaction costs with date equals or before the given one and converted to the given currency. As it is based in the m_transaction table it only returns cost for products that are Item type and stocked.
Parameters:
- Transaction id
- Identifier of the transaction whose cost is desired to get.
- Date
- Date limit to include all the costs related with the transaction.
- Currency
- Currency of the output cost amount.
New database function M_Get_No_Trx_Product_Cost
This function is intended to by used to get the products that are not stocked and Item type. These products won't have records in the m_transaction. It's cost is defined as Standard cost in the Costing tab of the Product window. This function can also get costs of Average type. It returns the corresponding unit cost converted to the given currency.
Parameters:
- Product id
- Product to get the cost.
- Movement Date
- Date when the cost is desired to be valid.
- Cost Type
- Cost type of the M_Costing cost. Possible values are AVA (Average) and STA (Standard)
- Org id
- Organization where the cost has to be valid.
- Warehouse id
- Warehouse where the cost has to be valid, in case it is defined at warehouse level.
- Currency id
- Currency to get the cost in.
- islegalentity
- Optional flag that can be used when a legal entity is passed as org id to avoid calculating it inside the function.
New database function M_Get_Trx_And_Product_Cost
Common function that returns the total cost amount in the given currency. Based on the transaction id parameter calls the M_Get_No_Trx_Product_Cost function when its value is null and the M_Get_Transaction_Cost when it is not null. This function can be used to avoid the complexity of checking the product type and whether transactions exists or not. Returns the total cost amount in the given currency.
Parameters:
- Transaction id
- Identifier of the transaction whose cost is desired to get.
- Product id
- Product to get the cost.
- Movement Date
- Date when the cost is desired to be valid.
- Cost Type
- Cost type of the M_Costing cost. Possible values are AVA (Average) and STA (Standard)
- Org id
- Organization where the cost has to be valid.
- Warehouse id
- Warehouse where the cost has to be valid, in case it is defined at warehouse level.
- Qty
- Quantity of the movement whose cost is desired to known. Used to calculate the total cost amount when the M_Get_No_Trx_Product_Cost is used.
- Precision
- Standard precision of the currency to round the amounts.
- Currency id
- Currency to get the cost in.
New CostingUtils class
This utility class has several public methods. Main methods:
- getTransactionCost(MaterialTransaction transaction, Date date, boolean calculateTrx)
- Calculates the total cost amount of a transaction including the cost adjustments done until the given date.
- getStandardCost(Product product, Organization org, Date date, HashMap<CostDimension, BaseOBObject> costDimensions, boolean recheckWithoutDimensions)
- Calculates the standard cost of a product on the given date and cost dimensions.
- getCurrentValuedStock(Product product, Organization org, Date date, HashMap<CostDimension, BaseOBObject> costDimensions, Currency currency)
- Calculates the value of the stock of the product on the given date, for the given cost dimensions and for the given currency. It only takes transactions that have its cost calculated.
Migration
One of the main changes that needs to be considered is that cost is not dependent of the date as it happens with Average and Standard cost. The costs have to be retrieved material transaction by material transaction. Existing reports and processes need to be updated to use the new source instead of the M_Costing table or M_Get_Product_Cost(product id, date, cost type) database function. In the new cost engine it is needed to get the Material Transaction (M_Transaction_ID) related to the document. Once we have it is possible to use the public methods of CostingUtils, the database functions or a join to the M_Transaction_Cost table. Developer can choose based on performance or ease criteria.
There are a few things that need to be considered and managed:
- Only products in the M_Transaction are calculated by new engine. This is only products with product type Item and stocked
- Remaining products cost is defined as a Standard Cost (STA) in the m_costing table. M_Get_No_Trx_Product_Cost function is available to calculate it.
- There can be transactions that do not have the cost calculated.
- Costs are calculated in the currency of the Legal Entity.
- This currency is stored with the all the costs. When retrieving those values from the tables it has to be checked if a currency conversion is needed.
- Available db functions and methods in CostingUtils class already manage the conversions.
- There are related utility methods in FinancialUtils and OrganizationStructureProvider classes.
