FND_PROFILE.save Example

This simple script shows how to use FND_PROFILE.save function. This could be of some use when you need to save multiple profile options for various users/responsibilities. The script is

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  l_resp_id     NUMBER;
  l_resp_app_id NUMBER;
  l_success     BOOLEAN;
BEGIN
  --
  -- This will set value of profile option 'ONT_DEBUG_LEVEL' at site level to 1
  --
  l_success := FND_PROFILE.save
               ( x_name                 => 'ONT_DEBUG_LEVEL'
               , x_value                => 1
               , 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 'ONT_DEBUG_LEVEL' at Application level to 1
  --
  l_success := FND_PROFILE.save
               ( x_name                 => 'ONT_DEBUG_LEVEL'
               , x_value                => 1
               , x_level_name           => 'APPL'
               , x_level_value          => 660 -- it is order management application
               , x_level_value_app_id   => NULL
               ) ;
  IF l_success
  THEN
     DBMS_OUTPUT.put_line('Profile Updated successfully at Application Level');
  ELSE
     DBMS_OUTPUT.put_line('Profile Update Failed at Application Level. Error:'||sqlerrm);
  END IF;
  --
  -- This will set value of profile option 'ONT_DEBUG_LEVEL' at Responsibility level to 1
  --
  SELECT responsibility_id
       , application_id
    INTO l_resp_id
       , l_resp_app_id
    FROM fnd_responsibility
   WHERE responsibility_key = 'ORDER_MGMT_SUPER_USER'
   ;
  l_success := FND_PROFILE.save
               ( x_name                 => 'ONT_DEBUG_LEVEL'
               , x_value                => 1
               , x_level_name           => 'RESP'
               , x_level_value          => l_resp_id -- it is responsibility id
               , x_level_value_app_id   => l_resp_app_id
               ) ;
  IF l_success
  THEN
     DBMS_OUTPUT.put_line('Profile Updated successfully at Responsibility Level');
  ELSE
     DBMS_OUTPUT.put_line('Profile Update Failed at Responsibility Level. Error:'||sqlerrm);
  END IF;
  --
  -- This will set value of profile option 'ONT_DEBUG_LEVEL' at User level to 1
  --
  l_success := FND_PROFILE.save
               ( x_name                 => 'ONT_DEBUG_LEVEL'
               , x_value                => 1
               , x_level_name           => 'USER'
               , x_level_value          => 14304 -- Some User Id
               , x_level_value_app_id   => NULL
               ) ;
  IF l_success
  THEN
     DBMS_OUTPUT.put_line('Profile Updated successfully at User Level');
  ELSE
     DBMS_OUTPUT.put_line('Profile Update Failed at User Level. Error:'||sqlerrm);
  END IF;
  --
  -- Commit is needed because this function will not commit
  --
  Commit;
END;
/