Hi, I've been busy this week...
I'm trying to help a client reconcile their QuickBooks Open AR as of 12/31/2012 to GP as of the same date. They've been entering everything in parallel since August and the YE Open AR should tie out. However, I am running into a ton of issues and I'm not sure if I am missing something or the system needs some maintenance.
Example, I have an 2012 invoice that was paid by a check in 2012. In GP, I apply it with a 12/31/2012 date and run the paid transaction removal with a date of 12/31/2012. However, when I run my HATB both the check and invoice still appear as open. When I do a customer inquiry, they are both in history and applied properly.
Do you know if, even though I set the dates as 12/31/2012, the transactions wouldn't clear because we are in 2013? Again, all dates were set to 2012.
Any help is appreciated.
The HATB calculates its balances based on the apply date. What was the apply date of the check? It will default to the date the check was applied regardless of the date on the check. So, if the transaction was applied in 2013, it will show open as of 12/31/2012
Does this help?
Leslie Vail, CPA, MVP, MCT, MCITP, MCP, MCITSASCI, Inc. * PO Box 600965 * Dallas, TX 75360 * 972-814-8550 * email@example.com
Thanks for responding!
I've made sure that none of the transactions or apply dates are in 2013. They are dated 12/31/2012. Since I am using a test database, I've been able to restore and try it more than once to make sure that all the transactions are dated correctly.
I've opened a case with Microsoft and will see what they have to say. I'll follow up with the results.
Thanks again Leslie!
Boy, I will follow this post to see what the answer is. Thank you so much for being willing to share it with us!
OK.... so Microsoft support has confirmed that I have records in RM20201 that also exist in RM30301 where the CURTRXAM = 0 in RM30101. In other words, there are some fully paid transactions in history where the payments still exist as not fully applied in RM20201.
Since I am not an expert is writing select statements that query multiple tables, perhaps someone could help translate this into query langague for me....
"IF the Payment is referenced in the APFRDCNM field of the RM30201 table AND the Payment is referenced in the APFRDCNM field of the RM20201 table AND the Payment has a ‘0’ CURTRXAM in the RM30101 table THEN DELETE"
Of course, I don't want to delete anything until I review it first, so I can edit that out. Also, yes, I am doing this all in test before production.
Any help with the SQL Query is greatly appreciated.