• 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