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

    Report of Monograph Orders with Only One Item and One Invoice Line

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

    Desired Outcome Goal:
    A report of monograph orders that have only one item and one invoice line.

    Procedure:
    Use the following SQL:

    > s+ xxx50
    SQL> select /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ z68_order_number, z68_rec_key from z68, z30, z601 where Z68_ORDER_TYPE = 'M' and rtrim (z68_order_number) = rtrim (Z30_ORDER_NUMBER)
    and substr (z30_rec_key,1,9) not in (select substr (z30_rec_key,1,9) from z30 group by substr (z30_rec_key,1,9) having count(*) > 1 )
    and substr (Z601_REC_KEY_3,1,9) = substr (z30_rec_key,1,9) and z601_type = 'INV' and substr (Z601_REC_KEY_3,1,9) not in (select substr (z601_rec_key_3,1,9) from z601 where z601_type = 'INV' group by substr (z601_rec_key_3,1,9) having count(*) > 1 )
    order by z68_order_number asc;

    Category: Acquisitions

    Subject: Orders


    • Article last edited: 2/12/2014