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

    Patron Cash has incorrect bib, item, and loan information

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

    This morning (08/25/11) we are looking at Patron 190470 on Production server. They currently have 1.00 fine in Cash, under the Cash Details, Item Tab the data is incorrect, the wrong call no, collection, barcode, etc. Under the Loan Tab the information seems off as well, the Loan Time is after the Due Time and Original due date is back in 2010.

    This is just one example of multiple instances like this. We are wondering what is going on.

    This is like KB 16384-34306.

    As described in another KB, 3986, bytes 21-29 of the z31_key are the z36/z36h_loan_number. The z31 for this patron 190470 shows 000010391 as the loan number:

    z31_key: 001660283000010 000010391 ...

    The loan number is used in connecting the cash record to the loan history record.

    The z36_loan_number is assigned using the abc50 util g/2 last-loan-number counter. The counter currently has a value of "10860".

    But the following SQL shows that this is too low: there are 46,505 z36h records with values higher than this:

    abc50@ALEPH20> select count(*) from z36h where z36h_number > '000010860';
    **** Hit return to continue ****


    Per the scenario in KB 16384-34306, when an item is returned and the circ program finds that the z36_number (the z36h_number to be) is already in use, it writes the following error in the pc_server log:

    Oracle error: io_z36h_write
    ORA-00001: unique constraint (ABC50.Z36H_ID2) violated

    We see these errors in the pc_server_6991 logs starting on July 21. The first occurrence was on July 21 at 8:42 (in pc_server_6991.log.2107.095550). The logs preceding that time don't have the error; *all* those later do.

    This shows that the last-loan-number was set to "10" at that time:

    abc50@ALEPH20> select min (z36_number) from z36 where z36_loan_date = '20110721';
    **** Hit return to continue ****


    All the z31's created since that time have gotten the unique constraint violated and have a wrong z36_number/z36h_number: it's already in use by another loan and erroneously connects the cash transaction to an incorrect item.

    The following SQL shows that the highest z36h_number is 000068018:

    abc50@ALEPH20> select max(z36h_number) from z36h;
    **** Hit return to continue ****


    So ... the abc50 last-loan-number should be corrected (in util g/2) to 68018. This will prevent future z36s from being assigned conflicting z36_number values.

    But the following SQL showed thousands of z36's whose z36_number was shared with an existiing z36h:

    SQL> select substr (z36_number,1,5), count(*) from z36 where z36_number in (select z36h_number from z36h) group by substr (z36_number,1,5);

    This was solved by adding 90 million to each bad z36_number.

    Since, when bytes 21-29 of the z31_key are blank, the program uses other info in the z31_key to locate the correct bib and item records, cash records which already had this problem were corrected by blanking out bytes 21-29 of the z31 record.

    If you have questions, contact

    • Article last edited: 10/8/2013