Sarah Ansell

Brovanture Consultant

Automating Oracle Cloud EPM Data Integrations using Automate EPM Data Load Rules without the need to install any automation software on a server. Entirely within the cloud.

Make life easier, by kicking of one or more data load rules from the click of a button, using a Business Rule, which can be added to a Task List or a Data Form in Oracle Cloud EPM Planning.

Define periods for loading via prompts at run time or utilise substitution variables.

Business Rule for running integrations – with Run Time Prompts

Below is a step-by-step guide on how to achieve this, using Connections and Calculation Manager.

Step 1: Create a ‘ Other Web Service Provider’ Connection

Go to Navigator -> Connections, and create an Other Web Service Provider connection with the following details:

Use ‘localhost:9000’ to avoid needing to change the URL in all environments
Additional information for User field:
- If your environment is on Classic Oracle Cloud Infrastructure, use a username in the format identitydomain.username.
- If your environment is on OCI (Gen 2) Oracle Cloud Infrastructure, you can use basic authentication by supplying a username in the format of identitydomain.username or only username (without identity domain).
I believe the user must be a native user, rather than a single-sign-on user. Best that this has Service Administrator privileges.

Step 2: Create a Groovy Business Rule

Below is my example groovy script.

My example was designed for pushing data from EPM to ERP and was designed so that the admin could run the business rule from a task list, inputting the dates at runtime. The original rule had sequence of data load rules running one after another.

I’m sure the code isn’t perfectly concise, feel free to advise improvements!

/* RTPS: {RTP_StartPeriod} {RTP_EndPeriod} */

//Defining variables for Start Period and End Period //

def StartPeriod = rtps.RTP_StartPeriod.toString()

def EndPeriod = rtps.RTP_EndPeriod.toString()

println “Start Period has been defined as” + StartPeriod

println “End Period has been defined as” + EndPeriod

// Following commands ensure rule waits for completion and handles any errors with responses

def awaitCompletion(HttpResponse<String> jsonResponse, String connectionName, String operation) {final int IN_PROGRESS = -1

if (!(200..299).contains(jsonResponse.status))

throwVetoException(“Error occured: $jsonResponse.statusText”)

ReadContext ctx = JsonPath.parse(jsonResponse.body)

int status = ctx.read(‘$.status’)

for(long delay = 50; status == IN_PROGRESS; delay = Math.min(1000, delay * 2)) {sleep(delay)

status = getJobStatus(connectionName, (String)ctx.read(‘$.jobId’))}

println(“$operation ${status == 0 ? “successful” : “failed”}.\n”)

return status == 0}

int getJobStatus(String connectionName, String jobId){

HttpResponse<String> pingResponse = operation.application.getConnection(connectionName).get(“/” + jobId).asString()

return JsonPath.parse(pingResponse.body).read(‘$.status’)}

//Launch Integration//

HttpResponse<String> jsonResponse1 = operation.application.getConnection(“DM”).post()

.header(“Content-Type”, “application/json”)

.body(json([“jobType”:”INTEGRATION”, “jobName”:”Consol_Budget”, “periodName”:StartPeriod+EndPeriod, “importMode”:”Replace”, “exportMode”:”Replace”,

“sourceFilters”:[“Version”:”Latest Archive”]]))

.asString();

// Run awaitCompletion for this Integration – as defined above//

boolean PushData1 = awaitCompletion(jsonResponse1, “DM”, “Push Data”)

if (PushData1 == false){throwVetoException(“Error running rule please view log in Data Exchange – Process Details. ($jsonResponse1.statusText)”)}

Please note:

In my Run Time Prompts, I set the default value to {Apr-2223} for the Start Date, and {Mar-2223} for the end date. These default dates are in the format required for pushing data to Oracle ERP. The curly brackets are a must when pushing data to ERP using this method!
I am using REST API command for jobType: Integration. The reason for this, is that it allows me to override Source Filters at runtime for my data push to Oracle ERP. At runtime, I choose to push Latest Archive, my Version dimension member. Even if a different Source Filter is defined in the Integration, for manual running, this will run for Latest Archive instead.

Step 3: Schedule the Business Rule using the Jobs area.

Within Jobs, click Schedule Jobs, and add the details in the below screen.

Please note, this is optional. I have included Run Time Prompts in my example above because I the administrators wanted to run this Business Rule from a Task List, and it was used to kick off a sequence of Data Integrations rather than just one.