Search Results orga_date_to

Overview

HRI.EDW_ORGANIZATION_M is a denormalized star-schema dimension table belonging to the Oracle E-Business Suite Operational Reporting / Enterprise Data Warehouse (EDW) layer. It is registered under FND Design Data as BIS.EDW_ORGANIZATION_M and is owned by the HRI schema, with storage in the APPS_TS_SUMMARY tablespace at PCTFREE 30. The table serves as the Internal Organization dimension, representing organizations and organization hierarchies that describe the structures existing within an enterprise. Critically, it contains records for the lowest level of the dimension and every level above it, enabling hierarchical roll-up without recursive traversal of the base transaction tables.

The dimension allows reporting to access Primary organization hierarchies, Operational hierarchies, and the relationships between Business Groups, legal entities, operating units, and organizations. Within the Oracle EBS 12.1.1 and 12.2.2 environments, this object is typically consumed by ETRM/BIS (Business Intelligence System) reports and any downstream extract that requires a flattened organization view. Based on the mined foreign key structure, the heuristic Data Vault classification is standalone — the table is modeled as an independent dimension with no enforced parent links, though a vault-oriented refactoring would treat the ORGA, OPER, LGET and BGRP column groups as separate hub-style references resolved through the flattened grain.

Key Information Stored

With 208 documented columns, the table is intentionally wide. The most significant columns for query and join purposes are:

• ORGA_ORGANIZATION_PK_KEY (NUMBER) — the surrogate primary key of the table (EDW_ORGANIZATION_M_PK). This is the unique warehouse identifier for the organization level and is the preferred join key.
• ORGA_ORGANIZATION_PK (VARCHAR2, 120) — the textual unique identifier for the organization level; together with ORGA_ORGANIZATION_PK_KEY it forms unique index EDW_ORGANIZATION_M_U1. EDW_ORGANIZATION_M_U2 enforces uniqueness on ORGA_ORGANIZATION_PK_KEY alone, confirming it as the single-column business-key candidate.
• ORGA_ORG_CODE (VARCHAR2, 20) — the hidden organization type code from the data source. This is the column most frequently searched for under the alias oper_org_code when users attempt to correlate an operating-unit code; note that the operating-unit variant is stored separately as OPER_ORG_CODE.
• OPER_ORG_CODE and OPER_OPERATING_UNIT_PK_KEY — the operating-unit code and its warehouse identifier, used when the dimension grain is an operating unit rather than a generic organization.
• LGET_ORG_CODE, LGET_LEGAL_ENTITY_PK_KEY, LGET_SET_OF_BOOKS — legal-entity level attributes.
• BGRP_ORG_CODE, BGRP_BUSINESS_GROUP_PK_KEY, BGRP_LEGISLATION, BGRP_COST_ALLOCATION — business-group level attributes.
• ORGA_NAME (VARCHAR2, 500) and OPER_NAME — the descriptive organization/operating-unit names used in report labels.
• ORGA_DATE_FROM / ORGA_DATE_TO — the effective-dating pair that drives as-of reporting.
• ORGA_ORG_TYPE, ORGA_ORG_INT_EXT_FLAG, ORGA_ORG_PRIM_CST_MTHD — classification, internal/external flag, and primary cost method.
• T1L1_* through T1L8_* column groups — eight pre-flattened Organization Tree 1 levels, each carrying PK key, name, business group, organization ID and primary-org descriptor.
• ORGA_INSTANCE — identity of the data source, used to disambiguate multiple source instances.

Common Use Cases and Queries

Typical reporting scenarios include organization hierarchy roll-ups, operating-unit level financial extracts, and cross-validation of legal-entity to business-group mappings. A representative filtered query resolving the searched term is:

• SELECT ORGA_ORGANIZATION_PK_KEY, ORGA_NAME, ORGA_ORG_CODE, OPER_ORG_CODE FROM HRI.EDW_ORGANIZATION_M WHERE OPER_ORG_CODE = :p_oper_org_code;
• Hierarchy traversal at a fixed depth, e.g. selecting T1L1_NAME, T1L2_NAME, T1L3_NAME with T1L1_ORG_TREE1_LVL1_PK_KEY as the top-level filter.
• Effective-dated lookups using ORGA_DATE_FROM <= :as_of AND (ORGA_DATE_TO IS NULL OR ORGA_DATE_TO >= :as_of).
• Joining facts to this dimension on ORGA_ORGANIZATION_PK_KEY rather than on the code columns, since the surrogate key is the indexed, non-null join path.

Because ORGA_ORG_CODE is a VARCHAR2(20) hidden code from the data source, queries that pass an operating-unit code against it return no rows when the intended value resides in OPER_ORG_CODE; report builders should confirm which level of the dimension the filter targets.

Related Objects

The table is classified as standalone with no documented outbound foreign keys, so dependencies are primarily inbound — fact tables and other EDW dimensions that join on its surrogate or business key.

• HRI.EDW_ORGANIZATION_M unique indexes EDW_ORGANIZATION_M_U1 and EDW_ORGANIZATION_M_U2 — enforce the join key uniqueness relied upon by consuming facts.
• Organization-level fact tables and materialized views in the BIS/EDW layer that reference ORGA_ORGANIZATION_PK_KEY as their organization dimension foreign key.
• Operating-unit fact extracts joined via OPER_OPERATING_UNIT_PK_KEY.
• Legal-entity reporting objects joined via LGET_LEGAL_ENTITY_PK_KEY.
• Business-group aggregation objects joined via BGRP_BUSINESS_GROUP_PK_KEY.
• Base EBS organization tables in the HR and INV schemas (HR_ALL_ORGANIZATION_UNITS and its organization classifications) from which this dimension is derived by the EDW refresh.
• Person/manager reference resolved through ORGA_PERSON_MANAGER_ID and ORGA_PERSON_MANAGER_FK_KEY, which link organization rows to the manager dimension.