[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION get_update_mode(
p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
,p_ovn IN per_all_assignments_f.object_version_number%TYPE
,p_effective_date IN DATE
)
RETURN VARCHAR2
IS
l_correction boolean;
l_update boolean;
l_update_override boolean;
l_update_change_insert boolean;
l_datetrack_update_mode varchar2(100);
,p_update => l_update
,p_update_override => l_update_override
,p_update_change_insert => l_update_change_insert);
if l_update then
-- we can do an update
l_datetrack_update_mode := hr_api.g_update;
elsif l_update_change_insert then
-- we can do an update change insert
l_datetrack_update_mode := hr_api.g_update_change_insert;
l_datetrack_update_mode := hr_api.g_correction;
l_datetrack_update_mode := hr_api.g_update;
RETURN l_datetrack_update_mode;
procedure delete_transaction
(p_transaction_id in number
,p_transaction_type in varchar2) is
begin
delete ben_transaction
where transaction_id = p_transaction_id
and transaction_type = p_transaction_type;
select inf.group_per_in_ler_id
,inf.group_pl_id pl_id
,inf.lf_evt_ocrd_dt
from ben_cwb_person_info inf
,ben_cwb_pl_dsgn pl
where inf.assignment_id = p_txn_old.transaction_id
and inf.group_pl_id = p_group_pl_id
and inf.group_pl_id = pl.pl_id
and inf.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
and pl.oipl_id = -1
and to_char(pl.perf_revw_strt_dt,'yyyy/mm/dd') = p_txn_old.attribute1
and nvl(pl.emp_interview_typ_cd,'-1') = nvl(p_txn_old.attribute2,'-1');
select meaning
from hr_lookups
where lookup_type = v_lookup_type
and lookup_code = v_lookup_code;
select employee_id
from fnd_user
where user_id = l_user_id;
select *
from ben_transaction
where transaction_id = p_assgn_id
and transaction_type = p_trans_type;
select * into l_txn_new
from ben_transaction
where transaction_id = p_txn_old.transaction_id
and transaction_type = p_txn_old.transaction_type;
select inf.group_per_in_ler_id
,inf.group_pl_id pl_id
,inf.lf_evt_ocrd_dt
from ben_cwb_person_info inf
,ben_cwb_pl_dsgn pl
where inf.assignment_id = p_txn_old.transaction_id
and inf.group_pl_id = p_group_pl_id
and inf.group_pl_id = pl.pl_id
and inf.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
and pl.oipl_id = -1
and to_char(pl.asg_updt_eff_date,'yyyy/mm/dd') = p_txn_old.attribute1;
select meaning
from hr_lookups
where lookup_type = v_lookup_type
and lookup_code = v_lookup_code;
select employee_id
from fnd_user
where user_id = fnd_global.user_id;
select *
from ben_transaction
where transaction_id = v_assgn_id
and transaction_type = v_trans_type;
select substr(name,1,200)
from per_jobs_tl
where job_id = v_job_id
and language = v_lang;
select substr(name,1,200)
from per_grades_tl
where grade_id = v_grade_id
and language = v_lang;
select substr(name,1,200)
from hr_all_positions_f_tl
where position_id = v_position_id
and language = l_lang;
select userenv('LANG') into l_lang
from dual;
select * into l_txn_new
from ben_transaction
where transaction_id = p_txn_old.transaction_id
and transaction_type = p_txn_old.transaction_type;
procedure insert_or_update_transaction
(p_transaction_id in number
,p_transaction_type in varchar2
,p_txn_rec in g_txn%rowtype) is
cursor c_found is
select txn.transaction_id
from ben_transaction txn
where txn.transaction_id = p_transaction_id
and txn.transaction_type = p_transaction_type;
insert into ben_transaction
(transaction_id ,transaction_type
,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
,attribute31 ,attribute32
,attribute33 ,attribute34
,attribute35 ,attribute36
,attribute37 ,attribute38
,attribute39 ,attribute40)
values
(p_transaction_id ,p_transaction_type
,p_txn_rec.attribute1 ,p_txn_rec.attribute2
,p_txn_rec.attribute3 ,p_txn_rec.attribute4
,p_txn_rec.attribute5 ,p_txn_rec.attribute6
,p_txn_rec.attribute7 ,p_txn_rec.attribute8
,p_txn_rec.attribute9 ,p_txn_rec.attribute10
,p_txn_rec.attribute11 ,p_txn_rec.attribute12
,p_txn_rec.attribute13 ,p_txn_rec.attribute14
,p_txn_rec.attribute15 ,p_txn_rec.attribute16
,p_txn_rec.attribute17 ,p_txn_rec.attribute18
,p_txn_rec.attribute19 ,p_txn_rec.attribute20
,p_txn_rec.attribute21 ,p_txn_rec.attribute22
,p_txn_rec.attribute23 ,p_txn_rec.attribute24
,p_txn_rec.attribute25 ,p_txn_rec.attribute26
,p_txn_rec.attribute27 ,p_txn_rec.attribute28
,p_txn_rec.attribute29 ,p_txn_rec.attribute30
,p_txn_rec.attribute31 ,p_txn_rec.attribute32
,p_txn_rec.attribute33 ,p_txn_rec.attribute34
,p_txn_rec.attribute35 ,p_txn_rec.attribute36
,p_txn_rec.attribute37 ,p_txn_rec.attribute38
,p_txn_rec.attribute39 ,p_txn_rec.attribute40);
update ben_transaction
set attribute1 = p_txn_rec.attribute1
,attribute2 = p_txn_rec.attribute2
,attribute3 = p_txn_rec.attribute3
,attribute4 = p_txn_rec.attribute4
,attribute5 = p_txn_rec.attribute5
,attribute6 = p_txn_rec.attribute6
,attribute7 = p_txn_rec.attribute7
,attribute8 = p_txn_rec.attribute8
,attribute9 = p_txn_rec.attribute9
,attribute10 = p_txn_rec.attribute10
,attribute11 = p_txn_rec.attribute11
,attribute12 = p_txn_rec.attribute12
,attribute13 = p_txn_rec.attribute13
,attribute14 = p_txn_rec.attribute14
,attribute15 = p_txn_rec.attribute15
,attribute16 = p_txn_rec.attribute16
,attribute17 = p_txn_rec.attribute17
,attribute18 = p_txn_rec.attribute18
,attribute19 = p_txn_rec.attribute19
,attribute20 = p_txn_rec.attribute20
,attribute21 = p_txn_rec.attribute21
,attribute22 = p_txn_rec.attribute22
,attribute23 = p_txn_rec.attribute23
,attribute24 = p_txn_rec.attribute24
,attribute25 = p_txn_rec.attribute25
,attribute26 = p_txn_rec.attribute26
,attribute27 = p_txn_rec.attribute27
,attribute28 = p_txn_rec.attribute28
,attribute29 = p_txn_rec.attribute29
,attribute30 = p_txn_rec.attribute30
,attribute31 = p_txn_rec.attribute31
,attribute32 = p_txn_rec.attribute32
,attribute33 = p_txn_rec.attribute33
,attribute34 = p_txn_rec.attribute34
,attribute35 = p_txn_rec.attribute35
,attribute36 = p_txn_rec.attribute36
,attribute37 = p_txn_rec.attribute37
,attribute38 = p_txn_rec.attribute38
,attribute39 = p_txn_rec.attribute39
,attribute40 = p_txn_rec.attribute40
where transaction_id = p_transaction_id
and transaction_type = p_transaction_type;
end insert_or_update_transaction;
,p_update_person_id in number
,p_update_date in date
,p_group_pl_id in number) is
l_txn g_txn%rowtype;
select *
from ben_transaction
where transaction_id = p_assgn_id
and transaction_type = g_ws_perf_rec_typ || p_perf_revw_dt || p_perfor_type;
delete_transaction(p_transaction_id => p_assignment_id
,p_transaction_type => g_ws_perf_rec_type||p_perf_revw_strt_dt||p_perf_type);
l_txn.attribute4 := to_char(p_update_date, 'yyyy/mm/dd');
l_txn.attribute5 := p_update_person_id;
insert_or_update_transaction(p_transaction_id => p_assignment_id
,p_transaction_type => g_ws_perf_rec_type||p_perf_revw_strt_dt||p_perf_type
,p_txn_rec => l_txn);
,p_update_person_id in number
,p_update_date in date
,p_group_pl_id in number) is
l_txn g_txn%rowtype;
select *
from ben_transaction
where transaction_id = p_assgn_id
and transaction_type = g_ws_perf_rec_typ || p_asg_updt_eff_dt;
delete_transaction(p_transaction_id => p_assignment_id
,p_transaction_type => g_ws_asg_rec_type||p_asg_updt_eff_date);
insert_or_update_transaction(p_transaction_id => p_assignment_id
,p_transaction_type => g_ws_asg_rec_type||p_asg_updt_eff_date
,p_txn_rec => l_txn);
l_update_event_id number;
l_update_review_id number;
select perf.performance_review_id
,perf.event_id
,perf.object_version_number
from per_performance_reviews perf
where perf.person_id = p_person_id
and perf.review_date = l_perf_date;
select evt.type
from per_events evt
where evt.assignment_id = p_txn_rec.assignment_id
and evt.date_start <= l_perf_date
and evt.event_id = l_event_id;
hr_perf_review_api.update_perf_review(
p_validate => false
,p_performance_review_id => l_performance_review_id
,p_performance_rating => p_txn_rec.attribute3
,p_object_version_number => l_perf_ovn
,p_next_review_date_warning => l_next_review_date_warning);
l_update_event_id := l_event_id;
l_update_review_id := l_performance_review_id;
update ben_cwb_person_info
set new_perf_event_id = l_update_event_id,
new_perf_review_id = l_update_review_id,
performance_rating = p_txn_rec.attribute3
where group_per_in_ler_id = p_group_per_in_ler_id;
l_spp_delete_warning boolean;
l_update_position_id number;
l_update_done boolean := false;
select asg.*
from per_all_assignments_f asg
where asg.assignment_id = l_assignment_id
and l_effective_date between
asg.effective_start_date and asg.effective_end_date;
l_datetrack_mode := hr_api.g_update;
l_datetrack_mode := get_update_mode(
p_assignment_id => l_assignment_id
,p_ovn => l_asg_rec.object_version_number
,p_effective_date => l_effective_date
);
hr_assignment_api.update_emp_asg(
p_validate => false
,p_effective_date => l_effective_date
,p_datetrack_update_mode => l_datetrack_mode
,p_assignment_id => l_assignment_id
,p_object_version_number => l_asg_rec.object_version_number
,p_change_reason => nvl(p_asg_txn_rec.attribute3,
l_asg_rec.change_reason)
,p_ass_attribute_category => l_asg_rec.ass_attribute_category
,p_ass_attribute1 => nvl(p_asg_txn_rec.attribute11,
l_asg_rec.ass_attribute1)
,p_ass_attribute2 => nvl(p_asg_txn_rec.attribute12,
l_asg_rec.ass_attribute2)
,p_ass_attribute3 => nvl(p_asg_txn_rec.attribute13,
l_asg_rec.ass_attribute3)
,p_ass_attribute4 => nvl(p_asg_txn_rec.attribute14,
l_asg_rec.ass_attribute4)
,p_ass_attribute5 => nvl(p_asg_txn_rec.attribute15,
l_asg_rec.ass_attribute5)
,p_ass_attribute6 => nvl(p_asg_txn_rec.attribute16,
l_asg_rec.ass_attribute6)
,p_ass_attribute7 => nvl(p_asg_txn_rec.attribute17,
l_asg_rec.ass_attribute7)
,p_ass_attribute8 => nvl(p_asg_txn_rec.attribute18,
l_asg_rec.ass_attribute8)
,p_ass_attribute9 => nvl(p_asg_txn_rec.attribute19,
l_asg_rec.ass_attribute9)
,p_ass_attribute10 => nvl(p_asg_txn_rec.attribute20,
l_asg_rec.ass_attribute10)
,p_ass_attribute11 => nvl(p_asg_txn_rec.attribute21,
l_asg_rec.ass_attribute11)
,p_ass_attribute12 => nvl(p_asg_txn_rec.attribute22,
l_asg_rec.ass_attribute12)
,p_ass_attribute13 => nvl(p_asg_txn_rec.attribute23,
l_asg_rec.ass_attribute13)
,p_ass_attribute14 => nvl(p_asg_txn_rec.attribute24,
l_asg_rec.ass_attribute14)
,p_ass_attribute15 => nvl(p_asg_txn_rec.attribute25,
l_asg_rec.ass_attribute15)
,p_ass_attribute16 => nvl(p_asg_txn_rec.attribute26,
l_asg_rec.ass_attribute16)
,p_ass_attribute17 => nvl(p_asg_txn_rec.attribute27,
l_asg_rec.ass_attribute17)
,p_ass_attribute18 => nvl(p_asg_txn_rec.attribute28,
l_asg_rec.ass_attribute18)
,p_ass_attribute19 => nvl(p_asg_txn_rec.attribute29,
l_asg_rec.ass_attribute19)
,p_ass_attribute20 => nvl(p_asg_txn_rec.attribute30,
l_asg_rec.ass_attribute20)
,p_ass_attribute21 => nvl(p_asg_txn_rec.attribute31,
l_asg_rec.ass_attribute21)
,p_ass_attribute22 => nvl(p_asg_txn_rec.attribute32,
l_asg_rec.ass_attribute22)
,p_ass_attribute23 => nvl(p_asg_txn_rec.attribute33,
l_asg_rec.ass_attribute23)
,p_ass_attribute24 => nvl(p_asg_txn_rec.attribute34,
l_asg_rec.ass_attribute24)
,p_ass_attribute25 => nvl(p_asg_txn_rec.attribute35,
l_asg_rec.ass_attribute25)
,p_ass_attribute26 => nvl(p_asg_txn_rec.attribute36,
l_asg_rec.ass_attribute26)
,p_ass_attribute27 => nvl(p_asg_txn_rec.attribute37,
l_asg_rec.ass_attribute27)
,p_ass_attribute28 => nvl(p_asg_txn_rec.attribute38,
l_asg_rec.ass_attribute28)
,p_ass_attribute29 => nvl(p_asg_txn_rec.attribute39,
l_asg_rec.ass_attribute29)
,p_ass_attribute30 => nvl(p_asg_txn_rec.attribute40,
l_asg_rec.ass_attribute30)
,p_concatenated_segments => l_concat_segments
,p_soft_coding_keyflex_id => l_scl_id
,p_comment_id => l_comment_id
,p_effective_start_date => l_asg_rec.effective_start_date
,p_effective_end_date => l_asg_rec.effective_end_date
,p_no_managers_warning => l_no_mgr_warning
,p_other_manager_warning => l_othr_mgr_warning);
l_update_done := true;
l_datetrack_mode := hr_api.g_update;
l_datetrack_mode := get_update_mode(
p_assignment_id => l_assignment_id
,p_ovn => l_asg_rec.object_version_number
,p_effective_date => l_effective_date
);
l_update_position_id := null;
l_update_position_id := nvl(p_asg_txn_rec.attribute6,
l_asg_rec.position_id);
hr_assignment_api.update_emp_asg_criteria(
p_validate => false
,p_effective_date => l_effective_date
,p_datetrack_update_mode => l_datetrack_mode
,p_assignment_id => l_assignment_id
,p_object_version_number => l_asg_rec.object_version_number
,p_grade_id => nvl(p_asg_txn_rec.attribute7,
l_asg_rec.grade_id)
,p_position_id => l_update_position_id
,p_job_id => nvl(p_asg_txn_rec.attribute5,
l_asg_rec.job_id)
,p_special_ceiling_step_id => l_asg_rec.special_ceiling_step_id
,p_group_name => l_group_name
,p_effective_start_date => l_asg_rec.effective_start_date
,p_effective_end_date => l_asg_rec.effective_end_date
,p_people_group_id => l_people_group_id
,p_org_now_no_manager_warning => l_no_mgr_warning
,p_other_manager_warning => l_othr_mgr_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entry_chg_warning
,p_tax_district_changed_warning => l_tax_dist_changed);
l_update_done := true;
if p_group_per_in_ler_id is not null and l_update_done then
--
update ben_cwb_person_info
set new_assgn_ovn = l_asg_rec.object_version_number
where group_per_in_ler_id = p_group_per_in_ler_id;