[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select business_group_id
from per_pay_bases
where pay_basis_id = p_pay_basis_id;
select nvl(max(change_date),p_validation_start_date)
from per_pay_proposals
where assignment_id = p_assignment_id;
,p_payroll_id_updated out nocopy boolean
,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
)
is
--
l_proc varchar2(72) := g_package||'chk_payroll_id';
select address_line1
from per_addresses
where person_id = p_person_id
and primary_flag='Y'
and ( (style='US' and region_1 is not null)
or style<>'US');
select date_of_birth
from per_people_f
where person_id = p_person_id
and p_effective_date between effective_start_date
and effective_end_date;
l_payroll_id_updated boolean;
,p_payroll_id_updated => l_payroll_id_updated
,p_object_version_number => p_object_version_number
);
p_payroll_id_updated:=l_payroll_id_updated;
,p_payroll_id_updated out nocopy boolean
,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
)
is
--
l_legislation_code per_business_groups.legislation_code%TYPE;
select legislation_code
from per_business_groups_perf
where business_group_id = p_business_group_id;
select null
from sys.dual
where exists(select null
from pay_all_payrolls_f pp
where p_effective_date
between pp.effective_start_date
and pp.effective_end_date
and pp.payroll_id = p_payroll_id);
select business_group_id
from pay_all_payrolls_f
where payroll_id = p_payroll_id
and p_effective_date between effective_start_date
and effective_end_date;
select ppm.personal_payment_method_id
,ppm.org_payment_method_id
,min(ppm.effective_start_date)
,max(ppm.effective_end_date)
from pay_personal_payment_methods_f ppm
where ppm.assignment_id = p_assignment_id
and ppm.effective_start_date <= p_validation_end_date
and ppm.effective_end_date >= p_validation_start_date
group by ppm.personal_payment_method_id
,ppm.org_payment_method_id;
select opu.org_pay_method_usage_id
,min(opu.effective_start_date)
,max(opu.effective_end_date)
from pay_org_pay_method_usages_f opu
where opu.org_payment_method_id = c_org_payment_method_id
and opu.payroll_id = p_payroll_id
and opu.effective_start_date <= c_effective_end_date
and opu.effective_end_date >= c_effective_start_date
group by opu.org_pay_method_usage_id
order by 2;
select null
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and asg.payroll_id <> p_payroll_id
and asg.effective_start_date <= p_validation_end_date
and asg.effective_end_date >= p_validation_start_date;
select null
from pay_assignment_actions asa
,pay_payroll_actions pra
,per_all_assignments_f paf
where asa.assignment_id = p_assignment_id
and pra.payroll_action_id = asa.payroll_action_id
--
-- Fix for bug 3693830 starts here.
--
and paf.assignment_id = p_assignment_id
and nvl(paf.payroll_id,-1) <> nvl(p_payroll_id,-1)
and paf.effective_end_date >= p_validation_start_date
and paf.effective_start_date <= p_validation_end_date
--
-- Fix for bug 3693830 ends here.
--
-- Start of Bug fix: 2185300.
--
and pra.action_type not in ('X','BEE') -- Fix for bug# 2711532
and ((pra.effective_date
between p_validation_start_date
and p_validation_end_date )
--updated for bug 8450873
/*or (nvl(pra.date_earned,p_validation_start_date-1) >= p_validation_start_date
and nvl(pra.date_earned,p_validation_end_date+1) <= p_validation_end_date )*/
);
select style
from per_addresses a
where a.person_id = p_person_id and a.primary_flag = 'Y'
and p_effective_date between a.date_from and nvl(a.date_to,hr_api.g_eot);
p_payroll_id_updated := FALSE;
p_payroll_id_updated := TRUE;
and (p_datetrack_mode = 'DELETE_NEXT_CHANGE'
or p_datetrack_mode = 'FUTURE_CHANGE'
or p_datetrack_mode = 'UPDATE_OVERRIDE'
or p_datetrack_mode = 'UPDATE_CHANGE_INSERT')) --added for bug 8404508
then
--
hr_utility.set_location(l_proc, 100);
or (p_datetrack_mode = 'DELETE_NEXT_CHANGE'
or p_datetrack_mode = 'FUTURE_CHANGE'
or p_datetrack_mode = 'UPDATE_OVERRIDE'
or p_datetrack_mode = 'UPDATE_CHANGE_INSERT' -- added for bug 8404508
)
then
--
hr_utility.set_location(l_proc, 220);
select enabled_flag
from pay_people_groups
where people_group_id = p_people_group_id
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_business_groups_perf pbg
,pay_people_groups ppg
where ppg.people_group_id = p_people_group_id
and pbg.people_group_structure = to_char(ppg.id_flex_num)
and pbg.business_group_id = p_business_group_id;
select business_group_id, actual_termination_date
from per_periods_of_service
where period_of_service_id = p_period_of_service_id
and p_validation_start_date
between date_start
and nvl(actual_termination_date, hr_api.g_eot);
select ppf.business_group_id
from per_people_f ppf
where ppf.person_id = p_person_id
and p_effective_date between ppf.effective_start_date
and ppf.effective_end_date;
select business_group_id, current_employee_flag, current_npw_flag
from per_all_people_f
where person_id = p_person_referred_by_id
and p_validation_start_date
between effective_start_date
and effective_end_date;
select hp.business_group_id
from hr_positions_f hp
, per_shared_types ps
where hp.position_id = p_position_id
and p_validation_start_date
between hp.effective_start_date
and hp.effective_end_date
and p_validation_start_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 null
from per_valid_grades
where position_id = p_position_id
and p_validation_start_date
between date_from
and nvl(date_to, hr_api.g_eot);
select null
from per_valid_grades
where position_id = p_position_id
and grade_id = p_grade_id
and p_validation_start_date
between date_from
and nvl(date_to, hr_api.g_eot);
select null
from hr_positions_f hp
, per_shared_types ps
where hp.position_id = p_position_id
and p_validation_start_date
between hp.effective_start_date
and hp.effective_end_date
and hp.organization_id = p_organization_id
and p_validation_start_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 null
from hr_positions_f hp
, per_shared_types ps
where hp.position_id = p_position_id
and p_validation_start_date
between hp.effective_start_date
and hp.effective_end_date
and hp.job_id = p_job_id
and p_validation_start_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 null
from per_all_assignments_f
where person_id = p_person_id
and period_of_service_id = p_period_of_service_id
and primary_flag = 'Y';
select null
from per_all_assignments_f
where person_id = p_person_id
and period_of_placement_date_start = p_pop_date_start
and primary_flag = 'Y';
SELECT business_group_id
FROM per_all_people_f
WHERE person_id = p_person_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT per_system_status
FROM per_ass_status_type_amends
WHERE assignment_status_type_id = per_asg_shd.g_old_rec.assignment_status_type_id
AND business_group_id = csr_chk_amends.p_bg_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 null
from sys.dual
where exists
(select null
from per_all_assignments_f pas
where pas.effective_start_date <= p_validation_start_date
and pas.person_id = p_person_id
and pas.period_of_service_id = p_period_of_service_id
--
-- 120.10 (START)
--
--and pas.primary_flag = 'Y');
(select null
from per_all_assignments_f pas1
where pas1.effective_start_date <= p_validation_start_date
and pas1.person_id = p_person_id
and pas1.period_of_service_id <> p_period_of_service_id
and pas1.primary_flag = 'Y'
)
);
select null
from sys.dual
where exists
(select null
from per_all_assignments_f pas
where pas.effective_start_date <= p_validation_start_date
and pas.person_id = p_person_id
and pas.period_of_placement_date_start = p_pop_date_start
and pas.primary_flag = 'Y');
select business_group_id, current_employee_flag, current_npw_flag
--from per_people_f bug 5078945
from per_all_people_f
where person_id = p_recruiter_id
and p_validation_start_date
between effective_start_date
and nvl(effective_end_date, hr_api.g_eot);
select business_group_id
from per_recruitment_activities
where recruitment_activity_id = p_recruitment_activity_id
and p_validation_start_date
between date_start
and nvl(date_end, hr_api.g_eot);
select null
from sys.dual
where exists(select null
from per_events pe
where pe.assignment_id = p_assignment_id
and (p_datetrack_mode = 'ZAP'
or (p_datetrack_mode = 'DELETE'
and date_start > p_validation_start_date))
and not exists
(select null
from irc_interview_details iid
where pe.event_id = iid.event_id)
);
select null
from sys.dual
where exists(select null
from per_letter_request_lines
where assignment_id = p_assignment_id
and (p_datetrack_mode = 'ZAP'
or (p_datetrack_mode = 'DELETE'
and date_from > p_validation_start_date)));*/
select null
from sys.dual
where exists(select null
from pay_cost_allocations_f
where assignment_id = p_assignment_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_personal_payment_methods_f
where assignment_id = p_assignment_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_assignment_actions aa
, pay_payroll_actions pa
where aa.assignment_id = p_assignment_id
and pa.payroll_action_id = aa.payroll_action_id
and (p_datetrack_mode = 'ZAP'
or (p_datetrack_mode = 'DELETE'
and pa.effective_date > p_validation_start_date))
and pa.action_type not in ('X','BEE')); -- Fix for bug# 2711532
select null
from sys.dual
where exists(select null
from per_secondary_ass_statuses
where assignment_id = p_assignment_id
and (p_datetrack_mode = 'ZAP'
or (p_datetrack_mode = 'DELETE'
and start_date > p_validation_start_date)));
select null
from sys.dual
where exists(select null
from per_cobra_cov_enrollments
where assignment_id = p_assignment_id
and (p_datetrack_mode = 'ZAP'
or ((p_datetrack_mode = 'DELETE'
and coverage_start_date is null)
or (coverage_start_date > p_validation_start_date))));
select null
from sys.dual
where exists(select null
from per_cobra_coverage_benefits_f b
, per_cobra_cov_enrollments e
where e.assignment_id = p_assignment_id
and e.cobra_coverage_enrollment_id =
b.cobra_coverage_enrollment_id
and (p_datetrack_mode = 'ZAP'
or (p_datetrack_mode = 'DELETE'
and b.effective_start_date > p_validation_start_date)));
select null
from sys.dual
where exists(select null
from per_assignment_extra_info
where assignment_id = p_assignment_id);
select null
from sys.dual
where exists(select null
from hr_assignment_set_amendments
where assignment_id = p_assignment_id);
select null
from gl_sets_of_books
where set_of_books_id = p_set_of_books_id;
select null
from financials_system_params_all
where set_of_books_id = p_set_of_books_id
and business_group_id = p_business_group_id;
select null
from hr_soft_coding_keyflex
where soft_coding_keyflex_id = p_soft_coding_keyflex_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 legislation_code
from per_business_groups_perf
where business_group_id = p_business_group_id;
select null
from pay_legislation_rules
where legislation_code = l_legislation_code
and rule_type = 'TAX_UNIT'
and rule_mode = 'Y';
select 1 from fnd_new_messages
where message_name = p_message_name
and application_id = 801;
select business_group_id
from per_organization_units
where organization_id = p_source_organization_id
and p_validation_start_date
between date_from
and nvl(date_to, hr_api.g_eot);
else -- inserting an employee assignment
--
hr_utility.set_message(801, 'HR_51219_ASG_INV_EASG_I_SORG');
select type
from per_recruitment_activities
where recruitment_activity_id = p_recruitment_activity_id;
select 1
from sys.dual
where exists
(select null
from per_spinal_point_steps_f psps
where psps.effective_start_date <= p_validation_start_date
and psps.step_id = p_special_ceiling_step_id
and (exists
(select null
from per_spinal_point_steps_f psps2
where psps2.effective_end_date >= p_validation_end_date
and psps2.step_id = p_special_ceiling_step_id
and psps2.grade_spine_id = psps.grade_spine_id)));
select pgs.business_group_id
from per_grade_spines_f pgs
where pgs.ceiling_step_id = p_special_ceiling_step_id
and p_effective_date between pgs.effective_start_date
and pgs.effective_end_date;
select psps.sequence
from per_grade_spines_f pgs,
per_spinal_point_steps_f psps
where psps.step_id = p_special_ceiling_step_id
and pgs.grade_id = p_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 1
from sys.dual
where exists(select null
from per_spinal_point_placements_f pspp
, per_spinal_point_steps_f psps
, per_grade_spines_f pgs
where pspp.assignment_id = p_assignment_id
and pspp.step_id = psps.step_id
and psps.grade_spine_id=pgs.grade_spine_id
and pgs.grade_id = p_grade_id
and psps.sequence > l_sequence
and pspp.effective_start_date <= p_validation_end_date
and pspp.effective_end_date >= p_validation_start_date
and psps.effective_start_date between psps.effective_start_date
and psps.effective_end_date
and psps.effective_start_date between pgs.effective_start_date
and pgs.effective_end_date);
select party_id
from per_all_people_f
where person_id = p_per_id
and p_validation_start_date
between effective_start_date
and effective_end_date;
select assignment_type
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and p_validation_start_date
between asg.effective_start_date
and asg.effective_end_date;
select business_group_id, current_employee_flag, current_npw_flag
from per_all_people_f
where person_id = p_supervisor_id
and p_validation_start_date
between effective_start_date
and effective_end_date;
select business_group_id, current_employee_flag, current_npw_flag
from per_all_people_f
where person_id = p_supervisor_id;
select status into l_inst_type from fnd_product_installations
where application_id = 800;
select paaf.assignment_type
from per_all_assignments_f paaf
where paaf.person_id = p_supervisor_id
and p_supervisor_id is not null
and paaf.assignment_id = p_supervisor_assignment_id
and p_validation_start_date between
paaf.effective_start_date and paaf.effective_end_date;
if p_datetrack_mode in ('UPDATE_OVERRIDE',
'ZAP',
'FUTURE_CHANGE',
'DELETE_NEXT_CHANGE') then
--
-- Get current value for system_person_type (i.e. as of the
-- effective date)
--
-- ER FPT
if hrempter.g_fpt_rev_ter = 'N'
or (nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'N') then
per_per_bus.chk_system_pers_type
(p_person_id => p_person_id
,p_validation_start_date => p_validation_start_date
,p_validation_end_date => p_validation_end_date
,p_datetrack_mode => p_datetrack_mode
,p_effective_date => p_effective_date
);
select null
from per_all_assignments_f pas
, per_assignment_status_types past
where pas.assignment_id = p_assignment_id
and pas.effective_start_date >= p_validation_start_date
and past.assignment_status_type_id = pas.assignment_status_type_id
and past.per_system_status = 'TERM_ASSIGN';
if p_datetrack_mode in ('UPDATE_OVERRIDE'
,'FUTURE_CHANGE'
,'DELETE_NEXT_CHANGE') then
open csr_chk_term_status;
,p_datetrack_mode => 'INSERT'
);
select paf.assignment_id
from per_all_assignments_f paf
,per_applications pa
,per_vacancies pv
where paf.application_id = pa.application_id
and pa.date_end is null
and paf.vacancy_id = pv.vacancy_id
and paf.person_id = p_person_id
and paf.vacancy_id = p_vacancy_id
and paf.assignment_type = p_assignment_type
and paf.assignment_id <> nvl(p_assignment_id,0)
and (p_validation_start_date between paf.effective_start_date and paf.effective_end_date
or
paf.effective_end_date >= p_validation_start_date);
select paf.assignment_id
from per_all_assignments_f paf
,per_applications pa
,per_vacancies pv
where paf.application_id = pa.application_id
and pa.date_end is null
and paf.vacancy_id = pv.vacancy_id
and paf.person_id = p_person_id
and paf.vacancy_id = p_vacancy_id
and paf.assignment_type = p_assignment_type
and paf.assignment_id <> nvl(p_assignment_id,0)
and (p_validation_start_date between paf.effective_start_date and paf.effective_end_date
or
p_validation_end_date between paf.effective_start_date and paf.effective_end_date);
select pa.application_id
from
per_applications pa,
per_all_assignments_f paf,
per_vacancies pv
where
paf.person_id = p_person_id
and paf.vacancy_id = pv.vacancy_id
and paf.vacancy_id = p_vacancy_id
and paf.application_id = pa.application_id
and paf.business_group_id = p_business_group_id
and p_effective_date between paf.effective_start_date
and paf.effective_end_date
and pa.date_end is null;
or p_assignment_type is null) and p_datetrack_mode not in ('ZAP','DELETE')
then
-- Start changes for bug 8687386
--
/* open csr_dup_apl_vacancy;
select business_group_id
from per_vacancies
where vacancy_id = p_vacancy_id
and p_validation_start_date
between date_from
and nvl(date_to, hr_api.g_eot);
select business_group_id
from per_vacancies
where vacancy_id = p_vacancy_id;
else -- inserting a non employee
--
hr_utility.set_message(801, 'HR_51221_ASG_INV_EASG_I_VAC');
select nvl(max(assignment_sequence),0) +1
from per_all_assignments_f
where person_id = p_person_id
and assignment_type = p_assignment_type;
select null
from per_all_assignments_f pas
where pas.organization_id = p_organization_id
and pas.assignment_type = 'E'
and pas.manager_flag = 'Y'
and pas.assignment_id <> l_assignment_id
and p_effective_date between pas.effective_start_date
and pas.effective_end_date;
else -- inserting a non applicant
--
hr_utility.set_message(800, 'HR_289620_APPLICANT_RANK_ASG');
select posting_content_id
from irc_posting_contents
where posting_content_id = p_posting_content_id
and rownum = 1;
else -- inserting a non applicant
--
hr_utility.set_message(800, 'HR_289619_POSTING_CONTENT_ASG');