Search Results pos_po_details_v

Overview

The APPS.POS_PO_DETAILS_V view is a denormalized reporting and integration object belonging to the POS (iSupplier Portal) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to fetch all information related to a purchase order, consolidating header, line, shipment, vendor, tax, and location attributes into a single flat result set. Because iSupplier Portal exposes purchasing data to external suppliers through the supplier-facing UI, this view serves as the primary data conduit that joins purchasing transaction tables to supplier-addressable attributes such as vendor site, contact, tax code, and ship-to location names. Technical users leverage it as a stable interface for building reports, extracts, and custom inquiries without having to replicate the complex join logic between PO headers, lines, line locations, releases, vendors, and tax codes. It is defined with a DISTINCT clause, so consumers should expect deduplication across the projected column set rather than one row per base transaction. The view owner is APPS and its status is VALID.

Underlying Base Objects

The ETRM metadata documents that POS_PO_DETAILS_V is built on a combination of purchasing tables, vendor views, and reference objects. The core transactional sources are PO_HEADERS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL, and PO_RELEASES_ALL, which supply header, line, shipment, and blanket-release context respectively. Vendor information is drawn from PO_VENDORS, PO_VENDOR_SITES_ALL, and PO_VENDOR_CONTACTS, providing supplier and contact detail. Item description and concatenated item information originate from MTL_SYSTEM_ITEMS_KFV, while ship-to addresses are resolved through HR_LOCATIONS_ALL. Tax attributes come from AP_TAX_CODES, and operating unit defaults are taken from FINANCIALS_SYSTEM_PARAMS_ALL. The view also references the PO_INQ_SV and FND_GLOBAL packages, which supply inquiry logic and session context (such as org_id and user_id). The supplier-priced line location table (PO_LINE_LOCATIONS_ALL, aliased PLL) is the driving table in the view text, joined to PO_LINES_ALL (POL) to resolve unit price and item relationships.

Key Columns

The view exposes shipment-level purchasing attributes. PO_LINE_ID, PO_HEADER_ID, LINE_LOCATION_ID, PO_RELEASE_ID, and SHIPMENT_NUM identify the transactional grain of each row. QUANTITY is derived with a DECODE that suppresses quantity for PRICE BREAK shipment types, while QUANTITY_RECEIVED, QUANTITY_ACCEPTED, QUANTITY_BILLED, QUANTITY_CANCELLED, and QUANTITY_REJECTED support receipt and invoice reconciliation. PRICE_OVERRIDE is conditionally resolved: for PRICE BREAK shipments the line unit price (POL.UNIT_PRICE) is substituted, otherwise the shipment price override is used. This is significant because queries searching on price_discount typically need to test PRICE_OVERRIDE together with the line and shipment grain to interpret negotiated pricing. Dates include CREATION_DATE, NEED_BY_DATE, LAST_ACCEPT_DATE, PROMISED_DATE (which falls back to NEED_BY_DATE via NVL), and CLOSED_DATE. Status and control flags include CLOSED_CODE (defaulted to OPEN), CLOSED_FLAG, CANCEL_FLAG (with cancelled-inventory rows nulled), APPROVED_FLAG, INSPECTION_REQUIRED_FLAG, RECEIPT_REQUIRED_FLAG, TAXABLE_FLAG, and MATCHING_TYPE, the latter computed from receipt and inspection flags into 2-WAY, 3-WAY, or 4-WAY. SHIP_TO_LOCATION_ID, SHIP_TO_ORGANIZATION_ID, and the vendor tax code name round out the supplier-facing attributes.

Common Use Cases and Queries

Typical scenarios include supplier-facing PO extracts, open shipment aging, receipt-versus-billed reconciliation, and pricing analysis where users investigate posted unit prices and overrides. A representative query for open shipments with pricing context is:

• SELECT po_header_id, po_line_id, shipment_num, quantity, price_override, promised_date, closed_code FROM apps.pos_po_details_v WHERE closed_code = 'OPEN' AND approved_flag = 'Y';
• SELECT po_header_id, SUM(quantity * price_override) extended_value FROM apps.pos_po_details_v WHERE cancel_flag IS NULL GROUP BY po_header_id;
• SELECT v.po_header_id, v.price_override, v.taxable_flag, v.matching_type FROM apps.pos_po_details_v v WHERE v.po_line_id = :line_id;
• SELECT po_header_id, promised_date, need_by_date FROM apps.pos_po_details_v WHERE ship_to_organization_id = :org_id AND closed_code = 'OPEN' ORDER BY promised_date;

Because the view applies DISTINCT, aggregation queries should group by the intended business key (for example PO_HEADER_ID plus PO_LINE_ID plus SHIPMENT_NUM) to avoid unexpected summation. For price_discount analysis, join or compare PRICE_OVERRIDE against PO_LINES_ALL.UNIT_PRICE, noting that PRICE BREAK shipments already substitute the line unit price. All references require the APPS schema prefix and should be executed under the appropriate operating unit context.