Query that lists pending approval of a particular service approver:

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

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
          • 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 show requesters who are service and PO approvers

            PGSQL & MSSQL: select ad.org_name "Account Name", sdo.name "Site Name", au.first_name "First Name", aci.emailid "Email ID",aci.landline "Telephone", aci.mobile "Mobile",po.approverstatus "Purchase Request Approver", sr.approverstatus "Service Request ...
          • Query reports to know the Ticket wise Approval status

            1. Login to SDP MSP as administrator 2. Execute this from SDP MSP application -> Reports -> New Query report SELECT wo.WORKORDERID AS "Request ID",  wo.TITLE AS "Subject",  srep.First_Name As "Suprep",   qd.QUEUENAME AS "Group", ...