Automates rate card–based amount calculations across Contracts, Entitlements, and Time Worked in CSM

[Hafeeza_Shaik]

CSM-Based Rate Card Calculation

This setup automates rate-based calculations between Contracts, Entitlements, and Time Worked records in Customer Service Management (CSM). The process ensures accurate tracking of agent effort and contract consumption.

---

Overview

We used the out-of-the-box tables: Labor Rate Card, Time Worked, and Case (Technology Product Support Case).
When a TPSC record is created and linked with a Contract and Entitlement, a Time Worked record is automatically created.

For our requirement, we created custom fields on the Time Worked table: rate_type, billable, non_billable, and role (reference to Labor Rate Card). Each Rate Type (Standard = 1, Overtime = 1.5) has a multiplier stored in the description field of the Rate Type record.

The Hourly Rate field in the Labor Rate Card is used in the calculation.

In the Contract record, we added:

• total_amount

• consumed_amount

• remaining_amount

• rate_cards (Name–Value pair storing Role: Hourly Rate from Labor Rate Card)

• When a contract is created, the Rate Cards populate automatically.
  The Hourly Rate from the matching role in the contract is used in the calculation.
• Each Contract can have multiple Entitlements, and each Entitlement belongs to only one Contract.
  Contract totals are the sum of all linked Entitlement amounts.

---

Calculation Logic

When Recalculate is clicked on the Entitlement, it checks all related Time Worked records.

Consumed Amount = (time_in_seconds / 3600) * hourly_rate * rate_type_multiplier

The Consumed Amount is updated on the Entitlement, and:

• Remaining Amount = Total - Consumed

• Contract totals are updated accordingly

If the Entitlement or Role on Time Worked changes, the system adjusts previous and new amounts accordingly.

---

Script Include – ContractEntitlementUtils

var ContractEntitlementUtils = Class.create();
ContractEntitlementUtils.prototype = {
    initialize: function() {},

    /* Calculates consumed and remaining amounts for an Entitlement based on Time Worked entries */
    calculateEntitlementAmounts: function(entitlementGr) {
        var taskGr = new GlideRecord('task_time_worked');
        taskGr.addQuery('u_entitlement', entitlementGr.sys_id);
        taskGr.query();

        var consumed = 0;
        while (taskGr.next()) {
            var timeSeconds = taskGr.getValue('time_in_seconds');
            var rateType = taskGr.rate_type.getRefRecord();
            var role = taskGr.role.getRefRecord();
            var rateMultiplier = parseFloat(rateType.description) || 1;
            var hourlyRate = parseFloat(role.hourly_rate) || 0;
            consumed += (timeSeconds / 3600) * hourlyRate * rateMultiplier;
        }

        entitlementGr.consumed_amount = consumed;
        entitlementGr.remaining_amount = entitlementGr.total_amount - consumed;
        entitlementGr.update();

        var contractGr = entitlementGr.contract.getRefRecord();
        if (contractGr.isValidRecord())
            this.calculateContractAmounts(contractGr, 'contract=' + contractGr.sys_id);
    },

    /* Aggregates total, consumed, and remaining amounts from related Entitlements to Contract */
    calculateContractAmounts: function(contractGr, query) {
        var total = 0, consumed = 0, remaining = 0;
        var entGr = new GlideRecord('entitlement');
        entGr.addEncodedQuery(query);
        entGr.query();
        while (entGr.next()) {
            total += parseFloat(entGr.total_amount) || 0;
            consumed += parseFloat(entGr.consumed_amount) || 0;
            remaining += parseFloat(entGr.remaining_amount) || 0;
        }
        contractGr.total_amount = total;
        contractGr.consumed_amount = consumed;
        contractGr.remaining_amount = remaining;
        contractGr.update();
    },

    /* Locks rate card details to Contract when created */
    lockRateCardsToContract: function(contractGr) {
        var rateCardGr = new GlideRecord('fm_labor_rate_card');
        rateCardGr.addQuery('active', true);
        rateCardGr.query();

        var pairs = {};
        while (rateCardGr.next())
            pairs[rateCardGr.rate_code + ''] = rateCardGr.hourly_rate + '';

        contractGr.rate_cards = JSON.stringify(pairs);
        contractGr.update();
    },

    /* Recalculates contract amount when rate card is modified */
    recalculateAmountOnRatecardChange: function(contractGr) {
        this.calculateContractAmounts(contractGr, 'contract=' + contractGr.sys_id);
    },

    /* Recalculates amounts when entitlement reference is changed in task */
    transferAmountWithEntitlementChange: function(taskGr, oldEntGr, newEntGr) {
        if (oldEntGr.isValidRecord()) this.calculateEntitlementAmounts(oldEntGr);
        if (newEntGr.isValidRecord()) this.calculateEntitlementAmounts(newEntGr);
    },

    /* Checks if Time Worked records exist for a given task */
    checkTimeWorked: function(taskGr) {
        var tw = new GlideRecord('task_time_worked');
        tw.addQuery('task', taskGr.sys_id);
        tw.query();
        return tw.hasNext();
    },

    type: 'ContractEntitlementUtils'
};

Business Rules

On Contract Table

/* After Update (Rate Card changed) */
(function executeRule(current, previous) {
    if (current.isValidRecord())
        new global.ContractEntitlementUtils().recalculateAmountOnRatecardChange(current);
})(current, previous);

/* Async Insert (Lock Rate Cards) */
(function executeRule(current, previous) {
    new global.ContractEntitlementUtils().lockRateCardsToContract(current);
})(current, previous);

On Entitlement Table

/* Before Delete */
(function executeRule(current, previous) {
    var contractGr = current.contract.getRefRecord();
    if (contractGr.isValidRecord())
        new global.ContractEntitlementUtils().calculateContractAmounts(contractGr, 'contract=' + contractGr.sys_id + '^sys_id!=' + current.sys_id);
})(current, previous);

/* After Update (Contract changes) */
(function executeRule(current, previous) {
    var currCont = current.contract.getRefRecord(), prevCont = previous.contract.getRefRecord();
    if (currCont.isValidRecord())
        new global.ContractEntitlementUtils().calculateEntitlementAmounts(current);
    if (prevCont.isValidRecord())
        new global.ContractEntitlementUtils().calculateContractAmounts(prevCont, 'contract=' + prevCont.sys_id);
    current.update();
})(current, previous);

/* After Insert/Update (Amount fields change) */
(function executeRule(current, previous) {
    var contGr = current.contract.getRefRecord();
    if (contGr.isValidRecord())
        new global.ContractEntitlementUtils().calculateContractAmounts(contGr, 'contract=' + contGr.sys_id);
})(current, previous);

On Task Table

/* After Update (Entitlement changes) */
(function executeRule(current, previous) {
    var oldEnt = previous.u_entitlement.getRefRecord();
    var newEnt = current.u_entitlement.getRefRecord();
    new global.ContractEntitlementUtils().transferAmountWithEntitlementChange(current, oldEnt, newEnt);
})(current, previous);

UI Action (Recalculate Amount) on Entitlement

var contractGr = current.contract.getRefRecord();
if (contractGr.isValidRecord()) {
    new global.ContractEntitlementUtils().calculateEntitlementAmounts(current);
    current.update();
    action.setRedirectURL(current);
}

End Result

• Time Worked records automatically calculate consumed amounts.

• Entitlement and Contract totals update instantly on recalculation or changes.

• Contract-level aggregation always stays in sync with linked entitlements.

Please mark this article as Helpful if you found it useful.