Results for “po_item_sources”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PO_ITEM_SOURCES is a Purchasing (PO) module dictionary view owned by the APPS schema and reported as VALID in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented description is concise — "Sources for an item" — but the view's role is more specific than that label implies. It exposes the set of approved, currently active sourcing documents from which a given item may be procured, resolving purchasing documents of type QUOTATION and BLANKET into a single, consumable result set.

The view is constructed from the three core Purchasing document tables and applies a WHERE predicate that restricts output to headers that are either quotations in Approved status (TYPE_LOOKUP_CODE = 'QUOTATION' AND STATUS_LOOKUP_CODE = 'A') or blanket purchase agreements that have been approved (TYPE_LOOKUP_CODE = 'BLANKET' AND APPROVED_FLAG = 'Y'). Cached documents are therefore excluded. For reporting, integration, and sourcing logic, PO_ITEM_SOURCES functions as a pre-joined, pre-filtered projection of header, line, and line-location data, removing the need for callers to reconstruct the join and the approval filter themselves.

The column QUOTE_TYPE_LOOKUP_CODE, which prompted this search, is surfaced through the expression NVL(QUOTE_TYPE_LOOKUP_CODE, 'BLANKET'). Because blanket agreements carry no quotation classification, the view substitutes the literal value 'BLANKET' so that every row bears a usable source-type indicator. This NVL is significant: it means consumers can group or filter on a single column to distinguish quotations (for example 'CATALOG' or 'MANUAL' quote types) from blanket agreements without writing conditional logic against TYPE_LOOKUP_CODE.

Underlying Base Objects

The views documented base objects are PO_HEADERS_ALL (exposed in the view text as alias H), PO_LINES_ALL (alias L), and PO_LINE_LOCATIONS_ALL (alias LL), with PO_HEADERS_ALL, PO_LINES_ALL, and PO_LINE_LOCATIONS presented as synonyms in the 12.2.2 metadata. The view text joins these on the standard primary/foreign key chain: PO_LINE_LOCATIONS_ALL.PO_HEADER_ID to PO_HEADERS_ALL.PO_HEADER_ID and PO_LINE_LOCATIONS_ALL.PO_LINE_ID to PO_LINES_ALL.PO_LINE_ID.

This join places the line location — the shipment or schedule level — at the grain of the view, so a single item sourced by one blanket agreement with multiple releases or shipment schedules returns multiple rows. Header-level attributes are inherited through the PO_HEADERS_ALL join, while item, category, and vendor-product attributes derive from PO_LINES_ALL.

Key Columns

• QUOTE_TYPE_LOOKUP_CODE — the quotation classification; defaulted to 'BLANKET' for blanket agreements via NVL. Primary discriminator returned to callers.
• TYPE_LOOKUP_CODE — the header document type ('QUOTATION' or 'BLANKET'); the underlying filter column.
• SEGMENT1, LINE_NUM — the human-readable document number and line number.
• VENDOR_ID, VENDOR_SITE_ID, VENDOR_CONTACT_ID — supplier identification for the source.
• ITEM_ID, ITEM_REVISION, CATEGORY_ID, VENDOR_PRODUCT_NUM — the item being sourced.
• START_DATE, END_DATE — effective dates, coalesced from line location to header via NVL.
• PRICE_OVERRIDE, QUANTITY, UNIT_MEAS_LOOKUP_CODE, LEAD_TIME, LEAD_TIME_UNIT — sourcing terms.
• ORG_ID — operating unit, the multi-org partitioning key.
• CURRENCY_CODE, RATE, RATE_TYPE, RATE_DATE — currency context.

Common Use Cases and Queries

Typical uses include populating sourcing lists, validating that an item has an approved source before requisition entry, and extracting supplier price agreements for downstream systems.

To list all approved blanket sources for an item:

• SELECT segment1, line_num, vendor_id, quote_type_lookup_code, price_override, start_date, end_date FROM po_item_sources WHERE item_id = :item_id AND quote_type_lookup_code = 'BLANKET' AND NVL(end_date, SYSDATE+1) > SYSDATE;
• SELECT quote_type_lookup_code, COUNT(*) FROM po_item_sources WHERE org_id = :org_id GROUP BY quote_type_lookup_code;

Callers must always constrain by ORG_ID in a multi-org environment and should apply their own date-validity test, since the view does not itself filter on the current date.