[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1
from per_assignments_f a
where a.assignment_id = P_ASS_ID
and a.effective_start_date > P_SESS_DATE
and exists (select null
from per_assignment_status_types s
where s.assignment_status_type_id =
a.assignment_status_type_id
and s.per_system_status <> 'TERM_ASSIGN');
if p_dt_mode = 'UPDATE_OVERRIDE' then
l_start_date := p_val_start_date;
procedure pre_update_bundle2(
p_upd_mode varchar2,
p_del_mode varchar2,
p_sess_date date,
p_per_sys_st varchar2,
p_val_st_date date,
p_new_end_date date,
p_val_end_date date,
p_ass_id number,
p_pay_id number,
p_eot date,
p_eff_end_date date,
p_prim_flag varchar2,
p_new_prim_flag IN OUT NOCOPY varchar2,
p_pd_os_id number,
p_s_per_sys_st varchar2,
p_ass_number varchar2,
p_s_ass_number varchar2,
p_row_id varchar2,
p_s_prim_flag varchar2,
p_bg_id number,
p_eff_st_date date,
p_grd_id number,
p_sp_ceil_st_id number,
p_ceil_seq number,
p_re_entry_point IN OUT NOCOPY number,
p_returned_warning IN OUT NOCOPY varchar2,
p_prim_change_flag IN OUT NOCOPY varchar2,
p_prim_date_from IN OUT NOCOPY date,
p_new_prim_ass_id IN OUT NOCOPY varchar2,
p_cancel_atd IN OUT NOCOPY date,
p_cancel_lspd IN OUT NOCOPY date,
p_reterm_atd IN OUT NOCOPY date,
p_reterm_lspd IN OUT NOCOPY date,
p_copy_y_to_prim_ch IN OUT NOCOPY varchar2,
p_pay_basis_id number --fix for bug 4764140
) is
l_re_entry_point number;
hr_utility.set_location('Entering: '|| 'PER_ASSIGNMENTS_F3_PKG.pre_update_bundle2' , 5);
if p_upd_mode <> 'UPDATE_OVERRIDE' then
--
-- Do payroll_change_validate.
--
l_re_entry_point := 2;
update_and_delete_bundle(
p_upd_mode,
p_val_st_date,
p_eff_st_date,
p_eff_end_date,
p_pd_os_id,
p_per_sys_st,
p_ass_id,
p_val_end_date,
p_upd_mode,
p_del_mode,
p_sess_date,
p_pay_id,
p_grd_id,
p_sp_ceil_st_id,
p_ceil_seq,
l_new_end_date,
l_warning,
l_re_entry_point,
'Y',
p_pay_basis_id );--fix for bug 4764140.
hr_utility.set_location('PER_ASSIGNMENTS_F3_PKG.pre_update_bundle2' , 10);
hr_utility.set_location('PER_ASSIGNMENTS_F3_PKG.pre_update_bundle2' , 20);
if p_upd_mode = 'UPDATE_OVERRIDE' then
check_future_primary(
p_upd_mode,
p_val_st_date,
p_prim_flag,
p_eff_st_date,
p_s_prim_flag,
p_prim_change_flag,
p_new_prim_flag,
p_ass_id,
p_eff_end_date,
p_pd_os_id,
l_show_cand_prim_assgts,
p_prim_date_from,
p_new_prim_ass_id);
hr_utility.set_location('PER_ASSIGNMENTS_F3_PKG.pre_update_bundle2' , 30);
update per_all_assignments_f a
set a.assignment_number = P_ASS_NUMBER
where a.business_group_id + 0 = P_BG_ID
and a.rowid <> P_ROW_ID
and a.assignment_id = P_ASS_ID;
hr_utility.set_location('Leaving: '|| 'PER_ASSIGNMENTS_F3_PKG.pre_update_bundle2' , 40);
end pre_update_bundle2;
procedure update_and_delete_bundle(
p_dt_mode varchar2,
p_val_st_date date,
p_eff_st_date date,
p_eff_end_date date,
p_pd_os_id number,
p_per_sys_st varchar2,
p_ass_id number,
p_val_end_date date,
p_dt_upd_mode varchar2,
p_dt_del_mode varchar2,
p_sess_date date,
p_pay_id number,
p_grd_id number,
p_sp_ceil_st_id number,
p_ceil_seq number,
p_new_end_date IN OUT NOCOPY date,
p_returned_warning IN OUT NOCOPY varchar2,
p_re_entry_point IN OUT NOCOPY number,
p_only_one_entry_point varchar2,
p_pay_basis_id number --fix for bug 4764140
) is
--
l_null number;
select 1
from sys.dual where exists (
select null
from per_spinal_point_steps_f s,
per_spinal_point_placements_f p,
per_grade_spines_f g
where s.step_id = p.step_id
and g.grade_spine_id = s.grade_spine_id
and s.sequence > P_CEIL_SEQ
and p.assignment_id = P_ASS_ID
and g.grade_id = P_GRD_ID
and (
(p_eff_st_date BETWEEN p.effective_start_date
AND p.effective_end_date)
OR ( ( p_dt_mode='UPDATE_OVERRIDE' or p_dt_mode= 'UPDATE')
and p_eff_st_date <= p.effective_start_date )
)
); -- modified the cursor for bug fix 7205433
select element_entry_id
from pay_element_entries_f
where assignment_id = p_ass_id
and creator_type = 'SP'
and l_val_start_date between
effective_start_date and effective_end_date;
select element_entry_id
from pay_element_entries_f
where assignment_id = p_ass_id
and creator_type = 'SP'
and (l_val_start_date - 1) between
effective_start_date and effective_end_date;
select pay_basis_id
from per_all_assignments
where assignment_id = p_ass_id
and effective_start_date <= p_eff_st_date
and effective_end_date >= p_eff_st_date;
Select Pay_Proposal_Id, Object_Version_Number,business_group_id,change_date
From Per_Pay_Proposals
where assignment_id = p_ass_id
and change_date <= p_val_st_date
and nvl(date_to,to_date('31/12/4712','dd/mm/yyyy')) >= p_val_st_date;
select s.effective_end_date
from per_spinal_point_steps_f s,
per_grade_spines_F g
where s.step_id= p_sp_ceil_st_id
and g.grade_id= P_GRD_ID
and g.grade_spine_id=s.grade_spine_id ;
select max(effective_end_date ) from per_all_assignments_f
where assignment_id= P_ASS_ID
-- and grade_id=P_GRD_ID
and assignment_type='E';
SELECT legislation_code
FROM per_business_groups
WHERE business_group_id = (SELECT business_group_id
FROM per_all_assignments_f
WHERE assignment_id = p_ass_id
and effective_start_date <= p_eff_st_date
and effective_end_date >= p_eff_st_date);
hr_utility.set_location('per_assignments_f3_pkg.update_and_delete_bundle',1);
if p_dt_mode = 'UPDATE_OVERRIDE' then
l_start_date := p_val_st_date;
if p_dt_upd_mode = 'UPDATE_OVERRIDE' then
l_start_date := p_val_st_date;
l_dt_mode := 'DELETE';
l_dt_mode := 'DELETE';
if l_dt_mode = 'DELETE' then
l_val_start_date := p_sess_date;
hr_maintain_proposal_api.update_salary_proposal(
p_validate => false,
p_pay_proposal_id => l_Del_proposal_Id,
p_date_to => l_val_start_date - 1,
p_object_version_number => l_Del_Proposal_Ovn,
p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
p_proposed_salary_warning => l_proposed_salary_warning,
p_approved_warning => l_approved_warning,
p_payroll_warning => l_payroll_warning);
Hr_Maintain_Proposal_Api.DELETE_SALARY_PROPOSAL
(P_PAY_PROPOSAL_ID => l_Del_proposal_Id
,P_BUSINESS_GROUP_ID => l_del_bg_id
,P_OBJECT_VERSION_NUMBER => l_Del_Proposal_Ovn
,P_SALARY_WARNING => l_Del_Warn);
hr_entry_api.delete_element_entry
('DELETE'
,l_val_start_date - 1
,l_element_entry_id1);
hr_entry_api.delete_element_entry
('ZAP'
,l_val_start_date
,l_element_entry_id);
hr_utility.set_location('per_assignments_f3_pkg.update_and_delete_bundle',2);
end update_and_delete_bundle;