Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Thursday, August 9, 2012

R12 daily used views


**** 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


No comments: