Creating an Orchestration to Export EnterpriseOne Data to CSV, JSON, and XML Formats

Before You Begin

This OBE shows you how to create an orchestration that includes a data request to retrieve EnterpriseOne data and a custom service request that uses Apache Groovy to export data retrieved from EnterpriseOne into CSV, JSON, and XML formatted files.

Duration: 20 minutes.

Background

In the Orchestrator Studio, you can add a data request to an orchestration to enable an orchestration to retrieve data from EnterpriseOne. You can also add a service request to an orchestration that uses Groovy scripting to route data to another database or execute a custom process, such as outputting data into another format. To use Groovy in a service request, the Orchestrator Studio provides an edit area that contains a sample Groovy script with instructions on how to modify the script.

What Do You Need?

• Orchestrator Studio 6.1.0.0 or higher deployed in a JD Edwards EnterpriseOne test environment with a minimum of EnterpriseOne Tools 9.2.2.4.
• Access to the EnterpriseOne AIS Server file system so you can locate and open the exported csv, json, and xml files.

Creating an Orchestration with a Data Request to Retrieve Data from EnterpriseOne

This task will show you how to create an orchestration that includes a data request to retrieve Address Book data from the V0101B business view in EnterpriseOne.

1. Access the Orchestrator Studio.
2. On the Orchestrator Studio home page, click the Orchestrations icon, and then click the New Orchestration button on the Orchestrations page.
3. On the Orchestration design page, complete these fields:
• Orchestration = Groovy_Export_Files
• Enter Short Description = Export EnterpriseOne Address Book data to other files.
4. In the Orchestration Steps grid, click Add Step and select Service Request.
5. At the end of the row with the new step, click “Edit” (pencil icon) and select Data Request.
6. On the Data Request design page, complete the following fields:
• Service Request = Get_AddressBooks
• Enter Short Description = Retrieve Address Book data.
7. In the Table/View Name field, enter V0101B and click Load.
8. In the grid, click the Filter icon next to Address Number[F0101] - AN8.
9. In the Conditions area, configure the condition to filter on address numbers greater than zero, which should look like this when complete:
   Address Number[F0101] > 0
10. To specify the data for the data request to return, in the Fields grid, click the Return icon next to the following fields:
• Number(AN8)
• Alpha Name(ALPH)
• Sch Type(AT1)
• Address Line(ADD1)
• Postal Code(ADDZ)
• City(CTY1)
• ST(ADDS)
• Ctry(CTR)
• Prefix(AR1)
• Phone Number(PH1)
11. Click Save.

    The data request should look like this when complete:

    

12. Close the Data Request to return to the orchestration.
13. At the end of the service request step, click the down arrow and select the Get_AddressBooks data request.
    

    Notice that the Orchestrator Studio automatically adds Get_AddressBooks.output as an orchestration input, so that the data returned from the data request can be mapped to a subsequent step in the orchestration.

    

14. Save the orchestration.

Adding a Custom Service Request to Export Data to a CSV File

In this task, you will create and add a custom service request to the orchestration. The custom service request contains a Groovy script, provided for you, that includes instructions for exporting data to a CSV file.

1. In the Groovy_Export_Files orchestration, click Insert Step After to add another step, then select Service Request.
2. At the end of the new step, click “Edit” (pencil icon) and select Custom.
3. On the Custom design page, complete the following fields:
• Service Request = Groovy_Export_CSV
• Enter Short Description = Export Address Book data to CSV file.
4. In the first row in the Input grid, enter One Line Data in the Input column.
5. In the Output grid, enter the following outputs:
• CSVComplete
• CSVOutFileName
• csv
• CSVcount



6. In the edit area, replace the Groovy template with this Groovy script (click here).

   This script will parse the JSON text and update it. See the comments within the script for more details about the process.

7. Save the custom service request, and then close it to return to the orchestration.
8. At the end of the service request step, click the down arrow and select the Groovy_Export_CSV custom service request.
9. Map the output of the data request to the custom service request input:
1. Make sure the Groovy_Export_CSV service request is selected in the Orchestration Steps grid.

   Notice that the Orchestrator Studio displays the One Line Data input in the Transformations grid. This is the input to the custom service request.

2. Click the Additional Values drop-down list next to the One Line Data input and select Get_AddresssBooks.output.
10. Save the orchestration.

Adding a Custom Service Request to Export Data to a JSON File

In this task, you will create another custom service request to add to the orchestration, this time with a Groovy script for exporting data to a JSON file.

1. In the Groovy_Export_Files orchestration, click the Insert Step After button to add another step, and then select Service Request.
   
2. At the end of the new step, click “Edit” (pencil icon) and select Custom.
3. On the Custom design page, complete the following fields:
• Service Request = Groovy_Export_JSON
• Enter Short Description = Export Address Book data to JSON file.
4. In the first row in the Input grid, enter One Line Data in the Input column.
5. In the Output grid, enter the following outputs in consecutive rows:
• JSONComplete
• JSONOutFileName
• json
• JSONcount
6. Replace the Groovy template with this Groovy script (click here) .

   The script will parse the JSON text and update it. See the comments within the script for more details about the process.

7. Save the service request.

   The custom service request should look like this when complete:

   

8. Close the custom service request to return to the orchestration.
9. At the end of the service request step, click the down arrow and select the Groovy_Export_JSON custom service request.
10. Map the output of the data request to the custom service request input:
1. Make sure the Groovy_Export_JSON service request is selected in the Orchestration Steps grid.

   Notice that the Orchestrator Studio displays the One Line Data input, the input to this custom service request, in the Transformations grid.

2. Next to the One Line Data input, click the Additional Values drop-down list and select Get_AddresssBooks.output.
11. Save the orchestration.

Adding a Custom Groovy Service Request to Export Data to an XML File

In this task, you will create another custom service request to add to the orchestration, this time with a Groovy script for exporting data to an XML file.

1. In the Groovy_Export_Files orchestration, click the Insert Step After button to add another step, and then select Service Request.
2. At the end of the new step, click “Edit” (pencil icon) and select Custom.
3. On the Custom design page, complete the following fields:
• Service Request = Groovy_Export_XML
• Enter Short Description = Export Address Book data to XML file.
4. In the first row in the Input grid, enter One Line Data in the Input column.
5. In the Output grid, enter the following outputs in consecutive rows:
• XMLComplete
• XMLOutFileName
• xml
• XMLcount
6. Replace the Groovy template with this Groovy script (click here).

   See the comments within the script for more details about the process.

7. Save the Service Request.

   The custom service request should look like this:

   

8. Close the Groovy_Export_XML custom service request to return to the orchestration.
9. At the end of the new service request step, click the down arrow and select the Groovy_Export_XML custom service request.
10. Map the output of the data request to the custom service request input:
    1. Make sure the Groovy_Export_XML service request is selected in the Orchestration Steps grid.
       

       The Orchestrator Studio displays the One Line Data input, the input to this custom service request, in the Transformations grid.

    2. Next to the One Line Data input, click the Additional Values drop-down list and select Get_AddresssBooks.output.
11. Save the orchestration.
12. Click the Orchestration Output button.
13. In the Outputs screen, click Add.
14. Scroll down and expand the Custom node for Groovy_Export_CSV, and then select the check box next to CSVOutFileName.
15. Expand the Custom node for Groovy_Export_JSON and select the check box next to JSONOutFileName.
16. Expand the Custom node for Groovy_Export_XML and select the check box next to XMLOutFileName.

    Optional: You can select the other Output fields also you want to see them in the Orchestration response.

17. Click OK, and then close the Outputs screen.
18. Save the orchestration.

    The orchestration should look like this:

    

Testing the Orchestration

Use the Orchestrator Client to test the orchestration, making sure that it retrieves Address Book records from EnterpriseOne and exports this data to CSV, JSON, and XML files.

1. In the Orchestrator Studio, click the Tools link in the upper-right corner, and then click the Orchestrator Client icon.
2. Sign in to Orchestrator Client with the same credentials you used to access the Orchestrator Studio.
3. Click the Orchestration Name drop-down list and select Groovy_Export_Files.
4. Click Run.
   

   A green check mark appears if the orchestration ran successfully. The results of running the test should look like this:

   

5. In the Output area, note the location of the output files, and then locate them on the AIS Server.
6. Open the three generated files and verify that each file contains the address book records.

Want to Learn More?

• "Configuring a Data Request in the JD Edwards EnterpriseOne Tools Orchestrator Guide
• "Configuring a Custom Service Request with Groovy" in the JD Edwards EnterpriseOne Tools Orchestrator Guide