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 this 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.
For effective date i think the issue is :

hr_assignment_api.update_emp_asg(p_validate => l_validate,
p_effective_date => l_effective_date, -- l_date,


Please help with samples

thank you

Mr Exboy, if you are calling that specific api, the primary key will be assignment_id (p_assignment_id parameter) and the effective date. Essentially the message is saying that it can't find the row that you think you want to update, either because the assignment_id doesn't exist at all, or because the effective date you're supplying is earlier than the earliest effective_Start_Date for that assignment_id

Hi CT,
Thanks for replying
This is the code ,
can u help modify if to reflect your solution:


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.

Mr Exboy,

Sadly I don't have the time to look at this too deeply, save to say that if this is a migration exercise then I think you are trying to do too much in one program.

If it were me, I'd be writing several programs, each dealing with a particular entity, e.g.: Load the employees; update the assignments; create element entries; update (standard-linked) element entries - that kind of thing.

Also, I found the code very difficult to follow as there are large lumps of it commented out. As a matter of good coding practice, it would be most beneficial to completely rid the program(s) of code that isn't used. Don't get me wrong, we've all done it, and obviously in the short term there might be a good reason to do so. However, it does pay not to leave it there too long. If your site is adopting any kind of version control on its code and/or documentation, then with care you can refer back to a previous version of a program if necessary for that code snippet that you'd previously gotten rid of but now need.

PS Mr Admin, I think this thread probably belongs in the Data Migration forum or similar, rather than DBA. Can you move it please?

Thread moved.

By the way, which API call is throwing the error? You have 4 uncommented-out api calls. Since there is no additional info reported in the error how do you know which of the API calls is the one giving you grief?

Either wrap each call within its own block, and trap exceptions locally, or (at the very least) maintain some form of "where am i in the process" flag and append this to the single exception handler message.

regards

Barry

Hi bcooper,

Thanks for the reply

After modifying the API it gives me this error:

ORA-20001: There must be a collective agreement grade structure specified with a collective agreement

Kindly help out on modifying the script, i do not need the commented APIs, can u look through and help point out.

Thanks

So the question becomes "Are you using collective agreements within your system"?
If not, then i would suggest using the overriden version of the assignment API that does not specify CAGR parameters, or simply not supply them.

If you are using collective agreements then this error suggests that the set-up of these collective agreements is not correct, or the grade you are supplying does not tie-up with the supplied collective agreement structure.

Can u take a look at the script and help tune it?.

Dear Mr Exboy

Please take this in the spirit it is intended: You'd be more likely to get some constructive assistance if you were to care to take on board what has already been said by bcooper and I, and have a go at improving it yourself. Even if doing so still gives rise to errors, personally I would be more inclined to offer further assistance if I could see for myself you were at least trying to resolve this issue yourself, and understanding at least something of what you were doing, rather than simply inviting someone to fix the problem for you. You will learn nothing from that approach, I assure you.

Also, can we have a bit more background as to what exactly you are trying to achieve. What is your overall mission - is it a migration, is it HR and/or Payroll, what sort of population are you having to load? What is your project timescale, roughly? How about yourself? On a scale of 0 to 10, what sort of experience have you in doing this sort of thing? If you've very little or none at all, then that's OK, there's no shame in that. It does however give others reading it a sense of perspective as to the problem you face.

Like I said, no offence meant in any of this.

Hi CT,

Thanks for the reply, but sincerely i have already put in my effort and i have tried to resolve it my self, before even coming to this forum to seek assistance,but i have not been able to make headway much an d i have learned a lot, but since i this forum is also intended as a online community for help i thought there is no shame in asking for help.



It is a migration of employees from one business group to another using a Payroll API.

I am trying to sort a population of employees of about 3000 in number.


My project timescale is already almost over, since the API has been generating errors from one step of resolution to another.


I am an oracle apps DBA and on a sacle of 10 , i can score myself

6,

I have only about one year experience and i am only doing this because i am involved in the payroll project on theoracle e-business suite for my workplace for the first time.

Trust me i am not offended.

Kindly help out.


Best Regards.