Create Parametrized Custom ESS Jobs Running Scheduled Custom Groovy Function

Business Challenge

Oracle Fusion provides a very powerful framework for executing custom Groovy functions via scheduled ESS jobs.
However, according to official documentation, you cannot pass parameters to these scheduled functions.

Many of our clients have asked for this feature — to allow dynamic behavior based on runtime inputs. Unfortunately, parameterized execution is still only considered an enhancement request with no release date.

Our Solution

At CID Software Solutions, we devised a practical and robust workaround that allows you to pass parameters to Groovy functions scheduled via ESS jobs.

The approach relies on cloning the standard ScheduledCustomGroovyFunctionJob and using the ESS request context to pass and retrieve parameters through SQL and a BIP report.

Key Steps:

1. Create a custom ESS job definition by cloning the standard Schedule Custom Groovy Object Functions job.
   
   
2. Add parameter fields after the two required parameters — Custom Object and Custom Function — in the job definition.

   

3. Use Groovy code to retrieve the current sessionId.
4. Build a BIP report that queries FND_SESSION_ATTRIBUTES and ESS_REQUEST_PROPERTY to fetch passed parameters.
5. Execute the report from Groovy and parse the parameters for use in logic.

Sample SQL for the BIP Report:

SELECT fsa.attribute_value AS requestid,
       erp.name            AS param_name,
       erp.value           AS param_value
  FROM fnd_session_attributes fsa,
       ess_request_property   erp
 WHERE fsa.session_id = '41F49C044B284C39E063C265410A0468'
   AND fsa.attribute_name = 'JOB_REQUEST_ID'
   AND TO_NUMBER(fsa.attribute_value) = erp.requestid
   AND erp.name LIKE 'submit.argument%';

Groovy Code: Accessing Parameters from ESS Job

Define the following code as a row-level Groovy function inside your custom object:

• Return type: String
• Invokable by external system: Checked

You can copy the code from the section below:

def output = new StringBuilder()
output.append("\n")

def sessionId = adf.util.getSession().toString().split('/')[0]
output.append("SessionID=" + sessionId + "\n")

def json = adf.util.runSqlToJson("""
    select fsa.attribute_value requestid, 
           erp.name param_name, 
           erp.value param_value 
      from fnd_session_attributes fsa, 
           ess_request_property erp  
     where fsa.session_id='${sessionId}' 
       and fsa.attribute_name='JOB_REQUEST_ID' 
       and to_number(fsa.attribute_value) = erp.requestid 
       and erp.name like 'submit.argument%' 
     order by erp.name
""")

def res = (List)adf.util.parseJson(json)
output.append("Param1=" + res[2]["PARAM_VALUE"] + "\n")
output.append("Param2=" + res[3]["PARAM_VALUE"] + "\n")

return output.toString()

Conclusion

This method gives you full control over parameterized Groovy execution — solving a long-standing limitation in Fusion scheduling. It keeps your solution native, maintainable, and compatible with future Fusion updates.

Need help building advanced ESS jobs, Groovy logic, or Fusion extensions?
CID Software Solutions can help.