aonestar.public

Parameters

Name	Type	Mode
user_name	text	IN
status	text	INOUT
msg	text	INOUT

Definition

DECLARE 
    system_date date = fn_system_date();
    rm record;
    _checkout_status char(2);
    _return_status char(2);
    _update_count int = 0;
BEGIN
    
    _checkout_status = fn_get_param('ROOM_STATUS', 'CHECKOUT_STATUS', 'DI');
    _return_status   = fn_get_param('ROOM_STATUS', 'RETURN_STATUS', _checkout_status);


    /* change OOO to default checkout status */
    FOR rm IN (
        SELECT rs.room_id, o.return_status 
        FROM room_status rs INNER JOIN out_of_order o 
            ON o.room_id = rs.room_id AND o.end_date <= system_date
        WHERE rs.status IN ('OOO','OOS','OOI')
    )LOOP 
        update room_status
        set status = 'VAC', hk_status = COALESCE(rm.return_status, _return_status)
        where room_id = rm.room_id;
       
        _update_count = _update_count +1;
    END LOOP;

    /* change pre-mark OOO to current OOO */
    FOR rm IN (
        SELECT oo.room_id, oo.ooo_type
        FROM out_of_order oo LEFT JOIN room_status rs ON rs.room_id = oo.room_id
        WHERE (oo.start_date = system_date OR oo.start_date = system_date+1) 
            AND (rs.status <> oo.ooo_type) AND (rs.status <> 'OCC')
    )LOOP 
        UPDATE room_status 
        SET status = rm.ooo_type
        WHERE room_id = rm.room_id;
    
        _update_count = _update_count +1;
    END LOOP;

    /* Reset maid status on occupied rooms*/
    UPDATE room_status as rs 
    SET hk_status = _checkout_status
    WHERE rs.status = 'OCC';  

    status = 'success';
    msg = format('%s rooms updated', _update_count); 
END

sp_endday_update_room_status

Parameters

Definition