Identify and Fix Corrupt SOP Transactions in Microsoft Dynamics GP

A client recently logged a support call whereby reports were showing incorrect information, including for transactions which had been deleted. I did some exploring mof data and found that the Sales Transaction Amounts Work (SOP10200) and Sales User-Defined Work History (SOP10106) tables contained rows for transactions which were not in the Sales Transaction Work (SOP10100) table.

From reviewing the data, deleted transactions which had an entry in Sales User-Defined Work History (SOP10106) would also have one in Sales Transaction Amounts Work (SOP10200) making the job of identifying the corrupt ones somewhat easier (Sales User-Defined Work History (SOP10106) contains both Work and History rows).

The first script identifies rows in Sales User-Defined Work History (SOP10106) which are orphaned:

/*
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).
*/
SELECT
	DISTINCT
		SOPL.SOPNUMBE
		,SOPL.SOPTYPE
FROM
	SOP10200 AS SOPL --Sales Transaction Amounts Work (SOP10200)
INNER JOIN
	SOP10106 AS SOPU --Sales User-Defined Work History (SOP10106)
		ON
			SOPU.SOPNUMBE = SOPL.SOPNUMBE
		AND
			SOPU.SOPTYPE = SOPL.SOPTYPE
LEFT JOIN
	SOP10100 AS SOPH --Sales Transaction Work (SOP10100)
		ON
			SOPH.SOPNUMBE = SOPL.SOPNUMBE
		AND
			SOPH.SOPTYPE = SOPL.SOPTYPE
WHERE
	SOPH.SOPNUMBE IS NULL
GO

The second script identifies rows in Sales Transaction Amounts Work (SOP10200) which are orphaned:]

/*
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).
*/
SELECT
	*
FROM
	SOP10200 AS SOPL --Sales Transaction Amounts Work (SOP10200)
LEFT JOIN
	SOP10100 AS SOPH --Sales Transaction Work (SOP10100)
		ON
			SOPH.SOPNUMBE = SOPL.SOPNUMBE
		AND
			SOPH.SOPTYPE = SOPL.SOPTYPE
WHERE
	SOPH.SOPNUMBE IS NULL
GO

The third script deletes rows in Sales User-Defined Work History (SOP10106) which are orphaned:

/*
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).
*/
DELETE
	SOPU
FROM
	SOP10106 AS SOPU --Sales User-Defined Work History (SOP10106)
INNER JOIN
	SOP10200 AS SOPL --Sales Transaction Amounts Work (SOP10200)
		ON
			SOPU.SOPNUMBE = SOPL.SOPNUMBE
		AND
			SOPU.SOPTYPE = SOPL.SOPTYPE
LEFT JOIN
	SOP10100 AS SOPH --Sales Transaction Work (SOP10100)
		ON
			SOPH.SOPNUMBE = SOPL.SOPNUMBE
		AND
			SOPH.SOPTYPE = SOPL.SOPTYPE
WHERE
	SOPH.SOPNUMBE IS NULL
GO

The fourth script deletes rows in Sales Transaction Amounts Work (SOP10200) which are orphaned:

/*
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).
*/
DELETE
	SOPL
FROM
	SOP10200 AS SOPL --Sales Transaction Amounts Work (SOP10200)
LEFT JOIN
	SOP10100 AS SOPH --Sales Transaction Work (SOP10100)
		ON
			SOPH.SOPNUMBE = SOPL.SOPNUMBE
		AND
			SOPH.SOPTYPE = SOPL.SOPTYPE
WHERE
	SOPH.SOPNUMBE IS NULL
GO