Orphan Profiles in Oracle Apps

You must be wondering what's that. Yup it does exists sometime in our clone instances and it takes us to somewhere else. Debugging and troubleshooting takes long time with no results. Check the following if you have any rows results out of following query that means you have someone which you don't want in your profiles:

col profile_option_name format a40
col "Orphan ID" format a10
select
v.level_value "Server Orphan",
v.level_value2 "ServResp Orphan",
decode(v.level_id,
10005, 'Server',
10007, 'ServResp',
'Other') LEVEL_SET,
p.Profile_option_name
from
fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n
where
p.profile_option_name = n.profile_option_name
and p.profile_option_id = v.profile_option_id (+)
and
( /* check Server level */
(
v.level_id=10005
and v.level_value > 0
and v.level_value
not in
(
select
f.node_id
from
fnd_nodes f
)
)
or /* check ServResp level */
(
v.level_id=10007
and (v.level_value2 is not null)
and (v.level_value2 > 0)
and v.level_value2
not in
(
select
f.node_id
from
fnd_nodes f
)
)
);

Now what, if you get some rows: Do this

alter trigger APPS.FND_PROFILE_OPTION_VALUES_AD disable;

delete from fnd_profile_option_values
where
level_id = 10005
and level_value in (Value of Node);

commit;

alter trigger APPS.FND_PROFILE_OPTION_VALUES_AD enable;

Happy Troubleshooting !!!