advanced orchestration lessons

I’ve had a little help on this one, but I wanted to explain some frustrations / limitations in the current edition of the orchestration studio.

My requirements were simple (I thought).

I wanted to scroll through a complex result set, for each row build a string and eventually send that string to a web service.

My web service took a single payload of a parent | child data sequence.

Simple – yeah?  NO!!

My initial thoughts were to create the data request and then a groovy connector to concatenate the strings, but this does not work – as the variables only have scope for each iteration of the result set – i.e. I cannot concatenate and get the results after the entire resultset is complete… arrgghh

I started to get fancy and thought that I could use a connector, but this did not work either.

I started creating edit lines, and begin docs – thinking that I was clever, but this did not work either because of the above variable scope and iteration.

So.

I phoned a friend and Trev gave me a tip of manipulating the output of an orchestration.

I then proceeded to have something like:

This is simple.

I recommend that if you are going to use this method, you need to get the output of the data request from your orchestration client before you write code, as you will need to understand the output JSON document format.

See that I’ve not done and Add’s to the data, left is standard.

Looks something like:

{
   "ServiceRequest1" : {
     "ds_V4209C" : {
       "output" : [ {
         "groupBy" : {
           "F4209.RPER" : 58026
         },
         "F4301.OTOT_SUM" : 756389.52,
         "F4209.DOCO_COUNT_DISTINCT" : 9
       }, {
         "groupBy" : {
           "F4209.RPER" : 8444
         },
         "F4301.OTOT_SUM" : 228918.32,
         "F4209.DOCO_COUNT_DISTINCT" : 9
       }, {
         "groupBy" : {
           "F4209.RPER" : 58018
         },
         "F4301.OTOT_SUM" : 216092.0,
         "F4209.DOCO_COUNT_DISTINCT" : 7
       }, {
         "groupBy" : {
           "F4209.RPER" : 123238
         },
         "F4301.OTOT_SUM" : 113000.0,
         "F4209.DOCO_COUNT_DISTINCT" : 1
       }, {
         "groupBy" : {
           "F4209.RPER" : 7500
         },
         "F4301.OTOT_SUM" : 24893.75,
         "F4209.DOCO_COUNT_DISTINCT" : 3
       }, {
         "groupBy" : {
           "F4209.RPER" : 6002
         },
         "F4301.OTOT_SUM" : 20000.0,
         "F4209.DOCO_COUNT_DISTINCT" : 1
       }, {
         "groupBy" : {
           "F4209.RPER" : 6001
         },
         "F4301.OTOT_SUM" : 2287.29,
         "F4209.DOCO_COUNT_DISTINCT" : 2
       }, {
         "groupBy" : {
           "F4209.RPER" : 533095
         },
         "F4301.OTOT_SUM" : 1327.5,
         "F4209.DOCO_COUNT_DISTINCT" : 7
       }, {
         "groupBy" : {
           "F4209.RPER" : 7504
         },
         "F4301.OTOT_SUM" : 1000.0,
         "F4209.DOCO_COUNT_DISTINCT" : 1
       }, {
         "groupBy" : {
           "F4209.RPER" : 43393
         },
         "F4301.OTOT_SUM" : 593.75,
         "F4209.DOCO_COUNT_DISTINCT" : 1
       }, {
         "groupBy" : {
           "F4209.RPER" : 533093
         },
         "F4301.OTOT_SUM" : 70.0,
         "F4209.DOCO_COUNT_DISTINCT" : 3
       }, {
         "groupBy" : {
           "F4209.RPER" : 70012
         },
         "F4301.OTOT_SUM" : 25.0,
         "F4209.DOCO_COUNT_DISTINCT" : 1
       } ]
     }
   }

With this, I can formulate the code below.  Note the hierarchy of the output and the use of the iterator.

import groovy.json.JsonSlurper;
import groovy.json.JsonBuilder;
import com.oracle.e1.common.OrchestrationAttributes;
String main(OrchestrationAttributes orchAttr, String input)
{
   def jsonIn = new JsonSlurper().parseText(input);
   // modify jsonIn
    
   String bigString = "";
   orchAttr.writeWarn("TEsting");
   items = jsonIn.ServiceRequest1.ds_V4209C.output.iterator();
   while (items.hasNext()) {
     item = items.next();
     bigString += item.get("F4301.OTOT_SUM") + "|"
   }
   jsonIn.put("concat",bigString);
  
   orchAttr.writeWarn(bigString);
   
   def jsonOut = new JsonBuilder(jsonIn).toString();
  
   return jsonOut;
}

When I run this now, I have an additional parameter at the bottom:

  "concat" : "756389.52|228918.32|216092.00|113000.00|24893.75|20000.00|2287.29|1327.50|1000.00|593.75|70.00|25.00|"

Nice variable name!

So, now I need to create a SR that is a connector type that is going to put this output variable “concat” into my other SR that does an external post to my web service!  Easy.

my orchestration looks like this:

My connector SR looks like this:

Note that the concat variable is defined in the output of the connector SR

So When I call my orchestration that calls the SR that calls an orchestration…

I get the output that I want.  The concatenation of the resultset in a string that I can send to my web service, that is great…  A long journey though!