ORA-20001: HR_7220_INVALID_PRIMARY
Posted: Sun Sep 26, 2010 11:36 pm
Dear Oracle apps guru,
I need to update or correction in PAY_COST_ALLOCATION_API.UPDATE_COST_ALLOCATION. while I am doing the update I am getting the error
ORA-20001: HR_7220_INVALID_PRIMARY_KEY:
ORA-06512: at "APPS.PAY_COST_ALLOCATION_API", line 1244
ORA-06512: at "APPS.SEAHR_ASSIGN_CRIT_PKG", line 197
ORA-06512: at line 1
I don't what is the reason for this error. I have tried to reslove it many ways but i am not able to slove. I am new oracle apps. please do let me know what is the reason for this error and how to reslove it
I have enclosed the code below. I the code is executing with out compilation errors and quries are working fine, able to records from the data base but while inserting in to api. I am ecountering this error
set serveroutput on;
create or replace package SEAHR_ASSIGN_CRIT_PKG as
procedure SEAHR_ASSIGN_CRIT_CREATE
(
p_employee_number in number,
p_assignment_supervisor in Number,
p_assignment_job in number,
p_assignment_grade in number,
p_assignment_organization in number,
p_assignment_location in number,
p_assignment_shift in varchar2,
p_assignment_category in varchar2,
p_costing_dev in varchar2,
p_costing_cost_center in varchar2
/*p_Assignment_grade in number,
p_assignment_job in number,
p_effective_Start_Date in date,
p_job_family in varchar2,
p_Job_function in varchar2,
p_job_title in varchar2,
p_Nonflex_programplan_option in varchar2,
p_proc_li_event_commit_proceed in varchar2,
p_proc_li_events_covered in varchar2,
p_proc_li_events_desig_depend in varchar2,
p_process_li_events_desiginee in varchar2,
p_salary_approved in varchar2,
p_salary_change_date in date,
P_salary_new_value in number,
p_view_person_li_event_name in varchar2,
p_vw_per_li_event_occuredDate in date,
p_vw_person_life_events_status in varchar2,
p_v_per_li_eve_unprocessedDate in date,
p_effective_Start_Date1 in date,
p_assignment_location in number,
p_effective_start_Date2 in date,
p_salary_proposal_reason in varchar2,
p_sal_pro_comp_change_value in number,
p_sal_pro_comp_reason in varchar2,
p_assingment_group_shift in varchar2,
p_effective_start_date3 in date,
p_assignment_organization in varchar2,
p_effective_start_date4 in date,
p_costing_cost_center in varchar2,
p_costing_Dev in varchar2,
p_effective_start_date5 in date */
);
end SEAHR_ASSIGN_CRIT_PKG;
/
CREATE OR REPLACE package body SEAHR_ASSIGN_CRIT_PKG as
/* Wrapper procedure for 'Create Assignment tegrator */
procedure SEAHR_ASSIGN_CRIT_CREATE(
p_employee_number in number,
p_assignment_supervisor in Number,
p_assignment_job in number,
p_assignment_grade in number,
p_assignment_organization in number,
p_assignment_location in number,
p_assignment_shift in varchar2,
p_assignment_category in varchar2,
p_costing_dev in varchar2,
p_costing_cost_center in varchar2
/* p_effective_Start_Date in date,
p_job_family in varchar2,
p_Job_function in varchar2,
p_job_title in varchar2,
p_Nonflex_programplan_option in varchar2,
p_proc_li_event_commit_proceed in varchar2,
p_proc_li_events_covered in varchar2,
p_proc_li_events_desig_depend in varchar2,
p_process_li_events_desiginee in varchar2,
p_salary_approved in varchar2,
p_salary_change_date in date,
P_salary_new_value in number,
p_view_person_li_event_name in varchar2,
p_vw_per_li_event_occuredDate in date,
p_vw_person_life_events_status in varchar2,
p_v_per_li_eve_unprocessedDate in date,
p_effective_Start_Date1 in date,
p_assignment_location in number,
p_effective_start_Date2 in date,
p_salary_proposal_reason in varchar2,
p_sal_pro_comp_change_value in number,
p_sal_pro_comp_reason in varchar2,
p_effective_start_date3 in date,
p_effective_start_date4 in date,
p_costing_cost_center in varchar2,
p_costing_Dev in varchar2,
p_effective_start_date5 in date */
)
is
p_effective_date date :=to_date('02-FEB-2002','DD-MON-YYYY');
p_effective_assign_date date :=to_date('23-SEP-2001','DD-MON-YYYY');
p_effective_end_date date:=to_date('31-DEC-2003','DD-MON-YYYY');
l_validate boolean := false;
l_person_id number;
l_assignment_id number;
l_mode_asg_date date;
l_datetrack_update_mode varchar2(30);
l_curr_cost_alloc_ovn number(9);
l_curr_cost_allocation_id number(15);
l_cost_alloc_keyflex_id number(9);
l_effective_start_date date;
l_effective_end_date date;
l_con_seg varchar2(30);
l_concat_variable varchar2(200);
begin
l_concat_variable:=p_costing_dev||'.'||p_costing_cost_center;
begin
select distinct papf1.person_id
into l_person_id
from per_all_people_f papf1
where papf1.employee_number =p_employee_number;
dbms_output.put_line(l_person_id);
exception
when no_data_found then
hr_utility.set_message(800,'Employee Number is valid');
hr_utility.raise_error;
when others then
hr_utility.set_message(800,'Error gettg Supervisor No');
hr_utility.raise_error;
end;
/* begin
select distinct cost_allocation_keyflex_id
into l_curr_cost_allocation_id
from PAY_COST_ALLOCATION_KEYFLEX
where CONCATENATED_SEGMENTS =l_concat_variable;
dbms_output.put_line(l_curr_cost_allocation_id);
exception
when no_data_found then
hr_utility.set_message(800,'cost_allocation_keyflex_id Number is valid');
hr_utility.raise_error;
when others then
hr_utility.set_message(800,'Error gettg cost_allocation_keyflex_id');
hr_utility.raise_error;
end; */
begin
select distinct assignment_id
into l_assignment_id
from per_assignments_f paf
where paf.person_id =l_person_id
and p_effective_assign_date between effective_start_date and effective_end_date;
dbms_output.put_line(l_assignment_id);
exception
when no_data_found then
hr_utility.set_message (800, '1# Assignment ID is not found');
hr_utility.raise_error;
when others then
hr_utility.set_message (800, 'Error gettg Acce. Assgment ID');
hr_utility.raise_error;
end;
/* to get the pay_cost_allocation_id by assignment_id */
begin
select distinct cost_allocation_id
into l_curr_cost_allocation_id
from pay_cost_allocations_f pcaf
where pcaf.assignment_id=l_assignment_id;
dbms_output.put_line('The Cost Allocation id is '||l_curr_cost_allocation_id);
exception
when no_data_found then
hr_utility.set_message (800, '1# cost Allocation ID is not found');
hr_utility.raise_error;
when others then
hr_utility.set_message (800, 'Error gettg Acce. cost Allocation ID');
hr_utility.raise_error;
end;
begin
select distinct cost_allocation_keyflex_id
into l_cost_alloc_keyflex_id
from pay_cost_allocations_f pcaf
where pcaf.assignment_id=l_assignment_id and pcaf.effective_end_date=p_effective_end_date;
dbms_output.put_line('The Cost Allocation keyflex id is '||l_cost_alloc_keyflex_id);
exception
when no_data_found then
hr_utility.set_message (800, '1# cost Allocation ID is not found');
hr_utility.raise_error;
when others then
hr_utility.set_message (800, 'Error gettg Acce. cost Allocation ID');
hr_utility.raise_error;
end;
begin
select effective_start_date,object_version_number
into l_mode_asg_date,l_curr_cost_alloc_ovn
from pay_cost_allocations_f pcaf
where pcaf.cost_allocation_id=l_curr_cost_allocation_id
and pcaf.assignment_id=l_assignment_id
and trunc(p_effective_date) between effective_start_date and effective_end_date;
exception
when no_data_found then
hr_utility.set_message (800, 'Effective Start Date is not found');
hr_utility.raise_error;
when others then
hr_utility.set_message (800, 'Error gettg Acce. Assgment ID');
hr_utility.raise_error;
end;
if trunc(l_mode_asg_date)=trunc(p_effective_date) then
l_datetrack_update_mode :='CORRECTION';
else
l_datetrack_update_mode :='UPDATE';
end if;
dbms_output.put_line('The person_id is'||l_person_id);
dbms_output.put_line('The assignment_id is'||l_assignment_id);
dbms_output.put_line('The employee_number is'||p_employee_number);
dbms_output.put_line('The assingment supervisor is'||p_assignment_supervisor);
dbms_output.put_line('The assignment_job is'||p_assignment_job);
dbms_output.put_line('the Assignment organization is'||p_assignment_organization);
dbms_output.put_line('the Assignment location is'||p_assignment_location);
dbms_output.put_line('the Assignment shift is'||p_assignment_shift);
dbms_output.put_line('the Assignment category is'||p_assignment_category);
dbms_output.put_line('the Costing Dev is'||p_costing_dev);
dbms_output.put_line('the Costing cost center is'||p_costing_cost_center);
dbms_output.put_line('the l_mode_asg_date is'||l_mode_asg_date);
dbms_output.put_line('the object version number is'||l_curr_cost_alloc_ovn);
dbms_output.put_line('The l_datetrack_update_mode is'||l_datetrack_update_mode);
l_concat_variable:=p_costing_dev||'.'||p_costing_cost_center;
dbms_output.put_line('the Costing cost center is'||p_costing_cost_center);
dbms_output.put_line('the Costing Dev is'||p_costing_dev);
dbms_output.put_line('the concat_variable is'||l_concat_variable);
PAY_COST_ALLOCATION_API.UPDATE_COST_ALLOCATION
(p_validate => l_validate
,P_EFFECTIVE_DATE => p_effective_date
,P_DATETRACK_UPDATE_MODE => l_datetrack_update_mode
,P_COST_ALLOCATION_ID => l_curr_cost_allocation_id
,P_OBJECT_VERSION_NUMBER => l_curr_cost_alloc_ovn
,P_SEGMENT1 => p_costing_Dev
,P_SEGMENT2 => p_costing_cost_center
,P_COMBINATION_NAME => l_con_seg
,P_COST_ALLOCATION_KEYFLEX_ID => l_cost_alloc_keyflex_id
,P_EFFECTIVE_START_DATE => p_effective_date
,P_EFFECTIVE_END_DATE => p_effective_end_date
);
dbms_output.put_line('the update mode api is executed');
end SEAHR_ASSIGN_CRIT_CREATE;
end SEAHR_ASSIGN_CRIT_PKG;
I need to update or correction in PAY_COST_ALLOCATION_API.UPDATE_COST_ALLOCATION. while I am doing the update I am getting the error
ORA-20001: HR_7220_INVALID_PRIMARY_KEY:
ORA-06512: at "APPS.PAY_COST_ALLOCATION_API", line 1244
ORA-06512: at "APPS.SEAHR_ASSIGN_CRIT_PKG", line 197
ORA-06512: at line 1
I don't what is the reason for this error. I have tried to reslove it many ways but i am not able to slove. I am new oracle apps. please do let me know what is the reason for this error and how to reslove it
I have enclosed the code below. I the code is executing with out compilation errors and quries are working fine, able to records from the data base but while inserting in to api. I am ecountering this error
set serveroutput on;
create or replace package SEAHR_ASSIGN_CRIT_PKG as
procedure SEAHR_ASSIGN_CRIT_CREATE
(
p_employee_number in number,
p_assignment_supervisor in Number,
p_assignment_job in number,
p_assignment_grade in number,
p_assignment_organization in number,
p_assignment_location in number,
p_assignment_shift in varchar2,
p_assignment_category in varchar2,
p_costing_dev in varchar2,
p_costing_cost_center in varchar2
/*p_Assignment_grade in number,
p_assignment_job in number,
p_effective_Start_Date in date,
p_job_family in varchar2,
p_Job_function in varchar2,
p_job_title in varchar2,
p_Nonflex_programplan_option in varchar2,
p_proc_li_event_commit_proceed in varchar2,
p_proc_li_events_covered in varchar2,
p_proc_li_events_desig_depend in varchar2,
p_process_li_events_desiginee in varchar2,
p_salary_approved in varchar2,
p_salary_change_date in date,
P_salary_new_value in number,
p_view_person_li_event_name in varchar2,
p_vw_per_li_event_occuredDate in date,
p_vw_person_life_events_status in varchar2,
p_v_per_li_eve_unprocessedDate in date,
p_effective_Start_Date1 in date,
p_assignment_location in number,
p_effective_start_Date2 in date,
p_salary_proposal_reason in varchar2,
p_sal_pro_comp_change_value in number,
p_sal_pro_comp_reason in varchar2,
p_assingment_group_shift in varchar2,
p_effective_start_date3 in date,
p_assignment_organization in varchar2,
p_effective_start_date4 in date,
p_costing_cost_center in varchar2,
p_costing_Dev in varchar2,
p_effective_start_date5 in date */
);
end SEAHR_ASSIGN_CRIT_PKG;
/
CREATE OR REPLACE package body SEAHR_ASSIGN_CRIT_PKG as
/* Wrapper procedure for 'Create Assignment tegrator */
procedure SEAHR_ASSIGN_CRIT_CREATE(
p_employee_number in number,
p_assignment_supervisor in Number,
p_assignment_job in number,
p_assignment_grade in number,
p_assignment_organization in number,
p_assignment_location in number,
p_assignment_shift in varchar2,
p_assignment_category in varchar2,
p_costing_dev in varchar2,
p_costing_cost_center in varchar2
/* p_effective_Start_Date in date,
p_job_family in varchar2,
p_Job_function in varchar2,
p_job_title in varchar2,
p_Nonflex_programplan_option in varchar2,
p_proc_li_event_commit_proceed in varchar2,
p_proc_li_events_covered in varchar2,
p_proc_li_events_desig_depend in varchar2,
p_process_li_events_desiginee in varchar2,
p_salary_approved in varchar2,
p_salary_change_date in date,
P_salary_new_value in number,
p_view_person_li_event_name in varchar2,
p_vw_per_li_event_occuredDate in date,
p_vw_person_life_events_status in varchar2,
p_v_per_li_eve_unprocessedDate in date,
p_effective_Start_Date1 in date,
p_assignment_location in number,
p_effective_start_Date2 in date,
p_salary_proposal_reason in varchar2,
p_sal_pro_comp_change_value in number,
p_sal_pro_comp_reason in varchar2,
p_effective_start_date3 in date,
p_effective_start_date4 in date,
p_costing_cost_center in varchar2,
p_costing_Dev in varchar2,
p_effective_start_date5 in date */
)
is
p_effective_date date :=to_date('02-FEB-2002','DD-MON-YYYY');
p_effective_assign_date date :=to_date('23-SEP-2001','DD-MON-YYYY');
p_effective_end_date date:=to_date('31-DEC-2003','DD-MON-YYYY');
l_validate boolean := false;
l_person_id number;
l_assignment_id number;
l_mode_asg_date date;
l_datetrack_update_mode varchar2(30);
l_curr_cost_alloc_ovn number(9);
l_curr_cost_allocation_id number(15);
l_cost_alloc_keyflex_id number(9);
l_effective_start_date date;
l_effective_end_date date;
l_con_seg varchar2(30);
l_concat_variable varchar2(200);
begin
l_concat_variable:=p_costing_dev||'.'||p_costing_cost_center;
begin
select distinct papf1.person_id
into l_person_id
from per_all_people_f papf1
where papf1.employee_number =p_employee_number;
dbms_output.put_line(l_person_id);
exception
when no_data_found then
hr_utility.set_message(800,'Employee Number is valid');
hr_utility.raise_error;
when others then
hr_utility.set_message(800,'Error gettg Supervisor No');
hr_utility.raise_error;
end;
/* begin
select distinct cost_allocation_keyflex_id
into l_curr_cost_allocation_id
from PAY_COST_ALLOCATION_KEYFLEX
where CONCATENATED_SEGMENTS =l_concat_variable;
dbms_output.put_line(l_curr_cost_allocation_id);
exception
when no_data_found then
hr_utility.set_message(800,'cost_allocation_keyflex_id Number is valid');
hr_utility.raise_error;
when others then
hr_utility.set_message(800,'Error gettg cost_allocation_keyflex_id');
hr_utility.raise_error;
end; */
begin
select distinct assignment_id
into l_assignment_id
from per_assignments_f paf
where paf.person_id =l_person_id
and p_effective_assign_date between effective_start_date and effective_end_date;
dbms_output.put_line(l_assignment_id);
exception
when no_data_found then
hr_utility.set_message (800, '1# Assignment ID is not found');
hr_utility.raise_error;
when others then
hr_utility.set_message (800, 'Error gettg Acce. Assgment ID');
hr_utility.raise_error;
end;
/* to get the pay_cost_allocation_id by assignment_id */
begin
select distinct cost_allocation_id
into l_curr_cost_allocation_id
from pay_cost_allocations_f pcaf
where pcaf.assignment_id=l_assignment_id;
dbms_output.put_line('The Cost Allocation id is '||l_curr_cost_allocation_id);
exception
when no_data_found then
hr_utility.set_message (800, '1# cost Allocation ID is not found');
hr_utility.raise_error;
when others then
hr_utility.set_message (800, 'Error gettg Acce. cost Allocation ID');
hr_utility.raise_error;
end;
begin
select distinct cost_allocation_keyflex_id
into l_cost_alloc_keyflex_id
from pay_cost_allocations_f pcaf
where pcaf.assignment_id=l_assignment_id and pcaf.effective_end_date=p_effective_end_date;
dbms_output.put_line('The Cost Allocation keyflex id is '||l_cost_alloc_keyflex_id);
exception
when no_data_found then
hr_utility.set_message (800, '1# cost Allocation ID is not found');
hr_utility.raise_error;
when others then
hr_utility.set_message (800, 'Error gettg Acce. cost Allocation ID');
hr_utility.raise_error;
end;
begin
select effective_start_date,object_version_number
into l_mode_asg_date,l_curr_cost_alloc_ovn
from pay_cost_allocations_f pcaf
where pcaf.cost_allocation_id=l_curr_cost_allocation_id
and pcaf.assignment_id=l_assignment_id
and trunc(p_effective_date) between effective_start_date and effective_end_date;
exception
when no_data_found then
hr_utility.set_message (800, 'Effective Start Date is not found');
hr_utility.raise_error;
when others then
hr_utility.set_message (800, 'Error gettg Acce. Assgment ID');
hr_utility.raise_error;
end;
if trunc(l_mode_asg_date)=trunc(p_effective_date) then
l_datetrack_update_mode :='CORRECTION';
else
l_datetrack_update_mode :='UPDATE';
end if;
dbms_output.put_line('The person_id is'||l_person_id);
dbms_output.put_line('The assignment_id is'||l_assignment_id);
dbms_output.put_line('The employee_number is'||p_employee_number);
dbms_output.put_line('The assingment supervisor is'||p_assignment_supervisor);
dbms_output.put_line('The assignment_job is'||p_assignment_job);
dbms_output.put_line('the Assignment organization is'||p_assignment_organization);
dbms_output.put_line('the Assignment location is'||p_assignment_location);
dbms_output.put_line('the Assignment shift is'||p_assignment_shift);
dbms_output.put_line('the Assignment category is'||p_assignment_category);
dbms_output.put_line('the Costing Dev is'||p_costing_dev);
dbms_output.put_line('the Costing cost center is'||p_costing_cost_center);
dbms_output.put_line('the l_mode_asg_date is'||l_mode_asg_date);
dbms_output.put_line('the object version number is'||l_curr_cost_alloc_ovn);
dbms_output.put_line('The l_datetrack_update_mode is'||l_datetrack_update_mode);
l_concat_variable:=p_costing_dev||'.'||p_costing_cost_center;
dbms_output.put_line('the Costing cost center is'||p_costing_cost_center);
dbms_output.put_line('the Costing Dev is'||p_costing_dev);
dbms_output.put_line('the concat_variable is'||l_concat_variable);
PAY_COST_ALLOCATION_API.UPDATE_COST_ALLOCATION
(p_validate => l_validate
,P_EFFECTIVE_DATE => p_effective_date
,P_DATETRACK_UPDATE_MODE => l_datetrack_update_mode
,P_COST_ALLOCATION_ID => l_curr_cost_allocation_id
,P_OBJECT_VERSION_NUMBER => l_curr_cost_alloc_ovn
,P_SEGMENT1 => p_costing_Dev
,P_SEGMENT2 => p_costing_cost_center
,P_COMBINATION_NAME => l_con_seg
,P_COST_ALLOCATION_KEYFLEX_ID => l_cost_alloc_keyflex_id
,P_EFFECTIVE_START_DATE => p_effective_date
,P_EFFECTIVE_END_DATE => p_effective_end_date
);
dbms_output.put_line('the update mode api is executed');
end SEAHR_ASSIGN_CRIT_CREATE;
end SEAHR_ASSIGN_CRIT_PKG;