E9.2 How to pass the output from data request to custom request (Groovy Script)

[vbansal]

How to pass the output from data request to custom request (Groovy Script)?
I have a data request over a business view with multiple records. How do I pass the output from DR to custom request scripted in Groovy. When running testing the custom request with test value in input works fine. I could be missing a simple mapping being my 1st attempt.

Here is the groovy script

import com.oracle.e1.common.OrchestrationAttributes;
import java.text.SimpleDateFormat;
import groovy.json.*;
HashMap<String, Object> main(OrchestrationAttributes orchAttr, HashMap inputMap)
{
HashMap<String, Object> returnMap = new HashMap<String, Object>();
//Input is the full output of the data request
String dataSetIn = (String)inputMap.get("DataSetIn");

//create an object out of it
def jsonSlurper = new JsonSlurper();
def object = jsonSlurper.parseText(dataSetIn);

//get the record set
def dataset = object.fs_DATABROWSE_V55RR01J.data.gridData.rowset;

def newDataSet = [];

for(def member : dataset) {


//create a new object for the resulting data set with the new date values
def newObj = [:]
newObj.freightClass=member.F55RR11_55FRCL;
newObj.actualWeight=member.F55RR11_WGTS;
newObj.weightUnit = "Pounds";


//add to new output array
newDataSet.push(newObj);
}
//create a json string from the new data set
String newDataSetJson = JsonOutput.toJson(newDataSet);
//set that to the array variable defined in the custom
returnMap.put("GridData", newDataSetJson);

return returnMap;
}

When I copy the output manually in test value input I get correct results in test output.



Screen for Orchestrator which doesn't work when called one after the other -

 

[DaveWagoner]

My original response over on tech sig board:

Hi, I want to start with the basics!

Your Data Request:

• Are you returning a data set variable name within the data request?
• If not, are you selecting "return raw data" in the transformation screen?

I'm guessing you're doing the 2nd option, and mapping the entire <datarequestname>.output variable to the scripting input?

If all this is true, I'm guessing that the output var from the datarequest has a structure that is a slight bit different than you're mapping to in the step "def dataset = object.fs_DATABROWSE_V55RR01J.data.gridData.rowset". The "hand off" of data between the data request and the expected structure of your script is probably where the issue is. Check the output of the data request within orch debugger which will hopefully give you the complete json output that you need to handle in your script.

Seeing your screenshots here I see you're populating a data set var. Are you also selecting "return raw data" in the transformation window of the data request? I think you will need to return raw data, and work from the "raw" json coming from the data request.

I think this part of my original post still applies:
I'm guessing that the output var from the datarequest has a structure that is a slight bit different than you're mapping to in the step "def dataset = object.fs_DATABROWSE_V55RR01J.data.gridData.rowset". The "hand off" of data between the data request and the expected structure of your script is probably where the issue is. Check the output of the data request within orch debugger which will hopefully give you the complete json output that you need to handle in your script.

 

[vbansal]

My original response over on tech sig board:



Seeing your screenshots here I see you're populating a data set var. Are you also selecting "return raw data" in the transformation window of the data request? I think you will need to return raw data, and work from the "raw" json coming from the data request.

I think this part of my original post still applies:
I'm guessing that the output var from the datarequest has a structure that is a slight bit different than you're mapping to in the step "def dataset = object.fs_DATABROWSE_V55RR01J.data.gridData.rowset". The "hand off" of data between the data request and the expected structure of your script is probably where the issue is. Check the output of the data request within orch debugger which will hopefully give you the complete json output that you need to handle in your script.

Thanks for your quick response, do I need to map all the columns that I have in DR output in the script.

 

[vbansal]

Yes I do have the return raw data on data request. Now that I mapped everything I am getting an error - "Message": "groovy.lang.MissingPropertyException: No such property: fs_DATABROWSE_V55RR01J for class: java.lang.Integer"

Here is the new Groovy
newObj.documentNumber=member.F55RR01_DOCO;
newObj.customerCCode=member.F55RR01_55CCOD;
newObj.companyKey=member.F55RR01_KCOO;
newObj.country=member.F55RR01_CTR;
newObj.originCtr=member.F55RR01_OTCTR;
newObj.originZip=member.F55RR01_OTZIP;
newObj.shipDate=member.F55RR01_SHIPDT;
newObj.weightUnit = "Pounds";
newObj.freightClass=member.F55RR11_UWUM;
newObj.deliveryNo=member.F55RR01_DELN;
newObj.shipmentWeight=member.F55RR01_WGTS;
newObj.orderType=member.F55RR01_DCTO;
newObj.freightClass=member.F55RR11_55FRCL;
newObj.UKID=member.F55RR01_UKID;
newObj.postalcode=member.F55RR01_ADDZ;

Data request output looks like this -
{
"F55RR01_DOCO": 1155173,
"F55RR01_55CCOD": "C8363601",
"F55RR01_KCOO": "00010",
"F55RR01_CTR": "US",
"F55RR01_OTCTR": "US",
"F55RR01_OTZIP": "77039",
"F55RR01_SHIPDT": "20240214",
"F55RR11_UWUM": "LB",
"F55RR01_DELN": 11346465,
"F55RR11_WGTS": 108.8,
"F55RR01_DCTO": "SO",
"F55RR11_55FRCL": "070",
"F55RR01_UKID": 921398,
"F55RR01_ADDZ": "80916"
},

 

[JohnDanter2]

This is doing the same thing using a Form Request, but the principle is the same

E9.2 - Custom groovy to read a form request response and check for errors

Hi folks I wish to add 2 output variables scsSuccess and scsFail to my orchestration. Now this is easy to do (even for me) in the manipluate output section it works great if the consumer was expecting JSON back, but in this case I want to see the 2 output as columns for use in Excel. So I need...

www.jdelist.com

 

[vbansal]

Ignore previous details. I was able to get the groovy script to work. Now struggling with how to create the body for the format I need. Here is the latest groovy that works but everything is inside for loop. I need to create more sections or objects.
import com.oracle.e1.common.OrchestrationAttributes;
import java.text.SimpleDateFormat;
import groovy.json.*;
HashMap<String, Object> main(OrchestrationAttributes orchAttr, HashMap inputMap)
{
HashMap<String, Object> returnMap = new HashMap<String, Object>();
//Input is the full output of the data request
String dataSetIn = (String)inputMap.get("CHRQuoteDataSet");

//create an object out of it
def jsonSlurper = new JsonSlurper();
def object = jsonSlurper.parseText(dataSetIn);

//get the record set
def dataset = object.fs_DATABROWSE_V55RR01J.data.gridData.rowset;

//def inputData = new JsonSlurper().parseText(inputMap.get("CHRQuoteDataSet"));
//def inputOrders = inputData.fs_DATABROWSE_V55RR01J.data.gridData.rowset;


def newDataSet = [];

for(def member : dataset) {


//create a new object for the resulting data set with the new date values
def newObj = [:]
newObj.documentNumber=member.F55RR01_DOCO;
newObj.customerCCode=member.F55RR01_55CCOD;
newObj.companyKey=member.F55RR01_KCOO;
newObj.country=member.F55RR01_CTR;
newObj.originCtr=member.F55RR01_OTCTR;
newObj.originZip=member.F55RR01_OTZIP;
newObj.shipDate=member.F55RR01_SHIPDT;
newObj.weightUnit = "Pounds";
newObj.freightClass=member.F55RR11_UWUM;
newObj.deliveryNo=member.F55RR01_DELN;
newObj.shipmentWeight=member.F55RR11_WGTS;
newObj.orderType=member.F55RR01_DCTO;
newObj.freightClass=member.F55RR11_55FRCL;
newObj.UKID=member.F55RR01_UKID;
newObj.postalcode=member.F55RR01_ADDZ;

//add to new output array
newDataSet.push(newObj);
}
//create a json string from the new data set
String newDataSetJson = JsonOutput.toJson(newDataSet);
//set that to the array variable defined in the custom
returnMap.put("GridData", newDataSetJson);

return returnMap;
}

Here is an example of JSON i need so I can pass into the api. Not sure how to get items:, origin: text before the brackets.
{
"items": [
{
"description": "AG 1100+ SWITCH FOR METAL PAN",
"freightClass": 70,
"actualWeight": 22,
"weightUnit": "Pounds",
"volumeUnit": "CubicFeet",
"declaredValue": 686.4,
"packagingCode": "CAS",
"productCode": "AG 1100+ SWITCH FOR METAL PAN",
"productName": "AG 1100+ SWITCH FOR METAL PAN",
"isStackable": false,
"nmfc": 70,
},
],
"origin": {
"locationName": ".",
"address1": "12128 New Berlin Rd",
"city": "Jacksonville",
"stateProvinceCode": "FL",
"countryCode": "US",
"postalCode": 32226
},
"destination": {
"locationName": "CARRIER SOUTHEAST 1850",
"address1": "1711 CORPORATION PKY",
"city": "RALEIGH",
"stateProvinceCode": "NC",
"countryCode": "US",
"postalCode": 27604
},
"shipDate": "2024-03-13",
"customerCode": "C8598560",
"transportModes": [
{
"mode": "LTL"
},
{
"mode": "Parcel"
}
]
}

 

[BetoJDE]

I tried to solve yout problem with ChatGPT, here is the work done, i hope that works and help

import com.oracle.e1.common.OrchestrationAttributes;
import java.text.SimpleDateFormat;
import groovy.json.*;
HashMap<String, Object> main(OrchestrationAttributes orchAttr, HashMap inputMap)
{
HashMap<String, Object> returnMap = new HashMap<String, Object>();
//Input is the full output of the data request
String originalDataList  = (String)inputMap.get("CHRQuoteDataSet");
def convertedItems = originalDataList.collect { originalData ->
    [
        description: "AG 1100+ SWITCH FOR METAL PAN",
        freightClass: originalData.F55RR11_UWUM,
        actualWeight: originalData.F55RR11_WGTS,
        weightUnit: "Pounds",
        volumeUnit: "CubicFeet",
        declaredValue: 686.4,
        packagingCode: "CAS",
        productCode: "AG 1100+ SWITCH FOR METAL PAN",
        productName: "AG 1100+ SWITCH FOR METAL PAN",
        isStackable: false,
        nmfc: originalData.F55RR11_55FRCL
    ]
}

def convertedJsonList = originalDataList.collect { originalData ->
    [
        items: convertedItems,
        origin: [
            locationName: ".",
            address1: "12128 New Berlin Rd",
            city: "Jacksonville",
            stateProvinceCode: "FL",
            countryCode: originalData.F55RR01_CTR,
            postalCode: originalData.F55RR01_OTZIP.toInteger()
        ],
        destination: [
            locationName: "CARRIER SOUTHEAST 1850",
            address1: "1711 CORPORATION PKY",
            city: "RALEIGH",
            stateProvinceCode: "NC",
            countryCode: originalData.F55RR01_CTR,
            postalCode: 27604
        ],
        shipDate: "2024-03-13",
        customerCode: originalData.F55RR01_55CCOD,
        transportModes: [
            [mode: "LTL"],
            [mode: "Parcel"]
        ]
    ]
}
//create a json string from the new data set
String newDataSetJson = JsonOutput.toJson(convertedJsonList);
//set that to the array variable defined in the custom
returnMap.put("GridData", newDataSetJson);

return returnMap;
}

 

[vbansal]

Thanks Beto this might work.

 

[vbansal]

That didnt work giving me all kinds of error