aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_tr_booking_items_avail_change
Parameters
Name
Type
Mode
Definition
DECLARE v_status char(1); v_channel_id int; old_qty int; new_qty int; 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 FROM booking b WHERE id = iif(TG_OP = 'DELETE', old.booking_id, new.booking_id) INTO v_status, v_channel_id; IF (v_status not in ('R','C','I')) THEN RETURN NULL; END IF; CASE TG_OP WHEN 'UPDATE' THEN old_qty = old.qty - old.block_qty - old.inhouse_qty; new_qty = new.qty - new.block_qty - new.inhouse_qty; IF (old.roomtype_id <> new.roomtype_id or old.arrival <> new.arrival or old.departure <> new.departure) and (new.qty > (new.inhouse_qty + new.block_qty)) 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); 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); ELSIF (old.qty <> new.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); -- ELSIF (new.inhouse_qty <> old.inhouse_qty) then -- PERFORM sp_update_avail -- ('RSV', iif(new.inhouse_qty > old.inhouse_qty, 'Check in', 'Cancel check-in'), -- new.roomtype_id, new.arrival, new.departure-1, old.inhouse_qty-new.inhouse_qty, v_channel_id, new.item_id, booking_id := new.booking_id); END IF; WHEN 'INSERT' THEN PERFORM sp_update_avail ('RSV', 'Add booking', new.roomtype_id, new.arrival, new.departure-1, new.qty-coalesce(new.block_qty,0), v_channel_id, new.item_id, booking_id := new.booking_id); WHEN 'DELETE' 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 CASE; RETURN NULL; END