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;

Oracle SQL Query to Analyze Journal Entries by Period

 Here is the sample query to retrieve the Journal entries/balance for a particular period. Please note this is a sample query. Modify accordingly as per your requirement.

SELECT 

  gl.PERIOD_NAME,

  gl.JOURNAL_CATEGORY_NAME,

  COUNT(*) as ENTRY_COUNT,

  SUM(gl.ENTERED_DR) as TOTAL_DEBIT,

  SUM(gl.ENTERED_CR) as TOTAL_CREDIT

FROM GL_JE_HEADERS gh,

     GL_JE_LINES gl 

WHERE gh.JE_HEADER_ID = gl.JE_HEADER_ID

AND gl.PERIOD_NAME = :P_PERIOD_NAME

GROUP BY gl.PERIOD_NAME, gl.JOURNAL_CATEGORY_NAME

ORDER BY gl.PERIOD_NAME;

Oracle SQL Query to get the GL Account Balances by Segments.

 Here is the sample SQL Query to get the GL Account Balances by Segments.

Please note this is the sample query, modify accordingly as per your requirements.

SELECT 

  gcc.SEGMENT1 || '.' || gcc.SEGMENT2 as ACCOUNT,

  gb.PERIOD_NAME,

  gb.BEGIN_BALANCE_DR - gb.BEGIN_BALANCE_CR as OPENING_BALANCE,

  gb.PERIOD_NET_DR - gb.PERIOD_NET_CR as PERIOD_MOVEMENT,

  gb.END_BALANCE_DR - gb.END_BALANCE_CR as CLOSING_BALANCE

FROM GL_BALANCES gb

     GL_CODE_COMBINATIONS gcc 

WHERE gb.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID

AND   gb.PERIOD_NAME = :P_PERIOD_NAME;

Oracle SQL Query to Customer Invoices

 Here is the sample SQL Query to analyze the Customer Invoices. Please note this is sample, please modify accordingly as per your requirement :

SELECT 
  hca.ACCOUNT_NUMBER,
  hca.ACCOUNT_NAME,
  rcta.TRX_NUMBER,
  rcta.TRX_DATE,
  rcta.INVOICE_CURRENCY_CODE,
  rctla.EXTENDED_AMOUNT
FROM RA_CUSTOMER_TRX_ALL rcta,
     RA_CUSTOMER_TRX_LINES_ALL rctla,
     HZ_CUST_ACCOUNTS hca
WHERE rcta.CUSTOMER_TRX_ID = rctla.CUSTOMER_TRX_ID
AND   rcta.BILL_TO_CUSTOMER_ID = hca.CUST_ACCOUNT_ID
WHERE rcta.TRX_DATE BETWEEN :P_START_DATE AND :P_END_DATE;

Oracle tables related to the Accounts Receivables(AR)

Here are some of the common Oracle tables related to the Accounts Receivables in Oracle Fusion 

Oracle Fusion Accounts Receivables (AR) stores key transactional and configuration data across various tables. 

Below are the important tables in Oracle Fusion AR categorized by their function:

1. Customer and Party Tables

• HZ_PARTIES – Stores customer and other party information.
• HZ_CUST_ACCOUNTS – Holds customer account details linked to HZ_PARTIES.
• HZ_CUST_SITE_USES_ALL – Stores customer site usages (e.g., Bill-To, Ship-To).
• HZ_LOCATIONS – Stores address/location details.
• HZ_CONTACT_POINTS – Contains customer contact information.

2. Transactions (Invoices, Credit Memos, Debit Memos)

• RA_CUSTOMER_TRX_ALL – Stores AR transaction headers (invoices, credit memos, debit memos).
• RA_CUSTOMER_TRX_LINES_ALL – Holds transaction line details, linked to RA_CUSTOMER_TRX_ALL.
• RA_CUST_TRX_LINE_GL_DIST_ALL – Stores GL distribution details for transactions.
• RA_CUST_TRX_TYPES_ALL – Stores transaction types (Standard Invoice, Credit Memo, etc.).

3. Receipts and Payments

• AR_CASH_RECEIPTS_ALL – Holds receipt headers (payments made by customers).
• AR_CASH_RECEIPT_HISTORY_ALL – Stores receipt status history.
• AR_RECEIVABLE_APPLICATIONS_ALL – Stores details of receipt applications (linking receipts to invoices).
• AR_PAYMENT_SCHEDULES_ALL – Holds invoice payment schedules.

4. Adjustments, Refunds, and Chargebacks

• AR_ADJUSTMENTS_ALL – Stores adjustment details applied to invoices.
• AR_REFUNDS_ALL – Contains refund details linked to receipts.
• AR_CASH_RECEIPT_HISTORY_ALL – Maintains historical data for cash receipts.
• AR_CHARGEBACKS_ALL – Stores chargeback-related information.

5. Accounting Tables

• XLA_AE_HEADERS – Stores accounting entry headers.
• XLA_AE_LINES – Holds accounting line details.
• XLA_DISTRIBUTION_LINKS – Links subledger data to accounting transactions.
• GL_IMPORT_REFERENCES – Links AR transactions to General Ledger (GL).

6. Aging and Collections

• AR_AGING_BUCKETS – Defines aging bucket configurations.
• AR_AGED_TRIAL_BALANCE – Holds aged balances for customer transactions.

Oracle Key Tables in Oracle Fusion Payables

This post provides you some of the sample queries for the payables in Oracle fusion.

Here’s an example of how you might query invoice data:

Key Tables in Oracle Fusion Payables

1. Invoice Tables
AP_INVOICES_ALL: Stores invoice header information. Key columns include INVOICE_ID, INVOICE_NUM, VENDOR_ID, INVOICE_DATE, and INVOICE_AMOUNT.
AP_INVOICE_LINES_ALL: Stores invoice line-level details. Links to AP_INVOICES_ALL via INVOICE_ID.
AP_INVOICE_PAYMENTS_ALL: Contains information about payments applied to invoices. Links to AP_INVOICES_ALL via INVOICE_ID.
AP_INVOICE_DISTRIBUTIONS_ALL: Holds the accounting distribution details for each invoice. Links to AP_INVOICES_ALL via INVOICE_ID.
2. Payment Tables
AP_PAYMENT_SCHEDULES_ALL: Contains invoice payment schedule information. Links to AP_INVOICES_ALL via INVOICE_ID.
AP_PAYMENTS_ALL: Stores payment batch or manual payment header details.
AP_PAYMENT_HISTORY_ALL: Maintains a history of payment-related activities.
AP_CHECKS_ALL: Stores check-related payment information (e.g., check number, payment date).
3. Supplier Tables
PO_VENDORS: Stores supplier (vendor) information. Key columns include VENDOR_ID, VENDOR_NAME, and VENDOR_TYPE.
PO_VENDOR_SITES_ALL: Holds supplier site-related data. Links to PO_VENDORS via VENDOR_ID.
PO_VENDOR_CONTACTS: Contains supplier contact details.

















4. Accounting Tables
GL_IMPORT_REFERENCES: Links subledger transactions (like payables invoices) to general ledger journals.
XLA_AE_HEADERS: Holds accounting entry header data.
XLA_AE_LINES: Stores accounting entry lines for subledger transactions.
XLA_DISTRIBUTION_LINKS: Links subledger distributions to journal lines.
5. Miscellaneous Tables
FND_LOOKUPS: Stores lookup codes used across modules, including payables.
FND_USER: Maintains user-related information for access and approvals.
HZ_PARTIES: Contains party information (used for suppliers, customers, etc.).
HZ_LOCATIONS: Stores location details associated with parties.

Oracle SQL Queries related to the Suppliers Data Model in Oracle Fusion

Here are some of the common SQL queries related to the Supplier Model in Oracle Fusion, involving  tables typically used for supplier data 

 Supplier profiles, 

 Supplier addresses, 

 Supplier Contacts

 Supplier Sites

 and data related to payments

1. SQL Query to List All Suppliers

SELECT vendor_id, vendor_name, vendor_number, creation_date, last_update_date
FROM po_vendors;

2. SQL Query to Find Supplier Details by Name or Number

SELECT vendor_id, vendor_name, vendor_number, vendor_type_lookup_code, enabled_flag, creation_date
FROM po_vendors
WHERE UPPER(vendor_name) LIKE '%SUPPLIER_NAME%' -- Replace 'SUPPLIER_NAME' with your search keyword
   OR vendor_number = 'SUPPLIER_NUMBER'; -- Replace 'SUPPLIER_NUMBER' with your number

3. SQL Query to Retrieve Supplier Sites

SELECT vendor_site_id, vendor_id, vendor_site_code, address_line1, city, state, postal_code, country
FROM po_vendor_sites_all
WHERE vendor_id = :VENDOR_ID; -- Replace ':VENDOR_ID' with the supplier ID

4. SQL Query to List Supplier Contacts

SELECT vendor_contact_id, vendor_id, first_name, last_name, email_address, phone_number
















FROM po_vendor_contacts
WHERE vendor_id = :VENDOR_ID; -- Replace ':VENDOR_ID' with the supplier ID

5. SQL Query to Get Supplier Site Payment Details

SELECT vendor_site_id, payment_method_lookup_code, payment_currency_code, bank_account_num, bank_name
FROM po_vendor_sites_all
WHERE vendor_id = :VENDOR_ID; -- Replace ':VENDOR_ID' with the supplier ID

6. SQL Query to List Supplier Payment Methods

SELECT vendor_id, vendor_name, payment_method_lookup_code
FROM po_vendors
WHERE payment_method_lookup_code IS NOT NULL;

7. SQL Query to Find Active Suppliers

SELECT vendor_id, vendor_name, enabled_flag, creation_date
FROM po_vendors
WHERE enabled_flag = 'Y';

8. SQL Query to List All Bank Accounts Associated with Suppliers

SELECT ext_bank_account_id, bank_account_name, bank_account_num, currency_code, vendor_id
FROM iby_ext_bank_accounts
WHERE party_type_lookup_code = 'SUPPLIER';

9. SQL Query to Get Supplier Invoices

SELECT invoice_id, invoice_num, invoice_date, invoice_amount, payment_status_flag
FROM ap_invoices_all
WHERE vendor_id = :VENDOR_ID; -- Replace ':VENDOR_ID' with the supplier ID

10. SQL Query to Find Purchase Orders by Supplier

SELECT po_header_id, segment1 AS po_number, type_lookup_code, creation_date, total_amount
FROM po_headers_all
WHERE vendor_id = :VENDOR_ID; -- Replace ':VENDOR_ID' with the supplier ID

11. SQL Query to Find Supplier Purchase Agreements

SELECT agreement_id, vendor_id, agreement_type, status, start_date, end_date, total_amount
FROM po_agreements
WHERE vendor_id = :VENDOR_ID; -- Replace ':VENDOR_ID' with the supplier ID

12. SQL Query to List Suppliers with No Recent Activity

SELECT vendor_id, vendor_name, last_update_date
FROM po_vendors
WHERE last_update_date < ADD_MONTHS(SYSDATE, -6); -- Suppliers with no updates in the last 6 months

13. SQL Query to Retrieve Supplier Category Assignments

SELECT pac.vendor_id, pv.vendor_name, pac.category_id, msc.segment1 AS category
FROM po_assign_categories pac
JOIN po_vendors pv ON pac.vendor_id = pv.vendor_id
JOIN mtl_categories_b msc ON pac.category_id = msc.category_id;

14. SQL Query to List Suppliers by Country

SELECT pv.vendor_id, pv.vendor_name, pvsa.country
FROM po_vendors pv
JOIN po_vendor_sites_all pvsa ON pv.vendor_id = pvsa.vendor_id
WHERE pvsa.country = 'USA'; -- Replace 'USA' with your desired country

15. SQL Query to Find Suppliers with Specific Payment Terms

SELECT pv.vendor_id, pv.vendor_name, pvsa.payment_terms_id, hl.meaning AS payment_terms
FROM po_vendors pv
JOIN po_vendor_sites_all pvsa ON pv.vendor_id = pvsa.vendor_id
JOIN hr_lookups hl ON pvsa.payment_terms_id = hl.lookup_code
WHERE hl.meaning = 'Net 30'; -- Replace 'Net 30' with your payment terms

Key Oracle fusion tables in Supplier Model

• PO_VENDORS: Stores supplier header information.
• PO_VENDOR_SITES_ALL: Stores supplier site details.
• PO_VENDOR_CONTACTS: Contains supplier contact information.
• IBY_EXT_BANK_ACCOUNTS: Stores external bank account information for suppliers.
• AP_INVOICES_ALL: Holds invoice details.
• PO_HEADERS_ALL: Stores purchase order information.
• PO_AGREEMENTS: Contains blanket or contract purchase agreement details.

Common Oracle SQL Queries to fetch the database table in Oracle fusion

Here are some of the common Oracle SQL queries to retrieve metadata in Oracle databases, including table details, columns, constraints, indexes, and more. 

These queries are especially useful when working with Oracle Fusion to find the underlying table metadata information.

Note : These are the sample queries, please modify accordingly as per your requirements. 

1. SQL Query to list All Tables in a Schema

SELECT table_name
FROM all_tables
WHERE owner = 'FUSION';     -- Replace 'FUSION' with the schema name

2. SQL Query to List All Columns in a Table

SELECT column_name, data_type, data_length
FROM all_tab_columns
WHERE table_name = 'PER_ALL_PEOPLE_F'; -- Replace with your table name

3. SQL Query to Search for Tables with Specific Column Names

SELECT table_name, column_name
FROM all_tab_columns
WHERE column_name LIKE '%PERSON_ID%'; -- Replace with the column keyword

4. SQL Query to View Primary Key Information

SELECT cols.table_name, cols.column_name, cons.constraint_name
FROM all_cons_columns cols
JOIN all_constraints cons
  ON cols.constraint_name = cons.constraint_name
WHERE cons.constraint_type = 'P'
  AND cols.table_name = 'PER_ALL_PEOPLE_F'; -- Replace with your table name

5. SQL Query to View Foreign Key Information

SELECT a.table_name, a.column_name, a.constraint_name, 
c_pk.table_name AS referenced_table, c_pk.constraint_name AS referenced_constraint
FROM all_cons_columns a
JOIN all_constraints c
  ON a.constraint_name = c.constraint_name
JOIN all_constraints c_pk
  ON c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R'
  AND a.table_name = 'PER_ALL_ASSIGNMENTS_F'; -- Replace with your table name

6. SQL Query to View All Indexes on a Table

SELECT index_name, column_name, uniqueness
FROM all_ind_columns
WHERE table_name = 'PER_ALL_PEOPLE_F'; -- Replace with your table name

7. SQL Query to View Constraints on a Table

SELECT constraint_name, constraint_type, status
FROM all_constraints
WHERE table_name = 'PER_ALL_PEOPLE_F'; -- Replace with your table name

8. SQL Query to Search for a Keyword in Table or Column Names

SELECT table_name, column_name
FROM all_tab_columns
WHERE table_name LIKE '%PERSON%' OR column_name LIKE '%PERSON%';

9. SQL Query to List All Views in a Schema

SELECT view_name
FROM all_views
WHERE owner = 'FUSION'; -- Replace with the schema name

10. SQL Query to View Dependencies of a Table

SELECT name, type, referenced_name, referenced_type
FROM all_dependencies
WHERE name = 'PER_ALL_PEOPLE_F'; -- Replace with your table name

11. SQL Query to Identify Data Types and Lengths

SELECT column_name, data_type, data_length, data_precision, data_scale
FROM all_tab_columns
WHERE table_name = 'PER_ALL_PEOPLE_F'; -- Replace with your table name

12. SQL Query to List All Synonyms in a Schema

SELECT synonym_name, table_owner, table_name
FROM all_synonyms
WHERE owner = 'FUSION'; -- Replace with your schema name

13. SQL Query to Search Materialized Views

SELECT mview_name
FROM all_mviews
WHERE owner = 'FUSION'; -- Replace with the schema name

14. SQL Query to Find Columns with Date Data Type

SELECT table_name, column_name
FROM all_tab_columns
WHERE data_type = 'DATE';

Oracle Metadata Tables

• ALL_TABLES: Contains details about all tables accessible to the user.
• ALL_TAB_COLUMNS: Stores metadata about all columns.
• ALL_CONSTRAINTS: Contains information about constraints on tables.
• ALL_VIEWS: Provides details on views.
• ALL_SYNONYMS: Contains synonym information.

SQL Query to get all the tables in Oracle Fusion.

 Here is the sample query to get all the tables in Oracle fusion.

SELECT table_name

FROM all_tables

WHERE owner = 'FUSION';

SQL Query to get the Metadata details for the tables in Oracle Fusion

 Here is the sample query to get the metadata details for the Oracle tables.

Note : Please modify as per your requirements accordingly.

SELECT atc.table_name, atc.column_name, atc.*

FROM all_tab_columns atc

WHERE column_name LIKE '%EMPLOYEE%' OR column_name LIKE '%PERSON%';

Tables in Oracle HCM

 Oracle Fusion HCM (Human Capital Management) is a comprehensive suite of HR tools. 

Commonly Used HCM Tables

Here are some common examples of tables in Oracle Fusion HCM:

1. Core HR Tables

• PER_ALL_PEOPLE_F: Holds person details like name, date of birth, and hire date.
• PER_ALL_ASSIGNMENTS_F: Stores employee assignment details.
• PER_GRADES_F: Contains information about grades.
• PER_JOBS_F: Holds job information.

2. Workforce Structures

• HR_ORGANIZATION_UNITS_F: Organizational unit details.
• HR_ALL_POSITIONS_F: Information about positions.

3. Payroll Tables

• PAY_PERSONAL_PAYMENT_METHODS_F: Stores employee payment methods.
• PAY_PAYROLL_REL_ACTIONS: Information about payroll run actions.
• PAY_RUN_RESULTS: Results of payroll runs.

4. Security and User Tables

• FND_USER: Stores user account details.
• FND_USER_RESPONSIBILITY: User responsibilities.