Finding canceled serials HOLs connected to open orders

Last updated
Save as PDF
Share
1. Share
2. Tweet
3. Share

Product: Aleph
Product Version: 20, 21, 22, 23
Relevant for Installation Type: Dedicated-Direct, Direct, Local, Total Care

Description:
I am trying to write a report of all cancelled serials orders. While I can look at the holdings record in the 008 field/ byte 06 for "Not currently received", the serials librarian asked me to double check against the order record to make sure that the order is not 'SV'. The main problem here is that the holdings and ADM records are not linked similar to the bib and ADM. I find only 383 links in the z103 and they are all mistakes.

So, I attempted to link the holdings and the order through the item record. The problems is that we have a lot of holdings without linked items. Now I am thinking that maybe I should be looking at the subscription record since it has both a link to the holdings and to the order. Again, not everything has subscription records.

What would you suggest as the most accurate way to check if a holdings record still has an open order?

Resolution:
Below is some SQL which finds cases of HOL's with an 008 field/ byte 06 ("Not currently received"), connected to bibs connected to ADMs with a status SV order.

SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ z13u_rec_key from sba60.z13u where Z13U_USER_DEFINED_12_CODE = '008' and substr (Z13U_USER_DEFINED_12,7,1) = '5' and z13u_rec_key
in (select substr (z103_rec_key,6,9) from sba60.z103 where z103_rec_key_1 like 'SBA01%' and substr (z103_rec_key_1,6,9)
in (select substr (z103_rec_key_1,6,9) from sba01.z103 where z103_rec_key_1 like 'SBA01%' and z103_rec_key like 'SBA50%' and substr (z103_rec_key_1,6,9)
in (select substr (z68_rec_key,1,9) from sba50.z68 where z68_order_status = 'SV' ) ) );

The subscription, which contains the Z16-HOL-DOC-NUMBER, is clearer in that it links a specific order/subscription to a specific HOL.

Article last edited: 12-Mar-2016