[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pbg.security_group_id, pbg.legislation_code
from per_business_groups_perf pbg
where pbg.business_group_id = (select distinct asg.business_group_id from
per_all_assignments_f asg
where asg.assignment_id = p_assignment_id);
Procedure check_non_updateable_args(p_rec in per_asg_shd.g_rec_type
,p_effective_date in date) is
--
l_proc varchar2(72) := g_package||'check_non_updateable_args';
end check_non_updateable_args;
Procedure delete_validate
(p_rec in per_asg_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,
p_org_now_no_manager_warning out nocopy boolean,
p_loc_change_tax_issues OUT nocopy boolean,
p_delete_asg_budgets OUT nocopy boolean,
p_element_salary_warning OUT nocopy boolean,
p_element_entries_warning OUT nocopy boolean,
p_spp_warning OUT nocopy boolean,
p_cost_warning OUT nocopy boolean,
p_life_events_exists OUT nocopy boolean,
p_cobra_coverage_elements OUT nocopy boolean,
p_assgt_term_elements OUT nocopy boolean,
---
p_new_prim_ass_id OUT nocopy number,
p_prim_change_flag OUT nocopy varchar2,
p_new_end_date OUT nocopy date,
p_new_primary_flag OUT nocopy varchar2,
p_s_pay_id OUT nocopy number,
p_cancel_atd OUT nocopy date,
p_cancel_lspd OUT nocopy date,
p_reterm_atd OUT nocopy date,
p_reterm_lspd OUT nocopy date,
---
p_appl_asg_new_end_date OUT nocopy date ) is
--
l_proc varchar2(72);
l_delete_asg_budgets boolean;
l_proc:= g_package||'delete_validate';
hr_utility.set_location('Selected assignment is of type Benifits', 10);
hr_utility.set_location('Before calling pre_delete checks ', 20);
if (p_datetrack_mode <> 'DELETE') THEN
hr_assignment_internal.pre_delete
(p_rec => p_rec,
p_effective_date => p_effective_date,
p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date,
p_org_now_no_manager_warning => p_org_now_no_manager_warning,
p_loc_change_tax_issues => l_loc_change_tax_issues,
p_delete_asg_budgets => l_delete_asg_budgets,
p_element_salary_warning => l_element_salary_warning,
p_element_entries_warning => l_element_entries_warning,
p_spp_warning => l_spp_warning,
P_cost_warning => l_cost_warning,
p_life_events_exists => l_life_events_exists,
p_cobra_coverage_elements => l_cobra_coverage_elements,
p_assgt_term_elements => l_assgt_term_elements,
---
p_new_prim_ass_id => p_new_prim_ass_id,
p_prim_change_flag => p_prim_change_flag,
p_new_end_date => p_new_end_date,
p_new_primary_flag => p_new_primary_flag,
p_s_pay_id => p_s_pay_id,
p_cancel_atd => p_cancel_atd,
p_cancel_lspd => p_cancel_lspd,
p_reterm_atd => p_reterm_atd,
p_reterm_lspd => p_reterm_lspd,
---
p_appl_asg_new_end_date => p_appl_asg_new_end_date );
per_asg_bus1.dt_delete_validate
(p_assignment_id => p_rec.assignment_id
,p_datetrack_mode => p_datetrack_mode
,p_validation_start_date => p_validation_start_date
,p_validation_end_date => p_validation_end_date
);
,p_payroll_id_updated => l_temp_flag
,p_object_version_number => p_rec.object_version_number
);
per_pqh_shr.per_asg_bus('DELETE_VALIDATE',
p_rec,
p_effective_date,
p_validation_start_date,
p_validation_end_date,
p_datetrack_mode);
End delete_validate;
Procedure dt_delete_validate
(p_assignment_id in number,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) Is
--
CURSOR c_business_group_legislation IS -- cursor added for 6917434
SELECT pbg.legislation_code
FROM per_business_groups pbg, per_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND paf.business_group_id = pbg.business_group_id;
l_proc varchar2(72) := g_package||'dt_delete_validate';
If (p_datetrack_mode = 'DELETE' or
p_datetrack_mode = 'ZAP') then
--
--
-- Ensure the arguments are not null
--
hr_utility.set_location(l_proc,30);
End dt_delete_validate;
Procedure dt_update_validate
(p_payroll_id in number default hr_api.g_number,
p_person_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) := g_package||'dt_update_validate';
End dt_update_validate;
Procedure insert_validate
(p_rec in out nocopy per_asg_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,
p_validate_df_flex in boolean,
p_other_manager_warning out nocopy boolean,
p_hourly_salaried_warning out nocopy boolean,
p_inv_pos_grade_warning out nocopy boolean
) is
l_proc varchar2(72);
l_proc := g_package||'insert_validate';
select source_type into l_source_type from per_all_assignments_f
where person_id = p_rec.person_id and BUSINESS_GROUP_ID = p_rec.business_group_id
and ASSIGNMENT_TYPE = 'A' and VACANCY_ID = p_rec.vacancy_id and
p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
SELECT paf.source_type into l_source_type
FROM per_all_assignments_f paf
,PER_VAC_LINKED_ASSIGNMENTS ivla
WHERE paf.assignment_type = 'A'
AND trunc (SYSDATE) BETWEEN effective_start_date
AND effective_end_date
AND ivla.tgt_apl_asg_id = paf.assignment_id
AND ivla.tgt_offer_asg_id = p_rec.assignment_id;
,p_payroll_id_updated => l_temp_flag
,p_object_version_number => p_rec.object_version_number
);
per_pqh_shr.per_asg_bus('INSERT_VALIDATE',
p_rec,
p_effective_date,
p_validation_start_date,
p_validation_end_date,
p_datetrack_mode);
pqh_psf_bus.per_asg_bus_insert_validate
(p_position_id => p_rec.position_id
,p_assignment_date => p_effective_date
,p_assignment_grade_id => p_rec.grade_id
,p_assignment_emp_cat => p_rec.employment_category
);
End insert_validate;
Procedure update_validate
(p_rec in out nocopy per_asg_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,
p_payroll_id_updated out nocopy boolean,
p_other_manager_warning out nocopy boolean,
p_hourly_salaried_warning out nocopy boolean,
p_no_managers_warning out nocopy boolean,
p_org_now_no_manager_warning out nocopy boolean,
p_inv_pos_grade_warning out nocopy boolean
) is
--
l_proc varchar2(72);
l_payroll_id_updated boolean;
l_proc := g_package||'update_validate';
per_asg_bus1.check_non_updateable_args
(p_rec => p_rec
,p_effective_date => p_effective_date
);
,p_payroll_id_updated => l_payroll_id_updated
,p_object_version_number => p_rec.object_version_number
);
per_asg_bus1.dt_update_validate
(p_payroll_id => p_rec.payroll_id,
p_person_id => p_rec.person_id,
p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date);
p_payroll_id_updated := l_payroll_id_updated;
per_pqh_shr.per_asg_bus('UPDATE_VALIDATE',
p_rec,
p_effective_date,
p_validation_start_date,
p_validation_end_date,
p_datetrack_mode);
pqh_psf_bus.per_asg_bus_update_validate
(p_position_id => p_rec.position_id
,p_assignment_id => p_rec.assignment_id
,p_assignment_date => p_rec.effective_start_date
,p_assignment_grade_id => p_rec.grade_id
,p_assignment_emp_cat => p_rec.employment_category
);
End update_validate;
select business_group_id
from per_applications
where application_id = p_application_id
and date_received = p_validation_start_date;
select business_group_id
from per_applications
where application_id = p_application_id
and p_validation_start_date
between date_received
and nvl(date_end,hr_api.g_eot);
else -- inserted an employee assignment
--
-- Check that application is null
--
If p_application_id is not null then
--
hr_utility.set_message(801, 'HR_51211_ASG_INV_E_ASG_APL_ID');
select decode(p_assignment_type,'E',employee_number,'C',npw_number)
from per_all_people_f --#3663845 per_people_f
where person_id = p_person_id
and p_effective_date between effective_start_date
and effective_end_date;
select ast.per_system_status
, ast.business_group_id
from per_assignment_status_types ast
where ast.assignment_status_type_id = p_assignment_status_type_id;
select legislation_code, active_flag, business_group_id, per_system_status
from per_assignment_status_types
where assignment_status_type_id = p_rec.assignment_status_type_id;
select legislation_code
from per_business_groups_perf
where business_group_id = p_rec.business_group_id;
select active_flag, per_system_status
from per_ass_status_type_amends
where assignment_status_type_id = p_rec.assignment_status_type_id
and business_group_id = p_rec.business_group_id;
select per_system_status
from per_assignment_status_types
where assignment_status_type_id =
per_asg_shd.g_old_rec.assignment_status_type_id;
select nvl(min(asg.effective_start_date), hr_api.g_eot)
from per_all_assignments_f asg
,per_assignment_status_types ast
where asg.assignment_id = p_rec.assignment_id
and ast.assignment_status_type_id = asg.assignment_status_type_id
and ast.per_system_status = 'ACTIVE_ASSIGN';
select min(effective_start_date)
from per_all_assignments_f
where assignment_id = p_rec.assignment_id
and assignment_type = 'A';
select ast.per_system_status
from per_all_assignments_f asg
,per_assignment_status_types ast
where asg.assignment_id = p_rec.assignment_id
and asg.effective_start_date = c_effective_start_date
and ast.assignment_status_type_id = asg.assignment_status_type_id;
or p_rec.program_update_date <>
per_asg_shd.g_old_rec.program_update_date
or p_rec.ass_attribute_category <>
per_asg_shd.g_old_rec.ass_attribute_category
)
then
--
hr_utility.set_message(801, 'HR_7946_ASG_INV_TERM_ASS_UPD');
else -- Inserting Employee assignment
--
-- Check that the per_system_status is 'ACTIVE_ASSIGN'
--
if l_per_system_status <> 'ACTIVE_ASSIGN'
then
--
hr_utility.set_message(801, 'HR_7941_ASG_INV_STAT_NOT_ACT');
ELSE -- Inserting
--
if g_debug then
hr_utility.set_location(l_proc, 190);
select pet.system_person_type
from per_people_f per,
per_person_types pet
where per.person_id = p_person_id
and per.person_type_id = pet.person_type_id
and p_validation_start_date
between effective_start_date
and effective_end_date;
select null
from sys.dual
where exists(select null
from per_all_assignments_f pas
, per_periods_of_service ppos
where pas.effective_start_date <= p_effective_date
and ppos.period_of_service_id = pas.period_of_service_id
and pas.person_id = p_person_id
and pas.primary_flag = 'N'
and (exists(select null
from per_all_assignments_f pas2
, per_periods_of_service ppos2
where pas2.effective_end_date =
nvl(ppos2.actual_termination_date, hr_api.g_eot)
and pas.assignment_id = pas2.assignment_id
and pas2.period_of_service_id = ppos2.period_of_service_id ))); -- Added this last filter for Bug 4300591.
if p_datetrack_mode = 'DELETE' and p_primary_flag = 'Y' then
open csr_chk_contig_ass;
select min(effective_start_date)
from per_all_assignments_f
where assignment_id = p_assignment_id;
select null
from gl_code_combinations
where code_combination_id = p_default_code_comb_id
and enabled_flag = 'Y'
and p_validation_start_date
between nvl(start_date_active,hr_api.g_sot)
and nvl(end_date_active,hr_api.g_eot);
select null
from per_all_assignments_f
where assignment_id = p_assignment_id
and effective_start_date >= p_effective_date
and primary_flag = 'Y';
if p_datetrack_mode = 'DELETE' and
p_primary_flag = 'N' then
open csr_chk_fut_prim;
select business_group_id
from per_grades
where grade_id = p_grade_id
and p_validation_start_date
between date_from and nvl(date_to, hr_api.g_eot);
select business_group_id
from per_jobs_v
where job_id = p_job_id
and p_validation_start_date
between date_from
and nvl(date_to, hr_api.g_eot);
select job.business_group_id
from per_jobs job
, per_job_groups jgr
where job.job_id = p_job_id
and (p_validation_start_date
between job.date_from
and nvl(job.date_to, hr_api.g_eot))
and job.job_group_id = jgr.job_group_id
and jgr.internal_name = 'HR_'||jgr.business_group_id
and (jgr.business_group_id = job.business_group_id
or jgr.business_group_id is null);
select null
from per_valid_grades
where job_id = p_job_id
and p_validation_start_date
between date_from
and nvl(date_to, hr_api.g_eot);
select null
from per_valid_grades
where job_id = p_job_id
and grade_id = p_grade_id
and p_validation_start_date
between date_from
and nvl(date_to, hr_api.g_eot);
select assignment_type
from per_all_assignments_f
where assignment_id = p_assignment_id
and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
select inactive_date
from hr_locations_all
where location_id = p_location_id
and (business_group_id= (
select distinct business_group_id
from per_all_assignments_f
where assignment_id= p_assignment_id)
or business_group_id is null);
select inactive_date
from hr_locations_all
where location_id = p_location_id
and (business_group_id= nvl(p_business_group_id,business_group_id) --Modified for bug 11063182 nvl(hr_general.get_business_group_id,business_group_id)
or business_group_id is null);
select business_group_id
from hr_all_organization_units
where organization_id = p_organization_id
and internal_external_flag = 'INT';
select null
from per_organization_units
where organization_id = p_organization_id;
select pay.pay_basis
from per_pay_bases pay
where pay.pay_basis_id = p_pay_basis_id;
select pbg.legislation_code
from per_business_groups_perf pbg
where pbg.business_group_id = (select distinct asg.business_group_id from
per_all_assignments_f asg
where asg.assignment_id = p_assignment_id);
select 'x'
from per_position_extra_info
where position_id= p_position_id
and information_type = 'PER_OVERLAP';
/* select 'x'
from per_position_extra_info
where p_assignment_start_date
between fnd_date.canonical_to_date(poei_information3)
and fnd_date.canonical_to_date(poei_information4)
-- Start changes for bug 10220040
and p_assignment_end_date
between fnd_date.canonical_to_date(poei_information3)
and fnd_date.canonical_to_date(poei_information4)
-- End changes for bug 10220040
and position_id= p_position_id -- l_position_id -- for bug 7129787
and information_type = 'PER_OVERLAP'; */
select
fnd_date.canonical_to_date(poei_information3) effective_start_date,
nvl(fnd_date.canonical_to_date(poei_information4), to_date('31/12/4712','dd/mm/rrrr')) 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 p_assignment_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'))
order by 1,2 desc;
select position_type
from hr_all_positions_f
where position_id = p_position_id;
select
paaf.assignment_number,
paaf.effective_start_date,
paaf.effective_end_date
from per_all_assignments_f paaf, per_assignment_status_types past
where paaf.assignment_status_type_id = past.assignment_status_type_id
and paaf.assignment_type in ('E','C') -- Added For Bug#13860076
--and paaf.assignment_type = 'E' -- Commented For Bug#13860076
and past.per_system_status <> 'TERM_ASSIGN'
and paaf.position_id = p_position_id
and paaf.effective_end_date > lp_effective_end_date
and paaf.assignment_id <> nvl(p_assignment_id,-1);
SELECT ASSIGNMENT_TYPE
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_assignment_start_date between effective_start_date and effective_end_date;
select position_type, availability_status_id, business_group_id
from hr_all_positions_f
where position_id = p_position_id
and p_effective_date between effective_start_date and effective_end_date;
select 'x'
from per_all_assignments_f asg, per_assignment_status_types ast
where position_id = p_position_id
and assignment_id <> nvl(p_except_assignment_id, -1)
and assignment_type in ('E','C') -- For Bug#13860076, commented below statement and added this condition
--and ( assignment_type = 'E' /*or assignment_type = 'A'*/) -- change for the bug 5854568 (6332332)
-- Start changes for bug 10220040
--and p_effective_date between effective_start_date and effective_end_date
and p_effective_date < effective_end_date
-- End changes for bug 10220040
and asg.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status <> 'TERM_ASSIGN';