Ahmad Bilal has thankfully decided to share some of the commonly used scripts that he uses.
Please find these scripts below.
Note: - all queries are executed on RDBMS: 9.2.0.6.0 Oracle Applications: 11.5.10.2 by using Toad for Oracle version 8.5.0.50
| |
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; |
| |
Purpose | To get Menus Associated with responsibility |
Description | User to check menu attached with a reponsilblity |
Parameters | responsibility_id Which user can get from query of section Responsibilities Listing |
Query | 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 = ‘20538’ 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 | 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'; |
| |
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); |
| |
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_id = frg.request_id ORDER BY responsibility_name |
| |
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; |
| |
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 |
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; |
| |
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; |
| |
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 order by order by user_function_name; |
| |
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_id = frg.request_id AND frgu.request_unit_id = fcpv.concurrent_program_id AND frv.request_id = frg.request_id ORDER BY responsibility_name; /; |
| |
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 |
| |
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; |
| |
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.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 | Check responsibility assigned to a specific USER |
Description | |
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) |
| |
Purpose | Check Current Applied Patch |
Description | |
Parameters | None |
Query | SELECT patch_name, patch_type, maint_pack_level, creation_date FROM applsys.ad_applied_patches ORDER BY creation_date DESC |
Comments
Do you have query for which all the Programs I can submit from a Responsibility. I am not talking only about Requests being submit from View ---> Request Menu. I am talking about Requests which can also be submitted from any function.
Le t me know if you need more Clarification on this.
Lookin g forward for feedback.
Ma ny Thanks.
Sach in Ahuja
Doin g a great Job..
Thanks
Thanks A TON
Great!Tha t is real handy!
Great, it is really useful..
Than ks
ARIF
Can u help me plz, I have to customize the AP Invoice Approval workflow
to send notification to superuser when ever invoice amount execeed US$10000
Thank s
ARIF
Thanks very much for sharing the scripts with the users
could you please give me script to extract all the concurrent request that run during the specific period and the time taken to complete the job
Hope you could help me with my request
Thanks
Anna
i have a problem in nambering my sequence(Autonu mber)
can u help me...
how create a new sequence which will containg the existing number in a good numbering format from 1 up maxvalue.
Thank s
These are useful scripts.
Do you know if there is anyway of identifying the Oracle forms which make reference to DFF's?
I need to do some analysis of how DFF's are being used across a number of orgs setup in a multi org arrangement.
I can view the tables/attribut es but I ideally need to see which forms use DFF and make reference to the table/attribute .
Any ideas on an SQL script for this?
Thanks
There are really very useful scripts. Thanks a lot.
Regards,
I'm suguna, working as Oracle Associate Consultant in Ispace Softwares Pvt ltd., could you tell about Data Load software like how it works because i tried before but i was unable to get the Accounts from Data Load. So please help in this matter.
Thank ing you,
Suguna.
Those scripts above are really helpful ,
just wanted to say keep up this good work ,
may God bless you.
Thanks & Regards,
MD Kashif.
RSS feed for comments to this post