Enhance application to support US Sales Tax

From http://www.openvpms.org/project/add-support-invoice-level-taxation

OpenVPMS currently:

• calculates tax per-line; the invoice tax is the sum of the line item tax

• assumes product prices are tax-inclusive

This is not sufficient for US-style taxation, where the tax is based on the total invoice amount, and prices are tax-exclusive.

To support other jurisdictions, the following changes are required:

1. Taxation rules

Currently, OpenVPMS supports one taxation rule. This is effectively PER_LINE below.

This needs to be expanded to:

• PER_LINE

  • this is used for Australian/New Zealand style taxation

  • invoice tax totals are summed from the invoice item tax totals

  • invoice items amounts are tax-inc

  • prices are tax-inc

• TOTAL

  • this is used for US-style taxation

  • tax is calculated per-line but not rounded until the total is determined.

  • invoice items amounts are tax-ex

  • invoice amounts are tax-inc. For reporting, the invoice tax total needs to be subtracted

  • prices are tax-ex

  • invoice item tax amounts are rounded, and informative only. Summing them will not necessarily add up to the invoice tax total due to rounding.

2. Archetypes

• The Practice archetype will be updated to specify the tax rule
  The node will be named "taxRule", and default to PER_LINE

• Archetypes calculating tax will be updated to specify the tax rule

  1. the node will be named "taxRule", and default to the Practice taxRule

  2. the node will be hidden and read-only

This applies to the following archetypes:

• Customer Invoice

• Counter Sale

• Customer Credit

• Estimate

• Order

• Delivery

• Supplier Invoice

• Supplier Credit

3. Display

The tax-inclusive or tax-exclusive prices will be displayed based on the tax rule.

If the tax rule is:

• PER_LINE - inc-tax prices will be displayed, and ex-tax prices hidden

• TOTAL - inc-tax prices will be hidden, and ex-tax prices will be displayed

4. Editing

If the tax rule is:

• PER_LINE

  • Ex-tax prices will be:

    • hidden

    • initially set to the product ex-tax price

  • Inc-tax prices will be:

    • editable. If an inc-tax price changes, the ex-tax price will be derived from it

    • initially calculated from the ex-tax price

• TOTAL

  • Ex-tax prices will be:

    • editable

    • initially set to the product ex-tax price

  • Inc-tax prices will be:

    • hidden

    • derived from the ex-tax price

5. Charges

Invoices, Counter Sale and Credit items will be updated to include the following fields:

• Fixed Price (Ex-Tax)

• Unit Price (Ex-Tax)

• Tax Rule

6. Estimates

Estimates will be updated to include the following fields:

• Fixed Price (Ex-Tax)

• Low Unit Price (Ex-Tax)

• High Unit Price (Ex-Tax)

• Tax Rule

7. Orders and Deliveries

Orders and deliveries already store prices ex-tax. They will be updated to include the following fields:

• Unit Price (Ex-Tax) - this will duplicate the existing Unit Price

• Tax Rule

8. Supplier Charges

Supplier invoices and credits already store unit prices tax-exclusive. They will be updated to include the following fields:

• Unit Price (Ex-Tax) - this will duplicate the existing Unit Price

• Tax Rule

9. Reports

In order to support practices that change their tax rule, reports will need to support both PER_LINE and TOTAL tax rules.

This affects:

• Customer Invoice

• Counter Sale

• Customer Credit

• Estimate

• Order

• Delivery

• Supplier Invoice

• Supplier Credit

10. Database Changes

This project will require a database schema change to add four new columns to the financial_acts table:

• fixed_amount_ex_tax - the fixed price, tax exclusive

• unit_amount_ex_tax - the unit price, tax exclusive

• total_ex_tax - the total, tax exclusive

• tax_rule - one of PER_LINE or TOTAL

The existing columns, fixed_amount and unit_amount will be used to store the tax inclusive fixed and unit prices whilst the total column will store the tax inclusive total.

11. Data Migration

Existing databases must have the schema changes applied, and the new tax-exclusive fixed/unit price and total columns must be derived from existing data.

Key to the migration is the requirement that existing totals and tax totals must not change.

For existing Customer Invoice, Counter Sale, Customer Credit, Estimate, Order, Delivery, Supplier Invoice and Supplier Credit acts, the tax rule will be set to PER_LINE.

11.1 Charge Data Migration

Tax exclusive fixed and unit prices will be derived:

• if the tax total is 0:

  • the exclusive fixed and unit prices are the same as the tax inc prices

  • the tax exclusive total is 0

• if the tax total is non-zero:

  • the fixed and unit prices will be derived from the product tax rate i.e.

    the ex-tax total is:

NOTE: due to rounding, there may be a difference calculating the tax amount using the tax-ex prices.

11.2 Estimate Data Migration

Estimate tax exclusive prices will be derived using:

11.3 Order, Delivery and Supplier Charge Data Migration

Order, Delivery, Supplier Invoice and Supplier Credits tax exclusive prices and tax ex totals will be derived using:

12. Exclusions

This project will not include the following:

• changes to ESCI to support TOTAL tax rules
  To date, ESCI has only been implemented by Australian suppliers. Should it be implemented in other jurisdictions, then it will need to incorporate the other tax rules.

• region based taxes
  In many jurisdictions, the customer's address determines the sales taxes that apply. That is out of the scope of this project.

13. Sample configurations

14. Practice Implications

Practices currently using the PER_LINE tax rule can change to TOTAL tax rules.

Charges, Estimates, Orders and Deliveries generated prior to migration will continue to use the PER_LINE tax rule. When printed, these will use the report with the PER_LINE style.
See Also

See https://developers.google.com/checkout/developer/Google_Checkout_XML_API... for a discussion of Google Checkout's approach

Note that Google Checkout specifies a PER_ITEM calculation for the UK, but it appears that PER_LINE is sufficient.