[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
assignments only selected once and
character validation is enforced.
06-AUG-2002 A.Mills 115.2 2473608. Added join to periods_of_service
from per_all_assignments_f.
07-AUG-2002 A.Mills 115.4 Enabled package for Aggregated PAYE.
02-DEC-2002 G.Butler 115.7 nocopy qualifier added to range_cursor
18-DEC-2003 asengar 115.8 BUG 3294480 Changed code for NI update
08-SEP-2004 K.Thampan 115.9 Revert the change for bug 2545016
13-JAN-2005 K.Thampan 115.10 Bug 4117609 - Amended the cursor so that
it will return employee regardless the
NI number.
25-MAY-2005 K.Thampan 115.11 Bug 4392220 - Amended the cursor c_state in
procedure wnu_cleanse_act_creation to return
assignments that does't have a record on the
per_assignment_extra_info table. This is
because these assignments might have been entered
using API, instead of front end (Form).
09-FEB-2006 K.Thampan 115.12 Fixed bug 4938724. Set g_stored_asg_id to null
16-JUN-2006 K.Thampan 115.13 Code change for EDI Rollback.
23-JUN-2006 K.Thampan 115.14 Update deinitilization procedure.
27-JUN-2006 K.Thampan 115.15 Added code to clear down data for aggregated
assignments
29-JUN-2006 K.Thampan 115.16 Fixed GSCC error
28-JUL-2006 tukumar 115.13 Enhancement 5398360 : wnu 3.0
01-SEP-2006 tukumar 115.14 Performance fix bug 5504855
13-MAR-2006 K.Thampan 115.19 Bug fix 5929268
==============================================================================*/
--
--
TYPE act_info_rec IS RECORD
( assignment_id number(20)
,effective_date date
,action_info_category varchar2(50)
,act_info1 varchar2(300)
,act_info2 varchar2(300)
,act_info3 varchar2(300)
,act_info4 varchar2(300)
,act_info5 varchar2(300)
,act_info6 varchar2(300)
,act_info7 varchar2(300)
,act_info8 varchar2(300)
,act_info9 varchar2(300)
,act_info10 varchar2(300)
,act_info11 varchar2(300)
,act_info12 varchar2(300)
,act_info13 varchar2(300)
,act_info14 varchar2(300)
,act_info15 varchar2(300)
,act_info16 varchar2(300)
,act_info17 varchar2(300)
,act_info18 varchar2(300)
,act_info19 varchar2(300)
,act_info20 varchar2(300)
,act_info21 varchar2(300)
,act_info22 varchar2(300)
,act_info23 varchar2(300)
,act_info24 varchar2(300)
,act_info25 varchar2(300)
,act_info26 varchar2(300)
,act_info27 varchar2(300)
,act_info28 varchar2(300)
,act_info29 varchar2(300)
,act_info30 varchar2(300)
);
select to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'PAYROLL_ID')) payroll_id,
substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'TAX_REF'),1,20) tax_ref,
effective_date,
business_group_id
from pay_payroll_actions
where payroll_action_id = pactid;
select /*+ ORDERED */
asg.assignment_id assignment_id,
decode(pap.per_information10,'Y','Y',NULL) agg_paye_flag,
pap.person_id
from per_all_people_f pap,
per_assignments_f asg,
per_periods_of_service serv,
pay_all_payrolls_f pay,
per_assignment_extra_info aei,
hr_soft_coding_keyflex sck
where pap.person_id between stperson and endperson
and asg.business_group_id = l_business_group_id
and asg.person_id = pap.person_id
and asg.period_of_service_id = serv.period_of_service_id
and asg.payroll_id = pay.payroll_id
and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and upper(l_tax_ref) = upper(sck.segment1)
and (l_payroll_id IS NULL
or
l_payroll_id = pay.payroll_id)
and pap.current_employee_flag = 'Y'
and l_effective_date between asg.effective_start_date and asg.effective_end_date
and l_effective_date between pap.effective_start_date and pap.effective_end_date
and l_effective_date between pay.effective_start_date and pay.effective_end_date
and l_effective_date between serv.date_start and nvl(serv.actual_termination_date,hr_general.end_of_time)
and aei.assignment_id(+) = asg.assignment_id
and aei.information_type(+) = 'GB_WNU'
and nvl(aei.aei_information2,'N') <> 'Y'
and (p_mode = 'FULL'
or
( p_mode = 'UPDATE'
and (aei.aei_information1 is not null or aei.aei_information3 = 'Y')))
order by pap.person_id;
select pay_assignment_actions_s.nextval
into l_locking_act_id
from dual;
select pay_assignment_actions_s.nextval
into l_locking_act_id
from dual;
PROCEDURE update_aggregate_asg(p_assact_id in number)
IS
l_payroll_id number;
select to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'PAYROLL_ID')) payroll_id,
substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'TAX_REF'),1,20) tax_ref,
effective_date,
business_group_id
from pay_assignment_actions paa,
pay_payroll_actions pay
where paa.assignment_action_id = p_assact_id
and pay.payroll_action_id = paa.payroll_action_id;
select person_id,
paa.assignment_id
from pay_assignment_actions paa,
per_all_assignments_f paf
where paa.assignment_action_id = p_assact_id
and paa.assignment_id = paf.assignment_id;
select aei.assignment_extra_info_id
from per_assignment_extra_info aei
where aei.assignment_id = p_asg_id
and aei.information_type = 'GB_WNU';
select /*+ ORDERED */
asg.assignment_id assignment_id
from per_all_people_f pap,
per_assignments_f asg,
per_periods_of_service serv,
pay_all_payrolls_f pay,
per_assignment_extra_info aei,
hr_soft_coding_keyflex sck
where pap.person_id = l_person_id
and asg.business_group_id = l_business_group_id
and asg.person_id = pap.person_id
and asg.period_of_service_id = serv.period_of_service_id
and asg.payroll_id = pay.payroll_id
and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and upper(l_tax_ref) = upper(sck.segment1)
and (l_payroll_id IS NULL
or
l_payroll_id = pay.payroll_id)
and pap.current_employee_flag = 'Y'
and pap.per_information10 = 'Y'
and l_effective_date between asg.effective_start_date and asg.effective_end_date
and l_effective_date between pap.effective_start_date and pap.effective_end_date
and l_effective_date between pay.effective_start_date and pay.effective_end_date
and l_effective_date between serv.date_start and nvl(serv.actual_termination_date,hr_general.end_of_time)
and aei.assignment_id = asg.assignment_id
and aei.information_type = 'GB_WNU'
and nvl(aei.aei_information2,'N') <> 'Y'
and (aei.aei_information1 is not null or aei.aei_information3 = 'Y')
order by pap.person_id;
hr_assignment_extra_info_api.update_assignment_extra_info
(p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_wnu_id,
p_aei_information_category => 'GB_WNU',
p_aei_information1 => null,
p_aei_information2 => 'N',
p_aei_information3 => 'N');
END update_aggregate_asg;
select /*+ ORDERED */
pap.title,
pap.first_name,
pap.middle_names,
pap.last_name,
paa.ASSIGNMENT_NUMBER,
pap.national_identifier,
paa.assignment_id
from pay_assignment_actions pact,
per_assignments_f paa,
per_people_f pap
where pact.assignment_action_id = p_assactid
and pact.assignment_id = paa.assignment_id
and paa.person_id = pap.person_id
and p_effective_date between paa.effective_start_date and paa.effective_end_date
and p_effective_date between pap.effective_start_date and pap.effective_end_date;
select aei.assignment_extra_info_id,
aei.aei_information1 old_asg_number,
aei.aei_information2 not_flag,
aei.aei_information3 ni_update,
aei.object_version_number,
paa.assignment_id
from pay_assignment_actions paa,
per_assignment_extra_info aei
where paa.assignment_action_id = p_assactid
and paa.assignment_id = aei.assignment_id
and aei.information_type = 'GB_WNU';
hr_assignment_extra_info_api.update_assignment_extra_info
(p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_wnu_rec.assignment_extra_info_id,
p_aei_information_category => 'GB_WNU',
p_aei_information1 => null,
p_aei_information2 => 'N',
p_aei_information3 => 'N');
update_aggregate_asg(p_assactid);
p_wnu_rec.act_info4 := l_wnu_rec.ni_update;
PROCEDURE insert_archive_row(p_assactid IN NUMBER,
p_effective_date IN DATE,
p_tab_rec_data IN action_info_table) IS
l_proc CONSTANT VARCHAR2(50):= g_package||'insert_archive_row';
END insert_archive_row;
select hoi.org_information11,
hoi.org_information1
from pay_payroll_actions pact,
hr_organization_information hoi
where pact.payroll_action_id = p_payroll_action_id
and pact.business_group_id = hoi.organization_id
and hoi.org_information_context = 'Tax Details References'
and (hoi.org_information10 is null
OR
hoi.org_information10 = 'UK')
and hoi.org_information1 =
substr(pact.legislative_parameters,
instr(pact.legislative_parameters,'TAX_REF=') + 8,
instr(pact.legislative_parameters||' ',' ',
instr(pact.legislative_parameters,'TAX_REF=')+8)
- instr(pact.legislative_parameters,'TAX_REF=') - 8);
sqlstr := 'select distinct person_id '||
'from per_people_f ppf, '||
'pay_payroll_actions ppa '||
'where ppa.payroll_action_id = :payroll_action_id '||
'and ppa.business_group_id = ppf.business_group_id '||
'order by ppf.person_id';
PROCEDURE wnu_update_action_creation(pactid in number,
stperson in number,
endperson in number,
chunk in number) IS
BEGIN
internal_act_creation(pactid, stperson, endperson, chunk, 'UPDATE');
END wnu_update_action_creation;
insert_archive_row(p_assactid, p_effective_date, l_archive_tab);
select substr(pact.legislative_parameters,
instr(pact.legislative_parameters,'VERSION=') + 8,
instr(pact.legislative_parameters||' ',' ',instr(pact.legislative_parameters,'VERSION=')+8)
- instr(pact.legislative_parameters,'VERSION=') - 8) version
from pay_payroll_actions pact where pact.payroll_action_id = pactid;
select legislative_parameters para,
fnd_number.number_to_canonical(request_id) control_id,
report_type,
business_group_id
from pay_payroll_actions
where payroll_action_id = pactid;
select nvl(hoi.org_information11,' ') sender_id,
nvl(upper(hoi.org_information2),' ') hrmc_office,
nvl(upper(hoi.org_information4),' ') er_addr,
nvl(upper(hoi.org_information3),' ') er_name
from hr_organization_information hoi
where hoi.organization_id = p_bus_id
and hoi.org_information_context = 'Tax Details References'
and nvl(hoi.org_information10,'UK') = 'UK'
and upper(hoi.org_information1) = upper(p_tax_ref);
select /*+ ORDERED */
peo.first_name f_name ,
peo.middle_names m_name,
peo.last_name l_name,
peo.title title,
paf.assignment_number emp_no,
peo.national_identifier ni_no ,
pai.action_information2 old_works_number
from pay_payroll_actions pay,
pay_assignment_actions paa,
per_all_assignments_f paf,
per_all_people_f peo,
pay_action_information pai
where pay.payroll_action_id = pactid
and paa.payroll_action_id = pay.payroll_action_id
and paa.action_status = p_type
and pai.action_context_id(+) = paa.assignment_action_id
and pai.action_context_type(+) = 'AAP'
and pai.action_information_category(+) = 'GB WNU EDI'
and paf.assignment_id = paa.assignment_id
and peo.person_id = paf.person_id
and pay.effective_date between paf.effective_start_date and paf.effective_end_date
and pay.effective_date between peo.effective_start_date and peo.effective_end_date;