Scheduling or automating tasks in Microsoft Dynamics GP is a common request, so this example demonstrates a method that can be used to run any process on a scheduled or automated basis using GP Power Tools.

The specific example we will be using is running an Inventory Item Reconcile on items that have been flagged as needing reconciliation. This code was developed in conjunction with Arthur Achilleos, who has agreed for it to be published.

The project table was set up so it can be used for other modules, but the code is specifically designed to identify items that need processing rather than reconciling all items in the system which could take and excessively long time.

Individual inventory items need to be reconciled for various reasons such as Qty on Order not matching open POs or Allocated Quantities being incorrect due to various reasons. Some maintenance tasks such as running reconcile have to be executed when nobody is transacting anything that affects the Inventory module in the system. This means having to wait until after hours to run reconcile on the selected items.

Using GP Power Tools, we can schedule any process to run at a desired time.

There are two approaches that can be used to run scheduled processes:

1. If you already have a GP session that is always logged in for other tasks, such as automated posting, or are willing to have a session always logged in, you can use that session to run the trigger scheduled for a specified time. The trigger can be limited to only register for the user (and company) for that session.
2. Another approach is to only launch GP when desired, so a user license is not being used up. You can launch GP with the Windows Scheduler at the desired time and use the options to remember user and company, or use a macro to login. A trigger can be written to run automatically after login for the specified user (and company), and start the automations.

Below is a screenshot of the SCHEDULED project showing the components:

NOTE: The triggers in the project archive are currently disabled. You must create the table with the SCH_CREATETABLE SQL Execute script and set the User (and Company) the project should be active for with the Users Button before enabling them (select them, right click and Enable Trigger).

Here are the components of the project:

Trigger SCH_CLOSE_RPT

This trigger will automatically close the Report Destination dialog while the scheduled task is running, selecting to output the reconciliation report to a file called ScheduledTask.txt in the Temp folder of the machine.

Trigger SCH_ITEMREC

This is the main trigger for starting the scheduled process. It can be set to execute at a specific time or, if desired, After Logging In. The After Logging In option should be used if NOT using an always logged in session (i.e. using option 2 above). Also uncomment the MBS_Exit_After_Processes Helper function to exit GP if there are no items to process.

The trigger first optionally calls the SQL Execute SCH_JOB_QUERY script before checking if there are any items to process. If you wish, the code to populate the Scheduling table can be run as a SQL Job and so can be commented out of this script.

Then the trigger calls the SQL Execute SCH_GET_ITEMS script to see if there are any items that need reconciling. If there are, it will call the Runtime Execute SCH_ITEMREC script to start the scheduled task processing.

Trigger SCH_ITEMREC_CON

This trigger calls the Runtime Execute SCH_ITEMREC script in the background after the reconcile process has completed while the scheduled task is running. This will start the next iteration of the loop to process the next item if there is one.

Runtime Execute SCH_ITEMREC

This script is the main code that is executed for each item to be processed. It will use the SQL Execute SCH_GET_ITEMS script to get the next item to be processed and then drives the user interface to select the one item and start the reconcile process.

Then the script uses the SQL Execute SCH_UPDATEQUEUE script along with the Parameter List SCH_UPDATEQUEUE to record that the item has been processed.

This script does not loop or iterate through the items itself, the Trigger SCH_ITEMREC_CON will execute after the reconcile has completed to restart this script as a background call to process the next item.

If NOT using an always logged in session (i.e. using option 2 above), uncomment the MBS_Exit_After_Processes Helper function to exit GP when there are no more items to process.

Runtime Execute SCH_TESTSIMPLE

This script is just a test script which will open the items in the process queue table with the Item Maintenance window. This example does iterate through records within the one script. Note that the results of the call to SQL Execute SCH_GET_ITEMS is stored in a separate l_Records variable so that the data set is not overwritten when loading and executing other scripts.

SQL Execute SCH_ADDTABLERCD

This SQL script will insert a record into the process queue table manually. It uses the Parameter List SCH_UPDATEQUEUE to pass data to the insert command. It can be used for testing or to force an item to be reconciled.

SQL Execute SCH_CREATETABLE

This SQL script must be executed once for each company you plan to run this automation for. It will create the SQL table used to store the records to be processed.

SQL Execute SCH_GET_ITEMS

This SQL query returns the records for the Items that are waiting to be processed. It can be called from GP Power Tools to check if any items are waiting without returning data or returning data with a list of the item numbers in a data set.

SQL Execute SCH_JOB_QUERY

This query looks for items that should be reconciled. It looks for Items where the Quantity Allocated is great than the Quantity On Hand as well as Purchase Orders where the Quantity on order is not correct. It is based on the Professional Services Tools Library (PSTL) IV Reconcile scripts.

• SQL PSTL Iv reconcile scripts.zip

This script can be called each time as part of the scheduled process, or the contents of the script can be added to a SQL Agent Job and scheduled as desired.

SQL Execute SCH_UPDATEQUEUE

This script is used to write back to the process queue table to record once an item has had the reconcile process started. It uses the Parameter List SCH_UPDATEQUEUE to pass data into the script.

Parameter List SCH_UPDATEQUEUE

This Parameter List is used by the SQL Scripts SCH_UPDATEQUEUE and SCH_ADDTABLERCD to pass the data when updating or adding records to the process queue table.

NOTE: If NOT using an always logged in session (i.e. using option 2 above). Edit both the Trigger SCH_ITEMREC script and the Runtime Execute SCH_ITEMREC script to uncomment the call to the MBS_Exit_After_Processes Helper Function. This will close GP when there are no more items to process.

Downloading and Installing

Download the example code, import using the Project Setup window (without any project showing, select the path to the xml file and click Import):

• GP Power Tools Settings Scheduled.dbg.zip

The code will be active on next login or after switching companies, or you can use start the triggers manually from the Project Setup window.

More Information

For more information see:

• GP Power Tools Portal: http://winthropdc.com/GPPT
• GP Power Tools Samples: http://winthropdc.com/GPPT/Samples
• GP Power Tools Videos: http://winthropdc.com/GPPT/Videos

Hope you find this example useful.

David

This article was originally posted on http://www.winthropdc.com/blog.