Query to show requesters who are service and PO approvers

Query to show requesters who are service and PO approvers


select ad.org_name "Account Name", sdo.name "Site Name", au.first_name "First Name", aci.emailid "Email ID",aci.landline "Telephone", aci.mobile "Mobile",po.approverstatus "Purchase Request Approver", sr.approverstatus "Service Request Approver" from aaauser au 
inner join sduser sdu on au.user_id = sdu.userid 
inner join requester r on r.ciid = sdu.ciid 
inner join ci ci on ci.ciid = r.ciid 
left join sdorganization sdo on ci.siteid = sdo.org_id 
left join accountsitemapping asm on sdo.org_id=asm.siteid 
left join accountdefinition ad on ad.org_id=asm.accountid 
left join aaausercontactinfo auci on auci.user_id = sdu.userid 
left join aaacontactinfo aci on aci.contactinfo_id = auci.contactinfo_id
left join sduserprofile sduprof on sduprof.userid=sdu.userid
left join POApproverDetails po on po.approver=au.user_id
left join SRApproverDetails sr on sr.APPROVER=au.USER_ID where sdu.status='ACTIVE' AND sr.APPROVERSTATUS='True' or po.APPROVERSTATUS='True' group by 1,2,3,4,5,6,7,8 ORDER BY 1

      New to ADSelfService Plus?


            • Related Articles

            • Query to list Service Request Approver and Purchase Request Approver

              Postgres & SQL Select aau.first_name "Name", po.approverstatus "Purchase Request Approver", sr.approverstatus "Service Request Approver", adef.org_name "Account" from aaauser aau left join POApproverDetails po on po.approver=aau.user_id left join ...
            • Query to show solution details with approver and associated requests

              MSSQL: SELECT solution.solutionid "Solution ID" , Ad.org_name "Account", max(Solution.TITLE) "Solution Title", max(au.first_name) "Solution Approver", max(KB_Topics.TOPICNAME) "Solution Topic", max(Solution_Keywords.keyword) "Keywords", ...
            • Query to show PO associated assets with Invoice details

              SELECT po.Purchaseorderid "PO ID", MAX(po.poname) "PO Name", Max(ci.ciname) "CI Name", MAX(systeminfo.SERVICETAG) "Service Tag", MAX(systeminfo.MANUFACTURER) "Manufacturer", MAX(systeminfo.MODEL) "Model", max(paao.NAME) "Vendor Name", ...
            • Query to list the pending approvals of a Service Approver

              PGSQL & MSSQL: SELECT wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",wo.TITLE "Subject",ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time",appsau.first_name "Approver", asd.statusname "Approval Status" FROM ...
            • Query to show Approved tickets per Approver

              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 "Approver Email", asd.stagename "Stage", ...