Rem
Rem $Header: gensvc_montmpl_pkgbody.sql 26-aug-2005.12:03:38 mvajapey Exp $
Rem
Rem gensvc_montmpl_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      gensvc_montmpl_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    mvajapey    08/26/05 - bug 4552869 - add boolean return parameter to 
Rem                           read_template saying if user has privileges on 
Rem                           all beacons. 
Rem    snakai      07/07/05 - check privs 
Rem    andyao      06/09/05 - use different api to associate beacons with 
Rem                           template 
Rem    mvajapey    04/14/05 - Add procedure to associate beacons with the 
Rem                           template. 
Rem    snakai      04/11/05 - use emd_bcntxn beacon assoc functions
Rem    snakai      02/18/05 - add delete_template api 
Rem    mfidanbo    12/16/04 - fix calls to send_updbcn_job 
Rem    snakai      10/07/04 - snakai_svc_template_2
Rem    snakai      09/30/04 - add apply function 
Rem    snakai      09/28/04 - Created
Rem 

CREATE OR REPLACE PACKAGE BODY MGMT_GENSVC_MONTMPL AS

/*
  READ_TEMPLATE
*/

PROCEDURE READ_TEMPLATE ( p_obj_id IN VARCHAR2, 
                          p_test_list OUT MGMT_GENSVC_OBJID_ARRAY, 
                          p_beacon_list OUT MGMT_GENSVC_TMPL_BCN_ARRAY, 
                          p_variable_list OUT MGMT_GENSVC_TMPL_VAR_ARRAY,
                          p_has_privileges OUT VARCHAR2)
IS

  l_obj_guid RAW(16);
  l_more     BOOLEAN;
  l_count    NUMBER;
  l_rep_owner_beacon_count  NUMBER;

  CURSOR l_test_cursor IS
    SELECT txn_guid
      FROM MGMT_BCN_TXN_DEFN
     WHERE target_guid = l_obj_guid;

  CURSOR l_beacon_cursor IS
    SELECT t.target_guid, t.target_name, b.participates_avail
      FROM MGMT_BCN_TARGET b, MGMT_TARGETS t
     WHERE b.target_guid = l_obj_guid
       AND b.beacon_target_guid = t.target_guid
       AND t.target_type = EMD_BCNTXN.p_beacon_type;

  CURSOR l_var_cursor IS
    SELECT name, default_value
      FROM MGMT_GENSVC_TMPL_VARS
     WHERE template_guid = l_obj_guid;

  l_test     l_test_cursor%ROWTYPE;
  l_beacon   l_beacon_cursor%ROWTYPE;
  l_var      l_var_cursor%ROWTYPE;
  l_avbcn    NUMBER;
  l_decrypt_value MGMT_GENSVC_TMPL_VARS.default_value%TYPE;
  l_obj_name VARCHAR2(1000);
  l_privs_ok BOOLEAN;
 
BEGIN

  -- validate the input params
  IF p_obj_id IS NULL THEN  
    RAISE_APPLICATION_ERROR(mgmt_global.invalid_params_err,
                            'NULL obj_guid found');
  END IF;
  
  l_obj_guid := HEXTORAW(p_obj_id);

  -- validate user has at least view privs, force query on mgmt_templates
  l_privs_ok := FALSE;
  BEGIN
    SELECT template_name
      INTO l_obj_name
      FROM MGMT_TEMPLATES
     WHERE template_guid = l_obj_guid;
    l_privs_ok := TRUE;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      -- either the user does not have privs or it is a target (not a template)
      l_privs_ok := FALSE;
  END;  
  IF NOT l_privs_ok THEN
    BEGIN
      SELECT target_name
        INTO l_obj_name
        FROM MGMT_TARGETS
       WHERE target_guid = l_obj_guid;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        -- no privs, fail
        RAISE_APPLICATION_ERROR(MGMT_GLOBAL.insufficient_privileges_err, 
          'The user does not have sufficient privileges to perform this operation');  
    END;
  END IF;

  -- get the tests
  BEGIN
    l_more := TRUE;
    l_count := 0;
    OPEN l_test_cursor;
    WHILE l_more LOOP
      FETCH l_test_cursor INTO l_test;
      l_more := l_test_cursor%FOUND;
      IF l_more THEN
        IF l_count = 0 THEN
          p_test_list := MGMT_GENSVC_OBJID_ARRAY();
        END IF;
        p_test_list.EXTEND();
        l_count := l_count + 1;
        p_test_list(l_count) := RAWTOHEX(l_test.txn_guid);
      END IF;
    END LOOP;
    CLOSE l_test_cursor;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      -- it may not have any tests
      NULL;
  END;

  -- get the beacons
  BEGIN
    l_more := TRUE;
    l_count := 0;
    OPEN l_beacon_cursor;
    WHILE l_more LOOP
      FETCH l_beacon_cursor INTO l_beacon;
      l_more := l_beacon_cursor%FOUND;
      IF l_more THEN
        IF l_count = 0 THEN
          p_beacon_list := MGMT_GENSVC_TMPL_BCN_ARRAY();
        END IF;
        p_beacon_list.EXTEND();
        l_count := l_count + 1;
        IF l_beacon.participates_avail = 'Y' THEN
          l_avbcn := 1;
        ELSE
          l_avbcn := 0;
        END IF;
        p_beacon_list(l_count) := MGMT_GENSVC_TMPL_BCN(rawtohex(l_beacon.target_guid),
                                                       l_beacon.target_name, 
                                                       l_avbcn);
      END IF;
    END LOOP;
    CLOSE l_beacon_cursor;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      -- it may not have any beacons
      NULL;
  END;
  
  -- get the privileges
  BEGIN
    
    --get the count of all beacons associated with the template. Bypass the join against mgmt_targets 
    --since we want to know what rep_owner sees 
    SELECT count(*)
      INTO l_rep_owner_beacon_count
      FROM MGMT_BCN_TARGET b
     WHERE b.target_guid = l_obj_guid;
      
    --compare the count of all beacons (as seen by rep owner) with the count as read by the EM user
    --use result to set the p_has_privileges, which says whether the current user has privileges on all 
    --template beacons
    IF (l_rep_owner_beacon_count != l_count) THEN
      p_has_privileges := 'N';
    ELSE
      p_has_privileges := 'Y';
    END IF; 
       
  END;                   

  -- get the template vars
  BEGIN
    l_more := TRUE;
    l_count := 0;
    OPEN l_var_cursor;
    WHILE l_more LOOP
      FETCH l_var_cursor INTO l_var;
      l_more := l_var_cursor%FOUND;
      IF l_more THEN
        IF l_count = 0 THEN
          p_variable_list := MGMT_GENSVC_TMPL_VAR_ARRAY();
        END IF;
        p_variable_list.EXTEND();
        l_count := l_count + 1;
        IF l_var.default_value IS NOT NULL THEN
          l_decrypt_value := decrypt(l_var.default_value);
        ELSE
          l_decrypt_value := NULL;
        END IF;
        p_variable_list(l_count) := MGMT_GENSVC_TMPL_VAR(l_var.name,
                                                         l_decrypt_value);
      END IF;
    END LOOP;
    CLOSE l_var_cursor;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      -- it may not have any variables
      NULL;
  END;
END READ_TEMPLATE;


PROCEDURE SET_TEMPLATE_BEACONS( p_template_id IN VARCHAR2, 
                                p_beacon_list IN MGMT_GENSVC_TMPL_BCN_ARRAY)
IS

  l_template_guid MGMT_TEMPLATES.template_guid%TYPE;
  l_template_name MGMT_TEMPLATES.template_name%TYPE;
  l_target_type MGMT_TEMPLATES.target_type%TYPE;
  l_bcn_array MGMT_BCN_ASSOC_ARRAY;
  l_guid_array MGMT_GENSVC_GUID_ARRAY;
  l_avail_beacon VARCHAR2(1);
  l_count PLS_INTEGER;
  l_more BOOLEAN;
  l_cur_user MGMT_CREATED_USERS.user_name%TYPE;

  CURSOR l_rm_bcns_cursor IS
    SELECT t.target_name, t.target_type, b.participates_avail, b.is_local
      FROM MGMT_TARGETS t, MGMT_BCN_TARGET b
     WHERE b.target_guid = l_template_guid
       AND b.beacon_target_guid NOT IN 
           ( SELECT * 
               FROM TABLE(CAST(l_guid_array AS MGMT_GENSVC_GUID_ARRAY))
            )
       AND b.beacon_target_guid = t.target_guid;

  CURSOR l_rm_all_bcns_cursor IS
    SELECT t.target_name, t.target_type, b.participates_avail, b.is_local
      FROM MGMT_TARGETS t, MGMT_BCN_TARGET b
     WHERE b.target_guid = l_template_guid
       AND b.beacon_target_guid = t.target_guid;

  l_rm_bcn l_rm_bcns_cursor%ROWTYPE;

BEGIN

  -- validate params
  IF p_template_id IS NULL THEN
    RAISE_APPLICATION_ERROR(mgmt_global.invalid_params_err,
                            'NULL template_guid found');
  END IF;

  l_template_guid := HEXTORAW(p_template_id);  
  l_cur_user := MGMT_USER.get_current_em_user();

  -- validate user has full privs on the template
  IF MGMT_USER.has_priv(l_cur_user, MGMT_USER.FULL_TEMPLATE, l_template_guid) <> MGMT_USER.USER_HAS_PRIV THEN
    RAISE_APPLICATION_ERROR(MGMT_GLOBAL.insufficient_privileges_err, 
        'The user does not have sufficient privileges to modify this template');
  END IF;

  -- validate user has view privs on the beacons
  FOR l_count IN 1..p_beacon_list.COUNT LOOP
   IF MGMT_USER.has_priv(l_cur_user, MGMT_USER.VIEW_TARGET, p_beacon_list(l_count).target_guid) <> MGMT_USER.USER_HAS_PRIV THEN
     RAISE_APPLICATION_ERROR(MGMT_GLOBAL.insufficient_privileges_err, 
          'The user does not have sufficient privileges to use this beacon');
    END IF;
  END LOOP;

  SELECT template_name, target_type
    INTO l_template_name, l_target_type
    FROM MGMT_TEMPLATES
   WHERE template_guid = l_template_guid;

  -- process the beacons
  IF (p_beacon_list IS NOT NULL) AND (p_beacon_list.COUNT > 0) THEN

    -- create an array with the beacon guids
    l_guid_array := MGMT_GENSVC_GUID_ARRAY();
    l_guid_array.EXTEND(p_beacon_list.COUNT); 

    -- array of beacons to assoc with the template
    l_bcn_array := MGMT_BCN_ASSOC_ARRAY();
    l_bcn_array.EXTEND(p_beacon_list.COUNT); 

    FOR l_count IN 1..p_beacon_list.COUNT LOOP
      -- add the beacon to the array
      IF p_beacon_list(l_count).avail_beacon = 0 THEN
        l_avail_beacon := 'N';
      ELSE
        l_avail_beacon := 'Y';
      END IF;
      l_bcn_array(l_count) := mgmt_bcn_assoc( p_beacon_list(l_count).target_name,
                                              EMD_BCNTXN.p_beacon_type,
                                              l_avail_beacon, 'N');
      l_guid_array(l_count) := p_beacon_list(l_count).target_guid;
    END LOOP;

    EMD_BCNTXN.EMD_BCN_ASSOCIATE_BEACONS(l_template_name, l_target_type, l_bcn_array, TRUE);
    
    -- find the beacons to remove
    l_bcn_array.DELETE;
    l_count := 0;
    l_more := TRUE;
    OPEN l_rm_bcns_cursor;
    WHILE l_more LOOP
      FETCH l_rm_bcns_cursor INTO l_rm_bcn;
      l_more := l_rm_bcns_cursor%FOUND;
      IF l_more THEN
        IF l_count = 0 THEN
          l_bcn_array := MGMT_BCN_ASSOC_ARRAY();
        END IF;
        l_bcn_array.EXTEND();
        l_count := l_count + 1;
        l_bcn_array(l_count) := mgmt_bcn_assoc( l_rm_bcn.target_name,
                                                l_rm_bcn.target_type,
                                                l_rm_bcn.participates_avail,
                                                l_rm_bcn.is_local );
      END IF;
    END LOOP;
    CLOSE l_rm_bcns_cursor;
  ELSE
    -- no beacons provided, delete all beacons from the template
    l_count := 0;
    l_more := TRUE;    
    OPEN l_rm_all_bcns_cursor;
    WHILE l_more LOOP
      FETCH l_rm_all_bcns_cursor INTO l_rm_bcn;
      l_more := l_rm_all_bcns_cursor%FOUND;
      IF l_more THEN
        IF l_count = 0 THEN
          l_bcn_array := MGMT_BCN_ASSOC_ARRAY();
        END IF;
        l_bcn_array.EXTEND();
        l_count := l_count + 1;
        l_bcn_array(l_count) := mgmt_bcn_assoc( l_rm_bcn.target_name,
                                                l_rm_bcn.target_type,
                                                l_rm_bcn.participates_avail,
                                                l_rm_bcn.is_local );
      END IF;
    END LOOP;
    CLOSE l_rm_all_bcns_cursor;
  END IF;

  -- delete associations with any beacons that are not in the list provided
  IF l_count > 0 THEN
    EMD_BCNTXN.EMD_BCN_REMOVE_BEACON_ASSOCS(l_template_name, l_target_type, l_bcn_array, TRUE);
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    IF l_rm_bcns_cursor%ISOPEN THEN  
      CLOSE l_rm_bcns_cursor;
    END IF;
    IF l_rm_all_bcns_cursor%ISOPEN THEN  
      CLOSE l_rm_all_bcns_cursor;
    END IF;
    RAISE;

END SET_TEMPLATE_BEACONS;


PROCEDURE WRITE_TEMPLATE ( p_template_id IN VARCHAR2, 
                           p_test_list IN MGMT_GENSVC_OBJID_ARRAY, 
                           p_variable_list IN MGMT_GENSVC_TMPL_VAR_ARRAY )
IS

  l_template_guid MGMT_TEMPLATES.template_guid%TYPE;
  l_template_name MGMT_TEMPLATES.template_name%TYPE;
  l_target_type MGMT_TEMPLATES.target_type%TYPE;
  l_name_array MGMT_GENSVC_VAR_NAME_ARRAY;
  l_count PLS_INTEGER;
  l_cur_user MGMT_CREATED_USERS.user_name%TYPE;
  l_encrypt_value MGMT_GENSVC_TMPL_VARS.default_value%TYPE;

BEGIN

  -- validate params
  IF p_template_id IS NULL THEN
    RAISE_APPLICATION_ERROR(mgmt_global.invalid_params_err,
                            'NULL template_guid found');
  END IF;

  l_template_guid := HEXTORAW(p_template_id);  

  -- validate user has privs
  l_cur_user := MGMT_USER.get_current_em_user();
  IF MGMT_USER.has_priv(l_cur_user, MGMT_USER.FULL_TEMPLATE, l_template_guid) <> MGMT_USER.USER_HAS_PRIV THEN
    RAISE_APPLICATION_ERROR(MGMT_GLOBAL.insufficient_privileges_err, 
        'The user does not have sufficient privileges to modify this template');
  END IF;

  -- process the tests
  IF (p_test_list IS NOT NULL) AND (p_test_list.COUNT > 0) THEN

    -- make sure we got all the tests are in the repository
    SELECT COUNT(*) 
      INTO l_count
      FROM MGMT_BCN_TXN_DEFN
     WHERE target_guid = l_template_guid
       AND RAWTOHEX(txn_guid) IN 
             ( SELECT * 
                 FROM TABLE(CAST(p_test_list AS MGMT_GENSVC_OBJID_ARRAY))
              );
    IF l_count <> p_test_list.COUNT THEN
      RAISE_APPLICATION_ERROR(mgmt_global.invalid_params_err,
                              'Tests were not found in template');
    END IF;

    -- delete any tests from the repository that are not in the list provided.
    -- a trigger in MGMT_BCN_TXN_DEFN deletes all related test data.
    DELETE FROM MGMT_BCN_TXN_DEFN
     WHERE target_guid = l_template_guid
       AND RAWTOHEX(txn_guid) NOT IN 
           ( SELECT * 
               FROM TABLE(CAST(p_test_list AS MGMT_GENSVC_OBJID_ARRAY))
            );
  ELSE
    -- no tests provided, delete all tests from the template
    DELETE FROM MGMT_BCN_TXN_DEFN
     WHERE target_guid = l_template_guid;
  END IF;  

  -- process the variables
  IF (p_variable_list IS NOT NULL) AND (p_variable_list.COUNT > 0) THEN

    -- make an array with all the variable names
    l_name_array := MGMT_GENSVC_VAR_NAME_ARRAY();
    l_name_array.EXTEND(p_variable_list.COUNT); 
    
    FOR l_count IN 1..p_variable_list.COUNT LOOP
      l_name_array(l_count) := p_variable_list(l_count).name;
      BEGIN
        IF p_variable_list(l_count).default_value IS NOT NULL THEN
          l_encrypt_value := encrypt(p_variable_list(l_count).default_value);
        ELSE
          l_encrypt_value := NULL;
        END IF;
        INSERT INTO MGMT_GENSVC_TMPL_VARS
          ( template_guid, name, default_value )
        VALUES
          ( l_template_guid,
            p_variable_list(l_count).name,
            l_encrypt_value );
      EXCEPTION
        -- update the default value if the variable was already defined
        -- for the template
        WHEN DUP_VAL_ON_INDEX THEN
          UPDATE MGMT_GENSVC_TMPL_VARS
             SET default_value = l_encrypt_value
           WHERE template_guid = l_template_guid
             AND name = p_variable_list(l_count).name;
      END;
    END LOOP;

    -- delete any variables from the template that are not in the list provided.
    DELETE FROM MGMT_GENSVC_TMPL_VARS
     WHERE template_guid = l_template_guid
       AND name NOT IN 
           ( SELECT * 
               FROM TABLE(CAST(l_name_array AS MGMT_GENSVC_VAR_NAME_ARRAY))
            );
  ELSE
    -- no variables provided, delete all variables from the template
    DELETE FROM MGMT_GENSVC_TMPL_VARS
     WHERE template_guid = l_template_guid;
  END IF;

END WRITE_TEMPLATE;


/*
  DELETE_TEMPLATE

*/
PROCEDURE DELETE_TEMPLATE ( p_template_id IN RAW )
IS
  l_cur_user MGMT_CREATED_USERS.user_name%TYPE;

BEGIN

  -- Validate the template id
  IF p_template_id IS NULL THEN
    RAISE_APPLICATION_ERROR(mgmt_global.invalid_params_err,
                            'NULL template_guid found');
  END IF;

  -- validate user has privs
  l_cur_user := MGMT_USER.get_current_em_user();
  IF MGMT_USER.has_priv(l_cur_user, MGMT_USER.FULL_TEMPLATE, p_template_id) <> MGMT_USER.USER_HAS_PRIV THEN
    RAISE_APPLICATION_ERROR(MGMT_GLOBAL.insufficient_privileges_err, 
        'The user does not have sufficient privileges to delete this template');
  END IF;

  -- Delete the tests
  DELETE FROM MGMT_BCN_TXN_DEFN
   WHERE target_guid = p_template_id;

  -- Delete the beacon associations  
  DELETE FROM MGMT_BCN_TARGET
   WHERE target_guid = p_template_id;

  -- Delete the template variables
  DELETE FROM MGMT_GENSVC_TMPL_VARS
   WHERE template_guid = p_template_id;

END DELETE_TEMPLATE;


/*
  WRITE_TEMPLATE
  -- obsolete
*/
/*
PROCEDURE WRITE_TEMPLATE ( p_template_id IN VARCHAR2, 
                           p_test_list IN MGMT_GENSVC_OBJID_ARRAY, 
                           p_beacon_list IN MGMT_GENSVC_TMPL_BCN_ARRAY, 
                           p_variable_list IN MGMT_GENSVC_TMPL_VAR_ARRAY )
IS

  l_template_guid MGMT_TEMPLATES.template_guid%TYPE;
  l_template_name MGMT_TEMPLATES.template_name%TYPE;
  l_target_type MGMT_TEMPLATES.target_type%TYPE;
  l_bcn_array MGMT_BCN_ASSOC_ARRAY;
  l_guid_array MGMT_GENSVC_GUID_ARRAY;
  l_name_array MGMT_GENSVC_VAR_NAME_ARRAY;
  l_avail_beacon VARCHAR2(1);
  l_count PLS_INTEGER;
  l_more BOOLEAN;

  CURSOR l_rm_bcns_cursor IS
    SELECT t.target_name, t.target_type, b.participates_avail, b.is_local
      FROM MGMT_TARGETS t, MGMT_BCN_TARGET b
     WHERE b.target_guid = l_template_guid
       AND b.beacon_target_guid NOT IN 
           ( SELECT * 
               FROM TABLE(CAST(l_guid_array AS MGMT_GENSVC_GUID_ARRAY))
            )
       AND b.beacon_target_guid = t.target_guid;

  CURSOR l_rm_all_bcns_cursor IS
    SELECT t.target_name, t.target_type, b.participates_avail, b.is_local
      FROM MGMT_TARGETS t, MGMT_BCN_TARGET b
     WHERE b.target_guid = l_template_guid
       AND b.beacon_target_guid = t.target_guid;

  l_rm_bcn l_rm_bcns_cursor%ROWTYPE;

BEGIN

  -- validate params
  IF p_template_id IS NULL THEN
    RAISE_APPLICATION_ERROR(mgmt_global.invalid_params_err,
                            'NULL template_guid found');
  END IF;
  IF (p_test_list IS NULL OR p_test_list.COUNT <= 0) AND
     (p_beacon_list IS NULL OR p_beacon_list.COUNT <= 0) THEN
    RAISE_APPLICATION_ERROR(mgmt_global.invalid_params_err,
                            'Cannot create a template with no tests or beacons');
  END IF;

  l_template_guid := HEXTORAW(p_template_id);  
  SELECT template_name, target_type
    INTO l_template_name, l_target_type
    FROM MGMT_TEMPLATES
   WHERE template_guid = l_template_guid;

  -- process the tests
  IF (p_test_list IS NOT NULL AND p_test_list.COUNT > 0) THEN

    -- make sure we got all the tests are in the repository
    SELECT COUNT(*) 
      INTO l_count
      FROM MGMT_BCN_TXN_DEFN
     WHERE target_guid = l_template_guid
       AND RAWTOHEX(txn_guid) IN 
             ( SELECT * 
                 FROM TABLE(CAST(p_test_list AS MGMT_GENSVC_OBJID_ARRAY))
              );
    IF l_count <> p_test_list.COUNT THEN
      RAISE_APPLICATION_ERROR(mgmt_global.invalid_params_err,
                              'Tests were not found in template');
    END IF;

    -- delete any tests from the repository that are not in the list provided.
    -- a trigger in MGMT_BCN_TXN_DEFN deletes all related test data.
    DELETE FROM MGMT_BCN_TXN_DEFN
     WHERE target_guid = l_template_guid
       AND RAWTOHEX(txn_guid) NOT IN 
           ( SELECT * 
               FROM TABLE(CAST(p_test_list AS MGMT_GENSVC_OBJID_ARRAY))
            );
  ELSE
    -- no tests provided, delete all tests from the template
    DELETE FROM MGMT_BCN_TXN_DEFN
     WHERE target_guid = l_template_guid;
  END IF;  

  -- process the beacons
  IF (p_beacon_list IS NOT NULL) AND (p_beacon_list.COUNT > 0) THEN

    -- create an array with the beacon guids
    l_guid_array := MGMT_GENSVC_GUID_ARRAY();
    l_guid_array.EXTEND(p_beacon_list.COUNT); 

    -- array of beacons to assoc with the template
    l_bcn_array := MGMT_BCN_ASSOC_ARRAY();
    l_bcn_array.EXTEND(p_beacon_list.COUNT); 

    FOR l_count IN 1..p_beacon_list.COUNT LOOP
      -- add the beacon to the array
      IF p_beacon_list(l_count).avail_beacon = 0 THEN
        l_avail_beacon := 'N';
      ELSE
        l_avail_beacon := 'Y';
      END IF;
      l_bcn_array(l_count) := mgmt_bcn_assoc( p_beacon_list(l_count).target_name,
                                              EMD_BCNTXN.p_beacon_type,
                                              l_avail_beacon, 'N');
      l_guid_array(l_count) := p_beacon_list(l_count).target_guid;
    END LOOP;

    EMD_BCNTXN.EMD_BCN_ASSOCIATE_BEACONS(l_template_name, l_target_type, l_bcn_array, TRUE);
    
    -- find the beacons to remove
    l_bcn_array.DELETE;
    l_count := 0;
    l_more := TRUE;
    OPEN l_rm_bcns_cursor;
    WHILE l_more LOOP
      FETCH l_rm_bcns_cursor INTO l_rm_bcn;
      l_more := l_rm_bcns_cursor%FOUND;
      IF l_more THEN
        IF l_count = 0 THEN
          l_bcn_array := MGMT_BCN_ASSOC_ARRAY();
        END IF;
        l_bcn_array.EXTEND();
        l_count := l_count + 1;
        l_bcn_array(l_count) := mgmt_bcn_assoc( l_rm_bcn.target_name,
                                                l_rm_bcn.target_type,
                                                l_rm_bcn.participates_avail,
                                                l_rm_bcn.is_local );
      END IF;
    END LOOP;
    CLOSE l_rm_bcns_cursor;
  ELSE
    -- no beacons provided, delete all beacons from the template
    l_count := 0;
    l_more := TRUE;    
    OPEN l_rm_all_bcns_cursor;
    WHILE l_more LOOP
      FETCH l_rm_all_bcns_cursor INTO l_rm_bcn;
      l_more := l_rm_all_bcns_cursor%FOUND;
      IF l_more THEN
        IF l_count = 0 THEN
          l_bcn_array := MGMT_BCN_ASSOC_ARRAY();
        END IF;
        l_bcn_array.EXTEND();
        l_count := l_count + 1;
        l_bcn_array(l_count) := mgmt_bcn_assoc( l_rm_bcn.target_name,
                                                l_rm_bcn.target_type,
                                                l_rm_bcn.participates_avail,
                                                l_rm_bcn.is_local );
      END IF;
    END LOOP;
    CLOSE l_rm_all_bcns_cursor;
  END IF;

  -- delete associations with any beacons that are not in the list provided
  IF l_count > 0 THEN
    EMD_BCNTXN.EMD_BCN_REMOVE_BEACON_ASSOCS(l_template_name, l_target_type, l_bcn_array, TRUE);
  END IF;

  -- process the variables
  IF (p_variable_list IS NOT NULL) AND (p_variable_list.COUNT > 0) THEN

    -- make an array with all the variable names
    l_name_array := MGMT_GENSVC_VAR_NAME_ARRAY();
    l_name_array.EXTEND(p_variable_list.COUNT); 
    
    FOR l_count IN 1..p_variable_list.COUNT LOOP
      l_name_array(l_count) := p_variable_list(l_count).name;
      BEGIN
        INSERT INTO MGMT_GENSVC_TMPL_VARS
          ( template_guid, name, default_value )
        VALUES
          ( l_template_guid,
            p_variable_list(l_count).name,
            p_variable_list(l_count).default_value );
      EXCEPTION
        -- update the default value if the variable was already defined
        -- for the template
        WHEN DUP_VAL_ON_INDEX THEN
          UPDATE MGMT_GENSVC_TMPL_VARS
             SET default_value = p_variable_list(l_count).default_value
           WHERE template_guid = l_template_guid
             AND name = p_variable_list(l_count).name;
      END;
    END LOOP;

    -- delete any variables from the template that are not in the list provided.
    DELETE FROM MGMT_GENSVC_TMPL_VARS
     WHERE template_guid = l_template_guid
       AND name NOT IN 
           ( SELECT * 
               FROM TABLE(CAST(l_name_array AS MGMT_GENSVC_VAR_NAME_ARRAY))
            );
  ELSE
    -- no variables provided, delete all variables from the template
    DELETE FROM MGMT_GENSVC_TMPL_VARS
     WHERE template_guid = l_template_guid;
  END IF;

EXCEPTION

  WHEN OTHERS THEN
    IF l_rm_bcns_cursor%ISOPEN THEN  
      CLOSE l_rm_bcns_cursor;
    END IF;
    IF l_rm_all_bcns_cursor%ISOPEN THEN  
      CLOSE l_rm_all_bcns_cursor;
    END IF;
END WRITE_TEMPLATE;
*/

/*
  APPLY_TEMPLATE
  -- obsolete
*/
/*
PROCEDURE APPLY_TEMPLATE ( p_target_name IN VARCHAR2, 
                           p_target_type IN VARCHAR2,
                           p_beacon_list IN MGMT_GENSVC_TMPL_BCN_ARRAY,
                           p_test_list   IN MGMT_GENSVC_OBJID_ARRAY,
                           p_job_list    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_avail_beacon       VARCHAR2(1);
  l_test               MGMT_GENSVC_UBJOB_TEST;
  l_tests              MGMT_GENSVC_UBJOB_TEST_ARRAY;
  l_new_beacons        MGMT_BCN_ASSOC_ARRAY;
  l_new_bcn_list       MGMT_GENSVC_GUID_ARRAY;
  l_job_id             MGMT_JOB.job_id%TYPE;
  l_job_count          PLS_INTEGER := 0;
  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';

  CURSOR l_new_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 RAWTOHEX(txn_guid) IN 
             ( SELECT * 
                 FROM TABLE(CAST(p_test_list AS MGMT_GENSVC_OBJID_ARRAY))
              );

  CURSOR l_older_beacons_cursor (c_new_beacons MGMT_GENSVC_GUID_ARRAY) IS
    SELECT t.target_name, t.target_guid
      FROM MGMT_TARGETS t, MGMT_BCN_TARGET b
     WHERE b.target_guid = l_target_guid
       AND b.beacon_target_guid = t.target_guid
       AND b.target_guid <> b.beacon_target_guid
       AND b.beacon_target_guid NOT IN 
             ( SELECT * 
                 FROM TABLE(CAST(c_new_beacons AS MGMT_GENSVC_GUID_ARRAY))
              );

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

  l_beacon_1 l_older_beacons_cursor%ROWTYPE;
  l_beacon_2 l_all_beacons_cursor%ROWTYPE;
  l_mon_test l_monitoring_tests_cursor%ROWTYPE;
  l_new_test l_new_tests_cursor%ROWTYPE;

BEGIN

  -- validate parameters
  IF (p_target_name IS NULL) OR (p_target_type IS NULL) 
     OR ( ((p_beacon_list IS NULL) OR (p_beacon_list.COUNT <= 0))
          AND ((p_test_list IS NULL) OR (p_test_list.COUNT <= 0)) ) THEN
    raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
      'MGMT_GENSVC_MONTMPL.APPLY_TEMPLATE: 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;

  -- process beacons
  IF (p_beacon_list IS NOT NULL) AND (p_beacon_list.COUNT > 0) THEN
    -- associate the new beacons
    FOR l_count IN 1..p_beacon_list.COUNT LOOP
      -- validate the beacon guid
      SELECT target_guid, target_name
        INTO l_beacon_guid, l_beacon_name
        FROM MGMT_TARGETS
       WHERE target_guid = HEXTORAW(p_beacon_list(l_count).target_guid)
         AND target_type = EMD_BCNTXN.p_beacon_type;
      -- avail settings
      IF p_beacon_list(l_count).avail_beacon <> 0 THEN
        l_avail_beacon := 'Y';
      ELSE
        l_avail_beacon := 'N';
      END IF;
      -- add the beacon to the new beacon list
      IF l_count = 1 THEN
        l_new_beacons := MGMT_BCN_ASSOC_ARRAY();
        l_new_bcn_list := MGMT_GENSVC_GUID_ARRAY();
      END IF;
      l_new_beacons.EXTEND(1);
      l_new_beacons(l_count) := MGMT_BCN_ASSOC( l_beacon_name,
                                                EMD_BCNTXN.p_beacon_type,
                                                l_avail_beacon,
                                                'N');
      l_new_bcn_list.EXTEND(1);
      l_new_bcn_list(l_count) := l_beacon_guid;
    END LOOP;
    IF (l_new_beacons IS NOT NULL) AND (l_new_beacons.COUNT > 0) THEN
      EMD_BCNTXN.EMD_BCN_ASSOCIATE_BEACONS( p_target_name,
                                            p_target_type,
                                            l_new_beacons,
                                            TRUE);
      l_new_beacons.DELETE;
      -- now push the collections to the new beacons
      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);
        END IF;
      END LOOP;
      CLOSE l_monitoring_tests_cursor;
      IF l_tests IS NOT NULL THEN
        -- submit a job for each beacon
        FOR l_count IN 1..p_beacon_list.COUNT LOOP
          -- update all collections at the beacon
          IF l_tests IS NOT NULL THEN
            l_job_id := MGMT_GENSVC_UPDBCN.SUBMIT_UPDBCN_JOB( 
                                                  l_target_guid,
                                                  l_beacon_guid,
                                                  l_beacon_name,
                                                  l_tests,
                                                  MGMT_GENSVC_UPDBCN.UPDATE_JOB,
                                                  'APPLY_TEMPLATE' );
            IF l_job_id IS NOT NULL THEN
              IF l_job_count = 0 THEN
                p_job_list := MGMT_GENSVC_UBJOB_JOB_ARRAY();
              END IF;
              p_job_list.EXTEND(1);
              l_job_count := l_job_count + 1;
              p_job_list(l_job_count) := MGMT_GENSVC_UBJOB_JOB(l_beacon_name,
                                                               l_job_id);
            END IF;
          END IF;
        END LOOP;
        -- clear test array
        l_tests.DELETE;
      END IF;
    END IF;
  END IF;

  -- push collections for the new tests for the pre-existing beacons
  IF (p_test_list IS NOT NULL) AND (p_test_list.COUNT > 0) THEN
    -- get the list of new tests
    l_more := TRUE;
    l_count := 0;
    OPEN l_new_tests_cursor;
    WHILE l_more LOOP
      FETCH l_new_tests_cursor INTO l_new_test;
      l_more := l_new_tests_cursor%FOUND;
      IF l_more THEN
        IF l_count = 0 THEN
          l_tests := MGMT_GENSVC_UBJOB_TEST_ARRAY();
        END IF;
        l_tests.EXTEND(1);
        l_count := l_count + 1;
        l_tests(l_count) := MGMT_GENSVC_UBJOB_TEST(l_new_test.name,
                                                   l_new_test.txn_type,
                                                   l_new_test.txn_guid,
                                                   l_new_test.is_avail,
                                                   1);
      END IF;
    END LOOP;
    CLOSE l_new_tests_cursor;
    IF l_tests IS NOT NULL THEN
      -- update the list of tests for all beacons which were NOT updated above
      l_more := TRUE;
      l_count := 0;
      IF l_new_bcn_list IS NOT NULL THEN
        OPEN l_older_beacons_cursor (l_new_bcn_list);
        WHILE l_more LOOP
        FETCH l_older_beacons_cursor INTO l_beacon_1;
        l_more := l_older_beacons_cursor%FOUND;
        IF l_more THEN
          l_job_id := MGMT_GENSVC_UPDBCN.SUBMIT_UPDBCN_JOB( 
                                                l_target_guid,
                                                l_beacon_1.target_guid,
                                                l_beacon_1.target_name,
                                                l_tests,
                                                MGMT_GENSVC_UPDBCN.UPDATE_JOB,
                                                'APPLY_TEMPLATE' );
          IF l_job_id IS NOT NULL THEN
            IF l_job_count = 0 THEN
              p_job_list := MGMT_GENSVC_UBJOB_JOB_ARRAY();
            END IF;
            p_job_list.EXTEND(1);
            l_job_count := l_job_count + 1;
            p_job_list(l_job_count) := MGMT_GENSVC_UBJOB_JOB(l_beacon_1.target_name,
                                                             l_job_id);
          END IF;
        END IF;
        END LOOP;
        CLOSE l_older_beacons_cursor;
        l_new_bcn_list.DELETE;
      ELSE
        OPEN l_all_beacons_cursor;
        WHILE l_more LOOP
        FETCH l_all_beacons_cursor INTO l_beacon_2;
        l_more := l_all_beacons_cursor%FOUND;
        IF l_more THEN
          l_job_id := MGMT_GENSVC_UPDBCN.SUBMIT_UPDBCN_JOB( 
                                                l_target_guid,
                                                l_beacon_2.target_guid,
                                                l_beacon_2.target_name,
                                                l_tests,
                                                MGMT_GENSVC_UPDBCN.UPDATE_JOB,
                                                'APPLY_TEMPLATE' );
          IF l_job_id IS NOT NULL THEN
            IF l_job_count = 0 THEN
              p_job_list := MGMT_GENSVC_UBJOB_JOB_ARRAY();
            END IF;
            p_job_list.EXTEND(1);
            l_job_count := l_job_count + 1;
            p_job_list(l_job_count) := MGMT_GENSVC_UBJOB_JOB(l_beacon_2.target_name,
                                                             l_job_id);
          END IF;
        END IF;
        END LOOP;
        CLOSE l_all_beacons_cursor;      
      END IF;
      -- clear the tests array
      l_tests.DELETE;
    END IF;
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    IF l_monitoring_tests_cursor%ISOPEN THEN
      CLOSE l_monitoring_tests_cursor;
    END IF;
    IF l_older_beacons_cursor%ISOPEN THEN
      CLOSE l_older_beacons_cursor;
    END IF;
    IF l_all_beacons_cursor%ISOPEN THEN
      CLOSE l_all_beacons_cursor;
    END IF;
    IF l_new_tests_cursor%ISOPEN THEN
      CLOSE l_new_tests_cursor;
    END IF;
    IF l_tests IS NOT NULL THEN
      l_tests.DELETE;
    END IF;
    IF l_new_beacons IS NOT NULL THEN
      l_new_beacons.DELETE;
    END IF;
    IF l_new_bcn_list IS NOT NULL THEN
      l_new_bcn_list.DELETE;
    END IF;
    RAISE;

END APPLY_TEMPLATE;
*/

END MGMT_GENSVC_MONTMPL;
/
SHOW ERRORS;