How to generate an GL debug log file .

Detail Log for GL programs

Used for concurrent request problems where we need to know what sql was used in key areas or we need  to retain temporary tables.
Note: Be aware that temp tables are never dropped so leaving this on will create a build up of many temporary tables throughout GL, like gl_posting_interim_nnnnn

1. Set GL: Debug Mode profile option to Yes, run the program and provide the program log.
Note: This profile option introduced in GL 11i FP.D initially allowed 5 main GL concurrent processes (Posting, Translation, Consolidation, Revaluation and Mass Allocation) to be run in debug mode without the requirement for operating system command line access. This enhancement has not been released as a stand-alone patch.

2. Switch the profile back to No when the test is complete.

Note:On solving your current problems please get your DBA to delete temporary tables see Internal
Note.176180.1 Can the GL_Posting_Interim_XXX and GL_Summary_Interim_X_XXX Tables be Deleted?:

FND Log Messages

OA Framework (OA Fwk)
This is for web page issues like accounting setup manager, account analysis drill down, fsg drill down etc.

1. Log into system admin responsibility and set the following profile options at user level :
FND: Debug Log Enabled - Yes
FND: Debug Log Level - Statement

2. Run the following select in sqlplus :
 
SELECT MAX(log_sequence) FROM fnd_log_messages;

Take the max of log sequence and use it as log_seq1 in step#5

3. Reproduce the issue with above user for which fnd profile options are set.

4. Run the following select in sqlplus :

SELECT MAX(log_sequence) FROM fnd_log_messages;

Take the max of log sequence and use it as log_seq2 in step#5

5. Run following select to get the log messages and provide in xls format ::

SELECT LOG_SEQUENCE, MESSAGE_TEXT, USER_ID
FROM fnd_log_messages
WHERE log_sequence > log_seq1-1 and log_sequence < log_seq2+1 ;

You can also use
SELECT log_sequence, message_text,substr(module,1,100)
FROM fnd_log_messages msg
where log_sequence between <min_log_seq> and <max_log_seq>
ORDER BY LOG_SEQUENCE ;

6. Switch the FND: Debug Log Enabled profile back to No when the test is complete.