In this document all needed developments to deliver the project.
This is the list of developments that will be done. Some of them will require adding new tables and/or columns.
- Costing server
- For each transaction, searches the algorithm to be used and calculates the cost.
- Background process
- Similar to the accounting engine process. It searches all transactions pending to calculate its cost and calculate it.
- API for algorithms
- Abstract classes defining the API of the algorithms.
- Cost Adjustment
- New document to manage each cost calculation.
- Costing configuration
- New window where rules are added to configure the costing engine.
- Cost initialization
- Process to initialize costs when a new rule is added.
- Standard and Average algorithms
- Standard and Average algorithms implementation
- FIFO/LIFO algorithm
- FIFO/LIFO algorithm implementation
- Specific Identification algorithm
- Specific Identification algorithm implementation
- Manufacturing cost update
- Needed changes on manufacturing cost calculation to use the new API.
- Accounting update
- Needed changes on accounting to adapt it to the new API.
- Reporting update
- Needed changes on reports to adapt them to the new API.
- Landed cost
- Implementation of landed cost.
- Costing Utils
- Utility class with public methods related to costing.
Java process that calculates the cost of a transaction.
- Transaction to calculate. M_Transaction_ID
- Get costing rule that applies to the transaction. First are considered rules specific to the product with lower priority number, secondly rules for the product category and lastly generic rules.
- Init trxCost object with transaction cost in case it is already calculated.
- Get needed algorithm and instantiate it.
- Set the algorithm in the M_Transaction.
- Get transaction's cost amount using algorithm api.
- Algorithm API implements a generic API for all transaction types.
- Each incoming transaction type has it's own default method to calculate it's cost based on the document information.
- Algorithms might override these methods at their convenience.
- Method for outgoing transactions must be implemented by algorithms.
- Save calculated cost on M_Transaction and M_Transaction_Cost table.
Changes in the model:
- New column M_CostingAlgorithm_ID (table is defined in the API for algorithms section) in M_Transaction
- New column TransactionCost in M_Transaction. Amount column with the total cost of the transaction.
- New table M_Transaction_Cost. Child of M_Transaction, with date and amount as main columns. It can be related to Cost Adjustments lines
Java process extending org.openbravo.service.db.DalBaseProcess.
The background performs two different big steps. First it calculate the cost of all Material Transactions that does not have its cost calculated. And secondly it loops through all the invoices that have not been checked for changes on the price that require a cost adjustment for the original shipment transaction.
Material Transaction cost calculation:
- Get all transactions that have not been processed. M_Transaction.TransactionCost is null.
- Order transactions by M_Transaction.TrxProcessDate.
- Call Costing Process for each one.
- Commit and start a new transaction for each successfully calculated Material Transaction.
Cost adjustments related to invoices:
- Get all invoices that have not been checked to adjust the cost.
- Create a new cost adjustment for each invoice that needs it.
- Process the cost adjustment.
- Set the invoice as checked.
Changes in the model:
- New column TrxProcessDate on M_Transaction defaulted to SYSDATE is the date when the transaction is registered on the system.
API for algorithms
Algorithms are stored in a new window where is defined the name of the algorithm, a brief description and the Java class name with the implementation.
Main columns (common columns to all tables not included):
- JavaClassName that implements the Costing Algorithm class.
Each module implementing an algorithm will have to insert a record for each algorithm on this table.
New document for each cost calculation.
Cost Adjustment window
Read only transactional window with all cost adjustments.
- Document Number and Document Type
- Fields to identify each cost adjustment document.
- Date when adjustment takes place. Used for accounting.
- Inventory amount
- Total amount that needs to be updated on the inventory valuation account.
- COGS amount
- Total amount that needs to be updated on the COGS account.
- Table Id and Record Id
- Fields to identify the document that originates the adjustment.
- Transaction Id
- Transaction the adjustmen applies to.
- COGS or Inventory
- The amount to be adjusted for this transaction and type.
Cost Adjustment process
Process to manage cost calculations. If a new cost is added to an already calculated transaction it has to call the corresponding algorithm api to make the needed adjustments on related transactions. It has to check that the amounst are correct. A new record on the M_Transaction_Cost table is created for each line.
New Cost configuration window to create and manage the costing rules. The same tab has to be added to Product window.
- Costing algorithm
- Costing algorithm to be used.
- Date From
- Starting date to be applied the algorithm. Read Only. It is populated automatically when the rule is validated.
- Date to
- Ending date to be applied the algorithm. Read Only. It is populated when the rule is closed.
- Check to break the cost by Warehouse.
- Process to initialize the products to which apply the rule. Rules not applied won't be considered.
- Process to close a rule. A rule can only be closed if another rule is available for the product.
- It won't be possible to deactivate a rule which has some stock/cost available.
- New M_CostConfiguration table.
- New M_CostConfiguration_Trg trigger (or hibernate event)
Whenever a new costing rule is added the cost of the affected products need to be initialized with the new algorithm. It is only possible to validate a new rule when the instance has been migrated to the new cost engine or when no migration is needed.
There are 2 possible scenarios. 1st, there is a previous costing rule that applies to the legal entity. 2nd, any cost has never been calculated. The first scenario means that the starting date of the rule will be the date when the costing rule is validated. While in the second scenario the starting date is the first date in the system. Defaulted to January 1st, 1900.
When there is a previous costing rule, this is overwritten. The process creates two physical inventories for each organization and warehouse the rule applies to. These inventories will empty all the stock available and fill it in again. The empty inventory is processed and its cost is calculated using the previous rule. The fill in inventory is processed using the new rule. The same cost calculated on the empty inventory is set on each inventory line. To achieve this the starting date of the rule has to be between the process date of both inventories. To be able to validate the rule all the transaction costs of the related products have to be calculated.
When there is not a previous costing rule, it has to be checked that existing transactions do not have it's cost calculated. And it shouldn't be any record on the m_costing table with a cost type different to Standard.
Standard and Average algorithms
The cost is stored for a date range in the M_Costing table. This cost is used for all transactions during that period of time.
- Cost is calculated based on a weighted average of the existing stock and the incoming stock.
- Cost is stored in existing M_Costing table for all incoming transactions. New cost type, AVA, is used to differentiate from old engine's average.
- New column M_Warehouse_ID on M_Costing table.
- New column M_Transaction_ID on M_Costing table.
- Overridden methods
- getInventoryIncreaseCost: Changed to use first the current average cost. If no average cost is found the default method its called.
- Transaction type list that trigger an insert in the M_Costing table recalculating the average cost:
- Receipt, ReceiptVoid, ReceiptReturn, ShipmentVoid, ShipmentReturn, ShipmentNegative, InventoryIncrease, IntMovementTo, InternalConsNegative, InternalConsVoid, BOMProduct.
- In case that on a transaction that triggers an insert in the M_Costing results in a zero stock level but not all inventory consumed a cost adjustment has to be triggered to adjust that cost difference on previous transactions.
- For example: Void a receipt with a price different than the current average cost.
FIFO / LIFO algorithm
Speficic Identification algorithm
Manufacturing cost update
The cost is now calculated by transaction and it is stored as an amount by transaction instead of a cost by unit. These changes the way that reports will show and calculate the costs.
A new procedure M_GET_TRANSACTION_COST(m_transaction_id, date) has been created. It returns the total cost amount of the given transaction. If the transaction is from a date when old engine was in use it calls the existing M_GET_PRODUCT_COST to get the unitary cost to be multiplied by the quantity of the given transaction.
List of reports that need to adapt:
Utility class with public methods related to Costs.
Calculates the total cost amount of a transaction including the cost adjustments done until the given date. It exists an additional method without the boolean flag that calls this method setting the flag to false.
- transaction: MaterialTransaction to get its cost.
- date: The Date it is desired to know the cost.
- calculateTrx: boolean flag to force the calculation of the transaction cost if it is not calculated.
Calculates the standard cost of a product on the given date and cost dimensions. It exists an additional method without the boolean flag that calls this method setting the flag to true.
- The Product to get its Standard Cost
- The Date to get the Standard Cost
- The cost dimensions to get the Standard Cost if it is defined by some of them.
- boolean flag to force a recall the method to get the Standard Cost at client level if no cost is found in the given cost dimensions.
- when no standard cost is found.
Returns the costDimensions HashMap with null values for the dimensions.
Calculates the stock of the product on the given date and for the given cost dimensions. It only takes transactions that have its cost calculated.