Rem drv: <migrate type="data_upgrade" version="10.2.0.3" pos="service/service_data_upgrade.sql+"/>
Rem
Rem $Header: formsapp_data_upgrade.sql 03-may-2006.14:05:53 yxie Exp $
Rem
Rem formsapp_data_upgrade.sql
Rem
Rem Copyright (c) 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      formsapp_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    yxie        04/03/06 - Created
Rem

DECLARE

  MODULE_NAME VARCHAR2(32);

  -- value of homepageUrl property of the webapp
  homepageUrl MGMT_TARGET_PROPERTIES.PROPERTY_VALUE%TYPE;

  -- properties might be able to get migrated from webapp to formsapp
  p_svc_props MGMT_TARGET_PROPERTY_LIST := MGMT_TARGET_PROPERTY_LIST ();

  -- system name and type of the website target
  p_system_name MGMT_TARGETS.TARGET_NAME%TYPE;
  p_system_type MGMT_TARGETS.TARGET_TYPE%TYPE;

  -- critical components
  p_critical_components SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY();

  -- function to evaluate availability
  p_avail_eval_func MGMT_METRIC_DEPENDENCY.EVAL_FUNC%TYPE;

  l_counter NUMBER := 0;

  formsapp_guid MGMT_TARGETS.TARGET_GUID%TYPE;
  region_guid MGMT_RT_REGIONS.REGION_GUID%TYPE;

  formsapp_created BOOLEAN;
  add_region_result INTEGER;

BEGIN

  MODULE_NAME := 'Formsapp10203Upgrade';
  mgmt_log.register_logging_module ( MODULE_NAME,
                                     'Forms Application 10.2.0.3 Upgrade');

  FOR each_webapp IN 
      (SELECT t.target_name, 
              t.target_type,
              t.category_prop_1, 
              t.category_prop_2,
              t.category_prop_3,
              t.category_prop_4,
              t.category_prop_5,
              t.target_guid,
              t.timezone_region,
              t.display_name,
              t.host_name,
              t.emd_url
         FROM mgmt_targets t, mgmt_target_properties tp
        WHERE t.target_type = MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE
          AND t.target_guid = tp.target_guid
          AND tp.property_name = 'app_type'
          AND tp.property_value IN ('FORMS', 'is_form', 'forms'))

    LOOP
      BEGIN

        -- First check if a formsapp with the same name as the webapp already exists
        -- If it does, stop upgrade and return
        formsapp_guid := null;  
        BEGIN
          formsapp_guid := MGMT_TARGET.GET_TARGET_GUID(each_webapp.target_name, mgmt_global.G_FORMSAPP_TARGET_TYPE);
        EXCEPTION
          WHEN OTHERS THEN
            formsapp_guid := null;
        END;

        IF (formsapp_guid IS NULL) THEN

          -- get the possible common property of the webapp and formsapp
          BEGIN
            p_svc_props := MGMT_TARGET_PROPERTY_LIST ();

            SELECT property_value
              INTO homepageUrl
              FROM mgmt_target_properties
             WHERE target_guid = each_webapp.target_guid
               AND property_name = 'homepageUrl';

            p_svc_props.extend;
    	      p_svc_props(1) := MGMT_TARGET_PROPERTY('homepageUrl', 'INSTANCE', homepageUrl);
          EXCEPTION
            WHEN NO_DATA_FOUND THEN 
              NULL;
            WHEN OTHERS THEN
              MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'Getting Web Application ' || each_webapp.target_name || ' Homepage URL Exception: ' || SQLERRM);          
          END;


          -- get system of the website target
          MGMT_SERVICE.GET_SYSTEM(each_webapp.target_name, each_webapp.target_type, p_system_name, p_system_type);
      

          -- get critical components list
          l_counter := 0;
          p_critical_components := SMP_EMD_NVPAIR_ARRAY();
          FOR each_component IN 
            (SELECT assoc_t.target_name, assoc_t.target_type
               FROM mgmt_targets assoc_t,
                    mgmt_target_assocs assoc
              WHERE assoc.assoc_target_guid = assoc_t.target_guid
                AND assoc.source_target_guid = each_webapp.target_guid
                AND assoc.assoc_guid = MGMT_ASSOC.g_depends_on_guid)
          LOOP
            BEGIN
              l_counter := l_counter + 1;
              p_critical_components.extend;
              p_critical_components(l_counter) := SMP_EMD_NVPAIR(each_component.target_name, each_component.target_type);
            EXCEPTION
              WHEN OTHERS THEN 
                MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'Getting Web Application ' || each_webapp.target_name || ' Critical Components Exception: ' || SQLERRM);
            END;
          END LOOP;


          -- get evaluation fucntion for availability 
          p_avail_eval_func := NULL;
          MGMT_SERVICE.GET_AVAIL_EVAL_FUNC(each_webapp.target_name,
                                           each_webapp.target_type,
                                           p_avail_eval_func);


          -- create a Forms Application target with the same name,
          -- properties and associations
          BEGIN
            formsapp_created := TRUE;
            MGMT_SERVICE.CREATE_SERVICE(each_webapp.target_name,
                                        mgmt_global.G_FORMSAPP_TARGET_TYPE,
                                        each_webapp.display_name,
                                        each_webapp.host_name,
                                        each_webapp.emd_url, 
                                        each_webapp.timezone_region,
                                        p_svc_props,
                                        p_system_name,
                                        p_system_type,
                                        p_critical_components,
                                        p_avail_eval_func, -- always system based?
                                        '1.0',  
                                        each_webapp.category_prop_1,
                                        each_webapp.category_prop_2,
                                        each_webapp.category_prop_3,
                                        each_webapp.category_prop_4,
                                        each_webapp.category_prop_5,
                                        1);

          EXCEPTION
            WHEN OTHERS THEN
              MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'Create Service ' || each_webapp.target_name || ' Exception: ' || SQLERRM);
              formsapp_created := FALSE;
          END;          

          IF (formsapp_created) THEN

            formsapp_guid := MGMT_TARGET.GET_TARGET_GUID(each_webapp.target_name, mgmt_global.G_FORMSAPP_TARGET_TYPE);

            -- copy over the EUM watch list to the new formsapp
            FOR watchlist_item IN 
              (SELECT url_guid, display_name, url_filename, description
                 FROM mgmt_rt_urls 
                WHERE target_guid = each_webapp.target_guid)
            LOOP
              BEGIN
                INSERT INTO mgmt_rt_urls
                  (target_guid, 
                   display_name, 
                   url_filename, 
                   description)
                VALUES
                  (formsapp_guid, 
                   watchlist_item.display_name,
                   watchlist_item.url_filename,
                   watchlist_item.description);
              EXCEPTION
                WHEN OTHERS THEN
                  MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'Copying Web Application ' || each_webapp.target_name || ' EUM Watchlist Exception: ' || SQLERRM);
              END;
            END LOOP;


            -- copy over the EUM regions to the new formsapp
            FOR regionEntry IN 
              (SELECT r.region_name, r.description, re.min_ip, re.max_ip, re.domain
                 FROM mgmt_rt_regions r, mgmt_rt_region_entries re,
                      mgmt_rt_region_mapping rm
                WHERE r.target_guid = each_webapp.target_guid
                  AND r.region_guid = rm.region_guid
                  AND re.ID = rm.ID)
            LOOP
              BEGIN
                INSERT INTO mgmt_rt_regions
                  (target_guid, 
                   region_name, 
                   description)
                VALUES
                  (formsapp_guid, 
                   regionEntry.region_name,
                   regionEntry.description);

                -- Now get the region guid
                SELECT region_guid INTO region_guid
                  FROM mgmt_rt_regions
                 WHERE target_guid = formsapp_guid
                   AND region_name = regionEntry.region_name;
  
                -- Insert region component
                add_region_result := EMD_MNTR_USER.addRegionEntry(region_guid, regionEntry.domain, regionEntry.min_ip, regionEntry.max_ip);

              EXCEPTION
                WHEN OTHERS THEN 
                  MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'Copying Web Application ' || each_webapp.target_name || ' EUM Regions Exception: ' || SQLERRM);
              END;
            END LOOP;
        
          END IF;
        END IF;

      EXCEPTION
        WHEN OTHERS THEN
          MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'Upgrading Web Application '||each_webapp.target_name||' to Forms Application Exception: ' || SQLERRM);
      END; 

    END LOOP;

    COMMIT;

EXCEPTION
  WHEN NO_DATA_FOUND THEN 
    NULL;
  WHEN OTHERS THEN
    MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'Upgrading Web Applications to Forms Applications Exception: ' || SQLERRM);
END;
/
show errors;