Labels

🚀 Exploring ESS Job Execution via REST API in Oracle EPM: A Performance Investigation

In our recent project, we were tasked with extracting data from Oracle Fusion using ESS Jobs within Oracle Planning and Budgeting Cloud Service (PBCS) via Data Management. However, we encountered significant performance bottlenecks during the extraction process. This led us to explore alternative methods to improve efficiency.

One such alternative was to invoke the ESS Job directly using REST APIs through a Groovy rule. While this approach didn’t yield a major performance boost—likely because Data Management internally uses the same REST API—it was still a valuable learning experience. Understanding how to manually trigger and manage ESS Jobs via Groovy can be useful in other scenarios or for custom automation.

Credit goes to my Team Mate who did a brilliant job of finding the right Oracle Fusion Rest Api's

Let’s break down the Groovy rule we used and explain each step.

🔧 Prerequisites

To run this Groovy rule, you need to set up two connections in Oracle EPM:

  • ERP Connection: https://xxxxxx.oraclecloud.com (Oracle Fusion)
  • EPM Connection: https://xxxxxx.oraclecloud.com (Local EPM instance)

🧩 Step-by-Step Breakdown of the Groovy Rule

1. Define Parameters


String BUSINESS_UNIT = "USA"
String ACCOUNTING_DATE_FROM = "2025-02-27"
String ACCOUNTING_DATE_TO = "2025-03-26"

These parameters define the scope of the data extract.

2. Submit the ESS Job


HttpResponse EssJobResponse = operation.application.getConnection("OracleERP")
  .post("/fscmRestApi/resources/latest/erpintegrations")
  .header("Content-Type", "application/json")
  .body("""
  {
    "OperationName": "submitESSJobRequest",
    "JobPackageName": "/oracle/apps/ess/custom/EPM/CustomExtract", 
    "JobDefName": "CUSTOM_EPM_EXTRACT",
    "ESSParameters": "$BUSINESS_UNIT,#NULL,#NULL,#NULL,#NULL,#NULL,$ACCOUNTING_DATE_FROM,$ACCOUNTING_DATE_TO" 	
  }
  """)
  .asString();

This submits the ESS job to Oracle ERP using the REST API.

3. Extract the Request ID


def parsedResponse = new JsonSlurper().parseText(EssJobResponse.getBody())
def reqstId = parsedResponse["ReqstId"] as String

The ReqstId is essential for tracking the job status and retrieving the output.

4. Poll for Job Status


String getJobStatus(String connectionName, String reqstId) { ... }

A helper function is defined to check the job status using the getESSJobStatus operation.


while ((status == "RUNNING" || status == "WAIT" || status == "READY") && retries < maxRetries) {
  status = getJobStatus("OracleERP", reqstId)
  sleep(2000*30*2) // Wait 2 minutes
}

The script polls the job status until it completes or reaches the retry limit.

5. Download the Job Output


HttpResponse erpResponse = operation.application.getConnection("OracleERP")
  .post("fscmRestApi/resources/11.13.18.05/erpintegrations")
  .header("Content-Type", "application/json")
  .body("""
  {
    "OperationName": "downloadESSJobExecutionDetails",
    "ReqstId": "$reqstId",
    "FileType": "Out"
  }
  """)
  .asString()

Once the job is successful, the output file is downloaded in Base64-encoded ZIP format.

6. Decode and Extract ZIP Contents


Map decodeAndExtractBase64Zip(String base64String) {
    def extractedFiles = [:] as Map

    try {
        byte[] zipBytes = Base64.decoder.decode(base64String)
        ByteArrayInputStream byteStream = new ByteArrayInputStream(zipBytes)
        ZipInputStream zipStream = new ZipInputStream(byteStream)

        ZipEntry entry
        while ((entry = zipStream.nextEntry) != null) {
            ByteArrayOutputStream output = new ByteArrayOutputStream()
            byte[] buffer = new byte[1024]
            int len
            while ((len = zipStream.read(buffer)) > 0) {
                output.write(buffer, 0, len)
            }
            extractedFiles[entry.name] = output.toByteArray()
            zipStream.closeEntry()
        }

        zipStream.close()
    } catch (Exception e) {
        println "Error during decoding or extraction: ${e.message}"
    }

    return extractedFiles
}

This function decodes the Base64 string and extracts files from the ZIP archive.

The ESS job output is base64-encoded to safely transmit binary data over HTTP. We decode it to get the raw ZIP bytes. We then extract the ZIP contents in memory using ZipInputStream—as we cannot zip it in Cloud.

7. Upload to Local EPM


HttpResponse apendFile = operation.application.getConnection("Local")
  .post("/interop/rest/11.1.2.3.600/applicationsnapshots/$name/contents")
  .header("Content-Type", "application/octet-stream")
  .body(text)
  .asString()

The extracted file is uploaded to the local EPM environment for further processing.

🧠 Key Takeaways

  • REST API invocation of ESS Jobs is a viable alternative to Data Management.
  • Performance gains may be limited if both methods use the same backend API.
  • This approach provides greater control and flexibility, especially for custom automation or troubleshooting.

📌 Final Thoughts

While this method didn’t solve our performance issues, it gave us deeper insight into how Oracle Fusion and EPM interact. It’s a handy tool to have in your Oracle Cloud toolbox and might just be the solution for someone else’s use case.

Complete code below


    /* RTPS:  */

import java.util.Base64
import groovy.json.JsonSlurper
import java.util.zip.ZipInputStream
import java.util.zip.ZipEntry
import java.io.ByteArrayInputStream
import java.io.ByteArrayOutputStream

String BUSINESS_UNIT = "USA"
String ACCOUNTING_DATE_FROM = "2025-02-27"
String ACCOUNTING_DATE_TO = "2025-03-26"

HttpResponse EssJobResponse = operation.application.getConnection("OracleERP")
    .post("/fscmRestApi/resources/latest/erpintegrations")
    .header("Content-Type", "application/json")
    .body("""
    {
        "OperationName": "submitESSJobRequest",
        "JobPackageName": "/oracle/apps/ess/custom/EPM/CustomExtract", 
        "JobDefName": "CUSTOM_EPM_EXTRACT",
        "ESSParameters": "$BUSINESS_UNIT,#NULL,#NULL,#NULL,#NULL,#NULL,$ACCOUNTING_DATE_FROM,$ACCOUNTING_DATE_TO"   	
    }
    """)
    .asString();
    
def responseBody = EssJobResponse.getBody()
def parsedResponse = new JsonSlurper().parseText(responseBody)

// Extract ReqstId
def reqstId = parsedResponse["ReqstId"] as String

println("ESS Job submitted successfully and the Request id is $reqstId")
  

if (!reqstId) {
	println "Failed to retrieve ReqstId from ESS job submission response."
    println "Response: $responseBody"
    return
}

// Function to get job status
String getJobStatus(String connectionName, String reqstId) {
	def payload = """
    {
    	"OperationName": "getESSJobStatus",
        "ReqstId": "$reqstId"
    }
    """
    
    /*println("Sending payload to getESSJobStatus:\n$payload")*/
     
    
	HttpResponse jobResponse = operation.application.getConnection(connectionName)
    	.post("/fscmRestApi/resources/11.13.18.05/erpintegrations")
        .header("Content-Type", "application/json")
        .body(payload)
        .asString()
        
	String responseText = jobResponse.getBody()
    /*println("Raw response from getESSJobStatus: $responseText")*/
    
    try {
    	def parsed = new JsonSlurper().parseText(responseText)
        return parsed["RequestStatus"] ?: "UNKNOWN"
    } 	catch (Exception e) {
    	println("Error parsing job status response: ${e.message}")
        return "UNKNOWN"
    }
}

// Enhanced polling logic with retry limit and logging
int maxRetries = 100
int retries = 0
String status = "RUNNING"

while ((status == "RUNNING" || status == "WAIT" || status == "READY") && retries < maxRetries) {
	println("Polling attempt ${retries + 1} for ESS Job ID $reqstId...")
    status = getJobStatus("OracleERP", reqstId)
    println("Current job status: $status")
    println()
    if (status == "RUNNING") {
    	sleep(2000*30*2) // Wait 2 minutes before next poll
       }
       retries++
}

if (status == "SUCCEEDED") {
	println("ESS Job $reqstId completed successfully.")
} else if (status == "FAILED") {
	println("ESS Job $reqstId failed.")
} else {
	println("ESS Job $reqstId did not complete within the expected time. Final status: $status")
}

/*// Poll for job status
String status = "RUNNING"

if(status == "RUNNING") {
	status = getJobStatus("OracleERP", reqstId)
    for(long delay = 50; status == "RUNNING"; delay = Math.min(1000, delay * 2)) {
    	println("Polling job status... current status: $status")
        sleep(delay)
        status = getJobStatus("OracleERP", $reqstId)
    }
    println("${status == 'SUCCEEDED' ? "successful" : "failed"}.\n")
}

if(status == "SUCCEEDED") {
	println("Process completed...")
}*/


HttpResponse JobResponse = operation.application.getConnection("OracleERP")
.post("/fscmRestApi/resources/11.13.18.05/erpintegrations")
.header("Content-Type", "application/json")
.body("""
{
"OperationName": "getESSJobStatus",
"ReqstId": "$reqstId"
}
""")
.asString();

Map decodeAndExtractBase64Zip(String base64String) {
    def extractedFiles = [:] as Map

    try {
        byte[] zipBytes = Base64.decoder.decode(base64String)
        ByteArrayInputStream byteStream = new ByteArrayInputStream(zipBytes)
        ZipInputStream zipStream = new ZipInputStream(byteStream)

        ZipEntry entry
        while ((entry = zipStream.nextEntry) != null) {
            ByteArrayOutputStream output = new ByteArrayOutputStream()
            byte[] buffer = new byte[1024]
            int len
            while ((len = zipStream.read(buffer)) > 0) {
                output.write(buffer, 0, len)
            }
            extractedFiles[entry.name] = output.toByteArray()
            zipStream.closeEntry()
        }

        zipStream.close()
    } catch (Exception e) {
        println "Error during decoding or extraction: ${e.message}"
    }

    return extractedFiles
}

//Call Oracle ERP to download the ESS job output file
HttpResponse erpResponse = operation.application.getConnection("OracleERP")
.post("fscmRestApi/resources/11.13.18.05/erpintegrations")
.header("Content-Type", "application/json")
.body("""
{
  "OperationName": "downloadESSJobExecutionDetails",
  "ReqstId": "$reqstId",
  "FileType": "Out"
}
""")
.asString()

//Parse the JSON response

def responseBodyERP = erpResponse.getBody()
def parsedJson = new JsonSlurper().parseText(responseBodyERP)
def base64Content = parsedJson["DocumentContent"] as String


if (!base64Content) {
    println("No document content found in the response.")
    return
}

// Check ZIP content
def zipBytes = Base64.decoder.decode(base64Content)
def zipStream = new ZipInputStream(new ByteArrayInputStream(zipBytes))
ZipEntry entry = zipStream.nextEntry
entry = zipStream.nextEntry
if (entry == null) {
	println "ZIP file is empty before upload."
} else {
	println ("Found entry: ${entry.name}")
}


Map files = decodeAndExtractBase64Zip(base64Content)

try{


files.each { name, content ->
    try {
       if(name == "$entry"){
       	byte[] contentBytes = (byte[]) content
        String text = new String(contentBytes, "UTF-8")
        println "File: $name"
        println "Content:\n$text"
        HttpResponse apendFile = operation.application.getConnection("Local")
.post("/interop/rest/11.1.2.3.600/applicationsnapshots/$name/contents")
.header("Content-Type", "application/octet-stream")
.body(text)
.asString()

        }
    } catch (Exception e) {
        println "Error processing file $name - ${e.message}"
    }
}
} catch (Exception e) {
        println "Error processing file ${e.message}"
    }
Labels:

Comments

Post a Comment