Query to get the Request Approval details

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')

                New to ADManager Plus?

                  New to ADSelfService Plus?

                    • Related Articles

                    • Login Frequency Query Report

                      The below report would help us find the last logged in time of the users in SCP 11.0 SELECT AaaUser.FIRST_NAME "Technician",        MAX(AaaLogin.NAME) "LoginName",        MAX(AaaContactInfo.EMAILID) "Email",        MAX(AaaAccSession.USER_HOST) "IP ...
                    • Script to Auto close Request when the status is Resolved for a particular number of days

                      For version 11 and above: -> In version 11, we only have the option to set the request Auto-close for a max of 10 days -> The below script will help you to extend the auto-closure time Execution steps: 1. Go to Reports--> New Query Report and run the ...
                    • Followers report

                      You can use the below query to get the Followers and the request details. Go to Reports --> New Query Report select wo.workorderid "Request Id",wo.title "Subject",aaau.first_name "Followers",aaauc.emailid "Followers email" from workorder_cclist woc ...
                    • Query report for Time Spent for First response in Version 11.0 (Postgres)

                      SELECT slad.SLANAME AS "Request SLA", wo.WORKORDERID AS "Request ID", longtodate(wo.CREATEDTIME) AS "Created Time", slad.SLANAME AS "Request SLA", longtodate(wo.FR_DUETIME) AS "Request first response due time",round(wo.RESPONSE_TIMESPENT / 60000) AS ...
                    • Set Request Id to start from a particular number - Version 11.0.

                      Regarding the First Portal (General), you can use the below steps to change the Request Id to start from 136000. 1.The below Select Query will give the last active ticket ID. Select * from SeqGenState where seqname='WorkOrder.WORKORDERID'; 2.Then, ...