Rem
Rem $Header: gensvc_updbcncol_pkgbody.sql 19-aug-2005.15:44:03 yxie Exp $
Rem
Rem gensvc_updbcncol_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      gensvc_updbcncol_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    yxie        08/12/05 - fix bug 4535347 
Rem    rmarripa    07/27/05 - add key values to the job 
Rem    andyao      07/14/05 - fix bug 4494243 
Rem    rmarripa    07/12/05 - delete pending jobs during sync job 
Rem    mfidanbo    05/10/05 - add test_type to updbcn_tests 
Rem    mfidanbo    02/23/05 - dont send sync if there is no active tests 
Rem    mfidanbo    01/28/05 - debug,.
Rem    mfidanbo    01/21/05 - select beacon itself if passed as a target 
Rem    mfidanbo    12/16/04 - add job switch variable to updateBcn job 
Rem    snakai      09/30/04 - add support for multiple beacons 
Rem    snakai      09/24/04 - snakai_svc_template_1
Rem    snakai      09/21/04 - Created
Rem


CREATE OR REPLACE PACKAGE BODY MGMT_GENSVC_UPDBCN AS


/*
  CREATE_UPDBCN_TEMPLATE
*/
FUNCTION CREATE_UPDBCN_TEMPLATE ( p_target_guid IN RAW,
                                  p_beacon_guid IN RAW,
                                  p_test_list IN MGMT_GENSVC_GUID_ARRAY )
RETURN RAW
IS

  l_template_guid MGMT_TEMPLATES.template_guid%TYPE;

BEGIN

  RETURN NULL;

END CREATE_UPDBCN_TEMPLATE;


/*
  UPDATE_TESTS
*/
PROCEDURE UPDATE_TESTS   ( p_target_name   IN VARCHAR2, 
                           p_target_type   IN VARCHAR2,
                           p_tests         IN MGMT_GENSVC_UBJOB_TEST_ARRAY,
                           p_incl_rmv_bcns IN BOOLEAN,
                           p_key_values    IN SMP_EMD_STRING_ARRAY,
                           p_action_desc   IN VARCHAR2,
                           p_jobs          OUT MGMT_GENSVC_UBJOB_JOB_ARRAY )
IS

  l_target_guid   MGMT_TARGETS.target_guid%TYPE;
  l_beacon_guid   MGMT_TARGETS.target_guid%TYPE;
  l_beacon_name   MGMT_TARGETS.target_name%TYPE;
  l_job_id        MGMT_JOB.job_id%TYPE;

  CURSOR l_beacon_cursor IS
    SELECT t.target_guid, t.target_name
      FROM MGMT_BCN_TARGET b, MGMT_TARGETS t
     WHERE b.target_guid = l_target_guid
       AND b.beacon_target_guid = t.target_guid
       AND b.beacon_target_guid <> l_target_guid
       AND b.is_removing = 'N';

  CURSOR l_beacon_incl_rmv_cursor IS
    SELECT t.target_guid, t.target_name
      FROM MGMT_BCN_TARGET b, MGMT_TARGETS t
     WHERE b.target_guid = l_target_guid
       AND b.beacon_target_guid = t.target_guid
       AND b.beacon_target_guid <> l_target_guid;

  l_beacon       l_beacon_cursor%ROWTYPE;
  l_count        PLS_INTEGER;
  l_more         BOOLEAN;

BEGIN

  -- validate parameters
  IF (p_target_name IS NULL) OR (p_target_type IS NULL) OR
     (p_tests IS NULL) OR (p_tests.COUNT <= 0) OR 
     (p_action_desc IS NULL) THEN
    raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
      'MGMT_GENSVC_UPDBCN.UPDATE_TEST: Invalid Parameters');  
  END IF;

  -- get the target guid
  SELECT target_guid
    INTO l_target_guid
    FROM MGMT_TARGETS
   WHERE target_name = p_target_name
     AND target_type = p_target_type;

  IF (p_target_type = MGMT_GLOBAL.G_BEACON_TARGET_TYPE ) THEN
    l_job_id := SUBMIT_UPDBCN_JOB ( l_target_guid,
                                    l_target_guid,
                                    p_target_name,
                                    p_tests,
                                    update_job,
                                    p_key_values,
                                    p_action_desc);
    -- add the job to the result array
    p_jobs := MGMT_GENSVC_UBJOB_JOB_ARRAY();
    p_jobs.EXTEND();
    p_jobs(1) := MGMT_GENSVC_UBJOB_JOB(p_target_name, l_job_id);
  ELSE
    -- get the beacons for the webapp
    IF p_incl_rmv_bcns THEN
      OPEN l_beacon_incl_rmv_cursor;
    ELSE
      OPEN l_beacon_cursor;
    END IF;
    l_count := 0;
    l_more := TRUE;
    WHILE l_more LOOP
      IF p_incl_rmv_bcns THEN
        FETCH l_beacon_incl_rmv_cursor INTO l_beacon;
        l_more := l_beacon_incl_rmv_cursor%FOUND;
      ELSE
        FETCH l_beacon_cursor INTO l_beacon;
        l_more := l_beacon_cursor%FOUND;
      END IF;
      IF l_more THEN
        -- submit the job
        l_job_id := SUBMIT_UPDBCN_JOB ( l_target_guid,
                                        l_beacon.target_guid,
                                        l_beacon.target_name,
                                        p_tests,
                                        update_job,
                                        p_key_values,
                                        p_action_desc);
        -- add the job to the result array
        IF l_count = 0 THEN
          p_jobs := MGMT_GENSVC_UBJOB_JOB_ARRAY();
        END IF;
        p_jobs.EXTEND();
        l_count := l_count + 1;
        p_jobs(l_count) := MGMT_GENSVC_UBJOB_JOB(l_beacon.target_name, l_job_id);
      END IF;
    END LOOP;
    IF p_incl_rmv_bcns THEN
      CLOSE l_beacon_incl_rmv_cursor;
    ELSE
      CLOSE l_beacon_cursor;
    END IF;
  END IF;
  
EXCEPTION
  WHEN OTHERS THEN
    IF l_beacon_incl_rmv_cursor%ISOPEN THEN
      CLOSE l_beacon_incl_rmv_cursor;
    END IF;
    IF l_beacon_cursor%ISOPEN THEN
      CLOSE l_beacon_cursor;
    END IF;
  
END UPDATE_TESTS;


PROCEDURE SUBMIT_SYNC_JOB (   p_target_name   IN VARCHAR2, 
                              p_target_type   IN VARCHAR2,
                              p_beacon_name   IN VARCHAR2,
                              p_action_desc   IN VARCHAR2,
                              p_job_id        OUT RAW )
IS

  l_target_guid   MGMT_TARGETS.target_guid%TYPE;
  l_beacon_guid   MGMT_TARGETS.target_guid%TYPE;
  l_tests         MGMT_GENSVC_UBJOB_TEST_ARRAY;
  l_count         PLS_INTEGER;
  l_more          BOOLEAN;

  CURSOR l_monitoring_tests_cursor IS
    SELECT name, txn_type, txn_guid,
           DECODE(is_representative, 'Y', 1, 0) is_avail
      FROM MGMT_BCN_TXN_DEFN
     WHERE target_guid = l_target_guid
       AND state = 'M';

  l_mon_test l_monitoring_tests_cursor%ROWTYPE;
BEGIN


  -- validate parameters
  IF (p_target_name IS NULL) OR (p_target_type IS NULL) OR
     (p_beacon_name IS NULL) OR
     (p_action_desc IS NULL) THEN
    raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
      'MGMT_GENSVC_UPDBCN.SUBMIT_UPDBCN_JOB: Invalid Parameters');  
  END IF;

  -- get the target guid
  SELECT target_guid
    INTO l_target_guid
    FROM MGMT_TARGETS
   WHERE target_name = p_target_name
     AND target_type = p_target_type;

  -- get the beacon information
  SELECT target_guid
    INTO l_beacon_guid
    FROM MGMT_TARGETS
   WHERE target_name = p_beacon_name
     AND target_type = EMD_BCNTXN.p_beacon_type;
 
  -- since we are sync'ing we need to push all tests to the beacon
  l_more := TRUE;
  l_count := 0;
  OPEN l_monitoring_tests_cursor;
  WHILE l_more LOOP
    FETCH l_monitoring_tests_cursor INTO l_mon_test;
    l_more := l_monitoring_tests_cursor%FOUND;
    IF l_more THEN
      IF l_count = 0 THEN
        l_tests := MGMT_GENSVC_UBJOB_TEST_ARRAY();
      END IF;
      l_count := l_count + 1;
      l_tests.EXTEND(1);
      l_tests(l_count) := MGMT_GENSVC_UBJOB_TEST(l_mon_test.name,
                                                 l_mon_test.txn_type,
                                                 l_mon_test.txn_guid,
                                                 l_mon_test.is_avail,
                                                 1); -- add by default
    END IF;
  END LOOP;
  CLOSE l_monitoring_tests_cursor;

  IF ( l_count = 0 ) THEN
    -- submit the job to delete all collections associated
    -- with this target
    p_job_id := SUBMIT_UPDBCN_JOB ( l_target_guid,
                                    l_beacon_guid,
                                    p_beacon_name,
                                    l_tests,
                                    DELETE_JOB,
                                    null,
                                    p_action_desc);
  ELSE
    -- submit the job
    p_job_id := SUBMIT_UPDBCN_JOB ( l_target_guid,
                                    l_beacon_guid,
                                    p_beacon_name,
                                    l_tests,
                                    SYNC_JOB,
                                    null,
                                    p_action_desc );
  END IF;

END SUBMIT_SYNC_JOB;


/* 
  SUBMIT_UPDBCN_JOB
*/
PROCEDURE SUBMIT_UPDBCN_JOB ( p_target_name   IN VARCHAR2, 
                              p_target_type   IN VARCHAR2,
                              p_beacon_name   IN VARCHAR2,
                              p_tests         IN MGMT_GENSVC_UBJOB_TEST_ARRAY,
                              p_key_values    IN SMP_EMD_STRING_ARRAY,
                              p_action_desc   IN VARCHAR2,
                              p_job_id        OUT RAW ) 
IS

  l_target_guid   MGMT_TARGETS.target_guid%TYPE;
  l_beacon_guid   MGMT_TARGETS.target_guid%TYPE;
  

BEGIN

  -- validate parameters
  IF (p_target_name IS NULL) OR (p_target_type IS NULL) OR
     (p_beacon_name IS NULL) OR
     (p_tests IS NULL) OR (p_tests.COUNT <= 0) OR 
     (p_action_desc IS NULL) THEN
    raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
      'MGMT_GENSVC_UPDBCN.SUBMIT_UPDBCN_JOB: Invalid Parameters');  
  END IF;

  -- get the target guid
  SELECT target_guid
    INTO l_target_guid
    FROM MGMT_TARGETS
   WHERE target_name = p_target_name
     AND target_type = p_target_type;

  -- get the beacon information
  SELECT target_guid
    INTO l_beacon_guid
    FROM MGMT_TARGETS
   WHERE target_name = p_beacon_name
     AND target_type = EMD_BCNTXN.p_beacon_type;
 


  -- submit the job
  p_job_id := SUBMIT_UPDBCN_JOB ( l_target_guid,
                                  l_beacon_guid,
                                  p_beacon_name,
                                  p_tests,
                                  update_job,
                                  p_key_values,
                                  p_action_desc);
END SUBMIT_UPDBCN_JOB;

PROCEDURE UPDATE_PENDING_JOB_FOR_SYNC ( p_target_guid   IN RAW,
                                        p_queue_name    IN VARCHAR2 )
IS 
BEGIN
  IF ( p_target_guid IS NULL ) OR ( p_queue_name IS NULL ) THEN 
    RETURN;
  END IF;

   -- Update the status in update operations table 
  UPDATE MGMT_GENSVC_JOBS_DETAILS 
     SET execution_status = STATUS_FAILED,  
         last_updated_timestamp = SYSDATE,
         error_message = decode (error_message, NULL, 'This action was aborted. Sync Beacon action will apply this change.', error_message || ' -- This action was aborted. Sync Beacon action will apply this change.' )
   WHERE target_guid = p_target_guid
     AND operation_guid IN 
             (SELECT gjd.operation_guid
                FROM MGMT_GENSVC_JOBS_DETAILS gjd, mgmt_job_exec_summary js,
                           mgmt_job_queues jq
               WHERE jq.queue_name = p_queue_name
                 AND jq.queue_id = js.queue_id
                 AND js.job_id = gjd.job_id);

    -- Cleanup the jobs from the queue
    MGMT_JOB_ENGINE.cleanup_job_queue(p_queue_name, true);
END UPDATE_PENDING_JOB_FOR_SYNC;

/* 
  SUBMIT_UPDBCN_JOB
*/
FUNCTION SUBMIT_UPDBCN_JOB ( p_target_guid   IN RAW,
                             p_beacon_guid   IN RAW,
                             p_beacon_name   IN VARCHAR2,
                             p_tests         IN MGMT_GENSVC_UBJOB_TEST_ARRAY,
                             p_jobtype       IN INTEGER,
                             p_key_values    IN SMP_EMD_STRING_ARRAY,
                             p_action_desc   IN VARCHAR2)
RETURN RAW
IS

  l_current_user       VARCHAR2(256) := mgmt_user.get_current_em_user();
  l_target_name        MGMT_TARGETS.target_name%TYPE;
  l_target_type        MGMT_TARGETS.target_type%TYPE;  
  l_template_guid      MGMT_TEMPLATES.template_guid%TYPE;
  l_add_tests          MGMT_GENSVC_GUID_ARRAY;
  l_add_test_count     NUMBER;
  l_test_guid          MGMT_BCN_TXN_DEFN.txn_guid%TYPE;
  l_operation_guid     RAW(16);
  l_job_targets        MGMT_JOB_TARGET_LIST;
  l_job_target_list    MGMT_JOB_TARGET_LIST_ARRAY;
  l_job_params         MGMT_JOB_PARAM_LIST;
  l_job_name           MGMT_JOB.job_name%TYPE;
  l_job_id             MGMT_JOB.job_id%TYPE;
  l_execution_ids      MGMT_JOB_GUID_ARRAY;
  l_queue_names        SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
  l_schedule           MGMT_JOB_SCHEDULE_RECORD;
  l_count              NUMBER;
  l_key_values         MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();

BEGIN

  -- Check parameters
  IF (p_target_guid IS NULL) OR (p_beacon_guid IS NULL) 
     OR (p_action_desc IS NULL) THEN
    raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
      'MGMT_GENSVC_UPDBCN.SUBMIT_UPDBCN_JOB: Invalid Parameters');  
  END IF;

  -- Check User Privs
  IF (mgmt_user.has_priv(l_current_user, MGMT_USER.OPERATOR_TARGET,
                         p_target_guid) != MGMT_USER.USER_HAS_PRIV) OR
     ((mgmt_user.has_priv(l_current_user, MGMT_USER.USE_ANY_BEACON) 
	                  != MGMT_USER.USER_HAS_PRIV) AND
      (mgmt_user.has_priv(l_current_user, MGMT_USER.OPERATOR_TARGET,
	                  p_beacon_guid)!= MGMT_USER.USER_HAS_PRIV))

  THEN
    raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR,
      'MGMT_GENSVC_UPDBCN.SUBMIT_UPDBCN_JOB: The current user (' 
      || l_current_user || 
      ') does not have sufficient privileges to perform this operation');
  END IF;

  -- get the target name and target type
  SELECT target_name, target_type
    INTO l_target_name, l_target_type
    FROM MGMT_TARGETS
   WHERE target_guid = p_target_guid;

  -- get the tests to add
  l_add_tests := MGMT_GENSVC_GUID_ARRAY();
  l_add_test_count := 0;
  IF (p_tests IS NOT NULL) AND (p_tests.COUNT > 0) THEN
    FOR l_count IN 1..p_tests.COUNT LOOP
      -- add it to the ADD list
      IF p_tests(l_count).add_test <> 0 THEN
        l_add_test_count := l_add_test_count + 1;
        l_add_tests.EXTEND();
        l_add_tests(l_add_test_count) := HEXTORAW(p_tests(l_count).test_guid);
      END IF;
    END LOOP;
  END IF;

  -- Create the temporary template
  IF (l_add_tests IS NOT NULL) AND (l_add_tests.COUNT > 0) THEN
    l_template_guid := CREATE_UPDBCN_TEMPLATE( p_target_guid, 
                                               p_beacon_guid,
                                               l_add_tests );
  ELSE
    l_template_guid := NULL;
  END IF;
  
  -- create key vlaues 
  IF ( p_key_values IS NOT NULL ) AND ( p_key_values.COUNT > 0 ) THEN 
    l_key_values.extend(p_key_values.COUNT);

    FOR i in p_key_values.FIRST .. p_key_values.LAST LOOP
      l_key_values(i) := p_key_values(i);
    END LOOP;
  END IF;

  -- Create the job parameters
  l_job_targets := MGMT_JOB_TARGET_LIST();
  l_job_targets.EXTEND(1);
  l_job_targets(1) := MGMT_JOB_TARGET_RECORD(p_beacon_name, 
                                             EMD_BCNTXN.p_beacon_type);
  l_job_target_list := MGMT_JOB_TARGET_LIST_ARRAY();
  l_job_target_list.EXTEND(1);
  l_job_target_list(1) := l_job_targets;

  l_operation_guid := SYS_GUID();
  l_job_name := JOB_NAME_PREFIX || l_operation_guid;
  
  l_job_params := MGMT_JOB_PARAM_LIST();
  l_job_params.extend(5);
  l_job_params(1) := MGMT_JOB_PARAM_RECORD('operation_guid', MGMT_JOBS.PARAM_TYPE_SCALAR, 
                                           l_operation_guid, null);
  l_job_params(2) := MGMT_JOB_PARAM_RECORD('target_name', MGMT_JOBS.PARAM_TYPE_SCALAR, 
                                           l_target_name, null);
  l_job_params(3) := MGMT_JOB_PARAM_RECORD('target_type', MGMT_JOBS.PARAM_TYPE_SCALAR, 
                                           l_target_type, null);
  l_job_params(4) := MGMT_JOB_PARAM_RECORD('key_values', MGMT_JOBS.PARAM_TYPE_VECTOR,
                                           null, l_key_values);

  IF (p_jobtype = UPDATE_JOB) THEN
    l_job_params(5) := MGMT_JOB_PARAM_RECORD('typeOfBeaconJob', MGMT_JOBS.PARAM_TYPE_SCALAR, 
                                            'updateJob', null);
  ELSIF (p_jobtype = SYNC_JOB) THEN
    l_job_params(5) := MGMT_JOB_PARAM_RECORD('typeOfBeaconJob', MGMT_JOBS.PARAM_TYPE_SCALAR, 
                                            'syncJob', null);
    l_job_params.extend(3);
    l_job_params(6) := MGMT_JOB_PARAM_RECORD('beacon_name', MGMT_JOBS.PARAM_TYPE_SCALAR, 
                                            p_beacon_name, null);
    l_job_params(7) := MGMT_JOB_PARAM_RECORD('beacon_type', MGMT_JOBS.PARAM_TYPE_SCALAR, 
                                            MGMT_GLOBAL.G_BEACON_TARGET_TYPE, null);
    l_job_params(8) := MGMT_JOB_PARAM_RECORD('beacon_guid', MGMT_JOBS.PARAM_TYPE_SCALAR, 
                                            p_beacon_guid, null);
  ELSIF (p_jobtype = DELETE_JOB) THEN
    l_job_params(5) := MGMT_JOB_PARAM_RECORD('typeOfBeaconJob', MGMT_JOBS.PARAM_TYPE_SCALAR, 
                                            'deleteJob', null);
    l_job_params.extend(3);
    l_job_params(6) := MGMT_JOB_PARAM_RECORD('beacon_name', MGMT_JOBS.PARAM_TYPE_SCALAR, 
                                            p_beacon_name, null);
    l_job_params(7) := MGMT_JOB_PARAM_RECORD('beacon_type', MGMT_JOBS.PARAM_TYPE_SCALAR, 
                                            MGMT_GLOBAL.G_BEACON_TARGET_TYPE, null);
    l_job_params(8) := MGMT_JOB_PARAM_RECORD('beacon_guid', MGMT_JOBS.PARAM_TYPE_SCALAR, 
                                            p_beacon_guid, null);
  END IF;
  
  l_schedule := null;  

  -- Create the queue if not already created
  l_queue_names.extend(1);
  l_queue_names(1) := JOB_NAME_PREFIX ||
                         p_target_guid || ' ' || p_beacon_guid;
  SELECT count(1) INTO l_count
    FROM MGMT_JOB_QUEUES
   WHERE queue_name = l_queue_names(1);

  IF l_count=0 THEN
    MGMT_JOB_ENGINE.create_job_queue(l_queue_names(1));
  ELSIF (p_jobtype = SYNC_JOB) THEN
    -- Clean ip Pending jobs in beacon job queue
    -- and update the history of the jobs as comleted 
    UPDATE_PENDING_JOB_FOR_SYNC( p_target_guid, l_queue_names(1) );
  END IF;

  -- Submit the job
  MGMT_JOBS.submit_job( l_job_name,
                        JOB_DESCRIPTION, 
                        0,
                        JOB_TYPE,
                        l_job_target_list,
                        l_job_params,
                        l_schedule,
                        -1,
                        l_job_id,
                        l_execution_ids,
                        null,
                        MGMT_JOB_ENGINE.SYSTEM_JOB_RETRY,
                        null,
                        null,
                        l_queue_names);

  -- insert data in the job state tables
  INSERT INTO MGMT_GENSVC_UPDBCN_JOB
    ( operation_guid, target_guid, template_guid, 
      beacon_target_guid, beacon_name, action_description )
  VALUES
    ( l_operation_guid, p_target_guid, l_template_guid,
      p_beacon_guid, p_beacon_name, p_action_desc );

  IF (p_tests IS NOT NULL) AND (p_tests.COUNT > 0) THEN
    FOR l_count IN 1..p_tests.COUNT LOOP
      INSERT INTO MGMT_GENSVC_UPDBCN_JOB_TESTS
        ( operation_guid, target_guid, test_guid, test_name, test_type, action )
      VALUES
        ( l_operation_guid, p_target_guid, 
          HEXTORAW(p_tests(l_count).test_guid),
          p_tests(l_count).test_name, p_tests(l_count).test_type, 
          p_tests(l_count).add_test );
    END LOOP;
  END IF;

  INSERT INTO MGMT_GENSVC_JOBS_DETAILS
    ( target_guid, operation_guid, job_id, job_owner, execution_id, execution_status, submission_timestamp )
  VALUES 
    ( p_target_guid, l_operation_guid, l_job_id, l_current_user, l_execution_ids(1), MGMT_JOBS.SCHEDULED_STATUS, SYSDATE );

  -- completed successfully, return the job id
  RETURN l_job_id;

END SUBMIT_UPDBCN_JOB;

PROCEDURE CLEANUP_HISTORY_AFTER_SYNC ( p_operation_guid IN VARCHAR2,
                                       p_target_name  IN VARCHAR2,
                                       p_target_type  IN VARCHAR2,
                                       p_beacon_guid  IN VARCHAR2,
                                       p_beacon_name  IN VARCHAR2,
                                       p_jobtype    IN VARCHAR2)
IS
BEGIN
UPDATE MGMT_GENSVC_JOBS_DETAILS
  SET  execution_status = STATUS_FIXED_BY_SYNC,
       error_message = CONCAT(error_message, ' (Fixed by the Sync Beacon Action)'),
       last_updated_timestamp = SYSDATE
 WHERE operation_guid IN (SELECT gjd.operation_guid 
                            FROM MGMT_GENSVC_JOBS_DETAILS gjd,
                                 MGMT_GENSVC_UPDBCN_JOB uj,
                                 MGMT_TARGETS t
                           WHERE t.target_name = p_target_name
                             AND t.target_type = p_target_type
                             AND t.target_guid = uj.target_guid
                             AND t.target_guid = gjd.target_guid
                             AND gjd.operation_guid = uj.operation_guid
                             AND uj.beacon_target_guid = HEXTORAW(p_beacon_guid)
                             AND (gjd.execution_status != STATUS_FIXED_BY_SYNC AND 
                                  gjd.execution_status != STATUS_COMPLETED AND
                                  gjd.execution_status != STATUS_ABORTED ));
 
  CLEAR_SEV_METRIC_ERRS ( p_operation_guid, p_target_name, p_target_type, 
                        p_jobtype, NULL);
END CLEANUP_HISTORY_AFTER_SYNC;

--This procedures clears open severities and closes metric errors 
-- for transaction/steps/step grops or beacons. 
-- This will be executed as part of beacon update collections job
-- directly or indirectly
-- This procedure will only handle 'updateJob' and 'syncJob' types.
-- 'deleteJob' only executes as part of service deletion. It is not
-- required to delete severities as part of job, if the service is deleted. 
PROCEDURE CLEAR_SEV_METRIC_ERRS ( p_operation_guid IN VARCHAR2,
                        p_target_name    IN VARCHAR2,
                        p_target_type    IN VARCHAR2,
                        p_jobtype    IN VARCHAR2,
                        p_key_values     IN MGMT_JOB_VECTOR_PARAMS ) 
IS
  l_target_guid        MGMT_TARGETS.target_guid%TYPE;
  l_key_values         MGMT_MEDIUM_STRING_TABLE := mgmt_medium_string_table();

  CURSOR open_alerts_cur(tgt_id IN RAW, key_vals IN MGMT_JOB_VECTOR_PARAMS)
  IS
    -- distinct may not be needed, but just to be sure
    SELECT DISTINCT key_value, policy_guid
      FROM mgmt_current_violation 
     WHERE target_guid     = tgt_id
       AND key_value IN ( SELECT * 
                            FROM TABLE (CAST( key_vals AS MGMT_JOB_VECTOR_PARAMS )));
  
  CURSOR l_err_cur(tgt RAW, coll_names SMP_EMD_STRING_ARRAY) IS
    SELECT e.agent_guid, e.metric_guid, e.coll_name,
           e.collection_timestamp, e.metric_error_type
      FROM mgmt_current_metric_errors e
     WHERE e.target_guid = tgt
       AND e.coll_name IN (SELECT * 
                             FROM TABLE (CAST (coll_names AS SMP_EMD_STRING_ARRAY )) );

  l_removed_colls SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
  l_invalid_sev_recs SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY();
  l_tgt_ts     DATE;
  l_ts     DATE;
  l_tgt_tz mgmt_targets.timezone_region%TYPE;
  l_valid_arr  SMP_EMD_STRING_ARRAY ;

BEGIN
  IF ( p_target_name IS NULL ) OR ( p_target_type IS NULL ) OR 
     ( p_operation_guid IS NULL ) OR ( p_jobtype IS NULL ) THEN 
    RETURN;
  END IF;

  BEGIN
    SELECT target_guid, timezone_region 
      INTO l_target_guid, l_tgt_tz
      FROM mgmt_targets 
     WHERE target_name = p_target_name
       AND target_type = p_target_type;
  EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
      RETURN;
  END;

  l_tgt_ts :=  MGMT_GLOBAL.SYSDATE_TZRGN(l_tgt_tz);
 
  IF ( p_jobtype = 'updateJob') THEN 
  -- Delete all severities generated by the beacon being deleted
    IF ( p_key_values IS NULL ) OR ( p_key_values.COUNT = 0 ) THEN 
      RETURN;
    END IF;
    l_key_values.extend;
    -- Get all open alerts and clear them
    FOR sev_rec IN open_alerts_cur(l_target_guid, p_key_values ) LOOP 
      l_key_values(1) := sev_rec.key_value;
      BEGIN
        em_severity.clear_open_alerts(p_target_guid => l_target_guid, 
                              p_policy_guid => sev_rec.policy_guid, 
                              p_key_values => l_key_values,  
                              p_is_metric => TRUE, 
                              p_clear_message => 'Clearing  the severity, since the collection containing the key value '||l_key_values(1) || ' was deleted or modified.' );
      EXCEPTION
        WHEN OTHERS THEN
            MGMT_LOG.LOG_ERROR(EMD_BCNTXN.MODULE_NAME, NULL, SQLERRM);
            MGMT_LOG.LOG_ERROR(EMD_BCNTXN.MODULE_NAME, NULL, 'Unable to clear severity for target' ||l_target_guid||', metric '||sev_rec.policy_guid||'; key '||l_key_values(1) );
      END;
    END LOOP;

    BEGIN 
      SELECT DISTINCT RAWTOHEX(test_guid)|| RAWTOHEX(beacon_target_guid) 
        BULK COLLECT INTO l_removed_colls
        FROM MGMT_GENSVC_UPDBCN_JOB upjb, MGMT_GENSVC_UPDBCN_JOB_TESTS upjbts
       WHERE upjb.target_guid = l_target_guid 
         AND upjb.operation_guid = p_operation_guid
         AND upjb.target_guid = upjbts.target_guid 
         AND upjbts.operation_guid = upjb.operation_guid
         AND upjbts.action = 0;
    EXCEPTION 
      WHEN NO_DATA_FOUND THEN 
        RETURN;
    END;

    -- close metric errors 
    FOR met_err in l_err_cur(l_target_guid, l_removed_colls ) LOOP
      l_ts := GREATEST(l_tgt_ts, met_err.collection_timestamp + 1/1440);
      BEGIN
        INSERT INTO MGMT_METRIC_ERRORS
          ( target_guid, metric_guid, coll_name, agent_guid,
            collection_timestamp, metric_error_message, metric_error_type )
        VALUES
          ( l_target_guid, met_err.metric_guid, met_err.coll_name,
            met_err.agent_guid, l_ts, NULL, met_err.metric_error_type );
      EXCEPTION 
        WHEN OTHERS THEN 
          MGMT_LOG.LOG_ERROR(EMD_BCNTXN.MODULE_NAME, NULL, SQLERRM);
          MGMT_LOG.LOG_ERROR(EMD_BCNTXN.MODULE_NAME, NULL, 'Unable to clear metric error for target' ||l_target_guid||'; metric '||met_err.metric_guid||'; collection name  '||met_err.coll_name||'; agent '||met_err.agent_guid||'; timestamp '||l_ts||' ; error type '||met_err.metric_error_type );
      END;
    END LOOP;
  ELSIF ( p_jobtype = 'syncJob') OR ( p_jobtype = 'deleteJob' ) THEN
    l_key_values.extend;
    -- select critical/warning severities, with invalid key values
    -- a Key value without any associated transaction/beacon is considered as invalid
    FOR sub_rec IN(
            SELECT DISTINCT key_value, policy_guid
              FROM mgmt_current_violation v
             WHERE v.target_guid = l_target_guid
               AND (v.violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL OR v.violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING)
               AND v.policy_guid
                IN (SELECT metric_guid 
                      FROM mgmt_metrics m, mgmt_targets t 
                     WHERE t.target_guid = l_target_guid
                       AND m.target_type = t.target_type 
                       AND t.type_meta_ver = m.type_meta_ver
                       AND (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ')
                       AND (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ')
                       AND (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ')
                       AND (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ')
                       AND (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ') 
                       AND m.remote = 1 ) 
           ) 
    LOOP
      IF( EMD_BCNTXN.IS_KEY_VALID_ASSOC(l_target_guid, sub_rec.key_value) = 'N' )THEN 

        --Close collected invalid severities
        l_key_values(1) := sub_rec.key_value;
        BEGIN
          -- Close severity if the key value is invalid
          em_severity.clear_open_alerts(p_target_guid => l_target_guid, 
                                p_policy_guid => sub_rec.policy_guid, 
                                p_key_values => l_key_values,  
                                p_is_metric => TRUE, 
                                p_clear_message => 'Clearing  the severity, since the collection containing the key value '||l_key_values(1) || ' was deleted or modified.' );
        EXCEPTION 
          WHEN OTHERS THEN 
            MGMT_LOG.LOG_ERROR(EMD_BCNTXN.MODULE_NAME, NULL, SQLERRM);
            MGMT_LOG.LOG_ERROR(EMD_BCNTXN.MODULE_NAME, NULL, 'Unable to clear severity for target' ||l_target_guid||', metric '||sub_rec.key_value||'; key '||l_key_values(1) );
        END;
      END IF;
    END LOOP;
  END IF;

EXCEPTION
  WHEN OTHERS THEN 
    MGMT_LOG.LOG_ERROR(EMD_BCNTXN.MODULE_NAME, NULL, 'Error while clearing severities for target '||p_target_name ||'; '||p_target_type||'; Job Type '||p_jobtype ||'operation guid '||p_operation_guid ||'. Error '||SQLERRM);
    
END CLEAR_SEV_METRIC_ERRS;

END MGMT_GENSVC_UPDBCN;
/
SHOW ERRORS;