Skip to main content

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_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 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 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.

Notice than I'm 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 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!

Thank you for reading.

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

-Richard

Comments

Popular posts from this blog

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

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