Add collection code from item record to holdings record

Description

a) We are in the Alma implementation process and found that because our collection code is in the item record and not the holding record in Aleph, the collection code did not migrate over to Alma. We would like to add the collection code to the holding records in Aleph so that it will migrate. Please advise how we can globally enhance existing holding records with the collection codes from the linked item records. 

[Note:  We have: 

  X852-ITEM-OVERRIDE=N 
  X852-ITEM-OVERRIDE-C=N 

because don't want to update the item collection from the holding because we have items from different collections linked to the same holding record. ] 

b) Also, would it possible to also have a query to retrieve only items from the REF and AAUTH collections? The regular query will retrieve too many items because all our technical reports which will not be migrating to Alma do not have collection codes in the holding records.

Resolution

[Note: Normally, you should not have items from different collections linked to the same holding record and X852-ITEM-OVERRIDE=Y and X852-ITEM-OVERRIDE-C=Y would be the normal settings.]

a) The following SQL can be used to produce a list of xxx60 HOL record numbers from items with collection "MAIN":
s+ xxx50 
  set echo off 
  set pause off 
  set feed off 
  set heading off 
  SET PAGESIZE 0 
  spool hol_spool 

  select Z30_HOL_DOC_NUMBER_X || 'XXX60' from xxx50.z30 where Z30_COLLECTION = 'MAIN '; 

  spool off;

That list was used as input to this ./xxx01/tab/import script (executed by fix_doc_doc_do_file_08):

  1 852##                    STOP-SCRIPT                    $$c= 
  2 852##                    ADD-SUBFIELD                   c,MAIN 

(Step 1 is necessary to make sure that "MAIN" is not added to 852 fields which already have a $$c.)

b)  The following will give you the HOL record numbers of items which have z30_collection REF or AAUTH: 

s+ xxx50
  select /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ 
  unique substr (a.z30_rec_key,1,9), a.z30_hol_doc_number_x from xxx50.z30 a, xxx50.z30 b 
  where substr (a.z30_rec_key,1,9) = substr (b.z30_rec_key,1,9) and 
  a.z30_hol_doc_number_x = b.z30_hol_doc_number_x and 
  a.z30_collection ^= b.z30_collection and 
  a.z30_collection in ('REF ', 'AAUTH'); 

• Article last edited: 5-Feb-2020