Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/basic/basic_loader_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/1 2009/01/21 20:28:49 jsadras Exp $
Rem
Rem basic_loader_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      basic_loader_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    jsadras     01/01/09 - Bug:7479524 , bound rollups
Rem    jashukla    06/03/08 - Bug 7144619 remove internal identifiers
Rem    caroy       07/26/07  - 
Rem    rrawat      06/08/07  - Bug-6114814
Rem    rrawat      07/23/07  - Backport rrawat_bug-6114814 from main
Rem    jsadras     08/11/06  - migrate get_retention windows to emd_maint_util
Rem    jsadras     05/03/07  - Backport jsadras_bug-5357916 from main
Rem    smalathe    04/27/07  - Bug 5956240: Correct emdw_log call
Rem    smodh       03/29/07  - Add Host Metadata Caching
Rem    smodh       04/11/07 - Backport smodh_bug-4727918 from main
Rem    dgiaimo     04/09/07  - Removing redundant check
Rem    dgiaimo     04/09/07  - Removing redundant check
Rem    dgiaimo     04/16/07 - Backport dgiaimo_bug-5927961 from main
Rem    neearora    09/13/05 - Bug 4607971. added procedure 
Rem                           submit_discard_state_job 
Rem    neearora    04/02/07  - Backport neearora_bug-4607971 from main
Rem    rmaggarw    03/05/07  - bug5856156 - use seconds internally
Rem    rmaggarw    03/12/07  - Backport rmaggarw_bug-5856156 from main
Rem    neearora    09/29/06  - handled DUP_VAL_ON_INDEX exception while
Rem                            updating row_set_handlers
Rem    rmaggarw    08/25/06  - get backlog across oms
Rem    rmaggarw    08/28/06 - Backport rmaggarw_bug-5475506 from main
Rem    denath      07/29/06 - Backport denath_bug-5390224 from main 
Rem    neearora    07/24/06  - project 21470. added API to register callback 
Rem                            for CRS event 
Rem    pmodi       11/09/05 - Bug:4712213/8 - Chg qry for perf 
Rem    pmodi       07/23/06 - Backport pmodi_bug-4712213 from main 
Rem    neearora    03/27/06 - Bug 5108394. Adding capability in loader to 
Rem                           delete rows through uploads 
Rem    neearora    07/16/06 - Backport neearora_bug-5108394 from main 
Rem    jsadras     07/19/06 - Backport jsadras_bug-4588426 from main 
Rem    scgrover    07/12/06 - Backport scgrover_bug-5043773 from main
Rem    pmodi       01/19/06 - Bug:4588500 - chg qry for perf
Rem    pmodi       07/11/06 - Backport pmodi_bug-4588500 from main 
Rem    rmaggarw    05/17/06  - 5092232-get backlog across oms 
Rem    rmaggarw    07/10/06 - Backport rmaggarw_bug-5092232 from main 
Rem    pmodi       04/27/06 - Bug:3900473 Use function to get default 
Rem                           retention window for metric tables 
Rem    pmodi       07/07/06 - Backport pmodi_bug-3900473 from main 
Rem    jsadras     01/29/06 - bug:4588426, purge system_performance_log 
Rem                           performance 
Rem    rkpandey    12/07/05 - Bug 4753716:Set last load type while releasing 
Rem                           lock 
Rem    rkpandey    12/12/05 - Backport rkpandey_bug-4753716 from main 
Rem    gan         12/02/05 - Backport gan_bug-4669119 from main 
Rem    jsadras     12/09/05 - Bug:4696731, snapshot too old 
Rem    rpinnama    09/21/05 - Fix 4595139 : Obtain settings lock before 
Rem                           updating the settings 
Rem    gan         09/09/05 - include host for update cache 
Rem    rpinnama    09/06/05 - Add mgmt_coll_items, mgmt_coll_item_metrics to 
Rem                           metadata tables 
Rem    neearora    09/01/05 - Bug 4574664. Changed update_metadata_cache 
Rem    rmaggarw    08/26/05 - make update_loader_params autonomous 
Rem    pmodi       08/22/05 - Bug:4561906 - Handle exception during error 
Rem                           logging for broken target 
Rem    rkpandey    08/18/05 - Remove redundant get_target_details 
Rem    pmodi       08/16/05 - Bug:4538663 - Include order by clause and chg 
Rem                           batch size 
Rem    jsadras     08/04/05 - Bug:4496694 , sync up unit_nlsid 
Rem    pmodi       08/02/05 - Bug:4529233 - Existance chk for clear state in 
Rem                           handle broken target 
Rem    rmaggarw    08/01/05 - add clean_admin_messages
Rem    pmodi       07/26/05 - Bug:4505434 - Chk for same name and port in 
Rem                           end_mgmt_targets_rowset 
Rem    rmaggarw    07/26/05 - 4436017: cutover nio -> dbms_lock 
Rem    rkpandey    07/25/05 - Bug 4502846:Throw exception 
Rem                           AGENT_LOCK_BUSY_ERR
Rem    rmaggarw    07/20/05 - remove commit from failover 
Rem    pmodi       07/21/05 - Bug:4260179 - Handle broken target 
Rem    neearora    07/20/05 - Bug 4500972. upload callback added 
Rem    gan         07/18/05 - add callback for metric metadata 
Rem    neearora    07/13/05 - Bug 4476740. Delete records from 
Rem                           mgmt_targets_delete only after 5 days. 
Rem    neearora    07/05/05 - Canged obtain/release lock_for_emd_url to accept 
Rem                           file type 
Rem    neearora    07/01/05 - Bug 4453451: Supressed the error message 'Fatal 
Rem                           error fetching rollup timestamps' if the target 
Rem                           is deleted 
Rem    neearora    06/28/05 - Change the signature of START_UPLOAD to accept 
Rem                           p_upload_time  
Rem    rmaggarw    06/28/05 - add logPerformance 
Rem    gan         06/24/05 - catch new exception 
Rem    rmaggarw    06/20/05 - bug 4336804: reset backlog on oms failover 
Rem    jsadras     06/19/05 - Bug:4406046, current_metrics_purge deadlock 
Rem    neearora    06/16/05 - Bug 4343519. Added to procedure 
Rem                           obtain_lock_for_emd_url and 
Rem                           release_lock_for_emd_url 
Rem    neearora    06/10/05 - changed END_MGMT_TARGETS_ROWSET to call 
Rem                           em_sec.delete_agent_key  
Rem    neearora    06/10/05 - added function get_name_port_from_emd_url.
Rem    rmaggarw    05/31/05 - add msgId in dequeue 
Rem    rpinnama    05/31/05 - Add threshold for avail metric with a coll_name
Rem    rmaggarw    05/23/05 - 4350484:remove delay in enqueue of START msg 
Rem    jsadras     05/16/05 - Security fixes, remove references to p_emd_schema 
Rem    rkpandey    05/12/05 - Optimised plan for get_target_details 
Rem    jsadras     04/27/05 - Bug:4262569: deadlock on mgmt_targets 
Rem    rpinnama    05/06/05 - Fix bug 4302515 : Insert the change_agent_url 
Rem                           rec to track changes across files 
Rem    jsadras     03/28/05 - Bug:4148612 check callback 
Rem    rmaggarw    03/29/05 - Bug:4241027:change order in update_loader_metrics 
Rem    rmenon      03/16/05 - added nls id for response metric and
Rem                           its status column.
Rem    jsadras     03/16/05 - Bug:4148612, Enforcing callback signature 
Rem    jsadras     03/10/05 - Bug:4186866 metaver callbacks 
Rem    rpinnama    03/15/05 - Fix 4116241: Supported restricted updates on 
Rem                           mgmt_target.timezone_region 
Rem    rkpandey    03/05/05 - Added get_target_details 
Rem    rmaggarw    03/10/05 - 4175804: install api for sharedfs 
Rem    ramalhot    03/03/05 - fixed bug 4218008 
Rem    jsadras     02/27/05 - Bug:4201036 1Dayrollup 
Rem    rmaggarw    02/24/05 - bug 4204931: prefix schema name to AQ objects 
Rem    eporter     02/25/05 - Add rollup times for new tables 
Rem    ramalhot    02/23/05 - call to em_rep_metric package removed 
Rem    jsadras     02/20/05 - bug:3114089 (emd_raw_purge,1hour_purge,1day_purge)
Rem    rpinnama    02/21/05 - Use the new create_metric API 
Rem    jsadras     02/17/05 - commit in update_met_label_nlsid for Bug:4055992 
Rem    ramalhot    02/17/05 - infrom em_master_agent about agent port change 
Rem    rzkrishn    02/09/05 - setting timezone region for the target to new 
Rem                           agent after relocation 
Rem    rpinnama    02/06/05 - Fix 4127785. Retain the resouce bundle during metadata load
Rem    ramalhot    01/17/05 - ASSOC_DEF_MEMBER changed to ASSOC_DEF_CONTAINS 
Rem    edemembe    01/28/05 - Locking CBO stats for large partitioned IOTs 
Rem    rpinnama    01/03/05 - Use register_target_type API 
Rem    rzkrishn    01/04/05 - if update fails , insert to DUPLICATES table 
Rem    asawant     11/11/04 - Add property propagation. 
Rem    bkesavan    12/14/04 - Change string and metrics error to system wide purge
Rem    rpinnama    12/03/04 - Fix 4044204 : Add procedure to add host 
Rem                           Response/Status metric 
Rem    ramalhot    10/20/04 - 
Rem    rmaggarw    10/27/04 - 2957569 add procedures for shared_fs 
Rem    ramalhot    10/08/04 - *_membership_rowset modified 
Rem    rmaggarw    10/08/04 - add procedures for shared_fs 
Rem    rkpandey    09/29/04 - 
Rem    kmanicka    09/20/04 - 
Rem    ramalhot    08/25/04 - cutover to new assoc tables 
Rem    rpinnama    09/29/04 - Add register_rowset_handler API 
Rem    vkgarg      08/24/04 - updating retention APIs
Rem    vkgarg      08/24/04 - adding retention period APIs
Rem    eporter     09/14/04 - Add new target bootstrap table
Rem    ramalhot    09/11/04 - get_table_object added 
Rem    jsadras     08/25/04 - bug_3356702
Rem    rzazueta    08/11/04 - Fix 3729742: commits in update_met_label_nlsid
Rem    rzkrishn    07/30/04 - review change 
Rem    ramalhot    07/29/04 - *_membership_rowset modified for assocs 
Rem    streddy     07/14/04 - Inform rep-metric layer about target additions 
Rem    gan         07/01/04 - target direct load 
Rem    ramalhot    07/13/04 - on agent port change, inform assoc pkg 
Rem    edemembe    04/13/04 - Moving partition truncation 
Rem    rzazueta    03/23/04 - Bug 3347751: Fix deadlock on mgmt_metrics
Rem    snakai      02/09/04 - report successful cleanups as info msgs 
Rem    aholser     01/06/04 - fix loader deleting ports 
Rem    rpinnama    01/09/04 - Fix 2814831 : Change the agent guid for the
Rem                           agent key on agent URL change.
Rem    skini       12/12/03 - Remove blackout windows only for expired 
Rem    ancheng     12/04/03 - bug 3091097 
Rem    streddy     11/26/03 - Include multi-agent targets in delete target 
Rem    mbhoopat    11/16/03 - Added standby target list to 
Rem    rzazueta    11/09/03 - Fix bug 3159465: Add update_met_label_nlsid proc 
Rem    rpinnama    10/31/03 - Add APIs to get/set metadata status 
Rem    streddy     09/26/03 - Added category_prop check 
Rem    streddy     09/25/03 - Added duptarget cursor end_targets_rowset 
Rem    streddy     09/23/03 - Added END_MGMT_TARGET_MEMBERSHIPS 
Rem    aholser     09/19/03 - clean upload ports 
Rem    lgloyd      09/19/03 - 
Rem    lgloyd      09/18/03 - 
Rem    lgloyd      09/17/03 - perf: improve query 
Rem    rpinnama    09/07/03 - Add purge/rollup callback procedures for purge 
Rem    edemembe    08/25/03 - Partition truncation added 
Rem    edemembe    08/20/03 - SYSAUX modification 
Rem    rzazueta    08/18/03 - Fix bug 2960829: Return resolved targets list 
Rem                           in END_MGMT_TARGETS_ROWSET 
Rem    rpinnama    08/06/03 - Add init_loader_params and update_loader_params 
Rem    edemembe    07/01/03 - Adding partition support
Rem    streddy     06/09/03 - Master-agent support
Rem    streddy     04/21/03 - Target added callback support
Rem    asawant     05/15/03 - Adding e2e JDBC tables
Rem    edemembe    03/24/03 - Removing TRUNC from rollup queries
Rem    sgrover     03/20/03 - temp put back trunc
Rem    sgrover     03/19/03 - remove variance
Rem    streddy     02/21/03 - Special case for emrep targets
Rem    streddy     02/20/03 - Added multi-agent support
Rem    rpinnama    02/03/03 - Resolve duplicate targets based on agent timezone
Rem    edemembe    01/17/03 - Fixing current metric purge logic
Rem    edemembe    01/09/03 - Moving current metric purge
Rem    sgrover     01/05/03 - fix performance log purge
Rem    snakai      01/02/03 - Add new raw table
Rem    rpinnama    12/18/02 - Show delete request and completion times
Rem    skini       11/19/02 - Fix purging of windows, dups, deleted targets
Rem    rpinnama    11/22/02 - Delay updating the emd url
Rem    rpinnama    11/21/02 - Check and resolve duplicates
Rem    rpinnama    11/13/02 - Use emd_url while marking the resolved time
Rem    rpinnama    11/12/02 - modify the signature
Rem    rpinnama    11/05/02 - Add procedures called by the loader
Rem    edemembe    08/08/02 - Adding performance logging
Rem    aholser     07/29/02 - change performance purge to 6 hrs
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Created
Rem

CREATE OR REPLACE PACKAGE BODY EMD_LOADER AS

-- NLS ID constants for host metric name 'Response' 
-- and its column 'Status
G_HOST_AVAIL_METRIC_NLSID CONSTANT VARCHAR2(30) := 'host_response_nlsid';
G_HOST_AVAIL_METRIC_COL_NLSID CONSTANT VARCHAR2(30) := 'host_status_nlsid';

G_REPO_OWNER CONSTANT VARCHAR2(30):=
             EM_CHECK.NOOP(MGMT_USER.GET_REPOSITORY_OWNER);
G_QUEUE_NAME VARCHAR2(50) := NULL;
G_DEQUEUE_IOT VARCHAR2(100) := NULL;

G_RECURSIVE_CLEANUP BOOLEAN := FALSE;

-- Package global variable that holds the handle of 
-- designator currently locked
G_DESIGNATOR_LOCK VARCHAR2(128) := NULL;

-- Package global variables for batch sizes
G_CMETRIC_BATCH_SIZE NUMBER := 500;
G_SMETRIC_BATCH_SIZE NUMBER := 500;

  -- Stores whether the timezone region for the target is is being changed;
  s_tzrgn_changed BOOLEAN := FALSE;

-- Forward Declarations
PROCEDURE PROCESS_MULTI_AGENT_TARGETS(p_emd_url_in IN VARCHAR2);
PROCEDURE GET_DUPTARGET_DETAILS(p_emd_url_in IN VARCHAR2,
                             p_duptarget_details_out OUT DUPTARGET_CURSOR);
PROCEDURE UPDATE_MET_LABEL_NLSID(p_target_type_in IN VARCHAR2);

PROCEDURE handle_target_broken_state
  (
    p_broken_target_list MGMT_BROKEN_TARGET_LIST
  );
-- Forward Declarations

PROCEDURE DBMSJOB_EXTENDED_SQL_TRACE_ON(p_value IN BOOLEAN) IS
BEGIN
  MGMT_SQLTRACE.EXTENDED_SQL_TRACE_ON(EST_ROLLUP_NAME, p_value);
END DBMSJOB_EXTENDED_SQL_TRACE_ON;

PROCEDURE VALIDATE_CALLBACK(p_callback_name IN VARCHAR2,
                            p_callback_type IN VARCHAR2,
                            p_error_msg     IN VARCHAR2);
----------------------------------------------------------------------
PROCEDURE ROLLUP IS

  v_target_guid            p_guid_list_type;
  v_last_load_time         p_date_list_type;
  v_start_timestamp        DATE DEFAULT SYSDATE;
  v_target_count           NUMBER;
  v_raw_partitioned        BOOLEAN DEFAULT FALSE;
  v_1hour_partitioned      BOOLEAN DEFAULT FALSE;
  v_1day_partitioned       BOOLEAN DEFAULT FALSE;
  v_curr_target_count       NUMBER;

  CURSOR target_cursor IS
    SELECT mt.target_guid, last_load_time
      FROM MGMT_TARGETS mt, MGMT_TARGET_ROLLUP_TIMES mtrt
      WHERE mtrt.rollup_table_name = p_metrics_1hour_table
        AND mt.target_guid = mtrt.target_guid
        AND (TRUNC(mt.last_load_time, 'HH24')
             > mtrt.rollup_timestamp);

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'Oracle Enterprise Manager.rollup',
    action_name => 'start');

  MGMT_SQLTRACE.EXTENDED_SQL_TRACE(EST_ROLLUP_NAME);

  v_target_count := 0;

  EMD_INITIALIZE();

  -- Check for partitioning.
  v_raw_partitioned := EMD_MAINTENANCE.PARTITIONS_ENABLED(p_metrics_raw_table);
  v_1hour_partitioned := 
       EMD_MAINTENANCE.PARTITIONS_ENABLED(p_metrics_1hour_table);
  v_1day_partitioned := 
       EMD_MAINTENANCE.PARTITIONS_ENABLED(p_metrics_1day_table);

  OPEN target_cursor;
  LOOP

    FETCH target_cursor BULK COLLECT INTO v_target_guid,
                                          v_last_load_time ;
    EXIT WHEN NOT v_target_guid.EXISTS(1);

    -- Loop through the targets returned from the target_cursor.
    FOR i IN v_target_guid.FIRST..v_target_guid.LAST
    LOOP
      v_curr_target_count := 0;
      v_target_count := v_target_count + 1;

      DBMS_APPLICATION_INFO.SET_ACTION(action_name => 'target ' || v_target_count);

      -- Make sure we have all the target's information
      IF ( EMD_GET_TARGET_INFO(v_target_guid(i)) ) THEN

        p_cur_target_guid := v_target_guid(i);

        -- Calculate the new rollup timestamps.
        p_cur_hour_rollup := TRUNC(v_last_load_time(i), 'HH24') - 1/24;
        p_cur_day_rollup := TRUNC(p_cur_hour_rollup, 'DD') - 1;

        -- Calculate the purge boundaries.
        -- The rollup timestamps mark the beginning of the the latest
        -- rollup and thus they count for 1 unit (hour or day).  Hence,
        -- 1 unit is substracted from the keep window.
        p_cur_hour_purge := LEAST(p_cur_day_rollup,
                p_cur_hour_rollup - ((p_cur_hour_keep-1)/24));
        p_cur_day_purge :=
                p_cur_day_rollup - (p_cur_day_keep-1);

        IF NOT (v_raw_partitioned) THEN
          -- if cur hour purge is more recent than the rollup timestamp,
          -- no point in rolling up just to purge below.
          p_cur_rollup_timestamps(p_metrics_1hour_idx) :=
            GREATEST(p_cur_rollup_timestamps(p_metrics_1hour_idx),p_cur_hour_purge) ;
          p_cur_rollup_timestamps(p_metrics_1day_idx) :=
            GREATEST(p_cur_rollup_timestamps(p_metrics_1day_idx),p_cur_day_purge) ;
        END IF;

        -- Run hourly rollups
        EMD_1HOUR_ROLLUP;
        
        -- Only purge the raw metrics table if it is not partitioned.
        IF NOT (v_raw_partitioned) THEN
           EMD_RAW_PURGE;
        END IF;

        -- Run daily rollup
        EMD_1DAY_ROLLUP;

        -- Only purge the 1hour metrics table if it is not partitioned.
        IF NOT (v_1hour_partitioned) THEN
           EMD_1HOUR_PURGE;
        END IF;

        -- Only purge the 1day metrics table if it is not partitioned.
        IF NOT (v_1day_partitioned) THEN
           EMD_1DAY_PURGE;
        END IF;

        -- Free up memory used by global variables
        p_cur_rollup_timestamps.DELETE;
        p_cur_rollup_rowids.DELETE;

      ELSE

        SELECT count(*) 
        INTO v_curr_target_count 
        FROM mgmt_targets 
        WHERE target_guid = v_target_guid(i); 
       
       -- throw exception only if the target is not deleted
        IF (v_curr_target_count = 1) THEN
          -- IMPORTANT: The user needs to be alerted of this error as
          -- it stops all rollups/purges for the target.
          MGMT_LOG.LOG_ERROR('ROLLUP', 0,
              'Fatal error fetching rollup timestamps (target_guid='
              || v_target_guid(i) || ')');
        END IF;
      END IF;

    END LOOP;

  END LOOP;

  v_target_guid.DELETE;
  v_last_load_time.DELETE;

  IF target_cursor%ISOPEN THEN
    CLOSE target_cursor;
  END IF;
  
  -- Log all rollup/purge performance stats
  MGMT_LOG.LOG_PERFORMANCE('ROLLUP', (SYSDATE-v_start_timestamp)*(24*60*60*1000),
             v_start_timestamp, 'Y', 'RECORDS', p_TotalRowsProcessed);

  MGMT_LOG.LOG_PERFORMANCE('ROLLUP 1HOUR', p_1hour_rollup_time,
             v_start_timestamp, 'Y', 'RECORDS', p_1hour_rollup_records);

  MGMT_LOG.LOG_PERFORMANCE('PURGE RAW', p_raw_purge_time,
             v_start_timestamp, 'Y', 'RECORDS', p_raw_purge_records);

  MGMT_LOG.LOG_PERFORMANCE('ROLLUP 1DAY', p_1day_rollup_time,
             v_start_timestamp, 'Y', 'RECORDS', p_1day_rollup_records);             

  MGMT_LOG.LOG_PERFORMANCE('PURGE 1HOUR', p_1hour_purge_time,
             v_start_timestamp, 'Y', 'RECORDS', p_1hour_purge_records);

  MGMT_LOG.LOG_PERFORMANCE('PURGE 1DAY', p_1day_purge_time,
             v_start_timestamp, 'Y', 'RECORDS', p_1day_purge_records);


  EMD_PURGE_ERROR_LOG;
  EMD_PURGE_PERFORMANCE_LOG;
  EMD_CURRENT_METRIC_PURGE;
 
  --
  --  Partition maintenance moved to analyze_emd_schema
  --

  DBMS_APPLICATION_INFO.SET_MODULE(null,null); 
 
EXCEPTION

  WHEN OTHERS THEN
    ROLLBACK;
    IF target_cursor%ISOPEN THEN
      CLOSE target_cursor;
    END IF;
    v_target_guid.DELETE;
    v_last_load_time.DELETE;
    -- Log this message only if Error code is not equal to 1089 (immediate shutdown in progress) 
    IF(SQLCODE != MGMT_GLOBAL.G_ORA_SHUTDOWN) THEN
      MGMT_LOG.LOG_ERROR('ROLLUP', null,
       'Fatal error (see related errors): ' ||
        SUBSTR(SQLERRM, 1, 1000));
    END IF;

END ROLLUP;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_INITIALIZE IS

BEGIN

  p_TotalRowsProcessed := 0;

  -- Get the default keep window values
  BEGIN
    SELECT NVL(TO_NUMBER(parameter_value), p_default_raw_keep)
      INTO p_curdef_raw_keep
      FROM MGMT_PARAMETERS 
      WHERE parameter_name = EMD_MAINT_UTIL.RAW_KEEP_PARAM ;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      p_curdef_raw_keep := p_default_raw_keep;
    WHEN OTHERS THEN RAISE;
  END;
  BEGIN  
    SELECT NVL(TO_NUMBER(parameter_value), p_default_hour_keep)
      INTO p_curdef_hour_keep
      FROM MGMT_PARAMETERS 
      WHERE parameter_name = EMD_MAINT_UTIL.HOUR_KEEP_PARAM ;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      p_curdef_hour_keep := p_default_hour_keep;
    WHEN OTHERS THEN RAISE;
  END;
  BEGIN  
    SELECT NVL(TO_NUMBER(parameter_value), p_default_day_keep)
      INTO p_curdef_day_keep
      FROM MGMT_PARAMETERS 
      WHERE parameter_name = EMD_MAINT_UTIL.DAY_KEEP_PARAM ;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      p_curdef_day_keep := p_default_day_keep;
    WHEN OTHERS THEN RAISE;
  END;

EXCEPTION

  WHEN OTHERS THEN
    -- Log this message only if Error code is not equal to 1089 (immediate shutdown in progress) 
    IF(SQLCODE != MGMT_GLOBAL.G_ORA_SHUTDOWN) THEN
      MGMT_LOG.LOG_ERROR('ROLLUP', null,
        'Fatal error: ' || SUBSTR(SQLERRM, 1, 1000));
    END IF;
    RAISE;

END EMD_INITIALIZE;
----------------------------------------------------------------------

----------------------------------------------------------------------
FUNCTION EMD_GET_TARGET_INFO( cur_target_guid IN RAW ) 
  RETURN BOOLEAN IS

  CURSOR timestamp_cursor IS
    SELECT rollup_table_name, rollup_timestamp, rowid
      FROM MGMT_TARGET_ROLLUP_TIMES
      WHERE target_guid = cur_target_guid AND
        rollup_table_name IN (p_metrics_1hour_table, p_metrics_1day_table)
      ORDER BY rollup_table_name;

  v_tabname     p_varchar2_256_list_type;
 
BEGIN 

  -- Clean up the previous target's information
  IF (p_cur_rollup_timestamps.COUNT > 0) THEN
    p_cur_rollup_timestamps.DELETE;
  END IF;
  IF (p_cur_rollup_rowids.COUNT > 0) THEN
    p_cur_rollup_rowids.DELETE;
  END IF;

  -- Get the rollup timestamps
  OPEN timestamp_cursor;
  FETCH timestamp_cursor BULK COLLECT INTO 
    v_tabname, p_cur_rollup_timestamps, p_cur_rollup_rowids;
  CLOSE timestamp_cursor;

  -- Check that we got the timestamps for all tables
  IF ((p_cur_rollup_timestamps.COUNT != 2)
      OR (v_tabname(p_metrics_1day_idx) != p_metrics_1day_table)
      OR (v_tabname(p_metrics_1hour_idx) != p_metrics_1hour_table)
     ) THEN
    v_tabname.DELETE;
    p_cur_rollup_timestamps.DELETE;
    p_cur_rollup_rowids.DELETE;
    RETURN FALSE;
  END IF;

  -- Not needed anymore, only used for the check above
  v_tabname.DELETE;

  -- Get any keep window values defined for the target 
  BEGIN
    SELECT NVL(TO_NUMBER(property_value), p_curdef_raw_keep)
      INTO p_cur_raw_keep
      FROM MGMT_TARGET_PROPERTIES 
      WHERE target_guid = cur_target_guid
        AND property_name = EMD_MAINT_UTIL.RAW_KEEP_PARAM ;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      p_cur_raw_keep := p_curdef_raw_keep;
    WHEN OTHERS THEN RAISE;
  END;     
  BEGIN
    SELECT NVL(TO_NUMBER(property_value), p_curdef_hour_keep)
      INTO p_cur_hour_keep
      FROM MGMT_TARGET_PROPERTIES 
      WHERE target_guid = cur_target_guid
        AND property_name = EMD_MAINT_UTIL.HOUR_KEEP_PARAM ;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      p_cur_hour_keep := p_curdef_hour_keep;
    WHEN OTHERS THEN RAISE;
  END;     
  BEGIN
    SELECT NVL(TO_NUMBER(property_value), p_curdef_day_keep)
      INTO p_cur_day_keep
      FROM MGMT_TARGET_PROPERTIES 
      WHERE target_guid = cur_target_guid
        AND property_name = EMD_MAINT_UTIL.DAY_KEEP_PARAM ;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      p_cur_day_keep := p_curdef_day_keep;
    WHEN OTHERS THEN RAISE;
  END;

  RETURN TRUE;

EXCEPTION

  WHEN OTHERS THEN
    IF timestamp_cursor%ISOPEN THEN
       CLOSE timestamp_cursor;
    END IF;
    v_tabname.DELETE;
    p_cur_rollup_timestamps.DELETE;
    p_cur_rollup_rowids.DELETE;
    -- Log this message only if Error code is not equal to 1089 (immediate shutdown in progress) 
    IF(SQLCODE != MGMT_GLOBAL.G_ORA_SHUTDOWN) THEN
      MGMT_LOG.LOG_ERROR('ROLLUP', null,
        'Fatal error getting target info: ' || SUBSTR(SQLERRM, 1, 1000));
    END IF;
    RETURN FALSE;

END EMD_GET_TARGET_INFO;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_RAW_PURGE IS

  v_rows_processed  NUMBER := 0;
  v_start_timestamp DATE DEFAULT SYSDATE;
  v_rollup_timestamp DATE ;
  v_deleted_count    NUMBER := 0 ;
BEGIN  
    
  -- p_cur_hour_rollup marks the beginning of the latest rollup hour,
  -- i.e. it already counts for 1 hour.  Hence, 1 is substracted from
  -- the keep window.
  v_rollup_timestamp :=  p_cur_hour_rollup - ((p_cur_raw_keep-1)/24) ;

  LOOP
    --Delete in batches of purge batchsize
    DELETE mgmt_metrics_raw
     WHERE target_guid          =  p_cur_target_guid 
       AND collection_timestamp <  v_rollup_timestamp
       AND ROWNUM               <= p_purge_batchsize ;

    v_deleted_count := SQL%ROWCOUNT ;

    IF v_deleted_count > 0
    THEN
      COMMIT;
      v_rows_processed := v_rows_processed + v_deleted_count ;
    END IF ;

    EXIT WHEN v_deleted_count < p_purge_batchsize ;

  END LOOP;

  p_raw_purge_time := p_raw_purge_time + 
                          (SYSDATE-v_start_timestamp)*(24*60*60*1000);

  -- Update the global row counters
  p_raw_purge_records := p_raw_purge_records + v_rows_processed;
  p_TotalRowsProcessed := p_TotalRowsProcessed + v_rows_processed;

EXCEPTION

  WHEN OTHERS THEN
    ROLLBACK;
    -- Log this message only if Error code is not equal to 1089 (immediate shutdown in progress) 
    IF(SQLCODE != MGMT_GLOBAL.G_ORA_SHUTDOWN) THEN
      MGMT_LOG.LOG_ERROR('PURGE', null,
        'Error during raw purge: ' || SUBSTR(SQLERRM, 1, 1000));
    END IF;

END EMD_RAW_PURGE;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_1HOUR_ROLLUP IS

  v_rollup_rec      p_hour_rollup_record_type;
  v_rows_processed  NUMBER := 0;
  v_start_timestamp DATE DEFAULT SYSDATE;

  CURSOR rollup_cursor ( c_target_guid RAW,
                         c_prev_rollup DATE,
                         c_cur_rollup DATE ) IS
    SELECT metric_guid, TRUNC(collection_timestamp, 'HH24'),
      key_value, COUNT(target_guid), AVG(value), MIN(value),
      MAX(value), STDDEV(value)
    FROM MGMT_METRICS_RAW
    WHERE target_guid = c_target_guid
      AND collection_timestamp >= (c_prev_rollup+(1/24))
      AND collection_timestamp < (c_cur_rollup+(1/24))
      AND value IS NOT NULL
    GROUP BY target_guid, metric_guid, key_value,
      TRUNC(collection_timestamp, 'HH24');

BEGIN

  OPEN rollup_cursor( p_cur_target_guid,
                      p_cur_rollup_timestamps(p_metrics_1hour_idx),
                      p_cur_hour_rollup );
  LOOP

    -- Process batchsize number of rows per commit.
    FETCH rollup_cursor BULK COLLECT INTO v_rollup_rec.metric_guid,
                                          v_rollup_rec.rollup_time,
                                          v_rollup_rec.key_val,
                                          v_rollup_rec.sample_count,
                                          v_rollup_rec.avg_val,
                                          v_rollup_rec.min_val,
                                          v_rollup_rec.max_val,
                                          v_rollup_rec.stddev_val
                                          LIMIT p_rollup_batchsize;

    -- Exit if no rollup records are generated.
    EXIT WHEN NOT v_rollup_rec.rollup_time.EXISTS(1);
    v_rows_processed := v_rows_processed + v_rollup_rec.metric_guid.COUNT;

    -- Bulk inserts
    FORALL i IN v_rollup_rec.rollup_time.FIRST..v_rollup_rec.rollup_time.LAST
      INSERT INTO MGMT_METRICS_1HOUR
      (
        target_guid, metric_guid, rollup_timestamp,
        key_value, sample_count, value_average, value_minimum,
        value_maximum, value_sdev
      )
      VALUES
      (
        p_cur_target_guid, v_rollup_rec.metric_guid(i),
        v_rollup_rec.rollup_time(i),
        v_rollup_rec.key_val(i),
        v_rollup_rec.sample_count(i), v_rollup_rec.avg_val(i),
        v_rollup_rec.min_val(i), v_rollup_rec.max_val(i),
        v_rollup_rec.stddev_val(i)
      );

    COMMIT;

    -- Free temporary tables
    v_rollup_rec.metric_guid.DELETE;
    v_rollup_rec.rollup_time.DELETE;
    v_rollup_rec.key_val.DELETE;
    v_rollup_rec.sample_count.DELETE;
    v_rollup_rec.avg_val.DELETE;
    v_rollup_rec.min_val.DELETE;
    v_rollup_rec.max_val.DELETE;
    v_rollup_rec.stddev_val.DELETE;

    -- Check cursor status here because cursor may return not found on a
    -- bulk fetch, even though records were retrieved.
    EXIT WHEN rollup_cursor%NOTFOUND;

  END LOOP;

  -- Update the rollup timestamp
  UPDATE MGMT_TARGET_ROLLUP_TIMES
    SET rollup_timestamp = p_cur_hour_rollup
    WHERE rowid = p_cur_rollup_rowids(p_metrics_1hour_idx);

  COMMIT;

  IF rollup_cursor%ISOPEN THEN
    CLOSE rollup_cursor;
  END IF;

  p_1hour_rollup_time := p_1hour_rollup_time + 
                          (SYSDATE-v_start_timestamp)*(24*60*60*1000);
  
  p_1hour_rollup_records := p_1hour_rollup_records + v_rows_processed;
  p_TotalRowsProcessed := p_TotalRowsProcessed + v_rows_processed;

EXCEPTION

  WHEN DUP_VAL_ON_INDEX THEN
    -- Partial rollup detected.

    -- Rollback any partial work performed in this procedure
    ROLLBACK;

    -- Free all resources used
    IF rollup_cursor%ISOPEN THEN
      CLOSE rollup_cursor;
    END IF;
    v_rollup_rec.metric_guid.DELETE;
    v_rollup_rec.rollup_time.DELETE;
    v_rollup_rec.key_val.DELETE;
    v_rollup_rec.sample_count.DELETE;
    v_rollup_rec.avg_val.DELETE;
    v_rollup_rec.min_val.DELETE;
    v_rollup_rec.max_val.DELETE;
    v_rollup_rec.stddev_val.DELETE;

    -- Cleanup all partial work.  If the cleanup fails, update the rollup
    -- timestamp so that the rollup doesn't get stuck forever.
    IF (EMD_1HOUR_CLEANUP = TRUE) THEN
      MGMT_LOG.LOG_ERROR('ROLLUP', null,
        'Successfully cleaned up partial 1hour rollup (target=' ||
        p_cur_target_guid || ')', 'MGMT', NULL, NULL, NULL, MGMT_GLOBAL.G_INFO);
    ELSE
      MGMT_LOG.LOG_ERROR('ROLLUP', null,
        'Unable to cleanup the partial 1hour rollup (target=' ||
        p_cur_target_guid || ')');
      BEGIN
        UPDATE MGMT_TARGET_ROLLUP_TIMES
          SET rollup_timestamp = p_cur_hour_rollup
          WHERE rowid = p_cur_rollup_rowids(p_metrics_1hour_idx);
        COMMIT;
        MGMT_LOG.LOG_ERROR('ROLLUP', null,
          '1hour rollups for the period [' ||
          TO_CHAR(p_cur_rollup_timestamps(p_metrics_1hour_idx),
          'MM:DD:YYYY HH24:MI') || ', ' ||
          TO_CHAR(p_cur_hour_rollup, 'MM:DD:YYYY HH24:MI') || ']
          cannot be performed (target=' || p_cur_target_guid || ')');
      EXCEPTION
        WHEN OTHERS THEN
          -- Log this message only if Error code is not equal to 1089 (immediate shutdown in progress) 
          IF(SQLCODE != MGMT_GLOBAL.G_ORA_SHUTDOWN) THEN
            MGMT_LOG.LOG_ERROR('ROLLUP', null,
              'Fatal Error: Unable to set the timestamp (target=' ||
               p_cur_target_guid || '). Error: ' || SUBSTR(SQLERRM, 1, 1000));
          END IF;
          RAISE;
      END;
    END IF;

  WHEN OTHERS THEN
    -- Assumes permanent error and attempts to update the rollup timestamp,
    -- thus losing the rollup period in question.
    -- This should be improved upon, we need to determine if the error is
    -- permanent or transient and only update the timestamp if it's permanent.

    -- Rollback any partial work
    ROLLBACK;

    -- Free all resources used
    IF rollup_cursor%ISOPEN THEN
      CLOSE rollup_cursor;
    END IF;
    v_rollup_rec.metric_guid.DELETE;
    v_rollup_rec.rollup_time.DELETE;
    v_rollup_rec.key_val.DELETE;
    v_rollup_rec.sample_count.DELETE;
    v_rollup_rec.avg_val.DELETE;
    v_rollup_rec.min_val.DELETE;
    v_rollup_rec.max_val.DELETE;
    v_rollup_rec.stddev_val.DELETE;

    -- Log this message only if Error code is not equal to 1089 (immediate shutdown in progress) 
    IF(SQLCODE != MGMT_GLOBAL.G_ORA_SHUTDOWN) THEN
      MGMT_LOG.LOG_ERROR('ROLLUP', null,
        'The following unrecoverable error occurred while performing the' ||
        ' 1hour rollup (target=' || p_cur_target_guid || ').  Attempting to' ||
        ' skip over rollup period [' ||
        TO_CHAR(p_cur_rollup_timestamps(p_metrics_1hour_idx),
        'MM:DD:YYYY HH24:MI') || ', ' ||
        TO_CHAR(p_cur_hour_rollup,
        'MM:DD:YYYY HH24:MI') || '].  Error: ' || SUBSTR(SQLERRM, 1, 1000));
    END IF;

    BEGIN
      UPDATE MGMT_TARGET_ROLLUP_TIMES
        SET rollup_timestamp = p_cur_hour_rollup
        WHERE rowid = p_cur_rollup_rowids(p_metrics_1hour_idx);
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        -- Log this message only if Error code is not equal to 1089 (immediate shutdown in progress) 
        IF(SQLCODE != MGMT_GLOBAL.G_ORA_SHUTDOWN) THEN
          MGMT_LOG.LOG_ERROR('ROLLUP', null,
            'Fatal Error: Unable to set the 1hour rollup timestamp (target=' ||
             p_cur_target_guid || '). Error: ' || SUBSTR(SQLERRM, 1, 1000));
        END IF; 
        RAISE;
    END;

END EMD_1HOUR_ROLLUP;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_1HOUR_PURGE IS

  v_purge_rowid     p_rowid_list_type;
  v_rows_processed  NUMBER := 0;
  v_start_timestamp DATE DEFAULT SYSDATE;
  v_deleted_count   NUMBER := 0 ;

BEGIN

  LOOP
    --Delete in batches of purge batchsize
    DELETE mgmt_metrics_1hour
     WHERE target_guid      =  p_cur_target_guid
       AND rollup_timestamp <  p_cur_hour_purge
       AND ROWNUM           <= p_purge_batchsize ;

    v_deleted_count := SQL%ROWCOUNT ;

    IF v_deleted_count > 0
    THEN
      COMMIT;
      v_rows_processed := v_rows_processed + v_deleted_count ;
    END IF ;

    EXIT WHEN v_deleted_count < p_purge_batchsize ;  

  END LOOP;

  p_1hour_purge_time := p_1hour_purge_time + 
                          (SYSDATE-v_start_timestamp)*(24*60*60*1000);
  
  p_1hour_purge_records := p_1hour_purge_records + v_rows_processed;
  p_TotalRowsProcessed := p_TotalRowsProcessed + v_rows_processed;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    -- Log this message only if Error code is not equal to 1089 (immediate shutdown in progress) 
    IF(SQLCODE != MGMT_GLOBAL.G_ORA_SHUTDOWN) THEN
      MGMT_LOG.LOG_ERROR('PURGE', null,
      'Error during 1hour purge: ' || SUBSTR(SQLERRM, 1, 1100));
    END IF;
END EMD_1HOUR_PURGE;
----------------------------------------------------------------------

----------------------------------------------------------------------
FUNCTION EMD_1HOUR_CLEANUP RETURN BOOLEAN IS

  v_cleanup_rowid   p_rowid_list_type;
  v_rows_processed  NUMBER := 0;
  v_start_timestamp DATE DEFAULT SYSDATE;

  CURSOR cleanup_cursor ( c_target_guid RAW,
                          c_last_good_rollup DATE ) IS
    SELECT rowid
      FROM MGMT_METRICS_1HOUR
      WHERE target_guid = c_target_guid
        AND rollup_timestamp > c_last_good_rollup;

BEGIN

  OPEN cleanup_cursor( p_cur_target_guid,
                       p_cur_rollup_timestamps(p_metrics_1hour_idx) );
  LOOP

    FETCH cleanup_cursor BULK COLLECT INTO v_cleanup_rowid 
      LIMIT p_purge_batchsize;

    EXIT WHEN v_cleanup_rowid.COUNT <= 0;
 
    -- Bulk delete by rowid
    FORALL i IN v_cleanup_rowid.FIRST..v_cleanup_rowid.LAST
      DELETE FROM MGMT_METRICS_1HOUR 
        WHERE rowid = v_cleanup_rowid(i);

    COMMIT;

    -- Keep track of the number of deleted rows
    v_rows_processed := v_rows_processed + v_cleanup_rowid.COUNT;

    -- Clear the rowid list
    v_cleanup_rowid.DELETE;

  END LOOP;

  IF cleanup_cursor%ISOPEN THEN
    CLOSE cleanup_cursor;
  END IF;

  RETURN TRUE;

EXCEPTION

  WHEN NO_DATA_FOUND THEN
    ROLLBACK;
    IF cleanup_cursor%ISOPEN THEN
      CLOSE cleanup_cursor;
    END IF;
    v_cleanup_rowid.DELETE;
    RETURN TRUE;
  
  WHEN OTHERS THEN
    ROLLBACK;
    IF cleanup_cursor%ISOPEN THEN
      CLOSE cleanup_cursor;
    END IF;
    v_cleanup_rowid.DELETE;
    -- Log this message only if Error code is not equal to 1089 (immediate shutdown in progress) 
    IF(SQLCODE != MGMT_GLOBAL.G_ORA_SHUTDOWN) THEN
      MGMT_LOG.LOG_ERROR('ROLLUP', null,
        'Error during 1hour cleanup: ' || SUBSTR(SQLERRM, 1, 1100));
    END IF;
    RETURN FALSE;

END EMD_1HOUR_CLEANUP;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_1DAY_ROLLUP IS

  v_rollup_rec      p_day_rollup_record_type;
  v_rows_processed  NUMBER := 0;
  v_start_timestamp DATE DEFAULT SYSDATE;

  CURSOR rollup_cursor( c_target_guid RAW,
                        c_prev_rollup DATE,
                        c_cur_rollup DATE) IS
    SELECT metric_guid, 
      TRUNC(rollup_timestamp, 'DD'),
      key_value, 
      SUM(sample_count), 
      MIN(value_minimum),
      MAX(value_maximum),
      SUM(sample_count * value_average),
      SUM((POWER(value_sdev, 2) * (sample_count-1)) + 
          (sample_count * POWER(value_average, 2)))
    FROM MGMT_METRICS_1HOUR
    WHERE target_guid = c_target_guid 
      AND rollup_timestamp >= (c_prev_rollup+1)
      AND rollup_timestamp < (c_cur_rollup+1)
    GROUP BY target_guid, metric_guid, key_value,
      TRUNC(rollup_timestamp, 'DD');

BEGIN

  OPEN rollup_cursor( p_cur_target_guid, 
                      p_cur_rollup_timestamps(p_metrics_1day_idx),
                      p_cur_day_rollup ); 
  LOOP

    -- Process batchsize number of rows per commit.
    FETCH rollup_cursor BULK COLLECT INTO v_rollup_rec.metric_guid,
                                          v_rollup_rec.rollup_time,
                                          v_rollup_rec.key_val,
                                          v_rollup_rec.sample_count,
                                          v_rollup_rec.min_val,
                                          v_rollup_rec.max_val,
                                          v_rollup_rec.sumx_val,
                                          v_rollup_rec.sumx2_val
                                          LIMIT p_rollup_batchsize;

    -- Exit loop if no rollup records are generated.
    EXIT WHEN NOT v_rollup_rec.rollup_time.EXISTS(1);
    v_rows_processed := v_rows_processed + v_rollup_rec.metric_guid.COUNT;

    -- Bulk inserts
    FORALL i IN v_rollup_rec.rollup_time.FIRST..v_rollup_rec.rollup_time.LAST
      INSERT INTO MGMT_METRICS_1DAY
      (
        target_guid, metric_guid, rollup_timestamp,
        key_value, sample_count, value_average, value_minimum,
        value_maximum, value_sdev
      )
      VALUES
      ( 
        p_cur_target_guid, v_rollup_rec.metric_guid(i),
        v_rollup_rec.rollup_time(i),
        v_rollup_rec.key_val(i), 
        v_rollup_rec.sample_count(i), 
        DECODE(v_rollup_rec.sample_count(i), 
               0, 0,
               v_rollup_rec.sumx_val(i)/v_rollup_rec.sample_count(i)),
        v_rollup_rec.min_val(i), 
        v_rollup_rec.max_val(i),  
        DECODE(v_rollup_rec.sample_count(i), 
               0, 0,
               1, 0,
               DECODE( 
                SIGN(v_rollup_rec.sample_count(i) * v_rollup_rec.sumx2_val(i)  - 
                 POWER(v_rollup_rec.sumx_val(i),2)), -1,0,
                 SQRT((v_rollup_rec.sample_count(i) * v_rollup_rec.sumx2_val(i) - 
                        POWER(v_rollup_rec.sumx_val(i),2))
                       /(v_rollup_rec.sample_count(i) * (v_rollup_rec.sample_count(i) - 1))
                      )
                     )
               )
      );

    COMMIT;

    -- Free temporary tables
    v_rollup_rec.metric_guid.DELETE;
    v_rollup_rec.rollup_time.DELETE;
    v_rollup_rec.key_val.DELETE;
    v_rollup_rec.sample_count.DELETE;
    v_rollup_rec.min_val.DELETE;
    v_rollup_rec.max_val.DELETE;
    v_rollup_rec.sumx_val.DELETE;
    v_rollup_rec.sumx2_val.DELETE;

    -- Check cursor status here because cursor may return not found on a
    -- bulk fetch, even though records were retrieved.
    EXIT WHEN rollup_cursor%NOTFOUND;

  END LOOP; 

  -- Update the rollup timestamp
  UPDATE MGMT_TARGET_ROLLUP_TIMES
    SET rollup_timestamp = p_cur_day_rollup
    WHERE rowid = p_cur_rollup_rowids(p_metrics_1day_idx);

  COMMIT;

  IF rollup_cursor%ISOPEN THEN
    CLOSE rollup_cursor;
  END IF;

  p_1day_rollup_time := p_1day_rollup_time + 
                          (SYSDATE-v_start_timestamp)*(24*60*60*1000);
  
  p_1day_rollup_records := p_1day_rollup_records + v_rows_processed;
  
  -- Update the global row counters
  p_TotalRowsProcessed := p_TotalRowsProcessed + v_rows_processed;

EXCEPTION

  WHEN DUP_VAL_ON_INDEX THEN
    -- Partial rollup detected.
 
   -- Rollback any partial work
    ROLLBACK;

    -- Free all resources used
    IF rollup_cursor%ISOPEN THEN
      CLOSE rollup_cursor;
    END IF;
    v_rollup_rec.metric_guid.DELETE;
    v_rollup_rec.rollup_time.DELETE;
    v_rollup_rec.key_val.DELETE;
    v_rollup_rec.sample_count.DELETE;
    v_rollup_rec.min_val.DELETE;
    v_rollup_rec.max_val.DELETE;
    v_rollup_rec.sumx_val.DELETE;
    v_rollup_rec.sumx2_val.DELETE;

    -- Cleanup all partial work.  If the cleanup fails, update the rollup
    -- timestamp so that the rollup doesn't get stuck forever.
    IF (EMD_1DAY_CLEANUP = TRUE) THEN
      MGMT_LOG.LOG_ERROR('ROLLUP', null,
        'Successfully cleaned up partial 1day rollup (target=' ||
        p_cur_target_guid || ')', 'MGMT', NULL, NULL, NULL, MGMT_GLOBAL.G_INFO);
    ELSE
      MGMT_LOG.LOG_ERROR('ROLLUP', null,
        'Unable to cleanup the partial 1day rollup (target=' ||
        p_cur_target_guid || ')');
      BEGIN
        UPDATE MGMT_TARGET_ROLLUP_TIMES
          SET rollup_timestamp = p_cur_day_rollup
          WHERE rowid = p_cur_rollup_rowids(p_metrics_1day_idx);
        COMMIT;
        MGMT_LOG.LOG_ERROR('ROLLUP', null,
          '1day rollups for the period [' ||
          TO_CHAR(p_cur_rollup_timestamps(p_metrics_1day_idx),
          'MM:DD:YYYY') || ', ' ||
          TO_CHAR(p_cur_day_rollup, 'MM:DD:YYYY') || '] 
          cannot be performed (target=' || p_cur_target_guid || ')');
      EXCEPTION
        WHEN OTHERS THEN
          -- Log this message only if Error code is not equal to 1089 (immediate shutdown in progress) 
          IF(SQLCODE != MGMT_GLOBAL.G_ORA_SHUTDOWN) THEN
            MGMT_LOG.LOG_ERROR('ROLLUP', null,
              'Fatal Error: Unable to set the 1day rollup timestamp (target=' ||
               p_cur_target_guid || '). Error: ' || SUBSTR(SQLERRM, 1, 1000));
          END IF;
          RAISE;
      END;
    END IF;

  WHEN OTHERS THEN
    -- Assumes permanent error and attempts to update the rollup timestamp,
    -- thus losing the rollup period in question.
    -- This should be improved upon, we need to determine if the error is
    -- permanent or transient and only update the timestamp if it's permanent.
   
    -- Rollback any partial work
    ROLLBACK;

    -- Free all resources used
    IF rollup_cursor%ISOPEN THEN
      CLOSE rollup_cursor;
    END IF;
    v_rollup_rec.metric_guid.DELETE;
    v_rollup_rec.rollup_time.DELETE;
    v_rollup_rec.key_val.DELETE;
    v_rollup_rec.sample_count.DELETE;
    v_rollup_rec.min_val.DELETE;
    v_rollup_rec.max_val.DELETE;
    v_rollup_rec.sumx_val.DELETE;
    v_rollup_rec.sumx2_val.DELETE;

    -- Log this message only if Error code is not equal to 1089 (immediate shutdown in progress) 
    IF(SQLCODE != MGMT_GLOBAL.G_ORA_SHUTDOWN) THEN
      MGMT_LOG.LOG_ERROR('ROLLUP', null,
        'The following unrecoverable error occurred while performing the' ||
        ' 1day rollup  for (target=' || p_cur_target_guid || ').  Attempting to' ||
        ' skip over rollup period [' || 
        TO_CHAR(p_cur_rollup_timestamps(p_metrics_1day_idx),
        'MM:DD:YYYY') || ', ' ||
        TO_CHAR(p_cur_day_rollup,
        'MM:DD:YYYY') || '].  Error: ' || SUBSTR(SQLERRM, 1, 1000));
    END IF; 

    BEGIN
      UPDATE MGMT_TARGET_ROLLUP_TIMES
        SET rollup_timestamp = p_cur_day_rollup
        WHERE rowid = p_cur_rollup_rowids(p_metrics_1day_idx);
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        -- Log this message only if Error code is not equal to 1089 (immediate shutdown in progress) 
        IF(SQLCODE != MGMT_GLOBAL.G_ORA_SHUTDOWN) THEN
          MGMT_LOG.LOG_ERROR('ROLLUP', null,
            'Fatal Error: Unable to set the 1day rollup timestamp (target=' ||
            p_cur_target_guid || '). Error: ' || SUBSTR(SQLERRM, 1, 1900));
        END IF;
        RAISE;
    END;

END EMD_1DAY_ROLLUP;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_1DAY_PURGE IS

  v_rows_processed  NUMBER := 0;
  v_start_timestamp DATE DEFAULT SYSDATE;
  v_deleted_count   NUMBER := 0 ;
BEGIN

  LOOP
    -- Purge in batches of p_purge_batch_size
    DELETE mgmt_metrics_1day
     WHERE target_guid      = p_cur_target_guid
       AND rollup_timestamp <  p_cur_day_purge
       AND ROWNUM           <= p_purge_batchsize ;

    v_deleted_count := SQL%ROWCOUNT ;

    IF v_deleted_count > 0
    THEN
      COMMIT ;
      v_rows_processed := v_rows_processed + v_deleted_count ;
    END IF ;

    EXIT WHEN v_deleted_count < p_purge_batchsize ;

  END LOOP;

  p_1day_purge_time := p_1day_purge_time + 
                          (SYSDATE-v_start_timestamp)*(24*60*60*1000);
  
  p_1day_purge_records := p_1day_purge_records + v_rows_processed;
  p_TotalRowsProcessed := p_TotalRowsProcessed + v_rows_processed;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    -- Log this message only if Error code is not equal to 1089 (immediate shutdown in progress) 
    IF(SQLCODE != MGMT_GLOBAL.G_ORA_SHUTDOWN) THEN
      MGMT_LOG.LOG_ERROR('PURGE', null,
        'Error during 1day purge: ' || SUBSTR(SQLERRM, 1, 1000));
    END IF;
END EMD_1DAY_PURGE;
----------------------------------------------------------------------

----------------------------------------------------------------------
FUNCTION EMD_1DAY_CLEANUP RETURN BOOLEAN IS

  v_cleanup_rowid   p_rowid_list_type;
  v_rows_processed  NUMBER := 0;
  v_start_timestamp DATE DEFAULT SYSDATE;

  CURSOR cleanup_cursor ( c_target_guid RAW,
                          c_last_good_rollup DATE ) IS
    SELECT rowid
      FROM MGMT_METRICS_1DAY
      WHERE target_guid = c_target_guid
        AND rollup_timestamp > c_last_good_rollup;

BEGIN

  OPEN cleanup_cursor( p_cur_target_guid, 
                       p_cur_rollup_timestamps(p_metrics_1day_idx) );
  LOOP

    FETCH cleanup_cursor BULK COLLECT INTO v_cleanup_rowid 
      LIMIT p_purge_batchsize;

    EXIT WHEN v_cleanup_rowid.COUNT <= 0;
 
    -- Bulk delete by rowid
    FORALL i IN v_cleanup_rowid.FIRST..v_cleanup_rowid.LAST
      DELETE FROM MGMT_METRICS_1DAY
        WHERE rowid = v_cleanup_rowid(i);

    COMMIT;

    -- Keep track of the number of deleted rows
    v_rows_processed := v_rows_processed + v_cleanup_rowid.COUNT;

    -- Clear the rowid list
    v_cleanup_rowid.DELETE;

  END LOOP;

  IF cleanup_cursor%ISOPEN THEN
    CLOSE cleanup_cursor;
  END IF;

  RETURN TRUE;

EXCEPTION

  WHEN NO_DATA_FOUND THEN
    ROLLBACK;
    IF cleanup_cursor%ISOPEN THEN
      CLOSE cleanup_cursor;
    END IF;
    v_cleanup_rowid.DELETE;
    RETURN TRUE;
  
  WHEN OTHERS THEN
    ROLLBACK;
    IF cleanup_cursor%ISOPEN THEN
      CLOSE cleanup_cursor;
    END IF;
    v_cleanup_rowid.DELETE;

    -- Log this message only if Error code is not equal to 1089 (immediate shutdown in progress) 
    IF(SQLCODE != MGMT_GLOBAL.G_ORA_SHUTDOWN) THEN
      MGMT_LOG.LOG_ERROR('ROLLUP', null,
        'Error during 1day cleanup: ' || SUBSTR(SQLERRM, 1, 1100));
    END IF;

    RETURN FALSE;

END EMD_1DAY_CLEANUP;
----------------------------------------------------------------------

--
--  The goal of the EMD_CURRENT_METRIC_PURGE procedure is to quickly, politely,
--  and efficiently clean the MGMT_CURRENT_METRICS table of any metric data
--  that doesn't not represent the latest collection for that metric.  This
--  cleanup is better done here than in a trigger during data load due to the
--  expensive nature of delete transactions.
--
PROCEDURE EMD_CURRENT_METRIC_PURGE IS  
 
 -- performance data is stored in emdrep/rollup/perf 
 CURSOR max_column_cursor IS
  WITH
  MET as (SELECT DISTINCT metric_guid metric_guid
            FROM mgmt_metrics
           WHERE keys_from_mult_colls = 0 ),
  CM as (SELECT target_guid, metric_guid, max(collection_timestamp) max_time
           FROM MGMT_CURRENT_METRICS
          GROUP BY target_guid, metric_guid
         HAVING max(collection_timestamp) != min(collection_timestamp))
  SELECT cm.target_guid target_guid, cm.metric_guid metric_guid,
         cm. max_time max_time
    FROM met, cm
   WHERE cm.metric_guid = met.metric_guid ;

  --
  -- The update skip locked will get and lock rows which are not locked
  -- by loader. However since there is a commit, the locks will be released
  -- Since this current metric purge for a given target/metric and only
  -- those key values which do not have max timestamp, the number of records
  -- will be less than purge_batch_size of 2000, if causes deadlocks
  -- with loader, purge_batchsize has to be increased.
  --
  CURSOR column_rowid_cursor (targetGUID RAW, metricGUID RAW,
                  maxTimestamp DATE) IS
    SELECT ROWID 
      FROM MGMT_CURRENT_METRICS
     WHERE target_guid = targetGUID
       AND metric_guid = metricGUID
       AND collection_timestamp < maxTimestamp
       ORDER BY ROWID
       FOR UPDATE SKIP LOCKED ;


  v_rowIds p_RowIdList;
  v_targetGUIDS p_TargetGUIDList;
  v_metricGUIDS p_MetricGUIDList;
  v_keyValues   p_keyValueList;
  v_maxTimestamps p_MaxTimestampList;
  i BINARY_INTEGER;
  z BINARY_INTEGER;
  l_purge_batchsize NUMBER;
BEGIN
  -- DBMS_OUTPUT.PUT_LINE('Start current metric purge: ' ||
  --                                  to_char(SYSDATE, 'hh24:mi:ss'));
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'Oracle Enterprise Manager.current metric purge',
    action_name => 'start');

  -- This would return the purge batch size value set in mgmt_parametrs
  -- If no value is set in mgmt_parameters then it would return value from p_purge_batchsize.
  l_purge_batchsize := get_purge_batchsize ;

  -- Fetch the raw data maximum load times for the standard metrics grouped
  -- by metric column.
  OPEN max_column_cursor;
  LOOP
    FETCH max_column_cursor 
     BULK COLLECT INTO v_targetGUIDS, v_metricGUIDS, v_maxTimestamps
    LIMIT p_fetch_batchsize ;

    EXIT WHEN v_targetGUIDS.COUNT <= 0  ;

    --
    --  If there are any targets with metrics collected, walk through them
    --  deleting any records older than the max (current) metric collection
    --  timestamp in chunks based on the global purge batch size.
    --
    -- We did not do a simple delete to avoid getting into deadlocks with 
    -- loader loading current data.
    --
    FOR i IN v_targetGUIDS.FIRST..v_targetGUIDS.LAST LOOP
      OPEN column_rowid_cursor(v_targetGUIDS(i), v_metricGUIDS(i),
                                   v_maxTimestamps(i));
      FETCH column_rowid_cursor BULK COLLECT INTO v_rowIds LIMIT l_purge_batchsize;
      WHILE v_rowIds.COUNT > 0 LOOP
        -- Bulk delete all rows by id.
        FORALL z IN v_rowIds.FIRST..v_rowIds.LAST
          DELETE FROM MGMT_CURRENT_METRICS WHERE rowid = v_rowIds(z);
        COMMIT;
        EXIT WHEN v_rowids.COUNT < l_purge_batchsize ; 
        v_rowIds.DELETE;  -- Clear the temporary row id list.
        -- close and reopen to avoid fetch out of sequence
        CLOSE column_rowid_cursor;
        OPEN column_rowid_cursor(v_targetGUIDS(i), v_metricGUIDS(i),
                                     v_maxTimestamps(i));
        FETCH column_rowid_cursor BULK COLLECT INTO v_rowIds LIMIT l_purge_batchsize;        
      END LOOP;
      CLOSE column_rowid_cursor;
    END LOOP;
    COMMIT;
    
    IF column_rowid_cursor%ISOPEN THEN
      CLOSE column_rowid_cursor;
    END IF;

    EXIT WHEN v_targetGUIDS.COUNT < p_fetch_batchsize ;

  END LOOP ;
  CLOSE max_column_cursor;

  -- Clear the lists
  v_rowIds.DELETE;
  v_targetGUIDS.DELETE;
  v_metricGUIDS.DELETE;
  v_keyValues.DELETE;
  v_maxTimestamps.DELETE;

  DBMS_APPLICATION_INFO.SET_MODULE(null,null); 

EXCEPTION
  WHEN OTHERS THEN
    IF (max_column_cursor%ISOPEN) THEN
      CLOSE max_column_cursor;
    END IF;
    IF (column_rowid_cursor%ISOPEN) THEN
      CLOSE column_rowid_cursor;
    END IF;
    p_ErrMsg := SUBSTR(SQLERRM, 1, 1000);
    DBMS_APPLICATION_INFO.READ_MODULE(p_Module,p_Action);
    -- Log this message only if Error code is not equal to 1089 (immediate shutdown in progress) 
    IF(SQLCODE != MGMT_GLOBAL.G_ORA_SHUTDOWN) THEN
      MGMT_LOG.LOG_ERROR('PURGE', null, 'Error during ' || p_Module || ' : ' ||
         p_ErrMsg);
    END IF;
END EMD_CURRENT_METRIC_PURGE;


PROCEDURE EMD_PURGE_ERROR_LOG IS
  CURSOR rowid_cursor (maxTime DATE) IS
    SELECT rowid
    FROM MGMT_SYSTEM_ERROR_LOG m
    WHERE occur_date < maxTime
    ORDER BY rowid;

  v_rowIds p_RowIdList;
  v_maxTime DATE;
  i BINARY_INTEGER;
  v_rows_processed  NUMBER := 0;  
  v_start_timestamp DATE DEFAULT SYSDATE;  
  l_purge_batchsize NUMBER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Start purge error log: ' || to_char(SYSDATE, 'hh24:mi:ss'));
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'Oracle Enterprise Manager.purge system error log',
    action_name => 'start');

  -- This would return the purge batch size value set in mgmt_parametrs
  -- If no value is set in mgmt_parameters then it would return value from p_purge_batchsize.
  l_purge_batchsize := get_purge_batchsize ;

  SELECT MAX(occur_date) INTO v_maxTime
              FROM MGMT_SYSTEM_ERROR_LOG;
  -- This sets the purge window 7 days back from the latest entry (not necessarily the current time)
  v_maxTime := (v_maxTime - (p_LogPurgeWindow/24));
  IF SQL%FOUND THEN
    OPEN rowid_cursor(v_maxTime);
    LOOP
      FETCH rowid_cursor BULK COLLECT INTO v_rowIds LIMIT l_purge_batchsize;
      EXIT WHEN NOT v_rowIds.EXISTS(1);  -- Exit when nothing to purge

      FORALL i IN v_rowIds.FIRST..v_rowIds.LAST -- Bulk delete all rows by id.
        DELETE FROM MGMT_SYSTEM_ERROR_LOG WHERE rowid = v_rowIds(i);

      COMMIT;
      -- Keep track of the number of deleted rows
      v_rows_processed := v_rows_processed + v_rowIds.COUNT;
      
      v_rowIds.DELETE;  -- Clear the temporary row id list.
      EXIT WHEN rowid_cursor%NOTFOUND;
    END LOOP;
    CLOSE rowid_cursor;
  END IF; -- IF SQL%FOUND
  MGMT_LOG.LOG_PERFORMANCE('PURGE ERROR LOG', 
             (SYSDATE-v_start_timestamp)*(24*60*60*1000),
             v_start_timestamp, 'Y', 'RECORDS', v_rows_processed);  
  DBMS_OUTPUT.PUT_LINE('End purge error log: ' || to_char(SYSDATE, 'hh24:mi:ss'));

  DBMS_APPLICATION_INFO.SET_MODULE(null,null); 

EXCEPTION
  WHEN OTHERS THEN
      IF (rowid_cursor%ISOPEN) THEN
        CLOSE rowid_cursor;
      END IF;
    p_ErrMsg := SUBSTR(SQLERRM, 1, 2048);
    DBMS_APPLICATION_INFO.READ_MODULE(p_Module,p_Action);
    -- Log this message only if Error code is not equal to 1089 (immediate shutdown in progress) 
    IF(SQLCODE != MGMT_GLOBAL.G_ORA_SHUTDOWN) THEN
      MGMT_LOG.LOG_ERROR('PURGE', null, 'Error during ' || p_Module || ' : ' ||
         p_ErrMsg,p_Action);
    END IF;
END EMD_PURGE_ERROR_LOG;


PROCEDURE EMD_PURGE_PERFORMANCE_LOG IS

  v_maxTime DATE;
  v_rows_processed  NUMBER := 0;  
  v_start_timestamp DATE DEFAULT SYSDATE;
  l_purge_batchsize NUMBER;  
  l_delete_count    NUMBER := 0 ;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Start purge performance log: ' || to_char(SYSDATE, 'hh24:mi:ss'));
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'Oracle Enterprise Manager.purge system performance log',
    action_name => 'start');

  -- This would return the purge batch size value set in mgmt_parametrs
  -- If no value is set in mgmt_parameters then it would return value from p_purge_batchsize.
  l_purge_batchsize := get_purge_batchsize ;

  -- save only last six hours worth of performance data
  v_maxTime := SYSDATE - (1/4);

  LOOP
    DELETE MGMT_SYSTEM_PERFORMANCE_LOG
     WHERE time   <  v_maxTime
       AND ROWNUM <= l_purge_batchsize ;

    l_delete_count := SQL%ROWCOUNT ;

    IF l_delete_count > 0 
    THEN
      v_rows_processed := v_rows_processed + l_delete_count ;
      COMMIT;
    END IF ;

    EXIT WHEN l_delete_count < l_purge_batchsize ;

  END LOOP;

  MGMT_LOG.LOG_PERFORMANCE('PURGE PERFORMANCE LOG', 
             (SYSDATE-v_start_timestamp)*(24*60*60*1000),
             v_start_timestamp, 'Y', 'RECORDS', v_rows_processed);
  DBMS_APPLICATION_INFO.SET_MODULE(null,null); 

EXCEPTION
  WHEN OTHERS THEN
    p_ErrMsg := SUBSTR(SQLERRM, 1, 2048);
    DBMS_APPLICATION_INFO.READ_MODULE(p_Module,p_Action);
    -- Log this message only if Error code is not equal to 1089 (immediate shutdown in progress) 
    IF(SQLCODE != MGMT_GLOBAL.G_ORA_SHUTDOWN) THEN
      MGMT_LOG.LOG_ERROR('PURGE', null, 'Error during ' || p_Module || ' : ' ||
         p_ErrMsg,p_Action);
    END IF;
    DBMS_APPLICATION_INFO.SET_MODULE(null,null); 
END EMD_PURGE_PERFORMANCE_LOG;

--
--  This procedure will convert an existing metrics raw table to use
--  partitions.
--
--  **WARNING** No data should be loaded into the metrics raw table
--  while this procedure is running.  Shut down every OMS loading data
--  before starting this procedure.
--
PROCEDURE INIT_RAW_PARTITIONS IS

  v_partitionDate DATE := 
    TRUNC(SYSDATE - EMD_MAINT_UTIL.get_raw_retention_window, 'DD');

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'Oracle Enterprise Manager.Init Raw Partitions',
    action_name => 'start');

  -- Only perform these steps if MGMT_METRICS_RAW is not partitioned.
  IF (EMD_MAINTENANCE.PARTITIONS_ENABLED(p_metrics_raw_table)) THEN
     DBMS_OUTPUT.PUT_LINE('The table is already partitioned.  Exiting.');
     RETURN;
  END IF;

  --
  -- Create the intermediate raw metrics table which will be the blueprint 
  -- for the new partitioned raw metrics table.
  -- 
  EXECUTE IMMEDIATE 'CREATE TABLE ' || p_metrics_raw_int_table ||
                                ' (TARGET_GUID RAW(16) NOT NULL, 
                                   METRIC_GUID RAW(16) NOT NULL, 
                                   KEY_VALUE VARCHAR2(256) DEFAULT '' '' NOT NULL, 
                                   COLLECTION_TIMESTAMP DATE NOT NULL,
                                   VALUE NUMBER DEFAULT NULL, 
                                   STRING_VALUE VARCHAR2(4000) DEFAULT NULL,
       CONSTRAINT MGMT_METRICS_RAW_PK_INT
        PRIMARY KEY(TARGET_GUID, METRIC_GUID, KEY_VALUE, COLLECTION_TIMESTAMP))  
    ORGANIZATION INDEX PCTFREE 0 
    COMPRESS 3  
    INCLUDING VALUE OVERFLOW
    PARTITION BY RANGE (COLLECTION_TIMESTAMP)
    (PARTITION "' || TO_CHAR(v_partitionDate, 'YYYY-MM-DD HH24:MI') || '" VALUES 
      LESS THAN (TO_DATE(''' || 
         TO_CHAR(v_partitionDate, 'YYYY-MM-DD HH24:MI') ||
           ''', ''YYYY-MM-DD HH24:MI'')))';

  --
  --  Pre-populate the partitions out for the raw int table.
  --
  EMD_MAINTENANCE.ADD_PARTITIONS(p_metrics_raw_int_table, 
                                    p_prepopulate_days_raw, FALSE);

  --
  --  Start the raw metric table redefinition process.
  --
  DBMS_REDEFINITION.START_REDEF_TABLE(G_REPO_OWNER, p_metrics_raw_table,
                                                      p_metrics_raw_int_table);

  --
  --  Finish the raw metric table redefinition process.
  --
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(G_REPO_OWNER, p_metrics_raw_table,
                                                       p_metrics_raw_int_table);
  --
  --  Drop the intermediate table.
  --
  EXECUTE IMMEDIATE 'DROP TABLE ' || p_metrics_raw_int_table;

  --
  --  Lock the CBO stats.
  -- 
  BEGIN
    EXECUTE IMMEDIATE 
      'call dbms_stats.lock_table_stats(''' || G_REPO_OWNER || ''', ''' ||
      p_metrics_raw_table || ''')';
  EXCEPTION
    WHEN OTHERS THEN
      NULL;  -- Ignore errors
  END;

EXCEPTION
  WHEN OTHERS THEN
    p_ErrMsg := SUBSTR(SQLERRM, 1, 2048);
    DBMS_APPLICATION_INFO.READ_MODULE(p_Module,p_Action);
    -- Log this message only if Error code is not equal to 1089 (immediate shutdown in progress) 
    IF(SQLCODE != MGMT_GLOBAL.G_ORA_SHUTDOWN) THEN
      MGMT_LOG.LOG_ERROR('MAINT', null, 'Error during ' || p_Module || ' : ' ||
         p_ErrMsg,p_Action);
     END IF;
END INIT_RAW_PARTITIONS;

--
--  This procedure will convert an existing metrics 1hour table to use
--  partitions.
--
--  **WARNING** No data should be loaded into the metrics 1hour table
--  while this procedure is running.  Stop the rollup DBMS JOB 
--  before starting this procedure.
--
PROCEDURE INIT_1HOUR_PARTITIONS IS

  v_partitionDate DATE := 
    TRUNC(SYSDATE - EMD_MAINT_UTIL.get_1hour_retention_window, 'DD');

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'Oracle Enterprise Manager.Init 1Hour Partitions',
    action_name => 'start');

  -- Only perform these steps if MGMT_METRICS_1HOUR is not partitioned.
  IF (EMD_MAINTENANCE.PARTITIONS_ENABLED(p_metrics_1hour_table)) THEN
     DBMS_OUTPUT.PUT_LINE('The table is already partitioned.  Exiting.');
     RETURN;
  END IF;

  --
  -- Create the intermediate 1hour metrics table which will be the blueprint 
  -- for the new partitioned 1hour metrics table.
  -- 
  EXECUTE IMMEDIATE 'CREATE TABLE ' || p_metrics_1hour_int_tab ||
                                ' (TARGET_GUID RAW(16) NOT NULL, 
                                   METRIC_GUID RAW(16) NOT NULL, 
                                   KEY_VALUE VARCHAR2(256) DEFAULT '' '' NOT NULL, 
                                   ROLLUP_TIMESTAMP DATE NOT NULL,
                                   SAMPLE_COUNT NUMBER DEFAULT 0, 
                                   VALUE_AVERAGE NUMBER DEFAULT 0,
                                   VALUE_MINIMUM NUMBER DEFAULT 0,
                                   VALUE_MAXIMUM NUMBER DEFAULT 0,
                                   VALUE_SDEV NUMBER DEFAULT 0,
       CONSTRAINT MGMT_METRICS_1HOUR_PK_INT
        PRIMARY KEY(TARGET_GUID, METRIC_GUID, KEY_VALUE, ROLLUP_TIMESTAMP))  
    ORGANIZATION INDEX PCTFREE 0 
    COMPRESS 3  
    PARTITION BY RANGE (ROLLUP_TIMESTAMP)
    (PARTITION "' || TO_CHAR(v_partitionDate, 'YYYY-MM-DD HH24:MI') || '" VALUES 
      LESS THAN (TO_DATE(''' || 
         TO_CHAR(v_partitionDate, 'YYYY-MM-DD HH24:MI') ||
           ''', ''YYYY-MM-DD HH24:MI'')))';

  --
  --  Pre-populate the partitions out for the 1hour int table.
  --
  EMD_MAINTENANCE.ADD_PARTITIONS(p_metrics_1hour_int_tab, 
                                    p_prepopulate_days_1hour, TRUE);

  --
  --  Start the 1hour metric table redefinition process.
  --
  DBMS_REDEFINITION.START_REDEF_TABLE(G_REPO_OWNER, p_metrics_1hour_table,
                                                      p_metrics_1hour_int_tab);

  --
  --  Finish the 1hour metric table redefinition process.
  --
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(G_REPO_OWNER, p_metrics_1hour_table,
                                                       p_metrics_1hour_int_tab);
  --
  --  Drop the intermediate table.
  --
  EXECUTE IMMEDIATE 'DROP TABLE ' || p_metrics_1hour_int_tab;

  --
  --  Lock the CBO stats.
  -- 
  BEGIN
    EXECUTE IMMEDIATE 
      'call dbms_stats.lock_table_stats(''' || G_REPO_OWNER || ''', ''' ||
      p_metrics_1hour_table || ''')';
  EXCEPTION
    WHEN OTHERS THEN
      NULL;  -- Ignore errors
  END;

  DBMS_APPLICATION_INFO.SET_MODULE(null,null); 
  
EXCEPTION
  WHEN OTHERS THEN
    p_ErrMsg := SUBSTR(SQLERRM, 1, 2048);
    DBMS_APPLICATION_INFO.READ_MODULE(p_Module,p_Action);
    -- Log this message only if Error code is not equal to 1089 (immediate shutdown in progress) 
    IF(SQLCODE != MGMT_GLOBAL.G_ORA_SHUTDOWN) THEN
      MGMT_LOG.LOG_ERROR('MAINT', null, 'Error during ' || p_Module || ' : ' ||
         p_ErrMsg,p_Action);
    END IF;
END INIT_1HOUR_PARTITIONS;

--
--  This procedure will convert an existing metrics 1day table to use
--  partitions.
--
--  **WARNING** No data should be loaded into the metrics 1day table
--  while this procedure is running.  Stop the rollup DBMS JOB 
--  before starting this procedure.
--
PROCEDURE INIT_1DAY_PARTITIONS IS

  v_partitionDate DATE := 
    TRUNC(SYSDATE - EMD_MAINT_UTIL.get_1day_retention_window, 'DD');

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'Oracle Enterprise Manager.Init 1Day Partitions',
    action_name => 'start');

  -- Only perform these steps if MGMT_METRICS_1HOUR is not partitioned.
  IF (EMD_MAINTENANCE.PARTITIONS_ENABLED(p_metrics_1day_table)) THEN
     DBMS_OUTPUT.PUT_LINE('The table is already partitioned.  Exiting.');
     RETURN;
  END IF;

  --
  -- Create the intermediate 1day metrics table which will be the blueprint 
  -- for the new partitioned 1day metrics table.
  -- 
  EXECUTE IMMEDIATE 'CREATE TABLE ' || p_metrics_1day_int_table ||
                                ' (TARGET_GUID RAW(16) NOT NULL, 
                                   METRIC_GUID RAW(16) NOT NULL, 
                                   KEY_VALUE VARCHAR2(256) DEFAULT '' '' NOT NULL, 
                                   ROLLUP_TIMESTAMP DATE NOT NULL,
                                   SAMPLE_COUNT NUMBER DEFAULT 0, 
                                   VALUE_AVERAGE NUMBER DEFAULT 0,
                                   VALUE_MINIMUM NUMBER DEFAULT 0,
                                   VALUE_MAXIMUM NUMBER DEFAULT 0,
                                   VALUE_SDEV NUMBER DEFAULT 0,
       CONSTRAINT MGMT_METRICS_1DAY_PK_INT
        PRIMARY KEY(TARGET_GUID, METRIC_GUID, KEY_VALUE, ROLLUP_TIMESTAMP))  
    ORGANIZATION INDEX PCTFREE 0 
    COMPRESS 3  
    PARTITION BY RANGE (ROLLUP_TIMESTAMP)
    (PARTITION "' || TO_CHAR(v_partitionDate, 'YYYY-MM-DD HH24:MI') || '" VALUES 
      LESS THAN (TO_DATE(''' || 
         TO_CHAR(v_partitionDate, 'YYYY-MM-DD HH24:MI') ||
           ''', ''YYYY-MM-DD HH24:MI'')))';

  --
  --  Pre-populate the partitions out for the 1day int table.
  --
  EMD_MAINTENANCE.ADD_PARTITIONS(p_metrics_1day_int_table, 
                                    p_prepopulate_days_1day, TRUE);

  --
  --  Start the 1day metric table redefinition process.
  --
  DBMS_REDEFINITION.START_REDEF_TABLE(G_REPO_OWNER, p_metrics_1day_table,
                                                      p_metrics_1day_int_table);

  --
  --  Finish the 1day metric table redefinition process.
  --
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(G_REPO_OWNER, p_metrics_1day_table,
                                                       p_metrics_1day_int_table);
  --
  --  Drop the intermediate table.
  --
  EXECUTE IMMEDIATE 'DROP TABLE ' || p_metrics_1day_int_table;

  --
  --  Lock the CBO stats.
  -- 
  BEGIN
    EXECUTE IMMEDIATE 
      'call dbms_stats.lock_table_stats(''' || G_REPO_OWNER || ''', ''' ||
      p_metrics_1day_table || ''')';
  EXCEPTION
    WHEN OTHERS THEN
      NULL;  -- Ignore errors
  END;

  DBMS_APPLICATION_INFO.SET_MODULE(null,null); 

EXCEPTION
  WHEN OTHERS THEN
    p_ErrMsg := SUBSTR(SQLERRM, 1, 2048);
    DBMS_APPLICATION_INFO.READ_MODULE(p_Module,p_Action);
    -- Log this message only if Error code is not equal to 1089 (immediate shutdown in progress) 
    IF(SQLCODE != MGMT_GLOBAL.G_ORA_SHUTDOWN) THEN
      MGMT_LOG.LOG_ERROR('MAINT', null, 'Error during ' || p_Module || ' : ' ||
         p_ErrMsg,p_Action);
   END IF;
END INIT_1DAY_PARTITIONS;
----------------------------------------------------------------------

----------------------------------------------------------------------
  -- Basic internal API to add data to MGMT_METRICS_RAW
  -- Needs to be here so other internal packages can call this
  --

--  Internal proc for adding metric data
PROCEDURE ADD_METRIC_DATA (v_target_guid IN RAW,
                           v_metric_guid IN RAW,
                           v_key_value   IN VARCHAR2,
                           v_value       IN NUMBER,
                           v_string_value  IN VARCHAR2,
                           v_timestamp     IN DATE DEFAULT SYSDATE)
IS
  ignore NUMBER;
  l_key_value VARCHAR2(256);
BEGIN

  IF v_key_value IS NULL
  THEN
    l_key_value := ' ';
  ELSE
    l_key_value := v_key_value;
  END IF;

  IF NOT v_value IS NULL 
  THEN 
    BEGIN
      INSERT INTO mgmt_metrics_raw
        (target_guid, collection_timestamp, metric_guid, key_value,
         value)
      VALUES
        (v_target_guid, v_timestamp, v_metric_guid, l_key_value,
         v_value);
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        UPDATE mgmt_metrics_raw
        SET value = v_value
        WHERE target_guid = v_target_guid
        AND metric_guid = v_metric_guid
        AND key_value = l_key_value
        AND collection_timestamp = v_timestamp;
    END;
  ELSE
    ignore := UPDATE_STRING_METRICS_ROW(v_target_guid, v_metric_guid, l_key_value,
                                        v_timestamp, v_string_value);
  END IF;

  ignore := UPDATE_CURRENT_METRICS_ROW(v_target_guid, v_metric_guid, l_key_value,
                                       v_timestamp, v_value, v_string_value);

END ADD_METRIC_DATA;

-- Add number data
PROCEDURE ADD_METRIC_DATA (v_target_guid IN RAW,
                           v_metric_guid IN RAW,
                           v_key_value   IN VARCHAR2,
                           v_value       IN NUMBER,
                           v_timestamp   IN DATE DEFAULT SYSDATE)
IS
BEGIN 

  ADD_METRIC_DATA(v_target_guid, v_metric_guid, v_key_value, v_value, NULL, v_timestamp);

END ADD_METRIC_DATA;

-- Add String data
PROCEDURE ADD_METRIC_DATA (v_target_guid IN RAW,
                           v_metric_guid IN RAW,
                           v_key_value   IN VARCHAR2,
                           v_string_value  IN VARCHAR2,
                           v_timestamp     IN DATE DEFAULT SYSDATE)
IS
BEGIN

  ADD_METRIC_DATA(v_target_guid, v_metric_guid, v_key_value, NULL, v_string_value, v_timestamp);

END ADD_METRIC_DATA;

----------------------------------------------------------------------

  --
  -- BEGIN PROCEDURES used by the Loader 
  --
  -- The following logic assumes that the metadata file is ordered as follows
  --  1. UPLOAD start tag..
  --  2. ROWSET for MGMT_CHANGE_AGENT_URL table, if any
  --  3. ROWSET for MGMT_TARGETS table 
  --     (all targets monitored by the agent should be present in this rowset)
  --  4. UPLOAD end tag
----------------------------------------------------------------------
  --
  -- Called by XMLLoader on seeing a ROWSET begin tag for MGMT_TARGETS table
  -- 
  -- Returns the DB time which is passed back into END_MGMT_TARGETS_ROWSET
  --

  PROCEDURE START_MGMT_TARGETS_ROWSET(p_start_time_out OUT DATE) IS
  BEGIN 
    p_start_time_out := SYSDATE;
    p_targets_added.DELETE;
    p_metaver_changes.DELETE ;
    p_targets_loaded.DELETE;
    p_targets_broken.DELETE;
  END START_MGMT_TARGETS_ROWSET;

----------------------------------------------------------------------

  --
  -- Called by XMLLoader on seeing a ROWSET end tag for MGMT_TARGETS table
  -- 
  -- Resolves any duplicates
  --   If agent has not sent target info for a duplicate target, then
  --   that duplicate target is resolved
  --   This is detected by performing a UTC date comparision of detection_time
  --   and when the MGMT_TARGETS rowset processing started.
  -- Deletes change agent url record(s), if any, as this proc is called after
  --   processing all targets.
  -- Reset the security info.
  -- Out parameter contains list of resolved duplicate targets.
  --   

  PROCEDURE END_MGMT_TARGETS_ROWSET(p_emd_url_in           IN VARCHAR2,
                                    p_start_time_in        IN VARCHAR2,
                                    p_last_updated_time_in IN VARCHAR2,
                                    p_resolved_dup_targets_out OUT RESOLVEDTARGET_CURSOR,
                                    p_duptarget_details_out OUT DUPTARGET_CURSOR,
                                    p_standby_targets_out   OUT STANDBYTARGET_CURSOR)
  IS
    l_start_time      DATE;
    l_start_time_utc  DATE;
    l_db_timezone_rgn MGMT_TARGETS.timezone_region%TYPE;
    l_agent_guid      MGMT_TARGETS.target_guid%TYPE;
    l_old_emd_url     MGMT_TARGETS.emd_url%TYPE;
    l_new_emd_url     MGMT_TARGETS.emd_url%TYPE;
    l_old_agent_guid  MGMT_TARGETS.target_guid%TYPE;
    l_new_agent_guid  MGMT_TARGETS.target_guid%TYPE;
    l_url_changed     BOOLEAN := FALSE;
    l_agent_name VARCHAR2(256);
    l_agent_port VARCHAR2(64); 
    l_emd_url    MGMT_TARGETS.emd_url%TYPE;
  BEGIN

    l_db_timezone_rgn := TO_CHAR(SYSTIMESTAMP,'TZR');
    l_start_time      := TO_DATE(p_start_time_in, 'YYYY-MM-DD HH24:MI:SS');
    l_start_time_utc  := MGMT_GLOBAL.TO_UTC(l_start_time, l_db_timezone_rgn);
    l_emd_url := p_emd_url_in ;

    BEGIN
      SELECT target_guid
        INTO l_agent_guid
        FROM mgmt_targets
       WHERE emd_url = p_emd_url_in
         AND target_type = mgmt_global.G_AGENT_TARGET_TYPE;
    EXCEPTION
      -- We have seen cases where agent does not send agent target
      -- -or- sends an incorrect target_guid for agent
      WHEN NO_DATA_FOUND THEN
        -- Try to get agent_guid based on name and port
        get_name_port_from_emd_url(p_emd_url_in, l_agent_name, l_agent_port);

        BEGIN
          SELECT target_guid, emd_url
            INTO l_agent_guid, l_emd_url
            FROM mgmt_targets
           WHERE emd_url LIKE '%://'||l_agent_name||':'||l_agent_port||'/%'
             AND target_type = mgmt_global.G_AGENT_TARGET_TYPE;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            raise_application_error(MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR,
                MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR_M ||
                ' Agent does not exist for '|| p_emd_url_in);
        END;
    END;

    IF p_emd_url_in != l_emd_url THEN
      -- This indicates that Agent url received does not exists in MGMT_TARGETS
      -- but url with same agent name and port no exists.
      -- It could be just a http and https diff in emd_url
      -- We shall update all target with l_emd_url (old url) to p_emd_url_in (new url)
      set_emd_url_change
      (
        p_last_emd_url => l_emd_url,
        p_emd_url      => p_emd_url_in
      );

      UPDATE mgmt_targets
         SET emd_url = p_emd_url_in
       WHERE emd_url = l_emd_url ;

      clear_emd_url_change
      (
        p_last_emd_url => l_emd_url,
        p_emd_url      => p_emd_url_in
      );

    END IF;

    l_agent_name := NULL;
    l_agent_port := NULL;

    -- Process multi-agent-targets
    PROCESS_MULTI_AGENT_TARGETS(p_emd_url_in);

    -- Resolve the duplicates, if any
    -- Resolve duplicates if the target set doesnot contain the target.
    -- Perfrom UTC time comparision to support multiple timezones
    UPDATE MGMT_DUPLICATE_TARGETS
       SET resolved_time = MGMT_GLOBAL.SYSDATE_TZRGN(timezone_region)
     WHERE duplicate_emd_url = p_emd_url_in
       AND MGMT_GLOBAL.TO_UTC(detection_time, timezone_region) < l_start_time_utc;

    -- List of resolved duplicate targets for out parameter
    OPEN p_resolved_dup_targets_out FOR
      SELECT target_name, target_type
        FROM MGMT_DUPLICATE_TARGETS
       WHERE resolved_time = MGMT_GLOBAL.SYSDATE_TZRGN(timezone_region);


    -- Check to see if there is a change URL in process
    BEGIN
      SELECT last_emd_url, emd_url
        INTO l_old_emd_url, l_new_emd_url
        FROM MGMT_CHANGE_AGENT_URL
       WHERE emd_url = p_emd_url_in
         AND ROWNUM = 1;

       l_url_changed := TRUE;

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        NULL;
    END;


    IF (l_url_changed) THEN

      -- Update the security info.
      -- Get old agent GUID
      BEGIN 
        SELECT target_guid INTO l_old_agent_guid
          FROM MGMT_TARGETS
         WHERE emd_url = l_old_emd_url
           AND target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE
           AND ROWNUM = 1;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          -- This is possible if agent is secured.
          -- In this case, the host,port of the agent did not change.
          -- So there will be no agent target corresponding to the 
          -- old emd_url
          l_old_agent_guid := NULL;
      END;

      -- Get new agent GUID
      BEGIN
        SELECT target_guid INTO l_new_agent_guid
          FROM MGMT_TARGETS
         WHERE emd_url = l_new_emd_url
           AND target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE
           AND ROWNUM = 1;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          -- Should never happen 
          l_new_agent_guid := NULL;
      END;

      IF ( (l_old_agent_guid IS NOT NULL) AND
           (l_new_agent_guid IS NOT NULL) ) THEN

            -- inform assoc packages about port change 
         BEGIN
           em_assoc.change_agent_guid(l_old_agent_guid,l_new_agent_guid);
           em_master_agent.handle_agent_port_change(l_old_agent_guid,l_new_agent_guid);
           mgmt_mp.handle_agent_port_change(l_old_agent_guid,l_new_agent_guid);
         EXCEPTION
          -- Ignore exceptions so that main transaction is not affected; 
          WHEN OTHERS THEN
            mgmt_log.log_error(v_module_name_in => 'ASSOC',
                               v_error_code_in  =>  0,
                               v_error_msg_in   => SUBSTR(SQLERRM, 1, 2000));
        END;

         -- delete the key corresponding to the old agent url. 
         get_name_port_from_emd_url(l_old_emd_url,l_agent_name,l_agent_port);
         EM_SEC.delete_agent_key(l_agent_name,l_agent_port);  
        
      END IF;

    END IF;
   
    -- Delete change agent URL records.
    clear_emd_url_change(l_old_emd_url, l_new_emd_url);

    -- Bug:4365302 - Mark target as broken if not exists in targets.xml file

     -- Get list of target loaded in ths rowset
     -- Get list of target exist in repository
     -- Differnce has to be mark as broken
     FOR i IN (SELECT target_name, target_type
                 FROM ( SELECT t.target_name target_name, t.target_type target_type
                          FROM mgmt_targets t
                         WHERE emd_url = p_emd_url_in
                         MINUS
                        SELECT l.name target_name, l.value target_type
                          FROM TABLE(CAST(p_targets_loaded as SMP_EMD_NVPAIR_ARRAY)) l
                      )
               ORDER BY DECODE(target_type, MGMT_GLOBAL.G_AGENT_TARGET_TYPE, 1, 2), target_type, target_name )
     LOOP
       -- This update will populate list p_targets_broken, for current target, so that we can log metric error msg.
       -- So it will add on more entry to p_targets_broken, if there exists in broken target, if not available in
       -- targets.xml
       UPDATE mgmt_targets
          SET broken_reason = MGMT_GLOBAL.G_BROKEN_UNMONITORED,
              broken_str = 'Target deleted from agent'
        WHERE target_name = i.target_name
          AND target_type = i.target_type ;
     END LOOP;
     p_targets_loaded.DELETE;
                                  
     
     -- Bug: 4260179 - Handle broken target
     -- Call handle target with broken list
     handle_target_broken_state
     (
       p_broken_target_list => p_targets_broken
     );
     p_targets_broken.DELETE;

    -- Open a cursor for list of duplicate targets
    GET_DUPTARGET_DETAILS(p_emd_url_in, p_duptarget_details_out);

    -- List of standby targets for this agent
    OPEN p_standby_targets_out FOR
     SELECT a.target_guid
       FROM mgmt_targets t, mgmt_target_agent_assoc a
      WHERE a.agent_guid = l_agent_guid
        AND a.target_guid = t.target_guid
        AND t.emd_url <> p_emd_url_in;

    FOR i in 1..p_targets_added.COUNT LOOP
      exec_target_addition_callbacks(EM_CHECK.NOOP(p_targets_added(i).name), 
                                     EM_CHECK.NOOP(p_targets_added(i).value),
         MGMT_TARGET.generate_target_guid(p_targets_added(i).name, p_targets_added(i).value));
    END LOOP;
    p_targets_added.DELETE;
    IF p_metaver_changes IS NOT NULL AND p_metaver_changes.COUNT > 0
    THEN
      em_target.exec_metaver_upd_callback(p_metaver_changes) ;
    END IF ;
    p_metaver_changes.DELETE ;

  END END_MGMT_TARGETS_ROWSET;

----------------------------------------------------------------------
  --
  -- Called by XMLLoader on seeing a ROWSET end tag for MGMT_CHANGE_AGENT_URL table
  -- 
  -- Change the URL for duplicate target entries
  -- Change the URL for deleted target entries
  -- 
  PROCEDURE END_CHANGE_AGENT_URL(p_old_agent_url  IN VARCHAR2,
                                 p_new_agent_url  IN VARCHAR2) 
  IS
  BEGIN

    -- Update duplicate targets table..
    -- Bug: 4516691 - Updating it in loop due to PK error

    BEGIN
      FOR i IN (SELECT target_guid
                  FROM mgmt_duplicate_targets
                 WHERE duplicate_emd_url = p_old_agent_url)
      LOOP
        BEGIN
          UPDATE mgmt_duplicate_targets
             SET duplicate_emd_url = p_new_agent_url
           WHERE target_guid = i.target_guid
             AND duplicate_emd_url = p_old_agent_url ;
        EXCEPTION
          WHEN DUP_VAL_ON_INDEX THEN
            DELETE
              FROM mgmt_duplicate_targets
            WHERE target_guid = i.target_guid
              AND duplicate_emd_url = p_old_agent_url ;
        END;
      END LOOP;
    END;

    -- Update delete target table..
    UPDATE MGMT_TARGETS_DELETE
       SET emd_url = p_new_agent_url
     WHERE emd_url = p_old_agent_url;

  END END_CHANGE_AGENT_URL;

----------------------------------------------------------------------

  -- OBSOLETED.....
  PROCEDURE UPSERT_DUPLICATE_TARGET(p_target_guid IN RAW,
                                    p_emd_url IN VARCHAR2)
  IS
    l_target_name MGMT_TARGETS.target_name%TYPE;
    l_target_type MGMT_TARGETS.target_type%TYPE;
  BEGIN

    -- Try updating the duplicate target.
    UPDATE MGMT_DUPLICATE_TARGETS
       SET detection_time = SYSDATE,
           resolved_time = NULL
     WHERE target_guid = p_target_guid
       AND duplicate_emd_url = p_emd_url;

    IF (SQL%ROWCOUNT = 0) THEN

      BEGIN 
        SELECT target_name, target_type
          INTO l_target_name, l_target_type
          FROM MGMT_TARGETS
         WHERE target_guid = p_target_guid;
      EXCEPTION
        WHEN OTHERS THEN
          l_target_name := ' ';
          l_target_type := ' ';
      END;

      INSERT INTO MGMT_DUPLICATE_TARGETS
        (target_guid, duplicate_emd_url, target_name, target_type, 
         detection_time)
       VALUES
        (p_target_guid, p_emd_url, l_target_name, l_target_type, 
         SYSDATE);

    END IF;

  END UPSERT_DUPLICATE_TARGET;

----------------------------------------------------------------------
  --
  -- Called by XMLLoader on getting a MGMT_GLOBAL.DUPLICATE_TARGET_ERR
  --   on perfoming an insert/update on MGMT_TARGETS table.
  -- 
  -- Upsert into MGMT_DUPLICATE_TARGETS
  --   If an entry already exists, reset the resolved time
  -- 
  PROCEDURE UPSERT_DUPLICATE_TARGET(p_target_name IN VARCHAR2,
                                    p_target_type IN VARCHAR2,
                                    p_target_guid IN RAW,
                                    p_emd_url     IN VARCHAR2,
                                    p_timezone_rgn IN VARCHAR2) IS
    l_timezone_region MGMT_TARGETS.timezone_region%TYPE;
    l_agent_guid MGMT_TARGETS.target_guid%TYPE;
  BEGIN
    -- If this a multi-agent target, then don't treat this
    -- as a duplicate target. Note that repository target
    -- can never be a multi-agent target.
    IF (p_target_type != MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE AND
        is_multi_agent_target(p_target_name, p_target_type) = 1) THEN
      BEGIN
          update_mat_agent_list(p_target_guid, p_emd_url);
      EXCEPTION
          -- We will never reach here with 4.1 agents as the agent
          -- gaurantees uploading agent target first before other
          -- targets; This is to handle 4.0 agents. Note that this
          -- Exception Handling can be removed once 4.1 agent implements
          -- uploading agent as the first target.
          WHEN OTHERS THEN 
              MGMT_LOG.LOG_ERROR('LOADER', null, 
              'UPSERT_DUPLICATE_TARGET: Error getting target_guid for '
               || p_target_name || ',' || p_target_type || ' : ' ||
               SUBSTR(SQLERRM, 1, 1000));
            RETURN;
      END;
    ELSE
      -- Try updating the duplicate target.
      BEGIN
        IF (p_timezone_rgn IS NULL) THEN
          l_timezone_region := TO_CHAR(SYSTIMESTAMP,'TZR');
        ELSE
          l_timezone_region := p_timezone_rgn;
        END IF;

        UPDATE MGMT_DUPLICATE_TARGETS
           SET detection_time = MGMT_GLOBAL.SYSDATE_TZRGN(l_timezone_region),
               resolved_time = NULL
         WHERE target_guid = p_target_guid
           AND duplicate_emd_url = p_emd_url;
 
        IF (SQL%ROWCOUNT = 0) THEN

          INSERT INTO MGMT_DUPLICATE_TARGETS
            (target_name, target_type, target_guid, duplicate_emd_url,
             timezone_region, detection_time)
           VALUES
            (p_target_name, p_target_type, p_target_guid, p_emd_url,
             p_timezone_rgn, MGMT_GLOBAL.SYSDATE_TZRGN(l_timezone_region));
        END IF;
      END;
    END IF; -- NOT is_multi_agent_target()

  END UPSERT_DUPLICATE_TARGET;

----------------------------------------------------------------------

  PROCEDURE START_MEMBERSHIPS_ROWSET
  IS 
  BEGIN
     em_assoc.start_assoc_rowset;
  END START_MEMBERSHIPS_ROWSET;

----------------------------------------------------------------------

  PROCEDURE END_MEMBERSHIPS_ROWSET
  IS
  BEGIN
     em_assoc.end_assoc_rowset;
  END END_MEMBERSHIPS_ROWSET;

----------------------------------------------------------------------

  PROCEDURE END_METADATA_SET(p_target_type_in   IN VARCHAR2,
                             p_type_meta_ver_in IN VARCHAR2,
                             p_table_name_in    IN VARCHAR2,
                             p_platform_in      IN VARCHAR2 DEFAULT NULL) IS
  BEGIN

    BEGIN
       INSERT INTO MGMT_METADATA_SETS
          (target_type, type_meta_ver, table_name, load_time)
       VALUES
         (p_target_type_in || DECODE( TRIM(p_platform_in),NULL,'','_'||p_platform_in) , p_type_meta_ver_in, p_table_name_in,
          SYSDATE);
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        UPDATE MGMT_METADATA_SETS
           SET load_time = SYSDATE
         WHERE target_type = p_target_type_in
           AND type_meta_ver = p_type_meta_ver_in
           AND table_name = p_table_name_in;
    END;

    IF (p_table_name_in = 'MGMT_METRICS') THEN

      update_met_label_nlsid(p_target_type_in);

      -- When mgmt_metrics are loaded, register the target type also.
      -- NOTE: The end of a target type is calculated based on an approximation.
      --   There is no clear indication from the agent about when the metadata
      --   for one target type has ended and the metadata for another target type
      --   has started.
      --   We approximate the end of a target type metadata to be the
      --   end of MGMT_METRICS rowset, which is a fair approximation.
      MGMT_TARGET.register_target_type(p_target_type_in => p_target_type_in,
                                       p_type_meta_ver_in => p_type_meta_ver_in,
                                       p_type_display_name_in => NULL,
                                       p_type_display_nlsid_in => NULL,
                                       p_type_resource_bundle_in => NULL);
      EM_METRIC.exec_metric_metadata_reg_cb(p_target_type_in,
                                            p_type_meta_ver_in);

    END IF;

  END END_METADATA_SET;

----------------------------------------------------------------------

  PROCEDURE GET_LOADED_METADATA_SETS(
            p_loaded_metadata_sets_out OUT METADATASET_CURSOR)
  IS
  BEGIN

    OPEN p_loaded_metadata_sets_out FOR
      SELECT target_type, type_meta_ver, table_name, load_time
        FROM MGMT_METADATA_SETS
       WHERE load_time IS NOT NULL;

  END GET_LOADED_METADATA_SETS;

----------------------------------------------------------------------

  PROCEDURE GET_DUPTARGET_DETAILS(p_emd_url_in IN VARCHAR2,
                                  p_duptarget_details_out OUT DUPTARGET_CURSOR)
  IS
  BEGIN
    OPEN p_duptarget_details_out FOR
      SELECT target_guid, detection_time, resolved_time
        FROM MGMT_DUPLICATE_TARGETS
       WHERE duplicate_emd_url = p_emd_url_in;

  END GET_DUPTARGET_DETAILS;
----------------------------------------------------------------------

  PROCEDURE GET_DELETETARGET_DETAILS(p_emd_url_in IN VARCHAR2,
                                     p_deletetarget_details_out OUT DELETETARGET_CURSOR)
  IS
     l_agent_guid mgmt_targets.target_guid%TYPE;
  BEGIN
    BEGIN
      SELECT target_guid
        INTO l_agent_guid
        FROM mgmt_targets
       WHERE emd_url = p_emd_url_in
         AND target_type = mgmt_global.G_AGENT_TARGET_TYPE;
    EXCEPTION
      -- agent is deleted first
      WHEN NO_DATA_FOUND THEN
        l_agent_guid := NULL;  
    END;

    OPEN p_deletetarget_details_out FOR
      SELECT target_guid, delete_request_time, delete_complete_time
        FROM MGMT_TARGETS_DELETE del
       WHERE emd_url = p_emd_url_in
          OR EXISTS (SELECT 1 
                       FROM mgmt_target_agent_assoc
                      WHERE target_guid = del.target_guid
                        AND agent_guid  = l_agent_guid);

  END GET_DELETETARGET_DETAILS;
  -- Called by Loader on startup
  -- 
  PROCEDURE INIT_LOADER_PARAMS(p_host_url VARCHAR2,
                               p_loader_params SMP_EMD_NVPAIR_ARRAY)
  IS
  BEGIN
    -- Clean up existing loader parameters 
    -- before inserting new ones.
    DELETE FROM MGMT_OMS_PARAMETERS
     WHERE host_url = p_host_url
       AND
       (name like ('loader%') OR name like ('%upload_port'));
	
    -- Insert loader parameters
    FOR i IN 1..p_loader_params.count
    LOOP
      INSERT INTO MGMT_OMS_PARAMETERS
        (host_url, name, value)
       VALUES (p_host_url, p_loader_params(i).name, p_loader_params(i).value);
    END LOOP;

  END INIT_LOADER_PARAMS;

----------------------------------------------------------------------
  --
  -- Called by Loader to log updated parameters
  -- 
  PROCEDURE UPDATE_LOADER_PARAMS(p_host_url VARCHAR2,
                                 p_loader_params SMP_EMD_NVPAIR_ARRAY)
  IS PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    -- Update the given list of loader parameters
    FOR i IN 1..p_loader_params.count
    LOOP
      BEGIN
          UPDATE mgmt_oms_parameters
          SET value = p_loader_params(i).value
          WHERE host_url = p_host_url
          AND name = p_loader_params(i).name;

          IF (SQL%ROWCOUNT = 0) THEN
              INSERT INTO MGMT_OMS_PARAMETERS (host_url, name, value)
              VALUES (p_host_url, p_loader_params(i).name, p_loader_params(i).value);
          END IF;
      END;
    END LOOP;
   
    COMMIT;

  END UPDATE_LOADER_PARAMS;

-----------------------------------------------------------------------
  --
  -- Called by XMLLoader on UPLOAD start tag
  -- 
  -- For a given agent (emd_url), return the list of
  --   duplicate targets 
  --   deleted targets
  --
  PROCEDURE START_UPLOAD(p_emd_url_in               IN  VARCHAR2,
                         p_upload_time_in           IN  VARCHAR2, 
                         p_pre_load_proc            IN  VARCHAR2 DEFAULT NULL,
                         p_post_load_proc           IN  VARCHAR2 DEFAULT NULL,
                         p_duptarget_details_out    OUT DUPTARGET_CURSOR,
                         p_deletetarget_details_out OUT DELETETARGET_CURSOR)
  IS
  BEGIN

    p_current_emd_url := p_emd_url_in;
    p_current_upload_time := p_upload_time_in;

    validate_callback(p_pre_load_proc, MGMT_GLOBAL.G_PRE_UPLOAD_CALLBACK,
                      'pre_load callback ' || p_pre_load_proc  || ' is not registered');

    validate_callback(p_post_load_proc, MGMT_GLOBAL.G_POST_UPLOAD_CALLBACK,
                      'post_load callback ' || p_post_load_proc  || ' is not registered');

    GET_DUPTARGET_DETAILS(p_emd_url_in, p_duptarget_details_out);
 
    GET_DELETETARGET_DETAILS(p_emd_url_in, p_deletetarget_details_out);

  END START_UPLOAD;
-----------------------------------------------------------------------
  --
  -- Called by XMLLoader on UPLOAD end tag
  -- 
  -- For a given emd_url,
  --    Update last_upload_time, last_rt_upload_times
  --    Delete blackouts for targets on this agent and for composite targets
  --    Delete resolved duplicate targets and delete completed targets for the agent
  -- 
  PROCEDURE END_UPLOAD(p_emd_url_in     IN VARCHAR2,
                       p_upload_time_in IN VARCHAR2,
                       p_raw_list       IN EM_LOADER_UPLOAD_TIME_ARRAY,
                       p_rt_raw_list    IN EM_LOADER_UPLOAD_TIME_ARRAY,
                       p_e2e_raw_list   IN EM_LOADER_UPLOAD_TIME_ARRAY)
  IS
    l_file_upload_time DATE;
    l_date_format VARCHAR2(32) := 'YYYY-MM-DD HH24:MI:SS';
  BEGIN

    p_current_emd_url := NULL;

    l_file_upload_time := TO_DATE(p_upload_time_in, l_date_format);
    --
    -- even though there are 3 update statements for mgmt_targets below
    -- only one of the lists is populated so only one column will be updated
    -- Ordering is essential to avoid deadlocks with metadata loader
    --
    -- Update last_upload_time for p_raw_list
    IF p_raw_list IS NOT NULL AND p_raw_list.COUNT > 0
    THEN
      FOR i IN ( SELECT tgt.target_guid,
                        to_date(list.upload_time,l_date_format) load_time
                   FROM TABLE(CAST(p_raw_list AS 
                                   EM_LOADER_UPLOAD_TIME_ARRAY)) list,
                        mgmt_targets tgt
                  WHERE tgt.target_guid = HEXTORAW(list.target_guid) 
                ORDER BY DECODE(tgt.target_type,
                                    MGMT_GLOBAL.G_AGENT_TARGET_TYPE,1,2),
                           tgt.target_type,tgt.target_name
               )
      LOOP
  
        -- Update last_upload_time
        UPDATE MGMT_TARGETS 
           SET last_load_time = i.load_time
         WHERE target_guid = i.target_guid
           AND (last_load_time < i.load_time
            OR  last_load_time IS NULL);

      END LOOP;
    END IF ;
    
    -- Update last_rt_upload_time for p_rt_raw_list
    IF p_rt_raw_list IS NOT NULL AND p_rt_raw_list.COUNT > 0
    THEN
      FOR i IN ( SELECT tgt.target_guid,
                        to_date(list.upload_time,l_date_format) load_time
                   FROM TABLE(CAST(p_rt_raw_list AS 
                                   EM_LOADER_UPLOAD_TIME_ARRAY)) list,
                      mgmt_targets tgt
                    WHERE tgt.target_guid = HEXTORAW(list.target_guid) 
                  ORDER BY DECODE(tgt.target_type,
                                  MGMT_GLOBAL.G_AGENT_TARGET_TYPE,1,2),
                           tgt.target_type,tgt.target_name
               )
      LOOP
  
        UPDATE MGMT_TARGETS 
           SET last_rt_load_time = i.load_time
         WHERE target_guid = i.target_guid
           AND (last_rt_load_time < i.load_time
            OR  last_rt_load_time IS NULL);
  
      END LOOP;
    END IF ;

    -- Update last_e2e_upload_time for p_e2e_raw_list
    IF p_e2e_raw_list IS NOT NULL AND p_e2e_raw_list.COUNT > 0
    THEN
      FOR i IN ( SELECT tgt.target_guid,to_date(list.upload_time,l_date_format) load_time
                   FROM TABLE(CAST(p_e2e_raw_list AS 
                                   EM_LOADER_UPLOAD_TIME_ARRAY)) list,
                        mgmt_targets tgt
                  WHERE tgt.target_guid = HEXTORAW(list.target_guid) 
                  ORDER BY DECODE(tgt.target_type,
                                  MGMT_GLOBAL.G_AGENT_TARGET_TYPE,1,2),
                           tgt.target_type,tgt.target_name
               )
      LOOP
  
        UPDATE MGMT_TARGETS 
           SET last_e2e_load_time = i.load_time
         WHERE target_guid = i.target_guid
           AND (last_e2e_load_time < i.load_time
            OR  last_e2e_load_time IS NULL);
  
      END LOOP;
    END IF ;

    -- Delete blackout windows for targets in p_raw_list
    DELETE FROM MGMT_BLACKOUT_WINDOWS
     WHERE target_guid IN (SELECT target_guid 
                             FROM MGMT_TARGETS
                            WHERE emd_url = p_emd_url_in)
       AND end_time IS NOT NULL
       AND end_time < (l_file_upload_time - (1/24))
       AND status IN (MGMT_BLACKOUT.BLK_STATE_STOPPED,
                      MGMT_BLACKOUT.BLK_STATE_ENDED);

    -- Delete blackout windows for repository-based targets
    DELETE FROM MGMT_BLACKOUT_WINDOWS 
     WHERE target_guid IN (SELECT target_guid 
                             FROM MGMT_TARGETS 
                            WHERE emd_url IS NULL) 
       AND end_time IS NOT NULL 
       AND end_time < (SYSDATE - (1/24))
       AND status IN (MGMT_BLACKOUT.BLK_STATE_STOPPED,
                      MGMT_BLACKOUT.BLK_STATE_ENDED);

    -- Delete target_deletes for all targets on p_emd_url 
    -- who have been deleted atleast five days before this file is uploaded.
    -- Earlier instead of 5 days, we used to delete record after 1 hour 
    -- (sysdate - delete_completion_time > 1 hour)
    -- This has been changed for the bug 4476740, to avoid the race condition for deleted targets.  

    DELETE FROM MGMT_TARGETS_DELETE 
     WHERE emd_url = p_emd_url_in
       AND delete_complete_time < (l_file_upload_time - 5);

    -- Delete duplicate targets for all targets on p_emd_url based 
    -- that are resolved at least one hour before this file is uploaded.
    DELETE FROM MGMT_DUPLICATE_TARGETS
     WHERE duplicate_emd_url = p_emd_url_in
       AND resolved_time < (l_file_upload_time - (1/24));

  END END_UPLOAD;
-----------------------------------------------------------------------
  --  
  -- END PROCEDURES used by the Loader 
  --

  PROCEDURE ADD_ROLLUP_TIME(v_target_guid IN RAW,
                            v_table_name  IN VARCHAR2,
                            v_start_time  IN DATE) IS
  BEGIN

    BEGIN
      INSERT INTO MGMT_TARGET_ROLLUP_TIMES
          ( TARGET_GUID,ROLLUP_TABLE_NAME, ROLLUP_TIMESTAMP ) 
       VALUES
          (v_target_guid, v_table_name, v_start_time);
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        -- Ignore duplicates
        NULL;
    END;

  END ADD_ROLLUP_TIME;

  PROCEDURE ADD_BOOTSTRAP_TIME(v_target_guid IN RAW,
                            v_start_time  IN DATE) IS
  BEGIN

    BEGIN
      INSERT INTO MGMT_RT_BOOTSTRAP_TIMES
          ( TARGET_GUID, ROLLUP_TIMESTAMP ) 
       VALUES
          (v_target_guid, v_start_time);
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        -- Ignore duplicates
        NULL;
    END;

  END ADD_BOOTSTRAP_TIME;
----------------------------------------------------------------------

  -- Procedure called by before update trigger on targets table
  PROCEDURE ADD_HOST_AVAIL_METRIC(p_target_guid     IN RAW,
                                  p_type_meta_ver   IN VARCHAR2 DEFAULT '1.0',
				  p_category_prop_1 IN VARCHAR2 DEFAULT ' ',
				  p_category_prop_2 IN VARCHAR2 DEFAULT ' ',
				  p_category_prop_3 IN VARCHAR2 DEFAULT ' ',
				  p_category_prop_4 IN VARCHAR2 DEFAULT ' ',
				  p_category_prop_5 IN VARCHAR2 DEFAULT ' ')
  IS
    resp_stat_metric_guid RAW(16);
  BEGIN
    --
    -- Check if a host/response/status metric has been defined.  If not, 
    -- create one.  Otherwise, continue on.
    --
    BEGIN

      resp_stat_metric_guid := MGMT_METRIC.get_metric_guid_for_props(
          p_target_type     => MGMT_GLOBAL.G_HOST_TARGET_TYPE,
          p_metric_name     => MGMT_GLOBAL.G_AVAIL_METRIC_NAME,
          p_metric_column   => MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN,
          p_type_meta_ver   => p_type_meta_ver,
          p_category_prop_1 => ' ',
          p_category_prop_2 => ' ',
          p_category_prop_3 => ' ',
          p_category_prop_4 => ' ',
          p_category_prop_5 => ' ');

          
    EXCEPTION
      WHEN MGMT_GLOBAL.no_such_metric_found THEN

        -- Add Response/Status metric to the host name
        -- Insert 'Response' table metric.
        MGMT_METRIC.create_metric(
                          p_target_type        => MGMT_GLOBAL.G_HOST_TARGET_TYPE,
                          p_metric_name        => MGMT_GLOBAL.G_AVAIL_METRIC_NAME,
                          p_metric_label        => MGMT_GLOBAL.G_AVAIL_METRIC_NAME,
                          p_metric_label_nlsid  => G_HOST_AVAIL_METRIC_NLSID,
                          p_type_meta_ver      => p_type_meta_ver,
                          p_metric_type        => MGMT_GLOBAL.G_METRIC_TYPE_TABLE, 
                          p_metric_column_list => MGMT_METRIC_COLUMN_ARRAY(
                              MGMT_METRIC_COLUMN_OBJ.NEW(
                                  p_column_name        => MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN,
                                  p_column_type        => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
                                  p_column_label_nlsid => G_HOST_AVAIL_METRIC_COL_NLSID,
                                  p_is_key             => MGMT_GLOBAL.G_FALSE))
                          );

        resp_stat_metric_guid := MGMT_METRIC.get_metric_guid_for_props(
            p_target_type     => MGMT_GLOBAL.G_HOST_TARGET_TYPE,
            p_metric_name     => MGMT_GLOBAL.G_AVAIL_METRIC_NAME,
            p_metric_column   => MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN,
            p_type_meta_ver   => p_type_meta_ver,
            p_category_prop_1 => ' ',
            p_category_prop_2 => ' ',
            p_category_prop_3 => ' ',
            p_category_prop_4 => ' ',
            p_category_prop_5 => ' ');

    END;

    -- Add threshold for this metric
    BEGIN
      INSERT INTO mgmt_metric_thresholds
        (target_guid, metric_guid, coll_name, key_value,
         warning_operator, warning_threshold, critical_operator, critical_threshold,
         num_occurences, num_warnings, num_criticals, eval_order, fixit_job)
      VALUES
        (p_target_guid, resp_stat_metric_guid, MGMT_GLOBAL.G_AVAIL_METRIC_NAME, ' ',
         0, ' ', 0, '0', 1, 0, 0, 0, '');
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        -- Ignore duplicate errors
        NULL;
    END;

    -- Add data for the Response/Status metric to the host name
    -- Collection time stamp should be in target timezone
    -- loadtimestamp should be in repository timezone
    -- INSERT INTO mgmt_metrics_raw
    --   (target_guid, metric_guid, collection_timestamp,
    --    key_value, value, string_value)
    -- VALUES
    --   (p_target_guid, resp_stat_metric_guid, 
    --    MGMT_GLOBAL.SYSDATE_TZRGN(:new.timezone_region), 
    --    ' ', 1, NULL);

  END ADD_HOST_AVAIL_METRIC;

----------------------------------------------------------------------

-- Procedure called by target before update trigger
PROCEDURE CHECK_CAT_PROP_CHANGES(v_cat_prop_name   IN VARCHAR2,
                                 v_target_name IN VARCHAR2,
                                 v_old_value   IN VARCHAR2,
                                 v_new_value   IN OUT VARCHAR2)
IS
BEGIN
  IF (v_old_value <> ' ' AND (v_new_value = ' ' OR v_new_value IS NULL)) THEN
     v_new_value := v_old_value;
     -- Don't log any messages for now as agent sends blank values
     -- for category properties that are dynamic and that cannot 
     -- be calculated
  END IF;
END;

----------------------------------------------------------------------

  --
  -- 
  --
  PROCEDURE ADD_TARGET(v_target_guid IN RAW)
  IS
    l_default_date DATE :=  TO_DATE('01-01-1900', 'MM-DD-YYYY');
  BEGIN
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_METRICS_1HOUR',        l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_METRICS_1DAY',         l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_RT_URL_1HOUR',         l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_RT_URL_DIST_1HOUR',    l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_RT_DOMAIN_1HOUR',      l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_RT_DOMAIN_DIST_1HOUR', l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_RT_IP_1HOUR',          l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_RT_IP_DIST_1HOUR',     l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_RT_URL_1DAY',          l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_RT_URL_DIST_1DAY',     l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_RT_DOMAIN_1DAY',       l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_RT_DOMAIN_DIST_1DAY',  l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_RT_IP_1DAY',           l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_RT_IP_DIST_1DAY',      l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_RT_PR_MAPPING_1HOUR',       l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_RT_PR_MAPPING_1DAY',        l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_RT_INCOMPLETE_LOADS_1HOUR', l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_RT_INCOMPLETE_LOADS_1DAY',  l_default_date );
    ADD_BOOTSTRAP_TIME(v_target_guid, l_default_date);
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_E2E_SUMMARY_1HOUR',    l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_E2E_SUMMARY_1DAY',     l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_E2E_DETAILS_1HOUR',    l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_E2E_DETAILS_1DAY',     l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_E2E_SQL_1HOUR',        l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_E2E_SQL_1DAY',         l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_E2E_JDBC_1HOUR',       l_default_date );
    ADD_ROLLUP_TIME(v_target_guid, 'MGMT_E2E_JDBC_1DAY',        l_default_date );

  END ADD_TARGET;
----------------------------------------------------------------------
--
-- Internal procedure to add multi-agent targets to 
-- mgmt_target_agent_assoc table. This is called from
-- END_MGMT_TARGETS_ROWSET. This logic should be moved
-- to an AFTER INSERT trigger once agent_guid is available
-- as part of mgmt_targets rowsets.
-- 
PROCEDURE PROCESS_MULTI_AGENT_TARGETS(p_emd_url_in IN VARCHAR2) IS

   l_agent_guid  MGMT_TARGETS.target_guid%TYPE;

  CURSOR target_cursor IS
    SELECT target_guid, monitoring_mode, timezone_region
      FROM MGMT_TARGETS
     WHERE emd_url = p_emd_url_in;

BEGIN
    -- Fetch the agent-guid
    SELECT target_guid INTO l_agent_guid
      FROM mgmt_targets
     WHERE target_type = mgmt_global.G_AGENT_TARGET_TYPE
       AND emd_url = p_emd_url_in;

    -- For each of the targets discovered from this agent, add an
    -- entry to MGMT_AGENT_TARGET_ASSOC table if this is a
    -- multi-agent target. Also, set this agent as the master-agent
    -- initially.
    FOR target IN target_cursor
    LOOP
      IF (is_multi_agent_target(target.monitoring_mode) = 1) THEN
         em_master_agent.initialize_master_agent(target.target_guid,
                                                 l_agent_guid,
                         MGMT_GLOBAL.SYSDATE_TZRGN(target.timezone_region));
         update_mat_agent_list(target.target_guid, p_emd_url_in);
      END IF;
    END LOOP;

  EXCEPTION
    WHEN OTHERS THEN
      -- Close the cursor if open
      IF (target_cursor%ISOPEN) THEN
        CLOSE target_cursor;
      END IF;
      RAISE;

END PROCESS_MULTI_AGENT_TARGETS;
----------------------------------------------------------------------
--
-- Checks whether a given target is a multi-agent target or not
-- Returns 1 if true, 0 otherwise.
--
FUNCTION is_multi_agent_target(p_target_name IN VARCHAR2,
                               p_target_type IN VARCHAR2)
                               RETURN NUMBER IS
  l_monitoring_mode NUMBER;
BEGIN

  BEGIN
    SELECT monitoring_mode
      INTO l_monitoring_mode
      FROM MGMT_TARGETS
      WHERE target_name = p_target_name AND
            target_type = p_target_type;
     
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
         raise_application_error(MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR,
             MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR_M ||
             ' Target name = ' || p_target_name || 
             ' and Target type = ' || p_target_type);
   END;     

   RETURN is_multi_agent_target(l_monitoring_mode);

END is_multi_agent_target;
----------------------------------------------------------------------
--
-- Given a target's monitoring mode,  it checks whether it is a
-- multi-agent target or not.
-- Returns 1 if true, 0 otherwise.
--
FUNCTION is_multi_agent_target(p_monitoring_mode IN NUMBER)
                               RETURN NUMBER IS
BEGIN
    -- Currently OMS_MEDIATED and AGENT_MEDIATED are the only
    -- multi-agent targets.
    IF ((p_monitoring_mode = MGMT_GLOBAL.G_MON_MODE_OMS_MEDIATED) OR
        (p_monitoring_mode = MGMT_GLOBAL.G_MON_MODE_AGENT_MEDIATED)) THEN
         RETURN 1; 
    END IF;

    RETURN 0;
                  
END is_multi_agent_target;
----------------------------------------------------------------------
-- The retention window procedures have been migrated to emd_maint_util
-- Please use emd_maint_util.get_..retention_windows directly
---
--- Gets the retention period for fetching raw data from repository
---
FUNCTION GET_RAW_RETENTION_WINDOW   RETURN NUMBER IS
BEGIN
  RETURN(EMD_MAINT_UTIL.GET_RAW_RETENTION_WINDOW) ;
END GET_RAW_RETENTION_WINDOW;
----------------------------------------------------------------------
---
--- Gets the retention period for fetching 1 hour data from repository
---
FUNCTION GET_1HOUR_RETENTION_WINDOW RETURN NUMBER IS
BEGIN
  RETURN(EMD_MAINT_UTIL.GET_1HOUR_RETENTION_WINDOW) ;
END GET_1HOUR_RETENTION_WINDOW;
----------------------------------------------------------------------
---
--- Gets the retention period for fetching 1 day data from repository
---
FUNCTION GET_1DAY_RETENTION_WINDOW RETURN NUMBER IS
BEGIN
  RETURN(EMD_MAINT_UTIL.GET_1DAY_RETENTION_WINDOW) ;
END GET_1DAY_RETENTION_WINDOW;
----------------------------------------------------------------------
---
--- Attempts to add an agent to the list of agents of a Multi-Agent
--- Target (MAT). If the specified agent is already present, it ignores
--- the entry. 
--
--- ERROR CODES:
---      TARGET_DOES_NOT_EXIST_ERR: If there is corresponding agent for the
---      specified emd_url.
---
PROCEDURE UPDATE_MAT_AGENT_LIST(p_target_guid IN RAW,
                                p_emd_url IN VARCHAR2) IS
    l_agent_guid  MGMT_TARGETS.target_guid%TYPE;
BEGIN
   SELECT target_guid INTO l_agent_guid
     FROM MGMT_TARGETS
    WHERE emd_url = p_emd_url AND
          target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE;

    INSERT into MGMT_TARGET_AGENT_ASSOC(target_guid, agent_guid)
       VALUES (p_target_guid, l_agent_guid);

    -- Modify the monitoring status of the agent if needed
    em_master_agent.update_agent_mon_status(p_target_guid, l_agent_guid);


    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
        NULL;
                                          
END UPDATE_MAT_AGENT_LIST;
-------------------------------------------------------------------------------
--
-- Adds a target addition callback
--
PROCEDURE ADD_TARGET_ADDITION_CALLBACK(p_callback_name IN VARCHAR2,
                                       p_target_type IN VARCHAR2 DEFAULT ' ')
IS
l_error_message VARCHAR2(2000) ;
BEGIN
  IF NOT EM_CHECK.is_valid_signature(
           p_callback_name,
           mgmt_short_string_array('VARCHAR2','VARCHAR2','RAW'),
           l_error_message)
  THEN
    raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
      l_error_message);
  END IF ;
  INSERT INTO mgmt_target_add_callbacks
     (callback_name, target_type)
  VALUES
     (p_callback_name, NVL(p_target_type, ' '));

END;
--------------------------------------------------------------------------------

--
-- Execute target addition callbacks
--
PROCEDURE EXEC_TARGET_ADDITION_CALLBACKS(p_target_name IN VARCHAR2,
                                         p_target_type IN VARCHAR2,
                                         p_target_guid IN RAW)
IS
BEGIN
  -- Call the target addition callbacks
  FOR callback IN (SELECT callback_name 
                    FROM  MGMT_TARGET_ADD_CALLBACKS
                   WHERE  target_type = p_target_type OR
                          target_type = ' '
                   ORDER BY callback_name ) LOOP
  BEGIN
      EXECUTE IMMEDIATE 
        'BEGIN ' || EM_CHECK.qualified_sql_name(callback.callback_name)
         || '(:1, :2, :3); END; ' USING
         p_target_name, p_target_type, p_target_guid;
  EXCEPTION
      -- Ignore exceptions so that main transaction is not affected; we do not
      -- treat callback exceptions on par with repository exceptions. In other
      -- words, we will not let some callback mess up repository operations.
      WHEN OTHERS THEN
          mgmt_log.log_error(v_module_name_in => 'LOADER',
                             v_error_code_in  =>  0,
                             v_error_msg_in   => SUBSTR(SQLERRM, 1, 2000));

  END;
  END LOOP;
  
END;

------------------------------------------------------------------------------
--
-- Deletes a target addition callback
--
PROCEDURE DEL_TARGET_ADDITION_CALLBACK(p_callback_name IN VARCHAR2,
                                       p_target_type IN VARCHAR2 DEFAULT ' ')
IS
  l_mod_target_type mgmt_targets.target_type%TYPE := NVL(p_target_type, ' ');
BEGIN
  DELETE FROM mgmt_target_add_callbacks
        WHERE callback_name = p_callback_name
          AND target_type = l_mod_target_type;
END;
-----------------------------------------------------------------------------

-- Returns the URL of the currently loading Agent. One can
-- use this to do some special processing based on which
-- agent is loading the data. For example, for multi-agent
-- targets, one can use this to filter out non-master data.
FUNCTION GET_CURRENT_EMD_URL RETURN VARCHAR2
IS
BEGIN
  RETURN p_current_emd_url;
END;

------------------------------------------------------------------------------

-- Nobody should call this except for testing purposes
PROCEDURE SET_CURRENT_EMD_URL(v_emd_url IN VARCHAR2)
IS
BEGIN
  p_current_emd_url := v_emd_url;
END;


------------------------------------------------------------------------------

  -- BEGIN Purge policies callback procedures
  PROCEDURE STRING_HISTORY_PURGE(pcb_params IN OUT MGMT_PURGE_CALLBACK_PARAMS) IS
    l_purge_rowids p_rowid_list_type;
    l_rows_purged NUMBER := 0;
    CURSOR purge_string_hist (c_purge_till_time DATE) IS
    SELECT max_str_hist.ROWID 
      FROM (SELECT ROWID, 
                   target_guid, 
                   metric_guid, 
                   key_value, 
                   collection_timestamp, 
                   MAX(collection_timestamp) OVER ( PARTITION BY 
                     target_guid,metric_guid,key_value) max_coll 
              FROM MGMT_STRING_METRIC_HISTORY 
             WHERE collection_timestamp < c_purge_till_time
           ) max_str_hist 
     WHERE collection_timestamp < max_coll ; 

  BEGIN

    DBMS_APPLICATION_INFO.SET_MODULE(
        module_name => 'Oracle Enterprise Manager.string history purge',
        action_name => 'start');

    OPEN purge_string_hist(pcb_params.purge_upto_time);
    LOOP
      -- Bulk collect row ids
      FETCH purge_string_hist BULK COLLECT INTO l_purge_rowids LIMIT p_purge_batchsize;

      -- Exit when no more row ids fetched
      EXIT WHEN l_purge_rowids.COUNT <= 0;

      -- BULK delete
      FORALL i IN l_purge_rowids.FIRST..l_purge_rowids.LAST
        DELETE FROM MGMT_STRING_METRIC_HISTORY WHERE ROWID = l_purge_rowids(i);

      COMMIT;

      l_rows_purged := l_rows_purged + l_purge_rowids.COUNT;

      l_purge_rowids.DELETE;

    END LOOP;

    -- Close the cursor if open
    IF (purge_string_hist%ISOPEN) THEN
      CLOSE purge_string_hist;
    END IF;

    pcb_params.callback_result := 0;
    pcb_params.rows_processed  := l_rows_purged;
    pcb_params.error_code      := 0;
    pcb_params.error_msg       := NULL;

    DBMS_APPLICATION_INFO.SET_MODULE(null,null); 

  EXCEPTION
    WHEN OTHERS THEN
      p_ErrMsg := SUBSTR(SQLERRM, 1, 2048);
      DBMS_APPLICATION_INFO.READ_MODULE(p_Module,p_Action);
      MGMT_LOG.LOG_ERROR('PURGE',null,
          'Error during '||p_Module||' : '||p_ErrMsg,p_Action);
      -- Close the cursor if open
      IF (purge_string_hist%ISOPEN) THEN
        CLOSE purge_string_hist;
      END IF; 

  END STRING_HISTORY_PURGE;

------------------------------------------------------------------------------

  PROCEDURE METRIC_ERROR_PURGE(pcb_params IN OUT MGMT_PURGE_CALLBACK_PARAMS) IS
    l_purge_rowids p_rowid_list_type;
    l_rows_purged NUMBER := 0;
    CURSOR metric_error_cursor (c_purge_till_time DATE) IS
    SELECT purge_errors.ROWID 
      FROM  (SELECT target_guid, 
                    metric_guid, 
                    collection_timestamp, 
                    ROWID, 
                    MAX(DECODE(metric_error_message, NULL,collection_timestamp,NULL)) 
                    OVER ( PARTITION BY target_guid,metric_guid) max_coll 
               FROM MGMT_METRIC_ERRORS 
              WHERE collection_timestamp < c_purge_till_time
             ) purge_errors 
     WHERE collection_timestamp < max_coll ;

  BEGIN

    DBMS_APPLICATION_INFO.SET_MODULE(
        module_name => 'Oracle Enterprise Manager.metric error purge',
        action_name => 'start');

    OPEN metric_error_cursor(pcb_params.purge_upto_time);
    LOOP
      -- Bulk collect row ids
      FETCH metric_error_cursor BULK COLLECT INTO l_purge_rowids LIMIT p_purge_batchsize;

      -- Exit when no more row ids fetched
      EXIT WHEN l_purge_rowids.COUNT <= 0;

      -- BULK delete
      FORALL i IN l_purge_rowids.FIRST..l_purge_rowids.LAST
        DELETE FROM MGMT_METRIC_ERRORS WHERE ROWID = l_purge_rowids(i);

      COMMIT;

      l_rows_purged := l_rows_purged + l_purge_rowids.COUNT;

      l_purge_rowids.DELETE;

    END LOOP;

    -- Close the cursor if open
    IF (metric_error_cursor%ISOPEN) THEN
      CLOSE metric_error_cursor;
    END IF;

    pcb_params.callback_result := 0;
    pcb_params.rows_processed  := l_rows_purged;
    pcb_params.error_code      := 0;
    pcb_params.error_msg       := NULL;

    DBMS_APPLICATION_INFO.SET_MODULE(null,null); 

  EXCEPTION
    WHEN OTHERS THEN
      p_ErrMsg := SUBSTR(SQLERRM, 1, 2048);
      DBMS_APPLICATION_INFO.READ_MODULE(p_Module,p_Action);
      MGMT_LOG.LOG_ERROR('PURGE',null,
          'Error during '||p_Module||' : '||p_ErrMsg,p_Action);
      -- Close the cursor if open
      IF (metric_error_cursor%ISOPEN) THEN
        CLOSE metric_error_cursor;
      END IF;

  END METRIC_ERROR_PURGE;

------------------------------------------------------------------------------

  PROCEDURE UPDATE_MET_LABEL_NLSID(p_target_type_in IN VARCHAR2) IS
    CURSOR metrics_to_update_cursor (targetType VARCHAR2) IS
        SELECT distinct m1.type_meta_ver, m1.metric_guid, m1.category_prop_1, 
               m1.category_prop_2, m1.category_prop_3, m1.category_prop_4, 
               m1.category_prop_5, m2.column_label, m2.column_label_nlsid, 
               m2.metric_label, m2.metric_label_nlsid,
               m2.short_name, m2.short_name_nlsid,
               m2.unit,m2.unit_nlsid
          FROM MGMT_METRICS m1, 
               (SELECT distinct m4.type_meta_ver, m4.metric_guid, target_type, column_label, 
                       column_label_nlsid, metric_label, metric_label_nlsid,
                       short_name, short_name_nlsid
                       ,unit, unit_nlsid
                  FROM (  SELECT max(type_meta_ver) type_meta_ver, metric_guid
                            FROM MGMT_METRICS
                           WHERE target_type = targetType
                        GROUP BY metric_guid 
                       ) m3,
                       (  SELECT type_meta_ver, metric_guid, target_type, column_label, 
                                 column_label_nlsid, metric_label, metric_label_nlsid, 
                                short_name, short_name_nlsid
                                ,unit, unit_nlsid
                            FROM MGMT_METRICS 
                           WHERE target_type = targetType 
                       ) m4
                 WHERE m3.metric_guid = m4.metric_guid 
                   AND m3.type_meta_ver = m4.type_meta_ver
              ORDER BY m4.type_meta_ver, m4.metric_guid, target_type, column_label,
                       column_label_nlsid, metric_label, metric_label_nlsid,
                       short_name, short_name_nlsid
               ) m2
         WHERE m1.target_type = m2.target_type
           AND m1.metric_guid = m2.metric_guid
           AND m1.type_meta_ver < m2.type_meta_ver
           AND ((m1.column_label IS NULL AND m2.column_label IS NOT NULL) OR
                (m1.column_label IS NOT NULL AND m2.column_label IS NULL) OR
                m1.column_label <> m2.column_label OR
                (m1.column_label_nlsid IS NULL AND m2.column_label_nlsid IS NOT NULL) OR
                (m1.column_label_nlsid IS NOT NULL AND m2.column_label_nlsid IS NULL) OR
                m1.column_label_nlsid <> m2.column_label_nlsid OR
                (m1.metric_label IS NULL AND m2.metric_label IS NOT NULL) OR
                (m1.metric_label IS NOT NULL AND m2.metric_label IS NULL) OR
                m1.metric_label <> m2.metric_label OR
                (m1.metric_label_nlsid IS NULL AND m2.metric_label_nlsid IS NOT NULL) OR
                (m1.metric_label_nlsid IS NOT NULL AND m2.metric_label_nlsid IS NULL) OR
                m1.metric_label_nlsid <> m2.metric_label_nlsid OR
                (m1.short_name IS NULL AND m2.short_name IS NOT NULL) OR
                (m1.short_name IS NOT NULL AND m2.short_name IS NULL) OR
                m1.short_name <> m2.short_name OR
                (m1.short_name_nlsid IS NULL AND m2.short_name_nlsid IS NOT NULL) OR
                (m1.short_name_nlsid IS NOT NULL AND m2.short_name_nlsid IS NULL) OR
                m1.short_name_nlsid <> m2.short_name_nlsid OR
                (m1.unit IS NULL AND m2.unit IS NOT NULL) OR
                (m1.unit IS NOT NULL AND m2.unit IS NULL) OR
                m1.unit <> m2.unit OR
                (m1.unit_nlsid IS NULL AND m2.unit_nlsid IS NOT NULL) OR
                (m1.unit_nlsid IS NOT NULL AND m2.unit_nlsid IS NULL) OR
                m1.unit_nlsid <> m2.unit_nlsid
               );

    l_num_versions NUMBER := 0;

    v_upd_type_meta_vers  p_varchar2_256_list_type;
    v_upd_metric_guids    p_guid_list_type;
    v_upd_category_prop_1 p_varchar2_256_list_type;
    v_upd_category_prop_2 p_varchar2_256_list_type;
    v_upd_category_prop_3 p_varchar2_256_list_type;
    v_upd_category_prop_4 p_varchar2_256_list_type;
    v_upd_category_prop_5 p_varchar2_256_list_type;
    v_column_labels       p_varchar2_256_list_type;
    v_column_label_nlsids p_varchar2_256_list_type;
    v_metric_labels       p_varchar2_256_list_type;
    v_metric_label_nlsids p_varchar2_256_list_type;
    v_short_names         p_varchar2_256_list_type;
    v_short_name_nlsids   p_varchar2_256_list_type;
    v_units               p_varchar2_256_list_type;
    v_unit_nlsids         p_varchar2_256_list_type;

    i BINARY_INTEGER;

  BEGIN
    -- Get number of versions for the given target type
    SELECT COUNT(*) INTO l_num_versions
      FROM MGMT_METADATA_SETS
     WHERE target_type = p_target_type_in
       AND table_name = 'MGMT_METRICS';

    -- Unless there's more than one version, we have no metrics to update
    IF (l_num_versions < 2) THEN
      return;
    END IF;

    -- Fetch the metrics that need to be updated. 
    OPEN metrics_to_update_cursor(p_target_type_in);
    FETCH metrics_to_update_cursor BULK COLLECT INTO v_upd_type_meta_vers,
                                                     v_upd_metric_guids,
                                                     v_upd_category_prop_1,
                                                     v_upd_category_prop_2,
                                                     v_upd_category_prop_3,
                                                     v_upd_category_prop_4,
                                                     v_upd_category_prop_5,
                                                     v_column_labels,
                                                     v_column_label_nlsids,
                                                     v_metric_labels,
                                                     v_metric_label_nlsids,
                                                     v_short_names,
                                                     v_short_name_nlsids,
                                                     v_units,
                                                     v_unit_nlsids ;

    CLOSE metrics_to_update_cursor;

    IF v_upd_metric_guids.COUNT <= 0 THEN
      RETURN;        -- no metrics to update
    END IF;

    -- Commit to avoid deadlock Bug:4055992
    COMMIT ;

    FOR i IN v_upd_metric_guids.FIRST..v_upd_metric_guids.LAST
    LOOP
      UPDATE MGMT_METRICS
         SET column_label = v_column_labels(i),
             column_label_nlsid = v_column_label_nlsids(i),
             metric_label = v_metric_labels(i),
             metric_label_nlsid = v_metric_label_nlsids(i),
             short_name = v_short_names(i),
             short_name_nlsid = v_short_name_nlsids(i),
             unit = v_units(i),
             unit_nlsid = v_unit_nlsids(i)
       WHERE target_type = p_target_type_in
         AND metric_guid = v_upd_metric_guids(i)
         AND type_meta_ver = v_upd_type_meta_vers(i)
         AND category_prop_1 = v_upd_category_prop_1(i)
         AND category_prop_2 = v_upd_category_prop_2(i)
         AND category_prop_3 = v_upd_category_prop_3(i)
         AND category_prop_4 = v_upd_category_prop_4(i)
         AND category_prop_5 = v_upd_category_prop_5(i);

        -- Commit one record at a time to avoid deadlocks with 
        -- threads inserting/updating metrics information.
        COMMIT;            
    END LOOP;

  END UPDATE_MET_LABEL_NLSID;

------------------------------------------------------------------------------
  PROCEDURE ADD_TARGET(p_upsert IN NUMBER,
                       p_target_name IN VARCHAR2,
                       p_target_type IN VARCHAR2,
                       p_properties IN MGMT_TARGET_PROPERTY_LIST,
                       p_target_display_name IN VARCHAR2,
                       p_type_meta_ver      IN VARCHAR2,
                       p_target_credentials IN MGMT_TARGET_CRED_ARRAY DEFAULT NULL,
                       p_member_targets IN SMP_EMD_NVPAIR_ARRAY DEFAULT NULL) 
  IS
    l_upsert NUMBER := p_upsert;
    l_properties MGMT_TARGET_PROPERTY_LIST := MGMT_TARGET_PROPERTY_LIST();
    l_target_guid MGMT_TARGETS.target_guid%TYPE;
  BEGIN
    BEGIN
       MGMT_TARGET.ADD_TARGET(p_target_name, p_target_type, NULL, p_properties,
                           NULL, NULL, NULL, p_target_display_name,
                           NULL, TO_CHAR(SYSTIMESTAMP,'TZR'), 
                           MGMT_GLOBAL.G_MON_MODE_DEFAULT,
                           p_type_meta_ver, ' ', ' ', ' ', ' ', ' ',1,
                           MGMT_GLOBAL.G_DEFAULT_TARGET_OWNER);
    EXCEPTION
      WHEN MGMT_GLOBAL.TARGET_ALREADY_EXISTS THEN
      IF (l_upsert = 1) THEN
         SELECT target_guid INTO l_target_guid
          FROM mgmt_targets
         WHERE target_name = p_target_name
           AND target_type = p_target_type;

         -- get alll existing instance properties and merge with new properties
         IF p_properties IS NULL THEN
            SELECT mgmt_target_property(property_name,
                                        property_type,
                                        property_value)
              BULK COLLECT INTO l_properties
              FROM mgmt_target_properties
             WHERE property_type = 'INSTANCE'
               AND target_guid = l_target_guid;
         ELSE
            SELECT mgmt_target_property(property_name,
                                        property_type,
                                        property_value)
              BULK COLLECT INTO l_properties
              FROM mgmt_target_properties
             WHERE property_type = 'INSTANCE'
               AND target_guid = l_target_guid
               AND property_name NOT IN 
                   ( SELECT property_name
                     FROM TABLE(CAST(p_properties AS MGMT_TARGET_PROPERTY_LIST))
                     WHERE property_type = 'INSTANCE');
 
            FOR indx IN 1..p_properties.COUNT LOOP
               l_properties.extend(1);
               l_properties(l_properties.COUNT) := p_properties(indx);
            END LOOP;
         END IF;

         MGMT_TARGET.MODIFY_TARGET(p_target_name, p_target_type, l_properties,
                                   NULL, NULL, NULL,
                                   p_target_display_name);
      ELSE
         RAISE;
      END IF;
    END;
   
    IF (p_target_credentials IS NOT NULL) THEN
         MGMT_CREDENTIAL.SET_TARGET_CREDENTIALS(p_target_credentials);
    END IF;

    IF (p_member_targets IS NOT NULL) THEN
        MODIFY_MEMBERSHIP(p_target_name, p_target_type, p_member_targets);
    END IF;

  END ADD_TARGET;
-------------------------------------------------------------------------------
  PROCEDURE MODIFY_MEMBERSHIP(p_parent_target_name IN VARCHAR2,
                              p_parent_target_type IN VARCHAR2,
                              p_targets_to_add SMP_EMD_NVPAIR_ARRAY) IS
    l_parent_guid MGMT_TARGETS.target_guid%TYPE;
    l_member_guid MGMT_TARGETS.target_guid%TYPE;
    l_member_name MGMT_TARGETS.target_name%TYPE;
    l_member_type MGMT_TARGETS.target_type%TYPE;
    l_affected_target_assoc MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
  BEGIN
    IF p_targets_to_add IS NOT NULL THEN
        -- Create a MEMBER type Target Associations, merge with existing one
        em_assoc.create_target_assocs(
                         p_assoc_def_name => mgmt_assoc.ASSOC_DEF_CONTAINS,
                         p_source_target_name => p_parent_target_name,
                         p_source_target_type => p_parent_target_type,
                         p_assoc_targets_list =>  p_targets_to_add,
                         p_suppress_error =>    true );  
    END IF;    
  END MODIFY_MEMBERSHIP;
-------------------------------------------------------------------------------
-- Procedure to change the emd_url for a target.
PROCEDURE CHANGE_TARGET_EMD_URL(v_target_name IN VARCHAR2,
                                v_target_type IN VARCHAR2,
                                v_old_emd_url IN VARCHAR2,
                                v_new_emd_url IN VARCHAR2)
IS
  l_new_host_name   MGMT_TARGETS.host_name%TYPE;
  l_target_guid     MGMT_TARGETS.target_guid%TYPE;
  targets_old_timezone MGMT_TARGETS.timezone_region%TYPE;
  l_new_agent_timezone MGMT_TARGETS.timezone_region%TYPE;

BEGIN

   l_target_guid := mgmt_target.get_target_guid( v_target_name,
                                                 v_target_type);

  -- Get the new host name of v_new_url
  SELECT host_name, timezone_region 
    INTO l_new_host_name, l_new_agent_timezone
     FROM MGMT_TARGETS
      WHERE emd_url = v_new_emd_url 
        AND target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE ;
 

   -- Get the old agent's time zone region
   SELECT timezone_region  
      INTO targets_old_timezone
        FROM MGMT_TARGETS
         WHERE target_name = v_target_name
          AND  target_type = v_target_type
          AND  emd_url = v_old_emd_url;

  -- Insert an entry in change_agent_url to fool the
  -- mgmt_targets update trigger
  set_emd_url_change(v_old_emd_url, v_new_emd_url);

  -- Make the entry in mgmt_targets point to the new agent
  UPDATE mgmt_targets
     SET host_name = l_new_host_name, emd_url = v_new_emd_url
      WHERE target_guid = l_target_guid;

  -- remove the change url entry
  clear_emd_url_change(v_old_emd_url, v_new_emd_url);

  -- Set the timezone of the target to new agent
   mgmt_target.set_target_tzrgn(v_target_name, v_target_type,
                                l_new_agent_timezone);

  -- update MGMT_DUPLICATES table to change emd_url if the target
  -- is present here.

      UPDATE MGMT_DUPLICATE_TARGETS
         SET resolved_time = MGMT_GLOBAL.SYSDATE_TZRGN(timezone_region)
      WHERE duplicate_emd_url = v_new_emd_url
        AND target_guid = l_target_guid;

   -- Insert an entry in mgmt_duplicate_targets for old agent
   UPDATE MGMT_DUPLICATE_TARGETS
       SET detection_time = MGMT_GLOBAL.SYSDATE_TZRGN(targets_old_timezone),
           resolved_time = NULL
   WHERE target_guid = l_target_guid
     AND duplicate_emd_url = v_old_emd_url;
 
   IF (SQL%ROWCOUNT = 0) THEN
      INSERT INTO mgmt_duplicate_targets
         (target_name, target_type, target_guid, duplicate_emd_url,
         timezone_region, detection_time)
      VALUES
         (v_target_name, v_target_type, l_target_guid, v_old_emd_url,
         targets_old_timezone,
         MGMT_GLOBAL.SYSDATE_TZRGN(targets_old_timezone));
   END IF;

END CHANGE_TARGET_EMD_URL; 

-- Procedure to get table object 
  PROCEDURE GET_TABLE_OBJECT(v_table_name  IN  VARCHAR2,
                             v_pk_cur      OUT CURSORTYPE,
                             v_lob_cur     OUT CURSORTYPE,
                             v_handler_cur OUT CURSORTYPE)
  IS
    l_table_row_list TABLE_ROW_LIST := TABLE_ROW_LIST();
  BEGIN
    FOR rec IN (  
        SELECT utc.column_name, utc.data_type, ucc.position,
               utc.data_default        
          FROM user_tab_columns utc, user_constraints uc,       
               user_cons_columns ucc                            
         WHERE utc.table_name      = upper(v_table_name)                        
           AND uc.table_name       = utc.table_name               
           AND uc.constraint_type  = 'P'                          
           AND ucc.table_name      = utc.table_name              
           AND ucc.constraint_name = uc.constraint_name          
           AND ucc.column_name     = utc.column_name             
          ORDER BY ucc.position)
    LOOP
          l_table_row_list.extend; 
          l_table_row_list(l_table_row_list.last) := TABLE_ROW(rec.column_name,
                                                               rec.data_type,
                                                               rec.position,
                                                               substr(rec.data_default,1,2000)); 
        END LOOP;          
                  
    OPEN v_pk_cur FOR 
     SELECT column_name, data_type, position, data_default
           FROM THE(SELECT  CAST(l_table_row_list AS TABLE_ROW_LIST ) FROM DUAL ) ;
         
   OPEN v_lob_cur FOR 
     SELECT utc.column_name, utc.data_type  
       FROM user_tab_columns utc                             
      WHERE utc.table_name = upper(v_table_name)                        
        AND (utc.data_type = 'CLOB' OR utc.data_type = 'BLOB'); 

    -- Return PLSQL handler cursor
    OPEN v_handler_cur FOR
      SELECT protocol_version, handler_type, handler_info  
        FROM mgmt_rowset_handlers
       WHERE rowset_name = upper(v_table_name)
       ORDER BY protocol_version;

  END get_table_object ; 

-------------------------------------------------------------------------

----------------- BEGIN API FOR HANDLERS FOR ROWSETS --------------------

  -- Procedure to associate a handler to a rowset
  -- NOTE : Does not throw any exception, if the association already exits
  PROCEDURE add_handler_for_rowset(
      p_rowset_name      IN VARCHAR2,
      p_protocol_version IN VARCHAR2 DEFAULT ' ',
      p_handler_type     IN NUMBER,
      p_handler_info     IN VARCHAR2)
  IS
  BEGIN

    BEGIN
      INSERT INTO mgmt_rowset_handlers
        (rowset_name, protocol_version, handler_type, 
         handler_info)
       VALUES
        (p_rowset_name, p_protocol_version, p_handler_type,
         p_handler_info);
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        -- If a handler is already defined, overwrite it.
        UPDATE mgmt_rowset_handlers
           SET handler_info = p_handler_info
         WHERE rowset_name = p_rowset_name
           AND handler_type = p_handler_type
           AND protocol_version = p_protocol_version;
    END;

  END add_handler_for_rowset;

  -- Procedure to add a PL/SQL handler to a rowset
  PROCEDURE add_plsql_handler_for_rowset(
      p_rowset_name      IN VARCHAR2,
      p_protocol_version IN VARCHAR2 DEFAULT ' ',
      p_proc_name        IN VARCHAR2,
      p_handler_type     IN NUMBER DEFAULT G_ROWSET_HANDLER_PLSQL)
  IS
  BEGIN

    add_handler_for_rowset(
        p_rowset_name => p_rowset_name,
        p_protocol_version => p_protocol_version,
        p_handler_type => p_handler_type,
        p_handler_info => p_proc_name);

  END add_plsql_handler_for_rowset;

  -- Procedure to remove the handler associated with a rowset.
  PROCEDURE remove_handler_for_rowset(
      p_rowset_name      IN VARCHAR2,
      p_protocol_version IN VARCHAR2 DEFAULT ' ',
      p_handler_type     IN NUMBER DEFAULT G_ROWSET_HANDLER_PLSQL)
  IS
  BEGIN
    DELETE FROM mgmt_rowset_handlers
     WHERE rowset_name = p_rowset_name
       AND protocol_version = p_protocol_version
       AND handler_type = p_handler_type;
  END remove_handler_for_rowset;

----------------- BEGIN API FOR HANDLERS FOR ROWSETS --------------------

-- PURPOSE
--   Procedure to enqueue a filename
--
-- NOTES
--
PROCEDURE ENQUEUE(p_designator IN VARCHAR2,
                p_mesg IN VARCHAR2,
                p_delay IN INTEGER DEFAULT DBMS_AQ.NO_DELAY)
IS
  enq_options         dbms_aq.enqueue_options_t;
  qMsg_properties     dbms_aq.message_properties_t;
  qMsg_handle         RAW(16);
  recipient           sys.aq$_agent;
BEGIN

  -- Set up the designator as the recipient name
  recipient := sys.aq$_agent(p_designator, NULL, 0);
  qMsg_properties.recipient_list(1) := recipient;

  -- Set the delay in seconds after which message becomes
  -- available for dequeue
  qMsg_properties.delay := p_delay;

  -- Add the message to the queue
  DBMS_AQ.ENQUEUE(queue_name => G_QUEUE_NAME,
                  enqueue_options => enq_options,       
                  message_properties => qMsg_properties,  
                  payload => utl_raw.cast_to_raw(p_mesg),
                  msgid => qMsg_handle);
END ENQUEUE;

-- PURPOSE
--   Procedure to generate and enqueue a filename
--
-- NOTES
--
PROCEDURE RECEIVE(p_designator IN VARCHAR2,
                p_filename OUT VARCHAR2)
IS
  seq_no              number(10);
BEGIN

  select mgmt_loader_sequence.nextval into seq_no
  from dual;

  p_filename := p_designator||seq_no;

  -- Add the message to the queue
  ENQUEUE(p_designator, p_filename);

EXCEPTION
WHEN OTHERS
THEN
    mgmt_log.log_error('LOADER.RECEIVER', 
                       MGMT_GLOBAL.UNEXPECTED_ERR,
                       MGMT_GLOBAL.UNEXPECTED_ERR_M || 
                           'LOADER_QUEUE(' || p_designator || '/' || 
                           p_filename || ') ' || SQLERRM, 
                       'MGMT',
                       'RECEIVE_FILE');
END RECEIVE;


PROCEDURE LISTEN(p_designators IN SMP_EMD_STRING_ARRAY,
                      p_qtimeout IN NUMBER,
                      p_qname OUT VARCHAR2)
IS
  agents          dbms_aq.aq$_agent_list_t;
  agent           sys.aq$_agent;
BEGIN

  FOR i IN p_designators.FIRST..p_designators.LAST
  LOOP
    agents(i) := sys.aq$_agent(p_designators(i), G_QUEUE_NAME, 0);
  END LOOP;

  -- This raises an ORA-25254 exception if there is nothing in the queue
  DBMS_AQ.LISTEN (agents, p_qtimeout, agent);
  p_qname := agent.name;

END LISTEN;


PROCEDURE DEQUEUE(p_designator IN VARCHAR2, 
                  p_qtimeout IN NUMBER,
                  p_remove IN NUMBER,
                  p_mesg OUT VARCHAR2,
                  p_mid IN OUT RAW)
IS
  dq_options      dbms_aq.dequeue_options_t;
  qMsg_properties dbms_aq.message_properties_t;
  mesg_raw        RAW(16);
BEGIN

  IF (p_remove = 0) THEN
     dq_options.dequeue_mode := DBMS_AQ.BROWSE;
  END IF;
  dq_options.consumer_name := p_designator;
  dq_options.wait := p_qtimeout;
  dq_options.navigation := DBMS_AQ.FIRST_MESSAGE;
  dq_options.msgid := p_mid;

  -- Dequeue a file 
  DBMS_AQ.DEQUEUE(queue_name => G_QUEUE_NAME,
                  dequeue_options    => dq_options,
                  message_properties => qMsg_properties,
                  payload            => mesg_raw,
                  msgid              => p_mid);

  p_mesg := utl_raw.cast_to_varchar2(mesg_raw);

END DEQUEUE;


PROCEDURE DEQUEUE_ADMIN(p_designator IN VARCHAR2, 
                  p_qtimeout IN NUMBER, 
                  p_mesg OUT VARCHAR2)
IS
  mid             RAW(16);
BEGIN
    DEQUEUE(p_designator, p_qtimeout, 1, p_mesg, mid);
END DEQUEUE_ADMIN;


PROCEDURE DEQUEUE_BROWSE(p_designator IN VARCHAR2, 
                  p_qtimeout IN NUMBER, 
                  p_filename OUT VARCHAR2,
                  p_mid OUT RAW)
IS
mid               RAW(16) := NULL;
BEGIN
    DEQUEUE(p_designator, p_qtimeout, 0, p_filename, mid);
    p_mid := mid;

    -- Lock the designator to prevent anyone else from processing
    -- files of this designator
    OBTAIN_LOCK_FOR_DESIGNATOR(p_designator);

END DEQUEUE_BROWSE;


PROCEDURE DEQUEUE_CONSUME(p_designator IN VARCHAR2, 
                  p_qtimeout IN NUMBER, 
                  p_mid IN RAW)
IS
filename          VARCHAR2(30);
mid               RAW(16);
BEGIN
    mid := p_mid;
    DEQUEUE(p_designator, p_qtimeout, 1, filename, mid);

    -- Release lock held for this designator
    RELEASE_LOCK_FOR_DESIGNATOR;

END DEQUEUE_CONSUME;

PROCEDURE OBTAIN_LOCK_FOR_DESIGNATOR(p_designator IN VARCHAR2) 
IS
BEGIN
    -- Release any lock we might be holding inadvertantly
    -- Note this should never happen
    IF (G_DESIGNATOR_LOCK != NULL) THEN
        RELEASE_LOCK_FOR_DESIGNATOR;
    END IF;

    BEGIN

        G_DESIGNATOR_LOCK :=
          MGMT_LOCK_UTIL.get_exclusive_lock(p_lock_type  => 'EMD_LOADER_DESIGNATOR',
                                            p_lock_value => p_designator,
                                            p_timeout_secs => 0,
                                            p_release_on_commit => FALSE);
    EXCEPTION
      WHEN OTHERS THEN
         raise_application_error(MGMT_GLOBAL.LOAD_DESIGNATOR_LOCK_ERR,
             MGMT_GLOBAL.LOAD_DESIGNATOR_LOCK_ERR_M|| p_designator || ': '|| SQLERRM);
    END;
END OBTAIN_LOCK_FOR_DESIGNATOR;


PROCEDURE RELEASE_LOCK_FOR_DESIGNATOR
IS
lock_released     NUMBER;
BEGIN
    BEGIN
        lock_released :=  
          MGMT_LOCK_UTIL.release_lock(p_lock_handle => G_DESIGNATOR_LOCK);

    EXCEPTION
      WHEN OTHERS THEN
         raise_application_error(MGMT_GLOBAL.LOAD_DESIGNATOR_RELEASE_ERR,
             MGMT_GLOBAL.LOAD_DESIGNATOR_RELEASE_ERR_M|| SQLERRM);
    END;

    IF (lock_released != 0) THEN
       raise_application_error(MGMT_GLOBAL.LOAD_DESIGNATOR_RELEASE_ERR,
          MGMT_GLOBAL.LOAD_DESIGNATOR_RELEASE_ERR_M||
          ' mgmt_lock_util.release_lock returned '||lock_released||
          ' handle='||G_DESIGNATOR_LOCK);
    END IF;
END RELEASE_LOCK_FOR_DESIGNATOR;


PROCEDURE REGISTER_SHAREDFS(p_oms_id IN NUMBER,
                       p_admin_qname OUT VARCHAR2)
IS
  admin_qname    VARCHAR2(30);
  noms           NUMBER;
  oms_ids        SMP_EMD_INTEGER_ARRAY;
  designators    SMP_EMD_STRING_ARRAY;
  backlog        SMP_EMD_INTEGER_ARRAY;
  qMsg           VARCHAR2(30);
  idx            INTEGER;
  oms_name       VARCHAR2(256);
BEGIN

  admin_qname := 'ADM' || p_oms_id;
  p_admin_qname := admin_qname;

  -- Lock the queue table
  LOCK TABLE MGMT_LOADER_DESIGNATORS IN EXCLUSIVE MODE;

  CLEANUP_ADMIN_MESSAGES;

  -- Update mgmt_oms_parameters to indicate this
  -- OMS is running sharedfs mode
  SELECT host_url
  INTO oms_name
  FROM mgmt_failover_table
  WHERE failover_id = p_oms_id;

  UPDATE_LOADER_PARAMS(oms_name,
     SMP_EMD_NVPAIR_ARRAY(SMP_EMD_NVPAIR(HA_MODE_PARAM, HA_MODE_SHAREDFS)));

  -- Get the list of OMS that are assigned designators
  -- The last condition is not really required as OMS
  -- can never register twice. But adding this condition
  -- gives us a way to redistribute load without bouncing
  -- any oms!!!
  SELECT distinct oms_id 
  BULK COLLECT INTO oms_ids
  FROM MGMT_LOADER_DESIGNATORS 
  WHERE oms_id <> 0
  AND oms_id <> p_oms_id; 

  -- If there are no OMSs registered then assign all the queues to this OMS
  IF oms_ids.count = 0
  THEN
    UPDATE MGMT_LOADER_DESIGNATORS SET oms_id = p_oms_id;
    FOR ld IN (SELECT designator FROM MGMT_LOADER_DESIGNATORS)
    LOOP
      qMsg := ADMIN_START_MONITORING||ld.designator;
      ENQUEUE(admin_qname, qMsg);
    END LOOP;

    COMMIT;
    RETURN;
  END IF;

  -- add the new oms to existing list of oms ids
  oms_ids.extend(1);
  oms_ids(oms_ids.LAST) := p_oms_id;

  -- If there are OMSs registered then ask them to stop monitoring
  FOR ld IN (SELECT designator, oms_id FROM MGMT_LOADER_DESIGNATORS)
  LOOP
    admin_qname := 'ADM' || ld.oms_id;
    qMsg := ADMIN_STOP_MONITORING || ld.designator;
    ENQUEUE(admin_qname, qMsg);
  END LOOP;
  UPDATE MGMT_LOADER_DESIGNATORS SET oms_id = 0;

  -- Get the list of designators sorted by number of files pending
  EXECUTE IMMEDIATE 'SELECT designator, count(name) backlog ' ||
                    'FROM ' ||  G_DEQUEUE_IOT || ' a ' ||
                    'RIGHT OUTER JOIN MGMT_LOADER_DESIGNATORS b ' ||
                    'on name = designator ' ||
                    'GROUP BY designator ' ||
                    'ORDER BY backlog desc, designator '
  BULK COLLECT INTO designators, backlog;
  
  -- Now assign designators to oms in a round-robin fashion
  noms := oms_ids.count;
  FOR i IN designators.FIRST..designators.LAST 
  LOOP
       idx := ((i-1) mod noms ) + 1;
       
       UPDATE MGMT_LOADER_DESIGNATORS SET oms_id = oms_ids(idx)
            WHERE designator = designators(i);
      
       -- Tell OMS to start monitoring the queue
       qMsg := ADMIN_START_MONITORING || designators(i);
       admin_qname := 'ADM'||oms_ids(idx);

       ENQUEUE(admin_qname, qMsg);
  END LOOP;

  COMMIT;
  
EXCEPTION
WHEN NO_DATA_FOUND
THEN
  ROLLBACK;
END REGISTER_SHAREDFS;

-- PURPOSE
--  To handle clear messages not cleaned by failover
PROCEDURE CLEANUP_ADMIN_MESSAGES
IS
  dq_options      dbms_aq.dequeue_options_t;
  qMsg_properties dbms_aq.message_properties_t;
  admin_msgid     RAW(16);
  msg_ids         SMP_EMD_STRING_ARRAY;
  qnames          SMP_EMD_STRING_ARRAY;
  qMsg            VARCHAR2(30);
BEGIN
                                                                                
  IF G_RECURSIVE_CLEANUP
  THEN
    RETURN;
  END IF;

  -- Lock the table
  LOCK TABLE MGMT_LOADER_DESIGNATORS IN EXCLUSIVE MODE;
                                                                                
  -- Cleanup any old admin messages for OMSs that have gone away. This
  -- should not be necessary if the failover callback is reliably issued
  -- for each OMS that goes down
  EXECUTE IMMEDIATE 'SELECT DISTINCT msgid, name ' ||
                    'FROM ' || G_DEQUEUE_IOT || ' ' ||
                    'WHERE name LIKE :1 ' ||
                    'AND name NOT IN (SELECT :2||failover_id ' ||
                    'FROM MGMT_FAILOVER_TABLE)'
  BULK COLLECT INTO msg_ids, qnames
  USING 'ADM%', 'ADM';
  
  FOR i IN 1..msg_ids.count
  LOOP
    BEGIN
      DEQUEUE(qnames(i), 0, 1, qMsg, msg_ids(i));    
    EXCEPTION
    WHEN OTHERS
    THEN
      NULL;
    END;
  END LOOP;
                                                                                
  -- Reassign any designators for OMSs not in the failover table
  FOR oms IN (SELECT DISTINCT oms_id FROM MGMT_LOADER_DESIGNATORS
              WHERE oms_id <> 0
              AND oms_id NOT IN (SELECT failover_id FROM MGMT_FAILOVER_TABLE))
  LOOP
    G_RECURSIVE_CLEANUP := TRUE;
    UNREGISTER_SHAREDFS(oms.oms_id);
    G_RECURSIVE_CLEANUP := FALSE;
  END LOOP;

EXCEPTION
WHEN OTHERS
THEN
  G_RECURSIVE_CLEANUP := FALSE;
  raise;
END CLEANUP_ADMIN_MESSAGES;

-- PURPOSE
--  To handle failover when an OMS goes down
PROCEDURE OMS_FAILOVER(p_oms_id IN NUMBER,
                       p_last_timestamp IN DATE)
IS
  admin_qname    VARCHAR2(30);
  noms           NUMBER;
  oms_ids        SMP_EMD_INTEGER_ARRAY;
  backlog        SMP_EMD_INTEGER_ARRAY;
  designators    SMP_EMD_STRING_ARRAY;
  qMsg           VARCHAR2(30);
  idx            INTEGER;
  more_messages  BOOLEAN := TRUE;
  oms_name       VARCHAR2(256);
  ha_mode        INTEGER;
BEGIN

  -- Remove any messages in the queue for this OMS's admin queue
  WHILE (more_messages)
  LOOP
    BEGIN
      DEQUEUE_ADMIN('ADM' || p_oms_id, 0, qMsg);
    EXCEPTION
    WHEN OTHERS
    THEN   
      more_messages := FALSE;
    END;
  END LOOP;

  -- Lock the queue table
  LOCK TABLE MGMT_LOADER_DESIGNATORS IN EXCLUSIVE MODE;

  UPDATE MGMT_LOADER_DESIGNATORS SET oms_id = 0
  WHERE oms_id = p_oms_id;

  CLEANUP_ADMIN_MESSAGES;

  -- Reset the backlog metrics in mgmt_oms_parameters
  -- so that downed OMS does not continue to show
  -- stale backlog info. Do this only if OMS in 
  -- running in sharedFS mode
  BEGIN
      SELECT value, p.host_url
      INTO ha_mode, oms_name
      FROM mgmt_oms_parameters p, mgmt_failover_table f
      WHERE p.host_url = f.host_url
      AND name = HA_MODE_PARAM
      AND failover_id = p_oms_id;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN ha_mode := HA_MODE_UNDEFINED;
  END;

  IF (ha_mode = HA_MODE_SHAREDFS) THEN
      UPDATE_LOADER_METRICS(oms_name);
  END IF;
  
  -- Get the OMS names 
  SELECT distinct oms_id
  BULK COLLECT INTO oms_ids
  FROM MGMT_LOADER_DESIGNATORS 
  WHERE oms_id <> 0;
  noms := oms_ids.COUNT;

  -- If there are no OMSs registered then just return
  IF noms = 0
  THEN
    RETURN;
  END IF;

  -- Get the list of designators being monitored by the
  -- failed OMS sorted by number of files pending
  EXECUTE IMMEDIATE 'SELECT designator, count(name) backlog ' ||
                    'FROM ' ||  G_DEQUEUE_IOT || ' a ' ||
                    'RIGHT OUTER JOIN MGMT_LOADER_DESIGNATORS b ' ||
                    'ON name = designator ' ||
                    'WHERE OMS_ID = 0 ' ||
                    'GROUP BY designator ' ||
                    'ORDER BY backlog desc, designator '
  BULK COLLECT INTO designators, backlog;
  
  -- Now assign designators from failed OMS 
  -- to existing oms in a round-robin fashion
  noms := oms_ids.count;
  FOR i IN 1..designators.count
  LOOP
       idx := ((i-1) mod noms ) + 1;
       
       UPDATE MGMT_LOADER_DESIGNATORS SET oms_id = oms_ids(idx)
            WHERE designator = designators(i);
      
       -- Tell OMS to start monitoring the queue
       qMsg := ADMIN_START_MONITORING || designators(i);
       admin_qname := 'ADM' || oms_ids(idx);

       -- No need for delay previous OMS is down
       ENQUEUE(admin_qname, qMsg);
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
    MGMT_LOG.log_error('LOADER', SQLCODE, 
              'Error during sharedfs loader failover: ' || SQLERRM);
    raise;
END OMS_FAILOVER;


-- PURPOSE
--   To unregister an OMS from load coordination so that its designators
--   can be reassigned
PROCEDURE UNREGISTER_SHAREDFS(p_oms_id IN NUMBER)
IS
BEGIN
  OMS_FAILOVER(p_oms_id, SYSDATE);
END UNREGISTER_SHAREDFS;


PROCEDURE INITIALIZE_SHAREDFS
IS
designator_label    VARCHAR2(256);
designator_count    INTEGER;
designator          VARCHAR2(10);

BEGIN
   -- Lock table
   LOCK TABLE MGMT_LOADER_DESIGNATORS IN EXCLUSIVE MODE;

   -- This should not be required as the procedure is
   -- called at repository creation time
   DELETE 
   FROM MGMT_LOADER_DESIGNATORS;

   -- Get designator label and length from MGMT_PARAMETERS
   -- which is populated at repository creation time
   GET_LOAD_DESIGNATORS(designator_label, designator_count);

   FOR i IN 1..designator_count LOOP
      designator := substr(designator_label, (i*2)-1, 2);

      --Create entry for designator with oms_id as 0
      INSERT INTO MGMT_LOADER_DESIGNATORS 
             (designator, oms_id)
      VALUES (designator, 0);
   END LOOP;
 
   COMMIT;

END INITIALIZE_SHAREDFS;

PROCEDURE UPDATE_LOADER_METRICS(p_oms_url VARCHAR2)
IS
  loader_params   SMP_EMD_NVPAIR_ARRAY;
  backlog         number;
  agesecs         number;
BEGIN

  GET_SHAREDFS_BACKLOG(p_oms_url, backlog, agesecs);

  loader_params := SMP_EMD_NVPAIR_ARRAY (
            SMP_EMD_NVPAIR('loaderFileCount', backlog),
            SMP_EMD_NVPAIR('loaderOldestFile', agesecs));

  UPDATE_LOADER_PARAMS(p_oms_url, loader_params);

END UPDATE_LOADER_METRICS;

PROCEDURE GET_SHAREDFS_BACKLOG(p_oms_url IN VARCHAR2, 
    p_backlog OUT NUMBER, p_agesecs OUT NUMBER)
IS
  oldest          date;
  backlog         number;
  now             date;
  qry             varchar2(200);
BEGIN
   
  qry := 'SELECT min(msg_enq_time), count(name) ' ||
                    'FROM ' || G_DEQUEUE_IOT || ' ' ||
                    'WHERE name in ( SELECT designator ' ||
                                    'FROM MGMT_LOADER_DESIGNATORS ';

  IF ((p_oms_url IS NOT NULL) AND (length(trim(p_oms_url)) > 0 )) THEN
    EXECUTE IMMEDIATE qry || ', MGMT_FAILOVER_TABLE WHERE oms_id = failover_id AND host_url = :1)'
    INTO oldest, backlog
    USING p_oms_url;
  ELSE
    EXECUTE IMMEDIATE qry || ')'
    INTO oldest, backlog;
  END IF;

  now := sysdate;
  p_agesecs := trunc((now - nvl(oldest, now))*24*60*60);
  p_backlog := backlog;
END;



PROCEDURE LOG_PERFORMANCE(p_host_url IN VARCHAR2,
    p_module IN SMP_EMD_STRING_ARRAY,
    p_name IN SMP_EMD_STRING_ARRAY, 
    p_value IN SMP_EMD_INTEGER_ARRAY,
    p_duration IN SMP_EMD_INTEGER_ARRAY) 
IS
BEGIN
   FOR i IN 1..p_module.count LOOP
      MGMT_LOG.LOG_PERFORMANCE('LOADER', p_duration(i), NULL, 'Y', 
                                p_name(i), p_value(i), p_module(i),
                                NULL,NULL,p_host_url);
   END LOOP;
END LOG_PERFORMANCE;


PROCEDURE UPDATE_CURRENT_METRICS(p_targets IN SMP_EMD_STRING_ARRAY,
                                 p_metrics IN SMP_EMD_STRING_ARRAY,
                                 p_keys    IN SMP_EMD_STRING_ARRAY,
                                 p_times   IN SMP_EMD_STRING_ARRAY,
                                 p_values  IN SMP_EMD_STRING_ARRAY,
                                 p_svalues IN SMP_EMD_LARGE_STRING_ARRAY)
IS
   l_count NUMBER;
   l_rows NUMBER := 0;
BEGIN
   FOR i IN 1..p_targets.count LOOP

      l_rows := l_rows + UPDATE_CURRENT_METRICS_ROW(HEXTORAW(p_targets(i)),
                                                    HEXTORAW(p_metrics(i)),
                                                    p_keys(i),
                                                    TO_DATE(p_times(i), 'YYYY-MM-DD HH24:MI:SS'),
                                                    p_values(i),
                                                    p_svalues(i));
      IF l_rows >= G_CMETRIC_BATCH_SIZE
      THEN
          COMMIT;
          l_rows := 0;
      END IF;
      
   END LOOP;

   IF l_rows > 0
   THEN
      COMMIT;
   END IF;

END UPDATE_CURRENT_METRICS;

PROCEDURE UPDATE_STRING_METRICS_HISTORY(p_targets IN SMP_EMD_STRING_ARRAY,
    p_metrics IN SMP_EMD_STRING_ARRAY,
    p_keys IN SMP_EMD_STRING_ARRAY,
    p_times IN SMP_EMD_STRING_ARRAY,
    p_svalues IN SMP_EMD_LARGE_STRING_ARRAY)
IS
   l_rows NUMBER := 0;
   l_ErrMsg VARCHAR2(2048);
BEGIN
   FOR i IN 1..p_targets.count LOOP
      BEGIN
         l_rows := l_rows + UPDATE_STRING_METRICS_ROW(HEXTORAW(p_targets(i)),
                                                      HEXTORAW(p_metrics(i)),
                                                      p_keys(i),
                                                      TO_DATE(p_times(i), 'YYYY-MM-DD HH24:MI:SS'),
                                                      p_svalues(i));
      EXCEPTION
      WHEN OTHERS THEN
         l_ErrMsg := SUBSTR(SQLERRM, 1, 2048);
         MGMT_LOG.LOG_ERROR('LOADER',null,
             'Error ' || l_ErrMsg, 'UPDATE_STRING_METRICS_HISTORY');
      END;

      IF l_rows >= G_SMETRIC_BATCH_SIZE
      THEN
         COMMIT;
         l_rows := 0;
      END IF;

   END LOOP;

   IF l_rows > 0
   THEN
      COMMIT;
   END IF;

END UPDATE_STRING_METRICS_HISTORY;


FUNCTION UPDATE_STRING_METRICS_ROW(p_target IN RAW,
                                   p_metric IN RAW,
                                   p_key IN VARCHAR2,
                                   p_time IN DATE,
                                   p_svalue IN VARCHAR2) RETURN NUMBER
IS
   last_string_value MGMT_METRICS_RAW.string_value%TYPE ;
   last_time MGMT_STRING_METRIC_HISTORY.collection_timestamp%TYPE ;
   l_count NUMBER;
   l_rows NUMBER := 0;
BEGIN

   -- add this to be compatable with old trigger
   IF (p_svalue IS NULL)
   THEN
      RETURN 0;
   END IF;

   BEGIN

      SELECT collection_timestamp, string_value 
        INTO last_time, last_string_value 
        FROM ( SELECT collection_timestamp, string_value 
                 FROM MGMT_STRING_METRIC_HISTORY 
                WHERE target_guid = p_target 
                  AND metric_guid = p_metric 
                  AND key_value   = p_key 
                ORDER BY collection_timestamp DESC) 
       WHERE rownum = 1;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
          last_time := NULL;
   END;

   --
   --  If there is a non-null string value for the metric, try to record it in
   --  the string metric history table.
   --  If there is no string_metric_history record, or there is and the new
   --  string value is *NOT* the same, insert this one.
   --
   IF ( (last_time IS NULL) OR ( (last_string_value IS NULL) AND (last_time < p_time) ) OR
        ( ( p_svalue <> last_string_value ) AND (last_time < p_time) ) ) THEN

     INSERT INTO MGMT_STRING_METRIC_HISTORY
           (target_guid, key_value, collection_timestamp,
            metric_guid, string_value)
     VALUES
           (p_target, p_key, p_time, p_metric, p_svalue);

     l_rows := 1;

   END IF; 

   RETURN l_rows;

END UPDATE_STRING_METRICS_ROW;



FUNCTION UPDATE_CURRENT_METRICS_ROW(p_target IN RAW,
                                    p_metric IN RAW,
                                    p_key IN VARCHAR2,
                                    p_time IN DATE,
                                    p_value IN NUMBER,
                                    p_svalue IN VARCHAR2) RETURN NUMBER
IS
   l_count NUMBER;
   l_rows NUMBER := 0;
BEGIN
   UPDATE MGMT_CURRENT_METRICS
   SET collection_timestamp = p_time,
        value               = p_value,
        string_value        = p_svalue
    WHERE target_guid       = p_target
        AND metric_guid     = p_metric
        AND key_value       = p_key
        AND collection_timestamp < p_time;

   IF SQL%ROWCOUNT = 0 THEN
   BEGIN
      SELECT 1
         INTO l_count
         FROM MGMT_CURRENT_METRICS
       WHERE target_guid = p_target
         AND metric_guid = p_metric 
         AND key_value   = p_key;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
      BEGIN
         INSERT INTO MGMT_CURRENT_METRICS
             (target_guid, metric_guid, key_value,
             collection_timestamp, value, string_value)
          VALUES
             (p_target, p_metric, p_key, p_time, p_value, p_svalue);

          l_rows := l_rows + 1;
      EXCEPTION
        -- The only reason why we would end up here is if multiple loaders
        -- across multiple OMSs are loading data for the same target at the
        -- same time.
         WHEN DUP_VAL_ON_INDEX THEN
            NULL;
         END;
      END;
   ELSE
      l_rows := l_rows + 1;
   END IF;

   RETURN l_rows;

END UPDATE_CURRENT_METRICS_ROW;

PROCEDURE GET_LOAD_DESIGNATORS(p_ld_label OUT VARCHAR2,
                            p_ld_count OUT NUMBER)
IS
designator VARCHAR2(2);
BEGIN
    SELECT to_number(parameter_value)
    INTO p_ld_count
    FROM mgmt_parameters
    WHERE parameter_name = 'LOAD_DESIGNATOR_COUNT';

    FOR i IN 1..p_ld_count LOOP
        designator := chr(67+trunc((i-1)/10)) || --start with C
                      chr(48+mod(i-1,10));   --start with 0
        p_ld_label := p_ld_label || designator;
    END LOOP;
END GET_LOAD_DESIGNATORS;

-- Install API to get loader coordination method
-- To be used *only* by the EM installer
-- Note for operations the source of truth is always
-- the oms properties file. The loader does *not* use
-- the mgmt_parameters values in any way
PROCEDURE GET_HA_MODE(p_ha_mode OUT NUMBER, p_recv_dir OUT VARCHAR2)
IS
BEGIN
    BEGIN
        SELECT to_number(parameter_value)
        INTO p_ha_mode
        FROM mgmt_parameters
        WHERE parameter_name = HA_MODE_PARAM;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN p_ha_mode := HA_MODE_UNDEFINED;
    END;    

    BEGIN
        SELECT parameter_value
        INTO p_recv_dir
        FROM mgmt_parameters
        WHERE parameter_name = HA_RECV_DIR_PARAM;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN p_recv_dir := ' ';
    END;    

END GET_HA_MODE;

-- Install API to set loader coordination method
-- To be used *only* by the EM installer
-- Note for operations the source of truth is always
-- the oms properties file. The loader does *not* use
-- the mgmt_parameters values in any way
PROCEDURE SET_HA_MODE(p_ha_mode IN NUMBER, 
                        p_recv_dir IN VARCHAR2 DEFAULT ' ')
IS
BEGIN
    BEGIN
        INSERT INTO MGMT_PARAMETERS(parameter_name, parameter_value,
                                    parameter_comment)
        VALUES (HA_MODE_PARAM, p_ha_mode,
           'Coordination method used by loader. ' ||
           '0=sharedFilesystem, 1=nonSharedFilesystem, -1=undefined. ' ||
           'For use by the installer only');
    EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        UPDATE MGMT_PARAMETERS SET parameter_value = p_ha_mode
            WHERE parameter_name=HA_MODE_PARAM;
    END;

    BEGIN
        INSERT INTO MGMT_PARAMETERS(parameter_name, parameter_value,
                                    parameter_comment)
        VALUES (HA_RECV_DIR_PARAM, p_recv_dir,
               'Loader Receive directory. For use by the installer only');
    EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
         UPDATE MGMT_PARAMETERS SET parameter_value = p_recv_dir
             WHERE parameter_name=HA_RECV_DIR_PARAM;
    END;
END SET_HA_MODE;

  -- APIs to set and clear the emd_url changes
  PROCEDURE set_emd_url_change ( 
      p_last_emd_url IN mgmt_targets.emd_url%TYPE,
      p_emd_url      IN mgmt_targets.emd_url%TYPE)
  IS
  BEGIN

    BEGIN
      INSERT INTO mgmt_change_agent_url
        (last_emd_url, emd_url)
       VALUES
        (p_last_emd_url, p_emd_url);
    EXCEPTION 
      WHEN OTHERS THEN
        -- Ignore duplicate exceptions
        NULL;
    END;
  END set_emd_url_change; 

  FUNCTION is_emd_url_change_allowed(
      p_last_emd_url IN mgmt_targets.emd_url%TYPE,
      p_emd_url IN mgmt_targets.emd_url%TYPE)
    RETURN BOOLEAN
  IS
    l_cnt NUMBER;
    l_retVal BOOLEAN;
  BEGIN
    l_retVal := FALSE;

    SELECT COUNT(*) INTO l_cnt
      FROM mgmt_change_agent_url
     WHERE last_emd_url = p_last_emd_url
       AND emd_url = p_emd_url;

    IF (l_cnt > 0) THEN
      l_retVal := TRUE;
    END IF;
    RETURN l_retVal;
  END is_emd_url_change_allowed;

  PROCEDURE clear_emd_url_change(
      p_last_emd_url IN mgmt_targets.emd_url%TYPE,
      p_emd_url      IN mgmt_targets.emd_url%TYPE)
  IS
  BEGIN

    DELETE FROM mgmt_change_agent_url
     WHERE last_emd_url = p_last_emd_url
       AND emd_url = p_emd_url;
   
  END clear_emd_url_change;

  
  -- APIs to set and clear the timezone region changes
  FUNCTION allow_tzrgn_change RETURN BOOLEAN
  IS
  BEGIN
    RETURN s_tzrgn_changed;
  END allow_tzrgn_change;

  PROCEDURE set_tzrgn_change
  IS
  BEGIN
    s_tzrgn_changed := TRUE;
  END set_tzrgn_change;

  PROCEDURE clear_tzrgn_change
  IS
  BEGIN
    s_tzrgn_changed := FALSE;
  END clear_tzrgn_change;

  -- Get agent_host_name from emd_url
  -- http://host.example.com:4889/emd/main/
  PROCEDURE get_name_port_from_emd_url
  (
    p_emd_url    IN  mgmt_targets.emd_url%TYPE,
    p_agent_name OUT VARCHAR2,
    p_agent_port OUT VARCHAR2
  ) 
  IS
    l_start_position NUMBER ;
    l_end_position NUMBER ;
  BEGIN

    -- figure out agent_name
    l_start_position := INSTR(p_emd_url,':');
    l_end_position :=  INSTR(p_emd_url,':',l_start_position+1);
    p_agent_name :=  SUBSTR(p_emd_url,l_start_position+3,l_end_position-l_start_position-3);
  
    -- figure out port
    l_start_position := l_end_position+1 ; 
    l_end_position := INSTR(p_emd_url,'/',l_start_position);   
    p_agent_port := SUBSTR(p_emd_url,l_start_position,l_end_position-l_start_position); 

    IF(trim(p_agent_name) IS NULL OR trim(p_agent_port) IS NULL)
      THEN 
        raise_application_error(MGMT_GLOBAL.INVALID_EMD_URL_ERR,
                        'The specified EMD_URL is invalid (' ||
                          p_emd_url || '). Not able to parse EMD_URL.');
    END IF;
   EXCEPTION WHEN OTHERS THEN
      raise_application_error(MGMT_GLOBAL.INVALID_EMD_URL_ERR,
                      'The specified EMD_URL is invalid (' ||
                        p_emd_url || '). Not able to parse EMD_URL.');

  END get_name_port_from_emd_url;

 -- This procedure updates the metadata cahce
 PROCEDURE update_metadata_cache(
   p_emd_url_in    IN  mgmt_targets.emd_url%TYPE)
 IS
  l_check_metadata_sql VARCHAR2(512);
  l_cnt NUMBER := 0;
  l_count_host NUMBER :=0;
  l_count_metric_metadata NUMBER := 0;
  l_platform MGMT_METRICS.category_prop_1%TYPE;
  l_target_type_host MGMT_TARGETS.target_type%TYPE;
  l_type_meta_ver MGMT_TARGETS.type_meta_ver%TYPE;
  l_agent_name VARCHAR2(256);
  l_agent_port VARCHAR2(64);

  metadata_tables VARCHAR_TABLE := VARCHAR_TABLE('MGMT_CREDENTIAL_SETS',
                                                 'MGMT_CREDENTIAL_SET_COLUMNS',
                                                 'MGMT_CREDENTIAL_TYPES',
                                                 'MGMT_CREDENTIAL_TYPE_COLUMNS',
                                                 'MGMT_CREDENTIAL_TYPE_REF',
                                                 'MGMT_COLL_ITEMS',
                                                 'MGMT_COLL_ITEM_METRICS',
                                                 'MGMT_METRICS',
                                                 'MGMT_TARGET_PROP_DEFS',
                                                 'MGMT_TARGET_TYPE_VERSIONS');

  CURSOR target_type_cursor(l_emd_url VARCHAR2) IS
    SELECT DISTINCT target_type, type_meta_ver
    FROM mgmt_targets mt
    WHERE mt.emd_url = l_emd_url
    AND mt.broken_reason = 0
    AND NOT EXISTS (SELECT DISTINCT ms.target_type, ms.type_meta_ver 
                    FROM MGMT_METADATA_SETS ms
                    WHERE mt.target_type = ms.target_type
                     AND  mt.type_meta_ver = ms.type_meta_ver);
 BEGIN

   BEGIN

     get_name_port_from_emd_url(p_emd_url_in, l_agent_name, l_agent_port);

     SELECT DISTINCT category_prop_1, target_type, type_meta_ver
       INTO l_platform, l_target_type_host, l_type_meta_ver
       FROM mgmt_targets
      WHERE target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE
        AND emd_url LIKE '%://'||l_agent_name||':'||l_agent_port||'/%' ;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       l_platform := NULL;
   END;

   FOR target_type IN target_type_cursor(p_emd_url_in)
     LOOP
       FOR i in 1.. metadata_tables.COUNT
         LOOP
           IF (metadata_tables(i) like 'MGMT_CREDENTIAL%') THEN
             l_check_metadata_sql := 'SELECT COUNT(*) '|| 
                                     ' FROM ' ||  metadata_tables(i) ||
                                     ' WHERE target_type = :target_type' || 
                                     '   AND target_type_meta_ver = :type_meta_ver'  ||
                                     '   AND ROWNUM < 2 '; 
           ELSE   
             l_check_metadata_sql := 'SELECT COUNT(*) '|| 
                                     ' FROM ' ||  metadata_tables(i) ||
                                     ' WHERE target_type = :target_type' || 
                                     '   AND type_meta_ver = :type_meta_ver'  ||
                                     '   AND ROWNUM < 2 ';
           END IF; 

           BEGIN
             EXECUTE IMMEDIATE l_check_metadata_sql INTO l_cnt USING target_type.target_type, target_type.type_meta_ver;
           EXCEPTION
             WHEN OTHERS THEN
               l_cnt := -1;
               IF (EMDW_LOG.p_is_debug_set) THEN
                  EMDW_LOG.DEBUG('Error occurred during caching of data for ' || metadata_tables(i) ||', ' 
                  || target_type.target_type ||', ' || target_type.type_meta_ver || ' Error:' || SQLERRM, 'EMD_LOADER');
               END IF;
           END;        
                  
           IF(l_cnt > 0) THEN   
             IF NOT (target_type.target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE AND metadata_tables(i) = 'MGMT_METRICS') THEN
             EMD_LOADER.END_METADATA_SET(target_type.target_type, 
                                         target_type.type_meta_ver,
                                         metadata_tables(i));  
             END IF;
           ELSIF(l_cnt = 0) THEN
             IF (EMDW_LOG.p_is_debug_set) THEN
                 EMDW_LOG.DEBUG('No data found for ' || metadata_tables(i) ||', ' 
                 || target_type.target_type ||', ' || target_type.type_meta_ver || ', on receiving first severity', 'EMD_LOADER');
             END IF;
           END IF;
       END LOOP;
   END LOOP;

   -- Check for Host Metadata
   IF ( l_platform IS NOT NULL ) THEN
     SELECT COUNT(*)
       INTO l_count_host
       FROM mgmt_metadata_sets
      WHERE target_type = l_target_type_host||'_'||l_platform
        AND type_meta_ver = l_type_meta_ver
        AND table_name = 'MGMT_METRICS';

     IF(l_count_host = 0)
     THEN
       SELECT COUNT(*)
         INTO l_count_metric_metadata
         FROM mgmt_metrics
        WHERE target_type = l_target_type_host
          AND type_meta_ver = l_type_meta_ver
          AND category_prop_1 = l_platform
          AND ROWNUM < 2 ;

       IF(l_count_metric_metadata > 0)
       THEN
         EMD_LOADER.END_METADATA_SET(l_target_type_host,
                                     l_type_meta_ver,
                                     'MGMT_METRICS',
                                     l_platform);
       END IF;
     END IF;
   END IF;

 END update_metadata_cache;
  


-- Obtain the lock for the emd_url
  PROCEDURE obtain_lock_for_emd_url(
      p_emd_url      IN  mgmt_targets.emd_url%TYPE,
      p_timeout_secs IN  NUMBER := 30,   
      p_load_type    IN  VARCHAR2,
      p_lock_handle  OUT VARCHAR2)
  IS 
   l_last_load_type VARCHAR2(8);  
  BEGIN
    BEGIN
      p_lock_handle :=  
          MGMT_LOCK_UTIL.get_exclusive_lock(p_lock_type  => 'EMD_URL',
                                            p_lock_value => p_emd_url,
                                            p_timeout_secs => p_timeout_secs,
                                            p_release_on_commit => FALSE);
    EXCEPTION
      WHEN MGMT_GLOBAL.RESOURCE_BUSY THEN
         raise_application_error(MGMT_GLOBAL.AGENT_LOCK_BUSY_ERR,
             MGMT_GLOBAL.AGENT_LOCK_BUSY_ERR_M|| p_emd_url);

    END;

    -- Check if the current load_type is severity and 
    -- last load_type is metadata, the update the metadata cache	 
    IF(p_load_type = 'severity') THEN
      BEGIN
        SELECT load_type
        INTO l_last_load_type 
        FROM MGMT_LAST_SYNC_LOAD_DETAILS
        WHERE emd_url = p_emd_url;
      EXCEPTION 
        WHEN NO_DATA_FOUND THEN
        NULL;
      END;
     
      IF(l_last_load_type = 'metadata') THEN
        update_metadata_cache(p_emd_url);
      END IF;
   END IF;
 
  END obtain_lock_for_emd_url;
 
-- Release the lock for the emd_url
  PROCEDURE release_lock_for_emd_url(
      p_lock_handle    IN  VARCHAR2,
      p_emd_url        IN  VARCHAR2,
      p_load_type      IN  VARCHAR2, 
      p_clean_load     IN  NUMBER,
      p_lock_released  OUT NUMBER)
  IS 
  BEGIN

   -- update MGMT_LAST_SYNC_LOAD_DETAILS
   BEGIN
    -- Insert a row in MGMT_LAST_SYNC_LOAD_DETAILS
   MERGE
    INTO MGMT_LAST_SYNC_LOAD_DETAILS b
   USING (SELECT p_emd_url emd_url,  DECODE(p_clean_load, 1, p_load_type, NULL) load_type FROM DUAL) a
      ON (a.emd_url = b.emd_url)
    WHEN MATCHED THEN
      UPDATE SET
        load_type = a.load_type,
        load_time = sysdate
    WHEN NOT MATCHED THEN
      INSERT
      (emd_url, load_type, load_time)
      values
      (a.emd_url, a.load_type, sysdate);

     IF (SQL%ROWCOUNT = 0) THEN
        MGMT_LOG.LOG_ERROR('LOADER', null,
         'FATAL_ERROR received while updating last_sync_load_details');
     END IF;    
   END; 
    p_lock_released :=  
          MGMT_LOCK_UTIL.release_lock(p_lock_handle => p_lock_handle);
  END release_lock_for_emd_url;

-- Procedure to check metadata is cached in database or not. 
  PROCEDURE check_metadata_sets(
      p_target_type    IN VARCHAR2,
      p_type_meta_ver  IN VARCHAR2,
      p_table_name     IN VARCHAR2,
      p_metadata_cnt   OUT NUMBER)
  IS
  BEGIN
     SELECT COUNT(*) 
     INTO p_metadata_cnt
     FROM mgmt_metadata_sets
     WHERE target_type = p_target_type
      AND  type_meta_ver = p_type_meta_ver
      AND  table_name = p_table_name;  

  END check_metadata_sets; 

  -- Procedure to obtain lock for metric settings
  PROCEDURE obtain_metric_settings_lock(
      p_lock_name         IN  VARCHAR2,
      p_timeout_secs      IN  NUMBER := 30,
      p_release_on_commit IN  NUMBER := 0,
      p_lock_handle       OUT VARCHAR2)
  IS
    l_release_on_commit BOOLEAN;
  BEGIN
    l_release_on_commit := TRUE;
    IF (p_release_on_commit = 0) THEN
      l_release_on_commit := FALSE;
    END IF;

    BEGIN
      p_lock_handle :=  
        MGMT_LOCK_UTIL.get_exclusive_lock(p_lock_type         => 'TARGET_METRIC_SETTINGS',
                                          p_lock_value        => p_lock_name,
                                          p_timeout_secs      => p_timeout_secs,
                                          p_release_on_commit => l_release_on_commit);
    EXCEPTION
      WHEN MGMT_GLOBAL.RESOURCE_BUSY THEN
         raise_application_error(MGMT_GLOBAL.METRIC_SETTINGS_LOCK_BUSY_ERR,
             MGMT_GLOBAL.METRIC_SETTINGS_LOCK_BUSY_ERRM || p_lock_name);
    END;

  END obtain_metric_settings_lock;

  -- Procedure to release the lock for metric settings
  PROCEDURE release_metric_settings_lock(
      p_lock_handle    IN  VARCHAR2,
      p_lock_released  OUT NUMBER)
  IS
  BEGIN
    p_lock_released :=  
          MGMT_LOCK_UTIL.release_lock(p_lock_handle => p_lock_handle);
  END release_metric_settings_lock;

   -- Internal Procedure to generate/clear Metric error upon change of broken state for a target
   --
   -- All TARGET SHOULD BE OF SAME AGENT
   --
   -- p_broken_target_list.TARGET_GUID    >> Has Target GUID
   -- p_broken_target_list.LOG_BROKEN_MSG >> Has error message to be logged
   --                              NULL Message > Target leaving broken state
   --                              NOT NULL message > Target entering broken state OR different broken reason.
   --
  PROCEDURE handle_target_broken_state
  (
    p_broken_target_list MGMT_BROKEN_TARGET_LIST
  )
  IS
    l_agent_guid mgmt_targets.target_guid%TYPE;
    l_response_metric_guid mgmt_metrics.metric_guid%TYPE;
    l_target_name mgmt_targets.target_name%TYPE;
    l_target_type mgmt_targets.target_type%TYPE;
    l_emd_url mgmt_targets.emd_url%TYPE;
    l_stop_time DATE;
    l_job_target_names MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();
    l_job_target_types MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();
    l_coll_name mgmt_coll_items.coll_name%TYPE;
    l_metric_error_cnt NUMBER := 0;
  BEGIN

    IF (EMDW_LOG.p_is_info_set) THEN
        EMDW_LOG.INFO('Entering: p_broken_target_list.count: '||p_broken_target_list.count ,
                      'EMD_LOADER.handle_target_broken_state');
    END IF;

    IF p_broken_target_list IS NULL OR
       p_broken_target_list.COUNT = 0 THEN
      RETURN;
    END IF;

    FOR i IN 1..p_broken_target_list.count LOOP

      SELECT target_name, target_type, emd_url
        INTO l_target_name, l_target_type, l_emd_url
        FROM mgmt_targets
       WHERE target_guid = p_broken_target_list(i).target_guid ;

        SELECT target_guid
          INTO l_agent_guid
          FROM mgmt_targets
         WHERE target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE
           AND emd_url = l_emd_url ;

      BEGIN
        SELECT m.metric_guid
          INTO l_response_metric_guid
          FROM MGMT_TARGETS t, MGMT_METRICS m
         WHERE t.target_type = m.target_type
           AND t.target_guid = p_broken_target_list(i).target_guid
           AND m.type_meta_ver = t.type_meta_ver
           AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
           AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
           AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
           AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
           AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ')
           AND m.metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME
           AND m.metric_column = ' '
           AND ROWNUM = 1;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          l_response_metric_guid := NULL;
          EMDW_LOG.INFO('Ignoring broken state. R/S Metric not available for target type '||l_target_type,
                        'EMD_LOADER') ;
      END;

      IF l_response_metric_guid IS NOT NULL THEN

       BEGIN
         SELECT mci.coll_name
           INTO l_coll_name
           FROM mgmt_coll_items mci, mgmt_coll_item_metrics mcim, mgmt_targets t
          WHERE t.target_guid =  p_broken_target_list(i).target_guid
            AND mcim.metric_guid = l_response_metric_guid
            AND mcim.type_meta_ver = mci.type_meta_ver
            AND mcim.type_meta_ver = t.type_meta_ver
            AND mcim.target_type = mci.target_type
            AND mcim.target_type = t.target_type
            AND mcim.coll_name = mci.coll_name
            AND (mci.category_prop_1 = t.category_prop_1 OR mci.category_prop_1 = ' ')
            AND (mci.category_prop_2 = t.category_prop_2 OR mci.category_prop_2 = ' ')
            AND (mci.category_prop_3 = t.category_prop_3 OR mci.category_prop_3 = ' ')
            AND (mci.category_prop_4 = t.category_prop_4 OR mci.category_prop_4 = ' ')
            AND (mci.category_prop_5 = t.category_prop_5 OR mci.category_prop_5 = ' ');
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            l_coll_name := MGMT_GLOBAL.G_AVAIL_METRIC_NAME ;
        END;

        -- If target is leaving broken state then chk existance of metric error before cleaning it
        -- else it would raise duplicate metric error message error.
        IF  p_broken_target_list(i).log_broken_msg IS NULL THEN
          -- Target is leaving broken state
          SELECT COUNT(*)
            INTO l_metric_error_cnt
            FROM MGMT_CURRENT_METRIC_ERRORS
           WHERE target_guid = p_broken_target_list(i).target_guid
             AND metric_guid = l_response_metric_guid
             AND coll_name   = l_coll_name
             AND agent_guid  = l_agent_guid ;

           l_job_target_names.extend(1);
           l_job_target_types.extend(1);

           l_job_target_names(l_job_target_names.last) := l_target_name;
           l_job_target_types(l_job_target_types.last) := l_target_type;

           l_stop_time := NVL(EMD_LOADER.P_CURRENT_UPLOAD_TIME, MGMT_TARGET.sysdate_target(p_broken_target_list(i).target_guid));

        ELSE
          -- Target has entered broken state or another broken reason
          l_metric_error_cnt := 1;
        END IF;

        IF l_metric_error_cnt = 1 THEN

          BEGIN
            EM_COLL_UTIL.log_metric_errors
            (
              p_target_guid          => p_broken_target_list(i).target_guid,
              p_metric_guid          => l_response_metric_guid,
              p_error_message        => p_broken_target_list(i).log_broken_msg,
              p_collection_timestamp => NVL(EMD_LOADER.P_CURRENT_UPLOAD_TIME, MGMT_TARGET.sysdate_target(p_broken_target_list(i).target_guid)),
              p_coll_name            => l_coll_name,
              p_agent_guid           => l_agent_guid,
              p_metric_error_type    => MGMT_GLOBAL.G_METRIC_ERROR_TYPE_ERROR
            );
          EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
              NULL;
            WHEN OTHERS THEN
              MGMT_LOG.LOG_ERROR('LOADER', 0,
              'Unable to handle broken target state for'|| 
              '(target_name : '||l_target_name ||') (target_type : '||l_target_type ||')'||
              '(agent_guid : '||l_agent_guid || ') ( broken_msg : '||p_broken_target_list(i).log_broken_msg ||')'||
              '(Error : '||sqlerrm ||')' );
          END;
        END IF;

      END IF;

   END LOOP;
   IF l_job_target_names IS NOT NULL AND l_job_target_names.count > 0 THEN
      --Submit a discard state job
      MGMT_BLACKOUT_ENGINE.submit_discard_state_job
      (
        p_emd_url      => l_emd_url,
        p_target_names => l_job_target_names,
        p_target_types => l_job_target_types,
        p_stop_time    => l_stop_time,
        p_is_proxy     => 'N',
        p_job_name_prefix => 'TGT_BROKEN_DISCST_JOB'
           );
    END IF;

    IF (EMDW_LOG.p_is_info_set) THEN
      EMDW_LOG.INFO('Exiting','EMD_LOADER.handle_target_broken_state');
    END IF;

  END handle_target_broken_state;
 

  --
  -- PROCEDURE: register_pre_load_callback
  --
  -- PURPOSE: 
  --   Registers the given load_proc to the repository.
  PROCEDURE register_load_callback
  (
    p_callback_name IN VARCHAR2,
    p_callback_type IN NUMBER
  )
  AS
  BEGIN
    INSERT
      INTO mgmt_callbacks
           (callback_type,callback_name)
    VALUES (p_callback_type,
            UPPER(p_callback_name));
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      raise_application_error(mgmt_global.invalid_params_err,
                              p_callback_name || ' callback already exist');
  END register_load_callback;

  --
  -- PROCEDURE: deregister_load_callback
  --
  -- PURPOSE: 
  --   Deregisters the given load_proc from the repository.
  PROCEDURE deregister_load_callback
  (
    p_callback_name IN VARCHAR2,
    p_callback_type IN NUMBER
  )
  AS
   l_callback_name mgmt_callbacks.callback_name%TYPE; 
  BEGIN
    BEGIN
      SELECT callback_name
        INTO l_callback_name 
        FROM mgmt_callbacks
       WHERE callback_type = p_callback_type
         AND callback_name = UPPER(p_callback_name);

      DELETE
        FROM mgmt_callbacks
       WHERE callback_type = p_callback_type
         AND callback_name = UPPER(p_callback_name);

      EXCEPTION 
        WHEN NO_DATA_FOUND THEN
          raise_application_error(mgmt_global.invalid_params_err,
                                  p_callback_name || ' callback is not registered');
      END; 
  END deregister_load_callback;

  --
  -- PROCEDURE: execute_pre_load_callback
  -- 
  -- PURPOSE: 
  --   This procedureexecutes the given pre_load_proc.
  PROCEDURE execute_pre_load_callback
  (
    p_pre_load_proc  IN VARCHAR2,
    p_input IN SMP_EMD_NVPAIR_ARRAY,
    p_pre_load_output OUT SMP_EMD_NVPAIR_ARRAY
  )
  AS
  BEGIN
     EXECUTE IMMEDIATE
        'BEGIN ' || EM_CHECK.qualified_sql_name(p_pre_load_proc)
         || '(:1, :2); END; ' USING
         IN p_input, OUT p_pre_load_output;
  END;

  --
  -- PROCEDURE: execute_post_load_callback
  -- 
  -- PURPOSE: 
  --   This procedure executes the given post_load_proc.
  PROCEDURE execute_post_load_callback
  (
    p_post_load_proc  IN VARCHAR2,
    p_input IN SMP_EMD_NVPAIR_ARRAY,
    p_pre_load_output IN SMP_EMD_NVPAIR_ARRAY
  )
  AS
   BEGIN
    EXECUTE IMMEDIATE 
       'BEGIN ' || EM_CHECK.qualified_sql_name(p_post_load_proc)
       || '(:1, :2); END; ' USING
       IN p_input, p_pre_load_output;
  END;

  --
  -- PROCEDURE: validate_callback
  -- 
  -- PURPOSE: 
  --   This procedure verifies whether the given
  --   callback is registered or not.
  --   If not registered then it throws an error.
  PROCEDURE validate_callback(p_callback_name IN VARCHAR2,
                              p_callback_type IN VARCHAR2,
                              p_error_msg     IN VARCHAR2)
  AS
   l_callback_name mgmt_callbacks.callback_name%TYPE;
   l_error_msg VARCHAR2(1024);
  BEGIN
    IF(p_callback_name IS NOT NULL AND
       LENGTH(TRIM(p_callback_name)) > 0)
      THEN
        BEGIN
          SELECT callback_name
            INTO l_callback_name
            FROM mgmt_callbacks
           WHERE callback_name = UPPER(p_callback_name)
             AND callback_type = p_callback_type;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            IF(p_error_msg IS NULL)
              THEN
                l_error_msg := 'callback ' || p_callback_name || ' is not registered';
              ELSE
                l_error_msg := SUBSTR(p_error_msg,1,1024);  
            END IF;  
            raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,l_error_msg);
        END;
    END IF;
  END validate_callback;  

  -- Procedure to set purge batch size

  PROCEDURE set_purge_batchsize
  (
    p_purge_batchsize IN NUMBER
  )
  IS
  BEGIN

    EM_CHECK.check_not_null(p_value      => p_purge_batchsize,
                            p_param_name => 'p_purge_batchsize',
                            p_error_msg  => 'Purge batch size can not be set to NULL');

    MERGE
     INTO mgmt_parameters p
    USING (SELECT EM_PURGE_BATCHSIZE parameter_name, p_purge_batchsize parameter_value FROM DUAL) p1
       ON (p.parameter_name = p1.parameter_name)
     WHEN MATCHED THEN
       UPDATE
          SET p.parameter_value = p1.parameter_value
     WHEN NOT MATCHED THEN
       INSERT (parameter_name, parameter_value, parameter_comment, internal_flag)
       VALUES (p1.parameter_name, p1.parameter_value, 'Purge batchsize', 1) ;

  END set_purge_batchsize;

  --  Function to get purge batch size

  FUNCTION get_purge_batchsize RETURN NUMBER
  IS
    l_purge_batchsize NUMBER;
  BEGIN
    BEGIN
      SELECT parameter_value
        INTO l_purge_batchsize
        FROM mgmt_parameters
       WHERE parameter_name = EM_PURGE_BATCHSIZE ;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
      l_purge_batchsize := p_purge_batchsize ;
    END;
    RETURN l_purge_batchsize ;
  END get_purge_batchsize ;

  FUNCTION is_fatally_broken 
  (
    p_new_broken_state IN mgmt_targets.broken_reason%TYPE
  ) RETURN BOOLEAN
  AS
  BEGIN

    /*
    ** Following broken reason(s) to be considerd as NON-FATAL.
    ** i.e we should not log metric error if broken reason is NON-FATAL
    ** NON-FATAL reason: 0, 4, 8 and 1024
    **
    ** All other broken reason to be considered as FATAL. 
    */
   
    IF NVL(TRIM(p_new_broken_state),0) NOT IN (MGMT_GLOBAL.G_BROKEN_DYNAMIC_PROP_ERROR,
                                               MGMT_GLOBAL.G_BROKEN_DYNAMIC_PROP_MISSING,
                                               MGMT_GLOBAL.G_BROKEN_CATG_DYN_PROP_ERROR,
                                               MGMT_GLOBAL.G_NOT_BROKEN                    
                                              ) THEN
       RETURN TRUE;

    ELSE

       RETURN FALSE;

    END IF;

  END is_fatally_broken;

 PROCEDURE check_deletion_allowed
 (
   p_target_guid IN mgmt_targets.target_guid%TYPE,
   p_metric_guid IN mgmt_metrics.metric_guid%TYPE,
   p_emd_url IN  mgmt_targets.emd_url%TYPE,
   p_table_name IN VARCHAR2
 )
 IS
 BEGIN
    IF NOT (EM_TARGET.is_agent_monitoring_target(p_target_guid, p_emd_url))
    THEN
        raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
         'Unable to delete from ' || p_table_name || ' as agent : ' ||  EMD_LOADER.GET_CURRENT_EMD_URL() ||
         ' is not monitoring the target : ' || p_target_guid);
    END IF;

    IF NOT (EM_METRIC.is_server_gen_alert_metric(p_metric_guid, p_target_guid))
    THEN
        raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
         'Unable to delete from ' || p_table_name || ' as metric : ' ||  p_metric_guid ||
         ' is not a server generated alert metric');
    END IF;
 END check_deletion_allowed;

 PROCEDURE register_cluster_crs_event_cbk
 (
   p_cluster_crs_event_cbk   IN VARCHAR2
 )
 AS
   l_error_message VARCHAR2(1000);
   l_callback_name mgmt_callbacks.callback_name%TYPE;
   l_cnt NUMBER;
 BEGIN
   EM_CHECK.check_not_null(p_cluster_crs_event_cbk,'p_cluster_crs_event_cbk');

   l_callback_name := EM_CHECK.qualified_sql_name(p_cluster_crs_event_cbk);

   IF NOT EM_CHECK.is_valid_signature(p_cluster_crs_event_cbk,
                     mgmt_short_string_array('MGMT_VIOLATION_SUMMARY_REC','NUMBER'),
                      l_error_message)
   THEN
     raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, l_error_message) ;
   END IF ;

   SELECT COUNT(*)
     INTO l_cnt
     FROM mgmt_callbacks
    WHERE callback_type = MGMT_GLOBAL.G_CLUSTER_CRS_EVENT_CALLBACK;

   IF(l_cnt > 0)
   THEN  
     raise_application_error(mgmt_global.invalid_params_err,
                             ' Only one callback can be registered for cluster crs event');
   END IF; 

   INSERT
     INTO mgmt_callbacks
          (callback_type,callback_name)
   VALUES (MGMT_GLOBAL.G_CLUSTER_CRS_EVENT_CALLBACK,
           UPPER(p_cluster_crs_event_cbk));

  END register_cluster_crs_event_cbk;

 PROCEDURE dereg_cluster_crs_event_cbk
 (
   p_cluster_crs_event_cbk   IN VARCHAR2
 )
 AS
   l_error_message VARCHAR2(1000);
   l_callback_name mgmt_callbacks.callback_name%TYPE;
 BEGIN
   EM_CHECK.check_not_null(p_cluster_crs_event_cbk,'p_cluster_crs_event_cbk');

   DELETE mgmt_callbacks
    WHERE callback_type = MGMT_GLOBAL.G_CLUSTER_CRS_EVENT_CALLBACK
      AND callback_name = UPPER(p_cluster_crs_event_cbk);

   IF(SQL%ROWCOUNT = 0)
   THEN
     raise_application_error(mgmt_global.invalid_params_err,
                             ' No such callback is registered for cluster crs event');
   END IF;

  END dereg_cluster_crs_event_cbk;



  PROCEDURE submit_discard_state_job
  (
    p_emd_url IN mgmt_targets.emd_url%TYPE,
    p_job_target_names IN MGMT_JOB_VECTOR_PARAMS,
    p_job_target_types IN MGMT_JOB_VECTOR_PARAMS
  )
  IS
    l_stop_time DATE;
    l_agent_name VARCHAR2(256);
    l_agent_port VARCHAR2(64);
    l_job_name_prefix VARCHAR2(286);
  BEGIN
    IF (EMDW_LOG.p_is_info_set) THEN
        EMDW_LOG.INFO('Entering: p_emd_url: '||p_emd_url,
                      'EMD_LOADER.submit_discard_state_job');
    END IF;

    IF(p_job_target_names IS NOT NULL AND p_job_target_names.count > 0 AND  
       p_job_target_types IS NOT NULL AND p_job_target_types.count > 0) THEN

         l_stop_time := NVL(EMD_LOADER.P_CURRENT_UPLOAD_TIME, MGMT_TARGET.sysdate_target(
                            mgmt_target.generate_target_guid(p_job_target_names(1),p_job_target_types(1))));
       get_name_port_from_emd_url(p_emd_url, l_agent_name, l_agent_port);
       l_job_name_prefix := 'LDR_DISCST:'|| l_agent_name;
       l_job_name_prefix := SUBSTR(l_job_name_prefix,1,30);     

       --Submit a discard state job
       MGMT_BLACKOUT_ENGINE.submit_discard_state_job
       (
         p_emd_url      => p_emd_url,
         p_target_names => p_job_target_names,
         p_target_types => p_job_target_types,
         p_stop_time    => l_stop_time,
         p_is_proxy     => 'N',
         p_job_name_prefix => l_job_name_prefix
       );
     END IF;

     IF (EMDW_LOG.p_is_info_set) THEN
        EMDW_LOG.INFO('Exiting','EMD_LOADER.submit_discard_state_job');
     END IF;
  END submit_discard_state_job;  

BEGIN
  -- Get the repository owner and set up the queue name to avoid string
  -- concatentation
  G_QUEUE_NAME := G_REPO_OWNER || '.MGMT_LOADER_Q';
  G_DEQUEUE_IOT := G_REPO_OWNER || '.aq$_MGMT_LOADER_QTABLE_I';

  BEGIN
    SELECT parameter_value INTO G_SMETRIC_BATCH_SIZE FROM MGMT_PARAMETERS
    WHERE parameter_name = EM_STRING_METRICS_BATCHSIZE;
  EXCEPTION WHEN NO_DATA_FOUND
  THEN
    G_SMETRIC_BATCH_SIZE := 500;
  END;

  BEGIN
    SELECT parameter_value INTO G_CMETRIC_BATCH_SIZE FROM MGMT_PARAMETERS
    WHERE parameter_name = EM_CURRENT_METRICS_BATCHSIZE;
  EXCEPTION WHEN NO_DATA_FOUND
  THEN
    G_CMETRIC_BATCH_SIZE := 500;
  END;


END EMD_LOADER;
/
show errors;