details: https://code.openbravo.com/erp/devel/pi/rev/6ef88c49cd26 changeset: 25736:6ef88c49cd26 user: Atul Gaware <atul.gaware <at> openbravo.com> date: Wed Jan 21 08:26:08 2015 +0530 summary: Backout 9c12d1443b20 changeset
Breaks int-inc-oracle test and is a old fix sent during review. details: https://code.openbravo.com/erp/devel/pi/rev/dad8f2ef7d46 changeset: 25737:dad8f2ef7d46 user: Atul Gaware <atul.gaware <at> openbravo.com> date: Tue Jan 20 14:57:35 2015 +0530 summary: Fixes Issue 28591:Reversing a payment is creating wrong invoice payment plan details Problem is Schedule detail was being updated by payment amount now it is taken care to update using individual schedule detail amount while creating reverse payment schedule and payment schedule details. Modulescript to fix errorneous data consists of update invoice paid, outstanding amt, update payment schedule received and outstanding amount, update payment credit generated info if in case required. diffstat: modules/org.openbravo.advpaymentmngt/build/classes/org/openbravo/advpaymentmngt/modulescript/Issue28591UpdatePSD.class | 0 modules/org.openbravo.advpaymentmngt/build/classes/org/openbravo/advpaymentmngt/modulescript/Issue28591UpdatePSDData.class | 0 modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/Issue28591UpdatePSD.java | 21 +- modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/Issue28591UpdatePSD_data.xsql | 164 +++- modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/src/org/openbravo/advpaymentmngt/modulescript/Issue28591UpdatePSDData.java | 420 +++++++++- 5 files changed, 583 insertions(+), 22 deletions(-) diffs (truncated from 700 to 300 lines): diff -r e3b446f2e3b0 -r dad8f2ef7d46 modules/org.openbravo.advpaymentmngt/build/classes/org/openbravo/advpaymentmngt/modulescript/Issue28591UpdatePSD.class Binary file modules/org.openbravo.advpaymentmngt/build/classes/org/openbravo/advpaymentmngt/modulescript/Issue28591UpdatePSD.class has changed diff -r e3b446f2e3b0 -r dad8f2ef7d46 modules/org.openbravo.advpaymentmngt/build/classes/org/openbravo/advpaymentmngt/modulescript/Issue28591UpdatePSDData.class Binary file modules/org.openbravo.advpaymentmngt/build/classes/org/openbravo/advpaymentmngt/modulescript/Issue28591UpdatePSDData.class has changed diff -r e3b446f2e3b0 -r dad8f2ef7d46 modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/Issue28591UpdatePSD.java --- a/modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/Issue28591UpdatePSD.java Mon Jan 19 14:42:57 2015 +0100 +++ b/modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/Issue28591UpdatePSD.java Tue Jan 20 14:57:35 2015 +0530 @@ -1,4 +1,4 @@ -/* +/* ************************************************************************* * The contents of this file are subject to the Openbravo Public License * Version 1.0 (the "License"), being the Mozilla Public License @@ -29,10 +29,21 @@ boolean issue28591UpdatePSD = Issue28591UpdatePSDData.updateWrongPSD(cp); if(!issue28591UpdatePSD) { Issue28591UpdatePSDData[] data = Issue28591UpdatePSDData.selectPSD(cp); - for (Issue28591UpdatePSDData upsd : data) { - Issue28591UpdatePSDData.updatePSDAmount(cp, upsd.outstandingamt, upsd.finPaymentScheduledetailId); - } - Issue28591UpdatePSDData.createPreference(cp); + for (Issue28591UpdatePSDData upsd : data) { + Issue28591UpdatePSDData.updatePSDAmount(cp, upsd.outstandingamt, upsd.finPaymentScheduledetailId); + } + Issue28591UpdatePSDData[] wrongdata = Issue28591UpdatePSDData.selectWrongPSD(cp); + for (Issue28591UpdatePSDData wpsd : wrongdata) { + Issue28591UpdatePSDData.updateWrongInvoiceAmt(cp, wpsd.wrongamt, wpsd.cInvoiceId); + Issue28591UpdatePSDData.updateWrongPSAmt(cp, wpsd.wrongamt, wpsd.finPaymentScheduleId); + Issue28591UpdatePSDData.updateWrongPSDAmt(cp, wpsd.wrongamt, wpsd.finPaymentScheduledetailId); + String finPaymentDetailId = Issue28591UpdatePSDData.selectFinPaymentDetailId(cp); + Issue28591UpdatePSDData.createCredit(cp, finPaymentDetailId, wpsd.finPaymentId, wpsd.wrongamt); + Issue28591UpdatePSDData.createCreditScheduledetail(cp, finPaymentDetailId, wpsd.wrongamt); + Issue28591UpdatePSDData.updateCreditGenerated(cp, wpsd.wrongamt, wpsd.finPaymentId); + Issue28591UpdatePSDData.updateWrongPDAmt(cp, wpsd.wrongamt, wpsd.finPaymentDetailId); + } + Issue28591UpdatePSDData.createPreference(cp); } } catch (Exception e) { handleError(e); diff -r e3b446f2e3b0 -r dad8f2ef7d46 modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/Issue28591UpdatePSD_data.xsql --- a/modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/Issue28591UpdatePSD_data.xsql Mon Jan 19 14:42:57 2015 +0100 +++ b/modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/Issue28591UpdatePSD_data.xsql Tue Jan 20 14:57:35 2015 +0530 @@ -23,35 +23,179 @@ <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT '' as fin_payment_scheduledetail_id, '' as outstandingamt + SELECT '' as fin_payment_scheduledetail_id, '' as outstandingamt, '' as wrongamt, + '' as fin_payment_schedule_id, '' as c_invoice_id, '' as c_currency_id, '' as bp_currency_id, + '' as fin_payment_id, '' as fin_payment_detail_id, '' as isreceipt, '' as c_bpartner_id FROM DUAL ]]> </Sql> - </SqlMethod> + </SqlMethod> <SqlMethod name="selectPSD" type="preparedStatement" return="multiple"> <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - select ps.outstandingamt as outstandingamt, max(psd.fin_payment_scheduledetail_id) as fin_payment_scheduledetail_id - from fin_payment_scheduledetail psd - LEFT JOIN fin_payment_schedule ps ON ps.fin_payment_schedule_id = COALESCE(psd.fin_payment_schedule_invoice,psd.fin_payment_schedule_order) - where psd.fin_payment_detail_id is null - group by ps.fin_payment_schedule_id + select ps.outstandingamt as outstandingamt, max(psd.fin_payment_scheduledetail_id) as fin_payment_scheduledetail_id + from fin_payment_scheduledetail psd + LEFT JOIN fin_payment_schedule ps ON ps.fin_payment_schedule_id = COALESCE(psd.fin_payment_schedule_invoice,psd.fin_payment_schedule_order) + where psd.fin_payment_detail_id is null and ps.outstandingamt > 0 + group by ps.outstandingamt, ps.fin_payment_schedule_id having sum(psd.amount) <> ps.outstandingamt ]]> </Sql> + </SqlMethod> + <SqlMethod name="selectWrongPSD" type="preparedStatement" return="multiple"> + <SqlMethodComment></SqlMethodComment> + <Sql> + <![CDATA[ + select i.c_invoice_id, fp.c_currency_id, bp.c_bpartner_id, bp.bp_currency_id, fp.fin_payment_id, ps.fin_payment_schedule_id, + psd.fin_payment_scheduledetail_id, pd.fin_payment_detail_id, ps.outstandingamt as wrongamt, to_char(fp.isreceipt) as isreceipt + from c_invoice i + left join c_bpartner bp on bp.c_bpartner_id = i.c_bpartner_id + left join fin_payment_schedule ps on ps.c_invoice_id = i.c_invoice_id + left join fin_payment_scheduledetail psd on psd.fin_payment_schedule_invoice = ps.fin_payment_schedule_id + left join fin_payment_detail pd on psd.fin_payment_detail_id = pd.fin_payment_detail_id + left join fin_payment fp on fp.fin_payment_id = pd.fin_payment_id + where i.outstandingamt < 0 + and i.outstandingamt = ps.outstandingamt + and ps.paidamt = psd.amount + ]]> + </Sql> + </SqlMethod> + <SqlMethod name="updateWrongInvoiceAmt" type="preparedStatement" return="rowcount"> + <SqlMethodComment></SqlMethodComment> + <Sql> + <![CDATA[ + update c_invoice set ispaid= case when grandtotal=totalpaid+to_number(?) then 'Y' else 'N' end, totalpaid=totalpaid+to_number(?), + LastCalculatedOnDate=LastCalculatedOnDate+to_number(?), outstandingamt=outstandingamt-to_number(?) , + DaysTillDue=substract_days(to_timestamp(now()),(select min (duedate) from FIN_Payment_Schedule where c_invoice_id=?)), + Percentageoverdue=(select round((sum(case when fp.Paymentdate > ps.duedate then psd.amount else 0 end )*100)/GrandTotal,2) + from c_invoice ci, fin_payment_scheduledetail psd , fin_payment_schedule ps, fin_payment fp, FIN_Payment_Detail pd + where ps.c_invoice_id = ci.c_invoice_id + AND psd.fin_payment_schedule_invoice = ps.fin_payment_schedule_id + and pd.fin_payment_detail_id=psd.fin_payment_detail_id + and fp.fin_payment_id=pd.fin_payment_id + and ci.c_invoice_id=? + group by ci.c_invoice_id, ci.grandtotal, ci.totalpaid, ci.LastCalculatedOnDate, ci.DaysTillDue, ci.Percentageoverdue), + updatedby='0', updated=now() + where c_invoice_id=? + ]]> + </Sql> + <Parameter name="Amount"/> + <Parameter name="Amount"/> + <Parameter name="Amount"/> + <Parameter name="Amount"/> + <Parameter name="cInvoiceId"/> + <Parameter name="cInvoiceId"/> + <Parameter name="cInvoiceId"/> + </SqlMethod> + <SqlMethod name="updateWrongPSAmt" type="preparedStatement" return="rowcount"> + <SqlMethodComment></SqlMethodComment> + <Sql> + <![CDATA[ + UPDATE FIN_PAYMENT_SCHEDULE SET PAIDAMT = PAIDAMT + TO_NUMBER(?), + OUTSTANDINGAMT= OUTSTANDINGAMT - TO_NUMBER(?), + updatedby='0', updated=now() + WHERE FIN_PAYMENT_SCHEDULE_ID = ? + ]]> + </Sql> + <Parameter name="outStandingAmount"/> + <Parameter name="outStandingAmount"/> + <Parameter name="finPaymentScheduleId"/> + </SqlMethod> + <SqlMethod name="updateWrongPSDAmt" type="preparedStatement" return="rowcount"> + <SqlMethodComment></SqlMethodComment> + <Sql> + <![CDATA[ + UPDATE FIN_PAYMENT_SCHEDULEDETAIL SET AMOUNT=AMOUNT + TO_NUMBER(?), + updatedby='0', updated=now() + WHERE FIN_PAYMENT_SCHEDULEDETAIL_ID = ? + ]]> + </Sql> + <Parameter name="Amount"/> + <Parameter name="finPaymentScheduledetailId"/> + </SqlMethod> + <SqlMethod name="selectFinPaymentDetailId" type="preparedStatement" return="String"> + <SqlMethodComment></SqlMethodComment> + <Sql> + <![CDATA[ + SELECT get_uuid() as fin_payment_detail_id + FROM DUAL + ]]> + </Sql> + </SqlMethod> + <SqlMethod name="createCredit" type="preparedStatement" return="rowcount"> + <SqlMethodComment></SqlMethodComment> + <Sql> + <![CDATA[ + INSERT INTO fin_payment_detail ( + fin_payment_detail_id, ad_client_id, ad_org_id, isactive, + createdby, created, updatedby, updated, fin_payment_id, + amount, refund, isprepayment + ) VALUES ( + ?, '0', '0', 'Y', + '0', NOW(), '0', NOW(), ?, + TO_NUMBER(?)*(-1), 'N', 'Y' + ) + ]]> + </Sql> + <Parameter name="finPaymentDetailId"/> + <Parameter name="finPaymentId"/> + <Parameter name="Amount"/> + </SqlMethod> + <SqlMethod name="createCreditScheduledetail" type="preparedStatement" return="rowcount"> + <SqlMethodComment></SqlMethodComment> + <Sql> + <![CDATA[ + INSERT INTO fin_payment_scheduledetail ( + fin_payment_scheduledetail_id, ad_client_id, ad_org_id, isactive, + createdby, created, updatedby, updated, fin_payment_detail_id, + amount + ) VALUES ( + get_uuid(), '0', '0', 'Y', + '0', NOW(), '0', NOW(), ?, + TO_NUMBER(?)*(-1) + ) + ]]> + </Sql> + <Parameter name="finPaymentDetailId"/> + <Parameter name="Amount"/> + </SqlMethod> + <SqlMethod name="updateCreditGenerated" type="preparedStatement" return="rowcount"> + <SqlMethodComment></SqlMethodComment> + <Sql> + <![CDATA[ + UPDATE FIN_PAYMENT SET generated_credit=generated_credit+(TO_NUMBER(?)*-1), + updatedby='0', updated=now() + WHERE FIN_PAYMENT_ID = ? + ]]> + </Sql> + <Parameter name="Amount"/> + <Parameter name="finPaymentId"/> + </SqlMethod> + <SqlMethod name="updateWrongPDAmt" type="preparedStatement" return="rowcount"> + <SqlMethodComment></SqlMethodComment> + <Sql> + <![CDATA[ + UPDATE FIN_PAYMENT_DETAIL SET AMOUNT=AMOUNT+TO_NUMBER(?), + updatedby='0', updated=now() + WHERE FIN_PAYMENT_DETAIL_ID = ? + ]]> + </Sql> + <Parameter name="Amount"/> + <Parameter name="finPaymentDetailId"/> </SqlMethod> <SqlMethod name="updatePSDAmount" type="preparedStatement" return="rowcount"> <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - UPDATE FIN_PAYMENT_SCHEDULEDETAIL SET AMOUNT=TO_NUMBER(?) + UPDATE FIN_PAYMENT_SCHEDULEDETAIL SET AMOUNT=TO_NUMBER(?), + updatedby='0', updated=now() WHERE FIN_PAYMENT_SCHEDULEDETAIL_ID = ? ]]> </Sql> <Parameter name="outStandingAmount"/> - <Parameter name="finPaymentScheduledetailId"/> - </SqlMethod> + <Parameter name="finPaymentScheduledetailId"/> + </SqlMethod> <SqlMethod name="updateWrongPSD" type="preparedStatement" return="boolean"> <SqlMethodComment></SqlMethodComment> <Sql> diff -r e3b446f2e3b0 -r dad8f2ef7d46 modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/src/org/openbravo/advpaymentmngt/modulescript/Issue28591UpdatePSDData.java --- a/modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/src/org/openbravo/advpaymentmngt/modulescript/Issue28591UpdatePSDData.java Mon Jan 19 14:42:57 2015 +0100 +++ b/modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/src/org/openbravo/advpaymentmngt/modulescript/Issue28591UpdatePSDData.java Tue Jan 20 14:57:35 2015 +0530 @@ -17,6 +17,15 @@ private String InitRecordNumber="0"; public String finPaymentScheduledetailId; public String outstandingamt; + public String wrongamt; + public String finPaymentScheduleId; + public String cInvoiceId; + public String cCurrencyId; + public String bpCurrencyId; + public String finPaymentId; + public String finPaymentDetailId; + public String isreceipt; + public String cBpartnerId; public String getInitRecordNumber() { return InitRecordNumber; @@ -27,6 +36,24 @@ return finPaymentScheduledetailId; else if (fieldName.equalsIgnoreCase("outstandingamt")) return outstandingamt; + else if (fieldName.equalsIgnoreCase("wrongamt")) + return wrongamt; + else if (fieldName.equalsIgnoreCase("fin_payment_schedule_id") || fieldName.equals("finPaymentScheduleId")) + return finPaymentScheduleId; + else if (fieldName.equalsIgnoreCase("c_invoice_id") || fieldName.equals("cInvoiceId")) + return cInvoiceId; + else if (fieldName.equalsIgnoreCase("c_currency_id") || fieldName.equals("cCurrencyId")) + return cCurrencyId; + else if (fieldName.equalsIgnoreCase("bp_currency_id") || fieldName.equals("bpCurrencyId")) + return bpCurrencyId; + else if (fieldName.equalsIgnoreCase("fin_payment_id") || fieldName.equals("finPaymentId")) + return finPaymentId; + else if (fieldName.equalsIgnoreCase("fin_payment_detail_id") || fieldName.equals("finPaymentDetailId")) + return finPaymentDetailId; + else if (fieldName.equalsIgnoreCase("isreceipt")) + return isreceipt; + else if (fieldName.equalsIgnoreCase("c_bpartner_id") || fieldName.equals("cBpartnerId")) + return cBpartnerId; else { log4j.debug("Field does not exist: " + fieldName); return null; @@ -40,7 +67,9 @@ public static Issue28591UpdatePSDData[] select(ConnectionProvider connectionProvider, int firstRegister, int numberRegisters) throws ServletException { String strSql = ""; strSql = strSql + - " SELECT '' as fin_payment_scheduledetail_id, '' as outstandingamt " + + " SELECT '' as fin_payment_scheduledetail_id, '' as outstandingamt, '' as wrongamt, " + + " '' as fin_payment_schedule_id, '' as c_invoice_id, '' as c_currency_id, '' as bp_currency_id," + + " '' as fin_payment_id, '' as fin_payment_detail_id, '' as isreceipt, '' as c_bpartner_id" + " FROM DUAL"; ResultSet result; @@ -63,6 +92,15 @@ Issue28591UpdatePSDData objectIssue28591UpdatePSDData = new Issue28591UpdatePSDData(); objectIssue28591UpdatePSDData.finPaymentScheduledetailId = UtilSql.getValue(result, "fin_payment_scheduledetail_id"); objectIssue28591UpdatePSDData.outstandingamt = UtilSql.getValue(result, "outstandingamt"); + objectIssue28591UpdatePSDData.wrongamt = UtilSql.getValue(result, "wrongamt"); + objectIssue28591UpdatePSDData.finPaymentScheduleId = UtilSql.getValue(result, "fin_payment_schedule_id"); + objectIssue28591UpdatePSDData.cInvoiceId = UtilSql.getValue(result, "c_invoice_id"); + objectIssue28591UpdatePSDData.cCurrencyId = UtilSql.getValue(result, "c_currency_id"); + objectIssue28591UpdatePSDData.bpCurrencyId = UtilSql.getValue(result, "bp_currency_id"); + objectIssue28591UpdatePSDData.finPaymentId = UtilSql.getValue(result, "fin_payment_id"); + objectIssue28591UpdatePSDData.finPaymentDetailId = UtilSql.getValue(result, "fin_payment_detail_id"); + objectIssue28591UpdatePSDData.isreceipt = UtilSql.getValue(result, "isreceipt"); + objectIssue28591UpdatePSDData.cBpartnerId = UtilSql.getValue(result, "c_bpartner_id"); ------------------------------------------------------------------------------ New Year. New Location. New Benefits. New Data Center in Ashburn, VA. GigeNET is offering a free month of service with a new server in Ashburn. Choose from 2 high performing configs, both with 100TB of bandwidth. Higher redundancy.Lower latency.Increased capacity.Completely compliant. http://p.sf.net/sfu/gigenet _______________________________________________ Openbravo-commits mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/openbravo-commits
