details: https://code.openbravo.com/erp/devel/pi/rev/c265310457cd changeset: 17809:c265310457cd user: Ioritz Cia <ioritz.cia <at> openbravo.com> date: Wed Aug 29 10:14:08 2012 +0200 summary: Fixes issue 20892: Goods Shipment line and Order line relation. It is possible save a good shipment with more quantity than the order.
details: https://code.openbravo.com/erp/devel/pi/rev/78585c145c1f changeset: 17810:78585c145c1f user: Ioritz Cia <ioritz.cia <at> openbravo.com> date: Wed Aug 29 12:04:22 2012 +0200 summary: Fixes issue 1443: 'Wrong payment plan on invoiced orders' alert. Query needs to be corrected. diffstat: modules/org.openbravo.advpaymentmngt/src-util/buildvalidation/src/org/openbravo/advpaymentmngt/buildvalidation/WrongPaymentScheduleDetailsCheck_data.xsql | 12 +- src-db/database/model/triggers/M_INOUTLINE_TRG2.xml | 49 ++++++++++ 2 files changed, 58 insertions(+), 3 deletions(-) diffs (93 lines): diff -r cf0a1983bcb5 -r 78585c145c1f modules/org.openbravo.advpaymentmngt/src-util/buildvalidation/src/org/openbravo/advpaymentmngt/buildvalidation/WrongPaymentScheduleDetailsCheck_data.xsql --- a/modules/org.openbravo.advpaymentmngt/src-util/buildvalidation/src/org/openbravo/advpaymentmngt/buildvalidation/WrongPaymentScheduleDetailsCheck_data.xsql Thu Aug 30 01:22:51 2012 +0200 +++ b/modules/org.openbravo.advpaymentmngt/src-util/buildvalidation/src/org/openbravo/advpaymentmngt/buildvalidation/WrongPaymentScheduleDetailsCheck_data.xsql Wed Aug 29 12:04:22 2012 +0200 @@ -46,13 +46,16 @@ AND NOT EXISTS (SELECT 1 FROM fin_payment_scheduledetail psd JOIN fin_payment_schedule psi ON psd.fin_payment_schedule_invoice = psi.fin_payment_schedule_id WHERE il.c_invoice_id = psi.c_invoice_id - AND psd.fin_payment_schedule_order = pso.fin_payment_schedule_id)) + AND psd.fin_payment_schedule_order = pso.fin_payment_schedule_id) + GROUP BY il.c_invoice_id + HAVING sum(il.linenetamt +coalesce(c_getinvoiceline_taxamount(il.c_invoiceline_id),0)) != 0) AND EXISTS (SELECT 1 FROM fin_payment_scheduledetail psdo WHERE psdo.fin_payment_schedule_order = pso.fin_payment_schedule_id)) o JOIN c_orderline ol ON ol.c_order_id = o.c_order_id JOIN c_invoiceline il ON il.c_orderline_id = ol.c_orderline_id JOIN c_invoice i ON i.c_invoice_id = il.c_invoice_id WHERE i.docstatus <> 'VO' + AND NOT EXITS (SELECT 1 FROM c_invoice_reverse WHERE c_invoice_reverse.c_invoice_id = inv.c_invoice_id) ]]></Sql> </SqlMethod> <SqlMethod name="getUUID" type="preparedStatement" return="string"> @@ -165,13 +168,16 @@ AND NOT EXISTS (SELECT 1 FROM fin_payment_scheduledetail psd JOIN fin_payment_schedule psi ON psd.fin_payment_schedule_invoice = psi.fin_payment_schedule_id WHERE il.c_invoice_id = psi.c_invoice_id - AND psd.fin_payment_schedule_order = pso.fin_payment_schedule_id)) + AND psd.fin_payment_schedule_order = pso.fin_payment_schedule_id) + GROUP BY il.c_invoice_id + HAVING sum(il.linenetamt +coalesce(c_getinvoiceline_taxamount(il.c_invoiceline_id),0)) != 0) AND EXISTS (SELECT 1 FROM fin_payment_scheduledetail psdo WHERE psdo.fin_payment_schedule_order = pso.fin_payment_schedule_id)) o JOIN c_orderline ol ON ol.c_order_id = o.c_order_id JOIN c_invoiceline il ON il.c_orderline_id = ol.c_orderline_id JOIN c_invoice inv ON inv.c_invoice_id = il.c_invoice_id - WHERE inv.docstatus <> ''VO'' ' + WHERE inv.docstatus <> ''VO'' + AND NOT EXITS (SELECT 1 FROM c_invoice_reverse WHERE c_invoice_reverse.c_invoice_id = inv.c_invoice_id)' ) ]]></Sql> <Parameter name="alertRuleId"/> diff -r cf0a1983bcb5 -r 78585c145c1f src-db/database/model/triggers/M_INOUTLINE_TRG2.xml --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src-db/database/model/triggers/M_INOUTLINE_TRG2.xml Wed Aug 29 12:04:22 2012 +0200 @@ -0,0 +1,49 @@ +<?xml version="1.0"?> + <database name="TRIGGER M_INOUTLINE_TRG2"> + <trigger name="M_INOUTLINE_TRG2" table="M_INOUTLINE" fires="after" insert="true" update="true" delete="false" foreach="statement"> + <body><![CDATA[ + + + + +/************************************************************************* +* The contents of this file are subject to the Openbravo Public License +* Version 1.1 (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) 2012 Openbravo SLU +* All Rights Reserved. +* Contributor(s): ______________________________________. +************************************************************************/ + +v_Count NUMBER; + +BEGIN + + IF AD_isTriggerEnabled()='N' THEN RETURN; + END IF; + + SELECT MAX(A.NUM) + INTO v_Count + FROM ( + SELECT COUNT(*) AS NUM + FROM c_orderline ol + LEFT JOIN m_inoutline iol ON ol.c_orderline_id = iol.c_orderline_id + GROUP BY ol.qtyordered, iol.c_orderline_id + HAVING SUM(iol.movementqty) > ol.qtyordered) A; + + IF(v_Count <> 0) THEN + RAISE_APPLICATION_ERROR(-20000, '@MovementQtyCheck@'); + END IF; + +END M_INOUTLINE_TRG2 +]]></body> + </trigger> + </database> ------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ _______________________________________________ Openbravo-commits mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/openbravo-commits
