How to Delete the attachments of requested items that are closed after 14 business days.

[Nagasamudram Ma]

How to delete the attachments of requested items that are closed after 14 business days which excludes weekends (saturday and sunday) and office holidays in cmn_Schedule table and the RITM closed on holidays on next working days servicenow.

[Maddysunil]

@Nagasamudram Ma 

you can write a schedule job which should run on daily basis, replace the fields with the actual fields in the below code:

// Function to calculate business days between two dates
function calculateBusinessDays(startDate, endDate, holidayTable) {
    var start = new GlideDateTime(startDate);
    var end = new GlideDateTime(endDate);
    var days = 0;
    var holidayGR = new GlideRecord(holidayTable);
    holidayGR.query();
    while (start.compareTo(end) <= 0) {
        if (start.getDayOfWeek() != 1 && start.getDayOfWeek() != 7) {
            var isHoliday = false;
            while (holidayGR.next()) {
                var holidayDate = new GlideDateTime(holidayGR.getValue('holiday_date'));
                if (start.equals(holidayDate)) {
                    isHoliday = true;
                    break;
                }
            }
            if (!isHoliday) {
                days++;
            }
        }
        start.addDays(1);
        holidayGR.refresh();
    }
    return days;
}

var currentDate = new GlideDateTime();
var ritmGR = new GlideRecord('sc_req_item');

ritmGR.addQuery('sys_updated_on', '<=', currentDate);
ritmGR.addQuery('active', false);
ritmGR.query();

while (ritmGR.next()) {
    var closedAt = ritmGR.getValue('closed_at');
    var businessDays = calculateBusinessDays(closedAt, currentDate, 'cmn_schedule');

    // Check if the RITM is closed after 14 business days
    if (businessDays >= 14) {
        // Delete attachments related to the RITM
        var attachmentGR = new GlideRecord('sys_attachment');
        attachmentGR.addQuery('table_name', 'sc_req_item');
        attachmentGR.addQuery('table_sys_id', ritmGR.getUniqueValue());
        attachmentGR.query();

        // Use a nested query to fetch attachments for multiple records
        var sysIds = [];
        while (attachmentGR.next()) {
            sysIds.push(attachmentGR.getUniqueValue());
        }

        // Delete attachments
        var attachmentToDeleteGR = new GlideRecord('sys_attachment');
        attachmentToDeleteGR.addQuery('sys_id', 'IN', sysIds);
        attachmentToDeleteGR.query();
        while (attachmentToDeleteGR.next()) {
            attachmentToDeleteGR.deleteRecord();
        }
    }
}

Kindly mark helpful/accepted if it helps you.

Thanks

[Aniket Bhanse]

You can simply write a scheduled job which runs daily. And in the Schedule, you can mention your schedule.
Daily you will check in the schedule job if any RITM is closed. If closed, then how many days it has been since it is closed. If your condition is fulfilled, you can simply delete the attachment using GlieAttachment API

var attachment = new GlideSysAttachment();
var attachmentSysID = 'a87769531b0820501363ff37dc4bcba2';
attachment.deleteAttachment(attachmentSysID);

If my response helped please mark it correct and close the thread so that it benefits others.