Voyager to Alma Migration Query: Identify Item Records where Item Permanent Location is not the same as the MFHD Location (location mismatch)
Before migrating to Alma, you may want to resolve any mismatches between an item's Permanent Location and its MFHD Location.
In the migration process, if an item record carries a different permanent location from the location in the holding record, Alma will create multiple holding records: one MFHD for the holding and items that match that holding; and one or more for any items that have a different permanent location, with the item permanent location being added to the new MFHD. This may not be desirable. For more information discuss this with your migration team.
This query, which you can run in Voyager's Prepackaged Access Reports, will help you identify items where the Permanent Location is different from the MFHD Location. Note that this query includes the barcode if it exists ("outer join" or "left join"):
SELECT DISTINCT LOCATION_1.LOCATION_CODE AS ItemPermLoc, LOCATION.LOCATION_CODE AS MFHDLoc, utf8to16([mfhd_master].[DISPLAY_CALL_NO]) AS Display_Call_No, MFHD_MASTER.MFHD_ID, ITEM.ITEM_ID, ITEM_BARCODE.ITEM_BARCODE
FROM ((((MFHD_MASTER INNER JOIN MFHD_ITEM ON MFHD_MASTER.MFHD_ID = MFHD_ITEM.MFHD_ID) INNER JOIN ITEM ON MFHD_ITEM.ITEM_ID = ITEM.ITEM_ID) INNER JOIN LOCATION ON MFHD_MASTER.LOCATION_ID = LOCATION.LOCATION_ID) INNER JOIN LOCATION AS LOCATION_1 ON ITEM.PERM_LOCATION = LOCATION_1.LOCATION_ID) LEFT JOIN ITEM_BARCODE ON ITEM.ITEM_ID = ITEM_BARCODE.ITEM_ID
WHERE (((MFHD_MASTER.LOCATION_ID)<>[ITEM].[PERM_LOCATION]));
BONUS SQL
The following query is a pass through version of the above query (note that it does not include the barcode):
SELECT
a.location_id, b.perm_location, b.temp_location, c.mfhd_id, c.item_id
FROM
mfhd_master a, item b, mfhd_item c
WHERE
a.mfhd_id = c.mfhd_id
and b.item_id = c.item_id
and a.location_id != b.perm_location
ORDER BY c.mfhd_id;
This pass through query includes the item barcode but note that it excludes MFHDs without a barcode (it uses an "inner join"):
SELECT
a.mfhd_id, a.location_id, b.item_id, b.perm_location, b.temp_location, c.mfhd_id, c.item_id, d.item_barcode
FROM
mfhd_master a, item b, mfhd_item c, item_barcode d
WHERE
a.mfhd_id = c.mfhd_id
and b.item_id = c.item_id
and c.item_id = d.item_id
and a.location_id != b.perm_location
ORDER BY c.mfhd_id;
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. If you need assistance in running custom SQL queries in Prepackaged Access Reports, consult the Voyager Customer Listserv.