Rem
Rem $Header: group_flat_pkgbody.sql 15-oct-2003.16:18:20 streddy Exp $
Rem
Rem group_flat_pkgbodys.sql
Rem
Rem Copyright (c) 2003, Oracle Corporation.  All rights reserved.  
Rem
Rem    NAME
Rem      group_flat_pkgbodys.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      Implementation of group flattening.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    streddy     10/15/03 - Handle deleted targets case 
Rem    streddy     09/23/03 - streddy_bug-3108368 
Rem    streddy     09/23/03 - Created
Rem

CREATE OR REPLACE PACKAGE BODY em_flat_comptgt AS


---
--- PURPOSE
---    Updates the exploded memberships of the specified 
---    group/composite in MGMT_FLAT_TARGET_MEMBERSHIPS
---    table.
---
PROCEDURE update_flat_memberships(v_composite_guid IN RAW)
IS
  l_tname mgmt_targets.TARGET_NAME%TYPE;
  l_ttype mgmt_targets.TARGET_TYPE%TYPE;
BEGIN
   -- Delete the existing memberships
   DELETE FROM mgmt_flat_target_memberships
    WHERE composite_target_guid = v_composite_guid;

   -- Get the target name/type for the composite
   SELECT target_name, target_type
     INTO l_tname, l_ttype
     FROM mgmt_targets
    WHERE target_guid = v_composite_guid;
 
   -- Now recursively fetch the memberships from the 
   -- mgmt_target_memberships table
   INSERT INTO mgmt_flat_target_memberships
     (composite_target_name,  composite_target_type, composite_target_guid,
      member_target_name, member_target_type,  member_target_guid, 
      is_group_memb)
     SELECT DISTINCT l_tname, l_ttype, v_composite_guid,
           member_target_name, member_target_type, member_target_guid, 0
       FROM  mgmt_target_memberships
      START WITH composite_target_guid = v_composite_guid
      CONNECT BY PRIOR member_target_guid = composite_target_guid;

   -- Set is_group flag to 1 for memberships that are derived from a group
   UPDATE mgmt_flat_target_memberships f1
      SET is_group_memb = 1
    WHERE composite_target_guid = v_composite_guid
     AND  EXISTS
         (SELECT 1     
            FROM   mgmt_flat_target_memberships f2,
                   mgmt_target_memberships m,
                   mgmt_type_properties p
            WHERE  p.property_name = 'is_group'
              AND  p.target_type   = m.composite_target_type
              AND  (m.composite_target_guid = f2.member_target_guid OR
                    m.composite_target_guid = f2.composite_target_guid)
              AND  f1.composite_target_guid = f2.composite_target_guid
              AND  f1.member_target_guid = m.member_target_guid);

END update_flat_memberships;


---
--- PURPOSE
---   Updates the flattened list for parent groups of the specified
---   composite/group target.
---
PROCEDURE update_parent_flat_memberships(v_composite_guid IN RAW)
IS
  CURSOR parent_cursor IS
    SELECT DISTINCT composite_target_guid 
      FROM mgmt_target_memberships
     START WITH member_target_guid = v_composite_guid
     CONNECT BY prior composite_target_guid = member_target_guid;

  TYPE p_parent_guids IS TABLE OF RAW(16) INDEX BY BINARY_INTEGER;
  l_parent_guids  p_parent_guids;
BEGIN
  OPEN parent_cursor;
  FETCH parent_cursor BULK COLLECT INTO l_parent_guids;
  CLOSE parent_cursor; 

  FOR i IN 1..l_parent_guids.COUNT LOOP
    UPDATE_FLAT_MEMBERSHIPS(l_parent_guids(i));
  END LOOP;

END update_parent_flat_memberships;

-- Adds the specified target to the specified groups lists
PROCEDURE add_target_to_flat_memberships(v_target_name IN VARCHAR2,
                                         v_target_type IN VARCHAR2,
                                         v_target_guid IN RAW,
                                         v_groups IN SMP_EMD_NVPAIR_ARRAY)
IS
  CURSOR parent_cursor IS
    SELECT DISTINCT composite_target_guid 
      FROM mgmt_target_memberships
     START WITH member_target_guid IN
        (SELECT t.target_guid 
           FROM mgmt_targets t,
                TABLE(CAST(v_groups AS SMP_EMD_NVPAIR_ARRAY)) g
          WHERE t.target_name = g.name
            AND t.target_type = g.value)
     CONNECT BY prior composite_target_guid = member_target_guid;

  TYPE p_parent_guids IS TABLE OF RAW(16) INDEX BY BINARY_INTEGER;
  l_parent_guids  p_parent_guids;
  l_tname mgmt_targets.TARGET_NAME%TYPE;
  l_ttype mgmt_targets.TARGET_TYPE%TYPE;
  l_group_guid  mgmt_targets.TARGET_GUID%TYPE;
BEGIN

  -- Add the member to the parent groups
  FOR i IN 1..v_groups.COUNT LOOP
    SELECT target_guid
      INTO l_group_guid
      FROM mgmt_targets
     WHERE target_name = v_groups(i).name
       AND target_type = v_groups(i).value;

    -- Try inserting the target first; if it fails, update the
    -- existing membership
    BEGIN
      INSERT INTO mgmt_flat_target_memberships
        (composite_target_name,  composite_target_type, composite_target_guid,
         member_target_name, member_target_type,  member_target_guid, 
         is_group_memb)
      VALUES
         (v_groups(i).name, v_groups(i).value, l_group_guid,
          v_target_name, v_target_type, v_target_guid, 1);
    EXCEPTION
      -- If the membership entry already exists, change the
      -- is_group_memb to 1 if needed
      WHEN DUP_VAL_ON_INDEX THEN
        UPDATE mgmt_flat_target_memberships
           SET is_group_memb = 1
         WHERE composite_target_guid = l_group_guid
           AND member_target_guid = v_target_guid
           AND is_group_memb = 0;
    END;
  END LOOP;


  -- Now add the grand parents of the parent groups
  OPEN parent_cursor;
  FETCH parent_cursor BULK COLLECT INTO l_parent_guids;
  CLOSE parent_cursor; 

  FOR i IN 1..l_parent_guids.COUNT LOOP
     -- Get the target name/type for the composite
    SELECT target_name, target_type
      INTO l_tname, l_ttype
      FROM mgmt_targets
     WHERE target_guid = l_parent_guids(i);

    -- Try inserting the target first; if it fails, update the
    -- existing membership
    BEGIN
      INSERT INTO mgmt_flat_target_memberships
        (composite_target_name,  composite_target_type, composite_target_guid,
         member_target_name, member_target_type,  member_target_guid, 
         is_group_memb)
      VALUES
         (l_tname, l_ttype, l_parent_guids(i),
          v_target_name, v_target_type, v_target_guid, 1);
    EXCEPTION
      -- If the membership entry already exists, change the
      -- is_group_memb to 1 if needed
      WHEN DUP_VAL_ON_INDEX THEN
        UPDATE mgmt_flat_target_memberships
           SET is_group_memb = 1
         WHERE composite_target_guid = l_parent_guids(i)
           AND member_target_guid = v_target_guid
           AND is_group_memb = 0;
    END;

  END LOOP;
END add_target_to_flat_memberships;

---
--- PURPOSE
---    Updates all parent groups upon deletion of a group.
---
PROCEDURE handle_delete_group(v_composite_guid IN RAW)
IS
  l_parent_guids MGMT_GUID_ARRAY;
BEGIN

  -- Get the parents of the group being deleted
  SELECT MGMT_GUID_OBJ(composite_target_guid, NULL)
    BULK COLLECT INTO l_parent_guids
    FROM mgmt_flat_target_memberships
   WHERE member_target_guid = v_composite_guid;

  -- Delete the current memberships for the group and its parent groups
  DELETE FROM mgmt_flat_target_memberships
   WHERE (composite_target_guid IN 
            (SELECT guid FROM TABLE(CAST(l_parent_guids AS MGMT_GUID_ARRAY)))
          OR composite_target_guid = v_composite_guid);

  -- Finally update the parents memberships
  FOR i IN 1..l_parent_guids.COUNT LOOP
    UPDATE_FLAT_MEMBERSHIPS(l_parent_guids(i).guid);
  END LOOP;

END handle_delete_group;


---
--- PURPOSE
---   Called from loader before inserting rows into
---   MGMT_TARGET_MEMBERSHIPS table      
---
PROCEDURE start_memberships_rowset
IS
BEGIN
     p_updated_composite_targets := SMP_EMD_NVPAIR_ARRAY();
END start_memberships_rowset;

---
--- PURPOSE
---   Called whenever a composite target memberships are modified
---   during a Loader session
---
PROCEDURE composite_target_modified(v_composite_target_name VARCHAR2,
                                    v_composite_target_type VARCHAR2)
IS 
  l_count NUMBER;
BEGIN
   -- If the package variable is null, then we are not in a loader
   -- session
   IF (p_updated_composite_targets IS NULL) THEN
      RETURN;
   END IF;

   -- Add the composite target to the list if required
   BEGIN
     SELECT 1
       INTO l_count
       FROM TABLE(CAST(p_updated_composite_targets AS SMP_EMD_NVPAIR_ARRAY)) c
      WHERE c.name   = v_composite_target_name
        AND c.value  = v_composite_target_type;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       p_updated_composite_targets.extend(1);
       p_updated_composite_targets(p_updated_composite_targets.LAST) :=
         SMP_EMD_NVPAIR(v_composite_target_name, v_composite_target_type);
   END;

END composite_target_modified;

---
--- PURPOSE
---   Called from loader when done with inserting rows into
---   MGMT_TARGET_MEMBERSHIPS table      
---
PROCEDURE end_memberships_rowset
IS
  l_target_guid mgmt_targets.TARGET_GUID%TYPE;
BEGIN
  -- For each of the modified composites, update its memberships
  -- and its parents memberships
  FOR idx IN 1..p_updated_composite_targets.COUNT LOOP
  
    BEGIN
       SELECT target_guid
         INTO l_target_guid
         FROM mgmt_targets
        WHERE target_name = p_updated_composite_targets(idx).name
          AND target_type = p_updated_composite_targets(idx).value;

      update_flat_memberships(l_target_guid);
    
      update_parent_flat_memberships(l_target_guid);
    EXCEPTION
      -- this is called in the context of an agent upload, so never
      -- propagate the errors back to the agent

      -- Ignore non-existing targets case
      WHEN NO_DATA_FOUND THEN
         NULL;

      -- Log all the other errors
      WHEN OTHERS THEN
        mgmt_log.log_error(v_module_name_in => 'LOADER',
                           v_error_code_in  =>  0,
                           v_error_msg_in   => SUBSTR(SQLERRM, 1, 2000));
    END;

  END LOOP;

  -- Free the object
  p_updated_composite_targets.delete;
  p_updated_composite_targets := NULL;


END end_memberships_rowset;

END em_flat_comptgt;

/

SHOW ERRORS;