Search Results user_attribute3

Overview

The view APPS.EDW_POA_SPIM_SPLRITEM_LCV is a data extraction view within the Oracle E-Business Suite Procurement and Spend analytics layer, associated with the Procurement Operations Analysis (POA) subject area of the Enterprise Data Warehouse (EDW). The suffix _LCV denotes a "Logical Conformed View" — a canonical, denormalized projection that presents procurement dimensions in a consistent format for downstream ETL, reporting, and analytics consumption. The view exposes a unified supplier item dimension by concatenating vendor, vendor site, and vendor product number attributes into surrogate key strings.

This view is commonly surfaced when searching for the identifier supplier_site_item_dp (supplier site item descriptive pointer), which is one of its key exposed columns. In Oracle EBS 12.1.1 and 12.2.2, this view supports supplier performance dashboards, spend analysis, and item-level sourcing intelligence by providing a stable, report-friendly representation of supplier product data drawn from the purchasing module.

Underlying Base Objects

The view is defined as an inline subquery joined across five base objects, then aggregated via an outer GROUP BY. The documented referenced objects are:

• PO_HEADERS_ALL — purchase order headers, supplying header-level context and the vendor linkage.
• PO_VENDORS — the supplier master, supplying VENDOR_NAME and VENDOR_ID.
• PO_LINES_ALL — purchase order lines, supplying VENDOR_PRODUCT_NUM and line-level dates.
• PO_VENDOR_SITES_ALL — supplier sites, supplying VENDOR_SITE_CODE.
• EDW_LOCAL_INSTANCE — the EDW instance registry, supplying the INSTANCE_CODE used to identify the source EBS instance in multi-instance warehouse deployments.

The join path relates vendors to headers through VENDOR_ID, headers to lines through PO_HEADER_ID, and vendor sites to vendors through VENDOR_ID. Only lines with a non-null VENDOR_PRODUCT_NUM are retained.

Key Columns

• SUPPLIER_ITEM_PK — composite surrogate key formed as vendor_name || '-' || vendor_site_code || '-' || vendor_product_num.
• SUPPLIER_SITE_ITEM_DP — descriptive pointer at the supplier-site-item grain; the column most often referenced by searches for supplier_site_item_dp.
• SUPPLIER_ITEM_DP — descriptive pointer at the supplier-item grain (vendor plus product number, without site).
• ALL_FK — constant 'ALL' marker used as a conformed foreign key for "all" aggregate rollups.
• NAME — the vendor product number, serving as the item name.
• SUPPLIER_SITE_CODE and SUPPLIER_NAME — human-readable site and supplier identifiers.
• INSTANCE — the EBS source instance code.
• MAX(CREATION_DATE) and MAX(LAST_UPDATE_DATE) — aggregated audit timestamps; LAST_UPDATE_DATE is the greatest of site, vendor, and line update dates.
• USER_ATTRIBUTE1 through USER_ATTRIBUTE5 — placeholder flex attributes, returned as NULL in the base definition but available for extension.

Common Use Cases and Queries

This view is typically consumed to build supplier item dimensions for spend cubes and supplier scorecards, to reconcile supplier product numbers across purchase orders, and to drive cross-instance analytics in an EDW environment.

A representative query retrieves distinct supplier site items for a given supplier:

• SELECT supplier_name, supplier_site_code, name, supplier_site_item_dp FROM apps.edw_poa_spim_splritem_lcv WHERE supplier_name = :p_supplier ORDER BY supplier_site_code, name;
• SELECT instance, COUNT(DISTINCT supplier_item_pk) FROM apps.edw_poa_spim_splritem_lcv GROUP BY instance;
• SELECT supplier_site_item_dp, MAX(last_update_date) FROM apps.edw_poa_spim_splritem_lcv GROUP BY supplier_site_item_dp;

Because the view performs aggregation and string concatenation, filter and join operations should target the descriptive pointer columns rather than the underlying base keys.