[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.0 2005/05/29 02:02:15 appldev noship $ */
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
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
996 END IF;
997
998 ELSE
999
1000 debug('not a teacher hence warn', 515) ;
1001 l_teacher := false;
1002
1003 END IF; -- End if of teacher and pension check...
1004
1005 End Loop;
1006
1007 Else
1008
1009 --the assignment does not have an entry for teacher.
1010 debug(':inside Else of If l_tab_aat_info.count > 0 ', 520) ;
1011 l_teacher := false;
1012
1013
1014 End If; -- End if of assignment attribute check...
1015
1016 return l_teacher;
1017
1018 debug_exit(l_proc_name);
1019
1020 End get_aat_info;
1021 --
1022
1023
1024 -- ENH3 And ENH4
1025 -- ----------------------------------------------------------------------------
1026 -- |-----------------------< get_all_secondary_asgs >-----------------------|
1027 -- ----------------------------------------------------------------------------
1028 --
1029
1030 FUNCTION get_all_secondary_asgs
1031 (p_primary_assignment_id IN NUMBER
1032 ,p_effective_date IN DATE
1033 ,p_person_id IN NUMBER
1034 ) RETURN pqp_gb_t1_pension_extracts.t_sec_asgs_type
1035 IS
1036
1037 -- Rowtype Variable Declaration
1038 l_sec_asgs pqp_gb_t1_pension_extracts.csr_sec_assignments%ROWTYPE;
1039 l_all_sec_asgs pqp_gb_t1_pension_extracts.t_sec_asgs_type;
1040 idx NUMBER;
1041
1042 --
1043 l_proc_name VARCHAR2(61):=
1044 g_proc_name||'get_all_secondary_asgs';
1045
1046 BEGIN -- get_all_secondary_asgs
1047
1048 debug_enter(l_proc_name);
1049
1050 debug(' p_primary_assignment_id '||p_primary_assignment_id,10);
1051 debug(' p_person_id '|| p_person_id ,20);
1052 debug(' p_effective_date '||p_effective_date,30);
1053 debug(' g_effective_run_date '||g_effective_run_date,40);
1054 debug(' g_cross_per_enabled '||g_cross_per_enabled,50);
1055 debug(' g_business_group_id '||g_business_group_id,60);
1056
1057 --to be removed later
1058 -- g_cross_per_enabled := 'Y';
1059
1060 -- Fetch secondary assignments
1061 idx := 0;
1062
1063 FOR l_sec_asgs IN pqp_gb_t1_pension_extracts.csr_sec_assignments(p_primary_assignment_id
1064 ,p_person_id
1065 ,p_effective_date
1066 )
1067 LOOP
1068
1069 idx := idx + 1;
1070
1071 debug('adding secondary assignment to the collection '||l_sec_asgs.assignment_id,80 + idx/10000);
1072 -- Add this to the table of valid secondary asgs
1073 l_all_sec_asgs(l_sec_asgs.assignment_id) := l_sec_asgs;
1074
1075 --
1076 END LOOP; -- l_sec_asg_details IN csr_sec_asg_details
1077
1078 debug_exit(l_proc_name);
1079 --
1080 RETURN l_all_sec_asgs;
1081 --
1082 EXCEPTION
1083 WHEN OTHERS THEN
1084
1085 hr_utility.set_location('Entering excep:'||l_proc_name, 35);
1086 hr_utility.set_location('SQLCODE :'||SQLCODE, 40);
1087 hr_utility.set_location('SQLERRM :'||SQLERRM, 50);
1088 debug_exit(' Others in '||l_proc_name);
1089 RAISE;
1090 END; -- get_all_secondary_asgs
1091
1092 --
1093 -- ----------------------------------------------------------------------------
1094 -- |-----------------------< chk_teacher_qual_for_tp2 >-----------------------|
1095 -- ----------------------------------------------------------------------------
1096 --
1097
1098 Function chk_teacher_qual_for_tp2
1099 (p_business_group_id in number -- context
1100 ,p_effective_date in date -- context
1101 ,p_assignment_id in number -- context
1102 ,p_error_text out nocopy varchar2
1103 ,p_error_number out nocopy number
1104 -- ,p_trace in varchar2 default null
1105 ) return varchar2 -- Y or N
1106 is
1107 --
1108 l_inclusion_flag varchar2(20) := 'N';
1109 l_sec_assignments pqp_gb_t1_pension_extracts.t_sec_asgs_type;
1110 l_is_a_teacher varchar2(1) := 'Y';
1111 l_person_id per_all_people_f.person_id%TYPE;
1112 l_start_date DATE;
1113 l_proc_name varchar2(61) := g_proc_name || 'chk_teacher_qual_for_tp2';
1114 l_curr_sec_asg_id per_all_assignments_f.assignment_id%TYPE;
1115 l_prev_sec_asg_id per_all_assignments_f.assignment_id%TYPE;
1116 idx NUMBER;
1117 l_first_time BOOLEAN := TRUE; --Flag to add sec asg details to primary asg
1118 -- if primary is not valid asg.
1119 l_business_group_id NUMBER;
1120 l_asg_cat_cd VARCHAR2(10);
1121 l_location_id hr_location_extra_info.location_id%TYPE;
1122 l_ext_emp_cat_cd VARCHAR2(10);
1123
1124 --
1125 Begin
1126
1127
1128 debug_enter(l_proc_name);
1129 debug(l_proc_name,10) ;
1130 debug(':p_assignment_id:'||p_assignment_id ,20);
1131 debug(':p_effective_date:'||p_effective_date ,30 );
1132 debug(':p_business_group_id:'||p_business_group_id ,40);
1133
1134 OPEN csr_get_person_id(p_assignment_id);
1135 FETCH csr_get_person_id INTO l_person_id,l_business_group_id;
1136 CLOSE csr_get_person_id;
1137
1138 debug(':l_person_id:'||l_person_id ,50);
1139 debug(':l_business_group_id:'||l_business_group_id ,55);
1140
1141
1142 g_inclusion_flag := 'N';
1143 g_error_text := null;
1144 g_error_number := null;
1145
1146 -- Bug fix 2848696
1147 -- Effective date passed should be the actual effective date
1148 -- passed whilst submitting extract process
1149 -- and not the session date
1150
1151 If g_business_group_id is null Then
1152
1153 debug(':inside If g_business_group_id is null', 60 );
1154 --ENH3 And ENH4 .Added the new parameter lea number.
1155
1156 pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
1157
1158
1159
1160 pqp_gb_tp_pension_extracts.set_extract_globals (p_business_group_id
1161 -- ,p_effective_date
1162 ,ben_ext_person.g_effective_date
1163 ,p_assignment_id
1164 );
1165
1166 pqp_gb_tp_pension_extracts.g_nested_level := 0;
1167
1168
1169 g_business_group_id := pqp_gb_tp_pension_extracts.g_business_group_id;
1170 g_effective_date := pqp_gb_tp_pension_extracts.g_effective_date;
1171 g_header_system_element := pqp_gb_tp_pension_extracts.g_header_system_element;
1172
1173 debug(':g_business_group_id:'||g_business_group_id,80 );
1174 debug(':g_effective_date:'||g_effective_date,90 );
1175 debug(':g_header_system_element:'||g_header_system_element,110 );
1176
1177
1178 g_add_cont_ele_ids.delete;
1179
1180 set_effective_dates;
1181
1182 set_type2_globals;
1183
1184 --set the g_effective_run_date for type1 as its being used in csr_sec_assignments.
1185 pqp_gb_t1_pension_extracts.g_effective_run_date := g_effective_run_date;
1186
1187 If g_error_number is not null Then
1188
1189 debug(':inside If g_error_number is not null'||g_error_number, 120 );
1190 debug('g_error_text '||g_error_text,130);
1191 p_error_text := g_error_text;
1192 p_error_number := g_error_number;
1193 l_inclusion_flag := 'ERROR';
1194 debug('l_inclusion_flag '||l_inclusion_flag,140);
1195 Return l_inclusion_flag;
1196
1197 End If; -- End if of error check...
1198
1199 End If;-- end of g_business_group_id is null
1200
1201
1202 -- Bugfix -- Bugfix 3671727: Performance enhancement
1203 -- If no location exists in the list of valid criteria
1204 -- establishments, then no point doing all checks
1205 -- Just warn once and skip every assignment
1206 IF pqp_gb_tp_pension_extracts.g_criteria_estbs.COUNT = 0 THEN
1207
1208 debug('Setting inclusion flag to N as no locations EXIST.', 145);
1209 l_inclusion_flag := 'N';
1210
1211 pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
1212 -- Call TP4 pkg proc to warning for no locations
1213 pqp_gb_tp_pension_extracts.warn_if_no_loc_exist
1214 (p_assignment_id => p_assignment_id) ;
1215 pqp_gb_tp_pension_extracts.g_nested_level := 0;
1216
1217 RETURN l_inclusion_flag ; -- the assignment will eventually fail for validity as no location exists.
1218
1219 END IF ;
1220
1221 pqp_gb_t1_pension_extracts.g_part_time_asg_count := 0;
1222
1223 -- Check if this person should be reported by the current run
1224
1225 pqp_gb_t1_pension_extracts.g_nested_level := g_nested_level;
1226
1227 IF pqp_gb_t1_pension_extracts.chk_report_person
1228 (p_business_group_id
1229 ,p_effective_date
1230 ,p_assignment_id
1231 )
1232 THEN
1233
1234 debug('chk_report_person is true', 150 );
1235
1236 debug('l_person_id is '||l_person_id, 160 );
1237
1238 --ENH8:Added the code below here,so that it checks
1239 --if the assignment is a valid record for type 2.
1240 --before checking the assignment attributes.
1241
1242 --
1243 -- Check if the person is eligible for Type 2 Report
1244 --
1245
1246 --Y indicates that the record is a valid type 2 record.
1247 IF get_asg_info
1248 (
1249 p_assignment_id => p_assignment_id
1250 ,p_effective_start_date => g_effective_start_date
1251 ,p_effective_end_date => g_effective_end_date
1252 ,p_location_id => l_location_id -- OUT
1253 ,p_ext_emp_cat_cd => l_ext_emp_cat_cd -- OUT
1254 )
1255 THEN
1256
1257 debug('get_asg_info is true ', 170 );
1258
1259 g_inclusion_flag := 'Y';
1260
1261 pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id :=
1262 l_person_id;
1263
1264 pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date :=
1265 g_effective_start_date;
1266
1267 pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
1268 pqp_gb_tp_pension_extracts.g_criteria_estbs(l_location_id).estb_number;
1269
1270 debug('estb_number is '||
1271 pqp_gb_tp_pension_extracts.g_criteria_estbs(l_location_id).estb_number
1272 , 175 );
1273
1274 --
1275 -- Check the person is a teacher and has elected pension
1276 --
1277 debug('calling get_aat_info', 180) ;
1278 -- checks if the assignment is a teacher,
1279 -- else gives a warning.
1280 IF get_aat_info
1281 (
1282 p_assignment_id => p_assignment_id
1283 ,p_effective_start_date => g_effective_start_date
1284 ,p_effective_end_date => g_effective_end_date
1285 ,p_ext_emp_cat_cd => l_ext_emp_cat_cd
1286 ,p_location_id => l_location_id
1287 )
1288 THEN
1289
1290 debug('get_aat_info is true'|| l_curr_sec_asg_id, 190 );
1291 Null;
1292
1293 ELSE
1294
1295 debug('get_aat_info is false'|| l_curr_sec_asg_id, 210 );
1296 l_is_a_teacher := 'N';
1297
1298 END IF;-- end if get_aat_info
1299
1300
1301 ELSE
1302
1303 --if the primary assignment is not a valid type 2 record,set the report_asg
1304 --flag to 'N'
1305 pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).report_asg
1306 := 'N';
1307
1308 debug('report_asg is N '|| p_assignment_id , 230 );
1309
1310 End If; -- End if get_asg_info
1311
1312
1313 --End of ENH8.
1314
1315 --ENH4:Earlier the check for type 2 records was carried out
1316 --for primary assignment only.
1317 --its now to be done for all the assignments.
1318
1319 l_sec_assignments := get_all_secondary_asgs
1320 (
1321 p_primary_assignment_id => p_assignment_id
1322 ,p_effective_date => g_effective_start_date
1323 ,p_person_id => l_person_id
1324 );
1325
1326 idx := 0;
1327
1328 IF l_sec_assignments.COUNT > 0 THEN
1329
1330 debug('secondary assignments count > 0 ' , 240 );
1331
1332 l_curr_sec_asg_id := l_sec_assignments.FIRST;
1333
1334 WHILE l_curr_sec_asg_id IS NOT NULL
1335 LOOP
1336
1337 idx := idx + 1;
1338
1339 debug('seconday assignment found'|| l_curr_sec_asg_id, 250 + idx/10000);
1340
1341 IF get_asg_info
1342 (
1343 p_assignment_id => l_curr_sec_asg_id
1344 ,p_effective_start_date => g_effective_start_date
1345 ,p_effective_end_date => g_effective_end_date
1346 ,p_location_id => l_location_id -- OUT
1347 ,p_ext_emp_cat_cd => l_ext_emp_cat_cd -- OUT
1348 )
1349 THEN
1350
1351 debug('get_asg_info is true'|| l_curr_sec_asg_id, 260 + idx/10000);
1352
1353 g_inclusion_flag := 'Y';
1354
1355 pqp_gb_tp_pension_extracts.g_ext_asg_details(l_curr_sec_asg_id).person_id :=
1356 l_person_id;
1357
1358 pqp_gb_tp_pension_extracts.g_ext_asg_details(l_curr_sec_asg_id).start_date :=
1359 g_effective_start_date;
1360
1361 pqp_gb_tp_pension_extracts.g_ext_asg_details(l_curr_sec_asg_id).estb_number :=
1362 pqp_gb_tp_pension_extracts.g_criteria_estbs(l_location_id).estb_number;
1363
1364 debug('l_estb_number is '||
1365 pqp_gb_tp_pension_extracts.g_criteria_estbs(l_location_id).estb_number
1366 ,265 );
1367
1368 -- Check if hte Primary asg is not a valid asg
1369 -- then Add the secondary asg details at Primary asg also
1370 -- but only for the very first sec asg.
1371
1372 IF l_first_time --Should be TRUE for the first time..
1373 AND -- the primary asg is not a valid asg
1374 (pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).report_asg ='N') THEN
1375
1376 debug ('Primary is not valid, adding details of sec to primary in global collection',267) ;
1377
1378 pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_person_id;
1379 pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_start_date;
1380 pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
1381 pqp_gb_tp_pension_extracts.g_criteria_estbs(l_location_id).estb_number;
1382
1383 l_first_time := FALSE; -- reset the variable to prevent overwriting again and again.
1384
1385 END IF;
1386
1387
1388 IF get_aat_info
1389 (
1390 p_assignment_id => l_curr_sec_asg_id
1391 ,p_effective_start_date => g_effective_start_date
1392 ,p_effective_end_date => g_effective_end_date
1393 ,p_ext_emp_cat_cd => l_ext_emp_cat_cd
1394 ,p_location_id => l_location_id
1395 )
1396 THEN
1397
1398 debug('get_aat_info is true'|| l_curr_sec_asg_id, 270 + idx/10000);
1399 Null;
1400
1401 ELSE
1402
1403 debug('get_aat_info is false'|| l_curr_sec_asg_id, 280 + idx/10000);
1404 l_is_a_teacher := 'N';
1405
1406 END IF;-- get_aat_info
1407
1408 --calculate the contributions and add them to the primary assignment
1409 IF g_add_cont_value.exists(l_curr_sec_asg_id) Then
1410
1411 debug(':inside If g_add_cont_value.exists' , 290 + idx/10000);
1412
1413 IF g_add_cont_value.exists(p_assignment_id) Then
1414
1415 g_add_cont_value(p_assignment_id) :=
1416 g_add_cont_value(p_assignment_id) +
1417 g_add_cont_value(l_curr_sec_asg_id);
1418
1419 ELSE
1420
1421 g_add_cont_value(p_assignment_id) :=
1422 g_add_cont_value(l_curr_sec_asg_id);
1423
1424 END IF;
1425
1426 debug(':g_add_cont_value(p_assignment_id):'||g_add_cont_value(p_assignment_id), 320 + idx/10000);
1427
1428 END IF; -- End if of add cont value exist check...
1429
1430
1431 END IF;--get_asg_info
1432
1433 -- Assign the current asg id to prev asg id
1434 -- and reset curr asg id, ready for the next one
1435 l_prev_sec_asg_id := l_curr_sec_asg_id;
1436 l_curr_sec_asg_id := NULL;
1437
1438 l_curr_sec_asg_id := l_sec_assignments.NEXT(l_prev_sec_asg_id);
1439
1440 debug('l_prev_sec_asg_id is '|| l_prev_sec_asg_id , 330 + idx/10000);
1441 debug('l_curr_sec_asg_id is '|| l_curr_sec_asg_id , 340 + idx/10000);
1442
1443
1444 END LOOP;--end of secondary assignments check
1445
1446 END IF;--count of secondary assignments > 0
1447
1448 -- CLOSE csr_sec_assignments;
1449
1450 END IF;--chk_report_person
1451
1452 pqp_gb_t1_pension_extracts.g_nested_level := 0;
1453
1454 l_inclusion_flag := g_inclusion_flag;
1455 --
1456 debug(':l_inclusion_flag:'||l_inclusion_flag, 350 );
1457 hr_utility.set_location('Leaving: '||l_proc_name, 360);
1458 --
1459
1460 IF l_inclusion_flag = 'Y' THEN
1461
1462 -- The following piece of code raises a warning if
1463 -- there exist more than one lea with the same lea Number within a BG.
1464 -- the warning is raised for the first valid assignment for a single Run.
1465 -- the flag for warning is set during the global setting through set_extract_globals.
1466
1467 pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
1468 pqp_gb_tp_pension_extracts.warn_if_multi_lea_exist (p_assignment_id => p_assignment_id);
1469 pqp_gb_tp_pension_extracts.g_nested_level := 0;
1470
1471 IF l_is_a_teacher = 'N' THEN
1472
1473 --set the warning
1474 g_error_number := 93007;
1475 g_error_text := 'BEN_93007_EXT_TP2_NOT_TEACHER';
1476
1477 END IF;
1478
1479 END IF;
1480
1481 p_error_number := g_error_number;
1482 p_error_text := g_error_text;
1483
1484 g_nested_level := 1;
1485
1486 debug_exit(l_proc_name
1487 ,'Y'-- turn trace off
1488 );
1489 Return l_inclusion_flag;
1490
1491 -- Added by tmehra for nocopy changes Feb'03
1492
1493 EXCEPTION
1494 WHEN OTHERS THEN
1495 hr_utility.set_location('Entering excep:'||l_proc_name, 35);
1496 hr_utility.set_location('SQLCODE :'||SQLCODE, 40);
1497 hr_utility.set_location('SQLERRM :'||SQLERRM, 50);
1498 p_error_number := SQLCODE;
1499 p_error_text := SQLERRM;
1500 debug_exit(' Others in '||l_proc_name
1501 ,'Y' -- turn trace off
1502 );
1503 RAISE;
1504
1505 End chk_teacher_qual_for_tp2;
1506 --
1507 -- ----------------------------------------------------------------------------
1508 -- |-----------------------< get_add_cont_value >-----------------------------|
1509 -- ----------------------------------------------------------------------------
1510 Function get_add_cont_value
1511 (p_assignment_id in number)
1512 Return varchar2 is
1513 --
1514 l_proc_name varchar2(61) := g_proc_name || 'get_add_cont_value';
1515 l_add_cont_value number := 0;
1516 l_add_cont varchar2(6);
1517 --
1518 Begin
1519
1520
1521 debug_enter(l_proc_name);
1522 debug(':p_assignment_id:'||p_assignment_id, 10 );
1523
1524
1525
1526 If g_add_cont_value.exists(p_assignment_id) Then
1527
1528 debug(':inside If g_add_cont_value.exists(p_assignment_id)', 20 );
1529 l_add_cont_value := g_add_cont_value(p_assignment_id);
1530
1531 Else
1532
1533 l_add_cont_value := 0;
1534
1535 End If; -- End if of add cont value exists check...
1536
1537 l_add_cont := lpad(l_add_cont_value,6,' ');
1538 debug(':l_add_cont:'||l_add_cont, 30 );
1539
1540 debug_exit(l_proc_name);
1541
1542 Return l_add_cont;
1543
1544 End get_add_cont_value;
1545 --
1546 -- ----------------------------------------------------------------------------
1547 -- |-----------------------< get_add_cont_refund_ind >------------------------|
1548 -- ----------------------------------------------------------------------------
1549 Function get_add_cont_refund_ind
1550 (p_assignment_id in number)
1551 Return number is
1552 --
1553 Cursor csr_translate_sign is
1554 select decode(sign(get_add_cont_value(p_assignment_id)),-1,1,0)
1555 from dual;
1556
1557 l_proc_name varchar2(61) := g_proc_name || 'get_add_cont_refund_ind';
1558 l_add_cont_ind number := 0;
1559 --
1560 Begin
1561
1562 --
1563 hr_utility.set_location('Entering: '||l_proc_name, 5);
1564 --
1565 debug_enter(l_proc_name);
1566 debug(':p_assignment_id:'||p_assignment_id, 1150 );
1567
1568
1569 Open csr_translate_sign;
1570 Fetch csr_translate_sign into l_add_cont_ind;
1571 Close csr_translate_sign;
1572
1573 debug(':l_add_cont_ind:'||l_add_cont_ind, 1160 );
1574 --
1575 hr_utility.set_location('Leaving: '||l_proc_name, 15);
1576 --
1577 debug_exit(l_proc_name);
1578
1579 Return l_add_cont_ind;
1580
1581 End get_add_cont_refund_ind;
1582 --
1583 -- ----------------------------------------------------------------------------
1584 -- |-----------------------< get_financial_year >-----------------------------|
1585 -- ----------------------------------------------------------------------------
1586 Function get_financial_year
1587 Return varchar2 is
1588 --
1589 l_proc_name varchar2(60) := g_proc_name || 'get_financial_year';
1590 l_financial_year varchar2(2);
1591 --
1592 Begin
1593
1594 --
1595 hr_utility.set_location('Entering: '||l_proc_name, 5);
1596 --
1597 debug_enter(l_proc_name);
1598
1599 l_financial_year := TO_CHAR(g_effective_end_date, 'YY');
1600
1601 --
1602 hr_utility.set_location('Leaving: '||l_proc_name, 15);
1603 --
1604 debug_exit(l_proc_name);
1605
1606 Return l_financial_year;
1607
1608 End get_financial_year;
1609 --
1610 -- ----------------------------------------------------------------------------
1611 -- |-----------------------< get_total_add_cont >-----------------------------|
1612 -- ----------------------------------------------------------------------------
1613 Function get_total_add_cont
1614 Return varchar2 is
1615 --
1616 Cursor csr_get_total_add_cont
1617 (p_ext_rcd_id ben_ext_rcd.ext_rcd_id%TYPE)
1618 is
1619 select sum(dtl.val_10) total_value
1620 from ben_ext_rslt_dtl dtl
1621 --,ben_ext_rcd rcd
1622 where dtl.ext_rslt_id = ben_ext_thread.g_ext_rslt_id
1623 and dtl.ext_rcd_id = p_ext_rcd_id;
1624 -- and rcd.rcd_type_cd = 'D';
1625
1626 l_proc_name varchar2(60) := g_proc_name || 'get_total_add_cont';
1627 l_total_add_cont_value number := 0;
1628 l_total_add_cont varchar2(10);
1629 l_ext_rcd_id ben_ext_rcd.ext_rcd_id%TYPE;
1630 --
1631 Begin
1632
1633 --
1634 hr_utility.set_location('Entering: '||l_proc_name, 5);
1635 --
1636 debug_enter(l_proc_name);
1637
1638 -- 11.5.10_CU2: Performance fix :
1639 -- get the ben_ext_rcd.ext_rcd_id
1640 -- and use this one for next the cursor
1641 -- This will prevent FTS on the table.
1642
1643 OPEN pqp_gb_t1_pension_extracts.csr_ext_rcd_id
1644 (p_hide_flag => 'N'
1645 ,p_rcd_type_cd => 'D'
1646 );
1647 FETCH pqp_gb_t1_pension_extracts.csr_ext_rcd_id INTO l_ext_rcd_id;
1648 CLOSE pqp_gb_t1_pension_extracts.csr_ext_rcd_id ;
1649
1650 debug('l_ext_rcd_id: '|| l_ext_rcd_id, 10) ;
1651
1652 Open csr_get_total_add_cont(p_ext_rcd_id => l_ext_rcd_id );
1653 Fetch csr_get_total_add_cont into l_total_add_cont_value;
1654 Close csr_get_total_add_cont;
1655
1656 debug(':l_total_add_cont_value:'||l_total_add_cont_value, 20 );
1657 debug(':g_total_add_cont:'||g_total_add_cont, 30 );
1658
1659 g_total_add_cont := l_total_add_cont_value;
1660
1661 l_total_add_cont := lpad(l_total_add_cont_value,10,' ');
1662
1663 debug(':l_total_add_cont:'||l_total_add_cont, 40);
1664
1665 --
1666 hr_utility.set_location('Leaving: '||l_proc_name, 50);
1667 --
1668 debug_exit(l_proc_name);
1669
1670 Return l_total_add_cont;
1671
1672 End get_total_add_cont;
1673 --
1674 -- ----------------------------------------------------------------------------
1675 -- |-----------------------< get_total_add_cont_sign >------------------------|
1676 -- ----------------------------------------------------------------------------
1677 Function get_total_add_cont_sign
1678 Return number is
1679 --
1680 Cursor csr_get_total_add_cont_sign is
1681 select decode(sign(g_total_add_cont),-1,1,0)
1682 from dual;
1683
1684 l_proc_name varchar2(60) := g_proc_name || 'get_total_add_cont_sign';
1685 l_total_refund_ind number ;
1686 --
1687 Begin
1688
1689 --
1690 hr_utility.set_location('Entering: '||l_proc_name, 5);
1691 --
1692 debug_enter(l_proc_name);
1693
1694 Open csr_get_total_add_cont_sign;
1695 Fetch csr_get_total_add_cont_sign into l_total_refund_ind;
1696 Close csr_get_total_add_cont_sign;
1697
1698 debug(':l_total_refund_ind:'||l_total_refund_ind, 1200 );
1699 --
1700 hr_utility.set_location('Leaving: '||l_proc_name, 15);
1701 --
1702 debug_exit(l_proc_name);
1703
1704 Return l_total_refund_ind;
1705
1706 End get_total_add_cont_sign;
1707 --
1708 -- ----------------------------------------------------------------------------
1709 -- |-----------------------------< chk_lea_run >------------------------------|
1710 -- ----------------------------------------------------------------------------
1711 Function chk_lea_run
1712 Return varchar2 is
1713 --
1714 l_proc_name varchar2(60) := g_proc_name || 'chk_lea_run';
1715 l_lea_run varchar2(1) := 'N';
1716 --
1717 Begin
1718
1719 --
1720 hr_utility.set_location('Entering: '||l_proc_name, 5);
1721 --
1722 debug_enter(l_proc_name);
1723
1724 If pqp_gb_tp_pension_extracts.g_estb_number = '0000' Then
1725 l_lea_run := 'Y';
1726 End If; -- End if of estb number check...
1727
1728 debug(':l_lea_run:'||l_lea_run, 1300 );
1729 --
1730 hr_utility.set_location('Leaving: '||l_proc_name, 15);
1731 --
1732 debug_exit(l_proc_name);
1733
1734 Return l_lea_run;
1735
1736 End chk_lea_run;
1737
1738
1739
1740 --
1741 End pqp_gb_tp_type2_functions;