[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Procedure wnu_update_extra_info calls apis to insert/update records in
PER_EXTRA_ASSIGNMENT_INFO */
--
PROCEDURE wnu_update_extra_info
(p_assignment_id in number,
p_effective_date in date,
p_include_in_wnu in varchar2 default null
) IS
--
l_ass_extra_info_id number(9):= null;
l_proc varchar2(72) := 'wnu_update_extra_info';
select upper(apf.current_employee_flag)
from per_all_people_f apf,
per_all_assignments_f aaf
where aaf.person_id = apf.person_id
and aaf.assignment_id = p_assignment_id
and p_effective_date between
apf.effective_start_date and apf.effective_end_date
and p_effective_date between
aaf.effective_start_date and aaf.effective_end_date;
select aei.assignment_extra_info_id ,
object_version_number
from per_assignment_extra_info aei
where aei.assignment_id = p_assignment_id
and information_type = 'IE_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_ass_extra_info_id,
p_aei_information_category => 'IE_WNU',
p_aei_information1 => l_include_in_wnu
);
hr_utility.trace('Updated flag');
end wnu_update_extra_info;
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';
select asg.assignment_id assignment_id,
ppa.effective_date effective_date
from per_all_assignments_f asg,
pay_payroll_actions ppa,
per_all_people_f pap,
per_periods_of_service serv,
-- hr_organization_information hoi,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck
where ppa.payroll_action_id = pactid
--and hoi.organization_id = ppa.business_group_id
and sck.segment4 = p_emp_ref
and asg.business_group_id = ppa.business_group_id
and asg.PRIMARY_FLAG = 'Y'
and asg.payroll_id = pay.payroll_id
and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
--and upper(p_tax_ref) = upper(sck.segment1)
-- For bug Fix 3567562 added condition to filter records based on PAYE Reference specified as parameter.
--and upper(sck.segment3) = upper(hoi.org_information2)
--and upper(p_paye_ref) = upper(sck.segment3)
--and upper(sck.segment1) = upper(hoi.org_information1)
and pay.payroll_id = NVL(p_payroll_id,pay.payroll_id)
and (p_assignment_set_id is null
OR exists
( select 1
from HR_ASSIGNMENT_SET_AMENDMENTS amend,
hr_assignment_sets aset
where
( P_ASSIGNMENT_SET_ID IS NOT NULL and
aset.assignment_set_id = P_ASSIGNMENT_SET_ID
-- Bug2856413 To handle payroll in Assgt sets
and nvl(aset.payroll_id,pay.payroll_id) = pay.payroll_id
and amend.assignment_set_id(+)= aset.assignment_set_id
and
(
(amend.include_or_exclude is not null AND
((amend.include_or_exclude='I' and amend.assignment_id = asg.assignment_id)
OR
(amend.include_or_exclude='E' and amend.assignment_id <> asg.assignment_id)))
OR
amend.include_or_exclude is null)
)
)
)
and asg.person_id = pap.person_id
and serv.person_id = pap.person_id
and serv.period_of_service_id = asg.period_of_service_id
and serv.date_start = (select max(s.date_start)
from per_periods_of_service s
where s.person_id = pap.person_id
and ppa.effective_date >= s.date_start)
and pap.current_employee_flag = 'Y'
and asg.person_id between
stperson and endperson
and ppa.effective_date between
asg.effective_start_date and asg.effective_end_date
and ppa.effective_date between
pap.effective_start_date and pap.effective_end_date
and ppa.effective_date between
pay.effective_start_date and pay.effective_end_date
order by asg.assignment_id;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
PAY_IE_WNU_EDI.wnu_update_extra_info
(p_assignment_id => asgrec.assignment_id,
p_effective_date => l_effective_date,
p_include_in_wnu => 'N');
hr_utility.trace('Succesfully updated flag');
/* PROCEDURE wnu_update_action_creation:
This PROC creates assignment actions when running the process in UPDATE Mode */
--
Procedure wnu_update_action_creation (pactid in number,
stperson in number,
endperson in number,
chunk in number)
IS
--
-- Bug Number : 4369280
-- commented hr_organization_information as the new legal employer classification is now
-- attached because of which we dont need the tax reference and the paye reference rather
-- we pass the employee reference. This change comes in effect due to a new legal employer
-- classification being used.
cursor csr_state(p_payroll_id NUMBER, p_emp_ref VARCHAR2, p_assignment_set_id NUMBER) is
select asg.assignment_id assignment_id,
ppa.effective_date effective_date
from per_all_assignments_f asg,
pay_payroll_actions ppa,
per_all_people_f pap,
per_periods_of_service serv,
per_assignment_extra_info aei,
-- hr_organization_information hoi,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck
where ppa.payroll_action_id = pactid
--and hoi.organization_id = ppa.business_group_id
and sck.segment4 = p_emp_ref
and asg.business_group_id = ppa.business_group_id
and asg.payroll_id = pay.payroll_id
and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
--and upper(p_tax_ref) = upper(sck.segment1)
--and upper(sck.segment1) = upper(hoi.org_information1)
-- For bug Fix 3567562 added condition to filter records based on PAYE Reference specified as parameter.
--and upper(sck.segment3) = upper(hoi.org_information2)
--and upper(p_paye_ref) = upper(sck.segment3)
and pay.payroll_id = NVL(p_payroll_id,pay.payroll_id)
and (p_assignment_set_id is null
OR exists
( select 1
from HR_ASSIGNMENT_SET_AMENDMENTS amend,
hr_assignment_sets aset
where
( P_ASSIGNMENT_SET_ID IS NOT NULL and
aset.assignment_set_id = P_ASSIGNMENT_SET_ID
-- Bug2856413 To handle payroll in Assgt sets
and nvl(aset.payroll_id,pay.payroll_id) = pay.payroll_id
and amend.assignment_set_id(+)= aset.assignment_set_id
and
(
(amend.include_or_exclude is not null AND
((amend.include_or_exclude='I' and amend.assignment_id = asg.assignment_id)
OR
(amend.include_or_exclude='E' and amend.assignment_id <> asg.assignment_id)))
OR
amend.include_or_exclude is null)
)))
and asg.person_id = pap.person_id
and serv.person_id = pap.person_id
and serv.period_of_service_id = asg.period_of_service_id
and serv.date_start = (select max(s.date_start)
from per_periods_of_service s
where s.person_id = pap.person_id
and ppa.effective_date >= s.date_start)
and pap.current_employee_flag = 'Y'
and asg.assignment_id = aei.assignment_id
and asg.PRIMARY_FLAG = 'Y'
and aei.information_type = 'IE_WNU'
and aei.aei_information1 = 'Y'
and asg.person_id between
stperson and endperson
and ppa.effective_date between
asg.effective_start_date and asg.effective_end_date
and ppa.effective_date between
pap.effective_start_date and pap.effective_end_date
and ppa.effective_date between
pay.effective_start_date and pay.effective_end_date
order by asg.assignment_id;
l_proc CONSTANT VARCHAR2(60):= g_package||'wnu_update_action_creation';
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
PAY_IE_WNU_EDI.wnu_update_extra_info
(p_assignment_id => asgrec.assignment_id,
p_effective_date => l_effective_date,
p_include_in_wnu => 'N');
hr_utility.trace('Succesfully updated flag');
hr_utility.trace('Error in Update Assgt Action cursor');
end wnu_update_action_creation;