Search Results edw_organization_m




Overview
Explore in AI Chat

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.

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.