[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 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;
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)
IS
BEGIN
--
-- Added for GSI Bug 5472781
--
IF hr_utility.chk_product_install('Oracle Human Resources', 'GB') THEN
--
--If aggregate assignment flag is 'Y'
IF p_per_information10 = 'Y' THEN
-- Check if 'NI Multiple assignments' flag is 'Y'
IF p_per_information9 = 'Y' THEN
-- Check for multiple assignments and same tax district
IF NOT assg_aggr_possible (p_person_id , p_effective_date,'Y') THEN
hr_utility.raise_error;