[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl(pay_core_utils.get_parameter('CPF_INTEREST',legislative_parameters),0)
into l_cpf_interest
from pay_payroll_actions
where payroll_action_id = c_payroll_action_id;
select nvl(pay_core_utils.get_parameter('FWL_INTEREST',legislative_parameters),0)
into l_fwl_interest
from pay_payroll_actions
where payroll_action_id = c_payroll_action_id;
select to_char(sum(decode( pai.action_information1,null,0,(decode(pai.action_information21,'WP',0,'EP',0,'SP',0,
decode(sign(to_number(pai.action_information7)),1,to_number(pai.action_information7),0))) )),'99999999'),
to_char(sum(decode( pai.action_information1,null,0,(decode(pai.action_information21,'WP',0,'EP',0,'SP',0,
decode(sign(to_number(pai.action_information6)),1,to_number(pai.action_information6),0))) )),'99999999'),
to_char(sum(decode( pai.action_information1,null,0,(decode(pai.action_information21,'WP',0,'EP',0,'SP',0,
decode(sign(to_number(pai.action_information5)),1,to_number(pai.action_information5),0))) )),'99999999'),
to_char(sum(decode( pai.action_information1,null,0,(decode(pai.action_information21,'WP',0,'EP',0,'SP',0,
decode(to_number(sign(pai.action_information4)),1,to_number(pai.action_information4),0))) )),'99999999'),
nvl(to_char(sum(decode(sign(to_number(pai.action_information14)),1,to_number(pai.action_information14),0)),'999999.99'),0),
nvl(to_char(sum(decode(sign(to_number(pai.action_information15)),1,to_number(pai.action_information15),0)),'999999.99'),0),
nvl(to_char(sum(decode(sign(to_number(pai.action_information8)),1,to_number(pai.action_information8),0)),'999999.99'),0),
nvl(to_char(sum(decode(sign(to_number(pai.action_information16)),1,to_number(pai.action_information16),0)),'999999.99'),0),
nvl(to_char(sum(decode(sign(to_number(pai.action_information9)),1,to_number(pai.action_information9),0)),'999999.99'),0),
nvl(to_char(sum(decode(sign(to_number(pai.action_information10)),1,to_number(pai.action_information10),0)),'999999.99'),0),
nvl(to_char(sum(decode(sign(to_number(pai.action_information11)),1,to_number(pai.action_information11),0)),'999999.99'),0)
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_information pai
where ppa.payroll_action_id = p_payroll_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = pai.action_context_id
and pai.action_information_category = 'SG CPF DETAILS'
and pai.action_context_type = 'AAC' ;
select nvl(hoi.org_information20,'Y')
from hr_organization_information hoi,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id
and hoi.organization_id =
to_number(pay_core_utils.get_parameter('LEGAL_ENTITY_ID',
ppa.legislative_parameters))
and hoi.org_information_context = 'SG_LEGAL_ENTITY';
select count( distinct nvl(pai.action_information1,pai.source_id) )
into l_count
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_information pai
where ppa.payroll_action_id = p_payroll_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = pai.action_context_id
and pai.action_information_category = 'SG CPF DETAILS'
and pai.action_context_type = 'AAC'
and to_number(pai.action_information8) > 0;
select count( distinct nvl(pai.action_information1,pai.source_id) )
into l_count
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_information pai
where ppa.payroll_action_id = p_payroll_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = pai.action_context_id
and pai.action_information_category = 'SG CPF DETAILS'
and pai.action_context_type = 'AAC'
and to_number(pai.action_information14) > 0;
select count( distinct nvl(pai.action_information1,pai.source_id) )
into l_count
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_information pai
where ppa.payroll_action_id = p_payroll_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = pai.action_context_id
and pai.action_information_category = 'SG CPF DETAILS'
and pai.action_context_type = 'AAC'
and to_number(pai.action_information9) > 0;
select count( distinct nvl(pai.action_information1,pai.source_id) )
into l_count
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_information pai
where ppa.payroll_action_id = p_payroll_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = pai.action_context_id
and pai.action_information_category = 'SG CPF DETAILS'
and pai.action_context_type = 'AAC'
and to_number(pai.action_information10) > 0;
select count( distinct nvl(pai.action_information1,pai.source_id) )
into l_count
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_information pai
where ppa.payroll_action_id = p_payroll_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = pai.action_context_id
and pai.action_information_category = 'SG CPF DETAILS'
and pai.action_context_type = 'AAC'
and to_number(pai.action_information11) > 0;
select nvl(pai.action_information1,pai.source_id) cpf_acc_number,
pai.action_information17,
pai.action_information18,
pai.action_information21,
pai.action_information22,
paa.assignment_id,
paa.assignment_action_id,
pai.tax_unit_id,
fnd_date.canonical_to_date(pai.action_information20),
pai.action_information19
from pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_action_information pai
where ppa.payroll_action_id = p_payroll_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = pai.action_context_id
and pai.action_information_category = 'SG CPF DETAILS'
and pai.action_context_type = 'AAC'
and pai.action_information2 = 'EE'
and exists ( select 1
from pay_action_information pai_dup,
pay_assignment_actions paa_dup
where pai.action_information_category = pai_dup.action_information_category
and pai.rowid <> pai_dup.rowid
and paa_dup.payroll_action_id = ppa.payroll_action_id
and paa_dup.assignment_action_id = pai_dup.action_context_id
and pai.action_information1 = pai_dup.action_information1 )
order by cpf_acc_number,pai.action_information3 desc,pai.action_information23 desc;
select nvl(pai.action_information1,pai.source_id) cpf_acc_number,
pai.action_information17,
pai.action_information18,
pai.action_information21,
pai.action_information22,
paa.assignment_id,
paa.assignment_action_id,
pai.tax_unit_id,
fnd_date.canonical_to_date(pai.action_information20),
pai.action_information19
from pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_action_information pai
where ppa.payroll_action_id = p_payroll_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = pai.action_context_id
and pai.action_information_category = 'SG CPF DETAILS'
and pai.action_context_type = 'AAC'
and pai.action_information2 = 'NEW'
and exists ( select 1
from pay_action_information pai_dup,
pay_assignment_actions paa_dup
where pai.action_information_category = pai_dup.action_information_category
and pai.rowid <> pai_dup.rowid
and paa_dup.payroll_action_id = ppa.payroll_action_id
and paa_dup.assignment_action_id = pai_dup.action_context_id
and pai.action_information1 = pai_dup.action_information1 )
order by cpf_acc_number,pai.action_information3 desc,pai.action_information23 desc;
update_status boolean;
select nvl(decode( action_information1,null,0,(decode(action_information21,'WP',0,(decode(action_information21,'EP',0,(decode(action_information21,'SP',0,to_number(action_information4) ))))))),0),
nvl(decode( action_information1,null,0,(decode(action_information21,'WP',0,(decode(action_information21,'EP',0,(decode(action_information21,'SP',0,to_number(action_information6) ))))))),0),
nvl(decode( action_information1,null,0,(decode(action_information21,'WP',0,(decode(action_information21,'EP',0,(decode(action_information21,'SP',0,to_number(action_information5) ))))))),0),
nvl(decode( action_information1,null,0,(decode(action_information21,'WP',0,(decode(action_information21,'EP',0,(decode(action_information21,'SP',0,to_number(action_information7) ))))))),0),
nvl(to_number(action_information8),0),
nvl(to_number(action_information9),0),
nvl(to_number(action_information10),0),
nvl(to_number(action_information11),0),
nvl(to_number(action_information12),0),
nvl(to_number(action_information13),0)
from pay_action_information
where action_context_id = c_assignment_action_id
and action_information_category = 'SG CPF DETAILS'
and action_context_type = 'AAC';
update_status := false;
t_dup_emp_rec.delete;
update_status := TRUE; -- update assignment status to processed (M)
if update_status=true then
t_dup_emp_rec(l_dup_counter).mf_record_status := 'M';
if update_status=true then
t_dup_emp_rec(l_dup_counter).mf_record_status := 'M';
if update_status=true then
t_dup_emp_rec(l_dup_counter).cl_record_status := 'C';
if update_status=true then
t_dup_emp_rec(l_dup_counter).cl_record_status := 'C';