Oracle APEX Putting an Appointment on a Calendar

Objectives

In the prior article, I explored process of building a calendar within an Oracle database. You would only build a calendar within Oracle when developing a robust enterprise level application that involves automatically planning and scheduling appointments for people on the corporate team.

While most would just opt to manage calendars using personal tools that are found with email systems, it can be beneficial when coordinating appointments for service calls or related activities. The scenario involves planning appointments for staff to attend to a service calls with clients. We want the selecting and booking of the appointment slot to be done within a database application written with Oracle APEX.

For this process, we built the calendar in two, maybe three, layers. Now we will add appointments to the calendar framework.

Oracle Calendar Framework

In the article referenced above we have defined timeslots as the framework for a calendar.

Calendar Type	Timeslot Types	Boundaries
Corporate	Available Unavailable others…	Start timestamp with time zone End timestamp with time zone
Staff	Available Unavailable others…	Start timestamp with time zone End timestamp with time zone

Therefore unlike the calendar found on an email system, a bit of time that is white/blank is also null. Our rules state that we can not build appointments on null data. We must have an affirmative defined a timeslot that is available or not available.

While it is possible to work through appointments without having an underlaying layer of timeslots, it is remarkably inaccurate and difficult to look at no data and find meaning. We write queries to explore data and if the query returns nothing, it is hard to know if that is good or bad. By creating a target, we can hit a target. By putting a slice of time on a calendar marked as “available”, we know affirmatively that is is available. By putting a slice of time on a calendar marked as “unavailable”, we know affirmatively that is is not available.

We explored the options in version 0. You rapidly get tangled in rules. Is this a Monday? Is this hour during the work day? Is this day a holiday? This rule says (1) we work from 8 to 4 Monday to Friday. And this rule (2) Says these days are holidays and can’t be booked. The risk we found was that we were switching contexts and creating ad-hoc functions to bring meaning to the null data and the great void.

Green is Go

If you find a green region on a calendar, you can use it for an appointment. It looks like this

Select
  ts.start_time,
  ts.end_time,
  ts.employee_fk
from pln_timeslot ts
left join emp_employee e on e.employee_pk = ts.employee_fk
where ts.active = 'Y' and e.active = 'Y'
and ts.timeslot_type_fk = 'AVAILABLE';

Add a between x and y and you can target a day or an hour.

Modify the query, and we can find no-go zones by changing the criteria in the where clause.

Rules for Appointments

• Appointment must be booked during a timeslot that is designated as available by an employee.
• Appointment cannot be booked during a timeslot that is designated as not-available by the corporate calendar
• An appointment is a call to action. In our case, 1 or more employees go to a client’s location to perform 1 service as described by a service order.
• 1 appointment is 1 person going to 1 location to do 1 service.
• Multiple appointments can be grouped, thereby allowing multiple people to arrive at the same location at the same time to perform 1 or more services
• Multiple appointments can be grouped thereby allowing 1 person to work through multiple services in a series.
• Appointments can undergo common maintenance activities such as:
  • Cancelled
  • Rescheduled
  • &c.

Appointment Minimum Data

By studying the rules above, we can determine the minimum data needed to book an appointment:

• An employee associated with both (employee primary key)
  • an available timeslot (timeslot primary key)
  • and assigned to perform the service (some link between service order and employee)
• A service ordered by a customer therefore:
  • A customer (customer primary key)
  • An address (address primary key)
  • The service ordered by the customer (service primary key)

Grouping Appointments

A sequence of linked appointments

Because the rules for appointments involve the possibility of 1 employee performing multiple services at 1 location, we need to link multiple appointments together.

I need to (1) inspect the hydraulic system on the tractor (2) change the engine oil (3) clean the air filter

Fred the Tractor Service Guy

You want to book each appointment, then link them in a series through time, 1 then 2 then 3. Of course, you don’t actually care that Fred the Tractor Service Guy changes order on site, but you do not want to have a gap between them or cause the series to break.

Let’s add:

A link to the prior appointment. If null, then not linked to anything before (before appointment foreign key). This links to the primary key for the appointment table (appointment primary key)

A link to the following appointment. If null, then not linked to anything after (after appointment foreign key). This links to the primary key for the appointment table (appointment primary key)

Coordinated Appointments

The rules permit multiple employees to be dispatched to the client location for a coordinated effort. In other words, 2 employees have an appointment each at the same location. Therefore, we can’t reschedule 1 employee without risking the entire process. You might say: keep both or reschedule the appointment.

It takes 2 of us to fix that crane.

Barney the Crane Service Guy

Let’s add an appointment group (appointment group foreign key) to group appointments so that multiple people arrive at the same time (you hope). To make this happen, we need a table to for grouping appointments. Now let’s add a table for that feature. In our example, we called it pln_appointment_group.

Appointment Data Structure

By stepping through the analysis, you can see that the data elements needed for an appointment becomes apparent. It looks like this (so far):

• Appointment primary key
• Timeslot foreign key linking to the timeslot table for available/not available/etc
• Appointment Group FK
• Before Appointment FK
• After Appointment FK
• Customer FK
• Address FK
• Employee FK
• Service Order Line FK
• Appointment Status FK (good practice: planned, in progress, completed, &c)
• Appointment Type FK (service, travel, visiting grandmother’s house)

For good measure, we added other information to keep track of the difference between planned and actual and to facilitate billing for the service. We track actual start/end time, actual duration, actual distance driven, billable time, billable distance.

It almost writes itself doesn’t it? Good analysis does. Good analysis is a very technical process and well done well, it results in answers that seem obvious.

The Long Pole Analysis

On my team, we do a process called a “Long Pole” analysis. Likely a terrible name. Envision a tent and think: what is holding this thing up? So we ask ourselves: what is going to hold us up? What is the biggest barrier?

On layer 1, we have timeslots. Let’s focus only on the “available timeslots” because obviously we don’t book appointments on anything that is not available. To keep this simple, we have 1 employee with 1 available timeslot that spans the entire day:

• Fred, Available 14JUL2024 from 0800 to 1700h

Fred has a timeslot defined as available with:

• Timeslot type: Available
• Start Date: 14 JUL 2024 08.00 CET (timestamp with time zone)
• Start Date: 14 JUL 2024 17.00 CET (timestamp with time zone)
• Employee FK: points to Fred

We want to book a 2 hour tractor service appointment for 10 am on 14 JUL for Fred.

We need to transform the data to get it to fit. We need a timeslot for the appointment that run from 10 am to 12 noon on 14 JUL 2024. But we have a timeslot that is bigger than that. It spans the full day.

Oh, interesting!

Appointment and Timeslot Appointment Analysis

Imagine that every staff member booked every work day with 8 hours as “AVAILABLE”. We need to book the 8 hours carefully so that we address issues:

1. Employees need time to travel to the site (not in the this article, but very real and solvable)
2. The company wants to maximize an employee’s billable time (available time). Because: profit

If you put the first appointment of a day at the first available minute of the day (say 0800h), then your appointment aligns neatly on both left edges.

We need to break the underlaying timeslot into 2 separate timeslots:

• Timeslot 1: Aligns with appointment. Start/End date match between Appointment and Timeslot
• Timeslot 2: Starts at the end of the appointment and ends at the end of the original timeslot

What variables do we need:

• Original Timeslot Start Time (left edge, or 0800h)
• Original Timeslot End Time (far right edge, or 1700h)
• Appointment start time (left edge, or 0800h)
• Appointment end time (right edge of appointment, or 10am)

The code comes clearer and clearer as we dig into the details:

-- Left Alignment
when l_appointment_start = l_original_start and l_appointment_end < l_original_end then
  l_case_type           := 'left aligned';
  -- "LEFT" will result in 2 timeslots (inspection, available) + 1 appointment 
  case
    when x = 1 then -- first timeslot is INSPECTION
      update pln_timeslot set
          start_date         = l_appointment_start,
          end_date           = l_appointment_end,
          timeslot_type_fk   = l_inspection,
          employee_fk       = P_EMPLOYEE_PK
        where timeslot_pk   = l_timeslot_array(x);
      l_appointment_pk := appointment_create (
        P_TIMESLOT_PK      => l_timeslot_array(x),
        P_EMPLOYEE_PK      => P_EMPLOYEE_PK,
        P_ORDER_LINE_PK    => P_ORDER_LINE_PK,
        P_CUSTOMER_PK      => r_order.customer_fk,
        P_ADDRESS_PK      => r_order_line.address_fk
        );
      -- if only 1 timeslot and 1 time through the loop then insert as needed.
      if l_timeslot_count = 1 then
        insert into pln_timeslot (
          start_date,
          end_date,
          timeslot_type_fk,
          employee_fk,
          slots_available
          ) values (
          l_appointment_start,
          l_appointment_end,
          l_available,
          P_EMPLOYEE_PK,
          1
          ) return timeslot_pk into l_new_timeslot_pk; 
      end if; -- only 1 timeslot

When you expand the analysis, the pattern appear.

In fact, if you have 1, 2, 3 or more underlaying timeslots, you’ll start seeing that the number of timeslots is not a factor. Sure you have to clean them up. The pattern is this:

Alignment	Resulting Timeslots
Left	2: appointment, available
Right	2: available, appointment
Centered	3: available, appointment, available
Perfect	1: appointment

The code that results will resemble this case statement.

case
  -- bad data
  when l_appointment_start is null or l_original_start is null or l_appointment_end is null or l_original_end is null then
    raise_application_error(-20001,'One of 4 timestamps is null in ' || l_procedure);
  -- Perfect Alignment
  when l_appointment_start = l_original_start and l_appointment_end = l_original_end then
    l_case_type           := 'Perfect';
    if x = 1 then -- first row
      ..... use existing timeslot, create appointment with the timeslot FK
    else
      -- delete all remaining timeslot from row 2 onward
      delete from pln_timeslot where timeslot_pk = l_timeslot_array(x);
    end if; -- first row
    
  -- Left Alignment
  when l_appointment_start = l_original_start and l_appointment_end < l_original_end then
    l_case_type           := 'left aligned';
    -- "LEFT" will result in 2 timeslots (inspection, available) + 1 appointment 
    case
      ..... use first timeslot for appointment, use second timeslot (or create new) timeslot as available
      else
        -- delete all remaining timeslot from timeslot 3 onward
        delete from pln_timeslot where timeslot_pk = l_timeslot_array(x);
    end case; -- which timeslot?

  -- right Alignment
  when l_appointment_start > l_original_start and l_appointment_end = l_original_end then
    l_case_type           := 'right aligned';
    --  "RIGHT" will result in 2 timeslots (available, inspection) + 1 appointment 
      ..... use first timeslot for avilable, use second timeslot (or create new) timeslot as available          
      else
        -- delete all remaining timeslot from timeslot 3 onward
        delete from pln_timeslot where timeslot_pk = l_timeslot_array(x);
    end case; -- which timeslot?    
    
  -- Center Alignment
  when l_appointment_start > l_original_start and l_appointment_end < l_original_end then
    l_case_type           := 'centered';
    --  "CENTER" will result in 3 timeslots (available, inspection, available) + 1 appointment 
      ..... use first timeslot for avilable, 
			..... use second timeslot (or create new) timeslot for appointment      		
			..... use third timeslot (or create new) timeslot for appointment      		
      else
        -- delete all remaining timeslot from timeslot 4 onward
        delete from pln_timeslot where timeslot_pk = l_timeslot_array(x);
    end case; -- which timeslot?
  else
    null; -- I think all cases are covered
end case; -- perfect, left, right, center

Essentially, the code must defragment the available timeslots. Ideally, we would not have three timeslots next to each other that are all “available”. If this happened due to cancellations or reschedules, then this process will bring them together.

Oracle APEX Calendar

When you put this together within Oracle APEX and build both a calendar page region blended with your service order data, you get a page that looks like this:

• Category: APEX, calendar, plsql
• Tag: apex, calendar, oracle, Oracle Standard Time, plsql, sql, timestamp