Results for “po_requisitions_interface_all”

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

Overview

PO_REQUISITIONS_INTERFACE_ALL is the Requisition Import interface table within the Oracle Purchasing (PO) module. It resides in the PO schema and holds 281 documented columns in ETRM 12.2.2. The table serves as the staging area for requisition data that is loaded from external sources — including XML feeds, flat files, spreadsheet interfaces, and other Oracle applications — before the Requisition Import concurrent program validates each row and transfers it into the production requisition tables PO_REQUISITION_HEADERS_ALL, PO_REQUISITION_LINES_ALL, and PO_REQ_DISTRIBUTIONS_ALL. Because a single interface row can carry header, line, and distribution attributes, the table functions as a denormalized staging surface rather than a normalized transactional entity.

From a Data Vault modeling perspective, the ETRM heuristic classifies this object as satellite-leaning. This classification reflects its role as an attribute-bearing staging record keyed by the surrogate TRANSACTION_ID, with a dense set of descriptive and foreign-key columns surrounding that key rather than serving as an independent business hub whose identity is defined by a natural business key.

Key Information Stored

The primary key is PO_REQ_INTERFACE_PK, defined on the TRANSACTION_ID column, and the unique index PO_REQUISITIONS_INTERFACE_U1 also covers TRANSACTION_ID. Because TRANSACTION_ID is a system-generated surrogate, it does not participate in the business meaning of the requisition; the true business identifiers are the requisition number segments and the interface source columns.

The most significant columns include:

• TRANSACTION_ID — surrogate primary key; unique row identifier for each staged record.
• PROCESS_FLAG — controls whether the row is processed, rejected, or already imported by the Requisition Import program.
• INTERFACE_SOURCE_CODE / INTERFACE_SOURCE_LINE_ID — identifies the originating external system and source line.
• REQ_NUMBER_SEGMENT1 through REQ_NUMBER_SEGMENT5 — the requisition number key flexfield segments.
• ITEM_ID / ITEM_SEGMENT1–ITEM_SEGMENT20 — item identification either by internal ID or by concatenated segment values.
• QUANTITY, UNIT_PRICE, UNIT_OF_MEASURE, UOM_CODE — requested quantity and pricing details for the line.
• CHARGE_ACCOUNT_ID and CHARGE_ACCOUNT_SEGMENT1–30 — the accounting flexfield for the distribution.
• CATEGORY_ID and CATEGORY_SEGMENT1–20 — purchasing category assignment.
• SUGGESTED_BUYER_ID, SUGGESTED_VENDOR_ID, SUGGESTED_VENDOR_SITE_ID — sourcing suggestions carried into the imported requisition.
• DESTINATION_ORGANIZATION_ID, DESTINATION_SUBINVENTORY, DELIVER_TO_LOCATION_ID — delivery and destination attributes.
• ORG_ID — the operating unit that owns the staged requisition.
• AUTHORIZATION_STATUS, APPROVER_ID, APPROVAL_PATH_ID — approval characteristics applied at import.

Common Use Cases and Queries

The principal use case is bulk requisition loading. External systems populate the interface table, set PROCESS_FLAG to a ready state, and then run the Requisition Import program. A common operational query monitors pending rows before import:

SELECT transaction_id, interface_source_code, req_number_segment1, item_id, quantity, process_flag FROM po.po_requisitions_interface_all WHERE process_flag IN ('PENDING','ERROR') AND org_id = :p_org_id;

Error diagnosis joins PO_INTERFACE_ERRORS, which references PO_REQUISITIONS_INTERFACE_ALL via INTERFACE_TRANSACTION_ID, to expose rejection messages per row:

SELECT i.transaction_id, i.req_number_segment1, e.error_message FROM po.po_requisitions_interface_all i, po.po_interface_errors e WHERE e.interface_transaction_id = i.transaction_id AND i.process_flag = 'ERROR';

Reporting use cases include auditing import volumes per source system, validating distributed account combinations against GL_CODE_COMBINATIONS before submission, and reconciling staged quantities against imported requisition lines.

Related Objects

• PO_REQUISITION_HEADERS_ALL — destination header table; joined on REQUISITION_HEADER_ID.
• PO_REQUISITION_LINES_ALL — destination line table; joined on REQUISITION_LINE_ID.
• PO_REQ_DISTRIBUTIONS_ALL — destination distribution table; joined on REQ_DISTRIBUTION_ID.
• PO_INTERFACE_ERRORS — references this table via INTERFACE_TRANSACTION_ID and stores validation failures.
• MTL_CATEGORIES_B — supplies the category key flexfield values via CATEGORY_ID.
• GL_CODE_COMBINATIONS — validates the accounting flexfield for CHARGE_ACCOUNT_ID, ACCRUAL_ACCOUNT_ID, VARIANCE_ACCOUNT_ID, and BUDGET_ACCOUNT_ID.
• PO_AGENTS, PO_VENDORS, PO_VENDOR_SITES_ALL — resolve buyer and supplier suggestions through SUGGESTED_BUYER_ID, SUGGESTED_VENDOR_ID, and SUGGESTED_VENDOR_SITE_ID.
• PO_LINE_TYPES_B, PO_UN_NUMBERS_B, PO_HAZARD_CLASSES_B — controlled reference data joined on LINE_TYPE_ID, UN_NUMBER_ID, and HAZARD_CLASS_ID.
• PO_NOTES — provides the ten note identifiers referenced by NOTE1_ID through NOTE10_ID.
• PA_PROJECTS_ALL and PA_TASKS — support project-charged requisitions through PROJECT_ID and TASK_ID.