Commonly used scripts

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

Query to get all Request 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;

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;

Query to calculate request time
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;

Query to 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)

Query to print the oracle apps version
SELECT substr(a.application_short_name, 1, 5) application_short_name,
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')

Script used to pick up the recent EBS users and their email addresses
SELECT DISTINCT ppf.full_name
, fu.user_name
, NVL(ppf.email_address,fu.email_address) email_address
FROM per_people_f ppf
, fnd_user fu
, fnd_logins fl
WHERE fl.start_time > SYSDATE - 2
AND fu.user_id = fl.user_id
AND ppf.person_id(+) = fu.employee_id
AND fu.user_name NOT IN ('SYSADMIN', 'GUEST')

Script to assign access to Oracle e-Business Suite responsoibilities via Workflow
DECLARE
v_user fnd_user.user_name%TYPE;
v_user_id PLS_INTEGER;

CURSOR c_get_user (cp_username IN VARCHAR2)
IS
SELECT user_orig_system_id user_id, user_orig_system orig_system
FROM wf_local_user_roles
WHERE user_name = cp_username;

CURSOR c_get_resp
IS
SELECT NAME, orig_system, orig_system_id, partition_id
FROM wf_roles
WHERE display_name = 'System Administrator'
AND orig_system = 'FND_RESP';

r_resp c_get_resp%ROWTYPE;
r_user c_get_user%ROWTYPE;
BEGIN
v_user := UPPER ('&username');

OPEN c_get_user (cp_username => v_user);

FETCH c_get_user
INTO r_user;

CLOSE c_get_user;

OPEN c_get_resp;

FETCH c_get_resp
INTO r_resp;

CLOSE c_get_resp;

INSERT INTO wf_local_user_roles
(user_name, role_name, user_orig_system, user_orig_system_id,
role_orig_system, role_orig_system_id, start_date,
expiration_date, security_group_id, partition_id, owner_tag,
created_by, creation_date, last_updated_by,
last_update_date, last_update_login, assignment_type,
parent_orig_system, parent_orig_system_id, user_start_date,
role_start_date, user_end_date, role_end_date,
effective_start_date, effective_end_date
)
VALUES (v_user, r_resp.NAME, r_user.orig_system, r_user.user_id,
r_resp.orig_system, r_resp.orig_system_id, SYSDATE - 1,
NULL, 0, r_resp.partition_id, 'SYSADMIN',
0, SYSDATE - 1, 0,
SYSDATE - 1, 0, 'D',
r_resp.orig_system, r_resp.orig_system_id, SYSDATE - 1,
TO_DATE ('01/01/1951', 'DD/MM/YYYY'), NULL, NULL,
SYSDATE - 1, NULL
);
END;

Query to fetch the managers managers information in HRMS
select
papf.employee_number
,papf.person_id employee_person_id
,papf.full_name employee_name
,papf.email_address emp_email_address
,papf.employee_number mgr_employee_number
,papf_mgr.person_id manager_person_id
,papf_mgr.FULL_NAME manager_full_name
,papf_mgr.email_address manager_email_address
from
per_all_people_f papf,
per_all_people_f papf_mgr
where
(papf.person_id,papf_mgr.person_id) in
( select
pasf.person_id,
pasf.supervisor_id
from
per_all_Assignments_f pasf
start with
( pasf.person_id = :x_person_id and
sysdate between pasf.effective_start_date and pasf.effective_end_date )
connect by prior
pasf.supervisor_id = pasf.person_id
and sysdate between pasf.effective_start_date and pasf.effective_end_date
)
and sysdate between papf.effective_start_date and papf.effective_end_date
and sysdate between papf_mgr.effective_start_date and papf_mgr.effective_end_date