Rem drv: <migrate type="data_upgrade" version="10.2.0.1"/>
Rem
Rem $Header: preferences_data_upgrade.sql 29-aug-2005.02:31:08 vmotamar Exp $
Rem
Rem preferences_data_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      preferences_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    vmotamar    08/29/05 - Modifying header
Rem    paachary    08/25/05 - paachary_upgrdbgs
Rem    paachary    08/24/05 - Created
Rem                           Refer Bug 4567891.
Rem


DECLARE
     l_count  NUMBER DEFAULT 0;
     TYPE l_column_id_tab IS TABLE OF mgmt_user_subtab_col_prefs.column_id%TYPE;
     l_column_id l_column_id_tab := l_column_id_tab();
BEGIN
    -- Introduce a Members Column for Systems Tab.
    -- Check if the record exists in mgmt_user_subtab_col_prefs.
    -- If it exists, then do nothing,
    -- else insert a record in mgmt_user_subtab_col_prefs for the Members Column.
    -- This step is being done for those customers who already have upgraded to Beta.
    -- Beta version does not have Members column for Systems Subtab.
    SELECT count(*)
    INTO   l_count
    FROM   mgmt_user_subtab_col_prefs
    WHERE  user_name = mgmt_user.wildcard_user_name_alias 
       AND subtab_name = emd_pref.mgmt_user_pref_systems
       AND column_id = emd_pref.mgmt_user_pref_grp_mem_col
       AND column_type = emd_pref.mgmt_user_pref_generic_col_tp;

    IF (l_count = 0) THEN
      INSERT INTO mgmt_user_subtab_col_prefs(user_name, subtab_name,
              column_id, column_type, display_order)
       VALUES (mgmt_user.wildcard_user_name_alias,
               emd_pref.mgmt_user_pref_systems,
               emd_pref.mgmt_user_pref_grp_mem_col,
               emd_pref.mgmt_user_pref_generic_col_tp, 3);
        COMMIT;    
    END IF;

    -- Check if there is an availability column for Groups Tab
    -- This should not be there.
    -- If its there, then remove it and re-arrange the display_order for other columns.
    -- This is being done as Beta version has Availability column for Groups subtab.
    -- This should not be there. This step is for customers who have already upgraded to Beta version.

    SELECT count(*)
    INTO   l_count
    FROM   mgmt_user_subtab_col_prefs
    WHERE  user_name = mgmt_user.wildcard_user_name_alias 
       AND subtab_name = emd_pref.mgmt_user_pref_groups
       AND column_id = emd_pref.mgmt_user_pref_avail_col
       AND column_type = emd_pref.mgmt_user_pref_generic_col_tp;

    IF (l_count > 0) THEN
        DELETE mgmt_user_subtab_col_prefs
        WHERE  user_name = mgmt_user.wildcard_user_name_alias 
           AND subtab_name = emd_pref.mgmt_user_pref_groups
           AND column_id = emd_pref.mgmt_user_pref_avail_col
           AND column_type = emd_pref.mgmt_user_pref_generic_col_tp;
        
        l_column_id.extend;
        l_column_id(1) :=   emd_pref.mgmt_user_pref_alerts_col; 
        l_column_id.extend;
        l_column_id(2) :=   emd_pref.mgmt_user_pref_pol_viol_col;
        l_column_id.extend;
        l_column_id(3) :=   emd_pref.mgmt_user_pref_grp_mem_col;

        FOR l_col_len IN 1..l_column_id.COUNT
        LOOP
            UPDATE mgmt_user_subtab_col_prefs
            SET    display_order = l_col_len
            WHERE  user_name = mgmt_user.wildcard_user_name_alias 
               AND subtab_name = emd_pref.mgmt_user_pref_groups
               AND column_id = l_column_id(l_col_len)
               AND column_type = emd_pref.mgmt_user_pref_generic_col_tp;
        END LOOP;
        COMMIT;
    END IF;
END;
/