Oracle SQL Queries for various modules

This post will provide you with some of the useful Oracle  Queries used across the modules in the Oracle Fusion/ERP. 

Oracle Fusion applications spans across multiple enterprise modules such as 

1. Oracle Financials, 

2. Oracle Procurement, 

3. Oracle HCM (Human Capital Management)

4. Oracle SCM (Supply Chain Management) and others.  

Here’s are the list of useful Oracle  queries categorized by module. These queries help in analyzing, reporting, auditing, and troubleshooting in Oracle Fusion.

Please note these are sample queries only. Modify accordingly as per your requirements.

 

General Queries for Oracle Fusion

 1. Check User Roles & Responsibilities

SELECT u.user_name, r.role_name

FROM fnd_user u

JOIN fnd_user_roles ur ON u.user_id = ur.user_id

JOIN fnd_roles r ON ur.role_id = r.role_id

WHERE u.user_name = <Enter your username>

2. Find Last Login Details of a User

SELECT user_name, last_logon_date

FROM fnd_user

WHERE user_name = <Enter your username>

3. Retrieve Ledger Information

SELECT ledger_id, name, short_name, currency_code

FROM gl_ledgers;

 

 Oracle Fusion Financials (GL, AP, AR, FA)

 

4. Get GL Journals Details

SELECT journal_name, ledger_id, period_name, balance_type, creation_date

FROM gl_je_headers

WHERE period_name = 'JAN-2024';

5 Find Invoices Pending for Payment (AP)

SELECT invoice_num, supplier_name, invoice_amount, due_date, payment_status

FROM ap_invoices_all ai

JOIN ap_suppliers asup ON ai.vendor_id = asup.vendor_id

WHERE ai.payment_status = 'Unpaid';

6 List AR Customer Transactions

SELECT trx_number, customer_name, invoice_amount, balance_due, invoice_date

FROM ar_transactions_all

WHERE balance_due > 0;

7 Fixed Assets Details

SELECT asset_number, asset_category, cost, depreciation_expense

FROM fa_assets;

 

Oracle Fusion Procurement (PO, Supplier, Payables)

8  Find Purchase Orders by Supplier

SELECT po.po_number, po.po_date, s.supplier_name, po.total_amount, po.status

FROM po_headers_all po

JOIN po_vendors s ON po.vendor_id = s.vendor_id

WHERE s.supplier_name = <Enter your supplier name>

9 Query for PO Approval Status

SELECT po_number, approval_status, creation_date

FROM po_headers_all

WHERE approval_status IN ('In Progress', 'Rejected');

10. Supplier Information Query

SELECT vendor_name, vendor_number, vendor_type, tax_reference

FROM po_vendors

WHERE status = 'ACTIVE';

 

Oracle Fusion HCM (Human Capital Management)

11 Employee Details Query

SELECT person_number, full_name, job_title, department_name, hire_date, salary

FROM per_all_people_f

WHERE current_flag = 'Y';

12 Retrieve Employee Salary Information

SELECT employee_name, salary, currency, grade, effective_start_date

FROM per_pay_proposals

WHERE effective_start_date > SYSDATE - 365;

13 Get User’s Assigned Roles (HCM Security)

SELECT u.user_name, r.role_name, u.start_date, u.end_date

FROM fnd_user u

JOIN fnd_user_roles ur ON u.user_id = ur.user_id

JOIN fnd_roles r ON ur.role_id = r.role_id

WHERE u.user_name = 'EMPLOYEE_NAME';

Oracle Fusion SCM (Supply Chain Management)

14 Fetch Inventory Transactions

SELECT transaction_id, item_number, quantity, transaction_date, subinventory_code

FROM mtl_material_transactions

WHERE transaction_date >= TRUNC(SYSDATE) - 30;

15 Query to List On-Hand Inventory

SELECT item_number, item_description, organization_code, on_hand_quantity

FROM mtl_onhand_quantities

WHERE on_hand_quantity > 0;

16 Fetch Sales Order Details

SELECT order_number, customer_name, order_status, total_amount, creation_date

FROM oe_order_headers_all

WHERE order_status IN ('Entered', 'Awaiting Shipping');

 Oracle Fusion Technical Queries

17 Find ESS Scheduled Processes

SELECT process_id, process_name, status, submitted_date, completion_date

FROM fnd_concurrent_requests

WHERE status IN ('Running', 'Error', 'Completed');

18 Query for BI Publisher Reports Details

SELECT report_name, report_path, last_run_date

FROM bip_reports

WHERE last_run_date >= TRUNC(SYSDATE) - 7;

19 Fetch UCM (Content Management) Upload History

  SELECT document_id, document_name, uploaded_by, uploaded_date

FROM ucm_documents

WHERE uploaded_date >= TRUNC(SYSDATE) - 30;

 

Other Useful Queries

20 Find List of Legal Entities in Fusion

SELECT legal_entity_id, name, registration_number, status

FROM xle_entity_profiles

WHERE status = 'Active';

21 Retrieve Open Requisitions (Procurement)

SELECT requisition_number, requester, amount, approval_status

FROM por_requisitions_all

WHERE approval_status = 'Pending Approval';

22 Identify Stuck Transactions in Interface Tables

SELECT interface_id, error_message, creation_date

FROM gl_interface_errors

WHERE error_message IS NOT NULL;