Useful EBS Queries

Concurrent programs run between particular time
++++++++++++++++++++++++++++++++++++

select
r.REQUEST_ID,p.user_concurrent_program_name Program_name,user_name usr,r.PHASE_CODE,r.STATUS_CODE,r.RESPONSIBILITY_ID,r.ACTUAL_START_DATE,r.ACTUAL_COMPLETION_DATE,r.ARGUMENT_TEXT
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_processes c,
apps.fnd_concurrent_queues q,
apps.fnd_concurrent_programs_vl p,
apps.fnd_user usr
where
p.concurrent_program_id = r.concurrent_program_id
and p.application_id = r.program_application_id
and usr.user_id = r.requested_by
and c.concurrent_process_id = r.controlling_manager
and q.concurrent_queue_id = c.concurrent_queue_id
and actual_start_date between to_date('01-APR-2019 00:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('03-FEB-2020: 14:00:00','DD-MON-YYYY HH24:MI:SS')
and r.status_code = 'C'
and r.phase_code in ('C')
order by to_char(r.actual_start_date,'DD-MON-YYYY HH24:MI:SS') asc;



Check for Custom PROFILE
++++++++++++++++++++++

SELECT e.profile_option_name Profile,
decode(a.level_id,10001,'Site',10002,c.application_short_name,10003,b.responsibility_name,10004,d.user_name) LValue,
nvl(a.profile_option_value,'Is Null') Value
FROM fnd_profile_option_values a, fnd_responsibility_tl b,
fnd_application c, fnd_user d, fnd_profile_options e, fnd_profile_options_vl f
WHERE f.user_profile_option_name like '%<>%'
AND e.profile_option_id = a.profile_option_id
AND e.profile_option_id = f.profile_option_id
AND a.level_value = b.responsibility_id (+)
AND a.level_value = c.application_id (+)
AND a.level_value = d.user_id (+)
ORDER BY 1,2;

LIST OF System Administrator Resp Assigned IN PAST 30 DAYS'
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SELECT fu.user_name "User Login", fu.start_date "Login Start Date", fu.end_date "Login End Date",
fu.employee_id "Employee Id",
hou.NAME "Business Group",
fr.responsibility_name "Responsibility Associated",
fur.start_date "Association Start Date",
fur.end_date "Association End Date",
fur.LAST_UPDATED_BY,
fur.START_DATE,
fur.END_DATE,
fur.CREATED_BY,
fur.LAST_UPDATED_BY,
fur.LAST_UPDATE_DATE
FROM apps.fnd_user fu,
apps.per_all_people_f ppf,
apps.hr_all_organization_units hou,
apps.fnd_user_resp_groups_all fur,
apps.fnd_responsibility_tl fr
WHERE ppf.person_id = fu.employee_id
AND hou.organization_id = ppf.business_group_id
AND fu.user_id = fur.user_id
AND fur.START_DATE  BETWEEN SYSDATE-30 AND SYSDATE
AND fr.responsibility_name LIKE '%System%Administrator%'
AND fur.responsibility_id = fr.responsibility_id
AND fr.LANGUAGE = 'US'
ORDER BY fu.user_name, fr.responsibility_name;

'LIST OF SYSADMIN USERS LOGONS WITH RESPONSIBILITIES IN PAST 7 Days'
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

select d.user_name,c.user_id,a.*, b.responsibility_name from APPLSYS.FND_LOGIN_RESPONSIBILITIES a,
APPLSYS.FND_RESPONSIBILITY_TL b,  APPLSYS.fnd_logins c, APPLSYS.fnd_user d
where a.login_id  = c.login_id
and c.user_id in (0) and trunc (c.start_time) BETWEEN SYSDATE-8 AND SYSDATE
and A.RESPONSIBILITY_ID = b.RESPONSIBILITY_ID
and d.user_id = c.user_id order by  trunc(c.start_time) asc;

USERS WITH System Administrator RESP ACCESSED IN LAST 7 DAYS'
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select fu.user_name, max(flr.start_time), flr.responsibility_id ,
flt.responsibility_name
from apps.fnd_logins fl, apps.fnd_login_Responsibilities flr, apps.fnd_user fu ,
apps.fnd_responsibility_tl flt
where flr.login_id = fl.login_id
and flt.responsibility_id = flr.responsibility_id
and fu.user_id = fl.user_id
and flr.RESPONSIBILITY_ID='20420'
and fl.start_time BETWEEN SYSDATE-7 AND SYSDATE
group by fl.user_id, flr.RESPONSIBILITY_ID ,fu.user_name,
flt.responsibility_name
order by fl.user_id;

Pending requests in OPP

=====================

select REQUEST_ID,PHASE_CODE,STATUS_CODE from fnd_concurrent_requests

where request_id in (

select concurrent_request_id

from fnd_conc_pp_actions

where action_type >= 6

and processor_id is null)

and PHASE_CODE!='C'

Recent changed profiles for USER SYSADMIN.

==================================

column profile format a40

column value format a20

column level format a15

column app format a20

column resp format a20

column user format a20

select substr(fpo.user_profile_option_name,1,60) profile,

substr(fpov.profile_option_value,1,75) value,

fpov.profile_option_id,

decode(fpov.level_id,10001,

'SITE',10002,

'APPLICATION',10003,

'RESPONSIBILITY',

10004,'USER',10005,'SERVER')"level",

fa.application_short_name app,

substr(fr.responsibility_name,1,20)"responsibility",

substr(fu.user_name,1,10)"user"

from fnd_profile_option_values fpov,

fnd_profile_options_vl fpo,

fnd_application fa,

fnd_responsibility_vl fr,

fnd_user fu,

fnd_logins fl

where fpo.profile_option_id=fpov.profile_option_id

and fa.application_id(+)=fpov.level_value

and fr.application_id(+)=fpov.level_value_application_id

and fr.responsibility_id(+)=fpov.level_value

and fu.user_id(+)=fpov.level_value

and fl.login_id(+) = fpov.LAST_UPDATE_LOGIN

and fpo.user_profile_option_name in('Client Timezone') and fu.USER_NAME='SYSADMIN';

Profiles changed in last 2 days

+++++++++++++++++++++

column profile format a40

column value format a20

column level format a15

column app format a20

column resp format a20

column user format a20

select p.profile_option_name SHORT_NAME, n.user_profile_option_name "PROFILE NAME",

decode(v.level_id, 10001, 'Site', 10002, 'Application',

10003, 'Responsibility', 10004, 'User', 10005, 'Server',

10007, 'SERVRESP', 'UnDef') LEVEL_SET,

decode(to_char(v.level_id), '10001', '',

'10002', app.application_short_name, '10003', rsp.responsibility_key,

'10005', svr.node_name, '10006', org.name, '10004', usr.user_name,

'10007', 'Serv/resp', 'UnDef') "CONTEXT", v.profile_option_value VALUE, v.LAST_UPDATE_DATE 

from fnd_profile_options p,

fnd_profile_option_values v,

fnd_profile_options_tl n,

fnd_user usr,

fnd_application app,

fnd_responsibility rsp,

fnd_nodes svr,

hr_operating_units org

where p.profile_option_id = v.profile_option_id (+)

and p.profile_option_name = n.profile_option_name

--and upper(n.user_profile_option_name) like upper('BNE%')

--and trunc(v.LAST_UPDATE_DATE) > trunc(sysdate-1)

and usr.user_id (+) = v.level_value

and rsp.application_id (+) = v.level_value_application_id

and rsp.responsibility_id (+) = v.level_value

and app.application_id (+) = v.level_value

and svr.node_id (+) = v.level_value

and org.organization_id (+) = v.level_value

and v.LAST_UPDATE_DATE is not null

order by last_update_date desc, short_name, level_set;