See attached PDF of the JC Billing History Report. For now, you can ignore the last 4 lines where we were trying to fix the issue and were unsuccessful at doing so. Our customer uses Job Cost and AIA Job Cost Billing. At the time these invoices were created, they were on Sage 100 v2017 and are now on Sage 100 v2019 PU2.
I believe the crux of our issue occurred on 6/13/2019, invoice 1861E. It appears they billed out a line item, but also reallocated retention from other invoices into this one. I think the GL Posting for this was as follows:
Debit AR: $40,287.65
Credit Retention: $4,355.15
Credit Revenue: $35,943.50
The invoice itself was only given a total of $35,943.50, though it probably should have been the full $40k.
On 1/27/2020, this invoice is paid along with a few others, and this is where the retention balance comes into play. On the Cash Receipt, the Retention applied between these invoices came out to our ending difference of $3673.43 (See Jan 2020 Cash Receipt.jpg attached). I think the GL Posting for this was:
Credit AR: $38,490.47
Debit Retention: $4,344.15
Credit Retention: $8,017.58
Debit Cash: $42,163.90
Later, Invoice 1861G is created to adjust the remaining retention balance into a billable AR Invoice. The amount of the invoice was for $9,817.44. Their customer only paid $6,144.01 of the invoice and it leaves them with the $3,673.43 balance. It's almost as if the invoice should have been $6144.01, but all the other totals lined up which made it seem like it was correct. After running through all of the GL Transactions, I also believe their short on the AR credits by this amount, and short on debits by the same amount.
I have tried next to everything I can think of besides using DFDM to fix data in order to resolve the issue. I have hesitated to do this due to the supposed discrepancy in the GL and only want to use that in a last ditch effort to get the Job Billing History Report to show correctly. Unfortunately, a simple zero dollar cash receipt does not resolve the issue.
Anyone know Job Cost and AIA Job Cost Billing to know how to resolve this? Thanks,
Chris
------------------------------
Chris Love
Accounting Systems, Inc. (ASI)
------------------------------