Regenerate R12 password and email?
Posted: Wed Dec 11, 2019 6:40 am
The following sample was to send email based on user request by SMS using APEX emails while you can change as per your scenario,
PROCEDURE r12_new_password (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
argument1 IN VARCHAR2 -- userid
)
IS
v_user_name VARCHAR2 (30) := UPPER (argument1);
v_new_password VARCHAR2 (6);
v_status BOOLEAN;
l_titles VARCHAR2 (32767);
l_footer VARCHAR2 (32767);
l_body CLOB;
l_req_data_table CLOB;
l_id NUMBER;
l_blob BLOB;
ARRAY apex_application_global.vc_arr2;
-- www.erpstuff.com
-- Code for learning only
-- SMS format is R12PASS XXXXXXX (XXXXXX is user id)
BEGIN
FOR i
IN ( select * from sms.inbox where SUBSTR(upper(MESSAGE),1,7) = 'R12PASS' and answered is null) LOOP
ARRAY := apex_util.string_to_table (upper(replace(replace(i.MESSAGE, ' ',' '), ' ', ' ')), ' ');
v_user_name := array(2);
SELECT DBMS_RANDOM.string ('A', 6) INTO v_new_password FROM DUAL;
fnd_user_pkg.updateuser (
x_user_name => v_user_name,
x_owner => 'CUST',
x_unencrypted_password => v_new_password,
x_password_date => TO_DATE ('2', 'J'));
update sms.inbox set answered = 'Y' where id = i.id;
fnd_file.put_line (fnd_file.LOG, v_user_name || ' - ' || v_new_password);
COMMIT;
fnd_file.put_line (fnd_file.LOG,
v_user_name || ' - ' || v_new_password);
l_titles :=
'<html><body> <b><br>Dear '
|| v_user_name
|| ',</b><p>An SMS request was sent by cell no +' || i.phoneno || ' to reset password of Oracle Applications R12. A new one time password is generated <b>' || v_new_password || '</b> so please use this and set your new password to login. If you feel that this reset request was not sent by you then kindly check cell no or immediately report to IT Department. Thanks</p>';
l_footer :=
'<b>Best Regards,</b> <br>ERPstuff.com.</body></html>';
l_body := l_titles || l_footer;
l_id :=
APEX_MAIL.SEND (
p_from => 'admin@erpstuff.com',
p_to => v_user_name || '@erpstuff.com',
p_cc => 'info@erpstuff.com',
p_subj => 'Oracle R12 one time password',
p_body => l_body,
p_body_html => l_body);
apex_mail.push_queue;
END LOOP;
END;
PROCEDURE r12_new_password (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
argument1 IN VARCHAR2 -- userid
)
IS
v_user_name VARCHAR2 (30) := UPPER (argument1);
v_new_password VARCHAR2 (6);
v_status BOOLEAN;
l_titles VARCHAR2 (32767);
l_footer VARCHAR2 (32767);
l_body CLOB;
l_req_data_table CLOB;
l_id NUMBER;
l_blob BLOB;
ARRAY apex_application_global.vc_arr2;
-- www.erpstuff.com
-- Code for learning only
-- SMS format is R12PASS XXXXXXX (XXXXXX is user id)
BEGIN
FOR i
IN ( select * from sms.inbox where SUBSTR(upper(MESSAGE),1,7) = 'R12PASS' and answered is null) LOOP
ARRAY := apex_util.string_to_table (upper(replace(replace(i.MESSAGE, ' ',' '), ' ', ' ')), ' ');
v_user_name := array(2);
SELECT DBMS_RANDOM.string ('A', 6) INTO v_new_password FROM DUAL;
fnd_user_pkg.updateuser (
x_user_name => v_user_name,
x_owner => 'CUST',
x_unencrypted_password => v_new_password,
x_password_date => TO_DATE ('2', 'J'));
update sms.inbox set answered = 'Y' where id = i.id;
fnd_file.put_line (fnd_file.LOG, v_user_name || ' - ' || v_new_password);
COMMIT;
fnd_file.put_line (fnd_file.LOG,
v_user_name || ' - ' || v_new_password);
l_titles :=
'<html><body> <b><br>Dear '
|| v_user_name
|| ',</b><p>An SMS request was sent by cell no +' || i.phoneno || ' to reset password of Oracle Applications R12. A new one time password is generated <b>' || v_new_password || '</b> so please use this and set your new password to login. If you feel that this reset request was not sent by you then kindly check cell no or immediately report to IT Department. Thanks</p>';
l_footer :=
'<b>Best Regards,</b> <br>ERPstuff.com.</body></html>';
l_body := l_titles || l_footer;
l_id :=
APEX_MAIL.SEND (
p_from => 'admin@erpstuff.com',
p_to => v_user_name || '@erpstuff.com',
p_cc => 'info@erpstuff.com',
p_subj => 'Oracle R12 one time password',
p_body => l_body,
p_body_html => l_body);
apex_mail.push_queue;
END LOOP;
END;