Home » Applications » Oracle Fusion Apps & E-Business Suite » Payroll API Package Error.
| Payroll API Package Error. [message #414966] |
Fri, 24 July 2009 03:34  |
exboy
Messages: 14
Registered: March 2009
|
Junior Member |
|
|
Hi everyone,
I am having the same error with the same Payroll API,
kindly help with how this issue was solved.
I am actually getting 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.
and how do i pass the parameters to correct the combination of person_id,object_version_number,effective_start_date and effective_end_date.
using the example below.
thank you
|
|
|
|
|
|
| Re: Payroll API Package Error. [message #414999 is a reply to message #414974] |
Fri, 24 July 2009 06:17  |
exboy
Messages: 14
Registered: March 2009
|
Junior Member |
|
|
Hi monikabhakuni,
Thanks for the reply,
really appreciate the help,
Tried but still returns the same error,
kindly help me look through the attachment and help point the changes and highlight them/paste..
Thank you
|
|
|
|
|
|
| Re: Payroll API Package Error. [message #415009 is a reply to message #415003] |
Fri, 24 July 2009 06:40 |
exboy
Messages: 14
Registered: March 2009
|
Junior Member |
|
|
Yes i have executed the apis one by one and my guess is that it is from here:
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
);
I think it has to do with the object_version_number and the effective date, but not quite sure what is wrong with the 2 parameters according to the script.
Thanks
|
|
|
|
| Re: Payroll API Package Error. [message #415012 is a reply to message #415009] |
Fri, 24 July 2009 07:04 |
monikabhakuni
Messages: 110
Registered: September 2008
Location: India
|
Senior Member |
|
|
Try it once...
here i have added a query and change it according to your naming conventions
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 => TRUNC(SYSDATE)-- Monika --l_effective_date,--It must lie between effective_start_date and effective_end_date)--CHECK it l_date,
p_datetrack_update_mode => 'CORRECTION'--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;*/
--Monika
SELECT paaf.object_version_number
,paaf.assignment_id
INTO xl_object_version_number
,vl_assignment_id
FROM per_all_people_f ppf, per_all_assignments_f paaf
WHERE ppf.person_id = paaf.person_id
AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND paaf.object_version_number = (SELECT MAX (object_version_number)
FROM per_all_assignments_f
WHERE assignment_number = to_char(rec.employee_number))
AND ppf.employee_number = rec.employee_number;
DBMS_OUTPUT.put_line ('Check Point : 4...........');
hr_assignment_api.update_emp_asg_criteria(p_validate => l_validate_mode,
p_object_version_number => xl_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;
|
|
|
|
| Re: Payroll API Package Error. [message #415107 is a reply to message #415012] |
Fri, 24 July 2009 18:00 |
exboy
Messages: 14
Registered: March 2009
|
Junior Member |
|
|
Hi ,
Thanks for all the help, been all all day and night trying to resolve the issue,
I ran the script and it gave this error message:
ORA-20001: There must be a collective agreement grade structure specified with a collective agreement
Kindly help with any ideas or modification on the scripts.
Thanks
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon May 20 15:51:50 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
