0036675: [Costing Migration Process]: Transactions related with closing inventory lines do not have an entry in Transaction Cost tab - Openbravo Issue Tracking System

Openbravo Issue Tracking System - Openbravo ERP

View Issue Details

ID

Project

Category

View Status

Date Submitted

Last Update

0036675

Openbravo ERP

04. Warehouse management

public

2017-08-21 11:33

2017-09-21 16:49

Reporter

ngarcia

Assigned To

markmm82

Priority

urgent

Severity

major

Reproducibility

always

Status

closed

Resolution

fixed

Platform

OS

5

OS Version

Product Version

Target Version

Fixed in Version

3.0PR17Q4

Merge Request Status

Review Assigned To

aferraz

OBNetwork customer

Web browser

Modules

Core

Support ticket

Regression level

Packaging and release

Regression date

2017-05-25

Regression introduced in release

main

Regression introduced by commit

https://code.openbravo.com/erp/devel/pi/rev/0bfbdaa123e3 [^]

Triggers an Emergency Pack

No

Summary

0036675: [Costing Migration Process]: Transactions related with closing inventory lines do not have an entry in Transaction Cost tab

Description

[Costing Migration Process]: Transactions related with closing inventory lines do not have an entry in Transaction Cost tab (M_TRANSACTION_COST)

The call to insertTrxCosts() function after cost is assigned to closing inventory lines' transactions is getting 0 in countTrx, obtained by calling

CostingUtilsData.countTrxCosts(conn)

It seems it is using different connection and the transactions are not available yet

Steps To Reproduce

Having a version previous to 3.0MP13 with products with stock, upgrade it to the latest version

As group admin role (for each client):
   Create current period and years (Fiscal Calendar)
   Open the current period (Open/Close Period Control)

As system admin role:
   Check two entries (Standard and Average) exist in Costing Algorithm window, create them if not
   Launch the Costing Migration Process
   If it finishes with error, remove the alert of products without cost
   Launch the process again

   Once it finishes, check the transactions related with closing inventory lines do have Transaction Cost defined in Transactions tab but not a related entry in Transaction Cost tab

Proposed Solution

Additional Information

Tags

No tags attached.

Relationships

related to	defect	0036659		closed	markmm82	[Costing Migration Process]: Closing inventories are not leaving stock valuation to 0
related to	defect	0034130		closed	aferraz	Costing Migration Process takes too long with big amount of records in m_transaction table
related to	design defect	0035384		closed	markmm82	Add logs on debug on the costing process and costing migration process to track the time invested on each part
related to	defect	0036534		closed	collazoandy4	Error in Costing Background Process if starting date of first costing rule is lower than movement date of existing transactions
depends on	backport	0036692	3.0PR17Q3	closed	markmm82	[Costing Migration Process]: Transactions related with closing inventory lines do not have an entry in Transaction Cost tab
caused by	defect	0035959		closed	markmm82	Performance problem in Costing Migration Process with high volume of records in m_transaction table

Attached Files

diff

TestCosting_v1.diff (719,290) 2017-08-24 09:39
https://issues.openbravo.com/file_download.php?file_id=11006&type=bug
diff

diff

TestCosting_v2.diff (720,249) 2017-08-24 09:39
https://issues.openbravo.com/file_download.php?file_id=11007&type=bug

Issue History

Date Modified

Username

Field

Change

2017-08-21 11:33

New Issue

2017-08-21 11:33

Assigned To

=> Triage Finance

2017-08-21 11:33

Modules

=> Core

2017-08-21 11:33

Resolution time

=> 1505080800

2017-08-21 11:33

Triggers an Emergency Pack

=> No

2017-08-21 11:33

Issue Monitored: networkb

2017-08-21 11:40

Relationship added

related to 0036659

2017-08-21 12:02

Assigned To

Triage Finance => markmm82

2017-08-21 16:32

Status

new => scheduled

2017-08-22 13:18

Relationship added

caused by 0035959

2017-08-22 13:20

Regression level

=> Packaging and release

2017-08-22 13:20

Regression date

=> 2017-05-25

2017-08-22 13:20

Regression introduced in release

=> main

2017-08-22 13:20

Regression introduced by commit

=> https://code.openbravo.com/erp/devel/pi/rev/0bfbdaa123e3 [^]

2017-08-22 13:20

Status

scheduled => feedback

2017-08-22 13:20

Status

feedback => scheduled

2017-08-23 15:06

Relationship added

related to 0034130

2017-08-24 09:39

File Added: TestCosting_v1.diff

2017-08-24 09:39

File Added: TestCosting_v2.diff

2017-08-24 10:52

Relationship added

related to 0035384

2017-08-28 11:19

Relationship added

related to 0036534

2017-08-30 10:13

Checkin

2017-08-30 10:13

Note Added: 0098721

2017-08-30 10:13

Status

scheduled => resolved

2017-08-30 10:13

Resolution

open => fixed

2017-08-30 10:13

Fixed in SCM revision

=> http://code.openbravo.com/erp/devel/pi/rev/5cd05d20d9cfdd4f380f7de4a2f9f4c24f554845 [^]

2017-08-30 10:13

Checkin

2017-08-30 10:13

Note Added: 0098722

2017-08-30 10:14

Review Assigned To

=> aferraz

2017-08-30 10:14

Note Added: 0098723

2017-08-30 10:14

Status

resolved => closed

2017-08-30 10:14

Fixed in Version

=> 3.0PR17Q4

2017-09-21 16:49

Checkin

2017-09-21 16:49

Note Added: 0099343

2017-09-21 16:49

Checkin

2017-09-21 16:49

Note Added: 0099345

Notes

Repository: erp/devel/pi
Changeset: 5cd05d20d9cfdd4f380f7de4a2f9f4c24f554845
Author: Mark <markmm82 <at> gmail.com>
Date: Wed Aug 23 14:16:09 2017 -0400
URL: http://code.openbravo.com/erp/devel/pi/rev/5cd05d20d9cfdd4f380f7de4a2f9f4c24f554845 [^]

Fixes issue 36675: Transaction Cost not created for closing inventory lines

After cost was assigned to closing inventory lines transactions,
when calling to insertTrxCosts() from calculateCosts() method (second call),
CostingUtilsData.countTrxCosts(conn) was getting 0 in countTrx.
It was caused because closing inventory lines cost calculation
was done by OBDal connection, and methods invoked in CostingUtilsData are done
using different connection provided by SQLC, and the transactions are not available
at this moment. See: http://wiki.openbravo.com/wiki/Data_Access_Layer#Transaction_and_Session [^]

To find the best solution for this issue and don't affect the performance (see issue 35959),
different solutions were tested to always use DAL and make process use the same connection.

1.- Doing a single insert-select query with limits is not supported in DAL. We tried it in two different ways (see attached TestCosting_v1.diff):
- Use setMaxResults(). It only works for selects but not for inserts/updates.
- Use setMaxResults() in select query and append it to insert query using getQueryString(). It appends the select without any limit.

2.- Another option could be to refactor insertTrxCosts() method to do a multiple insert query:
We can create the select query with limits using setMaxResults(), iterate it using an scroll
and create and save in each iteration a new TransactionCost. Flush won't be done in every iteration.
When flush is done, it raises every insert in multiple single-row inserts instead of only one multiple-row insert.
Single-row inserts performs worst than multiple-row insert, at least with not many rows (see attached TestCosting_v2.diff).

3.- Finally, we have refactor the process to avoid insertTrxCosts() method:
Our solution makes an insertion in M_Transaction_Cost table each
time we make the updation of related transaction cost in M_Transaction table.
We will do single-row inserts in two methods: updateTrxLegacyCosts() and calculateCosts(),
and multiple-row insert in one method: updateWithZeroCostRemainingTrx().
Thus, we split the number of TransactionCost records to be created in three different steps.

---
M src/org/openbravo/costing/CostingMigrationProcess.java
M src/org/openbravo/costing/CostingUtils_data.xsql
---