So I looked back at this original post and the objective is to understand what as a Scheduler, (a Planner doesn't care per-se who does the work as long as the correct skill is applied,) a user would see after you have assigned work, correct?
So it occurs to me that a report is really the only way of seeing this data without having to perform many and multiple queries. A user/ technician usually has a suprervisor. So the supervisor would have direct reports and those persons would be made into a sub-report and you would simply input the name of the supervisor to execute the report. This really is the most efficient way of managing the work effort. I think the query you have (while it works ) is over thought. Perhaps not. perhaps it is the best business solution for you the way you operate. But to get to the objective you want you would want to break that down into a manageable report, so you could iterate through a list of twenty or fifty or a hundred, or five hundred, or a thousand persons.
------------------------------
Bradley K. Downing , MBA
Solutions Engineer
IBM
Bakersfield CA
------------------------------
Original Message:
Sent: Mon September 27, 2021 09:38 PM
From: User1971
Subject: Saved query: Parameter via dialog window?
If a person had an appetite for messy code, then they could put a WITH clause in a subquery, and store a single instance of the PERSONID there.
workorderid IN (WITH c AS (SELECT '1234' AS p FROM DUAL)
SELECT workorderid
FROM maximo.workorder
WHERE woclass = 'WORKORDER'
AND worktype <> 'INSP'
AND status IN
(SELECT VALUE
FROM maximo.synonymdomain
WHERE domainid = 'WOSTATUS'
AND maxvalue IN ('INPRG'))
AND ( (EXISTS
(SELECT 1
FROM maximo.assignment
WHERE assignment.wonum =
workorder.wonum
AND siteid = workorder.siteid
AND ( amcrew IN
(SELECT amcrew
FROM maximo.amcrewlabor
WHERE laborcode =
(SELECT laborcode
FROM maximo.labor
WHERE personid =
(SELECT p FROM c)))
OR laborcode =
(SELECT laborcode
FROM maximo.labor
WHERE personid =
(SELECT p FROM c))
OR laborcode IN
(SELECT laborcode
FROM maximo.amcrewlabor
WHERE amcrew IN
(SELECT amcrew
FROM maximo.amcrewlabor
WHERE laborcode =
(SELECT laborcode
FROM maximo.labor
WHERE personid =
(SELECT p FROM c)))))))
OR (wonum IN
(SELECT parent
FROM maximo.workorder
WHERE woclass = 'ACTIVITY'
AND (EXISTS
(SELECT 1
FROM maximo.assignment
WHERE assignment.wonum =
workorder.wonum
AND siteid =
workorder.siteid
AND status IN
('ASSIGNED',
'STARTED')
AND ( amcrew IN
(SELECT amcrew
FROM maximo.amcrewlabor
WHERE laborcode =
(SELECT laborcode
FROM maximo.labor
WHERE personid =
(SELECT p FROM c)))
OR laborcode =
(SELECT laborcode
FROM maximo.labor
WHERE personid =
(SELECT p FROM c))
OR laborcode IN
(SELECT laborcode
FROM maximo.amcrewlabor
WHERE amcrew IN
(SELECT amcrew
FROM maximo.amcrewlabor
WHERE laborcode =
(SELECT laborcode
FROM maximo.labor
WHERE personid =
(SELECT p FROM c)))))))))))
Yikes. There's got to be a better way than this (other than a report -- unfortunately, a report doesn't let me filter the WO list view)
For the record, I did not write this query.
#Maximo
#AssetandFacilitiesManagement