aonestar.public

Parameters

Name	Type	Mode
roomtype_id	integer	IN
arrival	date	IN
departure	date	IN
qty	integer	IN
adult	integer	IN
child	integer	IN (DEFAULT 0)
infant	integer	IN (DEFAULT 0)
book_date	date	IN (DEFAULT NULL)
booking_item	integer	IN (DEFAULT NULL)
channel_id	integer	IN (DEFAULT NULL)
origin_id	integer	IN (DEFAULT NULL)
market_id	integer	IN (DEFAULT NULL)
rate_type	character	IN (DEFAULT 'D')

Definition

DECLARE 
    result_data json;
    sys_date date = fn_system_date();
    result_code text; 
    result_msg text; 
    msg_code text;
    _msg     TEXT;
    _state   TEXT;
    _detail  TEXT;
    _hint    TEXT;
    _context TEXT;
BEGIN
    /* RESULT CODE
       0, 10100 : Available 
       4, 10101 : All rooms were full 
       2, 10102 : Specified room type is not sellable (confirm user to make booking)
       4, 10103 : Specified room type not enough, overbook not allow
       2, 10104 : Specified room type not enough (confirm user to make booking)
       4, 10105 : Rate not found
       
       Pisanu created 04-07-2025
    */

    channel_id := nullif(channel_id, 0);
    origin_id  := nullif(origin_id, 0);
    market_id  := nullif(market_id, 0);
    rate_type  := coalesce(nullif(rate_type, ''), 'D');

    if (booking_item < 1) then
        booking_item := null; 
    end if;

    if arrival < sys_date then 
        arrival := sys_date;
    end if; 
 
    /* Check room available */
    SELECT a.result_code, a.result_msg, a.msg_code 
    FROM sp_check_room_available(roomtype_id, arrival, departure, qty, booking_item) AS a
    INTO result_code, result_msg, msg_code;

    IF result_code = '4' THEN
        RETURN fn_result_error(result_msg, msg_code);
    END IF;


    /* Get available rates */
    WITH Rates as (
        select * 
        from sp_get_available_rates(
                roomtype_id, 
                arrival, 
                departure, 
                qty, 
                adult, 
                child, 
                infant, 
                book_date, 
                booking_item, 
                channel_id, 
                origin_id, 
                market_id, 
                rate_type
            )
    )
    SELECT json_agg(Rates) FROM Rates INTO result_data;

    IF result_data IS NULL THEN 
        RETURN fn_result_error('10105', 'No available rates during this period');
    ELSE
        RETURN fn_result_success(result_data);
    END IF;

EXCEPTION 
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS 
        _msg = MESSAGE_TEXT, 
        _state = RETURNED_SQLSTATE, 
        _detail = PG_EXCEPTION_DETAIL, 
        _hint = PG_EXCEPTION_HINT, 
        _context = PG_EXCEPTION_CONTEXT;
    RETURN fn_handle_error(_state, _msg, _detail, _hint, _context, 'sp_get_available_rates_json', 
         jsonb_build_object(
            'roomtype_id', roomtype_id, 
            'arrival', arrival, 
            'departure', departure, 
            'qty', qty, 
            'adult', adult, 
            'child', child, 
            'infant', infant, 
            'book_date', book_date, 
            'booking_item', booking_item, 
            'channel_id', channel_id, 
            'origin_id', origin_id, 
            'market_id', market_id, 
            'rate_type', rate_type
         )
    ); 
END