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

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