[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure delete_per_pay_proposals(p_period_of_service_id number
,p_actual_termination_date date) is
--
cursor ass1 is select pa.assignment_id
from per_assignments pa
where pa.period_of_service_id = p_period_of_service_id;
select pp.pay_proposal_id
from per_pay_proposals pp
where pp.assignment_id = p_assignment_id_in
and pp.change_date > p_actual_termination_date;
delete from per_pay_proposal_components ppc
where ppc.pay_proposal_id = p_pay_proposal_id;
delete from per_pay_proposals
where assignment_id = p_assignment_id
and change_date > p_actual_termination_date;
end delete_per_pay_proposals;
select trunc(months_between(least(nvl(ACTUAL_TERMINATION_DATE + 1, p_session_date + 1),
p_session_date+ 1), DATE_START) / 12, 0) tp_years,
trunc(mod(months_between(least(nvl(ACTUAL_TERMINATION_DATE + 1, p_session_date + 1),
p_session_date + 1), DATE_START), 12) ,0) tp_months
from PER_PERIODS_OF_SERVICE
where DATE_START <= p_session_date
and PERIOD_OF_SERVICE_ID = p_period_of_service_id;
select trunc(sum(months_between(least(nvl(ACTUAL_TERMINATION_DATE + 1, p_session_date + 1),
p_session_date + 1), DATE_START)) / 12 ,0) total_years,
trunc(mod(sum(months_between(least(nvl(ACTUAL_TERMINATION_DATE + 1, p_session_date + 1),
p_session_date + 1), DATE_START)) , 12),0) total_months
from PER_PERIODS_OF_SERVICE
where PERSON_ID = p_person_id
and business_group_id + 0 = p_business_group_id
and DATE_START <= p_session_date
and PERIOD_OF_SERVICE_ID <= p_period_of_service_id;
select count(distinct(payroll_id))
into p_no_payrolls
from per_assignments pa
where pa.period_of_service_id = p_period_of_service_id
and pa.person_id = p_person_id;
select max(end_date),max(end_date)
into p_final_process_date
, p_last_standard_process_date
from per_time_periods
where p_actual_termination_date between
start_date and end_date
and payroll_id in (select payroll_id
from per_assignments pa
where pa.period_of_service_id = p_period_of_service_id
and pa.person_id = p_person_id);
procedure delete_row(p_row_id VARCHAR2) is
--
begin
--
delete from per_periods_of_service
where rowid=chartorowid(p_row_id);
procedure insert_row(p_row_id in out nocopy VARCHAR2
,p_period_of_service_id in out nocopy NUMBER
,p_business_group_id NUMBER
,p_person_id NUMBER
,p_date_start DATE
,p_termination_accepted_per_id NUMBER
,p_accepted_termination_date DATE
,p_actual_termination_date DATE
,p_comments VARCHAR2
,p_final_process_date DATE
,p_last_standard_process_date DATE
,p_leaving_reason VARCHAR2
,p_notified_termination_date DATE
,p_projected_termination_date DATE
,p_request_id NUMBER
,p_program_application_id NUMBER
,p_program_id NUMBER
,p_program_update_date DATE
,p_attribute_category VARCHAR2
,p_attribute1 VARCHAR2
,p_attribute2 VARCHAR2
,p_attribute3 VARCHAR2
,p_attribute4 VARCHAR2
,p_attribute5 VARCHAR2
,p_attribute6 VARCHAR2
,p_attribute7 VARCHAR2
,p_attribute8 VARCHAR2
,p_attribute9 VARCHAR2
,p_attribute10 VARCHAR2
,p_attribute11 VARCHAR2
,p_attribute12 VARCHAR2
,p_attribute13 VARCHAR2
,p_attribute14 VARCHAR2
,p_attribute15 VARCHAR2
,p_attribute16 VARCHAR2
,p_attribute17 VARCHAR2
,p_attribute18 VARCHAR2
,p_attribute19 VARCHAR2
,p_attribute20 VARCHAR2
,p_adjusted_svc_date DATE
) is
l_period_of_service_id NUMBER(15);
select per_periods_of_service_s.nextval
into l_period_of_service_id
from sys.dual;
insert into per_periods_of_service (
period_of_service_id
,business_group_id
,person_id
,date_start
,termination_accepted_person_id
,accepted_termination_date
,actual_termination_date
,comments
,final_process_date
,last_standard_process_date
,leaving_reason
,notified_termination_date
,projected_termination_date
,request_id
,program_application_id
,program_id
,program_update_date
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,adjusted_svc_date
)
values(
l_period_of_service_id
,p_business_group_id
,p_person_id
,p_date_start
,p_termination_accepted_per_id
,p_accepted_termination_date
,p_actual_termination_date
,p_comments
,p_final_process_date
,p_last_standard_process_date
,p_leaving_reason
,p_notified_termination_date
,p_projected_termination_date
,p_request_id
,p_program_application_id
,p_program_id
,p_program_update_date
,p_attribute_category
,p_attribute1
,p_attribute2
,p_attribute3
,p_attribute4
,p_attribute5
,p_attribute6
,p_attribute7
,p_attribute8
,p_attribute9
,p_attribute10
,p_attribute11
,p_attribute12
,p_attribute13
,p_attribute14
,p_attribute15
,p_attribute16
,p_attribute17
,p_attribute18
,p_attribute19
,p_attribute20
,p_adjusted_svc_date
);
,p_event => 'INSERTING'
,p_effective_date => p_date_start);
,p_program_update_date DATE
,p_attribute_category VARCHAR2
,p_attribute1 VARCHAR2
,p_attribute2 VARCHAR2
,p_attribute3 VARCHAR2
,p_attribute4 VARCHAR2
,p_attribute5 VARCHAR2
,p_attribute6 VARCHAR2
,p_attribute7 VARCHAR2
,p_attribute8 VARCHAR2
,p_attribute9 VARCHAR2
,p_attribute10 VARCHAR2
,p_attribute11 VARCHAR2
,p_attribute12 VARCHAR2
,p_attribute13 VARCHAR2
,p_attribute14 VARCHAR2
,p_attribute15 VARCHAR2
,p_attribute16 VARCHAR2
,p_attribute17 VARCHAR2
,p_attribute18 VARCHAR2
,p_attribute19 VARCHAR2
,p_attribute20 VARCHAR2
,p_pds_information_category VARCHAR2
,p_pds_information1 VARCHAR2
,p_pds_information2 VARCHAR2
,p_pds_information3 VARCHAR2
,p_pds_information4 VARCHAR2
,p_pds_information5 VARCHAR2
,p_pds_information6 VARCHAR2
,p_pds_information7 VARCHAR2
,p_pds_information8 VARCHAR2
,p_pds_information9 VARCHAR2
,p_pds_information10 VARCHAR2
,p_pds_information11 VARCHAR2
,p_pds_information12 VARCHAR2
,p_pds_information13 VARCHAR2
,p_pds_information14 VARCHAR2
,p_pds_information15 VARCHAR2
,p_pds_information16 VARCHAR2
,p_pds_information17 VARCHAR2
,p_pds_information18 VARCHAR2
,p_pds_information19 VARCHAR2
,p_pds_information20 VARCHAR2
,p_pds_information21 VARCHAR2
,p_pds_information22 VARCHAR2
,p_pds_information23 VARCHAR2
,p_pds_information24 VARCHAR2
,p_pds_information25 VARCHAR2
,p_pds_information26 VARCHAR2
,p_pds_information27 VARCHAR2
,p_pds_information28 VARCHAR2
,p_pds_information29 VARCHAR2
,p_pds_information30 VARCHAR2
,p_adjusted_svc_date DATE
) is
cursor pps is select *
from per_periods_of_service
where rowid = chartorowid(p_row_id)
for update nowait;
and (( p_program_update_date = pps_rec.program_update_date)
or (pps_rec.program_update_date is null
and (p_program_update_date is null)))
and (( p_attribute_category = pps_rec.attribute_category)
or (pps_rec.attribute_category is null
and (p_attribute_category is null)))
and (( p_attribute1 = pps_rec.attribute1)
or (pps_rec.attribute1 is null
and (p_attribute1 is null)))
and (( p_attribute2 = pps_rec.attribute2)
or (pps_rec.attribute2 is null
and (p_attribute2 is null)))
and (( p_attribute3 = pps_rec.attribute3)
or (pps_rec.attribute3 is null
and (p_attribute3 is null)))
and (( p_attribute4 = pps_rec.attribute4)
or (pps_rec.attribute4 is null
and (p_attribute4 is null)))
and (( p_attribute5 = pps_rec.attribute5)
or (pps_rec.attribute5 is null
and (p_attribute5 is null)))
and (( p_attribute6 = pps_rec.attribute6)
or (pps_rec.attribute6 is null
and (p_attribute6 is null)))
and (( p_attribute7 = pps_rec.attribute7)
or (pps_rec.attribute7 is null
and (p_attribute7 is null)))
and (( p_attribute8 = pps_rec.attribute8)
or (pps_rec.attribute8 is null
and (p_attribute8 is null)))
and (( p_attribute9 = pps_rec.attribute9)
or (pps_rec.attribute9 is null
and (p_attribute9 is null)))
and (( p_attribute10 = pps_rec.attribute10)
or (pps_rec.attribute10 is null
and (p_attribute10 is null)))
and (( p_attribute11 = pps_rec.attribute11)
or (pps_rec.attribute11 is null
and (p_attribute11 is null)))
and (( p_attribute12 = pps_rec.attribute12)
or (pps_rec.attribute12 is null
and (p_attribute12 is null)))
and (( p_attribute13 = pps_rec.attribute13)
or (pps_rec.attribute13 is null
and (p_attribute13 is null)))
and (( p_attribute14 = pps_rec.attribute14)
or (pps_rec.attribute14 is null
and (p_attribute14 is null)))
and (( p_attribute15 = pps_rec.attribute15)
or (pps_rec.attribute15 is null
and (p_attribute15 is null)))
and (( p_attribute16 = pps_rec.attribute16)
or (pps_rec.attribute16 is null
and (p_attribute16 is null)))
and (( p_attribute17 = pps_rec.attribute17)
or (pps_rec.attribute17 is null
and (p_attribute17 is null)))
and (( p_attribute18 = pps_rec.attribute18)
or (pps_rec.attribute18 is null
and (p_attribute18 is null)))
and (( p_attribute19 = pps_rec.attribute19)
or (pps_rec.attribute19 is null
and (p_attribute19 is null)))
and (( p_attribute20 = pps_rec.attribute20)
or (pps_rec.attribute20 is null
and (p_attribute20 is null)))
and (( p_pds_information_category = pps_rec.pds_information_category)
or (pps_rec.pds_information_category is null
and (p_pds_information_category is null)))
and (( p_pds_information1 = pps_rec.pds_information1)
or (pps_rec.pds_information1 is null
and (p_pds_information1 is null)))
and (( p_pds_information2 = pps_rec.pds_information2)
or (pps_rec.pds_information2 is null
and (p_pds_information2 is null)))
and (( p_pds_information3 = pps_rec.pds_information3)
or (pps_rec.pds_information3 is null
and (p_pds_information3 is null)))
and (( p_pds_information4 = pps_rec.pds_information4)
or (pps_rec.pds_information4 is null
and (p_pds_information4 is null)))
and (( p_pds_information5 = pps_rec.pds_information5)
or (pps_rec.pds_information5 is null
and (p_pds_information5 is null)))
and (( p_pds_information6 = pps_rec.pds_information6)
or (pps_rec.pds_information6 is null
and (p_pds_information6 is null)))
and (( p_pds_information7 = pps_rec.pds_information7)
or (pps_rec.pds_information7 is null
and (p_pds_information7 is null)))
and (( p_pds_information8 = pps_rec.pds_information8)
or (pps_rec.pds_information8 is null
and (p_pds_information8 is null)))
and (( p_pds_information9 = pps_rec.pds_information9)
or (pps_rec.pds_information9 is null
and (p_pds_information9 is null)))
and (( p_pds_information10 = pps_rec.pds_information10)
or (pps_rec.pds_information10 is null
and (p_pds_information10 is null)))
and (( p_pds_information11 = pps_rec.pds_information11)
or (pps_rec.pds_information11 is null
and (p_pds_information11 is null)))
and (( p_pds_information12 = pps_rec.pds_information12)
or (pps_rec.pds_information12 is null
and (p_pds_information12 is null)))
and (( p_pds_information13 = pps_rec.pds_information13)
or (pps_rec.pds_information13 is null
and (p_pds_information13 is null)))
and (( p_pds_information14 = pps_rec.pds_information14)
or (pps_rec.pds_information14 is null
and (p_pds_information14 is null)))
and (( p_pds_information15 = pps_rec.pds_information15)
or (pps_rec.pds_information15 is null
and (p_pds_information15 is null)))
and (( p_pds_information16 = pps_rec.pds_information16)
or (pps_rec.pds_information16 is null
and (p_pds_information16 is null)))
and (( p_pds_information17 = pps_rec.pds_information17)
or (pps_rec.pds_information17 is null
and (p_pds_information17 is null)))
and (( p_pds_information18 = pps_rec.pds_information18)
or (pps_rec.pds_information18 is null
and (p_pds_information18 is null)))
and (( p_pds_information19 = pps_rec.pds_information19)
or (pps_rec.pds_information19 is null
and (p_pds_information19 is null)))
and (( p_pds_information20 = pps_rec.pds_information20)
or (pps_rec.pds_information20 is null
and (p_pds_information20 is null)))
and (( p_pds_information21 = pps_rec.pds_information21)
or (pps_rec.pds_information21 is null
and (p_pds_information21 is null)))
and (( p_pds_information22 = pps_rec.pds_information22)
or (pps_rec.pds_information22 is null
and (p_pds_information22 is null)))
and (( p_pds_information23 = pps_rec.pds_information23)
or (pps_rec.pds_information23 is null
and (p_pds_information23 is null)))
and (( p_pds_information24 = pps_rec.pds_information24)
or (pps_rec.pds_information24 is null
and (p_pds_information24 is null)))
and (( p_pds_information25 = pps_rec.pds_information25)
or (pps_rec.pds_information25 is null
and (p_pds_information25 is null)))
and (( p_pds_information26 = pps_rec.pds_information26)
or (pps_rec.pds_information26 is null
and (p_pds_information26 is null)))
and (( p_pds_information27 = pps_rec.pds_information27)
or (pps_rec.pds_information27 is null
and (p_pds_information27 is null)))
and (( p_pds_information28 = pps_rec.pds_information28)
or (pps_rec.pds_information28 is null
and (p_pds_information28 is null)))
and (( p_pds_information29 = pps_rec.pds_information29)
or (pps_rec.pds_information29 is null
and (p_pds_information29 is null)))
and (( p_pds_information30 = pps_rec.pds_information30)
or (pps_rec.pds_information30 is null
and (p_pds_information30 is null)))
and ((p_adjusted_svc_date = pps_rec.adjusted_svc_date)
or (pps_rec.adjusted_svc_date is null
and (p_adjusted_svc_date is null)))
) then
return;
procedure update_term_row(p_row_id VARCHAR2
,p_period_of_service_id NUMBER
,p_business_group_id NUMBER
,p_person_id NUMBER
,p_date_start DATE
,p_termination_accepted_per_id NUMBER
,p_accepted_termination_date DATE
,p_actual_termination_date DATE
,p_comments VARCHAR2
,p_final_process_date DATE
,p_last_standard_process_date DATE
,p_leaving_reason VARCHAR2
,p_notified_termination_date DATE
,p_projected_termination_date DATE
,p_request_id NUMBER
,p_program_application_id NUMBER
,p_program_id NUMBER
,p_program_update_date DATE
,p_attribute_category VARCHAR2
,p_attribute1 VARCHAR2
,p_attribute2 VARCHAR2
,p_attribute3 VARCHAR2
,p_attribute4 VARCHAR2
,p_attribute5 VARCHAR2
,p_attribute6 VARCHAR2
,p_attribute7 VARCHAR2
,p_attribute8 VARCHAR2
,p_attribute9 VARCHAR2
,p_attribute10 VARCHAR2
,p_attribute11 VARCHAR2
,p_attribute12 VARCHAR2
,p_attribute13 VARCHAR2
,p_attribute14 VARCHAR2
,p_attribute15 VARCHAR2
,p_attribute16 VARCHAR2
,p_attribute17 VARCHAR2
,p_attribute18 VARCHAR2
,p_attribute19 VARCHAR2
,p_attribute20 VARCHAR2
,p_initiate_cancellation VARCHAR2
,p_s_final_process_date IN OUT NOCOPY DATE
,p_s_actual_termination_date IN OUT NOCOPY DATE
,p_c_assignment_status_type_id IN OUT NOCOPY NUMBER
,p_d_status VARCHAR2
,p_requery_required IN OUT NOCOPY VARCHAR2
,p_clear_details VARCHAR2 DEFAULT 'N'
,p_legislation_code VARCHAR2
,p_pds_information_category VARCHAR2
,p_pds_information1 VARCHAR2
,p_pds_information2 VARCHAR2
,p_pds_information3 VARCHAR2
,p_pds_information4 VARCHAR2
,p_pds_information5 VARCHAR2
,p_pds_information6 VARCHAR2
,p_pds_information7 VARCHAR2
,p_pds_information8 VARCHAR2
,p_pds_information9 VARCHAR2
,p_pds_information10 VARCHAR2
,p_pds_information11 VARCHAR2
,p_pds_information12 VARCHAR2
,p_pds_information13 VARCHAR2
,p_pds_information14 VARCHAR2
,p_pds_information15 VARCHAR2
,p_pds_information16 VARCHAR2
,p_pds_information17 VARCHAR2
,p_pds_information18 VARCHAR2
,p_pds_information19 VARCHAR2
,p_pds_information20 VARCHAR2
,p_pds_information21 VARCHAR2
,p_pds_information22 VARCHAR2
,p_pds_information23 VARCHAR2
,p_pds_information24 VARCHAR2
,p_pds_information25 VARCHAR2
,p_pds_information26 VARCHAR2
,p_pds_information27 VARCHAR2
,p_pds_information28 VARCHAR2
,p_pds_information29 VARCHAR2
,p_pds_information30 VARCHAR2
,p_adjusted_svc_date DATE) is
p_dodwarning varchar2(1);
begin update_term_row
(p_row_id
,p_period_of_service_id
,p_business_group_id
,p_person_id
,p_date_start
,p_termination_accepted_per_id
,p_accepted_termination_date
,p_actual_termination_date
,p_comments
,p_final_process_date
,p_last_standard_process_date
,p_leaving_reason
,p_notified_termination_date
,p_projected_termination_date
,p_request_id
,p_program_application_id
,p_program_id
,p_program_update_date
,p_attribute_category
,p_attribute1
,p_attribute2
,p_attribute3
,p_attribute4
,p_attribute5
,p_attribute6
,p_attribute7
,p_attribute8
,p_attribute9
,p_attribute10
,p_attribute11
,p_attribute12
,p_attribute13
,p_attribute14
,p_attribute15
,p_attribute16
,p_attribute17
,p_attribute18
,p_attribute19
,p_attribute20
,p_initiate_cancellation
,p_s_final_process_date
,p_s_actual_termination_date
,p_c_assignment_status_type_id
,p_d_status
,p_requery_required
,p_clear_details
,p_legislation_code
,p_pds_information_category
,p_pds_information1
,p_pds_information2
,p_pds_information3
,p_pds_information4
,p_pds_information5
,p_pds_information6
,p_pds_information7
,p_pds_information8
,p_pds_information9
,p_pds_information10
,p_pds_information11
,p_pds_information12
,p_pds_information13
,p_pds_information14
,p_pds_information15
,p_pds_information16
,p_pds_information17
,p_pds_information18
,p_pds_information19
,p_pds_information20
,p_pds_information21
,p_pds_information22
,p_pds_information23
,p_pds_information24
,p_pds_information25
,p_pds_information26
,p_pds_information27
,p_pds_information28
,p_pds_information29
,p_pds_information30
,p_adjusted_svc_date
,p_dodwarning
);
procedure update_term_row(p_row_id VARCHAR2
,p_period_of_service_id NUMBER
,p_business_group_id NUMBER
,p_person_id NUMBER
,p_date_start DATE
,p_termination_accepted_per_id NUMBER
,p_accepted_termination_date DATE
,p_actual_termination_date DATE
,p_comments VARCHAR2
,p_final_process_date DATE
,p_last_standard_process_date DATE
,p_leaving_reason VARCHAR2
,p_notified_termination_date DATE
,p_projected_termination_date DATE
,p_request_id NUMBER
,p_program_application_id NUMBER
,p_program_id NUMBER
,p_program_update_date DATE
,p_attribute_category VARCHAR2
,p_attribute1 VARCHAR2
,p_attribute2 VARCHAR2
,p_attribute3 VARCHAR2
,p_attribute4 VARCHAR2
,p_attribute5 VARCHAR2
,p_attribute6 VARCHAR2
,p_attribute7 VARCHAR2
,p_attribute8 VARCHAR2
,p_attribute9 VARCHAR2
,p_attribute10 VARCHAR2
,p_attribute11 VARCHAR2
,p_attribute12 VARCHAR2
,p_attribute13 VARCHAR2
,p_attribute14 VARCHAR2
,p_attribute15 VARCHAR2
,p_attribute16 VARCHAR2
,p_attribute17 VARCHAR2
,p_attribute18 VARCHAR2
,p_attribute19 VARCHAR2
,p_attribute20 VARCHAR2
,p_initiate_cancellation VARCHAR2
,p_s_final_process_date IN OUT NOCOPY DATE
,p_s_actual_termination_date IN OUT NOCOPY DATE
,p_c_assignment_status_type_id IN OUT NOCOPY NUMBER
,p_d_status VARCHAR2
,p_requery_required IN OUT NOCOPY VARCHAR2
,p_clear_details VARCHAR2 DEFAULT 'N'
,p_legislation_code VARCHAR2
,p_pds_information_category VARCHAR2
,p_pds_information1 VARCHAR2
,p_pds_information2 VARCHAR2
,p_pds_information3 VARCHAR2
,p_pds_information4 VARCHAR2
,p_pds_information5 VARCHAR2
,p_pds_information6 VARCHAR2
,p_pds_information7 VARCHAR2
,p_pds_information8 VARCHAR2
,p_pds_information9 VARCHAR2
,p_pds_information10 VARCHAR2
,p_pds_information11 VARCHAR2
,p_pds_information12 VARCHAR2
,p_pds_information13 VARCHAR2
,p_pds_information14 VARCHAR2
,p_pds_information15 VARCHAR2
,p_pds_information16 VARCHAR2
,p_pds_information17 VARCHAR2
,p_pds_information18 VARCHAR2
,p_pds_information19 VARCHAR2
,p_pds_information20 VARCHAR2
,p_pds_information21 VARCHAR2
,p_pds_information22 VARCHAR2
,p_pds_information23 VARCHAR2
,p_pds_information24 VARCHAR2
,p_pds_information25 VARCHAR2
,p_pds_information26 VARCHAR2
,p_pds_information27 VARCHAR2
,p_pds_information28 VARCHAR2
,p_pds_information29 VARCHAR2
,p_pds_information30 VARCHAR2
,p_adjusted_svc_date DATE
,p_dodwarning OUT NOCOPY VARCHAR2) is
--
l_proc varchar2(30) := 'update_term_row';
select date_start,
actual_termination_date,
final_process_date,
leaving_reason
from per_periods_of_service
where period_of_service_id = p_period_of_service_id;
select rowid,
business_group_id,
person_id,
effective_start_date,
effective_end_date,
date_of_birth,
date_of_death,
marital_status,
on_military_service,
registered_disabled_flag,
sex,
student_status,
coord_ben_med_pln_no,
coord_ben_no_cvg_flag,
uses_tobacco_flag,
benefit_group_id,
per_information10,
original_date_of_hire,
dpdnt_vlntry_svce_flag,
receipt_of_death_cert_date,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30
from per_all_people_f
where person_id = p_person_id
and p_date
between effective_start_date
and effective_end_date;
hrempter.terminate_employee('PRE_UPDATE'
,p_business_group_id
,p_person_id
,p_c_assignment_status_type_id
,p_actual_termination_date
,p_last_standard_process_date
,p_final_process_date);
hrempter.employee_shutdown('PRE_UPDATE'
,p_person_id
,p_final_process_date);
per_periods_of_service_pkg.update_row(p_row_id => p_row_id
,p_period_of_service_id => p_period_of_service_id
,p_business_group_id => p_business_group_id
,p_person_id => p_person_id
,p_date_start => p_date_start
,p_termination_accepted_per_id => p_termination_accepted_per_id
,p_accepted_termination_date => p_accepted_termination_date
,p_actual_termination_date => p_actual_termination_date
,p_comments => p_comments
,p_final_process_date => p_final_process_date
,p_last_standard_process_date => p_last_standard_process_date
,p_leaving_reason => p_leaving_reason
,p_notified_termination_date => p_notified_termination_date
,p_projected_termination_date => p_projected_termination_date
,p_request_id => p_request_id
,p_program_application_id => p_program_application_id
,p_program_id => p_program_id
,p_program_update_date => p_program_update_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
,p_adjusted_svc_date => p_adjusted_svc_date
);
/* This delete is now doen in hrempter.terminate_employee so that the
deletion of pay proposals can be kept in step with the deletion
of elements.
per_periods_of_service_pkg.delete_per_pay_proposals(
p_period_of_service_id => p_period_of_service_id
,p_actual_termination_date => p_actual_termination_date);
hrempter.terminate_employee('POST_UPDATE'
,p_business_group_id
,p_person_id
,p_c_assignment_status_type_id
,p_actual_termination_date
,p_last_standard_process_date
,p_final_process_date);
hrempter.employee_shutdown ('POST_UPDATE'
,p_person_id
,p_final_process_date);
update per_people_f
set date_of_death = p_actual_termination_date
where person_id = p_person_id
and effective_start_date >= p_actual_termination_date +1
and date_of_death is null;
,p_datetrack_update_mode => 'UPDATE');
,p_datetrack_delete_mode => 'ZAP');
end update_term_row;
procedure update_row(p_row_id VARCHAR2
,p_period_of_service_id NUMBER
,p_business_group_id NUMBER
,p_person_id NUMBER
,p_date_start DATE
,p_termination_accepted_per_id NUMBER
,p_accepted_termination_date DATE
,p_actual_termination_date DATE
,p_comments VARCHAR2
,p_final_process_date DATE
,p_last_standard_process_date DATE
,p_leaving_reason VARCHAR2
,p_notified_termination_date DATE
,p_projected_termination_date DATE
,p_request_id NUMBER
,p_program_application_id NUMBER
,p_program_id NUMBER
,p_program_update_date DATE
,p_attribute_category VARCHAR2
,p_attribute1 VARCHAR2
,p_attribute2 VARCHAR2
,p_attribute3 VARCHAR2
,p_attribute4 VARCHAR2
,p_attribute5 VARCHAR2
,p_attribute6 VARCHAR2
,p_attribute7 VARCHAR2
,p_attribute8 VARCHAR2
,p_attribute9 VARCHAR2
,p_attribute10 VARCHAR2
,p_attribute11 VARCHAR2
,p_attribute12 VARCHAR2
,p_attribute13 VARCHAR2
,p_attribute14 VARCHAR2
,p_attribute15 VARCHAR2
,p_attribute16 VARCHAR2
,p_attribute17 VARCHAR2
,p_attribute18 VARCHAR2
,p_attribute19 VARCHAR2
,p_attribute20 VARCHAR2
,p_pds_information_category VARCHAR2
,p_pds_information1 VARCHAR2
,p_pds_information2 VARCHAR2
,p_pds_information3 VARCHAR2
,p_pds_information4 VARCHAR2
,p_pds_information5 VARCHAR2
,p_pds_information6 VARCHAR2
,p_pds_information7 VARCHAR2
,p_pds_information8 VARCHAR2
,p_pds_information9 VARCHAR2
,p_pds_information10 VARCHAR2
,p_pds_information11 VARCHAR2
,p_pds_information12 VARCHAR2
,p_pds_information13 VARCHAR2
,p_pds_information14 VARCHAR2
,p_pds_information15 VARCHAR2
,p_pds_information16 VARCHAR2
,p_pds_information17 VARCHAR2
,p_pds_information18 VARCHAR2
,p_pds_information19 VARCHAR2
,p_pds_information20 VARCHAR2
,p_pds_information21 VARCHAR2
,p_pds_information22 VARCHAR2
,p_pds_information23 VARCHAR2
,p_pds_information24 VARCHAR2
,p_pds_information25 VARCHAR2
,p_pds_information26 VARCHAR2
,p_pds_information27 VARCHAR2
,p_pds_information28 VARCHAR2
,p_pds_information29 VARCHAR2
,p_pds_information30 VARCHAR2
,p_adjusted_svc_date DATE
) is
--
--
-- START WWBUG fix for 1390173
--
cursor c1 is
select *
from per_periods_of_service
where rowid = chartorowid(p_row_id);
update per_periods_of_service pps
set pps.period_of_service_id = p_period_of_service_id
,pps.business_group_id = p_business_group_id
,pps.person_id = p_person_id
,pps.date_start = p_date_start
,pps.termination_accepted_person_id = p_termination_accepted_per_id
,pps.accepted_termination_date = p_accepted_termination_date
,pps.actual_termination_date = p_actual_termination_date
,pps.comments = p_comments
,pps.final_process_date = p_final_process_date
,pps.last_standard_process_date = p_last_standard_process_date
,pps.leaving_reason = p_leaving_reason
,pps.notified_termination_date = p_notified_termination_date
,pps.projected_termination_date = p_projected_termination_date
,pps.request_id = p_request_id
,pps.program_application_id = p_program_application_id
,pps.program_id = p_program_id
,pps.program_update_date = p_program_update_date
,pps.attribute_category = p_attribute_category
,pps.attribute1 = p_attribute1
,pps.attribute2 = p_attribute2
,pps.attribute3 = p_attribute3
,pps.attribute4 = p_attribute4
,pps.attribute5 = p_attribute5
,pps.attribute6 = p_attribute6
,pps.attribute7 = p_attribute7
,pps.attribute8 = p_attribute8
,pps.attribute9 = p_attribute9
,pps.attribute10 = p_attribute10
,pps.attribute11 = p_attribute11
,pps.attribute12 = p_attribute12
,pps.attribute13 = p_attribute13
,pps.attribute14 = p_attribute14
,pps.attribute15 = p_attribute15
,pps.attribute16 = p_attribute16
,pps.attribute17 = p_attribute17
,pps.attribute18 = p_attribute18
,pps.attribute19 = p_attribute19
,pps.attribute20 = p_attribute20
,pps.pds_information_category = p_pds_information_category
,pps.pds_information1 = p_pds_information1
,pps.pds_information2 = p_pds_information2
,pps.pds_information3 = p_pds_information3
,pps.pds_information4 = p_pds_information4
,pps.pds_information5 = p_pds_information5
,pps.pds_information6 = p_pds_information6
,pps.pds_information7 = p_pds_information7
,pps.pds_information8 = p_pds_information8
,pps.pds_information9 = p_pds_information9
,pps.pds_information10 = p_pds_information10
,pps.pds_information11 = p_pds_information11
,pps.pds_information12 = p_pds_information12
,pps.pds_information13 = p_pds_information13
,pps.pds_information14 = p_pds_information14
,pps.pds_information15 = p_pds_information15
,pps.pds_information16 = p_pds_information16
,pps.pds_information17 = p_pds_information17
,pps.pds_information18 = p_pds_information18
,pps.pds_information19 = p_pds_information19
,pps.pds_information20 = p_pds_information20
,pps.pds_information21 = p_pds_information21
,pps.pds_information22 = p_pds_information22
,pps.pds_information23 = p_pds_information23
,pps.pds_information24 = p_pds_information24
,pps.pds_information25 = p_pds_information25
,pps.pds_information26 = p_pds_information26
,pps.pds_information27 = p_pds_information27
,pps.pds_information28 = p_pds_information28
,pps.pds_information29 = p_pds_information29
,pps.pds_information30 = p_pds_information30
,pps.adjusted_svc_date = p_adjusted_svc_date
where rowid = chartorowid(p_row_id);
end update_row;
cursor st1 is SELECT NVL(STATL.USER_STATUS,STTTL.USER_STATUS)
, STT.ASSIGNMENT_STATUS_TYPE_ID
FROM PER_ASSIGNMENT_STATUS_TYPES_TL STTTL
, PER_ASSIGNMENT_STATUS_TYPES STT
, PER_ASS_STATUS_TYPE_AMENDS_TL STATL
, PER_ASS_STATUS_TYPE_AMENDS STA
, PER_ALL_ASSIGNMENTS_F ASG
WHERE ASG.PERSON_ID = p_person_id
AND STT.ASSIGNMENT_STATUS_TYPE_ID = STTTL.ASSIGNMENT_STATUS_TYPE_ID
AND STA.ASS_STATUS_TYPE_AMEND_ID =
STATL.ASS_STATUS_TYPE_AMEND_ID(+)
AND STT.ASSIGNMENT_STATUS_TYPE_ID =
STA.ASSIGNMENT_STATUS_TYPE_ID (+)
AND ASG.ASSIGNMENT_STATUS_TYPE_ID = STT.ASSIGNMENT_STATUS_TYPE_ID
AND NVL(STA.ACTIVE_FLAG,STT.ACTIVE_FLAG) = 'Y'
AND NVL(STT.DEFAULT_FLAG,STA.DEFAULT_FLAG) = 'Y'
AND ASG.PRIMARY_FLAG = 'Y'
AND NVL(STA.PER_SYSTEM_STATUS,STT.PER_SYSTEM_STATUS) =
'TERM_ASSIGN'
AND decode(STATL.ASS_STATUS_TYPE_AMEND_ID, NULL, '1', STATL.LANGUAGE)
= decode(STATL.ASS_STATUS_TYPE_AMEND_ID, NULL, '1', userenv('LANG'))
AND STTTL.LANGUAGE = USERENV('LANG');
select p.full_name,nvl(p.employee_number, p.npw_number)
from per_all_people_f p,
per_periods_of_service pps
where p.person_id = pps.termination_accepted_person_id
and (p.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N') = 'Y')
and pps.period_of_service_id = p_period_of_service_id
and nvl(pps.accepted_termination_date,p_session_date)
between p.effective_start_date and p.effective_end_date ;