aonestar.public

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

sp_tr_booking_items_avail_change

Parameters

Definition