SELECT p.profile_option_name short_name, n.user_profile_option_name NAME,Query que identifica o SHORT_NAME que usaremos para alterar usando a API da ORACLE: Logo abaixo segue o bloco:
DECODE (v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10007, 'SERVRESP',
'UnDef'
) level_set,
DECODE (TO_CHAR (v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.NAME,
'10004', usr.user_name,
'10007', 'Serv/resp',
'UnDef'
) "CONTEXT",
v.profile_option_value VALUE
FROM fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE p.profile_option_id = v.profile_option_id(+)
AND p.profile_option_name = n.profile_option_name
AND UPPER (n.user_profile_option_name) LIKE UPPER ('&Nome_da_profile')
AND usr.user_id(+) = v.level_value
AND rsp.application_id(+) = v.level_value_application_id
AND rsp.responsibility_id(+) = v.level_value
AND app.application_id(+) = v.level_value
AND svr.node_id(+) = v.level_value
AND org.organization_id(+) = v.level_value
ORDER BY short_name, level_set;
DECLARE
stat boolean;
BEGIN
dbms_output.disable;
dbms_output.enable(100000);
stat := FND_PROFILE.SAVE('Valor SHORT_NAME', 'Valor a ser inserido', 'SITE');
IF stat THEN
dbms_output.put_line( 'Stat = TRUE - profile atualizada' );
ELSE
dbms_output.put_line( 'Stat = FALSE - profile não atualizada' );
END IF;
commit;
END;
