Hi Sir/Madam,
I have a requirement to add roles to a report sql. The query is as below. I have to include (wf_local_roles/wf_local_user_roles etc) tables for getting the role assign start date, role assign end date and role name. I have got a link between user table and roles table, but couldn't get a link between responsibility table and roles. Please Help...
SELECT a.user_id,
a.user_name,
a.start_date user_start_date,
a.end_date user_end_date,
last_logon_date,
password_date,
a.description,
a.employee_id,
c.responsibility_name,
b.responsibility_id,
b.start_date resp_assign_start_date,
b.end_date resp_assign_end_date,
(SELECT ledger_id
FROM gl_ledgers
WHERE ledger_id IN (SELECT hou.set_of_books_id
FROM hr_operating_units hou
WHERE hou.organization_id = FND_PROFILE.VALUE('ORG_ID'))) set_of_books_id,
(SELECT name
FROM gl_ledgers
WHERE ledger_id IN (SELECT hou.set_of_books_id
FROM hr_operating_units hou
WHERE hou.organization_id = FND_PROFILE.VALUE('ORG_ID'))) set_of_books_name
FROM apps.fnd_user a,
apps.FND_USER_RESP_GROUPS_ALL b,
apps.fnd_responsibility_tl c
WHERE a.user_id = b.user_id
AND b.responsibility_id = c.responsibility_id
ORDER BY user_name, responsibility_name
Regards
Akhil