SELECT pu.username
from per_user_roles pur,
per_users pu,
per_roles_dn_tl prdt,
per_roles_dn prd,
per_user_roles pur1,
per_users pu1,
per_roles_dn_tl prdt1,
per_roles_dn prd1
WHERE pu.user_id = pur.user_id
AND prdt.role_id = pur.role_id
AND prdt.role_id = prd.role_id
AND prdt.language = USERENV ('lang')
AND pu.active_flag = 'Y'
and prdt.role_name in ('Buyer')
and pu1.user_id = pur1.user_id
AND prdt1.role_id = pur1.role_id
AND prdt1.role_id = prd1.role_id
AND prdt1.language = USERENV ('lang')
AND pu1.active_flag = 'Y'
and prdt1.role_name in ('Procurement Manager')
and pu1.user_guid = FND_GLOBAL.USER_GUID
UNION
select distinct pu.username
from per_user_roles pur,
per_users pu,
per_roles_dn_tl prdt,
per_roles_dn prd
where pu.user_id = pur.user_id
AND prdt.role_id = pur.role_id
AND prdt.role_id = prd.role_id
AND prdt.language = USERENV ('lang')
AND pu.active_flag = 'Y'
and prdt.role_name not in ('Procurement Manager')
and prdt.role_name in ('Buyer')
and pu.user_guid = FND_GLOBAL.USER_GUID