**** Sid, serial#,status for a Concurrent Request
set linesize 10000
SELECT a.request_id, d.sid, d.serial# ,d.username,d.status,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id =&req_id
--AND a.phase_code = 'R';
/
*******Add Responsibilities to Users ***************
exec fnd_user_pkg.addresp('USERNAME','APPL_SHORT_NAME','RESP_KEY','SECURITY_GROUP','description',sstartdate,enddate);
**** Patch level and R12 product installations
select PATCH_LEVEL from fnd_product_installations
*** Find users with particular role:
SELECT * FROM APPS.WF_USER_ROLE_ASSIGNMENTS WHERE role_NAME ='UMX|9001A'
***** Find all or specific roles inside the R12
SELECT * FROM APPS.wf_local_roles where NAME ='UMX|9001A'
****** Find responsiblity_id for a particular responsibility:
select * from apps.FND_RESPONSIBILITY_TL where responsibility_name like '%Interface%'
****Assign roles via SQL or API
If there are more number of users
to assign the role then use the below API:
SQL> exec wf_local_synch.PropagateUserRole(p_user_name =>
'USERNAME',p_role_name => 'UMX|9001A' );
PL/SQL procedure successfully completed.
SQL> commit;
******Find users with particular specific responsibility or find user with all the responsibilities
SELECT fu.user_name "User Name",,fu.description,fu.last_logon_date,
fr.responsibility_name "Responsibility Name",
frg.start_date "Start Date",
frg.end_date "End Date"
FROM apps.fnd_user_resp_groups_direct frg,
applsys.fnd_user fu,
applsys.fnd_responsibility_tl fr
WHERE
-- UPPER(fu.user_name) = ''SAMEER' AND <-- ( Un comment if you want to see respb a particular user)
frg.user_id = fu.user_id
AND frg.responsibility_id = fr.responsibility_id
AND frg.responsibility_id = 50697 <--- ( Comment this if you want to see all resp for a user or give the resp_id for which you want to know all the users are assigned to)
*******Users with particular responsibility and a specific role
SELECT fu.user_name "User Name",
wf.role_name,
fu.description,
fu.last_logon_date,
fr.responsibility_name "Responsibility Name",
frg.start_date "Start Date",
frg.end_date "End Date"
FROM apps.fnd_user_resp_groups_direct frg,
applsys.fnd_user fu,
applsys.fnd_responsibility_tl fr,
APPS.WF_USER_ROLE_ASSIGNMENTS wf
WHERE
-- UPPER(fu.user_name) = ''SAMEER' AND <-- ( Un comment if you want to see respb a particular user)
frg.user_id = fu.user_id
AND frg.responsibility_id = fr.responsibility_id
AND frg.responsibility_id = 50697
AND fu.user_name=wf.user_name
AND WF. role_NAME ='UMX|9001'
AND fr.language = USERENV('LANG')
******** View SQL execution Plans
The view DBA_HIST_SQL_PLAN displays the execution plan information for each child cursor in the workload repository. This view captures information from V$SQL_PLAN and can be used in conjunction with the DBA_HIST_SQLSTAT view which shows historic statistics regarding SQL execution.
select sql_id, PLAN_HASH_VALUE,
to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') Timestamp
from DBA_HIST_SQL_PLAN
where sql_id= &SQL_ID
******** View scheduled concurrent requests
SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description, cp.user_concurrent_program_name ) NAME,
argument_text, cr.resubmit_interval, NVL2 (cr.resubmit_interval, 'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
) schedule_type, DECODE (NVL2 (cr.resubmit_interval, 'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE') ),
'PERIODICALLY', 'EVERY ' || cr.resubmit_interval || ' ' || cr.resubmit_interval_unit_code
|| ' FROM ' || cr.resubmit_interval_type_code || ' OF PREV RUN', 'ONCE', 'AT :'
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcr.class_info ) schedule,
fu.user_name, requested_start_date
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id;
*************** Query FND Nodes************
set lines 200
col host_name for a11
col node_name for a11
col database for a10
col concmgr for a9
col forms for a7
col webserver for a11
col admin for a7
select
NODE_NAME,
decode(STATUS,'Y','ACTIVE','INACTIVE') Status,
decode(SUPPORT_CP,'Y', 'YES','NO') ConcMgr,
decode(SUPPORT_FORMS,'Y','YES', 'NO') Forms,
decode(SUPPORT_WEB,'Y','YES', 'NO') WebServer,
decode(SUPPORT_ADMIN, 'Y','YES', 'NO') Admin,
decode(SUPPORT_DB, 'Y','YES', 'NO') Database
from fnd_nodes
where node_name != 'AUTHENTICATION' order by NODE_NAME;
********** Select SID,SERIAL,SPID based on username/module ....
--select count(distinct d.user_name) from apps.fnd_logins a,
select distinct b.inst_id,d.user_name,b.sid,b.serial#,c.spid,b.status
from
apps.fnd_logins a,gv$session b, gv$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and d.user_name='SHAIKSAMEER'
and b.module not like '%SQL Developer%'
--and (d.user_name = 'USER_NAME' OR 1=1);
***********Find profile values for a particular user *******
ALTER SESSION SET CURRENT_SCHEMA=apps;
SELECT po.user_profile_option_name,
po.profile_option_name "NAME" ,
DECODE (TO_CHAR (pov.level_id), '10001', 'SITE' , '10002', 'APP', '10003', 'RESP', '10004', 'USER', '???') "LEV",
DECODE (TO_CHAR (pov.level_id) , '10001', '', '10002', app.application_short_name , '10003', rsp.responsibility_key, '10004', usr.user_name, '???') "CONTEXT",
pov.profile_option_value "VALUE"
FROM fnd_profile_options_vl po,
fnd_profile_option_values pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp
WHERE usr.user_name='SYSADMIN'
--(po.profile_option_name = upper('&profil_name'))
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id(+) = pov.level_value
AND rsp.application_id(+) = pov.level_value_application_id
AND rsp.responsibility_id(+) = pov.level_value
AND app.application_id(+) = pov.level_value
ORDER BY "NAME", pov.level_id, "VALUE"
select p.profile_option_name SHORT_NAME, n.user_profile_option_name 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
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('%&profile_name%')
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 instr(v.profile_option_value,'http') > 0
order by short_name, level_set
