亿博电竞比赛下注注册
In order to perform an inventory reconcile in Microsoft 亿博电竞 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 亿博电竞 GP.
Prerequisites
- 亿博电竞 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 亿博电竞 .
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 亿博电竞
Executable - C:\Program Files (x86)\Microsoft 亿博电竞 \GP2016\亿博电竞 .exe Arguments - "C:\Program Files (x86)\Microsoft 亿博电竞 \GP2016\亿博电竞 .set" "C:\AutoReconcile\Macros\Stoneridge_Login.mac" Working Directory - C:\Program Files (x86)\Microsoft 亿博电竞 \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
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.