Set Profile Option Value from back end

on Monday, June 22, 2009

The PROFILE_OPTION_NAME can be found by the SQL statement,

SELECT profile_option_name

FROM fnd_profile_options_tl

WHERE user_profile_option_name LIKE 'MO%'


It returns more than one row but i can make out that "ORG_ID" is the PROFILE_OPTION_NAME for MO: Operating Unit.

Inorder to know the Org_ID of the Org whose value is to be set in MO: Operating Unit the following SQL statement is used.

SELECT organization_id, NAME

FROM hr_all_organization_units;


From the above query the default Operating Unit is found and the below code below is used to set the profile option value using fnd_profile.save


DECLARE

stat BOOLEAN;


BEGIN

DBMS_OUTPUT.DISABLE;

DBMS_OUTPUT.ENABLE (100000);

stat := fnd_profile.SAVE ('ORG_ID', 286, 'SITE');


IF stat THEN

DBMS_OUTPUT.put_line ('Stat = TRUE - profile updated');

ELSE

DBMS_OUTPUT.put_line ('Stat = FALSE - profile NOT updated');

END IF;


COMMIT;


END;