Related to issue 34993: Logic moved to the complete action
authorMark <markmm82@gmail.com>
Tue, 28 Feb 2017 14:56:32 -0500
changeset 31570 68702618a590
parent 31569 9c931e5263c1
child 31571 4fd058dfb0fd
Related to issue 34993: Logic moved to the complete action

To avoid inconsistents scenarios in this issue, the processing logic included inside
the c_invoiceline_trg2 trigger was removed and included inside the c_invoice_post function.

This way the prepayment amount is always updated when the invoice is completed and
not when it lines are created, updated or deleted.

All other possible scenarios will be covered when the related design defect
https://issues.openbravo.com/view.php?id=35339 be fixed.
src-db/database/model/functions/C_INVOICE_POST.xml
src-db/database/model/triggers/C_INVOICELINE_TRG2.xml
--- a/src-db/database/model/functions/C_INVOICE_POST.xml	Mon Feb 27 13:05:01 2017 -0500
+++ b/src-db/database/model/functions/C_INVOICE_POST.xml	Tue Feb 28 14:56:32 2017 -0500
@@ -22,7 +22,7 @@
   * parts created by ComPiere are Copyright (C) ComPiere, Inc.;
   * All Rights Reserved.
   * Contributor(s): Openbravo SLU
-  * Contributions are Copyright (C) 2001-2016 Openbravo, S.L.U.
+  * Contributions are Copyright (C) 2001-2017 Openbravo, S.L.U.
   *
   * Specifically, this derivative work is based upon the following Compiere
   * file and version.
@@ -174,6 +174,7 @@
   v_invoiceline_qtysum NUMBER;
   v_PriceList_ID varchar2(32);
   v_prepaymentamt NUMBER;
+  v_prepayment_inorders NUMBER;
   v_hasTaxes NUMBER;
    
 BEGIN
@@ -1796,11 +1797,21 @@
     END IF;
   END IF;--FINISH_PROCESS
   IF (NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN
+    --Update the prepayment amount from orders related to the invoice lines.
+    SELECT COALESCE(SUM(fps.paidamt),0)
+      INTO v_prepayment_inorders
+    FROM fin_payment_schedule fps
+      JOIN c_order o ON o.c_order_id=fps.c_order_id
+      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
+    WHERE il.c_invoice_id = v_Record_ID;
+
     -- Finish up -------------------------------------------------------------
     UPDATE C_INVOICE
     SET DocStatus='CO',
         Processed='Y',
         DocAction='RE',
+        PrepaymentAmt=v_prepayment_inorders,
         Updated=now(),
         UpdatedBy=v_UpdatedBy
     WHERE C_Invoice_ID=v_Record_ID;
--- a/src-db/database/model/triggers/C_INVOICELINE_TRG2.xml	Mon Feb 27 13:05:01 2017 -0500
+++ b/src-db/database/model/triggers/C_INVOICELINE_TRG2.xml	Tue Feb 28 14:56:32 2017 -0500
@@ -56,8 +56,6 @@
   v_CalcLine NUMBER;
   v_maxline NUMBER;    
           
-  v_prepayment NUMBER;
-
   TYPE RECORD IS REF CURSOR;
   Cur_BOM RECORD;          
 BEGIN
@@ -94,8 +92,6 @@
  AND C_Invoice_ID=v_ID;
  v_oldLine:=0;
  v_newLineNetAmt:=0;
- v_prepayment:=0;
- 
  IF(v_Processed='N') THEN
   -- Calculate taxes
   IF (v_istaxincluded = 'Y') THEN
@@ -106,30 +102,8 @@
         v_newgrossamt := :new.line_gross_amount;
      END IF;
    END IF;  
-  IF (UPDATING OR DELETING) THEN
-    v_oldLine:= COALESCE(:old.LineNetAmt,0);
-    IF UPDATING THEN
-      v_newLineNetAmt:= COALESCE(:new.LineNetAmt,0);
-    END IF;
-    
-    IF (:old.c_orderline_id IS NOT NULL AND (v_oldLine <> 0 OR v_newLineNetAmt <> 0)) THEN
-      SELECT COALESCE(SUM(fps.paidamt),0)
-        INTO v_prepayment
-      FROM fin_payment_schedule fps
-        JOIN c_order ord ON ord.c_order_id=fps.c_order_id
-        JOIN c_orderline ordline ON ordline.c_order_id = ord.c_order_id
-      WHERE ordline.c_orderline_id = :old.c_orderline_id;
-      IF UPDATING THEN
-        IF v_newLineNetAmt <> 0 AND v_oldLine = 0 THEN
-          v_prepayment:= -1 * v_prepayment;
-        ELSIF v_newLineNetAmt <> 0 AND v_oldLine <> 0 THEN
-          v_prepayment:= 0;
-        END IF;
-      END IF;
-      UPDATE c_invoice
-      SET prepaymentamt = prepaymentamt - v_prepayment
-      WHERE c_invoice_id = v_ID;
-    END IF;
+  IF(UPDATING OR DELETING) THEN
+  v_oldLine:= COALESCE(:old.LineNetAmt,0);
   END IF;  
   IF (INSERTING OR UPDATING) THEN
    v_newLineNetAmt := COALESCE(:new.LineNetAmt,0);