Fusion Toolkit Documentation

Learn how to install, configure, and automate your Oracle Cloud ERP with step-by-step guides, release notes examples.

What is Fusion Toolkit?

Fusion Toolkit is a CLI-based automation tool designed to simplify and streamline interactions with Oracle Cloud ERP. It allows users to run SQL queries, automate BI Publisher reports, execute ESS jobs, and securely call Oracle Cloud APIs—all from their own infrastructure.

Built for developers, IT administrators, and ERP consultants, this toolkit eliminates manual processes, reduces errors, and enhances efficiency when working with Oracle Cloud ERP.

Key Features Capabilities

  • Run SQL Queries Directly – Execute SQL queries on Oracle Cloud without the need for BI Publisher data models and its 200-row limit. The limit is now 200 000 rows per queries.
  • Automate ESS Jobs – Schedule and monitor Oracle ESS jobs via cron, ensuring reliable execution without unnoticed failures.
  • BI Publisher Management – Upload, download, and manage reports and data models effortlessly.
  • Secure Local Execution – Runs entirely on your infrastructure, ensuring no data is stored or processed externally.
  • CLI API Support – Call Oracle Cloud’s REST SOAP APIs securely from the command line for additional automation use cases.

Fusion Toolkit is designed for flexibility, allowing businesses to integrate it into local machines, Linux servers, or Kubernetes-based cron jobs for robust Oracle Cloud automation.

Why Use Fusion Toolkit?

Remove Oracle Cloud Limitations
BI Publisher’s 200-row limit and complex data model setup make simple queries a hassle. Fusion Toolkit removes these barriers, letting you execute SQL queries with no restrictions.

Ensure Reliable ESS Job Scheduling
Oracle Cloud’s built-in ESS job scheduling is unreliable, often getting stuck without notifications. Our toolkit lets you automate ESS jobs via cron, preventing unnoticed failures.

Full Control Security
Fusion Toolkit runs entirely on your infrastructure—local machines, Linux servers, or Kubernetes—ensuring data never leaves your environment.

Built for Automation Integration
Whether you need to export financial data, automate reporting, or integrate third-party systems, this CLI-based tool empowers developers and IT teams to streamline Oracle Cloud operations.

Release Notes – Fusion Toolkit

Stay up to date with the latest improvements, new features, and bug fixes in Fusion Toolkit.

Version 1.8 (Latest Release – March 2025)

Introducing Oracle Cloud SQL Studio!

New Feature: Oracle Cloud SQL Studio – A Windows-based SQL query tool with a user-friendly interface, offering SQL Developer-like functionality for executing queries on Oracle Cloud ERP.

Key Features of Oracle Cloud SQL Studio:

  • Run SQL Queries Locally – No BI Publisher setup needed.
  • Windows Portable Application – No installation required.
  • Auto-Save Queries Results – Easily access executed SQL and export CSV results.
  • Search in Query Results – Quickly find data using the built-in search bar.
  • SQL Syntax Highlighting – Intuitive query writing with color-coded syntax.
  • Prebuilt SQL Queries – Useful Oracle Cloud ERP administrator queries included.

Version 1.7 (February 2025)

New Feature: Enhanced BI Publisher automation with improved upload/download stability.
Fixes Improvements: Minor CLI performance optimizations

Version 1.6 (December 2024)

New Feature: Added support for ESS Job retry mechanism to prevent job failures due to Oracle Cloud delays.
Fixes: Resolved an issue with SQL query execution timing out on large datasets.

Version 1.5 (October 2024)

Fixes :

  • Improved error handling in Oracle REST API calls.
  • General performance optimizations.

Version 1.4 (August 2024)

Fixes: Stability improvements for BI Publisher downloads in high-latency environments.

Version 1.3 (June 2024)

Fixes Improvements:

  • Fixed inconsistent log output in CLI execution.
  • Optimized SOAP API authentication handling for better compatibility with Oracle updates.

Version 1.2 (April 2024)

Fixes:

  • Addressed an issue where certain SQL queries failed due to encoding mismatches.
  • Improved error messages for invalid Oracle credentials.

Version 1.1 (March 2024)

Fixes Improvements:

  • Initial BI Publisher integration for report uploads and downloads.
  • Fixed an issue with ESS job execution failure handling.

Version 1.0 (March 2024 – First Release)

🚀 Initial release of Fusion Toolkit!

Install Java OpenJDK 17+

Fusion Toolkit requires Java OpenJDK 17 or later. You can download and install the recommended and open source Eclipse Temurin JDK 17 or any other openjdk for free from:

Download Java OpenJDK 17 (Temurin)

Ensure JAVA_HOME is set to point to the Java installation directory:

    
    export JAVA_HOME=/path/to/java
    export PATH=$JAVA_HOME/bin:$PATH
    java -version

Expected output, version 17 or more :

    
    openjdk version "17.0.12" 2024-07-16
    OpenJDK Runtime Environment Temurin-17.0.12+7 (build 17.0.12+7)
    OpenJDK Client VM Temurin-17.0.12+7 (build 17.0.12+7, mixed mode, emulated-client)

Set Up an Oracle Cloud ERP User

Fusion Toolkit requires an Oracle Cloud ERP user with appropriate permissions to execute queries, manage BI Publisher reports, and submit ESS jobs.

  • You can use an existing user or create a new user in Oracle Cloud’s Security Console.
  • Go to Oracle Cloud > Security Console > Users and create a user if needed.
  • Assign the necessary roles:
    • BI Administrator (Required for running SQL Queries)
    • ESS Job Submitter Role (Required for submitting and monitoring ESS Jobs)
    • Data Access Roles (Required for querying specific tables and reports)

Security Tip: We strongly recommend using an admin user with a strong password to avoid permission issues during execution.

Purchase Download Fusion Toolkit

Fusion Toolkit requires a valid yearly license to function.

  • Purchase a license on your Dashboard Page.
  • After purchase, log in to your account and download the latest archive from the Downloads section.

Prepare the Toolkit on Your Machine

Once you have the JAR file, place it in a folder where you have full read and write access.

Possible locations to store the toolkit:
– Local Machine – For Oracle Cloud SQL Studio, personal CLI use or testing.
– Linux Server – For scheduled jobs and production usage.
– Docker Container – To containerize and integrate with your CI/CD pipeline.

Configure Oracle Credentials License Key

Before using the toolkit, you need to configure your Oracle Cloud credentials, environment, and license key.

Run the following command to create the configuration file:

    
    java -jar ./fusion-cli-toolkit.jar set-config \
        --username="YOUR_ADMIN_USER_WITH_THE_RIGHT_PERMISSION" \
        --password="YOUR_ADMIN_USER_STRONG_PASSWORD" \
        --hostname="https://YOUR_ORACLE_CLOUD_POD_ENV.fa.ocs.oraclecloud.com:443" \
        --licenseKey="YOUR_FUSION_TOOLKIT_LICENCE_KEY"
    
    Configuration saved successfully.

After running this command, the toolkit will securely store your credentials for future use.

You are now ready to run SQL queries, automate BI Publisher reports, and execute ESS jobs!

Why Automate ESS Jobs?

Oracle Cloud ERP requires running numerous ESS (Enterprise Scheduler Service) jobs to keep the system functioning properly. These jobs are critical for tasks like:

  • User Role Synchronization (for SSO users)
  • Invoice Importing (for Payables automation)
  • Notification Synchronization (for workflow alerts)

However, scheduling these jobs inside Oracle Cloud’s ESS screen can be unreliable. They often get stuck without alerts, making troubleshooting difficult.

Use Fusion Toolkit to schedule ESS jobs via cron (Linux server) or Kubernetes CronJobs for better control, monitoring, and reliability.

How to Find the Metadata Name and Parameters of an ESS Job

Before using the submit-ess-job command, you must determine the exact metadata name (also called jobDefinitionName) and any required parameters for the job.

Steps:

  1. Go to Oracle Cloud > Tools > Scheduled Processes.
  2. Manually run the ESS job you want to automate, selecting all expected parameters.
  3. Once it has run, search for the job in the history list.
  4. Add the “Metadata Name” column to the table using the View menu
  5. Select the executed job, then click “Parameters” > “All Parameter Values”.
  6. Take note of:
    • The Metadata Name (e.g., APXIIMPT), which is the value to use in --jobDefinitionName
    • The parameters, which must be passed via --parameters="..." (in the same order and format)

Example:

For the ESS job “Import Payables Invoices”, the metadata name is APXIIMPT, and parameters might look like:

Where Does --jobPackageName Come From?

The value you use for --jobPackageName (e.g., /oracle/apps/ess/financials/payables/invoices/transactions/) isn’t usually visible in the UI or Oracle documentation by default.

How to find it:

  1. Go to Setup and Maintenance in Oracle Cloud.
  2. Search for the task: Manage Enterprise Scheduler Job Definitions.
  3. Depending on your module, use one of these:
    • Manage Enterprise Scheduler Job Definitions and Job Sets for Customer Relationship Management and Related Applications
    • Manage Enterprise Scheduler Job Definitions and Job Sets for Financial, Supply Chain Management, and Related Applications
    • Manage Enterprise Scheduler Job Definitions and Job Sets for Human Capital Management and Related Applications
  4. Search for your job by display name (e.g., “Import Payables Invoices”).
  5. Click on the result. You will see:
    • Job Definition Name (e.g., APXIIMPT)
    • Job Package Name (e.g., /oracle/apps/ess/financials/payables/invoices/transactions/)
    • Parameter structure (if applicable)

This is the information you need to construct the correct command for the toolkit.

Fusion Toolkit Screenshot Manage Enterprise Scheduler Job Definitions and Job Sets for Financial, Supply Chain Management, and Related Applications

How to Submit an ESS Job Using the Toolkit

To submit an ESS job, use the following command structure:

    
    java -jar ./fusion-cli-toolkit.jar submit-ess-job \
        --jobPackageName="JOB_PACKAGE_NAME" \
        --jobDefinitionName="METADATA_NAME" \
        --parameters="PARAMETERS_IF_NEEDED"

Recommended ESS Job Schedules Commands

Here are essential ESS jobs that should be automated using cron or Kubernetes CronJobs for optimal Oracle Cloud ERP performance.

Import Payables Invoices (Every Hour, Daytime Only)

If using Intelligent Document Recognition (IDR) to import invoices, schedule this job hourly during business hours.

Command:

    
  java -jar ./fusion-cli-toolkit.jar submit-ess-job \
  --jobPackageName="/oracle/apps/ess/financials/payables/invoices/transactions/" \
  --jobDefinitionName="APXIIMPT" \
  --parameters="<typ:paramList>#NULL</typ:paramList><typ:paramList>#NULL</typ:paramList><typ:paramList>N</typ:paramList><typ:paramList>#NULL</typ:paramList><typ:paramList>BLOCK_REASON</typ:paramList><typ:paramList>BLOCK_DESCRIPTION</typ:paramList><typ:paramList>1000</typ:paramList><typ:paramList>IMAGE</typ:paramList><typ:paramList>#NULL</typ:paramList><typ:paramList>N</typ:paramList><typ:paramList>N</typ:paramList><typ:paramList>300000002904863</typ:paramList><typ:paramList>#NULL</typ:paramList><typ:paramList>1</typ:paramList>"

Cron Schedule:

    
    0 8-18 * * * (Runs every hour from 8 AM to 6 PM)

Confirm Receipt (Once a week)

Ensures that self-service receiving transactions are confirmed.

Command:

    
    java -jar ./fusion-cli-toolkit.jar submit-ess-job \
        --jobPackageName="/oracle/apps/ess/scm/receiving/selfService/confirmReceipt" \
        --jobDefinitionName="ConfirmReceiptJob"

Cron Schedule : 

    
    0 9 * * 5 (Runs every Friday at 9 AM)

Import User And Role Application Security (Every Hour, If Using SSO)

Syncs SSO users and roles in Oracle Cloud.

Command:

    
    java -jar ./fusion-cli-toolkit.jar submit-ess-job \
        --jobPackageName="/oracle/apps/ess/hcm/applicationsSecurity/" \
        --jobDefinitionName="AseImportUserAndRolesJob"

Cron Schedule: 

    
    0 * * * * (Runs every hour)

Import User and Role Application Security Data (Every Hour, If Using SSO)

Another important sync job for SSO authentication.

Command:

    
    java -jar ./fusion-cli-toolkit.jar submit-ess-job \
        --jobPackageName="/oracle/apps/ess/cdm/foundation/parties" \
        --jobDefinitionName="HzPartyUserGuidSyncJob"

Cron Schedule:

    
    0 * * * * (Runs every hour)

Retrieve Latest LDAP Changes (Every Hour, If Using SSO)

Ensures Oracle Cloud receives the latest user role changes.

Command:

    
    java -jar ./fusion-cli-toolkit.jar submit-ess-job \
        --jobPackageName="/oracle/apps/ess/hcm/users/" \
        --jobDefinitionName="SyncRolesJob"

Cron Schedule:

    
    0 * * * * (Runs every hour)

Synchronize Bell Notifications (Every 5-10 Minutes)

Ensures real-time notifications for users (workflow alerts, pending approvals).

Command:

    
    java -jar ./fusion-cli-toolkit.jar submit-ess-job \
        --jobPackageName="/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader/" \
        --jobDefinitionName="SyncBellNotifications"

Cron Schedule:

    
    */10 * * * * (Runs every 10 minutes)

Automating ESS Jobs with Cron or Kubernetes

Setting Up Cron on a Linux Server

  • Open the crontab editor:
    
    crontab -e
  • Add your ESS job schedules (example for invoice import notifications):
    
    0 8-18 * * * java -jar ./fusion-cli-toolkit.jar submit-ess-job --jobPackageName="/oracle/apps/ess/financials/payables/invoices/transactions/" --jobDefinitionName="APXIIMPT" 
    
    */10 * * * * java -jar ./fusion-cli-toolkit.jar submit-ess-job --jobPackageName="/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader/" --jobDefinitionName="SyncBellNotifications"
  • Save and exit. Cron will now execute these jobs automatically.

Setting Up Kubernetes CronJobs

If you’re using Kubernetes, define a CronJob YAML file:

    
    apiVersion: batch/v1
    kind: CronJob
    metadata:
      name: import-payables-invoices
    spec:
      schedule: "0 8-18 * * *"
      jobTemplate:
        spec:
          template:
            spec:
              containers:
              - name: fusionToolkit-ess-job
                image: openjdk:17
                command: ["java", "-jar", "/fusionToolkit/fusion-cli-toolkit.jar", "submit-ess-job",
                          "--jobPackageName=/oracle/apps/ess/financials/payables/invoices/transactions/",
                          "--jobDefinitionName=APXIIMPT"]
              restartPolicy: OnFailure

Apply it using:

    
    kubectl apply -f ess-job-cron.yaml

Why Use Fusion Toolkit for ESS Job Scheduling?

  • Reliability – Unlike Oracle’s built-in scheduling, your jobs won’t randomly get stuck.
  • Better Monitoring – Logs and notifications can be integrated with monitoring tools.
  • Full Control – Run on your own servers or Kubernetes, not inside Oracle’s ESS scheduler.
  • Quick Recovery – If a job fails, you can retry it immediately without navigating Oracle’s UI.

Downloading and Uploading BI Publisher Reports

Managing BI Publisher reports across different environments can be cumbersome. The Fusion Toolkit simplifies this process by allowing users to download and upload reports, data models, or entire folders seamlessly. This is particularly useful for version control, storing reports in a Git repository, or migrating them between Oracle Cloud environments.

Download a Report or Folder

To export a BI Publisher report or an entire folder, use the bi-publisher-download command. This allows you to back up reports or transfer them between environments.

Example:

    
    java -jar fusion-cli-toolkit.jar bi-publisher-download \
        --reportPath="/Custom/YOUR_REPORT_FOLDER" \
        --outputFile="/path/to/save/YOUR_REPORT_FOLDER.zip"

This command will download the report folder /Custom/YOUR_REPORT_FOLDER and save it as a zip.

Upload a Report or Folder

To restore or migrate a report or folder, use the bi-publisher-upload command.

Example:

    
    java -jar fusion-cli-toolkit.jar bi-publisher-upload \
        --file="/path/to/YOUR_REPORT_FOLDER.zip" \
        --targetPath="/Custom/YOUR_REPORT_FOLDER"

BI Publisher reports can be executed at scheduled times using the toolkit, enabling seamless data exports. The command-line tool is particularly useful for automating report exports in CSV format to be saved, forwarded, or ingested into databases, Kafka servers, or other applications.

Run a BI Publisher Report

To run a report and save the output, use the bi-publisher-run-report command. You can schedule this in a cron job to export data at specific times.

Example:

    
    java -jar .\fusion-cli-toolkit.jar bi-publisher-run-report \
        --reportPath="Custom/SQL/SQLTestReport.xdo" \
        --format="csv" \
        --outputFile="C:\fusionToolkitErpToolkit\SQLTestReport.csv"
    
    Running BI Publisher report: Custom/SQL/SQLTestReport.xdo
    Connection status: 200 | Connection response: OK | Post data size: 1285
    Report run successfully. File saved to: C:\fusionToolkitErpToolkit\SQLTestReport.csv
    
   #Or If you ESS Job take a parameter name LAST_UPDATE_DATE for example:
    java -jar .\fusion-cli-toolkit.jar bi-publisher-run-report \
        --reportPath="Custom/SQL/SQLTestReport.xdo" \
        --format="csv" \
        --outputFile="C:\fusionToolkitErpToolkit\SQLTestReport.csv" \
        --parametersXml="<pub:item><pub:name>LAST_UPDATE_DATE</pub:name><pub:values><pub:item>2025-01-01-00-00-00</pub:item></pub:values></pub:item>";
    
    Running BI Publisher report: Custom/SQL/SQLTestReport.xdo
    Connection status: 200 | Connection response: OK | Post data size: 1637
    Report run successfully. File saved to: C:\fusionToolkitErpToolkit\SQLTestReport.csv

This command executes the SQLTestReport, generates the result in CSV format, and saves it to SQLTestReport.csv.

Example Cron Job to Run the Report Every Day at 3 AM

To schedule a report to run daily, add the following entry to your Linux crontab:

    0 3 * * * java -jar /path/to/fusion-cli-toolkit.jar bi-publisher-run-report --reportPath="/shared/Custom/SQL/SQLTestReport" --format="csv"--outputFile="/path/to/output/SQLTestReport.csv"

This ensures that the report runs every day at 3 AM, making it ideal for automating data exports.

The Problem with Running SQL Queries in Oracle Cloud ERP

Running a simple SQL query on an Oracle Cloud ERP environment is unnecessarily complicated:

  • You must create a BI Publisher Data Model, even for a quick query. –
  • Limited to 200 rows, making it useless for large result sets.
  • Requires saving the query every time you make a change—no instant execution.
  • You need to create a BI Publisher Report just to view the data.
  • Downloading results requires extra steps, and you can only open them in Excel after export.

For ERP consultants, IT admins, and developers, this is a slow, manual, and frustrating process.

Run SQL Queries Locally with Fusion Toolkit

With Fusion Toolkit, you can run SQL queries directly against your Oracle Cloud ERP without BI Publisher limitations. We automate the whole BI Publisher process inside our CLI toolkit.

  • No Data Model Required – Just write your query in a simple .sql file.
  • Full Query Execution – No 200-row restriction the maximum is now 200000 rows.
  • Instant CSV Export – Query results are saved as a CSV file locally.
  • Fast and Efficient – No need to manually download and open reports.

How to Run an SQL Query

  1. Write Your Query in a .sql File
    Create a new .sql file with your query:

Example: Query.sql

-- Looking for an invoice received in the last 30 days ?
        SELECT invoice_number, supplier_name, amount
        FROM AP_INVOICES_ALL
        WHERE invoice_date >= SYSDATE - 30;

2. Run the Query Using the CLI
Execute the query using the sql-query command:

java -jar fusion-cli-toolkit.jar sql-query \ 
        --queryFile="/path/to/Query.sql" \ 
        --outputFile="/path/to/result.csv"

This command will:

  • Run your query on Oracle Cloud ERP
  • Generate a full CSV output without limitations (If your user have the permission to access the requested data)
  • Save the results locally for further processing

How to Run an SQL Query with Oracle Cloud SQL Studio

Prerequisites

Before running queries, ensure the following one-time setup is complete:

  • Install OpenJDK 17 or later (Required to run this portable application and toolkit).
  • Set JAVA_HOME to point to your OpenJDK17+ installation.
  • Run the set-config command to configure your Oracle Cloud credentials in the Oracle Cloud SQL Studio folder.

📌 See the Getting Started section for detailed setup instructions.

Start Oracle Cloud SQL Studio

Once the prerequisites are met, you can launch Oracle Cloud SQL Studio in two ways:

  • Option 1: Double-Click
    Simply double-click the "Run_Oracle_Cloud_SQL_Studio.bat" file in the Oracle Cloud SQL Studio folder.
  • Option 2: Run from Command Line
    Open a terminal in the Oracle Cloud SQL Studio folder and run:
  java -jar fusion-cli-toolkit.jar oracle-cloud-sql-studio -Dfile.encoding="UTF-8" --module-path "./libraries/" --add-modules javafx.controls,javafx.fxml

This command will start the graphical interface, allowing you to execute queries directly on Oracle Cloud ERP.

Writing Running SQL Queries

Creating a New Query

  • Click the “New Query” button to open a blank editor tab.
  • Write your BI Publisher-compatible SQL SELECT query in the editor.

Note: Only BI Publisher-style SELECT queries are supported. DML queries (INSERT, UPDATE, DELETE) are not allowed. Maximum number of rows returned is 200 000.

Executing a Query

  • Press CTRL + ENTER or click the “Run Query” button to execute the SQL statement.
  • The results will be displayed in the result table below the editor.
  • If there is an error in the query, the error message will appear at the top of the result section or in the log panel.

Searching Within Query Results

  • Use the filter input at the top of the result table to search for specific values within the results.
  • The table will dynamically highlight and filter matching rows, making it easier to locate relevant data.

Auto-Saving Queries Results

  • Every executed SQL query is automatically saved in the “SQL” folder for future reference.
  • The full result table is saved as a CSV file, allowing you to open it in Excel or share it with colleagues.

Why Use Oracle Cloud SQL Studio?

  • Fast Query Execution – One-click SQL execution in a familiar SQL Developer-like interface.
  • No Manual Downloads – Results are automatically saved for easy extraction and sharing.
  • Smart Search Filtering – Instantly find data in large result sets with the built-in search bar.
  • Error Handling Logs – Easily debug queries with clear error messages and log tracking.

Scroll to Top