Using API FND_PROFILE.save to update profile from backend

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/