Friday, 27 June 2025

Essential SQL Queries for Oracle Fusion Modules

 

Essential SQL Queries for Oracle Fusion Modules

Oracle Fusion Applications span across various modules such as HCM, Financials, Procurement, and Projects. Below are some practical SQL snippets that help in fetching data from key tables in each module. These queries can be used for reporting, troubleshooting, or data validation purposes.

1. Oracle Fusion HCM (Human Capital Management)

Fetch employee basic details:

sql
SELECT person.person_number,
       person.full_name,
       assignment.assignment_number,
       assignment.assignment_status_type,
       assignment.effective_start_date
FROM per_all_people_f person
JOIN per_all_assignments_f assignment
  ON person.person_id = assignment.person_id
WHERE SYSDATE BETWEEN person.effective_start_date AND person.effective_end_date
  AND SYSDATE BETWEEN assignment.effective_start_date AND assignment.effective_end_date;

Fetch absence records:

sql
SELECT person.full_name,
       absence.absence_type,
       absence.start_date,
       absence.end_date
FROM per_absence_entries absence
JOIN per_all_people_f person
  ON absence.person_id = person.person_id
WHERE absence.start_date >= TRUNC(SYSDATE) - 30;

2. Oracle Fusion Financials (Payables and Receivables)

AP Invoice Summary:

sql
SELECT invoice.invoice_num,
       invoice.invoice_date,
       invoice.invoice_amount,
       vendor.vendor_name
FROM ap_invoices_all invoice
JOIN ap_suppliers vendor
  ON invoice.vendor_id = vendor.vendor_id
WHERE invoice.invoice_date >= TRUNC(SYSDATE) - 90;

AR Customer Receipts:

sql
SELECT receipt.receipt_number,
       receipt.receipt_date,
       customer.customer_name,
       receipt.amount
FROM ar_cash_receipts_all receipt
JOIN hz_cust_accounts customer
  ON receipt.pay_from_customer = customer.cust_account_id
WHERE receipt.status = 'APPROVED';

3. Oracle Fusion Procurement

PO (Purchase Order) Headers and Lines:

sql
SELECT poh.po_header_id,
       poh.segment1 AS po_number,
       pol.line_num,
       pol.item_description,
       pol.quantity
FROM po_headers_all poh
JOIN po_lines_all pol
  ON poh.po_header_id = pol.po_header_id
WHERE poh.authorization_status = 'APPROVED';

Requisition Details:

sql
SELECT prh.requisition_header_id,
       prh.segment1 AS req_number,
       prl.line_num,
       prl.item_description,
       prl.quantity
FROM por_req_headers_all prh
JOIN por_req_lines_all prl
  ON prh.requisition_header_id = prl.requisition_header_id
WHERE prh.status = 'OPEN';

4. Oracle Fusion Projects (PPM)

Fetch project and task details:

sql
SELECT proj.project_number,
       proj.project_name,
       task.task_number,
       task.task_name
FROM pjf_projects_all_b proj
JOIN pjf_tasks task
  ON proj.project_id = task.project_id
WHERE proj.status_code = 'APPROVED';

Project expenditures summary:

sql
SELECT exp.expenditure_type,
       exp.amount,
       exp.expenditure_item_date,
       proj.project_number
FROM pjc_exp_items_all exp
JOIN pjf_projects_all_b proj
  ON exp.project_id = proj.project_id
WHERE exp.expenditure_item_date >= ADD_MONTHS(SYSDATE, -3);
at