I needed a query to fetch the profile option values at USER level for Language Preference set by the employee. So here is what I had written, let me know if any change is required.
SELECT
fnd.language_code
FROM
fnd_profile_options_b b,
fnd_profile_option_values v,
per_users per,
fnd_languages_vl fnd
WHERE
v.level_value = per.user_guid
AND b.profile_option_id = v.profile_option_id
AND per.person_id IS NOT NULL
AND b.profile_option_name = 'FND_LANGUAGE'
AND v.level_name = 'USER'
AND v.profile_option_value = fnd.language_tag
AND fnd.activation_status = 'ACTIVE'
AND per.person_id = (select distinct person_id from per_all_people_f where person_number='123456')
fnd.language_code
FROM
fnd_profile_options_b b,
fnd_profile_option_values v,
per_users per,
fnd_languages_vl fnd
WHERE
v.level_value = per.user_guid
AND b.profile_option_id = v.profile_option_id
AND per.person_id IS NOT NULL
AND b.profile_option_name = 'FND_LANGUAGE'
AND v.level_name = 'USER'
AND v.profile_option_value = fnd.language_tag
AND fnd.activation_status = 'ACTIVE'
AND per.person_id = (select distinct person_id from per_all_people_f where person_number='123456')
