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

march

03mar10:00 am10:30 amThe Modern Manufacturer - MRP vs. MPS – What, When and How

03mar2:00 pm2:30 pmLot Management Best Practices for Batch Manufacturers

10mar10:00 am10:30 amEnhancing the Customer Experience and Driving More Business with Dynamics 365 Customer Service

10mar2:00 pm2:30 pmThe Five Steps to Managing Sales Tax

11mar11:00 am12:00 pmConfab with Stoneridge - Livestream - Power Apps Component Framework Power Hour

17mar10:00 am10:30 amThe Modern Manufacturer: Pro Tips for your Finance Team

24mar10:00 am10:30 amUnlock the Potential of Digital Marketing with Dynamics 365 Marketing

25mar11:00 am12:00 pmConfab with Stoneridge - Livestream - The Four Keys to Choosing the Best ERP or CRM Solution

31mar10:00 am10:30 amThe Modern Manufacturer: Keys to Reporting Success

31mar01aprTraining Class: Financial Reporting W/Account Schedules for Dynamics 365 Business Central

april

31mar01aprTraining Class: Financial Reporting W/Account Schedules for Dynamics 365 Business Central

07apr10:00 am10:30 amBoost Seller Productivity and Close More Business with Dynamics 365 Sales

07apr2:00 pm2:30 pmEnsuring Quality for Batch Manufacturers

08apr11:00 am12:00 pmJumping into the Deep End of the Data Lake

14apr10:00 am10:30 amThe Modern Manufacturer: Cost Accounting in D365

22apr11:00 am12:00 pmMoving from Manual to Automated with Strategic Automation Strategies

28apr10:00 am10:30 amThe Modern Manufacturer: Best Practices for Inventory Turn

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