Skip to main content

Connecting EPM Cloud Planning to Apex with Rest API: Separating Substitution Variable access by User


Did you ever want users to only have access to certain substitution variables that apply to their area of the business, without giving them access to all the variables for the whole application? Apex could work well as a solution to this problem.

Using Apex we can restrict the tables that use our Rest API web source modules to only show substitution variables that meet a certain criteria, for instance only pull through those from a certain Plan Type.

Our Objective:

Here I will be demonstrating how to apply granular security to your Planning application's substitution variables within Apex, it will incorporate a lot from previous blog posts but I will try and point you toward these other posts when necessary.

Lets Begin:

We start off by creating a simple GET table that pulls through our substitution variables from Planning, I covered this in an older post here. Then we want to allow for POST functionality, so we can edit these variables, details of which can be found with my two part post on the topic here. You may also want to duplicate the Dynamic Actions I assigned to my POST report in my Extras blog post here.

Once this is up and running and we have the ability to see and edit our substitution variables, we will then have the basis for splitting up the forms as per user's defined access.

The first thing we want to do is limit the scope of these reports, we do this by adding a "Local Post Processing" SQL query, as shown below mine limits the search for substitution variables down to those whose Plan Type is "Ard_Rep".

I duplicated my original report so that I can see a comparison of the two, as you can see I now have my original table with all the substitution variables available, and the one below, that only shows those from the Plan Type "Ard_Rep".

Great so we have a table that only displays substitution variables from one Plan Type, but how do we restrict access to these tables by user? 

To assign user access you will first need to create a new user to assign the access to, you can do this within the "Manage Users and Groups" page.

Once the users you want are set up you can then head over to "Application Access Control".

Click "Add Role"and create and name the roles you wish to make, I've made one for Admins, the Report Team and the Planning Team.

Then in the "User Role Assignments" section of that same screen, click "Add User Role Assignment" and type an active Username like one we created earlier and assign the designated Application Roles, you can assign multiple here if required, which can be useful for providing greater access to admins or users that span multiple departments.

Then we want to head to "Authorization Schemes", where we will want to create an Authorization scheme for each of the roles we just created, as you can see here I made one for the Reporting Team and one for the Planning Team.

Click Create, and start "From Scratch", give the scheme a name and select "Is In Role or Group".

Choose type "Application Role" and provide an error message to display when the requirements are not met. Then simply assign the corresponding role to the scheme.

Now it is just a process of adding the Authorization scheme to the report we created earlier, making the report only visible to those users within the Authorization Scheme. i.e to view the below report, your role must be valid with the "Report Team Auth Scheme".

This is done in the "Security" section of the Interactive Report's settings.

The last thing to note is that whilst they can only see the variables applicable to them, they could still use the "Add New Variable" button (which I covered in my post here) to create variables for other applications. You likely don't want end users making entirely new substitution variables so I then simply removed the button.

But if you wanted to allow them to make new variables you could duplicate the form attached to that button and make the PlanType read only so they can only do so for their environment.

You also definitely want to make sure the "Name" and "PlanType" text fields are set to read only for the form that allows you to edit the variables, as this is intended for end users you don't want them renaming or moving variables between environments (this was my default approach in my POST blog here).

You can even extend this logic to restrict the variables by their name, for instance as shown below, the table will only show variables with a name that begins with "WFP", demonstrating how you can restrict access even further within the applications themselves, the only limit in customization being SQL.

It is worth noting here that while I would rather prefix my WFP variables with "WFP_" rather than just "WFP" the LIKE clause interprets an underscore as a single character. Meaning the difference between "WFP_%" and "WFP%" is only that the former needs at least one character to follow it while the latter does not, both would pick up a variable named "WFP_CurrYear".

You should now have a report that pulls through only certain substitution variables based on the user's defined access, this flexibility may allow admins to in future let more end users have access to their substitution variables without the risk of giving them further access to the system, or substitution variables that they have no business changing. This could work well in reducing system admin workloads by better distributing tasks down to the users that are directly effected by them.

Thanks for reading and let me know if you have any more ideas of where we can take these integrations.

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



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