Wednesday, 12 February 2025

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.


Posted by at