Search Results po_lines_xml




Overview
Explore in AI Chat

Overview

PO_LINES_XML is a reporting and integration view owned by the APPS schema in Oracle E-Business Suite Purchasing (PO). It exposes purchase order line information in a flattened, denormalized form intended primarily for XML document generation, supplier communication, and outbound purchasing interfaces. Rather than requiring callers to reconstruct purchasing line semantics from the transactional base tables, the view presents line attributes already translated into externally meaningful values — formatted currency amounts, human-readable unit of measure names, decoded agreement context, and formatted date strings.

The view is marked VALID in both Oracle EBS 12.1.1 and 12.2.2 and carries no direct DML capability, as it is a view and not a table. Its column set closely mirrors the data elements required for purchase order XML payloads, which is consistent with its naming convention: the "_XML" suffix is used throughout EBS for views consumed by XML Publisher, e-commerce gateways, and the PO Communication stack. Because much of the formatting logic is delegated to the PO_COMMUNICATION_PVT package, the view inherits the same locale- and system-parameter-driven behavior that governs outbound purchasing documents.

Underlying Base Objects

The ETRM metadata documents the following referenced objects for PO_LINES_XML: FINANCIALS_SYSTEM_PARAMS_ALL, MTL_SYSTEM_ITEMS_B, MTL_SYSTEM_ITEMS_TL, MTL_UNITS_OF_MEASURE_TL, PO_COMMUNICATION_GT, PO_COMMUNICATION_PVT, PO_CORE_S, PO_HAZARD_CLASSES_TL, PO_LINES_ALL, PO_LINE_TYPES_B, and PO_UN_NUMBERS_TL. Most of these are accessed in APPS through synonyms.

PO_LINES_ALL is the primary transactional source and supplies the core line columns. MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL provide item master context and translated item descriptions. MTL_UNITS_OF_MEASURE_TL resolves the unit of measure into a language-specific name, falling back to the stored lookup code. PO_HAZARD_CLASSES_TL and PO_UN_NUMBERS_TL supply hazardous material classification and United Nations number data for regulated goods. PO_LINE_TYPES_B contributes the order type lookup code. FINANCIALS_SYSTEM_PARAMS_ALL and PO_CORE_S typically provide currency and formatting context. PO_COMMUNICATION_GT, together with the PO_COMMUNICATION_PVT package, underpins the XML communication process that consumes this view.

Key Columns

Identifiers and keys: PO_HEADER_ID, PO_LINE_ID, LINE_NUM, ITEM_ID, FROM_HEADER_ID, and FROM_LINE_ID establish the line's position within a document and its relationship to a source agreement or quotation. The TO_CHAR conversions applied to UNIT_PRICE, QUANTITY_COMMITTED, and MIN_RELEASE_AMOUNT use PO_COMMUNICATION_PVT.GETFORMATMASK, ensuring monetary values follow the formatting profile configured for purchasing communication. CANCEL_FLAG is coalesced to 'N' so that a null flag never propagates as an unhandled value. CANCEL_DATE, PROGRAM_UPDATE_DATE, and CLOSED_DATE are rendered as 'DD-MON-YYYY HH24:MI:SS' strings.

Descriptive and classification columns include ITEM_DESCRIPTION, ITEM_REVISION, VENDOR_PRODUCT_NUM, NOTE_TO_VENDOR, UNIT_MEAS_LOOKUP_CODE, UN_NUMBER, UN_DESC, and HAZARD_CLASS. Agreement context is derived through DECODE and package calls: CONTRACT_NUM, SEGMENT1, GLOBAL_AGREEMENT_FLAG, and QUOTE_VENDOR_QUOTE_NUMBER all resolve to NULL when the corresponding source identifier is absent. The view also exposes fifteen descriptive flexfield columns (ATTRIBUTE_CATEGORY through ATTRIBUTE15), plus USSGL_TRANSACTION_CODE, GOVERNMENT_CONTEXT, and the standard concurrent program audit columns REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE.

Common Use Cases and Queries

Typical scenarios include custom XML Publisher templates for purchase orders, supplier portal extracts, audit reports on cancelled or closed lines, and reconciliation of agreement-referenced lines back to their blanket or contract sources.

  • Retrieve formatted lines for a specific order header: 
    SELECT line_num, item_description, unit_meas_lookup_code,
       unit_price, quantity, cancel_flag, closed_code
FROM   apps.po_lines_xml
WHERE  po_header_id = :p_header_id
ORDER BY line_num;
  • Identify lines sourced from agreements, where the agreement segment resolves: 
    SELECT po_line_id, line_num, contract_num, segment1,
       global_agreement_flag
FROM   apps.po_lines_xml
WHERE  from_header_id IS NOT NULL;
  • Report hazardous or UN-classified items: 
    SELECT po_header_id, line_num, un_number, un_desc, hazard_class
FROM   apps.po_lines_xml
WHERE  un_number IS NOT NULL;
  • Audit cancelled lines with their formatted cancellation timestamps: 
    SELECT po_header_id, line_num, cancel_date, cancel_reason
FROM   apps.po_lines_xml
WHERE  cancel_flag = 'Y';

Because the view invokes PL/SQL functions in PO_COMMUNICATION_PVT, queries against it should be executed in an APPS-initialized session with the appropriate purchasing responsibilities and system parameters set; otherwise formatting output may differ from what the outbound XML process produces.