Oracle Fusion Data Model & Table Structure Overview 

Oracle Fusion applications run on top of an Oracle database (RDBMS). Like any database, it contains tables, views, primary keys, and indexes. The data model defines how business entities are organized and related. Tables organize data in rows and columns, utilizing keys for unique identification and establishing relationships. 

As Oracle consultants, we often need to write SQL queries. Only SELECT statements are allowed; INSERT, UPDATE, and DELETE are restricted. Any data changes must be done through APIs, file uploads, or front-end interfaces. To retrieve data, developers must understand the table structures. 

In this blog post, we will cover the core concepts of the Oracle Fusion database that every consultant should be familiar with. We will focus on Fusion-specific table features such as Flexfields, multi-language tables, and date-tracked HCM tables.

We will also share tips and best practices for working effectively with Oracle Fusion data models. 

Core Concepts & Components of Oracle Fusion Data Model 

Table: The fundamental unit of data storage in Oracle Financials Cloud, where information is kept in rows and columns. 

Example: POZ_SUPPLIERS stores supplier-level details, such as the supplier number in the SEGMENT1 column. 

View: A logical representation of one or more tables. A view is essentially a stored query that pulls data from its underlying tables. 

Example: POZ_SUPPLIERS_V is a view based on POZ_SUPPLIERS and other related tables. It contains additional columns not found in the base table, for instance, the supplier name is available in the view but not in POZ_SUPPLIERS. 

Note: If you come from an Oracle E-Business Suite (EBS) background, you’ll find this familiar; many table names in Oracle Fusion are the same or very similar to those in EBS. Check out our blog on Oracle E-Business Suite to Oracle Fusion differences. 

Primary Key: A primary key uniquely identifies each record in a table. It is unique and non-null, ensuring the identity of each row. Primary keys generally have indexes to facilitate fast retrieval. 

Foreign Key: A foreign key is a column or set of columns in one table that references the primary key of another table, establishing relationships and referential integrity. In Oracle Fusion, foreign keys are often managed at the application level rather than being physically enforced in the database schema. 

Indexes: Indexes enhance query performance by facilitating rapid lookups on columns, including those designated as primary keys. Oracle Fusion tables come with Oracle-provided indexes. However, users do not have direct access to create custom.  

Note: Unlike other database structures, developers do not have access to tune the Oracle database; we can not create custom indexes and will have to adapt our SQLs to utilize Oracle-provided indexes.  

Also Read: Oracle Fusion Reporting Challenges Explained

Major Modules & Key Tables 

Oracle Fusion utilizes schema modularization to organize database objects into separate pillars, including Human Capital Management (HCM), Supply Chain Management (SCM), Financials, and Common/CX. Each pillar focuses on a specific business domain. 

Within each pillar, tables are further grouped into modules. Oracle Fusion Cloud has over 290 modules, including: 

How to Explore & Use the Data Model?

Exploring and effectively utilizing the Oracle Fusion data model is essential for accurate reporting and seamless data extraction. The official Oracle documentation provides comprehensive guides for each module, such as Human Capital Management (HCM), Financials, and Common Features, which serve as a foundational reference for understanding relevant tables and views. The complete documentation for each module is available in the links below: 

• Tables and Views for Common Features 
• Table and views for HCM  
• Tables and views for Financials 

Unlike traditional databases, Oracle Fusion does not allow direct connections using TNS details or standard database tools.

To access the data model, you need specific roles such as the BI Publisher Data Model Developer role, which grants permission to read tables and views. Oracle’s standard approach for writing SQL is through BI data models, where you first create a data model and then build a report.

Here is a step-by-step guide to create your first report. Alternatively, you can use the CloudSQL tool to query the Oracle Fusion database directly, just like you would with any other database. 

Let’s have a look at the metadata tables that Oracle Fusion provides. 

As an Oracle consultant, much of your SQL work involves finding the right tables and understanding their structure. CloudSQL makes this easier in three ways: 

1. Code autocomplete suggests table and column names as you type, saving you the effort of searching through the database or Oracle documentation. 

2. You can right-click a table name and select “Describe” to instantly view its structure. 

3. The database browser on the left pane lets you expand and explore each table’s structure in detail. 

Best Practices for Working with Oracle Fusion Data Model

• Choose Between Tables and Views Wisely: Views are convenient for quick, ad-hoc queries, but may include unnecessary joins that impact performance. For performance-critical SQL, use base tables. 

• Leverage Table Documentation: Use names, column definitions, and indexes to understand joins and keys. If documentation is not available, review existing SQLs from data models, views, or Oracle forums, but always thoroughly test them for your specific use case. 

• Stay Updated with Releases: Oracle updates the application every quarter and refreshes the database documentation. While core structures rarely change, new tables and columns are often added. Always check the documentation for your specific version before writing SQL. 

• Avoid SELECT *: Always specify only the columns you need to reduce data load and improve efficiency. 

You Should Read: Top Methods to Debug Oracle BI Publisher Error Quickly 

Schema Design Patterns & Relationships 

Relationships are expressed via foreign keys, lookup tables, and reference tables. However, foreign keys may not always be physically enforced in the schema, as integrity is application-managed. Here are some naming conventions that can save you time when writing SQL. These aren’t strict rules, but helpful tips to keep in mind: 

• Table names are usually plural (e.g., lines instead of line). 
• Table names often begin with the application or schema name, although there are exceptions due to historical reasons. 
• Foreign key columns typically share the name of the primary key in the related table. 
• View names usually match the table name but end with _V. 

Here are some features in Oracle Fusion, along with the underlying table structure. 

Descriptive Flexfields (DFFs) 

A Descriptive Flexfield (DFF) allows you to capture additional, user-defined information in Oracle Fusion without customizing the core product. Most tables in Oracle Fusion include ATTRIBUTE columns (e.g., ATTRIBUTE1, ATTRIBUTE2, etc.), which can be configured by clients to store data specific to their business needs. 

For example, a client might want to store a custom supplier type for each supplier record. They could configure the ATTRIBUTE1 column to hold this value. Functional consultants usually do this setup during the implementation phase. 

DFFs are also context-sensitive, meaning the same column can store different values depending on the scenario. For instance, ATTRIBUTE1 might store a payroll number for a UK employee, but a tax code for an Australian employee. 

When writing SQL queries, it’s essential to check the DFF configuration to understand what each ATTRIBUTEx column stores. Also, review the value set attached to a DFF field, since this defines how codes are translated into display values. 

Tip: To quickly access the DFF configuration from a page, use the Highlight Flexfields option from the top menu. This will highlight the DFF columns in use. 

If descriptive flexfields are available and configured on the screen, you will see one or more wrench icons displayed. 

Click on the Wrench icons to navigate to DFF configuration 

Must Read: Guide to Oracle BI Publisher Performance Tuning for Fusion Applications 

Extensible Flexfields (EFFs) 

An Extensible Flexfield (EFF) in Oracle Fusion is like an advanced version of a DFF; it stores additional user-defined attributes, but instead of using a few ATTRIBUTEx columns in the base table, it creates separate extension tables, making it more scalable and better for handling large or complex sets of custom fields.

If you are from an Oracle eBusiness Suite background, this is a new concept to you. Think of it as a table with only ATTRIBUTE columns. Also, multiple records can exist in an extensible flexfield table for a single record in the base table. 

For example,  PER_ASSIGNMENT_EXTRA_INFO holds additional assignment details linked to PER_ALL_ASSIGNMENTS_F. 

While working with the data model, it is essential to understand the names of the extensible tables for a base table, the contexts configured, and the segments. Here is an SQL to fetch the Extensible flexfield values for the above example. 

SELECT
     asg.assignment_number,
     asg.assignment_name,
     eff.AEI_ATTRIBUTE1,
     eff.AEI_ATTRIBUTE2,
     eff.AEI_ATTRIBUTE3,
     eff.AEI_ATTRIBUTE4
FROM
     per_all_assignments_f asg
     JOIN per_assignment_extra_info_m eff ON asg.assignment_id = eff.assignment_id
WHERE
     sysdate BETWEEN asg.effective_start_date AND asg.effective_end_date
     AND eff.information_type != 'ORA_WGEInformation'

Lookup Tables 

A lookup type is a predefined list of valid values that Oracle Fusion uses to control data entry and maintain consistent information. Many tables store a lookup code, and you need to join it with the lookup configuration to get the user-friendly meaning or description. These values can be queried from the FND_LOOKUP_VALUES view. 

For example, the PER_PEOPLE_LEGISLATIVE_F table has a column called MARITAL_STATUS, which stores codes like BE_LIV_TOG or D. In reports, these should be displayed as “Living Together” or “Divorced.” To do this, you query the lookup values and join them with your table. 

SELECT
     lookup_code,
     meaning,
     description
FROM
     fnd_lookup_values
WHERE
     lookup_type = 'MARITAL_STATUS'
     AND LANGUAGE = 'US'
     AND lookup_code = 'BE_LIV_TOG'

Multi-Language Tables 

Oracle Fusion supports users from different countries, so labels, descriptions, and names often need to be stored in multiple languages and shown in the user’s preferred language. To handle this, Oracle uses two linked tables: 

1. A base table (ending with _B) that stores codes, IDs, and other non-translatable data. 
2. A translation table (ending with _TL) that stores the translatable text in different languages, along with a LANGUAGE column. 

An example of a query involving a base table and a translation table is below. This SQL will fetch currency names in Arabic. 

SELECT
     curr.currency_code,
     curr_tl.name currency_arabic_name
FROM
     fnd_currencies_b curr,
     fnd_currencies_tl curr_tl
WHERE
     curr.currency_code = curr_tl.currency_code
     AND curr_tl.language = 'AR'

To make querying easy for developers, we can use the _VL views. These are  Views joining _B and _TL, with filters based on the session language (using nls(‘LANG’)). So this will return one record based on the current language associated with the user. 

Best Practice Querying Multi-Language Tables

• Use VL Views Whenever Possible: These views are designed to automatically return the correct language-specific data, saving you from having to manually manage language joins. 

• Be Explicit With_TL Tables: If you query a _TL (translation) table directly, always include a condition on the LANGUAGE column to ensure you’re retrieving the correct language version of the data. 

• Handle Single-Language Environments Carefully: In systems that haven’t implemented multiple languages, you can simplify your SQL by hardcoding LANGUAGE = ‘US’ for quick, ad hoc queries. 

You Can’t Ignore: Comparison & Guide of Reporting Tools in Oracle Fusion

Date-Tracked Human Resource Tables 

Date-tracked tables are primarily used in HCM (Human Capital Management). They are designed to maintain a history of changes over time. This means queries can return not only the current values (like an employee’s job title, role, or assignment) but also the full historical record of how those values have changed. 

Date-tracked tables in Oracle Fusion typically have names ending with _F. Each of these tables contains two mandatory columns: EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. For open-ended records, the EFFECTIVE_END_DATE is set to 31-DEC-4712, which represents “end of time.”.

When querying for current records, always include a condition that compares the effective start and end dates with the current date to ensure you return only active data. 

SELECT
     papf.person_number,
     papf.effective_start_date,
     papf.start_date,
     papf.primary_email_id
FROM
     per_all_people_f papf
WHERE
     sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date

Challenges with the Data Model & Tips 

Working with Oracle Fusion’s data model can be tricky. Here are the main challenges and how to handle them: 

Schema Complexity 

Oracle Fusion contains a huge number of tables, and figuring out which ones to use, and how they are related can feel overwhelming. 

Solution: Instead of starting from scratch, begin with existing SQLs and adapt them to your specific needs. You can reverse-engineer the joins and logic from SQLs found in views, view objects, standard data models, custom data models, or Oracle documentation. This approach saves time and helps you learn the correct relationships between tables. 

Performance 

Some Oracle Fusion tables, such as invoices or journals, can be huge. Running queries without filters or using inefficient joins can lead to significant performance issues.  

Solution: Always apply filter conditions when querying large tables to limit the data retrieved. Use indexed columns for joins whenever possible to improve performance. If a view brings in more data than you need, simplify the SQL by removing unnecessary joins and working directly with the required tables. 

Security & Access 

Access to Oracle Fusion tables is controlled by roles and data security profiles. This means that querying different versions of a table can return different results. For example: 

• PO_HEADERS_ALL → Base table that contains all purchase order data. 
• PO_HEADERS → A secured synonym of the base table that applies security rules, such as restricting results to specific business units (e.g., BUSINESS_UNIT IN (4,5,6)). 

If you use the wrong table, you may either miss data due to filters or accidentally bypass security rules. 

Solution: For user-level queries and reports, use the secured synonyms (like PO_HEADERS) so that fine-grained access control and policy functions are automatically applied. For system administrator queries, where ignoring security rules is acceptable, use the base tables (like PO_HEADERS_ALL). This ensures you balance both security compliance and administrative flexibility depending on your use case. 

You May Like: Oracle Fusion Custom Report Builder and CloudSQL Advantages

Tips for Effective Use of the Data Model in Reporting

When writing a query, especially a large or complex one, it’s best to follow a structured approach. 

• Start with Documentation: Review the module documentation to identify the tables relevant to your reporting domain. This helps narrow your focus before you begin. 
• Leverage Existing SQLs: Look at previously written SQLs, such as those in the CloudSQL Online public library. These examples show you common joins and relationships between tables. 
• Test with Samples: Run small sampling queries to explore the data and validate assumptions before building complete reports. 
• Structure with WITH Clauses: Break large queries into manageable pieces by using common table expressions (WITH clauses). This makes your SQL easier to read and maintain 
• Use Aliases and Prefixes: Always apply table aliases and prefixes to avoid ambiguity when joining multiple tables. 
• Develop in an SQL Editor: Tools like CloudSQL make development faster by letting you write and test queries with a single click. 
• Simplify with Views: Where permitted, create named or custom views to simplify queries for end users and reduce repetition in future reports. 

Summary Table: Quick Reference of Key Tables by Module 

When you start working with Oracle Fusion, here are some tables that you should know.  You can keep this cheatsheet handy for quick reference. 

Also Read: Oracle Fusion Ad Hoc Reporting Made Easy with CloudSQL

Frequently Asked Questions (FAQs)