Using API FND_PROFILE.save to update profile from backend （转）

最新推荐文章于 2022-06-13 15:13:50 发布

weixin_34228387

最新推荐文章于 2022-06-13 15:13:50 发布

阅读量146

点赞数

本文介绍了如何使用 Oracle 的 FND_PROFILE 过程来设置不同层级的配置选项，包括站点级、责任级和用户级，并提供了实用的 SQL 查询示例以帮助用户验证配置的有效性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Syntax
FND_PROFILE.SAVE(<Profile_Option_Name>,
                 <Profile_Option_Value>,
                 <Level SITE/APPL/RESP/USER>,
                 <Level_Value>,
                 <Level_Value_App_id>);

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'