Query that lists pending approval of a particular service approver:
PGSQL:
SELECT wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",wo.TITLE "Subject",ti.FIRST_NAME "Technician",
longtodate(wo.CREATEDTIME) "Created Time",appsau.first_name "Approver", asd.statusname "Approval Status" FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID 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 RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN ImpactDefinition id ON wos.IMPACTID=id.IMPACTID LEFT JOIN ApprovalStageMapping appsc on appsc.WORKORDERID=wo.WORKORDERID LEFT JOIN ApprovalStage apps on apps.APPROVAL_STAGEID=appsc.APPROVAL_STAGEID LEFT JOIN ApprovalDetails aaad on aaad.APPROVAL_STAGEID=appsc.APPROVAL_STAGEID LEFT JOIN aaauser appsau on appsau.user_id=aaad.approverid LEFT JOIN ApprovalStatusDefinition asd on asd.STATUSID=aaad.STATUSID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE (wot.THD_WOID=wot.WORKORDERID) and (wo.IS_CATALOG_TEMPLATE='TRUE') and asd.statusname='pending approval' and appsau.first_name='Jeniffer Doe' order by 6
td.USERID=ti.USER_ID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN ImpactDefinition id ON wos.IMPACTID=id.IMPACTID LEFT JOIN ApprovalStageMapping appsc on appsc.WORKORDERID=wo.WORKORDERID LEFT JOIN ApprovalStage apps on apps.APPROVAL_STAGEID=appsc.APPROVAL_STAGEID LEFT JOIN ApprovalDetails aaad on aaad.APPROVAL_STAGEID=appsc.APPROVAL_STAGEID LEFT JOIN aaauser appsau on appsau.user_id=aaad.approverid LEFT JOIN ApprovalStatusDefinition asd on asd.STATUSID=aaad.STATUSID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE (wot.THD_WOID=wot.WORKORDERID) and (wo.IS_CATALOG_TEMPLATE='TRUE') and asd.statusname='pending approval' and appsau.first_name='Jeniffer Doe' order by 6
Note: Replace the highlighted name with the approver's name to get the desired results.
Same query excluding those requests which are already approved:
SELECT wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",wo.TITLE "Subject",ti.FIRST_NAME "Technician",
longtodate(wo.CREATEDTIME) "Created Time",appsau.first_name "Approver", asd.statusname "Approval Status" FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID 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 RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN ImpactDefinition id ON wos.IMPACTID=id.IMPACTID LEFT JOIN ApprovalStageMapping appsc on appsc.WORKORDERID=wo.WORKORDERID LEFT JOIN ApprovalStage apps on apps.APPROVAL_STAGEID=appsc.APPROVAL_STAGEID LEFT JOIN ApprovalDetails aaad on aaad.APPROVAL_STAGEID=appsc.APPROVAL_STAGEID LEFT JOIN aaauser appsau on appsau.user_id=aaad.approverid LEFT JOIN ApprovalStatusDefinition asd on asd.STATUSID=aaad.STATUSID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE (wot.THD_WOID=wot.WORKORDERID) and (wo.IS_CATALOG_TEMPLATE='TRUE') and asd.statusname='pending approval' AND wos.appr_statusid!=2 AND appsau.first_name='administrator' order by 6
New to ADSelfService Plus?
Related Articles
Query to list the pending approvals of a Service Approver
PGSQL & MSSQL: SELECT wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",wo.TITLE "Subject",ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time",appsau.first_name "Approver", asd.statusname "Approval Status" FROM ...Query to show Approved tickets per Approver
PGSQL & MSSQL: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", std.STATUSNAME "Request Status", ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time", ApprovalDetails.EMAIL "Approver Email", asd.stagename "Stage", ...Query to list Service Request Approver and Purchase Request Approver
Postgres & SQL Select aau.first_name "Name", po.approverstatus "Purchase Request Approver", sr.approverstatus "Service Request Approver", adef.org_name "Account" from aaauser aau left join POApproverDetails po on po.approver=aau.user_id left join ...Query to retrieve pending approvals from a technician account specific
Database: MSSQL Builds: 10600 and above SELECT wo.WORKORDERID AS "Request ID", ad.org_name as "Account", aaa .first_name as "Technician", ApprovalStatusDefinition.STATUSNAME AS "Approval Status" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON ...Query to show Created, Pending and Completed Requests
PGSQL & MSSQL: SELECT count(wo.workorderid) "Total Created Last Week", count(case when std.ispending='1' THEN 1 ELSE NULL END) "Pending from Last Week", count(case when wo.completedtime >= <from_lastweek> AND wo.completedtime <= <to_lastweek> THEN ...