[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_TP_TYPE2_FUNCTIONS
Source
1 Package Body pqp_gb_tp_type2_functions as
2 -- /* $Header: pqpgbtp2.pkb 120.4 2011/06/09 06:08:26 nchinnam ship $ */
3 --
4 -- Local Variables
5
6 g_inclusion_flag varchar2(1) := 'N';
7 g_error_text varchar2(200) := null;
8 g_error_number number := null;
9 -- g_ele_exists varchar2(1) := null;
10
11 -- ----------------------------------------------------------------------------
12 -- |--------------------------------< debug >---------------------------------|
13 -- ----------------------------------------------------------------------------
14
15 Procedure debug
16 (p_trace_message in varchar2
17 ,p_trace_location in number default null
18 ) is
19 --
20 l_padding varchar2(12);
21 l_max_message_length number:= 2000;
22 --
23 Begin
24
25 --
26 --
27 --
28 If p_trace_location is not null Then
29
30 l_padding := substr
31 (rpad(' ',least(g_nested_level,5)*2,' ')
32 ,1,l_max_message_length
33 - least(length(p_trace_message)
34 ,l_max_message_length)
35 );
36
37 hr_utility.set_location (l_padding || substr(p_trace_message
38 ,greatest(-length(p_trace_message),-l_max_message_length))
39 ,p_trace_location);
40
41 Else
42
43 hr_utility.trace(substr(p_trace_message,1,250));
44
45 End If;
46 --
47
48 End debug;
49
50 --
51 -- ----------------------------------------------------------------------------
52 -- |--------------------------------< debug >---------------------------------|
53 -- ----------------------------------------------------------------------------
54
55 Procedure debug
56 (p_trace_number in number ) is
57 --
58 Begin
59
60 --
61 debug(fnd_number.number_to_canonical(p_trace_number));
62 --
63
64 End debug;
65 --
66 -- ----------------------------------------------------------------------------
67 -- |--------------------------------< debug >---------------------------------|
68 -- ----------------------------------------------------------------------------
69
70 Procedure debug
71 (p_trace_date in date ) is
72 --
73 Begin
74
75 --
76 debug(fnd_date.date_to_canonical(p_trace_date));
77 --
78
79 End debug;
80 --
81 -- ----------------------------------------------------------------------------
82 -- |----------------------------< debug_enter >-------------------------------|
83 -- ----------------------------------------------------------------------------
84
85 /*Procedure debug_enter
86 (p_proc_name in varchar2 default null
87 ,p_trace_on in varchar2 default null
88 ) is
89 --
90 l_trace_options varchar2(200);
91 --
92 Begin
93
94 --
95 -- --Uncomment this code to run the extract with a debug trace
96 --
97 -- If g_nested_level = 0 -- swtich tracing on/off at the top level only
98 -- And nvl(p_trace_on,'N') = 'Y'
99 -- Then
100 --
101 -- hr_utility.trace_on(null,'REQID'); -- Pipe name REQIDnnnnnn
102
103 -- End If; -- if nested level = 0
104 --
105 -- --Uncomment this code to run the extract with a debug trace
106
107 g_nested_level := g_nested_level + 1;
108 debug('Entered: '||nvl(p_proc_name,g_proc_name),g_nested_level*100);
109 --
110
111 End debug_enter;*/
112
113 --
114 -- debug_enter
115 -- swtich tracing on/off at the top level only
116 --
117
118 PROCEDURE debug_enter
119 (p_proc_name IN VARCHAR2 default null
120 ,p_trace_on IN VARCHAR2 default null
121 )
122 IS
123
124 l_extract_attributes pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes%ROWTYPE;
125 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
126
127 BEGIN
128
129 debug(':g_nested_level:'||g_nested_level,000);
130
131 IF g_nested_level = 0 THEN -- swtich tracing on/off at the top level only
132
133 -- Set the trace flag, but only the first time around
134 debug(':g_trace:'||g_trace,000);
135
136 IF g_trace IS NULL THEN
137
138 OPEN pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes;
139 FETCH pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes INTO l_extract_attributes;
140 CLOSE pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes;
141
142 l_business_group_id := fnd_global.per_business_group_id;
143
144 BEGIN
145 g_trace := hruserdt.get_table_value
146 (p_bus_group_id => l_business_group_id
147 ,p_table_name => l_extract_attributes.user_table_name
148 ,p_col_name => 'Attribute Location Qualifier 1'
149 ,p_row_value => 'Debug'
150 ,p_effective_date => NULL -- don't hv the date
151 );
152 debug(':g_trace:'||g_trace,000);
153
154 EXCEPTION
155 WHEN OTHERS THEN
156 g_trace := 'N';
157 END;
158
159 g_trace := nvl(g_trace,'N');
160
161 debug('UDT Trace Flag : '||g_trace);
162
163 END IF; -- g_trace IS NULL THEN
164
165 debug(':g_trace:'||g_trace,000);
166
167 IF NVL(p_trace_on,'N') = 'Y'
168 OR
169 g_trace = 'Y' THEN
170
171 hr_utility.trace_on(NULL,'REQID'); -- Pipe name REQIDnnnnnn
172 debug(':Switching on the Trace: ',000);
173
174 END IF; -- NVL(p_trace_on,'N') = 'Y'
175 --
176 END IF; -- if nested level = 0
177
178 g_nested_level := g_nested_level + 1;
179 debug('Entered: '||NVL(p_proc_name,g_proc_name),g_nested_level*100);
180
181 END debug_enter;
182
183 --
184 -- debug_exit
185 -- The exception handler of top level functions must call debug_ext
186 -- with p_trace_off = 'Y'
187
188 PROCEDURE debug_exit
189 (p_proc_name IN VARCHAR2 default null
190 ,p_trace_off IN VARCHAR2 default null
191 )
192 IS
193 BEGIN
194
195 debug('Leaving: '||NVL(p_proc_name,g_proc_name),-g_nested_level*100);
196 g_nested_level := g_nested_level - 1;
197 debug('g_nested level is '||g_nested_level,000);
198
199 -- debug enter sets trace ON when g_trace = 'Y' and nested level = 0
200 -- so we must turn it off for the same condition
201 -- Also turn off tracing when the override flag of p_trace_off has been passed as Y
202 IF (g_nested_level = 0
203 AND
204 g_trace = 'Y'
205 )
206 OR
207 NVL(p_trace_off,'N') = 'Y' THEN
208 debug(':Switching off the Trace: ',000);
209 hr_utility.trace_off;
210
211 END IF; -- (g_nested_level = 0
212
213 END debug_exit;
214
215
216 --
217 -- ----------------------------------------------------------------------------
218 -- |------------------------< set_effective_dates >---------------------------|
219 -- ----------------------------------------------------------------------------
220
221 Procedure set_effective_dates
222 is
223 --
224 l_year number;
225 l_proc_name varchar2(60) := g_proc_name || 'set_effective_dates';
226 --
227 Begin
228
229 --
230 hr_utility.set_location('Entering: '||l_proc_name, 5);
231 --
232 debug_enter(l_proc_name);
233
234 If to_number(to_char(g_effective_date, 'MM'))
235 between 1 and 3 Then
236
237 -- Pension year should start YY - 2
238 l_year := to_number(to_char(g_effective_date, 'YYYY')) - 2;
239
240 Else
241
242 -- Pension year should start YY - 1
243 l_year := to_number(to_char(g_effective_date, 'YYYY')) - 1;
244
245 End If; -- End if of month check...
246
247 debug(':l_year:'||l_year,500);
248
249 g_effective_start_date := to_date('01/04/'||to_char(l_year), 'DD/MM/YYYY');
250 g_effective_end_date := to_date('31/03/'||to_char(l_year+1)||
251 '23:59:59', 'DD/MM/YYYY HH24:MI:SS');
252
253 debug(':g_effective_start_date:'||g_effective_start_date,510);
254 debug(':g_effective_end_date:'||g_effective_end_date,520);
255 debug(':g_header_system_element:'||g_header_system_element,525);
256
257 g_header_system_element:=
258 g_header_system_element||
259 fnd_date.date_to_canonical(g_effective_start_date)||':'||
260 fnd_date.date_to_canonical(g_effective_end_date)||':'||
261 fnd_date.date_to_canonical(g_effective_date)||':' ;
262
263
264 pqp_gb_tp_pension_extracts.g_header_system_element := g_header_system_element;
265
266
267
268 --debug(':g_header_system_element:'||g_header_system_element,530);
269 --
270 hr_utility.set_location('Leaving: '||l_proc_name, 15);
271 --
272 debug_exit(l_proc_name);
273
274
275 End set_effective_dates;
276 --
277 -- ----------------------------------------------------------------------------
278 -- |--------------------------< get_pay_bal_id >------------------------------|
279 -- ----------------------------------------------------------------------------
280
281 Function get_pay_bal_id
282 (p_balance_name in varchar2)
283 Return number is
284 --
285 l_proc_name varchar2(60) := g_proc_name || 'get_pay_bal_id';
286 l_bal_type_id csr_get_pay_bal_id%rowtype;
287 --
288 Begin
289
290
291 debug_enter(l_proc_name);
292
293 debug(':p_balance_name:'||p_balance_name,1000);
294
295 Open csr_get_pay_bal_id
296 (c_balance_name => p_balance_name);
297 Fetch csr_get_pay_bal_id into l_bal_type_id;
298 Close csr_get_pay_bal_id;
299
300 debug(':l_bal_type_id.balance_type_id:'||l_bal_type_id.balance_type_id,1100);
301
302 debug_exit(l_proc_name);
303 --
304
305 Return l_bal_type_id.balance_type_id;
306
307 End get_pay_bal_id;
308 --
309 -- ----------------------------------------------------------------------------
310 -- |-------------------------< get_pay_ele_ids_from_bal >---------------------|
311 -- ----------------------------------------------------------------------------
312
313 Procedure get_pay_ele_ids_from_bal
314 (p_balance_type_id in number
315 ,p_effective_start_date in date
316 ,p_effective_end_date in date
317 ,p_tab_ele_ids out nocopy t_ele_ids_from_bal
318 ) is
319 --
320 l_proc_name varchar2(60) := g_proc_name || 'get_pay_ele_ids_from_bal';
321 l_ele_ids csr_get_pay_ele_ids_from_bal%rowtype;
322 idx number := 1;
323 i number := 1;
324 --
325 Begin
326
327
328 debug_enter(l_proc_name);
329 debug(l_proc_name, 900);
330 debug(':p_balance_type_id:'||p_balance_type_id);
331 debug(':p_effective_start_date:'||p_effective_start_date);
332 debug(':p_effective_end_date:'||p_effective_end_date);
333
334 Open csr_get_pay_ele_ids_from_bal
335 (c_balance_type_id => p_balance_type_id
336 ,c_effective_start_date => p_effective_start_date
337 ,c_effective_end_date => p_effective_end_date);
338 Loop
339
340 Fetch csr_get_pay_ele_ids_from_bal into l_ele_ids;
341 Exit when csr_get_pay_ele_ids_from_bal%notfound;
342
343 --get the valid element type ids for this BG.
344 IF (
345 pqp_gb_t1_pension_extracts.g_lea_business_groups.exists(l_ele_ids.business_group_id)
346 OR
347 g_business_group_id = l_ele_ids.business_group_id
348 )
349 THEN
350 p_tab_ele_ids(i) := l_ele_ids;
351 debug(':i:'||i, 910 + idx/1000000);
352 debug(':l_ele_ids eleement type id :'||l_ele_ids.element_type_id, 920 + idx/1000000);
353 debug(':l_ele_ids business group id :'||l_ele_ids.business_group_id, 930 + idx/1000000);
354 i := i + 1;
355 END IF;
356 --
357 idx := idx + 1 ;
358 End Loop;
359
360 If csr_get_pay_ele_ids_from_bal%ROWCOUNT = 0 Then
361
362 g_error_number := 93000;
363 g_error_text := 'BEN_93000_EXT_TP2_BAL_NOFEEDS';
364 debug(':csr_get_pay_ele_ids_from_bal%rowcount = 0', 940 );
365
366 End If;
367 Close csr_get_pay_ele_ids_from_bal;
368
369 debug_exit(l_proc_name);
370
371 -- Added by tmehra for nocopy changes Feb'03
372
373 EXCEPTION
374 WHEN OTHERS THEN
375 hr_utility.set_location('Entering excep:'||l_proc_name, 25);
376
377 p_tab_ele_ids.delete;
378
379 RAISE;
380
381 End get_pay_ele_ids_from_bal;
382 --
383 -- ----------------------------------------------------------------------------
384 -- |---------------------------< set_type2_globals >--------------------------|
385 -- ----------------------------------------------------------------------------
386
387 Procedure set_type2_globals
388 is
389 --
390 l_proc_name varchar2(60) := g_proc_name || 'set_type2_globals';
391
392 --
393 Begin
394
395 --
396 debug_enter(l_proc_name);
397 --ENH3 And ENH4.
398 -- Set the globals in this package from type 4
399
400 -- ********* Variables ***************
401
402 g_lea_number := pqp_gb_tp_pension_extracts.g_lea_number;
403 g_crossbg_enabled := pqp_gb_tp_pension_extracts.g_crossbg_enabled;
404 g_estb_number := pqp_gb_tp_pension_extracts.g_estb_number;
405 -- "end of day" of a day before effective date
406 g_effective_run_date :=
407 fnd_date.canonical_to_date(TO_CHAR(g_effective_date - 1,'YYYY/MM/DD')||'23:59:59');
408 -- ********* Ennd of Variables ***************
409
410
411 debug(':g_estb_number:'||g_estb_number,210 );
412 debug(':g_crossbg_enabled:'||g_crossbg_enabled,220);
413
414 /* Commented out as this is now being done in Type 4 set globals
415 -- If its the LEA run
416 -- AND current BG is enabled for cross BG reporting
417 IF g_estb_number = '0000'
418 AND
419 g_crossbg_enabled = 'Y'
420 THEN
421 -- Store all BGs with same LEA Number and
422 -- enabled for cross BG reporting
423
424 pqp_gb_t1_pension_extracts.g_nested_level := g_nested_level;
425
426 pqp_gb_t1_pension_extracts.store_cross_bg_details;
427
428 pqp_gb_t1_pension_extracts.g_nested_level := 0;
429
430 ELSE -- Non-LEA Run
431 g_master_bg_id := g_business_group_id;
432 END IF;
433
434 --ENH3 And ENH4.
435 */
436
437 --
438 -- Get balance type id for additional contribution balance
439 --
440 debug(':g_add_cont_balance_name:'||g_add_cont_balance_name,230 );
441
442 g_add_cont_bal_id := get_pay_bal_id
443 (p_balance_name => g_add_cont_balance_name
444 );
445 debug(':g_add_cont_bal_id:'||g_add_cont_bal_id,240 );
446
447
448 If g_add_cont_bal_id is not null Then
449
450 --
451 -- Get Additional Contribution Elements
452 --
453 get_pay_ele_ids_from_bal
454 (p_balance_type_id => g_add_cont_bal_id
455 ,p_effective_start_date => g_effective_start_date
456 ,p_effective_end_date => g_effective_end_date
457 ,p_tab_ele_ids => g_add_cont_ele_ids
458 );
459
460 Else
461
462 g_error_number := 92999;
463 g_error_text := 'BEN_92999_EXT_TP2_BAL_NOTFOUND';
464
465 End If; -- End if of cont bal id check...
466
467 debug('g_error_number '||g_error_number,250);
468 debug('g_error_text '||g_error_text,260);
469
470 debug_exit(l_proc_name);
471
472 End set_type2_globals;
473 --
474 -- ----------------------------------------------------------------------------
475 -- |---------------------------< calc_add_cont >-------------------------------|
476 -- ----------------------------------------------------------------------------
477 Procedure calc_add_cont
478 (p_assignment_id in number
479 ,p_effective_start_date in date
480 ,p_effective_end_date in date
481 ) is
482 --
483 l_proc_name varchar2(60) := g_proc_name || 'calc_add_cont';
484 l_add_cont_value number := 0;
485 l_effective_date date;
486 idx number := 1;
487 --
488 Begin
489
490
491 debug_enter(l_proc_name);
492 debug(l_proc_name, 800) ;
493 debug(':p_assignment_id:'||p_assignment_id ,810);
494 debug(':p_effective_start_date:'||p_effective_start_date ,820);
495 debug(':p_effective_end_date:'||p_effective_end_date ,830);
496
497
498
499 Open csr_get_end_date
500 (c_assignment_id => p_assignment_id
501 ,c_effective_start_date => p_effective_start_date
502 ,c_effective_end_date => p_effective_end_date
503 );
504 Loop
505
506 Fetch csr_get_end_date into l_effective_date;
507 Exit when csr_get_end_date%notfound;
508
509 debug(':l_effective_date:'||l_effective_date, 840 + idx/10000 );
510 debug(':g_add_cont_bal_id:'||g_add_cont_bal_id, 850 + idx/10000 );
511 --
512
513 l_add_cont_value := hr_gbbal.calc_asg_proc_ptd_date
514 (p_assignment_id => p_assignment_id
515 ,p_balance_type_id => g_add_cont_bal_id
516 ,p_effective_date => l_effective_date
517 );
518
519 debug(':l_add_cont_value:'||l_add_cont_value, 860 + idx/10000 );
520
521 If g_add_cont_value.exists(p_assignment_id) Then
522
523 debug(':inside IF of add cont value exists', 870+ idx/10000 );
524
525 g_add_cont_value(p_assignment_id) := g_add_cont_value(p_assignment_id) +
526 (l_add_cont_value * 100);
527
528 Else
529
530 debug(':inside ELSE of add cont value exists', 880 + idx/10000);
531
532 g_add_cont_value(p_assignment_id) := l_add_cont_value * 100;
533
534 End If; -- End if of add cont value exists check...
535 idx := idx + 1 ;
536 End Loop;
537 Close csr_get_end_date;
538
539 debug_exit(l_proc_name);
540
541 End calc_add_cont;
542 --
543 -- ----------------------------------------------------------------------------
544 -- |---------------------------< get_eet_info >-------------------------------|
545 -- ----------------------------------------------------------------------------
546 Procedure get_eet_info
547 (p_assignment_id in number
548 ,p_effective_start_date in date
549 ,p_effective_end_date in date
550 ,p_location_id in number
551 ,p_business_group_id in number --ENH8
552 ,p_return_status out nocopy boolean --ENH3 And ENH4
553 )
554 is
555 --
556 l_proc_name varchar2(60) := g_proc_name || 'get_eet_info';
557 l_eet_details csr_get_eet_info%rowtype;
558 l_ele_exists varchar2(1) := 'N';
559 idx number := 1;
560
561 --
562 Begin
563
564 --
565 debug_enter(l_proc_name);
566 debug(l_proc_name,600) ;
567 debug(':p_assignment_id:'||p_assignment_id,610 );
568 debug(':p_effective_start_date:'||p_effective_start_date,620 );
569 debug(':p_effective_end_date:'||p_effective_end_date,630 );
570 debug(':p_location_id:'||p_location_id ,640);
571
572
573 --ENH3 And ENH4.The g_add_cont_ele_ids contains a collection of all the element type ids
574 --linked to the Total Additional Contribution Balance across business groups.
575 For i in 1..g_add_cont_ele_ids.count Loop
576
577 debug('g_add_cont_ele_ids.count'||g_add_cont_ele_ids.count, 660 + i/10000 );
578 debug('g_add_cont_ele_ids.bg id'||g_add_cont_ele_ids(i).business_group_id, 670 + i/10000 );
579 debug('g_add_cont_ele_ids.element_type_id'||
580 g_add_cont_ele_ids(i).element_type_id, 680 + i/10000 );
581 debug('p_business_group_id '||p_business_group_id, 685 + i/10000 );
582
583
584 --ENH3 And ENH4.check if the business group id is present in the global collection.
585 IF ( NVL(g_add_cont_ele_ids(i).business_group_id,p_business_group_id)
586 = p_business_group_id
587 )
588 THEN
589
590 idx := 1;
591
592 -- Check element entries exist with additional cont ele's
593 Open csr_get_eet_info
594 (c_assignment_id => p_assignment_id
595 ,c_effective_start_date => p_effective_start_date
596 ,c_effective_end_date => p_effective_end_date
597 ,c_element_type_id => g_add_cont_ele_ids(i).element_type_id --ENH8
598 );
599 Loop
600
601 Fetch csr_get_eet_info into l_eet_details;
602 Exit when csr_get_eet_info%notfound;
603
604
605 -- Check atleast one add cont element exists
606 IF l_eet_details.element_type_id IS NOT NULL THEN
607
608 debug('element entry found ', 690 + idx/10000 );
609 debug(':l_eet_details.element_type_id :'||l_eet_details.element_type_id, 650 + idx/1000000 );
610
611 l_ele_exists := 'Y';
612
613 debug(':estb_number:'||pqp_gb_tp_pension_extracts.g_criteria_estbs(p_location_id).estb_number, 710 + idx/10000 );
614
615 --ENH3 And ENH4
616 p_return_status := true;
617 debug('return status is true',720 + idx/10000);
618 Exit;
619
620 End If; -- End of l_eet_details check
621
622 idx := idx + 1;
623
624 End Loop;--End of csr_get_eet_info
625 Close csr_get_eet_info;
626
627 END IF;--business_group_id check
628
629 End Loop;--g_add_cont_ele_ids
630
631 If l_ele_exists = 'Y' Then
632
633 debug('inside If l_ele_exists =Y ', 730 );
634
635 -- Calculate additional contribution for this effective date
636 calc_add_cont
637 (p_assignment_id => p_assignment_id
638 ,p_effective_start_date => p_effective_start_date
639 ,p_effective_end_date => p_effective_end_date
640 );
641
642 End If; -- End if of add cont element entry exists ...
643
644 debug_exit(l_proc_name);
645 EXCEPTION
646 WHEN OTHERS THEN
647 p_return_status := NULL;
648 RAISE ;
649
650 End get_eet_info;
651 --
652 -- ----------------------------------------------------------------------------
653 -- |---------------------------< get_asg_info >-------------------------------|
654 -- ----------------------------------------------------------------------------
655
656 FUNCTION get_asg_info
657 (p_assignment_id in number
658 ,p_effective_start_date in out nocopy date --ENH3 And ENH4
659 ,p_effective_end_date in date
660 ,p_location_id out nocopy number --ENH3 And ENH4
661 ,p_ext_emp_cat_cd out nocopy varchar2 --ENH3 And ENH4
662 ) RETURN BOOLEAN --ENH3 And ENH4
663 is
664 --
665 l_proc_name varchar2(60) := g_proc_name || 'get_asg_info';
666 i number := 0;
667 l_asg_details csr_get_asg_info%rowtype;
668 l_next_asg_details csr_get_asg_info%rowtype;
669 l_tab_asg_details t_asg_info;
670 l_return_status boolean := false ; --ENH3 And ENH4
671 idx NUMBER;
672 idy NUMBER;
673 l_ext_emp_cat_cd VARCHAR2(10);
674 l_effective_start_date DATE;
675 l_location_id hr_location_extra_info.location_id%TYPE;
676 l_asg_emp_cat_cd per_all_assignments_f.employment_category%TYPE;
677 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
678
679 --
680 Begin
681
682 --
683 debug_enter(l_proc_name);
684 debug(l_proc_name,310) ;
685 debug(':p_assignment_id:'||p_assignment_id ,320);
686 debug(':p_effective_start_date:'||p_effective_start_date ,330);
687 debug(l_proc_name ||':p_effective_end_date:'||p_effective_end_date ,340);
688
689 idx := 0;
690
691 Open csr_get_asg_info
692 (c_assignment_id => p_assignment_id
693 ,c_effective_start_date => p_effective_start_date
694 ,c_effective_end_date => p_effective_end_date
695 );
696
697 Loop -- Loop 1
698 Fetch csr_get_asg_info into l_asg_details;
699 Exit when csr_get_asg_info%notfound;
700
701 idx := idx + 1;
702
703
704 debug(':inside Loop 1', 350 +idx/10000) ;
705 debug(':l_asg_details.person_id:'||l_asg_details.person_id, 360 +idx/10000);
706 debug(':l_asg_details.assignment_id:'||l_asg_details.assignment_id ,370 +idx/10000);
707 debug(':l_asg_details.location_id:'||l_asg_details.location_id ,380 +idx/10000);
708 debug(':l_asg_details.business_group_id:'||l_asg_details.business_group_id ,390+idx/10000);
709
710 -- Check whether the establishment is a criteria establishment
711 If pqp_gb_tp_pension_extracts.g_criteria_estbs.exists(l_asg_details.location_id) Then
712
713 i := i + 1;
714
715 l_tab_asg_details(i) := l_asg_details;
716
717 -- Check whether the next assignment row has a location
718 -- Change or not
719 idy := 0;
720
721 Loop -- Loop 2
722 Fetch csr_get_asg_info into l_next_asg_details;
723 Exit when csr_get_asg_info%notfound;
724
725 idy := idy + 1;
726
727 debug(':inside Loop 2', 390 +idy/10000) ;
728 debug(':l_next_asg_details.person_id:'||l_next_asg_details.person_id , 410 + idy/10000);
729 debug(':l_next_asg_details.assignment_id:'||l_next_asg_details.assignment_id,420 + idy/10000);
730 debug(':l_next_asg_details.location_id:'||l_next_asg_details.location_id,430 + idy/10000 );
731
732 If pqp_gb_tp_pension_extracts.g_criteria_estbs.exists(l_next_asg_details.location_id) Then
733
734 debug(':inside if ', 440 +idy/10000) ;
735 -- Extend the effective end date
736 l_tab_asg_details(i).effective_end_date :=
737 l_next_asg_details.effective_end_date;
738
739 Else
740 debug(':inside else ', 450 +idy/10000) ;
741 Exit;
742
743 End If; -- End if of estb check...
744 End Loop; -- End of Loop 2...
745
746 End If; -- End if of estb check...
747
748 End Loop; -- End of loop 1...
749 Close csr_get_asg_info;
750
751 -- Check atleast one assignment qualifies for type 2
752 If l_tab_asg_details.count > 0 Then
753
754 debug(':inside If l_tab_asg_details.count > 0 ', 460) ;
755
756 debug(':l_tab_asg_details(1).person_id:' ||l_tab_asg_details(1).person_id, 480) ;
757
758 l_return_status := false;
759
760 For i in 1..l_tab_asg_details.count Loop
761
762
763 debug('inside For ', 490 + i/10000) ;
764 debug(':l_tab_asg_details(i).person_id:' ||l_tab_asg_details(i).person_id, 510+i/10000) ;
765 debug(':l_tab_asg_details(i).assignment_id:' ||l_tab_asg_details(i).assignment_id,520+i/10000) ;
766 debug(':l_tab_asg_details(i).location_id:' ||l_tab_asg_details(i).location_id ,530+i/10000) ;
767
768 l_location_id := l_tab_asg_details(i).location_id;
769 l_effective_start_date := l_tab_asg_details(i).effective_start_date;
770 l_asg_emp_cat_cd := l_tab_asg_details(i).asg_emp_cat_cd;
771 l_business_group_id := l_tab_asg_details(i).business_group_id;
772 --
773 -- Check whether additional contribution element exists
774 --
775
776 get_eet_info
777 (p_assignment_id => l_tab_asg_details(i).assignment_id
778 ,p_effective_start_date => l_tab_asg_details(i).effective_start_date
779 ,p_effective_end_date => l_tab_asg_details(i).effective_end_date
780 ,p_location_id => l_tab_asg_details(i).location_id
781 ,p_business_group_id => l_tab_asg_details(i).business_group_id
782 ,p_return_status => l_return_status -- OUT
783 );
784
785 End Loop;
786
787 End If; -- End if of assignment qual for type2 check...
788
789 IF l_return_status THEN
790
791 l_ext_emp_cat_cd := pqp_gb_t1_pension_extracts.Get_Translate_Asg_Emp_Cat_Code
792 (
793 l_asg_emp_cat_cd
794 ,p_effective_start_date
795 ,'Pension Extracts Employment Category Code'
796 ,l_business_group_id
797 );
798
799 debug('l_ext_emp_cat_cd is '|| l_ext_emp_cat_cd,171);
800
801 l_ext_emp_cat_cd := nvl(l_ext_emp_cat_cd,'F');
802
803 IF l_ext_emp_cat_cd = 'P' THEN
804
805 pqp_gb_t1_pension_extracts.g_part_time_asg_count :=
806 pqp_gb_t1_pension_extracts.g_part_time_asg_count + 1;
807 debug('Incrementing part time assignment count',172);
808
809 END IF;
810 -- Commented below to fix bug 7476796
811 -- p_effective_start_date := l_effective_start_date;
812 p_location_id := l_location_id;
813 p_ext_emp_cat_cd := l_ext_emp_cat_cd;
814
815 END IF;-- return_status
816
817 RETURN l_return_status;
818
819 debug_exit(l_proc_name);
820
821 EXCEPTION
822 WHEN OTHERS THEN
823
824 p_effective_start_date := NULL;
825 p_location_id := NULL;
826
827
828 RAISE ;
829
830 End get_asg_info;
831 --
832 -- ----------------------------------------------------------------------------
833 -- |---------------------------< get_aat_info >-------------------------------|
834 -- ----------------------------------------------------------------------------
835
836 FUNCTION get_aat_info
837 (p_assignment_id in number
838 ,p_effective_start_date in date
839 ,p_effective_end_date in date
840 ,p_ext_emp_cat_cd in varchar2 --ENH3 And ENH4
841 ,p_location_id in number --ENH3 And ENH4
842 ) RETURN BOOLEAN --ENH3 And ENH4
843 is
844 --
845 l_proc_name varchar2(60) := g_proc_name || 'get_aat_info';
846 i number := 0;
847 l_aat_info csr_get_aat_info%rowtype;
848 l_next_aat_info csr_get_aat_info%rowtype;
849 l_tab_aat_info t_aat_info;
850 l_teacher boolean := true; --ENH3 And ENH4
851 idx NUMBER;
852 idy NUMBER;
853 l_estb_number VARCHAR2(10);
854 l_estb_type VARCHAR2(200);
855 --
856 Begin
857
858
859 debug_enter(l_proc_name);
860 debug(l_proc_name,210) ;
861 debug(':p_assignment_id:'||p_assignment_id ,220);
862 debug(':p_effective_start_date:'||p_effective_start_date ,230);
863 debug(':p_effective_end_date:'||p_effective_end_date ,240 );
864 debug(':p_location_id:'||p_location_id ,245 );
865
866 idx := 0;
867
868 Open csr_get_aat_info
869 (c_assignment_id => p_assignment_id
870 ,c_effective_start_date => p_effective_start_date
871 ,c_effective_end_date => p_effective_end_date
872 );
873 Loop -- Loop 1
874
875
876 Fetch csr_get_aat_info into l_aat_info;
877 Exit when csr_get_aat_info%notfound;
878
879 idx := idx + 1;
880
881 --
882 debug(':inside Loop 1', 250 + idx/10000) ;
883 debug(':l_aat_info.assignment_attribute_id:'||l_aat_info.assignment_attribute_id ,260 + idx/10000);
884 debug(':l_aat_info.assignment_id:'||l_aat_info.assignment_id ,270 + idx/10000);
885 debug(':l_aat_info.effective_start_date:'||l_aat_info.effective_start_date,280 + idx/10000);
886 debug(':l_aat_info.effective_end_date:'||l_aat_info.effective_end_date,290 + idx/10000);
887 debug(':l_aat_info.tp_is_teacher:'||l_aat_info.tp_is_teacher,310 + idx/10000);
888 debug(':l_aat_info.tp_elected_pension:'||l_aat_info.tp_elected_pension,320 + idx/10000);
889 --
890
891 i := i + 1;
892 l_tab_aat_info(i) := l_aat_info;
893
894 idy := 0;
895
896 Loop -- Loop 2
897
898 -- Check whether the subsequent row qualifies the below cond
899 --
900 Fetch csr_get_aat_info into l_next_aat_info;
901 Exit when csr_get_aat_info%notfound;
902
903 idy := idy + 1;
904 --
905 debug(':inside Loop 2', 330 + idy/10000 ) ;
906 debug(':l_next_aat_info.assignment_attribute_id:'||l_next_aat_info.assignment_attribute_id,340 + idy/10000 );
907 debug(':l_next_aat_info.assignment_id:'||l_next_aat_info.assignment_id,350 + idy/10000 );
908 debug(':l_next_aat_info.effective_start_date:'||l_next_aat_info.effective_start_date,360 + idy/10000 );
909 debug(':l_next_aat_info.effective_end_date:'||l_next_aat_info.effective_end_date,370 + idy/10000 );
910 debug(':l_next_aat_info.tp_is_teacher:'||l_next_aat_info.tp_is_teacher,380 + idy/10000 );
911 debug(':l_next_aat_info.tp_elected_pension:'||l_next_aat_info.tp_elected_pension,390 + idy/10000 );
912 --
913
914 If nvl(l_next_aat_info.tp_is_teacher, hr_api.g_varchar2) =
915 nvl(l_tab_aat_info(i).tp_is_teacher, hr_api.g_varchar2)
916 and
917 nvl(l_next_aat_info.tp_elected_pension, hr_api.g_varchar2) =
918 nvl(l_tab_aat_info(i).tp_elected_pension, hr_api.g_varchar2)
919 Then
920
921 --
922 -- Adjust the effective end date of the previous row to
923 -- this one
924 --
925 l_tab_aat_info(i).effective_end_date :=
926 l_next_aat_info.effective_end_date;
927
928 Else
929
930 i := i + 1;
931 l_tab_aat_info(i) := l_next_aat_info;
932
933 End If; -- End if of subsequent row check for teacher ...
934
935 End Loop; -- End Loop 2 ...
936
937 End Loop; -- End Loop 1 ...
938 Close csr_get_aat_info;
939
940 --
941 -- Check atleast one assignment attribute for the given assignment
942 -- exists
943 --
944 If l_tab_aat_info.count > 0 Then
945
946 --
947 debug(l_proc_name ||':inside If l_tab_aat_info.count > 0 ', 410 ) ;
948 --
949 For i in 1..l_tab_aat_info.count Loop
950 --
951
952 debug('inside For ', 420 + i/1000000) ;
953 debug(':l_tab_aat_info(i).assignment_attribute_id:'||l_tab_aat_info(i).assignment_attribute_id,430 + i/1000000);
954 debug(':l_tab_aat_info(i).assignment_id:'||l_tab_aat_info(i).assignment_id,440 + i/1000000);
955 debug(':l_tab_aat_info(i).effective_start_date:'||l_tab_aat_info(i).effective_start_date , 450 + i/1000000);
956 debug(':l_tab_aat_info(i).effective_end_date:'||l_tab_aat_info(i).effective_end_date ,460 + i/1000000);
957 debug(':l_tab_aat_info(i).tp_is_teacher:'||l_tab_aat_info(i).tp_is_teacher ,470 + i/1000000);
958 debug(':l_tab_aat_info(i).tp_elected_pension:'||l_tab_aat_info(i).tp_elected_pension ,480 + i/1000000);
959 l_estb_number := pqp_gb_tp_pension_extracts.g_criteria_estbs(p_location_id).estb_number;
960 l_estb_type := pqp_gb_tp_pension_extracts.g_criteria_estbs(p_location_id).estb_type;
961
962 debug(':l_estb_number:'||l_estb_number,490 + i/1000000);
963 debug(':p_ext_emp_cat_cd:'||p_ext_emp_cat_cd,490 + i/1000000);
964 debug(':l_estb_type:'||l_estb_type,490 + i/1000000);
965
966 --
967 -- Assignment attribute exists
968 -- get assignment information
969 --
970 -- Check whether this assignment attribute is a teacher and
971 -- has elected pension
972
973 IF (nvl(l_tab_aat_info(i).tp_is_teacher,'NONT') IN ('TCHR', 'TTR6')) THEN
974
975 IF (
976 nvl(l_tab_aat_info(i).tp_elected_pension,'X') = 'N'
977 AND
978 (
979 l_estb_number = '0966'
980 OR
981 (p_ext_emp_cat_cd = 'P'
982 AND
983 l_estb_type <> 'LEA_ESTB'
984 )
985 OR
986 ( p_ext_emp_cat_cd = 'F'
987 AND
988 l_estb_type = 'IND_ESTB'
989 )
990 )
991 ) THEN
992
993 debug('not a teacher hence warn', 510) ;
994 l_teacher := false;
995 ELSE
996 debug('is a teacher hence continue', 513) ;
997 -- established that asg was a teacher in the year,
998 -- we no longer need to check further
999 l_teacher := true;
1000 exit;
1001
1002 END IF;
1003
1004 ELSE
1005
1006 debug('not a teacher hence warn', 515) ;
1007 l_teacher := false;
1008
1009 END IF; -- End if of teacher and pension check...
1010
1011 End Loop;
1012
1013 Else
1014
1015 --the assignment does not have an entry for teacher.
1016 debug(':inside Else of If l_tab_aat_info.count > 0 ', 520) ;
1017 l_teacher := false;
1018
1019
1020 End If; -- End if of assignment attribute check...
1021
1022 return l_teacher;
1023
1024 debug_exit(l_proc_name);
1025
1026 End get_aat_info;
1027 --
1028
1029
1030 -- ENH3 And ENH4
1031 -- ----------------------------------------------------------------------------
1032 -- |-----------------------< get_all_secondary_asgs >-----------------------|
1033 -- ----------------------------------------------------------------------------
1034 --
1035
1036 FUNCTION get_all_secondary_asgs
1037 (p_primary_assignment_id IN NUMBER
1038 ,p_effective_date IN DATE
1039 ,p_person_id IN NUMBER
1040 ) RETURN pqp_gb_t1_pension_extracts.t_sec_asgs_type
1041 IS
1042
1043 -- Rowtype Variable Declaration
1044 l_sec_asgs pqp_gb_t1_pension_extracts.csr_sec_assignments%ROWTYPE;
1045 l_all_sec_asgs pqp_gb_t1_pension_extracts.t_sec_asgs_type;
1046 idx NUMBER;
1047
1048 --
1049 l_proc_name VARCHAR2(61):=
1050 g_proc_name||'get_all_secondary_asgs';
1051
1052 BEGIN -- get_all_secondary_asgs
1053
1054 debug_enter(l_proc_name);
1055
1056 debug(' p_primary_assignment_id '||p_primary_assignment_id,10);
1057 debug(' p_person_id '|| p_person_id ,20);
1058 debug(' p_effective_date '||p_effective_date,30);
1059 debug(' g_effective_run_date '||g_effective_run_date,40);
1060 debug(' g_cross_per_enabled '||g_cross_per_enabled,50);
1061 debug(' g_business_group_id '||g_business_group_id,60);
1062
1063 --to be removed later
1064 -- g_cross_per_enabled := 'Y';
1065
1066 -- Fetch secondary assignments
1067 idx := 0;
1068
1069 FOR l_sec_asgs IN pqp_gb_t1_pension_extracts.csr_sec_assignments(p_primary_assignment_id
1070 ,p_person_id
1071 ,p_effective_date
1072 )
1073 LOOP
1074
1075 idx := idx + 1;
1076
1077 debug('adding secondary assignment to the collection '||l_sec_asgs.assignment_id,80 + idx/10000);
1078 -- Add this to the table of valid secondary asgs
1079 l_all_sec_asgs(l_sec_asgs.assignment_id) := l_sec_asgs;
1080
1081 --
1082 END LOOP; -- l_sec_asg_details IN csr_sec_asg_details
1083
1084 debug_exit(l_proc_name);
1085 --
1086 RETURN l_all_sec_asgs;
1087 --
1088 EXCEPTION
1089 WHEN OTHERS THEN
1090
1091 hr_utility.set_location('Entering excep:'||l_proc_name, 35);
1092 hr_utility.set_location('SQLCODE :'||SQLCODE, 40);
1093 hr_utility.set_location('SQLERRM :'||SQLERRM, 50);
1094 debug_exit(' Others in '||l_proc_name);
1095 RAISE;
1096 END; -- get_all_secondary_asgs
1097
1098 --
1099 -- ----------------------------------------------------------------------------
1100 -- |-----------------------< chk_teacher_qual_for_tp2 >-----------------------|
1101 -- ----------------------------------------------------------------------------
1102 --
1103
1104 Function chk_teacher_qual_for_tp2
1105 (p_business_group_id in number -- context
1106 ,p_effective_date in date -- context
1107 ,p_assignment_id in number -- context
1108 ,p_error_text out nocopy varchar2
1109 ,p_error_number out nocopy number
1110 -- ,p_trace in varchar2 default null
1111 ) return varchar2 -- Y or N
1112 is
1113 --
1114 l_inclusion_flag varchar2(20) := 'N';
1115 l_sec_assignments pqp_gb_t1_pension_extracts.t_sec_asgs_type;
1116 l_is_a_teacher varchar2(1) := 'Y';
1117 l_person_id per_all_people_f.person_id%TYPE;
1118 l_start_date DATE;
1119 l_proc_name varchar2(61) := g_proc_name || 'chk_teacher_qual_for_tp2';
1120 l_curr_sec_asg_id per_all_assignments_f.assignment_id%TYPE;
1121 l_prev_sec_asg_id per_all_assignments_f.assignment_id%TYPE;
1122 idx NUMBER;
1123 l_first_time BOOLEAN := TRUE; --Flag to add sec asg details to primary asg
1124 -- if primary is not valid asg.
1125 l_business_group_id NUMBER;
1126 l_asg_cat_cd VARCHAR2(10);
1127 l_location_id hr_location_extra_info.location_id%TYPE;
1128 l_ext_emp_cat_cd VARCHAR2(10);
1129 l_effective_start_date DATE;
1130
1131 --
1132 Begin
1133
1134 l_effective_start_date := null;
1135 debug_enter(l_proc_name);
1136 debug(l_proc_name,10) ;
1137 debug(':p_assignment_id:'||p_assignment_id ,20);
1138 debug(':p_effective_date:'||p_effective_date ,30 );
1139 debug(':p_business_group_id:'||p_business_group_id ,40);
1140
1141 OPEN csr_get_person_id(p_assignment_id);
1142 FETCH csr_get_person_id INTO l_person_id,l_business_group_id;
1143 CLOSE csr_get_person_id;
1144
1145 debug(':l_person_id:'||l_person_id ,50);
1146 debug(':l_business_group_id:'||l_business_group_id ,55);
1147
1148
1149 g_inclusion_flag := 'N';
1150 g_error_text := null;
1151 g_error_number := null;
1152
1153 -- Bug fix 2848696
1154 -- Effective date passed should be the actual effective date
1155 -- passed whilst submitting extract process
1156 -- and not the session date
1157
1158 If g_business_group_id is null Then
1159
1160 debug(':inside If g_business_group_id is null', 60 );
1161 --ENH3 And ENH4 .Added the new parameter lea number.
1162
1163 pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
1164
1165
1166
1167 pqp_gb_tp_pension_extracts.set_extract_globals (p_business_group_id
1168 -- ,p_effective_date
1169 ,ben_ext_person.g_effective_date
1170 ,p_assignment_id
1171 );
1172
1173 pqp_gb_tp_pension_extracts.g_nested_level := 0;
1174
1175
1176 g_business_group_id := pqp_gb_tp_pension_extracts.g_business_group_id;
1177 g_effective_date := pqp_gb_tp_pension_extracts.g_effective_date;
1178 g_header_system_element := pqp_gb_tp_pension_extracts.g_header_system_element;
1179
1180 debug(':g_business_group_id:'||g_business_group_id,80 );
1181 debug(':g_effective_date:'||g_effective_date,90 );
1182 debug(':g_header_system_element:'||g_header_system_element,110 );
1183
1184
1185 g_add_cont_ele_ids.delete;
1186
1187 set_effective_dates;
1188
1189 set_type2_globals;
1190
1191 --set the g_effective_run_date for type1 as its being used in csr_sec_assignments.
1192 pqp_gb_t1_pension_extracts.g_effective_run_date := g_effective_run_date;
1193
1194 If g_error_number is not null Then
1195
1196 debug(':inside If g_error_number is not null'||g_error_number, 120 );
1197 debug('g_error_text '||g_error_text,130);
1198 p_error_text := g_error_text;
1199 p_error_number := g_error_number;
1200 l_inclusion_flag := 'ERROR';
1201 debug('l_inclusion_flag '||l_inclusion_flag,140);
1202 Return l_inclusion_flag;
1203
1204 End If; -- End if of error check...
1205
1206 End If;-- end of g_business_group_id is null
1207
1208 l_effective_start_date := g_effective_start_date;
1209
1210 -- Bugfix -- Bugfix 3671727: Performance enhancement
1211 -- If no location exists in the list of valid criteria
1212 -- establishments, then no point doing all checks
1213 -- Just warn once and skip every assignment
1214 IF pqp_gb_tp_pension_extracts.g_criteria_estbs.COUNT = 0 THEN
1215
1216 debug('Setting inclusion flag to N as no locations EXIST.', 145);
1217 l_inclusion_flag := 'N';
1218
1219 pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
1220 -- Call TP4 pkg proc to warning for no locations
1221 pqp_gb_tp_pension_extracts.warn_if_no_loc_exist
1222 (p_assignment_id => p_assignment_id) ;
1223 pqp_gb_tp_pension_extracts.g_nested_level := 0;
1224
1225 RETURN l_inclusion_flag ; -- the assignment will eventually fail for validity as no location exists.
1226
1227 END IF ;
1228
1229 pqp_gb_t1_pension_extracts.g_part_time_asg_count := 0;
1230
1231 -- Check if this person should be reported by the current run
1232
1233 pqp_gb_t1_pension_extracts.g_nested_level := g_nested_level;
1234
1235 IF pqp_gb_t1_pension_extracts.chk_report_person
1236 (p_business_group_id
1237 ,p_effective_date
1238 ,p_assignment_id
1239 )
1240 THEN
1241
1242 debug('chk_report_person is true', 150 );
1243
1244 debug('l_person_id is '||l_person_id, 160 );
1245
1246 --ENH8:Added the code below here,so that it checks
1247 --if the assignment is a valid record for type 2.
1248 --before checking the assignment attributes.
1249
1250 --
1251 -- Check if the person is eligible for Type 2 Report
1252 --
1253
1254 --Y indicates that the record is a valid type 2 record.
1255 IF get_asg_info
1256 (
1257 p_assignment_id => p_assignment_id
1258 ,p_effective_start_date => g_effective_start_date
1259 ,p_effective_end_date => g_effective_end_date
1260 ,p_location_id => l_location_id -- OUT
1261 ,p_ext_emp_cat_cd => l_ext_emp_cat_cd -- OUT
1262 )
1263 THEN
1264
1265 debug('get_asg_info is true ', 170 );
1266
1267 g_inclusion_flag := 'Y';
1268
1269 pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id :=
1270 l_person_id;
1271
1272 pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date :=
1273 g_effective_start_date;
1274
1275 pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
1276 pqp_gb_tp_pension_extracts.g_criteria_estbs(l_location_id).estb_number;
1277
1278 debug('estb_number is '||
1279 pqp_gb_tp_pension_extracts.g_criteria_estbs(l_location_id).estb_number
1280 , 175 );
1281
1282 --
1283 -- Check the person is a teacher and has elected pension
1284 --
1285 debug('calling get_aat_info', 180) ;
1286 -- checks if the assignment is a teacher,
1287 -- else gives a warning.
1288 IF get_aat_info
1289 (
1290 p_assignment_id => p_assignment_id
1291 ,p_effective_start_date => g_effective_start_date
1292 ,p_effective_end_date => g_effective_end_date
1293 ,p_ext_emp_cat_cd => l_ext_emp_cat_cd
1294 ,p_location_id => l_location_id
1295 )
1296 THEN
1297
1298 debug('get_aat_info is true'|| l_curr_sec_asg_id, 190 );
1299 Null;
1300
1301 ELSE
1302
1303 debug('get_aat_info is false'|| l_curr_sec_asg_id, 210 );
1304 l_is_a_teacher := 'N';
1305
1306 END IF;-- end if get_aat_info
1307
1308
1309 ELSE
1310
1311 --if the primary assignment is not a valid type 2 record,set the report_asg
1312 --flag to 'N'
1313 pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).report_asg
1314 := 'N';
1315
1316 debug('report_asg is N '|| p_assignment_id , 230 );
1317
1318 End If; -- End if get_asg_info
1319
1320
1321 --End of ENH8.
1322
1323 --ENH4:Earlier the check for type 2 records was carried out
1324 --for primary assignment only.
1325 --its now to be done for all the assignments.
1326
1327 l_sec_assignments := get_all_secondary_asgs
1328 (
1329 p_primary_assignment_id => p_assignment_id
1330 ,p_effective_date => g_effective_start_date
1331 ,p_person_id => l_person_id
1332 );
1333
1334 idx := 0;
1335
1336 IF l_sec_assignments.COUNT > 0 THEN
1337
1338 debug('secondary assignments count > 0 ' , 240 );
1339
1340 l_curr_sec_asg_id := l_sec_assignments.FIRST;
1341
1342 WHILE l_curr_sec_asg_id IS NOT NULL
1343 LOOP
1344
1345 idx := idx + 1;
1346
1347 debug('seconday assignment found'|| l_curr_sec_asg_id, 250 + idx/10000);
1348
1349 IF get_asg_info
1350 (
1351 p_assignment_id => l_curr_sec_asg_id
1352 ,p_effective_start_date => g_effective_start_date
1353 ,p_effective_end_date => g_effective_end_date
1354 ,p_location_id => l_location_id -- OUT
1355 ,p_ext_emp_cat_cd => l_ext_emp_cat_cd -- OUT
1356 )
1357 THEN
1358
1359 debug('get_asg_info is true'|| l_curr_sec_asg_id, 260 + idx/10000);
1360
1361 g_inclusion_flag := 'Y';
1362
1363 pqp_gb_tp_pension_extracts.g_ext_asg_details(l_curr_sec_asg_id).person_id :=
1364 l_person_id;
1365
1366 pqp_gb_tp_pension_extracts.g_ext_asg_details(l_curr_sec_asg_id).start_date :=
1367 g_effective_start_date;
1368
1369 pqp_gb_tp_pension_extracts.g_ext_asg_details(l_curr_sec_asg_id).estb_number :=
1370 pqp_gb_tp_pension_extracts.g_criteria_estbs(l_location_id).estb_number;
1371
1372 debug('l_estb_number is '||
1373 pqp_gb_tp_pension_extracts.g_criteria_estbs(l_location_id).estb_number
1374 ,265 );
1375
1376 -- Check if hte Primary asg is not a valid asg
1377 -- then Add the secondary asg details at Primary asg also
1378 -- but only for the very first sec asg.
1379
1380 IF l_first_time --Should be TRUE for the first time..
1381 AND -- the primary asg is not a valid asg
1382 (pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).report_asg ='N') THEN
1383
1384 debug ('Primary is not valid, adding details of sec to primary in global collection',267) ;
1385
1386 pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_person_id;
1387 pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := g_effective_start_date; --l_start_date;
1388 pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
1389 pqp_gb_tp_pension_extracts.g_criteria_estbs(l_location_id).estb_number;
1390
1391 l_first_time := FALSE; -- reset the variable to prevent overwriting again and again.
1392
1393 END IF;
1394
1395
1396 IF get_aat_info
1397 (
1398 p_assignment_id => l_curr_sec_asg_id
1399 ,p_effective_start_date => g_effective_start_date
1400 ,p_effective_end_date => g_effective_end_date
1401 ,p_ext_emp_cat_cd => l_ext_emp_cat_cd
1402 ,p_location_id => l_location_id
1403 )
1404 THEN
1405
1406 debug('get_aat_info is true'|| l_curr_sec_asg_id, 270 + idx/10000);
1407 Null;
1408
1409 ELSE
1410
1411 debug('get_aat_info is false'|| l_curr_sec_asg_id, 280 + idx/10000);
1412 l_is_a_teacher := 'N';
1413
1414 END IF;-- get_aat_info
1415
1416 --calculate the contributions and add them to the primary assignment
1417 IF g_add_cont_value.exists(l_curr_sec_asg_id) Then
1418
1419 debug(':inside If g_add_cont_value.exists' , 290 + idx/10000);
1420
1421 IF g_add_cont_value.exists(p_assignment_id) Then
1422
1423 g_add_cont_value(p_assignment_id) :=
1424 g_add_cont_value(p_assignment_id) +
1425 g_add_cont_value(l_curr_sec_asg_id);
1426
1427 ELSE
1428
1429 g_add_cont_value(p_assignment_id) :=
1430 g_add_cont_value(l_curr_sec_asg_id);
1431
1432 END IF;
1433
1434 debug(':g_add_cont_value(p_assignment_id):'||g_add_cont_value(p_assignment_id), 320 + idx/10000);
1435
1436 END IF; -- End if of add cont value exist check...
1437
1438
1439 END IF;--get_asg_info
1440
1441 -- Assign the current asg id to prev asg id
1442 -- and reset curr asg id, ready for the next one
1443 l_prev_sec_asg_id := l_curr_sec_asg_id;
1444 l_curr_sec_asg_id := NULL;
1445
1446 l_curr_sec_asg_id := l_sec_assignments.NEXT(l_prev_sec_asg_id);
1447
1448 debug('l_prev_sec_asg_id is '|| l_prev_sec_asg_id , 330 + idx/10000);
1449 debug('l_curr_sec_asg_id is '|| l_curr_sec_asg_id , 340 + idx/10000);
1450
1451
1452 END LOOP;--end of secondary assignments check
1453
1454 END IF;--count of secondary assignments > 0
1455
1456 -- CLOSE csr_sec_assignments;
1457
1458 END IF;--chk_report_person
1459
1460 pqp_gb_t1_pension_extracts.g_nested_level := 0;
1461
1462 l_inclusion_flag := g_inclusion_flag;
1463 --
1464 debug(':l_inclusion_flag:'||l_inclusion_flag, 350 );
1465 hr_utility.set_location('Leaving: '||l_proc_name, 360);
1466 --
1467
1468 IF l_inclusion_flag = 'Y' THEN
1469
1470 -- The following piece of code raises a warning if
1471 -- there exist more than one lea with the same lea Number within a BG.
1472 -- the warning is raised for the first valid assignment for a single Run.
1473 -- the flag for warning is set during the global setting through set_extract_globals.
1474
1475 pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
1476 pqp_gb_tp_pension_extracts.warn_if_multi_lea_exist (p_assignment_id => p_assignment_id);
1477 pqp_gb_tp_pension_extracts.g_nested_level := 0;
1478
1479 IF l_is_a_teacher = 'N' THEN
1480
1481 --set the warning
1482 g_error_number := 93007;
1483 g_error_text := 'BEN_93007_EXT_TP2_NOT_TEACHER';
1484
1485 END IF;
1486
1487 END IF;
1488
1489 p_error_number := g_error_number;
1490 p_error_text := g_error_text;
1491
1492 g_nested_level := 1;
1493
1494 -- restoring the global start date for next asg
1495 g_effective_start_date := l_effective_start_date;
1496
1497 debug_exit(l_proc_name
1498 ,'Y'-- turn trace off
1499 );
1500 Return l_inclusion_flag;
1501
1502 -- Added by tmehra for nocopy changes Feb'03
1503
1504 EXCEPTION
1505 WHEN OTHERS THEN
1506 if l_effective_start_date is not null
1507 then
1508 g_effective_start_date := l_effective_start_date;
1509 end if;
1510 hr_utility.set_location('Entering excep:'||l_proc_name, 35);
1511 hr_utility.set_location('SQLCODE :'||SQLCODE, 40);
1512 hr_utility.set_location('SQLERRM :'||SQLERRM, 50);
1513 p_error_number := SQLCODE;
1514 p_error_text := SQLERRM;
1515 debug_exit(' Others in '||l_proc_name
1516 ,'Y' -- turn trace off
1517 );
1518 RAISE;
1519
1520 End chk_teacher_qual_for_tp2;
1521 --
1522 -- ----------------------------------------------------------------------------
1523 -- |-----------------------< get_add_cont_value >-----------------------------|
1524 -- ----------------------------------------------------------------------------
1525 Function get_add_cont_value
1526 (p_assignment_id in number)
1527 Return varchar2 is
1528 --
1529 l_proc_name varchar2(61) := g_proc_name || 'get_add_cont_value';
1530 l_add_cont_value number := 0;
1531 l_add_cont varchar2(6);
1532 --
1533 Begin
1534
1535
1536 debug_enter(l_proc_name);
1537 debug(':p_assignment_id:'||p_assignment_id, 10 );
1538
1539
1540
1541 If g_add_cont_value.exists(p_assignment_id) Then
1542
1543 debug(':inside If g_add_cont_value.exists(p_assignment_id)', 20 );
1544 l_add_cont_value := g_add_cont_value(p_assignment_id);
1545
1546 Else
1547
1548 l_add_cont_value := 0;
1549
1550 End If; -- End if of add cont value exists check...
1551
1552 -- Fix for bug#10092703
1553 --l_add_cont := lpad(l_add_cont_value,6,' ');
1554 l_add_cont := lpad(abs(l_add_cont_value),6,' ');
1555
1556 debug(':l_add_cont:'||l_add_cont, 30 );
1557
1558 debug_exit(l_proc_name);
1559
1560 Return l_add_cont;
1561
1562 End get_add_cont_value;
1563 --
1564 -- ----------------------------------------------------------------------------
1565 -- |-----------------------< get_add_cont_refund_ind >------------------------|
1566 -- ----------------------------------------------------------------------------
1567 Function get_add_cont_refund_ind
1568 (p_assignment_id in number)
1569 Return number is
1570 --
1571 Cursor csr_translate_sign is
1572 select decode(sign(g_add_cont_value(p_assignment_id)),-1,1,0)
1573 from dual;
1574
1575 l_proc_name varchar2(61) := g_proc_name || 'get_add_cont_refund_ind';
1576 l_add_cont_ind number := 0;
1577 --
1578 Begin
1579
1580 --
1581 hr_utility.set_location('Entering: '||l_proc_name, 5);
1582 --
1583 debug_enter(l_proc_name);
1584 debug(':p_assignment_id:'||p_assignment_id, 1150 );
1585
1586 -- Fix for bug#10092703
1587 If g_add_cont_value.exists(p_assignment_id) Then
1588 Open csr_translate_sign;
1589 Fetch csr_translate_sign into l_add_cont_ind;
1590 Close csr_translate_sign;
1591 End If;
1592
1593 debug(':l_add_cont_ind:'||l_add_cont_ind, 1160 );
1594 --
1595 hr_utility.set_location('Leaving: '||l_proc_name, 15);
1596 --
1597 debug_exit(l_proc_name);
1598
1599 Return l_add_cont_ind;
1600
1601 End get_add_cont_refund_ind;
1602 --
1603 -- ----------------------------------------------------------------------------
1604 -- |-----------------------< get_financial_year >-----------------------------|
1605 -- ----------------------------------------------------------------------------
1606 Function get_financial_year
1607 Return varchar2 is
1608 --
1609 l_proc_name varchar2(60) := g_proc_name || 'get_financial_year';
1610 l_financial_year varchar2(2);
1611 --
1612 Begin
1613
1614 --
1615 hr_utility.set_location('Entering: '||l_proc_name, 5);
1616 --
1617 debug_enter(l_proc_name);
1618
1619 l_financial_year := TO_CHAR(g_effective_end_date, 'YY');
1620
1621 --
1622 hr_utility.set_location('Leaving: '||l_proc_name, 15);
1623 --
1624 debug_exit(l_proc_name);
1625
1626 Return l_financial_year;
1627
1628 End get_financial_year;
1629 --
1630 -- ----------------------------------------------------------------------------
1631 -- |-----------------------< get_total_add_cont >-----------------------------|
1632 -- ----------------------------------------------------------------------------
1633 Function get_total_add_cont
1634 Return varchar2 is
1635 --
1636 Cursor csr_get_total_add_cont
1637 (p_ext_rcd_id ben_ext_rcd.ext_rcd_id%TYPE)
1638 is
1639 select sum(dtl.val_10) total_value
1640 from ben_ext_rslt_dtl dtl
1641 --,ben_ext_rcd rcd
1642 where dtl.ext_rslt_id = ben_ext_thread.g_ext_rslt_id
1643 and dtl.ext_rcd_id = p_ext_rcd_id;
1644 -- and rcd.rcd_type_cd = 'D';
1645
1646 l_proc_name varchar2(60) := g_proc_name || 'get_total_add_cont';
1647 l_total_add_cont_value number := 0;
1648 l_total_add_cont varchar2(10);
1649 l_ext_rcd_id ben_ext_rcd.ext_rcd_id%TYPE;
1650 --
1651 Begin
1652
1653 --
1654 hr_utility.set_location('Entering: '||l_proc_name, 5);
1655 --
1656 debug_enter(l_proc_name);
1657
1658 -- 11.5.10_CU2: Performance fix :
1659 -- get the ben_ext_rcd.ext_rcd_id
1660 -- and use this one for next the cursor
1661 -- This will prevent FTS on the table.
1662
1663 OPEN pqp_gb_t1_pension_extracts.csr_ext_rcd_id
1664 (p_hide_flag => 'N'
1665 ,p_rcd_type_cd => 'D'
1666 );
1667 FETCH pqp_gb_t1_pension_extracts.csr_ext_rcd_id INTO l_ext_rcd_id;
1668 CLOSE pqp_gb_t1_pension_extracts.csr_ext_rcd_id ;
1669
1670 debug('l_ext_rcd_id: '|| l_ext_rcd_id, 10) ;
1671
1672 Open csr_get_total_add_cont(p_ext_rcd_id => l_ext_rcd_id );
1673 Fetch csr_get_total_add_cont into l_total_add_cont_value;
1674 Close csr_get_total_add_cont;
1675
1676 debug(':l_total_add_cont_value:'||l_total_add_cont_value, 20 );
1677 debug(':g_total_add_cont:'||g_total_add_cont, 30 );
1678
1679 g_total_add_cont := l_total_add_cont_value;
1680
1681 l_total_add_cont := lpad(l_total_add_cont_value,10,' ');
1682
1683 debug(':l_total_add_cont:'||l_total_add_cont, 40);
1684
1685 --
1686 hr_utility.set_location('Leaving: '||l_proc_name, 50);
1687 --
1688 debug_exit(l_proc_name);
1689
1690 Return l_total_add_cont;
1691
1692 End get_total_add_cont;
1693 --
1694 -- ----------------------------------------------------------------------------
1695 -- |-----------------------< get_total_add_cont_sign >------------------------|
1696 -- ----------------------------------------------------------------------------
1697 Function get_total_add_cont_sign
1698 Return number is
1699 --
1700 Cursor csr_get_total_add_cont_sign is
1701 select decode(sign(g_total_add_cont),-1,1,0)
1702 from dual;
1703
1704 l_proc_name varchar2(60) := g_proc_name || 'get_total_add_cont_sign';
1705 l_total_refund_ind number ;
1706 --
1707 Begin
1708
1709 --
1710 hr_utility.set_location('Entering: '||l_proc_name, 5);
1711 --
1712 debug_enter(l_proc_name);
1713
1714 Open csr_get_total_add_cont_sign;
1715 Fetch csr_get_total_add_cont_sign into l_total_refund_ind;
1716 Close csr_get_total_add_cont_sign;
1717
1718 debug(':l_total_refund_ind:'||l_total_refund_ind, 1200 );
1719 --
1720 hr_utility.set_location('Leaving: '||l_proc_name, 15);
1721 --
1722 debug_exit(l_proc_name);
1723
1724 Return l_total_refund_ind;
1725
1726 End get_total_add_cont_sign;
1727 --
1728 -- ----------------------------------------------------------------------------
1729 -- |-----------------------------< chk_lea_run >------------------------------|
1730 -- ----------------------------------------------------------------------------
1731 Function chk_lea_run
1732 Return varchar2 is
1733 --
1734 l_proc_name varchar2(60) := g_proc_name || 'chk_lea_run';
1735 l_lea_run varchar2(1) := 'N';
1736 --
1737 Begin
1738
1739 --
1740 hr_utility.set_location('Entering: '||l_proc_name, 5);
1741 --
1742 debug_enter(l_proc_name);
1743
1744 If pqp_gb_tp_pension_extracts.g_estb_number = '0000' Then
1745 l_lea_run := 'Y';
1746 End If; -- End if of estb number check...
1747
1748 debug(':l_lea_run:'||l_lea_run, 1300 );
1749 --
1750 hr_utility.set_location('Leaving: '||l_proc_name, 15);
1751 --
1752 debug_exit(l_proc_name);
1753
1754 Return l_lea_run;
1755
1756 End chk_lea_run;
1757
1758
1759
1760 --
1761 End pqp_gb_tp_type2_functions;