!-------------------------------------------------------------------------------
!
!      hkp_hst_query.lis
!
!
!      This file is used by the update_db_tool to specify the templates
!      for the queries used to update the housekeeping fields in the
!      ARCH_DB database.
!
!
!-------------------------------------------------------------------------------
!                          REVISION HISTORY
!-------------------------------------------------------------------------------
!            PR
!   DATE   NUMBER  Developer  Description
! -------- ------  ---------  --------------------------------------------------
! 05/31/05 53016   J.Baum     Initial version
! 07/01/05 53246   J.Baum     Eliminate wildcards to stop deadlocks
! 08/07/06 56359   MSwam      Make qolink_sms ASN update optional (for DMS asns)
! 05/05/08 58278   Sherbert   Updates for COS, WFC3 ASN members
! 10/21/09 60589   MSwam      POD ads_instrument update only works if 
!                                       podnames,poddans rows are filled
! 01/15/09 64310   Sherbert   Make POD files proprietary
! 04/30/10 64292   Sherbert   Discontinue nulling out PSO/pep_id
! 07/15/10 65295   Sherbert   Fill date field in dads_archive to signal cat done
! 08/20/10 65688   Sherbert   EDT, PDQ ADS PSO instrument.
! 09/20/10 65745   Sherbert   new sti/cos cums; remove update for deprecated tbl
! 10/14/10 65688   Sherbert   do pep_id for PDQ after all
! 03/14/12 70833   LThompson  remove SET_QOLINK_STATUS_UNCOLLECTED query 
!
!-------------------------------------------------------------------------------
!                        Application Format
!-------------------------------------------------------------------------------
!
! This query source file resides in OPUS_DEFINITION_DIR. The file uses four 
! tokens: "NAME:", "TITLE:", "ZERO_RECORDS_ALLOWED:", and "END:". The token 
! NAME: is followed by a label used by the resource file consisting of a single 
! word. The token TITLE: is followed by a multi-word phrase used for log files. 
! The token ZERO_RECORDS_ALLOWED" is followed by Y or N. The text of the query 
! starts after the ZERO_RECORDS_ALLOWED line. The last line of the query is 
! followed by a line containing only the token "END:".
!
! The queries in this file are templates that have bracketed keywords that are
! replaced by the update_db_tool. The keywords <ARCH_DB>, <OPUS_DB>, 
! <OSF_DATASET>, <ARCHIVE_CLASS>, <INSTRUMENT>, and <INSTRUMENT_NAME> are 
! substituted just before the query is executed..
!
! NOTE: The revised update_db_tool supports multiple instances of a
! substitution per query line. 
!
! A successful query must update 1 or more records unless the
! ZERO_RECORDS_ALLOWED keyword is set to Y. 
!
! General format:
!
! All token names are required. 
! All tokens must start at the begininng of a line.
! Tokens and query NAME are all all-cap words
! Here is an example:
!
!NAME: AN_ALL_CAPS_NAME_WITH_NO_BLANKS
!TITLE: A user friendly title - mixed case
!ZERO_RECORDS_ALLOWED: (Y or N)
!first line of SQL
!second line of SQL
!more lines of SQL 
!last line of SQL 
!END:
!-------------------------------------------------------------------------------
!                        HST HKP QUERIES
!-------------------------------------------------------------------------------
!
! Update gendate and release date in science table
NAME: SCIENCE_UPDATE
TITLE: Update sci_generation_date and sci_release_date
ZERO_RECORDS_ALLOWED: N
update <ARCH_DB>..science set
      sci_generation_date = ads_generation_date,
      sci_release_date = ads_release_date
from <ARCH_DB>..archive_data_set_all
where sci_data_set_name = '<OSF_DATASET>'  and
      ads_data_set_name = '<OSF_DATASET>' and
      ads_archive_class='<ARCHIVE_CLASS>' and
      ads_best_version='Y'
END:
!
! Update archive_data_set_all
NAME: UPDATE_ADS_TABLE
TITLE: Update identifiers and dates in ADS table
ZERO_RECORDS_ALLOWED: N
update <ARCH_DB>..archive_data_set_all set
      ads_instrument = sci_instrume,
      ads_program_id = sci_program_id,
      ads_obset_id = sci_obset_id,
      ads_obsnum=sci_obsnum,
      ads_pep_id=sci_pep_id,
      ads_start_time = sci_start_time,
      ads_end_time=sci_stop_time
from <ARCH_DB>..science
where sci_data_set_name = '<OSF_DATASET>' and
      ads_data_set_name = '<OSF_DATASET>' and
      ads_archive_class='<ARCHIVE_CLASS>' and
      ads_best_version='Y'
END:
! 
!---------------------64292------------------------
! ! NULL out pso and pep_id values for any record 
! ! where ads_best_version does not equal Yes
! !
! NAME: NULL_OLD_RECORDS
! TITLE: Null out selected fields in old records
! ZERO_RECORDS_ALLOWED: Y
! update <ARCH_DB>..archive_data_set_all set
!       ads_program_id = null,
!       ads_obset_id = null,
!       ads_obsnum = null,
!       ads_pep_id = null
! where ads_data_set_name = '<OSF_DATASET>' and
!       ads_archive_class='<ARCHIVE_CLASS>' and
!       ads_best_version <NE> 'Y'
! END:
!---------------------64292------------------------
!
! Update ADS using OMS data
NAME: UPDATE_ADS_WITH_OMS
TITLE: Update ADS table with oms_data fields
ZERO_RECORDS_ALLOWED: N
update <ARCH_DB>..archive_data_set_all set
      ads_instrument = '<INSTRUMENT>',
      ads_program_id = oms_program_id,
      ads_obset_id = oms_obset_id,
      ads_obsnum = oms_obsnum,
      ads_start_time = oms_startime,
      ads_end_time= oms_endtime,
      ads_pep_id = oms_proposid
from <ARCH_DB>..oms_data
where oms_data_set_name = '<OSF_DATASET>' and
      ads_data_set_name = '<OSF_DATASET>' and
      ads_archive_class='<ARCHIVE_CLASS>' and 
      ads_best_version='Y'
END:
!
! Update ADS using OMS summary
NAME: UPDATE_ADS_WITH_SUMMARY
TITLE: Update ADS table with oms_summary fields
ZERO_RECORDS_ALLOWED: N
if exists (select * from oms_summary 
           where oss_data_set_name = '<OSF_DATASET>') 
  update <ARCH_DB>..archive_data_set_all set
      ads_instrument = '<INSTRUMENT>',
      ads_program_id = oss_program_id,
      ads_obset_id = oss_obset_id,
      ads_obsnum = oss_obsnum,
      ads_start_time = oss_tstrtime,
      ads_end_time = oss_tendtime,
      ads_pep_id = oss_proposid
  from  <ARCH_DB>..oms_summary
  where ads_data_set_name = '<OSF_DATASET>' and
      ads_archive_class=  '<ARCHIVE_CLASS>' and
      ads_data_set_name = oss_data_set_name and
      ads_data_set_name = oss_data_set_name and 
      ads_best_version='Y'
else 
  update <ARCH_DB>..archive_data_set_all set
      ads_instrument = '<INSTRUMENT>',
      ads_program_id = oss_program_id,
      ads_obset_id = oss_obset_id,
      ads_obsnum = oss_obsnum,
      ads_start_time = oss_tstrtime,
      ads_end_time = oss_tendtime,
      ads_pep_id = oss_proposid
  from  <ARCH_DB>..oms_summary
  where ads_data_set_name = substring('<OSF_DATASET>',1,8)+'1' and
      ads_archive_class=  '<ARCHIVE_CLASS>' and
      ads_data_set_name = oss_data_set_name and
      ads_data_set_name = oss_data_set_name and 
      ads_best_version='Y'
END:
!
!---------------------64292------------------------
!! Null ADS data using oms_summary 
!NAME: NULL_OLD_OMS_RECORDS
!TITLE: Null out selected fields for OMS associations
!ZERO_RECORDS_ALLOWED: Y
!update <ARCH_DB>..archive_data_set_all set
!      ads_program_id = null,
!      ads_obset_id = null,
!      ads_obsnum = null,
!      ads_pep_id = null
!from  <ARCH_DB>..oms_summary
!where oss_asn_id = '<OSF_DATASET>' and
!      ads_archive_class=  '<ARCHIVE_CLASS>' and
!      ads_data_set_name = oss_data_set_name and
!      ads_best_version <NE> 'Y'
!END:
!---------------------64292------------------------
!
! Update ADS for EPC datasets
NAME: UPDATE_ADS_FOR_EPC
TITLE: Update ADS table for EPC with OPUS_DB data
ZERO_RECORDS_ALLOWED: N
update <ARCH_DB>..archive_data_set_all set
      ads_instrument = '<INSTRUMENT>',
      ads_program_id = substring('<OSF_DATASET>',2,3),
      ads_obset_id = substring('<OSF_DATASET>',5,2),
      ads_obsnum = substring('<OSF_DATASET>',7,2),
      ads_pep_id = convert(int,e.proposal_id),
      ads_start_time = dateadd(mi, p.start_pad, dateadd(dy,
          convert(int,substring(q.start_time,6,3))-1,
          'JAN 01 ' + substring(q.start_time,1,4) + ' ' +
          substring(q.start_time,10,2)+ ':' +
          substring(q.start_time,13,2) + ':' +
          substring(q.start_time,16,2) )), 
      ads_end_time = dateadd(mi, p.end_pad, dateadd(dy,
          convert(int,substring(q.end_time,6,3))-1,
          'JAN 01 ' + substring(q.end_time,1,4) + ' ' +
          substring(q.end_time,10,2)+ ':' +
          substring(q.end_time,13,2) + ':' +
          substring(q.end_time,16,2) )) 
from  <OPUS_DB>..executed e,
      <OPUS_DB>..qolink_sms q,
      <OPUS_DB>..eng_dataset_pads p
where ads_data_set_name='<OSF_DATASET>' and
      ads_archive_class=  '<ARCHIVE_CLASS>' and
      ads_best_version='Y' and
      q.program_id = substring('<OSF_DATASET>',2,3) and
      q.obset_id   = substring('<OSF_DATASET>',5,2) and
      q.ob_number  = substring('<OSF_DATASET>',7,2) and
      e.program_id = substring('<OSF_DATASET>',2,3) and
      e.obset_id   = substring('<OSF_DATASET>',5,2) and
      e.ob_number  = substring('<OSF_DATASET>',7,2) and
      p.inst       = substring('<OSF_DATASET>',1,1) and
      p.exp_type   = 'EPC' 
END:
!
! update only ADS start time using OPUS file_times table 
NAME: UPDATE_ADS_START_TIME
TITLE: Update ADS start time using file_times
ZERO_RECORDS_ALLOWED: N
update <ARCH_DB>..archive_data_set_all
set ads_start_time = 
      (select  max(dateadd(dy,
           convert(int,substring(window_start,5,3))-1,'JAN 01 ' +
           substring(window_start,1,4) + ' ' +
           substring(window_start,8,2)+ ':' +
           substring(window_start,10,2) + ':' +
           substring(window_start,12,2) )) 
       from <OPUS_DB>..file_times
       where dataset_name='<OSF_DATASET>')
where ads_data_set_name='<OSF_DATASET>' and
      ads_archive_class='<ARCHIVE_CLASS>' and
      ads_best_version='Y'
END:
!
! update only ADS end time using OPUS file_times table 
NAME: UPDATE_ADS_END_TIME
TITLE: Update ADS end time using file_times
ZERO_RECORDS_ALLOWED: N
update <ARCH_DB>..archive_data_set_all
set ads_end_time = 
      (select  max(dateadd(dy,
           convert(int,substring(window_stop,5,3))-1,'JAN 01 ' +
           substring(window_stop,1,4) + ' ' +
           substring(window_stop,8,2)+ ':' +
           substring(window_stop,10,2) + ':' +
           substring(window_stop,12,2) )) 
       from <OPUS_DB>..file_times
       where dataset_name='<OSF_DATASET>' )
where ads_data_set_name='<OSF_DATASET>' and
      ads_archive_class='<ARCHIVE_CLASS>' and 
      ads_best_version='Y'
END:
!
!   update gen date in acs_mama record
NAME: UPDATE_ACS_MAMA
TITLE: Update gen date in acs_mama
ZERO_RECORDS_ALLOWED: N
update <ARCH_DB>..acs_mama set
      acm_generation_date = ads_generation_date
from <ARCH_DB>..archive_data_set_all
where acm_data_set_name='<OSF_DATASET>' and
      ads_data_set_name='<OSF_DATASET>' and
      ads_archive_class='<ARCHIVE_CLASS>' and
      ads_best_version='Y'
END:
! NOTE that if we ever ingest/catalog ACM data again
! we need to add a query to fill in ads_start_time 
! and ads_end_time from the acs_mama table.
!
! PR 65688
! Update ads_instrument for ACM data
NAME: UPDATE_ACS_MAMA_IN_ADS
TITLE: Update ads_instrument for ACS omama
ZERO_RECORDS_ALLOWED: N
UPDATE <ARCH_DB>..archive_data_set_all 
SET    ads_instrument = 'ACS'
WHERE  ads_data_set_name  = '<OSF_DATASET>' and
       ads_archive_class='ACM' and 
       ads_best_version='Y' 
END:
!
! PR 65745/65747
!   update gen date in STI cum img record
NAME: UPDATE_STI_CUMULATIVE_IMAGES
TITLE: Update gen date in stis_cumulative_images 
ZERO_RECORDS_ALLOWED: N
UPDATE <ARCH_DB>..stis_cumulative_images 
   SET ssi_generation_date = ads_generation_date
  FROM <ARCH_DB>..archive_data_set_all
 WHERE ssi_data_set_name='<OSF_DATASET>' 
   AND ssi_data_set_name=ads_data_set_name
   AND ssi_archive_class=ads_archive_class
   AND ads_best_version='Y'
END:
!
! PR 65745
!   update gen date in COS cum img record
NAME: UPDATE_COS_CUMULATIVE_IMAGES
TITLE: Update gen date in cos_cumulative_images 
ZERO_RECORDS_ALLOWED: N
UPDATE <ARCH_DB>..cos_cumulative_images 
   SET csi_generation_date = ads_generation_date
  FROM <ARCH_DB>..archive_data_set_all
 WHERE csi_data_set_name='<OSF_DATASET>' 
   AND csi_data_set_name=ads_data_set_name
   AND csi_archive_class=ads_archive_class
   AND ads_best_version='Y'
END:
!
! PR 65745/65747
! Update ads_instrument for SSI data
NAME: UPDATE_ADS_FOR_STI_CUMULATIVE_IMAGES
TITLE: Update ads_instrument for STIS cumulative images
ZERO_RECORDS_ALLOWED: N
UPDATE <ARCH_DB>..archive_data_set_all 
   SET ads_instrument = 'STIS'
     , ads_start_time = ssi_expstart 
     , ads_end_time   = ssi_expend 
  FROM archive_data_set_all,
       stis_cumulative_images
 WHERE ads_data_set_name='<OSF_DATASET>' 
   AND ads_archive_class='<ARCHIVE_CLASS>' 
   AND ads_best_version='Y' 
   AND ssi_data_set_name=ads_data_set_name
   AND ssi_generation_date=ads_generation_date
END:
!
! PR 65745
! Update ads_instrument for CSI data
NAME: UPDATE_ADS_FOR_COS_CUMULATIVE_IMAGES
TITLE: Update ads_instrument for COS cumulative images
ZERO_RECORDS_ALLOWED: N
UPDATE <ARCH_DB>..archive_data_set_all 
   SET ads_instrument = 'COS'
     , ads_start_time = csi_expstart 
     , ads_end_time   = csi_expend 
  FROM archive_data_set_all,
       cos_cumulative_images
 WHERE ads_data_set_name='<OSF_DATASET>' 
   AND ads_archive_class='<ARCHIVE_CLASS>' 
   AND ads_best_version='Y' 
   AND csi_data_set_name=ads_data_set_name
   AND csi_generation_date=ads_generation_date
END:
!
! Update ads_instrument for DIA data ids
NAME: UPDATE_DIA_INSTRUMENT
TITLE: Update ads_instrument for class DIA
ZERO_RECORDS_ALLOWED: N
update <ARCH_DB>..archive_data_set_all set
      ads_instrument = '<INSTRUMENT>'
where ads_data_set_name  = '<OSF_DATASET>' and
      ads_archive_class='DIA' and 
      ads_best_version='Y' 
END:
!
! PR 65688
! Update ads_instrument for EDT data ids
NAME: UPDATE_EDT_INSTRUMENT
TITLE: Update ads_instrument for class EDT
ZERO_RECORDS_ALLOWED: N
UPDATE <ARCH_DB>..archive_data_set_all 
   SET ads_instrument = '<INSTRUMENT>'
     , ads_program_id = SUBSTRING('<OSF_DATASET>',2,3)
     , ads_obset_id   = SUBSTRING('<OSF_DATASET>',5,2)
     , ads_obsnum     = SUBSTRING('<OSF_DATASET>',7,2)
     , ads_pep_id     = convert(int,e.proposal_id)
  FROM <OPUS_DB>..executed e
 WHERE ads_data_set_name  = '<OSF_DATASET>' 
   AND ads_archive_class='EDT' 
   AND ads_best_version='Y' 
END:
!
! update instrument in archive_data_set_all for pod class
NAME: UPDATE_POD_INSTRUMENT
TITLE: Update ads_instrument for class POD
ZERO_RECORDS_ALLOWED: N
update <ARCH_DB>..archive_data_set_all set
      ads_instrument = '<POD_INSTRUMENT>'
from  <OPUS_DB>..podnames pn,
      <OPUS_DB>..poddans pd
where ads_data_set_name = '<OSF_DATASET>' and
      ads_archive_class='POD' and 
      ads_best_version='Y' and
      pn.podname = '<OSF_DATASET>' and
      pd.podname = '<OSF_DATASET>'
END:
!
! PR 64310
! Make ALL pod files proprietary until a date in the future
! while avoiding the Unix Millennium Bug, Y2K38
NAME: UPDATE_POD_RELEASE_DATE
TITLE: Update ads_release_date for class POD
ZERO_RECORDS_ALLOWED: N
UPDATE <ARCH_DB>..archive_data_set_all SET
      ads_release_date = '1/1/2038 00:00:00'
FROM  <OPUS_DB>..podnames pn,
      <OPUS_DB>..poddans pd
WHERE ads_data_set_name = '<OSF_DATASET>' and
      ads_archive_class='POD' and 
      ads_best_version='Y' and
      pn.podname = '<OSF_DATASET>' and
      pd.podname = '<OSF_DATASET>'
END:
!
! The following queries only refer to asns
!
! update gen date in assoc_status record for all associations
NAME: UPDATE_ASSOC_STATUS
TITLE: Update gen date in assoc_status table
ZERO_RECORDS_ALLOWED: N
update <ARCH_DB>..assoc_status set
      ast_generation_date = ads_generation_date
from <ARCH_DB>..archive_data_set_all
where ast_asn_id = '<OSF_DATASET>' and
      ads_data_set_name='<OSF_DATASET>' and
      ads_archive_class='<ARCHIVE_CLASS>' and
      ads_best_version='Y'
END:
!
!  Update the ASN class record. Use science record of first product name or
!  when there are no products then use science record of first orphan name
NAME: UPDATE_ADS_FOR_ASN
TITLE: Update HST identifiers for ASN class
ZERO_RECORDS_ALLOWED: N
if exists (select * from assoc_member
           where asm_asn_id = '<OSF_DATASET>' and
           substring(asm_member_type,1,4) = 'PROD')
  update <ARCH_DB>..archive_data_set_all set
        ads_program_id = sci_program_id,
        ads_obset_id = sci_obset_id,
        ads_obsnum = substring('<OSF_DATASET>',7,3),
        ads_instrument = sci_instrume,
        ads_pep_id = sci_pep_id,
        ads_start_time = sci_start_time,
        ads_end_time = sci_stop_time
  from <ARCH_DB>..archive_data_set_all, 
       <ARCH_DB>..science
  where ads_data_set_name = '<OSF_DATASET>' and
        ads_archive_class = 'ASN' and
        ads_best_version = 'Y' and
        sci_data_set_name =  
           (select min(asm_member_name) from assoc_member
           where asm_asn_id = '<OSF_DATASET>' and
           substring(asm_member_type,1,4) = 'PROD')
else 
  update <ARCH_DB>..archive_data_set_all set
    ads_program_id = sci_program_id,
    ads_obset_id = sci_obset_id,
    ads_obsnum = substring('<OSF_DATASET>',7,3),
    ads_instrument = sci_instrume,
    ads_pep_id = sci_pep_id,
    ads_start_time = sci_start_time,
    ads_end_time = sci_stop_time
  from <ARCH_DB>..archive_data_set_all, 
       <ARCH_DB>..science
  where ads_data_set_name = '<OSF_DATASET>' and
        ads_archive_class = 'ASN' and
        ads_best_version = 'Y' and
        sci_data_set_name =  
           (select min(aso_data_set_name)
            from <ARCH_DB>..assoc_orphan
            where aso_asn_id = '<OSF_DATASET>')
END:
!
!---------------------64292------------------------
! ! NULL out pso and pep_id values for ASN record 
! ! where ads_best_version does not equal Yes
! NAME: NULL_OLD_ASN_RECORDS
! TITLE: Null out identifiers for old ASN class
! ZERO_RECORDS_ALLOWED: Y
! update <ARCH_DB>..archive_data_set_all set
!   ads_program_id = null,
!   ads_obset_id = null,
!   ads_obsnum = null,
!   ads_pep_id = null
! from <ARCH_DB>..archive_data_set_all 
! where ads_data_set_name = '<OSF_DATASET>' and
!   ads_archive_class = 'ASN' and 
!   ads_best_version <NE> 'Y' 
! END:
!---------------------64292------------------------
!
! the following queries update science records using ASN class data
!
! update gen date of ACS science records that are products or singletons -
! there may be no records for broken asn without products
NAME: SCIENCE_UPDATE_FOR_ASN
TITLE: Update gen date in science table for asn products
ZERO_RECORDS_ALLOWED: Y
update <ARCH_DB>..science set
      sci_generation_date = ads_generation_date,
      sci_release_date = ads_release_date
from <ARCH_DB>..archive_data_set_all,
     <ARCH_DB>..science,
     <ARCH_DB>..assoc_member     
where ads_data_set_name = '<OSF_DATASET>' and
      ads_archive_class='ASN' and 
      ads_best_version='Y' and
      asm_asn_id = '<OSF_DATASET>' and
      sci_data_set_name = asm_member_name
END:
!
! update gen date of ACS science records that are orphans
NAME: UPDATE_SCIENCE_ORPHANS
TITLE: Update gen date in science table for asn orphans
ZERO_RECORDS_ALLOWED: Y
update <ARCH_DB>..science set
      sci_generation_date = ads_generation_date,
      sci_release_date = ads_release_date
from <ARCH_DB>..archive_data_set_all,
     <ARCH_DB>..science,
     <ARCH_DB>..assoc_orphan
where aso_asn_id = '<OSF_DATASET>' and
      ads_data_set_name=aso_data_set_name and 
      ads_archive_class='CAL' and 
      ads_best_version='Y' and
      sci_data_set_name=aso_data_set_name 
END:
!
! Updates CAL record for products 
NAME: UPDATE_ADS_FOR_ASN_PRODUCTS
TITLE: Update HST identifiers for CAL association products
ZERO_RECORDS_ALLOWED: Y
update <ARCH_DB>..archive_data_set_all set
      ads_instrument = sci_instrume,
      ads_program_id = sci_program_id,
      ads_obset_id   = sci_obset_id,
      ads_obsnum     = sci_obsnum,
      ads_pep_id     = sci_pep_id,
      ads_start_time = sci_start_time,
      ads_end_time   = sci_stop_time
from <ARCH_DB>..science, 
     <ARCH_DB>..archive_data_set_all,
     <ARCH_DB>..assoc_member
where 
      asm_asn_id = '<OSF_DATASET>' and
      sci_data_set_name = asm_member_name and
      ads_data_set_name = sci_data_set_name and
      ads_best_version  = 'Y' and 
      ads_archive_class = 'CAL'
END:
!
!Updates CAL record for orphan members
NAME: UPDATE_ADS_ORPHANS
TITLE: Update HST identifiers for CAL association orphans
ZERO_RECORDS_ALLOWED: Y
update <ARCH_DB>..archive_data_set_all set
      ads_instrument = sci_instrume,
      ads_program_id = substring(ads_data_set_name,2,3),
      ads_obset_id =   substring(ads_data_set_name,5,2),
      ads_obsnum =     substring(ads_data_set_name,7,2),
      ads_pep_id =     sci_pep_id,
      ads_start_time = sci_start_time,
      ads_end_time =   sci_stop_time
from <ARCH_DB>..science, 
     <ARCH_DB>..archive_data_set_all,
     <ARCH_DB>..assoc_orphan
where aso_asn_id        = '<OSF_DATASET>' and
      ads_archive_class ='CAL' and
      ads_best_version  ='Y' and 
      ads_data_set_name = aso_data_set_name and
      sci_data_set_name = aso_data_set_name 
END:
!
!---------------------64292------------------------
! ! Now NULL out pso and pep_id values for any other archive_data_set_all
! ! record  where ads_best_version does not equal Yes
! !
! ! products and non-orphan members
! NAME: NULL_OLD_ASN_MEMBERS
! TITLE: Null out identifiers for old association members
! ZERO_RECORDS_ALLOWED: Y
! update <ARCH_DB>..archive_data_set_all set
!       ads_program_id = null,
!       ads_obset_id =   null,
!       ads_obsnum =     null,
!       ads_pep_id =     null
! from <ARCH_DB>..archive_data_set_all,
!      <ARCH_DB>..assoc_member
! where ads_archive_class='CAL' and
!       asm_asn_id = '<OSF_DATASET>' and
!       ads_data_set_name = asm_data_set_name and
!       ads_best_version <NE> 'Y'
! END:
!
!---------------------64292------------------------
! ! orphans
! NAME: NULL_OLD_ASN_ORPHANS
! TITLE: Null out identifiers for old association orphans
! ZERO_RECORDS_ALLOWED: Y
! update <ARCH_DB>..archive_data_set_all set
!       ads_program_id = null,
!       ads_obset_id =   null,
!       ads_obsnum =     null,
!       ads_pep_id =     null
! from <ARCH_DB>..archive_data_set_all,
!      <ARCH_DB>..assoc_orphan
! where ads_archive_class='CAL' and
!       aso_asn_id = '<OSF_DATASET>' and
!       ads_data_set_name = aso_data_set_name and
!       ads_best_version <NE> 'Y'
! END:
!---------------------64292------------------------
!
! the following queries are for asn exposure members that get data from
! instrument specific tables (only one data_id for each query)
!
!Updates CAL record for NICMOS member exposures 
NAME: UPDATE_ADS_FOR_NICMOS
TITLE: Update HST identifiers for NICMOS exposures
ZERO_RECORDS_ALLOWED: Y
update <ARCH_DB>..archive_data_set_all set
      ads_instrument = sci_instrume,
      ads_program_id = asm_program_id,
      ads_obset_id =   asm_obset_id,
      ads_obsnum =     asm_obsnum,
      ads_pep_id =     sci_pep_id,
      ads_start_time = nsa_expstart,
      ads_end_time =   nsa_expend
from <ARCH_DB>..science, 
     <ARCH_DB>..archive_data_set_all,
     <ARCH_DB>..assoc_member,
     <ARCH_DB>..nicmos_a_data
where sci_asn_id = '<OSF_DATASET>' and
      ads_archive_class='CAL' and
      ads_best_version='Y' and 
      asm_asn_id = '<OSF_DATASET>' and
      ads_data_set_name = asm_data_set_name and
      nsa_data_set_name = asm_data_set_name 
END:
!
!Updates CAL record for ACS member exposures 
NAME: UPDATE_ADS_FOR_ACS
TITLE: Update HST identifiers for ACS exposures
ZERO_RECORDS_ALLOWED: Y
update <ARCH_DB>..archive_data_set_all set
      ads_program_id = asm_program_id,
      ads_obset_id = asm_obset_id,
      ads_obsnum = asm_obsnum,
      ads_start_time = aca_date_obs,
      ads_end_time = dateadd(second,aca_exptime,aca_date_obs),
      ads_instrument = '<INSTRUMENT>',
      ads_pep_id = 
          (select max(sci_pep_id)
           from <ARCH_DB>..science
           where sci_asn_id = '<OSF_DATASET>')
from <ARCH_DB>..archive_data_set_all, 
     <ARCH_DB>..assoc_member,
     <ARCH_DB>..acs_a_data
where asm_asn_id = '<OSF_DATASET>' and
      aca_data_set_name = asm_data_set_name and 
      ads_data_set_name = asm_data_set_name and
      ads_archive_class='CAL' and 
      ads_best_version='Y'
END:
!
!Updates CAL record for WFC3 member exposures 
! Copied from ACS, but modified.
NAME: UPDATE_ADS_FOR_WFC3
TITLE: Update HST identifiers for WFC3 exposures
ZERO_RECORDS_ALLOWED: Y
UPDATE <ARCH_DB>..archive_data_set_all 
   SET ads_program_id = asm_program_id,
       ads_obset_id = asm_obset_id,
       ads_obsnum = asm_obsnum,
       ads_start_time = w3a_expstart, 
       ads_end_time = w3a_expend, 
       ads_instrument = '<INSTRUMENT>',
       ads_pep_id = 
          (select max(sci_pep_id)
           from <ARCH_DB>..science
           where sci_asn_id = '<OSF_DATASET>')
FROM <ARCH_DB>..archive_data_set_all, 
     <ARCH_DB>..assoc_member,
     <ARCH_DB>..wfc3_a_data
WHERE asm_asn_id = '<OSF_DATASET>' 
  AND w3a_data_set_name = asm_data_set_name 
  AND ads_data_set_name = asm_data_set_name 
  AND ads_archive_class='CAL' 
  AND ads_best_version='Y'
END:
!
!Updates CAL record for COS member exposures 
! Copied from ACS like WFC3 above but is it right?
NAME: UPDATE_ADS_FOR_COS
TITLE: Update HST identifiers for COS exposures
ZERO_RECORDS_ALLOWED: Y
UPDATE <ARCH_DB>..archive_data_set_all 
   SET ads_program_id = asm_program_id,
       ads_obset_id = asm_obset_id,
       ads_obsnum = asm_obsnum,
       ads_start_time = csa_expstart, 
       ads_end_time = csa_expend, 
       ads_instrument = '<INSTRUMENT>',
       ads_pep_id = 
          (select max(sci_pep_id)
           from <ARCH_DB>..science
           where sci_asn_id = '<OSF_DATASET>')
FROM <ARCH_DB>..archive_data_set_all, 
     <ARCH_DB>..assoc_member,
     <ARCH_DB>..cos_a_data
WHERE asm_asn_id = '<OSF_DATASET>' 
  AND csa_data_set_name = asm_data_set_name 
  AND ads_data_set_name = asm_data_set_name 
  AND ads_archive_class='CAL' 
  AND ads_best_version='Y'
END:
!
! The following query is for special science data types - temporary TV data
!
!  At this time data_id cos and wf3 are used for thermovac data TVL and TVI
!  where the ads_pep_id is always 100138
NAME: UPDATE_ADS_FOR_THERMOVAC 
TITLE: Update identifiers for ThermoVac datasets
ZERO_RECORDS_ALLOWED: N
update <ARCH_DB>..archive_data_set_all set
      ads_program_id = substring('<OSF_DATASET>',2,3),
      ads_obset_id = substring('<OSF_DATASET>',5,2),
      ads_obsnum = substring('<OSF_DATASET>',7,3),
      ads_instrument = '<INSTRUMENT>',
      ads_pep_id = 100138
from <ARCH_DB>..archive_data_set_all 
where ads_data_set_name = '<OSF_DATASET>' and
      ads_archive_class = '<ARCHIVE_CLASS>' and
      ads_best_version = 'Y' 
END:
!
! The following queries must executed last. This insures that all previous
! queries were successful.
! 
! Always set to E and always use two-character ob_number
! Allow zero records to reingest old wf2 without qolink_sms records
NAME: SET_QOLINK_STATUS_SINGLE
TITLE: Update qolink_sms.status to E for non-asn dataset
ZERO_RECORDS_ALLOWED: Y
update <OPUS_DB>..qolink_sms set
      status = 'E'
where program_id = substring('<OSF_DATASET>',2,3) and
      obset_id = substring('<OSF_DATASET>',5,2) and
      ob_number = substring('<OSF_DATASET>',7,2)
END:
! 
! Always try to set STIS asn to E and always use three-character ob_number
! (won't find a match for DMS asns, so zero records has to be allowed)
!
NAME: SET_QOLINK_STATUS_STIS
TITLE: Update single qolink_sms.status to E for STIS asn
ZERO_RECORDS_ALLOWED: Y
update <OPUS_DB>..qolink_sms set
      status = 'E'
where program_id = substring('<OSF_DATASET>',2,3) and
      obset_id = substring('<OSF_DATASET>',5,2) and
      ob_number = substring('<OSF_DATASET>',7,3)
END:
! 
! Set collected asn members and products to E using asn_members data
NAME: SET_QOLINK_STATUS_COLLECTED
TITLE: Update qolink_sms.status to E for collected members
ZERO_RECORDS_ALLOWED: N
update <OPUS_DB>..qolink_sms set
      status = 'E'
from  <OPUS_DB>..asn_members m,
      <OPUS_DB>..qolink_sms q
where q.program_id = m.program_id and
      q.obset_id   = m.obset_id and
      q.ob_number  = m.member_num and
      m.association_id = '<OSF_DATASET>' and
     (m.member_status = 'C' or m.product_status = 'C')
END:
!
! Set date in dads_archive to signal successful end of cataloging
NAME: SET_CAT_COMPLETE_DATE
TITLE: Upate dads_archive.catalog_date to signal successful cataloging
! Interactive tools do NOT update dads_archive, so allow 0 records.
ZERO_RECORDS_ALLOWED: Y
UPDATE <OPUS_DB>..dads_archive 
   SET catalog_date = getdate()
 WHERE dataset_name = '<OSF_DATASET>'
   AND archclass = '<ARCHIVE_CLASS>'
   AND archdate = ( SELECT MAX(archdate) 
                    FROM <OPUS_DB>..dads_archive 
                    WHERE dataset_name = '<OSF_DATASET>'
                      AND archclass = '<ARCHIVE_CLASS>'
                  )
END:
! Update ADS for PDQ datasets for PR 65688 
! If PDQ is an association, we need 3-char (vs 2) obsnum 
!    so check if <OSF_DATASET> exists in assoc_member 
! Else we have a singleton and only need a 2-char obsnum
! ARCH PRs to backfill pep_id, so we should fill that, too.
! In order to get information about proposal_id 
!    from executed, we need to know a member ob_number, 
!    which we can also find in assoc_member.  
NAME: UPDATE_ADS_FOR_PDQ
TITLE: Update ADS table for PDQ with OPUS_DB data
ZERO_RECORDS_ALLOWED: N
if exists (SELECT * FROM <ARCH_DB>..assoc_member
           WHERE asm_asn_id = '<OSF_DATASET>'
             AND SUBSTRING(asm_member_type,1,4) = 'PROD')
UPDATE <ARCH_DB>..archive_data_set_all 
   SET ads_instrument = '<INSTRUMENT>'
     , ads_program_id = substring('<OSF_DATASET>',2,3)
     , ads_obset_id   = substring('<OSF_DATASET>',5,2)
     , ads_obsnum     = substring('<OSF_DATASET>',7,3)
     , ads_pep_id     = convert(int,e.proposal_id)
  FROM <OPUS_DB>..executed e
 WHERE ads_data_set_name = '<OSF_DATASET>'
   AND ads_archive_class = '<ARCHIVE_CLASS>' 
   AND ads_best_version  = 'Y' 
   AND e.program_id = substring('<OSF_DATASET>',2,3) 
   AND e.obset_id   = substring('<OSF_DATASET>',5,2) 
   AND e.ob_number  = 
           (SELECT MIN(asm_obsnum) FROM assoc_member
             WHERE asm_asn_id = '<OSF_DATASET>' and
           SUBSTRING(asm_member_type,1,4) != 'PROD')
else 
UPDATE <ARCH_DB>..archive_data_set_all 
   SET ads_instrument = '<INSTRUMENT>'
     , ads_program_id = substring('<OSF_DATASET>',2,3)
     , ads_obset_id   = substring('<OSF_DATASET>',5,2)
     , ads_obsnum     = substring('<OSF_DATASET>',7,2)
     , ads_pep_id     = convert(int,e.proposal_id)
  FROM <OPUS_DB>..executed e
 WHERE ads_data_set_name = '<OSF_DATASET>'
   AND ads_archive_class = '<ARCHIVE_CLASS>' 
   AND ads_best_version  = 'Y' 
   AND e.program_id = substring('<OSF_DATASET>',2,3) 
   AND e.obset_id   = substring('<OSF_DATASET>',5,2) 
   AND e.ob_number  = substring('<OSF_DATASET>',7,2) 
END:
