Oracle Ebiz R12 -- How To Clear Caches

Oracle Ebiz R12 -- How To Clear Caches

 

In R12 you can directly clear the cache from UI as SYSADMIN.

1. Functional level (individual product/all product cache)
   Functional Administrator -> Core Services -> Caching Framework -> Global Configuration -> "Go to specific cache or Clear All Cache

 As sysadmin go to -->Functional Administrator

 

 go to --> Core Services

 

Then go to --> Caching Framework

Pick the component from the list or select all to purge all caches:

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

R12 Unable to View Output for Requests Submitted by other Users

R12  View Output for Requests Submitted by other Users.
In R12 in order to view requests submitted by other users we need to create Roles and grant these roles to users.

Step by step instructions on how to implement this:

As SYSADMIN user with “Functional Developer” responsibility,



update object "Concurrent Requests".
    Search for object Concurrent Requests.
    Click on Concurrent Requests.


 

 
    Click on Object Instance Sets tab.
    Click on Create Instance Set button.

 

    Enter Name, Code and Description for new instance set.
  - Enter the following for predicate:

a)      While creating the object set, the requirement is to view all the conc requests run by all users including SYSADMIN then use the below query as the predicate:

select  cr.request_id

from apps.fnd_concurrent_requests cr, apps.fnd_concurrent_programs cp

where cr.concurrent_program_id = cp.concurrent_program_id

and cr.program_application_id = cp.application_id

and cp.concurrent_program_name like  '%'

b)      But if the requirement is to view the requests only for a particular program name then change the predicate to below with the correct program name:

select  cr.request_id

from apps.fnd_concurrent_requests cr, apps.fnd_concurrent_programs cp

where cr.concurrent_program_id = cp.concurrent_program_id

and cr.program_application_id = cp.application_id

and cp.concurrent_program_name =  'MY CONCURRENT PROGRAM NAME'

If the requirement is to  view requests for a particular responsibility then use the below

select * from apps.fnd_concurrent_requests cr where
cr.responsibility_application_id =20003
order by last_update_date desc

You can find the responsibility_id from the below query:
select * from apps.FND_RESPONSIBILITY_TL  where responsibility_name like '%Interface%'









As the SYSADMIN user with “User Management”, go to Roles and Role Inheritance tab.







   Create a Role







 Select the object that we created before:

 

Then create a Grant for the Role.
   Enter Name and Description for the new Grant.

   For the Data Security Object enter “Concurrent Requests”.

 

Click Next and provide the Data Content Type of “Instance Set”.
  Click Next and for Instance Set choose instance set created in step #1.

 

   The permission Set is “Request Operations”.
 

Review and save the grant:

Save and Apply.

Assign this role to users as needed. The users with this role will be able to see the log and
output files for the same as responsibility.


Go to   SYSADMIN--> USER Management

    Query the User Name you want to add the new role to.
    Click on the Update icon.



     Click Assign Roles button and find the new role. 






 Save and Apply to activate the new role.

 


   

  


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;

Commit complete.