[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cursor csr_payment is select sum(result_value)
from pay_element_types_v1
where classification_name in ('Normal Income','Allowances',
'Direct Payments','Lump Sum Amounts')
and p_assignment_action_id = assignment_action_id;
cursor csr_deduct is select sum(result_value)
from pay_element_types_v1
where classification_name in ('Statutory Deductions', 'Deductions',
'Involuntary Deductions','Voluntary Deductions')
and p_assignment_action_id = assignment_action_id;
SELECT
defined_balance_id
FROM
pay_defined_balances PDB,
pay_balance_dimensions PBD,
pay_balance_types PBT
WHERE PBT.balance_name = p_balance_type
AND nvl(PBT.legislation_code, l_legislation_code) = l_legislation_code -- Bug 4377803; added nvl function
SELECT
substr( nvl(pbtl.REPORTING_NAME, nvl(pbt.REPORTING_NAME,pbt.balance_name)),1,50)
FROM
pay_balance_types PBT,
pay_balance_types_tl PBTl
WHERE PBT.balance_name = p_balance_type
AND nvl(PBT.legislation_code, l_legislation_code) = l_legislation_code -- Bug 4377803; added nvl function
select /*+ ORDERED USE_NL(paa,ppa,rpaa,rppa) */
to_char(nvl(rppa.date_earned,rppa.effective_date),'YYYY/MM/DD'),
rpaa.payroll_action_id,
rpaa.assignment_action_id,
paa.assignment_action_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_assignment_actions rpaa,
pay_payroll_actions rppa
where paa.payroll_action_id = ppa.payroll_action_id
and rppa.payroll_action_id = rpaa.payroll_action_id
and paa.assignment_id = rpaa.assignment_id
and paa.assignment_action_id =
(select
to_number(substr(max(to_char(pa.effective_date,'J')||lpad(aa.assignment_action_id,15,'0')),8))
from pay_payroll_actions pa,
pay_assignment_actions aa
where pa.action_type in ('U','P')
and aa.action_status = 'C'
and pa.payroll_action_id = aa.payroll_action_id
and aa.assignment_id = p_assignment_id
and pa.effective_date <= p_session_date)
and ppa.action_type in ('P', 'U')
and rpaa.assignment_id = p_assignment_id
and rpaa.action_sequence =
(select max(aa.action_sequence)
from pay_assignment_actions aa,
pay_action_interlocks loc
where loc.locked_action_id = aa.assignment_action_id
and loc.locking_action_id = paa.assignment_action_id);
SELECT /*+ USE_NL(paa, ppa) */
fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE
paa.assignment_id = p_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND (paa.source_action_id is not null
or ppa.action_type in ('I','V','B'))
AND ppa.effective_date <= p_session_date
AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B', 'U', 'P');
select to_char(nvl(ppa.date_earned,ppa.effective_date),'YYYY/MM/DD'),
paa.payroll_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = p_assig_act_id
and ppa.payroll_action_id = paa.payroll_action_id;
select pact.action_type
from pay_assignment_actions assact,
pay_payroll_actions pact
where assact.assignment_action_id = p_assignment_action_id
and pact.payroll_action_id = assact.payroll_action_id;
select assact.assignment_action_id
from pay_assignment_actions assact,
pay_action_interlocks loc
where loc.locking_action_id = p_assignment_action_id
and assact.assignment_action_id = loc.locked_action_id
order by assact.action_sequence desc;
select pact.action_type , assact.assignment_id
from pay_assignment_actions assact,
pay_payroll_actions pact
where assact.assignment_action_id = p_assignment_action_id
and pact.payroll_action_id = assact.payroll_action_id
;
select assact.assignment_action_id
from pay_assignment_actions assact,
pay_action_interlocks loc
where loc.locking_action_id = p_assignment_action_id
and assact.assignment_action_id = loc.locked_action_id
order by assact.action_sequence desc
;
select assact.assignment_action_id
from pay_assignment_actions assact,
pay_payroll_actions pact,
pay_action_interlocks loc
where loc.locked_action_id = p_assignment_action_id
and assact.assignment_action_id = loc.locking_action_id
and pact.payroll_action_id = assact.payroll_action_id
and pact.action_type in ('P','U') /* prepayments only */
order by assact.action_sequence desc
;
select pact.payroll_action_id,
to_char(nvl(pact.date_earned,pact.effective_date),'YYYY/MM/DD')
from pay_assignment_actions assact,
pay_payroll_actions pact
where assact.assignment_action_id = p_run_assignment_action_id
and pact.payroll_action_id = assact.payroll_action_id
;
select pad.address_line1,
pad.address_line2,
pad.address_line3,
l.meaning,
pad.postal_code,
pad.region_3,
pad.town_or_city
from per_addresses pad,
hr_lookups l
where pad.person_id = p_person_id
and pad.primary_flag = 'Y'
and l.lookup_type(+) = 'ZA_PROVINCE'
and l.lookup_code(+) = pad.region_1
and sysdate between nvl(pad.date_from, sysdate)
and nvl(pad.date_to, sysdate);