aonestar.public

Parameters

Name	Type	Mode
i_rate_plan_id	integer	IN

Definition

DECLARE
    v_msg_text text;
    v_sqlstate text;
    v_detail text;
    v_hint text;
    v_context text;

    v_result json;
    v_channels json;
    v_rateplan record;

BEGIN
    ------- Create by T.Supol 10-Apr-2025

    -- ดึงข้อมูล rateplan
    SELECT id, "name"
    INTO v_rateplan
    FROM public.rate_plan
    WHERE id = i_rate_plan_id;

    IF NOT FOUND THEN
        -- ถ้าไม่พบ rateplan ก็ return null
        RETURN NULL;
    END IF;

    -- ดึง channel ที่เกี่ยวข้อง
    SELECT to_json(array_agg(rpc.channel_id))
    INTO v_channels
    FROM public.rate_plan_channels rpc
    WHERE rpc.rateplan_id = i_rate_plan_id AND rpc.enabled;

    -- ถ้าไม่มี channel ใดเลย ให้กำหนดเป็น []
    IF v_channels IS NULL THEN
        v_channels := '[]'::json;
    END IF;

    -- สร้าง JSON object
    v_result := json_build_object(
        'rateplan_id', v_rateplan.id,
        'rateplan_name', v_rateplan.name,
        'channels', v_channels
    );

    RETURN v_result;

EXCEPTION
    WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS
            v_msg_text = message_text,
            v_sqlstate = returned_sqlstate,
            v_detail = pg_exception_detail,
            v_hint = pg_exception_hint,
            v_context = pg_exception_context;

        RETURN fn_handle_error(
            v_sqlstate,
            v_msg_text,
            v_detail,
            v_hint,
            v_context,
            'sp_get_rateplan_mapping_channel_json',
            null
        );
END;