Pulling recently added and changed vendors from MIP via SQL

Depending on your integration and reporting needs, you may find yourself needing to pull a list of recently maintained vendors from Abila MIP.  Specifically, this is not the case where you want a full export of vendors, but rather just changes that have occurred since a given date. In this post, we will show to pull such a list.

The table tblAPVendor contains the main vendor data, but has no way of indicating when a vendor last changed. In this case, we can use table tblOrgActivityLog to determine all recent vendor changes since a given date.

Specifically, the following will get all vendor maintenance activity since the indicated date

SELECT *
FROM [YourMIPDB].[dbo].[tblOrgActivityLog]
where sMessageType like '%MtnVENDOR%'
and dtmDateStamp > '2018-07-16'

We will need to parse the column sMessage to extract the vendor code which was changed. We can do with a bit of string manipulation, realizing the vendor code is sandwiched between the strings ‘ID:’ and ‘was’. sMessage looks like:

Vendor Vendor ID: VDRABC was Added.
Vendor Vendor ID: VDRXYZ was Edited.

So now we can produce a list of all vendor codes changed since the given date, and we can use it in an IN clause to pull all the vendor data associated with the changed vendor. Note the IN clause may not be the most efficient way yo structure this query, but unless you are running tens of thousands of vendors or need online like response, it should suffice. A correlated subquery might be better if you need more efficiency.  In any case, here is the final query:

select * from [YourMIPDB].[dbo].tblAPVendor tvdr
where

tvdr.sVendorID in (
SELECT
--  Search the string to extract the vendor based on where preceding 'ID: ' and following ' was ' are in overall -- 
--  string
substring(sMessage,CHARINDEX('ID: ',sMessage) + 4,CHARINDEX(' was ',sMessage) - CHARINDEX('ID: ',sMessage) -4 )

FROM [YourMIPDB].[dbo].[tblOrgActivityLog]
where sMessageType like '%MtnVENDOR%'
and dtmDateStamp > '2018-07-16')