Useful info for Multi-org and related tables/queries

The difference between org_id and organization_id:

Organization_id:  All organizations are stored I one single table: HR_ALL_ORGANIZATION_UNITS. All organizations stored in this table can be assigned Classification. One single organization can be assigned multiple classifications if required.

Org_id is all about the operating unit.

Which means if an organization is classified as ‘operating unit’, then Organization_id  can be used as org_id.

ORG_ID/Multi-Org/operating Unit are the terminologies that used interchangeably.

In Multi org environment, each Multi-Org table will have a column named org_id. Any table that is multi-org will end with _all. For each _all table, Oracle provides a corresponding view without _all. The query for view like:

Create or replace view XX_INVOICES as select *f rom XX_INVOICES_ALL where org_id = fnd_profile.value(‘org_id’);

If you want to extract information just for one operating unit in SQL*PLUS, execute the below first:

execute dbms_application_info.set_client_info(1525); -- where 1525 is the org_id for that operating unit.

Useful query/tables to find he business group, legal entity, operating unit and inventory organization…

All organizations are maintained in HR_ALL_ORGANIZATION_UNITS table and organization classifications like Legal entity or operating unit information are stored in HR_ORGANIZATION_INFORMATION table. There are 3 views provided based on these 2 tables to easily find out the Legal entity, Operating unit and Inventory organization.
These are:

1)      HR_LEGAL_ENTITIES

2)      HR_OPERATING_UNITS

3)      ORG_ORGANIZATION_DEFINITIONS
The related query to find the same are as:
Query to find the business groups set up in the instance :
select FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')from dual;

Query to find SOBs set up in the instance :
select FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');

Query to find legal entities associated with a SOB :
select organization_id legal_entity_id
from hr_legal_entities
where set_of_books_id = (SET OF BOOKS ID)
and business_group_id = (BUSINESS GROUP ID)

Query to find operating units for a legal entity :

select organization_id operating_unit
from hr_operating_units
where legal_entity_id= (legal_entity_id)

Query to find inventory organizations for an operating unit :
select organization_id
from org_organization_definitions
where operating_unit= (operating_unit)

Reference link:
http://getappstraining.blogspot.com/2006/10/orgid-and-multi-org-in-oracle-apps.html