Skip to main content
ExLibris

Knowledge Assistant

BETA
 
  • Subscribe by RSS
  • Back
    Voyager

     

    Ex Libris Knowledge Center
    1. Search site
      Go back to previous article
      1. Sign in
        • Sign in
        • Forgot password
    1. Home
    2. Voyager
    3. Community Knowledge
    4. Voyager to Alma Migration Query: Identify Vendors with no currently attached POs or Invoices

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

    1. Last updated
    2. Save as PDF
    3. Share
      1. Share
      2. Tweet
      3. Share
    No headers
    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 Purchase Orders or Invoices.

    These three queries (a main query with two subqueries), which you can run in Voyager's Prepackaged Access Reports, will display such Vendors.  It also shows vendors that DO have purchase order or invoice activity.  So you get a two-fer with this query.

    Existing Voyager vendor data will be migrated from Voyager once, during the initial test load. To load successfully, vendor codes and names need to be unique. Libraries should also consider making vendor names and codes unambiguous. For more information discuss this with your migration team.

     

    There is the Main Query which can be named anything you want:

    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;

    There are two subqueries. Name them as you see displayed below:

     

    Subquery1 - Name It:  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;

     

     

    Subquery2 - Name It:  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;

     

     

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

     

     

     

     

     

     

    Report

     

     

     

    View article in the Exlibris Knowledge Center
    1. Back to top
      • Voyager to Alma Migration Query: Identify Serial Bibiographic Records that do not have an ISSN
      • Voyager to Alma Migration Query: Identifying Bound Withs in your database
    • Was this article helpful?

    Recommended articles

    1. Article type
      Topic
      Community Content Type
      How To
      Product
      Voyager
    2. Tags
      This page has no tags.
    1. © Copyright 2025 Ex Libris Knowledge Center
    2. Powered by CXone Expert ®
    • Term of Use
    • Privacy Policy
    • Contact Us
    2025 Ex Libris. All rights reserved