I had a couple of requests regarding this from the Intuitive ERP user group so I figured I would throw it in to a blog and make it easier for people to reference.

The Situation:

Our accounting friends want to be able to control the general ledger very closely in an attempt to keep from having to deal with lots of clean-up and problems down the road.  One of the ways they do this is by restricting the dates in which transactions can be entered into Intuitive.  Essentially if the date is not within the allowable range the transaction cannot be entered.

The Problem:

Normally the date restriction is set to restrict to the current month or quarter depending on the organization, regardless at times someone in the finance department may forget to update the end date to push it out to the next month or quarter and that’s when major problems come up.  If the date is not updated on the next business day the system pretty much grinds to a halt as no transactions such as work order release, purchase order release, work order clock on and off, etc. all stop.

The Solution:

Pretty simple fix here, we simply created a SQL Server Agent Job in the IT world that executes monthly to go out and push that end date out to the end of the next month.  Essentially automating the task for the finance group so no one has to remember to do it.  In addition we added strict error logic to the job so if anything fails it will notify a set group of users automatically via e-mail.  This is all configured via SQL Server Mail.

The Code:

You can download the SQL Agent Job script here: http://brntpb.st/A2hlld.  One note: we utilize multiple Intuitive databases so we create multiple steps in the job to update each individual database.  This is perfect for a multiple company or multi-site model.

Enjoy!

-Brent