SCM Tables in Oracle Fusion

Share

Introduction

In any implementation of SCM Tables in Oracle Fusion Cloud, understanding the data model is not optional—it’s critical. Whether you are building reports, integrations, or troubleshooting issues, SCM tables form the backbone of how data flows across Procurement, Inventory, Order Management, and Supply Planning.

From a consultant’s perspective, most real-world challenges—like missing transactions, incorrect costing, or integration failures—can be traced back to how data is stored and linked across these tables.

This blog gives you a deep, implementation-focused understanding of SCM tables in Oracle Fusion (26A), covering key tables, relationships, and practical usage scenarios.

What are SCM Tables in Oracle Fusion?

SCM tables in Oracle Fusion are database objects that store transactional and master data for supply chain processes.

Unlike legacy systems where direct DB access was common, in Fusion:

  • Tables are secured and abstracted
  • Access is primarily through:
    • OTBI (Oracle Transactional BI)
    • BI Publisher (BIP)
    • REST APIs
    • Oracle Integration Cloud

However, understanding tables is still essential for:

  • Debugging issues
  • Designing reports
  • Building integrations
  • Data migration validation

Key SCM Modules and Their Core Tables

Let’s break this down module-wise, which is how consultants typically approach real projects.

1. Inventory Management Tables

Inventory is the foundation of SCM. These tables store item, on-hand, and transaction data.

Table NameDescription
INV_MATERIAL_TXNSStores all inventory transactions
INV_ONHAND_QUANTITIES_DETAILTracks on-hand quantities
MTL_SYSTEM_ITEMS_BItem master data
INV_ORG_PARAMETERSInventory organization setup

Consultant Insight:
If a client says “stock is not reflecting correctly,” your first check is:

  • INV_ONHAND_QUANTITIES_DETAIL
  • Then reconcile with INV_MATERIAL_TXNS

2. Procurement Tables

Procurement deals with purchase orders, suppliers, and requisitions.

Table NameDescription
PO_HEADERS_ALLPurchase order header
PO_LINES_ALLPurchase order lines
PO_DISTRIBUTIONS_ALLAccounting distributions
POZ_SUPPLIERSSupplier master

Real Example:
In a project, a PO was approved but not visible in receiving. Root cause:

  • PO_HEADERS_ALL status mismatch
  • Missing distribution in PO_DISTRIBUTIONS_ALL

3. Order Management Tables

These tables handle sales orders and fulfillment.

Table NameDescription
DOO_HEADERS_ALLSales order header
DOO_LINES_ALLSales order lines
DOO_FULFILL_LINES_ALLFulfillment tracking

Consultant Tip:
Always check DOO_FULFILL_LINES_ALL when:

  • Orders are stuck in “Awaiting Shipping”
  • Or fulfillment errors occur

4. Costing Tables

Critical for finance integration and inventory valuation.

Table NameDescription
CST_COST_HISTORYCost history records
CST_ITEM_COSTSItem cost details
CST_TRANSACTION_COSTSTransaction costing

Real Scenario:
Client sees wrong inventory valuation:

  • Validate CST_ITEM_COSTS
  • Compare with INV_MATERIAL_TXNS

5. Receiving Tables

Used during goods receipt process.

Table NameDescription
RCV_SHIPMENT_HEADERSShipment details
RCV_SHIPMENT_LINESLine-level data
RCV_TRANSACTIONSReceiving transactions

Real-World Use Cases of SCM Tables

Use Case 1: Debugging Missing Inventory

Scenario:
User claims stock is not visible.

Approach:

  1. Check INV_MATERIAL_TXNS → Was transaction created?
  2. Check INV_ONHAND_QUANTITIES_DETAIL → Updated or not?
  3. Validate organization in INV_ORG_PARAMETERS

Use Case 2: Purchase Order Not Reflecting in Reports

Scenario:
PO created but not showing in BI report.

Approach:

  • Validate PO_HEADERS_ALL status
  • Check join with PO_LINES_ALL
  • Confirm security via data roles

Use Case 3: Integration Failure in OIC

Using Oracle Integration Cloud:

Scenario:
Sales order integration fails.

Debugging Steps:

  • Check DOO_HEADERS_ALL for record creation
  • Validate payload mapping
  • Compare API vs table data

Architecture / Technical Flow of SCM Tables

In modern Oracle Fusion (26A), the flow is:

  1. User Action (UI / API)
  2. Business Logic Layer (Fusion Services)
  3. Data Stored in Tables
  4. Exposed via:
    • OTBI
    • BIP
    • REST APIs
    • Oracle Cloud Infrastructure services

Important Note:
You cannot directly query tables in SaaS, but:

  • BI Publisher uses data models based on these tables
  • OTBI uses subject areas mapped to tables

Prerequisites to Work with SCM Tables

Before working with SCM tables, ensure:

  • Access to:
    • BI Publisher
    • OTBI
  • Knowledge of:
    • Table relationships
    • Keys (Primary/Foreign)
  • Required roles:
    • BI Administrator
    • SCM Analyst

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

Since direct DB access is restricted, consultants use BIP.

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 Query

Example:

 
SELECT
poh.segment1 PO_NUMBER,
pol.line_num,
pol.item_description
FROM
po_headers_all poh,
po_lines_all pol
WHERE
poh.po_header_id = pol.po_header_id;
 

Step 4 – Save Data Model

  • Validate SQL
  • Save in custom folder

Step 5 – Create Report

  • Use data model
  • Add layout (Excel/PDF)

Testing SCM Table Data

Example Test Scenario

Test Case: Create a Purchase Order

Steps:

  1. Create PO in UI
  2. Run BIP report

Expected Results:

  • Entry in PO_HEADERS_ALL
  • Corresponding lines in PO_LINES_ALL

Validation Checks:

  • Status = Approved
  • Supplier linked correctly

Common Errors and Troubleshooting

1. Data Not Visible in Reports

Cause:

  • Security roles missing

Fix:

  • Assign correct data roles

2. Duplicate Records

Cause:

  • Incorrect joins

Fix:

  • Use proper keys (PO_HEADER_ID)

3. Performance Issues

Cause:

  • Large tables like INV_MATERIAL_TXNS

Fix:

  • Use filters (date, org_id)

4. Missing Transactions

Cause:

  • Background process failure

Fix:

  • Check ESS jobs

Best Practices for Working with SCM Tables

1. Always Use Primary Keys

Avoid joins on:

  • Names
  • Descriptions

Use:

  • IDs (PO_HEADER_ID, INVENTORY_ITEM_ID)

2. Filter Data Efficiently

Bad Practice:

 
SELECT * FROM INV_MATERIAL_TXNS;
 

Good Practice:

 
WHERE transaction_date > SYSDATE – 30;
 

3. Understand Table Relationships

Example:

  • PO_HEADERS_ALL → PO_LINES_ALL → PO_DISTRIBUTIONS_ALL

4. Use OTBI When Possible

  • Faster
  • Secure
  • No SQL required

5. Leverage APIs Instead of Tables for Integrations

In modern implementations:

  • Prefer REST APIs over direct table logic
  • Especially when using Oracle Integration Cloud

Summary

Understanding SCM tables in Oracle Fusion is a foundational skill for any consultant working on real-time implementations.

While Oracle Fusion abstracts database access, table knowledge remains essential for:

  • Troubleshooting issues
  • Designing reports
  • Supporting integrations
  • Validating business transactions

A practical consultant always thinks in terms of:

  • “Which table stores this data?”
  • “What is the relationship?”
  • “How can I validate this issue?”

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

FAQs

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

No, direct database access is restricted. You must use:

  • BI Publisher
  • OTBI
  • APIs

2. Which table is most important in Inventory?

INV_MATERIAL_TXNS is critical as it stores all inventory transactions.

3. How do integrations use SCM data?

Integrations typically use REST APIs or services exposed via
Oracle Integration Cloud instead of directly accessing tables.


Share

Leave a Reply

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