For any custom application that does not use out of the box modules in Oracle EPM Planning, maintaining the Period Roll over in EPM is still a manual task. The date of period roll over is still very dynamic in nature and the period roll over in EPM can happen at any day in the first week of new period.

Let’s say if we are rolling over to a new period or opening a new Plan cycle or opening a new forecast cycle in Oracle EPM Planning, mostly the functional admin of EPM planning needs to update below components in the application.

• Substitution Variables
• Scenario Start and End Year Period Properties
• Valid Intersections

Closing the Loop:

Automating SubVars and Scenario Properties methods were initially available in the EPM Groovy API itself, but I was not fully able to automate the entire process. This was because I typically build Valid Intersections using the Year, Period, and Scenario members. Consequently, even though I could automate the SubVar and Scenario Start and End Periods, Valid Intersection automation was not available. I therefore used to advise customers to manually update valid intersections, as it was the only option.

However, some of you may have noticed that the EPM Groovy API now includes an Excel Workbook API that helps us create XLSX files dynamically with multiple sheets. By utilizing this feature, we can now use Server-side EPM Automate or the Rest API to import the Valid Intersections using the dynamic XLSX file. With this capability, all three components—SubVar, Scenario Properties, and Valid Intersections—can be automated. I therefore thought I would write a blog to explain how to utilize the existing methods to achieve this.

I am not going to use any external server or scripting; this is a pure Calc Manager Business Rule solution. It is as simple as below.

Updating Substitution Variables:

SubVar updates might seem a very easy thing to do. From the EPM Groovy API perspective, this is a single line of code that allows us to update the substitution variable value.

operation.application.setSubstitutionVariableValue(String subVarName, String subVarValue) 

For this example, I am using 4 subvars. Usually, there are a few SubVar values that a user chooses, and there are a few SubVar values that we can use Groovy to figure out. I categorize these two into two distinct categories:

1. Base SubVars: Users manually choose a value.
2. Dependent SubVars: We can set relationship rules to figure out the SubVar values.

Let’s say I have two variables: one is Actual_Year_Current and the other is Actual_Year_Prior. Here, I let the user choose what the current Actual Year is, but I can easily figure out the prior Actual Year using the current year.

Here is the simple flow diagram:

Map<String, String> subVars = [
	  'Actual_Year_Current' : rtps.'Actual_Year_Current'.member.name,
    'Actual_Period_Current' : rtps.'Actual_Period_Current'.member.name,
    'Actual_Year_Prior' : shiftYear(rtps.'Actual_Year_Current'.member.name, -1),
    'Actual_Period_Prior' : shiftPeriod(rtps.'Actual_Period_Current'.member.name, -1)
]
//Update SubVar Values
subVars.each { String subVarName, String subVarValue ->	
	setSubstitutionVariable(subVarName, subVarValue)	
}

The shiftYear and shiftPeriod custom methods are used to derive my Dependent SubVar values, such as Prior Year and Prior Period. setSubstitionVariable is a custom method that I have created. This method checks a lot of prerequisites, updates the variable values and writes the log in the job console.

Okay, here are the execution screenshots. Let’s say we are currently in Oct-FY26 and rolling over to the next period

SubVar Values before Execution:

Let’s execute the business rule:

Awesome, it executed without any errors! Now, let’s have a look at the Job Console Logs.

It successfully updated the Current Period to Nov and the Prior Period to Oct. The Year switch did not occur because my function first checks whether the SubVar value actually needs to be updated. It also provides me with the old value before making any update. With this, we have completed the SubVar Update section. Let’s move on to the next one.

Updating Scenario Start and End Properties

Most of us use Scenario member start and end period and year property values heavily in business rules and also in the suppression columns on the forms. Updating these new values is essentially a lifeline, as failing to do so will create a great deal of chaos during the planning cycle.

We would use the saveMember method from the Dimension class and utilize the updatedProperty with a specific format to save the Actual member with the new end year and end period. Since this is the Actual member, I am not changing the start period & Year; however, if you are using the same method for Plan or Forecast, it is essential to change the Start Year and Period properties too.

dimension.saveMember(updatedProperty as Map<String, Object>)

I have built a simple function that requires the member name, end year, and end period, and then updates the property as follows:

updateScenarioProperties('Actual', rtps.'Actual_Year_Current'.member.name, rtps.'Actual_Period_Current'.member.name)

let’s have a look at the Job Console Logs:

Let’s have a look at the dimension editor:

With this, the Scenario Property Update step has now been completed. Let’s move on to the next one.

Updating Valid Intersections

As I have mentioned before, creating Valid Intersections requires the prerequisite of creating an XLSX file. Here is the step-by-step process flow I followed to update the values in the Valid Intersections.

Here is my EPM Automate commands:

List<List<String>> updateValidIntersectionTasks  = 	[
  ['login', user, password, url], 
  ['importValidIntersections', "${member.name}_Valid_Intersections.zip","ErrorFile=${member.name}_ValidIntersections.txt"],
  ['logout']
]
EpmAutomate automate = getEpmAutomate()
updateValidIntersectionTasks.each {List<String> task ->
  EpmAutomateStatus status = automate.execute(task as String[])
    if(status.status != 0) {
      throwVetoException("${logOutput(status.output)}")        
    } else {
     	println("${logOutput(status.output)}")
    }     	
}

I would be using a method to go through these commands and execute them one by one. Alternatively, REST APIs can also be used to import the Valid Intersections using the XLSX file within a Zip file.

Please note that logOutput is a custom function that I created to print logs using a specific format in the Job Console.

I will not go into detail on how I generate the XLSX file. JeremieR-Oracle has written an excellent write-up on Cloud Customer Connect regarding Automating Cell Level Security using Groovy. Since Cell Level Security and Valid Intersections use a similar xlsx file format for mass updates, please have a look at that post to understand the step-by-step process.

Let’s look into the logs & App:

Scenario Periods for a reference:

The start year is FY17, so the process attempts to create the valid combinations for all years until FY26. I have added a condition for the first and last year to exclude the invalid period members. This logic is highly customizable and can create any type of valid/invalid combinations you need. There is no prerequisite that the Valid Intersection must be present before we initiate the import task. If the Valid Intersection is not already available in the system, the EPM Automate/REST process will automatically generate a new one for us.

Addressing Semi and Full Automation

All the things that I discussed so far are semi-automation. For example, this template or rule still needs to be executed by someone to pass the values for the current year and current period via Run Time Prompts. But what if we build logic around this? Let’s say we schedule the rule on a specific date—some firms schedule them at the end of the first week or the start of the second week in each month.

Since Oracle EPM has a built-in scheduler, to automate this fully, we would have to create a new rule to read the current month and current year from the current date and replace those RTPs values with Groovy functions.

//Update SubVar Values
Map<String, String> subVars = [
	'Actual_Year_Current' : getCurrFiscalYear(),
  'Actual_Period_Current' : getCurrMonth(),
  'Actual_Year_Prior' : shiftYear(getCurrFiscalYear(), -1),
  'Actual_Period_Prior' : shiftPeriod(getCurrMonth(), -1)
]

getCurrFiscalYear() and getCurrMonth() are simple functions, custom-created to fetch the year and period from the Date Object in Groovy instead of relying on user input values. I have executed today, so I am getting my Nov period rolled back to Oct in the Job console log.

Conclusion:

Is this level of automation truly necessary? While a functional administrator could certainly navigate a few places and update these fields manually, my past experience has consistently shown that these manual steps are prone to error.

Specifically, the SubVar field lacks validation and is easily corrupted. For Scenario Period updates, it’s often not just the single main member that requires updating, but also several dependent scenario members. Furthermore, manually updating Valid Intersections is a notoriously tedious process.

The good news is that the Groovy API now offers sophisticated methods that allow us to write application-agnostic rules. This means the same script can work across different applications, often requiring only minor adjustments. I am happy as long as we can improve the user experience and significantly reduce the chance of human error during the critical period switch. For me, these benefits confirm that full automation is necessary.

The key takeaway from this post is that we can now fully automate and control Valid Intersections along with SubVar and Scenario Member Properties. I hope this post gives you a new perspective on simplifying your month-end close and more.