/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2011 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
SET SCAN OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE

  --
  -- Local constants and variables
  --
  l_sql varchar2(32000);

BEGIN

  --
  -- Register Query Internationalization (i18n)
  --
  l_sql :=
'        SELECT XMLForest
                (fnd_global.user_id
                ,fnd_global.employee_id person_id
                ,fnd_global.resp_id responsibility_id
                ,fnd_global.resp_appl_id responsibility_application_id
                ,fnd_global.security_group_id
                ,fnd_profile.value(''FND_SSO_LOCAL_LOGIN_MASK'') login_personalization
                ,decode(fnd_profile.value(''APPS_MAINTENANCE_MODE''), ''MAINT'', ''Y'', ''N'') maintenance_mode
                ,fnd_profile.value(''SIGNON_PASSWORD_FAILURE_LIMIT'') password_attempts
                ,(SELECT fnd_run_function.get_run_function_url
                          (fn.function_id
                          ,fnd_global.resp_appl_id
                          ,fnd_global.resp_id
                          ,fnd_global.security_group_id)
                  FROM   fnd_form_functions fn
                  WHERE  fn.function_name = ''UMX_FORGOT_PWD'') password_reset_url
                ,xxas_com_sec_pkg.get_root_url || ''/OA_HTML/OALogout.jsp'' logout_url
                ,fnd_profile.value(''UPLOAD_FILE_SIZE_LIMIT'') upload_size_limit
                ,nvl(fnd_profile.value(''XXAS_COM_BRANDING_IMAGE'')
                    ,fnd_profile.value(''FND_CORPORATE_BRANDING_IMAGE'')) branding_image
                ,fnd_profile.value(''ICX_SESSION_TIMEOUT'') session_timeout_mins
                ,nvl(fnd_profile.value(''FND_DISABLE_LOOK_AHEAD_LOV''), ''N'') auto_complete_disable
                ,nvl(fnd_profile.value(''LOOK_AHEAD_MIN_CHARS''), 3) auto_complete_chars
                ,xxas_com_mls_pkg.get_iso_date_mask iso_date_mask
                ,fnd_profile.value(''ICX_NUMERIC_CHARACTERS'') numeric_characters
                ,280 auto_complete_wait_delay_ms
                ,''Y'' auto_update_desktop_apps
                ,''Y'' track_usage
                ,''Y'' support_submenus
                ,''Y'' support_rtl_phone
                ,''N'' support_rtl_tablet
                ) parameters
              ,(SELECT XMLAgg
                         (XMLElement
                           (LOCALE
                           ,XMLForest
                             (l.language_code orcl_code
                             ,lower(l.iso_language) || ''_'' || upper(l.iso_territory) code
                             ,l.description
                             ,decode
                               (l.language_code
                               ,nvl(fnd_profile.value(''XXAS_OVER_LANGUAGE''), userenv(''LANG''))
                               ,''Y''
                               ,''N'') current_flag
                             ,nvl2(xxas_util_pkg.decode_lookup(''XXAS_COM_RTL_LANGS'', l.language_code), ''rtl'', ''ltr'') writing_direction)) ORDER BY description)
                FROM   fnd_languages_vl l
                WHERE (nvl(fnd_profile.value(''XXAS_COM_DEINSTALL_LANG_SUPPORT''), ''N'') = ''Y'' OR
                       l.installed_flag IN (''B'', ''I''))) locales
              ,(SELECT XMLAgg
                         (XMLElement
                           ("RESOURCE"
                           ,XMLForest
                             (r.key
                             ,r.value)))
                FROM   TABLE(xxas_com_mls_pkg.get_resources(:pProductKey)) r) resources
              ,(SELECT XMLAgg
                         (XMLElement
                           ("STYLE"
                           ,XMLForest
                             (s.style_code
                             ,s.style_name
                             ,s.style_value)))
                FROM   TABLE(xxas_com_laf_pkg.get_styles) s) styles
         FROM dual';

  --
  -- This is a special service and doesn't require function permissions to run
  --
  xxas_com_sr_pkg.seed_query_service
    (p_service_name      => 'Queryi18n'
    ,p_securing_function => null
    ,p_sql               => l_sql
    ,p_parameters        => xxas_com_sr_param_varray
                              (xxas_com_sr_param_type('pProductKey', xxas_com_sr_pkg.g_VARCHAR2)));

END;
/
COMMIT;
EXIT;
