Request Management - report to fetch the historical details of approved and denied requests
This report helps to view the historical details of approved and denied requests. This also returns all requests for which approval decision is pending.
SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", dpt.DEPTNAME "Department", wo.TITLE "Subject", ti.FIRST_NAME "Technician", std.STATUSNAME "Request Status", longtodate(wo.CREATEDTIME) "Created Time", asd.stagename "Stage name", LONGTODATE(ApprovalStage.SENT_DATE) "Approval Sent date",LONGTODATE(ApprovalDetails.ACTION_DATE) "Approved Date", ApprovalStatusDefinition.STATUSNAME "Approval Status", ApprovalDetails.EMAIL "Approver Email" FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID 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 LEFT JOIN approvalstagedefinition asd ON asd.STAGEID=ApprovalDetails.STAGEID WHERE (wo.ISPARENT='1') AND wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek>