select
e.user_id,
(select full_name from
apps.per_all_people_f papf
where employee_number=(select distinct user_name from apps.fnd_user where user_id=a.user_id)
and sysdate between papf.effective_start_date and papf.effective_end_date) full_name,
fu.user_name,fu.start_date,fu.end_date,
b.application_id,b.RESPONSIBILITY_KEY,c.RESPONSIBILITY_NAME
from
apps.fnd_application fap,
apps.FND_USER_RESP_GROUPS a,
apps.FND_RESPONSIBILITY b,
apps.FND_RESPONSIBILITY_VL c,
apps.FND_RESPONSIBILITY_tl d,
apps.FND_USER_RESP_GROUPs_direct e,
apps.fnd_user fu
where
fap.application_id=b.application_id
and a.responsibility_id=b.responsibility_id
and c.RESPONSIBILITY_KEY=b.RESPONSIBILITY_KEY
and b.responsibility_id=d.responsibility_id
and b.responsibility_id=e.responsibility_id
and e.user_id=a.user_id
and a.user_id=fu.user_id
-- and c.RESPONSIBILITY_NAME like 'Application%'
and fu.user_name=:employee_number
-- and a.user_id=:User_id
e.user_id,
(select full_name from
apps.per_all_people_f papf
where employee_number=(select distinct user_name from apps.fnd_user where user_id=a.user_id)
and sysdate between papf.effective_start_date and papf.effective_end_date) full_name,
fu.user_name,fu.start_date,fu.end_date,
b.application_id,b.RESPONSIBILITY_KEY,c.RESPONSIBILITY_NAME
from
apps.fnd_application fap,
apps.FND_USER_RESP_GROUPS a,
apps.FND_RESPONSIBILITY b,
apps.FND_RESPONSIBILITY_VL c,
apps.FND_RESPONSIBILITY_tl d,
apps.FND_USER_RESP_GROUPs_direct e,
apps.fnd_user fu
where
fap.application_id=b.application_id
and a.responsibility_id=b.responsibility_id
and c.RESPONSIBILITY_KEY=b.RESPONSIBILITY_KEY
and b.responsibility_id=d.responsibility_id
and b.responsibility_id=e.responsibility_id
and e.user_id=a.user_id
and a.user_id=fu.user_id
-- and c.RESPONSIBILITY_NAME like 'Application%'
and fu.user_name=:employee_number
-- and a.user_id=:User_id