Rem drv: <migrate type="data_upgrade" version="10.2.0.0" />
Rem
Rem $Header: eml_data_upgrade.sql 23-aug-2005.11:51:05 ychan Exp $
Rem
Rem eml_data_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. 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    ychan      08/23/05 - ychan_bug-4129031
Rem    ychan      08/19/05 - Created
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

    -- This code logic is being added due to the "unusual" branch dependencies
    -- that we currently have in 10.2. Remove after branching!
    -- Find out if this is 10.2 core or an older core...
    SELECT count(*) INTO cnt
      FROM user_tables
     WHERE table_name LIKE 'MGMT_USER_TYPE_METRIC_PREFS';

    IF (cnt = 1) THEN -- Old core
      FOR l_metric_guid_i IN 1..k_metrics.COUNT
      LOOP

        ----
        -- Best effort attempt to insert preferences.
        ----
        BEGIN
          INSERT INTO mgmt_user_type_metric_prefs
            (user_name, target_type,
             metric_guid, display_order)
          VALUES
            (p_user_name, mgmt_global.g_database_target_type,
             k_metrics(l_metric_guid_i), l_metric_guid_i);
        EXCEPTION
          WHEN OTHERS THEN NULL;
        END;

      END LOOP;
    ELSE  -- New core
      -- 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;
    END IF;

    COMMIT;

  END IF;

  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;