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

    Amount owed to patron query

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

    Description:
    I have a library requesting a report of patrons to whom the LIBRARY owes money. Since I am not finding any canned reports (am I overlooking something?), I am trying an SQL query but am having difficulties finding the correct z field. Can you help with a query that would provide that information to my user?

    Resolution:
    I did not find any standard report that does what you want to do, so I think SQL is the ticket. The information you want is mostly in the Z31 table, in the Z31_SUM field. This field can be either a credit or a debit, depending on the value of Z31_CREDIT_DEBIT. I think what you need to do is to sum up the amounts for each patron and determine if they are positive (they owe us) or negative (we owe them). If the sum is negative, we can then go out and get more information about the specific amounts.

    I found some SQL that was offered by Christine Moulen from MIT for transferring charges to the Bursar, and I made some changes to make it closer to what you need. You will need to change the various libraries to match your setup, and may need to tweak some other parts, I don't know. I haven't analyzed each field and test in the SQL below to know if it might cause problems. In any case, I hope this will get you on your way.

    SELECT
    Z308_REC_KEY PATRON_ID,
    Z303_NAME "NAME",
    Z31_DATE_X "DATE",
    Z31_SUM "SUM",
    Z31_DESCRIPTION "DESC",
    SUBSTR(Z31_REC_KEY,1,12) "ALEPH_ID",
    Z30_BARCODE "BARCODE",
    Z30_SUB_LIBRARY "LIB",
    Z30_CALL_NO "CALL#",
    Z13_TITLE "TITLE",
    Z305_BOR_TYPE "BOR_TYPE",
    Z31_CREDIT_DEBIT "CRED_DEBT"
    FROM
    USR00.Z303,
    USM50.Z305,
    USR00.Z308,
    USM50.Z31,
    USM50.Z30,
    USM01.Z13,
    USM50.Z103
    WHERE RTRIM(Z303_REC_KEY) = Z308_ID
    AND RTRIM(Z303_REC_KEY) = RTRIM(SUBSTR(Z305_REC_KEY,1,12))
    AND SUBSTR(Z305_REC_KEY,13,5) = 'ALEPH'
    AND SUBSTR(Z308_REC_KEY,1,2) = '02'
    AND Z308_STATUS = 'AC'
    AND SUBSTR(Z31_REC_KEY,1,12) = Z303_REC_KEY
    AND Z31_STATUS = 'O'
    AND Z30_REC_KEY = SUBSTR(Z31_KEY,1,15)
    AND SUBSTR(Z103.Z103_REC_KEY_1,1,5) = 'USM01'
    AND SUBSTR(Z103.Z103_REC_KEY,6,9) = SUBSTR(Z30_REC_KEY,1,9)
    AND SUBSTR(Z103.Z103_REC_KEY_1,6,9) = Z13.Z13_REC_KEY
    AND Z305_BOR_STATUS <> '08'
    AND SUBSTR(Z31_REC_KEY,1,12) IN
    (
    SELECT
    SUBSTR(Z31_REC_KEY,1,12)
    FROM
    USM50.Z31
    WHERE Z31_STATUS = 'O'
    GROUP BY
    SUBSTR(Z31_REC_KEY,1,12)
    HAVING
    SUM(TO_NUMBER(CONCAT
    (
    TRANSLATE(Z31_CREDIT_DEBIT,'CD','-0'),
    TRANSLATE(Z31_SUM,' ','0')
    ))) < 0
    )
    ORDER BY
    SUBSTR(Z31_REC_KEY,1,12),
    Z30_BARCODE,
    Z31_DESCRIPTION,
    Z31_CREDIT_DEBIT DESC;

    The following is a similar, somewhat simpler query from Fang Peng at SUNY Stony Brook:

    set echo off
    set pause off
    set term off
    set show off
    set feed off
    set verify off
    set pagesize 58

    column PATRON_ID format a14 heading "Patron-ID"
    column BALANCE format $999,999.99 heading "Balance"

    spool patron-credit-may18.lst
    select substr(z31_rec_key,1,12) PATRON_ID,
    sum(to_number(concat(translate(z31_credit_debit,'CD','-0'),translate(z31_sum/100,'
    ','0')))) BALANCE
    from z31
    where z31_status = 'O'
    group by substr(Z31_REC_KEY,1,12) HAVING
    sum(to_number(concat(translate(z31_credit_debit,'CD','-0'),translate(z31_sum/100,'
    ','0')))) < 1
    order by BALANCE desc;

    spool off
    exit;

    <end Fang Peng query>


    • Article last edited: 10/8/2013
    • Was this article helpful?