, , , ,

Multi Language Data Support in Oracle E-business Suite

(MLS) is a feature in Oracle E-Business (EBS) Suite of applications that enables users to run their applications in many different languages. MLS has opened the doors for global rollout of Oracle EBS with users in different countries able to use the application in their own language at the same time.
MLS patch application
Multi Language patches can be applied in Oracle as per the metalink note, R11i / R12 : Requesting Translation Synchronization Patches (Doc ID 252422.1). This metalink note has got references to other notes for applying NLS patches.
Difference between NLS and MLS
National Language Support (NLS) refers to the ability of Oracle EBS to run in any one of the supported languages other than American English. However, MLS refers to the ability in which the instance can be run in as many as languages as are supported at the same time.
Once MLS patches have been applied in Oracle by the DBAs the developers have to keep a few more points in mind while developing custom components.
  • FND_LANGUAGES table shows which languages are installed, and which is the base language and which are non-base languages (Installed Languages)
    Query to identify which language(s) have been installed
SELECT LANGUAGE_CODE, INSTALLED_FLAG FROM FND_LANGUAGES
If the value of INSTALLED_FLAG is I then that language has been installed. If the value of INSTALLED_FLAG is B then it is the base language. By default, language_code is US is the base language.
Example
Execute the following query,
SELECT * FROM FND_LANGUAGES where installed_flag IN (‘I’, ‘B’)
  • The Translated tables (*_TL) contain one record for each language i.e the translated string for each language.
  • Translated views (*_VL) have been introduced for the “_TL” tables, which allow concurrent programs to select data based on the session language without running the risk of returning more rows than expected using a condition within the view query like,t.LANGUAGE= USERENV(‘LANG’)
Hence the view FND_CONCURRENT_PROGRAMS_VL will return rows as per the session language settings.
Note:
If queries are executed from SQL*Plus on the _VL views without setting the session language then no rows will be returned. This is explained in this article.
Example
If we query for concurrent_program_id, 20125, i.e. the program named Trial Balance, from the table FND_CONCURRENT_PROGRAMS_TLwe get the program name in all installed languages.
Querying the same concurrent_program_id from the corresponding _VL view, FND_CONCURRENT_PROGRAMS_VL gives us only 1 record.

MLS development guidelines

A list of general guidelines has been drawn up for developing technical components which will be display more than 1 language.

User messages

  • All user message (labels/pop ups) are to be derived from translation tables or from fnd message.
User message from the following,
  • Form item labels
  • Report item labels
  • Pop up messages
  • Concurrent program output
  • Workflow notifications
  • Form personalizations
Example
After the MLS patches have been applied open the Messages form.
Responsibility: Application Developer
Navigation: Application > Messages
Query for the seeded message named, ABORT.
Notice that the same message has been ported into as many messages as there are installed languages. The value has also been translated. Thus if a piece of code picks up the message text from a message it will only have pick up the message text based on the language and the code need not be changed at all.

SQL Queries

  • SQL queries should access data from _TL tables or _VL tables and have the following WHERE clause
    t.LANGUAGE= USERENV(‘LANG’)
An example of this incorrect usage is demonstrated here.


XML Publisher reports

  • There are 2 ways to develop XML Publisher report templates
  1. Each report will have as many templates as the number of languages it is expected to run on.
  2. Single template across all languages where the labels will also be derived from fnd messages or translation tables
    Single template Multiple template
    Advantage
    • 1 single file to be maintained
    • Modification/Addition of business logic or layout is simple
    • No business logic required to display template associate with language. It is configured in Oracle.
    Disadvantage
    • Modification/Addition of business logic or layout in the template is difficult
    • Business logic required to display the template associated with the language
    • Multiple files to be maintained
Example
Single template example
The developer is allowed to upload as many translatable files as there are installed languages in Oracle.
In this example there are 6 different language translations for a single template.
Multiple template example
You can also have multiple templates for a single report based on a language
In this case there is no need to have a separate translation file.


SQL Loader

Application File System

  • Once the MLS patches have been applied to Oracle a set of directories are created in Unix under reports, forms, etc directories with the 2 character language code.
    • For instance, if patch for Spanish has been applied in Oracle a new set of folders will be created, like $GL_TOP/reports/ES, $AP_TOP/forms/ES
    • The point to keep in mind here is that any custom component created with specific changes for a language must be dropped to the language specific directory.
    • All translatable files reside in a subdirectory which has the language code incorporated in the name (e.g. $AP_TOP/reports/EL, $GL_TOP/forms/F etc.) .
    – Forms, Reports, Messages & Seeded data are translated
Example
If you check $AU_TOP/forms directory in Unix you will find several directories
Now there is 1 directory to keep the same form in a different language. It is the same in all the seeded top directories, e.g. GL_TOP, AP_TOP, etc.

NLS parameters in functions

Many Oracle functions have MLS versions. The versions are listed below.
  • TO_DATE
    • NLS_DATE_LANGUAGE
    • NLS_CALENDAR
  • TO_NUMBER
    • NLS_NUMERIC_CHARACTERS
    • NLS_CURRENCY
    • NLS_DUAL_CURRENCY
    • NLS_ISO_CURRENCY
  • TO_CHAR
    • NLS_DATE_LANGUAGE
    • NLS_NUMERIC_CHARACTERS
    • NLS_CURRENCY
    • NLS_ISO_CURRENCY
    • NLS_DUAL_CURRENCY
    • NLS_CALENDAR
  • TO_NCHAR
    • NLS_DATE_LANGUAGE
    • NLS_NUMERIC_CHARACTERS
    • NLS_CURRENCY
    • NLS_ISO_CURRENCY
    • NLS_DUAL_CURRENCY
    • NLS_CALENDAR
  • NLS_UPPER
    • NLS_SORT
  • NLS_LOWER
    • NLS_SORT
  • NLS_INITCAP
    • NLS_SORT
  • NLSSORT
    • NLS_SORT

Example of usage

TO_NUMBER (’13.000,00′, ’99G999D99″,’nls_numeric_characters = ”,.”’)
Where:
’99G999D99″ –The format mask for the number
‘nls_numeric_characters = ”,.”” –The thousands and decimal separator to be used
The query
select TO_NUMBER (’13.000,00′, ’99G999D99′,’nls_numeric_characters = ”,.”’) Num from dual
will return
13000

Example of error message after applying MLS patches

Once the MLS patches were applied on a particular Oracle instance a DFF value started throwing errors. The error is displayed below.
The error message is the following,
The value ROW for value set ****** OE Shipment Priority occurs in more than one row in column LOOKUP_CODE of table FND_LOOKUP_VALUES and duplicate values are not allowed. Please choose a different value or contact your system administrator.
The reason for this error is,
  • The LOV associated with the DFF segment has not been correctly coded with regards to translations.
If we look into the value set definition for the DFF segment we find that the LOV is based on the table, FND_LOOKUP_VALUES.
As the Oracle instance now has MLS patches the table FND_LOOKUP_VALUES now can contain the same values across languages. We have 2 options to overcome the error faced by the user.
  1. We can add LANGUAGE= USERENV(‘LANG’) in the Where/Order By region to pick up the value based on the user’s language
  2. We can change the table name to FND_LOOKUP_VALUES_VL instead of FND_LOOKUP_VALUES
/by
, , , ,

R12 – How to extract Profile Option Values using Query

In Oracle E-Business Suite profile options can be set on several levels:
  • Site
  • Application
  • Responsibility
  • Server
  • Server with Responsibility
  • Organization
  • User

When needed one and the same profile option can be assigned to different levels. For example, when you implement Global Security Profiles to create access control on Operation Units – every responsibility for an Operating Unit may need a setting for profile options MO: Security Profile and HR: Security Profile. In the form which is used to set profile options all those different responsibilities can’t be seen at once.

In that case I use the SQL statement below to quickly provide me a list of the values of a profile option for all levels.  

The profile option name (column profile_option_name from table applsys.fnd_profile_options) can be found within the definition of the profile itself through responsibility Application Developer – menu Profile.


Here’s the SQL to provide you the values on all levels of a specific profile.

SELECT
    SUBSTR(e.profile_option_name,1,25) INTERNAL_NAME,
    SUBSTR(pot.user_profile_option_name,1,60) NAME_IN_FORMS,
    DECODE(a.level_id,10001,’Site’,10002,’Application’,10003,’Resp’,
    10004,’User’,10005,’Server’,10007,’Server+Resp’,a.level_id) LEVELl,
    DECODE(a.level_id,10001,’Site’,10002,c.application_short_name,
    10003,b.responsibility_name,10004,d.user_name,10005,n.node_name,
    10007,m.node_name||’ + ‘||b.responsibility_name,a.level_id) LEVEL_VALUE,
    NVL(a.profile_option_value,’Is Null’) VALUE,
    to_char(a.last_update_date, ‘DD-MON-YYYY HH24:MI’) LAST_UPDATE_DATE,
    dd.USER_NAME LAST_UPDATE_USER
FROM
    applsys.fnd_profile_option_values a,
    applsys.fnd_responsibility_tl b,
    applsys.fnd_application c,
    applsys.fnd_user d,
    applsys.fnd_profile_options e,
    applsys.fnd_nodes n,
    applsys.fnd_nodes m,
    applsys.fnd_responsibility_tl x,
    applsys.fnd_user dd,
    applsys.fnd_profile_options_tl pot
WHERE
    e.profile_option_name = ‘XLA_MO_SECURITY_PROFILE_LEVEL’    AND e.PROFILE_OPTION_NAME = pot.profile_option_name (+)
    AND e.profile_option_id = a.profile_option_id (+)
    AND a.level_value = b.responsibility_id (+)
    AND a.level_value = c.application_id (+)
    AND a.level_value = d.user_id (+)
    AND a.level_value = n.node_id (+)
    AND a.LEVEL_VALUE_APPLICATION_ID = x.responsibility_id (+)
    AND a.level_value2 = m.node_id (+)
    AND a.LAST_UPDATED_BY = dd.USER_ID (+)
    AND pot.LANGUAGE = ‘US’
ORDER BY
    e.profile_option_name

/by
, ,

How to Implement Bar Code in XML Publisher Report

Bar Code Local Setup:

1) Copy Barcode font in C:WindowsFonts directory.
2) Copy xdo.cfg in C:Program FilesOracleXML Publisher DesktopTemplate Builder for Wordconfig directory. The sample file is already available when BI Publisher desktop is installed.
3) Right click the font file and go to properties. Copy the .ttf file name. Double click the font file to get the family name.
4) Open the xdo.cfg file and paste as follows (My filename is w39elc.ttf and font family is WASP 39 ELC)

<font family=”WASP 39 ELC” style=”normal” weight=”normal”>  <truetype path=”C:WINDOWSFontsw39elc.ttf” /> </font>

Bar Code Application Setup:
Basically this post refers to any font that you want to embed into you output but I was working specifically on demand to insert the barcode.
So start with something simple. Obtain the font file. In my case I posses “WASP 39 ELC” font. Font file name is w39elc.ttf. Install the font into Windows by double clicking on it and choosing “install”
Open your RTF template and place the bar code in the location you want.


So far so good and if you run this template locally on your workstation you will see the bar code. But not in EBS.
  This is what you need to do in order to make it work in EBS
Open “XML Publisher Administrator” responsibility
Go to “Administration”-> “Font Files”


Click on “Create Font File”


In “Font Name” put the name of the font as it appears in Word. Choose the font file and click “Apply”


Go to “Administrator”-> “Font Mapping”
image
Click on “Create Font Mapping Set”


Put into “Mapping Name” – Bar Code Fonts, into “Mapping Code” – BAR_CODE_FONTS, into “Type” –FO To PDF and click “Apply”


Click on the Bar Code Fonts link


Click on “Create Font Mapping”


Define Font Mapping as appears in the print screen bellow and click “Continue”


Ok, now you need  template to “know” the bar code font we just created.


Open template definition and go to “Edit Configuration”
Expand “FO Processing” and put into “Font Mapping Set” – Bar Code Fonts and click “Apply”.


Now you can run your template and it will display the bar code properly.
/by
, , , ,

R12 – How to Clear Apache Cache

How to Clear Apache Cache (Oracle EBS General)

Steps to Clear Apache Cache

Step – 1 Navigate to Functional Administrator responsibility.

Step – 2 Go to: Home > Core Services > Caching Framework
Step – 3 Go to: Global Configuration > Clear All Cache.
Click Apply. Apache Cache is now clear.
/by
, , , , ,

R12 UOM Conversion of units using Function

Standard Package : INV_CONVERT

Get the Conversion rate from below function easily.

FUNCTION inv_um_convert (p_item_id IN NUMBER,
                          p_from_uom_code IN VARCHAR2,
                          p_to_uom_code IN VARCHAR2) RETURN NUMBER; 

select msi.segment1,
        msi.primary_uom_code,
        ‘Kg’ as second_uom,
        inv_convert.inv_um_convert (msi.inventory_item_id, ‘kg’
                                   msi.primary_uom_code) as coefficient from mtl_system_items_b
where msi.segment1 = ‘OEAG01-ITEM’;

/by