[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select null
from hr_assignment_sets
where upper(assignment_set_name) = upper(p_assignment_set_name)
and business_group_id = p_business_group_id;
select formula_type_id
into l_formula_type_id
from ff_formula_types
where formula_type_name = 'Assignment Set';
select hr_assignment_sets_s.nextval
into p_assignment_set_id
from dual;
hr_assignment_sets_pkg.insert_row(
p_rowid => l_rowid,
p_assignment_set_id => p_assignment_set_id,
p_business_group_id => p_business_group_id,
p_payroll_id => p_payroll_id,
p_assignment_set_name => p_assignment_set_name,
p_formula_id => null);
hr_assignment_set_amds_pkg.insert_row(
p_rowid => l_rowid,
p_assignment_id => p_assignment_id,
p_assignment_set_id => p_assignment_set_id,
p_include_or_exclude => p_include_or_exclude);
select 'Y'
from dual
where exists(
select null
from hr_assignment_set_amendments
where assignment_set_id = p_assignment_set_id
and include_or_exclude = p_include_or_exclude);
g_include_or_excludes.delete;
select formula_id
into g_formula_id
from hr_assignment_sets
where assignment_set_id = p_assignment_set_id;
select assignment_id,
include_or_exclude
bulk collect into
l_assignment_ids,
l_include_or_excludes
from hr_assignment_set_amendments
where assignment_set_id = p_assignment_set_id;
select rowid,
effective_date
into l_rowid,
l_effective_date
from fnd_sessions
where session_id = userenv('sessionid');
update fnd_sessions
set effective_date = p_effective_date
where rowid = l_rowid;
insert into fnd_sessions(
session_id,
effective_date)
values( userenv('sessionid'),
p_effective_date);
select formula_id
from hr_assignment_sets
where assignment_set_id = p_assignment_set_id;
select 'Y'
from hr_assignment_set_amendments
where assignment_set_id = p_assignment_set_id
and include_or_exclude = 'I'
and rownum < 2;
select /*+ ORDERED */
asg3.assignment_id,
greatest(asg3.effective_start_date, p_start_date) effective_date,
asg3.assignment_number,
per.full_name
from (
select /*+ ORDERED
USE_NL(ASG1, PPOS, ASG2)
INDEX(ASG1 PER_ASSIGNMENTS_F_N7)
INDEX(PPOS PER_PERIODS_OF_SERVICE_PK)
INDEX(ASG2 PER_ASSIGNMENTS_F_PK) */
asg2.assignment_id,
min(asg2.effective_start_date) effective_start_date
from per_all_assignments_f asg1,
per_periods_of_service ppos,
per_all_assignments_f asg2
where asg1.payroll_id = p_payroll_id
and p_effective_date
between asg1.effective_start_date and asg1.effective_end_date
and ppos.period_of_service_id = asg1.period_of_service_id
and p_effective_date
between ppos.date_start and nvl(ppos.final_process_date,p_effective_date)
and asg2.assignment_id = asg1.assignment_id
and asg2.effective_end_date >= p_start_date
and asg2.effective_start_date <= p_end_date
and asg2.payroll_id +0 = asg1.payroll_id
group by asg2.assignment_id
) v,
per_all_assignments_f asg3,
per_all_people_f per
--
-- Assignment information must be retrieved by Batch Line Upload Date.
--
where asg3.assignment_id = v.assignment_id
and asg3.effective_start_date = v.effective_start_date
and per.person_id = asg3.person_id
--
-- Person information must be retrieved by Batch Line Upload Date.
--
and greatest(asg3.effective_start_date, p_start_date)
between per.effective_start_date and per.effective_end_date
order by nvl(per.order_name, per.full_name), asg3.assignment_number;
select /*+ ORDERED */
asg3.assignment_id,
greatest(asg3.effective_start_date, p_start_date) effective_date,
asg3.assignment_number,
per.full_name
from (
select /*+ ORDERED */
asg2.assignment_id,
min(asg2.effective_start_date) effective_start_date
from hr_assignment_set_amendments asa,
per_all_assignments_f asg1,
per_periods_of_service ppos,
per_all_assignments_f asg2
where asa.assignment_set_id = p_assignment_set_id
and asa.include_or_exclude = 'I'
and asg1.assignment_id = asa.assignment_id
and p_effective_date
between asg1.effective_start_date and asg1.effective_end_date
and ppos.period_of_service_id = asg1.period_of_service_id
and p_effective_date
between ppos.date_start and nvl(ppos.final_process_date,p_effective_date)
and asg1.payroll_id + 0 = p_payroll_id
and asg2.assignment_id = asg1.assignment_id
and asg2.effective_end_date >= p_start_date
and asg2.effective_start_date <= p_end_date
and asg2.payroll_id + 0 = asg1.payroll_id
group by asg2.assignment_id
) v,
per_all_assignments_f asg3,
per_all_people_f per
where asg3.assignment_id = v.assignment_id
and asg3.effective_start_date = v.effective_start_date
and per.person_id = asg3.person_id
and greatest(asg3.effective_start_date, p_start_date)
between per.effective_start_date and per.effective_end_date
order by nvl(per.order_name, per.full_name), asg3.assignment_number;
select /*+ ORDERED */
asg3.assignment_id,
greatest(asg3.effective_start_date, p_start_date) effective_date,
asg3.assignment_number,
per.full_name,
v.include_or_exclude
from (
select /*+ ORDERED
USE_NL(ASG1, PPOS, ASG2, ASA)
INDEX(ASG1 PER_ASSIGNMENTS_F_N7)
INDEX(PPOS PER_PERIODS_OF_SERVICE_PK)
INDEX(ASG2 PER_ASSIGNMENTS_F_PK)
INDEX(ASA HR_ASSIGNMENT_SET_AMENDMEN_PK) */
asg2.assignment_id,
min(asg2.effective_start_date) effective_start_date,
min(asa.include_or_exclude) include_or_exclude
from per_all_assignments_f asg1,
per_periods_of_service ppos,
per_all_assignments_f asg2,
hr_assignment_set_amendments asa
where asg1.payroll_id = p_payroll_id
and p_effective_date
between asg1.effective_start_date and asg1.effective_end_date
and ppos.period_of_service_id = asg1.period_of_service_id
and p_effective_date
between ppos.date_start and nvl(ppos.final_process_date,p_effective_date)
and asg2.assignment_id = asg1.assignment_id
and asg2.effective_end_date >= p_start_date
and asg2.effective_start_date <= p_end_date
and asg2.payroll_id + 0 = asg1.payroll_id
and asa.assignment_set_id(+) = p_assignment_set_id
and asa.assignment_id(+) = asg2.assignment_id
and nvl(asa.include_or_exclude, 'I') = 'I'
group by asg2.assignment_id
) v,
per_all_assignments_f asg3,
per_all_people_f per
where asg3.assignment_id = v.assignment_id
and asg3.effective_start_date = v.effective_start_date
and per.person_id = asg3.person_id
and greatest(asg3.effective_start_date, p_start_date)
between per.effective_start_date and per.effective_end_date
order by nvl(per.order_name, per.full_name), asg3.assignment_number;