Queries: user id & emp number ,reponsibity & menu

[AIMAM]

Hi,
I have two different queries that are requested and I do not have a document to guide me in the tables relationships.
Query 1:
need to represent the reponsibility name asscociated with the name of menu and the functions excluded from that menu in the responsibility.
i was only able to retrieve the reponsibility name and the menu name, i do not know what is the table that contains the menu exclusion and how can i link it to the responsibility.
Please help, my query so far is as follows:

Code: Select all

SELECT DISTINCT e.application_name, a.responsibility_name
--,a.LANGUAGE,
--,b.responsibility_key
, c.user_menu_name
,c.DESCRIPTION
,fme.prompt, fme.description
,fme.MENU_ID,frf.ACTION_ID
FROM apps.fnd_responsibility_tl a,
apps.fnd_responsibility b,
apps.fnd_menus_tl c,
apps.fnd_menus d,
apps.fnd_application_tl e,
apps.fnd_application f,
apps.fnd_menus_tl apm, 
fnd_menu_entries_tl fme,
FND_RESP_FUNCTIONS frf
WHERE a.responsibility_id(+) = b.responsibility_id
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = 'US'
and apm.menu_id = fme.menu_id 
and fme.MENU_ID(+)=c.MENU_ID
and b.RESPONSIBILITY_ID=frf.RESPONSIBILITY_ID

Query 2 :
It is required to represent the employee info associated with their associated reposnsibilities.
i could not link the employee number to the user id.
how can i join the two tables: fnd_user and per_all_people_f

PLEASE HELP ME.

[Pirzada]

Hi,
For Query 2 you need to check if there are employees defined against a particular user id, then you can join both the tables fnd_user and per_all_people_f.

fnd_user.employee_id = per_all_people_f.person_id

Regards,
Ali Pirzada

[admin]

topic.asp?TOPIC_ID=572