Friday 19 April 2013

Useful Queries For Oracle Applications

1. Responsibilities Listing
Purpose To get list of responsibilities
Description Query useful when user wants to get application wise responsibility list
Parameters None
Query 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 To get assigned responsibility to a user.
Description User wants to check responsibility attached to a specific user
Parameters None
Query 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);

3. Submenu And Function Listing
Purpose To get submenus and Function attached to this Main menu.
Description By using this query user can check function and submenus attached to that

specific menu
Parameters User_menu_name Which user can get from query of section

Menu Listing
Query 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 = ‘F4 UK

PAY Navigator’;

4. User And Assigned Responsibility Listing
Purpose To get assigned responsibility to a user.
Description User wants to check responsibility attached to a specific user
Parameters None
Query 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 To get responsibility and attached request groups.
Description Every responsibility contains a request group(request group is basis of

submitting requests)
Parameters None
Query 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 To get modified profile options
Description Query used for audit point of view i.e. when a profile is changed and by

whom user
Parameters None
Query 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 To get modified profile options
Description Personalization is feature available in 11.5.10.X.

For More detail on form Personalization Use Following Tables (Rule_id)

is reference key for these tables

applsys.fnd_form_custom_actions, applsys.fnd_form_custom_scopes
Parameters None
Query 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 To get Patch Level.
Description Query used to view the patch level status of all modules
Parameters None
Query 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. Function Listing
Purpose To get all Functions
Description Complete forms and functions
Parameters None
Query SELECT function_id, user_function_name, creation_date, description

FROM applsys.fnd_form_functions_tl

y order by user_function_name;

10. Request Attached To Responsibility Listing
Purpose To get all Request attached to a responsibility
Description View all request who have attached to a reponsiblity
Parameters None
Query 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;

11. Request Listing Application Wise
Purpose To get all request with application
Description View all types of request Application wise
Parameters None
Query 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

12. Count Module Wise Reports
Purpose To Count Module Wise Report
Description Application wise request counting
Parameters None
Query 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;

13. Request Status Listing
Purpose To calculate request time
Description This query will shows report processing time
Parameters None
Query 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.descriptio

n||']‘,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;

14. User And Responsibility Listing
Purpose Check responsibility assigned to a specific USER
Description This query will shows User Responsibilities list.
Parameters None
Query 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 = ‘Purchasing’

ORDER BY SUBSTR (user_name, 1, 30),

SUBSTR (a.application_name, 1, 50),

SUBSTR (r.responsibility_name, 1, 60)

No comments:

Post a Comment