[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select person_id
from per_periods_of_service
where period_of_service_id = p_period_of_service_id;
select null
from hr_lookups
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code;
select null
from per_contracts_f
where person_id = g_person_id
and effective_start_date > p_actual_termination_date + 1
and substrb(status,1,2) = 'A-';
Local procedure to delete and create the element entry
FR_LAST_DAY_WORKED on LWD.
This is called from Terminate and Revers.
In the case of Reverse only the delete is performed.
----------------------------------------------------------------------- */
procedure last_day_worked_entry
(p_last_day_worked date
,p_actual_termination_date date) is
--
l_assignment_id number;
select assignment_id
from per_all_assignments_f
where person_id = g_person_id
and l_effective_date
between effective_start_date and effective_end_date
and primary_flag = 'Y';
select element_type_id, element_name
from pay_element_types_f
where l_effective_date
between effective_start_date and effective_end_date
and element_name IN ('FR_LAST_DAY_WORKED', 'FR_TERMINATION_INFORMATION')
and legislation_code = 'FR';
select input_value_id
from pay_input_values_f
where l_effective_date
between effective_start_date and effective_end_date
and legislation_code = 'FR'
and element_type_id = l_element_type_id
and name = 'Proration';
select element_entry_id
from pay_element_entries_f ee
, pay_element_links_f l
where ee.assignment_id = l_assignment_id
and l.element_type_id = l_element_type_id
and l.element_link_id = ee.element_link_id
and p_actual_termination_date
between ee.effective_start_date and ee.effective_end_date
and p_actual_termination_date
between l.effective_start_date and l.effective_end_date;
hr_entry_api.delete_element_entry
('ZAP'
,p_actual_termination_date
,l_element_entry_id);
hr_entry_api.insert_element_entry
(p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_element_entry_id => l_element_entry_id
,p_assignment_id => l_assignment_id
,p_element_link_id => l_element_link_id
,p_creator_type => 'F'
,p_entry_type => 'E');
hr_entry_api.update_element_entry
(p_dt_update_mode => 'UPDATE'
,p_session_date => p_last_day_worked + 1
,p_element_entry_id => l_element_entry_id
,p_comment_id => Null
,p_input_value_id1 => l_input_value_id
,p_entry_value1 => '2'
);
hr_entry_api.update_element_entry
(p_dt_update_mode => 'UPDATE'
,p_session_date => p_actual_termination_date + 1
,p_element_entry_id => l_element_entry_id
,p_comment_id => Null
,p_input_value_id1 => l_input_value_id
,p_entry_value1 => '3'
);
l_update_mode varchar2(30);
select a.assignment_id
, a.primary_flag
, a.object_version_number
, a.soft_coding_keyflex_id
, scl.segment4
, scl.segment2
from per_all_assignments_f a
, hr_soft_coding_keyflex scl
, per_assignment_status_types ast
where a.period_of_service_id = p_period_of_service_id
and p_actual_termination_date = a.effective_end_date
and a.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status <> 'TERM_ASSIGN'
and a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id (+);
hr_assignment_api.update_emp_asg
(P_VALIDATE => FALSE
,P_EFFECTIVE_DATE => p_actual_termination_date
,P_DATETRACK_UPDATE_MODE => 'CORRECTION'
,P_ASSIGNMENT_ID => a.assignment_id
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_SEGMENT2 => a.segment2
,P_SEGMENT4 => l_leaving_reason
,P_CONCATENATED_SEGMENTS => l_concatenated_segments
,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_NO_MANAGERS_WARNING => l_no_managers_warning
,P_OTHER_MANAGER_WARNING => l_other_manager_warning);
select leaving_reason
from per_periods_of_service
where period_of_service_id = p_period_of_service_id;
External procedure called from Termination API Row Handler After Update
hook call for France.
N.B. The Termination process as called from the form will call this procedure
3 times:
1. to update HR attributes
2. to maintain ATD
3. to maintain FPD
In 1. the ATD if changing is not available
In 2. the DDF segment changes are not available (both old and new values
are the same)
In 3. no hook processing is required.
Hence it is necessary to detect programatically then termination is happening
as opposed to just an update to the HR attributes (i.e DDF, Leaving Reason).
----------------------------------------------------------------------- */
procedure termination
(p_period_of_service_id number
,p_actual_termination_date date
,p_leaving_reason varchar2
,p_pds_information8 varchar2
,p_pds_information9 varchar2
,p_pds_information10 varchar2
,p_actual_termination_date_o date
,p_leaving_reason_o varchar2
,p_pds_information8_o varchar2
,p_pds_information9_o varchar2
,p_pds_information10_o varchar2
,p_final_process_date date -- for bug#5191942
) is
--
l_proc varchar2(72) := g_package||'.termination';
l_update_mode varchar2(30);
select a.assignment_id
, a.primary_flag
, a.object_version_number
, a.soft_coding_keyflex_id
, scl.segment4
, scl.segment2
from per_all_assignments_f a
, hr_soft_coding_keyflex scl
where a.period_of_service_id = p_period_of_service_id
and p_actual_termination_date = a.effective_end_date
and a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id (+);
select contract_id
, person_id
, object_version_number
, reference
, type
, status
,effective_start_date
,effective_end_date
from per_contracts_f c
where person_id = g_person_id
and l_effective_date between effective_start_date and effective_end_date
and substrb(status,1,2) = 'A-';
l_update_mode := 'CORRECTION';
l_update_mode := 'UPDATE_CHANGE_INSERT';
l_update_mode := 'UPDATE';
hr_utility.trace(c.contract_id||' '||l_update_mode);
hr_contract_api.update_contract
(P_VALIDATE => FALSE
,P_CONTRACT_ID => c.contract_id
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_PERSON_ID => c.person_id
,P_REFERENCE => c.reference
,P_TYPE => c.type
,P_STATUS => 'T-TERMINATION'
,P_EFFECTIVE_DATE => p_actual_termination_date+1
,P_DATETRACK_MODE => l_update_mode);
hr_contract_api.update_contract
(P_VALIDATE => FALSE
,P_CONTRACT_ID => c.contract_id
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_PERSON_ID => c.person_id
,P_REFERENCE => c.reference
,P_TYPE => c.type
,P_STATUS => c.status
,P_END_REASON => l_leaving_reason
,P_EFFECTIVE_DATE => p_actual_termination_date
,P_DATETRACK_MODE => 'CORRECTION');
select a.object_version_number
, a.assignment_id
, a.soft_coding_keyflex_id
, scl.segment2
, decode(effective_end_date
,p_actual_termination_date,p_actual_termination_date
,p_actual_termination_date+1) effective_date
from per_all_assignments_f a
, hr_soft_coding_keyflex scl
where period_of_service_id = p_period_of_service_id
and (effective_end_date = p_actual_termination_date
or effective_start_date = p_actual_termination_date+1
or (p_actual_termination_date >= effective_start_date and
effective_end_date = to_date('47121231','YYYYMMDD'))
)
and a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
select contract_id
, person_id
, object_version_number
, reference
, type
, status
,effective_start_date
,effective_end_date
from per_contracts_f c
where person_id =
(select person_id
from per_periods_of_service
where period_of_service_id = p_period_of_service_id)
and p_actual_termination_date = effective_end_date;
select contract_id
, person_id
, object_version_number
, reference
, type
, status
,effective_start_date
,effective_end_date
from per_contracts_f c
where contract_id = p_contract_id
and effective_start_date = p_effective_date;
hr_assignment_api.update_emp_asg
(P_VALIDATE => FALSE
,P_EFFECTIVE_DATE => a.effective_date
,P_DATETRACK_UPDATE_MODE => 'CORRECTION'
,P_ASSIGNMENT_ID => a.assignment_id
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_SEGMENT2 => a.segment2
,P_SEGMENT4 => null
,P_CONCATENATED_SEGMENTS => l_concatenated_segments
,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_NO_MANAGERS_WARNING => l_no_managers_warning
,P_OTHER_MANAGER_WARNING => l_other_manager_warning);
hr_contract_api.update_contract
(P_VALIDATE => FALSE
,P_CONTRACT_ID => c.contract_id
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_PERSON_ID => c.person_id
,P_REFERENCE => c.reference
,P_TYPE => c.type
,P_STATUS => l_status
,P_END_REASON => null
,P_EFFECTIVE_DATE => l_effective_date
,P_DATETRACK_MODE => 'CORRECTION');
hr_contract_api.update_contract
(P_VALIDATE => FALSE
,P_CONTRACT_ID => c.contract_id
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_PERSON_ID => c.person_id
,P_REFERENCE => c.reference
,P_TYPE => c.type
,P_STATUS => c.status
,P_END_REASON => null
,P_EFFECTIVE_DATE => p_actual_termination_date
,P_DATETRACK_MODE => 'CORRECTION');
hr_contract_api.update_contract
(P_VALIDATE => FALSE
,P_CONTRACT_ID => ctr2.contract_id
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_PERSON_ID => ctr2.person_id
,P_REFERENCE => ctr2.reference
,P_TYPE => ctr2.type
,P_STATUS => c.status
,P_END_REASON => null
,P_EFFECTIVE_DATE => p_actual_termination_date+1
,P_DATETRACK_MODE => 'CORRECTION');