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 Performed Date",
au.FIRST_NAME AS "APPROVEDBY",
apdet.STATUSID,
appStDef.STATUSNAME AS "Approval Status" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN ApprovalStatusDefinition appStDef ON wos.APPR_STATUSID=appStDef.STATUSID
LEFT JOIN request_approvallevel req_ap ON wo.WORKORDERID=req_ap.entityid
LEFT JOIN approvallevel aplev ON req_ap.levelid=aplev.id
LEFT JOIN approvaldetails apdet ON aplev.id=apdet.approval_level_id
LEFT Join SdUser sd on apdet.APPROVEDBY = sd.USERID
Left Join AaaUser au on sd.USERID=au.USER_ID WHERE (wo.ISPARENT='1')
wo.title AS "Subject",
wo.REQUESTERID AS "Requester ID",
LONGTODATE(wo.CREATEDTIME) AS "Requested Date",
LONGTODATE(apdet.sent_time) AS "Action Performed Date",
au.FIRST_NAME AS "APPROVEDBY",
apdet.STATUSID,
appStDef.STATUSNAME AS "Approval Status" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN ApprovalStatusDefinition appStDef ON wos.APPR_STATUSID=appStDef.STATUSID
LEFT JOIN request_approvallevel req_ap ON wo.WORKORDERID=req_ap.entityid
LEFT JOIN approvallevel aplev ON req_ap.levelid=aplev.id
LEFT JOIN approvaldetails apdet ON aplev.id=apdet.approval_level_id
LEFT Join SdUser sd on apdet.APPROVEDBY = sd.USERID
Left Join AaaUser au on sd.USERID=au.USER_ID WHERE (wo.ISPARENT='1')
New to ADSelfService Plus?
Related Articles
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 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 ...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 retrieve the requests details
Tested in: 14610, 14301 QUERY: SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESPONDEDTIME) AS "Responded Date and Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Date and Time", ...Query to retrieve the Change Details
Tested in: 14504, 14610 QUERY: SELECT chdt.CHANGEID AS "Change ID", cmDef.FIRST_NAME AS "Change Manager", ownaaa.FIRST_NAME AS "Change Owner", approvaldef.STATUSNAME AS "Approval Status", qd.QUEUENAME AS "Group", statusDef.STATUSDISPLAYNAME AS ...