[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select person_id
,business_group_id
,date_start
,actual_termination_date
,leaving_reason
,adjusted_svc_date
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,final_process_date
,period_of_service_id
from per_periods_of_service
where period_of_service_id = p_period_of_service_id;
select
role_id
, object_version_number
, end_date
from per_roles
where person_id = p_person_id
and p_actual_termination_date
between start_date
and nvl(end_date, hr_api.g_eot);
select role_id
from per_roles
where person_id = p_person_id
and EMP_RIGHTS_FLAG = 'Y'
and nvl(end_of_rights_date, hr_api.g_eot) > p_actual_termination_date;
select *
from per_periods_of_service
where period_of_service_id = p_period_of_service_id;
SELECT assignment_id
, assignment_status_type_id
, business_group_id
, effective_start_date
, effective_end_date
, payroll_id
, object_version_number
FROM per_assignments_f asg
WHERE asg.person_id = cp_person_id
AND asg.period_of_service_id = cp_period_of_service_id
AND cp_old_fpd BETWEEN asg.effective_start_date
AND asg.effective_end_date;
select max(asg.effective_end_date)
from per_all_assignments_f asg
where asg.period_of_service_id = p_period_of_service_id
and asg.effective_start_date > p_fpd_date1;
** then don't call either termination API and just update the details.
*/
open l_pds_cur;
hr_periods_of_service_api.update_pds_details
(p_effective_date => p_effective_date
,p_period_of_service_id => p_period_of_service_id
,p_termination_accepted_person => p_termination_accepted_person
,p_accepted_termination_date => p_accepted_termination_date
--
-- 120.2 (START)
--
,p_actual_termination_date => p_actual_termination_date
,p_last_standard_process_date => p_last_standard_process_date
,p_final_process_date => p_final_process_date
--
-- 120.2 (END)
--
,p_object_version_number => p_object_version_number
,p_comments => p_comments
,p_leaving_reason => p_leaving_reason
,p_notified_termination_date => p_notified_termination_date
,p_projected_termination_date => p_projected_termination_date
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_pds_information_category => p_pds_information_category
,p_pds_information1 => p_pds_information1
,p_pds_information2 => p_pds_information2
,p_pds_information3 => p_pds_information3
,p_pds_information4 => p_pds_information4
,p_pds_information5 => p_pds_information5
,p_pds_information6 => p_pds_information6
,p_pds_information7 => p_pds_information7
,p_pds_information8 => p_pds_information8
,p_pds_information9 => p_pds_information9
,p_pds_information10 => p_pds_information10
,p_pds_information11 => p_pds_information11
,p_pds_information12 => p_pds_information12
,p_pds_information13 => p_pds_information13
,p_pds_information14 => p_pds_information14
,p_pds_information15 => p_pds_information15
,p_pds_information16 => p_pds_information16
,p_pds_information17 => p_pds_information17
,p_pds_information18 => p_pds_information18
,p_pds_information19 => p_pds_information19
,p_pds_information20 => p_pds_information20
,p_pds_information21 => p_pds_information21
,p_pds_information22 => p_pds_information22
,p_pds_information23 => p_pds_information23
,p_pds_information24 => p_pds_information24
,p_pds_information25 => p_pds_information25
,p_pds_information26 => p_pds_information26
,p_pds_information27 => p_pds_information27
,p_pds_information28 => p_pds_information28
,p_pds_information29 => p_pds_information29
,p_pds_information30 => p_pds_information30
--
-- 120.2 (START)
--
,p_org_now_no_manager_warning => p_org_now_no_manager_warning
,p_asg_future_changes_warning => p_asg_future_changes_warning
,p_entries_changed_warning => p_entries_changed_warning
--
-- 120.2 (END)
--
);
pay_pog_all_assignments_pkg.after_delete
(p_effective_date => p_effective_date
,p_datetrack_mode => 'DELETE_NEXT_CHANGE'
,p_validation_start_date => lr_asg_rec.effective_start_date
,p_validation_end_date => lr_asg_rec.effective_end_date
,p_assignment_id => lr_asg_rec.assignment_id
,p_effective_end_date => hr_api.g_eot
,p_effective_start_date => lr_asg_rec.effective_start_date
,p_object_version_number => lr_asg_rec.object_version_number
,p_org_now_no_manager_warning => null
,p_applicant_rank_o => null
,p_application_id_o => null
,p_assignment_category_o => null
,p_assignment_number_o => null
,p_assignment_sequence_o => null
,p_assignment_status_type_id_o => null
,p_assignment_type_o => null
,p_ass_attribute1_o => null
,p_ass_attribute10_o => null
,p_ass_attribute11_o => null
,p_ass_attribute12_o => null
,p_ass_attribute13_o => null
,p_ass_attribute14_o => null
,p_ass_attribute15_o => null
,p_ass_attribute16_o => null
,p_ass_attribute17_o => null
,p_ass_attribute18_o => null
,p_ass_attribute19_o => null
,p_ass_attribute2_o => null
,p_ass_attribute20_o => null
,p_ass_attribute21_o => null
,p_ass_attribute22_o => null
,p_ass_attribute23_o => null
,p_ass_attribute24_o => null
,p_ass_attribute25_o => null
,p_ass_attribute26_o => null
,p_ass_attribute27_o => null
,p_ass_attribute28_o => null
,p_ass_attribute29_o => null
,p_ass_attribute3_o => null
,p_ass_attribute30_o => null
,p_ass_attribute4_o => null
,p_ass_attribute5_o => null
,p_ass_attribute6_o => null
,p_ass_attribute7_o => null
,p_ass_attribute8_o => null
,p_ass_attribute9_o => null
,p_ass_attribute_category_o => null
,p_bargaining_unit_code_o => null
,p_business_group_id_o => lr_asg_rec.business_group_id
,p_cagr_grade_def_id_o => null
,p_cagr_id_flex_num_o => null
,p_change_reason_o => null
,p_collective_agreement_id_o => null
,p_comment_id_o => null
,p_contract_id_o => null
,p_date_probation_end_o => null
,p_default_code_comb_id_o => null
,p_effective_end_date_o => l_pds_rec.final_process_date
,p_effective_start_date_o => lr_asg_rec.effective_start_date
,p_employee_category_o => null
,p_employment_category_o => null
,p_establishment_id_o => null
,p_frequency_o => null
,p_grade_id_o => null
,p_hourly_salaried_code_o => null
,p_internal_address_line_o => null
,p_job_id_o => null
,p_job_post_source_name_o => null
,p_labour_union_member_flag_o => null
,p_location_id_o => null
,p_manager_flag_o => null
,p_normal_hours_o => null
,p_notice_period_o => null
,p_notice_period_uom_o => null
,p_object_version_number_o => null
,p_organization_id_o => null
,p_payroll_id_o => lr_asg_rec.payroll_id
,p_pay_basis_id_o => null
,p_people_group_id_o => null
,p_perf_review_period_o => null
,p_perf_review_period_frequen_o => null
,p_period_of_service_id_o => null
,p_person_id_o => null
,p_person_referred_by_id_o => null
,p_placement_date_start_o => null
,p_position_id_o => null
,p_posting_content_id_o => null
,p_primary_flag_o => null
,p_probation_period_o => null
,p_probation_unit_o => null
,p_program_application_id_o => null
,p_program_id_o => null
,p_program_update_date_o => null
,p_project_title_o => null
,p_recruiter_id_o => null
,p_recruitment_activity_id_o => null
,p_request_id_o => null
,p_sal_review_period_o => null
,p_sal_review_period_frequen_o => null
,p_set_of_books_id_o => null
,p_soft_coding_keyflex_id_o => null
,p_source_organization_id_o => null
,p_source_type_o => null
,p_special_ceiling_step_id_o => null
,p_supervisor_id_o => null
,p_time_normal_finish_o => null
,p_time_normal_start_o => null
,p_title_o => null
,p_vacancy_id_o => null
,p_vendor_assignment_number_o => null
,p_vendor_employee_number_o => null
,p_vendor_id_o => null
,p_work_at_home_o => null
,p_grade_ladder_pgm_id_o => null
,p_supervisor_assignment_id_o => null
,p_vendor_site_id_o => null
,p_po_header_id_o => null
,p_po_line_id_o => null
,p_projected_assignment_end_o => null
);
pay_pog_all_assignments_pkg.after_delete
(p_effective_date => p_effective_date
,p_datetrack_mode => 'DELETE_NEXT_CHANGE'
,p_validation_start_date => lr_asg_rec.effective_start_date
,p_validation_end_date => lr_asg_rec.effective_end_date
,p_assignment_id => lr_asg_rec.assignment_id
,p_effective_end_date => lr_asg_rec.effective_end_date
,p_effective_start_date => lr_asg_rec.effective_start_date
,p_object_version_number => lr_asg_rec.object_version_number
,p_org_now_no_manager_warning => null
,p_applicant_rank_o => null
,p_application_id_o => null
,p_assignment_category_o => null
,p_assignment_number_o => null
,p_assignment_sequence_o => null
,p_assignment_status_type_id_o => null
,p_assignment_type_o => null
,p_ass_attribute1_o => null
,p_ass_attribute10_o => null
,p_ass_attribute11_o => null
,p_ass_attribute12_o => null
,p_ass_attribute13_o => null
,p_ass_attribute14_o => null
,p_ass_attribute15_o => null
,p_ass_attribute16_o => null
,p_ass_attribute17_o => null
,p_ass_attribute18_o => null
,p_ass_attribute19_o => null
,p_ass_attribute2_o => null
,p_ass_attribute20_o => null
,p_ass_attribute21_o => null
,p_ass_attribute22_o => null
,p_ass_attribute23_o => null
,p_ass_attribute24_o => null
,p_ass_attribute25_o => null
,p_ass_attribute26_o => null
,p_ass_attribute27_o => null
,p_ass_attribute28_o => null
,p_ass_attribute29_o => null
,p_ass_attribute3_o => null
,p_ass_attribute30_o => null
,p_ass_attribute4_o => null
,p_ass_attribute5_o => null
,p_ass_attribute6_o => null
,p_ass_attribute7_o => null
,p_ass_attribute8_o => null
,p_ass_attribute9_o => null
,p_ass_attribute_category_o => null
,p_bargaining_unit_code_o => null
,p_business_group_id_o => lr_asg_rec.business_group_id
,p_cagr_grade_def_id_o => null
,p_cagr_id_flex_num_o => null
,p_change_reason_o => null
,p_collective_agreement_id_o => null
,p_comment_id_o => null
,p_contract_id_o => null
,p_date_probation_end_o => null
,p_default_code_comb_id_o => null
,p_effective_end_date_o => l_pds_rec.final_process_date
,p_effective_start_date_o => lr_asg_rec.effective_start_date
,p_employee_category_o => null
,p_employment_category_o => null
,p_establishment_id_o => null
,p_frequency_o => null
,p_grade_id_o => null
,p_hourly_salaried_code_o => null
,p_internal_address_line_o => null
,p_job_id_o => null
,p_job_post_source_name_o => null
,p_labour_union_member_flag_o => null
,p_location_id_o => null
,p_manager_flag_o => null
,p_normal_hours_o => null
,p_notice_period_o => null
,p_notice_period_uom_o => null
,p_object_version_number_o => null
,p_organization_id_o => null
,p_payroll_id_o => lr_asg_rec.payroll_id
,p_pay_basis_id_o => null
,p_people_group_id_o => null
,p_perf_review_period_o => null
,p_perf_review_period_frequen_o => null
,p_period_of_service_id_o => null
,p_person_id_o => null
,p_person_referred_by_id_o => null
,p_placement_date_start_o => null
,p_position_id_o => null
,p_posting_content_id_o => null
,p_primary_flag_o => null
,p_probation_period_o => null
,p_probation_unit_o => null
,p_program_application_id_o => null
,p_program_id_o => null
,p_program_update_date_o => null
,p_project_title_o => null
,p_recruiter_id_o => null
,p_recruitment_activity_id_o => null
,p_request_id_o => null
,p_sal_review_period_o => null
,p_sal_review_period_frequen_o => null
,p_set_of_books_id_o => null
,p_soft_coding_keyflex_id_o => null
,p_source_organization_id_o => null
,p_source_type_o => null
,p_special_ceiling_step_id_o => null
,p_supervisor_id_o => null
,p_time_normal_finish_o => null
,p_time_normal_start_o => null
,p_title_o => null
,p_vacancy_id_o => null
,p_vendor_assignment_number_o => null
,p_vendor_employee_number_o => null
,p_vendor_id_o => null
,p_work_at_home_o => null
,p_grade_ladder_pgm_id_o => null
,p_supervisor_assignment_id_o => null
,p_vendor_site_id_o => null
,p_po_header_id_o => null
,p_po_line_id_o => null
,p_projected_assignment_end_o => null
);
per_supplementary_role_api.update_supplementary_role(
p_effective_date => p_effective_date
,p_role_id => roles_rec.role_id
,p_object_version_number => roles_rec.object_version_number
,p_end_date => p_actual_termination_date
,p_old_end_date => roles_rec.end_date
);
select SHARED_TYPE_CODE from
per_shared_types
where system_type_cd = p_LEAVING_REASON
and lookup_type='LEAV_REAS';
select SHARED_TYPE_CODE from
per_shared_types
where system_type_cd = p_LEAVING_REASON
and lookup_type='LEAV_REAS'
and business_group_id=p_business_group_id;