Oracle Documentation

Calling an external REST API using Groovy

Before you begin

This 15-minute tutorial shows you how to call an external Rest API from a Groovy script in Oracle EPM Cloud Planning. You'll also learn how to create a right-click action menu with a menu item to call the script, and how to associate the action menu with a data form.


The Groovy EPM object model provides a way to call internal (cross-POD or other Oracle Cloud Services) and external REST APIs. In this example, you create and execute a Groovy rule to call an external Google Places REST API to add or update employee address information in the ManageEmployees form.

What do you need?

An EPM Enterprise Cloud Service instance allows you to deploy and use one of the supported business processes. To deploy another business process, you must request another EPM Enterprise Cloud Service instance or remove the current business process.

  • Have Service Administrator access to EPM Enterprise Cloud Service. The instance should not have a business process created.
  • If you haven't already, register for a free Oracle Cloud Customer Connect account so you can access the required files. Upload and import this Planning snapshot into your environment. If you've previously uploaded the snapshot for another Groovy tutorial, you can continue using the same snapshot.
  • Before you can start using the Google Maps Platform APIs which includes the Places REST API used in this tutorial, you must sign up and create a billing account to acquire an API key. To learn more, see Get Started with Google Maps Platform.

For more information on uploading and importing snapshots, refer to the Administering Migration for Oracle Enterprise Performance Management Cloud documentation.

The scripts you need for this tutorial are linked as text files within each section.

Loading Planning calculation variables

In this section, you upload calculation variables from an XML file for use in the Groovy script.

  1. Right-click the link for HP4_Plan2_Variables.xml, and save the file to your local drive.
  2. From the Planning Home page, navigate to Rules (under Create and Manage) to open Calculation Manager. In the System View, expand EPM Cloud > HP4. Right-click Plan2 and select Import.
  3. Under File Import Details, browse to select HP4_Plan2_Variables.xml from your local drive.
  4. Under Location Details, make the following selections:
    • Application Type: EPM Cloud
    • Application: HP4
    • Cube: Plan2
  5. Under Import Options, select Override Existing Objects.
    Import Variables
  6. Click Import. Review the import results, and then click OK.
    Import Results
  7. Click Cancel to close the Import dialog box.

Creating a named connection in Planning

In this section, you edit an existing connection to include the Places REST API key.

  1. Navigate to Connections (under Tools), and select the Places connection.
  2. Edit the connection by entering the API key in the key parameter. Save and close the connection.
    API Key Parameter

Creating a Groovy script

In this section, you implement a Groovy script to update an employee address. We'll work with the predefined ManageEmployees form.

Manage Employees
  1. In Calculation Manager, create a rule named Groovy Add or Update Employee Address in the Plan2 cube.
    New Rule
  2. In the Rule Editor, change the Designer option to Edit Script and set the Script Type to Groovy Script.
    Rule Editor Options
  3. Copy this script and paste it into the editor:
    Rule Editor Options
    /*RTPS: {Employee} {EmployeeAddress} {Scenario} {Year} {Period} {Entity} {Version}*/ 
    Member employee = rtps.Employee.member  
    // Get the complete address using the Google maps REST API. Use the original if Google maps can't find the address
    def address = rtps.EmployeeAddress  
    HttpResponse<String> jsonResponse = operation.application.getConnection("Places")
    .queryParam("input", rtps.EmployeeAddress.enteredValue)
    ReadContext ctx = JsonPath.parse(jsonResponse.body) 
    if('$.status') == "OK") 
    	address = getTextCellId('$.candidates[0].formatted_address') as String, true) 
    // Generate the calc script to save the employee address
    FIX(${fixValues(rtps.Scenario, rtps.Year, rtps.Period, rtps.Entity, rtps.Version, employee)}, "USD")
        "Employee Address" = $address;

    Define all the run time prompts (RTPs) used by this rule on the first line.

    Get the Member object for employee specified by the Employee RTP.

    Assign the text entered in the EmployeeAddress RTP as the default value for address.

    Get the Places connection to execute the HTTP GET request on this resource and pass the text entered in the EmployeeAddress RTP as the value for the input query parameter.

    The Places connection object is a communication link between the Groovy script and the Google Places REST API resource.

    Log the Google Maps REST API response for debugging puprose.

    Parse the JSON response received from the API using the JsonPath library. If the status == OK, read the formatted_address field from the first candidate's object in the returned array, get the text cell id for the formatted_address and assign it to address.

    Generate the calculation script to save this employee address.

  4. On the toolbar, click Save (Save) to save the script.
  5. Saving the script displays the variables in the Variables tab.

  6. You want users to enter values only for EmployeeAddress. In the Variables tab, select Is Hidden for Employee, Entity, Period, Scenario, Version, and Year.
    Hidden Variables
  7. When users run the script from the form, these hidden variables are assigned values from the form context.

  8. Save the script again.
  9. Click Validate and Deploy (Validate and Deploy). Enter RTP values for validation purposes:
    • Employee: "Full Time Employees"
    • Entity: "No Entity"
    • Period: Jan
    • Scenario: Current
    • Version: BU_Version_1
    • Year: FY16
    • Rule Editor Options
  10. Check Apply values to the rule to save these values as defaults for validation purposes, then click OK.
  11. During this step, the rule is not executed; however, you must enter valid members for the validation process to succeed.

  12. Click OK when prompted, then close Calculation Manager.

Creating Planning action menus to run Groovy scripts

In this section, you create an action menu called Manage Employees, with a right-click action menu item that runs the Groovy script.

  1. From the Planning Home page, navigate to Action Menus (located under Create and Manage) and create an action menu named Manage Employees.
    Create Manage Employees Action Menu
  2. Edit the Manage Employees action menu and add a child menu item named Update Employee Address. Enter or select the following options to define the menu item:
    Option Field Value to Enter or Select
    Menu Item Update Employee Address
    Label Update Employee Address
    Type Business Rule
    Required Parameters Employee
    Cube Plan2
    Business Rule Groovy Add or Update Employee Address
    Update Employee Address Menu Item
  3. Click Save, then click OK to save the menu item. Finally, click Save and then OK again to save the action menu.
    Completed Manage Employees Action Menu

Associating Planning action nenus with forms

In this step, you associate the action menu with the ManageEmployees form and test your script.

  1. Navigate to Forms (located under Create and Manage) and edit the ManageEmployees form.
    Edit Manage Employees Form
  2. On the Other Options tab, add the Manage Employees context menu to the Selected Menus list.
    Add the Manage Employees Context Menu
  3. Click Finish to save changes to the form.
  4. Close the form manager and return to the Home page.

Testing the Groovy script

In this step, you test the script. The testing procedure includes entering a valid place for the EmployeeAddress RTP to retrive the full address for the place.

  1. From the Planning Home page, click Data to display the list of data entry forms, then click ManageEmployees to open the Manage Employees form.
    List of Data Entry Forms
  2. To test the Groovy script, right-click Employee 1 and select the Update Employee Address menu item.
    Add Employee Dialog with Invalid Email and Name
  3. In the Employee Address prompt, type Oracle, San Jose and then click Launch.
  4. Invalid Employee Name Error Message
    On successful launch, you should see the full address for Oracle, San Jose saved as the full address for Employee 1.
    Invalid Employee Email Error Message
  5. Save and close the form. Navigate to Jobs (under Application) to open the Jobs Console.
    Job Console
  6. Click Groovy Add and Update Employee Address to display the job details. Click the Completed status to see the log messages printed by the Groovy script.
    Groovy Script Log Messages

Want to learn more?

Copyright © 1995, 2020, Oracle and/or its affiliates.