OraFAQ Forum: Oracle Fusion Apps & E-Business Suite » Responsibilities and Forms Access (merged 2 threads)

Home » Applications » Oracle Fusion Apps & E-Business Suite » Responsibilities and Forms Access (merged 2 threads)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Responsibilities and Forms Access (merged 2 threads) [message #259382]

Wed, 15 August 2007 07:11

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

Report message to a moderator

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

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

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

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

Report message to a moderator

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

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

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

Report message to a moderator

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

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.

Report message to a moderator

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

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

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

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.

Report message to a moderator

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

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

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

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.

Report message to a moderator

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

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

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

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

Report message to a moderator

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

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

Report message to a moderator

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

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

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

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Please don't cross post.
Please format your posts.

By
Vamsi

Report message to a moderator

Previous Topic:	OCP Certification
Next Topic:	This is regarding Descriptive Flex Field.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jul 06 17:08:00 CDT 2024