icons of 3 formats for exporting data using Orchestrator Studio

Creating JDE orchestrations to export data

In our last blog, we looked at the relationship between AIS and Orchestrator Studio. Together, they empower JD Edwards users to build orchestrations that simplify and automate business processes. More specifically, orchestrations can trigger JDE data exports to provide end users with real-time updates. The better informed an end user, the quicker they can respond to changes in the business environment. 

Service request types 

Our ERP Suites’ customers are using form and data service request orchestrations to export data to Power BI dashboards, robotic process automation (RPA) tools, data warehouse systems, mobile applications, and scanning devices. Still, there are even more possibilities. You can employ messages requests, watchlist requests, report requests, or create a custom request with Java or Groovy.

The basics of exporting data

Regardless of the EnterpriseOne version you are running, JSON is the default input/output format for orchestration. It makes sense since JSON notation is easiest for people, applications, and web services to read. The custom HTTP headers you set for your service request then control JSON.

Set your custom HTTP headers for Content-Type and Accept to either application/json or application/xml. The REST client looks to the Accept header to understand the media type of the request coming from AIS. On the other hand, Content-Type identifies the request media type the client is sending to the AIS server. These terms generally apply to any REST client such as SoapUI and development languages, including JavaScript, Java, Python, and Visual Basic. 

JSON output tends to be the easiest of the media types when building orchestrations. However, most applications can convert JSON if your client requires XML, CSV, or another format. 

A JSON input/output cURL command might look like this:

curl -X POST -H 'Content-Type: application/json' -H 'Accept: application/json' -i 'https://aistest.myerpsuites.com/jderest/orchestrator/BI_42_OR_F42199Query' --data '{"deviceName":"PowerBI","username":"orch","password":"xxxxxxx"}'

A JSON input with XML output using cURL might look like this:

curl -X POST -H 'Content-Type: application/json' -H 'Accept: application/xml' -i 'https://aistest.myerpsuites.com/jderest/v3/orchestrator/BI_42_OR_F42199Query' --data '{"deviceName":"PowerBI","username":"orch","password":"xxxxxxx"}'

Note: JDE users running E1 or above must change the URL to invoke Version 3 orchestration APIs. If not, you may get an HTTP status 406 Not Acceptable or 500 error

Saving the JSON output as a .JSON file, you can also use Power Query for Excel 365 to read the data:

  1. Go to the Excel Data tab. Click Get Data > From File > From JSON.
    Power query
  2. Select your JSON file and click Open.
  3. Once your data is loaded, click Convert > Into Table > Close & Load.

If your client requires a CSV file, you can add a custom Groovy script to generate the export. The Oracle Help Center has a great tutorial to get you started. Groovy takes a bit of time and experience, but it’s a good option for users running E1 and later. If you are less familiar with Groovy or are running a lower version of E1, you could always create a conversion from the JSON output.  

Orchestration tips for exporting data

  • If you need to retrieve more than a few hundred records, use Orchestrator’s page size feature introduced in Tools and later. We find the 1,000 rows setting works well. It keeps the JAS instance from significantly increasing in heap size and balances performance. Learn more.
  • Change the JAS instance Maximum Request Parameters default to ensure your record count is not limited. We generally set the parameter to 500,000. Learn more
  • Set the AIS and JAS instance Java heap min/max size. A good starting point for the AIS instance is between 256m and 1024m. The JAS instance used by AIS for orchestrations varies from 1024m to 6192m. We set most configurations between 1024m and 2048m then monitor the JVM heap usage.

Now that you know more about the options for exporting data read how ERP Suites took this approach to bring visibility to Stanley Black & Decker’s shop floor. Then visit LearnJDE for more practical benefits of this powerful Orchestrator feature. In our next blog, we'll focus on the flip side with orchestrations for importing data.

Need help getting started or taking orchestration to the next level? Click to learn about Orchestrator Services. ERP Suites is a JD Edwards Gold Partner and winner of the 2019 Oracle Distinguished Partner Award for Successful Adoption of Orchestrator.