details: /erp/devel/pi/rev/1f414265d30b
changeset: 11986:1f414265d30b
user: Mikel Irurita <mikel.irurita <at> openbravo.com>
date: Thu May 05 12:21:12 2011 +0200
summary: Merge changeset 818:1dfc14a06c86 of 2.50 APRM
details: /erp/devel/pi/rev/f9e523287348
changeset: 11987:f9e523287348
user: Mikel Irurita <mikel.irurita <at> openbravo.com>
date: Thu May 05 12:24:58 2011 +0200
summary: [APRM] Merge changeset 820:065c35c42ada of 2.50 APRM
details: /erp/devel/pi/rev/894723d0b1ed
changeset: 11988:894723d0b1ed
user: Mikel Irurita <mikel.irurita <at> openbravo.com>
date: Thu May 05 12:29:20 2011 +0200
summary: [APRM] Merge changeset 821:601d55e4b571 of 2.50 APRM
details: /erp/devel/pi/rev/601c54bfb8b2
changeset: 11989:601c54bfb8b2
user: Mikel Irurita <mikel.irurita <at> openbravo.com>
date: Thu May 05 12:31:03 2011 +0200
summary: [APRM] Merge changeset 822:5e041777706a of 2.50 APRM
diffstat:
modules/org.openbravo.advpaymentmngt/build/classes/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalance.class
| 0
modules/org.openbravo.advpaymentmngt/build/classes/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalanceData.class
| 0
modules/org.openbravo.advpaymentmngt/src-db/database/model/functions/APRM_GEN_PAYMENTSCHEDULE_INV.xml
| 21 +-
modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_PREFERENCE.xml
| 11 +
modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalance.java
| 51 ++
modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalance_data.xsql
| 69 ++
modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalanceData.java
| 250 ++++++++++
modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/process/FIN_ExecutePayment.java
| 4 +-
modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/process/FIN_PaymentProcess.java
| 113 +++-
9 files changed, 487 insertions(+), 32 deletions(-)
diffs (truncated from 688 to 300 lines):
diff -r f2a90f673328 -r 601c54bfb8b2
modules/org.openbravo.advpaymentmngt/build/classes/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalance.class
Binary file
modules/org.openbravo.advpaymentmngt/build/classes/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalance.class
has changed
diff -r f2a90f673328 -r 601c54bfb8b2
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 f2a90f673328 -r 601c54bfb8b2
modules/org.openbravo.advpaymentmngt/src-db/database/model/functions/APRM_GEN_PAYMENTSCHEDULE_INV.xml
---
a/modules/org.openbravo.advpaymentmngt/src-db/database/model/functions/APRM_GEN_PAYMENTSCHEDULE_INV.xml
Thu May 05 11:28:22 2011 +0200
+++
b/modules/org.openbravo.advpaymentmngt/src-db/database/model/functions/APRM_GEN_PAYMENTSCHEDULE_INV.xml
Thu May 05 12:31:03 2011 +0200
@@ -65,6 +65,7 @@
v_Line FIN_FINACC_TRANSACTION.LINE%TYPE;
v_count NUMBER;
v_PaymentPriority VARCHAR(32);
+v_creditmultiplier NUMBER := 1;
TYPE RECORD IS REF CURSOR;
@@ -107,6 +108,10 @@
v_project_id, v_campaign_id, v_activity_id, v_user1, v_user2
FROM c_invoice
WHERE c_invoice_id=p_record_id;
+
+ IF (v_IsSOTrx = 'N') THEN
+ v_creditmultiplier := -1;
+ END IF;
-- complete invoice
IF (p_docaction = 'CO') THEN
@@ -172,7 +177,7 @@
fin_payment_scheduledetail_id,
fin_payment_scheduledetail.fin_payment_schedule_order,
fin_payment_scheduledetail.fin_payment_detail_id,
- CASE WHEN fin_payment.status IS NULL OR fin_payment.status IN
('RPAP', 'RPAE') THEN 'N' ELSE 'Y' END AS paid
+ CASE WHEN fin_payment.status IN ('PPM', 'RPR', 'PWNC', 'RDNC',
'RPPC') THEN 'Y' ELSE 'N' END AS paid
FROM c_invoiceline, c_orderline, fin_payment_schedule,
fin_payment_scheduledetail LEFT JOIN fin_payment_detail ON
fin_payment_scheduledetail.fin_payment_detail_id =
fin_payment_detail.fin_payment_detail_id
LEFT JOIN fin_payment ON
fin_payment_detail.fin_payment_id = fin_payment.fin_payment_id
@@ -453,7 +458,7 @@
-- Updating SO_CREDITUSED when Invoices are being processed
UPDATE C_BPARTNER
- SET SO_CREDITUSED = COALESCE(SO_CREDITUSED,0) + V_OutstandingAmount
+ SET SO_CREDITUSED = COALESCE(SO_CREDITUSED,0) +
(COALESCE(V_OutstandingAmount, 0) * v_creditmultiplier)
WHERE C_BPARTNER_ID = v_BPartner_ID;
-- Automatic creation of payment and financial transaction
@@ -466,7 +471,7 @@
SELECT ps.fin_paymentmethod_id, ps.duedate, ps.c_currency_id,
ps.ad_org_id, ps.fin_payment_schedule_id,
sum(psd.amount) as outstandingamt
FROM fin_payment_schedule ps
- INNER JOIN fin_payment_scheduledetail psd ON
ps.fin_payment_schedule_id = psd.fin_payment_schedule_order
+ INNER JOIN fin_payment_scheduledetail psd ON
ps.fin_payment_schedule_id = psd.fin_payment_schedule_invoice
AND
psd.fin_payment_detail_id is null
WHERE ps.c_invoice_id = p_record_id
AND ps.isactive = 'Y'
@@ -583,7 +588,7 @@
AND TOTALPAID = GRANDTOTAL;
-- Updating SO_CREDITUSED when PAYMENT OCCUR and is not set in
RPAE status
UPDATE C_BPARTNER
- SET SO_CREDITUSED = COALESCE(SO_CREDITUSED,0) -
COALESCE(cur_paymentschedule.OUTSTANDINGAMT, 0)
+ SET SO_CREDITUSED = COALESCE(SO_CREDITUSED,0) -
(COALESCE(cur_paymentschedule.OUTSTANDINGAMT, 0) * v_creditmultiplier)
WHERE C_BPARTNER_ID = v_BPartner_ID;
END IF;
@@ -621,6 +626,7 @@
DECLARE
v_count NUMBER;
v_documentno VARCHAR2(40);
+ v_PaymentPlanExpectedTotal NUMBER := 0;
BEGIN
SELECT count(*), max(p.documentno)
INTO v_count, v_documentno
@@ -647,11 +653,12 @@
v_delete_paymentplan := TRUE;
-- Revert the bp used credit amount
- SELECT SUM(fin_payment_schedule.paidamt) INTO v_PaidAmount
+ SELECT COALESCE(SUM(fin_payment_schedule.paidamt), 0),
COALESCE(SUM(fin_payment_schedule.amount), 0)
+ INTO v_PaidAmount, v_PaymentPlanExpectedTotal
FROM FIN_PAYMENT_SCHEDULE
- WHERE FIN_PAYMENT_SCHEDULE.C_INVOICE_ID = p_record_id;
+ WHERE FIN_PAYMENT_SCHEDULE.C_INVOICE_ID = p_record_id ;
UPDATE C_BPARTNER
- SET SO_CREDITUSED = COALESCE(SO_CREDITUSED,0) - V_GrandTotal +
v_PaidAmount
+ SET SO_CREDITUSED = COALESCE(SO_CREDITUSED,0) -
((v_PaymentPlanExpectedTotal - v_PaidAmount) * v_creditmultiplier)
WHERE C_BPARTNER_ID = v_BPartner_ID;
END;
diff -r f2a90f673328 -r 601c54bfb8b2
modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_PREFERENCE.xml
---
a/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_PREFERENCE.xml
Thu May 05 11:28:22 2011 +0200
+++
b/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_PREFERENCE.xml
Thu May 05 12:31:03 2011 +0200
@@ -22,6 +22,17 @@
<!--3DBC63716B7C4FF6846CA092EA058BE8-->
<AD_MODULE_ID><![CDATA[A918E3331C404B889D69AA9BFAFB23AC]]></AD_MODULE_ID>
<!--3DBC63716B7C4FF6846CA092EA058BE8--></AD_PREFERENCE>
+<!--43ECD009731844E38DEA2EA80D790D6F--><AD_PREFERENCE>
+<!--43ECD009731844E38DEA2EA80D790D6F-->
<AD_PREFERENCE_ID><![CDATA[43ECD009731844E38DEA2EA80D790D6F]]></AD_PREFERENCE_ID>
+<!--43ECD009731844E38DEA2EA80D790D6F-->
<AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--43ECD009731844E38DEA2EA80D790D6F--> <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--43ECD009731844E38DEA2EA80D790D6F--> <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--43ECD009731844E38DEA2EA80D790D6F-->
<ATTRIBUTE><![CDATA[IsCustomerBalanceRestored]]></ATTRIBUTE>
+<!--43ECD009731844E38DEA2EA80D790D6F--> <VALUE><![CDATA[Y]]></VALUE>
+<!--43ECD009731844E38DEA2EA80D790D6F-->
<ISPROPERTYLIST><![CDATA[N]]></ISPROPERTYLIST>
+<!--43ECD009731844E38DEA2EA80D790D6F-->
<AD_MODULE_ID><![CDATA[A918E3331C404B889D69AA9BFAFB23AC]]></AD_MODULE_ID>
+<!--43ECD009731844E38DEA2EA80D790D6F--></AD_PREFERENCE>
+
<!--4AF17D34610443D7B69447C68D1B0420--><AD_PREFERENCE>
<!--4AF17D34610443D7B69447C68D1B0420-->
<AD_PREFERENCE_ID><![CDATA[4AF17D34610443D7B69447C68D1B0420]]></AD_PREFERENCE_ID>
<!--4AF17D34610443D7B69447C68D1B0420-->
<AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
diff -r f2a90f673328 -r 601c54bfb8b2
modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalance.java
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++
b/modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalance.java
Thu May 05 12:31:03 2011 +0200
@@ -0,0 +1,51 @@
+/*
+ *************************************************************************
+ * The contents of this file are subject to the Openbravo Public License
+ * Version 1.0 (the "License"), being the Mozilla Public License
+ * Version 1.1 with a permitted attribution clause; you may not use this
+ * file except in compliance with the License. You may obtain a copy of
+ * the License at http://www.openbravo.com/legal/license.html
+ * Software distributed under the License is distributed on an "AS IS"
+ * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
+ * License for the specific language governing rights and limitations
+ * under the License.
+ * The Original Code is Openbravo ERP.
+ * The Initial Developer of the Original Code is Openbravo SLU
+ * All portions are Copyright (C) 2011 Openbravo SLU
+ * All Rights Reserved.
+ * Contributor(s): ______________________________________.
+ *************************************************************************
+ */
+package org.openbravo.advpaymentmngt.modulescript;
+
+import org.openbravo.database.ConnectionProvider;
+import org.openbravo.modulescript.ModuleScript;
+
+public class UpdateCustomerBalance extends ModuleScript {
+
+ /**
+ * This modulescript regenerates the customer balance for all the business
partners.
+ * It takes in account the outstanding sales/purchase invoices and the credit
+ * generated in payment in/out.
+ */
+ @Override
+ public void execute() {
+ try {
+ ConnectionProvider cp = getConnectionProvider();
+ // Check if the modulescript has been executed before.
+ // If the preference does not exist in the database yet the modulescript
must be executed.
+ boolean isCustomerBalanceFixed =
UpdateCustomerBalanceData.isCustomerBalanceFixed(cp);
+ if (!isCustomerBalanceFixed) {
+ // Reset the customer balance to 0
+ UpdateCustomerBalanceData.resetCustomerCredit(cp);
+ // Obtain the correct customer balance
+ UpdateCustomerBalanceData[] data =
UpdateCustomerBalanceData.calculateCustomerCredit(cp);
+ for (UpdateCustomerBalanceData ucb : data) {
+ UpdateCustomerBalanceData.updateCustomerCredit(cp,
ucb.customercredit, ucb.cBpartnerId);
+ }
+ }
+ } catch (Exception e) {
+ handleError(e);
+ }
+ }
+}
diff -r f2a90f673328 -r 601c54bfb8b2
modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalance_data.xsql
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++
b/modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalance_data.xsql
Thu May 05 12:31:03 2011 +0200
@@ -0,0 +1,69 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<!--
+
************************************************************************************
+ * Copyright (C) 2011 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.
+
************************************************************************************
+-->
+<SqlClass name="UpdateCustomerBalanceData"
package="org.openbravo.advpaymentmngt.modulescript">
+ <SqlClassComment></SqlClassComment>
+ <SqlMethod name="select" type="preparedStatement" return="multiple">
+ <SqlMethodComment></SqlMethodComment>
+ <Sql>
+ <![CDATA[
+ SELECT '' as c_bpartner_id, '' as customercredit FROM DUAL
+ ]]>
+ </Sql>
+ </SqlMethod>
+ <SqlMethod name="isCustomerBalanceFixed" type="preparedStatement"
return="boolean">
+ <SqlMethodComment></SqlMethodComment>
+ <Sql>
+ <![CDATA[
+ SELECT count(*)
+ FROM ad_preference
+ WHERE attribute = 'IsCustomerBalanceRestored'
+ ]]>
+ </Sql>
+ </SqlMethod>
+ <SqlMethod name="resetCustomerCredit" type="preparedStatement"
return="rowcount">
+ <SqlMethodComment></SqlMethodComment>
+ <Sql>
+ <![CDATA[
+ UPDATE c_bpartner SET so_creditused = 0, updatedby='0', updated=now()
+ ]]>
+ </Sql>
+ </SqlMethod>
+ <SqlMethod name="calculateCustomerCredit" type="preparedStatement"
return="multiple">
+ <SqlMethodComment></SqlMethodComment>
+ <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 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
+ GROUP BY A.c_bpartner_id
+ ]]>
+ </Sql>
+ </SqlMethod>
+ <SqlMethod name="updateCustomerCredit" type="preparedStatement"
return="rowcount">
+ <SqlMethodComment></SqlMethodComment>
+ <Sql>
+ <![CDATA[
+ UPDATE c_bpartner SET so_creditused = ?, updatedby='0', updated=now()
WHERE c_bpartner_id = ?
+ ]]>
+ </Sql>
+ <Parameter name="cumstomeCredit"/>
+ <Parameter name="businessPartnerId"/>
+ </SqlMethod>
+</SqlClass>
diff -r f2a90f673328 -r 601c54bfb8b2
modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalanceData.java
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++
b/modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/src/org/openbravo/advpaymentmngt/modulescript/UpdateCustomerBalanceData.java
Thu May 05 12:31:03 2011 +0200
@@ -0,0 +1,250 @@
+//Sqlc generated V1.O00-1
+package org.openbravo.advpaymentmngt.modulescript;
+
+import java.sql.*;
+
+import org.apache.log4j.Logger;
+
+import javax.servlet.ServletException;
+
+import org.openbravo.data.FieldProvider;
+import org.openbravo.database.ConnectionProvider;
+import org.openbravo.data.UtilSql;
+import java.util.*;
+
+class UpdateCustomerBalanceData implements FieldProvider {
+static Logger log4j = Logger.getLogger(UpdateCustomerBalanceData.class);
+ private String InitRecordNumber="0";
+ public String cBpartnerId;
+ public String customercredit;
+
+ public String getInitRecordNumber() {
+ return InitRecordNumber;
+ }
+
+ public String getField(String fieldName) {
+ if (fieldName.equalsIgnoreCase("C_BPARTNER_ID") ||
fieldName.equals("cBpartnerId"))
+ return cBpartnerId;
+ else if (fieldName.equalsIgnoreCase("CUSTOMERCREDIT"))
+ return customercredit;
+ else {
+ log4j.debug("Field does not exist: " + fieldName);
+ return null;
+ }
+ }
+
+ public static UpdateCustomerBalanceData[] select(ConnectionProvider
connectionProvider) throws ServletException {
+ return select(connectionProvider, 0, 0);
+ }
+
+ public static UpdateCustomerBalanceData[] select(ConnectionProvider
connectionProvider, int firstRegister, int numberRegisters) throws
ServletException {
+ String strSql = "";
+ strSql = strSql +
+ " SELECT '' as c_bpartner_id, '' as customercredit FROM DUAL";
+
+ ResultSet result;
+ Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
+ PreparedStatement st = null;
+
+ try {
+ st = connectionProvider.getPreparedStatement(strSql);
+
+ result = st.executeQuery();
+ long countRecord = 0;
+ long countRecordSkip = 1;
+ boolean continueResult = true;
+ while(countRecordSkip < firstRegister && continueResult) {
+ continueResult = result.next();
+ countRecordSkip++;
+ }
+ while(continueResult && result.next()) {
+ countRecord++;
------------------------------------------------------------------------------
WhatsUp Gold - Download Free Network Management Software
The most intuitive, comprehensive, and cost-effective network
management toolset available today. Delivers lowest initial
acquisition cost and overall TCO of any competing solution.
http://p.sf.net/sfu/whatsupgold-sd
_______________________________________________
Openbravo-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
