Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0012698Openbravo ERPZ. Otherspublic2010-03-16 12:592010-12-09 15:59
Reportermirurita 
Assigned Tomirurita 
PrioritynormalSeverityminorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS20OS VersionCommunity Appliance
Product Version2.50 
Target Version2.50Fixed in Version2.50 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesAdvanced Payables and Receivables Mngmt
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0012698: Updating payment monitor fails in FIN_GEN_PAYMENTSCHEDULE_INV extension point
DescriptionAt the end of the extension point the payment monitor is updated.
The problem appears when the queries return a NULL value and then we try to set to the a not nullable columns (TOTALPAID, OUTSTANDINGAMT, DUEAMT) of the C_INVOICE table a NULL value.

-- reading Data from FIN_PAYMENT_SCHEDULE
SELECT sum(PAIDAMT) as PaidAmount, SUM(OUTSTANDINGAMT) as OutstandingAmount INTO v_PaidAmount,V_OutstandingAmount
FROM FIN_PAYMENT_SCHEDULE
WHERE C_INVOICE_ID=P_RECORD_ID
GROUP BY c_invoice_id;

-- getting DueAmount from FIN_PAYMENT_SCHEDULE for the Invoice
SELECT SUM(OUTSTANDINGAMT) as DueAmount INTO V_DueAmount
FROM FIN_PAYMENT_SCHEDULE
WHERE C_INVOICE_ID=P_RECORD_ID AND duedate<=TO_DATE(NOW())
GROUP BY c_invoice_id;

-- updating Payment Monitor values into C_INVOICE
UPDATE C_INVOICE
SET TOTALPAID = v_PaidAmount, --ERROR IF NULL
    OUTSTANDINGAMT = V_OutstandingAmount, --ERROR IF NULL
    DUEAMT = V_DueAmount --ERROR IF NULL
WHERE C_INVOICE_ID = P_RECORD_ID;


https://code.openbravo.com/erp/devel/pi-engdev/rev/d7d77cdf4e4e [^]
Steps To Reproduce1) Create an invoice
2) Create a line
3) Complete the invoice
Proposed SolutionAdd a COALESCE to avoid NULL values.

-- updating Payment Monitor values into C_INVOICE
UPDATE C_INVOICE
SET TOTALPAID = COALESCE(v_PaidAmount, 0),
    OUTSTANDINGAMT = COALESCE(V_OutstandingAmount, 0),
    DUEAMT = COALESCE(V_DueAmount, 0)
WHERE C_INVOICE_ID = P_RECORD_ID;
Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2010-03-16 12:59miruritaNew Issue
2010-03-16 12:59miruritaAssigned To => mirurita
2010-03-16 12:59miruritaStatusnew => scheduled
2010-03-16 12:59miruritafix_in_branch => pi
2010-03-16 17:19miruritaNote Added: 0025543
2010-03-16 17:19miruritaStatusscheduled => resolved
2010-03-16 17:19miruritaResolutionopen => fixed
2010-03-31 18:11hgbotCheckin
2010-03-31 18:11hgbotNote Added: 0025844
2010-03-31 18:11hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/2f0fe8d95d095dd9736767568ff575b2f3aedb4e [^]
2010-04-19 21:11hudsonbotCheckin
2010-04-19 21:11hudsonbotNote Added: 0026405
2010-04-28 12:59sureshbabuNote Added: 0026762
2010-04-28 12:59sureshbabuStatusresolved => closed
2010-04-28 12:59sureshbabuFixed in Version => 2.50
2010-12-09 15:59anonymoussf_bug_id0 => 3133056

Notes
(0025543)
mirurita   
2010-03-16 17:19   
Fixed on pi-engdev revision 6754:2f0fe8d95d09

https://code.openbravo.com/erp/devel/pi-engdev/rev/2f0fe8d95d09 [^]
(0025844)
hgbot   
2010-03-31 18:11   
Repository: erp/devel/pi
Changeset: 2f0fe8d95d095dd9736767568ff575b2f3aedb4e
Author: Mikel Irurita <mikel.irurita <at> openbravo.com>
Date: Tue Mar 16 16:00:15 2010 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/2f0fe8d95d095dd9736767568ff575b2f3aedb4e [^]

Fixes issue 0012698: Updating payment monitor fails in FIN_GEN_PAYMENTSCHEDULE_INV extension point

---
M src-db/database/model/functions/FIN_GEN_PAYMENTSCHEDULE_INV.xml
---
(0026405)
hudsonbot   
2010-04-19 21:11   
A changeset related to this issue has been promoted to main after passing a series of tests and an OBX has been generated:

Changeset: http://code.openbravo.com/erp/devel/main/rev/2f0fe8d95d09 [^]
Merge Changeset: http://code.openbravo.com/erp/devel/main/rev/91d98bda46c1 [^]
Tests: http://builds.openbravo.com/view/devel-int/ [^]
OBX: http://builds.openbravo.com/erp/core/obx/OpenbravoERP-2.50CI.17088.obx [^]
(0026762)
sureshbabu   
2010-04-28 12:59   
Tested for the below scenarios

a) Due date = System date
b) Due date < System date
d) Due date > System date

working fine