pumpkinpietea/PWD_INITIALIZATION.sql

## PWD_INITIALIZATION.sql
create or replace procedure PWD_INITIALIZATION(p_user_id in NUMBER)
IS
l_username VARCHAR2(2000);
l_useremail VARCHAR2(100);
l_sendemail VARCHAR2(100);  -- need to set FROM address
l_fullname VARCHAR2(30);
l_ini_pwd VARCHAR2(8);
l_body VARCHAR2(2000) := 'This is HTML mail. If you cannot see the mail body, please contact XXXX.';
l_body_html VARCHAR2(4000);
l_subj VARCHAR2(300);
BEGIN
--- Getting user info for sending notification
select USERNAME,EMAIL_ADDRESS,FULL_NAME
into l_username,l_useremail,l_fullname
from USERMASTER
where USER_ID = p_user_id;
-- Create initial password
select dbms_random.string('A',8) into l_ini_pwd from sys.dual;
-- Set initial password in USERMASTER
UPDATE USERMASTER SET PWD = MD5_HASH(l_username,l_ini_pwd)
                           ,PWD_EXPIRE = '1'   ---Set EXPIRE flag
                where USER_ID = p_user_id;
-- sending temporary pwd.
--Creating EMAIL subject
l_subj := 'Temporary PWD notification.';
-- Creating EMAIL HTML BODY
l_body_html := '<HTML><BODY><PRE><span style="font-family:monospace;"><BR>';
l_body_html := l_body_html||'Dear Sir or Madam,<BR><BR>';
l_body_html := l_body_html||'Your temporaly Password is '||l_ini_pwd||' . <BR><BR>';
l_body_html := l_body_html||'Go to --> http://--------';
-- End part of Email body
  l_body_html := l_body_html||'Regards,<BR>';
  l_body_html := l_body_html||'</span></PRE></BODY></HTML>';
-- send email with apex_mail
   wwv_flow_api.set_security_group_id;
   apex_mail.send (p_to     => l_useremail,
                   p_from   => l_sendemail,
                   p_body   => l_body,
                   p_body_html => l_body_html,
                   p_subj   => l_subj);
END;
	create or replace procedure PWD_INITIALIZATION(p_user_id in NUMBER)
	IS
	l_username VARCHAR2(2000);
	l_useremail VARCHAR2(100);
	l_sendemail VARCHAR2(100); -- need to set FROM address
	l_fullname VARCHAR2(30);
	l_ini_pwd VARCHAR2(8);
	l_body VARCHAR2(2000) := 'This is HTML mail. If you cannot see the mail body, please contact XXXX.';
	l_body_html VARCHAR2(4000);
	l_subj VARCHAR2(300);
	BEGIN
	--- Getting user info for sending notification
	select USERNAME,EMAIL_ADDRESS,FULL_NAME
	into l_username,l_useremail,l_fullname
	from USERMASTER
	where USER_ID = p_user_id;
	-- Create initial password
	select dbms_random.string('A',8) into l_ini_pwd from sys.dual;
	-- Set initial password in USERMASTER
	UPDATE USERMASTER SET PWD = MD5_HASH(l_username,l_ini_pwd)
	,PWD_EXPIRE = '1' ---Set EXPIRE flag
	where USER_ID = p_user_id;
	-- sending temporary pwd.
	--Creating EMAIL subject
	l_subj := 'Temporary PWD notification.';
	-- Creating EMAIL HTML BODY
	l_body_html := '<HTML><BODY><PRE><span style="font-family:monospace;"><BR>';
	l_body_html := l_body_html\|\|'Dear Sir or Madam,<BR><BR>';
	l_body_html := l_body_html\|\|'Your temporaly Password is '\|\|l_ini_pwd\|\|' . <BR><BR>';
	l_body_html := l_body_html\|\|'Go to --> http://--------';
	-- End part of Email body
	l_body_html := l_body_html\|\|'Regards,<BR>';
	l_body_html := l_body_html\|\|'</span></PRE></BODY></HTML>';
	-- send email with apex_mail
	wwv_flow_api.set_security_group_id;
	apex_mail.send (p_to => l_useremail,
	p_from => l_sendemail,
	p_body => l_body,
	p_body_html => l_body_html,
	p_subj => l_subj);
	END;