Search Results get_locator_fk

Overview

EDW_MTL_INVENTORY_LOC_PKG is an Oracle E-Business Suite PL/SQL package owned by the APPS schema and classified as an "OTHER" API. Its header identifies it as a data-warehouse-oriented package through the EDW prefix, which is characteristic of the Oracle Enterprise Data Warehouse / Business Intelligence Foundation layer. The package is a lightweight, side-effect-free utility that resolves surrogate or foreign key values related to inventory locators and stock rooms for the material/inventory subject area used by the warehouse.

Instead of exposing the underlying tables or key derivation logic directly to warehouse ETL code, the package encapsulates the mapping rules so that the same deterministic logic is applied consistently across all callers. Both declared functions carry PRAGMA RESTRICT_REFERENCES clauses declaring WNDS, WNPS, and RNPS, meaning they write no database state, write no package state, and read no package state. This makes them safe for repeated invocation inside SQL statements and bulk loading routines. The package is referenced by five other packages, indicating it occupies a shared foundational position within the inventory dimension load path.

Key Procedures and Functions

The package declares three callable units, resolving to two distinct function names plus one overload:

• get_locator_fk — the first overload accepts an inventory location ID, an organization ID, and a subinventory code, and returns a VARCHAR2 value representing the locator foreign key. This form is the lightweight path used where warehouse control and item control flags are already implicit in the calling context. This is the function most relevant to the search term "get_locator_fk."
• get_locator_fk (overloaded) — the second overload extends the same purpose with additional context: warehouse locator control, item locator control, location code, organization code, and instance code. The extra attributes allow the locator key to be derived when the surrogate ID alone is insufficient or when values must be reconstructed from descriptive attributes such as organization code and instance code.
• get_stock_room_fk — accepts a secondary inventory name and an organization ID and returns a VARCHAR2 value representing the stock room foreign key. It resolves a stock room key from the name of a secondary inventory alongside its owning organization.

No documented function performs inserts, updates, or deletes.

Tables Accessed

Per the documented metadata, the package references two tables through APPS synonyms:

• MTL_PARAMETERS — the inventory organization parameters table. It supplies organization-level inventory control settings needed to correctly resolve locator and stock room keys, particularly the locator control flags relevant to the overloaded get_locator_fk function.
• EDW_LOCAL_INSTANCE — a data-warehouse instance or source-system mapping table, used when transforming values that include an instance code, supporting multi-instance and multi-source consolidation in the warehouse.

Usage Notes

Because the functions are restricted to read-only operations and are deterministic in behavior, they are typically invoked from PL/SQL-based ETL and dimension-loading routines feeding the inventory and locator dimensions of the warehouse. Typical consumers include concurrent programs, staging transformations, and custom warehouse load packages. The overload with the descriptive parameters is used when keys must be reconstructed from business attributes rather than supplied directly as IDs. The function is not a user-facing form API; it is intended for programmatic warehouse consumption. When extending or debugging integrations in 12.1.1 or 12.2.2, note that the three-argument overload is the concise path and the eight-argument overload the attribute-based path.