Fixes Issue 32594:Cash VAT field of document header is updated to 'N'
authorAtul Gaware <atul.gaware@openbravo.com>
Fri, 08 Apr 2016 14:51:17 +0530
changeset 29854 af0d6a9e780b
parent 29853 7cff9e8c97c2
child 29855 956cd99a1937
Fixes Issue 32594:Cash VAT field of document header is updated to 'N'
when you insert a tax rate of 0

Added condition prior to update the order cash flag vat, to check
whether tax is non withholding tax and non zero tax.

Code is now shifted to C_OrderTax and C_InvoiceTax Triggers instead
of C_OrderLine and C_InvoiceLine Triggers.
src-db/database/model/triggers/C_INVOICELINE_TRG.xml
src-db/database/model/triggers/C_INVOICETAX_TRG.xml
src-db/database/model/triggers/C_ORDERLINE_TRG.xml
src-db/database/model/triggers/C_ORDERTAX_TRG.xml
--- a/src-db/database/model/triggers/C_INVOICELINE_TRG.xml	Thu Apr 14 17:49:21 2016 +0200
+++ b/src-db/database/model/triggers/C_INVOICELINE_TRG.xml	Fri Apr 08 14:51:17 2016 +0530
@@ -14,7 +14,7 @@
 * under the License.
 * The Original Code is Openbravo ERP.
 * The Initial Developer of the Original Code is Openbravo SLU
-* All portions are Copyright (C) 2001-2015 Openbravo SLU
+* All portions are Copyright (C) 2001-2016 Openbravo SLU
 * All Rights Reserved.
 * Contributor(s):  ______________________________________.
 ************************************************************************/
@@ -39,8 +39,6 @@
  v_CinvoiceTaxID varchar2(32);
  v_AttrSetValueType M_Product.AttrSetValueType%TYPE;
  v_count NUMBER;
- v_IsCashVAT_Tax C_Tax.IsCashVAT%TYPE;
- v_IsCashVAT_Invoice C_Invoice.IsCashVAT%TYPE;
     
 BEGIN
     
@@ -102,38 +100,6 @@
   END IF;
  END IF;
 
-
- -- Update C_Invoice.IsCashVAT flag from the line tax rate.
- -- We do it this way to force any invoicing process to properly set the C_Invoice.IsCashVAT
- -- If a user manually changes the tax rate when entering a line, the invoice's header IsCash VAT may change
- IF (INSERTING OR UPDATING) THEN
-  IF (:NEW.C_TAX_ID IS NOT NULL) THEN
-   SELECT CASE WHEN COUNT(*) > 0 THEN 'Y' ELSE 'N' END
-   INTO v_IsCashVAT_Tax
-   FROM dual
-   WHERE EXISTS (
-        select 1
-        from c_tax t
-        where C_TAX_ISMEMBER(:NEW.C_Tax_ID, t.c_tax_id) = 1
-        and t.isCashVAT = 'Y'
-        and t.rate <> 0
-        and t.isSummary = 'N'
-                 );
-
-   SELECT COALESCE(IsCashVAT, 'N')
-   INTO v_IsCashVAT_Invoice
-   FROM C_Invoice
-   WHERE C_Invoice_ID=:NEW.C_Invoice_ID;
-
-   IF (v_IsCashVAT_Tax <> v_IsCashVAT_Invoice) THEN
-     UPDATE C_Invoice
-     SET IsCashVAT = v_IsCashVAT_Tax
-     WHERE C_Invoice_ID=:NEW.C_Invoice_ID;
-   END IF;
-  END IF;
- END IF;
-
-
 END C_INVOICELINE_TRG
 ]]></body>
     </trigger>
--- a/src-db/database/model/triggers/C_INVOICETAX_TRG.xml	Thu Apr 14 17:49:21 2016 +0200
+++ b/src-db/database/model/triggers/C_INVOICETAX_TRG.xml	Fri Apr 08 14:51:17 2016 +0530
@@ -17,7 +17,7 @@
     * parts created by ComPiere are Copyright (C) ComPiere, Inc.;
     * All Rights Reserved.
     * Contributor(s): Openbravo SLU
-    * Contributions are Copyright (C) 2001-2012 Openbravo, S.L.U.
+    * Contributions are Copyright (C) 2001-2016 Openbravo, S.L.U.
     *
     * Specifically, this derivative work is based upon the following Compiere
     * file and version.
@@ -30,7 +30,10 @@
   v_ID VARCHAR2(32);
   v_RO        NUMBER;
   v_Processed VARCHAR(60) ;
-    
+  v_IsCashVAT_Tax C_Tax.IsCashVAT%TYPE;
+  v_IsCashVAT_Invoice C_Invoice.IsCashVAT%TYPE;
+  v_IsWithholding_Tax C_Tax.IsWithholdingTax%TYPE;
+  v_Rate_Tax C_Tax.Rate%TYPE;
 BEGIN
     
     IF AD_isTriggerEnabled()='N' THEN RETURN;
@@ -67,6 +70,30 @@
   IF(v_RO > 0) THEN
     RAISE_APPLICATION_ERROR(-20000, '@20501@') ;
   END IF;
+
+ -- Update C_Invoice.IsCashVAT flag from the line tax rate.
+ -- We do it this way to force any invoicing process to properly set the C_Invoice.IsCashVAT
+ -- If a user manually changes the tax rate when entering a line, the invoice's header IsCash VAT may change
+  IF (INSERTING OR UPDATING) THEN
+    IF (:NEW.C_TAX_ID IS NOT NULL) THEN
+      SELECT COALESCE(iswithholdingtax, 'N'), COALESCE(rate, 0), COALESCE(iscashvat,'N')
+      INTO v_IsWithholding_Tax, v_Rate_Tax, v_IsCashVAT_Tax
+      FROM C_Tax
+      WHERE C_Tax_ID=:NEW.C_Tax_ID;
+
+      IF(v_IsWithholding_Tax='N' AND v_Rate_Tax<>0) THEN
+        SELECT COALESCE(IsCashVAT, 'N')
+        INTO v_IsCashVAT_Invoice
+        FROM C_Invoice
+        WHERE C_Invoice_ID=:NEW.C_Invoice_ID;
+        IF (v_IsCashVAT_Tax <> v_IsCashVAT_Invoice) THEN
+          UPDATE C_Invoice
+          SET IsCashVAT = v_IsCashVAT_Tax
+          WHERE C_Invoice_ID=:NEW.C_Invoice_ID;
+        END IF;
+      END IF;
+    END IF;
+  END IF;
 END C_INVOICETAX_TRG
 ]]></body>
     </trigger>
--- a/src-db/database/model/triggers/C_ORDERLINE_TRG.xml	Thu Apr 14 17:49:21 2016 +0200
+++ b/src-db/database/model/triggers/C_ORDERLINE_TRG.xml	Fri Apr 08 14:51:17 2016 +0530
@@ -17,7 +17,7 @@
   * parts created by ComPiere are Copyright (C) ComPiere, Inc.;
   * All Rights Reserved.
   * Contributor(s): Openbravo SLU
-  * Contributions are Copyright (C) 2001-2015 Openbravo, S.L.U.
+  * Contributions are Copyright (C) 2001-2016 Openbravo, S.L.U.
   *
   * Specifically, this derivative work is based upon the following Compiere
   * file and version.
@@ -32,8 +32,6 @@
   v_CountRelations NUMBER;
   v_reservation_id    VARCHAR2(32);
   v_prereservedqty    NUMBER;
-  v_IsCashVAT_Tax C_Tax.IsCashVAT%TYPE;
-  v_IsCashVAT_Order C_Order.IsCashVAT%TYPE;
   v_istaxincluded CHAR(1);
   v_PriceActual NUMBER;
   
@@ -52,7 +50,8 @@
   v_CalcLine NUMBER;
           
   TYPE RECORD IS REF CURSOR;
-  Cur_BOM RECORD;         
+  Cur_BOM RECORD;
+
 BEGIN
     
     IF AD_isTriggerEnabled()='N' THEN RETURN;
@@ -253,36 +252,6 @@
   :new.ChargeAmt := ROUND(:new.ChargeAmt, v_Prec);  
  END IF;
 
- -- Update C_Order.IsCashVAT flag from the line tax rate.
- -- We do it this way to force any process that creates Orders to properly set the C_Order.IsCashVAT
- -- If a user manually changes the tax rate when entering a line, the order's header IsCash VAT may change
- IF (INSERTING OR UPDATING) THEN
-  IF (:NEW.C_TAX_ID IS NOT NULL) THEN  
-   SELECT CASE WHEN COUNT(*) > 0 THEN 'Y' ELSE 'N' END
-   INTO v_IsCashVAT_Tax
-   FROM dual
-   WHERE EXISTS (
-        select 1
-        from c_tax t
-        where C_TAX_ISMEMBER(:NEW.C_Tax_ID, t.c_tax_id) = 1
-        and t.isCashVAT = 'Y'
-        and t.rate <> 0
-        and t.isSummary = 'N'
-                 );
-
-   SELECT COALESCE(IsCashVAT, 'N')
-   INTO v_IsCashVAT_Order
-   FROM C_Order
-   WHERE C_Order_ID=:NEW.C_Order_ID;
-
-   IF (v_IsCashVAT_Tax <> v_IsCashVAT_Order) THEN
-     UPDATE C_Order
-     SET IsCashVAT = v_IsCashVAT_Tax
-     WHERE C_Order_ID=:NEW.C_Order_ID;
-   END IF;
-  END IF;
- END IF;
-
 END C_ORDERLINE_TRG
 ]]></body>
     </trigger>
--- a/src-db/database/model/triggers/C_ORDERTAX_TRG.xml	Thu Apr 14 17:49:21 2016 +0200
+++ b/src-db/database/model/triggers/C_ORDERTAX_TRG.xml	Fri Apr 08 14:51:17 2016 +0530
@@ -17,7 +17,7 @@
     * parts created by ComPiere are Copyright (C) ComPiere, Inc.;
     * All Rights Reserved.
     * Contributor(s): Openbravo SLU
-    * Contributions are Copyright (C) 2001-2012 Openbravo, S.L.U.
+    * Contributions are Copyright (C) 2001-2016 Openbravo, S.L.U.
     *
     * Specifically, this derivative work is based upon the following Compiere
     * file and version.
@@ -32,6 +32,10 @@
   v_Processed  VARCHAR(60) ;
   v_C_ORDER_ID VARCHAR2(32) ;
   v_Docaction VARCHAR(60) ;
+  v_IsWithholding_Tax C_Tax.IsWithholdingTax%TYPE;
+  v_Rate_Tax C_Tax.Rate%TYPE;
+  v_IsCashVAT_Tax C_Tax.IsCashVAT%TYPE;
+  v_IsCashVAT_Order C_Order.IsCashVAT%TYPE;
     
 BEGIN
     
@@ -82,6 +86,31 @@
   RAISE_APPLICATION_ERROR(-20000, '@20501@');
   END IF;
   **/
+ -- Update C_Order.IsCashVAT flag from the line tax rate.
+ -- We do it this way to force any process that creates Orders to properly set the C_Order.IsCashVAT
+ -- If a user manually changes the tax rate when entering a line, the order's header IsCash VAT may change
+ IF (INSERTING OR UPDATING) THEN
+   IF (:NEW.C_TAX_ID IS NOT NULL) THEN
+     SELECT COALESCE(iswithholdingtax, 'N'), COALESCE(rate, 0), COALESCE(IsCashVat, 'N')
+     INTO v_IsWithholding_Tax, v_Rate_Tax, v_IsCashVAT_Tax
+     FROM C_Tax
+     WHERE C_Tax_ID=:NEW.C_Tax_ID;
+
+     IF (v_IsWithholding_Tax='N' AND v_Rate_Tax<>0) THEN
+       SELECT COALESCE(IsCashVAT, 'N')
+       INTO v_IsCashVAT_Order
+       FROM C_Order
+       WHERE C_Order_ID=:NEW.C_Order_ID;
+
+       IF (v_IsCashVAT_Tax <> v_IsCashVAT_Order) THEN
+         UPDATE C_Order
+         SET IsCashVAT = v_IsCashVAT_Tax
+         WHERE C_Order_ID=:NEW.C_Order_ID;
+       END IF;
+     END IF;
+   END IF;
+ END IF;
+
 END C_ORDERTAX_TRG
 ]]></body>
     </trigger>