Oracle iProcurement : Realms

In Oracle iProcurement, Realms can be used to control access to punchout catalogs(/stores) to different responsibilities or users. This helps in limiting the access to catalogs for users from different departments. So for e.g. if your company policy limits the purchasing of computer hardware to the Information Technology (IT) department. To adhere to this policy and restrict certain users from ordering these types of items the Realms feature can be used.

To use this the following steps need to be followed.

    1. Create the realm. (Log in to Oracle Purchasing and use the following navigation path to open the Realms window: Setup > E-Catalog Admin > Realms)
    2. Assign the realm to a responsibility. (Login to System Administrator responsibility. Open the Responsibilities window using the following navigation path:Security > Responsibility > Define. Open the         Securing Attributes tab and do the necessary setup).

For detailed steps refer Chapter number 2 - Buyer Setup in the Oracle iProcurement user guide (http://download.oracle.com/docs/cd/B12190_11/current/acrobat/icx115punchout.pdf) provided by Oracle.

Oracle Database: Utility Queries

--*****************************************************************************************************
--***************************** To find value of parameters set in init.ora****************************
--*****************************************************************************************************
select name,value from v$parameter where name = 'utl_file_dir'
--*****************************************************************************************************
--***************************** To find object information ****************************************
--*****************************************************************************************************
-- for all objects user dba_<xxxx> views
select * from dba_objects -- gives details of all types of objects
select * from dba_procedures
select * from dba_tables
select * from dba_indexes
-- for objects owned by user use user_<xxxx> views
select * from user_objects -- gives details of all types of objects
select * from user_procedures
select * from user_tables
select * from user_indexes
--*****************************************************************************************************
--***************************** To find current sessions running on a database*************************
--*****************************************************************************************************
select distinct
sid
,s.serial#
,s.module
,lockwait
,s.schemaname
,s.username
,osuser
,machine
,s.terminal
,s.program
,status
,q.sql_text
from v$session s
,v$process p
,v$sqlarea q
where p.addr(+)=s.paddr
and q.hash_value(+)=s.sql_hash_value
and q.address(+)=s.sql_address
and status = 'ACTIVE'
order by sid desc
--*****************************************************************************************************
--***************************** To kill a session on a database *************************************
--*****************************************************************************************************

--ALTER SYSTEM KILL SESSION 'SID, SER#';
alter system kill session '5,390'; -- SID and SERIAL# received from above query.

--*****************************************************************************************************
--***************************** To get the current execution of cursor *******************************
--*****************************************************************************************************
SELECT Executions,
loads,
first_load_time ,
elapsed_time
FROM V$sqlarea
WHERE hash_value in (SELECT sql_hash_value
FROM V$session
WHERE sid = 48 );
--*****************************************************************************************************
--**************** To check which processes have locked a resource (generally a Table)*****************
--*****************************************************************************************************

SELECT c.sid,
c.serial#,
c.username,
a.object_id,
b.object_name,
c.program,
c.status,
d.name,
c.osuser
FROM v$Locked_object a,
all_objects b,
v$session c,
audit_actions d
WHERE a.object_id=b.object_id
AND a.session_id =c.sid(+)
AND c.command=d.action;
--*****************************************************************************************************
--***************************** To check ACTIVE session in any instnace *******************************
--*****************************************************************************************************
SELECT s.osuser, s.sid, s.sql_hash_value, s.module, w.event
FROM v$session s, v$session_wait w
WHERE s.sid = w.sid
AND s.status = 'ACTIVE'
AND event not like 'rdbms ipc message'
AND event not like 'pipe get'
AND event not like 'queue messages'
ORDER BY s.module

--*****************************************************************************************************
--***************************** To check current activity done by the REQUEST_ID **********************
--*****************************************************************************************************
SELECT fcr.request_id
,st.sql_text
,fcr.status_code
,s.sid
,s.serial#
,s.*
FROM apps.fnd_concurrent_requests fcr
,v$session s
,v$sqltext st
WHERE fcr.os_process_id = s.process (+)
AND s.sql_hasH_VALUE = st.hash_value (+)
AND fcr.request_id = 18740879
ORDER BY fcr.request_id, st.piece
--*****************************************************************************************************
--****************************************** To check deadlock ****************************************
--*****************************************************************************************************
select /*+ ordered */ w1.sid waiting_session
,h1.sid holding_session
,w.kgllktype lock_or_pin
,w.kgllkhdl address
,decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive','Unknown') mode_held
,decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive','Unknown') mode_requested
from dba_kgllock w
, dba_kgllock h
, v$session w1
, v$session h1
where (((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and (((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr ;

Oracle Apps : Set Organization (Operating Unit) while working in a database session

-- ####################################
-- Notes
-- ####################################
-- All the queries below set the Organisation (read: Operating Unit) information for the session
--
-- Query 01 -- does the same by setting the user and responsibility for the session.
-- The calling code then derives the Organisation (Operating Unit, value in profile "MO: Operating Unit"
-- for that user/responsibility.

-- Query 02 -- does the same by directly setting the client info to the required 'Operating Unit'

-- Query 03 -- also does the same by directly setting the client info to the required 'Operating Unit'

-- Fyi - Operating units are defined in the HR_ALL_ORGANIZATION_UNITS table.

-- ####################################
-- Query 01
-- ####################################
-- The following call to apps_intialize function does not pass
-- the parameter "security_group_id", hence it is defaulted to
-- to zero (that is the setup Business Group, Business Group is the topmost level in the
-- Organization Definition Hierarchy. So generally the ORG_ID for the same is zero)

declare
    l_user_id      number;
    l_resp_id      number;
    l_resp_appl_id number;
begin
    -- get conversion user
    select fu.user_id
    into   l_user_id
    from   fnd_user fu
    where  fu.user_name = :p_user_name;
    -- get conversion responsibility
    select fr.responsibility_id,
           fr.application_id
    into   l_resp_id,
           l_resp_appl_id
    from   fnd_responsibility fr
    where  fr.responsibility_key = :p_responsibility_key;
    fnd_global.apps_initialize(
      user_id       => l_user_id,
      resp_id       => l_resp_id,
      resp_appl_id  => l_resp_appl_id);
end;
/

-- ####################################
-- Query 02
-- ####################################
-- To set the organisation for the session the following code can be used.

begin
  fnd_client_info.set_org_context('255');
end;
/


-- ####################################
-- Query 03
-- ####################################
-- To set the organisation for the session the following code can also be used.

exec dbms_application_info.set_client_info ('255');

Oracle Apps: HRMS - Set Effective Date for a Session through database

-- Following query can be used to set Effective Date for a session . This is particularly required while you are working with Oracle HRMS related tables.

INSERT INTO fnd_sessions
  (session_id
  ,effective_date)
  (SELECT userenv('sessionid')
         ,SYSDATE
   FROM dual
   WHERE NOT EXISTS (SELECT 'c'
          FROM fnd_sessions s1
           WHERE userenv('sessionid') = s1.session_id));