Search Results edw_organization_pkg




Overview
Explore in AI Chat

Overview

EDW_ORGANIZATION_PKG is an Oracle E-Business Suite PL/SQL package body owned by the APPS schema that supports the Enterprise Data Warehouse (EDW) organization dimension extraction and integration layer. Its primary business function is to resolve organization-related foreign key identifiers for the EDW reporting and staging environment, providing a consistent mechanism for mapping Human Resources organization structures to the identifiers consumed by downstream warehouse objects.

The package is classified as API classification OTHER, indicating it is not part of a published, formally supported public API set, but rather an internal utility used by the EDW extraction infrastructure. The package body is documented as VALID in the ETRM metadata for release 12.1.1, and the underlying object model is consistent across 12.1.1 and 12.2.2, where the EDW schemas and HR organization model remain structurally compatible.

Key Procedures and Functions

The ETRM metadata documents two procedures or functions within this package:

  • INT_ORGANIZATION_FK — Resolves the internal organization foreign key value associated with an HR organization record. This routine is used to obtain the surrogate key that the EDW organization dimension uses to reference an internal organization entry, enabling consistent joins between warehouse fact and dimension tables and the underlying HR organization data.
  • OPERATING_UNIT_FK — Resolves the operating unit foreign key value for an organization. This supports determinations of which operating unit an organization belongs to, which is essential for multi-org aware reporting and for partitioning EDW data by operating unit context.

Parameter lists are not documented in the available metadata and are therefore not reproduced here. Both routines operate as key-resolution helpers rather than as transactional business logic.

Tables Accessed

The package references the following tables through APPS synonyms:

  • HR_ORGANIZATION_INFORMATION — The core HR table storing descriptive and classificatory attributes of organizations, including organization classifications and other flexfield-style information. The package reads this table to determine organization identity and classification attributes required for foreign key resolution.
  • EDW_LOCAL_INSTANCE — An EDW infrastructure table that identifies the local warehouse instance. It is referenced to bind organization keys to the correct warehouse instance context, ensuring that resolved foreign keys are valid for the local EDW deployment.

The documented dependencies also include the STANDARD SYS package, which provides standard PL/SQL runtime facilities.

Usage Notes

EDW_ORGANIZATION_PKG is referenced by 18 other packages within the EBS data warehouse layer, confirming its role as a shared utility. It is not referenced by any database object outside that dependency set according to the metadata. Typical invocation occurs during EDW extraction, staging, and dimension-loading programs, where organization foreign keys must be resolved consistently before fact data is materialized. The package is generally not invoked directly from Oracle Forms; instead it is called from concurrent programs and custom PL/SQL routines that populate or refresh EDW organization dimension structures. Because it is classified as OTHER rather than a public API, customizations that depend on it should be treated as unsupported and validated against each patch level, particularly when moving between 12.1.1 and 12.2.2, where EDW schema objects may be recompiled or replaced.