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

    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
    • Was this article helpful?