This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.
This script will delete the orphaned vendor EFT details left over after a vendor record has been deleted, which was due to a but in Dynamics GP (which may be fixed by now).
I’ve previously posted about a SQL trigger to delete the EFT when the vendor card is deleted, but that wouldn’t remove existing orphaned records which this script will do.
/*
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
['Address Electronic Transfer Funds Master']
FROM
SY06000 AS ['Address Electronic Transfer Funds Master'] --Address Electronic Funds Transfer Master (SY06000)
LEFT JOIN
PM00300 AS ['PM Creditor Address Master'] --PM Address MSTR (PM00300)
ON
['PM Creditor Address Master'].VENDORID = ['Address Electronic Transfer Funds Master'].VENDORID
AND
['PM Creditor Address Master'].ADRSCODE = ['Address Electronic Transfer Funds Master'].ADRSCODE
WHERE
['PM Creditor Address Master'].VENDORID IS NULL