Debugging

XML Publisher (with Bursting): (tested in version: 12.1.3)

1. Connect to the server as 'applmgr'.

2. Run (source) the correct APPS*.env file to setup environment variables.

3. Create an $XDO_TOP/temp directory.

4. Navigate to following location and ensure that you can see the file rt.jar.

$ cd $OA_JRE_TOP/lib

5. Create an xdodebug.cfg file in the same directory as the rt.jar file, containing the following 2 lines:

LogLevel=STATEMENT

LogDir=[full path to XDO_TOP]/temp

6. Reproduce the problem by running it as a concurrent program and upload the files from LogDir location.

Concurrent Program Trace

Enable a sql level trace and FND logging when submitting a concurrent program.

1. The following profile options will need to be set in order to enable the Debug Options button in the Concurrent Request Submission form:

Profile Concurrent:Allow Debugging

2. Next, proceed to the Concurrent Request Submission form to submit the concurrent program.

3. Rather than submitting the request, choose the 'Debug Options' button now enabled in the submission form.

3. In the web based form that opens, choose the options as seen below to enable trace and FND logging.

4. A new screen will appear showing that the rule was created, choose the OK button which closes the window.

4. Submit the request. Let it complete.

Pull the FND Logging using SQL:

SELECT log.*

FROM fnd_log_messages log,

fnd_log_transaction_context con

WHERE con.transaction_id = &requestid

AND con.transaction_type = 'REQUEST'

AND con.transaction_context_id = log.transaction_context_id

ORDER BY log.log_sequence;

6. To locate the trace file, utilize the following sql to confirm the trace file location on the database server.

select name, value

from v$parameter

where name like 'user_dump_dest';

The trace filename will contain the USER_NAME which submitted the concurrent request along with the letters CR as can be seen above.

tkprof <filename>.trc <filename>.out explain=<found username/password>

example: tkprof sox_ora_7095_AA_CR3514556.trc 7095.out explain=<apps/apps>

Generate trace for RDF based concurrent program

1. Enable Trace at the Report Definition.

Go to System Administrator -> Concurrent Programs -> Define , Query the report and check the 'Enable trace' check box

( Navigate to:

  Profile->system

  Query on the Profile Option: "Concurrent: Allow Debugging"

  This should be set to 'yes' at 'Site' level.

  If it isn't set, then set it, then logout and bounce the APPS services.

  The 'Debug Options' button on the concurrent program will now be enabled. )

2. Run the report for Step 1. above

3. Get the request id from below query. i.e. Request id = 555555

select fcr.request_id "Request ID"

--, fcr.oracle_process_id "Trace ID"

, p1.value||'/'||lower(p2.value)||'_ora_'||fcr.oracle_process_id||'.trc' "Trace File"

, to_char(fcr.actual_completion_date, 'dd-mon-yyyy hh24:mi:ss') "Completed"

, fcp.user_concurrent_program_name "Program"

, fe.execution_file_name|| fe.subroutine_name "Program File"

, decode(fcr.phase_code,'R','Running')||'-'||decode(fcr.status_code,'R','Normal') "Status"

, fcr.enable_trace "Trace Flag"

from fnd_concurrent_requests fcr

, v$parameter p1

, v$parameter p2

, fnd_concurrent_programs_vl fcp

, fnd_executables fe

where p1.name='user_dump_dest'

and p2.name='db_name'

and fcr.concurrent_program_id = fcp.concurrent_program_id

and fcr.program_application_id = fcp.application_id

and fcp.application_id = fe.application_id

and fcp.executable_id=fe.executable_id

and ((fcr.request_id = &request_id

or fcr.actual_completion_date > trunc(sysdate)))

order by decode(fcr.request_id, &request_id, 1, 2), fcr.actual_completion_date desc;

--you will be prompted to enter the request_id;

4. In SQL: select value from v$parameter where name = 'user_dump_dest'

5. Go to directory in Step 4. above.

6. grep ‘555555’ *.trc

$ tkprof <RAW TRACE> <output> explain=apps_uname/apps_pwd sys=no  sort=prsela,exeela,fchela

-----------------------------------------

After applying below patches I was able to generate trace while selecting below while Debug Option screen opens

Important support note:

R12.1.x SQL Trace Does Not Get Created Using Debug Options from Submit Request Form ( Doc ID 1273539.1 )

Patch: 8743459

refer file AFCPRTUB.pls in same patch

*) Reports Trace -> Log Report SQL Trace Information

It worked, and Log file had location of dbg file generated.

create or replace package body FND_DEBUG_REP_UTIL as   function get_trace_file_name return varchar2 is      trc_file      varchar2(500);      trc_file_node varchar2(100);      trc_file_name varchar2(30);      trc_file_dir  varchar2(250);      path_sep      varchar2(1);      default_path  varchar2(16) := '/var/tmp';   begin          trc_file := '';      select 'dbg' || fnd_debug_rule_executions_s.nextval || '.log'        into trc_file_name        from dual;      trc_file_node := fnd_debug_rep_util.get_trace_file_node;      trc_file_dir := fnd_context_util.get_tag_value(trc_file_node, 'APPLTMP');      path_sep := fnd_context_util.get_tag_value(trc_file_node, 'pathsep');      if ( path_sep is null ) then         path_sep := '/';      end if;  --  -- if location is not specified, just write it to a default location  -- so it does not error out  --      if (trc_file_dir is null ) then         trc_file := default_path || path_sep || trc_file_name;      else         trc_file := trc_file_dir || path_sep || trc_file_name;      end if;      return trc_file;      exception          when others then             fnd_message.set_name ('FND', 'SQL-Generic error');             fnd_message.set_token ('ERRNO', sqlcode, FALSE);             fnd_message.set_token ('REASON', sqlerrm, FALSE);             fnd_message.set_token ('ROUTINE',                                                                                                                            'FND_DEBUG_REP_UTIL.GET_TRACE_FILE_NAME',                                                                                                                            FALSE);             if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then             fnd_log.message(FND_LOG.LEVEL_EXCEPTION,                             'fnd.plsql.FND_DEBUG_REP_UTIL.GET_TRACE_FILE_NAME.others',                             FALSE);             end if;             return trc_file;

WebADI Trace File Generation

Set the following System Profile Options:

Navigation: System Administrator > Profile > System

                    > BNE%Log%

BNE Server Log Filename : Any Name (Ex: WebADI.log)

BNE Server Log Level    : ERROR

BNE Server Log Path     : Provide the full directory path as per your convenient

> Bounce the Apache Server (adstpall.sh)

>Trace/debug statements will keep increasing the file for every run, to avoid excessive growth of the log file select the appropriate 'BNE Server Log Level' options

Options are: REQUIRED | CRITICAL ERROR | ERROR | WARNING | INFORMATION | DETAIL | TRACE

-- Web ADI Profile Values

select a.profile_option_name

      ,a.user_profile_option_name

      ,a.description

      ,c.profile_option_value

  from fnd_profile_options_tl a

      ,fnd_profile_options b

      ,fnd_profile_option_values c

where b.profile_option_id   = c.profile_option_id

   and a.profile_option_name = b.profile_option_name

   and b.profile_option_name like 'BNE%_LOG%'

   and a.language            = 'US'

;

Debugging Forms

The biggest challenge for any form application is to debug it. There are many ways to debug Oracle Apps Forms. Few of them are list as below

FND Logging

Debug Messages

Diagnostics Utility

SQL TRACE and TKPROF

FND Logging

We should design and build a custom extensions in a manner that can easily be debugged. This can be done by calling Oracle delivered API’s in your custom code. The API is FND_LOG.

The debug messages are stored in a table called FND_LOG_MESSAGES

A program written in any technology, either form, or report, or PL/SQL or java concurrent program or OAF.

FND Logging: Setups

To enable logging, there are few profiles which are required to enable. These are:

FND: Debug Log Level

FND: Debug Log Enabled

FND: Debug Log Module     

                                          

The various possible values available for FND: Debug Log Level are:

LEVEL_UNEXPECTED

LEVEL_ERROR

LEVEL_EXCEPTION

LEVEL_EVENT

LEVEL_PROCEDURE

LEVEL_STATEMENT

FND Logging: Setups

Oracle recommends to set this to "Statement" level as it extracts debug messages at all levels, in one glance.

FND: Debug Log Enabled: Set this profile to YES. If it is yes, then only system will store message.

FND: Debug Log Module: Set this profile which is causing this proble. Oracle recommends to set this value to “%” as we are not sure which module is causing this error. For example, set this to po%, if you know for sure that the error was caused by code written in po module. However po code might be internally calling hr code which might intern be calling fnd code. Hence it’s best to set this profile value as %.

Creating LOG Messages

You can invoke standard API to create error/debug messages

FND_LOG.STRING ( log_level => fnd_log.level_statement

                                     module    => 'xxpo.packagename.procedurename'

                                     message   => 'debug message here‘

                                  )

This procedure uses pragma AUTONOMOUS_TRANSACTION with a commit.

Hence your debug messages will not be lost despite a rollback in parent session.

Retrieving and Purging LOG Messages

You can retrieve the error/debug messages from table FND_LOG_MESSAGES

SELECT    *

   FROM   fnd_log_messages

 WHERE  user_id = 209122 /*your FND_USER user_id here*/

ORDER BY log_sequence DESC

Concurrent Program: “Purge Debug Log and System Alerts”

Debug Messages

Use FND_MESSAGE API to get any debug messages. Its one procedure Debug immediately displays the string passed to it as input parameter.

fnd_message.debug(‘Your Debug msg here’);

This is very useful at the time of development of forms

Diagnostics

Oracle has provided a very useful utility in Oracle Application to find the value of variables which are set by form in runtime. These variables can be:

Examine Utility

Trace

Properties

Custom Code

Diagnostics: Setups

Oracle has provided a very useful utility called Diagnostics in Oracle Application which can be used to debug the code. There are few profile which must be set in enable this utility. These are:

Hide Diagnostics menu entry -> If the profile option is set as ‘No’, Only then it will appear in the help menu

Utilities: Diagnostics -> If set to ‘Yes’, No APPS schema password is required otherwise APPS password required to use any of its feature

Examine Utility

Oracle has provided a very useful utility with-in Diagnostics to find the value of variables which are set by form in runtime. These variables can be:

System Variables

Environment Variables

Profile Values

Global Variables

Parameters

Form Level Items

Navigation To Examine Utility

Help->Menu->Diagnostics->Examine

Custom Code

You can also switch off your custom code

Help> Menu > Diagnostics > Custom Code

Last Query

You can use system variable LAST_QUERY to retrieve the last query which form has fired. This SQL will have all the bind variables embedded within it. You can run it as it is.

After having clicked on examine, enter values as:Block = SYSTEM and Field  = LAST_QUERY

SYSTEM.LAST_QUERY will not display:

LOV Queries

Post Query SQL’s

Cursors in Form Triggers/Attached pll’s

SQL Trace and Tkproof

The SQL Trace facility is very basic debugging diagnostic tool that can help you monitor as well as tune applications running against the Oracle Apps Forms.

The SQL trace files produced in raw form. This can be translated by the tkprof (transient kernel profiler) utility into a more human readable form.

Navigation to enable SQL Trace:

Help > Diagnostics >Trace > Trace with Binds and Waits

(Set trace with binds and waits, using help menu. Using the “binds” option, you will get to see value of the variables in SQL.)

System will give you location where trace file will be created. It always created at database tier.