SQL Scripts for Microsoft Dynamics GP: Delete Corrupt Extended Pricing Data

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 delete corrupt data from the Extended Pricing tables; we had a client use the Table Import function and, unfortunately, imported some rows which did not have a price sheet id defined.

This script removes data from three tables:

1. Extended Pricing Price Sheet Work (IV10401)
2. Extended Pricing Price Sheet UofM Work (IV10402)
3. Extended Pricing Price Sheet Work (IV10401)

/*
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).
*/
-- LINES WITHOUT HEADERS

DELETE

	['Extended Pricing Price Sheet Work']
FROM

	IV10401 AS ['Extended Pricing Price Sheet Work'] --Extended Pricing Price Sheet Work (IV10401)
LEFT JOIN

	SOP10110 AS ['Extended Pricing Price Sheet Header'] --Extended Pricing Price Sheet Header (SOP10110)
		ON

			['Extended Pricing Price Sheet Header'].PRCSHID = ['Extended Pricing Price Sheet Work'].PRCSHID

WHERE

	['Extended Pricing Price Sheet Header'].PRCSHID IS NULL

GO
--PRICES WITHOUT LINES

DELETE

	['Extended Pricing Price Sheet UofM Work']
FROM

	IV10402 AS ['Extended Pricing Price Sheet UofM Work'] --Extended Pricing Price Sheet UofM Work (IV10402)
LEFT JOIN

	IV10401 AS ['Extended Pricing Price Sheet Work'] --Extended Pricing Price Sheet Work (IV10401)
		ON

			['Extended Pricing Price Sheet Work'].PRCSHID = ['Extended Pricing Price Sheet UofM Work'].PRCSHID

		AND

			['Extended Pricing Price Sheet Work'].ITEMNMBR = ['Extended Pricing Price Sheet UofM Work'].ITEMNMBR

WHERE

	['Extended Pricing Price Sheet Work'].PRCSHID IS NULL

GO
--LINES WITHOUT PRICES

DELETE

	['Extended Pricing Price Sheet Work']
FROM

	IV10401 AS ['Extended Pricing Price Sheet Work'] --Extended Pricing Price Sheet Work (IV10401)
LEFT JOIN

	IV10402 AS ['Extended Pricing Price Sheet UofM Work'] --Extended Pricing Price Sheet UofM Work (IV10402)
		ON

			['Extended Pricing Price Sheet Work'].PRCSHID = ['Extended Pricing Price Sheet UofM Work'].PRCSHID

		AND

			['Extended Pricing Price Sheet Work'].ITEMNMBR = ['Extended Pricing Price Sheet UofM Work'].ITEMNMBR

WHERE

	['Extended Pricing Price Sheet UofM Work'].PRCSHID IS NULL

GO

Read original post SQL Scripts for Microsoft Dynamics GP: Delete Corrupt Extended Pricing Data at azurecurve|Ramblings of an IT Professional