All About MO_GLOBAL and FND_GLOBAL

11i -> dbms_application_info.set_client_info(&org_id);
R12 -> mo_global.set_policy_context('S',&org_id);
Ref: from http://www.orafaq.com/node/2243

How To Retrieve Rows From Table Or Synonym For An ORG_ID In E-Business Suite 12 [ID 787677.1]
Ref:from http://oracle.anilpassi.com/mo-global-dive-into-r12-multi-org-design.html

How is CLIENT_INFO being replaced in R12?
Lets take an example.
In pre Release 12, you would have had following methodology for PO_HEADERS_ALL
a. A table is created in PO Schema, named PO_HEADERS_ALL
b. A synonym named PO_HEADERS_ALL is created in APPS schema, referring to PO.PO_HEADERS_ALL
c. Create a view PO_HEADERS in APPS schema, as "select * from po_headers_all where org_id=client_info"

But now in R12, following will happen
a. A table is created in PO Schema, named PO_HEADERS_ALL
b. A synonym named PO_HEADERS_ALL is created in APPS schema, referring to PO.PO_HEADERS_ALL
c. Another synonym named PO_HEADERS is created in APPS, referring to PO_HEADERS_ALL

d. A Row Level security is applied to PO_HEADERS, using package function MO_GLOBAL.ORG_SECURITY.
This can be double-checked by running SQL

select * from all_policies where object_name='PO_HEADERS'
 
e. The effect of this policy is that,whenever you access PO_HEADERS, Oracle RLS will dynamically append WHERE CLAUSE similar to below

SELECT * FROM PO_HEADERS
WHERE EXISTS (SELECT 1 FROM mo_glob_org_access_tmp oa WHERE oa.organization_id = org_id)

Explanation:



What is MO_GLOBAL.INIT
Purpose of mo_global.init :-
It will check if new Multi Org Security Profile is set, to decide if new Security Profile method will be used.
If the new MO security profile is set, then mo_global.init inserts one record, for each Organization in Org Hierarchy, in table mo_glob_org_access_tmp

What is the purpose of MO_GLOBAL.ORG_SECURITY?
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

What is the purpose of MO_GLOBAL.SET_POLICY_CONTEXT ?
This procedure has two parameters
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
p_org_id
Only applicable if p_access_mode is passed value of "S"



FND_PROFILE and FND_GLOBAL values
[For more information, please visit http://docs.oracle.com/cd/E18727_01/doc.121/e12897/T302934T462356.htm]

Following are the FND_PROFILE values that can be used in the PL/SQL code:
   fnd_profile.value('PROFILEOPTION');
   fnd_profile.value('MFG_ORGANIZATION_ID');
   fnd_profile.value('ORG_ID');
   fnd_profile.value('LOGIN_ID');
   fnd_profile.value('USER_ID');
   fnd_profile.value('USERNAME');
   fnd_profile.value('CONCURRENT_REQUEST_ID');
   fnd_profile.value('GL_SET_OF_BKS_ID');
   fnd_profile.value('SO_ORGANIZATION_ID');
   fnd_profile.value('APPL_SHRT_NAME');
   fnd_profile.value('RESP_NAME');
   fnd_profile.value('RESP_ID');

Following are the FND_GLOBAL values that can be used in the PL/SQL code:
   FND_GLOBAL.USER_ID;
   FND_GLOBAL.APPS_INTIALIZE;
   FND_GLOBAL.LOGIN_ID;
   FND_GLOBAL.CONC_LOGIN_ID;
   FND_GLOBAL.PROG_APPL_ID;
   FND_GLOBAL.CONC_PROGRAM_ID;
   FND_GLOBAL.CONC_REQUEST_ID;

For example, I almost always use the following global variable assignments in my package specification to use throughout the entire package body:
   g_user_id      PLS_INTEGER  :=  fnd_global.user_id;
   g_login_id     PLS_INTEGER  :=  fnd_global.login_id;
   g_conc_req_id  PLS_INTEGER  :=  fnd_global.conc_request_id;
   g_org_id       PLS_INTEGER  :=  fnd_profile.value('ORG_ID');
   g_sob_id       PLS_INTEGER  :=  fnd_profile.value('GL_SET_OF_BKS_ID');

And initialize the application environment as follows:
   v_resp_appl_id  := fnd_global.resp_appl_id;
   v_resp_id       := fnd_global.resp_id;
   v_user_id       := fnd_global.user_id;
    
FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id, v_resp_appl_id);

Insert Lookup Values API

1. Create a table to store data that will be inserted into Lookup.

--CREATE TABLE XX_TEMP_TABLE
--  (
--    X_LOOKUP_CODE         VARCHAR2(30 BYTE),
--    X_LOOKUP_MEANING      VARCHAR2(80 BYTE),
--    X_LOOKUP_DESCRIPTION  VARCHAR2(240 BYTE),
--    X_LOOKUP_TAG          VARCHAR2(30 BYTE),
--    X_LOOKUP_START_DATE   DATE,
--    X_LOOKUP_END_DATE     DATE,
--    X_LOOKUP_ENABLED_FLAG VARCHAR2(1 BYTE),
--    X_LOOKUP_CONTEXT      VARCHAR2(30 BYTE),
--    X_LOOKUP_ATTRIBUTE1   VARCHAR2(150 BYTE),
--    X_LOOKUP_ATTRIBUTE2   VARCHAR2(150 BYTE),
--    X_LOOKUP_ATTRIBUTE3   VARCHAR2(150 BYTE),
--    X_LOOKUP_ATTRIBUTE4   VARCHAR2(150 BYTE),
--    X_LOOKUP_ATTRIBUTE5   VARCHAR2(150 BYTE),
--    X_LOOKUP_ATTRIBUTE6   VARCHAR2(150 BYTE),
--    X_LOOKUP_ATTRIBUTE7   VARCHAR2(150 BYTE),
--    X_LOOKUP_ATTRIBUTE8   VARCHAR2(150 BYTE),
--    X_LOOKUP_ATTRIBUTE9   VARCHAR2(150 BYTE),
--    X_LOOKUP_ATTRIBUTE10  VARCHAR2(150 BYTE)
--  );

2. Script to Insert Values

set serveroutput on;
-- API to Create Lookup Values - "FND_LOOKUP_VALUES"


DECLARE
   CURSOR get_lookup_details
   IS
      SELECT   ltype.application_id,
               ltype.customization_level,
               ltype.creation_date,
               ltype.created_by,
               ltype.last_update_date,
               ltype.last_updated_by,
               ltype.last_update_login,
               tl.lookup_type,
               tl.security_group_id,
               tl.view_application_id,
               tl.description,
               tl.meaning
        FROM   fnd_lookup_types_tl tl, fnd_lookup_types ltype
       WHERE       ltype.lookup_type = 'XX_USER_CUSTOMER_MAPPING'
               AND ltype.lookup_type = tl.lookup_type
               AND language = 'US';

   CURSOR get_VALUES
   IS
      SELECT   * FROM XX_TEMP_TABLE where x_lookup_code not in ( select lookup_code
        FROM   fnd_lookup_values_vl
       WHERE       lookup_type = 'XX_USER_CUSTOMER_MAPPING'
)
      ;--'CO12505A';

   l_rowid   VARCHAR2 (100) := 0;

   BEGIN
   FOR i IN get_lookup_details
   LOOP
      FOR j IN get_VALUES
      LOOP
         l_rowid := NULL;
       
         BEGIN
            fnd_lookup_values_pkg.insert_row (
               x_rowid                 => l_rowid,
               x_lookup_type           => i.lookup_type,
               x_security_group_id     => i.security_group_id,
               x_view_application_id   => i.view_application_id,
               x_lookup_code           => j.X_LOOKUP_CODE,
               x_tag                   => j.X_LOOKUP_TAG,
               x_attribute_category    => j.X_LOOKUP_CONTEXT,
               x_attribute1            => j.X_LOOKUP_ATTRIBUTE1,
               x_attribute2            => j.X_LOOKUP_ATTRIBUTE2,
               x_attribute3            => j.X_LOOKUP_ATTRIBUTE3,
               x_attribute4            => j.X_LOOKUP_ATTRIBUTE4,
               x_enabled_flag          => 'Y',
               x_start_date_active     => j.X_LOOKUP_START_DATE,
               x_end_date_active       => j.X_LOOKUP_END_DATE,
               x_territory_code        => NULL,
               x_attribute5            => j.X_LOOKUP_ATTRIBUTE5,
               x_attribute6            => j.X_LOOKUP_ATTRIBUTE6,
               x_attribute7            => j.X_LOOKUP_ATTRIBUTE7,
               x_attribute8            => j.X_LOOKUP_ATTRIBUTE8,
               x_attribute9            => j.X_LOOKUP_ATTRIBUTE9,
               x_attribute10           => j.X_LOOKUP_ATTRIBUTE10,
               x_attribute11           => NULL,
               x_attribute12           => NULL,
               x_attribute13           => NULL,
               x_attribute14           => NULL,
               x_attribute15           => NULL,
               x_meaning               => j.X_LOOKUP_MEANING,
               x_description           => j.X_LOOKUP_DESCRIPTION,
               x_creation_date         => SYSDATE,
               x_created_by            => 1230, --i.created_by,
               x_last_update_date      => SYSDATE, --i.last_update_date,
               x_last_updated_by       => 1230,--i.last_updated_by,
               x_last_update_login     => i.last_update_login
            );
            COMMIT;
            DBMS_OUTPUT.put_line (j.X_LOOKUP_CODE || ' loaded');
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line ('Inner Exception: ' || SQLERRM);
         END;
      END LOOP;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;