Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

CONTACT US

Ahmad Bilal
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new

Search Courses

×

Warning

JUser: :_load: Unable to load user with ID: 859

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

 



  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;




  1. Menus Listing

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';




  1. 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';




  1. 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);




  1. 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_id = frg.request_id

ORDER BY responsibility_name




  1. 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;




  1. 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;




  1. 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;




  1. 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

order by order by user_function_name;




  1. 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_id = frg.request_id

AND frgu.request_unit_id = fcpv.concurrent_program_id

AND frv.request_id = frg.request_id

ORDER BY responsibility_name;

/;




  1. 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



  1. 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;




  1. 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.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;





  1. User And Responsibility Listing

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)



  1. Applied Patch Listing

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   

0 #1 Venkat 2007-06-04 00:00
Very handy scripts useful for both Technical and Functional people.
Quote
0 #2 balkrishna 2007-06-05 00:00
Great!!!!!!!!!! !!
Quote
0 #3 Praveen 2007-06-06 00:00
Thanks a lot Ahmad & Anil, you are doing a great job.
Quote
0 #4 Sachin Ahuja 2007-06-06 00:00
Hi,

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
Quote
0 #5 kishore P 2007-06-07 00:00
Thanks Ahmad Bilal. A Great Contribution and very handy for all consultants.
Quote
0 #6 Anwar 2007-06-13 00:00
Amazing, very useful indeed.
Quote
0 #7 sainath 2007-06-17 00:00
very very useful we need same interaction with u
Quote
0 #8 Shiraz 2007-06-20 00:00
Thanks brother, Scripts are very handy.
Quote
0 #9 Ajit 2007-06-27 00:00
Hi Guyz...

Doin g a great Job..

Thanks
Quote
0 #10 Jehangeer Shaik 2007-07-05 11:17
Thanks brother, Scripts are very handy ...! :) .
Thanks A TON
Quote
0 #11 Prathibha 2007-07-05 17:31
Thanks Bilal
Great!Tha t is real handy!
Quote
0 #12 mohd arif ali 2007-07-14 06:05
Thank Bilal,
Great, it is really useful..


Than ks
ARIF
Quote
0 #13 mohd arif ali 2007-07-14 06:09
Hi,
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
Quote
0 #14 Anna 2007-08-19 11:50
Hi

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
Quote
0 #15 Atul Kumar 2007-08-23 23:04
Good one. Thanks to you and to your entire team
Quote
0 #16 Ramadhani 2007-08-28 13:01
Dear Ahmed,
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
Quote
0 #17 Said Shadi 2007-09-04 15:43
Hi Ahmed

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
Quote
0 #18 Kasaiah Akumalla 2008-01-24 01:52
Hi Ahmed,

There are really very useful scripts. Thanks a lot.

Regards,
Quote
0 #19 SDadi 2008-03-12 10:50
where can I get info on all the profiles attached to a particular responsibility?
Quote
0 #20 sugunalatha 2008-09-07 05:00
hi Ahmed,


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.
Quote
0 #21 MD Kashif 2015-04-02 04:57
HI Bilal,

Those scripts above are really helpful ,
just wanted to say keep up this good work ,
may God bless you.

Thanks & Regards,
MD Kashif.
Quote

Add comment


Security code
Refresh

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  May 2024  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
    1  2  3  4  5
  6  7  8  9101112
13141516171819
20212223242526
2728293031  

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner