Before You Begin

This 45-minute Oracle-by-Example (OBE) shows you how to create multiple sales orders in JD Edwards EnterpriseOne using a set of comma-separated values (CSV) files as input. This scenario is similar to the Using Files as Orchestration Input and Output OBE; however, it is simplified because it uses the Custom Create Array step type (Tools Release 9.2.7.4) instead of a custom script to map the data.

In this OBE you will:

1. Record the process to create a sales order using the process recorder.
2. Define an orchestration to receive multiple CSV files, set a condition of receiving one file for each sales order, and automatically create sales orders.
3. Use a Custom Create Array step to map the data in the CSV files into orchestration inputs.
4. Test the orchestration.

Background

Starting with Tools Release 9.2.7.4, you can use a Custom Create Array step to map inputs from a CSV file, a JSON file, or a JSON string into orchestration inputs. The Custom Create Array step provides an easy-to-use interface for mapping data to inputs; it is very similar to the FTP Connector interface for mapping data from CSV files coming from an FTP site. However, with the Custom Create Array step the files don’t have to reside on an FTP site; they can come as orchestration inputs or from a previous orchestration step, such as a REST connector that receives files from an external API.

What Do You Need?

• Access to a JD Edwards EnterpriseOne environment with Tools Release 9.2.7.4 or later.
  JD Edwards EnterpriseOne Trial Edition running on Oracle Cloud Infrastructure is suitable, but you can use any environment with the proper Tools release.
• Access to JD Edwards Orchestrator Studio.
• Access to the Process Recorder feature.
• The steps in this OBE refer to standard EnterpriseOne demo data. You can use other data available in your system to enter a sales order to perform this task.

Recording the Sales Order Creation Process

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 X (Disable Capture) to close the Process Recorder window.
15. Click Close to close the Manage Existing Order form.

Creating an Orchestration Using the Form Request

In this section, 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 section.

1. Log in to Orchestrator Studio.
2. Click Form Requests.
3. Select the SO Entry form request in the left panel.
4. In the SO Entry form request window, click step 2 Enter New Order.
   



The system displays the Order of Execution table with the steps you recorded in the previous section. Edit the values in the table according to the following steps.

Note: The orchestration works as expected by using the input names and default values that were generated by Record a Process. The input names are easier to understand when you edit the values in the table.

5. In the first row, in the Input column, change the name of the input to Sold_To. In the Default Value column, delete 4243.
6. In the second row, in the Input column, change the name of the input to Ship_To. In the Default Value column, delete 4243.
7. In the third row, in the Input column, change the name of the input to Branch. In the Default Value column, delete 30.
8. In the fourth row, in the Input column, change the name of the input to Sales_Order_Detail.
9. In the fifth row, in the Default Value column, delete 1.
10. In the sixth row, in the Default Value column, delete 210.



11. Click Save.
12. Click the Manage menu and select Create Orchestration.
    The system creates an orchestration with inputs, transformations, and outputs to call the form service request.
13. Verify the orchestration.



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



16. Click anywhere outside the Inputs and Values window to close the window.
17. 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.



18. 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.



19. Click Save. (The system saves the orchestration automatically with the name SO Entry).
20. Click the Start icon and click Run Orchestration.
21. Click the Raw button. Enter the following input to create the same order that you entered in the Sales Order Entry application during the recording in the previous section:
{
"Sold_To": "4243",
"Ship_To": "4243",
"Branch": "30",
"Sales_Order_Detail": [
{
  "Quantity_Ordered": "1",
  "Item_Number": "210"
},
{
  "Quantity_Ordered": "1",
  "Item_Number": "220"
}
]
}
22. Click Run.
    Verify the newly created order and note the order number.



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



26. Click Close to close the application.

Creating Two CSV Files Using the Test Data

1. To create a CSV file with one or more sales orders, 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, each on a new line.
   
   The first 3 columns of the CSV file are defined as Header Fields, meaning all consecutive rows that have matching values in the first 3 columns are grouped into a single header Sales Order object with a detail array that contains the values from the other columns.
   
   The last 2 rows in this CSV file generate a separate sales order using the break functionality.

Sold_To,Ship_To,Branch,Quantity_Ordered,Item_Number
4242,4242,30,1,210
4242,4242,30,3,220
4242,4242,30,2,230
4343,4343,30,1,210
4343,4343,30,2,220


2. Save the file as SODetail1.csv.
3. Similarly, create another CSV file with this data:
Sold_To,Ship_To,Branch,Quantity_Ordered,Item_Number
4243,4243,30,1,210
4243,4243,30,2,220


4. Save the file as SODetail2.csv.

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

In this section, you will understand how to create an orchestration that receives any number of CSV files (each file may contain many orders based on the header fields selected) as input and then uses the orchestration created in the Creating an Orchestration Using the Form Request section to add each order to JD Edwards EnterpriseOne.

Creating an Orchestration and Adding the Rule

1. In the Orchestrator Studio home page, click Orchestrations.
2. Click New.
3. In the Name field, enter Import Sales Orders from CSV.
4. In the Description field, enter Import one or more sales orders from CSV files. Transform the CSV input using a Custom Create Array step.
5. Click the Start icon and click Inputs and Values.
6. 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.

   

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



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



22. Click Save.

Adding the Custom Create Array Step

In this section, you will create a Custom Create Array step to map the data from the CSV files into orchestration inputs. This is the main objective of this OBE.

1. In the Import Sales Orders from CSV orchestration page, click Design Mode.
2. Click the + icon on the True path, select Custom, click New, and then select Create Array.  
3. In the Name and Description fields, enter Map Inputs from CSV.
4. Ensure that the CSV File option is selected.
   Note: You can use what you’ve learned in this OBE to similarly map data from a JSON file or a JSON string.
5. In the Data Set Variable Name field enter Sales_Order_Data.
   This is the name of the top-level array that will be created from the CSV data.
6. Enable the CSV Has Headers option.
7. Click the Sample File button.
8. Navigate and select the CSV file created in the previous section (SODetail1.csv).
   Notice that the Member Name and Variable Name columns are automatically populated with the column headers from your CSV file.
   
   You don't need to manually enter these values because the Sample File button reads the column headers in your CSV file and auto-populates them in the Custom window.
   
   The Variable Names for each column from the CSV file must match the expected inputs to the SO Entry orchestration since each order object will be passed to the orchestration using jde__object. Since the CSV column names already match the input variable names for the SO Entry orchestration, you do not need to change the names.
   
   The inputs for the orchestration created in the previous section had three header fields (Sold_To, Ship_To, and Branch) and an array named Sales_Order_Data with two members (Quantity_Ordered and Item_Number).
   
   The input looked like this:
{
"Sold_To": "4243",
"Ship_To": "4243",
"Branch": "30",
"Sales_Order_Data": [
{
  "Quantity_Ordered": "1",
  "Item_Number": "210"
},
{
"Quantity_Ordered": "1",
"Item_Number": "220"
}
]
}
9. To designate the first three members as header fields, enable the Header Field option for these three members: Sold_To, Ship_To, and Branch.
   
   As soon as you turn on the Header Field option for at least one member, Orchestrator Studio enables the Header Object and Detail Object fields for you to provide names for the header object and detail array created for each group.
   
   All the members designated as header fields will be in the Header Object, and all other members are added to an array using the Detail Object name.  The Data Set Variable Name will be the name of the array containing each of the header objects created based on changes in the values in any of the columns declared as Header Fields.
10. Rename Header Object to Sales_Order_Header and Detail Object to Sales_Order_Detail.
    Verify that the custom step is displayed as shown in the following screenshot. It is important that the Variable Names and the Detail Object name match the inputs in the SO Entry orchestration created in the first section of this OBE.



11. Click Save and then click Close.
12. Click the Map Inputs from CSV step in the orchestration and click the Transformations icon.
13. In the Transformations window, select CSVArray.fileName from the Available Values drop-down menu.



14. Click Save.

Adding the SO Entry Orchestration Step

1. In the Import Sales Orders from CSV orchestration page, click Design Mode.
2. Click the + icon on the line after the Map Inputs from CSV step, select Orchestration, and then select the SO Entry orchestration.
3. Click the SO Entry orchestration and then click the Transformations icon.
4. From the Iterate Over drop-down menu, select Sales_Order_Data.
5. From the Available Values drop-down menu, under Map Inputs from CSV, select Sales_Order_Data.Sales_Order_Header for jde_object.



6. Click Save.
7. Click the End icon and click Outputs and Assertions.
8. In the Outputs and Assertions window, enable the Select option for the Previous Order, Previous Order Type, and Previous Order Company output values.



9. Click Save.

Testing the Orchestration

1. Click the Start icon and click Run Orchestration.
2. Click the Files(0) button. The Files button is available for the orchestrations that have file inputs defined.



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



4. Click Run.
5. Verify that the output is as shown in the following screenshot. The orchestration result should contain details about the three orders that were created (two orders are created using SODetail1.csv and one order is created using SODetail2.csv).
   Make a note of the three sales order numbers that were created.



6. Click the Close button.
7. To verify the results in JD Edwards EnterpriseOne, click the Tools menu and click JD Edwards EnterpriseOne.
8. 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.
9. Search the order numbers and verify that the order details match the details in each CSV file.

Conclusion

In this OBE, you learned how to:

• Record a process and create an orchestration to create a sales order.
• Create an orchestration that accepts files as inputs.
• Create a rule that iterates over an array of file names until there are no more files.
• Use a Custom Create Array step to read the contents of an input CSV file and map those members to orchestration variables.
• Add the sales order orchestration as a step in the parent orchestration and iterate to create sales orders for each input CSV file.

Want to Learn More?

• Orchestrator Guide
• Quick Tour: Mapping Inputs from a CSV File
• Tutorial: Using Files As Orchestration Input from CSV