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
Continue reading “Identify and Fix Corrupt SOP Transactions in Microsoft Dynamics GP”