aonestar.public

Parameters

Name	Type	Mode

Definition

DECLARE
    sys_date date = fn_system_date();
BEGIN
  delete from room_usage;
  delete from room_activity;


  /* Bookings */
  INSERT INTO room_activity
    (status, activity_type, roomtype_id, start_date, end_date, qty, channel_id, booking_id, booking_item)
  SELECT 'RSV', 'Initialize', bi.roomtype_id, bi.arrival, bi.departure-1, (bi.qty-bi.block_qty-bi.inhouse_qty),
        b.channel_id, bi.booking_id, bi.item_id
  from booking_items bi inner join booking b on b.id = bi.booking_id
  where b.status in ('R','C','I') and (bi.qty-bi.block_qty-bi.inhouse_qty) > 0;

  /* Block */
  INSERT INTO room_activity
    (status, activity_type, roomtype_id, start_date, end_date, qty, channel_id, booking_id, booking_item, room_id)
  select 'BLK', 'Initialize', rm.roomtype_id, rb.arrival, rb.departure-1, 1,
      b.channel_id, rb.booking_id, rb.booking_item, rb.room_id
  from room_block rb
      left join room rm on rm.id = rb.room_id
      left join booking b on b.id = rb.booking_id
  where rb.status = 'B';

  /* Occupied rooms */
  INSERT INTO room_activity
    (status, activity_type, roomtype_id, start_date, end_date, qty, channel_id, register_id, room_id)
  with inh as (
    select rg.room_id, r.roomtype_id,
           max(rg.id) as register_id,
           min(arrival) as arrival,
           max(departure) as departure
    from registration rg left join room r on r.id = rg.room_id
    where rg.status = 'I' and rg.room_id is not null
    group by 1,2
  )
  select 'INH', 'Initialize', inh.roomtype_id, inh.arrival, inh.departure-1, 1,
         rg.channel_id, inh.register_id, inh.room_id
  from inh inner join registration rg on rg.id = inh.register_id;

  /* Out Of Order */
  INSERT INTO room_activity
    (status, activity_type, roomtype_id, start_date, end_date, qty, room_id)
  select o.ooo_type, 'Initialize', r.roomtype_id, o.start_date, o.end_date-1, 1, o.room_id
  from out_of_order o left join room r on r.id = o.room_id
  where o.ooo_type in ('OOO','OOI') and o.start_date <> o.end_date and o.end_date <> sys_date;

  --post_event 'ROOM_AVAIL_RESET';
END

sp_reset_roomavail

Parameters

Definition