Oracle EBS ER Diagram: Key Manufacturing and Purchasing Table Relationships
This is a searchable Oracle EBS ER diagram in table form: parent table, child table and the exact join columns for purchasing, receiving, WIP, BOM, routing and inventory. Filter by area, copy the join, and stop guessing which ID links which table.
| Area | Parent table | Child table | Join columns | Cardinality | Note |
|---|---|---|---|---|---|
Purchasing | PO_HEADERS_ALL | PO_LINES_ALL | PO_HEADER_ID | 1 to many | One PO header has many lines |
Purchasing | PO_LINES_ALL | PO_LINE_LOCATIONS_ALL | PO_LINE_ID | 1 to many | Shipments: promise dates, ship-to org, quantity |
Purchasing | PO_LINE_LOCATIONS_ALL | PO_DISTRIBUTIONS_ALL | LINE_LOCATION_ID | 1 to many | Charge account and quantity split |
Purchasing | PO_HEADERS_ALL | PO_DISTRIBUTIONS_ALL | PO_HEADER_ID | 1 to many | Shortcut when line detail is not needed |
Purchasing | AP_SUPPLIERS | PO_HEADERS_ALL | VENDOR_ID | 1 to many | Supplier on the PO (PO_VENDORS in 11i) |
Purchasing | AP_SUPPLIER_SITES_ALL | PO_HEADERS_ALL | VENDOR_SITE_ID | 1 to many | Supplier site; also filter by ORG_ID |
Purchasing | MTL_SYSTEM_ITEMS_B | PO_LINES_ALL | INVENTORY_ITEM_ID = ITEM_ID | 1 to many | Add ORGANIZATION_ID from the shipment ship-to org |
Requisitions | PO_REQUISITION_HEADERS_ALL | PO_REQUISITION_LINES_ALL | REQUISITION_HEADER_ID | 1 to many | Requisition header to lines |
Requisitions | PO_REQUISITION_LINES_ALL | PO_REQ_DISTRIBUTIONS_ALL | REQUISITION_LINE_ID | 1 to many | Requisition charge accounts |
Requisitions | PO_REQ_DISTRIBUTIONS_ALL | PO_DISTRIBUTIONS_ALL | DISTRIBUTION_ID = REQ_DISTRIBUTION_ID | 1 to 1 or many | Traces a PO distribution back to its requisition |
Receiving | PO_LINE_LOCATIONS_ALL | RCV_SHIPMENT_LINES | LINE_LOCATION_ID = PO_LINE_LOCATION_ID | 1 to many | Receipt lines against a PO shipment |
Receiving | RCV_SHIPMENT_HEADERS | RCV_SHIPMENT_LINES | SHIPMENT_HEADER_ID | 1 to many | Receipt header to lines |
Receiving | RCV_SHIPMENT_LINES | RCV_TRANSACTIONS | SHIPMENT_LINE_ID | 1 to many | RECEIVE, DELIVER, RETURN TO VENDOR rows |
WIP | WIP_ENTITIES | WIP_DISCRETE_JOBS | WIP_ENTITY_ID | 1 to 1 | Name and type in WIP_ENTITIES, status and dates in discrete jobs |
WIP | WIP_DISCRETE_JOBS | WIP_OPERATIONS | WIP_ENTITY_ID + ORGANIZATION_ID | 1 to many | Job operations keyed by OPERATION_SEQ_NUM |
WIP | WIP_OPERATIONS | WIP_OPERATION_RESOURCES | WIP_ENTITY_ID + ORGANIZATION_ID + OPERATION_SEQ_NUM | 1 to many | Resources per operation |
WIP | WIP_DISCRETE_JOBS | WIP_REQUIREMENT_OPERATIONS | WIP_ENTITY_ID + ORGANIZATION_ID | 1 to many | Component requirements; OPERATION_SEQ_NUM links to the operation |
WIP | WIP_ENTITIES | WIP_TRANSACTIONS | WIP_ENTITY_ID | 1 to many | Resource and overhead transactions |
WIP | WIP_ENTITIES | WIP_MOVE_TRANSACTIONS | WIP_ENTITY_ID | 1 to many | Operation moves |
WIP | WIP_ENTITIES | MTL_MATERIAL_TRANSACTIONS | WIP_ENTITY_ID = TRANSACTION_SOURCE_ID | 1 to many | Only where TRANSACTION_SOURCE_TYPE_ID is the job or schedule source type |
BOM | BOM_STRUCTURES_B | BOM_COMPONENTS_B | BILL_SEQUENCE_ID | 1 to many | Table names in 12.x; BOM_BILL_OF_MATERIALS and BOM_INVENTORY_COMPONENTS remain as synonyms |
BOM | MTL_SYSTEM_ITEMS_B | BOM_STRUCTURES_B | INVENTORY_ITEM_ID (assembly) + ORGANIZATION_ID | 1 to many | One bill per alternate |
BOM | MTL_SYSTEM_ITEMS_B | BOM_COMPONENTS_B | COMPONENT_ITEM_ID = INVENTORY_ITEM_ID | 1 to many | Component item; use the bill organization |
Routing | BOM_OPERATIONAL_ROUTINGS | BOM_OPERATION_SEQUENCES | ROUTING_SEQUENCE_ID | 1 to many | Routing header to operations |
Routing | BOM_OPERATION_SEQUENCES | BOM_OPERATION_RESOURCES | OPERATION_SEQUENCE_ID | 1 to many | Resources per routing operation |
Routing | BOM_DEPARTMENTS | BOM_OPERATION_SEQUENCES | DEPARTMENT_ID | 1 to many | Department performing the operation |
Inventory | MTL_PARAMETERS | MTL_SYSTEM_ITEMS_B | ORGANIZATION_ID | 1 to many | Item master is keyed by item plus organization |
Inventory | MTL_SYSTEM_ITEMS_B | MTL_ONHAND_QUANTITIES_DETAIL | INVENTORY_ITEM_ID + ORGANIZATION_ID | 1 to many | On-hand by subinventory, locator and lot |
Inventory | MTL_SYSTEM_ITEMS_B | MTL_MATERIAL_TRANSACTIONS | INVENTORY_ITEM_ID + ORGANIZATION_ID | 1 to many | Transaction history |
Inventory | MTL_PARAMETERS | MTL_SECONDARY_INVENTORIES | ORGANIZATION_ID | 1 to many | Subinventories per organization |
Organization | HR_ALL_ORGANIZATION_UNITS | MTL_PARAMETERS | ORGANIZATION_ID | 1 to 1 | Inventory organizations are HR organizations with parameters |
Join columns reflect standard 12.1 and 12.2 data models. Always confirm in the Oracle eTRM for your release and add ORG_ID or ORGANIZATION_ID filters on multi-org tables.
Natural-language queries over your EBS schema
Optional: see how a private AI layer can generate read-only EBS queries on your own servers.
Optional. The tool and every export work without this. No spam.
How to read the relationships
Each row is one foreign key relationship. The parent holds the unique row, the child holds many rows pointing back to it, and the join column shows how to connect them. Where several columns are shown with plus signs, all of them are needed. WIP tables, for example, need both WIP_ENTITY_ID and ORGANIZATION_ID, and inventory tables need INVENTORY_ITEM_ID with ORGANIZATION_ID, because an item belongs to an organization.
- Search by table name to see every parent and child it touches
- Use the Area filter to see one module's data model at a time
- Copy the join column text straight into your WHERE clause
Where an ER diagram does not appear in EBS
Oracle does not ship a single ER diagram for EBS. The official source is the Electronic Technical Reference Manual (eTRM), which lists every table, column, index and foreign key per release. Foreign keys are often documented rather than enforced in the database, so a database-level ERD tool will miss many of them. A relationship table like this one is faster to use than a drawn diagram when you need a join, and you can feed it into any diagramming tool afterwards.
Multi-org and organization traps
Most transactional tables ending in _ALL carry ORG_ID for the operating unit, and the unsuffixed synonym filters by the session's MO: Operating Unit setting. If you query the base _ALL table directly, add ORG_ID yourself. Item tables are keyed by organization, so a purchase order line joins to the item using the ship-to organization from the shipment, not the operating unit. Mixing those two produces duplicate rows or missing descriptions.
Common mistakes
Joining WIP_ENTITIES to inventory transactions without checking the source type returns unrelated rows, because TRANSACTION_SOURCE_ID holds IDs from many sources. Joining PO lines to items without ORGANIZATION_ID duplicates rows for every organization where the item exists. Using the old PO_VENDORS name fails on 12.x because supplier data moved to AP_SUPPLIERS and the Trading Community Architecture.
- Always filter transaction source type when joining to MTL_MATERIAL_TRANSACTIONS
- Always carry ORGANIZATION_ID on item joins
- Check whether your release uses BOM_STRUCTURES_B or the older name
Frequently Asked Questions
Is there an official Oracle EBS ER diagram?
Not as a single diagram. Oracle publishes the Electronic Technical Reference Manual (eTRM) with tables, columns and foreign keys by release. Use it as the authority and use a relationship list like this one for quick join lookups while you build queries.
Why do PO and item tables need ORGANIZATION_ID?
Items are defined in the item master and enabled per inventory organization, so MTL_SYSTEM_ITEMS_B has one row per item and organization. Without ORGANIZATION_ID in the join you get one result row for every organization that has the item.
Do these joins work on EBS 11i?
Most purchasing, WIP and inventory joins are the same, but 11i uses PO_VENDORS and different BOM table names, and has no Trading Community supplier model. Check the 11i eTRM before relying on the supplier and BOM rows.
Can I import these relationships into a diagram tool?
Yes. Export the table to Excel or CSV and load the parent, child and join columns into your modelling or diagramming tool. Add cardinality as the edge label to get a readable diagram of the area you care about.
If your team spends hours rebuilding these joins for each report, we can put a private AI layer over your EBS schema that writes and explains read-only queries on your own servers.
Related Tools
Oracle EBS WIP Tables Reference
Lookup of Oracle EBS Work in Process tables with purpose and key columns, covering discrete jobs, operations, requirements, resources and interfaces.
OracleOracle EBS Inventory Tables Reference
Searchable lookup of the core Oracle EBS Inventory and Cost Management tables with purpose and key columns, for report writers and technical consultants.
OracleOracle EBS Finance Tables Reference (GL, AP, AR, FA)
Lookup of the base tables behind General Ledger, Payables, Receivables, Fixed Assets, Subledger Accounting and Purchasing, with the columns you join on.
OracleOracle EBS Customer and Supplier Tables Reference
Searchable list of the Trading Community Architecture customer tables and the supplier tables in Oracle EBS, with keys, join columns and where to confirm in the eTRM.
Developer ToolsOracle EBS SQL Query Generator for Items and WIP Jobs
Build ready-to-run SELECT statements over MTL_SYSTEM_ITEMS_B, WIP_DISCRETE_JOBS and on-hand quantity tables.
More Oracle tools
See all- Oracle EBS Discrete Job Statuses Reference
- Oracle EBS Inventory Transaction Types Reference
- Oracle EBS Item Attribute Template Worksheet
- Oracle EBS MRP Exception Messages Reference
- Oracle EBS BOM Interface Load Worksheet
- Oracle EBS Inventory Period Close Checklist
- Oracle EBS WIP Accounting Class Reference
- Oracle EBS Cycle Count Setup Worksheet
- Oracle EBS WIP Job Close Variance Worksheet
Keep exploring
Free tools
- Oracle EBS WIP Tables ReferenceLookup of Oracle EBS Work in Process tables with purpose and key columns, covering discrete jobs, operations, requirements, resources and interfaces.
- Oracle EBS Discrete Manufacturing Go-Live ChecklistA grouped go-live checklist for Oracle Inventory, BOM, WIP and Cost Management covering setup, data, testing and cutover for discrete manufacturers.
- Oracle EBS Cost Management Tables ReferenceA lookup of the Oracle EBS tables behind item cost, WIP jobs, BOM, routing and inventory transactions used in manufacturing costing.
ERP fixes and how-tos
- Oracle EBS Outside Processing and Production SchedulingSet up outside processing in Oracle EBS WIP and Purchasing: OSP items, resources, departments, routing and requisitions, plus how discrete job scheduling works.
- Oracle Work Order Tables: WIP_ENTITIES, WIP_DISCRETE_JOBS and the REST RouteOracle work order tables explained: WIP_ENTITIES, WIP_DISCRETE_JOBS, operations and requirements, with a join query for EBS and the Fusion REST route.
- How to Create an Item in Oracle EBSCreate an inventory item in Oracle EBS: Master Items form, master organization, templates, attributes, organization assignment, categories and common errors.
Runs with your ERP
- Esker Order Management and Oracle E-Business SuiteHow Esker captures customer POs from email, fax and PDF and creates sales orders in Oracle EBS: integration, data flows, setup, pitfalls and a private AI layer.
- Cloud Inventory (DSI) with Oracle E-Business Suite: Mobile Barcode InventoryCloud Inventory, formerly DSI, adds offline mobile barcode inventory to Oracle E-Business Suite and JD Edwards. Data flows, setup steps, pitfalls and AI notes.
- Oracle Mobile Supply Chain Applications (MSCA) for Oracle EBSOracle Mobile Supply Chain Applications (MSCA) for EBS: native RF transactions for inventory, WMS and manufacturing, setup, pitfalls and where private AI helps.
AI on your ERP
- AI for Oracle EBS in Industrial Manufacturing: WIP, MRP, and BOM Questions Answered DirectlyAI for Oracle E-Business Suite in industrial manufacturing: WIP jobs, BOMs, MRP, inventory and purchasing answered in plain English, private and read-only.
- AI for Oracle E-Business Suite, Without Leaving On-PremHosting Oracle EBS 12.2 on-prem or in a private cloud? Add a private LLM over concurrent programs, interface tables and AP/PO data without moving to Fusion.
Integrations
- How to use ChatGPT with Oracle E-Business SuiteUsing ChatGPT with Oracle E-Business Suite: export vs live access, custom GPT actions over ISG REST, enterprise data controls and when to keep data on-prem.
- How to connect Claude to Oracle E-Business SuiteConnect Claude to Oracle E-Business Suite via MCP, the Anthropic API, AWS Bedrock or Vertex, and know when an on-prem open-weight model is the better fit.