Search Results orga_organization_pk_key

Overview

EDW_ORGANIZATION_M is a denormalized reporting table owned by the HRI (Human Resources Intelligence) schema in Oracle EBS 12.1.1 and 12.2.2. It is part of the Oracle HRMS Intelligence / E-Business Intelligence warehouse layer, which pre-joins and flattens operational HRMS organization data into wide, query-optimized structures intended for analytical reporting rather than transactional processing. With 208 documented columns, the table aggregates four related organization entities in a single row: organizations (ORGA_), operating units (OPER_), legal entities (LGET_), and business groups (BGRP_).

The table also materializes an eight-level organizational hierarchy, represented by column families T1L1_ through T1L8_ (ORG_TREE1, levels 1 to 8). The HRI schema feeds downstream Oracle HRMS Intelligence dashboards, Discoverer workbooks, and custom operational reporting extracts. The heuristic Data Vault classification mined from the FK structure is standalone; as a modeling suggestion, this object behaves less like a pure hub and more like a consolidated snapshot or denormalized reporting satellite, since it carries both surrogate keys and descriptive attributes for multiple organization types within a single physical row. It is not part of an enforced multi-table Data Vault diagram — no inbound foreign keys are documented.

Key Information Stored

The documented primary key is EDW_ORGANIZATION_M_PK, defined on ORGA_ORGANIZATION_PK_KEY. Two unique indexes supply business-key candidates: EDW_ORGANIZATION_M_U1 covers (ORGA_ORGANIZATION_PK, ORGA_ORGANIZATION_PK_KEY), and EDW_ORGANIZATION_M_U2 covers (ORGA_ORGANIZATION_PK_KEY). The surrogate key ORGA_ORGANIZATION_PK is therefore functionally dependent on the business key ORGA_ORGANIZATION_PK_KEY.

• ORGA_ORGANIZATION_PK and ORGA_ORGANIZATION_PK_KEY — surrogate and business keys for the organization entity; the U1/U2 unique indexes build on these.
• ORGA_ORGANIZATION_ID, ORGA_NAME, ORGA_ORG_CODE, ORGA_ORG_TYPE — the operational organization identity and classification.
• ORGA_ORG_INT_EXT_FLAG and ORGA_ORG_PRIM_CST_MTHD — internal/external designation and primary cost method.
• ORGA_DATE_FROM / ORGA_DATE_TO and ORGA_INSTANCE — effective-dating and multi-instance (multi-Org) support.
• OPER_OPERATING_UNIT_PK_KEY, OPER_OPERATING_UNIT_ID, OPER_NAME, OPER_ORG_CODE — the associated operating unit.
• LGET_LEGAL_ENTITY_PK_KEY, LGET_LEGAL_ENTITY_ID, LGET_NAME, LGET_SET_OF_BOOKS — the associated legal entity and its set of books.
• BGRP_BUSINESS_GROUP_PK_KEY, BGRP_BUSINESS_GROUP_ID, BGRP_NAME, BGRP_LEGISLATION, BGRP_COST_ALLOCATION — the parent business group.
• T1L1_ORG_TREE1_LVL1_PK_KEY through T1L8_ORG_TREE1_LVL8_PK_KEY, with matching NAME and ORGANIZATION_ID columns — the flattened eight-level hierarchy.
• ORGA_PERSON_MANAGER_ID and ORGA_PERSON_MANAGER_FK_KEY — the manager of the organization.
• CREATION_DATE / LAST_UPDATE_DATE — audit stamps common to HRI warehouse objects.

Each of the four entity families repeats USER_ATTRIBUTE1 through USER_ATTRIBUTE5 and DP (descriptive flexfield) columns, providing extensibility without schema change.

Common Use Cases and Queries

Typical usage is analytical: hierarchy roll-ups, operating-unit-to-legal-entity reconciliation, and business group reporting. Because the table is a wide snapshot, most queries filter on the business key or organization identifier and project a small subset of columns.

• Listing organizations under a named business group: SELECT ORGA_NAME, OPER_NAME, LGET_NAME FROM HRI.EDW_ORGANIZATION_M WHERE BGRP_NAME = :p_bg;
• Flattening a node to its full tree path, using the T1L1_ to T1L8_ NAME columns to display ancestry without recursive traversal.
• Effective-date validation using ORGA_DATE_FROM and ORGA_DATE_TO alongside ORGA_INSTANCE for multi-Org environments.
• Managerial reporting via joins on ORGA_PERSON_MANAGER_ID to person-level HRI tables to report organizations by manager.

Because no inbound foreign keys are documented and the classification is standalone, the table should be treated as a query target rather than a referential integrity anchor. Ensure statistics are current when filtering on the 208-column row, as full-table scans are common.

Related Objects

The documented key structure links EDW_ORGANIZATION_M to the following significant objects:

• HR_ORGANIZATION_UNITS — the operational source for ORGA_ORGANIZATION_ID and ORGA_NAME.
• HR_OPERATING_UNITS — source of the OPER_ family, joined on OPER_OPERATING_UNIT_ID.
• HR_LEGAL_ENTITIES — source of the LGET_ family, joined on LGET_LEGAL_ENTITY_ID; LGET_SET_OF_BOOKS ties to GL_SETS_OF_BOOKS.
• HR_ALL_ORGANIZATION_UNITS — the master organization view supplying ORGA and BGRP attributes.
• PER_ALL_PEOPLE_F — resolved through ORGA_PERSON_MANAGER_ID / ORGA_PERSON_MANAGER_FK_KEY for manager detail.
• FND_ID_FLEX_STRUCTURES — supplies context for the DP descriptive flexfield columns.
• Other HRI EDW objects (for example, employee and assignment warehouse tables) — consumed jointly for HRMS Intelligence reporting, though the column families here are self-contained.

No ETRM-documented inbound foreign keys reference this table, confirming its role as a terminal reporting object in the HRI layer rather than a transactional parent.