...

This document describes how DSP Contracts are represented and used to drive decisions for ServiceNow tasks. These are changing on R1138, R1139, R1140, R1141, D936, D937.

Background

DSP Contracts have been implemented as "Service Contracts" table (ast_service} that came with the asset management module. Each contract has a list of organizations which it serves; these lists are maintained in a Box spreadsheet and pulled into ServiceNow via a nightly import set (this could probably be improved by allowing in-tool editing; the trade-off is role/ACL management vs. import set/transform management).

...

• When processing an incident, an ITIL user should be able to determine which DSP group supports a particular user so that the incident can be routed to the appropriate DSP group.
• DSP managers need to be able to report at the end of the month the time worked for each support contract so they can accurately bill the orgs
• DSPs need to be able to filter tickets assigned to their groups by the contract of each client's org so they can constrain by the contracts they support
• DSP managers need counts of the users supported by a given contract so they can appropriately staff the contract support groups

Fields

To accomplish correct assignment, it was initially decided that the user table (sys_user) would have a visible field "DSP Group" (u_dsp_group) which would be populated by a business rule. The DSP contracts were loaded (one time) into the service contracts table (ast_service). The organization table was given a custom field to point each org back to exactly one contract. These relationships were initially loaded one time. Over time, the date in the user table became stale and a way was eventually established to update it regularly (see below). Currently, DSP group assignment is made reliable through a UI action that implements the business rule. 

To deal with billing, a "bill to contract" field was added to task_time_worked. Upon insertion of each task time worked entry, business logic records a DSP contract to bill as appropriate.

Data Source

Since there is no official data ensconced in an existing database, and we didn't feel like adding a role and ACLs at the time, it was desirable to delegate responsibility for the data source to something other than the contracts table. So a formatted spreadsheet was placed in Box.com and paired with a data source transform map in ServiceNow. Multiple people have edit access.

Data Contract

...

Currently, DSP group assignment is made reliable through a UI action that implements the business rule. 

To deal with billing, a "bill to contract" field was added to task_time_worked. Upon insertion of each task time worked entry, business logic records a DSP contract to bill as appropriate.

Data Source

Since there is no official data ensconced in an existing database, and we didn't feel like adding a role and ACLs at the time, we delegated responsibility for the data source to something outside of ServiceNow. So a formatted spreadsheet was placed in Box.com and paired with a data source transform map in ServiceNow. Multiple people have edit access, and they are responsible for editing workflow. The import is based on the below data contract.

Data Contract

• The DSP organization is responsible for the source data content
• spreadsheet containing data will exist at https://yale.box.com/shared/static/k53bk7qymee6t5g7j85u.xls (don't delete the file, or this will break)
• must remain XLS (Excel 97/2000) format
• must remain publicly accessible
• headers must be "Contract Number", "Active?", "Short Description", "Assignment Group", "Organizations"
  • Contract Number should be unique (enforced upon import)
  • Active? must TRUE or FALSE
  • Assignment Group should match an existing group (or it will be rendered as empty)
  • Organizations should be a space-delimited list of valid orgs (invalid orgs will not register upon import)
  • Each organization should map to one contract only (last match wins)
  • The data will be loaded daily at midnight.

...

// 
// 2013/01/24 - william.west@yale.edu - Defect 376 - DSP Query not showing contracts for requests.
//
// The scripted part of this transform parses the Organizations column from the input.
// * regex validate, ignore any input that doesn't match.
// * foreach org listed, get the gliderecord, set u_service_contract to point to current contract and update.
// * ignore no match.

//
// MDN implementation of Array.indexOf
// https://developer.mozilla.org/en-US/docs/JavaScript/Reference/Global_Objects/Array/indexOf
if (!Array.prototype.indexOf) {
    Array.prototype.indexOf = function (searchElement /*, fromIndex */ ) {
        "use strict";
        if (this == null) {
            throw new TypeError();
        }
        var t = Object(this);
        var len = t.length >>> 0;
        if (len === 0) {
            return -1;
        }
        var n = 0;
        if (arguments.length > 1) {
            n = Number(arguments[1]);
            if (n != n) { // shortcut for verifying if it's NaN
                n = 0;
            } else if (n != 0 && n != Infinity && n != -Infinity) {
                n = (n > 0 || -1) * Math.floor(Math.abs(n));
            }
        }
        if (n >= len) {
            return -1;
        }
        var k = n >= 0 ? n : Math.max(len - Math.abs(n), 0);
        for (; k < len; k++) {
            if (k in t && t[k] === searchElement) {
                return k;
            }
        }
        return -1;
    }
}

//
// create array of latest list orgs that are supposed to have this contract, trim any spaces from each
var newestorgs = source.u_organizations.split(' ');
for(var i=0; i<newestorgs.length; i++) {
    newestorgs[i] = newestorgs[i].replace(/\s+/g, '');
}

//
// first, get the list of orgs which currently have this contract
var contract_id = getContract(source.u_contract_number); 
var currentorgs = getOrgsWithContract(contract_id);

//
// next, get a list or orgs which have this contract but shouldn't
var deleteorgs = deleteThese(currentorgs, newestorgs);

//
// finally, get a list of orgs which should have this contract but don't
var addorgs = addThese(currentorgs, newestorgs);

// 
// if the contract_id isn't 0, make the prescribed org changes
if(contract_id != 0) {
    deleteOrgs(deleteorgs);
    addOrgs(addorgs, contract_id);
} 

//
// uncomment for debug output
gs.log('********* TRANSFORM service_contracts - ' + source.u_contract_number 
+ ', sys_id: ' + contract_id
+ '; will delete: ' + deleteorgs.toString()
+ '; will add: ' + addorgs.toString()
+ '; current orgs: ' + currentorgs.toString()
);
//+ '; new org list: ' + newestorgs.toString()
//);

//
// get a contract by number; return sys_id or 0
function getContract(number) {
    var gr = new GlideRecord('ast_service');
    gr.addQuery('number',number);
    gr.query();

    if(gr.next()) {
        return(gr.sys_id);
    } else {
        return 0;
    }
}

// 
// get orgs pointing to contract with sys_id == contract; return array of org sys_ids (could be empty)
function getOrgsWithContract(contract) {
    var orgs = [];

    var gr = new GlideRecord('u_cmn_organization');
    gr.addQuery('u_service_contract', contract);
    gr.query();
    
    while(gr.next()) {
        orgs.push(gr.u_id.toString());
    }
    return orgs;
}

//
// return array of orgs in current but not in newest
function deleteThese(current, newest) {
    var orgs = [];
    var i;

    for (i=0; i<current.length; i++) {
        if(newest.indexOf(current[i],0) == -1) {
            orgs.push(current[i]);
        }
    }
    return orgs;
}

//
// return array of orgs in newest but not in current
function addThese(current, newest) {
    var orgs = [];
    var i;

    for (i=0; i<newest.length; i++) {
        if(current.indexOf(newest[i],0) == -1) {
            orgs.push(newest[i]); 
        }
    }
    return orgs;
}

//
// disassociate service contract from orgs with matching names
function deleteOrgs(orgs) {
    var i;
 
    for(i=0; i<orgs.length; i++) {
        var gr = new GlideRecord('u_cmn_organization');
        gr.addQuery('u_id',orgs[i]);
        gr.query();
        if(gr.next()) {
            gr.u_service_contract = '';
            gr.update();
        }
    } 
}

//
// associate service contract with orgs matching names
function addOrgs(orgs, contract) {
    var i;
 
    for(i=0; i<orgs.length; i++) {
        var gr = new GlideRecord('u_cmn_organization');
        gr.addQuery('u_id',orgs[i]);
        gr.query();
        if(gr.next()) {
            gr.u_service_contract = contract;
            gr.update();
        }
    } 
 gr.update();
        }
    } 
}

Data Model

...

Implementation

Group Assignment

"When processing an incident, an ITIL user should be able to determine which DSP group supports a particular user so that the incident can be routed to the appropriate DSP group."

...

}

Data Model

Implementation

Group Assignment

"When processing an incident, an ITIL user should be able to determine which DSP group supports a particular user so that the incident can be routed to the appropriate DSP group."

This is facilitated via a UI action called "Assign to DSP Group", which is available in Incident and SC Task forms. It chooses a group by the rules described above.

There are a series of possible scenarios, stored in HPALM on R1138. We have copied them here for convenience:

Business Rules

• If a client has no DSP contract the ticket should be assigned to the Service Desk
• If a client has a DSP Contract that maps to an inactive assignment unit (e.g. an IT Partner who has not onboarded yet) the ticket should be assigned to CTS Service Desk, regardless of whether they are FASIT.
• Medical School Faculty are supported by CTS DSP Teams 2 and 3.  The query that flags clients as a FASIT person automatically excludes Medical School faculty from being flagged as FASIT. (The Medical School Faculty as NON-FASIT is not changed by this requirement; however, we should test it anyway with some Medical School Faculty members. We literally went to the Medical School website, found some faculty members, and tried it.)
• If a client is flagged as a FAS-IT person they should be assigned to the appropriate support contract
  • If the client is a faculty member supported by an active IT Partner the ticket should be assigned to the IT Partner assignment unit (see above for what happens if an IT Partner assignment unit is inactive). To identify whether the assignment group is an IT Partner, review the name of the assignment group: All IT Partner assignment group follow the naming convention of beginning with the letters "ITP".
  • Any other faculty members the ticket should be assigned to DSP Team FASIT

How to test:

You need to isolate people of each category, partially complete an Incident form, and use the 'Assign to DSP Group" UI Action button at the top bar of the Incident form. Those categories are:

• Find a user with no contract
• Find a user with FASIT, with a contract that is not tied to any IT Partner
• Find a user with FASIT, with a contract that is tied to an ACTIVE IT Partner
• Find a user with FASIT, with a contract that is tied to an INACTIVE IT Partner

Contract Billing Reports

"DSP managers need to be able to report at the end of the month the time worked for each support contract so they can accurately bill the orgs."

...

General algorithm is if Task Assignment Group starts with "CTS DSP Team", AND Client Contract is populated, then bill to Client Contract. If not, do no billing to Client Contract.
Over a month, sums are obtained by making a time-constrained list of task_time_worked records, then reporting on the "Bill to Contract" field.  Example:

...