Welcome Guest Login SignUp

Home

Technical

Functional

CNC

AS400

DB2

    Citrix

JDE News

Live Radio

Feedback

JDE Forum Comments/Reviews/Queries
1958 day(s) ago   #173
Name: Nick
Category: Technical
Location: India
Date: 2015-01-14 06:07:29
Replies(1)

Status:
  #173

How JDE Event Rule Data Selection Works Along with External Data Selection

Some time it gets confusing when need to use Data Selection inside Event Rules (ER) for UBE which works on top of external data selection supplied at run time.
Assuming values/range of values for ER data selection is passed from Processing Options.

Following are some scenarios with details about how it looks when converted to SQL statements -

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),,`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`)

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),,`07/31/2011`,)

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),,`07/01/2011`,)
Set Selection (BC Date - Requested (DRQJ),,`07/31/2011`,)

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),,`07/01/2011`,)
Set Selection (BC Date - Requested (DRQJ),,`07/31/2011`,)

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),,`07/01/2011`,)
Set Selection (BC Date - Requested (DRQJ),,`07/31/2011`,)
Set Selection (BC Order Type (DCTO),,`SI`,)
Set Selection (BC Order Type (DCTO),,`SO`,)

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),,`07/01/2011`,)
Set Selection (BC Order Type (DCTO),,`SI`,)
Set Selection (BC Order Type (DCTO),,`SO`,)
Set Selection (BC Date - Requested (DRQJ),,`07/31/2011`,)
Set Selection (BC Order Type (DCTO),,`SI`,)
Set Selection (BC Order Type (DCTO),,`SO`,)

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.

Reference - JDESource
1954 day(s) ago   #174
Name: tong
Category: Technical
Location: Australia
Date: 2015-01-18 01:58:24
Replies(1)

Status:
  #174

How JDE Event Rule Data Selection Works Along with External Data Selection

Good effort indeed. I am going to refer to it so often. Thanks.
Post Comments/Queries
Email:*
Name:*
Details:*
 
   
* Required Entry Field(s).  * Your comments may subject to varification.  * Your email ID will not appear in the forum.
 
Disclaimer: Most of the posts in this blog cater solutions/suggestions/workaround to issues for specific tools release or JDE E1 version and are just information only. Please be carefule while applying it in your environment. JDEthread will not be responsible for any data loss or spec corruption (if any).
Copyright © 2010 - 2020 JDEthread.in