, , ,

R12 – How to Delete XML Publisher Data Definition and Template

How often do you create a XML publisher definition with a wrong Codes (Template or Data Definition)? Or you want to change the Code so that it is more meaningful?
Due to some typo error or to give some more meaningful name as per the standards, you can’t change those fields later. Also you can’t delete them too. Oracle recommends to disable them by giving an end date. But many developers don’t like to leave the wrong stuff in the system. They better like to delete them and freshly recreate them.

Why Oracle has restricted to Update or Delete?
The reason is concurrent program with XML output matches the Short Name with the template Code to find out which XML Publisher template to use for post processing. If you delete this template, the Post Processor cannot find the template, and then give errors. So it is always better not to give an option to update or delete.

But we have this workaround to update or delete the template or data definitions using scripts. It is highly recommend to run first in development instances.

Before the workaround, let’s look at the main tables that store the information of the Data Definitions and Templates.

1. XDO_DS_DEFINITIONS_B: table for storing data source definition represented by XML Schema Definition (XSD). Each data source has one or more elements, and this information are stored in XDO_DS_ELEMENTS_B.
2. XDO_DS_DEFINITIONS_TL: translation table for XDO_DS_DEFINITIONS_B.
XDO_LOBS: This table is used for storing locale (language and territory) sensitive binary and text files. It is mainly used for storing language layout templates.
3. XDO_CONFIG_VALUES: stores the values of XML Publisher configuration properties entered from the Oracle Applications interface.
4. XDO_TEMPLATES_B: table for template information. Each template has a corresponding data source definition stored in the XDO_DS_DEFINITIONS_B. Each translation of a certain template, not each template, has a corresponding physical template file. The physical template file information is stored in the XDO_LOBS.
5. XDO_TEMPLATES_TL: translation table for XDO_TEMPLATES_B.

–Code Tested in R12.1.3 Instance
SET SERVEROUTPUT ON;
DECLARE
— Change the following two parameters
var_templateCode    VARCHAR2 (100) := ‘ORACLEERPAPPSGUIDE’;     — Template Code
boo_deleteDataDef   BOOLEAN := TRUE;     — delete the associated Data Def.
BEGIN
FOR RS
IN (SELECT T1.APPLICATION_SHORT_NAME TEMPLATE_APP_NAME,
T1.DATA_SOURCE_CODE,
T2.APPLICATION_SHORT_NAME DEF_APP_NAME
FROM XDO_TEMPLATES_B T1, XDO_DS_DEFINITIONS_B T2
WHERE T1.TEMPLATE_CODE = var_templateCode
AND T1.DATA_SOURCE_CODE = T2.DATA_SOURCE_CODE)
LOOP
XDO_TEMPLATES_PKG.DELETE_ROW (RS.TEMPLATE_APP_NAME, var_templateCode);

DELETE FROM XDO_LOBS
WHERE     LOB_CODE = var_templateCode
AND APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
AND LOB_TYPE IN (‘TEMPLATE_SOURCE’, ‘TEMPLATE’);

DELETE FROM XDO_CONFIG_VALUES
WHERE     APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
AND TEMPLATE_CODE = var_templateCode
AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
AND CONFIG_LEVEL = 50;

DBMS_OUTPUT.PUT_LINE (‘Selected template has been ‘ || var_templateCode || ‘ deleted.’);

IF boo_deleteDataDef
THEN
XDO_DS_DEFINITIONS_PKG.DELETE_ROW (RS.DEF_APP_NAME,
RS.DATA_SOURCE_CODE);

DELETE FROM XDO_LOBS
WHERE LOB_CODE = RS.DATA_SOURCE_CODE
AND APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
AND LOB_TYPE IN
(‘XML_SCHEMA’,
‘DATA_TEMPLATE’,
‘XML_SAMPLE’,
‘BURSTING_FILE’);

DELETE FROM XDO_CONFIG_VALUES
WHERE     APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
AND CONFIG_LEVEL = 30;

DBMS_OUTPUT.PUT_LINE (
‘Selected Data Defintion has been ‘ || RS.DATA_SOURCE_CODE || ‘ deleted.’);
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE (
‘Unable to delete XML Publisher Template ‘ || var_templateCode);
DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 200));
END;
/

/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
, , ,

Oracle E-Business Suite 12.2 Now Available to Download from Edelivery

We are very pleased to announce that Oracle E-Business Suite Release 12.2 is generally available for download now:
  • EBS 12.2 represents our most-groundbreaking release in years (Press Release).  It includes new product functionality, new Fusion Middleware and database components, and introduces new tools for installing, configuring, and maintaining E-Business Suite environments:

    Hundreds of new features

    Includes significant enhancements across the integrated suite of business applications spanning enterprise resource planning, human capital management, and supply chain management.  You can find the complete list here:
    Online Patching
    Apply EBS patches while users are still entering transactions and using the E-Business Suite.  Online Patching uses the Oracle Database’s Edition-Based Redefinition feature and other new technologies to allow the E-Business Suite to be updated while the system is still running. You can learn more about Online Patching via our official documentation and this technical webcast.
    WebLogic Server
    Uses Oracle WebLogic Server, which replaces the Oracle Containers for Java (OC4J) application server used in EBS 12.0 and 12.1.  You can learn more about how this improves the system administration experience via our official documentation and this technical webcast.

    Streamlined installation

    • Option for installing EBS 12.2 on to existing database servers.
    • Capability of installation into existing Real Application Clusters environments.
    • Database deployment on Automatic Storage Management (ASM) and other file systems.
    Upgrading to EBS 12.2
    Is there a direct upgrade path from EBS 11i to 12.2?

    Yes, there is a direct upgrade path from EBS 11.5.10.2 to EBS 12.2.  EBS 11.5.10.2 customers do not have to install an intermediary EBS 12 release (such as 12.1.3) before upgrading to EBS 12.2.  EBS 11.5.10.2 customers must have applied the minimum baseline patch requirements for Extended Support as described in Patch Requirements for Extended Support of Oracle E-Business Suite Release 11.5.10 (Document 883202.1). Customers on earlier EBS 11i releases (such as 11.5.7) need to be at the 11.5.10.2 level plus the minimum baseline patch requirements for Extended Support before they can upgrade to EBS 12.2.
    Is there a direct upgrade path from EBS 12.0 to 12.2?

    Yes, there is a direct upgrade path from EBS 12.0.4 and 12.0.6 to EBS 12.2.  EBS 12.0.4 or 12.0.6 customers do not have to install an intermediary EBS 12 release (such as 12.1.3) before upgrading to EBS 12.2.  Customers on earlier EBS 12.0 releases (such as 12.0.3) will need to be at the 12.0.4 or 12.0.6 level before they can upgrade to EBS 12.2.
    Is there a direct upgrade path from EBS 12.1 to 12.2?

    Yes, there is a direct upgrade path from EBS 12.1.1, 12.1.2, and 12.1.3 to EBS 12.2.
    How can I prepare for EBS 12.2?
      More technical references
    /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