Oracle Fusion SQL queries: 32 free read-only examples
32 read-only reference queries for Oracle Fusion Cloud ERP — period status, unposted journals, invoices on hold, bank reconciliation, open purchase orders, user and role lookups, scheduler health and more. Each one is a single SELECT written for the BI Publisher data-model SQL surface, so it runs in QueryWell or in any tool that reaches Fusion the supported way.
These are the reference and operational starters from QueryWell's DBA kit. QueryWell ships 82 scripts in all: this DBA kit, the Audit & ITGC kit with control references, and the Reports Toolkit of parameterised report starters.
General Ledger
GL period status by ledger
Lists all GL accounting periods per ledger with their open/closed status. Use to confirm month-end close is complete before running trial balance.
Unposted GL journals
Returns journals that exist in the system but have not yet been posted to balances. Indicates close-readiness and stale entries.
Reversed journals last 90 days
Journals that have been reversed — common in error correction, but spikes can indicate process issues.
Trial balance — natural account totals
Aggregates posted activity per natural account for a given ledger and period. Confirms feed of subledger to GL.
Journal count and amount by source (period)
Journal volume and debit/credit totals grouped by source and category for a period. Quick feel for where postings originate (Manual vs subledger feeds).
Top 50 journals by absolute amount this period
Largest journals in the current period across all ledgers — useful for management review and substantive testing sampling.
Accounts Payable
AP invoices currently on hold
Review AP hold records with no release code, alongside invoice and supplier details.
Top 25 suppliers by spend (last 12 months)
Highest-volume vendors over the trailing 12 months — supports vendor concentration analysis.
Potential duplicate invoices (supplier + amount + number)
Same supplier with identical invoice_num and amount but different invoice_id — strong duplicate-payment indicator.
Manual (quick) payments — last 90 days
Payments created as Manual or Quick (payment_type_flag M/Q) on ap_checks_all, outside the normal payment process — review for governance.
Fixed Assets
Cash Management
Procurement
Open purchase orders
POs that are approved but not yet fully received or invoiced — commits open against budget.
Procurement volume by buyer (last 90 days)
Spend created per buyer / agent — useful for workload and concentration views.
Three-way match exceptions
POs where invoiced amount exceeds the lower of ordered or received amount — indicates control breach or pricing change.
Security
Active Fusion application users
All active application users (per_users). Basic user inventory. (Fusion does not expose email or last-logon on this table.)
Direct role assignments per user
Every role currently granted directly to a user (autoprovisioned + manual). High-volume — filter to specific user via :username.
Top users by active role count
Active users ranked by number of currently-effective role assignments. High counts can indicate over-provisioning.
Scheduler (ESS)
ESS scheduled jobs in last 24h
All ESS requests submitted in the last 24 hours with status — operations dashboard view.
Long-running ESS jobs (>30 min)
ESS requests whose runtime exceeded 30 minutes in the last 7 days — performance hotspots.
Failed ESS jobs (last 30 days)
All ESS requests that ended in ERROR/WARNING in the last 30 days — incident triage and ITGC operations evidence.
Currently active ESS jobs (running / waiting)
ESS requests in a non-terminal state (wait/ready/running/blocked/hold/paused). STATE is a numeric ESS code. Operations view of in-flight work.
Setup & Config
Accounts Receivable
Human Capital
Active employees by department
Headcount by business unit / department / location — quarter-end staffing snapshot.
Recent hires (last 90 days)
New joiner list with start date, manager and position — useful when onboarding access controls.
Recent terminations (last 180 days)
Employees that left in the last 180 days — drives leaver-access reviews.
Want the audit and reporting starters too?
The free ITGC & reporting query pack adds a selection of Audit & ITGC scripts with control references and parameterised FIN, HCM and SCM report starters. We email it as a PDF; no card, no marketing list.