[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure update_certificate_number
(
p_errmsg out nocopy varchar2,
p_errcode out nocopy varchar2,
p_bgid in number,
p_payroll_id in number,
p_tax_year in varchar2,
p_pay_action_id in varchar2,
p_asg_id in number,
p_asg_action_id in number,
p_tax_cert_no in varchar2
) is
-- Cursor used to update Tax Certificate Numbers
cursor c_tax_cert_no is
select serial_number
from pay_assignment_actions
where assignment_action_id = p_asg_action_id;
select paa.serial_number, paa.assignment_action_id
from pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.business_group_id = p_bgid
and ppa.report_type = 'ZA_IRP5'
and ppa.action_type = 'X'
and substr(ppa.legislative_parameters, instr(ppa.legislative_parameters, 'TAX_YEAR') + 9, 4)
= p_tax_year
and ppa.payroll_action_id <> substr(p_pay_action_id, 28, 9)
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = p_asg_id
and paa.action_sequence =
(
select max(paa2.action_sequence)
from pay_assignment_actions paa2
where paa2.payroll_action_id = ppa.payroll_action_id
and paa2.assignment_id = p_asg_id
);
select paa.serial_number, paa.assignment_action_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
ff_database_items dbi,
ff_archive_items arc
where ppa.business_group_id = p_bgid
and ppa.report_type = 'ZA_IRP5'
and ppa.action_type = 'X'
and substr(ppa.legislative_parameters, instr(ppa.legislative_parameters, 'TAX_YEAR') + 9, 4)
= p_tax_year
and ppa.payroll_action_id <> substr(p_pay_action_id, 28, 9)
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = p_asg_id
and dbi.user_name = 'A_PAY_PROC_PERIOD_ID'
and arc.user_entity_id = dbi.user_entity_id
and arc.context1 = to_char(paa.assignment_action_id)
and arc.value = p_period
and paa.action_sequence <>
(
select max(paa2.action_sequence)
from pay_assignment_actions paa2
where paa2.payroll_action_id = ppa.payroll_action_id
and paa2.assignment_id = p_asg_id
);
Select decode(count(*), 0 ,'Y', 'N')
into l_lump_sum_ind
From pay_payroll_actions ppa_arch,
pay_assignment_actions paa_arch
where paa_arch.assignment_action_id = p_asg_action_id
and ppa_arch.payroll_action_id = paa_arch.payroll_action_id
and paa_arch.assignment_action_id =
(
select max(paa.assignment_action_id)
from pay_assignment_actions paa
where paa.payroll_action_id = ppa_arch.payroll_action_id
and paa.assignment_id = paa_arch.assignment_id
) ;
update pay_assignment_actions
set serial_number = '&&' || l_old_num
where assignment_action_id = l_old_aa;
select nvl(arc.value, '')
into l_period
from ff_database_items dbi,
ff_archive_items arc
where dbi.user_name = 'A_PAY_PROC_PERIOD_ID'
and arc.user_entity_id = dbi.user_entity_id
and arc.context1 = p_asg_action_id;
update pay_assignment_actions
set serial_number = '&&' || l_old_num
where assignment_action_id = l_old_aa;
update pay_assignment_actions
set serial_number = p_tax_cert_no
where assignment_action_id = p_asg_action_id;
end update_certificate_number;
select assignment_action_id
from pay_assignment_actions
where payroll_action_id = p_pay_action_id
and assignment_id = p_asg_id;
select pai.action_information1 cert_num, -- Certificate Number
pai.action_information29 man_cert_num, -- Manual Certificate Number
pai.action_information28 cert_ind, -- O for old electronic, M for manual, OM for old manual
pai.action_information30 temp_cert_num, -- Temporary Certificate Number
pai.action_information_id act_inf_id,
pai.action_information18 directive1, -- Directive 1
pai.action_information19 directive2, -- Directive 2
pai.action_information20 directive3, -- Directive 3
pai2.action_information26 cert_type -- MAIN/LMPSM
from pay_action_information pai, -- For Employee info
pay_action_information pai2 -- For Employee contact info
where pai.action_context_id=ass_act_id
and pai.action_information30 = p_cert_num
and pai.action_information_category='ZATYE_EMPLOYEE_INFO'
and pai2.action_information_category='ZATYE_EMPLOYEE_CONTACT_INFO'
and pai2.action_context_id = pai.action_context_id
and pai2.action_information30 = pai.action_information30
and pai.action_context_type = 'AAP'
and pai.action_context_type = pai2.action_context_type;
select paa.assignment_action_id ass_act_id,
pai.action_information1 cert_num, --Certificate Number
pai.action_information29 man_cert_num, --Manual Certificate Number
pai.action_information28 cert_ind, --O - old electronic, M - Manual, OM - Old Manual
pai.action_information30 temp_cert_num, --Temporary certificate Number
pai.action_information18 directive1, -- Directive 1
pai.action_information19 directive2, -- Directive 2
pai.action_information20 directive3, -- Directive 3
pai2.action_information26 cert_type, --MAIN/LMPSM
pai.action_information_id act_inf_id,
pai2.action_information_id act_inf_id2
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_information pai, --For Employee Info
pay_action_information pai2 --For Employee contact info
where ppa.business_group_id = p_bgid
and ppa.report_type = 'ZA_TYE'
and ppa.action_type = 'X'
and substr(ppa.legislative_parameters, instr(ppa.legislative_parameters, 'TAX_YEAR') + 9, 4)
= p_tax_year
and NVL(substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,'PERIOD_RECON')+13, 2), '02')
= NVL(p_period_recon,'02') -- 9877034 fix
and ppa.payroll_action_id <> p_pay_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = p_asg_id
and paa.assignment_id = pai.assignment_id
and paa.assignment_action_id = pai.action_context_id
and pai.action_information_category= 'ZATYE_EMPLOYEE_INFO'
and pai.action_context_id = pai2.action_context_id
and pai2.action_information_category='ZATYE_EMPLOYEE_CONTACT_INFO'
and pai2.action_information30 = pai.action_information30
and pai.action_context_type = 'AAP'
and pai.action_context_type = pai2.action_context_type
and pai.action_information2 not in ('ITREG','A')
and pai2.action_information26 = p_cert_type;
hr_utility.set_location('Selected preprocess has electronic certificate issued',14);
hr_utility.set_location('Directive Number selected is MAIN',16);
update pay_action_information
set action_information28 ='OM'
where action_information_id=rec_other_ass_actions.act_inf_id;
update pay_action_information
set action_information28 ='OM'
where action_information_id=rec_other_ass_actions.act_inf_id;
hr_utility.set_location('Directive Number selected is LMPSM',42);
update pay_action_information
set action_information28 ='OM'
where action_information_id=rec_other_ass_actions.act_inf_id;
hr_utility.set_location('Update with manual certificate details',60);
update pay_action_information
set action_information28='M', action_information29=p_tax_cert_no
where action_information_id=rec_cert_details.act_inf_id;