Logging in OAF Pages – A Technical Note!

The Logging Framework in Oracle Apps provides the ability to store and retrieve log messages for debugging, error reporting, and alerting purposes. We can use it for any OAF Page development and Customization.

Using Oracle AOL Profile Options to Configure Logging

You can configure logging by setting Oracle Application Object Library (FND) profile options.

The available levels are Site, Application, Responsibility, and User. User settings override Responsibility settings, Responsibility settings override Application settings, and Application settings override Site settings.

Here is a summary of the impacts of the different profile option levels:

• User: Affects only the given user.
• Application: Affects all users for the specific application.
• Responsibility: Affects all users in any application for that responsibility.
• Site: Affects all users, applications, and responsibilities.

Note: When setting up logging at the Site level, Oracle strongly recommend that you set the logging level to UNEXPECTED. ERROR or EXCEPTION. Also remember to return the profiles to their usual values after debugging has been completed.

Where is the debug message stored, once the logging is turned on?

Debug messages are stored in a table called FND_LOG_MESSAGES.

If you want to have logging from your concurrent program, you can write as below in your PL/SQL Code.

fnd_log.STRING(log_level => fnd_log.level_statement
                             ,module => 'xxpcm.packagename.procedurename'
                           ,message => 'You debug message here');

From OAF java code, you can write like

pageContext.writeDiagnostics(“xxpcm.oracle.apps.pa.ci.webui”, “Your debug message here”, 1);

Using Logging to Screen

In addition to the above methods where log messages are written to a file or the database, Logging to Screen provides:

• The ability to enable logging on a per HTTP request or per HTTP session basis.
• Dynamic configuration which does not require restarting any servers or changing any log profiles.
• A convenient lightweight mechanism to diagnose performance issues. Each message is timestamped to the millisecond.

If Logging to Screen is enabled, then the Java log messages generated for a particular HTTP Request-Response are buffered in memory and appended to the end of the generated HTML page. However this feature does not affect any existing configurations of file or database logging. File or database logging continues to behave per the configured middle tier log properties and/or log profile values.

Note that this mechanism currently provides only Java layer messages. Regular file or database logging should be used if messages from other layers (e.g., PL/SQL) are needed.

Enabling Logging to Screen in Oracle Application Framework Pages

For security reasons, oracle has made this feature only accessible if the “FND: Diagnostics” Profile is set to “Yes“.

1] First go to the page and click the Diagnostics button.

After you select the Diagnostics button to navigate to the Diagnostics page, you can select:

• Show Log – It directs you to the Oracle Applications Manager where you can view a “snapshot” of your Oracle E-Business Suite system.
• Show Log on Screen – It allows you to specify a log level and display Java log messages for a particular HTTP Request-Response at the end of the current page.
• Set Trace Level – It displays the Set Trace page where you can specify the level of information to collect in a database trace.
• Show Pool Monitor – It displays the Application Pool Monitor where you view different aspects of runtime and configuration information about the JVM.

2] Select Show Log to Screen from the drop-down list.

Caution: If you set the default site-level logging level to STATEMENT or PROCEDURE, a decrease in system performance could result. Under that configuration, the large amount of generated log messages might significantly slow down the system. Furthermore, if the site-level logging level is set to a low severity for a long time, then the FND_LOG_MESSAGES table could potentially run out of space.

For High Volumes

For high load, high volume scenarios, you can log middle-tier messages to a local file, which is faster than logging to a remote database.

Purging Log Messages

You should periodically delete old log messages to account for the space limitations of the database table. In addition, you should periodically rotate log files.

There are several ways to purge log messages. They are described below:

1] Using a Concurrent Program

The concurrent program “Purge Debug Log and System Alerts” (Short name: FNDLGPRG) is the recommended way to purge messages. This program purges all messages up to the specified date, except messages for active transactions (new or open alerts, active ICX sessions, concurrent requests, and so on). This program is by default scheduled to run daily and purge messages older than 7 days. Internally this concurrent program invokes the FND_LOG_ADMIN APIs, which are described below.

2] Using PL/SQL

You can use the FND_LOG_ADMIN PL/SQL package to delete log messages.

fnd_log_admin apis:

Name	Type	Description
delete_by_date_i	Procedure	This routine is used as a concurrent program. Nobody besides the concurrent manager should call it.
delete_by_user	Function	Delete all log messages for a particular user
delete_by_session	Function	Delete all log messages for a particular session
delete_by_user_session	Function	Delete all log messages for that match both user and session
delete_by_module	Function	Delete all log messages that are “like” module
delete_by_date_range	Function	Delete all messages between the specified dates. passing null means unlimited; null for both deletes all rows.
delete_by_max_level	Function	Deletes messages at level and all levels below.
delete_all	Function	Delete all messages
delete_by_sequence	Function	Delete all log messages based on sequenceid

For example:

SET SERVEROUTPUT ON
declare
    l_del_rows_cnt NUMBER;
BEGIN
l_del_rows_cnt := fnd_log_admin.delete_all;
DBMS_OUTPUT.PUT_LINE(l_del_rows_cnt || ' rows deleted');
END;

OAException Message and Dialog Page in OA Framework

You can use OAException (or any of its subclasses) to display a message on an OA Framework page and the OA Framework automatically displays an error message at the top of the current page.

You can display the following standard kinds of messages at the top of a page:

• Error
• Warning
• Confirmation
• Information

You can explicitly display a message box of any type using the following code in your controller.

OAException message = new OAException("You cannot create a new change order when a Draft version already exits.",OAException.ERROR);
pageContext.putDialogMessage(message);

Here you need to construct an oracle.apps.fnd.framework.OAException object and set the kind of message you want (other options are OAException.WARNING, OAException.INFORMATION and OAException.CONFIRMATION). Then you can simply identify this exception for display when the page renders by calling the OAPageContext.putDialogMessage() method.

If — after you call putDialogMessage() in your processFormRequest() method — you want to forward to the current page or another page and display the message at the top of the new target page, you need to call the appropriate oracle.apps.fnd.framework.webui.OAPageContext forwardImmediately*() method. The OA Framework immediately stops processing the page and issues a forward before displaying the messages.

You can register or throw multiple exceptions; the OA Framework combines them into a single message box using the following rules:

• Since an error is more important than a warning, the message box is titled “Error” if both errors and warnings exist.
• Confirmations and errors cannot be shown together. In this case, the OA Framework simply ignores the confirmation message(s).
• You can, however, show confirmations with warnings. The message box is titled “Confirmation,” and it contains both types of messages.

Show the Exception Message on a Dialog Page

You can display an exception as a message in a dialog page using the APIs in the oracle.apps.fnd.framework.webui.OADialogPage class and oracle.apps.fnd.framework.webui.OAPageContext interface.

The OADialogPage class holds properties for the generic dialog page. To create a dialog page object, first use the constructors to instantiate the basic properties, then use the setter methods provided in the class to set additional properties.

To navigate (redirect) to a dialog page, use the OAPageContext.redirectToDialogPage methods. The OAPageContext interface contains the context and state information specific for a client request.

// To Diaplay the Exception on a Dialog page
OAException message = new OAException("You cannot create a new change order when a Draft version already exits.");
OADialogPage dialogPage = new OADialogPage(OAException.ERROR, message, null, "",null);

dialogPage.setOkButtonToPost(true);
dialogPage.setOkButtonLabel("Ok");

dialogPage.setPostToCallingPage(true);
java.util.Hashtable formParams = new java.util.Hashtable(1);
dialogPage.setFormParameters(formParams);
pageContext.redirectToDialogPage(dialogPage);

If you want 2 buttons (Say Cancel and Ok), then put “” instead of null in the OADialogPage dialogPage = new OADialogPage line.

API to Load Values into Value Sets

DECLARE
----------------------------Local Variables---------------------------
   l_enabled_flag             VARCHAR2 (2);
   l_summary_flag             VARCHAR2 (2);
   l_who_type                 FND_FLEX_LOADER_APIS.WHO_TYPE;
   l_user_id                  NUMBER                := FND_GLOBAL.USER_ID;
   l_login_id                 NUMBER                := FND_GLOBAL.LOGIN_ID;
   l_value_set_name           FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_NAME%TYPE;
   l_value_set_value          FND_FLEX_VALUES.FLEX_VALUE%TYPE;
BEGIN

   l_value_set_name             :='VALUE_SET_NAME';
   l_value_set_value            :='VALUE_SET_VALUE';
   l_enabled_flag               := 'Y';
   l_summary_flag               := 'N';
   l_who_type.created_by        := l_user_id;
   l_who_type.creation_date     := SYSDATE;
   l_who_type.last_updated_by   := l_user_id;
   l_who_type.last_update_date  := SYSDATE;
   l_who_type.last_update_login := l_login_id;

     fnd_flex_loader_apis.up_value_set_value
                  (p_upload_phase               => 'BEGIN',
                   p_upload_mode                => NULL,
                   p_custom_mode                => 'FORCE',
                   p_flex_value_set_name        => l_value_set_name,
                   p_parent_flex_value_low      => NULL,
                   p_flex_value                 => l_value_set_value,
                   p_owner                      => NULL,
                   p_last_update_date           => TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
                   p_enabled_flag               => l_enabled_flag,
                   p_summary_flag               => l_summary_flag,
                   p_start_date_active          => TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
                   p_end_date_active            => NULL,
                   p_parent_flex_value_high     => NULL,
                   p_rollup_flex_value_set_name => NULL,
                   p_rollup_hierarchy_code      => NULL,
                   p_hierarchy_level            => NULL,
                   p_compiled_value_attributes  => NULL,
                   p_value_category             => 'VALUE_SET_NAME',
                   p_attribute1                 => '40912',
                   p_attribute2                 => NULL,
                   p_attribute3                 => NULL,
                   p_attribute4                 => NULL,
                   p_attribute5                 => NULL,
                   p_attribute6                 => NULL,
                   p_attribute7                 => NULL,
                   p_attribute8                 => NULL,
                   p_attribute9                 => NULL,
                   p_attribute10                => NULL,
                   p_attribute11                => NULL,
                   p_attribute12                => NULL,
                   p_attribute13                => NULL,
                   p_attribute14                => NULL,
                   p_attribute15                => NULL,
                   p_attribute16                => NULL,
                   p_attribute17                => NULL,
                   p_attribute18                => NULL,
                   p_attribute19                => NULL,
                   p_attribute20                => NULL,
                   p_attribute21                => NULL,
                   p_attribute22                => NULL,
                   p_attribute23                => NULL,
                   p_attribute24                => NULL,
                   p_attribute25                => NULL,
                   p_attribute26                => NULL,
                   p_attribute27                => NULL,
                   p_attribute28                => NULL,
                   p_attribute29                => NULL,
                   p_attribute30                => NULL,
                   p_attribute31                => NULL,
                   p_attribute32                => NULL,
                   p_attribute33                => NULL,
                   p_attribute34                => NULL,
                   p_attribute35                => NULL,
                   p_attribute36                => NULL,
                   p_attribute37                => NULL,
                   p_attribute38                => NULL,
                   p_attribute39                => NULL,
                   p_attribute40                => NULL,
                   p_attribute41                => NULL,
                   p_attribute42                => NULL,
                   p_attribute43                => NULL,
                   p_attribute44                => NULL,
                   p_attribute45                => NULL,
                   p_attribute46                => NULL,
                   p_attribute47                => NULL,
                   p_attribute48                => NULL,
                   p_attribute49                => NULL,
                   P_ATTRIBUTE50                => NULL,
                   p_flex_value_meaning         => l_value_set_value,
                   p_description                => NULL
                   );
      COMMIT;

   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT,PUT_LINE('Error is ' || SUBSTR (SQLERRM, 1, 1000));
   END;

Deriving Oracle GL Account Code Combination ID’s (CCID’s) through APIs

1] FND_FLEX_EXT.GET_COMBINATION_ID:

This API Finds combination_id for given set of key flexfield segment values. Segment values must be input in segments(1) – segments(n_segments) in the order displayed.

It also creates a new combination if it is valid and the flexfield allows dynamic inserts and the combination does not already exist. It commit the transaction soon after calling this function since if a combination is created it will prevent other users creating similar combinations on any flexfield until a commit is issued.

It performs all checks on values including security and cross-validation. Value security rules will be checked for the current user identified in the FND_GLOBAL package.

Generally pass in SYSDATE for validation date. If validation date is null, this function considers expired values valid and checks all cross-validation rules even if they are outdated.

This function returns TRUE if combination valid or FALSE and sets error message using FND_MESSAGE utility on error or if invalid. If this function returns FALSE, use GET_MESSAGE to get the text of the error message in the language of the database, or GET_ENCODED_MESSAGE to get the error message in a language-independent encoded format.

The Combination_id output may be NULL if combination is invalid.

Example: (Tested in R12.1.3)

SET serveroutput ON;
DECLARE
  l_application_short_name VARCHAR2(240);
  l_key_flex_code          VARCHAR2(240);
  l_structure_num          NUMBER;
  l_validation_date        DATE;
  n_segments               NUMBER;
  SEGMENTS                 APPS.FND_FLEX_EXT.SEGMENTARRAY;
  l_combination_id         NUMBER;
  l_data_set               NUMBER;
  l_return                 BOOLEAN;
  l_message                VARCHAR2(240);
BEGIN
  l_application_short_name := 'SQLGL';
  l_key_flex_code          := 'GL#';

  SELECT id_flex_num
  INTO l_structure_num
  FROM apps.fnd_id_flex_structures
  WHERE ID_FLEX_CODE        = 'GL#'
  AND ID_FLEX_STRUCTURE_CODE=<ACCOUNTING_FLEXFIELD>;

  l_validation_date        := SYSDATE;
  n_segments               := 6;
  segments(1)              := '00101';
  segments(2)              := '28506';
  segments(3)              := '00000';
  segments(4)              := '09063';
  segments(5)              := '00000';
  segments(6)              := '00000';
  l_data_set               := NULL;

  l_return := FND_FLEX_EXT.GET_COMBINATION_ID(
                    application_short_name => l_application_short_name,
                    key_flex_code          => l_key_flex_code,
                    structure_number       => l_structure_num,
                    validation_date        => l_validation_date,
                    n_segments             => n_segments,
                    segments               => segments,
                    combination_id         => l_combination_id,
                    data_set               => l_data_set
                    );
  l_message:= FND_FLEX_EXT.GET_MESSAGE;

  IF l_return THEN
    DBMS_OUTPUT.PUT_LINE('l_Return = TRUE');
    DBMS_OUTPUT.PUT_LINE('COMBINATION_ID = ' || l_combination_id);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Error: '||l_message);
  END IF;
END;

2] FND_FLEX_EXT.get_ccid:

This API gets combination id for the specified key flexfield segments.It is identical to get_combination_id() except this function takes segment values in a string concatenated by the segment  delimiter for this flexfield, and returns a positive combination id if valid or 0 on error.

3] FND_FLEX_KEYVAL.VALIDATE_SEGS:

These key flexfields server validations API are a low level interface to key flexfields validation.  They are designed to allow access to all the flexfields functionality, and to allow the user to get only the information they need in return.  Because of their generality, these functions are more difficult to use than those in the FND_FLEX_EXT package.  Oracle strongly suggests using the functions in FND_FLEX_EXT package if at all possible.

This function finds combination from given segment values.  Segments are passed in as a concatenated string in increasing order of segment_number (display order).

Various Operations that can be performed are:

• ‘FIND_COMBINATION’ – Combination must already exist.
• ‘CREATE_COMBINATION’ – Combination is created if doesn’t exist.
• ‘CREATE_COMB_NO_AT’ – same as create_combination but does not use an autonomous transaction.
• ‘CHECK_COMBINATION’ – Checks if combination valid, doesn’t create.
• ‘DEFAULT_COMBINATION’ – Returns minimal default combination.
• ‘CHECK_SEGMENTS’ – Validates segments individually.

If validation date is NULL checks all cross-validation rules. It returns TRUE if combination valid or FALSE and sets error message on server if invalid. Use the default values if you do not want any special functionality.

Example: (Tested in R12.1.3)

SET serveroutput ON;
DECLARE
  l_segment1   GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
  l_segment2   GL_CODE_COMBINATIONS.SEGMENT2%TYPE;
  l_segment3   GL_CODE_COMBINATIONS.SEGMENT3%TYPE;
  l_segment4   GL_CODE_COMBINATIONS.SEGMENT4%TYPE;
  l_segment5   GL_CODE_COMBINATIONS.SEGMENT5%TYPE;
  l_segment6   GL_CODE_COMBINATIONS.SEGMENT6%TYPE;
  l_valid_combination BOOLEAN;
  l_cr_combination    BOOLEAN;
  l_ccid       GL_CODE_COMBINATIONS_KFV.code_combination_id%TYPE;
  l_structure_num FND_ID_FLEX_STRUCTURES.ID_FLEX_NUM%TYPE;
  l_conc_segs GL_CODE_COMBINATIONS_KFV.CONCATENATED_SEGMENTS%TYPE;
  p_error_msg1                 VARCHAR2(240);
  p_error_msg2                 VARCHAR2(240);
BEGIN
  l_segment1  := '00101';
  l_segment2  := '28506';
  l_segment3  := '00000';
  l_segment4  := '14302';
  l_segment5  := '00455';
  l_segment6  := '00000';
  l_conc_segs := l_segment1||'.'||l_segment2||'.'||l_segment3||'.'||l_segment4||'.'||l_segment5||'.'||l_segment6 ;
  BEGIN
    SELECT id_flex_num
      INTO l_structure_num
      FROM apps.fnd_id_flex_structures
     WHERE id_flex_code        = 'GL#'
       AND id_flex_structure_code='EPC_GL_ACCOUNTING_FLEXFIELD';
  EXCEPTION
  WHEN OTHERS THEN
    l_structure_num:=NULL;
  END;
  ---------------Check if CCID exits with the above Concatenated Segments---------------
  BEGIN
    SELECT code_combination_id
      INTO l_ccid
      FROM apps.gl_code_combinations_kfv
     WHERE concatenated_segments = l_conc_segs;
  EXCEPTION
  WHEN OTHERS THEN
    l_ccid:=NULL;
  END;
  IF l_ccid IS NOT NULL THEN
    ------------------------The CCID is Available----------------------
    DBMS_OUTPUT.PUT_LINE('COMBINATION_ID= ' ||l_ccid);
  ELSE
  DBMS_OUTPUT.PUT_LINE('This is a New Combination. Validation Starts....');
    ------------Validate the New Combination--------------------------
    l_valid_combination := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS
                          (
                          operation => 'CHECK_COMBINATION',
                          appl_short_name => 'SQLGL',
                          key_flex_code => 'GL#',
                          structure_number => L_STRUCTURE_NUM,
                          concat_segments => L_CONC_SEGS
                          );
    p_error_msg1 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

    IF l_valid_combination then

      DBMS_OUTPUT.PUT_LINE('Validation Successful! Creating the Combination...');
      -------------------Create the New CCID--------------------------

      L_CR_COMBINATION := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS
                          (
                          operation => 'CREATE_COMBINATION',
                          appl_short_name => 'SQLGL',
                          key_flex_code => 'GL#',
                          structure_number => L_STRUCTURE_NUM,
                          concat_segments => L_CONC_SEGS );
          p_error_msg2 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

      IF l_cr_combination THEN
        -------------------Fetch the New CCID--------------------------
        SELECT code_combination_id
          INTO l_ccid
          FROM apps.gl_code_combinations_kfv
        WHERE concatenated_segments = l_conc_segs;
        DBMS_OUTPUT.PUT_LINE('NEW COMBINATION_ID = ' || l_ccid);
      ELSE
        -------------Error in creating a combination-----------------
        DBMS_OUTPUT.PUT_LINE('Error in creating the combination: '||p_error_msg2);
      END IF;
    ELSE
      --------The segments in the account string are not defined in gl value set----------
      DBMS_OUTPUT.PUT_LINE('Error in validating the combination: '||p_error_msg1);
    END IF;
  END IF;
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);
END;

AP Table Handler APIs

These APIs are handful when you want to do Insert, Update or Delete in some AP Base tables i.e. AP_INVOICES_ALL, AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS, AP_INVOICE_PAYMENTS and AP_CHECKS_ALL programmatically for some business requirements (rare cases!).

1] AP_AI_TABLE_HANDLER_PKG:

• Procedure Insert_Row:  Inserts a row in AP_INVOICES_ALL table.
• Procedure Update_Row:  Updates a row in AP_INVOICES_ALL table.
• Procedure Delete_Row:  Deletes a row in AP_INVOICES_ALL table. Also subsequently delete rows in the related tables like AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS_ALL, AP_PAYMENT_SCHEDULES_ALL, AP_HOLDS_ALL and AP_SELF_ASSESSED_TAX_DIST_ALL.

2] AP_AIL_TABLE_HANDLER_PKG:

• Procedure CHECK_UNIQUE: Check the Uniqueness of a Row.
• Procedure Insert_Row:  Inserts a row in AP_INVOICE_LINES_ALL table.
• Procedure Update_Row:  Updates a row in AP_INVOICE_LINES_ALL table.
• Procedure Delete_Row:  Deletes a row in AP_INVOICE_LINES_ALL table. Also subsequently delete rows in the related tables like AP_INVOICE_DISTRIBUTIONS_ALL.

3] AP_AID_TABLE_HANDLER_PKG:

• Procedure CHECK_UNIQUE: Check the Uniqueness of a Row.
• Procedure Insert_Row:  Inserts a row in AP_INVOICE_DISTRIBUTIONS table.
• Procedure Update_Row:  Updates a row in AP_INVOICE_DISTRIBUTIONS table.
• Procedure Delete_Row:  Deletes a row in AP_INVOICE_DISTRIBUTIONS table.

4] AP_AIP_TABLE_HANDLER_PKG:

• Procedure Insert_Row:  Inserts a row in AP_INVOICE_PAYMENTS table.
• Procedure Update_Amounts: Update amounts in AP_INVOICE_PAYMENTS table.

5] AP_AC_TABLE_HANDLER_PKG:

• Procedure Insert_Row:  Inserts a row in AP_CHECKS_ALL table.
• Procedure Update_Row:  Updates a row in AP_CHECKS table.
• Procedure Delete_Row:  Deletes a row in AP_CHECKS table.
• Procedure Update_Amounts: Update amounts in AP_CHECKS table.