How to enable and retrieve FND debug log messages

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%Debug%

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	leave this blank/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

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 :

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;

For either of the above select statements, you can pick up all the fields by using:

select * (and the rest of the statement...)

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