Oracle Documentation

Calling an internal REST API using Groovy

Before you begin

This 15-minute tutorial shows you how to call a Data Management REST API to execute a data load rule that will load the latest product volumes for the user’s entity into Oracle EPM Cloud Planning.

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 use the Product Volume form. The Product Volume form captures the volume for various products in monthly periods. You create and execute a Groovy rule to call an internal Data Management REST API to load product volumes in the form.

Product Volume 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.

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.

Setting up the Planning environment

Setting user variables in Planning

First, you set the required user variables to work with the Product Volume form.

  1. From the Planning Home page, click Tools, and then select User Preferences.
    User Preferences
  2. Select the User Variables tab.
  3. Enter the following variable definitions:
    User Variable Member
    Currency USD
    Entity Sales US
    Reporting Currency USD
    Scenario OEP_Plan
    Version OEP_Working
    User Variables Selected
  4. Click Save. At the confirmation message, click OK, then return to the Planning Home page.

Uploading a data file with product volumes

Upload a product volumes data file to the Data Management inbox (which is different than the Application inbox).

  1. Right-click the link for the ProductVolumes.csv data file and save it to your local drive.
  2. From the Planning Home page, navigate to Data Exchange (under Application). Select the Product Volumes integration.
    Product Volumes
  3. Select File Options.
    Inbox
  4. Select File Browser.
    Inbox
  5. Double-click Inbox.
    Inbox
  6. Upload the ProductVolumes.csv data file from your local drive to the Inbox folder.
    Inbox
  7. Click Cancel to close the File Browser dialog. Click Cancel to close the File Import - File Options dialog. Click Cancel to close the Edit Integration: ProductVolumes dialog.

Editing the Data Management connection

Edit the Data Management connection.

  1. Navigate to Connections (under Tools), and select the DM connection. Enter an administrator-level user and password, then save and close the connection.
    Enter Connection Details

Calling an internal REST API to load product volumes

In this section, you implement a Groovy script to call an internal REST API to load product volumes.

  1. Navigate to Rules (under Create and Manage) to open Calculation Manager and create a rule named Groovy Load Product Volumes in the OEP_FS cube.
    New Object
  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:
    // Load product volumes from DM to Planning
    HttpResponse<String> jsonResponse = operation.application.getConnection("DM").post()
    .body(json(["jobType":"DATARULE", "jobName":"ProductVolumes", "startPeriod":"Jan-17", "endPeriod":"Dec-17", 
    "importMode":"REPLACE", "exportMode":"STORE_DATA", "fileName":"inbox/ProductVolumes.csv"]))
    .asString(); 
    boolean pushDataToPlanning = awaitCompletion(jsonResponse, "DM", 
    "Push Product Volumes to Planning") 
    
    if(pushDataToPlanning) {
        // Capture the periods and products whose volumes were loaded
        Set<String> editedMembers = []
        operation.grid.dataCellIterator().each { DataCell cell ->
        	editedMembers << cell.periodName << cell.getMemberName("Product", MemberNameType.ESSBASE_NAME)
        }
    }
    
    // Wait for DM job to be completed
    def awaitCompletion(HttpResponse<String> jsonResponse, 
    String connectionName, String operation) {  
        final int IN_PROGRESS = -1
        if (!(200..299).contains(jsonResponse.status))
        throwVetoException("Error occured: $jsonResponse.statusText")
        
        // Parse the JSON response to get the status of the operation. 
    Keep polling the DM server until the operation completes. ReadContext ctx = JsonPath.parse(jsonResponse.body) int status = ctx.read('$.status') for(long delay = 50; status == IN_PROGRESS; delay = Math.min(1000, delay * 2)) { sleep(delay) status = getJobStatus(connectionName, (String)ctx.read('$.jobId')) } println("$operation ${status == 0 ? "successful" : "failed"}.\n") return status == 0 } // Poll the DM server to get the job status int getJobStatus(String connectionName, String jobId) { HttpResponse<String> pingResponse = operation.application.getConnection(connectionName).get("/" + jobId).asString() return JsonPath.parse(pingResponse.body).read('$.status') }

    Get the DM connection. This connection object is a communication link between the Groovy script and the Data Management REST API resource. Execute the HTTP POST on this resource to trigger a data load rule based on the start period and end period. This rule loads the product volumes to Planning.

    Wait for the Data Management rule execution to complete.

    Parse the JSON response from the REST API to get the status of the operation. Keep polling the Data Management server until the operation completes.

    Poll the Data Management server to get the latest job status for the specified job ID by executing the HTTP GET on this resource.

  4. On the toolbar, click Save (Save) to save the script, then click Validate and Deploy (Validate and Deploy) to validate and deploy the script. Click OK when prompted. Close Calculation Manager.
    Successful Deployment

Adding the rule to a form

In this section, you add your Groovy script rule to the Product Volume form, to be run after saving.

  1. Navigate to Forms (under Create and Manage) and select the Product Volume form.
    Form Manager
  2. Edit the form. Add the Groovy Load Product Volumes rule and set it to Run After Save. Save the form and close the Form Manager window.
    Run After Save

Testing the rule

In this section, you test your Groovy rule by editing data in the Product Volume form.

  1. From the Planning Home page, click Data. Open the Product Volume form.
    Product Volume Form
  2. Right click and select Business Rules. Select Groovy Load Product Volumes.
    Select Business Rule
  3. After the successful execution of the rule, the volumes for the products are updated.
    Product volumes are updated in the Product Volume form.

Want to learn more?



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