Oracle Fusion SCM Tables Guide

Introduction

When working with Oracle Fusion SCM Tables, understanding the underlying data model is critical for reporting, integrations, and troubleshooting. As consultants working on Oracle Fusion Cloud SCM projects, we often deal with business users who rely on reports or integrations—but behind every report or API lies a table structure that drives everything.

In real-world implementations, whether you’re building OTBI reports, BI Publisher reports, or integrations using Oracle Integration Cloud, knowing the right tables can save hours of debugging and design effort.

This blog will give you a deep, practical understanding of Oracle Fusion SCM tables, focusing on real implementation usage rather than just theoretical definitions.

What are Oracle Fusion SCM Tables?

Oracle Fusion SCM Tables are database objects stored in the Fusion SaaS environment that hold transactional and master data related to supply chain processes.

Unlike legacy systems like EBS, you don’t get direct database access. Instead, these tables are accessed via:

• OTBI (Oracle Transactional Business Intelligence)
• BI Publisher (BIP)
• REST / SOAP APIs
• Data extracts (BICC)
• HDL / FBDI (for loading data)

These tables are organized into functional modules, such as:

Key Oracle Fusion SCM Tables by Module

Let’s go module by module with important tables consultants use daily.

1. Inventory Tables

7

Inventory is one of the most frequently accessed areas.

Key Tables:

• EGP_SYSTEM_ITEMS_B → Item Master
• INV_ORGANIZATION_DEFINITIONS → Inventory Organizations
• INV_ONHAND_QUANTITIES_DETAIL → On-hand stock
• INV_MATERIAL_TXNS → Inventory transactions

Real-world example:

In a pharma client project, we used:

• EGP_SYSTEM_ITEMS_B to extract item details
• INV_ONHAND_QUANTITIES_DETAIL to build a stock dashboard

This helped business users track batch-level inventory.

2. Procurement Tables

8

Procurement is heavily used in integrations and reporting.

Key Tables:

• PO_HEADERS_ALL → Purchase Order Header
• PO_LINES_ALL → PO Lines
• PO_DISTRIBUTIONS_ALL → Accounting distributions
• POZ_SUPPLIERS → Supplier master

Real-world example:

In an OIC integration, we extracted PO data using:

• Header → PO_HEADERS_ALL
• Lines → PO_LINES_ALL

This was used to integrate with a third-party logistics system.

3. Order Management Tables

6

Order Management is critical for sales processing.

Key Tables:

• DOO_HEADERS_ALL → Sales Order Header
• DOO_LINES_ALL → Sales Order Lines
• DOO_FULFILL_LINES_ALL → Fulfillment details

Real-world example:

During a telecom implementation:

• We used DOO_HEADERS_ALL for order tracking
• Combined with fulfillment tables to track delivery status

4. Shipping Tables

7

Shipping helps track logistics and deliveries.

Key Tables:

• WSH_DELIVERY_DETAILS → Shipment details
• WSH_NEW_DELIVERIES → Delivery headers
• WSH_DEL_ASSIGNMENTS → Delivery assignments

Real-world example:

Used for generating dispatch reports in BI Publisher showing:

• Delivery number
• Shipment status
• Customer location

5. Product Management Tables

6

These tables manage item lifecycle and attributes.

Key Tables:

• EGP_SYSTEM_ITEMS_B → Item master
• EGP_ITEM_CATEGORIES → Item categories
• EGP_CATEGORIES_B → Category definitions

Real-world example:

In a retail client:

• We mapped items to categories using EGP_ITEM_CATEGORIES
• Used this in pricing and analytics dashboards

Real-World Integration Use Cases

Use Case 1: OIC Integration for Purchase Orders

Using Oracle Integration Cloud:

• Extract PO data from tables (via BIP)
• Transform JSON payload
• Send to external ERP

Use Case 2: Inventory Dashboard

Using OTBI:

• Fetch data from INV_ONHAND_QUANTITIES_DETAIL
• Combine with item master
• Display stock availability

Use Case 3: Order Status Tracking

• Use DOO_HEADERS_ALL + fulfillment tables
• Provide real-time order tracking to customers

Architecture / Technical Flow

Here’s how Oracle Fusion SCM Tables are accessed in real implementations:

1. Data stored in SCM tables
2. Access layer:
   • OTBI
   • BI Publisher
   • REST APIs
3. Integration layer:
   • Oracle Integration Cloud
4. External systems:
   • WMS
   • Logistics
   • Finance

Prerequisites

Before working with SCM tables:

• Access to Fusion instance
• BI Publisher / OTBI roles
• Knowledge of:
  • SQL joins
  • SCM business processes
• Basic understanding of:
  • Item structure
  • Procurement lifecycle
  • Order lifecycle

Step-by-Step: Building a BI Report Using SCM Tables

Step 1 – Navigate to BI Publisher

Navigator → Tools → Reports and Analytics

Step 2 – Create Data Model

• Choose SQL Query
• Example:

Step 3 – Create Report Layout

• Use RTF template
• Map fields

Step 4 – Save and Run

• Validate output
• Export to Excel/PDF

Testing the Technical Component

Test Scenario:

• Create a Purchase Order in Fusion
• Run the BI report

Expected Result:

• PO number displayed
• Line details visible
• Quantities accurate

Validation Checks:

• Data matches UI
• No missing joins
• Correct filters applied

Common Errors and Troubleshooting

Best Practices

1. Always Use Correct Joins

Example:

• PO_HEADERS_ALL → PO_LINES_ALL via PO_HEADER_ID

2. Avoid Direct Table Dependency in Integrations

Instead:

• Use APIs when possible
• Use BIP extracts when needed

3. Use Filters for Performance

4. Validate with Business Users

Always cross-check:

• Report output
• UI data

5. Understand Functional Flow First

Before writing SQL:

• Understand procurement lifecycle
• Understand order lifecycle

Frequently Asked Questions (FAQs)

1. Can we directly access Oracle Fusion SCM tables?

No. Direct DB access is restricted. Use:

• OTBI
• BI Publisher
• APIs

2. What is the most commonly used SCM table?

EGP_SYSTEM_ITEMS_B is widely used for item master data.

3. Should we use tables or APIs for integrations?

Best practice:

• Use APIs for real-time integrations
• Use tables (via BIP) for reporting

Summary

Understanding Oracle Fusion SCM Tables is essential for any consultant working on reporting, integrations, or analytics.

Key takeaways:

• Tables are the backbone of SCM data
• Access is indirect via tools like BIP and OTBI
• Each module has its own critical tables
• Real implementations depend heavily on correct table usage

As a consultant, your value increases significantly when you:

• Know which table stores what
• Understand relationships
• Can quickly troubleshoot data issues

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