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;
