aonestar.public

Parameters

Name	Type	Mode
booking_id	integer	IN
remark	text	IN
user_name	text	IN (DEFAULT NULL)
result_code	text	OUT
result_msg	text	OUT
msg_code	text	OUT
confirm_no	integer	OUT

Definition

DECLARE 
    booking_status char(1);
    v_state   TEXT;
    v_detail  TEXT;
    v_hint    TEXT;
    v_context TEXT;
BEGIN   
    booking_status = (SELECT status FROM booking WHERE id = $1);

    IF booking_status = 'R' THEN
        UPDATE booking b
        SET status = 'C',
            confirm_no   = nextval('confirm_seq') ,
            confirm_user = coalesce($3, fn_current_user()),
            confirm_time = current_timestamp,
            confirm_remark = $2
        WHERE id = $1
        RETURNING b.confirm_no INTO confirm_no;
    
        PERFORM sp_log_booking($1, null, 'Confirm', 'Confirm booking (Confirm# '||confirm_no::TEXT||')', user_name);
    END IF;

    msg_code = CASE booking_status
          WHEN 'R' THEN '00'
          WHEN 'C' THEN '01'
          WHEN 'X' THEN '02'
          WHEN 'N' THEN '03'
          WHEN 'I' THEN '04'
          WHEN 'O' THEN '05'
          WHEN 'W' THEN '06'
    END;

    result_code = iif(msg_code = '00', '0', '4');

    result_msg = CASE (msg_code)
        WHEN '00' THEN 'Success'
        WHEN '01' THEN sys_msg('104'||msg_code, 'Booking is already confirmed.') 
        WHEN '02' THEN sys_msg('104'||msg_code, 'Cannot confirm cancelled booking.') 
        WHEN '03' THEN sys_msg('104'||msg_code, 'Cannot confirm no-show booking.')     
        WHEN '04' THEN sys_msg('104'||msg_code, 'Cannot confirm checked in booking.') 
        WHEN '05' THEN sys_msg('104'||msg_code, 'Cannot confirm checked out booking.')   
        WHEN '06' THEN sys_msg('104'||msg_code, 'Cannot confirm booking in waiting list.')    
    END;
    msg_code = '104'||msg_code;  
EXCEPTION 
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        v_state   = RETURNED_SQLSTATE,
        result_msg= MESSAGE_TEXT,
        v_detail  = PG_EXCEPTION_DETAIL,
        v_hint    = PG_EXCEPTION_HINT,
        v_context = PG_EXCEPTION_CONTEXT;
    result_code = '4'; msg_code = '99';     
    PERFORM sp_log_error('sp_confirm_booking', result_msg, v_detail, v_state, v_hint, v_context, null, user_name); 
END