Projects:AverageCostRefactor/PreliminarySpecs

Contents 1 Introduction 2 Justification 3 Functional Specification 3.1 Users and Roles 3.1.1 Core Roles 3.1.2 Additional Roles 3.2 User Stories 3.2.1 Story 1 - Configuration 3.2.2 Story 2 - Ordering Goods 3.2.3 Story 3 - Receiving Goods 3.2.4 Story 4 - Calculating Average Cost 3.2.5 Story 5 - Moving Goods 3.2.6 Story 6 - Production of Goods 3.3 Processes affected 3.3.1 Processes that generate product costs 3.3.2 Accounting processes 3.3.3 Reports 4 Technical Specification 4.1 Considerations 4.2 Data Model 4.2.1 M_Costing 4.3 Deprecated elements 4.4 New Business Events 4.5 Processes to review 5 Doubts

Introduction

The following requirements are behind this development:

• calculate average cost per organization and per warehouse configurable by the user
• calculate average cost in the currency of the Organization configurable by the user
• always calculate average cost using the price of the Purchase Order Price instead of the Purchase Invoice
• ability for the posting process of Goods Receipt and Match Invoice to use the price of the Purchase Order instead of Average Cost, again, configurable by the user at product level

Justification

Having a multi-organizational ERP platform has quickly shown the need for the product costing that is multi-org and multi-warehouse as well.

Functional Specification

Users and Roles

Product costing relates to three important but different processes:

1. Calculating or updating the actual costing of a product
2. Using the product costing value in order to post relevant documents to the general ledger
3. viewing the value of inventory (reporting)

The users and roles involved are directly related to the above processes.

Core Roles

Let's see how the standard set of roles available in core Openbravo 3 are involved in the above processes:

• Material Management - creates the following documents that trigger the product costing recalculation:
  • Goods Receipt
  • Goods Movement
• Company Executive - may view a stock report to see the current value of the inventory
• Sales - creates invoices that are posted to the ledger and use the costing value, however, this process is transparent to sales users so they are not directly affected by these processes
• Finance:
  • defines product costing calculation parameters
  • manages the accounting side (all postings)
  • may enter manual costing entries for products
  • views stock report that reflects inventory value
• Purchase Management - enters Purchase Order documents that are the data source of product costing calculation
• Employee - not affected by these processes
• Company Admin - may do all of the above

Additional Roles

One process not covered by the core footprint of Openbravo 3 and the roles included is production. A floor manager enters Work Efforts that are the source data for calculating the costing of final products manufactured by that process.

User Stories

Story 1 - Configuration

As part of the initial ERP setup, Ana, the company's accountant sets up the following:

• the Cost Type for each product with the following options available:
  • Standard
  • Average per Client
  • Average per Organization/Warehouse
  • Purchase Order Price
• Standard Cost in case of the previously selected type indicates so
• a manual entry into the Costing tab if no automatic process will be used to calculate them (setting Manual checkbox to Yes)
• makes sure the following tables are active for accounting for all general ledger configurations using the Financial Management || Accounting || Setup || Genera Ledger Configuration screen:
  • m_inout

Specifically, the two products are configured as:

• Ale Beer with Average per Organization/Warehouse cost type
• Lager Beer with Purchase Order Price cost type

Story 2 - Ordering Goods

Jane orders 400 units of Ale Beer from Happy Drinks vendor to the east coast headquarters of F&B in US by filling out and completing a standard Purchase Order.

She fills in a separate order to the same vendor for 600 units of Lager Beer, ordered and to be delivered to the west coast headquarters.

Story 3 - Receiving Goods

Mike who works in warehousing receives a package that contains items that have been ordered a week ago by Jane. He creates a Goods Receipt with the corresponding lines, recording the receipt of 400 units of Ale Beer into the East. He completes the Goods Receipt and views the posting that should be automatically be done within a minute or so.

Story 4 - Calculating Average Cost

Ana from accounting triggers the Generate Average Cost process in order to recalculate them based on previous orders and receipts.

Looking into the Costing tab of both products (Ale and Lager beer), she should now see a new record that represents the new stock value of those products.

Story 5 - Moving Goods

Mike moves 200 units of Ale Beer from east coast warehouse to the west coast. The following posting should be generated????????????

Story 6 - Production of Goods

Processes affected

Processes that generate product costs

• Goods Receipt
• Production (P+)
• Goods movement
• Manual entries to Costing tab

Accounting processes

• Goods receipt/Shipment
• Matched Invoice
• Goods movement
• Physical inventory
• Production (Work Effort)

Here is an overview how accounting postings are done and sequenced:

Reports

The Valued Stock Report uses product costing to display the value of inventory on a given date.

Technical Specification

Considerations

Implementation of new costing features will be delivered as part of the core. No new modules are required. A new architecture of business events shall be used and the old stored procedures deprecated.

Data Model

M_Costing

The following properties of the Costing entity play a significant role in the average cost calculation:

• ad_org_id
• m_product_id
• datefrom
• dateto
• ismanual
• m_inoutline_id
• c_invoiceline_id
• qty
• price
• cumqty
• costtype
• ispermanent
• cost
• m_productionline_id - set when the entry to this entity is done by the production cost calculation process
• isproduction - set to Y when the entry to this entity is done by the production cost calculation process, otherwise N
• isactive - not

A new property needs to be added:

• m_warehouse_id - a nullable foreign key pointing to the warehouse the costing applies to (if any)

Deprecated elements

The following elements are to be deprecated due to the new automatic cost calculation instead of old manual batch calculation done by stored procedures:

• stored procedure M_GENERATE_AVERAGE_COST
• stored procedure M_GENERATE_STANDARD_COST
• stored procedure MA_PRODUCTION_COST_GENERATE

New Business Events

• onNew event for M_InOut
• onNew event for

Processes to review

• Goods receipt/Shipment posting: DocInOut.java
• Matched Invoice posting: DocMatchInv.java
• Goods movement posting: DocMovement.java
• Physical inventory posting: DocInventory.java
• Production posting: DocProduction.java

Doubts

Q: Is M_Costing.C_Invoiceline_ID used at any point?

A: Yes it is used in the existing/old calculation when only an invoice is available as the price source for the average calculation.

Q: Is IsPermanent flag ever set to Y and what does it mean if it is? I could not find any business logic that uses the Y value.

A: It is always set to N by the calculation process and must be manually set to Y for records that the batch calculation process should not delete on the next run (M_Generate_average_costs deletes all records with IsPermanent=N on every run)

Q: How is CumQty calculated?

A: It is calculated based on current stock at that given date, i.e. previous quantity minus all goods shipment plus all goods receipts)

Q: What does the manual check mean?

A: It is used to distinguish between an entry done by the process (Not manual) and a manual entry into the Costing tab. However, what is confusing for the end user is the ability to set this to any record. It should be readonly and automatically set by the system to Y when manually adding records and N otherwise.

Q: Where is the posting accounting class for the following docs that post using the costing value:

• Goods receipt/Shipment
• Matched Invoice
• Goods movement
• Physical inventory
• Production

A:

• Goods receipt/Shipment: DocInOut.java
• Matched Invoice: DocMatchInv.java
• Goods movement: DocMovement.java
• Physical inventory: DocInventory.java
• Production: DocProduction.java

Q: What is the difference between a Purchase Invoice and a Matched Invoice?

A: Matched invoice is a document that links a goods receipt with a purchase invoice. This document's entries are stored in M_MatchInv table. It is used in order to post differences between the posting of the goods receipt which derives from prices in the Purchase Order and the actual final prices on the Purchase Invoice.

Q: When is the value of the Product >> Standard Cost field used?

A: We have no clue...probably never

Q: When running the Generate Average Costs process, the following message can be received by the user:

"Error: 
One or more Goods Receipt documents are posted using a cost that will be modified if the Generate Average Cost process is run. Please unpost the document with Document No. "

What does that mean and why is it happening?

A: This is a weird behaviour that is very difficult for the end-user to understand and manage and will be removed with the new process.

Q: Technically, should it be implemented by using the "Complete" process of the underlying documents or onUpdate business event which triggers when a document is completed.