1. Responsibilities Listing
2. Menus Listing
3. Submenu and Function Listing
4. User and Assigned Responsibility Listing
5. Responsibility and assigned request group listing
6. Profile option with modification date and user
7. Forms personalization Listing
8. Patch Level Listing
9. Request attached to responsibility listing
10. Request listing application wise
11. Count Module Wise Reports
12. Request Status Listing
13. User and responsibility listing
14. Applied Patch Listing




//*
1. Responsibilities Listing
Purpose/Description:
Retrieve a list of all responsibilities.
Parameters
None
*//

SELECT
    (SELECT application_short_name
        FROM fnd_application fa
        WHERE fa.application_id = frt.application_id)
    application
,   frt.responsibility_id
,   frt.responsibility_name
FROM apps.fnd_responsibility_tl frt;



//*
2. Menus Listing
Purpose/Description:
To see the Menus associated with a given responsibility
Parameters
responsibility_id that you can retrieve from query nr 1 (Responsibilities Listing)
*//

SELECT DISTINCT
    a.responsibility_name
,   c.user_menu_name
FROM
    apps.fnd_responsibility_tl a
,   apps.fnd_responsibility b
,   apps.fnd_menus_tl c
,   apps.fnd_menus d
,   apps.fnd_application_tl e
,   apps.fnd_application f
WHERE
    a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_id = 50103
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = ‘US’;



//*
3. Submenu And Function Listing
Purpose/Description:
By using this query you can check function and submenus attached to a specific menu
Parameters
User_menu_name that you can get by running query 2 (Menu Listing)
*//

SELECT
    c.prompt
,   c.description
FROM
    apps.fnd_menus_tl a
,   fnd_menu_entries_tl c
WHERE
    a.menu_id = c.menu_id
AND a.user_menu_name = ‘Navigator Menu - System Administrator GUI’;



//*
4.User and Assigned Responsibility Listing
Purpose/Description:
You can use this query to check responsibilities assigned to users.
Parameters
None
*//
   
SELECT UNIQUE
    u.user_id
,   SUBSTR (u.user_name, 1, 30) user_name
,   SUBSTR (r.responsibility_name, 1, 60) responsiblity
,   SUBSTR (a.application_name, 1, 50) application
FROM
    fnd_user u
,   fnd_user_resp_groups g
,   fnd_application_tl a
,   fnd_responsibility_tl r
WHERE
    g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY
    SUBSTR (user_name, 1, 30)
,   SUBSTR (a.application_name, 1, 50)
,   SUBSTR (r.responsibility_name, 1, 60);



//*
5. Responsibility and assigned request group listing
Purpose/Description:
To find responsibility and assigned request groups.
Every responsibility contains a request group (The request group is basis of submitting requests)
Parameters
None
*//

SELECT
    responsibility_name responsibility
,   request_group_name
,   frg.description
FROM
    fnd_request_groups frg
,   fnd_responsibility_vl frv
WHERE
    frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name



//*
6. Profile option with modification date and user
Purpose/Description:
Query that can be used to audit profile options.
Parameters
None
*//

SELECT
    t.user_profile_option_name
,   profile_option_value
,   v.creation_date
,   v.last_update_date
,   v.creation_date - v.last_update_date "Change Date"
,   (SELECT UNIQUE user_name
        FROM fnd_user
        WHERE user_id = v.created_by) "Created By"
,   (SELECT user_name
        FROM fnd_user
        WHERE user_id = v.last_updated_by) "Last Update By"
FROM
    fnd_profile_options o
,  fnd_profile_option_values v
,   fnd_profile_options_tl t
    WHERE
        o.profile_option_id = v.profile_option_id
    AND o.application_id = v.application_id
    AND start_date_active <= SYSDATE
    AND NVL (end_date_active, SYSDATE) >= SYSDATE
    AND o.profile_option_name = t.profile_option_name
    AND level_id = 10001
    AND t.LANGUAGE IN (SELECT language_code
    FROM fnd_languages
WHERE installed_flag = ‘B’
UNION
    SELECT nls_language
    FROM fnd_languages
    WHERE installed_flag = ‘B’)
ORDER BY user_profile_option_name;



//*
7. Forms personalization Listing
Purpose/Description:
To get modified profile options.
Personalization is a feature available in 11.5.10.X.
Parameters
None
*//

SELECT
    ffft.user_function_name "User Form Name"
,   ffcr.SEQUENCE
,   ffcr.description
,   ffcr.rule_type
,   ffcr.enabled
,   ffcr.trigger_event
,   ffcr.trigger_object
,   ffcr.condition
,   ffcr.fire_in_enter_query
,   (SELECT user_name
        FROM fnd_user fu
        WHERE fu.user_id = ffcr.created_by) "Created By”
FROM
    fnd_form_custom_rules ffcr
,   fnd_form_functions_vl ffft
WHERE ffcr.ID = ffft.function_id
ORDER BY 1;



//*
8. Patch Level Listing
Purpose/Description:
Query that can be used to view the patch level status of all modules
Parameters
None
*//

SELECT
    a.application_name
,   DECODE (b.status, ‘I’, ‘Installed’, ‘S’, ‘Shared’, ‘N/A’) status
,   patch_level
FROM
    apps.fnd_application_vl a
,   apps.fnd_product_installations b
WHERE
    a.application_id = b.application_id;
 



//*
9. Request attached to responsibility listing
Purpose/Description:
To see all requests attached to a responsibility
Parameters
None
*//

SELECT
    responsibility_name
,   frg.request_group_name
,   fcpv.user_concurrent_program_name
,  fcpv.description
FROM
    fnd_request_groups frg
,   fnd_request_group_units frgu
,   fnd_concurrent_programs_vl fcpv
,   fnd_responsibility_vl frv
WHERE
    frgu.request_unit_type = ‘P’
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;



//*
10. Request listing application wise
Purpose/Description:
View all request types application wise
Parameters
None
*//

SELECT
    fa.application_short_name
,   fcpv.user_concurrent_program_name
,   description
,   DECODE (fcpv.execution_method_code
            ,’B', ‘Request Set Stage Function’
            ,’Q', ‘SQL*Plus’
            ,’H', ‘Host’
            ,’L', ‘SQL*Loader’
            ,’A', ‘Spawned’
            ,’I', ‘PL/SQL Stored Procedure’
            ,’P', ‘Oracle Reports’
            ,’S', ‘Immediate’
            ,fcpv.execution_method_code) exe_method
,   output_file_type
,   program_type
,   printer_name
,   minimum_width
,   minimum_length
,   concurrent_program_name
,   concurrent_program_id
FROM
    fnd_concurrent_programs_vl fcpv
,   fnd_application fa
WHERE
    fcpv.application_id = fa.application_id
ORDER BY description



//*
11. Count Reports per module
Purpose/Description:
To Count Reports
Parameters
None
*//

SELECT
    fa.application_short_name
,   DECODE (fcpv.execution_method_code
    ,’B', ‘Request Set Stage Function’
    ,’Q', ‘SQL*Plus’
    ,’H', ‘Host’
    ,’L', ‘SQL*Loader’
    ,’A', ‘Spawned’
    ,’I', ‘PL/SQL Stored Procedure’
    ,’P', ‘Oracle Reports’
    ,’S', ‘Immediate’
    ,fcpv.execution_method_code) exe_method
,   COUNT (concurrent_program_id) COUNT
FROM
    fnd_concurrent_programs_vl fcpv
,   fnd_application fa
WHERE
    fcpv.application_id = fa.application_id
GROUP BY
    fa.application_short_name
,   fcpv.execution_method_code
ORDER BY 1;



//*
12. Request Status Listing
Purpose/Description:
This query returns report/request processing time
Parameters
None
*//

SELECT
    f.request_id
,   pt.user_concurrent_program_name user_concurrent_program_name
,   f.actual_start_date actual_start_date
,   f.actual_completion_date actual_completion_date
,   floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
    || ‘ HOURS ‘ ||
    floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
    floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
    || ‘ MINUTES ‘ ||
    round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
    floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
    (floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
    floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
    || ‘ SECS ‘ time_difference
,   DECODE(p.concurrent_program_name
    ,’ALECDC’
    ,p.concurrent_program_name||’['||
     f.description||']‘
    ,p.concurrent_program_name) concurrent_program_name
,   decode(f.phase_code
    ,’R',’Running’
    ,’C',’Complete’
    ,f.phase_code) Phase
, f.status_code
FROM
    apps.fnd_concurrent_programs p
,   apps.fnd_concurrent_programs_tl pt
,   apps.fnd_concurrent_requests f
WHERE
    f.concurrent_program_id = p.concurrent_program_id
AND f.program_application_id = p.application_id
AND f.concurrent_program_id = pt.concurrent_program_id
AND f.program_application_id = pt.application_id
AND pt.language = USERENV(’Lang’)
AND f.actual_start_date is not null
ORDER by f.actual_completion_date-f.actual_start_date desc;



//*
13. User and responsibility listing
Purpose/Description:
Check responsibilities assigned to users
Parameters
None
*//

SELECT UNIQUE
    u.user_id
,   SUBSTR (u.user_name, 1, 30) user_name
,   SUBSTR (r.responsibility_name, 1, 60) responsiblity
,   SUBSTR (a.application_name, 1, 50) application
FROM
    fnd_user u
,   fnd_user_resp_groups g
,   fnd_application_tl a
,   fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
–AND a.application_name like ‘%Order Man%’
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60)



//*
14. Applied Patch Listing
Purpose/Description:
Check Current Applied Patches
Parameters
None
*//

SELECT
    patch_name
,   patch_type
,   maint_pack_level
,   creation_date
FROM applsys.ad_applied_patches
ORDER BY creation_date DESC



=================================
CM: jvm size of concurrent queues
=================================
select DEVELOPER_PARAMETERS from FND_CP_SERVICES
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
============
to increase
============
update FND_CP_SERVICES
set DEVELOPER_PARAMETERS =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');



===========================================
Workflow: To see failed, open notifications
===========================================
SELECT message_type, COUNT(1)
FROM apps.wf_notifications
WHERE 1 = 1 AND mail_status = 'FAILED' AND status = 'OPEN'
GROUP BY message_type;


================
SQL: patch level
================
set linesize 155;
set pagesize 200;
set verify off;
select b.bug_number bug, b.creation_date credate, b.last_update_date ldate,
decode(bug_number,
2728236, 'OWF.G',
3031977, 'POST OWF.G ROLLUP 1 - 11.5.9.1',
3061871, 'POST OWF.G ROLLUP 2 - 11.5.9.2',
3124460, 'POST OWF.G ROLLUP 3 - 11.5.9.3',
3316333, 'POST OWF.G ROLLUP 4 - 11.5.9.4.1',
3314376, 'POST OWF.G ROLLUP 5 - 11.5.9.5',
3409889, 'POST OWF.G ROLLUP 5 Consolidated Fixes',
3492743, 'POST OWF.G ROLLUP 6 - 11.5.9.6',
3672076, 'POST OWF.G ROLLUP 6.1',
3868138, 'POST OWF.G ROLLUP 7',
3258819, 'OWF.H',
3262159, 'FND.H',
3140000, '11.5.10',
3240000, '11.5.10 CU1',
4017300, '11.5.10 Consolidated Update (CU1) for ATG Product Family',
3460000, '11.5.10 CU2',
4125550, '11.5.10 Consolidated Update (CU2) for ATG Product Family',
4605136, 'WFDS 4.5 rollup FOR 11.5.10, 11.5.10.1, 11.5.10.2',
4645579, 'WFDS 4.5 v2 (replaces 4605136) FOR FND.H',
4684377, 'ONE OFF MISSING RESPONSIBILITIES PATCH',
4334965, '11.5.10 Consolidated Update (CU3) for ATG Product Family') Patch
from APPS.AD_BUGS b
where b.BUG_NUMBER in
('2728236','3031977','3061871','3124460','3316333',
'3314376','3409889','3492743','3672076','3868138',
'3258819','3262159','3140000','3240000','4017300',
'3460000','4125550','4605136','4645579','4684377',
'4334965')
order by bug_number desc;

=================
Workflow: version
=================
$FND_TOP/sql/wfver.sql


========================
Enable low level logging
========================
How to turn on the debugging:
Set the following profiles:
 FND: Debug Log Enabled = YES
 FND: Debug Log Filename = NULL
 FND: Debug Log Level = STATEMENT (most detailed log)
 FND: Debug Log Module = % or ar% or even ar.arp_auto_rule%

If you have applied Patch 3140000 - 11.5.10 MAINTENANCE PACK, the profile names have changed:
'FND: Debug Log Enabled' been replaced by 'FND: Log Enabled'
and 'FND: Debug Log Level' has been replaced by 'FND: Log Level

If FND: Debug Log Enabled profile is not set, then rest of the others won't matter.


============================================
To check the what is installed on what nodes
===========================================
select node_name, status,  support_cp "cp", support_web "web", support_forms "frm", support_db "db" from apps.fnd_nodes;

============================================
Summary of how many users -- 10g As oacore
============================================

REM
    REM        START OF SQL
    REM
    set feedback on
    set timing on
    set echo on
    set feedback on
    set pagesize 132
    set linesize 80
    col user_name format a15
    col first_connect format a18
    col last_connect format a18
    col How_many_user_sessions format 9999999999
    col How_many_sessions format 9999999999
    REM
    REM SQL 1
    REM Summary of how many users
    REM
    select 'Number of user sessions : ' || count( distinct session_id) How_many_user_sessions
    from icx_sessions icx
    where last_connect > sysdate - 1
    and disabled_flag != 'Y'
    and PSEUDO_FLAG = 'N'


===========================================
check JOC patch's
===========================================
select bug_number, last_update_date from ad_bugs where bug_number in ('5726582','5639951','5455628','5468797','5215394');


=====================
Check current sysdate
=====================
SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY HH:MM:SS AM')
       "Today's Date and Time" from DUAL;

===============================
Enable Apache and Jserv logging
===============================


Jserv
 Modify $ORACLE_HOME/Apache/Jserv/etc/jserv.properties
 Make sure the log switch is set to true. Can also specify the location
 of the jserv log file if so desired:

      log=true
      log.timestamp=true
      log.file=<path to jserv log file>/jserv.log
      log.channel.servletException=true
      log.channel.jservException=true
      log.channel.warning=true
      log.channel.servletLog=true
      log.channel.critical=true

Apache
# LogLevel: Control the number of messages logged to the error_log.
   # Possible values: debug,info,notice,warn,error,crit,alert,emerg
     Loglevel warn


=================================================================
Session info: high cpu consumption spid session info
=================================================================
set verify off
set echo off
set head off
set pages 1000
PROMPT Enter SPID :
ACCEPT 1
  select 'SID............ : ' || a.sid
|| chr(10) ||
         'SERIAL#........ : ' || a.serial#
|| chr(10) ||
         'USERNAME....... : ' || a.username
|| chr(10) ||
         'COMMAND........ : ' || a.command
|| chr(10) ||
         'STATUS......... : ' || a.status
|| chr(10) ||
         'Machine........ : ' || a.machine
|| chr(10) ||
         'Terminal....... : ' || a.terminal
|| chr(10) ||
         'Program........ : ' || a.program
|| chr(10) ||
         'Module........ : ' || a.module
|| chr(10) ||
         'SQL Hash Value. : ' || a.sql_hash_value
|| chr(10) ||
         'Logon Time..... : ' || to_char(a.logon_time,'DD-Mon-YYYY HH:MI:SS')
|| chr(10) ||
         'Last Call Et... : ' || a.last_call_et
|| chr(10) ||
         'Process ID..... : ' || a.process
|| chr(10) ||
         'SPID........... : ' || b.spid
  from   v$session a, v$process b
  where  a.paddr=b.addr     and b.spid='&1';
  PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  PROMPT  Process ID is  process col. in v$session table.
  PROMPT  SPID       is  spid    col. in v$process table.
  PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  select 'Session Waiting for event...: ' || event
  from    v$session_wait
  where   sid=(select sid from v$session a, v$process b where  a.paddr=b.addr
  and b.spid='&1');
  PROMPT  SQL STATEMENT :
  PROMPT  ===============
  select sql_text
  from   v$sqltext
  where  hash_value=(select sql_hash_value from v$session a, v$process b where
a.paddr=b.addr  and b.spid='&1')
  order  by piece;
==============================================