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

Why This Guide Exists

Oracle Fusion SQL fails in a distinctive way: the statement often looks perfectly reasonable, executes cleanly, and still answers the wrong business question.

The difficult part isn't SELECT, JOIN, or GROUP BY. It's knowing whether the question is really at person, assignment, invoice, payment-schedule, PO schedule, distribution, receipt, SLA line, fulfillment-line, or project-expenditure grain — and knowing which Oracle Fusion object actually owns that grain.

This guide is the query layer of the FusionLens Knowledge Hub. It assumes you already know basic SQL and focuses on the Fusion-specific decisions that determine whether a query is semantically correct.

The Overdue Invoice Test: Why Plausible SQL Can Be Completely Wrong

Consider a simple request:

Show me all suppliers with overdue invoices in the last 60 days — include the supplier name, invoice amount, and days overdue.

An AI model or developer who recognizes familiar EBS-era names can easily produce something like this:

SELECT v.vendor_name,
       v.segment1 AS supplier_number,
       i.invoice_num,
       i.invoice_amount,
       i.due_date,
       TRUNC(SYSDATE) - TRUNC(i.due_date) AS days_overdue
FROM   ap_invoices_all i
JOIN   ap_suppliers v
       ON v.vendor_id = i.vendor_id
WHERE  i.payment_status_flag <> 'Y'
AND    i.due_date BETWEEN SYSDATE - 60 AND SYSDATE;

It looks credible. In current Fusion, it is wrong in the exact places that matter. Supplier master is POZ_SUPPLIERS with the display name resolved through HZ_PARTIES. More importantly, invoice due date and remaining payable amount are installment/payment-schedule attributes, not invoice-header attributes. One invoice can have multiple installments with different due dates and payment states.

The correct reasoning is:

WITH overdue_schedules AS (
    SELECT invoice_id,
           MIN(due_date)             AS oldest_overdue_due_date,
           SUM(amount_remaining)     AS overdue_amount
    FROM   ap_payment_schedules_all
    WHERE  payment_status_flag IN ('N', 'P')
    AND    NVL(amount_remaining, 0) <> 0
    AND    due_date >= TRUNC(SYSDATE) - 60
    AND    due_date <  TRUNC(SYSDATE)
    GROUP BY invoice_id
)
SELECT hp.party_name AS supplier_name,
       ps.segment1   AS supplier_number,
       ai.invoice_num,
       ai.invoice_amount,
       ai.invoice_currency_code,
       os.oldest_overdue_due_date AS due_date,
       TRUNC(SYSDATE) - TRUNC(os.oldest_overdue_due_date) AS days_overdue,
       os.overdue_amount
FROM   ap_invoices_all ai
JOIN   overdue_schedules os
       ON os.invoice_id = ai.invoice_id
JOIN   poz_suppliers ps
       ON ps.vendor_id = ai.vendor_id
JOIN   hz_parties hp
       ON hp.party_id = ps.party_id
WHERE  ai.cancelled_date IS NULL
AND    ai.org_id = :p_org_id
ORDER BY days_overdue DESC,
         hp.party_name,
         ai.invoice_num;

Where Physical SQL Fits in Oracle Fusion Cloud

For custom physical SQL in Fusion SaaS, the normal application reporting path is Oracle Analytics Publisher / BI Publisher using the application data sources such as ApplicationDB_HCM and ApplicationDB_FSCM. This is different from having a customer-owned SQL*Net connection to the transactional database.

OTBI is also different. The SQL shown on an OTBI analysis's Advanced tab is logical SQL against the semantic model; it isn't the physical database SQL used by the BI Server underneath.

That distinction matters because these query examples describe Fusion physical objects and relationships. They are appropriate for physical-SQL reporting/diagnostic contexts, not as a replacement for supported REST APIs, FBDI/HDL, BICC/Data Extraction, or OTBI subject-area semantics.

Security Before SQL: Base Tables Are Not Automatically Data-Secured

A dangerous assumption in custom Publisher development is that selecting a Fusion base table automatically applies the same data-security scope the application UI uses. Oracle explicitly distinguishes the two: direct physical SQL against a table can return unsecured data; joining documented secured list views applies the runner's assigned security profiles for supported objects.

For HCM this distinction is especially important for people, assignments, positions and salary. PII objects can have additional database-level VPD protection, but that does not turn every ordinary base-table query into a fully data-secured report.

From Business Question to SQL: The 10-Step Method

1. Restate the business question. Replace vague words such as “employee,” “spend,” “open,” “current,” or “overdue” with a precise definition.
2. Choose the output grain. Define what exactly one result row represents.
3. Find the object that owns that grain. Do not start from the table whose name happens to match a noun in the request.
4. Map the relationship path. Header → line → schedule → distribution, person → work relationship → assignment, source transaction → SLA → GL, and so on.
5. Resolve time semantics. Current/as-of, transaction date, accounting date, due date, event date or overlap period?
6. Add organizational scope. Ledger, BU, legal entity, inventory organization, project BU, legal employer or legislative context.
7. Resolve status semantics. Use current Fusion status columns and documented codes; do not import EBS-era assumptions.
8. Resolve currency and amount semantics. Original amount, remaining amount, entered amount, accounted amount, project currency or functional currency?
9. Apply security intentionally. Base tables versus secured views are a report-design decision.
10. Validate cardinality before optimizing. Prove that row counts and totals are correct at the intended grain before tuning performance.

The Patterns That Repeat Across Oracle Fusion

Effective Dating: The HCM Rule That Breaks Otherwise-Correct SQL

For a normal date-effective object, an as-of predicate selects the row valid on the target date. PER_ALL_ASSIGNMENTS_M adds another dimension: it can retain multiple changes on the same day. For a final current/as-of assignment state, EFFECTIVE_LATEST_CHANGE='Y' is therefore part of the snapshot definition.

AND :p_as_of_date
    BETWEEN a.effective_start_date AND a.effective_end_date
AND a.effective_latest_change = 'Y'

PRIMARY_FLAG='Y' is different. It expresses a business choice — primary assignment/work relationship — and should only be used when the report really wants that subset.

Dates, Amounts and Currency: Never Leave the Noun Unqualified

Any aggregate across currencies must either group by currency or perform an explicit, documented conversion. A SQL statement that adds USD, EUR and TRY into a single number is syntactically valid and financially meaningless.

How to Validate AI-Generated Oracle Fusion SQL

Generated SQL should be treated as a proposed model, not as evidence that the model is correct. Before running or publishing it, check:

1. Does every table/view exist in current Fusion Cloud metadata?
2. Does every selected column exist on that exact object?
3. Is the source an EBS-era object accidentally carried into Fusion?
4. Does the query start at the correct business grain?
5. Are joins real FK/relationship paths rather than same-looking IDs or segments?
6. Are all date-effective objects filtered correctly?
7. Does a current PER_ALL_ASSIGNMENTS_M snapshot handle same-day changes?
8. Are supplier/customer display names resolved through the correct TCA layer?
9. Are due dates/open amounts coming from payment schedules where appropriate?
10. Are PO status columns current Fusion columns?
11. Does quantity × price actually apply to the line's value basis?
12. Are SLA joins using composite keys including APPLICATION_ID?
13. Are currency dimensions preserved?
14. Does the query apply the intended BU/ledger/org scope?
15. Is the report data-secured, or merely technically queryable?

75-Query Directory

75 Oracle Fusion SQL Queries — Correct Grain First

All examples below deliberately expose the grain and the most important semantic trap. Bind variables such as :p_org_id, :p_ledger_id and :p_start_date are placeholders for your Publisher/FusionLens data-model parameters.

SELECT p.person_number,
       pn.display_name,
       a.assignment_number,
       j.name                         AS job_name,
       ou.name                        AS department_name,
       l.location_name,
       g.name                         AS grade_name,
       pos.date_start                 AS work_relationship_start
FROM   per_all_people_f p
JOIN   per_person_names_f pn
       ON pn.person_id = p.person_id
      AND pn.name_type = 'GLOBAL'
      AND TRUNC(SYSDATE) BETWEEN pn.effective_start_date AND pn.effective_end_date
JOIN   per_all_assignments_m a
       ON a.person_id = p.person_id
      AND a.assignment_type = 'E'
      AND a.primary_flag = 'Y'
      AND a.effective_latest_change = 'Y'
      AND TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
LEFT JOIN per_jobs_f_vl j
       ON j.job_id = a.job_id
      AND TRUNC(SYSDATE) BETWEEN j.effective_start_date AND j.effective_end_date
LEFT JOIN hr_all_organization_units_f_vl ou
       ON ou.organization_id = a.organization_id
      AND TRUNC(SYSDATE) BETWEEN ou.effective_start_date AND ou.effective_end_date
LEFT JOIN hr_locations_all_f_vl l
       ON l.location_id = a.location_id
      AND TRUNC(SYSDATE) BETWEEN l.effective_start_date AND l.effective_end_date
LEFT JOIN per_grades_f_vl g
       ON g.grade_id = a.grade_id
      AND TRUNC(SYSDATE) BETWEEN g.effective_start_date AND g.effective_end_date
LEFT JOIN per_periods_of_service pos
       ON pos.period_of_service_id = a.period_of_service_id
WHERE  TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
AND    (pos.actual_termination_date IS NULL
        OR pos.actual_termination_date >= TRUNC(SYSDATE))
ORDER BY ou.name,
         pn.display_name

SELECT ou.name AS department_name,
       COUNT(DISTINCT a.person_id) AS headcount
FROM   per_all_assignments_m a
LEFT JOIN hr_all_organization_units_f_vl ou
       ON ou.organization_id = a.organization_id
      AND TRUNC(SYSDATE) BETWEEN ou.effective_start_date AND ou.effective_end_date
LEFT JOIN per_periods_of_service pos
       ON pos.period_of_service_id = a.period_of_service_id
WHERE  a.assignment_type = 'E'
AND    a.primary_flag = 'Y'
AND    a.effective_latest_change = 'Y'
AND    TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
AND    (pos.actual_termination_date IS NULL
        OR pos.actual_termination_date >= TRUNC(SYSDATE))
GROUP BY ou.name
ORDER BY headcount DESC

SELECT l.location_name,
       COUNT(DISTINCT a.person_id) AS headcount
FROM   per_all_assignments_m a
LEFT JOIN hr_locations_all_f_vl l
       ON l.location_id = a.location_id
      AND TRUNC(SYSDATE) BETWEEN l.effective_start_date AND l.effective_end_date
LEFT JOIN per_periods_of_service pos
       ON pos.period_of_service_id = a.period_of_service_id
WHERE  a.assignment_type = 'E'
AND    a.primary_flag = 'Y'
AND    a.effective_latest_change = 'Y'
AND    TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
AND    (pos.actual_termination_date IS NULL
        OR pos.actual_termination_date >= TRUNC(SYSDATE))
GROUP BY l.location_name
ORDER BY headcount DESC

SELECT lep.name AS legal_employer,
       COUNT(DISTINCT a.person_id) AS employee_count
FROM   per_all_assignments_m a
JOIN   xle_entity_profiles lep
       ON lep.legal_entity_id = a.legal_entity_id
LEFT JOIN per_periods_of_service pos
       ON pos.period_of_service_id = a.period_of_service_id
WHERE  a.assignment_type = 'E'
AND    a.primary_flag = 'Y'
AND    a.effective_latest_change = 'Y'
AND    TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
AND    (pos.actual_termination_date IS NULL
        OR pos.actual_termination_date >= TRUNC(SYSDATE))
GROUP BY lep.name
ORDER BY employee_count DESC

SELECT p.person_number,
       pn.display_name,
       pos.period_of_service_id,
       pos.date_start AS work_relationship_start
FROM   per_periods_of_service pos
JOIN   per_all_people_f p
       ON p.person_id = pos.person_id
      AND pos.date_start BETWEEN p.effective_start_date AND p.effective_end_date
JOIN   per_person_names_f pn
       ON pn.person_id = pos.person_id
      AND pn.name_type = 'GLOBAL'
      AND pos.date_start BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  pos.date_start >= TRUNC(:p_start_date)
AND    pos.date_start <  TRUNC(:p_end_date) + 1
ORDER BY pos.date_start DESC,
         p.person_number

SELECT p.person_number,
       pn.display_name,
       pos.period_of_service_id,
       pos.actual_termination_date
FROM   per_periods_of_service pos
JOIN   per_all_people_f p
       ON p.person_id = pos.person_id
      AND pos.actual_termination_date
          BETWEEN p.effective_start_date AND p.effective_end_date
JOIN   per_person_names_f pn
       ON pn.person_id = pos.person_id
      AND pn.name_type = 'GLOBAL'
      AND pos.actual_termination_date
          BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  pos.actual_termination_date >= TRUNC(:p_start_date)
AND    pos.actual_termination_date <  TRUNC(:p_end_date) + 1
ORDER BY pos.actual_termination_date DESC

SELECT ou.name AS department_name,
       COUNT(DISTINCT pos.period_of_service_id) AS terminated_work_relationships
FROM   per_periods_of_service pos
JOIN   per_all_assignments_m a
       ON a.period_of_service_id = pos.period_of_service_id
      AND a.assignment_type = 'E'
      AND a.primary_flag = 'Y'
      AND a.effective_latest_change = 'Y'
      AND pos.actual_termination_date
          BETWEEN a.effective_start_date AND a.effective_end_date
LEFT JOIN hr_all_organization_units_f_vl ou
       ON ou.organization_id = a.organization_id
      AND pos.actual_termination_date
          BETWEEN ou.effective_start_date AND ou.effective_end_date
WHERE  pos.actual_termination_date >= TRUNC(:p_start_date)
AND    pos.actual_termination_date <  TRUNC(:p_end_date) + 1
GROUP BY ou.name
ORDER BY terminated_work_relationships DESC

SELECT p.person_number,
       pn.display_name,
       a.assignment_number,
       a.organization_id,
       a.action_code,
       a.effective_start_date
FROM   per_all_assignments_m a
JOIN   per_all_people_f p
       ON p.person_id = a.person_id
      AND a.effective_start_date BETWEEN p.effective_start_date AND p.effective_end_date
JOIN   per_person_names_f pn
       ON pn.person_id = a.person_id
      AND pn.name_type = 'GLOBAL'
      AND a.effective_start_date BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  a.assignment_type = 'E'
AND    a.primary_flag = 'Y'
AND    a.effective_latest_change = 'Y'
AND    a.action_code = 'TRANSFER'
AND    a.effective_start_date >= TRUNC(:p_start_date)
AND    a.effective_start_date <  TRUNC(:p_end_date) + 1
ORDER BY a.effective_start_date DESC,
         p.person_number

SELECT p.person_number,
       pn.display_name,
       a.assignment_number,
       cs.salary_amount,
       cs.annual_salary,
       cs.currency_code,
       cs.payroll_frequency_code
FROM   cmp_salary cs
JOIN   per_all_assignments_m a
       ON a.assignment_id = cs.assignment_id
      AND a.assignment_type = 'E'
      AND a.primary_flag = 'Y'
      AND a.effective_latest_change = 'Y'
      AND TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
JOIN   per_all_people_f p
       ON p.person_id = a.person_id
      AND TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
JOIN   per_person_names_f pn
       ON pn.person_id = a.person_id
      AND pn.name_type = 'GLOBAL'
      AND TRUNC(SYSDATE) BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  cs.salary_approved = 'Y'
AND    TRUNC(SYSDATE) BETWEEN cs.date_from AND cs.date_to
ORDER BY pn.display_name

SELECT pn.display_name,
       a.assignment_number,
       cs.annual_salary,
       cs.currency_code
FROM   cmp_salary cs
JOIN   per_all_assignments_m a
       ON a.assignment_id = cs.assignment_id
      AND a.assignment_type = 'E'
      AND a.primary_flag = 'Y'
      AND a.effective_latest_change = 'Y'
      AND TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
JOIN   per_person_names_f pn
       ON pn.person_id = a.person_id
      AND pn.name_type = 'GLOBAL'
      AND TRUNC(SYSDATE) BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  cs.salary_approved = 'Y'
AND    TRUNC(SYSDATE) BETWEEN cs.date_from AND cs.date_to
ORDER BY cs.annual_salary DESC NULLS LAST
FETCH FIRST 100 ROWS ONLY

SELECT g.name AS grade_name,
       cs.currency_code,
       COUNT(*) AS salary_rows,
       AVG(cs.annual_salary) AS average_annual_salary
FROM   cmp_salary cs
JOIN   per_all_assignments_m a
       ON a.assignment_id = cs.assignment_id
      AND a.assignment_type = 'E'
      AND a.primary_flag = 'Y'
      AND a.effective_latest_change = 'Y'
      AND TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
LEFT JOIN per_grades_f_vl g
       ON g.grade_id = a.grade_id
      AND TRUNC(SYSDATE) BETWEEN g.effective_start_date AND g.effective_end_date
WHERE  cs.salary_approved = 'Y'
AND    TRUNC(SYSDATE) BETWEEN cs.date_from AND cs.date_to
GROUP BY g.name,
         cs.currency_code
ORDER BY g.name,
         cs.currency_code

SELECT cs.assignment_id,
       p.person_number,
       pn.display_name,
       cs.date_from,
       cs.salary_amount,
       cs.annual_salary,
       cs.currency_code,
       cs.salary_reason_code
FROM   cmp_salary cs
JOIN   per_all_assignments_m a
       ON a.assignment_id = cs.assignment_id
      AND a.assignment_type = 'E'
      AND a.effective_latest_change = 'Y'
      AND cs.date_from BETWEEN a.effective_start_date AND a.effective_end_date
JOIN   per_all_people_f p
       ON p.person_id = a.person_id
      AND cs.date_from BETWEEN p.effective_start_date AND p.effective_end_date
JOIN   per_person_names_f pn
       ON pn.person_id = a.person_id
      AND pn.name_type = 'GLOBAL'
      AND cs.date_from BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  cs.salary_approved = 'Y'
AND    cs.date_from >= ADD_MONTHS(TRUNC(SYSDATE), -12)
ORDER BY cs.date_from DESC,
         p.person_number

SELECT mgr.display_name AS manager_name,
       emp.display_name AS employee_name,
       a.assignment_number
FROM   per_all_assignments_m a
JOIN   per_assignment_supervisors_f sup
       ON sup.assignment_id = a.assignment_id
      AND sup.primary_flag = 'Y'
      AND TRUNC(SYSDATE) BETWEEN sup.effective_start_date AND sup.effective_end_date
JOIN   per_person_names_f emp
       ON emp.person_id = a.person_id
      AND emp.name_type = 'GLOBAL'
      AND TRUNC(SYSDATE) BETWEEN emp.effective_start_date AND emp.effective_end_date
JOIN   per_person_names_f mgr
       ON mgr.person_id = sup.manager_id
      AND mgr.name_type = 'GLOBAL'
      AND TRUNC(SYSDATE) BETWEEN mgr.effective_start_date AND mgr.effective_end_date
WHERE  a.assignment_type = 'E'
AND    a.primary_flag = 'Y'
AND    a.effective_latest_change = 'Y'
AND    TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
ORDER BY mgr.display_name,
         emp.display_name

SELECT p.person_number,
       pn.display_name,
       a.assignment_number
FROM   per_all_assignments_m a
JOIN   per_all_people_f p
       ON p.person_id = a.person_id
      AND TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
JOIN   per_person_names_f pn
       ON pn.person_id = a.person_id
      AND pn.name_type = 'GLOBAL'
      AND TRUNC(SYSDATE) BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  a.assignment_type = 'E'
AND    a.primary_flag = 'Y'
AND    a.effective_latest_change = 'Y'
AND    TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
AND    NOT EXISTS (
           SELECT 1
           FROM per_assignment_supervisors_f sup
           WHERE sup.assignment_id = a.assignment_id
           AND   sup.primary_flag = 'Y'
           AND   TRUNC(SYSDATE)
                 BETWEEN sup.effective_start_date AND sup.effective_end_date
       )
ORDER BY pn.display_name

SELECT pos.position_code,
       pos.name,
       pos.hiring_status,
       pos.max_persons
FROM   hr_all_positions_f_vl pos
WHERE  TRUNC(SYSDATE) BETWEEN pos.effective_start_date AND pos.effective_end_date
AND    NOT EXISTS (
           SELECT 1
           FROM per_all_assignments_m a
           WHERE a.position_id = pos.position_id
           AND   a.assignment_type = 'E'
           AND   a.effective_latest_change = 'Y'
           AND   TRUNC(SYSDATE)
                 BETWEEN a.effective_start_date AND a.effective_end_date
       )
ORDER BY pos.position_code

SELECT mgr.display_name AS manager_name,
       COUNT(DISTINCT a.person_id) AS direct_reports
FROM   per_all_assignments_m a
JOIN   per_assignment_supervisors_f sup
       ON sup.assignment_id = a.assignment_id
      AND sup.primary_flag = 'Y'
      AND TRUNC(SYSDATE) BETWEEN sup.effective_start_date AND sup.effective_end_date
JOIN   per_person_names_f mgr
       ON mgr.person_id = sup.manager_id
      AND mgr.name_type = 'GLOBAL'
      AND TRUNC(SYSDATE) BETWEEN mgr.effective_start_date AND mgr.effective_end_date
WHERE  a.assignment_type = 'E'
AND    a.primary_flag = 'Y'
AND    a.effective_latest_change = 'Y'
AND    TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
GROUP BY mgr.person_id,
         mgr.display_name
ORDER BY direct_reports DESC,
         mgr.display_name

SELECT p.person_number,
       pn.display_name,
       ae.per_absence_entry_id,
       ae.start_date,
       ae.end_date,
       ae.duration,
       ae.uom,
       ae.absence_status_cd,
       ae.approval_status_cd
FROM   anc_per_abs_entries ae
JOIN   per_all_people_f p
       ON p.person_id = ae.person_id
      AND NVL(ae.start_date, TRUNC(:p_start_date))
          BETWEEN p.effective_start_date AND p.effective_end_date
JOIN   per_person_names_f pn
       ON pn.person_id = ae.person_id
      AND pn.name_type = 'GLOBAL'
      AND NVL(ae.start_date, TRUNC(:p_start_date))
          BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  ae.start_date <= TRUNC(:p_end_date)
AND    NVL(ae.end_date, ae.start_date) >= TRUNC(:p_start_date)
ORDER BY ae.start_date,
         p.person_number

SELECT p.person_number,
       pn.display_name,
       pos.date_start AS work_relationship_start,
       ROUND(MONTHS_BETWEEN(TRUNC(SYSDATE), pos.date_start) / 12, 1)
           AS relationship_years
FROM   per_all_assignments_m a
JOIN   per_periods_of_service pos
       ON pos.period_of_service_id = a.period_of_service_id
JOIN   per_all_people_f p
       ON p.person_id = a.person_id
      AND TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
JOIN   per_person_names_f pn
       ON pn.person_id = a.person_id
      AND pn.name_type = 'GLOBAL'
      AND TRUNC(SYSDATE) BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  a.assignment_type = 'E'
AND    a.primary_flag = 'Y'
AND    a.effective_latest_change = 'Y'
AND    TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
AND    (pos.actual_termination_date IS NULL
        OR pos.actual_termination_date >= TRUNC(SYSDATE))
ORDER BY relationship_years DESC

SELECT p.person_number,
       pn.display_name,
       pos.date_start AS work_relationship_start
FROM   per_all_assignments_m a
JOIN   per_periods_of_service pos
       ON pos.period_of_service_id = a.period_of_service_id
JOIN   per_all_people_f p
       ON p.person_id = a.person_id
      AND TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
JOIN   per_person_names_f pn
       ON pn.person_id = a.person_id
      AND pn.name_type = 'GLOBAL'
      AND TRUNC(SYSDATE) BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  a.assignment_type = 'E'
AND    a.primary_flag = 'Y'
AND    a.effective_latest_change = 'Y'
AND    TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
AND    EXTRACT(MONTH FROM pos.date_start) = EXTRACT(MONTH FROM SYSDATE)
AND    pos.date_start < TRUNC(SYSDATE, 'YYYY')
AND    (pos.actual_termination_date IS NULL
        OR pos.actual_termination_date >= TRUNC(SYSDATE))
ORDER BY EXTRACT(DAY FROM pos.date_start),
         pn.display_name

SELECT a.person_id,
       pn.display_name,
       pp.date_of_birth,
       TRUNC(MONTHS_BETWEEN(TRUNC(SYSDATE), pp.date_of_birth) / 12) AS age_years
FROM   per_assignment_secured_list_v a
JOIN   per_pub_pers_secured_list_v pp
       ON pp.person_id = a.person_id
JOIN   per_person_names_f pn
       ON pn.person_id = a.person_id
      AND pn.name_type = 'GLOBAL'
      AND TRUNC(SYSDATE) BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  a.assignment_type = 'E'
AND    a.primary_flag = 'Y'
AND    a.effective_latest_change = 'Y'
AND    TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
AND    pp.date_of_birth IS NOT NULL
AND    pp.date_of_birth <= ADD_MONTHS(TRUNC(SYSDATE), -60 * 12)
ORDER BY age_years DESC,
         pn.display_name

SELECT gjh.je_header_id,
       gjh.name                    AS journal_name,
       gjh.je_source,
       gjh.je_category,
       gjh.period_name,
       gjh.currency_code,
       gjh.posted_date,
       gjl.je_line_num,
       gjl.effective_date,
       gcc.concatenated_segments   AS account_combination,
       gjl.entered_dr,
       gjl.entered_cr,
       gjl.accounted_dr,
       gjl.accounted_cr,
       gjl.description
FROM   gl_je_headers gjh
JOIN   gl_je_lines gjl
       ON gjl.je_header_id = gjh.je_header_id
JOIN   gl_code_combinations gcc
       ON gcc.code_combination_id = gjl.code_combination_id
WHERE  gjh.status      = 'P'
AND    gjh.ledger_id   = :p_ledger_id
AND    gjh.period_name = :p_period_name
ORDER BY gjh.posted_date,
         gjh.je_header_id,
         gjl.je_line_num

SELECT gb.period_name,
       gb.currency_code,
       gcc.concatenated_segments AS account,
       gb.begin_balance_dr,
       gb.begin_balance_cr,
       gb.period_net_dr,
       gb.period_net_cr,
       ( NVL(gb.begin_balance_dr, 0)
       - NVL(gb.begin_balance_cr, 0)
       + NVL(gb.period_net_dr, 0)
       - NVL(gb.period_net_cr, 0) ) AS ending_balance
FROM   gl_balances gb
JOIN   gl_code_combinations gcc
       ON gcc.code_combination_id = gb.code_combination_id
WHERE  gb.actual_flag    = 'A'
AND    gb.ledger_id      = :p_ledger_id
AND    gb.period_name    = :p_period_name
AND    gb.currency_code  = :p_currency_code
AND    gb.template_id IS NULL
ORDER BY gcc.concatenated_segments

SELECT gcc.concatenated_segments AS account,
       gb.currency_code,
       NVL(gb.begin_balance_dr, 0)
         - NVL(gb.begin_balance_cr, 0) AS opening_balance,
       NVL(gb.period_net_dr, 0)
         - NVL(gb.period_net_cr, 0) AS period_activity,
       NVL(gb.begin_balance_dr, 0)
         - NVL(gb.begin_balance_cr, 0)
         + NVL(gb.period_net_dr, 0)
         - NVL(gb.period_net_cr, 0) AS ending_balance
FROM   gl_balances gb
JOIN   gl_code_combinations gcc
       ON gcc.code_combination_id = gb.code_combination_id
WHERE  gb.ledger_id      = :p_ledger_id
AND    gb.actual_flag    = 'A'
AND    gb.period_name    = :p_period_name
AND    gb.currency_code  = :p_currency_code
AND    gb.template_id IS NULL
ORDER BY gcc.concatenated_segments

SELECT gjh.je_source,
       gjh.je_category,
       COUNT(DISTINCT gjh.je_header_id) AS journal_count,
       COUNT(*)                         AS journal_line_count,
       SUM(NVL(gjl.accounted_dr, 0))    AS total_dr,
       SUM(NVL(gjl.accounted_cr, 0))    AS total_cr
FROM   gl_je_headers gjh
JOIN   gl_je_lines gjl
       ON gjl.je_header_id = gjh.je_header_id
WHERE  gjh.status      = 'P'
AND    gjh.ledger_id   = :p_ledger_id
AND    gjh.period_name = :p_period_name
GROUP BY gjh.je_source,
         gjh.je_category
ORDER BY total_dr DESC

SELECT gjh.je_header_id,
       gjh.name,
       gjh.je_source,
       gjh.je_category,
       gjh.period_name,
       gjh.creation_date,
       gjh.status
FROM   gl_je_headers gjh
WHERE  gjh.ledger_id = :p_ledger_id
AND    gjh.status <> 'P'
ORDER BY gjh.creation_date DESC

SELECT gps.period_name,
       gps.start_date,
       gps.end_date,
       gps.adjustment_period_flag,
       gps.closing_status,
       DECODE(gps.closing_status,
              'O', 'Open',
              'F', 'Future Enterable',
              'C', 'Closed',
              'P', 'Permanently Closed',
              'N', 'Never Opened',
              gps.closing_status) AS closing_status_name
FROM   gl_period_statuses gps
WHERE  gps.ledger_id      = :p_ledger_id
AND    gps.application_id = 101
ORDER BY gps.start_date DESC

SELECT ai.invoice_id,
       ai.invoice_num,
       ai.invoice_date,
       ai.invoice_currency_code,
       ps.segment1                 AS supplier_number,
       hp.party_name               AS supplier_name,
       aps.payment_num,
       aps.due_date,
       aps.gross_amount,
       aps.amount_remaining,
       aps.payment_status_flag
FROM   ap_invoices_all ai
JOIN   ap_payment_schedules_all aps
       ON aps.invoice_id = ai.invoice_id
JOIN   poz_suppliers ps
       ON ps.vendor_id = ai.vendor_id
JOIN   hz_parties hp
       ON hp.party_id = ps.party_id
WHERE  ai.cancelled_date IS NULL
AND    aps.payment_status_flag IN ('N', 'P')
AND    ai.org_id = :p_org_id
ORDER BY aps.due_date,
         hp.party_name,
         ai.invoice_num,
         aps.payment_num

SELECT ps.vendor_id,
       ps.segment1 AS supplier_number,
       hp.party_name AS supplier_name,
       ai.invoice_currency_code,
       SUM(CASE WHEN aps.due_date >= TRUNC(SYSDATE)
                THEN NVL(aps.amount_remaining, 0) ELSE 0 END) AS current_bucket,
       SUM(CASE WHEN TRUNC(SYSDATE) - aps.due_date BETWEEN 1 AND 30
                THEN NVL(aps.amount_remaining, 0) ELSE 0 END) AS bucket_1_30,
       SUM(CASE WHEN TRUNC(SYSDATE) - aps.due_date BETWEEN 31 AND 60
                THEN NVL(aps.amount_remaining, 0) ELSE 0 END) AS bucket_31_60,
       SUM(CASE WHEN TRUNC(SYSDATE) - aps.due_date BETWEEN 61 AND 90
                THEN NVL(aps.amount_remaining, 0) ELSE 0 END) AS bucket_61_90,
       SUM(CASE WHEN TRUNC(SYSDATE) - aps.due_date > 90
                THEN NVL(aps.amount_remaining, 0) ELSE 0 END) AS over_90
FROM   ap_invoices_all ai
JOIN   ap_payment_schedules_all aps
       ON aps.invoice_id = ai.invoice_id
JOIN   poz_suppliers ps
       ON ps.vendor_id = ai.vendor_id
JOIN   hz_parties hp
       ON hp.party_id = ps.party_id
WHERE  ai.cancelled_date IS NULL
AND    aps.payment_status_flag IN ('N', 'P')
AND    ai.org_id = :p_org_id
GROUP BY ps.vendor_id,
         ps.segment1,
         hp.party_name,
         ai.invoice_currency_code
ORDER BY over_90 DESC

SELECT ps.vendor_id,
       ps.segment1 AS supplier_number,
       hp.party_name AS supplier_name,
       ai.invoice_currency_code,
       COUNT(ai.invoice_id)         AS invoice_count,
       SUM(ai.invoice_amount)       AS total_invoiced,
       SUM(NVL(ai.amount_paid, 0))  AS total_paid
FROM   ap_invoices_all ai
JOIN   poz_suppliers ps
       ON ps.vendor_id = ai.vendor_id
JOIN   hz_parties hp
       ON hp.party_id = ps.party_id
WHERE  ai.invoice_date >= ADD_MONTHS(TRUNC(SYSDATE), -12)
AND    ai.cancelled_date IS NULL
AND    ai.org_id = :p_org_id
GROUP BY ps.vendor_id,
         ps.segment1,
         hp.party_name,
         ai.invoice_currency_code
ORDER BY total_invoiced DESC

SELECT DISTINCT
       ai.invoice_id,
       ai.invoice_num,
       ai.invoice_date,
       ai.invoice_amount,
       ph.po_header_id,
       ph.segment1 AS po_number,
       ps.segment1 AS supplier_number,
       hp.party_name AS supplier_name
FROM   ap_invoices_all ai
JOIN   ap_invoice_lines_all ail
       ON ail.invoice_id = ai.invoice_id
JOIN   po_headers_all ph
       ON ph.po_header_id = ail.po_header_id
JOIN   poz_suppliers ps
       ON ps.vendor_id = ai.vendor_id
JOIN   hz_parties hp
       ON hp.party_id = ps.party_id
WHERE  ail.po_header_id IS NOT NULL
AND    ai.cancelled_date IS NULL
AND    ai.org_id = :p_org_id
ORDER BY ai.invoice_date DESC,
         ai.invoice_num

SELECT ai.invoice_num,
       aid.invoice_distribution_id,
       aid.invoice_line_number,
       aid.distribution_line_number,
       aid.line_type_lookup_code,
       aid.accounting_date,
       aid.amount,
       aid.base_amount,
       gcc.concatenated_segments AS distribution_account
FROM   ap_invoices_all ai
JOIN   ap_invoice_distributions_all aid
       ON aid.invoice_id = ai.invoice_id
JOIN   gl_code_combinations gcc
       ON gcc.code_combination_id = aid.dist_code_combination_id
WHERE  ai.cancelled_date IS NULL
AND    ai.org_id = :p_org_id
ORDER BY ai.invoice_num,
         aid.invoice_line_number,
         aid.distribution_line_number

SELECT ac.check_id,
       ac.check_number,
       ac.check_date,
       ac.amount,
       ac.currency_code,
       ac.status_lookup_code,
       ac.payment_method_code,
       ps.segment1   AS supplier_number,
       hp.party_name AS supplier_name
FROM   ap_checks_all ac
JOIN   poz_suppliers ps
       ON ps.vendor_id = ac.vendor_id
JOIN   hz_parties hp
       ON hp.party_id = ps.party_id
WHERE  ac.check_date >= ADD_MONTHS(TRUNC(SYSDATE), -6)
ORDER BY ac.check_date DESC,
         ac.check_id DESC

SELECT hca.cust_account_id,
       hca.account_number,
       hp.party_name,
       aps.invoice_currency_code,
       SUM(aps.amount_due_remaining) AS outstanding_balance
FROM   ar_payment_schedules_all aps
JOIN   hz_cust_accounts hca
       ON hca.cust_account_id = aps.customer_id
JOIN   hz_parties hp
       ON hp.party_id = hca.party_id
WHERE  aps.status = 'OP'
AND    aps.class IN ('INV', 'DM', 'CB')
AND    aps.org_id = :p_org_id
GROUP BY hca.cust_account_id,
         hca.account_number,
         hp.party_name,
         aps.invoice_currency_code
ORDER BY outstanding_balance DESC

SELECT hca.cust_account_id,
       hca.account_number,
       hp.party_name,
       aps.invoice_currency_code,
       SUM(CASE WHEN aps.due_date >= TRUNC(SYSDATE)
                THEN aps.amount_due_remaining ELSE 0 END) AS current_bucket,
       SUM(CASE WHEN TRUNC(SYSDATE) - aps.due_date BETWEEN 1 AND 30
                THEN aps.amount_due_remaining ELSE 0 END) AS bucket_1_30,
       SUM(CASE WHEN TRUNC(SYSDATE) - aps.due_date BETWEEN 31 AND 60
                THEN aps.amount_due_remaining ELSE 0 END) AS bucket_31_60,
       SUM(CASE WHEN TRUNC(SYSDATE) - aps.due_date BETWEEN 61 AND 90
                THEN aps.amount_due_remaining ELSE 0 END) AS bucket_61_90,
       SUM(CASE WHEN TRUNC(SYSDATE) - aps.due_date > 90
                THEN aps.amount_due_remaining ELSE 0 END) AS over_90
FROM   ar_payment_schedules_all aps
JOIN   hz_cust_accounts hca
       ON hca.cust_account_id = aps.customer_id
JOIN   hz_parties hp
       ON hp.party_id = hca.party_id
WHERE  aps.status = 'OP'
AND    aps.class IN ('INV', 'DM', 'CB')
AND    aps.org_id = :p_org_id
GROUP BY hca.cust_account_id,
         hca.account_number,
         hp.party_name,
         aps.invoice_currency_code
ORDER BY over_90 DESC

SELECT rcta.customer_trx_id,
       hp.party_name,
       hca.account_number,
       rcta.trx_number,
       rcta.trx_date,
       rcta.invoice_currency_code,
       rcta.complete_flag
FROM   ra_customer_trx_all rcta
JOIN   hz_cust_accounts hca
       ON hca.cust_account_id = rcta.bill_to_customer_id
JOIN   hz_parties hp
       ON hp.party_id = hca.party_id
WHERE  rcta.complete_flag = 'Y'
AND    rcta.org_id = :p_org_id
AND    rcta.trx_date >= ADD_MONTHS(TRUNC(SYSDATE), -12)
ORDER BY rcta.trx_date DESC,
         rcta.trx_number

SELECT ai.invoice_id,
       ai.invoice_num,
       xte.entity_id,
       xah.ae_header_id,
       xah.accounting_date,
       xah.gl_transfer_status_code,
       xal.ae_line_num,
       xal.accounting_class_code,
       xal.accounted_dr,
       xal.accounted_cr,
       gcc.concatenated_segments AS accounting_account
FROM   ap_invoices_all ai
JOIN   xla_transaction_entities xte
       ON xte.application_id = 200
      AND xte.entity_code = 'AP_INVOICES'
      AND xte.source_id_int_1 = ai.invoice_id
JOIN   xla_ae_headers xah
       ON xah.entity_id      = xte.entity_id
      AND xah.application_id = xte.application_id
JOIN   xla_ae_lines xal
       ON xal.ae_header_id   = xah.ae_header_id
      AND xal.application_id = xah.application_id
JOIN   gl_code_combinations gcc
       ON gcc.code_combination_id = xal.code_combination_id
WHERE  ai.invoice_id = :p_invoice_id
ORDER BY xah.ae_header_id,
         xal.ae_line_num

SELECT ai.invoice_id,
       ai.invoice_num,
       xah.ae_header_id,
       xal.ae_line_num,
       xal.accounting_class_code,
       xal.accounted_dr AS sla_accounted_dr,
       xal.accounted_cr AS sla_accounted_cr,
       gjh.je_header_id,
       gjh.name          AS journal_name,
       gjh.period_name,
       gjl.je_line_num,
       gjl.accounted_dr  AS gl_accounted_dr,
       gjl.accounted_cr  AS gl_accounted_cr,
       gcc.concatenated_segments AS gl_account
FROM   ap_invoices_all ai
JOIN   xla_transaction_entities xte
       ON xte.application_id = 200
      AND xte.entity_code = 'AP_INVOICES'
      AND xte.source_id_int_1 = ai.invoice_id
JOIN   xla_ae_headers xah
       ON xah.entity_id      = xte.entity_id
      AND xah.application_id = xte.application_id
JOIN   xla_ae_lines xal
       ON xal.ae_header_id   = xah.ae_header_id
      AND xal.application_id = xah.application_id
JOIN   gl_import_references gir
       ON gir.gl_sl_link_id    = xal.gl_sl_link_id
      AND gir.gl_sl_link_table = xal.gl_sl_link_table
JOIN   gl_je_lines gjl
       ON gjl.je_header_id = gir.je_header_id
      AND gjl.je_line_num  = gir.je_line_num
JOIN   gl_je_headers gjh
       ON gjh.je_header_id = gjl.je_header_id
JOIN   gl_code_combinations gcc
       ON gcc.code_combination_id = gjl.code_combination_id
WHERE  ai.invoice_id = :p_invoice_id
ORDER BY gjh.je_header_id,
         gjl.je_line_num,
         xah.ae_header_id,
         xal.ae_line_num

SELECT cba.bank_account_id,
       cba.bank_account_name,
       cba.bank_account_num,
       cba.currency_code,
       cba.bank_account_type,
       cbau.bank_acct_use_id,
       cbau.org_id,
       cbau.legal_entity_id,
       cbau.ap_use_enable_flag,
       cbau.ar_use_enable_flag,
       cbau.xtr_use_enable_flag,
       cbau.pay_use_enable_flag,
       cba.start_date,
       cba.end_date,
       cbau.end_date AS use_end_date
FROM   ce_bank_accounts cba
JOIN   ce_bank_acct_uses_all cbau
       ON cbau.bank_account_id = cba.bank_account_id
WHERE  cba.start_date <= TRUNC(SYSDATE)
AND    (cba.end_date IS NULL OR cba.end_date >= TRUNC(SYSDATE))
AND    (cbau.end_date IS NULL OR cbau.end_date >= TRUNC(SYSDATE))
ORDER BY cba.bank_account_name,
         cbau.org_id

SELECT
    ph.segment1                         AS po_number,
    ph.approved_date,
    ph.document_status,
    ph.prc_bu_id,
    ph.currency_code,
    hp.party_name                      AS supplier_name,
    ps.segment1                        AS supplier_number,
    pl.line_num,
    pl.item_description,
    pll.shipment_num,
    pll.need_by_date,
    pll.quantity                       AS schedule_quantity,
    pll.price_override,
    pll.amount                         AS schedule_amount,
    CASE
      WHEN pll.value_basis = 'QUANTITY'
        THEN NVL(pll.quantity,0) * NVL(pll.price_override, pl.unit_price)
      ELSE NVL(pll.amount,0)
    END                                AS ordered_value
FROM po_headers_all ph
JOIN po_lines_all pl
  ON pl.po_header_id = ph.po_header_id
JOIN po_line_locations_all pll
  ON pll.po_line_id = pl.po_line_id
JOIN poz_suppliers ps
  ON ps.vendor_id = ph.vendor_id
JOIN hz_parties hp
  ON hp.party_id = ps.party_id
WHERE ph.type_lookup_code = 'STANDARD'
  AND ph.approved_flag = 'Y'
  AND NVL(ph.cancel_flag,'N') = 'N'
  AND NVL(pll.cancel_flag,'N') = 'N'
  AND ph.prc_bu_id = :p_prc_bu_id
ORDER BY ph.approved_date DESC, ph.segment1, pl.line_num, pll.shipment_num;

SELECT
    ph.segment1 AS po_number,
    pl.line_num,
    pl.item_description,
    ph.currency_code,
    pll.need_by_date,
    pll.quantity,
    NVL(pll.quantity_received,0)  AS quantity_received,
    NVL(pll.quantity_cancelled,0) AS quantity_cancelled,
    CASE
      WHEN pll.value_basis = 'QUANTITY' THEN
        GREATEST(NVL(pll.quantity,0)
               - NVL(pll.quantity_received,0)
               - NVL(pll.quantity_cancelled,0), 0)
        * NVL(pll.price_override, pl.unit_price)
      ELSE
        GREATEST(NVL(pll.amount,0)
               - NVL(pll.amount_received,0)
               - NVL(pll.amount_cancelled,0), 0)
    END AS open_to_receive_value
FROM po_headers_all ph
JOIN po_lines_all pl
  ON pl.po_header_id = ph.po_header_id
JOIN po_line_locations_all pll
  ON pll.po_line_id = pl.po_line_id
WHERE ph.approved_flag = 'Y'
  AND NVL(ph.cancel_flag,'N') = 'N'
  AND NVL(pll.cancel_flag,'N') = 'N'
  AND ph.prc_bu_id = :p_prc_bu_id
  AND NVL(pll.schedule_status,'OPEN') NOT IN ('FINALLY CLOSED','INCOMPLETE','WITHDRAWN','REJECTED')
ORDER BY pll.need_by_date;

WITH po_value AS (
  SELECT pl.po_header_id,
         SUM(CASE
               WHEN pll.value_basis = 'QUANTITY'
                 THEN NVL(pll.quantity,0) * NVL(pll.price_override,pl.unit_price)
               ELSE NVL(pll.amount,0)
             END) AS po_value
  FROM po_lines_all pl
  JOIN po_line_locations_all pll
    ON pll.po_line_id = pl.po_line_id
  WHERE NVL(pll.cancel_flag,'N') = 'N'
  GROUP BY pl.po_header_id
)
SELECT ph.segment1 AS po_number,
       ph.creation_date,
       ph.document_status,
       ph.currency_code,
       pv.po_value,
       hp.party_name AS supplier_name,
       ph.agent_id AS buyer_person_id
FROM po_headers_all ph
LEFT JOIN po_value pv
  ON pv.po_header_id = ph.po_header_id
LEFT JOIN poz_suppliers ps
  ON ps.vendor_id = ph.vendor_id
LEFT JOIN hz_parties hp
  ON hp.party_id = ps.party_id
WHERE ph.document_status = 'PENDING APPROVAL'
  AND NVL(ph.cancel_flag,'N') = 'N'
  AND ph.prc_bu_id = :p_prc_bu_id
ORDER BY ph.creation_date DESC;

SELECT ph.segment1 AS po_number,
       ph.creation_date,
       ph.approved_date,
       ph.document_status,
       ph.cancel_flag,
       ph.closed_date,
       hp.party_name AS supplier_name,
       ph.comments
FROM po_headers_all ph
LEFT JOIN poz_suppliers ps
  ON ps.vendor_id = ph.vendor_id
LEFT JOIN hz_parties hp
  ON hp.party_id = ps.party_id
WHERE (NVL(ph.cancel_flag,'N') = 'Y' OR ph.document_status = 'CANCELED')
  AND ph.prc_bu_id = :p_prc_bu_id
ORDER BY ph.creation_date DESC;

SELECT ph.segment1 AS po_number,
       pl.line_num,
       pll.shipment_num,
       pd.distribution_num,
       gcc.concatenated_segments AS charge_account,
       pd.quantity_ordered,
       pd.quantity_delivered,
       pd.quantity_billed,
       pd.quantity_cancelled,
       pd.amount_ordered,
       pd.amount_delivered,
       pd.amount_billed,
       pd.amount_cancelled,
       pd.funds_status
FROM po_headers_all ph
JOIN po_lines_all pl
  ON pl.po_header_id = ph.po_header_id
JOIN po_line_locations_all pll
  ON pll.po_line_id = pl.po_line_id
JOIN po_distributions_all pd
  ON pd.line_location_id = pll.line_location_id
JOIN gl_code_combinations gcc
  ON gcc.code_combination_id = pd.code_combination_id
WHERE ph.approved_flag = 'Y'
  AND ph.prc_bu_id = :p_prc_bu_id
ORDER BY ph.segment1, pl.line_num, pll.shipment_num, pd.distribution_num;

SELECT rsh.receipt_num,
       rsh.shipment_num AS supplier_shipment_num,
       rt.transaction_date,
       rt.transaction_type,
       rt.quantity,
       rt.uom_code,
       ph.segment1 AS po_number,
       pl.item_description,
       hp.party_name AS supplier_name
FROM rcv_transactions rt
JOIN rcv_shipment_headers rsh
  ON rsh.shipment_header_id = rt.shipment_header_id
JOIN po_headers_all ph
  ON ph.po_header_id = rt.po_header_id
JOIN po_lines_all pl
  ON pl.po_line_id = rt.po_line_id
LEFT JOIN poz_suppliers ps
  ON ps.vendor_id = ph.vendor_id
LEFT JOIN hz_parties hp
  ON hp.party_id = ps.party_id
WHERE rt.transaction_type = 'RECEIVE'
  AND rt.transaction_date >= ADD_MONTHS(TRUNC(SYSDATE),-3)
  AND ph.prc_bu_id = :p_prc_bu_id
ORDER BY rt.transaction_date DESC;

SELECT ph.segment1 AS po_number,
       pl.line_num,
       pl.item_description,
       pll.shipment_num,
       pll.need_by_date,
       TRUNC(SYSDATE) - TRUNC(pll.need_by_date) AS days_overdue,
       hp.party_name AS supplier_name,
       ph.currency_code,
       CASE
         WHEN pll.value_basis = 'QUANTITY' THEN
           GREATEST(NVL(pll.quantity,0)
                  - NVL(pll.quantity_received,0)
                  - NVL(pll.quantity_cancelled,0),0)
           * NVL(pll.price_override,pl.unit_price)
         ELSE
           GREATEST(NVL(pll.amount,0)
                  - NVL(pll.amount_received,0)
                  - NVL(pll.amount_cancelled,0),0)
       END AS overdue_open_value
FROM po_headers_all ph
JOIN po_lines_all pl
  ON pl.po_header_id = ph.po_header_id
JOIN po_line_locations_all pll
  ON pll.po_line_id = pl.po_line_id
LEFT JOIN poz_suppliers ps
  ON ps.vendor_id = ph.vendor_id
LEFT JOIN hz_parties hp
  ON hp.party_id = ps.party_id
WHERE ph.approved_flag = 'Y'
  AND ph.prc_bu_id = :p_prc_bu_id
  AND NVL(ph.cancel_flag,'N') = 'N'
  AND NVL(pll.cancel_flag,'N') = 'N'
  AND pll.need_by_date < TRUNC(SYSDATE)
  AND (
       (pll.value_basis = 'QUANTITY' AND
        NVL(pll.quantity,0) - NVL(pll.quantity_received,0) - NVL(pll.quantity_cancelled,0) > 0)
       OR
       (NVL(pll.value_basis,'AMOUNT') <> 'QUANTITY' AND
        NVL(pll.amount,0) - NVL(pll.amount_received,0) - NVL(pll.amount_cancelled,0) > 0)
      )
ORDER BY days_overdue DESC;

SELECT rsh.receipt_num,
       rt.transaction_id,
       rt.parent_transaction_id,
       rt.transaction_type,
       rt.transaction_date,
       rt.quantity,
       rt.uom_code,
       ph.segment1 AS po_number,
       hp.party_name AS supplier_name
FROM rcv_transactions rt
JOIN rcv_shipment_headers rsh
  ON rsh.shipment_header_id = rt.shipment_header_id
LEFT JOIN po_headers_all ph
  ON ph.po_header_id = rt.po_header_id
LEFT JOIN poz_suppliers ps
  ON ps.vendor_id = ph.vendor_id
LEFT JOIN hz_parties hp
  ON hp.party_id = ps.party_id
WHERE rt.transaction_type IN ('RETURN TO VENDOR','CORRECT')
  AND (:p_prc_bu_id IS NULL OR ph.prc_bu_id = :p_prc_bu_id)
ORDER BY rt.transaction_date DESC;

SELECT hp.party_name AS supplier_name,
       ps.segment1 AS supplier_number,
       ai.invoice_currency_code,
       COUNT(DISTINCT ai.invoice_id) AS invoice_count,
       COUNT(*) AS matched_invoice_line_count,
       SUM(ail.amount) AS po_matched_invoiced_spend
FROM ap_invoices_all ai
JOIN ap_invoice_lines_all ail
  ON ail.invoice_id = ai.invoice_id
JOIN poz_suppliers ps
  ON ps.vendor_id = ai.vendor_id
JOIN hz_parties hp
  ON hp.party_id = ps.party_id
WHERE ail.po_header_id IS NOT NULL
  AND NVL(ail.discarded_flag,'N') = 'N'
  AND ai.cancelled_date IS NULL
  AND ai.invoice_date >= ADD_MONTHS(TRUNC(SYSDATE),-12)
  AND ai.org_id = :p_bu_id
GROUP BY hp.party_name, ps.segment1, ai.invoice_currency_code
ORDER BY po_matched_invoiced_spend DESC;

SELECT *
FROM (
  SELECT hp.party_name AS supplier_name,
         ps.segment1 AS supplier_number,
         SUM(ail.amount) AS po_matched_invoiced_spend
  FROM ap_invoices_all ai
  JOIN ap_invoice_lines_all ail
    ON ail.invoice_id = ai.invoice_id
  JOIN poz_suppliers ps
    ON ps.vendor_id = ai.vendor_id
  JOIN hz_parties hp
    ON hp.party_id = ps.party_id
  WHERE ail.po_header_id IS NOT NULL
    AND ai.cancelled_date IS NULL
    AND ai.org_id = :p_bu_id
    AND ai.invoice_currency_code = :p_currency_code
    AND ai.invoice_date >= ADD_MONTHS(TRUNC(SYSDATE),-12)
  GROUP BY hp.party_name, ps.segment1
  ORDER BY po_matched_invoiced_spend DESC
)
WHERE ROWNUM <= 20;

SELECT ai.org_id AS payables_bu_id,
       hp.party_name AS supplier_name,
       ai.invoice_currency_code,
       COUNT(DISTINCT ai.invoice_id) AS invoice_count,
       SUM(ail.amount) AS po_matched_invoiced_spend
FROM ap_invoices_all ai
JOIN ap_invoice_lines_all ail
  ON ail.invoice_id = ai.invoice_id
JOIN poz_suppliers ps
  ON ps.vendor_id = ai.vendor_id
JOIN hz_parties hp
  ON hp.party_id = ps.party_id
WHERE ail.po_header_id IS NOT NULL
  AND NVL(ail.discarded_flag,'N') = 'N'
  AND ai.cancelled_date IS NULL
  AND ai.invoice_date >= ADD_MONTHS(TRUNC(SYSDATE),-12)
GROUP BY ai.org_id, hp.party_name, ai.invoice_currency_code
ORDER BY ai.org_id, po_matched_invoiced_spend DESC;

SELECT ph.segment1 AS po_number,
       ai.invoice_num,
       ai.invoice_date,
       ai.invoice_currency_code,
       ail.line_number AS invoice_line_number,
       ail.line_type_lookup_code,
       ail.amount AS invoice_line_amount,
       ail.po_line_location_id,
       ail.po_distribution_id,
       ail.rcv_transaction_id,
       hp.party_name AS supplier_name
FROM ap_invoices_all ai
JOIN ap_invoice_lines_all ail
  ON ail.invoice_id = ai.invoice_id
JOIN po_headers_all ph
  ON ph.po_header_id = ail.po_header_id
JOIN poz_suppliers ps
  ON ps.vendor_id = ai.vendor_id
JOIN hz_parties hp
  ON hp.party_id = ps.party_id
WHERE ail.po_header_id IS NOT NULL
  AND NVL(ail.discarded_flag,'N') = 'N'
  AND ai.cancelled_date IS NULL
  AND ai.org_id = :p_bu_id
ORDER BY ai.invoice_date DESC, ai.invoice_num, ail.line_number;

SELECT ai.invoice_num,
       ai.invoice_date,
       ail.line_number AS invoice_line_number,
       ail.amount AS invoice_line_amount,
       ph.segment1 AS po_number,
       pl.line_num AS po_line_num,
       pll.shipment_num,
       hp.party_name AS supplier_name
FROM ap_invoices_all ai
JOIN ap_invoice_lines_all ail
  ON ail.invoice_id = ai.invoice_id
JOIN po_line_locations_all pll
  ON pll.line_location_id = ail.po_line_location_id
JOIN po_lines_all pl
  ON pl.po_line_id = pll.po_line_id
JOIN po_headers_all ph
  ON ph.po_header_id = pl.po_header_id
JOIN poz_suppliers ps
  ON ps.vendor_id = ai.vendor_id
JOIN hz_parties hp
  ON hp.party_id = ps.party_id
WHERE ai.cancelled_date IS NULL
  AND pll.receipt_required_flag = 'Y'
  AND ail.rcv_transaction_id IS NULL
  AND NOT EXISTS (
      SELECT 1
      FROM rcv_transactions rt
      WHERE rt.po_line_location_id = pll.line_location_id
        AND rt.transaction_type = 'RECEIVE'
  )
  AND ai.org_id = :p_bu_id
ORDER BY ai.invoice_date DESC;

WITH po_value AS (
  SELECT ph.po_header_id,
         ph.agent_id,
         ph.currency_code,
         SUM(CASE
               WHEN pll.value_basis = 'QUANTITY'
                 THEN NVL(pll.quantity,0) * NVL(pll.price_override,pl.unit_price)
               ELSE NVL(pll.amount,0)
             END) AS po_value
  FROM po_headers_all ph
  JOIN po_lines_all pl
    ON pl.po_header_id = ph.po_header_id
  JOIN po_line_locations_all pll
    ON pll.po_line_id = pl.po_line_id
  WHERE ph.approved_flag = 'Y'
    AND NVL(ph.cancel_flag,'N') = 'N'
    AND NVL(pll.cancel_flag,'N') = 'N'
    AND ph.prc_bu_id = :p_prc_bu_id
  GROUP BY ph.po_header_id, ph.agent_id, ph.currency_code
)
SELECT pn.display_name AS buyer_name,
       pv.currency_code,
       COUNT(DISTINCT pv.po_header_id) AS po_count,
       SUM(pv.po_value) AS approved_po_value
FROM po_value pv
JOIN per_person_names_f pn
  ON pn.person_id = pv.agent_id
 AND pn.name_type = 'GLOBAL'
 AND TRUNC(SYSDATE) BETWEEN pn.effective_start_date AND pn.effective_end_date
GROUP BY pn.display_name, pv.currency_code
ORDER BY approved_po_value DESC;

SELECT TO_CHAR(ph.approved_date,'YYYY-MM') AS approval_month,
       ph.currency_code,
       SUM(CASE
             WHEN pll.value_basis = 'QUANTITY'
               THEN NVL(pll.quantity,0) * NVL(pll.price_override,pl.unit_price)
             ELSE NVL(pll.amount,0)
           END) AS approved_po_value
FROM po_headers_all ph
JOIN po_lines_all pl
  ON pl.po_header_id = ph.po_header_id
JOIN po_line_locations_all pll
  ON pll.po_line_id = pl.po_line_id
WHERE ph.approved_flag = 'Y'
  AND NVL(ph.cancel_flag,'N') = 'N'
  AND NVL(pll.cancel_flag,'N') = 'N'
  AND ph.prc_bu_id = :p_prc_bu_id
  AND ph.approved_date >= ADD_MONTHS(TRUNC(SYSDATE,'MM'),-12)
GROUP BY TO_CHAR(ph.approved_date,'YYYY-MM'), ph.currency_code
ORDER BY approval_month, ph.currency_code;

SELECT rh.requisition_number,
       rh.creation_date,
       rh.document_status,
       rh.req_bu_id,
       rl.line_number,
       rl.item_description,
       rl.quantity,
       rl.unit_price,
       rl.need_by_date,
       rl.line_status
FROM por_requisition_headers_all rh
JOIN por_requisition_lines_all rl
  ON rl.requisition_header_id = rh.requisition_header_id
WHERE rh.document_status = 'APPROVED'
  AND rh.req_bu_id = :p_req_bu_id
  AND NVL(rl.cancel_flag,'N') = 'N'
  AND NVL(rl.line_status,'APPROVED') = 'APPROVED'
  AND NOT EXISTS (
      SELECT 1
      FROM por_req_distributions_all rd
      JOIN po_distributions_all pd
        ON pd.req_distribution_id = rd.distribution_id
      WHERE rd.requisition_line_id = rl.requisition_line_id
  )
ORDER BY rh.creation_date DESC, rh.requisition_number, rl.line_number;

SELECT rh.requisition_number,
       rh.req_bu_id,
       rl.line_number AS req_line_number,
       rl.item_description,
       rd.distribution_number AS req_distribution_number,
       ph.segment1 AS po_number,
       ph.prc_bu_id,
       pl.line_num AS po_line_number,
       pll.shipment_num,
       pd.distribution_num AS po_distribution_number,
       pll.quantity AS po_schedule_quantity,
       pll.quantity_received,
       ph.document_status AS po_status
FROM por_requisition_headers_all rh
JOIN por_requisition_lines_all rl
  ON rl.requisition_header_id = rh.requisition_header_id
JOIN por_req_distributions_all rd
  ON rd.requisition_line_id = rl.requisition_line_id
JOIN po_distributions_all pd
  ON pd.req_distribution_id = rd.distribution_id
JOIN po_line_locations_all pll
  ON pll.line_location_id = pd.line_location_id
JOIN po_lines_all pl
  ON pl.po_line_id = pll.po_line_id
JOIN po_headers_all ph
  ON ph.po_header_id = pl.po_header_id
WHERE rh.req_bu_id = :p_req_bu_id
ORDER BY rh.requisition_number, rl.line_number, rd.distribution_number;

SELECT gcc.concatenated_segments AS charge_account,
       ph.currency_code,
       SUM(
         CASE
           WHEN NVL(pll.matching_basis,'QUANTITY') = 'AMOUNT' THEN
             GREATEST(NVL(pd.amount_ordered,0)
                    - NVL(pd.amount_cancelled,0)
                    - NVL(pd.amount_billed,0), 0)
           ELSE
             GREATEST(NVL(pd.quantity_ordered,0)
                    - NVL(pd.quantity_cancelled,0)
                    - NVL(pd.quantity_billed,0), 0)
             * NVL(pll.price_override, pl.unit_price)
         END
       ) AS unbilled_po_exposure
FROM po_distributions_all pd
JOIN po_line_locations_all pll
  ON pll.line_location_id = pd.line_location_id
JOIN po_lines_all pl
  ON pl.po_line_id = pll.po_line_id
JOIN po_headers_all ph
  ON ph.po_header_id = pl.po_header_id
JOIN gl_code_combinations gcc
  ON gcc.code_combination_id = pd.code_combination_id
WHERE ph.approved_flag = 'Y'
  AND NVL(ph.cancel_flag,'N') = 'N'
  AND NVL(pll.cancel_flag,'N') = 'N'
  AND pd.prc_bu_id = :p_prc_bu_id
GROUP BY gcc.concatenated_segments, ph.currency_code
HAVING SUM(
         CASE
           WHEN NVL(pll.matching_basis,'QUANTITY') = 'AMOUNT' THEN
             GREATEST(NVL(pd.amount_ordered,0)
                    - NVL(pd.amount_cancelled,0)
                    - NVL(pd.amount_billed,0), 0)
           ELSE
             GREATEST(NVL(pd.quantity_ordered,0)
                    - NVL(pd.quantity_cancelled,0)
                    - NVL(pd.quantity_billed,0), 0)
             * NVL(pll.price_override, pl.unit_price)
         END
       ) > 0
ORDER BY unbilled_po_exposure DESC;

SELECT p.person_number,
       pn.display_name,
       pe.assignment_id,
       et.element_name,
       pe.entry_type,
       pe.date_from,
       pe.date_to,
       pe.processed_flag
FROM   pay_element_entries_vl pe
JOIN   pay_element_types_vl et
       ON et.element_type_id = pe.element_type_id
      AND TRUNC(SYSDATE)
          BETWEEN et.effective_start_date AND et.effective_end_date
JOIN   per_all_people_f p
       ON p.person_id = pe.person_id
      AND TRUNC(SYSDATE)
          BETWEEN p.effective_start_date AND p.effective_end_date
JOIN   per_person_names_f pn
       ON pn.person_id = pe.person_id
      AND pn.name_type = 'GLOBAL'
      AND TRUNC(SYSDATE)
          BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  TRUNC(SYSDATE)
       BETWEEN pe.effective_start_date AND pe.effective_end_date
AND    p.person_number = :p_person_number
ORDER BY et.element_name,
         pe.element_entry_id

SELECT person_number,
       full_name,
       payroll_name,
       action_type,
       status,
       date_earned,
       process_date,
       statutory_period_name,
       payroll_action_id,
       payroll_rel_action_id
FROM   pay_search_actions_v
WHERE  action_code = 'PRA'
AND    person_number = :p_person_number
AND    process_date >= TRUNC(:p_start_date)
AND    process_date <  TRUNC(:p_end_date) + 1
ORDER BY process_date DESC,
         payroll_action_id DESC

SELECT flow_instance_id,
       task_instance_id,
       flow_task_name,
       object_type,
       object_number       AS person_or_object_number,
       payroll_name,
       message_level,
       message_level_meaning,
       message_text
FROM   pay_process_messages_vl
WHERE  flow_instance_id = :p_flow_instance_id
ORDER BY task_instance_id,
         message_level,
         msg_source_id

SELECT et.element_name,
       et.reporting_name,
       et.processing_type,
       iv.name             AS input_value_name,
       iv.base_name,
       iv.uom,
       iv.mandatory_flag,
       iv.user_enterable_flag,
       iv.default_value
FROM   pay_element_types_vl et
JOIN   pay_input_values_vl iv
       ON iv.element_type_id = et.element_type_id
      AND TRUNC(SYSDATE)
          BETWEEN iv.effective_start_date AND iv.effective_end_date
WHERE  TRUNC(SYSDATE)
       BETWEEN et.effective_start_date AND et.effective_end_date
AND    et.element_name = :p_element_name
ORDER BY iv.display_sequence,
         iv.name

SELECT rrv.run_result_id,
       piv.name       AS input_value_name,
       piv.base_name,
       piv.uom,
       rrv.result_value,
       rrv.formula_result_flag
FROM   pay_run_result_values rrv
JOIN   pay_input_values_vl piv
       ON piv.input_value_id = rrv.input_value_id
      AND :p_effective_date
          BETWEEN piv.effective_start_date AND piv.effective_end_date
WHERE  rrv.run_result_id = :p_run_result_id
ORDER BY piv.display_sequence,
         piv.name

SELECT doh.header_id,
       doh.order_number,
       doh.source_order_system,
       doh.source_order_number,
       doh.ordered_date,
       doh.status_code,
       doh.transactional_currency_code,
       hp.party_name AS sold_to_party
FROM   doo_headers_all doh
LEFT JOIN hz_parties hp
       ON hp.party_id = doh.sold_to_party_id
WHERE  doh.org_id = :p_org_id
AND    doh.open_flag = 'Y'
AND    NVL(doh.canceled_flag, 'N') = 'N'
ORDER BY doh.ordered_date DESC,
         doh.order_number

SELECT source_order_number,
       source_line_number,
       fulfill_line_number,
       status_code,
       inventory_item_id,
       ordered_qty,
       shipped_qty,
       fulfilled_qty,
       schedule_ship_date,
       promise_ship_date,
       actual_ship_date,
       fulfill_org_id
FROM   doo_fulfill_lines_all_v
WHERE  org_id = :p_org_id
AND    open_flag = 'Y'
AND    NVL(canceled_flag, 'N') = 'N'
AND    schedule_ship_date < TRUNC(SYSDATE)
AND    NVL(shipped_qty, 0) < NVL(ordered_qty, 0)
ORDER BY schedule_ship_date,
         source_order_number,
         source_line_number

SELECT source_order_number,
       source_line_number,
       fulfill_line_number,
       status_code,
       on_hold,
       ordered_qty,
       fulfilled_qty,
       schedule_ship_date,
       fulfill_org_id
FROM   doo_fulfill_lines_all_v
WHERE  org_id = :p_org_id
AND    on_hold = 'Y'
ORDER BY source_order_number,
         source_line_number,
         fulfill_line_number

SELECT organization_code,
       item_number,
       subinventory_code,
       locator_name,
       lot_number,
       primary_uom_code,
       quantity
FROM   inv_onhand_quantities_summary
WHERE  organization_id = :p_organization_id
AND    quantity <> 0
ORDER BY item_number,
         subinventory_code,
         locator_name,
         lot_number

SELECT organization_code,
       item_number,
       subinventory_code,
       lot_number,
       owning_type,
       owning_entity_name,
       primary_uom_code,
       quantity
FROM   inv_onhand_quantities_summary
WHERE  organization_id = :p_organization_id
AND    owning_type IS NOT NULL
AND    quantity <> 0
ORDER BY owning_entity_name,
         item_number,
         subinventory_code

SELECT rsh.receipt_num,
       rt.transaction_id,
       rt.transaction_type,
       rt.transaction_date,
       ph.segment1 AS po_number,
       pl.line_num,
       rt.po_line_location_id,
       rt.po_distribution_id,
       rt.source_doc_quantity,
       rt.source_doc_uom_code,
       rt.destination_type_code
FROM   rcv_transactions rt
JOIN   rcv_shipment_headers rsh
       ON rsh.shipment_header_id = rt.shipment_header_id
LEFT JOIN po_headers_all ph
       ON ph.po_header_id = rt.po_header_id
LEFT JOIN po_lines_all pl
       ON pl.po_line_id = rt.po_line_id
WHERE  rt.transaction_date >= TRUNC(:p_start_date)
AND    rt.transaction_date <  TRUNC(:p_end_date) + 1
ORDER BY rt.transaction_date DESC,
         rsh.receipt_num,
         rt.transaction_id

SELECT project_id,
       segment1 AS project_number,
       name     AS project_name,
       project_status_code,
       start_date,
       completion_date,
       closed_date,
       project_currency_code,
       projfunc_currency_code
FROM   pjf_projects_all_vl
WHERE  org_id = :p_project_bu_id
AND    (:p_status_code IS NULL OR project_status_code = :p_status_code)
ORDER BY segment1

SELECT p.segment1 AS project_number,
       p.name     AS project_name,
       ei.expenditure_item_id,
       ei.task_id,
       ei.expenditure_item_date,
       ei.expenditure_type_id,
       ei.quantity,
       ei.projfunc_currency_code,
       ei.projfunc_raw_cost,
       ei.projfunc_burdened_cost,
       ei.billable_flag,
       ei.invoiced_flag
FROM   pjc_exp_items_all ei
JOIN   pjf_projects_all_vl p
       ON p.project_id = ei.project_id
WHERE  p.org_id = :p_project_bu_id
AND    ei.expenditure_item_date >= TRUNC(:p_start_date)
AND    ei.expenditure_item_date <  TRUNC(:p_end_date) + 1
ORDER BY ei.expenditure_item_date,
         p.segment1,
         ei.expenditure_item_id

SELECT p.segment1 AS project_number,
       p.name     AS project_name,
       ei.expenditure_item_id,
       ei.task_id,
       ei.expenditure_item_date,
       ei.projfunc_currency_code,
       ei.projfunc_raw_cost,
       ei.billable_flag,
       ei.bill_hold_flag,
       ei.invoiced_flag
FROM   pjc_exp_items_all ei
JOIN   pjf_projects_all_vl p
       ON p.project_id = ei.project_id
WHERE  p.org_id = :p_project_bu_id
AND    ei.billable_flag = 'Y'
AND    NVL(ei.bill_hold_flag, 'N') = 'N'
AND    NVL(ei.invoiced_flag, 'N') <> 'Y'
ORDER BY p.segment1,
         ei.expenditure_item_date,
         ei.expenditure_item_id

SELECT p.segment1 AS project_number,
       p.name     AS project_name,
       ei.projfunc_currency_code,
       COUNT(*) AS expenditure_item_count,
       SUM(ei.projfunc_raw_cost)      AS raw_cost,
       SUM(ei.projfunc_burdened_cost) AS burdened_cost
FROM   pjc_exp_items_all ei
JOIN   pjf_projects_all_vl p
       ON p.project_id = ei.project_id
WHERE  p.org_id = :p_project_bu_id
AND    ei.expenditure_item_date >= TRUNC(:p_start_date)
AND    ei.expenditure_item_date <  TRUNC(:p_end_date) + 1
GROUP BY p.segment1,
         p.name,
         ei.projfunc_currency_code
ORDER BY p.segment1,
         ei.projfunc_currency_code

WITH current_user_name AS (
    SELECT user_id,
           username,
           person_id
    FROM   per_user_history
    WHERE  TRUNC(SYSDATE) >= start_date
    AND    (end_date IS NULL OR TRUNC(SYSDATE) <= end_date)
)
SELECT u.username,
       u.person_id,
       r.role_name,
       r.role_common_name,
       r.abstract_role,
       r.job_role,
       r.data_role,
       r.duty_role,
       ur.method_code,
       ur.start_date,
       ur.end_date
FROM   current_user_name u
JOIN   per_user_roles ur
       ON ur.user_id = u.user_id
JOIN   per_roles_dn_vl r
       ON r.role_id = ur.role_id
WHERE  ur.active_flag = 'Y'
AND    TRUNC(SYSDATE) >= ur.start_date
AND    (ur.end_date IS NULL OR TRUNC(SYSDATE) <= ur.end_date)
ORDER BY u.username,
         r.role_name

SELECT user_id,
       person_id,
       party_id,
       username,
       user_guid,
       start_date,
       end_date,
       last_update_component,
       last_update_date
FROM   per_user_history
WHERE  user_id = :p_user_id
ORDER BY start_date,
         user_history_id

SELECT person_security_profile_id,
       name,
       description,
       view_all,
       secure_by_department,
       secure_by_business_unit,
       secure_by_aor,
       manager_types,
       max_levels_in_hierarchy,
       include_future_persons,
       include_shared_people_info
FROM   per_person_security_profiles
ORDER BY name

SELECT gdr.data_role_display_name,
       gdr.data_role_name,
       gdr.base_role_id,
       drp.hr_securing_object,
       drp.security_profile_id,
       drp.start_date,
       drp.end_date
FROM   per_generated_data_roles gdr
JOIN   per_gen_data_role_profiles drp
       ON drp.generated_data_role_id = gdr.generated_data_role_id
WHERE  TRUNC(SYSDATE) >= drp.start_date
AND    (drp.end_date IS NULL OR TRUNC(SYSDATE) <= drp.end_date)
ORDER BY gdr.data_role_display_name,
         drp.hr_securing_object,
         drp.security_profile_id

Performance Playbook: Optimize the Correct Query

Performance tuning starts only after correctness. The highest-leverage Fusion-specific practices are:

1. Filter the driving transaction early. Ledger/BU/date/object ID filters should reduce the working set before large detail joins.
2. Keep predicates sargable. Prefer half-open date ranges on transaction columns instead of wrapping indexed columns in functions when a timestamp range is intended.
3. Filter every effective object during the join. Avoid joining years of history and discarding it later.
4. Use EXISTS for existence questions. Do not join a 1:N child merely to prove that one child exists.
5. Aggregate at the lowest necessary layer. Pre-aggregate schedules/distributions when the final output is invoice or supplier grain.
6. Do not use DISTINCT as a cardinality repair tool. It can hide a wrong join while adding expensive sort/hash work.
7. Scope XLA aggressively. SLA tables are large; start from a transaction/application/entity/date whenever possible.
8. Remove unnecessary ORDER BY. Sort only when output order is a real requirement.
9. Use BI Publisher data-model controls. SQL pruning and skipping unused datasets can avoid work that a layout never consumes.
10. Validate with production-like cardinality. A query that is instant on 100 rows says little about a tenant with years of payroll, AP or XLA history.

A Production Validation Workflow

1. Pick one known business document/person/project and predict the expected rows manually.
2. Run the smallest core-object query first.
3. Add joins one at a time and record row-count changes.
4. Check one-to-many relationships before adding aggregate measures.
5. Test current, historical and edge dates.
6. Test canceled/terminated/closed/partially paid/partially received cases.
7. Validate one amount in the application at the same grain.
8. Validate currency explicitly.
9. Test the report as the intended runtime user/security context.
10. Only after row-level validation, reconcile aggregates and tune performance.

SQL, OTBI, Publisher and Bulk Extraction Solve Different Problems

Where FusionLens Fits

The recurring failure modes in this guide are exactly the parts that benefit from metadata intelligence: identifying the current Fusion object, finding the real column, understanding whether a table is date-effective, following a documented relationship, seeing the expected grain, and validating SQL against the connected Fusion reporting layer.

FusionLens doesn't make semantic decisions disappear. It reduces the amount of guessing required before you can make them: schema navigation, relationship discovery, SQL history, metadata search and live query validation are there to shorten the path from business question to verified SQL.

Frequently Asked Questions

Continue Learning

• Oracle Fusion Tables Reference — physical data model, keys, suffixes, relationships and module object families.
• Oracle Fusion Mental Models — business ownership, lifecycle, enterprise structure, security and integration reasoning.
• Effective Date Handling in Oracle Fusion SQL — deeper date-effective patterns.
• Oracle Fusion BI Publisher SQL Performance — query and data-model optimization.
• Oracle Fusion Table Relationships Explained — deeper relationship and cross-module grain examples.

Conclusion

The difference between generic Oracle SQL and Oracle Fusion SQL is not syntax. It is the data model behind the syntax.

Once you learn to identify the true business grain, current object, relationship path, time semantics, scope, currency and security model, the next query stops being a completely new problem. It becomes another combination of patterns you already understand.