This discussion is archived
6 Replies Latest reply: Aug 22, 2012 4:28 AM by 933676 RSS

Profile values for specifically if  enter section name and product name

933676 Newbie
Currently Being Moderated
I have tried to get Profile values with the following query,

SELECT *
FROM fnd_profile_options_vl 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 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

Suppose I need to populate the profile values for specifically if I enter section name and product name.
How can I do that ???
  • 1. Re: Profile values for specifically if  enter section name and product name
    Sanjay Desai Journeyer
    Currently Being Moderated
    Hi,

    You just add one more condition in your where clause for specifc product name .
    For example , Say for product = 'Inventory' then add following in your SQL and run.

    AND APP.PRODUCT_CODE = 'INV'

    HTH
    Sanjay
  • 2. Re: Profile values for specifically if  enter section name and product name
    933676 Newbie
    Currently Being Moderated
    Hi Sanjay,

    Thanks for the reply. I need all the profile options for a particular product. So using which field in the products table, I can add a relation to these profile tables?I am new to ebs.
  • 3. Re: Profile values for specifically if  enter section name and product name
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    Ep wrote:
    Hi Sanjay,

    Thanks for the reply. I need all the profile options for a particular product. So using which field in the products table, I can add a relation to these profile tables?I am new to ebs.
    Please see the docs referenced in this thread -- How to get profile values

    Thanks,
    Hussein
  • 4. Re: Profile values for specifically if  enter section name and product name
    933676 Newbie
    Currently Being Moderated
    Hussein,
    Thanks, I have gone through the documentation,I can get all profile values.But how can I get the profile values and names that is related to/affects a particular product.
    Is there any connection between products/items with profile option.
    Thanks again.

    Edited by: Ep on Aug 21, 2012 8:59 PM
  • 5. Re: Profile values for specifically if  enter section name and product name
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    Thanks, I have gone through the documentation,I can get all profile values.But how can I get the profile values and names that is related to/affects a particular product.
    Is there any connection between products/items with profile option.
    No. However, you may run the same queries using the follows:

    For example ..

    For GL --> PROFILE_OPTION_NAME like '%GL%'
    For AR --> PROFILE_OPTION_NAME like '%AR%'

    And so on.

    Please note that the above will not return all profile options that affect (or related to) specific module.

    You can also get the list of profile option which are set at the Application/Responsibility level.

    Thanks,
    Hussein
  • 6. Re: Profile values for specifically if  enter section name and product name
    933676 Newbie
    Currently Being Moderated
    Hussein Sawwan wrote:
    Thanks, I have gone through the documentation,I can get all profile values.But how can I get the profile values and names that is related to/affects a particular product.
    Is there any connection between products/items with profile option.
    No. However, you may run the same queries using the follows:

    For example ..

    For GL --> PROFILE_OPTION_NAME like '%GL%'
    For AR --> PROFILE_OPTION_NAME like '%AR%'

    And so on.

    Please note that the above will not return all profile options that affect (or related to) specific module.

    You can also get the list of profile option which are set at the Application/Responsibility level.

    Thanks,
    Hussein
    >
    Thanks, I have gone through the documentation,I can get all profile values.But how can I get the profile values and names that is related to/affects a particular product.
    Is there any connection between products/items with profile option.
    No. However, you may run the same queries using the follows:

    For example ..

    For GL --> PROFILE_OPTION_NAME like '%GL%'
    For AR --> PROFILE_OPTION_NAME like '%AR%'

    And so on.

    Please note that the above will not return all profile options that affect (or related to) specific module.

    You can also get the list of profile option which are set at the Application/Responsibility level.

    Thanks,
    Hussein
    Hussein ,

    Can I use following query to retrieve the profile values for an item with m.msite_id  =10218 AND s.section_id    =10591 and i.organization_id = 204 and i.inventory_item_id =66567

    select distinct *
    FROM fnd_profile_options_vl po,
    fnd_profile_option_values pov,
    fnd_user usr,
    fnd_application app,
    fnd_responsibility rsp,
    fnd_nodes svr,
    hr_operating_units org,
    ibe_msites_tl m,
    ibe_dsp_sections_tl s,
    mtl_system_items_b i
    where
    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

    and m.msite_id =10218
    AND s.section_id =10591
    and i.organization_id = 204
    and i.inventory_item_id =66567

    Anything wrong here???
    Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points