details: https://code.openbravo.com/erp/devel/pi/rev/331403eece14 changeset: 15188:331403eece14 user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Tue Jan 24 17:41:26 2012 +0100 summary: Fixed issue 19534.Set greatest of invoiced or delivered qty.
details: https://code.openbravo.com/erp/devel/pi/rev/8f2bc6078c77 changeset: 15189:8f2bc6078c77 user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Tue Jan 24 17:42:28 2012 +0100 summary: Fixed issue 19534.Properly update grand total difference on psds after close. diffstat: modules/org.openbravo.advpaymentmngt/src-db/database/model/functions/APRM_GEN_PAYMENTSCHEDULE_ORD.xml | 110 +++++---- modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_MESSAGE.xml | 11 + src-db/database/model/functions/C_ORDER_POST1.xml | 37 +- 3 files changed, 91 insertions(+), 67 deletions(-) diffs (235 lines): diff -r 91a0972e4ee7 -r 8f2bc6078c77 modules/org.openbravo.advpaymentmngt/src-db/database/model/functions/APRM_GEN_PAYMENTSCHEDULE_ORD.xml --- a/modules/org.openbravo.advpaymentmngt/src-db/database/model/functions/APRM_GEN_PAYMENTSCHEDULE_ORD.xml Tue Jan 24 16:29:50 2012 +0100 +++ b/modules/org.openbravo.advpaymentmngt/src-db/database/model/functions/APRM_GEN_PAYMENTSCHEDULE_ORD.xml Tue Jan 24 17:42:28 2012 +0100 @@ -38,11 +38,7 @@ v_bpartner_id c_order.c_bpartner_id%TYPE; v_docSubType c_doctype.docsubtypeso%TYPE; v_PaymentPriority VARCHAR(32); - -v_Outstandingamt NUMBER; -v_Paidamt NUMBER; -v_Amount NUMBER; -v_FinPaymentScheduleId VARCHAR2(32); +v_count NUMBER; TYPE RECORD IS REF CURSOR; Cur_Params RECORD; @@ -80,13 +76,25 @@ FROM c_order WHERE c_order_id = p_record_id; - SELECT docsubtypeso - INTO v_docSubType + SELECT COALESCE(docsubtypeso, '---') INTO v_docSubType FROM c_doctype WHERE c_doctype_id = v_C_Doctype_ID; - IF(v_docSubType is null or v_docSubType not in ('OB', 'ON')) THEN - IF (p_docaction = 'CO') THEN + SELECT count(*) INTO v_count + FROM fin_payment_schedule + WHERE c_order_id = p_record_id + -- Migrated orders with related debt-payments have an additional fin_payment_schedule with the paid amount. + -- These additional fin_payment_schedules do not have fin_payment_scheduledetail records. + AND EXISTS (SELECT 1 FROM fin_payment_scheduledetail + WHERE fin_payment_schedule_order = fin_payment_schedule.fin_payment_schedule_id); + IF (v_count > 1) THEN + RAISE_APPLICATION_ERROR(-20000, '@APRM_Multiple_Payment_Schedules@'); + END IF; + + IF(v_docSubType in ('OB', 'ON')) THEN + RETURN; + END IF; + IF (p_docaction = 'CO') THEN -- Checking Payment Method ID is available IF(v_paymentmethod_id IS NULL) THEN RAISE_APPLICATION_ERROR(-20000, '@APRM_PAYMENTMETHOD_MISSING@'); @@ -171,7 +179,6 @@ ELSIF (p_docaction = 'RE') THEN -- check there is not any paid payment DECLARE - v_count NUMBER; Cur_PaymentScheduleDetail RECORD; BEGIN SELECT count(*) INTO v_count @@ -204,53 +211,60 @@ END; ELSIF (p_docaction = 'CL') THEN DECLARE - v_OldGrandTotal NUMBER; + v_difference NUMBER; + v_FinPaymentScheduleId VARCHAR2(32); + v_FinPaymentScheduleDetailId VARCHAR2(32); + v_psd_amt NUMBER; BEGIN - SELECT sum(amount) INTO v_OldGrandTotal + SELECT v_grandtotal - sum(amount) INTO v_difference FROM fin_payment_schedule WHERE c_order_id = p_record_id; - --IF (v_difference <> 0) THEN - -- UPDATE fin_payment_schedule - -- SET amount = amount - v_difference, - -- outstandingamt = outstandingamt - v_difference, - -- updated = TO_DATE(NOW()), - -- updatedby = p_user - -- WHERE c_order_id = p_record_id - -- AND outstandingamt > v_difference; - --END IF; - --This part of code Update the tables fin_payment_schedule and fin_payment_scheduledetail when one order is closed and the pyment is not - --the same of the expected amount when the order is closed - SELECT co.grandtotal,fps.outstandingamt,fps.paidamt,fps.amount,fps.fin_payment_schedule_id - INTO v_GrandTotal,v_Outstandingamt,v_Paidamt,v_Amount,v_FinPaymentScheduleId - FROM c_order co LEFT JOIN c_orderline col ON co.c_order_id=col.c_order_id - LEFT JOIN fin_payment_schedule fps ON fps.c_order_id = col.c_order_id - WHERE co.C_Order_ID=p_record_id - GROUP BY co.c_order_id,co.grandtotal,fps.outstandingamt,fps.paidamt,fps.amount,fps.fin_payment_schedule_id; - IF (v_OldGrandTotal <> v_GrandTotal) THEN - IF (v_Paidamt < v_OldGrandTotal) THEN - UPDATE fin_payment_scheduledetail set amount = v_GrandTotal - v_Paidamt where fin_payment_detail_id IS NULL - AND fin_payment_schedule_order = v_FinPaymentScheduleId; - ELSE - INSERT INTO fin_payment_scheduledetail ( - fin_payment_scheduledetail_id,ad_client_id,ad_org_id,created,createdby,updated,updatedby, - fin_payment_detail_id,fin_payment_schedule_order,fin_payment_schedule_invoice,amount, - isactive,writeoffamt,iscanceled,c_bpartner_id, c_activity_id,m_product_id,c_campaign_id, - c_project_id,c_salesregion_id - ) - VALUES( - get_uuid(),v_Client_ID,v_Org_ID,now(),p_user,now(),p_user, - null,v_FinPaymentScheduleId,null,v_GrandTotal-v_OldGrandTotal, - 'Y',0 ,'N',v_bpartner_id, null,null,null, - null,null - ); + SELECT ps.fin_payment_schedule_id INTO v_finpaymentscheduleid + FROM fin_payment_schedule ps + WHERE ps.c_order_id=p_record_id + AND EXISTS (SELECT 1 FROM fin_payment_scheduledetail WHERE fin_payment_schedule_order = ps.fin_payment_schedule_id); + + IF (v_difference <> 0) THEN + --Get fin_payment_scheduledetail not related to payments nor invoices. + SELECT fin_payment_scheduledetail_id, amount + INTO v_finpaymentscheduledetailid, v_psd_amt + FROM fin_payment_scheduledetail + WHERE fin_payment_schedule_order = v_finpaymentscheduleid + AND fin_payment_schedule_invoice IS NULL + AND fin_payment_detail_id IS NULL; + IF (v_finpaymentscheduledetailid IS NOT NULL) THEN + --If exists a psd not related to any invoice nor invoice update its amount with the difference. + IF (v_psd_amt * -1 = v_difference) THEN + -- If amounts are equal we delete the psd + DELETE FROM fin_payment_scheduledetail + WHERE fin_payment_scheduledetail_id = v_finpaymentscheduledetailid; + ELSE + UPDATE fin_payment_scheduledetail + SET amount = amount + v_difference + WHERE fin_payment_scheduledetail_id = v_finpaymentscheduledetailid; + END IF; + ELSE + -- If not exists a psd to update create one with the difference + INSERT INTO fin_payment_scheduledetail ( + fin_payment_scheduledetail_id, ad_client_id, ad_org_id, isactive, + created, createdby, updated, updatedby, + fin_payment_detail_id, fin_payment_schedule_order, fin_payment_schedule_invoice, + amount, writeoffamt, iscanceled + ) VALUES ( + get_uuid(), v_Client_ID, v_Org_ID,'Y', + now(), p_user, now(), p_user, + null, v_FinPaymentScheduleId, null, + v_difference, 0 ,'N' + ); END IF; - UPDATE fin_payment_schedule SET amount = v_GrandTotal,outstandingamt = v_GrandTotal-v_Paidamt + UPDATE fin_payment_schedule + SET amount = amount + v_difference, + outstandingamt = outstandingamt + v_difference WHERE fin_payment_schedule_id = v_FinPaymentScheduleId; END IF; END; END IF; - END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('APRM_GENERATE_PAYMENT_SCHEDULE exception: '|| v_ResultStr); diff -r 91a0972e4ee7 -r 8f2bc6078c77 modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_MESSAGE.xml --- a/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_MESSAGE.xml Tue Jan 24 16:29:50 2012 +0100 +++ b/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_MESSAGE.xml Tue Jan 24 17:42:28 2012 +0100 @@ -66,6 +66,17 @@ <!--19D77522B7A4470FBA505D39A6AA051C--> <AD_MODULE_ID><![CDATA[A918E3331C404B889D69AA9BFAFB23AC]]></AD_MODULE_ID> <!--19D77522B7A4470FBA505D39A6AA051C--></AD_MESSAGE> +<!--1E8BFED210524080A7F8FE17D6C4DED1--><AD_MESSAGE> +<!--1E8BFED210524080A7F8FE17D6C4DED1--> <AD_MESSAGE_ID><![CDATA[1E8BFED210524080A7F8FE17D6C4DED1]]></AD_MESSAGE_ID> +<!--1E8BFED210524080A7F8FE17D6C4DED1--> <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> +<!--1E8BFED210524080A7F8FE17D6C4DED1--> <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> +<!--1E8BFED210524080A7F8FE17D6C4DED1--> <ISACTIVE><![CDATA[Y]]></ISACTIVE> +<!--1E8BFED210524080A7F8FE17D6C4DED1--> <VALUE><![CDATA[APRM_Multiple_Payment_Schedules]]></VALUE> +<!--1E8BFED210524080A7F8FE17D6C4DED1--> <MSGTEXT><![CDATA[The order has more than one Payment Plan record with related Payment Plan details. Please contact your System Administrator to fix it.]]></MSGTEXT> +<!--1E8BFED210524080A7F8FE17D6C4DED1--> <MSGTYPE><![CDATA[E]]></MSGTYPE> +<!--1E8BFED210524080A7F8FE17D6C4DED1--> <AD_MODULE_ID><![CDATA[A918E3331C404B889D69AA9BFAFB23AC]]></AD_MODULE_ID> +<!--1E8BFED210524080A7F8FE17D6C4DED1--></AD_MESSAGE> + <!--1EB0D3A668784AF8A9133F066EDE6C44--><AD_MESSAGE> <!--1EB0D3A668784AF8A9133F066EDE6C44--> <AD_MESSAGE_ID><![CDATA[1EB0D3A668784AF8A9133F066EDE6C44]]></AD_MESSAGE_ID> <!--1EB0D3A668784AF8A9133F066EDE6C44--> <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> diff -r 91a0972e4ee7 -r 8f2bc6078c77 src-db/database/model/functions/C_ORDER_POST1.xml --- a/src-db/database/model/functions/C_ORDER_POST1.xml Tue Jan 24 16:29:50 2012 +0100 +++ b/src-db/database/model/functions/C_ORDER_POST1.xml Tue Jan 24 17:42:28 2012 +0100 @@ -421,39 +421,38 @@ -- Cancel undelivered Items IF (v_isSoTrx='Y') THEN --Sales orders UPDATE C_ORDERLINE - SET QtyOrdered=QtyDelivered, + SET QtyOrdered=GREATEST(QtyDelivered, qtyinvoiced), --MODIFIED BY P.SAROBE - linenetamt=QtyDelivered*priceactual, + linenetamt=GREATEST(QtyDelivered, qtyinvoiced)*priceactual, --MODIFIED BY F.IRIAZABAL QuantityOrder=QuantityOrder, Updated=now() WHERE C_Order_ID=v_Record_ID - AND QtyOrdered<>QtyDelivered; + AND QtyOrdered<>GREATEST(QtyDelivered, qtyinvoiced); -- For Purchase orders, M_MatchPO table used. Notice that only delivered lines(C_Invoiceline_Id is null) using ELSE UPDATE C_ORDERLINE - SET QTYORDERED=COALESCE - ( - (SELECT SUM(M_MATCHPO.QTY) + SET QTYORDERED=COALESCE(( + SELECT GREATEST(SUM(CASE WHEN M_MATCHPO.C_INVOICELINE_ID IS NULL THEN M_MATCHPO.QTY ELSE 0 END), + SUM(CASE WHEN M_MATCHPO.M_INOUTLINE_ID IS NULL THEN M_MATCHPO.QTY ELSE 0 END)) FROM M_MATCHPO WHERE M_MATCHPO.C_ORDERLINE_ID=C_ORDERLINE.C_ORDERLINE_ID - AND M_MATCHPO.C_INVOICELINE_ID IS NULL - ) - , 0 - ), + ), 0), QuantityOrder=C_ORDERLINE.QuantityOrder, Updated=now(), - linenetamt=COALESCE - ( - (SELECT SUM(M_MATCHPO.QTY) + linenetamt=COALESCE(( + SELECT GREATEST(SUM(CASE WHEN M_MATCHPO.C_INVOICELINE_ID IS NULL THEN M_MATCHPO.QTY ELSE 0 END), + SUM(CASE WHEN M_MATCHPO.M_INOUTLINE_ID IS NULL THEN M_MATCHPO.QTY ELSE 0 END)) FROM M_MATCHPO WHERE M_MATCHPO.C_ORDERLINE_ID=C_ORDERLINE.C_ORDERLINE_ID - AND M_MATCHPO.C_INVOICELINE_ID IS NULL - ) - , 0 - ) - *priceactual - WHERE C_ORDERLINE.C_ORDER_ID=v_Record_ID; + ), 0)*priceactual + WHERE C_ORDERLINE.C_ORDER_ID=v_Record_ID + AND qtyordered <> COALESCE(( + SELECT GREATEST(SUM(CASE WHEN M_MATCHPO.C_INVOICELINE_ID IS NULL THEN M_MATCHPO.QTY ELSE 0 END), + SUM(CASE WHEN M_MATCHPO.M_INOUTLINE_ID IS NULL THEN M_MATCHPO.QTY ELSE 0 END)) + FROM M_MATCHPO + WHERE M_MATCHPO.C_ORDERLINE_ID=C_ORDERLINE.C_ORDERLINE_ID + ), 0); END IF; -- if there is no change, the tax calculation, etc. is not needed. -- potential problem, if posted (i.e. encumbered) for full amount ------------------------------------------------------------------------------ Keep Your Developer Skills Current with LearnDevNow! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-d2d _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
