Useful Oracle E-Business Suite Scripts Part - 1

Below are the various Oracle R12 E-Business Suite scripts that can be handy during support,upgrade,etc.,

Query for Scheduled reqeuts start on prior run less than 5 mins
select cs.request_id,cs.concurrent_program_id,cs.program,cs.argument_text,cr.resubmit_interval_type_code,cs.requestor,resubmit_interval,resubmit_interval_unit_code from fnd_concurrent_requests cr,fnd_conc_req_summary_v cs where
 cs.phase_code='P'
and cs.status_code in ('I','Q')
and
resubmit_interval_type_code='START' and cr.request_id=cs.request_id
and resubmit_interval_unit_code='MINUTES' and resubmit_interval<5

Script to disable all scheduled requests - After cloning
update fnd_concurrent_requests
set phase_code='C',
status_code='D'
where phase_code = 'P'
and (status_code = 'I' OR status_code = 'Q');
and requested_start_date >= SYSDATE
and hold_flag = 'N';

Number of times Purge Concurrent Request execution was run in last 30 days
set pages 1000
set line 132
set head on
select  r.requested_start_date,r.actual_start_date,r.phase_code,r.status_code,r.actual_completion_date from
apps.fnd_concurrent_programs_vl p, apps.fnd_concurrent_requests r
where p.concurrent_program_id = r.concurrent_program_id
and p.application_id = r.program_application_id
and p.user_concurrent_program_name like 'Purge Concurrent Request and/or Manager Data'
and  r.actual_start_date >= sysdate-30 order by r.requested_start_date;

Number of times gather schema stats was run in last 30 days
set pages 1000
set line 132
set head on
select  r.request_id,r.actual_start_date,r.phase_code,r.status_code,r.actual_completion_date,argument_text from
apps.fnd_concurrent_programs_vl p, apps.fnd_concurrent_requests r
where p.concurrent_program_id = r.concurrent_program_id
and p.application_id = r.program_application_id
and p.user_concurrent_program_name like '%Gather Schema Statistics%'
and  r.actual_start_date >= sysdate-30 order by r.requested_start_date;

Concurrent Requests Average/Max/Min hours runtime details
set linesize 200
col username for a10
col status for a10
col phase  for a10
col PNAME for a70
col request_id for 99999999999
col PNAME  for a40
select
b.user_name username,
a.USER_CONCURRENT_PROGRAM_NAME  as PNAME,
avg((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) avg_Hrs_running,  
max((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) Max_Hrs_running,
min((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) Min_Hrs_running
from
apps.fnd_conc_req_summary_v a,
apps.fnd_user  b
where
phase_code = 'C' and status_code = 'C' and
a.REQUESTED_START_DATE > sysdate-30 and
upper(a.USER_CONCURRENT_PROGRAM_NAME) like upper('%&str%') and
a.REQUESTED_BY=b.user_id
group by b.user_name,a.USER_CONCURRENT_PROGRAM_NAME;  


Oracle EBS user level scripts
Given a user name list all the responsibilities assigned to this user
SELECT UNIQUE u.user_id, u.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 u.USER_NAME = :USER_NAME
AND 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)


 This is to list the  Apps User who are assigned a particular responsibility
select a.user_name,b.responsibility_name 
from  fnd_user a,fnd_responsibility_vl b ,FND_USER_RESP_GROUPS c
where a.user_id = c.user_id
and c.RESPONSIBILITY_ID = b.RESPONSIBILITY_ID
and lower(b.responsibility_name) like lower('&Responsibility_name%')
and a.END_DATE is null
and (c.END_DATE is null
or c.end_date > sysdate)
group by a.USER_NAME,b.RESPONSIBILITY_NAME

List Responsibilities That Can Run a Given Concurrent Program
This script helps in identifying the list of responsibilities that can run a given concurrent request

SELECT responsibility_name RN
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 (UPPER(fcpv.concurrent_program_name) = UPPER('&1')
OR
UPPER(fcpv.user_concurrent_program_name) = UPPER('&1'))
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
/


How to create to Oracle DB User which is like APPS but has only SELECT privileges
(this user should not have to use schema name.objectname and all SELECT qeries run from APPS user should work from this DB User as well)
a.       Create a user using
Create user ‘username’ identified by ‘password’;
b.      Then execute the following command in the sql prompt as the sys user,
spool create_grants.sql
set echo off
set pagesize 0
set linesize 300
set feedback off
SELECT 'Grant select on APPS.'||object_name||' to A;' FROM user_objects WHERE object_type IN ('TABLE','VIEW');
spool off;
exit;
c.       Executing the script  would create a sql file name create_grants.sql that has all the ‘SELECT priveleges’ of all the objects that the APPS user has access to..
d.      Finally run the create_grants.sql script.