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

I 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.