0025555: Wrong invoice created when working with more than one ticket simultaneously and "group orders in one invoice" option disabled

diff --git a/src-db/database/model/functions/OBPOS_FIXISSUE25555.xml b/src-db/database/model/functions/OBPOS_FIXISSUE25555.xml new file mode 100644 --- /dev/null +++ b/src-db/database/model/functions/OBPOS_FIXISSUE25555.xml @@ -0,0 +1,131 @@ +<?xml version="1.0"?> + <database name="FUNCTION OBPOS_FIXISSUE25555"> + <function name="OBPOS_FIXISSUE25555" type="NULL"> + <body><![CDATA[TYPE RECORD IS REF CURSOR; +v_candidateInvoices RECORD; +v_wrongPaymentSched RECORD; +cursor1 RECORD; +v_needToExecute NUMBER:=0; +v_isWrongInvoice NUMBER:=0; +v_invoice1 character varying(32); +v_invoice2 character varying(32); +v_paymentSchedDet1 character varying(32); +v_paymentSched2 character varying(32); +v_order1 character varying(32); +v_numWrongInvoicesByOrder NUMBER:=0; +v_numOfPaymentSched NUMBER:=0; +v_numOfPaymentSched2 NUMBER:=0; +sumGrandTotal NUMBER:=0; +v_updateRecord boolean:=true; + + +BEGIN + + SELECT count(*) into v_needToExecute FROM ad_preference WHERE attribute = 'obpos_fixissue25555'; + + IF (v_needToExecute > 0) THEN + DBMS_OUTPUT.PUT_LINE('skip obpos_fixissue25555. Already executed - ' || v_needToExecute); + RETURN; + END IF; + DBMS_OUTPUT.PUT_LINE('Fixing wrong data because off issue 25555'); + + FOR v_candidateInvoices IN ( + select s.fin_payment_schedule_id , sum(psd.amount+psd.writeoffamt) as received_detail, s.amount, sum(psd.amount+psd.writeoffamt) - s.amount as diff_amt, s.c_invoice_id + from fin_payment_schedule s, fin_payment_scheduledetail psd + where s.fin_payment_schedule_id = psd.fin_payment_schedule_invoice + group by s.fin_payment_schedule_id, s.amount, s.c_invoice_id + having sum(psd.amount+psd.writeoffamt) > s.amount) LOOP + + v_updateRecord := true; + select count(*) INTO v_isWrongInvoice from( + select distinct il.c_invoice_id, o.c_order_id + from c_invoiceline il, c_orderline ol, c_order o + where il.c_invoice_id= v_candidateInvoices.c_invoice_id + and il.c_orderline_id = ol.c_orderline_id + and ol.c_order_id = o.c_order_id + and o.em_obpos_applications_id is not null + and exists (select 1 from c_invoiceline il2, c_orderline ol2, c_order o2 + where il2.c_orderline_id = ol2.c_orderline_id + and ol2.c_order_id = o2.c_order_id + and o2.em_obpos_applications_id is not null + and ol2.c_order_id = ol.c_order_id and il2.c_invoice_id <> il.c_invoice_id)) A; + + IF (v_isWrongInvoice=1) THEN + select distinct il.c_invoice_id, o.c_order_id INTO v_invoice1, v_order1 + from c_invoiceline il, c_orderline ol, c_order o + where il.c_invoice_id= v_candidateInvoices.c_invoice_id + and il.c_orderline_id = ol.c_orderline_id + and ol.c_order_id = o.c_order_id + and o.em_obpos_applications_id is not null + and exists (select 1 from c_invoiceline il2, c_orderline ol2, c_order o2 + where il2.c_orderline_id = ol2.c_orderline_id + and ol2.c_order_id = o2.c_order_id + and o2.em_obpos_applications_id is not null + and ol2.c_order_id = ol.c_order_id and il2.c_invoice_id <> il.c_invoice_id); + + sumGrandTotal :=0; + for cursor1 in (select distinct il.c_invoice_id, i.grandtotal + from c_invoiceline il, c_orderline ol, c_order o, c_invoice i + where il.c_orderline_id = ol.c_orderline_id + and ol.c_order_id = o.c_order_id + and il.c_invoice_id = i.c_invoice_id + and i.ispaid='Y' + and o.em_obpos_applications_id is not null + and ol.c_order_id = v_order1 + and il.c_invoice_id <> v_invoice1) loop + + v_invoice2:= cursor1.c_invoice_id; + + select count(1) INTO v_numOfPaymentSched + from fin_payment_schedule ps, fin_payment_scheduledetail psd + where ps.c_invoice_id = v_invoice1 + and ps.fin_payment_schedule_id = psd.fin_payment_schedule_invoice; + + IF (v_numOfPaymentSched=1) then + select psd.fin_payment_scheduledetail_id INTO v_paymentSchedDet1 + from fin_payment_schedule ps, fin_payment_scheduledetail psd + where ps.c_invoice_id = v_invoice1 + and ps.fin_payment_schedule_id = psd.fin_payment_schedule_invoice; + + + select count (ps.fin_payment_schedule_id) INTO v_numOfPaymentSched2 + from fin_payment_schedule ps + where ps.c_invoice_id = v_invoice2; + + IF (v_numOfPaymentSched2=1) then + + select ps.fin_payment_schedule_id INTO v_paymentSched2 + from fin_payment_schedule ps + where ps.c_invoice_id = v_invoice2; + + insert into fin_payment_scheduledetail + select get_uuid(), ad_client_id, ad_org_id, now(), '100', now(), '100', + fin_payment_detail_id, fin_payment_schedule_order, v_paymentSched2, cursor1.grandtotal, isactive, writeoffamt, iscanceled, + c_bpartner_id, c_activity_id, m_product_id, c_campaign_id, c_project_id, c_salesregion_id, c_costcenter_id, + user1_id, user2_id, doubtfuldebt_amount + from fin_payment_scheduledetail dv + where fin_payment_scheduledetail_id =v_paymentSchedDet1; + + sumGrandTotal := sumGrandTotal + cursor1.grandtotal; + END IF; + ELSE + v_updateRecord:=false; + end if; + + end loop; + if(v_updateRecord) then + update fin_payment_scheduledetail set amount = v_candidateInvoices.amount + where fin_payment_scheduledetail_id = v_paymentSchedDet1; + end if; + END IF; + END LOOP; + + INSERT INTO ad_preference (ad_preference_id, ad_client_id, ad_org_id, isactive, createdby, created, updatedby, updated, attribute) + VALUES ( get_uuid(), '0', '0', 'Y', '0', now(), '0', now(), 'obpos_fixissue25555' ); +EXCEPTION + WHEN OTHERS THEN + DBMS_OUTPUT.PUT_LINE('Error fixing data off issue 25555'); +END OBPOS_FIXISSUE25555 +]]></body> + </function> + </database> diff --git a/src-util/modulescript/src/org/openbravo/retail/posterminal/modulescript/FixDataIssue25555.java b/src-util/modulescript/src/org/openbravo/retail/posterminal/modulescript/FixDataIssue25555.java new file mode 100644 --- /dev/null +++ b/src-util/modulescript/src/org/openbravo/retail/posterminal/modulescript/FixDataIssue25555.java @@ -0,0 +1,41 @@ +/* + ************************************************************************* + * 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) 2013 Openbravo SLU + * All Rights Reserved. + * Contributor(s): ______________________________________. + ************************************************************************* + */ +package org.openbravo.retail.posterminal.modulescript; + +import java.sql.CallableStatement; +import java.sql.PreparedStatement; +import org.openbravo.database.ConnectionProvider; +import org.openbravo.modulescript.ModuleScript; + +public class FixDataIssue25555 extends ModuleScript { + + @Override + // Inserting m_inoutline_id for invoicelines which have this field as null + public void execute() { + try { + ConnectionProvider cp = getConnectionProvider(); + CallableStatement cs = cp.getConnection().prepareCall("{call OBPOS_FIXISSUE25555()}"); + cs.execute(); + cs.close(); + } catch (Exception e) { + System.out.println("error"); + handleError(e); + } + } +} diff --git a/src/org/openbravo/retail/posterminal/OrderGroupingProcessor.java b/src/org/openbravo/retail/posterminal/OrderGroupingProcessor.java --- a/src/org/openbravo/retail/posterminal/OrderGroupingProcessor.java +++ b/src/org/openbravo/retail/posterminal/OrderGroupingProcessor.java @@ -66,7 +66,7 @@ + "', '" + posTerminal.getObposTerminaltype().getDocumentTypeForReturns().getId() + "') and not exists (select 1 from OrderLine as ord where invoicedQuantity<>0 and ord.salesOrder = line.salesOrder)" - + " order by line.businessPartner.id"; + + " order by line.businessPartner.id, line.salesOrder.id"; OBQuery<OrderLine> query = OBDal.getInstance().createQuery(OrderLine.class, hqlWhereClause); query.setNamedParameter("terminal", posTerminal);

Issue History
Date Modified	Username	Field	Change
2014-01-24 13:07	maite	New Issue
2014-01-24 13:07	maite	Assigned To	=> marvintm
2014-01-24 13:07	maite	OBNetwork customer	=> No
2014-01-24 13:07	maite	Support ticket	=> 25995
2014-01-24 13:07	maite	Triggers an Emergency Pack	=> No
2014-01-24 13:08	maite	Issue Monitored: networkb
2014-01-24 13:11	maite	Proposed Solution updated
2014-01-24 13:11	maite	Target Version	=> RMP31
2014-01-24 13:18	malsasua	OBNetwork customer	No => Yes
2014-01-24 13:20	malsasua	Target Version	RMP31 => RMP32
2014-01-24 14:15	malsasua	Note Added: 0063679
2014-01-28 12:20	malsasua	File Added: patch25555.diff
2014-01-30 13:58	malsasua	File Deleted: patch25555.diff
2014-01-30 13:58	malsasua	File Added: issue25555.diff
2014-02-03 10:04	malsasua	File Deleted: issue25555.diff
2014-02-03 10:04	malsasua	File Added: diff25555.1.diff
2014-02-03 11:17	hgbot	Checkin
2014-02-03 11:17	hgbot	Note Added: 0063852
2014-02-03 11:17	hgbot	Status	new => resolved
2014-02-03 11:17	hgbot	Resolution	open => fixed
2014-02-03 11:17	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/1d01644315ccfd2e3a962bc608b0aad1389651fa [^]
2014-02-03 15:20	guilleaer	Review Assigned To	=> guilleaer
2014-02-03 15:20	guilleaer	Note Added: 0063864
2014-02-03 15:20	guilleaer	Status	resolved => closed
2014-02-03 15:20	guilleaer	Fixed in Version	=> RMP31
2014-02-06 13:20	hgbot	Checkin
2014-02-06 13:20	hgbot	Note Added: 0063973
2014-02-06 13:20	hgbot	Status	closed => resolved
2014-02-06 13:20	hgbot	Fixed in SCM revision	http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/1d01644315ccfd2e3a962bc608b0aad1389651fa [^] => http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal.cirque/rev/1d01644315ccfd2e3a962bc608b0aad1389651fa [^]
2014-02-06 15:22	guilleaer	Note Added: 0063984
2014-02-06 15:22	guilleaer	Status	resolved => closed

Notes
(0063679) malsasua (viewer) 2014-01-24 14:15	bug generated by this commit: https://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/b63277f5690d [^]

(0063852) hgbot (developer) 2014-02-03 11:17	Repository: erp/pmods/org.openbravo.retail.posterminal Changeset: 1d01644315ccfd2e3a962bc608b0aad1389651fa Author: Miguel A. Alsasua <miguel.alsasua <at> openbravo.com> Date: Mon Feb 03 11:17:25 2014 +0100 URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/1d01644315ccfd2e3a962bc608b0aad1389651fa [^] fixed issue 25555: Wrong invoice created when working with more than one ticket simultaneously and group orders in one invoice option disabled --- M src/org/openbravo/retail/posterminal/OrderGroupingProcessor.java A build/classes/org/openbravo/retail/posterminal/modulescript/FixDataIssue25555.class A src-db/database/model/functions/OBPOS_FIXISSUE25555.xml ---

(0063864) guilleaer (viewer) 2014-02-03 15:20	Verified

(0063973) hgbot (developer) 2014-02-06 13:20	Repository: erp/pmods/org.openbravo.retail.posterminal.cirque Changeset: 1d01644315ccfd2e3a962bc608b0aad1389651fa Author: Miguel A. Alsasua <miguel.alsasua <at> openbravo.com> Date: Mon Feb 03 11:17:25 2014 +0100 URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal.cirque/rev/1d01644315ccfd2e3a962bc608b0aad1389651fa [^] fixed issue 25555: Wrong invoice created when working with more than one ticket simultaneously and group orders in one invoice option disabled --- M src/org/openbravo/retail/posterminal/OrderGroupingProcessor.java A build/classes/org/openbravo/retail/posterminal/modulescript/FixDataIssue25555.class A src-db/database/model/functions/OBPOS_FIXISSUE25555.xml ---

(0063984) guilleaer (viewer) 2014-02-06 15:22	reclosing issues

Relationships [ Relation Graph ] [ Dependency Graph ]