Projects:Org based avg costing

Contents 1 Introduction 2 Functional Specification 2.1 Scope 2.2 User roles & profiles 2.3 Business process definition 2.4 User stories 2.5 Functional Requirements Based on Business Processes 2.5.1 Costing definition 2.5.2 Costing calculation 2.5.3 Costing accounting 2.5.4 Costing reporting 2.6 User Interface Mockups 2.7 Open discussion items 2.8 Closed discussion items 3 Technical Documentation 3.1 Costing definition 3.2 Costing calculation 3.3 Costing accounting 3.4 Costing reporting 4 Development Status 4.1 Costing definition 4.2 Costing calculation 4.3 Costing accounting 4.4 Costing reporting 5 Test cases 5.1 Costing definition 5.2 Costing calculation 5.3 Costing accounting 5.4 Costing reporting

Introduction

Currently Openbravo ERP is not considering multi-organization based average costing. This project aims to fix this issue.

• Developers:
  • Eduardo Argal <ear at openbravo dot com>
  • Gorka Ion Damián <gda at openbravo dot com>
  • Rafa Roda Palacios <rro at openbravo dot com>
• Issue: 0011317
• Project branch: https://code.openbravo.com/erp/devel/pi-costorg/

Functional Specification

Scope

This project will review the whole cycle of average costing in order to enable it as multi-organization:

1. Costs definition.
2. Costs calculation (generation).
   1. By reception of material.
   2. By manufacturing of products.
3. Costs accounting.
4. Costs reporting.

User roles & profiles

Warehousing manager: the person who controls the warehouses ins and outs as well as the valuation of their stock.

Production manager: the person who controls the manufacturing of products as well as the cost associated to their production.

Accounting manager: the person who controls the finances of a company both by controlling that every transaction happening in the company has been accounted and by preparing the financial reports.

Business process definition

Warehousing manager:

1. The warehousing manager of a manufacturing company is accepting receipts of materials (goods receipts) in each of the company factories (branches) around the country.
2. Each of these factories' warehouse receives the same materials.
3. Anyway, the warehousing manager would like to know the reception cost of each material per each of the factories. In other words, he would like to see whether a material costs more to be received in a factory than in another one.
4. Besides, he would like this material cost calculation to be triggered automatically on each material reception, without having to manually run a process.

Production manager:

1. The production manager of a manufacturing company is controlling the manufacturing of products (completion of work requirements) in each of the company factories (branches) around the country.
2. Each of these factories produces the same products.
3. Anyway, the production manager would like to know the production cost of each product per each of the factories. In other words she would like to see whether a product costs more to be manufactured in a factory than in another one.
4. Besides, he would like this product cost calculation to be triggered automatically on each product manufacturing, without having to manually run a process.

Accounting manager:

1. The accounting manager of a manufacturing company is preparing the financial reports for each of the branches (factories or organizations) of the company.
2. One of the accounting concepts to be accounted is the materials cost.
3. She wants the cost of each material to be properly split and accounted in its corresponding branch (factory or organization).
4. Once a material cost has been accounted, she expects this material cost not to be changed. In other words, the material cost already accounted:
   1. is un-accounted if it will be recalculated.
   2. can not be re-calculated if the period where it was accounted is closed.

User stories

Warehousing manager:

1. John, the warehousing manager of a manufacturing company, is accepting receipts of materials (goods receipts) in each of the company factories (branches) around the country.
2. Each of these factories' warehouse receives the same materials.
3. Anyway, the warehousing manager would like to know the reception cost of each material per each of the factories. In other words, he would like to see whether a material costs more to be received in a factory than in another one.
4. Besides, he would like this material cost calculation to be triggered automatically on each material reception, without having to manually run a process.

Production manager:

1. Mary, the production manager of a manufacturing company, is controlling the manufacturing of products (validation of work efforts) in each of the company factories (branches) around the country.
2. Each of these factories produces the same products.
3. Anyway, the production manager would like to know the manufacturing cost of each product per each of the factories. In other words she would like to see whether a product costs more to be manufactured in a factory than in another one.
4. Besides, she would like this product cost calculation to be triggered automatically on each product manufacturing, without having to manually run a process.

Accounting manager:

1. Sarah, the accounting manager of a manufacturing company, is preparing the financial reports for each of the branches (factories or organizations) of the company.
2. One of the accounting concepts to be accounted is the materials cost.
3. She wants the cost of each material to be properly split and accounted in its corresponding branch (factory or organization).
4. Once a material cost has been accounted, she expects this material cost not to be changed. In other words, the material cost already accounted:
   1. is un-accounted if it will be recalculated.
   2. can not be re-calculated if the period where it was accounted is closed.

Functional Requirements Based on Business Processes

Costing definition

Num	Requirement	Importance	Status
1.1	Costing method is defined in the Product window (Master Data Management >> Product) selecting a value of the non-mandatory Cost Type closed drop-down list: Global Average (former Average value, Global means by Client) Global Standard (former Standard value, Global means by Client) Organization Average (new value) None of these values is defaulted (since this drop-down list is not mandatory).	Must have	Complete
1.2	Changes in Cost Type method have be to checked and validated before saving: In order not to mess up the accounting of costs of existing Openbravo ERP live implementations, before performing the change from Global Average or Global Standard values to Organization Average value: If the existing material costs have been accounted: If all these material costs accounting entries belong to a closed period, allow the value change. If any of these material costs accounting entries belong to an open period: Set the cost lines belonging to a closed period as permanent, launch the regeneration of costs, and finally allow the value change. Before performing the change from Organization Average value to Global Average or Global Standard values: If the existing material costs have been accounted: If all these material costs accounting entries belong to a closed period, allow the value change. If any of these material costs accounting entries belong to an open period,[TBD] Before performing the change from Global Average value to Global Standard value and vice-versa:[TBD]	Must have

Costing calculation

Num	Requirement	Importance	Status
2.1	Costing calculation of received materials can be launched manually at any time via the Generate Average Costs process (Warehouse Management >> Transactions >> Generate Average Costs). This process: Removes all the non-manual, non-production and non-permanent records with Costing Type equals to Global Average or Organization Average of the Costing tab for the current client. Selects all the goods receipt lines of this client, except the goods receipt lines remaining in the Costing tab (permanent ones). Calculates the average cost of the materials with Costing Type equals to Global Average or Organization Average based on the data of the receipt lines selected using as material quantity the goods receipt line quantity and using as material price: the price actual of the matching invoice line, if it exists (see Procurement Management >> Transactions >> Matched Invoices) otherwise the price actual of the matching order line, if it exists (Procurement Management >> Transactions >> Matched Purchase Orders) otherwise the standard price of the purchase price list of the business partner (supplier/vendor) of the goods receipt, if it exists (see Master Data Management >> Business Partner >> Vendor/Creditor) otherwise zero. Based on the product costing method, this calculation: Organization Average: resets the average cost calculation for each organization (permits differentiation of material costs per organization). Global Average: does not reset the average cost calculation per organization and the material cost applies to the whole client (no differentiation of material costs per organization).	Must have
2.2	The material cost calculation is automatically triggered when completing goods receipts if the costing method of a product appearing in the receipt lines is Organization Average.	Must have

Num	Requirement	Importance	Status
2.3	Costing calculation of manufactured products can be launched manually at any time via the Create Production Costs process (Production Management >> Transactions >> Create Production Costs). This process: Removes all the non-manual, production and non-permanent records with Costing Type equals to Global Average or Organization Average of the Costing tab for the current client. Selects all the production lines of the existing production plans. Calculates the average cost of the products with Costing Type equals to Global Average or Organization Average based on the data of the production lines selected using as product quantity the production line quantity and using as product price the sum of costs involved in the manufacturing of this product (machines, employees, etc...) Based on the product costing method, this calculation: Organization Average: resets the average cost calculation for each organization (permits differentiation of product costs per organization). Global Average: does not reset the average cost calculation per organization and the product cost applies to the whole client (no differentiation of product costs per organization). Based on the product costing method, the records created in the Costing tab: Organization Average: are set as permanent (Permanent check-box ticked). Global Average: are NOT set as permanent (Permanent check-box un-ticked) which is the actual behaviour.	Must have
2.4	The product cost calculation is automatically triggered when completing products manufacturing (validating work efforts) if the costing method of a product appearing in the production lines is Organization Average or Global Average (current behaviour).	Must have

Costing accounting

Num	Requirement	Importance	Status
3.1	If a material cost is accounted in an open period, it can not be re-calculated nor modified: Permanent check-box (flag) value is set to yes for these records in the Product > Costing tab so they do not get re-calculated. These records in the Product > Costing tab can not be modified.	Must have
3.2	When closing a period, the material costs accounted during this period can not be re-calculated nor modified: Permanent check-box (flag) value is set to yes for these records in the Product > Costing tab so they do not get re-calculated. These records in the Product > Costing tab can not be modified.	Must have

Costing reporting

Num	Requirement	Importance	Status
4.1	Warehousing reports need to be adapted to display the costing per organization: Warehouse Management >> Analysis Tools >> Valued Stock Report Warehouse Management >> Analysis Tools >> Pareto Product Report	Must have
4.2	Production reports need to be adapted to display the costing per organization: Production Management >> Analysis Tools >> Production Cost Report	Must have
4.3	Accounting reports need to be adapted to display the costing per organization: [TBD]	Must have

User Interface Mockups

[TBD] Mockups of reports with organization drop-down list

Open discussion items

1. RRO: point 1.2:
   1. Are the checks proposed correct?
   2. Should we include a check for the transition from Global Average to Global Standard value? DME: I thinks so. Eduardo will also add definition of actions that will be triggered by this change.
   3. Which messages should be displayed for each check?
2. RRO: points 2.3 and 2.4:
   1. To be reviewed by GDA.
   2. Is there a check of the product costing method missing in the current procedures?
3. RRO: points 3.1 and 3.2: since 3.1 is more restrictive than 3.2, is 3.1 enough? DME: For me 3.1 is enough.
4. RRO: points 4.1, 4.2 and 4.3:
   1. Is any report missing?
   2. Which is the proposed solution for them? Organization drop-down (mandatory?) in the filter window? DME: The solution was to go just with current reports assuming most common case of one organization one warehouse.
5. RRO: Costing tab:
   1. Why is Production check-box editable? Can we make a Manual Costing line of type production?
   2. Should Manual check-box to be read-only and defaulted to Yes? Then the processes that insert data into Costing should set its value to No. DME: I think this is a good point.
6. RRO: Naming convention? We are having some processes related to cost calculation and they all differ in the action name:
   1. Generate Average Costs
   2. Create Production Costs
   3. Calculate Standard Costs
7. DME: IMHO migration from one costing method to another product by product (as this attribute is defined at the product level) could be a bit annoying for those having big products catalogue. I propose to have a script which could have Product category / All as an input parameter.

Closed discussion items

Technical Documentation

Costing definition

Functional requirement 1.1: Cost Type drop-down list values.

Modify Cost type reference list with values:

1. AVO (Organization Average)
2. AV (Global Average)
3. ST (Global Standard)

Functional requirement 1.2: Cost Type checks on product saving.

Modify M_PRODUCT_TRG trigger to make the checks proposed in Functional requirement 1.2.

Costing calculation

Functional requirement 2.1: Generate Average Costs process.

Modify M_GENERATE_AVERAGE_COSTS procedure in order to:

1. If costing method is Organization Average reset the average cost calculation for each organization.
2. If costing method is Organization Average the records created in the Costing tab are set as permanent.

Functional requirement 2.2: Generate Average Costs triggering when completing goods receipt.

M_INOUT_POST procedure

Functional requirement 2.3: Create Production Costs process.

MA_PRODUCTION_COST_GENERATE procedure

MA_PRODUCTION_COST procedure

Functional requirement 2.4: Create Production Costs triggering when validating work efforts.

MA_WORKEFFORT_VALIDATE procedure

Costing accounting

[TBD]

Costing reporting

Functional requirements 4.1, 4.2 and 4.3: Cost retrieving per organization.

Modify M_GET_PRODUCT_COST function in order to:

1. Accept Organization parameter.
2. Filter data based on organization if costing method is Organization Average.
3. When a product is both produced and purchased prioritize the production cost over the purchase cost.

Modify every occurrence of M_GET_PRODUCT_COST function in the following XSQL, JRXML and function files in order to pass the proper organization parameter:

1. erpCommon/ad_forms/DocMatchInv_data.xsql (1 occurrence)
2. erpCommon/ad_forms/ProductInfo_data.xsql (1 occurrence)
3. erpCommon/ad_reports/ReportInvoiceCustomerDimensionalAnalyses_data.xsql (3 occurrences)
4. erpCommon/ad_reports/ReportInvoiceCustomerDimensionalAnalysesJR_data.xsql (3 occurrences)
5. erpCommon/ad_reports/ReportInvoiceCustomerDimensionalAnalysesComparativeJR.jrxml (2 occurrences)
6. erpCommon/ad_reports/ReportInvoiceCustomerDimensionalAnalysesNoComparativeJR.jrxml (1 occurrence)
7. erpCommon/ad_reports/ReportValuationStock_data.xsql (2 occurrences)
8. src-db/database/model/functions/M_GET_PARETO_ABC.xml (4 occurrences)
9. src-db/database/model/functions/M_UPDATE_PARETO_PRODUCT.xml (4 occurrences)

Development Status

Costing definition

Type of file	Path	Change made	Comments	Status
Database file	src-db/database/sourcedata/AD_REF_LIST.xml	Modified Cost Type reference list. Changed names of: Average to Global Average with description Average costing per Client. Standard to Global Standard with description Standard costing per Client. Added record Organization Average with description Average costing per Organization. and value AVO.	Resolves Functional requirement 1.1	Complete

Costing calculation

Type of file	Path	Change made	Comments	Status
M_GENERATE_AVERAGE_COSTS	src-db/database/model/functions/M_GENERATE_AVERAGE_COSTS.xml	Multi-organization support enhancement	Add capability for new cost type "Organizational average"	On going
MA_PRODUCTION_COST_GENERATE	src-db/database/model/functions/MA_PRODUCTION_COST_GENERATE.xml	Multi-organization support enhancement	Add capability for new cost type "Organizational average"	Complete
MA_PRODUCTION_COST	src-db/database/model/functions/MA_PRODUCTION_COST.xml	Multi-organization support enhancement	Add capability for new cost type "Organizational average"	Complete

Costing accounting

Type of file	Path	Change made	Comments	Status

Costing reporting

Type of file	Path	Change made	Comments	Status

Test cases

Costing definition

Costing calculation

Costing accounting

Costing reporting

Category: Openbravo ERP 2.50