Reseting User Password and Send Email to User

This functionality is used to reset the existing user password from back end and send password details to the user 

CREATE OR REPLACE FUNCTION xxhcc_generatePassword(LENGTH IN NUMBER)
RETURN VARCHAR
IS
tmpChr VARCHAR2(1);
tmpPswd VARCHAR2(32767);
tmpnum NUMBER(1);
BEGIN

FOR i IN 1 .. LENGTH
LOOP

SELECT CHR(ROUND(dbms_random.value(48,57),0)) INTO tmpnum FROM dual;
SELECT CHR(ROUND(dbms_random.value(65,90),0)) INTO tmpChr FROM dual;

tmpPswd:=tmpPswd||tmpChr||tmpnum;
END LOOP;

RETURN tmpPswd;
END;

then populated a temp table with the user names i wanted to change and ran the following code:

declare
l_pass varchar(10);
v_notification_id NUMBER;
l_user varchar(100);
CURSOR ss_user IS

SELECT username
FROM
xxhcc.xxhcc_ssuser;
line_rec ss_user%ROWTYPE;

begin

UPDATE FND_USER
SET end_date= NULL
WHERE user_name In (SELECT * FROM xxhcc.xxhcc_ssuser);

FOR line_rec IN ss_user
LOOP
l_user := line_rec.username;
select xxhcc_generatePassword(5) as password into l_pass from dual;
fnd_user_pkg.updateuser(
x_user_name => l_user
, x_owner => ‘CUST’
, x_unencrypted_password => l_pass,
x_password_date => to_date(‘2′,’J’));

—code to send mail
v_notification_id := wf_notification.send (ROLE => l_user,
msg_type => ‘UMXUPWD’,
msg_name => ‘MSG_PASSWORDRESET’,
priority => 1
);
— And set message attributes.
wf_notification.setattrtext (nid => v_notification_id,
aname => ‘PASSWORD’,
avalue => l_pass
);
wf_notification.setattrtext (nid => v_notification_id,
aname => ‘#HIDE_MOREINFO’,
avalue => ‘N’);

end loop;

commit;
end;