Wednesday, 12 February 2025

Oracle Fusion SQL Queries and API Payload Samples

Here are some of the SQL queries for extracting data from Oracle Fusions and API payload samples for integrations.

Note : These are for reference only. please refer to the latest updates/upgrades to these API's

SQL Queries for Common Fusion Data Extraction

1. Fetching Supplier Details (Fusion Financials - ERP)

SELECT supplier_name, supplier_number, taxpayer_id, status
FROM POZ_SUPPLIERS_VL WHERE status = 'ACTIVE' ORDER BY supplier_name;

2. Fetching Employee Details (Fusion HCM)

SELECT person_id, full_name, person_number, email_address, hire_date, assignment_status
FROM PER_PEOPLE_F_VL WHERE TRUNC(hire_date) >= TRUNC(SYSDATE) - 30 -- Last 30 days hires ORDER BY hire_date DESC;

3. Fetching Invoice Details (Fusion ERP - Payables)

SELECT invoice_id, invoice_number, supplier_name, invoice_amount, invoice_status
FROM AP_INVOICES_V WHERE invoice_status = 'Validated' ORDER BY invoice_date DESC;

4. Fetching Purchase Orders (Fusion SCM)

SELECT po_header_id, po_number, supplier_name, total_amount, po_status
FROM PO_HEADERS_VL WHERE po_status = 'APPROVED' ORDER BY creation_date DESC;

5. Fetching Journal Entries (Fusion Financials - GL)

SELECT je_header_id, je_batch_id, je_category, ledger_name, period_name, accounted_dr, accounted_cr
FROM GL_JE_HEADERS WHERE period_name = 'JAN-2025';

Oracle Fusion API Payload Samples

Fusion APIs are REST-based and use JSON for communication. Below are examples of how to interact with them.

1. Fetching Supplier Data via REST API

API Endpoint:

http
GET /fscmRestApi/resources/11.13.18.05/suppliers?q=SupplierName='ABC Corp'

Response (JSON):

json
{ "items": [ { "SupplierId": 100000, "SupplierName": "Corp", "SupplierNumber": "SUP12345", "TaxpayerId": "TX987654", "Status": "ACTIVE" } ] }

2. Fetching Employee Data via HCM REST API

API Endpoint:

http
GET /hcmRestApi/resources/11.13.18.05/workers?q=PersonNumber='EMP00123'

Response (JSON):

{
"items": [ { "PersonId": 10000, "FullName": "Doe", "PersonNumber": "EMP00123", "Email": "doe@example.com", "HireDate": "2024-01-15", "AssignmentStatus": "ACTIVE" } ] }

3. Creating a New Supplier Using REST API

API Endpoint:

http
POST /fscmRestApi/resources/11.13.18.05/suppliers

Request Body (JSON):

json
{
"SupplierName": "Tech Solutions", "SupplierNumber": "SUP123", "TaxpayerId": "TX55555", "Status": "ACTIVE" }

Response (JSON):

json
{ "SupplierId": 1111, "SupplierName": "Tech Solutions", "SupplierNumber": "SUP1111", "Status": "ACTIVE" }

4. Creating an Invoice via REST API

API Endpoint:

http
POST /fscmRestApi/resources/11.13.18.05/invoices

Request Body (JSON):

json
{ "InvoiceNumber": "INV1111", "SupplierNumber": "SUP12345", "InvoiceAmount": 1200, "Currency": "USD", "InvoiceDate": "2024-02-10" }

Response (JSON):

json
{ "InvoiceId": 45678, "InvoiceNumber": "INV1111", "InvoiceAmount": 1200, "Status": "Validated" }

5.Updating Employee Email via HCM REST API

API Endpoint:

http
PATCH /hcmRestApi/resources/11.13.18.05/workers/10234

Request Body (JSON):

json
{ "Email": "doe@newcompany.com" }

Response (JSON):

json
{ "PersonId": 111, "FullName": "Doe", "Email": "doe@newcompany.com" }

Oracle Fusion : Tables, Views & Integration Strategies

Oracle Fusion Applications store data in underlying tables, but direct table access is not typically provided to end-users. Instead, Oracle offers public views and APIs for data retrieval and integration.

1. Base Tables vs. Interface Tables

Base tables store transactional and master data (e.g., HCM_EMPLOYEES, PO_HEADERS_ALL).

Interface tables (staging tables) facilitate data imports (e.g., AP_INVOICES_INTERFACE for invoices).

OTBI and BIP Reports for extracting Fusion data without direct table access.

2. Key Tables & Views for Common Modules

Here are some critical tables and views in Oracle Fusion for different modules:

Fusion Financials (ERP)
  • AP_INVOICES_ALL – Stores invoice details.
  • AP_SUPPLIERS – Holds supplier information.
  • GL_JE_HEADERS & GL_JE_LINES – Stores journal entries.
  • Fusion View: AP_INVOICE_V (for secured access).
Fusion HCM (Human Capital Management)
  • PER_ALL_PEOPLE_F – Employee records (date-tracked).
  • PER_JOBS_F – Job details.
  • Fusion View: PER_PEOPLE_F_VL (secured employee data view).
Fusion SCM (Supply Chain Management)
  • PO_HEADERS_ALL – Purchase order details.
  • INV_MATERIAL_TXNS – Inventory transactions.
  • Fusion View: PO_HEADERS_VL.

3. Extracting Data from Oracle Fusion

Here are some integration approaches:

A. Using BI Publisher (BIP) for Table Extraction
  1. Create a Data Model in BIP.
  2. Use SQL queries to fetch data from Fusion Views (e.g., SELECT * FROM PER_PEOPLE_F_VL).
  3. Schedule reports for automated data extraction in CSV/Excel format.
B. File-Based Data Import (FBDI) for Bulk Data Loads
  • Oracle provides FBDI templates for mass data uploads.
  • Data is staged in interface tables before processing into base tables.
  • Example: Importing invoices using AP_INVOICES_INTERFACE.
C. Oracle REST & SOAP APIs for Real-time Integration
  • Use REST APIs for fetching Fusion data:
    GET /fscmRestApi/resources/11.13.18.05/suppliers
  • Example: Fetching employee details via HCM REST API:
    GET /hcmRestApi/resources/11.13.18.05/workers?q=PersonNumber='44444'
D. Oracle Integration Cloud (OIC) for Automated Sync
  • OIC connects Oracle Fusion with external apps.
  • Prebuilt adapters for ERP, HCM, and SCM.
  • Uses orchestration workflows for automation.


Oracle Fusion Technical Integration: A Guide for Developers

Oracle Fusion Technical Integration: A Guide for Developers

This blog post will provide you basic details of the Oracle Fusion Integrations related details. 

Oracle Fusion Applications provide a robust, scalable platform for enterprise solutions, but seamless integration is key to maximizing their potential. 

Here we will explore the technical aspects of integrating Oracle Fusion with third-party applications, on-premise systems, and cloud services.

  1. Understanding Oracle Fusion Integration Architecture

    • Overview of Oracle Fusion Middleware
    • Integration tools: Oracle Integration Cloud (OIC), SOA Suite, REST & SOAP Web Services
  2. Key Integration Methods in Oracle Fusion

    • Web Services (REST and SOAP APIs)
    • File-Based Data Import (FBDI)
    • Application Composer & Groovy Scripts
    • Business Intelligence Publisher (BIP) for custom reporting
  3. Using Oracle Integration Cloud (OIC) for Seamless Connectivity

    • Prebuilt adapters for ERP, HCM, and SCM
    • Developing custom integrations with OIC
    • Managing security and authentication in OIC
  4. Best Practices for Oracle Fusion Integration

    • Handling data transformation and mapping
    • Error handling and logging strategies
    • Performance optimization techniques
  5. Real-World Use Cases & Case Studies

    • Automating HR data synchronization with third-party payroll systems
    • Integrating Oracle Fusion ERP with legacy systems
    • Connecting Fusion SCM with external logistics provider


Thursday, 6 February 2025

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;