, , ,

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

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

How to Create and Register a Concurrent Program using SQL*Loader

When building customization’s for the Oracle E-Business Suite it might be needed to load data from external sources into specific tables. Tables might be seeded tables, i.e. Open Interface tables, or custom tables which you’ve added to a special customization’s database schema.

To load data from external sources into Oracle eBS you might consider using SQL*Loader to accomplish this. SQL*Loader is using comma seperated (csv) files with data and a so called control file (ctl). The control file tells the system how to import the csv file with data. The control file describes the table and columns to be loaded, what the seperator is of the incoming file etc. Next the SQL*Loader program exports a log file to give an overview of the process, a bad file of records that caused errors in the process, and a discard file for records that were not selected during the load.


Starting a SQL*Loader load can be done by command line by executing the below:

sqlldr db_user/db_password@host:port/sid control=control_file_name.ctl log=log_file_name.log

SQL*Loader can also be executed by starting a special concurrent program which you can create in Oracle E-Business Suite. Below the steps how to do this.

1) First of all you need to have a csv file with data – the csv can also contain header information. The header in a csv file can be skipped by adding a special parameter in the control file. Take note of the columns in the csv file.

2) You need a control file – for example see the below control file. There are a big number of commands you can use in the control file to completely have control on how data gets loaded into tables. If you want to know more than contact me on this topic.

SKIP = 1
LOAD DATA
INFILE ‘data_file_name.csv’
BADFILE ‘data_file_name.bad’
REPLACE INTO TABLE XXX.YOUR_CUSTOM_TABLE
FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘
TRAILING NULLCOLS
(
COLUMN1,COLUMN2,COLUMN3,…
)

SKIP=1
Tells SQL*Loader to exclude row 1 in the data file (to exclude the headers)

INFILE
Specifies the name of the incoming data csv file

BADFILE
Determines the bad file generated for any errors occured during the load

REPLACE
This command will first truncate the table and than add the records. If you change this in an APPEND command the records will be added only to the table specified without truncating first

FIELDS TERMINATED BY
Determines the seperator used in the data csv file

OPTIONALLY ENCLOSED BY
Determines an additional enclosing character like for example ” if you’re adding data which contains the column seperator used

TRAILING NULLCOLS
Is used to treat any missing data in the csv file as NULL for the table to be loaded

COLUMN1, COLUMN2, COLUMN3, …
Gives the column names to be loaded

3) Create the SQL*Loader Executable in Oracle E-Business Suite. Go to responsibility System Administrator – Concurrent – Program – Executable.


Give you executable a name, shortname and assign it to your customizations application. A description is optionally. Select Execution Method SQL*Loader to let eBS know SQL*Loader needs to be started. The Execution File Name holds the name of the control file you want to start (exclude the extension ctl here). The control file needs to be located in the bin directory of your customization application.

4) Create the concurrent program in Oracle E-Business Suite. Go to responsibility System Administrator – Concurrent – Program – Define.


Give your concurrent program a name, a short name, assign it to your customization’s application and optionally provide a description. Assign your created executable to the concurrent program.

5) Add parameters to dynamically provide the incoming data csv file. Click on Parameters.


Add Sequence 10 and give the parameter a name. In this we want to provide a full path to the incoming data csv file so we use seeded Value Set 100 Characters to hold the path. Optionally add a default value.

6) When done creating the concurrent program add it to a Concurrent Request Group and start loading data in your tables.

/by