Oracle Fusion SQL Queries: 50 Real-World Examples + Complete Guide

The complete Oracle Fusion SQL reference for developers, consultants, analysts, and reporting teams — built from years of implementations across HCM, Financials, and Procurement, not from documentation summaries.

• 50 production-ready SQL examples, explained line by line
• HCM, Financials, Procurement, Security & advanced audit patterns
• Oracle Fusion's data architecture explained from first principles
• A complete performance optimization playbook
• 18 frequently asked questions, answered concisely

• 50
  working SQL queries
• 4
  modules: HCM, GL, AP/AR, Procurement
• 15
  best practices
• 15+
  FAQs answered

Why This Guide Exists

A business user asks for a headcount report. Nothing complicated — active employees, by department, as of today.

You open a SQL editor. The query looks almost too simple: pull from the people table, filter on employee status, group by department. Ten minutes of work, tops.

Then you run it, and the number is wrong. Not obviously wrong — the query executes fine, no errors, a result set comes back looking entirely reasonable. It's wrong the way these things usually are: 320 rows come back when the org chart says 210. Someone's counted twice. Someone's counted three times. One person who left in March is still sitting in the list, active as ever.

So you start pulling the thread. The person's name lives in one table. Their current assignment — department, manager, job, grade — lives in another one entirely, and that table doesn't store "current." It stores every version of that assignment that's ever existed: the transfer eighteen months ago, the manager change in Q2, the location update last week. Nobody deletes the old rows. Fusion layers history on top of history, each row bounded by a start date and an end date, and if your WHERE clause doesn't pin down "today" precisely on every one of those tables, you get the whole timeline back instead of the one row that's true right now.

Then it gets more specific than that. Some employees genuinely hold two active assignments at once — a primary role and a secondary one — and if you don't check which row is flagged primary, that person shows up twice, correctly, for a reason your query never asked about. The business unit you assumed was implied by the report title turns out not to be filtered anywhere, so you're looking at headcount for three legal entities instead of one. And the department name you expected to see plainly in the result set might come from a secured view that quietly excludes rows your session doesn't have visibility into — which means your count is wrong for a completely different reason than the one you've spent the last twenty minutes chasing.

Nothing in that sequence throws an error. That's what makes it expensive. The query runs, the result looks plausible, and it takes someone who already knows the real headcount number to say that's not right before anyone goes looking for why.

If that's familiar, you've already lived the reason this guide exists.

What makes Oracle Fusion SQL genuinely difficult isn't the syntax. It's a short list of structural realities that show up in almost every report you'll ever build against it:

• Effective-dated tables that store a full history of every record, not its current state.
• Duplicate rows from assignments, addresses, roles, or phone numbers that are all technically active on the same day.
• Primary assignment flags that decide which of several "active" rows actually represents the person today.
• Secured views that filter data based on who's running the query, not what your WHERE clause says.
• Business unit and legal entity scoping that quietly changes what "all" means depending on which table you joined to.
• Row multiplication from joins that look correct on paper and fan out the moment real data hits them.
• Hidden joins — the ones a subject area builds for you automatically, that you have to reconstruct by hand the moment you go direct-to-schema.

Each one of those is learnable on its own. The part that actually costs people time is that a single report usually touches four or five of them at once, and the first symptom is never a stack trace — it's a number that just doesn't add up, on a Friday afternoon, with someone waiting on it.

This guide tries to be the resource that actually prepares you for that Friday afternoon — not another list of table names, but the reasoning behind them, written the way you'd explain it to someone sitting next to you.

What Makes This Guide Different

Search "Oracle Fusion SQL examples" and you'll find no shortage of results. Run through a dozen of them and you'll notice they tend to fail in the same three or four ways.

• They stop at the query itself. You get a SELECT statement and nothing about why it's shaped that way — so the moment your report needs one more column, you're on your own again.
• They don't explain the joins. The query works, but you couldn't tell someone else why that particular table connects to that particular column, which means you can't safely extend it.
• They ignore effective-dated tables, or handle them inconsistently. The query runs clean in whatever sandbox the author tested it in and returns duplicates the moment it touches real employee or supplier history.
• They overlook security. Nobody mentions that the same query can return a different row count depending on which account runs it, which is exactly the kind of thing that costs someone a confusing afternoon.
• They don't build a mental model. You learn one query. You don't learn the pattern that would let you write the next fifty yourself.

This guide is built around not doing any of that. Every query here starts from a real business scenario, is explained rather than just pasted, and is written the way it would actually be reviewed on a production project — performance notes, common mistakes, and the reasoning included, not left as an exercise for the reader.

Concretely, that means:

• Real business scenarios, not table dumps. Every query starts from an actual request — "which invoices are overdue," not "here's what's in AP_INVOICES_ALL."
• Production-oriented, not demo-oriented. These are patterns pulled from live implementations, including the filters you only learn you need after a query runs slow or wrong in a real environment.
• Every join is explained, not just written. You'll know why each table connects the way it does, so you can adapt the query instead of copying it blindly.
• Performance notes included. A query that works on 500 rows in a sandbox can time out on 500,000 in production. We flag where that gap tends to show up.
• Common mistakes sit next to the fix. Not as a disclaimer at the bottom — as part of understanding why the query is written the way it is.
• Patterns you can reuse, not one-off answers. The effective-date predicate you learn in query three is the same one you'll use in query thirty-one.

If you only take the SQL from this guide, you'll get through today's report. If you take the reasoning behind it, you won't need this guide the next time — that's the actual goal.

Who This Guide Is For

This is a technical guide for people who will actually open a SQL editor and run these queries. If you're still setting up that connection or aren't sure which access method fits your situation, How to Run SQL Queries on Oracle Fusion Cloud covers that first step — the queries below assume you're already past it. The audience spans a few roles, and each one tends to be here to solve something slightly different:

• Oracle Fusion Developers

  Building BI Publisher reports, custom integrations, or trying to figure out why the SQL behind an extension isn't returning what it should.

• Technical Consultants

  Looking for SQL patterns that hold up across HCM, ERP, and SCM implementations — not a one-off query that only works for this client.

• OTBI & BI Publisher Developers

  Writing data models that need to do something a subject area can't generate on its own — or need to run faster than the SQL OTBI generates for you.

• Oracle Integration Cloud Developers

  Staging or reading Fusion data inside an integration, where a missing effective-date filter breaks a nightly job silently instead of loudly.

• HRIS & ERP Analysts

  Creating workforce or financial reports without spending an afternoon hunting for which of forty similarly named tables actually has the data.

• Data Engineers

  Extracting Fusion data into a warehouse and needing to know, before the first load runs, which tables are safe to pull directly and which will hand you years of history you didn't ask for.

• Oracle Fusion Architects

  Setting a team standard for how SQL gets written against Fusion once, instead of re-explaining the same five gotchas to every new hire.

You don't need to be an Oracle DBA to use this guide. You do need to already know basic SQL — SELECT, JOIN, WHERE, GROUP BY. Everything Fusion-specific, we build on top of that.

What You'll Learn

By the end of this guide, you should be able to open a Fusion table you've never seen before, correctly guess most of its behavior from its name alone, and write a query against it that doesn't silently return the wrong row count. Specifically:

Why SQL Still Matters

It isn't usually the SQL syntax that slows Fusion developers down. It's remembering which of three similarly named tables actually stores the current record. Most reporting problems trace back to a join, not a SELECT clause — and that's precisely the kind of problem you can only reason about if you know what's actually being joined.

SQL isn't a replacement for OTBI or BI Publisher, and this guide isn't building toward that argument. It's a different tool, suited to a specific set of jobs the other tools weren't built to do well:

• Debugging. When a subject area returns a number nobody can explain, SQL is how you find the exact row — and the exact join — responsible for it.
• Reconciliation. Tying a GL balance back to its subledger detail, or a payroll run back to its source assignments, means following the data through joins a dashboard was never built to expose.
• Integrations. Every OIC integration, ESS job, and custom extension that moves data in or out of Fusion at scale is backed by SQL somewhere underneath it.
• Complex, cross-subject-area joins. The moment a request needs data from two subject areas that were never designed to be combined, direct SQL is usually faster than the workaround.
• Ad hoc investigation. "Why does this one supplier look different" isn't a report anyone's going to build a subject area for. It's a five-minute query.
• Validating business data. Before you trust a number in a dashboard, someone has to have traced it back to source at least once. That tracing is SQL.

Developers often spend more time identifying the correct table than actually writing the query against it. Knowing the schema directly is what closes that gap.

Before writing your first query, it's worth spending a few minutes understanding how Oracle Fusion organizes business data behind the scenes. You don't need to memorize thousands of tables — you need a handful of core concepts, and every query in this guide will make more sense once you have them.

Understanding How Oracle Fusion Stores Business Data

Every Fusion table follows a small set of rules once you know what to look for. This section covers the concepts that repeat constantly in the fifty queries ahead, so treat it as the foundation the rest of the guide builds on.

Business Objects

A business user thinks in terms of Employees, Suppliers, Invoices, and Purchase Orders. The database doesn't store any of those as a single table — each one is assembled from several tables that together represent the thing a person actually means when they say "employee."

Take "Employee." What a business user calls one employee is, underneath, at least four separate tables working together: PER_ALL_PEOPLE_F holds the person record itself, PER_ALL_ASSIGNMENTS_M holds their current job assignment, PER_JOBS_F defines the job they're assigned to, and HR_ALL_ORGANIZATION_UNITS_F defines the department. Ask for "the employee's department" and you're already three joins deep before you've written a single business filter.

This is true across every module, not just HCM. A "Purchase Order" is a header table joined to a lines table joined to a distributions table. An "Invoice" is a header, lines, and a payment schedule. Once you stop expecting one table per business object and start expecting a small cluster of them, the schema stops feeling arbitrary.

Tables vs Views

Fusion exposes both base tables and views over those tables, and the two are not interchangeable in practice. A base table stores raw data exactly as the application wrote it — foreign keys instead of names, lookup codes instead of the text you'd recognize, no decoding done for you. A view, in many cases, does that decoding: it joins the base table to its lookups and translation tables and hands you something closer to what a report actually needs.

The practical rule of thumb: if a well-named view exists for what you're building, start there. It saves you from re-deriving decode logic that Oracle has already written and tested, and it's usually more forgiving of schema changes between releases. Reach for the base table directly when you need a column the view doesn't expose, or when you're chasing a performance problem and need to control the joins yourself.

Effective-Dated Tables

This is the single most important concept in this entire guide, so it's worth slowing down for a concrete example instead of a definition — the Effective Date Handling in Oracle Fusion SQL guide covers it at full length if you want more worked examples after this one.

Say an employee, Elena Cruz, has had three assignment changes over the last two years: she started in Finance, moved to a Senior Analyst role in Procurement, and was promoted to Team Lead six months ago. Her assignment history table doesn't overwrite those changes — it keeps all three rows, each bounded by a start and end date:

All three rows are real, correct, and permanently part of the table. If your query selects from this table without a date filter, you get Elena three times — once per row — and every one of those rows is technically true. That's what "duplicate rows" actually means in Fusion: not bad data, but unfiltered history.

The fix is the same predicate, applied consistently, on every effective-dated table in your query:

-- Current record only
WHERE TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date

-- Record as of a specific date
WHERE :as_of_date BETWEEN a.effective_start_date AND a.effective_end_date

-- Full history within a range (useful for audit or turnover reports)
WHERE a.effective_start_date <= :end_date
  AND a.effective_end_date   >= :start_date

The far end date — 4712-12-31 — is Oracle's convention for "still open, no end date yet." You'll see it constantly and it's not an error; it just means this is the current row.

Two mistakes account for most of the row-multiplication bugs people bring to a code review. The first is applying the date filter to one effective-dated table in a join and forgetting it on the second one — the join still runs, it just quietly produces a row for every combination of history on both sides. The second is assuming "active employee" and "current record" are the same filter; an employee can be active today while your query is still looking at last year's assignment row because the date predicate was never applied. Neither mistake throws an error. Both show up as a row count that's higher than it should be.

Translation Tables (_TL)

Fusion is built to run in dozens of languages at once, and it handles that by splitting translatable text out of the base table entirely. A lookup table like job titles or department names typically comes in two parts: a base table holding the language-independent attributes (IDs, dates, flags), and a translation table holding one row per language for each of those IDs.

PER_JOBS_B holds the job's ID, its dates, and its structural attributes. PER_JOBS_TL holds the job name — one row where LANGUAGE = 'US', another where LANGUAGE = 'FR', and so on for every installed language. Join the base table to the translation table on the job ID, filter to your language, and you get the name back:

SELECT b.job_id, t.name
FROM per_jobs_b b
JOIN per_jobs_tl t ON t.job_id = b.job_id
WHERE t.language = 'US'

Forget the language filter and you get the job name back once per installed language — another quiet source of duplicate rows, this time with nothing to do with effective-dating at all.

Language Views (_VL)

Because the base-plus-translation join above is so common, Oracle already builds it for you in most cases. A _VL view — "view, language" — joins the _B and _TL tables and filters to the current session's language automatically, so PER_JOBS_VL gives you the same result as the query above without writing the join yourself.

Prefer the _VL view over hand-joining _B and _TL whenever one exists. It's less code, it's harder to get wrong, and it's what Oracle's own reports use internally — which means it stays correct across upgrades even if the underlying join logic changes.

Table Naming Conventions

This is the part of the schema that pays off the most once it clicks, because it means you can look at a table you've never queried before and already know how to filter it correctly.

Take PER_ALL_PEOPLE_F apart piece by piece:

Once you can read that, the rest of the schema becomes pattern-matching instead of memorization. The common suffixes, in one place — and if you want the full join-by-join breakdown of how these tables connect across modules, Oracle Fusion Table Relationships Explained is the dedicated companion piece to this section:

Secured Views

Occasionally a query with a perfectly correct WHERE clause still comes back with fewer rows than you expect — not zero, just fewer. Before assuming the data is missing, check who's running the query.

Fusion enforces data security at the database level for a number of secured objects: HCM data roles restrict which people and organizations a given user can see, and similar role-based restrictions exist across Financials and Procurement. A view built on top of secured data applies those restrictions automatically, based on the session running the query — which means the same SQL statement can legitimately return different row counts depending on whether it's run by your personal login, a BI Publisher service account, or an integration user. It's not a bug in the query. It's the security model doing exactly what it was configured to do.

This is one of the more expensive mistakes to diagnose precisely because it looks identical to a WHERE clause problem. If a query someone else wrote returns the right count for them and a smaller count for you, the join is usually fine — the account context isn't.

Business Units and Data Security

Related, but worth separating out: many core financial and procurement tables are multi-org by design — the _ALL tables mentioned above. A purchase order table doesn't store one company's data; it stores every business unit's data in the same rows, distinguished by a business unit or org ID column.

Leave that filter out and you're not looking at "all the purchase orders" in any meaningful business sense — you're looking at every legal entity's purchase orders merged together, which is rarely what anyone actually asked for. It's worth treating business unit scoping as a required filter by default, the same way you'd treat the effective-date predicate, and only removing it when a report genuinely needs a cross-BU view.

Common Relationships

Most HCM reporting questions walk the same chain of tables. It's worth having this shape in your head before you look at your first JOIN clause:

Every one of those arrows is a join, and every box except the top one is an effective-dated table in its own right — which means the date predicate isn't optional on one table in this chain, it's required on all four. Finance and procurement reporting follow the same shape with different names: a document header joins out to organizational and reference data, most of which is also effective-dated or translation-split the same way.

Keep this picture in mind and the fifty queries ahead will read less like fifty separate problems and more like the same four or five patterns applied to different tables.

Where SQL Fits in the Oracle Fusion Reporting Ecosystem

SQL is one option among several for getting data out of Oracle Fusion, not the only one. Most implementations end up using all of them, each for the part it happens to be good at. It's worth knowing the full set before deciding where a given request belongs.

• SQL

  Direct queries against the schema, run through a SQL editor or embedded in a BI Publisher data model. The right choice for debugging, reconciliation, ad hoc investigation, and anywhere you need full control over the joins and filters.

• BI Publisher

  A template-driven layer, usually backed by a SQL data model, that turns a result set into a formatted PDF, Excel, or Word document. The right choice when a report needs a fixed layout, a scheduled distribution list, or a specific document format someone downstream has to file, print, or email.

• OTBI

  Drag-and-drop analysis built on subject areas that already encode Fusion's joins and security rules for you. The right choice for self-service dashboards built by business users, or any report that fits cleanly inside one subject area without needing a workaround. See Oracle Fusion OTBI Subject Areas for how those subject areas map back to the underlying tables.

• Oracle Analytics Cloud (OAC)

  A broader BI platform that can sit on top of OTBI, a warehouse, or other sources for advanced visualization and cross-source blending. The right choice for executive-level dashboards or analytics that need to combine Fusion data with something outside Fusion entirely.

• REST APIs

  Most Fusion business objects are exposed as REST resources for both reading and writing. The right choice for near-real-time integrations, mobile apps, or anything that needs to do more than read — creating or updating records, not just reporting on them.

• SOAP Services

  Older, still-supported, and still necessary in places — a handful of Fusion operations, particularly in HCM and certain process-driven actions, are only exposed via SOAP. The right choice when that's the only interface available, or you're integrating with something that already speaks SOAP.

• BICC Extracts

  Business Intelligence Cloud Connector — bulk, scheduled extraction of Fusion data into files, typically landing in a data lake or warehouse. The right choice for moving large volumes of data on a recurring schedule without hitting the transactional database directly for every report.

These tools overlap more than the list above suggests. A BI Publisher data model is SQL. An OTBI analysis, viewed through its Advanced tab, shows you the SQL Oracle generated on your behalf. Understanding direct SQL isn't a competing skill to any of these — it's the skill that makes all of them easier to use well, because you can tell when the generated SQL is doing something you wouldn't have written yourself.

How to Approach a New Reporting Requirement

The request never arrives in schema language. It arrives as "can you get me a list of suppliers we haven't paid in 90 days" or "I need everyone who reports up through this manager, including dotted-line." Turning that into a query starts before you write any SQL at all — it starts with a handful of questions experienced consultants ask almost automatically.

• Which business object does this actually start from? "Suppliers we haven't paid" starts from the invoice, not the supplier — you're filtering suppliers by the absence of a recent invoice, which changes the shape of the query from a simple filter into a NOT EXISTS or an outer join with a null check.
• Which module owns this data? Get this wrong and you'll spend twenty minutes in the wrong schema area before anything clicks. "Manager hierarchy" is HCM. "Approval hierarchy for this PO" is Procurement, and it isn't the same hierarchy, even for the same person.
• Which tables are likely involved, and which one is the anchor? Pick the table that defines the grain of the report — one row per what? — before deciding what to join outward from it. Get the anchor wrong and every join afterward compounds the mistake.
• Which of those tables are effective-dated? Anything with _F or _M in the name needs a date predicate before it goes anywhere near a JOIN. Assume yes until you've confirmed otherwise.
• Which relationships are mandatory, and which are optional? Not every employee has a manager on file. Not every invoice has a matched PO. Decide up front which joins need to be INNER and which need to be OUTER, or you'll silently drop rows that should have appeared with nulls instead.
• Where does security or business-unit scope need to apply? Before you write a single filter, decide whether this report is meant to span every business unit or exactly one — and whether the account that eventually runs it in production has the same visibility you have while testing it.

None of this takes long once it's a habit. It takes considerably longer to discover the answers after the fact, three joins into a query that's already returning the wrong count.

A Simple SQL Development Workflow

The questions above feed into a repeatable sequence. It's not rigid — experienced developers skip steps that don't apply — but it's the order that avoids rework:

1. Understand the business question. Restate the request in your own words before touching SQL. If you can't restate it precisely, you're not ready to write the query yet.
2. Identify the primary business object. Decide what one row in your final result set actually represents — one employee, one invoice, one PO line. This is the grain, and everything else follows from it.
3. Find the core table. Locate the table that stores that business object at that grain. This is your FROM clause and your anchor for every join that follows.
4. Identify related objects. List what else the report needs — department, manager, supplier name, GL account — and which table each one lives in, before writing a single JOIN.
5. Add only the joins you actually need. Every table you bring in is a chance to change the grain of the result. If a column doesn't require a join to get, don't add one for it.
6. Handle effective dates. Apply the date predicate to every date-tracked table in the query, consistently, before you worry about anything else.
7. Apply security and business-unit filters. Add the scoping filters explicitly, even if the account you're testing with would apply them automatically — the query needs to be correct regardless of who eventually runs it.
8. Validate the result with business users. Row counts and totals should match something a person who knows the business already believes to be true. If nobody can vouch for the number, don't ship the query.
9. Optimize only after correctness. A fast query that returns the wrong answer isn't a shortcut — it's a bug that runs quickly. Tune indexes, hints, and query shape only once the logic is right.

Oracle Fusion SQL Best Practices

Some of this is general SQL discipline. Most of it is specific to what actually goes wrong on Fusion projects, learned the way most best practices are learned — by cleaning up after the query that didn't follow them.

1. Never SELECT * against a Fusion table.

   Core Fusion tables routinely carry a hundred-plus columns, including descriptive flexfield segments that mean nothing without their context. SELECT * pulls all of it, breaks quietly the next time Oracle adds a column in a quarterly update, and forces the next reader to guess which columns you actually cared about.

2. Identify the business object before writing a single join.

   Know what one output row represents before you decide what to join to it. Joining outward from the wrong anchor table is the single most common reason a query returns technically-correct rows at the wrong grain.

3. Filter effective-dated tables consistently, not selectively.

   A date predicate on one _F or _M table and not on the next one doesn't cancel out — it multiplies. Apply the same predicate everywhere it's needed, every time, as a reflex rather than a decision.

4. Verify the primary assignment, not just "active."

   An employee can have more than one active assignment on the same day. If your report assumes one row per person, check the primary flag explicitly instead of trusting that "active" already means "the one you want."

5. Validate row counts early, before you build out the rest of the report.

   Run a bare COUNT or GROUP BY against your core join before adding every column and formatting layer. Confirming the grain is right at the start saves you from debugging duplication in a query that's already half-built.

6. Prefer readability over clever SQL.

   Analytic functions and nested subqueries are genuinely useful, but if the next person can't follow the query in under a minute, it's already technical debt — regardless of how elegant it looked when you wrote it.

7. Alias tables meaningfully.

   ppf for PER_ALL_PEOPLE_F tells the next reader something. t1 tells them nothing. Meaningful aliases turn the query itself into documentation.

8. Comment the non-obvious joins.

   Not what the query does — a competent reader can see that. Comment why a specific effective-date range, or a specific flag filter, is there. That reasoning is exactly what nobody can reconstruct from the SQL alone six months later.

9. Watch for accidental cartesian joins.

   The classic version happens under deadline pressure: a table gets added to support one more column, and the ON condition gets typed a beat too fast. Row counts jump, and it's rarely obvious why until someone checks the join conditions one by one.

10. Use bind variables in production data models and integrations.

    Hardcoded dates and org IDs work fine in testing and quietly go stale in production. Bind variables also let Oracle reuse the execution plan instead of hard-parsing a new one every run.

11. Test against realistic data volumes.

    A query that returns instantly against twenty rows in a sandbox can behave completely differently against half a million rows in production. Missing indexes and inefficient join orders rarely show up until the volume does.

12. Know what's indexed before you assume a filter is fast.

    A WHERE clause that looks selective isn't necessarily cheap. If you're not sure whether a column is indexed, check ALL_INDEXES and ALL_IND_COLUMNS, or ask someone who already knows — don't assume.

13. Apply business-unit and security scope explicitly.

    Don't rely on the account you're testing with to implicitly limit what the query returns. Write the scope into the WHERE clause so the query is correct no matter which account eventually runs it in production.

14. Keep production queries maintainable, not maximal.

    Resist folding five different reporting requirements into one query that tries to serve all of them with a dozen CASE statements. Two readable queries beat one clever one that nobody wants to touch after you've moved to another project.

15. Re-validate with business users, not just against the row count.

    A plausible number and a correct number look identical until someone who knows the real answer checks it. Get that sanity check on the first version of any report, not after it's already circulated.

16. Document assumptions the query depends on.

    If the query assumes a single legal entity, a specific currency, or that terminated employees are excluded elsewhere in the pipeline, write that down next to the query. Assumptions that live only in someone's memory don't survive the first handoff.

Common Oracle Fusion Tables Reference

A working list of the tables that show up constantly, grouped by module. This is the section worth bookmarking on its own — the fifty queries ahead pull almost entirely from this list.

HCM

General Ledger

Accounts Payable

Accounts Receivable

Procurement

Inventory

Projects

Security

At this point, you understand how Oracle Fusion organizes its data, how table naming works, how effective dating affects reporting, and how experienced developers approach a new reporting request before writing a single line of SQL.

Now it's time to put that to work. The examples ahead start with the most common Oracle Fusion HCM reporting scenarios, then move into Financials, Procurement, and the cross-module and security-related queries that tend to come up once a team has the basics covered.

50 Oracle Fusion SQL Queries You Can Actually Use

Every query below started as a real request on a real project. Each one explains what it solves, why it's built the way it is, and where it tends to go wrong — not just the SELECT statement.

HCM & Workforce Reporting

These fifteen cover the workforce reporting requests that come up on nearly every HCM engagement. For a wider set of HR-focused examples once you're through these, see Oracle Fusion HCM SQL: 20 Real Queries Every HR & BI Team Should Know.

Query 1 — Retrieve Active Employees with Their Current Assignment

One of the most common requests from HR teams is a list of active employees together with their current department, job title, and assignment number. It sounds like the simplest query in this entire guide — right up until you remember that Fusion stores the person, the assignment, the department, and the job in four separate effective-dated tables, and none of them agree to call themselves "current" for free.

Why This Query Matters

This is usually the first query I run against a new Fusion environment — it tells you almost immediately whether the assignment and organization structures are set up the way the design document says they are. Beyond that, it's the base pattern behind headcount reports, most BI Publisher HR data models, and the sanity check you run before trusting any integration that claims to export "active employees."

SQL

SELECT
    ppf.person_id,
    ppnf.display_name                  AS employee_name,
    paam.assignment_number,
    hou.name                           AS department_name,
    pj.name                            AS job_name
FROM   per_all_people_f            ppf
JOIN   per_person_names_f          ppnf
       ON  ppnf.person_id       = ppf.person_id
       AND ppnf.name_type       = 'GLOBAL'
       AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
JOIN   per_all_assignments_m       paam
       ON  paam.person_id       = ppf.person_id
       AND paam.primary_flag    = 'Y'
       AND paam.assignment_type = 'E'
       AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
JOIN   hr_all_organization_units_f hou
       ON  hou.organization_id  = paam.organization_id
       AND TRUNC(SYSDATE) BETWEEN hou.effective_start_date AND hou.effective_end_date
JOIN   per_jobs_vl                 pj
       ON  pj.job_id            = paam.job_id
       AND TRUNC(SYSDATE) BETWEEN pj.effective_start_date AND pj.effective_end_date
WHERE  TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
ORDER  BY hou.name, ppnf.display_name

Understanding the Query

The department and job don't come from the person table — they come from PER_ALL_ASSIGNMENTS_M, because in Fusion those are assignment attributes, not person attributes. primary_flag = 'Y' is doing real work here: without it, an employee with a secondary assignment shows up twice, correctly, for reasons that have nothing to do with a SQL mistake. assignment_type = 'E' keeps this to employee assignments specifically, excluding contingent worker and other non-employee assignment types that live in the same table. And the job name comes from PER_JOBS_VL rather than a hand-built join to PER_JOBS_B and PER_JOBS_TL — the _VL view already does that join and filters to the session language, which is exactly the pattern from the naming conventions section earlier in this guide.

Tables Involved

Common Mistakes

• Dropping primary_flag = 'Y' to "simplify" the query — the query still runs fine, it just quietly duplicates anyone with more than one active assignment.
• Applying the effective-date predicate to PER_ALL_PEOPLE_F but forgetting it on PER_ALL_ASSIGNMENTS_M — you'll get one row per historical assignment version instead of one row per person.
• Treating this query as proof of termination status. Assignment history is usually end-dated correctly on termination, but if termination processing is delayed or backdated, cross-check PER_PERIODS_OF_SERVICE.ACTUAL_TERMINATION_DATE before using this as the source of truth for a reconciliation.

Performance Tip

Keep every effective-date predicate inside the ON clause of its join, not moved out into a later WHERE clause. I've seen this exact query slow down noticeably after someone "cleaned it up" by consolidating the date filters into one WHERE clause at the bottom — same logic on paper, but the optimizer loses the ability to filter each table down before the join runs, and it shows on larger workforces.

Related Concepts

This query leans directly on the effective-date pattern and the _VL convention covered in the Database Architecture section above, plus the "verify primary assignments" best practice. Manager hierarchy is deliberately left out here — it's involved enough to earn its own query later in this section.

Query 2 — Employees with Their Direct Manager

Query 1 left manager information out on purpose. Here's the reason, and the query that fills the gap. Manager resolution in Fusion doesn't come from a simple column on the assignment — it comes from a dedicated supervisor table, because Fusion supports more than one kind of manager relationship at once (line manager, project manager, and other matrix roles, depending on configuration).

Why This Query Matters

Almost every HCM report that mentions "manager" depends on getting this join right the first time — org charts, approval routing checks, and any HR data model that needs to show reporting lines. It's also one of the most common integration payloads, since downstream systems (learning, performance, benefits) usually need to know who someone's manager is.

SQL

SELECT
    ppf.person_id,
    ppnf.display_name                  AS employee_name,
    mgr_ppnf.display_name              AS manager_name
FROM   per_all_people_f              ppf
JOIN   per_person_names_f            ppnf
       ON  ppnf.person_id = ppf.person_id
       AND ppnf.name_type = 'GLOBAL'
       AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
JOIN   per_assignment_supervisors_f  pas
       ON  pas.person_id       = ppf.person_id
       AND pas.supervisor_type = 'LINE_MANAGER'
       AND TRUNC(SYSDATE) BETWEEN pas.effective_start_date AND pas.effective_end_date
JOIN   per_person_names_f            mgr_ppnf
       ON  mgr_ppnf.person_id = pas.supervisor_id
       AND mgr_ppnf.name_type = 'GLOBAL'
       AND TRUNC(SYSDATE) BETWEEN mgr_ppnf.effective_start_date AND mgr_ppnf.effective_end_date
WHERE  TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
ORDER  BY ppnf.display_name

Understanding the Query

PER_ASSIGNMENT_SUPERVISORS_F is effective-dated like everything else in this chain, and it can hold more than one active row per person if the implementation uses matrix or dotted-line managers. supervisor_type = 'LINE_MANAGER' narrows that down to the one relationship most reports actually mean by "manager." The manager's own name is resolved with a second pass through PER_PERSON_NAMES_F, using supervisor_id as the person ID.

Tables Involved

Common Mistakes

• Omitting supervisor_type entirely — if matrix managers exist in your instance, you'll get one row per supervisor type instead of one row per employee.
• Assuming every employee has a manager. Some don't — usually the top of the hierarchy, sometimes a data gap. Use a LEFT JOIN if the report needs to show those employees too, not drop them silently.
• Treating supervisor_type and the value 'LINE_MANAGER' as guaranteed exact spellings. The table and the general pattern are solid; the exact column name and seeded value can vary slightly by release and by how supervisor types were configured in your instance. Run a quick SELECT DISTINCT supervisor_type FROM per_assignment_supervisors_f against your own environment before relying on this filter in a production report.

Performance Tip

The self-join back through PER_PERSON_NAMES_F for the manager's name is the expensive part at scale. If a report only needs the manager's ID, not their name, skip that second join entirely.

Related Concepts

Builds directly on the effective-date pattern from Query 1. Span of control — counting reports per manager — is covered later in this section using the same table from the other direction.

Query 3 — Full-Time vs. Part-Time Headcount by Department

Finance wants a headcount number. HR wants the same number split by employment type before they'll sign off on it. This is the query that satisfies both, and it introduces the aggregation pattern you'll reuse constantly through the rest of this guide.

Why This Query Matters

Workforce planning, budget reconciliation, and benefits eligibility reporting all care about the full-time/part-time split, not just the total. It's also a fast way to validate that employment category is set up consistently across departments before a bigger report gets built on top of it.

SQL

SELECT
    hou.name                                                        AS department_name,
    SUM(CASE WHEN paam.employment_category = 'FR' THEN 1 ELSE 0 END) AS full_time_count,
    SUM(CASE WHEN paam.employment_category = 'PR' THEN 1 ELSE 0 END) AS part_time_count,
    COUNT(*)                                                        AS total_headcount
FROM   per_all_assignments_m       paam
JOIN   hr_all_organization_units_f hou
       ON  hou.organization_id = paam.organization_id
       AND TRUNC(SYSDATE) BETWEEN hou.effective_start_date AND hou.effective_end_date
WHERE  paam.primary_flag    = 'Y'
  AND  paam.assignment_type = 'E'
  AND  TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
GROUP  BY hou.name
ORDER  BY total_headcount DESC

Understanding the Query

The primary_flag and effective-date filters from Query 1 are still doing the same job — getting one row per person before anything gets aggregated. Everything else is a straightforward CASE-based pivot on employment_category, summed per department.

Tables Involved

Common Mistakes

• Hardcoding 'FR' and 'PR' without checking — employment category values are configurable lookup codes, and not every client uses the seeded ones. Query the EMP_CAT lookup type first if you're unsure.
• Forgetting primary_flag = 'Y' here specifically inflates the totals in exactly the way Query 1 already warned about.

Performance Tip

Aggregating after filtering, not before, keeps this fast even on large workforces — the WHERE clause reduces the row set before GROUP BY has to do any work.

Related Concepts

Reuses the primary-assignment pattern from Query 1. Position-based versus job-based headcount, later in this section, follows the same aggregation shape.

Query 4 — New Hires in the Last 30 Days

Onboarding coordinators tend to want a rolling list, not a one-time report: who started recently, and which department they landed in. This introduces a table that hasn't come up yet — the one that actually owns hire and termination dates.

Why This Query Matters

Feeds onboarding checklists, IT provisioning triggers, and new-hire orientation scheduling. It's also a useful validation step after a bulk hire load — a fast way to confirm the load actually created what it was supposed to.

SQL

SELECT
    ppf.person_id,
    ppnf.display_name    AS employee_name,
    pos.date_start        AS hire_date,
    hou.name               AS department_name
FROM   per_periods_of_service       pos
JOIN   per_all_people_f             ppf
       ON  ppf.person_id = pos.person_id
       AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
JOIN   per_person_names_f           ppnf
       ON  ppnf.person_id = ppf.person_id
       AND ppnf.name_type = 'GLOBAL'
       AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
JOIN   per_all_assignments_m        paam
       ON  paam.person_id      = ppf.person_id
       AND paam.primary_flag   = 'Y'
       AND paam.assignment_type = 'E'
       AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
JOIN   hr_all_organization_units_f  hou
       ON  hou.organization_id = paam.organization_id
       AND TRUNC(SYSDATE) BETWEEN hou.effective_start_date AND hou.effective_end_date
WHERE  pos.date_start >= TRUNC(SYSDATE) - 30
ORDER  BY pos.date_start DESC

Understanding the Query

PER_PERIODS_OF_SERVICE tracks employment spells — one row per period someone has worked for the company, which matters for anyone who's ever left and come back. date_start here is the actual hire date for that period, which is a more reliable source than trying to infer it from the earliest assignment row.

Tables Involved

Common Mistakes

• Reading hire date off the assignment table instead of PER_PERIODS_OF_SERVICE — it can drift after transfers or assignment changes.
• Not accounting for rehires. Someone with two periods of service will appear once per period if the date filter matches more than one — usually not an issue at 30 days, but worth knowing before widening the window.

Performance Tip

An index on date_start makes this fast even on years of history; without one, the filter still works, it just scans more than it needs to.

Related Concepts

Termination reporting, next, is the mirror image of this query using the same table.

Query 5 — Employee Terminations with Reason, by Date Range

The mirror image of Query 4 — and a good example of a query where "as of today" is the wrong filter to reach for.

Why This Query Matters

Attrition reporting, exit interview scheduling, and benefits termination triggers all depend on this. It's also one of the first places to look when a headcount reconciliation doesn't tie out — someone who left mid-period can throw off a count if the report's date logic doesn't match how HR defines the period.

SQL

SELECT
    ppf.person_id,
    ppnf.display_name              AS employee_name,
    pos.actual_termination_date,
    pos.leaving_reason
FROM   per_periods_of_service   pos
JOIN   per_all_people_f         ppf
       ON  ppf.person_id = pos.person_id
       AND pos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
JOIN   per_person_names_f       ppnf
       ON  ppnf.person_id = ppf.person_id
       AND ppnf.name_type = 'GLOBAL'
       AND pos.actual_termination_date BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
WHERE  pos.actual_termination_date BETWEEN :start_date AND :end_date
ORDER  BY pos.actual_termination_date DESC

Understanding the Query

Notice the join predicate isn't TRUNC(SYSDATE) here — it's pos.actual_termination_date. A terminated employee isn't "current" by definition, so anchoring to today's date would fail to find their name and person record entirely. This is the "record as of a specific date" pattern from the Database Architecture section, applied to a date that isn't today.

Tables Involved

Common Mistakes

• Filtering PER_ALL_PEOPLE_F and PER_PERSON_NAMES_F with TRUNC(SYSDATE) instead of the termination date — this is the single most common reason this exact query returns zero rows for people everyone knows left.
• Treating a blank leaving_reason as missing data. Sometimes it genuinely wasn't captured at the time — worth a caveat on the report rather than an assumption.

Performance Tip

Always bind the date range rather than hardcoding it — this query gets reused for month-end, quarter-end, and ad hoc windows more than almost anything else in an HR reporting suite.

Related Concepts

The as-of-date join pattern here comes back for any historical or terminated-employee reporting throughout this guide.

Query 6 — Employees Currently on an Approved Leave of Absence

A different kind of headcount question: who's technically active today but not actually at their desk, and why.

Why This Query Matters

Workforce availability planning, payroll validation during leave periods, and coverage/backfill decisions all need this list. It's also a common source of confusion in headcount reports that don't separate "active" from "actively working."

SQL

SELECT
    ppf.person_id,
    ppnf.display_name     AS employee_name,
    aat.name                AS absence_type,
    pae.start_date,
    pae.end_date
FROM   per_absence_entries_f   pae
JOIN   anc_absence_types_f     aat
       ON  aat.absence_type_id = pae.absence_type_id
       AND TRUNC(SYSDATE) BETWEEN aat.effective_start_date AND aat.effective_end_date
JOIN   per_all_people_f        ppf
       ON  ppf.person_id = pae.person_id
       AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
JOIN   per_person_names_f      ppnf
       ON  ppnf.person_id = ppf.person_id
       AND ppnf.name_type = 'GLOBAL'
       AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
WHERE  pae.approval_status = 'APPROVED'
  AND  TRUNC(SYSDATE) BETWEEN pae.start_date AND NVL(pae.end_date, TRUNC(SYSDATE))
ORDER  BY pae.start_date

Understanding the Query

The start_date/end_date on the absence entry describe the leave itself — when it begins and ends — which is a different pair of dates from any effective-dating on the row. Don't confuse the two: one describes the absence, the other describes whether this version of the record is the current one. NVL(pae.end_date, TRUNC(SYSDATE)) handles open-ended leaves that don't have a confirmed return date yet.

Tables Involved

Common Mistakes

• Including unapproved or pending absence requests — always filter approval_status unless the report is specifically about the approval queue itself.
• Missing open-ended leaves by filtering on end_date alone without the NVL fallback.

Performance Tip

Filter approval_status before the date range in the WHERE clause — on most instances it's the more selective filter of the two.

Related Concepts

A reminder that not every date pair in Fusion is an effective-date pair — a theme worth watching for outside HCM too.

Query 7 — Employees Without a Manager on File (Data Quality Check)

Not every report answers a business question directly — some exist to prove the data behind the other reports can be trusted. This is one of those.

Why This Query Matters

Run this before trusting any org-chart or approval-routing report. A handful of missing manager assignments is normal — usually the CEO, sometimes a data gap from a recent reorg. A large number usually means something upstream broke.

SQL

SELECT
    ppf.person_id,
    ppnf.display_name    AS employee_name,
    hou.name               AS department_name
FROM   per_all_people_f            ppf
JOIN   per_person_names_f          ppnf
       ON  ppnf.person_id = ppf.person_id
       AND ppnf.name_type = 'GLOBAL'
       AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
JOIN   per_all_assignments_m       paam
       ON  paam.person_id      = ppf.person_id
       AND paam.primary_flag   = 'Y'
       AND paam.assignment_type = 'E'
       AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
JOIN   hr_all_organization_units_f hou
       ON  hou.organization_id = paam.organization_id
       AND TRUNC(SYSDATE) BETWEEN hou.effective_start_date AND hou.effective_end_date
WHERE  TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
  AND  NOT EXISTS (
           SELECT 1
           FROM   per_assignment_supervisors_f pas
           WHERE  pas.person_id       = ppf.person_id
             AND  pas.supervisor_type = 'LINE_MANAGER'
             AND  TRUNC(SYSDATE) BETWEEN pas.effective_start_date AND pas.effective_end_date
       )
ORDER  BY hou.name

Understanding the Query

NOT EXISTS rather than a LEFT JOIN ... IS NULL here is a style choice more than a requirement — both work, but NOT EXISTS reads more clearly as "this specific condition should not exist for this person," which matches what the query is actually checking for.

Tables Involved

Common Mistakes

• Forgetting the effective-date predicate inside the subquery — an easy miss, and it makes the check silently wrong for anyone whose manager relationship changed recently.
• Treating every result row as an error. Some are expected (the top of the hierarchy); confirm with HR before escalating the full list as a data issue.

Performance Tip

NOT EXISTS generally outperforms NOT IN for this kind of check, and avoids the null-handling trap that NOT IN has against a column that can contain nulls.

Related Concepts

Query 15, at the end of this section, extends this same audit approach to job, grade, and manager together.

Query 8 — Employee Work Location Report

A simpler one. Facilities and safety teams periodically need to know exactly who's assigned to which physical location — useful for anything from space planning to emergency headcounts.

Why This Query Matters

Comes up constantly for facilities planning, and again during any compliance or safety exercise that needs an accurate site-by-site headcount.

SQL

SELECT
    ppf.person_id,
    ppnf.display_name    AS employee_name,
    hla.location_code,
    hla.address_line_1,
    hla.town_or_city
FROM   per_all_assignments_m       paam
JOIN   per_all_people_f            ppf
       ON  ppf.person_id = paam.person_id
       AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
JOIN   per_person_names_f          ppnf
       ON  ppnf.person_id = ppf.person_id
       AND ppnf.name_type = 'GLOBAL'
       AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
JOIN   hr_locations_all_f          hla
       ON  hla.location_id = paam.location_id
       AND TRUNC(SYSDATE) BETWEEN hla.effective_start_date AND hla.effective_end_date
WHERE  paam.primary_flag    = 'Y'
  AND  paam.assignment_type = 'E'
  AND  TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
ORDER  BY hla.location_code

Understanding the Query

Nothing new here beyond swapping the department join from Query 1 for a location join — which is exactly the point. Once the base pattern is familiar, most single-attribute reports like this one are a five-minute change, not a new query written from scratch.

Tables Involved

Common Mistakes

• Assuming location_id is always populated. Remote or newly created assignments sometimes leave it blank — use a LEFT JOIN if the report needs to surface those gaps rather than silently exclude them.

Performance Tip

No surprises here — the same indexing advice from Query 1 applies, since the join shape is identical.

Related Concepts

A direct variant of Query 1 — worth noticing how little changes when you swap one reference table for another.

Query 9 — Headcount by Grade

Compensation planning cycles usually start with this exact number: how many people sit at each grade, before anyone starts talking about salary bands.

Why This Query Matters

Feeds annual compensation planning and grade-structure reviews. It's also a quick way to spot grade assignments that look wrong at a glance — a grade with one person in it, for instance, is often worth a second look.

SQL

SELECT
    pgv.name              AS grade_name,
    COUNT(*)                AS headcount
FROM   per_all_assignments_m   paam
JOIN   per_grades_vl           pgv
       ON  pgv.grade_id = paam.grade_id
       AND TRUNC(SYSDATE) BETWEEN pgv.effective_start_date AND pgv.effective_end_date
WHERE  paam.primary_flag    = 'Y'
  AND  paam.assignment_type = 'E'
  AND  TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
GROUP  BY pgv.name
ORDER  BY headcount DESC

Understanding the Query

PER_GRADES_VL follows the same base-plus-translation pattern as PER_JOBS_VL in Query 1 — another reminder that once you recognize the _VL convention once, it applies everywhere the same way.

Tables Involved

Common Mistakes

• Grouping by grade_id instead of the resolved name when the report is meant for a business audience — harmless technically, unreadable to anyone who isn't already looking at the schema.
• Not every assignment has a grade populated, particularly in implementations that lean on position-based structures instead. INNER JOIN here will drop those silently — decide if that's actually what the report needs.

Performance Tip

Same shape as Query 3 — filter first, aggregate second.

Related Concepts

The grade-versus-position gap mentioned above is exactly what the next query covers directly.

Query 10 — Position-Based vs. Job-Based Assignments

Not every Fusion implementation uses positions. Some manage headcount purely by job; others control it tightly through defined positions, each with its own budget and incumbent limit. Before building any report that assumes one model or the other, it's worth checking which one you're actually dealing with.

Why This Query Matters

This is a configuration discovery query as much as a reporting one — useful early on any new implementation or handoff, before you assume position-based headcount control is or isn't in play.

SQL

SELECT
    hou.name                                                       AS department_name,
    COUNT(*)                                                         AS total_assignments,
    SUM(CASE WHEN paam.position_id IS NOT NULL THEN 1 ELSE 0 END)   AS position_based,
    SUM(CASE WHEN paam.position_id IS NULL THEN 1 ELSE 0 END)       AS job_based_only
FROM   per_all_assignments_m       paam
JOIN   hr_all_organization_units_f hou
       ON  hou.organization_id = paam.organization_id
       AND TRUNC(SYSDATE) BETWEEN hou.effective_start_date AND hou.effective_end_date
WHERE  paam.primary_flag    = 'Y'
  AND  paam.assignment_type = 'E'
  AND  TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
GROUP  BY hou.name
ORDER  BY total_assignments DESC

Understanding the Query

The logic is a single check: does this assignment carry a position_id or not. A mixed result — some departments position-based, others not — isn't unusual and isn't necessarily wrong; some organizations deliberately run position control in regulated or unionized departments only.

Tables Involved

Common Mistakes

• Assuming the whole enterprise uses one model uniformly, then writing reports elsewhere that break for the departments that don't match that assumption.

Performance Tip

Cheap to run — no additional tables, just a conditional aggregate on a column already in the assignment table.

Related Concepts

Worth running once at the start of any new engagement, alongside the naming-convention groundwork from the Database Architecture section.

Query 11 — Employee Length of Service (Tenure) Report

HR business partners ask for this constantly, usually ahead of a service-recognition program or a retention analysis, and it's a good example of doing date arithmetic correctly instead of approximately.

Why This Query Matters

Feeds service-anniversary programs, retention and attrition analysis by tenure band, and eligibility checks for tenure-based benefits.

SQL

SELECT
    ppf.person_id,
    ppnf.display_name                                     AS employee_name,
    pos.date_start                                          AS hire_date,
    TRUNC(MONTHS_BETWEEN(SYSDATE, pos.date_start) / 12)    AS years_of_service
FROM   per_periods_of_service   pos
JOIN   per_all_people_f         ppf
       ON  ppf.person_id = pos.person_id
       AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
JOIN   per_person_names_f       ppnf
       ON  ppnf.person_id = ppf.person_id
       AND ppnf.name_type = 'GLOBAL'
       AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
WHERE  pos.actual_termination_date IS NULL
ORDER  BY years_of_service DESC

Understanding the Query

MONTHS_BETWEEN divided by 12 and truncated gives whole years of service — simple, but worth writing deliberately rather than approximating with SYSDATE - hire_date divided by 365, which drifts over leap years and long tenures. actual_termination_date IS NULL scopes this to people still currently employed.

Tables Involved

Common Mistakes

• Using SYSDATE - hire_date and dividing by 365 — close enough for a quick check, not for anything going in front of finance.
• Not filtering out terminated periods, which pulls former employees into a report that's supposed to be about the current workforce.

Performance Tip

The date math runs per row regardless of index support — if this feeds a dashboard that's queried often, consider computing tenure once in a view rather than in every consuming report.

Related Concepts

Uses the same anchor table as new-hire and termination reporting earlier in this section — three different questions, one table.

Query 12 — Employees Holding More Than One Active Assignment

Query 1 filtered secondary assignments out on purpose. This query finds them on purpose — useful whenever someone needs to know exactly who has more than one active role right now, and why.

Why This Query Matters

Multiple concurrent assignments show up in shared-services and academic-style organizations more than most implementations expect, and they're worth auditing periodically — both for payroll accuracy and to catch data entry mistakes that created an assignment nobody meant to leave open.

SQL

SELECT
    ppf.person_id,
    ppnf.display_name    AS employee_name,
    COUNT(*)                AS active_assignment_count
FROM   per_all_assignments_m       paam
JOIN   per_all_people_f            ppf
       ON  ppf.person_id = paam.person_id
       AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
JOIN   per_person_names_f          ppnf
       ON  ppnf.person_id = ppf.person_id
       AND ppnf.name_type = 'GLOBAL'
       AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
WHERE  paam.assignment_type = 'E'
  AND  TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
GROUP  BY ppf.person_id, ppnf.display_name
HAVING COUNT(*) > 1
ORDER  BY active_assignment_count DESC

Understanding the Query

This is Query 1 with primary_flag = 'Y' deliberately removed and a HAVING COUNT(*) > 1 added instead — the exact inverse of the filter that made Query 1 correct. Seeing both versions side by side is usually what makes the primary-flag concept click for someone new to Fusion.

Tables Involved

Common Mistakes

• Forgetting the effective-date filter here specifically — without it, this query returns almost everyone, since it'll count historical assignment versions as if they were concurrent.

Performance Tip

The HAVING clause runs after aggregation, so keep the WHERE filters as tight as possible beforehand — there's no way to push a row-count condition earlier in the plan.

Related Concepts

Pairs directly with Query 1 — worth reading the two side by side if the primary-assignment concept still feels abstract.

Query 13 — Span of Control: Direct Reports per Manager

Once you can resolve a manager reliably (Query 2), a natural follow-up question is how many direct reports each manager actually has — a number that shows up in almost every organizational design review.

Why This Query Matters

Used in org design reviews, manager workload analysis, and as an input to succession and promotion planning discussions.

SQL

SELECT
    mgr_ppnf.display_name   AS manager_name,
    COUNT(*)                  AS direct_report_count
FROM   per_assignment_supervisors_f pas
JOIN   per_person_names_f           mgr_ppnf
       ON  mgr_ppnf.person_id = pas.supervisor_id
       AND mgr_ppnf.name_type = 'GLOBAL'
       AND TRUNC(SYSDATE) BETWEEN mgr_ppnf.effective_start_date AND mgr_ppnf.effective_end_date
WHERE  pas.supervisor_type = 'LINE_MANAGER'
  AND  TRUNC(SYSDATE) BETWEEN pas.effective_start_date AND pas.effective_end_date
GROUP  BY mgr_ppnf.display_name
ORDER  BY direct_report_count DESC

Understanding the Query

This is Query 2 flipped around the other axis — instead of resolving one manager per employee, it groups by manager and counts how many employees point back to them. Same table, same filters, opposite direction.

Tables Involved

Common Mistakes

• Grouping by supervisor_id alone without joining to a name — readable to you while you're testing it, meaningless to whoever receives the report.
• Forgetting that a manager who is also a direct report to someone else won't show up as their own row here — this counts reports received, not reports given.

Performance Tip

Identical performance profile to Query 2 — the aggregation adds negligible cost on top of the join itself.

Related Concepts

Read alongside Query 2 — two views of the same relationship, approached from opposite ends.

Query 14 — Detecting Rehired Employees (Multiple Periods of Service)

People leave and come back more often than most reporting suites are built to handle gracefully. This query finds everyone with more than one period of service — a signal that's easy to miss if a report assumes one hire date per person.

Why This Query Matters

Rehires quietly break reports that weren't designed with them in mind — tenure calculations, benefits eligibility resets, and anniversary programs all need to know whether they're looking at total service or service since the most recent hire date.

SQL

SELECT
    ppf.person_id,
    ppnf.display_name       AS employee_name,
    COUNT(*)                  AS periods_of_service_count,
    MIN(pos.date_start)       AS first_hire_date,
    MAX(pos.date_start)       AS most_recent_hire_date
FROM   per_periods_of_service   pos
JOIN   per_all_people_f         ppf
       ON  ppf.person_id = pos.person_id
       AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
JOIN   per_person_names_f       ppnf
       ON  ppnf.person_id = ppf.person_id
       AND ppnf.name_type = 'GLOBAL'
       AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
GROUP  BY ppf.person_id, ppnf.display_name
HAVING COUNT(*) > 1
ORDER  BY periods_of_service_count DESC

Understanding the Query

Same HAVING COUNT(*) > 1 pattern as Query 12, applied to a different table. PER_PERIODS_OF_SERVICE holds one row per distinct employment spell, so more than one row per person means exactly one thing: this person left and was rehired at least once.

Tables Involved

Common Mistakes

• Assuming first_hire_date is what shows up elsewhere in the system as "hire date." Depending on configuration, some reports intentionally show the most recent hire date instead — confirm which one the business expects before publishing tenure numbers built on this.

Performance Tip

This table is small relative to most others in this guide, so this query is inexpensive even without any special tuning.

Related Concepts

Worth running before trusting the tenure report in Query 11 at an organization with any meaningful rehire history.

Query 15 — Active Employees with Incomplete HR Setup (Data Quality Audit)

One last HCM query, and it's less about answering a business question than making sure the previous fourteen answers can be trusted. It pulls together the job, grade, and manager checks from earlier in this section into a single audit.

Why This Query Matters

Worth running on a schedule, not just once — incomplete assignment data has a way of creeping back in after every reorg, bulk load, or new-hire batch, and it's much cheaper to catch here than in a report someone downstream is already relying on.

SQL

SELECT
    ppf.person_id,
    ppnf.display_name        AS employee_name,
    CASE WHEN paam.job_id IS NULL THEN 'Missing Job' END       AS job_gap,
    CASE WHEN paam.grade_id IS NULL THEN 'Missing Grade' END   AS grade_gap,
    CASE WHEN NOT EXISTS (
        SELECT 1 FROM per_assignment_supervisors_f pas
        WHERE  pas.person_id       = ppf.person_id
          AND  pas.supervisor_type = 'LINE_MANAGER'
          AND  TRUNC(SYSDATE) BETWEEN pas.effective_start_date AND pas.effective_end_date
    ) THEN 'Missing Manager' END                                AS manager_gap
FROM   per_all_people_f            ppf
JOIN   per_person_names_f          ppnf
       ON  ppnf.person_id = ppf.person_id
       AND ppnf.name_type = 'GLOBAL'
       AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
JOIN   per_all_assignments_m       paam
       ON  paam.person_id      = ppf.person_id
       AND paam.primary_flag   = 'Y'
       AND paam.assignment_type = 'E'
       AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
WHERE  TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
  AND  (paam.job_id IS NULL OR paam.grade_id IS NULL
        OR NOT EXISTS (
             SELECT 1 FROM per_assignment_supervisors_f pas
             WHERE  pas.person_id       = ppf.person_id
               AND  pas.supervisor_type = 'LINE_MANAGER'
               AND  TRUNC(SYSDATE) BETWEEN pas.effective_start_date AND pas.effective_end_date
           ))
ORDER  BY ppnf.display_name

Understanding the Query

Each CASE expression flags one specific gap rather than collapsing everything into a single generic "incomplete" flag — whoever receives this report needs to know exactly what to go fix, not just that something's wrong. The repeated NOT EXISTS subquery is the same check from Query 7, used here twice for the flag and once again in the WHERE clause to decide inclusion.

Tables Involved

Common Mistakes

• Treating every flagged row as a hard error. Some job-based implementations legitimately leave grade blank, and some organizations genuinely have people without a manager on file by design — validate the pattern with HR before broadcasting it as a defect list.

Performance Tip

Running the same NOT EXISTS subquery twice — once for the flag, once for inclusion — is easy to simplify with a CTE if this becomes a scheduled job rather than an ad hoc check.

Related Concepts

A synthesis of Queries 1, 7, 9, and 10 — if any of those felt unfamiliar, this is the query that will expose it. With the HCM patterns established, the next section moves into General Ledger, Accounts Payable, and Accounts Receivable, where the same effective-dating discipline applies to a different set of tables.

Accounts Payable & Suppliers

Financials modules follow a different shape than HCM, but the underlying discipline is the same kind of pattern-recognition. Instead of effective-dated history, most of what shows up here is a header/line/distribution hierarchy: one document header, one or more lines underneath it, and one or more GL distributions underneath each line. Get comfortable with that three-level shape and most of Oracle Fusion Financials starts to look familiar before you've even opened a table. The Oracle Fusion Finance SQL Guide goes deeper into GL, AP, AR, and SLA reporting than these fifteen queries alone.

Query 16 — Active Suppliers and Their Payment Terms

A procurement or AP team's most basic ask: which suppliers are currently active, and what payment terms are set up at each of their sites. Simple on its own, but it's also the first Financials query in this guide, so it's worth pointing out what's already familiar about it.

Why This Query Matters

Feeds supplier master data validation, onboarding checklists for new suppliers, and any AP report that needs to show terms alongside invoice data.

SQL

SELECT
    s.vendor_id,
    s.vendor_name,
    ss.vendor_site_code,
    apt.name                AS payment_terms,
    ss.org_id
FROM   poz_suppliers            s
JOIN   poz_supplier_sites_all_m ss
       ON  ss.vendor_id = s.vendor_id
       AND TRUNC(SYSDATE) BETWEEN ss.effective_start_date AND ss.effective_end_date
LEFT JOIN ap_terms_tl           apt
       ON  apt.term_id  = ss.payment_terms_id
       AND apt.language = 'US'
WHERE  s.status = 'ACTIVE'
ORDER  BY s.vendor_name, ss.vendor_site_code

Understanding the Query

Supplier sites are effective-dated (POZ_SUPPLIER_SITES_ALL_M) even though the supplier header isn't — the same layered pattern from HCM, just applied one level down. And the LANGUAGE = 'US' filter on AP_TERMS_TL is the exact _TL translation pattern from the Database Architecture section, showing up again outside HCM entirely.

Tables Involved

Common Mistakes

• Hardcoding 'ACTIVE' without checking — like employment category in HCM, supplier status values are configurable. Confirm the actual seeded value in your instance.
• Using AP_SUPPLIERS instead of POZ_SUPPLIERS. Both can exist in the same environment; the unified Procurement model is the more current one to build new reports against.

Performance Tip

Keep the terms join LEFT rather than INNER — a site without terms configured yet shouldn't silently disappear from a supplier audit.

Related Concepts

The _TL pattern here is identical to PER_JOBS_TL from the HCM section — once you've seen it once, you'll recognize it everywhere in Fusion.

Query 17 — Open AP Invoices Awaiting Payment

The question every AP manager asks at some point during the week: what's still open, and how much do we owe on it right now.

Why This Query Matters

Feeds cash forecasting, payment run preparation, and the weekly AP status meeting almost every finance team has in some form.

SQL

SELECT
    ai.invoice_id,
    ai.invoice_num,
    s.vendor_name,
    ai.invoice_amount,
    ai.invoice_amount - NVL(ai.amount_paid, 0) AS amount_remaining,
    ai.terms_date
FROM   ap_invoices_all ai
JOIN   poz_suppliers    s ON s.vendor_id = ai.vendor_id
WHERE  ai.payment_status_flag <> 'Y'
  AND  ai.invoice_amount > NVL(ai.amount_paid, 0)
  AND  ai.org_id = :business_unit_id
ORDER  BY ai.terms_date

Understanding the Query

AP_INVOICES_ALL carries the _ALL suffix for the same reason PO_HEADERS_ALL does — it spans every business unit, so org_id isn't optional here. I've seen this exact query return the wrong total more than once simply because that filter was left off during testing and nobody noticed until the numbers didn't match what one specific business unit expected.

Tables Involved

Common Mistakes

• Filtering only on payment_status_flag and skipping the amount comparison — a partially paid invoice can carry a status that doesn't obviously say "open" depending on configuration.
• Leaving out the org_id filter, as above.

Performance Tip

If this feeds a dashboard refreshed frequently, filter on payment_status_flag first — it's typically the more selective predicate on a large invoice table.

Related Concepts

The mirror image of this query on the receivables side — open customer invoices — shows up later in this section, built the same way.

Query 18 — AP Invoice Line and Distribution Detail

Zoom in one level. This is the query you reach for when someone asks "what's actually on this invoice" and means it literally — every line, and every GL account each line was distributed to.

Why This Query Matters

The standard drill-down from a summary AP report, and the starting point for investigating why an invoice posted to an account nobody expected.

SQL

SELECT
    ail.line_number,
    ail.description,
    ail.amount                        AS line_amount,
    aid.distribution_line_number,
    aid.amount                        AS distribution_amount,
    gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 AS gl_account
FROM   ap_invoice_lines_all         ail
JOIN   ap_invoice_distributions_all aid
       ON  aid.invoice_id           = ail.invoice_id
       AND aid.invoice_line_number  = ail.line_number
JOIN   gl_code_combinations         gcc
       ON  gcc.code_combination_id = aid.dist_code_combination_id
WHERE  ail.invoice_id = :invoice_id
ORDER  BY ail.line_number, aid.distribution_line_number

Understanding the Query

This is the header/line/distribution shape mentioned above, made concrete: one invoice line can split across several distributions if the cost needs to be allocated across more than one account. The three-segment concatenation is illustrative — real charts of accounts vary in segment count and structure, so treat this as a placeholder for however many segments your ledger actually uses.

Tables Involved

Common Mistakes

• Assuming one distribution per line. Tax, freight, and cost allocations routinely create more than one distribution row per line.
• Hardcoding three segments in the account concatenation when your chart of accounts uses a different number.

Performance Tip

Always scope this to one invoice, as shown — running it unfiltered across the full distributions table is one of the more expensive mistakes possible in AP reporting.

Related Concepts

The header/line/distribution pattern introduced here reappears identically in the Accounts Receivable and Procurement sections.

Query 19 — Invoices Matched to Purchase Orders vs. Not Matched

Not every invoice traces back to a PO, and that's fine — but it's exactly the kind of optional relationship the earlier section on approaching a new requirement warned about. Get the join type wrong here and you either lose the unmatched invoices or lose the matched ones.

Why This Query Matters

Used in three-way match auditing, spend-under-management analysis, and identifying suppliers being paid consistently outside the procurement process.

SQL

SELECT
    ai.invoice_num,
    s.vendor_name,
    ail.line_number,
    ail.amount,
    pol.po_header_id,
    CASE WHEN pol.po_header_id IS NULL
         THEN 'Not Matched' ELSE 'PO Matched' END AS match_status
FROM   ap_invoices_all       ai
JOIN   poz_suppliers          s   ON s.vendor_id = ai.vendor_id
JOIN   ap_invoice_lines_all   ail ON ail.invoice_id = ai.invoice_id
LEFT JOIN po_lines_all        pol ON pol.po_line_id = ail.po_line_id
WHERE  ai.org_id = :business_unit_id
ORDER  BY match_status, ai.invoice_num

Understanding the Query

The LEFT JOIN to PO_LINES_ALL is deliberate — an INNER JOIN here would quietly drop every non-PO invoice from the result set, which defeats the entire purpose of a query meant to find them. The CASE expression just makes that NULL explicit and readable instead of leaving it for someone else to interpret.

Tables Involved

Common Mistakes

• Using INNER JOIN here "to keep it simple" — the single most common way this exact report gets silently wrong.

Performance Tip

No special tuning needed beyond the standard org_id scoping — the LEFT JOIN itself doesn't cost meaningfully more than the INNER version would.

Related Concepts

The mandatory-versus-optional relationship judgment call from the "How to Approach a New Reporting Requirement" section, applied directly.

Query 20 — Supplier Payment History

Once an invoice is paid, the question shifts from "what do we owe" to "when did we pay it, and how."

Why This Query Matters

Supplier inquiries ("has this been paid yet"), payment audit trails, and bank reconciliation all start here.

SQL

SELECT
    s.vendor_name,
    c.check_number,
    c.check_date,
    aip.amount           AS amount_applied,
    ai.invoice_num
FROM   ap_invoice_payments_all aip
JOIN   ap_invoices_all         ai ON ai.invoice_id = aip.invoice_id
JOIN   poz_suppliers           s  ON s.vendor_id = ai.vendor_id
JOIN   ap_checks_all           c  ON c.check_id = aip.check_id
WHERE  c.check_date BETWEEN :start_date AND :end_date
ORDER  BY c.check_date DESC

Understanding the Query

Don't let the table name fool you — AP_CHECKS_ALL covers every payment method Fusion supports, not just physical checks. Wire transfers and ACH payments post through the same table under the same "check" terminology, a naming holdover that trips up people expecting a separate table per payment type.

Tables Involved

Common Mistakes

• Assuming one payment per invoice. Partial payments create multiple rows in AP_INVOICE_PAYMENTS_ALL for the same invoice.

Performance Tip

Index usage on check_date matters more here than it looks — this table accumulates fast in a mature environment, and an unbounded date range will feel it.

Related Concepts

Cash receipts applied to customer invoices, later in this section, is the same idea from the receivables side.

Query 21 — AP Invoices Overdue by Payment Terms

A small variation on Query 17, and worth including on its own because it introduces the aging calculation you'll see again, in a heavier form, on the receivables side.

Why This Query Matters

Late payments affect supplier relationships and, in some cases, early-payment discount eligibility. This is the list procurement and AP review together before it becomes a supplier escalation.

SQL

SELECT
    s.vendor_name,
    ai.invoice_num,
    ai.terms_date,
    ai.invoice_amount - NVL(ai.amount_paid, 0) AS amount_remaining,
    TRUNC(SYSDATE) - ai.terms_date              AS days_overdue
FROM   ap_invoices_all ai
JOIN   poz_suppliers    s ON s.vendor_id = ai.vendor_id
WHERE  ai.payment_status_flag <> 'Y'
  AND  ai.terms_date < TRUNC(SYSDATE)
  AND  ai.invoice_amount > NVL(ai.amount_paid, 0)
ORDER  BY days_overdue DESC

Understanding the Query

days_overdue is nothing more than today's date minus the due date — simple arithmetic, but the query only means what it says once every other filter around it is correct, which is why it builds directly on Query 17 instead of starting fresh.

Tables Involved

Common Mistakes

• Sorting by invoice amount instead of days overdue when the report's actual purpose is escalation, not size.

Performance Tip

Nothing beyond what Query 17 already covers — this is the same filtered set with one additional computed column.

Related Concepts

The single-bucket version of the aging logic that gets a full multi-bucket treatment in the AR Aging query later in this section.

General Ledger

Everything upstream — every AP invoice, every AR transaction, every payroll run — eventually lands here as a journal entry. The GL tables are comparatively simple once you know the shape: headers, lines, and a running balance table that exists specifically so nobody has to sum thousands of lines just to answer "what's the balance."

Query 22 — Journal Entries for a Specific Accounting Period

The most basic GL question there is: what got posted in this period, and to which accounts.

Why This Query Matters

The starting point for period-close review, journal audit, and any investigation into an account balance that moved unexpectedly.

SQL

SELECT
    jh.je_header_id,
    jh.name                AS journal_name,
    jh.period_name,
    jl.je_line_num,
    jl.accounted_dr,
    jl.accounted_cr,
    gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 AS gl_account
FROM   gl_je_headers        jh
JOIN   gl_je_lines          jl  ON jl.je_header_id = jh.je_header_id
JOIN   gl_code_combinations gcc ON gcc.code_combination_id = jl.code_combination_id
WHERE  jh.period_name = :period_name
  AND  jh.status       = 'P'
ORDER  BY jh.je_header_id, jl.je_line_num

Understanding the Query

The header/line shape here is the same one from AP, just without the third distribution level — a journal line already carries its own account and amount directly. status = 'P' restricts this to posted journals; drop it deliberately, not accidentally, if the report needs to include unposted activity too.

Tables Involved

Common Mistakes

• Forgetting the status filter and mixing unposted, posted, and rejected journals into one total.
• Summing this directly to get a period balance instead of using GL_BALANCES — technically correct, needlessly slow on a period with heavy activity.

Performance Tip

period_name is almost always indexed on GL_JE_HEADERS; filtering on it first keeps this fast even on ledgers with years of history.

Related Concepts

That second common mistake is exactly why the next query exists.

Query 23 — Trial Balance by Account, via GL_BALANCES

The query above works for a handful of journals. It's the wrong tool once you need a full trial balance across every account in a ledger — that's what the balances table is for.

Why This Query Matters

The core of month-end close reporting, and usually the first thing controllers ask for once a period is ready to review.

SQL

SELECT
    gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 AS gl_account,
    gb.period_name,
    gb.begin_balance_dr,
    gb.begin_balance_cr,
    gb.period_net_dr,
    gb.period_net_cr
FROM   gl_balances          gb
JOIN   gl_code_combinations gcc ON gcc.code_combination_id = gb.code_combination_id
WHERE  gb.period_name  = :period_name
  AND  gb.actual_flag  = 'A'
ORDER  BY gl_account

Understanding the Query

actual_flag = 'A' matters more than it looks — the same table also stores budget ('B') and encumbrance ('E') balances side by side. Skip that filter and a trial balance report silently blends actuals with budget figures, which is a much harder mistake to spot than a wrong row count.

Tables Involved

Common Mistakes

• Forgetting actual_flag, as above.
• Comparing this to a sum of GL_JE_LINES and expecting an exact match without accounting for encumbrances or budget journals also feeding the ledger.

Performance Tip

This is exactly why GL_BALANCES exists — orders of magnitude faster than aggregating journal lines for the same answer.

Related Concepts

Pairs with Query 22 — use journal detail to explain a movement, and balances to confirm the period total.

Query 24 — Unposted or Error Journal Batches

Before trusting any balance, it's worth knowing what hasn't made it into that balance yet.

Why This Query Matters

Standard part of period-close checklists — nothing should close with journals sitting in an unposted or error state unless someone's made that call deliberately.

SQL

SELECT
    jh.je_header_id,
    jh.name         AS journal_name,
    jh.period_name,
    jh.status,
    jh.je_source,
    jh.je_category
FROM   gl_je_headers jh
WHERE  jh.status <> 'P'
ORDER  BY jh.period_name, jh.je_source

Understanding the Query

Deliberately simple — this is an operational check, not a financial report, so it doesn't need the account-level detail from Query 22. je_source and je_category are usually enough to tell someone which subledger or process the stuck batch came from.

Tables Involved

Common Mistakes

• Treating every non-posted status the same. Some are simply awaiting a scheduled posting run, not actually in error — confirm what your instance's status codes mean before escalating.

Performance Tip

Cheap by nature — header-only, no line-level join.

Related Concepts

Run this alongside the period status check that follows — together they're most of a pre-close sanity check.

Query 25 — Checking Period Status Before Trusting a Report

A small query that prevents a specific, recurring kind of embarrassment: presenting numbers for a period that's still open, then having to explain why they changed the next day.

Why This Query Matters

I've learned to run this before pulling any period-end number for someone outside the finance team. An open period isn't wrong, exactly — it's just not finished, and reports built on it can shift without warning.

SQL

SELECT
    gp.period_name,
    gp.start_date,
    gp.end_date,
    gps.closing_status
FROM   gl_periods         gp
JOIN   gl_period_statuses gps
       ON  gps.period_name    = gp.period_name
       AND gps.application_id = 101
WHERE  gp.period_set_name = :period_set_name
ORDER  BY gp.start_date DESC

Understanding the Query

application_id = 101 scopes this to General Ledger specifically — the same period-status table tracks status across multiple applications, so this filter matters. If you're ever unsure of an application ID in your instance, it's worth confirming against FND_APPLICATION rather than assuming.

Tables Involved

Common Mistakes

• Assuming a period is closed just because it's in the past — adjustment periods and late-closing periods are more common than most non-finance stakeholders expect.

Performance Tip

Negligible cost — this table is small and this is exactly the kind of check worth running habitually rather than skipping to save a query.

Related Concepts

Worth pairing with Query 24 as a standard pre-close or pre-report checklist.

Cross-Module Reconciliation: Connecting Subledgers to the Ledger

Query 26 — Reconciling an AP Invoice Distribution to Its Subledger Accounting Entry

The most advanced query in this batch, and it comes with an unusually direct caveat about where it stops.

Why This Query Matters

Reconciliation between a subledger (AP, AR, Costing) and the GL is one of the most common audit and month-end tasks in a Fusion Financials implementation — and one of the least documented in a single, coherent place.

SQL

SELECT
    aid.invoice_id,
    aid.distribution_line_number,
    aid.amount           AS distribution_amount,
    xal.ae_header_id,
    xal.ae_line_num,
    xal.accounted_dr,
    xal.accounted_cr,
    xah.accounting_date
FROM   ap_invoice_distributions_all aid
JOIN   xla_distribution_links       xdl
       ON  xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
       AND xdl.source_distribution_type      = 'AP_INV_DIST'
JOIN   xla_ae_lines                 xal
       ON  xal.ae_header_id = xdl.ae_header_id
       AND xal.ae_line_num  = xdl.ae_line_num
JOIN   xla_ae_headers               xah
       ON  xah.ae_header_id = xal.ae_header_id
WHERE  aid.invoice_id = :invoice_id
ORDER  BY xal.ae_line_num

Understanding the Query

This traces an AP distribution to its Subledger Accounting entry — XLA_AE_HEADERS and XLA_AE_LINES are where Oracle builds the actual accounting for a transaction before it's transferred to GL. At that point you already have the accounted debit and credit that will post. The remaining hop — from the subledger accounting entry to the specific GL_JE_LINES row it became — typically runs through GL_IMPORT_REFERENCES, which stores a back-reference from an imported journal line to its source. I'm deliberately not asserting the exact join columns for that last hop here: they're consistent in principle but worth confirming against your own instance, or against Oracle's seeded subledger drill-down, rather than trusting a guessed column name in a reconciliation query.

Tables Involved

Common Mistakes

• Assuming this pattern is identical for every subledger event class. AP invoice distributions are one of the more straightforward cases — AR and Costing can involve additional accounting events on the same source transaction.
• Treating a guessed final join to GL_JE_LINES as reliable. If a reconciliation report needs to go that last step, validate the exact linkage against a known-good example in your environment first.

Performance Tip

Always scope this to a single invoice or a small batch, as shown — the XLA tables are large in any mature instance, and this is not a query to run unfiltered.

Related Concepts

Builds on the AP distribution structure from Query 18, and on the same editorial principle running through this whole guide: a query that stops one join short of a guess is worth more than one that completes the chain incorrectly.

Accounts Receivable & Collections

Query 27 — Open Customer Invoices and Balances Due

The receivables mirror of Query 17. If that one felt familiar by the time you got to the SQL, this is exactly the point — the shape barely changes.

Why This Query Matters

Cash forecasting from the incoming side, collections prioritization, and the starting point for aging analysis.

SQL

SELECT
    hp.party_name              AS customer_name,
    ct.trx_number,
    ct.trx_date,
    ps.amount_due_original,
    ps.amount_due_remaining,
    ps.due_date
FROM   ra_customer_trx_all      ct
JOIN   ar_payment_schedules_all ps  ON ps.customer_trx_id = ct.customer_trx_id
JOIN   hz_cust_accounts         hca ON hca.cust_account_id = ct.bill_to_customer_id
JOIN   hz_parties               hp  ON hp.party_id = hca.party_id
WHERE  ps.amount_due_remaining > 0
  AND  ct.org_id = :business_unit_id
ORDER  BY ps.due_date

Understanding the Query

The amount due doesn't live on the transaction header — it lives on AR_PAYMENT_SCHEDULES_ALL, which is where Fusion actually tracks what's owed and by when. The customer name comes through HZ_CUST_ACCOUNTS and HZ_PARTIES rather than directly off the transaction — the Trading Community Architecture party model sits underneath both suppliers and customers in Fusion, a detail that pays off the next time either one shows up.

Tables Involved

Common Mistakes

• Reading amount due off RA_CUSTOMER_TRX_ALL instead of the payment schedule — the transaction table describes what was billed, not what's still owed after partial payments.

Performance Tip

Same guidance as Query 17 — filter the more selective column (amount_due_remaining > 0) as early as possible in the plan.

Related Concepts

Directly parallels Query 17. The party-model join here carries straight into the customer detail query later in this section.

Query 28 — AR Aging by Customer

Query 21 introduced a single overdue bucket on the payables side. Collections teams need several buckets at once, so this is the more advanced version of the same idea.

Why This Query Matters

The backbone of any collections review, and usually the exact shape a CFO expects to see when asking "how much are we owed, and how overdue is it."

SQL

SELECT
    hp.party_name AS customer_name,
    SUM(CASE WHEN ps.due_date >= TRUNC(SYSDATE)
             THEN ps.amount_due_remaining ELSE 0 END)               AS current_due,
    SUM(CASE WHEN TRUNC(SYSDATE) - ps.due_date BETWEEN 1 AND 30
             THEN ps.amount_due_remaining ELSE 0 END)               AS days_1_30,
    SUM(CASE WHEN TRUNC(SYSDATE) - ps.due_date BETWEEN 31 AND 60
             THEN ps.amount_due_remaining ELSE 0 END)               AS days_31_60,
    SUM(CASE WHEN TRUNC(SYSDATE) - ps.due_date > 60
             THEN ps.amount_due_remaining ELSE 0 END)               AS days_60_plus
FROM   ar_payment_schedules_all ps
JOIN   ra_customer_trx_all      ct  ON ct.customer_trx_id = ps.customer_trx_id
JOIN   hz_cust_accounts         hca ON hca.cust_account_id = ct.bill_to_customer_id
JOIN   hz_parties               hp  ON hp.party_id = hca.party_id
WHERE  ps.amount_due_remaining > 0
  AND  ct.org_id = :business_unit_id
GROUP  BY hp.party_name
ORDER  BY days_60_plus DESC

Understanding the Query

This is the same CASE-based bucketing pattern from the full-time/part-time headcount query back in the HCM section, applied to a date difference instead of a lookup code. Once you've written one bucketed CASE aggregation, every other one — aging, tenure bands, spend ranges — is a variation on the same idea.

Tables Involved

Common Mistakes

• Using fixed 30/60/90 buckets when the business actually defines aging differently — confirm bucket definitions before assuming the textbook version.
• Double-counting a schedule row across two buckets due to an off-by-one in the BETWEEN ranges — worth testing the boundary days explicitly.

Performance Tip

On a large receivables ledger, consider pre-filtering to open items only (as shown) before the aggregation runs, rather than bucketing the entire history and discarding closed items afterward.

Related Concepts

The heavier sibling of Query 21, and a direct reuse of the aggregation pattern from the HCM section.

Query 29 — Customer Account and Contact Detail

A deliberately minimal query, and worth explaining why it stays minimal. Fusion's customer and supplier master data both sit on the same underlying party model — the Trading Community Architecture — which is powerful and, in its full form, considerably more layered than a two-table join.

Why This Query Matters

The base lookup behind almost any customer-facing AR report — account number, status, and the party name behind it.

SQL

SELECT
    hp.party_name,
    hp.party_number,
    hca.account_number,
    hca.status              AS account_status,
    hca.cust_account_id
FROM   hz_cust_accounts hca
JOIN   hz_parties       hp ON hp.party_id = hca.party_id
WHERE  hca.status = 'A'
ORDER  BY hp.party_name

Understanding the Query

This intentionally stops at the account and party level. Full address and contact resolution runs through HZ_PARTY_SITES and HZ_LOCATIONS, and that hierarchy is intricate enough — and configured differently enough from one implementation to the next — that it deserves its own dedicated query rather than a join bolted onto this one without full confidence in every column.

Tables Involved

Common Mistakes

• Assuming one account maps to one address. Most B2B customers have several sites, and the account-to-address relationship is one-to-many.

Performance Tip

Lightweight as written — the cost grows quickly once the full party-site hierarchy gets added, so only pull it in when the report genuinely needs an address.

Related Concepts

The same party model referenced in Query 27 — recognizing it here is the payoff of that earlier note.

Query 30 — Cash Receipts Applied to Customer Invoices

The receivables counterpart to Query 20, and a fitting place to close this batch: money coming in, matched against what it was meant to settle.

Why This Query Matters

Cash application review, customer payment inquiries, and reconciling bank deposits back to specific invoices.

SQL

SELECT
    hp.party_name                AS customer_name,
    cr.receipt_number,
    cr.receipt_date,
    cr.amount                    AS receipt_amount,
    ra.amount_applied,
    ct.trx_number
FROM   ar_cash_receipts_all           cr
JOIN   ar_receivable_applications_all ra  ON ra.cash_receipt_id = cr.cash_receipt_id
                                          AND ra.status = 'APP'
JOIN   ra_customer_trx_all            ct  ON ct.customer_trx_id = ra.applied_customer_trx_id
JOIN   hz_cust_accounts               hca ON hca.cust_account_id = cr.pay_from_customer
JOIN   hz_parties                     hp  ON hp.party_id = hca.party_id
WHERE  cr.receipt_date BETWEEN :start_date AND :end_date
ORDER  BY cr.receipt_date DESC

Understanding the Query

A receipt and an application aren't the same event — the receipt is money arriving, the application is that money being matched to a specific invoice. status = 'APP' keeps this to receipts that have actually been applied, since unapplied and on-account receipts also live in this same table and mean something different.

Tables Involved

Common Mistakes

• Ignoring unapplied receipts entirely. A large unapplied balance is often a bigger operational problem than any single overdue invoice, and this query won't surface it — it's explicitly scoped to applied cash only.
• Assuming cash application rules are uniform. Auto-apply configuration varies by implementation, and partial or split applications are common enough to expect, not treat as edge cases.
• pay_from_customer and status = 'APP' reflect the general receipt-to-application pattern; confirm both against your instance before using this in production — this is one of the queries in this guide built more from the general AR model than from a table I've personally re-verified column by column.

Performance Tip

Bind the receipt date range rather than hardcoding it — same reasoning as the termination query back in the HCM section.

Related Concepts

A close parallel to Query 20 on the payables side. With Financials covered, the next batch of queries moves into Procurement — purchase orders, requisitions, and receiving — before closing with cross-module and security-focused queries.

Procurement: Requisition to Pay

The remaining queries move faster. Effective-dating, primary assignments, header/line/distribution structure, business-unit scoping — those patterns are established at this point, so the notes from here on flag what's genuinely new rather than re-explaining what already is. Procurement follows the purchasing lifecycle in order: a requisition becomes a purchase order, the PO gets shipped against and received, and the whole thing eventually lands as spend against a supplier. The Oracle Fusion Procurement SQL Guide covers this lifecycle in more depth, including receipt and spend-analysis queries beyond the ten here.

Query 31 — Requisitions Pending Approval

The starting point of the cycle: what's sitting in someone's approval queue right now.

SELECT
    prh.requisition_header_id,
    prh.requisition_number,
    prh.creation_date,
    prh.preparer_id
FROM   por_requisition_headers_all prh
WHERE  prh.authorization_status = 'PENDING'
ORDER  BY prh.creation_date

Notes

• authorization_status follows the same pattern as PO and AP status flags — a configurable lookup, not a fixed enum. Confirm the seeded values before hardcoding.
• preparer_id resolves to a person the same way every other person reference has in this guide — through PER_ALL_PEOPLE_F, left out here to keep the query focused.

Query 32 — Requisition Cycle Time: Creation to Approval

How long approvals actually take, versus how long the policy says they should take.

SELECT
    prh.requisition_number,
    prh.creation_date,
    prh.approved_date,
    prh.approved_date - prh.creation_date AS days_to_approve
FROM   por_requisition_headers_all prh
WHERE  prh.approved_date IS NOT NULL
  AND  prh.creation_date >= ADD_MONTHS(TRUNC(SYSDATE), -3)
ORDER  BY days_to_approve DESC

Notes

• Simple date subtraction here, same discipline as the AP overdue-days query — correct only because the surrounding filters are correct.
• A requisition can be resubmitted after rejection; if that matters to the analysis, this simple version won't distinguish first-pass approvals from resubmissions.
• approved_date is the general Procurement approval-tracking pattern, but confirm the exact column name against your instance before scheduling this as a recurring report — header-level date columns are one of the more release-sensitive parts of the Procurement schema.

Query 33 — Open Purchase Orders by Supplier

Once a requisition becomes a PO, this is the equivalent of Query 17 for procurement: what's open, and with whom.

SELECT
    s.vendor_name,
    poh.segment1              AS po_number,
    poh.creation_date,
    poh.authorization_status
FROM   po_headers_all poh
JOIN   poz_suppliers   s ON s.vendor_id = poh.vendor_id
WHERE  poh.authorization_status = 'APPROVED'
  AND  poh.closed_code IS NULL
  AND  poh.org_id = :business_unit_id
ORDER  BY poh.creation_date DESC

Notes

• The PO number lives in segment1, not a column literally named "po_number" — a flexfield-driven numbering convention carried over from Oracle's older document-numbering design.
• Don't drop the org_id filter — same reasoning as every other _ALL table in this guide.
• closed_code IS NULL is the general "still open" pattern; the actual closed-status values (open, closed, finally closed, cancelled) can differ by configuration, so check a handful of known-closed POs against this filter before trusting it at face value.

Query 34 — PO Line Detail with Quantity and Price

The drill-down from a PO header into what was actually ordered.

SELECT
    pol.line_num,
    pol.item_description,
    pol.quantity,
    pol.unit_price,
    pol.quantity * pol.unit_price AS line_total
FROM   po_lines_all pol
WHERE  pol.po_header_id = :po_header_id
ORDER  BY pol.line_num

Notes

• Always scope this to one PO header, the same rule as the AP invoice line-detail query earlier.

Query 35 — PO Shipment and Delivery Detail

A PO line can ship in more than one increment. This is the table that tracks each of those shipment schedules individually.

SELECT
    poll.line_location_id,
    poll.shipment_num,
    poll.quantity,
    poll.quantity_received,
    poll.quantity - NVL(poll.quantity_received, 0) AS quantity_outstanding,
    poll.promised_date
FROM   po_line_locations_all poll
WHERE  poll.po_line_id = :po_line_id
ORDER  BY poll.shipment_num

Notes

• This is the level receiving actually happens against — not the PO line itself. Quantity-received tracking lives here.

Query 36 — Purchase Orders Pending Receipt

What's been ordered and approved, but hasn't physically shown up yet.

SELECT
    s.vendor_name,
    poh.segment1                                    AS po_number,
    poll.shipment_num,
    poll.quantity - NVL(poll.quantity_received, 0)  AS quantity_outstanding,
    poll.promised_date
FROM   po_line_locations_all poll
JOIN   po_lines_all          pol ON pol.po_line_id = poll.po_line_id
JOIN   po_headers_all        poh ON poh.po_header_id = pol.po_header_id
JOIN   poz_suppliers          s  ON s.vendor_id = poh.vendor_id
WHERE  poll.quantity > NVL(poll.quantity_received, 0)
  AND  poh.org_id = :business_unit_id
ORDER  BY poll.promised_date

Notes

• Feeds expediting reports and receiving-dock planning — usually sorted by promised_date exactly as shown, so the most urgent shipments surface first.

Query 37 — Receiving Transactions Detail

Once something has actually been received, this is where the transaction itself lives — distinct from the shipment schedule above, which only tracks quantities.

SELECT
    rt.transaction_id,
    rt.transaction_type,
    rt.transaction_date,
    rt.quantity,
    rt.po_line_location_id
FROM   rcv_transactions rt
WHERE  rt.po_line_location_id = :po_line_location_id
ORDER  BY rt.transaction_date

Notes

• RCV_TRANSACTIONS holds every receiving-related event — receive, deliver, return, correct — not just the initial receipt. A single shipment can carry several rows here, and that's expected, not a data problem.
• The receiving schema goes considerably deeper than this (shipment headers, lots, serials); this query is a starting point, not the full model.

Query 38 — Purchase Order Approval Status Overview

A quick operational pulse-check: how many POs sit in each stage of approval right now.

SELECT
    poh.authorization_status,
    COUNT(*) AS po_count
FROM   po_headers_all poh
WHERE  poh.org_id = :business_unit_id
GROUP  BY poh.authorization_status
ORDER  BY po_count DESC

Notes

• The same status-lookup pattern from the unposted-journal query earlier, applied to a different document type.

Query 39 — Suppliers with No Recent Purchase Activity

A procurement housekeeping query: active suppliers nobody's actually bought from in the last year.

SELECT
    s.vendor_name,
    s.vendor_id
FROM   poz_suppliers s
WHERE  s.status = 'ACTIVE'
  AND  NOT EXISTS (
           SELECT 1 FROM po_headers_all poh
           WHERE  poh.vendor_id      = s.vendor_id
             AND  poh.creation_date >= ADD_MONTHS(TRUNC(SYSDATE), -12)
       )
ORDER  BY s.vendor_name

Notes

• The same NOT EXISTS shape as the missing-manager query from the HCM section — a pattern worth keeping in your back pocket for any "who's missing X" question.

Query 40 — Procurement Spend by Supplier

Closing the loop on the purchasing lifecycle: total spend, aggregated back to the supplier the whole chain started with.

SELECT
    s.vendor_name,
    SUM(pod.amount) AS total_spend
FROM   po_distributions_all pod
JOIN   po_headers_all       poh ON poh.po_header_id = pod.po_header_id
JOIN   poz_suppliers         s  ON s.vendor_id = poh.vendor_id
WHERE  poh.org_id           = :business_unit_id
  AND  poh.creation_date   >= ADD_MONTHS(TRUNC(SYSDATE), -12)
GROUP  BY s.vendor_name
ORDER  BY total_spend DESC

Notes

• Distribution-level amounts, not header or line amounts — the same "go to the distribution for the real number" habit from the AP section.

Security & Access Control

Security in Fusion is partly a database concern and partly an identity-platform concern — a meaningful share of modern role provisioning happens through Oracle's identity services rather than a single queryable table. The queries below cover what's reliably visible at the database level: who has an account, how it maps to a person, and what's granted to it. For the row-level security and data-role side of this — how secured views actually restrict what a query returns — see Oracle Fusion SQL Security Explained.

Query 41 — Active Application Users

The most basic security question: who currently has a login.

SELECT
    pu.user_id,
    pu.username,
    pu.start_date,
    pu.end_date
FROM   per_users pu
WHERE  pu.active_flag = 'Y'
ORDER  BY pu.username

Notes

• A callback to the HCM section, from the opposite direction: not every table marks "current" with a date range. Here it's a flag — filter on active_flag, not by comparing today's date against start_date/end_date, which are informational rather than the actual current-status source.

Query 42 — Mapping an Application User to an HCM Person

Turning a username into a name a business audience actually recognizes.

SELECT
    pu.username,
    pu.person_id,
    ppnf.display_name
FROM   per_users           pu
JOIN   per_person_names_f  ppnf ON ppnf.person_id = pu.person_id
                                AND ppnf.name_type = 'GLOBAL'
                                AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
ORDER  BY ppnf.display_name

Notes

• PER_USERS carries the account and, where one exists, the linked person directly — person_id is nullable, which is exactly what the next query checks for.

Query 43 — Users Without a Linked Person Record

Service accounts and integration users legitimately have no person behind them. This query finds all of them, and it's up to whoever reviews the list to separate the expected from the unexpected.

SELECT
    pu.user_id,
    pu.username
FROM   per_users pu
WHERE  pu.person_id IS NULL
ORDER  BY pu.username

Notes

• Simpler than a NOT EXISTS check specifically because person_id lives directly on PER_USERS — there's no second table to check against, just a nullable column on the row you already have.
• Don't treat every result as a finding — the point is to shrink an unknown list down to a short one a human can actually review.

Query 44 — Role and Duty Grants for a User

What a given user or role has actually been granted, at the function and data-security level.

SELECT
    fg.grantee_key,
    fg.menu_id,
    fg.start_date,
    fg.end_date
FROM   fnd_grants fg
WHERE  fg.grantee_key = :user_or_role_key
ORDER  BY fg.start_date DESC

Notes

• Worth being direct about this one: FND_GRANTS shows function and data-security grants at the database level, but a meaningful part of role provisioning in a modern Fusion instance happens through identity services layered on top of this. Treat this as one input to a security review, not the complete picture.

Query 45 — HCM Data Role Security Profiles Defined in the System

Every HCM data role restricts a user to a population defined by a security profile. This lists what's been configured.

SELECT
    psp.security_profile_id,
    psp.name,
    psp.security_profile_type
FROM   per_security_profiles psp
ORDER  BY psp.name

Notes

• This is the table behind the "why does this account see fewer rows" behavior described in the Database Architecture section — useful to confirm a security profile exists before assuming a query is wrong.

Query 46 — Application User Account Lifecycle

A rolling audit of accounts provisioned and deprovisioned over the last year — standard evidence for a security or access review.

SELECT
    pu.username,
    pu.start_date AS provisioned_date,
    pu.end_date   AS deprovisioned_date,
    CASE WHEN pu.active_flag = 'Y'
         THEN 'Active' ELSE 'Inactive' END AS current_status
FROM   per_users pu
WHERE  pu.start_date >= ADD_MONTHS(TRUNC(SYSDATE), -12)
ORDER  BY pu.start_date DESC

Notes

• start_date/end_date describe when the account was provisioned or deprovisioned; active_flag is the actual current-status source of truth, same distinction as Query 41. They should agree in practice — if they don't, trust the flag.
• A common audit deliverable as-is — pair it with Query 43 to also flag any newly provisioned accounts still missing a person link.

Advanced Patterns: Validation, Auditing, and Reconciliation

The last four queries aren't about answering a specific business request — they're the kind of check an experienced consultant runs on a new engagement, or on a schedule, to catch problems before someone downstream finds them first.

Query 47 — Orphaned Assignments Referencing an Invalid Department

Every assignment points to an organization ID. This query finds the ones pointing at an organization that, as of today, doesn't actually resolve to a valid current department — usually the result of an org restructure that didn't fully propagate.

SELECT
    ppf.person_id,
    ppnf.display_name,
    paam.organization_id
FROM   per_all_assignments_m paam
JOIN   per_all_people_f      ppf  ON ppf.person_id = paam.person_id
                                  AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
JOIN   per_person_names_f    ppnf ON ppnf.person_id = ppf.person_id
                                  AND ppnf.name_type = 'GLOBAL'
                                  AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
WHERE  paam.primary_flag    = 'Y'
  AND  paam.assignment_type = 'E'
  AND  TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
  AND  NOT EXISTS (
           SELECT 1 FROM hr_all_organization_units_f hou
           WHERE  hou.organization_id = paam.organization_id
             AND  TRUNC(SYSDATE) BETWEEN hou.effective_start_date AND hou.effective_end_date
       )
ORDER  BY ppnf.display_name

Notes

• A non-empty result here almost always traces back to an organization that was end-dated without every referencing assignment being updated first — worth running right after any reorg, not just periodically.

Query 48 — Detecting Overlapping Effective-Date Ranges

Every effective-dated table in this guide depends on one unstated assumption: that date ranges for the same key never overlap. This query tests that assumption instead of trusting it.

SELECT
    a.person_id,
    a.assignment_id        AS assignment_id_1,
    b.assignment_id        AS assignment_id_2,
    a.effective_start_date AS start_1,
    a.effective_end_date   AS end_1,
    b.effective_start_date AS start_2,
    b.effective_end_date   AS end_2
FROM   per_all_assignments_m a
JOIN   per_all_assignments_m b
       ON  b.assignment_id > a.assignment_id
       AND b.person_id     = a.person_id
       AND b.primary_flag  = a.primary_flag
       AND a.effective_start_date <= b.effective_end_date
       AND a.effective_end_date   >= b.effective_start_date
WHERE  a.primary_flag = 'Y'
ORDER  BY a.person_id

Notes

• This technique — a self-join on overlapping date ranges — isn't specific to assignments. It's worth keeping as a general-purpose check for any effective-dated table you're not fully sure is clean.
• b.assignment_id > a.assignment_id avoids comparing every row to itself and reporting each overlap twice.

Query 49 — Validating That Every PO Distribution Has a Valid GL Account

A cross-module integrity check: distributions are supposed to always point at a real, resolvable chart-of-accounts combination. This confirms it instead of assuming it.

SELECT
    pod.po_distribution_id,
    pod.po_header_id,
    pod.code_combination_id
FROM   po_distributions_all pod
WHERE  NOT EXISTS (
           SELECT 1 FROM gl_code_combinations gcc
           WHERE  gcc.code_combination_id = pod.code_combination_id
       )

Notes

• Any row here points to a data-migration or integration defect, not a business scenario — this should return nothing in a healthy environment.

Query 50 — Employees Whose Manager Sits in a Different Legal Employer

The closing query, and deliberately the most layered one in this guide — it combines assignment resolution, manager resolution, and legal-entity scoping into a single audit most consultants only think to run after something's already gone wrong. Cross-entity reporting lines are sometimes intentional in shared-services structures, and sometimes exactly the kind of gap that surfaces during an audit.

SELECT
    ppnf.display_name         AS employee_name,
    paam.legal_entity_id      AS employee_legal_entity,
    mgr_paam.legal_entity_id  AS manager_legal_entity
FROM   per_all_assignments_m        paam
JOIN   per_all_people_f             ppf  ON ppf.person_id = paam.person_id
                                         AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
JOIN   per_person_names_f           ppnf ON ppnf.person_id = ppf.person_id
                                         AND ppnf.name_type = 'GLOBAL'
                                         AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
JOIN   per_assignment_supervisors_f pas  ON pas.person_id = ppf.person_id
                                         AND pas.supervisor_type = 'LINE_MANAGER'
                                         AND TRUNC(SYSDATE) BETWEEN pas.effective_start_date AND pas.effective_end_date
JOIN   per_all_assignments_m        mgr_paam
                                          ON  mgr_paam.person_id       = pas.supervisor_id
                                          AND mgr_paam.primary_flag    = 'Y'
                                          AND mgr_paam.assignment_type = 'E'
                                          AND TRUNC(SYSDATE) BETWEEN mgr_paam.effective_start_date AND mgr_paam.effective_end_date
WHERE  paam.primary_flag              = 'Y'
  AND  paam.assignment_type           = 'E'
  AND  TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
  AND  paam.legal_entity_id <> mgr_paam.legal_entity_id
ORDER  BY ppnf.display_name

Notes

• Every join in this query has already appeared somewhere earlier in this guide — the manager resolution from Query 2, the primary-assignment discipline from Query 1, legal-entity awareness from the Business Units section. If this one reads clearly, the rest of this guide has done its job.
• Don't treat every result as an error. Confirm with HR or finance whether cross-entity reporting lines are an intentional part of a shared-services model before flagging it as a defect.

Oracle Fusion SQL Performance Optimization

Every query in this guide was written to be correct first. Performance is worth treating as a genuinely separate step, not something you bake in while you're still working out the joins — a fast query that returns the wrong answer isn't an optimization, it's a bug that runs quickly. Everything below assumes you've already validated correctness and are now making a working query fast enough for production.

Filter Effective-Dated Tables Inside the Join, Not After

This has come up repeatedly across the fifty queries above for a reason: it's the single highest-leverage habit in this entire guide. A date predicate inside the ON clause lets the optimizer eliminate rows before a join runs. The same predicate moved into a WHERE clause after several joins have already executed forces the database to build a larger intermediate result first, then throw most of it away. On a small table the difference is invisible. On a workforce or transaction table with years of history, it's often the entire performance problem.

Choose the Driving Table Deliberately

The table you list first in a multi-join query isn't just stylistic — it's a hint about intent, even though Oracle's optimizer is usually free to reorder joins on its own. Lead with the table that applies the most selective filter, not necessarily the one that feels conceptually "central" to the report. A query built around all suppliers and then filtered down to one business unit does more work than one that starts from a pre-filtered set.

Don't Join for Columns Nobody Asked For

Every join is a chance to change the shape of your result set, and every join costs something, even a cheap one. If a report doesn't need the job title, don't bring in PER_JOBS_VL just because it's sitting right there in the pattern you've been reusing. This sounds obvious and gets ignored constantly — copy-pasting a proven query template is efficient right up until it drags in three unused joins along with it.

Reach for EXISTS Instead of JOIN Plus DISTINCT

When the only question is whether a related row exists — not what's in it — EXISTS (or NOT EXISTS, as used throughout the HCM and Procurement sections) is usually both clearer and faster than joining to the related table and de-duplicating the result with DISTINCT. A join followed by DISTINCT is often a sign the query answered a slightly different question than the one being asked.

Watch for Row Multiplication Before You Optimize Anything Else

A query returning far more rows than expected isn't a performance problem to tune — it's a correctness problem that happens to look like one, because all that extra volume is what's actually slowing things down. Query 48's overlapping-date-range check is worth running against any effective-dated table you suspect is contributing to this before spending time on indexes or hints that won't address the real cause.

Read the Execution Plan Before Guessing at a Fix

You don't need to be a DBA to get value from EXPLAIN PLAN or a SQL Developer plan view — you mainly need to know what to look for. A full table scan on a large table you expected to hit by index usually means the filter column isn't indexed, or a function wrapped around it (TRUNC(), string concatenation) is preventing the optimizer from using the index that does exist. A nested loop join against a very large row estimate is often a sign the join order or an index is missing. Cardinality estimates that are wildly off from the actual row counts usually point to stale statistics rather than a flaw in the query itself.

Split the Report Instead of Forcing One Query to Do Everything

A single query trying to serve five stakeholders at once tends to accumulate LEFT JOINs, CASE expressions, and subqueries until nobody can reason about its performance anymore. Two focused queries, combined at the presentation layer or joined only where the data genuinely needs to be combined, are usually both faster and easier to maintain than one that tries to be everything.

Validate Performance Against Realistic Volumes, Before You Optimize

A query that returns instantly against a hundred rows in a sandbox can behave completely differently against a production workforce or transaction table. Don't tune a query until you've tested it somewhere the row counts actually resemble production — otherwise you're optimizing for a problem that doesn't exist yet, or missing the one that does.

SQL vs. OTBI: Choosing the Right Tool

This isn't really a competition, and treating it like one leads to bad decisions in both directions. OTBI and direct SQL are built for different shapes of work, and the honest answer to "which one should I use" is almost always "it depends what you're actually trying to do." OTBI vs Direct SQL in Oracle Fusion covers this decision in more depth if you want the fuller version.

Reach for OTBI When…

• A business user needs to build or modify a report themselves, without waiting on a developer for every change.
• The report fits cleanly inside one subject area, with joins and security already handled by Oracle's repository design.
• The output is a dashboard meant to be explored interactively — filtered, sliced, drilled into — rather than delivered as a fixed document.
• Time-to-delivery matters more than fine control over the underlying SQL, and the subject area already covers what's needed.

Reach for Direct SQL When…

• The report needs data from two subject areas that were never designed to be combined, and the workaround inside OTBI is more convoluted than just writing the join.
• You're debugging a number nobody can explain, and need to see exactly which row and which join produced it.
• The query is feeding an integration, a scheduled extract, or a BI Publisher data model — anything that needs to run unattended rather than be explored by a person.
• Performance at scale matters more than convenience, and you need control over exactly how the joins execute.
• The data you need isn't exposed in any subject area at all — not every table has repository coverage.

In practice, most experienced Fusion teams use both, often for the same underlying report: OTBI for the version business users interact with day to day, and SQL underneath the data model, integration, or investigation that keeps that dashboard trustworthy in the first place.

SQL vs. BI Publisher

This comparison is a little different from the one above, because BI Publisher usually isn't an alternative to SQL — it's a delivery layer sitting on top of it. Most BI Publisher data models are SQL queries with a template attached, which means the SQL skills from this entire guide transfer directly.

Where BI Publisher earns its place is formatting and distribution: producing a PDF invoice that looks exactly like the paper version it replaced, generating an Excel workbook with specific formatting a finance team expects, or scheduling a report to land in a dozen inboxes every Monday morning. None of that is something raw SQL output handles on its own. Once the data model itself is right, Oracle Fusion BI Publisher SQL Performance covers the twelve most common ways that same report ends up running slower than it needs to.

What BI Publisher doesn't do is make the underlying query correct. A data model built on a query that forgot the effective-date predicate produces a beautifully formatted report with the wrong numbers on it — the template doesn't know the difference. Every join pattern, every common mistake, every performance consideration in this guide applies just as directly inside a BI Publisher data model as it does in a SQL editor. If anything, mistakes matter more there, because the output looks polished enough that nobody thinks to question it.

Where FusionLens Fits

Everything covered in this guide is learnable, and none of it requires a specific tool — a text editor and a Fusion connection are enough. But it's worth being honest about where the time actually goes once you know the patterns: not in writing the SELECT statement, but in the surrounding work. Which table actually holds this column. Whether it's effective-dated. Whether the join you're about to write already exists somewhere as a pattern you've used before. Whether the query you just wrote is about to return ten times the rows you expect.

That's the specific set of problems FusionLens is built around, for people who've already done the learning this guide covers and want to spend less time on the repetitive parts of applying it:

• Schema Navigator

  Browse Oracle Fusion tables and columns directly, without switching to a separate documentation tab or guessing at a name from memory.

• Ctrl+Click Describe Table

  Ctrl+click any table or alias in a query to see its structure inline — useful for exactly the moment in this guide where you're staring at an unfamiliar table name and trying to guess its behavior from the naming convention.

• SQL Autocomplete for Fusion Tables and Columns

  Autocomplete that already knows Fusion's schema, so less time gets spent double-checking a column name against documentation before running a query.

• AI SQL Generator

  Generates a starting query from a plain-language description, grounded in the Fusion schema rather than a generic SQL model — a faster starting point for the first draft of a query like the ones in this guide, not a replacement for understanding why it's written that way.

• One-Click Explain Plan

  Pulls up the execution plan for the query you're already looking at — the same kind of diagnosis covered in the performance section above, without leaving the editor.

• Query History and Snippets

  Every query you've run stays searchable, so a pattern you built once — the effective-date predicate, the manager join, the aging bucket — is something you can find and reuse instead of rebuilding from memory.

None of this replaces the fifty queries above, or the reasoning behind them — that knowledge is what makes any of these features useful in the first place, the same way autocomplete only helps once you know what you're trying to write. What it removes is the friction around applying that knowledge: the table lookups, the schema guessing, the repeated groundwork this guide just walked through by hand.

Frequently Asked Questions

Answers are deliberately short. Where a longer explanation already exists earlier in this guide, the answer points back to it instead of repeating it.

Continue Learning

Deeper, module-specific companions to the sections above:

• How to Run SQL Queries on Oracle Fusion Cloud — access methods and starter schema-exploration queries.
• Oracle Fusion HCM SQL — 20 additional HR and workforce queries.
• Oracle Fusion Finance SQL Guide — 18 queries for GL, AP, AR, and SLA reporting.
• Oracle Fusion Procurement SQL Guide — 18 queries for POs, requisitions, receipts, and spend analysis.
• Oracle Fusion Table Relationships Explained — ERP, HCM, and Procurement joins in full.
• Effective Date Handling in Oracle Fusion SQL — a full-length treatment of the pattern from this guide's Database Architecture section.
• Oracle Fusion OTBI Subject Areas — a table reference for BI Publisher developers.
• Oracle Fusion BI Publisher SQL Performance — 12 ways to make reports faster.
• Oracle Fusion SQL Security Explained — data roles, row-level security, and sensitive data access.
• OTBI vs Direct SQL in Oracle Fusion — choosing the right tool, in more depth.

Conclusion

Go back to the headcount report from the start of this guide. The query still looks simple — pull from the people table, filter on status, group by department. What's changed isn't the SQL syntax. It's that "active employees, by department, as of today" no longer reads as a simple request. It reads as a specific set of tables, a specific join order, and a specific handful of predicates that have to be right at the same time: effective-dating on every date-tracked table, a primary-assignment filter, the correct business unit scope.

That's the actual skill this guide was built around. Not fifty queries to copy, but the pattern-recognition that makes the fifty-first one — the one nobody's written yet, for a report nobody's asked for yet — feel like a variation on something familiar instead of a problem to solve from scratch. The naming conventions, the effective-date discipline, the header/line/distribution shape, the habit of asking which relationships are mandatory before you write the join: those transfer to every table you'll ever query in Oracle Fusion, including the ones this guide never mentioned.

The SQL was always going to be the easy part once that groundwork was in place. If you've made it this far, it probably already is.