Search Results int_organization_fk




Overview
Explore in AI Chat

Overview

EDW_ORGANIZATION_PKG is an Oracle E-Business Suite PL/SQL package owned by the APPS schema. Its header file (hriekorg.pkh, version 120.1, dated 2005/06/07) identifies it as part of the Oracle Enterprise Data Warehouse (EDW) integration layer that exposes HR and financial organization data for downstream reporting and warehouse extraction. The package provides foreign-key resolution functions that translate numeric primary-key identifiers, combined with an optional instance code, into the human-readable keys expected by the EDW schema. The package is classified as OTHER rather than as a public API; it is an internal EDW utility intended to be called by dependent EDW packages rather than by end-user forms or general-purpose customizations.

In the broader EBS architecture, the package mediates between the normalized organization model used in HRMS and the denormalized surrogate-key model used in the Enterprise Data Warehouse. It also encapsulates the local instance concept, allowing the same function to be reused across multiple EBS instances that contribute to a single warehouse.

Key Procedures and Functions

The package metadata documents two public functions. Both return VARCHAR2, which is consistent with their role as foreign-key string builders for the EDW dimension tables.

  • INT_ORGANIZATION_FK — Returns the EDW foreign key corresponding to an internal organization. This function is the one most commonly associated with the search term int_organization_fk. It is invoked to build the warehouse key for an internal organization record, optionally scoped by an instance code so that identically numbered organizations from different EBS instances do not collide.
  • OPERATING_UNIT_FK — Returns the EDW foreign key corresponding to an operating unit. It performs the equivalent key-resolution role for the operating unit (business unit) dimension.

Both functions are declared with the default-ID and default-instance-code parameter pattern used throughout the EDW conversion layer, and both are restricted with the PRAGMA RESTRICT_REFERENCES directive (WNDS, WNPS, RNPS), indicating that they are pure read-only functions with no side effects on package state or database state.

Tables Accessed

According to the documented metadata, the package reads through two APPS synonyms:

  • EDW_LOCAL_INSTANCE — Consulted to resolve the instance code used to disambiguate source-system identity. This is the table that supports the multi-instance keying convention used by both functions.
  • HR_ORGANIZATION_INFORMATION — Consulted to obtain the internal organization attributes required to derive the correct foreign key. This is the standard HRMS table that stores descriptive and classification information about organizations.

No insert, update, or delete operations are documented, and the PRAGMA declarations confirm that the package is strictly a read-only utility.

Usage Notes

EDW_ORGANIZATION_PKG is referenced by eighteen other packages, confirming its role as a shared dependency in the EDW extraction and transformation layer. It is typically invoked from ETL and conversion code that loads organization and operating unit dimensions, and from other EDW packages that need to resolve an organization identifier to its warehouse foreign key. Because the functions are deterministic and side-effect free, they are safe to call repeatedly within a single SQL or PL/SQL statement.

The package is not intended for direct invocation from EBS forms or from the standard concurrent manager request flow. Customizations that need organization foreign keys for EDW-style reporting should call these functions only where the corresponding EDW schema exists, since the returned key format depends on the instance code and on the warehouse's keying convention. In 12.1.1 and 12.2.2 the package header remains compatible, with no documented signature changes between those releases.