[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_scl_from_ddf
(errbuf OUT NOCOPY VARCHAR2
,retcode OUT NOCOPY VARCHAR2
,p_business_group_id IN NUMBER) IS
l_acco_provided VARCHAR2(30);
l_ov_update NUMBER;
SELECT org_information2 def_emp
,NVL(org_information3,'N') mig_indicator
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_business_group_id
AND hoi.org_information_context = 'AE_BG_DETAILS';
SELECT business_group_id
FROM per_business_groups
WHERE legislation_code = 'AE';
SELECT person_id
,per_information14 acco_provided
,per_information15 trans_provided
,effective_start_date
,effective_end_date
FROM per_all_people_f ppf
,per_person_types ppt
WHERE ppf.business_group_id = l_business_group_id
AND ppt.person_type_id = ppf.person_type_id
AND ppt.business_group_id = ppf.business_group_id
AND ppt.system_person_type LIKE 'EMP%'
ORDER BY person_id, effective_start_date, effective_end_date;
/*Cursor for fetching the data for assignments of each employee selected by the above cursor.
This cursor will be used for selecting the asignments for updating the scl segments. */
CURSOR csr_get_assignment_det (p_start_date DATE, p_end_date DATE, p_person_id NUMBER) IS
SELECT assignment_id
,paa.object_version_number
,paa.title
,hsc.segment1
,hsc.segment2
,hsc.segment3
,hsc.segment4
,hsc.segment5
,paa.effective_start_date
,paa.effective_end_date
,0 indicator
FROM per_all_assignments_f paa
,hr_soft_coding_keyflex hsc
WHERE person_id = p_person_id
AND paa.primary_flag = 'Y'
AND hsc.soft_coding_keyflex_id(+) = paa.soft_coding_keyflex_id
AND ((paa.effective_start_date BETWEEN p_start_date AND p_end_date ))
UNION
SELECT assignment_id
,paa.object_version_number
,paa.title
,hsc.segment1
,hsc.segment2
,hsc.segment3
,hsc.segment4
,hsc.segment5
,paa.effective_start_date
,paa.effective_end_date
,1 indicator
FROM per_all_assignments_f paa
,hr_soft_coding_keyflex hsc
WHERE person_id = p_person_id
AND paa.primary_flag = 'Y'
AND hsc.soft_coding_keyflex_id(+) = paa.soft_coding_keyflex_id
AND ((paa.effective_start_date < p_start_date AND paa.effective_end_date >= p_end_date))
ORDER BY effective_start_date;
/*All assignments are updated with the details of the current person before the next person is fetched*/
------------------------------------------
IF tab_rec_person.COUNT > 0 THEN
j := tab_rec_person.COUNT;
l_ov_update := 0;
/*If the start date matches, update the asignment in 'CORRECTION' mode*/
IF rec_get_assignment_det.effective_start_date = tab_rec_person(k-1).effective_start_date THEN
l_datetrack_mode := 'CORRECTION';
/*If start date do not match, check if any assignment has already been updated for the person within the same period
If there has been an UPDATe, then the next record should be updated in 'COREECTION mode*/
IF l_ov_update = 1 THEN
l_datetrack_mode := 'CORRECTION';
/* If there exists future rows in assignment that are date tracked, UPDATE_CHANGE_INSERT mode should be used*/
IF (rec_get_assignment_det.effective_end_date < TO_DATE('31-12-4712','DD-MM-YYYY')
OR rec_get_assignment_det.effective_start_date > tab_rec_person(k-1).effective_start_date) THEN
l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
l_datetrack_mode := 'UPDATE';
hr_assignment_api.update_emp_asg
(p_validate => FALSE
,p_effective_date => l_eff_date --tab_rec_person(k-1).effective_start_date
,p_datetrack_update_mode => l_datetrack_mode
,p_assignment_id => l_assignment_id
,p_object_version_number => l_object_version_number
,p_title => 'AE_STATUTORY_INFO' --l_title
,p_segment1 => l_segment1
,p_segment2 => l_segment2
,p_segment3 => l_segment3
,p_segment4 => l_segment4
,p_segment5 => l_segment5
,p_segment7 => tab_rec_person(k-1).per_information14 --l_acco_provided
,p_segment8 => tab_rec_person(k-1).per_information15 --l_trans_provided
,p_comment_id => l_comment_id
,p_soft_coding_keyflex_id => l_soft_key_flex_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_manager_warning
,p_other_manager_warning => l_other_manager_warning);
/*If a record was updated with UPDATE_CHANGE_INSERT mode, the next
assignments within the same person ddf periods should be updated in CORRECTION mode*/
IF l_datetrack_mode = 'UPDATE_CHANGE_INSERT' THEN
l_comment_id := NULL;
hr_assignment_api.update_emp_asg
(p_validate => FALSE
,p_effective_date => rec_get_assignment_det.effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => l_assignment_id
,p_object_version_number => l_object_version_number
,p_title => 'AE_STATUTORY_INFO' --l_title
,p_segment1 => l_segment1
,p_segment2 => l_segment2
,p_segment3 => l_segment3
,p_segment4 => l_segment4
,p_segment5 => l_segment5
,p_segment7 => tab_rec_person(k-1).per_information14 --l_acco_provided
,p_segment8 => tab_rec_person(k-1).per_information15 --l_trans_provided
,p_comment_id => l_comment_id
,p_soft_coding_keyflex_id => l_soft_key_flex_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_manager_warning
,p_other_manager_warning => l_other_manager_warning);
l_ov_update := 1;
/*Once the assignments of the previous person have been updated continue with the next person*/
l_same_person := 0;
tab_rec_person.DELETE;
l_ov_update := 0;
IF l_ov_update = 1 THEN
l_datetrack_mode := 'CORRECTION';
l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
l_datetrack_mode := 'UPDATE';
hr_assignment_api.update_emp_asg
(p_validate => FALSE
,p_effective_date => l_eff_date --tab_rec_person(k-1).effective_start_date
,p_datetrack_update_mode => l_datetrack_mode
,p_assignment_id => l_assignment_id
,p_object_version_number => l_object_version_number
,p_title => 'AE_STATUTORY_INFO' --l_title
,p_segment1 => l_segment1
,p_segment2 => l_segment2
,p_segment3 => l_segment3
,p_segment4 => l_segment4
,p_segment5 => l_segment5
,p_segment7 => tab_rec_person(k-1).per_information14 --l_acco_provided
,p_segment8 => tab_rec_person(k-1).per_information15 --l_trans_provided
,p_comment_id => l_comment_id
,p_soft_coding_keyflex_id => l_soft_key_flex_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_manager_warning
,p_other_manager_warning => l_other_manager_warning);
IF l_datetrack_mode = 'UPDATE_CHANGE_INSERT' THEN
l_comment_id := NULL;
hr_assignment_api.update_emp_asg
(p_validate => FALSE
,p_effective_date => rec_get_assignment_det.effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => l_assignment_id
,p_object_version_number => l_object_version_number
,p_title => 'AE_STATUTORY_INFO' --l_title
,p_segment1 => l_segment1
,p_segment2 => l_segment2
,p_segment3 => l_segment3
,p_segment4 => l_segment4
,p_segment5 => l_segment5
,p_segment7 => tab_rec_person(k-1).per_information14 --l_acco_provided
,p_segment8 => tab_rec_person(k-1).per_information15 --l_trans_provided
,p_comment_id => l_comment_id
,p_soft_coding_keyflex_id => l_soft_key_flex_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_manager_warning
,p_other_manager_warning => l_other_manager_warning);
l_ov_update := 1;
/*Update the org_information to indicate the migration is complete*/
UPDATE hr_organization_information
SET org_information3 = 'Y'
WHERE organization_id = p_business_group_id
AND org_information_context = 'AE_BG_DETAILS';
END update_scl_from_ddf;