Sales Tax Handling in Keystone 2.3

Sales Tax handling in Keystone 2.3 builds on the changes from Keystone 2.2, where sales tax transactions are created for all invoices. In 2.3, sales tax changes resulting from cash posting transactions are also included in the tax transaction table. This is the latest design to accurately calculate sales tax based on collections, and for reporting sales tax adjustments for customers who report sales tax on invoices.

ARTTAXTRX: The Sales Tax Transaction table

SEQ_NO : Unique number for every transactions

TAX_LOCALITY :

TRX_DATE :Date of Invoice or Payment

TRX_TYPE : I for Invoice, C standard cash, D discount, A: allowance, and T for Tax adjustments

TAXABLE :Taxable amount of Invoice

EXEMPT_1-9 :

TAX : Tax from invoice, Tax collected for C record, Tax Adjustment for T records

DISC_TAXABLE: Taxable portion of a discount.

DISC_EXEMPT: Exempt Portion of Discount

DISC_TAX :

AR_SESSION_NO :Points to invoice

AR_TRANS_NO

CASH_SESSION_NO: Points to Cash Transaction

CASH_TRANS_NO

CASH_LINE_NO

I Transactions

I transactions are created during the Print Invoices process.

For Tax on Invoicing systems, this is the main source of data for sales tax, indicating the exempt, taxable and tax amounts of the sale. The discount portion of the record is used as a basis, since discounts are only reported as taken.

For tax on receipts systems, the values in the I record work as the basis for all collection calculations.

Example:

SEQ_NO

SESSION_NO

TAX_LOCALITY

TRX_DATE

TRX_TYPE

AR_SESSION_NO

AR_TRANS_NO

CASH_SESSION_NO

CASH_TRANS_NO

CASH_LINE_NO

TAXABLE

EXEMPT_1

EXEMPT_2

…

EXEMPT_9

TAX

DISC_TAXABLE

DISC_EXEMPT

DISC_TAX

400

2568

CT

1/9/2009

I

2568

7

30

0

265

0

2.1

2.34

10

0.16

This is an example I record with taxable and exempt sales, as well as taxable and exempt discount.

C Transactions

C Transactions include:

Cash Payment

Discounts (Note: if Discounts reduce sales tax, the tax incurred from paying the invoice will be reported as tax incurred, and the the resulting tax adjustment will be reported separately)

Allowances that are not exempt, and portions some Exempt Alowances.

C transactions are consider taxable to customers who pay tax on receipts, but not to customers who pay tax on invoicing.

T Transactions

T Transactions are used to represent a change in Tax, Taxable and Exempt amounts, resulting from either a "Exempt" allowance or a "Tax Only" Allowance.

T transactions are considered as a sales tax adjustment to customers who pay tax on invoicing, but ignored for customers who pay tax on receipts. The changes from T transactions affect future C transactions, and in come cases will result in adjusting C transactions.

A Transactions

A transactions are the allowance component for a cash posting Allowance.

Allowances that have no tax adjustment are treated similar to a Cash transaction.

Allowances that affect Tax Balances result in adjusting "C" type transactions to bring the tax collected balance in line with the invoice. One "C" transaction reverses all A, C and D transactions, then another reapplies the A,C and D total with based on the new adjusted tax balance.

D Transactions

For sales tax based on collection Discount transactions are treated similarly to cash transactions.

Discount Tax Adjustments are handled as a separate tax calculation.

Calculations

Common Cash Posting Case

For most cases, the calculation for tax collections is a simple ratio: Tax Collected=Invoice Tax X (Amount Paid / Invoice Gross) Taxable Sales Collected=Invoice Taxable X (Amount Paid / Invoice Gross)

For example a 1000 sale with 5% tax totals to 1050. If you receive a $500 payment then:

Tax Collected=50*(500/1050) --> $23.80952 which rounds to $23.81.

Taxable Sales Collected=1000 x (500/1050) --> $476.1905 -->$476.19.

So the numbers reported to the state would be a $476.19 sale with $23.81 tax.

For a partially taxable invoice the same rules apply, but now we'll need to calculate the exempt sales collected too. Exempt Sales Collected=Invoice Exempt X (Amount Paid / Invoice Gross)

For example a 1000 sale with 5% tax, $600 taxable and $400 exempt totals to 1030. If you receive a $500 payment then:

Tax Collected=30*(500/1030) --> $14.56311 which rounds to $14.56

Taxable Sales Collected=500 x (600 /1030) --> $291.2621 -->$291.26

Exempt Sales Collected=500 x (400 /1030) --> $194.1748 -->$194.17

So the numbers reported to the state would be 291.26 taxable, 194.17 exempt sales with $14.56 tax.

Boundary Checking

Sales tax boundary checking is the method the system never reports either negative more tax than was originally charged on an invoice (for positive invoices). This is to handle cases where invoices are either overpaid, or where a negative payment is applied to a postive invoice.

The basic rules (for a postive invoice)

• Tax collected is between 0 and tax charged.
• Taxable is between 0 and invoice sale amount (Original Invoice Amount lest Invoice tax charged).
• Exempt total is between 0 and invoice sale amount.
• Exempt 1-9 must be between 0 and invoice sale amount.

Diagnostic Notes

One of the key things to understand is that the sales tax based on collection calculation is more complex than it might seem at first. We had to enhance the logic behind this over the years to take into account all type of scenarios such as overpayments, negative payments etc. We always need to make sure we never attempt to calculate tax that wasn’t originally on an invoice. Therefore we only record transaction that are between 0 and 100% of the invoice amount.

If you have an invoice for $100 with 8$ of tax If you pay $108 we will report $100 Taxable and $8 tax. …you pay $54 we report $50 Taxable and $4 Tax …you pay $200 we report $100 Taxable and $8 tax (we won’t go over 100% of the original amount) …you pay -$50 we report $0 taxable and $tax (you can’t go the negative of the invoice)

ON top of this we keep track of every transaction to date and apply these rules to the accumulative balance.

• You pay $108 on the invoice – ($100 Taxable + $8 tax)
• You then pay $10 more – ($0 Taxable and $0 tax ) because you are accumulatively over the balance.

The bottom line we report NO TAX unless it’s on an invoice.

So this leads to a tricky issue – Open Credits in cash posting. These are $0 invoices with $0 tax. It’s OK to sue Open Credits so long as you do them in a balanced way and either clear them at the end of the month OR understand that any money applied to an open credit will show up on cash posting reports but not on sales tax reports. This can be considered an explainable discrepancy.

Diagnostics

Sales Tax Diagnostics (10/12/17) This works well for single level.

select x.trx_date,x.tax_locality,
case trx_type when 'I' THEN 'Invoicing' else 'Csh Post' end "Source",
i.cust_no,h.check_no, h.check_amount,  i.invoice_no "Inv No", i.debit_amount "Inv Amt", i.credit_amount "Inv Paid", 
case i.invoice_type when 'I' THEN 'Inv' when 'S' then 'Svc Chg' when 'C' THEN 'Opn Crdt' else '??' end "Inv Typ",
Case trx_type 
when 'I' then 'Inv' when 'C' THEN 'Cash' WHEN 'A' THEN 'Allow' WHEN 'D' then 'Disc' when 'T' THEN 'Tax Adj' end "Trx Type",
x.taxable, x.tax, 
TAXABLE + EXEMPT_1 + EXEMPT_2 + EXEMPT_3 + EXEMPT_4 + EXEMPT_5 + EXEMPT_6 + EXEMPT_7 + EXEMPT_8 + EXEMPT_9 + tax "Gross",
case x.trx_type
when 'C' THEN CD.CREDIT_AMOUNT-DISC_AMOUNT-ALLOW_AMOUNT
WHEN 'A' THEN CD.ALLOW_AMOUNT
WHEN 'T' THEN CD.ALLOW_AMOUNT
WHEN 'D' THEN CD.DISC_AMOUNT END "Post Det Amt",
casE trx_type when 'I' THEN 0 ELSE
TAXABLE + EXEMPT_1 + EXEMPT_2 + EXEMPT_3 + EXEMPT_4 + EXEMPT_5 + EXEMPT_6 + EXEMPT_7 + EXEMPT_8 + EXEMPT_9 + tax - disc_taxable - disc_exempt - disc_tax END  "Cash Match",
EXEMPT_1 + EXEMPT_2 + EXEMPT_3 + EXEMPT_4 + EXEMPT_5 + EXEMPT_6 + EXEMPT_7 + EXEMPT_8 + EXEMPT_9 "Exempt",
disc_taxable + disc_exempt + disc_tax "Disc"
from arttaxtrx x
left outer join artcshhd h on x.cash_session_no = h.session_no and x.cash_trans_no = h.trans_no
left outer join artcshdt cd on x.cash_session_no = cd.session_no and x.cash_trans_no = cd.trans_no and x.cash_line_no=cd.line_no
inner join artinv i on i.session_no=x.ar_session_no and i.trans_no=x.ar_trans_no
where x.trx_date between :"Beginning" and :"Ending" 

Compare "Gross to "Post Det Amt." These 2 values should match.

Variations of this query can be used:

• One Cash Posting: (Where cash_session_no = :"Cash Session" and cash_trans_no = :"Cash Trans")
• One Invoice: (Where AR_Session_no = :"AR Session and AR_TRANS_NO = :"AR Trans")
• Or Invoice by cust/invoice number (Where i.cust_no = :"Cust No" and i.invoice_no = :"Invoice No")

Reasons they won't match:

• Posting outside of the 0-100% range of invoice balance
• Posting against an open credit invoice (which is $0 so has a range of $0 to $0)

This works well as a report grouped by 1: Date, 2: Source, 3: Cust, Check #, Amt. In this case the "Cash Match" should match the check amount.

Sales Tax History for a Customer/Invoice:

select i.cust_no,i.invoice_no,tx.*,i.credit_amount,i.debit_amount,i.sales_tax_due
  from artinv i
inner join artcshdt cd on cd.ar_session_no=i.session_no and cd.ar_trans_no=i.trans_no
inner join arttaxtrx tx on tx.trx_type IN ('A','C','T') AND TX.cash_session_no=cd.session_no and tx.cash_trans_no=cd.trans_no
where i.invoice_date = :"inv date"
  union all
select i.cust_no,i.invoice_no,tx.*,i.credit_amount,i.debit_amount,i.sales_tax_due
  from artinv i
inner join arttaxtrx tx on tx.trx_type='I' AND TX.ar_session_no=I.session_no and tx.ar_trans_no=I.trans_no
where i.invoice_date = :"inv date"