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 ...How to implement dynamic request approval using FAFR and custom triggers.
This is a sample script written in Python to handle Conditional Approvals for Incident \ Service Requests, through Custom Triggers using Field and Form Rules. One of the advantages of using this script is that there is no modification of the script ...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, ...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 ...