Skip to main content

Connecting EPM Cloud Planning to Apex with Rest API: GET Substitution Variables

Intro:

Oracle Apex and Oracle Planning should be a match made in heaven, both are cloud based and full of API possibilities. You can even get a free trial of Apex here to see if these sort of features would prove useful for you, or your organisation.

Our Objective:

Here I will be outlining how to create a table in Oracle Apex that dynamically pulls through the substitution variables from a Planning application, In a follow-up blog post I will try and outline how to then push changes made to the substitution variables in Apex back into Planning.

Let's Begin:

We start off this process in the “Shared Components” area of the application you want to add the substitution variables table to.


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


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



Then here we select “Simple HTTP”, name the module anything of your choice; I’ve named mine “Planning_Substitution_Variables”, 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/".


Then in the next screen you need to insert your “Service URL Path”, this is essentially choosing the artifact in Planning you wish to interact with, the application that it applies to, 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, this is where the process would vary if we were to be pulling something else from Planning.


Next you need to add in your user credentials, this will save your credentials, so if you already have a link with Planning setup you can select those credentials from the drop down list, otherwise select “Enter new credentials” and authentication type “Basic Authentication”.




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

Provided you didn’t make any spelling mistakes or syntax errors, once you hit “Discover” you should reach the “Web Source Discovery” panel which will preview the substitution variables from your chosen application.


As GET is the default API action here, we don't really need to do anything else, as APEX has found the substitution variable table on its own. This is where it will get more complex however when we try to POST changes in APEX back to Planning.

Now head to a sheet within the app you made the Web Source Module for, and insert an Interactive Report, and under “Location” select “Web Source” and under “Module” choose the web module you created earlier.




Click save and run the page, and you should have a direct link between your Planning Environment and your Apex App through Rest API for your substitution variables.


Thank you for reading and let me know of any other possible APEX-Planning Integrations!

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

-Richard

Labels:

Comments

Post a Comment

Popular posts from this blog

Oracle Apex Basics: Conditional Formatting

Our Objective: Here I will be outlining how to apply conditional formatting to your grids and reports in oracle apex, for instance shading highly positive figures as green or negative figures as red. This can be especially helpful when creating end user reports. Lets Begin: First you will need the report you're applying the formatting to, to have an SQL query as it's source. Once you have the query you want, we need to add an additional "Conditional_Formatting" column, and for that column to be populated with 'Green' should a certain column's data meet a criteria. Here I have made the 'Conditional_Formatting' column populate with 'Green' when the figure in the 'Likes' column is greater than or equal to 2. This will have added an extra column to our report, one which you will probably want to hide from view as it is only used in deciding the color of the conditional formatting.  From here we need to write t...
1 comment
Read more

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

In Part 1 I covered how to setup your web source modules, one to perform a GET request on available jobs, and another to perform a POST request to run these jobs. Now lets build the connector between the web source modules and our application. Head over to SQL Workshop and create a new Package  with the following "Specification" and "Body" , substituting "PLANNING_RUN_JOB" with the name you gave your package. Or as text: Body: create or replace package PLANNING_RUN_JOB as procedure planning_run_job(   p_jobtype varchar2,   p_jobname varchar2 ); end planning_run_job; ---------------------- Specification: create or replace package body PLANNING_RUN_JOB is procedure planning_run_job(   p_JobType varchar2,   p_JobName varchar2 ) is   l_parameters apex_exec.t_parameters; begin   apex_exec.add_parameter(p_parameters => l_parameters, p_name => 'JOBTYPE', p_value => p_JobType);   apex_exec.add_parameter(...
2 comments
Read more

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

Intro: I've already run through how to view and change your substitution variables in Oracle Planning via Apex, so let's take a look at something a bit more versatile, running jobs in Oracle 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/ if they require any further tweaking, but the one's that have worked have done so with relative ease which makes me feel fairly confident in writing this that you shouldn't run into any issues, but please do let me know if anything does fail to work f...
1 comment
Read more