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"
}