Hi All,

I'm new to all Oracle DB and Oracle Apps. I'm trying to modify a stored proc that calls the fnd user update user stored proc.

As a parameter my stored proc excepts a dummy password since this is required for the call to the fnd_usr_pkg. Since I wont know each person's password so that it can be kept the same, I wanted to use the fnd_usr getreencrypted password to hold the user's existing password, then let the update run so that other attributes such as fax field can be updated. Then I wanted to use the setreencrypted password function to use the value I stored before running the update to set the users password back to where it started.

During this process though, I check to see if the unencrypted password parameter passed to the function equals a certain value, if it does then it gets their existing password and tries to set that again. If not then it accepts the value passed as a parameter and sets the users password to this.

As far as I can tell when I pass password5 as the parameter to my code it executes the first getencrypted statement and then sets the users password to password5, but never sets their password back to the users original value.

Am I barking up the wrong tree? I know there is a change password option, is there a way to call out an unencrypted version of their password, store that temporarily in the procedure and then set it back to that value after the update is run?

My code is below. Please Help.



P.S. I've tried commenting out the select statements and the check performed on db_ld still no go.

owner in varchar2,
unencrypted_password in varchar2,
session_number in number default 0,
start_date in date default NULL,
end_date in date default NULL,
last_logon_date in date default NULL,
description in varchar2 default NULL,
password_date in date default NULL,
password_accesses_left in number default NULL,
password_lifespan_accesses in number default NULL,
password_lifespan_days in number default NULL,
employee_id in number default NULL,
email_address in varchar2 default NULL,
fax in varchar2 default NULL,
customer_id in number default NULL,
supplier_id in number default NULL,
user_status in varchar2 default NULL

newpass varchar2(250);
re_reset varchar2(250);
db_ld varchar2(250) :=null;
re_reset_success boolean;


re_reset :='N';

into db_ld
where USER_NAME = upper(user_name);

if (unencrypted_password='password5') then
if (db_ld is not NULL) then
newpass := fnd_user_pkg.getreencryptedpassword(upper(user_nam e), 'helpme');
re_reset :='Y';
end if;
end if;

if( user_status is not NULL) then
if (user_status='Suspend') then
FND_USER_PKG.DisableUser( upper(user_name));
update fnd_user set end_date = NULL
where user_name = upper(user_name);
end if;
FND_USER_PKG.UpdateUser (
x_user_name => user_name,
x_owner => owner,
x_unencrypted_password => unencrypted_password,
x_session_number => session_number,
x_start_date => start_date,
x_end_date => end_date,
x_last_logon_date => last_logon_date,
x_description => description,
x_password_date => password_date,
x_password_accesses_left => password_accesses_left,
x_password_lifespan_accesses => password_lifespan_accesses,
x_password_lifespan_days => password_lifespan_days,
x_employee_id => employee_id,
x_email_address => email_address,
x_fax => fax,
x_customer_id => customer_id,
x_supplier_id => supplier_id);
end if;

if (re_reset='Y') then
re_reset_success := fnd_user_pkg.setreencryptedpassword(upper(user_nam e), newpass, 'helpme');
end if;