Oracle Fusion Tables Reference: Complete Guide to the Oracle Fusion Data Model

A practical map of how Oracle Fusion organizes data: naming clues, temporal versions, translation layers, organizational scope, security, recurring relationship shapes, and more than fifty high-value tables and views across HCM, Financials, Procurement, Inventory, Projects, and Security.

Why This Reference Exists

Writing SQL is rarely the first difficult step in Oracle Fusion. The harder question is usually: which object represents the business grain I actually need?

An “employee” is not one database row. Person identity, name, work relationship, assignment, manager, job, position, location, and organization all have separate lifecycles. A purchase order is not one row either: header, line, schedule, distribution, receipt, invoice matching, and accounting each live at different grains. A supplier name is a TCA party attribute, while supplier operational attributes belong to Procurement.

This reference is designed to make those distinctions visible before you write the join.

Why Oracle Fusion's Data Model Is Hard to Learn

Fusion combines several architectural concerns in the same schema: application business objects, date effectivity, translation, enterprise partitioning, business-unit or inventory-organization scope, accounting grain, and data security. Those concerns do not map one-to-one to suffixes.

The useful skill is therefore not “memorize every suffix.” It is: use the name as a hypothesis, then verify the actual object's columns, keys, foreign keys, view definition, and security semantics.

What Makes This Reference Different

Oracle's object documentation is excellent for answering “does this column exist?” This guide focuses on the next questions: what is the row grain, which neighboring object completes the business concept, which join silently multiplies rows, and which familiar-looking name comes from an older Oracle Applications model rather than current Fusion.

How This Reference Complements the SQL Guide

The Oracle Fusion SQL Guide answers “how do I query this?” This reference answers “where does this live, what is its grain, and what should it join to?” Use the reference to choose the right objects; use the SQL guide for worked query patterns.

• XLA_TRANSACTION_ENTITIES gives the source transaction an XLA entity identity.
• XLA_EVENTS records accounting events, keyed within an application.
• XLA_AE_HEADERS and XLA_AE_LINES contain subledger journal headers and lines.
• XLA_AE_LINES.GL_SL_LINK_ID and the link-table discriminator support subledger-to-GL drillback.
• GL_IMPORT_REFERENCES is populated for journal sources configured to maintain import references; it is not guaranteed to contain every possible feeder transaction.

The Mental Models guide explains accounting conceptually. At the physical layer, Subledger Accounting introduces a separate identity and journal hierarchy between the source transaction and General Ledger.

The XLA Accounting Data Layer: From Source Transaction to GL

Trading Community Architecture (TCA) is the identity layer behind many customer and supplier relationships. The physical model separates who the party is from what commercial relationship it has and where/how a site is used.

TCA Party Identity Layer: Party, Account, Site and Business Use

Interface tables are processing surfaces, not alternate copies of production transactions. GL_INTERFACE holds journal-import data waiting to become GL journals. PO_HEADERS_INTERFACE participates in Purchasing Document Open Interface processing before a valid purchase order is created.

Interface & Staging Tables: Data Before It Becomes a Transaction

Do not infer a universal audit convention solely from the name. Oracle explicitly documents trailing-underscore audit variants in several product families, but the object must still be verified individually.

Technical Metadata Every Fusion Developer Should Recognize

This means two rows with the same business code can legitimately exist in different sets. Treat SET_ID as part of the data grain whenever the object is reference-data-set enabled.

The Mental Models guide explains why reference data sharing exists. At the schema layer, the important fact is that many shareable reference objects carry a SET_ID. FND_SETID_SETS stores reference-data sets, while FND_SETID_ASSIGNMENTS maps a reference group and determinant to the set that applies.

Reference Data Sets: The Physical SET_ID Layer

• FND_DF_SEGMENTS_B — DFF segment metadata, including context, database column and value-set references.
• FND_KF_SEGMENTS_B — KFF segment metadata, including structure, sequence, column and default value set.
• FND_VS_VALUE_SETS — value-set definitions.
• FND_VS_VALUES_B — values belonging to a value set.

Metadata Objects Worth Knowing

Flexfields are one of the biggest reasons two Fusion customers can use the same base object very differently. The physical storage model differs by flexfield type, so “where is my custom field?” has three different answers.

Flexfields & Extension Storage: DFF, EFF, KFF and Value Sets

The physical lesson is simple: a stored status code is not necessarily the text a user sees. Resolve codes through the documented lookup/value layer when the report needs a display meaning, and preserve the code when the integration or reconciliation depends on stable machine-readable values.

Fusion transactional tables often store a compact code while the user interface displays a translated meaning. FND_LOOKUP_VALUES exposes the familiar lookup dimensions: lookup type, lookup code, meaning, description, language, enabled status, active dates, and—where applicable—reference-data set context.

Lookups: Codes, Meanings & Display Values

Not every pair of dates is an effective-date model. Fusion uses several different date semantics, and confusing them produces subtle reporting errors.

Date Semantics Beyond Effective Dating

Suffixes are useful, but the object's role is often even more useful. Most Fusion data structures fall into a small set of physical roles.

Physical Table Roles: What Kind of Object Are You Looking At?

Not every object's practical uniqueness is a single ID. A balance can depend on ledger, period, currency, code combination, and balance type. An assignment version depends on assignment identity plus effective dating and same-day sequence state. A customer site use belongs to an account site and a business purpose. Always preserve the dimensions that define the business grain, not just the easiest numeric key to join.

Composite Grain Matters

Fusion commonly separates an internal numeric identifier from the number users recognize. PO_HEADERS_ALL.PO_HEADER_ID is the system key; SEGMENT1 is the displayed PO number. RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID is the transaction key; the user-facing transaction number is a different attribute. The same distinction appears across Projects, HCM, AP, and SCM.

Surrogate IDs vs. Business Numbers

A table name tells you roughly what the object is. The key tells you what one row actually represents. Before using a table, identify its grain in one sentence. “One purchase order header,” “one AP invoice line,” “one assignment version,” and “one subledger journal line” are different grains even when they share related IDs.

Keys, Grain & Cardinality: The Real Foundation of Fusion SQL

Oracle's Tables and Views pages are most useful when you treat them as a physical metadata dictionary, not as a ready-made reporting contract. For each object, read five things in order: object type, primary key, column descriptions, foreign keys, and indexes. Together they tell you what the object stores, what makes a row unique, and which relationships Oracle itself documents.

How to Read Oracle Fusion Table Documentation

Table Naming Conventions: A Safer Decoder

Suffixes are valuable, but they are conventions, not contracts. Do not derive a required predicate solely from a suffix. Confirm the actual object definition.

_B, _TL, and _VL

A common pattern is language-independent structure plus translated text. In HCM jobs, for example, the current objects are PER_JOBS_F, PER_JOBS_F_TL, and PER_JOBS_F_VL. The _VL view resolves the translated name using the session language.

Do not hard-code LANGUAGE='US' and describe it as “the user's session language.” US is an English language code. When querying translation tables directly, use the language required by the report; when a suitable _VL exists, it is often the simpler session-language option.

_F — Date-Effective Clue

An _F object often contains physical rows bounded by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. A current or as-of query must resolve the row for the intended date. But not every time-aware object ends in _F, and not every object with dates implements the same date-effectivity behavior.

_M — Inspect the Actual Versioning Model

PER_ALL_ASSIGNMENTS_M is the critical example. It supports multiple changes on the same day. Its key includes effective dates, EFFECTIVE_LATEST_CHANGE, and EFFECTIVE_SEQUENCE. For a normal current/as-of final-state assignment snapshot, date filtering alone is incomplete; EFFECTIVE_LATEST_CHANGE='Y' is normally part of the grain. For a same-day audit timeline, preserve the sequence instead of filtering those rows away.

_ALL — Do Not Decode It as a Universal Business-Unit Rule

Many Financials and Procurement _ALL tables contain rows across organizational scopes, but the relevant column varies by object. AP_INVOICES_ALL uses ORG_ID for the associated business unit. PO_HEADERS_ALL exposes procurement and requisitioning BU concepts such as PRC_BU_ID and REQ_BU_ID. Other modules use completely different organizational axes.

The safe rule is: identify the business scope from the object's documented columns, not from the suffix.

Module Prefixes — Useful Starting Clues

Understanding the Data Model: Business Objects vs. Tables vs. Views

A business object is the functional concept. A table is a physical storage object. A view is a database-defined projection or join. An OTBI subject area is a semantic analytics layer. These layers should not be treated as interchangeable.

Notice what is not true: PER_ALL_PEOPLE_F does not contain every personal attribute, and the assignment does not itself contain the manager relationship as a generic MANAGER_ID field for reporting.

Effective-Dated Tables: The Complete Model

Date effectivity represents one logical object with one or more physical rows over time. Past, current, and future physical rows can coexist.

That distinction matters because the common statement “Fusion never updates a row; every change creates another row” is false. Date-effective behavior depends on the operation.

DATE '4712-12-31' is commonly used as an end-of-time/open-ended sentinel. It means “no later effective end is currently defined,” not “this row is definitely current today.” A future-dated physical row can also be open-ended.

-- Generic date-effective row
:p_as_of_date BETWEEN effective_start_date AND effective_end_date

-- PER_ALL_ASSIGNMENTS_M final state for that effective date
:p_as_of_date BETWEEN effective_start_date AND effective_end_date
AND effective_latest_change = 'Y'

Translation Tables & Multi-Language Design

Translation objects separate language-specific labels from the underlying business identity. The exact shape varies, so verify the object definition rather than assuming every _TL has identical keys.

For HCM jobs, PER_JOBS_F_VL is a concrete example: Oracle's view joins PER_JOBS_F and PER_JOBS_F_TL on job ID and effective dates, then filters the translation row to the session language.

Data Visibility & Security in Oracle Fusion

This guide treats security only as a data-access layer in the physical model. Functional roles, ownership, and security-design philosophy belong in the Mental Models guide.

Secured Views vs. Base Tables

In HCM, Oracle documents secured list views such as PER_PERSON_SECURED_LIST_V, PER_ASSIGNMENT_SECURED_LIST_V, PER_POSITION_SECURED_LIST_V, and CMP_SALARY_SECURED_LIST_V. These are designed to apply HCM data-security scope for the logged-in user.

A BI Publisher physical SQL query against a base table does not automatically become equivalent to OTBI or the application UI merely because the user launched the report. Oracle explicitly distinguishes base-table physical SQL from secured-list-view access in HCM.

Physical Security Object Families

Organizational Scope: Business Units, Legal Entities, Ledgers, and Inventory Organizations

At the schema layer, organizational scope is expressed through different identifiers on different objects. Do not decode a table's organizational meaning from the _ALL suffix alone.

These columns are not synonyms. A transaction can legitimately carry several of them because operational scope, legal ownership, accounting ledger and reference-data context answer different physical relationship questions.

Relationship Archetypes

HCM

Start by separating person, work relationship, assignment, and supervisor. Most HCM reporting mistakes come from collapsing those grains into one.

Financials: General Ledger, Accounts Payable, Accounts Receivable

Procurement & Inventory/SCM

For PO-to-invoice or PO-to-receipt analysis, prefer the deepest documented common key available: schedule, distribution, or receipt transaction. A header-level join can be valid for document existence, but it is too coarse for matching and quantity/value reconciliation.

Projects & Security

The current Project Costing expenditure table is PJC_EXP_ITEMS_ALL. The older-looking name PJC_EXPENDITURE_ITEMS_ALL should not be used as the current Fusion object in this guide.

Foundation Metadata, Lookups, Flexfields & Reference Data

Projects & Billing

Procurement, Product, Inventory, Order Management & Shipping

Financials: SLA, Receipts, Payments, Cash, Assets & Tax

HCM, Compensation, Absence, Payroll & Benefits

The module sections above introduce the core objects. This layer goes one step deeper: not into functional process design and not into SQL, but into the physical subfamilies that repeatedly appear when you trace a record through the database.

Extended Module Data Families

Quick Reference Index — 117 Key Tables & Views

This index is intentionally broader than a “top 50” list. It contains 117 current, high-value tables and views used to orient yourself in the physical model. Inclusion here means “important to understand,” not “Oracle recommends this object as an analytics or extraction contract.”

Common Join Paths

Common Mistakes About the Schema Itself

FAQ

Conclusion

This reference is now deliberately a physical-model field manual: it explains how to read Oracle's schema metadata, how to reason about row grain and keys, and how the major storage layers—effective dating, translations, flexfields, reference data, audit/interface tables, TCA and XLA—fit together. The SQL Guide turns those objects into working queries; the Mental Models guide explains the functional architecture above them.

Once those ideas are explicit, unfamiliar Oracle Fusion objects stop being guesses. They become candidates you can verify quickly against Oracle's metadata.

You Now Understand How Oracle Fusion Organizes Its Data