aonestar.public

Parameters

Name	Type	Mode
i_booking_item	integer	IN
i_block_ids	int4[]	IN (DEFAULT NULL)
i_user_name	text	IN (DEFAULT NULL)
result_code	text	OUT
result_msg	text	OUT
msg_code	text	OUT

Definition

DECLARE block RECORD;
DECLARE bk_items int[];
DECLARE _booking_id int;
DECLARE block_rooms text[] = '{}';
DECLARE checked_in_rooms text[] = '{}';
BEGIN
    result_code = '0'; msg_code = '00'; result_msg = 'Success';

    IF i_block_ids IS NULL THEN
       bk_items = array[$1];
       i_block_ids = (SELECT array_agg(id) FROM room_block rb WHERE rb.booking_item = $1);  
    ELSE    
       bk_items = (SELECT array_agg(DISTINCT booking_item) FROM room_block WHERE id = ANY(i_block_ids));
    END IF;

    _booking_id = (SELECT booking_id FROM room_block rb WHERE id = i_block_ids[1]);

    block_rooms =   (SELECT array_agg(r.room_number) 
                     FROM room_block rb INNER JOIN room r ON r.id = rb.room_id 
                     WHERE rb.id = ANY(i_block_ids) AND NOT rb.checked_in);
                 
    checked_in_rooms = (SELECT array_agg(r.room_number) 
                        FROM room_block rb INNER JOIN room r ON r.id = rb.room_id 
                        WHERE rb.id = ANY(i_block_ids) AND rb.checked_in);             
         
    DELETE FROM room_block rb
    WHERE rb.id = ANY(i_block_ids) AND NOT rb.checked_in;


    IF fn_length(block_rooms) > 0 THEN
        PERFORM sp_recalc_booking_rooms(item_id) FROM UNNEST(bk_items) AS item_id;
        PERFORM sp_log_booking(_booking_id, bk_items[1], 'Unblock', 'Unblock room '||array_to_string(block_rooms, ', '), i_user_name); 
        result_code = iif(fn_length(checked_in_rooms) > 0, '1', '0');
    ELSEIF fn_length(checked_in_rooms) > 0 THEN 
        result_code = '4'; 
        msg_code = '10301'; 
        result_msg = sys_msg_format(msg_code, 'Room %s already checked in.', array_to_string(checked_in_rooms, ','));    
    END IF;
END

sp_unblock_rooms_old

Parameters

Definition