Updating Profile Options from Backend using API in Oracle APPS/EBS
In This post I am sharing how to update the profiles without opening forms.
USE the API: FND_PROFILE
The package FND_PROFILE can be found in file AFPFPROS.pls
Note: FND_PROFILE is not public interface. Use of this API is considered a customization and should be tested in a test environment.
FND_PROFILE.SAVE - sets the value of a profile option permanently to the database,
at any level. This can be used at runtime or during patching.
This routine will not actually commit the changes; We have to run commit separately.
The levels are: 'SITE', 'APPL', 'RESP', or 'USER'.
P_NAME= Profile name should be Short Name
Usage:
FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'SITE');
FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'APPL', 321532);
FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'RESP', 321532, 345234);
FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'USER', 123321);
returns: TRUE if successful, FALSE if failure.
To update a Profile Option value at Site level, you need to run the SQL Script below:
Declare
value Boolean;
Begin
value := fnd_profile.save('APPS_DATABASE_ID','<new_value>','SITE');
End;
===============
Example Code
===============
DECLARE
stat boolean;
BEGIN
dbms_output.disable;
dbms_output.enable(100000);
stat := FND_PROFILE.SAVE('GUEST_USER_PWD', 'GUEST/ORACLE', '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;
Description of the FND_PROFILE.SAVE function parameters:
Function SAVE(
X_NAME in varchar2, /* Profile name you are setting Short Name*/
X_VALUE in varchar2, /* Profile value you are setting */
X_LEVEL_NAME in varchar2, /* Level that you're setting at:
'SITE','APPL','RESP','USER', etc. */
X_LEVEL_VALUE in varchar2 default NULL,
/* Level value that you are setting at,
e.g. user id for 'USER' level.
X_LEVEL_VALUE is not used at site level.
Responsibility id in case RESP*/
X_LEVEL_VALUE_APP_ID in varchar2 default NULL,
/* Used for 'RESP' and 'SERVRESP' level;
Resp Application_Id. */
X_LEVEL_VALUE2 in varchar2 default NULL
/* 2nd Level value that you are setting at.
This is for the 'SERVRESP' hierarchy. */
) return boolean;
Example for update profile as responsibility level
set serveroutput on;
DECLARE
stat boolean;
CURSOR c
IS
select frt.responsibility_id,frt.application_id from fnd_responsibility_tl frt,fnd_responsibility fr where frt.responsibility_name like '%XX_CUST%' and frt.language='US'
and fr.responsibility_id=frt.responsibility_id;
--and frt.responsibility_name in ('AP XX_CUST Advance User','AP XX_CUST IT Super User'); --in case you want to do for specific responsibilities
BEGIN
dbms_output.disable;
dbms_output.enable(100000);
for i in c
loop
stat := FND_PROFILE.SAVE('&pofile_name', '&p_value', 'RESP',i.responsibility_id,i.application_id);
IF stat THEN
dbms_output.put_line( 'Stat = TRUE - profile updated' );
ELSE
dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );
END IF;
end loop;
commit;
END;
Post a Comment
Post a Comment