Search Results per_people_f_pk

Overview

POS_PO_SUPPLIER_ITEMS_V is a Purchasing (PO) module reporting view owned by the APPS schema. It presents the relationship between inventory items, their approved suppliers, and the internal buyer responsible for each item within a given inventory organization. The view consolidates sourcing information from MRP_ITEM_SOURCING_LEVELS_V together with master item definitions, vendor records, buyer identities, and approved supplier list (ASL) entries. By joining these sources, the view exposes a single row per active item–supplier–organization combination, subject to the effective and disable date window applied through the sourcing levels. It is primarily used for reporting and integration scenarios where the current, effective sourcing picture for an item must be retrieved without navigating multiple base entities. The DISTINCT operator, combined with the ORDERED and USE_NL hints and an explicit index hint on PER_PEOPLE_F_PK, indicates the view was tuned for nested-loop access paths against the buyer lookup, reflecting the designer's expectation that buyer joins would be filtered by primary key. This is directly relevant to the user lookups referencing per_people_f_pk.

Underlying Base Objects

The documented referenced base objects include FND_GLOBAL (package), HR_ALL_ORGANIZATION_UNITS_TL, MTL_PARAMETERS, MTL_SYSTEM_ITEMS_VL, PER_ALL_PEOPLE_F, PO_APPROVED_SUPPLIER_LIST, PO_ASL_ATTRIBUTES, PO_ASL_STATUS_RULES, PO_VENDORS, and PO_VENDOR_SITES_ALL. The view text joins MRP_ITEM_SOURCING_LEVELS_V (alias MISL) as the driving source of supplier allocation data, MTL_SYSTEM_ITEMS_KFV (MSI) for the concatenated item segments and description, PO_VENDORS (POV) for vendor names and the ATTRIBUTE14 supplier URL, PER_ALL_PEOPLE_F (HRV) for the buyer full name, HR_ALL_ORGANIZATION_UNITS_TL (HOU) for the organization name, and PO_APPROVED_SUPPLIER_LIST (ASL) to confirm the vendor is approved for the item. The buyer join is an outer join (HRV.PERSON_ID(+) = MSI.BUYER_ID), so items without an assigned buyer still appear. HOU is restricted to the session language via USERENV('LANG').

Key Columns

• ITEM_NUM — concatenated item segments from MTL_SYSTEM_ITEMS_KFV.
• ITEM_DESCRIPTION, UNIT_OF_MEASURE — item description and primary UOM.
• ITEM_ID, INVENTORY_ORGANIZATION_ID — inventory item and organization identifiers.
• BUYER_ID, BUYER_NAME — buyer identifier and full name from PER_ALL_PEOPLE_F.
• VENDOR_ID, VENDOR_NAME — supplier identifier and name from PO_VENDORS.
• EFFECTIVE_DATE, EXPIRATION_DATE — validity window of the sourcing allocation.
• RANK, SPLIT — sourcing rank and allocation percentage for the supplier.
• SUPPLIER_URL, ITEM_URL — URLs sourced from vendor and item ATTRIBUTE14 values.
• ORG_NAME, VENDOR_ITEM — organization name and primary vendor item flag from the ASL.

Common Use Cases and Queries

Typical uses include approved-supplier reporting, buyer workload analysis, and integration extracts feeding procurement or supplier-portal systems. A common query retrieves active suppliers for an item in a specific organization:

• SELECT ITEM_NUM, ORG_NAME, VENDOR_NAME, BUYER_NAME, RANK, SPLIT FROM POS_PO_SUPPLIER_ITEMS_V WHERE ITEM_ID = :p_item_id AND INVENTORY_ORGANIZATION_ID = :p_org_id ORDER BY RANK;
• SELECT BUYER_NAME, COUNT(DISTINCT ITEM_ID) FROM POS_PO_SUPPLIER_ITEMS_V GROUP BY BUYER_NAME;
• SELECT ITEM_NUM, VENDOR_NAME, EFFECTIVE_DATE FROM POS_PO_SUPPLIER_ITEMS_V WHERE VENDOR_ID = :p_vendor_id;

Because the view already filters by SYSDATE against the sourcing validity window, callers should not expect historical or future-dated allocations. The presence of the PER_PEOPLE_F_PK index hint confirms that buyer-driven filters and joins benefit from the primary key index on the people table.