Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

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

    Created By: Laura Guy
    Created on: 7/09/2020



    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.