Introduction

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

Complete the sections in order.

Background

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.

Prerequisites

Cloud EPM Hands-on Tutorials may require you to import a snapshot into a Cloud EPM Enterprise Service instance. A snapshot cannot be imported over an existing application or business process. Before importing the snapshot, use a separate Cloud EPM Enterprise Service instance or remove the existing application or business process from the instance you plan to use. Importing the snapshot does not automatically replace or restore existing content.

Before starting this tutorial, you must:

  • Have Service Administrator access to a Cloud EPM Enterprise Service instance.
  • Upload and import this snapshot into your Planning instance. If you've previously uploaded this 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 API (New) 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.

Note:

If you run into migration errors importing the snapshot, re-run the migration excluding the HSS-Shared Services component, as well as the Security and User Preferences artifacts in the Core component. For more information on uploading and importing snapshots, refer to the Administering Migration for Oracle Enterprise Performance Management Cloud documentation.

Tip:

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. Click Navigator (Navigator), then, under Create and Manage, select Rules to open Calculation Manager.
    Navigator with Rules highlighted under Create and Manage
  3. In System View, expand EPM Cloud, then HP4. Right-click Plan2 and select Import.
    Expanded System View hierarchy with Import selected for Plan2
  4. Under File Import Details, browse to and select HP4_Plan2_Variables.xml from your local drive.
    Import dialog with the calculation variables file selected
  5. Under Location Details, select the following values:
    • Application Type: EPM Cloud
    • Application: HP4
    • Cube: Plan2
  6. Under Import Options, select Override Existing Objects.
    Import dialog with EPM Cloud, HP4, Plan2, and Override Existing Objects selected
  7. Click Import. Review the import results, then click OK.
    Calculation variable import results

    Note:

    The message for each object may say "Inserted" or "Updated."
  8. Click Cancel to close the Import dialog box.

Creating a Named Connection in Planning

In this section, you create a Maps connection for the Google Places API.

  1. Leave Calculation Manager open and return to the Planning browser window.
  2. Click Navigator (Navigator), then, under Tools, select Connections.
    Manage Connections page before creating the Maps connection
  3. Click Create.
  4. Select Other Web Service Provider.
    Other Web Service Provider highlighted on the provider selection page
  5. Enter the following values:
    • Connection Name: Maps
    • URL: https://places.googleapis.com/v1/places:searchText
  6. Click Show Advanced Options.
    Maps connection details with advanced options displayed
  7. Define the API-key header using the following values:
    • Type: Header
    • Secure: Selected
    • Name: X-Goog-Api-Key
    • Value: Your Google Maps API key

    Keep your API key private.

  8. Click Add (Add) and define another header using the following values:
    • Type: Header
    • Secure: Cleared
    • Name: X-Goog-FieldMask
    • Value: places.formattedAddress
    Maps connection with both headers configured and the API key masked
  9. Click Save and Close to save the connection.
    Manage Connections page with the saved Maps connection

Creating a Groovy Script

In this section, you create a Groovy rule to update an employee’s address using the predefined ManageEmployees form.

ManageEmployees form for maintaining employee information
  1. Return to the Calculation Manager browser window.
  2. On the System View tab, expand EPM Cloud, then HP4, then Plan2. Right-click Rules, then select New.
    Expanded System View with New selected for the Plan2 Rules folder
  3. For Name, enter Groovy Add or Update Employee Address, then click OK.
    New Rule dialog with the employee address rule name
  4. In the Rule Editor, change the Designer option to Edit Script and set the Script Type to Groovy Script.
    Rule Editor with Edit Script and Groovy Script selected
  5. Copy this script and paste it into the editor:
    Code note 1 /*RTPS: {Employee} {EmployeeAddress} {Scenario} {Year} {Period} {Entity} {Version}*/
    
    Code note 2 Member employee = rtps.Employee.member
    
    // Get the complete address using the Google Places API (New).
    // Use the original RTP value if Google Maps can't find the address.
    Code note 3 def address = rtps.EmployeeAddress
    
    Code note 4 HttpResponse<String> jsonResponse = operation.application
        .getConnection("Maps")
        .post()
        .header("Content-Type", "application/json")
        .body(json([
            "textQuery": rtps.EmployeeAddress.enteredValue
        ]))
        .asString()
    
    Code note 5 println("Google Maps HTTP status: ${jsonResponse.status}")
    println("Google Maps response:")
    println(jsonResponse.body)
    
    Code note 6 ReadContext ctx = JsonPath.parse(jsonResponse.body)
    
    List places = ctx.read('$.places') as List
    
    if (jsonResponse.status >= 200 && jsonResponse.status < 300 && places && !places.isEmpty()) {
        String formattedAddress = ctx.read('$.places[0].formattedAddress')
        println("Google Maps formatted address: ${formattedAddress}")
        address = getTextCellId(formattedAddress, true)
    } else {
        println("Google Maps did not return a place. Using original address: ${rtps.EmployeeAddress.enteredValue}")
    }
    
    // Generate the calc script to save the employee address
    Code note 7 """SET CREATENONMISSINGBLK ON;
    FIX(${fixValues(rtps.Scenario, rtps.Year, rtps.Period, rtps.Entity, rtps.Version, employee)}, "USD")
        "Employee Address" = $address;
    ENDFIX;"""

    Code note 1 Define all the runtime prompts (RTPs) used by this rule on the first line.

    Code note 2 Get the Member object for the employee specified by the Employee RTP.

    Code note 3 Use the EmployeeAddress RTP as the default address when a formatted address is not assigned.

    Code note 4 Use the Maps connection to send an HTTP POST request. Set the content type to application/json and pass the entered EmployeeAddress value in the JSON textQuery field.

    Code note 5 Log the HTTP status and response body for troubleshooting.

    Code note 6 Parse the JSON response using JsonPath. Explicitly cast the places array to List so static type checking can resolve isEmpty(). When the response status is successful and the list is not empty, read places[0].formattedAddress, convert it to a text cell ID, and assign it to address. Otherwise, retain the original RTP value.

    Code note 7 Generate the calculation script to save the employee address at the intersection specified by the RTPs.

    Note:

    The Maps connection is a communication link between the Groovy Script and the Google Places API (New) resource.
  6. On the toolbar, click Save (Save). Click OK to close the information message.

    Note:

    Saving the script displays the variables in the Variables tab.
    Information message confirming that the Groovy rule was saved
    Saved rule with runtime prompts displayed in the Variables tab
  7. In the Variables tab, select Is Hidden for Employee, Entity, Period, Scenario, Version, and Year, so users enter a value only for EmployeeAddress.

    Note:

    When users run the rule from the form, these hidden variables receive values from the form context.
    Variables tab with all runtime prompts except EmployeeAddress hidden
  8. Click Save (Save) again. Click OK to close the information message.
  9. Click Validate and Deploy (Validate and Deploy). Enter the following RTP values for validation:
    • Employee: "Full Time Employees"
    • Entity: "No Entity"
    • Period: Jan
    • Scenario: Current
    • Version: "BU Version_1"
    • Year: FY26
    Enter RTP Values dialog with the validation members and FY26
  10. Select Apply values to the rule to save these values as defaults for validation, then click OK.

    Note:

    Validation does not execute the rule, but valid members are required for validation to succeed.
  11. Click OK when prompted, then close Calculation Manager.
    Message confirming successful rule deployment

Creating Planning Action Menus to Run Groovy Scripts

In this section, you create a Manage Employees action menu with a menu item that runs the Groovy rule.

  1. In Planning, click Navigator (Navigator), then, under Create and Manage, select Action Menus.

    Note:

    If you've completed other Groovy tutorials, the Manage Employees action menu may already exist. If it does, please proceed to step 4.
  2. Click Create Menu (Create Menu) to create an action menu.
    Create Menu dialog before entering the menu name
  3. For Menu Name, enter Manage Employees, then click OK.
    Create Menu dialog with Manage Employees entered
  4. Select the Manage Employees action menu, then click Edit Menu (Edit Menu).
    Manage Employees action menu open for editing
  5. Click Add Child (Add Child). Enter or select the following values:
    Option FieldValue
    Menu ItemUpdate Employee Address
    LabelUpdate Employee Address
    TypeBusiness Rule
    Required ParametersEmployee
    CubePlan2
    Business RuleGroovy Add or Update Employee Address
    Add Child settings for the Update Employee Address business rule
  6. Click Save. At the confirmation message, click OK.
    Information message confirming that the menu item was saved
  7. Click Save to save the action menu. At the confirmation message, click OK.
    Information message confirming that the action menu was saved
    Saved Manage Employees action menu containing Update Employee Address
  8. Click Cancel to close the Action Menu editor.

Associating Planning Action Menus with Forms

In this section, you associate the Manage Employees action menu with the ManageEmployees form.

  1. Click Navigator (Navigator), then select Data.
  2. Click ManageEmployees to open the form.
    ManageEmployees form before associating the action menu
  3. Click Actions, then select Edit to edit the form.

    A new dynamic tab is displayed with the form editor.

    Actions menu with Edit highlighted
  4. Select the Other Options tab.
    Other Options tab in the form editor
  5. Add Manage Employees to the Selected Menus list, then click Save.
    Manage Employees added to Selected Menus
  6. Click OK to close the information message.
    Information message confirming that the form was saved
  7. At the bottom of the page, close the Edit ManageEmployees dynamic tab to return to the data form.

Testing the Groovy Script

In this section, you run the rule from the open ManageEmployees form to retrieve and save a full address, then review the job log.

  1. Right-click Employee 1 and select Update Employee Address.
    Employee 1 context menu with Update Employee Address highlighted
  2. In the Employee Address prompt, enter Oracle, Redwood Shores, then click Launch.
    Employee Address prompt with Oracle, Redwood Shores entered
  3. Click OK to close the information message.
    Information message confirming successful execution of the employee address rule

    On successful completion, the full address returned by Google Maps is saved as the address for Employee 1.

    ManageEmployees form with the formatted employee address saved for Employee 1
  4. Click Navigator (Navigator), then, under Application, select Jobs to open the Jobs Console.
    All Jobs page showing the completed employee address rule
  5. Click Groovy Add or Update Employee Address to display the job details.
    Job Details with Completed collapsed and runtime prompt values shown
  6. Under Run Status, expand Completed to view the log messages printed by the Groovy script.
    Expanded Completed status showing HTTP 200, Google Places response, and the formatted address

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.