Skip to main content

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

In Part 2 of this post we finished up connecting our Web Source Module, Package and Interactive report, and you should now be able to submit changes straight to your substitution variables in Oracle Planning from Apex.

Here I'm just going to go through a few quality of life additions.

Lets begin:

First off, we want to have Apex refresh the Substitution variable table we've created so we don't need to refresh the page every time we make a change, this is simply achieved by creating a dynamic action that triggers on the closure of a dialog page.

To do this create a new dynamic action on the page with our Interactive Report and assign it's event as "Dialog Closed", selection type "Region" and then select the region that contains the report itself.

Then within that create a true "Refresh" action, by choosing the "Refresh" action on the right hand panel, and once again selecting "Region" and the region that contains our report.

This should mean once you've edited a variable the table will refresh automatically with any changes you have made.

Next we want another button that allows you to add whole new variables to your Oracle Planning application, although rare in a well established application, this is still worth adding just in case.

The easiest way to go about doing this is to copy your existing page that we created in part 2 of this post (our modal dialog). You will need to update the PL/SQL Code we wrote earlier however to take into account the new names for the text fields as demonstrated below.

We then want to make sure the Name and PlanType fields are not made read only for this version of the form as we will want to control these when we make a new variable.

The only other key difference between this and our last modal dialog is that here we don't want it to remember the last substitution variable we entered when we go to enter another, we want these text fields to appear blank each time we go to create a new sub var.

To do this we go to each of our text fields "Source" settings and change "Maintain Session State" to "Per Request (Memory Only)".

Once complete we just need to insert a new button back on our original Interactive Report that takes you to our new modal dialog page.

This now allows your Interactive Report to both edit existing Substitution variables and add entirely new ones to your Oracle Planning application straight from Apex.

That's it for this series of posts on Sub Vars for now, I hope this information proves useful to you, there is no way currently to delete Sub Vars via Rest API so that isn't a possible function we can add here just yet, but as soon as it's available I'll make a new post and let you know how it's done.

Thanks for reading.

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



  1. Why it's hard to get any information from casino - DrMCD
    A new 부천 출장안마 social casino might have a ton of players interested in 공주 출장샵 playing. 경상북도 출장샵 Casino Online Gaming: What Makes 전라남도 출장마사지 it Different From a 포천 출장샵 Scam? It's just the


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

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