Hello.
I'm encountering the error below when trying to run hr_assignment_api.update_emp_asg_criteria into ORACLE 11.5.10.2 RDBMS : 10.2.
error message: ERROR at line 1:
ORA-20001: System Error: Procedure at Step 40
Cause: The procedure has created an error at Step 40.
Action: Contact your system administrator quoting the procedure and
Step 40.
ORA-06512: at "APPS.HR_ASSIGNMENT_API", line 16009
ORA-06512: at line 79
line 79 is P_'EFFECTIVE_DATE' so its definitely connected to dates and object_version_number..
Help!
Code now looks like:
SET serveroutput ON SIZE 1000000 FORMAT WRAPPED
SET verify OFF
SET feedback OFF
DECLARE
error_msg varchar2(2000);
l_status varchar2(10);
l_validate_cnt number;
l_ass_count number;
l_validate BOOLEAN DEFAULT FALSE;
l_city varchar2(30);
l_mode varchar2(20) := 'UPDATE';
l_organization_id number;
l_effective_date date := to_date('12-FEB-2009','DD-MON-YYYY'); --'12-FEB-2009';
l_person_id number := '29987';
l_object_version_number number := '1';
l_effective_start_date date := to_date('12-FEB-2009','DD-MON-YYYY');
l_effective_end_date date := to_date('12-FEB-2059','DD-MON-YYYY');
l_job_id per_jobs.job_id%type;
l_position_id per_positions.position_id%type;
l_location_id number := '102';
l_grade_id number;
l_supervisor_id number;
l_assignment_status_type_id number;
l_pay_basis_id number;
l_join_date date;
l_soft_coding_keyflex_id number;
l_people_group_id per_all_assignments_f.people_group_id%type;
l_payroll_id per_all_assignments_f.payroll_id%type;
l_assignment_sequence per_all_assignments_f.assignment_sequence%type;
l_comment_id per_all_assignments_f.comment_id%type;
l_normal_start varchar2(15) := '09:30';
l_normal_end varchar2(15) := '17:30';
l_frequency varchar2(10) := 'W';
l_normal_hours varchar2(25) := '7.5';
l_assignment_id number := '29884';
l_gsp_post_process_warning varchar2(30);
l_entries_changed varchar2(30);
l_old_obj_ver_number number := '1';
l_segment1 varchar2(25);
l_segment2 varchar2(25);
l_segment3 varchar2(25);
l_concatenated_segments varchar2(240);
l_group_name varchar2(100);
l_other_manager_warning boolean;
l_org_now_no_manager_warning boolean;
l_spp_delete_warning boolean;
l_entries_changed_warning varchar2(200);
l_tax_district_changed_warning boolean;
l_special_ceiling_step_id number;
l_no_managers_warning boolean;
l_other_manager_warnings boolean;
l_cagr_grade_def_id number;
l_cagr_concatenated_segments varchar2(100);
l_datetrack_update_mod varchar2(10) := 'CORRECTION';
l_total_records number := 0;
l_success_records number := 0;
l_failure_records number := 0;
BEGIN
dbms_output.put_line('######################################');
dbms_output.put_line('Data Migration Of Employee Assignments :');
dbms_output.put_line('#######################################');
dbms_output.put_line('Start Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
l_person_id := null;
l_object_version_number := null;
l_organization_id := null;
l_location_id := null;
l_grade_id := null;
l_job_id := null;
l_position_id := null;
l_supervisor_id := null;
l_assignment_status_type_id := null;
error_msg := null;
l_status := 'True';
hr_assignment_api.update_emp_asg_criteria (
p_effective_date => l_effective_date
,p_datetrack_update_mode => l_datetrack_update_mod
,p_assignment_id => l_assignment_id
,p_called_from_mass_update => NULL
,p_grade_id => l_grade_id
,p_position_id => l_position_id
,p_job_id => l_job_id
,p_payroll_id => l_payroll_id
,p_location_id => l_location_id
,p_organization_id => l_organization_id
,p_pay_basis_id => l_pay_basis_id
,p_segment1 => l_segment1
,p_segment2 => l_segment2
,p_segment3 => l_segment3
,p_employment_category => NULL
,p_concat_segments => NULL
,p_contract_id => NULL
,p_establishment_id => NULL
,p_scl_segment1 => NULL
,p_grade_ladder_pgm_id => NULL
,p_supervisor_assignment_id => NULL
,p_object_version_number => l_old_obj_ver_number --l_object_version_number
,p_special_ceiling_step_id => l_special_ceiling_step_id
,p_people_group_id => l_people_group_id
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_group_name => l_group_name
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_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 ,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);
hr_assignment_api.update_emp_asg (
p_validate => l_validate
,p_effective_date => l_effective_date
,p_datetrack_update_mode => l_datetrack_update_mod
,p_assignment_id => l_assignment_id
,p_object_version_number => l_object_version_number
,p_supervisor_id => l_supervisor_id
,p_normal_hours => l_normal_hours
,p_frequency => l_frequency
,p_time_normal_start => l_normal_start
,p_time_normal_finish => l_normal_end
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_concatenated_segments => l_concatenated_segments
,p_no_managers_warning => l_no_managers_warning
,p_other_manager_warning => l_other_manager_warnings
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
);
dbms_output.put_line('End Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('
');
END;
/
commit;
exit;
Steven