Query to show requesters who are service and PO approvers

Query to show requesters who are service and PO approvers

PGSQL & MSSQL:

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

          • 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 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", ...
          • Query that lists pending approval of a particular service approver:

            PGSQL: 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 WorkOrder_Threaded wot ...
          • Send Auto reminders to Change Approvers after X days

            Here is the script and kindly follow the steps below to achieve your requirement to send Auto reminders to Change Approvers after X days. For now, the script sends reminder for every 24 and 48 hours. This can be customized in the script. 1.Download ...