OracleFree referencePDF, Excel, Word exportOracle E-Business SuiteAll Oracle E-Business Suite tools

Oracle EBS Purchasing and Receiving Tables Reference (PO, Requisition, RCV)

A searchable reference for the Oracle EBS 12.1/12.2 purchasing, requisition and receiving tables, including the open interface and error tables you check when an import fails. Rows give the primary key, the joins that matter and the status columns to read.

Download:

Table	Area	Holds	Primary key	Joins to	Notes
PO_HEADERS_ALL	PO	PO, blanket agreement, contract and planned PO headers	PO_HEADER_ID	VENDOR_ID = AP_SUPPLIERS.VENDOR_ID; VENDOR_SITE_ID = AP_SUPPLIER_SITES_ALL.VENDOR_SITE_ID; AGENT_ID = PO_AGENTS.AGENT_ID	SEGMENT1 is the PO number; TYPE_LOOKUP_CODE STANDARD, BLANKET, CONTRACT, PLANNED; AUTHORIZATION_STATUS (APPROVED, IN PROCESS, INCOMPLETE, REJECTED, REQUIRES REAPPROVAL); CLOSED_CODE; REVISION_NUM
PO_LINES_ALL	PO	PO lines: item or description, price, quantity, category	PO_LINE_ID	PO_HEADER_ID; ITEM_ID = MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID; CATEGORY_ID = MTL_CATEGORIES_B; LINE_TYPE_ID = PO_LINE_TYPES_B	LINE_NUM, UNIT_PRICE, QUANTITY, ITEM_DESCRIPTION; on a blanket the line is the agreement line
PO_LINE_LOCATIONS_ALL	PO	Shipments (schedules) under each line; also blanket price breaks	LINE_LOCATION_ID	PO_LINE_ID, PO_HEADER_ID; PO_RELEASE_ID for release shipments; SHIP_TO_ORGANIZATION_ID	QUANTITY, QUANTITY_RECEIVED, QUANTITY_ACCEPTED, QUANTITY_REJECTED, QUANTITY_BILLED, QUANTITY_CANCELLED; NEED_BY_DATE, PROMISED_DATE; SHIPMENT_TYPE; RECEIPT_REQUIRED_FLAG and INSPECTION_REQUIRED_FLAG set 2-, 3- or 4-way match
PO_DISTRIBUTIONS_ALL	PO	Accounting and destination for each shipment	PO_DISTRIBUTION_ID	LINE_LOCATION_ID, PO_LINE_ID, PO_HEADER_ID, PO_RELEASE_ID; CODE_COMBINATION_ID = GL_CODE_COMBINATIONS; REQ_DISTRIBUTION_ID = PO_REQ_DISTRIBUTIONS_ALL.DISTRIBUTION_ID	QUANTITY_ORDERED, QUANTITY_DELIVERED, QUANTITY_BILLED; DESTINATION_TYPE_CODE INVENTORY, EXPENSE, SHOP FLOOR; ACCRUAL_ACCOUNT_ID, VARIANCE_ACCOUNT_ID; PROJECT_ID, TASK_ID
PO_RELEASES_ALL	Release	Releases against blanket agreements and scheduled releases against planned POs	PO_RELEASE_ID	PO_HEADER_ID = the blanket or planned PO header	RELEASE_NUM; users quote it as PO number-release number; RELEASE_TYPE BLANKET or SCHEDULED; AUTHORIZATION_STATUS
PO_REQUISITION_HEADERS_ALL	Requisition	Purchase and internal requisition headers	REQUISITION_HEADER_ID	PREPARER_ID = PER_ALL_PEOPLE_F.PERSON_ID	SEGMENT1 is the requisition number; TYPE_LOOKUP_CODE PURCHASE or INTERNAL; AUTHORIZATION_STATUS
PO_REQUISITION_LINES_ALL	Requisition	Requisition lines with item, quantity, need-by date and source	REQUISITION_LINE_ID	REQUISITION_HEADER_ID; LINE_LOCATION_ID = PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID once placed on a PO	SOURCE_TYPE_CODE VENDOR or INVENTORY; DESTINATION_ORGANIZATION_ID; TO_PERSON_ID is the requester
PO_REQ_DISTRIBUTIONS_ALL	Requisition	Charge account and quantity per requisition line	DISTRIBUTION_ID	REQUISITION_LINE_ID; DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.REQ_DISTRIBUTION_ID	Key column is DISTRIBUTION_ID, not REQ_DISTRIBUTION_ID; CODE_COMBINATION_ID; REQ_LINE_QUANTITY
PO_ACTION_HISTORY	Approval	Approval history for POs, releases and requisitions	OBJECT_ID + OBJECT_TYPE_CODE + SEQUENCE_NUM	OBJECT_ID = PO_HEADER_ID, PO_RELEASE_ID or REQUISITION_HEADER_ID depending on OBJECT_TYPE_CODE; EMPLOYEE_ID = PER_ALL_PEOPLE_F.PERSON_ID	OBJECT_TYPE_CODE PO, PA, RELEASE, REQUISITION; ACTION_CODE SUBMIT, APPROVE, REJECT, FORWARD; the row with null ACTION_CODE is the pending approver
PO_AGENTS	PO	Employees set up as buyers	AGENT_ID	AGENT_ID = PER_ALL_PEOPLE_F.PERSON_ID; = PO_HEADERS_ALL.AGENT_ID	START_DATE_ACTIVE, END_DATE_ACTIVE; CATEGORY_ID default; not org-striped
PO_DOCUMENT_TYPES_ALL_B	Approval	Document type controls per operating unit: approval workflow, forwarding, archiving	DOCUMENT_TYPE_CODE + DOCUMENT_SUBTYPE + ORG_ID	Translated TYPE_NAME in PO_DOCUMENT_TYPES_ALL_TL	WF_APPROVAL_ITEMTYPE and WF_APPROVAL_PROCESS (for example POAPPRV, REQAPPRV); ARCHIVE_EXTERNAL_REVISION_CODE; FORWARDING_MODE_CODE
PO_HEADERS_ARCHIVE_ALL	PO	Header copy for each archived revision	PO_HEADER_ID + REVISION_NUM	PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID	LATEST_EXTERNAL_FLAG = 'Y' marks the latest archived revision; archived on approve or print per document type setup
PO_LINES_ARCHIVE_ALL	PO	Line copy for each archived revision	PO_LINE_ID + REVISION_NUM	PO_LINE_ID, PO_HEADER_ID	Shipments and distributions have matching PO_LINE_LOCATIONS_ARCHIVE_ALL and PO_DISTRIBUTIONS_ARCHIVE_ALL
RCV_SHIPMENT_HEADERS	Receiving	Receipt and ASN or in-transit shipment headers	SHIPMENT_HEADER_ID	VENDOR_ID, VENDOR_SITE_ID; SHIP_TO_ORG_ID	RECEIPT_NUM is the receipt number; RECEIPT_SOURCE_CODE VENDOR, INVENTORY, INTERNAL ORDER, CUSTOMER; SHIPMENT_NUM for ASNs; not org-striped by ORG_ID
RCV_SHIPMENT_LINES	Receiving	Receipt and shipment lines	SHIPMENT_LINE_ID	SHIPMENT_HEADER_ID; PO_HEADER_ID, PO_LINE_ID, PO_LINE_LOCATION_ID, PO_DISTRIBUTION_ID; ITEM_ID + TO_ORGANIZATION_ID	QUANTITY_SHIPPED, QUANTITY_RECEIVED; SHIPMENT_LINE_STATUS_CODE; SOURCE_DOCUMENT_CODE PO, REQ, INVENTORY, RMA
RCV_TRANSACTIONS	Receiving	Every receiving transaction: receipt, inspection, delivery, return, correction	TRANSACTION_ID	PARENT_TRANSACTION_ID = the transaction it acts on; SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID; PO_HEADER_ID, PO_LINE_LOCATION_ID, PO_DISTRIBUTION_ID	TRANSACTION_TYPE RECEIVE, ACCEPT, REJECT, TRANSFER, DELIVER, CORRECT, RETURN TO VENDOR, RETURN TO RECEIVING; DESTINATION_TYPE_CODE RECEIVING, INVENTORY, EXPENSE, SHOP FLOOR
RCV_SUPPLY	Receiving	Quantity sitting in receiving or in transit, not yet delivered	No single surrogate key; RCV_TRANSACTION_ID identifies receiving supply	RCV_TRANSACTION_ID = RCV_TRANSACTIONS.TRANSACTION_ID; SHIPMENT_LINE_ID; PO_DISTRIBUTION_ID	SUPPLY_TYPE_CODE RECEIVING or SHIPMENT; drives what the Receiving Transactions form offers for delivery
MTL_SUPPLY	Receiving	Supply picture used by planning and receiving: open PO, approved req, in transit, in receiving	No single surrogate key; SUPPLY_TYPE_CODE + SUPPLY_SOURCE_ID	PO_HEADER_ID, PO_LINE_ID, PO_LINE_LOCATION_ID, PO_DISTRIBUTION_ID; REQ_HEADER_ID, REQ_LINE_ID; SHIPMENT_LINE_ID	SUPPLY_TYPE_CODE PO, REQ, SHIPMENT, RECEIVING; a missing MTL_SUPPLY row is a classic cause of 'cannot receive this PO'
PO_HEADERS_INTERFACE	Interface	PDOI header rows for standard POs, blankets and catalogs	INTERFACE_HEADER_ID	Children by INTERFACE_HEADER_ID; BATCH_ID groups a run	ACTION ORIGINAL, UPDATE, REPLACE; DOCUMENT_TYPE_CODE; PROCESS_CODE (PENDING, ACCEPTED, REJECTED); APPROVAL_STATUS
PO_LINES_INTERFACE	Interface	PDOI line rows (in 12.1 and earlier also shipment data)	INTERFACE_LINE_ID	INTERFACE_HEADER_ID	LINE_NUM, SHIPMENT_NUM, ITEM or ITEM_ID, QUANTITY, UNIT_PRICE; PROCESS_CODE
PO_LINE_LOCATIONS_INTERFACE	Interface	PDOI shipment rows (R12)	INTERFACE_LINE_LOCATION_ID	INTERFACE_HEADER_ID, INTERFACE_LINE_ID	Use for multiple shipments per line; otherwise PDOI derives one shipment from the line row
PO_DISTRIBUTIONS_INTERFACE	Interface	PDOI distribution rows	INTERFACE_DISTRIBUTION_ID	INTERFACE_HEADER_ID, INTERFACE_LINE_ID (and INTERFACE_LINE_LOCATION_ID in R12)	CHARGE_ACCOUNT_ID or segments; DISTRIBUTION_NUM; QUANTITY_ORDERED
PO_REQUISITIONS_INTERFACE_ALL	Interface	Requisition Import rows (one row per requisition line)	TRANSACTION_ID	REQ_DIST_SEQUENCE_ID = PO_REQ_DIST_INTERFACE_ALL.DIST_SEQUENCE_ID when MULTI_DISTRIBUTIONS = 'Y'	INTERFACE_SOURCE_CODE, BATCH_ID; PROCESS_FLAG = 'ERROR' on failure; SOURCE_TYPE_CODE, DESTINATION_TYPE_CODE, AUTHORIZATION_STATUS, PREPARER_ID
PO_REQ_DIST_INTERFACE_ALL	Interface	Multiple distributions for one interfaced requisition line	DIST_SEQUENCE_ID + DISTRIBUTION_NUMBER	DIST_SEQUENCE_ID = PO_REQUISITIONS_INTERFACE_ALL.REQ_DIST_SEQUENCE_ID	Only used when the line needs more than one charge account
PO_INTERFACE_ERRORS	Interface	Errors from PDOI, Requisition Import and the Receiving Transaction Processor	No single surrogate key; filter by INTERFACE_TYPE and the interface IDs	INTERFACE_TRANSACTION_ID = PO_REQUISITIONS_INTERFACE_ALL.TRANSACTION_ID (REQIMPORT); INTERFACE_HEADER_ID / INTERFACE_LINE_ID to PDOI or RCV interface rows	INTERFACE_TYPE (for example PO_DOCS_OPEN_INTERFACE, REQIMPORT), TABLE_NAME, COLUMN_NAME, ERROR_MESSAGE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID
RCV_HEADERS_INTERFACE	Interface	Receipt and ASN headers to be processed	HEADER_INTERFACE_ID	HEADER_INTERFACE_ID = RCV_TRANSACTIONS_INTERFACE.HEADER_INTERFACE_ID; GROUP_ID	PROCESSING_STATUS_CODE; RECEIPT_SOURCE_CODE; TRANSACTION_TYPE NEW; VENDOR_ID; SHIP_TO_ORGANIZATION_ID; VALIDATION_FLAG
RCV_TRANSACTIONS_INTERFACE	Interface	Receiving transactions waiting for or rejected by the Receiving Transaction Processor	INTERFACE_TRANSACTION_ID	HEADER_INTERFACE_ID; GROUP_ID; PO_HEADER_ID, PO_LINE_LOCATION_ID; PARENT_TRANSACTION_ID for deliver or correct	PROCESSING_STATUS_CODE (PENDING, RUNNING, ERROR, COMPLETED) and TRANSACTION_STATUS_CODE (PENDING, ERROR); PROCESSING_MODE_CODE BATCH, IMMEDIATE, ONLINE; AUTO_TRANSACT_CODE DELIVER receives and delivers in one step
PO_VENDORS	PO	Supplier view kept for 11i compatibility	VENDOR_ID	VENDOR_ID = PO_HEADERS_ALL.VENDOR_ID	In R12 a view over AP_SUPPLIERS (suppliers live in TCA with AP_SUPPLIERS as the AP layer); write new code against AP_SUPPLIERS and AP_SUPPLIER_SITES_ALL

Scope: Oracle EBS 12.1 and 12.2 Purchasing (PO) and Receiving (RCV). Verify columns for your patch level in eTRM before production use.

Want a procure-to-pay SQL pack for your release?

Tell us your EBS version and the purchasing questions you answer every week. We will send reviewed SQL for open POs, unplaced requisitions and stuck receipts.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

The PO hierarchy: header, line, shipment, distribution

A standard PO has four levels. PO_HEADERS_ALL (supplier, buyer, status) has many PO_LINES_ALL (item, price), each line has one or more shipments in PO_LINE_LOCATIONS_ALL (ship-to org, need-by date, quantity received and billed), and each shipment has one or more distributions in PO_DISTRIBUTIONS_ALL (charge account, destination, quantity delivered). Receiving updates the shipment and distribution quantities; AP matching updates QUANTITY_BILLED. Blanket releases add PO_RELEASES_ALL: the release has no lines of its own, its shipments sit in PO_LINE_LOCATIONS_ALL with PO_RELEASE_ID set and point back to the blanket line. When summing open quantity, work at shipment level and subtract QUANTITY_RECEIVED and QUANTITY_CANCELLED from QUANTITY.

Header to line: PO_HEADER_ID
Line to shipment: PO_LINE_ID
Shipment to distribution: LINE_LOCATION_ID
Release shipments: PO_LINE_LOCATIONS_ALL.PO_RELEASE_ID = PO_RELEASES_ALL.PO_RELEASE_ID

Linking a requisition to its PO

The dependable link is at distribution level: PO_DISTRIBUTIONS_ALL.REQ_DISTRIBUTION_ID = PO_REQ_DISTRIBUTIONS_ALL.DISTRIBUTION_ID, then PO_REQ_DISTRIBUTIONS_ALL.REQUISITION_LINE_ID to the requisition line and REQUISITION_HEADER_ID to the header. PO_REQUISITION_LINES_ALL.LINE_LOCATION_ID also points to the PO shipment once AutoCreate or Create Documents places the line, which is handy for a quick check of which requisition lines are still unplaced (LINE_LOCATION_ID is null, not cancelled, approved header). Internal requisitions do not create a PO; they are sourced from inventory and become internal sales orders, so their trail continues into OE_ORDER_LINES_ALL rather than PO tables.

Receiving interface errors

Receipts loaded through RCV_HEADERS_INTERFACE and RCV_TRANSACTIONS_INTERFACE are processed by the Receiving Transaction Processor (RVCTP). After a run, rows with PROCESSING_STATUS_CODE = 'ERROR' or TRANSACTION_STATUS_CODE = 'ERROR' failed. The message is in PO_INTERFACE_ERRORS, joined on INTERFACE_HEADER_ID to RCV_HEADERS_INTERFACE.HEADER_INTERFACE_ID and INTERFACE_LINE_ID to RCV_TRANSACTIONS_INTERFACE.INTERFACE_TRANSACTION_ID, with ERROR_MESSAGE and COLUMN_NAME telling you what failed validation. Users can also see failures in the Transaction Status Summary form in Purchasing. Common causes are a closed shipment, a quantity over tolerance, a missing MTL_SUPPLY row, or a lot or serial not supplied for a controlled item. Fix the data and set the rows back to PENDING with a new GROUP_ID, or delete and reload.

Archive tables and revisions

When a document type is set to archive on approve (or on print), each approved revision is copied to PO_HEADERS_ARCHIVE_ALL, PO_LINES_ARCHIVE_ALL, PO_LINE_LOCATIONS_ARCHIVE_ALL and PO_DISTRIBUTIONS_ARCHIVE_ALL, keyed by the base ID plus REVISION_NUM. LATEST_EXTERNAL_FLAG = 'Y' marks the most recently archived revision. To see what changed between revisions, compare two archive rows for the same PO_LINE_ID. Note that an unapproved change raises REVISION_NUM on the base table but only reaches the archive after approval, so the base table can be one revision ahead. Approval history for the same document is in PO_ACTION_HISTORY with OBJECT_TYPE_CODE = 'PO' and OBJECT_ID = PO_HEADER_ID.

Frequently Asked Questions

What are the main PO tables in Oracle EBS?

PO_HEADERS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL (shipments) and PO_DISTRIBUTIONS_ALL, joined by PO_HEADER_ID, PO_LINE_ID and LINE_LOCATION_ID. Releases are in PO_RELEASES_ALL, approval history in PO_ACTION_HISTORY and archived revisions in the _ARCHIVE_ALL tables. Receipts are in RCV_SHIPMENT_HEADERS, RCV_SHIPMENT_LINES and RCV_TRANSACTIONS.

What are the PO interface tables?

Purchasing Documents Open Interface uses PO_HEADERS_INTERFACE, PO_LINES_INTERFACE, PO_LINE_LOCATIONS_INTERFACE (R12) and PO_DISTRIBUTIONS_INTERFACE, loaded by Import Standard Purchase Orders (POXPOPDOI) for standard POs. Requisitions use PO_REQUISITIONS_INTERFACE_ALL and PO_REQ_DIST_INTERFACE_ALL with Requisition Import. Receipts use RCV_HEADERS_INTERFACE and RCV_TRANSACTIONS_INTERFACE.

Where do Requisition Import errors go?

To PO_INTERFACE_ERRORS with INTERFACE_TYPE = 'REQIMPORT'. Join INTERFACE_TRANSACTION_ID to PO_REQUISITIONS_INTERFACE_ALL.TRANSACTION_ID; the failed interface rows have PROCESS_FLAG = 'ERROR'. The Requisition Import Exceptions report lists the same messages. Fix the data, reset PROCESS_FLAG to null and rerun import for the same INTERFACE_SOURCE_CODE and BATCH_ID.

Which tables hold inventory receipts in Oracle EBS?

Receipt headers are in RCV_SHIPMENT_HEADERS (RECEIPT_NUM), lines in RCV_SHIPMENT_LINES and each step (RECEIVE, ACCEPT, DELIVER, RETURN TO VENDOR) in RCV_TRANSACTIONS. A delivery to inventory also creates a row in MTL_MATERIAL_TRANSACTIONS, linked by RCV_TRANSACTION_ID, which is where on-hand changes. Quantity not yet delivered is in RCV_SUPPLY.

If your buyers and receiving team want to ask Oracle EBS about open POs, late shipments and stuck receipts in plain English on your own servers, we can set that up read-only over these tables.

Talk to an Expert Explore More Free Tools