Pulling Oracle Fusion Data into Oracle APEX – Automated Daily Integration

In one of my recent implementations, I worked on a requirement to sync Oracle Fusion data into Oracle APEX on a daily basis.

Instead of manual jobs or external schedulers, I used APEX Automation + PL/SQL + Fusion REST APIs to make the process fully automated and reliable.

🔹 High-Level Approach

✔ Created APEX Automation to run daily
✔ Consumed Oracle Fusion REST API using APEX_WEB_SERVICE
✔ Parsed JSON response using APEX_JSON
✔ Inserted processed data into custom APEX tables
✔ Maintained audit columns for traceability

This approach ensures near real-time data availability in APEX dashboards without manual intervention.

🔹 Sample (Dummy) PL/SQL Code – Fusion REST API Consumption

DECLARE
    l_response_clob   CLOB;
    l_json_values     APEX_JSON.t_values;
    l_item_count      NUMBER;

    -- Sample variables
    v_api_url         VARCHAR2(4000);
    v_period_name     VARCHAR2(100);
    v_start_date      DATE;
    v_end_date        DATE;
BEGIN
    -- Cleanup old data
    DELETE FROM demo_gl_periods;
    COMMIT;

    -- Construct API URL
    v_api_url := 'https://<FUSION_HOST>/fscmRestApi/resources/<VERSION>/accountingPeriodsLOV';

    -- Call Fusion REST API
    l_response_clob := APEX_WEB_SERVICE.make_rest_request(
        p_url         => v_api_url,
        p_http_method => 'GET',
        p_scheme      => 'Basic',
        p_username    => '<FUSION_USERNAME>',
        p_password    => '<FUSION_PASSWORD>'
    );

    -- Parse JSON response
    APEX_JSON.parse(
        p_values => l_json_values,
        p_source => l_response_clob
    );

    l_item_count := APEX_JSON.get_count(
        p_path   => 'items',
        p_values => l_json_values
    );

    -- Loop through JSON items
    FOR i IN 1 .. l_item_count LOOP
        v_period_name := APEX_JSON.get_varchar2(
            p_path   => 'items[%d].PeriodNameId',
            p0       => i,
            p_values => l_json_values
        );

        v_start_date := APEX_JSON.get_date(
            p_path   => 'items[%d].StartDate',
            p0       => i,
            p_values => l_json_values
        );

        v_end_date := APEX_JSON.get_date(
            p_path   => 'items[%d].EndDate',
            p0       => i,
            p_values => l_json_values
        );

        INSERT INTO demo_gl_periods (
            period_name,
            start_date,
            end_date,
            created_by,
            created_date
        ) VALUES (
            v_period_name,
            v_start_date,
            v_end_date,
            :APP_USER,
            SYSDATE
        );
    END LOOP;

    COMMIT;
END;

🔹 Why This Works Well

💡 No external scheduler required
💡 Fully managed inside Oracle APEX
💡 Easy to monitor and debug
💡 Scales well for multiple Fusion APIs

This pattern can be reused for Ledgers, Suppliers, Invoices, Customers, GL Balances, and more.

🤔 Final Thought

Low-code doesn’t mean low-control.
When Oracle APEX is combined with Fusion Technical skills, it becomes a powerful enterprise integration platform.

👉 Are you already using APEX Automations for Fusion integrations, or planning to?