OracleFree referencePDF, Excel, Word exportOracle E-Business SuiteAll Oracle E-Business Suite tools

Oracle EBS Multi-Org Structure Tables and MO_GLOBAL Reference (R12)

This reference maps the R12 multi-org structure from ledger down to inventory organization, plus the MOAC profiles, temporary table and MO_GLOBAL APIs that decide which rows a session can see. It applies to Oracle E-Business Suite 12.1 and 12.2.

Download:

Object	Level	What it holds	Key columns	How it joins	Notes
GL_LEDGERS	Ledger	One row per ledger (primary, secondary, reporting currency) and per ledger set	LEDGER_ID, NAME, SHORT_NAME, CHART_OF_ACCOUNTS_ID, CURRENCY_CODE, PERIOD_SET_NAME, ACCOUNTED_PERIOD_TYPE, LEDGER_CATEGORY_CODE, OBJECT_TYPE_CODE	LEDGER_ID = HR_OPERATING_UNITS.SET_OF_BOOKS_ID; CHART_OF_ACCOUNTS_ID = FND_ID_FLEX_STRUCTURES.ID_FLEX_NUM (GL#)	OBJECT_TYPE_CODE separates ledgers (L) from ledger sets (S). LEDGER_CATEGORY_CODE is PRIMARY, SECONDARY or ALC.
GL_SETS_OF_BOOKS	Ledger	Backward compatible view of ledgers using 11i column names	SET_OF_BOOKS_ID, NAME, CHART_OF_ACCOUNTS_ID, CURRENCY_CODE, PERIOD_SET_NAME	SET_OF_BOOKS_ID = GL_LEDGERS.LEDGER_ID	Old custom code still selects from it. New code should use GL_LEDGERS.
XLE_ENTITY_PROFILES	Legal entity	Legal entities created in Legal Entity Configurator	LEGAL_ENTITY_ID, PARTY_ID, NAME, LEGAL_ENTITY_IDENTIFIER, TRANSACTING_ENTITY_FLAG	LEGAL_ENTITY_ID = HR_OPERATING_UNITS.DEFAULT_LEGAL_CONTEXT_ID; PARTY_ID = HZ_PARTIES.PARTY_ID	Legal entities are TCA parties. Address and registration details sit in related XLE and HZ tables.
XLE_REGISTRATIONS	Legal entity	Registration numbers (tax, company) for legal entities and establishments	REGISTRATION_ID, SOURCE_ID, SOURCE_TABLE, REGISTRATION_NUMBER, IDENTIFYING_FLAG	SOURCE_TABLE = 'XLE_ENTITY_PROFILES' and SOURCE_ID = LEGAL_ENTITY_ID	Filter IDENTIFYING_FLAG = 'Y' for the registration that identifies the entity.
XLE_ETB_PROFILES	Legal entity	Establishments (sites of a legal entity, used by tax)	ESTABLISHMENT_ID, LEGAL_ENTITY_ID, PARTY_ID, NAME, MAIN_ESTABLISHMENT_FLAG	LEGAL_ENTITY_ID = XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID	Registrations for establishments use SOURCE_TABLE = 'XLE_ETB_PROFILES'.
HR_ALL_ORGANIZATION_UNITS	Operating unit	Every HR organization: business groups, operating units, inventory orgs, departments	ORGANIZATION_ID, BUSINESS_GROUP_ID, NAME, TYPE, LOCATION_ID, DATE_FROM, DATE_TO	ORGANIZATION_ID = HR_ORGANIZATION_INFORMATION.ORGANIZATION_ID	An org becomes an operating unit or inventory org only through its classification rows.
HR_ORGANIZATION_INFORMATION	Operating unit	Classifications and extra information per organization	ORGANIZATION_ID, ORG_INFORMATION_CONTEXT, ORG_INFORMATION1 to ORG_INFORMATION20	ORGANIZATION_ID = HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID	Context CLASS with ORG_INFORMATION1 = OPERATING_UNIT or INV marks the classification. 'Operating Unit Information' holds the ledger and default legal entity; 'Accounting Information' on an inventory org holds ledger, legal entity and operating unit.
HR_OPERATING_UNITS	Operating unit	View of organizations classified as operating units	ORGANIZATION_ID, NAME, BUSINESS_GROUP_ID, SET_OF_BOOKS_ID, DEFAULT_LEGAL_CONTEXT_ID, SHORT_CODE, DATE_FROM, DATE_TO	ORGANIZATION_ID = <table>_ALL.ORG_ID; SET_OF_BOOKS_ID = GL_LEDGERS.LEDGER_ID	The easiest way to turn an ORG_ID into an operating unit name and its ledger.
ORG_ORGANIZATION_DEFINITIONS	Inventory org	View of inventory organizations with their ledger and operating unit	ORGANIZATION_ID, ORGANIZATION_CODE, ORGANIZATION_NAME, SET_OF_BOOKS_ID, CHART_OF_ACCOUNTS_ID, OPERATING_UNIT, LEGAL_ENTITY, INVENTORY_ENABLED_FLAG	OPERATING_UNIT = HR_OPERATING_UNITS.ORGANIZATION_ID; ORGANIZATION_ID = MTL_PARAMETERS.ORGANIZATION_ID	Built over HR tables, so it can be slow in large joins. For bulk work join MTL_PARAMETERS and HR_ORGANIZATION_INFORMATION directly.
MTL_PARAMETERS	Inventory org	Inventory organization parameters	ORGANIZATION_ID, ORGANIZATION_CODE, MASTER_ORGANIZATION_ID, COST_ORGANIZATION_ID, PRIMARY_COST_METHOD, WMS_ENABLED_FLAG, PROCESS_ENABLED_FLAG	ORGANIZATION_ID = MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID	Inventory org data is striped by ORGANIZATION_ID, not ORG_ID, and is not under MOAC VPD.
PER_SECURITY_PROFILES	Security	HR security profiles, also used as MOAC security profiles	SECURITY_PROFILE_ID, SECURITY_PROFILE_NAME, BUSINESS_GROUP_ID, VIEW_ALL_FLAG	SECURITY_PROFILE_ID = value of profile MO: Security Profile	Defined in HRMS (Security Profile or Global Security Profile form). Run Security List Maintenance after changes.
PER_ORGANIZATION_LIST	Security	Organizations a security profile can access	SECURITY_PROFILE_ID, ORGANIZATION_ID	SECURITY_PROFILE_ID = PER_SECURITY_PROFILES.SECURITY_PROFILE_ID	Populated by the Security List Maintenance program. Stale lists explain missing operating units.
MO_GLOB_ORG_ACCESS_TMP	Security	Session-level global temporary table of operating units the session can access	ORGANIZATION_ID, ORGANIZATION_NAME	ORGANIZATION_ID = <table>_ALL.ORG_ID through the VPD predicate	Filled by MO_GLOBAL.INIT or SET_POLICY_CONTEXT('M', NULL). Empty table in Multiple mode means no rows from secured synonyms.
MO: Security Profile	Profile	Security profile that lists the operating units a responsibility can access	Profile option XLA_MO_SECURITY_PROFILE_LEVEL	Value = PER_SECURITY_PROFILES.SECURITY_PROFILE_ID	When set, it overrides MO: Operating Unit for MOAC-enabled modules.
MO: Default Operating Unit	Profile	Operating unit that defaults on MOAC forms	Profile option DEFAULT_ORG_ID	Must be one of the units in MO: Security Profile	Only a default. Users can still pick another unit they can access.
MO: Operating Unit	Profile	Single operating unit for responsibilities without a security profile	Profile option ORG_ID	Value = HR_OPERATING_UNITS.ORGANIZATION_ID	Used when MO: Security Profile is null, and by modules that are not MOAC enabled.
HR: Security Profile	Profile	HR data security profile for the responsibility	Profile option PER_SECURITY_PROFILE_ID	Value = PER_SECURITY_PROFILES.SECURITY_PROFILE_ID	Controls HR data access. Do not confuse it with MO: Security Profile.
FND_GLOBAL.APPS_INITIALIZE	API	Sets user, responsibility and application for the session	user_id, resp_id, resp_appl_id, security_group_id (default 0)	IDs from FND_USER and FND_RESPONSIBILITY	Call this first. MO_GLOBAL.INIT reads the profile values of this user and responsibility.
MO_GLOBAL.INIT	API	Initializes MOAC for an application short name	p_appl_short_name, e.g. 'SQLAP', 'AR', 'PO', 'ONT'	Reads MO: Security Profile or MO: Operating Unit and fills MO_GLOB_ORG_ACCESS_TMP	Sets Single mode when only one unit is accessible, otherwise Multiple mode.
MO_GLOBAL.SET_POLICY_CONTEXT	API	Sets access mode and current org for the session	p_access_mode ('S' or 'M'), p_org_id	'S' with an ORG_ID limits secured synonyms to that unit; 'M' with NULL uses MO_GLOB_ORG_ACCESS_TMP	'S' mode alone is enough for most ad hoc queries in SQL Developer or TOAD.
MO_GLOBAL.GET_CURRENT_ORG_ID	API	Returns the current org in Single mode	Function, no arguments	Compare with MO_GLOBAL.GET_ACCESS_MODE	Returns NULL in Multiple mode or before the context is set.
FND_CONCURRENT_PROGRAMS.MULTI_ORG_CATEGORY	API	Operating Unit Mode of a concurrent program (Define Concurrent Program form)	MULTI_ORG_CATEGORY: S = Single, M = Multiple	CONCURRENT_PROGRAM_ID, APPLICATION_ID	Single mode programs get an Operating Unit prompt at submission and run with SET_POLICY_CONTEXT('S', org).
OE_ORDER_HEADERS_ALL	Security	Base table striped by ORG_ID	HEADER_ID, ORG_ID, ORDER_NUMBER	ORG_ID = HR_OPERATING_UNITS.ORGANIZATION_ID	Querying the _ALL object returns every operating unit, no context needed. Always filter ORG_ID yourself.
OE_ORDER_HEADERS	Security	APPS synonym over OE_ORDER_HEADERS_ALL with a VPD policy (MO_GLOBAL.ORG_SECURITY)	Same columns as the _ALL table	Predicate uses SYS_CONTEXT('multi_org2', 'current_org_id') in Single mode or MO_GLOB_ORG_ACCESS_TMP in Multiple mode	Returns no rows when the MOAC context is not set. Same pattern for PO_HEADERS, AP_INVOICES, RA_CUSTOMER_TRX and most R12 transaction objects.

Applies to EBS 12.0, 12.1 and 12.2. Profile internal names are the ones shown in System Administrator > Profile > System; check FND_PROFILE_OPTIONS on your instance if a value looks different.

Map your EBS org structure

Send us your ledgers, operating units and inventory orgs and we will return a checked hierarchy diagram with the MOAC profile settings per responsibility.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

The hierarchy in R12

A ledger (GL_LEDGERS) owns a chart of accounts, calendar and currency. Legal entities (XLE_ENTITY_PROFILES) are tied to the ledger through Accounting Setup Manager. An operating unit is an HR organization (HR_ALL_ORGANIZATION_UNITS) with the Operating Unit classification; its 'Operating Unit Information' row in HR_ORGANIZATION_INFORMATION points to the primary ledger and the default legal context. An inventory organization is an HR organization with the Inventory Organization classification; its 'Accounting Information' row points to the ledger, legal entity and operating unit, and its parameters live in MTL_PARAMETERS. Transaction tables are striped by ORG_ID (operating unit) or ORGANIZATION_ID (inventory org). The views HR_OPERATING_UNITS and ORG_ORGANIZATION_DEFINITIONS flatten this so you rarely need to decode ORG_INFORMATION columns by hand.

Ledger: GL_LEDGERS.LEDGER_ID
Legal entity: XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID
Operating unit: HR_OPERATING_UNITS.ORGANIZATION_ID = ORG_ID
Inventory org: MTL_PARAMETERS.ORGANIZATION_ID

Why your query returns no rows in SQL Developer

Most R12 transaction objects in the APPS schema, such as OE_ORDER_HEADERS, PO_HEADERS or AP_INVOICES, are synonyms over the _ALL tables with a Virtual Private Database policy. The policy function MO_GLOBAL.ORG_SECURITY adds a predicate: in Single mode it compares ORG_ID to the current org in the multi_org2 context, and in Multiple mode it checks ORG_ID against MO_GLOB_ORG_ACCESS_TMP. A plain SQL Developer session has neither, so the synonym returns nothing while the _ALL table returns everything. Either query the _ALL table with an explicit ORG_ID filter, or set the context first. Report and integration code should normally use _ALL tables with explicit filters, because it makes the result independent of who runs it.

Worked script: set the MOAC context

For a quick look at one operating unit, one call is enough. For code that must behave exactly as a responsibility does, initialize the user and responsibility first, then MOAC.

Single unit: BEGIN MO_GLOBAL.SET_POLICY_CONTEXT('S', 204); END;
As a responsibility: BEGIN FND_GLOBAL.APPS_INITIALIZE(user_id => 1318, resp_id => 21623, resp_appl_id => 660); MO_GLOBAL.INIT('ONT'); END;
All accessible units after INIT: MO_GLOBAL.SET_POLICY_CONTEXT('M', NULL)
Check: SELECT MO_GLOBAL.GET_ACCESS_MODE, MO_GLOBAL.GET_CURRENT_ORG_ID FROM dual;
List units: SELECT organization_id, organization_name FROM mo_glob_org_access_tmp;

12.1 versus 12.2

MOAC behaves the same in 12.1 and 12.2: the same profiles, the same MO_GLOBAL calls and the same VPD policies. The difference in 12.2 is online patching. Each product table keeps its name in the product schema, an editioning view with a # suffix is created over it (for example ONT.OE_ORDER_HEADERS_ALL#), and the APPS synonym OE_ORDER_HEADERS_ALL points at that editioning view. Always query through the APPS synonyms, never the base tables or # views directly, and create custom objects with the 12.2 development standards (AD_ZD_TABLE.UPGRADE for custom tables). The 11i habit of calling DBMS_APPLICATION_INFO.SET_CLIENT_INFO with an org id does not drive R12 MOAC security.

Frequently Asked Questions

Which table holds operating units in Oracle EBS R12?

Operating units are rows in HR_ALL_ORGANIZATION_UNITS with the Operating Unit classification in HR_ORGANIZATION_INFORMATION. Use the HR_OPERATING_UNITS view to get ORGANIZATION_ID, NAME, SET_OF_BOOKS_ID (the ledger id) and DEFAULT_LEGAL_CONTEXT_ID in one place. The ORGANIZATION_ID is the ORG_ID stored on transaction tables.

What is ORG_ORGANIZATION_DEFINITIONS?

It is a view of inventory organizations that returns the organization code and name, the ledger (SET_OF_BOOKS_ID), chart of accounts, operating unit and legal entity. It is convenient for lookups but built over several HR tables, so for large report joins use MTL_PARAMETERS and HR_ORGANIZATION_INFORMATION directly.

Where is the legal entity table in Oracle EBS?

Legal entities are in XLE_ENTITY_PROFILES (LEGAL_ENTITY_ID, NAME, PARTY_ID). Registration numbers are in XLE_REGISTRATIONS with SOURCE_TABLE = 'XLE_ENTITY_PROFILES', and establishments in XLE_ETB_PROFILES. The default legal context of an operating unit is HR_OPERATING_UNITS.DEFAULT_LEGAL_CONTEXT_ID.

What is the difference between MO: Operating Unit and MO: Security Profile?

MO: Operating Unit gives a responsibility one operating unit. MO: Security Profile points to a security profile that can list many units, and when it is set it takes precedence for MOAC-enabled modules. MO: Default Operating Unit only picks the default value shown on forms.

If you want planners and finance users to ask EBS questions in plain English across operating units, with MOAC security respected on your own servers, we can set that up.

Talk to an Expert Explore More Free Tools