How to enable and retrieve FND debug log messages

 

SOLUTION

1) Set up profiles for the User / Responsibility to be used to reproduce the issue

Responsibility: System Administrator
Navigation: Profile > System

Query up the Application, Responsibility and User you will use to reproduce the issue you want to debug.
For example:
Application = Receivables
Responsibility = Receivables Manager
User = MYUSER1
Profile = FNDÞbug%

then set the profiles as shown below:

Profile Name	Suggested value	Comments
FND: Debug Log Enabled	YES	This turns the debugging feature on
FND: Debug Log Filename  or FND: Debug Log Filename for Middle-Tier	leave this blank/null at all levels	Use when you want debug messages to get stored to a file. Note however, that the preferred method of downloading debug messages to a .xls file is provided in step 5 below.
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

Following are examples on how you would set the above profiles depending on what you want to debug :

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%

2) Since the debugging routine will start writing messages to the table, we want to know which messages pertain to our test.
    a) If you are tracking the debug messages for a concurrent request, take note of the Concurrent Request id

    b) Otherwise, retrieve the current max value of log sequence as follows:

SELECT MAX(LOG_SEQUENCE) before_seq
FROM FND_LOG_MESSAGES;

3) Run your test case, try to stay on track in reproducing the issue and leave out extraneous steps so that you don't end up with debug messages that are not relevant to your issue. It is ideal to not have anyone else using the Responsibility you have enabled debug for, so that only messages pertaining to your testcase are picked up.

4) If you ran the script above to get the log_sequence, do it again now, after you have completed the steps to reproduce the issue:

SELECT MAX(LOG_SEQUENCE) after_seq
FROM FND_LOG_MESSAGES;

5) For ease of review by Development, spool the output of the following to a .xls spreadsheet :

You have the option to retrieve the columns you would like to see from FND_LOG_MESSAGES.

If you want to retrieve only a few columns to keep the spreadsheet small, you can do the following:

a) Retrieve the debug messages for a concurrent request ID:

SELECT log.module, log.message_text message
FROM fnd_log_messages log,
     fnd_log_transaction_context con
WHERE con.transaction_id = &request_id
AND con.transaction_type = 'REQUEST'
AND con.transaction_context_id = log.transaction_context_id
ORDER BY log.log_sequence; 

b) Otherwise, retrieve debug messages using a log_sequence range:

SELECT module, message_text
FROM fnd_log_messages
WHERE log_sequence between &before_seq and &after_seq
ORDER BY log_sequence;

If you want to retrieve all the columns for detailed debugging and troubleshooting, you can do the following:

a) Retrieve the debug messages for a concurrent request ID:

SELECT log.*
FROM fnd_log_messages log,
     fnd_log_transaction_context con
WHERE con.transaction_id = &request_id
AND con.transaction_type = 'REQUEST'
AND con.transaction_context_id = log.transaction_context_id
ORDER BY log.log_sequence;

b) Otherwise, retrieve debug messages using a log_sequence range:

SELECT *
FROM fnd_log_messages
WHERE log_sequence between &before_seq and &after_seq
ORDER BY log_sequence;

 

6) Don't forget to turn OFF debugging, otherwise all your actions will be logged and this could impact performance of the application.

FND: Debug Log Enabled = NO