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

The complete reference for how Oracle Fusion organizes, relates, and secures its data — the second Foundation guide in FusionLens's Oracle Fusion Knowledge Hub, built to complement the SQL Guide rather than repeat it.

Why This Reference Exists

Writing SQL usually isn't the hardest part of an Oracle Fusion reporting project. Finding the right table is.

If you've already worked through our SQL Guide, you know how to write a WHERE clause that survives effective-dating, how to pick the primary assignment, how to scope a query to the right business unit — the syntax problem is solved, and what's left is quieter: knowing which table to open in the first place. If you're arriving here first, that's fine too — think of this reference as the foundation worth having before you write anything more complex than a single-table SELECT.

Oracle Fusion contains thousands of tables, but there isn't a single practical reference that explains how they fit together from a reporting perspective — most of what exists is documented only as a column list, in a naming pattern that only makes sense once someone's already explained it to you.

So people default to guessing. PER_ALL_PEOPLE_F feels obviously right for "the employee table" — and it is, for the person's core identity. But department, manager, job, and business unit don't live there; they're assignment attributes, tracked on a separate table that can change independently of who the person is. An hour disappears before a single line of SQL gets written, not because the SQL is hard, but because nobody could say with confidence where the data actually lives.

This reference exists to close that gap. By the end of this Guide, you should be able to look at an unfamiliar Oracle Fusion table name and make an educated guess about its purpose, relationships, and reporting role before opening the documentation.

Why Oracle Fusion's Data Model Is Hard to Learn

Part of it is scale — thousands of tables across a dozen product families. But scale alone isn't the real obstacle; people learn large schemas all the time. What makes Fusion's data model specifically hard is that it was built cloud-native and multi-tenant from the ground up, and that decision shows up directly in the schema: business objects are split across base tables, translation tables, and effective-dated version tables, because Fusion has to support simultaneous customers, languages, and historical versions in a way most schemas never had to.

The result is a naming convention that's genuinely logical once you know it — _TL really does mean "translation," _M really does mean a newer effective-dating model than _F — and genuinely opaque until then. That's the part this reference is built to teach: not just what's in each table, but why the schema is shaped this way, so the next table you've never seen still makes sense on sight.

What Makes This Reference Different

Search "Oracle Fusion tables" and most of what comes back is one of two things: Oracle's own documentation, or a scraped table dump with no narrative at all. Oracle's documentation is excellent as a technical reference — it's the right place to confirm a column exists. This Guide focuses on the architectural reasoning and practical relationships that developers usually learn only through project experience: why a table is split the way it is, which of three plausible-looking tables an experienced consultant would actually join to, and where people get bitten.

Every table here explains its business purpose in plain language, states its primary key explicitly, names the tables it actually joins to and why, and — where it matters — flags the mistake people make with it most often. It's organized by module the way a consultant actually thinks about the system, not alphabetically the way a database catalog does.

The goal isn't simply to document 150 Oracle Fusion tables. It's to give you the mental model that helps you understand the next 150 you haven't seen yet.

How This Reference Complements the SQL Guide

The SQL Guide answers "how do I write this query." This reference answers "where does this data live, and how is it related." They're built to be used together, not read as substitutes for each other.

Where a SQL example genuinely helps here, you'll see one — short, just enough to show a join in action — with a link to the fuller, explained version in the SQL Guide when one exists. This reference won't re-teach the effective-date WHERE-clause pattern or re-explain primary_flag = 'Y'; that's already covered in full there, and repeating it here would just be the same page twice under a different title.

Before we look at individual modules or tables, we first need to understand the language Oracle Fusion uses to describe its data model. Once you recognize those naming patterns, thousands of tables become far less intimidating. That's where we'll begin — by learning the small set of conventions Oracle uses repeatedly throughout the entire application.

Table Naming Conventions: The Complete Decoder

Oracle Fusion table names look cryptic until you realize they're not names at all — they're a compact description of how Oracle chose to store that business object. Once you know the seven patterns that repeat across literally every module, an unfamiliar table name stops being a mystery and starts being a set of testable predictions.

Read left to right and you already know, before opening a single column: this is HR data, covering every worker type, about assignments specifically, and it changes over time. That's the skill the rest of this section builds.

_B — Base Table

What it means: _B tables typically store the core structural attributes of a business object — IDs, dates, flags, and other numeric/structural columns. Human-readable values are often stored separately in the corresponding _TL table.

Why: Oracle generally separates "what this record fundamentally is" (identical across languages) from "what it's called" (varies by session language) at the table level, rather than storing a dozen language columns on one row.

When it appears: on objects with a human-readable name that needs to display in multiple languages — jobs, positions, projects, categories, most lookup-style master data.

Reporting impact: querying a _B table alone typically gets you IDs and structural data but not the display name a report needs — you'll usually need the paired _TL or _VL object too.

Example: PER_JOBS_B holds a job's ID and structural attributes; the job title lives in PER_JOBS_TL.

_TL — Translation

What it means: translation table — one row per business-object ID per installed language.

Why: Fusion is multi-language by design from day one, so translatable objects generally follow this same table shape rather than a special case per module.

When it appears: paired with most _B tables across every module.

Reporting impact: filter by LANGUAGE = 'US' (your session's language code) or you get one row per installed language per record — a guaranteed row-multiplication bug that looks like a join problem but is actually a missing language filter.

Example: PER_JOBS_TL holds the job title — one row where LANGUAGE = 'US', another where LANGUAGE = 'FR', for every job in PER_JOBS_B.

_VL — Translated View

What it means: a database view — not a table — that pre-joins the _B and _TL objects and filters to your session's language for you.

Why: so query authors don't have to hand-write the base+TL+language-filter join themselves every time.

When it appears: Oracle frequently provides a corresponding _VL view for a _B/_TL pair, although not every object exposes one — worth checking rather than assuming.

Example: PER_JOBS_VL returns one row per job, already resolved to your session language — see Retrieve Active Employees with Their Current Assignment and Headcount by Grade in the SQL Guide for this in a working query.

_F — Effective-Dated (Date-Track)

What it means: an effective-dated table — it stores every version a record has ever had, not just its current state, each row bounded by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.

Why: Fusion needs to answer "what did this look like on any given date," not just "what does it look like today" — for audit, retroactive corrections, and future-dated changes entered ahead of time.

When it appears: on anything that changes over time and where history matters — people, assignments, jobs, organizations, positions, and a long list of related HCM objects.

Reporting impact: every query against a _F table needs a date predicate, or you get every historical version back instead of the one true today.

Example: PER_ALL_PEOPLE_F — the exact pattern behind Retrieve Active Employees with Their Current Assignment in the SQL Guide.

_M — Effective-Dated (Newer Model)

What it means: also an effective-dated table, using a related but structurally newer versioning approach than the classic _F pattern.

Why: Oracle doesn't publicly document the internal rationale behind the _M suffix in detail. What's reliably observable — and what matters for reporting — is that these tables still follow the same effective-dating principles described above.

When it appears: most visibly on assignments (PER_ALL_ASSIGNMENTS_M) and a handful of related, newer objects — not a wholesale replacement of _F.

Reporting impact: identical to _F from a query-writing standpoint — a date predicate is required, and primary_flag still needs checking where multiple rows can be genuinely active at once.

Example: PER_ALL_ASSIGNMENTS_M, behind nearly every query in the SQL Guide's HCM section, including Employees with Their Direct Manager.

_ALL — Multi-Org

What it means: in Financials and Procurement, the table holds data for every business unit or legal entity in the same physical table, distinguished only by a scoping column.

Why: rather than replicate a separate physical table per business unit, Fusion's financial and procurement objects generally share one table across the enterprise and rely on an org-scoping column, enforced through security and expected in every query.

When it appears: mainly Financials and Procurement — invoices, purchase orders, receivables transactions, and their related tables.

Reporting impact: leave the business-unit filter out and you're not looking at "all the invoices" in any useful sense — you're looking at every legal entity's invoices merged together.

Example: AP_INVOICES_ALL — behind the Accounts Payable queries in the SQL Guide.

_ALL_M — Multi-Org and Effective-Dated

What it means: both conventions stacked on one table — multi-org scoping and the newer effective-dating model together.

Why: some objects are genuinely both — they vary by business unit and change over time, so Oracle combines the two suffixes rather than inventing a third scheme.

When it appears: less common than either convention alone — supplier sites are the clearest example.

Reporting impact: both predicates are required, and dropping either produces the same category of wrong-scope result described above.

Example: POZ_SUPPLIER_SITES_ALL_M.

Two predicates, no exceptions: a business-unit filter and a date filter, or the row count is wrong twice over.

Module Prefixes — A Quick Decoder

Beyond suffixes, the first few characters tell you which module owns a table.

That's the real purpose of Oracle Fusion's naming conventions. They aren't there to make table names longer — they're there to encode information. Once you learn how to read that information, the data model becomes far easier to navigate, and it sets up the next question: what's actually the difference between a business object, a table, and a view in Oracle Fusion?

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

A functional consultant asks for "the Employee record." A developer asks which table holds it. Both are asking about the same thing, and the honest answer is that there isn't one table that holds it — there are several, and the "Employee" the business talks about only exists as the sum of them.

That gap — between how the business describes data and how the database actually stores it — is worth naming explicitly, because it's the single biggest reason people get lost in Oracle Fusion's schema. Three layers are doing three different jobs:

Business Objects

A Business Object is the functional concept — Employee, Supplier, Purchase Order, Invoice — the thing a business user, a functional consultant, or a requirement document refers to by name. It's also, generally, the level Oracle's own REST API resources and BI Publisher data models are built to expose: ask the REST API for a "worker," and it hands back an assembled record, even though that record didn't live in one place internally.

A Business Object typically has no single home in the database. It's assembled — often from several tables spanning identity, assignment, organization, and translation — the moment someone actually needs to see it as one thing.

Tables

Tables are where the data physically lives, and this is where the naming conventions from the previous section stop being trivia and start being useful: a _B table holds structural identity, a paired _TL table holds the display name in every installed language, an _F or _M table holds the version that's true as of a given date, and an _ALL table holds every business unit's rows in one place.

None of those tables, on its own, is "the Employee." Together, joined correctly, they are.

No single row, anywhere, is "the Employee." This table is the join list — the same one behind Retrieve Active Employees with Their Current Assignment in the SQL Guide.

Views

Views sit between the two — built from one or more tables, exposed as something easier to query than the raw physical layer underneath. Some exist purely for convenience (the _VL translated views from the previous section). Some exist to enforce security, filtering rows based on who's asking before the data ever reaches the query (covered in full later in this Guide). And some exist to get closer to the Business Object layer for reporting specifically — OTBI's subject areas are themselves a curated, pre-joined view layer, built so a report author doesn't have to reconstruct "Employee" from five tables by hand. Oracle Fusion OTBI Subject Areas covers how those subject areas map back down to the tables underneath them, for readers building reports through OTBI rather than direct SQL.

Why the Layers Don't Line Up

None of this is Oracle making things needlessly complicated. Splitting a Business Object across tables is what makes multi-language support, historical tracking, and multi-org sharing possible without duplicating the entire record for every language, every date, and every business unit. The cost is that "the Employee" stops being one row anywhere — the benefit is everything the naming conventions in the previous section exist to solve.

If you're writing SQL against these tables directly, the SQL Guide shows exactly how that assembly happens, query by query, for fifty real business objects. This reference is about recognizing the layers before you get there — so that when a query joins five tables together, you understand why, instead of just copying the pattern.

Effective-dating is the layer that causes the most confusion in practice, and it deserves its own complete treatment — not just the suffix, but the full model behind it.

Effective-Dated Tables: The Complete Model

Why can HR schedule a promotion that becomes effective next month? Why can Procurement activate a supplier site from a future date? Why can Finance reproduce the exact organizational structure that existed when an invoice was approved?

None of those is a small trick, and none of them is HCM-specific — they're the same capability showing up in three different modules. A traditional system that just stores "the current record" can tell you what's true right now, and nothing else — update a value, and the old one is gone. Fusion needs both directions at once: the ability to enter a change today that shouldn't take effect until later, and the ability to reconstruct exactly what was true on any past date, for audit, for reprocessing, for a dispute about what a record actually said on a specific day.

How Fusion Makes This Possible

From the application's perspective, users never think about rows or history tables. They simply open a worker record, update a job, schedule a future change, or correct an existing one. Oracle Fusion decides whether that action creates a new effective-dated version, updates an existing row, or preserves history behind the scenes. The database model exists to support those application behaviors, not the other way around.

Underneath, Fusion solves this by never overwriting a record — it adds a new version of it instead. Every change to an effective-dated business object (a person's assignment, a job definition, a supplier site, an organization) creates a new row bounded by a start date and an end date, rather than updating the existing row in place. The old version isn't deleted; it's just no longer the version that's "current" as of today. This is what Oracle calls date-tracking, and it's the single most consequential design decision behind Fusion's schema.

Practically, this is what makes all three opening questions possible in the same system: a promotion or a supplier-site activation entered today with a future start date sits in the table as a row that isn't "current" yet, and won't become current until its start date arrives. A record from two years ago never disappeared — it's a row whose end date has already passed, still sitting there, still queryable, if you ask for the date it applied.

How Oracle Stores Historical Versions

Effective-dated tables (the _F and _M tables from the naming conventions section) implement this with two columns on every row: EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. A row is the "true" version of a record for any date between those two bounds. A person's assignment history might have five rows in PER_ALL_ASSIGNMENTS_M — one per change — each with its own start and end date, together covering that person's entire assignment history without a gap.

A few mechanics worth knowing at this layer, because they explain behavior that otherwise looks like a bug:

• Correction rows. Fixing a mistake in a past-dated row doesn't edit that row — it typically creates a new version covering the same period. Oracle doesn't publish a separately-documented "correction" mechanism distinct from ordinary date-tracking; which version applies as of a given date generally follows the same effective-date resolution as any other row.
• Future-dated rows. A row with a start date later than today is a real, saved row — it's just not the current one yet. Query "today" and you correctly won't see it. Query as of its start date (or later) and you will.
• No gaps, by convention. A well-maintained effective-dated history has no date gap between consecutive rows for the same record — the end date of one version is typically one day before the start date of the next. A gap usually means missing history, not an intentional absence of data.

Reporting Implications

This is where the mechanism stops being a database detail and starts being something every kind of reader runs into directly. Whether you're validating an OTBI analysis, building a BI Publisher report, extracting data through REST, or writing SQL directly, the same rule applies: if you don't define "as of when," Fusion may legitimately return multiple historical versions of the same business object. An employee who changed departments three times this year doesn't show up as one row with the current department; they show up three times, once per version, unless something in the query, data model, or extract definition specifically asks for "today's" row.

This is the most common reason a Tester validating a report sees a headcount that doesn't match what the business expects, and it's also the most common reason two people troubleshooting the same report land on different explanations — one assumes a join problem, when the actual issue is a missing date filter.

Where the SQL Lives

This Guide intentionally doesn't repeat the SQL implementation in detail — that's the SQL Guide's job, starting with Retrieve Active Employees with Their Current Assignment. The short version, for context: a query against PER_ALL_PEOPLE_F needs a condition like :as_of_date BETWEEN effective_start_date AND effective_end_date on every effective-dated table it joins, not just one of them.

Effective dating explains when a row is valid. The next concept — translations — explains how the same row can appear differently to different users without duplicating the underlying business data.

Translation Tables & Multi-Language Design

Why can two users looking at the same job see two different titles — one in English, one in French — without Oracle maintaining two separate jobs? Why can a project name entered by a US project manager appear in Spanish to a team member in Mexico, without anyone translating it by hand?

Both are the same underlying capability, and neither is specific to HCM or Projects. Oracle Fusion is built to run for organizations operating across countries and languages at once — the same organizational unit, job, or project has to mean the same thing to everyone, while displaying in whatever language each person's session is set to.

How Fusion Makes This Possible

From the application's perspective, nobody managing a job, a project, or a category ever thinks about translation logic directly. Someone enters a name once, in whatever language they're working in, and Oracle Fusion is designed to store it per installed language the environment needs to support — with every language's version tied back to the same underlying record. A user in Paris and a user in Chicago aren't looking at two different jobs; they're looking at the same job, through two different language versions of its name.

How Oracle Stores Translated Text

This is the _B / _TL / _VL pattern from the naming conventions section, in full. A base (_B) table holds the language-independent structural attributes — the ID, the dates, the flags — and a paired translation (_TL) table holds one row per installed language for anything that needs to display as text. Each _TL row is tied back to the base record by its ID, plus a LANGUAGE column identifying which language that particular row is in. Translation tables also typically carry a SOURCE_LANG column, identifying which language the text was originally entered in — useful for distinguishing an authoritative entry from a translated copy of it, though this is worth confirming against your own instance rather than assumed universal.

A _VL view generally sits on top of both, pre-joined and filtered to the session's language, so a report or query gets one row per record, already in the right language, without needing to write that join by hand.

Reporting Implications

This shows up identically across every reporting tool, not just SQL. An OTBI analysis, a BI Publisher data model, or a direct query built against a _TL table without a language constraint will return the same underlying record once per installed language it has a translation for — the same kind of duplication effective-dating causes, just triggered by language instead of time. A global report that genuinely needs to show a name in more than one language at once is a different, deliberate case — but showing it once, correctly, in the viewer's language, requires the language filter (or the pre-built _VL view) every time.

Where the SQL Lives

This Guide intentionally doesn't repeat the SQL implementation in detail — the SQL Guide covers the _VL join pattern directly, including Headcount by Grade, which resolves a grade name through per_grades_vl using exactly this mechanism.

Once you understand when a row is valid and how it appears in different languages, the next question is who is allowed to see it. That's where Oracle Fusion's security model comes in.

Data Visibility & Security in Oracle Fusion

Two managers open the same OTBI analysis. One sees 12 employees. The other sees 43.

Neither report is wrong.

Why This Happens

Oracle Fusion doesn't show every user the same data just because they're running the same report, the same analysis, or the same query. Each user is assigned a data role or security profile that defines the population of data they're allowed to see — a manager sees their own team, an HR business partner sees their assigned population, a Procurement approver sees purchase orders within their approval hierarchy. Administrators configure security through roles, security profiles, and related configuration. Once that model is in place, report authors typically don't enable or disable data security per report.

How Oracle Implements This

Oracle Fusion enforces data security through multiple application and database mechanisms, and the exact implementation varies by product area — not something most report authors need to understand. What matters, from a report author's perspective, is that security acts as an additional filter on the data returned to each user, regardless of the reporting tool being used.

Reporting Implications

A tester compares a report against production. The numbers don't match. The SQL is identical. The report definition is identical. The underlying data is identical. The missing piece is security — the test account and the production comparison account simply don't have the same data-visibility scope, and no amount of checking the report logic will explain the gap.

This same pattern shows up across every tool, not just SQL: an OTBI analysis run by two different users, a BI Publisher report scheduled under a service account versus run manually by an end user, a REST API call authenticated as different users — all of them can legitimately return different row counts for what is, on paper, "the same" request.

What This Means for Integrations

REST illustrates the read side of this. REST integrations are typically executed under the identity of a user or integration account, and the data available through those APIs is subject to the security assigned to that identity. The exact authentication model depends on how the integration is configured.

HDL illustrates the write side, and it's a different kind of access control — not about which rows a loading account can see, but which business objects and data it's permitted to create or change. Worth keeping these two directions distinct rather than treating them as the same concern.

Related FusionLens Guides: SQL Guide (SQL-level access-control queries) · Oracle Fusion SQL Security Explained · Oracle Fusion Security Guide (coming soon)

After understanding when, how, and who, the final architectural layer is scope — how Oracle Fusion keeps multiple business units and legal entities inside the same application instance without mixing their data.

Multi-Org: Business Units & Legal Entities

How can one Oracle Fusion environment serve dozens of companies, hundreds of departments, and thousands of users — without mixing their transactions?

The answer isn't separate databases. It's Oracle Fusion's organizational model, which separates legal responsibility, operational ownership, and financial accounting while keeping them inside a single application instance.

How This Plays Out in the Application

Users generally work within a Business Unit context. Depending on the product area and transaction type, related organizational structures — such as the associated Legal Entity — are derived through the application's configuration rather than selected directly on every transaction.

The Three Concepts Worth Keeping Straight

• Legal Entity — the legal/tax structure. A registered company or subsidiary with its own statutory and tax reporting obligations.
• Business Unit — the operational transaction scope. Where day-to-day transactions actually get processed and reported.
• Ledger — the financial record-keeping structure. Where accounting entries land, defined by a chart of accounts, currency, and calendar.

Manufacturing and Supply Chain modules introduce additional organizational concepts such as Inventory Organizations and Cost Organizations. Those are intentionally outside the scope of this Foundation Guide.

Why This Lives in Shared Tables

Oracle could have created separate tables for every Business Unit or Legal Entity. Instead, it stores many organizations together in shared tables and distinguishes them through organizational attributes. That design keeps one application instance scalable while allowing organizational separation through configuration, security, and reporting — the same _ALL pattern from the naming conventions section, now with the business reason behind it.

Reporting Implications

A Finance user asks: "Why does my invoice report contain invoices from three countries?" The report isn't necessarily wrong. It may simply not have been scoped to the intended Business Unit.

The same pattern shows up in Procurement: a buyer asks why their requisition list only shows requests from their own Business Unit, when they expected to see the whole company's activity — that's the scope working as designed, not a missing requisition.

Where the SQL Lives

The SQL implementation of organizational scoping belongs in the SQL Guide. This Guide focuses on understanding why organizational scope exists before showing how it's queried.

Related FusionLens Guides: SQL Guide — for the _ALL-table scoping pattern in a working query

Relationship Archetypes

Oracle Fusion doesn't invent a new relationship shape for every module. The relationship patterns below appear again and again across HCM, Financials, Procurement, SCM, and Projects. They aren't the only patterns you'll ever encounter, but they're among the most common — and once you recognize one, an unfamiliar table stops being a blank slate.

Pattern — Master → Translation

Covered in full in the Translation Tables section earlier in this Guide — this pattern is the _B/_TL/_VL structure you already know, showing up again here as the first of several recurring shapes.

Pattern — Header → Lines

You'll see this in: an AP Invoice, a Purchase Order, a GL Journal Entry — a single document with one set of header-level facts (who, when, status) and a variable number of line-level facts (what, how much) underneath it.

Typical tables: AP_INVOICES_ALL → AP_INVOICE_LINES_ALL (Financials); PO_HEADERS_ALL → PO_LINES_ALL (Procurement).

Pattern — Person → Assignment

You'll see this in: an employee's core identity versus their department, job, and manager — this pattern is genuinely HCM-specific rather than something that repeats identically elsewhere, and it's worth learning as its own shape rather than forcing a parallel that doesn't quite hold in other modules.

Typical tables: PER_ALL_PEOPLE_F → PER_ALL_ASSIGNMENTS_M.

Pattern — Organization → Hierarchy

Hierarchies appear throughout Oracle Fusion wherever one business object can contain another of the same kind — a department containing sub-departments, a project containing tasks and sub-tasks, a category containing sub-categories. The shape is self-referencing: the same table, pointing back to itself, one level at a time. In database terms, this is commonly implemented as a row pointing to another row in the same table.

Pattern — Transaction → Distribution

You'll see this in: an AP invoice and a Purchase Order, both of which need to answer "which GL account does this actually hit," a question the transaction itself doesn't answer directly.

Typical tables: AP_INVOICE_DISTRIBUTIONS_ALL (Financials); PO_DISTRIBUTIONS_ALL (Procurement).

Pattern — Party → Account/Site

Typical examples:

• Customer: HZ_PARTIES → HZ_CUST_ACCOUNTS → Customer Sites
• Supplier: Supplier → Supplier Sites

The party is one thing; the accounts and sites built on top of it are how that one thing actually transacts. The two chains aren't identical in shape to each other — the pattern is "one entity, several operational manifestations," not one specific table pair repeated verbatim.

Pattern — Lookup → Meaning

You'll see this in: almost every status field you'll ever query — the honest, common question behind this pattern is "why is STATUS = 'A' instead of just saying 'Approved'?" The stored code is compact and stable across languages; the meaning attached to it is what a human actually reads. Unlike translation tables, lookup meanings usually represent business semantics rather than language variants.

These relationships are what SQL JOINs actually encode — see the SQL Guide for them written out as working queries.

By this point, individual table names should matter less than they did at the start of this guide. Once you recognize the relationship pattern, an unfamiliar table often becomes much easier to understand. That's the goal of this guide — not to memorize Oracle Fusion's schema, but to recognize the design patterns behind it.

Related FusionLens Guides: SQL Guide — every pattern above, written out as a working query

How HCM Thinks

Every HCM process — hiring, promotions, transfers, payroll, absences, performance, and talent — ultimately revolves around the same business object: the worker.

That doesn't mean Oracle stores a worker in one table. As you learned in the Business Objects section, a business object is assembled from multiple related tables, and HCM is one of the clearest examples of that design.

Core Business Objects: Worker · Assignment · Job · Department (Organization) · Position

Recurring Patterns

• ✓ Effective Dating — Worker, Assignment, Job, Department are all date-tracked
• ✓ Translation — Job/Department names resolve through _TL/_VL
• ✓ Person → Assignment — this is where that archetype was introduced
• ✓ Organization → Hierarchy — Department structures nest

If you've understood the previous five sections, very little in the HCM schema should feel completely unfamiliar. You'll mostly be recognizing patterns you've already seen.

Tables You'll Meet Most Often (in the order you'll actually need them, not alphabetical)

• PER_ALL_PEOPLE_F — a worker's identity
• PER_ALL_ASSIGNMENTS_M — their current role
• PER_PERSON_NAMES_F — their display name
• HR_ALL_ORGANIZATION_UNITS_F — their department
• PER_JOBS_VL — their job title

Need primary keys, join paths, reporting scenarios, and common mistakes? Expand the detailed reference below.

How Financials Thinks

Every financial transaction eventually answers the same question: which GL account does this hit? An AP invoice, an AR transaction, and a GL journal entry are three different starting points that all converge on the same ledger — this is the Transaction → Distribution pattern, applied at the scale of an entire company's books.

Financials also leans harder on the Multi-Org pattern than HCM does — nearly everything here is an _ALL table, because invoices, receivables, and journals are exactly the kind of data that has to stay separated by business unit and legal entity while still living in one shared schema.

Core Business Objects: Journal Entry (GL) · Invoice (AP) · Customer Transaction (AR) · Chart of Accounts

Recurring Patterns

• ✓ Header → Lines — journal headers/lines, invoice headers/lines, AR transaction headers/lines
• ✓ Transaction → Distribution — the AP/AR-to-GL bridge, the defining shape of this whole module
• ✓ Party → Account/Site — suppliers and customers, both built on the party pattern
• ✓ Multi-Org — almost every table here is _ALL
• ✗ Person → Assignment, Organization → Hierarchy — not natural fits here; if you're looking for those, they belong in HCM

General Ledger — Tables You'll Meet Most Often

GL_JE_HEADERS → GL_JE_LINES → GL_CODE_COMBINATIONS → GL_BALANCES

The four General Ledger tables below are enough to understand how journals move through Oracle Fusion. The detailed reference expands each one.

Accounts Payable — Tables You'll Meet Most Often

AP_INVOICES_ALL → AP_INVOICE_LINES_ALL → AP_INVOICE_DISTRIBUTIONS_ALL → Supplier

These four tables cover most invoice-to-payment reporting. The detailed reference below fills in the rest.

Accounts Receivable — Tables You'll Meet Most Often

RA_CUSTOMER_TRX_ALL → RA_CUSTOMER_TRX_LINES_ALL → AR_PAYMENT_SCHEDULES_ALL → Customer

These four tables cover most billing-to-collection reporting. The detailed reference below fills in the rest.

How Procurement Thinks

Every purchased item follows the same journey: request → order → receipt → invoice → payment. Procurement owns only part of that journey. Receiving completes the purchasing process, while invoicing and payment continue in Accounts Payable (see the Financials module above). Requisition and Purchase Order are Procurement's own objects; Invoice and Supplier are the same objects already covered there — Procurement doesn't duplicate them, it hands off to them at the end of the journey.

Core Business Objects: Requisition · Purchase Order · Receipt (Invoice and Supplier — see Financials)

Recurring Patterns

• ✓ Header → Lines — requisition header/lines, PO header/lines
• ✓ Transaction → Distribution — PO → GL, the same shape as AP → GL
• ✓ Party → Account/Site — Supplier, already introduced in Financials
• ✓ Multi-Org — _ALL tables again

Tables You'll Meet Most Often

POR_REQUISITION_HEADERS_ALL → PO_HEADERS_ALL → PO_LINES_ALL → RCV_TRANSACTIONS → PO_DISTRIBUTIONS_ALL

These five tables cover the requisition-to-receipt journey. The detailed reference below fills in the rest.

How Inventory Thinks

Inventory doesn't primarily track products. It tracks where those products are, how they move, and how much of them exists at a given point in time. That's a different question from "what is this item," and Fusion answers it with different tables for each part.

Inventory introduces its own organizational scoping concept, the Inventory Organization — a physical or logical location distinct from the Business Unit scoping covered earlier. This Guide doesn't go deep on Inventory Organization's full configuration, but it's worth knowing it exists as the "where" behind every inventory table below.

Core Business Objects: Item · On-Hand Quantity · Material Transaction

Recurring Patterns

• ✓ Organization-specific master data — the same item can look different per inventory organization
• ✓ Current State ↔ Transaction History — on-hand quantity vs. the movements that produced it
• ✓ Multi-Org (Inventory Organization) — a different scope than Business Unit
• ✓ Translation — item descriptions, the least distinctive pattern here, listed last on purpose

Tables You'll Meet Most Often

EGP_SYSTEM_ITEMS_B → EGP_ITEM_CAT_ASSIGNMENTS → INV_ONHAND_QUANTITIES_DETAIL → INV_MATERIAL_TXNS

These four tables cover most item and stock-level reporting. The detailed reference below fills in the rest.

How Projects Thinks

Every cost, time entry, and invoice belongs to a project structure. Projects doesn't originate most of its own transactions — it's where costs that started in AP, Payroll, or Expenses ultimately get organized, by project and by task.

Core Business Objects: Project · Project Element (Task/WBS) · Expenditure Item

Recurring Patterns

• ✓ Organization → Hierarchy — this is the same self-referencing shape covered earlier, and Projects is where that pattern's second worked example came from: a project containing tasks, tasks containing sub-tasks.
• ✓ Transaction → Distribution — expenditure items connect back to the ledger, the same shape as AP and PO.
• ✓ Multi-Org — _ALL tables again.
• 🟡 Translation — a project name likely resolves through a paired translation object, following the same pattern as everything else in this Guide, though this Guide hasn't independently verified the exact translation table name for Projects.

Tables You'll Meet Most Often

PJF_PROJECTS_ALL_B → PJF_PROJ_ELEMENTS_B → PJC_EXPENDITURE_ITEMS_ALL

These three tables cover most project-cost reporting. The detailed reference below fills in the rest.

How Security Thinks

Every action begins with identity, then permission, then data visibility. This section doesn't introduce a new security model; it maps the concepts from the Data Visibility & Security section to the tables that implement them.

Core Business Objects: Application User · Role · Security Profile

Recurring Patterns

• ✓ Everything from the Security section earlier — this section is the table-level companion to that chapter, not a fresh introduction.
• 🟡 Person → Assignment-adjacent — an application user is optionally linked to an HCM person, but the link is nullable; service and integration accounts legitimately have no person behind them.

Tables You'll Meet Most Often

PER_USERS → FND_GRANTS → PER_SECURITY_PROFILES

These three tables cover most access and grant reporting. The detailed reference below fills in the rest.

Quick Reference Index

Every table this Guide covers, grouped by module. Jump to the module for full context — this index is for when you already know roughly what you're looking for.

Find by Business Question

"I need employee information" → PER_ALL_PEOPLE_F
"I need current assignments" → PER_ALL_ASSIGNMENTS_M
"I need who someone's manager is" → PER_ASSIGNMENT_SUPERVISORS_F
"I need AP invoices" → AP_INVOICES_ALL
"I need customer invoices" → RA_CUSTOMER_TRX_ALL
"I need supplier information" → POZ_SUPPLIERS
"I need journal entries" → GL_JE_HEADERS
"I need account balances" → GL_BALANCES
"I need purchase orders" → PO_HEADERS_ALL
"I need what's been received against a PO" → RCV_TRANSACTIONS
"I need current inventory levels" → INV_ONHAND_QUANTITIES_DETAIL
"I need project cost detail" → PJC_EXPENDITURE_ITEMS_ALL
"I need why two users see different data" → PER_SECURITY_PROFILES

Common Join Paths

The Quick Reference Index above answers "which module is this table in?" This section answers a different question: "I need to build this report — where do I start, and what do I join to?" Nobody starts writing a query by asking "what can I join to PER_ALL_PEOPLE_F?" You start with a business question. Full worked queries with WHERE clauses and effective-date filters live in the SQL Guide — this is your structural map, not the finished query.

1. Employee Directory

I need names, active jobs, and current departments.

PER_ALL_PEOPLE_F → PER_PERSON_NAMES_F → PER_ALL_ASSIGNMENTS_M → PER_JOBS_VL → HR_ALL_ORGANIZATION_UNITS_F

Pattern: ✓ Person → Assignment ✓ Master → Translation
Crucial Rule: apply the same TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date filter to every _F/_M table in the chain, not just one.
SQL Guide → Retrieve Active Employees with Their Current Assignment

2. Employee → Manager

I need the direct line manager for an active employee.

PER_ALL_PEOPLE_F → PER_ALL_ASSIGNMENTS_M → PER_ASSIGNMENT_SUPERVISORS_F → PER_PERSON_NAMES_F

Pattern: ✓ Person → Assignment
Crucial Rule: filtering to supervisor_type = 'LINE_MANAGER' is the general pattern — the exact seeded value can vary by release, worth confirming against your instance.
SQL Guide → Employees with Their Direct Manager

3. Supplier Invoice → GL Account

AP_INVOICES_ALL → AP_INVOICE_LINES_ALL → AP_INVOICE_DISTRIBUTIONS_ALL → GL_CODE_COMBINATIONS

Pattern: ✓ Header → Lines ✓ Transaction → Distribution
SQL Guide → AP Invoice Line and Distribution Detail

4. Purchase Order → Receipt

I want to see if ordered items actually arrived.

PO_HEADERS_ALL → PO_LINES_ALL → PO_LINE_LOCATIONS_ALL → RCV_TRANSACTIONS

SQL Guide → Receiving Transactions Detail; PO Shipment and Delivery Detail

5. Purchase Order → GL Distribution

PO_HEADERS_ALL → PO_LINES_ALL → PO_DISTRIBUTIONS_ALL → GL_CODE_COMBINATIONS

Pattern: ✓ Transaction → Distribution
SQL Guide → Validating That Every PO Distribution Has a Valid GL Account

6. Customer Invoice → Payment Status

RA_CUSTOMER_TRX_ALL → AR_PAYMENT_SCHEDULES_ALL

SQL Guide → AR Aging by Customer

7. Customer Account → Customer Name

HZ_CUST_ACCOUNTS → HZ_PARTIES

Pattern: ✓ Party → Account
SQL Guide → Customer Account and Contact Detail

8. Journal → Chart of Accounts

GL_JE_HEADERS → GL_JE_LINES → GL_CODE_COMBINATIONS

Pattern: ✓ Header → Lines
SQL Guide → Journal Entries for a Specific Accounting Period

9. Item → Current Stock

EGP_SYSTEM_ITEMS_B → INV_ONHAND_QUANTITIES_DETAIL

10. Project → Task → Cost

PJF_PROJECTS_ALL_B → PJF_PROJ_ELEMENTS_B → PJC_EXPENDITURE_ITEMS_ALL

Pattern: ✓ Organization → Hierarchy ✓ Transaction → Distribution

If You Want to Report On...

Common Mistakes About the Schema Itself

The SQL Guide's own "Common Mistakes" sections are usually about queries — a missing date filter, a forgotten primary_flag, or a bad join. This section is different. These are mistaken assumptions about the schema itself, made before a single line of SQL gets written, and they are just as costly.

1. Assuming an EBS table name carries over unchanged.

Oracle Fusion evolved from, but isn't identical to, E-Business Suite (EBS). Some objects kept their EBS-era names; others moved entirely. The item master's migration from the legacy MTL_SYSTEM_ITEMS_B to EGP_SYSTEM_ITEMS_B under Product Hub is a live, current example. A table name that "feels standard" from general Oracle Applications experience is exactly the kind of assumption worth verifying rather than trusting blindly.

2. Assuming _ALL always means the same thing.

The _ALL suffix means multi-business-unit (multi-org) in Financials and Procurement (e.g., AP_INVOICES_ALL). However, it means something entirely different in HCM, where it signals "every worker type in one table" (e.g., PER_ALL_PEOPLE_F). Reading the suffix without checking the module it belongs to produces a confident but completely wrong assumption.

3. Assuming the base table holds the display name.

A _B (Base) table holds structural identity and internal IDs. The human-readable name or description almost always lives in the paired _TL (Translation) or _VL (View Language) object. Querying a _B table alone and getting no name back isn't a bug — it is the Fusion architecture working exactly as designed.

4. Treating a _VL view as a permanent, stable contract.

A view can be rebuilt, and the underlying columns it exposes can shift in ways a base table's structure typically won't. "Convenient" does not mean "guaranteed." It is always worth doing a quick sanity check on your views after a major release upgrade, not just at initial implementation time.

5. Assuming one canonical table version exists forever.

Oracle Fusion Cloud is updated quarterly. A table, column, or relationship that works perfectly today isn't automatically guaranteed to behave the exact same way on a future release. Re-verify your critical queries after major upgrades rather than assuming absolute stability by default.

6. Relying on a column just because it "happens to work."

An undocumented or clearly internal-purpose column holding a useful value today is not the same thing as a supported reporting column. It can change or disappear without notice in a future patch — a distinction that matters most when a critical production report suddenly breaks and nobody remembers how it was built.

The remaining open questions readers tend to have aren't about any one specific table — they are about how to use this Guide day to day. That is exactly what the FAQ is for.

FAQ

Conclusion

Go back to the opening line of this Guide: finding the right table is usually the hardest part. That is still true —but it should feel different now than it did on page one.

PER_ALL_PEOPLE_F no longer looks like "the employee table" in some vague, catch-all sense. It looks like exactly what it is: one piece of a Business Object, holding identity and nothing else, with the department, job, and manager living deliberately elsewhere. An unfamiliar table like EGP_ITEM_CAT_ASSIGNMENTS or PJC_EXPENDITURE_ITEMS_ALL shouldn't feel like a cold start anymore either — you now recognize a _B / _ALL combination, a Header → Lines shape, or a Transaction → Distribution bridge.

The vocabulary from the naming conventions section and the patterns from the Relationship Archetypes section don't just explain the fifty-some tables this Guide actually covers. They are what make the next table — the one this Guide never mentioned — readable on sight.

That was always the actual goal: not a longer table list, but a shorter list of ideas that explain a longer list of tables.

Naming conventions that encode real information instead of adding noise.

A schema split across _F, _TL, and _ALL not because Oracle likes complexity, but because multi-language, multi-org, effective-dated software genuinely needs that split to work at all.

Business Objects that live nowhere and everywhere at once, assembled fresh every time someone actually needs to see one as a whole.

Security applied as a filter before anyone's report logic gets a say.

A handful of relationship shapes standing in for what would otherwise be hundreds of one-off joins to memorize.

If you came here first, the Oracle Fusion SQL Guide is where fifty of these patterns turn into working queries — the same tables, the same joins, written out in full.

If you came here from the SQL Guide, you now have the reasoning behind the joins you were already writing — the primary keys, the relationships, and the "why" that wasn't strictly necessary mid-query, but was always worth having.

Neither guide is the complete picture on its own. They are the two core references this Guide's architecture assumes you will actually use: one for what the data means, and one for how to ask it a question.