[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_sql := ' SELECT distinct p.person_id' ||
' FROM per_people_f p,' ||
' pay_payroll_actions pa ' ||
' WHERE pa.payroll_action_id = :payroll_action_id' ||
' AND p.business_group_id = pa.business_group_id' ||
' ORDER BY p.person_id';
/* Bug 3229452 - Used fnd_date.canonical_to_date instead of to_date for selecting
report end date from ppa.legislative paramenters */
/* Bug 4215439 - Added ORDERED hint TO the assignment cursor*/
/*Bug 5348307 - Commented condition
c_report_end_date BETWEEN paa.effective_start_date AND paa.effective_end_date
and condition
paa.effective_start_date between c_report_end_date - 13 and c_report_end_date */
CURSOR process_assignments
(c_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE,
c_start_person_id in per_all_people_f.person_id%TYPE,
c_end_person_id in per_all_people_f.person_id%TYPE,
c_business_group_id in per_business_groups.business_group_id%TYPE,
c_legal_employer_id in hr_soft_coding_keyflex.segment1%TYPE,
c_report_end_date in date) IS
SELECT /*+ ORDERED */ paa.assignment_id,
decode(sign(nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) - c_report_end_date),1,
null,pps.actual_termination_date) actual_termination_date,
peev.screen_entry_value tax_file_number
FROM pay_payroll_actions ppa
,per_people_f pap
,per_assignments_f paa
,hr_soft_coding_keyflex hsc
,per_periods_of_service pps
,pay_element_entries_f pee
,pay_element_links_f pel
,pay_element_types_f pet
,pay_input_values_f piv
,pay_element_entry_values_f peev
WHERE ppa.payroll_action_id = c_payroll_action_id
AND pap.person_id BETWEEN c_start_person_id AND c_end_person_id
AND pap.person_id = paa.person_id
AND paa.business_group_id = c_business_group_id
AND pap.business_group_id = ppa.business_group_id
AND paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
AND hsc.segment1 = c_legal_employer_id
AND pps.person_id = paa.person_id
AND pps.date_start= (select max(pps1.date_start)
from per_periods_of_service pps1
where pps1.person_id=pps.person_id
AND pps1.date_start <= c_report_end_date
) /*Bug2751008*/
AND paa.effective_start_date = (SELECT max(effective_Start_date)
FROM per_assignments_f a
WHERE a.assignment_id = paa.assignment_id
and a.effective_start_date <= c_report_end_Date /*5474358 */
group by a.assignment_id
)
AND pap.effective_start_date = (SELECT max(effective_Start_date)
FROM per_people_f p
WHERE p.person_id = pap.person_id
and p.effective_start_Date <= c_report_end_date /*5474358 */
group by p.person_id)
AND pet.element_name = 'Tax Information'
AND pel.element_type_id = pet.element_type_id
AND pee.element_link_id = pel.element_link_id
AND pee.assignment_id = paa.assignment_id
AND pee.entry_information_category = 'AU_TAX DEDUCTIONS'
AND ((trunc(fnd_date.canonical_to_date(pee.entry_information1)) BETWEEN c_report_end_date -13 AND c_report_end_date
and pps.actual_termination_date is null)
OR (nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) BETWEEN c_report_end_date - 13 AND c_report_end_date and peev.screen_entry_value = '111 111 111'))
AND piv.name = 'Tax File Number'
AND piv.element_type_id = pet.element_type_id
AND peev.input_value_id = piv.input_value_id
AND peev.element_entry_id = pee.element_entry_id
AND pee.effective_start_date =
(SELECT max(pee1.effective_start_date)
FROM pay_element_types_f pet1
,pay_element_links_f pel1
,pay_element_entries_f pee1
WHERE pet1.element_name = 'Tax Information'
AND pet1.element_type_id = pel1.element_type_id
AND pel1.element_link_id = pee1.element_link_id
AND pee1.assignment_id = paa.assignment_id
AND pee1.entry_information1 is not null /*Bug 5356467*/
AND pee1.effective_start_date <= c_report_end_date
AND pel1.effective_start_date BETWEEN pet1.effective_start_date
AND pet1.effective_end_date
)
AND peev.effective_start_date = (SELECT max(peev1.effective_start_date)
FROM pay_element_entry_values_f peev1
WHERE peev1.element_entry_value_id = peev.element_entry_value_id
AND peev1.effective_start_date <= c_report_end_date)
/* 4620635 */
AND ((
/* Begin commented for bug 5348307*/
/* c_report_end_date BETWEEN paa.effective_start_date AND paa.effective_end_date
and*/
/* End commented for bug 5348307*/
pps.actual_termination_date is null)
OR (nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY'))
BETWEEN c_report_end_date - 13 AND c_report_end_date and peev.screen_entry_value = '111 111 111'
/* Begin commented for bug 5348307*/
/* and paa.effective_start_date between c_report_end_date - 13 and c_report_end_date */
/* End commented for bug 5348307*/
))
AND c_report_end_date BETWEEN pap.effective_start_date AND pap.effective_end_date /* 4620635 */
AND c_report_end_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND c_report_end_date BETWEEN pel.effective_start_date AND pel.effective_end_date
AND c_report_end_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND NOT EXISTS
(SELECT 1
FROM pay_payroll_actions ppa,
pay_assignment_actions pac
WHERE pac.assignment_id = paa.assignment_id
AND ppa.payroll_Action_id = pac.payroll_action_id
AND fnd_date.canonical_to_date(pay_core_utils.get_parameter('REPORT_END_DATE',ppa.legislative_parameters))
BETWEEN c_report_end_date - 13 AND c_report_end_date
AND pac.action_status = 'C'
AND ppa.action_TYPE = 'X'
AND ppa.report_TYPE = 'AU_TFN_MAGTAPE');
/* Bug 3229452 - Used fnd_date.canonical_to_date instead of to_date for selecting
report end date from ppa.legislative paramenters */
CURSOR c_get_parameters( c_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE) IS
SELECT
pay_core_utils.get_parameter('BUSINESS_GROUP_ID',ppa.legislative_parameters),
pay_core_utils.get_parameter('LEGAL_EMPLOYER',ppa.legislative_parameters),
fnd_date.canonical_to_date(pay_core_utils.get_parameter('REPORT_END_DATE',ppa.legislative_parameters))-1
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = c_payroll_action_id;
SELECT pay_assignment_actions_s.nextval
FROM dual;
hr_utility.trace(' In the assignment action insertion ');
/* Bug 3229452 - Used fnd_date.canonical_to_date instead of to_date for selecting
report end date from ppa.legislative paramenters */
CURSOR c_get_parameters( c_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE) IS
SELECT
pay_core_utils.get_parameter('BUSINESS_GROUP_ID',ppa.legislative_parameters),
pay_core_utils.get_parameter('LEGAL_EMPLOYER',ppa.legislative_parameters),
fnd_date.canonical_to_date(pay_core_utils.get_parameter('REPORT_END_DATE',ppa.legislative_parameters))-1
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = c_payroll_action_id;