All Vendors that do not have a record in the transactional tables

I received a request yesterday to write a query that would find all vendors that did not have a record in any of the transactional tables; so I thought I'd talk for a little bit about that.

The first thing is that you should not be doing this if you don't understand SQL or the Dynamics GP table structure. Take a backup first.

Second, there is a little bit of selectivity in use here. I started with a query of all the tables that use the VENDORID field, in my install there are 188. Too many for me to want to include all of them in my searching. But, I know that I can eliminate the SVC tables and the JC tables because this client doesn't use them.

So, you would use the script below as a template and modify it to your needs.


A/P Distribution to GL - when using Intercompany
All Vendors that do not have a record in the transactional tables
dd_PMApply_SEL
dd_PMVendorDocument_SEL
ddf_CommaSeparatedPOs
ddf_CommaSeparatedReceipts
ddv_PayablesWithPOs
insert missing lines in the Vendor Master PM00200
Move a debit and credit to history
seePMAgedTB
seepmHATBWrapper
Stored procedure that mimics the Payables Inquiry 'Re-create check stub' button
taGetPMNextVoucherNumber usage example
taPMDistributionCreate
taPMTransacitonInsert wrapper
taPMTransactionInsert
taPMTransactionInsert Wrapper, including distributions

... and you 'll find more on the SQL (Dynamics) Menu

--discover all the tables that use vendorid

SELECT table_name FROM INFORMATION_SCHEMA.columns WHERE COLUMN_NAME = 'vendorid' ORDER BY table_name

--finds all vendor records that do not have a current record in any of the work tables

--add to the LEFT JOINs to include all the tables that might have VENDORID in use.

SELECT v.*

FROM pm00200 v

LEFT JOIN (SELECT DISTINCT vendorid FROM pm10000) ptw ON ptw.VENDORID = v.VENDORID

LEFT JOIN (SELECT DISTINCT vendorid FROM pm20000) pto ON pto.VENDORID = v.VENDORID

LEFT JOIN (SELECT DISTINCT vendorid FROM pm30200) pth ON pth.VENDORID = v.VENDORID

LEFT JOIN (SELECT DISTINCT vendorid FROM pop10110) pol ON pol.VENDORID = v.VENDORID

LEFT JOIN (SELECT DISTINCT vendorid FROM pop30110) polh ON polh.VENDORID = v.VENDORID

WHERE ptw.VENDORID IS NULL

AND pto.VENDORID IS NULL

AND pth.VENDORID IS null

AND pol.VENDORID IS null

AND polh.VENDORID IS null

--once you're happy with the result set from the above query, this will change the Vendor Class of the selected vendors

--and from there they can be mass deleted (or so I'm told)

UPDATE pm00200 SET VNDCLSID = 'MY NEW CLASS'