Saturday, May 24, 2008

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;
/

2 comments:

  1. Hi,

    I have followed your blog to update profile options. It worked fine for updating at site level.

    Need some help to update at responsibility level.

    "set value of profile option 'ICX: Forms Launcher' at Responsibility level for Responsibility 'Workflow Administrator Web (New)' to NULL"

    I have modified ur procedure as below...

    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 'ICX: Forms Launcher' at Responsibility level for Responsibility 'Workflow Administrator Web (New)' to NULL
    DBMS_OUTPUT.put_line('---');
    DBMS_OUTPUT.put_line('Updating value of profile option "ICX: Forms Launcher" at Responsibility level for Responsibility "Workflow Administrator Web (New)" to NULL');
    DBMS_OUTPUT.put_line('---');
    --
    SELECT responsibility_id
    , application_id
    INTO l_resp_id
    , l_resp_app_id
    FROM fnd_responsibility
    WHERE responsibility_key = 'FNDWF_ADMIN_WEB_NEW'
    ;
    l_success := FND_PROFILE.save
    ( x_name => 'ICX_FORMS_LAUNCHER'
    , x_value => NULL
    , 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('---');
    DBMS_OUTPUT.put_line('Profile Updated successfully at Responsibility Level');
    DBMS_OUTPUT.put_line('---');
    ELSE
    DBMS_OUTPUT.put_line('---');
    DBMS_OUTPUT.put_line('Profile Update Failed at Responsibility Level. Error:'||sqlerrm);
    DBMS_OUTPUT.put_line('---');
    END IF;
    --



    --
    -- Commit is needed because this function will not commit
    --
    Commit;
    END;
    /


    The PL/SQL is getting executed successfully, but the value for responsibility "Workflow Administrator Web (New)" has not changed to NULL.

    Plz suggest me in this regard


    Thanks,
    Paul

    ReplyDelete
  2. i used your script and it worked for me. i have Oracle Apps ver 11.5.9.

    ReplyDelete