SQL Scripts for Microsoft Dynamics GP: Set New Vendor On Hold if EFT Exists

Microsoft Dynamics GPThis 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 sets a new vendor to a status of on hold, if there are EFT details already present when the vendor record is inserted.

This was created for a client who had some problems due to a bug in Dynamics GP where EFT details weren’t deleted with the vendor card, which meant next time a vendor was created with the same Vendor ID there bank details for the previous record would still be present.

/*
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). */
CREATE TRIGGER [dbo].[utr_AZRCRV_EFTCreditorHold] ON [dbo].[PM00200] AFTER INSERT AS BEGIN SET NOCOUNT ON; DECLARE @VENDORID CHAR(15) SELECT @VENDORID = VENDORID FROM INSERTED IF @VENDORID IN (SELECT VENDORID FROM SY06000 --Address Electronic Funds Transfer Master (SY06000) WHERE VENDORID = @VENDORID) UPDATE PM00200 --PM Vendor Master File (PM00200) SET HOLD = 1 WHERE VENDORID = @VENDORID END

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index

What should we write about next?

Looking for support or consultancy with Microsoft Dynamics GP?

Leave a Reply

Your email address will not be published. Required fields are marked *