aonestar.public

Parameters

Name	Type	Mode
ooo_id	integer	IN
new_start	date	IN (DEFAULT NULL)
new_end	date	IN (DEFAULT NULL)
user_name	text	IN (DEFAULT NULL)

Definition

DECLARE 
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text TEXT;
    _room_id int;
    old_start date;
    old_end date;
    update_result json;
BEGIN   
    --CALL sp_check_endday_process();

    SELECT oo.room_id, oo.start_date, oo.end_date
    FROM out_of_order oo WHERE oo.id = $1
    INTO _room_id, old_start, old_end;

    new_start = coalesce(new_start, old_start);
    new_end = coalesce(new_end, old_end);

    IF new_start = old_end AND new_end = old_end THEN 
        RETURN fn_result_error('30134', 'The date range is already the same');  
    END IF;

    SELECT CASE upd.result_code
             WHEN '4' THEN fn_result_error(upd.msg_code, upd.result_msg)
             ELSE fn_result_success()
           END   
    FROM out_of_order AS oo, 
        LATERAL sp_update_out_of_order(ooo_id, oo.ooo_type, oo.reason_id, new_start, new_end, oo.remark, oo.return_status, user_name) AS upd
    WHERE oo.id = ooo_id 
    INTO update_result;

    RETURN update_result;
EXCEPTION  
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        _sqlstate = RETURNED_SQLSTATE,
        _msg_text = MESSAGE_TEXT,
        _detail   = PG_EXCEPTION_DETAIL,
        _hint     = PG_EXCEPTION_HINT,
        _context  = PG_EXCEPTION_CONTEXT;    
    RETURN fn_handle_error(_sqlstate, _msg_text, _detail, _hint, _context, 'sp_change_ooo_period', 
                jsonb_build_object('ooo_id',ooo_id, 'new_start',new_start, 'new_end',new_end)
           );
END