Rem drv: <migrate type="data_upgrade" version="10.1.0.3"/> 
Rem
Rem $Header: license_data_upgrade.sql 01-aug-2007.03:16:15 paachary Exp $
Rem
Rem license_data_upgrade.sql
Rem
Rem Copyright (c) 2004, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      license_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    paachary    10/24/06 - fixing bug 5612799 as it is causing ora errors (ORA-01400) in rdbms upgrade.
Rem    ktlaw       01/13/05 - add repmgr header 
Rem    yaofeng     04/23/04 - yaofeng_work0423 
Rem    yaofeng     04/23/04 - Created
Rem

DECLARE
    l_count NUMBER := 0;
BEGIN

/*  
    Fixing bug 5612799 as it causes ORA-01400 in RDBMS Upgrade
*/
    SELECT count(*)
    INTO  l_count
    FROM all_tab_columns
    WHERE table_name = 'MGMT_LICENSE_DEFINITIONS'
      AND column_name = 'PACK_ABBR';
    
    IF (l_count > 0) 
    THEN
        EXECUTE IMMEDIATE 'ALTER TABLE mgmt_license_definitions DROP CONSTRAINT mgmt_license_definitions_unq CASCADE DROP INDEX';
        EXECUTE IMMEDIATE 'ALTER TABLE mgmt_license_definitions MODIFY (pack_abbr NULL)';
    END IF;

    INSERT INTO MGMT_LICENSE_DEFINITIONS
      (pack_label, target_type, pack_display_label, pack_label_nlsid, pack_description, pack_description_nlsid)
      VALUES
        ('db_diag', 'oracle_database', 'Database Diagnostics Pack', 'db_diag_nlsid', ' ', 'db_diag_description_nlsid');

    INSERT INTO MGMT_LICENSE_DEFINITIONS
      (pack_label, target_type, pack_display_label, pack_label_nlsid, pack_description, pack_description_nlsid)
      VALUES
        ('db_tuning', 'oracle_database', 'Database Tuning Pack', 'db_tuning_nlsid', ' ', 'db_tuning_description_nlsid');

    INSERT INTO MGMT_LICENSE_DEFINITIONS
      (pack_label, target_type, pack_display_label, pack_label_nlsid, pack_description, pack_description_nlsid)
      VALUES
        ('db_config', 'oracle_database', 'Database Configuration Pack', 'db_config_nlsid', ' ', 'db_config_description_nlsid');

    INSERT INTO MGMT_LICENSE_DEFINITIONS
      (pack_label, target_type, pack_display_label, pack_label_nlsid, pack_description, pack_description_nlsid)
      VALUES
        ('as_diag', 'oracle_ias', 'Application Server Diagnostics Pack', 'as_diag_nlsid', ' ', 'as_diag_description_nlsid');

    INSERT INTO MGMT_LICENSE_DEFINITIONS
      (pack_label, target_type, pack_display_label, pack_label_nlsid, pack_description, pack_description_nlsid)
      VALUES
        ('as_config', 'oracle_ias', 'Application Server Configuration Pack', 'db_config_nlsid', ' ', 'db_config_description_nlsid');
    COMMIT;

    INSERT INTO MGMT_LICENSABLE_TARGET_TYPES
      (pack_target_type, target_type) VALUES ('oracle_database', 'oracle_database');

    INSERT INTO MGMT_LICENSABLE_TARGET_TYPES
      (pack_target_type, target_type) VALUES ('oracle_database', 'host');

    INSERT INTO MGMT_LICENSABLE_TARGET_TYPES
      (pack_target_type, target_type) VALUES ('oracle_ias', 'oracle_ias');

    INSERT INTO MGMT_LICENSABLE_TARGET_TYPES
      (pack_target_type, target_type) VALUES ('oracle_ias', 'oracle_apache');

    INSERT INTO MGMT_LICENSABLE_TARGET_TYPES
      (pack_target_type, target_type) VALUES ('oracle_ias', 'oracle_jserv');

    INSERT INTO MGMT_LICENSABLE_TARGET_TYPES
      (pack_target_type, target_type) VALUES ('oracle_ias', 'oracle_webcache');

    INSERT INTO MGMT_LICENSABLE_TARGET_TYPES
      (pack_target_type, target_type) VALUES ('oracle_ias', 'oc4j');

    INSERT INTO MGMT_LICENSABLE_TARGET_TYPES
      (pack_target_type, target_type) VALUES ('oracle_ias', 'oracle_processconnect_domain');

    INSERT INTO MGMT_LICENSABLE_TARGET_TYPES
      (pack_target_type, target_type) VALUES ('oracle_ias', 'oracle_ldap');

    INSERT INTO MGMT_LICENSABLE_TARGET_TYPES
      (pack_target_type, target_type) VALUES ('oracle_ias', 'oracle_sso_server');

    INSERT INTO MGMT_LICENSABLE_TARGET_TYPES
      (pack_target_type, target_type) VALUES ('oracle_ias', 'website');
    commit;


    INSERT INTO MGMT_ADMIN_LICENSES VALUES ('db_diag');
    INSERT INTO MGMT_ADMIN_LICENSES VALUES ('db_tuning');
    INSERT INTO MGMT_ADMIN_LICENSES VALUES ('db_config');
    INSERT INTO MGMT_ADMIN_LICENSES VALUES ('as_diag');
    INSERT INTO MGMT_ADMIN_LICENSES VALUES ('as_config');

    COMMIT;

    IF (l_count > 0)
    THEN
        EXECUTE IMMEDIATE 'UPDATE mgmt_license_definitions SET pack_abbr=substr(pack_label,1,5)';
        EXECUTE IMMEDIATE 'ALTER TABLE mgmt_license_definitions MODIFY (pack_abbr NOT NULL)';
        EXECUTE IMMEDIATE 'ALTER TABLE mgmt_license_definitions ADD CONSTRAINT mgmt_license_definitions_unq UNIQUE(pack_abbr,target_type)';
    END IF;

END;
/