{"id":25975,"date":"2025-10-06T10:00:53","date_gmt":"2025-10-06T10:00:53","guid":{"rendered":"https:\/\/www.datafusing.com\/?p=25975"},"modified":"2026-03-30T13:09:49","modified_gmt":"2026-03-30T13:09:49","slug":"oracle-fusion-data-model-table-structure-overview","status":"publish","type":"post","link":"https:\/\/www.datafusing.com\/oracle-fusion-data-model-table-structure-overview\/","title":{"rendered":"Oracle Fusion Data Model & Table Structure Overview\u00a0"},"content":{"rendered":"\n

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. <\/p>\n\n\n\n

As Oracle consultants, we often need to write SQL queries. Only SELECT<\/strong> statements are allowed; INSERT, UPDATE, and DELETE<\/strong> 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. <\/p>\n\n\n\n

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<\/strong>. <\/p>\n\n\n\n

We will also share tips and best practices for working effectively with Oracle Fusion data models. <\/p>\n\n\n\n

Core Concepts & Components of Oracle Fusion Data Model <\/h2>\n\n\n\n

Table<\/strong>: The fundamental unit of data storage in Oracle Financials Cloud, where information is kept in rows and columns. <\/p>\n\n\n\n

Example:<\/strong> POZ_SUPPLIERS stores supplier-level details, such as the supplier number in the SEGMENT1 column. <\/p>\n\n\n\n

View<\/strong>: A logical representation of one or more tables. A view is essentially a stored query that pulls data from its underlying tables. <\/p>\n\n\n\n

Example: <\/strong>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. <\/p>\n\n\n\n

Note:<\/strong> If you come from an Oracle E-Business Suite (EBS) background, you\u2019ll find this familiar; many table names in Oracle Fusion are the same or very similar to those in EBS. Check out our blog on<\/mark>\u202fOracle E-Business Suite to Oracle Fusion differences.<\/a> <\/p>\n\n\n\n

Primary Key<\/strong>: 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. <\/p>\n\n\n\n

Foreign Key<\/strong>: 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. <\/p>\n\n\n\n

Indexes<\/strong>: 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.  <\/p>\n\n\n\n

Note: <\/strong>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.  <\/mark><\/p>\n\n\n\n

\n

Also Read: <\/strong>Oracle Fusion Reporting Challenges Explained<\/a><\/p>\n<\/blockquote>\n\n\n\n

Major Modules & Key Tables <\/h2>\n\n\n\n

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. <\/p>\n\n\n\n

Within each pillar, tables are further grouped into modules. Oracle Fusion Cloud has over 290 modules, including: <\/p>\n\n\n\n

Pillar<\/strong> <\/td>Key Tables \/ Views<\/strong> <\/td>Typical Entities Covered<\/strong> <\/td>Common Use in Reporting \/ SQL Extraction<\/strong> <\/td><\/tr>
HCM (Human Capital Management)<\/strong> <\/td>PER_ALL_PEOPLE_F,  PER_ALL_ASSIGNMENTS_F HR_ALL_ORGANIZATION_UNITS_F PER_USERS <\/td>Employees, Assignments, Organization Structure,  Users <\/td>For headcount reports, org-charts, HR dashboards <\/td><\/tr>
SCM (Supply Chain Management)<\/strong> <\/td>POZ_SUPPLIERS POZ_SUPPLIER_SITES_ALL_M PO_HEADERS_ALL PO_LINES_ALL  <\/td>Suppliers Supplier site Purchase orders PO Lines <\/td>Procurement analytics <\/td><\/tr>
Financials<\/strong> <\/td>GL_JE_HEADERS GL_JE_LINES RA_CUSTOMER_TRX_ALL AP_INVOICES_ALL  <\/td>General Ledger entries, Payables Invoices, Receivables invoices <\/td>Financial reporting, audit, cash flow, expense analysis <\/td><\/tr>
Common Features<\/strong> <\/td>FND_LOOKUP_VALUES FND_FLEX_VALUES FND_CURRENCIES_VL FND_TERRITORIES_VL  <\/td>Lookup values, Value sets, Currencies, Territories <\/td>Reference data for other modules <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

How to Explore & Use the Data Model?<\/h2>\n\n\n\n

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: <\/p>\n\n\n\n