create or replace PROCEDURE "XX_UPDATE_PER_ASSIGNMENT" IS lv_validate boolean default false; --in-- lv_effective_date date default sysdate; --in-- lv_cagr_grade_def_id varchar2(100); lv_cagr_concatenated_segments varchar2(30); --out nocopy varchar2 lv_soft_coding_keyflex_id number;-- out nocopy number lv_effective_start_date date; -- out lv_effective_end_date date;-- out lv_other_manager_warning boolean;-- out boolean lv_hourly_salaried_warning boolean; -- out boolean lv_assignment_id NUMBER(10); --INPUT AND NOT NULL, lv_grade_id NUMBER(15); lv_position_id NUMBER(15); lv_job_id NUMBER(15); lv_assignment_status_type_id NUMBER(9); --in and NOT NULL lv_ass_attribute1 varchar2 (150); -- career contract expire date-- lv_ass_attribute3 varchar2(150); --career contract type--- lv_ass_attribute4 varchar2 (150); ---assignment end date----- lv_payroll_id NUMBER(9); --in-- lv_location_id NUMBER(15); --in-- lv_supervisor_id NUMBER(10); lv_assignment_number varchar2(30); --in lv_person_id NUMBER(10); --in and NOT NULL, lv_organization_id NUMBER(15); --in and NOT NULL, lv_people_group_id NUMBER(15); lv_assignment_sequence NUMBER(15); --in and NOT NULL, lv_assignment_type VARCHAR2(1); -- in and NOT NULL, lv_primary_flag VARCHAR2(30);--in and NOT NULL, lv_change_reason VARCHAR2(300); --in-- lv_comment_id NUMBER(15); lv_manager_flag VARCHAR2(30 ); --in-- lv_normal_hours NUMBER(22,3); --in-- lv_period_of_service_id NUMBER(15); lv_probation_period NUMBER(22) default 0; lv_bargaining_unit_code varchar2(30); lv_hourly_salaried_code VARCHAR2(30); lv_contract_id NUMBER(9); lv_TITLE VARCHAR2(30); lv_employee_category VARCHAR2(30); lv_supervisor_assignment_id NUMBER(15); lv_group_name varchar2(30); lv_org_now_no_manager_warning boolean; lv_special_ceiling_step_id number; lv_spp_delete_warning boolean; lv_entries_changed_warning varchar2(30); lv_tax_district_c_warning boolean; lv_gsp_post_process_warning varchar2(30); lv_concatenated_segments varchar2(30); lv_error_message varchar2(2000); lv_object_version_number number; lv_data_transfered varchar2(1); LV_MAX_EFF_START_DATE DATE; lv_datetrack_update_mode varchar2(20); lv_effective_start_date2 date; lv_career_entry number; lv_max_eff_s_date date; lv_debug varchar2(100); begin -- loop for each person in the update_gb_emp_asg table -- there could be many entries in this table (for staff who have -- had career changes). for person in (select distinct person_id from my_apps.update_gb_emp_asg where DATA_TRANSFERRED='N') loop -- first task is to make sure that the data already in the system is -- correct. An issue is those assignments that start before the -- 1 April. We need to end date these. lv_debug := 'person id = ' || person.person_id; -- find out the start date of this person's most recent assignment begin select max(effective_start_date) into lv_effective_start_date from hr.per_all_assignments_f where person_id = person.person_id; -- Each person will only have one assignment at a time as we have been using -- a shared instance of Oracle HR. exception when no_data_found then lv_effective_start_date := null; end; -- if this person should have been given an assignment in a previous -- stage - if they don't have an assingment by now, it's too late to -- fix it. if lv_effective_start_date is null then UPDATE my_apps.update_gb_emp_asg SET DATA_TRANSFERRED ='F', error_message = 'No assignment found in PER_ALL_ASSIGNMENTS_F.' WHERE person_id= person.person_id; COMMIT; else -- we are good to proceed. if lv_effective_start_date < to_date('01-APR-2007') then -- we need to end-date this assignemnt. -- we do this by updating their most recent assignment -- when we call the first API. lv_datetrack_update_mode := 'UPDATE'; else -- We can safely edit the existing record lv_datetrack_update_mode := 'CORRECTION'; end if; -- Initialise this variable. This is used to track how many career changes -- a person has had since 1 April 2007. Each career change will need -- to start with an update. lv_career_entry := 0; -- Get the career record from 1 April onwards FOR career IN (SELECT * FROM my_apps.update_gb_emp_asg ua3 where UA3.person_id = person.person_id and ua3.data_transfered='N' order by ua3.effective_start_date ) LOOP -- increment the career entry number lv_career_entry := lv_career_entry + 1; -- test to see if this is another career record for this person if lv_career_entry > 1 then -- if so, we need to update the record to create a new row -- in the assignments table. lv_datetrack_update_mode := 'UPDATE'; end if; lv_debug := 'Assignment loop update_mode = ' || lv_datetrack_update_mode; -- Get the object version number for the assignment record we -- are going to update or correct. -- In every case this should be the most recent entry. select MAX(AA.OBJECT_VERSION_NUMBER) into lv_object_version_number from HR.PER_ALL_ASSIGNMENTS_F AA WHERE AA.person_id = person.person_id; lv_debug := ' effective_date '|| career.EFFECTIVE_START_DATE || 'object version number = '|| lv_object_version_number || ' change type '|| lv_datetrack_update_mode; -- set variables lv_special_ceiling_step_id := null; lv_soft_coding_keyflex_id := null; lv_group_name := null; lv_org_now_no_manager_warning := null; lv_other_manager_warning := null; lv_spp_delete_warning := null; lv_entries_changed_warning := null; lv_concatenated_segments := null; lv_gsp_post_process_warning := null; lv_tax_district_c_warning :=null; -- now we are ready to call the first API -- we need to call two APIs because each one only does some of the -- attributes that we need. begin -- we call the UPDATE_GB_EMP_ASG first hr_assignment_api.update_gb_emp_asg ( p_validate => false ,p_effective_date => career.effective_start_date--lv_effective_start_date ,p_datetrack_update_mode => lv_datetrack_update_mode ,p_assignment_id => career.assignment_id ,p_object_version_number => lv_object_version_number ,p_supervisor_id => career.supervisor_id ,p_assignment_number => TRIM(career.assignment_number) ,p_change_reason => TRIM(career.change_reason) ,p_frequency => career.frequency ,p_manager_flag => career.manager_flag ,p_normal_hours => career.normal_hours ,p_perf_review_period => career.perf_review_period ,p_perf_review_period_frequency => career.perf_review_period_frequency ,p_probation_period => 6 ,p_probation_unit => 'M' ,p_sal_review_period => 1 ,p_sal_review_period_frequency => 'Y' ,p_time_normal_finish => career.time_normal_finish ,p_time_normal_start => career.time_normal_start ,p_ass_attribute1 => TO_CHAR(to_date(career.ass_attribute1),'YYYY/MM/DD HH24:MI:SS' ) ,p_ass_attribute3 => TRIM(career.ass_attribute3) ,p_ass_attribute4 => TO_CHAR(career.effective_end_date,'YYYY/MM/DD HH24:MI:SS' ) ,p_employee_category => lv_employee_category ,p_supervisor_assignment_id => career.supervisor_assignment_id ,p_cagr_grade_def_id => lv_cagr_grade_def_id ,p_cagr_concatenated_segments => lv_cagr_concatenated_segments ,p_concatenated_segments => lv_concatenated_segments ,p_soft_coding_keyflex_id => lv_soft_coding_keyflex_id ,p_comment_id => lv_comment_id ,p_effective_start_date => lv_effective_start_date--career.effective_start_date ,p_effective_end_date => lv_effective_end_date ,p_no_managers_warning => lv_org_now_no_manager_warning ,p_other_manager_warning => lv_other_manager_warning ,p_hourly_salaried_warning => lv_hourly_salaried_warning ); lv_debug := 'First API done.'; -- Get the new Object version number select MAX(AA.OBJECT_VERSION_NUMBER) into lv_object_version_number from HR.PER_ALL_ASSIGNMENTS_F AA WHERE AA.person_id = person.person_id; lv_debug := 'Start second API.'; -- before we can say that this record has been a success -- we need to call the next API, but this time as a correction. hr_assignment_api.update_emp_asg_criteria (p_effective_date => career.effective_START_Date ,p_datetrack_update_mode => 'CORRECTION' ,p_assignment_id => career.assignment_id ,p_validate => false ,p_called_from_mass_update => false ,p_grade_id => career.grade_id ,p_position_id => TRIM(career.position_id) ,p_job_id => career.job_id ,p_payroll_id => career.payroll_id ,p_location_id => career.location_id ,p_organization_id => career.organization_id ,p_pay_basis_id => career.pay_basis_id ,p_employment_category => career.ASSIGNMENT_CATEGORY ,p_object_version_number => lv_object_version_number ,p_special_ceiling_step_id => lv_special_ceiling_step_id ,p_people_group_id => career.people_group_id ,p_soft_coding_keyflex_id => lv_soft_coding_keyflex_id ,p_group_name => lv_group_name ,p_effective_start_date => lv_effective_start_date ,p_effective_end_date => lv_effective_end_date ,p_org_now_no_manager_warning => lv_org_now_no_manager_warning ,p_other_manager_warning => lv_other_manager_warning ,p_spp_delete_warning => lv_spp_delete_warning ,p_entries_changed_warning => lv_entries_changed_warning ,p_tax_district_changed_warning => lv_tax_district_c_warning ,p_concatenated_segments => lv_concatenated_segments ,p_gsp_post_process_warning => lv_gsp_post_process_warning ); lv_debug := 'Second API done.'; -- now we can write back to say what we did UPDATE my_apps.update_gb_emp_asg SET DATA_TRANSFERRED ='Y', error_message = lv_datetrack_update_mode WHERE assignment_id=career.assignment_id and effective_start_date = career.effective_start_date ; COMMIT; exception -- with either of the APIs when others then lv_error_message := sqlerrm; -- record what went wrong so we can fix it - hopefully! UPDATE my_apps.update_gb_emp_asg SET DATA_TRANSFERRED ='F', error_message = lv_datetrack_update_mode || ': ' || substr( lv_error_message, 1, 100 ) || substr( lv_debug, 1, 80 ) WHERE assignment_id = career.assignment_id and effective_start_date = career.effective_start_date ; COMMIT; END; end loop; -- career loop end if; -- if the person has an assignment for us to update or correct end loop; -- person loop END xx_UPDATE_PER_ASSIGNMENT;