Quick Start — How to Run SQL Query against Oracle Fusion
What SQL4Fusion does, in one line
An Oracle Fusion-aware SQL Editor and script library that helps your team author, review, and reuse production-ready SQL for ERP, HCM, and SCM, then hand it off to BICC, BI Publisher, or your warehouse with fewer rewrites.
Quick Start — How to Run SQL Query against Oracle Fusion
What SQL4Fusion does, in one line
A Oracle Fusion-aware SQL Editor and script library that helps your team author, review, and reuse production-ready SQL for ERP, HCM, and SCM, then hand it off to BICC, BI Publisher, or your warehouse with fewer rewrites.
Where SQL4Fusion fits in your stack
BICC for scheduled bulk extracts → Use SQL4Fusion to identify the right PVO and columns to extract for your warehouse
BI Publisher (BIP) for curated reports → Use SQL4Fusion to draft and peer-review SQL before promoting to BIP data models.
Warehouse/ADW for analytics at scale → Use SQL4Fusion to keep a governed library of Fusion-aware queries for ELT jobs and analyst self-serve.
OTBI/APIs for lightweight needs → Use SQL4Fusion when complexity grows beyond subject areas or when you want team-wide reuse.
Choose the right path
High volume, repeatable loads: Prefer BICC + warehouse.
Curated business reports: Prefer BIP.
Ad-hoc and advanced joins: Prefer warehouse/ADW.
Team collaboration and governance: Start in SQL4Fusion → review → promote to BICC/BIP/ELT.
Adhoc reporting: Use SQL4Fusion.
Core Concepts
You Must Know
Ledgers, COA segments, calendars, period statuses
What trips teams up: Cross-ledger noise, wrong calendar joins, open vs closed period confusion.
How SQL4Fusion helps: Pre-filtered templates with required predicates for ledger, calendar, and period status, plus segment pickers for company, cost center, and account.
Reference vs transaction tables
What trips teams up: Mixing master data and transactions, missing keys, or inconsistent joins.
How SQL4Fusion helps: Joins blueprints per module (GL, AP, AR, FA, Procurement, HCM, SCM) and provides inline guidance on which keys to use so relationships are
ERP (Financials) —
Copy-Paste SQL Examples
GL: balances by period and segment
— GL balances by period with segment filters
SELECT
gb.ledger_id,
gcc.segment1 AS company,
gcc.segment2 AS cost_center,
gcc.segment3 AS account,
gb.period_name,
gb.begin_balance_dr,
gb.begin_balance_cr,
gb.period_net_dr,
gb.period_net_cr
FROM gl_balances gb
JOIN gl_code_combinations gcc ON gcc.code_combination_id = gb.code_combination_id
WHERE gb.ledger_id = :LEDGER_ID
AND gb.period_name = :PERIOD_NAME
AND gcc.segment1 = :COMPANY
AND gcc.segment3 = :ACCOUNT ;
Payables (AP): open vs paid invoices, aging
— AP invoice status with due buckets
SELECT
ai.invoice_num,
ai.invoice_date,
ai.invoice_amount,
aps.vendor_name,
aps.segment1 AS supplier_number,
apsa.amount_paid,
CASE
WHEN ai.payment_status_flag = ‘Y’ THEN ‘PAID’
WHEN ai.payment_status_flag = ‘N’ THEN ‘UNPAID’
ELSE ‘N/A’
END AS payment_status,
TRUNC(SYSDATE) – TRUNC(ai.invoice_date) AS days_since_invoice
FROM ap_invoices_all ai
JOIN ap_suppliers aps ON aps.vendor_id = ai.vendor_id
LEFT JOIN ap_invoice_payments_all aipa ON aipa.invoice_id = ai.invoice_id
LEFT JOIN ap_payment_schedules_all apsa ON apsa.invoice_id = ai.invoice_id
WHERE ai.set_of_books_id = :LEDGER_ID
AND ai.invoice_date BETWEEN :START_DATE AND :END_DATE;
Receivables (AR): customer balances and receipts
— AR open balance by customer with last receipt date
SELECT
hp.party_name AS customer_name,
hca.account_number,
SUM(CASE WHEN ra.status = ‘OPEN’ THEN ra.amount_due_remaining ELSE 0 END) AS open_balance,
MAX(rc.cash_receipt_date) AS last_receipt_date
FROM ar_payment_schedules_all ra
JOIN hz_cust_accounts hca ON hca.cust_account_id = ra.customer_id
JOIN hz_parties hp ON hp.party_id = hca.party_id
LEFT JOIN ar_cash_receipts_all rc ON rc.pay_from_customer = hca.cust_account_id
WHERE ra.org_id = :BUSINESS_UNIT_ID
AND ra.gl_date BETWEEN :START_PERIOD AND :END_PERIOD
GROUP BY hp.party_name, hca.account_number
ORDER BY open_balance DESC;
Payables (AP): open vs paid invoices, aging
— FA additions and retirements by period
SELECT
fa.asset_number,
fa.description,
fad.addition_date,
far.retirement_date,
fb.book_type_code,
fbd.deprn_amount,
fbd.period_counter
FROM fa_additions_b fa
LEFT JOIN fa_asset_additions fad ON fad.asset_id = fa.asset_id
LEFT JOIN fa_retirements far ON far.asset_id = fa.asset_id
LEFT JOIN fa_books fb ON fb.asset_id = fa.asset_id
LEFT JOIN fa_deprn_detail fbd ON fbd.asset_id = fa.asset_id
WHERE fb.book_type_code = :BOOK
AND fbd.period_counter BETWEEN :START_PC AND :END_PC;
Cash Management (CE): bank statements and reconciliations
SELECT
cbb.bank_account_name,
cbs.statement_number,
cbsl.trx_date,
cbsl.trx_code,
cbsl.amount,
cbsl.currency_code,
NVL(cbsl.reconciliation_flag, ‘N’) AS reconciled
FROM ce_bank_accounts cba
JOIN ce_bank_branches_v cbb ON cbb.branch_party_id = cba.branch_id
JOIN ce_bank_statements cbs ON cbs.bank_account_id = cba.bank_account_id
JOIN ce_bank_stmt_lines cbsl ON cbsl.statement_id = cbs.statement_id
WHERE cba.bank_account_id = :BANK_ACCOUNT_ID
AND cbsl.trx_date BETWEEN :START_DATE AND :END_DATE
ORDER BY cbsl.trx_date DESC;
HCM —
Common SQL Templates
Worker, assignment, and payroll readiness checks
Outcome: Confirm active workers, primary assignments, and payroll eligibility before each cycle.
Parameters: Legal employer, business unit, payroll period, assignment status.
Built-in guardrails: Effective start/end dates, primary flag checks, exclusion of terminated workers by date.
Headcount, attrition, and transfers by period
Outcome: Accurate period-over-period movement with clear reasons.
Parameters: Period start/end, legal entity, department, grade, location.
Built-in guardrails: Point-in-time views, transfers vs. new hires vs. terminations, deduping rehires
Security contexts (who sees what)
Outcome: Align results with HCM role visibility for audits and self-serve management.
Parameters: Role, data access set, security profile.
Built-in guardrails: Optional audit joins for “who ran” and “who changed,” and notes on masking PII.
SCM and Procurement —
Frequently Needed SQL Templates
Purchase orders, receipts, and three way match
Outcome: Reconcile POs to receipts and invoices, and highlight mismatches before the end of the month.
Parameters: Business unit, supplier, item, PO status, date range.
Guardrails: Enforce matching conditions, exclude cancelled or returned lines, and optional tolerance filters.
Supplier performance snapshots
Outcome: On-time delivery, lead time variance, quality flags, and dispute rates by supplier.
Parameters: Supplier, category, period, site.
Guardrails: Standard definitions for on time, handling of partial receipts, and optional exclusion of disputed lines.
Item master, categories, and on hand or transactions
Outcome: Clean view of item attributes, category assignments, on hand, issues, and receipts.
Parameters: Item class, category set, subinventory, date range.
Guardrails: Effective date checks, unit of measure normalization, consistent item keys across modules.
Performance and Cost Tips
SQL4Fusion bakes performance patterns into templates, allowing you to query less data, avoid expensive joins, and control warehouse costs.
Predicate pushdown, selective columns, date or period filters
- Always filter by ledger, BU, and period first.
- Select only the columns you need; avoid “select *”.
- Standard predicates are pre-wired in SQL4Fusion templates to minimize scans.
Avoiding cross-module Cartesian joins
- Join through approved keys and staging views, not free-form cross joins.
- SQL4Fusion templates include safe join blueprints per module to keep row counts sane.
Alternatives & Next Steps
Use SQL4Fusion to author, review, and standardize your Fusion-aware SQL. Add Orbit DataJump when you need end-to-end data pipeline automation: BICC orchestration, schema-drift handling, warehouse loads, and SLA monitoring.
