Friday, 6 July 2012


Caching Framework in oracle apps R12 comes with an administration User interface and it is available under the Functional Administrator responsibility. This interface can be used to perform administrative operations including changing the time-out values for cache components, looking at cache usage statistics, and clearing caches.
Here are the steps to clear all Global Cache:
1] Login to Functional Administrator responsibility – then choose Home.
2] Choose the ‘Core Services’ Tab – then the “Caching Framework” Sub-Menu.
3] Proceed to choose ‘Global Configuration’ from the left hand side menu.
4] In the far right choose ‘Clear all Cache’ button.
5] A screen prompts and confirms that the action will clear all cache on the middle tier server – choose Yes. Essentially, this just forces all user sessions to engage and validate – rather than using cached values.
6] A confirmation message is displayed, confirming that all cache has been cleared across middle tiers.
7] Proceed to test and confirm whatever change was made to the preference, profile, etc….
Please note that clearing the OA Framework cache can cause data issues if multiple users are engaged and transacting data in the application at the time cache is cleared. Please use this utility with proper care.
You can also clear cache for specific component. To do that you need to go to Core Services -> Caching Framework -> Tuning. Query the application (for example iProcurement) or by Name or code.  Select the component and clear the cache.

Messages in Oracle Application

What is a message in Oracle Application?

Oracle Application uses the Message Dictionary to store translatable Error and Warning messages that can be used by any programs written in Forms, Reports, Java, or PL/SQL.
These messages mainly provide information about business rule errors, such as missing or incorrect data, and how to resolve them, warn about the consequences of intended actions, inform about the status of an application, pages, or business objects, and indicate that processes and actions are performing or are completed.
By using the messages in the Message Dictionary, you can define standard messages that you can use in all your applications(Oracle Form, Reports, OAF and ADF), provide consistency for messages within and across all your applications, define flexible messages, and change or translate the text of your messages without regenerating or recompiling your application code.

How will you create a Message?

In Application Developer Responsibility, navigate to (N) Application | Messages.
Using that screen, the new messages can be created by entering records. The contents entered in field message text become visible to the end user. The content of the message can optionally have tokens, which act as placeholders for dynamically substituted values at runtime.
You can also create messages from OAF Page. For that-
1] Login to Functional Administrator responsibility – then choose Home.
2] Choose the ‘Core Services’ Tab – then the “Messages” Sub-Menu.

The components of a Message:

Component Name Description
Name Every message must have a unique name. You should include a unique prefix that makes it easier to find your custom messages and that helps to avoid name conflicts with non-custom messages.
Language Select the language that your message is written in.
Application Select the application that the message belongs, this will usually be the custom application.
Current Message Text Message text is required. This is a brief statement of the operation attempted and the problem that occurred as a result, or information that the user needs to know. The maximum field size for messages stored in the Message Dictionary is 240 characters.
Number A unique and persistent message number can be included with each message. When displayed, the number takes the format of (Application Shortname-Number). If the message does not have a message number, the formatted number is not displayed.
Type The message type indicates which message components are applicable, determines whether implicit logging and incident creation occurs, and determines the logging level if the message is logged.
Maximum Length Maximum number of display characters the translators can use to translate the message.
Description Description of the Message.
Alert Category This will allow user interfaces and other programs to filter exception messages based on category. The types are Product, System, Security and User.
Alert Severity This will allow user interfaces and other programs to filter exception messages based on severity. The types can be: Critical, Error or Warning.
Log Severity This group indicates the Log severity levels like: Unexpected, Error, Exception, Event, Procedure, Statement or Off.

About Tokens:

Tokens are identified in the message text by their use of & and all uppercase letters. The token values are supplied at runtime by the code that raises the message. For example, the following token &FIELD_NAME is replaced by a field when the user receives the error message on their screen:
“A Value must be entered for &FIELD_NAME.
Becomes: “A Value must be entered for PO Number.”.

Table Used by Messages Dictionary:

FND_NEW_MESSAGES stores application messages for Message Dictionary. Each row includes the application to which the message belongs, the language the message is in, the message name, the message text, and the message number. You need one row for each application message in each of the language.

APIs to Set, Retrieve, Clear the messages:

Use the Message Dictionary APIs to retrieve a Message Dictionary message. The PL/SQL methods are in the FND_MESSAGE package and the Java methods are in the messageService package. There are many functions and procedures in the package- FND_MESSAGE. However I have given the details of three most used procedures below.
procedure SET_NAME(APPLICATION in varchar2, NAME in varchar2)
In Database Server, this Sets a message name in the global area without actually retrieving the message from Message Dictionary.
procedure SET_TOKEN(TOKEN     in varchar2,
                        VALUE     in varchar2,
                        TRANSLATE in boolean default false)
This procedure defines a message token with a value. In Database Server, SET_TOKEN adds a token/value pair to the global area without actually doing the substitution. Call FND_MESSAGE.SET_TOKEN once for each token/value pair in a message.
function GET return varchar2
This function gets a translated and token substituted message from the message dictionary database. It returns NULL if the message cannot be found. If this function is called from a stored procedure on the database server side, the message is retrieved from the Message Dictionary table. If the function is called from a form or forms library, the message is retrieved from the messages file on the forms server.

How to use message in PL/SQL Concurrent Program:

2fnd_message.set_name ('XXSCM', 'XXSCM_MANDATORY_FIELD');
3fnd_message.set_token('FIELD_NAME', 'PO Number');
4--Now get the final string
5l_message := fnd_message.get;
6--Display the message text in output of concurrent program
7fnd_file.put_line(fnd_file.OUTPUT, l_message);

How to use message in Oracle Forms:

1fnd_message.set_name('XXSCM', 'XXSCM_MANDATORY_FIELD');
2fnd_message.set_token('FIELD_NAME', 'PO Number');;

How to use message in OA Framework Controller:

1String sReturnMsg = oapagecontext.getMessage("XXSCM", " XXSCM_MANDATORY_FIELD ", new MessageToken[] {new MessageToken(" FIELD_NAME ", "PO Number") });

Example of retrieving message from the Stack:

01-- setting INVALID_USER as the current message
02fnd_message.set_name('FND', 'INVALID_USER');
03-- setting value for token NAME for INVALID_USER message
04fnd_message.set_token('NAME', 'TESTUSER');
05-- saving the current message onto stack
07-- setting LOGIN_FAILED as the current message
08fnd_message.set_name('FND', 'LOGIN_FAILED');
09-- saving the current message onto stack
11-- poping one message out of stack and set it as the current message
13-- get the translated and token subsituted LOGIN_FAILED message
14-- then clear the current message
15msg := fnd_message.get;
16-- poping one message out of stack and set it as the current message
18-- get the translated and token subsituted INVALID_USER message
19-- then clear the message
20msg := fnd_message.get;

FNDLOAD for Messages:

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXSCM_MANDATORY_FIELD_MSG.ldt

SQL related to Oracle Application Messages:

01SELECT m.message_name,
02  m.message_text,
03  m.message_number,
04  a.application_short_name
07WHERE upper(m.message_text) LIKE upper('%&Enter_Message_Text%')
08AND m.language_code  = 'US'
11SELECT m.message_name,
12  m.message_text,
13  m.message_number,
14  a.application_short_name
17WHERE m.message_name LIKE '%&Enter_Message_Name%'
18AND m.language_code  = 'US'

API to Load Values into Value Sets

002----------------------------Local Variables---------------------------
003   l_enabled_flag             VARCHAR2 (2);
004   l_summary_flag             VARCHAR2 (2);
005   l_who_type                 FND_FLEX_LOADER_APIS.WHO_TYPE;
006   l_user_id                  NUMBER                := FND_GLOBAL.USER_ID;
007   l_login_id                 NUMBER                := FND_GLOBAL.LOGIN_ID;
008   l_value_set_name           FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_NAME%TYPE;
009   l_value_set_value          FND_FLEX_VALUES.FLEX_VALUE%TYPE;
012   l_value_set_name             :='VALUE_SET_NAME';
013   l_value_set_value            :='VALUE_SET_VALUE';
014   l_enabled_flag               := 'Y';
015   l_summary_flag               := 'N';
016   l_who_type.created_by        := l_user_id;
017   l_who_type.creation_date     := SYSDATE;
018   l_who_type.last_updated_by   := l_user_id;
019   l_who_type.last_update_date  := SYSDATE;
020   l_who_type.last_update_login := l_login_id;
022     fnd_flex_loader_apis.up_value_set_value
023                  (p_upload_phase               => 'BEGIN',
024                   p_upload_mode                => NULL,
025                   p_custom_mode                => 'FORCE',
026                   p_flex_value_set_name        => l_value_set_name,
027                   p_parent_flex_value_low      => NULL,
028                   p_flex_value                 => l_value_set_value,
029                   p_owner                      => NULL,
030                   p_last_update_date           => TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
031                   p_enabled_flag               => l_enabled_flag,
032                   p_summary_flag               => l_summary_flag,
033                   p_start_date_active          => TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
034                   p_end_date_active            => NULL,
035                   p_parent_flex_value_high     => NULL,
036                   p_rollup_flex_value_set_name => NULL,
037                   p_rollup_hierarchy_code      => NULL,
038                   p_hierarchy_level            => NULL,
039                   p_compiled_value_attributes  => NULL,
040                   p_value_category             => 'VALUE_SET_NAME',
041                   p_attribute1                 => '40912',
042                   p_attribute2                 => NULL,
043                   p_attribute3                 => NULL,
044                   p_attribute4                 => NULL,
045                   p_attribute5                 => NULL,
046                   p_attribute6                 => NULL,
047                   p_attribute7                 => NULL,
048                   p_attribute8                 => NULL,
049                   p_attribute9                 => NULL,
050                   p_attribute10                => NULL,
051                   p_attribute11                => NULL,
052                   p_attribute12                => NULL,
053                   p_attribute13                => NULL,
054                   p_attribute14                => NULL,
055                   p_attribute15                => NULL,
056                   p_attribute16                => NULL,
057                   p_attribute17                => NULL,
058                   p_attribute18                => NULL,
059                   p_attribute19                => NULL,
060                   p_attribute20                => NULL,
061                   p_attribute21                => NULL,
062                   p_attribute22                => NULL,
063                   p_attribute23                => NULL,
064                   p_attribute24                => NULL,
065                   p_attribute25                => NULL,
066                   p_attribute26                => NULL,
067                   p_attribute27                => NULL,
068                   p_attribute28                => NULL,
069                   p_attribute29                => NULL,
070                   p_attribute30                => NULL,
071                   p_attribute31                => NULL,
072                   p_attribute32                => NULL,
073                   p_attribute33                => NULL,
074                   p_attribute34                => NULL,
075                   p_attribute35                => NULL,
076                   p_attribute36                => NULL,
077                   p_attribute37                => NULL,
078                   p_attribute38                => NULL,
079                   p_attribute39                => NULL,
080                   p_attribute40                => NULL,
081                   p_attribute41                => NULL,
082                   p_attribute42                => NULL,
083                   p_attribute43                => NULL,
084                   p_attribute44                => NULL,
085                   p_attribute45                => NULL,
086                   p_attribute46                => NULL,
087                   p_attribute47                => NULL,
088                   p_attribute48                => NULL,
089                   p_attribute49                => NULL,
090                   P_ATTRIBUTE50                => NULL,
091                   p_flex_value_meaning         => l_value_set_value,
092                   p_description                => NULL
093                   );
094      COMMIT;
097      WHEN OTHERS
098      THEN
099         DBMS_OUTPUT,PUT_LINE('Error is ' || SUBSTR (SQLERRM, 1, 1000));
100   END;

How to Use Global Variables in Form Personalization?

Here let’s say the requirement is to run a concurrent program through a custom menu from an oracle form and in that concurrent program parameters, we need to fetch some values from the oracle form itself. In that case we need to first assign the form values to some Global Variables and then use them in the concurrent program parameters. Here are the Steps:
1] Go to Form Personalization for that Form (Use: Help > Diagnostics > Custom Code > Personalize)
2] Create one custom menu (ex. SPECIAL15) on the triggering event: WHEN-NEW-FORM-INSTANCE.
3] On the triggering event: SPECIAL15, do the following actions:
  • Select Type as ‘Property’ and select Object Type as ‘Global Variable’.
  • Give a name to the global variable in the ‘Target Object’ tab. (Ex. G_ITEM_NAME, G_ORG_CODE)
  • Select the Property Name as ‘VALUE’.
  • In the value tab- Give ‘=:Block_name.Field_name’ (Use: Help > Diagnostics > Examine). This is the value which you want to put in the global variable.
4] Select Type as ‘Builtin’ and Select Builtin Type as ‘Launch SRS Form’.
5] Select your Concurrent Program in the ‘Program Name’ tab. Please note that you need to assign the Function – ‘Requests: Submit’ to the Main Menu of the responsibility to which your concurrent program is attached.
  • Use: System Administrator > Security > Responsibility > Define to find the Menu Name.
  • Go to System Administrator > Application > Menu and add the Function – ‘Requests: Submit’ at the end and Save.
  • Also don’t forget to attach the concurrent program to proper Request Group.
6] Go to the concurrent program parameters. Select the Default Type as SQL Statement in the Validation tab and give the default values as
  • select :GLOBAL.G_ITEM_NAME from dual
  • select :GLOBAL.G_ORG_CODE from dual
7] Save and Test the functionality.
An Alternate Way:
Create a PL/SQL function in the database that calls fnd_request.submit_request and commits in an AUTONOMOUS TRANSACTION. The function returns a message to the user, with the request_id.
1] Go to Form Personalization for that Form (Use: Help > Diagnostics > Custom Code > Personalize)
2] Create one custom menu (ex. SPECIAL15) on the triggering event: WHEN-NEW-FORM-INSTANCE.
3] On the triggering event: SPECIAL15, do the following actions:
  • Define a global variable for the message (Ex. XX_CONC_PROG_RESULT)
  • Assign the above global variable the following value: =SELECT <Your Custom PL/SQL Function> from dual. You can pass parameters to the function as: Block_name.Field_name.
  • Define a message to show as =:GLOBAL.XX_CONC_PROG_RESULT

How to migrate Oracle Alerts?

1] Using FNDLOAD utility:
Here first DOWNLOAD the custom alert from the source instance, copy the ldt file to the new instance and then UPLOAD to the destination instance.
Command to download:
FNDLOAD apps_user_name/apps_password 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct my_file.ldt ALR_ALERTS APPLICATION_SHORT_NAME=’XXCUST’ ALERT_NAME=<Alert name to download>
Here are some additional parameters that can also be passed are: 
Command to upload:
FNDLOAD apps_user_name/apps_password 0 Y UPLOAD  $ALR_TOP/patch/115/import/alr.lct my_file.ldt – CUSTOM_MODE=FORCE
2] Using Alert Manager Responsibility:
You can use the Transfer Alert Definitions window to transfer an alert definition to another database, or make a copy of an existing alert. 
1. From the Menu, choose Tools > Transfer Alert.
2. In the Source Alert block of the Transfer Alert Definition window, enter:
* Application name associated with the alert to be transferred
* Alert name to be transferred
* Username and password for the database where the Alert to transfer resides
Include any necessary SQL*Net syntax to indicate where your database resides.
SQL*Net syntax may be used. The different databases need to be defined in the
$TNS_ADMIN/tnsnames.ora file.
Example of: username/ password@ SQL*Net_syntax
Note: Database Links are not supported in Applications
3. Similarly, in the Destination Alert block, enter:
* Application name for the new Alert.
* Alert name for the new Alert.
* Username and password for the new database location.
4. Click Transfer button to complete the alert transfer.
NOTE: This process works for NEW Alerts that do not exist. If the Alert exists already in the Destination location, you will receive the error:
APP-ALR-04016: The alert <alert_name> already exists on the destination database account.
  • Oracle Alert User’s Guide
  • How to transfer alert definition from One Instance to another instance using cmdline script [ID 400295.1]

How to design Periodic Alert to send emails?

1] Go to ‘Alert Manager’ responsibility and navigate Alert > Define.
  • Enter the name of the application that will own the alert
  • Enter a suitable Name of the alert (up to 50 characters), and give it a meaningful description (up to 240 characters).
  • Select a frequency for your periodic alert. You can choose from nine frequency options:
  1. On Demand
  2. On Day of the Month
  3. On Day of the Week
  4. Every N Calendar Days
  5. Every Day
  6. Every Other Day
  7. Every N Business Days
  8. Every Business Day
  9. Every Other Business Day
  • Choose ‘On Demand’ frequency when you are developing a periodic alert so that you can test your alert at any time you want. When you will sure that the alert is working fine, then you can change the frequency as per business need.
  • Depending on the frequency you choose in the previous step, the Start Time and End Time fields become enabled.  You may also specify the number of times within a 24-hour period that Oracle Alert checks your alert.
  • Specify a value in the Keep _ Days field to indicate the number of days of exceptions, actions, and response actions history you want to keep for this alert.
  • Specify a value in the End Date field if you want to disable your alert by a certain date.
  • Enter a SQL Select statement that retrieves all the data your alert needs to perform the actions you plan to define.
  • Your periodic alert Select statement must include an INTO clause that contains one output for each column selected by your Select statement.
  • Identify any inputs with a colon before the name, for example, :INPUT_NAME.
  • Identify any outputs with an ampersand (&) before the name, for example, &OUTPUT_NAME.
  • Do not use set operators in your Select statement.
  • You can use PL/SQL functions in your Select statement to fetch complex business logic.
Click on the ‘Verify’ button to check the select statement is correct.
Click on the ‘Run’ button to execute the Select statement.
Once you are satisfied with the SQL statement, save your work.
2] You can view all the input and output column details in ‘Alert Details’ Tab. The Alert Details window includes information such as which Application installations you want the alert to run against, what default values you want your inputs variables to use, and what additional characteristics you want your output variables to have.
3] After you define your alert you need to create the actions you want your alert to perform. For that click on the ‘Actions’ tab.
  • Enter a name (up to 80 characters) and description (up to 240 characters) for your alert action.
  • Select a level for your action: Detail, Summary, or No Exception.
  1. Detail action: performs once for each individual exception found
  2. Summary action: performs once for all exceptions found
  3. No exception action: performs when no exceptions are found.
4] Click on ‘Action Details’ tab to display the Action Details window.
  • Select the Action Type field as ‘Message’ if you want to send emails. Other action types are: Concurrent Program, Operating System Script and SQL Script.
  • Specify the electronic mail IDs of the recipients you want to send your message to in the To field.
  • If you list more than one recipient in any of these recipient fields, separate each recipient by a space, or a comma, or a combination of the two.
  • You can enter as many recipients as you want, up to 240 characters.
  • You can also enter alert outputs or response variables in any of the alert detail fields. Oracle Alert automatically substitutes the associated output value when checking the alert or the associated response variable value when reading the response.
  • Save your changes.
5] Click on ‘Action Sets’ tab in the main Alert Window.
  • Once you create your alert actions, you must include them in an enabled action set for Oracle Alert to perform during an alert check. An action set can include an unlimited number of actions and any combination of actions.
  • Enter a Sequence number that lets you order the execution of action sets during an alert check.
  • Give any suitable name and description.
  • Check Suppress Duplicates if you want Oracle Alert to suppress the actions in this action set if the exception found is a duplicate that occurred during the last alert check.
6] Click on ‘Action Set Details’ tab.
  • Go to ‘Members’ tab.
  • Find and attach the action that is created in Step 3.
  • Save the changes. 
7] Since it is an ‘On Demand’ periodic alert, we can run the alert at any time we want. For that go to Request > Check and enter the alert details. Then click on ‘Submit Request’.
  • This will fire one concurrent program which you can view by going through the navigation:  Request > View
  • View the Log and Output files of the concurrent program to find that the alert is fired successfully.
Done…check your mailbox and you should get emails that are sent from Oracle Alerts.

Utility APIs for Concurrent Processing


This API Returns the Status of a concurrent request. It also returns the completion text if the request is already completed. The return type is Boolean (Returns TRUE on successful retrieval of the information, FALSE otherwise).
1function get_request_status(request_id     IN OUT NOCOPY number,
2                    appl_shortname IN varchar2 default NULL,
3                    program        IN varchar2 default NULL,
4                    phase      OUT NOCOPY varchar2,
5                    status     OUT NOCOPY varchar2,
6                    dev_phase  OUT NOCOPY varchar2,
7                    dev_status OUT NOCOPY varchar2,
8                    message    OUT NOCOPY varchar2) return boolean;
The parameters are:
  • REQUEST_ID: Request ID of the program to be checked.
  • APPL_SHORTNAME: Short name of the application associated with the program. The default is NULL.
  • PROGRAM: Short name of the concurrent program. The default is NULL.
  • PHASE: Request phase.
  • STATUS: Request status.
  • DEV_PHASE: Request phase as a string constant.
  • DEV_STATUS: Request status as a string constant.
  • MESSAGE: Request completion message.


This API waits for the request completion, then returns the request phase/status and completion message to the caller. It goes to sleep between checks for the request completion. The return type is Boolean (Returns TRUE on successful retrieval of the information, FALSE otherwise).
1function wait_for_request(request_id IN number default NULL,
2        interval   IN  number default 60,
3        max_wait   IN  number default 0,
4        phase      OUT NOCOPY varchar2,
5        status     OUT NOCOPY varchar2,
6        dev_phase  OUT NOCOPY varchar2,
7        dev_status OUT NOCOPY varchar2,
8        message    OUT NOCOPY varchar2) return  boolean;
The parameters are:
  • REQUEST_ID: Request ID of the request to wait on. The default is NULL.
  • INTERVAL: Number of seconds to wait between checks. The default is 60 seconds.
  • MAX_WAIT: Maximum number of seconds to wait for the request completion. The default is 00 seconds.
  • PHASE: User-friendly Request phase.
  • STATUS: User-friendly Request status.
  • DEV_PHASE: Request phase as a constant string.
  • DEV_STATUS: Request status as a constant string.
  • MESSAGE: Request completion message.
There are few other useful apis too.
  • FND_CONCURRENT.SET_COMPLETION_STATUS: Called from a concurrent request to set its completion status and message.
  • FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS: Returns the print options for a concurrent request.
  • FND_CONCURRENT.GET_SUB_REQUESTS: Get all sub-requests for a given request id. For each sub-request it provides request_id, phase,status, developer phase , developer status and completion text.
  • FND_CONCURRENT.Cancel_Request: It cancels a given Concurrent Request.

How to call a Concurrent Program from a Special Menu Item?

Well, we can run our concurrent programs from a Special Menu Item, and if you have a requirement of this sort, you can use the steps below to use Form Personalization Builtin to achieve this task in couple of minutes.
1] First create the Special Menu Item wherever required through Form Personalization. Here I have added a Special Menu Item called ‘Assign Item to a Subinventory’ to the Form-‘INVIDITM’. For that assign the Trigger Event as ‘WHEN-NEW-FORM-INSTANCE’.
2] In Actions Tab, choose the type as ‘MENU’ and select the Menu Entry and give a Proper Menu Label.
3] Create the Concurrent Program which you want to attach to this custom menu item. Once created, assign the program to the Request Group of the Responsibility. Also it is required to add the ‘Lunch SRS Form’ Function (Requests: Submit) to the main menu of that responsibility. If you don’t do this step you may get Form Personalization error in later steps.
4] Create one more entry in Form Personalization window with Trigger Event as your custom menu item.
5] Here select the Actions Type as ‘Builtin’ and Builtin Type as ‘Lunch SRS Form’. In the program name give the name of your concurrent program.
6] Validate and Apply Now
Once done, you will be able to view the custom menu item in the Form and when you will click it, it opens the SRS Form with your concurrent program.

Query to find the Request Group of a Concurrent Program

Many a times we need to find out the Request Group of a Concurrent Program. In such cases the below query will be a useful one.
02  RG.APPLICATION_ID "Request Group Application ID",
03  RG.REQUEST_GROUP_ID "Request Group - Group ID",
06  rgu.unit_application_id,
07  rgu.request_group_id "Request Group Unit - Group ID",
08  rgu.request_unit_id,cp.concurrent_program_id,
09  cp.concurrent_program_name,
10  cpt.user_concurrent_program_name,
11  DECODE(rgu.request_unit_type,'P','Program','S','Set',rgu.request_unit_type) "Unit Type"
13  fnd_request_groups rg,
14  fnd_request_group_units rgu,
15  fnd_concurrent_programs cp,
17WHERE rg.request_group_id = rgu.request_group_id
18  AND rgu.request_unit_id = cp.concurrent_program_id
19  AND cp.concurrent_program_id = cpt.concurrent_program_id
20  AND cpt.user_concurrent_program_name =’<Your_Concurrent_Program_Name>’;

Common Debugging Framework in Oracle Application

Debugging plays a very crucial rule when you develop something and in oracle application also it is no different. Prior to Oracle E-Business Suite 11i, each module used in Oracle EBS had its own debugging methodology. However, with the Common Debugging Framework initiative, Oracle has introduced a common set of profile options and tables that can be used to debug any application across all technologies used in oracle apps..
Starting in 11.5.10, FND has incorporated a debugging technique to enable debug messages to get stored into the table FND_LOG_MESSAGES. This method was introduced in 11.5.10 and it is available in subsequent releases.
There are few profile options to enable and retrieve the debug messages. Here are those profile options.
Profile Name Suggested value Comments
FND: Debug Log Enabled YES This turns the debugging feature on
FND: Debug Log Filename  NULL Use when you want debug messages to get stored to a file
FND: Debug Log Level STATEMENT Following are options listed from least to most detailed debugging : Unexpected, Error, Exception, Event, Procedure, Statement
FND: Debug Log Module % Indicate what modules to debug. You can use something like ‘ar%’ or even  ’%arp_rounding%’ to limit modules debugged
Sample setting to debug everything:
FND: Debug Log Enabled YES
FND: Debug Log Filename NULL
FND: Debug Log Level STATEMENT
FND: Debug Log Module %
Sample setting to debug ONLY Receivables:
FND: Debug Log Enabled YES
FND: Debug Log Filename NULL
FND: Debug Log Level STATEMENT
FND: Debug Log Module ar% 
Sample Program:
01Create or replace PACKAGE BODY xx_debug_pkg
03   g_level_statement      CONSTANT NUMBER         := fnd_log.level_statement;
04   g_level_procedure      CONSTANT NUMBER         := fnd_log.level_procedure;
05   g_level_event          CONSTANT NUMBER         := fnd_log.level_event;
06   g_level_exception      CONSTANT NUMBER         := fnd_log.level_exception;
07   g_level_error          CONSTANT NUMBER         := fnd_log.level_error;
08   g_level_unexpected     CONSTANT NUMBER         := fnd_log.level_unexpected;
09   g_default_module       CONSTANT VARCHAR2 (240) := 'Any_Package_Name';
10   g_level_log_disabled   CONSTANT NUMBER         := 99;
11   g_log_level                     NUMBER;
12   g_log_enabled                   BOOLEAN;
15      p_log_level    IN   NUMBER,
16      p_module       IN   VARCHAR2,
17      p_message      IN   VARCHAR2,
18      p_request_id   IN   NUMBER
19   )
20   IS
21   BEGIN
22      fnd_profile.put ('AFLOG_MODULE', g_default_module);
24      IF ((p_module IS NULL) OR (p_message IS NULL))
25      THEN
26         fnd_file.put_line
27            (fnd_file.LOG,
28                'Error in package:'
29             || g_default_module
30             || ' , module : debug :Parameters p_module and  p_message cant be null'
31            );
32      ELSE
33         BEGIN
34            IF (NVL ((fnd_profile.VALUE ('AFLOG_ENABLED')), 'N') = 'Y')
35            THEN
37                  IF (p_message IS NOT NULL AND p_log_level >= g_log_level)
38                  THEN
39                     fnd_log_repository.init;
40                     fnd_log.STRING (p_log_level, g_default_module,
41                                     p_message);
42               END IF;
43            END IF;
44         EXCEPTION
45            WHEN OTHERS
46            THEN
47               fnd_file.put_line (fnd_file.LOG,
48                                     'Error in package: '
49                                  || g_default_module
50                                  || ', module : debug, at check point 1 :'
51                                  || SQLERRM
52                                 );
53         END;
54      END IF;
57      THEN
58         fnd_file.put_line (fnd_file.LOG,
59                               'Error in package:'
60                            || g_default_module
61                            || ' , module : debug, at check point 2 :'
62                            || SQLERRM
63                           );
65END xx_debug_pkg;
Debugging an API from SQL*Plus
You can enable FND logging for just one single PL/SQL API. Here is how we can do it from SQL*Plus:
1. From SQL*Plus, issue the following:
1fnd_global.apps_initialize(fnd_user_id, fnd_resp_id, fnd_appl_id);
2fnd_profile.put('AFLOG_ENABLED', 'Y');
3fnd_profile.put('AFLOG_MODULE', '%');
4fnd_profile.put('AFLOG_LEVEL','1'); -- Level 1 is Statement Level
2. Call the desired API.
3. Call step 1 again, but this time set AFLOG_ENABLED to N.
Since the debugging routine will start writing messages to the table, we want to know which messages pertain to our test. If you are tracking the debug messages for a concurrent request, note down the Concurrent Request id. Otherwise, note down current max value of log sequence retrieved as follows:
If you are debugging a concurrent process:
1SELECT log.module , log.message_text message
2FROM fnd_log_messages log,
3            fnd_log_transaction_context con
4WHERE con.transaction_id = < request_id >
5AND con.transaction_type = 'REQUEST'
6AND con.transaction_context_id = log.transaction_context_id
7ORDER BY log.log_sequence;
Otherwise use this:
1SELECT module, message_text
2FROM fnd_log_messages
3WHERE log_sequence > &max_log_from_step2
4ORDER BY log_sequence;
Debugging OA pages 
  a. Enable the profile option: FND: Debug Log Enabled — Set to Yes
  b. Enable the profile option: FND: Debug Log Level – Set to Statement level
  c. Add the below piece of code in your OA page 
1boolean isLoggingEnabled = pageContext.isLoggingEnabled(OAFwkConstants.STATEMENT);
2 if (isLoggingEnabled)
3     {
4       pageContext.writeDiagnostics(this, "your log statement", OAFwkConstants.STATEMENT);
5     }
To see log stmt on browser append below string to browser URL and click on enter
Reference MOS Notes:
  • How to enable and retrieve FND debug log messages [ID 433199.1]
  • How Can Trace and Debug Be Turned On For A Concurrent Request? [ID 759389.1]
  • How to Collect an FND Diagnostics Trace (aka FND: Debug) [ID 372209.1]
  • How to Obtain Debug Log in R12 [ID 787727.1]

1 comment: