[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_RTI_P60
Source
1 PACKAGE BODY PAY_GB_RTI_P60 AS
2 /* $Header: pygbrtip60.pkb 120.0.12020000.2 2013/02/11 18:11:16 krreddy noship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================
8 Name:
9 PAY_GB_RTI_P60
10 Purpose:
11 It fetches the live data and archives it for RTI P60 Process.
12 This is a UK Specific payroll package.
13 History:
14 29-Dec-2012 krreddy 115.0 14759137 Created.
15 02-Jan-2013 krreddy 115.1 14759137 Modified to fix No Data Found error.
16 07-Jan-2013 krreddy 115.2 14759137 Modified to remove pence conversion
17 for amount fields.
18 09-Jan-2013 krreddy 115.3 14759137 Modified to update the logic for
19 name_and_initials, w1_m1_indicator
20 and NI Employee YTD values.
21 10-Jan-2013 ssarap 115.4 16102030 Archiving date of birth , sex and tax year.
22 17-Jan-2013 krreddy 115.5 16175074 Modified for the below fields:
23 w1_m1_indicator, week_53_indicator, refund_flag etc.
24 24-Jan-2013 krreddy 115.6 16076312 Modified for the below:
25 PAYE Aggregation logic
26 NI Aggregation logic
27 25-Jan-2013 krreddy 115.7 16076312 Modified to fix No Data Found error and remove unnecessary variables.
28 29-Jan-2013 ssarap 115.8 Added the function get_column_value to be used in PYGBRTIP60OP.xml report.
29 =============================================================================*/
30 --
31 --
32 g_package CONSTANT VARCHAR2(20) := 'pay_gb_rti_fps.';
33 g_start_year DATE;
34 g_end_year DATE;
35 g_effective_date DATE;
36 g_fps_effective_date DATE;
37 g_fps_start_year DATE;
38 g_fps_end_year DATE;
39 g_business_group_id hr_organization_units.business_group_id%TYPE;
40 g_legislation_code VARCHAR2(2) := 'GB';
41 g_pre_pact_id NUMBER;
42 g_first_fps VARCHAR2(30);
43 g_paye_element_id NUMBER:= -1;
44 g_paye_details_id NUMBER:= -1;
45 g_payroll_action_id NUMBER;
46 g_date_paid DATE;
47 g_asg_primary_flag VARCHAR2(1);
48 g_per_agg_flag VARCHAR2(1);
49 g_ni_pt_eid NUMBER:=0;
50 g_ni_pt_ivid NUMBER:=0;
51 g_fps_bal_det_tab t_fps_bal_det_tab;
52 g_defined_balance_lst pay_balance_pkg.t_balance_value_tab; -- used for batch balance retrieval of normal balances
53 g_tax_ref VARCHAR2(20);
54 g_payroll_id NUMBER;
55 --
56 g_nia_able_id pay_defined_balances.defined_balance_id%TYPE;
57 g_nia_able_id_run pay_defined_balances.defined_balance_id%TYPE;
58 g_nia_tot_id pay_defined_balances.defined_balance_id%TYPE;
59 g_nia_lel_id pay_defined_balances.defined_balance_id%TYPE;
60 g_nia_uel_id pay_defined_balances.defined_balance_id%TYPE;
61 g_nia_uap_id pay_defined_balances.defined_balance_id%TYPE;
62 g_nia_auel_id pay_defined_balances.defined_balance_id%TYPE;
63 g_nia_et_id pay_defined_balances.defined_balance_id%TYPE;
64 g_nib_able_id pay_defined_balances.defined_balance_id%TYPE;
65 g_nib_able_id_run pay_defined_balances.defined_balance_id%TYPE;
66 g_nib_tot_id pay_defined_balances.defined_balance_id%TYPE;
67 g_nib_lel_id pay_defined_balances.defined_balance_id%TYPE;
68 g_nib_uel_id pay_defined_balances.defined_balance_id%TYPE;
69 g_nib_uap_id pay_defined_balances.defined_balance_id%TYPE;
70 g_nib_auel_id pay_defined_balances.defined_balance_id%TYPE;
71 g_nib_et_id pay_defined_balances.defined_balance_id%TYPE;
72 g_nic_able_id pay_defined_balances.defined_balance_id%TYPE;
73 g_nic_able_id_run pay_defined_balances.defined_balance_id%TYPE;
74 g_nic_tot_id pay_defined_balances.defined_balance_id%TYPE;
75 g_nic_lel_id pay_defined_balances.defined_balance_id%TYPE;
76 g_nic_uel_id pay_defined_balances.defined_balance_id%TYPE;
77 g_nic_uap_id pay_defined_balances.defined_balance_id%TYPE;
78 g_nic_auel_id pay_defined_balances.defined_balance_id%TYPE;
79 g_nic_et_id pay_defined_balances.defined_balance_id%TYPE;
80 g_nic_ers_rebate_id pay_defined_balances.defined_balance_id%TYPE;
81 g_nid_able_id pay_defined_balances.defined_balance_id%TYPE;
82 g_nid_able_id_run pay_defined_balances.defined_balance_id%TYPE;
83 g_nid_tot_id pay_defined_balances.defined_balance_id%TYPE;
84 g_nid_lel_id pay_defined_balances.defined_balance_id%TYPE;
85 g_nid_uel_id pay_defined_balances.defined_balance_id%TYPE;
86 g_nid_uap_id pay_defined_balances.defined_balance_id%TYPE;
87 g_nid_auel_id pay_defined_balances.defined_balance_id%TYPE;
88 g_nid_et_id pay_defined_balances.defined_balance_id%TYPE;
89 g_nid_ers_rebate_id pay_defined_balances.defined_balance_id%TYPE;
90 g_nid_ees_rebate_id pay_defined_balances.defined_balance_id%TYPE;
91 g_nid_rebate_emp_id pay_defined_balances.defined_balance_id%TYPE;
92 g_nie_able_id pay_defined_balances.defined_balance_id%TYPE;
93 g_nie_able_id_run pay_defined_balances.defined_balance_id%TYPE;
94 g_nie_tot_id pay_defined_balances.defined_balance_id%TYPE;
95 g_nie_lel_id pay_defined_balances.defined_balance_id%TYPE;
96 g_nie_uel_id pay_defined_balances.defined_balance_id%TYPE;
97 g_nie_uap_id pay_defined_balances.defined_balance_id%TYPE;
98 g_nie_auel_id pay_defined_balances.defined_balance_id%TYPE;
99 g_nie_et_id pay_defined_balances.defined_balance_id%TYPE;
100 g_nie_ers_rebate_id pay_defined_balances.defined_balance_id%TYPE;
101 g_nig_tot_id pay_defined_balances.defined_balance_id%TYPE;
102 g_nij_able_id pay_defined_balances.defined_balance_id%TYPE;
103 g_nij_able_id_run pay_defined_balances.defined_balance_id%TYPE;
104 g_nij_tot_id pay_defined_balances.defined_balance_id%TYPE;
105 g_nij_lel_id pay_defined_balances.defined_balance_id%TYPE;
106 g_nij_uel_id pay_defined_balances.defined_balance_id%TYPE;
107 g_nij_uap_id pay_defined_balances.defined_balance_id%TYPE;
108 g_nij_auel_id pay_defined_balances.defined_balance_id%TYPE;
109 g_nij_et_id pay_defined_balances.defined_balance_id%TYPE;
110 g_nil_able_id pay_defined_balances.defined_balance_id%TYPE;
111 g_nil_able_id_run pay_defined_balances.defined_balance_id%TYPE;
112 g_nil_tot_id pay_defined_balances.defined_balance_id%TYPE;
113 g_nil_lel_id pay_defined_balances.defined_balance_id%TYPE;
114 g_nil_uel_id pay_defined_balances.defined_balance_id%TYPE;
115 g_nil_uap_id pay_defined_balances.defined_balance_id%TYPE;
116 g_nil_auel_id pay_defined_balances.defined_balance_id%TYPE;
117 g_nil_et_id pay_defined_balances.defined_balance_id%TYPE;
118 g_nia_e_ytd_id pay_defined_balances.defined_balance_id%TYPE;
119 g_nia_e_ptd_id pay_defined_balances.defined_balance_id%TYPE;
120 g_nia_r_ytd_id pay_defined_balances.defined_balance_id%TYPE;
121 g_nia_r_ptd_id pay_defined_balances.defined_balance_id%TYPE;
122 g_nib_e_ytd_id pay_defined_balances.defined_balance_id%TYPE;
123 g_nib_e_ptd_id pay_defined_balances.defined_balance_id%TYPE;
124 g_nib_r_ytd_id pay_defined_balances.defined_balance_id%TYPE;
125 g_nib_r_ptd_id pay_defined_balances.defined_balance_id%TYPE;
126 g_nic_e_ytd_id pay_defined_balances.defined_balance_id%TYPE;
127 g_nic_e_ptd_id pay_defined_balances.defined_balance_id%TYPE;
128 g_nic_r_ytd_id pay_defined_balances.defined_balance_id%TYPE;
129 g_nic_r_ptd_id pay_defined_balances.defined_balance_id%TYPE;
130 g_nid_e_ytd_id pay_defined_balances.defined_balance_id%TYPE;
131 g_nid_e_ptd_id pay_defined_balances.defined_balance_id%TYPE;
132 g_nid_r_ytd_id pay_defined_balances.defined_balance_id%TYPE;
133 g_nid_r_ptd_id pay_defined_balances.defined_balance_id%TYPE;
134 g_nie_e_ytd_id pay_defined_balances.defined_balance_id%TYPE;
135 g_nie_e_ptd_id pay_defined_balances.defined_balance_id%TYPE;
136 g_nie_r_ytd_id pay_defined_balances.defined_balance_id%TYPE;
137 g_nie_r_ptd_id pay_defined_balances.defined_balance_id%TYPE;
138 g_nij_e_ytd_id pay_defined_balances.defined_balance_id%TYPE;
139 g_nij_e_ptd_id pay_defined_balances.defined_balance_id%TYPE;
140 g_nij_r_ytd_id pay_defined_balances.defined_balance_id%TYPE;
141 g_nij_r_ptd_id pay_defined_balances.defined_balance_id%TYPE;
142 g_nil_e_ytd_id pay_defined_balances.defined_balance_id%TYPE;
143 g_nil_e_ptd_id pay_defined_balances.defined_balance_id%TYPE;
144 g_nil_r_ytd_id pay_defined_balances.defined_balance_id%TYPE;
145 g_nil_r_ptd_id pay_defined_balances.defined_balance_id%TYPE;
146
147 PROCEDURE remove_null_address_lines(p_address_line1 IN OUT NOCOPY VARCHAR2,
148 p_address_line2 IN OUT NOCOPY VARCHAR2,
149 p_address_line3 IN OUT NOCOPY VARCHAR2,
150 p_address_line4 IN OUT NOCOPY VARCHAR2)
151 IS
152 --
153 TYPE t_lines IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
154 l_lines t_lines;
155 l_dummy VARCHAR2(1000);
156 l_proc VARCHAR2(100) := 'pay_gb_eoy_archive.remove_null_address_lines';
157 --
158 BEGIN
159 hr_utility.set_location('Entering: '||l_proc,1);
160 hr_utility.trace('p_address_line1='||p_address_line1);
161 hr_utility.trace('p_address_line2='||p_address_line2);
162 hr_utility.trace('p_address_line3='||p_address_line3);
163 hr_utility.trace('p_address_line4='||p_address_line4);
164 --
165 l_lines(1) := p_address_line1;
166 l_lines(2) := p_address_line2;
167 l_lines(3) := p_address_line3;
168 l_lines(4) := p_address_line4;
169 --
170 FOR i IN 1..3 LOOP
171 FOR j IN 1..(4-i) LOOP
172 IF l_lines(j) IS NULL and l_lines(j+1) IS NOT NULL THEN
173 l_lines(j) := l_lines(j+1);
174 l_lines(j+1) := NULL;
175 END IF;
176 END LOOP;
177 END LOOP;
178 --
179 hr_utility.set_location(l_proc,10);
180 --
181 p_address_line1 := l_lines(1);
182 p_address_line2 := l_lines(2);
183 p_address_line3 := l_lines(3);
184 p_address_line4 := l_lines(4);
185 --
186 hr_utility.set_location('Leaving: '||l_proc,20);
187 hr_utility.trace('p_address_line1='||p_address_line1);
188 hr_utility.trace('p_address_line2='||p_address_line2);
189 hr_utility.trace('p_address_line3='||p_address_line3);
190 hr_utility.trace('p_address_line4='||p_address_line4);
191 END;
192 --
193
194
195 --
196 FUNCTION fetch_p60_payroll_det(
197 p_pactid IN NUMBER,
198 p_start_date IN DATE,
199 p_end_date IN DATE
200 --p_payroll_rec OUT nocopy act_info_rec
201 )
202
203 RETURN BOOLEAN
204 IS
205
206 CURSOR csr_parameter_info
207 IS
208 SELECT to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id,
209 SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
210 effective_date eff_date,
211 business_group_id bg_id
212 FROM pay_payroll_actions pact
213 WHERE payroll_action_id = p_pactid;
214
215 cursor csr_payrolls(p_bg_id NUMBER, p_tax_ref VARCHAR2, p_end_year DATE) IS
216 SELECT
217 distinct substr(flex.segment1,1,3) tax_district_reference,
218 substr(ltrim(substr(org_information1,4,11),'/') ,1,10) tax_reference,
219 flex.segment1 emp_paye_ref,
220 substr(org.org_information2 ,1,40) tax_district_name,
221 substr(ltrim(org.org_information3),1,36) employers_name,
222 substr(ltrim(org.org_information4),1,60) employers_address_line
223 FROM pay_all_payrolls_f p,
224 hr_soft_coding_keyflex flex,
225 hr_organization_information org
226 WHERE p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
227 AND org.org_information_context = 'Tax Details References'
228 AND org.org_information1 = flex.segment1
229 AND NVL(org.org_information10,'UK') = 'UK'
230 AND flex.segment10 IS NOT NULL
231 AND p.business_group_id = p_bg_id
232 AND org.organization_id = p_bg_id
233 AND p_end_year BETWEEN p.effective_start_date
234 AND p.effective_end_date
235 AND org.org_information1 = p_tax_ref;
236
237
238 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_p60_payroll_det';
239 l_arch BOOLEAN;
240 l_asg_id number;
241 l_payroll_rec csr_payrolls%rowtype;
242
243 l_payroll_id NUMBER;
244 l_tax_ref VARCHAR2(20);
245 l_effective_date DATE;
246 l_business_group_id NUMBER;
247 l_action_info_id NUMBER(15);
248 l_ovn NUMBER;
249
250 BEGIN
251
252 hr_utility.set_location('Entering '|| l_proc, 10);
253 l_arch := TRUE;
254
255 OPEN csr_parameter_info;
256 FETCH csr_parameter_info
257 INTO l_payroll_id,
258 l_tax_ref,
259 l_effective_date,
260 l_business_group_id;
261 CLOSE csr_parameter_info;
262
263 OPEN csr_payrolls(l_business_group_id, l_tax_ref, l_effective_date);
264 FETCH csr_payrolls
265 INTO l_payroll_rec;
266 CLOSE csr_payrolls;
267
268 hr_utility.trace('Assigning values to p_payroll_rec');
269 hr_utility.trace('l_payroll_rec.tax_district_reference '||l_payroll_rec.tax_district_reference);
270 hr_utility.trace('l_payroll_rec.tax_reference '||l_payroll_rec.tax_reference);
271 hr_utility.trace('l_payroll_rec.tax_district_name '||l_payroll_rec.tax_district_name);
272 hr_utility.trace('l_payroll_rec.employers_name '||l_payroll_rec.employers_name);
273 hr_utility.trace('l_payroll_rec.employers_address_line '||l_payroll_rec.employers_address_line);
274
275 pay_action_information_api.create_action_information ( p_action_information_id => l_action_info_id ,
276 p_action_context_id => p_pactid ,
277 p_action_context_type => 'PA' ,
278 p_object_version_number => l_ovn ,
279 p_source_id => NULL ,
280 p_source_text => NULL ,
281 p_action_information_category => 'GB RTI P60 PAYROLL DET' ,
282 p_action_information1 => lpad(to_char(l_payroll_rec.tax_district_reference),3,'0')|| ' ' || l_payroll_rec.tax_reference ,
283 p_action_information2 => l_payroll_rec.tax_district_name ,
284 p_action_information3 => l_payroll_rec.employers_name ,
285 p_action_information4 => l_payroll_rec.employers_address_line);
286
287 hr_utility.trace('After archiving data for GB RTI P60 PAYROLL DET');
288 RETURN l_arch;
289
290 EXCEPTION
291 WHEN OTHERS THEN
292 hr_utility.set_location('Error in function fetch_p60_payroll_det ', 15);
293 hr_utility.trace('Exception:' || SQLCODE || ' - ' || SQLERRM );
294 fnd_file.put_line(fnd_file.LOG,'Exception:' || SQLCODE || ' - ' || SQLERRM);
295 RAISE;
296 END fetch_p60_payroll_det;
297
298 --
299 FUNCTION fetch_p60_emp_det(
300 p_assactid IN NUMBER,
301 p_pactid IN NUMBER,
302 p_person_id IN NUMBER,
303 p_asg_id IN NUMBER,
304 p_start_date IN DATE,
305 p_end_date IN DATE,
306 p_emp_rec OUT nocopy act_info_rec)
307 RETURN BOOLEAN
308 IS
309
310 cursor csr_person_info(p_person_id NUMBER) IS
311 SELECT substr(last_name, 1,35) last_name,
312 substr(first_name, 1,35) first_name,
313 substr(middle_names,1,35) middle_names,
314 date_of_birth, title,
315 substr(expense_check_send_to_address,1,1) expense_check_send_to_address,
316 substr(national_identifier,1,9) national_identifier,
317 substr(sex,1,1) sex ,
318 decode(substr(per_information4,1,1),'Y','P',' ') pensioner_indicator,
319 decode(per_information10,'Y','Y',NULL) agg_paye_flag,
320 decode(per_information9,'Y','Y',NULL) multiple_asg_flag,
321 per.employee_number employee_number,
322 per.EXPENSE_CHECK_SEND_TO_ADDRESS
323 FROM per_all_people_f per
324 WHERE per.person_id = p_person_id
325 AND g_end_year BETWEEN per.effective_start_date
326 AND per.effective_end_date;
327
328 cursor csr_addresses(p_person_id NUMBER) IS
329 SELECT ltrim(rtrim(pad.address_line1)) address_line1,
330 ltrim(rtrim(pad.address_line2)) address_line2,
331 ltrim(rtrim(pad.address_line3)) address_line3,
332 ltrim(rtrim(pad.town_or_city)) town_or_city,
333 substr(l.meaning,1,27) county,
334 substr(pad.postal_code,1,8),
335 country
336 FROM per_addresses pad,
337 hr_lookups l
338 WHERE pad.person_id = p_person_id
339 AND pad.primary_flag = 'Y'
340 AND l.lookup_type(+) = 'GB_COUNTY'
341 AND l.lookup_code(+) = pad.region_1
342 AND sysdate BETWEEN nvl(pad.date_from, sysdate)
343 AND nvl(pad.date_to, sysdate);
344 --
345 cursor csr_country_name(p_country_code VARCHAR2) IS
346 SELECT substr(ftt.territory_short_name, 1, 35) country -- 4011263
347 FROM fnd_territories_tl ftt
348 WHERE ftt.territory_code = p_country_code
349 AND ftt.language = userenv('LANG');
350
351 CURSOR csr_asg_num
352 IS
353 SELECT asg.assignment_number,
354 asg.people_group_id
355 FROM per_all_assignments_f asg
356 WHERE asg.assignment_id = p_asg_id
357 AND sysdate BETWEEN asg.effective_start_date AND asg.effective_end_date;
358
359
360 --
361
362 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_p60_emp_det';
363 l_arch BOOLEAN;
364 l_asg_id number;
365
366 l_last_name per_all_people_f.last_name%TYPE;
367 l_first_name per_all_people_f.first_name%TYPE;
368 l_middle_name per_all_people_f.middle_names%TYPE;
369 l_date_of_birth DATE;
370 l_title per_all_people_f.title%TYPE;
371 l_expense_check_to_address per_all_people_f.expense_check_send_to_address%TYPE;
372 l_ni_number per_all_people_f.national_identifier%TYPE;
373 l_sex per_all_people_f.sex%TYPE;
374 l_pensioner_indicator VARCHAR2(1);
375 l_aggregated_paye_flag VARCHAR2(1);
376 l_multiple_asg_flag VARCHAR2(1);
377 l_employee_number per_all_people_f.employee_number%TYPE;
378 l_address_line1 per_addresses.address_line1%type;
379 l_address_line2 per_addresses.address_line2%type;
380 l_address_line3 per_addresses.address_line3%type;
381 l_town_or_city VARCHAR2(30);
382 l_county VARCHAR2(27);
383 l_country_name fnd_territories_tl.territory_short_name%type;
384 l_country per_addresses.country%type;
385 l_postal_code VARCHAR2(8);
386 l_name_and_initials VARCHAR2(100);
387 l_profile_name_format VARCHAR2(100);
388 l_assignment_number VARCHAR2(40);
389 l_first_name_str VARCHAR2(40);
390 l_middle_name_str VARCHAR2(40);
391 l_title_str VARCHAR2(40);
392 l_expense_check VARCHAR2(10);
393 l_people_group_flex number;
394 --
395
396 BEGIN
397 hr_utility.set_location('Entering '|| l_proc, 10);
398 l_arch := true;
399 OPEN csr_person_info(p_person_id);
400 FETCH csr_person_info INTO l_last_name,
401 l_first_name,
402 l_middle_name,
403 l_date_of_birth,
404 l_title,
405 l_expense_check_to_address,
406 l_ni_number,
407 l_sex,
408 l_pensioner_indicator,
409 l_aggregated_paye_flag,
410 l_multiple_asg_flag,
411 l_employee_number,
412 l_expense_check;
413 CLOSE csr_person_info;
414
415 hr_utility.trace('l_first_name='||l_first_name);
416 hr_utility.trace('l_middle_name='||l_middle_name);
417 hr_utility.trace('l_date_of_birth='||fnd_date.date_to_displaydate(l_date_of_birth));
418 hr_utility.trace('l_title='||l_title);
419 hr_utility.trace('l_expense_check_to_address='||l_expense_check_to_address);
420 hr_utility.trace('l_ni_number='||l_ni_number);
421 hr_utility.trace('l_sex='||l_sex);
422 hr_utility.trace('l_pensioner_indicator='||l_pensioner_indicator);
423 hr_utility.trace('l_aggregated_paye_flag='||l_aggregated_paye_flag);
424 hr_utility.trace('l_multiple_asg_flag='||l_multiple_asg_flag);
425
426 OPEN csr_addresses(p_person_id);
427 FETCH csr_addresses INTO l_address_line1,
428 l_address_line2,
429 l_address_line3,
430 l_town_or_city,
431 l_county,
432 l_postal_code,
433 l_country; -- 4011263
434 CLOSE csr_addresses;
435
436 hr_utility.trace('l_address_line1='||l_address_line1);
437 hr_utility.trace('l_address_line2='||l_address_line2);
438 hr_utility.trace('l_address_line3='||l_address_line3);
439 hr_utility.trace('l_town_or_city='||l_town_or_city);
440 hr_utility.trace('l_county='||l_county);
441 hr_utility.trace('l_postal_code='||l_postal_code);
442 hr_utility.trace('l_country='||l_country);
443 --
444 /* 4752018 - Push null address lines to the end */
445 remove_null_address_lines(p_address_line1 => l_address_line1,
446 p_address_line2 => l_address_line2,
447 p_address_line3 => l_address_line3,
448 p_address_line4 => l_town_or_city);
449 --
450 OPEN csr_country_name(l_country);
451 FETCH csr_country_name INTO l_country_name;
452 CLOSE csr_country_name;
453
454 OPEN csr_asg_num;
455 FETCH csr_asg_num INTO l_assignment_number,l_people_group_flex;
456 CLOSE csr_asg_num;
457
458 if (l_first_name is null) then
459 l_first_name_str := null;
460 else
461 l_first_name_str := l_first_name|| ' ';
462 end if;
463
464 if (l_middle_name is null) then
465 l_middle_name_str := null;
466 else
467 l_middle_name_str := substr(l_middle_name,1,1)||' ';
468 end if;
469
470 if (l_title is null) then
471 l_title_str := null;
472 else
473 l_title_str := substr(l_title,1,1)||' ';
474 end if;
475
476 fnd_profile.get('GB_EMPLOYEE_NAME_REPORT_FORMAT', l_profile_name_format);
477
478 if (l_profile_name_format is null or l_profile_name_format = 'FORMAT1') then
479 l_name_and_initials := upper(rpad(substr((l_last_name || ' ' ||
480 l_first_name_str||
481 l_middle_name_str ||
482 l_title),
483 1,(36-(length(l_assignment_number)+1))) ,
484 (36-(length(l_assignment_number)+1)),' ')||' '||
485 l_assignment_number);
486
487 elsif (l_profile_name_format = 'FORMAT2') then
488 l_name_and_initials := upper(rpad(substr((l_title ||
489 l_first_name||
490 l_middle_name||' '||
491 l_last_name),
492 1,(36-(length(l_assignment_number)+1))) ,
493 (36-(length(l_assignment_number)+1)),' ')||' '||
494 l_assignment_number);
495
496
497 elsif (l_profile_name_format = 'FORMAT3') then
498 l_name_and_initials := upper(rpad(substr((l_last_name || ' ' ||
499 l_title ||
500 l_first_name||
501 l_middle_name),
502 1,(36-(length(l_assignment_number)+1))) ,
503 (36-(length(l_assignment_number)+1)),' ')||' '
504 || l_assignment_number);
505
506 elsif (l_profile_name_format = 'FORMAT4') then
507 l_name_and_initials := upper(rpad(substr((l_title ||
508 l_first_name||
509 l_last_name),
510 1,(36-(length(l_assignment_number)+1))) ,
511 (36-(length(l_assignment_number)+1)),' ')||' '||
512 l_assignment_number);
513 end if;
514
515 hr_utility.trace('Assigning values to p_emp_rec');
516
517 p_emp_rec.assignment_id := p_asg_id;
518 p_emp_rec.action_info_category := 'GB RTI P60 EMP DET';
519 p_emp_rec.act_info1 := l_ni_number;
520 p_emp_rec.act_info2 := l_employee_number;
521 p_emp_rec.act_info3 := l_last_name;
522 p_emp_rec.act_info4 := l_name_and_initials;
523 p_emp_rec.act_info5 := upper(rpad(l_first_name,17) || ' ' || substr(l_middle_name,1,1));
524
525 p_emp_rec.act_info6 := l_address_line1;
526 p_emp_rec.act_info7 := l_address_line2;
527 p_emp_rec.act_info8 := l_address_line3;
528 p_emp_rec.act_info9 := l_town_or_city;
529 p_emp_rec.act_info10 := l_county;
530 p_emp_rec.act_info11 := l_postal_code;
531 p_emp_rec.act_info12 := l_country_name;
532 p_emp_rec.act_info13 := substr(to_char(l_date_of_birth,'DD-MM-YYYY'),1,2);
533 p_emp_rec.act_info14 := substr(to_char(l_date_of_birth,'DD-MM-YYYY'),4,2);
534 p_emp_rec.act_info15 := substr(to_char(l_date_of_birth,'DD-MM-YYYY'),7,4);
535 p_emp_rec.act_info16 := l_sex;
536 p_emp_rec.act_info17 := substr(to_char(g_end_year,'DD-MM-YYYY'),7,4);
537 p_emp_rec.act_info18 := l_expense_check;
538 p_emp_rec.act_info19 := l_people_group_flex;
539 p_emp_rec.act_info20 := g_business_group_id;
540
541 hr_utility.set_location('Leaving: '||l_proc,999);
542 RETURN l_arch;
543
544
545 EXCEPTION
546 WHEN OTHERS THEN
547 hr_utility.set_location('Error in function fetch_p60_emp_det ', 15);
548 hr_utility.trace('Exception:' || SQLCODE || ' - ' || SQLERRM );
549 fnd_file.put_line(fnd_file.LOG,'Exception:' || SQLCODE || ' - ' || SQLERRM);
550 RAISE;
551 END fetch_p60_emp_det;
552
553 --
554 FUNCTION fetch_p60_asg_det(
555 p_assactid IN NUMBER,
556 p_pactid IN NUMBER,
557 p_last_asg_action_id IN NUMBER,
558 p_person_id IN NUMBER,
559 p_asg_id IN NUMBER,
560 p_start_date IN DATE,
561 p_end_date IN DATE,
562 p_paye_aggr_asgactid OUT nocopy NUMBER,
563 p_paye_aggr_asgid OUT nocopy NUMBER,
564 p_asg_rec OUT nocopy act_info_rec)
565 RETURN BOOLEAN
566 IS
567
568 CURSOR csr_child_act_id(c_lst_act_id NUMBER)
569 IS
570 SELECT MAX(ASSIGNMENT_ACTION_ID)
571 FROM pay_assignment_actions
572 WHERE SOURCE_ACTION_ID = c_lst_act_id;
573
574 CURSOR csr_tax_paye_result(p_action_id NUMBER)
575 IS
576 SELECT MAX(DECODE(name,'Tax Code',prrv.result_value,NULL)),
577 MAX(DECODE(name,'Tax Basis',prrv.result_value,NULL)),
578 to_number(max(decode(name,'Pay Previous',
579 fnd_number.canonical_to_number(prrv.result_value),NULL)))
580 pay_previous,
581 to_number(max(decode(name,'Tax Previous',
582 fnd_number.canonical_to_number(prrv.result_value),NULL)))
583 tax_previous
584 FROM pay_run_results prr ,
585 pay_run_result_values prrv ,
586 pay_input_values_f pivf
587 WHERE prr.assignment_action_id = p_action_id
588 AND prr.ELEMENT_TYPE_ID = g_paye_element_id
589 AND prrv.run_result_id = prr.run_result_id
590 AND pivf.INPUT_VALUE_ID = prrv.INPUT_VALUE_ID;
591
592 CURSOR csr_paye_details(p_assignment_id NUMBER) IS
593 SELECT max(decode(iv.name,'Tax Code',screen_entry_value)) tax_code,
594 max(decode(iv.name,'Tax Basis',screen_entry_value,NULL)) tax_basis,
595 max(decode(iv.name,'Pay Previous',
596 fnd_number.canonical_to_number(screen_entry_value),NULL))
597 pay_previous,
598 max(decode(iv.name,'Tax Previous',
599 fnd_number.canonical_to_number(screen_entry_value),NULL))
600 tax_previous
601 FROM pay_element_entries_f e,
602 pay_element_entry_values_f v,
603 pay_input_values_f iv,
604 pay_element_links_f link
605 WHERE e.assignment_id = p_asg_id
606 AND link.element_type_id = g_paye_details_id
607 AND e.element_link_id = link.element_link_id
608 AND e.element_entry_id = v.element_entry_id
609 AND iv.input_value_id = v.input_value_id
610 AND e.effective_end_date BETWEEN link.effective_start_date AND link.effective_end_date
611 AND e.effective_end_date BETWEEN iv.effective_start_date AND iv.effective_end_date
612 AND e.effective_end_date BETWEEN v.effective_start_date AND v.effective_end_date
613 AND e.effective_end_date = (select max(e1.effective_end_date)
614 from pay_element_entries_f e1,
615 pay_element_links_f link1
616 where link1.element_type_id = g_paye_details_id
617 and e1.assignment_id = p_asg_id
618 and e1.element_link_id = link1.element_link_id);
619
620 CURSOR csr_get_taxable_pay(c_balance_name varchar2, c_dim_name varchar2) is
621 SELECT pdb.defined_balance_id
622 FROM pay_defined_balances pdb,
623 pay_balance_dimensions pbd,
624 pay_balance_types pbt
625 WHERE pbt.balance_name = c_balance_name
626 AND pbd.database_item_suffix = c_dim_name
627 AND pbt.balance_type_id= pdb.balance_type_id
628 AND pbd.balance_dimension_id = pdb.balance_dimension_id
629 AND pbt.legislation_code = 'GB';
630
631
632 CURSOR csr_tax_pay_value(p_last_asg_action_id number,l_tax_pay_def_bal_id number)
633 IS
634 SELECT hr_dirbal.get_balance(p_last_asg_action_id,l_tax_pay_def_bal_id)
635 FROM dual;
636
637 --Below cursor fetches period type of the current assignment
638 CURSOR csr_get_period_type
639 IS
640 SELECT max(ppa.payroll_id),
641 max(ptp.period_type),
642 max(ptp.period_num) max_period_number,
643 min(ptp.start_date) start_year,
644 max(ptp.end_date) end_year
645 FROM pay_payroll_actions ppa,
646 pay_assignment_actions paa,
647 per_time_periods ptp
648 WHERE assignment_action_id = p_last_asg_action_id
649 AND ppa.time_period_id = ptp.time_period_id
650 AND ppa.payroll_id = ptp.payroll_id
651 AND paa.payroll_action_id = ppa.payroll_action_id;
652
653 --Below cursor fetches further period type details of the current assignment
654 CURSOR csr_period_type_info(p_period_type VARCHAR2)
655 IS
656 SELECT ptpt.number_per_fiscal_year
657 FROM per_time_period_types ptpt
658 WHERE p_period_type = ptpt.period_type;
659
660 CURSOR get_week_53_start(p_payroll_id NUMBER, p_prl_max_period_number NUMBER, p_prl_start_year DATE, p_prl_end_year DATE) IS
661 SELECT start_date
662 FROM per_time_periods ptp
663 WHERE payroll_id = p_payroll_id
664 AND regular_payment_date BETWEEN p_prl_start_year AND p_prl_end_year
665 AND period_num = p_prl_max_period_number;
666
667
668 CURSOR csr_parameter_info
669 IS
670 SELECT SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref
671 FROM pay_payroll_actions pact
672 WHERE payroll_action_id = p_pactid;
673
674 CURSOR get_asg_active_range(p_asg_id NUMBER, p_tax_ref VARCHAR2) IS
675 SELECT min(paaf.effective_start_date) min_active,
676 max(paaf.effective_end_date) max_active
677 FROM per_all_assignments_f paaf,
678 per_assignment_status_types past,
679 pay_all_payrolls_f papf,
680 hr_soft_coding_keyflex flex
681 WHERE paaf.assignment_id = p_asg_id
682 AND paaf.assignment_status_type_id = past.assignment_status_type_id
683 AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
684 AND paaf.payroll_id = papf.payroll_id
685 AND paaf.effective_start_date BETWEEN papf.effective_start_date and papf.effective_end_date
686 AND papf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
687 AND flex.segment1 = p_tax_ref;
688
689
690 --Below cursor fetches the further details of the current assignment
691 CURSOR csr_asg
692 IS
693 SELECT trim(NVL(asg.primary_flag,'N')) asg_primary_flag,
694 trim(NVL(pap.per_information9,'N')) per_ni_agg_flag,
695 trim(NVL(pap.per_information10,'N')) per_paye_agg_flag,
696 asg.assignment_number assignment_number
697 FROM per_all_people_f pap,
698 per_all_assignments_f asg,
699 pay_assignment_actions paa,
700 pay_payroll_actions ppa
701 WHERE paa.assignment_action_id = p_last_asg_action_id
702 AND ppa.payroll_action_id = paa.payroll_action_id
703 AND paa.assignment_id = asg.assignment_id
704 AND pap.person_id = asg.person_id
705 AND ppa.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
706 AND ppa.effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
707 AND pap.per_information_category = 'GB';
708
709 --Below cursor fetches the latest payroll run details for the person
710 CURSOR csr_aggr_last_payroll_action
711 IS
712 SELECT
713 /*+ USE_NL(paa, pact, ptp) */
714 to_number(SUBSTR(MAX(lpad(paa.action_sequence,15,'0')
715 || paa.assignment_action_id),16)) max_asg_act_id,
716 MAX(pact.effective_date) effective_date
717 FROM pay_assignment_actions paa,
718 per_all_assignments_f paaf,
719 pay_payroll_actions pact,
720 per_time_periods ptp
721 WHERE paa.assignment_id = paaf.assignment_id
722 AND paaf.person_id = p_person_id
723 AND paa.payroll_action_id = pact.payroll_action_id
724 AND pact.time_period_id = ptp.time_period_id
725 AND pact.action_type IN ('Q','R','B','I','V')
726 AND paa.action_status IN ('C','S')
727 AND ptp.regular_payment_date BETWEEN p_start_date AND p_end_date;
728
729 cursor csr_agg_oldest_asg is
730 select paa.assignment_action_id, paa.assignment_id
731 from pay_assignment_actions paa,
732 per_all_assignments_f paaf
733 where paaf.person_id = p_person_id
734 and paaf.assignment_id = paa.assignment_id
735 and paa.payroll_action_id = p_pactid
736 and paaf.effective_start_date =
737 (select min(paaf1.effective_start_date)
738 from pay_assignment_actions paa1,
739 per_all_assignments_f paaf1
740 where paaf1.person_id = p_person_id
741 and paaf1.assignment_id = paa1.assignment_id
742 and paa1.payroll_action_id = p_pactid
743 );
744
745 cursor csr_agg_primary_asg is
746 select paa.assignment_id
747 from pay_assignment_actions paa,
748 per_all_assignments_f paaf,
749 per_all_people_f papf
750 where paaf.person_id = p_person_id --41650 --
751 and paaf.assignment_id = paa.assignment_id
752 and paa.payroll_action_id = p_pactid --923889 --
753 and papf.person_id = paaf.person_id
754 and trim(nvl(paaf.primary_flag,'N')) = 'Y';
755
756
757 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_p60_asg_det';
758 l_arch BOOLEAN;
759 l_asg_id number;
760 l_context_lst pay_balance_pkg.t_context_tab; -- used for batch balance retrieval
761 l_output_table pay_balance_pkg.t_detailed_bal_out_tab; -- output of batch balance retrieval
762 l_last_chld_act_id number;
763 l_tax_pay_def_bal_id number;
764 l_tax_pay_val number;
765 l_pay_previous number;
766 l_tax_previous number;
767 l_tax_code varchar2(30);
768 l_period_type varchar2(30);
769 l_number_per_fiscal_yr number;
770 l_w1_m1_indicator varchar2(30);
771 l_prl_start_year date;
772 l_prl_end_year date;
773 l_prl_max_period_number number;
774 l_week_53_start date;
775 l_week_53_indicator varchar2(1);
776 l_tax_ref VARCHAR2(20);
777 l_active_start date;
778 l_active_end date;
779 l_payroll_id number;
780 l_asg_primary_flag varchar2(1);
781 l_per_ni_agg_flag varchar2(1);
782 l_per_paye_agg_flag varchar2(1);
783 l_aggr_last_asgact_id number;
784 l_aggr_last_eff_date date;
785 l_oldest_assignment_action_id number;
786 l_oldest_asg_id number;
787 l_primary_asg_id number;
788 l_aggr_last_chld_act_id number;
789 l_assignment_number varchar2(30);
790
791 BEGIN
792 hr_utility.set_location('Entering '|| l_proc, 10);
793 l_arch := true;
794
795 hr_utility.trace('p_assactid : '||p_assactid);
796 hr_utility.trace('p_pactid : '||p_pactid);
797 hr_utility.trace('p_last_asg_action_id : '||p_last_asg_action_id);
798 hr_utility.trace('p_person_id : '||p_person_id);
799 hr_utility.trace('p_asg_id : '||p_asg_id);
800 hr_utility.trace('p_start_date : '||p_start_date);
801 hr_utility.trace('p_end_date : '||p_end_date);
802
803 OPEN csr_parameter_info;
804 FETCH csr_parameter_info
805 INTO l_tax_ref;
806 CLOSE csr_parameter_info;
807
808 hr_utility.trace('l_tax_ref : '||l_tax_ref);
809
810 l_context_lst(1).tax_unit_id :=NULL;
811 l_context_lst(1).jurisdiction_code :=NULL;
812 l_context_lst(1).source_id :=NULL;
813 l_context_lst(1).source_text :=NULL;
814 l_context_lst(1).source_number :=NULL;
815 l_context_lst(1).source_text2 :=NULL;
816 l_context_lst(1).time_def_id :=NULL;
817 l_context_lst(1).balance_date :=NULL;
818 l_context_lst(1).local_unit_id :=NULL;
819 l_context_lst(1).source_number2 :=NULL;
820 l_context_lst(1).organization_id :=NULL;
821
822 OPEN csr_child_act_id(p_last_asg_action_id);
823 FETCH csr_child_act_id INTO l_last_chld_act_id;
824 CLOSE csr_child_act_id;
825
826 IF l_last_chld_act_id is NULL THEN
827 l_last_chld_act_id := p_last_asg_action_id;
828 END IF;
829
830 hr_utility.trace('l_last_chld_act_id : '||l_last_chld_act_id);
831
832 OPEN csr_tax_paye_result(l_last_chld_act_id);
833 FETCH csr_tax_paye_result
834 INTO l_tax_code,
835 l_w1_m1_indicator,
836 l_pay_previous,
837 l_tax_previous;
838 CLOSE csr_tax_paye_result;
839
840 hr_utility.trace('l_tax_code : '||l_tax_code);
841
842 if l_tax_code is null then
843 open csr_paye_details(p_asg_id);
844 fetch csr_paye_details
845 INTO l_tax_code,
846 l_w1_m1_indicator,
847 l_pay_previous,
848 l_tax_previous;
849 close csr_paye_details;
850 end if;
851
852 hr_utility.trace('l_tax_code : '||l_tax_code);
853 hr_utility.trace('l_w1_m1_indicator : '||l_w1_m1_indicator);
854 hr_utility.trace('l_pay_previous : '||l_pay_previous);
855 hr_utility.trace('l_tax_previous : '||l_tax_previous);
856
857 OPEN csr_get_period_type;
858 FETCH csr_get_period_type
859 INTO l_payroll_id, l_period_type, l_prl_max_period_number, l_prl_start_year, l_prl_end_year;
860 CLOSE csr_get_period_type;
861
862 hr_utility.trace('l_payroll_id : '||l_payroll_id);
863 hr_utility.trace('l_period_type :'||l_period_type);
864 hr_utility.trace('l_prl_max_period_number : '||l_prl_max_period_number);
865 hr_utility.trace('l_prl_start_year : '||l_prl_start_year);
866 hr_utility.trace('l_prl_end_year : '||l_prl_end_year);
867
868 OPEN csr_period_type_info(l_period_type);
869 FETCH csr_period_type_info INTO l_number_per_fiscal_yr;
870 CLOSE csr_period_type_info;
871
872 hr_utility.trace('l_number_per_fiscal_yr :'||l_number_per_fiscal_yr);
873 hr_utility.trace('l_w1_m1_indicator before :'||l_w1_m1_indicator);
874
875 IF l_w1_m1_indicator = 'C' THEN
876 l_w1_m1_indicator := ' ';
877 ELSE
878 hr_utility.trace('No of periods per year = '||l_number_per_fiscal_yr);
879 IF l_number_per_fiscal_yr in (1,2,4,6,12,24) THEN
880 l_w1_m1_indicator := 'M';
881 ELSE
882 l_w1_m1_indicator := 'W';
883 END IF;
884 END IF;
885
886 IF (l_w1_m1_indicator in ('M', 'W')) THEN
887 l_w1_m1_indicator := l_w1_m1_indicator || '1';
888 END IF;
889
890 hr_utility.trace('l_w1_m1_indicator after :'||l_w1_m1_indicator);
891
892 p_asg_rec.act_info5 := l_tax_code;
893 p_asg_rec.act_info6 := l_w1_m1_indicator;
894
895 OPEN get_asg_active_range(p_asg_id, l_tax_ref);
896 FETCH get_asg_active_range INTO l_active_start, l_active_end;
897 CLOSE get_asg_active_range;
898
899 hr_utility.trace('l_active_start : '||l_active_start);
900 hr_utility.trace('l_active_end : '||l_active_end);
901
902 if l_prl_max_period_number in (53,54,56) then
903 OPEN get_week_53_start(l_payroll_id, l_prl_max_period_number,l_prl_start_year, l_prl_end_year);
904 FETCH get_week_53_start INTO l_week_53_start;
905 CLOSE get_week_53_start;
906
907 hr_utility.trace('After get_week_53_start, l_week_53_start='||
908 fnd_date.date_to_displaydate(l_week_53_start));
909 IF nvl(l_week_53_start, hr_general.end_of_time) <= l_active_end THEN
910 l_week_53_indicator := substr(to_char(l_prl_max_period_number),2,1);
911 END IF;
912 else
913 l_week_53_indicator := ' ';
914 end if;
915
916 hr_utility.trace('l_week_53_indicator : '||l_week_53_indicator);
917
918 open csr_get_taxable_pay('Taxable Pay', '_ASG_RUN');
919 fetch csr_get_taxable_pay into l_tax_pay_def_bal_id;
920 close csr_get_taxable_pay;
921 hr_utility.trace('l_tax_pay_def_bal_id : '||l_tax_pay_def_bal_id);
922
923 OPEN csr_asg;
924 FETCH csr_asg INTO l_asg_primary_flag, l_per_ni_agg_flag, l_per_paye_agg_flag, l_assignment_number;
925 CLOSE csr_asg;
926 hr_utility.trace('l_asg_primary_flag : '||l_asg_primary_flag);
927 hr_utility.trace('l_per_ni_agg_flag : '||l_per_ni_agg_flag);
928 hr_utility.trace('l_per_paye_agg_flag : '||l_per_paye_agg_flag);
929 hr_utility.trace('l_assignment_number : '||l_assignment_number);
930
931 OPEN csr_agg_primary_asg;
932 FETCH csr_agg_primary_asg INTO l_primary_asg_id;
933 CLOSE csr_agg_primary_asg;
934
935 IF (l_primary_asg_id is null) THEN
936 OPEN csr_agg_oldest_asg;
937 FETCH csr_agg_oldest_asg INTO l_oldest_assignment_action_id, l_oldest_asg_id;
938 CLOSE csr_agg_oldest_asg;
939 ELSE
940 l_oldest_asg_id := l_primary_asg_id;
941 END IF;
942
943 if (l_per_paye_agg_flag <> 'Y' or
944 (l_per_paye_agg_flag = 'Y' and p_asg_id <> l_oldest_asg_id)) then
945 hr_utility.trace('Entered Non-Aggregated Code');
946
947 p_asg_rec.assignment_id := p_asg_id;
948 p_asg_rec.action_info_category := 'GB RTI P60 ASG DET';
949
950 open csr_tax_pay_value(p_last_asg_action_id,l_tax_pay_def_bal_id);
951 fetch csr_tax_pay_value into l_tax_pay_val;
952 close csr_tax_pay_value;
953 hr_utility.trace('l_tax_pay_val : '||l_tax_pay_val);
954 -- Test whether we can get run level value with parent action id.
955 -- If not pass the child assignment action id.
956 if l_tax_pay_val = 0 then
957 pay_balance_pkg.get_value(p_assignment_action_id => l_last_chld_act_id,
958 p_defined_balance_lst => g_defined_balance_lst ,
959 p_context_lst => l_context_lst ,
960 p_output_table => l_output_table );
961 else
962 pay_balance_pkg.get_value(p_assignment_action_id => p_last_asg_action_id ,
963 p_defined_balance_lst => g_defined_balance_lst ,
964 p_context_lst => l_context_lst ,
965 p_output_table => l_output_table );
966 end if;
967
968 hr_utility.trace('After invoking get_value');
969
970 hr_utility.trace('Before entering for i in 1..g_fps_bal_det_tab.last loop');
971
972 hr_utility.trace('l_output_table.count: '||l_output_table.count);
973
974 FOR outer_rec IN 1..l_output_table.count
975 LOOP
976
977 IF l_output_table(outer_rec).balance_value is not null then -- Null Check
978
979 FOR inner_rec IN 1..g_fps_bal_det_tab.last
980 LOOP
981
982 IF (g_fps_bal_det_tab(inner_rec).defined_balance_id = l_output_table(outer_rec).defined_balance_id) THEN
983 IF g_fps_bal_det_tab(inner_rec).balance_name = 'Taxable Pay' THEN
984 IF (g_fps_bal_det_tab(inner_rec).database_item_suffix = '_ASG_TD_YTD' ) THEN
985 g_fps_bal_det_tab(inner_rec).balance_value := NVL(l_output_table(outer_rec).balance_value,0);
986 p_asg_rec.act_info1 := NVL(l_output_table(outer_rec).balance_value,0);
987 END IF;
988 elsif g_fps_bal_det_tab(inner_rec).balance_name = 'PAYE' THEN
989 IF (g_fps_bal_det_tab(inner_rec).database_item_suffix = '_ASG_TD_YTD') THEN
990 g_fps_bal_det_tab(inner_rec).balance_value := NVL(l_output_table(outer_rec).balance_value,0);
991 p_asg_rec.act_info2 := NVL(l_output_table(outer_rec).balance_value,0);
992 END IF;
993 elsif g_fps_bal_det_tab(inner_rec).balance_name = 'Student Loan' THEN
994 IF (g_fps_bal_det_tab(inner_rec).database_item_suffix = '_ASG_TD_YTD') THEN
995 g_fps_bal_det_tab(inner_rec).balance_value := NVL(l_output_table(outer_rec).balance_value,0);
996 p_asg_rec.act_info3 := NVL(l_output_table(outer_rec).balance_value,0);
997 END IF;
998 elsif g_fps_bal_det_tab(inner_rec).balance_name = 'SMP Total' THEN
999 IF (g_fps_bal_det_tab(inner_rec).database_item_suffix = '_ASG_TD_YTD') THEN
1000 g_fps_bal_det_tab(inner_rec).balance_value := NVL(l_output_table(outer_rec).balance_value,0);
1001 p_asg_rec.act_info12 := NVL(l_output_table(outer_rec).balance_value,0);
1002 END IF;
1003 elsif g_fps_bal_det_tab(inner_rec).balance_name = 'SAP Total' THEN
1004 IF (g_fps_bal_det_tab(inner_rec).database_item_suffix = '_ASG_TD_YTD') THEN
1005 g_fps_bal_det_tab(inner_rec).balance_value := NVL(l_output_table(outer_rec).balance_value,0);
1006 p_asg_rec.act_info13 := NVL(l_output_table(outer_rec).balance_value,0);
1007 END IF;
1008 elsif g_fps_bal_det_tab(inner_rec).balance_name = 'SPP Adoption Total' THEN
1009 IF (g_fps_bal_det_tab(inner_rec).database_item_suffix = '_ASG_TD_YTD') THEN
1010 g_fps_bal_det_tab(inner_rec).balance_value := NVL(l_output_table(outer_rec).balance_value,0);
1011 p_asg_rec.act_info14 := nvl(p_asg_rec.act_info14,0) + NVL(l_output_table(outer_rec).balance_value,0);
1012 END IF;
1013 elsif g_fps_bal_det_tab(inner_rec).balance_name = 'SPP Birth Total' THEN
1014 IF (g_fps_bal_det_tab(inner_rec).database_item_suffix = '_ASG_TD_YTD') THEN
1015 g_fps_bal_det_tab(inner_rec).balance_value := NVL(l_output_table(outer_rec).balance_value,0);
1016 p_asg_rec.act_info14 := nvl(p_asg_rec.act_info14,0) + NVL(l_output_table(outer_rec).balance_value,0);
1017 END IF;
1018 elsif g_fps_bal_det_tab(inner_rec).balance_name = 'ASPP Adoption Total' THEN
1019 IF (g_fps_bal_det_tab(inner_rec).database_item_suffix = '_ASG_TD_YTD') THEN
1020 g_fps_bal_det_tab(inner_rec).balance_value := NVL(l_output_table(outer_rec).balance_value,0);
1021 p_asg_rec.act_info15 := nvl(p_asg_rec.act_info15,0) + NVL(l_output_table(outer_rec).balance_value,0);
1022 END IF;
1023 elsif g_fps_bal_det_tab(inner_rec).balance_name = 'ASPP Birth Total' THEN
1024 IF (g_fps_bal_det_tab(inner_rec).database_item_suffix = '_ASG_TD_YTD') THEN
1025 g_fps_bal_det_tab(inner_rec).balance_value := NVL(l_output_table(outer_rec).balance_value,0);
1026 p_asg_rec.act_info15 := nvl(p_asg_rec.act_info15,0) + NVL(l_output_table(outer_rec).balance_value,0);
1027 END IF;
1028 elsif g_fps_bal_det_tab(inner_rec).balance_name = 'Widows and Orphans' THEN
1029 IF (g_fps_bal_det_tab(inner_rec).database_item_suffix = '_ASG_TD_YTD') THEN
1030 g_fps_bal_det_tab(inner_rec).balance_value := NVL(l_output_table(outer_rec).balance_value,0);
1031 p_asg_rec.act_info7 := NVL(l_output_table(outer_rec).balance_value,0);
1032 END IF;
1033
1034 END IF;
1035 END IF;
1036 END LOOP;
1037 hr_utility.trace('After for i in 1..g_fps_bal_det_tab.last loop');
1038 END IF; --Not null check ends
1039 END LOOP;
1040 IF (l_per_paye_agg_flag = 'Y') THEN
1041 p_asg_rec.act_info20 := 'Y';
1042 END IF;
1043 else --if l_per_paye_agg_flag <> 'Y' then
1044
1045 hr_utility.trace('Entered PAYE Aggregated Code');
1046
1047 OPEN csr_aggr_last_payroll_action;
1048 FETCH csr_aggr_last_payroll_action
1049 INTO l_aggr_last_asgact_id,
1050 l_aggr_last_eff_date;
1051 CLOSE csr_aggr_last_payroll_action;
1052
1053 hr_utility.trace('l_aggr_last_asgact_id : '||l_aggr_last_asgact_id);
1054 hr_utility.trace('l_aggr_last_eff_date : '||l_aggr_last_eff_date);
1055
1056 p_asg_rec.assignment_id := p_asg_id;
1057 p_asg_rec.action_info_category := 'GB RTI P60 ASG DET';
1058
1059 OPEN csr_child_act_id(l_aggr_last_asgact_id);
1060 FETCH csr_child_act_id INTO l_aggr_last_chld_act_id;
1061 CLOSE csr_child_act_id;
1062
1063 IF l_aggr_last_chld_act_id is NULL THEN
1064 l_aggr_last_chld_act_id := l_aggr_last_asgact_id;
1065 END IF;
1066
1067 hr_utility.trace('l_aggr_last_chld_act_id : '||l_aggr_last_chld_act_id);
1068
1069 p_paye_aggr_asgactid := l_aggr_last_asgact_id;
1070 p_paye_aggr_asgid := p_asg_id;
1071
1072 hr_utility.trace('p_paye_aggr_asgactid : '||p_paye_aggr_asgactid);
1073 hr_utility.trace('p_paye_aggr_asgid : '||p_paye_aggr_asgid);
1074
1075 open csr_tax_pay_value(l_aggr_last_asgact_id,l_tax_pay_def_bal_id);
1076 fetch csr_tax_pay_value into l_tax_pay_val;
1077 close csr_tax_pay_value;
1078 hr_utility.trace('l_tax_pay_val : '||l_tax_pay_val);
1079 -- Test whether we can get run level value with parent action id.
1080 -- If not pass the child assignment action id.
1081 if l_tax_pay_val = 0 then
1082 pay_balance_pkg.get_value(p_assignment_action_id => l_aggr_last_chld_act_id,
1083 p_defined_balance_lst => g_defined_balance_lst ,
1084 p_context_lst => l_context_lst ,
1085 p_output_table => l_output_table );
1086 else
1087 pay_balance_pkg.get_value(p_assignment_action_id => l_aggr_last_asgact_id ,
1088 p_defined_balance_lst => g_defined_balance_lst ,
1089 p_context_lst => l_context_lst ,
1090 p_output_table => l_output_table );
1091 end if;
1092
1093 hr_utility.trace('After invoking get_value');
1094
1095 hr_utility.trace('Before entering for i in 1..g_fps_bal_det_tab.last loop');
1096
1097 hr_utility.trace('l_output_table.count: '||l_output_table.count);
1098
1099 FOR outer_rec IN 1..l_output_table.count
1100 LOOP
1101
1102 IF l_output_table(outer_rec).balance_value is not null then -- Null Check
1103
1104 FOR inner_rec IN 1..g_fps_bal_det_tab.last
1105 LOOP
1106
1107 IF (g_fps_bal_det_tab(inner_rec).defined_balance_id = l_output_table(outer_rec).defined_balance_id) THEN
1108 IF g_fps_bal_det_tab(inner_rec).balance_name = 'Taxable Pay' THEN
1109 IF (g_fps_bal_det_tab(inner_rec).database_item_suffix = '_PER_TD_YTD' ) THEN
1110 g_fps_bal_det_tab(inner_rec).balance_value := NVL(l_output_table(outer_rec).balance_value,0);
1111 p_asg_rec.act_info1 := NVL(l_output_table(outer_rec).balance_value,0);
1112 END IF;
1113 elsif g_fps_bal_det_tab(inner_rec).balance_name = 'PAYE' THEN
1114 IF (g_fps_bal_det_tab(inner_rec).database_item_suffix = '_PER_TD_CPE_YTD') THEN
1115 g_fps_bal_det_tab(inner_rec).balance_value := NVL(l_output_table(outer_rec).balance_value,0);
1116 p_asg_rec.act_info2 := NVL(l_output_table(outer_rec).balance_value,0);
1117 END IF;
1118 elsif g_fps_bal_det_tab(inner_rec).balance_name = 'Student Loan' THEN
1119 IF (g_fps_bal_det_tab(inner_rec).database_item_suffix = '_PER_TD_YTD') THEN
1120 g_fps_bal_det_tab(inner_rec).balance_value := NVL(l_output_table(outer_rec).balance_value,0);
1121 p_asg_rec.act_info3 := NVL(l_output_table(outer_rec).balance_value,0);
1122 END IF;
1123 elsif g_fps_bal_det_tab(inner_rec).balance_name = 'SMP Total' THEN
1124 IF (g_fps_bal_det_tab(inner_rec).database_item_suffix = '_PER_TD_YTD') THEN
1125 g_fps_bal_det_tab(inner_rec).balance_value := NVL(l_output_table(outer_rec).balance_value,0);
1126 p_asg_rec.act_info12 := NVL(l_output_table(outer_rec).balance_value,0);
1127 END IF;
1128 elsif g_fps_bal_det_tab(inner_rec).balance_name = 'SAP Total' THEN
1129 IF (g_fps_bal_det_tab(inner_rec).database_item_suffix = '_PER_TD_YTD') THEN
1130 g_fps_bal_det_tab(inner_rec).balance_value := NVL(l_output_table(outer_rec).balance_value,0);
1131 p_asg_rec.act_info13 := NVL(l_output_table(outer_rec).balance_value,0);
1132 END IF;
1133 elsif g_fps_bal_det_tab(inner_rec).balance_name = 'SPP Adoption Total' THEN
1134 IF (g_fps_bal_det_tab(inner_rec).database_item_suffix = '_PER_TD_YTD') THEN
1135 g_fps_bal_det_tab(inner_rec).balance_value := NVL(l_output_table(outer_rec).balance_value,0);
1136 p_asg_rec.act_info14 := nvl(p_asg_rec.act_info14,0) + NVL(l_output_table(outer_rec).balance_value,0);
1137 END IF;
1138 elsif g_fps_bal_det_tab(inner_rec).balance_name = 'SPP Birth Total' THEN
1139 IF (g_fps_bal_det_tab(inner_rec).database_item_suffix = '_PER_TD_YTD') THEN
1140 g_fps_bal_det_tab(inner_rec).balance_value := NVL(l_output_table(outer_rec).balance_value,0);
1141 p_asg_rec.act_info14 := nvl(p_asg_rec.act_info14,0) + NVL(l_output_table(outer_rec).balance_value,0);
1142 END IF;
1143 elsif g_fps_bal_det_tab(inner_rec).balance_name = 'ASPP Adoption Total' THEN
1144 IF (g_fps_bal_det_tab(inner_rec).database_item_suffix = '_PER_TD_YTD') THEN
1145 g_fps_bal_det_tab(inner_rec).balance_value := NVL(l_output_table(outer_rec).balance_value,0);
1146 p_asg_rec.act_info15 := nvl(p_asg_rec.act_info15,0) + NVL(l_output_table(outer_rec).balance_value,0);
1147 END IF;
1148 elsif g_fps_bal_det_tab(inner_rec).balance_name = 'ASPP Birth Total' THEN
1149 IF (g_fps_bal_det_tab(inner_rec).database_item_suffix = '_PER_TD_YTD') THEN
1150 g_fps_bal_det_tab(inner_rec).balance_value := NVL(l_output_table(outer_rec).balance_value,0);
1151 p_asg_rec.act_info15 := nvl(p_asg_rec.act_info15,0) + NVL(l_output_table(outer_rec).balance_value,0);
1152 END IF;
1153 elsif g_fps_bal_det_tab(inner_rec).balance_name = 'Widows and Orphans' THEN
1154 IF (g_fps_bal_det_tab(inner_rec).database_item_suffix = '_PER_TD_YTD') THEN
1155 g_fps_bal_det_tab(inner_rec).balance_value := NVL(l_output_table(outer_rec).balance_value,0);
1156 p_asg_rec.act_info7 := NVL(l_output_table(outer_rec).balance_value,0);
1157 END IF;
1158
1159 END IF;
1160 END IF;
1161 END LOOP;
1162 hr_utility.trace('After for i in 1..g_fps_bal_det_tab.last loop');
1163 END IF; --Not null check ends
1164 END LOOP;
1165 p_asg_rec.act_info20 := 'Y';
1166 p_asg_rec.act_info21 := 'Y';
1167
1168 end if; --if l_per_paye_agg_flag <> 'Y' then
1169
1170
1171 p_asg_rec.act_info8 := l_pay_previous + p_asg_rec.act_info1;
1172 p_asg_rec.act_info9 := l_tax_previous + p_asg_rec.act_info2;
1173
1174 p_asg_rec.act_info10 := l_pay_previous;
1175 p_asg_rec.act_info11 := l_tax_previous;
1176
1177 p_asg_rec.act_info17 := l_assignment_number;
1178
1179 IF (p_asg_rec.act_info2 < 0) THEN
1180 p_asg_rec.act_info4 := 'R';
1181 END IF;
1182
1183 p_asg_rec.act_info4 := l_week_53_indicator;
1184
1185 RETURN l_arch;
1186
1187 EXCEPTION
1188 WHEN OTHERS THEN
1189 hr_utility.set_location('Error in function fetch_p60_asg_det ', 15);
1190 hr_utility.trace('Exception:' || SQLCODE || ' - ' || SQLERRM );
1191 fnd_file.put_line(fnd_file.LOG,'Exception:' || SQLCODE || ' - ' || SQLERRM);
1192 RAISE;
1193 END fetch_p60_asg_det;
1194 --
1195 -- Procedure to insert data to the archive table pay_action_information
1196 PROCEDURE insert_archive_row
1197 (
1198 p_assactid IN NUMBER,
1199 p_effective_date IN DATE,
1200 p_tab_rec_data IN action_info_table
1201 )
1202 IS
1203 l_proc CONSTANT VARCHAR2(50):= g_package||'insert_archive_row';
1204 l_ovn NUMBER;
1205 l_action_id NUMBER;
1206 BEGIN
1207 hr_utility.set_location('Entering: '||l_proc,1);
1208
1209 IF p_tab_rec_data.count > 0 THEN
1210 FOR i IN p_tab_rec_data.first .. p_tab_rec_data.last
1211 LOOP
1212 hr_utility.trace
1213 (
1214 'Defining category '|| p_tab_rec_data(i).action_info_category
1215 )
1216 ;
1217 hr_utility.trace('action_context_id = '|| p_assactid);
1218 IF p_tab_rec_data(i).action_info_category IS NOT NULL THEN
1219 pay_action_information_api.create_action_information( p_action_information_id => l_action_id,
1220 p_object_version_number => l_ovn,
1221 p_action_information_category => p_tab_rec_data(i).action_info_category,
1222 p_action_context_id => p_assactid,
1223 p_action_context_type => 'AAP',
1224 p_assignment_id => p_tab_rec_data(i).assignment_id,
1225 p_effective_date => p_effective_date,
1226 p_action_information1 => p_tab_rec_data(i).act_info1,
1227 p_action_information2 => p_tab_rec_data(i).act_info2,
1228 p_action_information3 => p_tab_rec_data(i).act_info3,
1229 p_action_information4 => p_tab_rec_data(i).act_info4,
1230 p_action_information5 => p_tab_rec_data(i).act_info5,
1231 p_action_information6 => p_tab_rec_data(i).act_info6,
1232 p_action_information7 => p_tab_rec_data(i).act_info7,
1233 p_action_information8 => p_tab_rec_data(i).act_info8,
1234 p_action_information9 => p_tab_rec_data(i).act_info9,
1235 p_action_information10 => p_tab_rec_data(i).act_info10,
1236 p_action_information11 => p_tab_rec_data(i).act_info11,
1237 p_action_information12 => p_tab_rec_data(i).act_info12,
1238 p_action_information13 => p_tab_rec_data(i).act_info13,
1239 p_action_information14 => p_tab_rec_data(i).act_info14,
1240 p_action_information15 => p_tab_rec_data(i).act_info15,
1241 p_action_information16 => p_tab_rec_data(i).act_info16,
1242 p_action_information17 => p_tab_rec_data(i).act_info17,
1243 p_action_information18 => p_tab_rec_data(i).act_info18,
1244 p_action_information19 => p_tab_rec_data(i).act_info19,
1245 p_action_information20 => p_tab_rec_data(i).act_info20,
1246 p_action_information21 => p_tab_rec_data(i).act_info21,
1247 p_action_information22 => p_tab_rec_data(i).act_info22,
1248 p_action_information23 => p_tab_rec_data(i).act_info23,
1249 p_action_information24 => p_tab_rec_data(i).act_info24,
1250 p_action_information25 => p_tab_rec_data(i).act_info25,
1251 p_action_information26 => p_tab_rec_data(i).act_info26,
1252 p_action_information27 => p_tab_rec_data(i).act_info27,
1253 p_action_information28 => p_tab_rec_data(i).act_info28,
1254 p_action_information29 => p_tab_rec_data(i).act_info29,
1255 p_action_information30 => p_tab_rec_data(i).act_info30 );
1256 END IF;
1257 END LOOP;
1258 END IF;
1259 hr_utility.set_location('Leaving: '||l_proc,999);
1260 END insert_archive_row;
1261 --For fetching NI balances
1262 --
1263 PROCEDURE load_defined_balances
1264 IS
1265 CURSOR get_defined_balance_id (p_balance_name VARCHAR2, p_dimension_name VARCHAR2)
1266 IS
1267 SELECT defined_balance_id
1268 FROM pay_defined_balances db,
1269 pay_balance_types b,
1270 pay_balance_dimensions d
1271 WHERE b.balance_name = p_balance_name
1272 AND d.dimension_name = p_dimension_name
1273 AND db.balance_type_id = b.balance_type_id
1274 AND db.balance_dimension_id = d.balance_dimension_id;
1275 BEGIN
1276 OPEN get_defined_balance_id('NI A Able','_ASG_TD_YTD');
1277 FETCH get_defined_balance_id INTO g_nia_able_id;
1278 CLOSE get_defined_balance_id;
1279 --
1280 OPEN get_defined_balance_id('NI A Able','_ASG_RUN');
1281 FETCH get_defined_balance_id INTO g_nia_able_id_run;
1282 CLOSE get_defined_balance_id;
1283 --
1284 OPEN get_defined_balance_id('NI A Employee','_ASG_TD_YTD');
1285 FETCH get_defined_balance_id INTO g_nia_e_ytd_id;
1286 CLOSE get_defined_balance_id;
1287 --
1288 OPEN get_defined_balance_id('NI A Employee','_ASG_RUN');
1289 FETCH get_defined_balance_id INTO g_nia_e_ptd_id;
1290 CLOSE get_defined_balance_id;
1291 --
1292 OPEN get_defined_balance_id('NI A Employer','_ASG_TD_YTD');
1293 FETCH get_defined_balance_id INTO g_nia_r_ytd_id;
1294 CLOSE get_defined_balance_id;
1295 --
1296 OPEN get_defined_balance_id('NI A Employer','_ASG_RUN');
1297 FETCH get_defined_balance_id INTO g_nia_r_ptd_id;
1298 CLOSE get_defined_balance_id;
1299 --
1300 OPEN get_defined_balance_id('NI A Total','_ASG_TD_YTD');
1301 FETCH get_defined_balance_id INTO g_nia_tot_id;
1302 CLOSE get_defined_balance_id;
1303 --
1304 OPEN get_defined_balance_id('NI A Able LEL','_ASG_TD_YTD');
1305 FETCH get_defined_balance_id INTO g_nia_lel_id;
1306 CLOSE get_defined_balance_id;
1307 --
1308 OPEN get_defined_balance_id('NI A Able UEL','_ASG_TD_YTD');
1309 FETCH get_defined_balance_id INTO g_nia_uel_id;
1310 CLOSE get_defined_balance_id;
1311 -- 8357870 begin
1312 OPEN get_defined_balance_id('NI A Able UAP','_ASG_TD_YTD');
1313 FETCH get_defined_balance_id INTO g_nia_uap_id;
1314 CLOSE get_defined_balance_id;
1315 -- 8357870 end
1316 --EOY 07/08 begin
1317 OPEN get_defined_balance_id('NI A Able AUEL','_ASG_TD_YTD');
1318 FETCH get_defined_balance_id INTO g_nia_auel_id;
1319 CLOSE get_defined_balance_id;
1320 --EOY 07/08 end
1321 OPEN get_defined_balance_id('NI A Able ET','_ASG_TD_YTD');
1322 FETCH get_defined_balance_id INTO g_nia_et_id;
1323 CLOSE get_defined_balance_id;
1324 --
1325 OPEN get_defined_balance_id('NI B Able','_ASG_TD_YTD');
1326 FETCH get_defined_balance_id INTO g_nib_able_id;
1327 CLOSE get_defined_balance_id;
1328 --
1329 --
1330 OPEN get_defined_balance_id('NI B Able','_ASG_RUN');
1331 FETCH get_defined_balance_id INTO g_nib_able_id_run;
1332 CLOSE get_defined_balance_id;
1333 --
1334 OPEN get_defined_balance_id('NI B Employee','_ASG_TD_YTD');
1335 FETCH get_defined_balance_id INTO g_nib_e_ytd_id;
1336 CLOSE get_defined_balance_id;
1337 --
1338 OPEN get_defined_balance_id('NI B Employee','_ASG_RUN');
1339 FETCH get_defined_balance_id INTO g_nib_e_ptd_id;
1340 CLOSE get_defined_balance_id;
1341 --
1342 OPEN get_defined_balance_id('NI B Employer','_ASG_TD_YTD');
1343 FETCH get_defined_balance_id INTO g_nib_r_ytd_id;
1344 CLOSE get_defined_balance_id;
1345 --
1346 OPEN get_defined_balance_id('NI B Employer','_ASG_RUN');
1347 FETCH get_defined_balance_id INTO g_nib_r_ptd_id;
1348 CLOSE get_defined_balance_id;
1349 --
1350 --
1351 OPEN get_defined_balance_id('NI B Total','_ASG_TD_YTD');
1352 FETCH get_defined_balance_id INTO g_nib_tot_id;
1353 CLOSE get_defined_balance_id;
1354 --
1355 OPEN get_defined_balance_id('NI B Able LEL','_ASG_TD_YTD');
1356 FETCH get_defined_balance_id INTO g_nib_lel_id;
1357 CLOSE get_defined_balance_id;
1358 --
1359 OPEN get_defined_balance_id('NI B Able UEL','_ASG_TD_YTD');
1360 FETCH get_defined_balance_id INTO g_nib_uel_id;
1361 CLOSE get_defined_balance_id;
1362 -- 8357870 begin
1363 OPEN get_defined_balance_id('NI B Able UAP','_ASG_TD_YTD');
1364 FETCH get_defined_balance_id INTO g_nib_uap_id;
1365 CLOSE get_defined_balance_id;
1366 -- 8357870 end
1367 --EOY 07/08 begin
1368 OPEN get_defined_balance_id('NI B Able AUEL','_ASG_TD_YTD');
1369 FETCH get_defined_balance_id INTO g_nib_auel_id;
1370 CLOSE get_defined_balance_id;
1371 --EOY 07/08 end
1372 OPEN get_defined_balance_id('NI B Able ET','_ASG_TD_YTD');
1373 FETCH get_defined_balance_id INTO g_nib_et_id;
1374 CLOSE get_defined_balance_id;
1375 --
1376 OPEN get_defined_balance_id('NI C Able LEL','_ASG_TD_YTD');
1377 FETCH get_defined_balance_id INTO g_nic_lel_id;
1378 CLOSE get_defined_balance_id;
1379 --
1380 OPEN get_defined_balance_id('NI C Able UEL','_ASG_TD_YTD');
1381 FETCH get_defined_balance_id INTO g_nic_uel_id;
1382 CLOSE get_defined_balance_id;
1383 -- 8357870 begin
1384 OPEN get_defined_balance_id('NI C Able UAP','_ASG_TD_YTD');
1385 FETCH get_defined_balance_id INTO g_nic_uap_id;
1386 CLOSE get_defined_balance_id;
1387 -- 8357870 end
1388 --EOY 07/08 Begin
1389 OPEN get_defined_balance_id('NI C Able AUEL','_ASG_TD_YTD');
1390 FETCH get_defined_balance_id INTO g_nic_auel_id;
1391 CLOSE get_defined_balance_id;
1392 --EOY 07/08 End
1393 OPEN get_defined_balance_id('NI C Able ET','_ASG_TD_YTD');
1394 FETCH get_defined_balance_id INTO g_nic_et_id;
1395 CLOSE get_defined_balance_id;
1396 --
1397 OPEN get_defined_balance_id('NI C Able','_ASG_TD_YTD');
1398 FETCH get_defined_balance_id INTO g_nic_able_id;
1399 CLOSE get_defined_balance_id;
1400 --
1401 --
1402 OPEN get_defined_balance_id('NI C Able','_ASG_RUN');
1403 FETCH get_defined_balance_id INTO g_nic_able_id_run;
1404 CLOSE get_defined_balance_id;
1405 --
1406
1407 OPEN get_defined_balance_id('NI C Employee','_ASG_TD_YTD');
1408 FETCH get_defined_balance_id INTO g_nic_e_ytd_id;
1409 CLOSE get_defined_balance_id;
1410 --
1411 OPEN get_defined_balance_id('NI C Employee','_ASG_RUN');
1412 FETCH get_defined_balance_id INTO g_nic_e_ptd_id;
1413 CLOSE get_defined_balance_id;
1414 --
1415 OPEN get_defined_balance_id('NI C Employer','_ASG_TD_YTD');
1416 FETCH get_defined_balance_id INTO g_nic_r_ytd_id;
1417 CLOSE get_defined_balance_id;
1418 --
1419 OPEN get_defined_balance_id('NI C Employer','_ASG_RUN');
1420 FETCH get_defined_balance_id INTO g_nic_r_ptd_id;
1421 CLOSE get_defined_balance_id;
1422 -- Fix for Bug 1976152, added the below stmt to fetch the balance id
1423 -- for the balance NI C Employers Rebate
1424 OPEN get_defined_balance_id('NI C Ers Rebate','_ASG_TD_YTD');
1425 FETCH get_defined_balance_id INTO g_nic_ers_rebate_id;
1426 CLOSE get_defined_balance_id;
1427 --
1428 OPEN get_defined_balance_id('NI D Able','_ASG_TD_YTD');
1429 FETCH get_defined_balance_id INTO g_nid_able_id;
1430 CLOSE get_defined_balance_id;
1431 --
1432 OPEN get_defined_balance_id('NI D Able','_ASG_RUN');
1433 FETCH get_defined_balance_id INTO g_nid_able_id_run;
1434 CLOSE get_defined_balance_id;
1435 --
1436
1437 OPEN get_defined_balance_id('NI D Employee','_ASG_TD_YTD');
1438 FETCH get_defined_balance_id INTO g_nid_e_ytd_id;
1439 CLOSE get_defined_balance_id;
1440 --
1441 OPEN get_defined_balance_id('NI D Employee','_ASG_RUN');
1442 FETCH get_defined_balance_id INTO g_nid_e_ptd_id;
1443 CLOSE get_defined_balance_id;
1444 --
1445 OPEN get_defined_balance_id('NI D Employer','_ASG_TD_YTD');
1446 FETCH get_defined_balance_id INTO g_nid_r_ytd_id;
1447 CLOSE get_defined_balance_id;
1448 --
1449 OPEN get_defined_balance_id('NI D Employer','_ASG_RUN');
1450 FETCH get_defined_balance_id INTO g_nid_r_ptd_id;
1451 CLOSE get_defined_balance_id;
1452 --
1453 OPEN get_defined_balance_id('NI D Total','_ASG_TD_YTD');
1454 FETCH get_defined_balance_id INTO g_nid_tot_id;
1455 CLOSE get_defined_balance_id;
1456 --
1457 OPEN get_defined_balance_id('NI D Able LEL','_ASG_TD_YTD');
1458 FETCH get_defined_balance_id INTO g_nid_lel_id;
1459 CLOSE get_defined_balance_id;
1460 --
1461 OPEN get_defined_balance_id('NI D Able UEL','_ASG_TD_YTD');
1462 FETCH get_defined_balance_id INTO g_nid_uel_id;
1463 CLOSE get_defined_balance_id;
1464 -- 8357870 begin
1465 OPEN get_defined_balance_id('NI D Able UAP','_ASG_TD_YTD');
1466 FETCH get_defined_balance_id INTO g_nid_uap_id;
1467 CLOSE get_defined_balance_id;
1468 -- 8357870 end
1469 --EOY 07/08 Begin
1470 OPEN get_defined_balance_id('NI D Able AUEL','_ASG_TD_YTD');
1471 FETCH get_defined_balance_id INTO g_nid_auel_id;
1472 CLOSE get_defined_balance_id;
1473 --EOY 07/08 End
1474 OPEN get_defined_balance_id('NI D Able ET','_ASG_TD_YTD');
1475 FETCH get_defined_balance_id INTO g_nid_et_id;
1476 CLOSE get_defined_balance_id;
1477 --
1478 OPEN get_defined_balance_id('NI D Ers Rebate','_ASG_TD_YTD');
1479 FETCH get_defined_balance_id INTO g_nid_ers_rebate_id;
1480 CLOSE get_defined_balance_id;
1481 --
1482 OPEN get_defined_balance_id('NI D Ees Rebate','_ASG_TD_YTD');
1483 FETCH get_defined_balance_id INTO g_nid_ees_rebate_id;
1484 CLOSE get_defined_balance_id;
1485 --
1486 OPEN get_defined_balance_id('NI D Rebate to Employee','_ASG_TD_YTD');
1487 FETCH get_defined_balance_id INTO g_nid_rebate_emp_id;
1488 CLOSE get_defined_balance_id;
1489 --
1490 OPEN get_defined_balance_id('NI E Able','_ASG_TD_YTD');
1491 FETCH get_defined_balance_id INTO g_nie_able_id;
1492 CLOSE get_defined_balance_id;
1493 --
1494 OPEN get_defined_balance_id('NI E Able','_ASG_RUN');
1495 FETCH get_defined_balance_id INTO g_nie_able_id_run;
1496 CLOSE get_defined_balance_id;
1497 --
1498
1499 OPEN get_defined_balance_id('NI E Employee','_ASG_TD_YTD');
1500 FETCH get_defined_balance_id INTO g_nie_e_ytd_id;
1501 CLOSE get_defined_balance_id;
1502 --
1503 OPEN get_defined_balance_id('NI E Employee','_ASG_RUN');
1504 FETCH get_defined_balance_id INTO g_nie_e_ptd_id;
1505 CLOSE get_defined_balance_id;
1506 --
1507 OPEN get_defined_balance_id('NI E Employer','_ASG_TD_YTD');
1508 FETCH get_defined_balance_id INTO g_nie_r_ytd_id;
1509 CLOSE get_defined_balance_id;
1510 --
1511 OPEN get_defined_balance_id('NI E Employer','_ASG_RUN');
1512 FETCH get_defined_balance_id INTO g_nie_r_ptd_id;
1513 CLOSE get_defined_balance_id;
1514
1515 OPEN get_defined_balance_id('NI E Total','_ASG_TD_YTD');
1516 FETCH get_defined_balance_id INTO g_nie_tot_id;
1517 CLOSE get_defined_balance_id;
1518 --
1519 OPEN get_defined_balance_id('NI E Able LEL','_ASG_TD_YTD');
1520 FETCH get_defined_balance_id INTO g_nie_lel_id;
1521 CLOSE get_defined_balance_id;
1522 --
1523 OPEN get_defined_balance_id('NI E Able UEL','_ASG_TD_YTD');
1524 FETCH get_defined_balance_id INTO g_nie_uel_id;
1525 CLOSE get_defined_balance_id;
1526
1527 OPEN get_defined_balance_id('NI E Able UAP','_ASG_TD_YTD');
1528 FETCH get_defined_balance_id INTO g_nie_uap_id;
1529 CLOSE get_defined_balance_id;
1530
1531 OPEN get_defined_balance_id('NI E Able AUEL','_ASG_TD_YTD');
1532 FETCH get_defined_balance_id INTO g_nie_auel_id;
1533 CLOSE get_defined_balance_id;
1534
1535 OPEN get_defined_balance_id('NI E Able ET','_ASG_TD_YTD');
1536 FETCH get_defined_balance_id INTO g_nie_et_id;
1537 CLOSE get_defined_balance_id;
1538
1539 OPEN get_defined_balance_id('NI J Able','_ASG_TD_YTD');
1540 FETCH get_defined_balance_id INTO g_nij_able_id;
1541 CLOSE get_defined_balance_id;
1542 --
1543 OPEN get_defined_balance_id('NI J Able','_ASG_RUN');
1544 FETCH get_defined_balance_id INTO g_nij_able_id_run;
1545 CLOSE get_defined_balance_id;
1546 --
1547 OPEN get_defined_balance_id('NI J Employee','_ASG_TD_YTD');
1548 FETCH get_defined_balance_id INTO g_nij_e_ytd_id;
1549 CLOSE get_defined_balance_id;
1550 --
1551 OPEN get_defined_balance_id('NI J Employee','_ASG_RUN');
1552 FETCH get_defined_balance_id INTO g_nij_e_ptd_id;
1553 CLOSE get_defined_balance_id;
1554 --
1555 OPEN get_defined_balance_id('NI J Employer','_ASG_TD_YTD');
1556 FETCH get_defined_balance_id INTO g_nij_r_ytd_id;
1557 CLOSE get_defined_balance_id;
1558 --
1559 OPEN get_defined_balance_id('NI J Employer','_ASG_RUN');
1560 FETCH get_defined_balance_id INTO g_nij_r_ptd_id;
1561 CLOSE get_defined_balance_id;
1562 --
1563 OPEN get_defined_balance_id('NI J Total','_ASG_TD_YTD');
1564 FETCH get_defined_balance_id INTO g_nij_tot_id;
1565 CLOSE get_defined_balance_id;
1566 --
1567 OPEN get_defined_balance_id('NI J Able LEL','_ASG_TD_YTD');
1568 FETCH get_defined_balance_id INTO g_nij_lel_id;
1569 CLOSE get_defined_balance_id;
1570 --
1571 OPEN get_defined_balance_id('NI J Able UEL','_ASG_TD_YTD');
1572 FETCH get_defined_balance_id INTO g_nij_uel_id;
1573 CLOSE get_defined_balance_id;
1574
1575 OPEN get_defined_balance_id('NI J Able UAP','_ASG_TD_YTD');
1576 FETCH get_defined_balance_id INTO g_nij_uap_id;
1577 CLOSE get_defined_balance_id;
1578
1579 OPEN get_defined_balance_id('NI J Able AUEL','_ASG_TD_YTD');
1580 FETCH get_defined_balance_id INTO g_nij_auel_id;
1581 CLOSE get_defined_balance_id;
1582
1583 OPEN get_defined_balance_id('NI J Able ET','_ASG_TD_YTD');
1584 FETCH get_defined_balance_id INTO g_nij_et_id;
1585 CLOSE get_defined_balance_id;
1586 --
1587 OPEN get_defined_balance_id('NI L Able','_ASG_TD_YTD');
1588 FETCH get_defined_balance_id INTO g_nil_able_id;
1589 CLOSE get_defined_balance_id;
1590 --
1591 OPEN get_defined_balance_id('NI L Able','_ASG_RUN');
1592 FETCH get_defined_balance_id INTO g_nil_able_id_run;
1593 CLOSE get_defined_balance_id;
1594 --
1595 OPEN get_defined_balance_id('NI L Employee','_ASG_TD_YTD');
1596 FETCH get_defined_balance_id INTO g_nil_e_ytd_id;
1597 CLOSE get_defined_balance_id;
1598 --
1599 OPEN get_defined_balance_id('NI L Employee','_ASG_RUN');
1600 FETCH get_defined_balance_id INTO g_nil_e_ptd_id;
1601 CLOSE get_defined_balance_id;
1602 --
1603 OPEN get_defined_balance_id('NI L Employer','_ASG_TD_YTD');
1604 FETCH get_defined_balance_id INTO g_nil_r_ytd_id;
1605 CLOSE get_defined_balance_id;
1606 --
1607 OPEN get_defined_balance_id('NI L Employer','_ASG_RUN');
1608 FETCH get_defined_balance_id INTO g_nil_r_ptd_id;
1609 CLOSE get_defined_balance_id;
1610 --
1611 OPEN get_defined_balance_id('NI L Total','_ASG_TD_YTD');
1612 FETCH get_defined_balance_id INTO g_nil_tot_id;
1613 CLOSE get_defined_balance_id;
1614 --
1615 OPEN get_defined_balance_id('NI L Able LEL','_ASG_TD_YTD');
1616 FETCH get_defined_balance_id INTO g_nil_lel_id;
1617 CLOSE get_defined_balance_id;
1618 --
1619 OPEN get_defined_balance_id('NI L Able UEL','_ASG_TD_YTD');
1620 FETCH get_defined_balance_id INTO g_nil_uel_id;
1621 CLOSE get_defined_balance_id;
1622
1623 OPEN get_defined_balance_id('NI L Able UAP','_ASG_TD_YTD');
1624 FETCH get_defined_balance_id INTO g_nil_uap_id;
1625 CLOSE get_defined_balance_id;
1626
1627 OPEN get_defined_balance_id('NI L Able AUEL','_ASG_TD_YTD');
1628 FETCH get_defined_balance_id INTO g_nil_auel_id;
1629 CLOSE get_defined_balance_id;
1630
1631 OPEN get_defined_balance_id('NI L Able ET','_ASG_TD_YTD');
1632 FETCH get_defined_balance_id INTO g_nil_et_id;
1633 CLOSE get_defined_balance_id;
1634 --
1635 END load_defined_balances;
1636 --
1637 PROCEDURE load_defined_balances_aggr
1638 IS
1639 CURSOR get_defined_balance_id (p_balance_name VARCHAR2, p_dimension_name VARCHAR2)
1640 IS
1641 SELECT defined_balance_id
1642 FROM pay_defined_balances db,
1643 pay_balance_types b,
1644 pay_balance_dimensions d
1645 WHERE b.balance_name = p_balance_name
1646 AND d.dimension_name = p_dimension_name
1647 AND db.balance_type_id = b.balance_type_id
1648 AND db.balance_dimension_id = d.balance_dimension_id;
1649 BEGIN
1650 OPEN get_defined_balance_id('NI A Able','_PER_TD_YTD');
1651 FETCH get_defined_balance_id INTO g_nia_able_id;
1652 CLOSE get_defined_balance_id;
1653 --
1654 OPEN get_defined_balance_id('NI A Able','_ASG_RUN');
1655 FETCH get_defined_balance_id INTO g_nia_able_id_run;
1656 CLOSE get_defined_balance_id;
1657 --
1658 OPEN get_defined_balance_id('NI A Employee','_PER_TD_YTD');
1659 FETCH get_defined_balance_id INTO g_nia_e_ytd_id;
1660 CLOSE get_defined_balance_id;
1661 --
1662 OPEN get_defined_balance_id('NI A Employee','_ASG_RUN');
1663 FETCH get_defined_balance_id INTO g_nia_e_ptd_id;
1664 CLOSE get_defined_balance_id;
1665 --
1666 OPEN get_defined_balance_id('NI A Employer','_PER_TD_YTD');
1667 FETCH get_defined_balance_id INTO g_nia_r_ytd_id;
1668 CLOSE get_defined_balance_id;
1669 --
1670 OPEN get_defined_balance_id('NI A Employer','_ASG_RUN');
1671 FETCH get_defined_balance_id INTO g_nia_r_ptd_id;
1672 CLOSE get_defined_balance_id;
1673 --
1674 OPEN get_defined_balance_id('NI A Total','_PER_TD_YTD');
1675 FETCH get_defined_balance_id INTO g_nia_tot_id;
1676 CLOSE get_defined_balance_id;
1677 --
1678 OPEN get_defined_balance_id('NI A Able LEL','_PER_TD_YTD');
1679 FETCH get_defined_balance_id INTO g_nia_lel_id;
1680 CLOSE get_defined_balance_id;
1681 --
1682 OPEN get_defined_balance_id('NI A Able UEL','_PER_TD_YTD');
1683 FETCH get_defined_balance_id INTO g_nia_uel_id;
1684 CLOSE get_defined_balance_id;
1685 -- 8357870 begin
1686 OPEN get_defined_balance_id('NI A Able UAP','_PER_TD_YTD');
1687 FETCH get_defined_balance_id INTO g_nia_uap_id;
1688 CLOSE get_defined_balance_id;
1689 -- 8357870 end
1690 --EOY 07/08 begin
1691 OPEN get_defined_balance_id('NI A Able AUEL','_PER_TD_YTD');
1692 FETCH get_defined_balance_id INTO g_nia_auel_id;
1693 CLOSE get_defined_balance_id;
1694 --EOY 07/08 end
1695 OPEN get_defined_balance_id('NI A Able ET','_PER_TD_YTD');
1696 FETCH get_defined_balance_id INTO g_nia_et_id;
1697 CLOSE get_defined_balance_id;
1698 --
1699 OPEN get_defined_balance_id('NI B Able','_PER_TD_YTD');
1700 FETCH get_defined_balance_id INTO g_nib_able_id;
1701 CLOSE get_defined_balance_id;
1702 --
1703 OPEN get_defined_balance_id('NI B Able','_ASG_RUN');
1704 FETCH get_defined_balance_id INTO g_nib_able_id_run;
1705 CLOSE get_defined_balance_id;
1706 --
1707
1708 OPEN get_defined_balance_id('NI B Employee','_PER_TD_YTD');
1709 FETCH get_defined_balance_id INTO g_nib_e_ytd_id;
1710 CLOSE get_defined_balance_id;
1711 --
1712 OPEN get_defined_balance_id('NI B Employee','_ASG_RUN');
1713 FETCH get_defined_balance_id INTO g_nib_e_ptd_id;
1714 CLOSE get_defined_balance_id;
1715 --
1716 OPEN get_defined_balance_id('NI B Employer','_PER_TD_YTD');
1717 FETCH get_defined_balance_id INTO g_nib_r_ytd_id;
1718 CLOSE get_defined_balance_id;
1719 --
1720 OPEN get_defined_balance_id('NI B Employer','_ASG_RUN');
1721 FETCH get_defined_balance_id INTO g_nib_r_ptd_id;
1722 CLOSE get_defined_balance_id;
1723 --
1724 --
1725 OPEN get_defined_balance_id('NI B Total','_PER_TD_YTD');
1726 FETCH get_defined_balance_id INTO g_nib_tot_id;
1727 CLOSE get_defined_balance_id;
1728 --
1729 OPEN get_defined_balance_id('NI B Able LEL','_PER_TD_YTD');
1730 FETCH get_defined_balance_id INTO g_nib_lel_id;
1731 CLOSE get_defined_balance_id;
1732 --
1733 OPEN get_defined_balance_id('NI B Able UEL','_PER_TD_YTD');
1734 FETCH get_defined_balance_id INTO g_nib_uel_id;
1735 CLOSE get_defined_balance_id;
1736 -- 8357870 begin
1737 OPEN get_defined_balance_id('NI B Able UAP','_PER_TD_YTD');
1738 FETCH get_defined_balance_id INTO g_nib_uap_id;
1739 CLOSE get_defined_balance_id;
1740 -- 8357870 end
1741 --EOY 07/08 begin
1742 OPEN get_defined_balance_id('NI B Able AUEL','_PER_TD_YTD');
1743 FETCH get_defined_balance_id INTO g_nib_auel_id;
1744 CLOSE get_defined_balance_id;
1745 --EOY 07/08 end
1746 OPEN get_defined_balance_id('NI B Able ET','_PER_TD_YTD');
1747 FETCH get_defined_balance_id INTO g_nib_et_id;
1748 CLOSE get_defined_balance_id;
1749 --
1750 OPEN get_defined_balance_id('NI C Able LEL','_PER_TD_YTD');
1751 FETCH get_defined_balance_id INTO g_nic_lel_id;
1752 CLOSE get_defined_balance_id;
1753 --
1754 OPEN get_defined_balance_id('NI C Able UEL','_PER_TD_YTD');
1755 FETCH get_defined_balance_id INTO g_nic_uel_id;
1756 CLOSE get_defined_balance_id;
1757 -- 8357870 begin
1758 OPEN get_defined_balance_id('NI C Able UAP','_PER_TD_YTD');
1759 FETCH get_defined_balance_id INTO g_nic_uap_id;
1760 CLOSE get_defined_balance_id;
1761 -- 8357870 end
1762 --EOY 07/08 Begin
1763 OPEN get_defined_balance_id('NI C Able AUEL','_PER_TD_YTD');
1764 FETCH get_defined_balance_id INTO g_nic_auel_id;
1765 CLOSE get_defined_balance_id;
1766 --EOY 07/08 End
1767 OPEN get_defined_balance_id('NI C Able ET','_PER_TD_YTD');
1768 FETCH get_defined_balance_id INTO g_nic_et_id;
1769 CLOSE get_defined_balance_id;
1770 --
1771 OPEN get_defined_balance_id('NI C Able','_PER_TD_YTD');
1772 FETCH get_defined_balance_id INTO g_nic_able_id;
1773 CLOSE get_defined_balance_id;
1774 --
1775 OPEN get_defined_balance_id('NI C Able','_ASG_RUN');
1776 FETCH get_defined_balance_id INTO g_nic_able_id_run;
1777 CLOSE get_defined_balance_id;
1778 --
1779
1780 OPEN get_defined_balance_id('NI C Employee','_PER_TD_YTD');
1781 FETCH get_defined_balance_id INTO g_nic_e_ytd_id;
1782 CLOSE get_defined_balance_id;
1783 --
1784 OPEN get_defined_balance_id('NI C Employee','_ASG_RUN');
1785 FETCH get_defined_balance_id INTO g_nic_e_ptd_id;
1786 CLOSE get_defined_balance_id;
1787 --
1788 OPEN get_defined_balance_id('NI C Employer','_PER_TD_YTD');
1789 FETCH get_defined_balance_id INTO g_nic_r_ytd_id;
1790 CLOSE get_defined_balance_id;
1791 --
1792 OPEN get_defined_balance_id('NI C Employer','_ASG_RUN');
1793 FETCH get_defined_balance_id INTO g_nic_r_ptd_id;
1794 CLOSE get_defined_balance_id;
1795 -- Fix for Bug 1976152, added the below stmt to fetch the balance id
1796 -- for the balance NI C Employers Rebate
1797 OPEN get_defined_balance_id('NI C Ers Rebate','_PER_TD_YTD');
1798 FETCH get_defined_balance_id INTO g_nic_ers_rebate_id;
1799 CLOSE get_defined_balance_id;
1800 --
1801 OPEN get_defined_balance_id('NI D Able','_PER_TD_YTD');
1802 FETCH get_defined_balance_id INTO g_nid_able_id;
1803 CLOSE get_defined_balance_id;
1804 --
1805 OPEN get_defined_balance_id('NI D Able','_ASG_RUN');
1806 FETCH get_defined_balance_id INTO g_nid_able_id_run;
1807 CLOSE get_defined_balance_id;
1808 --
1809
1810 OPEN get_defined_balance_id('NI D Employee','_PER_TD_YTD');
1811 FETCH get_defined_balance_id INTO g_nid_e_ytd_id;
1812 CLOSE get_defined_balance_id;
1813 --
1814 OPEN get_defined_balance_id('NI D Employee','_ASG_RUN');
1815 FETCH get_defined_balance_id INTO g_nid_e_ptd_id;
1816 CLOSE get_defined_balance_id;
1817 --
1818 OPEN get_defined_balance_id('NI D Employer','_PER_TD_YTD');
1819 FETCH get_defined_balance_id INTO g_nid_r_ytd_id;
1820 CLOSE get_defined_balance_id;
1821 --
1822 OPEN get_defined_balance_id('NI D Employer','_ASG_RUN');
1823 FETCH get_defined_balance_id INTO g_nid_r_ptd_id;
1824 CLOSE get_defined_balance_id;
1825 --
1826 OPEN get_defined_balance_id('NI D Total','_PER_TD_YTD');
1827 FETCH get_defined_balance_id INTO g_nid_tot_id;
1828 CLOSE get_defined_balance_id;
1829 --
1830 OPEN get_defined_balance_id('NI D Able LEL','_PER_TD_YTD');
1831 FETCH get_defined_balance_id INTO g_nid_lel_id;
1832 CLOSE get_defined_balance_id;
1833 --
1834 OPEN get_defined_balance_id('NI D Able UEL','_PER_TD_YTD');
1835 FETCH get_defined_balance_id INTO g_nid_uel_id;
1836 CLOSE get_defined_balance_id;
1837 -- 8357870 begin
1838 OPEN get_defined_balance_id('NI D Able UAP','_PER_TD_YTD');
1839 FETCH get_defined_balance_id INTO g_nid_uap_id;
1840 CLOSE get_defined_balance_id;
1841 -- 8357870 end
1842 --EOY 07/08 Begin
1843 OPEN get_defined_balance_id('NI D Able AUEL','_PER_TD_YTD');
1844 FETCH get_defined_balance_id INTO g_nid_auel_id;
1845 CLOSE get_defined_balance_id;
1846 --EOY 07/08 End
1847 OPEN get_defined_balance_id('NI D Able ET','_PER_TD_YTD');
1848 FETCH get_defined_balance_id INTO g_nid_et_id;
1849 CLOSE get_defined_balance_id;
1850 --
1851 OPEN get_defined_balance_id('NI D Ers Rebate','_PER_TD_YTD');
1852 FETCH get_defined_balance_id INTO g_nid_ers_rebate_id;
1853 CLOSE get_defined_balance_id;
1854 --
1855 OPEN get_defined_balance_id('NI D Ees Rebate','_PER_TD_YTD');
1856 FETCH get_defined_balance_id INTO g_nid_ees_rebate_id;
1857 CLOSE get_defined_balance_id;
1858 --
1859 OPEN get_defined_balance_id('NI D Rebate to Employee','_PER_TD_YTD');
1860 FETCH get_defined_balance_id INTO g_nid_rebate_emp_id;
1861 CLOSE get_defined_balance_id;
1862 --
1863 OPEN get_defined_balance_id('NI E Able','_PER_TD_YTD');
1864 FETCH get_defined_balance_id INTO g_nie_able_id;
1865 CLOSE get_defined_balance_id;
1866 --
1867 OPEN get_defined_balance_id('NI E Able','_ASG_RUN');
1868 FETCH get_defined_balance_id INTO g_nie_able_id_run;
1869 CLOSE get_defined_balance_id;
1870 --
1871
1872 OPEN get_defined_balance_id('NI E Employee','_PER_TD_YTD');
1873 FETCH get_defined_balance_id INTO g_nie_e_ytd_id;
1874 CLOSE get_defined_balance_id;
1875 --
1876 OPEN get_defined_balance_id('NI E Employee','_ASG_RUN');
1877 FETCH get_defined_balance_id INTO g_nie_e_ptd_id;
1878 CLOSE get_defined_balance_id;
1879 --
1880 OPEN get_defined_balance_id('NI E Employer','_PER_TD_YTD');
1881 FETCH get_defined_balance_id INTO g_nie_r_ytd_id;
1882 CLOSE get_defined_balance_id;
1883 --
1884 OPEN get_defined_balance_id('NI E Employer','_ASG_RUN');
1885 FETCH get_defined_balance_id INTO g_nie_r_ptd_id;
1886 CLOSE get_defined_balance_id;
1887 OPEN get_defined_balance_id('NI E Total','_PER_TD_YTD');
1888 FETCH get_defined_balance_id INTO g_nie_tot_id;
1889 CLOSE get_defined_balance_id;
1890 --
1891 OPEN get_defined_balance_id('NI E Able LEL','_PER_TD_YTD');
1892 FETCH get_defined_balance_id INTO g_nie_lel_id;
1893 CLOSE get_defined_balance_id;
1894 --
1895 OPEN get_defined_balance_id('NI E Able UEL','_PER_TD_YTD');
1896 FETCH get_defined_balance_id INTO g_nie_uel_id;
1897 CLOSE get_defined_balance_id;
1898 -- 8357870 begin
1899 OPEN get_defined_balance_id('NI E Able UAP','_PER_TD_YTD');
1900 FETCH get_defined_balance_id INTO g_nie_uap_id;
1901 CLOSE get_defined_balance_id;
1902 -- 8357870 end
1903 --EOY 07/08 Begin
1904 OPEN get_defined_balance_id('NI E Able AUEL','_PER_TD_YTD');
1905 FETCH get_defined_balance_id INTO g_nie_auel_id;
1906 CLOSE get_defined_balance_id;
1907 --EOY 07/08 End
1908 OPEN get_defined_balance_id('NI E Able ET','_PER_TD_YTD');
1909 FETCH get_defined_balance_id INTO g_nie_et_id;
1910 CLOSE get_defined_balance_id;
1911 OPEN get_defined_balance_id('NI J Able','_PER_TD_YTD');
1912 FETCH get_defined_balance_id INTO g_nij_able_id;
1913 CLOSE get_defined_balance_id;
1914 --
1915 OPEN get_defined_balance_id('NI J Able','_ASG_RUN');
1916 FETCH get_defined_balance_id INTO g_nij_able_id_run;
1917 CLOSE get_defined_balance_id;
1918 --
1919
1920 OPEN get_defined_balance_id('NI J Employee','_PER_TD_YTD');
1921 FETCH get_defined_balance_id INTO g_nij_e_ytd_id;
1922 CLOSE get_defined_balance_id;
1923 --
1924 OPEN get_defined_balance_id('NI J Employee','_ASG_RUN');
1925 FETCH get_defined_balance_id INTO g_nij_e_ptd_id;
1926 CLOSE get_defined_balance_id;
1927 --
1928 OPEN get_defined_balance_id('NI J Employer','_PER_TD_YTD');
1929 FETCH get_defined_balance_id INTO g_nij_r_ytd_id;
1930 CLOSE get_defined_balance_id;
1931 --
1932 OPEN get_defined_balance_id('NI J Employer','_ASG_RUN');
1933 FETCH get_defined_balance_id INTO g_nij_r_ptd_id;
1934 CLOSE get_defined_balance_id;
1935 --
1936 OPEN get_defined_balance_id('NI J Total','_PER_TD_YTD');
1937 FETCH get_defined_balance_id INTO g_nij_tot_id;
1938 CLOSE get_defined_balance_id;
1939 --
1940 OPEN get_defined_balance_id('NI J Able LEL','_PER_TD_YTD');
1941 FETCH get_defined_balance_id INTO g_nij_lel_id;
1942 CLOSE get_defined_balance_id;
1943 --
1944 OPEN get_defined_balance_id('NI J Able UEL','_PER_TD_YTD');
1945 FETCH get_defined_balance_id INTO g_nij_uel_id;
1946 CLOSE get_defined_balance_id;
1947 -- 8357870 begin
1948 OPEN get_defined_balance_id('NI J Able UAP','_PER_TD_YTD');
1949 FETCH get_defined_balance_id INTO g_nij_uap_id;
1950 CLOSE get_defined_balance_id;
1951 -- 8357870 end
1952 --EOY 07/08 Begin
1953 OPEN get_defined_balance_id('NI J Able AUEL','_PER_TD_YTD');
1954 FETCH get_defined_balance_id INTO g_nij_auel_id;
1955 CLOSE get_defined_balance_id;
1956 --EOY 07/08 End
1957 OPEN get_defined_balance_id('NI J Able ET','_PER_TD_YTD');
1958 FETCH get_defined_balance_id INTO g_nij_et_id;
1959 CLOSE get_defined_balance_id;
1960 --
1961 OPEN get_defined_balance_id('NI L Able','_PER_TD_YTD');
1962 FETCH get_defined_balance_id INTO g_nil_able_id;
1963 CLOSE get_defined_balance_id;
1964 --
1965 OPEN get_defined_balance_id('NI L Able','_ASG_RUN');
1966 FETCH get_defined_balance_id INTO g_nil_able_id_run;
1967 CLOSE get_defined_balance_id;
1968 --
1969
1970 OPEN get_defined_balance_id('NI L Employee','_PER_TD_YTD');
1971 FETCH get_defined_balance_id INTO g_nil_e_ytd_id;
1972 CLOSE get_defined_balance_id;
1973 --
1974 OPEN get_defined_balance_id('NI L Employee','_ASG_RUN');
1975 FETCH get_defined_balance_id INTO g_nil_e_ptd_id;
1976 CLOSE get_defined_balance_id;
1977 --
1978 OPEN get_defined_balance_id('NI L Employer','_PER_TD_YTD');
1979 FETCH get_defined_balance_id INTO g_nil_r_ytd_id;
1980 CLOSE get_defined_balance_id;
1981 --
1982 OPEN get_defined_balance_id('NI L Employer','_ASG_RUN');
1983 FETCH get_defined_balance_id INTO g_nil_r_ptd_id;
1984 CLOSE get_defined_balance_id;
1985 --
1986 OPEN get_defined_balance_id('NI L Total','_PER_TD_YTD');
1987 FETCH get_defined_balance_id INTO g_nil_tot_id;
1988 CLOSE get_defined_balance_id;
1989 --
1990 OPEN get_defined_balance_id('NI L Able LEL','_PER_TD_YTD');
1991 FETCH get_defined_balance_id INTO g_nil_lel_id;
1992 CLOSE get_defined_balance_id;
1993 --
1994 OPEN get_defined_balance_id('NI L Able UEL','_PER_TD_YTD');
1995 FETCH get_defined_balance_id INTO g_nil_uel_id;
1996 CLOSE get_defined_balance_id;
1997 -- 8357870 begin
1998 OPEN get_defined_balance_id('NI L Able UAP','_PER_TD_YTD');
1999 FETCH get_defined_balance_id INTO g_nil_uap_id;
2000 CLOSE get_defined_balance_id;
2001 -- 8357870 end
2002 --EOY 07/08 Begin
2003 OPEN get_defined_balance_id('NI L Able AUEL','_PER_TD_YTD');
2004 FETCH get_defined_balance_id INTO g_nil_auel_id;
2005 CLOSE get_defined_balance_id;
2006 --EOY 07/08 End
2007 OPEN get_defined_balance_id('NI L Able ET','_PER_TD_YTD');
2008 FETCH get_defined_balance_id INTO g_nil_et_id;
2009 CLOSE get_defined_balance_id;
2010 --
2011 END load_defined_balances_aggr;
2012 --
2013 PROCEDURE get_ni_balances_for_asg(
2014 l_last_asg_action_id IN NUMBER,
2015 p_ni_a_balance IN OUT nocopy act_info_rec,
2016 p_ni_b_balance IN OUT nocopy act_info_rec,
2017 p_ni_c_balance IN OUT nocopy act_info_rec,
2018 p_ni_d_balance IN OUT nocopy act_info_rec,
2019 p_ni_e_balance IN OUT nocopy act_info_rec,
2020 p_ni_j_balance IN OUT nocopy act_info_rec,
2021 p_ni_l_balance IN OUT nocopy act_info_rec )
2022 IS
2023 -- local variables to store NI total and able balances
2024 l_nia_tot NUMBER(15) :=0;
2025 l_nia_able NUMBER(15) :=0;
2026 l_nib_tot NUMBER(15) :=0;
2027 l_nib_able NUMBER(15) :=0;
2028 l_nic_tot NUMBER(15) :=0;
2029 l_nic_able NUMBER(15) :=0;
2030 l_nid_tot NUMBER(15) :=0;
2031 l_nid_able NUMBER(15) :=0;
2032 l_nie_tot NUMBER(15) :=0;
2033 l_nie_able NUMBER(15) :=0;
2034 l_nij_tot NUMBER(15) :=0;
2035 l_nij_able NUMBER(15) :=0;
2036 l_nil_tot NUMBER(15) :=0;
2037 l_nil_able NUMBER(15) :=0;
2038
2039 l_nia_able_run NUMBER(15) :=0;
2040 l_nia_e_ptd NUMBER(15) :=0;
2041 l_nib_able_run NUMBER(15) :=0;
2042 l_nib_e_ptd NUMBER(15) :=0;
2043 l_nic_able_run NUMBER(15) :=0;
2044 l_nic_e_ptd NUMBER(15) :=0;
2045 l_nid_able_run NUMBER(15) :=0;
2046 l_nid_e_ptd NUMBER(15) :=0;
2047 l_nie_able_run NUMBER(15) :=0;
2048 l_nie_e_ptd NUMBER(15) :=0;
2049 l_nij_able_run NUMBER(15) :=0;
2050 l_nij_e_ptd NUMBER(15) :=0;
2051 l_nil_able_run NUMBER(15) :=0;
2052 l_nil_e_ptd NUMBER(15) :=0;
2053
2054 l_count_values NUMBER := 0;
2055 l_asg_action_id NUMBER;
2056 l_tax_pay_def_bal_id NUMBER;
2057 l_tax_pay_val NUMBER;
2058 l_child_flag VARCHAR2(2) :='N';
2059 l_reversal VARCHAR2(1);
2060
2061 CURSOR csr_child_act_id(c_lst_act_id NUMBER)
2062 IS
2063 SELECT ASSIGNMENT_ACTION_ID
2064 FROM pay_assignment_actions
2065 WHERE SOURCE_ACTION_ID = c_lst_act_id;
2066
2067
2068 CURSOR csr_get_taxable_pay(c_balance_name VARCHAR2, c_dim_name VARCHAR2)
2069 IS
2070 SELECT pdb.defined_balance_id
2071 FROM pay_defined_balances pdb,
2072 pay_balance_dimensions pbd,
2073 pay_balance_types pbt
2074 WHERE pbt.balance_name = c_balance_name
2075 AND pbd.database_item_suffix = c_dim_name
2076 AND pbt.balance_type_id = pdb.balance_type_id
2077 AND pbd.balance_dimension_id = pdb.balance_dimension_id
2078 AND pbt.legislation_code = 'GB';
2079 CURSOR csr_tax_pay_value(p_last_asg_action_id NUMBER,l_tax_pay_def_bal_id NUMBER)
2080 IS
2081 SELECT hr_dirbal.get_balance(p_last_asg_action_id,l_tax_pay_def_bal_id)
2082 FROM dual;
2083
2084 cursor csr_reversal is
2085 select ACTION_TYPE from pay_assignment_actions paa, pay_payroll_actions ppa
2086 where paa.assignment_action_id = l_last_asg_action_id
2087 and paa.payroll_action_id = ppa.payroll_action_id
2088 and ACTION_TYPE in ('V');
2089
2090 BEGIN
2091 hr_utility.trace('Entering PAY_GB_RTI_P60.get_ni_balances_for_asg.');
2092 load_defined_balances();
2093 -- fetch NI x Total/Able balances for checking whether this assignment
2094 -- reported in the P35 report or not. -- Bug 6271548
2095 hr_utility.trace('fetching NI x Total/Able balances');
2096
2097 OPEN csr_get_taxable_pay('Taxable Pay', '_ASG_RUN');
2098 FETCH csr_get_taxable_pay INTO l_tax_pay_def_bal_id;
2099 CLOSE csr_get_taxable_pay;
2100
2101 OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
2102 FETCH csr_tax_pay_value INTO l_tax_pay_val;
2103 CLOSE csr_tax_pay_value;
2104
2105 hr_utility.trace('l_last_asg_action_id: ' || l_last_asg_action_id);
2106 -- Test whether we can get run level value with parent action id.
2107 -- If not pass the child assignment action id.
2108
2109 hr_utility.trace('l_child_flag := '||l_child_flag);
2110
2111 IF l_tax_pay_val = 0 THEN
2112 OPEN csr_child_act_id(l_last_asg_action_id);
2113 FETCH csr_child_act_id INTO l_asg_action_id;
2114 CLOSE csr_child_act_id;
2115 l_child_flag := 'Y';
2116 ELSE
2117 l_asg_action_id := l_last_asg_action_id;
2118 l_child_flag := 'N';
2119 END IF;
2120
2121 if l_asg_action_id is null then
2122 l_asg_action_id := l_last_asg_action_id;
2123 l_child_flag := 'N';
2124 end if;
2125
2126 open csr_reversal;
2127 fetch csr_reversal into l_reversal;
2128 close csr_reversal;
2129
2130 if l_child_flag = 'N' then
2131
2132 hr_utility.trace('Processing NI with Master Action ID.');
2133 hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2134 -- NI A
2135 l_nia_tot := hr_dirbal.get_balance(l_asg_action_id,g_nia_tot_id);
2136 l_nia_able := hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id);
2137 -- NI B
2138 l_nib_tot := hr_dirbal.get_balance(l_asg_action_id, g_nib_tot_id);
2139 l_nib_able := hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id);
2140 -- NI C
2141 l_nic_tot := hr_dirbal.get_balance(l_asg_action_id,g_nic_tot_id);
2142 l_nic_able := hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id);
2143 -- NI D
2144 l_nid_tot := hr_dirbal.get_balance(l_asg_action_id,g_nid_tot_id);
2145 l_nid_able := hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id);
2146 -- NI E
2147 l_nie_tot := hr_dirbal.get_balance(l_asg_action_id,g_nie_tot_id);
2148 l_nie_able := hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id);
2149 -- NI J
2150 l_nij_tot := hr_dirbal.get_balance(l_asg_action_id,g_nij_tot_id);
2151 l_nij_able := hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id);
2152 -- NI L
2153 l_nil_tot := hr_dirbal.get_balance(l_asg_action_id,g_nil_tot_id);
2154 l_nil_able := hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id);
2155
2156 if l_reversal is not null then
2157
2158 l_nia_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id_run);
2159 l_nia_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ptd_id);
2160
2161 l_nib_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id_run);
2162 l_nib_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ptd_id);
2163
2164 l_nic_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id_run);
2165 l_nic_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ptd_id);
2166
2167 l_nid_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
2168 l_nid_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id);
2169
2170 l_nie_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id_run);
2171 l_nie_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ptd_id);
2172
2173 l_nij_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id_run);
2174 l_nij_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ptd_id);
2175
2176 l_nil_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id_run);
2177 l_nil_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
2178
2179 end if;
2180
2181
2182 IF (NVL(l_nia_tot,0) <> 0 OR NVL(l_nia_able,0) <> 0 ) or ( l_reversal is not null and NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_e_ptd,0) <> 0) THEN
2183 hr_utility.trace('fetching NI A Total/Able balances');
2184 p_ni_a_balance.act_info1 := 'A';
2185 p_ni_a_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id_run);
2186 p_ni_a_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nia_lel_id);
2187 p_ni_a_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nia_et_id);
2188 p_ni_a_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nia_uap_id);
2189 p_ni_a_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nia_uel_id);
2190 p_ni_a_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nia_r_ytd_id);
2191 p_ni_a_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ytd_id);
2192 p_ni_a_balance.act_info9 := hr_dirbal.get_balance(l_asg_action_id, g_nia_r_ptd_id);
2193 p_ni_a_balance.act_info10 := hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ptd_id);
2194 l_count_values := l_count_values +1;
2195 END IF;
2196 IF ( NVL(l_nib_tot,0) <> 0 OR NVL(l_nib_able,0) <> 0 ) or ( l_reversal is not null and NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_e_ptd,0) <> 0) THEN
2197 hr_utility.trace('fetching NI B Total/Able balances');
2198 p_ni_b_balance.act_info1 := 'B';
2199 p_ni_b_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id_run);
2200 p_ni_b_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nib_lel_id);
2201 p_ni_b_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nib_et_id);
2202 p_ni_b_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nib_uap_id);
2203 p_ni_b_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nib_uel_id);
2204 p_ni_b_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nib_r_ytd_id);
2205 p_ni_b_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ytd_id);
2206 p_ni_b_balance.act_info9 := hr_dirbal.get_balance(l_asg_action_id, g_nib_r_ptd_id);
2207 p_ni_b_balance.act_info10 := hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ptd_id);
2208 l_count_values := l_count_values +1;
2209 END IF;
2210 IF( NVL(l_nic_tot,0) <> 0 OR NVL(l_nic_able,0) <> 0 ) or ( l_reversal is not null and NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_e_ptd,0) <> 0) THEN
2211 hr_utility.trace('fetching NI C Total/Able balances');
2212 p_ni_c_balance.act_info1 := 'C';
2213 p_ni_c_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id_run);
2214 p_ni_c_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nic_lel_id);
2215 p_ni_c_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nic_et_id);
2216 p_ni_c_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nic_uap_id);
2217 p_ni_c_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nic_uel_id);
2218 p_ni_c_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nic_r_ytd_id);
2219 p_ni_c_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ytd_id);
2220 p_ni_c_balance.act_info9 := hr_dirbal.get_balance(l_asg_action_id, g_nic_r_ptd_id);
2221 p_ni_c_balance.act_info10 := hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ptd_id);
2222 l_count_values := l_count_values +1;
2223 END IF;
2224 IF ( NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 ) or ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
2225 hr_utility.trace('fetching NI D Total/Able balances');
2226 hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2227 hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2228 hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2229 hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2230 hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2231 p_ni_d_balance.act_info1 := 'D';
2232 p_ni_d_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
2233 p_ni_d_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
2234 p_ni_d_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nid_et_id);
2235 p_ni_d_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nid_uap_id);
2236 p_ni_d_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nid_uel_id);
2237 p_ni_d_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nid_r_ytd_id);
2238 p_ni_d_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ytd_id);
2239 p_ni_d_balance.act_info9 := hr_dirbal.get_balance(l_asg_action_id, g_nid_r_ptd_id);
2240 p_ni_d_balance.act_info10 := hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id);
2241 l_count_values := l_count_values +1;
2242 END IF;
2243 IF( NVL(l_nie_tot,0) <> 0 OR NVL(l_nie_able,0) <> 0 ) or ( l_reversal is not null and NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_e_ptd,0) <> 0) THEN
2244 hr_utility.trace('fetching NI E Total/Able balances');
2245 p_ni_e_balance.act_info1 := 'E';
2246 p_ni_e_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id_run);
2247 p_ni_e_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nie_lel_id);
2248 p_ni_e_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nie_et_id);
2249 p_ni_e_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nie_uap_id);
2250 p_ni_e_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nie_uel_id);
2251 p_ni_e_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nie_r_ytd_id);
2252 p_ni_e_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ytd_id);
2253 p_ni_e_balance.act_info9 := hr_dirbal.get_balance(l_asg_action_id, g_nie_r_ptd_id);
2254 p_ni_e_balance.act_info10 := hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ptd_id);
2255 l_count_values := l_count_values +1;
2256 END IF;
2257 IF( NVL(l_nij_tot,0) <> 0 OR NVL(l_nij_able,0) <> 0 ) or ( l_reversal is not null and NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_e_ptd,0) <> 0) THEN
2258 hr_utility.trace('fetching NI J Total/Able balances');
2259 p_ni_j_balance.act_info1 := 'J';
2260 p_ni_j_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id_run);
2261 p_ni_j_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nij_lel_id);
2262 p_ni_j_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nij_et_id);
2263 p_ni_j_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nij_uap_id);
2264 p_ni_j_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nij_uel_id);
2265 p_ni_j_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nij_r_ytd_id);
2266 p_ni_j_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ytd_id);
2267 p_ni_j_balance.act_info9 := hr_dirbal.get_balance(l_asg_action_id, g_nij_r_ptd_id);
2268 p_ni_j_balance.act_info10 := hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ptd_id);
2269 l_count_values := l_count_values +1;
2270 END IF;
2271 IF( NVL(l_nil_tot,0) <> 0 OR NVL(l_nil_able,0) <> 0 ) or ( l_reversal is not null and NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_e_ptd,0) <> 0) THEN
2272 hr_utility.trace('fetching NI L Total/Able balances');
2273 p_ni_l_balance.act_info1 := 'L';
2274 p_ni_l_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id_run);
2275 p_ni_l_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nil_lel_id);
2276 p_ni_l_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nil_et_id);
2277 p_ni_l_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nil_uap_id);
2278 p_ni_l_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nil_uel_id);
2279 p_ni_l_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nil_r_ytd_id);
2280 p_ni_l_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ytd_id);
2281 p_ni_l_balance.act_info9 := hr_dirbal.get_balance(l_asg_action_id, g_nil_r_ptd_id);
2282 p_ni_l_balance.act_info10 := hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
2283 l_count_values := l_count_values +1;
2284 END IF;
2285
2286 ELSE -- If Child Flag is set
2287 p_ni_a_balance.act_info7 := 0;
2288 p_ni_a_balance.act_info8 := 0;
2289 p_ni_a_balance.act_info9 := 0;
2290 p_ni_a_balance.act_info10 := 0;
2291
2292 p_ni_b_balance.act_info7 := 0;
2293 p_ni_b_balance.act_info8 := 0;
2294 p_ni_b_balance.act_info9 := 0;
2295 p_ni_b_balance.act_info10 := 0;
2296
2297
2298 p_ni_c_balance.act_info7 := 0;
2299 p_ni_c_balance.act_info8 := 0;
2300 p_ni_c_balance.act_info9 := 0;
2301 p_ni_c_balance.act_info10 := 0;
2302
2303 p_ni_d_balance.act_info7 := 0;
2304 p_ni_d_balance.act_info8 := 0;
2305 p_ni_d_balance.act_info9 := 0;
2306 p_ni_d_balance.act_info10 := 0;
2307
2308
2309 p_ni_e_balance.act_info7 := 0;
2310 p_ni_e_balance.act_info8 := 0;
2311 p_ni_e_balance.act_info9 := 0;
2312 p_ni_e_balance.act_info10 := 0;
2313
2314
2315 p_ni_j_balance.act_info7 := 0;
2316 p_ni_j_balance.act_info8 := 0;
2317 p_ni_j_balance.act_info9 := 0;
2318 p_ni_j_balance.act_info10 := 0;
2319
2320
2321 p_ni_l_balance.act_info7 := 0;
2322 p_ni_l_balance.act_info8 := 0;
2323 p_ni_l_balance.act_info9 := 0;
2324 p_ni_l_balance.act_info10 := 0;
2325
2326 for child in csr_child_act_id(l_last_asg_action_id)
2327 loop
2328
2329 l_asg_action_id := child.ASSIGNMENT_ACTION_ID;
2330 l_count_values :=0;
2331
2332 hr_utility.trace('Processing NI with Child Action ID.');
2333 hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2334
2335 -- NI A
2336 l_nia_tot := hr_dirbal.get_balance(l_last_asg_action_id,g_nia_tot_id);
2337 l_nia_able := hr_dirbal.get_balance(l_last_asg_action_id, g_nia_able_id);
2338 -- NI B
2339 l_nib_tot := hr_dirbal.get_balance(l_last_asg_action_id, g_nib_tot_id);
2340 l_nib_able := hr_dirbal.get_balance(l_last_asg_action_id, g_nib_able_id);
2341 -- NI C
2342 l_nic_tot := hr_dirbal.get_balance(l_last_asg_action_id,g_nic_tot_id);
2343 l_nic_able := hr_dirbal.get_balance(l_last_asg_action_id, g_nic_able_id);
2344 -- NI D
2345 l_nid_tot := hr_dirbal.get_balance(l_last_asg_action_id,g_nid_tot_id);
2346 l_nid_able := hr_dirbal.get_balance(l_last_asg_action_id, g_nid_able_id);
2347 -- NI E
2348 l_nie_tot := hr_dirbal.get_balance(l_last_asg_action_id,g_nie_tot_id);
2349 l_nie_able := hr_dirbal.get_balance(l_last_asg_action_id, g_nie_able_id);
2350 -- NI J
2351 l_nij_tot := hr_dirbal.get_balance(l_last_asg_action_id,g_nij_tot_id);
2352 l_nij_able := hr_dirbal.get_balance(l_last_asg_action_id, g_nij_able_id);
2353 -- NI L
2354 l_nil_tot := hr_dirbal.get_balance(l_last_asg_action_id,g_nil_tot_id);
2355 l_nil_able := hr_dirbal.get_balance(l_last_asg_action_id, g_nil_able_id);
2356
2357
2358 IF NVL(l_nia_tot,0) <> 0 OR NVL(l_nia_able,0) <> 0 THEN
2359 hr_utility.trace('fetching NI A Total/Able balances');
2360 p_ni_a_balance.act_info1 := 'A';
2361 p_ni_a_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id_run);
2362 p_ni_a_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nia_lel_id);
2363 p_ni_a_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nia_et_id);
2364 p_ni_a_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nia_uap_id);
2365 p_ni_a_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nia_uel_id);
2366 p_ni_a_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nia_r_ytd_id);
2367 p_ni_a_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ytd_id);
2368 p_ni_a_balance.act_info9 := p_ni_a_balance.act_info9 + (hr_dirbal.get_balance(l_asg_action_id, g_nia_r_ptd_id));
2369 p_ni_a_balance.act_info10 := p_ni_a_balance.act_info10 + (hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ptd_id));
2370 l_count_values := l_count_values +1;
2371 END IF;
2372 IF NVL(l_nib_tot,0) <> 0 OR NVL(l_nib_able,0) <> 0 THEN
2373 hr_utility.trace('fetching NI B Total/Able balances');
2374 p_ni_b_balance.act_info1 := 'B';
2375 p_ni_b_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id_run);
2376 p_ni_b_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nib_lel_id);
2377 p_ni_b_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nib_et_id);
2378 p_ni_b_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nib_uap_id);
2379 p_ni_b_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nib_uel_id);
2380 p_ni_b_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nib_r_ytd_id);
2381 p_ni_b_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ytd_id);
2382 p_ni_b_balance.act_info9 := p_ni_b_balance.act_info9 + (hr_dirbal.get_balance(l_asg_action_id, g_nib_r_ptd_id));
2383 p_ni_b_balance.act_info10 := p_ni_b_balance.act_info10 + (hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ptd_id));
2384 l_count_values := l_count_values +1;
2385 END IF;
2386 IF NVL(l_nic_tot,0) <> 0 OR NVL(l_nic_able,0) <> 0 THEN
2387 hr_utility.trace('fetching NI C Total/Able balances');
2388 p_ni_c_balance.act_info1 := 'C';
2389 p_ni_c_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id_run);
2390 p_ni_c_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nic_lel_id);
2391 p_ni_c_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nic_et_id);
2392 p_ni_c_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nic_uap_id);
2393 p_ni_c_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nic_uel_id);
2394 p_ni_c_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nic_r_ytd_id);
2395 p_ni_c_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ytd_id);
2396 p_ni_c_balance.act_info9 := p_ni_c_balance.act_info9 + (hr_dirbal.get_balance(l_asg_action_id, g_nic_r_ptd_id));
2397 p_ni_c_balance.act_info10 := p_ni_c_balance.act_info10 + (hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ptd_id));
2398 l_count_values := l_count_values +1;
2399 END IF;
2400 IF NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 THEN
2401 hr_utility.trace('fetching NI D Total/Able balances');
2402 hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2403 hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2404 hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2405 hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2406 hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2407 p_ni_d_balance.act_info1 := 'D';
2408 p_ni_d_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
2409 p_ni_d_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
2410 p_ni_d_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nid_et_id);
2411 p_ni_d_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nid_uap_id);
2412 p_ni_d_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nid_uel_id);
2413 p_ni_d_balance.act_info7 := hr_dirbal.get_balance(l_last_asg_action_id, g_nid_r_ytd_id);
2414 p_ni_d_balance.act_info8 := hr_dirbal.get_balance(l_last_asg_action_id, g_nid_e_ytd_id);
2415 p_ni_d_balance.act_info9 := p_ni_d_balance.act_info9 + (hr_dirbal.get_balance(l_asg_action_id, g_nid_r_ptd_id));
2416 p_ni_d_balance.act_info10 := p_ni_d_balance.act_info10 + (hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id));
2417 hr_utility.trace('p_ni_d_balance.act_info7 D'||p_ni_d_balance.act_info7);
2418 hr_utility.trace('p_ni_d_balance.act_info8 D'||p_ni_d_balance.act_info8);
2419 hr_utility.trace('p_ni_d_balance.act_info9 D'||p_ni_d_balance.act_info9);
2420 hr_utility.trace('p_ni_d_balance.act_info10 D'||p_ni_d_balance.act_info10);
2421
2422 l_count_values := l_count_values +1;
2423 END IF;
2424 IF NVL(l_nie_tot,0) <> 0 OR NVL(l_nie_able,0) <> 0 THEN
2425 hr_utility.trace('fetching NI E Total/Able balances');
2426 p_ni_e_balance.act_info1 := 'E';
2427 p_ni_e_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id_run);
2428 p_ni_e_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nie_lel_id);
2429 p_ni_e_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nie_et_id);
2430 p_ni_e_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nie_uap_id);
2431 p_ni_e_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nie_uel_id);
2432 p_ni_e_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nie_r_ytd_id);
2433 p_ni_e_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ytd_id);
2434 p_ni_e_balance.act_info9 := p_ni_e_balance.act_info9 + (hr_dirbal.get_balance(l_asg_action_id, g_nie_r_ptd_id));
2435 p_ni_e_balance.act_info10 := p_ni_e_balance.act_info10 + (hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ptd_id));
2436 l_count_values := l_count_values +1;
2437 END IF;
2438 IF NVL(l_nij_tot,0) <> 0 OR NVL(l_nij_able,0) <> 0 THEN
2439 hr_utility.trace('fetching NI J Total/Able balances');
2440 p_ni_j_balance.act_info1 := 'J';
2441 p_ni_j_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id_run);
2442 p_ni_j_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nij_lel_id);
2443 p_ni_j_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nij_et_id);
2444 p_ni_j_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nij_uap_id);
2445 p_ni_j_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nij_uel_id);
2446 p_ni_j_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nij_r_ytd_id);
2447 p_ni_j_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ytd_id);
2448 p_ni_j_balance.act_info9 := p_ni_j_balance.act_info9 + (hr_dirbal.get_balance(l_asg_action_id, g_nij_r_ptd_id));
2449 p_ni_j_balance.act_info10 := p_ni_j_balance.act_info10 + (hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ptd_id));
2450 l_count_values := l_count_values +1;
2451 END IF;
2452 IF NVL(l_nil_tot,0) <> 0 OR NVL(l_nil_able,0) <> 0 THEN
2453 hr_utility.trace('fetching NI L Total/Able balances');
2454 p_ni_l_balance.act_info1 := 'L';
2455 p_ni_l_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id_run);
2456 p_ni_l_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nil_lel_id);
2457 p_ni_l_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nil_et_id);
2458 p_ni_l_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nil_uap_id);
2459 p_ni_l_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nil_uel_id);
2460 p_ni_l_balance.act_info7 := hr_dirbal.get_balance(l_last_asg_action_id, g_nil_r_ytd_id);
2461 p_ni_l_balance.act_info8 := hr_dirbal.get_balance(l_last_asg_action_id, g_nil_e_ytd_id);
2462 p_ni_l_balance.act_info9 := p_ni_l_balance.act_info9 + (hr_dirbal.get_balance(l_asg_action_id, g_nil_r_ptd_id));
2463 p_ni_l_balance.act_info10 := p_ni_l_balance.act_info10 + (hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id));
2464 l_count_values := l_count_values +1;
2465 END IF;
2466 end loop; -- Child Loop Ends
2467 END IF; -- If Ends for Child flag
2468
2469 hr_utility.trace('Number of categories: ' || l_count_values);
2470
2471
2472 END get_ni_balances_for_asg;
2473
2474 PROCEDURE get_paye_aggr_ni_balances(
2475 p_asg_id IN NUMBER,
2476 l_last_asg_action_id IN NUMBER,
2477 p_ni_a_balance IN OUT nocopy act_info_rec,
2478 p_ni_b_balance IN OUT nocopy act_info_rec,
2479 p_ni_c_balance IN OUT nocopy act_info_rec,
2480 p_ni_d_balance IN OUT nocopy act_info_rec,
2481 p_ni_e_balance IN OUT nocopy act_info_rec,
2482 p_ni_j_balance IN OUT nocopy act_info_rec,
2483 p_ni_l_balance IN OUT nocopy act_info_rec )
2484 IS
2485 -- local variables to store NI total and able balances
2486 l_nia_tot NUMBER(15) :=0;
2487 l_nia_able NUMBER(15) :=0;
2488 l_nib_tot NUMBER(15) :=0;
2489 l_nib_able NUMBER(15) :=0;
2490 l_nic_tot NUMBER(15) :=0;
2491 l_nic_able NUMBER(15) :=0;
2492 l_nid_tot NUMBER(15) :=0;
2493 l_nid_able NUMBER(15) :=0;
2494 l_nie_tot NUMBER(15) :=0;
2495 l_nie_able NUMBER(15) :=0;
2496 l_nij_tot NUMBER(15) :=0;
2497 l_nij_able NUMBER(15) :=0;
2498 l_nil_tot NUMBER(15) :=0;
2499 l_nil_able NUMBER(15) :=0;
2500
2501 l_nia_able_run NUMBER(15) :=0;
2502 l_nia_e_ptd NUMBER(15) :=0;
2503 l_nib_able_run NUMBER(15) :=0;
2504 l_nib_e_ptd NUMBER(15) :=0;
2505 l_nic_able_run NUMBER(15) :=0;
2506 l_nic_e_ptd NUMBER(15) :=0;
2507 l_nid_able_run NUMBER(15) :=0;
2508 l_nid_e_ptd NUMBER(15) :=0;
2509 l_nie_able_run NUMBER(15) :=0;
2510 l_nie_e_ptd NUMBER(15) :=0;
2511 l_nij_able_run NUMBER(15) :=0;
2512 l_nij_e_ptd NUMBER(15) :=0;
2513 l_nil_able_run NUMBER(15) :=0;
2514 l_nil_e_ptd NUMBER(15) :=0;
2515
2516 l_count_values NUMBER := 0;
2517 l_asg_action_id NUMBER;
2518 l_tax_pay_def_bal_id NUMBER;
2519 l_tax_pay_val NUMBER;
2520 l_reversal VARCHAR2(1);
2521
2522 CURSOR csr_child_act_id(c_lst_act_id NUMBER)
2523 IS
2524 SELECT ASSIGNMENT_ACTION_ID
2525 FROM pay_assignment_actions
2526 WHERE SOURCE_ACTION_ID = c_lst_act_id;
2527 CURSOR csr_get_taxable_pay(c_balance_name VARCHAR2, c_dim_name VARCHAR2)
2528 IS
2529 SELECT pdb.defined_balance_id
2530 FROM pay_defined_balances pdb,
2531 pay_balance_dimensions pbd,
2532 pay_balance_types pbt
2533 WHERE pbt.balance_name = c_balance_name
2534 AND pbd.database_item_suffix = c_dim_name
2535 AND pbt.balance_type_id = pdb.balance_type_id
2536 AND pbd.balance_dimension_id = pdb.balance_dimension_id
2537 AND pbt.legislation_code = 'GB';
2538 CURSOR csr_tax_pay_value(p_last_asg_action_id NUMBER,l_tax_pay_def_bal_id NUMBER)
2539 IS
2540 SELECT hr_dirbal.get_balance(p_last_asg_action_id,l_tax_pay_def_bal_id)
2541 FROM dual;
2542
2543 cursor csr_reversal is
2544 select ACTION_TYPE from pay_assignment_actions paa, pay_payroll_actions ppa
2545 where paa.assignment_action_id = l_last_asg_action_id
2546 and paa.payroll_action_id = ppa.payroll_action_id
2547 and ACTION_TYPE in ('V');
2548
2549 BEGIN
2550 hr_utility.trace('Entering get_paye_aggr_ni_balances');
2551 load_defined_balances_aggr();
2552 -- fetch NI x Total/Able balances for checking whether this assignment
2553 -- reported in the P35 report or not. -- Bug 6271548
2554 hr_utility.trace('fetching NI x Total/Able balances');
2555 OPEN csr_get_taxable_pay('Taxable Pay', '_ASG_RUN');
2556 FETCH csr_get_taxable_pay INTO l_tax_pay_def_bal_id;
2557 CLOSE csr_get_taxable_pay;
2558 OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
2559 FETCH csr_tax_pay_value INTO l_tax_pay_val;
2560 CLOSE csr_tax_pay_value;
2561 hr_utility.trace('l_last_asg_action_id: ' || l_last_asg_action_id);
2562 -- Test whether we can get run level value with parent action id.
2563 -- If not pass the child assignment action id.
2564 IF l_tax_pay_val = 0 THEN
2565 OPEN csr_child_act_id(l_last_asg_action_id);
2566 FETCH csr_child_act_id INTO l_asg_action_id;
2567 CLOSE csr_child_act_id;
2568 ELSE
2569 l_asg_action_id := l_last_asg_action_id;
2570 END IF;
2571 hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2572 if l_asg_action_id is null then
2573 l_asg_action_id := l_last_asg_action_id;
2574 end if;
2575
2576 open csr_reversal;
2577 fetch csr_reversal into l_reversal;
2578 close csr_reversal;
2579
2580 -- NI A
2581 l_nia_tot := hr_dirbal.get_balance(l_asg_action_id,g_nia_tot_id);
2582 l_nia_able := hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id);
2583 -- NI B
2584 l_nib_tot := hr_dirbal.get_balance(l_asg_action_id, g_nib_tot_id);
2585 l_nib_able := hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id);
2586 -- NI C
2587 l_nic_tot := hr_dirbal.get_balance(l_asg_action_id,g_nic_tot_id);
2588 l_nic_able := hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id);
2589 -- NI D
2590 l_nid_tot := hr_dirbal.get_balance(l_asg_action_id,g_nid_tot_id);
2591 l_nid_able := hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id);
2592 -- NI E
2593 l_nie_tot := hr_dirbal.get_balance(l_asg_action_id,g_nie_tot_id);
2594 l_nie_able := hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id);
2595 -- NI J
2596 l_nij_tot := hr_dirbal.get_balance(l_asg_action_id,g_nij_tot_id);
2597 l_nij_able := hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id);
2598 -- NI L
2599 l_nil_tot := hr_dirbal.get_balance(l_asg_action_id,g_nil_tot_id);
2600 l_nil_able := hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id);
2601
2602 if l_reversal is not null then
2603
2604 l_nia_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id_run);
2605 l_nia_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ptd_id);
2606
2607 l_nib_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id_run);
2608 l_nib_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ptd_id);
2609
2610 l_nic_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id_run);
2611 l_nic_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ptd_id);
2612
2613 l_nid_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
2614 l_nid_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id);
2615
2616 l_nie_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id_run);
2617 l_nie_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ptd_id);
2618
2619 l_nij_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id_run);
2620 l_nij_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ptd_id);
2621
2622 l_nil_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id_run);
2623 l_nil_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
2624
2625 end if;
2626
2627
2628 IF (NVL(l_nia_tot,0) <> 0 OR NVL(l_nia_able,0) <> 0 ) or ( l_reversal is not null and NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_e_ptd,0) <> 0) THEN
2629 hr_utility.trace('fetching NI A Total/Able balances');
2630 p_ni_a_balance.assignment_id := p_asg_id;
2631 p_ni_a_balance.act_info1 := 'A';
2632 p_ni_a_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id_run);
2633 p_ni_a_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nia_lel_id);
2634 p_ni_a_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nia_et_id);
2635 p_ni_a_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nia_uap_id);
2636 p_ni_a_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nia_uel_id);
2637 p_ni_a_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nia_r_ytd_id);
2638 p_ni_a_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ytd_id);
2639 p_ni_a_balance.act_info9 := hr_dirbal.get_balance(l_asg_action_id, g_nia_r_ptd_id);
2640 p_ni_a_balance.act_info10 := hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ptd_id);
2641 l_count_values := l_count_values +1;
2642 END IF;
2643 IF ( NVL(l_nib_tot,0) <> 0 OR NVL(l_nib_able,0) <> 0 ) or ( l_reversal is not null and NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_e_ptd,0) <> 0) THEN
2644 hr_utility.trace('fetching NI B Total/Able balances');
2645 p_ni_b_balance.assignment_id := p_asg_id;
2646 p_ni_b_balance.act_info1 := 'B';
2647 p_ni_b_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id_run);
2648 p_ni_b_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nib_lel_id);
2649 p_ni_b_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nib_et_id);
2650 p_ni_b_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nib_uap_id);
2651 p_ni_b_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nib_uel_id);
2652 p_ni_b_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nib_r_ytd_id);
2653 p_ni_b_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ytd_id);
2654 p_ni_b_balance.act_info9 := hr_dirbal.get_balance(l_asg_action_id, g_nib_r_ptd_id);
2655 p_ni_b_balance.act_info10 := hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ptd_id);
2656 l_count_values := l_count_values +1;
2657 END IF;
2658 IF( NVL(l_nic_tot,0) <> 0 OR NVL(l_nic_able,0) <> 0 ) or ( l_reversal is not null and NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_e_ptd,0) <> 0) THEN
2659 hr_utility.trace('fetching NI C Total/Able balances');
2660 p_ni_c_balance.assignment_id := p_asg_id;
2661 p_ni_c_balance.act_info1 := 'C';
2662 p_ni_c_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id_run);
2663 p_ni_c_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nic_lel_id);
2664 p_ni_c_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nic_et_id);
2665 p_ni_c_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nic_uap_id);
2666 p_ni_c_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nic_uel_id);
2667 p_ni_c_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nic_r_ytd_id);
2668 p_ni_c_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ytd_id);
2669 p_ni_c_balance.act_info9 := hr_dirbal.get_balance(l_asg_action_id, g_nic_r_ptd_id);
2670 p_ni_c_balance.act_info10 := hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ptd_id);
2671 l_count_values := l_count_values +1;
2672 END IF;
2673 IF ( NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 ) or ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
2674 hr_utility.trace('fetching NI D Total/Able balances');
2675 hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2676 hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2677 hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2678 hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2679 hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2680 p_ni_d_balance.assignment_id := p_asg_id;
2681 p_ni_d_balance.act_info1 := 'D';
2682 p_ni_d_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
2683 p_ni_d_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
2684 p_ni_d_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nid_et_id);
2685 p_ni_d_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nid_uap_id);
2686 p_ni_d_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nid_uel_id);
2687 p_ni_d_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nid_r_ytd_id);
2688 p_ni_d_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ytd_id);
2689 p_ni_d_balance.act_info9 := hr_dirbal.get_balance(l_asg_action_id, g_nid_r_ptd_id);
2690 p_ni_d_balance.act_info10 := hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id);
2691 l_count_values := l_count_values +1;
2692 END IF;
2693 IF( NVL(l_nie_tot,0) <> 0 OR NVL(l_nie_able,0) <> 0 ) or ( l_reversal is not null and NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_e_ptd,0) <> 0) THEN
2694 hr_utility.trace('fetching NI E Total/Able balances');
2695 p_ni_e_balance.assignment_id := p_asg_id;
2696 p_ni_e_balance.act_info1 := 'E';
2697 p_ni_e_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id_run);
2698 p_ni_e_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nie_lel_id);
2699 p_ni_e_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nie_et_id);
2700 p_ni_e_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nie_uap_id);
2701 p_ni_e_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nie_uel_id);
2702 p_ni_e_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nie_r_ytd_id);
2703 p_ni_e_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ytd_id);
2704 p_ni_e_balance.act_info9 := hr_dirbal.get_balance(l_asg_action_id, g_nie_r_ptd_id);
2705 p_ni_e_balance.act_info10 := hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ptd_id);
2706 l_count_values := l_count_values +1;
2707 END IF;
2708 IF( NVL(l_nij_tot,0) <> 0 OR NVL(l_nij_able,0) <> 0 ) or ( l_reversal is not null and NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_e_ptd,0) <> 0) THEN
2709 hr_utility.trace('fetching NI J Total/Able balances');
2710 p_ni_j_balance.assignment_id := p_asg_id;
2711 p_ni_j_balance.act_info1 := 'J';
2712 p_ni_j_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id_run);
2713 p_ni_j_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nij_lel_id);
2714 p_ni_j_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nij_et_id);
2715 p_ni_j_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nij_uap_id);
2716 p_ni_j_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nij_uel_id);
2717 p_ni_j_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nij_r_ytd_id);
2718 p_ni_j_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ytd_id);
2719 p_ni_j_balance.act_info9 := hr_dirbal.get_balance(l_asg_action_id, g_nij_r_ptd_id);
2720 p_ni_j_balance.act_info10 := hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ptd_id);
2721 l_count_values := l_count_values +1;
2722 END IF;
2723 IF( NVL(l_nil_tot,0) <> 0 OR NVL(l_nil_able,0) <> 0 ) or ( l_reversal is not null and NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_e_ptd,0) <> 0) THEN
2724 hr_utility.trace('fetching NI L Total/Able balances');
2725 p_ni_l_balance.assignment_id := p_asg_id;
2726 p_ni_l_balance.act_info1 := 'L';
2727 p_ni_l_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id_run);
2728 p_ni_l_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nil_lel_id);
2729 p_ni_l_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nil_et_id);
2730 p_ni_l_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nil_uap_id);
2731 p_ni_l_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nil_uel_id);
2732 p_ni_l_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nil_r_ytd_id);
2733 p_ni_l_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ytd_id);
2734 p_ni_l_balance.act_info9 := hr_dirbal.get_balance(l_asg_action_id, g_nil_r_ptd_id);
2735 p_ni_l_balance.act_info10 := hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
2736 l_count_values := l_count_values +1;
2737 END IF;
2738
2739 hr_utility.trace('Number of categories: ' || l_count_values);
2740 END get_paye_aggr_ni_balances;
2741 --
2742 PROCEDURE get_ni_aggr_ni_balances(
2743 l_last_asg_action_id IN NUMBER,
2744 p_ni_a_balance IN OUT nocopy act_info_rec,
2745 p_ni_b_balance IN OUT nocopy act_info_rec,
2746 p_ni_c_balance IN OUT nocopy act_info_rec,
2747 p_ni_d_balance IN OUT nocopy act_info_rec,
2748 p_ni_e_balance IN OUT nocopy act_info_rec,
2749 p_ni_j_balance IN OUT nocopy act_info_rec,
2750 p_ni_l_balance IN OUT nocopy act_info_rec )
2751 IS
2752 -- local variables to store NI total and able balances
2753 l_nia_tot NUMBER(15) :=0;
2754 l_nia_able NUMBER(15) :=0;
2755 l_nib_tot NUMBER(15) :=0;
2756 l_nib_able NUMBER(15) :=0;
2757 l_nic_tot NUMBER(15) :=0;
2758 l_nic_able NUMBER(15) :=0;
2759 l_nid_tot NUMBER(15) :=0;
2760 l_nid_able NUMBER(15) :=0;
2761 l_nie_tot NUMBER(15) :=0;
2762 l_nie_able NUMBER(15) :=0;
2763 l_nij_tot NUMBER(15) :=0;
2764 l_nij_able NUMBER(15) :=0;
2765 l_nil_tot NUMBER(15) :=0;
2766 l_nil_able NUMBER(15) :=0;
2767
2768 l_nia_able_run NUMBER(15) :=0;
2769 l_nia_e_ptd NUMBER(15) :=0;
2770 l_nib_able_run NUMBER(15) :=0;
2771 l_nib_e_ptd NUMBER(15) :=0;
2772 l_nic_able_run NUMBER(15) :=0;
2773 l_nic_e_ptd NUMBER(15) :=0;
2774 l_nid_able_run NUMBER(15) :=0;
2775 l_nid_e_ptd NUMBER(15) :=0;
2776 l_nie_able_run NUMBER(15) :=0;
2777 l_nie_e_ptd NUMBER(15) :=0;
2778 l_nij_able_run NUMBER(15) :=0;
2779 l_nij_e_ptd NUMBER(15) :=0;
2780 l_nil_able_run NUMBER(15) :=0;
2781 l_nil_e_ptd NUMBER(15) :=0;
2782
2783 l_count_values NUMBER := 0;
2784 l_asg_action_id NUMBER;
2785 l_tax_pay_def_bal_id NUMBER;
2786 l_tax_pay_val NUMBER;
2787 l_reversal VARCHAR2(1);
2788
2789 CURSOR csr_child_act_id(c_lst_act_id NUMBER)
2790 IS
2791 SELECT ASSIGNMENT_ACTION_ID
2792 FROM pay_assignment_actions
2793 WHERE SOURCE_ACTION_ID = c_lst_act_id;
2794 CURSOR csr_get_taxable_pay(c_balance_name VARCHAR2, c_dim_name VARCHAR2)
2795 IS
2796 SELECT pdb.defined_balance_id
2797 FROM pay_defined_balances pdb,
2798 pay_balance_dimensions pbd,
2799 pay_balance_types pbt
2800 WHERE pbt.balance_name = c_balance_name
2801 AND pbd.database_item_suffix = c_dim_name
2802 AND pbt.balance_type_id = pdb.balance_type_id
2803 AND pbd.balance_dimension_id = pdb.balance_dimension_id
2804 AND pbt.legislation_code = 'GB';
2805 CURSOR csr_tax_pay_value(p_last_asg_action_id NUMBER,l_tax_pay_def_bal_id NUMBER)
2806 IS
2807 SELECT hr_dirbal.get_balance(p_last_asg_action_id,l_tax_pay_def_bal_id)
2808 FROM dual;
2809
2810 cursor csr_reversal is
2811 select ACTION_TYPE from pay_assignment_actions paa, pay_payroll_actions ppa
2812 where paa.assignment_action_id = l_last_asg_action_id
2813 and paa.payroll_action_id = ppa.payroll_action_id
2814 and ACTION_TYPE in ('V');
2815
2816 BEGIN
2817 hr_utility.trace('Entering get_ni_aggr_ni_balances');
2818 load_defined_balances_aggr();
2819 -- fetch NI x Total/Able balances for checking whether this assignment
2820 -- reported in the P35 report or not. -- Bug 6271548
2821 hr_utility.trace('fetching NI x Total/Able balances');
2822 OPEN csr_get_taxable_pay('Taxable Pay', '_ASG_RUN');
2823 FETCH csr_get_taxable_pay INTO l_tax_pay_def_bal_id;
2824 CLOSE csr_get_taxable_pay;
2825 OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
2826 FETCH csr_tax_pay_value INTO l_tax_pay_val;
2827 CLOSE csr_tax_pay_value;
2828 hr_utility.trace('l_last_asg_action_id: ' || l_last_asg_action_id);
2829 -- Test whether we can get run level value with parent action id.
2830 -- If not pass the child assignment action id.
2831 IF l_tax_pay_val = 0 THEN
2832 OPEN csr_child_act_id(l_last_asg_action_id);
2833 FETCH csr_child_act_id INTO l_asg_action_id;
2834 CLOSE csr_child_act_id;
2835 ELSE
2836 l_asg_action_id := l_last_asg_action_id;
2837 END IF;
2838 hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2839 if l_asg_action_id is null then
2840 l_asg_action_id := l_last_asg_action_id;
2841 end if;
2842
2843 open csr_reversal;
2844 fetch csr_reversal into l_reversal;
2845 close csr_reversal;
2846
2847 -- NI A
2848 l_nia_tot := hr_dirbal.get_balance(l_asg_action_id,g_nia_tot_id);
2849 l_nia_able := hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id);
2850 -- NI B
2851 l_nib_tot := hr_dirbal.get_balance(l_asg_action_id, g_nib_tot_id);
2852 l_nib_able := hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id);
2853 -- NI C
2854 l_nic_tot := hr_dirbal.get_balance(l_asg_action_id,g_nic_tot_id);
2855 l_nic_able := hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id);
2856 -- NI D
2857 l_nid_tot := hr_dirbal.get_balance(l_asg_action_id,g_nid_tot_id);
2858 l_nid_able := hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id);
2859 -- NI E
2860 l_nie_tot := hr_dirbal.get_balance(l_asg_action_id,g_nie_tot_id);
2861 l_nie_able := hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id);
2862 -- NI J
2863 l_nij_tot := hr_dirbal.get_balance(l_asg_action_id,g_nij_tot_id);
2864 l_nij_able := hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id);
2865 -- NI L
2866 l_nil_tot := hr_dirbal.get_balance(l_asg_action_id,g_nil_tot_id);
2867 l_nil_able := hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id);
2868
2869 if l_reversal is not null then
2870
2871 l_nia_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id_run);
2872 l_nia_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ptd_id);
2873
2874 l_nib_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id_run);
2875 l_nib_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ptd_id);
2876
2877 l_nic_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id_run);
2878 l_nic_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ptd_id);
2879
2880 l_nid_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
2881 l_nid_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id);
2882
2883 l_nie_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id_run);
2884 l_nie_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ptd_id);
2885
2886 l_nij_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id_run);
2887 l_nij_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ptd_id);
2888
2889 l_nil_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id_run);
2890 l_nil_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
2891
2892 end if;
2893
2894 IF (NVL(l_nia_tot,0) <> 0 OR NVL(l_nia_able,0) <> 0 ) or ( l_reversal is not null and NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_e_ptd,0) <> 0) THEN
2895 hr_utility.trace('fetching NI A Total/Able balances');
2896 p_ni_a_balance.act_info1 := 'A';
2897 p_ni_a_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id_run);
2898 p_ni_a_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nia_lel_id);
2899 p_ni_a_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nia_et_id);
2900 p_ni_a_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nia_uap_id);
2901 p_ni_a_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nia_uel_id);
2902 p_ni_a_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nia_r_ytd_id);
2903 p_ni_a_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ytd_id);
2904 p_ni_a_balance.act_info9 := hr_dirbal.get_balance(l_asg_action_id, g_nia_r_ptd_id);
2905 p_ni_a_balance.act_info10 := hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ptd_id);
2906 l_count_values := l_count_values +1;
2907 END IF;
2908 IF ( NVL(l_nib_tot,0) <> 0 OR NVL(l_nib_able,0) <> 0 ) or ( l_reversal is not null and NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_e_ptd,0) <> 0) THEN
2909 hr_utility.trace('fetching NI B Total/Able balances');
2910 p_ni_b_balance.act_info1 := 'B';
2911 p_ni_b_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id_run);
2912 p_ni_b_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nib_lel_id);
2913 p_ni_b_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nib_et_id);
2914 p_ni_b_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nib_uap_id);
2915 p_ni_b_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nib_uel_id);
2916 p_ni_b_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nib_r_ytd_id);
2917 p_ni_b_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ytd_id);
2918 p_ni_b_balance.act_info9 := hr_dirbal.get_balance(l_asg_action_id, g_nib_r_ptd_id);
2919 p_ni_b_balance.act_info10 := hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ptd_id);
2920 l_count_values := l_count_values +1;
2921 END IF;
2922 IF( NVL(l_nic_tot,0) <> 0 OR NVL(l_nic_able,0) <> 0 ) or ( l_reversal is not null and NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_e_ptd,0) <> 0) THEN
2923 hr_utility.trace('fetching NI C Total/Able balances');
2924 p_ni_c_balance.act_info1 := 'C';
2925 p_ni_c_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id_run);
2926 p_ni_c_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nic_lel_id);
2927 p_ni_c_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nic_et_id);
2928 p_ni_c_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nic_uap_id);
2929 p_ni_c_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nic_uel_id);
2930 p_ni_c_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nic_r_ytd_id);
2931 p_ni_c_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ytd_id);
2932 p_ni_c_balance.act_info9 := hr_dirbal.get_balance(l_asg_action_id, g_nic_r_ptd_id);
2933 p_ni_c_balance.act_info10 := hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ptd_id);
2934 l_count_values := l_count_values +1;
2935 END IF;
2936 IF ( NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 ) or ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
2937 hr_utility.trace('fetching NI D Total/Able balances');
2938 hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2939 hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2940 hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2941 hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2942 hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2943 p_ni_d_balance.act_info1 := 'D';
2944 p_ni_d_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
2945 p_ni_d_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
2946 p_ni_d_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nid_et_id);
2947 p_ni_d_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nid_uap_id);
2948 p_ni_d_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nid_uel_id);
2949 p_ni_d_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nid_r_ytd_id);
2950 p_ni_d_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ytd_id);
2951 p_ni_d_balance.act_info9 := hr_dirbal.get_balance(l_asg_action_id, g_nid_r_ptd_id);
2952 p_ni_d_balance.act_info10 := hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id);
2953 l_count_values := l_count_values +1;
2954 END IF;
2955 IF( NVL(l_nie_tot,0) <> 0 OR NVL(l_nie_able,0) <> 0 ) or ( l_reversal is not null and NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_e_ptd,0) <> 0) THEN
2956 hr_utility.trace('fetching NI E Total/Able balances');
2957 p_ni_e_balance.act_info1 := 'E';
2958 p_ni_e_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id_run);
2959 p_ni_e_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nie_lel_id);
2960 p_ni_e_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nie_et_id);
2961 p_ni_e_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nie_uap_id);
2962 p_ni_e_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nie_uel_id);
2963 p_ni_e_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nie_r_ytd_id);
2964 p_ni_e_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ytd_id);
2965 p_ni_e_balance.act_info9 := hr_dirbal.get_balance(l_asg_action_id, g_nie_r_ptd_id);
2966 p_ni_e_balance.act_info10 := hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ptd_id);
2967 l_count_values := l_count_values +1;
2968 END IF;
2969 IF( NVL(l_nij_tot,0) <> 0 OR NVL(l_nij_able,0) <> 0 ) or ( l_reversal is not null and NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_e_ptd,0) <> 0) THEN
2970 hr_utility.trace('fetching NI J Total/Able balances');
2971 p_ni_j_balance.act_info1 := 'J';
2972 p_ni_j_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id_run);
2973 p_ni_j_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nij_lel_id);
2974 p_ni_j_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nij_et_id);
2975 p_ni_j_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nij_uap_id);
2976 p_ni_j_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nij_uel_id);
2977 p_ni_j_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nij_r_ytd_id);
2978 p_ni_j_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ytd_id);
2979 p_ni_j_balance.act_info9 := hr_dirbal.get_balance(l_asg_action_id, g_nij_r_ptd_id);
2980 p_ni_j_balance.act_info10 := hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ptd_id);
2981 l_count_values := l_count_values +1;
2982 END IF;
2983 IF( NVL(l_nil_tot,0) <> 0 OR NVL(l_nil_able,0) <> 0 ) or ( l_reversal is not null and NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_e_ptd,0) <> 0) THEN
2984 hr_utility.trace('fetching NI L Total/Able balances');
2985 p_ni_l_balance.act_info1 := 'L';
2986 p_ni_l_balance.act_info2 := hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id_run);
2987 p_ni_l_balance.act_info3 := hr_dirbal.get_balance(l_last_asg_action_id, g_nil_lel_id);
2988 p_ni_l_balance.act_info4 := hr_dirbal.get_balance(l_last_asg_action_id, g_nil_et_id);
2989 p_ni_l_balance.act_info5 := hr_dirbal.get_balance(l_last_asg_action_id, g_nil_uap_id);
2990 p_ni_l_balance.act_info6 := hr_dirbal.get_balance(l_last_asg_action_id, g_nil_uel_id);
2991 p_ni_l_balance.act_info7 := hr_dirbal.get_balance(l_asg_action_id, g_nil_r_ytd_id);
2992 p_ni_l_balance.act_info8 := hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ytd_id);
2993 p_ni_l_balance.act_info9 := hr_dirbal.get_balance(l_asg_action_id, g_nil_r_ptd_id);
2994 p_ni_l_balance.act_info10 := hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
2995 l_count_values := l_count_values +1;
2996 END IF;
2997 hr_utility.trace('Number of categories: ' || l_count_values);
2998 END get_ni_aggr_ni_balances;
2999
3000
3001 --
3002 /*------------ PUBLIC PROCEDURES --------------*/
3003 /*--------------------------------------------------------------------------
3004 Name : range_cursor
3005 Purpose : This returns the select statement that is used to create the
3006 range rows.
3007 Arguments :
3008 Notes : The range cursor determines which people should be processed.
3009 The normal practice is to include everyone, and then limit
3010 the list during the assignment action creation.
3011 --------------------------------------------------------------------------*/
3012 --
3013
3014 PROCEDURE range_cursor (pactid IN NUMBER,
3015 sqlstr OUT NOCOPY VARCHAR2)
3016 -- public procedure which archives the payroll information, then returns a
3017 -- varchar2 defining a SQL Statement to select all the people that may be
3018 -- eligible for Year End reporting.
3019 -- The archiver uses this cursor to split the people into chunks for parallel
3020 -- processing.
3021 IS
3022 --
3023 l_proc CONSTANT VARCHAR2(32):= g_package||'range_cursor';
3024 -- vars for constructing the sqlstr
3025 l_range_cursor VARCHAR2(4000):= NULL;
3026 l_parameter_match VARCHAR2(500) := NULL;
3027 -- vars for constructing an error message:
3028 l_payroll_action_message VARCHAR(240);
3029 -- vars for holding SRS Parameters:
3030 l_start_year DATE;
3031 l_end_year DATE;
3032 l_business_group_id hr_organization_units.business_group_id%TYPE;
3033 l_permit_number VARCHAR2(12);
3034 l_tax_district_reference VARCHAR2(3); -- error check will ensure numeric
3035 l_tax_reference_number VARCHAR2(10); -- 4011263: length 10 chars
3036 l_test_indicator varchar2(1); -- 5909829 EOY to store test indicator value
3037 l_unique_test_id varchar2(50); -- 5909829 EOY to store unique test id value
3038 -- vars for returns from the API:
3039 l_archive_item_id ff_archive_items.archive_item_id%TYPE;
3040 l_ovn NUMBER;
3041 l_some_warning BOOLEAN;
3042 -- vars for holding payroll data:
3043 l_payroll_start_year DATE;
3044 l_payroll_end_year DATE;
3045 l_payroll_period_type VARCHAR2(30);
3046 l_payroll_max_period_number NUMBER;
3047 l_dummy NUMBER;
3048 -- exceptions
3049 tax_dist_ref_error EXCEPTION; -- raised when l_tax_district_reference
3050 -- has incorrect format
3051 inconsis_ref_error EXCEPTION; -- raised when a payroll has more than
3052 -- PAYE Ref in the tax year
3053 test_indicator_error EXCEPTION; -- raised when Test indicaor is Yes
3054 --
3055 cursor csr_parameter_info(p_pact_id NUMBER) IS
3056 SELECT
3057 -- to_date('06/04/'||to_char(start_date,'YYYY'),'dd/mm/yyyy') start_year,
3058 -- effective_date end_year,
3059 business_group_id,
3060 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
3061 'PERMIT'),1,12) permit,
3062 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
3063 'TAX_REF'),1,3) tax_dist,
3064 substr(ltrim(substr(pay_gb_eoy_archive.get_parameter(
3065 legislative_parameters,'TAX_REF'),4,11),'/'),1,10) tax_ref, -- 4011263: tax ref can be 10 chars long
3066 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
3067 'TEST'),1,1) test_indicator,
3068 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
3069 'UNIQUE_TEST_ID'),1,8) unique_test_id
3070 FROM pay_payroll_actions
3071 WHERE payroll_action_id = p_pact_id;
3072
3073 cursor csr_payrolls(p_bg_id NUMBER, p_end_year DATE) IS
3074 SELECT
3075 p.payroll_id payroll_id,
3076 substr(flex.segment10,1,12) permit_number,
3077 p.payroll_name payroll_name,
3078 substr(flex.segment1,1,3) tax_district_reference,
3079 substr(ltrim(substr(org_information1,4,11),'/') ,1,10) tax_reference,
3080 flex.segment1 emp_paye_ref,
3081 substr(org.org_information2 ,1,40) tax_district_name,
3082 substr(ltrim(org.org_information3),1,36) employers_name, -- 4011263: added ltrim
3083 substr(ltrim(org.org_information4),1,60) employers_address_line, -- 4011263: added ltrim
3084 substr(nvl(flex.segment14,org.org_information7),1,9) econ
3085
3086 FROM pay_all_payrolls_f p,
3087 hr_soft_coding_keyflex flex,
3088 hr_organization_information org
3089 WHERE p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
3090 AND org.org_information_context = 'Tax Details References'
3091 AND org.org_information1 = flex.segment1
3092 AND NVL(org.org_information10,'UK') = 'UK'
3093 AND flex.segment10 IS NOT NULL
3094 AND p.business_group_id = p_bg_id
3095 AND org.organization_id = p_bg_id
3096 AND p_end_year BETWEEN p.effective_start_date
3097 AND p.effective_end_date;
3098 --
3099 l_payroll_name pay_all_payrolls_f.payroll_name%TYPE;
3100 --
3101 -- cursor to find a different PAYE Ref within a tax year on a given payroll
3102 CURSOR csr_another_paye_ref(p_payroll_id NUMBER,
3103 p_end_year DATE,
3104 p_paye_ref VARCHAR2) IS
3105 SELECT flex.segment1
3106 FROM pay_all_payrolls_f p,
3107 hr_soft_coding_keyflex flex
3108 WHERE p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id(+)
3109 AND p.payroll_id = p_payroll_id
3110 AND p.effective_start_date <= hr_gbbal.span_end(p_end_year)
3111 AND p.effective_end_date >= hr_gbbal.span_start(p_end_year)
3112 AND nvl(flex.segment1, 'XYZ') <> nvl(p_paye_ref, 'ABC');
3113 --
3114 l_another_paye_ref hr_soft_coding_keyflex.segment1%TYPE;
3115 --
3116 cursor csr_payroll_year (p_payroll_id NUMBER,
3117 p_start_year DATE,
3118 p_end_year DATE) IS
3119 SELECT
3120 min(start_date) start_year,
3121 max(end_date) end_year,
3122 max(period_type) period_type,
3123 max(period_num) max_period_number
3124 FROM per_time_periods ptp
3125 WHERE ptp.payroll_id = p_payroll_id
3126 AND ptp.regular_payment_date BETWEEN p_start_year
3127 AND p_end_year;
3128 --
3129 cursor csr_user_entity(p_entity_name VARCHAR2) IS
3130 SELECT user_entity_id
3131 FROM ff_user_entities
3132 WHERE user_entity_name = p_entity_name
3133 AND legislation_code = 'GB'
3134 AND business_group_id IS NULL;
3135
3136 BEGIN
3137 hr_utility.set_location('Entering: '||l_proc,1);
3138 --
3139 -- Find payroll action parameters
3140 /*
3141 OPEN csr_parameter_info(pactid);
3142 FETCH csr_parameter_info INTO --l_start_year,
3143 -- l_end_year,
3144 l_business_group_id,
3145 l_permit_number,
3146 l_tax_district_reference,
3147 l_tax_reference_number,
3148 l_test_indicator,
3149 l_unique_test_id;
3150
3151 CLOSE csr_parameter_info;
3152 */
3153
3154 -- Unique Test ID is mandatory if EDI Test indicator is Yes
3155
3156 IF (l_test_indicator = 'Y' AND l_unique_test_id IS NULL) THEN
3157 fnd_file.put_line (fnd_file.LOG,'You must provide a Unique Test ID if the EDI Test Indicator is Yes.');
3158 -- RAISE test_indicator_error;
3159 END IF;
3160
3161 -- End BUG 5909829 EOY
3162
3163 BEGIN -- ensure tax district reference is numeric (if supplied)
3164 IF to_number(l_tax_district_reference) < 0 THEN
3165 RAISE value_error;
3166 END IF;
3167 EXCEPTION
3168 WHEN value_error THEN
3169 RAISE tax_dist_ref_error;
3170 END;
3171 hr_utility.set_location(l_proc,10);
3172 --
3173 l_payroll_name := NULL;
3174 -- Extract Payroll info
3175
3176 hr_utility.set_location(l_proc,30);
3177 -- sqlstr must contain one and only one entry of :payroll_action_id
3178 -- it must be ordered by person_id
3179 --
3180 sqlstr := 'SELECT DISTINCT person_id
3181 FROM per_all_people_f ppf,
3182 pay_payroll_actions ppa
3183 WHERE ppa.payroll_action_id = :payroll_action_id
3184 AND ppa.business_group_id +0= ppf.business_group_id
3185 --and rownum < 50
3186 ORDER BY ppf.person_id';
3187 hr_utility.set_location('Leaving: '||l_proc,40);
3188 EXCEPTION
3189 WHEN OTHERS THEN
3190 hr_utility.set_location(' Leaving: '||l_proc,50);
3191 fnd_file.put_line(fnd_file.log,
3192 substr('Error in rangecode '||sqlerrm(sqlcode),1,80));
3193 -- Return cursor that selects no rows
3194 sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
3195 END range_cursor;
3196
3197 --
3198 --
3199 PROCEDURE internal_action_creation(
3200 pactid IN NUMBER,
3201 stperson IN NUMBER,
3202 endperson IN NUMBER,
3203 chunk IN NUMBER,
3204 p_info_type IN VARCHAR2,
3205 p_rep_type IN VARCHAR2)
3206 IS
3207 l_proc CONSTANT VARCHAR2(90):= g_package||'internal_action_creation';
3208 l_payroll_id NUMBER;
3209 l_tax_ref VARCHAR2(20);
3210 l_business_group_id NUMBER;
3211 l_end_date DATE;
3212 l_start_date DATE;
3213 l_ass_act_id NUMBER;
3214 l_assignment_id NUMBER;
3215 l_arch BOOLEAN;
3216 lockingactid NUMBER;
3217 l_locked_action_id NUMBER;
3218 l_exist NUMBER;
3219 l_first_fps VARCHAR2(1);
3220 l_first_fps_run_date date;
3221 l_first_fps_eff_date date;
3222 l_asg_set_id number;
3223
3224 CURSOR csr_parameter_info
3225 IS
3226 SELECT --to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id,
3227 SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
3228 -- SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'FIRST_FPS'),1,1) first_fps,
3229 effective_date,
3230 business_group_id,
3231 SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'ASG_SET'),1,20) asg_set_id
3232 -- fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'FIRST_FPS_DATE'),1,10)) first_fps_date
3233 FROM pay_payroll_actions pact
3234 WHERE payroll_action_id = pactid;
3235
3236 CURSOR csr_asg(c_asg_id NUMBER, c_effective_date DATE)
3237 IS
3238 SELECT asg.person_id person_id,
3239 trim(asg.primary_flag) asg_primary_flag,
3240 trim(nvl(pap.per_information10,'N')) per_agg_flag,
3241 trim(nvl(pap.per_information9,'N')) ni_agg_flag
3242 FROM per_all_people_f pap,
3243 per_all_assignments_f asg
3244 WHERE asg.assignment_id = c_asg_id
3245 AND pap.person_id = asg.person_id
3246 AND asg.business_group_id = l_business_group_id
3247 AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
3248 AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
3249
3250 l_flag VARCHAR2(1) :='Y';
3251 l_prev_person NUMBER :=0;
3252 --
3253 l_effective_date DATE;
3254 l_asg_primary_flag VARCHAR2(1);
3255 l_per_agg_flag VARCHAR2(1);
3256 l_ni_agg_flag VARCHAR2(1);
3257 l_pre_pact_id NUMBER;
3258
3259
3260 -- First FPS Cursors
3261 cursor csr_get_prepayment_date(p_pre_pact_id number) is
3262 select EFFECTIVE_DATE from pay_payroll_actions where payroll_action_id = p_pre_pact_id;
3263
3264 --For RTIP60
3265 cursor csr_asg_details(c_tax_ref varchar2,c_eff_date date,c_asg_set_id number) is
3266 select distinct paaf.assignment_id assignment_id,
3267 trim(paaf.primary_flag) asg_primary_flag,
3268 trim(pap.per_information10) per_agg_flag,
3269 pap.person_id
3270 from per_all_people_f pap,
3271 per_all_assignments_f paaf,
3272 per_assignment_status_types past,
3273 pay_all_payrolls_f papf,
3274 hr_soft_coding_keyflex flex
3275 where pap.person_id between stperson and endperson
3276 and pap.person_id = paaf.person_id
3277 and paaf.assignment_type = 'E'
3278 and paaf.assignment_status_type_id = past.assignment_status_type_id
3279 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN','TERM_ASSIGN')
3280 and pap.effective_start_date =
3281 ( select max(pap2.effective_start_date) from
3282 per_all_people_f pap2
3283 where pap2.person_id = pap.person_id
3284 and pap2.effective_start_date <= c_eff_date
3285 )
3286 and paaf.effective_start_date =
3287 ( select max(asg2.effective_start_date)
3288 from per_all_assignments_f asg2
3289 where asg2.assignment_id = paaf.assignment_id
3290 and asg2.assignment_type = 'E'
3291 and asg2.effective_start_date <= c_eff_date
3292 )
3293 AND papf.payroll_id = paaf.payroll_id
3294 AND flex.SOFT_CODING_KEYFLEX_ID = papf.SOFT_CODING_KEYFLEX_ID
3295 AND upper(flex.segment1) = upper(c_tax_ref)
3296 AND (c_asg_set_id IS NULL -- don't check for assignment set in this case
3297 OR EXISTS (SELECT 1 FROM hr_assignment_sets has1
3298 WHERE has1.assignment_set_id = c_asg_set_id
3299 -- AND has1.business_group_id = asg.business_group_id
3300 AND nvl(has1.payroll_id, paaf.payroll_id ) = paaf.payroll_id
3301 AND (NOT EXISTS (SELECT 1 -- chk no amendmts
3302 FROM hr_assignment_set_amendments hasa1
3303 WHERE hasa1.assignment_set_id =
3304 has1.assignment_set_id)
3305 OR EXISTS (SELECT 1 -- chk include amendmts
3306 FROM hr_assignment_set_amendments hasa2
3307 WHERE hasa2.assignment_set_id =
3308 has1.assignment_set_id
3309 AND hasa2.assignment_id = paaf.assignment_id
3310 AND nvl(hasa2.include_or_exclude,'I') = 'I')
3311 OR (NOT EXISTS (SELECT 1 --chk no exlude amendmts
3312 FROM hr_assignment_set_amendments hasa3
3313 WHERE hasa3.assignment_set_id =
3314 has1.assignment_set_id
3315 AND hasa3.assignment_id = paaf.assignment_id
3316 AND nvl(hasa3.include_or_exclude,'I') = 'E')
3317 AND NOT EXISTS (SELECT 1 --and chk no Inc amendmts
3318 FROM hr_assignment_set_amendments hasa4
3319 WHERE hasa4.assignment_set_id =
3320 has1.assignment_set_id
3321 AND nvl(hasa4.include_or_exclude,'I') = 'I') ) -- end checking exclude amendmts
3322 ) -- done checking amendments
3323 ) -- done asg set check when not null
3324 )
3325
3326 --and pap.person_id = 41689
3327 order by pap.person_id;
3328
3329 -- get the defined balance id for specified balance and dimension
3330 cursor get_defined_balance_id
3331 (p_balance_name VARCHAR2, p_dimension_name VARCHAR2) IS
3332 SELECT defined_balance_id
3333 FROM pay_defined_balances db
3334 , pay_balance_types b
3335 , pay_balance_dimensions d
3336 WHERE b.balance_name = p_balance_name
3337 AND d.dimension_name = p_dimension_name
3338 AND db.balance_type_id = b.balance_type_id
3339 AND db.balance_dimension_id = d.balance_dimension_id
3340 AND b.legislation_code = 'GB'
3341 AND d.legislation_code = 'GB';
3342
3343 -- to fetch last assignment action id for the given assignment
3344 cursor csr_last_action(p_asgid NUMBER, p_start_year DATE,p_end_year DATE) IS
3345 SELECT
3346 to_number (substr (max (lpad (paa.action_sequence, 15
3347 , '0')
3348 || paa.assignment_action_id), 16))
3349 , max (pact.effective_date) effective_date
3350 FROM pay_assignment_actions paa
3351 , pay_payroll_actions pact
3352 , per_time_periods ptp
3353 WHERE paa.assignment_id = p_asgid
3354 AND paa.payroll_action_id = pact.payroll_action_id
3355 --AND pact.payroll_id = l_payroll_id --commented for testing
3356 AND pact.time_period_id = ptp.time_period_id
3357 AND pact.action_type IN ('Q', 'R', 'B'
3358 , 'I', 'V')
3359 AND paa.action_status IN ('C', 'S')
3360 AND ptp.regular_payment_date
3361 BETWEEN p_start_year
3362 AND p_end_year;
3363
3364 l_person_id number;
3365
3366
3367 -- new agg changes
3368 CURSOR csr_asg_id(c_aggr_max_act_id number)
3369 IS
3370 SELECT assignment_id
3371 FROM pay_assignment_actions
3372 WHERE assignment_action_id = c_aggr_max_act_id;
3373
3374 -- Declarations for First FPS
3375 l_last_asg_action_id number;
3376
3377 l_gross number;
3378 l_taxable number;
3379 l_niable number;
3380 l_gross_id number;
3381 l_taxable_id number;
3382 l_niable_id number;
3383
3384 l_first_fps_prepay_asg varchar2(10);
3385
3386 l_date_soy date;
3387 l_date_eoy date;
3388 l_pre_id number;
3389 l_prepayment_date date;
3390
3391 l_asg_action_id number;
3392 l_asg_id number;
3393 l_last_effective_date date;
3394
3395 --Newly added for RTI P60
3396 l_stud_loan_id number;
3397 l_smp_id number;
3398 l_sap_id number;
3399 l_spp_adopt_id number;
3400 l_spp_birth_id number;
3401 l_aspp_adopt_id number;
3402 l_aspp_birth_id number;
3403
3404 l_stud_loan number;
3405 l_smp number;
3406 l_sap number;
3407 l_spp_adopt number;
3408 l_spp_birth number;
3409 l_aspp_adopt number;
3410 l_aspp_birth number;
3411
3412 BEGIN
3413 hr_utility.set_location('Entering: '||l_proc,1);
3414
3415 OPEN csr_parameter_info;
3416 FETCH csr_parameter_info
3417 INTO l_tax_ref,
3418 l_effective_date,
3419 l_business_group_id,
3420 l_asg_set_id;
3421 CLOSE csr_parameter_info;
3422
3423 hr_utility.set_location('process start date'||l_start_date,20);
3424 hr_utility.set_location('process end date'||l_end_date,20);
3425 hr_utility.set_location('pactid: '||pactid,20);
3426 hr_utility.set_location('stperson: '||stperson,20);
3427 hr_utility.set_location('endperson: '||endperson,20);
3428 hr_utility.set_location('l_payroll_id: '||l_payroll_id,20);
3429 hr_utility.set_location('l_tax_ref: '||l_tax_ref,20);
3430 hr_utility.set_location('l_effective_date: '||l_effective_date,20);
3431 hr_utility.set_location('l_business_group_id: '||l_business_group_id,20);
3432 hr_utility.set_location('g_start_year: '||g_start_year,20);
3433 hr_utility.set_location('l_first_fps_run_date: '||l_first_fps_run_date,20);
3434 hr_utility.set_location('l_first_fps_eff_date: '||l_first_fps_eff_date,20);
3435
3436 hr_utility.trace(' Payroll : '||l_payroll_id);
3437
3438 IF l_effective_date >= to_date('06-04-'||SUBSTR(TO_CHAR(l_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY' ) THEN
3439 g_start_year := to_date('06-04-'||SUBSTR(TO_CHAR(l_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY' ) ;
3440 g_end_year := to_date('05-04-'||TO_CHAR(to_number(SUBSTR(TO_CHAR(l_effective_date,'YYYY/MON/DD'),1,4))+1 ),'DD-MM-YYYY') ;
3441 ELSE
3442 g_start_year := to_date('06-04-'||TO_CHAR(to_number(SUBSTR(TO_CHAR(l_effective_date,'YYYY/MON/DD'),1,4))-1 ),'DD-MM-YYYY') ;
3443 g_end_year := to_date('05-04-'||SUBSTR(TO_CHAR(l_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY') ;
3444 END IF;
3445
3446 hr_utility.set_location('pactid :' || pactid,15);
3447 hr_utility.set_location('stperson :' || stperson,15);
3448 hr_utility.set_location('endperson :' || endperson,15);
3449 hr_utility.set_location('l_payroll_id :' || l_payroll_id,15);
3450 hr_utility.set_location('l_pre_pact_id :' || l_pre_pact_id,15);
3451 hr_utility.set_location('l_effective_date :' || l_effective_date,15);
3452
3453 hr_utility.set_location('l_first_fps : '||l_first_fps,20);
3454 hr_utility.set_location('l_effective_date :' || l_effective_date,15);
3455 hr_utility.set_location('g_start_year :' || g_start_year,15);
3456 hr_utility.set_location('g_end_year :' || g_end_year,15);
3457
3458 FOR asg_record IN csr_asg_details(l_tax_ref, l_effective_date,l_asg_set_id)
3459 LOOP
3460 l_flag := 'Y';
3461
3462 hr_utility.set_location('l_flag: '||l_flag,20);
3463
3464 OPEN get_defined_balance_id('Gross Pay','_ASG_TD_YTD');
3465 FETCH get_defined_balance_id INTO l_gross_id;
3466 CLOSE get_defined_balance_id;
3467
3468 OPEN get_defined_balance_id('Taxable Pay','_ASG_TD_YTD');
3469 FETCH get_defined_balance_id INTO l_taxable_id;
3470 CLOSE get_defined_balance_id;
3471
3472 OPEN get_defined_balance_id('NIable Pay','_ASG_TD_YTD');
3473 FETCH get_defined_balance_id INTO l_niable_id;
3474 CLOSE get_defined_balance_id;
3475
3476 OPEN get_defined_balance_id('Student Loan','_ASG_TD_YTD');
3477 FETCH get_defined_balance_id INTO l_stud_loan_id;
3478 CLOSE get_defined_balance_id;
3479
3480 OPEN get_defined_balance_id('SMP Total','_ASG_TD_YTD');
3481 FETCH get_defined_balance_id INTO l_smp_id;
3482 CLOSE get_defined_balance_id;
3483
3484 OPEN get_defined_balance_id('SAP Total','_ASG_TD_YTD');
3485 FETCH get_defined_balance_id INTO l_sap_id;
3486 CLOSE get_defined_balance_id;
3487
3488 OPEN get_defined_balance_id('SPP Adoption Total','_ASG_TD_YTD');
3489 FETCH get_defined_balance_id INTO l_spp_adopt_id;
3490 CLOSE get_defined_balance_id;
3491
3492 OPEN get_defined_balance_id('SPP Birth Total','_ASG_TD_YTD');
3493 FETCH get_defined_balance_id INTO l_spp_birth_id;
3494 CLOSE get_defined_balance_id;
3495
3496 OPEN get_defined_balance_id('ASPP Adoption Total','_ASG_TD_YTD');
3497 FETCH get_defined_balance_id INTO l_aspp_adopt_id;
3498 CLOSE get_defined_balance_id;
3499
3500 OPEN get_defined_balance_id('ASPP Birth Total','_ASG_TD_YTD');
3501 FETCH get_defined_balance_id INTO l_aspp_birth_id;
3502 CLOSE get_defined_balance_id;
3503
3504 -- Tax Year Start and End
3505 If l_effective_date >= to_date('06-04-'||substr(to_char(l_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY' ) Then
3506 l_date_soy := to_date('06-04-'||substr(to_char(l_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY' ) ;
3507 l_date_eoy := to_date('05-04-'||to_char(to_number(substr(to_char(l_effective_date,'YYYY/MON/DD'),1,4))+1 ),'DD-MM-YYYY') ;
3508 Else
3509 l_date_soy := to_date('06-04-'||to_char(to_number(substr(to_char(l_effective_date,'YYYY/MON/DD'),1,4))-1 ),'DD-MM-YYYY') ;
3510 l_date_eoy := to_date('05-04-'||substr(to_char(l_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY') ;
3511 End If;
3512
3513 hr_utility.trace('asg_record.assignment_id '||asg_record.assignment_id);
3514 hr_utility.trace('l_date_soy '||l_date_soy);
3515 hr_utility.trace('l_date_eoy '||l_date_eoy);
3516
3517 -- To get latest assignment action id for the assignment
3518 OPEN csr_last_action(asg_record.assignment_id,l_date_soy,l_date_eoy);
3519 FETCH csr_last_action
3520 INTO l_last_asg_action_id, l_last_effective_date;
3521
3522 -- If there are no assignment action ids, no payment has been made for the assignment
3523 -- This assignment will not be archived.
3524 IF l_last_asg_action_id is null then
3525 l_flag:= 'N';
3526 end if;
3527 CLOSE csr_last_action;
3528
3529 -- Fetching balances
3530 IF l_last_asg_action_id IS NOT NULL THEN
3531 l_gross := pay_balance_pkg.get_value(l_gross_id,l_last_asg_action_id);
3532 IF l_gross <= 0 THEN
3533 l_taxable := pay_balance_pkg.get_value(l_taxable_id,l_last_asg_action_id);
3534 END IF;
3535
3536 IF l_taxable <= 0 THEN
3537 l_niable := pay_balance_pkg.get_value(l_niable_id,l_last_asg_action_id);
3538 end if;
3539
3540 IF l_gross <= 0 THEN
3541 l_stud_loan := pay_balance_pkg.get_value(l_stud_loan_id,l_last_asg_action_id);
3542 l_smp := pay_balance_pkg.get_value(l_smp_id,l_last_asg_action_id);
3543 l_sap := pay_balance_pkg.get_value(l_sap_id,l_last_asg_action_id);
3544 l_spp_adopt := pay_balance_pkg.get_value(l_spp_adopt_id,l_last_asg_action_id);
3545 l_spp_birth := pay_balance_pkg.get_value(l_spp_birth_id,l_last_asg_action_id);
3546 l_aspp_adopt := pay_balance_pkg.get_value(l_aspp_adopt_id,l_last_asg_action_id);
3547 l_aspp_birth := pay_balance_pkg.get_value(l_aspp_birth_id,l_last_asg_action_id);
3548 END IF;
3549
3550 END IF;
3551
3552 IF (l_gross > 0 or l_taxable > 0 or l_niable > 0 or l_stud_loan > 0 or l_smp > 0 or l_sap > 0
3553 or l_spp_adopt > 0 or l_spp_birth > 0 or l_aspp_adopt > 0 or l_aspp_birth > 0)
3554 THEN
3555 hr_utility.trace('Assignment '||asg_record.assignment_id||' has YTD values');
3556 ELSE
3557 hr_utility.trace('Assignment '||asg_record.assignment_id||' has No YTD values.');
3558 l_flag:= 'N';
3559 END IF;
3560
3561 if l_flag = 'Y' Then --and l_first_fps_prepay_asg = 'N' Then
3562 SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;
3563 -- Insert assignment into pay_assignment_actions
3564 hr_nonrun_asact.insact(lockingactid, asg_record.assignment_id, pactid, chunk, NULL);
3565 hr_nonrun_asact.insint(lockingactid, l_last_asg_action_id);
3566 End if;
3567
3568 END LOOP;
3569
3570 hr_utility.set_location('Leaving: '||l_proc,999);
3571 END internal_action_creation;
3572 --
3573 --
3574 --
3575 /*--------------------------------------------------------------------------
3576 Name : action_creation
3577 Purpose : This creates the assignment actions for a specific chunk.
3578 Arguments :
3579 Notes :
3580 --------------------------------------------------------------------------*/
3581 PROCEDURE rti_fps_action_creation(
3582 pactid IN NUMBER,
3583 stperson IN NUMBER,
3584 endperson IN NUMBER,
3585 chunk IN NUMBER)
3586 IS
3587 BEGIN
3588 internal_action_creation(pactid, stperson, endperson, chunk,'GB_RTI_FPS', 'RTI_FPS_REP');
3589 END rti_fps_action_creation;
3590 ---
3591 ---
3592
3593
3594 /*--------------------------------------------------------------------------
3595 Name : archinit
3596 Purpose : This procedure can be used to perform an initialisation
3597 section and validation of Employer details
3598 Arguments :
3599 Notes :
3600 --------------------------------------------------------------------------*/
3601 PROCEDURE archinit(
3602 p_payroll_action_id IN NUMBER)
3603 IS
3604 error_found EXCEPTION;
3605 l_proc CONSTANT VARCHAR2(50) := g_package || ' archinit';
3606 l_sender_id hr_organization_information.org_information11%TYPE;
3607 l_tax_ref hr_organization_information.org_information1%TYPE;
3608 l_tax_dist hr_organization_information.org_information2%TYPE;
3609 l_employer_addr VARCHAR2(255);
3610 l_employer_name VARCHAR2(150);
3611 l_err BOOLEAN;
3612 l_exp EXCEPTION;
3613 l_rep_typ VARCHAR2(100);
3614 l_effective_date DATE;
3615
3616
3617 CURSOR csr_parameter_info(p_payroll_action_id NUMBER)
3618 IS
3619 SELECT effective_date,
3620 business_group_id,
3621 SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PRE_PAY_ID'),1,20) pre_pay_id,
3622 SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYROLL_ID'),1,20) payroll_id,
3623 SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
3624 SUBSTR(pay_gb_eoy_archive.get_parameter (legislative_parameters, 'FIRST_FPS'), 1,1) first_fps
3625
3626 FROM pay_payroll_actions
3627 WHERE payroll_action_id = p_payroll_action_id;
3628
3629 cursor csr_prepayment_date(pre_pay_id number)
3630 is
3631 SELECT
3632 ppa.effective_date
3633 FROM
3634 pay_payroll_actions ppa
3635 WHERE
3636 ppa.payroll_action_id = pre_pay_id;
3637
3638 CURSOR csr_pre_pact_eff_date(p_payroll_action_id NUMBER)
3639 IS
3640 SELECT effective_date
3641 FROM pay_payroll_actions
3642 WHERE payroll_action_id = p_payroll_action_id;
3643
3644 CURSOR csr_payroll_details(pactid NUMBER)
3645 IS
3646 SELECT NVL(UPPER(hoi.org_information11),' ') sender_id,
3647 DECODE(SUBSTR(pact.legislative_parameters,instr(pact.legislative_parameters,'TEST=') + 5,1) ,'N',' ','Y','1') test_indicator,
3648 fnd_number.number_to_canonical(pact.request_id) request_id,
3649 NVL(upper(SUBSTR(ltrim(hoi.org_information3),1,35)),' ') employer_name,
3650 NVL(upper(SUBSTR(ltrim(SUBSTR(hoi.org_information1,4,11),'/'),1,10)),' ') tax_ref_no,
3651 lpad(NVL(SUBSTR(hoi.org_information1,1,3),' '),3,0) tax_office_no,
3652 DECODE(PAY_GB_EOY_MAGTAPE.get_payroll_version, ' ', '0', PAY_GB_EOY_MAGTAPE.get_payroll_version) payroll_ver,
3653 NVL(upper(SUBSTR(hoi.org_information6,1,13)),' ') acc_ref_no,
3654 report_type
3655 FROM pay_payroll_actions pact,
3656 hr_organization_information hoi
3657 WHERE pact.payroll_action_id =pactid
3658 AND pact.business_group_id = hoi.organization_id
3659 AND hoi.org_information_context = 'Tax Details References'
3660 AND NVL(hoi.org_information10,'UK') = 'UK'
3661 AND SUBSTR(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8, instr(pact.legislative_parameters
3662 ||' ',' ', instr(pact.legislative_parameters,'TAX_REF=') +8) - instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
3663
3664 CURSOR csr_ni_details
3665 IS
3666 SELECT petf.element_type_id,
3667 input_value_id
3668 FROM pay_element_types_f petf,
3669 pay_input_values_f pivf
3670 WHERE petf.element_name = 'NI'
3671 AND petf.element_type_id = pivf.element_type_id
3672 AND pivf.name = 'Process Type'
3673 AND petf.legislation_code = 'GB'
3674 AND pivf.legislation_code = 'GB'
3675 AND l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
3676 AND l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date;
3677
3678 CURSOR get_defined_balance_id
3679 IS
3680 SELECT b.balance_name balance_name,
3681 b.balance_type_id balance_type_id,
3682 d.database_item_suffix database_item_suffix,
3683 d.balance_dimension_id balance_dimension_id,
3684 db.defined_balance_id defined_balance_id
3685 FROM pay_defined_balances db,
3686 pay_balance_types b,
3687 pay_balance_dimensions d
3688 WHERE db.balance_type_id = b.balance_type_id
3689 AND db.balance_dimension_id = d.balance_dimension_id
3690 AND ((d.database_item_suffix = '_ASG_TD_YTD'
3691 AND b.balance_name IN ('Taxable Pay', 'PAYE', 'Student Loan', 'SMP Total',
3692 'SAP Total', 'SPP Adoption Total', 'SPP Birth Total',
3693 'ASPP Adoption Total', 'ASPP Birth Total', 'Widows and Orphans'))
3694
3695 OR(d.database_item_suffix = '_PER_TD_YTD'
3696 AND b.balance_name IN ('Taxable Pay', 'Student Loan', 'SMP Total',
3697 'SAP Total', 'SPP Adoption Total', 'SPP Birth Total',
3698 'ASPP Adoption Total', 'ASPP Birth Total', 'Widows and Orphans'))
3699
3700 OR(d.database_item_suffix = '_PER_TD_CPE_YTD'
3701 AND b.balance_name IN ('PAYE')))
3702 AND b.legislation_code = 'GB'
3703 AND d.legislation_code = 'GB'
3704 AND db.legislation_code = 'GB';
3705
3706 l_count NUMBER := 1;
3707 l_fps_effective_date VARCHAR2(30);
3708 l_balance_counter NUMBER;
3709 taxable_id NUMBER :=1;
3710 paye_id NUMBER :=2;
3711 student_loan_id NUMBER :=3;
3712 taxable_ptd_id NUMBER :=4;
3713 student_loan_ptd_id NUMBER :=5;
3714 paye_ptd_id NUMBER :=6;
3715 ssp_id NUMBER :=7;
3716 smp_id NUMBER :=8;
3717 ospp_adopt_id NUMBER :=9;
3718 sap_id NUMBER :=10;
3719 aspp_adopt_id NUMBER :=11;
3720 l_dummy NUMBER;
3721 l_pre_pact_effective_date DATE;
3722 l_first_fps_run_date date;
3723
3724 BEGIN
3725 hr_utility.set_location('Entering '|| l_proc, 10);
3726 l_err := FALSE;
3727
3728 OPEN csr_parameter_info(p_payroll_action_id);
3729 FETCH csr_parameter_info
3730 INTO l_effective_date,
3731 g_business_group_id,
3732 g_pre_pact_id,
3733 g_payroll_id,
3734 g_tax_ref,
3735 g_first_fps;
3736 CLOSE csr_parameter_info;
3737
3738 g_fps_effective_date := l_effective_date;
3739 g_payroll_action_id := p_payroll_action_id;
3740
3741 hr_utility.trace('l_effective_date: '||l_effective_date);
3742 hr_utility.trace('g_fps_effective_date: '||g_fps_effective_date);
3743 hr_utility.trace('g_business_group_id: '||g_business_group_id);
3744 hr_utility.trace('g_pre_pact_id: '||g_pre_pact_id);
3745 hr_utility.trace('g_payroll_id: '||g_payroll_id);
3746
3747 hr_utility.trace('l_first_fps_run_date: '||l_first_fps_run_date);
3748
3749 SELECT distinct element_type_id
3750 INTO g_paye_element_id
3751 FROM pay_element_types_f
3752 WHERE element_name = 'PAYE'
3753 AND legislation_code = 'GB';
3754
3755 SELECT distinct element_type_id
3756 INTO g_paye_details_id
3757 FROM pay_element_types_f
3758 WHERE element_name = 'PAYE Details'
3759 AND legislation_code = 'GB';
3760
3761 hr_utility.trace('g_paye_element_id: '||TO_CHAR(g_paye_element_id));
3762
3763 IF l_effective_date >= to_date('06-04-'||SUBSTR(TO_CHAR(l_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY' ) THEN
3764 g_start_year := to_date('06-04-'||SUBSTR(TO_CHAR(l_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY' ) ;
3765 g_end_year := to_date('05-04-'||TO_CHAR(to_number(SUBSTR(TO_CHAR(l_effective_date,'YYYY/MON/DD'),1,4))+1 ),'DD-MM-YYYY') ;
3766 ELSE
3767 g_start_year := to_date('06-04-'||TO_CHAR(to_number(SUBSTR(TO_CHAR(l_effective_date,'YYYY/MON/DD'),1,4))-1 ),'DD-MM-YYYY') ;
3768 g_end_year := to_date('05-04-'||SUBSTR(TO_CHAR(l_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY') ;
3769 END IF;
3770
3771 hr_utility.trace('g_start_year: '||g_start_year);
3772 hr_utility.trace('g_end_year: '||g_end_year);
3773 hr_utility.trace('l_pre_pact_effective_date: '||l_pre_pact_effective_date);
3774
3775 OPEN csr_ni_details;
3776 FETCH csr_ni_details INTO g_ni_pt_eid, g_ni_pt_ivid;
3777 CLOSE csr_ni_details;
3778
3779 --******************************
3780 l_balance_counter := 1;
3781 FOR rec IN get_defined_balance_id
3782 LOOP
3783 g_fps_bal_det_tab(l_balance_counter).balance_name := rec.balance_name;
3784 hr_utility.trace('balance_name: '||g_fps_bal_det_tab(l_balance_counter).balance_name);
3785 g_fps_bal_det_tab(l_balance_counter).balance_type_id := rec.balance_type_id;
3786 hr_utility.trace('balance_type_id: '||g_fps_bal_det_tab(l_balance_counter).balance_type_id);
3787 g_fps_bal_det_tab(l_balance_counter).database_item_suffix := rec.database_item_suffix;
3788 hr_utility.trace('database_item_suffix: '||g_fps_bal_det_tab(l_balance_counter).database_item_suffix);
3789 g_fps_bal_det_tab(l_balance_counter).balance_dimension_id := rec.balance_dimension_id;
3790 hr_utility.trace('balance_dimension_id: '||g_fps_bal_det_tab(l_balance_counter).balance_dimension_id);
3791 g_fps_bal_det_tab(l_balance_counter).defined_balance_id := rec.defined_balance_id;
3792 hr_utility.trace('defined_balance_id: '||g_fps_bal_det_tab(l_balance_counter).defined_balance_id);
3793 l_balance_counter := l_balance_counter + 1;
3794 hr_utility.trace('Balance Counter: '||l_balance_counter);
3795 END LOOP;
3796
3797 FOR i IN 1..g_fps_bal_det_tab.last
3798 LOOP
3799 g_defined_balance_lst(i).defined_balance_id := g_fps_bal_det_tab(i).defined_balance_id;
3800 END LOOP;
3801
3802 hr_utility.set_location('Leaving '|| l_proc, 10);
3803
3804 EXCEPTION
3805 WHEN OTHERS THEN
3806 fnd_file.put_line(fnd_file.LOG,'Exception:' || SQLCODE || ' - ' || SQLERRM);
3807 hr_utility.raise_error;
3808 END archinit;
3809 --
3810 --
3811 --
3812 /*--------------------------------------------------------------------------
3813 Name : archive_data
3814 Purpose : This archives the data required for RTI - P60.
3815 --------------------------------------------------------------------------*/
3816 --
3817 --
3818 PROCEDURE archive_code(
3819 p_assactid IN NUMBER,
3820 p_effective_date IN DATE)
3821 IS
3822 l_proc CONSTANT VARCHAR2(35):= g_package||'archive_code';
3823 error_found EXCEPTION;
3824 already_processed EXCEPTION;
3825 l_archive_tab action_info_table;
3826 l_ni_rpt_asg_id NUMBER;
3827 l_ni_rpt_date DATE;
3828 l_archive_asg BOOLEAN;
3829 l_asg_action_id number;
3830 l_assact_id number;
3831
3832 --Below cursor fetches the basic details of the current assignment
3833 CURSOR csr_asg_basic_details(c_asg_act_id NUMBER)
3834 IS
3835 SELECT asg.assignment_id,
3836 asg.EFFECTIVE_START_DATE asg_eff_start_date,
3837 asg.person_id,
3838 asg.assignment_number,
3839 asg.payroll_id
3840 FROM per_all_assignments_f asg,
3841 pay_assignment_actions paa,
3842 pay_payroll_actions ppa
3843 WHERE paa.assignment_action_id = c_asg_act_id
3844 AND paa.assignment_id = asg.assignment_id
3845 AND ppa.payroll_action_id = paa.payroll_action_id
3846 AND (ppa.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
3847 OR asg.effective_end_date BETWEEN g_start_year AND g_end_year)
3848 AND asg.effective_start_date = (SELECT max(asg1.effective_start_date)
3849 FROM per_all_assignments_f asg1
3850 WHERE asg1.assignment_id = asg.assignment_id
3851 AND asg1.effective_start_date <= g_end_year);
3852 ---
3853
3854 --Below cursor fetches the further details of the current assignment
3855 CURSOR csr_asg(c_asg_act_id NUMBER, c_effective_date DATE)
3856 IS
3857 SELECT trim(NVL(asg.primary_flag,'N')) asg_primary_flag,
3858 trim(NVL(pap.per_information9,'N')) per_ni_agg_flag,
3859 trim(NVL(pap.per_information10,'N')) per_paye_agg_flag,
3860 asg.payroll_id payroll_id
3861 FROM per_all_people_f pap,
3862 per_all_assignments_f asg,
3863 pay_assignment_actions paa
3864 WHERE paa.assignment_action_id = c_asg_act_id
3865 AND paa.assignment_id = asg.assignment_id
3866 AND pap.person_id = asg.person_id
3867 -- and asg.business_group_id = l_business_group_id
3868 AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
3869 AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
3870 AND pap.per_information_category = 'GB';
3871
3872
3873 --P60 cursors starts here
3874
3875 cursor csr_last_action_p60(p_asgid NUMBER, p_start_year DATE,p_end_year DATE, l_payroll_id number) IS
3876 SELECT
3877 to_number (substr (max (lpad (paa.action_sequence, 15
3878 , '0')
3879 || paa.assignment_action_id), 16))
3880 , max (pact.effective_date) effective_date
3881 FROM pay_assignment_actions paa
3882 , pay_payroll_actions pact
3883 , per_time_periods ptp
3884 WHERE paa.assignment_id = p_asgid
3885 AND paa.payroll_action_id = pact.payroll_action_id
3886 AND pact.payroll_id = l_payroll_id
3887 AND pact.time_period_id = ptp.time_period_id
3888 AND pact.action_type IN ('Q', 'R', 'B'
3889 , 'I', 'V')
3890 AND paa.action_status IN ('C', 'S')
3891 AND ptp.regular_payment_date
3892 BETWEEN p_start_year
3893 AND p_end_year;
3894
3895
3896 CURSOR csr_parameter_info
3897 IS
3898 SELECT
3899 --to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id,
3900 SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
3901 effective_date eff_date,
3902 business_group_id bg_id,
3903 pact.payroll_action_id pactid
3904 FROM pay_payroll_actions pact,
3905 pay_assignment_actions paa
3906 WHERE paa.payroll_action_id = pact.payroll_action_id
3907 AND paa.assignment_action_id = p_assactid;
3908
3909 -- cursor to get assignment id using assignment action id parameter
3910 CURSOR csr_asg_id(c_asg_act_id NUMBER) IS
3911 SELECT paa.assignment_id
3912 FROM pay_assignment_actions paa
3913 WHERE paa.assignment_action_id = c_asg_act_id;
3914
3915 -- Cursor to fetch the RTI NI Reporting assignment for this person.
3916 cursor csr_get_ni_rpt_asg(c_asg_id number) is
3917 select
3918 distinct paei.assignment_id , fnd_date.canonical_to_date(paei.AEI_INFORMATION2)
3919 from per_all_assignments_f paaf1, per_all_assignments_f paaf2 ,per_assignment_extra_info paei
3920 where paaf1.assignment_id = c_asg_id
3921 and paaf1.person_id = paaf2.person_id
3922 and paaf2.assignment_status_type_id in(select ASSIGNMENT_STATUS_TYPE_ID from per_assignment_status_types past where past.LEGISLATION_CODE is null and past.BUSINESS_GROUP_ID is null
3923 and PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN')
3924 and paei.assignment_id = paaf2.assignment_id
3925 and paei.AEI_INFORMATION_CATEGORY = 'GB_RTI_AGGREGATION'
3926 and paei. AEI_INFORMATION1 = 'Y'
3927 order by 2 desc;
3928
3929 CURSOR csr_aggr_max_act_id( p_person_id NUMBER,
3930 p_pre_pact_id NUMBER,
3931 c_effective_date DATE)
3932 IS
3933
3934 SELECT count(distinct act.assignment_action_id),
3935 max(act.assignment_action_id) assignment_action_id
3936 FROM pay_payroll_actions ppa, --Current pactid
3937 pay_payroll_actions appa, --Payroll Run
3938 pay_payroll_actions appa2, --Prepayments
3939 pay_assignment_actions act, --Payroll Run
3940 pay_assignment_actions act1, --Prepayments
3941 pay_action_interlocks pai, --Prepayments
3942 per_all_assignments_f as1
3943 WHERE as1.person_id = p_person_id --45885
3944 AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
3945 AND act.payroll_action_id = appa.payroll_action_id
3946 AND act.source_action_id IS NULL
3947 AND as1.assignment_id = act.assignment_id
3948 AND ppa.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
3949 AND act.action_status = 'C'
3950 AND act.assignment_action_id = pai.locked_action_id
3951 AND act1.assignment_action_id = pai.locking_action_id
3952 AND act1.action_status = 'C'
3953 AND act1.payroll_action_id = appa2.payroll_action_id
3954 AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
3955 AND appa2.payroll_action_id = p_pre_pact_id;
3956
3957 --Below cursor fetches the latest payroll run details for the person
3958 CURSOR csr_aggr_last_payroll_action(c_person_id number, c_start_date date, c_end_date date)
3959 IS
3960 SELECT
3961 /*+ USE_NL(paa, pact, ptp) */
3962 to_number(SUBSTR(MAX(lpad(paa.action_sequence,15,'0')
3963 || paa.assignment_action_id),16)) max_asg_act_id,
3964 MAX(pact.effective_date) effective_date
3965 FROM pay_assignment_actions paa,
3966 per_all_assignments_f paaf,
3967 pay_payroll_actions pact,
3968 per_time_periods ptp
3969 WHERE paa.assignment_id = paaf.assignment_id
3970 AND paaf.person_id = c_person_id --41650
3971 AND paa.payroll_action_id = pact.payroll_action_id
3972 AND pact.time_period_id = ptp.time_period_id
3973 AND pact.action_type IN ('Q','R','B','I','V')
3974 AND paa.action_status IN ('C','S')
3975 AND ptp.regular_payment_date BETWEEN c_start_date AND c_end_date; --'06-APR-2012' AND '05-APR-2013'
3976
3977 --Below cursor fetches the basic details of the current assignment
3978 CURSOR csr_asg_num(c_asg_act_id NUMBER, c_cur_last_effective_date date)
3979 IS
3980 SELECT asg.assignment_number
3981 FROM per_all_assignments_f asg,
3982 pay_assignment_actions paa
3983 WHERE paa.assignment_action_id = c_asg_act_id
3984 AND paa.assignment_id = asg.assignment_id
3985 AND c_cur_last_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
3986
3987 TYPE l_typ_payroll_asg_acts IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3988 l_payroll_asg_acts l_typ_payroll_asg_acts;
3989 TYPE l_typ_proc_in_prepay IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3990 l_proc_in_prepay l_typ_proc_in_prepay;
3991 TYPE l_typ_not_proc_in_prepay IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3992 l_not_proc_in_prepay l_typ_not_proc_in_prepay;
3993
3994 l_asg_primary_flag VARCHAR2(1);
3995 l_per_agg_flag VARCHAR2(1);
3996 l_aggr_archive_flag VARCHAR2(1);
3997 l_asg_id NUMBER;
3998 l_effective_start_date DATE;
3999 l_effective_end_date DATE;
4000 l_last_asg_action_id NUMBER;
4001 l_last_effective_date DATE;
4002 l_asg_eff_start_date DATE;
4003 l_payroll_id NUMBER;
4004 l_person_id NUMBER;
4005 l_payment_date DATE;
4006
4007 p_ni_a_balance act_info_rec;
4008 p_ni_b_balance act_info_rec;
4009 p_ni_c_balance act_info_rec;
4010 p_ni_d_balance act_info_rec;
4011 p_ni_e_balance act_info_rec;
4012 p_ni_j_balance act_info_rec;
4013 p_ni_l_balance act_info_rec;
4014
4015 already_a NUMBER := -1;
4016 already_b NUMBER := -1;
4017 already_c NUMBER := -1;
4018 already_d NUMBER := -1;
4019 already_e NUMBER := -1;
4020 already_j NUMBER := -1;
4021 already_l NUMBER := -1;
4022 i NUMBER;
4023
4024 l_assignment_id number;
4025 l_tax_ref varchar2(30);
4026 l_effective_date date;
4027 l_date_flag boolean;
4028 l_assignment_number per_assignments_f.assignment_number%TYPE;
4029 l_asg_primary_flag1 VARCHAR2(1);
4030 l_per_ni_agg_flag1 VARCHAR2(1);
4031 l_per_paye_agg_flag1 VARCHAR2(1);
4032 l_payroll_id1 NUMBER;
4033 l_business_group_id number;
4034 l_archive_p60_payroll boolean;
4035 l_archive_p60_emp boolean;
4036 l_archive_p60_asg boolean;
4037 l_archive_p60_ni boolean;
4038 l_pact_id number;
4039 l_ni_counter number;
4040 l_paye_aggr_asgactid number;
4041 l_paye_aggr_asgid number;
4042 l_aggr_last_asgact_id number;
4043 l_aggr_last_eff_date date;
4044
4045 BEGIN
4046
4047 --archive_code begins here
4048 hr_utility.set_location('Entering the debug: '||l_proc,1);
4049 hr_utility.trace('p_assactid: '||p_assactid);
4050 hr_utility.trace('p_effective_date: '||p_effective_date);
4051 i := 1;
4052 -- get the parameter values present in globals to local variables.
4053 l_tax_ref := g_tax_ref;
4054
4055 hr_utility.trace('l_tax_ref: '||l_tax_ref);
4056
4057 IF p_effective_date >= to_date('06-04-'||SUBSTR(TO_CHAR(p_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY' ) THEN
4058 g_start_year := to_date('06-04-'||SUBSTR(TO_CHAR(p_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY' ) ;
4059 g_end_year := to_date('05-04-'||TO_CHAR(to_number(SUBSTR(TO_CHAR(p_effective_date,'YYYY/MON/DD'),1,4))+1 ),'DD-MM-YYYY') ;
4060 ELSE
4061 g_start_year := to_date('06-04-'||TO_CHAR(to_number(SUBSTR(TO_CHAR(p_effective_date,'YYYY/MON/DD'),1,4))-1 ),'DD-MM-YYYY') ;
4062 g_end_year := to_date('05-04-'||SUBSTR(TO_CHAR(p_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY') ;
4063 END IF;
4064
4065 OPEN csr_parameter_info;
4066 FETCH csr_parameter_info
4067 INTO
4068 --l_payroll_id,
4069 l_tax_ref,
4070 l_effective_date,
4071 l_business_group_id,
4072 l_pact_id;
4073 CLOSE csr_parameter_info;
4074
4075 OPEN csr_asg_basic_details(p_assactid);
4076 FETCH csr_asg_basic_details INTO l_asg_id, l_asg_eff_start_date, l_person_id, l_assignment_number, l_payroll_id;
4077 CLOSE csr_asg_basic_details;
4078
4079 hr_utility.trace('l_asg_id: '||l_asg_id);
4080 hr_utility.trace('l_asg_eff_start_date: '||l_asg_eff_start_date);
4081 hr_utility.trace('l_person_id: '||l_person_id);
4082 hr_utility.trace('l_assignment_number: '||l_assignment_number);
4083 hr_utility.trace('l_payroll_id: '||l_payroll_id);
4084
4085 OPEN csr_asg(p_assactid, p_effective_date);
4086 FETCH csr_asg INTO l_asg_primary_flag1, l_per_ni_agg_flag1, l_per_paye_agg_flag1, l_payroll_id1;
4087 CLOSE csr_asg;
4088 hr_utility.trace('l_per_ni_agg_flag1: '|| l_per_ni_agg_flag1);
4089 hr_utility.trace('l_per_paye_agg_flag1: '|| l_per_paye_agg_flag1);
4090
4091
4092 -- To get Assignment ID
4093 open csr_asg_id(p_assactid);
4094 fetch csr_asg_id into l_asg_id;
4095 close csr_asg_id;
4096
4097 hr_utility.trace('l_asg_id: '|| l_asg_id);
4098 hr_utility.trace('g_start_year: '|| g_start_year);
4099 hr_utility.trace('g_end_year: '|| g_end_year);
4100 hr_utility.trace('l_payroll_id: '|| l_payroll_id);
4101
4102 -- To get latest assignment action id for the assignment
4103 OPEN csr_last_action_p60(l_asg_id, g_start_year, g_end_year, l_payroll_id);
4104 FETCH csr_last_action_p60 INTO l_last_asg_action_id,
4105 l_last_effective_date;
4106 CLOSE csr_last_action_p60;
4107
4108 hr_utility.trace('l_last_asg_action_id: '|| l_last_asg_action_id);
4109 hr_utility.trace('l_last_effective_date: '|| l_last_effective_date);
4110
4111 hr_utility.set_location('Fetching Payroll Details ',10);
4112 l_archive_p60_emp := fetch_p60_emp_det(p_assactid,
4113 l_pact_id,
4114 l_person_id,
4115 l_asg_id,
4116 g_start_year,
4117 g_end_year,
4118 l_archive_tab(0));
4119
4120 hr_utility.set_location('Fetching address details ',20);
4121
4122 l_archive_p60_asg := fetch_p60_asg_det(p_assactid,
4123 l_pact_id,
4124 l_last_asg_action_id,
4125 l_person_id,
4126 l_asg_id,
4127 g_start_year,
4128 g_end_year,
4129 l_paye_aggr_asgactid,
4130 l_paye_aggr_asgid,
4131 l_archive_tab(1));
4132
4133 hr_utility.set_location('l_paye_aggr_asgactid: ' || l_paye_aggr_asgactid, 50);
4134 hr_utility.set_location('l_paye_aggr_asgid: ' || l_paye_aggr_asgid, 50);
4135
4136 if (l_per_ni_agg_flag1 = 'Y') then
4137 open csr_get_ni_rpt_asg(l_asg_id);
4138 fetch csr_get_ni_rpt_asg into l_ni_rpt_asg_id,l_ni_rpt_date;
4139 close csr_get_ni_rpt_asg;
4140 hr_utility.set_location('l_ni_rpt_asg_id: ' || l_ni_rpt_asg_id, 50);
4141 hr_utility.set_location('l_ni_rpt_date: ' || l_ni_rpt_date, 50);
4142
4143 if l_ni_rpt_asg_id is null then
4144 hr_utility.set_location('NO RTI Reporting asg for :'|| l_asg_id, 50);
4145 fnd_file.put_line (fnd_file.LOG,'NO RTI Reporting asg for :'|| l_asg_id);
4146 end if;
4147 end if;
4148
4149 if (l_per_ni_agg_flag1 = 'Y' and l_ni_rpt_asg_id = l_asg_id) then
4150
4151 hr_utility.set_location('Inside NI Only Aggregation for NI Balances for: '||l_aggr_last_asgact_id,60);
4152
4153 open csr_aggr_last_payroll_action(l_person_id,g_start_year,g_end_year);
4154 fetch csr_aggr_last_payroll_action into l_aggr_last_asgact_id, l_aggr_last_eff_date;
4155 close csr_aggr_last_payroll_action;
4156
4157 get_ni_aggr_ni_balances(l_aggr_last_asgact_id,
4158 p_ni_a_balance,
4159 p_ni_b_balance,
4160 p_ni_c_balance,
4161 p_ni_d_balance,
4162 p_ni_e_balance,
4163 p_ni_j_balance,
4164 p_ni_l_balance);
4165
4166 elsif (l_per_paye_agg_flag1 = 'Y' and l_asg_id = nvl(l_paye_aggr_asgid,-999)) then
4167 hr_utility.set_location('Inside PAYE Aggregation for NI Balances for: '||l_paye_aggr_asgactid, 60);
4168 get_paye_aggr_ni_balances(l_asg_id,
4169 l_paye_aggr_asgactid,
4170 p_ni_a_balance,
4171 p_ni_b_balance,
4172 p_ni_c_balance,
4173 p_ni_d_balance,
4174 p_ni_e_balance,
4175 p_ni_j_balance,
4176 p_ni_l_balance);
4177 hr_utility.trace('l_asg_id: '|| l_asg_id);
4178 hr_utility.trace('l_paye_aggr_asgactid: '|| l_paye_aggr_asgactid);
4179 else
4180 hr_utility.set_location('Inside Non-Aggregation for NI Balances for: '||l_last_asg_action_id, 60);
4181 get_ni_balances_for_asg(l_last_asg_action_id,
4182 p_ni_a_balance,
4183 p_ni_b_balance,
4184 p_ni_c_balance,
4185 p_ni_d_balance,
4186 p_ni_e_balance,
4187 p_ni_j_balance,
4188 p_ni_l_balance);
4189 hr_utility.trace('l_last_asg_action_id: '|| l_last_asg_action_id);
4190 end if;
4191
4192 if (l_per_ni_agg_flag1 = 'Y' and l_ni_rpt_asg_id <> l_asg_id) then
4193 hr_utility.trace('Archiving X category for NI Only Aggregation scenarios: '|| l_asg_id);
4194
4195 l_archive_tab(2).assignment_id := l_archive_tab(1).assignment_id;
4196 l_archive_tab(2).action_info_category := 'GB RTI P60 NI DET';
4197 l_archive_tab(2).act_info1 := 'X';
4198 l_archive_tab(2).act_info2 := 0;
4199 l_archive_tab(2).act_info3 := 0;
4200 l_archive_tab(2).act_info4 := 0;
4201 l_archive_tab(2).act_info5 := 0;
4202 l_archive_tab(2).act_info6 := 0;
4203 l_archive_tab(2).act_info7 := 'NIC1';
4204 else
4205 l_ni_counter := 1;
4206 IF (p_ni_a_balance.act_info1 IS NOT NULL) THEN
4207 IF already_a = -1 THEN
4208 i := i+1;
4209 already_a := i;
4210 END IF;
4211
4212 l_archive_tab(already_a).assignment_id := l_archive_tab(1).assignment_id;
4213 l_archive_tab(already_a).action_info_category := 'GB RTI P60 NI DET';
4214 l_archive_tab(already_a).act_info1 := p_ni_a_balance.act_info1;
4215 l_archive_tab(already_a).act_info2 := p_ni_a_balance.act_info3;
4216 l_archive_tab(already_a).act_info3 := p_ni_a_balance.act_info4;
4217 l_archive_tab(already_a).act_info4 := p_ni_a_balance.act_info5;
4218 l_archive_tab(already_a).act_info5 := p_ni_a_balance.act_info6;
4219 l_archive_tab(already_a).act_info6 := p_ni_a_balance.act_info8;
4220
4221 hr_utility.set_location('l_ni_counter: '||l_ni_counter,10);
4222
4223 if (l_ni_counter = 1) then
4224 l_archive_tab(already_a).act_info7 := 'NIC1';
4225 elsif (l_ni_counter = 2) then
4226 l_archive_tab(already_a).act_info7 := 'NIC2';
4227 elsif (l_ni_counter = 3) then
4228 l_archive_tab(already_a).act_info7 := 'NIC3';
4229 elsif (l_ni_counter = 4) then
4230 l_archive_tab(already_a).act_info7 := 'NIC4';
4231 end if;
4232 l_ni_counter := l_ni_counter + 1;
4233
4234 END IF;
4235
4236 -- For Category B
4237 IF (p_ni_b_balance.act_info1 IS NOT NULL) THEN
4238 IF already_b = -1 THEN
4239 i := i+1;
4240 already_b := i;
4241 END IF;
4242 l_archive_tab(already_b).assignment_id := l_archive_tab(1).assignment_id;
4243 l_archive_tab(already_b).action_info_category := 'GB RTI P60 NI DET';
4244 l_archive_tab(already_b).act_info1 := p_ni_b_balance.act_info1;
4245 l_archive_tab(already_b).act_info2 := p_ni_b_balance.act_info3;
4246 l_archive_tab(already_b).act_info3 := p_ni_b_balance.act_info4;
4247 l_archive_tab(already_b).act_info4 := p_ni_b_balance.act_info5;
4248 l_archive_tab(already_b).act_info5 := p_ni_b_balance.act_info6;
4249 l_archive_tab(already_b).act_info6 := p_ni_b_balance.act_info8;
4250
4251 hr_utility.set_location('l_ni_counter: '||l_ni_counter,10);
4252
4253 if (l_ni_counter = 1) then
4254 l_archive_tab(already_b).act_info7 := 'NIC1';
4255 elsif (l_ni_counter = 2) then
4256 l_archive_tab(already_b).act_info7 := 'NIC2';
4257 elsif (l_ni_counter = 3) then
4258 l_archive_tab(already_b).act_info7 := 'NIC3';
4259 elsif (l_ni_counter = 4) then
4260 l_archive_tab(already_b).act_info7 := 'NIC4';
4261 end if;
4262 l_ni_counter := l_ni_counter + 1;
4263
4264
4265 END IF;
4266
4267 -- For Category C
4268 IF (p_ni_c_balance.act_info1 IS NOT NULL) THEN
4269 IF already_c = -1 THEN
4270 i := i+1;
4271 already_c := i;
4272 END IF;
4273
4274 l_archive_tab(already_c).assignment_id := l_archive_tab(1).assignment_id;
4275 l_archive_tab(already_c).action_info_category := 'GB RTI P60 NI DET';
4276 l_archive_tab(already_c).act_info1 := p_ni_c_balance.act_info1;
4277 l_archive_tab(already_c).act_info2 := p_ni_c_balance.act_info3;
4278 l_archive_tab(already_c).act_info3 := p_ni_c_balance.act_info4;
4279 l_archive_tab(already_c).act_info4 := p_ni_c_balance.act_info5;
4280 l_archive_tab(already_c).act_info5 := p_ni_c_balance.act_info6;
4281 l_archive_tab(already_c).act_info6 := p_ni_c_balance.act_info8;
4282
4283 hr_utility.set_location('l_ni_counter: '||l_ni_counter,10);
4284
4285 if (l_ni_counter = 1) then
4286 l_archive_tab(already_c).act_info7 := 'NIC1';
4287 elsif (l_ni_counter = 2) then
4288 l_archive_tab(already_c).act_info7 := 'NIC2';
4289 elsif (l_ni_counter = 3) then
4290 l_archive_tab(already_c).act_info7 := 'NIC3';
4291 elsif (l_ni_counter = 4) then
4292 l_archive_tab(already_c).act_info7 := 'NIC4';
4293 end if;
4294 l_ni_counter := l_ni_counter + 1;
4295
4296 END IF;
4297
4298
4299 --For Category D
4300 IF (p_ni_d_balance.act_info1 IS NOT NULL) THEN
4301 IF already_d = -1 THEN
4302 i := i+1;
4303 already_d := i;
4304 END IF;
4305 l_archive_tab(already_d).assignment_id := l_archive_tab(1).assignment_id;
4306 l_archive_tab(already_d).action_info_category := 'GB RTI P60 NI DET';
4307 l_archive_tab(already_d).act_info1 := p_ni_d_balance.act_info1;
4308 l_archive_tab(already_d).act_info2 := p_ni_d_balance.act_info3;
4309 l_archive_tab(already_d).act_info3 := p_ni_d_balance.act_info4;
4310 l_archive_tab(already_d).act_info4 := p_ni_d_balance.act_info5;
4311 l_archive_tab(already_d).act_info5 := p_ni_d_balance.act_info6;
4312 l_archive_tab(already_d).act_info6 := p_ni_d_balance.act_info8;
4313
4314 hr_utility.set_location('l_ni_counter: '||l_ni_counter,10);
4315
4316 if (l_ni_counter = 1) then
4317 l_archive_tab(already_d).act_info7 := 'NIC1';
4318 elsif (l_ni_counter = 2) then
4319 l_archive_tab(already_d).act_info7 := 'NIC2';
4320 elsif (l_ni_counter = 3) then
4321 l_archive_tab(already_d).act_info7 := 'NIC3';
4322 elsif (l_ni_counter = 4) then
4323 l_archive_tab(already_d).act_info7 := 'NIC4';
4324 end if;
4325 l_ni_counter := l_ni_counter + 1;
4326
4327 END IF;
4328
4329 -- For Category E
4330 IF (p_ni_e_balance.act_info1 IS NOT NULL) THEN
4331 IF already_e = -1 THEN
4332 i := i+1;
4333 already_e := i;
4334 END IF;
4335
4336 l_archive_tab(already_e).assignment_id := l_archive_tab(1).assignment_id;
4337 l_archive_tab(already_e).action_info_category := 'GB RTI P60 NI DET';
4338 l_archive_tab(already_e).act_info1 := p_ni_e_balance.act_info1;
4339 l_archive_tab(already_e).act_info2 := p_ni_e_balance.act_info3;
4340 l_archive_tab(already_e).act_info3 := p_ni_e_balance.act_info4;
4341 l_archive_tab(already_e).act_info4 := p_ni_e_balance.act_info5;
4342 l_archive_tab(already_e).act_info5 := p_ni_e_balance.act_info6;
4343 l_archive_tab(already_e).act_info6 := p_ni_e_balance.act_info8;
4344
4345 hr_utility.set_location('l_ni_counter: '||l_ni_counter,10);
4346
4347 if (l_ni_counter = 1) then
4348 l_archive_tab(already_e).act_info7 := 'NIC1';
4349 elsif (l_ni_counter = 2) then
4350 l_archive_tab(already_e).act_info7 := 'NIC2';
4351 elsif (l_ni_counter = 3) then
4352 l_archive_tab(already_e).act_info7 := 'NIC3';
4353 elsif (l_ni_counter = 4) then
4354 l_archive_tab(already_e).act_info7 := 'NIC4';
4355 end if;
4356 l_ni_counter := l_ni_counter + 1;
4357
4358 END IF;
4359
4360 -- For Category J
4361 IF (p_ni_j_balance.act_info1 IS NOT NULL) THEN
4362 IF already_j = -1 THEN
4363 i := i+1;
4364 already_j := i;
4365 END IF;
4366
4367 l_archive_tab(already_j).assignment_id := l_archive_tab(1).assignment_id;
4368 l_archive_tab(already_j).action_info_category := 'GB RTI P60 NI DET';
4369 l_archive_tab(already_j).act_info1 := p_ni_j_balance.act_info1;
4370 l_archive_tab(already_j).act_info2 := p_ni_j_balance.act_info3;
4371 l_archive_tab(already_j).act_info3 := p_ni_j_balance.act_info4;
4372 l_archive_tab(already_j).act_info4 := p_ni_j_balance.act_info5;
4373 l_archive_tab(already_j).act_info5 := p_ni_j_balance.act_info6;
4374 l_archive_tab(already_j).act_info6 := p_ni_j_balance.act_info8;
4375
4376 hr_utility.set_location('l_ni_counter: '||l_ni_counter,10);
4377
4378 if (l_ni_counter = 1) then
4379 l_archive_tab(already_j).act_info7 := 'NIC1';
4380 elsif (l_ni_counter = 2) then
4381 l_archive_tab(already_j).act_info7 := 'NIC2';
4382 elsif (l_ni_counter = 3) then
4383 l_archive_tab(already_j).act_info7 := 'NIC3';
4384 elsif (l_ni_counter = 4) then
4385 l_archive_tab(already_j).act_info7 := 'NIC4';
4386 end if;
4387 l_ni_counter := l_ni_counter + 1;
4388
4389 END IF;
4390
4391 -- For Category L
4392 IF (p_ni_l_balance.act_info1 IS NOT NULL) THEN
4393 IF already_l = -1 THEN
4394 i := i+1;
4395 already_l := i;
4396 END IF;
4397
4398 l_archive_tab(already_l).assignment_id := l_archive_tab(1).assignment_id;
4399 l_archive_tab(already_l).action_info_category := 'GB RTI P60 NI DET';
4400 l_archive_tab(already_l).act_info1 := p_ni_l_balance.act_info1;
4401 l_archive_tab(already_l).act_info2 := p_ni_l_balance.act_info3;
4402 l_archive_tab(already_l).act_info3 := p_ni_l_balance.act_info4;
4403 l_archive_tab(already_l).act_info4 := p_ni_l_balance.act_info5;
4404 l_archive_tab(already_l).act_info5 := p_ni_l_balance.act_info6;
4405 l_archive_tab(already_l).act_info6 := p_ni_l_balance.act_info8;
4406
4407 hr_utility.set_location('l_ni_counter: '||l_ni_counter,10);
4408
4409 if (l_ni_counter = 1) then
4410 l_archive_tab(already_l).act_info7 := 'NIC1';
4411 elsif (l_ni_counter = 2) then
4412 l_archive_tab(already_l).act_info7 := 'NIC2';
4413 elsif (l_ni_counter = 3) then
4414 l_archive_tab(already_l).act_info7 := 'NIC3';
4415 elsif (l_ni_counter = 4) then
4416 l_archive_tab(already_l).act_info7 := 'NIC4';
4417 end if;
4418 l_ni_counter := l_ni_counter + 1;
4419
4420 END IF;
4421
4422 end if; --if (l_per_ni_agg_flag1 = 'Y' and l_ni_rpt_asg_id <> l_asg_id) then
4423
4424 hr_utility.trace('already_a: '||already_a);
4425 hr_utility.trace('already_b: '||already_b);
4426 hr_utility.trace('already_c: '||already_c);
4427 hr_utility.trace('already_d: '||already_d);
4428 hr_utility.trace('already_e: '||already_e);
4429 hr_utility.trace('already_j: '||already_j);
4430 hr_utility.trace('already_l: '||already_l);
4431 hr_utility.trace('***i: '||i);
4432
4433 IF l_archive_p60_emp AND l_archive_p60_asg THEN
4434 hr_utility.set_location('Validation successful, archive data.',999);
4435 insert_archive_row(p_assactid, p_effective_date,l_archive_tab);
4436 ELSE
4437 hr_utility.set_location('Archiving Error - assignment action id ' || p_assactid ,999);
4438 raise error_found;
4439 END IF;
4440
4441 hr_utility.set_location('Leaving: '||l_proc,999);
4442 EXCEPTION
4443 WHEN error_found THEN
4444 -- reset_flag('GB_RTI_ASG_DETAILS',g_reset_flag_type, p_assactid);
4445 fnd_file.put_line(fnd_file.LOG,'Errors found while archiving data.');
4446 raise_application_error(-20001,'Errors found while archiving data.');
4447
4448 WHEN already_processed THEN
4449 fnd_file.put_line (fnd_file.LOG,'Current Assignment '|| l_assignment_number ||' is already processed in an earlier FPS run. Hence, not processing now.');
4450
4451 WHEN OTHERS THEN
4452 fnd_file.put_line(fnd_file.LOG,'An error encountered was - '||SQLERRM);
4453 raise_application_error(-20001,'An error encountered was - '||SQLERRM);
4454 END archive_code;
4455 --
4456 --
4457 PROCEDURE deinitialization_code
4458 (
4459 pactid IN NUMBER
4460 )
4461 IS
4462 l_proc CONSTANT VARCHAR2(50) := g_package || 'deinitialization_code';
4463 l_counter NUMBER;
4464 l_flag VARCHAR2(1);
4465 l_exp EXCEPTION;
4466 errbuf varchar2(100);
4467 retcode number;
4468 l_econ varchar2(100);
4469 l_person_count number :=0;
4470 l_error_flag VARCHAR2(1);
4471 CURSOR csr_is_etext_report
4472 IS
4473 SELECT report_type
4474 FROM pay_payroll_actions pact
4475 WHERE pact.payroll_action_id = pactid;
4476 l_is_etext_report VARCHAR2(50);
4477 l_request_id fnd_concurrent_requests.request_id%TYPE;
4478 xml_layout BOOLEAN;
4479
4480 -- Cursor to fetch Employer details
4481 CURSOR csr_payroll_details(pactid NUMBER)
4482 IS
4483 SELECT NVL(UPPER(hoi.org_information11),' ') sender_id,
4484 DECODE(SUBSTR(pact.legislative_parameters,instr(pact.legislative_parameters,'TEST=') + 5,1) ,'N',' ','Y','1') test_indicator,
4485 fnd_number.number_to_canonical(pact.request_id) request_id,
4486 NVL(upper(SUBSTR(ltrim(hoi.org_information3),1,35)),' ') employer_name,
4487 NVL(upper(SUBSTR(ltrim(SUBSTR(hoi.org_information1,4,11),'/'),1,10)),' ') tax_ref_no,
4488 lpad(SUBSTR(hoi.ORG_INFORMATION1,0,instr(hoi.ORG_INFORMATION1,'/')-1),3,0) tax_office_no,
4489 DECODE(PAY_GB_EOY_MAGTAPE.get_payroll_version, ' ', '0', PAY_GB_EOY_MAGTAPE.get_payroll_version) payroll_ver,
4490 (lpad(SUBSTR(hoi.ORG_INFORMATION6,0,instr(hoi.ORG_INFORMATION6,'P')-1),3,0)
4491 || 'P'
4492 || SUBSTR(hoi.ORG_INFORMATION6,instr(hoi.ORG_INFORMATION6,'P') +1,1)
4493 || lpad(SUBSTR(hoi.ORG_INFORMATION6,instr(hoi.ORG_INFORMATION6,'P')+2,LENGTH(hoi.ORG_INFORMATION6)-3-(instr(hoi.ORG_INFORMATION6,'P')-1)),7,0)
4494 || SUBSTR(hoi.ORG_INFORMATION6,LENGTH(hoi.ORG_INFORMATION6),1) ) AS acc_ref_no,
4495 hoi.org_information7 econ_number,
4496 pact.business_group_id bus_grp_id,
4497 pact.action_parameter_group_id act_param_grp_id,
4498 org_information19 service_company
4499 FROM pay_payroll_actions pact,
4500 hr_organization_information hoi
4501 WHERE pact.payroll_action_id =pactid
4502 AND pact.business_group_id = hoi.organization_id
4503 AND hoi.org_information_context = 'Tax Details References'
4504 AND NVL(hoi.org_information10,'UK') = 'UK'
4505 AND SUBSTR(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8, instr(pact.legislative_parameters
4506 ||' ',' ', instr(pact.legislative_parameters,'TAX_REF=') +8) - instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
4507
4508
4509 l_payroll_rec csr_payroll_details%rowtype;
4510 l_ovn NUMBER;
4511 l_archive_p60_payroll boolean;
4512 ---------------------
4513 ---------------------
4514
4515 PROCEDURE write_header(
4516 report_type VARCHAR2)
4517 IS
4518 l_token VARCHAR2(255);
4519 l_addr1 VARCHAR2(255);
4520 l_addr2 VARCHAR2(255);
4521 l_addr3 VARCHAR2(255);
4522 l_addr4 VARCHAR2(255);
4523 l_form VARCHAR2(40);
4524 l_tax_ref VARCHAR2(20);
4525 l_urgent VARCHAR2(2);
4526 l_test VARCHAR2(2);
4527 l_temp NUMBER;
4528 l_form_name VARCHAR2(100);
4529 l_payroll_name VARCHAR2(100);
4530 l_prepay_eff_date date;
4531
4532 CURSOR csr_leg_param
4533 IS
4534 SELECT legislative_parameters para,
4535 fnd_number.number_to_canonical(request_id) control_id,
4536 report_type,
4537 business_group_id
4538 FROM pay_payroll_actions pact
4539 WHERE payroll_action_id = pactid;
4540
4541 CURSOR csr_header_det(p_bus_id NUMBER, p_tax_ref VARCHAR2)
4542 IS
4543 SELECT NVL(hoi.org_information11,' ') sender_id,
4544 NVL(upper(hoi.org_information2),' ') hrmc_office,
4545 NVL(upper(hoi.org_information4),' ') er_addr,
4546 NVL(upper(hoi.org_information3),' ') er_name
4547 FROM hr_organization_information hoi
4548 WHERE hoi.organization_id = p_bus_id
4549 AND hoi.org_information_context = 'Tax Details References'
4550 AND NVL(hoi.org_information10,'UK') = 'UK'
4551 AND upper(hoi.org_information1) = upper(p_tax_ref);
4552
4553 CURSOR csr_payroll_details
4554 IS
4555 SELECT ppf.PAYROLL_NAME , ppapre.EFFECTIVE_DATE
4556 from pay_payrolls_f ppf,pay_payroll_actions ppapre,pay_payroll_actions ppa
4557 WHERE ppa.payroll_action_id = pactid
4558 and ppf.payroll_id = SUBSTR(pay_gb_eoy_archive.get_parameter(ppa.legislative_parameters,'PAYROLL_ID'),1,20)
4559 and ppapre.payroll_action_id = SUBSTR(pay_gb_eoy_archive.get_parameter(ppa.legislative_parameters,'PRE_PAY_ID'),1,20);
4560
4561 -- Cursor to fetch Errored Records
4562 CURSOR get_asg_action_id_error
4563 IS
4564 SELECT DISTINCT paa.assignment_action_id asg_action_id,
4565 assignment_id
4566 FROM pay_payroll_actions ppa,
4567 pay_assignment_actions paa
4568 WHERE ppa.payroll_action_id = pactid -- pact_id
4569 AND paa.payroll_action_id = ppa.payroll_action_id
4570 AND paa.action_status = 'E';
4571
4572 l_param csr_leg_param%rowtype;
4573 l_det csr_header_det%rowtype;
4574 l_start_date DATE;
4575 l_end_date DATE;
4576 l_err_msg varchar2(100);
4577 BEGIN
4578 OPEN csr_leg_param;
4579 FETCH csr_leg_param INTO l_param;
4580 CLOSE csr_leg_param;
4581
4582 open csr_payroll_details;
4583 fetch csr_payroll_details into l_payroll_name,l_prepay_eff_date;
4584 close csr_payroll_details;
4585
4586 l_token := 'TAX_REF';
4587 l_temp := instr(l_param.para,l_token);
4588 l_tax_ref := SUBSTR(l_param.para, l_temp + LENGTH(l_token) + 1, instr(l_param.para||' ',' ',l_temp)
4589 - (l_temp + LENGTH(l_token) + 1));
4590 l_token := 'TEST';
4591 l_temp := instr(l_param.para,l_token);
4592 l_test := SUBSTR(l_param.para, l_temp + LENGTH(l_token) + 1, instr(l_param.para||' ',' ',l_temp)
4593 - (l_temp + LENGTH(l_token) + 1));
4594
4595 OPEN csr_header_det(l_param.business_group_id, l_tax_ref);
4596 FETCH csr_header_det INTO l_det;
4597 CLOSE csr_header_det;
4598
4599 l_addr1 := l_det.er_addr;
4600 IF LENGTH(l_addr1) > 35 THEN
4601 l_temp := instr(l_addr1, ',', 34 - LENGTH(l_addr1));
4602 IF l_temp = 0 THEN
4603 l_temp := 35;
4604 END IF;
4605 l_addr2 := ltrim(SUBSTR(l_addr1, 1 + l_temp),' ,');
4606 l_addr1 := SUBSTR(l_addr1,1,l_temp);
4607 END IF;
4608 IF LENGTH(l_addr2) > 35 THEN
4609 l_temp := instr(l_addr2, ',', 34 - LENGTH(l_addr2));
4610 IF l_temp = 0 THEN
4611 l_temp := 35;
4612 END IF;
4613 l_addr3 := ltrim(SUBSTR(l_addr2, 1 + l_temp),' ,');
4614 l_addr2 := SUBSTR(l_addr2,1,l_temp);
4615 END IF;
4616 IF LENGTH(l_addr3) > 35 THEN
4617 l_temp := instr(l_addr3, ',', 34 - LENGTH(l_addr3));
4618 IF l_temp = 0 THEN
4619 l_temp := 35;
4620 END IF;
4621 l_addr3 := ltrim(SUBSTR(l_addr3, 1 + l_temp),' ,');
4622 l_addr4 := SUBSTR(l_addr3,1,l_temp);
4623 END IF;
4624
4625 l_form_name :='RTI - P60 Archive Process';
4626
4627 fnd_file.put_line(fnd_file.output,' ');
4628 fnd_file.put_line(fnd_file.output,rpad('Form Type : ',32) || l_form_name);
4629 fnd_file.put_line(fnd_file.output,rpad('Date : ',32) || TO_CHAR(sysdate, 'DD/MM/YYYY HH24:MI:SS'));
4630 fnd_file.put_line(fnd_file.output,rpad('Interchange Control Reference : ',32) || l_param.control_id);
4631 fnd_file.put_line(fnd_file.output,rpad('-',80,'-'));
4632 fnd_file.put_line(fnd_file.output,rpad('Employers PAYE Reference : ',32) || l_tax_ref);
4633 fnd_file.put_line(fnd_file.output,rpad('Employer Name : ',32) || l_det.er_name);
4634 fnd_file.put_line(fnd_file.output,rpad('Employer Address : ',32) || l_addr1);
4635
4636 IF LENGTH(l_addr2) > 0 THEN
4637 fnd_file.put_line(fnd_file.output,rpad(' ',32) || l_addr2);
4638 END IF;
4639 IF LENGTH(l_addr3) > 0 THEN
4640 fnd_file.put_line(fnd_file.output,rpad(' ',32) || l_addr3);
4641 END IF;
4642 IF LENGTH(l_addr4) > 0 THEN
4643 fnd_file.put_line(fnd_file.output,rpad(' ',32) || l_addr4);
4644 END IF;
4645
4646 END write_header;
4647 ---
4648 PROCEDURE write_sub_header(
4649 p_type VARCHAR2,
4650 report_type VARCHAR2)
4651 IS
4652 BEGIN
4653 fnd_file.put_line(fnd_file.output,NULL);
4654 IF p_type = 'E' THEN
4655 fnd_file.put_line(fnd_file.output,'The following employments have completed with error');
4656 ELSIF p_type = 'W' THEN
4657 fnd_file.put_line(fnd_file.output,'The following employments have completed with warning');
4658 ELSE
4659 fnd_file.put_line(fnd_file.output,'The following employments have completed successfully');
4660 END IF;
4661
4662 IF p_type = 'W' THEN -- warning records
4663 fnd_file.put_line(fnd_file.output,rpad('Assignment Number',19) || rpad('NI Number',11) || rpad('Employee Name', 51)
4664 || rpad('Warning Messages',30));
4665 ELSIF p_type <> 'E' THEN --Completed records
4666 fnd_file.put_line(fnd_file.output,rpad('Assignment Number',19) || rpad('NI Number',11) || rpad('Employee Name', 51));
4667 ELSE -- errored records
4668 fnd_file.put_line(fnd_file.output,rpad('Assignment Number',19) || rpad('NI Number',11) || rpad('Employee Name', 51)
4669 || rpad('Error Messages',30));
4670 END IF;
4671
4672 IF p_type = 'W' THEN -- warning records
4673 fnd_file.put_line(fnd_file.output,rpad('-',18,'-') || ' ' || rpad('-',10,'-') || ' ' || rpad('-',50,'-') || ' '
4674 || rpad('-',30,'-'));
4675 ELSIF p_type <> 'E' THEN -- Completed records
4676 fnd_file.put_line(fnd_file.output,rpad('-',18,'-') || ' ' || rpad('-',10,'-') || ' ' || rpad('-',50,'-'));
4677 ELSE -- errored records
4678 fnd_file.put_line(fnd_file.output,rpad('-',18,'-') || ' ' || rpad('-',10,'-') || ' ' || rpad('-',50,'-') || ' '
4679 || rpad('-',30,'-'));
4680 END IF;
4681 END write_sub_header;
4682 ---
4683 PROCEDURE write_body(
4684 p_type VARCHAR2,
4685 pactid IN NUMBER,
4686 report_type VARCHAR2)
4687 IS
4688 l_count NUMBER;
4689 i NUMBER;
4690 l_temp VARCHAR2(255);
4691 l_emp_count NUMBER;
4692 l_number VARCHAR2(30);
4693 l_start_date DATE;
4694 l_end_date DATE;
4695 l_count_warns number;
4696 l_effective_date date;
4697
4698 -- Cursor to fetch Completed records
4699 CURSOR get_asg_action_id
4700 IS
4701 SELECT DISTINCT paa.assignment_action_id asg_action_id,
4702 assignment_id
4703 FROM pay_payroll_actions ppa,
4704 pay_assignment_actions paa
4705 WHERE ppa.payroll_action_id = pactid -- pact_id
4706 AND paa.payroll_action_id = ppa.payroll_action_id
4707 AND paa.action_status = 'C';
4708
4709 -- Cursor to fetch Errored Records
4710 CURSOR get_asg_action_id_error
4711 IS
4712 SELECT DISTINCT paa.assignment_action_id asg_action_id,
4713 assignment_id
4714 FROM pay_payroll_actions ppa,
4715 pay_assignment_actions paa
4716 WHERE ppa.payroll_action_id = pactid -- pact_id
4717 AND paa.payroll_action_id = ppa.payroll_action_id
4718 AND paa.action_status = 'E';
4719
4720 CURSOR get_person_details(c_assignment_id NUMBER)
4721 IS
4722 SELECT DISTINCT pap.first_name f_name ,
4723 pap.middle_names m_name,
4724 pap.last_name l_name,
4725 pap.title title,
4726 paa.assignment_number emp_no,
4727 NVL(pap.national_identifier,' ')ni_no,
4728 NVL(pap.employee_number,' ') employee_number,
4729 TO_CHAR(paa.EFFECTIVE_START_DATE,'DD-MON-RRRR') start_date,
4730 paa.EFFECTIVE_END_DATE end_date,
4731 pap.person_id
4732 FROM per_all_assignments_f paa,
4733 per_assignment_status_types past,
4734 per_all_people_f pap
4735 WHERE paa.person_id = pap.person_id
4736 AND paa.assignment_id = c_assignment_id
4737 AND past.ASSIGNMENT_STATUS_TYPE_ID = paa.ASSIGNMENT_STATUS_TYPE_ID
4738 AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
4739 and pap.effective_start_date =
4740 ( select max(pap2.effective_start_date) from
4741 per_all_people_f pap2
4742 where pap2.person_id = pap.person_id
4743 and pap2.effective_start_date <= l_effective_date
4744 )
4745 and paa.effective_start_date =
4746 ( select max(asg2.effective_start_date)
4747 from per_all_assignments_f asg2
4748 where asg2.assignment_id = paa.assignment_id
4749 and asg2.assignment_type = 'E'
4750 and ASSIGNMENT_STATUS_TYPE_ID = paa.ASSIGNMENT_STATUS_TYPE_ID
4751 and asg2.effective_start_date <= l_effective_date
4752 )
4753
4754 ORDER BY end_date DESC;
4755
4756 -- Cursor to fetch error Messages from pay_message lines table
4757 CURSOR csr_err_msg(asg_action_id NUMBER)
4758 IS
4759 SELECT DISTINCT line_text
4760 FROM pay_message_lines
4761 WHERE source_id = asg_action_id
4762 AND message_level <> 'W'-- p_message_level
4763 AND payroll_id = 100;
4764
4765 CURSOR csr_warn_msg(asg_action_id NUMBER)
4766 IS
4767 SELECT DISTINCT line_text
4768 FROM pay_message_lines
4769 WHERE source_id = asg_action_id
4770 AND message_level = 'W'
4771 AND payroll_id = 100;
4772
4773 cursor csr_get_eff_date(p_pact_id number) is
4774 select EFFECTIVE_DATE
4775 from pay_payroll_actions
4776 where payroll_action_id = p_pact_id;
4777
4778 err_msg varchar2(255);--csr_err_msg%rowtype;
4779 et_asg_rec get_person_details%rowtype;
4780 asg_rec get_person_details%rowtype;
4781 l_et_temp VARCHAR2(255);
4782 BEGIN
4783 l_count := 0;
4784 l_count_warns := 0;
4785 open csr_get_eff_date(pactid);
4786 fetch csr_get_eff_date into l_effective_date;
4787 close csr_get_eff_date;
4788
4789 fnd_file.put_line(fnd_file.LOG, 'l_effective_date: ' || l_effective_date);
4790 --Completed Records
4791
4792 IF p_type = 'ET'THEN
4793 FOR action_id IN get_asg_action_id
4794 LOOP
4795 OPEN get_person_details(action_id.assignment_id);
4796 FETCH get_person_details INTO et_asg_rec;
4797 CLOSE get_person_details;
4798 l_et_temp := et_asg_rec.l_name || ', '|| et_asg_rec.title || ' ' || et_asg_rec.f_name || ' ' || et_asg_rec.m_name;
4799 IF et_asg_rec.end_date = fnd_date.canonical_to_date('4712/12/31 00:00:00') THEN
4800 fnd_file.put_line(fnd_file.output,rpad(et_asg_rec.emp_no, 18) || ' ' || rpad(et_asg_rec.ni_no ,10) || ' '
4801 || rpad(l_et_temp,50) );
4802 ELSE
4803 fnd_file.put_line(fnd_file.output,rpad(et_asg_rec.emp_no, 18) || ' ' || rpad(et_asg_rec.ni_no ,10) || ' '
4804 || rpad(l_et_temp,50));
4805 END IF;
4806 l_count := l_count + 1;
4807 END LOOP;
4808 END IF;
4809
4810 -- Errored Records
4811 IF p_type = 'E'THEN
4812 FOR action_id IN get_asg_action_id_error
4813 LOOP
4814 err_msg := ' ';
4815 -- Fetching details
4816 OPEN get_person_details(action_id.assignment_id);
4817 FETCH get_person_details INTO asg_rec;
4818 CLOSE get_person_details;
4819
4820 OPEN csr_err_msg(action_id.asg_action_id);
4821 FETCH csr_err_msg INTO err_msg;
4822 if csr_err_msg%notfound then
4823 SELECT LINE_TEXT into err_msg FROM pay_message_lines WHERE source_id = action_id.asg_action_id and rownum <=1;
4824 end if;
4825
4826 l_error_flag := 'Y';
4827
4828 l_temp := asg_rec.l_name || ', '|| asg_rec.title || ' ' || asg_rec.f_name || ' ' || asg_rec.m_name;
4829 IF asg_rec.end_date = fnd_date.canonical_to_date('4712/12/31 00:00:00') THEN
4830 fnd_file.put_line(fnd_file.output,rpad(asg_rec.emp_no, 18) || ' ' || rpad(asg_rec.ni_no ,10) || ' ' || rpad(l_temp,50)
4831 || ' ' || rpad(err_msg,120) ); -- error message
4832 ELSE
4833 fnd_file.put_line(fnd_file.output,rpad(asg_rec.emp_no, 18) || ' ' || rpad(asg_rec.ni_no ,10) || ' ' || rpad(l_temp,50)
4834 || ' ' || rpad(err_msg,120) ); -- error message
4835 END IF;
4836
4837 -- To print remaining error messgaes if any
4838 LOOP
4839 FETCH csr_err_msg INTO err_msg;
4840 EXIT
4841 WHEN csr_err_msg%notfound;
4842 fnd_file.put_line(fnd_file.output,rpad(' ',81,' ')||rpad(err_msg,120));
4843 END LOOP;
4844
4845 CLOSE csr_err_msg;
4846 l_count := l_count + 1;
4847 END LOOP;
4848 END IF;
4849 fnd_file.put_line(fnd_file.output,NULL);
4850
4851 -- Warning Records
4852 IF p_type = 'W'THEN
4853
4854 FOR action_id IN get_asg_action_id
4855 LOOP
4856 err_msg := ' ';
4857 -- Fetching details
4858 OPEN get_person_details(action_id.assignment_id);
4859 FETCH get_person_details INTO asg_rec;
4860 CLOSE get_person_details;
4861
4862 OPEN csr_warn_msg(action_id.asg_action_id);
4863 FETCH csr_warn_msg INTO err_msg;
4864
4865 IF csr_warn_msg%NOTFOUND THEN
4866 NULL;
4867 ELSE
4868 l_temp := asg_rec.l_name || ', '|| asg_rec.title || ' ' || asg_rec.f_name || ' ' || asg_rec.m_name;
4869 IF asg_rec.end_date = fnd_date.canonical_to_date('4712/12/31 00:00:00') THEN
4870 fnd_file.put_line(fnd_file.output,rpad(asg_rec.emp_no, 18) || ' ' || rpad(asg_rec.ni_no ,10) || ' ' || rpad(l_temp,50)
4871 || ' ' || rpad(err_msg,100) );
4872 ELSE
4873 fnd_file.put_line(fnd_file.output,rpad(asg_rec.emp_no, 18) || ' ' || rpad(asg_rec.ni_no ,10) || ' ' || rpad(l_temp,50)
4874 || ' ' || rpad(err_msg,100) );
4875 END IF;
4876 l_count_warns := l_count_warns + 1;
4877 END IF;
4878 CLOSE csr_warn_msg;
4879
4880 END LOOP;
4881
4882 END IF;
4883 fnd_file.put_line(fnd_file.output,NULL);
4884
4885 IF p_type = 'E' THEN
4886 fnd_file.put_line(fnd_file.output,'Total Number of employments completed with error : ' || l_count);
4887 ELSIF p_type = 'W' THEN
4888 fnd_file.put_line(fnd_file.output,'Total Number of employments completed with warning : ' || l_count_warns);
4889 ELSE
4890 fnd_file.put_line(fnd_file.output,'Total Number of employments completed successfully :' || l_count);
4891 END IF;
4892 l_counter := l_counter + l_count;
4893 END write_body;
4894 ---
4895 PROCEDURE write_footer
4896 IS
4897
4898 CURSOR get_person_count
4899 IS
4900 SELECT COUNT(DISTINCT(paaf.person_id))
4901 FROM pay_payroll_actions ppa,
4902 pay_assignment_actions paa,
4903 per_all_assignments_f paaf
4904 WHERE ppa.payroll_action_id = pactid -- pact_id
4905 AND paa.payroll_action_id = ppa.payroll_action_id
4906 AND paa.action_status = 'C'
4907 AND paaf.assignment_id = paa.assignment_id;
4908
4909 BEGIN
4910 OPEN get_person_count;
4911 FETCH get_person_count INTO l_person_count;
4912 CLOSE get_person_count;
4913
4914 fnd_file.put_line(fnd_file.output,NULL);
4915 fnd_file.put_line(fnd_file.output,'Total Number of employments : ' || l_counter);
4916 fnd_file.put_line(fnd_file.output,NULL);
4917 fnd_file.put_line(fnd_file.output,'Total Number of employees successfully processed : ' || l_person_count);
4918 END write_footer;
4919 ---------------------
4920 BEGIN
4921 hr_utility.trace('Entering deinit: '||l_proc);
4922
4923 hr_utility.set_location('Fetching Payroll Details ',10);
4924 delete from pay_action_information pai
4925 where pai.action_context_id = pactid
4926 and pai.action_context_type = 'PA'
4927 and pai.action_information_category in ('GB RTI P60 PAYROLL DET');
4928
4929
4930 hr_utility.set_location('Archiving GB RTI P60 PAYROLL DET',60);
4931
4932
4933 l_archive_p60_payroll := fetch_p60_payroll_det(pactid,
4934 g_start_year,
4935 g_end_year);
4936 hr_utility.set_location('After Fetching Payroll Details ',10);
4937
4938 pay_balance_pkg.set_context('PAYROLL_ACTION_ID',pactid);
4939
4940 OPEN csr_is_etext_report;
4941 FETCH csr_is_etext_report INTO l_is_etext_report;
4942 CLOSE csr_is_etext_report;
4943
4944 l_counter := 0;
4945 write_header(l_is_etext_report);
4946 write_sub_header('C',l_is_etext_report);
4947 write_body('ET',pactid,l_is_etext_report);
4948 write_sub_header('E',l_is_etext_report);
4949 write_body('E',pactid,l_is_etext_report);
4950 write_sub_header('W',l_is_etext_report);
4951 write_body('W',pactid,l_is_etext_report);
4952 write_footer;
4953
4954 hr_utility.set_location('Leaving: '||l_proc,999);
4955
4956 EXCEPTION
4957 When l_exp then
4958 fnd_file.put_line(fnd_file.LOG,'Errors found while archiving data.');
4959 raise_application_error(-20001,'Errors found while archiving data.');
4960 END deinitialization_code;
4961 --
4962 /**
4963 Called by the report PYGBRTIP60OP.xml
4964 **/
4965 function get_column_value(p_column_name varchar2, p_peop_grp_id number) return
4966 varchar2 is
4967 l_stmt varchar2(200);
4968 l_col_val varchar2(100);
4969 begin
4970 hr_utility.set_location('p_column_name: ' || p_column_name,60);
4971 hr_utility.set_location('p_peop_grp_id: '|| p_peop_grp_id,60);
4972 fnd_file.put_line(fnd_file.LOG,'p_column_name: ' || p_column_name || '-' ||'p_peop_grp_id: '|| p_peop_grp_id);
4973
4974 if p_column_name is not null and p_peop_grp_id is not null then
4975 l_stmt := 'select '|| p_column_name ||' from pay_people_groups pp where pp.people_group_id = :PEOPLE_GRP_ID';
4976 execute immediate l_stmt into l_col_val using p_peop_grp_id;
4977 fnd_file.put_line(fnd_file.LOG,'P-value: ' || l_col_val);
4978 return l_col_val;
4979 else
4980 return null;
4981 end if;
4982 end get_column_value;
4983
4984 END PAY_GB_RTI_P60;