Oracle Fusion SCM Tables And Views
Oracle Fusion SCM Tables And Views
Introduction
Oracle Fusion SCM Tables and Views are a critical foundation for anyone working on reporting, integrations, data migration, or troubleshooting within Oracle Fusion Supply Chain Management. In real-world implementations, consultants rarely rely only on UI screens—understanding backend tables and views becomes essential for solving complex business problems, especially when working with BI Publisher, OTBI, or Oracle Integration Cloud (OIC Gen 3).
In Oracle Fusion Cloud (26A), direct database access is restricted, but Oracle provides secure access through BI Views, OTBI subject areas, and BICC extracts. Still, knowing the underlying table structure helps you design accurate reports, debug issues, and build scalable integrations.
This article provides a practical, implementation-focused deep dive into Oracle Fusion SCM tables and views, covering procurement, inventory, order management, and more.
What are Oracle Fusion SCM Tables and Views?
Oracle Fusion SCM Tables and Views represent the backend data model that stores all transactional and master data related to supply chain operations.
Tables
- Physical storage objects in Oracle database
- Store transactional and master data
- Example:
PO_HEADERS_ALL,MTL_SYSTEM_ITEMS_B
Views
- Logical representations of data (built on tables)
- Used for reporting and secure access
- Example:
PO_HEADERS_V,INV_ITEM_VIEW
Key Difference (Consultant Perspective)
| Aspect | Tables | Views |
|---|---|---|
| Data Storage | Physical | Logical |
| Access | Restricted in Fusion | Accessible via BIP/OTBI |
| Use Case | Backend logic | Reporting, integrations |
👉 In Fusion Cloud, you primarily work with views, but understanding tables helps you troubleshoot and design better solutions.
Key Features of Oracle Fusion SCM Data Model
1. Modular Data Structure
Each SCM module has its own set of tables:
- Procurement
- Inventory
- Order Management
- Shipping
- Costing
2. Multi-Org Architecture
- Data is stored using Business Units (BU), Inventory Orgs, Legal Entities
- Columns like
ORG_ID,BUSINESS_UNIT_IDare critical
3. Audit and History Tracking
- WHO columns:
CREATED_BYCREATION_DATELAST_UPDATED_BY
4. Secure Data Access
- Data accessed via:
- BI Publisher Data Models
- OTBI Subject Areas
- BICC Extracts
Core Oracle Fusion SCM Tables and Views by Module
Procurement Tables
| Table Name | Description |
|---|---|
| PO_HEADERS_ALL | Purchase Order Header |
| PO_LINES_ALL | PO Line Details |
| PO_DISTRIBUTIONS_ALL | Accounting Distribution |
| PO_ACTION_HISTORY | Approval History |
👉 Example: If a PO is stuck in approval, check PO_ACTION_HISTORY.
Inventory Tables
| Table Name | Description |
|---|---|
| MTL_SYSTEM_ITEMS_B | Item Master |
| MTL_ONHAND_QUANTITIES | On-hand Inventory |
| INV_ORGANIZATION_DEFINITIONS | Inventory Org Details |
👉 Used heavily in inventory reconciliation and stock reporting.
Order Management Tables
| Table Name | Description |
|---|---|
| DOO_HEADERS_ALL | Sales Order Header |
| DOO_LINES_ALL | Sales Order Lines |
| DOO_FULFILL_LINES_ALL | Fulfillment Details |
👉 Critical for troubleshooting order lifecycle issues.
Shipping Tables
| Table Name | Description |
|---|---|
| WSH_DELIVERY_DETAILS | Shipping Lines |
| WSH_NEW_DELIVERIES | Delivery Info |
Costing Tables
| Table Name | Description |
|---|---|
| CST_COST_HISTORY | Cost Records |
| CST_ITEM_COSTS | Item Costing |
Real-World Implementation Use Cases
Use Case 1: Custom Procurement Report
Scenario:
Client needs a report showing:
- PO Number
- Supplier
- Amount
- Approval Status
Solution:
- Use:
PO_HEADERS_ALLPO_LINES_ALLPO_ACTION_HISTORY
- Build BI Publisher Data Model
Use Case 2: Inventory Reconciliation
Scenario:
Mismatch between system stock and physical stock.
Solution:
- Query:
MTL_ONHAND_QUANTITIESMTL_SYSTEM_ITEMS_B
- Validate against inventory org
Use Case 3: Order Fulfillment Tracking via OIC Gen 3
Scenario:
Integrate order data with external warehouse system.
Solution:
- Extract from:
DOO_HEADERS_ALLDOO_FULFILL_LINES_ALL
- Use REST APIs or BICC
- Integrate using OIC Gen 3
Architecture / Technical Flow
How Data Flows in Oracle Fusion SCM
- User creates transaction (UI/API)
- Data stored in base tables
- Views expose data securely
- Reporting tools consume views:
- OTBI
- BI Publisher
- BICC
Example Flow (Purchase Order)
UI → PO_HEADERS_ALL → View → BI Report
Prerequisites
Before working with SCM tables and views:
- Access to Fusion Application
- BI Publisher Role (BI Administrator / Developer)
- Knowledge of:
- SQL
- Data relationships
- Understanding of SCM modules
Step-by-Step: Using SCM Tables in BI Publisher
Step 1 – Navigate to BI Publisher
Navigator → Tools → Reports and Analytics
Step 2 – Create Data Model
- Click: Create → Data Model
- Select: SQL Query
Step 3 – Write SQL Query
Example:
poh.segment1 AS PO_NUMBER,
poh.type_lookup_code,
poh.authorization_status,
pol.line_num,
pol.quantity
FROM
po_headers_all poh,
po_lines_all pol
WHERE
poh.po_header_id = pol.po_header_id
Step 4 – Define Parameters (Optional)
Example:
- Business Unit
- Date Range
Step 5 – Save Data Model
- Validate query
- Save
Step 6 – Create Report
- Use Data Model
- Design layout (RTF/XPT)
Testing the Technical Component
Test Scenario: Purchase Order Report
Input:
- BU = Vision Operations
- Date Range = Last 30 Days
Expected Output:
- List of POs
- Correct status (Approved/Pending)
Validation Checks
- Compare with UI data
- Validate totals
- Check missing records
Common Errors and Troubleshooting
1. No Data Returned
Cause:
- Wrong joins
- Missing BU filter
Solution:
- Validate joins
- Add organization filters
2. Performance Issues
Cause:
- Large tables
- No indexing
Solution:
- Use filters
- Avoid full table scans
3. Data Security Issues
Cause:
- Role-based access
Solution:
- Assign correct BI roles
4. Incorrect Data
Cause:
- Using wrong tables instead of views
Solution:
- Use validated views or subject areas
Best Practices
1. Always Use Views Where Possible
Avoid direct table usage unless necessary.
2. Understand Data Relationships
Example:
- PO Header → PO Lines → Distributions
3. Use Aliases in Queries
Improves readability and debugging.
4. Filter Data Early
Apply BU, Org filters in SQL.
5. Validate Against UI
Always cross-check report output.
6. Use BICC for Large Data Extracts
Avoid heavy BI queries for bulk data.
Real Consultant Tips
- Always keep a data dictionary document for projects
- Maintain join logic templates
- Use sample queries for reuse
- Debug issues using WHO columns
- For integrations, prefer REST APIs over direct table extraction
Frequently Asked Questions (FAQs)
1. Can we access Oracle Fusion SCM tables directly?
No. Direct database access is restricted. Use:
- BI Publisher
- OTBI
- BICC
2. What is the difference between OTBI and BI Publisher?
| OTBI | BI Publisher |
|---|---|
| Real-time reporting | Scheduled reporting |
| Drag-and-drop | SQL-based |
| Limited customization | Highly customizable |
3. Which tables are most commonly used in SCM?
PO_HEADERS_ALLMTL_SYSTEM_ITEMS_BDOO_HEADERS_ALLWSH_DELIVERY_DETAILS
Summary
Understanding Oracle Fusion SCM Tables and Views is a game-changer for consultants working on reporting, integrations, and troubleshooting. While Fusion Cloud abstracts database access, the underlying data model still drives everything.
From procurement to inventory and order management, knowing which tables store what data—and how they relate—helps you:
- Build accurate reports
- Troubleshoot production issues
- Design efficient integrations using OIC Gen 3
- Deliver better solutions to clients
As a consultant, this knowledge separates basic users from true solution architects.
For deeper reference, always consult the official Oracle documentation:
https://docs.oracle.com/en/cloud/saas/index.html