Debug using "FND Debug Log"

We have a few profiles which need to be set to enable log Oracle seeded log messages .

A program written in any technology, either form, report, PL/SQL, java concurrent program, OAF has their debug message stored in FND_LOG_MESSAGES.

How does this work? 

How to use FND debug Log to pinpoint the error?

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

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
FND: Debug log module -> %


If the error pertains to a specific module use the application short name.


Ex: For receivable use FND: Debug log module -> ar%

Log into the Oracle application and setup these profiles.

Step 2: 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 :

SELECT MAX(LOG_SEQUENCE) FROM FND_LOG_MESSAGE 

Step 3: Run the test case, avoiding extraneous steps to avoid stacking the table.

Step 4:

a) For a concurrent process:

  SELECT LOG.module, LOG.MESSAGE_TEXT MESSAGE
    FROM fnd_log_messages LOG, fnd_log_transaction_context con
   WHERE     request_id = <Conc Req ID>
         AND con.transaction_type = 'REQUEST'
         AND con.transaction_context_id = LOG.transaction_context_id
ORDER BY LOG.log_sequence;


b) For any other debugging:

SELECT module, MESSAGE_TEXT
  FROM fnd_log_messages
 WHERE log_sequence = (SELECT MAX (LOG_SEQUENCE) FROM FND_LOG_MESSAGE);

Set the given profile and perform the activity you want to take the logs for. Now query in the table.

SELECT module, MESSAGE_TEXT
  FROM fnd_log_messages
 WHERE log_sequence > :max_seq;