Rem
Rem $Header: target_folders_pkgbody.sql 15-jul-2005.14:14:29 jpyang Exp $
Rem
Rem target_folders_pkgbody.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      target_folders_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    jpyang      07/15/05 - increase col size 
Rem    rpatti      05/10/05 - rpatti_bug-4147142
Rem    rpatti      05/02/05 - Created
Rem

CREATE OR REPLACE PACKAGE BODY em_folders_ui AS

/*
 * set_default_metric_preferences - set the default metrics to show up in the
 *                                  summary for a given user
 *
 *   p_user_name     - the user being modified
 *   p_callback_type - type of modification; may be one of:
 *                       MGMT_USER.USER_CREATED_CALLBACK
 *                       MGMT_USER.USER_DROPPED_CALLBACK
 */
PROCEDURE set_default_subtab_preferences(p_user_name     IN VARCHAR2,
                                         p_callback_type IN NUMBER)
AS

  TYPE guid_list IS TABLE OF RAW(16);

  -- metrics for summary UI
  k_metrics CONSTANT guid_list := guid_list
    (HEXTORAW('0C71A1AFAC2D7199013837DA35522C08'),  -- Cpu Util %
     HEXTORAW('86821B5F0CE858D6E4A7F7390E88B73C'),  -- Memory Util %
     HEXTORAW('B2F6335B27BDD06BE55F69AC5A4FC2E7')); -- Total Disk IO

BEGIN
  IF p_callback_type = MGMT_USER.USER_CREATED_CALLBACK THEN
    FOR l_metric_guid_i IN 1..k_metrics.COUNT
    LOOP
      INSERT INTO mgmt_user_subtab_col_prefs
        (user_name, subtab_name, column_id_guid, column_type, display_order)
      VALUES
        (p_user_name, mgmt_global.g_host_target_type,
         k_metrics(l_metric_guid_i), 0, 4 + l_metric_guid_i);
    END LOOP;

    -- Hosts default generic columns
    INSERT INTO mgmt_user_subtab_col_prefs(user_name, subtab_name, 
                column_id, column_type, display_order)
         VALUES (p_user_name,
                 mgmt_global.g_host_target_type,
                 emd_pref.mgmt_user_pref_avail_col,
                 emd_pref.mgmt_user_pref_generic_col_tp, 1);
    INSERT INTO mgmt_user_subtab_col_prefs(user_name, subtab_name, 
                column_id, column_type, display_order)
         VALUES (p_user_name,
                 mgmt_global.g_host_target_type,
                 emd_pref.mgmt_user_pref_alerts_col,
                 emd_pref.mgmt_user_pref_generic_col_tp, 2);
    INSERT INTO mgmt_user_subtab_col_prefs(user_name, subtab_name, 
                column_id, column_type, display_order)
         VALUES (p_user_name,
                 mgmt_global.g_host_target_type,
                 emd_pref.mgmt_user_pref_pol_viol_col,
                 emd_pref.mgmt_user_pref_generic_col_tp, 3);
    INSERT INTO mgmt_user_subtab_col_prefs(user_name, subtab_name, 
                column_id, column_type, display_order)
         VALUES (p_user_name,
                 mgmt_global.g_host_target_type,
                 emd_pref.mgmt_user_pref_pol_comp_column,
                 emd_pref.mgmt_user_pref_generic_col_tp, 4);
  
    COMMIT;
  END IF;

END set_default_subtab_preferences;

/**
 This procedure takes as input a target type and returns a list of summary 
 metrics, non-hidden properties, and generic columns that a user may display
 in the sub-tab for the target type in question. The current choice setting
 is also returned.
 */
PROCEDURE getSubTabPreferrences (
              p_target_type_in     IN  VARCHAR2,
              p_user_prefs_cur_out OUT cursorType,
              p_def_prefs_cur_out  OUT cursorType)
IS
l_user_alias VARCHAR2(256);
l_pref_count NUMBER(3);
BEGIN

  -- find the alias for the current user name (from sys context)
  l_user_alias :=
    mgmt_user.get_subtab_prefs_alias(mgmt_user.get_current_em_user(),
                                     p_target_type_in);

  -- Is this a composite subtab or a simple target subtab?
  IF (p_target_type_in != emd_pref.mgmt_user_pref_all_targets) AND
     (p_target_type_in != emd_pref.mgmt_user_pref_groups) AND
     (p_target_type_in != emd_pref.mgmt_user_pref_systems) AND
     (p_target_type_in != emd_pref.mgmt_user_pref_services) AND
     (p_target_type_in != emd_pref.mgmt_user_pref_webapps) THEN
    -- Simple Target subtab

    SELECT COUNT(*) INTO l_pref_count
      FROM mgmt_user_subtab_col_prefs
     WHERE user_name = l_user_alias
       AND subtab_name = p_target_type_in;

    -- Does this target_type have preferences
    IF (l_pref_count > 0) THEN
      -- Returns all the preferred columns for a given alias and tgt_type 
      OPEN p_user_prefs_cur_out FOR
        WITH prefs AS
          (
            SELECT column_type, display_order, column_id, column_id_guid
              FROM mgmt_user_subtab_col_prefs
             WHERE user_name = l_user_alias
               AND subtab_name = p_target_type_in
          )
          SELECT DISTINCT column_id_guid, 
                 NULL AS "COLUMN_ID",
                 short_name AS "COLUMN_ID_DISP",
                 short_name_nlsid AS "COLUMN_ID_NLSID",
                 column_type, display_order
            FROM mgmt_metrics met, prefs
           WHERE met.target_type = p_target_type_in
             AND met.metric_type = 0
             AND met.is_for_summary = 1
             AND prefs.column_type = 0
             AND met.metric_guid = prefs.column_id_guid
           UNION
          SELECT DISTINCT NULL AS "COLUMN_ID_GUID",
                 column_id,
                 property_display_name AS "COLUMN_ID_DISP",
                 property_display_nlsid AS "COLUMN_ID_NLSID",
                 column_type, display_order
            FROM mgmt_target_prop_defs props, prefs
           WHERE props.target_type = p_target_type_in
             AND props.hidden_flag = 0
             AND props.credential_flag = 0
             AND prefs.column_type = 1
             AND props.property_name = prefs.column_id
           UNION
          SELECT NULL AS "COLUMN_ID_GUID",
                 column_id,
                 column_id AS "COLUMN_ID_DISP",
                 NULL AS "COLUMN_ID_NLSID",
                 column_type, display_order
            FROM prefs
           WHERE prefs.column_type = 2
           ORDER BY DISPLAY_ORDER;
    
      -- Returns all the unselected columns for a given alias and target
      OPEN p_def_prefs_cur_out FOR
        WITH props AS
          (
            SELECT *
              FROM mgmt_target_prop_defs
             WHERE target_type = p_target_type_in
               AND hidden_flag = 0
               AND credential_flag = 0
          ),
        metrics AS
          (
            SELECT *
              FROM mgmt_metrics
             WHERE metric_type = 0
               AND is_for_summary = 1
               AND target_type = p_target_type_in
          ),
        user_prefs AS
          (
            SELECT column_type, column_id, column_id_guid
              FROM mgmt_user_subtab_col_prefs
             WHERE user_name = l_user_alias
               AND subtab_name = p_target_type_in
          ),
        all_prefs AS
          (
            SELECT 2 AS "COLUMN_TYPE", column_id, NULL AS "COLUMN_ID_GUID"
              FROM mgmt_user_subtab_col_prefs
             WHERE user_name = mgmt_user.wildcard_user_name_alias
               AND subtab_name = p_target_type_in
               AND column_type = 2
             UNION
            SELECT DISTINCT 1 AS "COLUMN_TYPE", property_name AS "COLUMN_ID",
                   NULL AS "COLUMN_ID_GUID"
              FROM props
             UNION
            SELECT DISTINCT 0 AS "COLUMN_TYPE", NULL AS "COLUMN_ID",
                   metric_guid AS "COLUMN_ID_GUID"
              FROM metrics
          ),
        result_prefs AS
          (
            SELECT * FROM all_prefs
             MINUS
            SELECT * FROM user_prefs
          )
        SELECT column_id_guid, column_id, column_id AS "COLUMN_ID_DISP",
               NULL AS "COLUMN_ID_NLSID", column_type
          FROM result_prefs
         WHERE column_type = 2
         UNION
        SELECT column_id_guid, column_id,
               property_display_name AS "COLUMN_ID_DISP",
               property_display_nlsid AS "COLUMN_ID_NLSID",
               column_type
          FROM props, result_prefs
         WHERE props.property_name = column_id
         UNION
        SELECT column_id_guid, column_id, short_name AS "COLUMN_ID_DISP",
               short_name_nlsid AS "COLUMN_ID_NLSID", column_type
          FROM metrics, result_prefs
         WHERE metric_guid = column_id_guid;

    ELSE -- Target type does not have def prefs (support older integ. folders)

      OPEN p_user_prefs_cur_out FOR
        SELECT column_id_guid, column_id, column_id_disp,
               column_id_nlsid, column_type, ROWNUM AS "DISPLAY_ORDER"
      FROM
      (
        SELECT NULL AS "COLUMN_ID_GUID",
               emd_pref.MGMT_USER_PREF_AVAIL_COL AS "COLUMN_ID",
               emd_pref.MGMT_USER_PREF_AVAIL_COL AS "COLUMN_ID_DISP",
               NULL AS "COLUMN_ID_NLSID",
               2 AS "COLUMN_TYPE", 0 AS "COL_ORDER"
          FROM dual
         UNION ALL
        SELECT NULL AS "COLUMN_ID_GUID",
               emd_pref.MGMT_USER_PREF_ALERTS_COL AS "COLUMN_ID",
               emd_pref.MGMT_USER_PREF_ALERTS_COL AS "COLUMN_ID_DISP",
               NULL AS "COLUMN_ID_NLSID",
               2 AS "COLUMN_TYPE", 0 AS "COL_ORDER"
          FROM dual
         UNION ALL
        SELECT DISTINCT metric_guid AS "COLUMN_ID_GUID",
               NULL AS "COLUMN_ID",
               short_name AS "COLUMN_ID_DISP",
               short_name_nlsid AS "COLUMN_ID_NLSID",
               0 AS "COLUMN_TYPE", 2 AS "COL_ORDER"
          FROM mgmt_metrics met 
         WHERE met.metric_type = 0
           AND met.is_for_summary = 1
           AND met.target_type = p_target_type_in
         ORDER BY "COL_ORDER", "COLUMN_ID_DISP"
       );

      -- Open a cursor with no data...
      OPEN p_def_prefs_cur_out FOR
        SELECT NULL AS "COLUMN_ID_GUID", NULL AS "COLUMN_ID", 
               NULL AS "COLUMN_ID_DISP", NULL AS "COLUMN_ID_NLSID", 
               NULL AS "COLUMN_TYPE", NULL AS "DISPLAY_ORDER"
          FROM dual
         WHERE ROWNUM < 1;

    END IF;
    
  ELSE -- Composite Target (i.e. All Targets, Groups)
    -- This cursor returns all the preferred columns for a given alias
    -- NOTE: Composite folders only have global props and generic columns (i.e.
    -- availability, alerts, etc.)
    OPEN p_user_prefs_cur_out FOR
      WITH prefs AS
        (
          SELECT column_type, column_id, display_order
            FROM mgmt_user_subtab_col_prefs
           WHERE user_name = l_user_alias
             AND subtab_name = p_target_type_in
        )
        SELECT DISTINCT NULL AS "COLUMN_ID_GUID",
               column_id,
               property_display_name AS "COLUMN_ID_DISP",
               property_display_nlsid AS "COLUMN_ID_NLSID",
               column_type, display_order
          FROM mgmt_all_target_props props, prefs
         WHERE props.hidden_flag = 0
           AND props.credential_flag = 0
           AND props.property_name = prefs.column_id
           AND prefs.column_type = 1
           AND prefs.column_id = props.property_name
         UNION
        SELECT NULL AS "COLUMN_ID_GUID",
               column_id,
               NULL AS "COLUMN_ID_DISP",
               NULL AS "COLUMN_ID_NLSID",
               column_type, display_order
          FROM prefs
         WHERE column_type = 2
         ORDER BY display_order;

    -- Returns all the unselected columns for a given alias and composite folder
    OPEN p_def_prefs_cur_out FOR
      WITH props AS
        (
          SELECT *
            FROM mgmt_all_target_props
           WHERE hidden_flag = 0
             AND credential_flag = 0
        ),
      user_prefs AS
        (
          SELECT column_id, column_type
            FROM mgmt_user_subtab_col_prefs
           WHERE user_name = l_user_alias
             AND subtab_name = p_target_type_in
        ),
      def_prefs AS
        (
          SELECT property_name AS "COLUMN_ID", 1 AS "COLUMN_TYPE"
            FROM props
           UNION
          SELECT column_id, column_type
            FROM mgmt_user_subtab_col_prefs
           WHERE user_name = mgmt_user.wildcard_user_name_alias
             AND subtab_name = p_target_type_in
        ),
      remain_prefs AS
        (
           SELECT * FROM def_prefs
            MINUS
           SELECT * FROM user_prefs
        )
      SELECT DISTINCT NULL AS "COLUMN_ID_GUID",
             column_id,
             property_display_name AS "COLUMN_ID_DISP",
             property_display_nlsid AS "COLUMN_ID_NLSID",
             column_type
        FROM props, remain_prefs prefs
       WHERE property_name = column_id
         AND column_type = 1
       UNION
      SELECT NULL AS "COLUMN_ID_GUID",
             column_id,
             NULL AS "COLUMN_ID_DISP",
             NULL AS "COLUMN_ID_NLSID",
             column_type
        FROM remain_prefs
       WHERE column_type = 2;
  END IF;

END getSubTabPreferrences;

/**
  Insert the preferences of the user - for metrics to be shown 
  in the dense UI for a given type
 */
PROCEDURE saveSummaryMetricsInfo (
              p_target_type_in     IN  VARCHAR2,
              p_sel_metrics_in     IN  MGMT_SUBTAB_PREF_ARRAY)
IS
  l_user_name VARCHAR2(256);

BEGIN

  -- get the current user name from sys context
  l_user_name := mgmt_user.get_current_em_user();

  -- delete any old preferences for the user
  DELETE FROM mgmt_user_subtab_col_prefs
    WHERE user_name = l_user_name AND
          subtab_name = p_target_type_in;

  -- insert the new preferences
  INSERT INTO mgmt_user_subtab_col_prefs
    (user_name, subtab_name, column_id_guid, column_id, column_type, 
     display_order)
    SELECT l_user_name, p_target_type_in, col.column_id_guid, col.column_id,
           col.column_type, col.display_order
      FROM TABLE(CAST(p_sel_metrics_in AS MGMT_SUBTAB_PREF_ARRAY)) col;

END saveSummaryMetricsInfo;

END em_folders_ui;
/

show errors;