Results for “po_attr_values_archive_u1”

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

Overview

The PO.PO_ATTR_VALUES_ARCHIVE table is the archive counterpart of the Oracle Purchasing attribute values entity. In Oracle E-Business Suite 12.1.1 and 12.2.2, the online table PO_ATTRIBUTE_VALUES stores the descriptive and categorization attribute values attached to purchasing documents such as requisition and quotation lines, and this archive table preserves historical revisions of those records. The object resides in the APPS schema under the PO (Purchasing) product and is defined with a storage clause in the APPS_TS_TX_DATA tablespace with a PCT Free of 10.

The table is documented as the archive table for PO_ATTRIBUTE_VALUES, which means that rows are written here as older revisions are superseded on the transactional table. This supports auditability, point-in-time reporting, and retention requirements for iProcurement and Purchasing attribute data, including item descriptive attributes, category attributes, supplier-facing URLs, and image references associated with a shopping or sourcing line.

The ETRM relationship heuristic classifies this object as standalone, meaning the mined foreign-key structure does not resolve it into a strict hub, link, or satellite pattern within a Data Vault model. As a modeling suggestion, it is best treated as a revision-history satellite attached to the purchasing attribute business key, with its own effective versioning expressed through REVISION_NUM. Because REVISION_NUM participates in both unique indexes, the version dimension is central to how the table is queried.

Key Information Stored

With 328 documented columns, this is a wide, denormalized attribute store. The most significant columns are:

• ATTRIBUTE_VALUES_ID and REVISION_NUM — together they form the unique index PO_ATTR_VALUES_ARCHIVE_U1 and the physical primary key SYS_C00204851. ATTRIBUTE_VALUES_ID is documented as a foreign key to the transaction table, while REVISION_NUM identifies the specific revision.
• INVENTORY_ITEM_ID, PO_LINE_ID, REQ_TEMPLATE_NAME, REQ_TEMPLATE_LINE_NUM, and ORG_ID — together these form the second unique index, PO_ATTR_VALUES_ARCHIVE_U2, which is the user's search term. This composite is the primary business-key candidate: it identifies an attribute row by item, purchasing line, requisition template context, and operating unit for a given revision.
• PO_LINE_ID — populated with a positive value when the row is a child of a blanket line or quotation line, and -2 otherwise.
• REQ_TEMPLATE_NAME / REQ_TEMPLATE_LINE_NUM — hold the requisition template name and line number the record belongs to, or -2 when not applicable.
• IP_CATEGORY_ID — the item category used by iProcurement.
• MANUFACTURER_PART_NUM, UNSPSC, LEAD_TIME, AVAILABILITY — commercial and sourcing attributes.
• THUMBNAIL_IMAGE, SUPPLIER_URL, MANUFACTURER_URL, ATTACHMENT_URL, PICTURE — content and media references surfaced in iProcurement punchout and catalog displays.
• TEXT_BASE_ATTRIBUTE1..100, NUM_BASE_ATTRIBUTE1..100, TEXT_CAT_ATTRIBUTE1..50, NUM_CAT_ATTRIBUTE1..50 — the descriptive flexfield and category flexfield segments that carry the bulk of configurable attribute data.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — standard WHO and concurrent-program audit columns.
• LATEST_EXTERNAL_FLAG and LAST_UPDATED_PROGRAM — indicate whether the revision was the latest externally sourced version and which program last modified it.

Common Use Cases and Queries

Reporting and reconciliation are the dominant uses. To trace the revision history for a specific attribute record:

• SELECT * FROM po.po_attr_values_archive WHERE attribute_values_id = :id ORDER BY revision_num;
• To find attribute history for a given item and operating unit: SELECT * FROM po.po_attr_values_archive WHERE inventory_item_id = :item AND org_id = :org ORDER BY revision_num DESC;

Auditors commonly compare the archive against the live PO_ATTRIBUTE_VALUES table to identify changed attributes, while iProcurement and catalog administrators use archived rows to reconstruct the attribute content presented at the time a requisition was created. Purging and retention programs also target this table, and DBAs frequently investigate the two unique indexes for growth and fragmentation.

Related Objects

The parent transactional table PO_ATTRIBUTE_VALUES is the principal source of archived rows, joined on ATTRIBUTE_VALUES_ID and REVISION_NUM. PO_LINE_ID links to PO_LINES (or PO_LINES_ALL in R12), while INVENTORY_ITEM_ID joins to MTL_SYSTEM_ITEMS_B and ORG_ID to HR_OPERATING_UNITS. IP_CATEGORY_ID relates to MTL_CATEGORIES_B, and requisition template references relate to the PO_REQ_TEMPLATES area. Requisition-side linkage flows through PO_REQUISITION_LINES_ALL and PO_HEADERS_ALL, and standard concurrent processing references FND_CONCURRENT_REQUESTS through REQUEST_ID.