Feature Specification

 

Feature Name: Enhanced Tax Internationalization

 

Overview

The existing Tax management system in xTuple 3.0 is designed to be flexible to the extent that it can handle a variety of tax schemes where tax rates and authorities vary from one product and ship to location to another. However, there are limitations in both the tax structure methodology and reporting that make reporting difficult outside the United States without customization. The goal of this specification is to add in enough flexibility in tax functionality so that most international tax scenarios can be handled without major customization to the software.

Functional Requirements

• It should be possible to associate unlimited tax codes to a taxable item.
• It should be possible for a the tax codes related to a taxable item to reference multiple tax authorities.
• It should be possible to define tax calculations that are calculated against the result of another tax calculation (i.e. taxes on taxes).
• It should be possible to define tax assignments that calculate tax on the cumulative sum of the base price item and multiple other taxes.
• Tax rates should have effective and expiration dates.
• Tax rates should be able to be defined as percentages or fixed currency amounts.
• Purchase Orders should support tax estimates at the line item level.
• Vouchers should support tax distributions at the line item level.
• Voucher line item tax distributions should default to purchase order estimates.
• Vouchers should support the ability to identify miscellaneous distributions as tax.
• A/R and A/P misc. credit memos should support tax distributions.
• Provide VAT compliant tax reporting that displays the net of taxes collected on sales and taxes paid on purchases for a given tax summary.
• Tax reporting should be able to be summarized by tax code, tax type, tax zone or tax authority.
• Tax report summaries should allow drilling down to source detail.
• Terminology should be made consistent with other like functionality in the system.

New Terms and Definitions

Cumulative Taxes
Taxes that are calculated against the sum of a base amount plus other taxes applied.
Nested Taxes
Taxes that are calculated against other taxes.
Tax Assignments
Tax assignments map tax zone and tax type combinations to tax codes. Formerly known as tax selections.
Tax Authority
An organization to which collected taxes will be paid. Formerly associated with customers, vendors, ship-to addresses and many document headers, tax authority will now be referenced on tax code assignments to allow for the possibility of multiple tax authorities being involved in a single transaction.
Tax Class
A list of user defined tax classes may be created that can be assigned to tax codes. It can be referenced as a grouping mechanism that can be used for structuring of cumulative taxes, but can also be used for reporting purposes such as summarized historical reporting and subtotaling on forms.
Tax Code
A single tax item that specifies a G/L account mapping, tax rate and optionally a tax class. Formerly tax code contained 3 fixed tax items A, B and C with a single percentage rate.
Tax Rate
A tax rate determines the amount of tax that a tax code collects. Tax rates will now have effective and expire dates such that multiple rates can be associated to one tax item. However, only one rate may be effective in any given date range.
Tax Zone
A geographic area that has specific tax jurisdiction and tax assignments. Tax assignments are assigned on a tax zone and tax type basis to determine what tax structure to apply to a given item. Tax zone will replace the position of tax authority in the tax structure on previous versions of xTuple.

Related Existing Functionality

The new functionality described in this document builds on existing functionality described in Tax Basics.

Similar and Related Requests

3075 Tax calculation needed on PO.
3148 Voucher ignors PO Freight/Tax.
5158 No tax on Misc. Charge.
8224 Purchase VAT report
8274 Add tax calculations to purchase orders.
8411, 8496, 8221 Rounding problems on tax calculations
7633, 4886, 4887 Add support for tax an misc debit and credit memos in A/P and A/R.

There is a forum posting on the topic of VAT handling here.

Conflicting Features

This specification will call for creating a one to many relationship between tax types and tax codes which is a major paradigm shift from the current hard coded 3 element A, B, C method. Tax codes will be altered so they may include one and only one G/L Account mapping. A large amount of historical data using the field based paradigm will have to be converted in sales orders, invoices, sales history and other related documents to complete this transition. In large databases this will likely require substantial upgrade time. In addition, a complete switchover to this methodology will likely "break" existing reports and forms users have customized that rely on existing tax calculations. These obstacles will make upgrading to the nex version of xTuple a substantial task for some users. In order to defray that effort it may be necessary to support the legacy structure for at least one version and provide a mechanism to convert data and report structures over extended period while still being able to use the system with the new method moving forward.

User-Level Functionality

The most glaring problem with the existing tax structure is that 3 tax elements are hard coded into every tax code, and only one tax code may be referenced in a tax authority/type mapping and subsequent transaction. This creates two problems: 1) It limits the number tax elements to 3 per transaction and 2) tax elements are not normalized. If there are two tax authorities that collect the same VAT but separate local taxes, two tax codes must be created each having a VAT component, but the VAT component is not shared between codes. So while the tax selections mapping system should ideally allow the flexibility to reduce the number of tax types and tax codes, the hard coding of the A, B, C fields all but ensures that tax codes will proliferate for every possible combination of tax, and that reporting on any specific element A, B, or C is both limited and fallible. The only way to ensure an accurate total for a given tax element is to rely on the G/L account mappings to record all transactions for a given tax. Consequently, there is no way to confidently report on supporting detail.
To solve this problem tax codes will be altered so that each code has one and only one G/L mapping. The existing A, B, C structure will be broken up so that it is record based instead of field based, so an existing tax code that had A, B, and C percentages will be converted to three distinct tax code records. Tax selection mappings will be renamed "tax assignments" to more closely follow existing naming conventions such sales assignments, A/R assignments and price schedule assignments. Tax assignments will allow the mapping of multiple tax codes to a tax authority and tax type pair. This will effectively allow tax types and subsequent transactions to support as many tax codes and rates as are needed. It will also allow tax codes that are common throughout a given country or region, such a VAT, to be reusable on many tax types thereby limiting the number of total tax codes required. Tax code definition will allow users to specify the base price of the item amount as the calculation basis or another tax code that exists. This will allow a nesting mechanism for taxes to be calculated against taxes.
A new reference table for tax classes will be created. Tax classes will have a special grouping sequence that will be used for calculating cumulative taxes. Tax classes will be associated with tax codes to define cumulative behavior, or for reporting purposes such as report summarizations and subtotals on forms.
The ability to define tax rate effectivity will be added to tax code maintenance so that users may define effective and expiration dates in much the same way they are defined for currency exchange rates and bill of material items.
Another problem with the existing paradigm is it assumes only one tax authority per transaction, while in some countries a transaction may involve several tax authorities. To handle this problem tax authority as it is used in xTuple 3.2.1 and earlier will be replaced by the term "tax zone." Tax authority will be re-implemented as a reference at the tax assignment level which will make tax authority primarily a vehicle for reporting, though it will still be possible to create checks for tax authorities.
Purchase orders will be modified to include the ability to specify a tax zone at the header level, and populate tax information at the line item level essentially the same way sales orders do. This tax information will carry over to the voucher which will be modified to allow tax zone at the header level and tax type selection at the line item level, both of which will be populated by purchase order tax type selections by default.
Voucher functionality will also include new miscellaneous distribution of type "tax" which may be selected along with a corresponding tax code. The tax code will be referenced to determine the G/L account to debit for the amount specified.
A/R and A/P debit and credit memos will be modified to include the ability to distribute tax amounts.
The sales tax summary report will be modified so that it may consolidate both taxes collected on invoices and vouchers for a given code, tax type, tax zone or tax class. The two amounts will be netted to provide easy calculation of net VAT tax due for tax codes that are VAT or have similar requirements to VAT. The summary report will provide an option to drill down to displays of supporting detail for both sales and purchasing tax data.

Window Changes

Tax Authority

Tax Authority will be reorganized as pictured:

Tax Code

The tax code window will be reorganized as pictured. Only one G/L account may be associated with a tax code. Multiple rates may be associated with a tax code with corresponding effective and expire dates. Error trapping will need to be built into the rates to ensure there is no overlap so that only one rate may be effective at any given time.

Tax class and tax authority combo boxes will be added to the window as optional selections. These may be used for reporting purposes such subtotals on forms and tax historical summaries. Tax class in particular may have bearing on cumulative tax calculation behavior as described in the algorithms section of this document.

Calculation basis will be a combo box that lists "Base Price" plus all other tax codes that share the same class assignment. As such this combo box will need to be re-populated when the class code is changed. When base price is chosen the tax code may be assigned on the tax assignment window, and the tax calculation will be based on the base price amount to be taxed, plus any applicable cumulative taxes. When a tax code is selected, then the tax code will be calculated against derived amount from the referenced tax code. In other words, this is a way to define nested taxes or "a tax on a tax."

Color coding (not pictured) should apply on the rates list following the same rules for effectivity on the Bill of Materials window: Expired rate rows are red (error display role), future rate rows are blue (emphasis display role) and the current rate row is the default color (usually black).

The buttons function as follows

• New - Opens the Tax Code Rate window in new mode.

• Edit - Opens the selected Tax Code Rate window in edit mode. Enabled only when a row is selected.

• View - Opens the selected Tax Code Rate window in view mode. Enabled only when a row is selected.

• Expire - Expires the selected row on the current date.

• Delete - Deletes the selected row.

Tax Code Rate

When creating or editing a tax rate users will be presented with a window as pictured:

Effectivity will default to "Always" for the start date and "Never" for the end date on the first entry. On subsequent entries the start date should default to the latest expire date on the list. Error trapping should be included to prevent overlapping dates.

Users will be able to specify both percentage and/or flat rate amounts. The percentage and should use the percentage validator, while the flat rate will be specified in a specific currency amount. The currency amount will be converted to the transaction document currency at the time of a transaction.

Tax Zones

A Tax Zones window will be created that lists tax zones. It will be located in the Accounting > Tax menu. New and edit buttons will open the Tax Zone window in new and edit mode respectively.

Tax Zone

A Tax Zone window will be created to create and edit tax zone records as pictured. Tax Zones should be unique and blank entries should not be allowed.

Several UI files and their corresponding .cpp files should be changed to reference tax zone instead of tax authority including: configureGL.ui, creditMemo.ui, customer.ui, invoice.ui, itemtax.ui, prospect.cpp, returnAuthorization.ui, salesOrder.ui, selectOrderForBilling.ui, shipTo.ui,taxBreakdown.ui, transferOrder.ui, vendor.ui, vendorAddress.ui, warehouse.ui

The metric for global default tax authority internally stored as "DefaultTaxAuthority" should be renamed "DefaultTaxZone." The files that reference this metric should be changed to accommodate the name change are configureGL.cpp and warehouse.cpp.

Tax Classes

A Tax Class window will be created that lists tax classes. It will be located in the Accounting > Tax menu. New and edit buttons will open the Tax Class window in new and edit mode respectively.

Tax Class

A Tax Class window will be created to create and edit tax class records as pictured. Tax classes should be unique and blank entries should not be allowed.

The group sequence is used for calculating cumulative taxes as described below.

Tax Registration

Tax registration will be altered as pictured to include both tax zone and tax authority. The tax zone may be ~Any~ or a selected zone. Effectivity dates will be added with start and end default values of "Always" and "Never" respectively. Notes will be added to the tax registration for reporting.

Tax Assignments

The tax selections window will be renamed the tax assignments window and reference tax zones in lieu of tax authority as pictured. The menu item Accounting > Tax > Selections will need to be renamed and repointed to this new window.

Screen filter options will allow users to filter on tax zone, tax type or both. When filtering on a specific tax zone or type, assignments with the value ~Any~ will be included since they apply to any zone or type respectively. Tax code description, class and class group sequence wil be display for informational purposes. The list will be sorted on Tax Zone, Tax Type, Group Sequence and Tax Code. Where tax codes are not associated with a tax class, the group sequence will be zero.

The main change to the the logic of assignments over "selections" in previous versions will be the ability to associate as many tax codes as are necessary to one tax zone/tax type pair.

Note the display will show tax code relationship by indentation. Nested tax codes, whose calculations are based on other tax codes, will also be shown as indented. The indentation will be collapsed by default, but is shown expanded here.

The tax assignments window also allows users to have a visual cue of the relationship of group sequences. These sequence numbers designate cumulative tax groupings. Cumulative taxes are calculated in order of group sequence. As tax codes in each sequence are processed all the taxes are cumulatively added to a running total. The running total of a sequence is added to the base price number in the next sequence so that tax codes in subsequent sequences consider the base price and all the taxes calculated in the previous sequence as the basis for their tax calculation. More detail concerning this calculation can be found in the Algorithms section of this document.

Tax Assignment

The Tax Selection window will be renamed "Tax Assignment" as pictured, and reference tax zone in lieu of tax authority.

Users will select multiple tax codes to be associated with a tax assignment. When the tax zone and type selections are changed, assignments associated with the selected combination should automatically be loaded.

Special business logic needs to be included concerning allowed configuration of nested and cumulative taxes:

• Multiple codes of the same (class) group sequence may be allowed, so long as they all calculate on base price.
• If a code is added that has nested references to it, then no other codes of that group sequence may be assigned to the tax zone/type pair.

In other words multiple tax codes of the same group sequence and nested tax codes of a particular group sequence are mutually exclusive choices. You can have 2 tax codes with a sequence of zero assigned to zone "Guarajat" tax type "~Any~" if they all calculate on "Base Price." However, if you do this, then no new assignments can be made for tax codes of that sequence that are nested. Likewise, if you add a tax code that has one or more nested taxes, then you may not assign any additional codes that share that sequence. Remember that tax codes not associated with a class are presumed to have a sequence of zero.

Sales Order Item, Select Billing Quantity, Invoice Item, Return Authorization Item and Credit Memo Item

The tax code widget will be removed from these windows. The tax type widget will remain and only the tax type value will be saved at the line item level. Tax code detail will be saved as sub records on documents that currently record A, B, C tax detail as described later in this document.

Tax Detail

The tax detail window appears when clicking on the tax hyperlink on the sales order item, select billing quantity, invoice item, return authorization item and credit memo item windows. It also can be navigated to from the tax breakdown window. The tax detail window should be converted to use an xtreewidget as pictured to present a list of all the tax codes that apply to the source record or to the entire order depending on the context. The amount listed should be in the currency of the source document with the corresponding currency symbol in the header. It will also show tax codes which are based on other tax codes using an indented relationship as pictured. The display should be ordered by tax class group sequence then tax code.

Note, this window serves multiple purposes and code will need to be changed support each purpose:

1. Displays tax estimates for line item windows such as Sales Order Item, Quote Item and Return Authorization Item
   
   1. Logic will need to present detail using new calculatedTaxDetail function described in Algorithms below.
2. Displays tax estimates head windows such as Sales Order, Quote and Return Authorization (As launched
   
   1. Logic will need to present detail summarized from all line items usnig calculatedTaxDetail function.
3. Displays actual tax line detail on windows such as Invoice Item, Credit Memo Item.
   
   1. Logic will need to draw from the taxhist master table. See example on addTaxToGLSeries function on how to do this generically.
4. Display actual tax header summary on windows such as Invoice and Credit Memo.
   
   1. Logic will neew to drow from the taxhist master table and summarize results.
5. Make adjustments to tax on an existing document. This can happen when launched from the Tax Breakdown window in Invoice and Credit Memo.
   
   1. Logic will need to allow the selection of a tax type and entry of amount that results in the detail being displayed.
   2. When save is clicked, the resulting tax codes are written to the appropriate tax sub table. Will likely require the table name be passed as a parameter so we know which table to write to.

Purchase Order

Purchase Order will be modified to allow a tax zone to be specified. The tax zone should default to the vendor default tax zone.

The tax field on the line items page of the purchase order window will be modified to calculate tax totals from purchase order items. The tax field will be changed to read-only. The tax field label will be a hyperlink that opens the tax breakdown window. These behaviors will all essentially mirror existing behavior on the Sales Order window.

Purchase Order Item

Purchase Order Item will be modified as pictured to include tax type selection and the calculated tax amount. The behavior of this screen should essentially mirror the Sales Order Item window. Tax type should default to the selected items tax type for the tax zone selected on the order. Tax should be calculated when item, quantity and tax type have been selected or changed. Clicking the "Tax" hyperlink will open the tax detail window to display the tax breakdown.

Voucher and Miscellaneous Voucher

The Voucher and Miscellaneous Voucher windows will be modified to include tax zone as a selectable field on the document. The default tax zone will be set to the purchase order tax zone when a purchase order is selected on Voucher, and the vendor default when vendor is selected on a Miscellaneous Voucher.

Voucher Item

The voucher item window will be reorganized as pictured. The tax type combo box will be added to a new "Distributions" tab that contains all widgets related to distribution amounts. The tax type combo box will be populated with the value stored on the purchase order item by default. Tax will be calculated any time a distribution is added, edited or removed from the distribution list, or when the tax type selection is changed. The tax hyperlink in blue will open the tax detail window in the same way the application does in sales order item and other windows that use tax. Other than selecting tax type, there will be no facility to manually adjust tax calculations. Manual adjustments should be made against miscellaneous distributions. The total distributed calculation should include the sum of all distributions, freight and tax.

Voucher Miscellaneous Distribution

In addition to G/L account and expense category, users will now have a third option to select a tax code on miscellaneous distributions. The G/L account associated with the tax code will be used to capture the debit amount associated with the tax on the general ledger. Tax code will only be enabled if a tax zone has been selected on the voucher. Because the tax may represent an adjustment to line item calculations, it should be allowed to be a positive or negative number.

A/R Misc. Debit and Credit Memo

A/R misc. debit and credit memos will allow a tax type to be selected. When this happens taxes will be inversely calculated and distributed on the total amount. On posting tax records will be recorded against an aropentax table and the cohisttax table.

A/P Misc. Debit and Credit Memo

A/P misc. debit and credit memos will allow a tax type to be selected. When this happens taxes will be inversely calculated and distributed on the total amount. On posting tax records will be recorded against an aropentax table. When credit memos are created against purchase order returns, the appropriate taxt ype and tax calculation should apply.

Report Changes

Summarized Tax History

The Summarized Taxable Sales report will be renamed Summarized Tax History. The report should be moved from the Sales > Reports menu to an Accounting > Tax > Reports menu.

The report will be expanded to summarize by any of the following combo box options: tax code, tax type, tax class, tax zone or tax authority. If tax type or another option is selected as the summary option, the options to show "All Tax Codes" or "Selected Tax Codes" should be changed to display options relating to the summary.

It is likely that users will want to use this report to reconcile general ledger activity, so an option will be added for date basis to be based on the invoice date, which is used currently, or the (G/L) distribution date which will both be recorded on cohisttax.

Users will be able to choose whether to display sales and purchase history. If both options are chosen, the report will show totals for sales history as it has historically done, and summarized purchase history based on voucher tax distributions. These two tax figures will be netted to display the net tax due on a given tax code. This functionality is specifically targeted toward users who both collect and pay VAT taxes and must report on and pay net VAT taxes collected. Columns that display sales data will only be visible when the sales option is selected, and columns that display purchase data will only be visible when the purchase option is selected. The netting column will only be visible when both sale and purchase options are selected.

If sales tax history is being displayed, users will be able to right click on a row and drill down to the supporting detail on the Detailed Sales Tax History window described below. This window will open in a modal state with the tax code or type and date options pre-selected and disabled. This behavior will mirror functionality in other places such as the ability to drill down to supporting detail in the Recievables Aging window.

Similarly, if purchase tax history is being displayed, users will be able to right click on a row and drill down to the supporting detail on the Detailed Purchase Tax History window described below. This window will also open in a modal state with options pre-selected.

The corresponding printed report for this window should likewise be renamed and modified to include the additional data points described for the window.

Detailed Sales Tax History

The Detailed Sales Tax History window will be included to provide supporting detail for the Summarized Tax History report. While it will be able to be accessed by right click drill down from that window, it will also be available in the menu system under Accounting > Tax > Reports.

The screen shot above shows the report content requirements. The sales history (cohist) table and a corresponding tax table will be used to accumulate most of information shown. The rules concerning the date basis will be the same as the Summarized Tax History described above. The currency designated on the right most calculations will be drawn from the tax authoriity designated on the sales history record.

The "Show only" group box allows users to filter on specific criteria. When checked users should be able to filter on the same options as could be summarized by the summarized tax report above in the left checkbox. The right combo box will allow them select from a list of corresponding codes. The when the "Show only" group box is checked, the query button will be disabled until valid selections are mad from both combo boxes.

There should be a right click option to drill to the Sales History Information window that is accessible in the same manner from all of the windows found in Sales > Sales Analysis > Sales History.

A printed version of this report should be created to match data elements as shown in the window.

Detailed Purchase Tax History

The Detailed Purchase Tax History window will be included to provide supporting detail for the Summarized Tax History report. While it will be able to be accessed by right click drill down from that window, it will also be available in the menu system under Accounting > Tax > Reports.

The screen shot above shows the report content requirements. The vohead, vodist and apopentax tables will be used to accumulate most of information shown. The rules concerning the date basis will be the same as the Summarized Tax History described above. The currency listed in the rightmost column will be that of the tax authority specified on the source voucher if applicable, or the base currency if none has been assigned.

The "Show only" group should operate the same on this window as the detailed sales tax display.

There should be a right click option to drill down to view the voucher window from the selected row.

Quote

The query on the standard quote form will need to be modified to reference tax zone instead of tax authority.

Batch Manager Changes

TBA

Usability Considerations

Certainly the change of assigning multiple tax codes to a tax type will make the application more flexible, and will likely reduce the amount of redundant tax code data. Establishing tax codes associated with tax types should be more intuitive and reporting capabilities by tax code will be much more granular as a result of this change.

Also, the ability to specify tax rate effictivity will make it much easier to prepare for and administrate tax holidays or tax rate changes.

On the down side, tax set up and control will remain a complex topic, and with some of the new features such a tax rate control and type, will become increasingly so.

Problems and Alternatives

Incompatible legacy data

The new configuration of tax structure described replaces the function of tax code being a template for multiple taxes (A, B, and C) to tax zone/type pairs being the template with tax code and tax rate now representing the lowest level of tax structure. On documents where the applied taxes have been recorded such as invoice and credit memo, tax data will be replaced using the new structure. However, converting legacy structures is more problematic for documents such as sales order where the tax codes are stored as a pointer mechanism because tax code will be replaced by tax type acting as the pointer. If users have manually changed the tax code from the default tax type mapping selection with an unmapped tax code on these documents, then there is no way to upgrade the data with an appropriate tax type.

The good news is that it is unlikely that many if any users are manually selecting tax codes at the sales order line item level, because if they were tax code assignment would be terribly manual and error prone, a situation that would generally be unacceptable concerning tax collection. In the event that any users are operating this way, what we can do is include a check on the upgrade process to make sure that all open sales orders have tax code selections that are the system default. If any exist that are not the default (i.e. manually selected) we do not allow the upgrade process until these orders are cleared out. While this may create difficulties for user that encounter this problem, we suspect it will be very, very few users if any, and the work around of closing or processing all orders with manually edited tax codes is fairly straight forward.

Another consequence is that if users had been manually selecting tax codes, the new paradigm will require a more rigorous discipline in creating tax assignments as they will no longer be able to select codes manually on a case by case basis. However, users will still be able to manually select tax types and, with thoughtful reorganization of data, should be able to recreate the same configuration of options with tax type assignments that was available previously with manual tax code selection.

Any other solutions that would attempt to preserve tax code as an intermediate mapping with additional subordinate child tables would only serve to add a new and unwelcome layer of complexity to the matrix system.

Upgrade scripts

The update scripts required to convert data to the new format will be some of the most comprehensive ever by a feature change to xTuple. They will include the following basic elements:

• Check for manually edited tax codes on Sales Orders and Return Authorizations (as cited above)
• Addition of new tables
• Replacement of functions
• Conversion of tax authority to tax zone data
• Creation of default A, B, C tax classes
• Restructuring of tax codes with A, B, C taxes converted to independent records with special consideration for class assignment on cumulative tax codes vs. non-cumulative tax codes
• Conversion of tax rates to the taxrate table
• Cross reference table built between old tax code ids and new ones
• Convert taxsel table to taxass using cross reference
• Original tax code records deleted (obsolete_tax table should house legacy data)
• Tax type id populated on historical documents where applicable
• Detailed tax history in tables such as invchead, invcitem and cohist converted to record based tax history.
• Obsolete tables and fields relating to tax should be commented as deprecated and removed in future releases.

As mentioned earlier, the conversion process of turning legacy A, B, C field based tax data into records could be extremely time consuming on large databases. One way to reduce this would be to only convert A, B, C elements that have an actual rate greater than zero, which in many cases will mean no conversions at all. Test upgrades should be run on known large databases to determine whether this will be a real problem. If the upgrade time is out of bounds another option would be add a boolean to all tables containing historical tax detail flagging whether they have been upgraded, and a utility to perform these conversions manually from the application in user defined chunks. This utility would flip the boolean to "true" for records that have been converted. In this way users could operate the application with legacy data conversion incomplete and perform tax conversions over a long course of time at low load times (such as evenings) without interrupting daily work flow. Checks could be added to tax summary reports to warn user if data has not been converted in the time range they are reporting on, and to run the conversion process. Finally, all tax conversions would be completed, if they hadn't already, on the next upgrade of the software.

The upside of this is it helps people with large databases make the conversion in a reasonable manner. The downside is it leaves databases in a partially upgraded state for an extended period of time, which may cause potential confusion and support traffic. A final option to mitigate these problems is to convert a fixed number of records by default that is likely greater than that most of our user base so most people get complete upgrades. Only users that exceed the predetermined threshold and who do not get a complete upgrade would be warned on log in that they must manually complete tax conversion. All of these options, however, increase the level of effort and the complexity of conversion, not to mention development. We will not be able to determine the best route on this issue until development is substantially complete on this specification.

Internal Design

The changes described throughout the rest of this document are meant to provide as much detailed guidance as possible for how to alter the application to achieve the functionality cited above. They are not, however, exhaustive. Developers should use their own discretion where they find gaps concerning implementation specifics, or when in doubt feel free to collaborate directly with xTuple staff or the community at large on the discussion board associated with this page.

Basic Algorithms

Deriving Subordinate Taxes

Because taxes may be nested by having one tax based on the results of another, we will need a utility table function that returns a row set of all the taxes that are subordinate to another. This function will take arguments of a tax code and a current tax level, and run recursively to return all tax codes and their respective levels subordinate to the tax code id passed in the argument.

First our function requires a new composite type called subtax.

-- Note: This definition is complete
CREATE TYPE subtax AS
(
  subtax_taxcode_id integer,
  subtax_taxcode_code text,
  subtax_taxcode_descrip text,
  subtax_taxcode_level integer
)

Then we can build our getSubTax function

-- Note: This definition is partial pseudo code and incomplete
CREATE OR REPLACE FUNCTION getSubTax(INTEGER, INTEGER) RETURNS SET OF  subtax AS $$
DECLARE
  pTaxCodeId ALIAS FOR $1;
  pLevel ALIAS FOR $2;
  _row subtax%ROWTYPE;
  _x RECORD;
  _y RECORD;
BEGIN
  FOR _x IN Select all tax codes whose calculation basis is pTaxCodeId
  LOOP
    Map result to _row.  Level is pLevel + 1.
    RETURN NEXT _row

    FOR _y IN  Select getSubTax(_x tax id, pLevel + 1).  This is the recursive part.
    LOOP
      Map result to _row.
      RETURN NEXT _row

... close loops and function

A query on this function would look something like this:

select * from getSubTax(7,1); -- Where 7 is system assigned tax code id for "ED-10 - Education Tax".

Displaying tax assignments

A new table function will be created that takes an input of tax zone and tax type and returns a list of tax assignments the correspond to the inputs, including sub taxes. When null is passed to either input, the result will be assumed to include all results for that input. A column will be passed to specify indentation role that may be used by the xtreewidget to define the indentation role. IndentedBOM(int,int,int,int) is an example of a similar function.

Our new function will require a new taxassign composite type used to return the indented list of tax assignments:

-- Note: This definition is complete
CREATE TYPE taxassign AS
(
  taxassign_taxzone_id integer,
  taxassign_taxtype_id integer,
  taxassign_level integer,
  taxassign_zone_code text,
  taxassign_type_descrip text,
  taxassign_taxclass_code text,
  taxassign_taxclass_sequence integer
);

Then we can build our function taxAssignments:

-- Note: This definition is partial pseudo code and incomplete
CREATE OR REPLACE FUNCTION taxAssignments(INTEGER, INTEGER) RETURNS SETOF taxassign AS $$
DECLARE
  pTaxZoneId ALIAS FOR $1;
  pTaxTypeId ALIAS FOR $2;
  _row taxassign%ROWTYPE;
  _qry text;
  _x RECORD;
  _y RECORD;
BEGIN
  -- Need to build a query statement based on input parameters
  _qry = 'SELECT DISTINCT taxass_taxzone_id,taxass_taxtype_id FROM taxass';
  IF (pTaxZoneId IS NOT NULL OR pTaxTypeId IS NOT NULL) THEN
    _qry := _qry || 'WHERE ';
    IF (pTaxZone IS NOT NULL) THEN
      _qry := '(taxass_taxzone_id=pTaxZoneId)'
      IF (pTaxType IS NOT NULL) THEN
        _qry := ' AND ';
      END IF;
    END IF;
    IF (pTaxType IS NOT NULL) THEN
      _qry := '(taxass_taxtype_id=pTaxTypeId)'
    END IF;
  END IF;

  --This first query gets all the distinct tax zone and type groupings as if it were its own table.
  --This allows us to have a level 0 record as pictured in Tax Assignments window that code assignements will
  --Subordinate to.
  FOR _x IN EXECUTE _qry
  LOOP
    Map values to _row here
    RETURN NEXT so we get a level tax zone/type 0 record.

    -- Now get all the tax code assignments that belong to this Zone and Type pair
    FOR _y IN SELECT all the records with matching _x.taxzone_id and _x.taxtype_id
                     make sure to coalesce group sequnce to zero if no tax class
              FROM taxass
              JOIN taxcode ON (taxass_taxcode_id=taxcode_id)
              LEFT OUTER JOIN taxclass ON (taxass_taxcode_id=tax)
    LOOP
      Map results to _row
      RETURN NEXT to get code detail record;

      -- Get the sub taxes for this particular code
      SELECT results FROM taxcodesub(_y.taxassign_taxcode_id, 1) a new recursive function described above
      LOOP
        Map results for sub taxes to _row
        RETURN NEXT return to _row

... end loops and function

A query should look something like this:

select * from taxAssignments(4,NULL) -- Where 4 is the system assigned tax code id for "Gujarat" and NULL indicates all tax types are to be returned

The results as presented for Food Staple and Service in Tax Assignment screen shot above:

A new table function will be created that takes the an input of tax zone,tax type,date,currency id, and amount to produce a set of output records describing tax detail. This function will be multilevel similar to the taxAssignments above.

To create our function a composite type called taxdetail will used to return a calculated tax detail record set used by the taxdetail function which is in turn used by the Tax Detail window.

-- Note: This definition is complete
CREATE TYPE taxdetail AS
(
  taxdetail_tax_id integer,
  taxdetail_tax_code text,
  taxdetail_tax_descrip text,
  taxdetail_tax_basis_tax_id integer,
  taxdetail_taxrate_percent numeric(8,4),
  taxdetail_taxrate_amount numeric(16,2),
  taxdetail_level integer,
  taxdetail_taxclass_id integer,
  taxdetail_taxclass_code text,
  taxdetail_taxclass_sequence integer,
  taxdetail_tax numeric (16,2),
  taxdetail_curr_id integer,
  taxdetail_curr_abbr text
);

A recursive calculateSubTax function will need to be created to support our taxDetail function:

-- Note: This definition is partial pseudo code and incomplete
CREATE OR REPLACE FUNCTION calculateSubTax(INTEGER, DATE, INTEGER, NUMERIC, INTEGER) RETURNS SETOF taxdetail AS ' $$
DECLARE
  pTaxCodeId FOR ALIAS $1;
  pDate FOR ALIAS $3;
  pCurrId FOR ALIAS $4;
  pAmount FOR ALIAS $5;
  pLevel ALIAS FOR $2;
  _row subtax%ROWTYPE;
  _rownumber := 1;
  _x RECORD;
  _y RECORD;
BEGIN
  FOR _x IN Select all tax codes and rates whose calculation basis is pTaxCodeId.
    See calculateTaxDetail pseudo-code below for more info on calculation.
  LOOP
    Calculate tax Map result to _row.  Map _rownumber to taxdetail_rownumber. Level is pLevel + 1.
    RETURN NEXT _row;
    _rownumber := _rownumber + 1;

    FOR _y IN  Select calculateSubTax(_x tax id, pDate, pCurrId, calculated tax, pLevel + 1).  This is the recursive part.
    LOOP
      Map result to _row.
      RETURN NEXT _row;
      _rownumber := _rownumber + 1;

... close loops and function

Now we can create our taxDetail function using the new type. Note that it is important that the lookup for tax zone/tax type pairs mirror legacy behavior.

-- Note: This definition is partial pseudo code and incomplete
CREATE OR REPLACE FUNCTION calculateTaxDetail(INTEGER, INTEGER, DATE, INTEGER, NUMERIC) RETURNS SETOF taxdetail AS '
DECLARE
  pTaxZoneId FOR ALIAS $1;
  pTaxTypeId FOR ALIAS $2;
  pDate FOR ALIAS $3;
  pCurrId FOR ALIAS $4;
  pAmount FOR ALIAS $5;
  _row taxdetail%ROWTYPE;
  _x RECORD;
  _y RECORD;
  _currcum numeric := 0;  -- Current cumulative tax
  _currseq numeric := 0;  -- Current group sequence
  _prevcum numeric := 0;  -- Previous cumulative tax
  _tax numeric := 0;      -- Calculated tax amount
  _taxbasis numeric := 0  -- Use for calculating sub taxes

BEGIN

  First check to make sure input values have been passed in for each variable.  If not, throw an error.

  Next determine which tax assignment zone/type pair should be used for assignment references.
  This works similar to the old getTaxSelection function, but uses "DISTINCT" grouping to emulate the
  notion of one tax zone/type record per code that not longer exists.
  We convert null to negative one so we can query later.

  -- Begin complete query example
  SELECT DISTINCT
    coalesce(taxass_taxzone_id,-1) as taxzone_id,
    coalesce(taxass_taxtype_id,-1) as taxtype_id,
    CASE
      WHEN ((taxass_taxzone_id IS NOT NULL) AND (taxass_taxtype_id IS NOT NULL)) THEN
        0
      WHEN ((taxass_taxzone_id IS NOT NULL) AND (taxass_taxtype_id IS NULL)) THEN
        1
      WHEN ((taxass_taxzone_id IS NULL) AND (taxass_taxtype_id IS NOT NULL)) THEN
        2
      ELSE
        4
    END AS sequence
    INTO _x
  FROM taxass
  WHERE  ((coalesce(taxass_taxzone_id,pTaxZoneId) = pTaxZoneId)
  AND    (coalesce(taxass_taxtype_id,pTaxTypeId) = pTaxTypeId))
  ORDER BY sequence
  LIMIT 1
  -- End complete query example

  IF (NOT FOUND) THEN
    throw an error
  END IF;

  --Now loop through each tax detail record and return calculated result
  FOR _y IN
    SELECT the data required by taxdetail type.  Coalesce group sequence to 0 if no class.
    FROM taxass
      JOIN taxcode ON (taxass_taxcode_id=taxcode_id)
      LEFT OUTER JOIN taxclass ON (taxcode_taxclass_id=taxclass_id)
    WHERE ((coalesce(taxass_taxzone_id,-1) = _x.taxzone_id)
    AND   (coalesce(taxass_taxtype_id,-1) = _x.taxtype_id))
    ORDER BY coalesce(taxclass_seq,0)
  LOOP
    -- If sequence has changed, cache the previous cumulative tax
    IF (_currseq != _x.sequence)
      _prvcum := _currcum;
    END IF;

    -- Calculate the tax amount.  Convert currency for flat rate amounts
    SELECT round(((_y.taxrate_percent + 1) * (pAmount + _prvcum) +
           currToCurr(_y.taxrate_curr_id,pCurrId,_y.rate_amount,pDate),2) INTO _tax
    FROM tax
      JOIN taxrate
    WHERE ((taxcode_id=_x.taxass_taxcode_id)
    AND (pDate BETWEEN coalesce(taxrate_effective,startoftime()) AND coalesce(taxrate_effective,endoftime())))

    Map fields to _row
    RETURN NEXT _row;

    -- Increment cumulative balance and sequence number
    _currcum := _currcum + _tax;
    _currseq := _x.sequence;

    -- Loop to Calculate sub taxes
    SELECT necessary data
    FROM calculateSubTax(_y.taxcode_id,pDate, pCurrId, _tax, 0)
      JOIN taxrate ON (subtax_taxcode_id=taxrate_taxcode_id)
    WHERE (pDate BETWEEN coalesce(taxrate_effective,startoftime()) AND coalesce(taxrate_effective,endoftime()))
    LOOP
       Map data
       Return row
       Add to cumulative counter (_curcum)

... close up loops and function

A sample query might look like this:

SELECT * FROM taxDetail(4,6,'04/04/2009',2,60); -- Where 4 is the zone id for "Guarjat", 6 is the tax type id for "Domestic Food", the date is an invoice date, 2 is the currency id for U.S. dollars and 60 is the amount being taxed.

The results should look something like this:

A replacement convenience function for calculateTax will be needed that accepts all the same arguments as calculateTaxDetail and uses that function internally to simply return the sum total value. The existing calculateTax function should be dropped.

Recording Tax Data

Currently several documents record taxes one of two ways. The first is where key tax code identifiers are stored at the header or line item level that are used to calculate tax estimates. Sales Order Item is an example of this. It records a tax code in the database, and the tax on the window is calculated on the fly each time it is viewed. The second method is found on documents that actually record the tax code detail including codes, percentages and rate detail in columns for each of the existing A, B and C tax codes. Invoice is a an example of this type of document. The rate detail is recorded on the header and line item records respectively.

For the first kind of document that only records codes for tax estimates, the tax code id will be replaced by the tax type id. This will allow the practice to continue of recording a single data identifier that acts as a pointer to access the calculateTax function to calculate tax estimates. Functional areas that currently or will work this way are Quotes, Sales Orders, Return Authorizations and Purchase Orders. The windows and corresponding tables will need to be modified, as described throughout this document, to deal exclusively with tax types. Functions that exist to lookup tax codes can be removed and functions that calculate tax should be replaced by the new calculateTax function already described above.

On the second type of document the tax detail will no longer be recorded at the header and line item, but in sub tables that require a record to be recorded for each code. To ensure consistency and allow for more reusable code, all these tables will inherit their structure from a single master table called taxhist as described in table definitions below. Functional areas that work this way are Billing Selection, Invoice, Credit Memo, Transfer Order (freight only), Voucher (table structure exists, but functionality not previously implemented), and Sales History. Those documents will need to be modified to move historical tax data to corresponding sub table records. The functionality in the windows that calculates and records taxes should be replaced by trigger driven logic in the database for all areas except sales history. Thise includes these tables: cobmisc, cobitem, invchead, invcitem, cmhead, cmitem, cobmisc, tohead, toitem, vohead, voitem.

The trigger logic will require a common helper function calcuateTaxHist that leverages the advantage of table inheritance used for recording tax history:

-- Note: This definition is partial pseudo code and incomplete
CREATE OR REPLACE FUNCTION calculateTaxHist(text, integer, date, integer, numeric) RETURNS BOOLEAN AS $$
DECLARE
  pTableName ALIAS FOR $1;
  pParentId ALIAS FOR $1;
  pDate ALIAS FOR $1;
  pAmount ALIAS FOR $2;
  _x RECORD;
  _qry TEXT;
BEGIN
  First check to make sure all input variables are passed, if not throw an error.

  Build a query that deletes any previous tax history for this document record
  _qry := 'DELETE FROM ' || pTableName || ' WHERE taxhist_parent_id = ' || pParentId || ';';
  EXECUTE _qry;

  Next, build and execute query that inserts new rows.
  _qry := 'INSERT INTO ' || pTableName || ' (taxhist_parent_id, tahist_tax_id... etc. ' ||
          'SELECT pParentId, taxdetail_taxid, .... etc. ' ||
          'FROM taxDetail(inputs here)'

  RETURN true;
END;
$$ LANGUAGE 'plpgsql';

  Note:  The date passed into this function should be the document header date.
  In the case of invoice the invoice header date would be passed for both header and line item calls to this function.
  We can insert the document date into history from this function, but we CAN NOT record
  the distribution date until the document is posted, so leave invhist_distdate NULL for now.

Triggers should then be added to parent tables that call this function when ever taxable data have changed such as tax type, amount etc.

Posting Tax records to the G/L

Invoice and Credit Memo are the two documents that actually post tax records to the general ledger. A helper function currently exists to handle G/L posting of taxes called addTaxToGLSeries. The current iteration of this function should be dropped and replaced with a new iteration that works like this:

-- Note: This definition is partial pseudo code and incomplete
CREATE OR REPLACE FUNCTION addTaxToGLSeries(INTEGER, TEXT, TEXT, TEXT, DATE, DATE, TEXT, INTEGER, TEXT) RETURNS NUMERIC AS $$
DECLARE
    pSequence   ALIAS FOR $1;
    pSource     ALIAS FOR $2;
    pDocType    ALIAS FOR $3;
    pDocNumber  ALIAS FOR $4;
    pExchDate   ALIAS FOR $5
    pDistDate   ALIAS FOR $6;
    pTableName  ALIAS FOR $7;
    pParentId   ALIAS FOR $8;
    pNotes      ALIAS FOR $9;
BEGIN

  This is just a fancy select statement on taxhist calling a function.
  Because all tax record tables inherit from taxhist, we can use the same select statement for all
  http://www.postgresql.org/docs/8.1/static/ddl-inherit.html
  pTableName in the where clause narrows down the selection to the right sub table.

  SELECT insertIntoGLSeries( pSequence, pSource, pDocType, pDocNumber,
                                   tax_sales_accnt_id, currToBase(taxhist_curr_id, tax, pExchDate),
                                   pGLDate, pNotes )
  FROM tax, taxhist, pg_class
  WHERE ((tax_id = taxhist_tax_id)
  AND (taxhist_parent_id = pParentId)
  AND (taxhist.tableoid = pg_class.oid)
  AND (relname = pTablename));

  Now use a similar statement to sum up the total tax amount from taxhist and return that value.

... close function

Calculating Taxes for A/P and A/R Credit and Debit Memos

These documents start with a user entering a total amount. Therefore if a tax type is to be applied then the tax needs to be inversely calculated from the total amount to determine the basis. We will need a special helper function to accomplish this:

-- Note: This definition is partial pseudo code and incomplete
CREATE OR REPLACE FUNCTION calculateTaxBasis(INTEGER, INTEGER, DATE, INTEGER, NUMERIC) RETURNS numeric AS '
DECLARE
  pTaxZoneId FOR ALIAS $1;
  pTaxTypeId FOR ALIAS $2;
  pDate FOR ALIAS $3;
  pCurrId FOR ALIAS $4;
  pAmount FOR ALIAS $5;
  _x RECORD;
  _tax numeric := 0;         -- Running amount
  _amount numeric := 0;      -- Calculated base amount
BEGIN

  Check for valid inputs, throw an error if anything missing

  _amount := pAmount;

  We will leverage the regular calculatetaxdetail function to get the list of taxes and tax rates,
  but process in reverse.
  FOR _x IN
    SELECT sum(taxdetail_percent) AS tax_percent, 
           sum(taxdetail_amount) AS tax_amount
    FROM calculateTaxDetail(pTaxZoneId,pTaxTypeId,pDate,pCurrId,0)
    GROUP BY taxdetail_taxclass_sequence, taxdetail_level
    ORDER BY taxdetail_taxclass_sequence DESC, taxdetail_level DESC
  LOOP
    -- Calculate backward
    _amount := _amount - round((_amount - tax_amount) / (1 + tax_percent),2);
  END LOOP

  RETURN _amount;

  ... close function.

The posting functions for these documents will need to be altered to consider tax types. The affected functions will be postarcreditmemo, postardebitmemo, postapcreditmemo and postapdebitmemo. The changes will be similar to all like this:

Add tax type to input variables
Add _sequence INTEGER to input variables
Add _basis NUMERIC to input variables

 execute regular functionality...

 ... add in/Change

 _sequence := fetchGLSequence(); -- Use this for insertIntoGLSeries and addTaxTOGLSeries
 _basis := calculateTaxBasis(inputs including pAmount);
 IF tax type specified on memo THEN
    calculateTaxHist(inputs including _basis for amount)  -- Builds tax records for aropentax
    addTaxTOGLSeries(inputs including _basis for amount)  -- Sets up G/L entries
 END

 Replace insertGLTransaction function with insertIntoGLseries -- Because now there can be more than one due to taxes.  The one entry here should use _basis as the amount.
 SELECT postGLSeries(inputs) -- Posts the series at once.

 For A/R memos ONLY: Create "Misc" entry in cohist and in cohisttax using calculateTaxHist.  This has not been done before, but we need to do it now so that tax reports that drive off cohist and cohist tax have all the data and will be complete.

 ... continue existing....

Custom Widget Changes

The xcombobox widget needs to include to new types: TaxZones and TaxClasses, which should draw from the taxzone and taxclass tables respectively.

Schema Changes

New Tables

• taxzone - Will be used to store tax zone master data.

CREATE TABLE taxzone
(
  taxzone_id serial,
  taxzone_code text,
  taxzone_descrip text,
  CONSTRAINT taxzone_pkey PRIMARY KEY (taxzone_id)
);
GRANT ALL ON TABLE taxzone TO xtrole;
COMMENT ON TABLE taxzone IS 'Tax zone information';
COMMENT ON COLUMN taxzone.taxzone_id IS 'Primary key';
COMMENT ON COLUMN taxzone.taxzone_code IS 'Code';
COMMENT ON COLUMN taxzone.taxzone_descrip IS 'Description';

Tax zone should be populated by the id, code and names listed in existing tax authorities into the tax zone id, code and description fields. This will allow for a smooth transition from the use of tax authority to tax zone on all records.

• taxclass - Will be used to store tax class master data

-- Note: This definition is complete
CREATE TABLE taxclass
(
  taxclass_id serial PRIMARY KEY,
  taxclass_code text,
  taxclass_descrip text,
  taxclass_sequence integer,
);
GRANT ALL ON TABLE taxclass TO xtrole;
COMMENT ON TABLE taxclass IS 'Tax class information';
COMMENT ON COLUMN taxclass.taxclass_id IS 'Primary key';
COMMENT ON COLUMN taxclass.taxclass_code IS 'Code';
COMMENT ON COLUMN taxclass.taxclass_descrip IS 'Description';
COMMENT ON COLUMN taxclass.taxclass_sequence IS 'Group sequence';

• taxrate - Stores tax rate data for tax codes

-- Note: This definition is complete
CREATE TABLE taxrate
(
  taxrate_id serial PRIMARY KEY,
  taxrate_tax_id integer NOT NULL REFERENCES tax (tax_id),
  taxrate_percent numeric (8,4) NOT NULL,
  taxrate_curr_id integer REFERENCES curr_symbol (curr_id),
  taxrate_amount numeric (16,2) NOT NULL,
  taxrate_effective date,
  taxrate_expires date
);
GRANT ALL ON TABLE taxrate TO xtrole;
COMMENT ON TABLE taxrate IS 'Tax rates.';
COMMENT ON COLUMN taxrate.taxrate_id IS 'Primary key.';
COMMENT ON COLUMN taxrate.taxrate_tax_id IS 'The id of the parent tax code.';
COMMENT ON COLUMN taxrate.taxrate_percent IS 'Tax rate percentage.';
COMMENT ON COLUMN taxrate.taxrate_curr_id IS 'The currency id of the flat rate amount.';
COMMENT ON COLUMN taxrate.taxrate_amount IS 'Flat tax rate amount.';
COMMENT ON COLUMN taxrate.taxrate_effective IS 'The effective date of the tax rate.  NULL value means always.';
COMMENT ON COLUMN taxrate.taxrate_expires IS 'The expire date of the tax rate.  NULL value means never.';

• taxass - Used to store tax assignment information. Replaces taxsel which should be dropped after upgrade

-- Note: This definition is complete
CREATE TABLE taxass
(
  taxass_id serial PRIMARY KEY,
  taxass_taxzone_id integer REFERENCES taxzone (taxzone_id),
  taxass_taxtype_id integer REFERENCES taxtype (taxtype_id),
  taxass_tax_id integer NOT NULL REFERENCES tax (tax_id)
);
ALTER TABLE taxass ADD UNIQUE (taxass_taxzone_id, taxass_taxtype_id, taxass_tax_id);
GRANT ALL ON TABLE taxass TO xtrole;
COMMENT ON TABLE taxass IS 'The tax assignment table associates different tax zones and tax types to a given set of tax codes.';
COMMENT ON COLUMN taxass.taxass_taxzone_id IS 'The id of the tax zone. If NULL any tax zone will apply.';
COMMENT ON COLUMN taxass.taxass_taxtype_id IS 'The id of the tax type. If NULL any tax type will apply.';
COMMENT ON COLUMN taxass.taxass_tax_id IS 'The id of the tax code.';

• taxhist - A master table definition inherited by child tables to record tax transaction history.

-- Note: This definition complete
CREATE TABLE taxhist
(
  taxhist_id serial PRIMARY KEY,
  taxhist_parent_id integer NOT NULL,

  taxhist_taxtype_id integer NOT NULL REFERENCES taxtype (taxtype_id)
  taxhist_tax_id integer NOT NULL REFERENCES tax (tax_id),
  taxhist_basis numeric (16,2) NOT NULL,
  taxhist_basis_tax_id integer,
  taxhist_sequence integer,
  taxhist_percent numeric (8,4) NOT NULL,
  taxhist_amount numeric (16,2) NOT NULL,
  taxhist_tax numeric (16,2) NOT NULL,
  taxhist_docdate date NOT NULL,
  taxhist_distdate date
);
COMMENT ON TABLE taxhist IS 'A table type to record tax transaction history. Inherited by other tables that actually record history. As the parent, queries can be run against it that will join all child tables. ';
COMMENT ON COLUMN taxhist.taxhist_id IS 'Primary key';
COMMENT ON COLUMN taxhist.taxhist_parent_id IS 'Source parent id.';

COMMENT ON COLUMN taxhist.taxhist_taxtype_id IS 'Tax type id';
COMMENT ON COLUMN taxhist.taxhist_tax_id IS 'Tax code id.';
COMMENT ON COLUMN taxhist.taxhist_basis IS 'Base price amount on which the tax calculation is based.';
COMMENT ON COLUMN taxhist.taxhist_basis_tax_id IS 'Tax rate calculation basis.  If null, then the amount of the parent document, otherwise calculated on the result amount of the tax code id referenced.';
COMMENT ON COLUMN taxhist.taxhist_amount IS 'Flat tax amount.';
COMMENT ON COLUMN taxhist.taxhist_tax IS 'Calculated tax amount.';
COMMENT ON COLUMN taxhist.taxhist_docdate IS 'The date of the parent document.';
COMMENT ON COLUMN taxhist.taxhist_docdate IS 'The G/L distribution date of the parent document.';

Tax history tables

The taxhist table will provide a common framework for several other tax history tables. Here's an example of a tax table for cohist (sales order history).

-- Note: This definition is complete
CREATE TABLE cohisttax
(
    PRIMARY KEY (taxhist_id),
    FOREIGN KEY (taxhist_parent_id) REFERENCES cohist (cohist_id) ON DELETE CASCADE,
    FOREIGN KEY (taxhist_tax_id) REFERENCES tax (tax_id),
    FOREIGN KEY (taxhist_basis_tax_id) REFERENCES tax (tax_id)
)
INHERITS (taxhist);

All that needed to be defined on this table were the constraints, the rest is inherited by taxhist. The advantage of using table inheritence is that it will enforce structural integrity, simplify queries and functions that must sum tax from multiple tables and likely future maintenance as well. A new table inheriting taxhist should be made for each table that currently stores historical tax data. The naming convention will be {tablename} + tax per the example above for each of these 12 tables: asohist, cmhead, cmitem, cobill, cobmisc, cohist, invchead, invcitem, tohead, toitem, vohead, voitem. Each table should have foreign keys referencing the parent and tax table ids as shown in the example above.

Updated Tables

The following tables will need to be updated to include a taxzone column as such:

ALTER TABLE cmhead ADD COLUMN cmhead_taxzone_id integer REFERENCES taxzone (taxzone_id);
ALTER TABLE cobmisc ADD COLUMN cobmisc_taxzone_id integer REFERENCES taxzone (taxzone_id);
ALTER TABLE cohead ADD COLUMN cohead_taxzone_id integer REFERENCES taxzone (taxzone_id);
ALTER TABLE custinfo ADD COLUMN custinfo_taxzone_id integer REFERENCES taxzone (taxzone_id);
ALTER TABLE invchead ADD COLUMN invchead_taxzone_id integer REFERENCES taxzone (taxzone_id);
ALTER TABLE itemtax ADD COLUMN itemtax_taxzone_id integer REFERENCES taxzone (taxzone_id);
ALTER TABLE prospect ADD COLUMN prospect_taxzone_id integer REFERENCES taxzone (taxzone_id);
ALTER TABLE quhead ADD COLUMN quhead_taxzone_id integer REFERENCES taxzone (taxzone_id);
ALTER TABLE rahead ADD COLUMN rahead_taxzone_id integer REFERENCES taxzone (taxzone_id);
ALTER TABLE shiptoinfo ADD COLUMN shiptoinfo_taxzone_id integer REFERENCES taxzone (taxzone_id);
ALTER TABLE taxreg ADD COLUMN taxreg_taxzone_id integer REFERENCES taxzone (taxzone_id);
ALTER TABLE tohead ADD COLUMN tohead_taxzone_id integer REFERENCES taxzone (taxzone_id);
ALTER TABLE vendaddrinfo ADD COLUMN vendaddrinfo_taxzone_id integer REFERENCES taxzone (taxzone_id);
ALTER TABLE vendinfo ADD COLUMN vendinfo_taxzone_id integer REFERENCES taxzone (taxzone_id);
ALTER TABLE vohead ADD COLUMN vohead_taxzone_id integer REFERENCES taxzone (taxzone_id);
ALTER TABLE whsinfo ADD COLUMN whsinfo_taxzone_id integer REFERENCES taxzone (taxzone_id);

These same tables should be populated with the existing taxauth_id into the taxzone_id on their respective records. Finally, the taxauth_id on the tables listed above should be dropped, with the exception of taxreg (tax registration).

tax will be modified to include new references to tax class, tax authority, tax basis and a foreign key to account:

ALTER TABLE tax ADD COLUMN tax_taxclass_id integer REFERENCES taxclass (taxclass_id);
ALTER TABLE tax ADD COLUMN tax_taxauth_id integer REFERENCES taxauth (taxauth_id);
ALTER TABLE tax ADD COLUMN tax_basis_tax_id integer REFERENCES tax (tax_id) ON DELETE CASCADE;
ALTER TABLE tax ADD FOREIGN KEY (tax_sales_accnt_id) REFERENCES accnt (accnt_id)

Tax will also be heavily involved during the upgrade process. When the upgrade process is complete, the following fields will be dropped from tax:

ALTER TABLE tax DROP COLUMN tax_ratea;
ALTER TABLE tax DROP COLUMN tax_freight;
ALTER TABLE tax DROP COLUMN tax_cumulative;
ALTER TABLE tax DROP COLUMN tax_rateb;
ALTER TABLE tax DROP COLUMN tax_salesb_accnt_id;
ALTER TABLE tax DROP COLUMN tax_ratec;
ALTER TABLE tax DROP COLUMN tax_salesc_accnt_id;

To reduce potential catastrophic data loss, an exact copy of tax should be made called obsolete_tax before any changes are made to the table. This way if any errors are found in the conversion process, there is an easy way to reference the original data.

Several tables that currently reference tax will need to be altered to reference taxtype as such:

ALTER TABLE asohist ADD COLUMN asohist_taxtype_id REFERENCES taxtype (taxtype_id);
ALTER TABLE cmhead ADD COLUMN cmhead_taxtype_id REFERENCES taxtype (taxtype_id);
ALTER TABLE cmitem ADD COLUMN cmitem_taxtype_id REFERENCES taxtype (taxtype_id);
ALTER TABLE cobill ADD COLUMN cobill_taxtype_id REFERENCES taxtype (taxtype_id);
ALTER TABLE cobmisc ADD COLUMN cobmisc_taxtype_id REFERENCES taxtype (taxtype_id);
ALTER TABLE cohist ADD COLUMN cohist_taxtype_id REFERENCES taxtype (taxtype_id);
ALTER TABLE cohisttax ADD COLUMN cohisttax_taxtype_id REFERENCES taxtype (taxtype_id);
ALTER TABLE coitem ADD COLUMN coitem_taxtype_id REFERENCES taxtype (taxtype_id);
ALTER TABLE invchead ADD COLUMN invchead_taxtype_id REFERENCES taxtype (taxtype_id);
ALTER TABLE invcitem ADD COLUMN invcitem_taxtype_id REFERENCES taxtype (taxtype_id);
ALTER TABLE pohead ADD COLUMN tohead_taxtype_id REFERENCES taxtype (taxtype_id);
ALTER TABLE poitem ADD COLUMN toitem_taxtype_id REFERENCES taxtype (taxtype_id);
ALTER TABLE quhead ADD COLUMN quhead_taxtype_id REFERENCES taxtype (taxtype_id);
ALTER TABLE quitem ADD COLUMN quitem_taxtype_id REFERENCES taxtype (taxtype_id);
ALTER TABLE raitem ADD COLUMN raitem_taxtype_id REFERENCES taxtype (taxtype_id);
ALTER TABLE tohead ADD COLUMN tohead_taxtype_id REFERENCES taxtype (taxtype_id);
ALTER TABLE toitem ADD COLUMN toitem_taxtype_id REFERENCES taxtype (taxtype_id);
ALTER TABLE voitem ADD COLUMN voitem_taxtype_id REFERENCES taxtype (taxtype_id);

These same tables listed should have foreign key references to tax_id removed where they exist.

• taxreg will need to include new fields as follows:

-- Note: This definition is complete
ALTER TABLE taxreg ADD COLUMN taxreg_taxzone_id integer REFERENCES taxzone (taxzone_id);
ALTER TABLE taxreg ADD COLUMN taxreg_effective date DEFAULT startoftime();
ALTER TABLE taxreg ADD COLUMN taxreg_expires date DEFAULT endoftime();
ALTER TABLE taxreg ADD COLUMN taxreg_notes text DEFAULT '';

Privilege Changes

The privilege "MaintainTaxSel" and "ViewTaxSel" will need to be renamed "MaintainTaxAssignments" and "ViewTaxAssignments" respectively. The new menu for Tax Assignment should check for these privileges.

Stored Procedure Changes

The following functions will need to be altered to reference tax zone instead of tax authority. The old function should be renamed and replaced where used in the application. Other tax logic concerning calculations should be updated where applicable.

• changecmheadtaxauth
• changecobtaxauth
• changeinvoicetaxauth
• changequotetaxauth
• changesotaxauth
• changetotaxauth

Major new functions are described in the Algorithms sections. The functions listed below require updates to conform to the new logic.

• addtaxtoglseries - Described in Algorithms section above.
• archivesaleshistory - Change to include tax zone and tax type. Should copy tax information from cohisttax to asohisttax and delete cohisttax data.
• calculatetax - Replaced by new calculate tax function cited in 'algorithms' section. The new version should replace the existing in all places where used including: creditMemo.cpp, creditMemoItem.cpp, invoice.cpp, invoiceItem.cpp, Quote.xml, returnAuthorization.cpp, returnAuthorizationItem.cpp, salesOrder.cpp, salesOrderItem.cpp, selectOrderForBilling.cpp, taxBreakDown, toitemtableModel.cpp, transferOrder.cpp, transferOrderItem.cpp.
• convertcustomertoprospect - Replace taxauth reference with taxzone
• convertprospecttocustomer - Replace taxauth reference with taxzone
• convertquote - Replace taxauth reference with taxzone, remove tax id reference
• copyinvoice - Replace taxauth reference with taxzone. Move tax references to invcheadtax and incvitemtax.
• copyitem - Replace taxauth reference with taxzone.
• copypo - Remove reference to po head tax. Add reference to tax zone at the header and tax type at the line item.
• copyso- Replace taxauth reference with taxzone and tax code reference with taxtype.
• createbillingheader - Replace taxauth reference with taxzone. Move freight tax references to toheadtax and toitemtax.
• deleteaccount - Update checks for usage on tax.
• deletetax - Update checks for usage.
• deletetaxauthority - Update checks for usage on tax table instead of taxsel.
• deletetaxtype - Replace reference to taxsel with reference to taxass.
• getfreighttaxselection - Remove. Should be replaced by calculateTaxDetail which can be used with getFreightTaxTypeId to handle this functionality. Replaced where used on Quote.xml (report).
• gettaxselection - Drop. Should generally be replaced by calculateTaxDetail, though in some places will be removed altogether.
• postbillingselection - Should be altered to use caculateTaxDetail to create records on invcheadtax and invcitemtax in lieu of recording on invchead and invcitem.
• postbillingselectionconsolidated - Same as postbillingselection.
• postcreditmemo - Revise to use new addTaxToGLSeries function. Should record tax history on cohisttax.
• postinvoice - Same as postcreditmemo
• postinvoices - Update to remove grouping reference to invchead_tax.
• postvoucher - Update to distribute taxes and record tax history.
• recalculatecmheadtaxtotal - Drop. Also drop in cmhead and cmitem triggers where used.
• recalculatecobmisctaxtotal - Drop. Also drop in cobmisc and cobill triggers where used.
• recalculateinvcheadtaxtotal - Drop. Also drop in invchead and invcitem triggers where used.
• restoresaleshistory - Replace reference from taxauth to taxzone. Reference restore from asohisttax to cohisttax
• selectforbilling - References to taxauth should be replaced with taxzone. Tax calculation should create tax sub tables using calculateTaxHist.
• selectuninvoicedshipment - Should create tax sub tables using calculateTaxHist.
• site - Replace reference from taxauth to taxzone.
• transitwhs - Replace reference from taxauth to taxzone.

The following functions on xTuple Standard Edition will need to be altered to accommodate new tax logic:

• raitem
• calcradueamt
• calcraamt

• copytransferorder (currently in PostBooks repository and should be removed from there)

• changerataxauth
• importcoitemstora
• createracreditmemo
• calcrataxamt

The triggers on cohead and quhead will need to be altered to reference tax zone instead of tax authority. The following api views will need to be altered to reference tax zone instead of tax authority:

• creditmemo
• creditmemoline
• customer
• customertaxreg
• custshipto
• custtax
• invoice
• invoiceline
• itemtaxtype
• prospect
• quote
• quoteline
• salesline
• salesorder
• site
• vendor
• vendoraddress

Performance Considerations

No significant impact on performance is expected.

QA Considerations

The impact on QA testing from these changes will be significant. While of course our standard use-case scenarios will apply, every screen affected in the Windows changes section should be tested manually be senior xTuple QA tester.

Documentation Considerations

Stardard reference documentation updates apply.

Release Considerations

This specification is intended to be implemented on version 3.3 of xTuple ERP.