Projects:AverageCostRefactor/TechnicalSpecs

Contents 1 Introduction 2 Summary 3 Costing Server 4 Background Process 5 API for algorithms 6 Cost Adjustment 6.1 Cost Adjustment window 6.2 Cost Adjustment process 7 Costing configuration 7.1 Configuration window 7.2 Database artifacts 8 Cost Migration 9 Cost initialization 10 Standard and Average algorithms 10.1 Standard algorithm 10.2 Average algorithm 11 FIFO / LIFO algorithm 12 Speficic Identification algorithm 13 Manufacturing cost update 14 Accounting update 15 Reporting update 15.1 New Infrastructure 15.2 Reports 16 Landed cost 17 Costing Utils

Introduction

In this document all needed developments to deliver the project.

Summary

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.

Costing Server

Java process that calculates the cost of a transaction.

Constructor parameters:

• Transaction to calculate. M_Transaction_ID

Init method:

• 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.

Process method:

1. Get needed algorithm and instantiate it.
2. Set the algorithm in the M_Transaction.
3. Get transaction's cost amount using algorithm api.
   1. Algorithm API implements a generic API for all transaction types.
   2. Each incoming transaction type has it's own default method to calculate it's cost based on the document information.
   3. Algorithms might override these methods at their convenience.
   4. Method for outgoing transactions must be implemented by algorithms.
4. 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

Background Process

Java process extending org.openbravo.service.db.DalBaseProcess.

Steps:

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:

1. Get all transactions that have not been processed. M_Transaction.TransactionCost is null.
2. Order transactions by M_Transaction.TrxProcessDate.
3. Call Costing Process for each one.
4. Commit and start a new transaction for each successfully calculated Material Transaction.

Cost adjustments related to invoices:

1. Get all invoices that have not been checked to adjust the cost.
2. Create a new cost adjustment for each invoice that needs it.
3. Process the cost adjustment.
4. 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.

Table M_CostingAlgorithm".

Main columns (common columns to all tables not included):

• Name
• Description
• JavaClassName that implements the Costing Algorithm class.

Each module implementing an algorithm will have to insert a record for each algorithm on this table.

Cost Adjustment

New document for each cost calculation.

Cost Adjustment window

Read only transactional window with all cost adjustments.

Header

Document Number and Document Type

Fields to identify each cost adjustment document.

Date

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.

Lines

Transaction Id

Transaction the adjustmen applies to.

Type

COGS or Inventory

Amount

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.

Costing configuration

Configuration window

New Cost configuration window to create and manage the costing rules. The same tab has to be added to Product window.

Fields:

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.

Warehouse

Check to break the cost by Warehouse.

Processes:

Initialize

Process to initialize the products to which apply the rule. Rules not applied won't be considered.

Close

Process to close a rule. A rule can only be closed if another rule is available for the product.

Constraints, checks:

Deactivate

It won't be possible to deactivate a rule which has some stock/cost available.

Database artifacts

• New M_CostConfiguration table.
• New M_CostConfiguration_Trg trigger (or hibernate event)

Cost Migration

Cost initialization

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

Standard algorithm

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.

Average algorithm

• 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

Accounting update

Reporting update

New Infrastructure

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.

Reports

List of reports that need to adapt:

• TBD

Landed cost

Costing Utils

Utility class with public methods related to Costs.

getTransactionCost

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.

Parameters:

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.

getStandardCost

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.

Parameters:

product

The Product to get its Standard Cost

date

The Date to get the Standard Cost

costDimensions

The cost dimensions to get the Standard Cost if it is defined by some of them.

recheckWithoutDimensions

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.

Throws:

OBException

when no standard cost is found.

getEmptyDimensions

Returns the costDimensions HashMap with null values for the dimensions.

getCurrentStock

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.

Parameters:

product

date

costDimensions