Setting Org Context in Oracle Apps 11i and R12

Oracle Apps tables ending with _ALL holds transaction data for multiple org ( Operating Units).

In 11i we had views on these tables. It requires setting context in order to fetch data from these views. This  is for security concerns as these objects holds transaction details.

The SQL command to set the ORG_ID prior to running a script is:

SQL> EXECUTE DBMS_APPLICATION_INFO.SET_CLIENT_INFO(&ORG_ID);

Enter the org_id when prompted.

If using Toad:

BEGIN
     FND_CLIENT_INFO.SET_ORG_CONTEXT (&ORG_ID);
END;   

In R12 oracle uses VPD (Virtual Private Database) to secure these transactional data .This is one of major difference in application architecture between 11i and R12.

In order to retrieve data from transactional objects, set policy context first ( as below ) –

BEGIN
     MO_GLOBAL.SET_POLICY_CONTEXT(‘MODE’ CHAR(1),ORG_ID NUMBER);
END;

    MODE – This is  either “S” – For Single Operating Unit OR “M” – For Multiple Operating Unit

    Org_ID – Operating unit (Value from column ORG_ID in all transactional objects)
                         This is mandatory for “S” mode.
Example :-

BEGIN
    MO_GLOBAL.SET_POLICY_CONTEXT(‘S’, 123);
END;