When you run the year end routine on the General Ledger in Dynamics GP the system automatically creates a journal to bring forward the balances for the Balance Sheet codes and it also closes all the profit and loss codes to the retained earnings account.
When you look at the header of the year end journal that’s created it shows a source document of BBF (Balance Brought Forward)
However if you take a closer look at the year end journal in smartlist you can see that some of the lines have a source document of “P/L”.
So why the mixture of BBF and P/L source documents for the year end journal?
The BBF entries are created to bring forward the balances of the “Balance Sheet” accounts and the “P/L” entries are created by closing the profit and loss accounts to the retained earnings account. As my demo data has multi currency postings to various profit and loss codes I get one entry per currency.
To confirm the correct amounts have been posted to the retained earnings account I can run the query below in SQL that sums all the amounts posted to the profit and loss accounts for the year 2024, and this reconciles back to the amounts posted to the retained earnings account on the year end journal:
The BBF entries are quite well known but I couldn’t find much information on the P/L source document which is why I created this post.
A quick one but hopefully it will help someone else in the future.
We had an issue raised last week with a client running the “Select Cheques” routine in payables and although it appeared to be running normally it was taking much longer than usual and never seemingly completing.
As usual my first port of call is to run a SQL Profile Trace of the process (should probably be using extended events by now) to monitor exactly what’s happening.
When I setup an SQL trace I generally start with RPC:Completed and SQL:BatchCompleted as per below:
After running this trace I could see that the process seems to be showing a loop where’s its SELECTing data and them attempting to INSERT into the DEX_LOCK table over and over again for the same document:
To investigate further I stopped the trace and added “Errors and Warning” and restarted the trace again
The trace now shows the issue. The system is producing a duplicate key error when trying to INSERT into the DEX_LOCK table.
Because of this it seems to loop back and then try the INSERT again. The error isn’t being reported to the end user so they just think the process is running but taking longer than usual.
As expected when i query the DEX_LOCK table the row_id (42468782 – yes this client posts LOTS and LOTS of payables data) relates to the DEX_ROW_ID in the PM20000 for the document mentioned in the SELECT in the trace:
To fix the issue I deleted the offending row in the DEX_LOCK table as per below and the “Select Cheques” process continued as normal. (I didn’t even have to stop the “Select Cheques” process, I just deleted the row and it went through fine)
I hope this helps anyone else facing a similar issue.