[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y'
from dual
where exists(
select NULL
from pay_message_lines pml
where pml.source_id = p_source_id
and pml.source_type = p_source_type);
select 'N'
from dual
where exists(
select 1
from pay_run_results prr,
pay_element_entries_f pee,
pay_assignment_actions paa
where pee.creator_id = p_creator_id
and paa.assignment_id = pee.assignment_id
and paa.assignment_action_id = p_creator_id
and pee.creator_type = 'R'
and prr.source_id(+) = pee.element_entry_id
--
-- Necessary to specify source_type because source_type of
-- reversal assignment action means source run_result_id.
--
and prr.source_type(+) = 'E'
and nvl(prr.status,'U') = 'U'
and rownum =1);
select 'N'
from dual
where exists(
select 1
from pay_run_results prr,
pay_element_entries_f pee,
pay_assignment_actions paa
where pee.creator_id = p_creator_id
and paa.assignment_id = pee.assignment_id
and paa.assignment_action_id = p_creator_id
and (pee.creator_type = 'RR' or pee.creator_type = 'EE')
and prr.source_id(+) = pee.element_entry_id
--
-- Necessary to specify source_type because source_type of
-- reversal assignment action means source run_result_id.
--
and prr.source_type(+) = 'E'
and nvl(prr.status,'U') = 'U'
and rownum =1);
SELECT NVL(org.org_information4, 'N')
INTO l_use_advanced_retropay
FROM pay_assignment_actions paa,
hr_organization_information org,
per_all_assignments asg
WHERE paa.assignment_action_id = p_creator_id
AND paa.assignment_id = asg.assignment_id
AND org.organization_id = asg.business_group_id
AND org.org_information_context LIKE 'JP_BUSINESS_GROUP_INFO';
select prr.status
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr
where prr.source_id = p_element_entry_id
--
-- Necessary to specify source_type because source_type of
-- reversal assignment action means source run_result_id.
--
and prr.source_type = 'E'
and paa.assignment_action_id = prr.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date
between p_effective_start_date and p_effective_end_date
and not exists(
select NULL
from pay_run_results prr2
where prr2.source_id = prr.run_result_id
and prr2.source_type = 'R')
order by decode(prr.status,'U',1,2);
select paa.assignment_action_id,
paa.action_status,
paa.object_version_number,
ppa.payroll_action_id,
ppa.action_type,
ppa.effective_date
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locked_action_id = p_locked_action_id
and paa.assignment_action_id = pai.locking_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type = p_locking_action_type;
select paa.assignment_action_id,
paa.action_status,
paa.object_version_number,
ppa.payroll_action_id,
ppa.action_type,
ppa.effective_date
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locked_action_id = p_locked_action_id
and paa.assignment_action_id = pai.locking_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('P','U');
select /*+ ORDERED
INDEX(PAA PAY_ASSIGNMENT_ACTIONS_FK2)
INDEX(PPA PAY_PAYROLL_ACTIONS_PK) */
paa.assignment_action_id,
paa.action_status,
paa.object_version_number,
ppa.payroll_action_id,
ppa.action_type,
ppa.effective_date
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.pre_payment_id = p_locked_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and not exists(
select /*+ ORDERED
INDEX(PAI PAY_ACTION_INTERLOCKS_FK2)
INDEX(PAA2 PAY_ASSIGNMENT_ACTIONS_PK)
INDEX(PPA2 PAY_PAYROLL_ACTIONS_PK) */
NULL
from pay_action_interlocks pai,
pay_assignment_actions paa2,
pay_payroll_actions ppa2
where pai.locked_action_id = paa.assignment_action_id
and paa2.assignment_action_id = pai.locking_action_id
and ppa2.payroll_action_id = paa2.payroll_action_id
and ppa2.action_type = 'D');
/* This select statement returns only 1 row. */
select decode(count(*),0,'U','C'),
/* When not locked, that means the following statement returns no rows,
max(decode(paa.action_status,'C',NULL,'E',2,1)) returns NULL. */
decode(max(decode(paa.action_status,'C',NULL,'E',2,1)),NULL,'C',1,'I','E')
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locked_action_id = p_locked_action_id
and paa.assignment_action_id = pai.locking_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type = p_locking_action_type;
/* This select statement returns only 1 row. */
select decode(count(decode(ppa2.payroll_action_id,NULL,paa.assignment_action_id,NULL)),count(distinct ppp.pre_payment_id),'C',0,'U','I'),
decode(max(decode(decode(ppa2.payroll_action_id,NULL,paa.action_status,NULL),'C',NULL,NULL,NULL,'E',2,1)),NULL,'C',1,'I','E')
from pay_payroll_actions ppa2,
pay_assignment_actions paa2,
pay_action_interlocks pai,
pay_assignment_actions paa,
pay_pre_payments ppp
where ppp.assignment_action_id = p_locked_action_id
and paa.pre_payment_id(+) = ppp.pre_payment_id
/* "H"(Cheque) action can be locked by "D"(Void) only once. */
and pai.locked_action_id(+) = paa.assignment_action_id
and paa2.assignment_action_id(+) = pai.locking_action_id
and ppa2.payroll_action_id(+) = paa2.payroll_action_id
and ppa2.action_type(+) = 'D';
select nvl( nvl( min(decode(greatest(least(p_effective_date,paa.effective_end_date), paa.effective_start_date),p_effective_date,p_effective_date)),
max(decode(greatest(paa.effective_end_date,p_effective_date),p_effective_date,paa.effective_end_date))),
min(decode(least(p_effective_date, paa.effective_start_date),p_effective_date, paa.effective_start_date)) ) EFFECTIVE_DATE
from per_all_assignments_f paa
where to_number(to_char(p_effective_date, 'YYYY'))
between to_number(to_char(paa.effective_start_date, 'YYYY'))
and to_number(to_char(paa.effective_end_date, 'YYYY'))
and paa.assignment_id = p_assignment_id;
select paa.object_version_number
from pay_assignment_actions paa
where paa.assignment_action_id=p_assignment_action_id
for update;
-- If record not found, issue error "Record is deleted".
--
if csr_obj%NOTFOUND then
close csr_obj;
fnd_message.set_name('FND','FORM_RECORD_DELETED');
select count(*)
into l_count
from pay_assignment_actions
where payroll_action_id = p_payroll_action_id
and rownum <= 2;
select pay_payroll_actions_s.nextval PAYROLL_ACTION_ID,
ppa.business_group_id,
ppa.effective_date,
ppa.date_earned,
ppa.payroll_id,
ppa.consolidation_set_id,
ppa.time_period_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = p_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id;
-- Insert "Reversal" payroll action.
--
insert into pay_payroll_actions(
PAYROLL_ACTION_ID,
ACTION_TYPE,
BUSINESS_GROUP_ID,
EFFECTIVE_DATE,
DATE_EARNED,
PAYROLL_ID,
CONSOLIDATION_SET_ID,
TIME_PERIOD_ID,
ACTION_POPULATION_STATUS,
ACTION_STATUS,
OBJECT_VERSION_NUMBER)
values( l_rec.payroll_action_id,
'V',
l_rec.business_group_id,
l_rec.effective_date,
l_rec.date_earned,
l_rec.payroll_id,
l_rec.consolidation_set_id,
l_rec.time_period_id,
'U',
'U',
1);