Rem drv: <create type="pkgbodys" pos="system/groups_ui_pkgbody.sql-"/>
Rem
Rem $Header: syshist_pkgbody.sql 07-jul-2006.12:35:16 vkarpura Exp $
Rem
Rem syshist_pkgbody.sql
Rem
Rem Copyright (c) 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      syshist_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    vkarpura    04/14/06  -  Creation 
Rem    vkarpura    04/14/06 -  Creation 
Rem    vkarpura    04/14/06 - Created
Rem

CREATE OR REPLACE PACKAGE BODY SYST_HIST AS


PROCEDURE RECORD_CHANGES( p_system IN SMP_EMD_NVPAIR,
			  v_system_list IN SMP_EMD_NVPAIR_ARRAY)
IS
    l_current_user mgmt_targets.owner%TYPE;
BEGIN
    l_current_user := MGMT_USER.get_current_em_user();

   RECORD_CHANGES(p_system, v_system_list, l_current_user);
END;

PROCEDURE RECORD_CHANGES( p_system IN SMP_EMD_NVPAIR,
			  v_system_list IN SMP_EMD_NVPAIR_ARRAY,
                          modified_by   IN VARCHAR2)
IS
l_targets_list SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY();
l_system_guid MGMT_TARGETS.TARGET_GUID%TYPE;
l_member_guid MGMT_TARGETS.TARGET_GUID%TYPE;
l_member_name MGMT_TARGETS.TARGET_NAME%TYPE;
l_member_type MGMT_TARGETS.TARGET_TYPE%TYPE;


--ecm precall back
l_precall_arr SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY();
l_preload_out SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY();
l_nvpair SMP_EMD_NVPAIR;
snapshot_id RAW(16) := NULL;
l_name VARCHAR(100);

l_system_name MGMT_TARGETS.TARGET_NAME%TYPE;
l_system_type MGMT_TARGETS.TARGET_TYPE%TYPE;
BEGIN
   IF (p_system IS NOT NULL AND
       p_system.name IS NOT NULL AND
       p_system.value IS NOT NULL ) THEN
   BEGIN
     l_system_name := p_system.name;
     l_system_type := p_system.value;
   END;
   ELSE 
     RETURN;
   END IF;
--dbms_output.put_line('Updating Sytem changes for :'||l_system_name || ' of ' || l_system_type);

  IF (IS_TRACKED_SYSTEM(p_system)) THEN

   l_system_guid := MGMT_TARGET.GET_TARGET_GUID(l_system_name, l_system_type); 

   l_precall_arr.extend(4);
   l_nvpair := SMP_EMD_NVPAIR(ECM_CT.G_TARGET_GUID, l_system_guid);
   l_precall_arr(1) := l_nvpair;
   l_nvpair := SMP_EMD_NVPAIR(ECM_CT.G_COLLECTION_NAME, 'system_change_config');
   l_precall_arr(2) := l_nvpair;
   l_nvpair := SMP_EMD_NVPAIR(ECM_CT.G_COLLECTION_TIMESTAMP, 
			      to_char(sysdate, MGMT_GLOBAL.G_AGENT_DATE_FORMAT));
   l_precall_arr(3) := l_nvpair;
   l_nvpair := SMP_EMD_NVPAIR(ECM_CT.G_CONFIG_METADATA_VERSION, '1');
   l_precall_arr(4) := l_nvpair;

   ECM_CT.PRELOAD_CALLBACK(l_precall_arr, l_preload_out);

   IF l_preload_out IS NOT NULL AND l_preload_out.COUNT > 0 THEN 
    l_name := UPPER(l_preload_out(1).NAME);
     IF (l_name = ECM_CT.G_ECM_SNAPSHOT_ID) THEN
      snapshot_id := l_preload_out(1).VALUE;
     END IF;
   END IF;

   IF snapshot_id IS NULL THEN
     RETURN;
   END IF;

    IF v_system_list IS NOT NULL AND v_system_list.COUNT > 0
    THEN
      --dbms_output.put_line('Adding the following :');
      --debug_me(v_system_list);
       FOR i IN 1..v_system_list.COUNT
       LOOP
      --dbms_output.put_line('Adding the :'|| v_system_list(i).name  || v_system_list(i).value);
	select target_guid, target_name, target_type 
	 INTO l_member_guid, l_member_name, l_member_type
	FROM mgmt_targets 
	where target_name = v_system_list(i).name 
        and target_type = v_system_list(i).value;

	insert into MGMT_SYSTEM_CHANGES(ECM_SNAPSHOT_ID, SYSTEM_NAME,
	      SYSTEM_TYPE, MEMBER_NAME, MEMBER_TYPE, MODIFIED_BY) 
	VALUES (snapshot_id, l_system_name, l_system_type, 
		l_member_name, l_member_type, 'NONE');
       END LOOP;
    END IF;
   ECM_CT.POSTLOAD_CALLBACK(l_precall_arr, l_preload_out);

   update MGMT_SYSTEM_CHANGES set MODIFIED_BY=modified_by
   where ECM_SNAPSHOT_ID = snapshot_id;

 END IF;

END RECORD_CHANGES;

FUNCTION IS_TRACKED_SYSTEM(p_system IN SMP_EMD_NVPAIR)
RETURN BOOLEAN 
IS
PROP_VALUE VARCHAR2(100);
l_system_name MGMT_TARGETS.TARGET_NAME%TYPE;
l_system_type MGMT_TARGETS.TARGET_TYPE%TYPE;
BEGIN
   IF (p_system IS NOT NULL AND
       p_system.name IS NOT NULL AND
       p_system.value IS NOT NULL ) THEN
   BEGIN
     l_system_name := p_system.name;
     l_system_type := p_system.value;
   END;
   ELSE 
     RETURN FALSE;
   END IF;

--return TRUE;
 BEGIN
  SELECT P.PROPERTY_VALUE 
  INTO PROP_VALUE 
  FROM MGMT_TARGETS T, MGMT_TARGET_PROPERTIES P
  WHERE T.TARGET_NAME = l_system_name 
  AND   T.TARGET_TYPE = l_system_type 
  AND   T.TARGET_GUID = P.TARGET_GUID
  AND   P.PROPERTY_TYPE = 'INSTANCE'
  AND   P.PROPERTY_NAME = 'creationType';
 EXCEPTION WHEN NO_DATA_FOUND THEN
   RETURN FALSE;
 END;

  IF PROP_VALUE IS NOT NULL AND PROP_VALUE = 'auto'
  THEN 
   RETURN TRUE;
  ELSE 
   RETURN FALSE;
  END IF;
END;

PROCEDURE GET_SYSTEM_HISTORY_INFO(P_TARGET_NAME IN MGMT_TARGETS.TARGET_NAME%TYPE,
                                  P_TARGET_TYPE IN MGMT_TARGETS.TARGET_TYPE%TYPE,
                                  P_DURATION IN INTEGER,
                                  IS_AUTO_CREATED OUT INTEGER,
                                  CATEGORY_TYPE OUT VARCHAR2,
                                  CHANGES_COUNT OUT INTEGER)
IS 
CNT INTEGER;
L_TARGET_GUID MGMT_TARGETS.TARGET_GUID%TYPE;
BEGIN
    CNT :=0;
    IS_AUTO_CREATED := 0;

   BEGIN
    L_TARGET_GUID := MGMT_TARGET.GET_TARGET_GUID(P_TARGET_NAME,P_TARGET_TYPE);

    SELECT COUNT(*) INTO CNT
    FROM MGMT_TARGET_PROPERTIES 
    WHERE TARGET_GUID = L_TARGET_GUID
    AND    PROPERTY_TYPE = 'INSTANCE'
    AND    PROPERTY_NAME = 'creationType'
    AND    PROPERTY_VALUE = 'auto';

    IF CNT > 0 THEN
     IS_AUTO_CREATED := 1;

      SELECT category, COUNT(DISTINCT DELTAGUID)
      INTO CATEGORY_TYPE, CHANGES_COUNT
      FROM mgmt$ecm_config_history hist
      WHERE (DELTATIME >= TRUNC(SYSDATE - P_DURATION))
      AND  hist.target_name=P_TARGET_NAME
      AND hist.target_type=P_TARGET_TYPE
      GROUP BY target_type, snapshottype, category;
    END IF;

    EXCEPTION WHEN NO_DATA_FOUND THEN
       CATEGORY_TYPE := NULL;
       CHANGES_COUNT := 0;
    END;



END GET_SYSTEM_HISTORY_INFO;


END SYST_HIST; 
/
COMMIT;
show errors;