Fixes issue 35434: Manual taxes deleted when reactivating a PIT invoice
authorAlvaro Ferraz <alvaro.ferraz@openbravo.com>
Mon, 06 Mar 2017 13:56:27 +0100
changeset 31608 b0e68131dd07
parent 31607 17c715fc6bfe
child 31609 ee35e34cd34b
Fixes issue 35434: Manual taxes deleted when reactivating a PIT invoice

Now when reactivating a price including taxes invoice, manual taxes will not be deleted, and invoice gross amount will be updated to the sum of tax amounts of the manual taxes instead of zero.
Also, when calculating invoice gross and net amounts in invoice line trigger, manual taxes will be taken into account when getting the sum of tax amounts but not for getting the sum of tax base amounts.
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	Fri Mar 03 11:09:57 2017 +0100
+++ b/src-db/database/model/functions/C_INVOICE_POST.xml	Mon Mar 06 13:56:27 2017 +0100
@@ -157,6 +157,7 @@
   v_stdPrecision C_Currency.StdPrecision%TYPE;
   v_gross_unit_price NUMBER;
   v_line_gross_amount NUMBER;
+  v_taxAmt NUMBER;
   v_Isquantityvariable CHAR(1);
   
   v_isdeferred_inv C_InvoiceLine.IsDeferred%TYPE;
@@ -1198,8 +1199,9 @@
       IF (v_isTaxIncluded = 'Y') THEN
 
         -- Remove header amounts and taxes
-        DELETE FROM C_INVOICETAX WHERE C_Invoice_ID = v_Record_ID;
-        UPDATE C_INVOICE SET TotalLines = 0, GrandTotal = 0 WHERE C_Invoice_ID = v_Record_ID;
+        DELETE FROM C_INVOICETAX WHERE C_Invoice_ID = v_Record_ID AND Recalculate = 'Y';
+        SELECT COALESCE(SUM(TaxAmt), 0) INTO v_taxAmt FROM C_INVOICETAX WHERE C_Invoice_ID = v_Record_ID AND Recalculate = 'N';
+        UPDATE C_INVOICE SET TotalLines = 0, GrandTotal = v_taxAmt WHERE C_Invoice_ID = v_Record_ID;
 
         FOR Cur_line IN (
           SELECT C_InvoiceLine_ID
--- a/src-db/database/model/triggers/C_INVOICELINE_TRG2.xml	Fri Mar 03 11:09:57 2017 +0100
+++ b/src-db/database/model/triggers/C_INVOICELINE_TRG2.xml	Mon Mar 06 13:56:27 2017 +0100
@@ -102,6 +102,8 @@
         v_newgrossamt := :new.line_gross_amount;
      ELSIF (INSERTING) THEN
         v_newgrossamt := :new.line_gross_amount;
+     ELSIF (DELETING) THEN
+        v_oldgrossamt := :old.line_gross_amount;
      END IF;
    END IF;  
   IF(UPDATING OR DELETING) THEN
@@ -190,14 +192,19 @@
               
    END IF;
    -- Get Total Tax Base Amt and Tax Amt
-   SELECT COALESCE(SUM(it.TaxBaseAmt), 0), COALESCE(SUM(it.TaxAmt), 0)
-   INTO v_taxBaseAmt, v_taxAmt
+   SELECT COALESCE(SUM(it.TaxBaseAmt), 0)
+   INTO v_taxBaseAmt
    FROM (
-     SELECT CASE WHEN MIN(it.TaxBaseAmt) > 0 THEN MIN(ROUND(it.TaxBaseAmt, v_Prec)) ELSE MAX(ROUND(it.TaxBaseAmt, v_Prec)) END as TaxBaseAmt, SUM(it.TaxAmt) as TaxAmt
+     SELECT CASE WHEN MIN(it.TaxBaseAmt) > 0 THEN MIN(ROUND(it.TaxBaseAmt, v_Prec)) ELSE MAX(ROUND(it.TaxBaseAmt, v_Prec)) END as TaxBaseAmt
      FROM C_InvoiceTax it
      WHERE it.C_Invoice_ID = :new.C_Invoice_ID
+     AND it.Recalculate = 'Y'
      GROUP BY c_tax_get_root(it.c_tax_id)
    ) it;
+   SELECT COALESCE(SUM(it.TaxAmt), 0)
+   INTO v_taxAmt
+   FROM C_InvoiceTax it
+   WHERE it.C_Invoice_ID = :new.C_Invoice_ID;
   -- DBMS_OUTPUT.PUT_LINE('TaxAmt = ' || v_taxAmt);
    -- Update Header
    UPDATE C_Invoice
@@ -206,18 +213,20 @@
    WHERE C_Invoice_ID = :new.C_Invoice_ID;
    END IF;
   ELSE -- DELETING
-   IF (v_istaxincluded = 'Y') THEN
-     v_oldgrossamt := :old.line_gross_amount;
-   END IF;
    -- Get Total Tax Base Amt and Tax Amt
-   SELECT COALESCE(SUM(it.TaxBaseAmt), 0), COALESCE(SUM(it.TaxAmt), 0)
-   INTO v_taxBaseAmt, v_taxAmt
+   SELECT COALESCE(SUM(it.TaxBaseAmt), 0)
+   INTO v_taxBaseAmt
    FROM (
-     SELECT CASE WHEN MIN(it.TaxBaseAmt) > 0 THEN MIN(ROUND(it.TaxBaseAmt, v_Prec)) ELSE MAX(ROUND(it.TaxBaseAmt, v_Prec)) END as TaxBaseAmt, SUM(it.TaxAmt) as TaxAmt
+     SELECT CASE WHEN MIN(it.TaxBaseAmt) > 0 THEN MIN(ROUND(it.TaxBaseAmt, v_Prec)) ELSE MAX(ROUND(it.TaxBaseAmt, v_Prec)) END as TaxBaseAmt
      FROM C_InvoiceTax it
      WHERE it.C_Invoice_ID = :old.C_Invoice_ID
+     AND it.Recalculate = 'Y'
      GROUP BY c_tax_get_root(it.c_tax_id)
    ) it;
+   SELECT COALESCE(SUM(it.TaxAmt), 0)
+   INTO v_taxAmt
+   FROM C_InvoiceTax it
+   WHERE it.C_Invoice_ID = :old.C_Invoice_ID;
   -- DBMS_OUTPUT.PUT_LINE('TaxAmt = ' || v_taxAmt);
    -- Update Header
    UPDATE C_Invoice