[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select per_information10,effective_end_date
from per_all_people_f
where person_id = c_person_id
and c_effective_date between effective_start_date and effective_end_date;
select typ.system_person_type
from per_person_types typ,
per_all_people_f ppf
where ppf.person_id = c_person_id
and ppf.person_type_id = typ.person_type_id
and c_effective_date between
ppf.effective_start_date and ppf.effective_end_date;
SELECT count(*)
FROM per_all_assignments_f
WHERE person_id = p_person_id
AND ((assignment_type <> 'A' and assignment_type <> 'O')
or (assignment_type in ('A','O') and payroll_id is not null))
AND p_effective_date BETWEEN effective_start_date AND effective_end_date ;
SELECT COUNT(hsck.segment1) Num, hsck.segment1 tax_district
FROM hr_soft_coding_keyflex hsck,
pay_all_payrolls_f papf,
per_all_assignments_f paaf,
per_assignment_status_types past
WHERE hsck.soft_coding_keyflex_id = papf.soft_coding_keyflex_id
AND papf.payroll_id =paaf.payroll_id
AND past.assignment_status_type_id = paaf.assignment_status_type_id
AND paaf.person_id = p_person_id
/*Commented for bug fix 3949536*/
--AND past.per_system_status='ACTIVE_ASSIGN'
AND p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND p_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
GROUP BY hsck.segment1;
SELECT nvl(min(decode(inv.name, 'Tax Code', eev.screen_entry_value, null)),0)||
nvl(min(decode(inv.name, 'Tax Basis', substr(HR_GENERAL.DECODE_LOOKUP('GB_TAX_BASIS',eev.screen_entry_value),1,80),null)),0)||
nvl(min(decode(inv.name, 'Refundable', substr(HR_GENERAL.DECODE_LOOKUP('GB_REFUNDABLE',eev.screen_entry_value),1,80),null)),0)||
nvl(min(decode(inv.name, 'Pay Previous', eev.screen_entry_value, null)),0)||
nvl(min(decode(inv.name, 'Tax Previous', eev.screen_entry_value, null)),0)||
nvl(min(decode(inv.name, 'Authority', substr(HR_GENERAL.DECODE_LOOKUP('GB_AUTHORITY',eev.screen_entry_value),1,80),null)),0)||
nvl(ele.entry_information1,0)||
nvl(ele.entry_information2,0) VALUE,
pay_gb_eoy_archive.get_agg_active_start(paa.assignment_id, p_tax_district, greatest(paa.effective_start_date,ppf.effective_start_date)) cpe_start_date,
paa.assignment_id assignment_id,
eev.effective_start_date effective_start_date,
eev.effective_end_date effective_end_date
from
pay_element_entries_f ele,
pay_element_entry_values_f eev,
pay_input_values_f inv,
pay_element_links_f lnk, pay_element_types_f elt,
per_all_assignments_f paa,
pay_all_payrolls_f ppf,
hr_soft_coding_keyflex scl
where ele.element_entry_id = eev.element_entry_id
-- and p_effective_date between ele.effective_start_date and ele.effective_end_date
and ele.effective_start_date <= p_end_date
and ele.effective_end_date >= p_start_date
and eev.input_value_id + 0 = inv.input_value_id
-- and p_effective_date between eev.effective_start_date and eev.effective_end_date
and eev.effective_start_date <= p_end_date
and eev.effective_end_date >= p_start_date
and inv.element_type_id = elt.element_type_id
-- and p_effective_date between inv.effective_start_date and inv.effective_end_date
and inv.effective_start_date <= p_end_date
and inv.effective_end_date >= p_start_date
and ele.element_link_id = lnk.element_link_id
and elt.element_type_id = lnk.element_type_id
-- and p_effective_date between lnk.effective_start_date and lnk.effective_end_date
and lnk.effective_start_date <= p_end_date
and lnk.effective_end_date >= p_start_date
and elt.element_name = 'PAYE Details'
and paa.person_id= p_person_id
and ele.assignment_id=paa.assignment_id
-- and p_effective_date between elt.effective_start_date and elt.effective_end_date
and elt.effective_start_date <= p_end_date
and elt.effective_end_date >= p_start_date
-- and p_effective_date between paa.effective_start_date and paa.effective_end_date
and paa.effective_start_date <= p_end_date
and paa.effective_end_date >= p_start_date
and scl.segment1=p_tax_district
and ppf.soft_coding_keyflex_id=scl.soft_coding_keyflex_id
and ppf.payroll_id = paa.payroll_id
-- and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
and ppf.effective_start_date <= p_end_date
and ppf.effective_end_date >= p_start_date
and exists ( SELECT 1
FROM per_all_assignments_f paaf,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex hsck,
per_assignment_status_types past
WHERE paaf.person_id = p_person_id
and paaf.assignment_id not in (paa.assignment_id)
and paaf.effective_start_date <= p_end_date
and paaf.effective_end_date >= p_start_date
and papf.effective_start_date <= p_end_date
and papf.effective_end_date >= p_start_date
and papf.payroll_id = paaf.payroll_id
and papf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
and hsck.segment1 = scl.segment1
and pay_gb_eoy_archive.get_agg_active_end(paa.assignment_id, p_tax_district, greatest(paa.effective_start_date,ppf.effective_start_date))
= pay_gb_eoy_archive.get_agg_active_end(paaf.assignment_id, p_tax_district, greatest(paaf.effective_start_date,papf.effective_start_date))
and pay_gb_eoy_archive.get_agg_active_start(paa.assignment_id, p_tax_district, greatest(paa.effective_start_date,ppf.effective_start_date))
= pay_gb_eoy_archive.get_agg_active_start(paaf.assignment_id, p_tax_district, greatest(paaf.effective_start_date,papf.effective_start_date))
and paaf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
)
group by ele.rowid, scl.segment1,
ele.assignment_id,ele.element_entry_id,
ele.entry_information_category, ele.entry_information1, ele.entry_information2,
ele.effective_start_date, ele.effective_end_date,
eev.effective_start_date,eev.effective_end_date,
paa.assignment_id,paa.effective_start_date,ppf.effective_start_date
order by cpe_start_date,eev.effective_start_date,paa.assignment_id;
select count(*)
from pay_element_entries_f ele,
pay_element_links_f lnk,
pay_element_types_f elt,
per_all_assignments_f paa
where elt.element_name = 'PAYE Details'
and p_effective_date between elt.effective_start_date and elt.effective_end_date
and elt.element_type_id = lnk.element_type_id
and p_effective_date between lnk.effective_start_date and lnk.effective_end_date
and lnk.element_link_id = ele.element_link_id
and p_effective_date between ele.effective_start_date and ele.effective_end_date
and ele.assignment_id = paa.assignment_id
and paa.person_id = p_person_id
and p_effective_date between paa.effective_start_date and paa.effective_end_date;
p_datetrack_update_mode IN VARCHAR2 DEFAULT NULL)
IS
l_ni_payroll_action_count NUMBER;
SELECT per_information9
from per_all_people_f
WHERE person_id = p_person_id
AND effective_end_date=p_cur_effective_start_date-1;
SELECT count(*)
FROM per_all_people_f
WHERE person_id = p_person_id
AND effective_start_date > p_effective_date
AND nvl(per_information9,'N')='N';
SELECT COUNT(distinct(paa.assignment_id))
FROM pay_payroll_actions ppa,
pay_assignment_actions paact,
per_all_assignments_f paa
WHERE paa.person_id = p_person_id
AND paa.assignment_id =paact.assignment_id
AND ppa.payroll_action_id =paact.payroll_action_id
AND ppa.action_type IN ('R','Q')
AND ppa.action_status='C'
AND paact.source_action_id IS NOT NULL
AND ppa.effective_date BETWEEN p_cur_effective_start_date AND p_cur_effective_end_date;
-- Validation for UPDATE MODE
IF p_datetrack_update_mode='UPDATE' or p_datetrack_update_mode= 'UPDATE_OVERRIDE' or p_datetrack_update_mode='UPDATE_CHANGE_INSERT' THEN
IF to_char(p_effective_date,'DDMM')<>'0604' THEN
hr_utility.set_message(801,'HR_GB_78153_NI_AGG_UNCK_ST_FUT');
ELSIF p_datetrack_update_mode='CORRECTION' THEN
-- In correction mode, we check for more than 1 assignments having payroll run in the date range
OPEN csr_ni_agg_ass;
Procedure to be called through User hook of update_person_api
for calling function assg_aggr_possible and checking if 'NI
Multiple assignments' flag is 'Y' if aggregate assignment flag
is 'Y'
-------------------------------------------------------------*/
PROCEDURE check_aggr_assg(p_person_id IN NUMBER,
p_effective_date IN DATE,
p_per_information9 IN VARCHAR2,
p_per_information10 IN VARCHAR2,
p_datetrack_update_mode in VARCHAR2 default null)
IS
-- Start of bug#8370225
l_effective_date date;
l_update_mode varchar2(100);
select a.per_information10, a.per_information9, a.effective_start_date,a.effective_end_date
from per_all_people_f a
where a.person_id = c_person_id
and c_effective_date between a.effective_start_date and a.effective_end_date;
select 1
from pay_all_payrolls_f papf,
per_all_assignments_f paaf,
hr_soft_coding_keyflex hsck,
per_assignment_status_types past
where paaf.person_id = c_person_id
and paaf.effective_start_date <= c_end_date-1
and paaf.effective_end_date >= c_start_date
and paaf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
and papf.payroll_id =paaf.payroll_id
and papf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and c_end_date between papf.effective_start_date and papf.effective_end_date
and exists ( select 1
from pay_all_payrolls_f apf,
per_all_assignments_f aaf,
hr_soft_coding_keyflex sck,
per_assignment_status_types ast
where aaf.person_id = c_person_id
and aaf.assignment_id not in (paaf.assignment_id)
and aaf.effective_start_date <= c_end_date-1
and aaf.effective_end_date >= c_start_date
and aaf.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
and apf.payroll_id =aaf.payroll_id
and c_end_date between apf.effective_start_date and apf.effective_end_date
and apf.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and sck.segment1 = hsck.segment1
AND pay_gb_eoy_archive.get_agg_active_end(aaf.assignment_id, hsck.segment1, c_end_date)
= pay_gb_eoy_archive.get_agg_active_end(paaf.assignment_id, hsck.segment1, c_end_date)
AND pay_gb_eoy_archive.get_agg_active_start(aaf.assignment_id, hsck.segment1, c_end_date)
= pay_gb_eoy_archive.get_agg_active_start(paaf.assignment_id, hsck.segment1, c_end_date));
select min(ppa.effective_date),
max(ppa.effective_date)
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_all_assignments_f paaf
where paa.assignment_id = c_assignment_id
and paaf.assignment_id = c_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q')
and ppa.effective_date between c_start_date and c_end_date
and paaf.effective_start_date <= c_end_date
and paaf.effective_end_date >= c_start_date
and paaf.payroll_id = ppa.payroll_id
order by ppa.effective_date;
select assignment_id
from per_all_assignments_f
where person_id = c_person_id
and effective_end_date >= c_start_date
and effective_start_date <= c_end_date;
select 1 from dual
where nvl(hr_gbbal.calc_all_balances(c_date_eoy, c_assignment_id, l_tax_pay_asg_td_ytd_dfbid),0) <>
nvl(hr_gbbal.calc_all_balances(c_date_eoy, c_assignment_id, l_tax_pay_per_td_cpe_ytd_dfbid),0) OR
nvl(hr_gbbal.calc_all_balances(c_date_eoy, c_assignment_id, l_paye_asg_td_ytd_dfbid),0) <>
nvl(hr_gbbal.calc_all_balances(c_date_eoy, c_assignment_id, l_paye_per_td_cpe_ytd_dfbid),0);
SELECT defined_balance_id
FROM pay_defined_balances
WHERE balance_type_id = (SELECT balance_type_id
FROM pay_balance_types
WHERE balance_name = c_balance_name AND legislation_code = 'GB')
AND balance_dimension_id = (SELECT balance_dimension_id
FROM pay_balance_dimensions
WHERE dimension_name = c_dimension_name AND legislation_code = 'GB');
select a.per_information10, a.effective_start_date
from per_all_people_f a
where a.person_id = c_person_id
and c_effective_date between a.effective_start_date and a.effective_end_date;
select 1
from per_all_assignments_f a,
per_assignment_status_types past
where a.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status = 'TERM_ASSIGN'
and a.person_id = c_person_id
and c_effective_date between a.effective_start_date and a.effective_end_date;
l_update_mode := p_datetrack_update_mode;
if p_datetrack_update_mode <> 'NOVALIDATION' then
begin
-- while changing the PAYE aggregation flag, we need to ensure that there are no future payroll actions, on
-- two (or) more assignment(s) referring a single PAYE Tax district reference(so asg'saggregated).
-- If found then we need to raise an error.
-- bug 9535747 : commented the below code as aggregation details are already fetched.
/* l_effective_date := p_effective_date;
l_update_mode := p_datetrack_update_mode;
if l_update_mode = 'CORRECTION' THEN
l_effective_date := l_cur_effective_start_date;
if l_update_mode = 'UPDATE_OVERRIDE' THEN
l_cur_effective_end_date := to_date('31-12-4712','DD-MM-YYYY');
to_char(l_effective_date,'dd-mm') = '06-04' and l_update_mode <> 'CORRECTION' then
open cur_term_asg_dtls(p_person_id, l_effective_date);
to_char(l_cur_effective_start_date,'dd-mm') = '06-04' and l_update_mode = 'CORRECTION' then
--
-- if datetrack mode is correction and from SOY then check for the previous day aggregation flag,
-- if the flag is Y; then we should not stop this aggregation flag change from N to Y
IF p_datetrack_update_mode <>'NOVALIDATION' THEN
l_local_date_trace_mode:=p_datetrack_update_mode;
p_datetrack_update_mode =>l_local_date_trace_mode);
select per_information10, object_version_number, employee_number
from per_all_people_f
where person_id = c_person_id
and c_effective_date between effective_start_date and effective_end_date;
select per_information9
from per_all_people_f
where person_id = c_person_id
and c_effective_date between effective_start_date and effective_end_date;
select person_id, effective_start_date, effective_end_date,
per_information9, per_information10, full_name, object_version_number
from per_all_people_f
where person_id=c_person_id
and effective_end_date >= c_effective_date
order by effective_start_date;
select count (distinct assignment_id)
from per_all_assignments_f
where person_id = c_person_id
and c_effective_date between effective_start_date and effective_end_date;
SELECT --COUNT(hsck.segment1) Num,
hsck.segment1 tax_district
FROM hr_soft_coding_keyflex hsck,
pay_all_payrolls_f papf,
per_all_assignments_f paaf
WHERE paaf.assignment_id= c_assignment_id
AND papf.payroll_id =paaf.payroll_id
AND hsck.soft_coding_keyflex_id = papf.soft_coding_keyflex_id
AND c_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND c_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
GROUP BY hsck.segment1;
SELECT distinct
ppev.TAX_CODE,
ppev.Tax_Basis,
ppev.Pay_Previous,
ppev.Tax_Previous,
ppev.Refundable,
ppev.Authority,
P6_iss_date,
P6_msg_date
FROM (SELECT min(decode(inv.name, 'Tax Code', eev.screen_entry_value, null)) Tax_Code,
min(decode(inv.name, 'Tax Basis', eev.screen_entry_value, null)) Tax_Basis,
min(decode(inv.name, 'Refundable', eev.screen_entry_value, null)) Refundable,
min(decode(inv.name, 'Pay Previous', nvl(eev.screen_entry_value,0), null)) Pay_Previous,
min(decode(inv.name, 'Tax Previous', nvl(eev.screen_entry_value,0), null)) Tax_Previous,
min(decode(inv.name, 'Authority', eev.screen_entry_value, null)) Authority,
min(ele.entry_information1) P6_iss_date,
min(ele.entry_information2) P6_msg_date
FROM pay_element_entries_f ele,
pay_element_entry_values_f eev,
pay_input_values_f inv,
pay_element_links_f lnk,
pay_element_types_f elt,
pay_all_payrolls_f papf,
per_all_assignments_f paaf,
hr_soft_coding_keyflex hsck
WHERE paaf.person_id = c_person_id
AND c_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.payroll_id = papf.payroll_id
AND c_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
AND hsck.segment1 = c_tax_ref
AND ele.assignment_id=paaf.assignment_id
AND c_effective_date between ele.effective_start_date and ele.effective_end_date
AND ele.element_entry_id = eev.element_entry_id
AND eev.input_value_id + 0 = inv.input_value_id
AND c_effective_date between eev.effective_start_date and eev.effective_end_date
AND inv.element_type_id = elt.element_type_id
AND c_effective_date between inv.effective_start_date and inv.effective_end_date
AND ele.element_link_id = lnk.element_link_id
AND c_effective_date between lnk.effective_start_date and lnk.effective_end_date
AND elt.element_name = 'PAYE Details'
AND elt.legislation_code = 'GB'
AND c_effective_date between elt.effective_start_date and elt.effective_end_date
-- AND pay_p45_pkg.PAYE_SYNC_P45_ISSUED_FLAG(paaf.assignment_id,c_effective_date) = 'N'
AND paaf.assignment_id <> c_assignment_id
AND pay_gb_eoy_archive.get_agg_active_start(paaf.assignment_id, c_tax_ref,c_effective_date) =
pay_gb_eoy_archive.get_agg_active_start(c_assignment_id, c_tax_ref,c_effective_date)
AND pay_gb_eoy_archive.get_agg_active_end(paaf.assignment_id, c_tax_ref,c_effective_date) =
pay_gb_eoy_archive.get_agg_active_end(c_assignment_id, c_tax_ref,c_effective_date)
) ppev
where ppev.TAX_CODE is not null
and ppev.Tax_Basis is not null
and ppev.Refundable is not null;
SELECT ele.element_entry_id element_entry_id,
min(decode(inv.name, 'Tax Code', eev.screen_entry_value, null)) Tax_Code,
min(decode(inv.name, 'Tax Code', eev.input_value_id, null)) Tax_Code_iv_id,
min(decode(inv.name, 'Tax Basis', eev.screen_entry_value, null)) Tax_Basis,
min(decode(inv.name, 'Tax Basis', eev.input_value_id, null)) Tax_Basis_iv_id,
min(decode(inv.name, 'Refundable', eev.screen_entry_value, null)) Refundable,
min(decode(inv.name, 'Refundable', eev.input_value_id, null)) Refundable_iv_id,
min(decode(inv.name, 'Pay Previous', nvl(eev.screen_entry_value,0), null)) Pay_Previous,
min(decode(inv.name, 'Pay Previous', eev.input_value_id, null)) Pay_Previous_iv_id,
min(decode(inv.name, 'Tax Previous', nvl(eev.screen_entry_value,0), null)) Tax_Previous,
min(decode(inv.name, 'Tax Previous', eev.input_value_id, null)) Tax_Previous_iv_id,
min(decode(inv.name, 'Authority', eev.screen_entry_value, null)) Authority,
min(decode(inv.name, 'Authority', eev.input_value_id, null)) Authority_iv_id,
min(ele.entry_information1) P6_iss_date,
min(ele.entry_information2) P6_msg_date
FROM pay_element_entries_f ele,
pay_element_entry_values_f eev,
pay_input_values_f inv,
pay_element_links_f lnk,
pay_element_types_f elt,
per_all_assignments_f paaf
WHERE ele.element_entry_id = eev.element_entry_id
AND c_effective_date between ele.effective_start_date and ele.effective_end_date
AND eev.input_value_id + 0 = inv.input_value_id
AND c_effective_date between eev.effective_start_date and eev.effective_end_date
AND inv.element_type_id = elt.element_type_id
AND c_effective_date between inv.effective_start_date and inv.effective_end_date
AND ele.element_link_id = lnk.element_link_id
AND c_effective_date between lnk.effective_start_date and lnk.effective_end_date
AND elt.element_name = 'PAYE Details'
AND elt.legislation_code = 'GB'
AND c_effective_date between elt.effective_start_date and elt.effective_end_date
AND ele.assignment_id=paaf.assignment_id
AND c_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.assignment_id = c_assignment_id
group by ele.element_entry_id;
select meaning from hr_lookups
where lookup_type=c_lookup_type
and lookup_code=c_lookup_code;
SELECT COUNT(distinct(paa.assignment_id))
FROM pay_payroll_actions ppa,
pay_assignment_actions paact,
per_all_assignments_f paa
WHERE paa.person_id = p_person_id
AND paa.assignment_id =paact.assignment_id
AND ppa.payroll_action_id =paact.payroll_action_id
AND ppa.action_type IN ('R','Q')
AND ppa.action_status='C'
AND paact.source_action_id IS NOT NULL
AND ppa.effective_date BETWEEN p_effective_start_date AND p_effective_end_date;
SELECT person_id,
employee_number, --Added for Bug 13323723
effective_start_date,
effective_end_date,
per_information9,
per_information10,
full_name,
object_version_number
FROM per_all_people_f
WHERE person_id =c_person_id
AND effective_end_date >= c_effective_date
ORDER BY effective_start_date desc;
l_pers_dt_mode := 'UPDATE';
l_pers_dt_mode := 'UPDATE_CHANGE_INSERT';
/* Call API to update NI Agg flag, this will fire validations from check_aggr_assg. */
l_employee_number :=rec.employee_number; --Added for Bug 13323723
hr_person_api.update_person(p_validate => false
,p_effective_date => l_person_effective_date
,p_datetrack_update_mode => l_pers_dt_mode
,p_person_id => p_person_id
,p_object_version_number => rec.object_version_number
,p_employee_number => l_employee_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_per_information9 => 'Y'
,p_full_name => rec.full_name
,p_comment_id => l_comment_id
,p_name_combination_warning => l_name_combination_warning
,p_assign_payroll_warning => l_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning );
END LOOP; -- Records in per_all_people_f which need to be updated.
hr_entry_api.update_element_entry(p_dt_update_mode => 'CORRECTION',
p_session_date => p_effective_date,
p_element_entry_id => r_curr_asg_paye.element_entry_id,
p_input_value_id1 => r_curr_asg_paye.Tax_Code_iv_id,
p_input_value_id2 => r_curr_asg_paye.Tax_Basis_iv_id,
p_input_value_id3 => r_curr_asg_paye.Pay_Previous_iv_id,
p_input_value_id4 => r_curr_asg_paye.Tax_Previous_iv_id,
p_input_value_id5 => r_curr_asg_paye.Refundable_iv_id,
p_input_value_id6 => r_curr_asg_paye.Authority_iv_id,
p_entry_value1 => r_agg_paye.Tax_Code,
p_entry_value2 => r_agg_paye.Tax_Basis,
p_entry_value3 => r_agg_paye.Pay_Previous,
p_entry_value4 => r_agg_paye.Tax_Previous,
p_entry_value5 => r_agg_paye.Refundable,
p_entry_value6 => r_agg_paye.Authority,
p_entry_information_category => 'GB_PAYE',
p_entry_information1 => r_agg_paye.P6_iss_date,
p_entry_information2 => r_agg_paye.P6_msg_date
);
l_pers_dt_mode := 'UPDATE';
l_pers_dt_mode := 'UPDATE_CHANGE_INSERT';
/* Call API to update PAYE Agg flag, this will fire validations from check_aggr_assg. */
hr_person_api.update_person(p_validate => false
,p_effective_date => l_person_effective_date
,p_datetrack_update_mode => l_pers_dt_mode
,p_person_id => p_person_id
,p_object_version_number => rec.object_version_number
,p_employee_number => l_employee_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_per_information9 => 'Y'
,p_per_information10 => 'Y'
,p_full_name => rec.full_name
,p_comment_id => l_comment_id
,p_name_combination_warning => l_name_combination_warning
,p_assign_payroll_warning => l_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning
);
end loop; -- Records in per_all_people_f which need to be updated.
Details: This function is called from PERGBOBJ.fmb, for POST-INSERT event of secondary Assignment.
Return: This will return the Error Message Name and Application, if any validations for PAYE Agg
failed. Else it will return null. Return values are used in PERGBOBJ.fmb to show appropriate
warning message to user. */
FUNCTION get_paye_agg_status(p_person_id IN NUMBER,
p_effective_date IN DATE,
p_assignment_id IN NUMBER,
p_payroll_id IN NUMBER) return varchar2
is
l_msg_name varchar2(100);
SELECT 1
INTO var
FROM per_all_people_f pap
,per_person_types ppt
WHERE p_effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND pap.person_id = p_person_id
AND ppt.system_person_type = 'EMP'
AND pap.person_type_id = ppt.person_type_id;
SELECT assignment_extra_info_id
,object_version_number
INTO p_assignment_extra_info_id
,p_object_version_number
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND aei_information_category = 'GB_RTI_AGGREGATION';
SELECT assignment_extra_info_id
,object_version_number
INTO p_assignment_extra_info_id
,p_object_version_number
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND aei_information_category = 'GB_RTI_ASG_DETAILS';
select paf.assignment_id
from
per_all_people_f pap,
per_all_assignments_f paf
where paf.person_id = pap.person_id
and paf.person_id = p_person_id
and p_effective_date between paf.effective_start_date and paf.effective_end_date
and p_effective_date between pap.effective_start_date and pap.effective_end_date
and nvl(paf.primary_flag,'N')='Y';
select paf.assignment_id
from
per_all_people_f pap,
per_all_assignments_f paf,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex hsc
where paf.person_id = pap.person_id
and paf.assignment_id = p_assignment_id
and paf.payroll_id = pay.payroll_id
and pay.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
and hsc.segment1 = p_paye_reference
and p_effective_date between paf.effective_start_date and paf.effective_end_date
and p_effective_date between pap.effective_start_date and pap.effective_end_date
and nvl(paf.primary_flag,'N')='Y';
select min(paf.assignment_id) from
per_all_assignments_f paf,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex hsc
where paf.payroll_id= pay.payroll_id
and pay.soft_coding_keyflex_id= hsc.soft_coding_keyflex_id
and p_effective_date between pay.effective_start_date and pay.effective_end_date
and p_effective_date between paf.effective_start_date and paf.effective_end_date
and paf.person_id = p_person_id
and hsc.segment1= p_paye_reference;
SELECT trim (pap.per_information10) per_agg_flag
,trim (pap.per_information9) per_ni_flag
,nvl (paf.primary_flag
,'N') primary_flag
INTO v_per_agg_flag
,v_per_ni_flag
,v_primary_flag
FROM per_all_people_f pap
,per_all_assignments_f paf
WHERE paf.person_id = pap.person_id
AND paf.assignment_id = p_assignment_id
AND p_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND p_effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date;
SELECT assignment_number
INTO v_rti_payroll_id
FROM per_all_assignments_f
WHERE person_id = p_person_id
AND nvl (primary_flag
,'N') = 'Y'
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT assignment_number
INTO v_rti_payroll_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
PROCEDURE update_rti_agg_update_asg
(p_assignment_id IN per_all_assignments_f.assignment_id%type
,p_effective_date IN date) IS
p_person_id number;
SELECT DISTINCT
person_id
INTO p_person_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
per_asg_aggr.update_rti_agg_person (p_person_id
,p_effective_date);
END update_rti_agg_update_asg;
PROCEDURE update_rti_agg_new_person
(p_person_id IN per_all_people_f.person_id%TYPE
,p_hire_date IN date) IS
BEGIN
per_asg_aggr.update_rti_agg_person
(p_person_id => p_person_id
,p_effective_date => p_hire_date);
END update_rti_agg_new_person;
PROCEDURE update_rti_agg_person
(p_person_id IN per_all_people_f.person_id%TYPE
,p_effective_date IN date) IS
CURSOR csr_ni_paye_flag IS
SELECT trim (pap.per_information10) per_agg_flag
,trim (pap.per_information9) per_ni_flag
FROM per_all_people_f pap
WHERE pap.person_id = p_person_id
AND p_effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date;
SELECT trim (paf.primary_flag) asg_primary_flag
,trim (pap.per_information10) per_agg_flag
,trim (pap.per_information9) per_ni_flag
,paf.assignment_id assignment_id
,paf.assignment_number assignment_number
,paf.effective_start_date assignment_start_date
,pap.business_group_id business_group_id
,nvl(
(SELECT MIN(paaf2.assignment_number)
FROM per_all_assignments_f paaf2,
pay_all_payrolls_f papf1,
hr_soft_coding_keyflex hsck1
WHERE paaf2.person_id = paf.person_id
AND papf1.payroll_id = paaf2.payroll_id
AND papf1.soft_coding_keyflex_id = hsck1.soft_coding_keyflex_id
AND hsck1.SEGMENT1 = (hsck.segment1)
AND paaf2.assignment_type = 'E'
AND paaf2.primary_flag = 'Y'
AND paf.effective_start_date BETWEEN paaf2.effective_start_date
AND paaf2.effective_end_date)
,
(SELECT MIN(paaf2.assignment_number)
FROM per_all_assignments_f paaf2,
pay_all_payrolls_f papf1,
hr_soft_coding_keyflex hsck1
WHERE paaf2.person_id = paf.person_id
AND papf1.payroll_id = paaf2.payroll_id
AND papf1.soft_coding_keyflex_id = hsck1.soft_coding_keyflex_id
AND hsck1.SEGMENT1 = (hsck.segment1)
AND paaf2.assignment_type = 'E'
AND paf.effective_start_date BETWEEN paaf2.effective_start_date
AND paaf2.effective_end_date)
) primary_assignment_number
,hsck.segment1 paye_reference
FROM per_all_people_f pap
,per_all_assignments_f paf
,pay_all_payrolls_f papf
,hr_soft_coding_keyflex hsck
WHERE paf.person_id = pap.person_id
AND pap.person_id = p_person_id
AND papf.payroll_id=paf.payroll_id
AND papf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND paf.assignment_type = 'E'
AND pap.effective_start_date =
(
SELECT MAX(papf2.effective_start_date)
FROM per_all_people_f papf2
WHERE papf2.person_id = pap.person_id
)
AND paf.effective_start_date =
(
SELECT MAX(paaf2.effective_start_date)
FROM per_all_assignments_f paaf2
WHERE paaf2.assignment_id = paf.assignment_id
AND paaf2.assignment_type = 'E'
)
AND TRUNC(sysdate) BETWEEN papf.effective_start_date
AND papf.effective_end_date;
select paaf.assignment_id
,paaf.assignment_number
,paaf.business_group_id
,trim (papf.per_information9)
from per_all_assignments_f paaf
,per_all_people_f papf
where paaf.person_id = p_person_id
and papf.person_id = paaf.person_id
AND p_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND p_effective_date BETWEEN papf.effective_start_date
AND papf.effective_end_date;
hr_utility.trace ('leaving update_rti_agg_person');
END update_rti_agg_person;
procedure update_asg_rti_starter_flag(p_person_id IN per_all_people_f.person_id%TYPE,
p_effective_date IN DATE)
is
CURSOR csr_get_all_asg(p_business_group_id NUMBER)
is
select paaf.assignment_id assignment_id
,sck.segment1 paye_ref
from per_all_assignments_f paaf,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex sck
where paaf.person_id = p_person_id
and paaf.payroll_id = papf.payroll_id
and paaf.business_group_id = p_business_group_id
and papf.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and p_effective_date between papf.effective_start_date and papf.effective_end_date
and paaf.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = paaf.assignment_id
and asg2.assignment_type = 'E'
and asg2.effective_start_date <= p_effective_date
);
select per_information10 per_agg_flag
,business_group_id business_group_id
from per_all_people_f
where person_id = p_person_id
and p_effective_date between effective_start_date and effective_end_date;
select paaf.assignment_id assignment_id
from per_all_assignments_f paaf,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex sck
where paaf.person_id = p_person_id
and paaf.assignment_id <> p_assignment_id
and paaf.business_group_id = p_business_group_id
and paaf.payroll_id = papf.payroll_id
and papf.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and upper(sck.segment1) = upper(p_paye_ref)
and p_effective_date between papf.effective_start_date and papf.effective_end_date
and paaf.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = paaf.assignment_id
and asg2.assignment_type = 'E'
and asg2.effective_start_date <= p_effective_date
);
select assignment_extra_info_id aeid
,aei_information8 starter_flag
,aei_information9 pensioner_flag
,aei_information19 expat_flag
,object_version_number
FROM per_assignment_extra_info
WHERE assignment_id = c_assignment_id
AND information_type = 'GB_RTI_ASG_DETAILS';
select 'Y' from dual
where exists (select *
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.report_type in ('RTI_EAS_REP','RTI_EAS_REP_13','RTI_FPS_REP','RTI_FPS_REP_13')
and ppa.action_status = 'C'
and ppa.business_group_id = l_business_group_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = p_assignment_id
and paa.action_status = 'C');
l_proc CONSTANT VARCHAR2(40) := 'update_asg_rti_starter_flag';
l_update_rti_starter_flag VARCHAR2(1) := 'N';
l_update_rti_starter_flag := 'Y';
hr_utility.set_location('l_update_rti_starter_flag :'||l_update_rti_starter_flag,80);
IF l_update_rti_starter_flag = 'Y' THEN
hr_utility.set_location(' Setting the RTI_starter flag for p_assignment_id :'||l_asg_rec.assignment_id,90);
--update the rti sent flag for p_assignment_id
hr_assignment_extra_info_api.update_assignment_extra_info
(p_validate => false,
p_object_version_number => l_object_version_number,
p_assignment_extra_info_id => l_assignment_extra_info_id,
p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information8 => 'Y'
);
--update the rti sent flag for p_assignment_id
hr_assignment_extra_info_api.update_assignment_extra_info
(p_validate => false,
p_object_version_number => l_object_version_number,
p_assignment_extra_info_id => l_assignment_extra_info_id,
p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information8 => 'N'
);
END update_asg_rti_starter_flag;
PROCEDURE update_rti_starter_asg
(p_assignment_id IN per_all_assignments_f.assignment_id%type
,p_effective_date IN date) IS
p_person_id number;
SELECT DISTINCT
person_id
INTO p_person_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
per_asg_aggr.update_asg_rti_starter_flag (p_person_id
,p_effective_date);
END update_rti_starter_asg;