aonestar.public

Parameters

Name	Type	Mode
booking_json	jsonb	IN
result_msg	text	OUT
result_data	text	OUT

Definition

DECLARE
    v_hotel_code TEXT;
    v_rateplan_code TEXT;
    v_roomtype_code TEXT;
    v_ota_code TEXT;
    v_payment_method TEXT;
    v_voucher_no TEXT;
    v_chanel_booking_id TEXT;
    v_arrival DATE;
    v_system_date DATE;
    v_departure DATE;
    v_arrival_time TEXT;
    v_departure_time TEXT;
    v_nights INTEGER;
    v_note TEXT;
    v_requirements JSONB;
    v_action TEXT;
    v_action_type TEXT;
    v_book_date DATE;

    v_state   TEXT;
    v_detail  TEXT;
    v_hint    TEXT;
    v_context TEXT;
    error TEXT;

    m_get_mapped_rateplan INTEGER;
    m_get_mapped_roomtype INTEGER;
    m_get_mapped_channel INTEGER;

    v_mapped_result JSONB;

    r_result_code text;
    r_result_msg text;
    r_msg_code text;
    r_out_booking_id int;

    v_booking_id INT;

    -- get map config nibs
    v_inft_rsvtype_id INT;
    check_booking boolean;
    current_username text;


BEGIN 
    current_username := current_setting('current.username', true);
    -- Invalid JSON
    IF booking_json IS NULL OR booking_json = '{}' THEN
        RETURN QUERY SELECT 'FAIL',
        jsonb_build_object(
            'result_code','4',
            'msg_code','10800',
            'result_msg','Invalid JSON data',
            'booking_id',null,
            'current_username',current_username
        )::text;
        RETURN;
    END IF;

    v_hotel_code     := TRIM(coalesce(booking_json->>'hotel_code', ''));
    v_ota_code       := TRIM(coalesce(booking_json->'reservations'->>'ota_code', ''));
    
    v_payment_method := TRIM(coalesce(booking_json->'reservations'->'payment'->>'payment_method', ''));
    v_voucher_no     := TRIM(coalesce(booking_json->'reservations'->>'voucher_no', ''));
    v_chanel_booking_id := TRIM(coalesce(booking_json->'reservations'->>'channel_booking_id', ''));
    v_nights         := coalesce((booking_json->'reservations'->>'nights')::INTEGER, 1);

    v_note           := TRIM(replace(coalesce(booking_json->'reservations'->>'note', ''), E'\r', ' '));
    v_requirements   := coalesce(booking_json->'reservations'->'requirements', '[]'::jsonb);
    v_action         := TRIM(coalesce(booking_json->>'action','NEW')); 
    v_action_type    := TRIM(coalesce(booking_json->'reservations'->>'action_type', ''));
    v_book_date      := NULLIF(booking_json->'reservations'->>'booked_date', '')::date;

    v_arrival  := NULLIF(booking_json->'reservations'->>'arrival', '')::date;
    v_departure:= NULLIF(booking_json->'reservations'->>'departure', '')::date;
    
    IF v_arrival IS NULL OR v_departure IS NULL OR v_departure <= v_arrival THEN
        RETURN QUERY SELECT 'FAIL',
        jsonb_build_object(
            'result_code','4',
            'msg_code','10801',
            'result_msg','Invalid arrival/departure date',
            'booking_id',null,
            'current_username',current_username
        )::text;
        RETURN;
    END IF;
    
    v_system_date := fn_system_date();


    IF v_arrival < v_system_date THEN
        RETURN QUERY SELECT 'FAIL',
        jsonb_build_object(
            'result_code','4',
            'msg_code','10802',
            'result_msg','Arrival date cannot be in the past',
            'booking_id',null,
            'current_username',current_username
        )::text;
        RETURN;
    END IF;

    v_arrival_time   := NULLIF(booking_json->'reservations'->>'arrival_time', '');
    v_departure_time := NULLIF(booking_json->'reservations'->>'departure_time', '');

    m_get_mapped_rateplan := CASE WHEN v_rateplan_code <> '' THEN fn_get_mapped_rateplan(v_rateplan_code) END;
    m_get_mapped_roomtype := CASE WHEN v_roomtype_code <> '' THEN fn_get_mapped_roomtype(v_roomtype_code) END;
    m_get_mapped_channel  := fn_get_mapped_channel(v_ota_code, v_hotel_code, v_payment_method, v_roomtype_code, v_rateplan_code);


IF EXISTS (
   SELECT 1
   FROM jsonb_array_elements(booking_json->'reservations'->'details') elem
   WHERE coalesce(fn_get_mapped_roomtype(elem->>'roomtype_code'),0) = 0
      OR coalesce(fn_get_mapped_rateplan(elem->>'rateplan_code'),0) = 0
) THEN
   RETURN QUERY SELECT 'FAIL',
   jsonb_build_object(
       'result_code','4',
       'msg_code','10803',
       'result_msg','Invalid mapping (rateplan/roomtype)',
       'booking_id',null,
       'current_username',current_username
   )::text;
   RETURN;
END IF;

    v_hotel_code := fn_intf_param('NBIS','HOTEL_CODE',v_hotel_code);
    v_inft_rsvtype_id := fn_intf_param('NBIS','RSVTYPE_ID',v_hotel_code)::int;

    IF v_hotel_code IS NULL THEN 
        RETURN QUERY SELECT 'FAIL',
        jsonb_build_object(
            'result_code','4',
            'msg_code','10804',
            'result_msg','Invalid hotel code',
            'booking_id',null,
            'current_username',current_username
        )::text;
        RETURN;
    END IF;
    
    -- Build mapped result JSON
    v_mapped_result := jsonb_build_object(
        'rsvtype_id', v_inft_rsvtype_id,
        'guest_id', null,
        'channel_id', m_get_mapped_channel,
        'voucher_no', v_voucher_no,
        'channel_booking_id', v_chanel_booking_id,
        'origin_id', null,
        'arrival', v_arrival,
        'arrival_time', v_arrival_time,
        'departure', v_departure,
        'departure_time', v_departure_time,
        'note', v_note,
        'book_date', v_book_date,
        'payment', jsonb_build_object(
            'payment_method', v_payment_method,
            'folio_pattern_id', null,
            'credit_limit', null
        ),
        'details', (
            SELECT coalesce(jsonb_agg(
                jsonb_build_object(
                    'roomtype_id', fn_get_mapped_roomtype(elem->>'roomtype_code'),
                    'roomtype_code', elem->>'roomtype_code',
                    'rateplan_id', fn_get_mapped_rateplan(elem->>'rateplan_code'),
                    'arrival', elem->>'arrival',
                    'departure', elem->>'departure',
                    'rate_schedule', coalesce(elem->'rate_schedule','[]'::jsonb),
                    'qty', (elem->>'qty')::int,
                    'adult', (elem->>'adult')::int,
                    'child', (elem->>'child')::int,
                    'infant', (elem->>'infant')::int,
                    'charge_to', 'B'
                )
            ), '[]'::jsonb)
            FROM jsonb_array_elements(booking_json->'reservations'->'details') elem
        ),
        'requirements', v_requirements,
        'nights', v_nights,
        'guests', coalesce(booking_json->'reservations'->'guests','[]'::jsonb)
    );

    v_action_type := upper(v_action_type);
    check_booking := EXISTS (SELECT 1 FROM booking WHERE (voucher_no = v_voucher_no and channel_id = m_get_mapped_channel) OR channel_booking_id = v_chanel_booking_id);

      v_action_type := upper(v_action_type);

    -- assign check_booking
    check_booking := EXISTS (
        SELECT 1
        FROM booking
        WHERE (voucher_no = v_voucher_no AND channel_id = m_get_mapped_channel)
           OR channel_booking_id = v_chanel_booking_id
    );

    IF v_action_type = 'NEW' THEN
        IF check_booking THEN
            RETURN QUERY SELECT 'FAIL',
            jsonb_build_object(
                'result_code','4',
                'msg_code','10805',
                'result_msg','Duplicate voucher_no or channel_booking_id',
                'booking_id',null,
            'current_username',current_username
            )::text;
            RETURN;
        END IF;

        SELECT s.result_code, s.result_msg, s.msg_code, s.out_booking_id
        INTO r_result_code, r_result_msg, r_msg_code, r_out_booking_id
        FROM sp_create_booking(v_mapped_result,'NBIS',false,NULL) s;

    ELSIF v_action_type = 'MODIFY' THEN
        IF NOT check_booking THEN
            RETURN QUERY SELECT 'FAIL',
            jsonb_build_object(
                'result_code','4',
                'msg_code','10806',
                'result_msg','No existing booking for update',
                'booking_id',null,
            'current_username',current_username
            )::text;
            RETURN;
        END IF;

        SELECT id INTO v_booking_id
        FROM booking 
        WHERE voucher_no = v_voucher_no OR channel_booking_id = v_chanel_booking_id
        LIMIT 1;
        
        v_mapped_result := v_mapped_result || jsonb_build_object('booking_id', v_booking_id);

        SELECT a.result_code, a.result_msg, a.msg_code
        INTO r_result_code, r_result_msg, r_msg_code
        FROM sp_amend_booking(v_mapped_result,'NBIS',false,NULL) a;

    ELSIF v_action_type = 'CANCEL' THEN
        IF NOT check_booking THEN
            RETURN QUERY SELECT 'FAIL',
            jsonb_build_object(
                'result_code','4',
                'msg_code','10807',
                'result_msg','No existing booking for cancellation',
                'booking_id',null,
            'current_username',current_username
            )::text;
            RETURN;
        END IF;

        SELECT id INTO v_booking_id
        FROM booking 
        WHERE voucher_no = v_voucher_no OR channel_booking_id = v_chanel_booking_id
        LIMIT 1;
    
        SELECT c.result_code, c.result_msg, c.msg_code
        INTO r_result_code, r_result_msg, r_msg_code
        FROM sp_cancel_booking(v_booking_id,'Cancelled by channel','NBIS') c;

    ELSE
        RETURN QUERY SELECT 'FAIL',
        jsonb_build_object(
            'result_code','4',
            'msg_code','10808',
            'result_msg','Unknown action',
            'booking_id',null,
            'current_username',current_username
        )::text;
        RETURN;
    END IF;

    RETURN QUERY 
    SELECT 'SUCCESS',
           jsonb_build_object(
               'result_code', r_result_code,
               'msg_code', r_msg_code,
               'result_msg', r_result_msg,
               'booking_id', r_out_booking_id,
            'current_username',current_username
           )::text;

EXCEPTION
    WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS
            v_state   = RETURNED_SQLSTATE,
            error     = MESSAGE_TEXT,
            v_detail  = PG_EXCEPTION_DETAIL,
            v_hint    = PG_EXCEPTION_HINT,
            v_context = PG_EXCEPTION_CONTEXT;

        RETURN QUERY 
        SELECT 'FAIL',
               jsonb_build_object(
                   'result_code','4',
                   'msg_code','10899',
                   'result_msg','System error',
                   'error_code', v_state,
                   'error_msg', error,
                   'detail', v_detail,
                   'hint', v_hint,
            'current_username',current_username
               )::text;
END;

sp_process_booking

Parameters

Definition