Help identifying old (more than 20 days) "in transit-discharged" or "in transit-on hold" status items
- Product: Voyager
- Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare
Question
An easy way to identify items that have had a status of “In Transit- Discharged” or "In Transit- On Hold" for more than 20 days.
Answer
The following example Prepackaged Access Reports query could be used to supplement the "Missing in Transit Report" that is provided in Voyager. The following example query lists items that have a status of "In Transit-Discharged" or "In Transit-On Hold" for more than 20 days (that value can be changed if you want). This example query produces a list could be used to help you search for items that may have been lost or misplaced.
SELECT BIB_TEXT.BIB_ID, MFHD_MASTER.MFHD_ID,
ITEM.ITEM_ID,
LOCATION_1.LOCATION_NAME AS ItemTempLoc,
LOCATION.LOCATION_NAME AS ItemPermLoc,
MFHD_MASTER.DISPLAY_CALL_NO, MFHD_MASTER.NORMALIZED_CALL_NO,
MFHD_ITEM.ITEM_ENUM, MFHD_ITEM.CHRON, MFHD_ITEM.Year,
ITEM_BARCODE.ITEM_BARCODE, ITEM_BARCODE.BARCODE_STATUS,
ITEM_STATUS_TYPE.ITEM_STATUS_DESC,
ITEM_STATUS.ITEM_STATUS_DATE, BIB_TEXT.TITLE_BRIEF
FROM ((((((MFHD_ITEM INNER JOIN MFHD_MASTER
ON MFHD_ITEM.MFHD_ID = MFHD_MASTER.MFHD_ID)
INNER JOIN BIB_MFHD
ON MFHD_MASTER.MFHD_ID = BIB_MFHD.MFHD_ID)
INNER JOIN BIB_TEXT
ON BIB_MFHD.BIB_ID = BIB_TEXT.BIB_ID)
INNER JOIN ((ITEM_STATUS INNER JOIN ITEM
ON ITEM_STATUS.ITEM_ID = ITEM.ITEM_ID)
LEFT JOIN ITEM_BARCODE
ON ITEM.ITEM_ID = ITEM_BARCODE.ITEM_ID)
ON MFHD_ITEM.ITEM_ID = ITEM.ITEM_ID)
INNER JOIN LOCATION
ON ITEM.PERM_LOCATION = LOCATION.LOCATION_ID)
LEFT JOIN LOCATION AS LOCATION_1
ON ITEM.TEMP_LOCATION = LOCATION_1.LOCATION_ID)
INNER JOIN ITEM_STATUS_TYPE
ON ITEM_STATUS.ITEM_STATUS = ITEM_STATUS_TYPE.ITEM_STATUS_TYPE
WHERE (((ITEM_BARCODE.BARCODE_STATUS)="1")
AND ((ITEM_STATUS.ITEM_STATUS_DATE)<Now()-20)
AND ((ITEM_STATUS.ITEM_STATUS)="9"
Or (ITEM_STATUS.ITEM_STATUS)="10"))
ORDER BY LOCATION_1.LOCATION_NAME,
LOCATION.LOCATION_NAME, MFHD_MASTER.NORMALIZED_CALL_NO,
MFHD_ITEM.ITEM_ENUM, MFHD_ITEM.CHRON, MFHD_ITEM.Year;
Additional Information
See also: How do you remove items that are no longer missing from the Missing in Transit Report?
Constructing custom SQL queries by request and troubleshooting unexpected results from customer-created SQL queries falls outside the scope of Support. The above has been posted for informational purposes. Voyager-L and Developer Network are useful resources for finding helpful custom SQL or obtaining assistance from peers in troubleshooting custom queries.
- Article last edited: 06-Nov-2020