Fixes issue 28979: UpdateCustomerBalance is wrongly calculating CurrentBalance
authorAlvaro Ferraz <alvaro.ferraz@openbravo.com>
Wed, 25 Feb 2015 09:53:23 +0100
changeset 26074 92405876894e
parent 26073 9b61d12d4904
child 26075 0b5b6b950341
Fixes issue 28979: UpdateCustomerBalance is wrongly calculating CurrentBalance

UpdateCustomerBalance modulescript has been modified in order to get credit for receipts as negative and credit for payments as positive. Also, it now takes into account partial payments.
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	Wed Feb 25 01:00:06 2015 +0100
+++ b/modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalance_data.xsql	Wed Feb 25 09:53:23 2015 +0100
@@ -1,7 +1,7 @@
 <?xml version="1.0" encoding="UTF-8" ?>
 <!--
  ************************************************************************************
- * Copyright (C) 2011-2014 Openbravo S.L.U.
+ * Copyright (C) 2011-2015 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.
@@ -23,7 +23,7 @@
       <![CDATA[
         SELECT count(*) as existpreference
         FROM ad_preference
-        WHERE attribute = 'IsCustomerBalanceRestored'        
+        WHERE attribute = 'IsCustomerBalanceRestoredV2'        
       ]]>
     </Sql>
   </SqlMethod>
@@ -33,7 +33,7 @@
       <![CDATA[
         SELECT count(*) as existpreference
         FROM ad_preference
-        WHERE attribute = 'IsCustomerBalanceRestored' AND to_char(value)='Y'        
+        WHERE attribute = 'IsCustomerBalanceRestoredV2' AND to_char(value)='Y'        
       ]]>
     </Sql>
   </SqlMethod>
@@ -42,7 +42,7 @@
     <Sql>
       <![CDATA[
         DELETE FROM ad_preference
-        WHERE attribute = 'IsCustomerBalanceRestored' AND to_char(value)='Y'        
+        WHERE attribute = 'IsCustomerBalanceRestoredV2' AND to_char(value)='Y'        
       ]]>
     </Sql>
   </SqlMethod>
@@ -59,12 +59,12 @@
     <Sql>
       <![CDATA[
         SELECT A.c_bpartner_id, SUM(A.amount) as customercredit
-        FROM (SELECT c_bpartner_id, COALESCE(SUM(ps.amount * (CASE WHEN inv.issotrx = 'Y' THEN 1 ELSE -1 END)), 0) as amount
+        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
+              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
@@ -96,7 +96,7 @@
         ) VALUES (
           get_uuid(), '0', '0', 'Y',
           '0', NOW(), '0', NOW(),
-          'IsCustomerBalanceRestored'
+          'IsCustomerBalanceRestoredV2'
         )
       ]]>
     </Sql>
--- a/modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalanceData.java	Wed Feb 25 01:00:06 2015 +0100
+++ b/modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalanceData.java	Wed Feb 25 09:53:23 2015 +0100
@@ -96,7 +96,7 @@
     strSql = strSql + 
       "        SELECT count(*) as existpreference" +
       "        FROM ad_preference" +
-      "        WHERE attribute = 'IsCustomerBalanceRestored'        ";
+      "        WHERE attribute = 'IsCustomerBalanceRestoredV2'        ";
 
     ResultSet result;
     boolean boolReturn = false;
@@ -131,7 +131,7 @@
     strSql = strSql + 
       "        SELECT count(*) as existpreference" +
       "        FROM ad_preference" +
-      "        WHERE attribute = 'IsCustomerBalanceRestored' AND to_char(value)='Y'        ";
+      "        WHERE attribute = 'IsCustomerBalanceRestoredV2' AND to_char(value)='Y'        ";
 
     ResultSet result;
     boolean boolReturn = false;
@@ -165,7 +165,7 @@
     String strSql = "";
     strSql = strSql + 
       "        DELETE FROM ad_preference" +
-      "        WHERE attribute = 'IsCustomerBalanceRestored' AND to_char(value)='Y'        ";
+      "        WHERE attribute = 'IsCustomerBalanceRestoredV2' AND to_char(value)='Y'        ";
 
     int updateCount = 0;
     PreparedStatement st = null;
@@ -226,12 +226,12 @@
     String strSql = "";
     strSql = strSql + 
       "        SELECT A.c_bpartner_id, SUM(A.amount) as customercredit" +
-      "        FROM (SELECT c_bpartner_id, COALESCE(SUM(ps.amount * (CASE WHEN inv.issotrx = 'Y' THEN 1 ELSE -1 END)), 0) as amount" +
+      "        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" +
+      "              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" +
@@ -326,7 +326,7 @@
       "        ) VALUES (" +
       "          get_uuid(), '0', '0', 'Y'," +
       "          '0', NOW(), '0', NOW()," +
-      "          'IsCustomerBalanceRestored'" +
+      "          'IsCustomerBalanceRestoredV2'" +
       "        )";
 
     int updateCount = 0;