I have a user who requires that I can provide the point in time unmatched transactions to look at what had not been matched when a bank statement was processed. For instance, he would like to view April’s posted bank statement reconciliation in Bank Statement processing and see what was unmatched on the date that this was completed.
I do not know of a way to do this. I have been told this is accounting 101 and that I should be able to provide this for an auditor on demand. i have suggested that current open transactions show if you are on an unposted statement and hit retrieve transactions.
Is this functionality in Epicor and if so how do i access it?
Personally never have seen it. The point in time reporting, especially on the financial side, is non-existent (to my knowledge, I really hope I’m wrong!).
We had a similar request for point in time AR balance, but I couldn’t figure out a way to do it.
I have seen a solution to basically offload data at an interval in SQL to build out a separate database and then would have the ability to run queries/reports off of that database.
OK. Thanks for the feedback. I have a process set up to pull a BAQ on a schedule and can work with that in my local SQL server. I guess I’ll need to find the posted bank statements and set up a tracking table to see if a new statement has been posted, then I can set aside a copy of the SubledgerTransactions data in a historical table in my SQL Server. Not ideal, but will probably be better for an auditor than me asking the guy who posts the statement to copy everything to an excel file when he posts the statement.
We should be able to show the date that those point-in-time open transactions get matched later on, or they will still be unmatched in the system, so I think this may be enough.
Looks like CashBHed has the records I need to look at. I’m grabbing it periodically and comparing the last value I saw posted for the BankAcctID with the current most recently posted CashBookNum.
There’s probably a more elegant way to do this using the CashBookNum already in the SubledgerTransaction table but without watching it over the next month I’m not sure I can say with certainty that the number will increment once the next one posts (there is currently an unposted May entry which may be driving the cashbooknum appearing in my BAQ and I can’t count on that always being opened right away so I’d have to better understand how this all works).
A Report in the reports section with a point in time option would be easier
