aonestar.public

Parameters

Name	Type	Mode

Definition

DECLARE
    sys_date date := fn_system_date();
BEGIN
    DELETE FROM room_usage;
    DELETE FROM room_activity;

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,
           GREATEST(bi.qty - bi.block_qty - bi.inhouse_qty, 0),
           b.channel_id, bi.booking_id, bi.item_id
    FROM booking_items bi
    JOIN booking b ON b.id = bi.booking_id
    WHERE b.status IN ('R', 'C', 'I')
      AND GREATEST(bi.qty - bi.block_qty - bi.inhouse_qty, 0) > 0;

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
    JOIN room rm ON rm.id = rb.room_id
    JOIN booking b ON b.id = rb.booking_id
    WHERE rb.status = 'B'
      AND b.status IN ('R', 'C', 'I');

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(rg.arrival) AS arrival, max(rg.departure) AS departure
        FROM registration rg
        JOIN room r ON r.id = rg.room_id
        WHERE rg.status = 'I' AND rg.room_id IS NOT NULL
        GROUP BY rg.room_id, r.roomtype_id
    )
    SELECT 'INH', 'Initialize', inh.roomtype_id, inh.arrival, inh.departure - 1,
           1, rg.channel_id, inh.register_id, inh.room_id
    FROM inh
    JOIN registration rg ON rg.id = inh.register_id;

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
    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;
END

sp_reset_roomavail

Parameters

Definition