Dynamics Community Forum Thread Details

I put in a bunch of work to set up automation for weekly reconcile routines in the Sales, Purchasing, and Inventory modules in my GP 2016 R2 environment, so I figured I'd share it if anyone else wants to try it out.

This routine does the following steps:

Check that some required directories exist, and create them if they don't.
Build a GP Macro that will be used to Auto Login the GP client.
Build a GP Macro that will run after startup ONLY if Inventory Reconcile has been enabled but the script detects that someone is posting inventory. This is essentially an "abort" macro.
Build a GP Macro that will run after startup and perform any or all of the desired reconciliation routines that you have chosen to run.
Build a SQL script that will enable one of the two above GP macros to be set as an Autorun macro depending on options chosen and conditions found.
Execute that SQL Script
Start the GP client with the Auto Login macro to kick off the whole process.
Rename the output reports with today's date.

This script is set up so that it can run unattended, and runs well via standard Microsoft Task Scheduler.

Prerequisites:

A GP user account for the script to run as. I created a new one called "AutoReconcile" and made it a POWERUSER. I supposed you could go through the effort to figure out exactly what permissions are needed to run the reconciles, but I didn't bother. Log in as that user, and set the desired company database to be the default database, and set up the home page options as desired. Since no one was ever going to see this, I chose "Basic Home Page."
A windows service account that has the proper access to the GP SQL Server instance for the script to run as, and with permission to the folder where the script is stored.
Microsoft's OSQL.EXE tool installed. Since I am running this on the GP server itself (recommended), it is there, but if you chose to run it on a client machine, you'd have to make sure.

Here are my suggested implementation steps:

Save the script as a .CMD file in the directory of your choice. I have it saved as c:\AutoReconcile\AutoReconcile.cmd.
Make sure that the @BaseDir variable reflects the folder that you chose to save it in.
Review the file name variables and change as desired.
Review the path variables and change as necessary.
Review the modules that you want the reconciliation to run for. In my environment, the sales one runs in about 7 minutes, the purchasing one runs in about 90 minutes, and the inventory one takes many (7 or so) hours, so while testing, I just enabled the sales one to start. Then I tested each one separately, and only then did I enable all three.
Set your GP Login credentials, server name, and GP database name in the proper variables.
Turn on test mode and run the script manually under your account from an administrative command prompt. Review/sanity check the macros and SQL files that it creates (It will tell you where they were saved).
Turn off test mode and run the script manually under your account from an administrative command prompt. It should create the script, start GP, Auto Login, and then run the reconciliations that you chose.
Review the reports that are created in the reports directory. The script will tell you where they are located.
Log onto the machine where the script will run with the windows service account created in prerequisite #2 and run the script manually from an administrative command prompt to verify that it works correctly.
Once it has been tested, create a schedule task set to run under the windows service account, whether the user is logged on or not, with elevated privileges, at the time of your choosing.

NB: The reconcile macro was created in my environment with the default report destinations set for each of the reconciles. For each report, it deselects "Screen" and "Printer" as necessary, and selects "File" of type "Text File" and enters the destination file name. I believe that my defaults are the standard GP defaults, so these should work for a brand new "default" GP account, but YMMV. Some minor tweaking may be necessary if my assumption is incorrect.

Feel free to ask any questions if you have them, and I hope this helps someone.

I can't seem to get a code block to work correctly here, so here's the file:

https://drive.google.com/file/d/1M6kEEq7sx5urV54r-khjdXmuTkC-gscP/view?usp=sharing

Glenn Sullivan