0038273: Current Balance is not updated if an invoice is completed when Business Partner record is pending to be saved

Openbravo Issue Tracking System - Openbravo ERP
View Issue Details

ID	Project	Category	View Status	Date Submitted	Last Update
0038273	Openbravo ERP	02. Master data management	public	2018-04-05 09:19	2018-05-09 19:29

Reporter	ngarcia
Assigned To	collazoandy4
Priority	urgent	Severity	major	Reproducibility	always
Status	closed	Resolution	fixed
Platform		OS	5	OS Version
Product Version
Target Version		Fixed in Version	3.0PR18Q3
Merge Request Status
Review Assigned To	aferraz
OBNetwork customer	OBPS
Web browser
Modules	Core
Support ticket	1145
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency Pack	No

Summary	0038273: Current Balance is not updated if an invoice is completed when Business Partner record is pending to be saved
Description	Current Balance is not updated if an invoice is completed when Business Partner record is pending to be saved
Steps To Reproduce	As group admin role: Go to Business Partner window and select 'Hoteles Buenas Noches, S.A.' Check its current balance Edit some field such as the Commercial Name Do not save it Create a Sales Invoice for that business partner, add a line and complete it Return to the Business Partner window and save the record Check Current Balance remains with the same value and it should have been updated
Proposed Solution
Additional Information
Tags	No tags attached.
Relationships
Attached Files	proposal38273.diff (3,253) 2018-04-06 08:27 https://issues.openbravo.com/file_download.php?file_id=11712&type=bug

Issue History
Date Modified	Username	Field	Change
2018-04-05 09:19	ngarcia	New Issue
2018-04-05 09:19	ngarcia	Assigned To	=> Triage Finance
2018-04-05 09:19	ngarcia	OBNetwork customer	=> Yes
2018-04-05 09:19	ngarcia	Modules	=> Core
2018-04-05 09:19	ngarcia	Support ticket	=> 1145
2018-04-05 09:19	ngarcia	Resolution time	=> 1524693600
2018-04-05 09:19	ngarcia	Triggers an Emergency Pack	=> No
2018-04-05 09:20	ngarcia	Issue Monitored: ngarcia
2018-04-05 15:36	jfrances	Issue Monitored: jfrances
2018-04-05 18:02	dmiguelez	Assigned To	Triage Finance => collazoandy4
2018-04-05 23:19	achaves	Issue Monitored: achaves
2018-04-06 08:27	dmiguelez	File Added: proposal38273.diff
2018-04-06 08:29	dmiguelez	Note Added: 0103705
2018-04-06 20:28	collazoandy4	Status	new => scheduled
2018-04-06 21:45	collazoandy4	Note Added: 0103727
2018-04-12 17:15	hgbot	Checkin
2018-04-12 17:15	hgbot	Note Added: 0103855
2018-04-12 17:15	hgbot	Status	scheduled => resolved
2018-04-12 17:15	hgbot	Resolution	open => fixed
2018-04-12 17:15	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/devel/pi/rev/86538f0c012469375e97d74760b7f3b265715ef7 [^]
2018-04-12 17:15	hgbot	Checkin
2018-04-12 17:15	hgbot	Note Added: 0103856
2018-04-12 17:15	aferraz	Review Assigned To	=> aferraz
2018-04-12 17:15	aferraz	Note Added: 0103857
2018-04-12 17:15	aferraz	Status	resolved => closed
2018-04-12 17:15	aferraz	Fixed in Version	=> 3.0PR18Q3
2018-05-09 19:29	hudsonbot	Checkin
2018-05-09 19:29	hudsonbot	Note Added: 0104378
2018-05-09 19:29	hudsonbot	Checkin
2018-05-09 19:29	hudsonbot	Note Added: 0104379

Notes

Note:

To fix wrong data UpdateCustomerBalance ModuleScript can be used.

To fix it only for one Business Partner this queries can be used (this is done for the Business Partner used to report the issue)

-- Update CreditUsed to 0
UPDATE c_bpartner SET so_creditused = 0, updatedby='0', updated=now() where c_bpartner_id = '9E6850C866BD4921AD0EB7F7796CE2C7';

-- Retrieve credit used
select SUM(A.amount)
        FROM (
          SELECT bp.c_bpartner_id, COALESCE(SUM(c_currency_convert(ps.outstandingamt * (CASE WHEN inv.issotrx = 'Y' THEN 1 ELSE -1 END), inv.c_currency_id, bp.bp_currency_id, inv.created, null, inv.ad_client_id, inv.ad_org_id)), 0) as amount
          FROM c_invoice inv
          JOIN c_bpartner bp
          ON inv.c_bpartner_id = bp.c_bpartner_id
          JOIN fin_payment_schedule ps
          ON inv.c_invoice_id = ps.c_invoice_id
          WHERE ps.outstandingamt <> 0
          and bp.c_bpartner_id = '9E6850C866BD4921AD0EB7F7796CE2C7'
          GROUP BY bp.c_bpartner_id
          UNION ALL
          SELECT bp.c_bpartner_id, COALESCE(SUM(c_currency_convert((p.generated_credit - p.used_credit) * (CASE WHEN p.isreceipt = 'Y' THEN -1 ELSE 1 END), p.c_currency_id, bp.bp_currency_id, p.created, null, p.ad_client_id, p.ad_org_id)), 0) as amount
          FROM FIN_PAYMENT p
          JOIN c_bpartner bp
          ON p.c_bpartner_id = bp.c_bpartner_id
          WHERE (p.generated_credit - p.used_credit) <> 0
          AND p.generated_credit <> 0
          AND p.processed = 'Y'
          and bp.c_bpartner_id = '9E6850C866BD4921AD0EB7F7796CE2C7'
          GROUP BY bp.c_bpartner_id
        ) A
        GROUP BY A.c_bpartner_id;

-- Set new credit used based on previous query
UPDATE c_bpartner SET so_creditused = TO_NUMBER(XXX), updatedby='0', updated=now() WHERE c_bpartner_id = '9E6850C866BD4921AD0EB7F7796CE2C7';