Description 

How can we get a list of patrons, with their blocks and notes (-- both global and local --)?

Resolution 

As described in the article " Where are patron "Local Blocks and Notes" stored? ", the global blocks and notes are stored in the $usr_library z303 (Global patron) table and the local blocks and notes, in the ADM library z305 (Local patron) table.

1. The following SQL shows all the global block #1's (where "xxx00" is the $usr_library):

SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ z303_rec_key, Z303_DELINQ_N_1 from xxx00.z303 where Z303_DELINQ_N_1 is not null order by z303_rec_key;

2. The following SQL shows all the global block #1's, in a multi-ADM situation, for patrons who have a local (z305) record for a particular ADM library (where "xxx00" is the $usr_library and "xxx50", the ADM library):

SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ z303_rec_key, Z303_DELINQ_N_1 from xxx00.z303 where z303_rec_key in (select substr (z305_rec_key,1,12) from xxx50.z305)AND (Z303_DELINQ_N_1 is not null) order by z303_rec_key;

3. The following SQL shows all the local block #1's for a particular xxx50 ADM library:

SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ z305_rec_key, Z305_DELINQ_N_1 from xxx50.z305 where Z305_DELINQ_N_1 is not null order by z305_rec_key;

If patrons have global block z303_delinq_n_2's or z303_delinq_n_3's or local block z305_delinq_n_2's or z305_delinq_n_3's, the same could be done for those. 

Note that patrons can also be blocked due to overdues and fines.  Those blocks are created dynamically from the xxx50 tab_check_circ table, using values from the xxx50 tab_block_circ table.
 

• Article last edited: 1-Feb-2018