How can I identify all ILL requests that were cancelled due to being available online?

Description

We are trying to gather stats on how many ILLs are cancelled due to the item being available online. 

In this scenario we send out letter ill-patron-letter-e-00 

Resolution

All ILL action logs are stored in the Oracle table z415 of your ILL library (xxx40). The available actions are depending on the configuration of ./xxx40/tab/tab46.lng.

Printing a letter to the patron (such as patron letter ill-patron-letter-e-00.xsl) can be identified as transaction '06 Letter was Sent to the Patron' 

Example of such a transaction 

01 z415 \ 
02 z415_rec_key \ 
03 trans_number ......000116040 
02 z415_rec_key_2 \ 
03 doc_number ........000000568 
03 sequence ..........000000000000006 
02 z415_user_name ......MASTER 
02 z415_open_date ......20180119 
02 z415_open_hour ......1608 
02 z415_open_second ....0889 
02 z415_action_date ....00000000 
02 z415_trans_type .....OUT 
02 z415_trans ..........06 
02 z415_alpha ..........L 
02 z415_text ...........Letter was Sent to the Patron 
02 z415_data ...........Patron letter : Online available - <> - sent to 
02 z415_triggered ......Y 
02 z415_partner_code ... 
02 z415_ill_unit .......ILL_LAW 
 

The corresponding entry in .\xxx40\tab\pc_tab_exp_field.lng reads like this in the example: 

ILL-BOR-PRINT L Online available e 

You should be able to identify all transactions of type '06' with text 'Online available' in z415_data field using SQL query. 

• Article last edited: 19-Jan-2018