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 |