create or replace procedure updateassignment as -- Essential functionality variables l_validate_mode BOOLEAN := FALSE; l_validate BOOLEAN := FALSE; l_person_type_id NUMBER(15); l_business_group_id NUMBER; l_employee_no NUMBER; l_title VARCHAR2(30); l_first_name VARCHAR2(40); l_last_name VARCHAR2(40); l_middle_names VARCHAR2(40); l_surname VARCHAR2(40); l_preferred_name VARCHAR2(40); l_date1 DATE; l_sex VARCHAR(1); l_birthdate DATE; l_marital_status VARCHAR2(5); l_ni_number VARCHAR2(11); /*csr_ovn number;*/ l_maiden_name VARCHAR2(20); l_supervisor_id number; l_default_code_comb_id number; l_set_of_books_id number; l_comment_id number; l_CREATOR_TYPE varchar2(80); P_CAGR_GRADE_DEF_ID NUMBER; ---- IN OUT P_CAGR_CONCATENATED_SEGMENTS VARCHAR2(80); ---- OUT l_CAGR_CONCATENATED_SEGMENTS VARCHAR2(80); P_CONCATENATED_SEGMENTS VARCHAR2(80); --- OUT P_SOFT_CODING_KEYFLEX_ID NUMBER; --- IN OUT /*P_CAGR_GRADE_DEF_ID NUMBER IN OUT P_CAGR_CONCATENATED_SEGMENTS VARCHAR2 OUT P_CONCATENATED_SEGMENTS VARCHAR2 OUT P_SOFT_CODING_KEYFLEX_ID NUMBER IN OUT */ p_payroll_id number := 101; p_effective_date date; l_effective_date DATE := to_date('01-JAN-1990', 'DD-MON-YYYY'); p_datetrack_update_mode varchar2(80); p_assignment_id number; p_ASSIGNMENT_STATUS_TYPE_ID number; l_obj NUMBER; p_object_version_number number; l_object_version_number number /*:= 24*/ ; l_special_ceiling_step_id number; p_people_group_id number := 1062; l_soft_coding_keyflex_id number; l_group_name varchar2(80); l_effective_start_date date; l_effective_end_date date; l_org_now_no_manager_warning boolean := FALSE; l_other_manager_warning boolean := FALSE; l_spp_delete_warning boolean := FALSE; l_entries_changed_warning varchar2(80); l_tax_district_changed_warning boolean; l_concatenated_segments varchar2(80); l_gsp_post_process_warning varchar2(80); l_datetrack_update_mode varchar2(80) := 'CORRECTION'; p_assignment_id number; l_people_group_id number; p_element_link_id number; l_element_link_id number; p_element_entry_id number; l_element_entry_id number; l_warning boolean := FALSE; P_ORIGINAL_ENTRY_ID number; l_ORIGINAL_ENTRY_ID number; p_update_warning boolean := FALSE; l_no_managers_warning boolean := FALSE; -- API Return Variables l_person_id NUMBER; l_assignment_id NUMBER; l_per_object_version_number NUMBER; l_asg_object_version_number NUMBER; l_per_effective_start_date DATE; l_per_effective_end_date DATE; l_full_name VARCHAR2(60); l_per_comment_id NUMBER; l_assignment_sequence NUMBER; l_assignment_number VARCHAR2(10); l_name_combination_warning BOOLEAN := FALSE; l_assign_payroll_warning BOOLEAN := FALSE; l_orig_hire_warning BOOLEAN := FALSE; l_eth_code VARCHAR2(10); -- Constant variables l_module_id CONSTANT VARCHAR2(30) := 'XXFTHR_MIGRATION002'; -- Error Handling variables l_error_message VARCHAR2(150); l_error_code VARCHAR2(30); l_error_statement VARCHAR2(50); -- Count Variables l_count_total NUMBER := 0; l_count_success NUMBER := 0; -- Total number of successful rows l_cnt1 NUMBER := 0; l_errm VARCHAR2(100); l_err_at_stmt NUMBER; -- Cursor definitions CURSOR c_emp IS /*SELECT rowid row_id, assignment_number, assignment_id, null person_loaded, creation_date from xxx_update_asg where assignment_number = 2781 ORDER BY assignment_number;*/ SELECT a.rowid row_id, a.staff_id assignment_number, a.staff_id employee_number, b.assignment_id, b.assignment_status_type_id, b.person_id, b.effective_start_date, b.effective_end_date, null person_loaded, b.object_version_number, b.grade_id, c.grade_definition_id, b.job_id, b.organization_id, b.location_id, to_date('23072009', 'ddmmyyyy') creation_date from uat_staff_list_mod a, per_all_assignments_f b, per_grades c where a.staff_id = /*1192*/ 2781 /*2086*/ and a.staff_id = b.assignment_number and b.business_group_id = 241 and b.grade_id = c.grade_id and b.business_group_id = c.business_group_id ORDER BY b.assignment_number; CURSOR csr_ovn(cp_person_id IN per_all_people_f.person_id%TYPE) IS SELECT MAX(a.object_version_number) /*a.object_version_number*/ FROM per_assignments_f a, per_all_people_f b WHERE a.person_id = b.person_id AND b.employee_number = a.assignment_number AND b.person_id = cp_person_id AND a.business_group_id = 241; /* WHERE a.person_id = cp_person_id and a.assignment_number = l_assignment_number AND b.employee_number = a.assignment_number and a.business_group_id=241;*/ /* CURSOR c_FocusThread_code(c_legacy_type IN VARCHAR2, c_legacy_code IN VARCHAR2) IS SELECT FocusThread_lookup_cd FROM xxft_lookup_values WHERE legacy_lookup_cd = c_legacy_code AND legacy_lookup_type = c_legacy_type;*/ -- BEGIN DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------'); DBMS_OUTPUT.PUT_LINE('-- Started Update Assignment Status of employee load at: ' || to_char(SYSDATE, 'DD-MON-RRRR HH24:MI:SS')); DBMS_OUTPUT.PUT_LINE('--'); -- Get business group id l_error_statement := 'Pre Update of assignment, Fetch Business Group'; l_business_group_id := 241; /*OPEN csr_ovn (v_emp.std_person_id); FETCH csr_ovn INTO l_obj; -- IF csr_ovn%NOTFOUND THEN RAISE NO_DATA_FOUND; END IF; CLOSE csr_ovn;*/ OPEN csr_ovn(l_person_id); FETCH csr_ovn INTO l_obj; -- IF csr_ovn%NOTFOUND THEN RAISE NO_DATA_FOUND; END IF; CLOSE csr_ovn; -------------------------------------------------- -- Assign People Group ID -------------------------------------------------- /*SELECT ppt.person_type_id INTO l_person_type_id FROM per_person_types ppt WHERE ppt.business_group_id = l_business_group_id AND ppt.user_person_type = 'Employee';*/ SELECT ppt.people_group_id INTO l_people_group_id FROM PAY_PEOPLE_GROUPS ppt where ppt.group_name = '1.'; -- ************************************************************ -- Start Main Loop -- ************************************************************ FOR rec IN c_emp LOOP l_err_at_stmt := 10; l_employee_no := rec.assignment_number; /* l_date1 := rec.creation_date; l_obj := rec.object_version_number; l_person_id := rec.person_id;*/ /*FOR rec IN c_emp_obj LOOP*/ -- Retrieve FocusThread code for ethnic origin /* OPEN c_FocusThread_code('ETHNICITY', rec.ethnic_code); FETCH c_FocusThread_code INTO l_eth_code; IF c_FocusThread_code%NOTFOUND THEN l_eth_code := NULL; END IF; CLOSE c_FocusThread_code;*/ l_cnt1 := l_cnt1 + 1; BEGIN /*hr_employee_api.create_employee(p_validate => l_validate_mode, p_hire_date => rec.start_date, p_business_group_id => l_business_group_id, p_last_name => initcap(rec.prev_last_name), p_sex => rec.sex, p_person_type_id => l_person_type_id, p_date_of_birth => rec.birth_date, p_employee_number => rec.employee_number, p_first_name => initcap(rec.forename), p_known_as => initcap(rec.known_as), p_marital_status => rec.marital_status, p_middle_names => initcap(rec.middle_name), -- p_ni_number => rec.ni_no, p_previous_last_name => initcap(rec.prev_last_name), p_title => rec.title -- , p_nationality => rec.nationality , p_original_date_of_hire => rec.group_start_date, p_person_id => l_person_id, p_assignment_id => l_assignment_id, p_per_object_version_number => l_per_object_version_number, p_asg_object_version_number => l_asg_object_version_number, p_per_effective_start_date => l_per_effective_start_date, p_per_effective_end_date => l_per_effective_end_date, p_full_name => l_full_name, p_per_comment_id => l_per_comment_id, p_assignment_sequence => l_assignment_sequence, p_assignment_number => l_assignment_number, p_name_combination_warning => l_name_combination_warning, p_assign_payroll_warning => l_assign_payroll_warning, p_orig_hire_warning => l_orig_hire_warning);*/ hr_assignment_api.update_emp_asg(p_validate => l_validate, p_effective_date => l_effective_date, -- l_date, p_datetrack_update_mode => l_datetrack_update_mode, p_assignment_id => rec.assignment_id, p_ASSIGNMENT_STATUS_TYPE_ID => rec.ASSIGNMENT_STATUS_TYPE_ID, p_assignment_number => rec.assignment_number, p_object_version_number => rec.object_version_number, p_supervisor_id => l_supervisor_id, P_CAGR_GRADE_DEF_ID => rec.grade_definition_id, P_CAGR_CONCATENATED_SEGMENTS => l_CAGR_CONCATENATED_SEGMENTS, p_default_code_comb_id => l_default_code_comb_id, p_set_of_books_id => l_set_of_books_id, p_concatenated_segments => l_concatenated_segments, --in/out p_soft_coding_keyflex_id => l_soft_coding_keyflex_id, --in/out p_comment_id => l_comment_id, --in/out p_effective_start_date => rec.effective_start_date, --in/out p_effective_end_date => rec.effective_end_date, --in/out p_no_managers_warning => l_no_managers_warning, --in/out p_other_manager_warning => l_other_manager_warning --in/out ); /*P_CAGR_GRADE_DEF_ID NUMBER; ---- IN OUT P_CAGR_CONCATENATED_SEGMENTS VARCHAR2(80); ---- OUT P_CONCATENATED_SEGMENTS VARCHAR2(80); --- OUT P_SOFT_CODING_KEYFLEX_ID NUMBER;*/ hr_assignment_api.update_emp_asg_criteria(p_validate => l_validate_mode, p_object_version_number => l_object_version_number, p_special_ceiling_step_id => l_special_ceiling_step_id, p_people_group_id => l_people_group_id, p_payroll_id => 101, p_datetrack_update_mode => l_datetrack_update_mode, /*p_person_id => l_person_id,*/ p_assignment_id => rec.assignment_id, p_organization_id => rec.organization_id, p_location_id => rec.location_id, p_job_id => rec.job_id, /*p_position_id => rec.position_id,*/ p_soft_coding_keyflex_id => l_soft_coding_keyflex_id, p_group_name => l_group_name, p_effective_date => l_effective_date, p_effective_start_date => rec.effective_start_date, p_effective_end_date => rec.effective_end_date, p_org_now_no_manager_warning => l_org_now_no_manager_warning, p_other_manager_warning => l_other_manager_warning, p_spp_delete_warning => l_spp_delete_warning, p_entries_changed_warning => l_entries_changed_warning, p_tax_district_changed_warning => l_tax_district_changed_warning, p_concatenated_segments => l_concatenated_segments, p_gsp_post_process_warning => l_gsp_post_process_warning); PAY_ELEMENT_ENTRY_API.CREATE_ELEMENT_ENTRY(p_validate => l_validate, p_effective_date => l_effective_date, p_business_group_id => l_business_group_id, p_assignment_id => rec.assignment_id, p_CREATOR_TYPE => l_CREATOR_TYPE, p_element_link_id => l_element_link_id, p_entry_type => 'E', -- Element Entry p_effective_start_date => rec.effective_start_date, p_effective_end_date => rec.effective_end_date, p_element_entry_id => l_element_entry_id, p_object_version_number => rec.object_version_number, p_create_warning => l_warning, P_ORIGINAL_ENTRY_ID => l_ORIGINAL_ENTRY_ID); PAY_ELEMENT_ENTRY_API.UPDATE_ELEMENT_ENTRY(p_validate => l_validate, p_datetrack_update_mode => l_datetrack_update_mode, p_effective_date => l_effective_date, p_business_group_id => l_business_group_id, p_element_entry_id => l_element_entry_id, p_object_version_number => rec.object_version_number, p_effective_start_date => rec.effective_start_date, p_effective_end_date => rec.effective_end_date, p_update_warning => l_warning); /*l_assignment_id := null;*/ /* l_object_version_number := null;*/ /*l_position_definition_id := null;*/ /*l_effective_start_date := null;*/ /*l_effective_end_date := null;*/ UPDATE uat_staff_list_mod SET person_loaded = 'Y', person_id = l_person_id, assignment_id = l_assignment_id WHERE rowid = rec.row_id; l_count_success := l_count_success + 1; EXCEPTION WHEN OTHERS THEN l_errm := substr(ltrim(sqlerrm), 1, 100); -- dbms_output.put_line (to_char(l_employee_no)||' Birth: '|| -- to_char(l_date1,'DD-MON-YYYY')); -- dbms_output.put_line(sqlerrm); INSERT INTO xxft_error_log (module_id, emp_no, error_desc, run_date) VALUES (l_module_id, rec.assignment_number, l_errm, sysdate); UPDATE uat_staff_list_mod SET person_loaded = 'N' WHERE rowid = rec.row_id; END; l_err_at_stmt := 40; IF l_cnt1 >= 10 THEN COMMIT; l_cnt1 := 0; END IF; END LOOP; COMMIT; DBMS_OUTPUT.PUT_LINE('No of people assignment inserted ' || to_Char(l_count_success)); IF l_count_success != l_count_total THEN NULL; --DBMS_OUTPUT.PUT_LINE('-- *************************************************************'); --DBMS_OUTPUT.PUT_LINE('-- **Please examine the error table for a list of errored rows**'); --DBMS_OUTPUT.PUT_LINE('-- *************************************************************'); END IF; DBMS_OUTPUT.PUT_LINE('--'); DBMS_OUTPUT.PUT_LINE('-- Finshed employee load at: ' || to_char(SYSDATE, 'DD-MON-RRRR HH24:MI:SS')); DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------'); --------- --------- EXCEPTION --------- --------- WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('No of people inserted ' || to_Char(l_count_success)); DBMS_OUTPUT.PUT_LINE('Error at ' || to_char(l_err_at_stmt)); DBMS_OUTPUT.PUT_LINE(SQLERRM || SQLCODE); l_error_message := substr(ltrim(sqlerrm), 1, 100); l_error_code := sqlcode; INSERT INTO xxft_error_log (module_id, emp_no, error_desc, run_date) VALUES (l_module_id, Null, l_error_statement || ', ' || l_error_message || ', Error code: ' || l_error_code, sysdate); DBMS_OUTPUT.PUT_LINE('Unhandled Update Payroll of employee load at: ' || to_char(SYSDATE, 'DD-MON-RRRR HH24:MI:SS')); DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------'); END;