SCM Tables in Oracle Fusion

Share

Tables for SCM in Oracle Fusion: A Practical Guide for Consultants

When working with Tables for SCM in Oracle Fusion, every functional or technical consultant eventually reaches a point where understanding backend data becomes critical. Whether you are debugging transactions, building reports, or integrating with external systems, knowing how SCM tables work in Oracle Fusion Cloud can significantly improve your efficiency and problem-solving ability.

In real implementations, I’ve seen many consultants struggle—not because they don’t understand the functional flows, but because they lack clarity on how data is stored and linked across modules. This blog will bridge that gap with a practical, implementation-focused approach.

What are SCM Tables in Oracle Fusion?

SCM tables in Oracle Fusion represent the backend database structure that stores transactional and master data for Supply Chain Management modules.

Unlike legacy systems, Oracle Fusion uses a secured cloud database architecture, where:

  • Direct database access is restricted
  • Tables are accessed through:
    • OTBI (Oracle Transactional Business Intelligence)
    • BI Publisher (BIP)
    • REST/SOAP APIs
    • Data extracts (BICC)

These tables are grouped by functional areas like:

  • Procurement
  • Inventory
  • Order Management
  • Shipping
  • Manufacturing

Key Features of SCM Tables in Oracle Fusion

Understanding SCM tables is not just about table names—it’s about how Oracle designed data relationships.

1. Modular Data Structure

Each SCM module has its own schema but shares common reference tables.

2. Use of Base Tables and Views

  • _B tables → Base tables (store core data)
  • _TL tables → Translation tables (multi-language support)
  • _VL views → Combine base + translation data

3. Multi-Org Data Model

Fusion uses:

  • Business Units
  • Inventory Organizations
  • Legal Entities

So most tables include:

  • ORGANIZATION_ID
  • BUSINESS_UNIT_ID

4. Audit and History Tracking

Many tables include:

  • CREATION_DATE
  • LAST_UPDATED_BY

Key SCM Tables by Module

Let’s break this down the way we do in real projects.

Procurement Tables

Table NameDescription
PO_HEADERS_ALLStores purchase order header details
PO_LINES_ALLStores PO line details
PO_DISTRIBUTIONS_ALLAccounting distributions
PO_SUPPLIERSSupplier master data
AP_SUPPLIERSSupplier information (shared with Payables)

Consultant Insight:
When a PO is stuck in approval, always check PO_HEADERS_ALL and status columns.

Inventory Tables

Table NameDescription
MTL_SYSTEM_ITEMS_BItem master
MTL_PARAMETERSOrganization parameters
MTL_ONHAND_QUANTITIESOn-hand stock
MTL_MATERIAL_TRANSACTIONSInventory transactions

Real Tip:
If stock is not reflecting correctly in UI, validate MTL_ONHAND_QUANTITIES.

Order Management Tables

Table NameDescription
DOO_HEADERS_ALLSales order header
DOO_LINES_ALLSales order lines
DOO_FULFILL_LINES_ALLFulfillment details

Shipping Tables

Table NameDescription
WSH_DELIVERY_DETAILSShipment line details
WSH_NEW_DELIVERIESDelivery header
WSH_DELIVERY_ASSIGNMENTSDelivery assignments

Manufacturing Tables

Table NameDescription
WIP_DISCRETE_JOBSWork orders
WIP_OPERATIONSOperation details
WIP_REQUIREMENT_OPERATIONSMaterial requirements

Real-World Business Use Cases

Let’s look at how SCM tables are actually used in implementations.

Use Case 1: Debugging Missing Inventory

Scenario:
Client reports that stock is not visible in UI.

Approach:

  • Query MTL_ONHAND_QUANTITIES
  • Validate:
    • ORGANIZATION_ID
    • SUBINVENTORY_CODE

Outcome:
Issue traced to wrong subinventory setup.

Use Case 2: PO Data Extraction for Integration

Scenario:
Integrate Fusion with a third-party procurement system.

Tables Used:

  • PO_HEADERS_ALL
  • PO_LINES_ALL

Result:
Data mapped via BIP and exposed through OIC Gen 3 integration.

Use Case 3: Order Fulfillment Tracking

Scenario:
Client wants real-time order tracking dashboard.

Tables Used:

  • DOO_HEADERS_ALL
  • DOO_LINES_ALL
  • WSH_DELIVERY_DETAILS

Outcome:
Custom OTBI report created for tracking order lifecycle.

Configuration Overview (Before Using Tables)

Even though tables are backend objects, you must ensure proper configuration:

  • Business Units created
  • Inventory Organizations configured
  • Items defined in Product Information Management (PIM)
  • Suppliers and customers created
  • Document sequences enabled

Step-by-Step: Accessing SCM Data via BI Publisher

Since direct DB access is restricted, here’s how consultants actually use tables.

Step 1 – Navigate to BI Publisher

Navigation:

Navigator → Tools → Reports and Analytics

Step 2 – Create Data Model

  • Click Create → Data Model
  • Choose SQL Query

Step 3 – Write Query

Example:

 
SELECT
pha.segment1 PO_NUMBER,
pla.line_num,
pla.quantity
FROM
po_headers_all pha,
po_lines_all pla
WHERE
pha.po_header_id = pla.po_header_id;
 

Step 4 – Save and Validate

  • Click Save
  • Click Data → View Data

Step 5 – Create Report

  • Use the data model
  • Generate output in Excel/PDF

Testing the Setup

Example Test Scenario

Create a Purchase Order:

  • Supplier: ABC Corp
  • Item: Laptop
  • Quantity: 10

Validation Steps

  • Query PO_HEADERS_ALL
  • Verify:
    • PO number exists
    • Status is correct

Expected Result

  • Data reflects immediately in backend tables

Common Implementation Challenges

1. No Direct Database Access

Consultants must rely on:

  • BIP
  • OTBI
  • APIs

2. Complex Joins

Tables are highly normalized.

Example:

  • PO requires joins across 3–5 tables

3. Security Restrictions

Data is filtered based on:

  • Roles
  • Data access policies

4. Version Differences

Table structures may evolve slightly with quarterly updates (like 26A)

Best Practices from Real Projects

1. Always Use Views (_VL)

Avoid base tables unless necessary.

2. Maintain SQL Repository

Keep reusable queries for:

  • POs
  • Inventory
  • Orders

3. Use Aliases Properly

Improves readability in complex joins.

4. Validate Data with UI

Never rely only on backend query.

5. Use BICC for Large Data Extraction

Avoid performance issues in BIP.

Architecture / Technical Flow

Here’s how SCM data flows:

  1. User performs transaction in UI
  2. Data stored in base tables
  3. Indexed and secured via views
  4. Accessed through:
    • OTBI
    • BIP
    • APIs
  5. Integrated via OIC Gen 3

Frequently Asked Interview Questions

1. What are PO_HEADERS_ALL and PO_LINES_ALL?

They store purchase order header and line data.

2. Difference between _B and _TL tables?

  • _B → Base data
  • _TL → Translated data

3. What is MTL_SYSTEM_ITEMS_B?

Stores item master data.

4. How do you extract SCM data?

Using BIP, OTBI, or APIs.

5. What is DOO_HEADERS_ALL?

Sales order header table.

6. Why can’t we access tables directly?

Due to SaaS security restrictions.

7. What is WSH_DELIVERY_DETAILS?

Stores shipment line details.

8. How do you debug inventory issues?

Check MTL_ONHAND_QUANTITIES.

9. What is the role of ORGANIZATION_ID?

Defines inventory organization.

10. What tools are used for reporting?

OTBI and BI Publisher.

11. What is BICC?

Bulk data extraction tool.

12. How are tables linked?

Using primary and foreign keys.

13. What is a fulfillment line?

Represents execution of order line.

14. What is WIP_DISCRETE_JOBS?

Manufacturing work orders.

15. How do you optimize queries?

Use indexed columns and avoid unnecessary joins.

Real Implementation Scenarios

Scenario 1: Data Migration

  • Extract legacy data
  • Map to Fusion tables
  • Load via FBDI/HDL

Scenario 2: Integration with External Warehouse

  • Use OIC Gen 3
  • Fetch inventory from tables
  • Sync with WMS

Scenario 3: Custom Reporting

  • Build BIP reports
  • Join multiple SCM tables
  • Deliver business insights

Expert Tips

  • Learn table relationships, not just names
  • Practice writing SQL queries daily
  • Use Oracle documentation as reference
  • Always test queries with small datasets first
  • Understand functional flow before querying tables

Summary

Understanding Tables for SCM in Oracle Fusion is a game-changer for any consultant. It allows you to:

  • Debug issues faster
  • Build accurate reports
  • Design better integrations
  • Gain deeper system insight

In real-world projects, consultants who understand backend tables are always more effective and confident.

For deeper reference, always explore official Oracle documentation:
https://docs.oracle.com/en/cloud/saas/index.html

FAQs

1. Can we directly access SCM tables in Oracle Fusion?

No, direct DB access is restricted. Use BIP, OTBI, or APIs.

2. Which table is used for item master?

MTL_SYSTEM_ITEMS_B.

3. What is the best way to learn SCM tables?

Practice SQL queries using BI Publisher and relate them to UI transactions.


Share

Leave a Reply

Your email address will not be published. Required fields are marked *