APPS USER ACTIVITIES:

APPS USER ACTIVITIES:
====================

  1. Users Connected through application
Purpose
To get the list of users connected.
Description
Query gives the list of users connected to the application
Parameters
None
Query
SELECT user_name username,
description name,
to_char(b.first_connect,'MM/DD/RR HH24:MI') firstconnect,
to_char(b.last_connect,'MM/DD/RR HH24:MI') lastconnect
FROM apps.fnd_user a,
(SELECT MIN (first_connect) first_connect,
MAX (last_connect) last_connect, last_updated_byuser_id
FROM apps.icx_sessions
GROUP BY last_updated_by) b
WHERE a.user_id = b.user_id
AND last_connect>SYSDATE-3/12
ORDER BY 4 DESC
/


  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_applicationfa
WHERE fa.application_id = frt.application_id) application,
frt.responsibility_id, frt.responsibility_name
FROM apps.fnd_responsibility_tlfrt;

  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 sectionResponsibilities 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_groupsfrg, fnd_responsibility_vl frv
WHERE frv.request_group_id = frg.request_group_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_userfu
WHERE fu.user_id = ffcr.created_by) "Created By "
FROM fnd_form_custom_rulesffcr, 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_groupsfrg,
fnd_request_group_unitsfrgu,
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;


  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_applicationfa
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_applicationfa
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_nameuser_concurrent_program_name
, f.actual_start_dateactual_start_date
, f.actual_completion_dateactual_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_tlpt
, 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
This shows the applied patch list.
Parameters
None
Query
SELECT patch_name, patch_type, maint_pack_level, creation_date
FROM applsys.ad_applied_patches
ORDER BY creation_date DESC



Posted by MADHU APPS at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)