Oracle Fusion SQL Interview Questions with Real Queries (2026)

Quick answer, for anyone skimming

Nobody senior gets asked "what is a JOIN" in an Oracle Fusion technical round. What actually gets asked is: write a query that returns an employee's current assignment, without duplicates, without stale historical rows. The entire difficulty of Fusion SQL lives in three things — date-effective (_F) tables, object-versioned (_M) tables with an EFFECTIVE_LATEST_CHANGE flag, and columns that don't mean what their name suggests (AP_INVOICES_ALL.APPROVAL_STATUS is validation status, not workflow approval status — a genuinely common trap). Get comfortable with those three things and most "hard" Fusion SQL questions stop being hard.

Why This Needed a Rewrite

An earlier draft of this topic listed good question titles — "fetch the latest effective-dated record," "find employees without managers" — but left most of them as bullet points with no actual query, and a couple of the ones that did have SQL used a shortcut that only works by accident (a plain date-range filter without the correction-row flag Fusion actually needs). That's a meaningful gap for a 4–8 year consultant, because the difference between a technically-correct answer and one that merely looks right is exactly what a senior interviewer probes for.

So this version has real, verified queries for every question, checked against Oracle's own Fusion data model documentation and cross-referenced against consultant-published queries actually run against Fusion instances — not just table names recalled from memory.

Scenario 1: Fetch an Employee's Current Assignment — Without Duplicates

This is the single most common Fusion HCM SQL question, and it's also the one where candidates lose the most points on a technicality they don't realize exists.

The trap: PER_ALL_ASSIGNMENTS_M is a date-effective, object-versioned table. A plain SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE filter is necessary but not sufficient — if a correction was made to an assignment record (a retroactive change, a correction to an existing date-effective row rather than a new date-tracked change), you can still get more than one row back for the same date-window unless you also filter to the latest version of that row using EFFECTIVE_LATEST_CHANGE = 'Y'.

SELECT
    papf.person_number,
    ppnf.first_name,
    ppnf.last_name,
    paam.assignment_number,
    paam.effective_start_date,
    paam.effective_end_date
FROM   per_all_people_f      papf,
       per_person_names_f    ppnf,
       per_all_assignments_m paam
WHERE  papf.person_id = ppnf.person_id
AND    ppnf.name_type = 'GLOBAL'                 -- avoids duplicate name rows (LOCAL/GLOBAL splits)
AND    papf.person_id = paam.person_id
AND    paam.assignment_type IN ('E', 'C', 'N')    -- Employee, Contingent Worker, Nonworker
AND    paam.effective_latest_change = 'Y'         -- the detail most candidates miss
AND    SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND    SYSDATE BETWEEN paam.effective_start_date AND paam.effective_end_date;

What to say in the interview: name the EFFECTIVE_LATEST_CHANGE flag unprompted. It signals you've actually queried this table against a real instance with correction history, not just a clean demo dataset where the naive date filter happens to work.

Scenario 2: Fetch Every Employee's Reporting Manager

This one has changed shape in Fusion, and it's a favorite interview question precisely because it catches people who learned Fusion HCM SQL a few years ago and never updated their mental model.

What changed: in older Fusion releases, the manager was a direct column on the assignment record. Since the 18B update, manager relationships live in their own table, PER_ASSIGNMENT_SUPERVISORS_F, which supports multiple concurrent manager types (line manager, project manager, and so on) per assignment rather than a single hardcoded column.

SELECT
    papf_emp.person_number       AS employee_number,
    ppnf_emp.first_name          AS employee_first_name,
    ppnf_emp.last_name           AS employee_last_name,
    paam_emp.assignment_number   AS employee_assignment,
    papf_mgr.person_number       AS manager_number,
    ppnf_mgr.first_name          AS manager_first_name,
    ppnf_mgr.last_name           AS manager_last_name,
    pasf.manager_type
FROM   per_all_people_f       papf_emp,
       per_person_names_f     ppnf_emp,
       per_all_assignments_m  paam_emp,
       per_assignment_supervisors_f pasf,
       per_all_people_f       papf_mgr,
       per_person_names_f     ppnf_mgr
WHERE  ppnf_emp.person_id = papf_emp.person_id
AND    ppnf_emp.name_type = 'GLOBAL'
AND    paam_emp.person_id = papf_emp.person_id
AND    paam_emp.assignment_type IN ('E', 'C', 'N')
AND    paam_emp.effective_latest_change = 'Y'
AND    pasf.assignment_id = paam_emp.assignment_id
AND    pasf.manager_type = 'LINE_MANAGER'
AND    pasf.supervisor_id = papf_mgr.person_id
AND    ppnf_mgr.person_id = papf_mgr.person_id
AND    ppnf_mgr.name_type = 'GLOBAL'
AND    SYSDATE BETWEEN pasf.effective_start_date AND pasf.effective_end_date
AND    SYSDATE BETWEEN papf_emp.effective_start_date AND papf_emp.effective_end_date
AND    SYSDATE BETWEEN papf_mgr.effective_start_date AND papf_mgr.effective_end_date;

Interview-level nuance worth adding unprompted: if the client needs this for OTBI or high-volume manager hierarchy reporting rather than a single-employee lookup, don't self-join this table recursively for a full org chart — Oracle explicitly ships a denormalized manager hierarchy table (PER_MANAGER_HRCHY_REPORTEES_DN) built from PER_ASSIGNMENT_SUPERVISORS_F via a scheduled Refresh Manager Hierarchy process, specifically because recursive CONNECT BY queries against the normalized table don't scale for full-organization traversal. Knowing when to reach for the denormalized table instead of hand-rolling a recursive query is exactly the kind of judgment a senior-consultant question is testing for.

Scenario 3: Find Employees Without a Manager Assigned

A straightforward NOT EXISTS question, but it's worth answering with the correct table given Scenario 2.

SELECT
    papf.person_number,
    ppnf.first_name,
    ppnf.last_name,
    paam.assignment_number
FROM   per_all_people_f      papf,
       per_person_names_f    ppnf,
       per_all_assignments_m paam
WHERE  papf.person_id = ppnf.person_id
AND    ppnf.name_type = 'GLOBAL'
AND    paam.person_id = papf.person_id
AND    paam.assignment_type = 'E'
AND    paam.effective_latest_change = 'Y'
AND    SYSDATE BETWEEN paam.effective_start_date AND paam.effective_end_date
AND    NOT EXISTS (
    SELECT 1
    FROM   per_assignment_supervisors_f pasf
    WHERE  pasf.assignment_id = paam.assignment_id
    AND    pasf.manager_type = 'LINE_MANAGER'
    AND    SYSDATE BETWEEN pasf.effective_start_date AND pasf.effective_end_date
);

Why NOT EXISTS over LEFT JOIN ... IS NULL here specifically: with an effective-dated child table like PER_ASSIGNMENT_SUPERVISORS_F, a LEFT JOIN without also repeating the date-effective predicate inside the join condition can silently multiply rows if any historical (non-current) supervisor record exists for that assignment. NOT EXISTS with the date filter scoped inside the subquery avoids that entirely — this is a good moment in an interview to explain why, not just produce the working query.

Scenario 4: Fetch Department and Business Unit for an Assignment

This tests whether you know Fusion HCM's organization model, which is a genuinely different shape from a simple "Department" table.

SELECT
    papf.person_number,
    paam.assignment_number,
    dept_org.name AS department_name,
    bu.name       AS business_unit_name
FROM   per_all_people_f          papf,
       per_all_assignments_m     paam,
       hr_all_organization_units_f dept_org,
       hr_all_organization_units_f bu
WHERE  papf.person_id = paam.person_id
AND    paam.assignment_type = 'E'
AND    paam.effective_latest_change = 'Y'
AND    paam.organization_id = dept_org.organization_id       -- assignment's department org
AND    paam.business_unit_id = bu.organization_id             -- business unit
AND    SYSDATE BETWEEN paam.effective_start_date AND paam.effective_end_date
AND    SYSDATE BETWEEN dept_org.effective_start_date AND dept_org.effective_end_date
AND    SYSDATE BETWEEN bu.effective_start_date AND bu.effective_end_date;

Worth mentioning: both "Department" and "Business Unit" are stored in the same underlying table (HR_ALL_ORGANIZATION_UNITS_F) — what distinguishes them is the organization classification (HR_ORG_UNIT_CLASSIFICATIONS_F, classification codes like DEPARTMENT), not a separate department-specific table. A candidate who says "Departments and Business Units both live in the same organization table, differentiated by classification" is demonstrating real Fusion data-model fluency, not memorized table names.

Scenario 5: Find Duplicate Suppliers

SELECT
    supplier_name,
    COUNT(*) AS duplicate_count
FROM   poz_suppliers
GROUP BY supplier_name
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;

The follow-up an interviewer will ask: "This finds exact name matches — what about near-duplicates (extra spaces, 'Ltd' vs 'Limited', case differences)?" The production-grade answer is to normalize before grouping — UPPER(TRIM(REGEXP_REPLACE(supplier_name, '[^A-Za-z0-9]', ''))) as a comparison key — and to mention that a true fuzzy-match dedup (Levenshtein distance, UTL_MATCH.EDIT_DISTANCE) is a reasonable escalation path for a real data-cleansing engagement, not something you'd do inline in a reporting query.

Scenario 6: Fetch Open Purchase Orders

The gotcha: many older references point to PO_HEADERS_ALL.CLOSED_CODE or AUTHORIZATION_STATUS, both genuinely valid EBS-lineage columns that still exist — but the column actually used for PO lifecycle state in current Fusion Procurement reporting is DOCUMENT_STATUS, with values like OPEN, CLOSED, CANCELED, CLOSED FOR RECEIVING, CLOSED FOR INVOICING, and INCOMPLETE.

SELECT
    poh.segment1        AS po_number,
    poh.document_status,
    poh.type_lookup_code AS po_type,
    pol.line_num,
    pol.quantity,
    pol.unit_price
FROM   po_headers_all poh,
       po_lines_all   pol
WHERE  poh.po_header_id = pol.po_header_id
AND    poh.document_status NOT IN ('CLOSED', 'CANCELED', 'CLOSED FOR RECEIVING', 'CLOSED FOR INVOICING')
AND    poh.approved_flag = 'Y';

Why approved_flag = 'Y' matters as a separate filter: DOCUMENT_STATUS <> 'CLOSED' alone doesn't guarantee the PO is actually approved and usable — an incomplete, unapproved PO can also show a non-closed status. Filtering on both is the difference between "technically not closed" and "genuinely open and actionable," and calling that distinction out is a strong signal in an interview.

Scenario 7: Fetch Invoices Awaiting Approval

This is the single best "gotcha" question on this list, and it's worth knowing cold.

The trap: AP_INVOICES_ALL.APPROVAL_STATUS sounds like it tracks the invoice approval workflow (AME/BPM routing) — it doesn't. It tracks validation status, with values like NEVER APPROVED (not validated), APPROVED (validated), NEEDS REAPPROVAL (needs revalidation after a change), CANCELLED, and others. Confusing "validated" with "approved by a workflow" produces a query that returns the wrong invoice population entirely.

-- Invoices that still need validation (the AP_INVOICES_ALL.APPROVAL_STATUS meaning)
SELECT
    aia.invoice_num,
    aia.invoice_date,
    aia.invoice_amount,
    aia.approval_status,
    aia.payment_status_flag
FROM   ap_invoices_all aia
WHERE  aia.approval_status IN ('NEVER APPROVED', 'NEEDS REAPPROVAL')
AND    aia.cancelled_date IS NULL;

If the actual requirement is the invoice approval workflow status (who it's pending with, what rule triggered it), that lives in the approval-history data associated with the invoice's BPM/AME task — not in AP_INVOICES_ALL.APPROVAL_STATUS at all. Stating this distinction unprompted is one of the strongest single moves available in an Oracle Fusion Payables SQL interview, because it's a mistake even some experienced consultants make.

Scenario 8: Latest Salary of Every Employee

This tests analytic functions against a date-effective payroll structure — a very common senior-level ask.

SELECT
    person_number,
    salary_amount,
    effective_date
FROM (
    SELECT
        papf.person_number,
        pee.effective_start_date AS effective_date,
        pev.screen_entry_value   AS salary_amount,
        ROW_NUMBER() OVER (
            PARTITION BY papf.person_id
            ORDER BY pee.effective_start_date DESC
        ) AS rn
    FROM   per_all_people_f    papf,
           pay_element_entries_f pee,
           pay_element_entry_values_f pev
    WHERE  papf.person_id = pee.person_id
    AND    pee.element_entry_id = pev.element_entry_id
    AND    SYSDATE BETWEEN pee.effective_start_date AND pee.effective_end_date
)
WHERE rn = 1;

Interview framing: explain why ROW_NUMBER() rather than MAX(effective_start_date) in a correlated subquery — ROW_NUMBER() gets you the whole row (salary amount alongside the winning date) in a single pass, while MAX() in a subquery typically forces a second self-join or correlated lookup to retrieve the corresponding amount. For a wide reporting query touching several such "latest value" columns, the analytic-function approach is both more readable and generally cheaper to execute.

Scenario 9: Top 10 Highest Paid Employees

SELECT
    person_number,
    first_name,
    last_name,
    salary_amount
FROM (
    SELECT
        papf.person_number,
        ppnf.first_name,
        ppnf.last_name,
        pev.screen_entry_value AS salary_amount
    FROM   per_all_people_f papf,
           per_person_names_f ppnf,
           pay_element_entries_f pee,
           pay_element_entry_values_f pev
    WHERE  papf.person_id = ppnf.person_id
    AND    ppnf.name_type = 'GLOBAL'
    AND    papf.person_id = pee.person_id
    AND    pee.element_entry_id = pev.element_entry_id
    AND    SYSDATE BETWEEN pee.effective_start_date AND pee.effective_end_date
)
ORDER BY salary_amount DESC
FETCH FIRST 10 ROWS ONLY;

Worth raising proactively: FETCH FIRST n ROWS ONLY over the older ROWNUM <= 10 pattern — ROWNUM is applied before the ORDER BY logically completes in some query shapes, which is a classic source of a wrong "top N" result if someone isn't careful about wrapping it in a subquery first. FETCH FIRST avoids that class of mistake entirely and is the modern, ANSI-standard, Oracle-recommended approach.

Scenario 10: ROW_NUMBER() vs. RANK() vs. DENSE_RANK() — with an Actual Business Case

Nearly every senior SQL round asks for the definitions. A stronger answer ties them to a real HCM tie-breaking scenario:

SELECT
    department_name,
    person_number,
    salary_amount,
    ROW_NUMBER() OVER (PARTITION BY department_name ORDER BY salary_amount DESC) AS rn,
    RANK()       OVER (PARTITION BY department_name ORDER BY salary_amount DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY department_name ORDER BY salary_amount DESC) AS drnk
FROM department_salary_view;

• ROW_NUMBER() — always unique, arbitrarily breaks ties. Use when you need exactly one row per group regardless of ties (e.g., "the" latest effective-dated row, as in Scenario 1).
• RANK() — ties share a rank, but leaves a gap afterward (1, 1, 3). Use for "top N with ties visible" where the gap correctly reflects that two people tied for 1st means there is no 2nd.
• DENSE_RANK() — ties share a rank with no gap (1, 1, 2). Use when you want a compressed tier number — e.g., salary bands — where the gap from RANK() would be misleading.

The tell of a strong answer: picking ROW_NUMBER() specifically for de-duplication scenarios (Scenario 1, Scenario 8) and reserving RANK()/DENSE_RANK() for genuine ranking/reporting scenarios, rather than treating all three as interchangeable.

Scenario 11: EXISTS vs. IN — When Does It Actually Matter?

-- EXISTS: generally preferred when the subquery is on a large table
-- and you only need existence, not the values themselves
SELECT papf.person_number
FROM   per_all_people_f papf
WHERE  EXISTS (
    SELECT 1
    FROM   per_all_assignments_m paam
    WHERE  paam.person_id = papf.person_id
    AND    paam.assignment_status_type = 'ACTIVE'
);

The performance-literate answer, not just the syntax: the optimizer very often transforms IN and EXISTS into equivalent execution plans for simple cases, so in modern Oracle the difference is smaller than it used to be. Where it genuinely matters:

• NOT IN with a subquery that can return NULL is a real correctness trap, not just a performance one — if the subquery returns even one NULL, NOT IN returns zero rows for the entire query, silently. NOT EXISTS doesn't have this problem. This is worth stating even if not asked, because it's caused real production incidents.
• For genuinely large driving tables, EXISTS lets the optimizer short-circuit on the first matching row rather than materializing a full IN-list, which can matter at Fusion's transaction-table scale (AP_INVOICES_ALL, PO_HEADERS_ALL in a busy tenant).

Scenario 12: Optimizing a Slow Fusion Report Query

This is the standard senior-consultant closer, and it deserves a checklist answer, not a one-liner:

1. Check the execution plan first (EXPLAIN PLAN / DBMS_XPLAN.DISPLAY_CURSOR) — never guess at a fix before seeing where the cost actually is.
2. Never carry _F/_M date-effective joins without the date predicate on every date-tracked table in the join — a missing effective-date filter is one of the most common causes of both wrong results and unnecessary row explosion feeding into later joins.
3. Avoid SELECT * — Fusion tables are wide, and pulling unused columns (especially CLOBs or translated _TL columns) inflates I/O for no benefit in a report.
4. Use bind variables, not literals — for reports run repeatedly (BIP, OTBI-backed custom SQL), literals blow out the shared pool with unique SQL and prevent cursor sharing.
5. Push predicates as early as possible — filter on the smallest/most selective table first if the optimizer isn't already doing so, and validate with the plan rather than assuming join order doesn't matter.
6. Watch for functions on indexed columns (TRUNC(effective_start_date) = ... instead of a proper date range) — this silently defeats an index unless a matching function-based index exists.
7. Avoid unnecessary DISTINCT used to paper over a join that's producing duplicate rows — fix the join (usually a missing date-effective predicate on a child table), don't mask it with DISTINCT, which adds a sort operation and hides the real bug.

Common Fusion SQL Tables Referenced Above

Common Mistakes That Cost Points in Interviews

FAQ

Why is EFFECTIVE_LATEST_CHANGE needed if I already filter by SYSDATE BETWEEN the effective dates? Because a correction to an existing date-effective row can produce more than one row satisfying the same date window. The flag identifies the current, corrected version specifically — the date range alone doesn't guarantee uniqueness.

Is AP_INVOICES_ALL.APPROVAL_STATUS the invoice's approval workflow status? No — despite the name, it reflects validation status (NEVER APPROVED, APPROVED, NEEDS REAPPROVAL, etc.). The actual approval workflow routing is tracked separately from this column.

Should I always use EXISTS instead of IN? Not always — for small, non-nullable subqueries the optimizer often produces equivalent plans either way. The genuine risk is NOT IN against a subquery that can return NULL, which silently returns zero rows; NOT EXISTS is the safer default for negative existence checks.

Where do I find department and business unit for an employee? Both live in HR_ALL_ORGANIZATION_UNITS_F, distinguished by organization classification (HR_ORG_UNIT_CLASSIFICATIONS_F) rather than separate tables.

Do I need to memorize every Oracle Fusion table name for an interview? No — interviewers are testing whether you understand the data model's shape (date-effectiveness, object versioning, classification-based organization structure) and can reason about joins and edge cases, not whether you've memorized a schema diagram.

Closing Thoughts

The actual skill being tested in an Oracle Fusion SQL interview isn't SQL syntax — it's whether you understand that Fusion's data model has a small number of recurring patterns (date-effective _F tables, object-versioned _M tables, classification-driven organization structures, and columns whose names don't always match their real business meaning) and whether you instinctively account for them. Get those patterns internalized, and most "hard" Fusion SQL questions turn out to be the same handful of gotchas wearing different table names.