Query to list the pending approvals of a Service Approver

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 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 LEFT JOIN aaausercontactinfo auci ON appsau.user_id=auci.user_id LEFT JOIN aaacontactinfo aci ON auci.contactinfo_id=aci.contactinfo_id WHERE (wot.THD_WOID=wot.WORKORDERID)  and (wo.IS_CATALOG_TEMPLATE='TRUE') and asd.statusname='pending approval' and aci.emailid='tech6@zoho.com'

1. Data is pulled based on the service approver's email address. Please replace it in the above query in the highlighted text.
‚Äč
2. If the Query output shows a pending approval which is not found in the service request ->Approvals tab, then it means that the approval is deleted by administrator.

          • Related Articles

          • 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 ...
          • 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 to show service catalog template details

            select sd.name "Service Name", rtl.templatename "Service Template Name", rtl.comments "Template description", rtf.description "Description field" from requesttemplate_list rtl LEFT JOIN requesttemplate_fields rtf ON rtl.templateid=rtf.templateid LEFT ...
          • Query to list fields used in each request template.

            Go to Reports-New Query Report and execute this query. POSTGRES select sd.name "Service Catalog Name",rt.templatename "Template name",fc.field_name "Fields" from requesttemplate_list rt left join servicedefinition sd on sd.serviceid = ...