Rem
Rem $Header: beacon_e2e_rollup_pkgbody.sql 08-jul-2005.06:11:30 scgrover Exp $
Rem
Rem beacon_e2e_rollup_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      beacon_e2e_rollup_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    scgrover    07/07/05 - add extended sql trace 
Rem    snakai      02/09/04 - report successful cleanups as info msgs 
Rem    snakai      09/19/03 - purge sql and conn tables with target guid 
Rem    snakai      08/07/03 - purge sql stmt and conn tables 
Rem    asawant     05/14/03 - Adding JDBC table
Rem    snakai      05/06/03 - snakai_e2e_agg_schema
Rem    snakai      04/16/03 - perf: fix TRUNC on collection_ts
Rem    snakai      12/30/02 - Created
Rem

CREATE OR REPLACE PACKAGE BODY EMD_E2E_ROLLUP AS

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

----------------------------------------------------------------------
PROCEDURE ROLLUP IS

  v_target_guid            p_guid_list_type;
  v_last_load_time         p_date_list_type;
  v_start_timestamp        DATE DEFAULT SYSDATE;

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

BEGIN

  MGMT_SQLTRACE.EXTENDED_SQL_TRACE(EST_E2E_ROLLUP_NAME);

  EMD_INITIALIZE();

  OPEN target_cursor;

  LOOP

    FETCH target_cursor BULK COLLECT INTO v_target_guid,
                                          v_last_load_time
                                          LIMIT p_target_batchsize;
    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

      -- 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);

        -- Run rollups
        EMD_SUMMARY_1HOUR_ROLLUP;
        EMD_SUMMARY_1DAY_ROLLUP;
        EMD_DETAILS_1HOUR_ROLLUP;
        EMD_DETAILS_1DAY_ROLLUP;
        EMD_SQL_1HOUR_ROLLUP;
        EMD_SQL_1DAY_ROLLUP;
        EMD_JDBC_1HOUR_ROLLUP;
        EMD_JDBC_1DAY_ROLLUP;

        -- Run purges
        EMD_SUMMARY_1HOUR_PURGE;
        EMD_SUMMARY_1DAY_PURGE;
        EMD_DETAILS_1HOUR_PURGE;
        EMD_DETAILS_1DAY_PURGE;
        EMD_SQL_1HOUR_PURGE;
        EMD_SQL_1DAY_PURGE;
        EMD_JDBC_1HOUR_PURGE;
        EMD_JDBC_1DAY_PURGE;

	-- Purge thte stmt and conn tables
        EMD_SQL_STMT_CONN_PURGE;

        -- Purge the Loader tables
        EMD_RAW_PURGE;

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

      ELSE

        -- IMPORTANT: The user needs to be alerted of this error as
        -- it stops all rollups/purges for the target.
        MGMT_LOG.LOG_ERROR('E2EROLLUP', 0,
          'Fatal error fetching rollup timestamps (target_guid='
          || v_target_guid(i) || ')');
      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('E2EROLLUP', (SYSDATE-v_start_timestamp)*(24*60*60*1000),
             v_start_timestamp, 'Y', 'RECORDS', 
             ( p_1hour_rollup_records + p_1day_rollup_records +
               p_1hour_purge_records + p_1day_purge_records +
               p_raw_purge_records ));

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

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

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

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

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

EXCEPTION

  WHEN OTHERS THEN

    ROLLBACK;
    IF target_cursor%ISOPEN THEN
      CLOSE target_cursor;
    END IF;
    v_target_guid.DELETE;
    v_last_load_time.DELETE;
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Fatal error (see related errors): ' ||
      SUBSTR(SQLERRM, 1, 1000));

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

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

BEGIN

  -- 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 = p_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 = p_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 = p_day_keep_param;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      p_curdef_day_keep := p_default_day_keep;
    WHEN OTHERS THEN RAISE;
  END;

  p_raw_purge_time          := 0;
  p_raw_purge_records       := 0;
  p_1hour_rollup_time       := 0;
  p_1hour_rollup_records    := 0;
  p_1day_rollup_time        := 0;
  p_1day_rollup_records     := 0;
  p_1hour_purge_time        := 0;
  p_1hour_purge_records     := 0;
  p_1day_purge_time         := 0;
  p_1day_purge_records      := 0;

EXCEPTION

  WHEN OTHERS THEN
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Fatal error: ' || SUBSTR(SQLERRM, 1, 1000));
    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 LIKE 'MGMT_E2E_%'
      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 != 8)
      OR (v_tabname(p_details_1day_idx) != p_details_1day_table)
      OR (v_tabname(p_details_1hour_idx) != p_details_1hour_table)
      OR (v_tabname(p_sql_1day_idx) != p_sql_1day_table)
      OR (v_tabname(p_sql_1hour_idx) != p_sql_1hour_table)
      OR (v_tabname(p_jdbc_1day_idx) != p_jdbc_1day_table)
      OR (v_tabname(p_jdbc_1hour_idx) != p_jdbc_1hour_table)
      OR (v_tabname(p_summary_1day_idx) != p_summary_1day_table)
      OR (v_tabname(p_summary_1hour_idx) != p_summary_1hour_table)
     ) THEN

    v_tabname.DELETE;
    p_cur_rollup_timestamps.DELETE;
    p_cur_rollup_rowids.DELETE;
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Fatal error getting target info: Invalid rollup time values.');
    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 = p_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 = p_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 = p_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;
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Fatal error getting target info: ' || SUBSTR(SQLERRM, 1, 1000));
    RETURN FALSE;

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

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

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

  CURSOR purge_summary_cursor ( c_target_guid RAW,
                                c_max_purge_time DATE ) IS
    SELECT rowid
      FROM MGMT_E2E_SUMMARY
      WHERE target_guid = c_target_guid
        AND collection_timestamp < c_max_purge_time;

  CURSOR purge_details_cursor ( c_target_guid RAW,
                                c_max_purge_time DATE ) IS
    SELECT rowid
      FROM MGMT_E2E_DETAILS
      WHERE target_guid = c_target_guid
        AND collection_timestamp < c_max_purge_time;

  CURSOR purge_sql_cursor ( c_target_guid RAW,
                            c_max_purge_time DATE ) IS
    SELECT rowid
      FROM MGMT_E2E_SQL
      WHERE target_guid = c_target_guid
        AND collection_timestamp < c_max_purge_time;

  CURSOR purge_jdbc_cursor ( c_target_guid RAW,
                             c_max_purge_time DATE ) IS
    SELECT rowid
      FROM MGMT_E2E_JDBC
      WHERE target_guid = c_target_guid
        AND collection_timestamp < c_max_purge_time;

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.

  --
  -- Purge the Summary table
  --
  OPEN purge_summary_cursor( p_cur_target_guid,
                             p_cur_hour_rollup - ((p_cur_raw_keep-1)/24) );
  LOOP

    FETCH purge_summary_cursor BULK COLLECT INTO v_purge_rowid 
      LIMIT p_purge_batchsize;

    -- Exit loop when there's no more data to purge
    EXIT WHEN v_purge_rowid.COUNT <= 0;

    -- Bulk delete by rowid.
    FORALL i IN v_purge_rowid.FIRST..v_purge_rowid.LAST
      DELETE FROM MGMT_E2E_SUMMARY
        WHERE rowid = v_purge_rowid(i);

    COMMIT;

    -- Keep track of the number of inserts
    v_rows_processed := v_rows_processed + v_purge_rowid.COUNT;

    -- Clear the rowid list
    v_purge_rowid.DELETE;

  END LOOP;

  IF purge_summary_cursor%ISOPEN THEN
    CLOSE purge_summary_cursor;
  END IF;
  
  --
  -- Purge the Details table
  --
  OPEN purge_details_cursor( p_cur_target_guid,
                             p_cur_hour_rollup - ((p_cur_raw_keep-1)/24) );
  LOOP

    FETCH purge_details_cursor BULK COLLECT INTO v_purge_rowid 
      LIMIT p_purge_batchsize;

    -- Exit loop when there's no more data to purge
    EXIT WHEN v_purge_rowid.COUNT <= 0;

    -- Bulk delete by rowid.
    FORALL i IN v_purge_rowid.FIRST..v_purge_rowid.LAST
      DELETE FROM MGMT_E2E_DETAILS
        WHERE rowid = v_purge_rowid(i);

    COMMIT;

    -- Keep track of the number of inserts
    v_rows_processed := v_rows_processed + v_purge_rowid.COUNT;

    -- Clear the rowid list
    v_purge_rowid.DELETE;

  END LOOP;

  IF purge_details_cursor%ISOPEN THEN
    CLOSE purge_details_cursor;
  END IF;
  
  --
  -- Purge the Sql table
  --
  OPEN purge_sql_cursor( p_cur_target_guid,
                         p_cur_hour_rollup - ((p_cur_raw_keep-1)/24) );
  LOOP

    FETCH purge_sql_cursor BULK COLLECT INTO v_purge_rowid 
      LIMIT p_purge_batchsize;

    -- Exit loop when there's no more data to purge
    EXIT WHEN v_purge_rowid.COUNT <= 0;

    -- Bulk delete by rowid.
    FORALL i IN v_purge_rowid.FIRST..v_purge_rowid.LAST
      DELETE FROM MGMT_E2E_SQL
        WHERE rowid = v_purge_rowid(i);

    COMMIT;

    -- Keep track of the number of inserts
    v_rows_processed := v_rows_processed + v_purge_rowid.COUNT;

    -- Clear the rowid list
    v_purge_rowid.DELETE;

  END LOOP;

  IF purge_sql_cursor%ISOPEN THEN
    CLOSE purge_sql_cursor;
  END IF;
  
  --
  -- Purge the JDBC table
  --
  OPEN purge_jdbc_cursor( p_cur_target_guid,
                          p_cur_hour_rollup - ((p_cur_raw_keep-1)/24) );
  LOOP

    FETCH purge_jdbc_cursor BULK COLLECT INTO v_purge_rowid 
      LIMIT p_purge_batchsize;

    -- Exit loop when there's no more data to purge
    EXIT WHEN v_purge_rowid.COUNT <= 0;

    -- Bulk delete by rowid.
    FORALL i IN v_purge_rowid.FIRST..v_purge_rowid.LAST
      DELETE FROM MGMT_E2E_JDBC
        WHERE rowid = v_purge_rowid(i);

    COMMIT;

    -- Keep track of the number of inserts
    v_rows_processed := v_rows_processed + v_purge_rowid.COUNT;

    -- Clear the rowid list
    v_purge_rowid.DELETE;

  END LOOP;

  IF purge_jdbc_cursor%ISOPEN THEN
    CLOSE purge_jdbc_cursor;
  END IF;

  -- TODO: Purge the sql stmt table

  -- TODO: Purge the sql conn table

  -- Calculate stats

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

EXCEPTION

  WHEN OTHERS THEN
    ROLLBACK;
    IF purge_summary_cursor%ISOPEN THEN
      CLOSE purge_summary_cursor;
    END IF;
    IF purge_details_cursor%ISOPEN THEN
      CLOSE purge_details_cursor;
    END IF;
    IF purge_sql_cursor%ISOPEN THEN
      CLOSE purge_sql_cursor;
    END IF;
    IF purge_jdbc_cursor%ISOPEN THEN
      CLOSE purge_jdbc_cursor;
    END IF;
    v_purge_rowid.DELETE;
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Error during raw purge: ' || SUBSTR(SQLERRM, 1, 1000));

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

----------------------------------------------------------------------
PROCEDURE EMD_SUMMARY_1HOUR_ROLLUP IS

  v_rollup_rec      p_summary_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 vhost, uri, TRUNC(collection_timestamp, 'HH24'),
           SUM(hit_count), SUM(total_time), 
           MAX(max_time), MIN(min_time), 
           SUM(servlet_count), SUM(servlet_time),
           SUM(jsp_count), SUM(jsp_time),
           SUM(ejb_count), SUM(ejb_time),
           SUM(jdbc_time)
    FROM MGMT_E2E_SUMMARY
    WHERE target_guid = c_target_guid
      AND TRUNC(collection_timestamp, 'HH24') > c_prev_rollup
      AND TRUNC(collection_timestamp, 'HH24') <= c_cur_rollup
    GROUP BY vhost, uri, TRUNC(collection_timestamp, 'HH24');

BEGIN

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

    -- Process batchsize number of rows per commit.
    FETCH rollup_cursor BULK COLLECT INTO v_rollup_rec.vhost,
                                          v_rollup_rec.uri,
                                          v_rollup_rec.rollup_time,
                                          v_rollup_rec.hit_count,
                                          v_rollup_rec.total_time,
                                          v_rollup_rec.max_time,
                                          v_rollup_rec.min_time,
                                          v_rollup_rec.servlet_count,
                                          v_rollup_rec.servlet_time,
                                          v_rollup_rec.jsp_count,
                                          v_rollup_rec.jsp_time,
                                          v_rollup_rec.ejb_count,
                                          v_rollup_rec.ejb_time,
                                          v_rollup_rec.jdbc_time
                                          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.uri.COUNT;

    -- Bulk inserts
    FORALL i IN v_rollup_rec.rollup_time.FIRST..v_rollup_rec.rollup_time.LAST
      INSERT INTO MGMT_E2E_SUMMARY_1HOUR
      (
        target_guid, vhost, uri, rollup_timestamp, hit_count, 
        total_time, max_time, min_time, servlet_count, servlet_time, 
        jsp_count, jsp_time, ejb_count, ejb_time, jdbc_time
      )
      VALUES
      (
        p_cur_target_guid, 
        v_rollup_rec.vhost(i), 
        v_rollup_rec.uri(i),
        v_rollup_rec.rollup_time(i), 
        v_rollup_rec.hit_count(i),
        v_rollup_rec.total_time(i),
        v_rollup_rec.max_time(i),
        v_rollup_rec.min_time(i),
        v_rollup_rec.servlet_count(i),
        v_rollup_rec.servlet_time(i),
        v_rollup_rec.jsp_count(i),
        v_rollup_rec.jsp_time(i),
        v_rollup_rec.ejb_count(i),
        v_rollup_rec.ejb_time(i),
        v_rollup_rec.jdbc_time(i)
      );

    COMMIT;

    -- Free temporary tables
    v_rollup_rec.vhost.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.hit_count.DELETE;
    v_rollup_rec.total_time.DELETE;
    v_rollup_rec.max_time.DELETE;
    v_rollup_rec.min_time.DELETE;
    v_rollup_rec.servlet_count.DELETE;
    v_rollup_rec.servlet_time.DELETE;
    v_rollup_rec.jsp_count.DELETE;
    v_rollup_rec.jsp_time.DELETE;
    v_rollup_rec.ejb_count.DELETE;
    v_rollup_rec.ejb_time.DELETE;
    v_rollup_rec.jdbc_time.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_summary_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;

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.vhost.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.hit_count.DELETE;
    v_rollup_rec.total_time.DELETE;
    v_rollup_rec.max_time.DELETE;
    v_rollup_rec.min_time.DELETE;
    v_rollup_rec.servlet_count.DELETE;
    v_rollup_rec.servlet_time.DELETE;
    v_rollup_rec.jsp_count.DELETE;
    v_rollup_rec.jsp_time.DELETE;
    v_rollup_rec.ejb_count.DELETE;
    v_rollup_rec.ejb_time.DELETE;
    v_rollup_rec.jdbc_time.DELETE;

    -- Cleanup all partial work.  If the cleanup fails, update the rollup
    -- timestamp so that the rollup doesn't get stuck forever.
    IF (EMD_SUMMARY_1HOUR_CLEANUP = TRUE) THEN
      MGMT_LOG.LOG_ERROR('E2EROLLUP', 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('E2EROLLUP', 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_summary_1hour_idx);
        COMMIT;
        MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
          '1hour rollups for the period [' ||
          TO_CHAR(p_cur_rollup_timestamps(p_summary_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
          MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
            'Fatal Error: Unable to set the timestamp (target=' ||
            p_cur_target_guid || '). Error: ' || SUBSTR(SQLERRM, 1, 1000));
          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.vhost.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.hit_count.DELETE;
    v_rollup_rec.total_time.DELETE;
    v_rollup_rec.max_time.DELETE;
    v_rollup_rec.min_time.DELETE;
    v_rollup_rec.servlet_count.DELETE;
    v_rollup_rec.servlet_time.DELETE;
    v_rollup_rec.jsp_count.DELETE;
    v_rollup_rec.jsp_time.DELETE;
    v_rollup_rec.ejb_count.DELETE;
    v_rollup_rec.ejb_time.DELETE;
    v_rollup_rec.jdbc_time.DELETE;

    MGMT_LOG.LOG_ERROR('E2EROLLUP', 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_summary_1hour_idx),
      'MM:DD:YYYY HH24:MI') || ', ' ||
      TO_CHAR(p_cur_hour_rollup,
      'MM:DD:YYYY HH24:MI') || '].  Error: ' || SUBSTR(SQLERRM, 1, 1000));

    BEGIN
      UPDATE MGMT_TARGET_ROLLUP_TIMES
        SET rollup_timestamp = p_cur_hour_rollup
        WHERE rowid = p_cur_rollup_rowids(p_summary_1hour_idx);
      COMMIT;
      MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
        'Successfully skipped over period.  1hour rollup data will not be' ||
        ' generated for this period (target=' || p_cur_target_guid || ').');
    EXCEPTION
      WHEN OTHERS THEN
        MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
          'Fatal Error: Unable to set the 1hour rollup timestamp (target=' ||
          p_cur_target_guid || '). Error: ' || SUBSTR(SQLERRM, 1, 1000));
        RAISE;
    END;

END EMD_SUMMARY_1HOUR_ROLLUP;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_DETAILS_1HOUR_ROLLUP IS

  v_rollup_rec      p_details_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 vhost, app_id, uri, TRUNC(collection_timestamp, 'HH24'),
           key_guid, parent_key_guid, node_id,
           node_attribute, node_type,
           SUM(hit_count), SUM(total_time), SUM(component_time)
    FROM MGMT_E2E_DETAILS
    WHERE target_guid = c_target_guid
      AND TRUNC(collection_timestamp, 'HH24') > c_prev_rollup
      AND TRUNC(collection_timestamp, 'HH24') <= c_cur_rollup
    GROUP BY vhost, app_id, uri, TRUNC(collection_timestamp, 'HH24'),
          key_guid, parent_key_guid, node_id, node_attribute, node_type;

BEGIN

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

    -- Process batchsize number of rows per commit.
    FETCH rollup_cursor BULK COLLECT INTO v_rollup_rec.vhost,
                                          v_rollup_rec.app_id,
                                          v_rollup_rec.uri,
                                          v_rollup_rec.rollup_time,
                                          v_rollup_rec.key_guid,
                                          v_rollup_rec.parent_key_guid,
                                          v_rollup_rec.node_id,
                                          v_rollup_rec.node_attr,
                                          v_rollup_rec.node_type,
                                          v_rollup_rec.hit_count,
                                          v_rollup_rec.total_time,
                                          v_rollup_rec.comp_time
                                          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.uri.COUNT;

    -- Bulk inserts
    FORALL i IN v_rollup_rec.rollup_time.FIRST..v_rollup_rec.rollup_time.LAST
      INSERT INTO MGMT_E2E_DETAILS_1HOUR
      (
        target_guid, vhost, app_id, uri, key_guid, parent_key_guid,
        node_id, node_attribute, node_type,
        rollup_timestamp, hit_count, total_time, component_time
      )
      VALUES
      (
        p_cur_target_guid, 
        v_rollup_rec.vhost(i), 
        v_rollup_rec.app_id(i), 
        v_rollup_rec.uri(i),
        v_rollup_rec.key_guid(i),
        v_rollup_rec.parent_key_guid(i),
        v_rollup_rec.node_id(i),
        v_rollup_rec.node_attr(i),
        v_rollup_rec.node_type(i),
        v_rollup_rec.rollup_time(i), 
        v_rollup_rec.hit_count(i),
        v_rollup_rec.total_time(i),
        v_rollup_rec.comp_time(i)
      );

    COMMIT;

    -- Free temporary tables
    v_rollup_rec.vhost.DELETE;
    v_rollup_rec.app_id.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.key_guid.DELETE;
    v_rollup_rec.parent_key_guid.DELETE;
    v_rollup_rec.node_id.DELETE;
    v_rollup_rec.node_attr.DELETE;
    v_rollup_rec.node_type.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.hit_count.DELETE;
    v_rollup_rec.total_time.DELETE;
    v_rollup_rec.comp_time.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_details_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;

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.vhost.DELETE;
    v_rollup_rec.app_id.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.key_guid.DELETE;
    v_rollup_rec.parent_key_guid.DELETE;
    v_rollup_rec.node_id.DELETE;
    v_rollup_rec.node_attr.DELETE;
    v_rollup_rec.node_type.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.hit_count.DELETE;
    v_rollup_rec.total_time.DELETE;
    v_rollup_rec.comp_time.DELETE;

    -- Cleanup all partial work.  If the cleanup fails, update the rollup
    -- timestamp so that the rollup doesn't get stuck forever.
    IF (EMD_DETAILS_1HOUR_CLEANUP = TRUE) THEN
      MGMT_LOG.LOG_ERROR('E2EROLLUP', 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('E2EROLLUP', 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_details_1hour_idx);
        COMMIT;
        MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
          '1hour rollups for the period [' ||
          TO_CHAR(p_cur_rollup_timestamps(p_details_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
          MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
            'Fatal Error: Unable to set the timestamp (target=' ||
            p_cur_target_guid || '). Error: ' || SUBSTR(SQLERRM, 1, 1000));
          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.vhost.DELETE;
    v_rollup_rec.app_id.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.key_guid.DELETE;
    v_rollup_rec.parent_key_guid.DELETE;
    v_rollup_rec.node_id.DELETE;
    v_rollup_rec.node_attr.DELETE;
    v_rollup_rec.node_type.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.hit_count.DELETE;
    v_rollup_rec.total_time.DELETE;
    v_rollup_rec.comp_time.DELETE;

    MGMT_LOG.LOG_ERROR('E2EROLLUP', 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_details_1hour_idx),
      'MM:DD:YYYY HH24:MI') || ', ' ||
      TO_CHAR(p_cur_hour_rollup,
      'MM:DD:YYYY HH24:MI') || '].  Error: ' || SUBSTR(SQLERRM, 1, 1000));

    BEGIN
      UPDATE MGMT_TARGET_ROLLUP_TIMES
        SET rollup_timestamp = p_cur_hour_rollup
        WHERE rowid = p_cur_rollup_rowids(p_details_1hour_idx);
      COMMIT;
      MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
        'Successfully skipped over period.  1hour rollup data will not be' ||
        ' generated for this period (target=' || p_cur_target_guid || ').');
    EXCEPTION
      WHEN OTHERS THEN
        MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
          'Fatal Error: Unable to set the 1hour rollup timestamp (target=' ||
          p_cur_target_guid || '). Error: ' || SUBSTR(SQLERRM, 1, 1000));
        RAISE;
    END;

END EMD_DETAILS_1HOUR_ROLLUP;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_SQL_1HOUR_ROLLUP IS

  v_rollup_rec      p_sql_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 vhost, app_id, uri, 
           TRUNC(collection_timestamp, 'HH24'), key_guid, 
           SUM(exec_count), SUM(exec_time),
           SUM(fetch_count), SUM(fetch_time)
    FROM MGMT_E2E_SQL
    WHERE target_guid = c_target_guid
      AND TRUNC(collection_timestamp, 'HH24') > c_prev_rollup
      AND TRUNC(collection_timestamp, 'HH24') <= c_cur_rollup
    GROUP BY vhost, app_id, uri, 
          TRUNC(collection_timestamp, 'HH24'), key_guid;

BEGIN

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

    -- Process batchsize number of rows per commit.
    FETCH rollup_cursor BULK COLLECT INTO v_rollup_rec.vhost,
                                          v_rollup_rec.app_id,
                                          v_rollup_rec.uri,
                                          v_rollup_rec.rollup_time,
                                          v_rollup_rec.key_guid,
                                          v_rollup_rec.exec_count,
                                          v_rollup_rec.exec_time,
                                          v_rollup_rec.fetch_count,
                                          v_rollup_rec.fetch_time
                                          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.uri.COUNT;

    -- Bulk inserts
    FORALL i IN v_rollup_rec.rollup_time.FIRST..v_rollup_rec.rollup_time.LAST
      INSERT INTO MGMT_E2E_SQL_1HOUR
      (
        target_guid, vhost, app_id, uri, key_guid, rollup_timestamp, 
        exec_count, exec_time, fetch_count, fetch_time
      )
      VALUES
      (
        p_cur_target_guid, 
        v_rollup_rec.vhost(i), 
        v_rollup_rec.app_id(i), 
        v_rollup_rec.uri(i),
        v_rollup_rec.key_guid(i),
        v_rollup_rec.rollup_time(i), 
        v_rollup_rec.exec_count(i),
        v_rollup_rec.exec_time(i),
        v_rollup_rec.fetch_count(i),
        v_rollup_rec.fetch_time(i)
      );

    COMMIT;

    -- Free temporary tables
    v_rollup_rec.vhost.DELETE;
    v_rollup_rec.app_id.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.key_guid.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.exec_count.DELETE;
    v_rollup_rec.exec_time.DELETE;
    v_rollup_rec.fetch_count.DELETE;
    v_rollup_rec.fetch_time.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_sql_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;

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.vhost.DELETE;
    v_rollup_rec.app_id.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.key_guid.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.exec_count.DELETE;
    v_rollup_rec.exec_time.DELETE;
    v_rollup_rec.fetch_count.DELETE;
    v_rollup_rec.fetch_time.DELETE;

    -- Cleanup all partial work.  If the cleanup fails, update the rollup
    -- timestamp so that the rollup doesn't get stuck forever.
    IF (EMD_SQL_1HOUR_CLEANUP = TRUE) THEN
      MGMT_LOG.LOG_ERROR('E2EROLLUP', 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('E2EROLLUP', 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_sql_1hour_idx);
        COMMIT;
        MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
          '1hour rollups for the period [' ||
          TO_CHAR(p_cur_rollup_timestamps(p_sql_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
          MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
            'Fatal Error: Unable to set the timestamp (target=' ||
            p_cur_target_guid || '). Error: ' || SUBSTR(SQLERRM, 1, 1000));
          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.vhost.DELETE;
    v_rollup_rec.app_id.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.key_guid.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.exec_count.DELETE;
    v_rollup_rec.exec_time.DELETE;
    v_rollup_rec.fetch_count.DELETE;
    v_rollup_rec.fetch_time.DELETE;

    MGMT_LOG.LOG_ERROR('E2EROLLUP', 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_sql_1hour_idx),
      'MM:DD:YYYY HH24:MI') || ', ' ||
      TO_CHAR(p_cur_hour_rollup,
      'MM:DD:YYYY HH24:MI') || '].  Error: ' || SUBSTR(SQLERRM, 1, 1000));

    BEGIN
      UPDATE MGMT_TARGET_ROLLUP_TIMES
        SET rollup_timestamp = p_cur_hour_rollup
        WHERE rowid = p_cur_rollup_rowids(p_sql_1hour_idx);
      COMMIT;
      MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
        'Successfully skipped over period.  1hour rollup data will not be' ||
        ' generated for this period (target=' || p_cur_target_guid || ').');
    EXCEPTION
      WHEN OTHERS THEN
        MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
          'Fatal Error: Unable to set the 1hour rollup timestamp (target=' ||
          p_cur_target_guid || '). Error: ' || SUBSTR(SQLERRM, 1, 1000));
        RAISE;
    END;

END EMD_SQL_1HOUR_ROLLUP;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_JDBC_1HOUR_ROLLUP IS

  v_rollup_rec      p_jdbc_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 vhost, app_id, uri, 
           TRUNC(collection_timestamp, 'HH24'), key_guid, 
           SUM(used_conn_count), SUM(conn_cache_hit),
           SUM(conn_cache_miss), SUM(stmt_create_time),
           SUM(stmt_cache_hit), SUM(stmt_cache_miss)
    FROM MGMT_E2E_JDBC
    WHERE target_guid = c_target_guid
      AND TRUNC(collection_timestamp, 'HH24') > c_prev_rollup
      AND TRUNC(collection_timestamp, 'HH24') <= c_cur_rollup
    GROUP BY vhost, app_id, uri, 
          TRUNC(collection_timestamp, 'HH24'), key_guid;

BEGIN

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

    -- Process batchsize number of rows per commit.
    FETCH rollup_cursor BULK COLLECT INTO v_rollup_rec.vhost,
                                          v_rollup_rec.app_id,
                                          v_rollup_rec.uri,
                                          v_rollup_rec.rollup_time,
                                          v_rollup_rec.key_guid,
                                          v_rollup_rec.used_conn_count,
                                          v_rollup_rec.conn_cache_hit,
                                          v_rollup_rec.conn_cache_miss,
                                          v_rollup_rec.stmt_create_time,
                                          v_rollup_rec.stmt_cache_hit,
                                          v_rollup_rec.stmt_cache_miss
                                          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.uri.COUNT;

    -- Bulk inserts
    FORALL i IN v_rollup_rec.rollup_time.FIRST..v_rollup_rec.rollup_time.LAST
      INSERT INTO MGMT_E2E_JDBC_1HOUR
      (
        target_guid, vhost, app_id, uri, key_guid, rollup_timestamp, 
        used_conn_count, conn_cache_hit, conn_cache_miss, stmt_create_time,
        stmt_cache_hit, stmt_cache_miss
      )
      VALUES
      (
        p_cur_target_guid, 
        v_rollup_rec.vhost(i), 
        v_rollup_rec.app_id(i), 
        v_rollup_rec.uri(i),
        v_rollup_rec.key_guid(i),
        v_rollup_rec.rollup_time(i), 
        v_rollup_rec.used_conn_count(i),
        v_rollup_rec.conn_cache_hit(i),
        v_rollup_rec.conn_cache_miss(i),
        v_rollup_rec.stmt_create_time(i),
        v_rollup_rec.stmt_cache_hit(i),
        v_rollup_rec.stmt_cache_miss(i)
      );

    COMMIT;

    -- Free temporary tables
    v_rollup_rec.vhost.DELETE;
    v_rollup_rec.app_id.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.key_guid.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.used_conn_count.DELETE;
    v_rollup_rec.conn_cache_hit.DELETE;
    v_rollup_rec.conn_cache_miss.DELETE;
    v_rollup_rec.stmt_create_time.DELETE;
    v_rollup_rec.stmt_cache_hit.DELETE;
    v_rollup_rec.stmt_cache_miss.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_jdbc_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;

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.vhost.DELETE;
    v_rollup_rec.app_id.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.key_guid.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.used_conn_count.DELETE;
    v_rollup_rec.conn_cache_hit.DELETE;
    v_rollup_rec.conn_cache_miss.DELETE;
    v_rollup_rec.stmt_create_time.DELETE;
    v_rollup_rec.stmt_cache_hit.DELETE;
    v_rollup_rec.stmt_cache_miss.DELETE;

    -- Cleanup all partial work.  If the cleanup fails, update the rollup
    -- timestamp so that the rollup doesn't get stuck forever.
    IF (EMD_JDBC_1HOUR_CLEANUP = TRUE) THEN
      MGMT_LOG.LOG_ERROR('E2EROLLUP', 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('E2EROLLUP', 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_jdbc_1hour_idx);
        COMMIT;
        MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
          '1hour rollups for the period [' ||
          TO_CHAR(p_cur_rollup_timestamps(p_jdbc_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
          MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
            'Fatal Error: Unable to set the timestamp (target=' ||
            p_cur_target_guid || '). Error: ' || SUBSTR(SQLERRM, 1, 1000));
          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.vhost.DELETE;
    v_rollup_rec.app_id.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.key_guid.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.used_conn_count.DELETE;
    v_rollup_rec.conn_cache_hit.DELETE;
    v_rollup_rec.conn_cache_miss.DELETE;
    v_rollup_rec.stmt_create_time.DELETE;
    v_rollup_rec.stmt_cache_hit.DELETE;
    v_rollup_rec.stmt_cache_miss.DELETE;

    MGMT_LOG.LOG_ERROR('E2EROLLUP', 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_jdbc_1hour_idx),
      'MM:DD:YYYY HH24:MI') || ', ' ||
      TO_CHAR(p_cur_hour_rollup,
      'MM:DD:YYYY HH24:MI') || '].  Error: ' || SUBSTR(SQLERRM, 1, 1000));

    BEGIN
      UPDATE MGMT_TARGET_ROLLUP_TIMES
        SET rollup_timestamp = p_cur_hour_rollup
        WHERE rowid = p_cur_rollup_rowids(p_jdbc_1hour_idx);
      COMMIT;
      MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
        'Successfully skipped over period.  1hour rollup data will not be' ||
        ' generated for this period (target=' || p_cur_target_guid || ').');
    EXCEPTION
      WHEN OTHERS THEN
        MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
          'Fatal Error: Unable to set the 1hour rollup timestamp (target=' ||
          p_cur_target_guid || '). Error: ' || SUBSTR(SQLERRM, 1, 1000));
        RAISE;
    END;

END EMD_JDBC_1HOUR_ROLLUP;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_SUMMARY_1HOUR_PURGE IS

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

  CURSOR purge_cursor ( c_target_guid RAW,
                        c_max_purge_time DATE ) IS
    SELECT rowid
      FROM MGMT_E2E_SUMMARY_1HOUR
      WHERE target_guid = c_target_guid
        AND rollup_timestamp < c_max_purge_time;

BEGIN

  OPEN purge_cursor( p_cur_target_guid, p_cur_hour_purge );
  LOOP

    -- Process batchsize number of rows per commit.
    FETCH purge_cursor BULK COLLECT INTO v_purge_rowid 
      LIMIT p_purge_batchsize;

    -- Exit when nothing to purge
    EXIT WHEN v_purge_rowid.COUNT <= 0;  
 
    -- Bulk delete by rowid
    FORALL i IN v_purge_rowid.FIRST..v_purge_rowid.LAST
      DELETE FROM MGMT_E2E_SUMMARY_1HOUR
        WHERE rowid = v_purge_rowid(i);

    COMMIT;

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

    -- Clear the rowid list
    v_purge_rowid.DELETE;

  END LOOP;

  IF purge_cursor%ISOPEN THEN
    CLOSE purge_cursor;
  END IF;

  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;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    IF purge_cursor%ISOPEN THEN
      CLOSE purge_cursor;
    END IF;
    v_purge_rowid.DELETE;
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Error during 1hour purge: ' || SUBSTR(SQLERRM, 1, 1000));

END EMD_SUMMARY_1HOUR_PURGE;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_DETAILS_1HOUR_PURGE IS

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

  CURSOR purge_cursor ( c_target_guid RAW,
                        c_max_purge_time DATE ) IS
    SELECT rowid
      FROM MGMT_E2E_DETAILS_1HOUR
      WHERE target_guid = c_target_guid
        AND rollup_timestamp < c_max_purge_time;

BEGIN

  OPEN purge_cursor( p_cur_target_guid, p_cur_hour_purge );
  LOOP

    -- Process batchsize number of rows per commit.
    FETCH purge_cursor BULK COLLECT INTO v_purge_rowid 
      LIMIT p_purge_batchsize;

    -- Exit when nothing to purge
    EXIT WHEN v_purge_rowid.COUNT <= 0;  
 
    -- Bulk delete by rowid
    FORALL i IN v_purge_rowid.FIRST..v_purge_rowid.LAST
      DELETE FROM MGMT_E2E_DETAILS_1HOUR
        WHERE rowid = v_purge_rowid(i);

    COMMIT;

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

    -- Clear the rowid list
    v_purge_rowid.DELETE;

  END LOOP;

  IF purge_cursor%ISOPEN THEN
    CLOSE purge_cursor;
  END IF;

  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;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    IF purge_cursor%ISOPEN THEN
      CLOSE purge_cursor;
    END IF;
    v_purge_rowid.DELETE;
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Error during 1hour purge: ' || SUBSTR(SQLERRM, 1, 1000));

END EMD_DETAILS_1HOUR_PURGE;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_SQL_1HOUR_PURGE IS

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

  CURSOR purge_cursor ( c_target_guid RAW,
                        c_max_purge_time DATE ) IS
    SELECT rowid
      FROM MGMT_E2E_SQL_1HOUR
      WHERE target_guid = c_target_guid
        AND rollup_timestamp < c_max_purge_time;

BEGIN

  OPEN purge_cursor( p_cur_target_guid, p_cur_hour_purge );
  LOOP

    -- Process batchsize number of rows per commit.
    FETCH purge_cursor BULK COLLECT INTO v_purge_rowid 
      LIMIT p_purge_batchsize;

    -- Exit when nothing to purge
    EXIT WHEN v_purge_rowid.COUNT <= 0;  
 
    -- Bulk delete by rowid
    FORALL i IN v_purge_rowid.FIRST..v_purge_rowid.LAST
      DELETE FROM MGMT_E2E_SQL_1HOUR
        WHERE rowid = v_purge_rowid(i);

    COMMIT;

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

    -- Clear the rowid list
    v_purge_rowid.DELETE;

  END LOOP;

  IF purge_cursor%ISOPEN THEN
    CLOSE purge_cursor;
  END IF;

  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;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    IF purge_cursor%ISOPEN THEN
      CLOSE purge_cursor;
    END IF;
    v_purge_rowid.DELETE;
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Error during 1hour purge: ' || SUBSTR(SQLERRM, 1, 1000));

END EMD_SQL_1HOUR_PURGE;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_JDBC_1HOUR_PURGE IS

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

  CURSOR purge_cursor ( c_target_guid RAW,
                        c_max_purge_time DATE ) IS
    SELECT rowid
      FROM MGMT_E2E_JDBC_1HOUR
      WHERE target_guid = c_target_guid
        AND rollup_timestamp < c_max_purge_time;

BEGIN

  OPEN purge_cursor( p_cur_target_guid, p_cur_hour_purge );
  LOOP

    -- Process batchsize number of rows per commit.
    FETCH purge_cursor BULK COLLECT INTO v_purge_rowid 
      LIMIT p_purge_batchsize;

    -- Exit when nothing to purge
    EXIT WHEN v_purge_rowid.COUNT <= 0;  
 
    -- Bulk delete by rowid
    FORALL i IN v_purge_rowid.FIRST..v_purge_rowid.LAST
      DELETE FROM MGMT_E2E_JDBC_1HOUR
        WHERE rowid = v_purge_rowid(i);

    COMMIT;

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

    -- Clear the rowid list
    v_purge_rowid.DELETE;

  END LOOP;

  IF purge_cursor%ISOPEN THEN
    CLOSE purge_cursor;
  END IF;

  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;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    IF purge_cursor%ISOPEN THEN
      CLOSE purge_cursor;
    END IF;
    v_purge_rowid.DELETE;
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Error during 1hour purge: ' || SUBSTR(SQLERRM, 1, 1000));

END EMD_JDBC_1HOUR_PURGE;
----------------------------------------------------------------------

----------------------------------------------------------------------
FUNCTION EMD_SUMMARY_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_E2E_SUMMARY_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_summary_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_E2E_SUMMARY_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;
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Error during 1hour cleanup: ' || SUBSTR(SQLERRM, 1, 1100));
    RETURN FALSE;

END EMD_SUMMARY_1HOUR_CLEANUP;
----------------------------------------------------------------------

----------------------------------------------------------------------
FUNCTION EMD_DETAILS_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_E2E_DETAILS_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_details_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_E2E_DETAILS_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;
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Error during 1hour cleanup: ' || SUBSTR(SQLERRM, 1, 1100));
    RETURN FALSE;

END EMD_DETAILS_1HOUR_CLEANUP;
----------------------------------------------------------------------

----------------------------------------------------------------------
FUNCTION EMD_SQL_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_E2E_SQL_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_sql_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_E2E_SQL_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;
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Error during 1hour cleanup: ' || SUBSTR(SQLERRM, 1, 1100));
    RETURN FALSE;

END EMD_SQL_1HOUR_CLEANUP;
----------------------------------------------------------------------

----------------------------------------------------------------------
FUNCTION EMD_JDBC_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_E2E_JDBC_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_jdbc_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_E2E_JDBC_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;
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Error during 1hour cleanup: ' || SUBSTR(SQLERRM, 1, 1100));
    RETURN FALSE;

END EMD_JDBC_1HOUR_CLEANUP;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_SUMMARY_1DAY_ROLLUP IS

  v_rollup_rec      p_summary_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 vhost, uri, TRUNC(rollup_timestamp, 'DD'),
           SUM(hit_count), SUM(total_time), 
           MAX(max_time), MIN(min_time), 
           SUM(servlet_count), SUM(servlet_time),
           SUM(jsp_count), SUM(jsp_time),
           SUM(ejb_count), SUM(ejb_time),
           SUM(jdbc_time)
    FROM MGMT_E2E_SUMMARY_1HOUR
    WHERE target_guid = c_target_guid
      AND TRUNC(rollup_timestamp, 'DD') > c_prev_rollup
      AND TRUNC(rollup_timestamp, 'DD') <= c_cur_rollup
    GROUP BY vhost, uri, TRUNC(rollup_timestamp, 'DD');

BEGIN

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

    -- Process batchsize number of rows per commit.
    FETCH rollup_cursor BULK COLLECT INTO v_rollup_rec.vhost,
                                          v_rollup_rec.uri,
                                          v_rollup_rec.rollup_time,
                                          v_rollup_rec.hit_count,
                                          v_rollup_rec.total_time,
                                          v_rollup_rec.max_time,
                                          v_rollup_rec.min_time,
                                          v_rollup_rec.servlet_count,
                                          v_rollup_rec.servlet_time,
                                          v_rollup_rec.jsp_count,
                                          v_rollup_rec.jsp_time,
                                          v_rollup_rec.ejb_count,
                                          v_rollup_rec.ejb_time,
                                          v_rollup_rec.jdbc_time
                                          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.uri.COUNT;

    -- Bulk inserts
    FORALL i IN v_rollup_rec.rollup_time.FIRST..v_rollup_rec.rollup_time.LAST
      INSERT INTO MGMT_E2E_SUMMARY_1DAY
      (
        target_guid, vhost, uri, rollup_timestamp, hit_count, 
        total_time, max_time, min_time, servlet_count, servlet_time, 
        jsp_count, jsp_time, ejb_count, ejb_time, jdbc_time
      )
      VALUES
      (
        p_cur_target_guid, 
        v_rollup_rec.vhost(i), 
        v_rollup_rec.uri(i),
        v_rollup_rec.rollup_time(i), 
        v_rollup_rec.hit_count(i),
        v_rollup_rec.total_time(i),
        v_rollup_rec.max_time(i),
        v_rollup_rec.min_time(i),
        v_rollup_rec.servlet_count(i),
        v_rollup_rec.servlet_time(i),
        v_rollup_rec.jsp_count(i),
        v_rollup_rec.jsp_time(i),
        v_rollup_rec.ejb_count(i),
        v_rollup_rec.ejb_time(i),
        v_rollup_rec.jdbc_time(i)
      );

    COMMIT;

    -- Free temporary tables
    v_rollup_rec.vhost.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.hit_count.DELETE;
    v_rollup_rec.total_time.DELETE;
    v_rollup_rec.max_time.DELETE;
    v_rollup_rec.min_time.DELETE;
    v_rollup_rec.servlet_count.DELETE;
    v_rollup_rec.servlet_time.DELETE;
    v_rollup_rec.jsp_count.DELETE;
    v_rollup_rec.jsp_time.DELETE;
    v_rollup_rec.ejb_count.DELETE;
    v_rollup_rec.ejb_time.DELETE;
    v_rollup_rec.jdbc_time.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_summary_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;

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.vhost.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.hit_count.DELETE;
    v_rollup_rec.total_time.DELETE;
    v_rollup_rec.max_time.DELETE;
    v_rollup_rec.min_time.DELETE;
    v_rollup_rec.servlet_count.DELETE;
    v_rollup_rec.servlet_time.DELETE;
    v_rollup_rec.jsp_count.DELETE;
    v_rollup_rec.jsp_time.DELETE;
    v_rollup_rec.ejb_count.DELETE;
    v_rollup_rec.ejb_time.DELETE;
    v_rollup_rec.jdbc_time.DELETE;

    -- Cleanup all partial work.  If the cleanup fails, update the rollup
    -- timestamp so that the rollup doesn't get stuck forever.
    IF (EMD_SUMMARY_1DAY_CLEANUP = TRUE) THEN
      MGMT_LOG.LOG_ERROR('E2EROLLUP', 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('E2EROLLUP', 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_summary_1day_idx);
        COMMIT;
        MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
          '1day rollups for the period [' ||
          TO_CHAR(p_cur_rollup_timestamps(p_summary_1day_idx),
          'MM:DD:YYYY HH24:MI') || ', ' ||
          TO_CHAR(p_cur_day_rollup, 'MM:DD:YYYY HH24:MI') || ']
          cannot be performed (target=' || p_cur_target_guid || ')');
      EXCEPTION
        WHEN OTHERS THEN
          MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
            'Fatal Error: Unable to set the timestamp (target=' ||
            p_cur_target_guid || '). Error: ' || SUBSTR(SQLERRM, 1, 1000));
          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.vhost.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.hit_count.DELETE;
    v_rollup_rec.total_time.DELETE;
    v_rollup_rec.max_time.DELETE;
    v_rollup_rec.min_time.DELETE;
    v_rollup_rec.servlet_count.DELETE;
    v_rollup_rec.servlet_time.DELETE;
    v_rollup_rec.jsp_count.DELETE;
    v_rollup_rec.jsp_time.DELETE;
    v_rollup_rec.ejb_count.DELETE;
    v_rollup_rec.ejb_time.DELETE;
    v_rollup_rec.jdbc_time.DELETE;

    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'The following unrecoverable error occurred while performing the' ||
      ' 1day rollup (target=' || p_cur_target_guid || ').  Attempting to' ||
      ' skip over rollup period [' ||
      TO_CHAR(p_cur_rollup_timestamps(p_summary_1day_idx),
      'MM:DD:YYYY HH24:MI') || ', ' ||
      TO_CHAR(p_cur_day_rollup,
      'MM:DD:YYYY HH24:MI') || '].  Error: ' || SUBSTR(SQLERRM, 1, 1000));

    BEGIN
      UPDATE MGMT_TARGET_ROLLUP_TIMES
        SET rollup_timestamp = p_cur_day_rollup
        WHERE rowid = p_cur_rollup_rowids(p_summary_1day_idx);
      COMMIT;
      MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
        'Successfully skipped over period.  1day rollup data will not be' ||
        ' generated for this period (target=' || p_cur_target_guid || ').');
    EXCEPTION
      WHEN OTHERS THEN
        MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
          'Fatal Error: Unable to set the 1day rollup timestamp (target=' ||
          p_cur_target_guid || '). Error: ' || SUBSTR(SQLERRM, 1, 1000));
        RAISE;
    END;

END EMD_SUMMARY_1DAY_ROLLUP;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_DETAILS_1DAY_ROLLUP IS

  v_rollup_rec      p_details_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 vhost, app_id, uri, TRUNC(rollup_timestamp, 'DD'),
           key_guid, parent_key_guid, node_id,
           node_attribute, node_type,
           SUM(hit_count), SUM(total_time), SUM(component_time)
    FROM MGMT_E2E_DETAILS_1HOUR
    WHERE target_guid = c_target_guid
      AND TRUNC(rollup_timestamp, 'DD') > c_prev_rollup
      AND TRUNC(rollup_timestamp, 'DD') <= c_cur_rollup
    GROUP BY vhost, app_id, uri, TRUNC(rollup_timestamp, 'DD'),
          key_guid, parent_key_guid, node_id,
          node_attribute, node_type;

BEGIN

  OPEN rollup_cursor( p_cur_target_guid,
                      p_cur_rollup_timestamps(p_details_1day_idx),
                      p_cur_day_rollup );

  LOOP

    -- Process batchsize number of rows per commit.
    FETCH rollup_cursor BULK COLLECT INTO v_rollup_rec.vhost,
                                          v_rollup_rec.app_id,
                                          v_rollup_rec.uri,
                                          v_rollup_rec.rollup_time,
                                          v_rollup_rec.key_guid,
                                          v_rollup_rec.parent_key_guid,
                                          v_rollup_rec.node_id,
                                          v_rollup_rec.node_attr,
                                          v_rollup_rec.node_type,
                                          v_rollup_rec.hit_count,
                                          v_rollup_rec.total_time,
                                          v_rollup_rec.comp_time
                                          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.uri.COUNT;

    -- Bulk inserts
    FORALL i IN v_rollup_rec.rollup_time.FIRST..v_rollup_rec.rollup_time.LAST
      INSERT INTO MGMT_E2E_DETAILS_1DAY
      (
        target_guid, vhost, app_id, uri, key_guid, parent_key_guid,
        node_id, node_attribute, node_type,
        rollup_timestamp, hit_count, total_time, component_time
      )
      VALUES
      (
        p_cur_target_guid, 
        v_rollup_rec.vhost(i), 
        v_rollup_rec.app_id(i), 
        v_rollup_rec.uri(i),
        v_rollup_rec.key_guid(i),
        v_rollup_rec.parent_key_guid(i),
        v_rollup_rec.node_id(i),
        v_rollup_rec.node_attr(i),
        v_rollup_rec.node_type(i),
        v_rollup_rec.rollup_time(i), 
        v_rollup_rec.hit_count(i),
        v_rollup_rec.total_time(i),
        v_rollup_rec.comp_time(i)
      );

    COMMIT;

    -- Free temporary tables
    v_rollup_rec.vhost.DELETE;
    v_rollup_rec.app_id.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.key_guid.DELETE;
    v_rollup_rec.parent_key_guid.DELETE;
    v_rollup_rec.node_id.DELETE;
    v_rollup_rec.node_attr.DELETE;
    v_rollup_rec.node_type.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.hit_count.DELETE;
    v_rollup_rec.total_time.DELETE;
    v_rollup_rec.comp_time.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_details_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;

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.vhost.DELETE;
    v_rollup_rec.app_id.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.key_guid.DELETE;
    v_rollup_rec.parent_key_guid.DELETE;
    v_rollup_rec.node_id.DELETE;
    v_rollup_rec.node_attr.DELETE;
    v_rollup_rec.node_type.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.hit_count.DELETE;
    v_rollup_rec.total_time.DELETE;
    v_rollup_rec.comp_time.DELETE;

    -- Cleanup all partial work.  If the cleanup fails, update the rollup
    -- timestamp so that the rollup doesn't get stuck forever.
    IF (EMD_DETAILS_1DAY_CLEANUP = TRUE) THEN
      MGMT_LOG.LOG_ERROR('E2EROLLUP', 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('E2EROLLUP', 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_details_1day_idx);
        COMMIT;
        MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
          '1day rollups for the period [' ||
          TO_CHAR(p_cur_rollup_timestamps(p_details_1day_idx),
          'MM:DD:YYYY HH24:MI') || ', ' ||
          TO_CHAR(p_cur_day_rollup, 'MM:DD:YYYY HH24:MI') || ']
          cannot be performed (target=' || p_cur_target_guid || ')');
      EXCEPTION
        WHEN OTHERS THEN
          MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
            'Fatal Error: Unable to set the timestamp (target=' ||
            p_cur_target_guid || '). Error: ' || SUBSTR(SQLERRM, 1, 1000));
          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.vhost.DELETE;
    v_rollup_rec.app_id.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.key_guid.DELETE;
    v_rollup_rec.parent_key_guid.DELETE;
    v_rollup_rec.node_id.DELETE;
    v_rollup_rec.node_attr.DELETE;
    v_rollup_rec.node_type.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.hit_count.DELETE;
    v_rollup_rec.total_time.DELETE;
    v_rollup_rec.comp_time.DELETE;

    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'The following unrecoverable error occurred while performing the' ||
      ' 1day rollup (target=' || p_cur_target_guid || ').  Attempting to' ||
      ' skip over rollup period [' ||
      TO_CHAR(p_cur_rollup_timestamps(p_details_1day_idx),
      'MM:DD:YYYY HH24:MI') || ', ' ||
      TO_CHAR(p_cur_day_rollup,
      'MM:DD:YYYY HH24:MI') || '].  Error: ' || SUBSTR(SQLERRM, 1, 1000));

    BEGIN
      UPDATE MGMT_TARGET_ROLLUP_TIMES
        SET rollup_timestamp = p_cur_day_rollup
        WHERE rowid = p_cur_rollup_rowids(p_details_1day_idx);
      COMMIT;
      MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
        'Successfully skipped over period.  1day rollup data will not be' ||
        ' generated for this period (target=' || p_cur_target_guid || ').');
    EXCEPTION
      WHEN OTHERS THEN
        MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
          'Fatal Error: Unable to set the 1day rollup timestamp (target=' ||
          p_cur_target_guid || '). Error: ' || SUBSTR(SQLERRM, 1, 1000));
        RAISE;
    END;

END EMD_DETAILS_1DAY_ROLLUP;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_SQL_1DAY_ROLLUP IS

  v_rollup_rec      p_sql_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 vhost, app_id, uri, 
           TRUNC(rollup_timestamp, 'DD'), key_guid,
           SUM(exec_count), SUM(exec_time),
           SUM(fetch_count), SUM(fetch_time)
    FROM MGMT_E2E_SQL_1HOUR
    WHERE target_guid = c_target_guid
      AND TRUNC(rollup_timestamp, 'DD') > c_prev_rollup
      AND TRUNC(rollup_timestamp, 'DD') <= c_cur_rollup
    GROUP BY vhost, app_id, uri, 
          TRUNC(rollup_timestamp, 'DD'), key_guid;

BEGIN

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

    -- Process batchsize number of rows per commit.
    FETCH rollup_cursor BULK COLLECT INTO v_rollup_rec.vhost,
                                          v_rollup_rec.app_id,
                                          v_rollup_rec.uri,
                                          v_rollup_rec.rollup_time,
                                          v_rollup_rec.key_guid,
                                          v_rollup_rec.exec_count,
                                          v_rollup_rec.exec_time,
                                          v_rollup_rec.fetch_count,
                                          v_rollup_rec.fetch_time
                                          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.uri.COUNT;

    -- Bulk inserts
    FORALL i IN v_rollup_rec.rollup_time.FIRST..v_rollup_rec.rollup_time.LAST
      INSERT INTO MGMT_E2E_SQL_1DAY
      (
        target_guid, vhost, app_id, uri, key_guid, rollup_timestamp, 
        exec_count, exec_time, fetch_count, fetch_time
      )
      VALUES
      (
        p_cur_target_guid, 
        v_rollup_rec.vhost(i), 
        v_rollup_rec.app_id(i), 
        v_rollup_rec.uri(i),
        v_rollup_rec.key_guid(i),
        v_rollup_rec.rollup_time(i), 
        v_rollup_rec.exec_count(i),
        v_rollup_rec.exec_time(i),
        v_rollup_rec.fetch_count(i),
        v_rollup_rec.fetch_time(i)
      );

    COMMIT;

    -- Free temporary tables
    v_rollup_rec.vhost.DELETE;
    v_rollup_rec.app_id.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.key_guid.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.exec_count.DELETE;
    v_rollup_rec.exec_time.DELETE;
    v_rollup_rec.fetch_count.DELETE;
    v_rollup_rec.fetch_time.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_sql_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;

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.vhost.DELETE;
    v_rollup_rec.app_id.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.key_guid.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.exec_count.DELETE;
    v_rollup_rec.exec_time.DELETE;
    v_rollup_rec.fetch_count.DELETE;
    v_rollup_rec.fetch_time.DELETE;

    -- Cleanup all partial work.  If the cleanup fails, update the rollup
    -- timestamp so that the rollup doesn't get stuck forever.
    IF (EMD_SQL_1DAY_CLEANUP = TRUE) THEN
      MGMT_LOG.LOG_ERROR('E2EROLLUP', 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('E2EROLLUP', 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_sql_1day_idx);
        COMMIT;
        MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
          '1day rollups for the period [' ||
          TO_CHAR(p_cur_rollup_timestamps(p_sql_1day_idx),
          'MM:DD:YYYY HH24:MI') || ', ' ||
          TO_CHAR(p_cur_day_rollup, 'MM:DD:YYYY HH24:MI') || ']
          cannot be performed (target=' || p_cur_target_guid || ')');
      EXCEPTION
        WHEN OTHERS THEN
          MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
            'Fatal Error: Unable to set the timestamp (target=' ||
            p_cur_target_guid || '). Error: ' || SUBSTR(SQLERRM, 1, 1000));
          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.vhost.DELETE;
    v_rollup_rec.app_id.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.key_guid.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.exec_count.DELETE;
    v_rollup_rec.exec_time.DELETE;
    v_rollup_rec.fetch_count.DELETE;
    v_rollup_rec.fetch_time.DELETE;

    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'The following unrecoverable error occurred while performing the' ||
      ' 1day rollup (target=' || p_cur_target_guid || ').  Attempting to' ||
      ' skip over rollup period [' ||
      TO_CHAR(p_cur_rollup_timestamps(p_sql_1day_idx),
      'MM:DD:YYYY HH24:MI') || ', ' ||
      TO_CHAR(p_cur_day_rollup,
      'MM:DD:YYYY HH24:MI') || '].  Error: ' || SUBSTR(SQLERRM, 1, 1000));

    BEGIN
      UPDATE MGMT_TARGET_ROLLUP_TIMES
        SET rollup_timestamp = p_cur_day_rollup
        WHERE rowid = p_cur_rollup_rowids(p_sql_1day_idx);
      COMMIT;
      MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
        'Successfully skipped over period.  1day rollup data will not be' ||
        ' generated for this period (target=' || p_cur_target_guid || ').');
    EXCEPTION
      WHEN OTHERS THEN
        MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
          'Fatal Error: Unable to set the 1day rollup timestamp (target=' ||
          p_cur_target_guid || '). Error: ' || SUBSTR(SQLERRM, 1, 1000));
        RAISE;
    END;

END EMD_SQL_1DAY_ROLLUP;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_JDBC_1DAY_ROLLUP IS

  v_rollup_rec      p_jdbc_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 vhost, app_id, uri, 
           TRUNC(rollup_timestamp, 'DD'), key_guid,
           SUM(used_conn_count), SUM(conn_cache_hit),
           SUM(conn_cache_miss), SUM(stmt_create_time),
           SUM(stmt_cache_hit), SUM(stmt_cache_miss)
    FROM MGMT_E2E_JDBC_1HOUR
    WHERE target_guid = c_target_guid
      AND TRUNC(rollup_timestamp, 'DD') > c_prev_rollup
      AND TRUNC(rollup_timestamp, 'DD') <= c_cur_rollup
    GROUP BY vhost, app_id, uri, 
          TRUNC(rollup_timestamp, 'DD'), key_guid;

BEGIN

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

    -- Process batchsize number of rows per commit.
    FETCH rollup_cursor BULK COLLECT INTO v_rollup_rec.vhost,
                                          v_rollup_rec.app_id,
                                          v_rollup_rec.uri,
                                          v_rollup_rec.rollup_time,
                                          v_rollup_rec.key_guid,
                                          v_rollup_rec.used_conn_count,
                                          v_rollup_rec.conn_cache_hit,
                                          v_rollup_rec.conn_cache_miss,
                                          v_rollup_rec.stmt_create_time,
                                          v_rollup_rec.stmt_cache_hit,
                                          v_rollup_rec.stmt_cache_miss
                                          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.uri.COUNT;

    -- Bulk inserts
    FORALL i IN v_rollup_rec.rollup_time.FIRST..v_rollup_rec.rollup_time.LAST
      INSERT INTO MGMT_E2E_JDBC_1DAY
      (
        target_guid, vhost, app_id, uri, key_guid, rollup_timestamp, 
        used_conn_count, conn_cache_hit, conn_cache_miss, stmt_create_time,
        stmt_cache_hit, stmt_cache_miss
      )
      VALUES
      (
        p_cur_target_guid, 
        v_rollup_rec.vhost(i), 
        v_rollup_rec.app_id(i), 
        v_rollup_rec.uri(i),
        v_rollup_rec.key_guid(i),
        v_rollup_rec.rollup_time(i), 
        v_rollup_rec.used_conn_count(i),
        v_rollup_rec.conn_cache_hit(i),
        v_rollup_rec.conn_cache_miss(i),
        v_rollup_rec.stmt_create_time(i),
        v_rollup_rec.stmt_cache_hit(i),
        v_rollup_rec.stmt_cache_miss(i)
      );

    COMMIT;

    -- Free temporary tables
    v_rollup_rec.vhost.DELETE;
    v_rollup_rec.app_id.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.key_guid.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.used_conn_count.DELETE;
    v_rollup_rec.conn_cache_hit.DELETE;
    v_rollup_rec.conn_cache_miss.DELETE;
    v_rollup_rec.stmt_create_time.DELETE;
    v_rollup_rec.stmt_cache_hit.DELETE;
    v_rollup_rec.stmt_cache_miss.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_jdbc_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;

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.vhost.DELETE;
    v_rollup_rec.app_id.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.key_guid.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.used_conn_count.DELETE;
    v_rollup_rec.conn_cache_hit.DELETE;
    v_rollup_rec.conn_cache_miss.DELETE;
    v_rollup_rec.stmt_create_time.DELETE;
    v_rollup_rec.stmt_cache_hit.DELETE;
    v_rollup_rec.stmt_cache_miss.DELETE;

    -- Cleanup all partial work.  If the cleanup fails, update the rollup
    -- timestamp so that the rollup doesn't get stuck forever.
    IF (EMD_JDBC_1DAY_CLEANUP = TRUE) THEN
      MGMT_LOG.LOG_ERROR('E2EROLLUP', 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('E2EROLLUP', 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_jdbc_1day_idx);
        COMMIT;
        MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
          '1day rollups for the period [' ||
          TO_CHAR(p_cur_rollup_timestamps(p_jdbc_1day_idx),
          'MM:DD:YYYY HH24:MI') || ', ' ||
          TO_CHAR(p_cur_day_rollup, 'MM:DD:YYYY HH24:MI') || ']
          cannot be performed (target=' || p_cur_target_guid || ')');
      EXCEPTION
        WHEN OTHERS THEN
          MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
            'Fatal Error: Unable to set the timestamp (target=' ||
            p_cur_target_guid || '). Error: ' || SUBSTR(SQLERRM, 1, 1000));
          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.vhost.DELETE;
    v_rollup_rec.app_id.DELETE;
    v_rollup_rec.uri.DELETE;
    v_rollup_rec.key_guid.DELETE;
    v_rollup_rec.rollup_time.DELETE; 
    v_rollup_rec.used_conn_count.DELETE;
    v_rollup_rec.conn_cache_hit.DELETE;
    v_rollup_rec.conn_cache_miss.DELETE;
    v_rollup_rec.stmt_create_time.DELETE;
    v_rollup_rec.stmt_cache_hit.DELETE;
    v_rollup_rec.stmt_cache_miss.DELETE;

    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'The following unrecoverable error occurred while performing the' ||
      ' 1day rollup (target=' || p_cur_target_guid || ').  Attempting to' ||
      ' skip over rollup period [' ||
      TO_CHAR(p_cur_rollup_timestamps(p_jdbc_1day_idx),
      'MM:DD:YYYY HH24:MI') || ', ' ||
      TO_CHAR(p_cur_day_rollup,
      'MM:DD:YYYY HH24:MI') || '].  Error: ' || SUBSTR(SQLERRM, 1, 1000));

    BEGIN
      UPDATE MGMT_TARGET_ROLLUP_TIMES
        SET rollup_timestamp = p_cur_day_rollup
        WHERE rowid = p_cur_rollup_rowids(p_jdbc_1day_idx);
      COMMIT;
      MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
        'Successfully skipped over period.  1day rollup data will not be' ||
        ' generated for this period (target=' || p_cur_target_guid || ').');
    EXCEPTION
      WHEN OTHERS THEN
        MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
          'Fatal Error: Unable to set the 1day rollup timestamp (target=' ||
          p_cur_target_guid || '). Error: ' || SUBSTR(SQLERRM, 1, 1000));
        RAISE;
    END;

END EMD_JDBC_1DAY_ROLLUP;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_SUMMARY_1DAY_PURGE IS

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

  CURSOR purge_cursor ( c_target_guid RAW,
                        c_max_purge_time DATE ) IS
    SELECT rowid
      FROM MGMT_E2E_SUMMARY_1DAY
      WHERE target_guid = c_target_guid
        AND rollup_timestamp < c_max_purge_time;

BEGIN

  OPEN purge_cursor( p_cur_target_guid, p_cur_day_purge );
  LOOP

    -- Process batchsize number of rows per commit.
    FETCH purge_cursor BULK COLLECT INTO v_purge_rowid 
      LIMIT p_purge_batchsize;

    -- Exit when nothing to purge
    EXIT WHEN v_purge_rowid.COUNT <= 0;
 
    -- Bulk delete by rowid
    FORALL i IN v_purge_rowid.FIRST..v_purge_rowid.LAST
      DELETE FROM MGMT_E2E_SUMMARY_1DAY
        WHERE rowid = v_purge_rowid(i);

    COMMIT;

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

    -- Clear the rowid list
    v_purge_rowid.DELETE;  

  END LOOP;

  IF purge_cursor%ISOPEN THEN
    CLOSE purge_cursor;
  END IF;

  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;
  
EXCEPTION

  WHEN OTHERS THEN
    ROLLBACK;
    IF purge_cursor%ISOPEN THEN
      CLOSE purge_cursor;
    END IF;
    v_purge_rowid.DELETE;
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Error during 1day purge: ' || SUBSTR(SQLERRM, 1, 1000));

END EMD_SUMMARY_1DAY_PURGE;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_DETAILS_1DAY_PURGE IS

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

  CURSOR purge_cursor ( c_target_guid RAW,
                        c_max_purge_time DATE ) IS
    SELECT rowid
      FROM MGMT_E2E_DETAILS_1DAY
      WHERE target_guid = c_target_guid
        AND rollup_timestamp < c_max_purge_time;

BEGIN

  OPEN purge_cursor( p_cur_target_guid, p_cur_day_purge );
  LOOP

    -- Process batchsize number of rows per commit.
    FETCH purge_cursor BULK COLLECT INTO v_purge_rowid 
      LIMIT p_purge_batchsize;

    -- Exit when nothing to purge
    EXIT WHEN v_purge_rowid.COUNT <= 0;
 
    -- Bulk delete by rowid
    FORALL i IN v_purge_rowid.FIRST..v_purge_rowid.LAST
      DELETE FROM MGMT_E2E_DETAILS_1DAY
        WHERE rowid = v_purge_rowid(i);

    COMMIT;

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

    -- Clear the rowid list
    v_purge_rowid.DELETE;  

  END LOOP;

  IF purge_cursor%ISOPEN THEN
    CLOSE purge_cursor;
  END IF;

  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;
  
EXCEPTION

  WHEN OTHERS THEN
    ROLLBACK;
    IF purge_cursor%ISOPEN THEN
      CLOSE purge_cursor;
    END IF;
    v_purge_rowid.DELETE;
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Error during 1day purge: ' || SUBSTR(SQLERRM, 1, 1000));

END EMD_DETAILS_1DAY_PURGE;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_SQL_1DAY_PURGE IS

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

  CURSOR purge_cursor ( c_target_guid RAW,
                        c_max_purge_time DATE ) IS
    SELECT rowid
      FROM MGMT_E2E_SQL_1DAY
      WHERE target_guid = c_target_guid
        AND rollup_timestamp < c_max_purge_time;

BEGIN

  OPEN purge_cursor( p_cur_target_guid, p_cur_day_purge );
  LOOP

    -- Process batchsize number of rows per commit.
    FETCH purge_cursor BULK COLLECT INTO v_purge_rowid 
      LIMIT p_purge_batchsize;

    -- Exit when nothing to purge
    EXIT WHEN v_purge_rowid.COUNT <= 0;
 
    -- Bulk delete by rowid
    FORALL i IN v_purge_rowid.FIRST..v_purge_rowid.LAST
      DELETE FROM MGMT_E2E_SQL_1DAY
        WHERE rowid = v_purge_rowid(i);

    COMMIT;

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

    -- Clear the rowid list
    v_purge_rowid.DELETE;  

  END LOOP;

  IF purge_cursor%ISOPEN THEN
    CLOSE purge_cursor;
  END IF;

  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;
  
EXCEPTION

  WHEN OTHERS THEN
    ROLLBACK;
    IF purge_cursor%ISOPEN THEN
      CLOSE purge_cursor;
    END IF;
    v_purge_rowid.DELETE;
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Error during 1day purge: ' || SUBSTR(SQLERRM, 1, 1000));

END EMD_SQL_1DAY_PURGE;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_JDBC_1DAY_PURGE IS

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

  CURSOR purge_cursor ( c_target_guid RAW,
                        c_max_purge_time DATE ) IS
    SELECT rowid
      FROM MGMT_E2E_JDBC_1DAY
      WHERE target_guid = c_target_guid
        AND rollup_timestamp < c_max_purge_time;

BEGIN

  OPEN purge_cursor( p_cur_target_guid, p_cur_day_purge );
  LOOP

    -- Process batchsize number of rows per commit.
    FETCH purge_cursor BULK COLLECT INTO v_purge_rowid 
      LIMIT p_purge_batchsize;

    -- Exit when nothing to purge
    EXIT WHEN v_purge_rowid.COUNT <= 0;
 
    -- Bulk delete by rowid
    FORALL i IN v_purge_rowid.FIRST..v_purge_rowid.LAST
      DELETE FROM MGMT_E2E_JDBC_1DAY
        WHERE rowid = v_purge_rowid(i);

    COMMIT;

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

    -- Clear the rowid list
    v_purge_rowid.DELETE;  

  END LOOP;

  IF purge_cursor%ISOPEN THEN
    CLOSE purge_cursor;
  END IF;

  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;
  
EXCEPTION

  WHEN OTHERS THEN
    ROLLBACK;
    IF purge_cursor%ISOPEN THEN
      CLOSE purge_cursor;
    END IF;
    v_purge_rowid.DELETE;
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Error during 1day purge: ' || SUBSTR(SQLERRM, 1, 1000));

END EMD_JDBC_1DAY_PURGE;
----------------------------------------------------------------------

----------------------------------------------------------------------
FUNCTION EMD_SUMMARY_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_E2E_SUMMARY_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_summary_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_E2E_SUMMARY_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;
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Error during 1day cleanup: ' || SUBSTR(SQLERRM, 1, 1100));
    RETURN FALSE;

END EMD_SUMMARY_1DAY_CLEANUP;
----------------------------------------------------------------------

----------------------------------------------------------------------
FUNCTION EMD_DETAILS_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_E2E_DETAILS_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_details_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_E2E_DETAILS_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;
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Error during 1day cleanup: ' || SUBSTR(SQLERRM, 1, 1100));
    RETURN FALSE;

END EMD_DETAILS_1DAY_CLEANUP;
----------------------------------------------------------------------

----------------------------------------------------------------------
FUNCTION EMD_SQL_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_E2E_SQL_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_sql_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_E2E_SQL_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;
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Error during 1day cleanup: ' || SUBSTR(SQLERRM, 1, 1100));
    RETURN FALSE;

END EMD_SQL_1DAY_CLEANUP;
----------------------------------------------------------------------

----------------------------------------------------------------------
FUNCTION EMD_JDBC_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_E2E_JDBC_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_jdbc_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_E2E_JDBC_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;
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Error during 1day cleanup: ' || SUBSTR(SQLERRM, 1, 1100));
    RETURN FALSE;

END EMD_JDBC_1DAY_CLEANUP;
----------------------------------------------------------------------

----------------------------------------------------------------------
PROCEDURE EMD_SQL_STMT_CONN_PURGE IS

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

  CURSOR purge_stmt_cursor ( c_target_guid RAW, c_max_purge_time DATE ) IS
    SELECT rowid
      FROM MGMT_E2E_SQL_STMT
      WHERE target_guid = c_target_guid
        AND ttl_ref < c_max_purge_time;

  CURSOR purge_conn_cursor ( c_target_guid RAW, c_max_purge_time DATE ) IS
    SELECT rowid
      FROM MGMT_E2E_SQL_CONN
      WHERE target_guid = c_target_guid
        AND ttl_ref < c_max_purge_time;

BEGIN

  v_purge_date := LEAST(p_cur_day_purge, p_cur_hour_purge);

  -- Purge the STMT table
  OPEN purge_stmt_cursor( p_cur_target_guid, v_purge_date);
  LOOP

    -- Process batchsize number of rows per commit.
    FETCH purge_stmt_cursor BULK COLLECT INTO v_purge_rowid 
      LIMIT p_purge_batchsize;

    -- Exit when nothing to purge
    EXIT WHEN v_purge_rowid.COUNT <= 0;
 
    -- Bulk delete by rowid
    FORALL i IN v_purge_rowid.FIRST..v_purge_rowid.LAST
      DELETE FROM MGMT_E2E_SQL_STMT
        WHERE rowid = v_purge_rowid(i);

    COMMIT;

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

    -- Clear the rowid list
    v_purge_rowid.DELETE;  

  END LOOP;

  IF purge_stmt_cursor%ISOPEN THEN
    CLOSE purge_stmt_cursor;
  END IF;

  -- Purge the CONN table
  OPEN purge_conn_cursor( p_cur_target_guid, v_purge_date );
  LOOP

    -- Process batchsize number of rows per commit.
    FETCH purge_conn_cursor BULK COLLECT INTO v_purge_rowid 
      LIMIT p_purge_batchsize;

    -- Exit when nothing to purge
    EXIT WHEN v_purge_rowid.COUNT <= 0;
 
    -- Bulk delete by rowid
    FORALL i IN v_purge_rowid.FIRST..v_purge_rowid.LAST
      DELETE FROM MGMT_E2E_SQL_CONN
        WHERE rowid = v_purge_rowid(i);

    COMMIT;

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

    -- Clear the rowid list
    v_purge_rowid.DELETE;  

  END LOOP;

  IF purge_conn_cursor%ISOPEN THEN
    CLOSE purge_conn_cursor;
  END IF;
  
EXCEPTION

  WHEN OTHERS THEN
    ROLLBACK;
    IF purge_stmt_cursor%ISOPEN THEN
      CLOSE purge_stmt_cursor;
    END IF;
    IF purge_conn_cursor%ISOPEN THEN
      CLOSE purge_conn_cursor;
    END IF;
    v_purge_rowid.DELETE;
    MGMT_LOG.LOG_ERROR('E2EROLLUP', null,
      'Error during stmt conn purge: ' || SUBSTR(SQLERRM, 1, 1000));

END EMD_SQL_STMT_CONN_PURGE;
----------------------------------------------------------------------

END EMD_E2E_ROLLUP;
/
show errors;