Related product I-Share

Acquisitions Pre-Migration Maintenance: Vendor Data

As I-Share libraries begin to prepare for migration to Alma, CARLI staff and committees will identify projects that your libraries may find useful in identifying data to clean-up, delete, or keep watch on.

Migration of Voyager Acquisitions data is included in the scope of work for all I-Share libraries. CARLI will plan to migrate each library's Acquisitions data unless a library opts not to migrate that data. Libraries are not expected to remove or delete any of this data, but some libraries may find some pre-migration clean-up to be useful. Libraries that plan to migrate acquisitions data may wish to prioritize maintenance of vendor records.

Vendor data in Alma are created as part of the initial configuration process. Existing Voyager vendor data will be migrated once, during the initial test load. To load successfully, vendor codes and names will need to be unique. Libraries should also consider making vendor names and codes unambiguous.

CARLI staff have developed some new queries to help identify records.

Queries: Finding Vendor Activity

Depending on the number of vendors and amount of activity in your acquisitions client, you may wish to remove vendors that have no activity or that have not been used in many years. There are two main queries and two subqueries for these purposes.

Create the following subqueries and save them with the names indicated. You do not need to run the queries before using them:

Subquery: Vendor_Usage_POcount_sq

SELECT PURCHASE_ORDER.VENDOR_ID,
Count(PURCHASE_ORDER.PO_ID) AS CountOfPO_ID,
Max(PURCHASE_ORDER.PO_STATUS_DATE) AS MaxOfPO_STATUS_DATE
FROM PURCHASE_ORDER
GROUP BY PURCHASE_ORDER.VENDOR_ID;

Subquery: Vendor_Usage_InvCount_sq

SELECT INVOICE.VENDOR_ID,
Count(INVOICE.INVOICE_ID) AS CountOfINVOICE_ID,
Max(INVOICE.INVOICE_STATUS_DATE) AS MaxOfINVOICE_STATUS_DATE
FROM INVOICE
GROUP BY INVOICE.VENDOR_ID;

The two subqueries above are used to count purchase orders and invoices, and find the most recent status date for those objects. These are used by the following two queries.

Main Query: Vendors with No Attached Purchase Orders or Invoices

Create and save this query with the name above. Run the query to find vendor records that have no currently attached purchase orders and no attached invoices.

SELECT VENDOR.VENDOR_NAME, VENDOR.VENDOR_CODE,
VENDOR_TYPES.VENDOR_TYPE_DESC,
VENDOR.CREATE_DATE, VENDOR.UPDATE_DATE,
VENDOR.FEDERAL_TAX_ID, VENDOR.INSTITUTION_ID
FROM ((VENDOR INNER JOIN VENDOR_TYPES
ON VENDOR.VENDOR_TYPE = VENDOR_TYPES.VENDOR_TYPE)
LEFT JOIN Vendor_Usage_InvCount_sq 
ON VENDOR.VENDOR_ID = Vendor_Usage_InvCount_sq.VENDOR_ID)
LEFT JOIN Vendor_Usage_POcount_sq
ON VENDOR.VENDOR_ID = Vendor_Usage_POcount_sq.VENDOR_ID
WHERE (((Vendor_Usage_POcount_sq.VENDOR_ID) Is Null)
AND ((Vendor_Usage_InvCount_sq.VENDOR_ID) Is Null))
ORDER BY VENDOR.VENDOR_NAME;

Main Query: Vendor Usage

Create and save this query with the above name. Run the query to find the counts of purchase orders and invoices, and the last status date for each object with that vendor. Note that it is possible to have a purchase order count of 0 and have invoice counts of 1 or more; this is because one can change the vendor on purchase orders.

SELECT VENDOR.VENDOR_NAME, VENDOR.VENDOR_CODE,
VENDOR_TYPES.VENDOR_TYPE_DESC,
Vendor_Usage_POcount_sq.CountOfPO_ID AS poCount,
Vendor_Usage_POcount_sq.MaxOfPO_STATUS_DATE AS lastPOstatus, 
Vendor_Usage_InvCount_sq.CountOfINVOICE_ID AS invoiceCount,
Vendor_Usage_InvCount_sq.MaxOfINVOICE_STATUS_DATE AS lastInvoiceStatus
FROM ((VENDOR INNER JOIN VENDOR_TYPES
ON VENDOR.VENDOR_TYPE = VENDOR_TYPES.VENDOR_TYPE)
LEFT JOIN Vendor_Usage_InvCount_sq
ON VENDOR.VENDOR_ID = Vendor_Usage_InvCount_sq.VENDOR_ID)
LEFT JOIN Vendor_Usage_POcount_sq
ON VENDOR.VENDOR_ID = Vendor_Usage_POcount_sq.VENDOR_ID
ORDER BY VENDOR.VENDOR_NAME;

Ways to use these queries

A library might use the list of vendors with no attached orders or invoices to select vendor records for deletion. Similarly, libraries might review the list of vendors usage data to find vendors with little to no activity (such as one-off publisher purchases) or vendors that have not been used in several years. These may also be deleted IF the associated invoices, subscription components, and purchase orders have been deleted as well.

Library staff should also review the list of vendor names carefully and ensure that vendor names and vendor codes are unique. Voyager allows both vendor names and codes to duplicate one another; however Alma does not allow duplicate names and codes. If you find duplicate names or codes, you may choose to rename or delete the obsolete vendor record in Voyager.

As noted above, it is possible to change the vendors on purchase orders. If a Vendor is tied to the purchase order's vendor history tab, you may be unable to delete the vendor record. To find vendors tied to vendor history, you may use the following query:

SELECT VENDOR.VENDOR_NAME AS PreviousVendor, PURCHASE_ORDER.PO_NUMBER, 
PO_VENDOR_HISTORY.REPLACE_DATE, VENDOR_1.VENDOR_NAME AS CurrentVendor
FROM VENDOR AS VENDOR_1 INNER JOIN ((VENDOR 
INNER JOIN PO_VENDOR_HISTORY ON VENDOR.VENDOR_ID = PO_VENDOR_HISTORY.VENDOR_ID) 
INNER JOIN PURCHASE_ORDER ON PO_VENDOR_HISTORY.PO_ID = PURCHASE_ORDER.PO_ID) 
ON VENDOR_1.VENDOR_ID = PURCHASE_ORDER.VENDOR_ID
ORDER BY VENDOR.VENDOR_NAME;

Save the above query as "Find Vendors on Purchase Order Vendor History tab". Running the query will list vendors, sorted by the vendor on the vendor history, along with the purchase order number, the date that the purchase order was changed, and the current vendor name on the purchase order.