Syntax
FND_PROFILE.SAVE(,
,
,
,
);
Example
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_user_id NUMBER;
l_resp_id NUMBER;
l_resp_app_id NUMBER;
l_success BOOLEAN;
BEGIN
l_user_id := 1068; --****Use your user id to replace,1068 is MFG***
--
-- This will set value of profile option \'INV_DEBUG_LEVEL\' at SITE level to 11
--
l_success := FND_PROFILE.save
( x_name => \'INV_DEBUG_LEVEL\'
, x_value => 11
, x_level_name => \'SITE\'
, x_level_value => NULL
, x_level_value_app_id => NULL
) ;
IF l_success
THEN
DBMS_OUTPUT.put_line(\'Profile Updated successfully at site Level\');
ELSE
DBMS_OUTPUT.put_line(\'Profile Update Failed at site Level. Error:\'||sqlerrm);
END IF;
--
-- This will set value of profile option \'INV_DEBUG_LEVEL\' at RESP level to 11
--
SELECT responsibility_id
, application_id
INTO l_resp_id
, l_resp_app_id
FROM fnd_responsibility
WHERE responsibility_key = \'INVENTORY\';
l_success := FND_PROFILE.save
( x_name => \'INV_DEBUG_LEVEL\'
, x_value => 11
, x_level_name => \'RESP\'
, x_level_value => l_resp_id --responsibility_id
, x_level_value_app_id => l_resp_app_id --401
) ;
IF l_success
THEN
DBMS_OUTPUT.put_line(\'Profile Updated successfully at responsiblity Level\');
ELSE
DBMS_OUTPUT.put_line(\'Profile Update Failed at site Level. Error:\'||sqlerrm);
END IF;
--
-- This will set value of profile option \'INV_DEBUG_LEVEL\' at user level to 11
--
l_success := FND_PROFILE.save
( x_name => \'INV_DEBUG_LEVEL\'
, x_value => 11
, x_level_name => \'USER\'
, x_level_value => l_user_id
) ;
IF l_success
THEN
DBMS_OUTPUT.put_line(\'Profile Updated successfully at user Level\');
ELSE
DBMS_OUTPUT.put_line(\'Profile Update Failed at site Level. Error:\'||sqlerrm);
END IF;
--Commit is needed because this function will not commit
Commit;
END;
/
Useful Queries
SELECT B.USER_PROFILE_OPTION_NAME,A.*
FROM FND_PROFILE_OPTIONS A,FND_PROFILE_OPTIONS_TL B
WHERE A.PROFILE_OPTION_NAME = B.PROFILE_OPTION_NAME
AND A.PROFILE_OPTION_NAME LIKE \'INV_DEBUG%\'
AND B.LANGUAGE = \'US\';
SELECT C.USER_PROFILE_OPTION_NAME,B.PROFILE_OPTION_NAME,A.*
FROM FND_PROFILE_OPTION_VALUES A,FND_PROFILE_OPTIONS B,FND_PROFILE_OPTIONS_TL C
WHERE B.PROFILE_OPTION_NAME = C.PROFILE_OPTION_NAME
AND A.PROFILE_OPTION_ID = B.PROFILE_OPTION_ID
AND A.APPLICATION_ID = B.APPLICATION_ID
AND C.LANGUAGE = \'US\'
AND Upper(C.USER_PROFILE_OPTION_NAME) LIKE Upper(\'INV%Debug%Trace%\');
10001: \'Site\',
10002: \'Application\',
10003: \'Responsibility\',
10004: \'User\',
10005: \'Server\'
Related Topic: Oracle EBS中查询Profile的各种SQL
转载于:http://blog.itpub.net/26687597/viewspace-1204626/