Skip to main content
ExLibris

Knowledge Assistant

BETA
 
  • Subscribe by RSS
  • Back
    Aleph

     

    Ex Libris Knowledge Center
    1. Search site
      Go back to previous article
      1. Sign in
        • Sign in
        • Forgot password
    1. Home
    2. Aleph
    3. Knowledge Articles
    4. Explanation of Cash Export Report (cash-09)

    Explanation of Cash Export Report (cash-09)

    1. Last updated
    2. Save as PDF
    3. Share
      1. Share
      2. Tweet
      3. Share
    1. Additional Information
    • 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
    View article in the Exlibris Knowledge Center
    1. Back to top
      • Expired Users Get "Page Not Found" Instead of ILL Error Message
      • Explanation of dates for serials check-in
    • Was this article helpful?

    Recommended articles

    1. Article type
      Topic
      Language
      English
      Product
      Aleph
    2. Tags
      1. 21
      2. Cash management (500)
      3. Circulation (500)
      4. contype:kba
      5. Prod:Aleph
      6. Type:General
    1. © Copyright 2025 Ex Libris Knowledge Center
    2. Powered by CXone Expert ®
    • Term of Use
    • Privacy Policy
    • Contact Us
    2025 Ex Libris. All rights reserved