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

    Voyager to Alma Migration Query: Identify Purchase Orders without Invoices

    Created By: Laura Guy (contact)
    Created on: 10/09/2020



    When migrating from Voyager to Alma it may be useful to identify Purchase Orders that have no Invoices attached to them.  This report will identify such PO's so that you can review them.

    This query, which can be run using Voyager's Prepackaged Access Reports, will require you to enter a cutoff date in the SQL code, since recent orders will not have been invoiced yet.  Check with your acquisitions staff to see what a reasonable cut-off date is.  The example below has it set to 01/01/2020

     

    SELECT PURCHASE_ORDER.PO_NUMBER, PO_TYPE.PO_TYPE_DESC, PO_STATUS.PO_STATUS_DESC, PURCHASE_ORDER.PO_STATUS_DATE, INVOICE_LINE_ITEM.INV_LINE_ITEM_ID INTO [POs w No Invoice]
    FROM (((PURCHASE_ORDER INNER JOIN LINE_ITEM ON PURCHASE_ORDER.PO_ID = LINE_ITEM.PO_ID) LEFT JOIN INVOICE_LINE_ITEM ON LINE_ITEM.LINE_ITEM_ID = INVOICE_LINE_ITEM.LINE_ITEM_ID) INNER JOIN PO_TYPE ON PURCHASE_ORDER.PO_TYPE = PO_TYPE.PO_TYPE) INNER JOIN PO_STATUS ON PURCHASE_ORDER.PO_STATUS = PO_STATUS.PO_STATUS
    GROUP BY PURCHASE_ORDER.PO_NUMBER, PO_TYPE.PO_TYPE_DESC, PO_STATUS.PO_STATUS_DESC, PURCHASE_ORDER.PO_STATUS_DATE, INVOICE_LINE_ITEM.INV_LINE_ITEM_ID
    HAVING (((PURCHASE_ORDER.PO_STATUS_DATE)<#1/1/2020#) AND ((INVOICE_LINE_ITEM.INV_LINE_ITEM_ID) Is Null))
    ORDER BY PO_TYPE.PO_TYPE_DESC, PO_STATUS.PO_STATUS_DESC, PURCHASE_ORDER.PO_STATUS_DATE;

     

    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.