Explanation of Cash Export Report (cash-09)
- Article Type: General
- Product: Aleph
- Product Version: 21
Desired Outcome Goal:
What SQL query can pull transactions mirroring cash-09 -- including 04 id type from z308 (Patron's ID)?
Procedure:
Step 1: Running this SQL query should give the same results as running cash-09:
COLUMN TransfDt FORMAT 99999999
COLUMN SystemID FORMAT A12
COLUMN 04ID FORMAT A12
COLUMN Type FORMAT A6
COLUMN Amount FORMAT $9999D99
COLUMN Status FORMAT A6
SELECT
z31.Z31_TRANSFER_DATE "TransfDt",
substr( z31.Z31_REC_KEY, 1, 12) "SystemID",
decode(substr( z308.z308_rec_key, 1, 2), '04', substr( z308.z308_rec_key, 3, 12), '') "04ID",
decode( z31.Z31_CREDIT_DEBIT, 'C', 'Credit', 'Debit') "Type",
(z31.Z31_SUM/100) "Amount",
Z31_STATUS "Status"
FROM zzz50.z31 Z31 left outer join zzz50.z308 Z308
on substr( z31.Z31_REC_KEY, 1, 12) = z308.Z308_ID
WHERE substr( z31.Z31_REC_KEY, 1, 12) = z308.Z308_ID
AND z31.Z31_TRANSFER_DATE >= '0'
AND z31.Z31_TRANSFER_DATE <= '0'
AND z31.Z31_SUM > '00000000000000'
GROUP BY
z31.Z31_TRANSFER_DATE,
substr( z31.Z31_REC_KEY, 1, 12),
substr(z308.z308_rec_key, 1, 2),
z31.Z31_CREDIT_DEBIT,
z31.Z31_SUM,
substr( z308.z308_rec_key, 3, 12),
Z31_STATUS;
Additional Information
[1] cash-09 is not only pulling from z31 (Cash). For example, in the xxx library, running cash-09 returns 17 cash transactions. However, using SELECT COUNT(*) FROM Z31; returns only 13 transactions in that file.
[2] 04ID only has contents if an 04 ID exists in Z308.
None of the items in this table have a transfer date, so it is set to 0.
Category: Circulation (500)
Subject: Cash management (500)
- Article last edited: 10/8/2013