Fixes issue 33742: UpdateCustomerBalance modulescript BP currency conversion
authorRafael Queralta Pozo <rqueralta@nauta.cu>
Wed, 07 Sep 2016 12:06:09 -0400
changeset 30046 7cbdcc419167
parent 30045 b4a8ad7a445f
child 30047 0fe55503791b
child 30048 bdb3e747cf46
child 30271 066303859ed9
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.
modules/org.openbravo.advpaymentmngt/build/classes/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalanceData.class
modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalance_data.xsql
modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalanceData.java
Binary file modules/org.openbravo.advpaymentmngt/build/classes/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalanceData.class has changed
--- 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>
--- 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;