Search Results all_fk




Overview
Explore in AI Chat

Overview

EDW_POA_SPIM_SPLRITEM_LTC_IV is a reporting view owned by the APPS schema in Oracle E-Business Suite. The suffix convention _IV denotes an interface view, while the prefix EDW_POA_SPIM identifies the object as part of the Enterprise Data Warehouse (EDW) extract layer associated with Procurement Market Intelligence/Spend Performance (POA) sourcing and supplier item master (SPIM) subject areas. The view exposes supplier item data held in the EDW_POA_SPIM_SPLRITEM_LTC table, presenting both the surrogate key columns used by the warehouse and the descriptive business attributes of supplier items. Its role is to provide a stable, denormalized projection of supplier item information suitable for downstream reporting, data extraction, and integration with external analytics platforms.

Underlying Base Objects

The documented metadata lists no referenced base objects, but the embedded view text makes the source explicit. The view is defined as a single-table SELECT over EDW_POA_SPIM_SPLRITEM_LTC, a staging or load-to-column (LTC) table that holds supplier item records for the EDW pipeline. The view performs no joins and no filters; it projects the columns of the underlying table and renames the trailing ALL_FK column to OPERATION_CODE. Because the definition is a direct projection, all filtering, key resolution, and referential integrity are inherited from the base table. The ROWID pseudo-column is included and aliased as ROW_ID, giving each row a unique physical address within the base table.

Key Columns

  • ROW_ID — the ROWID of the source row in the base table.
  • ALL_FK_KEY and SUPPLIER_ITEM_PK_KEY — surrogate key columns used to link this extract to other warehouse entities.
  • ALL_FK — the all-purpose foreign key column in the base table; this same column is re-exposed as OPERATION_CODE, indicating the change operation (insert, update, delete) applied to the record during extraction.
  • INSTANCE — identifies the source EBS instance for multi-instance consolidation.
  • NAME — the supplier item name or description.
  • SUPPLIER_ITEM_DP, SUPPLIER_ITEM_PK, SUPPLIER_NAME, SUPPLIER_SITE_CODE, and SUPPLIER_SITE_ITEM_DP — supplier item identifiers and their associated supplier and supplier site context.
  • USER_ATTRIBUTE1 through USER_ATTRIBUTE5 — the standard EBS descriptive flexfield segments, carried through for flexible reporting.
  • CREATION_DATE and LAST_UPDATE_DATE — standard audit columns for record creation and last modification.

Common Use Cases and Queries

This view is typically consumed by ETL routines and BI tools that need supplier item data alongside its change operation. Because the trailing ALL_FK value is renamed to OPERATION_CODE, queries can filter for incremental changes. For example, to retrieve newly inserted or updated supplier items for a given instance:

  • SELECT ROW_ID, SUPPLIER_ITEM_PK, SUPPLIER_NAME, NAME, OPERATION_CODE FROM APPS.EDW_POA_SPIM_SPLRITEM_LTC_IV WHERE INSTANCE = :instance AND OPERATION_CODE IN ('I','U');
  • SELECT SUPPLIER_NAME, SUPPLIER_SITE_CODE, COUNT(*) FROM APPS.EDW_POA_SPIM_SPLRITEM_LTC_IV GROUP BY SUPPLIER_NAME, SUPPLIER_SITE_CODE;
  • SELECT SUPPLIER_ITEM_PK_KEY, USER_ATTRIBUTE1, CREATION_DATE, LAST_UPDATE_DATE FROM APPS.EDW_POA_SPIM_SPLRITEM_LTC_IV WHERE LAST_UPDATE_DATE >= :last_run_date;

Typical scenarios include incremental supplier item extraction for the EDW, reconciliation of supplier site items against source EBS records, and reporting on user-defined flexfield attributes. Since the view performs no filtering, consumers are responsible for applying instance, date, and operation predicates to constrain result sets.