aonestar.public

Parameters

Name	Type	Mode

Definition

DECLARE
    v_status char(1);
    v_channel_id integer;
    old_qty integer;
    new_qty integer;
BEGIN
    IF (TG_OP = 'UPDATE'
        AND OLD.roomtype_id = NEW.roomtype_id
        AND OLD.arrival = NEW.arrival
        AND OLD.departure = NEW.departure
        AND OLD.qty = NEW.qty
        AND OLD.inhouse_qty = NEW.inhouse_qty
        AND OLD.block_qty = NEW.block_qty)
       OR (TG_OP = 'DELETE' AND OLD.departure < fn_system_date())
    THEN
        RETURN NULL;
    END IF;

SELECT b.status, b.channel_id
    INTO v_status, v_channel_id
    FROM booking b
    WHERE b.id = CASE WHEN TG_OP = 'DELETE' THEN OLD.booking_id ELSE NEW.booking_id END;

IF v_status NOT IN ('R', 'C', 'I') THEN
        RETURN NULL;
    END IF;

CASE TG_OP
        WHEN 'UPDATE' THEN
            old_qty := GREATEST(OLD.qty - OLD.block_qty - OLD.inhouse_qty, 0);
            new_qty := GREATEST(NEW.qty - NEW.block_qty - NEW.inhouse_qty, 0);

IF OLD.roomtype_id <> NEW.roomtype_id
               OR OLD.arrival <> NEW.arrival
               OR OLD.departure <> NEW.departure
            THEN
                IF old_qty > 0 THEN
                    PERFORM sp_update_avail(
                        'RSV', 'Amend booking', OLD.roomtype_id,
                        OLD.arrival, OLD.departure - 1, -old_qty,
                        v_channel_id, OLD.item_id, booking_id := OLD.booking_id
                    );
                END IF;
                IF new_qty > 0 THEN
                    PERFORM sp_update_avail(
                        'RSV', 'Amend booking', NEW.roomtype_id,
                        NEW.arrival, NEW.departure - 1, new_qty,
                        v_channel_id, NEW.item_id, booking_id := NEW.booking_id
                    );
                END IF;
            ELSIF OLD.qty <> NEW.qty AND new_qty <> old_qty THEN
                PERFORM sp_update_avail(
                    'RSV', 'Amend booking', NEW.roomtype_id,
                    NEW.arrival, NEW.departure - 1, new_qty - old_qty,
                    v_channel_id, NEW.item_id, booking_id := NEW.booking_id
                );
            END IF;

WHEN 'INSERT' THEN
            new_qty := GREATEST(NEW.qty - NEW.inhouse_qty, 0);
            IF new_qty > 0 THEN
                PERFORM sp_update_avail(
                    'RSV', 'Add booking', NEW.roomtype_id,
                    NEW.arrival, NEW.departure - 1, new_qty,
                    v_channel_id, NEW.item_id, booking_id := NEW.booking_id
                );
            END IF;

WHEN 'DELETE' THEN
            old_qty := GREATEST(OLD.qty - OLD.block_qty - OLD.inhouse_qty, 0);
            IF old_qty > 0 THEN
                PERFORM sp_update_avail(
                    'RSV', 'Delete booking', OLD.roomtype_id,
                    OLD.arrival, OLD.departure - 1, -old_qty,
                    v_channel_id, OLD.item_id, booking_id := OLD.booking_id
                );
            END IF;
    END CASE;

RETURN NULL;
END

sp_tr_booking_items_avail_change

Parameters

Definition