# Oracle EBS ER Diagram: Key Manufacturing and Purchasing Table Relationships > Source: https://netray.co/tools/oracle-ebs-er-diagram-key-table-relationships > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer Parent and child table pairs with the exact join columns for the Oracle EBS purchasing, WIP, BOM, routing and inventory data models. ## Category oracle ## Erp Oracle E-Business Suite ## Industry Discrete Manufacturing ## Audience - EBS developer - Business analyst - Report writer ## Tool **Kind:** worksheet **Mode:** reference **Columns:** #### Area **Key:** area **Type:** text #### Parent table **Key:** parent **Type:** text #### Child table **Key:** child **Type:** text #### Join columns **Key:** join **Type:** text #### Cardinality **Key:** card **Type:** text #### Note **Key:** note **Type:** text **Rows:** **Area:** Purchasing **Parent:** PO_HEADERS_ALL **Child:** PO_LINES_ALL **Join:** PO_HEADER_ID **Card:** 1 to many **Note:** One PO header has many lines **Area:** Purchasing **Parent:** PO_LINES_ALL **Child:** PO_LINE_LOCATIONS_ALL **Join:** PO_LINE_ID **Card:** 1 to many **Note:** Shipments: promise dates, ship-to org, quantity **Area:** Purchasing **Parent:** PO_LINE_LOCATIONS_ALL **Child:** PO_DISTRIBUTIONS_ALL **Join:** LINE_LOCATION_ID **Card:** 1 to many **Note:** Charge account and quantity split **Area:** Purchasing **Parent:** PO_HEADERS_ALL **Child:** PO_DISTRIBUTIONS_ALL **Join:** PO_HEADER_ID **Card:** 1 to many **Note:** Shortcut when line detail is not needed **Area:** Purchasing **Parent:** AP_SUPPLIERS **Child:** PO_HEADERS_ALL **Join:** VENDOR_ID **Card:** 1 to many **Note:** Supplier on the PO (PO_VENDORS in 11i) **Area:** Purchasing **Parent:** AP_SUPPLIER_SITES_ALL **Child:** PO_HEADERS_ALL **Join:** VENDOR_SITE_ID **Card:** 1 to many **Note:** Supplier site; also filter by ORG_ID **Area:** Purchasing **Parent:** MTL_SYSTEM_ITEMS_B **Child:** PO_LINES_ALL **Join:** INVENTORY_ITEM_ID = ITEM_ID **Card:** 1 to many **Note:** Add ORGANIZATION_ID from the shipment ship-to org **Area:** Requisitions **Parent:** PO_REQUISITION_HEADERS_ALL **Child:** PO_REQUISITION_LINES_ALL **Join:** REQUISITION_HEADER_ID **Card:** 1 to many **Note:** Requisition header to lines **Area:** Requisitions **Parent:** PO_REQUISITION_LINES_ALL **Child:** PO_REQ_DISTRIBUTIONS_ALL **Join:** REQUISITION_LINE_ID **Card:** 1 to many **Note:** Requisition charge accounts **Area:** Requisitions **Parent:** PO_REQ_DISTRIBUTIONS_ALL **Child:** PO_DISTRIBUTIONS_ALL **Join:** DISTRIBUTION_ID = REQ_DISTRIBUTION_ID **Card:** 1 to 1 or many **Note:** Traces a PO distribution back to its requisition **Area:** Receiving **Parent:** PO_LINE_LOCATIONS_ALL **Child:** RCV_SHIPMENT_LINES **Join:** LINE_LOCATION_ID = PO_LINE_LOCATION_ID **Card:** 1 to many **Note:** Receipt lines against a PO shipment **Area:** Receiving **Parent:** RCV_SHIPMENT_HEADERS **Child:** RCV_SHIPMENT_LINES **Join:** SHIPMENT_HEADER_ID **Card:** 1 to many **Note:** Receipt header to lines **Area:** Receiving **Parent:** RCV_SHIPMENT_LINES **Child:** RCV_TRANSACTIONS **Join:** SHIPMENT_LINE_ID **Card:** 1 to many **Note:** RECEIVE, DELIVER, RETURN TO VENDOR rows **Area:** WIP **Parent:** WIP_ENTITIES **Child:** WIP_DISCRETE_JOBS **Join:** WIP_ENTITY_ID **Card:** 1 to 1 **Note:** Name and type in WIP_ENTITIES, status and dates in discrete jobs **Area:** WIP **Parent:** WIP_DISCRETE_JOBS **Child:** WIP_OPERATIONS **Join:** WIP_ENTITY_ID + ORGANIZATION_ID **Card:** 1 to many **Note:** Job operations keyed by OPERATION_SEQ_NUM **Area:** WIP **Parent:** WIP_OPERATIONS **Child:** WIP_OPERATION_RESOURCES **Join:** WIP_ENTITY_ID + ORGANIZATION_ID + OPERATION_SEQ_NUM **Card:** 1 to many **Note:** Resources per operation **Area:** WIP **Parent:** WIP_DISCRETE_JOBS **Child:** WIP_REQUIREMENT_OPERATIONS **Join:** WIP_ENTITY_ID + ORGANIZATION_ID **Card:** 1 to many **Note:** Component requirements; OPERATION_SEQ_NUM links to the operation **Area:** WIP **Parent:** WIP_ENTITIES **Child:** WIP_TRANSACTIONS **Join:** WIP_ENTITY_ID **Card:** 1 to many **Note:** Resource and overhead transactions **Area:** WIP **Parent:** WIP_ENTITIES **Child:** WIP_MOVE_TRANSACTIONS **Join:** WIP_ENTITY_ID **Card:** 1 to many **Note:** Operation moves **Area:** WIP **Parent:** WIP_ENTITIES **Child:** MTL_MATERIAL_TRANSACTIONS **Join:** WIP_ENTITY_ID = TRANSACTION_SOURCE_ID **Card:** 1 to many **Note:** Only where TRANSACTION_SOURCE_TYPE_ID is the job or schedule source type **Area:** BOM **Parent:** BOM_STRUCTURES_B **Child:** BOM_COMPONENTS_B **Join:** BILL_SEQUENCE_ID **Card:** 1 to many **Note:** Table names in 12.x; BOM_BILL_OF_MATERIALS and BOM_INVENTORY_COMPONENTS remain as synonyms **Area:** BOM **Parent:** MTL_SYSTEM_ITEMS_B **Child:** BOM_STRUCTURES_B **Join:** INVENTORY_ITEM_ID (assembly) + ORGANIZATION_ID **Card:** 1 to many **Note:** One bill per alternate **Area:** BOM **Parent:** MTL_SYSTEM_ITEMS_B **Child:** BOM_COMPONENTS_B **Join:** COMPONENT_ITEM_ID = INVENTORY_ITEM_ID **Card:** 1 to many **Note:** Component item; use the bill organization **Area:** Routing **Parent:** BOM_OPERATIONAL_ROUTINGS **Child:** BOM_OPERATION_SEQUENCES **Join:** ROUTING_SEQUENCE_ID **Card:** 1 to many **Note:** Routing header to operations **Area:** Routing **Parent:** BOM_OPERATION_SEQUENCES **Child:** BOM_OPERATION_RESOURCES **Join:** OPERATION_SEQUENCE_ID **Card:** 1 to many **Note:** Resources per routing operation **Area:** Routing **Parent:** BOM_DEPARTMENTS **Child:** BOM_OPERATION_SEQUENCES **Join:** DEPARTMENT_ID **Card:** 1 to many **Note:** Department performing the operation **Area:** Inventory **Parent:** MTL_PARAMETERS **Child:** MTL_SYSTEM_ITEMS_B **Join:** ORGANIZATION_ID **Card:** 1 to many **Note:** Item master is keyed by item plus organization **Area:** Inventory **Parent:** MTL_SYSTEM_ITEMS_B **Child:** MTL_ONHAND_QUANTITIES_DETAIL **Join:** INVENTORY_ITEM_ID + ORGANIZATION_ID **Card:** 1 to many **Note:** On-hand by subinventory, locator and lot **Area:** Inventory **Parent:** MTL_SYSTEM_ITEMS_B **Child:** MTL_MATERIAL_TRANSACTIONS **Join:** INVENTORY_ITEM_ID + ORGANIZATION_ID **Card:** 1 to many **Note:** Transaction history **Area:** Inventory **Parent:** MTL_PARAMETERS **Child:** MTL_SECONDARY_INVENTORIES **Join:** ORGANIZATION_ID **Card:** 1 to many **Note:** Subinventories per organization **Area:** Organization **Parent:** HR_ALL_ORGANIZATION_UNITS **Child:** MTL_PARAMETERS **Join:** ORGANIZATION_ID **Card:** 1 to 1 **Note:** Inventory organizations are HR organizations with parameters **Notes:** 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. ## Content **Intro:** 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. **Sections:** #### How to read the relationships **Body:** 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. **Bullets:** - 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 **Body:** 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 **Body:** 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 **Body:** 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. **Bullets:** - 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 **Cta:** 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. **Faq:** #### Is there an official Oracle EBS ER diagram? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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. ## Search terms oracle ebs er diagram, oracle ebs entity relationship diagram, oracle ebs table relationships, oracle ebs po tables relationship, oracle ebs wip tables join, oracle ebs bom tables relationship, oracle apps table join conditions, oracle ebs data model --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json