, , ,

Oracle R12 – Special and Pair Validation Value Sets

Special and Pair validation types

I am working on an article about flexfields and flexfield validation.
Even though the article is not yet finished, I thought the part about ‘SPECIAL’ and ‘PAIR’ validation types might be interesting enough. Many people seem to think they can only use the seeded validation sets. However, you can also create your own validation sets. And their options are very powerful. So I wanted to publish this part of the article as a prelude to the full story.

Special Validation

Special validation is used to provide flexfield functionality for a single value. What that means is that you can have for example a concurrent program parameter that will be filled with a Key flexfield value, or a range of flexfield values.
Let’s go back to the Key Flexfield. We know that they are combinations of different segment values that are stored in a separate combination table.
When you want to submit a key-flexfield combination as a parameter to a concurrent program, you can code your own validation for the separate values. But you’ll be missing the nice functionality that gives you pop-ups, a validation over the resulting combination and if needed the ID-value for the flexfield combination.
That is possible with a ‘Special’ validation type.
The special validation uses a number of user exits to enter, validate and query keyflex segments. With special validation, you will be able to enter one or more segment values for a key flexfield. To enter these segment values, 3 user exits can be used. They are: ‘POPID’, ‘VALID’ and ‘LOADID’.
POPID is used to enable the user to enter the flexfield segment value. It is called when the users cursor enters the segment value field. With this user exit, you decide which segment values should be shown, and how they should be shown.
 VALID is called when the user exits the segment value, or confirms the chosen flexfield combination. It validates the entered value against the values existing in the key flexfield table.
LOADID is optional, and it can be used to choose which information will be returned as flexfield value. This can be the concatenated segments, or the id-value for the flexfield combination or segment values.
These 3 user exits can be assigned to 3 ‘events’. There are more events possible, but they are either not yet in use, or their use is not yet supported. So we will only use ‘Validate’, ‘Edit’ and ‘Load’.
Sounds complicated, so far? Don’t worry; this is not an easy validation. But we’ll build some examples to give you an idea. First we start with building a very easy special validation. This will be built on our Code Combination key flexfield. We’ll be using a concurrent program ‘Test Flex Validation’ program to see our different options.
This program is based on the following procedure:
CREATE OR REPLACE PROCEDURE XXX_TEST_FLEXFIELD_PARAMS
( errbuf   out varchar2
, retcode  out varchar2
, p_flex   in  varchar2
, p_flex2  in  varchar2 := ‘XXX’
, p_flex3  in  varchar2 := ‘XXX’
, p_flex4  in  varchar2 := ‘XXX’
, p_flex5  in  varchar2 := ‘XXX’
) IS
BEGIN
   FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_flex);
   FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_flex2);
   FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_flex3);
   FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_flex4);
   FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_flex5);
END;
This will only write the parameter value to the output of the request. To use flexfields as parameters for concurrent programs, we need to define a value set based on them.
We will start with the barest setup to enter a key-flexfield combination. For this article, we use the accounting flexfield, with code ‘GL#’  and id-num ‘101’.
In this case, we have the following definition:
So what does this mean?
The first box is for the edit event. This will be triggered when the user enters the cursor into the field with this value set.
FND POPID         This is the user exit to pop up a flexfield screen, and let the user enter the flexfield values.
CODE=”GL#”     This is the flexfield code for the key flexfield that we will be using.
APPL_SHORT_NAME=”SQLGL” The short name for the application the flexfield belongs too. Together with ‘Code’, this will identify the flexfield itself.
NUM=”101″       The id-number for the flexfield structure. If you have only a single structure flexfield, it is optional. For flexfields enabled for multiple structures, you need to enter the id-number.
VALIDATE=”PARTIAL”   Validate can be ‘None’, ‘Partial’ or ‘Full’. None means the combination is not validated. Partial means that the separate segments are validated, there is no validation if the combination exists. Full means that segments and combination will be checked, and if a new value is entered, this will be inserted into the key flexfield table.
SEG=”:!VALUE”                This is the forms field that will be used to store the value of the segments.
The second box is for the ‘Validation’ event. This code will be called when the user navigates out of the field, or submits the entire combination.
Now when we set this value set as a parameter for our concurrent program, we can see how the validation works:
Now when we run the program, we get this pop-up:
We have all the functionality of the key flexfield. We can use the ‘Combinations’ button to search for existing combinations, and all separate segments will be validated, as will be the final combination.
When we submit a value to our program, it will show the concatenated segments as the value of our parameter:
Now let’s see some more features of this validation. For example, we’d like to have the value of the combination id. (CODE_COMBINATION_ID in our case, since we use the Accounting Flexfield).
To get that, we need to add the LOADID user exit:
 
The ‘Load’ event will get the combination-id from the flexfield table. This is only possible for the ‘VALIDATE=”FULL”, since it will validate the whole combination. Also we need to set the ID=”:!ID”. This will populate the :!ID column with the ID value of the combination.
Finally, I added the ‘DINSERT=”NO” ‘, because we don’t want to allow insertion of new code combinations from this value set. (And Validation=”FULL” by default inserts new combinations into the flexfield column).
Now when we run the concurrent request, we see that the parameter value is the code_combination_id instead of the concatenated segments:

With these user exits it is also possible to select just a number of segments, instead of the whole combination. For this we remove the ‘Load’ / ‘LOADID’ part again.
Then we add a ‘DISPLAY=”x” ‘ to the ‘Edit’ and ‘Validate’ user exits. The “display” parameter is defaulting to ‘ALL’. But you can also specify separate segments by their sequence number or names. In our case, we display the first 2 segments:
Now when we run the concurrent program, we get a pop-up for only the first 2 values:
A very nice feature (at least as far as I’m concerned) is the use of a where clause on the combination values. Consider the following ‘Enter’ code:
FND POPID
CODE=”GL#”
NUM=”101″
APPL_SHORT_NAME=”SQLGL”
VALIDATE=”FULL”
TITLE=”Special Validation Key”
ID=”:!ID”
SEG=”:!VALUE”
DESC=”:!MEANING”
WHERE=”segment2 not like ‘1%’ “
The “WHERE” clause prevents us from choosing combinations that have a segment2 starting with ‘1’. When we run our concurrent program with this, and choose the combinations:
There is no Dpt starting with 1.
When we add the “WHERE”-clause to the validation event too, it will prevent us from entering the values manually:
The last feature that we’ll look into is the use of a pl/sql validation through the special validation routines. By using the user-exit PLSQL, we can call an anonymous PL/SQL block in our ‘Validation’ event. I created a value set with the following function for the ‘Validation’ event:
FND PLSQL ” declare
  v_value varchar2( 10 ) := :!value ;
  v_sum number;
  v_valid boolean;
begin
   v_sum:=0;
   for i in 1..length(v_value) loop
    v_sum :=v_sum+(length(v_value)+1-i)*substr(v_value,i,1);
  end loop;
  if mod(v_sum,11)=0 then
     v_valid := TRUE;
  else
     v_valid:=FALSE;
  end if;
  if not v_valid then
      fnd_message.set_name(‘FND’,’FND_GENERIC_MESSAGE’ );
      fnd_message.set_token(‘MESSAGE’,’This is not a valid bank account’);
      fnd_message.raise_error;
  end if;
END; “
This PL/SQL procedure validates a (Dutch) bank account number. If it does need pass the test, a message will be displayed. This gives you almost unlimited possibilities for validating entered data.
As you can see, it is only a ‘Validate’ event. Because we don’t need any special functionality for entering the data. We can limit the entry to numbers only on the ‘Validation Set’ main page.
Now when we use this value set for our concurrent program, we can only enter valid dutch bank accounts:
And

The list of parameters for the user exits is longer than this. So we won’t be going through all the possibilities. You can check the Developers Guide and the Flexfield guide for a complete listing of options. (Did you notice the flexfield title that I sneaked into the pop-up? Try and find the option for that!)
Please try the different options for yourself, and realize the possibilities of the special validation.

Pair Validation

Meanwhile, we’ll continue to the ‘Pair’ validation. The pair validation is very much like the ‘special’ validation. It uses the same kind of user exits, but this time, a range of segment values or combinations is selected.
Let’s first create a range of the account segment. Instead of using POPID and VALID, we use POPIDR and VALIDR. The R-version of the user-exits automatically create a range.
Of course we need 2 parameters to set the range. However, we need only one validation set.
I created the validation set ‘XXX_PAIR_VAL’. I entered only the edit and validate events:
The next step is to set the parameters for both the low and high value. Both parameters have the validation set ‘XXX_PAIR_VAL’.
Now when we run the program, we can enter a range. This includes validation that the high value is indeed higher or equal to the low value.
Of course the concurrent program will receive the values for 2 parameters.
When we use the full validation we can enter a range of the whole account combination. Note that we cannot use the FULL validation for pair-validation. Because that would mean the use of the combination-id from the flexfield table and based on the combination-id’s you cannot build a range.
So we can only use PARTIAL and NONE for the validation. For that same reason, I have not yet had a reason to use a LOAD event for PAIR validation. It is however allowed to use one.
I created a PAIR validation for the whole accounting range as follows:
 
When used in the concurrent program, it will indeed allow us to enter a range of all segments:
That completes the chapter on PAIR validation too.
/by
, ,

Query to Find Application Top in Oracle Apps R12

Query to Find Application Top in Oracle Apps

Below query will return the Oracle apps all application’s Top.

SELECT variable_name,
  VALUE
FROM fnd_env_context
WHERE variable_name LIKE ‘%_TOP’ ESCAPE ”
AND concurrent_process_id =
  (SELECT MAX(concurrent_process_id) FROM fnd_env_context
  )
ORDER BY 1;

/by
, , , ,

R12 – How to Get Concurrent Program Output in Multiple Languages

 concurrent program can provide output in a language if the language is installed in Oracle. To find out which languages are installed in Oracle you can check this article. Generally seeded programs provide output of a program based on the user preferred language. In this example we have illustrated how to override user preference and provide the output of a concurrent program and give multi lingual output based on a certain rule/condition.

Step 1: Create Multi language function in the database
We created a multi language packaged function named, XX_MLS_LANG.GET_LANG. The function expects an input from a concurrent program. This input will come from a concurrent program parameter named, Language. In this example the user will enter a language name and this name will be converted into the language code by the function. For example, if the users enter GERMAN the language function will return D, that is the code in Oracle for German.
The code for this function is given below.
CREATE OR REPLACE PACKAGE APPS.xx_mls_lang AUTHID CURRENT_USER
AS
   FUNCTION get_lang
      RETURN VARCHAR2;
END xx_mls_lang;
/
 
CREATE OR REPLACE PACKAGE BODY apps.xx_mls_lang
AS
   FUNCTION get_lang
      RETURN VARCHAR2
   IS
      p_lingo              VARCHAR2 (40)   := NULL;
      l_select_statement   VARCHAR2 (4000);
      source_cursor        INTEGER;
      lang_string          VARCHAR2 (240)  := NULL;
      l_lang               VARCHAR2 (30);
      l_lang_str           VARCHAR2 (500)  := NULL;
      l_base_lang          VARCHAR2 (30);
      l_dummy              INTEGER;
      ret_val              NUMBER          := NULL;
      parm_number          NUMBER;
      l_trns_lang_check    NUMBER;
   BEGIN
      -- Parameter Entry
      ret_val := fnd_request_info.get_param_number ('Language', parm_number);
 
      IF (ret_val = -1)
      THEN
         p_lingo := NULL;
      ELSE
         p_lingo := fnd_request_info.get_parameter (parm_number);
      END IF;
 
      -- Get Base Language
      SELECT language_code
        INTO l_base_lang
        FROM fnd_languages
       WHERE installed_flag = 'B';
 
      -- If the user has entered a language/value for the parameter then
      -- extract it the value
      IF p_lingo IS NOT NULL
      THEN
         -- Open the cursor
         source_cursor := DBMS_SQL.open_cursor;
 
         -- Create a query string to get languages based on parameters.
         l_select_statement :=
            'SELECT language_code FROM fnd_languages where nls_language = UPPER(:p_language)';
         DBMS_SQL.parse (source_cursor, l_select_statement, DBMS_SQL.v7);
         DBMS_SQL.bind_variable (source_cursor, ':p_language', p_lingo);
 
         -- Execute the cursor
         DBMS_SQL.define_column (source_cursor, 1, l_lang, 30);
         l_dummy := DBMS_SQL.EXECUTE (source_cursor);
 
         -- If the cursor has returned more than 1 row then
         -- get the output of the cursor into respective variables
         IF DBMS_SQL.fetch_rows (source_cursor) <> 0
         THEN
            DBMS_SQL.COLUMN_VALUE (source_cursor, 1, l_lang);
            l_lang_str := l_lang;
         ELSE
            -- If the cursor returned 0 rows then return the base language
            l_lang_str := l_base_lang;
         END IF;
 
         -- Close the cursor
         DBMS_SQL.close_cursor (source_cursor);
      ELSE
         -- If the user has not entered any value then return the base language
         l_lang_str := l_base_lang;
      END IF;
 
      fnd_file.put_line
         (fnd_file.LOG,
          'Checking to see if the derived language has a translated layout or not'
         );
 
      BEGIN
         -- Check if the language entered by the user is associated to a translated template or not
         SELECT 1
           INTO l_trns_lang_check
           FROM xdo_lobs xl
          WHERE xl.lob_type = 'MLS_TEMPLATE'
            AND xl.trans_complete = 'Y'
            AND xl.LANGUAGE = l_lang_str
            AND xl.lob_code =
                   ( -- Get the actual program name from the MLS request
                    SELECT argument2  -- Prog name
                      FROM fnd_run_req_pp_actions
                     WHERE parent_request_id = fnd_global.conc_request_id -- Request id of the MLS function
                       AND action_type = 6)             -- Template Code
                                                 ;
      EXCEPTION
         WHEN OTHERS
         THEN
            -- If the chosen language does not have an associated template the SQL will fail
            -- and therefore return the default language
            fnd_file.put_line (fnd_file.LOG,
                                  'There is no layout for language: '
                               || l_lang_str
                              );
            fnd_file.put_line
               (fnd_file.LOG,
                   'Therefore we are using the default template for language: '
                || l_base_lang
               );
            l_lang_str := l_base_lang;
      END;
 
      RETURN (l_lang_str);
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_SQL.close_cursor (source_cursor);
         RAISE;
   END get_lang;
END xx_mls_lang;
/

Step 2: Create an executable for Multi Language
When the multi language packs are installed in Oracle a new type of concurrent executable is created, Multi Language Function. We shall create a concurrent executable of this type for the database function we have created.

Step 3: Add the parameter to the concurrent program (Optional)
In this step we are going to modify the seeded program, Active Users, to provide the output in multi language. Since this program does not take any parameters we are going to add a parameter to this program to accept a language name as a user entry (as explained in Step 1).
Step 4: Attach the multi language executable
Now we shall attach the multi language executable to the concurrent program, Active Users, so that the output language is taken from the MLS function.
Once the language packs are installed a field named, MLS function, is enabled on concurrent program form. Enter the MLS executable here.
Note: MLS function field has a LOV attached to it. The LOV has the list of executables that are of type Multi Language Function, i.e. executables defined as in Step 2.

Test the concurrent program
Now we shall execute the concurrent program to check the output. Open the SRS form
Now select the program as Active Users.
We get a prompt for the parameter we had created, i.e. Language. Enter a language, say Spanish.
Press OK and submit the program.
Notice that 2 concurrent programs are executed by Oracle instead of 1.
  1. Active Users (Multiple Languages)
  2. ES-ES: (Active Users)
This is because the first request is for the MLS language function and the second request is for the concurrent program.
When the concurrent programs complete check the log and output of both the requests.
  • Output of request, Active Users (Multiple Languages)
There is no output of the request that is kicked off for the MLS function.
  • Log of request, Active Users (Multiple Languages)
+---------------------------------------------------------------------------+
Application Object Library: Version : 12.0.0
 
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
 
FNDMLSUB module: Multiple Languages
+---------------------------------------------------------------------------+
 
Current system time is 15-AUG-2013 08:35:59
 
+---------------------------------------------------------------------------+
 
**Starts**15-AUG-2013 08:35:59
**Ends**15-AUG-2013 08:35:59
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
Calling language function xx_mls_lang.get_lang  : 15-AUG-2013 08:35:59
Language function returned the following languages : E .  : 15-AUG-2013 08:35:59
+---------------------------------------------------------------------------+
The following are the details of submitted requests:
Request ID Language
------------------------------------------
     57613357       SPANISH
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+

Important: When a concurrent request gives output in multiple languages it is the header or the data labels that are changed into different languages. The data remains in the same language as it is stored in the database.
  • Log of request, Active Users (Multiple Languages)
+---------------------------------------------------------------------------+
Application Object Library: Version : 12.0.0
 
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
 
FNDSCURS module: Usuarios Activos
+---------------------------------------------------------------------------+
 
Hora actual del sistema: 15-AGO-2013 08:35:59
 
+---------------------------------------------------------------------------+
 
+-----------------------------
| Iniciando la ejecución del programa simultáneo...
+-----------------------------
 
Argumentos
------------
Language='Spanish'
------------
 
APPLLCSP Environment Variable set to :
 
 Current NLS_LANG and NLS_NUMERIC_CHARACTERS Environment Variables are :
SPANISH_SPAIN.UTF8
 
' '
 
Introduzca la Contraseña:
REP-0092: Advertencia: Argumento LANGUAGE versión 1.1 no soportado. Use en su lugar la variable de entorno de Idioma Nacional de ORACLE.
REP-0092: Advertencia: Argumento LANGUAGE versión 1.1 no soportado. Use en su lugar la variable de entorno de Idioma Nacional de ORACLE.
 
Report Builder: Release 10.1.2.3.0 - Production on Jue Ago 15 08:36:02 2013
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
+---------------------------------------------------------------------------+
Inicio del log de mensajes de FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
Fin del log de mensajes de FND_FILE
+---------------------------------------------------------------------------+
 
Note that the log file has now changed to Spanish. This means that MLS affects data labels, headers and log files but not data.
Appendix:
Now if we were to take off the MLS function from Step 4 and executed Active Users concurrent program then Oracle would have executed only 1 request.

/by
, , , ,

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