Before You Begin

This 45-minute tutorial shows how to create an orchestration that receives a CSV file as input, parses it, and uses a form request to insert records to JD Edwards EnterpriseOne. Additionally, you will learn how to create another orchestration that builds a CSV file of the records that were added and returns that as the output of the orchestration.

In this tutorial you will:

1. Record the sales order creating process using the process recorder.
2. Define an orchestration to receive multiple CSV files and additionally set a condition of receiving one file for each order.
3. Add a custom step to read the file, create an object with the order rows, and pass the object to a form request to add the records. (The script is provided in this OBE.)
4. Create another orchestration that fetches the new records, generates a CSV file, and returns the file as the output.

Background

Starting with Tools Release 9.2.6, you can define orchestrations to receive files as input or return files as output. You can perform several operations by using a file received as input to an orchestration, such as uploading the file as an attachment in JD Edwards EnterpriseOne, parsing the file by using a script, and using the data received.

Using a file received as an output, you can return a file fetched from an FTP site, return a file fetched from EnterpriseOne attachments, or generate a file in a script and return it. To call an orchestration that has file inputs, you must use the HTTP multipart/form-data type request, and to receive an output from an orchestration, you must use the application/octet-stream in the accept header of the HTTP request.

Recording the Sales Order Creation Process

Note: This task relies on EnterpriseOne demo data. You can use other data available in your system to enter a sales order to perform this task. You must have Process Recorder access, which is enabled through user-defined object (UDO) Feature Security and Action Security.

1. Sign in to the EnterpriseOne web client. You can access the EnterpriseOne web client from the Orchestrator Studio home page or from the Tools drop-down menu on any of the pages.
2. On the EnterpriseOne page, click the drop-down menu next to your user name and select Record a Process.
3. In the Process Recorder window, click the Start button.
4. Access the Sales Order Entry (P42101) application from the Fast Path or open the application from the Navigator menu.
5. Click Add.
6. Complete these header fields:
   • Sold To = 4243
   • Ship To = 4243
   • Branch/Plant = 30
7. Enter these values in the grid:
   Line 1
   • Quantity Ordered = 1
   • Item Number = 210
   Line 2
   • Quantity Ordered = 1
   • Item Number = 220
8. Click Submit and Close.
9. Click Return Values in the Process Recorder window.
10. Click Previous Order Number, Previous Order Type, and Previous Order Company.
    Note: You might have to move the Process Recorder window to see these fields. Click and drag the title bar to move it.



11. Click Resume in the Process Recorder window.
12. Click Stop in the Process Recorder window and enter a name:
    Name = SO Entry
13. Click Save.
14. Click Close to close the Process Recorder window.

Creating an Orchestration Using the Form Request

In this task, you will understand how to create an orchestration that performs order entry in JD Edwards EnterpriseOne. The orchestration created in this section will be used by the orchestration that receives the CSV file as an input explained in the section Creating an Orchestration to Accept the CSV Files as Input to Create Sales Orders.

1. Log in to Orchestrator Studio.
2. Click Form Requests.
3. Select the SO Entry form request in the left panel.
4. From the Manage menu, select Create Orchestration.
   The system creates an orchestration with inputs, transformations, and outputs to call the form service request.
5. Verify the orchestration.



6. Click the Start icon, and then click the Inputs and Values icon.
7. In the Inputs and Values window, verify the inputs, and click the Delete icon (X) to delete P42101_Version.



8. Click anywhere outside the Inputs and Values window to close the window.
9. Click the SO Entry form request and click the Transformations icon. Verify the transformations and click outside the Transformations window to close it.
   Note: The transformation for P42101_Version is invalid because you removed it as an orchestration input. That's OK. It will correct itself when you save the orchestration.



10. Click the End icon and click the Outputs and Assertions icon.|
    Verify the outputs and make sure that Select option is enabled for Previous Order, Previous Order Type, and Previous Order Company outputs.



11. Click Save. (The system saves the orchestration automatically with the name So Entry).
12. Click the Start icon and click Run Orchestration.
13. Click the Raw button and enter the following input to create the same order entered during the recording:
{
"Long_Address_Number_Sold_To": "4243",
"Long_Address_Number_Ship_To": "4243",
"HdrBusinessUnit": "30",
"GridIn_188_20_2": [
{
  "Quantity_Ordered": "1",
  "Item_Number": "210"
},
{
  "Quantity_Ordered": "1",
  "Item_Number": "220"
}
]
}
14. Click Run. You should see a new order created.



15. Click the Close icon.
16. To verify the newly created order in JD Edwards EnterpriseOne, click the Tools menu and click JD Edwards EnterpriseOne.
17. Access the Sales Order Entry (P42101) application using the Fast Path or open the application from the Navigator menu. Verify the newly created order.

Creating an Orchestration to Accept the CSV Files as Input to Create Sales Orders

In this task, you will understand how to create an orchestration that receives any number of CSV files (with order details for one order in each file) as input and then uses the orchestration created in the ”Creating an Orchestration to Add Sales Orders” section to add each order to JD Edwards EnterpriseOne.

Note:

In order to read the data from the input CSV file, parse it, and create sales orders you will need to use a custom step with a JRuby script. This tutorial is not focused on coding in JRuby, so you can simply copy and paste the script that is provided.

Creating Two CSV Files Using the Test Data

1. To create a CSV file with one row of test values, copy the following lines into any text editor and save the file with a .csv extension:
   Note:

   To ensure that the text is converted into a valid CSV file, enter the headers in one line which is followed by a new line, and enter all the values in the subsequent rows.

Long_Address_Number_Sold_To,Long_Address_Number_Ship_To,HdrBusinessUnit,Quantity_Ordered,Item_Number 4242,4242,30,1,210
4242,4242,30,3,220
4242,4242,30,2,230
2. Save the file as SODetail.csv.
3. Similarly, create another CSV file with this data:
Long_Address_Number_Sold_To,Long_Address_Number_Ship_To,HdrBusinessUnit,Quantity_Ordered,Item_Number 4243,4243,30,1,210
4243,4243,30,2,220
4. Save the file as SODetail2.csv.

Creating the Orchestration to Receive the CSV Files as Input

1. In the Orchestrator Studio home page, click Orchestrations.
2. Click New.
3. In the Name field, enter EnterSOCSV.
4. Click the Start icon and click Inputs and Values.
5. Select the File Inputs tab and perform these steps:
   1. Select the Process Files As An Array option.
   2. Enable the Overwrite Existing Files option.
   3. In the File Array Name field, enter CSVArray.

   

6. Click Save to save the orchestration.
7. Click Design Mode.
8. Click the + icon between the Start and End icons, and select Rule.
9. Click New and select Standard. This rule will be used to iterate over the incoming set of files.
10. Enter HaveAFile in the Name field.
11. Select Match All from the Match Type drop-down menu.
12. Select String from the Rule Type drop-down menu.
13. Enter fileName in the Value 1 field.
14. From the Operator drop-down menu, select >.
15. Enable the Literal option.
16. Enter 0 in the Value 2 field.
17. Select String from the Literal Value Type drop-down menu.
    
    This rule compares an input string called fileName and checks if it is greater than 0.



18. Click Save and then click the Close icon.
19. Click the HaveAFile rule and click the Transformations icon, and then from the Iterate Over drop-down menu, select CSVArray.
20. Select CSVArray.fileName from the Available Values drop-down menu for the fileName input.



21. Click Save.
22. Click Design Mode.
23. Click the + icon on the True path, select Custom, click New, and then select Script to add a new script.
    The script entered in this rule will use each file and create a JSON object to pass into the SO Entry orchestration.
24. In the Name and Description fields, enter SalesOrderCSV.
25. Select JRuby as the language from the drop-down menu.
26. Enter fileName in the input field.
27. Enter SODetail.csv in the Test Value field.
28. Enter OrderObject in both the Outputs field and the Variable Name field.
29. Replace the entire contents of the code field with the following script:
require 'json'
require 'csv'

def main(orchAttr, inputMap)

   returnMap = {}
   # Get the File Name from Inputs
   fileName = inputMap["fileName"]

   # Get the fully qualified name of the file in the temp directory
   tempFileName = orchAttr.getTempFileName(fileName);

   # Parse the CSV file into a table
   table = CSV.parse(File.read(tempFileName), headers: true)

   # Build an array of orders based on CSV table
   rowsArray = []
   orderObject = {}

   orderObject["Long_Address_Number_Sold_To"] =
   table[0] ["Long_Address_Number_Sold_To"]
   orderObject["Long_Address_Number_Ship_To"] =
   table[0]["Long_Address_Number_Ship_To"]
   orderObject["HdrBusinessUnit"] = table[0]["HdrBusinessUnit"]
  
   table.each do |row|
       #create row
   hash = {"Quantity_Ordered":row['Quantity_Ordered'],   "Item_Number":row['Item_Number']}
    rowsArray  << hash
   end
#put the rows in the grid order
orderObject["GridIn_188_20_2"] = rowsArray

# return the order, in a JSON object
returnMap["OrderObject"] = JSON.generate(orderObject)

return returnMap
end
30. Verify that your custom step is displayed as shown in the following screenshot:

 

31. Click Save and then click Close.
32. Click the SalesOrderCSV step in the orchestration and click the Transformations icon.
33. In the Transformations window, select CSVArray.fileName from the Available Values drop-down menu.



34. Click Save.
35. Click the Design Mode.
36. Click the + icon on the line after the SalesOrderCSV step, select Orchestration, and then select the SO Entry orchestration.
37. Click the SO Entry orchestration and then click the Transformations icon.
38. From the Available Values drop-down menu, select OrderObject for jde_object input as shown in the following screenshot:



39. Click Save.
40. Click the End icon and click Outputs and Assertions.
41. In the Outputs and Assertions window, enable the Named Object option for SO Entry and enable the Select option for the Previous Order, Previous Order Type, and Previous Order Company output values.



42. Save the orchestration.
43. Click the Start icon and click Run Orchestration.
44. Click the Files(0) button. The Files button is available for the orchestrations that have file inputs defined.



45. In the Files window, browse and select, or drag and drop the SODetail.csv and SODetail2.csv files.



46. Click Run.
47. Verify that the output is as shown in the following screenshot. The orchestration result should contain details about the two orders that were created (one order is created from each .csv file).
    Make a note of the two sales order numbers that were created.



48. Click the Close button.
49. To verify the results in JD Edwards EnterpriseOne, click the Tools menu and click JD Edwards EnterpriseOne.
50. Log in to JD Edwards EnterpriseOne and access the Sales Order Entry (P42101) application from the Fast Path or open the application from the Navigator menu.
51. Search the order numbers and verify that the order details match the details in each CSV file.

Creating an Orchestration to Generate CSV Files and Return Them As Orchestration Output

In this task, you will define an orchestration that fetches records from JD Edwards EnterpriseOne, generates a CSV file, and returns the file as the orchestration output.

Prerequisites

You must have orders available in the database to return them in the CSV file.

1. In the Orchestrator Studio home page, select Data Requests.
2. Click New and in the Name field, enter F4211 Orders Today.
3. Enter the table name F4211 and click Load.
4. Find the Order Date field and click the Filter icon.
5. In the Filter Criteria section, click the drop-down button in the Values field and select Today, and then click OK in the Set Special Value window.



6. In the left side panel, click the Return icon next to these fields: Order Co, Order Number, Or Ty, Line Number, Business Unit, Ship to Number, and 2nd Item Number.
7. Verify that all the fields are displayed in the Return Fields and Variable Names section and enable the Associated Description option for the Order Co, Or Ty, Business Unit, Ship To Number, and 2nd Item Number fields.



8. Verify that the Data Request page is similar to the following screenshot. Click Save.



9. From the Manage menu, click Create Orchestration.
10. Click Save to save the resulting orchestration.
11. Click the End icon and click Output and Assertions.
12. Enable the Return All Grid Rows option and click the Output Grid to File icon which is in the last column of the same row.
13. Enable the Output Grid to File option and do not change the other default options or values.
    Make sure that the Select option is enabled for all the values in the Output field for F4211 Order Today.



14. Click Save to save the orchestration.
15. Click the End icon and select Output and Assertions.
16. Click the Files tab.
17. Select the Response is a single file option.
18. Select F4211_Orders_Today_filename from the File Name drop-down menu.



19. Click Save to save the orchestration.
20. Click the Start icon and then click Run Orchestration.
21. To receive the file output, change the value in the Accept drop-down menu to application/octet-stream.



22. Click Run.
23. Verify that your browser has downloaded the resulting CSV file.



24. Open the downloaded CSV file and verify the orders. In the following example, the downloaded file is opened using Microsoft Excel.

Want to Learn More?

• Orchestrator Guide