If you know anything about NAV, you know it doesn’t really have a hard month end close. So, apologies for the somewhat misleading title of this post.
However, month end close remains a topic for discussion for folks switching to Dynamics NAV from other systems (like Lawson) that have a more traditional, hard period end.
Consequently, clients will ask for my advice on how to implement some level of period end control in NAV. (Yes, NAV does have a year-end process. But I’ll save that topic for another post.)
To help these clients, I’ll walk them through the following period end control options:
- Accounting periods
- Date control in General Ledger Setup
- Date control in User Setup.
In this blog post, I’ll outline these options for you as well. In addition, I’ll share with you a query/report you can use to look for un-posted transactions. (Indeed, for those of you who already understand month end, you may want to skip to the report).
Let’s start with accounting periods. Every accounting period has two options we care about for the purposes of closing: “Date Locked” and “Closed.”
Date Locked means the date cannot be changed. The date is set once the prior fiscal year is closed, and you can’t change it after that.
The meaning of Closed is less obvious. First, it’s only set by the year end process (you can’t update it month by month). And second, it doesn’t really prevent posting.
We can see this by entering an invoice and posting it to December 2016:
We set “Posting Date” to 12/31/2016 and click post. And, guess what? It posts! Even though we had set that date to “Closed.”
If we check General Ledger entries in NAV, we can see the entry:
And if we navigated to the posted purchase invoice, we’d see the record there as well.
Now, let’s make a journal entry and see what happens:
And that posts also!
So, what does “Closed” actually do?
Let’s go back to the General Ledger Entries page and dig a little deeper:
Highlight either record and choose “About This Page”:
Then choose “Table Fields”:
If we scroll down, we see a field called “Prior-Year Entry”—which is set to “Yes” for both transactions:
Now, we can see why we can’t use accounting periods to prevent people from posting.
Date Control in General Ledger Setup
So if we can’t stop people from posting using accounting periods, what other options do we have?
One thing we can do is to control valid posting dates on a global level in “General Ledger Setup.”
Let’s start by looking at this page in Cronus:
We see two fields: “Allow Posting From” and “Allow Posting To.” Let’s set these to 01/01/2017 and 02/28/2017:
Now, let’s try to make the a journal entry similar to the one we made earlier:
When we try to post, we get this error message:
So that’s one way of implementing date controls.
Date Control in User Setup
Of course, the general ledger control we described above is at a global level, which may be helpful at times but generally isn’t granular enough. For example, it’s common to have accounts payable and accounts receivable closed and general ledger open.
In this case, we can set up date controls in user setup.
Let’s say, for example, we’re only processing accounts payable. So, we should only be allowed to post in February. We set up that control in user setup:
Now, we enter a purchase invoice with a posting date of January 31, 2017:
And we get this message:
Which is what we expect. Even though General Ledger Setup is open in January and February, our user profile limits our posting dates to February.
One important point: You can actually do the reverse and grant users a broader date range in User Setup than the range specified in General Ledger Setup. The User Setup date range OVERRIDES the General Ledger Setup data range.
Here, for example, we set the date range in User Setup from December 1, 2016 to February 28, 2017:
Now, if we try to post the same invoice with a posting date of December 1, 2016?
It posts! Even though we’ve limited the date range via the general ledger.
So, the lesson is this—be very careful when you set up allowable posting dates in User Setup.
Creating a Report to Help with NAV Month End
As you can see by my examples, posting dates are ONLY checked when documents actually post, which is NOT good. When we issue financial statements, we want to be sure that nothing is missed. We certainly don’t want checks sitting out there or purchase invoices that haven’t been posted.
To address this problem, I’ve built a SSRS report that allows users to see all unposted transactions before a certain date in NAV. This report covers purchasing (invoice, credit memos, orders being matched), sales (invoices, credit memos, orders being billed), financial journals (General Journals, Payment Journals, Cash Receipt Journals), and item journals.
While it gives detailed data for purchasing and sales, for journals it only gives the number of records (because journals are posted by batch).
With this report, users can chase down all possible open records before they change dates and close off the month.
And here’s an example of the report:
And below is the SQL query used to generate this report, using my generic view names.
(See my post Using SSRS with Dynamics NAV—Creating Views to Write SSRS Queries for more on views.)
And here’s the same SQL query using CRONUS_USA: