However, this query statement would be out of date the next week, requiring the user to adjust the dates.
The new filter displays all work orders with an end date that falls within the seven days previous to it being run.
|
'%LOGGEDINUSERID%' - Returns the currently logged in user's 'Login ID.'
 Example
When user Josh runs the filter:
SELECT * FROM WKORDER WHERE WKORDER.WO_EMP_TY = '%LOGGEDINUSERID%'
it becomes:
SELECT * FROM WKORDER WHERE WKORDER.WO_EMP_TY = 'JOSH'
|
'%LOGGEDINEMPCODE%' - Returns the currently logged in user's 'Employee ID,' which is found in the EM_EMPL_CD field in the WKUEMP table. When used in a filter, the system replaces the reserved word with the Work Employee Code of the currently logged in user.
Requirements - You must own the Work module to use this string. Also, the logged in employee must have his or her Login ID associated with an Employee record in the Work Flow Setup > Employees module.
Example
When user Josh runs the filter:
SELECT * FROM WKORDER WHERE WKORDER.WO_EMP_CD = '%LOGGEDINEMPCODE%'
it becomes:
SELECT * FROM WKORDER WHERE WKORDER.WO_EMP_CD = 'JoshL '
|
'%FISCALYEAR%' - Returns the current fiscal year from the Desktop application's Planning and Budgeting, Fiscal Year module.
Requirements - You must own the Work module to use this string.
Example
On 6/27/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%FISCALYEAR%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '01/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '01/01/2015 12:00 AM'
|
'%CURRENTYEAR%' - Inserts the current calendar year.
Example
On 6/27/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTYEAR%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '2014-01-01' and EFEQUIP.GE_CUR_DT < '2015-01-01'
|
'%CURRENTQUARTER%' - Inserts the current quarter.
Example
On 6/27/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTQUARTER%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.GE_FL_ID = EFFLEET.FL_ID WHERE EFEQUIP.ge_cur_dt >= '04/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/01/2014 12:00 AM'
|
'%CURRENTMONTH%' - Inserts the current month.
Example
On 6/27/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTMONTH%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/01/2014 12:00 AM'
|
'%CURRENTWEEK%' - Looks at the current date and inserts the dates for the Sundays before and after the current date.
Example
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/29/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/06/2014 12:00 AM'
|
'%CURRENTWEEK1%' - Looks at the current date and inserts the dates for the Sundays before and after the current date. (Results are identical to those using the previous reserve word.)
Example
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK1%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/29/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/06/2014 12:00 AM'
|
'%CURRENTWEEK2%' - Looks at the current date and inserts the dates for the Monday in the same calendar week and the next Monday.
Example
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK2%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/30/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/07/2014 12:00 AM'
In this example, the user runs the filter on a Monday; so, the system returns records from the "previous" Monday (12 a.m. that same day, 06/30) to 12 a.m. the following Tuesday,07/07 (which catches all of Monday, 7/6).
|
'%CURRENTWEEK3%' - Looks at the current date and inserts the dates for the Tuesday in the same calendar week and the next Tuesday.
Example
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK3%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '07/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/08/2014 12:00 AM'
|
'%CURRENTWEEK4%' - Looks at the current date and inserts the dates for the Wednesday in the same calendar week and the next Wednesday.
Example
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK4%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '07/02/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/09/2014 12:00 AM'
|
'%CURRENTWEEK5%' - Looks at the current date and inserts the dates for the Thursday in the same calendar week and the next Thursday.
Example
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK5%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '07/03/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/10/2014 12:00 AM'
|
'%CURRENTWEEK6%' - Looks at the current date and inserts the dates for the Friday in the same calendar week and the next Friday.
Example
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK6%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '07/04/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/11/2014 12:00 AM'
|
'%CURRENTWEEK7%' - Looks at the current date and inserts the dates for the Saturday in the same calendar week and the next Saturday.
Example
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK7%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '07/05/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/12/2014 12:00 AM'
|
'%CURRENTDAY%' - Inserts the current 24-hour period. This is a range from 12:00 AM of the current day to 12:00 AM of the next day.
Example
On Friday, 06/27/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTDAY%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/27/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '06/28/2014 12:00 AM'
|
'%PREVIOUSYEAR%' - Looks at the current date and inserts January 1 of the previous year to January 1 of the current year.
Example
On Friday, 6/27/2014, a user runs the following filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%PREVIOUSYEAR%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '01/01/2013 12:00 AM' AND EFEQUIP.ge_cur_dt < '01/01/2014 12:00 AM'
|
'%PREVIOUSMONTH%' - Looks at the current date and inserts the first date of the previous month and the first date of the current month.
Example
On Friday, 6/27/2014, a user runs the following filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%PREVIOUSMONTH%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '05/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '06/01/2014 12:00 AM'
|
'%PREVIOUSWEEK%' - Looks at the current date and inserts the dates of the previous week, Sunday to Sunday.
Example
On Friday, 6/27/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%PREVIOUSWEEK%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/15/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '06/22/2014 12:00 AM'
|
'%PREVIOUSDAY%' - Inserts the day previous to the current day.
Example
On Friday, 6/27/2014, user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%PREVIOUSDAY%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/26/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '06/27/2014 12:00 AM'
|
'%FIRSTHALFOFTHEMONTH%' - Inserts the first half of the month (days 1-15).
Example
On Friday, 6/27/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%FIRSTHALFOFTHEMONTH%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '06/16/2014 12:00 AM'
|
'%SECONDHALFOFTHEMONTH%' - Inserts the second half of the month (days 16-31).
Example
On Friday, 6/27/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%SECONDHALFOFTHEMONTH%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/16/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/01/2014 12:00 AM'
|
'%ONEWEEKAHEAD%' - Inserts the current day, plus seven future days.
Example
On Friday, 6/27/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%ONEWEEKAHEAD%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/27/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/05/2014 12:00 AM'
|
'%TWOWEEKSAHEAD%' - Inserts the plus 14 future days.
Example
On Friday, 6/27/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%TWOWEEKSAHEAD%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/27/2014 12:00 AM'' AND EFEQUIP.ge_cur_dt < ''07/12/2014 12:00 AM'
|
'%ONEWEEKAHEADANDBEFORE%' - Inserts code to encompass all previous time, plus one week in the future. For example, this string could be used to query for all existing open work orders as well as those that are open/due in the next week.
Example
On Friday, 6/27/2014, the user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%ONEWEEKAHEADANDBEFORE%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt <= '07/05/2014 12:00 AM'
|
'%TWOWEEKSAHEADANDBEFORE%' - Inserts code to encompass all previous time, plus two weeks in the future.
Example
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%TWOWEEKSAHEADANDBEFORE%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt <= '07/15/2014 12:00 AM'
|
'%LAST(X,Y)%' - Inserts a range of dates (X) going backward in time, starting a given number of days (Y) prior to the current date.
Example
On Friday, 6/27/2014, a user runs the filter:
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt = '%LAST(30,40)%'
It becomes:
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt >= '4/18/2014' AND WKORDER.wo_mod_dt <= '5/28/2014'
The current date is 6/27. The reserve word goes back 30 days to 5/28 to find the end of the date range. It then goes back 40 days to find the beginning of the date range, 4/18.
|
'%NEXT(X,Y)%' - Inserts a range of dates (X) going forward in time, starting a given number of days (Y) following to the current date.
Example
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt = '%NEXT(0,10)%'
It becomes:
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt >= '6/30/2014' AND WKORDER.wo_mod_dt <= '7/10/2014'
The current date is 6/30. The reserve word goes forward 0 days to find a beginning date of 6/30. It then goes forward 10 days to find an end date of 7/10.
|
'%TODAY%' - Inserts today's date.
Example
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt = '%TODAY%'
It becomes:
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt = '6/30/2014'
|