Klaas' weblog

A thing that has been bothering our project for some time now is vague behaviour of the Autonomous Scheduler. We found out that the key problem of this behaviour was the table CAC_SR_OBJECT_CAPACITY. This table contains the capacity for your resources. So if you’re wondering why your resource isn’t getting any tasks assigned, you might want to check the corresponding record in this table.

Capacity is defined as the availability in a certain trip, so basically when you run the process Generate Field Service Trips, this table is filled with capacity records. Next, all task assignments within this trip are deducted and if there’s time left, you get a positive number in the AVAILABLE_HOURS column. So far so good.

It sometimes happens the available hours is a negative number. This basically means something went wrong. Logically the Scheduler will not assign any tasks to a person without any availability, especially when your availability is negative! Use the query below to figure out which resources have a negative availability.

select source_name, object_id, count(1)
from cac_sr_object_capacity b
, jtf_rs_defresources_v c
where available_hours < -30
and c.resource_id = b.object_id
and trunc(start_date_time) = '20-10-2009'
group by source_name, object_id;


This basically shows which resources have a problem. We use -30 to exclude any resources who have double tasks assigned, which sometimes leads to a negative number, just below 0. Less than -30 is considered a problem for us, but you might want to use a higher number, 0 or -10 for example. The object_id is what you need in the next query.


select a.creation_date "assignment creation date"
, a.resource_id "resource id"
, a.assignment_status_id "assignment status id"
, a.booking_start_date "assignment start"
, a.booking_end_date "assignment end"
, a.object_capacity_id
, b.object_id "resource id - capacity"
, b.start_date_time "capacity start"
, b.end_date_time "capacity end"
, b.available_hours "available hours"
, c.planned_start_date
, c.planned_end_date
, c.scheduled_start_date
, c.scheduled_end_date
, c.planned_effort
, c.planned_effort_uom
, a.sched_travel_duration
, a.sched_travel_duration
, c.task_number
from jtf_task_assignments a
, cac_sr_object_capacity b
, jtf_tasks_b c
where a.object_capacity_id = b.object_capacity_id
and c.task_id = a.task_id
and object_id = 100004376
and available_hours < -30
and trunc(start_date_time) = '20-10-2009';


This yields an overview of all tasks linked to the capacity records. For some reason this usually contains all kinds of tasks that should not be there. Mostly the assignments are from repeating tasks in our case. Erasing these task is the first part of the solution.

To fix the availability, i.e. to recalculate it, you have to run the Generate Field Service Trips concurrent program with the FIX option. (what's in a name). If the concurrent program finished succesfully, you're done. If it ends in error, you missed a resource.

Good luck and let me know if it helped.