select SITEID, ORGID, WONUM, CALNUM, STATUS, CHANGEDATE, NEXT_CHANGEDATE,
--to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,
--to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,
ASSET_UTILITIES.CALENDAR_TIME_HOURS
(ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE) STATUS_HOURS
from (
select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS, S.WOSTATUSID,
S.CHANGEDATE,
nvl(lead(S.CHANGEDATE) over ( order by S.WONUM, S.SITEID, S.CHANGEDATE, S.WOSTATUSID),
sysdate) NEXT_CHANGEDATE
from WORKORDER W
left join ASSET A
on W.ASSETNUM = A.ASSETNUM
and W.SITEID = A.SITEID
left join WOSTATUS S
on W.WONUM = S.WONUM
and W.SITEID = S.SITEID
where W.WONUM in ( '624671','624672','624673','624674')
and W.SITEID = 'LEAD'
) A
order by WONUM, SITEID, CHANGEDATE, WOSTATUSID
|