[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select asg.business_group_id,
asg.payroll_id,
ptp.time_period_id
into l_assignment_rec.business_group_id,
l_assignment_rec.payroll_id,
l_assignment_rec.time_period_id
from per_all_assignments_f asg,
per_time_periods ptp
where asg.assignment_id = p_assignment_id
and p_effective_date
between asg.effective_start_date and asg.effective_end_date
and ptp.payroll_id(+) = asg.payroll_id
and p_effective_date
between ptp.start_date(+) and ptp.end_date(+);
select def.defined_balance_id,
dim.dimension_level,
pay_core_utils.get_parameter('DATE_TYPE', dim.description),
dim.period_type,
dim.start_date_code
into l_defined_balance_rec.defined_balance_id,
l_defined_balance_rec.dimension_level,
l_defined_balance_rec.date_type,
l_defined_balance_rec.period_type,
l_defined_balance_rec.start_date_code
from pay_defined_balances def,
pay_balance_dimensions dim
where def.balance_type_id = p_balance_type_id
and nvl(def.business_group_id, p_business_group_id) = p_business_group_id
and nvl(def.legislation_code, l_legislation_code) = l_legislation_code
and dim.balance_dimension_id = def.balance_dimension_id
and dim.dimension_name = p_dimension_name;
select def.balance_type_id,
dim.dimension_name,
def.business_group_id,
dim.dimension_level,
pay_core_utils.get_parameter('DATE_TYPE', dim.description),
dim.period_type,
dim.start_date_code
into l_defined_balance_rec.balance_type_id,
l_defined_balance_rec.dimension_name,
l_defined_balance_rec.business_group_id,
l_defined_balance_rec.dimension_level,
l_defined_balance_rec.date_type,
l_defined_balance_rec.period_type,
l_defined_balance_rec.start_date_code
from pay_defined_balances def,
pay_balance_dimensions dim
where def.defined_balance_id = p_defined_balance_id
and dim.balance_dimension_id = def.balance_dimension_id;
select /*+ ORDERED USE_NL(PPA) */
to_number(substr(
max(
to_char(ppa.date_earned, 'YYYYMMDD') ||
to_char(paa.action_sequence, 'FM099999999999999') ||
to_char(paa.assignment_action_id)
)
, 24))
into l_assignment_action_id
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id = p_assignment_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.date_earned
between nvl(p_start_date, ppa.date_earned) and p_effective_date
and ppa.action_type in ('R', 'Q', 'B', 'I', 'V');
select /*+ ORDERED USE_NL(PPA) */
to_number(substr(
max(
to_char(paa.action_sequence, 'FM099999999999999') ||
to_char(paa.assignment_action_id)
)
, 16))
into l_assignment_action_id
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id = p_assignment_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date
between nvl(p_start_date, ppa.effective_date) and p_effective_date
and ppa.action_type in ('R', 'Q', 'B', 'I', 'V');
select /*+ ORDERED USE_NL(PAA PPA) NO_EXPAND */
to_number(substr(
max(
to_char(ppa.date_earned, 'YYYYMMDD') ||
to_char(paa.action_sequence, 'FM099999999999999') ||
to_char(paa.assignment_action_id)
)
, 24))
into l_assignment_action_id
from (
select asg2.assignment_id
from per_all_assignments_f asg,
per_all_assignments_f asg2
where asg.assignment_id = p_assignment_id
and p_effective_date
between asg.effective_start_date and asg.effective_end_date
and asg2.person_id = asg.person_id
group by asg2.assignment_id
) v,
pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id = v.assignment_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.date_earned
between nvl(p_start_date, ppa.date_earned) and p_effective_date
and ppa.action_type in ('R', 'Q', 'B', 'I', 'V');
select /*+ ORDERED USE_NL(PAA PPA) NO_EXPAND */
to_number(substr(
max(
to_char(paa.action_sequence, 'FM099999999999999') ||
to_char(paa.assignment_action_id)
)
, 16))
into l_assignment_action_id
from (
select asg2.assignment_id
from per_all_assignments_f asg,
per_all_assignments_f asg2
where asg.assignment_id = p_assignment_id
and p_effective_date
between asg.effective_start_date and asg.effective_end_date
and asg2.person_id = asg.person_id
group by asg2.assignment_id
) v,
pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id = v.assignment_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date
between nvl(p_start_date, ppa.effective_date) and p_effective_date
and ppa.action_type in ('R', 'Q', 'B', 'I', 'V');
select /*+ ORDERED USE_NL(PPA) */
to_number(substr(
max(
to_char(paa.action_sequence, 'FM099999999999999') ||
to_char(paa.assignment_action_id)
)
, 16))
into l_assignment_action_id
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id = p_assignment_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.time_period_id = l_assignment_rec.time_period_id
and ppa.effective_date <= p_effective_date
and ppa.action_type in ('R', 'Q', 'B', 'I', 'V');
select /*+ ORDERED USE_NL(PAA PPA) */
to_number(substr(
max(
to_char(paa.action_sequence, 'FM099999999999999') ||
to_char(paa.assignment_action_id)
)
, 16))
into l_assignment_action_id
from (
select asg2.assignment_id
from per_all_assignments_f asg,
per_all_assignments_f asg2
where asg.assignment_id = p_assignment_id
and p_effective_date
between asg.effective_start_date and asg.effective_end_date
and asg2.person_id = asg.person_id
group by asg2.assignment_id
) v,
pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id = v.assignment_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.time_period_id = l_assignment_rec.time_period_id
and ppa.effective_date <= p_effective_date
and ppa.action_type in ('R', 'Q', 'B', 'I', 'V');