l_responsibility_key VARCHAR2(250) := 'XX_AP_INQUIRY';
l_resp_app VARCHAR2(30) := 'SQLAP';
l_security_group_key VARCHAR2(100) := 'STANDARD';
l_processed_count NUMBER := 0;
l_new_asg_count NUMBER := 0;
l_reactivate_asg_count NUMBER := 0;
l_no_action_count NUMBER := 0;
l_asg_exists_count NUMBER := 0;
l_error_count NUMBER := 0;
CURSOR csr_act_emps IS
SELECT user_name FROM XXhr_active_employees emps WHERE emps.assignment_primary_flag = 'Y' ORDER BY user_name;
CURSOR csr_chk_resp_exists(p_user_name VARCHAR2) IS
SELECT 'x' exist,
decode(sign(nvl(c.end_date, SYSDATE + 1) - SYSDATE), 1, 'A', 'I') user_status,
decode(sign(nvl(a.end_date, SYSDATE + 1) - SYSDATE), 1, 'A', 'I') asg_status,
c.user_name,
a.start_date
FROM fnd_user_resp_groups_direct a,
fnd_responsibility_vl b,
fnd_user c,
XXhr_active_employees d
WHERE a.responsibility_id = b.responsibility_id
AND b.responsibility_key = l_responsibility_key
AND c.user_id = a.user_id
AND c.user_name = p_user_name
AND d.user_name = c.user_name;
r_exists csr_chk_resp_exists%ROWTYPE;
l_file utl_file.file_type;
l_output_dir VARCHAR2(30);
BEGIN
SELECT instance_name INTO l_output_dir FROM v$instance;
l_output_dir := '/applcsf/' || l_output_dir || '/temp';
l_file := utl_file.fopen(l_output_dir, 'XX_bulk_resp_asg2.log', 'A');
IF (l_file.id IS NOT NULL) THEN
dbms_output.put_line('file opened');
ELSE
dbms_output.put_line('error opening file');
END IF;
-- Sync program required to be run in Prod before running this procedure;
/*BEGIN
utl_file.put_line(l_file, 'calling sync prgram ...');
WF_LOCAL_SYNCH.BulkSynchronization('ALL');
utl_file.put_line(l_file, 'Sync completed ...');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
utl_file.put_line(l_file, 'ERROR: '||SQLERRM);
END;*/
utl_file.fflush(l_file);
FOR r1 IN csr_act_emps LOOP
r_exists := NULL;
OPEN csr_chk_resp_exists(r1.user_name);
FETCH csr_chk_resp_exists
INTO r_exists;
CLOSE csr_chk_resp_exists;
utl_file.put(l_file, r1.user_name || ',');
BEGIN
IF r_exists.exist IS NULL THEN
utl_file.put(l_file, 'New Assignment' || chr(10));
fnd_user_pkg.addresp(username => r1.user_name,
resp_app => l_resp_app,
resp_key => l_responsibility_key,
security_group => l_security_group_key,
description => NULL,
start_date => SYSDATE,
end_date => NULL);
l_new_asg_count := l_new_asg_count + 1;
ELSIF r_exists.user_status = 'A' AND r_exists.asg_status = 'I' THEN
utl_file.put(l_file, 'Updating Existing Assignment' || chr(10));
fnd_user_pkg.addresp(username => r1.user_name,
resp_app => l_resp_app,
resp_key => l_responsibility_key,
security_group => l_security_group_key,
description => NULL,
start_date => r_exists.start_date,
end_date => NULL);
l_reactivate_asg_count := l_reactivate_asg_count + 1;
ELSIF r_exists.user_status = 'A' AND r_exists.asg_status = 'A' THEN
utl_file.put(l_file, 'No Action Active Resp Assignment already exists' ||chr(10));
l_no_action_count := l_no_action_count + 1;
ELSIF r_exists.user_status = 'I' THEN
utl_file.put(l_file, 'No Action, Inactive User'|| chr(10));
l_asg_exists_count := l_asg_exists_count + 1;
END IF;
EXCEPTION
WHEN OTHERS THEN
utl_file.put_line(l_file, 'Error: ' || SQLERRM);
l_error_count := l_error_count + 1;
IF l_error_count >= 10 THEN
EXIT;
END IF;
END;
l_processed_count := l_processed_count + 1;
ROLLBACK;
utl_file.fflush(l_file);
END LOOP;
utl_file.put_line(l_file, '');
utl_file.put_line(l_file,'l_new_asg_count: ' || l_new_asg_count);
utl_file.put_line(l_file,'l_reactivate_asg_count: ' || l_reactivate_asg_count);
utl_file.put_line(l_file,'l_no_action_count: ' || l_no_action_count);
utl_file.put_line(l_file,'l_asg_exists_count: ' || l_asg_exists_count);
utl_file.put_line(l_file,'l_processed_count: ' || l_processed_count);
utl_file.put_line(l_file,'l_error_count: ' || l_error_count);
utl_file.fclose(l_file);
--COMMIT;
END;
