Example of Coding for Currency

This section provides an example of how to identify the currency processing type for a column in EnterpriseOne, and then it describes how to specify that currency processing type in the data service so that the proper currency is applied to the data aggregation.

The aggregation in this example uses the Expense Report Detail Table (F20112). The F20112 table has two business functions defined in the table event rules, one that processes domestic amounts and another that processes foreign amounts. The amount used in this example aggregation is a foreign amount, specifically the EXPFAMT (Expense Amount).

The following image shows Event Rules Design in EnterpriseOne with the second business function selected because this is the one that operates on EXPFAMT.

In this function, the Expense Amount (F20112.EXPFAMT) field is processed based on a single key value, a currency code Currency Code - From (F20112.CRCD), as shown in the following image:

The following information has been identified in EnterpriseOne, which is enough information to code the data aggregation request:

• Type = CURRENCYCODE

• Key column = CRCD

• Currency column = EXPFAMT

The following example code shows how to apply the preceding values to the data service request so that the request applies currency to the data aggregation:

//specify all three used capabilities needed for the request       
loginEnv.getUsedCapabilities().add(AISClientCapability.DATA_SERVICE);       
loginEnv.getUsedCapabilities().add(AISClientCapability.DATA_SERVICE_AGGREGATION);
loginEnv.getUsedCapabilities().add(AISClientCapability.AGGREGATION_CURRENCY_DECIMAL);
 
//create the request for the F20112 table, aggregation type
DataRequest dataAggregation = new DataRequest(loginEnv);
dataAggregation.setDataServiceType(DataRequest.TYPE_AGGREGATION);
dataAggregation.setFindOnEntry(true);
dataAggregation.setTargetName("F20112");
dataAggregation.setTargetType(DataRequest.TARGET_TABLE);
//define the aggregation, we are requesting a sum and average of the EXPFAMT
AggregationInfo aggregation = new AggregationInfo(loginEnv);
aggregation.addAggregationColumn("F20112.EXPFAMT", AggregationType.AGG_TYPE_SUM());
aggregation.addAggregationColumn("F20112.EXPFAMT", AggregationType.AGG_TYPE_AVG());
dataAggregation.setAggregation(aggregation);
 
//group by employee
aggregation.addAggregationGroupBy("F20112. EMPLOYID");
 
//create a currency processing object
AggregationInfo.CurrencyProcessing currency = new AggregationInfo.CurrencyProcessing(loginEnv);
//select the CURRENCYCODE type of processing
currency.setType(AggregationInfo.CurrencyProcessing.CURRENCYCODE);
//add the key column of CRCD
currency.addToKeyCols("F20112.CRCD");
//add the currency column to be processed as EXPFAMT
currency.addToCurrencyCols("F20112.EXPFAMT");
aggregation.setCurrency(currency);
 
String response =
    JDERestServiceProvider.jdeRestServiceCall(loginEnv, dataAggregation, JDERestServiceProvider.POST_METHOD,
      JDERestServiceProvider.DATA_SERVICE_URI);
 
//process the response, which will be by employee and currency code because the request included group by of employee and the currency processing key was currency code
 
ArrayNode array = AggregationResponseHelper.getAggregateValuesArray(response, "F20112");       
System.out.printf("%-10s %10s %10s %n", "EE ID", "Average","Currency");
System.out.printf("%-10s %10s %10s %n", "----", "------","-------");
for (Iterator groups = array.iterator(); groups.hasNext();) {
JsonNode aGroup = (JsonNode) groups.next();
JsonNode groupByInfo = aGroup.get(AggregationResponseHelper.GROUP_BY);
JsonNode average = aGroup.get("F20112.EXPFAMT_AVG");
JsonNode curencyCode = aGroup.get("currencyCode");
System.out.printf("%-10s %10s %10s %n", groupByInfo.get("F20112.EMPLOYID").asText().trim(), average.asText(),curencyCode.asText());
        }

The following image shows the charting of data used in this example:

To better understand the processing of the response, the JSON response looks like this:

{
  "ds_F20112" : {
    "output" : [ {
      "groupBy" : {
        "F20112.EMPLOYID" : 6002
      },
      "F20112.EXPFAMT_SUM" : 3532.88,
      "F20112.EXPFAMT_AVG" : 235.53,
      "currencyCode" : "USD"
    }, {
      "groupBy" : {
        "F20112.EMPLOYID" : 6001
      },
      "F20112.EXPFAMT_SUM" : 128773,
      "F20112.EXPFAMT_AVG" : 64387,
      "currencyCode" : "JPY"
    }, {
      "groupBy" : {
        "F20112.EMPLOYID" : 6001
      },
      "F20112.EXPFAMT_SUM" : 343.02,
      "F20112.EXPFAMT_AVG" : 343.02,
      "currencyCode" : "USD"
    }, {
      "groupBy" : {
        "F20112.EMPLOYID" : 5651
      },
      "F20112.EXPFAMT_SUM" : 214.06,
      "F20112.EXPFAMT_AVG" : 71.35,
      "currencyCode" : "USD"
    }, {
      "groupBy" : {
        "F20112.EMPLOYID" : 5127
      },
      "F20112.EXPFAMT_SUM" : 2845.0,
      "F20112.EXPFAMT_AVG" : 1422.5,
      "currencyCode" : "USD"
    }, {
      "groupBy" : {
        "F20112.EMPLOYID" : 7702
      },
      "F20112.EXPFAMT_SUM" : 175.4,
      "F20112.EXPFAMT_AVG" : 175.4,
      "currencyCode" : "CAD"
    }
   ]
  }
}