Rem drv: <migrate type="data_upgrade" version="10.2.0.2" />
Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/v102020/eml/eml_data_upgrade.sql /st_emdbsa_11.2/1 2008/11/11 10:16:50 yemeng Exp $
Rem
Rem eml_data_upgrade.sql
Rem
Rem Copyright (c) 2005, 2008, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      eml_data_upgrade.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       yemeng   07/28/08 - 
Rem

Rem Now add the generic columns that would have been added in 
Rem eml_post_creation for a regular install and move the old prefs over.

DECLARE
  WILDCARD_USER_NAME_ALIAS        CONSTANT VARCHAR2(2) := '*';
  MGMT_USER_PREF_METRIC_COL_TP    CONSTANT NUMBER(1) := 0;
  MGMT_USER_PREF_PROPERTY_COL_TP  CONSTANT NUMBER(1) := 1;
  MGMT_USER_PREF_GENERIC_COL_TP   CONSTANT NUMBER(1) := 2;

  MGMT_USER_PREF_AVAIL_COL         CONSTANT VARCHAR2(20) := 'availability';
  MGMT_USER_PREF_ALERTS_COL        CONSTANT VARCHAR2(20) := 'alerts';
  MGMT_USER_PREF_POL_VIOL_COL      CONSTANT VARCHAR2(20) := 'policyviolation';
  MGMT_USER_PREF_POL_COMP_COLUMN   CONSTANT VARCHAR2(20) := 'policycompliance';

  PROCEDURE set_db_default_metric_prefs(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('F63470E537948FA4150647777C1C6F6C'),  -- Sessions: CPU
      HEXTORAW('41F56D5BA13F1504351F427330294F67'),   -- Sessions: I/O
      HEXTORAW('C2AB9BEC83DF961752257C9C5476427C'),   -- Sessions: Other
      HEXTORAW('CADFA1BF3907CA34D1CD1DE4D3E46B9B')    -- Instance CPU (%)
    );

  cnt NUMBER(2);

  BEGIN
  IF p_callback_type = MGMT_USER.USER_CREATED_CALLBACK THEN

      -- Databases default generic columns
      EXECUTE IMMEDIATE
      'DECLARE
         TYPE guid_list IS TABLE OF RAW(16);
         k_metrics CONSTANT guid_list := guid_list
           ( HEXTORAW(''F63470E537948FA4150647777C1C6F6C''),  -- Sessions: CPU
             HEXTORAW(''41F56D5BA13F1504351F427330294F67''),   -- Sessions: I/O
             HEXTORAW(''C2AB9BEC83DF961752257C9C5476427C''),   -- Sessions: Other
             HEXTORAW(''CADFA1BF3907CA34D1CD1DE4D3E46B9B'')    -- Instance CPU (%)
           );
       BEGIN
       INSERT INTO mgmt_user_subtab_col_prefs(user_name, subtab_name,
                  column_id, column_type, display_order)
           VALUES ( :username_i,
                   mgmt_global.g_database_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 ( :username_i,
                   mgmt_global.g_database_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 ( :username_i,
                   mgmt_global.g_database_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 ( :username_i,
                   mgmt_global.g_database_target_type,
                   emd_pref.mgmt_user_pref_pol_comp_column,
                   emd_pref.mgmt_user_pref_generic_col_tp, 4);
        INSERT INTO mgmt_user_subtab_col_prefs(user_name, subtab_name,
                  column_id, column_type, display_order)
           VALUES ( :username_i,
                   mgmt_global.g_database_target_type,
                   ''DBVersion'',
                   emd_pref.mgmt_user_pref_property_col_tp, 5);

       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
           ( :username_i, mgmt_global.g_database_target_type,
           k_metrics(l_metric_guid_i), 0, 5 + l_metric_guid_i);
       END LOOP;
       END;' USING p_user_name;

    COMMIT;

  END IF;

  EXCEPTION
 	  WHEN OTHERS THEN NULL;

  END set_db_default_metric_prefs;

BEGIN
  -- Database generic default columns
  set_db_default_metric_prefs(
    WILDCARD_USER_NAME_ALIAS,
    mgmt_user.user_created_callback);
END;
/

show errors;