1 PACKAGE BODY pqp_gb_tp_pension_extracts AS
2 -- /* $Header: pqpgbtp4.pkb 120.1.12010000.3 2009/05/29 07:07:08 nchinnam ship $ */
3 --
4 --
5 --
6 PROCEDURE debug
7 (p_trace_message IN VARCHAR2
8 ,p_trace_location IN NUMBER
9 )
10 IS
11 l_padding VARCHAR2(12);
12 l_MAX_MESSAGE_LENGTH NUMBER:= 72;
13 BEGIN
14
15 IF p_trace_location IS NOT NULL THEN
16
17 l_padding := SUBSTR
18 (RPAD(' ',LEAST(g_nested_level,5)*2,' ')
19 ,1,l_MAX_MESSAGE_LENGTH
20 - LEAST(LENGTH(p_trace_message)
21 ,l_MAX_MESSAGE_LENGTH)
22 );
23
24 hr_utility.set_location
25 (l_padding||
26 SUBSTR(p_trace_message
27 ,GREATEST(-LENGTH(p_trace_message),-l_MAX_MESSAGE_LENGTH))
28 ,p_trace_location);
29
30 ELSE
31
32 hr_utility.trace(SUBSTR(p_trace_message,1,250));
33
34 END IF;
35
36 END debug;
37 --
38 --
39 --
40 PROCEDURE debug
41 (p_trace_number IN NUMBER )
42 IS
43 BEGIN
44 debug(fnd_number.number_to_canonical(p_trace_number));
45 END debug;
46 --
47 --
48 --
49 PROCEDURE debug
50 (p_trace_date IN DATE )
51 IS
52 BEGIN
53 debug(fnd_date.date_to_canonical(p_trace_date));
54 END debug;
55 --
56 --
57 --
58 PROCEDURE debug_enter
59 (p_proc_name IN VARCHAR2
60 ,p_trace_on IN VARCHAR2
61 )
62 IS
63 -- l_trace_options VARCHAR2(200);
64 l_extract_attributes pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes%ROWTYPE;
65 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
66
67 BEGIN
68
69 -- --Uncomment this code to run the extract with a debug trace
70 --
71 -- IF g_nested_level = 0 -- swtich tracing on/off at the top level only
72 -- AND NVL(p_trace_on,'N') = 'Y'
73 -- THEN
74 --
75 -- hr_utility.trace_on(NULL,'REQID'); -- Pipe name REQIDnnnnnn
76 --
77 -- END IF; -- if nested level = 0
78 --
79 -- --Uncomment this code to run the extract with a debug trace
80
81 -- Added for Tracing as Type 1 calls Type 4 functions
82 IF g_nested_level = 0 THEN -- swtich tracing on/off at the top level only
83
84 -- Set the trace flag, but only the first time around
85 IF g_trace IS NULL THEN
86
87 OPEN pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes;
88 FETCH pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes INTO l_extract_attributes;
89 CLOSE pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes;
90
91 l_business_group_id := fnd_global.per_business_group_id;
92
93 BEGIN
94 g_trace := hruserdt.get_table_value
95 (p_bus_group_id => l_business_group_id
96 ,p_table_name => l_extract_attributes.user_table_name
97 ,p_col_name => 'Attribute Location Qualifier 1'
98 ,p_row_value => 'Debug'
99 ,p_effective_date => NULL -- don't hv the date
100 );
101 EXCEPTION
102 WHEN OTHERS THEN
103 g_trace := 'N';
104 END;
105
106 g_trace := nvl(g_trace,'N');
107
108 debug('UDT Trace Flag : '||g_trace);
109
110 END IF; -- g_trace IS NULL THEN
111
112 IF NVL(p_trace_on,'N') = 'Y'
113 OR
114 g_trace = 'Y' THEN
115
116 hr_utility.trace_on(NULL,'REQID'); -- Pipe name REQIDnnnnnn
117 g_debug := hr_utility.debug_enabled;
118
119 END IF; -- NVL(p_trace_on,'N') = 'Y'
120 --
121 END IF; -- if nested level = 0
122
123 g_nested_level := g_nested_level + 1;
124 debug('Entered: '||NVL(p_proc_name,g_proc_name),g_nested_level*100);
125
126 END debug_enter;
127 --
128 --
129 --
130 PROCEDURE debug_exit
131 (p_proc_name IN VARCHAR2
132 ,p_trace_off IN VARCHAR2
133 )
134 IS
135 BEGIN
136
137 debug('Leaving: '||NVL(p_proc_name,g_proc_name),-g_nested_level*100);
138 g_nested_level := g_nested_level - 1;
139
140 -- debug enter sets trace ON when g_trace = 'Y' and nested level = 0
141 -- so we must turn it off for the same condition
142 -- Also turn off tracing when the override flag of p_trace_off has been passed as Y
143 IF (g_nested_level = 0
144 AND
145 g_trace = 'Y'
146 )
147 OR
148 NVL(p_trace_off,'N') = 'Y' THEN
149
150 hr_utility.trace_off;
151
152 END IF; -- (g_nested_level = 0
153
154
155 -- --Uncomment this code to run the extract with a debug trace
156 --
157 -- IF g_nested_level = 0
158 -- AND NVL(p_trace_off,'Y') = 'Y'
159 -- THEN
160 --
161 -- hr_utility.trace_off;
162 --
163 -- END IF;
164 --
165 -- --Uncomment this code to run the extract with a debug trace
166
167
168 END debug_exit;
169 --
170 --
171 --
172 FUNCTION get_extract_udt_info
173 (p_udt_column_name VARCHAR2
174 ,p_udt_row_name VARCHAR2
175 ,p_effective_date DATE
176 ) RETURN VARCHAR2 -- row value
177 IS
178 l_udt_value pay_user_column_instances_f.value%type;
179 l_proc_name VARCHAR2(61):= 'get_extract_udt_info';
180 BEGIN
181 debug_enter(l_proc_name);
182
183 l_udt_value := hruserdt.get_table_value
184 (p_bus_group_id => g_business_group_id
185 ,p_table_name => g_extract_udt_name
186 ,p_col_name => p_udt_column_name
187 ,p_row_value => p_udt_row_name
188 ,p_effective_date => NVL(p_effective_date,g_effective_date)
189 );
190 debug('l_udt_value:'||l_udt_value,1030) ;
191 debug_exit(l_proc_name);
192 RETURN l_udt_value;
193 EXCEPTION
194 WHEN NO_DATA_FOUND THEN
195 debug_exit(' No data found in '||l_proc_name);
196 l_udt_value := NULL;
197 RETURN l_udt_value;
198 END get_extract_udt_info;
199 --
200 --
201 --
202 -- FUNCTION get_establishment_details
203 -- (l_location_id IN NUMBER
204 -- ) RETURN pqp_gb_tp_pension_extracts.csr_estb_details%ROWTYPE
205 -- IS
206 --
207 -- l_estb_details csr_estb_details%ROWTYPE;
208 -- l_proc_name VARCHAR2(61):= 'get_establishment_details';
209 --
210 -- BEGIN
211 -- debug_enter(l_proc_name);
212 --
213 -- OPEN csr_estb_details(l_location_id);
214 -- FETCH csr_estb_details INTO l_estb_details;
215 -- CLOSE csr_estb_details;
216 --
217 -- debug_exit(l_proc_name);
218 -- RETURN l_estb_details;
219 --
220 -- END get_establishment_details;
221 ----
222 --
223 --
224 PROCEDURE fetch_criteria_establishments
225 (p_estb_details IN csr_estb_details%ROWTYPE)
226 IS
227
228 l_estb_details csr_estb_details%ROWTYPE;
229 l_lea_dets_by_loc csr_lea_details_by_loc%ROWTYPE ;
230
231 l_organization_id NUMBER ; --Added for non-lea Location
232
233 l_proc_name VARCHAR2(61):= 'fetch_criteria_establishments';
234
235 BEGIN
236 debug_enter(l_proc_name);
237 debug (l_proc_name, 1210);
238
239 debug ('p_estb_details.business_group_id:'||p_estb_details.business_group_id);
240 debug ('p_estb_details.location_id:'||p_estb_details.location_id);
241 debug ('p_estb_details.lea_estb_yn:'||p_estb_details.lea_estb_yn);
242 debug ('p_estb_details.estb_number:'||p_estb_details.estb_number);
243 debug ('p_estb_details.estb_name:'||p_estb_details.estb_name);
244 debug ('p_estb_details.estb_type:'||p_estb_details.estb_type);
245 debug ('p_estb_details.school_number:'||p_estb_details.school_number);
246 --debug ('p_estb_details.organization_id:'||p_estb_details.organization_id);
247
248 IF g_estb_number = '0000' THEN
249 debug ('inside IF g_estb_number = 0000', 1220);
250 OPEN csr_estb_details
251 (p_estb_number => p_estb_details.estb_number
252 ,p_lea_estb_yn => 'Y'
253 );
254 ELSE
255 debug ('inside ELSE of g_estb_number = 0000', 1230);
256 -- changes fro non-Lea Estb
257 /* OPEN csr_lea_details_by_loc(p_location_id => p_estb_details.location_id);
258 FETCH csr_lea_details_by_loc INTO l_lea_dets_by_loc;
259
260 IF (csr_lea_details_by_loc%FOUND
261 AND
262 l_lea_dets_by_loc.lea_number IS NOT NULL
263 ) THEN
264 debug ('l_lea_dets_by_loc.organization_id: '||l_lea_dets_by_loc.organization_id);
265 l_organization_id := l_lea_dets_by_loc.organization_id ;
266 ELSE
267 l_organization_id := NULL;
268 END IF ;
269
270 CLOSE csr_lea_details_by_loc ;*/
271
272
273 OPEN csr_estb_details
274 (p_estb_number => p_estb_details.estb_number
275 ,p_estb_type => p_estb_details.estb_type
276 ,p_lea_estb_yn => 'N'
277 );
278 END IF;
279
280 LOOP
281 FETCH csr_estb_details INTO l_estb_details;
282 EXIT WHEN csr_estb_details%NOTFOUND;
283 g_criteria_estbs(l_estb_details.location_id):= l_estb_details;
284 debug('Establishment Details...');
285 debug(g_criteria_estbs(l_estb_details.location_id).location_id);
286 debug(g_criteria_estbs(l_estb_details.location_id).lea_estb_yn);
287 debug(g_criteria_estbs(l_estb_details.location_id).estb_number);
288 debug(g_criteria_estbs(l_estb_details.location_id).estb_name);
289 debug(g_criteria_estbs(l_estb_details.location_id).estb_type);
290 -- Added new segment school number for salary scale changes
291 debug(g_criteria_estbs(l_estb_details.location_id).school_number);
292 debug('...Establishment Details');
293 END LOOP;
294 CLOSE csr_estb_details;
295
296 debug_exit(l_proc_name);
297 END fetch_criteria_establishments;
298 --
299 --
300 --
301 FUNCTION get_translate_asg_emp_cat_code
302 (p_asg_emp_cat_cd VARCHAR2
303 ,p_effective_date DATE
304 ) RETURN VARCHAR2
305 IS
306 --
307 l_proc_name VARCHAR2(61):= 'get_translate_asg_emp_cat_code';
308 l_udt_value VARCHAR2(1):= '?';
309 CURSOR csr_get_emp_cat_code (p_effective_date DATE) IS
310 SELECT extv.value
311 FROM pay_user_tables tbls
312 ,pay_user_columns asgc
313 ,pay_user_columns extc
314 ,pay_user_rows_f urws
315 ,pay_user_column_instances_f asgv
316 ,pay_user_column_instances_f extv
317 WHERE tbls.user_table_name =
318 'PQP_GB_TP_EMPLOYMENT_CATEGORY_TRANSALATION_TABLE'
319 AND asgc.user_table_id = tbls.user_table_id
320 AND extc.user_table_id = tbls.user_table_id
321 AND asgc.user_column_name = 'Assignment Employment Category Lookup Code'
322 AND extc.user_column_name = 'Pension Extracts Employment Category Code'
323 AND urws.user_table_id = tbls.user_table_id
324 AND (urws.business_group_id = g_business_group_id
325 OR
326 (urws.business_group_id IS NULL
327 AND urws.legislation_code = g_legislation_code)
328 OR
329 (urws.business_group_id IS NULL AND urws.legislation_code IS NULL)
330 )
331 AND p_effective_date BETWEEN urws.effective_start_date
332 AND urws.effective_end_date
333 AND asgv.user_column_id = asgc.user_column_id
334 AND p_effective_date BETWEEN asgv.effective_start_date
335 AND asgv.effective_end_date
336 AND extv.user_column_id = extc.user_column_id
337 AND p_effective_date BETWEEN extv.effective_start_date
338 AND extv.effective_end_date
339 AND asgv.user_row_id = urws.user_row_id
340 AND extv.user_row_id = asgv.user_row_id
341 AND asgv.value = p_asg_emp_cat_cd;
342 --
343 BEGIN
344
345 debug_enter(l_proc_name);
346 --
347
348 IF p_asg_emp_cat_cd = g_asg_emp_cat_cd AND g_ext_emp_cat_cd IS NOT NULL
349 THEN
350 l_udt_value := g_ext_emp_cat_cd;
351 ELSE
352 --
353 OPEN csr_get_emp_cat_code (NVL(p_effective_date,g_effective_date));
354 FETCH csr_get_emp_cat_code INTO l_udt_value;
355 g_asg_emp_cat_cd := p_asg_emp_cat_cd;
356 g_ext_emp_cat_cd := l_udt_value;
357 CLOSE csr_get_emp_cat_code;
358 --
359 END IF;
360 --
361 debug_exit(l_proc_name);
362 RETURN l_udt_value;
363 EXCEPTION
364 WHEN NO_DATA_FOUND THEN
365 debug('No Data Found in Translate UDT');
366 debug_exit;
367 l_udt_value := NULL;
368 RETURN l_udt_value;
369 END get_translate_asg_emp_cat_code;
370 --
371 --
372 --
373 PROCEDURE set_run_effective_dates
374 IS
375 l_proc_name VARCHAR2(61):= 'set_run_effective_dates';
376 l_year NUMBER; --RET2.a : New Variable
377
378 CURSOR csr_last_run_details IS
379 SELECT MAX(TRUNC(rslt.eff_dt)) -- highest effective date of all prev runs
380 FROM pqp_extract_attributes pqea
381 ,ben_ext_rslt rslt
382 ,ben_ext_rslt_dtl rdtl
383 ,ben_ext_rcd drcd
384 WHERE pqea.ext_dfn_type = g_extract_type
385 AND rslt.ext_dfn_id = pqea.ext_dfn_id
386 AND rslt.business_group_id = g_business_group_id
387 AND rslt.ext_stat_cd NOT IN
388 ('F' -- Job Failure
389 ,'R' -- Rejected By User
390 ,'X' -- Executing
391 )
392 AND rdtl.ext_rslt_id = rslt.ext_rslt_id
393 AND drcd.ext_rcd_id = rdtl.ext_rcd_id
394 AND drcd.rcd_type_cd = 'H'
395 AND SUBSTR(rdtl.val_01
396 ,1
397 ,INSTR(rdtl.val_01,':',1,3)--upto third occurence
398 )
399 =
400 SUBSTR(g_header_system_element
401 ,1
402 ,INSTR(g_header_system_element,':',1,3)
403 )
404 AND rslt.eff_dt < g_effective_date
405 -- The following part check the successful runs only for the LEA
406 -- for which the report is run currently.
407 -- the g_lea_number has already been set by the set_extract_globals.
408 AND SUBSTR(rdtl.val_01
409 ,INSTR(rdtl.val_01,':',1,1)+1 --lea Number
410 ,INSTR(rdtl.val_01,':',1,2)-INSTR(rdtl.val_01,':',1,1)-1) = g_lea_number ;
411
412
413
414 CURSOR csr_next_run_details IS
415 SELECT MIN(TRUNC(rslt.eff_dt)) -- least effective date of all future runs
416 FROM pqp_extract_attributes pqea
417 ,ben_ext_rslt rslt
418 ,ben_ext_rslt_dtl rdtl
419 ,ben_ext_rcd drcd
420 WHERE pqea.ext_dfn_type = g_extract_type
421 AND rslt.ext_dfn_id = pqea.ext_dfn_id
422 AND rslt.business_group_id = g_business_group_id
423 -- even failed future runs are to be considered
424 -- since pay process events might have completed
425 -- AND rslt.ext_stat_cd NOT IN
426 -- ('F' -- Job Failure
427 -- ,'R' -- Rejected By User
428 -- ,'X' -- Executing
429 -- )
430 AND rdtl.ext_rslt_id = rslt.ext_rslt_id
431 AND drcd.ext_rcd_id = rdtl.ext_rcd_id
432 AND drcd.rcd_type_cd = 'H'
433 AND SUBSTR(rdtl.val_01
434 ,1
435 ,INSTR(rdtl.val_01,':',1,3) --upto third occurence of
436 )
437 =
438 SUBSTR(g_header_system_element
439 ,1
440 ,INSTR(g_header_system_element,':',1,3)
441 )
442 AND rslt.eff_dt >= g_effective_date -- include any runs on the same day
443 -- The following part check the successful runs only for the LEA
444 -- for which the report is run currently.
445 -- the g_lea_number has already been set by the set_extract_globals.
446 AND SUBSTR(rdtl.val_01
447 ,INSTR(rdtl.val_01,':',1,1)+1 --lea Number
448 ,INSTR(rdtl.val_01,':',1,2)-INSTR(rdtl.val_01,':',1,1)-1) = g_lea_number ;
449
450 BEGIN
451 debug_enter(l_proc_name);
452
453 debug(TO_CHAR(g_effective_date,'DD-MON-YYYY'), 10);
454 debug('g_effective_date: '||
455 fnd_date.date_to_canonical(g_effective_date));
456
457 g_effective_run_date := -- "end of day" of a day before effective date
458 fnd_date.canonical_to_date
459 (TO_CHAR(g_effective_date - 1,'YYYY/MM/DD')||'23:59:59');
460
461 debug('g_effective_run_date: '||to_char(g_effective_run_date));
462
463 OPEN csr_last_run_details;
464 FETCH csr_last_run_details INTO g_last_effective_date;
465
466 debug('g_last_effective_date just after fetch: '||
467 fnd_date.date_to_canonical(g_last_effective_date), 30);
468
469 IF csr_last_run_details%NOTFOUND -- not likely ever bcos of use of MAX
470 OR
471 g_last_effective_date IS NULL
472 THEN
473
474 debug('No succeful last completed run was found',40);
475
476 g_last_effective_date :=
477 TO_DATE(get_extract_udt_info
478 ('Initial Extract Date' -- column
479 ,'Criteria' )
480 ,'DD-MM-YYYY');
481
482 IF g_last_effective_date IS NULL THEN -- use tax year first of april
483
484 debug('Initial Extract Date at UDT not defined', 50);
485
486 SELECT TO_DATE('01-04-'||
487 DECODE
488 (SIGN(TO_NUMBER(TO_CHAR(g_effective_date,'MM')) - 04)
489 ,-1,TO_CHAR(ADD_MONTHS(g_effective_date,-12),'YYYY')
490 ,TO_CHAR(g_effective_date,'YYYY'))
491 ,'DD-MM-YYYY')
492 INTO g_last_effective_date
493 FROM DUAL;
494
495 END IF;
496
497 END IF;
498 CLOSE csr_last_run_details;
499
500 debug('g_last_effective_date: '||
501 fnd_date.date_to_canonical(g_last_effective_date),60);
502
503 OPEN csr_next_run_details;
504 FETCH csr_next_run_details INTO g_next_effective_date;
505 CLOSE csr_next_run_details;
506
507 debug('g_next_effective_date: '||
508 fnd_date.date_to_canonical(g_next_effective_date), 70);
509
510 g_header_system_element:=
511 g_header_system_element||
512 fnd_date.date_to_canonical(g_last_effective_date)||':'||
513 fnd_date.date_to_canonical(g_effective_run_date) ||':'||
514 fnd_date.date_to_canonical(g_next_effective_date)||':';
515
516 debug('g_header_system_element: '||g_header_system_element, 80);
517
518 -- Setting all the required date globals of tp1 package
519 -- other tp1 globals are already set by set_extract_globals function
520 pqp_gb_t1_pension_extracts.g_effective_date := g_effective_date ;
521 pqp_gb_t1_pension_extracts.g_last_effective_date := g_last_effective_date ;
522 pqp_gb_t1_pension_extracts.g_next_effective_date := g_next_effective_date ;
523 pqp_gb_t1_pension_extracts.g_effective_run_date := g_effective_run_date ;
524 pqp_gb_t1_pension_extracts.g_header_system_element := g_header_system_element;
525
526 -- RET2.a : Changes related to Legislative updates to Retention Allowance
527 -- The Pension Year start date is required : as changes are effective from
528 -- 01-APR-2004.
529 -- Need to set the g_pension_year_start_date of Type1 Package
530 -- as we are not holding a pension_year_start_date in Type4 Package
531
532 debug(l_proc_name, 90);
533 IF to_number(to_char(g_effective_date, 'MM'))
534 BETWEEN 1 AND 3 THEN
535 debug(l_proc_name, 110);
536 -- Pension year should end YY - 1
537 l_year := to_number(to_char(g_effective_date, 'YYYY')) - 1;
538
539 ELSE
540 debug(l_proc_name, 120);
541 -- Pension year should end YY
542 l_year := to_number(to_char(g_effective_date, 'YYYY'));
543
544 END IF; -- End if of month check...
545
546 debug('l_year: '||to_char(l_year), 130);
547
548 pqp_gb_t1_pension_extracts.g_pension_year_start_date
549 := to_date('01/04/'||to_char(l_year), 'DD/MM/YYYY');
550
551 debug('g_pension_year_start_date: '||
552 fnd_date.date_to_canonical(pqp_gb_t1_pension_extracts.g_pension_year_start_date),140);
553
554 debug_exit(l_proc_name);
555 END set_run_effective_dates;
556 --
557 -- The following three functions were added for salary scale changes
558 -- This function returns the udt id for a given udt name
559 --
560 -- ----------------------------------------------------------------------------
561 -- |---------------------------< get_udt_id >---------------------------------|
562 -- ----------------------------------------------------------------------------
563 FUNCTION get_udt_id (p_udt_name IN VARCHAR2)
564 RETURN NUMBER
565 IS
566
567 --
568 -- Cursor to get user_table_id
569 CURSOR csr_get_udt_id
570 IS
571 SELECT user_table_id
572 FROM pay_user_tables
573 WHERE user_table_name = p_udt_name
574 AND ( ( business_group_id IS NULL
575 AND legislation_code = g_legislation_code
576 )
577 OR ( business_group_id IS NOT NULL
578 AND business_group_id = g_business_group_id
579 )
580 );
581
582 l_proc_name VARCHAR2 (60) := g_proc_name
583 || 'get_udt_id';
584 l_udt_id NUMBER;
585 l_proc_step NUMBER;
586 --
587 BEGIN
588 --
589 IF g_debug
590 THEN
591 DEBUG ( 'Entering: '
592 || l_proc_name, l_proc_step);
593 END IF;
594
595 OPEN csr_get_udt_id;
596 FETCH csr_get_udt_id INTO l_udt_id;
597 CLOSE csr_get_udt_id;
598
599 IF g_debug
600 THEN
601 DEBUG ( 'UDT Name: '
602 || p_udt_name);
603 DEBUG ( 'UDT ID: '
604 || TO_CHAR(l_udt_id));
605 l_proc_step := 20;
606 DEBUG ( 'Leaving: '
607 || l_proc_name, l_proc_step);
608 END IF;
609
610 RETURN l_udt_id;
611 END get_udt_id;
612
613 --
614 --
615 -- ----------------------------------------------------------------------------
616 -- |---------------------------< get_allow_ele_info >-------------------------|
617 -- ----------------------------------------------------------------------------
618 FUNCTION get_allow_ele_info (p_assignment_id IN NUMBER
619 ,p_effective_date IN DATE
620 ,p_table_name IN VARCHAR2
621 ,p_row_name IN VARCHAR2
622 ,p_column_name IN VARCHAR2
623 )
624 RETURN NUMBER
625 IS
626 --
627 l_proc_name VARCHAR2 (80) := g_proc_name
628 || 'get_allow_ele_info';
629 l_proc_step NUMBER;
630 l_return NUMBER;
631 l_user_value pay_user_column_instances_f.value%TYPE;
632 l_error_msg VARCHAR2(2000);
633 l_element_type_id NUMBER := NULL;
634
635 --
636 BEGIN
637 --
638 debug_enter(l_proc_name);
639
640 l_return := pqp_utilities.pqp_gb_get_table_value
641 (p_business_group_id => g_business_group_id
642 ,p_effective_date => p_effective_date
643 ,p_table_name => p_table_name
644 ,p_column_name => p_column_name
645 ,p_row_name => p_row_name
646 ,p_value => l_user_value
647 ,p_error_msg => l_error_msg
648 );
649
650 --
651 IF l_return <> -1 THEN
652
653 --
654 IF l_user_value IS NOT NULL THEN
655
656 -- fetch the element type id information
657 debug ('User Value: ' || l_user_value, 10);
658
659 l_element_type_id := pqp_utilities.pqp_get_element_type_id
660 (p_business_group_id => g_business_group_id
661 ,p_legislation_code => g_legislation_code
662 ,p_effective_date => p_effective_date
663 ,p_element_type_name => l_user_value
664 ,p_error_code => l_return
665 ,p_message => l_error_msg
666 );
667
668 --
669 IF l_return <> -1 THEN -- no error
670
671 debug ('Element Type ID: '
672 || TO_CHAR(l_element_type_id),20);
673 --
674 ELSE -- Else of return <> -1 , error
675 --
676 --
677 debug_exit(l_proc_name);
678 --
679 -- Raise an error for element does not exist
680 l_return := pqp_gb_tp_extract_functions.raise_extract_error
681 (p_business_group_id => g_business_group_id
682 ,p_assignment_id => p_assignment_id
683 ,p_error_text =>'BEN_93026_EXT_TP1_ELE_NOTEXIST'
684 ,p_error_number => 93026
685 ,p_token1 => l_user_value);
686
687 END IF; -- End if of element type exists return check ...
688 --
689 END IF; -- End if of user value is not null check ...
690 --
691 ELSE -- Else return = -1 from get table value function
692
693 debug_exit(l_proc_name);
694
695 fnd_message.set_name ('PQP', 'PQP_230661_OSP_DUMMY_MSG');
696 fnd_message.set_token ('TOKEN', l_error_msg);
697 fnd_message.raise_error;
698
699 END IF; -- End if of return <> -1 check from get table value func...
700
701 debug_exit(l_proc_name);
702
703 RETURN l_element_type_id;
704 --
705 END get_allow_ele_info;
706 --
707 --
708 -- ----------------------------------------------------------------------------
709 -- |---------------------------< get_allow_rt_ele_info >----------------------|
710 -- ----------------------------------------------------------------------------
711 FUNCTION get_allow_rt_ele_info (p_assignment_id IN NUMBER
712 ,p_effective_date IN DATE
713 ,p_table_name IN VARCHAR2
714 ,p_row_name IN VARCHAR2
715 ,p_column_name IN VARCHAR2
716 ,p_tab_aln_eles IN t_allowance_eles
717 )
718 RETURN t_allowance_eles
719 IS
720 --
721 l_proc_name VARCHAR2 (80)
722 := g_proc_name
723 || 'get_allow_rt_ele_info';
724 l_proc_step NUMBER;
725 l_return NUMBER;
726 l_user_value pay_user_column_instances_f.value%TYPE;
727 l_error_msg VARCHAR2(2000);
728 l_element_type_id NUMBER := NULL;
729 l_tab_allowance_eles t_allowance_eles := p_tab_aln_eles;
730
731 -- RET1.a : new variables to store element_type_extra_info_id
732 l_element_type_extra_info_id pay_element_type_extra_info.element_type_extra_info_id%type ;
733 l_retval NUMBER;
734 l_token VARCHAR2(80);
735 --
736 BEGIN
737 --
738 debug_enter (l_proc_name);
739
740 l_return := pqp_utilities.pqp_gb_get_table_value
741 (p_business_group_id => g_business_group_id
742 ,p_effective_date => p_effective_date
743 ,p_table_name => p_table_name
744 ,p_column_name => p_column_name
745 ,p_row_name => p_row_name
746 ,p_value => l_user_value
747 ,p_error_msg => l_error_msg
748 );
749 --
750 IF l_return <> -1
751 THEN
752 --
753 IF l_user_value IS NOT NULL THEN
754
755 -- fetch the element type id information
756 -- for this rate type the rate type validation
757 -- is already added in the UDT so no need to
758 -- check for validation again
759
760 debug ('User Value: '
761 || l_user_value, 10);
762
763 --
764 OPEN csr_get_eles_frm_rate (p_effective_date
765 ,l_user_value
766 );
767 LOOP
768 FETCH csr_get_eles_frm_rate INTO l_element_type_id;
769 EXIT WHEN csr_get_eles_frm_rate%NOTFOUND;
770
771 l_tab_allowance_eles (l_element_type_id).element_type_id
772 := l_element_type_id;
773 l_tab_allowance_eles (l_element_type_id).salary_scale_code
774 := p_row_name;
775
776 -- check which type of allowance, do only for retention allowance
777 IF p_column_name = 'Retention Allowance Rate Type'
778 THEN
779 l_token := 'Retention Allowance';
780
781 -- RET1.a : start of block
782 -- get element_type_extra_info_id
783 l_retval := pqp_utilities.pqp_get_ele_type_extra_info_id
784 (p_element_type_id => l_element_type_id
785 ,p_information_type => 'PQP_UK_ELEMENT_ATTRIBUTION'
786 ,p_element_type_extra_info_id => l_element_type_extra_info_id
787 ,p_error_msg => l_error_msg
788 );
789
790 --
791 IF l_retval <> -1 -- no error
792 THEN
793
794 debug('l_element_type_extra_info_id: '||l_element_type_extra_info_id,20);
795
796 -- store element_type_extra_info_id in the collection
797 l_tab_allowance_eles (l_element_type_id).element_type_extra_info_id
798 := l_element_type_extra_info_id ;
799
800 ELSE -- error case
801
802 debug('l_element_type_extra_info_id not found',30);
803
804 debug_exit(l_proc_name);
805
806 /*
807 -- check which type of allowance, and set error token
808 IF p_column_name = 'Management Allowance Rate Type' THEN
809 l_token := 'Management Allowance';
810 ELSIF p_column_name = 'Retention Allowance Rate Type' THEN
811 l_token := 'Retention Allowance';
812 ELSIF p_column_name = 'TLR Allowance Rate Type' THEN
813 l_token := 'TLR Allowance';
814 END IF;
815 */
816
817 --
818 -- Raise an error for failure to get element_type_extra_info_id
819 l_return := pqp_gb_tp_extract_functions.raise_extract_error
820 (p_business_group_id => g_business_group_id
821 ,p_assignment_id => p_assignment_id
822 -- RET1.a : Added error BEN_94155_EXT_TP1_ERR_RET_ALL
823 ,p_error_text =>'BEN_94155_EXT_TP1_ERR_RET_ALL'
824 ,p_error_number => 94155
825 ,p_token1 => l_token
826 );
827
828 END IF; -- IF l_retval <> -1 -- no error
829 END IF; -- IF p_column_name = 'Retention Allowance Rate Type'
830 -- RET1.a : end of block
831
832
833 debug ('Element Type ID: '
834 || TO_CHAR(l_element_type_id),40);
835 END LOOP; -- End loop of eles from rate cursor...
836 CLOSE csr_get_eles_frm_rate;
837 --
838
839 --
840 IF l_tab_allowance_eles.COUNT = 0 THEN
841
842 debug_exit(l_proc_name);
843
844 -- Raise an error for no element are associated
845 -- with this rate type
846
847 l_return := pqp_gb_tp_extract_functions.raise_extract_error
848 (p_business_group_id => g_business_group_id
849 ,p_assignment_id => p_assignment_id
850 ,p_error_text =>'BEN_93640_EXT_TP_NO_ELE_FOR_RT'
851 ,p_error_number => 93640 );
852
853 END IF; -- End if of element type count = 0 check ...
854 --
855 END IF; -- End if of user value is not null check ...
856 --
857
858 ELSE -- Else return = -1 from get table value function
859
860 debug_exit(l_proc_name);
861
862 fnd_message.set_name ('PQP', 'PQP_230661_OSP_DUMMY_MSG');
863 fnd_message.set_token ('TOKEN', l_error_msg);
864 fnd_message.raise_error;
865
866 END IF; -- End if of return <> -1 check from get table value func...
867 --
868
869 debug_exit(l_proc_name);
870
871 RETURN l_tab_allowance_eles;
872 --
873 END get_allow_rt_ele_info;
874 --
875
876 -- ----------------------------------------------------------------------------
877 -- |----------------------------< fetch_allow_eles_frm_udt >------------------|
878 -- ----------------------------------------------------------------------------
879 PROCEDURE fetch_allow_eles_frm_udt
880 (p_assignment_id IN NUMBER
881 ,p_effective_date IN DATE
882 )
883 IS
884 --
885
886 CURSOR csr_get_user_rows (c_udt_id NUMBER)
887 IS
888 SELECT row_low_range_or_name
889 FROM pay_user_rows_f
890 WHERE user_table_id = c_udt_id
891 AND p_effective_date BETWEEN effective_start_date
892 AND effective_end_date
893 ORDER BY display_sequence;
894
895
896 l_proc_name VARCHAR2 (80) := g_proc_name
897 || 'fetch_allow_eles_frm_udt';
898 l_proc_step NUMBER;
899 l_element_type_id NUMBER;
900 l_tab_mng_aln_eles t_allowance_eles;
901 l_tab_ret_aln_eles t_allowance_eles;
902
903 -- 115.49 : TLR
904 l_tab_tlr_aln_eles t_allowance_eles;
905
906 l_user_row_name pay_user_rows_f.row_low_range_or_name%TYPE;
907 l_udt_name pay_user_tables.user_table_name%TYPE :=
908 'PQP_GB_TP_ALLOWANCE_ELEMENTS_MAPPING_TABLE';
909 l_return NUMBER;
910 l_udt_id NUMBER;
911 l_user_value pay_user_column_instances_f.value%TYPE;
912 l_error_msg VARCHAR2(2000);
913
914 -- RET1.a : new variables to store element_type_extra_info_id
915 l_element_type_extra_info_id pay_element_type_extra_info.element_type_extra_info_id%type;
916 l_retval NUMBER;
917 --
918 --
919 BEGIN
920 --
921 debug_enter(l_proc_name);
922 debug('p_effective_date: '|| p_effective_date);
923
924
925
926 -- Get UDT ID
927 l_udt_id := get_udt_id
928 (p_udt_name => l_udt_name);
929
930 -- Get the user rows information for this UDT
931 --
932 IF l_udt_id IS NOT NULL THEN
933
934 debug(l_proc_name, 10);
935
936 --
937 OPEN csr_get_user_rows (l_udt_id);
938 LOOP
939 FETCH csr_get_user_rows INTO l_user_row_name;
940 EXIT WHEN csr_get_user_rows%NOTFOUND;
941
942 -- Get the user value for this row if one exist
943 -- for each type of allowance and store it in their
944 -- respective collections
945 debug('User Row Name: '
946 || l_user_row_name,20);
947 debug('User Column Name: Management Allowance Element Type');
948
949 l_element_type_id := get_allow_ele_info
950 (p_assignment_id => p_assignment_id
951 ,p_effective_date => p_effective_date
952 ,p_table_name => l_udt_name
953 ,p_row_name => l_user_row_name
954 ,p_column_name => 'Management Allowance Element Type'
955 );
956 debug('l_element_type_id : '|| to_char(l_element_type_id));
957
958 IF l_element_type_id IS NOT NULL
959 THEN
960
961 -- Check whether users have specified any rate type information
962 -- for this salary scale
963 debug(l_proc_name, 30);
964 debug('p_effective_date: '|| p_effective_date,31);
965 debug('udT_name :' || l_udt_name,32);
966
967 l_return := pqp_utilities.pqp_gb_get_table_value
968 (p_business_group_id => g_business_group_id
969 ,p_effective_date => p_effective_date
970 ,p_table_name => l_udt_name
971 ,p_column_name => 'Management Allowance Rate Type'
972 ,p_row_name => l_user_row_name
973 ,p_value => l_user_value
974 ,p_error_msg => l_error_msg
975 );
976
977 debug('l_user_row_name : '|| l_user_row_name,33);
978 debug('l_user_value : '|| l_user_value,34);
979 debug('l_return : '|| to_char(l_return),35);
980
981 --
982 IF l_return <> -1 THEN
983 --
984 IF l_user_value IS NOT NULL THEN
985 -- Raise an error
986
987 debug_exit(l_proc_name);
988
989 -- Raise an error as one cannot enter a value
990 -- for both rate type and element type for the
991 -- same salary scale
992
993 l_return := pqp_gb_tp_extract_functions.raise_extract_error
994 (p_business_group_id => g_business_group_id
995 ,p_assignment_id => p_assignment_id
996 ,p_error_text =>'BEN_93639_EXT_TP_ELE_RT_EXISTS'
997 ,p_error_number => 93639
998 ,p_token1 => 'Management Allowance' );
999
1000 END IF; -- End if of user value check ...
1001 --
1002 ELSE -- Else return = -1 from get table value function
1003 debug('Leaving: '
1004 || l_proc_name, 40);
1005
1006 fnd_message.set_name ('PQP', 'PQP_230661_OSP_DUMMY_MSG');
1007 fnd_message.set_token ('TOKEN', l_error_msg);
1008 fnd_message.raise_error;
1009
1010 END IF; -- End if of return <> -1 check from get table value func...
1011 --
1012 -- Store it in the management allowance collection
1013 l_tab_mng_aln_eles (l_element_type_id).salary_scale_code
1014 := l_user_row_name;
1015 l_tab_mng_aln_eles (l_element_type_id).element_type_id
1016 := l_element_type_id;
1017
1018 ELSE -- element type id is null
1019 -- Check for rate type
1020 debug(l_proc_name, 50);
1021
1022 l_tab_mng_aln_eles := get_allow_rt_ele_info
1023 (p_assignment_id => p_assignment_id
1024 ,p_effective_date => p_effective_date
1025 ,p_table_name => l_udt_name
1026 ,p_row_name => l_user_row_name
1027 ,p_column_name => 'Management Allowance Rate Type'
1028 ,p_tab_aln_eles => l_tab_mng_aln_eles
1029 );
1030
1031 END IF; -- End if of element type id not null check ...
1032 -- end of code for "Management Allowance Element Type" --
1033
1034 -- start of code for "Retention Allowance Element Type" --
1035
1036 debug('User Row Name: '
1037 || l_user_row_name,60);
1038 debug('User Column Name: Retention Allowance Element Type');
1039
1040
1041 l_element_type_id := get_allow_ele_info
1042 (p_assignment_id => p_assignment_id
1043 ,p_effective_date => p_effective_date
1044 ,p_table_name => l_udt_name
1045 ,p_row_name => l_user_row_name
1046 ,p_column_name => 'Retention Allowance Element Type'
1047 );
1048
1049 IF l_element_type_id IS NOT NULL THEN
1050
1051 -- Check whether users have specified any rate type information
1052 -- for this salary scale
1053 debug(l_proc_name, 70);
1054
1055 l_return := pqp_utilities.pqp_gb_get_table_value
1056 (p_business_group_id => g_business_group_id
1057 ,p_effective_date => p_effective_date
1058 ,p_table_name => l_udt_name
1059 ,p_column_name => 'Retention Allowance Rate Type'
1060 ,p_row_name => l_user_row_name
1061 ,p_value => l_user_value
1062 ,p_error_msg => l_error_msg
1063 );
1064
1065 debug('l_user_row_name : '|| l_user_row_name);
1066 debug('l_user_value : '|| l_user_value);
1067 debug('l_return : '|| to_char(l_return));
1068
1069 IF l_return <> -1 THEN
1070
1071 IF l_user_value IS NOT NULL THEN
1072 -- Raise an error
1073 debug_exit(l_proc_name);
1074
1075 -- Raise an error as one cannot enter a value
1076 -- for both rate type and element type for the
1077 -- same salary scale
1078
1079 l_return := pqp_gb_tp_extract_functions.raise_extract_error
1080 (p_business_group_id => g_business_group_id
1081 ,p_assignment_id => p_assignment_id
1082 ,p_error_text =>'BEN_93639_EXT_TP_ELE_RT_EXISTS'
1083 ,p_error_number => 93639
1084 ,p_token1 => 'Retention Allowance');
1085
1086 END IF; -- End if of user value check ...
1087 ELSE -- Else return = -1 from get table value function
1088
1089 debug('Leaving: ' || l_proc_name,80);
1090
1091 fnd_message.set_name ('PQP', 'PQP_230661_OSP_DUMMY_MSG');
1092 fnd_message.set_token ('TOKEN', l_error_msg);
1093 fnd_message.raise_error;
1094
1095 END IF; -- End if of return <> -1 check from get table value func...
1096
1097 -- RET1.a : start of block
1098 -- get element_type_extra_info_id
1099 l_retval := pqp_utilities.pqp_get_ele_type_extra_info_id
1100 (p_element_type_id => l_element_type_id
1101 ,p_information_type => 'PQP_UK_ELEMENT_ATTRIBUTION'
1102 ,p_element_type_extra_info_id => l_element_type_extra_info_id
1103 ,p_error_msg => l_error_msg
1104 );
1105
1106 --
1107 IF l_retval <> -1 -- no error
1108 THEN
1109 debug('l_element_type_extra_info_id: '||l_element_type_extra_info_id);
1110
1111 ELSE -- error case
1112 debug('l_element_type_extra_info_id not found');
1113 debug_exit(l_proc_name);
1114 --
1115 -- Raise an error for failure to get element_type_extra_info_id
1116 l_return := pqp_gb_tp_extract_functions.raise_extract_error
1117 (p_business_group_id => g_business_group_id
1118 ,p_assignment_id => p_assignment_id
1119 -- RET1.a : Added error BEN_94155_EXT_TP1_ERR_RET_ALL
1120 ,p_error_text =>'BEN_94155_EXT_TP1_ERR_RET_ALL'
1121 ,p_error_number => 94155
1122 ,p_token1 => 'Retention Allowance'
1123 );
1124 END IF;
1125 -- RET1.a : end of block
1126
1127
1128 -- Store it in the retention allowance collection
1129 l_tab_ret_aln_eles (l_element_type_id).salary_scale_code
1130 := TO_NUMBER(l_user_row_name);
1131 l_tab_ret_aln_eles (l_element_type_id).element_type_id
1132 := l_element_type_id;
1133
1134 -- RET1.a : storing the element_type_extra_info_id
1135 l_tab_ret_aln_eles (l_element_type_id).element_type_extra_info_id
1136 := l_element_type_extra_info_id;
1137
1138 ELSE -- element type id is null
1139 -- Check for rate type
1140 debug(l_proc_name, 90);
1141
1142 l_tab_ret_aln_eles := get_allow_rt_ele_info
1143 (p_assignment_id => p_assignment_id
1144 ,p_effective_date => p_effective_date
1145 ,p_table_name => l_udt_name
1146 ,p_row_name => l_user_row_name
1147 ,p_column_name => 'Retention Allowance Rate Type'
1148 ,p_tab_aln_eles => l_tab_ret_aln_eles
1149 );
1150
1151 END IF; -- End if of element type id not null check ...
1152 --
1153
1154 -- 115.49 TLR (1)
1155 -- start of code for fetching TLR elements
1156
1157 debug('User Row Name: '
1158 || l_user_row_name,90);
1159 debug('User Column Name: TLR Allowance Element Type');
1160
1161 l_element_type_id := get_allow_ele_info
1162 (p_assignment_id => p_assignment_id
1163 ,p_effective_date => p_effective_date
1164 ,p_table_name => l_udt_name
1165 ,p_row_name => l_user_row_name
1166 ,p_column_name => 'TLR Allowance Element Type'
1167 );
1168 debug('l_element_type_id : '|| to_char(l_element_type_id));
1169
1170 IF l_element_type_id IS NOT NULL
1171 THEN
1172
1173 -- Check whether users have specified any rate type information
1174 -- for this salary scale
1175 debug(l_proc_name, 30);
1176 debug('p_effective_date: '|| p_effective_date,110);
1177 debug('udT_name :' || l_udt_name,120);
1178
1179 l_return := pqp_utilities.pqp_gb_get_table_value
1180 (p_business_group_id => g_business_group_id
1181 ,p_effective_date => p_effective_date
1182 ,p_table_name => l_udt_name
1183 ,p_column_name => 'TLR Allowance Rate Type'
1184 ,p_row_name => l_user_row_name
1185 ,p_value => l_user_value
1186 ,p_error_msg => l_error_msg
1187 );
1188
1189 debug('l_user_row_name : '|| l_user_row_name,130);
1190 debug('l_user_value : '|| l_user_value,130);
1191 debug('l_return : '|| to_char(l_return),130);
1192
1193 --
1194 IF l_return <> -1 THEN
1195 --
1196 IF l_user_value IS NOT NULL THEN
1197 -- Raise an error
1198
1199 debug_exit(l_proc_name);
1200
1201 -- Raise an error as one cannot enter a value
1202 -- for both rate type and element type for the
1203 -- same salary scale
1204
1205 l_return := pqp_gb_tp_extract_functions.raise_extract_error
1206 (p_business_group_id => g_business_group_id
1207 ,p_assignment_id => p_assignment_id
1208 ,p_error_text =>'BEN_93639_EXT_TP_ELE_RT_EXISTS'
1209 ,p_error_number => 93639
1210 ,p_token1 => 'TLR Allowance' );
1211
1212 END IF; -- End if of user value check ...
1213 --
1214 ELSE -- Else return = -1 from get table value function
1215 debug('Leaving: '
1216 || l_proc_name, 140);
1217
1218 fnd_message.set_name ('PQP', 'PQP_230661_OSP_DUMMY_MSG');
1219 fnd_message.set_token ('TOKEN', l_error_msg);
1220 fnd_message.raise_error;
1221
1222 END IF; -- End if of return <> -1 check from get table value func...
1223 --
1224 -- Store it in the TLR allowance collection
1225 l_tab_tlr_aln_eles (l_element_type_id).salary_scale_code
1226 := l_user_row_name;
1227 l_tab_tlr_aln_eles (l_element_type_id).element_type_id
1228 := l_element_type_id;
1229
1230 ELSE -- element type id is null
1231 -- Check for rate type
1232 debug(l_proc_name, 150);
1233
1234 l_tab_tlr_aln_eles := get_allow_rt_ele_info
1235 (p_assignment_id => p_assignment_id
1236 ,p_effective_date => p_effective_date
1237 ,p_table_name => l_udt_name
1238 ,p_row_name => l_user_row_name
1239 ,p_column_name => 'TLR Allowance Rate Type'
1240 ,p_tab_aln_eles => l_tab_tlr_aln_eles
1241 );
1242
1243 END IF; -- End if of element type id not null check ...
1244 -- end of code for "Management Allowance Element Type" --
1245
1246
1247
1248 END LOOP;
1249 CLOSE csr_get_user_rows;
1250 END IF; -- End if of udt id is not null check ...
1251
1252 debug('Managment collection count: '||TO_CHAR(l_tab_mng_aln_eles.COUNT));
1253 debug('Retention collection count: '||TO_CHAR(l_tab_ret_aln_eles.COUNT));
1254
1255 g_tab_mng_aln_eles := l_tab_mng_aln_eles;
1256 g_tab_ret_aln_eles := l_tab_ret_aln_eles;
1257 g_tab_tlr_aln_eles := l_tab_tlr_aln_eles;
1258
1259 debug_exit(l_proc_name);
1260 --
1261 END fetch_allow_eles_frm_udt;
1262 --
1263 --
1264 --
1265 PROCEDURE set_extract_globals
1266 (p_business_group_id IN NUMBER
1267 ,p_effective_date IN DATE
1268 ,p_assignment_id IN NUMBER
1269 )
1270 IS
1271
1272 l_proc_name VARCHAR2(61):= 'set_extract_globals';
1273
1274 l_error NUMBER ;
1275 l_tp1_nested_level NUMBER ;
1276 l_request_id NUMBER ;
1277 l_conc_prog_name VARCHAR2(30);
1278
1279 l_udt_id pay_user_tables.user_table_id%TYPE;
1280 l_lea_details csr_lea_details%ROWTYPE;
1281 l_lea_details_1 csr_lea_details%ROWTYPE; -- Added for checking if
1282 -- there are multiple LEA's with same LEA number
1283 l_lea_dets_by_loc csr_lea_details_by_loc%ROWTYPE;
1284 l_estb_details csr_estb_details%ROWTYPE;
1285 l_estb_details_by_loc csr_estb_details_by_loc%ROWTYPE;
1286 l_req_dets pqp_gb_t1_pension_extracts.csr_request_dets%ROWTYPE;
1287 l_lea_dets_frm_bg csr_lea_details%ROWTYPE;
1288
1289
1290
1291 BEGIN
1292 debug_enter(l_proc_name);
1293
1294 debug(l_proc_name, 10);
1295 debug ('p_business_group_id:'||p_business_group_id);
1296 debug ('p_effective_date:'||p_effective_date);
1297 debug ('p_assignment_id:'||p_assignment_id);
1298
1299 g_business_group_id := p_business_group_id;
1300 g_effective_date := p_effective_date;
1301
1302 -- get the parent request Id.
1303 -- It is required to fetch the correct LEA number
1304 -- from the pqp_ext_cross_person_records table.
1305
1306 OPEN pqp_gb_t1_pension_extracts.csr_request_dets;
1307 FETCH pqp_gb_t1_pension_extracts.csr_request_dets INTO l_req_dets;
1308 CLOSE pqp_gb_t1_pension_extracts.csr_request_dets;
1309
1310 l_request_id := l_req_dets.parent_request_id ;
1311 debug ('l_request_id:'||l_request_id, 20);
1312
1313 -- Check for the paerntID till the request Id of TPEP
1314 -- Coz, there may be requests, which are generating sub requests.
1315 -- and we are only interested in the main parent process.
1316 WHILE (l_request_id <> -1)
1317 LOOP
1318 g_parent_request_id := l_request_id ;
1319
1320 OPEN pqp_gb_t1_pension_extracts.csr_request_dets (p_request_id => l_request_id);
1321 FETCH pqp_gb_t1_pension_extracts.csr_request_dets INTO l_req_dets;
1322 CLOSE pqp_gb_t1_pension_extracts.csr_request_dets;
1323 l_conc_prog_name := l_req_dets.concurrent_program_name ;
1324 debug ('l_conc_prog_name:'||l_conc_prog_name, 25);
1325 l_request_id := l_req_dets.parent_request_id ;
1326 END LOOP;
1327
1328 --Extract Process itself generates threads,
1329 -- We need to be sure if it is TPEP or EP
1330 --check the concurrent Program Name
1331 -- BENXTRCT = Extract Process
1332 -- PQPXTRCT = TPEP
1333 IF l_conc_prog_name = 'BENXTRCT' THEN --Extracp Process
1334 g_parent_request_id := -1 ;
1335 END IF ;
1336
1337
1338 -- if the Extract Process is running on its own., then g_parent_request_id = -1
1339 debug ('g_parent_request_id:'||g_parent_request_id, 30);
1340 -- This request ID will be furhter used in pqp_gb_t1_pension_extracts.reset_proc_status.
1341
1342 IF (g_parent_request_id <> -1) THEN
1343 debug ('*********this is a TPEP run***********', 40);
1344 ELSE
1345 debug ('*********this is a EXTRACT Process run***********', 50);
1346 END IF ;
1347
1348 debug('open csr_pqp_extract_attributes:', 60);
1349
1350 OPEN csr_pqp_extract_attributes;
1351 FETCH csr_pqp_extract_attributes INTO g_extract_type, g_extract_udt_name, l_udt_id;
1352 CLOSE csr_pqp_extract_attributes;
1353
1354 debug ('g_extract_type:'||g_extract_type, 70);
1355 debug ('g_extract_udt_name:'||g_extract_udt_name);
1356 debug ('l_udt_id:'||l_udt_id);
1357
1358 g_criteria_location_code := get_extract_udt_info
1359 ('Location Code' -- column
1360 ,'Criteria' -- row
1361 ,p_effective_date
1362 );
1363 debug ('g_criteria_location_code:'||g_criteria_location_code, 80);
1364
1365 IF g_criteria_location_code IS NOT NULL THEN
1366 -- it could be a non-lea run to confirm get location EIT Details
1367 debug ('inside IF g_criteria_location_code IS NOT NULL ', 90);
1368
1369 OPEN csr_estb_details_by_loc(p_location_code => g_criteria_location_code
1370 );
1371 FETCH csr_estb_details_by_loc INTO l_estb_details_by_loc;
1372
1373 -- Bug on Type 4
1374 -- Check whether criteria establishment exists
1375 IF csr_estb_details_by_loc%notfound THEN
1376
1377 debug ('inside csr_estb_details_by_loc%notfound ', 110);
1378
1379 l_error := pqp_gb_tp_extract_functions.raise_extract_error
1380 (p_business_group_id => p_business_group_id
1381 ,p_assignment_id => p_assignment_id
1382 ,p_error_text => 'BEN_93008_EXT_TP4_INVALID_LOC'
1383 ,p_error_number => 93008
1384 );
1385 debug ('raised error:'||l_error, 120);
1386 END IF; -- End if of not found check...
1387 CLOSE csr_estb_details_by_loc;
1388
1389 debug ('l_estb_details_by_loc.business_group_id: '||l_estb_details_by_loc.business_group_id, 130);
1390 debug ('l_estb_details_by_loc.location_id: '||l_estb_details_by_loc.location_id);
1391 debug ('l_estb_details_by_loc.lea_estb_yn: '||l_estb_details_by_loc.lea_estb_yn);
1392 debug ('l_estb_details_by_loc.estb_number: '||l_estb_details_by_loc.estb_number);
1393 debug ('l_estb_details_by_loc.estb_name: '||l_estb_details_by_loc.estb_name);
1394 debug ('l_estb_details_by_loc.lea_number: '||l_estb_details_by_loc.lea_number);
1395
1396
1397 IF l_estb_details_by_loc.lea_estb_yn = 'Y' THEN
1398 -- it is a lea run in the guise of a non-lea run
1399 debug ('inside csr_estb_details_by_loc.lea_estb_yn = Y ', 140);
1400 debug ('***It is an LEA Run *** ', 150);
1401 g_estb_number:= '0000';
1402 --g_originators_title := SUBSTR(l_lea_details.lea_name,1,16);
1403 ELSE -- l_estb_details_by_loc.lea_estb_yn = 'Y'
1404 debug ('inside ELSE of csr_estb_details_by_loc.lea_estb_yn = Y ', 160);
1405 debug ('***It is an NON - LEA Run *** ', 170);
1406
1407 g_estb_number := l_estb_details_by_loc.estb_number;
1408 g_originators_title := SUBSTR(l_estb_details_by_loc.estb_name,1,16);
1409
1410 END IF ; --l_estb_details_by_loc.lea_estb_yn = 'Y'
1411 ELSE -- g_criteria_location_code IS NULL
1412 -- it is a lea run
1413 debug ('inside ELSE g_criteria_location_code IS NOT NULL ', 180);
1414 debug ('***It is an LEA Run *** ', 190);
1415 g_estb_number := '0000' ;
1416 END IF; -- g_criteria_location_code IS NULL
1417
1418
1419 -- At this point we know if it is a LEA or a Non-LEA run
1420 -- And whether it is TPEP or EP...
1421 -- We need to get the LEA number accordingly.
1422 debug ('g_lea_number:>'||g_lea_number||'<', 210);
1423 g_lea_number := NULL; -- coz g_lea_number is a padded string
1424 -- and it will fail the csr_lea_details later.
1425
1426 IF (g_estb_number <> '0000' AND g_parent_request_id <> -1) THEN --set the g_lea_number
1427 debug ('***It is an NON- LEA --- TPEP Run *** ', 220);
1428 -- LEA Number is already s et by the Extract process.
1429 -- fetch from the cross Person table.
1430 -- The Cursor fetches the LEA numebr for the current Run.
1431 OPEN csr_lea_number ;
1432 FETCH csr_lea_number INTO g_lea_number ;
1433
1434 IF csr_lea_number%notfound THEN
1435 debug ('LEA Number not found at pqp_ext_cross_person_records ',230);
1436 -- it is an Extract Process thread ..
1437 -- set g_parent_request_id = -1
1438 g_parent_request_id := -1 ;
1439 END IF ;
1440 CLOSE csr_lea_number;
1441 debug ('g_lea_number:'||g_lea_number, 240);
1442 ELSIF (g_estb_number <> '0000' AND g_parent_request_id = -1) THEN ----set the g_lea_number
1443 debug ('***It is an NON- LEA --- EXTRACT Process Run *** ', 250);
1444 -- Bugfix 3671727:ENH1 : Get the LEA Number in this order
1445 -- 1) from the location EIT
1446 -- 2) Org linked to that location
1447 -- 3) The BG
1448 IF l_estb_details_by_loc.lea_number IS NOT NULL THEN
1449 -- Step 1) Getting LEA Number from location EIT
1450 g_lea_number := l_estb_details_by_loc.lea_number ;
1451 --For warning msg if more than one LEA are found
1452 g_token_org_name := l_estb_details_by_loc.estb_name ;
1453 END IF ;
1454 debug ('g_lea_number:'||g_lea_number,260);
1455
1456 IF g_lea_number IS NULL THEN -- fetch it from the Org linked to Location
1457
1458 -- Step 2) Getting LEA Number from Org linked to the location
1459 OPEN csr_lea_details_by_loc(l_estb_details_by_loc.location_id);
1460 FETCH csr_lea_details_by_loc INTO l_lea_dets_by_loc;
1461
1462 IF (csr_lea_details_by_loc%FOUND
1463 AND
1464 l_lea_dets_by_loc.lea_number IS NOT NULL
1465 ) THEN
1466 g_lea_number := l_lea_dets_by_loc.lea_number;
1467 debug ('g_lea_number:'||g_lea_number,270);
1468 END IF ;
1469 CLOSE csr_lea_details_by_loc ;
1470 END IF ;
1471
1472 IF g_lea_number IS NULL THEN -- fetch it from the BG
1473 -- LEA Number is not present on org linked to location
1474 -- Step 3) Look for LEA Number at BG level
1475 OPEN csr_lea_details
1476 (p_organization_id => p_business_group_id
1477 ,p_lea_number => NULL
1478 );
1479 FETCH csr_lea_details INTO l_lea_dets_frm_bg;
1480 IF (csr_lea_details%FOUND
1481 AND
1482 l_lea_dets_frm_bg.lea_number IS NOT NULL
1483 ) THEN
1484 g_lea_number := l_lea_dets_frm_bg.lea_number;
1485 --For warning msg if more than one LEA are found
1486 g_token_org_name := l_lea_dets_frm_bg.organization_name ;
1487 debug ('g_lea_number:'||g_lea_number,280);
1488 CLOSE csr_lea_details;
1489 ELSE -- NOT FOUND or LEA Number is NULL
1490 -- Close both cursors
1491 CLOSE csr_lea_details;
1492 -- Error out as the current BG is not set up as an LEA
1493
1494 l_error := pqp_gb_tp_extract_functions.raise_extract_error
1495 (p_business_group_id => p_business_group_id
1496 ,p_assignment_id => p_assignment_id
1497 ,p_error_text => 'BEN_94017_CURR_BG_NOT_LEA_ERR'
1498 ,p_error_number => 94017
1499 );
1500 debug ('raised error:'||l_error, 285);
1501 RETURN;
1502
1503 END IF; -- NOT FOUND or LEA Number is NULL
1504 END IF ; ---- fetch it from the BG
1505
1506 ELSIF (g_estb_number = '0000') THEN --set the g_lea_number
1507 IF (g_parent_request_id <> -1 ) THEN
1508 debug ('***It is an LEA --- TPEP Run *** ', 290);
1509 -- LEA Numebr is already set by the Extract process.
1510 -- fetch from the cross Person table.
1511 -- The Cursor fetches the LEA numebr for the current Run.
1512 OPEN csr_lea_number ;
1513 FETCH csr_lea_number INTO g_lea_number ;
1514 IF csr_lea_number%notfound THEN
1515 debug ('LEA Number not found at pqp_ext_cross_person_records ', 310);
1516 g_lea_number := NULL ;
1517 END IF ;
1518 CLOSE csr_lea_number;
1519 debug ('g_lea_number:'||g_lea_number,320);
1520 ELSE
1521 debug ('***It is an LEA --- EXTRACT Process Run *** ', 330);
1522
1523 END IF ; -- (g_parent_request_id <> -1 )
1524
1525 debug('open csr_lea_details:', 340);
1526 debug ('g_business_group_id:'||g_business_group_id);
1527 debug ('g_lea_number:>'||g_lea_number||'<');
1528
1529 OPEN csr_lea_details(p_organization_id => g_business_group_id
1530 -- ENH1 : added a new parameter to fetch the
1531 -- LEA details only for the required LEA
1532 ,p_lea_number => g_lea_number);
1533 FETCH csr_lea_details INTO l_lea_details;
1534 -- IF no LEA details are found , RAISE an ERROR and EXIT.
1535 -- The case can arise only if The Extract Process is Run
1536 -- and there is no LEA defined at BG level at all.
1537 IF (csr_lea_details%NOTFOUND OR l_lea_details.lea_number IS NULL) THEN
1538 l_error := pqp_gb_tp_extract_functions.raise_extract_error
1539 (p_business_group_id => g_business_group_id
1540 ,p_assignment_id => p_assignment_id
1541 ,p_error_text => 'BEN_94017_CURR_BG_NOT_LEA_ERR'
1542 ,p_error_number => 94017
1543 );
1544 debug ('raised error:'||l_error, 350);
1545 RETURN;
1546 ELSE -- csr_lea_details%NOTFOUND
1547 g_lea_number := l_lea_details.lea_number;
1548 g_crossbg_enabled := nvl(l_lea_details.crossbg_enabled, 'N');
1549 g_token_org_name := l_lea_details.organization_name ;
1550 g_originators_title := SUBSTR(l_lea_details.lea_name,1,16);
1551 END IF ; -- csr_lea_details%NOTFOUND
1552 CLOSE csr_lea_details;
1553
1554 END IF ; --set the g_lea_number
1555
1556
1557 debug ('g_business_group_id:'||g_business_group_id, 360);
1558 debug ('g_effective_date:'||g_effective_date);
1559 debug ('g_lea_number:'||g_lea_number);
1560
1561 -- check for more than one LEA with the same LEA_number in a BG .
1562 OPEN csr_lea_details(p_organization_id => g_business_group_id
1563 ,p_lea_number => g_lea_number);
1564 FETCH csr_lea_details INTO l_lea_details_1;
1565 debug ('1. l_lea_details_1.organization_name: '||l_lea_details_1.organization_name, 370);
1566 debug ('1. l_lea_details_1.CrossBG_Enabled: '||l_lea_details_1.CrossBG_Enabled);
1567 debug ('1. l_lea_details_1.organization_id: '||l_lea_details_1.organization_id);
1568
1569 FETCH csr_lea_details INTO l_lea_details_1;
1570 IF csr_lea_details%FOUND THEN
1571 debug ('2. l_lea_details_1.organization_name: '||l_lea_details_1.organization_name,380);
1572 debug ('2. l_lea_details_1.CrossBG_Enabled: '||l_lea_details_1.CrossBG_Enabled);
1573 debug ('1. l_lea_details_1.organization_id: '||l_lea_details_1.organization_id);
1574 g_multi_lea_exist := 'Y'; --set the warning flag o 'Y' .
1575 END IF;
1576 CLOSE csr_lea_details;
1577
1578 debug ('g_estb_number:'||g_estb_number,390);
1579 debug ('g_originators_title:'||g_originators_title);
1580
1581 -- Setting all the required globals of tp1 package
1582 -- global Dates for tp1 will be set in set_run_effective_dates procedure.
1583 -- These globals are required if the Type4 report is running.
1584 -- Type1 report sets these globals itself from the Type4 globals
1585
1586 pqp_gb_t1_pension_extracts.g_business_group_id := g_business_group_id ;
1587 pqp_gb_t1_pension_extracts.g_lea_number := g_lea_number ;
1588 pqp_gb_t1_pension_extracts.g_crossbg_enabled := g_crossbg_enabled ;
1589 pqp_gb_t1_pension_extracts.g_primary_assignment_id := p_assignment_id ;
1590 pqp_gb_t1_pension_extracts.g_extract_type := g_extract_type ;
1591
1592 -- Extract the list of criteria organizations which will be used to search
1593 IF g_estb_number = '0000' THEN -- LEA Run
1594
1595 fetch_criteria_establishments(l_estb_details);
1596 -- Call tp1 package procedure to store cross BG details..
1597 -- If its the LEA run
1598 -- AND current BG is enabled for cross BG reporting
1599 IF g_crossbg_enabled = 'Y' THEN
1600 -- Store all BGs with same LEA Number and
1601 -- enabled for cross BG reporting
1602 l_tp1_nested_level := pqp_gb_t1_pension_extracts.g_nested_level; --115.34
1603 pqp_gb_t1_pension_extracts.g_nested_level := g_nested_level;
1604 pqp_gb_t1_pension_extracts.store_cross_bg_details ;
1605 pqp_gb_t1_pension_extracts.g_nested_level := l_tp1_nested_level ; --115.34
1606 ELSE
1607 -- Bugfix 3823873 :
1608 -- Setting the master BG Id. It was not being set for single Bg
1609 -- set up for LEA run. Discovered when debugging issue with date
1610 -- track updates on NI
1611 pqp_gb_t1_pension_extracts.g_master_bg_id := g_business_group_id;
1612 END IF;
1613
1614 ELSE --non LEA run
1615 fetch_criteria_establishments(l_estb_details_by_loc);
1616 pqp_gb_t1_pension_extracts.g_master_bg_id := g_business_group_id;
1617 END IF ;
1618
1619 -- Added for salary scale changes
1620 -- at the moment do this only for Type 1 and Type 4
1621
1622 IF g_extract_type IN ('TP4', 'TP1P', 'TP1') THEN
1623
1624 debug ('inside IF g_extract_type IN (TP4, TP1P, TP1) ', 410);
1625
1626 g_tab_mng_aln_eles.DELETE;
1627 g_tab_ret_aln_eles.DELETE;
1628 g_tab_tlr_aln_eles.DELETE;
1629
1630 fetch_allow_eles_frm_udt (p_assignment_id => p_assignment_id
1631 ,p_effective_date => p_effective_date
1632 );
1633 END IF; -- End if of extract type check ...
1634
1635 g_header_system_element
1636 := g_extract_type||':'||g_lea_number||':'||g_estb_number||':';
1637
1638 debug ('g_header_system_element:'||g_header_system_element, 420);
1639
1640 -- Reset the processing status in multiproc data table to U
1641 -- if the extract process is running on its own.
1642 l_tp1_nested_level := pqp_gb_t1_pension_extracts.g_nested_level; --115.34
1643 pqp_gb_t1_pension_extracts.g_nested_level := g_nested_level;
1644
1645 pqp_gb_t1_pension_extracts.reset_proc_status ;
1646
1647 pqp_gb_t1_pension_extracts.g_nested_level := l_tp1_nested_level ; --115.34
1648
1649 debug_exit(l_proc_name);
1650 END set_extract_globals;
1651 --
1652 --
1653 --
1654 PROCEDURE set_pay_proc_events_to_process
1655 (p_assignment_id IN NUMBER
1656 ,p_status IN VARCHAR2
1657 ,p_start_date IN DATE
1658 ,p_end_date IN DATE
1659 )
1660 IS
1661
1662 PRAGMA AUTONOMOUS_TRANSACTION;
1663
1664 l_proc_name VARCHAR2(61):= 'set_pay_proc_events_to_process';
1665
1666 BEGIN
1667 debug_enter(l_proc_name);
1668
1669 debug('p_status: '||p_status);
1670 debug('p_start_date: '||fnd_date.date_to_canonical(p_start_date));
1671 debug('p_end_date: '||fnd_date.date_to_canonical(p_end_date));
1672
1673 --
1674 -- Mark pay_process_events to process
1675 -- as determined by the date range. The maxmim allowed range
1676 -- is the last eff date and a day before the current eff date
1677
1678 UPDATE pay_process_events
1679 SET retroactive_status = p_status
1680 ,status = p_status
1681 WHERE assignment_id = p_assignment_id
1682 AND change_type = 'REPORTS'
1683 AND creation_date -- allow all events as of and on last eff dt
1684 BETWEEN GREATEST(NVL(p_start_date,g_last_effective_date)
1685 ,g_last_effective_date)
1686 AND LEAST(NVL(p_end_date,g_effective_run_date)
1687 ,g_effective_run_date)
1688 ; -- allow all events upto end of day (eff_dt - 1)
1689
1690 COMMIT;
1691
1692 debug(fnd_number.number_to_canonical(SQL%ROWCOUNT)||' PPE row(s) updated.');
1693
1694 debug_exit(l_proc_name);
1695 END set_pay_proc_events_to_process;
1696 --
1697 --
1698 -- ----------------------------------------------------------------------------
1699 -- |---------------------< chk_tp4_is_teacher_new_starter >--------------------|
1700 -- ----------------------------------------------------------------------------
1701 --
1702 FUNCTION chk_tp4_is_teacher_new_starter
1703 (p_business_group_id IN NUMBER -- context
1704 ,p_effective_date IN DATE -- context
1705 ,p_assignment_id IN NUMBER -- context
1706 ) RETURN VARCHAR2 -- Y or N
1707 IS
1708
1709 l_inclusion_flag VARCHAR2(1):='N';
1710 l_starter_flag VARCHAR2(1):='N';
1711 -- l_leaver_flag VARCHAR2(1):='N';
1712 -- l_itr NUMBER;
1713 -- l_location_changed BOOLEAN:= FALSE;
1714 l_teacher_start_date DATE;
1715 l_estb_number_flag VARCHAR2(1) := 'N' ; -- ENH6
1716 l_cdt_tchr_start_date DATE ; --to keep the start date of candidate assignment
1717 -- l_leaver_date DATE ;
1718 l_tp1_nested_level NUMBER ;
1719 idx NUMBER := 0; --Loop counter
1720 -- cntr NUMBER := 0; --Loop counter
1721 -- these two variabel will be used to check the start and leaver date
1722 -- for assignments to check if there is any continious assignment
1723 l_prev_start_date DATE ;
1724 l_earliest_start_date DATE ;
1725 l_error NUMBER;
1726
1727
1728 l_asg_details csr_asg_details%ROWTYPE;
1729 --l_asg_details_up pqp_gb_t1_pension_extracts.csr_asg_details_up%ROWTYPE;
1730 l_candidate_asg csr_asg_details%ROWTYPE; --to keep the candidate assignment for reporting.
1731 l_pqp_asg_attributes csr_pqp_asg_attributes_dn%ROWTYPE;
1732 --l_pqp_asg_attributes_up pqp_gb_t1_pension_extracts.csr_pqp_asg_attributes_up%ROWTYPE;
1733 l_cdt_asg_attributes csr_pqp_asg_attributes_dn%ROWTYPE; --to keep the assignment attribues of candidate assignment.
1734
1735 l_proc_name VARCHAR2(61):= g_proc_name||'chk_tp4_is_teacher_new_starter';
1736
1737 BEGIN
1738
1739 debug_enter(l_proc_name);
1740
1741 debug('Checking Type 4 For Assignment: '
1742 ||fnd_number.number_to_canonical(p_assignment_id),10);
1743 debug('p_business_group_id:'||p_business_group_id);
1744 debug('p_effective_date:'||p_effective_date);
1745 debug('g_business_group_id:'||g_business_group_id);
1746
1747 -- set all the globals here, if not already set.
1748
1749 IF g_business_group_id IS NULL THEN
1750 debug('Globals are not already set..setting now..', 20);
1751
1752 -- Added a new param p_assignment_id for type 4 Bug fix
1753 set_extract_globals(p_business_group_id => p_business_group_id
1754 ,p_effective_date => p_effective_date
1755 ,p_assignment_id => p_assignment_id
1756 ) ;
1757
1758 debug('after set_extract_globals', 30);
1759
1760 -- set the effective dates for the particular LEA.
1761 -- the Procedure looks for the LEA number at g_lea_number.
1762 set_run_effective_dates;
1763 debug('after set_run_effective_dates', 40);
1764 END IF;
1765
1766 -- Print all the globals in the log
1767 debug('-------GLOBALS-----------', 50);
1768 debug('g_business_group_id:'||g_business_group_id);
1769 debug('g_effective_date:'||g_effective_date);
1770 debug('g_lea_number:'||g_lea_number);
1771 debug('g_extract_type:'||g_extract_type);
1772 debug('g_extract_udt_name:'||g_extract_udt_name);
1773 debug('g_crossbg_enabled:'||g_crossbg_enabled);
1774 debug('pqp_gb_t1_pension_extracts.g_cross_per_enabled:'||pqp_gb_t1_pension_extracts.g_cross_per_enabled);
1775 debug('g_criteria_location_code:'||g_criteria_location_code);
1776 debug('g_estb_number:'||g_estb_number);
1777 debug('g_originators_title:'||g_originators_title);
1778 debug('g_last_effective_date:'||g_last_effective_date);
1779 debug('g_next_effective_date:'||g_next_effective_date);
1780 debug('g_header_system_element:'||g_header_system_element);
1781 debug('g_effective_run_date:'||g_effective_run_date);
1782 debug('g_token_org_name:'||g_token_org_name);
1783 debug('g_multi_lea_exist:'||g_multi_lea_exist);
1784 debug('g_parent_request_id:'||g_parent_request_id);
1785 debug('g_warn_no_location:'||g_warn_no_location);
1786 debug('-------GLOBALS-----------');
1787
1788 -- Check if there are location existing for the LEA passed to Extract.
1789 -- IF no Location exists, Raise a warning (for the first assignment only in this thread)
1790 -- and RETURN..
1791 IF g_criteria_estbs.COUNT = 0 THEN
1792 -- the assignment will eventually fail for validity as no location exists.
1793 l_inclusion_flag := 'N' ;
1794 -- Raise a warning ..
1795 warn_if_no_loc_exist(p_assignment_id => p_assignment_id) ;
1796
1797 ELSE -- g_criteria_estbs.COUNT = 0
1798
1799 -- Step 1. Check if no other process is processing the record
1800 -- for the person to which the p_assignment_id is attached.
1801 -- the method 'chk_report_person'in type1 report code does that,
1802 -- by checking for the same national_identifier
1803
1804 -- If other process is not processing then goto step 2 else STOP.
1805 l_tp1_nested_level := pqp_gb_t1_pension_extracts.g_nested_level; --115.34
1806 pqp_gb_t1_pension_extracts.g_nested_level := g_nested_level;
1807
1808 IF ( pqp_gb_t1_pension_extracts.chk_report_person
1809 (p_business_group_id => p_business_group_id
1810 ,p_effective_date => p_effective_date
1811 ,p_assignment_id => p_assignment_id)
1812 ) THEN
1813
1814 pqp_gb_t1_pension_extracts.g_nested_level := l_tp1_nested_level;
1815 debug('inside IF chk_report_person', 60);
1816
1817 -- check if the assignment has raised a starter event in the duration...
1818 -- if yes, then it is a candidate assignment for this report run
1819 l_starter_flag := assignment_has_a_starter_event(
1820 p_business_group_id => g_business_group_id
1821 ,p_assignment_id => p_assignment_id
1822 ,p_pqp_asg_attributes => l_pqp_asg_attributes --OUT
1823 ,p_asg_details => l_asg_details --OUT
1824 ,p_teacher_start_date => l_teacher_start_date --OUT
1825 ) ;
1826 debug('after assignment_has_a_starter_event', 70 );
1827 debug('l_starter_flag:'||l_starter_flag);
1828 debug('l_teacher_start_date:'||l_teacher_start_date);
1829
1830 print_debug_asg (l_asg_details) ; --To Remove later
1831 print_debug_asg_atr (l_pqp_asg_attributes) ; --To Remove Later
1832
1833 IF l_starter_flag = 'Y' THEN
1834 -- make this assignment a candidae assignment
1835 -- and store the l_earliest_start_date
1836 debug('inside IF l_starter_flag = Y', 80);
1837
1838 -- Fetch the Estb number for the assignment.
1839 l_asg_details.estb_number := g_criteria_estbs(l_asg_details.location_id).estb_number ;
1840 -- this is a candidate assignment for the reporting.
1841 -- add this assignment to g_ext_asg_details
1842 -- and set report_asg = 'N'
1843 -- (will decide later, if this has to be reported.)
1844 g_ext_asg_details(p_assignment_id) := l_asg_details ;
1845 g_ext_asg_details(p_assignment_id).report_asg := 'N' ;
1846
1847 --keep the candidate assignment for further verification.
1848 l_candidate_asg := l_asg_details;
1849 l_cdt_asg_attributes := l_pqp_asg_attributes ;
1850 l_cdt_tchr_start_date := l_teacher_start_date ;
1851 l_earliest_start_date := l_teacher_start_date ;
1852 debug('l_earliest_start_date:'||l_earliest_start_date, 90);
1853
1854 ELSE --l_starter_flag = 'Y' THEN
1855 debug('inside ELSE l_starter_flag = Y', 110);
1856 -- as there is no starter event in the report run period,
1857 -- so this assignment is not valid for the report
1858 debug('Primary asignment is not a valid teacher asssignment..',120);
1859
1860 -- As the Primary asignment is not a valid teaching assignment,
1861 -- we need to fetch the sec assignments for the Same person rec.
1862 -- we need to fetch the person ID from the primary assignment.
1863 OPEN pqp_gb_t1_pension_extracts.csr_asg_details_up (p_assignment_id => p_assignment_id
1864 ,p_effective_date => g_last_effective_date ) ; --Pension Period Start Date.
1865 FETCH pqp_gb_t1_pension_extracts.csr_asg_details_up INTO l_asg_details ;
1866 CLOSE pqp_gb_t1_pension_extracts.csr_asg_details_up ;
1867 debug('Adding the primary assignment to the global collection..',130);
1868 g_ext_asg_details(p_assignment_id) := l_asg_details ;
1869 g_ext_asg_details(p_assignment_id).report_asg := 'N' ;
1870 END IF ; -- l_starter_flag .
1871
1872 -- Fetch all the secondary assignments for the person
1873 -- with same National Identifier
1874 -- from all the BGs. (if Cross BG reporting is enabled)
1875 debug('Primary assignment has been checked. now fetch all Secondary asignments', 140);
1876 -- initialize the count of total concurrent
1877 -- part-time assignment starting on the same date
1878 pqp_gb_t1_pension_extracts.g_part_time_asg_count := 0 ;
1879 idx := 0; -- loop counter.....
1880 FOR l_sec_asgs IN pqp_gb_t1_pension_extracts.csr_sec_assignments(
1881 p_primary_assignment_id => p_assignment_id
1882 ,p_person_id => l_asg_details.person_id
1883 ,p_effective_date => g_last_effective_date -- Report period start date
1884 )
1885 -- and loop them to find the final reporting assignment.
1886 LOOP -- Check all Sec asignment
1887 idx := idx + 1;
1888 debug('Inside csr_sec_assignment loop',210+idx/100000);
1889 debug('l_sec_asgs.assignment_id:'||to_char(l_sec_asgs.assignment_id));
1890 debug('l_sec_asgs.person_id:'||to_char(l_sec_asgs.person_id));
1891 debug('l_sec_asgs.business_group_id:'||to_char(l_sec_asgs.business_group_id));
1892 debug('l_sec_asgs.bizgrpcol:'||to_char(l_sec_asgs.bizgrpcol));
1893
1894 l_starter_flag := assignment_has_a_starter_event(
1895 p_business_group_id => l_sec_asgs.business_group_id
1896 ,p_assignment_id => l_sec_asgs.assignment_id
1897 ,p_pqp_asg_attributes => l_pqp_asg_attributes --OUT
1898 ,p_asg_details => l_asg_details --OUT
1899 ,p_teacher_start_date => l_teacher_start_date --OUT
1900 ) ;
1901 debug('after assignment_has_a_starter_event', 220+idx/100000 );
1902 debug('l_starter_flag:'||l_starter_flag);
1903 debug('l_teacher_start_date:'||l_teacher_start_date);
1904 print_debug_asg (l_asg_details) ; --Need to remove later
1905 print_debug_asg_atr (l_pqp_asg_attributes) ; --Need to remove later
1906
1907 IF l_starter_flag = 'Y' THEN
1908 debug('inside IF l_starter_flag = Y', 230+idx/100000);
1909 --Check if the assignment is starting effectivly before the earlier chosen assignment
1910 IF (l_earliest_start_date IS NOT NULL ) THEN
1911
1912 IF (l_teacher_start_date <= l_earliest_start_date) THEN --It is a candidate assignment.
1913
1914 debug('l_teacher_start_date <= l_earliest_start_date', 235+idx/100000);
1915 l_inclusion_flag := 'Y' ;
1916 l_earliest_start_date := l_teacher_start_date ;
1917 debug('l_earliest_start_date:'||l_earliest_start_date);
1918 -- check if the assignment_start_date is less
1919 -- then the start_date of previously choosen candidaet_assignment
1920 -- if yes, then make the current assignment as the new candidate_asg.
1921 debug('inside IF (l_candidate_asg <> NULL)', 240+idx/100000);
1922 debug('l_candidate_asg.start_date:'||l_candidate_asg.start_date);
1923 debug('l_candidate_asg.ext_emp_cat_cd:'||l_candidate_asg.ext_emp_cat_cd);
1924
1925 -- ENH6: if there are 2 or more concurrent part-time eligible assignment
1926 -- report the estb_number = '0953'
1927 IF((l_asg_details.start_date = l_candidate_asg.start_date) --Conc Part Time chk
1928 AND (l_asg_details.ext_emp_cat_cd = 'P')
1929 AND (l_candidate_asg.ext_emp_cat_cd = 'P'))THEN
1930 -- set this flag for concurrent part_time assignments
1931 -- starting on the same date.
1932 l_estb_number_flag := 'Y' ;
1933 debug('l_estb_number_flag:'||l_estb_number_flag, 250+idx/100000);
1934 -- set the part time assignment count to 2.
1935 -- get_estb_number function checks this flag and returns '0953' if it is >1
1936 pqp_gb_t1_pension_extracts.g_part_time_asg_count := 2 ;
1937
1938 ELSIF (l_asg_details.start_date < l_candidate_asg.start_date) THEN ---Conc Part Time chk
1939 l_candidate_asg := l_asg_details ;
1940 l_cdt_asg_attributes := l_pqp_asg_attributes ;
1941 l_cdt_tchr_start_date := l_teacher_start_date ;
1942 l_estb_number_flag := 'N' ; --reset for further loops...
1943 pqp_gb_t1_pension_extracts.g_part_time_asg_count := 0 ; --reset for further loops.
1944 debug('l_estb_number_flag:'||l_estb_number_flag, 260+idx/100000);
1945 END IF; --Conc Part Time chk
1946 debug('l_estb_number_flag:'||l_estb_number_flag, 270+idx/100000);
1947 END IF ; --(l_teacher_start_date <= l_earliest_start_date)
1948 ELSE --l_earliest_start_date <> NULL
1949 debug('inside ELSE of l_earliest_start_date IS NOT NULL', 280+idx/100000);
1950 l_candidate_asg := l_asg_details ; -- First time assignment....
1951 l_earliest_start_date := l_teacher_start_date ;
1952 END IF; --l_earliest_start_date <> NULL
1953 ELSE --l_starter_flag = 'Y' THEN
1954 debug('else of l_starter_flag = Y', 290+idx/100000) ;
1955 END IF ; --l_starter_flag = 'Y' THEN
1956 debug('Moving to next assignment . . . .', 310 + idx/100000);
1957 END LOOP ; -- Check all Sec assignment
1958
1959 debug('After secondary assignment loop....',320);
1960 debug('l_inclusion_flag:'||l_inclusion_flag);
1961 debug('total secondary assignments checked:'||idx);
1962 debug('l_earliest_start_date:'||l_earliest_start_date);
1963 debug('l_estb_number_flag:'||l_estb_number_flag);
1964 debug('---------CANDIDATE ASSIGNMENT_DETAILS---------');
1965 print_debug_asg (l_candidate_asg) ; -- Need to remove later
1966
1967 IF (l_inclusion_flag = 'N' AND (l_candidate_asg.assignment_id IS NULL)) THEN
1968 debug('Assignment is not a valid assignemnt to be included in the report.',330);
1969 g_ext_asg_details(p_assignment_id) := NULL ;
1970 ELSE -- (l_inclusion_flag = 'N' AND (l_candidate_asg.assignment_id = NULL))
1971 -- now check all the previous results. to determine the actual report date
1972 l_prev_start_date := get_prev_tp4_result(l_candidate_asg.person_id) ;
1973 debug ('l_prev_start_date :'||l_prev_start_date, 335) ;
1974 IF l_prev_start_date IS NULL THEN
1975 -- there are no previous results. so the assignment found so far is the candidate asg.
1976 -- and the earliest start date is correct.
1977 debug ('l_prev_start_date IS NULL', 340) ;
1978 ELSIF l_prev_start_date <= l_earliest_start_date THEN
1979 --raise a warning : "Already reported with start_date = l_prev_start_date"
1980 l_error := pqp_gb_tp_extract_functions.raise_extract_warning
1981 (p_assignment_id => p_assignment_id
1982 ,p_error_text => 'BEN_94018_TPA_ALREADY_REPORTED'
1983 ,p_error_number => 94018
1984 ,p_token1 => fnd_date.date_to_displaydate(l_prev_start_date)
1985 );
1986 debug ('raised warning for Already reported with start_date:'||l_error,350);
1987 l_inclusion_flag := 'Y' ;
1988 ELSIF l_prev_start_date > l_earliest_start_date THEN
1989 --raise a warning : "Already reported with start_date = l_prev_start_date, new starter found at date = l_earliest_start_date".
1990 l_error := pqp_gb_tp_extract_functions.raise_extract_warning
1991 (p_assignment_id => p_assignment_id
1992 ,p_error_text => 'BEN_94019_TPA_RPRTD_NEW_FOUND'
1993 ,p_error_number => 94019
1994 ,p_token1 => fnd_date.date_to_displaydate(l_prev_start_date)
1995 ,p_token2 => fnd_date.date_to_displaydate(l_earliest_start_date)
1996 );
1997 debug ('raised warning for Already reported with start_date:'||l_error,360);
1998 debug ('new starter found.........:'||l_error,370);
1999 l_inclusion_flag := 'Y' ;
2000 END IF ;
2001
2002 -- ENH6: check if there are concurrent part_time assignments,
2003 -- starting on the same date, set the estb no = '0953'
2004 debug ('l_candidate_asg.estb_number: '||g_criteria_estbs(l_candidate_asg.location_id).estb_number, 380) ;
2005 l_candidate_asg.estb_number := g_criteria_estbs(l_candidate_asg.location_id).estb_number;
2006
2007 -- IF the finally chosen assignment is Primary Assignment,
2008 IF (l_candidate_asg.assignment_id = p_assignment_id) THEN
2009 g_ext_asg_details(p_assignment_id).report_asg := 'Y';
2010 g_ext_asg_details(p_assignment_id).tp_safeguarded_grade := l_cdt_asg_attributes.tp_safeguarded_grade ;
2011 g_ext_asg_details(p_assignment_id).tp_sf_spinal_point_id:= l_cdt_asg_attributes.tp_sf_spinal_point_id ;
2012 g_ext_asg_details(p_assignment_id).start_date := l_earliest_start_date ;
2013 l_inclusion_flag :='Y' ;
2014
2015 ELSE --(l_candidate_asg.assignment_id = p_assignment_id) THEN
2016
2017 -- Add l_candidate_asg assignment to g_ext_asg_details
2018 -- and set report_asg flag = 'Y' for this assignment in g_ext_asg_details.
2019 -- though this flag does not have any significance here.
2020
2021 l_candidate_asg.tp_safeguarded_grade := l_cdt_asg_attributes.tp_safeguarded_grade;
2022 l_candidate_asg.tp_sf_spinal_point_id := l_cdt_asg_attributes.tp_sf_spinal_point_id;
2023 l_candidate_asg.start_date := l_earliest_start_date ;
2024 g_ext_asg_details(l_candidate_asg.assignment_id) := l_candidate_asg ;
2025 g_ext_asg_details(l_candidate_asg.assignment_id).report_asg := 'Y' ;
2026
2027 -- overwirte the details of primary assignment with that of the candidate assignment
2028 -- coz, these details are to be shown on the reports. else the details of
2029 -- Primary assignment will be displayed (which actuallly is not a valid assignment)
2030 -- But, don't set the flag report_asg for Primary assignment to 'Y' (set only details)
2031
2032 g_ext_asg_details(p_assignment_id) := l_candidate_asg ;
2033 l_inclusion_flag :='Y' ;
2034
2035 END IF; --(l_candidate_asg.assignment_id = p_assignment_id) THEN
2036
2037 -- This number is the total number of rec in the collection,
2038 -- not the actual numebr to be reported
2039 -- as now we are adding a few primary asgs also,
2040 -- even though these are not to be reported
2041
2042 debug('Number of TP4 teachers :'||
2043 fnd_number.number_to_canonical(g_ext_asg_details.COUNT),440);
2044
2045 l_asg_details := g_ext_asg_details(p_assignment_id) ;
2046
2047 debug('--ASSIGNMENT_DETAILS - FINAL (Primary)--');
2048 print_debug_asg (l_asg_details) ; --Need to remove later
2049
2050 IF (l_asg_details.report_asg <> 'Y') THEN -- There is one more record present...
2051
2052 l_asg_details := g_ext_asg_details(l_candidate_asg.assignment_id) ;
2053 debug('--ASSIGNMENT_DETAILS - FINAL (Secondary)--');
2054 print_debug_asg (l_asg_details) ; --Need to remove Later
2055 END IF ;
2056
2057 END IF ; --(l_inclusion_flag = 'N' AND (l_candidate_asg.assignment_id = NULL))
2058
2059 ELSE ---- chk_report_person
2060 -- this person is being reported by some other process. no need to process here..............
2061 pqp_gb_t1_pension_extracts.g_nested_level := l_tp1_nested_level; -- l_tp1_nested_level ;
2062 debug(l_proc_name,450);
2063 l_inclusion_flag := 'N';
2064 END IF ; -- chk_report_person
2065
2066 END IF ; -- g_criteria_estbs.COUNT = 0.
2067
2068 debug('Just before return, Inclusion Flag :'||l_inclusion_flag,460);
2069
2070 -- The following piece of code raises a warning if
2071 -- there exist more than one lea with the same lea Number within a BG.
2072 -- the warning is raised for the first valid assignment for a single Run.
2073 -- the flag for warning is set during the global setting through set_extract_globals.
2074 IF l_inclusion_flag = 'Y' THEN
2075 warn_if_multi_lea_exist (p_assignment_id => l_candidate_asg.assignment_id);
2076 END IF;
2077
2078 debug('++++++++++++++++++++++++ assignment CHECK IS OVER +++++++++++++++++++++++++++++++');
2079
2080 debug_exit(l_proc_name);
2081 RETURN l_inclusion_flag;
2082
2083 EXCEPTION
2084 WHEN OTHERS THEN
2085 debug('SQLCODE :'||to_char(SQLCODE));
2086 debug('SQLERRM :'||SQLERRM);
2087 debug_exit(' Others in '||l_proc_name
2088 ,'Y' -- turn trace off
2089 );
2090 RAISE;
2091 END chk_tp4_is_teacher_new_starter;
2092
2093 --
2094 --
2095 --
2096
2097 FUNCTION get_header_system_element
2098 -- ( p_trace IN VARCHAR2 DEFAULT 'N')
2099 RETURN VARCHAR2
2100 IS
2101
2102 l_proc_name VARCHAR2(61):= g_proc_name||'get_header_system_element';
2103
2104 BEGIN
2105
2106 debug_enter(l_proc_name);
2107
2108 debug(pqp_gb_tp_pension_extracts.g_header_system_element);
2109
2110 debug_exit(l_proc_name);
2111 RETURN pqp_gb_tp_pension_extracts.g_header_system_element;
2112
2113 END get_header_system_element;
2114 --
2115 --
2116 --
2117 FUNCTION get_lea_number
2118 -- (p_trace IN VARCHAR2 DEFAULT 'N')
2119 RETURN VARCHAR2
2120 IS
2121
2122 l_proc_name VARCHAR2(61):= g_proc_name||'get_tp_lea_number';
2123
2124 BEGIN
2125
2126 debug_enter(l_proc_name);
2127
2128 debug(pqp_gb_tp_pension_extracts.g_lea_number);
2129
2130 debug_exit(l_proc_name);
2131
2132 RETURN pqp_gb_tp_pension_extracts.g_lea_number;
2133
2134 EXCEPTION
2135 WHEN OTHERS THEN
2136 debug_exit(l_proc_name);
2137 RAISE;
2138 END get_lea_number;
2139 --
2140 --
2141 --
2142 FUNCTION get_estb_number
2143 (p_assignment_id IN NUMBER -- context -1 for header
2144 -- ,p_trace IN VARCHAR2 DEFAULT 'N'
2145 )RETURN VARCHAR2
2146 IS
2147
2148 l_proc_name VARCHAR2(61):= g_proc_name||'get_tp_estb_number';
2149 l_estb_number VARCHAR2(4);
2150
2151 l_report_asg VARCHAR2(1) ;
2152 l_tp1_nested_level NUMBER ;
2153 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
2154
2155 BEGIN
2156
2157 debug_enter(l_proc_name);
2158
2159 debug('p_assignment_id :'||
2160 fnd_number.number_to_canonical(p_assignment_id));
2161
2162 -- Bugfix 3820719 : Added to get the effective sec asg
2163 -- id if primary is not effective
2164 IF NVL(p_assignment_id, -1) = -1 THEN
2165 -- p_assignment_id is -1 when the header record
2166 -- calls this func thru the FF
2167 l_assignment_id := p_assignment_id;
2168 ELSIF (g_extract_type = 'TP1' -- Extract type is Type 1 annual
2169 OR
2170 g_extract_type = 'TP1P' -- Extract type is Type 1 periodic
2171 ) THEN
2172
2173 -- Chk whether the primary is to be reported
2174 -- The l_assignment_id OUT var will hv the primary
2175 -- asg id (=p_assignment_id) if yes, otherwise it will
2176 -- hv the secondary asg id that is to be used to get estb number
2177
2178 l_tp1_nested_level := pqp_gb_t1_pension_extracts.g_nested_level;
2179 pqp_gb_t1_pension_extracts.g_nested_level := g_nested_level;
2180
2181 l_report_asg := pqp_gb_t1_pension_extracts.chk_report_assignment
2182 (p_assignment_id => p_assignment_id
2183 ,p_secondary_assignment_id => l_assignment_id -- OUT
2184 );
2185
2186 -- Added this line to fix issue with loggin in Type 1 after this
2187 -- function has been called
2188 pqp_gb_t1_pension_extracts.g_nested_level := l_tp1_nested_level;
2189
2190 ELSE
2191 l_assignment_id := p_assignment_id;
2192 END IF;
2193
2194 debug('l_assignment_id :'||
2195 fnd_number.number_to_canonical(l_assignment_id));
2196
2197 IF NVL(l_assignment_id,-1) = -1 THEN
2198 l_estb_number := pqp_gb_tp_pension_extracts.g_estb_number;
2199
2200 ELSIF pqp_gb_t1_pension_extracts.g_override_ft_asg_id IS NOT NULL THEN
2201 -- Bugfix 3803760:FTSUPPLY
2202 -- If override ft asg is set, always use that for estb number
2203 l_estb_number := g_ext_asg_details(l_assignment_id).estb_number;
2204 ELSIF (pqp_gb_t1_pension_extracts.g_part_time_asg_count > 1) THEN
2205 -- Added for bugfix 3641851:ENH6
2206 --Concurrent Part time employees are to be reported on estb-number = 0953.
2207 l_estb_number := '0953';
2208 ELSE
2209 l_estb_number := g_ext_asg_details(l_assignment_id).estb_number;
2210 END IF;
2211
2212 debug_exit(l_proc_name);
2213
2214 RETURN l_estb_number;
2215
2216 EXCEPTION
2217 WHEN OTHERS THEN
2218 debug(SQLCODE);
2219 debug(SQLERRM);
2220 debug_exit('Others In '||l_proc_name);
2221 RAISE;
2222 END get_estb_number;
2223 --
2224 --
2225 --
2226 FUNCTION get_originators_title
2227 --( p_trace IN VARCHAR2 DEFAULT 'N' )
2228 RETURN VARCHAR2
2229 IS
2230
2231 l_proc_name VARCHAR2(61):= g_proc_name||'get_tp_originators_title';
2232
2233 BEGIN
2234
2235 debug_enter(l_proc_name);
2236
2237 debug(pqp_gb_tp_pension_extracts.g_originators_title);
2238
2239 debug_exit(l_proc_name);
2240
2241 RETURN pqp_gb_tp_pension_extracts.g_originators_title;
2242
2243 EXCEPTION
2244 WHEN OTHERS THEN
2245 debug_exit(l_proc_name);
2246 RAISE;
2247 END get_originators_title;
2248 --
2249 --
2250 --
2251 FUNCTION get_tp4_employment_category
2252 (p_assignment_id IN NUMBER
2253 -- ,p_trace IN VARCHAR2 DEFAULT 'N'
2254 ) RETURN VARCHAR2
2255 IS
2256
2257 l_proc_name VARCHAR2(61):= g_proc_name||
2258 'get_tp4_employment_category';
2259
2260 l_report_asg VARCHAR2(1) ;
2261 l_tp1_nested_level NUMBER ;
2262 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
2263
2264 BEGIN
2265
2266 debug_enter(l_proc_name);
2267 IF (g_extract_type = 'TP1' -- Extract type is Type 1 annual
2268 OR
2269 g_extract_type = 'TP1P' -- Extract type is Type 1 periodic
2270 ) THEN
2271
2272 -- Chk whether the primary is to be reported
2273 -- The l_assignment_id OUT var will hv the primary
2274 -- asg id (=p_assignment_id) if yes, otherwise it will
2275 -- hv the secondary asg id that is to be used to get employment caetgory
2276
2277 -- Added this line to fix issue with loggin in Type 1 after this
2278 -- function has been called
2279 l_tp1_nested_level := pqp_gb_t1_pension_extracts.g_nested_level; --115.34
2280 pqp_gb_t1_pension_extracts.g_nested_level := g_nested_level;
2281
2282 l_report_asg := pqp_gb_t1_pension_extracts.chk_report_assignment
2283 (p_assignment_id => p_assignment_id
2284 ,p_secondary_assignment_id => l_assignment_id -- OUT
2285 );
2286
2287 -- Added this line to fix issue with loggin in Type 1 after this
2288 -- function has been called
2289 pqp_gb_t1_pension_extracts.g_nested_level := l_tp1_nested_level; --115.34
2290
2291 ELSE
2292 l_assignment_id := p_assignment_id;
2293 END IF;
2294
2295 -- Bugfix 8407293
2296 IF (g_extract_type = 'TP4') THEN
2297 debug('p_assignment_id:'||p_assignment_id,25);
2298 l_assignment_id :=g_ext_asg_details(p_assignment_id).assignment_id;
2299 debug('l_assignment_id:'||l_assignment_id,26);
2300 END IF;
2301
2302 debug('l_report_asg:'||l_report_asg);
2303 debug('l_assignment_id:'||l_assignment_id) ;
2304 debug(g_ext_asg_details(l_assignment_id).ext_emp_cat_cd);
2305
2306 debug_exit(l_proc_name);
2307
2308 RETURN g_ext_asg_details(l_assignment_id).ext_emp_cat_cd;
2309
2310 END get_tp4_employment_category;
2311 --
2312 --
2313 --
2314 FUNCTION get_qualification_mno
2315 (p_person_id IN NUMBER -- Person Id
2316 ,p_memb_type IN VARCHAR2 -- Membership Type
2317 ,p_memb_body_name IN VARCHAR2 -- Membership Body Name
2318 ,p_business_group_id IN NUMBER
2319 ,p_effective_date IN DATE
2320 ) RETURN VARCHAR2
2321 IS
2322
2323 l_proc_name VARCHAR2(61) := g_proc_name||'get_qualification_mno';
2324
2325 l_membership_number per_qualifications.membership_number%type := NULL;
2326 l_membership_number2 per_qualifications.membership_number%type := NULL;
2327
2328 BEGIN
2329
2330 debug_enter(l_proc_name);
2331
2332 OPEN csr_membership_no
2333 (p_person_id => p_person_id
2334 ,p_business_group_id => p_business_group_id
2335 ,p_effective_date => p_effective_date
2336 ,p_memb_body_name => p_memb_body_name
2337 ,p_memb_type => p_memb_type
2338 );
2339 FETCH csr_membership_no INTO l_membership_number;
2340
2341 IF csr_membership_no%NOTFOUND THEN
2342
2343 -- Not Found, set to UNKNOWN
2344 l_membership_number := 'UNKNOWN';
2345
2346 ELSE -- Found, look again
2347
2348 FETCH csr_membership_no INTO l_membership_number2;
2349
2350 IF csr_membership_no%FOUND THEN
2351
2352 -- Too many found
2353 l_membership_number := 'TOOMANY';
2354
2355 END IF;
2356
2357 END IF;
2358
2359 CLOSE csr_membership_no;
2360
2361 debug_exit(l_proc_name);
2362
2363 RETURN l_membership_number;
2364
2365 END get_qualification_mno;
2366 --
2367 --
2368 --
2369 FUNCTION get_dflex_value
2370 (p_value OUT NOCOPY VARCHAR2 -- return value
2371 ,p_desc_flex_name IN VARCHAR2 -- Desc Flex Name
2372 ,p_column_name IN VARCHAR2 -- Base Table Column Name
2373 ,p_effective_date IN DATE -- Default Session date
2374 ,p_entity_key_name IN VARCHAR2 --
2375 ,p_entity_key_value IN VARCHAR2 --
2376 ,p_busnsgrp_id IN NUMBER --
2377 ,p_entity_busnsgrp_yn IN VARCHAR2 --
2378 ,p_entity_eff_date_yn IN VARCHAR2 --
2379 ) RETURN NUMBER -- Success/Failure Error Return code.
2380 IS
2381
2382 l_proc_name VARCHAR2(61) := g_proc_name||'get_dflex_value';
2383
2384 l_return_code NUMBER:= 0;
2385 l_entity_key_name VARCHAR2(32):= LOWER(p_entity_key_name);
2386 l_entity_key_value VARCHAR2(4000):= p_entity_key_value;
2387 l_entity_eff_date_yn VARCHAR2(1):=
2388 NVL(SUBSTR(UPPER(p_entity_eff_date_yn),1,1),'N');
2389 l_entity_busnsgrp_yn VARCHAR2(1):=
2390 NVL(SUBSTR(UPPER(p_entity_busnsgrp_yn),1,1),'N');
2391 l_table_specific_clause VARCHAR2(2000);
2392
2393 TYPE base_table_ref_csr_typ IS REF CURSOR;
2394 base_table_csr base_table_ref_csr_typ;
2395
2396 l_effective_date_clause VARCHAR2(2000):=
2397 ' AND TO_DATE('''||TO_CHAR(p_effective_date,'DD-MM-YYYY')||
2398 ''',''DD-MM-YYYY'')'||
2399 ' BETWEEN effective_start_date AND effective_end_date ';
2400
2401
2402 CURSOR csr_fnd_desc_flex IS
2403 SELECT *
2404 FROM fnd_descriptive_flexs_vl
2405 WHERE descriptive_flexfield_name = UPPER(p_desc_flex_name);
2406
2407 rec_fnd_desc_flex csr_fnd_desc_flex%ROWTYPE;
2408 --
2409 BEGIN
2410
2411 debug_enter(l_proc_name);
2412
2413 OPEN csr_fnd_desc_flex;
2414 FETCH csr_fnd_desc_flex INTO rec_fnd_desc_flex;
2415
2416 IF csr_fnd_desc_flex%NOTFOUND THEN
2417
2418 l_return_code := -2;
2419 p_value := NVL(p_desc_flex_name,'UNKNOWN');
2420
2421 ELSE
2422
2423 l_table_specific_clause :=
2424 ' FROM '||rec_fnd_desc_flex.APPLICATION_TABLE_NAME||' '||
2425 ' WHERE '||l_entity_key_name||' = '||l_entity_key_value||
2426 ' ';
2427
2428 IF l_entity_eff_date_yn = 'Y' THEN
2429
2430 l_table_specific_clause := l_table_specific_clause||
2431 l_effective_date_clause;
2432
2433 END IF;
2434
2435 IF l_entity_busnsgrp_yn = 'Y' AND p_busnsgrp_id IS NOT NULL THEN
2436
2437 l_table_specific_clause := l_table_specific_clause||
2438 ' AND ( business_group_id = '||TO_CHAR(p_busnsgrp_id) ||
2439 ' ) ';
2440 END IF;
2441
2442
2443 debug('SELECT '||p_column_name);
2444 debug(l_table_specific_clause);
2445
2446 OPEN base_table_csr FOR 'SELECT '||p_column_name||
2447 l_table_specific_clause;
2448 FETCH base_table_csr INTO p_value;
2449 CLOSE base_table_csr;
2450
2451 END IF;
2452
2453 CLOSE csr_fnd_desc_flex;
2454
2455 debug_exit(l_proc_name);
2456
2457 RETURN l_return_code;
2458
2459 -- Added by tmehra for nocopy changes Feb'03
2460
2461 EXCEPTION
2462 WHEN OTHERS THEN
2463 p_value := null;
2464 RETURN -1;
2465
2466 END get_dflex_value;
2467 --
2468 --
2469 --
2470 FUNCTION get_formatted_dfee_refno
2471 ( p_dfee_refno IN VARCHAR2 -- Dfee Ref Number Value
2472 ) RETURN VARCHAR2
2473 IS
2474
2475 l_proc_name VARCHAR2(61):=
2476 g_proc_name||'get_formatted_dfee_refno';
2477
2478 l_value per_all_assignments_f.ass_attribute1%type;
2479 --Keep len same as that of l_dfee_refno
2480 l_num_pos NUMBER := 0;
2481 l_sep_pos NUMBER := -1;
2482 l_sep_count NUMBER := 0;
2483 l_len NUMBER := 0;
2484 l_pre VARCHAR2(2);
2485 l_post VARCHAR2(5);
2486 l_char VARCHAR2(1);
2487
2488 BEGIN
2489
2490 debug_enter(l_proc_name);
2491
2492 -- Strip the blanks
2493 l_value := NVL(TRIM(p_dfee_refno),'UNKNOWN');
2494 l_pre := '';
2495 l_post := '';
2496 l_len := length(l_value);
2497 l_char := ' ';
2498
2499 IF l_value = 'UNKNOWN' OR l_value = 'TOOMANY' THEN
2500 debug_exit(l_proc_name);
2501 RETURN l_value;
2502 END IF;
2503
2504 IF l_len = 0 OR l_len > 8 THEN
2505 debug_exit(l_proc_name);
2506 RETURN 'INVALID';
2507 END IF;
2508
2509 FOR i in 1 .. l_len
2510 LOOP
2511
2512 l_char := substr(l_value,i,1);
2513 l_num_pos := instr('0123456789',l_char);
2514
2515 IF l_num_pos <> 0 THEN
2516 IF i < 3 THEN
2517 l_pre := l_pre || l_char;
2518 ELSE
2519 l_post := l_post || l_char;
2520 END IF;
2521 ELSE
2522 l_sep_count := l_sep_count + 1;
2523 l_sep_pos := i;
2524 END IF;
2525 END LOOP;
2526
2527 IF l_sep_count > 1 -- More than one seperators
2528 OR (l_sep_pos > -1
2529 AND l_sep_pos NOT BETWEEN 2 AND 3
2530 ) -- Seperator found but not in positions 2 OR 3
2531 OR l_pre IS NULL -- Part 1 not entered
2532 OR l_post IS NULL -- Part 2 not entered
2533 THEN
2534
2535 debug_exit(l_proc_name);
2536 RETURN 'INVALID';
2537
2538 ELSE
2539
2540 debug_exit(l_proc_name);
2541 RETURN LPAD(nvl(l_pre,'0'),2,'0')||LPAD(nvl(l_post,'0'),5,'0');
2542
2543 END IF;
2544
2545 EXCEPTION
2546 WHEN OTHERS THEN
2547 debug_exit(l_proc_name);
2548 RETURN 'INVALID';
2549 END get_formatted_dfee_refno;
2550 --
2551 --
2552 --
2553 FUNCTION get_dfee_reference_number
2554 (p_assignment_id IN NUMBER
2555 -- ,p_trace IN VARCHAR2 DEFAULT 'N'
2556 ) RETURN VARCHAR2
2557 IS
2558 l_proc_name VARCHAR2(61):=
2559 g_proc_name||'get_tp_dfee_reference_number';
2560 l_status_code NUMBER;
2561 l_asg_start_date DATE;
2562 l_person_id NUMBER;
2563 l_busnsgrp_id NUMBER;
2564
2565 -- QA failed due to length 30 increased to 100
2566 -- Max length based on the highest possible length of all its sources
2567 -- PAY_USER_COLUMN_INSTANCES_F VALUE VARCHAR2(80)
2568 -- PER_QUALIFICATIONS_V NAME NOT NULL VARCHAR2(100)
2569 -- PER_QUALIFICATIONS_V PROFESSIONAL_BODY_NAME VARCHAR2(80)
2570 -- FND_DESCRIPTIVE_FLEXS_VL DESCRIPTIVE_FLEXFIELD_NAME VARCHAR2(40)
2571 -- ALL_TAB_COLUMNS COLUMN_NAME VARCHAR2(30)
2572
2573 l_attr_location_type pay_user_column_instances_f.value%type;
2574 -- Attribute Location Type
2575 l_flexfield_name pay_user_column_instances_f.value%type;
2576 -- Flexfield Name
2577 l_column_name pay_user_column_instances_f.value%type;
2578 -- Flexfield Segment Name
2579
2580 l_dfee_refno per_all_assignments_f.ass_attribute1%type;
2581 -- Max length based on the highest possible length of all its sources
2582 -- PER_QUALIFICATIONS_V MEMBERSHIP_NUMBER VARCHAR2(80)
2583 -- PER_ALL_PEOPLE_F PER_INFORMATION1 VARCHAR2(150)
2584
2585 BEGIN
2586
2587 debug_enter(l_proc_name);
2588
2589 -- Step 1 Get Asg Date
2590
2591 l_asg_start_date := g_ext_asg_details(p_assignment_id).start_date;
2592
2593 -- Step 2 Get the person_id and the business group id
2594 l_person_id := g_ext_asg_details(p_assignment_id).person_id;
2595 l_busnsgrp_id := g_business_group_id;
2596
2597
2598 -- Step 3 Get Flexfield Name and the Column Name from the UDTable
2599
2600 l_attr_location_type := get_extract_udt_info
2601 (p_udt_column_name => 'Attribute Location Type'
2602 ,p_udt_row_name => 'DfEE Reference Number'
2603 );
2604
2605 debug('DfEE Attribute Location Type'||l_attr_location_type);
2606 l_flexfield_name := get_extract_udt_info
2607 (p_udt_column_name =>
2608 'Attribute Location Qualifier 1'
2609 ,p_udt_row_name => 'DfEE Reference Number'
2610 );
2611
2612 debug('DfEE Attribute Location Qualifier 1'||l_flexfield_name);
2613 l_column_name := get_extract_udt_info
2614 (p_udt_column_name => 'Attribute Location Qualifier 2'
2615 ,p_udt_row_name => 'DfEE Reference Number'
2616 );
2617
2618 debug('DfEE Attribute Location Qualifier 2'||l_column_name);
2619
2620 IF l_attr_location_type = 'Qualifications' THEN
2621
2622 -- Step 4 Get DfEE Ref No stored in Qualification Table
2623 l_dfee_refno := get_qualification_mno
2624 (p_person_id => l_person_id
2625 ,p_memb_type => l_flexfield_name
2626 ,p_memb_body_name => l_column_name
2627 ,p_business_group_id => l_busnsgrp_id
2628 ,p_effective_date => l_asg_start_date
2629 );
2630
2631 ELSE
2632
2633 IF l_attr_location_type = 'People' THEN
2634
2635 -- Step 5 Get DfEE Ref No stored in per_people_f Table
2636 l_status_code := get_dflex_value(l_dfee_refno
2637 ,l_flexfield_name
2638 ,l_column_name
2639 ,l_asg_start_date
2640 ,'PERSON_ID'
2641 ,TO_CHAR(l_person_id)
2642 ,l_busnsgrp_id
2643 ,'Y'
2644 ,'Y'
2645 );
2646
2647 ELSIF l_attr_location_type = 'Assignments' THEN
2648
2649 -- Step 6 Get DfEE Ref No stored in per_assignment Table
2650 l_status_code := get_dflex_value(l_dfee_refno
2651 ,l_flexfield_name
2652 ,l_column_name
2653 ,l_asg_start_date
2654 ,'ASSIGNMENT_ID'
2655 ,TO_CHAR(p_assignment_id)
2656 ,l_busnsgrp_id
2657 ,'Y'
2658 ,'Y'
2659 );
2660 END IF;
2661 END IF;
2662
2663 -- Step 7 ReFormat the Ref No.
2664
2665 debug(' DfEE before formatting '||l_dfee_refno);
2666
2667 l_dfee_refno := NVL(TRIM(l_dfee_refno),'UNKNOWN');
2668
2669 l_dfee_refno := get_formatted_dfee_refno(l_dfee_refno);
2670
2671 debug(' DfEE after formatting '||l_dfee_refno);
2672
2673 debug_exit(l_proc_name);
2674
2675 RETURN l_dfee_refno;
2676
2677 END get_dfee_reference_number;
2678 --
2679 --
2680 --
2681 FUNCTION get_tp4_start_date
2682 (p_assignment_id IN NUMBER
2683 -- ,p_trace IN VARCHAR2 DEFAULT 'N'
2684 ) RETURN VARCHAR2
2685 IS
2686
2687 l_proc_name VARCHAR2(61):= g_proc_name||'get_tp4_start_date';
2688
2689 BEGIN
2690
2691 debug_enter(l_proc_name);
2692 debug('p_assignment_id: '||p_assignment_id);
2693 debug('tp4_start_date: '||g_ext_asg_details(p_assignment_id).start_date);
2694
2695 debug_exit(l_proc_name);
2696
2697 RETURN
2698 TO_CHAR(g_ext_asg_details(p_assignment_id).start_date
2699 ,'DDMMYY');
2700
2701 END get_tp4_start_date;
2702 --
2703 --
2704 --
2705 FUNCTION get_flex_segment_value
2706 (p_entity_name IN VARCHAR2 -- name of the table holding the values
2707 ,p_entity_rowid IN ROWID -- Row Id
2708 ,p_segment_col_name IN VARCHAR2 -- Segment column name
2709 ) RETURN VARCHAR2
2710 IS
2711 -- Type Declarations
2712 TYPE base_table_ref_csr_typ IS REF CURSOR;
2713
2714 -- Variable Declarations
2715 c_base_table base_table_ref_csr_typ;
2716
2717 l_query VARCHAR2(4000); -- Dynamically constructed query
2718 l_segment_value per_grade_definitions.segment1%type := NULL; -- Return value
2719
2720 l_proc_name VARCHAR2(61):= g_proc_name||'get_flex_segment_value';
2721
2722 BEGIN
2723
2724 debug_enter(l_proc_name);
2725
2726 IF (p_entity_name is not null) AND
2727 (p_entity_rowid is not null) AND
2728 (p_segment_col_name is not null) THEN
2729
2730
2731 l_query :=
2732 'SELECT '||p_segment_col_name||' '||
2733 'FROM '||p_entity_name||' '||
2734 'WHERE rowid = :b_rowid ';
2735
2736 debug('Before opening dynamic query',10);
2737
2738 OPEN c_base_table FOR l_query USING p_entity_rowid;
2739 FETCH c_base_table INTO l_segment_value;
2740 CLOSE c_base_table;
2741
2742 debug('After precessing dynamic query',20);
2743 END IF;
2744
2745 debug_exit(l_proc_name);
2746
2747 RETURN l_segment_value;
2748
2749 END get_flex_segment_value;
2750 --
2751 --
2752 --
2753 FUNCTION get_kflex_value
2754 (p_context_id IN NUMBER -- Context Id
2755 ,p_flexfield_name IN VARCHAR2 -- Flexfield Name
2756 ,p_segment_name IN VARCHAR2 -- Flexfield Segment Name
2757 ,p_effective_date IN DATE -- Effective Date
2758 ) RETURN VARCHAR2
2759 IS
2760 -- Variable Declarations
2761 l_segment_col_value per_grade_definitions.segment1%type;
2762 -- Keep len same as l_ret_salary_scale
2763 l_entity_rowid ROWID;
2764
2765 l_proc_name VARCHAR2(61):= g_proc_name||'get_kflex_value';
2766
2767 BEGIN
2768
2769 debug_enter(l_proc_name);
2770
2771 OPEN csr_grade_definition_rowid
2772 (p_context_id
2773 ,p_effective_date);
2774 FETCH csr_grade_definition_rowid INTO l_entity_rowid;
2775 CLOSE csr_grade_definition_rowid;
2776
2777 debug('After getting rowid',10);
2778
2779 l_segment_col_value :=
2780 get_flex_segment_value
2781 (p_entity_name => 'PER_GRADE_DEFINITIONS'
2782 ,p_entity_rowid => l_entity_rowid
2783 ,p_segment_col_name => p_segment_name
2784 );
2785
2786 debug_exit(l_proc_name);
2787
2788 RETURN l_segment_col_value;
2789
2790 END get_kflex_value;
2791 --
2792 --
2793 --
2794 FUNCTION chk_grade_format
2795 ( p_sal_grade IN VARCHAR2 -- Salary Grade
2796 ) RETURN VARCHAR2 -- Return Y if correct format, N otherwise
2797 IS
2798
2799 CURSOR chkformat IS
2800 SELECT 'Y'
2801 FROM DUAL
2802 WHERE LENGTH(NVL(p_sal_grade,'x')) <= 3
2803 AND ASCII( SUBSTR(p_sal_grade,1,1))
2804 BETWEEN 65 AND 90
2805 AND TO_NUMBER(SUBSTR(p_sal_grade,2)) <= 99;
2806
2807 l_proc_name VARCHAR2(61) := g_proc_name||'chk_grade_format';
2808 l_formatvalid VARCHAR2(1) := 'N';
2809
2810 BEGIN
2811
2812 debug_enter(l_proc_name);
2813
2814 BEGIN
2815 OPEN chkformat;
2816 FETCH chkformat INTO l_formatvalid;
2817 CLOSE chkformat;
2818 EXCEPTION
2819 WHEN OTHERS THEN
2820 l_formatvalid := 'N';
2821 END;
2822
2823 debug_exit(l_proc_name);
2824
2825 RETURN l_formatvalid;
2826
2827 END chk_grade_format;
2828 --
2829 --
2830 --
2831 FUNCTION get_tp4_salary_scale
2832 (p_assignment_id IN NUMBER
2833 -- ,p_trace IN VARCHAR2 DEFAULT 'N'
2834 ) RETURN VARCHAR2
2835 IS
2836 l_proc_name VARCHAR2(61):= g_proc_name||'get_tp4_salary_scale';
2837
2838 l_teacher_start_date DATE; -- Teacher Start Date
2839
2840 -- QA failed due to length 30 increased to 100
2841 -- Max length based on the highest possible length of all its sources
2842 -- PAY_USER_COLUMN_INSTANCES_F VALUE VARCHAR2(80)
2843 -- FND_DESCRIPTIVE_FLEXS_VL DESCRIPTIVE_FLEXFIELD_NAME VARCHAR2(40)
2844 -- ALL_TAB_COLUMNS COLUMN_NAME VARCHAR2(30)
2845
2846 l_flexfield_name pay_user_column_instances_f.value%type;
2847 -- Flexfield Name
2848 l_segment_name pay_user_column_instances_f.value%type;
2849 -- Flexfield Segment Name
2850
2851 l_ret_salary_scale per_grade_definitions.segment1%type;
2852 -- Salary Scale Return Value
2853 -- Max length based on the highest of all its sources
2854 -- PER_GRADE_DEFINITIONS SEGMENTn VARCHAR2(60)
2855
2856 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
2857 l_report_asg VARCHAR2(1);
2858
2859 -- Added for salary scale changes
2860
2861 l_first_sal_code VARCHAR2(2);
2862 l_second_sal_code VARCHAR2(2);
2863 l_third_sal_code VARCHAR2(2);
2864 l_location_id NUMBER;
2865 l_exists VARCHAR2(1);
2866 l_spinal_point per_spinal_points.spinal_point%TYPE;
2867 i NUMBER;
2868 l_tp1_nested_level NUMBER;
2869 -- RET2.a
2870 l_count NUMBER; --Loop counter
2871 -- variable to store head teacher group code.
2872 l_asg_attributes csr_pqp_asg_attributes_dn%ROWTYPE;
2873 l_headteacher_grp_code NUMBER;
2874
2875 BEGIN
2876 -- The terms Salary Scale and Grade have been used interchangably
2877
2878 debug_enter(l_proc_name);
2879
2880 -- Bugfix 3073562:GAP6
2881 -- Adding this check to support reporting on secondary asgs in Type 1
2882 IF (g_extract_type = 'TP1' -- Extract type is Type 1 annual
2883 OR
2884 g_extract_type = 'TP1P' -- Extract type is Type 1 periodic
2885 ) THEN
2886 debug (l_proc_name, 10);
2887 -- Chk whether the primary is to be reported
2888 -- The l_assignment_id OUT var will hv the primary
2889 -- asg id (=p_assignment_id) if yes, otherwise it will
2890 -- hv the secondary asg id that is to be used to get sal scale
2891
2892 -- Added this line to fix issue with loggin in Type 1 after this
2893 -- function has been called
2894 l_tp1_nested_level := pqp_gb_t1_pension_extracts.g_nested_level; --115.34
2895 pqp_gb_t1_pension_extracts.g_nested_level := g_nested_level;
2896
2897 l_report_asg := pqp_gb_t1_pension_extracts.chk_report_assignment
2898 (p_assignment_id => p_assignment_id
2899 ,p_secondary_assignment_id => l_assignment_id -- OUT
2900 );
2901
2902 -- Added this line to fix issue with loggin in Type 1 after this
2903 -- function has been called
2904 pqp_gb_t1_pension_extracts.g_nested_level :=l_tp1_nested_level; --115.34
2905
2906 ELSE
2907 debug (l_proc_name, 20);
2908 l_assignment_id := p_assignment_id;
2909 END IF;
2910
2911 -- Bugfix 8407293
2912 IF (g_extract_type = 'TP4') THEN
2913 debug('p_assignment_id:'||p_assignment_id,25);
2914 l_assignment_id :=g_ext_asg_details(p_assignment_id).assignment_id;
2915 debug('l_assignment_id:'||l_assignment_id,26);
2916 END IF;
2917
2918 -- Set Teachers start date
2919
2920 l_teacher_start_date := g_ext_asg_details(l_assignment_id).start_date;
2921
2922
2923 debug ('Assignment ID: '||TO_CHAR(l_assignment_id),30);
2924 debug ('Start Date: '||TO_CHAR(l_teacher_start_date, 'DD/MM/YYYY'));
2925
2926 -- Step 1 : Check for the Safeguarded Salary Scale
2927 IF TRIM(g_ext_asg_details(l_assignment_id).tp_safeguarded_grade)
2928 IS NOT NULL
2929 THEN
2930 -- 1
2931
2932 debug(l_proc_name,40);
2933
2934 -- Step 2 : Fetch Sageguarded Grade found in PQP_ASSIGNMENT_ATTRIBUTES_F
2935 l_ret_salary_scale :=
2936 g_ext_asg_details(l_assignment_id).tp_safeguarded_grade;
2937
2938 ELSE -- 1 Salary Scale not found in PQP_ASSIGNMENT_ATTRIBUTES_F
2939
2940 debug(l_proc_name,50);
2941
2942 -- Step 3 : Get Flexfield Name and Segment Name from User Table
2943 l_flexfield_name := get_extract_udt_info
2944 (p_udt_column_name =>
2945 'Attribute Location Qualifier 1'
2946 ,p_udt_row_name => 'Salary Scale'
2947 );
2948 debug ('l_flexfield_name: '||l_flexfield_name,60);
2949 l_segment_name := get_extract_udt_info
2950 (p_udt_column_name => 'Attribute Location Qualifier 2'
2951 ,p_udt_row_name => 'Salary Scale'
2952 );
2953
2954 debug ('l_segment_name: '||l_segment_name,70);
2955
2956 -- Step 4 : Get Salary scale from key flexfield
2957 l_ret_salary_scale := get_kflex_value
2958 (p_context_id => l_assignment_id
2959 ,p_flexfield_name => l_flexfield_name
2960 ,p_segment_name => l_segment_name
2961 ,p_effective_date => l_teacher_start_date
2962 );
2963 debug ('l_ret_salary_scale: '||l_ret_salary_scale,80);
2964
2965 END IF; -- 1
2966
2967 -- Check that the salary grade is of a valid format
2968 debug(l_proc_name||' :Before Check Format',90);
2969
2970 IF TRIM(l_ret_salary_scale) IS NULL THEN
2971
2972 l_ret_salary_scale := 'UNKNOWN';
2973
2974 ELSIF chk_grade_format(l_ret_salary_scale) = 'N' THEN
2975 -- Added changes to fetch the salary scale information
2976 -- based on management and retention allowance information
2977 debug(l_proc_name,110);
2978
2979 l_first_sal_code := SUBSTR(l_ret_salary_scale, 1, 1);
2980
2981 debug('First Sal Code: '||l_first_sal_code,120);
2982
2983 -- Check whether the first letter matches with Qualified or Post
2984 -- Threshold Teachers salary scale
2985
2986 IF l_first_sal_code IN ('W', 'P') THEN
2987
2988 debug(l_proc_name,130);
2989
2990 -- Initialize the second and third digit sal code variables
2991 -- to zero
2992
2993 l_second_sal_code := '0';
2994 l_third_sal_code := '0';
2995
2996 -- Check whether the salary scale represents the safeguraded one
2997
2998 IF g_ext_asg_details(l_assignment_id).tp_safeguarded_grade IS NOT NULL
2999 THEN
3000 debug(l_proc_name,140);
3001 l_second_sal_code := '0';
3002 l_third_sal_code := '0';
3003
3004 ELSE -- safeguarded information not provided
3005
3006 debug(l_proc_name,150);
3007 -- Get the element entries effective for this assignment
3008 -- loop through the management allowance element collection first
3009 i := g_tab_mng_aln_eles.FIRST;
3010 l_count := 1; -- initialize the lop counter..
3011
3012 WHILE i IS NOT NULL
3013 LOOP
3014 OPEN csr_ele_entry_exists (l_assignment_id
3015 ,g_tab_mng_aln_eles(i).element_type_id
3016 ,l_teacher_start_date
3017 );
3018 FETCH csr_ele_entry_exists INTO l_exists;
3019 IF csr_ele_entry_exists%FOUND THEN
3020 debug('Management Element Type: '||TO_CHAR(i), 160+l_count/100);
3021 l_second_sal_code
3022 := TO_CHAR(g_tab_mng_aln_eles(i).salary_scale_code);
3023 CLOSE csr_ele_entry_exists;
3024 EXIT;
3025 END IF; -- End if of row found check ...
3026 CLOSE csr_ele_entry_exists;
3027 i := g_tab_mng_aln_eles.NEXT(i);
3028 l_count := l_count + 1;
3029 END LOOP;
3030 debug(l_proc_name, 170);
3031
3032
3033 -- TLR :
3034 -- Third Sal Code reporting
3035 -- 1) if l_teacher_start_date >= 01-01-2006 -> report TLR code
3036 -- 2) if l_teacher_start_date < 01-01-2006 -> report Retention code
3037 -- 2-a) if l_teacher_start_date < 01-04-2004, report 1-5 Retension Code
3038 -- 2-b) if l_teacher_start_date >= 01-04-2004, report 0/1 Retension Code
3039
3040 -- This is Step (1)
3041 IF l_teacher_start_date >= to_date('01-01-2006','DD-MM-YYYY')
3042 THEN -- calculate third sal code using TLR g_tab_tlr_aln_eles
3043
3044 -- loop through the TLR allowance element collection
3045 i := g_tab_tlr_aln_eles.FIRST;
3046 l_count := 1; -- initialize the lop counter..
3047
3048 WHILE i IS NOT NULL
3049 LOOP
3050 OPEN csr_ele_entry_exists (l_assignment_id
3051 ,g_tab_tlr_aln_eles(i).element_type_id
3052 ,l_teacher_start_date
3053 );
3054 FETCH csr_ele_entry_exists INTO l_exists;
3055 IF csr_ele_entry_exists%FOUND THEN
3056 debug('TLR Element Type: '||TO_CHAR(i),180+l_count/100);
3057 debug('salary_scale_code : '|| TO_CHAR(g_tab_tlr_aln_eles(i).salary_scale_code));
3058
3059 l_third_sal_code
3060 := TO_CHAR(g_tab_tlr_aln_eles(i).salary_scale_code);
3061 CLOSE csr_ele_entry_exists;
3062 EXIT;
3063 END IF; -- End if of row found check ...
3064 CLOSE csr_ele_entry_exists;
3065 i := g_tab_tlr_aln_eles.NEXT(i);
3066 l_count := l_count + 1;
3067 END LOOP;
3068
3069 debug(l_proc_name, 180);
3070
3071 ELSE -- before 01/jan/2006, calculate third sal code using retention allowance
3072 -- This is Step (2)
3073
3074 -- loop through the retention allowance element collection
3075 i := g_tab_ret_aln_eles.FIRST;
3076 l_count := 1; -- initialize the lop counter..
3077
3078 WHILE i IS NOT NULL
3079 LOOP
3080 OPEN csr_ele_entry_exists (l_assignment_id
3081 ,g_tab_ret_aln_eles(i).element_type_id
3082 ,l_teacher_start_date
3083 );
3084 FETCH csr_ele_entry_exists INTO l_exists;
3085 IF csr_ele_entry_exists%FOUND THEN
3086 debug('Retention Element Type: '||TO_CHAR(i),180+l_count/100);
3087 debug('salary_scale_code : '|| TO_CHAR(g_tab_ret_aln_eles(i).salary_scale_code));
3088
3089 l_third_sal_code
3090 := TO_CHAR(g_tab_ret_aln_eles(i).salary_scale_code);
3091 CLOSE csr_ele_entry_exists;
3092 EXIT;
3093 END IF; -- End if of row found check ...
3094 CLOSE csr_ele_entry_exists;
3095 i := g_tab_ret_aln_eles.NEXT(i);
3096 l_count := l_count + 1;
3097 END LOOP;
3098
3099 debug(l_proc_name, 190);
3100 END IF; -- l_teacher_start_date >= to_date('01-01-2006','DD-MM-YYYY')
3101
3102 END IF; -- End if of safeguarded information specified check ...
3103
3104 -- RET2.a
3105 -- Check the g_pension_year_start_date
3106 -- IF g_pension_year_start_date > = 01-APR-2004 AND Retention Allowance is being paid THEN
3107 -- Override the value for Retention Allowances code by '1' .
3108 -- END IF;
3109
3110 debug('g_pension_year_start_date: '
3111 ||to_char(pqp_gb_t1_pension_extracts.g_pension_year_start_date,
3112 'DD/MM/YYYY'), 210);
3113 debug('l_third_sal_code: '||l_third_sal_code);
3114
3115
3116 -- This is Step 2-a and 2-b.
3117 -- At this point of time, if l_teacher_start_date > 01-01-2006, then this code is TLR code
3118 -- If l_teacher_start_date < 01-01-2006, it has retention code, now check if
3119 -- if >= 01-04-2004, then override
3120 -- else report retention code
3121
3122 IF pqp_gb_t1_pension_extracts.g_pension_year_start_date >= to_date('01-04-2004','DD-MM-YYYY')
3123 AND l_teacher_start_date < to_date('01-01-2006','DD-MM-YYYY') -- => it is TLR code
3124 AND l_third_sal_code <> '0' THEN
3125 debug(l_proc_name, 220);
3126 -- Override the retention allowance code by '1'.
3127 l_third_sal_code := '1' ;
3128 END IF;
3129
3130 debug('Third Sal Code: ' || l_third_sal_code, 230);
3131 l_ret_salary_scale := l_first_sal_code ||
3132 l_second_sal_code ||
3133 l_third_sal_code;
3134
3135 debug('l_ret_salary_scale: ' || l_ret_salary_scale);
3136
3137 ELSIF l_first_sal_code = 'H' -- Head Teacher
3138 THEN
3139 debug(l_proc_name,240);
3140 l_second_sal_code := NULL;
3141 l_location_id := NULL;
3142
3143 -- Check whether the salary scale represents the safeguraded one
3144
3145 IF g_ext_asg_details(l_assignment_id).tp_safeguarded_grade IS NOT NULL
3146 THEN
3147 debug(l_proc_name,250);
3148 l_second_sal_code := '01';
3149
3150 -- -- Get the location id information from the global
3151 -- l_location_id
3152 -- := g_ext_asg_details(p_assignment_id).tp_sg_location_id;
3153
3154 ELSE -- safeguarded salary information not provided
3155 debug(l_proc_name,260);
3156
3157 -- SSC: If a head teacher group code is defined for the teacher
3158 -- get the salary scale code from the group code.
3159 -- else get it from the location attached to the assignment
3160
3161 OPEN csr_pqp_asg_attributes_dn
3162 ( p_assignment_id => p_assignment_id
3163 ,p_effective_date => l_teacher_start_date
3164 );
3165 FETCH csr_pqp_asg_attributes_dn INTO l_asg_attributes;
3166
3167 IF csr_pqp_asg_attributes_dn%FOUND THEN
3168 debug(l_proc_name,265);
3169 l_headteacher_grp_code := l_asg_attributes.tp_headteacher_grp_code ;
3170 END IF ;
3171
3172 CLOSE csr_pqp_asg_attributes_dn;
3173
3174
3175 debug('l_headteacher_grp_code: '||to_char(l_headteacher_grp_code), 270) ;
3176
3177 IF l_headteacher_grp_code IS NOT NULL THEN
3178 l_second_sal_code := lpad((to_char(l_headteacher_grp_code)),2,'0') ;
3179
3180 ELSE -- get teh location from the assignment
3181 l_location_id := g_ext_asg_details(l_assignment_id).location_id;
3182
3183 debug('l_location_id: '||to_char(l_location_id), 275) ;
3184
3185 IF g_criteria_estbs.EXISTS(l_location_id) THEN
3186
3187 debug('school_number: '||g_criteria_estbs(l_location_id).school_number, 280) ;
3188 l_second_sal_code := TRIM(g_criteria_estbs(l_location_id).school_number);
3189
3190 END IF; --g_criteria_estbs.EXISTS(l_location_id) THEN
3191
3192 END IF; -- l_headteacher_grp_code IS NOT NULL THEN
3193
3194 END IF; -- End if of safeguarded information provided check ...
3195
3196 debug('Second Sal Code: ' || l_second_sal_code,310);
3197
3198 l_ret_salary_scale := l_first_sal_code ||
3199 l_second_sal_code;
3200
3201
3202 debug('l_ret_salary_scale: ' || l_ret_salary_scale,320);
3203
3204 ELSIF l_first_sal_code = 'A' -- Advanced Skilled Teacher
3205 THEN
3206 debug(l_proc_name,330);
3207 l_second_sal_code := NULL;
3208 l_spinal_point := NULL;
3209
3210 -- Check whether the salary scale represents the safeguraded one
3211
3212 IF g_ext_asg_details(l_assignment_id).tp_safeguarded_grade IS NOT NULL
3213 THEN
3214 debug(l_proc_name,340);
3215 -- Get the spinal point ID information
3216 OPEN csr_get_sf_spinal_point
3217 (g_ext_asg_details(l_assignment_id).tp_sf_spinal_point_id);
3218 FETCH csr_get_sf_spinal_point INTO l_spinal_point;
3219 CLOSE csr_get_sf_spinal_point;
3220 ELSE -- safeguarded information not specified
3221 debug(l_proc_name,350);
3222 -- Get spinal point id from per_spinal_points
3223 OPEN csr_get_spinal_point (l_assignment_id
3224 ,l_teacher_start_date
3225 );
3226 FETCH csr_get_spinal_point INTO l_spinal_point;
3227 CLOSE csr_get_spinal_point;
3228
3229 END IF; -- End if of safeguarded grade specified check ...
3230
3231 IF l_spinal_point IS NOT NULL THEN
3232 debug(l_proc_name,360);
3233 l_second_sal_code := TRIM(TO_CHAR((TO_NUMBER(l_spinal_point) - 1), '09'));
3234
3235
3236 END IF; -- End if of spinal point not null check ...
3237 debug('l_second_sal_code: ' || l_second_sal_code,370);
3238
3239 l_ret_salary_scale := l_first_sal_code ||
3240 l_second_sal_code;
3241
3242 debug('l_ret_salary_scale: ' || l_ret_salary_scale,380);
3243
3244
3245 END IF; -- End if of first sal code in W or P check ...
3246
3247 IF chk_grade_format(l_ret_salary_scale) = 'N' THEN
3248 debug(l_proc_name,390);
3249 l_ret_salary_scale := 'INVALID';
3250 END IF;
3251
3252 debug('Return Salary Scale: '||l_ret_salary_scale,410);
3253
3254 END IF;
3255
3256 debug('Return Salary Scale: '||l_ret_salary_scale,420);
3257
3258 debug_exit(l_proc_name);
3259
3260 -- Step 5 : Return Salary Scale / Grade value
3261 RETURN l_ret_salary_scale;
3262
3263 END get_tp4_salary_scale;
3264 --
3265 --
3266 --
3267 FUNCTION get_total_number_data_records
3268 (p_type IN VARCHAR2
3269 -- ,p_trace IN VARCHAR2 DEFAULT 'N'
3270 ) RETURN VARCHAR2
3271 IS
3272
3273 l_proc_name VARCHAR2(61):= g_proc_name||'get_total_number_data_records';
3274
3275 l_ext_rcd_id ben_ext_rcd.ext_rcd_id%TYPE;
3276
3277 CURSOR count_extract_details
3278 (p_ext_rcd_id ben_ext_rcd.ext_rcd_id%TYPE)
3279 IS
3280 SELECT COUNT(*)
3281 FROM ben_ext_rslt_dtl dtl
3282 --,ben_ext_rcd rcd
3283 WHERE dtl.ext_rslt_id = ben_ext_thread.g_ext_rslt_id
3284 --AND rcd.ext_rcd_id = dtl.ext_rcd_id
3285 --AND rcd.rcd_type_cd = 'D'
3286 AND dtl.ext_rcd_id = p_ext_rcd_id
3287 AND DECODE(NVL(TRIM(p_type),hr_api.g_varchar2)
3288 ,hr_api.g_varchar2,hr_api.g_varchar2
3289 ,dtl.val_01
3290 ) = NVL(TRIM(p_type),hr_api.g_varchar2)
3291 AND dtl.val_01 <> 'DELETE';
3292
3293 l_count NUMBER:= 0;
3294 l_count_099999 VARCHAR2(6):= '000000';
3295
3296 BEGIN
3297
3298 debug_enter(l_proc_name);
3299
3300 -- 11.5.10_CU2: Performance fix :
3301 -- get the ben_ext_rcd.ext_rcd_id
3302 -- and use this one for next cursor
3303 -- This will prevent FTS on the table.
3304
3305 OPEN pqp_gb_t1_pension_extracts.csr_ext_rcd_id
3306 (p_hide_flag => 'N'
3307 ,p_rcd_type_cd => 'D'
3308 );
3309 FETCH pqp_gb_t1_pension_extracts.csr_ext_rcd_id INTO l_ext_rcd_id;
3310 CLOSE pqp_gb_t1_pension_extracts.csr_ext_rcd_id ;
3311
3312 debug('l_ext_rcd_id: '|| l_ext_rcd_id, 10) ;
3313
3314
3315 OPEN count_extract_details (p_ext_rcd_id => l_ext_rcd_id );
3316 FETCH count_extract_details INTO l_count;
3317
3318 debug('l_count: '|| l_count, 10) ;
3319
3320 IF l_count < 999999 THEN
3321
3322 l_count_099999 := TRIM(TO_CHAR(l_count,'099999'));
3323
3324 ELSE
3325
3326 l_count_099999 := '999999';
3327
3328 END IF;
3329 CLOSE count_extract_details;
3330
3331 debug('l_count: '|| l_count, 20) ;
3332
3333 debug_exit(l_proc_name);
3334
3335 RETURN l_count_099999;
3336
3337 END get_total_number_data_records;
3338
3339 --
3340 -- ----------------------------------------------------------------------------
3341 -- |---------------------< assignment_has_a_starter_event >--------------------|
3342 -- ----------------------------------------------------------------------------
3343 --
3344
3345 FUNCTION assignment_has_a_starter_event
3346 (p_business_group_id IN NUMBER
3347 -- ,p_effective_date IN DATE
3348 ,p_assignment_id IN NUMBER
3349 ,p_pqp_asg_attributes OUT NOCOPY csr_pqp_asg_attributes_dn%ROWTYPE
3350 ,p_asg_details OUT NOCOPY csr_asg_details%ROWTYPE
3351 ,p_teacher_start_date OUT NOCOPY DATE
3352 ) RETURN VARCHAR2 -- 'Y' or 'N'
3353 IS
3354
3355 l_inclusion_flag VARCHAR2(1):='N';
3356 l_itr NUMBER;
3357 l_location_changed BOOLEAN:= FALSE;
3358 l_teacher_start_date DATE;
3359 l_no_of_events NUMBER;
3360 idx NUMBER := 0; --Loop counter
3361 cntr NUMBER := 0; --Loop counter
3362
3363 l_asg_details csr_asg_details%ROWTYPE;
3364 l_prev_asg_details csr_asg_details%ROWTYPE;
3365 l_proration_dates pay_interpreter_pkg.t_proration_dates_table_type;
3366 l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
3367 l_pqp_asg_attributes csr_pqp_asg_attributes_dn%ROWTYPE;
3368 l_last_pqp_asg_attributes csr_pqp_asg_attributes_dn%ROWTYPE;
3369 l_pqp_aat csr_pqp_asg_attributes_up%ROWTYPE;
3370 l_event_group_details csr_event_group_details%ROWTYPE;
3371
3372 l_proc_name VARCHAR2(61):=
3373 g_proc_name||'assignment_has_a_starter_event';
3374 BEGIN
3375
3376
3377 debug_enter(l_proc_name) ;
3378
3379 -- Check if the person is a new gb starter
3380 -- check teacher flag
3381 -- at the end of this step we will know the whether
3382 -- 1. the person became teacher or not and is effective at
3383 -- any since the last run date
3384 -- 2. the person start date as a teacher
3385 -- 3. the person's location details
3386 --
3387
3388 -- Update retro status on PPE for this asg
3389 -- Bug 3015917 : Removed set_pay_proc... call, we now use new style DTI
3390
3391 -- Now invoke the date track interpreter
3392 -- Bug 3015917 : New DTI call
3393 debug('Calling pqp_utilities.get_events', 10);
3394 debug('p_assignment_id: '||p_assignment_id);
3395 debug('p_element_entry_id: NULL');
3396 debug('p_business_group_id: '||p_business_group_id);
3397 debug('p_process_mode: ENTRY_CREATION_DATE');
3398 debug('p_event_group_name: PQP_GB_TP_IS_TEACHER');
3399 debug('p_start_date: '||g_last_effective_date);
3400 debug('p_end_date: '||g_effective_run_date);
3401
3402 l_no_of_events := pqp_utilities.get_events
3403 (p_assignment_id => p_assignment_id
3404 ,p_element_entry_id => NULL
3405 ,p_business_group_id => p_business_group_id
3406 ,p_process_mode => 'ENTRY_CREATION_DATE'
3407 ,p_event_group_name => 'PQP_GB_TP_IS_TEACHER'
3408 ,p_start_date => g_last_effective_date
3409 ,p_end_date => g_effective_run_date
3410 ,t_proration_dates => l_proration_dates -- OUT
3411 ,t_proration_change_type => l_proration_changes -- OUT
3412 );
3413 debug('l_no_of_events: '||l_no_of_events, 20);
3414
3415 -- Sample Outputs
3416 -- l_proration_changes
3417 -- C
3418 -- I
3419 -- U
3420 -- C
3421 -- C
3422 ---- U
3423 -- l_proration_dates6
3424 -- 19-DEC-01
3425 -- 19-DEC-01
3426 -- 20-DEC-01
3427 -- 25-DEC-01
3428 -- 30-DEC-01
3429 -- 30-DEC-01
3430
3431
3432 -- Now search in the marked events for change in teacher job status
3433 -- that caused the asg to "become" a teacher
3434 -- ie search for a change from NULL/NONT to TCHR
3435
3436 debug('Number of IS_TEACHER Events: '||
3437 fnd_number.number_to_canonical(l_proration_dates.COUNT),30);
3438 debug('Number of t_proration_change_type: '||
3439 fnd_number.number_to_canonical(l_proration_changes.COUNT),40);
3440
3441 l_itr := l_proration_dates.FIRST;
3442 debug('l_itr: '||l_itr, 50);
3443 WHILE l_itr <= l_proration_dates.LAST
3444 LOOP
3445 --
3446 idx := idx + 1 ; --Loop Counter
3447 debug('l_itr: '||l_itr, 60 + idx/100000);
3448
3449 IF l_itr = l_proration_dates.FIRST -- eliminate duplicate dates
3450 OR
3451 ( l_proration_dates(l_itr) <>
3452 l_proration_dates(l_proration_dates.PRIOR(l_itr))
3453 )
3454 THEN
3455 -- Fetch the effective set of attributes
3456 debug('inside Eliminate duplicate Dates...', 70);
3457 debug('Open Cursor csr_pqp_asg_attributes_dn, l_proration_dates(l_itr):'||l_proration_dates(l_itr), 80);
3458
3459 OPEN csr_pqp_asg_attributes_dn
3460 (p_assignment_id
3461 ,l_proration_dates(l_itr)
3462 );
3463 FETCH csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
3464 IF csr_pqp_asg_attributes_dn%FOUND
3465 AND l_pqp_asg_attributes.tp_is_teacher = 'TCHR'
3466 THEN
3467 -- Fetch the previous set of attributes
3468 debug('Assignment Attributes Details ', 90) ;
3469 print_debug_asg_atr (l_pqp_asg_attributes);
3470
3471 FETCH csr_pqp_asg_attributes_dn INTO l_last_pqp_asg_attributes;
3472 IF csr_pqp_asg_attributes_dn%NOTFOUND -- Insert
3473 OR
3474 l_last_pqp_asg_attributes.tp_is_teacher = 'NONT' -- Update
3475 THEN
3476 --
3477 debug('This assignment HAS become a teacher.', 110);
3478 print_debug_asg_atr (l_last_pqp_asg_attributes);
3479
3480 l_inclusion_flag := 'Y';
3481 l_location_changed := FALSE;
3482 l_teacher_start_date := l_pqp_asg_attributes.effective_start_date;
3483
3484 OPEN csr_asg_details
3485 (p_assignment_id
3486 ,l_teacher_start_date
3487 );
3488 FETCH csr_asg_details INTO l_asg_details;
3489 debug('l_asg_details.person_id: '||l_asg_details.person_id,120);
3490 print_debug_asg (l_asg_details) ;
3491
3492 l_asg_details.ext_emp_cat_cd
3493 := get_translate_asg_emp_cat_code
3494 (l_asg_details.asg_emp_cat_cd
3495 ,l_teacher_start_date);
3496
3497 debug('After translation : l_asg_details.ext_emp_cat_cd: '||l_asg_details.ext_emp_cat_cd,130);
3498 CLOSE csr_asg_details;
3499 CLOSE csr_pqp_asg_attributes_dn;
3500 EXIT; -- quit loop -- no need to search for other events
3501 --
3502 END IF; --csr_pqp_asg_attributes_dn%NOTFOUND
3503 --
3504 END IF; --csr_pqp_asg_attributes_dn%FOUND
3505 -- AND l_pqp_asg_attributes.tp_is_teacher = 'TCHR'
3506 CLOSE csr_pqp_asg_attributes_dn;
3507 --
3508 END IF; -- l_itr = l_proration_dates.FIRST
3509 l_itr := l_proration_dates.NEXT(l_itr);
3510 debug('at the end of loop : l_itr: '||l_itr, 140 + idx/100000);
3511 --
3512 END LOOP;
3513
3514 l_proration_dates.DELETE;
3515 l_proration_changes.DELETE;
3516
3517 -- Unmark events back to unprocessed
3518 -- Bug 3015917 : Removed set_pay_proc... call, we now use new style DTI
3519
3520 debug('l_inclusion_flag: '||l_inclusion_flag, 150 );
3521 --debug('g_criteria_estbs.EXISTS(l_asg_details.location_id):'||g_criteria_estbs.EXISTS(l_asg_details.location_id), 1700 );
3522
3523 IF l_inclusion_flag = 'N'
3524 -- AND g_estb_number <> '0000' -- MAYBE Reference Allan McMorland.
3525 -- ie the person is NOT eligibe by virtue of "becoming" a teacher
3526 OR
3527 (
3528 -- This assignment HAS become a Teacher
3529 l_inclusion_flag = 'Y'
3530
3531 AND
3532
3533 -- But, the location is not the same as the one we are reporting for.
3534 -- In this case, look for location changes within the run date range
3535 -- PS : If we donot do this check, later on when we check whether the
3536 -- location (for the assignment details found during Teacher check)
3537 -- is a criteria establishment, it will be rejected as NOT being a
3538 -- criteria establishment.
3539 NOT g_criteria_estbs.EXISTS(l_asg_details.location_id)
3540 )
3541 THEN
3542 -- serach for change in location as of that day
3543 -- but for that he must first have been a teacher from the
3544 -- effective in the extract run period.
3545
3546 debug('This assignment has NOT become a teacher.', 160);
3547 idx := 0 ; --Loop counter
3548 OPEN csr_pqp_asg_attributes_up
3549 (p_assignment_id
3550 ,g_last_effective_date
3551 );
3552 LOOP
3553 idx := idx +1;
3554 debug('inside LOOP pqp_asg_attributes ', 170+idx/100000);
3555 FETCH csr_pqp_asg_attributes_up INTO l_pqp_asg_attributes;
3556
3557 IF csr_pqp_asg_attributes_up%FOUND THEN
3558 debug('l_pqp_asg_attributes.effective_start_date: '||l_pqp_asg_attributes.effective_start_date, 180);
3559 debug('l_pqp_asg_attributes.effective_end_date: '||l_pqp_asg_attributes.effective_end_date);
3560 debug('l_pqp_asg_attributes.tp_is_teacher: '||l_pqp_asg_attributes.tp_is_teacher);
3561 debug('l_pqp_asg_attributes.creation_date: '||l_pqp_asg_attributes.creation_date);
3562 END IF;
3563
3564
3565 IF csr_pqp_asg_attributes_up%NOTFOUND
3566 OR
3567 l_pqp_asg_attributes.effective_start_date > g_effective_run_date
3568 THEN
3569 debug('EXITING From Loop', 190);
3570 EXIT;
3571
3572 END IF; -- if pqp asg not found or pqp asg started after run date
3573
3574 IF l_pqp_asg_attributes.tp_is_teacher IN ('TCHR','TTR6')
3575
3576 AND -- the assignment was created before effective date
3577 -- needed to allow that in reruns we do not see
3578 -- records which were not created then
3579
3580 l_pqp_asg_attributes.creation_date < g_effective_date
3581
3582 THEN
3583
3584 l_proration_dates.DELETE;
3585 l_proration_changes.DELETE;
3586
3587 -- Now invoke the date track interpreter
3588 -- Bug 3015917 : Removed set_pay_proc.. call, now using new style DTI
3589
3590 l_no_of_events := 0;
3591 debug('Calling pqp_utilities.get_events', 210);
3592 debug('p_assignment_id: '||p_assignment_id);
3593 debug('p_element_entry_id: NULL');
3594 debug('p_business_group_id: '||p_business_group_id);
3595 debug('p_process_mode: ENTRY_CREATION_DATE');
3596 debug('p_event_group_name: PQP_GB_TP_IS_TEACHER');
3597 debug('p_start_date: '||g_last_effective_date);
3598 debug('p_end_date: '||g_effective_run_date);
3599
3600 l_no_of_events :=
3601 pqp_utilities.get_events
3602 (p_assignment_id => p_assignment_id
3603 ,p_element_entry_id => NULL
3604 ,p_business_group_id => p_business_group_id
3605 ,p_process_mode => 'ENTRY_CREATION_DATE'
3606 ,p_event_group_name => 'PQP_GB_TP_ASG_LOCATION'
3607 ,p_start_date => GREATEST(l_pqp_asg_attributes.effective_start_date
3608 ,g_last_effective_date)
3609 ,p_end_date => LEAST(l_pqp_asg_attributes.effective_end_date
3610 ,g_effective_run_date)
3611 ,t_proration_dates => l_proration_dates -- OUT
3612 ,t_proration_change_type => l_proration_changes -- OUT
3613 );
3614
3615 debug('Number of ASG_LOCATION Events: '||fnd_number.number_to_canonical(l_proration_dates.COUNT),220);
3616 debug('Number of Prorotaion Changes: '||fnd_number.number_to_canonical(l_proration_changes.COUNT));
3617 debug('l_no_of_events: '||l_no_of_events);
3618
3619 cntr := 0; --Loop Counter
3620 l_itr := l_proration_dates.FIRST;
3621 debug('l_itr: '||l_itr, 230);
3622 WHILE l_itr <= l_proration_dates.LAST
3623 LOOP
3624 cntr := cntr + 1 ; --Loop Counter
3625 debug('l_itr: '||l_itr, 240 + cntr/100000);
3626
3627 -- a location change event did take place, correction or update
3628 -- loop thru all the dates
3629 -- and query asg for location value
3630 -- check if location is a criteria location
3631 -- if so flag location changed and store teacher start date
3632 -- and exit else continue
3633
3634 --
3635 -- eliminate duplicate dates
3636 -- compare the last value to the current one
3637 -- always process the first date
3638 IF l_itr = l_proration_dates.FIRST
3639 OR
3640 ( l_proration_dates(l_itr) <>
3641 l_proration_dates(l_proration_dates.PRIOR(l_itr))
3642 )
3643 THEN
3644
3645 OPEN csr_asg_details
3646 (p_assignment_id
3647 ,l_proration_dates(l_itr)
3648 );
3649 FETCH csr_asg_details INTO l_asg_details;
3650 --
3651 -- Loc Change
3652 -- Effective 3
3653 -- Loc Change |
3654 -- Loc Change Effective 2 |
3655 --- Effective 1 | |
3656 -- | | |
3657 -----ASG-------|<-------------|--|------------->|------->
3658 -- |
3659 -- |
3660 -----PQP--------N----------|--|---Y---|----|--N---------->
3661 -- | | | |
3662 -- | | | |
3663 -- | | Loc Change |
3664 -- | | Actual |
3665 -- | | |
3666 -- | TCHR |
3667 -- | Effective |
3668 -- | |
3669 -- | |
3670 -- | |
3671 -- Last This
3672 -- Run Run
3673 --
3674 --
3675
3676 IF csr_asg_details%FOUND
3677
3678 -- the location is a criteria location
3679 AND
3680
3681 g_criteria_estbs.EXISTS(l_asg_details.location_id)
3682
3683 AND -- the location should have started before the TCHR ended
3684
3685 l_asg_details.start_date
3686 <= l_pqp_asg_attributes.effective_end_date
3687
3688 AND -- the location should have ended after the TCHR started
3689
3690 l_asg_details.effective_end_date
3691 >= l_pqp_asg_attributes.effective_start_date
3692
3693 THEN
3694
3695
3696 -- need to check if it was an establishment number change
3697 -- from the last period
3698
3699 debug('This assignment has HAD a change in location.', 250);
3700
3701 -- fetch the previous asg history row
3702 -- to see if its a valid
3703
3704 FETCH csr_asg_details INTO l_prev_asg_details;
3705 IF (csr_asg_details%NOTFOUND -- correction of first asg row
3706
3707 AND
3708 (
3709 -- For an existing Teacher (TCHR)
3710 l_pqp_asg_attributes.tp_is_teacher = 'TCHR'
3711
3712 OR
3713
3714 (-- The assignment is a new Teacher and has a TR6 raised
3715 l_pqp_asg_attributes.tp_is_teacher = 'TTR6'
3716
3717 AND
3718
3719 -- IF the location started on the same day as the
3720 -- assignment became a TTR6 Teacher, we DON'T report this.
3721 -- We only report a location change if it happened
3722 -- on a date later than the assignment became a TR6 Teacher.
3723 l_asg_details.start_date
3724 > l_pqp_asg_attributes.effective_start_date
3725 )
3726
3727 )
3728 )
3729
3730 OR -- changed from a non estb or a non criteria estb
3731
3732 (
3733 csr_asg_details%FOUND
3734
3735 AND
3736
3737 NOT g_criteria_estbs.EXISTS(l_prev_asg_details.location_id)
3738 )
3739
3740 OR -- changed from another criteria estb and has a diff number
3741
3742 (csr_asg_details%FOUND
3743
3744 AND
3745
3746 g_criteria_estbs.EXISTS(l_prev_asg_details.location_id )
3747
3748 AND
3749
3750 g_criteria_estbs(l_asg_details.location_id).estb_number <>
3751 g_criteria_estbs(l_prev_asg_details.location_id).estb_number
3752
3753 AND -- ignore change for estbs reporting thru the same LEA
3754 -- note we do not explicilt check for "same LEA"
3755 -- since that is guranteed by locations being in the
3756 -- same business group, we check for a change in
3757 -- the reporting thru lea yes-no flag
3758 -- change between two independent establishments
3759 -- is an acceptable change of establishments
3760
3761 (g_criteria_estbs(l_asg_details.location_id).lea_estb_yn <>
3762 g_criteria_estbs(l_prev_asg_details.location_id).lea_estb_yn
3763
3764 OR
3765
3766 (g_criteria_estbs(l_asg_details.location_id).lea_estb_yn
3767 = 'N'
3768
3769 AND
3770
3771 g_criteria_estbs(l_asg_details.location_id).lea_estb_yn
3772 = g_criteria_estbs(l_prev_asg_details.location_id).lea_estb_yn
3773
3774 ) -- or change of ind schools,lea_yn is N, ie N->N change
3775
3776 ) -- "reporting thru lea" is diff (ie Y->N or N->Y)
3777
3778 ) -- estb exists and estb numbers are diff and
3779 THEN
3780
3781 debug('This assignment has HAD a change in establishment.',260);
3782
3783 l_location_changed := TRUE;
3784
3785 l_inclusion_flag := 'Y';
3786
3787 l_teacher_start_date :=
3788 GREATEST -- of location start or teacher start
3789 (l_asg_details.start_date
3790 ,l_pqp_asg_attributes.effective_start_date
3791 );
3792 debug('l_teacher_start_date: '||l_teacher_start_date,270);
3793
3794 CLOSE csr_asg_details;
3795 EXIT; -- the asg details loop
3796
3797 END IF; -- if the location change is a estb change also
3798
3799 END IF; -- if location change is valid
3800 CLOSE csr_asg_details;
3801
3802 END IF; -- if this date <> last date to eliminate duplicates
3803 l_itr := l_proration_dates.NEXT(l_itr);
3804 debug('l_itr: '||l_itr,280);
3805 --
3806 END LOOP; -- location change proration dates
3807
3808 l_proration_dates.DELETE;
3809 l_proration_changes.DELETE;
3810
3811 -- AUTONOMOUS TRANSACTION
3812 -- Unmark events to unprocessed
3813 -- Bug 3015917 : Removed set_pay_proc... as we now use new style DTI
3814
3815 END IF; -- if pqp asg is a tchr and was created before effective date
3816
3817
3818 IF l_pqp_asg_attributes.effective_end_date > g_effective_run_date
3819
3820 OR -- or a valid location change has been found
3821 -- we only report the first location change in that period
3822
3823 l_location_changed = TRUE
3824
3825 THEN
3826
3827 EXIT; -- the pqp asg loop
3828
3829 END IF; -- if this was the last pqp asg effective in the run period
3830
3831
3832 END LOOP; -- pqp asg attributes
3833 CLOSE csr_pqp_asg_attributes_up;
3834
3835 -- ELSE -- FYI Only
3836 -- person has a new teacher event so no need to check for location changes
3837
3838 END IF; -- l_incl = N ie person did not "become" a new teacher
3839
3840
3841 IF l_inclusion_flag = 'Y' THEN
3842
3843 -- yes person has become a teacher or a exitsing teacher has changed locations
3844 -- but we do not know if the persons location is one of the criteria ones
3845 -- so by default exclude the person
3846 -- note this check is redundant for existing teachers who have had a location
3847 -- change since we have allready checked that the location is one of the
3848 -- criteria estbs
3849 --
3850
3851 l_inclusion_flag := 'N';
3852
3853 debug('Checking asg details for criteria establishment match.',290);
3854 debug('location_id: '||l_asg_details.location_id);
3855
3856
3857 -- IF location_changed -- requery pqp asg to get attribs as of tchr start
3858 -- THEN
3859 -- OPEN csr_pqp_asg_attributes_up
3860 -- (p_assigment_id
3861 -- ,
3862 --
3863 -- END IF;
3864
3865 -- check to see if he belongs to one of the criteria establsihments
3866
3867 IF g_criteria_estbs.EXISTS(l_asg_details.location_id) THEN
3868
3869 debug('This assignment HAS a valid criteria estbalishment.');
3870 debug('Estb type: '||g_criteria_estbs(l_asg_details.location_id).estb_type, 310);
3871 -- The persons location is one of the criteria estb
3872 -- Now evaluate criteria specific to the estb type
3873 -- of the assignments criteria
3874 -- NOTE the default is to exclude the person
3875 -- so we only evaluate inclusion criteria for each estb type
3876
3877 IF g_criteria_estbs(l_asg_details.location_id).estb_type = 'LEA_ESTB'
3878 THEN
3879
3880 l_inclusion_flag := 'Y';
3881
3882 ELSIF g_criteria_estbs(l_asg_details.location_id).estb_type = 'HGR_ESTB'
3883 THEN
3884
3885 debug('Checking employment category code for HGR_ESTBs.');
3886 debug('l_asg_details.ext_emp_cat_cd: '||l_asg_details.ext_emp_cat_cd, 320);
3887 IF l_asg_details.ext_emp_cat_cd = 'P' THEN
3888
3889 -- include part-timers only if pension elected
3890
3891 debug('Checking pension elected for part timers in HGR_ESTBs.');
3892 debug('tp_elected_pension: '||l_pqp_asg_attributes.tp_elected_pension, 330);
3893
3894 IF l_pqp_asg_attributes.tp_elected_pension = 'Y' THEN
3895 l_inclusion_flag := 'Y';
3896 END IF;
3897
3898 ELSE
3899 -- include all full-timers
3900 l_inclusion_flag := 'Y';
3901
3902 END IF;
3903 ELSIF g_criteria_estbs(l_asg_details.location_id).estb_type = 'IND_ESTB'
3904 THEN
3905
3906 -- regardless of employment category inlcude only if pension elected
3907 debug('Checking pension elected in IND_ESTBs.');
3908 debug('tp_elected_pension: '||l_pqp_asg_attributes.tp_elected_pension, 340);
3909
3910 IF l_pqp_asg_attributes.tp_elected_pension = 'Y' THEN
3911 l_inclusion_flag := 'Y';
3912 ELSE
3913 -- Bugfix(Enhancement ) : 2264062
3914 -- Added this ELSE part has a bugix enhancement
3915 -- Requirement : The start date for a new appointment at a
3916 -- 'Voluntary' establishment should be the date the teacher
3917 -- joined the pension scheme (this is currently set as the
3918 -- date the teacher joined the voluntary establishment).
3919
3920 -- Find assignment attributes between Teacher start date
3921 -- and g_effective_run_date where the elected pension flag
3922 -- has become Y.
3923 debug('Checking if the flag became Y during the reporting period.', 350);
3924
3925 OPEN csr_pqp_asg_attributes_up
3926 (p_assignment_id
3927 ,l_teacher_start_date
3928 );
3929
3930 LOOP
3931 --
3932 FETCH csr_pqp_asg_attributes_up INTO l_pqp_aat;
3933 --
3934 IF csr_pqp_asg_attributes_up%NOTFOUND
3935 OR
3936 l_pqp_asg_attributes.effective_start_date > g_effective_run_date THEN
3937
3938 EXIT;
3939
3940 ELSIF l_pqp_aat.tp_elected_pension = 'Y' -- Has Elected Pension
3941 AND
3942 -- And flag bcame Y between teacher start date and g_effective_run_date
3943 l_pqp_aat.effective_start_date
3944 BETWEEN l_teacher_start_date
3945 AND g_effective_run_date THEN
3946
3947 l_inclusion_flag := 'Y';
3948 l_teacher_start_date := GREATEST(l_teacher_start_date
3949 ,l_pqp_aat.effective_start_date
3950 );
3951 EXIT;
3952 --
3953 END IF;
3954 --
3955 END LOOP;
3956 --
3957 CLOSE csr_pqp_asg_attributes_up;
3958 --
3959 END IF; -- l_pqp_asg_attributes.tp_elected_pension = 'Y' THEN
3960 --
3961 ELSE -- No Other Estb Type is acceptable
3962
3963 l_inclusion_flag := 'N'; --FYI only , exclsuion is default
3964
3965 END IF;
3966
3967 --ELSE -- person;s locations does not belong to the critera estbs
3968
3969 --l_inclusion_flag := 'N'; -- FYI only, exclusion is default.
3970
3971 END IF;
3972 ELSE
3973
3974 debug('This assignment has NOT had a change in establishment.', 360);
3975
3976 END IF;--if l_incl = Y ie new teacher or existing teacher changed locations
3977
3978 debug(fnd_number.number_to_canonical(p_assignment_id)||
3979 ' l_inclusion_flag: '||l_inclusion_flag, 370);
3980
3981 -- set OUT variables....
3982 p_asg_details := l_asg_details;
3983 p_teacher_start_date := l_teacher_start_date ;
3984 p_pqp_asg_attributes := l_pqp_asg_attributes ;
3985
3986 debug_exit(l_proc_name);
3987
3988 RETURN l_inclusion_flag;
3989
3990
3991 EXCEPTION
3992 WHEN OTHERS THEN
3993 p_pqp_asg_attributes := NULL;
3994 p_asg_details := NULL;
3995 p_teacher_start_date := NULL;
3996 debug_exit(' Others in '||l_proc_name);
3997 RAISE;
3998 END assignment_has_a_starter_event ;
3999
4000 -- The procedure checks the flag g_multi_lea_exist
4001 -- to check if there are more than one lea with the same lea numebr in tha same BG.
4002 -- This flag will be set while setting the globals. and for the first valid assignment
4003 -- warning msg will be displayed.
4004 -- Reset the flag as soon as the first warning is raised.
4005
4006 PROCEDURE warn_if_multi_lea_exist (p_assignment_id IN NUMBER)
4007 IS
4008 l_proc_name VARCHAR2(61):= 'warn_if_multi_lea_exist';
4009 l_error NUMBER;
4010
4011 BEGIN
4012 debug_enter(l_proc_name);
4013
4014 IF g_multi_lea_exist = 'Y' THEN
4015 -- Raise Warinig here
4016 --fnd_message.set_name ('BEN', 'BEN_23014_TPA_MANY_LEA');
4017 --fnd_message.set_token ('TOKEN1',g_lea_number);
4018 --fnd_message.set_token ('TOKEN2',g_token_org_name);
4019 --More than one organizations have been set up with the LEA Number <token >.
4020 --The organization <org name> was used to get the LEA Details for this report.
4021 l_error := pqp_gb_tp_extract_functions.raise_extract_warning
4022 (p_assignment_id => p_assignment_id
4023 ,p_error_text => 'BEN_94006_TPA_MANY_LEA'
4024 ,p_error_number => 94006
4025 ,p_token1 => g_lea_number
4026 ,p_token2 => g_token_org_name
4027 );
4028 debug ('raised error for many lea orgs:'||l_error);
4029 g_multi_lea_exist := 'N'; --Reset the warning flag.
4030 END IF;
4031
4032 debug_exit(l_proc_name);
4033 EXCEPTION
4034 WHEN OTHERS THEN
4035 debug_exit(' Others in '||l_proc_name);
4036 RAISE;
4037 END warn_if_multi_lea_exist ;
4038
4039 -- The procedure raises a warning if there is no Location defined for LEA
4040 -- This will set the flag g_warn_no_location to 'N'
4041 -- flag will be set while setting the globals.
4042 -- and for the first assignment only warning msg will be displayed.
4043 -- Reset the flag as soon as the first warning is raised.
4044
4045 PROCEDURE warn_if_no_loc_exist (p_assignment_id IN NUMBER)
4046 IS
4047 l_proc_name VARCHAR2(61):= 'warn_if_no_loc_exist';
4048 l_error NUMBER;
4049
4050 BEGIN
4051 debug_enter(l_proc_name);
4052
4053 IF g_warn_no_location = 'Y' THEN
4054 -- Raise Warning here
4055 --fnd_message.set_name ('PQP', 'PQP_230151_NO_LOC_MAP_ON_LEA');
4056 --fnd_message.set_token ('TOKEN1',g_lea_number);
4057 -- These is no Location defined for LEA <Token1>
4058 l_error := pqp_gb_tp_extract_functions.raise_extract_warning
4059 (p_assignment_id => p_assignment_id
4060 ,p_error_text => 'BEN_94007_NO_LOC_MAP_ON_LEA'
4061 ,p_error_number => 94007
4062 ,p_token1 => g_lea_number
4063 );
4064 debug ('raised error for no location for LEA:'||l_error);
4065 g_warn_no_location := 'N'; --reset the warning flag.
4066 END IF;
4067
4068 debug_exit(l_proc_name);
4069 EXCEPTION
4070 WHEN OTHERS THEN
4071 debug_exit(' Others in '||l_proc_name);
4072 RAISE;
4073 END warn_if_no_loc_exist ;
4074
4075 --
4076 --
4077 --
4078
4079 FUNCTION get_prev_tp4_result( p_person_id IN NUMBER )
4080 RETURN DATE
4081 IS
4082 l_prev_start_date DATE := NULL ;
4083 l_proc_name VARCHAR2(61):= 'get_prev_tp4_result';
4084 BEGIN
4085 debug_enter(l_proc_name);
4086
4087 OPEN csr_prev_tp4_results (p_person_id);
4088 FETCH csr_prev_tp4_results INTO l_prev_start_date ;
4089 CLOSE csr_prev_tp4_results ;
4090
4091 debug_exit(l_proc_name) ;
4092
4093 RETURN l_prev_start_date ;
4094 EXCEPTION
4095 WHEN OTHERS THEN
4096 debug_exit(' Others in '||l_proc_name);
4097 RAISE;
4098 END get_prev_tp4_result ;
4099
4100 --
4101 --
4102 --
4103
4104 -- DEBUG procs ....
4105 PROCEDURE print_debug_asg(p_asg_detail IN csr_asg_details%ROWTYPE)
4106 IS
4107 l_asg_details csr_asg_details%ROWTYPE ;
4108 BEGIN
4109 l_asg_details := p_asg_detail ;
4110 debug('---------ASSIGNMENT_DETAILS---------');
4111 debug('l_asg_details.person_id:'||l_asg_details.person_id);
4112 debug('l_asg_details.assignment_id:'||l_asg_details.assignment_id);
4113 debug('l_asg_details.business_group_id:'||l_asg_details.business_group_id);
4114 debug('l_asg_details.start_date:'||l_asg_details.start_date);
4115 debug('l_asg_details.effective_end_date:'||l_asg_details.effective_end_date);
4116 debug('l_asg_details.creation_date:'||l_asg_details.creation_date);
4117 debug('l_asg_details.location_id:'||l_asg_details.location_id);
4118 debug('l_asg_details.asg_emp_cat_cd:'||l_asg_details.asg_emp_cat_cd);
4119 debug('l_asg_details.ext_emp_cat_cd:'||l_asg_details.ext_emp_cat_cd);
4120 debug('l_asg_details.estb_number:'||l_asg_details.estb_number);
4121 debug('l_asg_details.status_type:'||l_asg_details.status_type);
4122 debug('l_asg_details.leaver_date:'||l_asg_details.leaver_date);
4123 debug('l_asg_details.restarter_date:'||l_asg_details.restarter_date);
4124 debug('l_asg_details.report_asg:'||l_asg_details.report_asg);
4125 END print_debug_asg;
4126
4127 --
4128 --
4129 --
4130
4131 PROCEDURE print_debug_asg_atr_up(p_pqp_asg_attributes_up IN pqp_gb_t1_pension_extracts.csr_pqp_asg_attributes_up%ROWTYPE)
4132 IS
4133 l_pqp_asg_attributes_up pqp_gb_t1_pension_extracts.csr_pqp_asg_attributes_up%ROWTYPE ;
4134 BEGIN
4135 l_pqp_asg_attributes_up := p_pqp_asg_attributes_up ;
4136 debug('---------ASSIGNMENT_ATTRIBUTES---------');
4137 debug('l_pqp_asg_attributes_up.assignment_attribute_id:'||l_pqp_asg_attributes_up.assignment_attribute_id);
4138 debug('l_pqp_asg_attributes_up.assignment_id:'||l_pqp_asg_attributes_up.assignment_id);
4139 debug('l_pqp_asg_attributes_up.effective_start_date:'||l_pqp_asg_attributes_up.effective_start_date);
4140 debug('l_pqp_asg_attributes_up.effective_end_date:'||l_pqp_asg_attributes_up.effective_end_date);
4141 debug('l_pqp_asg_attributes_up.tp_is_teacher:'||l_pqp_asg_attributes_up.tp_is_teacher);
4142 debug('l_pqp_asg_attributes_up.tp_safeguarded_grade:'||l_pqp_asg_attributes_up.tp_safeguarded_grade);
4143 debug('l_pqp_asg_attributes_up.tp_safeguarded_grade_id:'||l_pqp_asg_attributes_up.tp_safeguarded_grade_id);
4144 debug('l_pqp_asg_attributes_up.tp_safeguarded_rate_type:'||l_pqp_asg_attributes_up.tp_safeguarded_rate_type);
4145 debug('l_pqp_asg_attributes_up.tp_safeguarded_rate_id:'||l_pqp_asg_attributes_up.tp_safeguarded_rate_id);
4146 debug('l_pqp_asg_attributes_up.tp_safeguarded_spinal_point_id:'||l_pqp_asg_attributes_up.tp_safeguarded_spinal_point_id);
4147 debug('l_pqp_asg_attributes_up.tp_fast_track:'||l_pqp_asg_attributes_up.tp_fast_track);
4148 debug('l_pqp_asg_attributes_up.tp_elected_pension:'||l_pqp_asg_attributes_up.tp_elected_pension);
4149 debug('l_pqp_asg_attributes_up.creation_date:'||l_pqp_asg_attributes_up.creation_date);
4150 END print_debug_asg_atr_up ;
4151
4152 --
4153 --
4154 --
4155
4156 PROCEDURE print_debug_asg_atr(p_pqp_asg_attributes IN csr_pqp_asg_attributes_dn%ROWTYPE)
4157 IS
4158 l_pqp_asg_attributes csr_pqp_asg_attributes_dn%ROWTYPE ;
4159 BEGIN
4160 l_pqp_asg_attributes := p_pqp_asg_attributes ;
4161 debug('---------ASSIGNMENT_ATTRIBUTES---------');
4162 debug('l_pqp_asg_attributes.assignment_attribute_id:'||l_pqp_asg_attributes.assignment_attribute_id);
4163 debug('l_pqp_asg_attributes.assignment_id:'||l_pqp_asg_attributes.assignment_id);
4164 debug('l_pqp_asg_attributes.effective_start_date:'||l_pqp_asg_attributes.effective_start_date);
4165 debug('l_pqp_asg_attributes.effective_end_date:'||l_pqp_asg_attributes.effective_end_date);
4166 debug('l_pqp_asg_attributes.tp_is_teacher:'||l_pqp_asg_attributes.tp_is_teacher);
4167 --debug('l_pqp_asg_attributes.tp_safeguarded_grade:'||l_pqp_asg_attributes.tp_safeguarded_grade);
4168 --debug('l_pqp_asg_attributes.tp_safeguarded_grade_id:'||l_pqp_asg_attributes.tp_safeguarded_grade_id);
4169 -- debug('l_pqp_asg_attributes.tp_safeguarded_rate_type:'||l_pqp_asg_attributes.tp_safeguarded_rate_type);
4170 -- debug('l_pqp_asg_attributes.tp_safeguarded_rate_id:'||l_pqp_asg_attributes.tp_safeguarded_rate_id);
4171 -- debug('l_pqp_asg_attributes.tp_safeguarded_spinal_point_id:'||l_pqp_asg_attributes.tp_safeguarded_spinal_point_id);
4172 -- debug('l_pqp_asg_attributes.tp_fast_track:'||l_pqp_asg_attributes.tp_fast_track);
4173 debug('l_pqp_asg_attributes.tp_elected_pension:'||l_pqp_asg_attributes.tp_elected_pension);
4174 debug('l_pqp_asg_attributes.creation_date:'||l_pqp_asg_attributes.creation_date);
4175 END print_debug_asg_atr ;
4176
4177 FUNCTION get_allow_code_rt_ele_info (p_assignment_id IN NUMBER
4178 ,p_effective_date IN DATE
4179 ,p_table_name IN VARCHAR2
4180 ,p_row_name IN VARCHAR2
4181 ,p_column_name IN VARCHAR2
4182 ,p_tab_aln_eles IN pqp_gb_t1_pension_extracts.t_allowance_eles
4183 ,p_allowance_code IN VARCHAR2
4184 )
4185 RETURN pqp_gb_t1_pension_extracts.t_allowance_eles
4186 IS
4187 --
4188 l_proc_name VARCHAR2 (80)
4189 := g_proc_name
4190 || 'get_allow_code_rt_ele_info';
4191 l_proc_step NUMBER;
4192 l_return NUMBER;
4193 l_user_value pay_user_column_instances_f.value%TYPE;
4194 l_error_msg VARCHAR2(2000);
4195 l_element_type_id NUMBER := NULL;
4196 l_tab_allowance_eles pqp_gb_t1_pension_extracts.t_allowance_eles := p_tab_aln_eles;
4197
4198 -- RET1.a : new variables to store element_type_extra_info_id
4199 l_element_type_extra_info_id pay_element_type_extra_info.element_type_extra_info_id%type ;
4200 l_retval NUMBER;
4201 l_token VARCHAR2(80);
4202 --
4203 BEGIN
4204 --
4205 debug_enter (l_proc_name);
4206
4207 l_return := pqp_utilities.pqp_gb_get_table_value
4208 (p_business_group_id => g_business_group_id
4209 ,p_effective_date => p_effective_date
4210 ,p_table_name => p_table_name
4211 ,p_column_name => p_column_name
4212 ,p_row_name => p_row_name
4213 ,p_value => l_user_value
4214 ,p_error_msg => l_error_msg
4215 );
4216 --
4217 IF l_return <> -1
4218 THEN
4219 --
4220 IF l_user_value IS NOT NULL THEN
4221
4222 -- fetch the element type id information
4223 -- for this rate type the rate type validation
4224 -- is already added in the UDT so no need to
4225 -- check for validation again
4226
4227 debug ('User Value: '
4228 || l_user_value, 10);
4229
4230 --
4231 OPEN csr_get_eles_frm_rate (p_effective_date
4232 ,l_user_value
4233 );
4234 LOOP
4235 FETCH csr_get_eles_frm_rate INTO l_element_type_id;
4236 EXIT WHEN csr_get_eles_frm_rate%NOTFOUND;
4237
4238 l_tab_allowance_eles (l_element_type_id).element_type_id
4239 := l_element_type_id;
4240 l_tab_allowance_eles (l_element_type_id).salary_scale_code
4241 := p_allowance_code;
4242
4243 debug ('Element Type ID: '
4244 || TO_CHAR(l_element_type_id),40);
4245 END LOOP; -- End loop of eles from rate cursor...
4246 CLOSE csr_get_eles_frm_rate;
4247 --
4248
4249 --
4250 IF l_tab_allowance_eles.COUNT = 0 THEN
4251
4252 debug_exit(l_proc_name);
4253
4254 -- Raise an error for no element are associated
4255 -- with this rate type
4256
4257 l_return := pqp_gb_tp_extract_functions.raise_extract_error
4258 (p_business_group_id => g_business_group_id
4259 ,p_assignment_id => p_assignment_id
4260 ,p_error_text =>'BEN_93640_EXT_TP_NO_ELE_FOR_RT'
4261 ,p_error_number => 93640 );
4262
4263 END IF; -- End if of element type count = 0 check ...
4264 --
4265 END IF; -- End if of user value is not null check ...
4266 --
4267
4268 ELSE -- Else return = -1 from get table value function
4269
4270 debug_exit(l_proc_name);
4271
4272 fnd_message.set_name ('PQP', 'PQP_230661_OSP_DUMMY_MSG');
4273 fnd_message.set_token ('TOKEN', l_error_msg);
4274 fnd_message.raise_error;
4275
4276 END IF; -- End if of return <> -1 check from get table value func...
4277 --
4278
4279 debug_exit(l_proc_name);
4280
4281 RETURN l_tab_allowance_eles;
4282 --
4283 END get_allow_code_rt_ele_info;
4284 --
4285
4286 END pqp_gb_tp_pension_extracts;
[Home] [Help]