Before You Begin

This 30-minute OBE shows you how to create a logic extension to access the Sales Order Detail table (F4211) and use the aggregation function to list the popular products ordered by the customers.

In this OBE, you will:

• Create a logic extension and add an array as a data structure.
• Add an Aggregation function to the logic extension and use the Filter By, Group By, Aggregation, and Having operations to filter for the most ordered product names.
• Test the logic extension.

Background

Logic extensions are a key component of the JD Edwards extensibility framework. They enable nontechnical business analysts as well as JD Edwards developers to use the familiar syntax of EnterpriseOne event rules to create logic without the need for a development client and without the need to build and deploy a package. Logic extensions provide a no-code way to manipulate numerical, date, and string data. Logic extensions can also read and write data to EnterpriseOne tables.

Using the aggregation feature in logic extensions, you can aggregate data that is read from the EnterpriseOne tables. In addition to reading data from the EnterpriseOne tables, logic extensions can now provide functions such as sums, averages, counts, and minimum/maximum values. These aggregated results can then be used in subsequent operations within the logic extension or passed as output to the next step in an orchestration.

What Do You Need?

To perform the steps in this OBE, you will need:

• Access to a JD Edwards EnterpriseOne environment with Tools Release 9.2.7 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.
• Security access to use Orchestrator Studio.

Scenario

Jennifer is an eCommerce administrator. She would like to access a list of the in-demand products to research and understand the current market needs.

Creating Logic Extension and Adding an Array as Data Structure

In this section, you will learn how to create a new logic extension and add an output array as a data structure.

1. Sign in to the Orchestrator Studio.
2. Click Logic Extensions.
3. Click the New button.
4. In the Name field, enter Sales Order Detail.
5. In the Description field, enter List the most ordered product names.
6. To add an array as a data structure, click the +Array button.
7. Change the name of the newly added array to OrderList.

Note: You will learn how to add members to the OrderList array in the next section.

8. From the IO Type drop-down list, select Output Only.



9. Click Save.

Building the Logic Extension

In this section, you will learn how to add an aggregation statement. You will also learn how to filter columns in the F4211 table by OrderType SO, group the columns by description, use aggregation operation to add the total order count, and sort the orders in descending order.

1. Click the Logic Extensions tab.
2. In the design panel, hover over the line between the Start and the End node and click the + icon.
3. From the context menu, select Aggregation. The system displays an Aggregation statement along with the branch label in the design panel and an Aggregation panel on the right.
   
   Note: You can also drag and drop the Aggregation option from the Statements tab to the + icon between the Start and End node in the design panel.



4. To add the Sales Order table, in the Object Name field, enter F4211 and press Enter.



5. Click the Edit icon. The Column Mapping-Aggregation window is displayed along with all the columns in the F4211 table.
6. To filter the rows in the F4211 table by OrderType:
   1. In the Filter Criteria section, click the Filter icon next to OrderType[DCTO].
      The value in the Operator type field is displayed as is equal to and in the Mapped Object field,  the Edit icon is highlighted automatically.
   2. To add a value in the Mapped Object field, click the Literal icon in the right panel.
      The Literal type is displayed as Single Value by default.
   3. In the Single Value field, enter SO.

   

   4. Click the OK button.
7. To group the columns by the description: 
   1. Click the GroupBy icon in the DescriptionLine1[DSC1] row.
      You can search for DescriptionLine1 using the Filter field.
   2. In the Group By section, click the drop-down list next to DescriptionLine1, click Add to Array, and then select OrderList. The system adds DescriptionLine1 as a member to the OrderList output array created in the previous section.
      In the Mapped Object field, the value GrpDescriptionLine1 is displayed automatically.

   

   
   
8. In the Aggregations section:
   1. Click the drop-down list next to Include Count, click Add to Array, and then click OrderList.
      The system enables the Include Count option and displays F4211_Count next to the Include Count field.
      The system adds F4211_Count as a member to the OrderList output array created in the previous section.
   2. To calculate the total order count, filter and search for UnitsSecondaryQtyOr[SQOR].
   3. Click the Aggregation icon and select Sum.
   4. In the Aggregation section, select the drop-down list next to UnitsSecondaryQtyOr[SQOR], click Add to Array, and then select OrderList.
      The system adds UnitsSecondaryQtyOr as a member to the OrderList output array.
      The value AggSumUnitsSecondaryQtyQr is displayed in the Mapped Object field automatically.

   

9. To sort the orders by descending order:
   
   1. Click the Order By icon in the UnitsSecondaryQtyOr[SQOR] row.
   2. In the Order By section, select Descending from the Order drop-down list.

   

10. Click the Close icon.
11. Click Save.
12. In the design panel, hover over the line after the For Each Row label and click the + icon.
13. From the context menu, select Array Function. The Array Function is added in the design panel and the Array Function panel is displayed on the right.
14. In the Array Function panel, from the Array drop-down list, select OrderList.
15. From the Array Function drop-down list, select append row.
16. Click Save.
17. Click the Data Structure tab. The newly added array members are displayed.
18. Change the values in the Name field from:
    
    • GrpDescriptionLine1 to Product Name
    • F4211_count to Total Order Count
    • AggSumUnitsSecondaryQtyOr to Total Quantity Ordered

    

19. Click Save.

Testing the Logic Extension

1. Click the Test tab.
2. Click the Test button. You can view the output in the Response section.



3. Click the Array Output icon in the Output field.
   Verify that the sales orders are sorted in decreasing order depending on the number of quantities ordered.
   
   Note: The results of this logic extension include all the sales orders. To learn more about how to further filter the orders, see the next section.

Using the Having Operation

In this exercise, you will learn how to filter the columns to fetch records that have the total number of orders greater than 10000 by using the Having operation in the Column Mapping-Aggregation window.

1. Access the Sales Order Detail logic extension.
2. In the design panel, click Aggregation.
3. In the right-panel, click the Edit icon. The Column Mapping-Aggregation window is displayed.
4. Use the Filter field and search for the UnitsSecondaryQtyOr[SQOR] column and click the Having icon.
5. In the Having section, from the Operator drop-down list, select >.
6. In the right panel, click the Literal icon, and in the Single Value field, enter 10000.
7. Click OK.



8. Click Close.
9. Click Save.

Testing the Logic Extension

1. Click the Test tab.
2. Click the Test button. You can view the output in the Response section.
3. Click the Array Output icon in the Output field.
   Verify that the result displays only the sales orders greater than 10000 and they are sorted in decreasing order.

Note: You can add this logic extension to a new orchestration by clicking the Manage menu, and then selecting Create Orchestration.

Want to Learn More?

• Quick Tour: Aggregation Support within Logic Extensions
• Tutorial: Using Aggregation in Logic Extensions
• JD Edwards EnterpriseOne Tools Orchestrator Guide