Skip to main content

Connecting EPM Cloud Planning to Apex with Rest API: POST Substitution Variables Part 1 of 2

In my previous post I talked about using Oracle Apex to dynamically pull through substitution Variables from Oracle Planning.

Our Objective:

Here I will walk you through how you can edit these variables in Apex and have it push these changes straight into Planning, ideal if you need to manage multiple environments and want to keep all the variables in sync.

Lets Begin:

My Starting point for this walk-through will be the end of the last one as the same steps are required, so if you haven't done so already please visit my previous post on how to create a simple GET table of your substitution variables so we can begin to manage them from Apex too.

So having followed my previous post you should now have in your application a report like the one below that displays your application's substitution variables.

Your first task in letting you manage these variables from Apex is found back in the "Web Source Modules" page within "Shared Components". You'll want to click on the Module that we created earlier and from here head over to the "Operations" tab where you should see the "GET" operation that was made during the "Discovery" phase of the module's initial creation.

Here we want to click "Add Operation", for this new operation we want to have the HTTP Method as "POST", the Database Operation as "Insert Row" and the Request Body Template as the following:

    "items": [{
        "name": "#NAME#",
        "value": "#VALUE#",
        "planType": "#PLANTYPE#"

For future reference this body template information can be found on the Oracle Rest API Reference Guide which may help you perform other Rest calls to Oracle Planning.

The URL Pattern should auto-fill with "." but if not you can also insert this here, then click "Create".

Once this has been created, go back into the editor for the "POST" operation you just made and you will now be able to add "Operation Parameters", we want to add 4 new parameters, the required details of which are listed below.

Type: Request / Response Body
Name: NAME
Direction: In
Static: Off
Required: Yes
Type: Request / Response Body
Direction: In
Static: Off
Required: Yes
Type: Request / Response Body
Direction: In
Static: Off
Required: Yes
Type: HTTP Header
Name: Content-Type
Value: application/json
Direction: In
Static: Off
Required: No

Once submitted the last thing to check is the "Static ID" of the web source module, this can be found under the "Advanced" tab, this should default to the name of the web module, but if not, or if you wish to change it you will need to type in an appropriate unique name here which we will be referencing later.

Once completed, click "Apply Changes" and we're ready to move onto Stage 2.

That concludes part 1, we now have our Web Source Module up and running and in part 2 we will be looking at how to build a bridge between this module and the interactive report in our application so as to POST changes back to Oracle Planning.

I hope this information proves useful to you and you can find part 2 of this post here.

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

Thank you for reading.



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

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

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(p