Rem
Rem $Header: ecm_hostpatch_pkgbody.sql 07-aug-2007.06:03:00 aloganda Exp $
Rem
Rem ecm_hostpatch_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ecm_hostpatch_pkgbody.sql - Defines the MGMT_ECM_HOSTPATCH package.
Rem
Rem    DESCRIPTION
Rem      Defines the MGMT_ECM_HOSTPATCH package.
Rem
Rem    NOTES
Rem      None.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    aloganda    08/07/07 - 
Rem    ranmath     06/01/07 - XbranchMerge ranmath_bug-4573095 from main
Rem    ranmath     05/30/07 - bug-4573095: Use saved overridden credentials, if
Rem                           any, for undoing Host Patching configuration on a
Rem                           host.
Rem    ranmath     08/23/05 - bug-4565977: Do not delete Host Patching Jobs. 
Rem    ranmath     08/05/05 - bug-4538024: Ensure that all non-active job 
Rem                           executions are deleted before stopping active 
Rem                           ones and deleting the job to ensure that an 
Rem                           execution doesn't start just before we try to 
Rem                           delete the job. 
Rem    ranmath     05/10/05 - Undo host patching configuration only if there 
Rem                           is at least one host in the group. 
Rem    achugh      02/28/05 - bug 4199708: Add procedure 
Rem                           'get_hosts_prefcreds_not_set'. 
Rem    tasingh     02/07/05 - Add get_host_timezones. 
Rem    ranmath     12/08/04 - (bug-4053039) Fix Group information deletion 
Rem                           on Target deletion callback. 
Rem    ranmath     12/07/04 - (bug-4046371) Update for schema changes. 
Rem    ranmath     12/03/04 - (bug-4046371) Update procedure definitions for 
Rem                           new columns. 
Rem    ranmath     11/25/04 - Add delete_repos_info. 
Rem    ranmath     11/24/04 - Do not use Group name in undo configuration Job 
Rem                           name. 
Rem    dsahrawa    10/17/04 - Use job_schedule_record static ctor 
Rem    ranmath     10/08/04 - Add functions for Job Executions status. 
Rem    ranmath     10/04/04 - Undo Host Patching configuration on member Hosts 
Rem                           when a Host Patching Group is deleted. 
Rem    ranmath     09/16/04 - Created
Rem

CREATE OR REPLACE PACKAGE BODY mgmt_ecm_hostpatch
IS

  --
  -- Clean up the Host Patching related information for a Group,
  -- if any.
  --
  PROCEDURE delete_group_info(
    p_group_guid IN RAW,
    p_group_name IN VARCHAR2)
  IS
    l_job_id mgmt_job.job_id%type;
    l_job_name mgmt_job.job_name%type;
  BEGIN
    -- Do we have any information on this Group?
    DECLARE
      l_tmp_guid mgmt_ecm_hostpatch_groups.group_guid%type;
    BEGIN
      SELECT group_guid INTO l_tmp_guid
        FROM mgmt_ecm_hostpatch_groups
        WHERE group_guid = p_group_guid;
    EXCEPTION
      -- We don't know about this Group - don't bother anymore.
      WHEN NO_DATA_FOUND THEN
        RETURN;
    END;


    -- Stop the updater Job for this Group, if any.
    BEGIN
      SELECT updater_job_guid INTO l_job_id
        FROM mgmt_ecm_hostpatch_groups
        WHERE group_guid = p_group_guid;

      -- bug-4565977
      stop_hp_job( l_job_id);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        NULL;
    END;


    -- Stop the compliance collection Job for this Group, if any.
    BEGIN
      SELECT collector_job_guid INTO l_job_id
        FROM mgmt_ecm_hostpatch_groups
        WHERE group_guid = p_group_guid;

      -- bug-4565977
      stop_hp_job( l_job_id);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        NULL;
    END;


    -- Undo Host Patching configuration on member Hosts for this Group.
    DECLARE
      l_job_tgts_list MGMT_JOB_TARGET_LIST;
      l_job_params MGMT_JOB_PARAM_LIST;
      l_job_sched MGMT_JOB_SCHEDULE_RECORD;
      l_job_creds MGMT_JOB_CRED_ARRAY := null;
      l_saved_creds MGMT_CRED_ROW_ARRAY;
      l_host_cred MGMT_CRED_RECORD;
      l_cred_guid RAW(16);
      l_execution_id RAW(16);
      l_ctr_1 NUMBER(6);
      l_ctr_2 NUMBER(6);
      CURSOR l_grp_tgts_cr IS
        SELECT t.target_name AS host_name, t.target_guid AS host_guid
          FROM mgmt_ecm_hostpatch_hosts h, mgmt_targets t
          WHERE t.target_guid = h.host_guid
            AND h.group_guid = p_group_guid;
    BEGIN
      l_job_name
        := 'Undo Patching Configuration '
           || TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF');

      l_job_tgts_list := MGMT_JOB_TARGET_LIST();
      l_ctr_1 := 1;
      l_ctr_2 := 1;
      FOR l_target IN l_grp_tgts_cr
      LOOP
        l_job_tgts_list.extend(1);
        l_job_tgts_list(l_ctr_1)
          := MGMT_JOB_TARGET_RECORD(
              l_target.host_name,
              MGMT_ECM_HOSTPATCH.HOSTPATCH_HOST_TARGET_TYPE);

        l_ctr_1 := l_ctr_1 + 1;

        -- BEGIN: bug-4573095
        -- See if we have saved overridden credentials for this host.
        BEGIN
          SELECT credential_guid INTO l_cred_guid
            FROM mgmt_container_credentials
            WHERE target_guid=l_target.host_guid
              AND container_location=mgmt_ecm_hostpatch.hostpatch_container_location
              AND credential_set_name=mgmt_ecm_hostpatch.oh_creds
              AND user_name=mgmt_user.get_current_em_user();

          SELECT
            MGMT_CRED_ROW_RECORD(
              credential_set_column,
              decode(credential_value, null, null, decrypt(credential_value)))
            BULK COLLECT INTO l_saved_creds
            FROM MGMT_CREDENTIALS2
            WHERE credential_guid=l_cred_guid;

          l_host_cred
            := MGMT_CRED_RECORD(
                mgmt_user.get_current_em_user(),
                mgmt_ecm_hostpatch.host_creds_normal,
                l_saved_creds);

          IF l_job_creds IS NULL
          THEN
            l_job_creds := MGMT_JOB_CRED_ARRAY();
          END IF;

          l_job_creds.extend(1);
          l_job_creds(l_ctr_2)
            := MGMT_JOB_CRED_RECORD(
                l_target.host_name,
                mgmt_ecm_hostpatch.hostpatch_host_target_type,
                null,
                l_host_cred);

          mgmt_credential.delete_container_credentials(
            mgmt_ecm_hostpatch.oh_creds,
            l_target.host_name,
            mgmt_ecm_hostpatch.hostpatch_container_location);

          l_ctr_2 := l_ctr_2 + 1;

        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            NULL;
        END;
        -- END: bug-4573095
      END LOOP;

      IF l_job_tgts_list.last IS NOT NULL
      THEN
        l_job_params := MGMT_JOB_PARAM_LIST();
        l_job_params.extend(4);
        l_job_params(1)
          := MGMT_JOB_PARAM_RECORD('reposURLs',
                                   MGMT_JOBS.PARAM_TYPE_SCALAR,
                                   '_dummy_',
                                   null);
        l_job_params(2)
          := MGMT_JOB_PARAM_RECORD('needRebootPkgsOption',
                                   MGMT_JOBS.PARAM_TYPE_SCALAR,
                                   '_dummy_',
                                   null);
        l_job_params(3)
          := MGMT_JOB_PARAM_RECORD('undoOption',
                                   MGMT_JOBS.PARAM_TYPE_SCALAR,
                                   '-undo',
                                   null);

        l_job_params(4)
          := MGMT_JOB_PARAM_RECORD('groupName',
              MGMT_JOBS.PARAM_TYPE_SCALAR,
              '_dummy_',
              null);

        l_job_sched
          := MGMT_JOBS.get_job_schedule_record(
               MGMT_JOBS.ONE_TIME_FREQUENCY_CODE,
               SYSDATE, null,
               0, 0, 0, null, null,
               MGMT_JOBS.TIMEZONE_REPOSITORY,
               -1, 0, 0);

        MGMT_JOBS.submit_job(l_job_name,
                             'Undo Patching Configuration for Hosts in Group '
                             || p_group_name,
                             'ConfigHostForPatching',
                             l_job_tgts_list,
                             l_job_params,
                             l_job_sched,
                             l_job_id,
                             l_execution_id,
                             null,
                             0,
                             l_job_creds);
      END IF;
    END;


    -- Delete the compliance history information for this Group.
    DELETE FROM mgmt_ecm_hostpatch_compl_hist
      WHERE group_guid = p_group_guid;

    -- Delete the package repository associations of this Group.
    DELETE FROM mgmt_ecm_hostpatch_group_repos
      WHERE group_guid = p_group_guid;


    -- Delete compliance information for Hosts that belonged to
    -- this Group.
    DELETE FROM mgmt_ecm_hostpatch_host_compl
      WHERE host_guid IN
      (
        SELECT host_guid
          FROM mgmt_ecm_hostpatch_hosts
          WHERE group_guid = p_group_guid
      );


    -- Delete the information on Hosts that belonged to this Group.
    DELETE FROM mgmt_ecm_hostpatch_hosts
      WHERE group_guid = p_group_guid;
    

    -- Delete the Host Patching information for the Group itself.
    DELETE FROM mgmt_ecm_hostpatch_groups
      WHERE group_guid = p_group_guid;
  END delete_group_info;


  --
  -- Clean up the Host Patching related information for a Host,
  -- if any.
  --
  PROCEDURE delete_host_info(
    p_host_guid IN RAW,
    p_host_name IN VARCHAR2)
  IS
  BEGIN
    -- Do we know this Host?
    DECLARE
      l_host_guid mgmt_ecm_hostpatch_hosts.host_guid%type;
    BEGIN
      SELECT host_guid INTO l_host_guid
        FROM mgmt_ecm_hostpatch_hosts
        WHERE host_guid = p_host_guid;
    EXCEPTION
      -- Unknown Host. Don't bother.
      WHEN NO_DATA_FOUND THEN
        RETURN;
    END;


    -- Delete the compliance information for the Host.
    DELETE FROM mgmt_ecm_hostpatch_host_compl
      WHERE host_guid = p_host_guid;
    

    -- Delete Host Patching information for the Host.
    DELETE FROM mgmt_ecm_hostpatch_hosts
      WHERE host_guid = p_host_guid;
  END delete_host_info;


  --
  -- Clean up the Host Patching related information for a package repository,
  -- if any.
  --
  PROCEDURE delete_repos_info(
    p_repos_guid IN RAW)
  IS
    l_know_repos NUMBER(1);
    l_job_id mgmt_job.job_id%type;
  BEGIN
    -- Do we know anything about this package repository?
    SELECT COUNT(repos_guid) INTO l_know_repos
      FROM mgmt_ecm_hostpatch_repos
      WHERE repos_guid = p_repos_guid;

    IF l_know_repos = 0
    THEN
      -- Unknown package repository - don't bother any more.
      RETURN;
    END IF;


    -- Stop the refresher Job for this package repository, if any.
    BEGIN
      SELECT refresher_job_guid INTO l_job_id
        FROM mgmt_ecm_hostpatch_repos
        WHERE repos_guid = p_repos_guid;

      -- bug-4565977
      stop_hp_job( l_job_id);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        NULL;
    END;


    -- Delete packages information for this package repository, if any.
    DELETE FROM mgmt_ecm_hostpatch_repos_pkgs
      WHERE repos_guid = p_repos_guid;


    -- Delete the main information for the package repository.
    DELETE FROM mgmt_ecm_hostpatch_repos
      WHERE repos_guid = p_repos_guid;
  END delete_repos_info;


  --
  -- Set the compliance summary information for the given Host Patching
  -- Group.
  --
  PROCEDURE update_group_compl_summary(
    p_group_name IN VARCHAR2,
    p_total_hosts IN NUMBER,
    p_compl_hosts IN NUMBER)
  IS
    l_group_guid mgmt_ecm_hostpatch_groups.group_guid%type;
    l_today DATE := TRUNC(SYSDATE);
    l_an_year_ago DATE;
  BEGIN
    -- Get the Group's Target GUID.
    SELECT g.group_guid INTO l_group_guid
      FROM mgmt_ecm_hostpatch_groups g, mgmt_targets t
      WHERE t.target_guid = g.group_guid
        AND t.target_name = p_group_name;

    -- Delete any existing record for today for the Group.
    DELETE FROM mgmt_ecm_hostpatch_compl_hist
      WHERE group_guid = l_group_guid
      AND checked_on = l_today;

    -- Add the record for today for the Group.
    INSERT INTO
      mgmt_ecm_hostpatch_compl_hist(
        group_guid,
        total_hosts,
        compl_hosts,
        checked_on)
      VALUES(l_group_guid,p_total_hosts,p_compl_hosts,l_today);


    -- Remove records that are more than a year old.
    --
    -- FIXME: We ought to use the EM Purge Policy framework here, but
    -- that is unnecessarily complicated. Some other day perhaps...

    SELECT TRUNC(l_today - 365) INTO l_an_year_ago FROM DUAL;

    -- Now purge the old records.
    DELETE FROM mgmt_ecm_hostpatch_compl_hist
      WHERE group_guid = l_group_guid
      AND checked_on < l_an_year_ago;
  END update_group_compl_summary;

  
  --
  -- Clean up the Host Patching related information for a Host or
  -- a Group when it is deleted. This procedure is called by the EM
  -- Core when a Target is deleted.
  --
  PROCEDURE group_or_host_deleted(
    p_target_name IN VARCHAR2,
    p_target_type IN VARCHAR2,
    p_target_guid IN RAW)
  IS
  BEGIN
    IF p_target_type = HOSTPATCH_GROUP_TARGET_TYPE
    THEN
      delete_group_info( p_target_guid, p_target_name);
    ELSIF p_target_type = HOSTPATCH_HOST_TARGET_TYPE
    THEN
      delete_host_info( p_target_guid, p_target_name);
    END IF;
  END group_or_host_deleted;


  -- bug-4565977
  --
  -- Stop the given Host Patching job.
  --
  PROCEDURE stop_hp_job(
    p_job_id IN RAW)
  IS
    l_job_name mgmt_job.job_name%type;
    l_job_owner mgmt_job.job_owner%type;
    l_dummy_sch mgmt_job_schedule_record;
    l_dummy_target_list mgmt_job_target_list;
  BEGIN
    SELECT job_name, job_owner INTO l_job_name, l_job_owner
      FROM mgmt_job
      WHERE job_id = p_job_id;

    -- Set a dummy schedule (once on 1 Jan 1970) for the job so that
    -- scheduled runs for a job with an originally repeating schedule
    -- are purged.

    l_dummy_sch
      := mgmt_jobs.get_job_schedule_record(
          mgmt_jobs.ONE_TIME_FREQUENCY_CODE,
          TO_DATE( '1970:01:01', 'YYYY:MM:DD'),
          TO_DATE( '1970:01:02', 'YYYY:MM:DD'),
          1,
          1,
          0,
          NULL,
          NULL,
          mgmt_jobs.TIMEZONE_REPOSITORY,
          0,
          0,
          NULL,
          -1);

    mgmt_jobs.edit_job(
      l_job_name,
      l_job_owner,
      0,
      NULL,
      NULL,
      l_dummy_target_list,
      l_dummy_sch,
      NULL,
      NULL);

    -- Stop currently active executions of the job.
    mgmt_jobs.stop_all_executions( l_job_name, l_job_owner);

    -- NOTE:
    -- We used to delete the job altogether, but to do that reliably,
    -- we need to wait for all STOP_PENDING steps to complete. This is
    -- not kosher in a synchronous procedure call.

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END stop_hp_job;

  --
  -- Get the number of problematic Executions for Jobs of the given type.
  --
  FUNCTION get_num_problematic_executions(
    p_job_type IN VARCHAR2,
    p_timeframe IN NUMBER DEFAULT 7)
  RETURN NUMBER
  IS
    l_retval NUMBER(6);
  BEGIN
    l_retval := 0;

    SELECT COUNT(s.execution_id) INTO l_retval
      FROM mgmt_job j, mgmt_job_exec_summary s
      WHERE (j.job_id = s.job_id)
        AND (j.job_type = p_job_type)
        AND (s.scheduled_time > (SYSDATE - p_timeframe))
        AND (s.status_bucket = MGMT_JOBS.STATUS_BUCKET_PROBLEM);

    RETURN l_retval;
  END get_num_problematic_executions;


  --
  -- Get the number of suspended Executions for Jobs of the given type.
  --
  FUNCTION get_num_suspended_executions(
    p_job_type IN VARCHAR2,
    p_timeframe IN NUMBER DEFAULT 7)
  RETURN NUMBER
  IS
    l_retval NUMBER(6);
  BEGIN
    l_retval := 0;

    SELECT COUNT(s.execution_id) INTO l_retval
      FROM mgmt_job j, mgmt_job_exec_summary s
      WHERE (j.job_id = s.job_id)
        AND (j.job_type = p_job_type)
        AND (s.scheduled_time > (SYSDATE - p_timeframe))
        AND (s.status_bucket = MGMT_JOBS.STATUS_BUCKET_SUSPENDED);

    RETURN l_retval;
  END get_num_suspended_executions;


  --
  -- Gets the timezones for all the hosts viewable by this user.
  --
  FUNCTION get_host_timezones
  RETURN SMP_EMD_STRING_ARRAY
  IS
    l_retval SMP_EMD_STRING_ARRAY;
  BEGIN
    SELECT DISTINCT timezone_region
    BULK COLLECT INTO l_retval
    FROM mgmt_targets
    WHERE target_type = HOSTPATCH_HOST_TARGET_TYPE;

    RETURN l_retval;
  END get_host_timezones;

  --  bug 4199708
  -- 
  -- Gets an array of host names that do not have preferred credentials set.  
  --
  FUNCTION get_hosts_prefcreds_not_set(
    p_target_list   IN SMP_EMD_STRING_ARRAY,
    p_cred_set_name IN VARCHAR2 
  )
  RETURN SMP_EMD_STRING_ARRAY
  IS
    l_em_user     VARCHAR2(256) := MGMT_USER.get_current_em_user();
    l_target_list SMP_EMD_STRING_ARRAY;   
    l_cred_guid   MGMT_ENTERPRISE_CREDENTIALS.CREDENTIAL_GUID%TYPE;
    l_retval      SMP_EMD_STRING_ARRAY;
  BEGIN
   
    IF ( p_target_list IS NOT NULL AND p_target_list.COUNT > 0 ) THEN

     
      BEGIN
        SELECT credential_guid INTO l_cred_guid
          FROM MGMT_ENTERPRISE_CREDENTIALS
          WHERE target_type = HOSTPATCH_HOST_TARGET_TYPE
          AND   credential_set_name = p_cred_set_name
          AND   user_name = l_em_user;
      EXCEPTION WHEN no_data_found THEN NULL;
      END;

      IF ( l_cred_guid IS NOT NULL ) THEN
        l_retval := NULL; -- default credentials set 
      ELSE
        l_retval := p_target_list;
        -- If default credentials are not set, check if they are explicitly set
        -- for each target
        -- Obtain all hosts that have pref cred set 
        
        BEGIN
          SELECT t.target_name
            BULK COLLECT INTO l_target_list
            FROM MGMT_TARGET_CREDENTIALS c, MGMT_TARGETS t
            WHERE c.target_guid = t.target_guid
            AND   t.target_type = HOSTPATCH_HOST_TARGET_TYPE
            AND   c.credential_set_name = p_cred_set_name
            AND   c.user_name = l_em_user;
        EXCEPTION WHEN NO_DATA_FOUND THEN
         l_target_list := NULL;
         NULL;
        END;
        

        IF l_target_list IS NOT NULL AND l_target_list.count > 0 THEN
            -- Filter out targets with creds from p_target_list
          FOR i IN l_target_list.FIRST..l_target_list.LAST 
          LOOP

            IF ( l_retval IS NULL       OR   -- not expected
                 l_retval.FIRST IS NULL OR
                 l_retval.COUNT <= 0
                ) THEN
               EXIT;                              -- already all pruned.
            END IF;

            FOR j IN l_retval.FIRST..l_retval.LAST 
            LOOP
              IF ( l_retval.EXISTS(j) AND
                   l_target_list(i) = l_retval(j) ) THEN
                   l_retval.DELETE(j);
                   EXIT;
              END IF;
            END LOOP;
          END LOOP;
        END IF;
      END IF; 
    END IF;
   RETURN l_retval; 
  END get_hosts_prefcreds_not_set;

END mgmt_ecm_hostpatch;
/

show errors;