[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_ni_category_pension(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_paye_ref in varchar2,
p_bg_id IN NUMBER,
p_payroll_id in number,
p_f_category in varchar2,
p_g_category in varchar2,
p_s_category in varchar2,
p_mode in varchar2
)
IS
/*** Local variables ***/
l_ni_new_category varchar2(1) := ' ';
SELECT max (decode (pivf.name
,'Category'
,pivf.input_value_id
,NULL)) input_category
,max (decode (pivf.name
,'Pension'
,pivf.input_value_id
,NULL)) input_pension
FROM pay_element_types_f petf
,pay_input_values_f pivf
WHERE petf.element_name = 'NI'
AND petf.legislation_code = 'GB'
AND petf.element_type_id = pivf.element_type_id
AND pivf.name IN ('Category','Pension')
AND SYSDATE BETWEEN petf.effective_start_date
AND pivf.effective_end_date
AND SYSDATE BETWEEN pivf.effective_start_date
AND pivf.effective_end_date;
Cursor retrieves all the element entries that are to be updated for a given
business group, paye reference and payroll.
Retrieve all the element entries which have the NI categories F,G,S
and Ni Pension basis 'M' after 06-Apr-2012.
Inner query retrieves all the NI elements with Categories F,G,S and
also all the NI elements which has pension basis 'A' which is an obsoleted pension
basis from 06-APR-2012. Apart from this it also fetches the NI categories A,B,J with
pension basis value as null , we get null value for pension basis because the pension basis for A,B,J
can be either A or N, but we dint retrieve rows with the screen entry value 'N'.
Outer query filters these null values./
*/
CURSOR csr_element_entries_for_update(
c_bg_id NUMBER,
c_paye_ref VARCHAR2,
c_payroll_id NUMBER
)
IS
SELECT ele_entries.*
FROM
(
SELECT peevf.element_entry_id
,peevf.effective_start_date
,peevf.effective_end_date
,max (decode (peevf.input_value_id
,l_input_value_id_category
,peevf.screen_entry_value
,NULL)) pension_category
,max (decode (peevf.input_value_id
,l_input_value_id_pension
,peevf.screen_entry_value
,NULL)) pension_basis
,paaf.assignment_id
,papf.sex
,papf.full_name
,paaf.assignment_number
,org.org_information1
,ppf.payroll_id
FROM per_all_assignments_f paaf
,per_all_people_f papf
,pay_payrolls_f ppf
,pay_element_entries_f peef
,pay_element_entry_values_f peevf
,hr_organization_information org
,hr_soft_coding_keyflex flex
WHERE org.organization_id = paaf.business_group_id
AND paaf.business_group_id = nvl (c_bg_id
,paaf.business_group_id)
AND paaf.business_group_id = papf.business_group_id
AND nvl (org.org_information10
,'UK') = 'UK'
AND org.org_information_context = 'Tax Details References'
AND org.org_information1 = flex.segment1
AND org.org_information1 = nvl (c_paye_ref
,org.org_information1)
AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND ppf.payroll_id = nvl (c_payroll_id
,ppf.payroll_id)
AND paaf.payroll_id = ppf.payroll_id
AND paaf.assignment_id = peef.assignment_id
AND papf.person_id = paaf.person_id
AND peef.element_entry_id = peevf.element_entry_id
AND peevf.input_value_id IN (l_input_value_id_category,l_input_value_id_pension)
AND peevf.screen_entry_value IN ('F','G','S'
,'A','B','J'
,'M')
AND l_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND l_effective_date BETWEEN peef.effective_start_date
AND peef.effective_end_date
AND l_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND l_effective_date BETWEEN peevf.effective_start_date
AND peevf.effective_end_date
AND l_effective_date BETWEEN papf.effective_start_date
AND papf.effective_end_date
GROUP BY peevf.element_entry_id
,peevf.effective_start_date
,peevf.effective_end_date
,paaf.assignment_id
,papf.sex
,papf.full_name
,paaf.assignment_number
,org.org_information1
,ppf.payroll_id
) ele_entries
WHERE ele_entries.pension_basis IS NOT NULL
ORDER BY ele_entries.full_name, ele_entries.payroll_id;
hr_utility.trace('Entering ' || p_package_name || '.update_ni_category_pension');
FOR l_element_entry in csr_element_entries_for_update(p_bg_id, p_paye_ref,p_payroll_id)
LOOP
-- determine the new category and new pension basis for the this element entry.
if l_element_entry.pension_category = 'F' THEN
l_ni_new_category := p_f_category;
else -- Pension category would be either A,B,J. Then we should just update pension basis.
l_ni_new_category := l_element_entry.pension_category;
-- Call the update api in UPDATE_OVERRIDE mode for updating the element entries for future entries as well.
hr_entry_api.update_element_entry(p_dt_update_mode => 'UPDATE_OVERRIDE',
p_session_date => l_effective_date,
p_element_entry_id => l_element_entry.element_entry_id,
p_input_value_id1 => l_input_value_id_category,
p_input_value_id2 => l_input_value_id_pension,
p_entry_value1 => l_ni_new_category,
p_entry_value2 => pen_basis(l_ni_new_pension)
);
hr_utility.trace(l_element_entry.assignment_id ||' ' || l_element_entry.element_entry_id|| ' Updated Successfully');
hr_utility.trace('Leaving ' || p_package_name || '.update_ni_catergory_and_pension');
fnd_file.put_line(FND_FILE.OUTPUT,rpad('-',68,'-')||'No records updated'||rpad('-',69,'-'));
END update_ni_category_pension;