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

In Part 1 of this post I covered how to setup your web source module to allow you to POST changes to Oracle Planning, now lets build the bridge between that module and our application.

Lets Begin:

First we head to the "SQL Workshop" and to the "Object Browser", then from here we want to create a new "Package" as shown below. This package will be the way in which we tell our Apex table how to interact with our web source module.

Name the Package something unique and memorable, then insert the below information into the "Specification" and "Body" of your new package, substituting "SUB_VAR_MANAGEMENT" with the name you have given to the package.

Or as text:

Specification:

create or replace package SUB_VAR_MANAGEMENT as

procedure add_variable(
  p_name varchar2,
  p_value varchar2,
  p_plantype varchar2
);

end SUB_VAR_MANAGEMENT;

Body:

create or replace package body SUB_VAR_MANAGEMENT is

procedure add_variable(
  p_name varchar2,
  p_value varchar2,
  p_plantype varchar2
) is

  l_parameters apex_exec.t_parameters;
begin
  apex_exec.add_parameter(p_parameters => l_parameters, p_name => 'NAME',     p_value => p_name);
  apex_exec.add_parameter(p_parameters => l_parameters, p_name => 'VALUE', p_value => p_value);
  apex_exec.add_parameter(p_parameters => l_parameters, p_name => 'PLANTYPE',  p_value => p_plantype);

  begin
    apex_exec.execute_web_source(
        p_module_static_id => 'Planning_Substitution_Variables',
        p_operation        => 'POST',
        p_parameters       => l_parameters
    );
  exception when VALUE_ERROR then
    null;
  end;
end add_variable;

end SUB_VAR_MANAGEMENT;

(Denis Savenko's blog post on making a Read-Write APEX application fully based on alien data was invaluable in helping me to get this to work so I highly recommend you pay their blog a visit as an extra point of reference if you're having trouble.)

This is the artifact that our application will reference that allows it to communicate directly with our web source module.

Once this is complete, and the package is saved and compiled without error, we can move on to adjusting the report we made earlier in our application, the first step to this is creating a new blank modal dialogue page, creating a "Static Content" region, containing 3 "Text Fields": Name, Value and PlanType, as well as a "Save Variable" button that simply submits the page.

You likely want to make both the "Name" and "PlanType" fields as read only to prevent accidentally adding the variable to another cube or ruining existing scripts that reference the variable names.

From here we head to the "Processing" tab and create a process with the type "PL/SQL Code", we then want to insert the below code into our "PL/SQL Code" source, with the second value matching the name of the text field on your page, as well as adding a server side condition of when the button you created is pressed.

SUB_VAR_MANAGEMENT.add_variable(
  p_name => :P201_NAME,
  p_value => :P201_VALUE,
  p_plantype => :P201_PLANTYPE
);

This "PL/SQL Code" is simply pulling the p_name etc variables from the "SUB_VAR_MANAGEMENT" package we created earlier and assigning the values from the applicable text fields to them.

I also added another process here to "Close Dialog" on the same button press.

From here all that's left to do is link this custom form we made to the report we made in my original article. We do this simply by heading back to our Interactive Report's attributes, selecting "Link to Custom Target", selecting the page we just created as the target and assigning the corresponding values between the pages as shown below.

And there you go! You should now be able to edit your substitution variables in Oracle Planning from Apex to your hearts content. The main application for this that comes to mind could be when you or a client needs to manage multiple environments at once, and you need to keep all the substitution variables up to date and in sync with one another.

In my "Extras" Part of this post I'll cover a few ease of life changes you can make, such as having a button that adds entirely new substitution variables and making sure your tables refresh when you submit a POST command so you don't need to keep refreshing the page after you make a change.

I hope this information proves useful to you and let me know if you can think of any further integration we could build between these two systems!

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

Thank you for reading.

-Richard