Introduction

When working with Oracle Fusion Schema, understanding the underlying data structure is critical for any consultant dealing with reporting, integrations, or troubleshooting. Whether you’re building OTBI reports, developing BI Publisher extracts, or designing integrations using Oracle Integration Cloud (OIC Gen 3), knowledge of Fusion schemas helps you bridge the gap between functional processes and technical execution.

In real projects, one of the biggest challenges consultants face is identifying the correct tables, relationships, and data flow across modules like HCM, ERP, and SCM. This is where Oracle Fusion schema knowledge becomes a game-changer.

What is Oracle Fusion Schema?

The Oracle Fusion Schema refers to the database structure that stores all transactional and master data in Oracle Fusion Cloud applications. Unlike traditional on-premise systems, Fusion Cloud uses a multi-tenant SaaS architecture, meaning direct database access is restricted.

Instead of direct SQL access, Oracle exposes schema data via:

• OTBI (Oracle Transactional Business Intelligence)
• BI Publisher (BIP)
• REST/SOAP APIs
• BICC (BI Cloud Connector)
• Data Extracts (HDL/HCM Extracts)

Key Point:

Even though you don’t access schemas directly in SaaS, understanding them is essential for:

• Writing efficient reports
• Debugging data issues
• Designing integrations
• Mapping data during implementations

Key Features of Oracle Fusion Schema

1. Modular Schema Design

Each Fusion module has its own schema:

2. Multi-Layer Data Architecture

Fusion uses layered architecture:

• Transaction Tables → Store core data
• View Objects (VOs) → Used in UI and APIs
• BI Views → Used in OTBI
• Materialized Views → Performance optimization

3. Secured Access Model

• No direct SQL access
• Data is accessed via secured services
• Role-based data access enforced

4. Consistent Naming Conventions

Common suffixes used:

Real-World Implementation Use Cases

Use Case 1: HCM Employee Data Extraction

A client needed employee data for payroll integration.

Tables involved:

• PER_ALL_PEOPLE_F
• PER_ALL_ASSIGNMENTS_M

Approach:

• Use BI Publisher query based on schema understanding
• Join person and assignment tables using PERSON_ID

Use Case 2: Financial Reporting (GL Balances)

Finance team needed real-time trial balance.

Tables:

• GL_BALANCES
• GL_CODE_COMBINATIONS

Solution:

• Use OTBI subject area mapped to these schema tables
• Build report with segment filters

Use Case 3: Procurement Integration

External system needed PO data.

Tables:

• PO_HEADERS_ALL
• PO_LINES_ALL

Approach:

• Use REST API mapped to schema objects
• Validate using backend table relationships

Architecture / Technical Flow

Understanding how data flows is critical.

Data Flow in Oracle Fusion:

1. User enters data in UI
2. Data is stored in base tables (_B tables)
3. View Objects (VOs) fetch data
4. OTBI/BI Publisher queries data
5. APIs expose data externally

Example:

Employee Creation Flow:

UI → PER_ALL_PEOPLE_F → VO Layer → OTBI → Report

Prerequisites

Before working with Oracle Fusion Schema, ensure:

• Basic SQL knowledge
• Understanding of Fusion modules (HCM/ERP/SCM)
• Access to:
  • BI Publisher
  • OTBI
  • OIC Gen 3 (for integrations)
• Knowledge of business processes

Step-by-Step Build Process (Example: BI Publisher Report Using Schema)

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 SQL Query

Example:

Step 4 – Define Parameters

• Add parameters like:
  • Person Number
  • Business Unit

Step 5 – Save Data Model

• Save and validate

Step 6 – Create Report Layout

• Use Word Template (RTF)
• Map fields to layout

Step 7 – Save and Run Report

• Test output
• Validate data accuracy

Testing the Technical Component

Test Scenario: Employee Data Extraction

Input:

• Person Number = 1001

Expected Output:

• Employee details with active assignment

Validation Checks:

• Data matches UI
• Effective dates are correct
• No duplicate records

Common Errors and Troubleshooting

1. Missing Data Issue

Cause:

• Incorrect effective date filters

Solution:

• Always use:

2. Duplicate Records

Cause:

• Multiple assignments

Solution:

• Filter using primary_flag = ‘Y’

3. Performance Issues

Cause:

• Large joins without filters

Solution:

• Use indexed columns
• Apply filters early

4. Incorrect Joins

Cause:

• Wrong key mapping

Solution:

• Always use:
  • PERSON_ID for HCM
  • PO_HEADER_ID for Procurement

Best Practices

1. Always Use Effective Dating

Fusion tables are date-effective. Ignoring this leads to incorrect data.

2. Avoid Hardcoding Values

Instead of:

Use parameters.

3. Use Views Instead of Base Tables (When Possible)

• Safer
• Supported by Oracle

4. Understand Data Relationships

Example:

• Employee → Assignment → Payroll

Missing link leads to incomplete data.

5. Leverage OTBI Subject Areas

Instead of building complex SQL, use OTBI where possible.

6. Follow Security Model

• Respect role-based access
• Avoid exposing sensitive data

Summary

Understanding Oracle Fusion Schema is not optional—it’s essential for any serious consultant working in Oracle Cloud. While direct database access is restricted, schema knowledge helps in:

• Designing reports
• Building integrations
• Troubleshooting issues
• Understanding business data flow

From HCM employee data to financial transactions and procurement processes, every functional activity maps back to schema structures.

As a consultant, the more you understand these schemas, the faster and more efficiently you can solve real-world business problems.

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

FAQs

1. Can we access Oracle Fusion schema directly?

No, direct database access is not allowed in Fusion Cloud. You must use tools like OTBI, BI Publisher, APIs, or BICC.

2. What is the difference between _B and _TL tables?

• _B → Base table (stores core data)
• _TL → Translation table (stores multi-language data)

3. Which tool is best for querying Fusion schema?

• OTBI → For business users
• BI Publisher → For complex SQL reports
• OIC → For integrations