SQL view for Dynamics GP vendors with last activity date

July 17, 2014 by Victoria Yudin 13 Comments

I have seen many requests recently for a way to see what Dynamics GP vendors have had no recent activity. Since ‘recent’ can mean different things, I thought I would put together a view that shows the last activity date for each vendor. Once you have this you can easily filter for whatever date range you need. I also added the date the vendor was created, since a brand new vendor might not have any activity yet.

Here is a sample of the results (please click on the image to see it bigger):

Some additional resources:

create view view_Vendors_Last_Activity
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- Created July 17, 2014 by Victoria Yudin
-- Flexible Solutions, Inc.
-- For other code, please visit http://victoriayudin.com
-- Returns all vendors with last activity date
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

select
rtrim(v.VENDORID) Vendor_ID,
rtrim(v.VENDNAME) Vendor_Name,
max(coalesce(d.DOCDATE, '1/1/1900')) Last_Activity,
v.CREATDDT Created_Date

from PM00200 v

left outer join
(select VENDORID, DOCDATE from PM10000
   union
 select VENDORID, DOCDATE from PM20000
   union
 select VENDORID, DOCDATE from PM30200) d
    on v.VENDORID = d.VENDORID

group by v.VENDORID, v.VENDNAME, v.CREATDDT

go
grant select on view_Vendors_Last_Activity to DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

Dynamics GP, GP Reports code, GP SQL scripts, Payables SQL code

GP SQL view, Payables, SQL code

SQL view for Dynamics GP open year GL trial balance with month end balances

SQL view for easier General Ledger transaction searches in Dynamics GP

13 Responses to “SQL view for Dynamics GP vendors with last activity date”

David McIndoe Reply April 22, 2015 at 11:32 pm

Hi Victoria, we are running GP version 6. I tried running this script in SQL but get the following error response:
Server: Msg 208, Level 16, State 1, Procedure view_Vendors_Last_Activity, Line 11
Invalid object name ‘PM00200’.
Server: Msg 208, Level 16, State 1, Procedure view_Vendors_Last_Activity, Line 11
Invalid object name ‘PM10000’.
Server: Msg 208, Level 16, State 1, Procedure view_Vendors_Last_Activity, Line 11
Invalid object name ‘PM20000’.
Server: Msg 208, Level 16, State 1, Procedure view_Vendors_Last_Activity, Line 11
Invalid object name ‘PM30200’.
Server: Msg 208, Level 16, State 11, Line 1
Invalid object name ‘view_Vendors_Last_Activity’.
It looks like it is not recognising the table names which look correct from what I can see.
Help
David

LikeLike
- Victoria Yudin Reply April 23, 2015 at 3:46 am
  
  Hi David,
  
  Are you sure you’re running the script against the company database in SQL? Those errors sound like you might be running it against another database that does not have those tables.
  
  -Victoria
  
  LikeLike
- David McIndoe Reply April 23, 2015 at 11:43 pm
  
  Hi Victoria. You were right. I’m very new to SQL. All working fine now. How do I send the output to Excel?
  Thanks again
  David
  
  LikeLike
  - Victoria Yudin Reply April 24, 2015 at 3:23 am
    
    David,
    
    This may depend on the version of SQL you’re on, however, generally, you can either copy the results and paste them to Excel or right click on the results and choose Save Results As…
    
    -Victoria
    
    LikeLike
vailleslie Reply July 17, 2014 at 11:40 am

Thank you Victoria for sharing so many of these SQL gems!

Leslie

LikeLike
- David McIndoe Reply May 20, 2015 at 2:20 am
  
  Hi Victoria, sorry to be a pest. When I use either of these methods I do not get the headers, just the data.
  Also, is there a similar report for sales?
  
  LikeLike
  - Victoria Yudin Reply May 20, 2015 at 2:35 am
    
    Hi David,
    
    What version of SQL are you using?
    
    For sales – please take a look at my page with SOP SQL views.
    
    -Victoria
    
    LikeLike
    - David McIndoe Reply May 21, 2015 at 12:11 am
      
      Hi Victoria, we are using GP v6. The SQL version is 8.00.194. We are soon to upgrade.
      Most of our sales invoices happen through Wennsoft service manager so the SOP reports are no good for me.
      
      David
      
      LikeLike
      - Victoria Yudin Reply May 21, 2015 at 4:48 am
        
        Hi David,
        
        Version 8.00.194 is SQL 2000. I don’t have that installed anywhere anymore, so I cannot test this, but from Googling it, it looks like you can go to SQL Server Query Analyzer | Tools | Options | Results and there will be a setting there for including column headers with your results.
        
        If Wennsoft is not populating SOP tables with their invoicing, then you are right you cannot use my SOP queries. You might want to talk to Wennsoft about what you’re looking for to see if they can help you.
        
        -Victoria
        
        LikeLike