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

By Rob Wagner | July 24, 2018

How to perform an after-hours inventory reconcile in microsoft 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

Logging file 'LOGIN.LOG'
CheckActiveWin dictionary 'default' form Login window Login
MoveTo field 'User ID'
TypeTo field 'User ID' , '%USERNAME%'
MoveTo field Password
TypeTo field Password , '%USERPASSWORD%'
MoveTo field 'OK Button'
ClickHit field 'OK Button'
MoveTo field 'Cancel Button'
MoveTo field 'OK Button'
ClickHit field 'OK Button'

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

# DEXVERSION=16.00.0034.000 2 2
ShellCommand 'Navigate set context to \cmdListShortcutBar\Startup\Stoneridge_Exit'
  CommandExec form BuiLtin command cmdNavPaneDelete 
  CommandExec form BuiLtin command cmdQuitApplication

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

# DEXVERSION=16.00.0034.000 2 2
  CommandExec dictionary 'default'  form 'Command_Inventory' command 'IV_Reconcile' 
NewActiveWin dictionary 'default'  form 'IV_Reconcile' window 'IV_Reconcile' 
  MoveTo field 'Include Item History'  # 'TRUE'
  ClickHit field 'Include Item History'  # 'FALSE'
  CommandExec dictionary 'default'  form 'IV_Reconcile' command 'Process Button P_w_IV_Reconcile_f_IV_Reconcile' 
NewActiveWin dictionary 'default'  form 'Report_Destination' window 'Report_Destination' 
  MoveTo field 'Print to Screen'  # 'TRUE'
  ClickHit field 'Print to Screen'  # 'FALSE'
  MoveTo field 'Print to File'  # 'FALSE'
  ClickHit field 'Print to File'  # 'TRUE'
  MoveTo field 'File Export Name' 
  TypeTo field 'File Export Name' , 'C:\AutoReconcile\Reports\InventoryReconcile'
  ContTypeTo field 'File Export Name' , 'Report.txt'
  MoveTo field 'OK Button' 
  ClickHit field 'OK Button' 
NewActiveWin dictionary 'DEX.DIC'  form 'Report Destination' window 'Report Type' 
  MoveTo field '(L) Printer'  # 'TRUE'
  ClickHit field '(L) Printer'  # 'FALSE'
  MoveTo field '(L) Export'  # 'FALSE'
  ClickHit field '(L) Export'  # 'TRUE'
  MoveTo field '(L) Files' 
  TypeTo field '(L) Files' , 'C:\AutoReconcile\Reports\PicklistReconcileI'
  ContTypeTo field '(L) Files' , 'nventoryAllocationReport.txt'
  MoveTo field '(L) File Type' item 0 
  ClickHit field '(L) File Type' item 1  # 'Text file' 
  MoveTo field '(L) OK' 
  ClickHit field '(L) OK' 
NewActiveWin dictionary 'default'  form 'Report_Destination' window 'Report_Destination' 
NewActiveWin dictionary 'DEX.DIC'  form 'Report Destination' window 'Report Type' 
  MoveTo field '(L) Printer'  # 'TRUE'
  ClickHit field '(L) Printer'  # 'FALSE'
  MoveTo field '(L) Export'  # 'FALSE'
  ClickHit field '(L) Export'  # 'TRUE'
  MoveTo field '(L) Files' 
  TypeTo field '(L) Files' , 'C:\AutoReconcile\Reports\PicklistReconcileR'
  ContTypeTo field '(L) Files' , 'eport.txt'
  MoveTo field '(L) File Type' item 0 
  ClickHit field '(L) File Type' item 1  # 'Text file' 
  MoveTo field '(L) OK' 
  ClickHit field '(L) OK' 
NewActiveWin dictionary 'default'  form 'Report_Destination' window 'Report_Destination' 
NewActiveWin dictionary 'default'  form sheLL window sheLL 
ActivateWindow dictionary 'default'  form 'Progress_Control' window 'Progress_Window' 
# Print To File:  'Inventory Reconcile Report'
# Print To File:  'Picklist Reconcile Inventory Allocation Report'
# Print To File:  'Picklist Reconcile Report'
ActivateWindow dictionary 'default'  form sheLL window sheLL 
ShellCommand 'Navigate set context to \cmdListShortcutBar\Startup\Stoneridge_Reconcile_Exit'
  CommandExec form BuiLtin command cmdNavPaneDelete 
  CommandExec form BuiLtin command cmdQuitApplication

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

for /f "tokens=1-5 delims=/ " %%d in ("%date%") do rename "InventoryReconcileReport.txt" %%e-%%f-%%g_InventoryReconcileReport.txt
for /f "tokens=1-5 delims=/ " %%d in ("%date%") do rename "PicklistReconcileInventoryAllocationReport.txt" %%e-%%f-%%g_PicklistReconcileInventoryAllocationReport.txt
for /f "tokens=1-5 delims=/ " %%d in ("%date%") do rename "PicklistReconcileReport.txt" %%e-%%f-%%g_PicklistReconcileReport.txt

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

use TWO
go

delete from DYNAMICS..SY01990 where SCBowneriD = '%USERID%' and ScbNodeID = 999

if exists(select 1 from [DYNAMICS]..[SY00800]
		where TRXSOURC in ('Change Decimal Places'
						  ,'Change Valuation Method'
						  ,'Reconcile'
						  ,'Change Item Standard Cost'
						  ,'Year-End Close'
						  ,'Stock Count Process Count')
		or BCHSOURC in ('TRX ACTIVITY')
		and CMPNYNAM = %Company Name%') 
or
	exists(select top 1 * from MOP10223 order by DEX_ROW_ID)

	INSERT INTO [DYNAMICS]..[SY01990]([ScbGroupType],[ScbOwnerID],[ScbNodeID],[ScbParentNodeID],[ScbShortcutType],[ScbDisplayName],[ScbTargetStringOne]) VALUES (1,'%USERID%',999,9,3,'Stoneridge_Exit','C:\AutoReconcile\Macros\Stoneridge_Startup_Exit_Without_Reconcile.mac')
else
	INSERT INTO [DYNAMICS]..[SY01990]([ScbGroupType],[ScbOwnerID],[ScbNodeID],[ScbParentNodeID],[ScbShortcutType],[ScbDisplayName],[ScbTargetStringOne]) VALUES (1, '%USERID%',999,9,3,'Stoneridge_Reconcile_Exit','C:\AutoReconcile\Macros\Stoneridge_Exit_After_Reconcile.mac')

Execute Process – Start Dynamics

Executable - C:\Program Files (x86)\Microsoft Dynamics\GP2016\Dynamics.exe
Arguments - "C:\Program Files (x86)\Microsoft Dynamics\GP2016\Dynamics.set" "C:\AutoReconcile\Macros\Stoneridge_Login.mac"
Working Directory - C:\Program Files (x86)\Microsoft Dynamics\GP2016

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

use master
go

if exists(select 1 from sys.credentials where name ='StoneridgeProxyCredentials')
	drop credential [StoneridgeProxyCredentials]
go

create credential [StoneridgeProxyCredentials] with identity = N'%DOMAIN%\%DOMAINUSER%', secret = N'%DOMAINUSERPASSWORD%'
go

use msdb
go

if exists(select 1 from msdb.dbo.sysproxies where name = N'StoneridgeProxyCredentials')
	exec dbo.sp_delete_proxy @proxy_name = N'StoneridgeProxyCredentials'
go

exec msdb.dbo.sp_add_proxy @proxy_name = N'StoneridgeProxyCredentials'
,@enabled=1
,@credential_name=N'StoneridgeProxyCredentials'
go

use msdb
go

exec msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name = N'StoneridgeProxyCredentials' 
,@subsystem_id = 11

exec msdb.dbo.sp_grant_login_to_proxy @proxy_name = N'StoneridgeProxyCredentials'
,@login_name=N'NT SERVICE\SQLSERVERAGENT'
go

exec msdb.dbo.sp_add_jobstep 
@job_name=N'Inventory Reconcile'
,@step_name=N'CallReconcilePackage'
,@step_id = 1
,@cmdexec_success_code = 0
,@on_success_action = 1
,@on_success_step_id = 0
,@on_fail_action = 2
,@on_fail_step_id = 0
,@retry_attempts = 0
,@retry_interval = 0
,@os_run_priority = 0
,@subsystem = N'SSIS'
,@command = N'/FILE "C:\AutoReconcile\Code\Package.dtsx" /CHECKPOINTING OFF /REPORTING E'
,@database_name = N'master'
,@flags = 0
,@proxy_name = N'StoneridgeProxyCredentials'

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

Recommended Reading:

5.27.22 Dynamics GP

How to Run Drop Ship Sales in Dynamics GP

Need to Invoice the Customer before you get the invoice from the vendor? This The freight forwarder has contacted you […]

Read the Article

New Features in Dynamics 365 Business Central 2022 Wave 1 Release – Protecting G/L Accounts From Being Deleted

There are a lot of exciting New Features in Dynamics 365 Business Central 2022 Wave 1 Release to get excited […]

Read the Article
5.25.22 Financials

Rounding Numbers in Power Automate

  If you’ve ever tried to round numbers in Power Automate, you have probably already run into the issue there […]

Read the Article

Manage U.S. Use Tax on Purchase Orders in Dynamics 365 Finance and Operations

  Managing sales tax requirements on your business purchase can be complicated, but Dynamics 365 Finance and Operations can help […]

Read the Article
5.19.22 Dynamics CRM

How to Write a Great Support Ticket in the Stoneridge Support Portal

Submitting a support ticket through the Stoneridge Support Portal is a quick and effective way to get assistance for any […]

Read the Article

Managing Your Business Through Uncertain Times Using Dynamics 365 Finance and Operations

  Dynamics 365 Finance and Operations (F&O) can help you make informed decisions on how to move your business forward. […]

Read the Article
5.13.22 Power Platform

Using Power BI Object Level Security

  The following article will demonstrate how to use Power BI Object Level Security to disable column data based on […]

Read the Article
5.12.22 Dynamics CRM

How to Use the Stoneridge Support Portal

Stoneridge Software’s support portal is an intuitive and useful function that makes it easy for you to access resources to […]

Read the Article
5.6.22 Dynamics GP

Dynamics GP Transaction Removal: Purchase Orders

  Are you having performance issues with Purchase Orders?  Do you find that there are old Purchase Orders on your […]

Read the Article

Start the Conversation

It’s our mission to help clients win. We’d love to talk to you about the right business solutions to help you achieve your goals.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X