Some sql scripts for analyzing Oracle eBS

1

While working in Oracle eBS it can sometimes be very helpful to have a quick overview of how and/or what is setup. Sometimes it is enough to just use what was made available to you and by clicking through the available setup screens you will simply find what you are looking for. However, in some cases you end up not having the right responsibilities to see these details or you just want to prevent scrolling and clicking through these windows or combine details from different screens.

In addition to the diagnostic scripts that Oracle provided there are various other scripts available that can be very useful from case to case.
 
I have posted some of the scripts I have been using in the past. If you have some good similar script(s) available that you have been using and willing to share, please feel free to do so.
I am sure you’ll make a lot of people happy.
 

 

 
 
 
//*
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;
 
 
 
//*
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';
 
 
 
//*
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';
 
 
 
//*
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);
 
 
 
//*
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
 
 
 
//*
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;
 
 
 
//*
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;
 
 
 
//*
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;
   
 
 
 
//*
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;
 
 
 
//*
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
 
 
 
//*
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;
 
 
 
//*
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;
 
 
 
//*
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)
 
 
 
//*
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
 
Share.

About Author

1 Comment