Trigger weekly notification to assignment group when assigned to is not longer with company

Trigger weekly notification to assignment group when assigned to is not longer with company and add incident and catalog task details.

Notifications :

Hi ${mail_script:get.assignmentgroup.member}
  Pls note that the Service-Now tickets below are assigned to an individual  who is no longer with us.  Kindly take necessary steps to assign an appropriate ownership.
 ${mail_script:offboarding.reminder.email.incident}
${mail_script:offboardingtask.reminder.email.task}
 Thank you

Mail Script

(function runMailScript( /* GlideRecord */ current, /* TemplatePrinter */ template,
    /* Optional EmailOutbound */
    email, /* Optional GlideRecord */ email_action,
    /* Optional GlideRecord */
    event) {
 var getEvent=event.parm1;
 if(getEvent.indexOf("SCTASK")!=-1)
  {
    var html = '';
    html += "<table  style='font-size: 10pt; border: 1px solid black;'><tr><th style='text-align:center;width: 100px;border: 1px solid black;'><b>REQ#</b></th><th style='text-align:center;width: 100px;border: 1px solid black;'><b>RITM#</b></th><th style='text-align:center;width: 140px;border: 1px solid black;'><b>STASK#</b></th><th style='width: 100px;border: 1px solid black;'><b>Requested For</b></th><th style='width: 100px;border: 1px solid black;'><b>Opened</b></th><th style='width: 230px;border: 1px solid black;'><b>Short Description</b></th></tr>";
    var app = new GlideRecord('sc_task'); //create a query to retrieve open approvals
    app.addEncodedQuery("numberIN" + event.parm1);
    app.query();
    while (app.next()) {
        var SCnumber = "/sc_task.do?sys_id=" + app.sys_id;
        var SCURL = "<a href=" + SCnumber + ">" + app.number + "</a>";
  var RITMnumber = "/sc_req_item.do?sys_id=" + app.request_item;
        var RITMURL = "<a href=" + RITMnumber + ">" + app.request_item.getDisplayValue() + "</a>";
  var REQnumber = "/sc_request.do?sys_id=" + app.request_item.request;
        var REQURL = "<a href=" + REQnumber + ">" + app.request_item.request.getDisplayValue() + "</a>";
         html += "<tr><td style='width: 100px;border: 1px solid black;'><b>" + REQURL + "</b></td><td  style='width: 100px;border: 1px solid black;'><b>"  + RITMURL  + "</b></td><td  style='width: 140px;border: 1px solid black;'><b>"  + SCURL  + "</b></td><td  style='width: 100px;border: 1px solid black;'>" + app.request_item.request.requested_for.getDisplayValue() + "</td><td  style='width: 100px;border: 1px solid black;'>" + app.opened_at.getDisplayValue() + "</td><td style='width: 230px;border: 1px solid black;'>" + app.short_description + "</td></tr>";
     }
    html += '</table>';
    template.print(html);
  }
})(current, template, email, email_action, event);

Mail Script: offboarding.reminder.email.task

(function runMailScript( /* GlideRecord */ current, /* TemplatePrinter */ template,
    /* Optional EmailOutbound */
    email, /* Optional GlideRecord */ email_action,
    /* Optional GlideRecord */
    event) {
 var getEvent=event.parm1;
 if(getEvent.indexOf("INC")!=-1)
  {
    var html = '';
    html += "<table style='font-size: 10pt; border: 1px solid black;'><tr><th style='text-align:center;width: 85px;border: 1px solid black;'><b>Number</b></th><th style='text-align:center;width: 100px;border: 1px solid black;'><b>Caller</b></th><th style='width: 170px;border: 1px solid black;'><b>Opened</b></th><th style='width: 401px;border: 1px solid black;'><b>Short Description</b></th></tr>";
    var app = new GlideRecord('incident'); //create a query to retrieve open approvals
    app.addEncodedQuery("numberIN" + event.parm1);
    app.query();
    while (app.next()) {
        var number = "/incident.do?sys_id=" + app.sys_id;
        var URL = "<a href=" + number + ">" + app.number + "</a>";
         html += "<tr><td style='width: 85px;border: 1px solid black;'><b>" + URL + "</b></td><td  style='width: 120px;border: 1px solid black;'><b>"  + app.caller_id.getDisplayValue()  + "</b></td><td  style='width: 170px;border: 1px solid black;'>" + app.opened_at.getDisplayValue() + "</td><td style='width: 401px;border: 1px solid black;'>" + app.short_description + "</td></tr>";
     }
    html += '</table>';
    template.print(html);
  }
})(current, template, email, email_action, event);

Mail Script: get.assignmentgroup.member

(function runMailScript( /* GlideRecord */ current, /* TemplatePrinter */ template,
    /* Optional EmailOutbound */
    email, /* Optional GlideRecord */ email_action,
    /* Optional GlideRecord */
    event) {
    var userName = '';
    var gr = new GlideRecord("sys_user_grmember");
    gr.addEncodedQuery("user.active=true^group.name=" + event.parm2);
    gr.query();
    while (gr.next()) {
        userName += gr.user.first_name + ",";
    }
    template.print(userName);
})(current, template, email, email_action, event);

Scheduled Job

var inc = new GlideAggregate("task");
inc.addEncodedQuery("assigned_toISEMPTY^sys_created_onRELATIVELT@hour@ago@24^stateNOT IN3,4,7,8,5,6,106,107,157^assignment_groupISNOTEMPTY^sys_class_name=incident^ORsys_class_name=sc_task");
inc.addAggregate('GROUP_CONCAT', 'number');
inc.groupBy('assignment_group');
inc.query();
while (inc.next()) {
    var inc1 = new GlideRecord("task");
    inc1.addEncodedQuery("numberIN" + inc.getAggregate('GROUP_CONCAT', 'number'));
    inc1.query();
    if (inc1.next()) {
        gs.eventQueue("reminder_notassigned_ticket", inc1, inc.getAggregate('GROUP_CONCAT', 'number'), inc.getDisplayValue('assignment_group'));
    }
 }
////////////////////////////////////Offboarding Task //////////////////////////////////////////////////////////
var offboarding = new GlideAggregate("task");
offboarding.addEncodedQuery("assigned_to.active=false^sys_created_onRELATIVELT@dayofweek@ago@7^stateNOT IN3,4,7,8,5,6,106,107,157^assignment_groupISNOTEMPTY^sys_class_name=incident^ORsys_class_name=sc_task^assignment_group.active=true");
offboarding.addAggregate('GROUP_CONCAT', 'number');
offboarding.groupBy('assignment_group');
offboarding.query();
while (offboarding.next()) {
    var offboarding1 = new GlideRecord("task");
    offboarding1.addEncodedQuery("numberIN" + offboarding.getAggregate('GROUP_CONCAT', 'number'));
    offboarding1.query();
    if (offboarding1.next()) {
        gs.eventQueue("offboarding_task_notification", offboarding1, offboarding.getAggregate('GROUP_CONCAT', 'number'), offboarding.getDisplayValue('assignment_group'));
    }
 }