SELECT
  Slot.slotID,
  Slot.maxWorkUnits,
  Slot.name,
  Slot.startTime,
  Slot.endTime,
  CASE 
    WHEN numberWorkUnits IS NULL 
    THEN 0 
    ELSE numberWorkUnits 
  END,
  Slot.startDate,
  Slot.endDate,
  Slot.frequencyPattern 
INTO
  :slotID,
  :maxWorkUnits,
  :slotName,
  :startTime,
  :endTime,
  :numberWorkUnits,
  :startDate,
  :endDate,
  :frequencyPattern 
FROM
  Slot  
  LEFT OUTER JOIN
  
  ( 
  SELECT
    slotID AS SASlotID,
    numberWorkUnits 
  FROM
    SlotAllocation 
  WHERE SlotAllocation.slotAllocationDate = :effectiveDate 
  ) SA 
  ON Slot.slotID = SA.SASlotID,
  DailySchedule 
WHERE DailySchedule.effectiveDate = 
  ( 
  SELECT
    MAX(effectiveDate) 
  FROM
    DailySchedule 
  WHERE DailySchedule.locationID = :locationID 
    AND DailySchedule.effectiveDate <= :effectiveDate 
    AND Dailyschedule.type = :locationScheduleType
   AND DailySchedule.recordStatus = :status
  ) 
  AND Slot.scheduleID = DailySchedule.scheduleID 
  AND Slot.locationID = :locationID 
  AND Slot.recordStatus = :status 
  AND Dailyschedule.type = :locationScheduleType
ORDER BY 
  Slot.startTime