Using API FND_PROFILE.save to update profile from backend (转)

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' 

 

posted @ 2013-03-12 16:16 郭振斌 阅读(...) 评论(...) 编辑 收藏