Search Results total_line_amount

Overview

The APPS.PO_LINES_ARCHIVE_XML view is a Purchasing (PO) module reporting object within Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes archived purchase order line data in a flattened, XML-oriented projection intended to support outbound document generation, archival extracts, and integration feeds where the line-level detail of a purchase order must be reconstructed outside the transactional line tables. Because the view is defined in the APPS schema and remains VALID, it is generally accessed by custom reports, concurrent programs, and interface extracts that need to read archived PO line attributes without joining the numerous lookup, UOM, UN number, and hazard class tables individually.

The view is closely related to the archived purchasing line structure and relies on the PO_COMMUNICATION_PVT package to resolve segment numbers, agreement flags, quotation references, and formatting masks at query time. This design makes the view convenient but also means its output depends on PL/SQL package functions rather than pure SQL joins.

Underlying Base Objects

The documented base objects for PO_LINES_ARCHIVE_XML are:

• PO_LINES_ARCHIVE_ALL (SYNONYM) — the primary archived PO line table supplying the bulk of the selected columns.
• MTL_UNITS_OF_MEASURE_TL — provides the translated unit of measure description via MUM.UNIT_OF_MEASURE_TL.
• PO_UN_NUMBERS_TL — supplies United Nations number and description (PUN.UN_NUMBER, PUN.DESCRIPTION).
• PO_HAZARD_CLASSES_TL — supplies hazard class information.
• PO_COMMUNICATION_PVT (PACKAGE) — resolves contract number, segment number, agreement flag, quotation number, agreement line number, and format masks.
• PO_CORE_S (PACKAGE) — supporting core Purchasing logic referenced by the view definition.
• PO_COMMUNICATION_GT (SYNONYM), FINANCIALS_SYSTEM_PARAMS_ALL, MTL_SYSTEM_ITEMS_KFV, PO_LINE_TYPES_B — referenced objects supporting item, currency, and system parameter resolution.

The view joins or references these objects to enrich the archived line record with descriptive lookup values and formatted numeric strings.

Key Columns

The view exposes line identity and descriptive fields such as ITEM_REVISION, LINE_NUM, ITEM_DESCRIPTION, VENDOR_PRODUCT_NUM, and NOTE_TO_VENDOR, along with cancellation attributes (CANCEL_FLAG, CANCEL_DATE, CANCEL_REASON) and closure attributes (CLOSED_CODE, CLOSED_DATE, CLOSED_REASON, CLOSED_BY).

Quantities and pricing are represented by QUANTITY, QUANTITY_COMMITTED, UNIT_PRICE, and MIN_RELEASE_AMOUNT. Notably, these monetary and price columns are wrapped in TO_CHAR with PO_COMMUNICATION_PVT.GETFORMATMASK, returning formatted character strings rather than native numeric values. In the context of the search term "total_line_amount," it is important to note that this view does not expose a dedicated TOTAL_LINE_AMOUNT column; consumers typically derive line amount externally as QUANTITY multiplied by UNIT_PRICE. Because UNIT_PRICE is returned as a formatted string, arithmetic must be performed against the underlying archived table or after converting the string value.

Additional columns include UNIT_MEAS_LOOKUP_CODE, UN_NUMBER, UN_DESC, HAZARD_CLASS, CONTRACT_NUM, SEGMENT1, GLOBAL_AGREEMENT_FLAG, QUOTE_VENDOR_QUOTE_NUMBER, QUOTATION_LINE, PRICE_TYPE_LOOKUP_CODE, PRICE_BREAK_LOOKUP_CODE, REFERENCE_NUM, USSGL_TRANSACTION_CODE, GOVERNMENT_CONTEXT, the ATTRIBUTE1 through ATTRIBUTE15 descriptive flexfield columns, and the standard key columns PO_HEADER_ID, PO_LINE_ID, ITEM_ID, FROM_HEADER_ID, FROM_LINE_ID, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE.

Common Use Cases and Queries

Typical uses include reconstructing archived PO line detail for XML document output, auditing cancelled or closed lines, and feeding external procurement or supplier portals. A representative query joining the view to the archived line table for line amount derivation is:

• SELECT v.LINE_NUM, v.ITEM_DESCRIPTION, v.QUANTITY, v.UNIT_PRICE FROM APPS.PO_LINES_ARCHIVE_XML v WHERE v.PO_HEADER_ID = :header_id ORDER BY v.LINE_NUM;
• To obtain a numeric line amount, join back to PO_LINES_ARCHIVE_ALL: SELECT v.LINE_NUM, a.QUANTITY * a.UNIT_PRICE TOTAL_LINE_AMOUNT FROM APPS.PO_LINES_ARCHIVE_XML v, APPS.PO_LINES_ARCHIVE_ALL a WHERE v.PO_LINE_ID = a.PO_LINE_ID AND v.PO_HEADER_ID = :header_id;
• For cancellation analysis: SELECT CANCEL_FLAG, CANCEL_DATE, CANCEL_REASON, COUNT(*) FROM APPS.PO_LINES_ARCHIVE_XML GROUP BY CANCEL_FLAG, CANCEL_DATE, CANCEL_REASON;

Because monetary columns are formatted strings and package functions are invoked at runtime, performance and type-conversion considerations should be evaluated when the view is used for high-volume extracts.