This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
I apologize in advance if this has already been asked. I tried to search for it, but couldn't find it.
Could someone help me write a query that will pull all Work Orders that have been approved, then changed to an unapproved status, and then re-approved again? This is something that many users are our company wish to keep a close eye on (as it's really something that they shouldn't do, unless it's an emergency, really). I just can't seem to figure out how to query the WOSTATUS table to find WOs that have been reapproved multiple times.
I would need a query that will show WOs that were once approved and still in an unapproved status currently and then another query that would show WOs that were unapproved; however, are currently in an approved status.
For your reference, these are the Status we are mainly looking at:
Unapproved = WAPPR, PLANNED, WPLAN and WMATL
Approved = APPR, SCHED
I would need a query that will show WOs that were once approved and still in an unapproved status currently - try the one below.
((woclass = 'WORKORDER' or woclass = 'ACTIVITY')
and historyflag = 0
and status in ('WAPPR','PLANNED','WPLAN','WMATL')
and wonum in (select wonum from wostatus
where status in ('APPR', 'SCHED'))
and istask = 0
I will try to squeeze in few minutes tomorrow to work on the other one for you.
Pat Morrow
pmorrow8@yahoo.com
On Tuesday, June 3, 2014 3:37 PM, "jackie20062003@yahoo.com [MAXIMO]" <MAXIMO@yahoogroups.com> wrote:
I apologize in advance if this has already been asked. I tried to search for it, but couldn't find it.
Could someone help me write a query that will pull all Work Orders that have been approved, then changed to an unapproved status, and then re-approved again? This is something that many users are our company wish to keep a close eye on (as it's really something that they shouldn't do, unless it's an emergency, really). I just can't seem to figure out how to query the WOSTATUS table to find WOs that have been reapproved multiple times.
I would need a query that will show WOs that were once approved and still in an unapproved status currently and then another query that would show WOs that were unapproved; however, are currently in an approved status.
For your reference, these are the Status we are mainly looking at:
Unapproved = WAPPR, PLANNED, WPLAN and WMATL
Approved = APPR, SCHED
Thank you :)
Here's what I came up with, but not sure if it's 100% accurate or the most efficient:
--WOs that were unapproved and STILL remain in an unapproved status
select a.wonum, a.status, a.changeby, max(a.changedate) back_status_date, b.status current_wo_status
from WOSTATUS a, workorder b, wostatus c
where a.WONUM = b.WONUM and a.SITEID = b.SITEID
and c.WONUM = b.WONUM and a.SITEID = c.SITEID
and a.status in ('WAPPR','PLANNED','WPLAN','WMATL')
and c.status in ('APPR','SCHED')
and b.status in ('WAPPR','PLANNED','WPLAN','WMATL')
and a.CHANGEDATE > c.CHANGEDATE
[Entered in some company specific data to narrow down my results]
group by a.wonum, a.status, a.changeby, a.changedate, b.status;
--WOs that were unapproved and were reapproved
select a.wonum, a.status, a.changeby, max(a.changedate) back_status_date, b.status current_wo_status
from WOSTATUS a, workorder b, wostatus c
where a.WONUM = b.WONUM and a.SITEID = b.SITEID
and c.WONUM = b.WONUM and a.SITEID = c.SITEID
and a.status in ('WAPPR','PLANNED','WPLAN','WMATL')
and c.status in ('APPR','SCHED')
and b.status not in ('WAPPR','PLANNED','WPLAN','WMATL')
and a.CHANGEDATE > c.CHANGEDATE
[Entered in some company specific data to narrow down my results]
group by a.wonum, a.status, a.changeby, a.changedate, b.status;
I forgot to mention, I also need to capture the "culprit" that changed the status of an approved WO back to WAPPR, WPLAN, WMATL or PLANNED. :(
istask = 0 and woclass = 'WORKORDER' and
(
(
status in ('WAPPR','PLANNED','WPLAN','WMATL') and exists
(select null from wostatus where status in ('APPR', 'SCHED') and wonum = workorder.wonum and siteid = workorder.siteid)
)
or
(
status in ('APPR', 'SCHED') and
(
1 <
(select COUNT(*) from wostatus where status = 'APPR' and wonum = workorder.wonum and siteid = workorder.siteid)
or
1 < (select COUNT(*) from wostatus where status = 'SCHED' and wonum = workorder.wonum and siteid = workorder.siteid)
)
)
)
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com]
Sent: 04 June 2014 02:53
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] Re: Work Orders that have been "back statused"
Thank you :)
Here's what I came up with, but not sure if it's 100% accurate or the most efficient:
--WOs that were unapproved and STILL remain in an unapproved status
select a.wonum, a.status, a.changeby, max(a.changedate) back_status_date, b.status current_wo_status
from WOSTATUS a, workorder b, wostatus c
where a.WONUM = b.WONUM and a.SITEID = b.SITEID
and c.WONUM = b.WONUM and a.SITEID = c.SITEID
and a.status in ('WAPPR','PLANNED','WPLAN','WMATL')
and c.status in ('APPR','SCHED')
and b.status in ('WAPPR','PLANNED','WPLAN','WMATL')
and a.CHANGEDATE > c.CHANGEDATE
[Entered in some company specific data to narrow down my results]
group by a.wonum, a.status, a.changeby, a.changedate, b.status;
--WOs that were unapproved and were reapproved
select a.wonum, a.status, a.changeby, max(a.cha! ngedate) back_status_date, b.status current_wo_status
from WOSTATUS a, workorder b, wostatus c
where a.WONUM = b.WONUM and a.SITEID = b.SITEID
and c.WONUM = b.WONUM and a.SITEID = c.SITEID
and a.status in ('WAPPR','PLANNED','WPLAN','WMATL')
and c.status in ('APPR','SCHED')
and b.status not in ('WAPPR','PLANNED','WPLAN','WMATL')
and a.CHANGEDATE > c.CHANGEDATE
[Entered in some company specific data to narrow down my results]
group by a.wonum, a.status, a.changeby, a.changedate, b.status;
I forgot to mention, I also need to capture the "culprit" that changed the status of an approved WO back to WAPPR, WPLAN, WMATL or PLANNED. :(
GDF SUEZ E&P UK Ltd (Company Number 3386464), registered in England and Wales with a registered office address at: 40 Holborn Viaduct, London, EC1N 2PB.
**************************************************************************************************************
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the system manager.
**************************************************************************************************************