Related to issue 34993: Update the invoice prepaiment amount with correct value
authorMark <markmm82@gmail.com>
Wed, 15 Feb 2017 15:56:52 -0500
changeset 31525 42227f2c47c3
parent 31524 e1c6deda8742
child 31526 0c31b06dd168
Related to issue 34993: Update the invoice prepaiment amount with correct value
if after create from a paid order line, it updates the line net amount to 0
or it changes from 0 to a another value.

If you create the invoice line from a paid order line, and then the line net amount
changes it value to 0 (by updating qty = 0 or price = 0, for instance), then
previously prepayment was remaining instead of be discarded. So, you may have an
invoice with Total Gross Amount of 0 and a prepayment different than 0.

When you are updating a line with a NEW line net amount = 0 or deleting the line,
it is needed to remove the prepayment of this line from the invoice.

If the OLD line net amount was zero (the prepayment was removed or not taken into
account when creating or updating the line) and it changes
to a non-zero new line net amount value, is necessary take into account the prepaid
order line amount to increase the invoice prepayment amount with its value.

In any other case it is not necessary update the prepayment amount of the invoice.
src-db/database/model/triggers/C_INVOICELINE_TRG2.xml
--- a/src-db/database/model/triggers/C_INVOICELINE_TRG2.xml	Tue Feb 14 15:52:41 2017 -0500
+++ b/src-db/database/model/triggers/C_INVOICELINE_TRG2.xml	Wed Feb 15 15:56:52 2017 -0500
@@ -105,17 +105,27 @@
    END IF;  
   IF(UPDATING OR DELETING) THEN
     v_oldLine:= COALESCE(:OLD.LineNetAmt,0);
-    IF(DELETING AND (:old.c_orderline_id IS NOT NULL OR :old.c_orderline_id <> '')) THEN
+    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;
-
-      UPDATE c_invoice
-      SET prepaymentamt = prepaymentamt - v_prepayment
-      WHERE c_invoice_id = :old.c_invoice_id;
+        
+      IF (DELETING OR (UPDATING AND v_newLineNetAmt = 0 AND v_oldLine <> 0)) THEN
+        UPDATE c_invoice
+        SET prepaymentamt = prepaymentamt - v_prepayment
+        WHERE c_invoice_id = :old.c_invoice_id;
+      ELSEIF (UPDATING AND v_newLineNetAmt <> 0 AND v_oldLine = 0) THEN
+        UPDATE c_invoice
+        SET prepaymentamt = prepaymentamt + v_prepayment
+        WHERE c_invoice_id = :old.c_invoice_id;      
+      END IF;     
     END IF;
   END IF;  
   IF (INSERTING OR UPDATING) THEN