Oracle EBS BOM Interface Load Worksheet
Loading bills of material into Oracle EBS is usually done through the BOM open interface tables. This worksheet helps you stage and sanity-check component rows in Excel before they ever reach the interface.
| ASSEMBLY_ITEM_NUMBER | ORGANIZATION_CODE | COMPONENT_ITEM_NUMBER | ITEM_NUM | OPERATION_SEQ_NUM | COMPONENT_QUANTITY | Unit cost (check) | Extended cost | TRANSACTION_TYPE | EFFECTIVITY_DATE | |
|---|---|---|---|---|---|---|---|---|---|---|
$43 | ||||||||||
$19 | ||||||||||
$6 | ||||||||||
$3 | ||||||||||
$2 |
Component columns follow BOM_INVENTORY_COMPS_INTERFACE and the header columns follow BOM_BILL_OF_MTLS_INTERFACE. The load also needs PROCESS_FLAG set to 1 and a batch or group identifier, then you run the Bill and Routing import program. Column requirements vary by release, so validate in a test instance. The unit cost column is only a local check and is not loaded.
BOM conversion review
Optional: we can review your conversion mapping and test load plan for Oracle EBS.
Optional. The tool and every export work without this. No spam.
How to use this worksheet
Replace the sample rows with your assembly, organization and component data. Choose CREATE for new components, UPDATE or DELETE for changes. Use the extended cost column and totals to spot a component with a wildly wrong quantity before load. Export to Excel, convert to a CSV or SQL loader file, and populate the interface tables through your standard conversion process.
Header and component tables
The interface has a header table for the bill itself and a component table for each line. Rows in both are matched by assembly, organization and a transaction identifier. After loading with PROCESS_FLAG set to pending, run the Bills and Routings import program, then check the error table for rows that failed validation and fix them in the staging data.
- Header: BOM_BILL_OF_MTLS_INTERFACE
- Components: BOM_INVENTORY_COMPS_INTERFACE
- Errors: MTL_INTERFACE_ERRORS
- Run the import program after the load
Pre-load validation tips
Confirm that every assembly and component already exists as an item in the target organization with BOM Allowed enabled. Check that operation sequence numbers match the routing if one exists, and that the item sequence numbers are unique within the bill. Look for duplicate component lines and zero quantities, which are the most common reasons for rejected rows.
Common mistakes
Loading components before the items are assigned to the organization causes mass failures. Reusing the same effectivity date for engineering and production changes can create overlapping effectivity. Finally, running an import twice without clearing earlier rows duplicates components. Always reconcile counts after load.
Frequently Asked Questions
Which tables does the Oracle BOM open interface use?
The header goes into BOM_BILL_OF_MTLS_INTERFACE and components into BOM_INVENTORY_COMPS_INTERFACE. Revisions and routing have their own interface tables. Errors are recorded in MTL_INTERFACE_ERRORS.
What does ACD_TYPE do?
It marks whether a component row adds, changes or disables a component through a change order. For a plain bill load the TRANSACTION_TYPE column such as CREATE or UPDATE is the main control.
Can I load a multi-level BOM?
Yes. Each level is its own bill with its own assembly. Load the child assemblies first or in the same batch so that every referenced item and bill exists.
How do I check what failed?
Query MTL_INTERFACE_ERRORS for the transaction identifiers of your batch. Fix the staging data and reset the rows to pending before running the import again.
If you are migrating bills of material from SyteLine, Baan or a legacy system into Oracle, we can help build and test the conversion scripts.
Related Tools
Oracle EBS Item Attribute Template Worksheet
Plan item master attribute values for a new item template, with the underlying MTL_SYSTEM_ITEMS_B column for each attribute.
OracleOracle EBS WIP Accounting Class Reference
Understand Oracle WIP accounting class types and which valuation and variance accounts each class uses.
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.
OracleOracle EBS MRP Exception Messages Reference
What each Oracle MRP exception message means, why it appears and what a planner usually does about it.
More Oracle tools
See all- Oracle EBS Discrete Job Statuses Reference
- Oracle EBS Inventory Transaction Types Reference
- Oracle EBS Inventory Period Close Checklist
- Oracle EBS Cycle Count Setup Worksheet
- Oracle EBS WIP Job Close Variance Worksheet
- Oracle EBS Routing and Operations Load Worksheet
- Oracle EBS Inventory Organization Parameters Setup Template
- Oracle EBS Item Cost Update Request Form
- Oracle EBS Engineering Change Order Request Template
New to Oracle E-Business Suite? Start with the Oracle E-Business Suite guide and toolkit, or plan with Oracle EBS Roadmap Planner, Training Path Builder, ERP Certification Study Planner.
Keep exploring
Free tools
- Oracle EBS AP Invoice Interface TemplateFill in header and line values to get insert statements for the Payables open interface tables, ready to adapt for your conversion or integration.
- 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
- Data Conversion in Oracle EBS: Interface Tables, Open Interfaces and ReconciliationHow data conversion works in Oracle EBS: extract, stage, validate, load through interface tables and APIs, then reconcile. Practical steps and common failures.
- How to Import AP Invoices in Oracle EBS with Payables Open Interface ImportStep-by-step guide to importing AP invoices into Oracle EBS Payables using the AP_INVOICES_INTERFACE tables and the Payables Open Interface Import program.
- Fix Oracle EBS MTL_TRANSACTIONS_INTERFACE Rows Stuck With PROCESS_FLAG = 3Inventory rows stuck in MTL_TRANSACTIONS_INTERFACE with PROCESS_FLAG 3? Read ERROR_CODE and ERROR_EXPLANATION, fix the cause, and resubmit from the Pending Transactions form.
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.
- Loftware Integration for Oracle EBS: WMS Label RequestsHow the Loftware Integration - Oracle EBS component connects Oracle WMS label requests to Loftware Cloud, Enterprise SP or NiceLabel LMS, plus setup.
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 automate email-to-transaction in Oracle E-Business SuiteTurn inbound POs, RFQs, invoices and supplier confirmations in shared mailboxes into Oracle EBS transactions through open interfaces, with review and audit.
- 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.