#----------------------------------------------------------------------------
#
# Name: ingest_update_archive.pm
#
# This perl script is used to contain the subroutines for the UPDATE task 
# of the Ingest pipeline. 
#
# Implementation note; the PrintMsg subroutine prints to both the standard
# output and to a FILEHANDLE reference named $trl
#
# History:
# Date     OPR      Who         Reason
# -------- -------- ----------  ---------------------------------------------
# 10/27/09 61672    Sherbert    to aid testing, move subs to a .pm 
# 05/19/10 44712    MSwam       replace ST_DBlib with Perl DBI
# 06/30/10 64432    MSwam       Use single quotes for SQLServer
# 07/10/12 71706    Heller      first OWL version
#----------------------------------------------------------------------------
# set up external routines
unshift @INC,(split /:/, $ENV{PATH});
require 'printmsg.pl';          # prints a formatted status message
require 'do_dbi_pkg.pl';            # run query returning only record count

 # prototypes seem to make order of definition less important
 sub reset_cal_dataset($$$) ;
 sub get_cal_members ($$$) ;
 sub get_PSO($$) ;

 #constants
 my $true   = 1;
 my $false  = 0;

 my $dbVerbose = 0 ;
 if ( defined( $ENV{"STDB_REPORT_LEVEL"} ) ) {
    # perl is annoying.   Get nasty-gram if this ENV var is NOT defined.
    # Would like to avoid nasty-grams.
    $dbVerbose = ( $ENV{"STDB_REPORT_LEVEL"} =~ /^STDB/ );
    if ( $dbVerbose ) { PrintMsg ("D", "STDB_REPORT_LEVEL high!", $nm); }
 }
 # Testing 
 #$ENV{"MSG_REPORT_LEVEL"} = "MSG_ALL" ;
 my $dbVerbose = 1 ;

#----------------------------------------------------------------------------
# Name: put_install_id_list
# # no args
# !!! Only use if testing from the command-line (must uncomment in main prog)
# Takes the place of reading these 3 keywords from the resource file
# All other resource file keywords can be set using setenv in a csh script
#----------------------------------------------------------------------------
sub put_install_id_list () { 
    # facilitate testing from the command line
    my $nm = "put_install_id_list";
    # I cannot seem to setenv these 
    # though they come from resource file fine when run in pipeline
    # REMOVE THIS BEFORE CHECK-IN
    $ENV{"INSTALL_ID.01"} = "HST" ;
    $ENV{"INSTALL_ID.02"} = "HSI" ;
    $ENV{"INSTALL_ID.03"} = "FUS" ;
    # These rest is just for debugging purposes
    my $myVar01 = $ENV{"INSTALL_ID.01"};
    my $myVar02 = $ENV{"INSTALL_ID.02"};
    my $myVar03 = $ENV{"INSTALL_ID.03"};
    PrintMsg( 'D', "set these: ", $nm );
    PrintMsg( 'D', "myVar01: $myVar01 ", $nm );
    PrintMsg( 'D', "myVar02: $myVar02 ", $nm );
    PrintMsg( 'D', "myVar03: $myVar03 ", $nm );
    return ;
} # end put_install_id_list     # For command-line testing only


#----------------------------------------------------------------------------
# Name: get_install_id_list 
#
# Input:   None
#
# Output:  ENV vars like INSTALL_ID.nn from resource file
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
#
#----------------------------------------------------------------------------
sub get_install_id_list  { 
    # look for ENV variables with the format INSTALL_IN.nn to get a list of
    # data_id values used for installtion OSFs. The nn is 01,02, etc.
    my $ref = shift;
    my $nm = "get_install_id_list";
 
    my @install_id = ();  # empty list
    my $kw_counter = 1;
    while ( $kw_counter ) {
        my $number = sprintf ("%02d", $kw_counter);
        my $env_var = "INSTALL_ID.".$number;
        #my $env_val = $ENV{$env_var};
        my $env_val = $ref->{$env_var};
        if (defined($env_val)) {
           PrintMsg( 'D', "Push $env_val onto install_id array ", $nm );
           push @install_id, $env_val;
           $kw_counter++;
        } else {
           $kw_counter = 0;
        }
    }
    if ( (scalar @install_id) == 0) {
        my $errMsg = $nm . "-Missing ENV variables INSTALL_ID.nn in resource file." ;
        die( $errMsg );
    }
    PrintMsg( 'D', "Returning install_id array @install_id. ", $nm );
    return @install_id;
} # end get_install_id_list

#----------------------------------------------------------------------------
# Name: get_mission_by_data_id 
# Input: 
#       db_arch
#       missions
#       group_data_id
#        
# Output: 
#       mission
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
#
#----------------------------------------------------------------------------
sub get_mission_by_data_id () { 
    my $nm = 'get_mission_by_data_id' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_arch         = $_[0] ;
    my $missions        = $_[1] ;
    my $group_data_id   = $_[2] ;

    # query mission name from ingest_up_control
    my @mission_list = split (/,/, $missions);
    $quoted_missions = "'".join("','",@mission_list)."'";
    my $query = <<"EOQ";
SELECT max(iuc_mission) 
FROM ingest_up_control
WHERE iuc_data_id = '$group_data_id' and iuc_mission IN ($quoted_missions)
EOQ
    if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }
    my @record = DoDBIselect( $db_arch, $query);

    if (!defined($record[0])) {
        my $errMsg = $nm . "-Cannot get mission from ingest_up_control table" ;
        die( $errMsg );
    }
    if ( $dbVerbose ) {
        my $msg = "Returning mission: "  ;
        $msg = $msg . "(" . join(", ", @record) . ")";
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return @record;
} # end get_mission_by_data_id

#----------------------------------------------------------------------------
# Name: get_gen_date_class 
#
# Input: 
#       db_arch
#       group_name 
#       group_data_id 
#
# Output: 
#       generation_date and archive_class 
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
# 11/01/04 67129  Sherbert    ids_receipt_dates not nec. unique on linux
# 
#----------------------------------------------------------------------------
sub get_gen_date_class () { 
    # query generation date and archive class from the database where
    # $db_arch is the catalog database object       
    # The data format 109 is "mon.dd.yyyy hh:mi:ss.mmmAM (or PM)" 
    my $nm = 'get_gen_date_class' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_arch       = $_[0] ;
    my $group_name    = $_[1] ;
    my $group_data_id = $_[2] ;

    my $query = <<"EOQ";
SELECT  
    CONVERT(varchar,max(ids_generation_date),109) generation_date,
    min(ids_archive_class)
FROM ingest_data_set_info
WHERE ids_group_name = '$group_name' and ids_group_data_id = '$group_data_id'
   and ids_receipt_date = 
      (SELECT max(ids_receipt_date) 
       FROM ingest_data_set_info 
       WHERE ids_group_name = '$group_name' and 
             ids_group_data_id = '$group_data_id')
EOQ
    if ( $dbVerbose ) { PrintMsg( 'I', "query:\n$query", $nm ); }
    my @record = DoDBIselect( $db_arch, $query);

    if (!defined($record[0])) {
        my $errMsg = $nm . "-Cannot get ingest_data_set_info data" ;
        die( $errMsg );
    }
    if ( $dbVerbose ) {
        my $msg = "Returning generation_date and archive_class: "  ;
        $msg = $msg . "(" . join(", ", @record) . ")";
        PrintMsg( 'I', $msg, $nm );
    }
    PrintMsg( 'I', "Done ... ", $nm );
    return @record;
} # end get_gen_date_class

#----------------------------------------------------------------------------
# Name: get_remote_parameters 
#
# Input:
#       db_arch
#       mission
#       group_data_id
#
# Output:
#       remote_path_file 
#       remote_stage 
#       remote_success 
#       remote_failure
#       remote_alarm 
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
#
#----------------------------------------------------------------------------
sub get_remote_parameters () {  
    # get remote paramters from database and translate the remote_pipeline 
    # value to the remote_path name by using the ENV variable of the same name
    my $nm = 'get_remote_parameters' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_arch         = $_[0] ;
    my $mission         = $_[1] ;
    my $group_data_id   = $_[2] ;
         
    my $query = <<"EOQ";
SELECT  
    iuc_remote_pipeline, iuc_remote_stage, iuc_remote_success, 
    iuc_remote_failure, iuc_remote_alarm
FROM ingest_up_control
WHERE iuc_mission = '$mission' and iuc_data_id='$group_data_id'
EOQ

    if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }
    my @record = DoDBIselect( $db_arch, $query);
    my $remote_path_file = "NONE";  # default value
    
    if (!defined($record[0])) { # check pipeline name
       PrintMsg("W",
          "No remote pipeline parameters for data_id $group_data_id", $nm);
       PrintMsg("I", "Assuming remote_pipeline parameter is NONE", $nm);
    } else {
       if ($record[0] ne "NONE") {
          $remote_path_file = $ENV{$record[0]};
          if (!defined( $remote_path_file)) {
             my $errMsg = $nm . "-Missing ENV for $record[0] having path file name." ;
             die( $errMsg );
          }
       }
    }
    $record[0] = $remote_path_file;  # replace pipeline name with path name
    if ( $dbVerbose ) {
        my $msg = "Returning remote_path_file, remote_stage, " ;
        $msg = $msg . "remote_success, remote_failure, remote_alarm: "  ;
        $msg = $msg . "(" . join(", ", @record) . ")";
        PrintMsg( 'D', $msg, $nm );
    }
    # I think I would prefer to return a hash
    my %remote_parameters = (
        remote_path_file => $record[0]
      , remote_stage     => $record[1]
      , remote_success   => $record[2]
      , remote_failure   => $record[3]
      , remote_alarm     => $record[4]
    );
    PrintMsg( 'D', "Done ... ", $nm );
    return \%remote_parameters; ## return a hash reference
} # end get_remote_parameters

#----------------------------------------------------------------------------
# Name: get_group_identifiers 
#
# Input:
#       db_arch
#       osf_root
#
# Output:
#       ids_group_name
#       ids_group_data_id
#       ids_generation_date
#       ids_mission
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
#
#----------------------------------------------------------------------------
sub get_group_identifiers () { 
    # get group request data from ingest_data_set_info
    # The data format 109 is "mon.dd.yyyy hh:mi:ss.mmmAM (or PM)" 
    my $nm = 'get_group_identifiers' ;
    PrintMsg( 'D', "Starting... ", $nm );
    
    my $db_arch         = $_[0] ;
    my $osf_root        = $_[1] ;

    my $query = <<"EOQ";
SELECT ids_group_name, ids_group_data_id, 
       CONVERT(varchar,ids_generation_date,109) generation_date, ids_mission
FROM ingest_data_set_info
WHERE ids_ins_request_id = '$osf_root'
EOQ
    if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }
    my @record = DoDBIselect( $db_arch, $query);

    if (!defined($record[0])) {
        my $errMsg = $nm . "-Missing record in ingest_data_set_info for $osf_root" ;
        die( $errMsg );
    }
    if ( $dbVerbose ) {
        my $msg = "Returning gp_nm, gp_data_id, gen_dt, mission: "  ;
        $msg = $msg . "(" . join(", ", @record) . ")";
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return @record;
} # end get_group_identifiers

#----------------------------------------------------------------------------
# Name:  update_install_flag 
#
# Input:
#       db_arch
#       proc_mode
#       osf_dataset
#
# Output:
#       None -- updates a flag
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
#
#----------------------------------------------------------------------------
sub update_install_flag () {  
    # set database install flag according to process mode
    my $nm = 'update_install_flag' ;
    PrintMsg( 'D', "Starting... ", $nm );
    
    my $db_arch     = $_[0] ;
    my $proc_mode   = $_[1] ;
    my $osf_dataset = $_[2] ;

    my $install_flag;
    my $query;
       
    if ( $proc_mode eq "UPDATE" || $proc_mode eq "BAD_DATASET") {
       if ($proc_mode eq "UPDATE") {
          $install_flag = "Y";
          $query = <<"EOQ";
UPDATE ingest_data_set_info
SET ids_install_flag = 'Y'
WHERE ids_ins_request_id = '$osf_dataset' and
      ids_nsa_rsp_date > ids_generation_date 
EOQ
       } else {
          $install_flag = 'E';
          $query = <<"EOQ";
UPDATE ingest_data_set_info
SET ids_install_flag = 'E'
WHERE ids_ins_request_id = '$osf_dataset'  
EOQ
       }
       if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }

       my $rec_count = DoDBI( $db_arch, $query);
       if ($rec_count == 0) {
           if ($proc_mode eq "UPDATE") {
              PrintMsg("W","NSA install may have failed", $nm);
           }
           $errMsg = "ERROR-$nm-" ;
           my $errMsg = $nm . "-Failed to update ids_install_flag to $install_flag" ;
           die( $errMsg );
       }
       ## I think it should only blab this message IF query worked
       PrintMsg("I","Updating ids_install_flag to $install_flag.", $nm);
    }
    if ( $dbVerbose ) {
        my $msg = "Not returning values. "  ;
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return ;
} # end update_install_flag

#----------------------------------------------------------------------------
# Name: copy_log_file   
#
# Input:
#       group_info (hash ref)
#           needs group_name     
#           needs group_data_id  
#           needs trl
#       problem_log_dir
#       ds_log_spec     
#
# Output:
#       None - copies a file
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
#
#----------------------------------------------------------------------------
sub copy_log_file () { 
    # close dataset log and copy or append it to the problem log
    my $nm = 'copy_log_file' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $hash_ref_group  = $_[0] ;
    my $problem_log_dir = $_[1] ;
    my $ds_log_spec     = $_[2] ;

    my %group_info      = %{$hash_ref_group} ;
    my $group_name      = $group_info{group_name} ;
    my $group_data_id   = $group_info{group_data_id} ;
    my $trl             = $group_info{trl} ;

    # close log file that is about to be copied
    ## Should these be moved Back to the perl file?
    ## Does this file have to be closed before copying it?
    ## Is problem_log_spec same as something in pl?
    ## Should entire subroutine be moved back?
    close TRL;          ## I have no idea what to do about this global
    undef $trl;         ## I do not know what this means

    PrintMsg("I","Copying dataset log to problem log directory.", $nm);
    # construct bad dataset log name 
    my $problem_log_spec = 
           $problem_log_dir."/".$group_name."_".$group_data_id.".log";
    my $command;
    my $sys_stat;

    if (-e $problem_log_spec) {
        # append to existing file after closing dataset log
        PrintMsg("I", "Appending $ds_log_spec to $problem_log_spec", $nm);
        $command = "cat $ds_log_spec >> $problem_log_spec";
        $sys_stat = system("$command");
    } else {
        # copy to new file after closing dataset log
        PrintMsg("I", "Copying $ds_log_spec to $problem_log_spec", $nm);
        $command = "cp $ds_log_spec $problem_log_spec";
        $sys_stat = system("$command");
    }
    if ($sys_stat) {  # check status after stripping out wait status
        # this message goes only go to process log file
        my $errMsg = $nm . "-Cannot execute command: $command" ;
        die( $errMsg );
    }
    PrintMsg( 'D', "Done. Figured there were enough INFO messages...", $nm );
    return ;
} # end copy_log_file

#----------------------------------------------------------------------------
# Name: group_completion_status 
#       check if group request is complete 
#
# Input:
#       db_arch       
#       group_info (hash ref)
#          needs group_name    
#          needs group_data_id 
#          needs gen_date     
#          needs mission      
#
# Output:
#       completed_group 
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
#
#----------------------------------------------------------------------------
sub group_completion_status () { 
    # complete - there are no install flag values left as N
    my $nm = 'group_completion_status' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_arch        = $_[0] ;
    my $hash_ref_group = $_[1] ;

    my %group_info    = %{$hash_ref_group} ;
    my $group_name    = $group_info{group_name} ;
    my $group_data_id = $group_info{group_data_id} ;
    my $gen_date      = $group_info{gen_date} ;
    my $mission       = $group_info{mission} ;

    my $completed_group = $false;
    my $query = <<"EOQ";
SELECT count(*) n_count
FROM ingest_data_set_info
WHERE ids_group_name = '$group_name' and ids_group_data_id = '$group_data_id'
   and ids_generation_date = '$gen_date' and ids_mission = '$mission' and
   ids_install_flag = 'N'
EOQ
    if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }
    my (@n_count) = DoDBIselect( $db_arch, $query);

    if (!defined($n_count[0])) {
        my $errMsg = $nm . 
           "-Query failure for install count in ingest_data_set_info ". 
           "for $group_name and $group_data_id" ;
        die( $errMsg );
    }
    if ($n_count[0] == 0) {
        $completed_group = $true;
    }
    if ( $dbVerbose ) {
        my $msg = "Returning completed_group: $completed_group "  ;
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return $completed_group;
} # end group_completion_status

#----------------------------------------------------------------------------
# Name: is_any_dataset_bad 
#
# Input:
#       db_arch      
#       group_info (hash ref)
#          needs group_name    
#          needs group_data_id 
#          needs gen_date      
#          needs mission       
#       group_osf       
#       process_mode    
#       completed_group 
#
# Output:
#       bad_data
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
#
#----------------------------------------------------------------------------
sub is_any_dataset_bad () {  
    # query for bad dataset 
    my $nm = 'is_any_dataset_bad' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_arch         = $_[0] ;
    my $hash_ref_group  = $_[1] ;
    my $group_osf       = $_[2] ;
    my $process_mode    = $_[3] ;
    my $completed_group = $_[4] ;

    my %group_info    = %{$hash_ref_group} ;
    my $group_name    = $group_info{group_name} ;
    my $group_data_id = $group_info{group_data_id} ;
    my $gen_date      = $group_info{gen_date} ;
    my $mission       = $group_info{mission} ;

#######
 # To reference a hash, pass \%group_info
 # To deref the hash, do: my $hash_ref_group = @_; my %group_info = %{$hash_ref_group}
 # To access the parts, after deref: $group_info{group_name} (no quotes)
#require 'print_utils.pl' ; ## Del me
#printHash( %group_info );  ## Del me
#######
       
    my $bad_data = ($group_osf && $process_mode eq "BAD_DATASET"); # initialize
    
    if ($group_osf && $process_mode eq "UPDATE" && $completed_group) {

       my $query = <<"EOQ";
SELECT MIN(ids_install_flag) min_flag
FROM ingest_data_set_info
WHERE ids_group_name = '$group_name' and ids_group_data_id = '$group_data_id'
   and ids_generation_date = '$gen_date' and ids_mission = '$mission'
EOQ
       if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }
       my (@min_flag) = DoDBIselect( $db_arch, $query);

       if (!defined($min_flag[0])) {
           my $errMsg = $nm . "-Query failure for min(ids_install_flag) in " . 
              "ingest_data_set_info for $group_name and $group_data_id" ;
           die( $errMsg );
       }
       $bad_data = $min_flag[0] eq 'E'; 
       if ($bad_data) {
           PrintMsg("I",
            "At least one bad dataset has been detected in this request.", $nm);
       }
    }
    if ( $bad_data eq "" ) { $bad_data = $false ; }
    if ( $dbVerbose ) {
        my $msg = "Returning bad_data: $bad_data."  ;
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return $bad_data;
} # end is_any_dataset_bad

#----------------------------------------------------------------------------
# Name: check_archive_table 
#       check for redundant update
#
# Input:
#       db_arch      
#       group_info (hash ref)
#           needs group_name    
#           needs group_data_id 
#           needs gen_date      
#           needs mission       
#
# Output:
#       needs_insertion
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
# 01/13/10 61672  Sherbert    Do not let needs_insertion remain undefined
#
#----------------------------------------------------------------------------
sub check_archive_table () {  
    # see if archive_data_set_all records already exists.
    my $nm = 'check_archive_table' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_arch         = $_[0] ;
    my $hash_ref_group  = $_[1] ;

    my %group_info    = %{$hash_ref_group} ;
    my $group_name    = $group_info{group_name} ;
    my $group_data_id = $group_info{group_data_id} ;
    my $gen_date      = $group_info{gen_date} ;
    my $mission       = $group_info{mission} ;

    my $query = <<"EOQ";
SELECT count(*) ads_count 
FROM ingest_data_set_info, archive_data_set_all
WHERE ids_mission = '$mission' and ids_group_name = '$group_name' and
   ids_group_data_id = '$group_data_id' and ids_generation_date = '$gen_date' 
   and ads_data_set_name = ids_data_set_name and 
   ads_archive_class = ids_archive_class and 
   ads_generation_date = ids_generation_date and ads_mission = ids_mission  
EOQ
    
    if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }
    PrintMsg("I","Querying archive_data_set_all for old records.", $nm);
    my (@ads_count) = DoDBIselect( $db_arch, $query);
    
    if (!defined($ads_count[0])) {
        my $errMsg = $nm . "-Query failure for count of old archive records" ;
        die( $errMsg );
    }
    PrintMsg('D', "Query found $ads_count[0] records. ", $nm);
    my $needs_insertion ;
    ## LES best guess: 0 records means there are no old ads records 
    ## therefore not redundant, therefore needs insertion is true?
    if ($ads_count[0] == 0) { 
        $needs_insertion = $true ; 
    } else {
        $needs_insertion = $false ;
    }
    if ( ! $needs_insertion ) {
        PrintMsg('D', "needs_insertion=false ($needs_insertion) ", $nm);
        PrintMsg("I","Archive table records already present.", $nm);
    } else {
        PrintMsg('D', "needs_insertion=true ($needs_insertion) ", $nm);
        PrintMsg("I","Archive table records need to be inserted.", $nm);
    }
    if ( $dbVerbose ) {
        my $msg = "Returning needs_insertion: $needs_insertion. "  ;
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return $needs_insertion;
} # end check_archive_table

#----------------------------------------------------------------------------
# Name: get_testbed_version 
#       obtain OPUS build number 
#
# Input:
#       sogs_disk 
#
# Output:
#       version
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
#
#----------------------------------------------------------------------------
sub get_testbed_version () { 
    # check every directory in $sogs_disk to find testbed.ver file then
    # extract the OPUS version number from that file.
    my $nm = 'get_testbed_version' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $sogs_disk = $_[0] ;
#   my $msg = "sogs_disk is $sogs_disk " ;
#   PrintMsg( 'D', $msg, $nm ) ;

    # split $sogs_disk into array of words
    my @dir_list = split /\s+/, $sogs_disk ;
    my $directory;

    foreach $directory (@dir_list) {
#      PrintMsg( 'D', "directory is $directory", $nm ) ;
       my $file_spec = $directory."/dat/testbed.ver";
#      PrintMsg( 'D', "file_spec is $file_spec", $nm ) ;
       
       if (-e $file_spec) {
           open TESTBED, "<".$file_spec;
           last;
       }
    }
    if (!defined( TESTBED)) {
        my $errMsg = $nm . "-Cannot open $file_spec to get OPUS build version." ;
        die( $errMsg );
    }
    my @testbed_lines = <TESTBED>;  # read entire file
#   $msg = "Reading testbed_lines: "  ;
#   $msg = $msg . "(" . join(", ", @testbed_lines) . ")";
#   PrintMsg( 'D', $msg, $nm ) ;
    close TESTBED;

    my $line;
    my $version;
    
    foreach $line (@testbed_lines) {
#       PrintMsg( 'D', $line, $nm ) ;
        if ($line =~ /OPUS/) {
           $version = $line;
           # remove leading and trailing white space
           $version =~s/(\s+)(OPUS\s\S+)(\s*\n)/$2/;
           last;
        }
    }
    if (!defined( $version)) {
        my $errMsg = $nm . "-Cannot parse contents of $file_spec to get OPUS version." ;
        die( $errMsg );
    }
    PrintMsg( 'D', "Done.  Returning version $version.", $nm );
    return $version;
} # end get_testbed_version

#----------------------------------------------------------------------------
# Name: insert_archive_tables 
#
# Input:
#       db_arch 
#       group_info (hash ref)
#           needs group_name 
#           needs group_data_id 
#           needs gen_date 
#           needs mission 
#           needs archive_class 
#       proprietary 
#       override_data_source 
#       data_source 
#       version 
#
# Output:
#       None - updates archive_data_set_all and archive_files
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
#
#----------------------------------------------------------------------------
sub insert_archive_tables () {  
    # insert all records of request into archive_data_set_all and archive_files
    my $nm = 'insert_archive_tables' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_arch              = $_[0] ;
    my $hash_ref_group       = $_[1] ;
    my $proprietary          = $_[2] ;
    my $override_data_source = $_[3] ;
    my $data_source          = $_[4] ;
    my $version              = $_[5] ;
    my $release_delay_months = $_[6] ;
    my $proprietary_start_tm = $_[7] ;

    my %group_info    = %{$hash_ref_group} ;
    my $group_name    = $group_info{group_name} ;
    my $group_data_id = $group_info{group_data_id} ;
    my $gen_date      = $group_info{gen_date} ;
    my $mission       = $group_info{mission} ;
    my $archive_class = $group_info{archive_class} ;

    # Create query that performs all archive table updates in a single
    # transaction. The updated tables are archive_data_set_all and
    # archive_files. The source for the updates is ingest_data_set_info
    # and ingest_files.

    # Set the ads_data_source to the ids_data_source unless the override is
    # requested, using variables $override_data_source and $data_source.
    
    my $data_source_value = "ids_data_source";
    if ($override_data_source) {
       $data_source_value = "'".$data_source."'";
    }
#   PrintMsg( 'D', "data_source_value    is $data_source_value ", $nm );

    # check of archive_class now in pl script.  For non-proprietary classes 
    # we want ads_proprietary_start_time=ads_generation_date=ads_release_date
    # which should occur with a passed-in value of 0 for release_delay_months.
    my $release_date = "dateadd( month, $release_delay_months, $proprietary_start_tm )";
    PrintMsg( 'D', "release_date is $release_date ", $nm  );

    PrintMsg("I","Inserting archive database records.", $nm);
    my $query = <<"EOQ";
BEGIN TRANSACTION update_archive
INSERT INTO archive_data_set_all 
  (ads_data_set_name, ads_archive_class, ads_generation_date, ads_mission,
   ads_best_version, ads_data_receipt_time, ads_data_set_size, ads_data_source,
   ads_file_count, ads_build_num, ads_release_date, ads_proprietary_start_time )
SELECT 
   ids_data_set_name, ids_archive_class, ids_generation_date, ids_mission,
   'Y', getdate(), ids_data_set_size, $data_source_value, 
   ids_file_count,'$version', $release_date, $proprietary_start_tm 
FROM ingest_data_set_info
WHERE ids_mission = '$mission' and ids_group_name = '$group_name' and
   ids_group_data_id = '$group_data_id' and ids_generation_date = '$gen_date' 
IF \@\@rowcount = 0
  BEGIN
    SELECT 'Rolling back after no insertions for archive_data_set_all'
    ROLLBACK TRANSACTION update_archive
    RETURN
  END  
INSERT INTO archive_files 
  (afi_data_set_name, afi_archive_class, afi_generation_date, afi_mission,
   afi_file_extension, afi_file_name, afi_file_type, afi_virtual,
   afi_pre_compress_size, afi_post_compress_size, afi_checksum)
SELECT 
   ifi_data_set_name, ifi_archive_class, ifi_generation_date, ifi_mission,
   ifi_file_extension, ifi_file_name, ifi_file_type, 'N',
   ifi_pre_compress_size, ifi_post_compress_size, ifi_checksum
FROM ingest_data_set_info, ingest_files
WHERE ids_mission = '$mission' and ids_group_name = '$group_name' and
   ids_group_data_id = '$group_data_id' and ids_generation_date = '$gen_date' 
   and ifi_data_set_name = ids_data_set_name and 
   ifi_archive_class = ids_archive_class and 
   ifi_generation_date = ids_generation_date and ifi_mission = ids_mission  
IF \@\@rowcount = 0
  BEGIN
    SELECT 'Rolling back after no insertions for archive_files'
    ROLLBACK TRANSACTION update_archive
    RETURN
  END  
COMMIT TRANSACTION update_archive
EOQ

    if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }
    # USED to call RunSqlTransaction( ), see if this does the trick
    my $rec_count = DoDBI( $db_arch, $query);
    PrintMsg("I", "$rec_count total records inserted.", $nm);

    if ($rec_count == 0) {
        my $errMsg = $nm . "-Failed to update archive tables" ;
        die( $errMsg );
    }
    if ( $dbVerbose ) {
        my $msg = "Not returning values. "  ;
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return ;
} # end insert_archive_tables

#----------------------------------------------------------------------------
# Name: best_version  
#       Apply precedence rules... 
#       Correct ads_best_version values if required. There are two rules for
#       best version. For HST CAL data that have group names ending in a
#       character of the set found in the variable $ds_type_versions,
#       the best version is that having the leftmost value is this set. 
#       For other cases, the most recent generation date is the best version. 
#       Every dataset of the group is treated the same. 
#       QS-Splits are allowed to break the rule. 
#
# Input:
#       db_arch          -- 
#       group_info (hash ref)
#           needs group_name 
#           needs group_data_id 
#           needs gen_date 
#           needs mission 
#       precedence_vals -- a constant
#
# Output:
#       is_qs_split -- date resetters need to know 
#       but this routine also updates ads_best_version
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
# 09/10/30 63837  Sherbert    QS-Split Exception to the rule
# 10/04/26 64912  Sherbert    pass Q/S-split-tedness back to caller 
#
#----------------------------------------------------------------------------
sub best_version () {   
    my $nm = 'best_version' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_arch         = $_[0] ;
    my $hash_ref_group  = $_[1] ;
    my $precedence_vals = $_[2] ;

    my %group_info    = %{$hash_ref_group} ;
    my $group_name    = $group_info{group_name} ;
    my $group_data_id = $group_info{group_data_id} ;
    my $gen_date      = $group_info{gen_date} ;
    my $mission       = $group_info{mission} ;
    my $arch_class    = $group_info{archive_class} ;
    
    my $upcased_grp = uc($group_name);
    my $is_hst_cal = $false;
    my $is_qs_split = 0 ; # Innocent until proven guilty 

    my $is_cal_or_asn = $false ;
    if ($arch_class eq "CAL" or $arch_class eq "ASN") {
        # This is the only combination I can think of where 
        # one member of group may have a different archive_class
        $is_cal_or_asn = $true ;
    }
    # I would like to understand the purpose of this.
    # There is no point in doing it for anything other than CAL or ASN class data...
    # CAL or ASN are only in HST mission to best of my knowledge, esp since 
    # only other known mission is FUSE which we are no longer ingesting. 
    if ($mission eq "HST" && $is_cal_or_asn) {    
       # check if HST CAL class is in dataset that matches group name
       # Query will only return count > 0 for ASN_ID or Singleton 
       # and count will never be > 1.  So why not just call cal_count is_hst_cal?
       # Is there some point or type of obs that I am missing ? 
       my $query = <<"EOQ";
SELECT count(*) cal_count
FROM ingest_data_set_info
WHERE ids_mission = '$mission' and 
      ids_group_name = '$group_name' and
      ids_group_data_id = '$group_data_id' and 
      ids_generation_date = '$gen_date' and
      ids_archive_class = 'CAL'
EOQ
       if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }
       PrintMsg("I","Querying ingest_data_set_info for CAL class count.", $nm);
       my @cal_cnt = DoDBIselect( $db_arch, $query);
       if ($cal_cnt[0]) {
          $is_hst_cal = $true;
       }
    }
    # For HST CAL data, want to call 
    #             check_dataset_type() to check for old datasets w/diff 9th char 
    #          or check_cal_dates()    for other cal data
    # For other kinds of data, call
    #             check_gen_dates()  
    if ($is_hst_cal) {
       PrintMsg( 'D', "is_hst_cal=true ($is_hst_cal)", $nm );
       my $ninth_char = substr($upcased_grp, 8, 1) ;
       PrintMsg( 'D', "Is $ninth_char in $precedence_vals? ", $nm );
       my $type_idx = index( $precedence_vals, $ninth_char );
       PrintMsg( 'D', "...type_idx is $type_idx", $nm );
       if ( $type_idx > -1) {
          PrintMsg( 'D', "Yes, $ninth_char IS in $precedence_vals? ", $nm );
          $is_qs_split = check_dataset_type( $db_arch, \%group_info, 
                              $upcased_grp, $type_idx, $precedence_vals );
       } else {
          PrintMsg( 'D', "No, $ninth_char is not in $precedence_vals. ", $nm );
          check_cal_dates( $db_arch, \%group_info, $precedence_vals );
       }
    } else {
       PrintMsg( 'D', "is_hst_cal=false ($is_hst_cal)", $nm );
       check_gen_dates( $db_arch, \%group_info );
    }
    if ( $dbVerbose ) {
        my $msg = "Returning is_qs_split: " . $is_qs_split ;
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return $is_qs_split ;
} # end best_version

#----------------------------------------------------------------------------
# Name: check_dataset_type
#     This is used only for HST CAL class. These have nine character rootnames.
#     Use a wild card in the ninth character of the rootname to find all
#     datasets that differ only by the last character of the dataset name
#     and do not share the current generation date.
#
# Input:
#       db_arch          
#       group_info (hash ref)
#           needs gen_date
#       dataset          
#       type_idx         
#       precedence_vals 
#
# Output:
#       is_qs_split - so eventually date resetter will know what to do 
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
# 09/10/30 63837  Sherbert    QS-Split Exception to the rule
# 10/04/26 64912  Sherbert    or should I just call check_merge_reqd again?
#
#----------------------------------------------------------------------------
sub check_dataset_type () {  
    my $nm = 'check_dataset_type' ;
    PrintMsg( 'D', "Starting... ", $nm );
    
    my $db_arch         = $_[0] ;
    my $hash_ref_group  = $_[1] ;
    my $dataset         = $_[2] ;
    my $type_idx        = $_[3] ;
    my $precedence_vals = $_[4] ;

    my %group_info    = %{$hash_ref_group} ;    ## need to pass to other subs
    my $gen_date      = $group_info{gen_date} ;
    my $wild_name = substr($dataset,0,8)."%";

    PrintMsg( 'D', "How do group_name and dataset and wild_name differ?", $nm );
    PrintMsg( 'D', "I fear I am messing up a call to something else...?", $nm );
    PrintMsg( 'D', "group_name is $group_info{group_name} ", $nm );
    PrintMsg( 'D', "dataset    is $dataset ", $nm );
    PrintMsg( 'D', "wild_name  is $wild_name ", $nm );
    PrintMsg( 'D', "type_idx   is $type_idx ", $nm );

    my $query = <<"EOQ";
SELECT ads_data_set_name
  FROM archive_data_set_all
 WHERE ads_data_set_name like '$wild_name' 
   AND ads_archive_class = 'CAL' 
   AND ads_mission = 'HST' 
   AND ads_generation_date != '$gen_date'
GROUP BY ads_data_set_name
EOQ

    if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }
    PrintMsg("I","Querying archive_data_set_all for best type.", $nm);
    my $err_msg1 = "Cannot access 1st ADS record";
    my $err_msg2 = "Cannot access following ADS record";
    my $ds_count = 0;
    my @ds_list;
    my @ds_record;
    my $sth = DoDBIexecute( $db_arch, $query);
    while ( (@ds_record) = DoDBIfetch( $db_arch, $query, $sth) ) {
        $ds_count += 1;
        push @ds_list, $ds_record[0];
    }
    if ($ds_count == 0) {
        PrintMsg( 'D', "no additional versions - already marked best", $nm ) ;
        return;  # no additional version - already marked best
    }
    # go though all datasets to determine if there is a better version type
    # and test if old data matches the original dataset
    my $msg = "go though all datasets to determine if there is a better version " ;
    $msg = $msg . "type and test if old data matches the original dataset " ;
    PrintMsg( 'D', $msg, $nm ) ;
    my $ds_dataset;
    my $old_type_idx;
    my $better_ver = $false;  # is old data better?
    my $match_ds = $false;

    for $ds_dataset (@ds_list) {
        my $lastChar = substr($ds_dataset, 8, 1) ;
        if ($ds_dataset eq $dataset) {
            $match_ds = $true;
            my $msg = "old $ds_dataset matches $dataset therefore match_ds=true ($match_ds) " ;
            PrintMsg( 'D', $msg, $nm ) ;
        } else {
            my $msg = "old $ds_dataset DOES NOT match $dataset therefore match_ds=false ($match_ds) " ;
            PrintMsg( 'D', $msg, $nm ) ;
            $old_type_idx = index( $precedence_vals, $lastChar );
            my $msg = "lastChar ($lastChar) gets old_type_idx $old_type_idx in " ;
            $msg = $msg . "precedence_vals ($precedence_vals) " ;
            PrintMsg( 'D', $msg, $nm ) ;
           #$msg = "Compare old_type_idx ($old_type_idx) to type_idx ($type_idx) " ;
           #PrintMsg( 'D', $msg, $nm ) ;
            if ($old_type_idx > -1 && $old_type_idx < $type_idx) {
                $better_ver = $true;
                my $msg = "$ds_dataset is a better version than $dataset " ;
                PrintMsg( 'D', $msg, $nm ) ;
               #$msg = "because EITHER old_type_idx > -1 ($old_type_idx) " ;
               #$msg = $msg . "OR old_type_idx < type_idx ( $old_type_idx < $type_idx ) ";
               #PrintMsg( 'D', $msg, $nm ) ;
            }
        }
    }
    # For 63837:
    # A change to how Q/S-splits are handled means Q could be better than S, 
    PrintMsg( 'D', "check for Q/S-split complication ", $nm ) ;
    my $is_qs_split = check_merge_reqd( $db_arch, $dataset, $precedence_vals ) ;
    if ( $better_ver && ! $is_qs_split ) { # reset this group IF this is NOT a Q/S-split.  
        # this group is not best version
       #PrintMsg( 'D', "better_ver=true && is_qs_split=false ", $nm ) ;
        PrintMsg( 'D', "This group ($group_info{group_name}) is NOT best version ", $nm ) ;
        reset_this_group( $db_arch, \%group_info ) ;
    } elsif ($match_ds) {  # let next sub decide what to reset 
       #PrintMsg( 'D', "match_ds=true ", $nm ) ;
        # more that one dataset with current name
        PrintMsg( 'D', "More that one dataset with current name ", $nm ) ;
        check_gen_dates( $db_arch, \%group_info );
    } else {  # this is best version, reset others using wildcard name
        my $msg = "This ($group_info{group_name}) IS best version, ";
        $msg = $msg . "reset best_version to N for older others, using wildcard name" ;
        PrintMsg( 'D', $msg, $nm ) ;
        reset_cal_dataset( $db_arch, \%group_info, $wild_name );
    }
    if ( $dbVerbose ) {
        my $msg = "Returning is_qs_split $is_qs_split for passing around..." ;
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return $is_qs_split ;
} # end check_dataset_type    

#----------------------------------------------------------------------------
# Name: check_gen_dates
#       Determines whether to call a reset for "this group" or "other group"
#       ADS entries with more recent gen_dates reset ads_best_version for "this group"
#       otherwise (more likely) reset ads_best_version for "other group"
#
# Input:
#       db_arch       
#       group_info (hash ref)
#           needs group_name    
#           needs group_data_id 
#           needs gen_date      
#           needs mission       
#
# Output:
#       None
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
#
#----------------------------------------------------------------------------
sub check_gen_dates () {  
    # see if there exists a dataset with a more recent generation date 
    my $nm = 'check_gen_dates' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_arch        = $_[0] ;
    my $hash_ref_group = $_[1] ;

    my %group_info    = %{$hash_ref_group} ;
    my $group_name    = $group_info{group_name} ;
    my $group_data_id = $group_info{group_data_id} ;
    my $gen_date      = $group_info{gen_date} ;
    my $mission       = $group_info{mission} ;

    my $query = <<"EOQ";
SELECT count(*) more_recent_cnt  
FROM ingest_data_set_info, archive_data_set_all
WHERE ids_mission = '$mission' and 
      ids_group_name = '$group_name' and
      ids_group_data_id = '$group_data_id' and 
      ids_generation_date = '$gen_date' and
      ads_data_set_name = ids_data_set_name and 
      ads_archive_class = ids_archive_class and 
      ads_mission = ids_mission and 
      ads_generation_date > ids_generation_date 
EOQ
    if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }
    PrintMsg("I","Querying archive_data_set_all for generation dates ", $nm);
    my @recent_cnt = DoDBIselect( $db_arch, $query);
    
    if ($recent_cnt[0]) {
        reset_this_group( $db_arch, \%group_info );
    } else {
        reset_other_groups( $db_arch, \%group_info );
    }
    if ( $dbVerbose ) {
        my $msg = "Not returning values. "  ;
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return ;
} # check_gen_dates    

#----------------------------------------------------------------------------
# Name: check_cal_dates
#       See if there exists a CAL class dataset with a more recent generation date.
#       Determines whether to call a reset for "this group" or "other group".
#
# Input:
#       db_arch       
#       group_info (hash ref)
#           needs group_name    
#           needs group_data_id 
#           needs gen_date      
#           needs mission       
#       precedence_vals - for calling reset-cal-members
#
# Output:
#       None
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters (&missing lines?)
#
#----------------------------------------------------------------------------
sub check_cal_dates () {  
    my $nm = 'check_cal_dates' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_arch         = $_[0] ;
    my $hash_ref_group  = $_[1] ;
    my $precedence_vals = $_[2] ;

    my %group_info    = %{$hash_ref_group} ;
    my $group_name    = $group_info{group_name} ;
    my $group_data_id = $group_info{group_data_id} ;
    my $gen_date      = $group_info{gen_date} ;
    my $mission       = $group_info{mission} ;

    my $query = <<"EOQ";
SELECT count(*) more_recent_cnt  
FROM ingest_data_set_info, archive_data_set_all
WHERE ids_mission = '$mission' and 
      ids_group_name = '$group_name' and
      ids_group_data_id = '$group_data_id' and 
      ids_generation_date = '$gen_date' and
      ids_archive_class = 'CAL' and 
      ads_data_set_name = ids_data_set_name and 
      ads_archive_class = ids_archive_class and 
      ads_mission = ids_mission and 
      ads_generation_date > ids_generation_date 
EOQ
    if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }

    PrintMsg("I","Querying archive_data_set_all for generation dates later than $gen_date", $nm);
    my @recent_cnt = DoDBIselect( $db_arch, $query);
    $msg = "Found $recent_cnt[0] records with a more recent gen_date " ;
    PrintMsg( 'D', $msg, $nm ) ;
    
    if ($recent_cnt[0]) {
        my $msg = "Therefore reset_this_group ($group_name, $gen_date) " ;
        PrintMsg( 'D', $msg, $nm ) ;
        reset_this_group( $db_arch, \%group_info );
    } else {
        my $msg = "Therefore reset_other_groups (NOT $group_name, $gen_date) " ;
        PrintMsg( 'D', $msg, $nm ) ;
        reset_other_groups( $db_arch, \%group_info );
        my @members = get_cal_members( $db_arch, \%group_info, $precedence_vals);
        my $num_members = scalar @members ;
        if ( $num_members ) {
            PrintMsg("D","Perhaps reset ads_best_version to 'N' for $num_members cal members.", $nm);
            reset_cal_members( \@members, \%group_info, $precedence_vals, $db_arch );
        }
    }
    if ( $dbVerbose ) {
        my $msg = "Not returning values. "  ;
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return ;
} # end check_cal_dates    

#----------------------------------------------------------------------------
# Name: reset_this_group 
#       reset ads_best_version to "N" for all datasets in current group
#
# Input:
#       db_arch       
#       group_info (hash ref)
#           needs group_name    
#           needs group_data_id 
#           needs gen_date      
#           needs mission       
#
# Output:
#       None
#
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
#
#----------------------------------------------------------------------------
sub reset_this_group () {  
    my $nm = 'reset_this_group' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_arch        = $_[0] ;
    my $hash_ref_group = $_[1] ;

    my %group_info    = %{$hash_ref_group} ;
    my $group_name    = $group_info{group_name} ;
    my $group_data_id = $group_info{group_data_id} ;
    my $gen_date      = $group_info{gen_date} ;
    my $mission       = $group_info{mission} ;

    my $query = <<"EOQ";
UPDATE archive_data_set_all 
SET ads_best_version = 'N' 
FROM ingest_data_set_info, archive_data_set_all
WHERE ids_mission = '$mission' and 
      ids_group_name = '$group_name' and
      ids_group_data_id = '$group_data_id' and 
      ids_generation_date = '$gen_date' and
      ads_data_set_name = ids_data_set_name and 
      ads_archive_class = ids_archive_class and 
      ads_mission = ids_mission and
      ads_generation_date = ids_generation_date 
EOQ
    if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }
    my $count = DoDBI( $db_arch, $query);
    my $msg = "This group of datasets (gen_date $gen_date) is NOT the best version. " ;
    PrintMsg("I", $msg, $nm);
    $msg = "Reset ads_best_version to N for $count datasets having this gen_date." ;
    PrintMsg("I", $msg, $nm);
    if ( $dbVerbose ) {
        my $msg = "Not returning values. "  ;
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return ;
} # end reset_this_group 

#----------------------------------------------------------------------------
# Name: reset_cal_dataset 
#     reset ads_best_version to "N" for all datasets matching the wild card
#     name but not having the current generation date
#
# Input:
#       db_arch       
#       group_info (hash ref)
#           needs gen_date      
#       wild_name    
#
# Output:
#       None - may update ads_best_version 
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
#
#----------------------------------------------------------------------------
sub reset_cal_dataset ($$$) {  
    my $nm = 'reset_cal_dataset' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_arch        = $_[0] ;
    my $hash_ref_group = $_[1] ;
    my $wild_name      = $_[2] ;

    my %group_info    = %{$hash_ref_group} ;
    my $gen_date      = $group_info{gen_date} ;

    my $query = <<"EOQ";
UPDATE archive_data_set_all 
SET ads_best_version = 'N' 
FROM archive_data_set_all
WHERE ads_data_set_name LIKE '$wild_name' 
  AND ads_archive_class = 'CAL' 
  AND ads_mission = 'HST' 
  AND ads_best_version = 'Y' 
  AND ads_generation_date != '$gen_date'
EOQ
    if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }
    my $count = DoDBI( $db_arch, $query);
    if ($count > 0) {
       my $msg = "This gen_date is the best version. ";
       $msg = $msg. "Reset ads_best_version for other gen_dates of $wild_name." ;
       PrintMsg("I", $msg, $nm ) ;
    }
    PrintMsg("I","Reset ads_best_version to N for $count $wild_name records.", $nm);
    if ( $dbVerbose ) {
        my $msg = "Not returning values. "  ;
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return ;
} # end reset_cal_dataset 

#----------------------------------------------------------------------------
# Name: reset_other_groups 
#     reset ads_best_version to "N" for all datasets in any groups not having
#     the current generation date. The member names must match exactly.
#
# Input:
#       db_arch       
#       group_info (hash ref)
#           needs group_name    
#           needs group_data_id 
#           needs gen_date      
#           needs mission       
#
# Output:
#       None
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
#
#----------------------------------------------------------------------------
sub reset_other_groups () {  
    my $nm = 'reset_other_groups' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_arch        = $_[0] ;
    my $hash_ref_group = $_[1] ;

    my %group_info    = %{$hash_ref_group} ;
    my $group_name    = $group_info{group_name} ;
    my $group_data_id = $group_info{group_data_id} ;
    my $gen_date      = $group_info{gen_date} ;
    my $mission       = $group_info{mission} ;

    my $query = <<"EOQ";
UPDATE archive_data_set_all 
SET ads_best_version = 'N' 
FROM ingest_data_set_info, archive_data_set_all
WHERE ids_mission = '$mission' and 
      ids_group_name = '$group_name' and
      ids_group_data_id = '$group_data_id' and 
      ids_generation_date = '$gen_date' and
      ads_data_set_name = ids_data_set_name and 
      ads_archive_class = ids_archive_class and 
      ads_mission = ids_mission and 
      ads_best_version = 'Y' and
      ads_generation_date != ids_generation_date
EOQ
    if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }
    my $count = DoDBI( $db_arch, $query);
    my $msg = "This group of datasets (gen_date $gen_date) IS the best version." ;
    PrintMsg("I", $msg, $nm);
    $msg = "Reset ads_best_version to N for $count datasets NOT having that gen_date." ;
    PrintMsg("I", $msg, $nm);
    if ( $dbVerbose ) {
        my $msg = "Not returning values. "  ;
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return ;
} # end reset_other_groups 

#----------------------------------------------------------------------------
# Name: get_cal_members 
#     get list of non-product members using a mask for the
#     dataset type character. Zero member count is OK  - e.g. STIS.
#
# Input:
#       db_arch       
#       group_info (hash ref)
#           needs group_name    
#           needs group_data_id 
#           needs gen_date      
#           needs mission       
#       precedence_vals 
#
# Output:
#       an array of member rootnames (ipppssoot values)
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
#
#----------------------------------------------------------------------------
sub get_cal_members ($$$) {  
    # get list of non-product members using a mask for the
    # dataset type character. Zero member count is OK  - e.g. STIS.
    my $nm = 'get_cal_members' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_arch         = $_[0] ;
    my $hash_ref_group  = $_[1] ;
    my $precedence_vals = $_[2] ;

    my %group_info    = %{$hash_ref_group} ;
    my $group_name    = $group_info{group_name} ;
    my $group_data_id = $group_info{group_data_id} ;
    my $gen_date      = $group_info{gen_date} ;
    my $mission       = $group_info{mission} ;

    my $query = <<"EOQ";
SELECT ids_data_set_name   
FROM ingest_data_set_info
WHERE ids_mission = '$mission' and 
      ids_group_name = '$group_name' and
      ids_group_data_id = '$group_data_id' and 
      ids_generation_date = '$gen_date' and
      ids_archive_class = 'CAL' and
      ids_data_set_name like '%[$precedence_vals]'
EOQ
    if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }

    PrintMsg("D","Querying ingest_data_set_info for ASN exposures.", $nm);
    my $err_msg1 = "Cannot access first ingest_data_set_info record";
    my $err_msg2 = "Cannot access next ingest_data_set_info record";
    my $dset_count = 0;
    my $dataset;
    my $dataset_mask;
    my @dset_list;
    my @ds_record;
    my $sth = DoDBIexecute( $db_arch, $query);
    while ( (@ds_record) = DoDBIfetch( $db_arch, $query, $sth) ) {
        $dset_count += 1;
        push @dset_list, $ds_record[0];  # add datset name to list
    }
    if ($dset_count > 0) {
       PrintMsg("D",
          "Obtained $dset_count exposure records from ingest_data_set_all.", $nm);
    }
    if ( $dbVerbose ) {
        my $msg = "Returning dset_list: "  ;
        $msg = $msg . "(" . join(", ", @dset_list) . ")";
        PrintMsg( 'D', $msg, $nm );
    }
    return @dset_list ;
}   # end get_cal_members


#----------------------------------------------------------------------------
# Name: get_sti_members 
#     get list of non-product members using the OPUS_DB asn_members table
#     Only use for STIS simply because the result has hard-coded 
#     an 'O' before the pppssoo.
#
# Input:
#       db_opus
#       group_info (hash ref)
#           needs group_name    
#
# Output:
#       array of member ipppssoo values
#
# History:
# yy/mm/dd PR     Who         Description
# 10/02/04 61672  Sherbert    Add parameters
#
#----------------------------------------------------------------------------
sub get_sti_members ($$) {  
    # get list of non-product members using OPUS asn_members table
    # because STIS members are NOT written to IDS 
    # Using asn_members means we do not know the 9th char of the member
    # rootname.   However, since we seem to drop this last char 
    # in favor of precedence values, perhaps we shall not miss it.
    my $nm = 'get_sti_members' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_opus         = $_[0] ;
    my $hash_ref_group  = $_[1] ;

    my %group_info    = %{$hash_ref_group} ;
    my $uc_group_name    = uc( $group_info{group_name} ) ;

    my $query = <<"EOQ";
SELECT 'O'+program_id+obset_id+member_num 
  FROM asn_members 
 WHERE association_id = '$uc_group_name'
   AND member_status  = 'C'
ORDER BY program_id, obset_id, member_num
EOQ

    if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }

    PrintMsg("D","Querying asn_members for ASN exposures.", $nm);
    my $err_msg1 = "Cannot access first asn_members record";
    my $err_msg2 = "Cannot access next asn_members record";
    my $dset_count = 0;
    my $dataset;
    my $dataset_mask;
    my @dset_list;
    my @ds_record;
    my $sth = DoDBIexecute( $db_opus, $query);
    while ( (@ds_record) = DoDBIfetch( $db_opus, $query, $sth) ) {
        $dset_count += 1;
        push @dset_list, $ds_record[0];  # add datset name to list
    }
    if ($dset_count > 0) {
       PrintMsg("D",
          "Obtained $dset_count exposure records from asn_members.", $nm);
    }
    if ( $dbVerbose ) {
        my $msg = "Returning dset_list: "  ;
        $msg = $msg . "(" . join(", ", @dset_list) . ")";
        PrintMsg( 'D', $msg, $nm );
    }
    return @dset_list ;
}   # end get_sti_members




#----------------------------------------------------------------------------
# Name: reset_cal_members 
#     reset_cal_members is a wrapper around reset_cal_datasets to 
#     reset ads_best_version to "N" for each non-product member 
#     not having the current generation date
# 
# Input:
#       dset_reference   - reference to an array of rootnames
#       group_info (hash ref)
#       precedence_vals - the same it is all the time, sigh
#       db_arch         - to pass to reset_cal_dataset
#
# Output:
#       None
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
#
#----------------------------------------------------------------------------
sub reset_cal_members () {  
    my $nm = 'reset_cal_members' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $dset_reference  = $_[0] ;
    my $hash_ref_group  = $_[1] ;
    my $precedence_vals = $_[2] ;
    my $db_arch         = $_[3] ;  ## Pass to reset_cal_dataset

    my %group_info    = %{$hash_ref_group} ;
    my @dset_list     = @{$dset_reference} ;
    my $dset_count    = scalar( @dset_list ) ;

    if ($dset_count > 0) {
       PrintMsg("I", "Obtained $dset_count exposure records from ingest_data_set_all.", $nm );
       $dataset = shift @dset_list;
       while (defined( $dataset) ) {
          $dataset_mask = substr( $dataset, 0 , 8)."[".$precedence_vals."]";
          PrintMsg("I","Exposure mask is $dataset_mask.", $nm);
          reset_cal_dataset( $db_arch, \%group_info, $dataset_mask );
          $dataset = shift @dset_list;
       }
    }
    if ( $dbVerbose ) {
        my $msg = "Not returning values. "  ;
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return ;
} # end reset_cal_members

#----------------------------------------------------------------------------
# Name: report_archive_table_fields 
#       report all archive db data to $trl file only
#       Two queries are made to obtain all the archive_data_set_all data
#       and all the related archive_files data. The queries are made prior
#       to the report generation and the query results are stored in two
#       array of arrays.
#
# Input:
#       db_arch       
#       group_info (hash ref)
#           needs group_name    
#           needs group_data_id 
#           needs gen_date      
#           needs mission       
#           needs trl
#
# Output:
#      prints out text
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters 
# 09/11/04 61672  Sherbert    add ads_proprietary_start_time
#
#----------------------------------------------------------------------------
sub report_archive_table_fields () { 
    # report all archive db data to $trl file only
    my $nm = 'report_archive_table_fields' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_arch        = $_[0] ;
    my $hash_ref_group = $_[1] ;

    my %group_info    = %{$hash_ref_group} ;
    my $group_name    = $group_info{group_name} ;
    my $group_data_id = $group_info{group_data_id} ;
    my $gen_date      = $group_info{gen_date} ;
    my $mission       = $group_info{mission} ;
    my $trl           = $group_info{trl} ;

    PrintMsg("I","Generating report on all archive table insertions.", $nm);

    # Two queries are made to obtain all the archive_data_set_all data
    # and all the related archive_files data. The queries are made prior
    # to the report generation and the query results are stored in two
    # array of arrays 
    # The data format 109 is "mon.dd.yyyy hh:mi:ss.mmmAM (or PM)" 

    # The first query loads the @all_list from archive_data_set_all
    my $query = <<"EOQ";
SELECT ads_data_set_name
     , ads_archive_class
     , CONVERT(varchar,ads_generation_date,109) generation_date
     , ads_mission
     , ads_best_version
     , CONVERT(varchar,ads_data_receipt_time,109) data_receipt
     , ads_data_set_size
     , ads_data_source
     , ads_file_count
     , ads_build_num
     , isnull(CONVERT(varchar,ads_proprietary_start_time,109),'NULL') proprietary_start_time
     , isnull(CONVERT(varchar,ads_release_date,109),'NULL') release_date
     , isnull(CONVERT(varchar,ads_release_date_mod,109),'NULL') release_date_mod
FROM ingest_data_set_info, archive_data_set_all
WHERE ids_mission = '$mission' and ids_group_name = '$group_name' and
   ids_group_data_id = '$group_data_id' and ids_generation_date = '$gen_date' 
   and ads_data_set_name = ids_data_set_name and 
   ads_archive_class = ids_archive_class and 
   ads_generation_date = ids_generation_date and ads_mission = ids_mission  
ORDER BY ads_data_set_name, ads_archive_class
EOQ
    if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }

    PrintMsg("I","Querying archive_data_set_all.", $nm);
    my $err_msg1 = "Cannot access first archive_data_set_all record";
    my $err_msg2 = "Cannot access next archive_data_set_all record";
    my $dset_count = 0;
    my @dset_list;
    my @dset_record;
    my $sth = DoDBIexecute( $db_arch, $query);
    while ( (@dset_record) = DoDBIfetch( $db_arch, $query, $sth) ) {
        $dset_count += 1;
        push @dset_list, [@dset_record];
    }
    PrintMsg("I","Obtained $dset_count records from archive_data_set_all.", $nm);
    
    if ($dset_count == 0) {     
        my $errMsg = $nm . "-Failed to read archive_data_set_all table" ;
        die( $errMsg );
    }
    # The second query loads the @file_list from archive_files
    $query = <<"EOQ";
SELECT afi_data_set_name
     , afi_archive_class
     , CONVERT(varchar,afi_generation_date,109) generation_date
     , afi_mission
     , afi_file_extension
     , afi_file_name
     , afi_file_type
     , afi_virtual
     , afi_pre_compress_size
     , afi_post_compress_size
     , afi_checksum
FROM ingest_data_set_info, archive_files
WHERE ids_mission = '$mission' and ids_group_name = '$group_name' and
   ids_group_data_id = '$group_data_id' and ids_generation_date = '$gen_date' 
   and afi_data_set_name = ids_data_set_name and 
   afi_archive_class = ids_archive_class and 
   afi_generation_date = ids_generation_date and afi_mission = ids_mission  
ORDER BY afi_data_set_name, afi_archive_class, afi_file_extension
EOQ
    if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }

    PrintMsg("I","Querying archive_files.", $nm);
    $err_msg1 = "Cannot access first archive_data_set_all record";
    $err_msg2 = "Cannot access next archive_data_set_all record";
    my $file_count = 0;
    my @file_list;
    my @file_record;
    my $sth = DoDBIexecute( $db_arch, $query);
    while ( (@file_record) = DoDBIfetch( $db_arch, $query, $sth) ) {
        $file_count += 1;
        push @file_list, [@file_record];
    }
    PrintMsg("I","Obtained $file_count records from archive_files.", $nm);
    
    if ($file_count == 0) {     
        my $errMsg = $nm . "-Failed to read archive_files table" ;
        die( $errMsg );
    }
    # Now we have all the data for the report, write the data:
    print $trl "\nReport of archive table data for $group_name ".
        "$group_data_id request.\n";

    # First,get the first file record into @file_rec
    my $file_record_ptr = shift @file_list;
    my @file_rec = @$file_record_ptr;
    my $dset_record_ptr;
    my @dset_rec;

    # process every dataset
    foreach $dset_record_ptr (@dset_list) {
        @dset_rec = @$dset_record_ptr;

        my $prop_start_tm = $dset_rec[10];
        if ($prop_start_tm ne "NULL") {$prop_start_tm = "'".$prop_start_tm."'";}
        my $release_date = $dset_rec[11];
        if ($release_date ne "NULL") {$release_date = "'".$release_date."'";}
        my $release_date_mod = $dset_rec[12];
        if ($release_date_mod ne "NULL") {
           $release_date_mod = "'".$release_date_mod."'";
        }
        # report dset record values
        printf $trl "\nRecord for archive_data_set_all:\n";
        printf $trl "   ads_data_set_name = '%s'\n",          $dset_rec[0];
        printf $trl "   ads_archive_class = '%s'\n",          $dset_rec[1];
        printf $trl "   ads_generation_date = '%s'\n",        $dset_rec[2];
        printf $trl "   ads_mission = '%s'\n",                $dset_rec[3],
        printf $trl "   ads_best_version = '%s'\n",           $dset_rec[4];
        printf $trl "   ads_data_receipt_time = '%s'\n",      $dset_rec[5];
        printf $trl "   ads_data_set_size = %.0f\n",          $dset_rec[6];
        printf $trl "   ads_data_source = '%s'\n",            $dset_rec[7];
        printf $trl "   ads_file_count = %d\n",               $dset_rec[8];
        printf $trl "   ads_build_num = '%s'\n",              $dset_rec[9];
        printf $trl "   ads_release_date = %s\n",             $release_date;
        printf $trl "   ads_release_date_mod = %s\n",         $release_date_mod;
        printf $trl "   ads_proprietary_start_time = %s\n",   $prop_start_tm;

        while ($dset_rec[0] eq $file_rec[0] &&
               $dset_rec[1] eq $file_rec[1]) 
        {
            # report file record values
            printf $trl "\nRecord for archive_files:\n";
            printf $trl "   afi_data_set_name = '%s'\n",      $file_rec[0];
            printf $trl "   afi_archive_class = '%s'\n",      $file_rec[1];
            printf $trl "   afi_generation_date = '%s'\n",    $file_rec[2];
            printf $trl "   afi_mission = '%s'\n",            $file_rec[3];
            printf $trl "   afi_file_extension = '%s'\n",     $file_rec[4];
            printf $trl "   afi_file_name = '%s'\n",          $file_rec[5];
            printf $trl "   afi_file_type = '%s'\n",          $file_rec[6];
            printf $trl "   afi_virtual = '%s'\n",            $file_rec[7];
            printf $trl "   afi_pre_compress_size = %.0f\n",  $file_rec[8];
            printf $trl "   afi_post_compress_size = %.0f\n", $file_rec[9];
            printf $trl "   afi_checksum = %d\n",             $file_rec[10];
             
	    # get next record or quit loop
            $file_record_ptr = shift @file_list;
            if (!defined($file_record_ptr)) {last;}
            @file_rec = @$file_record_ptr;
        } # end while for each file  
    } # end foreach dset_list
    print $trl "\nEnd of report.\n\n";
    if ( $dbVerbose ) {
        my $msg = "Not returning values. "  ;
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return ;
} # end report_archive_table_fields

#----------------------------------------------------------------------------
# Name: send_remote_status   
#       send status to remote pipeline using variable data
#
# Input:
#       group_info (hash ref)
#           needs group_info
#           needs group_name
#           needs group_data_id
#       completed_group  
#       process_mode     
#       needs_insertion  # can be undefined.  Afraid to change that.
#       bad_data         
#       remote_params (hash ref)
#
# Output:
#       None - osf_update command sent
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
#
#----------------------------------------------------------------------------
sub send_remote_status () {  
    # send status to remote pipeline 
    my $nm = 'send_remote_status' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $hash_ref_group   = $_[0] ;
    my $completed_group  = $_[1] ;
    my $process_mode     = $_[2] ;
    my $needs_insertion  = $_[3] ;
    my $bad_data         = $_[4] ;
    my $hash_ref_remote  = $_[5] ;

    my %group_info    = %{$hash_ref_group} ;
    my $group_name    = $group_info{group_name} ;
    my $group_data_id = $group_info{group_data_id} ;

    PrintMsg( 'D', "group_name is       $group_name", $nm );
    PrintMsg( 'D', "group_data_id is    $group_data_id", $nm );

    my %remote_params    = %{$hash_ref_remote} ;
    my $remote_path_file = $remote_params{remote_path_file} ;
    my $remote_stage     = $remote_params{remote_stage}     ;
    my $remote_success   = $remote_params{remote_success}   ;
    my $remote_failure   = $remote_params{remote_failure}   ;
    my $remote_alarm     = $remote_params{remote_alarm}     ;

    PrintMsg( 'D', "remote_path_file is $remote_path_file", $nm );
    PrintMsg( 'D', "remote_stage is     $remote_stage", $nm );
    PrintMsg( 'D', "remote_success is   $remote_success", $nm );
    PrintMsg( 'D', "remote_failure is   $remote_failure", $nm );
    PrintMsg( 'D', "remote_alarm is     $remote_alarm", $nm );

    my $remote_status = 'N/A';
    my $send_it;

    PrintMsg( 'D', "process_mode     is $process_mode ", $nm ) ;
    PrintMsg( 'D', "bad_data         is $bad_data. ", $nm );
    PrintMsg( 'D', "needs_insertion  is $needs_insertion. ", $nm );
    if ($process_mode eq "ALARM") {
        $remote_status = $remote_alarm;
        $send_it = $true;
        my $msg = "process_mode eq ALARM; remote_status ($remote_status) ";
        $msg = $msg . "equals remote_alarm ($remote_alarm) and " ;
        $msg = $msg . "send_it ($send_it) equals true ($true) " ;
        PrintMsg( 'D', $msg, $nm );
    } elsif ($bad_data) {
        $remote_status = $remote_failure;
        $send_it = $true;
        my $msg = "; remote_status ($remote_status) ";
        $msg = $msg . "equals remote_failure ($remote_failure) and " ;
        $msg = $msg . "send_it ($send_it) equals true ($true) " ;
        PrintMsg( 'D', $msg, $nm );
    } elsif (!$needs_insertion) {
        # a success but redundant - no second siganl 
        my $msg = "needs_insertion=false so set send_it to false ($false)" ;
        PrintMsg('D', $msg, $nm);
        $send_it = $false;
        $msg = "No setting of remote_status ($remote_status) but ";
        $msg = $msg . "send_it ($send_it) equals false ($false) " ;
        PrintMsg( 'D', $msg, $nm );
    } else {
        $remote_status = $remote_success;
        $send_it = $true;
        my $msg = "else: ? process mode UPDATE or EMAIL_FAILURE ?; ";
        $msg = $msg . "remote_status ($remote_status) ";
        $msg = $msg . "equals remote_success ($remote_success) and " ;
        $msg = $msg . "send_it ($send_it) equals true ($true) " ;
        PrintMsg( 'D', $msg, $nm );
    }
   #PrintMsg( 'D', 
   #    "send_it is $send_it and remote_status is $remote_status. ", $nm );
   # signal the status to the remote OSF 
   #PrintMsg( 'D', "completed_group is $completed_group ", $nm );
    if ($send_it && ($process_mode ne "UPDATE" || $completed_group)) {
        my $msg = "send_it=true ($send_it) AND ";
        $msg = $msg . "[process_mode ($process_mode) is not UPDATE ";
        $msg = $msg . "OR completed_group=true ($completed_group)]. Try: ";
        PrintMsg( 'D', $msg, $nm );
        PrintMsg( "I", 'signal the status to the remote OSF ', $nm);
        $upd_command="osf_update -p $remote_path_file -f $group_name".
             " -t $group_data_id -c $remote_stage -s $remote_status";
        PrintMsg( 'D', "upd_command: $upd_command. ", $nm );
        $msg = "NOT Setting $remote_path_file pipeline $remote_stage stage " ;
        $msg = $msg . "to status $remote_status for $group_name. " ;
        PrintMsg("I", $msg, $nm);

        #if (system($upd_command)) {
        #    my $errMsg = $nm . "-Error executing command: $upd_command" ;
        #    die( $errMsg );
        #}
    }
    if ( $dbVerbose ) {
        my $msg = "Not returning values. "  ;
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return ;
} # end send_remote_status

#----------------------------------------------------------------------------
# Name: check_merge_reqd
#       See if ipppssoo is in otfr_required_processing with 
#       osp_processing_steps = MERGE_REQD.  Only singletons will be found 
#       in otfr_special_processing (osp), so if an ASN is passed in, 
#       return 0.
#
# Input:
#       db_arch
#       rootname
#       precedence_vals - a constant
#
# Output:
#       is_this_a_qs_split
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 63837  Sherbert    Add parameters
#
#----------------------------------------------------------------------------
sub check_merge_reqd () {  
    my $nm = 'check_merge_reqd' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_arch         = $_[0] ;
    my $rootname        = uc( $_[1] ) ;
    my $precedence_vals = $_[2] ;

    my $is_this_a_qs_split = 0 ; # init

    PrintMsg( 'I', "db_arch is $db_arch", $nm ) ;
    PrintMsg( 'I', "rootname is $rootname", $nm ) ;
    PrintMsg( 'I', "precedence_vals is $precedence_vals", $nm ) ;

    # Never gonna be ASN in otfr_special_processing table
    # Need to be sure I am getting members (2-digit oo) here.  
    my ( $ppp, $ss, $oo ) = get_PSO( $rootname, $precedence_vals );
    my $si = substr( $rootname, 0, 1 ) ;
    my $ipppssoo = $si . $ppp . $ss . $oo ;
    if ( $dbVerbose ) { PrintMsg( 'D', "ipppssoo is $ipppssoo.", $nm ); }
    if ( 3 == length( $oo ) ) {
        # $oo is 3-char therefore rootname must be an ASN 
        # No ASNs will be in otfr_special_processing therefore return 0
        return $is_this_a_qs_split ;    # 0 the init value
    }

        my $query = <<"EOQ";
SELECT COUNT(*) 
  FROM otfr_special_processing
 WHERE osp_data_set_name = '$ipppssoo'
   AND osp_processing_steps = 'MERGE_REQD'
   AND osp_status = 'ACTIVE'
EOQ

    if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }
    PrintMsg("I", "Querying otfr_special_processing to check for Q/S-splits.", $nm);
    my @record_cnt = DoDBIselect( $db_arch, $query);
    if ( $record_cnt[0] > 0 ) {
        my $msg = "$ipppssoo is a Q/S-Split.  Q trumps S, if necessary. " ;
        PrintMsg("I", $msg, $nm) ;
        $is_this_a_qs_split = $true ;
    } else {
        my $msg = "$ipppssoo is NOT a Q/S-Split. ";
        PrintMsg("I", $msg, $nm) ;
        $msg = "(S still trumps Q)" ;
        PrintMsg("D", $msg, $nm ) ;
        $is_this_a_qs_split = $false ;
    }

    if ( $dbVerbose ) {
        my $msg = "Returning: is_this_a_qs_split: $is_this_a_qs_split. "  ;
        PrintMsg( 'D', $msg, $nm);
    }
    PrintMsg( 'D', "Done ... ", $nm);
    return $is_this_a_qs_split ;
} # end check_merge_reqd

#----------------------------------------------------------------------------
# Name: get_pep_id
# 
# Input: 
#       connection_to_SPSS_DB - ST_DBlib (or DBI) connection
#       rootname              - 
#       precedence_vals      - 
# 
# Output: 
#       record - containing proposal_id 
# 
# History:
# 10/20/09 61672 Sherbert changes to proprietary rights
#----------------------------------------------------------------------------
sub get_pep_id () {  
    # determine program_id (ppp), obset_id (ss), ob_number (oo) and pep_id
    my $nm = 'get_pep_id';
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_spss          = $_[0] ;
    my $rootname         = uc($_[1]) ;
    my $precedence_vals = $_[2];

#   if ( $dbVerbose ) { 
    PrintMsg( 'I', "rootname         is $rootname. ", $nm );
    PrintMsg( 'I', "precedence_vals is $precedence_vals. ", $nm );
#   }
    my ( $ppp, $ss, $oo ) = get_PSO( $rootname, $precedence_vals );
#   if ( $dbVerbose ) { 
#       my $msg     = "ppp: " . $ppp . "; " ;
#       $msg = $msg . "ss:  " . $ss  . "; " ;
#       $msg = $msg . "oo:  " . $oo;
#       PrintMsg( 'D', $msg, $nm );
#   }

# FROM $SPSS_DB..qolink
    my $query = <<"EOQ";
SELECT DISTINCT proposal_id
  FROM qolink
 WHERE program_id = '$ppp'
EOQ
    if ( $dbVerbose ) { PrintMsg( 'I', "query:\n$query", $nm ); }
    my @prop_id = DoDBIselect( $db_spss, $query);

    if (!defined($prop_id[0])) {
        # PRB class could be anything and a 9-char input name may not be
        # an HST science exposure, could be a CDBS file...
        # I think we will have to return a value that indicates 
        # no pep_id was found by the query 
        # instead of exiting with an error
        $prop_id[0] = 0 ;
    }
    
    if ( $dbVerbose ) {
        my $msg = "Returning proposal_id "  ;
        $msg = $msg . "(" . join(", ", @prop_id) . ")";
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return $prop_id[0];
} # end get_pep_id

#----------------------------------------------------------------------------
# Name: get_PSO
#
# Using this subroutine to distinguish between ASNs and member/singletons
# (mostly in the 2-char ob_number vs 3-char obsnum sense).
# However, since it is called in a few different places, the input cannot 
# always be guaranteed to be an IPPPSSOOT (esp. when called by get_pep_id).  
# Therefore, IF the -w switch for perl is used, 
# we can sometimes expect "substr outside of string" warnings 
# which look like errors.  Therefore, remove the very pesky -w switch from 
# the first line of the *.pl script.
#
# Input: 
#       rootname              - input name to dissect
#       precedence_vals      - a constant
#
# Returns: 
#       @records containing [ ppp, ss, oo ]
#       Note: 3-char-length oo means ASN
#
# History:
# 10/20/09 61672 Sherbert changes to proprietary rights
#----------------------------------------------------------------------------
sub get_PSO ($$) {  
    # determine program_id (ppp), obset_id (ss), ob_number (oo) and pep_id
    my $nm = 'get_PSO';
    PrintMsg( 'D', "Starting... ", $nm );

    my $rootname        = uc($_[0]) ;
    my $precedence_vals = $_[1];

#   if ( $dbVerbose ) { 
#       PrintMsg( 'D', "rootname         is $rootname. ", $nm );
#       PrintMsg( 'D', "precedence_vals is $precedence_vals. ", $nm );
#   }
    my @records;
    my $ppp = substr( $rootname, 1, 3 );
    push @records, $ppp;
    my $ss  = substr( $rootname, 4, 2 );
    push @records, $ss;
    my $oo  = substr( $rootname, 6, 3 );
    # If rootname is a singleton, then only use 2 chars for oo
    if ( substr( $oo, 2, 1 ) =~ /[$precedence_vals]/ ) { 
        $oo  = substr( $oo, 0, 2 );
    }
    push @records, $oo;
#   if ( $dbVerbose ) { 
#       my $msg     = "ppp: " . $ppp . "; " ;
#       $msg = $msg . "ss:  " . $ss  . "; " ;
#       $msg = $msg . "oo:  " . $oo;
#       PrintMsg( 'D', $msg, $nm );
#   }

    if ( $dbVerbose ) {
        my $msg = "Returning ppp, ss, oo: "  ;
        $msg = $msg . "(" . join(", ", @records) . ")";
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return @records;
} # end get_PSO

#----------------------------------------------------------------------------
# Name: check_restricted_data
#       Goal: get rda_proprietary_start_time from restricted_data
#             IF it is there, else return generation date
#
#       Query ARCH_DB restricted_data for input proposal number
#             AND rda_status eq "ACTIVE"  
#       If NO record is found, use input generation date for prop'ty start tm
#       else use rda_proprietary_start_time for prop'ty start tm
#
# Input:   
#       connection_to_ARCH_DB - ST_DBlib (or DBI) connection
#       group_info (hash ref)
#           generation_date       - 
#           proposal_id           - 
#
# Output:  a proprietary_start_time that can be used for archive_data_set_all
#
# History:
# 10/20/09 61672 Sherbert changes to proprietary rights
#
#----------------------------------------------------------------------------
sub check_restricted_data () {  
    # determine ads_proprietary_start_time
    my $nm = 'check_restricted_data';
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_arch        = $_[0] ; ## connection to ARCH_DB
    my $hash_ref_group = $_[1] ;

    my %group_info    = %{$hash_ref_group} ;
    my $gen_date      = $group_info{gen_date} ;
    my $pep_id        = int( $group_info{proposal_id} );

#######
 # To reference a hash, pass \%group_info
 # To deref the hash, do: my $hash_ref_group = @_; my %group_info = %{$hash_ref_group}
 # To access the parts, after deref: $group_info{group_name} (no quotes)
#printHash( %group_info );  ## Del me
#######

    PrintMsg( 'D', "db_arch         is $db_arch         ", $nm ) ;
    PrintMsg( 'D', "gen_date        is $gen_date        ", $nm ) ;
    PrintMsg( 'D', "pep_id          is $pep_id          ", $nm ) ;


    my $query = <<"EOQ";
SELECT DISTINCT CONVERT(varchar, rda_proprietary_start_time, 100)
  FROM restricted_data
 WHERE rda_pep_id = $pep_id 
   AND rda_status = 'ACTIVE'
EOQ
    if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }
    my @prop_st = DoDBIselect( $db_arch, $query);
    my $proprietary_start_tm = $prop_st[0];
    if ( $dbVerbose && defined( $proprietary_start_tm ) ) { 
        PrintMsg( 'D', "...returns... $proprietary_start_tm.", $nm );
    }
    if ( ! defined($prop_st[0]) ) {
        # default to ads_generation_date
        $proprietary_start_tm = $gen_date;
        PrintMsg( "I", "Using gen_date as default proprietary_start_time ", $nm );
    }
    else {
        my $msg = "Found proprietary_start_time ($proprietary_start_tm) in restricted_data." ;
        PrintMsg( "I", $msg, $nm );
    }
    if ( $dbVerbose ) { 
        my $msg = "Returning proprietary_start_tm: $proprietary_start_tm " ;
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return $proprietary_start_tm ;
} # end check_restricted_data

#----------------------------------------------------------------------------
# Name: check_obs_prop_per
#       Proprietary Rights 2nd check: 
#           is there a PEP_ID/PPP/SS/OO entry 
#           in obs_proprietary_period?
#
# Input:
#       db_arch        - connection to ARCH_DB       
#       group_info (hash ref)
#           needs group_name 
#           needs group_data_id  
#           needs gen_date 
#           needs mission 
#           needs pep_id 
#       db_assi        - connection to ASSIST_DB
#       db_opus        - connection to OPUS_DB 
# 
# Output:
#       prop_per_list  - list of proprietary period in months, OR 'no',
#                        per member
#
# History:
# 10/20/09 61672 Sherbert changes to proprietary rights
#----------------------------------------------------------------------------
sub check_obs_prop_per () {
    my $nm = 'check_obs_prop_per';
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_arch         = $_[0] ;
    my $hash_ref_group  = $_[1] ;
    my $precedence_vals = $_[2] ;
    my $db_assi         = $_[3] ;
    my $db_opus         = $_[4] ;   # In case it is a stis ASN

    my %group_info    = %{$hash_ref_group} ;
    my $group_name    = $group_info{group_name} ;
    my $group_data_id = $group_info{group_data_id} ;
    my $gen_date      = $group_info{gen_date} ;
    my $mission       = $group_info{mission} ;
    my $pep_id        = int( $group_info{proposal_id} );

    PrintMsg( 'I', "db_arch         is $db_arch         ", $nm ) ;
    PrintMsg( 'I', "group_name      is $group_name      ", $nm ) ;
    PrintMsg( 'I', "group_data_id   is $group_data_id   ", $nm ) ;
    PrintMsg( 'I', "gen_date        is $gen_date        ", $nm ) ;
    PrintMsg( 'I', "mission         is $mission         ", $nm ) ;
    PrintMsg( 'I', "precedence_vals is $precedence_vals ", $nm ) ;
    PrintMsg( 'I', "db_assi         is $db_assi         ", $nm ) ;
    PrintMsg( 'I', "pep_id          is $pep_id          ", $nm ) ;
    PrintMsg( 'I', "db_opus         is $db_opus         ", $nm ) ;

    my @members = () ;
    my $caller ;
    if ( $group_data_id =~ 'sas' || $group_data_id =~ 'Sas' ) {
        PrintMsg( 'I', 'group_data_id in STIS family', $nm ) ;
        # STIS members are NOT written to IDS therfore must find 
        # them some other way.
        @members = get_sti_members( $db_opus, \%group_info );
        $caller = "get_sti_members" ;
    } else {
        @members = get_cal_members( $db_arch, \%group_info, $precedence_vals );
        $caller = "get_cal_members" ;
    }
    my $num_members = scalar( @members );
    my $msg = "$caller found $num_members group members " ;
    PrintMsg( 'I', $msg, $nm ) ;
    # Why did I not need this before?
    if ( $num_members == 0 ) {
        $members[0] = $group_name ;
    }

    my @prop_per_list ;
    foreach $member ( @members ) {
        PrintMsg( 'I', "member is $member ", $nm ) ;
        # break the name down
        my ( $ppp, $ss, $oo ) = get_PSO( $member, $precedence_vals ) ;

        # look for name in obs_proprietary_period
        # If any ONE member has an entry, then the period will apply to ALL
        my $query = <<"EOQ";
SELECT DISTINCT period
  FROM obs_proprietary_period
 WHERE prop_id   = $pep_id 
   AND program_id = '$ppp'
   AND obset_id   = '$ss'
   AND obsnum     = '$oo'
EOQ
        if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }
        my @periods = DoDBIselect( $db_assi, $query);
        PrintMsg('I',"DISTINCT period=@periods\n", $nm);
    
        if ( ! defined( $periods[0] ) ) {
            # Not sure it is required that there be a record...
            # Would like to raise an error here that caller could interpret
            $periods[0] = 'no' ;
           #my $msg = 'There was no result from query?  Assigning periods[0]=no.' ;
           #PrintMsg( 'D', $msg, $nm ) ;
        }
        # create a list of proprietary periods (or 'no') for each member
       #my $msg = 'Push $periods[0] (' . $periods[0] . ') onto @prop_per_list' ;
       #PrintMsg( 'D', $msg, $nm ) ;
        push @prop_per_list, $periods[0] ;
    }

    if ( $dbVerbose ) {
        my $msg = "Found proprietary period for each member: " ;
        $msg = $msg . "(" . join(", ", @prop_per_list ) . ")";
        PrintMsg( 'D', $msg, $nm );
    }

   #PrintMsg( 'D', 'Create undefined $prop_per', $nm ) ;
    my $prop_per ;
   #$msg = 'Number of elements in @prop_per_list is ' . scalar( @prop_per_list ) ;
   #PrintMsg( 'D', $msg, $nm );
    if ( scalar( @prop_per_list ) > 1 ) {
       #my $msg = 'Number of elements in @prop_per_list must be greater than 1.' ;
       #PrintMsg( 'D', $msg, $nm );
        # Do all the values agree?  We only want to return one consisent value.
       #$msg = 'Next call check_period_list, assign result to $prop_per, wrap whole in eval' ;
       #PrintMsg( 'D', $msg, $nm );
        eval { $prop_per = check_period_list( @prop_per_list )  } ;
        die "Too many periods values" if $@ ;
       #$msg = 'check_period_list returned $prop_per = ' . $prop_per ;
       #PrintMsg( 'D', $msg, $nm );
    }
    else { # No need to call check_period_list for an array of 1 value.
       #my $msg = 'Number of elements in @prop_per_list must NOT be greater than 1.' ;
       #PrintMsg( 'D', $msg, $nm );
       #$msg = 'Next assign 0th element of $prop_per[iod]_list to $prop_per, BET This was the error!' ;
       #PrintMsg( 'D', $msg, $nm );
        $prop_per = $prop_per_list[0] ;
       #$msg = '$prop_per_list[0] returned $prop_per = ' . $prop_per ;
       #PrintMsg( 'D', $msg, $nm );
    }

    if ( $dbVerbose ) {
        my $msg = "Returning proprietary period: $prop_per " ;
        PrintMsg( 'D', $msg, $nm );
    }

    PrintMsg( 'D', "Done ... ", $nm );
    return $prop_per ;
} # end check_obs_prop_per

#----------------------------------------------------------------------------
# Name: check_period_list
#       Proprietary Rights helper: 
#           observation_proprietary_period could contain >1 member of an ASN.
#           Make sure ASN members do NOT have multiple period values.
#           If they do, we should exit with an error.
#
#    In the following code, why non-'no'?
#     If pppssoo NOT in observation_proprietary_program, the period value
#     will be 'no' so that info can be transmitted.  We want to eliminate 
#     those now so they do not disagree with valid values.
#
# Input:
#       period_list - an array returned by check_obs_prop_per
# 
# Output:
#       propreitary_period - in months
#
# History:
# 10/20/09 61672 Sherbert changes to proprietary rights
# 02/05/10 61672 Sherbert create an error if >1 value found
#----------------------------------------------------------------------------
sub check_period_list () {
    my $nm = 'check_period_list';
    PrintMsg( 'D', "Starting... ", $nm );

    my @inArray = @_;
    my @arrayOfNonNoElements ;

    my $msg = "Verify that all period list elements agree." ;
    PrintMsg( 'D', $msg, $nm ) ;
    PrintMsg( 'D', "inArray is @inArray ", $nm );

    # Eliminate any 'no' values so they do not disagree with valid values.
    for ( my $i=0 ; $i < scalar( @inArray ); $i++ ) { 
       # Create an array on non-'no' values
       if ( $inArray[$i] ne "no" ) { 
           PrintMsg( "$nm-I-inArray[$i] is not 'no' ", $nm );
           push @arrayOfNonNoElements, $inArray[$i] ;
       }
    }
    my $sizeOfArrayOfNonNoElements = scalar( @arrayOfNonNoElements ) ;
    PrintMsg( 'D', "sizeOfArrayOfNonNoElements is $sizeOfArrayOfNonNoElements ", $nm );
    PrintMsg( 'D', "arrayOfNonNoElements       is @arrayOfNonNoElements ", $nm );

    if ( $sizeOfArrayOfNonNoElements == 0 ) { 
        # Must have been all 'no' values
        return 'not_in_obs_proprietary_period' ;
    }

    # Check to see if any remaining non-'no' array values disagree.
    # As soon as one set disagrees, throw an error.
    for ( my $j=0 ; $j < $sizeOfArrayOfNonNoElements - 1 ; $j++ ) { 
       foreach my $item ( @arrayOfNonNoElements ) { 
           my $msg = "Is $item (item) equal to " ;
           my $next_item = $arrayOfNonNoElements[$j+1] ;
           $msg = $msg = "$next_item (next_item)? " ;
           PrintMsg( 'D', $msg, $nm );
           if ( $next_item != $item ) { 
               my $errMsg = $nm . "-Error found non-matching period values " ;
               die( $errMsg );
              #return "error" ;
           }
       }
    }
    # All values agreed, so send back any one of them
    my $propreitary_period = $arrayOfNonNoElements[0] ;

    PrintMsg( 'D', "Done ... ", $nm );
    return $propreitary_period ;
} # end check_period_list

#----------------------------------------------------------------------------
# Name: check_prog_prop_per
#       Proprietary Rights 3rd check: 
#           is there a PEP_ID entry in program_proprietary_period?
#
# Input:
#       db_assi  - connection_to_ASSIST_DB (ST_DBlib (or DBI) connection)
#       proposal_id - pep_id
#
# Output:
#       proprietary_period    - in months
#
# History:
# 10/20/09 61672 Sherbert changes to proprietary rights
#----------------------------------------------------------------------------
sub check_prog_prop_per () {  
    my $nm = 'check_prog_prop_per';
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_assi       = $_[0] ;
    my $pep_id        = int($_[1]) ;

    my $release_date ;

    my $query = <<"EOQ";       ## Currently bogus
SELECT DISTINCT period
  FROM program_proprietary_period
 WHERE prop_id = $pep_id 
EOQ
    if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }
    my @periods = DoDBIselect( $db_assi, $query);

    my $prop_period = $periods[0] ;
    if ( ! defined($periods[0]) ) {
        # Not sure it is required that there be a record...
        # Would like to raise an error here that caller could interpret
        PrintMsg ( 'D', "prop_period is EMPTY so should become 'no' ", $nm );
        $prop_period = 'no' ;
    }
    PrintMsg ( 'D', "prop_period is $prop_period ", $nm );

    if ( $dbVerbose ) { 
        my $msg = "Returning proprietary period: $prop_period " ;
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return $prop_period ;
} # end check_prog_prop_per

#----------------------------------------------------------------------------
# Name: set_a_date
#     Find the earliest field1 date and (optionally) the latest field2 date
#     in ADS for any previously ingested versions of group_name.  
#
#     This query is used to reset an ADS date-time field1 
#     (like ads_release_date or ads_proprietary_start_time) 
#     to its minimum value and an ADS date-time field2 
#     (e.g. ads_release_date_mod) to its maximum non-null value, 
#     if necessary.   
#
#     For cases from PR 63837, when a dataset originally processed 
#     with 9th char X but upon reprocessing becomes 9th char Y
#     this query is not going to find a match and therefore will 
#     NOT preserve the original dates.   How do we fix that?  
#
# Input:
#       db_arch       
#       group_info (hash ref)
#           needs group_name    
#           needs group_data_id 
#           needs gen_date      
#           needs mission       
#       precedence_vals 
#       qs_split  - 0 (not), 1 (is and therefore a different query nec.) 
#       field1 - a date field 
#       field2 - a date field, optional  
#
# Output:
#      None - may call a routine to change database
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Will this work for > 1 field?
# 10/04/22 64912  Sherbert    Handle cases from PR 63837 
#
#----------------------------------------------------------------------------
sub set_a_date () {  
    # reset ads_release_date to its minimum value and 
    # ads_release_date_mod to its maximum non-null value 
    my $nm = 'set_a_date';
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_arch         = $_[0] ;
    my $hash_ref_group  = $_[1] ;
    my $precedence_vals = $_[2] ;
    my $qs_split        = $_[3] ;
    my $field1          = $_[4] ;
    my $field2          = $_[5] ;  ## optional

    my %group_info    = %{$hash_ref_group} ;
    my $group_name    = $group_info{group_name} ;
    my $group_data_id = $group_info{group_data_id} ;
    my $gen_date      = $group_info{gen_date} ;
    my $mission       = $group_info{mission} ;

    PrintMsg( 'D', "db_arch         is $db_arch         " ) ;
    PrintMsg( 'D', "precedence_vals is $precedence_vals " ) ;
    PrintMsg( 'D', "qs_split        is $qs_split        " ) ;
    PrintMsg( 'D', "field1          is $field1          " ) ;
    PrintMsg( 'D', "field2          is $field2          " ) ;  ## optional
    PrintMsg( 'D', "group_name      is $group_name    " ) ;
    PrintMsg( 'D', "group_data_id   is $group_data_id " ) ;
    PrintMsg( 'D', "gen_date        is $gen_date      " ) ;
    PrintMsg( 'D', "mission         is $mission       " ) ;

    my $msg;
    my $select_query ;
    if ( ! defined($field2 ) ) {
        $select_query = <<"EOQ";
SELECT CONVERT(varchar,MIN($field1),109) min_release
EOQ
    }
    else {
        $select_query = <<"EOQ";
SELECT CONVERT(varchar,MIN($field1),109) min_release
     , isnull(CONVERT(varchar,MAX($field2),109),'NULL') max_release_mod
EOQ
    }

    my $dsn_query;
    # NO DEFAULT VALUE for qs_split ... THIS NO WKG ... 
    if ( ! defined( $qs_split ) || $qs_split == 0 ) {
        $dsn_query = "AND ads_data_set_name = ids_data_set_name " ;
    }
    else {
        # qs_split means previous version could have a different 
        # 9th char from current version in a reingest situation 
        $dsn_query = <<"EOQ";
  AND ads_program_id = upper(substring(ids_data_set_name, 2, 3))
  AND ads_obset_id   = upper(substring(ids_data_set_name, 5, 2))
  AND ads_obsnum     = upper(substring(ids_data_set_name, 7, 2))
EOQ
    }

    # first get earlier date field from other generations
    # smoosh select_query to the next line because the EOQ builds in a \n
    my $query = <<"EOQ" ;
${select_query}FROM ingest_data_set_info, archive_data_set_all
WHERE ids_mission = '$mission' 
  AND ids_group_name = '$group_name' 
  AND ids_group_data_id = '$group_data_id' 
  AND ids_generation_date = '$gen_date' 
  ${dsn_query}AND ads_archive_class = ids_archive_class 
  AND ads_mission = ids_mission 
  AND ads_generation_date != ids_generation_date 
EOQ
    if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }
    PrintMsg("D","Querying archive_data_set_all for other $field1 dates.", $nm);
    my $err_msg = "Cannot access ADS for other $field1 dates";
    my @db_record = DoDBIselect( $db_arch, $query);
    my $min_release     = $db_record[0];
    my $min_release_mod = $db_record[1];
    if ( defined( $min_release     ) ) { 
        $msg = "min_release     is $min_release " ;
        PrintMsg( 'D', $msg, $nm );
    }
    else {
        $msg = "min_release     is NOT defined (new Ingest likely) " ;
        PrintMsg( 'D', $msg, $nm );
    }
    if ( ! defined( $min_release_mod ) ) { $min_release_mod = "NULL" ; }
    $msg = "min_release_mod is $min_release_mod " ;
    PrintMsg( 'D', $msg, $nm );
    
    if (defined($min_release)) {
       PrintMsg("I","Multiple versions of this data - resetting $field1 date.", $nm );
       reset_a_date( $db_arch, \%group_info, $min_release, $min_release_mod, $field1, $field2 );
    } else {
       PrintMsg("I","Only one version of this data - no $field1 date changes.", $nm );
    }
    if ( $dbVerbose ) {
        my $msg = "Not returning values. "  ;
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return ;
} # end set_a_date    

#----------------------------------------------------------------------------
# Name: reset_a_date
#       Update all versions of archive_data_set_all entries for group_info
#       to use date values from older entries to override the values just 
#       put in place for the new data.  (Used for ads_release_date and 
#       ads_proprietary_start_time which should not change after initial 
#       ingest.)  field2, likely ads_release_date_mod, may be set to NULL.
#
# Input:
#       db_arch
#       group_info (hash ref)
#           needs group_name
#           needs group_data_id
#           needs gen_date
#           needs mission
#       release_date     
#       release_date_mod 
#       field1           
#       field2           ## optional
#
# Output:
#       None - may change database
#
# History:
# yy/mm/dd PR     Who         Description
# 09/10/29 61672  Sherbert    Add parameters
#
#----------------------------------------------------------------------------
sub reset_a_date () {  
    my $nm = 'reset_a_date' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $db_arch          = $_[0] ;
    my $hash_ref_group   = $_[1] ;
    my $release_date     = $_[2] ;
    my $release_date_mod = $_[3] ;
    my $field1           = $_[4] ;
    my $field2           = $_[5] ;  ## optional

    my %group_info    = %{$hash_ref_group} ;
    my $group_name    = $group_info{group_name} ;
    my $group_data_id = $group_info{group_data_id} ;
    my $gen_date      = $group_info{gen_date} ;
    my $mission       = $group_info{mission} ;

    my $msg ;                       ## expect many incarnations

#   $msg = "release_date     is $release_date " ;
#   PrintMsg( 'D', $msg, $nm );
#   $msg = "release_date_mod is $release_date_mod " ;
#   PrintMsg( 'D', $msg, $nm );
#   $msg = "field1           is $field1     " ;
#   PrintMsg( 'D', $msg, $nm );
#   if ( defined( $field2 ) ) {
#       $msg = "field2           is $field2     " ;
#       PrintMsg( 'D', $msg, $nm );
#   }

    # update all versions of archive_data_set_all to use values 
    # which overrides the values of the new data which are based on the
    # current date or are null. The field2 (likely to represent
    # ads_release_date_mod) may be set to NULL.

    if ($release_date_mod eq "NULL") {
       $set_date_mod = "NULL";
    } else {
       $set_date_mod = "'".$release_date_mod."'";
    }
    $msg = "set_date_mod  is $set_date_mod " ;
    PrintMsg( 'D', $msg, $nm );

    my $update_query ;
    if ( ! defined( $field2 ) ) {
        $update_query = <<"EOQ";
UPDATE archive_data_set_all
SET $field1 = '$release_date' 
EOQ
    }
    else {
        $update_query = <<"EOQ";
UPDATE archive_data_set_all
SET $field1 = '$release_date', 
    $field2 = $set_date_mod
EOQ
    }

    # smoosh update_query to the next line because the EOQ builds in a \n
    my $query = <<"EOQ";
${update_query}FROM ingest_data_set_info, archive_data_set_all
WHERE ids_mission = '$mission' and 
      ids_group_name = '$group_name' and
      ids_group_data_id = '$group_data_id' and 
      ids_generation_date = '$gen_date' and
      ads_data_set_name = ids_data_set_name and 
      ads_archive_class = ids_archive_class and 
      ads_mission = ids_mission and 
      $field1 != '$release_date'
EOQ
    if ( $dbVerbose ) { PrintMsg( 'D', "query:\n$query", $nm ); }
    PrintMsg("I","Updating $field1 to $release_date.", $nm );
    if ($set_date_mod ne "NULL") {
        PrintMsg("I","Updating $field2 to $release_date_mod.", $nm );
    }
    my $update_count = DoDBI( $db_arch, $query);
    PrintMsg("I","$update_count ADS records updated for $field1 date.", $nm);
    if ( $dbVerbose ) {
        my $msg = "Not returning values. "  ;
        PrintMsg( 'D', $msg, $nm );
    }
    PrintMsg( 'D', "Done ... ", $nm );
    return ;
} # end reset_a_date    

#----------------------------------------------------------------------------
# Name: send_email
#       If a default value is used to determine ads_release_date, the 
#       requirements want the operators notified.
#       
#
# Input:
#       group_info (hash ref)
#           needs group_name
#           needs gen_date 
#           needs mission 
#           needs archclass 
#           needs pep_id      = proposal ID to save operators some trouble
#       default_mos = number of months to add to gen_date to create release dt
#       to_address 
#       log_dir 
#
# Output:
#       email sent
#       status code
#
# History:
# yy/mm/dd PR     Who         Description
# 09/11/11 61672  Sherbert    create
#
#----------------------------------------------------------------------------
sub send_email () {  
    my $nm = 'send_email' ;
    PrintMsg( 'D', "Starting... ", $nm );

    my $hash_ref_group = $_[0] ;
    my $default_mos    = $_[1] ;
    my $to_address     = $_[2] ;
    my $log_dir        = $_[3] ;

    my %group_info    = %{$hash_ref_group} ;
    my $dataset       = $group_info{group_name} ;
    my $gen_date      = $group_info{gen_date} ;
    my $mission       = $group_info{mission} ;
    my $archclass     = $group_info{archive_class} ;
    my $pep_id        = int( $group_info{proposal_id} );

    PrintMsg( 'D', "dataset     is $dataset    ", $nm ) ;
    PrintMsg( 'D', "archclass   is $archclass  ", $nm ) ;
    PrintMsg( 'D', "gen_date    is $gen_date   ", $nm ) ;
    PrintMsg( 'D', "mission     is $mission    ", $nm ) ;
    PrintMsg( 'D', "pep_id      is $pep_id     ", $nm ) ;
    PrintMsg( 'D', "default_mos is $default_mos", $nm ) ;
    PrintMsg( 'D', "to_address  is $to_address ", $nm ) ;
    PrintMsg( 'D', "log_dir     is $log_dir    ", $nm ) ;

    my $ok = 0 ;
    my $not_ok = 1 ;
    my $exit_status = $ok ;

    # open email message file in log directory
    my $msg_file   = $log_dir."/".$dataset ."_".$archclass.".msg";
    PrintMsg( 'D', "msg_file   is $msg_file", $nm ) ;
    my $msg_status = " DEFAULT $default_mos months proprietary period used" ;
    PrintMsg( 'D', "msg_status is $msg_status", $nm ) ;
    my $subject    = $mission . ' INGEST ' . $dataset . $msg_status;
    PrintMsg( 'D', "subject    is $subject", $nm ) ;
    my $msg_email = <<"EOM" ;
for mission         $mission 
    prop_id         $pep_id 
    archive_class   $archclass 
    data_set_name   $dataset 
    generation_date $gen_date 
EOM
    PrintMsg( 'D', "msg_email  is $msg_email", $nm ) ;

    my $mailer = "mailx" ;
    PrintMsg( 'D', "mailer     is $mailer", $nm ) ;


    # Write rmail message
    open (MSG, ">".$msg_file);
    print MSG "$msg_status\n";              # Make a complete sentence
    print MSG "$msg_email\n";

    # send rmail message
    close MSG;
    my $cmd ;
    $cmd = "cat $msg_file | $mailer -s '" .$subject. "' $to_address ";  
   #$cmd = "smack $msg_file ";  # force an error
    PrintMsg( 'D', "cmd        is $cmd   ", $nm ) ;
    if (system("$cmd")) {
       PrintMsg("E","Failed to mail Ingest proprietary period message.", $nm );
       PrintMsg("E","Failed command: $cmd", $nm );
       $exit_status = $not_ok ;
    }

    # mail message no longer needed
    $count = unlink $msg_file ;
    if ($count) {
       PrintMsg("D","Deleted mail message file", $nm );
    } else {
       PrintMsg("W","Could not delete mail message file: $msg_file ", $nm);
    }
    PrintMsg( 'D', "Done ... ", $nm );

    return $exit_status ;
} # end send_email    

1;

####################################################################
#SELECT DISTINCT CONVERT(varchar, rda_proprietary_start_time, 109)
#                                      Dec 15 2015  3:15:15:000PM 
#SELECT DISTINCT CONVERT(varchar, rda_proprietary_start_time, 100)
#                                      Dec 15 2015  3:15PM
#SELECT DISTINCT CONVERT(varchar, rda_proprietary_start_time, 116)
#                                      Dec 15 2015 15:15:15
#SELECT DISTINCT CONVERT(varchar, rda_proprietary_start_time, 117)
#                                      2015/12/15 15:15:15
#SELECT DISTINCT CONVERT(varchar, rda_proprietary_start_time, 118)
#                                      2015/12/15  3:15PM 
####################################################################