Request CLOSURECODEID getting cleared out for some Closed requests?
It looks like the Closure Code (CLOSURECODEID field on WorkOrderStates table) may be getting wiped out for some of our Closed requests.
Has anyone else seen anything like that?
For example, one of our requests that shows a Status of 'Closed' on the Request screen also shows a Request Closure Code of 'Not Assigned'. Yet I can see on the History tab that the request was 'Resolved' by one of our Technicians and immediately had a value of 'Resolved/Fulfilled' selected for Request Closure Code. About 20 minutes later, the original Requester actually Closed it, I believe via the link supplied with the email notification they received as a result of the request being resolved. No further activity in History after that.
When I run the SQL query below, it currently brings back a total of 1,295 requests with a Request Status of 'Closed'. 685 of those have the CLOSURECOIDEID populated as expected, but the rest have a NULL value for CLOSURECODEID.
Of the ones with a NULL value, I noticed that 500 have Close Comments of 'Aged Request was Closed.', which I believe are requests that were resolved by a Technician, but then closed automatically by the system as a result of not being manually closed by the original Requester. Does the automatic close wipe out the Closure Code originally entered by the Technician at the time of resolving the request?
That still leaves 110 other requests that I believe would have had a Closure Code selected by the Technician, but where that code was apparently subsequently cleared out. Is there anything that would cause that?
Here's the SQL that I'm running to look at these:
SELECT
wo.WORKORDERID "Request ID",
wo.TITLE "Subject",
aau.FIRST_NAME "Requester",
ti.FIRST_NAME "Technician",
std.STATUSNAME "Request Status",
wos.CLOSURECODEID,
rcode.NAME "Request Closure Code",
wos.close_comments "Close comments",
wos.CLOSURECOMMENTS "Request Closure Comments"
FROM WorkOrder wo
LEFT JOIN SDUser sdu
ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau
ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderStates wos
ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td
ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti
ON td.USERID=ti.USER_ID
LEFT JOIN RequestClosureCode rcode
ON wos.CLOSURECODEID=rcode.CLOSURECODEID
LEFT JOIN StatusDefinition std
ON wos.STATUSID=std.STATUSID
WHERE
(wo.ISPARENT='1')
AND wo.WORKORDERID >= 200
AND std.STATUSNAME = 'Closed'
ORDER BY
std.STATUSNAME,
CASE
WHEN wos.CLOSE_COMMENTS = 'Aged Request was Closed.' THEN 1
WHEN wos.CLOSE_COMMENTS is NULL THEN 2
ELSE 3
END,
wos.CLOSURECODEID,
wos.close_comments,
wos.CLOSURECOMMENTS,
wo.WORKORDERID
We're running SDP Version 9.3, Build 9335 on MS SQLServer.
Has anyone else seen anything like that?
For example, one of our requests that shows a Status of 'Closed' on the Request screen also shows a Request Closure Code of 'Not Assigned'. Yet I can see on the History tab that the request was 'Resolved' by one of our Technicians and immediately had a value of 'Resolved/Fulfilled' selected for Request Closure Code. About 20 minutes later, the original Requester actually Closed it, I believe via the link supplied with the email notification they received as a result of the request being resolved. No further activity in History after that.
When I run the SQL query below, it currently brings back a total of 1,295 requests with a Request Status of 'Closed'. 685 of those have the CLOSURECOIDEID populated as expected, but the rest have a NULL value for CLOSURECODEID.
Of the ones with a NULL value, I noticed that 500 have Close Comments of 'Aged Request was Closed.', which I believe are requests that were resolved by a Technician, but then closed automatically by the system as a result of not being manually closed by the original Requester. Does the automatic close wipe out the Closure Code originally entered by the Technician at the time of resolving the request?
That still leaves 110 other requests that I believe would have had a Closure Code selected by the Technician, but where that code was apparently subsequently cleared out. Is there anything that would cause that?
Here's the SQL that I'm running to look at these:
SELECT
wo.WORKORDERID "Request ID",
wo.TITLE "Subject",
aau.FIRST_NAME "Requester",
ti.FIRST_NAME "Technician",
std.STATUSNAME "Request Status",
wos.CLOSURECODEID,
rcode.NAME "Request Closure Code",
wos.close_comments "Close comments",
wos.CLOSURECOMMENTS "Request Closure Comments"
FROM WorkOrder wo
LEFT JOIN SDUser sdu
ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau
ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderStates wos
ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td
ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti
ON td.USERID=ti.USER_ID
LEFT JOIN RequestClosureCode rcode
ON wos.CLOSURECODEID=rcode.CLOSURECODEID
LEFT JOIN StatusDefinition std
ON wos.STATUSID=std.STATUSID
WHERE
(wo.ISPARENT='1')
AND wo.WORKORDERID >= 200
AND std.STATUSNAME = 'Closed'
ORDER BY
std.STATUSNAME,
CASE
WHEN wos.CLOSE_COMMENTS = 'Aged Request was Closed.' THEN 1
WHEN wos.CLOSE_COMMENTS is NULL THEN 2
ELSE 3
END,
wos.CLOSURECODEID,
wos.close_comments,
wos.CLOSURECOMMENTS,
wo.WORKORDERID
We're running SDP Version 9.3, Build 9335 on MS SQLServer.