Close requests with pending approval for more than 10 days

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. 
  1. Goto Reports > New Query Report > execute the query and save it with a name.
    1. MSSQL
    2. 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
    3. PGSQL
    4. 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
    5. This can be modified as per your requirement.
    6. Execute the query and save the report with a name. [Report name: closerequest]
  2. Goto Admin > Custom Schedule Function > Global Function > New > Paste the global function contents and save it with a name.
    1. Update the URL and integration key in the script. 
    2. You can get the integration key under Admin > Integrations > Integration Key > New
    3. Please use an integration key with admin privilege, as there wont be any role related permission issues. 

    4. Update the integration key in the global function.

  3. Goto Admin > Custom Schedule Functions > New > Paste the content from close_request.txt and save it with a name

  4. Configure a custom schedule to invoke the created custom function with the query report as parameter. 
    1. Note:  We can configure this on repeat mode if the request count is huge.




      New to ADSelfService Plus?

        Resources

            • Related Articles

            • Report on requests pending approval

              DB Compatibility : PGSQL & MSSQL Build Compatibility : Builds above 10000 SELECT wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",dpt.DEPTNAME "Department",wo.TITLE "Subject",ti.FIRST_NAME "Technician", std.STATUSNAME "Request Status", ...
            • How to email pending requests list to technician periodically

              This post describes the use of a python script to email pending requests to each technician in a periodic interval using Custom Schedules.This showcases the use of a feature called 'Custom Schedules' that was released in the build 9300 of ServiceDesk ...
            • How to implement dynamic request approval using FAFR and custom triggers - Deluge

              This is a sample script written in Deluge to handle Conditional Approvals for Incident \ Service Requests, through Custom Triggers using Field and Form Rules.  One of the advantages of using this script that no modification in the script is required ...
            • Automatically close request after successive approval reminders.

              This post describes the use of a python script to close requests after successive approval reminders using Custom Schedules. Use Case: We have an option under the self-service portal in order to send reminders mail for the approvals, what would be ...
            • Pending Request for more than 10 days

              This report provides a quick view of all Pending incidents which are not closed for more than 10 days. When the incidents start backlogging faster than they can be resolved, the tendency of long incidents pending time can be exposed in this report. ...