Rem
Rem $Header: group_hist_pkgbody.sql 09-apr-2003.23:54:19 streddy Exp $
Rem
Rem group_hist_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2003, Oracle Corporation.  All rights reserved.  
Rem
Rem    NAME
Rem      group_hist_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    streddy    04/09/03 - streddy_composite_target_availability
Rem    streddy    04/08/03 - Created
Rem
CREATE OR REPLACE PACKAGE BODY em_hist_comptgt AS

-- PURPOSE
--   Internal procedure to add or delete a target from the
--   membership history. This is called from BEFORE INSERT and
--   AFTER DELETE triggers of mgmt_targets_membership table, 
--   so we CANNOT query the membership table in this proc.
-- PARAMETERS
--   v_member_added - TRUE if a member is getting added; FALSE if
--                    a member got deleted
PROCEDURE ADD_HISTORY_SNAPSHOT(v_composite_target_guid IN RAW,
                               v_member_target_guid    IN RAW,
                               v_member_added          IN BOOLEAN)
IS
  l_tzrgn          MGMT_TARGETS.timezone_region%TYPE;
  l_timestamp      DATE;
  l_old_member_targets MGMT_MEMBER_GUIDS;
  l_new_member_targets MGMT_MEMBER_GUIDS;
  l_index          BINARY_INTEGER;
  l_rowid          ROWID;
BEGIN
  -- make sure that the composite target exists before updating any state
  BEGIN
    SELECT timezone_region INTO l_tzrgn
      FROM mgmt_targets
     WHERE target_guid = v_composite_target_guid;
  EXCEPTION
     WHEN NO_DATA_FOUND THEN
        RETURN;
  END;


  l_timestamp := mgmt_global.sysdate_tzrgn(l_tzrgn);

  -- get the old members
  BEGIN
    SELECT rowid, member_targets
      INTO l_rowid, l_old_member_targets
      FROM mgmt_target_memberships_hist             
     WHERE composite_target_guid = v_composite_target_guid
       AND end_timestamp IS NULL;

    UPDATE mgmt_target_memberships_hist 
       SET end_timestamp = l_timestamp
     WHERE rowid = l_rowid;
  EXCEPTION
     WHEN NO_DATA_FOUND THEN
      -- this means that this is the first record for the composite
     l_old_member_targets := MGMT_MEMBER_GUIDS();
  END;


  IF (v_member_added) THEN
     -- Add this member to the existing members
     l_old_member_targets.extend;
     l_old_member_targets(l_old_member_targets.last) := v_member_target_guid;
     l_new_member_targets := l_old_member_targets;
  ELSE
     -- create a new guid array so that we don't end up sparse array during
     -- deletions
     l_new_member_targets := MGMT_MEMBER_GUIDS();
     l_index := 0;
     FOR i IN 1..l_old_member_targets.count LOOP
        IF (l_old_member_targets(i) <> v_member_target_guid) THEN
           l_index := l_index + 1;
           l_new_member_targets.extend;
           l_new_member_targets(l_index) := l_old_member_targets(i);
        END IF;
     END LOOP;    
  END IF;

  INSERT INTO mgmt_target_memberships_hist
      (composite_target_guid, start_timestamp, member_targets)
   VALUES
      (v_composite_target_guid, l_timestamp, l_new_member_targets);

END ADD_HISTORY_SNAPSHOT;


-- Whenever a new target is added, this method is called from the
-- mgmt_targets BEFORE INSERT trigger.
PROCEDURE INIT_MEMBERSHIPS_HIST(v_composite_target_guid IN RAW,
                                v_composite_tzrgn       IN VARCHAR2)
IS
  l_timestamp      DATE;
  l_member_targets MGMT_MEMBER_GUIDS;
  l_count          BINARY_INTEGER := 0;
  l_rowid          ROWID;
BEGIN

  -- Figure out the members first
  l_member_targets := MGMT_MEMBER_GUIDS();

  FOR member IN (SELECT member_target_guid 
                   FROM mgmt_target_memberships
                  WHERE composite_target_guid = v_composite_target_guid)
  LOOP
    BEGIN
      SELECT rowid INTO l_rowid
        FROM mgmt_targets
       WHERE target_guid = member.member_target_guid;
       l_count := l_count + 1;
       l_member_targets.extend(1);
       l_member_targets(l_count) := member.member_target_guid;
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
        -- ignore the member targets that are not created yet
       NULL;
    END;
  END LOOP;

  -- If there are no members, this target may not be a composite target.
  -- Currently, there is no type property that tells us whether a target
  -- instance is a composite or not.
  IF (l_count > 0) THEN
    l_timestamp := mgmt_global.sysdate_tzrgn(v_composite_tzrgn);
    INSERT INTO mgmt_target_memberships_hist
       (composite_target_guid, start_timestamp, member_targets)
    VALUES
       (v_composite_target_guid, l_timestamp, l_member_targets);
  END IF;  


  -- See if this composite target is actually a member of other composite targets
  -- and add update the history of containing composite targets
  FOR comp_tgt IN (SELECT composite_target_guid
                     FROM mgmt_target_memberships
                    WHERE member_target_guid = v_composite_target_guid)
  LOOP
       add_history_snapshot(comp_tgt.composite_target_guid, 
                            v_composite_target_guid, TRUE);
  END LOOP;  

END INIT_MEMBERSHIPS_HIST;


END em_hist_comptgt;
/

show errors;