Cutoff date logic using cmn_schedule

pranavagarw
Tera Expert

3 weeks ago

Hello

 

I have a requirement that, 

"Payroll has a monthly cut-off date defined in an annually updated calendar. Any pay element claims (such as overtime or allowances) submitted after the cut-off must still be accepted but will not be processed until the next payroll cycle. The system must read the official payroll cut-off calendar, validate submission dates, warn users submitting during the blackout period, hold such claims, and automatically send them via the payroll API once the blackout period ends."
The effective date for the element entry is set to the 1st of the relevant month, formatted as DD-MM-YYYY (01/01/2025).

This date is determined based on:

    • The current date.
    • Whether the payroll cut-off has passed at the time of approval or sending

So, how can I achieve this requirement using Schedule table (I don't want to create custom table).

Submitting a Record Producer, based on its Created Date, I have to find out cutoff date and setup the effective date in payload.

Thank you




0 Helpfuls
  • 333 Views
1 REPLY 1

Matthew_13
Mega Sage

3 weeks ago

Hi 

You can most likely meet this requirement using the OOTB Schedule tables (cmn_schedule + cmn_schedule_span) and the GlideSchedule API. So no custom tables needed here that im aware.

1) Model the payroll calendar as a Schedule

Create one schedule per payroll year (or per country/pay group if needed). Then load the official calendar into schedule spans:

  • Approach A (simplest to reason about): “Processing Window” schedule

    • Each month create an Included span from cycle open → cutoff datetime

    • Everything after cutoff is implicitly the “blackout” (out of schedule)

This matches your rule: “After cut-off, accept but hold until next cycle.”

You can update the schedule annually by importing the new spans (CSV/Excel import into cmn_schedule_span)—still OOTB.

2) On Record Producer submit: check if the submission is in/out of schedule

Server-side (Business Rule on the target table or in the Record Producer script):

  • Load the schedule with GlideSchedule

  • Check if sys_created_on (or “approval date” if that’s the decision point) is in schedule

  • If out of schedule, you’re in blackout → warn + hold + compute next release time

ServiceNow supports using GlideSchedule for this kind of date validation. (ServiceNow)

Example logic (server-side)

// sys_id of your payroll calendar schedule (cmn_schedule)
var PAYROLL_SCHED = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';

var sched = new GlideSchedule(PAYROLL_SCHED);
var submitDt = new GlideDateTime(current.sys_created_on); // or approval date/time

var inWindow = sched.isInSchedule(submitDt);

if (!inWindow) {
  // blackout: hold the claim
  current.u_payroll_hold = true;
  current.u_hold_reason = 'Submitted after payroll cutoff';

  // whenNext() gives ms until the next "included" window starts
  var msToNext = sched.whenNext(submitDt);
  var nextWindowStart = new GlideDateTime(submitDt);
  nextWindowStart.add(msToNext);

  current.u_send_after = nextWindowStart;   // date/time to release to payroll API
}

3) Set the “effective date” (01-MM-YYYY) based on cutoff

If in processing window → effective date = 1st of current month
If blackout → effective date =  1st of next payroll month

(You can compute the first-of-month using GlideDateTime/GlideDate logic right before building the API payload.)

4) “Warn user” but still accept the request

  • Show a message in the Record Producer UI (“Submitted after cutoff; will process next cycle”) via:

    • Catalog Client Script (UI warning), and/or

    • Add an Info Message after submit (server-side message)

5) Automatically send to payroll API when blackout ends

Use a Scheduled Flow / Scheduled Job that runs frequently (e.g., hourly/daily) and sends any held claims where:

  • u_payroll_hold = true

  • u_send_after <= now

Then clear the hold flag and log the API result. Hopefully this can get id one for you.

 

@pranavagarw - Please leave a Thumbs up and Accepted Solution if you find Helpful!

 

0 Helpfuls