HZ PARTIES LINK TO ORGANIZATION TABLE LINK

SELECT hrl.country, hroutl_bg.NAME bg, hroutl_bg.organization_id,
       lep.legal_entity_id, lep.NAME legal_entity,
       hroutl_ou.NAME ou_name, hroutl_ou.organization_id org_id,
       hrl.location_id,
       hrl.location_code,
       glev.FLEX_SEGMENT_VALUE
  FROM xle_entity_profiles lep,
       xle_registrations reg,
       hr_locations_all hrl,
       hz_parties hzp,
       fnd_territories_vl ter,
       hr_operating_units hro,
       hr_all_organization_units_tl hroutl_bg,
       hr_all_organization_units_tl hroutl_ou,
       hr_organization_units gloperatingunitseo,
       gl_legal_entities_bsvs glev
 WHERE lep.transacting_entity_flag = 'Y'
   AND lep.party_id = hzp.party_id
   AND lep.legal_entity_id = reg.source_id
   AND reg.source_table = 'XLE_ENTITY_PROFILES'
   AND hrl.location_id = reg.location_id
   AND reg.identifying_flag = 'Y'
  AND ter.territory_code = hrl.country
   AND lep.legal_entity_id = hro.default_legal_context_id
   AND gloperatingunitseo.organization_id = hro.organization_id
   AND hroutl_bg.organization_id = hro.business_group_id
   AND hroutl_ou.organization_id = hro.organization_id
   AND glev.legal_entity_id = lep.legal_entity_id

/* Formatted on 7/28/2015 7:48:07 PM (QP5 v5.240.12305.39446) */
SELECT hzp.*
  FROM hz_parties hzp,  --Table
       xle_entity_profiles lep, --Table
       xle_registrations reg,
       hr_locations_all hrl,
       fnd_territories_vl ter,
       hr_operating_units hro   --Table
 WHERE     1 = 1
       AND lep.party_id = hzp.party_id --Link  
       AND lep.legal_entity_id = reg.source_id
       AND hrl.location_id = reg.location_id
       AND ter.territory_code = hrl.country
       AND lep.legal_entity_id = hro.default_legal_context_id  --Link

Extract profile option values for all possible levels

In Oracle E-Business Suite profile options can be set on several levels:





    Site
    Application
    Responsibility
    Server
    Server with Responsibility
    Organization
    User

When needed one and the same profile option can be assigned to different levels. For example, when you implement Global Security Profiles to create access control on Operation Units - every responsibility for an Operating Unit may need a setting for profile options MO: Security Profile and HR: Security Profile. In the form which is used to set profile options all those different responsibilities can't be seen at once.

In that case I use the SQL statement below to quickly provide me a list of the values of a profile option for all levels.
The profile option name (column profile_option_name from table applsys.fnd_profile_options) can be found within the definition of the profile itself through responsibility Application Developer - menu Profile.


Here's the SQL to provide you the values on all levels of a specific profile.

SELECT
    SUBSTR(e.profile_option_name,1,25) INTERNAL_NAME,
    SUBSTR(pot.user_profile_option_name,1,60) NAME_IN_FORMS,
    DECODE(a.level_id,10001,'Site',10002,'Application',10003,'Resp',
    10004,'User',10005,'Server',10007,'Server+Resp',a.level_id) LEVELl,
    DECODE(a.level_id,10001,'Site',10002,c.application_short_name,
    10003,b.responsibility_name,10004,d.user_name,10005,n.node_name,
    10007,m.node_name||' + '||b.responsibility_name,a.level_id) LEVEL_VALUE,
    NVL(a.profile_option_value,'Is Null') VALUE,
    to_char(a.last_update_date, 'DD-MON-YYYY HH24:MI') LAST_UPDATE_DATE,
    dd.USER_NAME LAST_UPDATE_USER
FROM
    applsys.fnd_profile_option_values a,
    applsys.fnd_responsibility_tl b,
    applsys.fnd_application c,
    applsys.fnd_user d,
    applsys.fnd_profile_options e,
    applsys.fnd_nodes n,
    applsys.fnd_nodes m,
    applsys.fnd_responsibility_tl x,
    applsys.fnd_user dd,
    applsys.fnd_profile_options_tl pot
WHERE
    e.profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'    AND e.PROFILE_OPTION_NAME = pot.profile_option_name (+)
    AND e.profile_option_id = a.profile_option_id (+)
    AND a.level_value = b.responsibility_id (+)
    AND a.level_value = c.application_id (+)
    AND a.level_value = d.user_id (+)
    AND a.level_value = n.node_id (+)
    AND a.LEVEL_VALUE_APPLICATION_ID = x.responsibility_id (+)
    AND a.level_value2 = m.node_id (+)
    AND a.LAST_UPDATED_BY = dd.USER_ID (+)
    AND pot.LANGUAGE = 'US'
ORDER BY
    e.profile_option_name
    
                 ========================================================

SELECT b.name,

       b.organization_id,

       level_value,

       PROFILE_OPTION_VALUE

  FROM fnd_profile_option_values a, hr_operating_units b

 WHERE 1 = 1

 AND a.level_value = b.ORGANIZATION_ID

 

Extract profile option values for all possible levels

Author: Cyriel van Schaik at 12:34 AM

 0 0 0 326

In Oracle E-Business Suite profile options can be set on several levels:

• Site
• Application
• Responsibility
• Server
• Server with Responsibility
• Organization
• User

When needed one and the same profile option can be assigned to different levels. For example, when you implement Global Security Profiles to create access control on Operation Units - every responsibility for an Operating Unit may need a setting for profile options MO: Security Profile and HR: Security Profile. In the form which is used to set profile options all those different responsibilities can't be seen at once.

In that case I use the SQL statement below to quickly provide me a list of the values of a profile option for all levels. 
The profile option name (column profile_option_name from table applsys.fnd_profile_options) can be found within the definition of the profile itself through responsibility Application Developer - menu Profile.

Here's the SQL to provide you the values on all levels of a specific profile.

SELECT
    SUBSTR(e.profile_option_name,1,25) INTERNAL_NAME,
    SUBSTR(pot.user_profile_option_name,1,60) NAME_IN_FORMS,
    DECODE(a.level_id,10001,'Site',10002,'Application',10003,'Resp',
    10004,'User',10005,'Server',10007,'Server+Resp',a.level_id) LEVELl,
    DECODE(a.level_id,10001,'Site',10002,c.application_short_name,
    10003,b.responsibility_name,10004,d.user_name,10005,n.node_name,
    10007,m.node_name||' + '||b.responsibility_name,a.level_id) LEVEL_VALUE,
    NVL(a.profile_option_value,'Is Null') VALUE,
    to_char(a.last_update_date, 'DD-MON-YYYY HH24:MI') LAST_UPDATE_DATE,
    dd.USER_NAME LAST_UPDATE_USER
FROM
    applsys.fnd_profile_option_values a,
    applsys.fnd_responsibility_tl b,
    applsys.fnd_application c,
    applsys.fnd_user d,
    applsys.fnd_profile_options e,
    applsys.fnd_nodes n,
    applsys.fnd_nodes m,
    applsys.fnd_responsibility_tl x,
    applsys.fnd_user dd,
    applsys.fnd_profile_options_tl pot
WHERE
    e.profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'    AND e.PROFILE_OPTION_NAME = pot.profile_option_name (+)
    AND e.profile_option_id = a.profile_option_id (+)
    AND a.level_value = b.responsibility_id (+)
    AND a.level_value = c.application_id (+)
    AND a.level_value = d.user_id (+)
    AND a.level_value = n.node_id (+)
    AND a.LEVEL_VALUE_APPLICATION_ID = x.responsibility_id (+)
    AND a.level_value2 = m.node_id (+)
    AND a.LAST_UPDATED_BY = dd.USER_ID (+)
    AND pot.LANGUAGE = 'US'
ORDER BY
    e.profile_option_name

- See more at: http://oracleebsapps.blogspot.in/2011/08/extract-profile-option-values-for-all.html#sthash.plOQ44mQ.dpuf