• 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