How Event Rule Dataselection Works in JDE Tools

*The article is rated Must read for Beginners/Novice

There has been many a confusion with JDE freshers, on how to use the Internal Data selection, or called as ER Data selection and how to use them.

Following are our assumptions for the scenarios (for REPORT DESIGN AID (RDA)) –

  • There’s a Processing Option which takes inputs of Date range (F4201.DRQJ) for internal data selection.

Scenario 1

Suppose we need to select all Sales orders (F4201) entered between (DRQJ=) 7/1/2011 and 7/31/2011

Set Selection (BC Date - Requested (DRQJ),<Greater Than and Equal To>,'07/01/2011',<AND>)
Set Selection (BC Date - Requested (DRQJ),<Less Than and Equal To>,'07/31/2011',<AND>)

Generated SQL  –
SELECT * FROM F4201 WHERE (DRQJ>='07/01/2011' AND DRQJ<='07/31/2011')


Scenario 2

Now, if the UBE already has an External Data selection of DCTO=’SI’,’SO’ our earlier data selection of DRQJ (in processing options) between 7/1/2011 and 7/31/2011.

Set Selection Append flag (Yes)
Set Selection (BC Date - Requested (DRQJ),,'07/01/2011',)
Set Selection (BC Date - Requested (DRQJ),<Less Than and Equal To>,'07/31/2011',<AND>)

Generated SQL  –
SELECT * FROM F4201 WHERE (DCTO IN ('SI','SO')) AND (DRQJ>='07/01/2011' AND DRQJ<='07/31/2011')

Explanation –
The Selection Append Flag specifies if to append the extra data selection after the External Data selection of the UBE. If this is set to NO, only the data select statements after that is applied.


Scenario 3

Let’s take a scenario where you have an external data selection of DCTO = ‘SF’.
Our situation demands that if the Processing option range is entered, then select those in DRQJ range and all the ‘SF’ orders.

Set Selection Append flag (Yes)
Set Selection (BC Date - Requested (DRQJ),<Greater Than and Equal To>,'07/01/2011',<OR>)
Set Selection (BC Date - Requested (DRQJ),<Less Than and Equal To>,'07/31/2011',<AND>)

Generated SQL  –
SELECT * FROM F4201 WHERE (DCTO='SF') OR (DRQJ>='07/01/2011' AND DRQJ<='07/31/2011')

Explanation –
How did that work? The basic understanding how to form your AND / OR conditions is that, the conditions you put at the End of your statements is what is placed before that statement. In the above scenario we had the OR condition in the first statement. The condition is placed before the Set Selection statement.


Scenario 4

Let’s take a scenario where you have an external data selection of DRQJ in range of 01/01/2011 and 01/31/2011.
Our situation demands that if the Processing option range is entered, then include that DRQJ range too along with the external data selection.

Set Selection Append flag (Yes)
Set Selection (BC Date - Requested (DRQJ),<Greater Than and Equal To>,'07/01/2011',<OR>)
Set Selection (BC Date - Requested (DRQJ),<Less Than and Equal To>,'07/31/2011',<AND>)

Generated SQL  –
SELECT * FROM F4201 WHERE (DRQJ BETWEEN '01/01/2011' AND '01/31/2011') OR (DRQJ>='07/01/2011' AND DRQJ<='07/31/2011')

Explanation –
How did that work? The basic understanding how to form your AND / OR conditions is that, the conditions you put at the End of your statements is what is placed before that statement. In the above scenario we had the OR condition in the first statement. The condition is placed before the Set Selection statement.


Scenario 5

Now suppose the above same scenario of DRQJ in range of 01/01/2011 and 01/31/2011 was given in Processing options, and along with it you also want to select only the order types SI and SO (also set in processing options). We have a tricky situation here. the Issue being that JD Edwards does not support parenthesis or does not allow you to segregate the SQL where clause to prioritize the conditions.

Lets think of the following code if you feel its its correct.

Set Selection Append flag (Yes)
Set Selection (BC Date - Requested (DRQJ),<Greater Than and Equal To>,'07/01/2011',<AND>)
Set Selection (BC Date - Requested (DRQJ),<Less Than and Equal To>,'07/31/2011',<AND>)
Set Selection (BC Order Type (DCTO),<Equal To>,'SI',<AND>)
Set Selection (BC Order Type (DCTO),<Equal To>,'SO',<OR>)

Generated SQL  –
SELECT * FROM F4201 WHERE (DRQJ>='07/01/2011' AND DRQJ<='07/31/2011' AND DCTO='SI' OR DCTO='SO')

Do you think this will work? NO it does not.
The OR condition at the end does not have a priority or parenthesis and so messes up the statement we want to build. So how do we tackle this?

This is how we do it in JD Edwards……!

Set Selection Append flag (Yes)
Set Selection (BC Date - Requested (DRQJ),<Greater Than and Equal To>,'07/01/2011',<AND>)
Set Selection (BC Order Type (DCTO),<Equal To>,'SI',<AND>)
Set Selection (BC Order Type (DCTO),<Equal To>,'SO',<OR>)
Set Selection (BC Date - Requested (DRQJ),<Less Than and Equal To>,'07/31/2011',<AND>)
Set Selection (BC Order Type (DCTO),<Equal To>,'SI',<AND>)
Set Selection (BC Order Type (DCTO),<Equal To>,'SO',<OR>)

Generated SQL  –
SELECT * FROM F4201
WHERE (DRQJ >= '07/01/2011' AND DCTO='SI' OR DCTO='SO' AND
DRQJ <='07/31/2011' AND DCTO='SI' OR DCTO='SO')

Explanation –
Yes now, if you look at the SQL generated, you can see how the selection works. BTW, SQL conditions work from right to left. i.e. the right most condition executes first, followed by the conditions on its left.

Do go ahead and comment out with questions if any.

3 thoughts on “How Event Rule Dataselection Works in JDE Tools

  1. Niranjan Reply

    Dear Deepesh,
    How are you Doing?
    Im working with UBE to Formulate Sales Delivery Percentage on MTD Quantity.
    For this I am extracting selected fields with calculated Date Difference between Order and Invoice Date
    to Temp Table named(F59DTIME) based on Selection.
    After that I am trying to calculate percentage based on subtotal by Each Date Difference & BussinessUNit over MTD Total Quanityby BusinessUnit using this table.
    I did the above as shown below
    VA rpt_Identifier2ndItem <-TK 2nd Item Number
    0020 ! End While
    0021 ! RV Day 02 = VA rpt_Day_Qty
    0022 ! VA rpt_rQty02 = RV Day 02
    0023 ! VA rpt_Qty02 = round(([VA rpt_rQty02]/[VA rpt_TotalMCUQuantity]),2)*100
    0024 ! If VA rpt_Diff_Days is equal to "2"
    0025 ! F59DTIME.Fetch Next
    VA rpt_Identifier2ndItem <-TK 2nd Item Number
    0026 ! If VA rpt_Diff_Days is equal to "2"
    0027 ! While SV File_IO_Status is equal to CO SUCCESS
    0028 ! VA rpt_Day_Qty = [VA rpt_Day_Qty]+[PC Quantity Shipped (F59DTIME) (SOQS)]
    0029 ! F59DTIME.Fetch Next

    VA rpt_Identifier2ndItem <-TK 2nd Item Number
    0030 ! End While
    0031 ! End If
    0032 ! RV Day 03 = VA rpt_Day_Qty
    0033 ! VA rpt_rQty03 = RV Day 03
    0034 ! VA rpt_Qty03 = round(([VA rpt_rQty03]/[VA rpt_TotalMCUQuantity]),2)*100
    0035 ! End If
    0036 ! F59DTIME.Select
    VA rpt_CostCenter = TK Business Unit -Home
    VA rpt_Diff_Days = TK Period Number -Accounts Receivable
    0037 ! If VA rpt_Diff_Days is equal to "3"
    0038 ! F59DTIME.Fetch Next
    PC Quantity Shipped (F59DTIME) <-TK Quantity Shipped
    I Tried Debugging and i found Quantity per date difference is accumulating a constant Value inside While Loop.
    Your valuable Support Really Appreciatable
    Thanks & Regards

  2. Rahul Vijay Reply

    Hi Dipesh,

    I am very new in JDE now I have below SQL statement that I want to put in E1 code can you help me?

    NSERT INTO F55XXX SELECT SDMCU, SDITM, SDLITM, COUNT(SDITM) LKM, SUM(SDSOQS) KPL FROM F4211, F55365 WHERE SDMCU IN (‘ FCV’, ‘ FVA’, ‘ FTN’, ‘ FOU’, ‘ FCT’) AND SDSOQS > 0 AND SDDCTO IN (‘SA’,’SD’,’SO’,’SV’,’TC’,’TP’,’TU’,’T1′,’T8′,’SF’) AND SDADDJ BETWEEN TDPMB AND TDPME GROUP BY SDMCU, SDITM, SDLITM ;

    • Deepesh M Divakaran Post authorReply

      I assume this is a UBE where you are performing the above. You will heed to have a Section on F4211, F55365, with the dataselection as you need. Within the DO Section event of the Section, just insert the values into your required table.

Leave a Reply

Your email address will not be published. Required fields are marked *