Time & Capacity Management in ERP5

ERP5 currently provides closed loop MRP but does not provide complete MRPII at this time. MRPII will be implemented according to the following technical note.

The base problem of time and capacity management can be stated as follows: does the current simulation plan meet the current capacity plan ? This requires to be able to calculate capacity over a period of time as well as to be able to calculate usage of resource over a period of time.

Problem 1: calculate the capacity of resource over a time period

For example, calculate the number of working days of an employee over a period of time, the number of working hours of a machine over a period of time.

We suppose that working calendars are represented as movements, with a quantity of 1 for an available day, -1 for a non available day and 0 for information movement. Available days are supposed to completely overlap unavailable days.

Calculation of the net number of available days for a resource can be implemented with a method like this:

      SELECT 
        SUM((MAX(from_date, start_date) - MIN(to_date, stop_date)) * quantity)
      FROM  
        movement
      WHERE    
        from_date <= start_date < to_date
      OR 
        from_date < stop_date <= to_date           
      AND
        (  movement is in global calendar
        OR movement is in personal calendar
        OR movement is in career calendar
        OR etc.)

or like this:

      SELECT 
        SUM(
          IF (portal_type = "Global Calendar") THEN (MAX(from_date, start_date) - MIN(to_date, stop_date))
          ELSEIF (portal_type = "Personal Calendar") THEN - (MAX(from_date, start_date) - MIN(to_date, stop_date))
        )          
      FROM  
        movement
      WHERE    
        from_date <= start_date < to_date
      OR 
        from_date < stop_date <= to_date           
      AND
        (  movement is in global calendar
        OR movement is in personal calendar
        OR movement is in career calendar
        OR etc.)

Problem 2: calculate the resource usage over a time period

We suppose that the quantity in a movement has been set in order to take into account non working days. For example, a batch starting on friday morning and finishing on monday evening counts for a 2 days of labour if saturday and sunday are non working days. Quantity is therefore set to the equivalent of 2 days. The problem we have to solve is to calculate the resource usage over, for example, an interval of time starting on sunday and stopping on wednesday.

We can use for this an expression like this:

      SELECT 
        SUM(
          IF (start_date <> stop_date) THEN
            quantity
            * (MAX(from_date, start_date) - MIN(stop_date, to_date))
            / (start_date - stop_date)
          ELSE
            quantity
        )                        
      FROM
        movement
      WHERE       
        from_date <= start_date < to_date
      OR 
        from_date < stop_date <= to_date           

Problem 3: schedule simulation movements

We can now calculate the capacity and the usage of a resource, or of a group of resources. We now want to apply this to the scheduling of simulation events.

The main time related question to solve in simulation is: if a production starts at start_date, when will it finish if we suppose we have infinite capacity with finite execution speed.

What makes this question less easy than it seems is that production batches may contain numerous produced items and, eventually, multiple production levels. Production items are grouped and sequenced. A given operation will be executed in one location before another operation is executed in another location.

The method we recommend involves the following steps:

• step 1: production operations define a quantity with identical start_date and stop_date. start_date are set based on outsourcing lead time (a.k.a. waiting time for internal production) and logistic time.
• step 2: collectMovement groups simulation movements according to their depth in the simulation, their rank in the BOM and their location.
• step 3: duration for each group is calculated based on the resource usage and the node capacity calculated with the method described for problem 1
• step 4: dates for each group are updated to take into account the constraints between each group and the previously calculated duration. Operations are considered to happen in parallel within each group.
• step 5 (undecided): some dates may be rescheduled according to rules such such as "produce ASAP" or "group with".

Problem 4: capacity planning

Based on the plan defined in the simulation, we can display a capacity plan by calculating over intervals of time the capacity of groups of resources and their usage. This allows to detect capacity problems. The intervals of time can be quite long for distant events and short for close events.

Using this system consists in the following steps:

• step 1: display production plan and capacity graph. Display planning problems with colors
• step 2: allow user to perform some operations on displayed blocs (ex. move, move ASAP, etc.)
• step 3: use solvers to reschedule some production batches based on constraints. solvers sould use methods similar to step 4 of problem 3

Technical Issues

Improved date arithmetics will be required in MySQL to support the above methods. We may have to backport TIMESTAMPDIFF or implement DATETIMEDIFF in MySQL 4.1.