Introduction
In real Oracle Fusion SCM implementations, understanding Oracle Fusion SCM table names is not just a technical skill—it’s a critical capability for reporting, integrations, data validation, and troubleshooting. Whether you are building OTBI reports, BIP extracts, or working with REST/SOAP APIs via Oracle Integration Cloud (OIC Gen 3), knowing the underlying data model gives you a strong edge as a consultant.
In Oracle Fusion Cloud (Release 26A and beyond), Oracle has abstracted database access, but table knowledge is still essential for:
- Writing custom BI Publisher reports
- Debugging data issues
- Understanding API payload structures
- Supporting data migrations
This blog will walk you through Oracle Fusion SCM table names, how they are structured, where they are used, and how to practically work with them in real-world projects.
What are Oracle Fusion SCM Table Names?
Oracle Fusion SCM table names refer to the database objects that store transactional and master data in Supply Chain Management modules.
Unlike legacy systems like Oracle E-Business Suite, direct database access is restricted in Fusion SaaS. However, Oracle still exposes:
- Tables (via BI tools like BIP)
- Views (secured data layers)
- Subject areas (OTBI abstraction)
Key Point:
You don’t directly query tables in production—but you use table knowledge to design reports and integrations effectively.
Key SCM Modules and Their Core Tables
Let’s break down Oracle Fusion SCM table names module-wise.
1. Inventory Management Tables
| Table Name | Description |
|---|---|
| INV_MATERIAL_TXNS | Stores all material transactions |
| INV_ONHAND_QUANTITIES_DETAIL | On-hand inventory quantities |
| INV_ITEMS_B | Item master base table |
| INV_ORG_PARAMETERS | Inventory organization setup |
Consultant Insight:
When a client says:
“Stock is not matching in reports”
You always start with:
INV_ONHAND_QUANTITIES_DETAIL- Cross-check with
INV_MATERIAL_TXNS
2. Procurement (Purchasing) Tables
| Table Name | Description |
|---|---|
| PO_HEADERS_ALL | Purchase order header |
| PO_LINES_ALL | Purchase order lines |
| PO_DISTRIBUTIONS_ALL | Accounting distributions |
| PO_SUPPLIERS | Supplier master data |
Real Scenario:
While building a custom PO approval report:
- Header →
PO_HEADERS_ALL - Lines →
PO_LINES_ALL - Supplier →
PO_SUPPLIERS
3. Order Management Tables
| Table Name | Description |
|---|---|
| DOO_HEADERS_ALL | Sales order header |
| DOO_LINES_ALL | Sales order lines |
| DOO_FULFILL_LINES_ALL | Fulfillment details |
Consultant Tip:
For tracking order lifecycle:
- Start with
DOO_HEADERS_ALL - Drill into
DOO_FULFILL_LINES_ALL
4. Shipping Tables
| Table Name | Description |
|---|---|
| WSH_DELIVERY_DETAILS | Shipment line details |
| WSH_NEW_DELIVERIES | Delivery header |
| WSH_DELIVERY_ASSIGNMENTS | Delivery assignments |
5. Product Information Management (PIM)
| Table Name | Description |
|---|---|
| EGP_SYSTEM_ITEMS_B | Item master |
| EGP_ITEM_REVISIONS_B | Item revisions |
| EGP_ITEM_CATEGORIES | Item categories |
6. Cost Management Tables
| Table Name | Description |
|---|---|
| CST_COST_DETAILS | Cost elements |
| CST_ITEM_COSTS | Item cost |
| CST_COST_DISTRIBUTIONS | Cost accounting |
Real-World Integration Use Cases
Use Case 1: OIC Integration for Sales Orders
In a real project:
- API payload maps to:
DOO_HEADERS_ALLDOO_LINES_ALL
When debugging:
- Compare API payload vs table data
Use Case 2: Inventory Reconciliation Report
Client Issue:
“On-hand stock mismatch between system and warehouse”
Solution:
- Query
INV_ONHAND_QUANTITIES_DETAIL - Compare with transaction history in
INV_MATERIAL_TXNS
Use Case 3: Procurement Spend Analysis
Custom BIP report built using:
PO_HEADERS_ALLPO_LINES_ALLPO_DISTRIBUTIONS_ALL
Result:
- Accurate spend visibility across business units
Architecture / Technical Flow
In Fusion SCM, table usage follows this flow:
→ Business Logic Layer
→ Database Tables
→ BI / OTBI / APIs expose data
Important Layers:
- Base Tables (e.g., PO_HEADERS_ALL)
- Secure Views
- OTBI Subject Areas
- REST APIs
Key Insight:
You never directly expose tables—always use:
- OTBI
- BI Publisher
- REST APIs
Prerequisites for Working with SCM Tables
Before using Oracle Fusion SCM table names:
- Access to BI Publisher (BIP)
- Understanding of data roles and security
- Knowledge of SQL joins
- Familiarity with module flows (Procurement, Inventory, OM)
Step-by-Step: Using Table Names in BI Publisher
Step 1 – Navigate to BI Publisher
Navigator → Tools → Reports and Analytics
Step 2 – Create Data Model
- Click Create → Data Model
- Choose SQL Query
Step 3 – Write Query
Example:
poh.segment1 AS PO_NUMBER,
pol.line_num,
pol.quantity,
sup.vendor_name
FROM
po_headers_all poh,
po_lines_all pol,
po_suppliers sup
WHERE
poh.po_header_id = pol.po_header_id
AND poh.vendor_id = sup.vendor_id;
Step 4 – Save and Validate
- Click Save
- Click Data → View Data
Step 5 – Create Report
- Use the data model
- Design layout (Excel/PDF)
Testing the Technical Component
Example Test Scenario
Requirement: Fetch PO details
Steps:
- Run BIP report
- Enter PO number
- Validate:
- Header matches
PO_HEADERS_ALL - Lines match
PO_LINES_ALL - Supplier matches
PO_SUPPLIERS
Expected Output:
| PO Number | Line | Quantity | Supplier |
|---|
Common Errors and Troubleshooting
1. No Data Returned
Cause:
- Missing joins
- Incorrect org_id
Fix:
- Always filter by
PRC_BU_IDorORG_ID
2. Duplicate Records
Cause:
- Joining header with multiple distributions
Fix:
- Use proper join conditions
- Apply aggregation if needed
3. Security Issues
Error:
You do not have permission
Fix:
- Check data roles
- Use secured views instead of base tables
4. Performance Issues
Cause:
- Large tables (e.g., INV_MATERIAL_TXNS)
Fix:
- Add filters
- Avoid full table scans
Best Practices for Working with SCM Tables
1. Always Start with Business Flow
Don’t jump into tables. Understand:
- Procurement flow
- Order lifecycle
- Inventory transactions
2. Use OTBI First
If requirement is simple:
- Use OTBI instead of BIP
3. Avoid Direct Table Dependency
Oracle may:
- Change structures in upgrades (26A+)
Always validate after updates.
4. Use Aliases in Queries
Example:
Improves readability.
5. Filter by Business Unit
Almost every table requires:
PRC_BU_IDORG_ID
6. Document Your Data Model
In real projects:
- Maintain mapping documents
- Helps during support phase
Real Implementation Scenario (End-to-End)
Scenario: Custom Procurement Dashboard
Client Requirement:
- PO status
- Supplier performance
- Spend analysis
Tables Used:
PO_HEADERS_ALLPO_LINES_ALLPO_DISTRIBUTIONS_ALLPO_SUPPLIERS
Solution:
- Created BIP Data Model
- Joined all tables
- Built dashboard in OTBI
Outcome:
- Real-time procurement insights
- Reduced manual reporting effort
Frequently Asked Questions (FAQs)
1. Can we directly access Oracle Fusion SCM tables?
No. Direct database access is restricted. You access data via:
- BI Publisher
- OTBI
- REST APIs
2. What is the difference between tables and views in Fusion?
- Tables → Store raw data
- Views → Secure and filtered data
Always prefer views in reports.
3. How do I find table names in Oracle Fusion?
Options:
- Oracle documentation
- BI subject areas
- Existing BIP reports
- SQL developer (limited environments)
Summary
Understanding Oracle Fusion SCM table names is a foundational skill for any consultant working on reporting, integrations, or troubleshooting.
Key takeaways:
- Tables like
PO_HEADERS_ALL,INV_MATERIAL_TXNS, andDOO_HEADERS_ALLare core to SCM - Always align table usage with business processes
- Use BI Publisher and OTBI instead of direct access
- Validate joins, security, and performance
In real projects, this knowledge helps you move from a beginner to a solution-driven consultant who can debug issues and deliver accurate reporting.
For deeper reference, always consult official Oracle documentation:
https://docs.oracle.com/en/cloud/saas/index.html