How to Perform an After-Hours Inventory Reconcile in Microsoft Dynamics GP

by | Updated July 25, 2018 | Dynamics GP

In order to perform an inventory reconcile in Microsoft Dynamics GP, users cannot be processing inventory transactions. Thus, the best time to have this process take place is after hours. This way the event can work overnight without interruptions. In the event that someone is processing a transaction when the scheduled job occurs, the automation will exit the system gracefully. Otherwise, a reconcile happens and reports will be archived in a text file. Here are the steps for performing an after-hours inventory reconcile in Dynamics GP.

Prerequisites

  • Dynamics GP
  • Microsoft SQL Server
    • SQL Server Agent
    • SQL Server Integration Services
  • Visual Studio 2017 with SQL Server Data Tools Addin
  • SQL Server Agent

Step 1

Use these automation macros to perform the operations needed to reconcile inventory.

Stoneridge_Login.mac – Log into the system

Stoneridge_Startup_Exit_Without_Reconcile.mac – Removes the startup macro, then exit the system

Stoneridge_Startup_Exit_After_Reconcile.mac – Reconcile, remove the startup macro, exit the system

Step 2

Use a three-step SSIS package to archive reports, execute a sql statement and run Dynamics.

Inventory Reconcile

Archive_Report.bat – Rename the files at the archive location

T-SQL_Statement.sql – Add the startup macro for %USERID%

Execute Process – Start Dynamics

Step 3

Use SQL Agent to run the package from above.

Inventory Reconcile Job – Blank job scheduled to run at 2:00 am each evening

Inventory Reconcile

Script for Proxy and Job Step execute SSIS package – Proxy credentials with job step assigned

Conclusion

When the SQL agent job runs the SSIS package at 2:00 am and there are no users currently processing inventory transactions, the previous reconcile reports at the location are archived, then the inventory reconcile runs to produce new reports. In the case that someone is inside a transaction, no action is taken.

 

Related Posts

2 Comments

  1. Nancy Sparks

    Where do I store the login macro?
    Thanks.

  2. Leah Baker

    Hi Nancy, Here is Rob’s response:

    Thanks for reaching out here. This particular example stores the macro at the following location.

    C:\AutoReconcile\Macros\Stoneridge_Login.mac

    You could use the same location or make the updates to the macro that will match your environment.

Submit a Comment

Your email address will not be published. Required fields are marked *

Upcoming Events

september

02sep10:00 am10:30 amThe Modern Manufacturer - Cycle Count Management

02sep12:00 pm12:30 pmBest Practices for Work Order Planning and Routing for Field Service Companies

09sep10:00 am11:00 amWhat is Levridge? An Overview of the Ultimate Ag Solution

09sep12:00 pm12:30 pmOverview of Microsoft Promotion for NAV and GP Clients

10sep11:00 am12:00 pmConfab with Stoneridge - Livestream - Field Service Comparisons

16sep10:00 am10:30 amThe Modern Manufacturer - Product Lifecycle Management

16sep12:00 pm12:30 pmHow to Upgrade to Dynamics 365 Finance and Operations: Examining Custom Code and ISV Implications

16sep2:00 pm2:30 pmScheduling and Dispatching for the Modern Field Service Company

23sep12:00 pm1:00 pmDynamics 365 Business Central Wave 2 Release Preview - An Overview of the Most Exciting New Features Coming in October

24sep11:00 am12:00 pmConfab with Stoneridge - Livestream - The Modern Workplace: Teams, Sharepoint, Outlook, Office, and AI Integration

30sep10:00 am10:30 amThe Modern Manufacturer - Return Management

30sep12:00 pm12:30 pmEmpowering Your Field Service Technicians with a Mobile Solution

october

07oct12:00 pm1:00 pmThe Three Paths to Business Central from Dynamics GP

08oct11:00 am12:00 pmConfab with Stoneridge - Livestream - The Vision and Strategy of Microsoft Business Systems

14oct10:00 am10:30 amThe Modern Manufacturer - Complex Cost Modeling

14oct12:00 pm12:30 pmGenerating Custom Inspection or Process Forms

19octAll Day22Stoneridge Connect Fall 2020

22oct11:00 am12:00 pmConfab with Stoneridge - Livestream - Stoneridge Connect Recap

28oct10:00 am10:30 amThe Modern Manufacturer - Engineering Change Orders

About Stoneridge
Stoneridge Software is a unique Microsoft Gold Partner, with emphasis on partner. With specialties in Microsoft Dynamics 365, Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP and Microsoft Dynamics CRM, we focus on attracting the most knowledgeable experts in the field to our team, and prioritize delivering stellar solutions with maximum impact for your business. At Stoneridge, we are deeply committed to your results. Each engagement is met with a dedicated team, ready to provide thorough, tailored, and expert service. Based in Minnesota, we intentionally “step into your shoes,” wherever you are. We focus on what you care about, and develop trusting, long-term relationships with our clients.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X