General Ledger

Dynamics 365 Business Central – Why didn’t the Reverse Transaction function in the General Ledger Entries page reverse all my General Journal?

Gavin Whittaker3 Comments

Introduction

A client reported that when reversing a large journal via the “Reverse” option in the “General Ledger Entries” page only part of their original General Journal was reversed?

This stumped me and forced me to dig deeper into how the journal was posted. On investigation it seems the journal lines on the journal had been grouped, and each group has been given a different “Transaction No.” in the G/L Entry table. Then, when the reversal option had been selected, only those lines with the same “Transaction No” had reversed.

Its the first time I’ve come across this behaviour in one distinct journal, so in this post I’ll demonstrate how this happened and the correct way this should have been reversed. (i.e. I’d expect this for different document numbers but not the same document number)

The Journal

The clients journal contained many lines however I can recreate the scenario with a much simpler journal.

Below is a journal with five lines. All the lines have the same Posting Date and Document number and the journal balances to nil. This is essentially one journal which we want to post as one unit.

I’ll now post the journal and go and view this in the General Ledger Entries page. I filter on the document number and I can see the full journal. This is great and just what I expect 🙂

The Reversal

I now realise I’ve made a mistake and want to reverse the whole journal. Therefore, while still in the General Ledger Entries page, I select the top line and choose “Process > Reverse Transaction” from the menu however it only gives me the option to reverse a portion of the journal? (the first three lines of the original journal)

So what has happened here? Why is the system not offering to Reverse all the entries on this Document Number?

The Transaction No. column

As shown on the screen shot above it seems the “Reverse Transaction” option pulls back the G/L Entries based on a “Transaction No.”?

I’ve never seen this column before, and when I try and add this via personalisation to the “General Ledger Entries” page, its not an available column?

I’ll therefore dig deeper and look at this document number directly in the the G/L Entry table, and there I can see the journal has been grouped and each group has been given a separate “Transaction No.”

So now the question is why has a journal with the same Posting Date and Document Number been broken up and given two separate “Transaction No.”?

The answer lies in how I posted the journal. If I look back at my original journal I’d inadvertently balanced the journal in two sections.

It seems even though the journal lines have the same posting date, and more importantly the same document number, the system gave these a different “Transaction No” based on how its balanced part way through.

Finally, it seems the “Reverse Transaction” function pulls back the entries based on this “Transaction No.” which causes a complication if I wanted to reverse the whole Document Number.

Quickly Reverse all Entries

So how do I reverse all the lines?

In my example its quick and easy as the journal is small. I could stay in the “General Ledger Entries” page and select one of the first three lines, and click “Reverse Transaction” and then select the fourth line and use the same “Reverse Transaction” option. However what happens if the journal contained many lines that had been grouped into lots of different “Transaction No.”?

In this scenario you can reverse via the G/L Registers page.

As the G/L Register contains all the G/L entries in my journal I just click click “Reverse > Reverse Register” as per below

This gives me all the G/L Entries in my journal that I can then reverse.


Conclusion

It was great investigating and finding out more about how Business Central works in the background. In some ways you could argue this behaviour is advantageous, as you have the opportunity to only reverse part of one huge journal.

My biggest takeaway from this is when reversing an entry via the “Reverse Transaction” in the “General Ledger Entries” page ensure all the expected lines are being reversed.

Also, if you do want to reverse all the journal lines consider using the “Reverse Register” function on the “G/L Register” page rather than “Reverse Transaction”.

Thanks for reading!

Dynamics 365 Business Central – Method and explanation to assist troubleshooting the message “The Posting Date is not within your range of allowed posting dates”

Gavin Whittaker2 Comments

Introduction

When posting Purchase Invoices you can sometimes encounter the error “The Posting Date is not within your range of allowed posting dates” even though the Posting Date on the Invoice is within your allowed range.

In this post I’ll briefly explain a method you can use to find out which Posting Date the system is trying to post into and also why you are getting this message.

** Please note I’ve also written about this here but cover more things in this post 🙂

The Error

A common scenario is you are happily posting a Purchase Invoice that is matched to a Posted Purchase Receipt when you receive the error below:

The first thing to do is check if the Posting Date of the Purchase Invoice does indeed fall within your allowed posting range. You can check your allow posting date range via “User Setup” and compare this to the Posting Date on the document.

As you can see the Posting Date of the document (05/03/21) falls within my allowed posting dates (01/03/21 – 31/03/21) so what’s the issue? Why is the system saying this posting falls outside my allowed range?

The Troubleshooting Method

Before we try and understand why this is happening its a good idea to figure out which date the system is trying to post into. I do this by following the method below:

1) Blank out your posting range in the “User Setup”.

2) Rather than posting the document select to “Preview” the postings as per below. (if you did choose to post the document it would work so be sure to only Preview)

3) If you then drill down on the G/L Entries this will show you the Posting Date the system is trying to use which is outside of your range:

So, now we know the system is trying to post a portion of the GL entries to the date 20/02/21, which falls outside my range of Posting Dates, and this is why I’m receiving the error message. However, before we go changing our allowed posting dates and posting the document, let’s look into why the system is posting back to this date.

A Little background

Before we get into why this happens its useful to have some background on this example. In this example the Sales and Purchasing process followed the following steps:

1) The Item was purchase receipted at a cost of £15.00 on the 10/02/21.

2) The Item was sold on a Sales Invoice on the 20/02/21.

3) The Purchase Receipt is being Purchase Invoiced on the 05/03/21 but the cost changed from £15.00 to £17.00 on the Item.

The key point here is point 3. The costs of the Item have changed from when they were purchase receipted (point 1) and subsequently sold (point 2). When this happens a key piece of functionality called “Adjust Costs – Item Entries” makes adjustment postings but more on that later.

Digging deeper

In Business Central when posting a Purchase Invoice that involves Items you are essentially creating two separate postings (registers). This is standard behaviour to support the “Automatic Cost Posting” feature. (more on that here)

One posting is for the purchases side of the transaction, which includes the Account Payable G/L code, and the other is for the Inventory. If you take a look at the G/L entries window you can see these defined in groups by the “Source Code”. The PURCHASES source code is the entries related to purchases such as the Account Payables G/L code and the INVPTCOST is for the Inventory postings.

In our example one of the INVPTCOST set of postings is causing an issue. For some reason this has been back dated to the 20/02/21. So why have these entries been created?

Adjust Costs – Item Entries

These entries have been created by the “Adjust Cost – Item Entries” batch job that runs when you post the Purchase Invoice (assuming you have “Automatic Cost Adjustment” set to Always).

As the costs have changed from £15.00 on the Purchase Receipt (point 1) to £17.00 on the Purchase Invoice (point 3) adjustments need to be made as the Item was sold at a cost of £15.00 (point 2). Therefore the Cost of Goods Sold and Inventory postings need adjusting to the new Cost.

Choosing the Posting Date for the Adjustment

So why has the system selected the date of 20/02/21 for these postings?

The system determines the earliest date for Inventory adjustments based on the Allowed Posting Dates in the General Ledger Setup. (and also Inventory Periods if they are in use. You can read more about how the Posting Date is selected when Inventory Periods are involved via this Microsoft article.)

Therefore if we check our General Ledger Setup the Allowed Posting Dates are as follows:

In this configuration the earliest date we can post an adjustment is the 01/02/21.

In our scenario the Inventory we need to adjust on the Sales Invoice has a posting date of 20/02/21. Looking at our General Ledger Posting Setup we allow postings to this date so system creates adjustments with a posting date of 20/02/21 and as a consequence of this we receive the error “The Posting Date is not within your range of allowed posting dates” as our User Setup only allows postings between 01/03/12 and 31/03/21.

The Fix

Now we know why this is happening, and how the date is selected, we can choose to fix this by either adjusting our User Setup to allow posting in February or change the General Ledger Setup.

In my scenario I’ve changed the General Ledger Setup to only allow postings from 01/03/21 through to 31/03/21 which matches my User Setup. So now the earliest posting date that can be used for the Inventory Adjustment is 01/03/21.

If I now preview postings you can see the system will make the adjustment on the 01/03/21.

I will now be able to post this transaction as the Posting Dates on all entries are within my Allowed Posting Dates.

Conclusion

This error crops up a lot when people sell items prior to Purchase Invoicing them and costs change. Hopefully this article will help explain why this is happening and how you can troubleshoot it.

Thanks for reading!

Dynamics GP – 10 tips when running a General Ledger Year End on a large SQL Database

Gavin WhittakerLeave a comment

Introduction

A few weeks ago I assisted with a General Ledger year end for a GP user with a very large number of journals and consequently a very large SQL database.

Usually a year end is a fairly straight forward routine, however as this user has such a large database, along with additional SQL bits and pieces for disaster recovery and bespoke reporting, it required a little more thought to ensure things went as smoothly as possible.

In this post I thought I’d share the steps I took to hopefully assist anyone else facing a similar situation in the future.

Here goes…..

1) Do the basics first.

By basics I mean following Microsoft’s general Year End prerequisites which can be found here.

I find the most important steps are ensuring you have a good backup and also ensuring the general ledger codes have the correct posting type as this can be a pain to correct post year end.

2) Ensure you have enough disk space for the SQL Data and SQL Log files.

This usually isn’t a consideration when running the Year End however given the size of the data its something that needs planning when dealing with GL tables in the 100’s of GBs.

Microsoft’s official guidance on disk space is included in the Year End documentation that I’ve linked to above however I’ve copied the section on disk space below:

During the year-end closing routine, all the records that will be moved are put in a temporary table before they are moved to the GL30000 table. You must have free disk space that is equal to the size of the GL20000 table to perform the routine.

To find the current size of the GL20000 you can use the sp_spaceused system stored procedure i.e.

However I’d also add that when dealing with large data volumes you also need to plan for extraordinary SQL transaction log growth as well.

For example the SQL transaction log grew to 240GB when I did this particular year end. This is because various statements including the INSERT statements into GL30000 are completed in one SQL transaction. Therefore no matter what the SQL recovery model is of your database, the log file is going to grow while the process completes. This is standard behaviour so SQL can roll the whole transaction back should any issue occur.

3) Remove any Database Mirroring or Availability Groups.

In my scenario the user has database mirroring for disaster recovery. I could have left this on however this would result in a lot of transaction log being sent over the wire while the Year End ran potentially doubling the length time it takes. I always see the amount of time something takes, as the size of the window of opportunity for something to go wrong. The quicker I could get the Year End process to go, the less chance I had of something going wrong. On the down side it does mean I had additional configuration to complete post year end, but I was fine with this.

4) Remove any custom indexes

If you have any additional custom indexes on GL20000 and GL30000 its best to remove them. Again its not a problem if you don’t remove them, however it can slow things down and also increase the amount of transaction log that SQL will produce. This is because if you are DELETEing and INSERTing data into tables the indexes also have to be updated. Therefore the less indexes you have the better. This requires additional space and also additional log to record the operations.

**Don’t remove any of the standard indexes though.

5) Stop the SQL Agent.

Maintenance tasks like reindex routines sometimes use the SQL agent to run. As I suspected the Year End will run for a very long time, I stopped the SQL agent to prevent this from happening. Its always best to check that critical operations won’t be affected by this though. If so just disable any SQL jobs that run maintenance on the Dynamics databases.

6) Temporarily revoke permissions for any Reporting Logins.

Ideally I’d like to set the database to single user mode while the Year End ran however as I wanted to monitor the process I didn’t want to restrict this too much. I therefore disable logins that I knew were used for reporting in case anyone sets off a large report in the middle of the year end.

7) Ensure SQL log growth on the database is set sensibly.

As I mentioned above the SQL transaction log is going to grow during this operation regardless of the recovery model. Therefore go to the properties of the SQL database and ensure the growth is set correctly, and its set in MB’s rather than a percent. I set it to grow 10GB increments for this particular year end process.

8) Use SQL monitoring scripts

I like to nosy under the hood when running most things but I especially want a sneak peak on the General Ledger Year End. My tool of choice for this is Adam Machanic’s awesome free monitoring tool sp_whoisative. This is a free script you can use to monitor activity in SQL. Using this I can pretty much see at what point the Year End was up to. i.e. INSERTing the data to GL30000 etc.

**If you didn’t know about this free script, and this is all you take away from this blog I’d be happy :). Its a really cool free tool you can use when troubleshooting all sorts of things in SQL i.e. blocking etc.

I also want to monitor my database and log growth as the process is running. If I monitor this I can predict when the log will run out of space and need to grow. I then check the disk space to ensure the growth will happen smoothly. To do this I use this script 

SELECT DB_NAME() AS DbName, 
name AS FileName, 
size/128.0 AS CurrentSizeMB, 
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB 
FROM sys.database_files;

9) Be patient 🙂

One of the most important things is to be patient as this routine can last hours on a large data set. Its also important to never cancel the operation by manually closing GP down even if it says “Not Responding”. If it doubt you can run the sp_whoisactive stored procedure and check what’s happening on the server.

10) Once complete

After a huge sigh of relief I performed an additional backup and then started recreating the indexes, setting up the mirroring and restarting the SQL agent. I also shrank the SQL transaction log back down to a reasonable size.

Conclusion

I hope you find the information in this post useful. Although you may not need to follow all the points I outlined, I think the most important are disk space for the SQL transaction log, monitoring and lots of patience 🙂

Thanks for reading!