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

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

By Rob Wagner | July 24, 2018

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.

Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.

Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.

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!

Stoneridge Software respects your privacy. We will never share your information with others.