Oracle APEX Translations

This post may not be ideal under Oracle Apps blog but sometimes you can't stop working on associated technologies especially when doing technical consulting work.

I explored Oracle APEX product during Redhat Assignment for Supportable 360 application 

Steps used for APEX translated application access to users

APEX translated application Language Drop down.

Requirement is to have languages drop down close to navigation bar so user can select language of choice and should be available for next user visit.

0) These steps assumes XLIFF files and application mappings are in place and working correctly Application Express 4.2.0.00.27

1) Define shared component - User Interface - list of values P_LANGUAGE -- add static values as Deutsch and return value 1::LANG:NO::FSP_LANGUAGE_PREFERENCE:de
for each language applicable. Total 10 entries should be there

2) Define shared component - Logic - application item - FSP_LANGUAGE_PREFERENCE  and RH_APEX_LANG_URL

   application processes
   i)   p_set_apex_lang_url , sequence 1 ,
        Process Point "On Load: Before Header(page template header)
      
       BEGIN
          :RH_APEX_LANG_URL:=fnd_profile.value('FND_APEX_URL')||'f?p=&APP_ID.:1' ;
       END;

  ii) set_language , sequence 1 ,
      Process Point "On Load: Before Header(page template header)
      begin
           owa_util.redirect_url('f?p='||:APP_ID||':'||:APP_PAGE_ID||':'||:APP_SESSION);
     end;

   Process Error Message :  "Process cannot be executed"
   Condition Type : "Request = Expression 1"
   Expression 1 : "LANG"
     
  iii) p_set_user_lang_preference sequence 2 ,
      Process Point "On Load: Before Header(page template  header)
 
   BEGIN
     IF :FSP_LANGUAGE_PREFERENCE  IS NOT NULL THEN
       APEX_UTIL.SET_PREFERENCE (
                   p_preference => 'FSP_LANGUAGE_PREFERENCE',
                   p_value => :FSP_LANGUAGE_PREFERENCE );
    END IF;
  END;

This ensures that user preference is stored for future references and every time when user opens Supportable 360 last language is used

 iv) Create Shared Component --> Logic --> Application Computations
   Sequence 10 , Name RH_APEX_LANG_URL , Point "On New Instance (new session)"  Type : "SQL Query (return single value)"

Computation: " select fnd_profile.value('FND_APEX_URL')||'f?p=&APP_ID.:1' from dual "

Note : FND_APEX_URL is profile option value set in sourcing Oracle Applications


3) Set Shared Componenet --> Globalization --> globalization attributes
   Application Language Derived From : Application Preference (use FSP_LANGUAGE_PREFERENCE )

4) On page 0 define html region called P_LANGUAGE_SELECTOR ,  type "HTML Text" Display Point "Page Template Region Position 8"
Start on new Row "Yes", column/ coloumn span automatic  , Region Display Selector "No"

5) Define page item P0_PICK_LANG inside above region
Display as "Select List" , start on new row "Yes" , column/ coloumn span automatic ,
Page action when value changed "Redirect to page (based on selected value)
Named LOV "P_LANGUAGE" , HTML Form Element attributes style ="height:22px;width:115px;vertical-align:top;" Display Extra Values : "Yes" , Display null value: "Yes"

Default value

case
when lower(APEX_UTIL.GET_PREFERENCE (p_preference => 'FSP_LANGUAGE_PREFERENCE',p_user => APEX_CUSTOM_AUTH.GET_USER ))= 'en' then 'English'
when lower(APEX_UTIL.GET_PREFERENCE (p_preference => 'FSP_LANGUAGE_PREFERENCE',p_user => APEX_CUSTOM_AUTH.GET_USER ))= 'de' then 'Deutsch'
when lower(APEX_UTIL.GET_PREFERENCE (p_preference => 'FSP_LANGUAGE_PREFERENCE',p_user => APEX_CUSTOM_AUTH.GET_USER ))= 'es' then 'Español'
when lower(APEX_UTIL.GET_PREFERENCE (p_preference => 'FSP_LANGUAGE_PREFERENCE',p_user => APEX_CUSTOM_AUTH.GET_USER ))= 'fr' then 'Français'
when lower(APEX_UTIL.GET_PREFERENCE (p_preference => 'FSP_LANGUAGE_PREFERENCE',p_user => APEX_CUSTOM_AUTH.GET_USER ))= 'it' then 'Italiano'
when lower(APEX_UTIL.GET_PREFERENCE (p_preference => 'FSP_LANGUAGE_PREFERENCE',p_user => APEX_CUSTOM_AUTH.GET_USER ))= 'pt-br' then 'Português(Br)'
when lower(APEX_UTIL.GET_PREFERENCE (p_preference => 'FSP_LANGUAGE_PREFERENCE',p_user => APEX_CUSTOM_AUTH.GET_USER ))= 'zh-tw' then '中文 (繁體)'
when lower(APEX_UTIL.GET_PREFERENCE (p_preference => 'FSP_LANGUAGE_PREFERENCE',p_user => APEX_CUSTOM_AUTH.GET_USER ))= 'zh-cn' then '中文（简体）'
when lower(APEX_UTIL.GET_PREFERENCE (p_preference => 'FSP_LANGUAGE_PREFERENCE',p_user => APEX_CUSTOM_AUTH.GET_USER ))= 'ja' then '日本語'
when lower(APEX_UTIL.GET_PREFERENCE (p_preference => 'FSP_LANGUAGE_PREFERENCE',p_user => APEX_CUSTOM_AUTH.GET_USER ))= 'ko' then '한국어'
end

Default Value type : PL/SQL Expression


6) Modify Page theme for 9 Simple Gray
Theme Name : One Level Tabs - Right Sidebar (optional / table-based)

add below code in Definition --> Body section.
This format's positioning by using HTML attributes defined here.
 I have used display point #REGION_POSITION_08# for language drop down


<div id="header">
  <div id="app-logo"><a href="#HOME_LINK#">#LOGO##REGION_POSITION_06#</a></div>
  #REGION_POSITION_07#
  <div style="width:600px;vertical-align:top;" id="navbar">
   <div class="app-user">#REGION_POSITION_08#</div>
     #NAVIGATION_BAR#
   <div class="app-user">#WELCOME_USER#</div>   
  </div>

Need more details ?  Please refer Oracle APEX 4.0 Cookbook .

Browser Language Setting Can Wreak Havoc

Before this support incident I never realized that browser language setting can "Wreak Havoc" for oracle queries. 

We have supportable application developed in APEX which authenticates users with RSA login and check if necessary responsibility is available with user. The issue occurring was few users were getting access denied custom errors even when they had needed access. 
After many days of debugging efforts we could get to root of Access Denied error users were receiving in Firefox browser.

Usual debugging steps like trying the queries at our end , logging debug messages were not giving expected results. The same user was able to login from different desktop giving suspicion on browser settings.
Usual helpdesk tips like uninstalling browser and re-installing it also didn't worked.

After some brainstorming on debug messages we came to conclusion that query itself is failing and getting changed run time for the user. On close look we found use of _VL tables from Oracle EBS. Then we asked user for browser language and alaas..... user was using en-gb( English United Kingdom) as browser language and this was causing count zero for the query checking active supportable responsibility.

Browser language change set nls language on database session changing native language and queries can fail if US English is not default language. Other altenative is not to use_VL or _TL views from Oracle EBS and to design queries on base tables directly. 

Lesson learned after too many to and fro communications with user and constrained testing environment!!! 

Technical details

Setting language runs below


export NLS_LANG="ENGLISH_UNITED KINGDOM.WE8MSWIN1252"

The purpose of the ALTER SESSION statement is "to specify or modify any of the conditions or parameters that affect your connection to the database. The statement stays in effect until you disconnect from the database." 

Query which caused failure 

SELECT count(1)
  INTO l_user_resp_cnt
  FROM fnd_user fu,
    fnd_user_resp_groups_all furgd,
    fnd_responsibility_vl frv
  WHERE fu.user_id = furgd.user_id
  AND furgd.responsibility_id = frv.responsibility_id
  AND fu.user_name = APEX_CUSTOM_AUTH.GET_USERNAME
  AND fu.user_name = v_final_user
  AND frv.responsibility_name = 'RH Supportable Self Service'
  AND furgd.start_date <= sysdate
  AND NVL(furgd.end_date, sysdate + 1) > sysdate
  AND fu.start_date  <= sysdate
  AND NVL(fu.end_date, sysdate + 1) > sysdate
  AND frv.start_date <= sysdate
  AND NVL(frv.end_date, sysdate + 1) > sysdate;

View FND_RESPONSIBILITY_VL uses t.LANGUAGE = USERENV ('LANG') condition which causes no data found and hence count 0

Fix was to use FND_RESPONSIBILITY_TL  FND_RESPONSIBILITY tables in query

Good debugging stuff, when ideas are dried up..  right ? :) Keep working