Sales Tax On Collection

(This is a copied from keystone help wiki - removing tech info from keystone help version)

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 tabale

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 result from invoices.

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

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.

Discount Tax Adjustments

When a discount is posted to an invoice, two distinct transactions can occur...

• For systems with "Sales Tax Based on Collections" set, the portion of the invoice paid will be reported as paid along with the corresponding portion of sales tax due.
• For all systems, a sales tax adjustment calculation will be performed to determin if a tax reduction applies.

Discount Tax Fields

At the time of invoicing, the discount tax fields are calculated and entered into an "I" tax transaction record whenever a discount is offered. Every line of the invoice is analyzed.

• If either the "Discount Includes Tax" option is set in Tax Rate Maintenance, or a discount is on an exempt line, then the whole discount will be entered into the discount exempt line.
• If the "Discount Includes Tax" option is set in Tax Rate Maintenance then all discounts on taxable lines will included in the Disc Taxable and the Disc Tax fields. (The discount is apportioned based on the tax rate between the Disc Taxable and Disc Tax fields)

For example a 1000 sale with 5% tax, $600 taxable and $400 exempt totals to 1030. If a $21 discount is offered on taxable items then then Disc Exempt= $0, Disc Taxable =$20.00 and Disc Tax=$1.00

For example a 1000 sale with 5% tax, $600 taxable and $400 exempt totals to 1030. If a $20 discount is offered on exempt items then then Disc Exempt= $20.00, Disc Taxable =$0.00 and Disc Tax=$0.00

Calculating the Adjustment

At the time a discount is taken, the system calculates the percentage of discount taken, and determines the amound if discount tax adjustment. By applying bounds checking, the total amount of discount taken on an invoice is limited to between 0-100% of the original discount offered.

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.

Diagnostics

Diagnostic Detail:

select 
x.tax_locality "Tax Loc",
case trx_type 
when 'I' THEN 'Invoicing' else 'Csh Post' end "Source",
x.trx_date,
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,
EXEMPT_1 + EXEMPT_2 + EXEMPT_3 + EXEMPT_4 + EXEMPT_5 + EXEMPT_6 + EXEMPT_7 + EXEMPT_8 + EXEMPT_9 "Exempt", 
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",
disc_taxable + disc_exempt + disc_tax "Disc",x.seq_no,x.cash_session_no,x.cash_trans_no
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" 
order by 1,2 desc,3,4,5

This works nicely in datascope reports - Put Tax,Taxable,Exempt and Cash Match all as Data column. Set Row headers as Trx Type, Allow Code etc.

Cash Received vs Sales Tax Cash Recorded: Quickly find total cash recieved compared to the total received recognized by the sales tax table. The most likely issues are open credits and "out of bounds" posting to standard invoices (like when you post $1100 to a $1000 invoice).

SELECT 'CSH' "SRC",
ch.post_date,cd.ar_session_no,cd.ar_trans_no,cd.session_no,cD.trans_no,cd.line_no,cd.credit_amount
,i.invoice_no,i.invoice_type
 FROM ARTCSHHD CH
INNER JOIN ARTCSHDT CD ON CH.SESSION_NO=CD.SESSION_NO AND CD.TRANS_NO=CH.TRANS_NO
INNER JOIN ARTINV I ON I.SESSION_NO=CD.AR_SESSION_NO AND I.TRANS_NO=CD.AR_TRANS_NO
WHERE POST_DATE BETWEEN '2/1/19' AND '2/28/19'
union all
SELECT 'TAX',
x.trx_date,x.ar_session_no,x.ar_trans_no,x.cash_session_no,x.cash_trans_no,x.cash_line_no,
x.TAX + x.TAXABLE +  x.EXEMPT_1 + x.EXEMPT_2 + EXEMPT_3 + EXEMPT_4 + EXEMPT_5 + EXEMPT_6 + EXEMPT_7 + EXEMPT_8 + EXEMPT_9 TOTAL
,i.invoice_no,i.invoice_type
 from arttaxtrx x
INNER JOIN ARTINV I ON I.SESSION_NO=x.AR_SESSION_NO AND I.TRANS_NO=x.AR_TRANS_NO
WHERE TRX_DATE BETWEEN '2/1/19' AND '2/28/19'  AND X.TRX_TYPE NOT IN ('I')

Use in datacsope. Put "Credit Amount" as data column. Src as the column Field. Set Row field to trx date, Optionally add Invoice number etc to drill further.

Sales Tax History for a Customer/Invoice:

select i.cust_no "Cust:",i.invoice_no "Inv#:",
i.credit_amount "InvAmt:",i.debit_amount "Paid:",i.sales_tax_due "InvTax:",I.DISC_OFFERED "DiscOfrd:",
tx.taxable,tx.tax,
EXEMPT_1, EXEMPT_2,  EXEMPT_3, EXEMPT_4, EXEMPT_5, EXEMPT_6, EXEMPT_7, EXEMPT_8, EXEMPT_9,
Case trx_type 
when 'I' then '.Inv' when 'C' THEN 'Cash' WHEN 'A' THEN 'Allow % Method' WHEN 'D' then 'Disc' when 'T' THEN 'Allow Tax Method' end "Trx Type"
  from artinv i
inner join arttaxtrx tx on  TX.ar_session_no=I.SESSION_no and tx.AR_trans_no=I.trans_no
where i.cust_no=:cust_no and invoice_no=:inv_no

Use in query export. Set the break and group of invoice fields to 1. Set total on all detail fields but "Trx Type"