!------------------------------------------------------------------------------
!
!      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
! 01/09/13 73010   Gardner    Clean up queries.
!
!-------------------------------------------------------------------------------
!                        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
  JOIN <ARCH_DB>..science 
    ON sci_data_set_name = ads_data_set_name
 WHERE ads_data_set_name = '<OSF_DATASET>'
   AND ads_archive_class = '<ARCHIVE_CLASS>'
   AND ads_best_version  = 'Y'
END:
!
! Update pso key, pep_id, start and end times in 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>..archive_data_set_all
  JOIN <ARCH_DB>..science
    ON sci_data_set_name = ads_data_set_name
 WHERE ads_data_set_name = '<OSF_DATASET>'
   AND ads_archive_class = '<ARCHIVE_CLASS>'
   AND ads_best_version  = 'Y'
END:
!
! 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>..archive_data_set_all
  JOIN <ARCH_DB>..oms_data
    ON oms_data_set_name = ads_data_set_name
 WHERE 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>..archive_data_set_all
    JOIN <ARCH_DB>..oms_summary 
      ON oss_data_set_name = ads_data_set_name
   WHERE ads_data_set_name = '<OSF_DATASET>'
     AND ads_archive_class = '<ARCHIVE_CLASS>'
     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>..archive_data_set_all
    JOIN <ARCH_DB>..oms_summary
      ON oss_data_set_name = ads_data_set_name
   WHERE ads_data_set_name = SUBSTRING('<OSF_DATASET>',1,8)+'1'
     AND ads_archive_class=  '<ARCHIVE_CLASS>'
     AND ads_best_version='Y'
END:
!
! 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 <ARCH_DB>..archive_data_set_all
  JOIN <OPUS_DB>..qolink_sms q
    ON ads_data_set_name LIKE
           q.inst+q.program_id+q.obset_id+q.ob_number+'J'
  JOIN <OPUS_DB>..executed e
    ON e.program_id = q.program_id
   AND e.obset_id   = q.obset_id
   AND e.ob_number  = q.ob_number
  JOIN <OPUS_DB>..eng_dataset_pads p
    ON p.inst       = q.inst
   AND p.exp_type   = 'EPC' 
 WHERE ads_data_set_name = '<OSF_DATASET>'
   AND ads_archive_class = '<ARCHIVE_CLASS>'
   AND ads_best_version  = 'Y'
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
  JOIN <ARCH_DB>..acs_mama
    ON acm_data_set_name = ads_data_set_name
 WHERE 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>..stis_cumulative_images 
  JOIN <ARCH_DB>..archive_data_set_all
    ON ads_data_set_name = ssi_data_set_name 
   AND ads_archive_class = ssi_archive_class
 WHERE ssi_data_set_name = '<OSF_DATASET>' 
   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>..cos_cumulative_images
  JOIN <ARCH_DB>..archive_data_set_all
    ON ads_data_set_name = csi_data_set_name
   AND ads_archive_class = csi_archive_class
 WHERE csi_data_set_name = '<OSF_DATASET>' 
   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
  JOIN stis_cumulative_images 
    ON ssi_data_set_name   = ads_data_set_name
   AND ssi_generation_date = ads_generation_date
 WHERE ads_data_set_name = '<OSF_DATASET>' 
   AND ads_archive_class = '<ARCHIVE_CLASS>' 
   AND ads_best_version  = 'Y' 
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
  JOIN cos_cumulative_images
    ON csi_data_set_name   = ads_data_set_name
   AND csi_generation_date = ads_generation_date
 WHERE ads_data_set_name = '<OSF_DATASET>' 
   AND ads_archive_class = '<ARCHIVE_CLASS>' 
   AND ads_best_version  = 'Y' 
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 <ARCH_DB>..archive_data_set_all
  JOIN <OPUS_DB>..executed e
    ON ads_data_set_name LIKE SUBSTRING('<OSF_DATASET>',1,1)+
         e.program_id+e.obset_id+e.ob_number+e.executed_flg
 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 <ARCH_DB>..archive_data_set_all
  JOIN <OPUS_DB>..podnames pn 
    ON pn.podname = ads_data_set_name
  JOIN <OPUS_DB>..poddans pd 
    ON pd.podname = pn.podname
 WHERE ads_data_set_name = '<OSF_DATASET>'
   AND ads_archive_class = 'POD'
   AND ads_best_version  = 'Y'
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 <ARCH_DB>..archive_data_set_all
  JOIN <OPUS_DB>..podnames pn 
    ON pn.podname = ads_data_set_name
  JOIN <OPUS_DB>..poddans pd
    ON pd.podname = pn.podname
 WHERE ads_data_set_name = '<OSF_DATASET>'
   AND ads_archive_class = 'POD' 
   AND ads_best_version  = 'Y'
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>..assoc_status
  JOIN <ARCH_DB>..archive_data_set_all
    ON ads_data_set_name = ast_asn_id
 WHERE 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>..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>..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:
!
! 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>..science
  JOIN <ARCH_DB>..assoc_member
    ON asm_member_name = sci_data_set_name
  JOIN <ARCH_DB>..archive_data_set_all
    ON ads_data_set_name = asm_asn_id
 WHERE ads_data_set_name = '<OSF_DATASET>'
   AND ads_archive_class = 'ASN'
   AND ads_best_version  = 'Y'
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>..science
  JOIN <ARCH_DB>..archive_data_set_all
    ON ads_data_set_name = sci_data_set_name
  JOIN <ARCH_DB>..assoc_orphan
    ON aso_data_set_name = ads_data_set_name
 WHERE aso_asn_id        = '<OSF_DATASET>'
   AND ads_archive_class = 'CAL'
   AND ads_best_version  = 'Y'
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>..archive_data_set_all
  JOIN <ARCH_DB>..science
    ON sci_data_set_name = ads_data_set_name
  JOIN <ARCH_DB>..assoc_member 
    ON asm_member_name = sci_data_set_name
 WHERE asm_asn_id        = '<OSF_DATASET>'
   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>..archive_data_set_all
  JOIN <ARCH_DB>..science
    ON sci_data_set_name = ads_data_set_name
  JOIN <ARCH_DB>..assoc_orphan
    ON aso_data_set_name = sci_data_set_name
 WHERE aso_asn_id        = '<OSF_DATASET>'
   AND ads_archive_class ='CAL'
   AND ads_best_version  ='Y'
END:
!
! 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_program_id = shp_program_id,
       ads_obset_id   = shp_obset_id,
       ads_obsnum     = shp_obsnum,
       ads_pep_id     = shp_proposid,
       ads_instrument = shp_instrume,
       ads_start_time = nsa_expstart,
       ads_end_time   = nsa_expend
  FROM <ARCH_DB>..archive_data_set_all
  JOIN <ARCH_DB>..shp_data
    ON shp_data_set_name = ads_data_set_name
  JOIN <ARCH_DB>..nicmos_a_data
    ON nsa_data_set_name = shp_data_set_name
 WHERE shp_asn_id        = '<OSF_DATASET>'
   AND ads_archive_class = 'CAL'
   AND ads_best_version  = 'Y'
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 = shp_program_id,
       ads_obset_id   = shp_obset_id,
       ads_obsnum     = shp_obsnum,
       ads_pep_id     = shp_proposid,
       ads_instrument = shp_instrume,
       ads_start_time = aca_date_obs,
       ads_end_time   = DATEADD(SECOND,aca_exptime,aca_date_obs)
  FROM <ARCH_DB>..archive_data_set_all
  JOIN <ARCH_DB>..shp_data
    ON shp_data_set_name = ads_data_set_name
  JOIN <ARCH_DB>..acs_a_data
    ON aca_data_set_name = shp_data_set_name
 WHERE shp_asn_id        = '<OSF_DATASET>'
   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 = shp_program_id,
       ads_obset_id   = shp_obset_id,
       ads_obsnum     = shp_obsnum,
       ads_pep_id     = shp_proposid,
       ads_instrument = shp_instrume,
       ads_start_time = w3a_expstart, 
       ads_end_time   = w3a_expend
  FROM <ARCH_DB>..archive_data_set_all
  JOIN <ARCH_DB>..shp_data
    ON shp_data_set_name = ads_data_set_name
  JOIN <ARCH_DB>..wfc3_a_data
    ON w3a_data_set_name = shp_data_set_name
 WHERE shp_asn_id        = '<OSF_DATASET>' 
   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 = shp_program_id,
       ads_obset_id   = shp_obset_id,
       ads_obsnum     = shp_obsnum,
       ads_pep_id     = shp_proposid,
       ads_instrument = shp_instrume,
       ads_start_time = csa_expstart, 
       ads_end_time   = csa_expend
  FROM <ARCH_DB>..archive_data_set_all
  JOIN <ARCH_DB>..shp_data
    ON shp_data_set_name = ads_data_set_name
  JOIN <ARCH_DB>..cos_a_data
    ON csa_data_set_name = shp_data_set_name
 WHERE shp_asn_id        = '<OSF_DATASET>' 
   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
 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>..qolink_sms q
  JOIN <OPUS_DB>..asn_members m
    ON q.program_id = m.program_id
   AND q.obset_id   = m.obset_id
   AND q.ob_number  = m.member_num
 WHERE 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:
