Sponsored Development: Libero Global Tax Management
From ADempiere
This Wiki is read-only for reference purposes to avoid broken links.
Contents
Proposal
Current the tax functionality is very limited. The goal is to create new functionality that let configure any tax in any country. This is an effort under the Libero project of ADempiere spearheaded by e-Evolution and supported by the ADempiere Bazaar.
Project Team
Coordinator
Functional Specs
- New Tax Group Window, this window allows the user allocate a tax group to business partner as same way that a tax category is defined in a product.
- New Tax Type Window, this window allows the user to define a tax type and can be to used in a tax definition, for example VAT tax.
- New Tax Base, this window allows the user to define the tax base such as the price, cost, percentage or weight of the product.
- New Rate Parent,this window allows the user to define hierarchical taxes.
- New Definition Tax, this window allows the user to define a tax combination necessary to any country, so you can create a new callout to determinate what is the hierarchical tax you can use to calculate the tax.
Developers
Testers
- everybody is invited to participate
- Michael Judd
Sponsors
Requirements
Screenshot
Tax Group
Tax Type
Tax Base
Tax Rate Parent
Tax Definition
Tax Rate
- You can see now a new field called Rule, this let you can define a rule to calculate a dynamic rate.
Business Partner
- You can see now a new field called Tax Group , this let you can grouping the business partner with reference to the tax
How create a new Logical Tax Calculation ?
- Create Tax with the right rate that you want use
- Create a new record in Tax Definition with you Tax definition
- Entry as System and Table and Column Window (e.g. C_OrderLine)
- Create new Table Script Validator with Event Model Validator (Table After Change)
- Create new JSR 223 Scripting APIs Rule (groovy:TaxDefinition):
Example Script:
import java.math.*;
import java.sql.*;
import java.util.*;
import java.util.logging.Level;
import org.compiere.util.*;
import org.compiere.model.*;
//
Integer reg = (Integer)A_Value;
String IsSOTrx = Env.getContext(A_Ctx, "IsSOTrx");
//
if (reg == null || reg.intValue() == 0 || IsSOTrx.compareTo("Y") > 0 ) // No sales document
return "";
//
int AD_Org_ID,C_BPartner_ID,M_Product_ID,C_Charge_ID,AD_OrgType_ID,C_BP_Group_ID,GL_TaxGroup_ID,tax;
AD_Org_ID = Env.getContextAsInt(A_Ctx, "AD_Org_ID");
C_BPartner_ID = Env.getContextAsInt(A_Ctx, "C_BPartner_ID");
C_Charge_ID = Env.getContextAsInt(A_Ctx,"C_Charge_ID");
//
AD_OrgType_ID = DB.getSQLValue("OrgType", "SELECT AD_OrgType_ID FROM AD_OrgInfo WHERE AD_Org_ID="+AD_Org_ID);
MBPartner bp = new MBPartner(A_Ctx,C_BPartner_ID,null);
M_Product_ID = Env.getContextAsInt(A_Ctx, "M_Product_ID");
MCharge charge = new MCharge(A_Ctx,C_Charge_ID,null);
//
C_BP_Group_ID = bp.getC_BP_Group_ID();
GL_TaxGroup_ID = bp.getGT_TaxGroup_ID();
//
String sql = "SELECT C_Tax_ID FROM GT_TaxDefinition WHERE AD_OrgType_ID =" +AD_OrgType_ID
+ " AND (C_BPartner_ID =" +C_BPartner_ID+ " OR C_BPartner_ID IS NULL)" // optional
+ " AND (C_BP_Group_ID =" +C_BP_Group_ID+ " OR C_BP_Group_ID IS NULL)" // optional
+ " AND GT_TaxGroup_ID =" +GL_TaxGroup_ID; // necess
//
if(M_Product_ID > 0) // productos
{
MProduct product = new MProduct(ctx,M_Product_ID,null);
sql = sql + " AND (M_Product_ID =" + M_Product_ID + " OR M_Product_ID IS NULL)" // optional
+ " AND (M_Product_Category_ID =" + product.getM_Product_Category_ID()
+ " OR M_Product_Category_ID IS NULL)" // optional
+ " AND C_TaxCategory_ID = " + product.getC_TaxCategory_ID(); // necess
}
else // cargos
{
sql = sql + " AND (C_Charge_ID = " + C_Charge_ID + " OR C_Charge_ID IS NULL) AND C_TaxCategory_ID = " + charge.getC_TaxCategory_ID();
}
tax = DB.getSQLValue("Tax", sql + " ORDER BY SeqNo");
if ( tax < 1 )
result "There is no valid combination";
A_Tab.setValue("C_Tax_ID", tax);
return "";
Requirements
Victor, Mike J and Paul A have been discussing requirements for the GTM. Here is a summary of some of the requirements:
- The tax calculation needs a number of dimensions
- from / to tax zone - where a tax zone consists of one or many countries, regions or zip codes
- a time dimension - i.e. a tax definition for a specific date may change each year according to a government budget - so we need to have a "from date" so that tax definitions can be updated over time
- a tax base definition - this can be a complex calculation/script to calculate the base on which a tax is levied. This may include not only the price, but the cost (which cost???), the weight or the quantity of the line item
- a tax rate / calculation - this can be a complex calculation / script applied to the tax base to calculate the tax applicable to a line or document
- the tax definitions should have a sequence number so that multiple taxes can be defined, and it should be possible to have compound and non-compound taxes such that you can decide if you calculate a tax on a tax and in what order the calculation should occur.
- the document type - the tax calculation may be levied on different documents - for example an order (sales / purchase), invoice (sales, purchase), shipment (in / out)
- the tax definition should also include product, product group, business partner, business partner group as optional dimensions in the definition
Testing
- I created basic implementations of MTaxDefinition and MTaxBase for use in my script
- The design pattern for tax is still a bit of a problem I think. The TaxDefinition allows you to derive a TaxRate (C_Tax) and the TaxRate can be different for multiple from/to combinations. However, the TaxBase cannot be different and I understand the tax base may vary from country to country. In this case, the user would have to create multiple taxes to accommodate the different tax bases. I think it would be better to have the tax base and tax rate in the same table i.e. C_Tax
- Percentage in C_TaxBase is an int but probably should be a BigDecimal.
- Max & Min Taxable in C_TaxDefinition should be a BigDecimal.
- Tax Base should be in Tax Rate rather than Tax Definition - this allows it to be inherited in parent taxes.
- Is a tax rate is summary - then we should disable the tax rate, parent (unless we want embedded parents - too complex?) and rule
- The Tax does not recognise economic areas only country/country. This is an issue for the EU as it means you need to create 702 combinations to cater for all scenarios for each "cross border" tax. Perhaps we could make this simpler by having a sub table of C_Tax where you could define rules for domestic (from/to = same country) - the user chooses the from country, EA (from/to within an economic area) - the user chooses the economic area - the economic areas would have to be a new table which allows the user to add countries / regions, or international - which is similar to the current situation - where the user can select a from country/economic area and a to country/economic area. Each of these should have a Tax Base and Tax Rate.
- Is it your plan to wire all taxes to this in the future?
- The tax base could use a "Fixed" amount added to Price, Cost, Weight and Qty.
- Which 'Cost' is the Tax Base going to retrieve? What about multiple schemas?
- If multiple Tax Definitions match a certain line/orider/invoice combination - then the example model validator can only store one c_tax_id in the order line - which is later used by the ordertax to determine the rate. The Tax definition provides a seqno so it seems anticipated that multiple matching conditions can be configured. So it appears that more work than configuring a callout will be required.
Tax Model
I am currently working on designing a better tax model.
An economic area can be the European Union, the United Kingdom, the United Union, the Euro Zone, the Basque Region etc. This reflects that laws are now made at both regional, national and between countries in 'blocks' and that each country, and region may be a member of one or a number of economic regions. e.g. Great Britain is in the EU but not the Euro Zone.
The concept of Tax Calculation - allows for the definition of a calculation (simple rate or fixed amount - or other more complex calculation defined in script) which can be applied to a tax base to calculate a tax.
The Tax base, can be defined from a TaxBaseType (product/charge price, cost, weight or quantity) and a calculation also in script.
Tax Definitions allow you to define combinations of Economic Area, Country and Region to calculate tax based on a tax base and a tax calculation. We probably also need to include product, product group, bpartner, bpartner group, minimum, maximum (relates to what?) as per the current tax definition. Is Compound allows you to take in to account lower taxes in the seqno that meet the same criteria to be calculated and added to the tax base before calculating the later tax.
I think we may be able to drop org type - but I will map this out separately as I review the organisation structure which I tihnk needs expanding too.
