Thursday, June 02, 2016

Query to Find Org id based on Responsibility id

SELECT   pers.organization_id
    FROM apps.fnd_profile_options_vl fpo,
         apps.fnd_profile_option_values fpov,
         apps.fnd_responsibility_vl frv,
         apps.per_security_organizations_v pers
   WHERE frv.responsibility_id = :resp_id
     AND fpov.level_value = frv.responsibility_id
     AND fpo.profile_option_id = fpov.profile_option_id
     AND fpo.user_profile_option_name = 'MO: Security Profile'
     AND fpov.profile_option_id = fpo.profile_option_id
     AND fpov.profile_option_value = pers.security_profile_id
ORDER BY frv.responsibility_name;







SELECT frv.responsibility_name, fpov.profile_option_value org_id, hou.NAME
FROM apps.hr_organization_units hou,
apps.fnd_profile_options_vl fpo,
apps.fnd_profile_option_values fpov,
apps.fnd_responsibility_vl frv
WHERE frv.responsibility_name = :p_resp_name
AND fpov.level_value = frv.responsibility_id
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.user_profile_option_name = 'MO: Operating Unit'
AND fpov.profile_option_id = fpo.profile_option_id
AND hou.organization_id = TO_NUMBER (fpov.profile_option_value)
ORDER BY frv.responsibility_name

No comments:

Post a Comment

Clear BNE Cache for WebADI Changes

It Sometime happens that WebAdi Changes doesn't reflect once migrated in controlled instances. Here are the quick steps(Generally perfor...