set scan on ;
set scan off ;
CREATE OR REPLACE PACKAGE BODY xx_sshr_allocate_resp_pkg IS
--DO NOT RUN THIS WITHOUT CHANGING XXPRD
--REPLACE XXPRD BY RESULT OF BELOW SQL FROM PRODUCTION
--select instance_name from v$instance ;
--Created in Nov 06 by Anil Passi
/*
When By Why
-----------------------------------------------
29Nov06 AnilPassi To allocate responsibilities
01Dec06 Anil Passi To create new users too
Send emails to new users with their password etc
Send emails to existing users that they now have sshr
*/
g_instance_name VARCHAR2(100) := 'JUNK';
g_debug_procedure_context VARCHAR2(50);
g_debug_header_context CONSTANT VARCHAR2(80) := 'xxxx_sshr_allocate_resp_pkg.';
PROCEDURE debug_begin_procedure IS
BEGIN
fnd_log.STRING(log_level => fnd_log.level_statement
,module => g_debug_header_context ||
g_debug_procedure_context
,message => 'Begin ' || g_debug_procedure_context);
IF fnd_global.conc_request_id > 0 AND
fnd_profile.VALUE('AFLOG_ENABLED') = 'Y'
THEN
fnd_file.put_line(which => fnd_file.log
,buff => 'Begin ' || g_debug_procedure_context);
END IF;
END debug_begin_procedure;
PROCEDURE debug_stmt(p_msg IN VARCHAR2) IS
BEGIN
fnd_log.STRING(log_level => fnd_log.level_statement
,module => g_debug_header_context ||
g_debug_procedure_context
,message => p_msg);
IF fnd_global.conc_request_id > 0
THEN
fnd_file.put_line(which => fnd_file.log
,buff => p_msg);
END IF;
END debug_stmt;
PROCEDURE debug_end_procedure IS
BEGIN
fnd_log.STRING(log_level => fnd_log.level_statement
,module => g_debug_header_context ||
g_debug_procedure_context
,message => 'End ' || g_debug_procedure_context);
IF fnd_global.conc_request_id > 0 AND
fnd_profile.VALUE('AFLOG_ENABLED') = 'Y'
THEN
fnd_file.put_line(which => fnd_file.log
,buff => 'End ' || g_debug_procedure_context);
END IF;
END debug_end_procedure;
PROCEDURE set_debug_context(p_procedure_name IN VARCHAR2) IS
BEGIN
g_debug_procedure_context := p_procedure_name;
debug_begin_procedure;
END set_debug_context;
FUNCTION is_user_creation_possible(p_person_id IN INTEGER
,p_xxdp OUT xx_windows_logon_table%ROWTYPE)
RETURN VARCHAR2 IS
CURSOR c_check IS
SELECT xxdp.*
FROM per_people_x ppx, xx_windows_logon_table xxdp
WHERE ltrim(ppx.employee_number
,'0') = ltrim(xxdp.emp_no
,'0')
AND ppx.person_id = p_person_id;
p_check c_check%ROWTYPE;
BEGIN
OPEN c_check;
FETCH c_check
INTO p_check;
CLOSE c_check;
p_xxdp := p_check;
IF p_check.emp_no IS NULL
THEN
RETURN 'No emp_no record in Network Login Table';
ELSIF p_check.nt_login IS NULL
THEN
RETURN 'No NT Login Available for this Person in Network Login Table';
ELSIF p_check.college_email_address IS NULL
THEN
RETURN 'No Email Address for this Person in Network Login Table';
END IF;
RETURN NULL;
END is_user_creation_possible;
FUNCTION get_email_from_emp_no(p_emp_no_email IN VARCHAR2
,p_test_email IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
IF g_instance_name = 'XXPRD'
THEN
RETURN p_emp_no_email;
ELSE
RETURN p_test_email;
END IF;
END get_email_from_emp_no;
FUNCTION does_fu_exist(p_fu_name IN VARCHAR2) RETURN BOOLEAN IS
CURSOR c_check IS
SELECT 'x' FROM fnd_user fu WHERE fu.user_name = upper(p_fu_name);
p_check c_check%ROWTYPE;
BEGIN
OPEN c_check;
FETCH c_check
INTO p_check;
IF c_check%FOUND
THEN
CLOSE c_check;
RETURN TRUE;
END IF;
CLOSE c_check;
RETURN FALSE;
END does_fu_exist;
PROCEDURE send_email_to_new_user(p_xxdp IN xx_windows_logon_table%ROWTYPE
,p_user_name IN VARCHAR2
,p_password IN VARCHAR2
,p_test_email IN VARCHAR2) IS
BEGIN
DECLARE
BEGIN
send_html_email(p_to => get_email_from_emp_no(p_xxdp.college_email_address
,p_test_email)
,p_from => nvl(p_test_email
,'xxmail@gmail.com')
,p_subject => 'Welcome to Self Service HR'
,p_text => 'Welcome to Self Service HR'
,p_html => '<b>Your User Name</b> : ' ||
p_user_name ||
'<br/><b>Your Password</b> : ' ||
p_password || '<br/><br/>' ||
'This user name and password gives you access the new Self Service HR.' ||
'<br/><br/>Self Service HR enables Company staff to view and update their own personal data. The <br/>information is current and any changes made will be implemented immediately.' ||
'<br/><br/>Please go to Spectrum following this link <br/><a href="http://anilpassi.com">http://anilpassi.com</a>' ||
'<br/>where you can log into Self Service HR, find out more and read the FAQs.'
,p_smtp_hostname => 'localhost'
,p_smtp_portnum => '25');
END;
END send_email_to_new_user;
PROCEDURE send_email_to_existing_user(p_xxdp IN xx_windows_logon_table%ROWTYPE
,p_test_email IN VARCHAR2) IS
BEGIN
DECLARE
BEGIN
send_html_email(p_to => get_email_from_emp_no(p_xxdp.college_email_address
,p_test_email)
,p_from => nvl(p_test_email
,'xxmail@gmail.com')
,p_subject => 'Welcome to Self Service HR'
,p_text => 'Welcome to Self Service HR'
,p_html => 'We are writing to let you know that the next time you log into Oracle Apps you will see a new<br/> responsibility, <b>XX HR Employee Self Service</b>. This responsibility gives you access the new<br/> Self Service HR feature in Oracle Apps.' ||
'<p></p>Self Service HR enables staff to view and update their own personal data.' ||
'<p></p>Please go to this link<br/><a href="http://anilpassi.com">http://anilpassi.com</a><br/>to find out more and read the FAQs.' ||
'<br/><br/>' || 'Regards' ||
'<br/><br/>SSHR Rollout Team' ||
'<br/>' ||
'HR Dept'
,p_smtp_hostname => 'localhost'
,p_smtp_portnum => '25');
END;
END send_email_to_existing_user;
FUNCTION get_latest_fu(p_proposed_fu_name IN VARCHAR2
,p_proposed_offset IN INTEGER) RETURN VARCHAR2 IS
BEGIN
IF does_fu_exist(p_proposed_fu_name || p_proposed_offset)
THEN
RETURN get_latest_fu(p_proposed_fu_name
,p_proposed_offset + 1);
END IF;
RETURN upper(p_proposed_fu_name || p_proposed_offset);
END get_latest_fu;
FUNCTION get_fu_name(p_nt_login IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
IF NOT does_fu_exist(p_nt_login)
THEN
RETURN upper(p_nt_login);
END IF;
IF NOT does_fu_exist(p_nt_login)
THEN
RETURN upper(p_nt_login);
END IF;
RETURN get_latest_fu(p_nt_login
,1);
END get_fu_name;
FUNCTION get_user_name_from_fu_per_id(p_person_id IN VARCHAR2)
RETURN VARCHAR2 IS
CURSOR c_get IS
SELECT fu.user_name
FROM fnd_user fu
WHERE fu.employee_id = p_person_id;
p_get c_get%ROWTYPE;
BEGIN
OPEN c_get;
FETCH c_get
INTO p_get;
CLOSE c_get;
RETURN p_get.user_name;
END get_user_name_from_fu_per_id;
FUNCTION get_random_password RETURN VARCHAR2 IS
BEGIN
RETURN lower(dbms_random.STRING('X'
,8));
END get_random_password;
FUNCTION get_person_name(p_person_id IN INTEGER) RETURN VARCHAR2 IS
CURSOR c_get IS
SELECT full_name
FROM per_all_people_f
WHERE person_id = p_person_id
ORDER BY effective_start_date DESC;
p_get c_get%ROWTYPE;
BEGIN
OPEN c_get;
FETCH c_get
INTO p_get;
CLOSE c_get;
RETURN p_get.full_name;
END get_person_name;
PROCEDURE create_fnd_user_for_emp_no(p_user_name IN VARCHAR2
,p_person_id IN INTEGER
,p_email_address IN VARCHAR2
,p_person_description IN VARCHAR2
,p_password OUT VARCHAR2) IS
v_session_id VARCHAR2(200);
v_password VARCHAR2(100) := get_random_password;
BEGIN
p_password := v_password;
fnd_user_pkg.createuser(x_user_name => p_user_name
,x_owner => ''
,x_unencrypted_password => v_password
,x_description => p_person_description
,x_password_lifespan_days => 180
,x_employee_id => p_person_id
,x_email_address => p_email_address);
END create_fnd_user_for_emp_no;
FUNCTION get_fu_id(p_fu_name IN VARCHAR2) RETURN VARCHAR2 IS
CURSOR c_get IS
SELECT user_id FROM fnd_user WHERE user_name = p_fu_name;
p_get c_get%ROWTYPE;
BEGIN
OPEN c_get;
FETCH c_get
INTO p_get;
CLOSE c_get;
RETURN p_get.user_id;
END get_fu_id;
FUNCTION create_fnd_user(p_person_id IN INTEGER
,p_xxdp IN xx_windows_logon_table%ROWTYPE
,p_new_fnd_user_name OUT VARCHAR2
,p_new_fnd_user_password OUT VARCHAR2)
RETURN INTEGER IS
v_user_name fnd_user.user_name%TYPE;
v_password VARCHAR2(200);
v_err VARCHAR2(2000);
BEGIN
v_user_name := get_fu_name(p_nt_login => p_xxdp.nt_login);
debug_stmt('For p_xxdp.nt_login=>' || p_xxdp.nt_login ||
' the username is ' || v_user_name);
create_fnd_user_for_emp_no(p_user_name => p_xxdp.nt_login
,p_person_id => p_person_id
,p_email_address => p_xxdp.college_email_address
,p_person_description => p_xxdp.title || ' ' ||
p_xxdp.first_name || ' ' ||
p_xxdp.last_name
,p_password => v_password);
p_new_fnd_user_name := v_user_name;
p_new_fnd_user_password := v_password;
RETURN get_fu_id(p_fu_name => v_user_name);
EXCEPTION
WHEN OTHERS THEN
v_err := substr(SQLERRM
,1
,2000);
debug_stmt(v_err);
RETURN NULL;
END create_fnd_user;
PROCEDURE send_html_email(p_to IN VARCHAR2
,p_from IN VARCHAR2
,p_subject IN VARCHAR2
,p_text IN VARCHAR2 DEFAULT NULL
,p_html IN VARCHAR2 DEFAULT NULL
,p_smtp_hostname IN VARCHAR2
,p_smtp_portnum IN VARCHAR2) IS
l_boundary VARCHAR2(255) DEFAULT 'a1b2c3d4e3f2g1';
l_connection utl_smtp.connection;
l_body_html CLOB := empty_clob; --This LOB will be the email message
l_offset NUMBER;
l_ammount NUMBER;
l_temp VARCHAR2(32767) DEFAULT NULL;
BEGIN
/* Usage......
html_email(p_to => 'a.passi@Company.ac.uk'
,p_from => 'anilpassi@gmail.com'
,p_subject => 'Testing from anil'
,p_text => 'ABCD'
,p_html => '<b>IJKLM</b> Testing for the HTML Format of the email'
,p_smtp_hostname => 'localhost'
,p_smtp_portnum => '25');
*/
l_connection := utl_smtp.open_connection(p_smtp_hostname
,p_smtp_portnum);
utl_smtp.helo(l_connection
,p_smtp_hostname);
utl_smtp.mail(l_connection
,p_from);
utl_smtp.rcpt(l_connection
,p_to);
l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
l_temp := l_temp || 'Reply-To: ' || p_from || chr(13) || chr(10);
l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
chr(34) || l_boundary || chr(34) || chr(13) || chr(10);
----------------------------------------------------
-- Write the headers
dbms_lob.createtemporary(l_body_html
,FALSE
,10);
dbms_lob.WRITE(l_body_html
,length(l_temp)
,1
,l_temp);
----------------------------------------------------
-- Write the text boundary
l_offset := dbms_lob.getlength(l_body_html) + 1;
l_temp := '--' || l_boundary || chr(13) || chr(10);
l_temp := l_temp || 'content-type: text/plain; charset=us-ascii' ||
chr(13) || chr(10) || chr(13) || chr(10);
dbms_lob.WRITE(l_body_html
,length(l_temp)
,l_offset
,l_temp);
----------------------------------------------------
-- Write the plain text portion of the email
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.WRITE(l_body_html
,length(p_text)
,l_offset
,p_text);
----------------------------------------------------
-- Write the HTML boundary
l_temp := chr(13) || chr(10) || chr(13) || chr(10) || '--' ||
l_boundary || chr(13) || chr(10);
l_temp := l_temp || 'content-type: text/html;' || chr(13) || chr(10) ||
chr(13) || chr(10);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.WRITE(l_body_html
,length(l_temp)
,l_offset
,l_temp);
----------------------------------------------------
-- Write the HTML portion of the message
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.WRITE(l_body_html
,length(p_html)
,l_offset
,p_html);
----------------------------------------------------
-- Write the final html boundary
l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.WRITE(l_body_html
,length(l_temp)
,l_offset
,l_temp);
----------------------------------------------------
-- Send the email in 1900 byte chunks to UTL_SMTP
l_offset := 1;
l_ammount := 1900;
utl_smtp.open_data(l_connection);
WHILE l_offset < dbms_lob.getlength(l_body_html)
LOOP
utl_smtp.write_data(l_connection
,dbms_lob.substr(l_body_html
,l_ammount
,l_offset));
l_offset := l_offset + l_ammount;
l_ammount := least(1900
,dbms_lob.getlength(l_body_html) - l_ammount);
END LOOP;
utl_smtp.close_data(l_connection);
utl_smtp.quit(l_connection);
dbms_lob.freetemporary(l_body_html);
END send_html_email;
PROCEDURE excel_output(p_msg IN VARCHAR2) IS
BEGIN
fnd_file.put_line(fnd_file.output
,p_msg);
END excel_output;
FUNCTION get_user_name(p_user_id IN INTEGER) RETURN VARCHAR2 IS
CURSOR c_get IS
SELECT user_name FROM fnd_user WHERE user_id = p_user_id;
p_get c_get%ROWTYPE;
BEGIN
OPEN c_get;
FETCH c_get
INTO p_get;
CLOSE c_get;
RETURN p_get.user_name;
END get_user_name;
FUNCTION get_emp_no(p_person_id IN INTEGER) RETURN VARCHAR2 IS
CURSOR c_get IS
SELECT employee_number
FROM xx_per_all_people_x
WHERE person_id = p_person_id;
p_get c_get%ROWTYPE;
BEGIN
OPEN c_get;
FETCH c_get
INTO p_get;
CLOSE c_get;
RETURN p_get.employee_number;
END get_emp_no;
FUNCTION get_cost_centre_group(p_person_id IN INTEGER) RETURN VARCHAR2 IS
CURSOR c_get IS
SELECT hrou1.attribute5
FROM hr_all_organization_units hrou1
,hr_all_organization_units hrou
,xx_per_all_asg_x ass
,xx_per_all_people_x ppx
WHERE ppx.person_id = p_person_id
AND ass.person_id = ppx.person_id
AND ass.assignment_number IS NOT NULL
AND ass.primary_flag = 'Y'
AND hrou.organization_id = ass.organization_id
AND hrou1.NAME = primaryhro_pkg.fn_get_primaryhro(ass.organization_id);
p_get c_get%ROWTYPE;
BEGIN
OPEN c_get;
FETCH c_get
INTO p_get;
CLOSE c_get;
RETURN p_get.attribute5;
END get_cost_centre_group;
FUNCTION get_parent_org(p_person_id IN INTEGER) RETURN VARCHAR2 IS
CURSOR c_get IS
SELECT primaryhro_pkg.fn_get_primaryhro(ass.organization_id) parent_org
FROM hr_all_organization_units hrou
,xx_per_all_asg_x ass
,xx_per_all_people_x ppx
WHERE ppx.person_id = p_person_id
AND ass.person_id = ppx.person_id
AND ass.assignment_number IS NOT NULL
AND ass.primary_flag = 'Y'
AND hrou.organization_id = ass.organization_id;
p_get c_get%ROWTYPE;
BEGIN
OPEN c_get;
FETCH c_get
INTO p_get;
CLOSE c_get;
RETURN p_get.parent_org;
END get_parent_org;
FUNCTION get_grade(p_person_id IN INTEGER) RETURN VARCHAR2 IS
CURSOR c_get IS
SELECT pg.NAME
FROM per_grade_definitions pgd
,per_grades pg
,xx_per_all_asg_x ass
,xx_per_all_people_x ppx
WHERE ppx.person_id = p_person_id
AND ass.person_id = ppx.person_id
AND ass.assignment_number IS NOT NULL
AND ass.primary_flag = 'Y'
AND pg.grade_id = ass.grade_id
AND pgd.grade_definition_id = pg.grade_definition_id;
p_get c_get%ROWTYPE;
BEGIN
OPEN c_get;
FETCH c_get
INTO p_get;
CLOSE c_get;
RETURN p_get.NAME;
END get_grade;
PROCEDURE run(errbuf OUT VARCHAR2
,retcode OUT VARCHAR2
,p_responsibility_name IN VARCHAR2
,p_person_type IN VARCHAR2
,p_cost_centre_group_1 IN VARCHAR2
,p_cost_centre_group_2 IN VARCHAR2
,p_parent_org_1 IN VARCHAR2
,p_parent_org_2 IN VARCHAR2
,p_emp_no IN VARCHAR2
,p_read_only_flag IN VARCHAR2
,p_test_ceration_email_address IN VARCHAR2) IS
n_count INTEGER;
v_sqlerrm VARCHAR2(2000);
can_not_fnd_create_user EXCEPTION;
error_in_fnd_user_pkg EXCEPTION;
v_fnd_user_name VARCHAR2(100);
CURSOR c_get IS
SELECT *
FROM fnd_responsibility_vl
WHERE responsibility_name =
nvl('XX HR Employee Self Service'
,p_responsibility_name);
p_get c_get%ROWTYPE;
duplicate_responsibility EXCEPTION;
PRAGMA EXCEPTION_INIT(duplicate_responsibility
,-20001);
v_hard_password VARCHAR2(1) := fnd_profile.VALUE('SIGNON_PASSWORD_HARD_TO_GUESS');
l_xxdp xx_windows_logon_table%ROWTYPE;
b_new_user_created BOOLEAN;
v_fnd_user_password VARCHAR2(100);
BEGIN
set_debug_context('run');
SELECT instance_name INTO g_instance_name FROM v$instance;
debug_stmt('g_instance_name=>' || g_instance_name);
fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
,val => 'N');
OPEN c_get;
FETCH c_get
INTO p_get;
CLOSE c_get;
--lets dump the records first into the temp table,
--this will be followed by
--a. see which people do not have Sign On
--b. Which people already have Responsibility
INSERT INTO xx_sshr_allocate_resp
(sshr_allocate_resp_id
,person_id
,future_dated_employee_flag
,responsibillity_name
,error_during_resp_allocation
,fnd_user_id
,fnd_request_id
,email_address)
(SELECT xx_sshr_allocate_resp_s.NEXTVAL
,ppx.person_id --PERSON_ID
,'N' --FUTURE_DATED_EMPLOYEE_FLAG
,p_responsibility_name --responsibillity_name
,NULL --ERROR_DURING_RESP_ALLOCATION
,NULL --FND_USER_ID
,fnd_global.conc_request_id --FND_REQUEST_ID
,ppx.email_address
FROM per_person_types ppt
,per_person_type_usages_x pptux
,xx_per_all_people_x ppx
WHERE ppx.person_id = pptux.person_id
AND ppt.person_type_id = pptux.person_type_id
AND ppx.employee_number = nvl(p_emp_no
,ppx.employee_number)
AND ppt.system_person_type = 'EMP'
AND ppt.user_person_type = p_person_type
AND ppt.business_group_id =
fnd_profile.VALUE('PER_BUSINESS_GROUP_ID')
AND EXISTS (SELECT 'x'
FROM hr_all_organization_units hrou1
,hr_all_organization_units hrou
,xx_per_all_asg_x pax
WHERE p_cost_centre_group_1 IS NOT NULL
AND pax.person_id = ppx.person_id
AND pax.primary_flag = 'Y'
AND pax.assignment_number IS NOT NULL
AND hrou.organization_id = pax.organization_id
AND hrou1.NAME =
primaryhro_pkg.fn_get_primaryhro(pax.organization_id)
AND hrou1.attribute5 IN
(nvl(p_cost_centre_group_1
,'XXXX'), nvl(p_cost_centre_group_2
,'XXXX'))
UNION ALL
SELECT 'x'
FROM dual
WHERE (p_cost_centre_group_1 IS NULL AND p_cost_centre_group_2 IS NULL))
AND EXISTS
(SELECT 'x'
FROM hr_all_organization_units hrou, xx_per_all_asg_x pax
WHERE p_parent_org_1 IS NOT NULL
AND pax.person_id = ppx.person_id
AND pax.primary_flag = 'Y'
AND pax.assignment_number IS NOT NULL
AND hrou.organization_id = pax.organization_id
AND primaryhro_pkg.fn_get_primaryhro(pax.organization_id) IN
(nvl(p_parent_org_1
,'XXXX'), nvl(p_parent_org_2
,'XXXX'))
UNION ALL
SELECT 'x'
FROM dual
WHERE (p_parent_org_1 IS NULL AND p_parent_org_2 IS NULL)));
n_count := SQL%ROWCOUNT;
debug_stmt(n_count ||
' Records inserted into Temp Table based on Eligibility Criteria');
INSERT INTO xx_sshr_allocate_resp
(sshr_allocate_resp_id
,person_id
,future_dated_employee_flag
,responsibillity_name
,error_during_resp_allocation
,fnd_user_id
,fnd_request_id
,email_address)
(SELECT xx_sshr_allocate_resp_s.NEXTVAL
,ppx.person_id --PERSON_ID
,'Y' --FUTURE_DATED_EMPLOYEE_FLAG
,p_responsibility_name --responsibillity_name
,'Employee Is a Future Starter' --ERROR_DURING_RESP_ALLOCATION
,NULL --FND_USER_ID
,fnd_global.conc_request_id --FND_REQUEST_ID
,ppx.email_address
FROM per_person_types ppt
,xx_per_person_type_usages_eot pptux
,xx_per_all_people_eot ppx
WHERE NOT EXISTS
(SELECT 'x'
FROM xx_sshr_allocate_resp iar
WHERE iar.person_id = ppx.person_id
AND fnd_request_id = fnd_global.conc_request_id)
AND ppx.person_id = pptux.person_id
AND ppt.person_type_id = pptux.person_type_id
AND ppx.employee_number = nvl(p_emp_no
,ppx.employee_number)
AND ppt.system_person_type = 'EMP'
AND ppt.user_person_type = p_person_type
AND ppt.business_group_id =
fnd_profile.VALUE('PER_BUSINESS_GROUP_ID')
AND EXISTS (SELECT 'x'
FROM hr_all_organization_units hrou1
,hr_all_organization_units hrou
,xx_per_all_asg_x pax
WHERE p_cost_centre_group_1 IS NOT NULL
AND pax.person_id = ppx.person_id
AND pax.primary_flag = 'Y'
AND pax.assignment_number IS NOT NULL
AND hrou.organization_id = pax.organization_id
AND hrou1.NAME =
primaryhro_pkg.fn_get_primaryhro(pax.organization_id)
AND hrou1.attribute5 IN
(nvl(p_cost_centre_group_1
,'XXXX'), nvl(p_cost_centre_group_2
,'XXXX'))
UNION ALL
SELECT 'x'
FROM dual
WHERE (p_cost_centre_group_1 IS NULL AND p_cost_centre_group_2 IS NULL))
AND EXISTS
(SELECT 'x'
FROM hr_all_organization_units hrou, xx_per_all_asg_x pax
WHERE p_parent_org_1 IS NOT NULL
AND pax.person_id = ppx.person_id
AND pax.primary_flag = 'Y'
AND pax.assignment_number IS NOT NULL
AND hrou.organization_id = pax.organization_id
AND primaryhro_pkg.fn_get_primaryhro(pax.organization_id) IN
(nvl(p_parent_org_1
,'XXXX'), nvl(p_parent_org_2
,'XXXX'))
UNION ALL
SELECT 'x'
FROM dual
WHERE (p_parent_org_1 IS NULL AND p_parent_org_2 IS NULL)));
n_count := SQL%ROWCOUNT;
debug_stmt(n_count ||
' Records inserted into Temp Table that aer eligible but Future Dated');
--Commenting the below, as we need to create User Accounts for these folks
/* UPDATE xx_sshr_allocate_resp isar
SET error_during_resp_allocation = 'Employee Is Not a User'
WHERE isar.fnd_request_id = fnd_global.conc_request_id
AND error_during_resp_allocation IS NULL
AND NOT EXISTS
(SELECT 'x' FROM fnd_user fu WHERE fu.employee_id = isar.person_id);
n_count := SQL%ROWCOUNT;
put_log(n_count || ' Records errored due to them not being Employee');
*/
UPDATE xx_sshr_allocate_resp isar
SET fnd_user_id = (SELECT user_id
FROM fnd_user
WHERE employee_id = isar.person_id
AND rownum < 2)
WHERE isar.fnd_request_id = fnd_global.conc_request_id
AND error_during_resp_allocation IS NULL;
UPDATE xx_sshr_allocate_resp isar
SET responsibility_alloc_date = (SELECT start_date
FROM fnd_user_resp_groups_direct
WHERE user_id = isar.fnd_user_id
AND responsibility_id =
p_get.responsibility_id
AND rownum < 2)
WHERE isar.fnd_request_id = fnd_global.conc_request_id;
n_count := SQL%ROWCOUNT;
debug_stmt(n_count ||
' Records were attempted to be assigned existing responsibility_alloc_date');
UPDATE xx_sshr_allocate_resp isar
SET error_during_resp_allocation = 'Responsibility Already Allocated on ' ||
to_char(responsibility_alloc_date
,'DD-MON-YYYY')
WHERE isar.fnd_request_id = fnd_global.conc_request_id
AND responsibility_alloc_date IS NOT NULL;
n_count := SQL%ROWCOUNT;
debug_stmt(n_count ||
' Records errored as they already have the responsibility');
/* UPDATE xx_sshr_allocate_resp isar
SET error_during_resp_allocation = 'Employees User Record is Terminated'
WHERE isar.fnd_request_id = fnd_global.conc_request_id
AND error_during_resp_allocation IS NULL
AND EXISTS (SELECT 'x'
FROM fnd_user fu
WHERE fu.employee_id = isar.person_id
AND NOT (trunc(SYSDATE) BETWEEN
nvl(fu.start_date
,trunc(SYSDATE)) AND
nvl(fu.start_date
,trunc(SYSDATE))));
n_count := SQL%ROWCOUNT;
put_log(n_count || ' Records errored as their FND_USER is end dated');
*/
UPDATE xx_sshr_allocate_resp isar
SET error_during_resp_allocation = 'No Email Address'
WHERE isar.fnd_request_id = fnd_global.conc_request_id
AND isar.email_address IS NULL
AND error_during_resp_allocation IS NULL;
n_count := SQL%ROWCOUNT;
debug_stmt(n_count ||
' Records errored as they have no email address in HRMS');
UPDATE xx_sshr_allocate_resp isar
SET fnd_user_id = (SELECT user_id
FROM fnd_user
WHERE employee_id = isar.person_id
AND rownum < 2)
WHERE isar.fnd_request_id = fnd_global.conc_request_id
AND error_during_resp_allocation IS NULL;
n_count := SQL%ROWCOUNT;
debug_stmt(n_count ||
' Records aer unerrored, and hence will be processed further');
excel_output('Action' || chr(9) || 'UserName' || chr(9) || 'emp_no' ||
chr(9) || 'Person Full Name' || chr(9) ||
'Allocation Date' || chr(9) || 'Error' || chr(9) ||
'cost_centre_group' || chr(9) || 'parent_org' || chr(9) || 'Grade');
FOR p_rec IN (SELECT *
FROM xx_sshr_allocate_resp isar
WHERE isar.fnd_request_id = fnd_global.conc_request_id
AND error_during_resp_allocation IS NULL)
LOOP
BEGIN
l_xxdp := NULL;
v_fnd_user_password := NULL;
b_new_user_created := FALSE;
v_fnd_user_name := NULL;
v_sqlerrm := is_user_creation_possible(p_person_id => p_rec.person_id
,p_xxdp => l_xxdp);
debug_stmt('p_rec.fnd_user_id =>' || p_rec.fnd_user_id);
debug_stmt('Is user creation possible returned => ' || v_sqlerrm);
IF p_rec.fnd_user_id IS NULL AND v_sqlerrm IS NOT NULL
THEN
RAISE can_not_fnd_create_user;
END IF;
IF NOT (p_read_only_flag = 'Y')
THEN
debug_stmt('Not read only');
IF p_rec.fnd_user_id IS NULL
THEN
debug_stmt('Looks like new user is needed');
p_rec.fnd_user_id := create_fnd_user(p_person_id => p_rec.person_id
,p_xxdp => l_xxdp
,p_new_fnd_user_name => v_fnd_user_name
,p_new_fnd_user_password => v_fnd_user_password);
IF p_rec.fnd_user_id IS NULL
THEN
RAISE error_in_fnd_user_pkg;
ELSE
UPDATE xx_sshr_allocate_resp ir
SET ir.fnd_user_id = p_rec.fnd_user_id
,new_fnd_user_flag = 'Y'
,messsage_code = v_fnd_user_password
WHERE ir.sshr_allocate_resp_id = p_rec.sshr_allocate_resp_id;
b_new_user_created := TRUE;
END IF;
END IF;
fnd_user_resp_groups_api.insert_assignment(user_id => p_rec.fnd_user_id
,responsibility_id => p_get.responsibility_id
,responsibility_application_id => p_get.application_id
,security_group_id => 0
,start_date => trunc(SYSDATE)
,end_date => NULL
,description => 'Auto Allocation for SSHR');
UPDATE xx_sshr_allocate_resp
SET responsibility_alloc_date = SYSDATE
WHERE sshr_allocate_resp_id = p_rec.sshr_allocate_resp_id;
IF b_new_user_created
THEN
excel_output('Allocated[With New User]' || chr(9) ||
get_user_name(p_rec.fnd_user_id) || chr(9) ||
get_emp_no(p_rec.person_id) || chr(9) ||
get_person_name(p_rec.person_id) || chr(9) ||
to_char(trunc(SYSDATE)
,'DD-MON-YYYY') || chr(9) || '' || chr(9) ||
get_cost_centre_group(p_rec.person_id) || chr(9) ||
get_parent_org(p_rec.person_id) || chr(9) ||
get_grade(p_rec.person_id));
send_email_to_new_user(p_xxdp => l_xxdp
,p_user_name => v_fnd_user_name
,p_password => v_fnd_user_password
,p_test_email => p_test_ceration_email_address);
ELSE
excel_output('Allocated' || chr(9) ||
get_user_name(p_rec.fnd_user_id) || chr(9) ||
get_emp_no(p_rec.person_id) || chr(9) ||
get_person_name(p_rec.person_id) || chr(9) ||
to_char(trunc(SYSDATE)
,'DD-MON-YYYY') || chr(9) || '' || chr(9) ||
get_cost_centre_group(p_rec.person_id) || chr(9) ||
get_parent_org(p_rec.person_id) || chr(9) ||
get_grade(p_rec.person_id));
send_email_to_existing_user(p_xxdp => l_xxdp
,p_test_email => p_test_ceration_email_address);
END IF;
COMMIT;
ELSE
IF p_rec.fnd_user_id IS NULL
THEN
excel_output('Eligible [New User Will Be Created]' || chr(9) ||
nvl(get_user_name(p_rec.fnd_user_id)
,get_fu_name(l_xxdp.nt_login)) || chr(9) ||
get_emp_no(p_rec.person_id) || chr(9) ||
get_person_name(p_rec.person_id) || chr(9) ||
chr(9) || chr(9) || get_cost_centre_group(p_rec.person_id) ||
chr(9) || get_parent_org(p_rec.person_id) || chr(9) ||
get_grade(p_rec.person_id));
ELSE
excel_output('Eligible' || chr(9) ||
get_user_name(p_rec.fnd_user_id) || chr(9) ||
get_emp_no(p_rec.person_id) || chr(9) ||
get_person_name(p_rec.person_id) || chr(9) ||
chr(9) || chr(9) || get_cost_centre_group(p_rec.person_id) ||
chr(9) || get_parent_org(p_rec.person_id) || chr(9) ||
get_grade(p_rec.person_id));
END IF;
END IF;
EXCEPTION
WHEN can_not_fnd_create_user THEN
UPDATE xx_sshr_allocate_resp ir
SET ir.error_during_resp_allocation = v_sqlerrm
WHERE sshr_allocate_resp_id = p_rec.sshr_allocate_resp_id;
WHEN error_in_fnd_user_pkg THEN
UPDATE xx_sshr_allocate_resp ir
SET ir.error_during_resp_allocation = 'Error while creating FND User. Please see Concurrent Log file for details'
WHERE sshr_allocate_resp_id = p_rec.sshr_allocate_resp_id;
WHEN OTHERS THEN
v_sqlerrm := SQLERRM;
UPDATE xx_sshr_allocate_resp
SET error_during_resp_allocation = substr(v_sqlerrm
,1
,2000)
WHERE sshr_allocate_resp_id = p_rec.sshr_allocate_resp_id;
END;
END LOOP;
FOR p_recx IN (SELECT *
FROM xx_sshr_allocate_resp isar
WHERE isar.fnd_request_id = fnd_global.conc_request_id
AND error_during_resp_allocation IS NOT NULL)
LOOP
excel_output('Error' || chr(9) || get_user_name(p_recx.fnd_user_id) ||
chr(9) || get_emp_no(p_recx.person_id) || chr(9) ||
get_person_name(p_recx.person_id) || chr(9) ||
to_char(p_recx.responsibility_alloc_date
,'DD-MON-YYYY') || chr(9) ||
p_recx.error_during_resp_allocation || chr(9) ||
get_cost_centre_group(p_recx.person_id) || chr(9) ||
get_parent_org(p_recx.person_id) || chr(9) ||
get_grade(p_recx.person_id));
END LOOP;
fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
,val => v_hard_password);
debug_end_procedure;
EXCEPTION
WHEN OTHERS THEN
fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
,val => v_hard_password);
RAISE;
END run;
END xx_sshr_allocate_resp_pkg;
|
