Rem drv: <create type="pkgbodys" pos="policy/policy_pkgbody.sql+"/>
Rem
Rem $Header: 
Rem
Rem credentials_ui_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      credentials_ui_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      Preferred Credentials PL/SQL routines
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    nqureshi    07/04/07 - support for delete pdp setting cli
Rem    nqureshi    05/23/07 - backporting procedures
Rem    nqureshi    04/21/07 - pdp backport 
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    jpyang      05/18/05 - get_creds_summary should use current user not 
Rem                           SYSMAN 
Rem    kmanicka    03/04/05 - move to EMDW_LOG from dbms_output 
Rem    sjconnol    07/27/04 - sjconnol_bug-3785403
Rem    sjconnol    07/27/03 - Creation
Rem

CREATE OR REPLACE PACKAGE BODY MGMT_CREDENTIALS_UI AS

--
-- Name: get_creds_summary
--
-- Package: MGMT_CREDENTIALS_UI
--
-- Purpose: 
--  Get pref creds summary info for pref creds home page
--
PROCEDURE get_creds_summary (targettype OUT MGMT_CREDS_LOUTPUT, numtargets OUT MGMT_CREDS_SOUTPUT, defset OUT MGMT_CREDS_SOUTPUT, numset OUT MGMT_CREDS_SOUTPUT) IS
  hascreds NUMBER;
  i BINARY_INTEGER;
  BEGIN
  targettype := MGMT_CREDS_LOUTPUT();
  numtargets := MGMT_CREDS_SOUTPUT();
  defset := MGMT_CREDS_SOUTPUT();
  numset := MGMT_CREDS_SOUTPUT();
  i := 0;
  FOR res IN (SELECT unique(target_type) from mgmt_targets)
  LOOP
  SELECT count(DISTINCT set_name) INTO hascreds FROM MGMT_CREDENTIAL_SETS WHERE target_type=res.target_type AND set_usage != 'MONITORING' AND set_context_type != 'CONTAINER';
  IF (hascreds > 0) THEN
    IF EMDW_LOG.p_is_debug_set THEN
        EMDW_LOG.debug(res.target_type,EM_CREDENTIAL.MODULE_NAME);
    END IF;
    i := i + 1;
    targettype.EXTEND(1);
    numtargets.EXTEND(1);
    defset.EXTEND(1);
    numset.EXTEND(1);
    targettype(i) := res.target_type;    
    select count(target_name) INTO numtargets(i) from mgmt_targets where target_type = res.target_type;
    select count(credential_set_name) INTO defset(i) from mgmt_enterprise_credentials where target_type = res.target_type and user_name = mgmt_user.get_current_em_user();
    select count(unique(mt.target_guid)) INTO numset(i) from mgmt_target_credentials mt, mgmt_targets t where mt.target_guid = t.target_guid and t.target_type = res.target_type and mt.user_name = mgmt_user.get_current_em_user();
  END IF;
  END LOOP;
END get_creds_summary;

-- deletes the PDP settings 
PROCEDURE delete_pdp_settings(p_setting_guid_in IN MGMT_USER_GUID_ARRAY) 
IS
l_op_guids MGMT_USER_GUID_ARRAY;
BEGIN

    SELECT operation_guid BULK COLLECT INTO l_op_guids
    FROM  MGMT_UPDATE_PDP_DATA_MAP
    WHERE setting_guid IN 
			( SELECT * FROM TABLE(CAST(p_setting_guid_in AS MGMT_USER_GUID_ARRAY)));

    FORALL i in 1..p_setting_guid_in.LAST
       DELETE FROM MGMT_PDP_SETTINGS 
       WHERE setting_guid = p_setting_guid_in(i);
    
    FORALL i in 1..p_setting_guid_in.LAST       
       DELETE FROM MGMT_PDP_SETTING_VALUES 
       WHERE setting_guid =p_setting_guid_in(i);
   
    IF l_op_guids IS NULL OR l_op_guids.COUNT = 0 THEN
        RETURN;
    END IF;
    
    FORALL i in 1..l_op_guids.LAST
	DELETE FROM MGMT_UPDATE_PDP_DATA_COPY
	WHERE operation_guid = l_op_guids(i);

    FORALL i in 1..l_op_guids.LAST
	DELETE FROM MGMT_UPDATE_PDP_DATA_MAP
	WHERE operation_guid = l_op_guids(i);

    FOR i in 1..l_op_guids.COUNT
    LOOP
        BEGIN
            MGMT_TARGET_UPDATE.delete_operation_data(l_op_guids(i));
        EXCEPTION 
            -- user may have deleteed the execution from other sources so cat the exception and ignore
            WHEN OTHERS THEN
            NULL;   
       END;
    END LOOP;  
    
END delete_pdp_settings;

-- create the PDP settings 
PROCEDURE create_pdp_settings(p_setting_name_in IN VARCHAR ,
                              p_setting_type_in IN VARCHAR ,
                              p_setting_values_in IN MGMT_PDP_ACTUAL_SETTING_ARRAY,
                              p_setting_status_in IN NUMBER,
                              p_error_code_out OUT VARCHAR) 
IS
l_count NUMBER;
l_setting_guid MGMT_PDP_SETTINGS.SETTING_GUID%TYPE;
BEGIN


    SELECT  COUNT(1) INTO l_count 
    FROM MGMT_PDP_SETTINGS
    WHERE SETTING_NAME= p_setting_name_in
    AND CREATED_BY = MGMT_USER.get_current_em_user ;

    IF l_count != 0 THEN
        p_error_code_out := 'SETTING_ALREADY_EXIST';
        return;
    END IF;
    
    l_setting_guid := sys_guid();
    INSERT INTO MGMT_PDP_SETTINGS (SETTING_GUID,SETTING_NAME,PDP_TYPE,CREATED_BY,LAST_MODIFIED,IS_ENABLED) 
                                   values(l_setting_guid,p_setting_name_in,p_setting_type_in,
                                   MGMT_USER.get_current_em_user,sysdate(),p_setting_status_in );
                                   
    IF p_setting_status_in = 1 THEN
        FOR i IN 1..p_setting_values_in.COUNT
           LOOP
              INSERT INTO MGMT_PDP_SETTING_VALUES( setting_guid,value_name,value ) 
                          VALUES (l_setting_guid,
                          p_setting_values_in(i).pdp_value_name,
                          p_setting_values_in(i).pdp_value);
           END LOOP;
        UPDATE MGMT_PDP_SETTINGS SET last_modified=sysdate 
        WHERE setting_guid=l_setting_guid;
   END IF;

   END;

 PROCEDURE get_flattened_aggr_targets
  ( p_target_list IN OUT MGMT_JOB_TARGET_list )
  IS
  BEGIN
     MGMT_JOB_UI.get_flattened_aggr_targets(p_target_list,MGMT_GLOBAL.G_HOST_TARGET_TYPE);
  END;



 -- 
  -- PROCEDURE apply_pdp 
  -- 
  -- PURPOSE:
  --   procedure to apply a pdp to a specified destination list. 
  -- 
  -- Convenience procedure to apply a pdp to a specified destination list. 
  -- 
  -- p_pdp_guid The source template name 
  -- p_target_type The target type 
  -- p_destination_list The set of destination targets 
  -- p_copy_common_only_flags An array that is as large as the number of 
  --   destination targets. For each target, it specifies whether to  copy the common 
  --   thresholds only (1) or all the thresholds (0) from the source. 
  -- p_update_master_agents This is a flag that indicates how to deal with multi-agent 
  --   targets in the destination list. If set to true (1), the master agent as well as all the 
  --   standby agents monitoring the multi-agent target are updated. If set to false (0), 
  --   only the standby agents are updated, but not the master. 
  -- p_schedule An optional schedule 
  -- 
  PROCEDURE apply_pdp(     
                           p_setting_guid_in IN RAW,
                           p_setting_values_in IN MGMT_PDP_ACTUAL_SETTING_ARRAY,
                           p_setting_status_in IN NUMBER,
                           p_pdp_type_in IN VARCHAR2,
                           p_destination_list IN MGMT_JOB_TARGET_LIST,
                           p_update_master_agents IN NUMBER DEFAULT 1
                           )
  IS
    l_data_set_guid RAW(16);
    l_operation_guid RAW(16);
    l_data_guids MGMT_USER_GUID_ARRAY;
    l_destination_list MGMT_JOB_TARGET_LIST := p_destination_list; 
  BEGIN

    IF ( (p_destination_list IS NULL) OR (p_destination_list.COUNT <= 0) ) THEN
      raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
          'Destination target list to apply_pdp is empty.');
    ELSE
        
        get_flattened_aggr_targets( l_destination_list);

      l_data_guids := MGMT_USER_GUID_ARRAY();


      -- Create template_copy and get its associated data_set_guid
      l_data_set_guid := MGMT_TARGET_UPDATE.submit_pdp_data(
                                                p_setting_guid_in,
                                                NULL,    -- target_name
                                                MGMT_GLOBAL.G_HOST_TARGET_TYPE, --target_type
                                                p_setting_status_in,
                                                p_pdp_type_in,
                                                p_setting_values_in);

      l_data_guids := MGMT_USER_GUID_ARRAY();
      l_data_guids.extend(1);
      l_data_guids(1) := l_data_set_guid;


      l_operation_guid := MGMT_TARGET_UPDATE.submit_update_operation(l_data_guids,
                                                    l_destination_list,
                                                    p_update_master_agents,
                                                    NULL);


    INSERT INTO MGMT_UPDATE_PDP_DATA_MAP 
          (operation_guid,setting_guid,applied_by)
    VALUES (l_operation_guid,p_setting_guid_in,mgmt_user.get_current_em_user());
    
    -- now insert into the copy table to get the details later
    INSERT INTO MGMT_UPDATE_PDP_DATA_COPY
       (operation_guid, setting_name, SETTING_VALUE)
    VALUES (l_operation_guid, 'SETTING_STATUS',
            p_setting_status_in );
    INSERT INTO MGMT_UPDATE_PDP_DATA_COPY
       (operation_guid, setting_name, SETTING_VALUE)
    VALUES (l_operation_guid, 'SETTING_TYPE',
            p_pdp_type_in );
    
    FOR j in 1..p_setting_values_in.count LOOP
        INSERT INTO MGMT_UPDATE_PDP_DATA_COPY
           (operation_guid, setting_name, SETTING_VALUE)
        VALUES (l_operation_guid, p_setting_values_in(j).PDP_VALUE_NAME,
                p_setting_values_in(j).PDP_VALUE );
    END LOOP;
                                                    
    END IF;

  END apply_pdp;


PROCEDURE REMOVE_PDP_SETTING(p_target_name_in IN VARCHAR2,p_target_type_in IN VARCHAR2 ) 
IS
  l_target_guid  mgmt_targets.target_guid%TYPE;
BEGIN
  l_target_guid := mgmt_target.get_target_guid(p_target_name_in,p_target_type_in);
  
  DELETE FROM MGMT_PDP_HOST_SETTING 
  WHERE  TARGET_GUID=l_target_guid;

END REMOVE_PDP_SETTING;

PROCEDURE INSERT_PDP_SETTING( p_target_name_in IN VARCHAR2,
            p_target_type_in IN VARCHAR2 ,
            p_setting_type_in IN VARCHAR2 ,
            p_setting_values_in IN SMP_EMD_NVPAIR_ARRAY )
IS 
  l_target_guid  mgmt_targets.target_guid%TYPE;
BEGIN
  l_target_guid := mgmt_target.get_target_guid(p_target_name_in,p_target_type_in);
  REMOVE_PDP_SETTING(p_target_name_in,p_target_type_in);

  FOR i IN 1..p_setting_values_in.COUNT
  LOOP
    INSERT INTO MGMT_PDP_HOST_SETTING(TARGET_GUID, PDP_TYPE
                ,PDP_SETTING_NAME,PDP_SETTING_VALUE )		
                VALUES (l_target_guid,p_setting_type_in,
                p_setting_values_in(i).NAME,
                p_setting_values_in(i).VALUE) ;

   END LOOP;

END INSERT_PDP_SETTING;

-- return pdpd settings information for viewPdpSetings page
PROCEDURE GET_PDP_SETTINGS( p_setting_guid_in IN RAW,
                            p_settings_cur OUT  CURSOR_TYPE )
IS

BEGIN
     IF p_setting_guid_in IS NOT NULL THEN
         OPEN p_settings_cur FOR
                         SELECT s.setting_guid, s.setting_name , v.value_name, v.value, s.pdp_type,s.created_by,
                         s.last_modified , s.is_enabled , nvl(ap.apply_pending_count,0) as apply_pending_count
                         FROM   MGMT_PDP_SETTINGS s, MGMT_PDP_SETTING_VALUES v,
                         (SELECT pm.setting_guid, count(od.DESTINATION_TARGET_GUID) as apply_pending_count
                         FROM  MGMT_UPDATE_OPERATIONS_DETAILS od,
                         MGMT_UPDATE_PDP_DATA_MAP pm
                         WHERE  pm.operation_guid = od.operation_guid
                         AND    od.execution_status IN(MGMT_JOB_ENGINE.SCHEDULED_STATUS,MGMT_JOB_ENGINE.EXECUTING_STATUS )
                         GROUP BY pm.setting_guid) ap
                         WHERE  s.setting_guid = ap.setting_guid(+)
                         AND  s.setting_guid = v.setting_guid(+)
                         AND    s.setting_guid = p_setting_guid_in;
         ELSE
            OPEN p_settings_cur FOR
                         SELECT s.setting_guid, s.setting_name , v.value_name, v.value, s.pdp_type,s.created_by,
                         s.last_modified , s.is_enabled , nvl(ap.apply_pending_count,0) as apply_pending_count
                         FROM   MGMT_PDP_SETTINGS s, MGMT_PDP_SETTING_VALUES v,
                         (SELECT pm.setting_guid, count(od.DESTINATION_TARGET_GUID) as apply_pending_count
                         FROM  MGMT_UPDATE_OPERATIONS_DETAILS od,
                         MGMT_UPDATE_PDP_DATA_MAP pm
                         WHERE  pm.operation_guid = od.operation_guid
                         AND    od.execution_status IN(MGMT_JOB_ENGINE.SCHEDULED_STATUS,MGMT_JOB_ENGINE.EXECUTING_STATUS )
                         GROUP BY pm.setting_guid) ap
                         WHERE  s.setting_guid = ap.setting_guid(+)
                         AND  s.setting_guid = v.setting_guid(+);


          END IF;

END GET_PDP_SETTINGS;

PROCEDURE VLDTE_PDP_STNG_NMES_AND_SUBMIT( p_setting_names_in IN MGMT_SHORT_STRING_ARRAY,
                                          p_invalid_settings_cur OUT  CURSOR_TYPE,
                                          p_has_error_out OUT NUMBER)
IS 
    l_invalid_setting_cnt  INTEGER :=0;
    l_invalid_settings MGMT_SHORT_STRING_ARRAY;
    l_user_guids MGMT_USER_GUID_ARRAY;
BEGIN
    IF p_setting_names_in IS NOT NULL THEN
        SELECT column_value
        BULK COLLECT INTO l_invalid_settings
        FROM   TABLE(CAST(p_setting_names_in AS MGMT_SHORT_STRING_ARRAY)) s
        WHERE  NOT EXISTS (SELECT 1 FROM MGMT_PDP_SETTINGS 
                           WHERE  setting_name = s.column_value
                           AND    CREATED_BY = mgmt_user.get_current_em_user());

        IF l_invalid_settings.COUNT = 0 THEN
            SELECT SETTING_GUID
            BULK COLLECT INTO l_user_guids
            FROM   MGMT_PDP_SETTINGS s
            WHERE  EXISTS (SELECT 1
                          FROM TABLE(CAST(p_setting_names_in AS MGMT_SHORT_STRING_ARRAY))
                          WHERE  column_value = s.setting_name)
            AND   s.CREATED_BY = mgmt_user.get_current_em_user();
            delete_pdp_settings(l_user_guids);
        ELSE
          p_has_error_out :=1;
          OPEN p_invalid_settings_cur FOR
            SELECT * FROM TABLE(l_invalid_settings);
        END IF;
    END IF;

END VLDTE_PDP_STNG_NMES_AND_SUBMIT;

END MGMT_CREDENTIALS_UI;
/

SHOW ERRORS;