Search Results supplier_item_sc_fk




Overview
Explore in AI Chat

Overview

EDW_SUPPLIER_ITEM_PKG is an Oracle E-Business Suite PL/SQL package owned by the APPS schema. It belongs to the supplier item API layer that supports the Supplier Item dimension within the Oracle Enterprise Data Warehouse (EDW) and related Business Intelligence extracts. Its principal business function is to resolve the surrogate foreign key for a supplier item dimension member from a small set of descriptive business attributes. Callers supply human-readable identifiers — the supplier name, a supplier site identifier or site code, and the supplier product number — and the package returns the corresponding supplier item level foreign key as a VARCHAR2 value.

This package follows the dimensional modelling convention used across the EDW foreign key packages, where a thin PL/SQL wrapper hides the underlying star-schema view from the calling application. The header comment identifies the underlining view as EDW_POA_SPIM_SPLRITEM_FKV, which is the object actually queried to derive the key. The package is therefore a lookup utility rather than a data maintenance API: it does not create, update, or delete supplier item records.

Key Procedures and Functions

  • SUPPLIER_ITEM_FK — Returns the Supplier Item level foreign key. This overload accepts the supplier site identifier as its site parameter, making it suitable for callers that already hold the internal site ID from a prior lookup or from a purchasing document.
  • SUPPLIER_ITEM_SC_FK — Returns the same Supplier Item level foreign key, but accepts the supplier site code instead of the site ID. This variant is intended for callers working from externally meaningful site codes, such as those captured in flat-file feeds or user-entered reference data.

Both functions are declared with PRAGMA RESTRICT_REFERENCES using WNDS, WNPS, and RNPS. This asserts that the functions write no database state, write no package state, and read no package state, which allows them to be safely invoked from SQL statements and from other restricted contexts. The package specification contains no global variables, cursors, or exceptions; it is a pure functional interface.

Tables Accessed

The ETRM metadata records no direct table references for this package. All data access is performed through the underlying view EDW_POA_SPIM_SPLRITEM_FKV, named in the package header. Because the package is compiled in the APPS schema, that view — and any base tables it resolves to, such as the supplier item staging or dimension entities in the EDW schema — are reached through APPS synonyms rather than through direct schema-qualified references. The package performs read-only access, consistent with the RESTRICT_REFERENCES pragmas.

Usage Notes

EDW_SUPPLIER_ITEM_PKG is a utility package intended for programmatic and SQL-based consumption rather than for direct end-user invocation. Typical callers include ETL and extract programs that populate or validate the Supplier Item dimension, custom reports and concurrent programs that must join transactional data to supplier item dimension keys, and other PL/SQL packages. The metadata notes that the package is referenced by four other packages, confirming its role as a shared dependency within the EDW foreign key layer.

Because the functions are restricted-reference pure functions, they may be embedded directly in SELECT statements. When integrating, the site-ID variant should be preferred where the numeric site identifier is already available, while the site-code variant suits interfaces driven by external reference data. In Oracle EBS 12.1.1 and 12.2.2, the APPS synonym and privilege model applies unchanged; the package requires no additional setup beyond the standard EDW installation.