aonestar.public

Parameters

Name	Type	Mode
folio_id	integer	IN
new_close_date	date	IN (DEFAULT NULL)
user_name	text	IN (DEFAULT NULL)

Definition

DECLARE 
    _status char(1);
    _open_date date;
    _close_date date;
    _close_time timestamptz;
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text  TEXT;
    messages json = $${     
        "30116" : "Folio not found",
        "30122" : "Cannot reopen outdated cash sale",
        "30123" : "Folio %s is already open" 
    }$$;       --"Reopen is only allowed on the day of closing"
BEGIN
    -- CHECK WHETHER CANCEL-CHECKIN IS AVAILABLE
    /*
     1. check status
     2. check stay period
     3. check room avail
     */
    CALL sp_check_endday_process(); 

    SELECT f.status, f.open_date, f.close_date, f.close_time
    FROM folio f
    WHERE id = $1
    INTO _status, _open_date, _close_date, _close_time;

    user_name = coalesce(user_name, fn_current_user());

CASE 
        WHEN _status IS NULL THEN RETURN fn_result_error('30116', messages->>'30116');  
        WHEN _status = 'A'   THEN RETURN fn_result_error('30123', messages->>'30123', folio_id::text); 
        WHEN _close_date <> fn_system_date() THEN RETURN fn_result_error('30122', messages->>'30122'); 
        ELSE 
    END CASE;

UPDATE folio AS f
    SET status = 'A', 
        close_time = NULL,
        close_user = NULL,
        close_date = COALESCE(new_close_date, close_date)
    WHERE f.id = $1;

UPDATE folio_items AS fi    
    SET closed = FALSE, 
        close_date = NULL,
        close_time = NULL,
        close_user = NULL,
        close_shift = NULL
    WHERE fi.folio_id = $1 AND fi.close_date = _close_date AND fi.close_time = _close_time;  

    -- TODO: save log 

    RETURN fn_result_success();
EXCEPTION 
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        _msg_text = MESSAGE_TEXT,
        _sqlstate = RETURNED_SQLSTATE,
        _detail   = PG_EXCEPTION_DETAIL,
        _hint     = PG_EXCEPTION_HINT,
        _context  = PG_EXCEPTION_CONTEXT;    
    RETURN fn_handle_error(_sqlstate, _msg_text, _detail, _hint, _context, format('sp_reopen_cashsale(%L)', $1));  
END;

sp_reopen_cashsale_folio

Parameters

Definition