ORA-20001: HR_7220_INVALID_PRIMARY

[Denish.shan]

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;

[ERP_GURU]

Hi Denish,

I saw your package..

Your package is below 200 lines, still you are getting error at line 1244. Please explain this if you are using it somewhere.

You should not use these many paramters to call any package, try to get the assignment_id as a parameter and find out other parameter values inside the package body.

Suppose you have to update pay cost allocations for 1000 people, how you are doing it here.

Specifically for your error here, add one begin and end for update API block and add one more exception block after update API and before that end. Print assignment_id for every row updated and apply a row commit logic menas after every record commit or else rollback and print the assignment id.

Let me know what you get.
Thanks!!