details: https://code.openbravo.com/erp/devel/pi/rev/af56683ab963 changeset: 31637:af56683ab963 user: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com> date: Fri Mar 10 10:23:51 2017 +0100 summary: Fixes issue 35471: Order Line amounts and taxes not rounded after booking
M_PROMOTION_CALCULATE was run after rounding and adjusting the order, and it was updating the order lines, so triggers were run again calculating line net amount and taxes without rounding. Now, round, adjust and recalculate will be done at the end of c_order_post and c_invoice_post, just before finish process extension point. Also, line net amount will always be rounded to standard precision to avoid problems. In case we need to calculate taxes without rounding (price including taxes and taxes at document level), we will calculate again the line net amount in line after trigger to use it for calculating taxes at line level and document level without rounding. details: https://code.openbravo.com/erp/devel/pi/rev/165521c4ee6e changeset: 31638:165521c4ee6e user: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com> date: Thu Mar 09 17:47:15 2017 +0100 summary: Related to issue 35471: Do not round line net amount in TaxesTest diffstat: src-db/database/model/functions/C_INVOICETAX_ADJUSTMENT.xml | 185 ++++++++++++ src-db/database/model/functions/C_INVOICE_POST.xml | 174 +---------- src-db/database/model/functions/C_ORDERTAX_ADJUSTMENT.xml | 179 +++++++++++ src-db/database/model/functions/C_ORDER_POST1.xml | 156 +--------- src-db/database/model/triggers/C_INVOICELINE_BEFORE_TRG.xml | 12 +- src-db/database/model/triggers/C_INVOICELINE_TRG2.xml | 8 +- src-db/database/model/triggers/C_ORDERLINE_TRG.xml | 17 +- src-db/database/model/triggers/C_ORDERLINE_TRG2.xml | 8 +- src-test/src/org/openbravo/test/taxes/TaxesTest.java | 10 +- 9 files changed, 405 insertions(+), 344 deletions(-) diffs (truncated from 936 to 300 lines): diff -r 1eb8d1da9bb9 -r 165521c4ee6e src-db/database/model/functions/C_INVOICETAX_ADJUSTMENT.xml --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src-db/database/model/functions/C_INVOICETAX_ADJUSTMENT.xml Thu Mar 09 17:47:15 2017 +0100 @@ -0,0 +1,185 @@ +<?xml version="1.0"?> + <database name="FUNCTION C_INVOICETAX_ADJUSTMENT"> + <function name="C_INVOICETAX_ADJUSTMENT" type="NULL"> + <parameter name="p_invoice_id" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_precision" type="NUMERIC" mode="in"> + <default/> + </parameter> + <parameter name="p_action" type="VARCHAR" mode="in"> + <default/> + </parameter> + <body><![CDATA[/************************************************************************* +* The contents of this file are subject to the Openbravo Public License +* Version 1.1 (the "License"), being the Mozilla Public License +* Version 1.1 with a permitted attribution clause; you may not use this +* file except in compliance with the License. You may obtain a copy of +* the License at http://www.openbravo.com/legal/license.html +* Software distributed under the License is distributed on an "AS IS" +* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the +* License for the specific language governing rights and limitations +* under the License. +* The Original Code is Openbravo ERP. +* The Initial Developer of the Original Code is Openbravo SLU +* All portions are Copyright (C) 2017 Openbravo SLU +* All Rights Reserved. +* Contributor(s): ______________________________________. +************************************************************************/ +-- Variables +v_taxAmt C_INVOICETAX.TaxAmt%TYPE; +v_amount C_ORDERLINE.Line_Gross_Amount%TYPE; +TYPE RECORD IS REF CURSOR; +CUR_Tax RECORD; +CUR_Line RECORD; + +BEGIN --BODY + + -- Round and Adjust taxes + IF (p_action = 'CO') THEN + + -- Disable triggers + INSERT INTO AD_Session_Status (ad_session_status_id, ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby, isimporting) + VALUES (get_uuid(), '0', '0', 'Y', now(), '0', now(), '0', 'Y'); + + -- Round + UPDATE C_INVOICELINETAX + SET TaxBaseAmt = round(TaxBaseAmt, p_precision) + WHERE C_Invoice_ID = p_invoice_id; + + UPDATE C_INVOICETAX + SET TaxBaseAmt = round(TaxBaseAmt, p_precision) + WHERE C_Invoice_ID = p_invoice_id + AND Recalculate = 'Y'; + + -- Adjust + FOR CUR_Tax IN ( + SELECT it.C_Tax_ID, COALESCE((MIN(it.DocTaxBaseAmt) + MIN(it.DocTaxAmt)) - (SUM(ilt.LineTaxBaseAmt) + SUM(ilt.LineTaxAmt)), 0) as TaxAdjustment, COALESCE(MIN(it.DocTaxBaseAmt) - SUM(ilt.LineTaxBaseAmt), 0) as LineAdjustment + FROM ( + SELECT c_tax_get_root(it.c_tax_id) as C_Tax_ID, CASE WHEN MIN(it.TaxBaseAmt) > 0 THEN MIN(it.TaxBaseAmt) ELSE MAX(it.TaxBaseAmt) END as DocTaxBaseAmt, SUM(it.TaxAmt) as DocTaxAmt, MIN(it.Line) as Line + FROM C_InvoiceTax it + JOIN C_Tax t + ON it.C_Tax_ID = t.C_Tax_ID + WHERE it.C_Invoice_ID = p_invoice_id + AND it.Recalculate = 'Y' + AND t.DocTaxAmount = 'D' + GROUP BY c_tax_get_root(it.c_tax_id) + ) it + JOIN ( + SELECT c_tax_get_root(ilt.c_tax_id) as C_Tax_ID, CASE WHEN MIN(ilt.TaxBaseAmt) > 0 THEN MIN(ilt.TaxBaseAmt) ELSE MAX(ilt.TaxBaseAmt) END as LineTaxBaseAmt, SUM(ilt.TaxAmt) as LineTaxAmt + FROM C_InvoiceLineTax ilt + JOIN C_Tax t + ON ilt.C_Tax_ID = t.C_Tax_ID + WHERE ilt.C_Invoice_ID = p_invoice_id + AND t.DocTaxAmount = 'D' + GROUP BY ilt.C_InvoiceLine_ID, c_tax_get_root(ilt.c_tax_id) + ) ilt + ON ilt.C_Tax_ID = it.C_Tax_ID + GROUP BY it.C_Tax_ID, it.Line + HAVING COALESCE(MIN(it.DocTaxBaseAmt), 0) <> COALESCE(SUM(ilt.LineTaxBaseAmt), 0) + OR COALESCE(MIN(it.DocTaxBaseAmt) + MIN(it.DocTaxAmt), 0) <> COALESCE(SUM(ilt.LineTaxBaseAmt) + SUM(ilt.LineTaxAmt), 0) + ORDER BY it.Line + ) + LOOP + -- Adjust TaxAmt + IF (CUR_Tax.TaxAdjustment <> 0) THEN + FOR CUR_Line IN ( + SELECT it.C_InvoiceTax_ID, it.C_Tax_ID, t.Line + FROM C_InvoiceTax it + JOIN C_Tax t + ON it.C_Tax_ID = t.C_Tax_ID + WHERE it.C_Invoice_ID = p_invoice_id + AND it.Recalculate = 'Y' + AND c_tax_get_root(it.C_Tax_ID) = CUR_Tax.C_Tax_ID + ORDER BY ABS(it.TaxAmt) DESC, t.Line DESC + ) + LOOP + UPDATE C_INVOICETAX + SET TaxAmt = TaxAmt - CUR_Tax.TaxAdjustment + WHERE C_InvoiceTax_ID = CUR_Line.C_InvoiceTax_ID; + + UPDATE C_INVOICETAX it + SET TaxBaseAmt = TaxBaseAmt - CUR_Tax.TaxAdjustment + WHERE it.C_Invoice_ID = p_invoice_id + AND it.Recalculate = 'Y' + AND c_tax_get_root(it.C_Tax_ID) = CUR_Tax.C_Tax_ID + AND EXISTS ( + SELECT 1 + FROM C_Tax t + WHERE t.C_Tax_ID = it.C_Tax_ID + AND ((t.Cascade = 'Y' + AND t.Line > CUR_Line.Line) + OR (t.BaseAmount IN ('TAX', 'LNATAX', 'TBATAX') + AND C_TAX_ISMEMBER(t.C_TaxBase_ID, CUR_Line.C_Tax_ID) = 1 + AND t.IsSummary = 'N')) + ); + + EXIT; + END LOOP; + END IF; + + -- Adjust LineNetAmt + IF (CUR_Tax.LineAdjustment <> 0) THEN + FOR CUR_Line IN ( + SELECT il.C_InvoiceLine_ID + FROM C_InvoiceLine il + WHERE il.C_Invoice_ID = p_invoice_id + AND EXISTS ( + SELECT 1 + FROM C_InvoiceLineTax ilt + WHERE il.C_InvoiceLine_ID = ilt.C_InvoiceLine_ID + AND c_tax_get_root(ilt.C_Tax_ID) = CUR_Tax.c_Tax_ID + ) + ORDER BY ABS(il.LineNetAmt) DESC, il.Line DESC + ) + LOOP + UPDATE C_INVOICELINE + SET LineNetAmt = LineNetAmt + CUR_Tax.LineAdjustment + WHERE C_InvoiceLine_ID = CUR_Line.C_InvoiceLine_ID; + + EXIT; + END LOOP; + END IF; + END LOOP; + + -- Enable triggers + DELETE FROM AD_Session_Status + WHERE isimporting = 'Y'; + + -- Recalculate amounts and taxes + ELSIF (p_action = 'RE') THEN + + -- Remove header amounts and taxes + DELETE FROM C_INVOICETAX WHERE C_Invoice_ID = p_invoice_id AND Recalculate = 'Y'; + SELECT COALESCE(SUM(TaxAmt), 0) INTO v_taxAmt FROM C_INVOICETAX WHERE C_Invoice_ID = p_invoice_id AND Recalculate = 'N'; + UPDATE C_INVOICE SET TotalLines = 0, GrandTotal = v_taxAmt WHERE C_Invoice_ID = p_invoice_id; + + FOR Cur_line IN ( + SELECT C_InvoiceLine_ID + FROM C_INVOICELINE + WHERE C_Invoice_ID = p_invoice_id + ) LOOP + + -- Disable triggers + INSERT INTO AD_Session_Status (ad_session_status_id, ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby, isimporting) + VALUES (get_uuid(), '0', '0', 'Y', now(), '0', now(), '0', 'Y'); + + -- Remove line amounts and taxes + DELETE FROM C_INVOICELINETAX WHERE C_InvoiceLine_ID = Cur_line.C_InvoiceLine_ID; + SELECT Line_Gross_Amount INTO v_amount FROM C_INVOICELINE WHERE C_InvoiceLine_ID = Cur_line.C_InvoiceLine_ID; + UPDATE C_INVOICELINE SET Line_Gross_Amount = 0 WHERE C_InvoiceLine_ID = Cur_line.C_InvoiceLine_ID; + + -- Enable triggers + DELETE FROM AD_Session_Status + WHERE isimporting = 'Y'; + + -- Recalculate amounts and taxes + UPDATE C_INVOICELINE SET Line_Gross_Amount = v_amount WHERE C_InvoiceLine_ID = Cur_line.C_InvoiceLine_ID; + + END LOOP; + + END IF; +END C_INVOICETAX_ADJUSTMENT +]]></body> + </function> + </database> diff -r 1eb8d1da9bb9 -r 165521c4ee6e src-db/database/model/functions/C_INVOICE_POST.xml --- a/src-db/database/model/functions/C_INVOICE_POST.xml Fri Mar 10 10:15:36 2017 +0100 +++ b/src-db/database/model/functions/C_INVOICE_POST.xml Thu Mar 09 17:47:15 2017 +0100 @@ -60,7 +60,6 @@ Cur_lineqty RECORD; Cur_InvoiceTax RECORD; Cur_Offer RECORD; - Cur_Tax RECORD; -- Record Info v_Client_ID VARCHAR2(32); @@ -157,7 +156,6 @@ 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; @@ -225,132 +223,11 @@ ON i.M_PRICELIST_ID = p.M_PRICELIST_ID WHERE i.C_INVOICE_ID = v_Record_ID; - /************************************************************************** - * Round amounts and taxes - *************************************************************************/ - IF (v_DocAction = 'CO' AND v_isTaxIncluded = 'Y') THEN - - -- Disable triggers - INSERT INTO AD_Session_Status (ad_session_status_id, ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby, isimporting) - VALUES (get_uuid(), '0', '0', 'Y', now(), '0', now(), '0', 'Y'); - - -- Round - UPDATE C_INVOICELINE - SET LineNetAmt = round(LineNetAmt, v_stdPrecision) - WHERE C_Invoice_ID = v_Record_ID; - - UPDATE C_INVOICELINETAX - SET TaxBaseAmt = round(TaxBaseAmt, v_stdPrecision) - WHERE C_Invoice_ID = v_Record_ID; - - UPDATE C_INVOICETAX - SET TaxBaseAmt = round(TaxBaseAmt, v_stdPrecision) - WHERE C_Invoice_ID = v_Record_ID - AND Recalculate = 'Y'; - - -- Adjust - FOR CUR_Tax IN ( - SELECT it.C_Tax_ID, COALESCE((MIN(it.DocTaxBaseAmt) + MIN(it.DocTaxAmt)) - (SUM(ilt.LineTaxBaseAmt) + SUM(ilt.LineTaxAmt)), 0) as TaxAdjustment, COALESCE(MIN(it.DocTaxBaseAmt) - SUM(ilt.LineTaxBaseAmt), 0) as LineAdjustment - FROM ( - SELECT c_tax_get_root(it.c_tax_id) as C_Tax_ID, CASE WHEN MIN(it.TaxBaseAmt) > 0 THEN MIN(it.TaxBaseAmt) ELSE MAX(it.TaxBaseAmt) END as DocTaxBaseAmt, SUM(it.TaxAmt) as DocTaxAmt, MIN(it.Line) as Line - FROM C_InvoiceTax it - JOIN C_Tax t - ON it.C_Tax_ID = t.C_Tax_ID - WHERE it.C_Invoice_ID = v_Record_ID - AND it.Recalculate = 'Y' - AND t.DocTaxAmount = 'D' - GROUP BY c_tax_get_root(it.c_tax_id) - ) it - JOIN ( - SELECT c_tax_get_root(ilt.c_tax_id) as C_Tax_ID, CASE WHEN MIN(ilt.TaxBaseAmt) > 0 THEN MIN(ilt.TaxBaseAmt) ELSE MAX(ilt.TaxBaseAmt) END as LineTaxBaseAmt, SUM(ilt.TaxAmt) as LineTaxAmt - FROM C_InvoiceLineTax ilt - JOIN C_Tax t - ON ilt.C_Tax_ID = t.C_Tax_ID - WHERE ilt.C_Invoice_ID = v_Record_ID - AND t.DocTaxAmount = 'D' - GROUP BY ilt.C_InvoiceLine_ID, c_tax_get_root(ilt.c_tax_id) - ) ilt - ON ilt.C_Tax_ID = it.C_Tax_ID - GROUP BY it.C_Tax_ID, it.Line - HAVING COALESCE(MIN(it.DocTaxBaseAmt), 0) <> COALESCE(SUM(ilt.LineTaxBaseAmt), 0) - OR COALESCE(MIN(it.DocTaxBaseAmt) + MIN(it.DocTaxAmt), 0) <> COALESCE(SUM(ilt.LineTaxBaseAmt) + SUM(ilt.LineTaxAmt), 0) - ORDER BY it.Line - ) - LOOP - -- Adjust TaxAmt - IF (CUR_Tax.TaxAdjustment <> 0) THEN - FOR CUR_Line IN ( - SELECT it.C_InvoiceTax_ID, it.C_Tax_ID, t.Line - FROM C_InvoiceTax it - JOIN C_Tax t - ON it.C_Tax_ID = t.C_Tax_ID - WHERE it.C_Invoice_ID = v_Record_ID - AND it.Recalculate = 'Y' - AND c_tax_get_root(it.C_Tax_ID) = CUR_Tax.C_Tax_ID - ORDER BY ABS(it.TaxAmt) DESC, t.Line DESC - ) - LOOP - UPDATE C_INVOICETAX - SET TaxAmt = TaxAmt - CUR_Tax.TaxAdjustment - WHERE C_InvoiceTax_ID = CUR_Line.C_InvoiceTax_ID; - - UPDATE C_INVOICETAX it - SET TaxBaseAmt = TaxBaseAmt - CUR_Tax.TaxAdjustment - WHERE it.C_Invoice_ID = v_Record_ID - AND it.Recalculate = 'Y' - AND c_tax_get_root(it.C_Tax_ID) = CUR_Tax.C_Tax_ID - AND EXISTS ( - SELECT 1 - FROM C_Tax t - WHERE t.C_Tax_ID = it.C_Tax_ID - AND ((t.Cascade = 'Y' - AND t.Line > CUR_Line.Line) - OR (t.BaseAmount IN ('TAX', 'LNATAX', 'TBATAX') - AND C_TAX_ISMEMBER(t.C_TaxBase_ID, CUR_Line.C_Tax_ID) = 1 - AND t.IsSummary = 'N')) - ); - - EXIT; - END LOOP; - END IF; ------------------------------------------------------------------------------ Announcing the Oxford Dictionaries API! The API offers world-renowned dictionary content that is easy and intuitive to access. Sign up for an account today to start using our lexical data to power your apps and projects. Get started today and enter our developer competition. http://sdm.link/oxford _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
