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

    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