«

»

Aug 13 2011

How Event Rule Dataselection Works in JDE Tools

VN:F [1.9.22_1171]
Rating: 4.0/5 (3 votes cast)

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

Published under the license.

VN:F [1.9.22_1171]
Rating: 4.0/5 (3 votes cast)
How Event Rule Dataselection Works in JDE Tools, 4.0 out of 5 based on 3 ratings
(Visited 1,793 times, 3 visits today)

About the author

Deepesh M Divakaran

Deepesh MD is more of a Gizmo Geek, and a techie.
A very experienced Technical Consultant on Oracle JD Edwards EnterpriseOne, and OneWorld. As a consultant he has worked for wide range of industries, from Manufacturing, Media, Oil & gas etc. and has worked on re-engineering modules, and areas of customization.
With a touch of class and understanding towards what ever he does, he is often regarded by his colleagues as the one stop answer for all Technical/Software related queries.

8 comments

Skip to comment form

  1. Tafsir JDE

    very useful information thank you thanx a lot.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  2. Satish kanth

    Scenario 5
    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 (No)
    Set Selection (BC Date – Requested (DRQJ),,’07/01/2011′,)
    Set Selection (BC Date – Requested (DRQJ),,’07/31/2011′,)

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

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  3. Wayne Huffman

    I have a situation where the Version’s data selection selects on Company, ie RPCO = 00015 and internally, I need to build the Set User Selection statements from values in a UDC for G/L Class Codes as well as Processing Option date ranges. So, for example there are 2 UDC entries for 2 G/L Classes. The statement I need to build would be something like this:

    Where RPCO = 00015 and RPGLC = BASE (UDC Value 1) and RPIVD >= PO From Date and RPIVD = PO From Date and RPIVD <= PO Thru
    Date

    I have set the Set Selection Append Flag to Yes before I go into a loop to read the F0005 and build the Set User Selection statement based on the UDC value, but am not getting the results I need. I assume because it only pulls in the Company selection once, not every time I need to build the OR group. How can I address this?

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    1. Deepesh M Divakaran

      Wayne,
      You can not have a record in any table where Field1 = “X” and Field1 = “Y”, just ‘coz Field1 can not have X and Y value at the same time. For such conditions, you need to put the OR condition.

      VN:F [1.9.22_1171]
      Rating: 0 (from 0 votes)
  4. Rauf

    Can I use multiple value for selection such as Order Type is equal to “OR,O3” ?

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    1. Deepesh M Divakaran

      Rauf,
      Order Type = “OR”,”O3″ is actually
      Order Type = “OR” or Order Type = “O3”. You will have to code accordingly.
      From tools release 8.97, we have something called Set Selection Group, which can help do the above easily. You can find about its usage in the Oracle Docs. I’ll be writing up on the typical usage soon.

      VN:F [1.9.22_1171]
      Rating: 0 (from 0 votes)
  5. Raphael Bueno

    I´d Like to know, if true possible to create Dataselection with “Group By” ???

    Thanks

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  6. gans

    Hi,
    I have the scenario like below:
    ***************************************************************************************************************************
    Set Selection Append Flag()
    If PO szNextStatusFrom is equal to Or PO szNextStatusCodeThru is equal to Or PO szNextStatusFrom is equal to Or PO szNextStatusCodeThru is equal to
    VA rpt_cPONxtStatusBlankFlag_EV01 = “1”
    Set User Selection(BC Status Code – Next (F4211)(NXTR), , “000”, )
    Set User Selection(BC Status Code – Next (F4211)(NXTR), , “000”, )
    Else
    // SAR 7749090
    Set User Selection(BC Status Code – Next (F4211)(NXTR), , PO szNextStatusFrom, )
    Set Selection Append Flag()
    Set User Selection(BC Status Code – Next (F4211)(NXTR), , PO szNextStatusCodeThru, )
    // SAR 7749090 Start
    End If
    // SAR 7749090 end
    Set Selection Append Flag()
    Set User Selection(BC Hold Orders Code (F4201)(HOLD), , , )
    ****************************************************************************************************************************
    why did they used set selection append flag more than one time???
    Having only one set selection append flag is enough right?

    VN:F [1.9.22_1171]
    Rating: 0 (from 0 votes)

Leave a Reply