Query to get approval status per request with approvers by account (MSSQL)
Tested in build MSSQL (14306)
SELECT wo.WORKORDERID AS "Request ID",
wo.TITLE AS "Subject",
wo.DESCRIPTION As "DESCRIPTION",
aau.FIRST_NAME AS "Requester",
dpt1.DEPTNAME AS " Requester Department",
srep.First_Name As "Suprep",
qd.QUEUENAME AS "Group",
longtodate(wo.CREATEDTIME) as "Created Time" ,
ApprovalDetails.EMAIL AS "Approver Email",
longtodate(ApprovalDetails.ACTION_DATE) AS "Date Of Approval",
ApprovalDetails.COMMENTS AS "Approval COMMENTS",
longtodate(wo.RESOLVEDTIME) as "Resolved/Closed Time",
ApprovalStatusDefinition.STATUSNAME AS "Approval Status"
FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
Left Join AAAUser srep ON wos.OwnerID=srep.User_ID
LEFT JOIN SDUser req ON wo.requesterid=req.userid
LEFT JOIN AaaUser requ ON req.userid=requ.user_id
LEFT JOIN UserDepartment UD1 on req.USERID=ud1.userid
LEFT JOIN DepartmentDefinition dpt1 ON ud1.DEPTID=dpt1.DEPTID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN AccountSiteMapping asm ON wo.SITEID=asm.SITEID
LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID
left JOIN ApprovalStageMapping ON wo.WORKORDERID=ApprovalStageMapping.WORKORDERID
LEFT JOIN ApprovalDetails ON ApprovalStageMapping.APPROVAL_STAGEID=ApprovalDetails.APPROVAL_STAGEID
LEFT JOIN ApprovalStage ON ApprovalDetails.APPROVAL_STAGEID=ApprovalStage.APPROVAL_STAGEID
LEFT JOIN approvalstatusdefinition ON ApprovalDetails.STATUSID=ApprovalStatusDefinition.STATUSID
WHERE ApprovalDetails.ISDELETED!=1 and ad.ORG_NAME = 'My Org Inc';
You can rename the account name as per your requirement and execute the query.
New to ADSelfService Plus?
Related Articles
Query to get the Request Approval details
Compatible builds : 14000 to 14200 DB : MSSQL /PGSQL OUTPUT: SELECT wo.WORKORDERID AS "Request ID", wo.title AS "Subject", wo.REQUESTERID AS "Requester ID", LONGTODATE(wo.CREATEDTIME) AS "Requested Date", LONGTODATE(apdet.sent_time) AS "Action ...Query reports to know the Ticket wise Approval status (MSSQL)
Tested in build MSSQL (14306) 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 ...Query to show Approved tickets per Approver (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) 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 ...Service request approvers
This report helps to find the who all the service request approvers in the application. SELECT aaauser.first_name "Approver Name", dpt.deptname "Department", sdo.name "Site", sd.jobtitle "Job Title" FROM srapproverdetails LEFT JOIN aaauser ON ...Request Approval Details
This report is used to view the historical details of Approved and Denied approval processes and their individual steps. We can also use this report to display all details for which an approval decision is pending. To make any changes to a query, ...