SQL Scripts for Microsoft Dynamics GP: Remove Multicurrency from Sales Transactions

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created for a client who imported a batch of transactions with the incorrect multicurrency information; this was only found when they came to post the transactions and found that they would not post. The transactions were in the functional currency of EURO, but had a US Dollar exchange rate applied to them.

The script was set up to allow the Currency ID and Exchange Table ID to be specified and the Sales Taxes Work and History (SOP10105) and Sales Transaction Work (SOP10100) tables updated to remove the incorrect multicurrency information.

/*
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 @CURNCYID VARCHAR(15) = 'EURO'
DECLARE @EXGTBLID  VARCHAR(15) = 'EUR-USD'

UPDATE
	SOP10105 --Sales Taxes Work and History (SOP10105)
SET
	ORSLSTAX = STAXAMNT
	,ORTOTSLS = TAXDTSLS
	,ORTXSLS = TDTTXSLS
	,OTTAXPON = TXDTOTTX
WHERE
	SOPNUMBE IN (
					SELECT
						SOPNUMBE
					FROM
						SOP10100 AS SOPH --Sales Transaction Work (SOP10100)
					WHERE
						CURNCYID = @CURNCYID
					AND
						RATETPID <> ''
					AND
						EXGTBLID = @EXGTBLID
				)
AND
	STAXAMNT <> ORSLSTAX


UPDATE
	SOP10100 --Sales Transaction Work (SOP10100)
SET
	ORDOCAMT = DOCAMNT
	,ORSUBTOT = SUBTOTAL
	,ORTAXAMT = TAXAMNT
	,RATETPID = ''
	,EXGTBLID = ''
	,XCHGRATE = 0
WHERE
	SOPNUMBE IN (
					SELECT
						SOPNUMBE
					FROM
						SOP10100 AS SOPH --Sales Transaction Work (SOP10100)
					WHERE
						CURNCYID = @CURNCYID
					AND
						RATETPID <> ''
					AND
						EXGTBLID = @EXGTBLID
				)
AND
	DOCAMNT <> ORDOCAMT