Voyager to Alma Migration Query: Identify Purchase Orders without Invoices
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.
Posted as is. If you need assistance in running custom SQL queries in Prepackaged Access Reports, consult the Voyager Customer Listserv. .