NetSuite Issue: NetSuite not calculating sales tax correctly. Results in open invoices and unapplied payments of 0.01 on SuitePOS transactions

NetSuite Issue: NetSuite not calculating sales tax correctly. Results in open invoices and unapplied payments of 0.01 on SuitePOS transactions


UPDATE
According to NetSuite, fixes will be released on a case by case basis to accounts that experience the issue. Please log a case directly with NetSuite and reference defect 580567 (and 563147).
This is confined to accounts that use NetSuite's SuitePromotions bundle.
In some accounts (typically on transactions with discounts and accounts that are using NetSuite's SuitePromotions bundle ), NetSuite is mathematically calculating the sales tax wrong (not enough precision) and therefore the invoice total is over/under by 0.01. 

If over, the invoice is left open; If under, there is an unapplied payment.
The payment record will be correct
The long term solution is for NetSuite to fix this discrepancy across all accounts. 

According to NetSuite, fixes will be released on a case by case basis to accounts that experience the issue. Please log a case directly with NetSuite and reference defect 580567 (and 563147).  

Alternatively, Avatax could be implemented to avoid the issue and/or you remove the NetSuite SuitePromotions bundle.

In the meantime, past transactions will need to be fixed on an initial sweep and then this needs to be repeated, on an on-going basis (periodically) until the fix is applied.

Unapplied Payments Correction

Summary

This solution centers around creating a journal to increase AR and apply that journal as a payment to the transaction. 

Correction Process

Create a summary saved search (grouped by customer) to report all the SuitePOS un-applied payments. There is no prior period impact.

Summary Saved Search:


Using the results from the Summary Saved Search, manually create a Summary Journal entry (with a line for each customer) to record the AR rounding gain/loss. Make a note of the Internal Id of the Journal.

Summary Journal Example:

Create a Detailed Saved Search (no grouping) based off the Summary Saved Search to identify all the SuitePOS un-applied payment.

Detailed Saved Search:


Using the results from the Detailed Saved Search, export to a CSV file, populate the JE Internal ID column with the Internal Id of the Journal (from above).

CSV file:


Perform a NetSuite CSV import using the CSV file. This will then update the payment records and apply the journal to individual invoiced transactions.

Import screens:

 

Open Invoice Correction

Summary

This solution is centered around creating a summary credit memo (for the current period) and applying that credit memo to the open invoices for a particular customer. There is no prior period impact.

Correction Process

Create a Detailed Saved Search (with specific custom labels, sorted by name) to report all the SuitePOS open invoices.

Detailed Saved Search:


 
Create a non-taxable, for sale item (non-inventory or service) that will be used to adjust the open invoices. This item points to a "Rounding Gain/Loss" GL account. Make a note of the Item Internal ID.

Create Item:

Using the results from the Detailed Saved Search, manually modify the CSV file as follows:

External Id - This field (which must be unique in NetSuite) is used by CSV import to identify a break in credit memos. Typically you would want one credit memo per customer. 

CSV file:


Perform a NetSuite CSV import using the CSV file. This will then create a credit memo for the customer and apply that credit memo to each invoice that is open for that customer.

Import Screens:

 




Field Mapping - Special Instructions:

Location Internal Id - Ensure you set the Reference Type to Internal Id by clicking on the pencil icon.
Date - Ensure you set this to the date you want the credit memos created (typically the day you execute the import)
Item - Select the correction item created
Quantity - Set this to 1

Important Information

  • The end-customer is responsible for this and the associated outcomes.
  • Make sure you are familiar with NetSuite Administration and consult with your Accounting Department to make sure they are happy with the procedures.
  • Test these procedures before fully deploying them (maybe in a sandbox, using one customer correction).
  • It is possible to script this using a combination of  scheduled workflows and scripts. 
  • The solution does not involve having to open up accounting periods.
  • If accounting periods have been closed and these transactions have been adjusted so that there are no open invoices or un-applied payments, that accounting period needs to be filtered out.
  • The CSV import files can become large. If you hit NS import limits you will need to break these files up.