Incorrect Calculation of 1099 History

Posted by Jen Bieker on June 15, 2015


While recently working with a customer to help validate their 1099 reports to the vendor AP information I uncovered a flaw in the way that Acumatica calculates 1099 History.

Normally, if a vendor is configured as a “1099” vendor, one could expect that the 1099 information would match the payment history for that specific vendor for a given year.  In this particular case though, the vendor payment history was not matching the vendor’s 1099 information.

At first glance the obvious answer was that some records were not being flagged correctly as 1099 data but after further investigation I was able to account for all AP transactions in the 1099 data.  So where was the problem with the numbers not matching originating from?

For this I had to dig into the actual transactions which uncovered the mentioned flaw:  Acumatica was not properly calculating 1099 records for any “Debit Adjustments” made in the system.

To understand the overall logic here it is important to understand how Debit Adjustments work in Acumatica.   In most ERP/Accounting systems I have worked with, a Debit Adjustment is more or less a “Virtual Payment”.   When the user enters a Debit Adjustment in the system, they apply this to an open invoice in the same way they would apply a physical Payment.    That being that when an application is made, instead of selecting the PAYMENT as the source, the DEBIT ADJUSTMENT is selected and then open “Bills” are applied against that.   To clear the bill, a physical payment is made against the remaining outstanding balance.   

The 1099 information is usually calculated based off the BILL however, not the payments made.

This can be visualized via the Diagram below:

Batch A –

Debit Adjustment ABCD - $250
LINE 1 - Bill 1234 ($1250) for $250, remaining balance $1000

Batch B –

Check EFGH - $1000
 LINE 1 - Bill 1234 ($1500 originally, $1000 outstanding)

1099 Amount for the payment to the vendor - $1000 (amount actually paid to the vendor)

In this case, the Debit adjustment will lower the outstanding amount of the bill but no real check is cut to the vendor. Bill 1234 had a remaining outstanding balance of $500 that is then paid to the vendor via Check EFGH.   The 1099 amount recorded should reflect the $500 that was actually paid to the vendor. This is based off the following formula: {BILL} - {DEBIT ADJUSTMENTS} = {PAYMENTS} = {1099 Amount).  In this case $1250 - $250 = $1000

In Acumatica however, Debit Adjustments are applied along with a Bill to a Physical Payment as diagramed below

Check EFGH - $1000
LINE 1 - Bill 1234 ($1250)
 LINE 2 – Debit Adjustment ABCD ($250)

1099 Amount for the transaction - $1500.

Notice in this situation that the 1099 amount was exactly double the Debit Adjustment amount.   This is due to the flaw in Acumatica that is calculating the 1099 amount of the Debit adjustment as if it’s a payment, not simply lowering the outstanding balance of the bill.   Acumatica is using the following formula to calculate:

{BILL} + {DEBIT ADJUSTMENT} = {1099 Amount}
{BILL} – {DEBIT ADJUSTMENTS} = {PAYMENTS}

This can be seen in the examples below taken from the Acumatica Sample Database:

Check:

AP History:

1099 History:

At this point I took the situation to Acumatica technical support.   There is obviously a problem here and we need to track down the correct fix.    After various emails, Acumatica did confirm that there was a current “bug” in the system where debit adjustments are being incorrectly processed and that it would be resolved in a future build.    I was supplied a “Correction Script” that is to be ran in SQL to resolve the issue in the meantime.

After running the script let’s look at our example same example above:

Check:

AP History:

1099 History:

Now at this point my 1099 History reflects the correct amount which ties to my AP History.

So now we have a confirmed flaw and a workaround till this is fixed in Acumatica.  The important question is, what versions of Acumatica does this effect and the answer here is, ALL OF THEM.  

A quick check into the code for version 4.0, 4.1, 4.2, 5.0 show they are all effected by this flaw.

While it is too late to correct the issue for the 2014 year, this is important to note as to date, it is still not corrected and all 2015 amounts will be wrong as well.

There is two possible corrective actions that need to be taken at year end depending on your situation to correct the 2015 1099 balances:

1) Once the fix is released, upgrade the site to the current build of Acumatica then run the “Validate Vendor Balances” routine for all vendors specifying a Financial Period of 01-2015
2) If you cannot or prefer not to upgrade to the corrected builds of Acumatica then run the “Validate Vendor Balances” routine for all vendors specifying a Financial Period of 01-2015 THEN run the “correction” script that Acumatica provided.

Thanks to Jeff Williams, Software Engineer, for submitting this information.  If you need more information please contact Jennifer Bieker at JBieker@crestwood.com.


Share this:
 
 

Comments:

Add your comment: