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

    Borrowing Library Statistic Report (ill-63) p_ill_63

    • Article Type: General
    • Product: Aleph
    • Product Version: 18.01

    Description:
    What statuses are included in the ILL Borrowing Statistical Report (p_ill_63) Pending, Cancelled, Not Supplied and Filled and Total columns?

    Resolution:
    The ill_63 service produces a statistical report for the Borrowing library.

    The statistics report includes the following information:

    Supplier code.
    Total no. of requests - Requests that were sent to a supplier.
    No. of pending requests - Requests that were sent to a supplier or shipped but have not arrived yet.
    No. of cancelled requests - Requests that were sent to a supplier and cancelled.
    No. of unsupplied requests - Requests that were sent to a supplier and replied with 'Unfilled'.
    No. of filled requests.
    Percentage of filled requests.
    Average number of days for filling the requests - Number of days from the time the request was made until it was shipped.
    Average number of days for shipping the request - Number of days from the time the request was shipped until it was received by the requester.
    Average number of days for returning the request - Number of days from the time the request was returned from the requester until it was checked-in by the lending library.

    To understand this report, it is important to differentiate between Z411 Borrowing Supplier Requests and Z410 Borrowing Patron Requests.
    One Z410 Request can generate one or more Z411 Requests.
    It is Z411 Borrowing Requests to Suppliers that are being counted by this report, not Z410 Borrowing Patron Requests or a combination of Z411 and Z410.
    Therefore, if 3 Z411 Requests from the same Z410 Request fall within the dates specified in the report definition, then they will each be counted in the report.

    Total = not
    DSF – Daemon Send Failed
    NEB – Ready for BL
    NEW – New
    NTS – No Time to Supply
    PND – Pending. Use this status for supplier requests that are temporarily placed on hold.
    SNA – Supplier Not Active
    WPR – Waiting patron response

    Pending
    CRP – Conditional Reply
    EST – Estimate Reply
    HPL – Hold Placed
    LCR – Location Reply
    RTY – Retry
    SHP – Shipped
    SV – Sent to Supplier/Vendor
    WCR – Cancel Requested (SV, then requested to cancel)
    WSP – Will be Supplied

    Cancelled
    CA – Cancelled (SV, then requested to cancel)

    Unsupplied
    EXP - Expired
    UNF – Unfilled

    Supplied/Filled
    CLS – Closed (entire process loan/return complete)
    DMG – Damaged
    LON – Received by Library
    LOP – Loaned to Patron
    LST – Lost (after LON)
    OVD – Overdue
    RCL – Recalled
    RNA – Renewal Accepted
    RNR – Renewal Rejected
    RT – Returned
    RTP – Returned by Patron
    WRN – Waiting for Renewal Reply

    The following SQL recreates the data in the report (global, not the "returnable" vs "non-returnable" sections). Switch out the last "and" for the "ands" listed below (TOTAL, UNF, CA, PEND, FILLED) in order to get the same numbers (adjust ILL Unit and From-To values to suit your report).

    select count( *)
    from z410 a, z411 b
    where substr( b.Z411_REC_KEY, 1, 9) = a.Z410_REC_KEY
    and a.Z410_ILL_UNIT = 'AAAA'
    and b.Z411_OPEN_DATE >= 20070301 and b.Z411_OPEN_DATE <= 20070813
    and b.Z411_STATUS NOT IN ('NEW', 'NTS', 'PND', 'WPR');

    "ANDs" to Switch
    TOTAL
    and b.Z411_STATUS NOT IN ('NEW', 'NTS', 'PND', 'WPR');
    UNF
    and b.Z411_STATUS IN ('UNF', 'EXP');
    CA
    and b.Z411_STATUS = 'CA';
    PEND
    and b.Z411_STATUS IN ('SV', 'WSP', 'CRP', 'HPL', 'EST', 'WCR', 'SHP', 'RTY', 'LCR');
    FILLED
    and b.Z411_STATUS IN ('LON', 'RT', 'CLS', 'WRN', 'RNR', 'RNA', 'LST', 'DMG', 'RCL', 'OVD', 'LOP', 'RTP');

    Use the following SQL to show "uncounted" statuses: DEL, E00, SNA, and DSF.

    select count( b.Z411_STATUS), b.Z411_STATUS
    from z410 a, z411 b
    where substr( b.Z411_REC_KEY, 1, 9) = a.Z410_REC_KEY
    and a.Z410_ILL_UNIT = 'AAAA'
    and b.Z411_OPEN_DATE >= 20070301 and b.Z411_OPEN_DATE <= 20070813
    and b.Z411_STATUS NOT IN ('NEW', 'NTS', 'PND', 'WPR', 'UNF', 'EXP', 'CA', 'LON', 'RT', 'CLS', 'WRN', 'RNR', 'RNA', 'LST', 'DMG', 'RCL', 'OVD', 'LOP', 'RTP', 'SV', 'WSP', 'CRP', 'HPL', 'EST', 'WCR', 'SHP', 'RTY', 'LCR')
    group by b.Z411_STATUS;

    The file output uses template: ill-out-statistic-report.xsl

    A sample log file name from $alephe_scratch: usm40_p_ill_63.00246

    Command Line:
    csh -f $aleph_proc/p_ill_63 active_library,report_name,from_date,to_date,partner_list,ill_unit,report_format,user_name
    examples
    csh -f $aleph_proc/p_ill_63 USM40,jill63,20000101,20071119,,ILL_LAW,00,LAW, > /exlibris/aleph/a18_1/alephe/scratch/usm40_p_ill_63.20071129law

    job_list
    02 14:23:00 Y USM40 p_ill_63 USM40,jill63,20000101,20071119,,ILL_LAW,00,LAW,

    [faq]


    • Article last edited: 10/8/2013