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

In Part 1 I covered how to setup your web source modules, how to perform a GET request on available jobs, and how to perform a POST request to run these jobs. Now let’s build the connector between the web source modules and our application – connecting Oracle Cloud EPM Planning.

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 as
———————-
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_parameters => l_parameters, p_name => ‘JOBNAME’, p_value => p_JobName);

begin
apex_exec.execute_web_source(
p_module_static_id => ‘Planning_Run_Job’,
p_operation        => ‘POST’,
p_parameters       => l_parameters
);
exception when VALUE_ERROR then
null;
end;
end planning_run_job;

end PLANNING_RUN_JOB;
————————————

Much like the package I made in my other post where we posted changes in substitution variables back to Oracle Cloud EPM Planning this is the artifact that our application will reference and allow 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 our application directly.

The first step is creating the report that pulls through the list of available jobs, to do this make a new interactive report, and under ‘Location’ select ‘Web Source’ and under ‘Module’ choose the GET web module you created earlier, the one I named ‘Planning_Job_Definitions’.

Then we need to create a new blank modal dialog page, creating a ‘Static Content’ region, containing two ‘Text Fields’: JobName and JobType, as well as a ‘Run Job’ button that submits the page.

From here we head to the ‘Processing’ tab and create a process with the type ‘PL/SQL Code’, we then want to insert the code below 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.

Notice that I am now referencing the package we created that uses the POST Web Source Module, while the Interactive Report we made earlier only references the GET Web Source Module.

———————-

PLANNING_RUN_JOB.planning_run_job(
p_jobtype => :P91_JOBTYPE,
p_jobname => :P91_JOBNAME
);

———————-

---

This ‘PL/SQL Code’ is simply pulling the p_name etc variables from the ‘PLANNING_RUN_JOB’ 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 modal dialog with the interactive report we made earlier. To do this we simply head 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. (I’ve hidden the ‘Links’ column and not linked this to the form as it isn’t necessary for this exercise.)

Now this should all work! If you go to this report page and go to edit the job, it will take you to your new modal dialog, allowing you to submit that page thus pushing the POST request through to Oracle Cloud EPM Planning, so you should now be able to submit Jobs straight from APEX.

You may want to change the icons for the rows, like I have above as it doesn’t really make sense for it to be a pencil as we’re not editing the job but all these peripherals can be edited to best suit your uses.

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 via our contacts page HERE!

Thank you for reading.

Richard

You can read more of Richard’s blogs on his site HERE