aonestar.public

Parameters

Name	Type	Mode

Definition

DECLARE
    activity_type text;
    rsv record;
    blk record;
BEGIN
    /* Trigger: after update of status, channel_id */

    IF (new.status in ('R','C','W','N','X')) THEN
        /* channel changed */
        IF (new.status <> 'W' and old.channel_id is distinct from new.channel_id) THEN
            FOR rsv IN (
                SELECT item_id, roomtype_id, arrival, departure, qty -block_qty -inhouse_qty AS qty
                FROM booking_items WHERE booking_id = new.id
            )
            LOOP
                PERFORM sp_update_avail('RSV', 'Channel changed',
                   rsv.roomtype_id, rsv.arrival, rsv.departure-1, -rsv.qty, OLD.channel_id, rsv.item_id, booking_id := OLD.id);
                PERFORM sp_update_avail('RSV', 'Channel changed',
                   rsv.roomtype_id, rsv.arrival, rsv.departure-1,  rsv.qty, NEW.channel_id, rsv.item_id, booking_id := NEW.id);
            END LOOP;

            FOR blk IN (
                SELECT b.booking_item, r.roomtype_id, b.arrival, b.departure
                FROM room_block b LEFT JOIN room r ON r.id = b.room_id
                WHERE booking_id = new.id
            )
            LOOP
                PERFORM sp_update_avail('BLK', 'Channel changed',
                   blk.roomtype_id, blk.arrival, blk.departure-1, -1, OLD.channel_id, blk.booking_item, booking_id := OLD.id);
                PERFORM sp_update_avail('BLK', 'Channel changed',
                   blk.roomtype_id, blk.arrival, blk.departure-1,  1, NEW.channel_id, blk.booking_item, booking_id := NEW.id);
            END LOOP;
        END IF;

        /* Rebook */
        IF (old.status in ('W','X','N') and new.status in ('R','C')) THEN
            FOR rsv IN (SELECT item_id, roomtype_id, arrival, departure, qty FROM booking_items WHERE booking_id = NEW.id)
            LOOP
                PERFORM sp_update_avail('RSV', 'Rebook',
                   rsv.roomtype_id, rsv.arrival, rsv.departure-1, rsv.qty, NEW.channel_id, rsv.item_id, booking_id := NEW.id);
            END LOOP;
        END IF;

        /* Wait List, Canceled and No-show*/
        IF (old.status in ('R','C') and new.status in ('W','X','N')) THEN
            activity_type = CASE (new.status)
                                WHEN 'W' THEN 'Waitlist'
                                WHEN 'X' THEN 'Cancel booking'
                                WHEN 'N' THEN 'No-show'
                            END;
            FOR rsv IN (SELECT item_id, roomtype_id, arrival, departure, qty FROM booking_items WHERE booking_id = new.id)
            LOOP
                PERFORM sp_update_avail('RSV',
                  activity_type, rsv.roomtype_id, rsv.arrival, rsv.departure-1, -rsv.qty, OLD.channel_id, rsv.item_id, booking_id := OLD.id);
            END LOOP;
        END IF;
    END IF;

    RETURN NULL;
END

sp_tr_booking_avail_change

Parameters

Definition