Home » Applications » Oracle Fusion Apps & E-Business Suite » Payroll API Package Error.
Payroll API Package Error. [message #414966] Fri, 24 July 2009 03:34 Go to next message
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 #414974 is a reply to message #414966] Fri, 24 July 2009 04:03 Go to previous messageGo to next message
monikabhakuni
Messages: 110
Registered: September 2008
Location: India
Senior Member
Just chk object version number and effective date..
It should lie between effective start date and effective end date of the employee
Re: Payroll API Package Error. [message #414999 is a reply to message #414974] Fri, 24 July 2009 06:17 Go to previous messageGo to next message
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 #415003 is a reply to message #414999] Fri, 24 July 2009 06:25 Go to previous messageGo to next message
monikabhakuni
Messages: 110
Registered: September 2008
Location: India
Senior Member
Hi,

Have you executed all these API's one by one..
Do you have any idea which API is not working
Re: Payroll API Package Error. [message #415009 is a reply to message #415003] Fri, 24 July 2009 06:40 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Payroll API Package Error. [message #415271 is a reply to message #415107] Mon, 27 July 2009 00:21 Go to previous messageGo to next message
monikabhakuni
Messages: 110
Registered: September 2008
Location: India
Senior Member
Hi,

Can you please mail your csv file(data file) and table structure
Re: Payroll API Package Error. [message #415549 is a reply to message #415107] Tue, 28 July 2009 04:00 Go to previous messageGo to next message
exboy
Messages: 14
Registered: March 2009
Junior Member
Hi monikabhakuni,

The datafile is to large to upload, but can u please explain what you mean by table structure and what it entails.


Re: Payroll API Package Error. [message #415556 is a reply to message #415549] Tue, 28 July 2009 04:14 Go to previous message
monikabhakuni
Messages: 110
Registered: September 2008
Location: India
Senior Member
table strucutre means columns of your staging table.
and .ctl file
Previous Topic: How oracle standard Credit Check gathers amounts to derive available credit limit for a customer
Next Topic: Update SIT
Goto Forum:
  


Current Time: Thu Dec 08 02:14:38 CST 2016

Total time taken to generate the page: 0.16428 seconds