Voyager to Alma Migration Query: Identify Vendors with no currently attached POs or Invoices

When migrating from Voyager to Alma, you may wish to identify and delete Vendors that have no currently attached Purchase Orders or Invoices.

These three queries (a main query and its two required subqueries), which you can run in Voyager's Prepackaged Access Reports, will display such Vendors and allow you to delete them if you wish.

Subquery (Name the query the following): 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 (Name the query  the following): 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 subqueries are used by the following query that you will run:

Main Query (Name it whatever you wish) 

Vendors with No Attached Purchase Orders or Invoices

Create and save this query (which uses the previous two subqueries) to display 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;

For other queries that may be useful, see my "Surviving Almanado: tips for a successful pre-implementation" presentation's Accompanying Materials.

If you need assistance in running custom SQL queries in Prepackaged Access Reports, consult the Voyager Customer Listserv.