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

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

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

**********************************************************************************


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]


My Cloud Certifications:
Oracle Cloud Infrastructure 2018 Certified Architect Associate.
Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.
Oracle Database Cloud Administrator Certified Professional.
Oracle Database Cloud Service Operations Certified Associate.

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: