Results for “po_attr_values_archive”

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

Overview

PO_ATTR_VALUES_ARCHIVE is a Purchasing (PO) module table in the Oracle E-Business Suite 12.1.1 and 12.2.2 data model, owned by the PO schema and documented as VALID in the ETRM repository. Its stated purpose is to serve as the archive table for PO_ATTRIBUTE_VALUES, the transactional store that holds the flexible attribute values captured against purchasing documents such as lines, requisition templates, and items. In EBS, purchasing attributes are extensible: rather than adding a physical column for every supplier- or item-specific attribute, the application writes values into numbered generic columns. PO_ATTR_VALUES_ARCHIVE preserves historical revisions of those values so that audit trails, amendment history, and point-in-time reporting remain possible after the live rows change.

The table is large and wide by design, carrying 328 documented columns in the 12.2.2 schema. A heuristic Data Vault classification derived from its foreign-key structure identifies it as standalone. In Data Vault modeling terms this suggests the object behaves primarily as a satellite-like history store attached to a business key, rather than as a true hub or a many-to-many link. That classification is a modeling suggestion only; the physical implementation remains a conventional relational archive table without enforced foreign keys to the parent entity.

Key Information Stored

The surrogate primary key is SYS_C00204851, defined over the composite of ATTRIBUTE_VALUES_ID and REVISION_NUM. Because the same logical attribute record can exist across multiple revisions, REVISION_NUM is integral to the key and provides the versioning semantics that distinguish the archive from the live table.

• ATTRIBUTE_VALUES_ID and REVISION_NUM — surrogate key components; together they uniquely identify an archived revision.
• PO_LINE_ID — the purchasing document line to which the attribute values belong; a principal business-key candidate.
• INVENTORY_ITEM_ID, IP_CATEGORY_ID, and ORG_ID — item, category, and operating unit context for the attribute set; INVENTORY_ITEM_ID and ORG_ID participate in the second unique index.
• REQ_TEMPLATE_NAME and REQ_TEMPLATE_LINE_NUM — the requisition template and line that sourced the values, also part of the second unique index.
• MANUFACTURER_PART_NUM, SUPPLIER_URL, MANUFACTURER_URL, ATTACHMENT_URL, UNSPSC, AVAILABILITY, LEAD_TIME, PICTURE, THUMBNAIL_IMAGE — named descriptor columns held directly on the row.
• TEXT_BASE_ATTRIBUTE1..100 and NUM_BASE_ATTRIBUTE1..100 — the generic base attribute value slots.
• TEXT_CAT_ATTRIBUTE1..50 and NUM_CAT_ATTRIBUTE1..50 — category-specific generic attribute slots.
• LATEST_EXTERNAL_FLAG and LAST_UPDATED_PROGRAM — indicators of the source system state and the program that last wrote the row.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — standard EBS who-columns and concurrent program audit context.

Two unique indexes exist: PO_ATTR_VALUES_ARCHIVE_U1 on (ATTRIBUTE_VALUES_ID, REVISION_NUM), mirroring the primary key, and PO_ATTR_VALUES_ARCHIVE_U2 on (INVENTORY_ITEM_ID, PO_LINE_ID, REQ_TEMPLATE_NAME, REQ_TEMPLATE_LINE_NUM, ORG_ID, REVISION_NUM), which identifies the business entity a revision describes.

Common Use Cases and Queries

The dominant use case is historical reconstruction: determining what purchasing attribute values applied to a line, item, or requisition template at a given revision. Because the archive preserves every revision, queries typically filter to the maximum REVISION_NUM for a given business key.

• Retrieving the latest archived revision for a line: SELECT * FROM po.po_attr_values_archive WHERE po_line_id = :line_id AND revision_num = (SELECT MAX(revision_num) FROM po.po_attr_values_archive WHERE po_line_id = :line_id).
• Reconstructing item-level attribute history: join on INVENTORY_ITEM_ID and ORG_ID together with REVISION_NUM ordering to trace how manufacturer part numbers or URLs changed over time.
• Audit and compliance reporting on who changed which attribute and when, using CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, and LAST_UPDATE_DATE.
• Concurrent program diagnostics, using REQUEST_ID and PROGRAM_ID to trace which scheduled process produced an archived revision.
• Amendment analysis for purchasing documents, comparing REVISION_NUM offsets for a PO_LINE_ID to identify changed attribute slots.

Related Objects

• PO_ATTRIBUTE_VALUES — the live parent table from which archived rows originate; the archive mirrors its ATTRIBUTE_VALUES_ID key space.
• PO_LINES_ALL — joined via PO_LINE_ID to relate archived attribute revisions to purchasing document lines.
• PO_HEADERS_ALL — reached through PO_LINES_ALL to provide document-level context for archived line attributes.
• MTL_SYSTEM_ITEMS_B — joined via INVENTORY_ITEM_ID and ORG_ID to resolve item descriptions and item-level attribute context.
• PO_REQ_TEMPLATES_ALL and requisition template line entities — matched via REQ_TEMPLATE_NAME and REQ_TEMPLATE_LINE_NUM.
• FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROGRAMS — joined via REQUEST_ID and PROGRAM_ID for provenance of archived revisions.
• FND_USER — joined via CREATED_BY and LAST_UPDATED_BY to resolve the actors behind attribute changes.