User can select legal entities in AP invoice and AR transaction through “Customer Tax Payer ID” and “Legal Entity” field. The list of value of these two fields looks from the view xle_le_ou_ledger_v through operating unit, in which the relationship between ledger, OU and legal entity are described. The three entities are associated by leger configuration (ledger&LE) and organization definition (OU&ledger).
CREATE OR REPLACE VIEW APPS.XLE_LE_OU_LEDGER_V AS SELECT O2.ORGANIZATION_ID OPERATING_UNIT_ID, lg.LEDGER_ID , lg.NAME LEDGER_NAME , lg.SHORT_NAME LEDGER_SHORT_NAME , cfgDet.OBJECT_ID LEGAL_ENTITY_ID , xlep.name legal_entity_name, xlep.legal_entity_identifier, xlep.activity_code, xlep.sub_activity_code, xlep.type_of_company, xlep.effective_from le_effective_from, xlep.effective_to le_effective_to, reg.registration_number, hrl.address_line_1, hrl.address_line_2, hrl.address_line_3, hrl.region_1, hrl.region_2, hrl.region_3, hrl.town_or_city, hrl.postal_code, hrl.country FROM GL_LEDGERS primaryLg , GL_LEDGERS lg , GL_LEDGER_RELATIONSHIPS rs , GL_LEDGER_CONFIGURATIONS cfg , GL_LEDGER_CONFIG_DETAILS cfgDet, XLE_ENTITY_PROFILES xlep, XLE_REGISTRATIONS reg, HR_LOCATIONS_ALL hrl, HR_ORGANIZATION_INFORMATION O2, HR_ORGANIZATION_INFORMATION O3 WHERE rs.application_id = 101 AND ( ( rs.target_ledger_category_code = ‘SECONDARY’ AND rs.relationship_type_code <> ‘NONE’ ) OR ( rs.target_ledger_category_code = ‘PRIMARY’ AND rs.relationship_type_code = ‘NONE’ ) OR ( rs.target_ledger_category_code = ‘ALC’ AND rs.relationship_type_code IN ( ‘JOURNAL’, ‘SUBLEDGER’) ) ) AND lg.ledger_id = rs.target_ledger_id AND lg.ledger_category_code = rs.target_ledger_category_code AND primaryLg.ledger_id = rs.primary_ledger_id AND primaryLg.ledger_category_code = ‘PRIMARY’ AND cfg.configuration_id = primaryLg.configuration_id AND cfgDet.configuration_id ( + ) = cfg.configuration_id AND cfgDet.object_type_code ( + ) = ‘LEGAL_ENTITY’ AND cfgDet.object_id = xlep.legal_entity_id AND xlep.legal_entity_id = reg.source_id AND reg.source_table = ‘XLE_ENTITY_PROFILES’ AND reg.identifying_flag = ‘Y’ AND reg.location_id = hrl.location_id AND O3.ORG_INFORMATION3 = to_char(lg.LEDGER_ID) AND O3.ORGANIZATION_ID = O2.ORGANIZATION_ID AND O2.ORG_INFORMATION_CONTEXT = ‘CLASS’ AND O3.ORG_INFORMATION_CONTEXT = ‘Operating Unit Information’ AND O2.ORG_INFORMATION1 = ‘OPERATING_UNIT’ AND O2.ORG_INFORMATION2 = ‘Y’ AND xlep.transacting_entity_flag = ‘Y’;
Generally, Oracle 11i fulfills multi-org feature by the views in apps schema; while Oracle R12 fulfills the feature by Virtual Private Database feature.
In 11i, the base table are created by product schema, e.g. oe_order_headers_all in ont db schema. In apps schema, there’s a view called oe_order_headers, which has a where clause like “org_id = SUBSTRB (USERENV (‘CLIENT_INFO’), 1, 10)” . So every user accessed application has his own userenv, then every user can access his own org’s data.
In R12, the base table are also created by product schema, e.g. oe_order_haders_all in ont schema. In apps schema, there’s a Synonym oe_order_headers with VPD feature to fulfill the multi-org feature.
In 11i, we set MO: Operating Unit profile on the responsibility level, then all of user logged from this responsibility has this OU(org_id) client info, so this user can only access this operating unit data from this responsibility.
In R12, we set up a security profile, which can include several operating units, then we assign this security profile to the profile option “MO: Security Profile” on the responsibility level. The user logged from this responsibility can access all of OUs’ data in the security profile.
The following steps are required to add new operating units to your organizational structure:
1.1 Revise the Organization Structure.
1.2 Define Sets of Books (optional).
1.3 Define Locations.
1.4. Define Organizations and Relationships.
2.1 Define Responsibilities.
2.2 Set Profile Options for Each Responsibility linked to the new Operating Unit.
1.1. HR: Business Group
1.2 HR: Security Option
1.3: HR: User Type (FOR accessing HRMS functions)
1.4 HR: Cross Business Group
2.1. GL: Set of Books(11i)
2.1 GL:%Ledger% (R12)
2.3 GL: Data Access Set. This profile option to control the ledgers that can be used by Oracle General Ledger.
3.1. MO: Operating Unit
3.2. MO: Security Profile (R12)
3.3. MO: Default Operating Unit
4.1 Tax: Allow Override of Tax Code
4.2 Tax: Invoice Freight as Revenue
4.3 Tax: Inventory Item for Freight
5.1 Sequential Numbering
5.2 INV: Intercompany Currency Conversion
2.3 Run the Replicate Seed Data concurrent program.
4.1 Secure Balancing Segment Values (optional).
4.2 Run the Setup Validation Report (recommended).
4.3 Implement Document Sequencing (optional).
4.4 Set the top reporting level (optional).
4.5 Set up conflict domains (optional).
Maybe you have known that, by Oracle default setup, we can’t use the separate sequence for AP invoice voucher number by Operating Unit (OU) while we can only share one sequence for each type of AP invoice in one set of books (SOB). These days, I’m considering this flexibility and find out a way to set up the separate sequence for AP invoice voucher number by specific operating unit (OU) like below.
1. Mark the check box of “Allow Document Category Override” in payables options, invoice tab
2. Set up Document Category for the specific OU
3. Define the sequence for the OU
4. Assign the sequence (of step 3) to the category (of step 2)
5. Make the field “Document Category Name” to mandatory by Form Personalization Feature
6. To make the system not to auto default the document category, inactive the default category sequence assignment