[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
p_ins_new_sal_flag varchar2 -- fix for bug 9109727
) 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.
p_ins_new_sal_flag); -- fix for bug 9109727
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
p_ins_new_sal_flag VARCHAR2 DEFAULT 'N' -- fix for 9109727
) 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);
Select ele.element_entry_id
from per_pay_bases bas,
pay_element_entries_f ele,
pay_element_entry_values_f entval
where bas.pay_basis_id = P_pay_basis_id
and entval.input_value_id = bas.input_value_id
and p_effective_date
between entval.effective_start_date
and entval.effective_end_date
and ele.assignment_id = P_assignmnet_id
and p_effective_date between ele.effective_start_date
and ele.effective_end_date
and ele.element_entry_id = entval.element_entry_id;
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);
/******** Commented out for bug fix 9128059. This code has been moved to ASSGT.POST-UPDATE trigger, form PERWSEMA.fmb **********
-- If asked by the user, create a new sal proposal with the new sal basis
IF nvl(p_ins_new_sal_flag, 'N') = 'Y' then
Open Element_Info(p_ass_id, p_pay_basis_id, l_val_start_date);
Hr_Maintain_Proposal_Api.INSERT_SALARY_PROPOSAL
(P_PAY_PROPOSAL_ID => l_Pay_Proposal_Id
,P_ASSIGNMENT_ID => p_ass_id
,P_BUSINESS_GROUP_ID => l_del_bg_id
,P_CHANGE_DATE => l_val_start_date
,P_PROPOSED_SALARY_N => per_saladmin_utility.get_proposed_salary(p_ass_id,l_val_start_date-1)
,P_OBJECT_VERSION_NUMBER => l_ovn
,P_ELEMENT_ENTRY_ID => L_Element_Entry_Id
,P_MULTIPLE_COMPONENTS => 'N'
,P_APPROVED => 'Y'
,P_PROPOSAL_REASON => l_proposal_reason -- Modified for bug 10145976
,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);
********** Commented out for bug fix 9128059. This code has been moved to ASSGT.POST-UPDATE trigger, form PERWSEMA.fmb ***********/
Elsif l_del_proposal_change_dt = l_val_start_date THEN
-- Zap the proposal and the element entry.
hr_utility.set_location('Zap proposal',25);
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_Maintain_Proposal_Api.INSERT_SALARY_PROPOSAL
(P_PAY_PROPOSAL_ID => l_Pay_Proposal_Id
,P_ASSIGNMENT_ID => p_ass_id
,P_BUSINESS_GROUP_ID => l_del_bg_id
,P_CHANGE_DATE => l_val_start_date
,P_PROPOSED_SALARY_N => per_saladmin_utility.get_proposed_salary(p_ass_id,l_val_start_date-1)
,P_OBJECT_VERSION_NUMBER => l_ovn
,P_ELEMENT_ENTRY_ID => L_Element_Entry_Id
,P_MULTIPLE_COMPONENTS => 'N'
,P_APPROVED => 'Y'
,P_PROPOSAL_REASON => l_proposal_reason -- Modified for bug 10161268
,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_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;