Multi-Org Access Control(MOAC) in Oracle APPS R12-- mo_global

Multi Org Access Control(MOAC):- – Multi Org Access Control(MOAC) enables us to access multiple operating units without changing responsibilities. From a single responsibility user can access multiple operating units. This is now a feature in Release 12 of the Oracle eBusiness Suite, and it changes in some fundamental ways, how the data is accessed.

How does this affect Developers and SQL queries?

Let us understand how things were under the original multi-org structure first introduced in Release 10. Forms generally accessed data through a view that would check an organization parameter.

Example: po_headers_v. This would offer a reasonably well-tuned view that looked up many of the values in related tables, without having to join a lot of lookup tables. The problem for developers was that a query on this view in a SQL session would show no rows(0 rows).

To see the data for a given organization, we had to set an Organization Context for our current session:

BEGIN
    DBMS_APPLICATION_INFO.SET_CLIENT_INFO(204);
END;

This PLSQL block would make these views show all data for organization_id = 204(operating Unit).

If you wanted multi-org access (and many of us do), you would have the option of querying the _ALL table(Multi Org tables), which would offer unrestricted access to all of the data for all organizations, without resolving the lookup values in related tables, so you would end up joining a lot of the other tables, but would end up with a quite specific, hopefully well-tuned query that showed data for all of the operating units.

In Oracle EBS R12, the security is often set at the table level using policies. This can be baffling at first, as you can see you are querying a table, you know there is data there, but you cannot see it. To see what package is controlling the access to a table, you can query the all_policies data dictionary view:

select * from all_policies where object_name = 'PO_HEADERS'

In R12 provides a new package that offers access to a single or all organizations in one call. This package, mo_global is called below for a single organization:

BEGIN
  mo_global.set_policy_context('S',204);
END;

The 1st parameter, ‘S’, means Single Organization(Single Operating Unit), the 2nd parameter is organization_id. Queries on this table will yield a view of all rows for organization_id=204. If you want access to all organization data, you can change the first parameter to ‘M’ (for multiple Operating Units), and no second parameter:

BEGIN
   mo_global.set_policy_context('M');
END;