[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select null
from per_person_type_usages_f ptu,
per_person_types ppt
where ptu.person_id = p_person_id
and p_effective_date
between ptu.effective_start_date
and ptu.effective_end_date
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type = decode(p_contact_type,
'S','SRVNG_SPS',
'D','SRVNG_DP',
'R','SRVNG_DPFM',
'SRVNG_FMLY_MMBR');
select person_type_id
into l_pet_id
from per_person_types
where business_group_id = p_business_group_id
and system_person_type = l_type
and default_flag = 'Y' ; -- Bug 3878962
select system_person_type ,business_group_id
from per_person_types
where person_type_id = lc_person_type_id;
select person_type_usage_id
from per_person_type_usages_f
where person_type_id in ( select person_type_id
from per_person_types
where system_person_type = l_person_type
and business_group_id = l_business_group_id )
and person_id = p_person_id
and ((effective_start_date <= p_effective_date and
effective_end_date >= p_effective_date) or
(effective_start_date >= p_effective_date));
l_dtupdate_mode varchar2(200);
select bgp.business_group_id,
bgp.legislation_code,
pet.system_person_type
from per_business_groups bgp,
per_all_people_f per,
per_person_types pet
where per.business_group_id = bgp.business_group_id
and per.person_type_id = pet.person_type_id
and per.person_id = c_person_id
and c_effective_date between per.effective_start_date -- Bug No 4451864 Removed -1 from the c_effective_date
and per.effective_end_date;
select ctr.contact_person_id, ctr.contact_type
from per_contact_relationships ctr
where ctr.person_id = c_person_id
and c_eff_date
between nvl(ctr.date_start,hr_api.g_sot) and nvl(ctr.date_end,hr_api.g_eot)
and ctr.contact_type = c_contact_type
order by ctr.sequence_number;
select distinct ctr.contact_person_id
from per_contact_relationships ctr
where exists
(select null
from per_person_type_usages_f ptu,
per_person_types pet
where ctr.contact_person_id = ptu.person_id
and ptu.person_type_id = pet.person_type_id
and pet.system_person_type = 'DPNT'
and c_eff_date between ptu.effective_start_date and ptu.effective_end_date)
and ctr.person_id = c_person_id
and ctr.personal_flag = 'Y'
and c_eff_date between nvl(ctr.date_start,hr_api.g_sot) and nvl(ctr.date_end,hr_api.g_eot)
order by ctr.contact_person_id;
select ctr.contact_person_id, ctr.contact_type
from per_contact_relationships ctr
where ctr.person_id = c_person_id
and ctr.contact_person_id = nvl(c_contact_person_id, ctr.contact_person_id)
and ctr.personal_flag = 'Y'
and c_eff_date
between nvl(ctr.date_start,hr_api.g_sot) and nvl(ctr.date_end,hr_api.g_eot)
order by ctr.sequence_number;
select ctr.contact_person_id, ctr.contact_type
from per_contact_relationships ctr
where ctr.person_id = c_person_id
and ctr.contact_person_id = nvl(c_contact_person_id, ctr.contact_person_id)
and ctr.personal_flag = 'Y'
order by ctr.sequence_number;
select asg.assignment_id,
asg.object_version_number,
asg.effective_start_date
from per_all_assignments_f asg
where asg.person_id = c_person_id
and asg.assignment_type = 'B';
select null
from per_all_assignments_f asg
where asg.person_id = c_person_id
and asg.assignment_type = 'B'
and c_eff_date
between asg.effective_start_date and asg.effective_end_date;
select 'Y'
from hr_positions_f hp
, per_shared_types ps
where hp.position_id = c_position_id
and c_eff_date
between hp.date_effective
and nvl(hp.date_end, hr_api.g_eot)
and ps.shared_type_id = hp.availability_status_id
and ps.system_type_cd = 'ACTIVE' ;
select *
from per_all_assignments_f asg
where asg.person_id = c_person_id
and asg.assignment_type = 'E'
and asg.primary_flag = 'Y'
and c_eff_date
between asg.effective_start_date and asg.effective_end_date;
select *
from per_all_assignments_f asg
where asg.person_id = c_person_id
and asg.primary_flag = 'Y'
and c_eff_date
between asg.effective_start_date and asg.effective_end_date;
select asg.object_version_number,
asg.effective_start_date
from per_all_assignments_f asg
where asg.assignment_id = c_assignment_id
and c_eff_date
between asg.effective_start_date and asg.effective_end_date;
select null
from per_all_assignments_f asg
where asg.assignment_id = c_assignment_id
and asg.object_version_number = c_ovn;
select aei.aei_information4,
aei.aei_information5
from per_assignment_extra_info aei
where aei.assignment_id = c_assignment_id
and aei.information_type = 'BEN_DERIVED';
select *
from per_pay_proposals
where pay_proposal_id = c_pyp_id;
select pet.person_type_id
from per_person_types pet
where pet.business_group_id = c_bgp_id
and pet.system_person_type = c_pet_spt
and pet.default_flag = 'Y';
select pet.system_person_type
from per_person_type_usages_f ptu, per_person_types pet
where ptu.person_type_id = pet.person_type_id
and ptu.person_id = c_per_id
---- added to test a particular type avaialble # 2852514
and pet.system_person_type = nvl(c_type_cd,pet.system_person_type )
and c_eff_date
between ptu.effective_start_date and ptu.effective_end_date;
select business_group_id
from per_all_people_f
where person_id = p_person_id;
select per.marital_status
from per_all_people_f per
where per.person_id = p_person_id
and p_per_esd - 1
between per.effective_start_date and per.effective_end_date;
select 'x'
from ben_elig_cvrd_dpnt_f pdp,
ben_prtt_enrt_rslt_f pen
where pen.person_id = p_person_id
and pen.prtt_enrt_rslt_id = pdp.prtt_enrt_rslt_id
and pdp.dpnt_person_id = c_contact_person_id
and c_eff_date between
pen.effective_start_date and pen.effective_end_date
and c_eff_date between
pdp.effective_start_date and pdp.effective_end_date ;
select substr(hoi.ORG_INFORMATION3,1)
from hr_organization_information hoi
where hoi.org_information_context = 'Benefits Defaults'
and hoi.organization_id = p_business_group_id
;
select ctr.rltd_per_rsds_w_dsgntr_flag,
ctr.contact_relationship_id,
ctr.object_version_number
from per_contact_relationships ctr
where ctr.person_id = c_per_id
and ctr.contact_person_id = c_conper_id
and p_benasg_effdate between ctr.date_start and nvl(ctr.date_end,p_benasg_effdate);
select *
from per_addresses adr
where adr.person_id = c_per_id
and adr.primary_flag = 'Y'
and p_benasg_effdate between adr.date_from and nvl(adr.date_to,p_benasg_effdate)
order by adr.date_from desc ;
hr_contact_rel_api.update_contact_relationship (p_effective_date => p_benasg_effdate
,p_contact_relationship_id => l_ctr_id
,p_rltd_per_rsds_w_dsgntr_flag => 'N'
,p_object_version_number => l_ctr_ovn
);
select asg.*
from per_all_assignments_f asg
where asg.person_id = p_person_id
and asg.assignment_type = 'B'
and p_effective_date
between asg.effective_start_date and asg.effective_end_date;
l_update boolean;
l_update_override boolean;
l_update_change_insert boolean;
p_update => l_update,
p_update_override => l_update_override,
p_update_change_insert => l_update_change_insert);
if l_update_override then
--
l_datetrack_mode := hr_api.g_update_override;
elsif l_update then
--
l_datetrack_mode := hr_api.g_update;
ben_assignment_api.update_ben_asg
(p_validate => FALSE
,p_effective_date => p_effective_date
,p_datetrack_update_mode => l_datetrack_mode
,p_assignment_id => l_asg_rec.assignment_id
,p_object_version_number => l_object_version_number
--
,p_grade_id => p_asg_dets.grade_id
,p_position_id => p_asg_dets.position_id
,p_job_id => p_asg_dets.job_id
,p_payroll_id => p_asg_dets.payroll_id
,p_location_id => p_asg_dets.location_id
,p_special_ceiling_step_id => l_special_ceiling_step_id
,p_organization_id => p_asg_dets.organization_id
,p_people_group_id => p_asg_dets.people_group_id
,p_pay_basis_id => p_asg_dets.pay_basis_id
,p_employment_category => p_asg_dets.employment_category
--
,p_supervisor_id => p_asg_dets.supervisor_id
,p_change_reason => p_asg_dets.change_reason
,p_date_probation_end => p_asg_dets.date_probation_end
,p_default_code_comb_id => p_asg_dets.default_code_comb_id
,p_frequency => p_asg_dets.frequency
,p_internal_address_line => p_asg_dets.internal_address_line
,p_manager_flag => p_asg_dets.manager_flag
,p_normal_hours => p_asg_dets.normal_hours
,p_perf_review_period => p_asg_dets.perf_review_period
,p_perf_review_period_frequency => p_asg_dets.perf_review_period_frequency
,p_probation_period => p_asg_dets.probation_period
,p_probation_unit => p_asg_dets.probation_unit
,p_sal_review_period => p_asg_dets.sal_review_period
,p_sal_review_period_frequency => p_asg_dets.sal_review_period_frequency
,p_set_of_books_id => null
,p_source_type => p_asg_dets.source_type
,p_time_normal_finish => p_asg_dets.time_normal_finish
,p_time_normal_start => p_asg_dets.time_normal_start
,p_bargaining_unit_code => p_asg_dets.bargaining_unit_code
,p_labour_union_member_flag => p_asg_dets.labour_union_member_flag
,p_hourly_salaried_code => p_asg_dets.hourly_salaried_code
,p_ass_attribute_category => p_asg_dets.ass_attribute_category
,p_ass_attribute1 => p_asg_dets.ass_attribute1
,p_ass_attribute2 => p_asg_dets.ass_attribute2
,p_ass_attribute3 => p_asg_dets.ass_attribute3
,p_ass_attribute4 => p_asg_dets.ass_attribute4
,p_ass_attribute5 => p_asg_dets.ass_attribute5
,p_ass_attribute6 => p_asg_dets.ass_attribute6
,p_ass_attribute7 => p_asg_dets.ass_attribute7
,p_ass_attribute8 => p_asg_dets.ass_attribute8
,p_ass_attribute9 => p_asg_dets.ass_attribute9
,p_ass_attribute10 => p_asg_dets.ass_attribute10
,p_ass_attribute11 => p_asg_dets.ass_attribute11
,p_ass_attribute12 => p_asg_dets.ass_attribute12
,p_ass_attribute13 => p_asg_dets.ass_attribute13
,p_ass_attribute14 => p_asg_dets.ass_attribute14
,p_ass_attribute15 => p_asg_dets.ass_attribute15
,p_ass_attribute16 => p_asg_dets.ass_attribute16
,p_ass_attribute17 => p_asg_dets.ass_attribute17
,p_ass_attribute18 => p_asg_dets.ass_attribute18
,p_ass_attribute19 => p_asg_dets.ass_attribute19
,p_ass_attribute20 => p_asg_dets.ass_attribute20
,p_ass_attribute21 => p_asg_dets.ass_attribute21
,p_ass_attribute22 => p_asg_dets.ass_attribute22
,p_ass_attribute23 => p_asg_dets.ass_attribute23
,p_ass_attribute24 => p_asg_dets.ass_attribute24
,p_ass_attribute25 => p_asg_dets.ass_attribute25
,p_ass_attribute26 => p_asg_dets.ass_attribute26
,p_ass_attribute27 => p_asg_dets.ass_attribute27
,p_ass_attribute28 => p_asg_dets.ass_attribute28
,p_ass_attribute29 => p_asg_dets.ass_attribute29
,p_ass_attribute30 => p_asg_dets.ass_attribute30
,p_title => p_asg_dets.title
,p_age => l_age
,p_adjusted_service_date => l_adj_serv_date
,p_original_hire_date => l_orig_hire_date
,p_salary => l_salary
,p_termination_date => l_termn_date
,p_termination_reason => l_termn_reason
,p_leave_of_absence_date => l_abs_date
,p_absence_type => l_abs_type
,p_absence_reason => l_abs_reason
,p_date_of_hire => l_date_of_hire
--
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
select per.date_of_birth,
pds.adjusted_svc_date,
per.original_date_of_hire,
pds.actual_termination_date,
pds.leaving_reason,
pds.date_start
from per_all_people_f per,
per_periods_of_service pds
where per.person_id = c_person_id
and per.person_id = pds.person_id
and c_eff_date
between per.effective_start_date and per.effective_end_date
and pds.date_start = (select max(date_start) from per_periods_of_service
pps where pps.person_id = c_person_id) ;
select paa.date_start,
paa.absence_attendance_type_id,
paa.abs_attendance_reason_id
from per_absence_attendances paa
where paa.person_id = c_person_id
and c_eff_date
between nvl(paa.date_start,c_eff_date) and nvl(paa.date_end,c_eff_date);
select pyp.proposed_salary_n
from per_all_assignments_f asg,
per_pay_proposals pyp
where asg.assignment_id = pyp.assignment_id
and c_eff_date
between asg.effective_start_date and asg.effective_end_date
and asg.person_id = c_person_id
and asg.primary_flag = 'Y'
and asg.assignment_type = 'E'
and pyp.approved = 'Y'
and nvl(pyp.change_date,hr_api.g_sot) <= c_eff_date
order by pyp.change_date desc;