Default queries for the My Work tab

Default queries facilitate the access to work orders assigned to you. You can list the work orders that are assigned to you automatically by setting up a default query. This default query is available in the saved queries list of the Everyplace applications.

You can create default queries to populate the My Work tab when you log on to Everyplace applications. If you are a supervisor, the query must find all work orders assigned to people who have you as their supervisor. If there are multiple levels of supervisors, the query can list work orders for each level of your hierarchy.
The query identifies the reporting structure according to the Hierarchylevels parameter setting you specify:
Hierarchylevels=0
Sets the search to you only.
Hierarchylevels>0
Sets the search for everyone below yourself.
Hierarchylevels=1
Sets the search for everyone that is your direct report.
Hierarchylevels>=0
Sets the search for you and everyone below. You can also remove the parameter from the query to reach this same result.
The assigned labor in such queries must match your username, and you must search for approved and in progress work orders. You must also set the Is Task parameter to N, because you are searching for work orders. If you want to search for tasks, then set this parameter to Y.

These queries search for labor and crew assignments. The work orders for an individual or a crew are sorted based on the scheduled start dates and times.

Table 1. Default queries in SQL
Query purpose SQL coding
Search for approved and in progress work orders for an individual by labor
istask = 0 and ((status in (select value from synonymdomain where 
(domainid='WOSTATUS') and maxvalue in('APPR','INPRG')) and (exists 
(select 1 from assignment where (laborcode = :USER) and (exists 
(select 1 from workorder yy where ((yy.istask = 1 and yy.parent = 
workorder.wonum) or (istask = 0 and yy.wonum = workorder.wonum)) 
and assignment.wonum = yy.wonum and yy.siteid = workorder.siteid) 
and siteid = workorder.siteid)))))
Search for approved and in progress work orders for an individual by crew
istask = 0 and ((status in (select value from synonymdomain where 
(domainid='WOSTATUS') and maxvalue in('APPR','INPRG')) and (exists 
(select 1 from assignment where (amcrew in (select amcrew from 
amcrewlabor where laborcode= :USER)) and (exists (select 1 from 
workorder yy where ((yy.istask = 1 and yy.parent = workorder.wonum ) 
or (istask = 0 and yy.wonum = workorder.wonum)) and assignment.wonum 
= yy.wonum and yy.siteid = workorder.siteid) and siteid = 
workorder.siteid)))))
Search for approved and in progress work orders for an individual by labor or crew
istask = 0 and ((status in (select value from synonymdomain where 
(domainid='WOSTATUS') and maxvalue in('APPR','INPRG')) and (exists 
(select 1 from assignment where ((amcrew in (select amcrew from 
amcrewlabor where laborcode= :USER)) or (laborcode = :USER)) and 
(exists (select 1 from workorder yy where ((yy.istask = 1 and 
yy.parent = workorder.wonum ) or (istask = 0 and yy.wonum = 
workorder.wonum)) and assignment.wonum = yy.wonum and yy.siteid = 
workorder.siteid) and siteid = workorder.siteid)))))
Search for approved, in progress, and complete work orders for a supervisor by labor
istask = 0 and ((status in (select value from synonymdomain where 
(domainid='WOSTATUS') and maxvalue in ('APPR','INPRG',’COMP’)) and 
(exists (select 1 from assignment where (laborcode in(select 
personid from personancestor where hierarchylevels>0 and ancestor = 
:USER ) and (exists (select 1 from workorder yy where ((yy.istask = 
1 and yy.parent = workorder.wonum ) or (istask = 0 and yy.wonum = 
workorder.wonum)) and assignment.wonum = yy.wonum and yy.siteid = 
workorder.siteid) and siteid = workorder.siteid))))))
Search for approved and in progress work orders for a supervisor by crew
istask = 0 and ((status in (select value from synonymdomain where 
(domainid='WOSTATUS') and maxvalue in ('APPR','INPRG')) and (exists 
(select 1 from assignment where (amcrew in (select amcrew from 
amcrewlabor where laborcode in(select personid from personancestor 
where hierarchylevels>0 and ancestor = :USER )) and (exists (select 
1 from workorder yy where ((yy.istask = 1 and yy.parent = 
workorder.wonum ) or (istask = 0 and yy.wonum = workorder.wonum)) 
and assignment.wonum = yy.wonum and yy.siteid = workorder.siteid) 
and siteid = workorder.siteid))))))
Search for approved and in progress work orders for a supervisor by labor or crew
istask = 0 and ((status in (select value from synonymdomain where 
(domainid='WOSTATUS') and maxvalue in ('APPR','INPRG')) and (exists 
(select 1 from assignment where ((amcrew in (select amcrew from 
amcrewlabor where laborcode in(select personid from personancestor 
where hierarchylevels>0 and ancestor = :USER ))  or laborcode in 
(select personid from personancestor where hierarchylevels>0 and 
ancestor = :USER )) and (exists (select 1 from workorder yy where 
((yy.istask = 1 and yy.parent = workorder.wonum ) or (istask = 0 
and yy.wonum = workorder.wonum)) and assignment.wonum = yy.wonum 
and yy.siteid = workorder.siteid) and siteid = workorder.siteid))))))
Note that the performance of the My Work tab queries might improve if you apply indexes to the following fields and tables:
  • Index the ISTASK, STATUS, WONUM, SITEID fields in the WORKORDER table
  • Index the LABORCODE field in the AMCREW table
  • Index the AMCREW field in the ASSIGNMENT table
  • Index the LABORCODE field in the ASSIGNMENT table


Feedback