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 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 ...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 ...Query to show tickets older than 30 days ( MSSQL )
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID "Request ID",ti.FIRST_NAME "Technician",std.STATUSNAME "Request Status",wo.TITLE "Subject",aau.FIRST_NAME AS "Requester Name", pd.PRIORITYNAME as "Priority", adef.ORG_NAME as "Account", ...Query for request attachment details (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Requests with Attachment, its name and path SELECT ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", ti.FIRST_NAME AS "Technician", sa.ATTACHMENTNAME "Attachment ...