Oracle by Example brandingCreating an Orchestration to Export EnterpriseOne Data to CSV, JSON, and XML Formats

section 0Before You Begin

This 30-minute OBE shows you how to create an orchestration that includes a data request to retrieve EnterpriseOne data and output mappings that allow you to export that data retrieved from EnterpriseOne into CSV, JSON, and XML formatted files. Finally, the generated file will be returned as output from the orchestration call.

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 choose to export the data from the data request to a CSV, JSON or XML file. The file generated can be returned as output of the orchestration, sent in a REST request, or sent to an FTP location.

What Do You Need?

  • JD Edwards EnterpriseOne test environment with a minimum of EnterpriseOne Tools 9.2.6.0.

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

This section describes 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 Data Request icon, and then click the New button on the Orchestrations page.
  3. On the Data Request design page, complete these fields:
    • Name = Get_AddressBooks
    • Enter Short Description = Retrieve Address Book data
  4. In the Table/View Name field, enter V0101B and click Load.
  5. In the grid, click the Filter icon next to Address Number[F0101] - AN8.
  6. 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
  7. 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 1 (ADD1)
    • Postal Code (ADDZ)
    • City (CTY1)
    • ST (ADDS)
    • Ctry (CTR)
    • Prefix (AR1)
    • Phone Number (PH1)
  8. Click Save.

    The data request should look like this when complete:

    Data Request
  9. Click Manage and click Create Orchestration.
  10. Change the value of the following fields:
    • Name = Export_Files
    • Enter Short Description = Export EnterpriseOne Address Book data to other files
  11. Click Save.
    The orchestration should look like this when complete:
    12. Export_Files Orchestration

section 2Testing the Data Request Orchestration

  1. Double-click the Start node of the Export_Files orchestration.
    Alternatively, you can use click the Tools menu and click Run Orchestrations.
  2. Click Run. Verify the output. Verify that the output displays a list of all address book records with addresses and phones.
    Run Orchestrations Window

section 3Configuring the Orchestration Output to Generate a CSV File

This section describes how to configure the output of the orchestration to generate a CSV file and return that file as the output of the orchestration.

  1. Access the Export_Files orchestration you just created, click the End node, and click Outputs and Assertions.
  2. Click the menu (Output Array to File) in the Select column on the Data Browser – V0101B row.
    3. Output Array to File option
  3. Enable the Output Grid to File option.
  4. Enable the Overwrite Existing File option.
    5. Overwrite Existing File
  5. Click anywhere outside the Outputs and Assertion window and save the orchestration.
  6. Click the End node and click Outputs and Assertions, again.
  7. Click the Files tab and select Response is a .zip file.
  8. In the grid, in the Output File Variable drop-down list, select Get_AddressBooks_filename.
    9. Output and Assertions Window
  9. Save the orchestration.

section 4Testing the Orchestration CSV Output

  1. Double-click the Start node of the orchestration. Alternatively, you can click Tools and select Run Orchestrations menu.
  2. On the right side of the Run Orchestrations page, select application/octet-stream from the Accept drop-down list (if it is not already selected).
    3. Accept Drop-down List
  3. Click Run.
  4. Verify that an output and the OK status response is displayed.
    5. Orchestration Output
  5. Verify that the output file is downloaded.
    6. Downloaded File
  6. Open the downloaded .zip file from your download location, and open the Get_AddressBooks.csv file.
  7. The file should look like this (it may open in Excel, but you will see each record with an address book data):
    8. Get_AddressBooks.csv File
  8. Click the X icon to close the Run Orchestrations window and return to the orchestration.

section 2Generating an XML File

This section describes how to use the same data request and generate a second XML output file.

  1. Double-click the line between the data request and the end node. Alternatively, you can click Design Mode and click the plus (+) icon on the line.
  2. Select Data Request.
    3. Orchestration - Add Data Request
  3. Select Get_AddressBooks.
    4. Orchestration - Get_AddressBooks Data Request
  4. Verify that the second data request is added to the orchestration.
  5. Click the End node and select Outputs and Assertions.
  6. Enable the Select All option.
  7. Collapse the first Get_AddressBooks row by clicking the triangle icon.
  8. Change the name of the second data request grid output to Get_AddressBooks_xml.
  9. Click the menu in the Select column on the second Data Browser – V0101B line.
    10. Data Browser - V0101B
  10. Enable the Output Grid to File option.
  11. Select the XML option from the Output Type drop-down list.
  12. Enable the Overwrite Existing File option.
  13. Enable the Remove Array From Response option. Since we are getting the data out in a file it doesn’t need to be in the response.
    14. Remove Array From Response
  14. Click anywhere outside the Output and Assertions window to close it and save the Orchestration.
  15. Return to Outputs and Assertions and select the Files tab.
  16. Add the second (xml) file to the list of files for the output .zip file by choosing it from the drop-down list in the second row.
    17. Output File Variable - Select Output Zip File
  17. Save the Orchestration.
  18. Perform the steps in Section 4 again. Verify that the downloaded zip file contains both a .csv file and an .xml file.
    The XML file should like this (formatted for easier reading):
    19. Get_AddressBooks_xml File
  19. Close the Run Orchestrations page and return to the orchestration.

section 3Adding JSON File to the Orchestration Output

This exercise describes how to add a JSON file to the orchestration output.

  1.  Double-click the line between the data request and the end node.
    Alternatively, you can click Design Mode and click the plus (+) icon on the line.
  2. Select Data Request.
  3. Select Get_AddressBooks.
  4. Verify that the third data request is added to the orchestration.
  5. Click the End node and select Outputs and Assertions.
  6. Enable the Select All option.
  7. Collapse the first and second Get_AddressBooks rows using the triangle icon.
  8. Change the name of the second data request grid output to Get_AddressBooks_json.
  9. Click the menu in the Select column on the third Data Browser – V0101B line.
  10. Enable the Output Grid to File option.
  11. Select the JSON option from the Output Type drop-down list.
  12. Enable the Overwrite Existing File option.
  13. Enable the Remove Array From Response option. Since we are getting the data out in a file, it doesn’t need to be in the response.
    14. Remove Array From Response Option
  14. Click anywhere outside the Outputs and Assertions window to close it and then save the Orchestration.
  15. Return to Outputs and Assertions and select the Files tab.
  16. Add the third (json) file to the list of files for the output .zip file by choosing it from the drop-down list in the third row.
    17. Get_AddressBooks_json_filename File
  17. Save the Orchestration
  18. Perform the steps in Section 4 again. This time the zip file that is downloaded should have all three files -  a .csv file, an .xml file, and an .json file.
  19. The JSON file looks like this (formatted for easier reading):
    20. Get_AddressBooks_json File

more informationWant to Learn More?