[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
delete pay_action_interlocks
where locking_action_id in (
select assignment_action_id
from pay_assignment_actions
where payroll_action_id = p_payroll_action_id);
select business_group_id,
start_date,
effective_date,
legislative_parameters
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
'select distinct per.person_id
from per_all_people_f per,
pay_payroll_actions ppa
where ppa.payroll_action_id = :payroll_action_id
and ppa.business_group_id + 0 = per.business_group_id
order by per.person_id';
select /* Removed the hint as per Bug# 4767118 */
paa.assignment_id,
paa.assignment_action_id
from (
select /* Removed the hint as per Bug# 4767118 */
distinct asg.assignment_id
from per_periods_of_service pds,
per_all_assignments_f asg
where pds.person_id
between p_start_person_id and p_end_person_id
and pds.business_group_id + 0 = p_business_group_id
and asg.period_of_service_id = pds.period_of_service_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.consolidation_set_id = p_consolidation_set_id or p_consolidation_set_id is null)
and ppa.effective_date
between nvl(p_start_date, ppa.effective_date) and p_effective_date
and (ppa.payroll_id = p_payroll_id or p_payroll_id is null)
and ppa.action_type in ('R', 'Q', 'B', 'I')
and not exists(
select /*+ ORDERED
USE_NL(PAAA PPAA)
INDEX(XPAI PAY_ACTION_INTERLOCKS_FK2)
INDEX(XPAA PAY_ASSIGNMENT_ACTIONS_PK)
INDEX(XPPA PAY_PAYROLL_ACTIONS_PK) */
null
from pay_action_interlocks xpai,
pay_assignment_actions xpaa,
pay_payroll_actions xppa
where xpai.locked_action_id = paa.assignment_action_id
and xpaa.assignment_action_id = xpai.locking_action_id
and xppa.payroll_action_id = xpaa.payroll_action_id
and xppa.action_type = 'X'
and xppa.report_type = 'PRT'
and xppa.report_qualifier = 'JP')
and not exists(
select null
from pay_action_information pai
where (pai.action_information_category='JP_PRE_TAX_1')
and pai.action_context_type='AAP'
and pai.action_information1=paa.assignment_action_id
and pai.assignment_id=paa.assignment_id)
order by paa.assignment_id
for update of paa.assignment_action_id nowait;
select pay_assignment_actions_s.nextval
into l_locking_action_id
from dual;
select locked_action_id
from pay_action_interlocks
where locking_action_id = p_assignment_action_id;
select 'Y'
from dual
where not exists(
select null
from pay_assignment_actions
where payroll_action_id = p_payroll_action_id
and action_status <> 'C');