1. Query To get list of responsibilities.
========================================================================
/* Formatted on 6/24/2014 11:14:55 AM (QP5 v5.115.810.9015) */
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
WHERE frt.responsibility_name LIKE 'Quality Man%';
2. Query To get Menus Associated with responsibility
=======================================================================
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';
3. Query To get submenus and Function attached to this Main menu.
========================================================================
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. Query To get assigned responsibility to a user.
========================================================================
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. Query To get responsibility and attached request groups.
======================================================================
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. Query To get modified profile options.
======================================================================
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. Query To get modified profile options.
===============================================================================
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. Query To get Patch Level.
================================================================================
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. Query To get all Functions
===============================================================================
SELECT function_id, user_function_name, creation_date, description
FROM fnd_form_functions_tl
ORDER BY user_function_name
10. Query To get all Request (conc. Program) attached to a responsibility
===============================================================================
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. Query To get all request with application
===============================================================================
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. Query To Count Module Wise Report
===============================================================================
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. Query To calculate request time
===============================================================================
/* Formatted on 6/24/2014 11:16:57 AM (QP5 v5.115.810.9015) */
SELECT pt.user_concurrent_program_name user_concurrent_program_name,
DECODE (p.concurrent_program_name,
'ALECDC', p.concurrent_program_name || '[' || f.description || ']',
p.concurrent_program_name)
concurrent_program_name,
f.request_id,
a.requestor,
f.argument_text,
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 (f.phase_code, 'R', 'Running', 'C', 'Complete', f.phase_code)
phase,
f.status_code
FROM apps.fnd_concurrent_programs p,
apps.fnd_conc_req_summary_v a,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
WHERE a.request_id = f.request_id
AND 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
-- AND pt.user_concurrent_program_name = '&Conc_prog_name'
AND f.request_id = :p_request_id
ORDER BY f.request_id DESC,
f.actual_completion_date - f.actual_start_date DESC;
14. Query Check responsibility assigned to a specific USER
===============================================================================
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)
15. Query Check Current Applied Patch
===============================================================================
SELECT patch_name, patch_type, maint_pack_level, creation_date
FROM applsys.ad_applied_patches
ORDER BY creation_date DESC
16. Script to check Concurrent Request Details
==============================================================================
SELECT a.request_id, a.user_concurrent_program_name, a.requestor, b.argument_text,
a.completion_text, a.actual_start_date, a.actual_completion_date,
a.argument_text, b.logfile_name, b.logfile_node_name, b.outfile_name,
b.outfile_node_name, a.responsibility_id, c.responsibility_name,
c.description, a.phase_code, a.status_code
FROM fnd_conc_req_summary_v a,
fnd_concurrent_requests b,
fnd_responsibility_vl c
WHERE 1 = 1
AND a.responsibility_id = c.responsibility_id
--and trunc(a.request_date) >= trunc(sysdate)-1
AND TRUNC (a.request_date) > '30-Jul-2008'
AND a.request_id = b.request_id
--and a.requestor not in ('SYSADMIN')
--and a.requestor in ('INTERFACES')
--and a.user_concurrent_program_name like '%Customer%'
--and a.argument_text like '%posarbinv050607112000%'
--and a.request_id between 427953 and 428200
--and a.request_id > 434045
--and responsibility_name = '&resp_name'
ORDER BY a.request_id DESC;
17. Script to print the Oracle Apps Version Number
========================================================
SELECT SUBSTR (a.application_short_name, 1, 5) code,
SUBSTR (t.application_name, 1, 50) application_name,
p.product_version VERSION
FROM fnd_application a, fnd_application_tl t, fnd_product_installations p
WHERE a.application_id = p.application_id
AND a.application_id = t.application_id
AND t.LANGUAGE = USERENV ('LANG') ;
18. Script to display status of all the Concurrent Managers
========================================================
SELECT DISTINCT concurrent_process_id "Concurrent Process ID",
pid "System Process ID", os_process_id "Oracle Process ID",
q.concurrent_queue_name "Concurrent Manager Name",
p.process_status_code "Status of Concurrent Manager",
TO_CHAR
(p.process_start_date,
'MM-DD-YYYY HH:MI:SSAM'
) "Concurrent Manager Started at"
FROM fnd_concurrent_processes p,
fnd_concurrent_queues q,
fnd_v$process
WHERE q.application_id = queue_application_id
AND q.concurrent_queue_id = p.concurrent_queue_id
AND spid = os_process_id
AND process_status_code NOT IN ('K', 'S')
ORDER BY concurrent_process_id, os_process_id, q.concurrent_queue_name
19. Script For Audit Changes in Profile Options
==========================================================================
SELECT '***Profile Option Name ***'
|| a.user_profile_option_name
|| '*** Was Updated with value '
|| '”'
|| b.profile_option_value
|| '”'
|| ' In The Last '
|| :p_no_of_days
|| ' days'
|| ' by '
|| (SELECT user_name
FROM apps.fnd_user u
WHERE u.user_id = b.last_updated_by) mesg
FROM apps.fnd_profile_options_vl a,
apps.fnd_profile_option_values b,
apps.fnd_user c
WHERE a.profile_option_id = b.profile_option_id
AND b.last_updated_by = c.user_id
AND ( b.last_update_date > SYSDATE - :p_no_of_days
OR b.creation_date > SYSDATE - :p_no_of_days
);
20. Script to find Object Locked and to kill the session
==============================================================================
SELECT vlo.os_user_name "OS USERNAME", vlo.oracle_username "DB USER",vs.SID,vs.SERIAL#,
vp.spid "SPID", ao.owner "OWNER", ao.object_name "OBJECT LOCKED",
ao.object_type,
DECODE (vlo.locked_mode,
1, 'NO LOCK',
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCL',
6, 'EXCLUSIVE',
NULL
) "MODE OF LOCK",
vs.status "CURRENT STATUS"
FROM v$locked_object vlo, all_objects ao, v$session vs, v$process vp
WHERE vlo.object_id = ao.object_id
AND vs.status <> 'KILLED'
AND vlo.session_id = vs.SID
AND vs.paddr = vp.addr
alter system kill session 'SID,SERIAL#'
21.Script to check Flexfield Setup
==================================================================
SELECT id_flex_structure_code structure_code,
DECODE (dynamic_inserts_allowed_flag,
'Y', 'OK: Dynamic',
'ERROR: No Dynamic'
) dynamic,
DECODE (freeze_flex_definition_flag,
'Y', 'OK: Freezed',
'ERROR: No Freezed'
) freeze,
segment_num seg_number, segment_name seg_name,
DECODE (required_flag,
'Y', 'OK: Required',
'ERROR: No required'
) required,
DECODE (fvs.flex_value_set_name,
NULL, NULL,
fvs.flex_value_set_name
) value_set,
DECODE (fvs.validation_type,
'N', 'OK: No validation',
'ERROR: ' || fvs.validation_type
) VALIDATION,
DECODE (fvs.uppercase_only_flag,
'N', 'OK: No',
'ERROR: Uppercase Only'
) uppercase_only,
DECODE (alphanumeric_allowed_flag,
'Y', 'OK: Allowed',
'ERROR: Not Allowed'
) alphanumeric,
DECODE (numeric_mode_enabled_flag,
'N', 'OK:Not justified',
'ERROR: Justified'
) right_justify,
DECODE (format_type,
'C', 'OK: Char',
'ERROR: ' || format_type
) format_type
FROM apps.fnd_id_flex_structures_vl ffst,
apps.fnd_id_flex_segments_vl ffsg,
applsys.fnd_flex_value_sets fvs
WHERE ffst.application_id = 401
AND ffst.id_flex_code = 'MCAT'
AND ffst.enabled_flag = 'Y'
AND ffst.application_id = ffsg.application_id
AND ffst.id_flex_code = ffsg.id_flex_code
AND ffst.id_flex_num = ffsg.id_flex_num
AND ffsg.enabled_flag = 'Y'
AND fvs.flex_value_set_id(+) = ffsg.flex_value_set_id
AND id_flex_structure_code = 'PLANNING';
OR
SELECT id_flex_structure_code structure_code, segment_num seg_number,
segment_name seg_name, ffsg.application_column_name,
DECODE (fvs.flex_value_set_name,
NULL, NULL,
fvs.flex_value_set_name
) value_set
FROM apps.fnd_id_flex_structures_vl ffst,
apps.fnd_id_flex_segments_vl ffsg,
applsys.fnd_flex_value_sets fvs
WHERE ffst.application_id = 101
AND ffst.id_flex_code = 'GL#'
AND ffst.enabled_flag = 'Y'
AND ffst.application_id = ffsg.application_id
AND ffst.id_flex_code = ffsg.id_flex_code
AND ffst.id_flex_num = ffsg.id_flex_num
AND ffsg.enabled_flag = 'Y'
AND fvs.flex_value_set_id(+) = ffsg.flex_value_set_id
AND id_flex_structure_code = 'Dach COA';
select * from apps.fnd_id_flex_structures_vl where APPLICATION_ID = 401
select * from applsys.fnd_application where APPLICATION_SHORT_NAME = 'INV'
22. Script to list Report With Parameters
======================================================================
SELECT a.concurrent_program_name AS concurrent_program_name,
a.user_concurrent_program_name AS user_concurrent_program_name,
c.application_short_name AS application_short_name,
b.column_seq_num AS column_seq_num, b.srw_param AS param_seq,
b.form_left_prompt AS prompt,
d.flex_value_set_name AS values_set_name
FROM fnd_concurrent_programs_vl@ebs_to_aps a,
fnd_descr_flex_col_usage_vl@ebs_to_aps b,
fnd_application@ebs_to_aps c,
fnd_flex_value_sets@ebs_to_aps d
WHERE a.enabled_flag = 'Y'
AND a.concurrent_program_name =
SUBSTR (b.descriptive_flexfield_name, 7, 100)
AND a.application_id = c.application_id
AND b.enabled_flag = 'Y'
AND b.flex_value_set_id = d.flex_value_set_id
AND a.user_concurrent_program_name LIKE 'CM%'
ORDER BY a.concurrent_program_id, b.column_seq_num
23. Script to get current profile option value
=======================================================================
SELECT fat.application_name, frv.responsibility_name,
fpo.user_profile_option_name, pov.profile_option_value
FROM applsys.fnd_application_tl fat,
apps.fnd_responsibility_vl frv,
apps.fnd_profile_option_values pov,
apps.fnd_profile_options_vl fpo
WHERE pov.application_id(+) = fpo.application_id
AND pov.profile_option_id(+) = fpo.profile_option_id
AND pov.level_value = frv.responsibility_id(+)
AND fat.application_id = fpo.application_id
AND fat.application_name = 'Master Scheduling/MRP'
AND UPPER (fpo.user_profile_option_name) =
'MRP:DEFAULT SOURCING ASSIGNMENT SET'
ORDER BY 1, 3, 2
SELECT o.profile_option_name,v.profile_option_value
FROM fnd_profile_options o, fnd_profile_option_values v
WHERE o.profile_option_name = NVL(:1,o.profile_option_name)
AND v.level_id = NVL(:2,v.level_id)
AND o.start_date_active <= SYSDATE
AND NVL (o.end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND v.level_value = nvL(:3,v.level_value)
24. Script to get the locked objects
======================================================================
/* SQL For locked objects*/
SELECT b.inst_id, b.session_id AS SID,
NVL (b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner, a.object_name,
DECODE (b.locked_mode,
0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode
) locked_mode,
b.os_user_name
FROM dba_objects a, gv$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
Find Running, Pending, On Hold and Scheduled Requests
=======================================================================
SELECT fcr.request_id,
DECODE (fcr.phase_code,
'P', DECODE (fcr.hold_flag,
'Y', 'Inactive',
fl_pend.meaning
),
fl_pend.meaning
) phase,
DECODE (fcr.phase_code,
'P', DECODE (fcr.hold_flag,
'Y', 'On Hold',
DECODE (SIGN (fcr.requested_start_date - SYSDATE),
1, 'Scheduled',
fl_stat.meaning
)
),
fl_stat.meaning
) status,
fcpt.user_concurrent_program_name, fcr.increment_dates,
fcr.resubmit_interval, fcr.resubmit_interval_unit_code,
fcr.resubmit_interval_type_code, parent_request_id,
fcr.requested_start_date, fu.user_name requested_by
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcpt,
fnd_lookups fl_pend,
fnd_lookups fl_stat,
fnd_user fu
WHERE 1 = 1
AND fcpt.concurrent_program_id = fcr.concurrent_program_id
AND fcpt.LANGUAGE = USERENV ('LANG')
AND fcr.phase_code = fl_pend.lookup_code
AND fl_pend.lookup_type = 'CP_PHASE_CODE'
AND fcr.status_code = fl_stat.lookup_code
AND fl_stat.lookup_type = 'CP_STATUS_CODE'
AND fl_pend.meaning != 'Completed'
AND fu.user_id = fcr.requested_by
ORDER BY fcr.request_id DESC
========================================
Profile Value at any Level
========================================SELECT DISTINCT pot.user_profile_option_name PROFILE,
DECODE (a.profile_option_value,
'1', '1 (may be "Yes")',
'2', '2 (may be "No")',
a.profile_option_value
) VALUE,
DECODE (a.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
'????'
) level_identifier,
DECODE (a.level_id,
10002, e.application_name,
10003, c.responsibility_name,
10004, d.user_name,
'-'
) level_name
FROM applsys.fnd_application_tl e,
applsys.fnd_user d,
applsys.fnd_responsibility_tl c,
applsys.fnd_profile_option_values a,
applsys.fnd_profile_options b,
applsys.fnd_profile_options_tl pot
WHERE 1 = 1
AND UPPER (pot.user_profile_option_name) LIKE
UPPER ('%&v_profile%')
AND pot.profile_option_name = b.profile_option_name
AND b.application_id = a.application_id(+)
AND b.profile_option_id = a.profile_option_id(+)
AND a.level_value = c.responsibility_id(+)
AND a.level_value = d.user_id(+)
AND a.level_value = e.application_id(+)
AND ( UPPER (e.application_name) LIKE
UPPER ('%&appname_respname_username%')
OR UPPER (c.responsibility_name) LIKE
UPPER ('%&&appname_respname_username%')
OR UPPER (d.user_name) LIKE
UPPER ('%&&appname_respname_username%')
)
ORDER BY PROFILE, level_identifier, level_name, VALUE
=============================================
Which User is Locking the table
=============================================SELECT c.owner, c.object_name, c.object_type,
fu.user_name locking_fnd_user_name,
fl.start_time locking_fnd_user_login_time, vs.module, vs.machine,
vs.osuser, vlocked.oracle_username, vs.SID, vp.pid,
vp.spid AS os_process, vs.serial#, vs.status, vs.saddr, vs.audsid,
vs.process
FROM fnd_logins fl,
fnd_user fu,
v$locked_object vlocked,
v$process vp,
v$session vs,
dba_objects c
WHERE vs.SID = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || UPPER ('&tabname_blank4all') || '%'
AND NVL (vs.status, 'XX') != 'KILLED';
==========================================
Link Purchase Order and Requisition
=========================================SELECT prh.segment1 req_number, prh.authorization_status,
prl.line_num req_line_num, prl.item_description req_item_description,
prl.unit_price req_unit_price, prl.quantity req_quantity,
pd.req_header_reference_num, pd.req_line_reference_num, pl.line_num,
pl.item_description, pl.quantity, pl.amount, ph.segment1 po_number,
prd.distribution_id, pd.req_distribution_id
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd,
po_distributions_all pd,
po_line_locations_all pll,
po_lines_all pl,
po_headers_all ph
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prh.org_id = prl.org_id
AND prl.requisition_line_id = prd.requisition_line_id
AND prl.org_id = prd.org_id
AND prd.distribution_id = pd.req_distribution_id(+)
AND prd.org_id = pd.org_id(+)
AND pd.line_location_id = pll.line_location_id(+)
AND pd.org_id = pll.org_id(+)
AND pll.po_line_id = pl.po_line_id(+)
AND pll.org_id = pl.org_id(+)
AND pl.po_header_id = ph.po_header_id(+)
AND pl.org_id = ph.org_id(+)
======================================================================
Query to find out the responsibility,Menu based on Function
======================================================================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 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'
AND b.menu_id IN (
SELECT menu_id
FROM fnd_menu_entries_vl
WHERE function_id IN (
SELECT function_id
FROM applsys.fnd_form_functions_tl
WHERE user_function_name =
'Fujitsu Trip Sequence'))
========================================================================
Query to delete the data definitions and concurrent program
=====================================================================
BEGIN
xdo_ds_definitions_pkg.delete_row (
x_application_short_name => 'APPL_SHORT_NAME',
x_data_source_code => 'DATA_SOURCE_CODE');
COMMIT;
END;
BEGIN
fnd_global.apps_initialize (FND_GLOBAL.user_id, FND_GLOBAL.resp_id, FND_GLOBAL.resp_appl_id);
COMMIT;
FND_PROGRAM.delete_program (
program_short_name => 'PROG_SHORT_NAME',
application => 'Prog_appl_name');
COMMIT;
END;
========================================================================
Query to get the List of parameters for a Concurrent program in Oracle Applications
========================================================================
SELECT p.user_concurrent_program_name "NAME"
, c.concurrent_program_name "INTERNAL"
, f.end_user_column_name "PARAMETER"
, f.enabled_flag "ON_OFF"
, f.DEFAULT_VALUE
, f.required_flag
, f.description
FROM fnd_descr_flex_col_usage_vl f
, fnd_concurrent_programs_tl p
, fnd_concurrent_programs c
WHERE SUBSTR( f.descriptive_flexfield_name
, 7
, 8 ) = c.concurrent_program_name
AND c.concurrent_program_id = p.concurrent_program_id
AND p.user_concurrent_program_name LIKE '%Purge Obsolete Workflow Runtime Data%'
AND p.language = 'US'
ORDER BY f.descriptive_flexfield_name, f.column_seq_num;
=================================================================
Query to find the Trace file path for Concurrent Program
=================================================================
SELECT 'Request id: ' || request_id
, 'Trace id: ' || oracle_process_id
, 'Trace Flag: ' || req.enable_trace
, 'Trace Name:
' || dest.VALUE || '/' || LOWER (dbnm.VALUE) || '_ora_' || oracle_process_id || '.trc'
, 'Prog. Name: ' || prog.user_concurrent_program_name
, 'File Name: ' || execname.execution_file_name || execname.subroutine_name
, 'Status : ' || DECODE (phase_code
, 'R', 'Running'
) || '-' || DECODE (status_code
, 'R', 'Normal'
)
, 'SID Serial: ' || ses.SID || ',' || ses.serial#
, 'Module : ' || ses.module
FROM fnd_concurrent_requests req
, v$session ses
, v$process proc
, v$parameter dest
, v$parameter dbnm
, fnd_concurrent_programs_vl prog
, fnd_executables execname
WHERE req.request_id = :p_request_id
AND req.oracle_process_id = proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.NAME = 'user_dump_dest'
AND dbnm.NAME = 'db_name'
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
--- and prog.application_id = execname.application_id
AND prog.executable_application_id = execname.application_id
AND prog.executable_id = execname.executable_id;
SELECT request_id
, TO_CHAR (request_date, 'DD-MON-YYYY HH24:MI:SS') request_date
, TO_CHAR (requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start_date
, TO_CHAR (actual_start_date, 'DD-MON-YYYY HH24:MI:SS') actual_start_date
, TO_CHAR (actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') actual_completion_date
, TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS') CURRENT_DATE
, ROUND ((NVL (actual_completion_date, SYSDATE) - actual_start_date) * 24, 2) DURATION
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER (:p_request_id);
