[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select inf.org_information14
from hr_organization_information inf
, hr_all_positions_f pos
where pos.position_id = p_position_id
and inf.organization_id = pos.business_group_id
and inf.org_information_context || '' = 'Business Group Information';
select count(*)
from hr_all_positions_f pos, per_shared_types sht
where pos.position_id = p_position_id
and pos.effective_start_date < p_effective_start_date
and hr_psf_shd.get_availability_status(pos.availability_status_id,p_business_group_id) ='ACTIVE';
select effective_start_date
from hr_all_positions_f pos, per_shared_types sht
where pos.position_id = p_position_id
and pos.effective_start_date > p_effective_start_date
and hr_psf_shd.get_availability_status(pos.availability_status_id,p_business_group_id) ='ACTIVE';
select min(effective_start_date)
from hr_all_positions_f pos
where effective_start_date > p_effective_end_date + 1
and hr_psf_shd.get_availability_status(pos.availability_status_id,p_business_group_id) ='ACTIVE';
if p_datetrack_mode = 'INSERT'
or p_datetrack_mode = 'CORRECTION'
or p_datetrack_mode = 'UPDATE'
or p_datetrack_mode = 'UPDATE_OVERRIDE'
or p_datetrack_mode = 'UPDATE_CHANGE_INSERT'
then
--
-- if current row's status is active and there are no active rows
-- prior to this row then date effective is to
-- be changed to the effective start date of this row.
-- no. of active rows in the database for this position prior to
-- the effective start date is computed
--
if g_debug then
hr_utility.set_location('inside for action '||p_datetrack_mode||l_proc, 30);
elsif p_datetrack_mode = 'DELETE_NEXT_CHANGE' then
if g_debug then
hr_utility.set_location('inside for action '||p_datetrack_mode||l_proc, 50);
select hr_psf_shd.get_availability_status(pos.availability_status_id,p_business_group_id)
from hr_all_positions_f pos
where pos.position_id = p_position_id
and pos.effective_start_date = p_effective_start_date;
select max(effective_start_date)
from hr_all_positions_f
where position_id = p_position_id
and effective_start_date < p_effective_start_date ;
select min(effective_start_date)
from hr_all_positions_f
where position_id = p_position_id
and effective_start_date > p_effective_start_date ;
select effective_start_date
from hr_all_positions_f
where position_id = p_position_id
and object_version_number = p_object_version_number ;
set_stat(chk_stat, 6, 'PROPOSED', 'PROPOSED', 'DELETED');
set_stat(chk_stat, 7, 'PROPOSED', 'DELETED', 'DELETED');
set_stat(chk_stat, 8, 'PROPOSED', 'DELETED', NULL);
set_stat(chk_stat, 10, 'DELETED', 'DELETED', 'DELETED');
set_stat(chk_stat, 11, 'DELETED', 'DELETED', NULL);
if p_datetrack_mode = 'INSERT' then
l_prev_stat := NULL;
elsif p_datetrack_mode = 'UPDATE' or
p_datetrack_mode = 'UPDATE_OVERRIDE' then
-- old rec values are used for previous status and future stat is
-- fetched from the database
l_prev_stat := hr_psf_shd.get_availability_status( p_old_avail_status_id,
p_business_group_id) ;
elsif p_datetrack_mode = 'UPDATE_CHANGE_INSERT' then -- 'INSERT after UPDATE' as seen on GUI
-- old rec values are used for previous status and future stat is
-- fetched from the database
l_prev_stat := hr_psf_shd.get_availability_status( p_old_avail_status_id,
p_business_group_id) ;
elsif p_datetrack_mode = 'DELETE_NEXT_CHANGE' then -- 'Next' as seen on GUI
-- current row effective start date is used to fetch next rows as
-- validation start date does not have value in this case
open csr_present(p_position_id,p_object_version_number) ;
hr_utility.set_location('deleted row esd is'||l_future_esd||l_proc,111);
select business_group_id
from per_spinal_point_steps_f psps
where psps.step_id = p_entry_step_id
and p_validation_start_date between psps.effective_start_date and psps.effective_end_date;
select psps.sequence
from per_grade_spines_f pgs,
per_spinal_point_steps_f psps
where psps.step_id = p_entry_step_id
and pgs.grade_id = p_entry_grade_id
and pgs.grade_spine_id = psps.grade_spine_id
and p_effective_date between pgs.effective_start_date
and pgs.effective_end_date
and p_effective_date between psps.effective_start_date
and psps.effective_end_date;
select business_group_id
from per_grades
where grade_id = p_entry_grade_id
and p_validation_start_date
between date_from and nvl(date_to, hr_api.g_eot);
select business_group_id
from pay_grade_rules_f
where grade_rule_id = p_entry_grade_rule_id
and p_validation_start_date
between effective_start_date and effective_end_date;
select business_group_id
from pay_payrolls_f pp
where pp.payroll_id = p_pay_freq_payroll_id and
p_validation_start_date between pp.effective_start_date and pp.effective_end_date ;
select 'x'
from hr_all_positions_f psf
where psf.position_id <> nvl(p_position_id, -1)
and psf.business_group_id = p_business_group_id
and psf.position_definition_id = p_position_definition_id
and psf.effective_start_date <= p_validation_end_date
and psf.effective_end_date >= p_validation_start_date;
select 'x'
from hr_all_positions_f psf
where psf.name = p_name
and (p_position_id is null or psf.position_id <> p_position_id)
and psf.business_group_id = p_business_group_id;
select 'x'
from fnd_common_lookups
where lookup_type = 'FREQUENCY'
and lookup_code = p_frequency
and enabled_flag = 'Y'
and p_effective_date between nvl(start_date_active,p_effective_date)
and nvl(end_date_active,p_effective_date);
select 'x'
from per_pay_bases
where pay_basis_id = p_pay_basis_id and
business_group_id = p_business_group_id;
select pbg.legislation_code
from per_business_groups pbg
, hr_positions_f pos
where pos.position_id = p_position_id
and pbg.business_group_id = pos.business_group_id
order by pos.effective_start_date;
select min(effective_start_date)
from hr_all_positions_f
where position_id = p_position_id;
l_updateable Boolean;
DE_Update_properties(
p_position_id => p_position_id,
p_effective_Start_Date => hr_psf_shd.g_old_rec.effective_start_date,
p_updateable => l_updateable,
p_lower_limit => l_ll,
p_upper_limit => l_ul);
if not l_updateable then
--
hr_utility.set_message(800,'HR_PSF_DE_NOT_UPDT_THIS_ROW');
select 'x'
from per_jobs_v job
where job.job_id = p_job_id
and job.business_group_id + 0 = p_business_group_id;
select 'x'
from per_jobs_v job
where job.job_id = p_job_id
and p_validation_date between job.date_from
and nvl(job.date_to,hr_api.g_eot);
select 'x'
from per_organization_units oru
where oru.organization_id = p_organization_id
and oru.business_group_id = p_business_group_id
and oru.internal_external_flag = 'INT';
select 'x'
from hr_organization_units oru
where oru.organization_id = p_organization_id
and p_validation_date between oru.date_from
and nvl(oru.date_to,hr_api.g_eot);
select 'x'
from hr_all_positions_f psf, per_shared_types sht
where psf.position_id = p_successor_position_id
and psf.availability_status_id = sht.shared_type_id
and (sht.business_group_id = p_business_group_id
or sht.business_group_id is null)
and sht.system_type_cd in ('ACTIVE','FROZEN')
and psf.business_group_id = p_business_group_id
and p_validation_start_date between psf.effective_start_date
and psf.effective_end_date ;
select 'x'
from hr_all_positions_f psf, per_shared_types sht
where psf.position_id = p_relief_position_id
and psf.availability_status_id = sht.shared_type_id
and (sht.business_group_id = p_business_group_id
or sht.business_group_id is null)
and sht.system_type_cd in ('ACTIVE','FROZEN')
and psf.business_group_id = p_business_group_id
and p_validation_start_date between psf.effective_start_date
and psf.effective_end_date ;
select 'x'
from hr_locations loc
where loc.location_id = p_location_id
and p_effective_date < nvl(loc.inactive_date,
hr_api.g_eot);
select 'x'
from per_position_definitions
where position_definition_id = p_position_definition_id;
select 'x'
from fnd_common_lookups
where lookup_type = 'QUALIFYING_UNITS'
and lookup_code = p_probation_period_unit_cd;
select null
from pqh_position_transactions ptx
where ptx.position_transaction_id = p_position_transaction_id;
select 'x'
from hr_all_positions_f psf, per_shared_types sht
where psf.position_id = p_supervisor_position_id
and psf.availability_status_id = sht.shared_type_id
and (sht.business_group_id = p_business_group_id
or sht.business_group_id is null)
and sht.system_type_cd in ('ACTIVE','FROZEN')
and psf.business_group_id = p_business_group_id
and p_validation_start_date between psf.effective_start_date
and psf.effective_end_date ;
select 'x'
from hr_all_positions_f psf, per_shared_types sht
where psf.position_id = p_prior_position_id
and psf.availability_status_id = sht.shared_type_id
and (sht.business_group_id = p_business_group_id
or sht.business_group_id is null)
and sht.system_type_cd in ('ACTIVE','FROZEN')
and psf.business_group_id = p_business_group_id
and p_validation_start_date between psf.effective_start_date
and psf.effective_end_date ;
select sum(poei_information6) fte
from (SELECT poei_information6, poei_information3, poei_information4, ROWNUM rn FROM per_position_extra_info
where position_id = p_position_id
and information_type= 'PER_RESERVED') PEI
where p_effective_date
between fnd_date.canonical_to_date(poei_information3)
and nvl(fnd_date.canonical_to_date(poei_information4),hr_general.end_of_time);
select p_validation_start_date start_date
from dual
union
select effective_start_date start_date
from hr_all_positions_f
where effective_start_date between p_validation_start_date
and p_validation_end_date
and position_id = p_position_id
union
select start_date
from (select fnd_date.canonical_to_date(poei_information3) start_date, ROWNUM rn -- bug 9387763
from per_position_extra_info
where position_id = p_position_id
and information_type = 'PER_RESERVED') a
where a.start_date between p_validation_start_date and p_validation_end_date;
select 'X'
from per_position_extra_info
where position_id = p_position_id
and information_type = 'PER_SEASONAL';
select 'X'
from per_position_extra_info
where position_id = p_position_id
and information_type = 'PER_OVERLAP';
select poei_information3
from per_position_extra_info
where position_id = p_position_id
and information_type = 'PER_FAMILY'
and poei_information3 in ('ACADEMIC','FACULTY');
select asg.assignment_id, asg.effective_start_date, asg.effective_end_date
from per_all_assignments_f asg, per_assignment_status_types ast
where asg.position_id = p_position_id
and (asg.effective_start_date >= p_effective_date
or p_effective_date between asg.effective_start_date and asg.effective_end_date) -- Added for Bug#14022433
and asg.assignment_type in ('E', 'C')
and asg.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status <> 'TERM_ASSIGN'
order by 2,3;
select
asg.effective_start_date,
asg.effective_end_date
from
per_all_assignments_f asg,
per_assignment_status_types ast
where asg.assignment_id <> lp_asg_id
and asg.position_id = p_position_id
and (lp_asg_start_date between asg.effective_start_date and asg.effective_end_date
or
lp_asg_end_date between asg.effective_start_date and asg.effective_end_date)
and asg.assignment_type in ('E', 'C')
and asg.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status <> 'TERM_ASSIGN';
select
fnd_date.canonical_to_date(poei_information3) effective_start_date,
fnd_date.canonical_to_date(poei_information4) effective_end_date
from (
select poei_information3, poei_information4
from per_position_extra_info
where information_type = 'PER_OVERLAP'
and position_id = p_position_id)
where lp_asg_start_date
between fnd_date.canonical_to_date(poei_information3)
and nvl(fnd_date.canonical_to_date(poei_information4), to_date('31/12/4712','dd/mm/rrrr'))
and lp_asg_end_date
between fnd_date.canonical_to_date(poei_information3)
and nvl(fnd_date.canonical_to_date(poei_information4), to_date('31/12/4712','dd/mm/rrrr'));
Procedure dt_update_validate
(p_relief_position_id in number default hr_api.g_number,
p_successor_position_id in number default hr_api.g_number,
p_supervisor_position_id in number default hr_api.g_number,
p_pay_freq_payroll_id in number default hr_api.g_number,
p_entry_grade_rule_id in number default hr_api.g_number,
p_entry_step_id in number default hr_api.g_number,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) Is
--
l_proc varchar2(72) ;
l_proc := g_package||'dt_update_validate';
End dt_update_validate;
Procedure dt_delete_validate
(p_position_id in number,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) Is
--
l_proc varchar2(72) ;
l_proc := g_package||'dt_delete_validate';
If (p_datetrack_mode = 'DELETE' or
p_datetrack_mode = 'ZAP') then
--
--
-- Ensure the arguments are not null
--
hr_api.mandatory_arg_error
(p_api_name => l_proc,
p_argument => 'validation_start_date',
p_argument_value => p_validation_start_date);
End dt_delete_validate;
select count(*)
from per_all_assignments_f
where position_id = p_position_id
and effective_start_date between p_validate_start_date and p_validate_end_date;
Procedure insert_validate
(p_rec in hr_psf_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) is
--
l_proc varchar2(72) ;
l_proc := g_package||'insert_validate';
per_pqh_shr.hr_psf_bus('INSERT_VALIDATE',p_rec
,p_effective_date
,p_validation_start_date
,p_validation_end_date
,p_datetrack_mode);
End insert_validate;
Procedure update_validate
(p_rec in hr_psf_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) is
--
l_proc varchar2(72) ;
l_proc := g_package||'update_validate';
dt_update_validate
(p_supervisor_position_id => p_rec.supervisor_position_id,
p_successor_position_id => p_rec.successor_position_id,
p_relief_position_id => p_rec.relief_position_id,
p_pay_freq_payroll_id => p_rec.pay_freq_payroll_id,
p_entry_grade_rule_id => p_rec.entry_grade_rule_id,
p_entry_step_id => p_rec.entry_step_id,
p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date);
per_pqh_shr.hr_psf_bus('UPDATE_VALIDATE',p_rec
,p_effective_date
,p_validation_start_date
,p_validation_end_date
,p_datetrack_mode);
End update_validate;
procedure delete_date_effective(p_position_id in number
,p_object_version_number in number
,p_business_group_id in number
,p_datetrack_mode in varchar2 ) is
l_proc varchar2(72) ;
select effective_start_date,effective_end_date
,hr_psf_shd.get_availability_status(availability_status_id,p_business_group_id)
from hr_all_positions_f
where position_id = p_position_id
and effective_start_date > p_effective_start_date
order by effective_start_date ;
select effective_start_date,effective_end_date
from hr_all_positions_f
where position_id = p_position_id
and object_version_number = p_object_version_number ;
select date_effective
from hr_all_positions_f
where position_id = p_position_id
for update of date_effective;
l_proc := g_package||'delete_date_effective ';
if p_datetrack_mode ='DELETE_NEXT_CHANGE' then
open current_row ;
update hr_all_positions_f
set date_effective = l_next_esd
where current of pos_all ;
PROCEDURE pre_delete_checks(p_position_id in number
,p_business_group_id in number
,p_datetrack_mode in varchar2
) is
--
l_exists varchar2(1);
l_proc := g_package||'pre_delete_checks';
select '1'
into l_exists
from sys.dual
where exists(SELECT NULL
from PER_BUDGET_ELEMENTS BE
where BE.POSITION_ID = p_position_id);
select '1'
into l_exists
from sys.dual
where exists(SELECT NULL
from PER_ALL_VACANCIES VAC
where VAC.POSITION_ID = p_position_id);
select e.pos_structure_element_id
into l_pos_structure_element_id
from per_pos_structure_elements e
where e.parent_position_id = p_position_id
and not exists (
select null
from per_pos_structure_elements e2
where e2.subordinate_position_id = p_position_id)
and 1 = (
select count(e3.pos_structure_element_id)
from per_pos_structure_elements e3
where e3.parent_position_id = p_position_id);
select '1'
into l_exists
from sys.dual
where exists(SELECT NULL
FROM PER_POS_STRUCTURE_ELEMENTS PSE
WHERE PSE.PARENT_POSITION_ID = p_position_id
OR PSE.SUBORDINATE_POSITION_ID = p_position_id) ;
select '1'
into l_exists
from sys.dual
where exists(SELECT NULL
FROM PER_VALID_GRADES VG1
WHERE business_group_id + 0 = p_business_group_id
AND VG1.POSITION_ID = p_position_id);
select '1'
into l_exists
from sys.dual
where exists(select null
from per_job_requirements jre1
where jre1.position_id = p_position_id);
select '1'
into l_exists
from sys.dual
where exists(select null
from per_job_evaluations jev1
where jev1.position_id = p_position_id);
select '1'
into l_exists
from sys.dual
where exists(select null
from hr_all_positions_f
where successor_position_id = p_position_id);
select '1'
into l_exists
from sys.dual
where exists(select null
from hr_all_positions_f
where supervisor_position_id = p_position_id);
select '1'
into l_exists
from sys.dual
where exists(select null
from per_position_extra_info
where position_id = p_position_id);
select '1'
into l_exists
from sys.dual
where exists(select null
from per_mm_positions
where new_position_id = p_position_id);
select '1'
into l_exists
from sys.dual
where exists(select null
from pqh_position_transactions
where position_id = p_position_id);
select '1'
into l_exists
from sys.dual
where exists(select null
from pqh_attribute_ranges
where position_id = p_position_id);
select '1'
into l_exists
from sys.dual
where exists(select null
from pqh_budgets
where position_id = p_position_id);
select '1'
into l_exists
from sys.dual
where exists(select null
from pqh_worksheet_details
where position_id = p_position_id);
l_sql_text := 'select null '
||' from sys.dual '
||' where exists( select null '
||' from po_system_parameters '
||' where security_position_structure_id = '
||to_char(p_position_id)
||' ) '
||' or exists( select null '
||' from po_employee_hierarchies '
||' where employee_position_id = '
||to_char(p_position_id)
||' or superior_position_id = '
||to_char(p_position_id)
||' ) '
|| ' or exists ( select null '
|| ' from po_position_controls_all '
|| ' where position_id = '
|| to_char(p_position_id)
||' ) ';
end pre_delete_checks;
Procedure delete_validate
(p_rec in hr_psf_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) is
--
l_proc varchar2(72) ;
l_proc := g_package||'delete_validate';
pre_delete_checks(p_position_id => p_rec.position_id
,p_business_group_id => p_rec.business_group_id
,p_datetrack_mode => p_datetrack_mode);
dt_delete_validate
(p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date,
p_position_id => p_rec.position_id);
/* delete_date_effective(p_position_id => p_rec.position_id
,p_object_version_number => p_rec.object_version_number
,p_business_group_id => p_rec.business_group_id
,p_datetrack_mode => p_datetrack_mode ); */
End delete_validate;
select null
from sys.dual
where exists(select null
from per_all_assignments_f
where position_id = p_position_id
and (p_datetrack_mode = 'ZAP'
or (p_datetrack_mode = 'DELETE'
and effective_start_date >= p_validation_start_date)));
select null
from sys.dual
where exists(select null
from pay_element_links_f
where position_id = p_position_id
and (p_datetrack_mode = 'ZAP'
or (p_datetrack_mode = 'DELETE'
and effective_start_date >= p_validation_start_date)));
select null
from sys.dual
where exists(select null
from pqh_budget_details
where position_id = p_position_id
and p_datetrack_mode = 'ZAP');
select min(psf.effective_start_date)
from
hr_all_positions_f psf
where psf.position_id = p_position_id
and hr_psf_shd.get_availability_status(psf.availability_status_id
,psf.business_group_id) = 'ACTIVE';
select count(*)
from
hr_all_positions_f psf
where psf.position_id = p_position_id
and hr_psf_shd.get_availability_status(psf.availability_status_id
,psf.business_group_id) <> 'PROPOSED';
select min(psf.effective_Start_Date)
from
hr_all_positions_f psf
where psf.position_id = p_position_id;
select count(*)
from
hr_all_positions_f psf
where psf.position_id = p_position_id
and hr_psf_shd.get_availability_status(psf.availability_status_id
,psf.business_group_id) <> 'PROPOSED';
select
min(psf.effective_start_date)
from
hr_all_positions_f psf
where psf.position_id = p_position_id;
select max(effective_start_date)
from hr_all_positions_f psf
where psf.position_id = p_position_id
and psf.effective_start_date < p_effective_start_date;
select max(psf.effective_start_date)
from hr_all_positions_f psf
where psf.position_id = p_position_id;
select min(effective_start_date)
from per_all_assignments_f paf
where paf.position_id = p_position_id;
select min(effective_start_date)
from hr_all_positions_f psf
where psf.position_id = p_position_id
and psf.effective_start_date > p_effective_start_date;
Procedure DE_Update_properties(
p_position_id in number,
p_effective_Start_Date in date,
p_updateable out nocopy boolean,
p_lower_limit out nocopy date,
p_upper_limit out nocopy date) is
--
l_updateable Boolean:=false;
l_updateable := first_active_position_row (p_position_id, p_effective_start_date);
if not l_updateable then
l_updateable := hr_psf_bus.all_proposed_only_position(p_position_id);
p_updateable := l_updateable;
if l_updateable then
p_lower_limit := lower_limit(p_position_id, p_effective_start_Date);
end DE_update_properties;