Home » Applications » Oracle Fusion Apps & E-Business Suite » Responsibilities and Forms Access (merged 2 threads)
Responsibilities and Forms Access (merged 2 threads) [message #259382] Wed, 15 August 2007 07:11 Go to next message
mujjbur
Messages: 13
Registered: August 2007
Junior Member
Hi,

I am new member of Orafaq website and this forum. I need a help from the users. I am Oracle Apps Technical Person newly engaged in writing scripts and queries to fetch data from fnd tables. I have an urgent request as follows.

I need to have a list of ORACLE users of who has which responsibility. Could retrieve this information for people belonging to org:PBJ and its each responsibilities. I would like to know the method too if possible ASAP.

Thanks in Advance.

Muzibur
Re: To get users and their responsibilities belongs to a particular org(Please Very Urgent) [message #259417 is a reply to message #259382] Wed, 15 August 2007 10:12 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I think you can get the required info from the following tables.

fnd_user
fnd_user_resp_groups
fnd_profile_option_values
hr_operating_units
fnd_profile_options

Edit: If you want responsibility name also, then you may need to join with fnd_responsibility_vl.

By
Vamsi

[Updated on: Wed, 15 August 2007 10:21]

Report message to a moderator

Re: To get users and their responsibilities belongs to a particular org(Please Very Urgent) [message #259736 is a reply to message #259417] Thu, 16 August 2007 06:42 Go to previous messageGo to next message
mujjbur
Messages: 13
Registered: August 2007
Junior Member
Hi Vamsi,

Thanks for your valuable inputs. Since i am a newbie, i am unable to understand the relationship between responsibility,user and it profile options.

I would be grateful to you if you provide me the script to find the list of users and their responsibilities in which whose responsibilities are belong to one particular organization sat 'XXX'.

Thanks in Advance.

Muzibur
Re: To get users and their responsibilities belongs to a particular org(Please Very Urgent) [message #259883 is a reply to message #259736] Thu, 16 August 2007 14:16 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Try this....
select frv.responsibility_name,fu.user_name
  from fnd_user fu
      ,fnd_user_resp_groups fug
      ,fnd_responsibility_vl frv
      ,fnd_profile_option_values fpv
      ,hr_operating_units hou
      ,fnd_profile_options fo
 where hou.name = &Org_Name
   and to_char(hou.organization_id) = fpv.profile_option_value
   and fpv.profile_option_id = fo.profile_option_id
   and fo.profile_option_name = 'ORG_ID'
   and fpv.level_value = fug.responsibility_id
   and fug.responsibility_id = frv.responsibility_id
   and fug.user_id = fu.user_id
 order by 1, 2;
By
Vamsi
Re: To get users and their responsibilities belongs to a particular org(Please Very Urgent) [message #259991 is a reply to message #259883] Fri, 17 August 2007 01:56 Go to previous messageGo to next message
mujjbur
Messages: 13
Registered: August 2007
Junior Member
Hi Vamsi,

Thank you so much for the inputs. It is working perfectly and i am trying to understand the script slowly. I am so happy with that, one more humble request. If i have doubt in understanding any different client requirements, I will post my query directly to you. Please help me as much as you can. Thanks a lot again. Have a Great Day and Happy Weekend.

Muzibur.
Re: To get users and their responsibilities belongs to a particular org(Please Very Urgent) [message #260024 is a reply to message #259991] Fri, 17 August 2007 03:16 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Muzibur,
This forum is viewed / monitored by many experts.
Someone or the other will help you.

By
Vamsi

[Updated on: Fri, 17 August 2007 03:20]

Report message to a moderator

Re: To get users and their responsibilities belongs to a particular org(Please Very Urgent) [message #261202 is a reply to message #260024] Wed, 22 August 2007 02:42 Go to previous messageGo to next message
mujjbur
Messages: 13
Registered: August 2007
Junior Member
Hi Vamsi,

Thanks for your valuable comments. Sir, only one request from you now. Its urgent. Please dont mistake me. I need to find List of users and responsibilities for some given forms.

I have form names. I want to know which are the responsibilities are attached to these forms.

Please provide the idea.

Thanks.

Expecting your reply.

Muzibur.
Re: To get users and their responsibilities belongs to a particular org(Please Very Urgent) [message #261420 is a reply to message #261202] Wed, 22 August 2007 09:36 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Muzibur,
You can get the required using the following tables.
fnd_user
fnd_user_resp_groups
fnd_responsibility_vl
fnd_compiled_menu_functions
fnd_form_functions_vl
fnd_form_vl
By
Vamsi

[Updated on: Wed, 22 August 2007 09:42]

Report message to a moderator

Re: To get users and their responsibilities belongs to a particular org(Please Very Urgent) [message #261427 is a reply to message #261420] Wed, 22 August 2007 10:12 Go to previous messageGo to next message
mujjbur
Messages: 13
Registered: August 2007
Junior Member
Hi vamsi,

Thank you so much. I tried with your idea. But i dont know how to map the values of fnd_compiled_menu_functions. No idea about grant flag.

Please only this time, kindly provide the script.

Thank you very much.

Muzibur.
Re: To get users and their responsibilities belongs to a particular org(Please Very Urgent) [message #261432 is a reply to message #261427] Wed, 22 August 2007 10:24 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I also don't know about grant_flag.
You can check etrm.
I don't think I can post the content from etrm here.

Regarding the join they are very simple and on id columns only.

Edit: Post your code. Then I'll post mine.

By
Vamsi

[Updated on: Wed, 22 August 2007 10:25]

Report message to a moderator

Responsiblities and Forms Access [message #261638 is a reply to message #259382] Thu, 23 August 2007 03:21 Go to previous messageGo to next message
mujjbur
Messages: 13
Registered: August 2007
Junior Member
Can someone please help me in this regard. Its very urgent and so kind of you.

I need to get of list of responsibilities and their corresponding forms access.

Please provide the script if possible.

I am talking about fnd_tables in Oracle Apps.

Thanks in Advance.

Muzibur
Re: To get users and their responsibilities belongs to a particular org(Please Very Urgent) [message #261766 is a reply to message #261432] Thu, 23 August 2007 09:37 Go to previous messageGo to next message
mujjbur
Messages: 13
Registered: August 2007
Junior Member
Hi Vamsi,

Thanks for your help. I have posted my query in this. Please help me in correcting this. Kindly give your ideas. Correct me where ever i am wrong.

Thanks a lot once again.

Here we go,

SELECT fu.user_name,fu.description,furg.start_date,
frvl.responsibility_name,fff.function_name,ff.form_name
FROM fnd_user fu,fnd_user_resp_groups furg,fnd_responsibility_vl frvl,
fnd_compiled_menu_functions fcmf,fnd_form_functions_vl fff,fnd_form_vl ff
WHERE fu.user_id = furg.user_id
AND furg.responsibility_id = frvl.responsibility_id
AND frvl.menu_id = fcmf.menu_id
AND fff.function_id = fcmf.function_id
AND fff.form_id = ff.form_id
AND (fu.end_date IS NULL OR fu.end_date >= SYSDATE)
AND (furg.end_date IS NULL OR furg.end_date >= SYSDATE)
AND (frvl.end_date IS NULL OR frvl.end_date >= SYSDATE)
AND form_name IN ('QPXPRLST','INVADPPI','APXVDDUP','FNDPOMSV','FNDRSRUN','BOMFDBOM','INVADCSC')

Please reply me back, expecting your reply.

Regards,

Muzibur
Re: To get users and their responsibilities belongs to a particular org(Please Very Urgent) [message #261811 is a reply to message #261766] Thu, 23 August 2007 11:58 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
It looks fine for me.
But you are better person to tell, as you can see whether your requirement has been fulfilled or not.

Seems I forgot to mention effective date ranges in my previous query.

Edit: By the way why didn't you use NVL instead of using NULL & OR.

By
Vamsi

[Updated on: Thu, 23 August 2007 12:00]

Report message to a moderator

Re: Responsiblities and Forms Access [message #261816 is a reply to message #261638] Thu, 23 August 2007 12:17 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Please don't cross post.
Please format your posts.

By
Vamsi
Previous Topic: OCP Certification
Next Topic: This is regarding Descriptive Flex Field.
Goto Forum:
  


Current Time: Fri Dec 09 00:09:40 CST 2016

Total time taken to generate the page: 0.07029 seconds