Identify and Fix Corrupt SOP Transactions in Microsoft Dynamics GP

Microsoft Dynamics GPA 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

Continue reading “Identify and Fix Corrupt SOP Transactions in Microsoft Dynamics GP”