details: https://code.openbravo.com/erp/devel/pi/rev/7cbdcc419167
changeset: 30046:7cbdcc419167
user: Rafael Queralta Pozo <rqueralta <at> nauta.cu>
date: Wed Sep 07 12:06:09 2016 -0400
summary: Fixes issue 33742: UpdateCustomerBalance modulescript BP currency
conversion
UpdateCurtomerBalance modulescript did not take in account the business partner
currency.Now if business partner currency and document currency are different,
the document amount is converted to the business partner currency.
diffstat:
modules/org.openbravo.advpaymentmngt/build/classes/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalanceData.class
| 0
modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalance_data.xsql
| 33 ++++++---
modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalanceData.java
| 31 +++++---
3 files changed, 39 insertions(+), 25 deletions(-)
diffs (93 lines):
diff -r b4a8ad7a445f -r 7cbdcc419167
modules/org.openbravo.advpaymentmngt/build/classes/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalanceData.class
Binary file
modules/org.openbravo.advpaymentmngt/build/classes/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalanceData.class
has changed
diff -r b4a8ad7a445f -r 7cbdcc419167
modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalance_data.xsql
---
a/modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalance_data.xsql
Thu Sep 08 13:20:39 2016 +0200
+++
b/modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalance_data.xsql
Wed Sep 07 12:06:09 2016 -0400
@@ -1,7 +1,7 @@
<?xml version="1.0" encoding="UTF-8" ?>
<!--
************************************************************************************
- * Copyright (C) 2011-2015 Openbravo S.L.U.
+ * Copyright (C) 2011-2016 Openbravo S.L.U.
* Licensed under the Openbravo Commercial License version 1.0
* You may obtain a copy of the License at
http://www.openbravo.com/legal/obcl.html
* or in the legal folder of this module distribution.
@@ -59,18 +59,25 @@
<Sql>
<![CDATA[
SELECT A.c_bpartner_id, SUM(A.amount) as customercredit
- FROM (SELECT c_bpartner_id, COALESCE(SUM(ps.outstandingamt * (CASE
WHEN inv.issotrx = 'Y' THEN 1 ELSE -1 END)), 0) as amount
- FROM fin_payment_schedule ps join c_invoice inv on
(ps.c_invoice_id = inv.c_invoice_id)
- WHERE ps.outstandingamt <> 0
- GROUP BY c_bpartner_id
- UNION ALL
- SELECT p.c_bpartner_id, COALESCE(SUM((p.generated_credit -
p.used_credit) * (CASE WHEN p.isreceipt = 'Y' THEN -1 ELSE 1 END)), 0) as amount
- FROM FIN_PAYMENT p
- WHERE p.c_bpartner_id is not null
- AND (p.generated_credit - p.used_credit) <> 0
- AND p.generated_credit <> 0
- AND p.processed = 'Y'
- GROUP BY p.c_bpartner_id) A
+ 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
+ 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'
+ GROUP BY bp.c_bpartner_id
+ ) A
GROUP BY A.c_bpartner_id
]]>
</Sql>
diff -r b4a8ad7a445f -r 7cbdcc419167
modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalanceData.java
---
a/modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalanceData.java
Thu Sep 08 13:20:39 2016 +0200
+++
b/modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalanceData.java
Wed Sep 07 12:06:09 2016 -0400
@@ -226,18 +226,25 @@
String strSql = "";
strSql = strSql +
" SELECT A.c_bpartner_id, SUM(A.amount) as customercredit" +
- " FROM (SELECT c_bpartner_id, COALESCE(SUM(ps.outstandingamt *
(CASE WHEN inv.issotrx = 'Y' THEN 1 ELSE -1 END)), 0) as amount" +
- " FROM fin_payment_schedule ps join c_invoice inv on
(ps.c_invoice_id = inv.c_invoice_id)" +
- " WHERE ps.outstandingamt <> 0" +
- " GROUP BY c_bpartner_id" +
- " UNION ALL" +
- " SELECT p.c_bpartner_id, COALESCE(SUM((p.generated_credit
- p.used_credit) * (CASE WHEN p.isreceipt = 'Y' THEN -1 ELSE 1 END)), 0) as
amount" +
- " FROM FIN_PAYMENT p" +
- " WHERE p.c_bpartner_id is not null" +
- " AND (p.generated_credit - p.used_credit) <> 0" +
- " AND p.generated_credit <> 0" +
- " AND p.processed = 'Y'" +
- " GROUP BY p.c_bpartner_id) A" +
+ " 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" +
+ " 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'" +
+ " GROUP BY bp.c_bpartner_id" +
+ " ) A" +
" GROUP BY A.c_bpartner_id";
ResultSet result;
------------------------------------------------------------------------------
_______________________________________________
Openbravo-commits mailing list
Openbravo-commits@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
