How to get the profile option value in Oracle apps

Hi friends, we are going to discuss about how to get the profile option value in Oracle apps. We will share the detail steps to find out the profile option and profile values in oracle apps. In this post , We will share the all possible ways in oracle apps to get any profile option values. Profile options helps to stores the system values which helps to get the run time system values in oracle apps. Oracle apps has itself provided hundreds of profile options to control the oracle application behavior.We will share the complete method to use the current profile option values in oracle apps and we will also share the detail sql query to get the profile options values. Please find below the complete detail about how to get the profile option value in Oracle apps.

How to get the profile option value in Oracle apps

2 Ways to get the profile option value in Oracle apps

1. Using fnd_profile.value function to get the profile value.

2. Using SQL query to get the profile option value.

Using fnd_profile.value function to get the profile value

Here below, we are sharing the method to get the profile option value.

I am sharing below the list of profiles which we can get using these below syntax.

For Example, If we have to get the Operating unit id then we can use this below syntax to get the Operating unit Id(ORG ID).

fnd_profile.value('ORG_ID')

For Example, If we have to get the Inventory Org ID then we can use this below syntax to get the Inventory Org Id(MFG_ORGANIZATION_ID)

fnd_profile.value('MFG_ORGANIZATION_ID')

fnd_profile.value('LOGIN_ID')

fnd_profile.value('USER_ID')

fnd_profile.value('USERNAME')

fnd_profile.value('CONCURRENT_REQUEST_ID')

fnd_profile.value('GL_SET_OF_BKS_ID')

fnd_profile.value('SO_ORGANIZATION_ID')

fnd_profile.value('APPL_SHRT_NAME')

fnd_profile.value('RESP_NAME')

fnd_profile.value('RESP_ID')

fnd_profile.value('PER_BUSINESS_GROUP_ID')

fnd_profile.value('GL_SET_OF_BKS_ID')

Using SQL query to get the profile option value

Here below , we are sharing the sql query to get the profile option values in oracle apps.

7 Tables Uses in the Profile Option Value in Oracle Apps

1.FND_PROFILE_OPTIONS

2.FND_PROFILE_OPTION_VALUES

3.fnd_user

4.fnd_application

5.fnd_responsibility

6.fnd_nodes

7.hr_operating_units

Query to get the profile option value in Oracle apps

SELECT po.profile_option_name "NAME",  

decode(to_char(pov.level_id),

'10001', 'SITE',

'10002', 'APP',

'10003', 'RESP',

'10005', 'SERVER',

'10006', 'ORG',

'10004', 'USER', '???') "LEV",

pov.profile_option_value

FROM   FND_PROFILE_OPTIONS po,

FND_PROFILE_OPTION_VALUES pov,

fnd_user usr,

fnd_application app,

fnd_responsibility rsp,

fnd_nodes svr,

hr_operating_units org

WHERE  1 =1

and    po.profile_option_name LIKE '&PROFILE%%'

AND    pov.application_id = po.application_id

AND    pov.profile_option_id = po.profile_option_id

AND    usr.user_id (+) = pov.level_value

AND    rsp.application_id (+) = pov.level_value_application_id

AND    rsp.responsibility_id (+) = pov.level_value

AND    app.application_id (+) = pov.level_value

AND    svr.node_id (+) = pov.level_value

AND    org.organization_id (+) = pov.level_value

ORDER BY "NAME", pov.level_id, "VALUE";

 

How to get the profile option value in Oracle apps