Close requests with pending approval for more than 10 days
Requirement: Close older requests with pending approvals.
UseCase: Close all requests for which approvals have been sent but no action has been taken in more than 10 days.
Please follow the below steps.
- Goto Reports > New Query Report > execute the query and save it with a name.
- MSSQL
- SELECT max(wo.helpdeskid) "portalid" ,wo.WORKORDERID "workorderid" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN ApprovalStatusDefinition appStDef ON wos.APPR_STATUSID=appStDef.STATUSID 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 appStDef.STATUSNAME like '%pending approval%' and ApprovalDetails.ISDELETED!='1' and std.ispending='1' and DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(ApprovalStage.SENT_DATE/1000),'1970-01-01 00:00:00'),GETDATE()) >10 group by wo.WORKORDERID
- PGSQL
- SELECT max(wo.helpdeskid) "portalid", wo.WORKORDERID "workorderid" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN ApprovalStatusDefinition appStDef ON wos.APPR_STATUSID=appStDef.STATUSID 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 appStDef.STATUSNAME like '%pending approval%' and ApprovalDetails.ISDELETED!='1' and std.ispending='1' and extract(epoch from(now()::TIMESTAMP - to_timestamp(ApprovalStage.SENT_DATE/1000)::TIMESTAMP))/3600/24 > 10 group by wo.WORKORDERID
- This can be modified as per your requirement.
- Execute the query and save the report with a name. [Report name: closerequest]
Goto Admin > Custom Schedule Function > Global Function > New > Paste the global function contents and save it with a name.
- Update the URL and integration key in the script.
- You can get the integration key under Admin > Integrations > Integration Key > New
- Please use an integration key with admin privilege, as there wont be any role related permission issues.
- Update the integration key in the global function.
- Goto Admin > Custom Schedule Functions > New > Paste the content from close_request.txt and save it with a name
- Configure a custom schedule to invoke the created custom function with the query report as parameter.
- Note: We can configure this on repeat mode if the request count is huge.
New to ADSelfService Plus?