PROCEDURE emp_supervisor_update_p(errbuf IN VARCHAR2
,retcode IN NUMBER
)
IS
l_person_id NUMBER;
l_assignment_id NUMBER;
l_effective_date DATE:= NULL;
l_supervisor_id NUMBER;
lb_correction BOOLEAN;
lb_update BOOLEAN;
lb_update_override BOOLEAN;
lb_update_change_insert BOOLEAN;
lc_dt_ud_mode VARCHAR2(100):= NULL;
l_obj_version_num NUMBER;
l_soft_coding_keyflex_id hr_soft_coding_keyflex.soft_coding_keyflex_id%TYPE;
l_concatenated_segments VARCHAR2(2000);
l_comment_id per_all_assignments_f.comment_id%TYPE;
l_effective_start_date per_all_assignments_f.effective_start_date%TYPE;
l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
l_no_managers_warning BOOLEAN;
l_other_manager_warning BOOLEAN;
err_msg VARCHAR2(4000):= NULL;
current_records NUMBER;
total_records NUMBER;
error_records NUMBER;
l_effective_date_valid NUMBER;
error_msg VARCHAR2(4000):= NULL;
CURSOR cur_emp_supervisor_upd
IS
SELECT ROWID, stg.*
FROM xx_emp_sup_update_stg stg
WHERE stg.update_status IS NULL OR stg.update_status = 'E';
BEGIN
SELECT COUNT(*)
INTO current_records
FROM xx_emp_sup_update_stg stg
WHERE stg.update_status IS NULL OR stg.update_status = 'E';
FOR rec_emp_supervisor_upd IN cur_emp_supervisor_upd
LOOP
l_soft_coding_keyflex_id := NULL;
l_obj_version_num := NULL;
BEGIN
SELECT person_id
INTO l_person_id
FROM per_all_people_f
WHERE employee_number =rec_emp_supervisor_upd.employee_id
AND SYSDATE BETWEEN effective_start_date
AND effective_end_date;
dbms_output.put_line(l_person_id);
EXCEPTION
WHEN OTHERS THEN
err_msg1 := err_msg1||SQLERRM||'.';
END;
BEGIN
SELECT MAX(object_version_number)
INTO l_obj_version_num
FROM per_all_assignments_f
WHERE person_id = l_person_id
AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
EXCEPTION
WHEN OTHERS THEN
err_msg1 := err_msg1||SQLERRM||' *';
END;
BEGIN
SELECT assignment_id
,effective_start_date
INTO l_assignment_id
,l_effective_date
FROM per_all_assignments_f
WHERE person_id = l_person_id
AND SYSDATE BETWEEN effective_start_date AND effective_end_date
AND object_version_number = l_obj_version_num;
EXCEPTION
WHEN OTHERS THEN
err_msg1 := err_msg1||SQLERRM||'.';
END;
IF rec_emp_supervisor_upd.effective_date >= l_effective_date THEN
l_effective_date_valid := 1;
ELSE
l_effective_date_valid := NULL;
END IF;
BEGIN
SELECT person_id
INTO l_supervisor_id
FROM per_all_people_f
WHERE employee_number = rec_emp_supervisor_upd.new_manager_id
AND SYSDATE BETWEEN effective_start_date
AND effective_end_date;
dbms_output.put_line(l_supervisor_id);
EXCEPTION
WHEN OTHERS THEN
err_msg1 := err_msg1||SQLERRM||' *';
END;
IF l_person_id IS NOT NULL
AND l_assignment_id IS NOT NULL
AND rec_emp_supervisor_upd.effective_date IS NOT NULL
AND l_supervisor_id IS NOT NULL
AND l_obj_version_num IS NOT NULL
AND l_effective_date_valid IS NOT NULL
THEN
BEGIN
dt_api.find_dt_upd_modes
(p_effective_date => (rec_emp_supervisor_upd.effective_date)
,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
,p_base_key_column => 'ASSIGNMENT_ID'
,p_base_key_value => l_assignment_id
,p_correction => lb_correction
,p_update => lb_update
,p_update_override => lb_update_override
,p_update_change_insert => lb_update_change_insert
);
IF ( lb_update_override = TRUE OR lb_update_change_insert = TRUE )
THEN
lc_dt_ud_mode := 'UPDATE_OVERRIDE';
END IF;
IF lb_correction = TRUE
THEN
lc_dt_ud_mode := 'CORRECTION';
END IF;
IF lb_update = TRUE
THEN
lc_dt_ud_mode := 'UPDATE';
END IF;
hr_assignment_api.update_emp_asg
(
p_effective_date => rec_emp_supervisor_upd.effective_date
,p_datetrack_update_mode => lc_dt_ud_mode
,p_assignment_id => l_assignment_id
,p_supervisor_id => l_supervisor_id
,p_change_reason => NULL
,p_object_version_number => l_obj_version_num
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_concatenated_segments => l_concatenated_segments
,p_comment_id => l_comment_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_no_managers_warning => l_no_managers_warning
,p_other_manager_warning => l_other_manager_warning
);
IF l_effective_start_date IS NOT NULL THEN
UPDATE xx_emp_sup_update_stg
SET update_status = 'S'
WHERE ROWID = rec_emp_supervisor_upd.ROWID;
dbms_output.put_line('employee_id: '||rec_emp_supervisor_upd.employee_id||' start date'||l_effective_start_date);
COMMIT;
ELSE
UPDATE xx_emp_sup_update_stg
SET update_status = 'E'
,err_msg = 'record not inserted due to api issue.'
WHERE ROWID = rec_emp_supervisor_upd.ROWID;
dbms_output.put_line('employee_id: '||rec_emp_supervisor_upd.employee_id||' has failed to update.');
END IF;
EXCEPTION
WHEN OTHERS THEN
error_msg := error_msg||SQLERRM||'..';
UPDATE xx_emp_sup_update_stg
SET update_status = 'E'
,err_msg = error_msg
WHERE ROWID = rec_emp_supervisor_upd.ROWID;
COMMIT;
END;
ELSE
IF l_person_id IS NULL THEN
err_msg:= err_msg||'no such employee exists.';
END IF;
IF l_assignment_id IS NULL THEN
err_msg:= err_msg||'no such assignment exists.';
END IF;
IF rec_emp_supervisor_upd.effective_date IS NULL THEN
err_msg:= err_msg||'please provide correct effective date.';
END IF;
IF l_supervisor_id IS NULL THEN
err_msg:= err_msg||'no such supervisor exists.';
END IF;
UPDATE xx_emp_sup_update_stg
SET update_status = 'E'
,err_msg = err_msg
WHERE ROWID = rec_emp_supervisor_upd.ROWID;
COMMIT;
END IF;
END LOOP;
SELECT COUNT(1)
INTO total_records
FROM xx_emp_sup_update_stg;
SELECT COUNT(1)
INTO error_records
FROM xx_emp_sup_update_stg
WHERE update_status = 'E';
fnd_file.put_line(fnd_file.log, '---------------RECORD VALIDATION STATS-------------------');
fnd_file.put_line(fnd_file.log,'total no. of records : '||total_records);
fnd_file.put_line(fnd_file.log,'current no. of records to insert : '||current_records);
fnd_file.put_line(fnd_file.log,'no. of records inserted : '||(current_records-error_records));
fnd_file.put_line(fnd_file.log,'no. of records failed to insert : '||error_records);
fnd_file.put_line(fnd_file.log, '---------------------------------------------------------');
fnd_file.put_line(fnd_file.output, '---------------record validation stats-------------------');
fnd_file.put_line(fnd_file.output,'total no. of records : '||total_records);
fnd_file.put_line(fnd_file.output,'current no. of records to insert : '||(total_records-error_records));
fnd_file.put_line(fnd_file.output,'no. of records failed to insert : '||error_records);
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
/
