Connecting Oracle Cloud EPM Planning to Apex with Rest API

Connecting Oracle Cloud EPM Planning to Apex with Rest API: Running Jobs from Apex Part 1 of 2


I’ve already run through how to view and change your substitution variables in connecting Oracle Cloud EPM Planning to Apex, so let’s look at something a bit more versatile. Running jobs in Oracle Cloud EPM Planning from Apex. According to Oracle’s documentation you should be able to run any of these types of jobs using Rest API:

  • Rules
  • Ruleset
  • Plan Type Map
  • Import Data
  • Export Data
  • Export Metadata
  • Import Metadata
  • Cube Refresh
  • Clear Cube
  • Administration Mode
  • Compact Cube
  • Restructure Cube
  • Merge Data Slices
  • Optimize Aggregation
  • Import Security
  • Export Security
  • Export Audit
  • Export Job Console
  • Sort Members
  • Import Exchange Rates

I haven’t had a chance to test them all and see how they work or if they require any further tweaking, but the ones that have worked have done so with relative ease. This makes me fairly confident in writing this that you shouldn’t run into any issues. Please do let me know if anything does fail to work for you so I can try and find a workaround for you.

Our Objective:

For now, I will be outlining how to create a table that lists all the available jobs you can run, which also provides you with the ability of connecting Oracle Cloud EPM Planning to Apex and to then run those jobs straight from Apex.

Let’s Begin:

We start off much in the same way as in previous blogs, in the “Shared Components” area of the application you want to run the jobs from.

Oracle Cloud EPM Planning 1


Then to “Web Source Modules” within the “Data Sources” tab.

Oracle Cloud EPM Planning 2


From here we click “Create”, then select the “From Scratch” option.

Oracle Cloud EPM Planning 3


You will need to repeat these steps even if you have made the other Web Source Modules from my other blog posts, as this one uses a different service URL Path.

Once here we select “Simple HTTP”, name the module anything of your choice; I’ve named mine “Planning_Job_Definitions”, and insert the URL Endpoint, this is the REST API URL for the environment you wish to access. You can copy mine shown below and simply insert the applicable ID Domain and data centre for you, but all we’re essentially doing here is appending the normal Planning URL with “HyperionPlanning/rest/” (Remove reference to “test” if required).

Oracle Cloud EPM Planning 4


Then in the next screen you need to insert your “Service URL Path”, this is essentially choosing how you wish to interact with Oracle Cloud EPM Planning, which application you’re specifically targeting, and the latest version of the API, which is currently V3.

All you need to do here is take the same Service URL Path as mine but replace APPNAME with the name of your application.

Oracle Cloud EPM Planning 5

Then you need to add in your user credentials, this will save your credentials. So, if you already have a link with Oracle Cloud EPM Planning setup you can select those credentials from the drop down list (this also makes maintenance easier if the credentials change). Otherwise select “Enter new credentials” and authentication type “Basic Authentication”.

Oracle Cloud EPM Planning 6


Your username will be a combination of your ID Domain and Username as shown above (IDDomain.Username), and your password which will be unchanged.

Once you hit “Discover” you should reach the “Web Source Discovery” panel which will preview the available jobs from your chosen application.

Oracle Cloud EPM Planning 7


The issue here is that the service URL needed to run a job is different to the service URL needed to list the jobs you can run.

So you will now need to repeat the process for another “Web Source Module”, this time using an alternative “Service URL Path” as shown below, all else staying the same, I named this new module “Planning_Run_Job”.

Oracle Cloud EPM Planning 8


If you click “Discover” you will hit the following error screen, this is due to the fact the module automatically creates a GET request for the module you create when you try and discover, and this Service URL doesn’t have GET functionality, only POST.

Oracle Cloud EPM Planning 9


To get around this you will need to click “Create Module Manually”, this will create the module without trying to perform a GET request.

Oracle Cloud EPM Planning 10


Then unlike the last web source we will need to edit the operation attached to this one by clicking on it in the web source module list and heading to the “Operations” tab.

Here we will need to change the existing GET operation to a POST in the “HTTP Method” area, as well as fill in the below extra details. As mentioned in earlier blogs the body template information can be found on the Oracle Rest API Reference Guide which may help you perform other Rest calls to Oracle Cloud EPM Planning going forward.

Oracle Cloud EPM Planning 11


Once complete, click “Apply Changes”, and we’re ready to move on to part 2. This concludes part 1 as we have now created the Web Source module we will reference in our application. In part 2 I will explain how to use these two modules to list and then run jobs in Oracle Cloud EPM Planning from Apex.

Thank you for reading.


You can read many other useful Oracle Cloud EPM and Netsuite ERP blogs posted by my colleagues at Brovanture here