Search Results po_lines_archive_all

Overview

The PO_LINES_ARCHIVE_ALL table is a core data object within the Oracle E-Business Suite Purchasing (PO) module, specifically for releases 12.1.1 and 12.2.2. It serves as the primary repository for historical, non-current versions of purchase order line records. This table is essential for maintaining a complete audit trail of changes made to purchase order lines throughout their lifecycle. When a purchase order line is revised, the system archives the pre-revision state of the line into this table before applying updates to the active record in the PO_LINES_ALL table. This architectural pattern supports compliance, historical reporting, and the ability to review the precise state of a purchase order line at any point in its revision history.

Key Information Stored

The table stores a comprehensive snapshot of a purchase order line at the moment of archival. Its structure mirrors that of the active PO_LINES_ALL table, capturing all line-level attributes. The primary key is a composite of PO_LINE_ID and REVISION_NUM, ensuring a unique record for each historical version of a line. Critical columns include PO_HEADER_ID (link to the purchase order header), LINE_NUM (the line's position on the order), and REVISION_NUM (the sequential version identifier). Other significant columns reference master data, such as LINE_TYPE_ID (link to PO_LINE_TYPES_B), CATEGORY_ID (link to MTL_CATEGORIES_B), and FROM_LINE_ID (which can point back to the active line in PO_LINES_ALL or a source line for blanket releases). The FROM_HEADER_ID column similarly links to a source purchase order header.

Common Use Cases and Queries

The primary use case is auditing and historical analysis of purchase order line changes. Organizations use this data to track modifications to item descriptions, quantities, prices, or dates over time. A common reporting requirement is to retrieve the full revision history for a specific purchase order line. The following sample SQL pattern illustrates this:

• SELECT * FROM po.po_lines_archive_all WHERE po_header_id = <header_id> AND line_num = <line_number> ORDER BY revision_num DESC;

Another practical scenario involves comparing the most recent archived version against the current active line to identify specific changes made during the last revision. Data from this table is also integral to custom compliance reports that must demonstrate the evolution of contractual terms within a purchase order.

Related Objects

PO_LINES_ARCHIVE_ALL has defined relationships with several key Purchasing and Inventory tables, as documented in the provided metadata. Its primary relationship is with the active purchase order lines table, PO_LINES_ALL, via the PO_LINE_ID and FROM_LINE_ID foreign keys. It is a child table of PO_HEADERS_ALL through the PO_HEADER_ID and FROM_HEADER_ID columns. The table also references various base reference tables: PO_LINE_TYPES_B (LINE_TYPE_ID), MTL_CATEGORIES_B (CATEGORY_ID), PO_UN_NUMBERS_B (UN_NUMBER_ID), and PO_HAZARD_CLASSES_B (HAZARD_CLASS_ID). Furthermore, it has a foreign key relationship with the temporary table ICX_PO_REVISIONS_TEMP on the composite of LINE_ID and REVISION_NUM, which is used in the revision comparison user interface.