[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_P11D_ARCHIVE_SS
Source
1 Package Body PAY_GB_P11D_ARCHIVE_SS as
2 /* $Header: pygbpdss.pkb 120.53 2011/04/20 08:24:37 ssarap noship $ */
3
4 g_package CONSTANT VARCHAR2(33) := 'PAY_GB_P11D_ARCHIVE_SS.';
5 g_pactid NUMBER;
6 g_application_id CONSTANT NUMBER := 801;
7 g_set_warning BOOLEAN := FALSE;
8 g_param_payroll_id NUMBER;
9 g_param_person_id NUMBER;
10 g_param_consolidation_set_id NUMBER;
11 g_param_tax_reference VARCHAR2(200);
12 g_param_assignment_set_id NUMBER;
13 g_param_benefit_end_date VARCHAR2(20);
14 g_param_benefit_start_date VARCHAR2(20);
15 g_param_business_group_id NUMBER;
16 g_param_rep_run varchar2(10);
17
18 TYPE g_rec_val_ff IS RECORD(
19 l_row_name VARCHAR2(80),
20 l_row_effective_start_date DATE,
21 l_row_effective_end_date DATE,
22 l_val_effective_start_date DATE,
23 l_val_effective_end_date DATE,
24 l_value VARCHAR2(80),
25 l_ff_formula_id NUMBER(9),
26 l_ff_effective_start_date DATE,
27 l_ff_effective_end_date DATE);
28
29 TYPE g_typ_val_ff_table IS TABLE OF g_rec_val_ff
30 INDEX BY BINARY_INTEGER;
31
32 g_val_ff_tab g_typ_val_ff_table;
33
34 TYPE g_typ_rec_benefit_detail IS RECORD(
35 assignment_action_id NUMBER(15),
36 element_type_id NUMBER(9),
37 element_entry_id NUMBER(15),
38 element_name VARCHAR2(80),
39 effective_start_date DATE,
40 person_id NUMBER(10),
41 assignment_id NUMBER(10),
42 classification_name VARCHAR2(83) );
43
44 TYPE g_typ_tab_ben_detail IS TABLE OF g_typ_rec_benefit_detail
45 INDEX BY BINARY_INTEGER;
46 /*
47 TYPE g_typ_tab_ben_detail_tab IS TABLE OF g_typ_tab_ben_detail
48 INDEX BY BINARY_INTEGER;
49 */
50
51 TYPE g_typ_non_iv_act_info_rec IS RECORD
52 (element_type_id NUMBER(9),
53 input_value_name varchar2(50) );
54
55 TYPE g_typ_non_iv_act_info_items IS TABLE OF g_typ_non_iv_act_info_rec
56 INDEX BY BINARY_INTEGER;
57
58 TYPE g_typ_non_iv_index IS TABLE OF Number
59 INDEX BY BINARY_INTEGER;
60
61 /*
62 TYPE g_typ_ele_extra_act_info_items IS TABLE OF g_typ_non_iv_act_info_items
63 INDEX BY BINARY_INTEGER;
64 */
65
66 l_non_iv_act_info_items g_typ_non_iv_act_info_items;
67 l_non_iv_index g_typ_non_iv_index;
68
69 -- l_extra_act_info_items g_typ_ele_extra_act_info_items;
70
71 g_tab_ben_detail g_typ_tab_ben_detail;
72 c_tab_ben_detail g_typ_tab_ben_detail; -- this is the null table
73 -- g_tab_ben_detail_tab g_typ_tab_ben_detail_tab;
74 g_ben_asg_count NUMBER := 0;
75
76
77 -- l_ben_asg_det_table l_typ_ben_asg_det_table;
78
79 TYPE g_typ_assign_sum_info_rec IS RECORD(
80 a_desc VARCHAR2(150),
81 a_cost NUMBER,
82 a_amg NUMBER,
83 a_ce NUMBER,
84 b_desc VARCHAR2(150),
85 b_ce NUMBER,
86 b_tnp NUMBER,
87 c_cost NUMBER,
88 c_amg NUMBER,
89 c_ce NUMBER,
90 d_ce NUMBER,
91 e_ce NUMBER,
92 f_tcce NUMBER,
93 f_tfce NUMBER,
94 g_ce NUMBER,
95 g_fce NUMBER,
96 i_cost NUMBER,
97 i_amg NUMBER,
98 i_ce NUMBER,
99 j_ce NUMBER,
100 k_cost NUMBER,
101 k_amg NUMBER,
102 k_ce NUMBER,
103 l_desc VARCHAR2(150),
104 l_cost NUMBER,
105 l_amg NUMBER,
106 l_ce NUMBER,
107 m_shares VARCHAR2(150),
108 h_ce1 NUMBER,
109 h_count NUMBER,
110 f_count NUMBER,
111 n_desc VARCHAR2(150),
112 n_cost NUMBER,
113 n_amg NUMBER,
114 n_ce NUMBER,
115 na_desc VARCHAR2(150),
116 na_cost NUMBER,
117 na_amg NUMBER,
118 na_ce NUMBER,
119 n_taxpaid NUMBER,
120 o1_cost NUMBER,
121 o1_amg NUMBER,
122 o1_ce NUMBER,
123 o2_cost NUMBER,
124 o2_amg NUMBER,
125 o2_ce NUMBER,
126 o_toi VARCHAR2(150),
127 o3_cost NUMBER,
128 o3_amg NUMBER,
129 o3_ce NUMBER,
130 o4_cost NUMBER,
131 o4_amg NUMBER,
132 o4_ce NUMBER,
133 o5_cost NUMBER,
134 o5_amg NUMBER,
135 o5_ce NUMBER,
136 o6_desc VARCHAR2(150),
137 o6_cost NUMBER,
138 o6_amg NUMBER,
139 o6_ce NUMBER);
140
141 g_assign_sum_info_rec g_typ_assign_sum_info_rec;
142 c_assign_sum_info_null_rec g_typ_assign_sum_info_rec;
143
144 CURSOR csr_payroll_info(v_benefit_end_date VARCHAR2, v_payroll_id NUMBER)
145 IS
146 SELECT org.org_information1 employers_ref_no, org.org_information2 tax_office_name,
147 org.org_information8 tax_office_phone_no, org.org_information3 employer_name,
148 org.org_information4 employer_address
149 FROM pay_payrolls_f ppf, hr_soft_coding_keyflex flex, hr_organization_information org
150 WHERE ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
151 AND fnd_date.canonical_to_date(v_benefit_end_date)
152 BETWEEN NVL(flex.start_date_active, fnd_date.canonical_to_date(v_benefit_end_date) )
153 AND NVL(flex.end_date_active, fnd_date.canonical_to_date(v_benefit_end_date) )
154 AND fnd_date.canonical_to_date(v_benefit_end_date)
155 BETWEEN NVL(ppf.effective_start_date, fnd_date.canonical_to_date(v_benefit_end_date) )
156 AND NVL(ppf.effective_end_date, fnd_date.canonical_to_date(v_benefit_end_date) )
157 AND ppf.business_group_id = org.organization_id AND org.org_information1 = flex.segment1
158 AND org.org_information_context = 'Tax Details References' AND ppf.payroll_id = v_payroll_id;
159
160 PROCEDURE get_parameters(p_payroll_action_id IN NUMBER, p_token_name IN VARCHAR2, p_token_value OUT NOCOPY VARCHAR2)
161 IS
162 CURSOR csr_parameter_info(p_pact_id NUMBER, p_token CHAR)
163 IS
164 SELECT SUBSTR(
165 legislative_parameters,
166 INSTR(legislative_parameters, p_token) + (LENGTH(p_token) + 1),
167 (DECODE(
168 INSTR(legislative_parameters, ' ', INSTR(legislative_parameters, p_token) ),
169 0, DECODE(INSTR(legislative_parameters, p_token), 0, .5, LENGTH(legislative_parameters) ),
170 INSTR(legislative_parameters, ' ', INSTR(legislative_parameters, p_token) )
171 - (INSTR(legislative_parameters, p_token) + (LENGTH(p_token) + 1) ) ) ) ),
172 business_group_id, start_date, effective_date -- this will be the benefit end date
173 FROM pay_payroll_actions
174 WHERE payroll_action_id = p_pact_id;
175
176 l_business_group_id VARCHAR2(20);
177 l_benefit_start_date VARCHAR2(20);
178 l_benefit_end_date VARCHAR2(20);
179 l_token_value VARCHAR2(50);
180 l_proc VARCHAR2(50) := g_package || 'get_parameters';
181 BEGIN
182 hr_utility.set_location('Entering '|| l_proc, 10);
183 hr_utility.set_location('Step '|| l_proc, 20);
184 hr_utility.set_location('p_token_name = '|| p_token_name, 20);
185 OPEN csr_parameter_info(p_payroll_action_id, p_token_name);
186 FETCH csr_parameter_info INTO l_token_value, l_business_group_id, l_benefit_start_date, l_benefit_end_date;
187 CLOSE csr_parameter_info;
188
189 IF p_token_name = 'BG_ID'
190 THEN
191 p_token_value := l_business_group_id;
192 ELSIF p_token_name = 'BENEFIT_START_DATE'
193 THEN
194 p_token_value := fnd_date.date_to_canonical(l_benefit_start_date);
195 ELSIF p_token_name = 'BENEFIT_END_DATE'
196 THEN
197 p_token_value := fnd_date.date_to_canonical(l_benefit_end_date);
198 ELSE
199 p_token_value := l_token_value;
200 END IF;
201
202 hr_utility.set_location('p_token_value = '|| p_token_value, 60);
203 hr_utility.set_location('Leaving '|| l_proc, 70);
204 END get_parameters;
205
206 FUNCTION check_assignment_tax_ref(p_assignment_id number,
207 p_tax_ref varchar2,
208 p_end_date varchar2) return boolean is
209 l_ret boolean;
210 l_check number;
211 cursor csr_check_asg is
212 select 1
213 from per_all_assignments_f asg,
214 pay_all_payrolls_f pay,
215 hr_soft_coding_keyflex flex
216 where asg.assignment_id = p_assignment_id
217 and asg.payroll_id = pay.payroll_id
218 and pay.soft_coding_keyflex_id + 0 = flex.soft_coding_keyflex_id
219 and (p_tax_ref is null
220 or
221 flex.segment1 = p_tax_ref)
222 and (fnd_date.canonical_to_date(p_end_date) between asg.effective_start_date and asg.effective_end_date
223 or
224 ( asg.effective_end_date = (select max(paa2.effective_end_date)
225 from per_assignments_f paa2
226 where paa2.assignment_id = p_assignment_id)
227 and asg.effective_end_date < fnd_date.canonical_to_date(p_end_date)));
228 BEGIN
229 l_ret := false;
230 open csr_check_asg;
231 fetch csr_check_asg into l_check;
232 if csr_check_asg%FOUND then
233 l_ret := true;
234 end if;
235 close csr_check_asg;
236 return l_ret;
237 END;
238
239 FUNCTION find_exec_formula(
240 p_element_name VARCHAR2,
241 p_effective_date DATE,
242 p_formula_effective_start_date OUT NOCOPY DATE)
243 RETURN NUMBER
244 IS
245 l_counter INTEGER := 0;
246 l_search_from INTEGER := 0;
247
248 FUNCTION find_first_entry
249 RETURN INTEGER
250 IS
251 l_lower INTEGER;
252 l_upper INTEGER;
253 l_check_item INTEGER;
254 l_first_matching_item INTEGER := 0;
255 l_match BOOLEAN := FALSE;
256 BEGIN
257 l_lower := 1;
258 l_upper := g_val_ff_tab.COUNT;
259
260 FOR counter IN l_lower .. l_upper
261 LOOP
262 l_check_item := FLOOR( (l_lower + l_upper) / 2);
263
264 IF g_val_ff_tab(l_check_item).l_row_name = p_element_name
265 THEN
266 l_match := TRUE;
267 hr_utility.TRACE('.. MATCHED..');
268 EXIT;
269 ELSIF p_element_name < g_val_ff_tab(l_check_item).l_row_name
270 THEN
271 -- search below this
272 l_upper := l_check_item - 1;
273 ELSE
274 l_lower := l_check_item + 1;
275 END IF;
276 END LOOP;
277
278 IF l_match
279 THEN
280 -- it returned the first match, due ti dat effective rows
281 -- there could be rows for same name before the matched row
282 -- we need to find them
283 IF l_check_item = 1
284 THEN
285 l_first_matching_item := l_check_item;
286 ELSE
287 FOR counter IN REVERSE 1 .. l_check_item
288 LOOP
289 IF g_val_ff_tab(counter).l_row_name = p_element_name
290 THEN
291 -- item matches and counter is 1 menaing the first item
292 IF counter = 1
293 THEN
294 l_first_matching_item := counter;
295 END IF;
296 ELSE -- item does not match meaning the match first is counter +1
297 l_first_matching_item := counter + 1;
298 EXIT;
299 END IF;
300 END LOOP;
301 END IF;
302 END IF;
303
304 hr_utility.TRACE(' Returning l_first_matching_item '|| l_first_matching_item);
305 RETURN l_first_matching_item;
306 END;
307 BEGIN
308 hr_utility.TRACE('inside find_exec_formula');
309 hr_utility.TRACE('g_val_ff_tab.count '|| g_val_ff_tab.COUNT);
310 hr_utility.TRACE('p_element_name '|| p_element_name);
311 hr_utility.TRACE('p_element_name '|| p_element_name);
312 hr_utility.TRACE('p_effective_date '|| TO_DATE(p_effective_date, 'DD/MM/YYYY') );
313 l_search_from := find_first_entry;
314
315 IF l_search_from <> 0
316 THEN
317 hr_utility.TRACE('l_search_from '|| l_search_from);
318
319 FOR l_counter IN l_search_from .. g_val_ff_tab.COUNT
320 LOOP
321 hr_utility.TRACE(
322 'g_val_ff_tab(l_counter).l_row_effective_start_date '
323 || TO_DATE(g_val_ff_tab(l_counter).l_row_effective_start_date, 'DD/MM/YYYY') );
324 hr_utility.TRACE(
325 'g_val_ff_tab(l_counter).l_row_effective_end_date '
326 || TO_DATE(g_val_ff_tab(l_counter).l_row_effective_end_date, 'DD/MM/YYYY') );
327 hr_utility.TRACE(
328 'l_val_effective_start_date '|| TO_DATE(
329 g_val_ff_tab(l_counter).l_val_effective_start_date,
330 'DD/MM/YYYY') );
331 hr_utility.TRACE(
332 'g_val_ff_tab(l_counter).l_val_effective_end_date '
333 || TO_DATE(g_val_ff_tab(l_counter).l_val_effective_end_date, 'DD/MM/YYYY') );
334 hr_utility.TRACE(
335 'l_ff_effective_start_date '|| TO_DATE(g_val_ff_tab(l_counter).l_ff_effective_start_date, 'DD/MM/YYYY') );
336 hr_utility.TRACE(
337 'l_ff_effective_end_date '|| TO_DATE(g_val_ff_tab(l_counter).l_ff_effective_end_date, 'DD/MM/YYYY') );
338
339 IF g_val_ff_tab(l_counter).l_row_name = p_element_name
340 AND p_effective_date BETWEEN g_val_ff_tab(l_counter).l_row_effective_start_date
341 AND g_val_ff_tab(l_counter).l_row_effective_end_date
342 AND p_effective_date BETWEEN g_val_ff_tab(l_counter).l_val_effective_start_date
343 AND g_val_ff_tab(l_counter).l_val_effective_end_date
344 AND p_effective_date BETWEEN g_val_ff_tab(l_counter).l_ff_effective_start_date
345 AND g_val_ff_tab(l_counter).l_ff_effective_end_date
346 THEN
347 p_formula_effective_start_date := g_val_ff_tab(l_counter).l_ff_effective_start_date;
348 RETURN g_val_ff_tab(l_counter).l_ff_formula_id;
349 END IF;
350 END LOOP;
351 ELSE
352 RETURN NULL;
353 END IF;
354 END;
355
356 PROCEDURE archinit(p_payroll_action_id IN NUMBER)
357 IS
358 l_proc CONSTANT VARCHAR2(50) := g_package || ' archinit';
359 l_table_id pay_user_tables.user_table_id%TYPE;
360
361 FUNCTION fetch_validation_table_id(p_table_name VARCHAR2)
362 RETURN NUMBER
363 IS
364 l_table_id pay_user_tables.user_table_id%TYPE;
365 BEGIN
366 SELECT user_table_id
367 INTO l_table_id
368 FROM pay_user_tables
369 WHERE UPPER(user_table_name) = UPPER(p_table_name) AND business_group_id IS NULL AND legislation_code = 'GB';
370
371 RETURN l_table_id;
372 END;
373
374 PROCEDURE populate_table_value(p_bus_group_id NUMBER, p_table_id NUMBER, p_col_name VARCHAR2)
375 IS
376 CURSOR populate_user_table
377 IS
378 SELECT r.row_low_range_or_name NAME, r.effective_start_date row_start_date,
379 r.effective_end_date row_end_date, cinst.effective_start_date col_inst_start_date,
380 cinst.effective_end_date col_inst_end_date, cinst.VALUE VALUE, ff.formula_id formula_id,
381 ff.effective_start_date ff_start_date, ff.effective_end_date ff_end_date
382 FROM pay_user_column_instances_f cinst,
383 pay_user_columns c,
384 pay_user_rows_f r,
385 pay_user_tables tab,
386 ff_formulas_f ff
387 WHERE tab.user_table_id = p_table_id AND c.user_table_id = tab.user_table_id
388 AND NVL(c.business_group_id, p_bus_group_id) = p_bus_group_id AND NVL(c.legislation_code, 'GB') =
389 'GB'
390 AND UPPER(c.user_column_name) = UPPER(p_col_name) AND cinst.user_column_id = c.user_column_id
391 AND r.user_table_id = tab.user_table_id
392
393 -- and l_effective_date between R.effective_start_date and R.effective_end_date
394 AND NVL(r.business_group_id, p_bus_group_id) = p_bus_group_id AND NVL(r.legislation_code, 'GB') =
395 'GB'
396 AND cinst.user_row_id = r.user_row_id
397
398 -- and l_effective_date between CINST.effective_start_date and CINST.effective_end_date
399 AND NVL(cinst.business_group_id, p_bus_group_id) = p_bus_group_id
400 AND NVL(cinst.legislation_code, 'GB') = 'GB' AND formula_name = cinst.VALUE
401 ORDER BY r.row_low_range_or_name,
402 r.effective_start_date,
403 r.effective_end_date,
404 cinst.effective_start_date,
405 cinst.effective_end_date,
406 ff.effective_start_date,
407 ff.effective_end_date;
408
409 l_count INTEGER := 0;
410 BEGIN
411 FOR get_all_ffs IN populate_user_table
412 LOOP
413 l_count := l_count + 1;
414 g_val_ff_tab(l_count).l_row_name := get_all_ffs.NAME;
415 g_val_ff_tab(l_count).l_row_effective_start_date := get_all_ffs.row_start_date;
416 g_val_ff_tab(l_count).l_row_effective_end_date := get_all_ffs.row_end_date;
417 g_val_ff_tab(l_count).l_val_effective_start_date := get_all_ffs.col_inst_start_date;
418 g_val_ff_tab(l_count).l_val_effective_end_date := get_all_ffs.col_inst_end_date;
419 g_val_ff_tab(l_count).l_value := get_all_ffs.VALUE;
420 g_val_ff_tab(l_count).l_ff_formula_id := get_all_ffs.formula_id;
421 g_val_ff_tab(l_count).l_ff_effective_start_date := get_all_ffs.ff_start_date;
422 g_val_ff_tab(l_count).l_ff_effective_end_date := get_all_ffs.ff_end_date;
423 END LOOP;
424 END;
425 BEGIN
426
427 -- -- hr_utility.trace_on(null,'ARCH');
428 hr_utility.set_location('Entering '|| l_proc, 10);
429 g_pactid := p_payroll_action_id;
430 PAY_GB_P11D_ARCHIVE_SS.get_parameters(
431 p_payroll_action_id => p_payroll_action_id,
432 p_token_name => 'PAYROLL',
433 p_token_value => g_param_payroll_id);
434 PAY_GB_P11D_ARCHIVE_SS.get_parameters(
435 p_payroll_action_id => p_payroll_action_id,
436 p_token_name => 'PERSON',
437 p_token_value => g_param_person_id);
438 PAY_GB_P11D_ARCHIVE_SS.get_parameters(
439 p_payroll_action_id => p_payroll_action_id,
440 p_token_name => 'CONSOLIDATION_SET',
441 p_token_value => g_param_consolidation_set_id);
442 PAY_GB_P11D_ARCHIVE_SS.get_parameters(
443 p_payroll_action_id => p_payroll_action_id,
444 p_token_name => 'TAX_REFERENCE',
445 p_token_value => g_param_tax_reference);
446 PAY_GB_P11D_ARCHIVE_SS.get_parameters(
447 p_payroll_action_id => p_payroll_action_id,
448 p_token_name => 'ASSIGNMENT_SET_ID',
449 p_token_value => g_param_assignment_set_id);
450 PAY_GB_P11D_ARCHIVE_SS.get_parameters(
451 p_payroll_action_id => p_payroll_action_id,
452 p_token_name => 'BG_ID',
453 p_token_value => g_param_business_group_id);
454 PAY_GB_P11D_ARCHIVE_SS.get_parameters(
455 p_payroll_action_id => p_payroll_action_id,
456 p_token_name => 'BENEFIT_START_DATE',
457 p_token_value => g_param_benefit_start_date);
458 PAY_GB_P11D_ARCHIVE_SS.get_parameters(
459 p_payroll_action_id => p_payroll_action_id,
460 p_token_name => 'BENEFIT_END_DATE',
461 p_token_value => g_param_benefit_end_date);
462
463 PAY_GB_P11D_ARCHIVE_SS.get_parameters(
464 p_payroll_action_id => p_payroll_action_id,
465 p_token_name => 'Rep_Run',
466 p_token_value => g_param_rep_run);
467
468 l_table_id := fetch_validation_table_id('VALIDATION_FORMULA_NAME');
469 populate_table_value(g_param_business_group_id, l_table_id, 'FORMULA_NAME');
470 hr_utility.set_location('Leaving '|| l_proc, 10);
471 END archinit;
472
473 PROCEDURE range_cursor(pactid IN NUMBER, sqlstr OUT NOCOPY VARCHAR2)
474 IS
475 l_proc CONSTANT VARCHAR2(50) := g_package || ' range_cursor';
476 l_person_id number;
477 BEGIN
478
479 -- hr_utility.trace_on(null,'ARCH');
480
481 PAY_GB_P11D_ARCHIVE_SS.get_parameters(
482 p_payroll_action_id => pactid,
483 p_token_name => 'PERSON',
484 p_token_value => l_person_id);
485
486 hr_utility.set_location('Entering '|| l_proc, 10);
487
488 if l_person_id is not null then
489 sqlstr :=
490 'SELECT DISTINCT person_id
491 FROM per_people_f ppf,
492 pay_payroll_actions ppa
493 WHERE ppa.payroll_action_id = :payroll_action_id
494 AND ppa.business_group_id +0= ppf.business_group_id
495 AND ppf.person_id = ' || l_person_id || ' ORDER BY ppf.person_id';
496 else
497 sqlstr :=
498 'SELECT DISTINCT person_id
499 FROM per_people_f ppf,
500 pay_payroll_actions ppa
501 WHERE ppa.payroll_action_id = :payroll_action_id
502 AND ppa.business_group_id +0= ppf.business_group_id
503 ORDER BY ppf.person_id';
504 end if;
505 hr_utility.set_location('Leaving '|| l_proc, 20);
506 END range_cursor;
507
508 PROCEDURE action_creation(pactid IN NUMBER, stperson IN NUMBER, endperson IN NUMBER, CHUNK IN NUMBER)
509 IS
510 l_actid NUMBER;
511 l_benefit_end_date VARCHAR2(20);
512 l_benefit_start_date VARCHAR2(20);
513 l_payroll_id NUMBER;
514 l_person_id NUMBER;
515 l_rep_run varchar2(10);
516 l_consolidation_set_id NUMBER;
517 l_tax_reference VARCHAR2(200);
518 l_business_group_id NUMBER;
519 l_assignment_set_id NUMBER;
520 l_flex_id NUMBER;
521 l_proc CONSTANT VARCHAR2(50) := g_package || 'action_creation';
522 l_prev_assignment_id NUMBER(10) := NULL;
523 l_leg_param pay_payroll_actions.legislative_parameters%type;
524 l_count NUMBER;
525 l_set_payroll_id NUMBER;
526 l_archive BOOLEAN;
527 l_set_type VARCHAR2(2);
528 --bug 6278134 removed parameter l_tax_reference from cursor csr_flex_id
529 -- bug 7122883 removed the below cursor
530 /* cursor csr_flex_id is
531 select distinct flex.soft_coding_keyflex_id flex_id
532 from hr_soft_coding_keyflex flex,
533 --bug 6278134 added join conditions with ppf
534 pay_all_payrolls_f ppf
535 where flex.segment1 = nvl(l_tax_reference,flex.segment1)
536 AND ppf.soft_coding_keyflex_id + 0 = flex.soft_coding_keyflex_id
537 and ppf.payroll_id = nvl(l_payroll_id,ppf.payroll_id)
538 -- bug 7122883 added join condition with business_group_id
539 and ppf.business_group_id=l_business_group_id
540 -- Bug 6278134: Added effective date condition
541 and fnd_date.canonical_to_date(l_benefit_end_date) between ppf.effective_start_date and ppf.effective_end_date; */
542
543 cursor csr_check_asg_set(p_asg_set_id number) is
544 select count(*)
545 from hr_assignment_set_amendments
546 where assignment_set_id = p_asg_set_id;
547
548 cursor csr_check_payroll(p_asg_set_id number) is
549 select payroll_id
550 from hr_assignment_sets
551 where assignment_set_id = p_asg_set_id;
552
553 -- assuming that set can contains 1 type of Amendment
554 cursor csr_set_type(p_asg_set_id number) is
555 select distinct include_or_exclude
556 from hr_assignment_set_amendments
557 where assignment_set_id = p_asg_set_id;
558 --bug 6278134 passed parameter to the csr_assign_set_X and csr_noassign_set
559
560 cursor csr_assign_set_X is
561 select
562 distinct
563 paa.assignment_id,
564 paa.person_id,
565 UPPER('GB_'|| pec.classification_name) classification_name,
566 pet.element_name element_name,
567 pet.element_type_id element_type_id,
568 peev.element_entry_id element_entry_id,
569 peev.effective_start_date effective_start_date
570 from per_all_assignments_f paa,
571 pay_all_payrolls_f ppf,
572 pay_element_classifications pec,
573 pay_element_types_f pet,
574 pay_input_values_f piv,
575 pay_element_entries_f pee,
576 pay_element_entry_values_f peev
577 where paa.person_id between stperson AND endperson
578 and (fnd_date.canonical_to_date(l_benefit_end_date)
579 between paa.effective_start_date AND paa.effective_end_date
580 or
581 paa.effective_end_date > fnd_date.canonical_to_date(l_benefit_start_date))
582 and paa.payroll_id = ppf.payroll_id
583 -- bug 7122883 added join condition with business_group_id
584 and ppf.business_group_id=l_business_group_id
585 and least(fnd_date.canonical_to_date(l_benefit_end_date),paa.effective_end_date)
586 between ppf.effective_start_date and ppf.effective_end_date
587 and (l_payroll_id is null or ppf.payroll_id = l_payroll_id)
588 and (l_consolidation_set_id is null or ppf.consolidation_set_id = l_consolidation_set_id)
589 -- bug 7122883 removed the below join
590 -- and ppf.soft_coding_keyflex_id + 0 = p_flex_id
591 --bug 7122883 added the below join so as to fetch all the records in that tax refernce
592 and (l_tax_reference is null or
593 ppf.soft_coding_keyflex_id + 0 in (select distinct flex.soft_coding_keyflex_id flex_id
594 from hr_soft_coding_keyflex flex where flex.segment1 = l_tax_reference))
595 and pec.legislation_code = 'GB'
596 and pec.classification_name like 'EXTERNAL REPORTING%'
597 and pet.classification_id = pec.classification_id
598 and pet.element_type_id = piv.element_type_id
599 and (piv.name = 'Benefit Start Date' or piv.name = 'Benefit End Date')
600 and pee.assignment_id = paa.assignment_id
601 and pee.element_type_id = pet.element_type_id
602 and pee.element_entry_id = peev.element_entry_id
603 and peev.input_value_id = piv.input_value_id
604 and peev.screen_entry_value between l_benefit_start_date and l_benefit_end_date
605 and exists (select 1
606 from hr_assignment_sets has,
607 hr_assignment_set_amendments hasa
608 where has.assignment_set_id = l_assignment_set_id
609 and has.business_group_id = paa.business_group_id
610 and nvl(has.payroll_id, paa.payroll_id) = paa.payroll_id
611 and hasa.assignment_set_id = has.assignment_set_id
612 and hasa.assignment_id = paa.assignment_id
613 and hasa.include_or_exclude = 'I')
614 --bug 6278134 removed exclude assignment set conditon
615 /*and not exists (select 1
616 from hr_assignment_sets has,
617 hr_assignment_set_amendments hasa
618 where has.assignment_set_id = l_assignment_set_id
619 and has.business_group_id = paa.business_group_id
620 and nvl(has.payroll_id, paa.payroll_id) = paa.payroll_id
621 and hasa.assignment_set_id = has.assignment_set_id
622 and hasa.assignment_id = paa.assignment_id
623 and hasa.include_or_exclude = 'E')*/
624 order by paa.assignment_id;
625
626 --bug 6278134 Cursor to fetch assignments for exclude assignment set
627 cursor csr_assign_set_EX is
628 select
629
630 distinct
631 paa.assignment_id,
632 paa.person_id,
633 UPPER('GB_'|| pec.classification_name) classification_name,
634 pet.element_name element_name,
635 pet.element_type_id element_type_id,
636 peev.element_entry_id element_entry_id,
637 peev.effective_start_date effective_start_date
638 from per_all_assignments_f paa,
639 pay_all_payrolls_f ppf,
640 pay_element_classifications pec,
641 pay_element_types_f pet,
642 pay_input_values_f piv,
643 pay_element_entries_f pee,
644 pay_element_entry_values_f peev
645 where paa.person_id between stperson AND endperson
646 and (fnd_date.canonical_to_date(l_benefit_end_date)
647 between paa.effective_start_date AND paa.effective_end_date
648 or
649 paa.effective_end_date > fnd_date.canonical_to_date(l_benefit_start_date))
650 and paa.payroll_id = ppf.payroll_id
651 -- bug 7122883 added join condition with business_group_id
652 and ppf.business_group_id=l_business_group_id
653 and least(fnd_date.canonical_to_date(l_benefit_end_date),paa.effective_end_date)
654 between ppf.effective_start_date and ppf.effective_end_date
655 and (l_payroll_id is null or ppf.payroll_id = l_payroll_id)
656 and (l_consolidation_set_id is null or ppf.consolidation_set_id = l_consolidation_set_id)
657 -- bug 7122883 removed the below join
658 -- and ppf.soft_coding_keyflex_id + 0 = p_flex_id
659 --bug 7122883 added the below join so as to fetch all the records in that tax refernce
660 and (l_tax_reference is null or
661 ppf.soft_coding_keyflex_id + 0 in (select distinct flex.soft_coding_keyflex_id flex_id
662 from hr_soft_coding_keyflex flex where flex.segment1 = l_tax_reference))
663 and pec.legislation_code = 'GB'
664 and pec.classification_name like 'EXTERNAL REPORTING%'
665 and pet.classification_id = pec.classification_id
666 and pet.element_type_id = piv.element_type_id
667 and (piv.name = 'Benefit Start Date' or piv.name = 'Benefit End Date')
668 and pee.assignment_id = paa.assignment_id
669 and pee.element_type_id = pet.element_type_id
670 and pee.element_entry_id = peev.element_entry_id
671 and peev.input_value_id = piv.input_value_id
672 and peev.screen_entry_value between l_benefit_start_date and l_benefit_end_date
673 and not exists (select 1
674 from hr_assignment_sets has,
675 hr_assignment_set_amendments hasa
676 where has.assignment_set_id = l_assignment_set_id
677 and has.business_group_id = paa.business_group_id
678 and nvl(has.payroll_id, paa.payroll_id) = paa.payroll_id
679 and hasa.assignment_set_id = has.assignment_set_id
680 and hasa.assignment_id = paa.assignment_id
681 and hasa.include_or_exclude = 'E')
682 order by paa.assignment_id;
683
684 cursor csr_noassign_set is
685 select /*+ ORDERED INDEX(paa PER_ASSIGNMENTS_F_N12,
686 ppf PAY_PAYROLLS_F_PK)
687 USE_NL(paa,ppf,pec,pet,piv,pee,peev) */
688 distinct
689 paa.assignment_id,
690 paa.person_id,
691 UPPER('GB_'|| pec.classification_name) classification_name,
692 pet.element_name element_name,
693 pet.element_type_id element_type_id,
694 peev.element_entry_id element_entry_id,
695 peev.effective_start_date effective_start_date
696 from per_all_assignments_f paa,
697 pay_all_payrolls_f ppf,
698 pay_element_classifications pec,
699 pay_element_types_f pet,
700 pay_input_values_f piv,
701 pay_element_entries_f pee,
702 pay_element_entry_values_f peev
703 where paa.person_id between stperson AND endperson
704 and (fnd_date.canonical_to_date(l_benefit_end_date)
705 between paa.effective_start_date AND paa.effective_end_date
706 or
707 paa.effective_end_date > fnd_date.canonical_to_date(l_benefit_start_date))
708 and paa.payroll_id = ppf.payroll_id
709 -- bug 7122883 added join condition with business_group_id
710 and ppf.business_group_id=l_business_group_id
711 and least(fnd_date.canonical_to_date(l_benefit_end_date),paa.effective_end_date)
712 between ppf.effective_start_date and ppf.effective_end_date
713 and (l_payroll_id is null or ppf.payroll_id = l_payroll_id)
714 and (l_consolidation_set_id is null or ppf.consolidation_set_id = l_consolidation_set_id)
715 -- bug 7122883 removed the below join
716 -- and ppf.soft_coding_keyflex_id + 0 = p_flex_id
717 --bug 7122883 added the below join so as to fetch all the records in that tax refernce
718 and (l_tax_reference is null or
719 ppf.soft_coding_keyflex_id + 0 in (select distinct flex.soft_coding_keyflex_id flex_id
720 from hr_soft_coding_keyflex flex where flex.segment1 = l_tax_reference))
721 and pec.legislation_code = 'GB'
722 and pec.classification_name like 'EXTERNAL REPORTING%'
723 and pet.classification_id = pec.classification_id
724 and pet.element_type_id = piv.element_type_id
725 and (piv.name = 'Benefit Start Date' or piv.name = 'Benefit End Date')
726 and pee.assignment_id = paa.assignment_id
727 and pee.element_type_id = pet.element_type_id
728 and pee.element_entry_id = peev.element_entry_id
729 and peev.input_value_id = piv.input_value_id
730 and peev.screen_entry_value between l_benefit_start_date and l_benefit_end_date
731 order by paa.assignment_id;
732
733 function get_param_value(p_token varchar2) return varchar2
734 is
735 --l_ret varchar2(255);
736 x number;
737 y number;
738 z number;
739 begin
740 /*
741 select SUBSTR(l_leg_param,INSTR(l_leg_param, p_token) + (LENGTH(p_token) + 1),
742 (DECODE(INSTR(l_leg_param, ' ', INSTR(l_leg_param, p_token)),
743 0, DECODE(INSTR(l_leg_param, p_token), 0, .5, LENGTH(l_leg_param)),
744 INSTR(l_leg_param, ' ', INSTR(l_leg_param, p_token))
745 - (INSTR(l_leg_param, p_token) + (LENGTH(p_token) + 1)))))
746 into l_ret
747 from dual;
748 return l_ret; */
749 x := instr(l_leg_param,p_token);
750 y := length(p_token);
751 if instr(l_leg_param, ' ', x) <> 0 then
752 Z := INSTR(l_leg_param, ' ', INSTR(l_leg_param, p_token)) -
753 (INSTR(l_leg_param, p_token) + (LENGTH(p_token) + 1));
754 else
755 z := 0;
756 if instr(l_leg_param, p_token) <> 0 then
757 z := length(l_leg_param);
758 end if;
759 end if;
760 return SUBSTR(l_leg_param, X + Y + 1, Z);
761 end;
762
763 BEGIN
764 -- hr_utility.trace_on(null,'ARCH');
765 hr_utility.set_location('Entering '|| l_proc, 10);
766
767 -- could not use the param parameters initialised in init procedure
768 -- as the action creation does not get called at all if i use them
769 select legislative_parameters,
770 business_group_id,
771 fnd_date.date_to_canonical(start_date),
772 fnd_date.date_to_canonical(effective_date)
773 into l_leg_param, l_business_group_id, l_benefit_start_date, l_benefit_end_date
774 from pay_payroll_actions
775 where payroll_action_id = pactid;
776
777 l_payroll_id := get_param_value('PAYROLL');
778 l_person_id := get_param_value('PERSON');
779 l_consolidation_set_id := get_param_value('CONSOLIDATION_SET');
780 l_tax_reference := get_param_value('TAX_REFERENCE');
781 l_assignment_set_id := get_param_value('ASSIGNMENT_SET_ID');
782 l_rep_run := get_param_value('Rep_Run');
783 l_archive := TRUE;
784 -- Check to see if the conc program parameters are
785 -- correctly selected
786 If fnd_date.canonical_to_date(l_benefit_start_date) <
787 to_date('06-04-' ||to_char(to_number(l_rep_run)-1),'dd-mm-yyyy') or
788 fnd_date.canonical_to_date(l_benefit_start_date) >
789 to_date('05-04-' ||l_rep_run,'dd-mm-yyyy')
790 Then
791 pay_core_utils.push_message(800, 'HR_78076_P11D_DATE_PARAM_ERR', 'F');
792 pay_core_utils.push_token('NAME', 'Benefit Start Date');
793 pay_core_utils.push_token('VAl1','06-04-' ||to_char(to_number(l_rep_run)-1));
794 pay_core_utils.push_token('VAl2', '05-04-' ||l_rep_run);
795
796 fnd_message.set_name('PER', 'HR_78076_P11D_DATE_PARAM_ERR');
797 fnd_message.set_token('NAME', 'Benefit Start Date');
798 fnd_message.set_token('VAL1', '06-04-' ||to_char(to_number(l_rep_run)-1));
799 fnd_message.set_token('VAL2', '05-04-' ||l_rep_run);
800 fnd_file.put_line(fnd_file.LOG,fnd_message.get);
801 hr_utility.raise_error;
802 End if;
803
804 If fnd_date.canonical_to_date(l_benefit_end_date) <
805 to_date('06-04-' ||to_char(to_number(l_rep_run)-1),'dd-mm-yyyy') or
806 fnd_date.canonical_to_date(l_benefit_end_date) >
807 to_date('05-04-' ||l_rep_run,'dd-mm-yyyy')
808 then
809 pay_core_utils.push_message(800, 'HR_78076_P11D_DATE_PARAM_ERR', 'F');
810 pay_core_utils.push_token('NAME', 'Benefit End Date');
811 pay_core_utils.push_token('VAl1','06-04-' ||to_char(to_number(l_rep_run)-1));
812 pay_core_utils.push_token('VAl2', '05-04-' ||l_rep_run);
813
814 fnd_message.set_name('PER', 'HR_78076_P11D_DATE_PARAM_ERR');
815 fnd_message.set_token('NAME', 'Benefit End Date');
816 fnd_message.set_token('VAL1', '06-04-' ||to_char(to_number(l_rep_run)-1));
817 fnd_message.set_token('VAL2', '05-04-' ||l_rep_run);
818 fnd_file.put_line(fnd_file.LOG,fnd_message.get);
819 hr_utility.raise_error;
820 End if;
821 hr_utility.set_location('Step '|| l_proc, 20);
822 hr_utility.set_location('l_benefit_start_date = '|| l_benefit_start_date, 20);
823 hr_utility.set_location('l_benefit_end_date = '|| l_benefit_end_date, 20);
824 hr_utility.set_location('l_business_group_id = '|| l_business_group_id, 20);
825 hr_utility.set_location('l_tax_reference = '|| l_tax_reference, 20);
826 hr_utility.set_location('l_consolidation_set_id = '|| l_consolidation_set_id, 20);
827 hr_utility.set_location('l_payroll_id = '|| l_payroll_id, 20);
828 hr_utility.set_location('l_person_id = '|| l_person_id, 20);
829 hr_utility.set_location('l_assignment_set_id = '|| l_assignment_set_id, 20);
830 hr_utility.set_location('Before the cursor assignment id ', 30);
831 -- removed for bug 6278134
832 /* open csr_flex_id(l_tax_reference);
833 fetch csr_flex_id into l_flex_id;
834 close csr_flex_id; */
835
836 open csr_check_asg_set(l_assignment_set_id);
837 fetch csr_check_asg_set into l_count;
838 close csr_check_asg_set;
839
840 open csr_check_payroll(l_assignment_set_id);
841 fetch csr_check_payroll into l_set_payroll_id;
842 close csr_check_payroll;
843 -- if it is empty assignment set contains no amendments,
844 -- check if the payroll is defined on the assignment set or not
845 -- if no payroll, then treated this as normal case run
846 if l_count < 1 then
847 if l_set_payroll_id is null then
848 l_assignment_set_id := null;
849 else
850 -- set payroll id is not null, check if it matches the param payroll id
851 -- if payroll id = set payroll id, then run as a normal payroll
852 if nvl(l_payroll_id,l_set_payroll_id) = l_set_payroll_id then
853 l_assignment_set_id := null;
854 l_payroll_id := l_set_payroll_id;
855 else -- incoming payroll <> to set payroll, don't do archive
856 l_archive := false;
857 end if;
858 end if;
859 else
860 -- incoming payroll <> to set payroll, don't do archive
861 if l_set_payroll_id is not null and
862 nvl(l_payroll_id, l_set_payroll_id) <> l_set_payroll_id then
863 l_archive := false;
864 end if;
865 end if;
866 --
867 if l_archive then
868 --bug 7122883 removed below for loop
869 -- for r_flex_id in csr_flex_id
870 -- loop
871 if l_assignment_set_id is not null then
872 -- bug 6278134 fetching assignment set type for assignment set id
873 OPEN csr_set_type(l_assignment_set_id);
874 FETCH csr_set_type into l_set_type;
875 CLOSE csr_set_type;
876 --bug 6278134 added check for Include and Exclude conditions
877 IF l_set_type = 'I' then
878 FOR csr_rec IN csr_assign_set_X
879 LOOP
880 hr_utility.TRACE(' l_prev_assignment_id '|| l_prev_assignment_id);
881 hr_utility.TRACE(' csr_rec.assignment_id '|| csr_rec.assignment_id);
882 if check_assignment_tax_ref(csr_rec.assignment_id,l_tax_reference,l_benefit_end_date) then
883 if l_prev_assignment_id IS NULL OR l_prev_assignment_id <> csr_rec.assignment_id
884 THEN
885 -- CREATE THE ARCHIVE ASSIGNMENT ACTION FOR THE MASTER ASSIGNMENT ACTION
886 SELECT pay_assignment_actions_s.NEXTVAL
887 INTO l_actid
888 FROM DUAL;
889
890 hr_utility.set_location('Archive assignment Action ', 30);
891 hr_nonrun_asact.insact(l_actid, csr_rec.assignment_id, pactid, CHUNK, NULL);
892 l_prev_assignment_id := csr_rec.assignment_id;
893 hr_utility.TRACE(' Created Assignment action ');
894 hr_utility.TRACE(' csr_rec.assignment_id '|| csr_rec.assignment_id);
895 END IF;
896
897 g_ben_asg_count := g_ben_asg_count + 1;
898 hr_utility.set_location('Inside the cursor assignment id ', 30);
899 g_tab_ben_detail(g_ben_asg_count).assignment_action_id := l_actid;
900 g_tab_ben_detail(g_ben_asg_count).element_type_id := csr_rec.element_type_id;
901 g_tab_ben_detail(g_ben_asg_count).element_entry_id := csr_rec.element_entry_id;
902 g_tab_ben_detail(g_ben_asg_count).element_name := csr_rec.element_name;
903 g_tab_ben_detail(g_ben_asg_count).effective_start_date := csr_rec.effective_start_date;
904 g_tab_ben_detail(g_ben_asg_count).person_id := csr_rec.person_id;
905 g_tab_ben_detail(g_ben_asg_count).assignment_id := csr_rec.assignment_id;
906 g_tab_ben_detail(g_ben_asg_count).classification_name := csr_rec.classification_name;
907 end if;
908 END LOOP;
909 ELSIF l_set_type = 'E' then
910 FOR csr_rec IN csr_assign_set_EX
911 LOOP
912 hr_utility.TRACE(' l_prev_assignment_id '|| l_prev_assignment_id);
913 hr_utility.TRACE(' csr_rec.assignment_id '|| csr_rec.assignment_id);
914 if check_assignment_tax_ref(csr_rec.assignment_id,l_tax_reference,l_benefit_end_date) then
915 if l_prev_assignment_id IS NULL OR l_prev_assignment_id <> csr_rec.assignment_id
916 THEN
917 -- CREATE THE ARCHIVE ASSIGNMENT ACTION FOR THE MASTER ASSIGNMENT ACTION
918 SELECT pay_assignment_actions_s.NEXTVAL
919 INTO l_actid
920 FROM DUAL;
921
922 hr_utility.set_location('Archive assignment Action ', 30);
923 hr_nonrun_asact.insact(l_actid, csr_rec.assignment_id, pactid, CHUNK, NULL);
924 l_prev_assignment_id := csr_rec.assignment_id;
925 hr_utility.TRACE(' Created Assignment action ');
926 hr_utility.TRACE(' csr_rec.assignment_id '|| csr_rec.assignment_id);
927 END IF;
928
929 g_ben_asg_count := g_ben_asg_count + 1;
930 hr_utility.set_location('Inside the cursor assignment id ', 30);
931 g_tab_ben_detail(g_ben_asg_count).assignment_action_id := l_actid;
932 g_tab_ben_detail(g_ben_asg_count).element_type_id := csr_rec.element_type_id;
933 g_tab_ben_detail(g_ben_asg_count).element_entry_id := csr_rec.element_entry_id;
934 g_tab_ben_detail(g_ben_asg_count).element_name := csr_rec.element_name;
935 g_tab_ben_detail(g_ben_asg_count).effective_start_date := csr_rec.effective_start_date;
936 g_tab_ben_detail(g_ben_asg_count).person_id := csr_rec.person_id;
937 g_tab_ben_detail(g_ben_asg_count).assignment_id := csr_rec.assignment_id;
938 g_tab_ben_detail(g_ben_asg_count).classification_name := csr_rec.classification_name;
939 end if;
940 END LOOP;
941 END IF;
942 else
943 FOR csr_rec in csr_noassign_set
944 LOOP
945 hr_utility.TRACE(' l_prev_assignment_id '|| l_prev_assignment_id);
946 hr_utility.TRACE(' csr_rec.assignment_id '|| csr_rec.assignment_id);
947 if check_assignment_tax_ref(csr_rec.assignment_id,l_tax_reference,l_benefit_end_date) then
948 IF l_prev_assignment_id IS NULL OR l_prev_assignment_id <> csr_rec.assignment_id
949 THEN
950 -- CREATE THE ARCHIVE ASSIGNMENT ACTION FOR THE MASTER ASSIGNMENT ACTION
951 SELECT pay_assignment_actions_s.NEXTVAL
952 INTO l_actid
953 FROM DUAL;
954 hr_utility.set_location('Archive assignment Action ', 30);
955 hr_nonrun_asact.insact(l_actid, csr_rec.assignment_id, pactid, CHUNK, NULL);
956 l_prev_assignment_id := csr_rec.assignment_id;
957 hr_utility.TRACE(' Created Assignment action ');
958 hr_utility.TRACE(' csr_rec.assignment_id '|| csr_rec.assignment_id);
959 END IF;
960
961 g_ben_asg_count := g_ben_asg_count + 1;
962 hr_utility.set_location('Inside the cursor assignment id ', 30);
963 g_tab_ben_detail(g_ben_asg_count).assignment_action_id := l_actid;
964 g_tab_ben_detail(g_ben_asg_count).element_type_id := csr_rec.element_type_id;
965 g_tab_ben_detail(g_ben_asg_count).element_entry_id := csr_rec.element_entry_id;
966 g_tab_ben_detail(g_ben_asg_count).element_name := csr_rec.element_name;
967 g_tab_ben_detail(g_ben_asg_count).effective_start_date := csr_rec.effective_start_date;
968 g_tab_ben_detail(g_ben_asg_count).person_id := csr_rec.person_id;
969 g_tab_ben_detail(g_ben_asg_count).assignment_id := csr_rec.assignment_id;
970 g_tab_ben_detail(g_ben_asg_count).classification_name := csr_rec.classification_name;
971 end if;
972 END LOOP;
973 end if;
974 -- END LOOP;
975 --end of bug 6278134
976 end if; -- end l_archive
977 hr_utility.set_location('Leaving '|| l_proc, 20);
978 END action_creation;
979
980 PROCEDURE archive_code(p_assactid IN NUMBER, p_effective_date IN DATE)
981 IS
982 l_actual_termination_date date;
983
984 CURSOR csr_assignment_det(p_assignment_id NUMBER, p_tax_ref VARCHAR2)
985 IS
986 SELECT pap.last_name || ' ' || pap.first_name,
987 paa.payroll_id,
988 NVL(pap.per_information2, 'N'),
989 pap.first_name,
990 pap.middle_names,
991 pap.last_name,
992 nvl(paa.ASSIGNMENT_NUMBER,pap.employee_number),
993 pap.person_id,
994 pap.national_identifier,
995 pap.sex,
996 pap.date_of_birth
997 FROM per_all_assignments_f paa,
998 per_all_people_f pap,
999 per_periods_of_service pps,
1000 pay_all_payrolls_f pay,
1001 hr_soft_coding_keyflex flex
1002 WHERE paa.person_id = pap.person_id
1003 AND pps.PERIOD_OF_SERVICE_ID(+) = paa.PERIOD_OF_SERVICE_ID
1004 AND least(nvl(pps.ACTUAL_TERMINATION_DATE,fnd_date.canonical_to_date(g_param_benefit_end_date)),
1005 fnd_date.canonical_to_date(g_param_benefit_end_date))
1006 BETWEEN pap.effective_start_date AND pap.effective_end_date
1007 AND paa.assignment_id = p_assignment_id
1008 AND paa.payroll_id = pay.payroll_id
1009 AND least(fnd_date.canonical_to_date(g_param_benefit_end_date), paa.effective_end_date)
1010 between pay.effective_start_date and pay.effective_end_date
1011 AND pay.soft_coding_keyflex_id + 0 = flex.soft_coding_keyflex_id
1012 AND (p_tax_ref is null
1013 OR
1014 flex.segment1 = p_tax_ref)
1015 AND (fnd_date.canonical_to_date(g_param_benefit_end_date) between paa.effective_start_date AND paa.effective_end_date
1016 OR
1017 (
1018 paa.effective_end_date = (select max(paa2.effective_end_date)
1019 from per_assignments_f paa2
1020 where paa2.assignment_id = p_assignment_id)
1021 and paa.effective_end_date < fnd_date.canonical_to_date(g_param_benefit_end_date))
1022 );
1023
1024 CURSOR assignments_to_sum(p_person_id NUMBER, p_emp_ref VARCHAR2, p_emp_name VARCHAR2)
1025 IS
1026 SELECT paa.assignment_action_id, pai_person.action_information1, pai_comp.action_information6,
1027 pai_comp.action_information7
1028 FROM pay_action_information pai_comp,
1029 pay_action_information pai_person,
1030 pay_assignment_actions paa,
1031 pay_payroll_actions ppa
1032 WHERE ppa.payroll_action_id = g_pactid
1033 AND paa.payroll_action_id = ppa.payroll_action_id
1034 AND pai_comp.action_context_id = paa.assignment_action_id
1035 AND pai_comp.action_information_category = 'EMEA PAYROLL INFO'
1036 AND pai_person.action_context_id = paa.assignment_action_id
1037 AND pai_person.action_information_category = 'ADDRESS DETAILS'
1038 AND pai_person.action_information14 = 'Employee Address'
1039 AND pai_person.action_information1 = p_person_id
1040 AND pai_comp.action_information6 = p_emp_ref
1041 AND pai_comp.action_information7 = p_emp_name;
1042
1043 CURSOR csr_val_element_entry_id(v_assactid pay_assignment_actions.assignment_action_id%TYPE,
1044 v_benefit_start_date VARCHAR2,
1045 v_benefit_end_date VARCHAR2)
1046 IS
1047 SELECT DISTINCT pet.element_type_id element_type_id, peev.element_entry_id element_entry_id,
1048 pet.element_name element_name, peev.effective_start_date effective_start_date, paa.person_id,
1049 paa.assignment_id, UPPER('GB_'|| pec.classification_name) classification_name
1050 FROM pay_element_types_f pet,
1051 pay_element_classifications pec,
1052 pay_input_values_f piv,
1053 pay_element_entry_values_f peev,
1054 pay_element_entries_f pee,
1055 per_assignments_f paa,
1056 pay_assignment_actions paac
1057 WHERE pet.classification_id = pec.classification_id AND
1058 pet.element_type_id = piv.element_type_id AND
1059 piv.input_value_id = peev.input_value_id AND
1060 pee.element_entry_id = peev.element_entry_id AND
1061 pee.assignment_id = paac.assignment_id AND
1062 paa.assignment_id = paac.assignment_id AND
1063 paac.assignment_action_id = v_assactid AND
1064 pec.classification_name LIKE 'EXTERNAL REPORTING%' AND
1065 (piv.NAME = 'Benefit Start Date' OR piv.NAME = 'Benefit End Date') AND
1066 pee.assignment_id = paa.assignment_id AND
1067 peev.screen_entry_value BETWEEN v_benefit_start_date AND v_benefit_end_date
1068 ORDER BY pet.element_type_id, peev.element_entry_id, peev.effective_start_date;
1069
1070
1071 CURSOR csr_element_entry_values(v_element_entry_id pay_element_entry_values_f.element_entry_id%TYPE,
1072 v_element_type_id pay_element_types_f.element_type_id%TYPE,
1073 v_effective_start_date DATE)
1074 IS
1075 SELECT peev.screen_entry_value, UPPER(TRANSLATE(piv.NAME, ' ', '_') ) NAME
1076 FROM pay_input_values_f piv, pay_element_entry_values_f peev
1077 WHERE piv.input_value_id = peev.input_value_id AND
1078 piv.element_type_id = v_element_type_id AND
1079 peev.element_entry_id = v_element_entry_id AND
1080 peev.effective_start_date = v_effective_start_date;
1081
1082
1083 CURSOR csr_element_entry_flex_values(v_classification_name VARCHAR2)
1084 IS
1085 SELECT application_column_name, UPPER(TRANSLATE(end_user_column_name, ' ', '_') ) NAME
1086 FROM fnd_descr_flex_col_usage_vl
1087 WHERE application_id = g_application_id AND
1088 descriptive_flexfield_name = 'Element Entry Developer DF'AND
1089 descriptive_flex_context_code = v_classification_name AND
1090 (enabled_flag IS NULL OR enabled_flag ='Y');
1091
1092 CURSOR csr_action_info_flex_fields(v_element_name VARCHAR2)
1093 IS
1094 SELECT application_column_name application_column_name, UPPER(TRANSLATE(end_user_column_name, ' ', '_') ) NAME
1095 FROM fnd_descr_flex_col_usage_vl
1096 WHERE application_id = g_application_id AND
1097 descriptive_flexfield_name = 'Action Information DF' AND
1098 descriptive_flex_context_code = v_element_name AND
1099 (enabled_flag IS NULL OR enabled_flag = 'Y');
1100
1101 Cursor csr_non_iv_action_info_items(v_element_entry_id pay_element_entry_values_f.element_entry_id%TYPE,
1102 v_element_type_id pay_element_types_f.element_type_id%TYPE,
1103 v_effective_start_date DATE,
1104 v_element_name VARCHAR2,
1105 v_classification_name VARCHAR2)
1106 is
1107 SELECT UPPER(TRANSLATE(flex_act.end_user_column_name, ' ', '_') ) NAME
1108 FROM fnd_descr_flex_col_usage_vl flex_act
1109 WHERE flex_act.application_id = g_application_id AND
1110 flex_act.descriptive_flexfield_name = 'Action Information DF' AND
1111 flex_act.descriptive_flex_context_code = v_element_name AND
1112 (flex_act.enabled_flag IS NULL OR flex_act.enabled_flag = 'Y') and
1113 not exists ( select /*+ no_unnest */ 1
1114 from
1115 fnd_descr_flex_col_usage_vl flex_ele
1116 where
1117 flex_ele.application_id = g_application_id AND
1118 flex_ele.descriptive_flexfield_name = 'Element Entry Developer DF' AND
1119 flex_ele.descriptive_flex_context_code = v_classification_name AND
1120 (flex_ele.enabled_flag IS NULL OR flex_ele.enabled_flag ='Y') AND
1121 flex_ele.end_user_column_name = flex_act.end_user_column_name ) AND
1122 not Exists (
1123 SELECT /*+ no_unnest */ 1
1124 FROM pay_input_values_f piv,
1125 pay_element_entry_values_f peev
1126 WHERE piv.input_value_id = peev.input_value_id AND
1127 piv.element_type_id = v_element_type_id AND
1128 peev.element_entry_id = v_element_entry_id AND
1129 peev.effective_start_date = v_effective_start_date AND
1130 UPPER(TRANSLATE(substr(piv.NAME,1,30),' ', '_') ) =
1131 UPPER(TRANSLATE(flex_act.end_user_column_name, ' ', '_')));
1132
1133 cursor csr_get_global(p_name varchar2,
1134 p_date date)
1135 is
1136 select to_number(global_value)
1137 from ff_globals_f
1138 where global_name = p_name
1139 and legislation_code = 'GB'
1140 and p_date between effective_start_date and effective_end_date;
1141
1142 cursor csr_get_term_date (p_assignment_id Number)
1143 is
1144 select ACTUAL_TERMINATION_DATE
1145 from per_periods_of_service pps,
1146 per_assignments_f paf
1147 where paf.PERIOD_OF_SERVICE_ID = pps.PERIOD_OF_SERVICE_ID
1148 and paf.assignment_id =p_assignment_id;
1149
1150 cursor csr_get_asg_end_date(p_assignment_id number)
1151 is
1152 select max(effective_end_date)
1153 from per_all_assignments_f
1154 where assignment_id = p_assignment_id;
1155
1156 l_element_name pay_element_types_f.element_name%TYPE;
1157 l_formula_id ff_formulas_f.formula_id%TYPE;
1158 l_formula_effective_start_date DATE;
1159 l_inputs ff_exec.inputs_t;
1160 l_user_inputs ff_exec.inputs_t;
1161 l_outputs ff_exec.outputs_t;
1162 l_counter NUMBER;
1163 l_assignment_id NUMBER;
1164 l_loan_threshold NUMBER;
1165 l_asg_max_end_date DATE;
1166 l_benefit_end_date VARCHAR2(20);
1167 l_benefit_start_date VARCHAR2(20); -- this used ot store the
1168 -- values of the p11d elements
1169 l_payroll_id NUMBER;
1170 l_pactid NUMBER;
1171 l_action_context_id NUMBER;
1172 l_action_info_id NUMBER(15);
1173 l_ovn NUMBER;
1174 error_found EXCEPTION;
1175 l_error VARCHAR2(10);
1176 l_sqlstr VARCHAR2(28000);
1177 l_sql_stmt VARCHAR2(1000);
1178 l_assignment_name VARCHAR2(100);
1179 l_assignment_number VARCHAR2(30);
1180 l_director_flag VARCHAR2(150);
1181 l_first_name VARCHAR2(150);
1182 l_middle_name VARCHAR2(150);
1183 l_last_name VARCHAR2(150);
1184 l_date_of_birth DATE;
1185 l_sex VARCHAR2(10);
1186 l_person_id NUMBER;
1187 l_ni_number VARCHAR2(12);
1188 l_proc VARCHAR2(50) := g_package || 'archive_code';
1189 l_edi_validation VARCHAR2(10);
1190 l_error_assignment BOOLEAN;
1191 l_index NUMBER(15);
1192
1193 TYPE t_error_rec IS RECORD(
1194 error_text VARCHAR2(2000),
1195 error_assignment_id NUMBER,
1196 error_assignment_number varchar2(30),
1197 error_assignment_name VARCHAR2(100),
1198 error_element_name VARCHAR2(100),
1199 error_element_entry_id NUMBER,
1200 error_ben_st_date VARCHAR2(20),
1201 error_ben_end_date VARCHAR2(20),
1202 error_type VARCHAR2(2)
1203 );
1204
1205
1206 TYPE t_error_msgs IS TABLE OF t_error_rec
1207 INDEX BY BINARY_INTEGER;
1208
1209 l_val_errors t_error_msgs;
1210 l_val_error_count INTEGER DEFAULT 0;
1211
1212 TYPE l_rec_pay_info IS RECORD(
1213 l_employers_ref_no VARCHAR2(150),
1214 l_tax_office_name VARCHAR2(150),
1215 l_tax_office_phone_no VARCHAR2(150),
1216 l_employer_name VARCHAR2(150),
1217 l_employer_address VARCHAR2(150) );
1218
1219 TYPE l_typ_pay_info_table IS TABLE OF l_rec_pay_info
1220 INDEX BY BINARY_INTEGER;
1221
1222 l_pay_info_tab l_typ_pay_info_table;
1223
1224 payroll_not_found EXCEPTION;
1225 tax_office_name_error EXCEPTION;
1226 employer_address_error EXCEPTION;
1227 employers_ref_no_error EXCEPTION;
1228 employer_name_error EXCEPTION;
1229 -- defining variables to use in dyn sql
1230 l_col1_val VARCHAR2(240);
1231 l_col2_val VARCHAR2(240);
1232 l_col3_val VARCHAR2(240);
1233 l_col4_val VARCHAR2(240);
1234 l_col5_val VARCHAR2(240);
1235 l_col6_val VARCHAR2(240);
1236 l_col7_val VARCHAR2(240);
1237 l_col8_val VARCHAR2(240);
1238 l_col9_val VARCHAR2(240);
1239 l_col10_val VARCHAR2(240);
1240 l_col11_val VARCHAR2(240);
1241 l_col12_val VARCHAR2(240);
1242 l_col13_val VARCHAR2(240);
1243 l_col14_val VARCHAR2(240);
1244 l_col15_val VARCHAR2(240);
1245 l_col16_val VARCHAR2(240);
1246 l_col17_val VARCHAR2(240);
1247 l_col18_val VARCHAR2(240);
1248 l_col19_val VARCHAR2(240);
1249 l_col20_val VARCHAR2(240);
1250 l_col21_val VARCHAR2(240);
1251 l_col22_val VARCHAR2(240);
1252 l_col23_val VARCHAR2(240);
1253 l_col24_val VARCHAR2(240);
1254 l_col25_val VARCHAR2(240);
1255 l_col26_val VARCHAR2(240);
1256 l_col27_val VARCHAR2(240);
1257 l_col28_val VARCHAR2(240);
1258 l_col29_val VARCHAR2(240);
1259 l_col30_val VARCHAR2(240);
1260 l_dyn_sql_ele_name_param VARCHAR2(30);
1261 l_ret INTEGER;
1262 l_warn number;
1263
1264 l_first_index Number;
1265 l_first_index_set Boolean;
1266
1267 --
1268 TYPE l_typ_processed_assign_actions IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1269
1270 l_processed_assign_actions l_typ_processed_assign_actions;
1271 c_proc_assign_actions_null l_typ_processed_assign_actions;
1272 l_tab_counter NUMBER;
1273 l_extra_items_count Number :=0;
1274
1275 --
1276 FUNCTION find_lowest_matching_index RETURN INTEGER
1277 IS
1278 l_lower INTEGER;
1279 l_upper INTEGER;
1280 l_check_item INTEGER;
1281 l_first_matching_item INTEGER := 0;
1282 l_match BOOLEAN := FALSE;
1283 BEGIN
1284 hr_utility.trace('Inside find_lowest_matching_index ');
1285
1286 l_lower := 1;
1287 l_upper := g_tab_ben_detail.COUNT;
1288
1289 FOR counter IN l_lower .. l_upper
1290 LOOP
1291 l_check_item := FLOOR( (l_lower + l_upper) / 2);
1292
1293 IF g_tab_ben_detail(l_check_item).assignment_action_id = p_assactid
1294 THEN
1295 l_match := TRUE;
1296 hr_utility.TRACE('.. MATCHED..');
1297 EXIT;
1298 ELSIF p_assactid < g_tab_ben_detail(l_check_item).assignment_action_id
1299 THEN
1300 -- search below this
1301 l_upper := l_check_item - 1;
1302 ELSE
1303 l_lower := l_check_item + 1;
1304 END IF;
1305 END LOOP;
1306
1307 IF l_match
1308 THEN
1309 -- it returned the match, due to multiple emtries
1310 -- there could be rows for same p_assactid before the matched row
1311 -- we need to find them
1312 IF l_check_item = 1
1313 THEN
1314 l_first_matching_item := l_check_item;
1315 ELSE
1316 FOR counter IN REVERSE 1 .. l_check_item
1317 LOOP
1318 IF g_tab_ben_detail(counter).assignment_action_id = p_assactid
1319 THEN
1320 -- item matches and counter is 1 menaing the first item
1321 IF counter = 1
1322 THEN
1323 l_first_matching_item := counter;
1324 END IF;
1325 ELSE -- item does not match meaning the match first is counter +1
1326 l_first_matching_item := counter + 1;
1327 EXIT;
1328 END IF;
1329 END LOOP;
1330 END IF; -- end of l_check_item = 1
1331 END IF;-- end of l_match
1332 hr_utility.TRACE(' Returning l_first_matching_item '|| l_first_matching_item);
1333 RETURN l_first_matching_item;
1334 Exception
1335 when others then
1336 l_first_matching_item := 0;
1337 RETURN l_first_matching_item;
1338 END;
1339
1340 --
1341 PROCEDURE populate_payroll_info(p_end_date VARCHAR2, p_payroll_id NUMBER)
1342 AS
1343 BEGIN
1344 hr_utility.TRACE('InsidePopulate_Payroll_info');
1345 hr_utility.TRACE('p_payroll_id '|| p_payroll_id);
1346 hr_utility.TRACE('p_end_date '|| p_end_date);
1347 -- end date is constant in the Archiver run
1348 -- hence we do nt need to worry about
1349 -- multiple recrds for a payroll id as date is fixed!
1350
1351 -- check if it exists in table
1352 -- if not fetch it and add to table
1353 OPEN csr_payroll_info(p_end_date, p_payroll_id);
1354 FETCH csr_payroll_info INTO l_pay_info_tab(p_payroll_id).l_employers_ref_no,
1355 l_pay_info_tab(p_payroll_id).l_tax_office_name,
1356 l_pay_info_tab(p_payroll_id).l_tax_office_phone_no,
1357 l_pay_info_tab(p_payroll_id).l_employer_name,
1358 l_pay_info_tab(p_payroll_id).l_employer_address;
1359
1360 IF csr_payroll_info%NOTFOUND
1361 THEN
1362 hr_utility.set_location('payroll info not found: ', 30);
1363 CLOSE csr_payroll_info;
1364 RAISE payroll_not_found;
1365 ELSE
1366 CLOSE csr_payroll_info;
1367 hr_utility.set_location('Archiving Payroll info', 35);
1368 hr_utility.TRACE(' Found InsidePopulate_Payroll_info');
1369
1370 IF pay_gb_eoy_magtape.validate_input(UPPER(l_pay_info_tab(p_payroll_id).l_tax_office_name) ) > 0
1371 THEN
1372 -- fnd_file.put_line(fnd_file.output,'Tax Office Name contains illegal character(s) :' || l_tax_office_name );
1373 RAISE tax_office_name_error;
1374 END IF;
1375
1376 IF pay_gb_eoy_magtape.validate_input(UPPER(l_pay_info_tab(p_payroll_id).l_employer_address),'P11D_EDI' ) > 0
1377 THEN
1378 -- fnd_file.put_line(fnd_file.output,'Employers Address contains illegal character(s) :' || l_employer_address );
1379 RAISE employer_address_error;
1380 END IF;
1381
1382 IF pay_gb_eoy_magtape.validate_input(UPPER(l_pay_info_tab(p_payroll_id).l_employers_ref_no) ) > 0
1383 THEN
1384 -- fnd_file.put_line(fnd_file.output,'Employers Reference Number contains illegal character(s) :' || l_employers_ref_no);
1385 RAISE employers_ref_no_error;
1386 END IF;
1387
1388 IF pay_gb_eoy_magtape.validate_input(to_number(substr(l_pay_info_tab(p_payroll_id).l_employers_ref_no,1,3)),'NUMBER') > 0
1389 THEN
1390 RAISE employers_ref_no_error;
1391 END IF;
1392
1393 IF pay_gb_eoy_magtape.validate_input(UPPER(l_pay_info_tab(p_payroll_id).l_employer_name),'P11D_EDI' ) > 0
1394 THEN
1395 -- fnd_file.put_line(fnd_file.output,'Employers Name contains illegal character(s) :' || l_employer_name );
1396 RAISE employer_name_error;
1397 END IF;
1398 END IF;
1399 END;
1400
1401 Function calculate_amap_ce
1402 return number
1403 is
1404 l_C_BUS_MILES Number;
1405 l_M_BUS_MILES Number;
1406 l_B_BUS_MILES Number;
1407 l_C_RATE1 Number;
1408 l_C_RATE2 Number;
1409 l_M_RATE1 Number;
1410 l_M_RATE2 Number;
1411 l_B_RATE1 Number;
1412 l_B_RATE2 Number;
1413 l_C_MILEAGE_PAYMENTS Number;
1414 l_B_MILEAGE_PAYMENTS Number;
1415 l_M_MILEAGE_PAYMENTS Number;
1416 l_C_TAX_DEDUCTED Number;
1417 l_B_TAX_DEDUCTED Number;
1418 l_M_TAX_DEDUCTED Number;
1419 l_PASSENGER_PAYMENTS Number;
1420 l_PASSENGER_BUS_MILES Number;
1421 l_PASSENGER_BUS_MILE_AMT Number;
1422
1423 l_c_net_allowance Number;
1424 l_c_tot_approved_payments Number;
1425 l_c_taxable_payment Number;
1426 l_b_net_allowance Number;
1427 l_b_tot_approved_payments Number;
1428 l_b_taxable_payment Number;
1429 l_m_net_allowance Number;
1430 l_m_tot_approved_payments Number;
1431 l_m_taxable_payment Number;
1432 l_taxable_pass_payment Number;
1433 l_ce Number;
1434 begin
1435 -- hr_utility.trace_on(null,'AMAP');
1436 l_C_BUS_MILES := nvl(per_formula_functions.get_number('C_BUS_MILES'),0);
1437 l_M_BUS_MILES := nvl(per_formula_functions.get_number('M_BUS_MILES'),0);
1438 l_B_BUS_MILES := nvl(per_formula_functions.get_number('B_BUS_MILES'),0);
1439 l_C_RATE1 := nvl(per_formula_functions.get_number('C_RATE1'),0);
1440 l_C_RATE2 := nvl(per_formula_functions.get_number('C_RATE2'),0);
1441 l_M_RATE1 := nvl(per_formula_functions.get_number('M_RATE1'),0);
1442 l_M_RATE2 := nvl(per_formula_functions.get_number('M_RATE2'),0);
1443 l_B_RATE1 := nvl(per_formula_functions.get_number('B_RATE1'),0);
1444 l_B_RATE2 := nvl(per_formula_functions.get_number('B_RATE2'),0);
1445 l_C_MILEAGE_PAYMENTS := nvl(per_formula_functions.get_number('C_MILEAGE_PAYMENTS'),0);
1446 l_B_MILEAGE_PAYMENTS := nvl(per_formula_functions.get_number('B_MILEAGE_PAYMENTS'),0);
1447 l_M_MILEAGE_PAYMENTS := nvl(per_formula_functions.get_number('M_MILEAGE_PAYMENTS'),0);
1448 l_C_TAX_DEDUCTED := nvl(per_formula_functions.get_number('C_TAX_DEDUCTED'),0);
1449 l_B_TAX_DEDUCTED := nvl(per_formula_functions.get_number('B_TAX_DEDUCTED'),0);
1450 l_M_TAX_DEDUCTED := nvl(per_formula_functions.get_number('M_TAX_DEDUCTED'),0);
1451 l_PASSENGER_PAYMENTS := nvl(per_formula_functions.get_number('PASSENGER_PAYMENTS'),0);
1452 l_PASSENGER_BUS_MILES := nvl(per_formula_functions.get_number('PASSENGER_BUS_MILES'),0);
1453 l_PASSENGER_BUS_MILE_AMT := nvl(per_formula_functions.get_number('PASSENGER_BUS_MILE_AMT'),0);
1454
1455 l_c_net_allowance := l_C_MILEAGE_PAYMENTS - l_C_TAX_DEDUCTED;
1456 if l_c_net_allowance < 0
1457 then
1458 l_c_net_allowance := 0;
1459 end if;
1460 hr_utility.trace(' l_c_net_allowance ' || l_c_net_allowance);
1461
1462 if l_C_BUS_MILES > 10000 then
1463 l_c_tot_approved_payments := (10000 * l_C_RATE1) +
1464 (
1465 (l_C_BUS_MILES - 10000) * l_C_RATE2
1466 );
1467 else
1468 l_c_tot_approved_payments := l_C_BUS_MILES * l_C_RATE1;
1469 end if;
1470 l_c_taxable_payment := l_c_net_allowance - l_c_tot_approved_payments;
1471 if l_c_taxable_payment < 0 then
1472 l_c_taxable_payment := 0;
1473 end if;
1474
1475 hr_utility.trace(' l_c_taxable_payment ' || l_c_taxable_payment);
1476
1477 l_b_net_allowance := l_B_MILEAGE_PAYMENTS - l_B_TAX_DEDUCTED;
1478 if l_b_net_allowance < 0
1479 then
1480 l_b_net_allowance := 0;
1481 end if;
1482 if l_B_BUS_MILES > 10000 then
1483 l_b_tot_approved_payments := (10000 * l_B_RATE1) +
1484 (
1485 (l_B_BUS_MILES - 10000) * l_B_RATE2
1486 );
1487 else
1488 l_b_tot_approved_payments := l_B_BUS_MILES * l_B_RATE1;
1489 end if;
1490 l_b_taxable_payment := l_b_net_allowance - l_b_tot_approved_payments;
1491 if l_b_taxable_payment < 0 then
1492 l_b_taxable_payment := 0;
1493 end if;
1494 --
1495 l_m_net_allowance := l_M_MILEAGE_PAYMENTS - l_M_TAX_DEDUCTED;
1496 if l_m_net_allowance < 0
1497 then
1498 l_m_net_allowance := 0;
1499 end if;
1500 if l_M_BUS_MILES > 10000 then
1501 l_m_tot_approved_payments := (10000 * l_M_RATE1) +
1502 (
1503 (l_M_BUS_MILES - 10000) * l_M_RATE2
1504 );
1505 else
1506 l_m_tot_approved_payments := l_M_BUS_MILES * l_M_RATE1;
1507 end if;
1508 l_m_taxable_payment := l_m_net_allowance - l_m_tot_approved_payments;
1509 if l_m_taxable_payment < 0 then
1510 l_m_taxable_payment := 0;
1511 end if;
1512
1513 l_taxable_pass_payment := l_PASSENGER_PAYMENTS - l_PASSENGER_BUS_MILE_AMT;
1514 if l_taxable_pass_payment < 0 then
1515 l_taxable_pass_payment := 0;
1516 end if;
1517 /* bug 7201761 rounded to 2 decimals to avoid HRMC rejection */
1518 l_ce := round(l_taxable_pass_payment + l_c_taxable_payment +
1519 l_b_taxable_payment + l_m_taxable_payment,2);
1520 --
1521 hr_utility.trace(' *************l_ce ************' || l_ce);
1522 -- hr_utility.trace_off;
1523
1524 return l_ce;
1525 end;
1526
1527 PROCEDURE insert_sum_records(p_assactid NUMBER)
1528 IS
1529 BEGIN
1530 if to_number(g_param_rep_run) < 2005
1531 then
1532 pay_action_information_api.create_action_information(
1533 p_action_information_id => l_action_info_id,
1534 p_action_context_id => p_assactid,
1535 p_action_context_type => 'AAP',
1536 p_object_version_number => l_ovn,
1537 p_effective_date => fnd_date.canonical_to_date(g_param_benefit_end_date),
1538 p_source_id => NULL,
1539 p_source_text => NULL,
1540 p_action_information_category => 'GB P11D ASSIGNMENT RESULTA',
1541 p_action_information1 => per_formula_functions.get_text('A_DESC'),
1542 p_action_information2 => per_formula_functions.get_number('A_COST'),
1543 p_action_information3 => per_formula_functions.get_number('A_AMG'),
1544 p_action_information4 => per_formula_functions.get_number('A_CE'),
1545 p_action_information5 => per_formula_functions.get_text('B_DESC'),
1546 p_action_information6 => per_formula_functions.get_number('B_CE'),
1547 p_action_information7 => per_formula_functions.get_number('B_TNP'),
1548 p_action_information8 => per_formula_functions.get_number('C_COST'),
1549 p_action_information9 => per_formula_functions.get_number('C_AMG'),
1550 p_action_information10 => per_formula_functions.get_number('C_CE'),
1551 p_action_information11 => per_formula_functions.get_number('D_CE'),
1552 p_action_information12 => calculate_amap_ce ,
1553 p_action_information13 => per_formula_functions.get_number('F_TCCE'),
1554 p_action_information14 => per_formula_functions.get_number('F_TFCE'),
1555 p_action_information15 => per_formula_functions.get_number('G_CE'),
1556 p_action_information16 => per_formula_functions.get_number('I_COST'),
1557 p_action_information17 => per_formula_functions.get_number('I_AMG'),
1558 p_action_information18 => per_formula_functions.get_number('I_CE'),
1559 p_action_information19 => per_formula_functions.get_number('J_CE'),
1560 p_action_information20 => per_formula_functions.get_number('K_COST'),
1561 p_action_information21 => per_formula_functions.get_number('K_AMG'),
1562 p_action_information22 => per_formula_functions.get_number('K_CE'),
1563 p_action_information23 => per_formula_functions.get_text('L_DESC'),
1564 p_action_information24 => per_formula_functions.get_number('L_COST'),
1565 p_action_information25 => per_formula_functions.get_number('L_AMG'),
1566 p_action_information26 => per_formula_functions.get_number('L_CE'),
1567 p_action_information27 => per_formula_functions.get_text('M_SHARES'),
1568 p_action_information28 => per_formula_functions.get_number('H_CE1'),
1569 p_action_information29 => per_formula_functions.get_number('H_COUNT'),
1570 p_action_information30 => per_formula_functions.get_number('F_COUNT') );
1571
1572 --
1573 pay_action_information_api.create_action_information(
1574 p_action_information_id => l_action_info_id,
1575 p_action_context_id => p_assactid,
1576 p_action_context_type => 'AAP',
1577 p_object_version_number => l_ovn,
1578 p_effective_date => fnd_date.canonical_to_date(g_param_benefit_end_date),
1579 p_source_id => NULL,
1580 p_source_text => NULL,
1581 p_action_information_category => 'GB P11D ASSIGNMENT RESULTB',
1582 p_action_information1 => per_formula_functions.get_text('N_DESC'),
1583 p_action_information2 => per_formula_functions.get_number('N_COST'),
1584 p_action_information3 => per_formula_functions.get_number('N_AMG'),
1585 p_action_information4 => per_formula_functions.get_number('N_CE'),
1586 p_action_information5 => per_formula_functions.get_text('NA_DESC'),
1587 p_action_information6 => per_formula_functions.get_number('NA_COST'),
1588 p_action_information7 => per_formula_functions.get_number('NA_AMG'),
1589 p_action_information8 => per_formula_functions.get_number('NA_CE'),
1590 p_action_information9 => per_formula_functions.get_number('N_TAXPAID'),
1591 p_action_information10 => per_formula_functions.get_number('O1_COST'),
1592 p_action_information11 => per_formula_functions.get_number('O1_AMG'),
1593 p_action_information12 => per_formula_functions.get_number('O1_CE'),
1594 p_action_information13 => per_formula_functions.get_number('O2_COST'),
1595 p_action_information14 => per_formula_functions.get_number('O2_AMG'),
1596 p_action_information15 => per_formula_functions.get_number('O2_CE'),
1597 p_action_information16 => per_formula_functions.get_text('O_TOI'),
1598 p_action_information17 => per_formula_functions.get_number('O3_COST'),
1599 p_action_information18 => per_formula_functions.get_number('O3_AMG'),
1600 p_action_information19 => per_formula_functions.get_number('O3_CE'),
1601 p_action_information20 => per_formula_functions.get_number('O4_COST'),
1602 p_action_information21 => per_formula_functions.get_number('O4_AMG'),
1603 p_action_information22 => per_formula_functions.get_number('O4_CE'),
1604 p_action_information23 => per_formula_functions.get_number('O5_COST'),
1605 p_action_information24 => per_formula_functions.get_number('O5_AMG'),
1606 p_action_information25 => per_formula_functions.get_number('O5_CE'),
1607 p_action_information26 => per_formula_functions.get_text('O6_DESC'),
1608 p_action_information27 => per_formula_functions.get_number('O6_COST'),
1609 p_action_information28 => per_formula_functions.get_number('O6_AMG'),
1610 p_action_information29 => per_formula_functions.get_number('O6_CE') );
1611
1612 --
1613 pay_action_information_api.create_action_information(
1614 p_action_information_id => l_action_info_id,
1615 p_action_context_id => p_assactid,
1616 p_action_context_type => 'AAP',
1617 p_object_version_number => l_ovn,
1618 p_effective_date => fnd_date.canonical_to_date(g_param_benefit_end_date),
1619 p_source_id => NULL,
1620 p_source_text => NULL,
1621 p_action_information_category => 'GB P11D ASSIGNMENT RESULTC',
1622 p_action_information1 => per_formula_functions.get_number('C_BUS_MILES'),
1623 p_action_information2 => per_formula_functions.get_number('M_BUS_MILES'),
1624 p_action_information3 => per_formula_functions.get_number('B_BUS_MILES'),
1625 p_action_information4 => per_formula_functions.get_number('C_RATE1'),
1626 p_action_information5 => per_formula_functions.get_number('C_RATE2'),
1627 p_action_information6 => per_formula_functions.get_number('M_RATE1'),
1628 p_action_information7 => per_formula_functions.get_number('M_RATE2'),
1629 p_action_information8 => per_formula_functions.get_number('B_RATE1'),
1630 p_action_information9 => per_formula_functions.get_number('B_RATE2'),
1631 p_action_information10 => per_formula_functions.get_text('DT_FREE_FUEL_WITHDRAWN'),
1632 p_action_information11 => per_formula_functions.get_text('FREE_FUEL_REINSTATED'),
1633 p_action_information12 => per_formula_functions.get_number('C_MILEAGE_PAYMENTS'),
1634 p_action_information13 => per_formula_functions.get_number('B_MILEAGE_PAYMENTS'),
1635 p_action_information14 => per_formula_functions.get_number('M_MILEAGE_PAYMENTS'),
1636 p_action_information15 => per_formula_functions.get_number('MARORS_COUNT'),
1637 p_action_information16 => per_formula_functions.get_number('C_TAX_DEDUCTED'),
1638 p_action_information17 => per_formula_functions.get_number('B_TAX_DEDUCTED'),
1639 p_action_information18 => per_formula_functions.get_number('M_TAX_DEDUCTED'),
1640 p_action_information19 => per_formula_functions.get_number('PASSENGER_PAYMENTS'),
1641 p_action_information20 => per_formula_functions.get_number('PASSENGER_BUS_MILES'),
1642 p_action_information21 => per_formula_functions.get_number('PASSENGER_BUS_MILE_AMT'),
1643 p_action_information22 => per_formula_functions.get_number('MILEAGE_ALLOWANCE_RELIEF'),
1644 p_action_information23 => per_formula_functions.get_number('INT_MAX_AMT_OUTSTANDING')
1645 );
1646 else
1647 /* Code for year 04/05 onward */
1648 pay_action_information_api.create_action_information(
1649 p_action_information_id => l_action_info_id,
1650 p_action_context_id => p_assactid,
1651 p_action_context_type => 'AAP',
1652 p_object_version_number => l_ovn,
1653 p_effective_date => fnd_date.canonical_to_date(g_param_benefit_end_date),
1654 p_source_id => NULL,
1655 p_source_text => NULL,
1656 p_action_information_category => 'GB P11D ASSIGNMENT RESULTA',
1657 p_action_information1 => per_formula_functions.get_text('A_DESC'),
1658 p_action_information2 => per_formula_functions.get_number('A_COST'),
1659 p_action_information3 => per_formula_functions.get_number('A_AMG'),
1660 p_action_information4 => per_formula_functions.get_number('A_CE'),
1661 p_action_information5 => per_formula_functions.get_text('B_DESC'),
1662 p_action_information6 => per_formula_functions.get_number('B_CE'),
1663 p_action_information7 => per_formula_functions.get_number('B_TNP'),
1664 p_action_information8 => per_formula_functions.get_number('C_COST'),
1665 p_action_information9 => per_formula_functions.get_number('C_AMG'),
1666 p_action_information10 => per_formula_functions.get_number('C_CE'),
1667 p_action_information11 => per_formula_functions.get_number('D_CE'),
1668 p_action_information12 => calculate_amap_ce ,
1669 p_action_information13 => per_formula_functions.get_number('F_TCCE'),
1670 p_action_information14 => per_formula_functions.get_number('F_TFCE'),
1671 p_action_information15 => per_formula_functions.get_number('G_CE'),
1672 p_action_information16 => per_formula_functions.get_number('I_COST'),
1673 p_action_information17 => per_formula_functions.get_number('I_AMG'),
1674 p_action_information18 => per_formula_functions.get_number('I_CE'),
1675 p_action_information19 => per_formula_functions.get_number('J_CE'),
1676 p_action_information20 => per_formula_functions.get_number('K_COST'),
1677 p_action_information21 => per_formula_functions.get_number('K_AMG'),
1678 p_action_information22 => per_formula_functions.get_number('K_CE'),
1679 p_action_information23 => per_formula_functions.get_text('L_DESC'),
1680 p_action_information24 => per_formula_functions.get_number('L_COST'),
1681 p_action_information25 => per_formula_functions.get_number('L_AMG'),
1682 p_action_information26 => per_formula_functions.get_number('L_CE'),
1683 p_action_information27 => null,
1684 p_action_information28 => per_formula_functions.get_number('H_CE1'),
1685 p_action_information29 => per_formula_functions.get_number('H_COUNT'),
1686 p_action_information30 => per_formula_functions.get_number('F_COUNT') );
1687
1688 hr_utility.trace('FFFFFF Count : ' || per_formula_functions.get_number('F_COUNT'));
1689 --
1690 pay_action_information_api.create_action_information(
1691 p_action_information_id => l_action_info_id,
1692 p_action_context_id => p_assactid,
1693 p_action_context_type => 'AAP',
1694 p_object_version_number => l_ovn,
1695 p_effective_date => fnd_date.canonical_to_date(g_param_benefit_end_date),
1696 p_source_id => NULL,
1697 p_source_text => NULL,
1698 p_action_information_category => 'GB P11D ASSIGNMENT RESULTB',
1699 p_action_information1 => per_formula_functions.get_text('M_DESC'),
1700 p_action_information2 => per_formula_functions.get_number('M_COST'),
1701 p_action_information3 => per_formula_functions.get_number('M_AMG'),
1702 p_action_information4 => per_formula_functions.get_number('M_CE'),
1703 p_action_information5 => per_formula_functions.get_text('MA_DESC'),
1704 p_action_information6 => per_formula_functions.get_number('MA_COST'),
1705 p_action_information7 => per_formula_functions.get_number('MA_AMG'),
1706 p_action_information8 => per_formula_functions.get_number('MA_CE'),
1707 p_action_information9 => per_formula_functions.get_number('M_TAXPAID'),
1708 p_action_information10 => per_formula_functions.get_number('N1_COST'),
1709 p_action_information11 => per_formula_functions.get_number('N1_AMG'),
1710 p_action_information12 => per_formula_functions.get_number('N1_CE'),
1711 p_action_information13 => per_formula_functions.get_number('N2_COST'),
1712 p_action_information14 => per_formula_functions.get_number('N2_AMG'),
1713 p_action_information15 => per_formula_functions.get_number('N2_CE'),
1714 p_action_information16 => per_formula_functions.get_text('N_TOI'),
1715 p_action_information17 => per_formula_functions.get_number('N3_COST'),
1716 p_action_information18 => per_formula_functions.get_number('N3_AMG'),
1717 p_action_information19 => per_formula_functions.get_number('N3_CE'),
1718 p_action_information20 => per_formula_functions.get_number('N4_COST'),
1719 p_action_information21 => per_formula_functions.get_number('N4_AMG'),
1720 p_action_information22 => per_formula_functions.get_number('N4_CE'),
1721 p_action_information23 => per_formula_functions.get_number('N5_COST'),
1722 p_action_information24 => per_formula_functions.get_number('N5_AMG'),
1723 p_action_information25 => per_formula_functions.get_number('N5_CE'),
1724 p_action_information26 => per_formula_functions.get_text('N6_DESC'),
1725 p_action_information27 => per_formula_functions.get_number('N6_COST'),
1726 p_action_information28 => per_formula_functions.get_number('N6_AMG'),
1727 p_action_information29 => per_formula_functions.get_number('N6_CE')
1728 ,p_action_information30 => per_formula_functions.get_number('G_FCE'));
1729
1730
1731 pay_action_information_api.create_action_information(
1732 p_action_information_id => l_action_info_id,
1733 p_action_context_id => p_assactid,
1734 p_action_context_type => 'AAP',
1735 p_object_version_number => l_ovn,
1736 p_effective_date => fnd_date.canonical_to_date(g_param_benefit_end_date),
1737 p_source_id => NULL,
1738 p_source_text => NULL,
1739 p_action_information_category => 'GB P11D ASSIGNMENT RESULTC',
1740 p_action_information1 => per_formula_functions.get_number('C_BUS_MILES'),
1741 p_action_information2 => per_formula_functions.get_number('M_BUS_MILES'),
1742 p_action_information3 => per_formula_functions.get_number('B_BUS_MILES'),
1743 p_action_information4 => per_formula_functions.get_number('C_RATE1'),
1744 p_action_information5 => per_formula_functions.get_number('C_RATE2'),
1745 p_action_information6 => per_formula_functions.get_number('M_RATE1'),
1746 p_action_information7 => per_formula_functions.get_number('M_RATE2'),
1747 p_action_information8 => per_formula_functions.get_number('B_RATE1'),
1748 p_action_information9 => per_formula_functions.get_number('B_RATE2'),
1749 p_action_information10 => null,
1750 p_action_information11 => null,
1751 p_action_information12 => per_formula_functions.get_number('C_MILEAGE_PAYMENTS'),
1752 p_action_information13 => per_formula_functions.get_number('B_MILEAGE_PAYMENTS'),
1753 p_action_information14 => per_formula_functions.get_number('M_MILEAGE_PAYMENTS'),
1754 p_action_information15 => per_formula_functions.get_number('MARORS_COUNT'),
1755 p_action_information16 => per_formula_functions.get_number('C_TAX_DEDUCTED'),
1756 p_action_information17 => per_formula_functions.get_number('B_TAX_DEDUCTED'),
1757 p_action_information18 => per_formula_functions.get_number('M_TAX_DEDUCTED'),
1758 p_action_information19 => per_formula_functions.get_number('PASSENGER_PAYMENTS'),
1759 p_action_information20 => per_formula_functions.get_number('PASSENGER_BUS_MILES'),
1760 p_action_information21 => per_formula_functions.get_number('PASSENGER_BUS_MILE_AMT'),
1761 p_action_information22 => per_formula_functions.get_number('MILEAGE_ALLOWANCE_RELIEF'),
1762 p_action_information23 => per_formula_functions.get_number('INT_MAX_AMT_OUTSTANDING')
1763 );
1764 end if;
1765
1766 END;
1767 --
1768 PROCEDURE fetch_values_and_set_globals(p_assignment_action_id NUMBER)
1769 IS
1770 l_col1_val VARCHAR2(240);
1771 l_col2_val VARCHAR2(240);
1772 l_col3_val VARCHAR2(240);
1773 l_col4_val VARCHAR2(240);
1774 l_col5_val VARCHAR2(240);
1775 l_col6_val VARCHAR2(240);
1776 l_col7_val VARCHAR2(240);
1777 l_col8_val VARCHAR2(240);
1778 l_col9_val VARCHAR2(240);
1779 l_col10_val VARCHAR2(240);
1780 l_col11_val VARCHAR2(240);
1781 l_col12_val VARCHAR2(240);
1782 l_col13_val VARCHAR2(240);
1783 l_col14_val VARCHAR2(240);
1784 l_col15_val VARCHAR2(240);
1785 l_col16_val VARCHAR2(240);
1786 l_col17_val VARCHAR2(240);
1787 l_col18_val VARCHAR2(240);
1788 l_col19_val VARCHAR2(240);
1789 l_col20_val VARCHAR2(240);
1790 l_col21_val VARCHAR2(240);
1791 l_col22_val VARCHAR2(240);
1792 l_col23_val VARCHAR2(240);
1793 l_col24_val VARCHAR2(240);
1794 l_col25_val VARCHAR2(240);
1795 l_col26_val VARCHAR2(240);
1796 l_col27_val VARCHAR2(240);
1797 l_col28_val VARCHAR2(240);
1798 l_col29_val VARCHAR2(240);
1799 l_col30_val VARCHAR2(240);
1800 l_ret_text VARCHAR2(240);
1801
1802 /*****************************************************************
1803 Added the below procedure for the bug fix 8864717.
1804 This procedure updates the global variable g_updated_flag value
1805 to 'Y' if any of the multiple assignments are updated with the
1806 summed up value of all the assignments till now.
1807 *****************************************************************/
1808
1809 PROCEDURE update_flag_var (p_ass_act_id IN NUMBER)
1810 IS
1811 l_payroll_action_id NUMBER(15);
1812 l_person_id NUMBER(15);
1813 l_updated VARCHAR2(10) := 'N';
1814 l_count NUMBER(15);
1815
1816 cursor c_get_per_det is
1817 select paa.payroll_action_id, paaf.person_id
1818 from pay_assignment_actions paa,
1819 per_all_assignments_f paaf
1820 where paa.assignment_action_id = p_ass_act_id
1821 and paa.assignment_id = paaf.assignment_id;
1822
1823 cursor c_get_updated_status (c_person_id in number,
1824 c_payroll_action_id in number) is
1825 select 'Y'
1826 from dual
1827 where exists (select 'X'
1828 from pay_action_information pai,
1829 pay_assignment_actions paa,
1830 per_all_assignments_f paaf
1831 where paaf.person_id = c_person_id
1832 and paaf.assignment_id = paa.assignment_id
1833 and paa.payroll_action_id = c_payroll_action_id
1834 and paa.assignment_action_id = pai.action_context_id
1835 and pai.action_information24 = 'Y'
1836 );
1837
1838 BEGIN
1839 hr_utility.TRACE('Entering update_flag_var procedure');
1840 hr_utility.TRACE('Value of p_ass_act_id: '||p_ass_act_id);
1841 --l_updated := 'N'
1842 open c_get_per_det;
1843 fetch c_get_per_det into l_payroll_action_id, l_person_id;
1844 hr_utility.TRACE('Value of l_payroll_action_id: '||l_payroll_action_id);
1845 hr_utility.TRACE('Value of l_person_id: '||l_person_id);
1846 close c_get_per_det;
1847
1848 open c_get_updated_status (l_person_id, l_payroll_action_id);
1849 fetch c_get_updated_status into l_updated;
1850 hr_utility.TRACE('Value of l_updated: '||l_updated);
1851 close c_get_updated_status;
1852
1853 if l_updated = 'Y' then
1854 hr_utility.TRACE('Inside if condition');
1855 g_updated_flag := 'Y';
1856 end if;
1857 hr_utility.TRACE('Leaving update_flag_var procedure');
1858 END update_flag_var;
1859
1860 BEGIN
1861 /* The code below can be removed when do P11D for year 05/06 */
1862 if to_number(g_param_rep_run) < 2005
1863 then
1864 SELECT action_information1, action_information2, action_information3, action_information4,
1865 action_information5, action_information6, action_information7, action_information8,
1866 action_information9, action_information10, action_information11, action_information12,
1867 action_information13, action_information14, action_information15, action_information16,
1868 action_information17, action_information18, action_information19, action_information20,
1869 action_information21, action_information22, action_information23, action_information24,
1870 action_information25, action_information26, action_information27, action_information28,
1871 action_information29, action_information30
1872 INTO l_col1_val, l_col2_val, l_col3_val, l_col4_val,
1873 l_col5_val, l_col6_val, l_col7_val, l_col8_val,
1874 l_col9_val, l_col10_val, l_col11_val, l_col12_val,
1875 l_col13_val, l_col14_val, l_col15_val, l_col16_val,
1876 l_col17_val, l_col18_val, l_col19_val, l_col20_val,
1877 l_col21_val, l_col22_val, l_col23_val, l_col24_val,
1878 l_col25_val, l_col26_val, l_col27_val, l_col28_val,
1879 l_col29_val, l_col30_val
1880 FROM pay_action_information
1881 WHERE action_context_id = p_assignment_action_id AND
1882 action_information_category = 'GB P11D ASSIGNMENT RESULTA'
1883 AND action_context_type = 'AAP';
1884
1885 l_ret := hr_gb_process_p11d_entries_pkg.check_desc_and_set_global_var('A_DESC', l_col1_val);
1886 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('A_COST', l_col2_val);
1887 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('A_AMG', l_col3_val);
1888 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('A_CE', l_col4_val);
1889 l_ret := hr_gb_process_p11d_entries_pkg.check_desc_and_set_global_var('B_DESC', l_col5_val);
1890 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('B_CE', l_col6_val);
1891 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('B_TNP', l_col7_val);
1892 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('C_COST', l_col8_val);
1893 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('C_AMG', l_col9_val);
1894 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('C_CE', l_col10_val);
1895 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('D_CE', l_col11_val);
1896 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('E_CE', l_col12_val);
1897 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('F_TCCE', l_col13_val);
1898 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('F_TFCE', l_col14_val);
1899 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('G_CE', l_col15_val);
1900 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('I_COST', l_col16_val);
1901 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('I_AMG', l_col17_val);
1902 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('I_CE', l_col18_val);
1903 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('J_CE', l_col19_val);
1904 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('K_COST', l_col20_val);
1905 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('K_AMG', l_col21_val);
1906 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('K_CE', l_col22_val);
1907 l_ret := hr_gb_process_p11d_entries_pkg.check_desc_and_set_global_var('L_DESC', l_col23_val);
1908 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('L_COST', l_col24_val);
1909 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('L_AMG', l_col25_val);
1910 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('L_CE', l_col26_val);
1911 l_ret := hr_gb_process_p11d_entries_pkg.check_desc_and_set_global_var('M_SHARES', l_col27_val);
1912 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('H_CE1', l_col28_val);
1913 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('H_COUNT', l_col29_val);
1914 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('F_COUNT', l_col30_val);
1915
1916 SELECT action_information1, action_information2, action_information3, action_information4,
1917 action_information5, action_information6, action_information7, action_information8,
1918 action_information9, action_information10, action_information11, action_information12,
1919 action_information13, action_information14, action_information15, action_information16,
1920 action_information17, action_information18, action_information19, action_information20,
1921 action_information21, action_information22, action_information23, action_information24,
1922 action_information25, action_information26, action_information27, action_information28,
1923 action_information29, action_information30
1924 INTO l_col1_val, l_col2_val, l_col3_val, l_col4_val,
1925 l_col5_val, l_col6_val, l_col7_val, l_col8_val,
1926 l_col9_val, l_col10_val, l_col11_val, l_col12_val,
1927 l_col13_val, l_col14_val, l_col15_val, l_col16_val,
1928 l_col17_val, l_col18_val, l_col19_val, l_col20_val,
1929 l_col21_val, l_col22_val, l_col23_val, l_col24_val,
1930 l_col25_val, l_col26_val, l_col27_val, l_col28_val,
1931 l_col29_val, l_col30_val
1932 FROM pay_action_information
1933 WHERE action_context_id = p_assignment_action_id AND
1934 action_information_category ='GB P11D ASSIGNMENT RESULTB'
1935 AND action_context_type = 'AAP';
1936
1937 l_ret := hr_gb_process_p11d_entries_pkg.check_desc_and_set_global_var('N_DESC', l_col1_val);
1938 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N_COST', l_col2_val);
1939 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N_AMG', l_col3_val);
1940 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N_CE', l_col4_val);
1941 l_ret := hr_gb_process_p11d_entries_pkg.check_desc_and_set_global_var('NA_DESC', l_col5_val);
1942 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('NA_COST', l_col6_val);
1943 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('NA_AMG', l_col7_val);
1944 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('NA_CE', l_col8_val);
1945 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N_TAXPAID', l_col9_val);
1946 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('O1_COST', l_col10_val);
1947 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('O1_AMG', l_col11_val);
1948 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('O1_CE', l_col12_val);
1949 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('O2_COST', l_col13_val);
1950 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('O2_AMG', l_col14_val);
1951 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('O2_CE', l_col15_val);
1952 l_ret := hr_gb_process_p11d_entries_pkg.check_desc_and_set_global_var('O_TOI', l_col16_val);
1953 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('O3_COST', l_col17_val);
1954 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('O3_AMG', l_col18_val);
1955 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('O3_CE', l_col19_val);
1956 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('O4_COST', l_col20_val);
1957 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('O4_AMG', l_col21_val);
1958 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('O4_CE', l_col22_val);
1959 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('O5_COST', l_col23_val);
1960 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('O5_AMG', l_col24_val);
1961 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('O5_CE', l_col25_val);
1962 l_ret := hr_gb_process_p11d_entries_pkg.check_desc_and_set_global_var('O6_DESC', l_col26_val);
1963 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('O6_COST', l_col27_val);
1964 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('O6_AMG', l_col28_val);
1965 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('O6_CE', l_col29_val);
1966
1967 --
1968 SELECT action_information1, action_information2, action_information3, action_information4,
1969 action_information5, action_information6, action_information7, action_information8,
1970 action_information9, action_information10, action_information11, action_information12,
1971 action_information13, action_information14, action_information15, action_information16,
1972 action_information17, action_information18, action_information19, action_information20,
1973 action_information21, action_information22, action_information23, action_information24,
1974 action_information25, action_information26, action_information27, action_information28,
1975 action_information29, action_information30
1976 INTO l_col1_val, l_col2_val, l_col3_val, l_col4_val,
1977 l_col5_val, l_col6_val, l_col7_val, l_col8_val,
1978 l_col9_val, l_col10_val, l_col11_val, l_col12_val,
1979 l_col13_val, l_col14_val, l_col15_val, l_col16_val,
1980 l_col17_val, l_col18_val, l_col19_val, l_col20_val,
1981 l_col21_val, l_col22_val, l_col23_val, l_col24_val,
1982 l_col25_val, l_col26_val, l_col27_val, l_col28_val,
1983 l_col29_val, l_col30_val
1984 FROM pay_action_information
1985 WHERE action_context_id = p_assignment_action_id AND
1986 action_information_category ='GB P11D ASSIGNMENT RESULTC'
1987 AND action_context_type = 'AAP';
1988
1989 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('C_BUS_MILES', l_col1_val);
1990 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('M_BUS_MILES', l_col2_val);
1991 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('B_BUS_MILES', l_col3_val);
1992 l_ret_text:= hr_gb_process_p11d_entries_pkg.max_and_set_global_var('C_RATE1','N',l_col4_val);
1993 l_ret_text:= hr_gb_process_p11d_entries_pkg.max_and_set_global_var('C_RATE2','N',l_col5_val);
1994 l_ret_text:= hr_gb_process_p11d_entries_pkg.max_and_set_global_var('M_RATE1','N',l_col6_val);
1995 l_ret_text:= hr_gb_process_p11d_entries_pkg.max_and_set_global_var('M_RATE2','N',l_col7_val);
1996 l_ret_text:= hr_gb_process_p11d_entries_pkg.max_and_set_global_var('B_RATE1','N',l_col8_val);
1997 l_ret_text:= hr_gb_process_p11d_entries_pkg.max_and_set_global_var('B_RATE2','N',l_col9_val);
1998
1999 l_ret_text:= hr_gb_process_p11d_entries_pkg.max_and_set_global_var('DT_FREE_FUEL_WITHDRAWN','D',l_col10_val);
2000 if l_ret_text = '1'
2001 then
2002 l_warn:=1;
2003 l_val_error_count := l_val_error_count + 1;
2004 l_val_errors(l_val_error_count).error_text := 'Warning:- Multiple Date Free Fuel Withdrawn Found';
2005 l_val_errors(l_val_error_count).error_assignment_name := l_assignment_name;
2006 l_val_errors(l_val_error_count).error_assignment_number := l_assignment_number;
2007 l_val_errors(l_val_error_count).error_element_name :='Car and Car Fuel 2003-04';
2008 end if;
2009 l_ret_text:= hr_gb_process_p11d_entries_pkg.max_and_set_global_var('FREE_FUEL_REINSTATED','T',l_col11_val);
2010 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('C_MILEAGE_PAYMENTS', l_col12_val);
2011 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('B_MILEAGE_PAYMENTS', l_col13_val);
2012 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('M_MILEAGE_PAYMENTS', l_col14_val);
2013 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('MARORS_COUNT', l_col15_val);
2014 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('C_TAX_DEDUCTED', l_col16_val);
2015 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('M_TAX_DEDUCTED', l_col17_val);
2016 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('M_TAX_DEDUCTED', l_col18_val);
2017 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('PASSENGER_PAYMENTS', l_col19_val);
2018 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('PASSENGER_BUS_MILES', l_col20_val);
2019 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('PASSENGER_BUS_MILE_AMT', l_col21_val);
2020 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('MILEAGE_ALLOWANCE_RELIEF', l_col22_val);
2021 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('INT_MAX_AMT_OUTSTANDING', l_col23_val);
2022 else
2023
2024 --Added the below procedure call for the bug fix 8864717.
2025 update_flag_var(p_assignment_action_id);
2026
2027
2028 /* Code for year 04/05 onwards */
2029 SELECT action_information1, action_information2, action_information3, action_information4,
2030 action_information5, action_information6, action_information7, action_information8,
2031 action_information9, action_information10, action_information11, action_information12,
2032 action_information13, action_information14, action_information15, action_information16,
2033 action_information17, action_information18, action_information19, action_information20,
2034 action_information21, action_information22, action_information23, action_information24,
2035 action_information25, action_information26, action_information28, action_information29,
2036 action_information30
2037 INTO l_col1_val, l_col2_val, l_col3_val, l_col4_val,
2038 l_col5_val, l_col6_val, l_col7_val, l_col8_val,
2039 l_col9_val, l_col10_val, l_col11_val, l_col12_val,
2040 l_col13_val, l_col14_val, l_col15_val, l_col16_val,
2041 l_col17_val, l_col18_val, l_col19_val, l_col20_val,
2042 l_col21_val, l_col22_val, l_col23_val, l_col24_val,
2043 l_col25_val, l_col26_val, l_col28_val, l_col29_val,
2044 l_col30_val
2045 FROM pay_action_information
2046 WHERE action_context_id = p_assignment_action_id AND
2047 action_information_category = 'GB P11D ASSIGNMENT RESULTA'
2048 AND action_context_type = 'AAP';
2049
2050 l_ret := hr_gb_process_p11d_entries_pkg.check_desc_and_set_global_var('A_DESC', l_col1_val);
2051 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('A_COST', l_col2_val);
2052 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('A_AMG', l_col3_val);
2053 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('A_CE', l_col4_val);
2054 l_ret := hr_gb_process_p11d_entries_pkg.check_desc_and_set_global_var('B_DESC', l_col5_val);
2055 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('B_CE', l_col6_val);
2056 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('B_TNP', l_col7_val);
2057 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('C_COST', l_col8_val);
2058 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('C_AMG', l_col9_val);
2059 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('C_CE', l_col10_val);
2060 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('D_CE', l_col11_val);
2061 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('E_CE', l_col12_val);
2062 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('F_TCCE', l_col13_val);
2063 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('F_TFCE', l_col14_val);
2064 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('G_CE', l_col15_val);
2065 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('I_COST', l_col16_val);
2066 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('I_AMG', l_col17_val);
2067 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('I_CE', l_col18_val);
2068 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('J_CE', l_col19_val);
2069 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('K_COST', l_col20_val);
2070 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('K_AMG', l_col21_val);
2071 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('K_CE', l_col22_val);
2072 l_ret := hr_gb_process_p11d_entries_pkg.check_desc_and_set_global_var('L_DESC', l_col23_val);
2073 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('L_COST', l_col24_val);
2074 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('L_AMG', l_col25_val);
2075 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('L_CE', l_col26_val);
2076 /* l_ret := hr_gb_process_p11d_entries_pkg.check_desc_and_set_global_var('M_SHARES', l_col27_val); */
2077 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('H_CE1', l_col28_val);
2078 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('H_COUNT', l_col29_val);
2079 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('F_COUNT', l_col30_val);
2080
2081 SELECT action_information1, action_information2, action_information3, action_information4,
2082 action_information5, action_information6, action_information7, action_information8,
2083 action_information9, action_information10, action_information11, action_information12,
2084 action_information13, action_information14, action_information15, action_information16,
2085 action_information17, action_information18, action_information19, action_information20,
2086 action_information21, action_information22, action_information23, action_information24,
2087 action_information25, action_information26, action_information27, action_information28,
2088 action_information29, action_information30
2089 INTO l_col1_val, l_col2_val, l_col3_val, l_col4_val,
2090 l_col5_val, l_col6_val, l_col7_val, l_col8_val,
2091 l_col9_val, l_col10_val, l_col11_val, l_col12_val,
2092 l_col13_val, l_col14_val, l_col15_val, l_col16_val,
2093 l_col17_val, l_col18_val, l_col19_val, l_col20_val,
2094 l_col21_val, l_col22_val, l_col23_val, l_col24_val,
2095 l_col25_val, l_col26_val, l_col27_val, l_col28_val,
2096 l_col29_val, l_col30_val
2097 FROM pay_action_information
2098 WHERE action_context_id = p_assignment_action_id AND
2099 action_information_category ='GB P11D ASSIGNMENT RESULTB'
2100 AND action_context_type = 'AAP';
2101
2102 l_ret := hr_gb_process_p11d_entries_pkg.check_desc_and_set_global_var('M_DESC', l_col1_val);
2103 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('M_COST', l_col2_val);
2104 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('M_AMG', l_col3_val);
2105 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('M_CE', l_col4_val);
2106 l_ret := hr_gb_process_p11d_entries_pkg.check_desc_and_set_global_var('MA_DESC', l_col5_val);
2107 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('MA_COST', l_col6_val);
2108 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('MA_AMG', l_col7_val);
2109 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('MA_CE', l_col8_val);
2110 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('M_TAXPAID', l_col9_val);
2111 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N1_COST', l_col10_val);
2112 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N1_AMG', l_col11_val);
2113 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N1_CE', l_col12_val);
2114 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N2_COST', l_col13_val);
2115 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N2_AMG', l_col14_val);
2116 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N2_CE', l_col15_val);
2117 l_ret := hr_gb_process_p11d_entries_pkg.check_desc_and_set_global_var('N_TOI', l_col16_val);
2118 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N3_COST', l_col17_val);
2119 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N3_AMG', l_col18_val);
2120 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N3_CE', l_col19_val);
2121 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N4_COST', l_col20_val);
2122 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N4_AMG', l_col21_val);
2123 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N4_CE', l_col22_val);
2124 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N5_COST', l_col23_val);
2125 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N5_AMG', l_col24_val);
2126 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N5_CE', l_col25_val);
2127 l_ret := hr_gb_process_p11d_entries_pkg.check_desc_and_set_global_var('N6_DESC', l_col26_val);
2128 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N6_COST', l_col27_val);
2129 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N6_AMG', l_col28_val);
2130 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('N6_CE', l_col29_val);
2131 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('G_FCE', l_col30_val);
2132
2133 --
2134 SELECT action_information1, action_information2, action_information3, action_information4,
2135 action_information5, action_information6, action_information7, action_information8,
2136 action_information9, action_information12,
2137 action_information13, action_information14, action_information15, action_information16,
2138 action_information17, action_information18, action_information19, action_information20,
2139 action_information21, action_information22, action_information23
2140 INTO l_col1_val, l_col2_val, l_col3_val, l_col4_val,
2141 l_col5_val, l_col6_val, l_col7_val, l_col8_val,
2142 l_col9_val, l_col12_val,
2143 l_col13_val, l_col14_val, l_col15_val, l_col16_val,
2144 l_col17_val, l_col18_val, l_col19_val, l_col20_val,
2145 l_col21_val, l_col22_val, l_col23_val
2146 FROM pay_action_information
2147 WHERE action_context_id = p_assignment_action_id AND
2148 action_information_category ='GB P11D ASSIGNMENT RESULTC'
2149 AND action_context_type = 'AAP';
2150
2151 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('C_BUS_MILES', l_col1_val);
2152 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('M_BUS_MILES', l_col2_val);
2153 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('B_BUS_MILES', l_col3_val);
2154 l_ret_text:= hr_gb_process_p11d_entries_pkg.max_and_set_global_var('C_RATE1','N',l_col4_val);
2155 l_ret_text:= hr_gb_process_p11d_entries_pkg.max_and_set_global_var('C_RATE2','N',l_col5_val);
2156 l_ret_text:= hr_gb_process_p11d_entries_pkg.max_and_set_global_var('M_RATE1','N',l_col6_val);
2157 l_ret_text:= hr_gb_process_p11d_entries_pkg.max_and_set_global_var('M_RATE2','N',l_col7_val);
2158 l_ret_text:= hr_gb_process_p11d_entries_pkg.max_and_set_global_var('B_RATE1','N',l_col8_val);
2159 l_ret_text:= hr_gb_process_p11d_entries_pkg.max_and_set_global_var('B_RATE2','N',l_col9_val);
2160 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('C_MILEAGE_PAYMENTS', l_col12_val);
2161 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('B_MILEAGE_PAYMENTS', l_col13_val);
2162 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('M_MILEAGE_PAYMENTS', l_col14_val);
2163 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('MARORS_COUNT', l_col15_val);
2164 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('C_TAX_DEDUCTED', l_col16_val);
2165 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('B_TAX_DEDUCTED', l_col17_val);
2166 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('M_TAX_DEDUCTED', l_col18_val);
2167 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('PASSENGER_PAYMENTS', l_col19_val);
2168 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('PASSENGER_BUS_MILES', l_col20_val);
2169 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('PASSENGER_BUS_MILE_AMT', l_col21_val);
2170 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('MILEAGE_ALLOWANCE_RELIEF', l_col22_val);
2171 l_ret := hr_gb_process_p11d_entries_pkg.sum_and_set_global_var('INT_MAX_AMT_OUTSTANDING', l_col23_val);
2172 end if;
2173
2174 END;
2175
2176 PROCEDURE update_value_act_info_id(p_action_info_id NUMBER,
2177 p_action_info_category VARCHAR2,
2178 p_ovn IN OUT nocopy NUMBER)
2179 IS
2180 BEGIN
2181 /* The code below can be removed when do P11D for year 05/06 */
2182 if to_number(g_param_rep_run) < 2005
2183 then
2184 IF p_action_info_category = 'GB P11D ASSIGNMENT RESULTA'
2185 THEN
2186 pay_action_information_api.update_action_information(
2187 p_action_information_id => p_action_info_id,
2188 p_object_version_number => p_ovn,
2189 p_action_information1 => per_formula_functions.get_text('A_DESC'),
2190 p_action_information2 => per_formula_functions.get_number('A_COST'),
2191 p_action_information3 => per_formula_functions.get_number('A_AMG'),
2192 p_action_information4 => per_formula_functions.get_number('A_CE'),
2193 p_action_information5 => per_formula_functions.get_text('B_DESC'),
2194 p_action_information6 => per_formula_functions.get_number('B_CE'),
2195 p_action_information7 => per_formula_functions.get_number('B_TNP'),
2196 p_action_information8 => per_formula_functions.get_number('C_COST'),
2197 p_action_information9 => per_formula_functions.get_number('C_AMG'),
2198 p_action_information10 => per_formula_functions.get_number('C_CE'),
2199 p_action_information11 => per_formula_functions.get_number('D_CE'),
2200 p_action_information12 => calculate_amap_ce ,
2201 p_action_information13 => per_formula_functions.get_number('F_TCCE'),
2202 p_action_information14 => per_formula_functions.get_number('F_TFCE'),
2203 p_action_information15 => per_formula_functions.get_number('G_CE'),
2204 p_action_information16 => per_formula_functions.get_number('I_COST'),
2205 p_action_information17 => per_formula_functions.get_number('I_AMG'),
2206 p_action_information18 => per_formula_functions.get_number('I_CE'),
2207 p_action_information19 => per_formula_functions.get_number('J_CE'),
2208 p_action_information20 => per_formula_functions.get_number('K_COST'),
2209 p_action_information21 => per_formula_functions.get_number('K_AMG'),
2210 p_action_information22 => per_formula_functions.get_number('K_CE'),
2211 p_action_information23 => per_formula_functions.get_text('L_DESC'),
2212 p_action_information24 => per_formula_functions.get_number('L_COST'),
2213 p_action_information25 => per_formula_functions.get_number('L_AMG'),
2214 p_action_information26 => per_formula_functions.get_number('L_CE'),
2215 p_action_information27 => per_formula_functions.get_text('M_SHARES'),
2216 p_action_information28 => per_formula_functions.get_number('H_CE1'),
2217 p_action_information29 => per_formula_functions.get_number('H_COUNT'),
2218 p_action_information30 => per_formula_functions.get_number('F_COUNT') );
2219 END IF;
2220
2221 IF p_action_info_category = 'GB P11D ASSIGNMENT RESULTB'
2222 THEN
2223 pay_action_information_api.update_action_information(
2224 p_action_information_id => p_action_info_id,
2225 p_object_version_number => p_ovn,
2226 p_action_information1 => per_formula_functions.get_text('N_DESC'),
2227 p_action_information2 => per_formula_functions.get_number('N_COST'),
2228 p_action_information3 => per_formula_functions.get_number('N_AMG'),
2229 p_action_information4 => per_formula_functions.get_number('N_CE'),
2230 p_action_information5 => per_formula_functions.get_text('NA_DESC'),
2231 p_action_information6 => per_formula_functions.get_number('NA_COST'),
2232 p_action_information7 => per_formula_functions.get_number('NA_AMG'),
2233 p_action_information8 => per_formula_functions.get_number('NA_CE'),
2234 p_action_information9 => per_formula_functions.get_number('N_TAXPAID'),
2235 p_action_information10 => per_formula_functions.get_number('O1_COST'),
2236 p_action_information11 => per_formula_functions.get_number('O1_AMG'),
2237 p_action_information12 => per_formula_functions.get_number('O1_CE'),
2238 p_action_information13 => per_formula_functions.get_number('O2_COST'),
2239 p_action_information14 => per_formula_functions.get_number('O2_AMG'),
2240 p_action_information15 => per_formula_functions.get_number('O2_CE'),
2241 p_action_information16 => per_formula_functions.get_text('O_TOI'),
2242 p_action_information17 => per_formula_functions.get_number('O3_COST'),
2243 p_action_information18 => per_formula_functions.get_number('O3_AMG'),
2244 p_action_information19 => per_formula_functions.get_number('O3_CE'),
2245 p_action_information20 => per_formula_functions.get_number('O4_COST'),
2246 p_action_information21 => per_formula_functions.get_number('O4_AMG'),
2247 p_action_information22 => per_formula_functions.get_number('O4_CE'),
2248 p_action_information23 => per_formula_functions.get_number('O5_COST'),
2249 p_action_information24 => per_formula_functions.get_number('O5_AMG'),
2250 p_action_information25 => per_formula_functions.get_number('O5_CE'),
2251 p_action_information26 => per_formula_functions.get_text('O6_DESC'),
2252 p_action_information27 => per_formula_functions.get_number('O6_COST'),
2253 p_action_information28 => per_formula_functions.get_number('O6_AMG'),
2254 p_action_information29 => per_formula_functions.get_number('O6_CE') );
2255 END IF;
2256
2257 IF p_action_info_category = 'GB P11D ASSIGNMENT RESULTC'
2258 THEN
2259 pay_action_information_api.update_action_information(
2260 p_action_information_id => p_action_info_id,
2261 p_object_version_number => p_ovn,
2262 p_action_information1 => per_formula_functions.get_number('C_BUS_MILES'),
2263 p_action_information2 => per_formula_functions.get_number('M_BUS_MILES'),
2264 p_action_information3 => per_formula_functions.get_number('B_BUS_MILES'),
2265 p_action_information4 => per_formula_functions.get_number('C_RATE1'),
2266 p_action_information5 => per_formula_functions.get_number('C_RATE2'),
2267 p_action_information6 => per_formula_functions.get_number('M_RATE1'),
2268 p_action_information7 => per_formula_functions.get_number('M_RATE2'),
2269 p_action_information8 => per_formula_functions.get_number('B_RATE1'),
2270 p_action_information9 => per_formula_functions.get_number('B_RATE2'),
2271 p_action_information10 => per_formula_functions.get_text('DT_FREE_FUEL_WITHDRAWN'),
2272 p_action_information11 => per_formula_functions.get_text('FREE_FUEL_REINSTATED'),
2273 p_action_information12 => per_formula_functions.get_number('C_MILEAGE_PAYMENTS'),
2274 p_action_information13 => per_formula_functions.get_number('B_MILEAGE_PAYMENTS'),
2275 p_action_information14 => per_formula_functions.get_number('M_MILEAGE_PAYMENTS'),
2276 p_action_information15 => per_formula_functions.get_number('MARORS_COUNT'),
2277 p_action_information16 => per_formula_functions.get_number('C_TAX_DEDUCTED'),
2278 p_action_information17 => per_formula_functions.get_number('B_TAX_DEDUCTED'),
2279 p_action_information18 => per_formula_functions.get_number('M_TAX_DEDUCTED'),
2280 p_action_information19 => per_formula_functions.get_number('PASSENGER_PAYMENTS'),
2281 p_action_information20 => per_formula_functions.get_number('PASSENGER_BUS_MILES'),
2282 p_action_information21 => per_formula_functions.get_number('PASSENGER_BUS_MILE_AMT'),
2283 p_action_information22 => per_formula_functions.get_number('MILEAGE_ALLOWANCE_RELIEF'),
2284 p_action_information23 => per_formula_functions.get_number('INT_MAX_AMT_OUTSTANDING')
2285 );
2286 END IF;
2287 else
2288 /* Code for year 04/05 onwards */
2289 IF p_action_info_category = 'GB P11D ASSIGNMENT RESULTA'
2290 THEN
2291 pay_action_information_api.update_action_information(
2292 p_action_information_id => p_action_info_id,
2293 p_object_version_number => p_ovn,
2294 p_action_information1 => per_formula_functions.get_text('A_DESC'),
2295 p_action_information2 => per_formula_functions.get_number('A_COST'),
2296 p_action_information3 => per_formula_functions.get_number('A_AMG'),
2297 p_action_information4 => per_formula_functions.get_number('A_CE'),
2298 p_action_information5 => per_formula_functions.get_text('B_DESC'),
2299 p_action_information6 => per_formula_functions.get_number('B_CE'),
2300 p_action_information7 => per_formula_functions.get_number('B_TNP'),
2301 p_action_information8 => per_formula_functions.get_number('C_COST'),
2302 p_action_information9 => per_formula_functions.get_number('C_AMG'),
2303 p_action_information10 => per_formula_functions.get_number('C_CE'),
2304 p_action_information11 => per_formula_functions.get_number('D_CE'),
2305 p_action_information12 => calculate_amap_ce ,
2306 p_action_information13 => per_formula_functions.get_number('F_TCCE'),
2307 p_action_information14 => per_formula_functions.get_number('F_TFCE'),
2308 p_action_information15 => per_formula_functions.get_number('G_CE'),
2309 p_action_information16 => per_formula_functions.get_number('I_COST'),
2310 p_action_information17 => per_formula_functions.get_number('I_AMG'),
2311 p_action_information18 => per_formula_functions.get_number('I_CE'),
2312 p_action_information19 => per_formula_functions.get_number('J_CE'),
2313 p_action_information20 => per_formula_functions.get_number('K_COST'),
2314 p_action_information21 => per_formula_functions.get_number('K_AMG'),
2315 p_action_information22 => per_formula_functions.get_number('K_CE'),
2316 p_action_information23 => per_formula_functions.get_text('L_DESC'),
2317 p_action_information24 => per_formula_functions.get_number('L_COST'),
2318 p_action_information25 => per_formula_functions.get_number('L_AMG'),
2319 p_action_information26 => per_formula_functions.get_number('L_CE'),
2320 p_action_information27 => null,
2321 p_action_information28 => per_formula_functions.get_number('H_CE1'),
2322 p_action_information29 => per_formula_functions.get_number('H_COUNT'),
2323 p_action_information30 => per_formula_functions.get_number('F_COUNT') );
2324 END IF;
2325
2326 IF p_action_info_category = 'GB P11D ASSIGNMENT RESULTB'
2327 THEN
2328 pay_action_information_api.update_action_information(
2329 p_action_information_id => p_action_info_id,
2330 p_object_version_number => p_ovn,
2331 p_action_information1 => per_formula_functions.get_text('M_DESC'),
2332 p_action_information2 => per_formula_functions.get_number('M_COST'),
2333 p_action_information3 => per_formula_functions.get_number('M_AMG'),
2334 p_action_information4 => per_formula_functions.get_number('M_CE'),
2335 p_action_information5 => per_formula_functions.get_text('MA_DESC'),
2336 p_action_information6 => per_formula_functions.get_number('MA_COST'),
2337 p_action_information7 => per_formula_functions.get_number('MA_AMG'),
2338 p_action_information8 => per_formula_functions.get_number('MA_CE'),
2339 p_action_information9 => per_formula_functions.get_number('M_TAXPAID'),
2340 p_action_information10 => per_formula_functions.get_number('N1_COST'),
2341 p_action_information11 => per_formula_functions.get_number('N1_AMG'),
2342 p_action_information12 => per_formula_functions.get_number('N1_CE'),
2343 p_action_information13 => per_formula_functions.get_number('N2_COST'),
2344 p_action_information14 => per_formula_functions.get_number('N2_AMG'),
2345 p_action_information15 => per_formula_functions.get_number('N2_CE'),
2346 p_action_information16 => per_formula_functions.get_text('N_TOI'),
2347 p_action_information17 => per_formula_functions.get_number('N3_COST'),
2348 p_action_information18 => per_formula_functions.get_number('N3_AMG'),
2349 p_action_information19 => per_formula_functions.get_number('N3_CE'),
2350 p_action_information20 => per_formula_functions.get_number('N4_COST'),
2351 p_action_information21 => per_formula_functions.get_number('N4_AMG'),
2352 p_action_information22 => per_formula_functions.get_number('N4_CE'),
2353 p_action_information23 => per_formula_functions.get_number('N5_COST'),
2354 p_action_information24 => per_formula_functions.get_number('N5_AMG'),
2355 p_action_information25 => per_formula_functions.get_number('N5_CE'),
2356 p_action_information26 => per_formula_functions.get_text('N6_DESC'),
2357 p_action_information27 => per_formula_functions.get_number('N6_COST'),
2358 p_action_information28 => per_formula_functions.get_number('N6_AMG'),
2359 p_action_information29 => per_formula_functions.get_number('N6_CE')
2360 ,p_action_information30 => per_formula_functions.get_number('G_FCE'));
2361
2362 END IF;
2363
2364 IF p_action_info_category = 'GB P11D ASSIGNMENT RESULTC'
2365 THEN
2366 pay_action_information_api.update_action_information(
2367 p_action_information_id => p_action_info_id,
2368 p_object_version_number => p_ovn,
2369 p_action_information1 => per_formula_functions.get_number('C_BUS_MILES'),
2370 p_action_information2 => per_formula_functions.get_number('M_BUS_MILES'),
2371 p_action_information3 => per_formula_functions.get_number('B_BUS_MILES'),
2372 p_action_information4 => per_formula_functions.get_number('C_RATE1'),
2373 p_action_information5 => per_formula_functions.get_number('C_RATE2'),
2374 p_action_information6 => per_formula_functions.get_number('M_RATE1'),
2375 p_action_information7 => per_formula_functions.get_number('M_RATE2'),
2376 p_action_information8 => per_formula_functions.get_number('B_RATE1'),
2377 p_action_information9 => per_formula_functions.get_number('B_RATE2'),
2378 p_action_information10 => null,
2379 p_action_information11 => null,
2380 p_action_information12 => per_formula_functions.get_number('C_MILEAGE_PAYMENTS'),
2381 p_action_information13 => per_formula_functions.get_number('B_MILEAGE_PAYMENTS'),
2382 p_action_information14 => per_formula_functions.get_number('M_MILEAGE_PAYMENTS'),
2383 p_action_information15 => per_formula_functions.get_number('MARORS_COUNT'),
2384 p_action_information16 => per_formula_functions.get_number('C_TAX_DEDUCTED'),
2385 p_action_information17 => per_formula_functions.get_number('B_TAX_DEDUCTED'),
2386 p_action_information18 => per_formula_functions.get_number('M_TAX_DEDUCTED'),
2387 p_action_information19 => per_formula_functions.get_number('PASSENGER_PAYMENTS'),
2388 p_action_information20 => per_formula_functions.get_number('PASSENGER_BUS_MILES'),
2389 p_action_information21 => per_formula_functions.get_number('PASSENGER_BUS_MILE_AMT'),
2390 p_action_information22 => per_formula_functions.get_number('MILEAGE_ALLOWANCE_RELIEF'),
2391 p_action_information23 => per_formula_functions.get_number('INT_MAX_AMT_OUTSTANDING'),
2392 p_action_information24 => 'Y' --Updating the below parameter value for the bug fix 8864717.
2393 );
2394 END IF;
2395 end if;
2396 END;
2397
2398 PROCEDURE update_values(p_assignment_action_id NUMBER)
2399 IS
2400 l_ovn NUMBER;
2401 l_action_info_id NUMBER(15);
2402
2403 FUNCTION get_action_info_id(p_action_info_category VARCHAR2, p_ovn OUT nocopy NUMBER)
2404 RETURN NUMBER
2405 IS
2406 l_action_info_id NUMBER(15);
2407 BEGIN
2408 SELECT action_information_id, object_version_number
2409 INTO l_action_info_id, p_ovn
2410 FROM pay_action_information
2411 WHERE action_context_id = p_assignment_action_id AND
2412 action_information_category = p_action_info_category;
2413 RETURN l_action_info_id;
2414 END;
2415 BEGIN
2416 l_action_info_id := get_action_info_id('GB P11D ASSIGNMENT RESULTA', l_ovn);
2417 update_value_act_info_id(l_action_info_id, 'GB P11D ASSIGNMENT RESULTA', l_ovn);
2418
2419 l_action_info_id := get_action_info_id('GB P11D ASSIGNMENT RESULTB', l_ovn);
2420 update_value_act_info_id(l_action_info_id, 'GB P11D ASSIGNMENT RESULTB', l_ovn);
2421
2422 l_action_info_id := get_action_info_id('GB P11D ASSIGNMENT RESULTC', l_ovn);
2423 update_value_act_info_id(l_action_info_id, 'GB P11D ASSIGNMENT RESULTC', l_ovn);
2424 END;
2425
2426 PROCEDURE validate_values
2427 IS
2428 PROCEDURE read_validate_log_desc(p_var_name VARCHAR2)
2429 IS
2430 l_var_value VARCHAR2(150);
2431 BEGIN
2432 l_var_value := per_formula_functions.get_text(p_var_name);
2433 IF UPPER(l_var_value) = 'MULTIPLE'
2434 THEN
2435 /* The code below can be removed when do P11D for year 05/06 */
2436 if to_number(g_param_rep_run) < 2005
2437 then
2438 IF p_var_name = 'M_SHARES'
2439 THEN
2440 pay_core_utils.push_message(800, 'HR_GB_78059_INCONSISTENT_VAL', 'F');
2441 fnd_message.set_name('PER', 'HR_GB_78059_INCONSISTENT_VAL');
2442 l_val_error_count := l_val_error_count + 1;
2443 l_val_errors(l_val_error_count).error_text := fnd_message.get;
2444 l_val_errors(l_val_error_count).error_assignment_name := l_assignment_name;
2445 l_val_errors(l_val_error_count).error_assignment_number := l_assignment_number;
2446 l_val_errors(l_val_error_count).error_element_name :='P11D Shares';
2447 END IF;
2448
2449 IF p_var_name = 'O_TOI'
2450 THEN
2451 pay_core_utils.push_message(800, 'HR_GB_78060_TOI_INCONSISTENT', 'F');
2452 fnd_message.set_name('PER', 'HR_GB_78060_TOI_INCONSISTENT');
2453 l_val_error_count := l_val_error_count + 1;
2454 l_val_errors(l_val_error_count).error_text := fnd_message.get;
2455 l_val_errors(l_val_error_count).error_assignment_name := l_assignment_name;
2456 l_val_errors(l_val_error_count).error_assignment_number := l_assignment_number;
2457 l_val_errors(l_val_error_count).error_element_name :='Expenses Payments';
2458 END IF;
2459 l_error := '1';
2460 l_error_assignment := TRUE;
2461 else /* code for year 04/05 onwards */
2462 IF p_var_name = 'N_TOI'
2463 THEN
2464 pay_core_utils.push_message(800, 'HR_GB_78060_TOI_INCONSISTENT', 'F');
2465 END IF;
2466 l_error := '1';
2467 l_error_assignment := TRUE;
2468 end if;
2469 END IF;
2470 END;
2471
2472 PROCEDURE read_validate_log(p_var_name VARCHAR2)
2473 IS
2474 l_var_value NUMBER;
2475 BEGIN
2476 l_var_value := per_formula_functions.get_number(p_var_name);
2477 IF p_var_name = 'F_COUNT'
2478 THEN
2479 IF l_var_value > 50
2480 THEN
2481 pay_core_utils.push_message(800, 'HR_GB_78061_CAR_NUM_ERROR', 'F');
2482 l_error := '1';
2483 l_error_assignment := TRUE;
2484 END IF;
2485 IF l_var_value > 2
2486 THEN
2487 pay_core_utils.push_message(800, 'PER_GB_P11D_78058_ASG_ERR_MSG', 'W');
2488 pay_core_utils.push_token('TEXT', 'Car and Car Fuel 2003_04: This employee has more than 2 cars');
2489 l_warn := '1';
2490 END IF;
2491 END IF;
2492 IF p_var_name = 'MARORS_COUNT'
2493 THEN
2494 IF l_var_value > 1
2495 THEN
2496 pay_core_utils.push_message(800, 'HR_GB_78081_MARORS_NUM_ERROR', 'F');
2497 l_error := '1';
2498 l_error_assignment := TRUE;
2499 END IF;
2500 END IF;
2501 END;
2502 --
2503 PROCEDURE read_validate_min_max(p_var_name VARCHAR2,
2504 p_min_value number,
2505 p_max_value number,
2506 p_element_name VARCHAR2)
2507 IS
2508 l_var_value NUMBER;
2509 BEGIN
2510 l_var_value := per_formula_functions.get_number(p_var_name);
2511 If l_var_value < p_min_value then
2512 pay_core_utils.push_message(800, 'HR_GB_78083_MIN_VALUE_ERROR', 'F');
2513 pay_core_utils.push_token('ELEMENT_NAME', p_element_name);
2514 pay_core_utils.push_token('VALUE', p_min_value);
2515 l_error := '1';
2516 l_error_assignment := TRUE;
2517 elsif l_var_value > p_max_value then
2518 pay_core_utils.push_message(800, 'HR_GB_78082_MAX_VALUE_ERROR', 'F');
2519 pay_core_utils.push_token('ELEMENT_NAME', p_element_name);
2520 pay_core_utils.push_token('VALUE', p_max_value);
2521 l_error := '1';
2522 l_error_assignment := TRUE;
2523 end if;
2524 END;
2525 --
2526 BEGIN
2527 read_validate_log('F_COUNT');
2528 /* The code below can be removed when do P11D for year 05/06 */
2529 if to_number(g_param_rep_run) < 2005 then
2530 read_validate_log_desc('M_SHARES');
2531 read_validate_log_desc('O_TOI');
2532 else /* code for year 04/05 onwards */
2533 read_validate_log_desc('N_TOI');
2534 end if;
2535 read_validate_min_max('A_COST',0,999999999.99,'Assets Transferred');
2536 read_validate_min_max('A_AMG',0,999999999.99,'Assets Transferred');
2537 read_validate_min_max('A_CE',0,999999999.99,'Assets Transferred');
2538 read_validate_min_max('B_CE',0,999999999.99,'Payments Made For Emp');
2539 read_validate_min_max('B_TNP',0,999999999.99,'Payments Made For Emp');
2540 read_validate_min_max('C_COST',0,999999999.99,'Vouchers or Credit Cards');
2541 read_validate_min_max('C_AMG',0,999999999.99,'Vouchers or Credit Cards');
2542 read_validate_min_max('C_CE',0,999999999.99,'Vouchers or Credit Cards');
2543
2544 read_validate_min_max('D_CE',0,999999999.99,'Living Accommodation');
2545
2546 read_validate_min_max('E_CE',0,999999.51,'Mileage Allowance and PPayment');
2547
2548 read_validate_min_max('F_TCCE',0,999999999.99,'Car and Car Fuel 2003_04');
2549 read_validate_min_max('F_TFCE',0,999999999.99,'Car and Car Fuel 2003_04');
2550
2551 read_validate_min_max('G_CE',0,999999999.99,'Vans 2002_03');
2552
2553 read_validate_min_max('I_COST',0,999999999.99,'Pvt Med Treatment or Insurance');
2554 read_validate_min_max('I_AMG',0,999999999.99,'Pvt Med Treatment or Insurance');
2555 read_validate_min_max('I_CE',0,999999999.99,'Pvt Med Treatment or Insurance');
2556
2557 read_validate_min_max('J_CE',0,999999999.99,'Relocation Expenses');
2558
2559 read_validate_min_max('K_COST',0,999999999.99,'Services Supplied');
2560 read_validate_min_max('K_AMG',0,999999999.99,'Services Supplied');
2561 read_validate_min_max('K_CE',0,999999999.99,'Services Supplied');
2562
2563 read_validate_min_max('L_COST',0,999999999.99,'Assets at Emp Disposal');
2564 read_validate_min_max('L_AMG',0,999999999.99,'Assets at Emp Disposal');
2565 read_validate_min_max('L_CE',0,999999999.99,'Assets at Emp Disposal');
2566
2567 /* The code below can be removed when do P11D for year 05/06 */
2568 if to_number(g_param_rep_run) < 2005
2569 then
2570 read_validate_min_max('N_COST',0,999999999.99,'Other Items Non 1A');
2571 read_validate_min_max('N_AMG',0,999999999.99,'Other Items Non 1A');
2572 read_validate_min_max('N_CE',0,999999999.99,'Other Items Non 1A');
2573
2574 read_validate_min_max('NA_COST',0,999999999.99,'Other Items');
2575 read_validate_min_max('NA_AMG',0,999999999.99,'Other Items');
2576 read_validate_min_max('NA_CE',0,999999999.99,'Other Items');
2577 read_validate_min_max('N_TAXPAID',0,999999999.99,'Other Items Non 1A');
2578
2579 read_validate_min_max('O1_COST',0,999999999.99,'Expenses Payments');
2580 read_validate_min_max('O1_AMG',0,999999999.99,'Expenses Payments');
2581 read_validate_min_max('O1_CE',0,999999999.99,'Expenses Payments');
2582
2583 read_validate_min_max('O2_COST',0,999999999.99,'Expenses Payments');
2584 read_validate_min_max('O2_AMG',0,999999999.99,'Expenses Payments');
2585 read_validate_min_max('O2_CE',0,999999999.99,'Expenses Payments');
2586
2587 read_validate_min_max('O3_COST',0,999999999.99,'Expenses Payments');
2588 read_validate_min_max('O3_AMG',0,999999999.99,'Expenses Payments');
2589 read_validate_min_max('O3_CE',0,999999999.99,'Expenses Payments');
2590
2591 read_validate_min_max('O4COST',0,999999999.99,'Expenses Payments');
2592 read_validate_min_max('O4_AMG',0,999999999.99,'Expenses Payments');
2593 read_validate_min_max('O4_CE',0,999999999.99,'Expenses Payments');
2594
2595 read_validate_min_max('O5_COST',0,999999999.99,'Expenses Payments');
2596 read_validate_min_max('O5_AMG',0,999999999.99,'Expenses Payments');
2597 read_validate_min_max('O5_CE',0,999999999.99,'Expenses Payments');
2598
2599 read_validate_min_max('O6_COST',0,999999999.99,'Expenses Payments');
2600 read_validate_min_max('O6_AMG',0,999999999.99,'Expenses Payments');
2601 read_validate_min_max('O6_CE',0,999999999.99,'Expenses Payments');
2602 else /* code for year 04/05 onwards */
2603 read_validate_min_max('M_COST',0,999999999.99,'Other Items Non 1A');
2604 read_validate_min_max('M_AMG',0,999999999.99,'Other Items Non 1A');
2605 read_validate_min_max('M_CE',0,999999999.99,'Other Items Non 1A');
2606
2607 read_validate_min_max('MA_COST',0,999999999.99,'Other Items');
2608 read_validate_min_max('MA_AMG',0,999999999.99,'Other Items');
2609 read_validate_min_max('MA_CE',0,999999999.99,'Other Items');
2610 read_validate_min_max('M_TAXPAID',0,999999999.99,'Other Items Non 1A');
2611
2612 read_validate_min_max('N1_COST',0,999999999.99,'Expenses Payments');
2613 read_validate_min_max('N1_AMG',0,999999999.99,'Expenses Payments');
2614 read_validate_min_max('N1_CE',0,999999999.99,'Expenses Payments');
2615
2616 read_validate_min_max('N2_COST',0,999999999.99,'Expenses Payments');
2617 read_validate_min_max('N2_AMG',0,999999999.99,'Expenses Payments');
2618 read_validate_min_max('N2_CE',0,999999999.99,'Expenses Payments');
2619
2620 read_validate_min_max('N3_COST',0,999999999.99,'Expenses Payments');
2621 read_validate_min_max('N3_AMG',0,999999999.99,'Expenses Payments');
2622 read_validate_min_max('N3_CE',0,999999999.99,'Expenses Payments');
2623
2624 read_validate_min_max('N4COST',0,999999999.99,'Expenses Payments');
2625 read_validate_min_max('N4_AMG',0,999999999.99,'Expenses Payments');
2626 read_validate_min_max('N4_CE',0,999999999.99,'Expenses Payments');
2627
2628 read_validate_min_max('N5_COST',0,999999999.99,'Expenses Payments');
2629 read_validate_min_max('N5_AMG',0,999999999.99,'Expenses Payments');
2630 read_validate_min_max('N5_CE',0,999999999.99,'Expenses Payments');
2631
2632 read_validate_min_max('N6_COST',0,999999999.99,'Expenses Payments');
2633 read_validate_min_max('N6_AMG',0,999999999.99,'Expenses Payments');
2634 read_validate_min_max('N6_CE',0,999999999.99,'Expenses Payments');
2635 end if;
2636 read_validate_min_max('MILEAGE_ALLOWANCE_RELIEF',-999999.99,0,'MARORS');
2637 -- checking max amount outstanding for Int free and low int loans
2638 -- will rasie just a warning for this
2639 If per_formula_functions.get_number('INT_MAX_AMT_OUTSTANDING') < l_loan_threshold then
2640 pay_core_utils.push_message(800, 'HR_GB_78083_MIN_VALUE_ERROR', 'W');
2641 pay_core_utils.push_token('ELEMENT_NAME', 'Int Free and Low Int Loans');
2642 pay_core_utils.push_token('VALUE', l_loan_threshold);
2643 l_warn := '1';
2644 End if;
2645 END;
2646 procedure validate_ni(p_assactid NUMBER,
2647 p_assid NUMBER,
2648 p_eff_date DATE)
2649 is
2650 l_var_value NUMBER;
2651 l_nat_number VARCHAR2(100);
2652
2653 cursor csr_ni is
2654 select ppf.national_identifier
2655 from per_assignments_f paf,
2656 per_all_people_f ppf,
2657 per_periods_of_service pps
2658 where ppf.person_id = paf.person_id
2659 and paf.assignment_id = p_assid
2660 and paf.period_of_service_id = pps.period_of_service_id(+)
2661 and p_eff_date between paf.effective_start_date and paf.effective_end_date
2662 and least(nvl(pps.actual_termination_date,
2663 fnd_date.canonical_to_date(g_param_benefit_end_date)),
2664 fnd_date.canonical_to_date(g_param_benefit_end_date))
2665 between ppf.effective_start_date and ppf.effective_end_date;
2666 begin
2667 open csr_ni;
2668 fetch csr_ni into l_nat_number;
2669 close csr_ni;
2670
2671 select hr_gb_utility.ni_validate(l_nat_number,p_eff_date)
2672 into l_var_value
2673 from dual;
2674
2675 if l_var_value <> 0 then
2676 -- Setup warning message
2677 l_warn:=1;
2678 pay_core_utils.push_message(800, 'PER_GB_P11D_78058_ASG_ERR_MSG', 'W');
2679 pay_core_utils.push_token('TEXT', ': Invalid NI number ' || l_nat_number);
2680
2681 -- Update the NI Number field to NULL
2682 update pay_action_information
2683 set action_information4 = null
2684 where action_information_category = 'EMPLOYEE DETAILS'
2685 and action_context_type = 'AAP'
2686 and action_context_id = p_assactid
2687 and assignment_id = p_assid;
2688 end if;
2689 end;
2690
2691 procedure validate_employee_address(p_assignment_action_id number,
2692 p_assignment_name varchar2,
2693 p_assignment_number varchar2)
2694 is
2695 l_addr1 varchar2(255);
2696 l_addr2 varchar2(255);
2697 l_addr3 varchar2(255);
2698 l_addr4 varchar2(255);
2699 l_addr5 varchar2(255);
2700
2701 cursor get_address is
2702 select NVL(UPPER(pai_person.action_information5), ' '), -- addr line 1
2703 NVL(UPPER(pai_person.action_information6), ' '), -- addr line 2
2704 NVL(UPPER(pai_person.action_information7), ' '), -- addr line 3
2705 NVL(UPPER(pai_person.action_information8), ' '), -- addr line 4
2706 NVL(UPPER(hl.meaning), ' ') -- addr line 5
2707 from pay_action_information pai_person,
2708 hr_lookups hl
2709 where pai_person.action_context_id = p_assignment_action_id
2710 and pai_person.action_information_category = 'ADDRESS DETAILS'
2711 and pai_person.action_information14 = 'Employee Address'
2712 and pai_person.action_context_type = 'AAP'
2713 and hl.lookup_type(+) = 'GB_COUNTY'
2714 and hl.lookup_code(+) = pai_person.action_information9;
2715 begin
2716 open get_address;
2717 fetch get_address into l_addr1,
2718 l_addr2,
2719 l_addr3,
2720 l_addr4,
2721 l_addr5;
2722 close get_address;
2723 if pay_gb_eoy_magtape.validate_input(l_addr1,'P11D_EDI') > 0 then
2724 l_error_assignment := TRUE;
2725 l_error := '1';
2726 pay_core_utils.push_message(800, 'PER_GB_P11D_78058_ASG_ERR_MSG', 'F');
2727 pay_core_utils.push_token('TEXT', ': Magtape Character Validation has failed for Address Line 1 value ' || l_addr1);
2728 end if;
2729 if pay_gb_eoy_magtape.validate_input(l_addr2,'P11D_EDI') > 0 then
2730 l_error_assignment := TRUE;
2731 l_error := '1';
2732 pay_core_utils.push_message(800, 'PER_GB_P11D_78058_ASG_ERR_MSG', 'F');
2733 pay_core_utils.push_token('TEXT', ': Magtape Character Validation has failed for Address Line 2 value ' || l_addr2);
2734 end if;
2735 if pay_gb_eoy_magtape.validate_input(l_addr3,'P11D_EDI') > 0 then
2736 l_error_assignment := TRUE;
2737 l_error := '1';
2738 pay_core_utils.push_message(800, 'PER_GB_P11D_78058_ASG_ERR_MSG', 'F');
2739 pay_core_utils.push_token('TEXT', ': Magtape Character Validation has failed for Address Line 3 value ' || l_addr3);
2740 end if;
2741 if pay_gb_eoy_magtape.validate_input(l_addr4,'P11D_EDI') > 0 then
2742 l_error_assignment := TRUE;
2743 l_error := '1';
2744 pay_core_utils.push_message(800, 'PER_GB_P11D_78058_ASG_ERR_MSG', 'F');
2745 pay_core_utils.push_token('TEXT', ': Magtape Character Validation has failed for Address Line 4 value ' || l_addr4);
2746 end if;
2747 if pay_gb_eoy_magtape.validate_input(l_addr5,'P11D_EDI') > 0 then
2748 l_error_assignment := TRUE;
2749 l_error := '1';
2750 pay_core_utils.push_message(800, 'PER_GB_P11D_78058_ASG_ERR_MSG', 'F');
2751 pay_core_utils.push_token('TEXT', ': Magtape Character Validation has failed for Address Line 5 value ' || l_addr5);
2752 end if;
2753 end;
2754
2755 procedure check_assignment_latest_info(p_assignment_id number,
2756 p_assignment_act_id number,
2757 p_ben_end_date date,
2758 p_asg_end_date date)
2759 is
2760 cursor row_person_return is
2761 select p1.full_name,
2762 p1.first_name,
2763 p1.last_name,
2764 p1.middle_names
2765 from per_all_assignments_f a,
2766 per_all_people_f p,
2767 per_all_people_f p1
2768 where a.assignment_id = p_assignment_id
2769 and p.person_id = a.person_id
2770 and p1.person_id = a.person_id
2771 and p_asg_end_date between p.effective_start_date and p.effective_end_date
2772 and p_ben_end_date between p1.effective_start_date and p1.effective_end_date
2773 and (nvl(p1.first_name,' ') <> nvl(p.first_name,' ')
2774 or
2775 nvl(p1.last_name,' ') <> nvl(p.last_name,' ')
2776 or
2777 nvl(p1.middle_names,' ') <> nvl(p.middle_names,' ') );
2778
2779 cursor row_address_return is
2780 select a1.address_line1,
2781 a1.address_line2,
2782 a1.address_line3,
2783 a1.town_or_city,
2784 a1.region_1,
2785 a1.region_2,
2786 a1.region_3,
2787 a1.postal_code,
2788 a1.country
2789 from pay_action_information a,
2790 per_addresses a1,
2791 per_all_assignments_f per
2792 where per.assignment_id = p_assignment_id
2793 and p_asg_end_date between per.effective_start_date and per.effective_end_date
2794 and a1.person_id = per.person_id
2795 /*
2796 and a1.date_from = (select max(a2.date_from)
2797 from per_addresses a2
2798 where a2.primary_flag = 'Y'
2799 and a2.person_id = a1.person_id)
2800 */
2801 and sysdate between a1.date_from and nvl(a1.date_to, hr_general.end_of_time)
2802 and a1.primary_flag = 'Y'
2803 and per.assignment_id = a.assignment_id
2804 and a.action_information_category = 'ADDRESS DETAILS'
2805 and a.action_context_type = 'AAP'
2806 and a.action_context_id = p_assignment_act_id
2807 and (nvl(a.action_information5,' ') <> nvl(a1.address_line1,' ') or
2808 nvl(a.action_information6,' ') <> nvl(a1.address_line2,' ') or
2809 nvl(a.action_information7,' ') <> nvl(a1.address_line3,' ') or
2810 nvl(a.action_information8,' ') <> nvl(a1.town_or_city,' ') or
2811 nvl(a.action_information9,' ') <> nvl(a1.region_1,' ') or
2812 nvl(a.action_information10,' ')<> nvl(a1.region_2,' ') or
2813 nvl(a.action_information11,' ')<> nvl(a1.region_3,' ') or
2814 nvl(a.action_information12,' ')<> nvl(a1.postal_code,' ') or
2815 nvl(a.action_information13,' ')<> nvl(a1.country,' '));
2816
2817 l_fu_name per_all_people_f.full_name%type;
2818 l_fi_name per_all_people_f.first_name%type;
2819 l_l_name per_all_people_f.last_name%type;
2820 l_m_names per_all_people_f.middle_names%type;
2821 l_addr1 per_addresses.address_line1%type;
2822 l_addr2 per_addresses.address_line2%type;
2823 l_addr3 per_addresses.address_line3%type;
2824 l_toc per_addresses.town_or_city%type;
2825 l_reg1 per_addresses.region_1%type;
2826 l_reg2 per_addresses.region_2%type;
2827 l_reg3 per_addresses.region_3%type;
2828 l_poc per_addresses.postal_code%type;
2829 l_country per_addresses.country%type;
2830
2831 begin
2832 open row_person_return;
2833 fetch row_person_return into l_fu_name,
2834 l_fi_name,
2835 l_l_name,
2836 l_m_names;
2837 if row_person_return%FOUND then
2838 update pay_action_information
2839 set action_information1 = l_fu_name
2840 where action_information_category = 'EMPLOYEE DETAILS'
2841 and action_context_type = 'AAP'
2842 and action_context_id = p_assignment_act_id;
2843
2844 update pay_action_information
2845 set action_information6 = l_fi_name,
2846 action_information7 = l_m_names,
2847 action_information8 = l_l_name
2848 where action_information_category = 'GB EMPLOYEE DETAILS'
2849 and action_context_type = 'AAP'
2850 and action_context_id = p_assignment_act_id;
2851 end if;
2852 close row_person_return;
2853
2854
2855 open row_address_return;
2856 fetch row_address_return into l_addr1,
2857 l_addr2,
2858 l_addr3,
2859 l_toc,
2860 l_reg1,
2861 l_reg2,
2862 l_reg3,
2863 l_poc,
2864 l_country;
2865 if row_address_return%FOUND then
2866 update pay_action_information
2867 set action_information5 = l_addr1,
2868 action_information6 = l_addr2,
2869 action_information7 = l_addr3,
2870 action_information8 = l_toc,
2871 action_information9 = l_reg1,
2872 action_information10= l_reg2,
2873 action_information11= l_reg3,
2874 action_information12= l_poc,
2875 action_information13= l_country
2876 where action_information_category = 'ADDRESS DETAILS'
2877 and action_context_type = 'AAP'
2878 and action_context_id = p_assignment_act_id
2879 and assignment_id = p_assignment_id;
2880 end if;
2881 close row_address_return;
2882 end check_assignment_latest_info;
2883
2884 BEGIN
2885
2886 l_warn := 0;
2887 hr_utility.set_location('Entering '|| l_proc, 10);
2888 hr_utility.set_location('step '|| l_proc, 20);
2889 hr_utility.set_location('finding assignment id '|| l_proc, 20);
2890 PAY_GB_P11D_ARCHIVE_SS.get_parameters(
2891 p_payroll_action_id => g_pactid,
2892 p_token_name => 'EDI_VALIDATION',
2893 p_token_value => l_edi_validation);
2894 -- checking the cached info exists or not
2895 -- It could be that the action creation and acrhive are fired in
2896 -- different threads, this may result in
2897 -- cached info not available in archive code.
2898 l_first_index := find_lowest_matching_index;
2899 if l_first_index = 0 then
2900 -- need to populate the g_tab_ben_detail
2901 l_first_index_set := FALSE;
2902
2903 FOR val_elememt_entry_id IN csr_val_element_entry_id(
2904 p_assactid,
2905 g_param_benefit_start_date,
2906 g_param_benefit_end_date)
2907 LOOP
2908 IF NOT l_first_index_set then
2909 l_first_index := g_ben_asg_count + 1;
2910 l_first_index_set := true;
2911 end if;
2912
2913 g_ben_asg_count := g_ben_asg_count + 1;
2914 hr_utility.set_location('Inside the cursor val_elememt_entry_id ', 30);
2915 g_tab_ben_detail(g_ben_asg_count).assignment_action_id := p_assactid;
2916 g_tab_ben_detail(g_ben_asg_count).element_type_id := val_elememt_entry_id.element_type_id;
2917 g_tab_ben_detail(g_ben_asg_count).element_entry_id := val_elememt_entry_id.element_entry_id;
2918 g_tab_ben_detail(g_ben_asg_count).element_name := val_elememt_entry_id.element_name;
2919 g_tab_ben_detail(g_ben_asg_count).effective_start_date := val_elememt_entry_id.effective_start_date;
2920 g_tab_ben_detail(g_ben_asg_count).person_id := val_elememt_entry_id.person_id;
2921 g_tab_ben_detail(g_ben_asg_count).assignment_id := val_elememt_entry_id.assignment_id;
2922 g_tab_ben_detail(g_ben_asg_count).classification_name := val_elememt_entry_id.classification_name;
2923 -- assign it to the table of table!
2924 -- g_tab_ben_detail_tab(p_assactid) := g_tab_ben_detail;
2925 END LOOP;
2926 end if;
2927
2928 -- l_index := g_tab_ben_detail.FIRST;
2929 l_index := l_first_index;
2930 l_assignment_id := g_tab_ben_detail(l_index).assignment_id;
2931 l_error_assignment := FALSE;
2932 l_error := '0';
2933 -- call generic procedure to retrieve and archive all data for EMPLOYEE DETAILS, ADDRESS DETAILS
2934 hr_utility.set_location('Calling pay_emp_action_arch', 20);
2935
2936 open csr_get_term_date(l_assignment_id);
2937 fetch csr_get_term_date into l_actual_termination_date;
2938 close csr_get_term_date;
2939
2940 open csr_get_asg_end_date(l_assignment_id);
2941 fetch csr_get_asg_end_date into l_asg_max_end_date;
2942 close csr_get_asg_end_date;
2943
2944 open csr_get_global('P11D_LOW_INT_LOAN_THRESHOLD', fnd_date.canonical_to_date(g_param_benefit_end_date));
2945 fetch csr_get_global into l_loan_threshold;
2946 close csr_get_global;
2947 -- default the value to 5000 for archive prior to 2006
2948 if l_loan_threshold is null then
2949 l_loan_threshold := 5000;
2950 end if;
2951
2952 hr_utility.trace('before get_asg_end_date');
2953 if (l_actual_termination_date is null) or
2954 (l_actual_termination_date > l_asg_max_end_date) then
2955 l_actual_termination_date := l_asg_max_end_date;
2956 end if;
2957 --if l_actual_termination_date is null then
2958 -- hr_utility.trace('inside if');
2959 -- open csr_get_asg_end_date(l_assignment_id);
2960 -- fetch csr_get_asg_end_date into l_actual_termination_date;
2961 -- close csr_get_asg_end_date;
2962 --end if;
2963 hr_utility.trace('after get_asg_end_date');
2964
2965 pay_emp_action_arch.get_personal_information(
2966 p_payroll_action_id => g_pactid, -- archive payroll_action_id
2967 p_assactid => p_assactid, -- archive assignment_action_id
2968 p_assignment_id => l_assignment_id, -- current assignment_id
2969 p_curr_pymt_ass_act_id => NULL, -- prepayment assignment_action_id
2970 p_curr_eff_date => least(
2971 nvl(l_actual_termination_date,
2972 fnd_date.canonical_to_date(g_param_benefit_end_date)),
2973 fnd_date.canonical_to_date(g_param_benefit_end_date)
2974 ), -- archive effective_date
2975 p_date_earned => least(
2976 nvl(l_actual_termination_date,
2977 fnd_date.canonical_to_date(g_param_benefit_end_date)),
2978 fnd_date.canonical_to_date(g_param_benefit_end_date)
2979 ), -- payroll date_earned
2980 p_curr_pymt_eff_date => least(
2981 nvl(l_actual_termination_date,
2982 fnd_date.canonical_to_date(g_param_benefit_end_date)),
2983 fnd_date.canonical_to_date(g_param_benefit_end_date)
2984 ), -- prepayment effective_date
2985 p_tax_unit_id => NULL, -- only required for US
2986 p_time_period_id => NULL, -- payroll time_period_id
2987 p_ppp_source_action_id => NULL);
2988 hr_utility.set_location('Returned from pay_emp_action_arch', 30);
2989
2990 /* Perform NI Validation */
2991 validate_ni(
2992 p_assactid => p_assactid,
2993 p_assid => l_assignment_id,
2994 p_eff_date => least(nvl(l_actual_termination_date,
2995 fnd_date.canonical_to_date(g_param_benefit_end_date)),
2996 fnd_date.canonical_to_date(g_param_benefit_end_date)));
2997
2998 hr_utility.set_location('Returned from NI Validation',30);
2999 open csr_assignment_det(l_assignment_id, g_param_tax_reference);
3000 fetch csr_assignment_det into
3001 l_assignment_name, l_payroll_id, l_director_flag, l_first_name,
3002 l_middle_name, l_last_name, l_assignment_number, l_person_id, l_ni_number, l_sex, l_date_of_birth;
3003 close csr_assignment_det;
3004
3005 hr_utility.set_location('finding payroll info: ', 25);
3006 hr_utility.set_location('payroll id: '|| l_payroll_id, 25);
3007 --set_payroll_info
3008 Begin
3009 IF l_pay_info_tab.EXISTS(l_payroll_id)
3010 THEN
3011 NULL;
3012 ELSE
3013 populate_payroll_info(g_param_benefit_end_date, l_payroll_id);
3014 END IF;
3015 EXCEPTION
3016 WHEN payroll_not_found
3017 THEN
3018 l_error_assignment := TRUE;
3019 pay_core_utils.push_message(800, 'PER_GB_P11D_78058_ASG_ERR_MSG', 'F');
3020 pay_core_utils.push_token('TEXT',
3021 ': Oracle HRMS cannot locate any information for ' || l_payroll_id || '. Please check that the Payroll Name is correct.');
3022 l_error := '1';
3023 WHEN tax_office_name_error
3024 THEN
3025 l_error_assignment := TRUE;
3026 l_error := '1';
3027 pay_core_utils.push_message(800, 'PER_GB_P11D_78058_ASG_ERR_MSG', 'F');
3028 pay_core_utils.push_token('TEXT', ': Magtape Character Validation has failed for Tax Office Name value '
3029 || l_pay_info_tab(l_payroll_id).l_tax_office_name);
3030 WHEN employer_address_error
3031 THEN
3032 l_error_assignment := TRUE;
3033 l_error := '1';
3034 pay_core_utils.push_message(800, 'PER_GB_P11D_78058_ASG_ERR_MSG', 'F');
3035 pay_core_utils.push_token('TEXT', ': Magtape Character Validation has failed for Employers Address value '
3036 || l_pay_info_tab(l_payroll_id).l_employer_address);
3037 WHEN employers_ref_no_error
3038 THEN
3039 l_error_assignment := TRUE;
3040 l_error := '1';
3041 pay_core_utils.push_message(800, 'PER_GB_P11D_78058_ASG_ERR_MSG', 'F');
3042 pay_core_utils.push_token('TEXT', ': Magtape Character Validation has failed for Employers Reference Number value '
3043 || l_pay_info_tab(l_payroll_id).l_employers_ref_no);
3044 WHEN employer_name_error
3045 THEN
3046 l_error_assignment := TRUE;
3047 l_error := '1';
3048 pay_core_utils.push_message(800, 'PER_GB_P11D_78058_ASG_ERR_MSG', 'F');
3049 pay_core_utils.push_token('TEXT', ': Magtape Character Validation has failed for Employers Name value '
3050 || l_pay_info_tab(l_payroll_id).l_employer_name);
3051 END; -- end for payroll info setup
3052
3053 IF l_error <> '1'
3054 THEN
3055 pay_action_information_api.create_action_information(
3056 p_action_information_id => l_action_info_id,
3057 p_action_context_id => p_assactid,
3058 p_action_context_type => 'AAP',
3059 p_object_version_number => l_ovn,
3060 p_effective_date => fnd_date.canonical_to_date(g_param_benefit_end_date),
3061 p_source_id => NULL,
3062 p_source_text => NULL,
3063 p_action_information_category => 'EMEA PAYROLL INFO',
3064 p_action_information1 => g_pactid,
3065 p_action_information2 => NULL,
3066 p_action_information3 => NULL,
3067 p_action_information4 => l_pay_info_tab(l_payroll_id).l_tax_office_name,
3068 p_action_information5 => l_pay_info_tab(l_payroll_id).l_tax_office_phone_no,
3069 p_action_information6 => l_pay_info_tab(l_payroll_id).l_employers_ref_no,
3070 p_action_information7 => l_pay_info_tab(l_payroll_id).l_employer_name,
3071 p_action_information8 => l_pay_info_tab(l_payroll_id).l_employer_address);
3072 END IF;
3073
3074 hr_utility.set_location('Archiving GB EMPLOYEE DETAILS', 50);
3075
3076 IF pay_gb_eoy_magtape.validate_input(UPPER(l_first_name), 'P11D_EDI') > 0
3077 THEN
3078 l_error_assignment := TRUE;
3079 l_error := '1';
3080 pay_core_utils.push_message(800, 'PER_GB_P11D_78058_ASG_ERR_MSG', 'F');
3081 pay_core_utils.push_token('TEXT', ': Magtape Character Validation has failed for First Name value ' || l_first_name);
3082 END IF;
3083
3084 IF pay_gb_eoy_magtape.validate_input(UPPER(l_middle_name), 'P11D_EDI') > 0
3085 THEN
3086 l_error_assignment := TRUE;
3087 l_error := '1';
3088 pay_core_utils.push_message(800, 'PER_GB_P11D_78058_ASG_ERR_MSG', 'F');
3089 pay_core_utils.push_token('TEXT', ': Magtape Character Validation has failed for Middle Name value ' || l_middle_name);
3090 END IF;
3091
3092 IF pay_gb_eoy_magtape.validate_input(UPPER(l_last_name), 'P11D_EDI') > 0
3093 THEN
3094 l_error_assignment := TRUE;
3095 l_error := '1';
3096 pay_core_utils.push_message(800, 'PER_GB_P11D_78058_ASG_ERR_MSG', 'F');
3097 pay_core_utils.push_token('TEXT', ': Magtape Character Validation has failed for Last Name value ' || l_last_name);
3098 END IF;
3099
3100
3101 -- EOY 2008
3102 IF to_number(g_param_rep_run) = 2008 THEN
3103 IF l_ni_number is null THEN
3104 IF l_sex is null THEN
3105 l_error_assignment := TRUE;
3106 l_error := '1';
3107 pay_core_utils.push_message(800, 'PER_GB_P11D_78058_ASG_ERR_MSG', 'F');
3108 pay_core_utils.push_token('TEXT', ': Magtape Validation has failed for missing gender value ' );
3109 END IF ;
3110
3111 IF l_date_of_birth is not null THEN
3112 IF l_date_of_birth > sysdate THEN
3113 l_error_assignment := TRUE;
3114 l_error := '1';
3115 pay_core_utils.push_message(800, 'PER_GB_P11D_78058_ASG_ERR_MSG', 'F');
3116 pay_core_utils.push_token('TEXT', ': Magtape Validation has failed for date of birth. The value must be the current date or an earlier date. ' );
3117 END IF;
3118 ELSE
3119 l_date_of_birth := to_date('19010101','YYYYMMDD') ;
3120 END IF ;
3121 END IF ;
3122 END IF;
3123
3124 IF l_error <> '1'
3125 THEN
3126 pay_action_information_api.create_action_information(
3127 p_action_information_id => l_action_info_id,
3128 p_action_context_id => p_assactid,
3129 p_action_context_type => 'AAP',
3130 p_object_version_number => l_ovn,
3131 p_effective_date => fnd_date.canonical_to_date(g_param_benefit_end_date),
3132 p_source_id => NULL,
3133 p_source_text => NULL,
3134 p_action_information_category => 'GB EMPLOYEE DETAILS',
3135 p_action_information1 => NULL,
3136 p_action_information2 => NULL,
3137 p_action_information3 => NULL,
3138 p_action_information4 => l_director_flag,
3139 p_action_information5 => l_payroll_id,
3140 p_action_information6 => l_first_name,
3141 p_action_information7 => l_middle_name,
3142 p_action_information8 => l_last_name,
3143 p_action_information9 => l_pay_info_tab(l_payroll_id).l_employer_name,
3144 p_action_information10 => l_person_id,
3145 p_action_information11 => l_assignment_number,
3146 p_action_information12 => l_ni_number,
3147 p_action_information13 => l_pay_info_tab(l_payroll_id).l_employers_ref_no,
3148 p_action_information15 => fnd_date.date_to_canonical(l_date_of_birth),
3149 p_action_information17 => l_sex);
3150
3151 check_assignment_latest_info(
3152 p_assignment_id => l_assignment_id,
3153 p_assignment_act_id => p_assactid,
3154 p_ben_end_date => fnd_date.canonical_to_date(g_param_benefit_end_date),
3155 p_asg_end_date => least(nvl(l_actual_termination_date,
3156 fnd_date.canonical_to_date(g_param_benefit_end_date)),
3157 fnd_date.canonical_to_date(g_param_benefit_end_date)));
3158 validate_employee_address(p_assignment_action_id => p_assactid,
3159 p_assignment_name => l_assignment_name,
3160 p_assignment_number => l_assignment_number);
3161 END IF;
3162
3163 -- l_index := g_tab_ben_detail.FIRST;
3164 l_index := l_first_index;
3165
3166 -- loop thru all the benefits
3167 -- resetting globals as earlier values should not be used!
3168 l_ret := per_formula_functions.remove_globals;
3169 -- hr_utility.trace_on(null,'ARCH');
3170
3171 WHILE l_index <= g_tab_ben_detail.LAST
3172 LOOP
3173
3174 -- hr_utility.TRACE_on(null,'ERR');
3175 l_element_name := g_tab_ben_detail(l_index).element_name;
3176 hr_utility.set_location('Element Name '|| l_element_name, 20);
3177 hr_utility.set_location('Element Entry id :'|| TO_CHAR(g_tab_ben_detail(l_index).element_entry_id), 20);
3178 hr_utility.TRACE('Inside assignment id '|| g_tab_ben_detail(l_index).assignment_id);
3179 l_counter := 0;
3180
3181 -- Setting the array l_user_inputs with the input values from
3182 -- element entry value
3183 hr_utility.set_location('Setting the input values ', 30);
3184
3185
3186 FOR entry_values IN csr_element_entry_values(
3187 g_tab_ben_detail(l_index).element_entry_id,
3188 g_tab_ben_detail(l_index).element_type_id,
3189 g_tab_ben_detail(l_index).effective_start_date)
3190 LOOP
3191 hr_utility.trace('entry_values.NAME ' || entry_values.NAME);
3192 l_counter := l_counter + 1;
3193 l_user_inputs(l_counter).NAME := TRIM(entry_values.NAME);
3194 l_user_inputs(l_counter).VALUE := TRIM(entry_values.screen_entry_value);
3195
3196 IF l_user_inputs(l_counter).NAME = 'BENEFIT_END_DATE'
3197 THEN
3198 l_benefit_end_date := l_user_inputs(l_counter).VALUE;
3199 END IF;
3200 IF l_user_inputs(l_counter).NAME = 'BENEFIT_START_DATE'
3201 THEN
3202 l_benefit_start_date := l_user_inputs(l_counter).VALUE;
3203 END IF;
3204 END LOOP;
3205
3206 -- check if the ben st and ben end falls within the tax year
3207 If fnd_date.canonical_to_date(l_benefit_start_date) <
3208 to_date('06-04-' ||to_char(to_number(g_param_rep_run)-1),'dd-mm-yyyy') or
3209 fnd_date.canonical_to_date(l_benefit_start_date) >
3210 to_date('05-04-' ||g_param_rep_run,'dd-mm-yyyy')
3211 Then
3212 l_error_assignment := TRUE;
3213 l_error := '1';
3214 pay_core_utils.push_message(800, 'HR_78076_P11D_DATE_PARAM_ERR', 'F');
3215 pay_core_utils.push_token('ELEMENT_NAME',l_element_name);
3216 pay_core_utils.push_token('NAME', 'Benefit Start Date in ' || l_element_name);
3217 pay_core_utils.push_token('VAl1','06-APR-' ||to_char(to_number(g_param_rep_run)-1));
3218 pay_core_utils.push_token('VAl2', '05-APR-' ||g_param_rep_run);
3219 End if;
3220
3221 If fnd_date.canonical_to_date(l_benefit_end_date) <
3222 to_date('06-04-' ||to_char(to_number(g_param_rep_run)-1),'dd-mm-yyyy') or
3223 fnd_date.canonical_to_date(l_benefit_end_date) >
3224 to_date('05-04-' ||g_param_rep_run,'dd-mm-yyyy')
3225 then
3226 l_error_assignment := TRUE;
3227 l_error := '1';
3228
3229 pay_core_utils.push_message(800, 'HR_78076_P11D_DATE_PARAM_ERR', 'F');
3230 pay_core_utils.push_token('ELEMENT_NAME',l_element_name);
3231 pay_core_utils.push_token('NAME', 'Benefit End Date in ' || l_element_name);
3232 pay_core_utils.push_token('VAl1','06-APR-' ||to_char(to_number(g_param_rep_run)-1));
3233 pay_core_utils.push_token('VAl2', '05-APR-' ||g_param_rep_run);
3234 End if;
3235 -- Setting the array l_user_inputs with the input values from
3236 -- element entry flexfield
3237 hr_utility.trace(' classification_name ' ||g_tab_ben_detail(l_index).classification_name);
3238 hr_utility.trace('l_counter b4 entry flex values ' || l_counter);
3239
3240 FOR entry_flex_values IN csr_element_entry_flex_values(g_tab_ben_detail(l_index).classification_name)
3241 LOOP
3242 l_counter := l_counter + 1;
3243 l_user_inputs(l_counter).NAME := TRIM(entry_flex_values.NAME);
3244 l_sql_stmt := 'Select ' || entry_flex_values.application_column_name || ' from ';
3245 l_sql_stmt := l_sql_stmt || ' pay_element_entries_f WHERE ';
3246 l_sql_stmt := l_sql_stmt || ' element_entry_id = :element_entry_id ';
3247 l_sql_stmt := l_sql_stmt || ' AND EFFECTIVE_START_DATE = :effec_st_date ';
3248 EXECUTE IMMEDIATE l_sql_stmt
3249 INTO l_user_inputs(l_counter).VALUE
3250 USING IN g_tab_ben_detail(l_index).element_entry_id, g_tab_ben_detail(l_index).effective_start_date;
3251 hr_utility.trace(' l_counter ' || l_counter);
3252 hr_utility.trace(' Name --' ||l_user_inputs(l_counter).name || '--');
3253 hr_utility.trace(' Value ' ||l_user_inputs(l_counter).VALUE);
3254 END LOOP;
3255
3256 -- hr_utility.trace_on(null,'NONIV');
3257 hr_utility.trace(' Out of loop');
3258 hr_utility.trace('l_counter at b4 extra arch items ' || l_counter);
3259
3260 For extra_archive_items in csr_non_iv_action_info_items (
3261 g_tab_ben_detail(l_index).element_entry_id,
3262 g_tab_ben_detail(l_index).element_type_id,
3263 g_tab_ben_detail(l_index).effective_start_date,
3264 UPPER(l_element_name),
3265 g_tab_ben_detail(l_index).classification_name
3266 )
3267 loop
3268 l_counter := l_counter + 1;
3269 l_user_inputs(l_counter).NAME := TRIM(extra_archive_items.NAME);
3270 l_user_inputs(l_counter).VALUE := null;
3271 hr_utility.trace('extra arch NAME ' || extra_archive_items.NAME);
3272 hr_utility.trace('user NAME ' ||l_user_inputs(l_counter).NAME);
3273 end loop;
3274
3275 hr_utility.trace('After Using pl/sql table');
3276
3277 hr_utility.trace('l_counter after extra arch items ' || l_counter);
3278 -- hr_utility.trace_off;
3279 -- Setting the Business group id as this is needed for Car Validation!
3280 l_counter := l_counter + 1;
3281 l_user_inputs(l_counter).NAME := 'BUSINESS_GROUP_ID';
3282 l_user_inputs(l_counter).VALUE := TO_CHAR(g_param_business_group_id);
3283 -- fetching the formula id the ff table is cached in memory and is
3284 -- referenced here
3285 l_formula_id := find_exec_formula(
3286 l_element_name,
3287 fnd_date.canonical_to_date(l_benefit_end_date),
3288 l_formula_effective_start_date);
3289 hr_utility.set_location('Formula id '|| l_formula_id, 25);
3290
3291 IF l_formula_id IS NOT NULL
3292 THEN
3293 hr_utility.set_location('Initializing the formula ', 40);
3294 Begin
3295 ff_exec.init_formula(l_formula_id, l_formula_effective_start_date, l_inputs, l_outputs);
3296 Exception
3297 when OTHERS then
3298 l_error_assignment := TRUE;
3299 l_error := '1';
3300 pay_core_utils.push_message(800, 'HR_78055_GB_P11D_FORMULA_ERR', 'F');
3301 pay_core_utils.push_token('ELEMENT_NAME', l_element_name);
3302 hr_utility.set_location('Nothing to execute ! '|| ' :' || l_proc, 70);
3303 -- Remove ALL GLOBALS
3304 l_ret := per_formula_functions.remove_globals;
3305 RAISE error_found;
3306 End;
3307 -- Now passing the l_user_inputs to the array l_inputs
3308 IF l_inputs.COUNT > 0 AND l_user_inputs.COUNT > 0
3309 THEN
3310 FOR l_outer IN l_inputs.FIRST .. l_inputs.LAST
3311 LOOP
3312 FOR l_inner IN l_user_inputs.FIRST .. l_user_inputs.LAST
3313 LOOP
3314 IF l_inputs(l_outer).NAME = l_user_inputs(l_inner).NAME
3315 AND (l_user_inputs(l_inner).NAME IS NOT NULL OR l_user_inputs(l_inner).NAME <> '')
3316 THEN
3317 hr_utility.TRACE('l_outer ' || l_outer);
3318 hr_utility.TRACE('l_inner ' || l_inner);
3319 hr_utility.TRACE(' l_inputs(l_outer).NAME --' || l_inputs(l_outer).NAME || '--');
3320 hr_utility.TRACE(' l_user_inputs(l_inner).NAME -' || l_user_inputs(l_inner).NAME || '-');
3321 hr_utility.TRACE(' l_user_inputs(l_inner).VALUE ' || l_user_inputs(l_inner).VALUE);
3322 l_inputs(l_outer).VALUE := l_user_inputs(l_inner).VALUE;
3323 hr_utility.TRACE(' Name : '|| l_inputs(l_outer).NAME || ' Value: ' || l_inputs(l_outer).VALUE);
3324 EXIT;
3325 END IF;
3326 if l_inputs(l_outer).NAME = 'EDI_VALIDATION' then
3327 l_inputs(l_outer).VALUE := l_edi_validation;
3328 end if;
3329 END LOOP;
3330 END LOOP;
3331 END IF; -- end if for setting user inputs
3332 -- hr_utility.trace_on(null,'CAR');
3333 hr_utility.set_location('Running the formula ', 20);
3334 ff_exec.run_formula(l_inputs, l_outputs);
3335
3336 -- Tapping the output from the formula using the l_outputs array
3337 hr_utility.set_location('After Running the formula ', 20);
3338 FOR l_counter IN l_outputs.FIRST .. l_outputs.LAST
3339 LOOP
3340 IF l_outputs(l_counter).NAME = 'L_ERROR' AND l_outputs(l_counter).VALUE = '1'
3341 THEN
3342 hr_utility.set_location('Error in Element Entry id value '|| TO_CHAR(g_tab_ben_detail(l_index).element_entry_id),65);
3343 l_error := '1';
3344 ELSIF l_outputs(l_counter).NAME = 'L_WARN' AND l_outputs(l_counter).VALUE = '1'
3345 THEN
3346 hr_utility.set_location('Warning in Element Entry id value '|| TO_CHAR(g_tab_ben_detail(l_index).element_entry_id),66);
3347 l_warn := '1';
3348 ELSIF (INSTR(UPPER(l_outputs(l_counter).NAME), 'MSG') > 0
3349 OR INSTR(UPPER(l_outputs(l_counter).NAME), 'MESSAGE') > 0
3350 OR (INSTR(UPPER(l_outputs(l_counter).NAME), 'ERR') > 0
3351 AND INSTR(UPPER(l_outputs(l_counter).NAME), 'L_ERROR') = 0) )
3352 AND (l_outputs(l_counter).VALUE <> '' OR l_outputs(l_counter).VALUE IS NOT NULL)
3353 THEN
3354 -- this bit needs to be looked for proper error msgs
3355 --pay_core_utils.push_message(800, 'PER_GB_P11D_78058_ASG_ERR_MSG', 'A');
3356 -- if the message is a warning
3357 if ( instr(upper(l_outputs(l_counter).NAME), 'WARN') > 0 ) then
3358 pay_core_utils.push_message(800, 'PER_GB_P11D_78058_ASG_ERR_MSG', 'W');
3359 pay_core_utils.push_token('TEXT', SUBSTR(l_outputs(l_counter).VALUE, 1, 200) );
3360 else
3361 l_error := '1';
3362 end if;
3363 /*
3364 else
3365 pay_core_utils.push_message(800, 'PER_GB_P11D_78058_ASG_ERR_MSG', 'F');
3366 pay_core_utils.push_token('TEXT', l_element_name ||': ' || SUBSTR(l_outputs(l_counter).VALUE, 2, 100) );
3367 end if;
3368 */
3369 hr_utility.TRACE('Error'|| l_outputs(l_counter).VALUE);
3370 END IF;
3371 hr_utility.TRACE(l_outputs(l_counter).NAME || ': ' || l_outputs(l_counter).VALUE);
3372 END LOOP;
3373 -- if the values of input values have changed within the formula
3374 -- then they are passed as items of table l_output
3375
3376 -- this loop checks if the name of l_output is same as that
3377 -- of l_inputs, if they are the same then it means that
3378 -- a new value has been returned from the formula
3379
3380 -- hr_utility.tracE_on(null,'ARCH');
3381 hr_utility.set_location('Checking input values with that of values returned by ff', 30);
3382
3383 FOR l_outer IN l_user_inputs.FIRST .. l_user_inputs.LAST
3384 LOOP
3385 FOR l_inner IN l_outputs.FIRST .. l_outputs.LAST
3386 LOOP
3387 -- hr_utility.trace('l_outputs(l_inner).NAME ' || l_outputs(l_inner).NAME);
3388 -- hr_utility.trace('l_outputs(l_inner).VALUE ' || l_outputs(l_inner).VALUE);
3389 -- hr_utility.trace('l_user_inputs(l_outer).NAME ' ||l_user_inputs(l_outer).NAME);
3390 IF l_user_inputs(l_outer).NAME = l_outputs(l_inner).NAME
3391 THEN
3392 l_user_inputs(l_outer).VALUE := l_outputs(l_inner).VALUE;
3393 EXIT;
3394 END IF;
3395 END LOOP;
3396 END LOOP;
3397
3398 hr_utility.set_location('Checking Magtape Validation', 35);
3399 -- hr_utility.tracE_off;
3400 -- check_magtape_validation;
3401 FOR l_inner IN l_user_inputs.FIRST .. l_user_inputs.LAST
3402 LOOP
3403 IF pay_gb_eoy_magtape.validate_input(UPPER(l_user_inputs(l_inner).VALUE) ) > 0
3404 THEN
3405 IF INSTR(UPPER(l_user_inputs(l_inner).NAME), 'DATE') > 0
3406 THEN
3407 NULL; -- ignore the failure as the dates validatred by the above process is incrrect
3408 -- and since our input values have date defined , the data will be correct.
3409 ELSIF UPPER(l_user_inputs(l_inner).NAME) = 'NOTES'
3410 THEN
3411 NULL; -- ignore the failure as NOTES is info field only
3412 ELSIF (l_user_inputs(l_inner).VALUE = 'PRECIOUS_METALS' AND l_user_inputs(l_inner).NAME = 'ASSET_TYPE')
3413 OR (l_user_inputs(l_inner).NAME = 'EXPENSE_TYPE'
3414 AND (l_user_inputs(l_inner).VALUE = 'PRSN_INCIDENTAL_EXPENSES'
3415 OR l_user_inputs(l_inner).VALUE = 'TELE_CALLS'
3416 OR l_user_inputs(l_inner).VALUE = 'TELE_RENTAL'
3417 OR l_user_inputs(l_inner).VALUE = 'WORK_DONE_AT_HOME') )
3418 OR (l_user_inputs(l_inner).NAME = 'FUEL_TYPE'
3419 AND (l_user_inputs(l_inner).VALUE = 'BATTERY_ELECTRIC'
3420 OR l_user_inputs(l_inner).VALUE = 'HYBRID_ELECTRIC'
3421 OR l_user_inputs(l_inner).VALUE = 'LPG_CNG'
3422 OR l_user_inputs(l_inner).VALUE = 'LPG_CNG_PETROL'
3423 OR l_user_inputs(l_inner).VALUE = 'EURO_IV_DIESEL'
3424 OR l_user_inputs(l_inner).VALUE = 'LPG_CNG_PETROL_CONV') )
3425 -- these are excluded as these are codes which have _ in them and the validation fails
3426 -- because of this
3427 THEN
3428 NULL;
3429 ELSE
3430 hr_utility.set_location('Magtape Validation failure', 35);
3431 l_error_assignment := TRUE;
3432 l_error := '1';
3433 pay_core_utils.push_message(800, 'PER_GB_P11D_78058_ASG_ERR_MSG', 'F');
3434 pay_core_utils.push_token('TEXT', ': Magtape Character Validation has failed for '
3435 ||l_user_inputs(l_inner).NAME || ' value ' || l_user_inputs(l_inner).VALUE);
3436 END IF; -- checking exceptions for mag tape validations
3437 END IF; -- end if for magtape validation error
3438 END LOOP; -- loop which runs thru inpout values
3439
3440 l_action_context_id := p_assactid;
3441
3442 IF l_error = '0'
3443 THEN
3444 hr_utility.set_location('Creating archive = '|| p_assactid, 20);
3445 hr_utility.set_location('p_assactid = '|| p_assactid, 20);
3446 l_col1_val := NULL; l_col2_val := NULL;
3447 l_col3_val := NULL; l_col4_val := NULL;
3448 l_col5_val := NULL; l_col6_val := NULL;
3449 l_col7_val := NULL; l_col8_val := NULL;
3450 l_col9_val := NULL; l_col10_val := NULL;
3451 l_col11_val := NULL; l_col12_val := NULL;
3452 l_col13_val := NULL; l_col14_val := NULL;
3453 l_col15_val := NULL; l_col16_val := NULL;
3454 l_col17_val := NULL; l_col18_val := NULL;
3455 l_col19_val := NULL; l_col20_val := NULL;
3456 l_col21_val := NULL; l_col22_val := NULL;
3457 l_col23_val := NULL; l_col24_val := NULL;
3458 l_col25_val := NULL; l_col26_val := NULL;
3459 l_col27_val := NULL; l_col28_val := NULL;
3460 l_col29_val := NULL; l_col30_val := NULL;
3461 FOR action_info IN csr_action_info_flex_fields(UPPER(l_element_name) )
3462 LOOP
3463 FOR l_inner IN l_user_inputs.FIRST .. l_user_inputs.LAST
3464 LOOP
3465 -- as the END_USER_COLUMN_NAME on table fnd_descr_flex_column_usages
3466 -- can be max fo size 30 and we are comapring the END_USER_COLUMN_NAME
3467 -- filed with the input value name , we should trim it dowjn to 30
3468 -- so that then first 30 will now becoem the matching
3469 -- criteria.
3470 hr_utility.trace(l_user_inputs(l_inner).NAME || ' : ' || l_user_inputs(l_inner).VALUE);
3471 IF action_info.NAME = SUBSTR(l_user_inputs(l_inner).NAME, 1, 30)
3472 THEN
3473 IF action_info.application_column_name = 'ACTION_INFORMATION3'
3474 THEN
3475 l_col3_val := l_user_inputs(l_inner).VALUE;
3476 END IF;
3477
3478 IF action_info.application_column_name = 'ACTION_INFORMATION4'
3479 THEN
3480 l_col4_val := l_user_inputs(l_inner).VALUE;
3481 END IF;
3482
3483 IF action_info.application_column_name = 'ACTION_INFORMATION5'
3484 THEN
3485 l_col5_val := l_user_inputs(l_inner).VALUE;
3486 END IF;
3487
3488 IF action_info.application_column_name = 'ACTION_INFORMATION6'
3489 THEN
3490 l_col6_val := l_user_inputs(l_inner).VALUE;
3491 END IF;
3492
3493 IF action_info.application_column_name = 'ACTION_INFORMATION7'
3494 THEN
3495 l_col7_val := l_user_inputs(l_inner).VALUE;
3496 END IF;
3497
3498 IF action_info.application_column_name = 'ACTION_INFORMATION8'
3499 THEN
3500 l_col8_val := l_user_inputs(l_inner).VALUE;
3501 END IF;
3502
3503 IF action_info.application_column_name = 'ACTION_INFORMATION9'
3504 THEN
3505 l_col9_val := l_user_inputs(l_inner).VALUE;
3506 END IF;
3507
3508 IF action_info.application_column_name = 'ACTION_INFORMATION10'
3509 THEN
3510 l_col10_val := l_user_inputs(l_inner).VALUE;
3511 END IF;
3512
3513 IF action_info.application_column_name = 'ACTION_INFORMATION11'
3514 THEN
3515 l_col11_val := l_user_inputs(l_inner).VALUE;
3516 END IF;
3517
3518 IF action_info.application_column_name = 'ACTION_INFORMATION12'
3519 THEN
3520 l_col12_val := l_user_inputs(l_inner).VALUE;
3521 END IF;
3522
3523 IF action_info.application_column_name = 'ACTION_INFORMATION13'
3524 THEN
3525 l_col13_val := l_user_inputs(l_inner).VALUE;
3526 END IF;
3527
3528 IF action_info.application_column_name = 'ACTION_INFORMATION14'
3529 THEN
3530 l_col14_val := l_user_inputs(l_inner).VALUE;
3531 END IF;
3532
3533 IF action_info.application_column_name = 'ACTION_INFORMATION15'
3534 THEN
3535 l_col15_val := l_user_inputs(l_inner).VALUE;
3536 END IF;
3537
3538 IF action_info.application_column_name = 'ACTION_INFORMATION16'
3539 THEN
3540 l_col16_val := l_user_inputs(l_inner).VALUE;
3541 END IF;
3542
3543 IF action_info.application_column_name = 'ACTION_INFORMATION17'
3544 THEN
3545 l_col17_val := l_user_inputs(l_inner).VALUE;
3546 END IF;
3547
3548 IF action_info.application_column_name = 'ACTION_INFORMATION18'
3549 THEN
3550 l_col18_val := l_user_inputs(l_inner).VALUE;
3551 END IF;
3552
3553 IF action_info.application_column_name = 'ACTION_INFORMATION19'
3554 THEN
3555 l_col19_val := l_user_inputs(l_inner).VALUE;
3556 END IF;
3557
3558 IF action_info.application_column_name = 'ACTION_INFORMATION20'
3559 THEN
3560 l_col20_val := l_user_inputs(l_inner).VALUE;
3561 END IF;
3562
3563 IF action_info.application_column_name = 'ACTION_INFORMATION21'
3564 THEN
3565 l_col21_val := l_user_inputs(l_inner).VALUE;
3566 END IF;
3567
3568 IF action_info.application_column_name = 'ACTION_INFORMATION22'
3569 THEN
3570 l_col22_val := l_user_inputs(l_inner).VALUE;
3571 END IF;
3572
3573 IF action_info.application_column_name = 'ACTION_INFORMATION23'
3574 THEN
3575 l_col23_val := l_user_inputs(l_inner).VALUE;
3576 END IF;
3577
3578 IF action_info.application_column_name = 'ACTION_INFORMATION24'
3579 THEN
3580 l_col24_val := l_user_inputs(l_inner).VALUE;
3581 END IF;
3582
3583 IF action_info.application_column_name = 'ACTION_INFORMATION25'
3584 THEN
3585 l_col25_val := l_user_inputs(l_inner).VALUE;
3586 IF UPPER(l_element_name) = 'CAR AND CAR FUEL 2003_04' THEN
3587 -- EOY 2008.
3588 -- For the element 'Car and Car Fuel 2003_04', archiving Valid_Benefit_End_Date_flag
3589 -- with Fuel_Benefit separated by a delimiter ':', as all the 30 fields against
3590 -- context 'CAR AND CAR FUEL 2003_04' in 'Action Information DF' were
3591 -- already archived. This flag will be used in EDI, to decide
3592 -- if we need to print '5-Apr' as Date_Car_Available_To DTM3 489
3593 FOR l_inner_temp IN l_user_inputs.FIRST .. l_user_inputs.LAST
3594 LOOP
3595 IF UPPER(l_user_inputs(l_inner_temp).NAME) = 'VALID_BENEFIT_END_DATE' THEN
3596 l_col25_val := l_col25_val || ':' || nvl(l_user_inputs(l_inner_temp).VALUE,'N');
3597 EXIT ;
3598 END IF ;
3599 END LOOP ;
3600 END IF ;
3601 END IF;
3602
3603 IF action_info.application_column_name = 'ACTION_INFORMATION26'
3604 THEN
3605 l_col26_val := l_user_inputs(l_inner).VALUE;
3606 END IF;
3607
3608 IF action_info.application_column_name = 'ACTION_INFORMATION27'
3609 THEN
3610 l_col27_val := l_user_inputs(l_inner).VALUE;
3611 END IF;
3612
3613 IF action_info.application_column_name = 'ACTION_INFORMATION28'
3614 THEN
3615 l_col28_val := l_user_inputs(l_inner).VALUE;
3616 END IF;
3617
3618 IF action_info.application_column_name = 'ACTION_INFORMATION29'
3619 THEN
3620 l_col29_val := l_user_inputs(l_inner).VALUE;
3621 END IF;
3622
3623 IF action_info.application_column_name = 'ACTION_INFORMATION30'
3624 THEN
3625 l_col30_val := l_user_inputs(l_inner).VALUE;
3626 END IF;
3627
3628 EXIT;
3629 END IF;
3630 END LOOP;
3631 END LOOP;
3632 hr_utility.set_location('calling the create api', 20);
3633 pay_action_information_api.create_action_information(
3634 p_action_information_id => l_action_info_id,
3635 p_action_context_id => l_action_context_id,
3636 p_action_context_type => 'AAP',
3637 p_object_version_number => l_ovn,
3638 p_source_id => NULL,
3639 p_source_text => NULL,
3640 p_effective_date => fnd_date.canonical_to_date(g_param_benefit_end_date),
3641 p_action_information_category => SUBSTR(UPPER(l_element_name), 1, 30),
3642 p_action_information1 => g_tab_ben_detail(l_index).element_entry_id,
3643 p_action_information2 => g_tab_ben_detail(l_index).effective_start_date,
3644 p_action_information3 => l_col3_val,
3645 p_action_information4 => l_col4_val,
3646 p_action_information5 => l_col5_val,
3647 p_action_information6 => l_col6_val,
3648 p_action_information7 => l_col7_val,
3649 p_action_information8 => l_col8_val,
3650 p_action_information9 => l_col9_val,
3651 p_action_information10 => l_col10_val,
3652 p_action_information11 => l_col11_val,
3653 p_action_information12 => l_col12_val,
3654 p_action_information13 => l_col13_val,
3655 p_action_information14 => l_col14_val,
3656 p_action_information15 => l_col15_val,
3657 p_action_information16 => l_col16_val,
3658 p_action_information17 => l_col17_val,
3659 p_action_information18 => l_col18_val,
3660 p_action_information19 => l_col19_val,
3661 p_action_information20 => l_col20_val,
3662 p_action_information21 => l_col21_val,
3663 p_action_information22 => l_col22_val,
3664 p_action_information23 => l_col23_val,
3665 p_action_information24 => l_col24_val,
3666 p_action_information25 => l_col25_val,
3667 p_action_information26 => l_col26_val,
3668 p_action_information27 => l_col27_val,
3669 p_action_information28 => l_col28_val,
3670 p_action_information29 => l_col29_val,
3671 p_action_information30 => l_col30_val);
3672 hr_utility.trace('After calling Create api');
3673 ELSE -- error is non 0
3674 l_error_assignment := TRUE;
3675 END IF;
3676 ELSE -- formual id is null
3677 l_error_assignment := TRUE;
3678 l_error := '1';
3679 pay_core_utils.push_message(800, 'HR_78055_GB_P11D_FORMULA_ERR', 'F');
3680 pay_core_utils.push_token('ELEMENT_NAME', l_element_name);
3681 hr_utility.set_location('Nothing to execute ! '|| ' :' || l_proc, 70);
3682 END IF;
3683
3684 hr_utility.trace('Looking at Next value!');
3685
3686 l_index := g_tab_ben_detail.NEXT(l_index);
3687
3688 hr_utility.trace('l_index=' || l_index);
3689
3690 if g_tab_ben_detail.EXISTS(l_index) then
3691 hr_utility.trace('Next item exists');
3692 if g_tab_ben_detail(l_index).assignment_action_id <> p_assactid
3693 then
3694 hr_utility.trace('Next item does not match');
3695 exit; -- come out of the loop
3696 end if;
3697 else
3698 hr_utility.trace('Next item does not exist');
3699 hr_utility.trace('Coming out loop for all benefits');
3700 exit;
3701 end if;
3702 END LOOP; -- loop for all benefits
3703
3704 -- this validates the sum within the assignment
3705 hr_utility.trace('Calling validate_values ');
3706
3707 validate_values;
3708 IF l_error_assignment
3709 THEN
3710 hr_utility.set_location('Failing Assignment '|| ' :' || l_proc, 70);
3711 --log_message;
3712 -- Remove ALL GLOBALS
3713 l_ret := per_formula_functions.remove_globals;
3714 RAISE error_found;
3715 ELSE
3716 -- insert rows which are sums for all values in ffs
3717 hr_utility.trace('Inserting summed records ');
3718 insert_sum_records(p_assactid);
3719 -- Remove ALL GLOBALS
3720 l_ret := per_formula_functions.remove_globals;
3721 /* bug fix for 3485256
3722 l_index := g_tab_ben_detail.FIRST;
3723 */
3724 l_index := l_first_index;
3725 l_tab_counter := 0;
3726 l_processed_assign_actions := c_proc_assign_actions_null;
3727 --check if multiple assignments exist
3728 hr_utility.trace('Checing if multiple assign exists');
3729 hr_utility.trace('person_id ' || g_tab_ben_detail(l_index).person_id);
3730 hr_utility.trace('l_employers_ref_no ' || l_pay_info_tab(l_payroll_id).l_employers_ref_no);
3731 hr_utility.trace('l_employer_name ' || l_pay_info_tab(l_payroll_id).l_employer_name);
3732
3733 FOR assignment_list IN assignments_to_sum(
3734 g_tab_ben_detail(l_index).person_id,
3735 l_pay_info_tab(l_payroll_id).l_employers_ref_no,
3736 l_pay_info_tab(l_payroll_id).l_employer_name)
3737 LOOP
3738 -- if yes sum them
3739 hr_utility.trace('multiple assign exists.....');
3740 hr_utility.trace('Assign act id ' || assignment_list.assignment_action_id );
3741 fetch_values_and_set_globals(assignment_list.assignment_action_id);
3742 l_tab_counter := l_tab_counter + 1;
3743 l_processed_assign_actions(l_tab_counter) := assignment_list.assignment_action_id;
3744 END LOOP;
3745
3746 IF l_tab_counter > 1
3747 THEN
3748 -- miltiple assignmenst exists validate and update all of them
3749 -- if validation fails then write log and raise error
3750 hr_utility.trace('Calling val values for multiple assign ..');
3751 validate_values;
3752 IF l_error = '1'
3753 THEN
3754 hr_utility.set_location('Failing Assignment '|| ' :' || l_proc, 90);
3755 -- Remove ALL GLOBALS
3756 l_ret := per_formula_functions.remove_globals;
3757 RAISE error_found;
3758 ELSE
3759 l_tab_counter := l_processed_assign_actions.FIRST;
3760 hr_utility.trace('Calling Update values..');
3761 WHILE l_tab_counter <= l_processed_assign_actions.LAST
3762 LOOP
3763 hr_utility.trace('Update values ' || l_processed_assign_actions(l_tab_counter) );
3764 update_values(l_processed_assign_actions(l_tab_counter) );
3765 l_tab_counter := l_processed_assign_actions.NEXT(l_tab_counter);
3766 END LOOP;
3767 END IF;
3768 END IF;
3769 END IF;
3770 l_ret := per_formula_functions.remove_globals;
3771 hr_utility.trace('nearing end');
3772 EXCEPTION
3773 WHEN error_found
3774 THEN
3775 l_ret := per_formula_functions.remove_globals;
3776 -- the error will be reported in Deinitialization proc
3777 -- write_error_to_log;
3778 g_set_warning := TRUE;
3779 hr_utility.raise_error;
3780 END archive_code;
3781
3782 PROCEDURE write_log(employers_name VARCHAR2, person_name VARCHAR2, employee_num VARCHAR2, err_text VARCHAR2)
3783 IS
3784 BEGIN
3785 fnd_file.put_line(
3786 fnd_file.output,
3787 RPAD(NVL(employers_name, ' '), 20) || RPAD(NVL(person_name, ' '), 25) || RPAD(NVL(employee_num, ' '), 15) || err_text);
3788 END;
3789
3790 PROCEDURE deinitialization_code(pactid IN NUMBER)
3791 IS
3792 l_proc CONSTANT VARCHAR2(50) := g_package || ' deinitialization_code';
3793 l_counter number;
3794 -- 4248907 Perf fix - csr_incorrect_ni_percentage - Broken into 2 separate cursors
3795 CURSOR csr_incorrect_ni_num IS
3796 SELECT pai.action_information7 action_information7, COUNT(1) temp_num
3797 FROM pay_payroll_actions ppa,
3798 pay_assignment_actions paa,
3799 pay_action_information pai,
3800 pay_action_information pai_emp
3801 WHERE ppa.payroll_action_id = pactid
3802 AND paa.payroll_action_id = ppa.payroll_action_id
3803 AND pai.action_context_id = paa.assignment_action_id
3804 AND pai.action_information_category = 'EMEA PAYROLL INFO'
3805 AND pai.action_context_type = 'AAP'
3806 AND pai_emp.action_context_id = paa.assignment_action_id
3807 AND pai_emp.action_information_category = 'EMPLOYEE DETAILS'
3808 AND pai_emp.action_context_type = 'AAP'
3809 AND (SUBSTR(pai_emp.action_information4, 1, 2) = 'TN'
3810 OR
3811 pai_emp.action_information4 IS NULL)
3812 GROUP BY pai.action_information7;
3813
3814 CURSOR csr_total_num(p_employer_name varchar2) IS
3815 SELECT pai.action_information7 action_information7, COUNT(1) tot_num
3816 FROM pay_payroll_actions ppa,
3817 pay_assignment_actions paa,
3818 pay_action_information pai
3819 WHERE ppa.payroll_action_id = pactid
3820 AND paa.payroll_action_id = ppa.payroll_action_id
3821 AND pai.action_context_id = paa.assignment_action_id
3822 AND pai.action_information_category = 'EMEA PAYROLL INFO'
3823 AND pai.action_context_type = 'AAP'
3824 AND pai.action_information7 = p_employer_name
3825 GROUP BY pai.action_information7;
3826
3827 CURSOR csr_expenses_payment_chk
3828 IS
3829 SELECT COUNT(DISTINCT pai_ben.action_information10)
3830 FROM pay_payroll_actions ppa,
3831 pay_assignment_actions paa,
3832 pay_action_information pai_ben
3833 WHERE ppa.payroll_action_id = pactid
3834 AND ppa.payroll_action_id = paa .payroll_action_id
3835 AND pai_ben.action_context_id = paa.assignment_action_id
3836 AND pai_ben.action_information_category = 'EXPENSES PAYMENTS'
3837 AND pai_ben.action_context_type = 'AAP'
3838 HAVING COUNT(DISTINCT pai_ben.action_information10) > 1;
3839
3840 -- 4312909 Perf fix - Removed redundant Car and Vans related categories
3841 CURSOR csr_p11db_value
3842 IS
3843 SELECT pai_comp.action_information7 employers_name,
3844 SUM(DECODE(
3845 pai.action_information_category,
3846 'ASSETS TRANSFERRED', pai.action_information9,
3847 'LIVING ACCOMMODATION', pai.action_information10,
3848 'CAR AND CAR FUEL 2003_04',NVL(pai.action_information10, 0) + NVL(pai.action_information11, 0),
3849 'VANS 2002_03', NVL(pai.action_information15, 0),
3850 'INT FREE AND LOW INT LOANS', pai.action_information11,
3851 'PVT MED TREATMENT OR INSURANCE', pai.action_information7,
3852 'RELOCATION EXPENSES', pai.action_information5,
3853 'SERVICES SUPPLIED', pai.action_information7,
3854 'ASSETS AT EMP DISPOSAL', pai.action_information9,
3855 'OTHER ITEMS', pai.action_information9,
3856 -- 'EXPENSES PAYMENTS', pai.action_information8,
3857 '0') ) p11db_value
3858 FROM pay_action_information pai_comp,
3859 pay_action_information pai,
3860 pay_assignment_actions paa,
3861 pay_payroll_actions ppa
3862 WHERE ppa.payroll_action_id = pactid
3863 AND ppa.payroll_action_id = paa .payroll_action_id
3864 AND pai_comp.action_context_id = paa.assignment_action_id
3865 AND pai_comp.action_information_category = 'EMEA PAYROLL INFO'
3866 AND pai.action_context_id = paa.assignment_action_id
3867 GROUP BY pai_comp.action_information7;
3868
3869 PROCEDURE check_duplicate
3870 IS
3871 type person_details is record(
3872 full_name varchar2(255),
3873 employer_name varchar2(255),
3874 person_id number,
3875 employee_no varchar2(70),
3876 ni_number varchar2(12)
3877 );
3878
3879 type t_person_table is table of person_details index by binary_integer;
3880
3881 person_table t_person_table;
3882 l_count number;
3883 l_prev number;
3884 l_curr number;
3885
3886 cursor get_details is
3887 select ppa.action_information6,
3888 ppa.action_information7,
3889 ppa.action_information8,
3890 ppa.action_information9,
3891 ppa.action_information10,
3892 ppa.action_information11,
3893 ppa.action_information12
3894 from pay_assignment_actions paa,
3895 pay_action_information ppa
3896 where paa.payroll_action_id = pactid
3897 and paa.assignment_action_id = ppa.action_context_id
3898 and ppa.action_information_category = 'GB EMPLOYEE DETAILS'
3899 and ppa.action_context_type = 'AAP'
3900 order by ppa.action_information12;
3901
3902 BEGIN
3903 l_count := 0;
3904 for x in get_details loop
3905 l_count := l_count + 1;
3906 person_table(l_count).full_name := x.action_information8;
3907 person_table(l_count).employer_name := x.action_information9;
3908 person_table(l_count).person_id := x.action_information10;
3909 person_table(l_count).employee_no := x.action_information11;
3910 person_table(l_count).ni_number := x.action_information12;
3911 end loop;
3912 fnd_file.put_line(fnd_file.output,null);
3913 fnd_file.put_line(fnd_file.output,'Duplicate NI Number Report');
3914 fnd_file.put_line(fnd_file.output,rpad('Employer Name',20) ||
3915 rpad(' Employee Name',26) ||
3916 rpad(' Employee Number',16));
3917 fnd_file.put_line(fnd_file.output,rpad('-',20,'-') || ' ' ||
3918 rpad('-',25,'-') || ' ' ||
3919 rpad('-',16,'-') || ' ' ||
3920 rpad('-',55,'-'));
3921
3922 if l_count > 0 then
3923 l_prev := 1;
3924 l_curr := 1;
3925 loop
3926 if l_curr > 1 then
3927 if (person_table(l_curr).ni_number = person_table(l_prev).ni_number
3928 and
3929 person_table(l_curr).person_id <> person_table(l_prev).person_id) then
3930 fnd_file.put_line(fnd_file.output,
3931 rpad(person_table(l_prev).employer_name,21) ||
3932 rpad(person_table(l_prev).full_name,26) ||
3933 rpad(person_table(l_prev).employee_no,16) ||
3934 'This employee has a duplicate NI number ' || person_table(l_prev).ni_number);
3935 fnd_file.put_line(fnd_file.output,
3936 rpad(person_table(l_curr).employer_name,21) ||
3937 rpad(person_table(l_curr).full_name,26) ||
3938 rpad(person_table(l_curr).employee_no,16) ||
3939 'This employee has a duplicate NI number ' || person_table(l_curr).ni_number);
3940 end if;
3941 end if;
3942 l_prev := l_curr;
3943 l_curr := l_curr + 1;
3944 exit
3945 when(l_curr > l_count);
3946 end loop;
3947 end if;
3948 END;
3949
3950 FUNCTION check_classA(p_benefit_code varchar2,
3951 p_benefit_name varchar2) return boolean
3952 IS
3953 class_a constant varchar2(10) := 'ADFGHIJKLM';
3954 non_class_a constant varchar2(5) := 'BCEN';
3955 code varchar2(10);
3956 ret boolean;
3957 BEGIN
3958 code := translate(p_benefit_code,class_a || non_class_a, class_a);
3959 if code is not null then
3960 ret := true;
3961 if p_benefit_name = 'OTHER ITEMS NON 1A' then
3962 ret := false;
3963 end if;
3964 else
3965 ret := false;
3966 end if;
3967 return ret;
3968 END check_classA;
3969
3970 FUNCTION get_input_name(p_benefit_code varchar2) return varchar2
3971 IS
3972 l_ret varchar2(30);
3973 BEGIN
3974 if p_benefit_code = 'F' then
3975 l_ret := 'Cash Equivalent for Car';
3976 elsif p_benefit_code = 'G' then
3977 l_ret := 'Van Benefit Charge';
3978 elsif p_benefit_code = 'D' then
3979 l_ret := 'Cash Equiv + Add Charge';
3980 else
3981 l_ret := 'Cash Equivalent';
3982 end if;
3983 return l_ret;
3984 END;
3985
3986 PROCEDURE write_summary(p_pact_id number) IS
3987
3988 cursor get_employer(p_pact_id number) is
3989 select action_information7
3990 from pay_assignment_actions paa,
3991 pay_action_information pai
3992 where paa.payroll_action_id = p_pact_id
3993 and pai.action_context_id = paa.assignment_action_id
3994 and pai.action_information_category = 'EMEA PAYROLL INFO'
3995 and pai.action_context_type = 'AAP'
3996 group by action_information7;
3997
3998 cursor get_total(p_pact_id number, p_emp_name varchar2) is
3999 select * from (
4000 select /*+ ORDERED use_nl(paf,paa,pai,pai_a,pai_person)
4001 use_index(pai_person,pay_action_information_n2)
4002 use_index(pai,pay_action_information_n2)
4003 use_index(pai_a,pay_action_information_n2)*/
4004 pai.action_information_category name,
4005 sum(decode(pai.action_information_category,
4006 'ASSETS TRANSFERRED', pai.action_information9,
4007 'PAYMENTS MADE FOR EMP', pai.action_information7,
4008 'VOUCHERS OR CREDIT CARDS', pai.action_information11,
4009 'LIVING ACCOMMODATION', pai.action_information10, --Changed for bug 8204969
4010 'MILEAGE ALLOWANCE AND PPAYMENT', pai_a.action_information12,
4011 'CAR AND CAR FUEL 2003_04', pai.action_information10,
4012 'VANS 2002_03',pai.action_information15,
4013 'VANS 2005', pai.action_information15,
4014 'VANS 2007', pai.action_information14,
4015 'INT FREE AND LOW INT LOANS', pai.action_information11,
4016 'PVT MED TREATMENT OR INSURANCE', pai.action_information7,
4017 'RELOCATION EXPENSES', pai.action_information5,
4018 'SERVICES SUPPLIED', pai.action_information7,
4019 'ASSETS AT EMP DISPOSAL', pai.action_information9,
4020 'OTHER ITEMS', pai.action_information9,
4021 'OTHER ITEMS NON 1A', pai.action_information9,
4022 'EXPENSES PAYMENTS', pai.action_information8)) total,
4023 decode(pai.action_information_category,
4024 'ASSETS TRANSFERRED', 'A',
4025 'PAYMENTS MADE FOR EMP', 'B',
4026 'VOUCHERS OR CREDIT CARDS', 'C',
4027 'LIVING ACCOMMODATION', 'D',
4028 'MILEAGE ALLOWANCE AND PPAYMENT', 'E',
4029 'CAR AND CAR FUEL 2003_04', 'F',
4030 'VANS 2005', 'G',
4031 'VANS 2007', 'O',
4032 'VANS 2002_03', 'G',
4033 'INT FREE AND LOW INT LOANS', 'H',
4034 'PVT MED TREATMENT OR INSURANCE', 'I',
4035 'RELOCATION EXPENSES', 'J',
4036 'SERVICES SUPPLIED', 'K',
4037 'ASSETS AT EMP DISPOSAL', 'L',
4038 'OTHER ITEMS', 'M',
4039 'OTHER ITEMS NON 1A', 'M',
4040 'EXPENSES PAYMENTS', 'N') cat,
4041 count(*) no_of_entries
4042 from pay_assignment_actions paa,
4043 pay_action_information pai,
4044 pay_action_information pai_a,
4045 pay_action_information pai_person
4046 where paa.payroll_action_id = p_pact_id
4047 and pai.action_context_id = paa.assignment_action_id
4048 and pai.action_context_type = 'AAP'
4049 and pai.action_information_category = pai.action_information_category
4050 and pai_person.action_context_id = paa.assignment_action_id
4051 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
4052 and pai_person.action_information9 = p_emp_name --p_employer_name
4053 and pai_person.action_context_type = 'AAP'
4054 and pai_a.action_context_id = paa.assignment_action_id
4055 and pai_a.action_context_type = 'AAP'
4056 and pai_a.action_information_category = 'GB P11D ASSIGNMENT RESULTA'
4057 group by pai.action_information_category)
4058 where cat in ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O')
4059 order by cat;
4060
4061 /* Added for the bug 8513401*/
4062 cursor get_int_free_total(p_pact_id number, p_emp_name varchar2) is
4063 select sum(decode(pai.action_information_category,
4064 'INT FREE AND LOW INT LOANS', pai.action_information11)) total
4065 from pay_assignment_actions paa,
4066 pay_action_information pai,
4067 pay_action_information pai_a,
4068 pay_action_information pai_person
4069 where paa.payroll_action_id = p_pact_id
4070 and pai.action_context_id = paa.assignment_action_id
4071 and pai.action_context_type = 'AAP'
4072 and pai_person.action_context_id = paa.assignment_action_id
4073 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
4074 and pai_person.action_information9 = p_emp_name --p_employer_name
4075 and pai_person.action_context_type = 'AAP'
4076 and pai_a.action_context_id = paa.assignment_action_id
4077 and pai_a.action_context_type = 'AAP'
4078 and pai_a.action_information_category = 'GB P11D ASSIGNMENT RESULTA'
4079 and not exists (select 1
4080 from pay_action_information pai_max
4081 where pai_max.action_context_id = paa.assignment_action_id
4082 and nvl(pai_max.ACTION_INFORMATION23,0) < 5000
4083 and pai_max.action_context_type = 'AAP'
4084 and pai_max.action_information_category = 'GB P11D ASSIGNMENT RESULTC');
4085
4086 /* Added for the bug 8513401*/
4087
4088 cursor get_mileage(p_pact_id number, p_emp_name varchar2) is
4089 /* Modified query for Bug:12314320 joined with pay_action_information to get the person id which is
4090 stored in action_information1 and group by person id so that it works for person with
4091 mulitple assignments and also for assignments for muliptle persons*/
4092
4093 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
4094 use_index(pai_person,pay_action_information_n2)
4095 use_index(pai,pay_action_information_n2) */
4096 -- modified for Bug 12314320
4097 sum(nonclassA) from
4098 (select
4099 max(pai.action_information12) as nonclassA --Modified for the bug 11727875
4100 from pay_assignment_actions paa,
4101 pay_action_information pai,
4102 pay_action_information pai_person,
4103 pay_action_information pai_add
4104 where paa.payroll_action_id = p_pact_id
4105 and pai.action_context_id = paa.assignment_action_id
4106 and pai.action_context_type = 'AAP'
4107 and pai.action_information_category = 'GB P11D ASSIGNMENT RESULTA'
4108 and pai_person.action_context_id = paa.assignment_action_id
4109 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
4110 and pai_person.action_information9 = p_emp_name --p_employer_name
4111 and pai_add.action_context_id = paa.assignment_action_id
4112 and pai_add.action_information_category = 'ADDRESS DETAILS'
4113 and pai_person.action_context_type = 'AAP' group by pai_add.action_information1);
4114
4115 cursor get_car_fuel(p_pact_id number, p_emp_name varchar2) is
4116 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
4117 use_index(pai_person,pay_action_information_n2)
4118 use_index(pai,pay_action_information_n2) */
4119 sum(pai.action_information11) -- Cash Equivalent For Fuel
4120 from pay_assignment_actions paa,
4121 pay_action_information pai,
4122 pay_action_information pai_person
4123 where paa.payroll_action_id = p_pact_id
4124 and pai.action_context_id = paa.assignment_action_id
4125 and pai.action_context_type = 'AAP'
4126 and pai.action_information_category = 'CAR AND CAR FUEL 2003_04'
4127 and pai_person.action_context_id = paa.assignment_action_id
4128 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
4129 and pai_person.action_information9 = p_emp_name --p_employer_name
4130 and pai_person.action_context_type = 'AAP';
4131
4132 cursor get_van_fuel(p_pact_id number, p_emp_name varchar2) is
4133 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
4134 use_index(pai_person,pay_action_information_n2)
4135 use_index(pai,pay_action_information_n2) */
4136 sum(pai.action_information30) -- Cash Equivalent For Fuel
4137 from pay_assignment_actions paa,
4138 pay_action_information pai,
4139 pay_action_information pai_person
4140 where paa.payroll_action_id = p_pact_id
4141 and pai.action_context_id = paa.assignment_action_id
4142 and pai.action_context_type = 'AAP'
4143 and pai.action_information_category = 'VANS 2007'
4144 and pai_person.action_context_id = paa.assignment_action_id
4145 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
4146 and pai_person.action_information9 = p_emp_name --p_employer_name
4147 and pai_person.action_context_type = 'AAP';
4148
4149 cursor get_person_count(p_pact_id number,
4150 p_emp_name varchar2,
4151 p_category varchar2) is
4152 select count(*)
4153 from (select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
4154 use_index(pai_person,pay_action_information_n2)
4155 use_index(pai,pay_action_information_n2) */
4156 pai_person.action_information10 -- Person id
4157 from pay_assignment_actions paa,
4158 pay_action_information pai,
4159 pay_action_information pai_person
4160 where paa.payroll_action_id = p_pact_id
4161 and pai.action_context_id = paa.assignment_action_id
4162 and pai.action_context_type = 'AAP'
4163 and pai.action_information_category = p_category
4164 and pai_person.action_context_id = paa.assignment_action_id
4165 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
4166 and pai_person.action_information9 = p_emp_name --p_employer_name
4167 and pai_person.action_context_type = 'AAP'
4168 group by pai_person.action_information10);
4169
4170 l_1a_total number;
4171 l_n1a_total number;
4172 l_car_fuel number;
4173 l_van_fuel number;
4174 l_mileage number;
4175 l_person_count number;
4176 l_int_free_total number;
4177
4178 PROCEDURE write_header(p_employer_name varchar2) IS
4179 BEGIN
4180 fnd_file.put_line(fnd_file.output,'Employer Name : ' || p_employer_name);
4181 fnd_file.put_line(fnd_file.output,
4182 rpad('Benefit Type Element', 31) || rpad('Input Value', 29) ||
4183 rpad('# People', 10) || rpad('# Entries', 10) ||
4184 rpad('Class 1A',16) || rpad('Non Class 1A',15));
4185 fnd_file.put_line(fnd_file.output,
4186 rpad('-',30,'-') || ' ' ||
4187 rpad('-',28,'-') || ' ' ||
4188 rpad('-',9,'-') || ' ' ||
4189 rpad('-',9,'-') || ' ' ||
4190 rpad('-',15,'-') || ' ' ||
4191 rpad('-',15,'-'));
4192 END write_header;
4193
4194 PROCEDURE write_body(p_ben_name varchar2,
4195 p_inp_name varchar2,
4196 p_nos_entries varchar2,
4197 p_nos_person varchar2,
4198 p_value varchar2,
4199 p_class_A boolean) IS
4200 BEGIN
4201 if p_class_A then
4202 fnd_file.put_line(fnd_file.output,
4203 rpad(p_ben_name, 31) || rpad(p_inp_name, 29) ||
4204 lpad(p_nos_person,9) || ' ' || lpad(p_nos_entries,9) || ' ' ||
4205 rpad(p_value,15) || ' ' || rpad(' ',15));
4206 else
4207 fnd_file.put_line(fnd_file.output,
4208 rpad(p_ben_name, 31) || rpad(p_inp_name, 29) ||
4209 lpad(p_nos_person,9) || ' ' || lpad(p_nos_entries,9) || ' ' ||
4210 rpad(' ',15) || ' ' || rpad(p_value,15));
4211 end if;
4212 END write_body;
4213
4214 PROCEDURE write_footer(p_class_a_value varchar2,
4215 p_nclass_value varchar2,
4216 p_total varchar2) IS
4217 BEGIN
4218 fnd_file.put_line(fnd_file.output,null);
4219 fnd_file.put_line(fnd_file.output,rpad('Total',80) || rpad(p_class_a_value,15) || ' ' ||
4220 rpad(p_nclass_value,15) || rpad(p_total,16));
4221 fnd_file.put_line(fnd_file.output,null);
4222 END write_footer;
4223
4224 BEGIN
4225 fnd_file.put_line(fnd_file.output,null);
4226 fnd_file.put_line(fnd_file.output,'P11D Summary Report');
4227 FOR employer IN get_employer(p_pact_id)
4228 LOOP
4229 l_1a_total := 0;
4230 l_n1a_total := 0;
4231 write_header(employer.action_information7);
4232 FOR benefit IN get_total(p_pact_id,employer.action_information7)
4233 LOOP
4234 l_person_count := 0;
4235 open get_person_count(p_pact_id, employer.action_information7, benefit.name);
4236 fetch get_person_count into l_person_count;
4237 close get_person_count;
4238
4239 if benefit.cat = 'E' then -- Mileage Allowance
4240 open get_mileage(p_pact_id, employer.action_information7);
4241 fetch get_mileage into l_mileage;
4242 close get_mileage;
4243 write_body(p_ben_name => benefit.name,
4244 p_inp_name => get_input_name(benefit.cat),
4245 p_nos_entries => benefit.no_of_entries,
4246 p_nos_person => l_person_count,
4247 p_value => to_char(l_mileage,'999,999,990.99'),
4248 p_class_A => check_classA(benefit.cat, benefit.name));
4249 if check_classA(benefit.cat, benefit.name) then
4250 l_1a_total := l_1a_total + l_mileage;
4251 else
4252 l_n1a_total := l_n1a_total + l_mileage;
4253 end if;
4254 /* Added for the bug 8513401*/
4255 elsif benefit.cat = 'H' then
4256 open get_int_free_total(p_pact_id,employer.action_information7);
4257 fetch get_int_free_total into l_int_free_total;
4258 close get_int_free_total;
4259 write_body(p_ben_name => benefit.name,
4260 p_inp_name => get_input_name(benefit.cat),
4261 p_nos_entries => benefit.no_of_entries,
4262 p_nos_person => l_person_count,
4263 p_value => to_char(nvl(l_int_free_total,0),'999,999,990.99'),
4264 p_class_A => check_classA(benefit.cat, benefit.name));
4265 if check_classA(benefit.cat, benefit.name) then
4266 l_1a_total := l_1a_total + nvl(l_int_free_total,0);
4267 else
4268 l_n1a_total := l_n1a_total + nvl(l_int_free_total,0);
4269 end if;
4270 /* Added for the bug 8513401*/
4271 else
4272 write_body(p_ben_name => benefit.name,
4273 p_inp_name => get_input_name(benefit.cat),
4274 p_nos_entries => benefit.no_of_entries,
4275 p_nos_person => l_person_count,
4276 p_value => to_char(benefit.total,'999,999,990.99'),
4277 p_class_A => check_classA(benefit.cat, benefit.name));
4278 if check_classA(benefit.cat, benefit.name) then
4279 l_1a_total := l_1a_total + benefit.total;
4280 else
4281 l_n1a_total := l_n1a_total + benefit.total;
4282 end if;
4283 end if;
4284 if benefit.cat = 'F' then -- car
4285 open get_car_fuel(p_pact_id, employer.action_information7);
4286 fetch get_car_fuel into l_car_fuel;
4287 close get_car_fuel;
4288 write_body(p_ben_name => ' ',
4289 p_inp_name => 'Cash Equivalent for Fuel',
4290 p_nos_entries => benefit.no_of_entries,
4291 p_nos_person => ' ',
4292 p_value => to_char(l_car_fuel,'999,999,990.99'),
4293 p_class_A => true);
4294 l_1a_total := l_1a_total + l_car_fuel;
4295 end if;
4296 if benefit.cat = 'O' then -- van
4297 open get_van_fuel(p_pact_id, employer.action_information7);
4298 fetch get_van_fuel into l_van_fuel;
4299 close get_van_fuel;
4300 write_body(p_ben_name => ' ',
4301 p_inp_name => 'Cash Equivalent for Fuel',
4302 p_nos_entries => benefit.no_of_entries,
4303 p_nos_person => ' ',
4304 p_value => to_char(l_van_fuel,'999,999,990.99'),
4305 p_class_A => true);
4306 l_1a_total := l_1a_total + l_van_fuel;
4307 end if;
4308 END LOOP;
4309 write_footer(to_char(l_1a_total,'999,999,990.99'),to_char(l_n1a_total,'999,999,990.99'),
4310 to_char(l_1a_total + l_n1a_total,'999,999,990.99'));
4311 END LOOP;
4312 END write_summary;
4313
4314 PROCEDURE write_error_log(p_pact_id number)
4315 IS
4316 l_error_count number;
4317 l_warn_count number;
4318 l_full_name varchar2(255);
4319 l_element varchar2(255);
4320 cursor get_message(p_pact_id varchar2,
4321 p_msg_typ varchar2)
4322 is
4323 select distinct
4324 pap.full_name,
4325 paf.assignment_number,
4326 pml.message_level,
4327 substr(pml.line_text,instr(pml.line_text,':') + 2) line_text,
4328 substr(pml.line_text,6,instr(pml.line_text,':') - 6) element_name,
4329 pml.line_sequence
4330 from pay_payroll_actions pay,
4331 pay_assignment_actions paa,
4332 per_all_assignments_f paf,
4333 per_all_people_f pap,
4334 per_periods_of_service pos,
4335 pay_message_lines pml
4336 where pay.payroll_action_id = p_pact_id
4337 and paa.payroll_action_id = pay.payroll_action_id
4338 and pml.source_id = paa.assignment_action_id
4339 and pml.message_level = p_msg_typ
4340 and pml.source_type = 'A'
4341 and substr(line_text,1,5) = 'P11D '
4342 and substr(line_text,6,5) <> 'Error'
4343 and paf.assignment_id = paa.assignment_id
4344 and pap.person_id = paf.person_id
4345 and pos.period_of_service_id(+) = paf.period_of_service_id
4346 and nvl(pos.actual_termination_date, pay.effective_date) between
4347 pap.effective_start_date and pap.effective_end_date
4348 order by paf.assignment_number, element_name, pml.line_sequence;
4349
4350 PROCEDURE write_header(p_type varchar2) IS
4351 BEGIN
4352 fnd_file.put_line(fnd_file.output,null);
4353 if p_type = 'F' then
4354 fnd_file.put_line(fnd_file.output,'The following assignments have completed with error');
4355 fnd_file.put_line(fnd_file.output,rpad('Employee Name', 26) ||
4356 rpad('Assignment Number', 18) ||
4357 rpad('Error Message',87));
4358 else
4359 fnd_file.put_line(fnd_file.output,'The following assignments have completed with warning');
4360 fnd_file.put_line(fnd_file.output,rpad('Employee Name', 26) ||
4361 rpad('Assignment Number', 18) ||
4362 rpad('Warning Message',87));
4363 end if;
4364 fnd_file.put_line(fnd_file.output,rpad('-',25,'-') || ' ' ||
4365 rpad('-',17,'-') || ' ' ||
4366 rpad('-',87,'-'));
4367 END write_header;
4368
4369 PROCEDURE write_body(p_emp_name varchar2,
4370 p_emp_no varchar2,
4371 p_element varchar2,
4372 p_message varchar2) IS
4373 l_msg varchar2(255) := p_message;
4374 l_out varchar2(60);
4375 l_first boolean;
4376 l_msg_length number;
4377 l_count number;
4378 l_temp number;
4379 l_pos number;
4380 BEGIN
4381 select length(l_msg) into l_msg_length from dual;
4382 l_first := true;
4383 while l_msg_length > 57 loop
4384 l_count := 1;
4385 l_pos := 0;
4386 l_temp := 1;
4387 while l_temp > 0 and l_temp < 56 loop
4388 l_pos := l_temp;
4389 select instr(l_msg,' ',1,l_count) into l_temp from dual;
4390 l_count := l_count + 1;
4391 end loop;
4392 select substr(l_msg,1,l_pos), substr(l_msg,l_pos + 1) into l_out, l_msg from dual;
4393 select length(l_msg) into l_msg_length from dual;
4394 if l_first then
4395 l_first := false;
4396 fnd_file.put_line(fnd_file.output,rpad(p_emp_name, 25) || ' ' ||
4397 rpad(p_emp_no, 17) || ' ' ||
4398 rpad(nvl(p_element,' '),30) || '-' ||
4399 rpad(l_out,56));
4400 else
4401 fnd_file.put_line(fnd_file.output,rpad(' ', 25) || ' ' ||
4402 rpad(' ', 17) || ' ' ||
4403 rpad(' ', 30) || ' ' ||
4404 rpad(l_out,56));
4405 end if;
4406 end loop;
4407 if l_first then
4408 l_first := false;
4409 fnd_file.put_line(fnd_file.output,rpad(p_emp_name, 25) || ' ' ||
4410 rpad(p_emp_no, 17) || ' ' ||
4411 rpad(nvl(p_element,' '),30) || '-' ||
4412 rpad(l_msg,56));
4413 else
4414 fnd_file.put_line(fnd_file.output,rpad(' ', 25) || ' ' ||
4415 rpad(' ', 17) || ' ' ||
4416 rpad(' ', 30) || ' ' ||
4417 rpad(l_msg,56));
4418 end if;
4419 END write_body;
4420
4421 PROCEDURE write_footer(p_type varchar2,
4422 p_total varchar2) IS
4423 BEGIN
4424 fnd_file.put_line(fnd_file.output,null);
4425 if p_type = 'F' then
4426 fnd_file.put_line(fnd_file.output,rpad('Total Number of assignments with error :',45) || rpad(p_total,15));
4427 else
4428 fnd_file.put_line(fnd_file.output,rpad('Total Number of assignments with warning :',45) || rpad(p_total,15));
4429 end if;
4430 END write_footer;
4431
4432 BEGIN
4433 l_error_count := 0;
4434 l_warn_count := 0;
4435 l_full_name := ' ';
4436 l_element := ' ';
4437 write_header('F');
4438 FOR error_messages in get_message(p_pact_id, 'F')
4439 LOOP
4440 if l_full_name <> error_messages.full_name then
4441 write_body(error_messages.full_name,
4442 error_messages.assignment_number,
4443 error_messages.element_name,
4444 error_messages.line_text);
4445 l_full_name := error_messages.full_name;
4446 l_error_count := l_error_count + 1;
4447 l_element := error_messages.element_name;
4448 else
4449 if l_element <> error_messages.element_name then
4450 write_body(' ', ' ',error_messages.element_name, error_messages.line_text);
4451 l_element := error_messages.element_name;
4452 else
4453 write_body(' ', ' ',' ', error_messages.line_text);
4454 end if;
4455 end if;
4456 END LOOP;
4457 write_footer('F',l_error_count);
4458
4459 write_header('W');
4460 l_full_name := ' ';
4461 l_element := ' ';
4462 FOR warn_messages in get_message(p_pact_id, 'W')
4463 LOOP
4464 if l_full_name <> warn_messages.full_name then
4465 write_body(warn_messages.full_name,
4466 warn_messages.assignment_number,
4467 warn_messages.element_name,
4468 warn_messages.line_text);
4469 l_full_name := warn_messages.full_name;
4470 l_warn_count := l_warn_count + 1;
4471 l_element := warn_messages.element_name;
4472 else
4473 if l_element <> warn_messages.element_name then
4474 write_body(' ', ' ',warn_messages.element_name, warn_messages.line_text);
4475 l_element := warn_messages.element_name;
4476 else
4477 write_body(' ',' ',' ', warn_messages.line_text);
4478 end if;
4479 end if;
4480 END LOOP;
4481 write_footer('W',l_warn_count);
4482 END write_error_log;
4483
4484 BEGIN
4485 hr_utility.set_location('Entering '|| l_proc, 10);
4486 hr_utility.trace('Checking incorrect NI ');
4487 FOR incorrect_ni_num IN csr_incorrect_ni_num
4488 LOOP
4489 FOR total_num IN csr_total_num(incorrect_ni_num.action_information7)
4490 LOOP
4491 write_log(
4492 incorrect_ni_num.action_information7,
4493 NULL,
4494 NULL,
4495 ROUND(incorrect_ni_num.temp_num/total_num.tot_num * 100, 2)
4496 || '% of employees have temporary NI numbers or no NI numbers. ');
4497 END LOOP;
4498 END LOOP;
4499 -- FOR dup_ni_num IN csr_dup_ni_num
4500 -- LOOP
4501 -- write_log(
4502 -- dup_ni_num.employers_name,
4503 -- dup_ni_num.person_name,
4504 -- dup_ni_num.employee_num,
4505 -- 'This employee has a duplicate NI number ' || dup_ni_num.ni_num);
4506 -- END LOOP;
4507 hr_utility.trace('Checking duplicate NI ');
4508 check_duplicate;
4509
4510 write_summary(pactid);
4511
4512 hr_utility.trace('Summing expenses payments ');
4513 FOR expenses_payment_chk IN csr_expenses_payment_chk
4514 LOOP
4515 fnd_file.put_line(fnd_file.output,
4516 'For Expenses Payments benefit, the Trading Orgainization Indicator has different values for different employees. ');
4517 fnd_file.put_line(fnd_file.output,
4518 'All employees in your Business Group must have the same Trading Orgainization Indicator. ');
4519 END LOOP;
4520
4521 write_error_log(pactid);
4522 --hr_utility.trace('Summing P11D value ');
4523 --FOR p11db_value IN csr_p11db_value
4524 --LOOP
4525 -- write_log(p11db_value.employers_name,
4526 -- NULL, NULL,
4527 -- 'The figure for you to include in P11D(b) report is ' || ROUND(p11db_value.p11db_value, 2) );
4528 --END LOOP;
4529
4530 hr_utility.set_location('Leaving '|| l_proc, 10);
4531 END;
4532
4533 Function is_p11d_benefit_allowed
4534 (p_effective_date date,
4535 p_person_id Number
4536 )
4537 return number
4538 is
4539 l_ret Number;
4540 begin
4541 if
4542 hr_person_type_usage_info.is_person_of_type(p_effective_date,p_person_id,'EMP')
4543 or
4544 hr_person_type_usage_info.is_person_of_type(p_effective_date,p_person_id,'EMP_APL')
4545 or
4546 hr_person_type_usage_info.is_person_of_type(p_effective_date,p_person_id,'EX_EMP')
4547 or
4548 hr_person_type_usage_info.is_person_of_type(p_effective_date,p_person_id,'EX_EMP_APL')
4549 then
4550 l_ret := 1;
4551 else
4552 l_ret := 0;
4553 end if;
4554 return l_ret;
4555 end;
4556
4557 END; -- Package Body PAY_GB_P11D_ARCHIVE_SS