[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 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'
and paf.assignment_type='E';
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 upper(hsc.segment1) = upper(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 p_effective_date between pay.effective_start_date and pay.effective_end_date
and nvl(paf.primary_flag,'N')='Y'
and paf.assignment_type='E';
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 upper(hsc.segment1)= upper(p_paye_reference)
and paf.assignment_type='E';
SELECT trim (pap.per_information10) per_agg_flag
,trim (pap.per_information9) per_ni_flag
,nvl (paf.primary_flag
,'N') 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 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 1
INTO v_no
FROM per_all_assignments_f
WHERE person_id = p_person_id
AND nvl (primary_flag
,'N') = 'Y'
AND assignment_type = 'E'
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 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;
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;
pay_gb_rti_upd.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
pay_gb_rti_upd.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
FROM per_all_people_f pap
,per_all_assignments_f paf
WHERE paf.person_id = pap.person_id
AND pap.person_id = p_person_id
AND p_effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND p_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
hr_utility.trace ('leaving update_rti_agg_person');
END update_rti_agg_person;
SELECT DISTINCT
asg.assignment_id assignment_id
,trim (asg.primary_flag) asg_primary_flag
,trim (pap.per_information10) per_agg_flag
,trim (pap.per_information9) per_ni_flag
,pap.person_id person_id
,REGEXP_REPLACE(asg.assignment_number,'[]\#^}{_.@\[\$]','')
assignment_number
,asg.payroll_id payroll_id
,asg.effective_start_date assignment_start_date
,REGEXP_REPLACE( nvl(
(SELECT MIN(paaf2.assignment_number)
FROM per_all_assignments_f paaf2,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex hsck
WHERE paaf2.person_id = asg.person_id
AND papf.payroll_id = paaf2.payroll_id
AND papf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND upper(hsck.SEGMENT1) = upper(tax_ref_no)
AND paaf2.assignment_type = 'E'
AND paaf2.primary_flag = 'Y'
AND asg.effective_start_date BETWEEN paaf2.effective_start_date
AND paaf2.effective_end_date
AND pay.effective_start_date BETWEEN papf.effective_start_date
AND papf.effective_end_date)
,
(SELECT MIN(paaf2.assignment_number)
FROM per_all_assignments_f paaf2,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex hsck
WHERE paaf2.person_id = asg.person_id
AND papf.payroll_id = paaf2.payroll_id
AND papf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND upper(hsck.SEGMENT1) = upper(tax_ref_no)
AND paaf2.assignment_type = 'E'
AND asg.effective_start_date BETWEEN paaf2.effective_start_date
AND paaf2.effective_end_date
AND pay.effective_start_date BETWEEN papf.effective_start_date
AND papf.effective_end_date)),'[]\#^}{_.@\[\$]','') primary_assignment_number
,pap.employee_number
FROM per_all_people_f pap
,per_all_assignments_f asg
,per_assignment_status_types past
--,per_periods_of_service serv
,pay_all_payrolls_f pay
,hr_soft_coding_keyflex sck
WHERE --pap.current_employee_flag = 'Y'
pap.person_id = asg.person_id
AND asg.business_group_id = l_business_group_id
AND asg.assignment_status_type_id = past.assignment_status_type_id
--AND past.per_system_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN','TERM_ASSIGN')
AND asg.payroll_id = pay.payroll_id
--AND asg.period_of_service_id = serv.period_of_service_id
AND pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
AND upper (tax_ref_no) = upper (sck.segment1)
AND asg.assignment_type = 'E'
AND fnd_date.canonical_to_date(p_effective_date) between pap.effective_start_date
and pap.effective_end_date
AND fnd_date.canonical_to_date(p_effective_date) between asg.effective_start_date
and asg.effective_end_date
AND fnd_date.canonical_to_date(p_effective_date) between pay.effective_start_date
and pay.effective_end_date
/*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 asg.effective_start_date =
(
SELECT MAX(paaf2.effective_start_date)
FROM per_all_assignments_f paaf2
WHERE paaf2.assignment_id = asg.assignment_id
AND paaf2.assignment_type = 'E'
)
AND asg.effective_end_date >= cp_soy_date
AND TRUNC(sysdate) BETWEEN pay.effective_start_date
AND pay.effective_end_date*/
ORDER BY person_id
,asg_primary_flag DESC;
SELECT paei.assignment_extra_info_id assignment_extra_info_id
,paei.assignment_id assignment_id
,paei.aei_information3 assignment_number
,paei.aei_information4 payroll_id
,paei.object_version_number object_version_number
FROM per_assignment_extra_info paei
WHERE paei.assignment_id = v_assignment_id
AND information_type = 'GB_RTI_AGGREGATION';
hr_utility.trace('For this assignment extra_information doesnt exists hence inserting it');
SELECT per_assignment_extra_info_s.nextval
INTO v_assignment_extra_info_id
FROM dual;
hr_utility.trace('Calling the Extra Information Insert API...');
hr_utility.trace('Back to main program after insert api call...');