Loading Assignments into Oracle HR
The most difficult part for us was loading assignment data. As we were loading into a shared instance many of the people already had default assignments. We needed to end date these assignments if they started before a certain date (the 1st April 2007) and then set them to be correct from then on.
We created a table to hold the data called UPLOAD_GB_EMP_ASG. You can see the script here.
We then created a procedure called XX_UPDATE_PER_ASSIGNMENT. You can see the full script here but I'll take you through the key parts.
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
As you can see in the code above we start by loop through each individual in the data to be loaded. We only want to look at the records which have not yet been transferred.
-- 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.
-- 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;
We look up the PER_ALL_ASSIGNMENTS_F table to find the start date of their most recent assignment. There should only be one assignment for each person in the system, but we check just in case.
-- 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;
Check to see if they have an assignment. If not, we record the fact in the upload table and that's the end for this person.
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;
Now we check to see if this assignment record was created before the start date for our HR data. If so, then we need to use the UPDATE setting in the API to create a new record. If it was created after the start date then we can simply edit the existing record using the CORRECTION parameter setting.
-- 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
where person_id = person.person_id
and data_transfered='N'
order by 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;
The LV_CAREER_ENTRY variable is used to track the number of career changes a person has had. All career entries after the first have to be UPDATEs to the data. The career cursor simply loops through all entries in the source data table for the current person in date order to give their career changes to date.
-- 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;
-- 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;
We set the values for the various variables. The most important one is the object_version_number, as if we get this wrong the change will be rejected.
-- 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
,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
,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
);
You should note that we have set three of the p_ass_attribute parameters to match the values expected in our configuration of HR.
-- 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
);
The main reason we needed to call both APIs was that the position the assignment refers to is set in one API while the supervisor is set in the other. So every change in position that involves a change in manager needs both APIs. There are various other parameters that are set by one API but not the other.
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;
The final bit is to record the results: either it all went well or one of the APIs raised an exception. Logging the sqlerrm allows us to find out what went wrong and hopefully fix it.
I'm sure you could improve on this substantially, but if you are starting from scratch like we had to this may save you quite a bit of time.