Region Based Tax is not Calculating Properly

[VipulAniya]

At product cart page, we are getting tax value which implies for multiple rule.
For example :-- If users has create tax rule for any specific country as well as its region also then if user select that region at cart page, it implies both rule like region based and country based for that product.

It should implies only for region based. Magento must have provide the priority to region then country.

Preconditions

Magento 2.2.2 Community Edition.

Steps to reproduce

1. Login to admin panel.
2. Use system configuration for Tax.
3. Create individual tax rate and tax rule for specif region and country.
4. Add any product into cart.
5. Go to cart page.
6. Select any region and verify that country specific rule is applied or not.
7. Now, update with region which you had created rule.
8. Verify that tax calculate for both rate region and country or not.

Expected result

1. If specific region is selected and associate rule is created then tax should calculate only for region.
2. If specific region based rule is not created and rule is created for country then tax should calculate only for country.
3. If rules are created for both specific region and country and user select specific region for shipping then tax should calculate only for specific region instead of total of both rates.

Actual result

1. Currently, we are getting multiple tax value by affecting multiple rule. Refer screen shots.
   
   
   
   
   

[magento-engcom-team]

@VipulAniya, thank you for your report.
We've acknowledged the issue and added to our backlog.

[stephan-cream]

I’m working on it #distributed-cd

[julian-van-drielen]

Basically when someone adds multiple rules for the same country it starts adding up these rules.
Example:
US-(region)-(postcode)-9%
US-Canada-*(postcode)-10%
US-Canada-90000-5%
This ends up resulting in 24% being calculated.
In reality there are cases where you would like the option to skip other rules from applying and only applying for example US-Canada-90000-5% rule if you have entered the US country, Canada region and 90000 as postcode.
Or am i seeing this wrong?

Long story short, shouldn’t the same logic be applied for tax calculation rules as with cart price rules where you have the option to skip any further processing and using the one that is the most applicable, which in this case would be US-Canada-90000-5%?

[stephan-cream]

app/code/Magento/Tax/Model/ResourceModel/Calculation.php
getRateInfo() function: This calls the 2 functions below to calculate the tax rates
_getrates() function: The generated select query on this is as follows:
( SELECT main_table.tax_calculation_rate_id, main_table.tax_calculation_rule_id, main_table.customer_tax_class_id, main_table.product_tax_class_id, rule.priority, rule.position, rule.calculate_subtotal, rate.rate AS value, rate.tax_country_id, rate.tax_region_id, rate.tax_postcode, rate.tax_calculation_rate_id, rate.code, IF(title_table.value IS NULL, rate.code, title_table.value) AS title FROM tax_calculation AS main_table INNER JOIN tax_calculation_rule AS rule ON rule.tax_calculation_rule_id = main_table.tax_calculation_rule_id INNER JOIN tax_calculation_rate AS rate ON rate.tax_calculation_rate_id = main_table.tax_calculation_rate_id LEFT JOIN tax_calculation_rate_title AS title_table ON rate.tax_calculation_rate_id = title_table.tax_calculation_rate_id AND title_table.store_id = '1' WHERE (customer_tax_class_id = 3) AND (product_tax_class_id IN ('2')) AND (rate.tax_country_id = 'US') AND (rate.tax_region_id IN(0, 12)) AND (rate.zip_is_range IS NULL) AND (rate.tax_postcode IS NULL OR rate.tax_postcode IN('*', '', '1900', '1900*', '190*', '19*', '1*') ) ) UNION ( SELECT main_table.tax_calculation_rate_id, main_table.tax_calculation_rule_id, main_table.customer_tax_class_id, main_table.product_tax_class_id, rule.priority, rule.position, rule.calculate_subtotal, rate.rate AS value, rate.tax_country_id, rate.tax_region_id, rate.tax_postcode, rate.tax_calculation_rate_id, rate.code, IF(title_table.value IS NULL, rate.code, title_table.value) AS title FROM tax_calculation AS main_table INNER JOIN tax_calculation_rule AS rule ON rule.tax_calculation_rule_id = main_table.tax_calculation_rule_id INNER JOIN tax_calculation_rate AS rate ON rate.tax_calculation_rate_id = main_table.tax_calculation_rate_id LEFT JOIN tax_calculation_rate_title AS title_table ON rate.tax_calculation_rate_id = title_table.tax_calculation_rate_id AND title_table.store_id = '1' WHERE (customer_tax_class_id = 3) AND (product_tax_class_id IN ('2')) AND (rate.tax_country_id = 'US') AND (rate.tax_region_id IN(0, 12)) AND (rate.zip_is_range IS NOT NULL) AND ('1900' BETWEEN rate.zip_from AND rate.zip_to) ) ORDER BY priority ASC, tax_calculation_rule_id ASC, tax_country_id DESC, tax_region_id DESC, tax_postcode DESC, value DESC

This seems strange as it basically retrieves everything that is in the US and has * for region, * for postcode, while you have entered a specific postcode (in this case 1900).

You would expect it to first look for anything that is most specific that fits the entered data, and if applicable continue onwards with the next tax rules, otherwise only apply that first tax rule.
So in this case it would be:

• tax_class_id : 2
• tax_country_id : US
• tax_region_id : 12
• postcode : 1900
  If that doesn't exist it would be:
• tax_class_id : 2
• tax_country_id : US
• tax_region_id : 12
• postcode : 1900 within a range of postcodes (e.g. between 1800 and 2000)
  If that doesn't exist it would be:
• tax_class_id : 2
• tax_country_id : US
• tax_region_id : 12
• postcode : *
  And finally if that doesn't exist it should be:
• tax_class_id : 2
• tax_country_id : US
• tax_region_id : 0 (this means *)
• postcode : *

Currently there can be up to 4 tax rules that add up resulting in very awkward tax percentages.
Namely:
[any combination] US-(region)-(postcode)-9%
[specic with any postcode ][US-Canada-*(postcode)-10%
[between range] US-Canada-1800~2000-5%
[everything specific] US-Canada-1900-1%
This currently results in 25%.

@magento-engcom-team As this logic must have a reason i decided not to edit the code just yet and wanted to discuss if having a catalog price rule like functionality select whether to skip any further rules would be a good option in this case.

_calculateRate() function: This does the actual calculation.
In here it adds up every result with: $result += $currentrate while looping through all the rates from the previous query.
This needs to be altered as well if the above option i suggested would be acceptable.