Hi ,
This is the PAYROLL API code ,
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;
It gives the error:
ORA-20001: The primary key specified is invalid
Cause: The primary key values specified are invalid and do not exist in the schema.