Mo_Global.Set_policy_Context

Posted on by

 MO_GLOBAL.SET_POLICY_CONTEXT   /   MO_GLOBAL.ORG_SECURITY

In this article i will try to give the answer of follwoing questions.
 
1) What is the purpose  MO_GLOBAL.ORG_SECURITY in Oracle EBS R12
2) What is the purpose of   MO_GLOBAL.SET_POLICY_CONTEXT in Oracle EBS R12
3) Following queries return null / No Rows.
 
         select * from po_headers_v
          select * from po_lines_v
          select * from po_headers_print
          select * from po_lines_print
          select * from po_requisition_headers
          select * from po_requisition_lines 
 
4)  How to Assign RLS (Row level Security) to Custom table.
5) In SQL-Plus how can I work against specific organization. How is it possible in R12
6) How  can I simulate login to a specific responsibility in SQL*Plus.
 
 
 
 
 
 Question
 1) What is the purpose  MO_GLOBAL.ORG_SECURITY
  
Answer:
The purpose of Row-Level-Security is to hide certain data[based on some conditions]. RLS does so by appending a where clause to the secured object.
1. MO_GLOBAL.ORG_SECURITY is a function that returns a predicate for the WHERE CLAUSE
2. The where clause will be appended to Table/Synonym/View for which Multi Org Row Level security is enabled
 
 
Questions
2) What is the purpose of   MO_GLOBAL.SET_POLICY_CONTEXT
3) Following queries return null / No Rows.
          select * from po_headers_v
          select * from po_lines_v
          select * from po_headers_print
          select * from po_lines_print
          select * from po_requisition_headers
          select * from po_requisition_lines 
5) In SQL-Plus how can I work against specific organization. How is it possible in R12
 
Answer:
MO_GLOBAL.SET_POLICY_CONTEXT has two parameters
   1. p_access_mode
         Pass a value “S” in case you want your current session to work against 
         Single ORG_ID
          Pass a value of “M” in case you want your current session to work 
          against multiple ORG_ID’s
   2. p_org_id
   Only applicable if p_access_mode is passed value of “S”
SQL>> exec MO_GLOBAL.SET_POLICY_CONTEXT(’S’,82);
In the above case, ORG_ID 101 will be assigned as current org for your session.
Internally, following code in blue will be executed by Oracle when you set your context to single Org, dbms_session.set_context(’multi_org2’, ’current_org_id’, 101);

If the current database session is initialised for Single Org[as in above step], then Where clause appended to object by Row-Level-Security will be
WHERE org_id = sys_context(’multi_org2’,’current_org_id’)

 
Question
6) How  can I simulate login to a specific responsibility in SQL*Plus
Answer
a. Call FND_GLOBAL.INITIALIZE
This will set your responsibility id, user_id etc
b. call MO_GLOBAL.INIT
This will read the MO profile option values for your responsibility/user, and will initialize the Multi Org Access.
Question
6) How  can I simulate login to a specific responsibility in SQL*Plus.
Answer
You have to add ORG_ID column in your table and assign package function MO_GLOBAL.ORG_SECURITY to the object (table/view/synonym).