Query To Get Enabled Trace/Log/Debug Profile Options

. This will display values of profile options "FND: Debug Log Enabled" (AFLOG_ENABLED) and
"Initialization SQL Statement - Custom'" (FND_INIT_SQL) at each level. If the values are
respectively 'Y' and/or contains sql statement for these profile options then log and/or
trace are enable for that level (which could be at site, user, ... level):


set pagesize 200
col NAME for a25
col LEV for a6
col CONTEXT for a25
col VALUE for a50
col USER_PROFILE_OPTION_NAME for a37
select po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
from FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where (po.profile_option_name like '%AFLOG_ENABLED%' or po.profile_option_name like
'%FND_INIT_SQL%')
and pov.application_id = po.application_id
and pov.profile_option_id = po.profile_option_id
and usr.user_id (+) = pov.level_value
and rsp.application_id (+) = pov.level_value_application_id
and rsp.responsibility_id (+) = pov.level_value
and app.application_id (+) = pov.level_value
and svr.node_id (+) = pov.level_value
and org.organization_id (+) = pov.level_value
order by "NAME", pov.level_id, "VALUE";


2. This will display all profile options with values and levels where the word TRACE, LOG, or
DEBUG is found in the PROFILE_OPTION_NAME:


set pagesize 200
col NAME for a25
col LEV for a6
col CONTEXT for a25
col VALUE for a50
col USER_PROFILE_OPTION_NAME for a37
select po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
from FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where (po.PROFILE_OPTION_NAME like '%TRACE%' or po.PROFILE_OPTION_NAME like '%
DEBUG%' or po.PROFILE_OPTION_NAME like '%LOG%')
and pov.application_id = po.application_id
and pov.profile_option_id = po.profile_option_id
and usr.user_id (+) = pov.level_value
and rsp.application_id (+) = pov.level_value_application_id
and rsp.responsibility_id (+) = pov.level_value
and app.application_id (+) = pov.level_value
and svr.node_id (+) = pov.level_value
and org.organization_id (+) = pov.level_value
order by "NAME", pov.level_id, "VALUE";

How to create a FRD (Forms Runtime Diagnostic) Log in EBusiness Suite R12 using Forms 10g

1. Make the USER value for profile option 'ICX: Forms Launcher' the same as the SITE value
e.g. copy value of ICX: Forms Launcher for the SITE"http://testserver.oracle.com:8005/forms/frmservlet"to be the value for the USER.

2. Append the USER value of ICX: Forms Launcher with the "?record=collect" (without the quotes)
e.g. update the value of ICX: Forms Launcher for the user to behttp://testserver.oracle.com:8005/forms/frmservlet?record=collect
Another method is to set the "Forms Runtime Parameters" profile option with a value of "record=collect" (without quotes)

3. Save this change at the USER level.

4. Logout and Log back in again, for this profile to take effect. When you login again, a message box pops up, which alerts you that Diagnostics is enabled.

5. Go to Help->About Oracle Applications, go to the section
----------------------------------------
Forms Server Environment Variables
----------------------------------------
There you should see a FORMS_TRACE_DIR. Note the value of this variable.

If you find that the FORMS_TRACE_DIR is not defined, then ask your system administrator to:
- bring down the Forms server- define the "s_forms_trace_dir" context variable in the middle tier (Forms tier) to point to a valid directory. For this the preferred method is to use Oracle Applications Manager => Site Map link => System Configuration section => AutoConfig => for the Applications tier, click the Edit Parameters => System tab => click the + sign to expand the "oa_forms_server". In this expanded area, look for Title = "Forms runtime file directory location" (OA_VAR = "s_forms_trace_dir"). On this line, you can see the text box where the current value exists (if any).Enter/modify the value as desired here, and save.- run Autoconfig- then bring back up the Forms sever.

6. Reproduce the Forms-Issue, which you are trying to diagnose.

7. Go to the directory specified in the FORMS_TRACE_DIR variable. List the latest files. The FRD Log should be the one with the string "collect" in its name.