Home » Applications » Oracle Fusion Apps & E-Business Suite » Update API failing on 'Primary Key' error.. (ORACLE 10grel2, Sun Solaris. )
| Update API failing on 'Primary Key' error.. [message #388160] |
Mon, 23 February 2009 09:50  |
MrDuke
Messages: 41
Registered: July 2007
Location: Coventry, UK
|
Member |
|
|
Im trying to load into ORACLE Financials 1 employee record that includes Assignment details. The script has been working fine with the 2 insert APIs but since Ive added the Hr_Assignment_Api.update_emp_asg_criteria its failing with the error message:
ORA-20001: The primary key specified is invalid
Cause: The primary key values specified are invalid and do not exist in
the schema.
Action: Check the primary key values before attempting to carry out the
operation again.
ORA-06512: at "APPS.HR_ASSIGNMENT_API", line 16009
ORA-06512: at "APPS.HR_ASSIGNMENT_API", line 14644
ORA-06512: at "APPS.HR_ASSIGNMENT_API", line 14429
ORA-06512: at line 232
On googling error message theres a problem with either ""this is the error you get when u pass assignment_id, effective_date and object_version_number parameter wrong.
in the Row handler it checks if the above combination is valid or not. if not valid then it will throw the above error. Make sure the assignment id and object version number you are passing are valid on that effective date. "
Thing is, I cant see where/what the problem is - can anyone out there?
Many thanks
Steven
/* Formatted on 2009/02/23 15:46 (Formatter Plus v4.8.7) */
/* Module : EMPLOYEE LOAD
*
* File : TEMPLOYEE_DTLS.sql
*
*
* Description : This script extracts data from SU_TEMPLOYEE_DETAILS and
* loads it into the Oracle tables
* per_all_people_f and per_addresses
*
* Assumptions : Data has been pre-loaded into SU_TEMPLOYEE_DETAILS
*
* Author : Steven Longstaff
* Version : 2.0
* Created : 18-Feb-2009
*
* CHANGE HISTORY.
* Vers Date Author Change
* -----------------------------------------------------------------------
*
*/
--whenever sqlerror exit failure
SET serveroutput ON SIZE 1000000 FORMAT WRAPPED
SET verify OFF
SET feedback OFF
DECLARE
-- *********
-- Debugging/error handling
-- *********
-- *********
-- Work variables
-- *********
emp_number VARCHAR2 (14);
employee_number VARCHAR2 (14);
v_rec_cnt NUMBER := 0;
insert_flag VARCHAR2 (8);
ip_p_address_id per_addresses.address_id%TYPE;
ip_p_object_version_number NUMBER;
ip_p_party_id per_addresses.party_id%TYPE;
l_person_id NUMBER;
l_employee_number VARCHAR2 (35);
l_validate BOOLEAN DEFAULT FALSE;
l_assignment_id NUMBER; -- := '1';
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 (240);
l_per_comment_id NUMBER;
l_assignment_sequence NUMBER;
l_assignment_number VARCHAR2 (35);
l_name_combination_warning BOOLEAN := FALSE;
l_assign_payroll_warning BOOLEAN := FALSE;
l_address_id NUMBER;
l_object_version_number NUMBER;
l_date DATE;
-- l_true BOOLEAN := FALSE;
l_assign_id NUMBER; -- := 29924;
l_obj NUMBER;
l_datetrack_update_mode VARCHAR2 (30) := 'CORRECTION';
l_organization_id NUMBER;
l_location_id NUMBER; -- := 101;
l_job_id NUMBER; -- := 8;
l_position_id NUMBER; -- := 1;
l_special_ceiling_step_id NUMBER;
l_effective_start_date DATE;
l_effective_end_date DATE;
l_people_group_id NUMBER;
l_group_name VARCHAR2 (30);
l_org_now_no_manager_warning BOOLEAN;
l_other_manager_warning BOOLEAN;
l_spp_delete_warning BOOLEAN;
l_entries_changed_warning VARCHAR2 (30);
l_tax_district_changed_warning BOOLEAN;
-- ***********************************
-- Get employee details info from work table
-- ***********************************
CURSOR get_employee_details
IS
SELECT std_person_id, std_assignment_id, std_validate, std_hire_date,
std_business_group_id, std_last_name, std_sex,
std_date_of_birth, std_email_address, std_employee_number,
std_first_name, std_marital_status, std_middle_names,
std_nationality, std_title, std_national_identifier,
std_address_line1, std_address_line2, std_address_line3,
std_address_line4, std_post_code, std_position_id, std_job_id,
std_location_id, std_organization_id, std_payroll_id,
std_grade_id, std_pay_basis_id, std_ass_ovn
FROM SU_TEMPLOYEE_DETAILS;
-- *****************************************
-- checks employee details info from PER_ALL_PEOPLE_F table
-- *****************************************
CURSOR c_check_employee (emp_number VARCHAR2)
IS
SELECT per.person_id, per.business_group_id, per.last_name,
per.start_date, per.date_of_birth, per.email_address,
per.employee_number, per.first_name, per.marital_status,
per.middle_names, per.nationality, per.national_identifier,
per.sex, per.title, padd.address_id, padd.primary_flag,
padd.address_line1, padd.address_line2, padd.address_line3,
padd.town_or_city, padd.postal_code, padd.telephone_number_1,
padd.object_version_number per_ovn, paas.assignment_number,
paas.object_version_number ass_ovn
FROM per_all_people_f per,
per_addresses padd,
per_all_assignments_f paas
WHERE per.employee_number = emp_number
AND per.person_id = padd.person_id
AND paas.person_id = per.person_id;
emp_rec c_check_employee%ROWTYPE;
-- ***************************************************
-- Cursor retrieves latest Object Version Number from per_assignments_f table..
-- ***************************************************
CURSOR csr_ovn (ovn NUMBER)
IS
SELECT MAX (paas.object_version_number)
FROM per_assignments_f paas, per_all_people_f per
WHERE paas.person_id = per.person_id
AND paas.assignment_number = l_assignment_number
AND per.employee_number = paas.assignment_number
AND paas.object_version_number = ovn;
BEGIN
-- ***********************************
-- Process each record in the work table
-- ***********************************
FOR v_emp IN get_employee_details
LOOP
-- ************************************
-- determine whether customer already exists
-- ************************************
OPEN c_check_employee (v_emp.std_employee_number);
FETCH c_check_employee
INTO emp_rec;
IF c_check_employee%NOTFOUND
THEN
insert_flag := 'I';
ELSE
insert_flag := 'X';
END IF;
CLOSE c_check_employee;
OPEN csr_ovn (v_emp.std_ass_ovn);
FETCH csr_ovn
INTO l_obj;
CLOSE csr_ovn;
-- ***********************************
-- Create new PER_ALL_PEOPLE_F and PER_ADDRESSES record from
-- info in table record
-- ***********************************
IF insert_flag = 'I'
THEN
BEGIN -- Importing Employee Procedure --
DBMS_OUTPUT.PUT (CHR (10));
DBMS_OUTPUT.PUT_LINE ('Importing employees.... ');
BEGIN
Hr_Employee_Api.create_gb_employee
(p_validate => l_validate,
p_hire_date => v_emp.std_hire_date,
p_business_group_id => v_emp.std_business_group_id,
p_date_of_birth => v_emp.std_date_of_birth,
p_email_address => v_emp.std_email_address,
p_first_name => v_emp.std_first_name,
p_middle_names => v_emp.std_middle_names,
p_last_name => v_emp.std_last_name,
p_sex => v_emp.std_sex,
p_ni_number => v_emp.std_national_identifier,
p_employee_number => v_emp.std_employee_number,
p_person_id => l_person_id,
p_title => v_emp.std_title,
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,
--IN/OUT
p_assignment_number => l_assignment_number,
--IN/OUT
p_name_combination_warning => l_name_combination_warning,
--IN/OUT
p_assign_payroll_warning => l_assign_payroll_warning
--IN/OUT
);
Hr_Person_Address_Api.create_person_address
(p_validate => l_validate,
-- p_effective_date => v_emp.std_hire_date,
p_effective_date => SYSDATE,
p_pradd_ovlapval_override => NULL,
p_validate_county => NULL,
p_person_id => l_person_id,
p_primary_flag => 'Y',
p_style => 'GB_GLB',
-- p_date_from => v_emp.std_hire_date,
p_date_from => SYSDATE,
p_date_to => NULL,
p_address_type => NULL,
p_comments => NULL,
p_address_line1 => v_emp.std_address_line1,
p_address_line2 => v_emp.std_address_line2,
p_address_line3 => v_emp.std_address_line3,
p_town_or_city => v_emp.std_address_line4,
p_region_1 => NULL,
p_region_2 => NULL,
p_region_3 => NULL,
p_postal_code => v_emp.std_post_code,
p_country => v_emp.std_nationality,
p_telephone_number_1 => NULL,
p_telephone_number_2 => NULL,
p_telephone_number_3 => NULL,
p_addr_attribute_category => NULL,
p_addr_attribute1 => NULL,
p_addr_attribute2 => NULL,
p_addr_attribute3 => NULL,
p_addr_attribute4 => NULL,
p_addr_attribute5 => NULL,
p_addr_attribute6 => NULL,
p_addr_attribute7 => NULL,
p_addr_attribute8 => NULL,
p_addr_attribute9 => NULL,
p_addr_attribute10 => NULL,
p_addr_attribute11 => NULL,
p_addr_attribute12 => NULL,
p_addr_attribute13 => NULL,
p_addr_attribute14 => NULL,
p_addr_attribute15 => NULL,
p_addr_attribute16 => NULL,
p_addr_attribute17 => NULL,
p_addr_attribute18 => NULL,
p_addr_attribute19 => NULL,
p_addr_attribute20 => NULL,
p_add_information13 => NULL,
p_add_information14 => NULL,
p_add_information15 => NULL,
p_add_information16 => NULL,
p_add_information17 => NULL,
p_add_information18 => NULL,
p_add_information19 => NULL,
p_add_information20 => NULL,
p_party_id => ip_p_party_id,
p_address_id => ip_p_address_id,
p_object_version_number => ip_p_object_version_number
);
Hr_Assignment_Api.update_emp_asg_criteria
(p_validate => l_validate,
p_effective_date => l_per_effective_start_date,
p_datetrack_update_mode => l_datetrack_update_mode,
p_assignment_id => l_assignment_id,
p_object_version_number => l_obj,
p_organization_id => v_emp.std_organization_id,
p_location_id => v_emp.std_location_id,
p_job_id => v_emp.std_job_id,
p_position_id => v_emp.std_position_id,
p_special_ceiling_step_id => l_special_ceiling_step_id,
p_effective_start_date => l_per_effective_start_date,
--IN/OUT
p_effective_end_date => l_effective_end_date,
--IN/OUT
p_people_group_id => l_people_group_id,
p_group_name => l_group_name,
p_org_now_no_manager_warning => l_org_now_no_manager_warning,
--IN/OUT
p_other_manager_warning => l_other_manager_warning,
--IN/OUT
p_spp_delete_warning => l_spp_delete_warning,
--IN/OUT
p_entries_changed_warning => l_entries_changed_warning,
--IN/OUT
p_tax_district_changed_warning => l_tax_district_changed_warning
--IN/OUT
);
DBMS_OUTPUT.PUT_LINE ( 'Employee No:'
|| v_emp.std_employee_number
|| ' imported successfully..'
);
/* EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT (CHR (10));
DBMS_OUTPUT.PUT_LINE ( 'Ah, Employee:'
|| v_emp.std_employee_number
|| ' failed to load.. '
|| SQLERRM
);
DBMS_OUTPUT.PUT (CHR (10));
DBMS_OUTPUT.PUT (CHR (10));
-- DBMS_OUTPUT.PUT_LINE ('..as its failed, its goin in the ERRORS records table..' );
-- DBMS_OUTPUT.PUT (CHR (10));
-- DBMS_OUTPUT.PUT (CHR (10)); */
END;
END;
DBMS_OUTPUT.PUT (CHR (10));
v_rec_cnt := v_rec_cnt + 1;
DBMS_OUTPUT.PUT_LINE ( 'There were '
|| v_rec_cnt
|| ' records read in..'
);
DBMS_OUTPUT.PUT (CHR (10));
-- ******************************
-- End of customer related details
-- ******************************
END IF;
END LOOP;
COMMIT;
END;
/
EXIT;
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jan 25 19:43:00 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
