Update Posting Setup in all companies to use Posting Date from Transaction

Microsoft Dynamics GPI was recently doing some work for a client and an issue which came up was the posting date for transactions posted to the GL was not always correct. After some investigation we determined that they had their Posting Setup (Administration area page » Setup » Posting » Posting) was set to use the posting date from the batch instead of the transaction.

As they had a substantial number of companies, and a lot of settings to change, they did not want to make this change manually. I’ve previously written a script to update some settings in Posting Setup in all companies so I was quickly able to amend the script for this change:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
DECLARE @SQL_Statement VARCHAR(1000) DECLARE cursor_InterID Cursor FOR SELECT INTERID FROM DYNAMICS..SY01500 Open cursor_InterID DECLAR @INTERID VARCHAR(100) FETCH NEXT FROM cursor_InterID INTO @INTERID WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) SET @SQL_Statement + 'UPDATE ' + RTRIM(@INTERID) + '.dbo.SY02300 SET UPSTDTFR = 1 WHERE UPSTDTFR = 0' exec (@SQL_Statement) FETCH NEXT FROM cursor_InterID INTO @INTERID END CLOSE cursor_InterID DEALLOCATE cursor_InterID

This will update all companies so I would recommend testing on a standalone test system before running on live and making sure you have a good backup before using the script.