[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_CPX_EXTRACT_FUNCTIONS
Source
1 PACKAGE BODY pqp_gb_cpx_extract_functions
2 -- /* $Header: pqpgbcpx.pkb 120.8.12010000.3 2008/08/05 14:01:39 ubhat ship $ */
3 AS
4
5 --
6
7 -- ----------------------------------------------------------------------------
8 -- |--------------------------------< debug >---------------------------------|
9 -- ----------------------------------------------------------------------------
10
11 PROCEDURE DEBUG (
12 p_trace_message IN VARCHAR2,
13 p_trace_location IN NUMBER DEFAULT NULL
14 )
15 IS
16
17 --
18 l_padding VARCHAR2 (12);
19 l_max_message_length NUMBER := 72;
20
21 --
22 BEGIN
23 --
24 IF p_trace_location IS NOT NULL
25 THEN
26 l_padding := SUBSTR (
27 RPAD (' ', LEAST (g_nested_level, 5) * 2, ' '),
28 1,
29 l_max_message_length
30 - LEAST (
31 LENGTH (p_trace_message),
32 l_max_message_length
33 )
34 );
35 hr_utility.set_location (
36 l_padding
37 || SUBSTR (
38 p_trace_message,
39 GREATEST (-LENGTH (p_trace_message), -l_max_message_length)
40 ),
41 p_trace_location
42 );
43 ELSE
44 hr_utility.TRACE (SUBSTR (p_trace_message, 1, 250));
45 END IF;
46 --
47
48 END DEBUG;
49
50
51 --
52 -- ----------------------------------------------------------------------------
53 -- |--------------------------------< debug >---------------------------------|
54 -- ----------------------------------------------------------------------------
55
56 PROCEDURE DEBUG (p_trace_number IN NUMBER)
57 IS
58
59 --
60 BEGIN
61 --
62 DEBUG (fnd_number.number_to_canonical (p_trace_number));
63 --
64
65 END DEBUG;
66
67
68 --
69 -- ----------------------------------------------------------------------------
70 -- |--------------------------------< debug >---------------------------------|
71 -- ----------------------------------------------------------------------------
72
73 PROCEDURE DEBUG (p_trace_date IN DATE)
74 IS
75
76 --
77 BEGIN
78 --
79 DEBUG (fnd_date.date_to_canonical (p_trace_date));
80 --
81
82 END DEBUG;
83
84
85 -- This procedure is used for debug purposes
86 -- debug_enter checks the debug flag and sets the trace on/off
87 --
88 -- ----------------------------------------------------------------------------
89 -- |----------------------------< debug_enter >-------------------------------|
90 -- ----------------------------------------------------------------------------
91
92 PROCEDURE debug_enter (p_proc_name IN VARCHAR2, p_trace_on IN VARCHAR2)
93 IS
94 l_extract_attributes csr_pqp_extract_attributes%ROWTYPE;
95 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
96 BEGIN
97 IF g_nested_level = 0
98 THEN -- swtich tracing on/off at the top level only
99 -- Set the trace flag, but only the first time around
100 IF g_trace IS NULL
101 THEN
102 OPEN csr_pqp_extract_attributes;
103 FETCH csr_pqp_extract_attributes INTO l_extract_attributes;
104 CLOSE csr_pqp_extract_attributes;
105 l_business_group_id := fnd_global.per_business_group_id;
106
107 BEGIN
108 g_trace :=
109 hruserdt.get_table_value (
110 p_bus_group_id=> l_business_group_id,
111 p_table_name=> l_extract_attributes.user_table_name,
112 p_col_name=> 'Attribute Location Qualifier 1',
113 p_row_value=> 'Debug',
114 p_effective_date=> NULL -- don't hv the date
115 );
116 EXCEPTION
117 WHEN OTHERS
118 THEN
119 g_trace := 'N';
120 END;
121
122 g_trace := NVL (g_trace, 'N');
123 DEBUG ( 'UDT Trace Flag : '
124 || g_trace);
125 END IF; -- g_trace IS NULL THEN
126
127 IF NVL (p_trace_on, 'N') = 'Y'
128 OR g_trace = 'Y'
129 THEN
130 hr_utility.trace_on (NULL, 'REQID'); -- Pipe name REQIDnnnnnn
131 END IF; -- NVL(p_trace_on,'N') = 'Y'
132 --
133 END IF; -- if nested level = 0
134
135 g_nested_level := g_nested_level
136 + 1;
137 DEBUG (
138 'Entered: '
139 || NVL (p_proc_name, g_proc_name),
140 g_nested_level * 100
141 );
142 END debug_enter;
143
144
145 -- This procedure is used for debug purposes
146 --
147 -- ----------------------------------------------------------------------------
148 -- |----------------------------< debug_exit >--------------------------------|
149 -- ----------------------------------------------------------------------------
150
151 PROCEDURE debug_exit (p_proc_name IN VARCHAR2, p_trace_off IN VARCHAR2)
152 IS
153 BEGIN
154 DEBUG (
155 'Leaving: '
156 || NVL (p_proc_name, g_proc_name),
157 -g_nested_level * 100
158 );
159 g_nested_level := g_nested_level
160 - 1;
161
162 -- debug enter sets trace ON when g_trace = 'Y' and nested level = 0
163 -- so we must turn it off for the same condition
164 -- Also turn off tracing when the override flag of p_trace_off has been passed as Y
165 IF (g_nested_level = 0 AND g_trace = 'Y')
166 OR NVL (p_trace_off, 'N') = 'Y'
167 THEN
168 hr_utility.trace_off;
169 END IF; -- (g_nested_level = 0
170 END debug_exit;
171
172
173 -- This function sets the run dates for periodic type of extract
174 --
175 -- ----------------------------------------------------------------------------
176 -- |------------------------< set_periodic_run_dates >------------------------|
177 -- ----------------------------------------------------------------------------
178
179 FUNCTION set_periodic_run_dates (
180 p_error_number OUT NOCOPY NUMBER,
181 p_error_text OUT NOCOPY VARCHAR2
182 )
183 RETURN NUMBER
184 IS
185
186 --
187 -- Modified cursor for performance fix
188
189 CURSOR csr_last_run_details
190 (p_ext_rcd_id ben_ext_rcd.ext_rcd_id%TYPE)
191 IS
192 SELECT MAX (
193 TRUNC (rslt.eff_dt)
194 ) -- highest effective date of all prev runs
195 FROM pqp_extract_attributes pqea,
196 ben_ext_rslt rslt,
197 ben_ext_rslt_dtl rdtl
198 -- ben_ext_rcd drcd
199 WHERE pqea.ext_dfn_type = g_extract_type
200 AND rslt.ext_dfn_id = pqea.ext_dfn_id
201 AND rslt.business_group_id = g_business_group_id
202 AND rslt.ext_stat_cd NOT IN ('F' -- Job Failure
203 ,
204 'R' -- Rejected By User
205 ,
206 'X' -- Executing
207 )
208 AND rdtl.ext_rslt_id = rslt.ext_rslt_id
209 AND rdtl.ext_rcd_id = p_ext_rcd_id
210 -- AND drcd.ext_rcd_id = rdtl.ext_rcd_id
211 -- AND drcd.rcd_type_cd = 'H'
212 AND SUBSTR (
213 rdtl.val_01,
214 1,
215 INSTR (g_header_system_element, ':', 1)
216 ) = SUBSTR (
217 g_header_system_element,
218 1,
219 INSTR (g_header_system_element, ':', 1)
220 )
221 AND rslt.eff_dt < g_effective_date;
222
223 CURSOR csr_next_run_details
224 (p_ext_rcd_id ben_ext_rcd.ext_rcd_id%TYPE)
225 IS
226 SELECT MIN (
227 TRUNC (rslt.eff_dt)
228 ) -- least effective date of all future runs
229 FROM pqp_extract_attributes pqea,
230 ben_ext_rslt rslt,
231 ben_ext_rslt_dtl rdtl
232 -- ben_ext_rcd drcd
233 WHERE pqea.ext_dfn_type = g_extract_type
234 AND rslt.ext_dfn_id = pqea.ext_dfn_id
235 AND rslt.business_group_id = g_business_group_id
236 AND rdtl.ext_rslt_id = rslt.ext_rslt_id
237 AND rdtl.ext_rcd_id = p_ext_rcd_id
238 -- AND drcd.ext_rcd_id = rdtl.ext_rcd_id
239 -- AND drcd.rcd_type_cd = 'H'
240 AND SUBSTR (
241 rdtl.val_01,
242 1,
243 INSTR (g_header_system_element, ':', 1)
244 ) = SUBSTR (
245 g_header_system_element,
246 1,
247 INSTR (g_header_system_element, ':', 1)
248 )
249 AND rslt.eff_dt >= g_effective_date; -- include any runs on the same day
250
251 CURSOR csr_get_tax_year_date
252 IS
253 SELECT TO_DATE (
254 '01-04-'
255 || DECODE (
256 SIGN (
257 TO_NUMBER (TO_CHAR (g_effective_date, 'MM'))
258 - 04
259 ),
260 -1, TO_CHAR (
261 ADD_MONTHS (g_effective_date, -12),
262 'YYYY'
263 ),
264 TO_CHAR (g_effective_date, 'YYYY')
265 ),
266 'DD-MM-YYYY'
267 )
268 FROM DUAL;
269
270 l_proc_name VARCHAR2 (60)
271 := g_proc_name
272 || 'set_periodic_run_date';
273 l_initial_ext_date DATE;
274 l_value pay_user_column_instances_f.value%TYPE;
275 l_error_text VARCHAR2 (200);
276 l_ext_rcd_id NUMBER;
277 BEGIN
278 debug_enter (l_proc_name);
279 DEBUG (TO_CHAR (g_effective_date, 'DD-MON-YYYY'));
280 DEBUG (
281 'g_effective_date: '
282 || fnd_date.date_to_canonical (g_effective_date)
283 );
284 g_effective_end_date := -- "end of day" of a day before effective date
285 fnd_date.canonical_to_date (
286 TO_CHAR ( g_effective_date
287 - 1, 'YYYY/MM/DD')
288 || '23:59:59'
289 );
290 DEBUG (
291 'g_effective_end_date: '
292 || fnd_date.date_to_canonical (g_effective_end_date)
293 );
294 -- 11.5.10_CU2: Performance fix :
295 -- get the ben_ext_rcd.ext_rcd_id
296 -- and use this one for next cursor
297 -- This will prevent FTS on the table.
298
299 OPEN csr_ext_rcd_id (p_hide_flag => 'Y'
300 ,p_rcd_type_cd => 'H'
301 );
302 FETCH csr_ext_rcd_id INTO l_ext_rcd_id;
303 CLOSE csr_ext_rcd_id ;
304
305 OPEN csr_last_run_details(l_ext_rcd_id);
306 FETCH csr_last_run_details INTO g_effective_start_date;
307 DEBUG (
308 'g_effective_start_date just after fetch: '
309 || fnd_date.date_to_canonical (g_effective_start_date)
310 );
311
312 IF csr_last_run_details%NOTFOUND -- not likely ever bcos of use of MAX
313 OR g_effective_start_date IS NULL
314 THEN
315 DEBUG ('No successful last completed run was found');
316 -- Call utility function to get the UDT values
317 -- for the initial extract date information only for
318 -- Starters and Hour Change reports
319
320 DEBUG ('Get Initial Extract Date');
321 DEBUG ('Calling function pqp_gb_get_table_value');
322
323 IF pqp_utilities.pqp_gb_get_table_value (
324 p_business_group_id=> g_business_group_id,
325 p_effective_date=> g_effective_date,
326 p_table_name=> g_extract_udt_name,
327 p_column_name=> 'Initial Extract Date',
328 p_row_name=> 'Criteria Date',
329 p_value=> l_value,
330 p_error_msg=> l_error_text
331 ) <> 0
332 THEN
333 DEBUG ( 'Function in Error: '
334 || l_error_text);
335 p_error_text := l_error_text;
336 debug_exit (l_proc_name);
337 RETURN -1;
338 END IF; -- End if of function in error check ...
339 l_initial_ext_date := fnd_date.displaydate_to_date(l_value);
340
341 DEBUG ( 'Initial Extract Date: '
342 || TO_CHAR(l_initial_ext_date, 'DD-MM-YYYY'));
343 DEBUG ('End of call to function pqp_gb_get_table_value');
344
345 IF l_initial_ext_date IS NULL
346 THEN
347 -- Get tax year date
348 DEBUG ('Get Tax year date');
349 OPEN csr_get_tax_year_date;
350 FETCH csr_get_tax_year_date INTO g_initial_ext_date;
351 CLOSE csr_get_tax_year_date;
352 ELSE
353 g_initial_ext_date := l_initial_ext_date;
354 END IF; -- End if of intial extract date check ...
355
356 DEBUG ( 'Initial Extract Date: '
357 || TO_CHAR(g_initial_ext_date, 'DD-MM-YYYY'));
358 g_effective_start_date := g_initial_ext_date;
359
360 IF g_effective_start_date IS NULL
361 THEN -- use tax year first of april
362 SELECT TO_DATE (
363 '01-04-'
364 || DECODE (
365 SIGN (
366 TO_NUMBER (TO_CHAR (g_effective_date, 'MM'))
367 - 04
368 ),
369 -1, TO_CHAR (
370 ADD_MONTHS (g_effective_date, -12),
371 'YYYY'
372 ),
373 TO_CHAR (g_effective_date, 'YYYY')
374 ),
375 'DD-MM-YYYY'
376 )
377 INTO g_effective_start_date
378 FROM DUAL;
379 END IF; -- End if of g_effective_start_date is Null check ...
380 END IF; -- End if of csr_last_run_details not found check ...
381
382 IF g_effective_start_date > g_effective_end_date
383 THEN
384 -- Reduce the effective start date by a year
385 -- this can happen when the effective date is
386 -- the same as tax year date '01-04'
387 DEBUG ('Start date greater than end date - Reduce it');
388 g_effective_start_date := ADD_MONTHS (g_effective_start_date, -12);
389 END IF; -- g_effective_start_date > g_effective_end_date check ...
390
391 CLOSE csr_last_run_details;
392 DEBUG (
393 'g_effective_start_date: '
394 || fnd_date.date_to_canonical (g_effective_start_date)
395 );
396 OPEN csr_next_run_details(l_ext_rcd_id);
397 FETCH csr_next_run_details INTO g_next_effective_date;
398 CLOSE csr_next_run_details;
399 DEBUG (
400 'g_next_effective_date: '
401 || fnd_date.date_to_canonical (g_next_effective_date)
402 );
403 g_header_system_element :=
404 g_header_system_element
405 || fnd_date.date_to_canonical (g_effective_start_date)
406 || ':'
407 || fnd_date.date_to_canonical (g_effective_end_date)
408 || ':'
409 || fnd_date.date_to_canonical (g_next_effective_date)
410 || ':';
411 DEBUG ( 'g_header_system_element: '
412 || g_header_system_element);
413 debug_exit (l_proc_name);
414 RETURN 0;
415 EXCEPTION
416 WHEN OTHERS
417 THEN
418 DEBUG ( ' Others Exception'
419 || l_proc_name);
420 p_error_number := SQLCODE;
421 p_error_text := SQLERRM;
422 RAISE;
423 END set_periodic_run_dates;
424
425
426 -- This procedure sets the run dates for annual type CPX extract
427 --
428 -- ----------------------------------------------------------------------------
429 -- |------------------------< set_annual_run_dates >--------------------------|
430 -- ----------------------------------------------------------------------------
431
432 PROCEDURE set_annual_run_dates
433 IS
434 l_year NUMBER;
435 l_proc_name VARCHAR2 (61) := 'set_annual_run_dates';
436 BEGIN
437 debug_enter (l_proc_name);
438 DEBUG (TO_CHAR (g_effective_date, 'DD-MON-YYYY'));
439 DEBUG (
440 'g_effective_date: '
441 || fnd_date.date_to_canonical (g_effective_date)
442 );
443 g_effective_end_date := g_effective_date;
444 g_effective_start_date := ADD_MONTHS (( g_effective_date
445 + 1
446 ), -12);
447 DEBUG (
448 'g_effective_start_date: '
449 || fnd_date.date_to_canonical (g_effective_start_date)
450 );
451 DEBUG (
452 'g_effective_end_date: '
453 || fnd_date.date_to_canonical (g_effective_end_date)
454 );
455 g_header_system_element :=
456 g_header_system_element
457 || fnd_date.date_to_canonical (g_effective_start_date)
458 || ':'
459 || fnd_date.date_to_canonical (g_effective_end_date)
460 || ':'
461 || fnd_date.date_to_canonical (g_effective_date)
462 || ':';
463 DEBUG ( 'g_header_system_element: '
464 || g_header_system_element);
465 debug_exit (l_proc_name);
466 END set_annual_run_dates;
467
468
469 -- This function returns the input value id for a given element type id
470 -- and input value name
471 --
472 -- ----------------------------------------------------------------------------
473 -- |--------------------------< get_input_value_id >--------------------------|
474 -- ----------------------------------------------------------------------------
475
476 FUNCTION get_input_value_id (
477 p_element_type_id IN NUMBER,
478 p_input_value_name IN VARCHAR2,
479 p_effective_date IN DATE
480 )
481 RETURN NUMBER
482 IS
483
484 --
485 l_proc_name VARCHAR2 (60)
486 := g_proc_name
487 || 'get_input_value_id';
488 l_input_value_id pay_input_values_f.input_value_id%TYPE;
489
490 --
491 BEGIN
492 debug_enter (l_proc_name);
493 OPEN csr_get_pay_iv_id (
494 p_element_type_id,
495 p_input_value_name,
496 p_effective_date
497 );
498 FETCH csr_get_pay_iv_id INTO l_input_value_id;
499 CLOSE csr_get_pay_iv_id;
500 DEBUG (
501 p_input_value_name
502 || ' Input Value ID: '
503 || TO_CHAR (l_input_value_id)
504 );
505 debug_exit (l_proc_name);
506 RETURN l_input_value_id;
507 END get_input_value_id;
508
509
510 -- This function gets the balance type id for a given balance name
511 --
512 -- ----------------------------------------------------------------------------
513 -- |--------------------------< get_pay_bal_id >------------------------------|
514 -- ----------------------------------------------------------------------------
515
516 FUNCTION get_pay_bal_id (p_balance_name IN VARCHAR2)
517 RETURN NUMBER
518 IS
519
520 --
521 l_proc_name VARCHAR2 (60) := g_proc_name
522 || 'get_pay_bal_id';
523 l_bal_type_id csr_get_pay_bal_id%ROWTYPE;
524
525 --
526 BEGIN
527 debug_enter (l_proc_name);
528 OPEN csr_get_pay_bal_id (c_balance_name => p_balance_name);
529 FETCH csr_get_pay_bal_id INTO l_bal_type_id;
530 CLOSE csr_get_pay_bal_id;
531 DEBUG (
532 p_balance_name
533 || ' Balance ID: '
534 || TO_CHAR (l_bal_type_id.balance_type_id)
535 );
536 debug_exit (l_proc_name);
537 RETURN l_bal_type_id.balance_type_id;
538 END get_pay_bal_id;
539
540
541 -- This function returns the element type id's as collectionfrom the balance
542 -- accepting the balance type id
543 --
544 -- ----------------------------------------------------------------------------
545 -- |-------------------------< get_pay_ele_ids_from_bal >---------------------|
546 -- ----------------------------------------------------------------------------
547
548 FUNCTION get_pay_ele_ids_from_bal (
549 p_balance_type_id IN NUMBER,
550 p_effective_start_date IN DATE,
551 p_effective_end_date IN DATE,
552 p_tab_ele_ids OUT NOCOPY t_ele_ids_from_bal,
553 p_error_number OUT NOCOPY NUMBER,
554 p_error_text OUT NOCOPY VARCHAR2
555 )
556 RETURN NUMBER
557 IS
558
559 --
560 l_proc_name VARCHAR2 (60)
561 := g_proc_name
562 || 'get_pay_ele_ids_from_bal';
563 l_iv_ids csr_get_pay_iv_ids_from_bal%ROWTYPE;
564 l_ele_ids csr_get_pay_ele_ids_from_bal%ROWTYPE;
565 l_error_text VARCHAR2 (200);
566 l_return NUMBER := 0;
567
568 --
569 BEGIN
570 debug_enter (l_proc_name);
571 OPEN csr_get_pay_iv_ids_from_bal (
572 c_balance_type_id=> p_balance_type_id,
573 c_effective_start_date=> p_effective_start_date,
574 c_effective_end_date=> p_effective_end_date
575 );
576
577 LOOP
578 FETCH csr_get_pay_iv_ids_from_bal INTO l_iv_ids;
579 EXIT WHEN csr_get_pay_iv_ids_from_bal%NOTFOUND;
580 --
581 OPEN csr_get_pay_ele_ids_from_bal (l_iv_ids.input_value_id);
582 FETCH csr_get_pay_ele_ids_from_bal INTO l_ele_ids;
583
584 IF csr_get_pay_ele_ids_from_bal%FOUND
585 THEN
586 p_tab_ele_ids (l_ele_ids.element_type_id) := l_ele_ids;
587 END IF; -- End if of get pay ele ids found check ...
588
589 CLOSE csr_get_pay_ele_ids_from_bal;
590 --
591 END LOOP;
592
593 IF csr_get_pay_iv_ids_from_bal%ROWCOUNT = 0
594 THEN
595 DEBUG ('Balance feeds not found');
596 p_error_number := 93342;
597 p_error_text := 'BEN_93342_EXT_CPX_BAL_NOFEEDS';
598 l_return := -1;
599 END IF;
600
601 CLOSE csr_get_pay_iv_ids_from_bal;
602 debug_exit (l_proc_name);
603 RETURN l_return;
604 EXCEPTION
605 WHEN OTHERS
606 THEN
607 DEBUG ( ' Others Exception'
608 || l_proc_name);
609 p_tab_ele_ids.DELETE;
610 p_error_number := SQLCODE;
611 p_error_text := SQLERRM;
612 RAISE;
613 END get_pay_ele_ids_from_bal;
614
615
616 -- This function fetches the details from the CPX extract definition UDT
617 --
618 -- ----------------------------------------------------------------------------
619 -- |---------------------------< fetch_CPX_UDT_details >----------------------|
620 -- ----------------------------------------------------------------------------
621 FUNCTION fetch_cpx_udt_details (
622 p_error_number OUT NOCOPY NUMBER,
623 p_error_text OUT NOCOPY VARCHAR2
624 )
625 RETURN NUMBER
626 IS
627 --
628
629 l_proc_name VARCHAR2 (61)
630 := g_proc_name
631 || 'fetch_CPX_UDT_details';
632 l_initial_ext_date DATE;
633 l_pension_source_type pay_user_column_instances_f.VALUE%TYPE;
634 l_pension_source_name pay_user_column_instances_f.VALUE%TYPE;
635 i NUMBER;
636 l_row_name t_varchar2;
637 l_value t_varchar2;
638 l_error_text VARCHAR2 (200);
639
640 --
641 BEGIN
642 --
643 debug_enter (l_proc_name);
644 -- Call utility function to get the UDT values
645 -- for the Pension Scheme Name
646
647 DEBUG ('Get Pension Scheme Source Type');
648 DEBUG ('Calling function pqp_gb_get_table_value');
649
650 IF pqp_utilities.pqp_gb_get_table_value (
651 p_business_group_id=> g_business_group_id,
652 p_effective_date=> g_effective_date,
653 p_table_name=> g_extract_udt_name,
654 p_column_name=> 'Attribute Location Type',
655 p_row_name=> 'Pension Schemes',
656 p_value=> l_pension_source_type,
657 p_error_msg=> l_error_text
658 ) <> 0
659 THEN
660 DEBUG ( 'Function in Error: '
661 || l_error_text);
662 p_error_text := l_error_text;
663 RETURN -1;
664 END IF; -- End if of function in error check ...
665
666 DEBUG ( 'Pension Scheme Source Type: '
667 || l_pension_source_type);
668 DEBUG ('End of call to function pqp_gb_get_table_value');
669
670 -- Check whether a pension source type is specified in the
671 -- UDT
672
673 IF l_pension_source_type IS NULL
674 THEN
675 -- Raise Extract Error, as this information is mandatory
676
677 DEBUG ('Raise Error no pension source type');
678 debug_exit (l_proc_name);
679 p_error_text := 'BEN_93344_EXT_CPX_UDT_NOPEN_SR';
680 p_error_number := 93344;
681 RETURN -1;
682 END IF; -- End if of pension source type is null check ...
683
684 DEBUG ('Get Pension Scheme Source Name');
685 DEBUG ('Calling function pqp_gb_get_table_value');
686
687 IF pqp_utilities.pqp_gb_get_table_value (
688 p_business_group_id=> g_business_group_id,
689 p_effective_date=> g_effective_date,
690 p_table_name=> g_extract_udt_name,
691 p_column_name=> 'Attribute Location Qualifier 1',
692 p_row_name=> 'Pension Schemes',
693 p_value=> l_pension_source_name,
694 p_error_msg=> l_error_text
695 ) <> 0
696 THEN
697 DEBUG ( 'Function in Error: '
698 || l_error_text);
699 p_error_text := l_error_text;
700 debug_exit (l_proc_name);
701 RETURN -1;
702 END IF; -- End if of function in error check ...
703
704 DEBUG ( 'Pension Scheme Source Name: '
705 || l_pension_source_name);
706 DEBUG ('End of call to function pqp_gb_get_table_value');
707
708 -- Check whether the pension source type is element
709 -- and whether an element name is provided in the UDT
710
711 IF l_pension_source_type = 'Element' AND l_pension_source_name IS NULL
712 THEN
713 -- Raise Extract Error, as this information is mandatory
714
715 DEBUG ('Raise Error pension source name is missing');
716 p_error_text := 'BEN_93345_EXT_CPX_UDT_NO_ELENM';
717 p_error_number := 93345;
718 debug_exit (l_proc_name);
719 RETURN -1;
720 ELSIF l_pension_source_type = 'Balance'
721 THEN
722 -- Elsif of source type = element ...
723
724 g_pension_bal_name :=
725 NVL (l_pension_source_name, 'Total Pension Contributions');
726 DEBUG ( 'Pension Balance Name: '
727 || g_pension_bal_name);
728 ELSE -- Else of source type = Element ...
729 g_pension_ele_name := l_pension_source_name;
730 DEBUG ( 'Pension Element Name: '
731 || g_pension_ele_name);
732 END IF; -- End if of pension source type = element check ...
733
734 -- Get the Employee Contribution input value information
735 -- from the UDT
736
737 i := 0;
738 i := i
739 + 1;
740 l_row_name (i) := 'Employee Contribution';
741 i := i
742 + 1;
743 l_row_name (i) := 'Superannuation Reference Number';
744
745 FOR i IN 1 .. l_row_name.COUNT
746 LOOP
747 DEBUG ( 'Get '
748 || l_row_name (i)
749 || ' information');
750 DEBUG ('Calling function pqp_gb_get_table_value');
751
752 IF pqp_utilities.pqp_gb_get_table_value (
753 p_business_group_id=> g_business_group_id,
754 p_effective_date=> g_effective_date,
755 p_table_name=> g_extract_udt_name,
756 p_column_name=> 'Attribute Location Qualifier 1',
757 p_row_name=> l_row_name (i),
758 p_value=> l_value (i),
759 p_error_msg=> l_error_text
760 ) <> 0
761 THEN
762 DEBUG ( 'Function in Error: '
763 || l_error_text);
764 p_error_text := l_error_text;
765 debug_exit (l_proc_name);
766 RETURN -1;
767 END IF; -- End if of function in error check ...
768
769 DEBUG ('End of call to function pqp_gb_get_table_value');
770 DEBUG ( l_row_name (i)
771 || ' value is: '
772 || l_value (i));
773
774 IF l_value (i) IS NULL
775 THEN
776 -- Raise Extract Error, as this information is mandatory
777 -- now made optional
778
779 DEBUG ('Raise Error');
780 p_error_text := 'BEN_93343_EXT_CPX_UDT_NO_IV';
781 p_error_number := 93343;
782 -- raise just a warning message
783 -- debug_exit (l_proc_name);
784 -- RETURN -1;
785 END IF; -- End if of l_value(i) is null check ...
786 END LOOP; -- End loop of l_row_name ...
787
788 i := 0;
789 i := i
790 + 1;
791 g_emp_cont_iv_name := l_value (i);
792 i := i
793 + 1;
794 g_superann_refno_iv_name := l_value (i);
795 i := 0;
796 l_row_name.DELETE;
797 l_value.DELETE;
798 i := i
799 + 1;
800 l_row_name (i) := 'Superannuable Salary';
801 i := i
802 + 1;
803 l_row_name (i) := 'Additional Contributions';
804 i := i
805 + 1;
806 l_row_name (i) := 'Buy-Back Contributions';
807
808 FOR i IN 1 .. l_row_name.COUNT
809 LOOP
810 DEBUG ( 'Get '
811 || l_row_name (i)
812 || ' information');
813 DEBUG ('Calling function pqp_gb_get_table_value');
814
815 IF pqp_utilities.pqp_gb_get_table_value (
816 p_business_group_id=> g_business_group_id,
817 p_effective_date=> g_effective_date,
818 p_table_name=> g_extract_udt_name,
819 p_column_name=> 'Attribute Location Qualifier 1',
820 p_row_name=> l_row_name (i),
821 p_value=> l_value (i),
822 p_error_msg=> l_error_text
823 ) <> 0
824 THEN
825 DEBUG ( 'Function in Error: '
826 || l_error_text);
827 p_error_text := l_error_text;
828 debug_exit (l_proc_name);
829 RETURN -1;
830 END IF; -- End if of function in error check ...
831
832 DEBUG ('End of call to function pqp_gb_get_table_value');
833 DEBUG ( l_row_name (i)
834 || ' value is: '
835 || l_value (i));
836 END LOOP; -- End loop of l_row_name ...
837
838 i := 0;
839 i := i
840 + 1;
841 g_superann_sal_bal_name := NVL (l_value (i), 'Superannuable Salary');
842 i := i
843 + 1;
844 g_additional_cont_bal_name :=
845 NVL (l_value (i), 'Total Additional Contributions');
846 i := i
847 + 1;
848 g_buyback_cont_bal_name :=
849 NVL (l_value (i), 'Total BuyBack Contributions');
850 debug_exit (l_proc_name);
851 RETURN 0;
852 --
853
854 EXCEPTION
855 WHEN OTHERS
856 THEN
857 DEBUG ( 'Others Exception Raised'
858 || l_proc_name);
859 p_error_text := SQLERRM;
860 p_error_number := SQLCODE;
861 RAISE;
862 END fetch_cpx_udt_details;
863
864
865 -- This function sets the extract global variables
866 --
867 -- ----------------------------------------------------------------------------
868 -- |---------------------------< set_extract_globals >------------------------|
869 -- ----------------------------------------------------------------------------
870
871 FUNCTION set_extract_globals (
872 p_assignment_id IN NUMBER,
873 p_business_group_id IN NUMBER,
874 p_effective_date IN DATE,
875 p_error_number OUT NOCOPY NUMBER,
876 p_error_text OUT NOCOPY VARCHAR2
877 )
878 RETURN NUMBER
879 IS
880
881 --
882 l_proc_name VARCHAR2 (60)
883 := g_proc_name
884 || 'set_extract_globals';
885 l_element_type_id NUMBER;
886 l_input_value_name t_varchar2;
887 l_input_value_id pay_input_values_f.input_value_id%TYPE;
888 l_bal_type_name t_varchar2;
889 l_bal_type_id t_number;
890 i NUMBER;
891 j NUMBER;
892 l_error_number NUMBER;
893 l_error_text VARCHAR2 (200);
894 l_return NUMBER;
895
896 --
897 BEGIN
898 debug_enter (l_proc_name);
899 DEBUG ( 'Business Group ID: '
900 || p_business_group_id);
901 g_business_group_id := p_business_group_id;
902 DEBUG ( 'Effective Date: '
903 || p_effective_date);
904 g_effective_date := p_effective_date;
905 OPEN csr_pqp_extract_attributes;
906 FETCH csr_pqp_extract_attributes INTO g_extract_type, g_extract_udt_name;
907 CLOSE csr_pqp_extract_attributes;
908 --
909 -- Based on extract type set the effective dates accordingly
910 --
911
912 DEBUG ( 'Extract Type: '
913 || g_extract_type);
914
915 IF g_extract_type = 'LYNX_ANNUAL'
916 THEN
917 DEBUG ('Before calling procedure set_annual_run_dates');
918 set_annual_run_dates;
919 ELSE -- Else of extract type = Annual
920 DEBUG ('Before calling function set_periodic_run_dates');
921 l_return := set_periodic_run_dates (
922 p_error_number=> l_error_number,
923 p_error_text=> l_error_text
924 );
925
926 IF l_return <> 0
927 THEN
928 -- Raise error
929 DEBUG ('Raise Error');
930 p_error_text := l_error_text;
931 p_error_number := l_error_number;
932 debug_exit (l_proc_name);
933 RETURN -1;
934 END IF; -- End if of set_periodic_run func for error check ...
935 END IF; -- End if of extract type = Annual check ...
936
937 DEBUG ('Before calling function fetch_CPX_UDT_details');
938 l_return := fetch_cpx_udt_details (
939 p_error_number=> l_error_number,
940 p_error_text=> l_error_text
941 );
942
943 IF l_return <> 0
944 THEN
945 -- Raise error
946 DEBUG ('Raise Error');
947 p_error_text := l_error_text;
948 p_error_number := l_error_number;
949 debug_exit (l_proc_name);
950 RETURN -1;
951 END IF; -- End if of fetch_UDT_details func for error check ...
952
953 IF l_error_number = 93343
954 THEN
955 -- Raise just a warning message
956 l_return :=
957 pqp_gb_tp_extract_functions.raise_extract_warning (
958 p_assignment_id=> p_assignment_id,
959 p_error_text=> l_error_text,
960 p_error_number=> l_error_number
961 );
962 END IF; -- End if of error number check ...
963
964 --
965 -- Populate the collection with pension elements
966 --
967
968 --
969 -- Check whether the user have specified a balance or element
970 -- for their pension schemes in the UDT
971 --
972
973 IF g_pension_bal_name IS NULL
974 THEN
975 --
976 -- The users have specified an element name
977 --
978 DEBUG ('Element Name specified in the UDT');
979 -- Get element type id
980 DEBUG ('Get element type id');
981 OPEN csr_get_pay_ele_id (g_pension_ele_name, g_effective_date);
982 FETCH csr_get_pay_ele_id INTO l_element_type_id;
983
984 IF csr_get_pay_ele_id%NOTFOUND
985 THEN
986 DEBUG ( 'Element: '
987 || g_pension_ele_name
988 || ' does not exist');
989 -- Raise error
990 DEBUG ('Raise Error');
991 p_error_text := 'BEN_93347_EXT_CPX_ELE_NOTEXIST';
992 p_error_number := 93347;
993 debug_exit (l_proc_name);
994 RETURN -1;
995 END IF; -- End if of element exists check ...
996
997 CLOSE csr_get_pay_ele_id;
998 g_pension_ele_ids (l_element_type_id).element_type_id :=
999 l_element_type_id;
1000 DEBUG ( 'Element Name: '
1001 || g_pension_ele_name);
1002 DEBUG ( 'Element type id: '
1003 || TO_CHAR (l_element_type_id));
1004 ELSE -- Pension balance Name is specified
1005 DEBUG ('Balance name exists');
1006 -- Get the balance type id
1007
1008 DEBUG (
1009 'Get the balance type id for balance '
1010 || g_pension_bal_name
1011 );
1012 g_pension_bal_id :=
1013 get_pay_bal_id (p_balance_name => g_pension_bal_name);
1014
1015 IF g_pension_bal_id IS NOT NULL
1016 THEN
1017 --
1018 DEBUG ( 'Pension Balance Id: '
1019 || g_pension_bal_id);
1020 DEBUG ('Before calling procedure get_pay_ele_ids_from_bal');
1021 --
1022 --
1023 -- Get Pension Scheme Elements
1024 --
1025 l_return :=
1026 get_pay_ele_ids_from_bal (
1027 p_balance_type_id=> g_pension_bal_id,
1028 p_effective_start_date=> g_effective_start_date,
1029 p_effective_end_date=> g_effective_end_date,
1030 p_tab_ele_ids=> g_pension_ele_ids,
1031 p_error_number=> l_error_number,
1032 p_error_text=> l_error_text
1033 );
1034
1035 IF l_return <> 0
1036 THEN
1037 -- Raise error
1038 DEBUG ('Raise Error');
1039 p_error_number := l_error_number;
1040 p_error_text := l_error_text;
1041 debug_exit (l_proc_name);
1042 RETURN -1;
1043 END IF; -- End if of pay ele ids in error check ...
1044 ELSE -- Else pension bal id is null ...
1045 DEBUG ('Pension Balance Id is Null');
1046 DEBUG ('Raise Error');
1047 p_error_text := 'BEN_93348_EXT_CPX_BAL_NOTEXIST';
1048 p_error_number := 93348;
1049 debug_exit (l_proc_name);
1050 RETURN -1;
1051 END IF; -- End if of pension bal id check...
1052 END IF; -- End if of pension balance name is null check ...
1053
1054 -- Populate the input value id's for superannuation reference number
1055 -- and Employee contribution
1056
1057 IF g_emp_cont_iv_name IS NOT NULL
1058 OR g_superann_refno_iv_name IS NOT NULL
1059 THEN
1060 i := g_pension_ele_ids.FIRST;
1061
1062 WHILE i IS NOT NULL
1063 LOOP
1064 DEBUG ( 'Element Type ID: '
1065 || TO_CHAR (i));
1066 j := 0;
1067
1068 IF g_emp_cont_iv_name IS NOT NULL
1069 THEN
1070 j := j
1071 + 1;
1072 l_input_value_name (j) := g_emp_cont_iv_name;
1073 END IF; -- End if of emp cont iv not null check ...
1074
1075 IF g_superann_refno_iv_name IS NOT NULL
1076 THEN
1077 j := j
1078 + 1;
1079 l_input_value_name (j) := g_superann_refno_iv_name;
1080 END IF; -- End if of super ann not null check ...
1081
1082 FOR j IN 1 .. l_input_value_name.COUNT
1083 LOOP
1084 -- Get input value id for the input value name
1085 DEBUG ( 'Get input value id for '
1086 || l_input_value_name (j));
1087 DEBUG ('Before calling get_input_value_id procedure');
1088 l_input_value_id := NULL;
1089 l_input_value_id :=
1090 get_input_value_id (
1091 p_element_type_id=> g_pension_ele_ids (i).element_type_id,
1092 p_input_value_name=> l_input_value_name (j),
1093 p_effective_date=> g_effective_date
1094 );
1095 DEBUG (
1096 'Input value id for '
1097 || l_input_value_name (j)
1098 || TO_CHAR (l_input_value_id)
1099 );
1100
1101 -- Check whether input value exists
1102
1103 IF l_input_value_id IS NULL
1104 THEN
1105 DEBUG ('Input value does not exist Raise error');
1106 p_error_text := 'BEN_93346_EXT_CPX_IV_NOT_EXIST';
1107 p_error_number := 93346;
1108 debug_exit (l_proc_name);
1109 RETURN -1;
1110 END IF; -- End if of input value exists check ...
1111 END LOOP; -- End loop for j counter (input value names) ...
1112
1113 i := g_pension_ele_ids.NEXT (i);
1114 END LOOP; -- End loop for i counter (element type id's) ...
1115 END IF; -- End if of check whether any of ip val is not null ...
1116
1117 -- Get balance type id's for Additional and Buy-Back Contribution
1118 -- balances
1119
1120 DEBUG ('Get balance type ids for additional and buy-back contribution');
1121 i := 0;
1122 i := i
1123 + 1;
1124 l_bal_type_name (i) := g_superann_sal_bal_name;
1125 i := i
1126 + 1;
1127 l_bal_type_name (i) := g_additional_cont_bal_name;
1128 i := i
1129 + 1;
1130 l_bal_type_name (i) := g_buyback_cont_bal_name;
1131
1132 FOR i IN 1 .. l_bal_type_name.COUNT
1133 LOOP
1134 DEBUG (
1135 'Get the balance type id for balance '
1136 || l_bal_type_name (i)
1137 );
1138 l_bal_type_id (i) :=
1139 get_pay_bal_id (p_balance_name => l_bal_type_name (i));
1140 DEBUG ( 'Balance type id is : '
1141 || TO_CHAR (l_bal_type_id (i)));
1142
1143 IF l_bal_type_id (i) IS NULL
1144 THEN
1145 DEBUG ('Balance does not exist Raise error');
1146 p_error_number := 93348;
1147 p_error_text := 'BEN_93348_EXT_CPX_BAL_NOTEXIST';
1148 debug_exit (l_proc_name);
1149 RETURN -1;
1150 END IF; -- End if of balance type id is null check ...
1151 END LOOP;
1152
1153 i := 0;
1154 i := i
1155 + 1;
1156 g_superann_sal_bal_id := l_bal_type_id (i);
1157 i := i
1158 + 1;
1159 g_additional_cont_bal_id := l_bal_type_id (i);
1160 i := i
1161 + 1;
1162 g_buyback_cont_bal_id := l_bal_type_id (i);
1163
1164 -- Bug 4721921 Fix
1165 OPEN csr_get_pay_ele_id ('NI', g_effective_date);
1166 FETCH csr_get_pay_ele_id INTO l_element_type_id;
1167 CLOSE csr_get_pay_ele_id;
1168 g_ni_ele_type_id := l_element_type_id;
1169 DEBUG ( 'Element Name: NI');
1170 DEBUG ( 'Element type id: '
1171 || TO_CHAR (l_element_type_id));
1172
1173 g_ni_cat_iv_id := get_input_value_id
1174 (p_element_type_id => g_ni_ele_type_id
1175 ,p_input_value_name => 'Category'
1176 ,p_effective_date => g_effective_date
1177 );
1178
1179 DEBUG ('g_ni_cat_iv_id: '|| g_ni_cat_iv_id);
1180 -- g_ni_pen_iv_id := get_input_value
1181 -- (p_element_type_id => g_ni_ele_type_id
1182 -- ,p_input_value_name => 'Pension'
1183 -- ,p_effective_date => g_effective_date
1184 -- );
1185 -- DEBUG ('g_ni_pen_iv_id : '|| g_ni_pen_iv_id);
1186
1187 DEBUG ('Before calling get_NI_cont_out_ele_details function');
1188 l_return :=
1189 get_ni_cont_out_ele_details (
1190 p_error_number=> l_error_number,
1191 p_error_text=> l_error_text
1192 );
1193
1194 IF l_return <> 0
1195 THEN
1196 DEBUG (
1197 'Function get_NI_cont_out_ele_details function is in Error'
1198 );
1199 p_error_text := l_error_text;
1200 p_error_number := l_error_number;
1201 debug_exit (l_proc_name);
1202 RETURN -1;
1203 END IF; -- End if of return <> 0 check...
1204
1205
1206 -- Bug 4721921 Fix End
1207
1208 debug_exit (l_proc_name);
1209 RETURN 0;
1210 EXCEPTION
1211 WHEN OTHERS
1212 THEN
1213 DEBUG ( 'Others Exception Raised'
1214 || l_proc_name);
1215 p_error_text := SQLERRM;
1216 p_error_number := SQLCODE;
1217 RAISE;
1218 END set_extract_globals;
1219
1220
1221 -- This function returns the udt id for a given udt name
1222 --
1223 -- ----------------------------------------------------------------------------
1224 -- |---------------------------< get_udt_id >---------------------------------|
1225 -- ----------------------------------------------------------------------------
1226 FUNCTION get_udt_id (p_udt_name IN VARCHAR2)
1227 RETURN NUMBER
1228 IS
1229
1230 --
1231
1232 l_proc_name VARCHAR2 (60) := g_proc_name
1233 || 'get_udt_id';
1234 l_udt_id NUMBER;
1235
1236 --
1237 BEGIN
1238 --
1239 debug_enter (l_proc_name);
1240 OPEN csr_get_udt_id (p_udt_name);
1241 FETCH csr_get_udt_id INTO l_udt_id;
1242 DEBUG ( 'UDT ID: '
1243 || l_udt_id);
1244 CLOSE csr_get_udt_id;
1245 DEBUG ( p_udt_name
1246 || ' UDT ID: '
1247 || TO_CHAR (l_udt_id));
1248 debug_exit (l_proc_name);
1249 RETURN l_udt_id;
1250 END get_udt_id;
1251
1252
1253 -- This function returns the user row id for a given udt id and user row
1254 -- name
1255 --
1256 -- ----------------------------------------------------------------------------
1257 -- |---------------------------< get_user_row_id >----------------------------|
1258 -- ----------------------------------------------------------------------------
1259 FUNCTION get_user_row_id (
1260 p_user_table_id IN NUMBER,
1261 p_user_row_name IN VARCHAR2,
1262 p_effective_date IN DATE
1263 )
1264 RETURN NUMBER
1265 IS
1266
1267 --
1268
1269 l_proc_name VARCHAR2 (60) := g_proc_name
1270 || 'get_user_row_id';
1271 l_user_row_id NUMBER;
1272
1273 --
1274 BEGIN
1275 --
1276 debug_enter (l_proc_name);
1277 OPEN csr_get_user_row_id (p_user_table_id, p_user_row_name, p_effective_date);
1278 FETCH csr_get_user_row_id INTO l_user_row_id;
1279 DEBUG ( 'User Row ID: '
1280 || l_user_row_id);
1281 CLOSE csr_get_user_row_id;
1282 DEBUG (
1283 p_user_row_name
1284 || ' User Row ID: '
1285 || TO_CHAR (l_user_row_id)
1286 );
1287 debug_exit (l_proc_name);
1288 RETURN l_user_row_id;
1289 END get_user_row_id;
1290
1291
1292 -- This function returns the user column id for a given udt id and user column
1293 -- name
1294 --
1295 -- ----------------------------------------------------------------------------
1296 -- |---------------------------< get_user_column_id >-------------------------|
1297 -- ----------------------------------------------------------------------------
1298 FUNCTION get_user_column_id (
1299 p_user_table_id IN NUMBER,
1300 p_user_col_name IN VARCHAR2
1301 )
1302 RETURN NUMBER
1303 IS
1304
1305 --
1306
1307 l_proc_name VARCHAR2 (60) := g_proc_name
1308 || 'get_user_column_id';
1309 l_user_col_id NUMBER;
1310
1311 --
1312 BEGIN
1313 --
1314 debug_enter (l_proc_name);
1315 OPEN csr_get_user_column_id (p_user_table_id, p_user_col_name);
1316 FETCH csr_get_user_column_id INTO l_user_col_id;
1317 DEBUG ( 'User Column ID: '
1318 || l_user_col_id);
1319 CLOSE csr_get_user_column_id;
1320 DEBUG (
1321 p_user_col_name
1322 || ' User Column ID: '
1323 || TO_CHAR (l_user_col_id)
1324 );
1325 debug_exit (l_proc_name);
1326 RETURN l_user_col_id;
1327 END get_user_column_id;
1328
1329
1330 -- This function returns the user column name for a given user table id and
1331 -- user row id
1332 --
1333 -- ----------------------------------------------------------------------------
1334 -- |---------------------------< get_user_column_name >-------------------------|
1335 -- ----------------------------------------------------------------------------
1336 FUNCTION get_user_column_name (
1337 p_user_table_id IN NUMBER,
1338 p_user_row_id IN NUMBER,
1339 p_effective_date IN DATE
1340 )
1341 RETURN t_varchar2
1342 IS
1343
1344 --
1345
1346 l_proc_name VARCHAR2 (60)
1347 := g_proc_name
1348 || 'get_user_column_name';
1349 l_user_col_name pay_user_columns.user_column_name%TYPE;
1350 l_user_col_coll t_varchar2;
1351 i NUMBER;
1352
1353 --
1354 BEGIN
1355 --
1356 debug_enter (l_proc_name);
1357 i := 0;
1358 OPEN csr_get_user_col_name (
1359 p_user_table_id,
1360 p_user_row_id,
1361 p_effective_date
1362 );
1363
1364 LOOP
1365 FETCH csr_get_user_col_name INTO l_user_col_name;
1366 EXIT WHEN csr_get_user_col_name%NOTFOUND;
1367 i := i
1368 + 1;
1369 l_user_col_coll (i) := l_user_col_name;
1370 DEBUG ( 'User Column Name: '
1371 || l_user_col_name);
1372 END LOOP;
1373
1374 CLOSE csr_get_user_col_name;
1375 debug_exit (l_proc_name);
1376 RETURN l_user_col_coll;
1377 END get_user_column_name;
1378
1379
1380 -- This function returns the translated code for a given udt id and user column
1381 -- ids and value from the translated UDT
1382 --
1383 -- ----------------------------------------------------------------------------
1384 -- |---------------------------< get_udt_translated_code >--------------------|
1385 -- ----------------------------------------------------------------------------
1386 FUNCTION get_udt_translated_code (
1387 p_user_table_name IN VARCHAR2,
1388 p_effective_date IN DATE,
1389 p_asg_user_col_name IN VARCHAR2,
1390 p_ext_user_col_name IN VARCHAR2,
1391 p_value IN VARCHAR2
1392 )
1393 RETURN VARCHAR2
1394 IS
1395
1396 --
1397
1398 l_proc_name VARCHAR2 (60)
1399 := g_proc_name
1400 || 'get_udt_translated_code';
1401 l_value pay_user_column_instances_f.VALUE%TYPE;
1402 l_user_table_id NUMBER;
1403 l_asg_user_col_id NUMBER;
1404 l_ext_user_col_id NUMBER;
1405
1406 --
1407 BEGIN
1408 --
1409 debug_enter (l_proc_name);
1410 -- Get the UDT id for the employment category translation table
1411 l_user_table_id := get_udt_id (p_udt_name => p_user_table_name);
1412 -- Get the assignment user column id
1413 l_asg_user_col_id :=
1414 get_user_column_id (
1415 p_user_table_id=> l_user_table_id,
1416 p_user_col_name=> p_asg_user_col_name
1417 );
1418 -- Get the extract user column id
1419 l_ext_user_col_id :=
1420 get_user_column_id (
1421 p_user_table_id=> l_user_table_id,
1422 p_user_col_name=> p_ext_user_col_name
1423 );
1424 OPEN csr_get_udt_translated_code (
1425 l_user_table_id,
1426 p_effective_date,
1427 l_asg_user_col_id,
1428 l_ext_user_col_id,
1429 p_value
1430 );
1431 FETCH csr_get_udt_translated_code INTO l_value;
1432 DEBUG ( 'UDT Translated Code: '
1433 || l_value);
1434 CLOSE csr_get_udt_translated_code;
1435 debug_exit (l_proc_name);
1436 RETURN l_value;
1437 END get_udt_translated_code;
1438
1439
1440 -- This function gets the NI contracted out element details from the
1441 -- Lynx NI LG Pension mapping UDT
1442 --
1443 -- ----------------------------------------------------------------------------
1444 -- |-----------------------< get_NI_cont_out_ele_details >--------------------|
1445 -- ----------------------------------------------------------------------------
1446 FUNCTION get_ni_cont_out_ele_details (
1447 p_error_number OUT NOCOPY NUMBER,
1448 p_error_text OUT NOCOPY VARCHAR2
1449 )
1450 RETURN NUMBER
1451 IS
1452
1453 --
1454 l_proc_name VARCHAR2 (60)
1455 := g_proc_name
1456 || 'get_NI_cont_out_ele_details';
1457 l_return NUMBER;
1458 l_ni_cont_out_ele_ids csr_get_ni_ele_ids_from_udt%ROWTYPE;
1459 l_user_table_id NUMBER;
1460 l_user_col_id NUMBER;
1461
1462 --
1463 BEGIN
1464 --
1465 debug_enter (l_proc_name);
1466 DEBUG ('Before calling function get_udt_id');
1467 l_user_table_id :=
1468 get_udt_id (p_udt_name => 'PQP_GB_LYNX_HEYWOOD_NI_MAPPING_TABLE');
1469 DEBUG ('UDT Name: PQP_GB_LYNX_HEYWOOD_NI_MAPPING_TABLE');
1470 DEBUG ( 'UDT ID: '
1471 || l_user_table_id);
1472
1473 IF l_user_table_id IS NULL
1474 THEN
1475 DEBUG ('UDT not found Raise Error');
1476 p_error_number := 93349;
1477 p_error_text := 'BEN_93349_EXT_CPX_UDT_NOTEXIST';
1478 debug_exit (l_proc_name);
1479 RETURN -1;
1480 END IF; -- End if of error check ...
1481
1482 DEBUG ( 'NI UDT ID: '
1483 || TO_CHAR (l_user_table_id));
1484 -- Get the user column id
1485 DEBUG ('Before calling function get_user_column_id');
1486 l_user_col_id := get_user_column_id (
1487 p_user_table_id=> l_user_table_id,
1488 p_user_col_name=> 'Contracted Out'
1489 );
1490 DEBUG ('User column Name: Contracted Out');
1491 DEBUG ( 'User column ID is: '
1492 || l_user_col_id);
1493
1494 IF l_user_col_id IS NULL
1495 THEN
1496 DEBUG ('User Column not found Raise Error');
1497 p_error_number := 93350;
1498 p_error_text := 'BEN_93350_EXT_CPX_UDTCOL_NOTEX';
1499 debug_exit (l_proc_name);
1500 RETURN -1;
1501 END IF; -- End if of error check ...
1502
1503 -- Get the NI Contracted Out details
1504
1505 OPEN csr_get_ni_ele_ids_from_udt (
1506 l_user_table_id,
1507 l_user_col_id,
1508 g_effective_date
1509 );
1510
1511 LOOP
1512 FETCH csr_get_ni_ele_ids_from_udt INTO l_ni_cont_out_ele_ids;
1513 EXIT WHEN csr_get_ni_ele_ids_from_udt%NOTFOUND;
1514 -- Store the ele details in the collection
1515 DEBUG ( 'NI Category : '
1516 || l_ni_cont_out_ele_ids.category);
1517 DEBUG (
1518 'User Row ID: '
1519 || l_ni_cont_out_ele_ids.user_row_id
1520 );
1521 g_ni_cont_out_ele_ids (l_ni_cont_out_ele_ids.user_row_id) :=
1522 l_ni_cont_out_ele_ids;
1523 END LOOP; -- End loop of NI cont cursor ...
1524
1525 CLOSE csr_get_ni_ele_ids_from_udt;
1526
1527 IF g_ni_cont_out_ele_ids.COUNT = 0
1528 THEN
1529 DEBUG ('No NI Contracted out elements');
1530 p_error_number := 93351;
1531 p_error_text := 'BEN_93351_EXT_CPX_NICONT_NOELE';
1532 debug_exit (l_proc_name);
1533 RETURN -1;
1534 END IF; -- End if of NI cont out elements exists...
1535
1536 debug_exit (l_proc_name);
1537 RETURN 0;
1538 EXCEPTION
1539 WHEN OTHERS
1540 THEN
1541 DEBUG ( 'Others Exception Raised'
1542 || l_proc_name);
1543 p_error_text := SQLERRM;
1544 p_error_number := SQLCODE;
1545 RAISE;
1546 END get_ni_cont_out_ele_details;
1547
1548
1549 -- This procedure gets the NI element details from the NI LG Pension Mapping
1550 -- UDT
1551 --
1552 -- ----------------------------------------------------------------------------
1553 -- |-----------------------------< get_NI_element_details>--------------------|
1554 -- ----------------------------------------------------------------------------
1555 PROCEDURE get_ni_element_details
1556 IS
1557
1558 --
1559 l_proc_name VARCHAR2 (60)
1560 := g_proc_name
1561 || 'get_NI_element_details';
1562 l_user_table_id NUMBER;
1563 l_element_type_id NUMBER;
1564 l_ni_ele_details csr_get_ni_ele_name%ROWTYPE;
1565
1566 --
1567 BEGIN
1568 --
1569 debug_enter (l_proc_name);
1570 -- Get the user table id for pension mapping UDT
1571 l_user_table_id := get_udt_id ('PQP_GB_LYNX_HEYWOOD_NI_MAPPING_TABLE');
1572 -- Fetch the NI elements from the UDT
1573 OPEN csr_get_ni_ele_name (l_user_table_id);
1574
1575 LOOP
1576 FETCH csr_get_ni_ele_name INTO l_ni_ele_details;
1577 EXIT WHEN csr_get_ni_ele_name%NOTFOUND;
1578 -- Get the element type id for the given element name
1579
1580 -- DEBUG (
1581 -- 'Get element type id for given element name '
1582 -- || l_ni_ele_details.row_low_range_or_name
1583 -- );
1584 -- OPEN csr_get_pay_ele_id (
1585 -- l_ni_ele_details.row_low_range_or_name,
1586 -- g_effective_date
1587 -- );
1588 -- FETCH csr_get_pay_ele_id INTO l_element_type_id;
1589 --
1590 -- IF csr_get_pay_ele_id%FOUND
1591 -- THEN
1592 DEBUG ( 'User Row ID: '
1593 || TO_CHAR (l_ni_ele_details.user_row_id));
1594 -- Store the element details
1595 -- g_ni_ele_details (l_ni_ele_details.user_row_id).element_type_id :=
1596 -- l_element_type_id;
1597 g_ni_ele_details (l_ni_ele_details.user_row_id).user_row_id :=
1598 l_ni_ele_details.user_row_id;
1599 g_ni_ele_details (l_ni_ele_details.user_row_id).category :=
1600 l_ni_ele_details.row_low_range_or_name;
1601 g_ni_ele_details (l_ni_ele_details.user_row_id).user_table_id :=
1602 l_user_table_id;
1603 -- END IF; -- End if of element exists check ...
1604
1605 -- CLOSE csr_get_pay_ele_id;
1606 END LOOP; -- End loop of ni elements from the UDT cursor ...
1607
1608 CLOSE csr_get_ni_ele_name;
1609 --
1610 debug_exit (l_proc_name);
1611 --
1612
1613 END get_ni_element_details;
1614
1615 --
1616
1617 -- This function returns the employment category information for a given
1618 -- assignment id
1619 --
1620 -- ----------------------------------------------------------------------------
1621 -- |---------------------------< get_asg_employment_cat >---------------------|
1622 -- ----------------------------------------------------------------------------
1623 FUNCTION get_asg_employment_cat (
1624 p_assignment_id IN NUMBER,
1625 p_effective_date IN DATE
1626 )
1627 RETURN VARCHAR2
1628 IS
1629
1630 --
1631 l_proc_name VARCHAR2 (60)
1632 := g_proc_name
1633 || 'get_asg_employment_cat';
1634 l_asg_employment_cat hr_lookups.meaning%TYPE;
1635
1636 --
1637 BEGIN
1638 --
1639 debug_enter (l_proc_name);
1640 OPEN csr_get_asg_employment_cat (p_assignment_id, p_effective_date);
1641 FETCH csr_get_asg_employment_cat INTO l_asg_employment_cat;
1642 CLOSE csr_get_asg_employment_cat;
1643 DEBUG ( 'Assignment employment category: '
1644 || l_asg_employment_cat);
1645 debug_exit (l_proc_name);
1646 RETURN l_asg_employment_cat;
1647 END get_asg_employment_cat;
1648
1649
1650 -- This function determines whether an assignment qualifies for CPX
1651 -- starters report
1652 --
1653 -- ----------------------------------------------------------------------------
1654 -- |---------------------------< chk_is_employee_a_starter >-------------------|
1655 -- ----------------------------------------------------------------------------
1656 FUNCTION chk_is_employee_a_starter (
1657 p_assignment_id IN NUMBER,
1658 p_effective_start_date IN DATE,
1659 p_effective_end_date IN DATE
1660 )
1661 RETURN VARCHAR2
1662 IS
1663
1664 --
1665 l_proc_name VARCHAR2 (60)
1666 := g_proc_name
1667 || 'chk_is_employee_a_starter';
1668 l_eet_details csr_get_starters_eet_info%ROWTYPE;
1669 l_inclusion_flag VARCHAR2 (1);
1670
1671 --
1672 BEGIN
1673 debug_enter (l_proc_name);
1674 DEBUG ('Check Element entries exists with pension elements');
1675 -- Check element entries exist with pension ele's
1676 l_inclusion_flag := 'N';
1677 OPEN csr_get_starters_eet_info (
1678 c_assignment_id=> p_assignment_id,
1679 c_effective_start_date=> p_effective_start_date,
1680 c_effective_end_date=> p_effective_end_date
1681 );
1682
1683 LOOP
1684 DEBUG ('Fetch element entries');
1685 FETCH csr_get_starters_eet_info INTO l_eet_details;
1686 EXIT WHEN csr_get_starters_eet_info%NOTFOUND;
1687
1688 -- Check atleast one pension element exists for this assignment
1689 IF g_pension_ele_ids.EXISTS (l_eet_details.element_type_id)
1690 THEN
1691 -- Element exists, set the inclusion flag to 'Y'
1692 DEBUG ('Pension element entry exists');
1693 DEBUG (
1694 'Pension Element Id: '
1695 || TO_CHAR (l_eet_details.element_type_id)
1696 );
1697 IF l_inclusion_flag = 'N'
1698 THEN
1699 g_ele_entry_details (p_assignment_id).element_type_id :=
1700 l_eet_details.element_type_id;
1701 g_ele_entry_details (p_assignment_id).element_entry_id :=
1702 l_eet_details.element_entry_id;
1703 g_ele_entry_details (p_assignment_id).effective_start_date :=
1704 l_eet_details.effective_start_date;
1705 g_ele_entry_details (p_assignment_id).effective_end_date :=
1706 l_eet_details.effective_end_date;
1707 g_ele_entry_details (p_assignment_id).assignment_id :=
1708 p_assignment_id;
1709 l_inclusion_flag := 'Y';
1710 END IF; -- l_inclusion flag is N check ...
1711
1712
1713 g_index := g_index + 1;
1714 DEBUG('g_index: '|| g_index);
1715 g_pen_ele_details (g_index).element_entry_id :=
1716 l_eet_details.element_entry_id;
1717 g_pen_ele_details (g_index).element_type_id :=
1718 l_eet_details.element_type_id;
1719 g_pen_ele_details (g_index).effective_start_date :=
1720 l_eet_details.effective_start_date;
1721 g_pen_ele_details (g_index).effective_end_date :=
1722 l_eet_details.effective_end_date;
1723 g_pen_ele_details (g_index).assignment_id :=
1724 p_assignment_id;
1725 EXIT;
1726 END IF; -- End if of pension element entry exists ...
1727 END LOOP;
1728
1729 CLOSE csr_get_starters_eet_info;
1730 debug_exit (l_proc_name);
1731 RETURN l_inclusion_flag;
1732 END chk_is_employee_a_starter;
1733
1734
1735 -- This function returns the element entry value for a given element entry id
1736 -- and input value id
1737 --
1738 -- ----------------------------------------------------------------------------
1739 -- |-------------------------< get_ele_entry_value >--------------------------|
1740 -- ----------------------------------------------------------------------------
1741 FUNCTION get_ele_entry_value (
1742 p_element_entry_id IN NUMBER,
1743 p_input_value_id IN NUMBER,
1744 p_effective_start_date IN DATE,
1745 p_effective_end_date IN DATE
1746 )
1747 RETURN VARCHAR2
1748 IS
1749
1750 --
1751 l_proc_name VARCHAR2 (60)
1752 := g_proc_name
1753 || 'get_ele_entry_value';
1754 l_ele_entry_value pay_element_entry_values_f.screen_entry_value%TYPE;
1755
1756 --
1757 BEGIN
1758 debug_enter (l_proc_name);
1759 OPEN csr_get_ele_entry_value (
1760 p_element_entry_id,
1761 p_input_value_id,
1762 p_effective_start_date,
1763 p_effective_end_date
1764 );
1765 FETCH csr_get_ele_entry_value INTO l_ele_entry_value;
1766 CLOSE csr_get_ele_entry_value;
1767 DEBUG ( 'Element Entry ID: '
1768 || TO_CHAR (p_element_entry_id));
1769 DEBUG ( 'Input Value ID: '
1770 || TO_CHAR (p_input_value_id));
1771 DEBUG ( 'Entry Value: '
1772 || l_ele_entry_value);
1773 debug_exit (l_proc_name);
1774 RETURN l_ele_entry_value;
1775 END get_ele_entry_value;
1776
1777
1778 -- This procedure gets all the secondary assignment information for any given
1779 -- primary assignment
1780 --
1781 -- ----------------------------------------------------------------------------
1782 -- |-----------------------< get_all_sec_assignments >------------------------|
1783 -- ----------------------------------------------------------------------------
1784 PROCEDURE get_all_sec_assignments (
1785 p_assignment_id IN NUMBER,
1786 p_secondary_asg_ids OUT NOCOPY t_number
1787 )
1788 IS
1789
1790 --
1791 l_proc_name VARCHAR2 (60)
1792 := g_proc_name
1793 || 'get_all_secondary_assignments';
1794 l_mult_asg_info csr_get_multiple_assignments%ROWTYPE;
1795
1796 --
1797 BEGIN
1798 debug_enter (l_proc_name);
1799 -- Check for multiple assignments
1800
1801 DEBUG ('Check for multiple assignments');
1802 OPEN csr_get_multiple_assignments (c_assignment_id => p_assignment_id);
1803
1804 LOOP
1805 FETCH csr_get_multiple_assignments INTO l_mult_asg_info;
1806 EXIT WHEN csr_get_multiple_assignments%NOTFOUND;
1807 DEBUG (
1808 'Secondary Assignments for '
1809 || TO_CHAR (p_assignment_id)
1810 || TO_CHAR (l_mult_asg_info.assignment_id)
1811 );
1812 p_secondary_asg_ids (l_mult_asg_info.assignment_id) :=
1813 l_mult_asg_info.assignment_id;
1814 END LOOP;
1815
1816 CLOSE csr_get_multiple_assignments;
1817 debug_exit (l_proc_name);
1818 EXCEPTION
1819 WHEN OTHERS
1820 THEN
1821 DEBUG ( 'Others Exception'
1822 || l_proc_name);
1823 p_secondary_asg_ids.DELETE;
1824 RAISE;
1825 END get_all_sec_assignments;
1826
1827
1828 -- This procedure evaluates the secondary assignments and eliminates all the
1829 -- secondary assignments that does not meet the eligibility criteria
1830 --
1831 -- ----------------------------------------------------------------------------
1832 -- |-----------------------< get_eligible_sec_assignments >-------------------|
1833 -- ----------------------------------------------------------------------------
1834 PROCEDURE get_eligible_sec_assignments (
1835 p_assignment_id IN NUMBER,
1836 p_secondary_asg_ids OUT NOCOPY t_number
1837 )
1838 IS
1839
1840 --
1841 l_proc_name VARCHAR2 (60)
1842 := g_proc_name
1843 || 'get_eligible_sec_assignments';
1844 l_all_sec_asg_ids t_number;
1845 i NUMBER;
1846
1847 --
1848 BEGIN
1849 debug_enter (l_proc_name);
1850 DEBUG ('Before calling procedure get_all_sec_assignments');
1851 -- Get all secondary assignments
1852
1853 get_all_sec_assignments (
1854 p_assignment_id=> p_assignment_id,
1855 p_secondary_asg_ids=> l_all_sec_asg_ids
1856 );
1857 DEBUG (
1858 'Check whether the assignment exists in the global '
1859 || 'eligible assignment collection'
1860 );
1861 -- Check whether this assignment exist in the Global collection
1862
1863 i := l_all_sec_asg_ids.FIRST;
1864
1865 WHILE i IS NOT NULL
1866 LOOP
1867 IF g_secondary_asg_ids.EXISTS (i)
1868 THEN
1869 DEBUG ( TO_CHAR (i)
1870 || ' Secondary assignment exists');
1871 p_secondary_asg_ids (i) := l_all_sec_asg_ids (i);
1872 END IF; -- End if of asg exists in global collection ...
1873
1874 i := l_all_sec_asg_ids.NEXT (i);
1875 END LOOP; -- End loop of secondary assignments ...
1876
1877 debug_exit (l_proc_name);
1878 EXCEPTION
1879 WHEN OTHERS
1880 THEN
1881 DEBUG ( 'Others Exception'
1882 || l_proc_name);
1883 p_secondary_asg_ids.DELETE;
1884 RAISE;
1885 END get_eligible_sec_assignments;
1886
1887
1888 -- This function will get the latest assignment action id for a given
1889 -- assignment id and effective date
1890 --
1891 -- ----------------------------------------------------------------------------
1892 -- |---------------------------< get_latest_action_id >----------------------|
1893 -- ----------------------------------------------------------------------------
1894 FUNCTION get_latest_action_id (
1895 p_assignment_id IN NUMBER,
1896 p_effective_date IN DATE
1897 )
1898 RETURN NUMBER
1899 IS
1900
1901 --
1902 l_assignment_action_id NUMBER;
1903 l_proc_name VARCHAR2 (60)
1904 := g_proc_name
1905 || 'get_latest_action_id';
1906
1907
1908 --
1909 CURSOR get_latest_id (c_assignment_id IN NUMBER, c_effective_date IN DATE)
1910 IS
1911 SELECT fnd_number.canonical_to_number (
1912 SUBSTR (
1913 MAX (
1914 LPAD (paa.action_sequence, 15, '0')
1915 || paa.assignment_action_id
1916 ),
1917 16
1918 )
1919 )
1920 FROM pay_assignment_actions paa, pay_payroll_actions ppa
1921 WHERE paa.assignment_id = c_assignment_id
1922 AND ppa.payroll_action_id = paa.payroll_action_id
1923 AND paa.source_action_id IS NOT NULL
1924 AND ppa.effective_date <= c_effective_date
1925 AND ppa.action_type IN ('R', 'Q', 'I', 'V', 'B');
1926
1927 --
1928 BEGIN
1929
1930 --
1931 debug_enter (l_proc_name);
1932 OPEN get_latest_id (p_assignment_id, p_effective_date);
1933 FETCH get_latest_id INTO l_assignment_action_id;
1934 CLOSE get_latest_id;
1935 DEBUG ( 'Latest Action Id: '
1936 || TO_CHAR (l_assignment_action_id));
1937 debug_exit (l_proc_name);
1938
1939 --
1940 RETURN l_assignment_action_id;
1941
1942 --
1943 END get_latest_action_id;
1944
1945
1946 -- This function returns the sum of run result value for a given assignment id
1947 -- element type id and input value id
1948 -- Please note that this function should only be used when a balance is not
1949 -- available
1950 --
1951 -- ----------------------------------------------------------------------------
1952 -- |---------------------------< get_asg_ele_rresult_value >-----------------|
1953 -- ----------------------------------------------------------------------------
1954 FUNCTION get_asg_ele_rresult_value (
1955 p_assignment_id IN NUMBER,
1956 p_element_type_id IN NUMBER,
1957 p_input_value_id IN NUMBER,
1958 p_effective_start_date IN DATE,
1959 p_effective_end_date IN DATE
1960 )
1961 RETURN NUMBER
1962 IS
1963
1964 --
1965 l_proc_name VARCHAR2 (60)
1966 := g_proc_name
1967 || 'get_asg_ele_rresult_value';
1968 l_rresult_value NUMBER := 0;
1969 l_effective_date DATE;
1970 l_assignment_action_id NUMBER;
1971 l_value NUMBER;
1972 i NUMBER;
1973
1974 --
1975 BEGIN
1976 debug_enter (l_proc_name);
1977 i := g_pen_ele_details.FIRST;
1978 WHILE i IS NOT NULL
1979 LOOP
1980 IF g_pen_ele_details(i).assignment_id = p_assignment_id AND
1981 g_pen_ele_details(i).element_type_id = p_element_type_id
1982 THEN
1983 DEBUG('g_pen_ele_details(i).element_type_id: '
1984 || g_pen_ele_details(i).element_type_id);
1985 DEBUG('g_pen_ele_details(i).effective_start_date: '
1986 || TO_CHAR(g_pen_ele_details(i).effective_start_date, 'DD/MON/YYYY'));
1987 DEBUG('g_pen_ele_details(i).effective_end_date: '
1988 || TO_CHAR(g_pen_ele_details(i).effective_end_date, 'DD/MON/YYYY'));
1989
1990 OPEN csr_get_end_date (
1991 c_assignment_id=> p_assignment_id,
1992 c_effective_start_date=> GREATEST(p_effective_start_date,
1993 g_pen_ele_details(i).effective_start_date),
1994 c_effective_end_date=> LEAST(p_effective_end_date,
1995 g_pen_ele_details(i).effective_end_date)
1996 );
1997
1998 LOOP
1999 FETCH csr_get_end_date INTO l_effective_date;
2000 EXIT WHEN csr_get_end_date%NOTFOUND;
2001 -- Call function to get the latest assignment action id
2002 DEBUG ('Before calling function get_latest_assignment_action_id');
2003 l_assignment_action_id :=
2004 get_latest_action_id (
2005 p_assignment_id=> p_assignment_id,
2006 p_effective_date=> l_effective_date
2007 );
2008 -- Get the sum of run result period for this assignment action
2009 OPEN csr_get_rresult_value (
2010 l_assignment_action_id,
2011 p_element_type_id,
2012 p_input_value_id
2013 );
2014 FETCH csr_get_rresult_value INTO l_value;
2015 CLOSE csr_get_rresult_value;
2016 DEBUG ( 'Run Result Value: '
2017 || TO_CHAR (l_value));
2018 l_rresult_value := l_rresult_value
2019 + l_value;
2020 END LOOP;
2021
2022 CLOSE csr_get_end_date;
2023 END IF; -- End if of element type and assignment id equals check ...
2024 i := g_pen_ele_details.NEXT(i);
2025 END LOOP; -- pl/sql loop
2026
2027 l_rresult_value := l_rresult_value * 100;
2028 DEBUG ( 'Final Run Result Value: '
2029 || TO_CHAR (l_rresult_value));
2030 debug_exit (l_proc_name);
2031 RETURN l_rresult_value;
2032 --
2033 END get_asg_ele_rresult_value;
2034
2035
2036 -- This function returns the sum of run result value for the person accepting
2037 -- assignment id, element type id and input value id
2038 -- Please note, this function will have to be used only when there is no
2039 -- balance available
2040 --
2041 -- ----------------------------------------------------------------------------
2042 -- |-----------------------< get_person_ele_rresult_value >-------------------|
2043 -- ----------------------------------------------------------------------------
2044 FUNCTION get_person_ele_rresult_value (
2045 p_assignment_id IN NUMBER,
2046 p_element_type_id IN NUMBER,
2047 p_input_value_id IN NUMBER,
2048 p_effective_start_date IN DATE,
2049 p_effective_end_date IN DATE
2050 )
2051 RETURN NUMBER
2052 IS
2053
2054 --
2055 l_proc_name VARCHAR2 (60)
2056 := g_proc_name
2057 || 'get_person_ele_rresult_value';
2058 l_secondary_asg_ids t_number;
2059 l_person_rresult_value NUMBER;
2060 l_rresult_value NUMBER := 0;
2061 i NUMBER;
2062
2063 --
2064 BEGIN
2065 --
2066 debug_enter (l_proc_name);
2067 -- Determine the element runresult value for primary assignment
2068
2069 DEBUG ('Primary Assignment');
2070 l_rresult_value :=
2071 get_asg_ele_rresult_value (
2072 p_assignment_id=> p_assignment_id,
2073 p_element_type_id=> p_element_type_id,
2074 p_input_value_id=> p_input_value_id,
2075 p_effective_start_date=> p_effective_start_date,
2076 p_effective_end_date=> p_effective_end_date
2077 );
2078 DEBUG ( 'Run Result Value: '
2079 || TO_CHAR (l_rresult_value));
2080 -- Check for secondary assignments
2081
2082
2083 get_eligible_sec_assignments (
2084 p_assignment_id=> p_assignment_id,
2085 p_secondary_asg_ids=> l_secondary_asg_ids
2086 );
2087 i := l_secondary_asg_ids.FIRST;
2088
2089 WHILE i IS NOT NULL
2090 LOOP
2091 DEBUG ('Secondary Assignment');
2092 l_rresult_value :=
2093 l_rresult_value
2094 + get_asg_ele_rresult_value (
2095 p_assignment_id=> l_secondary_asg_ids (i),
2096 p_element_type_id=> p_element_type_id,
2097 p_input_value_id=> p_input_value_id,
2098 p_effective_start_date=> p_effective_start_date,
2099 p_effective_end_date=> p_effective_end_date
2100 );
2101 DEBUG ( 'Run Result Value: '
2102 || TO_CHAR (l_rresult_value));
2103 i := l_secondary_asg_ids.NEXT (i);
2104 END LOOP;
2105
2106 l_person_rresult_value := l_rresult_value;
2107 DEBUG ( 'Person Run Result Value: '
2108 || TO_CHAR (l_person_rresult_value));
2109 debug_exit (l_proc_name);
2110 RETURN l_person_rresult_value;
2111 --
2112
2113 END get_person_ele_rresult_value;
2114
2115
2116 -- This function returns the balance value for a given assignment and
2117 -- balance type id
2118 --
2119 -- ----------------------------------------------------------------------------
2120 -- |---------------------------< get_asg_bal_value >--------------------------|
2121 -- ----------------------------------------------------------------------------
2122 FUNCTION get_asg_bal_value (
2123 p_assignment_id IN NUMBER,
2124 p_balance_type_id IN NUMBER,
2125 p_effective_start_date IN DATE,
2126 p_effective_end_date IN DATE
2127 )
2128 RETURN NUMBER
2129 IS
2130
2131 --
2132 l_proc_name VARCHAR2 (60) := g_proc_name
2133 || 'get_asg_bal_value';
2134 l_bal_value NUMBER := 0;
2135 l_effective_date DATE;
2136 i NUMBER;
2137
2138 --
2139 BEGIN
2140 debug_enter (l_proc_name);
2141 i := g_pen_ele_details.FIRST;
2142 WHILE i IS NOT NULL
2143 LOOP
2144 IF g_pen_ele_details(i).assignment_id = p_assignment_id
2145 THEN
2146 DEBUG('g_pen_ele_details(i).effective_start_date: '
2147 || TO_CHAR(g_pen_ele_details(i).effective_start_date, 'DD/MON/YYYY'));
2148 DEBUG('g_pen_ele_details(i).effective_end_date: '
2149 || TO_CHAR(g_pen_ele_details(i).effective_end_date, 'DD/MON/YYYY'));
2150
2151 OPEN csr_get_end_date (
2152 c_assignment_id=> p_assignment_id,
2153 c_effective_start_date=> GREATEST(p_effective_start_date,
2154 g_pen_ele_details(i).effective_start_date),
2155 c_effective_end_date=> LEAST(p_effective_end_date,
2156 g_pen_ele_details(i).effective_end_date)
2157 );
2158
2159 LOOP
2160 FETCH csr_get_end_date INTO l_effective_date;
2161 EXIT WHEN csr_get_end_date%NOTFOUND;
2162 DEBUG ('Before calling function hr_gbbal.calc_asg_proc_ptd_date');
2163 l_bal_value :=
2164 l_bal_value
2165 + hr_gbbal.calc_asg_proc_ptd_date (
2166 p_assignment_id=> p_assignment_id,
2167 p_balance_type_id=> p_balance_type_id,
2168 p_effective_date=> l_effective_date
2169 );
2170 DEBUG ( 'Balance Value: '
2171 || TO_CHAR (l_bal_value));
2172 END LOOP;
2173
2174 CLOSE csr_get_end_date;
2175 END IF; -- End if of assignment id equals check ...
2176 i := g_pen_ele_details.NEXT(i);
2177 END LOOP; -- pl/sql loop
2178 l_bal_value := l_bal_value * 100;
2179 DEBUG ( 'Final Balance Value: '
2180 || TO_CHAR (l_bal_value));
2181 debug_exit (l_proc_name);
2182 RETURN l_bal_value;
2183 END get_asg_bal_value;
2184
2185
2186 -- This function returns the person balance value for a given assignment
2187 -- and balance type id
2188 --
2189 -- ----------------------------------------------------------------------------
2190 -- |-----------------------< get_person_bal_value >---------------------------|
2191 -- ----------------------------------------------------------------------------
2192 FUNCTION get_person_bal_value (
2193 p_assignment_id IN NUMBER,
2194 p_balance_type_id IN NUMBER,
2195 p_effective_start_date IN DATE,
2196 p_effective_end_date IN DATE
2197 )
2198 RETURN NUMBER
2199 IS
2200
2201 --
2202 l_proc_name VARCHAR2 (60)
2203 := g_proc_name
2204 || 'get_person_bal_value';
2205 l_secondary_asg_ids t_number;
2206 l_person_bal_value NUMBER;
2207 l_bal_value NUMBER := 0;
2208 i NUMBER;
2209
2210 --
2211 BEGIN
2212 --
2213 debug_enter (l_proc_name);
2214 -- Determine the balance value for primary assignment
2215
2216 l_bal_value :=
2217 get_asg_bal_value (
2218 p_assignment_id=> p_assignment_id,
2219 p_balance_type_id=> p_balance_type_id,
2220 p_effective_start_date=> p_effective_start_date,
2221 p_effective_end_date=> p_effective_end_date
2222 );
2223 DEBUG ( 'Bal Value: '
2224 || TO_CHAR (l_bal_value));
2225 -- Check for secondary assignments
2226
2227 get_eligible_sec_assignments (
2228 p_assignment_id=> p_assignment_id,
2229 p_secondary_asg_ids=> l_secondary_asg_ids
2230 );
2231 i := l_secondary_asg_ids.FIRST;
2232
2233 WHILE i IS NOT NULL
2234 LOOP
2235 l_bal_value :=
2236 l_bal_value
2237 + get_asg_bal_value (
2238 p_assignment_id=> l_secondary_asg_ids (i),
2239 p_balance_type_id=> p_balance_type_id,
2240 p_effective_start_date=> p_effective_start_date,
2241 p_effective_end_date=> p_effective_end_date
2242 );
2243 DEBUG ( 'Bal Value: '
2244 || TO_CHAR (l_bal_value));
2245 i := l_secondary_asg_ids.NEXT (i);
2246 END LOOP;
2247
2248 l_person_bal_value := NVL (l_bal_value, 0);
2249 DEBUG ( 'Person Bal Value: '
2250 || TO_CHAR (l_person_bal_value));
2251 debug_exit (l_proc_name);
2252 RETURN l_person_bal_value;
2253 --
2254
2255 END get_person_bal_value;
2256
2257
2258 -- This function should be used when ASG_PROC_PTD dimension is not available for
2259 -- a balance to determine its value
2260 --
2261 -- ----------------------------------------------------------------------------
2262 -- |-----------------------< get_balance_value >-----------------------------|
2263 -- ----------------------------------------------------------------------------
2264 FUNCTION get_balance_value (
2265 p_assignment_id IN NUMBER,
2266 p_balance_type_id IN NUMBER,
2267 p_effective_start_date IN DATE,
2268 p_effective_end_date IN DATE
2269 )
2270 RETURN NUMBER
2271 IS
2272
2273 --
2274 l_proc_name VARCHAR2 (60)
2275 := g_proc_name
2276 || 'get_balance_value';
2277 l_secondary_asg_ids t_number;
2278 l_balance_value NUMBER;
2279 l_value NUMBER := 0;
2280 i NUMBER;
2281 j NUMBER;
2282
2283 --
2284 BEGIN
2285 --
2286 debug_enter (l_proc_name);
2287 -- Determine the balance value for primary assignment
2288 i := g_pen_ele_details.FIRST;
2289 WHILE i IS NOT NULL
2290 LOOP
2291 IF g_pen_ele_details(i).assignment_id = p_assignment_id
2292 THEN
2293 DEBUG('g_pen_ele_details(i).effective_start_date: '
2294 || TO_CHAR(g_pen_ele_details(i).effective_start_date, 'DD/MON/YYYY'));
2295 DEBUG('g_pen_ele_details(i).effective_end_date: '
2296 || TO_CHAR(g_pen_ele_details(i).effective_end_date, 'DD/MON/YYYY'));
2297
2298 DEBUG ('Primary Assignment');
2299 DEBUG ('Before calling function hr_gbbal.calc_balance');
2300 l_value := l_value +
2301 hr_gbbal.calc_balance (
2302 p_assignment_id=> p_assignment_id,
2303 p_balance_type_id=> p_balance_type_id,
2304 p_period_from_date=> GREATEST(p_effective_start_date,
2305 g_pen_ele_details(i).effective_start_date),
2306 p_event_from_date=> GREATEST(p_effective_start_date,
2307 g_pen_ele_details(i).effective_start_date),
2308 p_to_date=> LEAST(p_effective_end_date,
2309 g_pen_ele_details(i).effective_end_date),
2310 p_action_sequence=> NULL
2311 );
2312 END IF; -- assignment id equals check ...
2313 i := g_pen_ele_details.NEXT(i);
2314 END LOOP;
2315 DEBUG ( 'Bal Value: '
2316 || TO_CHAR (l_value));
2317 -- Check for secondary assignments
2318
2319 get_eligible_sec_assignments (
2320 p_assignment_id=> p_assignment_id,
2321 p_secondary_asg_ids=> l_secondary_asg_ids
2322 );
2323 DEBUG ('Secondary Assignments');
2324 i := l_secondary_asg_ids.FIRST;
2325
2326 WHILE i IS NOT NULL
2327 LOOP
2328 j := g_pen_ele_details.FIRST;
2329 WHILE j IS NOT NULL
2330 LOOP
2331 IF g_pen_ele_details(j).assignment_id = l_secondary_asg_ids (i)
2332 THEN
2333 DEBUG('g_pen_ele_details(j).effective_start_date: '
2334 || TO_CHAR(g_pen_ele_details(j).effective_start_date, 'DD/MON/YYYY'));
2335 DEBUG('g_pen_ele_details(j).effective_end_date: '
2336 || TO_CHAR(g_pen_ele_details(j).effective_end_date, 'DD/MON/YYYY'));
2337
2338 DEBUG ('Before calling function hr_gbbal.calc_balance');
2339 l_value :=
2340 l_value
2341 + hr_gbbal.calc_balance (
2342 p_assignment_id=> l_secondary_asg_ids (i),
2343 p_balance_type_id=> p_balance_type_id,
2344 p_period_from_date=> GREATEST(p_effective_start_date,
2345 g_pen_ele_details(j).effective_start_date),
2346 p_event_from_date=> GREATEST(p_effective_start_date,
2347 g_pen_ele_details(j).effective_start_date),
2348 p_to_date=> LEAST(p_effective_end_date,
2349 g_pen_ele_details(j).effective_end_date),
2350 p_action_sequence=> NULL
2351 );
2352 DEBUG ( 'Bal Value: '
2353 || TO_CHAR (l_value));
2354 END IF; -- assignment id equals check ...
2355 j := g_pen_ele_details.NEXT(j);
2356 END LOOP;
2357 i := l_secondary_asg_ids.NEXT (i);
2358 END LOOP;
2359
2360 l_balance_value := l_value;
2361 DEBUG ( 'Final Bal Value: '
2362 || TO_CHAR (l_balance_value));
2363 debug_exit (l_proc_name);
2364 RETURN l_balance_value;
2365 --
2366
2367 END get_balance_value;
2368
2369
2370 -- This procedure sets the assignment details for a given assignment
2371 -- PS Amend this code if you want to fetch any other assignment details
2372 --
2373 -- ----------------------------------------------------------------------------
2374 -- |-----------------------< set_assignment_details >-------------------------|
2375 -- ----------------------------------------------------------------------------
2376 PROCEDURE set_assignment_details (
2377 p_assignment_id IN NUMBER,
2378 p_effective_date IN DATE
2379 )
2380 IS
2381
2382 --
2383 l_proc_name VARCHAR2 (60) := g_proc_name
2384 || 'set_assignment_details';
2385 --
2386 BEGIN
2387 --
2388 debug_enter (l_proc_name);
2389 OPEN csr_get_asg_details (p_assignment_id, p_effective_date);
2390 FETCH csr_get_asg_details INTO g_asg_details (p_assignment_id);
2391 CLOSE csr_get_asg_details;
2392 DEBUG (
2393 'Person ID: '
2394 || TO_CHAR (g_asg_details (p_assignment_id).person_id)
2395 );
2396 DEBUG (
2397 'Assignment Number: '
2398 || g_asg_details (p_assignment_id).assignment_number
2399 );
2400 DEBUG (
2401 'Employee Category: '
2402 || g_asg_details (p_assignment_id).employee_category
2403 );
2404 debug_exit (l_proc_name);
2405 --
2406 END set_assignment_details;
2407
2408
2409 -- This function checks whether an assignment qualifies for starters
2410 -- and returns a Y or N or Error if there is an error
2411 --
2412 -- ----------------------------------------------------------------------------
2413 -- |-----------------------< chk_employee_qual_for_starters >-----------------|
2414 -- ----------------------------------------------------------------------------
2415
2416 FUNCTION chk_employee_qual_for_starters (
2417 p_business_group_id IN NUMBER -- context
2418 ,
2419 p_effective_date IN DATE -- context
2420 ,
2421 p_assignment_id IN NUMBER -- context
2422 ,
2423 p_error_number OUT NOCOPY NUMBER,
2424 p_error_text OUT NOCOPY VARCHAR2
2425 -- ,p_trace in varchar2 default null
2426 )
2427 RETURN VARCHAR2 -- Y or N
2428 IS
2429
2430 --
2431 l_inclusion_flag VARCHAR2 (20) := 'N';
2432 l_proc_name VARCHAR2 (61)
2433 := g_proc_name
2434 || 'chk_employee_qual_for_starters';
2435 l_secondary_asg_ids t_number;
2436 l_error_number NUMBER;
2437 l_error_text VARCHAR2 (200);
2438 l_return NUMBER;
2439 i NUMBER;
2440
2441 --
2442 BEGIN
2443 debug_enter (l_proc_name);
2444 l_error_text := NULL;
2445 l_error_number := NULL;
2446 DEBUG ( 'Business Group ID: '
2447 || TO_CHAR (g_business_group_id));
2448 DEBUG ( 'Assignment ID: '
2449 || TO_CHAR (p_assignment_id));
2450 DEBUG ( 'Session Date: '
2451 || p_effective_date);
2452
2453 IF g_business_group_id IS NULL
2454 THEN
2455 g_pension_ele_ids.DELETE;
2456 g_pension_bal_name := NULL;
2457 g_pension_ele_name := NULL;
2458 g_initial_ext_date := NULL;
2459 g_emp_cont_iv_name := NULL;
2460 g_superann_refno_iv_name := NULL;
2461 g_superann_sal_bal_name := NULL;
2462 g_additional_cont_bal_name := NULL;
2463 g_buyback_cont_bal_name := NULL;
2464 g_superann_sal_bal_id := NULL;
2465 g_additional_cont_bal_id := NULL;
2466 g_buyback_cont_bal_id := NULL;
2467 g_ele_entry_details.DELETE;
2468 g_secondary_asg_ids.DELETE;
2469 g_asg_details.DELETE;
2470 g_ni_cont_out_ele_ids.DELETE;
2471 g_ni_ele_details.DELETE;
2472 g_ni_ele_type_id := NULL;
2473 g_ni_cat_iv_id := NULL;
2474 g_ni_pen_iv_id := NULL;
2475 g_pen_ele_details.DELETE;
2476 g_index := 0;
2477
2478
2479 -- Use STARTERS for starters, HOURCHANGE for hour change and ANNUAL
2480 -- for Annual report
2481 g_header_system_element := 'STARTERS:';
2482 DEBUG ('Before calling set_extract_globals function');
2483 l_return :=
2484 set_extract_globals (
2485 p_assignment_id=> p_assignment_id,
2486 p_business_group_id=> p_business_group_id,
2487 p_effective_date=> ben_ext_person.g_effective_date,
2488 -- Do not use the effective date (session date) as this may have been
2489 -- reset to terminated assignment end date for override rule
2490 p_error_number=> l_error_number,
2491 p_error_text=> l_error_text
2492 );
2493
2494 IF l_return <> 0
2495 THEN
2496 DEBUG ('Function set_extract_globals function is in Error');
2497 p_error_text := l_error_text;
2498 p_error_number := l_error_number;
2499 l_inclusion_flag := 'ERROR';
2500 debug_exit (l_proc_name);
2501 RETURN l_inclusion_flag;
2502 END IF; -- End if of return <> 0 check...
2503
2504 DEBUG ('Before calling get_NI_element_details procedure');
2505 -- get_ni_element_details;
2506 -- Move this function to set_extract_globals
2507 --
2508 -- DEBUG ('Before calling get_NI_cont_out_ele_details function');
2509 -- l_return :=
2510 -- get_ni_cont_out_ele_details (
2511 -- p_error_number=> l_error_number,
2512 -- p_error_text=> l_error_text
2513 -- );
2514 --
2515 -- IF l_return <> 0
2516 -- THEN
2517 -- DEBUG (
2518 -- 'Function get_NI_cont_out_ele_details function is in Error'
2519 -- );
2520 -- p_error_text := l_error_text;
2521 -- p_error_number := l_error_number;
2522 -- l_inclusion_flag := 'ERROR';
2523 -- debug_exit (l_proc_name);
2524 -- RETURN l_inclusion_flag;
2525 -- END IF; -- End if of return <> 0 check...
2526 END IF;
2527
2528 g_pen_ele_details.DELETE;
2529 g_index := 0;
2530
2531 DEBUG ('Before calling chk_is_employee_a_starter function');
2532 --
2533 -- Check the person is a member and a new starter
2534 --
2535
2536 l_inclusion_flag :=
2537 chk_is_employee_a_starter (
2538 p_assignment_id=> p_assignment_id,
2539 p_effective_start_date=> g_effective_start_date,
2540 p_effective_end_date=> g_effective_end_date
2541 );
2542 DEBUG ( 'Inclusion Flag: '
2543 || l_inclusion_flag);
2544
2545 IF l_inclusion_flag = 'Y'
2546 THEN
2547 DEBUG ('Assignment qualifies for starters');
2548 -- Populate assignment details
2549
2550 set_assignment_details (
2551 p_assignment_id=> p_assignment_id,
2552 p_effective_date=> g_ele_entry_details (p_assignment_id).effective_start_date
2553 );
2554 DEBUG ('Get Secondary Assignments');
2555 -- Get Secondary Assignments
2556
2557 DEBUG ('Before calling all secondary assignments procedure');
2558 get_all_sec_assignments (
2559 p_assignment_id=> p_assignment_id,
2560 p_secondary_asg_ids=> l_secondary_asg_ids
2561 );
2562 i := l_secondary_asg_ids.FIRST;
2563
2564 WHILE i IS NOT NULL
2565 LOOP
2566 DEBUG ('Secondary assignment exist');
2567 DEBUG ('Check this secondary asg qualifies for starters');
2568 DEBUG ('Before calling function chk_is_employee_a_starter');
2569
2570 IF chk_is_employee_a_starter (
2571 p_assignment_id=> l_secondary_asg_ids (i),
2572 p_effective_start_date=> g_effective_start_date,
2573 p_effective_end_date=> g_effective_end_date
2574 ) = 'Y'
2575 THEN
2576 DEBUG (
2577 TO_CHAR (l_secondary_asg_ids (i))
2578 || ' Secondary assignment qualifies'
2579 );
2580 g_secondary_asg_ids (i) := l_secondary_asg_ids (i);
2581 END IF; -- End if of secondary asg check for starters ..
2582
2583 i := l_secondary_asg_ids.NEXT (i);
2584 END LOOP; -- End loop of secondary assignments ...
2585 END IF; -- End if of inclusion Flag Check...
2586
2587 debug_exit (l_proc_name);
2588 RETURN l_inclusion_flag;
2589 EXCEPTION
2590 WHEN OTHERS
2591 THEN
2592 debug_exit ( ' Others in '
2593 || l_proc_name, 'Y' -- turn trace off
2594 );
2595 p_error_number := SQLCODE;
2596 p_error_text := SQLERRM;
2597 RAISE;
2598 END chk_employee_qual_for_starters;
2599
2600
2601 -- This function returns the superannuation reference number for a given
2602 -- assignment
2603 --
2604 -- ----------------------------------------------------------------------------
2605 -- |---------------------< get_superannuation_ref_no >------------------------|
2606 -- ----------------------------------------------------------------------------
2607 FUNCTION get_superannuation_ref_no (p_assignment_id IN NUMBER)
2608 RETURN VARCHAR2
2609 IS
2610
2611 --
2612 l_proc_name VARCHAR2 (61)
2613 := g_proc_name
2614 || 'get_superannuation_ref_no';
2615 l_superann_ref_no VARCHAR2 (60) := TRIM (
2616 RPAD (
2617 ' ',
2618 12,
2619 ' '
2620 )
2621 );
2622 l_input_value_id pay_input_values_f.input_value_id%TYPE;
2623
2624 --
2625 BEGIN
2626 debug_enter (l_proc_name);
2627
2628 IF g_superann_refno_iv_name IS NOT NULL
2629 THEN
2630 -- Call function to get the first element entry details for this
2631 -- assignment id
2632
2633
2634 IF g_ele_entry_details.EXISTS (p_assignment_id)
2635 THEN
2636 -- Get superannuation reference number
2637
2638 DEBUG ('Before calling get_ele_entry_value function');
2639 -- Get input value id for superannuation ref number
2640
2641 l_input_value_id :=
2642 get_input_value_id (
2643 p_element_type_id=> g_ele_entry_details (
2644 p_assignment_id
2645 ).element_type_id,
2646 p_input_value_name=> g_superann_refno_iv_name,
2647 p_effective_date=> g_ele_entry_details (p_assignment_id).effective_start_date
2648 );
2649 l_superann_ref_no :=
2650 get_ele_entry_value (
2651 p_element_entry_id=> g_ele_entry_details (
2652 p_assignment_id
2653 ).element_entry_id,
2654 p_input_value_id=> l_input_value_id,
2655 p_effective_start_date=> g_ele_entry_details (
2656 p_assignment_id
2657 ).effective_start_date,
2658 p_effective_end_date=> g_ele_entry_details (
2659 p_assignment_id
2660 ).effective_end_date
2661 );
2662 DEBUG (
2663 'Superannuation reference number is '
2664 || l_superann_ref_no
2665 );
2666 END IF; -- End if of element entry details exists check ...
2667 END IF; -- End if of superann ip value not null check ...
2668
2669 debug_exit (l_proc_name);
2670 RETURN l_superann_ref_no;
2671 EXCEPTION
2672 WHEN OTHERS
2673 THEN
2674 DEBUG ( ' Others in '
2675 || l_proc_name, 'Y' -- turn trace off
2676 );
2677 RAISE;
2678 END get_superannuation_ref_no;
2679
2680
2681 -- This function returns the employee contribution rate for the person
2682 --
2683 -- ----------------------------------------------------------------------------
2684 -- |--------------------------< get_emp_cont_rate >--------------------------|
2685 -- ----------------------------------------------------------------------------
2686 FUNCTION get_emp_cont_rate (p_assignment_id IN NUMBER)
2687 RETURN VARCHAR2
2688 IS
2689
2690 --
2691 l_proc_name VARCHAR2 (61)
2692 := g_proc_name
2693 || 'get_emp_cont_rate';
2694 l_emp_cont_rate VARCHAR2 (6) := '000000';
2695 l_rate NUMBER := 0;
2696 l_input_value_id pay_input_values_f.input_value_id%TYPE;
2697 l_secondary_asg_ids t_number;
2698 i NUMBER;
2699 l_value NUMBER;
2700
2701 --
2702 BEGIN
2703 debug_enter (l_proc_name);
2704
2705 IF g_emp_cont_iv_name IS NOT NULL
2706 THEN
2707 -- Call function to get the first element entry details for this
2708 -- assignment id
2709
2710 IF g_ele_entry_details.EXISTS (p_assignment_id)
2711 THEN
2712 -- Get input value id for superannuation ref number
2713 DEBUG ('Before calling get_input_value_id function');
2714 l_input_value_id :=
2715 get_input_value_id (
2716 p_element_type_id=> g_ele_entry_details (
2717 p_assignment_id
2718 ).element_type_id,
2719 p_input_value_name=> g_emp_cont_iv_name,
2720 p_effective_date=> g_ele_entry_details (p_assignment_id).effective_start_date
2721 );
2722 DEBUG ( 'Input Value ID: '
2723 || TO_CHAR (l_input_value_id));
2724 DEBUG ('Before calling get_ele_entry_value function');
2725 l_rate :=
2726 NVL (
2727 TO_NUMBER (
2728 get_ele_entry_value (
2729 p_element_entry_id=> g_ele_entry_details (
2730 p_assignment_id
2731 ).element_entry_id,
2732 p_input_value_id=> l_input_value_id,
2733 p_effective_start_date=> g_ele_entry_details (
2734 p_assignment_id
2735 ).effective_start_date,
2736 p_effective_end_date=> g_ele_entry_details (
2737 p_assignment_id
2738 ).effective_end_date
2739 )
2740 ),
2741 0
2742 );
2743 DEBUG ( 'Contribution Rate is '
2744 || l_rate);
2745 END IF; -- End if of element entry details exists check ...
2746
2747 -- Coomented to report only contribution from Primary assignment
2748 -- Bug 5459147 Contribution from secondary assignments should not be cosnidered.
2749 /*
2750 -- Check for secondary assignments
2751
2752 get_eligible_sec_assignments (
2753 p_assignment_id=> p_assignment_id,
2754 p_secondary_asg_ids=> l_secondary_asg_ids
2755 );
2756 i := l_secondary_asg_ids.FIRST;
2757
2758 WHILE i IS NOT NULL
2759 LOOP
2760 IF g_ele_entry_details.EXISTS (i)
2761 THEN
2762 -- Get input value id for Contribution Rate
2763 DEBUG ('Before calling get_input_value_id function');
2764 l_input_value_id :=
2765 get_input_value_id (
2766 p_element_type_id=> g_ele_entry_details (
2767 l_secondary_asg_ids (i)
2768 ).element_type_id,
2769 p_input_value_name=> g_emp_cont_iv_name,
2770 p_effective_date=> g_ele_entry_details (
2771 l_secondary_asg_ids (i)
2772 ).effective_start_date
2773 );
2774 DEBUG ( 'Input Value ID: '
2775 || TO_CHAR (l_input_value_id));
2776 DEBUG ('Before calling get_ele_entry_value function');
2777 l_rate :=
2778 l_rate
2779 + NVL (
2780 TO_NUMBER (
2781 get_ele_entry_value (
2782 p_element_entry_id=> g_ele_entry_details (
2783 l_secondary_asg_ids (i)
2784 ).element_entry_id,
2785 p_input_value_id=> l_input_value_id,
2786 p_effective_start_date=> g_ele_entry_details (
2787 l_secondary_asg_ids (i)
2788 ).effective_start_date,
2789 p_effective_end_date=> g_ele_entry_details (
2790 l_secondary_asg_ids (i)
2791 ).effective_end_date
2792 )
2793 ),
2794 0
2795 );
2796 DEBUG ( 'Contribution Rate is '
2797 || l_rate);
2798 END IF; -- End if of element entry details exists check ...
2799
2800 i := l_secondary_asg_ids.NEXT (i);
2801 END LOOP; -- End loop of secondary asgn ...
2802 */
2803 END IF; -- End if of emp cont rate not null check ...
2804
2805 l_rate := l_rate * 100;
2806
2807 -- Bug Fix 5021075
2808 IF l_rate > 999999
2809 THEN
2810 l_rate := 999999;
2811 ELSIF l_rate < 0 THEN
2812 l_value := pqp_gb_tp_extract_functions.raise_extract_error
2813 (p_business_group_id => g_business_group_id
2814 ,p_assignment_id => p_assignment_id
2815 ,p_error_text => 'BEN_94556_EXT_VALUE_ERROR'
2816 ,p_error_number => 94556
2817 ,p_token1 => 'Employee Contribution Rate'
2818 ,p_fatal_flag => 'N'
2819 );
2820 END IF; -- End if of rate exceed max limit check ...
2821
2822 IF l_rate >= 0 THEN
2823 l_emp_cont_rate := TRIM (TO_CHAR ((l_rate), '099999'));
2824 ELSE
2825 l_emp_cont_rate := TRIM (TO_CHAR ((l_rate), 'S09999'));
2826 END IF;
2827 DEBUG ( 'Emp Contribution: '
2828 || l_emp_cont_rate);
2829 debug_exit (l_proc_name);
2830 RETURN l_emp_cont_rate;
2831 EXCEPTION
2832 WHEN OTHERS
2833 THEN
2834 DEBUG ( ' Others in '
2835 || l_proc_name, 'Y' -- turn trace off
2836 );
2837 RAISE;
2838 END get_emp_cont_rate;
2839
2840
2841 -- This function returns the scheme number for the given assignment
2842 --
2843 -- ----------------------------------------------------------------------------
2844 -- |-----------------------< get_scheme_number >-----------------------------|
2845 -- ----------------------------------------------------------------------------
2846 FUNCTION get_scheme_number (
2847 p_assignment_id IN NUMBER,
2848 p_scheme_number OUT NOCOPY VARCHAR2,
2849 p_error_number OUT NOCOPY NUMBER,
2850 p_error_text OUT NOCOPY VARCHAR2
2851 )
2852 RETURN NUMBER
2853 IS
2854
2855 --
2856 l_proc_name VARCHAR2 (60) := g_proc_name
2857 || 'get_scheme_number';
2858 l_scheme_number pay_element_type_extra_info.eei_information1%TYPE
2859 := TRIM (RPAD (' ', 3, ' '));
2860 l_return NUMBER;
2861 l_error_text VARCHAR2 (200);
2862 l_truncated VARCHAR2 (1);
2863
2864 --
2865 BEGIN
2866 debug_enter (l_proc_name);
2867 -- Get the element type id from the global collection
2868 DEBUG ('Get the element type id from the global collection');
2869
2870 IF g_ele_entry_details.EXISTS (p_assignment_id)
2871 THEN
2872 -- Get the scheme number from the element extra info type
2873 DEBUG ('Get the scheme number from the element EIT');
2874 -- Call function pqp_utility_function
2875 DEBUG (
2876 'Before calling function pqp_utilities.pqp_get_extra_element_info'
2877 );
2878 l_return := 0;
2879 l_return :=
2880 pqp_utilities.pqp_get_extra_element_info (
2881 p_element_type_id=> g_ele_entry_details (p_assignment_id).element_type_id,
2882 p_information_type=> 'PQP_GB_PENSION_SCHEME_INFO',
2883 p_segment_name=> 'Scheme Number',
2884 p_value=> l_scheme_number,
2885 p_truncated_yes_no=> l_truncated,
2886 p_error_msg=> l_error_text
2887 );
2888 DEBUG ( 'Scheme Number: '
2889 || l_scheme_number);
2890
2891 IF l_return <> 0
2892 THEN
2893 -- Error Occurred
2894 DEBUG ( 'Error Occurred report error '
2895 || l_error_text);
2896 p_error_text := l_error_text;
2897 ELSIF l_scheme_number IS NULL
2898 THEN -- l_return = 0
2899 -- Raise mandatory message
2900 DEBUG ('Scheme Number is mandatory');
2901 p_error_text := 'Scheme number is missing.';
2902 l_return := -1;
2903 END IF; -- End if of error check ...
2904 END IF; -- End if of element entry details exist check ...
2905
2906 p_scheme_number := TRIM (RPAD (l_scheme_number, 3, ' '));
2907 DEBUG ( 'Scheme Number: '
2908 || l_scheme_number);
2909 debug_exit (l_proc_name);
2910 RETURN l_return;
2911 EXCEPTION
2912 WHEN OTHERS
2913 THEN
2914 DEBUG ( ' Others in '
2915 || l_proc_name, 'Y' -- turn trace off
2916 );
2917 p_scheme_number := NULL;
2918 p_error_number := SQLCODE;
2919 p_error_text := SQLERRM;
2920 RAISE;
2921 END get_scheme_number;
2922
2923
2924 -- This function returns the employer reference number for the assignment
2925 --
2926 -- ----------------------------------------------------------------------------
2927 -- |-----------------------< get_employer_reference_number >------------------|
2928 -- ----------------------------------------------------------------------------
2929 FUNCTION get_employer_reference_number (
2930 p_assignment_id IN NUMBER,
2931 p_employer_ref_no OUT NOCOPY VARCHAR2,
2932 p_error_number OUT NOCOPY NUMBER,
2933 p_error_text OUT NOCOPY VARCHAR2
2934 )
2935 RETURN NUMBER
2936 IS
2937
2938 --
2939 l_proc_name VARCHAR2 (60)
2940 := g_proc_name
2941 || 'get_employer_reference_number';
2942 l_employer_ref_no pay_element_type_extra_info.eei_information1%TYPE
2943 := TRIM (RPAD (' ', 10, ' '));
2944 l_return NUMBER;
2945 l_error_text VARCHAR2 (200);
2946 l_truncated VARCHAR2 (1);
2947
2948 --
2949 BEGIN
2950 debug_enter (l_proc_name);
2951 -- Get the element type id from the global collection
2952 DEBUG ('Get the element type id from the global collection');
2953
2954 IF g_ele_entry_details.EXISTS (p_assignment_id)
2955 THEN
2956 -- Get the scheme number from the element extra info type
2957 DEBUG ('Get the employer number from the element EIT');
2958 -- Call function pqp_utility_function
2959 DEBUG (
2960 'Before calling function pqp_utilities.pqp_get_extra_element_info'
2961 );
2962 l_return := 0;
2963 l_return :=
2964 pqp_utilities.pqp_get_extra_element_info (
2965 p_element_type_id=> g_ele_entry_details (p_assignment_id).element_type_id,
2966 p_information_type=> 'PQP_GB_PENSION_SCHEME_INFO',
2967 p_segment_name=> 'Employer Reference Number',
2968 p_value=> l_employer_ref_no,
2969 p_truncated_yes_no=> l_truncated,
2970 p_error_msg=> l_error_text
2971 );
2972 DEBUG ( 'Employer Reference Number: '
2973 || l_employer_ref_no);
2974
2975 IF l_return <> 0
2976 THEN
2977 -- Error Occurred
2978 DEBUG ('Error Occurred report error');
2979 p_error_text := l_error_text;
2980 ELSIF l_employer_ref_no IS NULL
2981 THEN -- l_return = 0
2982 -- Raise mandatory message
2983 DEBUG ('Employer Reference Number is mandatory');
2984 p_error_text := 'Employer reference number is missing.';
2985 l_return := -1;
2986 END IF; -- End if of error check ...
2987 END IF; -- End if of element entry details exist check ...
2988
2989 p_employer_ref_no := TRIM (RPAD (l_employer_ref_no, 10, ' '));
2990 DEBUG ( 'Employer Reference Number: '
2991 || l_employer_ref_no);
2992 debug_exit (l_proc_name);
2993 RETURN l_return;
2994 EXCEPTION
2995 WHEN OTHERS
2996 THEN
2997 DEBUG ( ' Others in '
2998 || l_proc_name, 'Y' -- turn trace off
2999 );
3000 p_employer_ref_no := NULL;
3001 p_error_number := SQLCODE;
3002 p_error_text := SQLERRM;
3003 RAISE;
3004 END get_employer_reference_number;
3005
3006
3007 -- This function returns the date the person joined the pension fund
3008 --
3009 -- ----------------------------------------------------------------------------
3010 -- |-----------------------< get_date_joined_pens_fund >----------------------|
3011 -- ----------------------------------------------------------------------------
3012 FUNCTION get_date_joined_pens_fund (
3013 p_assignment_id IN NUMBER,
3014 p_dt_joined_pens OUT NOCOPY DATE,
3015 p_error_number OUT NOCOPY NUMBER,
3016 p_error_text OUT NOCOPY VARCHAR2
3017 )
3018 RETURN NUMBER
3019 IS
3020
3021 --
3022 l_proc_name VARCHAR2 (60)
3023 := g_proc_name
3024 || 'get_date_joined_pens_fund';
3025 l_dt_joined_pens DATE;
3026 l_return NUMBER;
3027 l_input_value_id pay_input_values_f.input_value_id%TYPE;
3028
3029 --
3030 BEGIN
3031 debug_enter (l_proc_name);
3032 -- Determine the Override Start Date
3033 DEBUG ('Determine the Override Start Date');
3034
3035 IF g_ele_entry_details.EXISTS (p_assignment_id)
3036 THEN
3037 -- Get input value id for Override Start Date
3038 DEBUG ('Before calling get_input_value_id function');
3039 l_input_value_id :=
3040 get_input_value_id (
3041 p_element_type_id=> g_ele_entry_details (p_assignment_id).element_type_id,
3042 p_input_value_name=> 'Override Start Date',
3043 p_effective_date=> g_ele_entry_details (p_assignment_id).effective_start_date
3044 );
3045 DEBUG ( 'Input Value ID: '
3046 || TO_CHAR (l_input_value_id));
3047
3048 IF l_input_value_id IS NOT NULL
3049 THEN
3050 DEBUG ('Before calling get_ele_entry_value function');
3051 l_dt_joined_pens :=
3052 fnd_date.canonical_to_date (
3053 get_ele_entry_value (
3054 p_element_entry_id=> g_ele_entry_details (
3055 p_assignment_id
3056 ).element_entry_id,
3057 p_input_value_id=> l_input_value_id,
3058 p_effective_start_date=> g_ele_entry_details (
3059 p_assignment_id
3060 ).effective_start_date,
3061 p_effective_end_date=> g_ele_entry_details (
3062 p_assignment_id
3063 ).effective_end_date
3064 )
3065 );
3066 END IF; -- End if of input value id not null check ...
3067
3068 DEBUG ( 'Date Joined Pens Fund: '
3069 || l_dt_joined_pens);
3070
3071 IF l_dt_joined_pens IS NULL
3072 THEN
3073 l_dt_joined_pens :=
3074 g_ele_entry_details (p_assignment_id).effective_start_date;
3075 END IF; -- End if of override start date is null check ...
3076 END IF; -- End if of element entry details exist check ...
3077
3078 IF l_dt_joined_pens IS NULL
3079 THEN
3080 DEBUG ('Raise Error');
3081 p_error_text := 'Date joined pension fund is missing';
3082 l_return := -1;
3083 ELSE -- date joined pension fund has a value ...
3084 p_dt_joined_pens := l_dt_joined_pens;
3085 l_return := 0;
3086 END IF; -- End if of date joined pens is null check ...
3087
3088 DEBUG ( 'Date Joined Pens Fund: '
3089 || l_dt_joined_pens);
3090 debug_exit (l_proc_name);
3091 RETURN l_return;
3092 EXCEPTION
3093 WHEN OTHERS
3094 THEN
3095 DEBUG ( ' Others in '
3096 || l_proc_name, 'Y' -- turn trace off
3097 );
3098 p_dt_joined_pens := NULL;
3099 p_error_number := SQLCODE;
3100 p_error_text := SQLERRM;
3101 RAISE;
3102 END get_date_joined_pens_fund;
3103
3104
3105 -- This function returns the first (MIN) date the person contracted out of
3106 -- National Insurance Contributions
3107 --
3108 -- ----------------------------------------------------------------------------
3109 -- |-------------------------< get_date_contracted_out >----------------------|
3110 -- ----------------------------------------------------------------------------
3111 FUNCTION get_date_contracted_out (
3112 p_assignment_id IN NUMBER,
3113 p_dt_cont_out OUT NOCOPY DATE,
3114 p_error_number OUT NOCOPY NUMBER,
3115 p_error_text OUT NOCOPY VARCHAR2
3116 )
3117 RETURN NUMBER
3118 IS
3119
3120 --
3121
3122 CURSOR csr_get_ni_ele_info (c_effective_date DATE)
3123 IS
3124 SELECT pee.element_entry_id, pee.effective_start_date
3125 ,pee.effective_end_date
3126 FROM pay_element_entries_f pee, pay_element_links_f pel
3127 WHERE pee.assignment_id = p_assignment_id
3128 AND pee.entry_type = 'E'
3129 AND pee.element_link_id = pel.element_link_id
3130 AND c_effective_date BETWEEN pee.effective_start_date
3131 AND pee.effective_end_date
3132 AND pel.element_type_id = g_ni_ele_type_id
3133 AND c_effective_date BETWEEN pel.effective_start_date
3134 AND pel.effective_end_date
3135 ORDER BY pee.effective_start_date;
3136
3137 l_proc_name VARCHAR2 (60)
3138 := g_proc_name
3139 || 'get_date_contracted_out';
3140 l_dt_cont_out DATE := NULL;
3141 l_return NUMBER;
3142 i NUMBER;
3143 l_min_start_date DATE := NULL;
3144 l_rec_ni_ele_info csr_get_ni_ele_info%ROWTYPE;
3145 l_effective_date DATE;
3146 l_ni_category VARCHAR2(10);
3147
3148 --
3149 BEGIN
3150 debug_enter (l_proc_name);
3151 -- The contracted out elements should be available from the
3152 -- Global Collection
3153
3154 -- Bug Fix 4721921
3155 l_effective_date := LEAST (
3156 g_ele_entry_details (p_assignment_id).effective_end_date,
3157 g_effective_end_date
3158 );
3159 OPEN csr_get_ni_ele_info (l_effective_date);
3160 LOOP
3161 FETCH csr_get_ni_ele_info INTO l_rec_ni_ele_info;
3162 EXIT WHEN csr_get_ni_ele_info%NOTFOUND;
3163
3164 l_ni_category := get_ele_entry_value
3165 (p_element_entry_id => l_rec_ni_ele_info.element_entry_id
3166 ,p_input_value_id => g_ni_cat_iv_id
3167 ,p_effective_start_date => l_rec_ni_ele_info.effective_start_date
3168 ,p_effective_end_date => l_rec_ni_ele_info.effective_end_date
3169 );
3170
3171
3172 i := g_ni_cont_out_ele_ids.FIRST;
3173 l_dt_cont_out := NULL;
3174 l_min_start_date := NULL;
3175
3176 WHILE i IS NOT NULL
3177 LOOP
3178 -- Retrieve the min effective start date
3179 DEBUG ( 'NI Category : '
3180 || g_ni_cont_out_ele_ids (i).category);
3181 IF l_ni_category = g_ni_cont_out_ele_ids (i).category
3182 THEN
3183 l_min_start_date := l_rec_ni_ele_info.effective_start_date;
3184 l_dt_cont_out := l_min_start_date;
3185 EXIT;
3186 END IF;
3187
3188
3189 -- OPEN csr_get_ele_ent_min_start_dt (
3190 -- p_assignment_id,
3191 -- g_ni_cont_out_ele_ids (i).category
3192 -- );
3193 -- FETCH csr_get_ele_ent_min_start_dt INTO l_min_start_date;
3194 -- CLOSE csr_get_ele_ent_min_start_dt;
3195 DEBUG ( 'Min start date: '
3196 || l_min_start_date);
3197
3198 -- IF NVL (l_min_start_date, hr_api.g_eot) <
3199 -- NVL (l_dt_cont_out, hr_api.g_eot)
3200 -- -- hr_api.g_eot = 31/12/4712
3201 -- THEN
3202 -- l_dt_cont_out := l_min_start_date;
3203 -- END IF; -- End if of min start date check ...
3204
3205 DEBUG ( 'Date Contracted Out: '
3206 || l_dt_cont_out);
3207 i := g_ni_cont_out_ele_ids.NEXT (i);
3208 END LOOP; -- End loop of cont out ele ids ...
3209 DEBUG ( 'Min start date: '
3210 || l_min_start_date);
3211 DEBUG ( 'Date Contracted Out: '
3212 || l_dt_cont_out);
3213 IF l_dt_cont_out IS NOT NULL THEN
3214 EXIT;
3215 END IF;
3216
3217 END LOOP; -- End loop of asg cursor
3218 CLOSE csr_get_ni_ele_info;
3219
3220 DEBUG ( 'Final Date Contracted Out: '
3221 || l_dt_cont_out);
3222
3223 IF l_dt_cont_out IS NULL
3224 THEN
3225 DEBUG ('Date Contracted OUT missing');
3226 p_error_text := 'Date contracted out is missing';
3227 l_return := -1;
3228 ELSE -- date cont out exists
3229 p_dt_cont_out := l_dt_cont_out;
3230 l_return := 0;
3231 END IF; -- End if of date cont out is null check ...
3232
3233 debug_exit (l_proc_name);
3234 RETURN l_return;
3235 EXCEPTION
3236 WHEN OTHERS
3237 THEN
3238 DEBUG ( ' Others in '
3239 || l_proc_name, 'Y' -- turn trace off
3240 );
3241 p_dt_cont_out := NULL;
3242 p_error_number := SQLCODE;
3243 p_error_text := SQLERRM;
3244 RAISE;
3245 END get_date_contracted_out;
3246
3247
3248 -- This function returns the part time indicator information for the assignment
3249 --
3250 -- ----------------------------------------------------------------------------
3251 -- |-------------------------< get_part_time_indicator >----------------------|
3252 -- ----------------------------------------------------------------------------
3253 FUNCTION get_part_time_indicator (
3254 p_assignment_id IN NUMBER,
3255 p_effective_date IN DATE
3256 )
3257 RETURN VARCHAR2
3258 IS
3259
3260 --
3261 l_proc_name VARCHAR2 (60)
3262 := g_proc_name
3263 || 'get_part_time_indicator';
3264 l_part_time_ind VARCHAR2 (1) := ' ';
3265 l_asg_employment_cat hr_lookups.lookup_code%TYPE;
3266 l_error_text VARCHAR2 (200);
3267 l_return NUMBER;
3268
3269 --
3270 BEGIN
3271 --
3272 debug_enter (l_proc_name);
3273 -- Get the assignment employment category
3274
3275 DEBUG ('Before calling function get_asg_employment_category');
3276 l_asg_employment_cat :=
3277 get_asg_employment_cat (
3278 p_assignment_id=> p_assignment_id,
3279 p_effective_date=> p_effective_date
3280 );
3281
3282 IF l_asg_employment_cat IS NOT NULL
3283 THEN
3284 -- Get the part time translation code from the UDT
3285 DEBUG ('Before calling get_udt_translated_code function');
3286 l_part_time_ind :=
3287 get_udt_translated_code (
3288 p_user_table_name=> 'PQP_GB_TP_EMPLOYMENT_CATEGORY_TRANSALATION_TABLE',
3289 p_effective_date=> g_effective_date,
3290 p_asg_user_col_name=> 'Assignment Employment Category Lookup Code',
3291 p_ext_user_col_name=> 'Lynx Heywood Employment Category Code',
3292 p_value=> l_asg_employment_cat
3293 );
3294 DEBUG ( 'Part Time Indicator: '
3295 || l_part_time_ind);
3296 END IF; -- End if of asg employment cat is not null check ...
3297
3298 l_part_time_ind := NVL (l_part_time_ind, ' ');
3299 debug_exit (l_proc_name);
3300 RETURN l_part_time_ind;
3301 --
3302 END get_part_time_indicator;
3303
3304
3305 -- This function should be called from the fast formula and is a wrapper to the
3306 -- low level function get_part_time_indicator
3307 --
3308 -- ----------------------------------------------------------------------------
3309 -- |-------------------------< get_STARTERS_part_time_ind >-------------------|
3310 -- ----------------------------------------------------------------------------
3311 FUNCTION get_starters_part_time_ind (p_assignment_id IN NUMBER)
3312 RETURN VARCHAR2
3313 IS
3314
3315 --
3316 l_proc_name VARCHAR2 (60)
3317 := g_proc_name
3318 || 'get_STARTERS_part_time_ind';
3319 l_part_time_ind VARCHAR2 (1);
3320
3321 --
3322 BEGIN
3323 --
3324 debug_enter (l_proc_name);
3325 l_part_time_ind :=
3326 get_part_time_indicator (
3327 p_assignment_id=> p_assignment_id,
3328 p_effective_date=> g_ele_entry_details (p_assignment_id).effective_start_date
3329 );
3330 debug_exit (l_proc_name);
3331 RETURN l_part_time_ind;
3332 --
3333
3334 EXCEPTION
3335 WHEN OTHERS
3336 THEN
3337 DEBUG ( ' Others in '
3338 || l_proc_name, 'Y' -- turn trace off
3339 );
3340 RAISE;
3341 END get_starters_part_time_ind;
3342
3343
3344 -- This function should be called from the fast formula and is a wrapper to the
3345 -- low level function get_part_time_indicator
3346 --
3347 -- ----------------------------------------------------------------------------
3348 -- |-------------------------< get_CPX_part_time_ind >-------------------|
3349 -- ----------------------------------------------------------------------------
3350 FUNCTION get_cpx_part_time_ind (p_assignment_id IN NUMBER)
3351 RETURN VARCHAR2
3352 IS
3353
3354 --
3355 l_proc_name VARCHAR2 (60)
3356 := g_proc_name
3357 || 'get_CPX_part_time_ind';
3358 l_part_time_ind VARCHAR2 (1);
3359
3360 --
3361 BEGIN
3362 --
3363 debug_enter (l_proc_name);
3364 l_part_time_ind :=
3365 get_part_time_indicator (
3366 p_assignment_id=> p_assignment_id,
3367 p_effective_date=> LEAST (
3368 g_ele_entry_details (p_assignment_id).effective_end_date,
3369 g_effective_end_date
3370 )
3371 );
3372 debug_exit (l_proc_name);
3373 RETURN l_part_time_ind;
3374 --
3375
3376 EXCEPTION
3377 WHEN OTHERS
3378 THEN
3379 DEBUG ( ' Others in '
3380 || l_proc_name, 'Y' -- turn trace off
3381 );
3382 RAISE;
3383 END get_cpx_part_time_ind;
3384
3385
3386 -- This function returns the marital status for the person
3387 --
3388 -- ----------------------------------------------------------------------------
3389 -- |----------------------------< get_marital_status >------------------------|
3390 -- ----------------------------------------------------------------------------
3391 FUNCTION get_marital_status (
3392 p_assignment_id IN NUMBER,
3393 p_effective_date IN DATE
3394 )
3395 RETURN VARCHAR2
3396 IS
3397
3398 --
3399 l_proc_name VARCHAR2 (60)
3400 := g_proc_name
3401 || 'get_marital_status';
3402 l_person_marital_sts VARCHAR2 (30);
3403 l_pens_marital_status VARCHAR2 (1) := ' ';
3404 l_return NUMBER;
3405 l_error_text VARCHAR2 (200);
3406
3407 --
3408 BEGIN
3409 --
3410 debug_enter (l_proc_name);
3411 -- Get the person marital status
3412
3413 OPEN csr_get_marital_status (
3414 g_asg_details (p_assignment_id).person_id,
3415 p_effective_date
3416 );
3417 FETCH csr_get_marital_status INTO l_person_marital_sts;
3418 CLOSE csr_get_marital_status;
3419 DEBUG ( 'Person Marital Status: '
3420 || l_person_marital_sts);
3421
3422 IF l_person_marital_sts IS NOT NULL
3423 THEN
3424 -- Get the marital status from UDT
3425 DEBUG ('Before calling get_udt_translated_code function');
3426 l_pens_marital_status :=
3427 get_udt_translated_code (
3428 p_user_table_name=> 'PQP_GB_LYNX_HEYWOOD_MARITAL_STATUS_TABLE',
3429 p_effective_date=> g_effective_date,
3430 p_asg_user_col_name=> 'Person Marital Status Lookup Value',
3431 p_ext_user_col_name=> 'Pension Extracts Marital Status Code',
3432 p_value=> l_person_marital_sts
3433 );
3434 END IF; -- End if of person marital status not null check ...
3435
3436 DEBUG ( 'Pension Marital Status: '
3437 || l_pens_marital_status);
3438 l_pens_marital_status := NVL (l_pens_marital_status, ' ');
3439 debug_exit (l_proc_name);
3440 RETURN l_pens_marital_status;
3441 END get_marital_status;
3442
3443
3444 -- This function should be called by the fast formula, this in turn calls the
3445 -- low level function get_marital_status
3446 --
3447 -- ----------------------------------------------------------------------------
3448 -- |-------------------------< get_STARTERS_marital_status >------------------|
3449 -- ----------------------------------------------------------------------------
3450 FUNCTION get_starters_marital_status (p_assignment_id IN NUMBER)
3451 RETURN VARCHAR2
3452 IS
3453
3454 --
3455 l_proc_name VARCHAR2 (60)
3456 := g_proc_name
3457 || 'get_STARTERS_marital_status';
3458 l_marital_status VARCHAR2 (1);
3459
3460 --
3461 BEGIN
3462 --
3463 debug_enter (l_proc_name);
3464 l_marital_status :=
3465 get_marital_status (
3466 p_assignment_id=> p_assignment_id,
3467 p_effective_date=> g_ele_entry_details (p_assignment_id).effective_start_date
3468 );
3469 debug_exit (l_proc_name);
3470 RETURN l_marital_status;
3471 --
3472
3473 EXCEPTION
3474 WHEN OTHERS
3475 THEN
3476 DEBUG ( ' Others in '
3477 || l_proc_name, 'Y' -- turn trace off
3478 );
3479 RAISE;
3480 END get_starters_marital_status;
3481
3482
3483 -- This function should be called by the fast formula, this in turn calls the
3484 -- low level function get_marital_status
3485 --
3486 -- ----------------------------------------------------------------------------
3487 -- |-------------------------< get_CPX_marital_status >-----------------------|
3488 -- ----------------------------------------------------------------------------
3489 FUNCTION get_cpx_marital_status (p_assignment_id IN NUMBER)
3490 RETURN VARCHAR2
3491 IS
3492
3493 --
3494 l_proc_name VARCHAR2 (60)
3495 := g_proc_name
3496 || 'get_CPX_marital_status';
3497 l_marital_status VARCHAR2 (1);
3498
3499 --
3500 BEGIN
3501 --
3502 debug_enter (l_proc_name);
3503 l_marital_status :=
3504 get_marital_status (
3505 p_assignment_id=> p_assignment_id,
3506 p_effective_date=> LEAST (
3507 g_ele_entry_details (p_assignment_id).effective_end_date,
3508 g_effective_end_date
3509 )
3510 );
3511 debug_exit (l_proc_name);
3512 RETURN l_marital_status;
3513 --
3514
3515 EXCEPTION
3516 WHEN OTHERS
3517 THEN
3518 DEBUG ( ' Others in '
3519 || l_proc_name, 'Y' -- turn trace off
3520 );
3521 RAISE;
3522 END get_cpx_marital_status;
3523
3524
3525 -- This function returns the spouses date of birth information for the person
3526 --
3527 -- ----------------------------------------------------------------------------
3528 -- |-------------------------< get_spouses_date_of_birth >--------------------|
3529 -- ----------------------------------------------------------------------------
3530 FUNCTION get_spouses_date_of_birth (p_assignment_id IN NUMBER)
3531 RETURN DATE
3532 IS
3533
3534 --
3535 l_proc_name VARCHAR2 (60)
3536 := g_proc_name
3537 || 'get_spouses_date_of_birth';
3538 l_spouses_details csr_get_spouses_details%ROWTYPE;
3539
3540 --
3541 BEGIN
3542 --
3543 debug_enter (l_proc_name);
3544 -- Get contact details
3545 OPEN csr_get_spouses_details (
3546 g_asg_details (p_assignment_id).person_id,
3547 g_ele_entry_details (p_assignment_id).effective_start_date
3548 );
3549 FETCH csr_get_spouses_details INTO l_spouses_details;
3550 CLOSE csr_get_spouses_details;
3551 DEBUG ( 'Spouses DOB'
3552 || l_spouses_details.date_of_birth);
3553 debug_exit (l_proc_name);
3554 RETURN l_spouses_details.date_of_birth;
3555 --
3556
3557 EXCEPTION
3558 WHEN OTHERS
3559 THEN
3560 DEBUG ( ' Others in '
3561 || l_proc_name, 'Y' -- turn trace off
3562 );
3563 RAISE;
3564 END get_spouses_date_of_birth;
3565
3566
3567 -- This function returns the spouses' initials for the person
3568 --
3569 -- ----------------------------------------------------------------------------
3570 -- |-------------------------< get_spouses_initials >-------------------------|
3571 -- ----------------------------------------------------------------------------
3572 FUNCTION get_spouses_initials (p_assignment_id IN NUMBER)
3573 RETURN VARCHAR2
3574 IS
3575
3576 --
3577 l_proc_name VARCHAR2 (60)
3578 := g_proc_name
3579 || 'get_spouses_initials';
3580 l_spouses_details csr_get_spouses_details%ROWTYPE;
3581 l_space_position NUMBER;
3582 l_spouses_initials VARCHAR2 (2) := TRIM (
3583 RPAD (
3584 ' ',
3585 2,
3586 ' '
3587 )
3588 );
3589 l_spouses_finitials VARCHAR2 (2);
3590
3591 --
3592 BEGIN
3593 --
3594 debug_enter (l_proc_name);
3595 -- Get contact details
3596 OPEN csr_get_spouses_details (
3597 g_asg_details (p_assignment_id).person_id,
3598 g_ele_entry_details (p_assignment_id).effective_start_date
3599 );
3600 FETCH csr_get_spouses_details INTO l_spouses_details;
3601 CLOSE csr_get_spouses_details;
3602 DEBUG ( 'First Name: '
3603 || l_spouses_details.first_name);
3604 DEBUG ( 'Middle Names: '
3605 || l_spouses_details.middle_names);
3606
3607 -- Check first name exists
3608
3609 IF l_spouses_details.first_name IS NOT NULL
3610 THEN
3611 -- Get the first character from first name
3612 l_spouses_finitials := SUBSTR (l_spouses_details.first_name, 1, 1);
3613 DEBUG ( 'Spouses Finitials: '
3614 || l_spouses_finitials);
3615 -- Check whether the first name has two name components
3616 l_space_position := INSTR (l_spouses_details.first_name, ' ', 1);
3617
3618 IF l_space_position <> 0
3619 THEN
3620 l_spouses_finitials := l_spouses_finitials
3621 || SUBSTR (
3622 l_spouses_details.first_name,
3623 ( l_space_position
3624 + 1
3625 ),
3626 1
3627 );
3628 DEBUG ( 'Spouses Initials: '
3629 || l_spouses_finitials);
3630 END IF; -- End if of space position check ...
3631 END IF; -- End if of first name not null check ...
3632
3633 -- Check whether the initial has first two characters
3634
3635 IF LENGTH (NVL (l_spouses_finitials, 0)) < 2
3636 THEN
3637 IF l_spouses_details.middle_names IS NOT NULL
3638 THEN
3639 -- Get the first character from middle name
3640 l_spouses_finitials := l_spouses_finitials
3641 || SUBSTR (
3642 l_spouses_details.middle_names,
3643 1,
3644 1
3645 );
3646 END IF; -- End if of middle name not null check ...
3647 END IF; -- End if of length check ...
3648
3649 l_spouses_initials := TRIM (RPAD (l_spouses_finitials, 2, ' '));
3650 DEBUG ( 'Spouses Initials: '
3651 || l_spouses_initials);
3652 debug_exit (l_proc_name);
3653 RETURN l_spouses_initials;
3654 --
3655
3656 EXCEPTION
3657 WHEN OTHERS
3658 THEN
3659 DEBUG ( ' Others in '
3660 || l_proc_name, 'Y' -- turn trace off
3661 );
3662 RAISE;
3663 END get_spouses_initials;
3664
3665
3666 -- This function returns the National insurance indicator for the assignment
3667 -- the indicator includes the reduced contribution indicator and the category
3668 --
3669 -- ----------------------------------------------------------------------------
3670 -- |-------------------------< get_NI_indicator >-----------------------------|
3671 -- ----------------------------------------------------------------------------
3672 FUNCTION get_ni_indicator (
3673 p_assignment_id IN NUMBER,
3674 p_effective_date IN DATE
3675 )
3676 RETURN VARCHAR2
3677 IS
3678
3679 --
3680 CURSOR csr_get_ni_red_ind (c_column_name VARCHAR2)
3681 IS
3682 SELECT DECODE (
3683 c_column_name,
3684 'P = Reduced Rate Conts but now Full Rate', 'P',
3685 'Y = Reduced Rate Conts Current', 'Y'
3686 )
3687 FROM DUAL;
3688
3689 --
3690
3691 l_proc_name VARCHAR2 (60)
3692 := g_proc_name
3693 || 'get_NI_indicator';
3694 l_asg_ni_ele_details csr_get_asg_ni_ele_info%ROWTYPE;
3695 l_max_start_date DATE;
3696 l_ni_table_letter VARCHAR2 (1) := ' ';
3697 l_ni_reduced_ind VARCHAR2 (1) := ' ';
3698 l_ni_indicator VARCHAR2 (2);
3699 l_user_col_coll t_varchar2;
3700 i NUMBER;
3701 l_user_table_id NUMBER;
3702 l_user_row_id NUMBER;
3703 l_rec_ni_ele_info csr_get_asg_ni_ele_info%ROWTYPE;
3704
3705
3706 --
3707 BEGIN
3708 --
3709 debug_enter (l_proc_name);
3710 -- Get the NI ele details from the collection
3711
3712 -- i := g_ni_ele_details.FIRST;
3713 --
3714 -- WHILE i IS NOT NULL
3715 -- LOOP
3716 -- DEBUG (
3717 -- 'Element Type ID: '
3718 -- || TO_CHAR (g_ni_ele_details (i).element_type_id)
3719 -- );
3720 -- Get the effective NI element assigned to this assignment
3721 DEBUG ('Get the effective NI element assigned to this assignment');
3722 -- Bug Fix 4721921
3723 OPEN csr_get_asg_ni_ele_info (
3724 p_assignment_id,
3725 g_ni_ele_type_id,
3726 p_effective_date
3727 );
3728 FETCH csr_get_asg_ni_ele_info INTO l_rec_ni_ele_info;
3729 CLOSE csr_get_asg_ni_ele_info;
3730
3731 l_ni_table_letter := get_ele_entry_value
3732 (p_element_entry_id => l_rec_ni_ele_info.element_entry_id
3733 ,p_input_value_id => g_ni_cat_iv_id
3734 ,p_effective_start_date => l_rec_ni_ele_info.effective_start_date
3735 ,p_effective_end_date => l_rec_ni_ele_info.effective_end_date
3736 );
3737 DEBUG (' l_rec_ni_ele_info.effective_start_date: '|| TO_CHAR( l_rec_ni_ele_info.effective_start_date,'DD/MON/YYYY'));
3738 DEBUG (' l_rec_ni_ele_info.effective_end_date: '|| TO_CHAR( l_rec_ni_ele_info.effective_end_date,'DD/MON/YYYY'));
3739
3740 -- -- Check whether an NI element exist ...
3741 -- DEBUG ( 'Start Date: '
3742 -- || l_asg_ni_ele_details.start_date);
3743 --
3744 -- -- Check whether this NI element entry start date is greater than
3745 -- -- the previous NI element entry start date (effective)
3746 --
3747 -- IF l_asg_ni_ele_details.start_date IS NOT NULL
3748 -- AND l_asg_ni_ele_details.start_date >
3749 -- NVL (l_max_start_date, hr_api.g_date)
3750 -- THEN
3751 -- -- If this date is greater then store the NI attributes
3752 -- -- Get the NI Table Letter
3753 -- l_ni_table_letter :=
3754 -- SUBSTR (g_ni_ele_details (i).element_name, 4, 1);
3755 -- l_max_start_date := l_asg_ni_ele_details.start_date;
3756 -- l_user_row_id := g_ni_ele_details (i).user_row_id;
3757 -- l_user_table_id := g_ni_ele_details (i).user_table_id;
3758 -- DEBUG ( 'NI Table Letter: '
3759 -- || l_ni_table_letter);
3760 -- DEBUG ( 'Max Start Date: '
3761 -- || l_max_start_date);
3762 -- DEBUG ( 'User Row id: '
3763 -- || TO_CHAR (l_user_row_id));
3764 -- DEBUG ( 'User Table id: '
3765 -- || TO_CHAR (l_user_table_id));
3766 -- END IF; -- End if of start date > check ...
3767 --
3768 -- i := g_ni_ele_details.NEXT (i);
3769 -- END LOOP; -- End loop of g_NI_ele_details collection loop ...
3770
3771 -- Check whether there is a NI table letter
3772
3773 IF l_ni_table_letter IS NOT NULL
3774 THEN
3775
3776 -- Get the user table id for pension mapping UDT
3777 l_user_table_id := get_udt_id ('PQP_GB_LYNX_HEYWOOD_NI_MAPPING_TABLE');
3778 l_user_row_id := get_user_row_id
3779 (p_user_table_id=> l_user_table_id
3780 ,p_user_row_name=> 'NI '||l_ni_table_letter
3781 ,p_effective_date=> p_effective_date
3782 );
3783
3784 -- Get the contribution indicator
3785 l_user_col_coll :=
3786 get_user_column_name (
3787 p_user_table_id=> l_user_table_id,
3788 p_user_row_id=> l_user_row_id,
3789 p_effective_date=> p_effective_date
3790 );
3791 i := l_user_col_coll.FIRST;
3792
3793 WHILE i IS NOT NULL
3794 LOOP
3795 -- Please note that the columns are seeded so the names
3796 -- are very unlikely to change, but if the user seed their
3797 -- own column names starting with P or Y then this will be a problem,
3798 -- so to ensure that we pick up the right column name
3799 -- we use the exact name match for checking
3800 IF l_user_col_coll (i) IN
3801 ('P = Reduced Rate Conts but now Full Rate',
3802 'Y = Reduced Rate Conts Current'
3803 )
3804 THEN
3805
3806 -- OPEN csr_get_ni_red_ind (l_user_col_coll(i));
3807 -- FETCH csr_get_ni_red_ind INTO l_ni_reduced_ind;
3808 -- CLOSE csr_get_ni_red_ind;
3809 l_ni_reduced_ind := SUBSTR (l_user_col_coll (i), 1, 1);
3810 DEBUG ( 'Reduced NI Ind: '
3811 || l_ni_reduced_ind);
3812 EXIT;
3813 END IF; -- End if of user col check in P,Y ...
3814
3815 i := l_user_col_coll.NEXT (i);
3816 END LOOP;
3817 END IF; -- End if of NI table letter exists check ...
3818
3819 l_ni_indicator := l_ni_table_letter
3820 || l_ni_reduced_ind;
3821 l_ni_indicator := TRIM (RPAD (l_ni_indicator, 2, ' '));
3822 DEBUG ( 'NI Indicator: '
3823 || l_ni_indicator);
3824 debug_exit (l_proc_name);
3825 RETURN l_ni_indicator;
3826 --
3827
3828 END get_ni_indicator;
3829
3830
3831 -- This function should be called from the fast formula, and is a wrapper to
3832 -- the low level function get_ni_indicator
3833 --
3834 -- ----------------------------------------------------------------------------
3835 -- |-------------------------< get_STARTERS_NI_indicator >--------------------|
3836 -- ----------------------------------------------------------------------------
3837 FUNCTION get_starters_ni_indicator (p_assignment_id IN NUMBER)
3838 RETURN VARCHAR2
3839 IS
3840
3841 --
3842 l_proc_name VARCHAR2 (60)
3843 := g_proc_name
3844 || 'get_STARTERS_NI_indicator';
3845 l_ni_indicator VARCHAR2 (2);
3846
3847 --
3848 BEGIN
3849 --
3850 debug_enter (l_proc_name);
3851 DEBUG ('Before calling function get_NI_indicator');
3852 l_ni_indicator :=
3853 get_ni_indicator (
3854 p_assignment_id=> p_assignment_id,
3855 p_effective_date=> g_ele_entry_details (p_assignment_id).effective_start_date
3856 );
3857 debug_exit (l_proc_name);
3858 RETURN l_ni_indicator;
3859 --
3860
3861 EXCEPTION
3862 WHEN OTHERS
3863 THEN
3864 DEBUG ( ' Others in '
3865 || l_proc_name, 'Y' -- turn trace off
3866 );
3867 RAISE;
3868 END get_starters_ni_indicator;
3869
3870
3871 -- This function should be called from the fast formula, and is a wrapper to
3872 -- the low level function get_ni_indicator
3873 --
3874 -- ----------------------------------------------------------------------------
3875 -- |-------------------------< get_CPX_NI_indicator >-------------------------|
3876 -- ----------------------------------------------------------------------------
3877 FUNCTION get_cpx_ni_indicator (p_assignment_id IN NUMBER)
3878 RETURN VARCHAR2
3879 IS
3880
3881 --
3882 l_proc_name VARCHAR2 (60)
3883 := g_proc_name
3884 || 'get_CPX_NI_indicator';
3885 l_ni_indicator VARCHAR2 (2);
3886
3887 --
3888 BEGIN
3889 --
3890 debug_enter (l_proc_name);
3891 DEBUG ('Before calling function get_NI_indicator');
3892 l_ni_indicator :=
3893 get_ni_indicator (
3894 p_assignment_id=> p_assignment_id,
3895 p_effective_date=> LEAST (
3896 g_ele_entry_details (p_assignment_id).effective_end_date,
3897 g_effective_end_date
3898 )
3899 );
3900 debug_exit (l_proc_name);
3901 RETURN l_ni_indicator;
3902 --
3903
3904 EXCEPTION
3905 WHEN OTHERS
3906 THEN
3907 DEBUG ( ' Others in '
3908 || l_proc_name, 'Y' -- turn trace off
3909 );
3910 RAISE;
3911 END get_cpx_ni_indicator;
3912
3913
3914 -- This function returns the employment number (assignment number) for the
3915 -- given assignment
3916 --
3917 -- ----------------------------------------------------------------------------
3918 -- |-------------------------< get_employment_number >------------------------|
3919 -- ----------------------------------------------------------------------------
3920 FUNCTION get_employment_number (p_assignment_id IN NUMBER)
3921 RETURN VARCHAR2
3922 IS
3923
3924 --
3925 l_proc_name VARCHAR2 (60)
3926 := g_proc_name
3927 || 'get_employment_number';
3928 l_employment_no per_all_assignments_f.assignment_number%TYPE
3929 := TRIM (RPAD (' ', 2, ' '));
3930
3931 --
3932 BEGIN
3933 --
3934 debug_enter (l_proc_name);
3935 l_employment_no := g_asg_details (p_assignment_id).assignment_number;
3936 l_employment_no := TRIM (RPAD (l_employment_no, 2, ' '));
3937 DEBUG ( 'Employment No: '
3938 || l_employment_no);
3939 debug_exit (l_proc_name);
3940 RETURN l_employment_no;
3941 --
3942 EXCEPTION
3943 WHEN OTHERS
3944 THEN
3945 DEBUG ( ' Others in '
3946 || l_proc_name, 'Y' -- turn trace off
3947 );
3948 RAISE;
3949 END get_employment_number;
3950
3951
3952 -- This function returns the employee category information for the assignment
3953 --
3954 -- ----------------------------------------------------------------------------
3955 -- |-------------------------< get_employee_category >------------------------|
3956 -- ----------------------------------------------------------------------------
3957 FUNCTION get_employee_category (p_assignment_id IN NUMBER)
3958 RETURN VARCHAR2
3959 IS
3960
3961 --
3962 l_proc_name VARCHAR2 (60)
3963 := g_proc_name
3964 || 'get_employee_category';
3965 l_employee_category per_all_assignments_f.employee_category%TYPE;
3966
3967 --
3968 BEGIN
3969 --
3970 debug_enter (l_proc_name);
3971 l_employee_category :=
3972 g_asg_details (p_assignment_id).employee_category;
3973 DEBUG ( 'Employee Category: '
3974 || l_employee_category);
3975 debug_exit (l_proc_name);
3976 RETURN l_employee_category;
3977 --
3978 EXCEPTION
3979 WHEN OTHERS
3980 THEN
3981 DEBUG ( ' Others in '
3982 || l_proc_name, 'Y' -- turn trace off
3983 );
3984 RAISE;
3985 END get_employee_category;
3986
3987
3988 -- This function determines the remuneration amount from a balance
3989 --
3990 -- ----------------------------------------------------------------------------
3991 -- |-------------------------< get_remuneration_from_bal >-------------------|
3992 -- ----------------------------------------------------------------------------
3993 FUNCTION get_remuneration_from_bal (
3994 p_assignment_id IN NUMBER,
3995 p_balance_type_id IN NUMBER,
3996 p_effective_start_date IN DATE,
3997 p_effective_end_date IN DATE
3998 )
3999 RETURN VARCHAR2
4000 IS
4001
4002 --
4003 l_proc_name VARCHAR2 (60)
4004 := g_proc_name
4005 || 'get_remuneration_from_bal';
4006 l_remuneration VARCHAR2 (11);
4007 l_value NUMBER;
4008
4009 --
4010 BEGIN
4011 --
4012 debug_enter (l_proc_name);
4013 l_value :=
4014 get_person_bal_value (
4015 p_assignment_id=> p_assignment_id,
4016 p_balance_type_id=> p_balance_type_id,
4017 p_effective_start_date=> p_effective_start_date,
4018 p_effective_end_date=> p_effective_end_date
4019 );
4020
4021 IF l_value > 99999999999
4022 THEN
4023 l_value := 99999999999;
4024 END IF; -- End if of value exceed max limit check ...
4025
4026 IF l_value >= 0 THEN
4027 l_remuneration := TRIM (TO_CHAR (l_value, '09999999999'));
4028 ELSE
4029 l_remuneration := TRIM (TO_CHAR (l_value, 'S0999999999'));
4030 END IF;
4031
4032 DEBUG ( 'Remuneration: '
4033 || l_remuneration);
4034 debug_exit (l_proc_name);
4035 RETURN l_remuneration;
4036 --
4037
4038 END get_remuneration_from_bal;
4039
4040
4041 -- This function returns the actual remuneration for a given assignment
4042 -- PS Actual remuneration has a balance called "Gross Pay"
4043 --
4044 -- ----------------------------------------------------------------------------
4045 -- |-------------------------< get_actual_remuneration >---------------------|
4046 -- ----------------------------------------------------------------------------
4047 FUNCTION get_actual_remuneration (p_assignment_id IN NUMBER)
4048 RETURN VARCHAR2
4049 IS
4050
4051 --
4052 l_proc_name VARCHAR2 (60)
4053 := g_proc_name
4054 || 'get_actual_remuneration';
4055 l_actual_rem VARCHAR2 (11);
4056 l_bal_type_id NUMBER;
4057 l_value NUMBER;
4058
4059 --
4060 BEGIN
4061 --
4062 debug_enter (l_proc_name);
4063 l_bal_type_id := get_pay_bal_id (p_balance_name => 'Gross Pay');
4064 l_actual_rem :=
4065 get_remuneration_from_bal (
4066 p_assignment_id=> p_assignment_id,
4067 p_balance_type_id=> l_bal_type_id,
4068 p_effective_start_date=> g_effective_start_date,
4069 p_effective_end_date=> g_effective_end_date
4070 );
4071 -- Bug Fix 5021075
4072 IF TO_NUMBER(l_actual_rem) < 0
4073 THEN
4074 l_value := pqp_gb_tp_extract_functions.raise_extract_error
4075 (p_business_group_id => g_business_group_id
4076 ,p_assignment_id => p_assignment_id
4077 ,p_error_text => 'BEN_94556_EXT_VALUE_ERROR'
4078 ,p_error_number => 94556
4079 ,p_token1 => 'Actual Remuneration'
4080 ,p_fatal_flag => 'N'
4081 );
4082 END IF;
4083 debug_exit (l_proc_name);
4084 RETURN l_actual_rem;
4085 --
4086 EXCEPTION
4087 WHEN OTHERS
4088 THEN
4089 DEBUG ( ' Others in '
4090 || l_proc_name, 'Y' -- turn trace off
4091 );
4092 RAISE;
4093 END get_actual_remuneration;
4094
4095
4096 -- This function returns the pensionable remuneration for a given assignment
4097 -- PS Pensionable remuneration has a balance called "Superannuable Salary"
4098 -- (Default) or the user provided balance name in the CPX definition UDT
4099 --
4100 -- ----------------------------------------------------------------------------
4101 -- |-------------------------< get_pensionable_remuneration >-----------------|
4102 -- ----------------------------------------------------------------------------
4103 FUNCTION get_pensionable_remuneration (p_assignment_id IN NUMBER)
4104 RETURN VARCHAR2
4105 IS
4106
4107 --
4108 l_proc_name VARCHAR2 (60)
4109 := g_proc_name
4110 || 'get_pensionable_remuneration';
4111 l_pensionable_rem VARCHAR2 (11);
4112 l_value NUMBER;
4113
4114 --
4115 BEGIN
4116 --
4117 debug_enter (l_proc_name);
4118 l_pensionable_rem :=
4119 get_remuneration_from_bal (
4120 p_assignment_id=> p_assignment_id,
4121 p_balance_type_id=> g_superann_sal_bal_id,
4122 p_effective_start_date=> g_effective_start_date,
4123 p_effective_end_date=> g_effective_end_date
4124 );
4125 -- Bug Fix 5021075
4126 IF TO_NUMBER(l_pensionable_rem) < 0
4127 THEN
4128 l_value := pqp_gb_tp_extract_functions.raise_extract_error
4129 (p_business_group_id => g_business_group_id
4130 ,p_assignment_id => p_assignment_id
4131 ,p_error_text => 'BEN_94556_EXT_VALUE_ERROR'
4132 ,p_error_number => 94556
4133 ,p_token1 => 'Pensionable Remuneration'
4134 ,p_fatal_flag => 'N'
4135 );
4136 END IF;
4137 debug_exit (l_proc_name);
4138 RETURN l_pensionable_rem;
4139 --
4140 EXCEPTION
4141 WHEN OTHERS
4142 THEN
4143 DEBUG ( ' Others in '
4144 || l_proc_name, 'Y' -- turn trace off
4145 );
4146 RAISE;
4147 END get_pensionable_remuneration;
4148
4149
4150 -- This function gets the header system data element information
4151 --
4152 -- ----------------------------------------------------------------------------
4153 -- |-------------------------< get_system_data_element >----------------------|
4154 -- ----------------------------------------------------------------------------
4155 FUNCTION get_system_data_element
4156 RETURN VARCHAR2
4157 IS
4158
4159 --
4160 l_proc_name VARCHAR2 (60)
4161 := g_proc_name
4162 || 'get_system_data_element';
4163
4164 --
4165 BEGIN
4166 --
4167 debug_enter (l_proc_name);
4168 debug_exit (l_proc_name);
4169 RETURN g_header_system_element;
4170 --
4171 EXCEPTION
4172 WHEN OTHERS
4173 THEN
4174 DEBUG ( ' Others in '
4175 || l_proc_name, 'Y' -- turn trace off
4176 );
4177 RAISE;
4178 END get_system_data_element;
4179
4180
4181 -- This function returns the total number of detail records for an extract type
4182 --
4183 -- ----------------------------------------------------------------------------
4184 -- |-------------------------< get_total_number_data_records >----------------|
4185 -- ----------------------------------------------------------------------------
4186 FUNCTION get_total_number_data_records (p_type IN VARCHAR2)
4187 RETURN VARCHAR2
4188 IS
4189
4190 --
4191 l_proc_name VARCHAR2 (61)
4192 := g_proc_name
4193 || 'get_total_number_data_records';
4194
4195 CURSOR count_extract_details
4196 (p_ext_rcd_id ben_ext_rcd.ext_rcd_id%TYPE)
4197 IS
4198 SELECT COUNT (*)
4199 FROM ben_ext_rslt_dtl dtl
4200 -- ,ben_ext_rcd rcd
4201 WHERE dtl.ext_rslt_id = ben_ext_thread.g_ext_rslt_id
4202 AND dtl.ext_rcd_id = p_ext_rcd_id
4203 -- AND rcd.ext_rcd_id = dtl.ext_rcd_id
4204 -- AND rcd.rcd_type_cd = 'D'
4205 AND DECODE (
4206 NVL (TRIM (p_type), hr_api.g_varchar2),
4207 hr_api.g_varchar2, hr_api.g_varchar2,
4208 dtl.val_01
4209 ) = NVL (TRIM (p_type), hr_api.g_varchar2)
4210 AND dtl.val_01 <> 'DELETE';
4211
4212 l_count NUMBER := 0;
4213 l_count_099999 VARCHAR2 (6) := '000000';
4214 l_ext_rcd_id NUMBER;
4215
4216 --
4217 BEGIN
4218 --
4219 debug_enter (l_proc_name);
4220 --
4221
4222 -- 11.5.10_CU2: Performance fix :
4223 -- get the ben_ext_rcd.ext_rcd_id
4224 -- and use this one for next cursor
4225 -- This will prevent FTS on the table.
4226
4227 OPEN csr_ext_rcd_id (p_hide_flag => 'N'
4228 ,p_rcd_type_cd => 'D'
4229 );
4230 FETCH csr_ext_rcd_id INTO l_ext_rcd_id;
4231 CLOSE csr_ext_rcd_id ;
4232
4233 OPEN count_extract_details(l_ext_rcd_id);
4234 FETCH count_extract_details INTO l_count;
4235
4236 IF l_count < 999999
4237 THEN
4238 l_count_099999 := TRIM (TO_CHAR (l_count, '099999'));
4239 ELSE
4240 l_count_099999 := '999999';
4241 END IF;
4242
4243 CLOSE count_extract_details;
4244 DEBUG ( 'Total Count: '
4245 || l_count_099999);
4246 debug_exit (l_proc_name);
4247 RETURN l_count_099999;
4248 --
4249
4250 EXCEPTION
4251 WHEN OTHERS
4252 THEN
4253 DEBUG ( ' Others in '
4254 || l_proc_name, 'Y' -- turn trace off
4255 );
4256 RAISE;
4257 END get_total_number_data_records;
4258
4259
4260 -- This function determines the sum of a particular data element in a detail
4261 -- record if available
4262 --
4263 -- ----------------------------------------------------------------------------
4264 -- |-----------------------< get_data_element_total_value >-------------------|
4265 -- ----------------------------------------------------------------------------
4266 FUNCTION get_data_element_total_value (p_val_seq IN NUMBER)
4267 RETURN VARCHAR2
4268 IS
4269
4270 -- Dynamic cursor does not work on version 8.0
4271 -- so use decode statements
4272 -- please include additional sequence values
4273 -- if you use any of them
4274
4275 CURSOR csr_get_total
4276 (p_ext_rcd_id ben_ext_rcd.ext_rcd_id%TYPE)
4277 IS
4278 SELECT NVL (SUM (TO_NUMBER(DECODE (p_val_seq,
4279 23, VAL_23,
4280 25, VAL_25,
4281 27, VAL_27,
4282 29, VAL_29,
4283 31, VAL_31,
4284 33, VAL_33,
4285 35, VAL_35,
4286 42, VAL_42,
4287 44, VAL_44
4288 )
4289 )), 0) total_value
4290 FROM ben_ext_rslt_dtl dtl
4291 -- ,ben_ext_rcd rcd
4292 WHERE dtl.ext_rslt_id = ben_ext_thread.g_ext_rslt_id
4293 AND dtl.ext_rcd_id = p_ext_rcd_id;
4294 -- AND rcd.ext_rcd_id = dtl.ext_rcd_id
4295 -- AND rcd.rcd_type_cd = 'D';
4296
4297 l_proc_name VARCHAR2 (60)
4298 := g_proc_name
4299 || 'get_data_element_total_value';
4300
4301 -- TYPE ref_get_total IS REF CURSOR;
4302
4303 -- csr_get_total ref_get_total;
4304 l_rslt_id NUMBER := ben_ext_thread.g_ext_rslt_id;
4305 l_total_value NUMBER := 0;
4306 l_fmt_total_value VARCHAR2 (12);
4307 l_val_seq VARCHAR2 (100);
4308 l_ext_rcd_id NUMBER;
4309 l_value NUMBER;
4310
4311 --
4312 BEGIN
4313 --
4314 debug_enter (l_proc_name);
4315
4316 --
4317
4318 -- IF p_val_seq < 10
4319 -- THEN
4320 -- l_val_seq := '0'
4321 -- || TO_CHAR (p_val_seq);
4322 -- ELSE
4323 -- l_val_seq := TO_CHAR (p_val_seq);
4324 -- END IF; -- End if of val seq < 10 check ...
4325 --
4326 -- l_val_seq := 'dtl.val_'
4327 -- || l_val_seq;
4328 -- 11.5.10_CU2: Performance fix :
4329 -- get the ben_ext_rcd.ext_rcd_id
4330 -- and use this one for next cursor
4331 -- This will prevent FTS on the table.
4332
4333 OPEN csr_ext_rcd_id (p_hide_flag => 'N'
4334 ,p_rcd_type_cd => 'D'
4335 );
4336 FETCH csr_ext_rcd_id INTO l_ext_rcd_id;
4337 CLOSE csr_ext_rcd_id ;
4338
4339 OPEN csr_get_total(l_ext_rcd_id);
4340 FETCH csr_get_total INTO l_total_value;
4341 CLOSE csr_get_total;
4342
4343 -- Bug Fix 5021075
4344 IF l_total_value > 999999999999
4345 THEN
4346 l_total_value := 999999999999;
4347 ELSIF l_total_value < 0
4348 THEN
4349 l_value := pqp_gb_tp_extract_functions.raise_extract_error
4350 (p_business_group_id => g_business_group_id
4351 ,p_assignment_id => NULL
4352 ,p_error_text => 'BEN_94556_EXT_VALUE_ERROR'
4353 ,p_error_number => 94556
4354 ,p_token1 => 'Total Contribution'
4355 ,p_fatal_flag => 'Y'
4356 );
4357 END IF; -- End if of total value exceed limit check ...
4358
4359 IF l_total_value >= 0 THEN
4360 l_fmt_total_value := TRIM (TO_CHAR (l_total_value, '099999999999'));
4361 ELSE
4362 l_fmt_total_value := TRIM (TO_CHAR (l_total_value, 'S09999999999'));
4363 END IF;
4364 DEBUG ( 'Total Value: '
4365 || l_fmt_total_value);
4366 debug_exit (l_proc_name);
4367 RETURN l_fmt_total_value;
4368 END get_data_element_total_value;
4369
4370
4371 --
4372 -- End of Starters Report functions
4373 --
4374 -- Annual Report Function Begins
4375
4376 -- This function checks whether an employee is a member of the pension scheme
4377 --
4378 -- ----------------------------------------------------------------------------
4379 -- |---------------------------< chk_is_employee_a_member >-------------------|
4380 -- ----------------------------------------------------------------------------
4381 FUNCTION chk_is_employee_a_member (
4382 p_assignment_id IN NUMBER,
4383 p_effective_start_date IN DATE,
4384 p_effective_end_date IN DATE
4385 )
4386 RETURN VARCHAR2
4387 IS
4388
4389 --
4390 l_proc_name VARCHAR2 (60)
4391 := g_proc_name
4392 || 'chk_is_employee_a_member';
4393 l_eet_details csr_get_eet_info%ROWTYPE;
4394 l_inclusion_flag VARCHAR2 (1);
4395
4396 --
4397 BEGIN
4398 debug_enter (l_proc_name);
4399 DEBUG ('Check Element entries exists with pension elements');
4400 -- Check element entries exist with pension ele's
4401 l_inclusion_flag := 'N';
4402 OPEN csr_get_eet_info (
4403 c_assignment_id=> p_assignment_id,
4404 c_effective_start_date=> p_effective_start_date,
4405 c_effective_end_date=> p_effective_end_date
4406 );
4407
4408 LOOP
4409 DEBUG ('Fetch element entries');
4410 FETCH csr_get_eet_info INTO l_eet_details;
4411 EXIT WHEN csr_get_eet_info%NOTFOUND;
4412
4413 -- Check atleast one pension element exists for this assignment
4414 IF g_pension_ele_ids.EXISTS (l_eet_details.element_type_id)
4415 THEN
4416 -- Element exists, set the inclusion flag to 'Y'
4417 DEBUG ('Pension element entry exists');
4418 DEBUG (
4419 'Pension Element Id: '
4420 || TO_CHAR (l_eet_details.element_type_id)
4421 );
4422 DEBUG ('effective start date: '|| TO_CHAR(l_eet_details.effective_start_date, 'DD/MON/YYYY'));
4423 DEBUG ('effective end date: '|| TO_CHAR(l_eet_details.effective_end_date, 'DD/MON/YYYY'));
4424 IF l_inclusion_flag = 'N' THEN
4425 g_ele_entry_details (p_assignment_id).element_type_id :=
4426 l_eet_details.element_type_id;
4427 g_ele_entry_details (p_assignment_id).element_entry_id :=
4428 l_eet_details.element_entry_id;
4429 g_ele_entry_details (p_assignment_id).effective_start_date :=
4430 l_eet_details.effective_start_date;
4431 g_ele_entry_details (p_assignment_id).effective_end_date :=
4432 l_eet_details.effective_end_date;
4433 g_ele_entry_details (p_assignment_id).assignment_id :=
4434 p_assignment_id;
4435 END IF;
4436 l_inclusion_flag := 'Y';
4437 -- EXIT;
4438 IF g_index > 0 AND
4439 g_pen_ele_details (g_index).element_entry_id = l_eet_details.element_entry_id
4440 THEN
4441 -- Extend the dates
4442 g_pen_ele_details (g_index).effective_start_date := l_eet_details.effective_start_date;
4443 ELSE
4444 g_index := g_index + 1;
4445 DEBUG('g_index: '|| g_index);
4446 g_pen_ele_details (g_index).element_entry_id :=
4447 l_eet_details.element_entry_id;
4448 g_pen_ele_details (g_index).element_type_id :=
4449 l_eet_details.element_type_id;
4450 g_pen_ele_details (g_index).effective_start_date :=
4451 l_eet_details.effective_start_date;
4452 g_pen_ele_details (g_index).effective_end_date :=
4453 l_eet_details.effective_end_date;
4454 g_pen_ele_details (g_index).assignment_id :=
4455 p_assignment_id;
4456 END IF; -- End if of g_index > 1 check ...
4457
4458 END IF; -- End if of pension element entry exists ...
4459 END LOOP;
4460
4461 CLOSE csr_get_eet_info;
4462 debug_exit (l_proc_name);
4463 RETURN l_inclusion_flag;
4464 END chk_is_employee_a_member;
4465
4466
4467 -- This function checks whether an assignment/person qualifies for annual CPX
4468 -- report and returns a 'Y', 'N' or 'ERROR'
4469 --
4470 -- ----------------------------------------------------------------------------
4471 -- |-----------------------< chk_employee_qual_for_annual >------------------|
4472 -- ----------------------------------------------------------------------------
4473
4474 FUNCTION chk_employee_qual_for_annual (
4475 p_business_group_id IN NUMBER -- context
4476 ,
4477 p_effective_date IN DATE -- context
4478 ,
4479 p_assignment_id IN NUMBER -- context
4480 ,
4481 p_error_number OUT NOCOPY NUMBER,
4482 p_error_text OUT NOCOPY VARCHAR2
4483 )
4484 RETURN VARCHAR2 -- Y or N
4485 IS
4486
4487 --
4488 l_inclusion_flag VARCHAR2 (20) := 'N';
4489 l_proc_name VARCHAR2 (61)
4490 := g_proc_name
4491 || 'chk_employee_qual_for_annual';
4492 l_secondary_asg_ids t_number;
4493 l_error_number NUMBER;
4494 l_error_text VARCHAR2 (200);
4495 l_return NUMBER;
4496 i NUMBER;
4497
4498 --
4499 BEGIN
4500 debug_enter (l_proc_name);
4501 l_error_text := NULL;
4502 l_error_number := NULL;
4503 DEBUG ( 'Business Group ID: '
4504 || TO_CHAR (g_business_group_id));
4505 DEBUG ( 'Assignment ID: '
4506 || TO_CHAR (p_assignment_id));
4507 DEBUG ( 'Session Date: '
4508 || p_effective_date);
4509
4510 IF g_business_group_id IS NULL
4511 THEN
4512 g_pension_ele_ids.DELETE;
4513 g_pension_bal_name := NULL;
4514 g_pension_ele_name := NULL;
4515 g_initial_ext_date := NULL;
4516 g_emp_cont_iv_name := NULL;
4517 g_superann_refno_iv_name := NULL;
4518 g_superann_sal_bal_name := NULL;
4519 g_additional_cont_bal_name := NULL;
4520 g_buyback_cont_bal_name := NULL;
4521 g_superann_sal_bal_id := NULL;
4522 g_additional_cont_bal_id := NULL;
4523 g_buyback_cont_bal_id := NULL;
4524 g_ele_entry_details.DELETE;
4525 g_secondary_asg_ids.DELETE;
4526 g_asg_details.DELETE;
4527 g_ni_ele_details.DELETE;
4528 g_ni_ele_type_id := NULL;
4529 g_ni_cat_iv_id := NULL;
4530 g_ni_pen_iv_id := NULL;
4531 g_pen_ele_details.DELETE;
4532 g_index := 0;
4533
4534 -- Use STARTERS for starters, HOURCHANGE for hour change and ANNUAL
4535 -- for Annual report
4536 g_header_system_element := 'ANNUAL:';
4537 DEBUG ('Before calling set_extract_globals function');
4538 l_return :=
4539 set_extract_globals (
4540 p_assignment_id=> p_assignment_id,
4541 p_business_group_id=> p_business_group_id,
4542 p_effective_date=> ben_ext_person.g_effective_date,
4543 p_error_number=> l_error_number,
4544 p_error_text=> l_error_text
4545 );
4546
4547 IF l_return <> 0
4548 THEN
4549 DEBUG ('Function set_extract_globals function is in Error');
4550 p_error_text := l_error_text;
4551 p_error_number := l_error_number;
4552 l_inclusion_flag := 'ERROR';
4553 debug_exit (l_proc_name);
4554 RETURN l_inclusion_flag;
4555 END IF; -- End if of return <> 0 check...
4556
4557 -- Call procedure get_NI_element_details to populate NI collection
4558 DEBUG ('Before calling get_NI_element_details procedure');
4559 -- get_ni_element_details;
4560 END IF;
4561
4562 DEBUG ('Before calling chk_is_employee_a_member function');
4563 --
4564 -- Check the person is a member
4565 --
4566
4567 g_pen_ele_details.DELETE;
4568 g_index := 0;
4569
4570 l_inclusion_flag :=
4571 chk_is_employee_a_member (
4572 p_assignment_id=> p_assignment_id,
4573 p_effective_start_date=> g_effective_start_date,
4574 p_effective_end_date=> g_effective_end_date
4575 );
4576 DEBUG ( 'Inclusion Flag: '
4577 || l_inclusion_flag);
4578
4579 IF l_inclusion_flag = 'Y'
4580 THEN
4581 DEBUG ('Assignment qualifies for annual report');
4582 -- Populate assignment details
4583
4584 set_assignment_details (
4585 p_assignment_id=> p_assignment_id,
4586 p_effective_date=> LEAST (
4587 g_ele_entry_details (p_assignment_id).effective_end_date,
4588 g_effective_end_date
4589 )
4590 );
4591 DEBUG ('Get Secondary Assignments');
4592 -- Get Secondary Assignments
4593
4594 DEBUG ('Before calling all secondary assignments procedure');
4595 get_all_sec_assignments (
4596 p_assignment_id=> p_assignment_id,
4597 p_secondary_asg_ids=> l_secondary_asg_ids
4598 );
4599 i := l_secondary_asg_ids.FIRST;
4600
4601 WHILE i IS NOT NULL
4602 LOOP
4603 DEBUG ('Secondary assignment exist');
4604 DEBUG ('Check this secondary asg qualifies for Annual report');
4605 DEBUG ('Before calling function chk_is_employee_a_member');
4606
4607 IF chk_is_employee_a_member (
4608 p_assignment_id=> l_secondary_asg_ids (i),
4609 p_effective_start_date=> g_effective_start_date,
4610 p_effective_end_date=> g_effective_end_date
4611 ) = 'Y'
4612 THEN
4613 DEBUG (
4614 TO_CHAR (l_secondary_asg_ids (i))
4615 || ' Secondary assignment qualifies'
4616 );
4617 g_secondary_asg_ids (i) := l_secondary_asg_ids (i);
4618 END IF; -- End if of secondary asg check for annual ..
4619
4620 i := l_secondary_asg_ids.NEXT (i);
4621 END LOOP; -- End loop of secondary assignments ...
4622 END IF; -- End if of inclusion Flag Check...
4623
4624 debug_exit (l_proc_name);
4625 RETURN l_inclusion_flag;
4626 EXCEPTION
4627 WHEN OTHERS
4628 THEN
4629 debug_exit ( ' Others in '
4630 || l_proc_name, 'Y' -- turn trace off
4631 );
4632 p_error_number := SQLCODE;
4633 p_error_text := SQLERRM;
4634 RAISE;
4635 END chk_employee_qual_for_annual;
4636
4637
4638 -- This function returns the member contribution for a given assignment
4639 -- PS member contribution may use a balance called "Total Pension Contributions"
4640 -- (Default) or the user provided balance name in the CPX definition UDT or
4641 -- determine it from the element/input value run result combo
4642 --
4643 -- ----------------------------------------------------------------------------
4644 -- |-------------------------< get_member_contributions >---------------------|
4645 -- ----------------------------------------------------------------------------
4646 FUNCTION get_member_contributions (p_assignment_id IN NUMBER)
4647 RETURN VARCHAR2
4648 IS
4649
4650 --
4651 l_proc_name VARCHAR2 (60)
4652 := g_proc_name
4653 || 'get_member_contributions';
4654 l_member_contributions VARCHAR2 (11);
4655 l_value NUMBER := 0;
4656 l_input_value_id NUMBER;
4657 l_return NUMBER;
4658
4659 --
4660 BEGIN
4661 --
4662 debug_enter (l_proc_name);
4663
4664 IF g_pension_bal_id IS NULL
4665 THEN
4666 -- The setup may be in element mode
4667 -- Get the contribution amount from the run result
4668
4669 -- Determine the input value id for "Pay Value" name
4670 DEBUG ('Before calling get_input_value_id function');
4671 l_input_value_id :=
4672 get_input_value_id (
4673 p_input_value_name=> 'Pay Value',
4674 p_element_type_id=> g_ele_entry_details (p_assignment_id).element_type_id,
4675 p_effective_date=> g_effective_date
4676 );
4677
4678 IF l_input_value_id IS NOT NULL
4679 THEN
4680 -- Get the person runresult value for the element/iv combo
4681 DEBUG ('Before calling get_person_ele_rresult_value function');
4682 l_value :=
4683 get_person_ele_rresult_value (
4684 p_assignment_id=> p_assignment_id,
4685 p_element_type_id=> g_ele_entry_details (
4686 p_assignment_id
4687 ).element_type_id,
4688 p_input_value_id=> l_input_value_id,
4689 p_effective_start_date=> g_effective_start_date,
4690 p_effective_end_date=> g_effective_end_date
4691 );
4692 END IF; -- End if of input value id is not null check ...
4693 -- Bug Fix 5021075
4694 -- Bug Fix 5057187
4695
4696 IF l_value > 99999999999
4697 THEN
4698 l_value := 99999999999;
4699 ELSIF l_value < 0 THEN
4700 l_return := pqp_gb_tp_extract_functions.raise_extract_error
4701 (p_business_group_id => g_business_group_id
4702 ,p_assignment_id => p_assignment_id
4703 ,p_error_text => 'BEN_94556_EXT_VALUE_ERROR'
4704 ,p_error_number => 94556
4705 ,p_token1 => 'Member Contributions'
4706 ,p_fatal_flag => 'N'
4707 );
4708 END IF; -- End if of value exceed max limit check ...
4709
4710 IF l_value >= 0 THEN
4711 l_member_contributions := TRIM (TO_CHAR (l_value, '09999999999'));
4712 ELSE
4713 l_member_contributions := TRIM (TO_CHAR (l_value, 'S0999999999'));
4714 END IF;
4715 ELSE -- Otherwise use the pension bal id from global
4716 l_member_contributions :=
4717 get_remuneration_from_bal (
4718 p_assignment_id=> p_assignment_id,
4719 p_balance_type_id=> g_pension_bal_id,
4720 p_effective_start_date=> g_effective_start_date,
4721 p_effective_end_date=> g_effective_end_date
4722 );
4723 IF TO_NUMBER(l_member_contributions) < 0
4724 THEN
4725 l_return := pqp_gb_tp_extract_functions.raise_extract_error
4726 (p_business_group_id => g_business_group_id
4727 ,p_assignment_id => p_assignment_id
4728 ,p_error_text => 'BEN_94556_EXT_VALUE_ERROR'
4729 ,p_error_number => 94556
4730 ,p_token1 => 'Member Contributions'
4731 ,p_fatal_flag => 'N'
4732 );
4733 END IF;
4734 END IF; -- End if of pension bal id is null check ...
4735
4736 DEBUG ( 'Member Contributions: '
4737 || l_member_contributions);
4738 debug_exit (l_proc_name);
4739 RETURN l_member_contributions;
4740 --
4741 EXCEPTION
4742 WHEN OTHERS
4743 THEN
4744 DEBUG ( ' Others in '
4745 || l_proc_name, 'Y' -- turn trace off
4746 );
4747 RAISE;
4748 END get_member_contributions;
4749
4750
4751 -- This function returns the Employees' National Earnings for a given assignment
4752 -- PS NI Earnings has a balance called "NI Employee"
4753 -- This balance has no dimensions so a different function has to be used to
4754 -- determine it's value
4755 -- Change the NI Employee to NIable Pay as we need earnings figure
4756 -- and not contributions
4757 --
4758 -- ----------------------------------------------------------------------------
4759 -- |-------------------------< get_NI_earnings >-----------------------------|
4760 -- ----------------------------------------------------------------------------
4761 FUNCTION get_ni_earnings (p_assignment_id IN NUMBER)
4762 RETURN VARCHAR2
4763 IS
4764
4765 --
4766 -- Cursor to get NI element details
4767 -- for this person
4768 CURSOR csr_get_ele_ent_details (c_assignment_id NUMBER
4769 ,c_effective_start_date DATE
4770 ,c_effective_end_date DATE)
4771 IS
4772 SELECT pee.element_entry_id, pee.effective_start_date
4773 ,pee.effective_end_date, pel.element_type_id
4774 FROM pay_element_entries_f pee, pay_element_links_f pel
4775 WHERE pee.assignment_id = c_assignment_id
4776 AND pee.entry_type = 'E'
4777 AND pee.element_link_id = pel.element_link_id
4778 AND (
4779 c_effective_start_date BETWEEN pee.effective_start_date
4780 AND pee.effective_end_date
4781 OR c_effective_end_date BETWEEN pee.effective_start_date
4782 AND pee.effective_end_date
4783 OR pee.effective_start_date BETWEEN c_effective_start_date
4784 AND c_effective_end_date
4785 OR pee.effective_end_date BETWEEN c_effective_start_date
4786 AND c_effective_end_date
4787 )
4788 AND pel.element_type_id = g_ni_ele_type_id
4789 AND (
4790 c_effective_start_date BETWEEN pel.effective_start_date
4791 AND pel.effective_end_date
4792 OR c_effective_end_date BETWEEN pel.effective_start_date
4793 AND pel.effective_end_date
4794 OR pel.effective_start_date BETWEEN c_effective_start_date
4795 AND c_effective_end_date
4796 OR pel.effective_end_date BETWEEN c_effective_start_date
4797 AND c_effective_end_date
4798 )
4799 ORDER BY pee.effective_start_date DESC;
4800
4801 -- Cursor to get screen entry value
4802
4803 CURSOR csr_get_screen_ent_val(
4804 c_element_entry_id NUMBER
4805 ,c_input_value_id NUMBER
4806 ,c_effective_start_date DATE
4807 ,c_effective_end_date DATE
4808 )
4809 IS
4810 SELECT screen_entry_value, effective_start_date, effective_end_date
4811 FROM pay_element_entry_values_f
4812 WHERE element_entry_id = c_element_entry_id
4813 AND (
4814 effective_start_date BETWEEN c_effective_start_date
4815 AND c_effective_end_date
4816 OR effective_end_date BETWEEN c_effective_start_date
4817 AND c_effective_end_date
4818 OR c_effective_start_date BETWEEN effective_start_date
4819 AND effective_end_date
4820 OR c_effective_end_date BETWEEN effective_start_date
4821 AND effective_end_date
4822 )
4823 AND input_value_id = c_input_value_id;
4824
4825 CURSOR csr_get_end_date (
4826 c_assignment_id NUMBER,
4827 c_effective_date DATE
4828 )
4829 IS
4830 SELECT DISTINCT (ptp.end_date) end_date
4831 FROM per_time_periods ptp,
4832 pay_payroll_actions ppa,
4833 pay_assignment_actions paa
4834 WHERE ptp.time_period_id = ppa.time_period_id
4835 AND ppa.effective_date BETWEEN ptp.start_date
4836 AND ptp.end_date
4837 AND ppa.payroll_action_id = paa.payroll_action_id
4838 AND c_effective_date BETWEEN ptp.start_date
4839 AND ptp.end_date
4840 AND ppa.action_type IN ('R', 'Q', 'I', 'V', 'B')
4841 AND NVL (ppa.business_group_id, g_business_group_id) =
4842 g_business_group_id
4843 AND paa.assignment_id = c_assignment_id
4844 ORDER BY ptp.end_date;
4845
4846
4847
4848 l_proc_name VARCHAR2 (60) := g_proc_name
4849 || 'get_NI_earnings';
4850 l_ni_earnings VARCHAR2 (11);
4851 l_bal_type_id NUMBER;
4852 l_value NUMBER := 0;
4853 l_rec_ele_ent_details csr_get_ele_ent_details%ROWTYPE;
4854 l_rec_screen_ent_val csr_get_screen_ent_val%ROWTYPE;
4855 l_balance_name pay_balance_types.balance_name%TYPE;
4856 l_total_value NUMBER := 0;
4857 i NUMBER;
4858 j NUMBER;
4859 l_effective_date DATE;
4860 l_return NUMBER;
4861 l_secondary_asg_ids t_number;
4862 l_ni_category pay_element_entry_values_f.screen_entry_value%TYPE;
4863 l_end_date DATE;
4864
4865 --
4866 BEGIN
4867 --
4868 debug_enter (l_proc_name);
4869 -- Bug Fix 4721921
4870 -- l_effective_date := LEAST (
4871 -- g_ele_entry_details (p_assignment_id).effective_end_date,
4872 -- g_effective_end_date
4873 -- );
4874 get_eligible_sec_assignments (
4875 p_assignment_id=> p_assignment_id,
4876 p_secondary_asg_ids=> l_secondary_asg_ids
4877 );
4878
4879 i := g_pen_ele_details.FIRST;
4880 WHILE i IS NOT NULL
4881 LOOP
4882 DEBUG('g_pen_ele_details(i).effective_start_date: '
4883 || TO_CHAR(g_pen_ele_details(i).effective_start_date, 'DD/MON/YYYY'));
4884 DEBUG('g_pen_ele_details(i).effective_end_date: '
4885 || TO_CHAR(g_pen_ele_details(i).effective_end_date, 'DD/MON/YYYY'));
4886 DEBUG('g_pen_ele_details(i).assignment_id: '
4887 || g_pen_ele_details(i).assignment_id);
4888
4889 IF g_pen_ele_details(i).assignment_id = p_assignment_id OR
4890 l_secondary_asg_ids.EXISTS(g_pen_ele_details(i).assignment_id)
4891 THEN
4892 OPEN csr_get_ele_ent_details(g_pen_ele_details(i).assignment_id
4893 ,GREATEST(g_effective_start_date,
4894 g_pen_ele_details(i).effective_start_date)
4895 ,LEAST(g_effective_end_date,
4896 g_pen_ele_details(i).effective_end_date)
4897 );
4898 LOOP
4899 FETCH csr_get_ele_ent_details INTO l_rec_ele_ent_details;
4900 EXIT WHEN csr_get_ele_ent_details%NOTFOUND;
4901
4902 l_ni_category := get_ele_entry_value
4903 (p_element_entry_id => l_rec_ele_ent_details.element_entry_id
4904 ,p_input_value_id => g_ni_cat_iv_id
4905 ,p_effective_start_date => l_rec_ele_ent_details.effective_start_date
4906 ,p_effective_end_date => l_rec_ele_ent_details.effective_end_date
4907 );
4908 DEBUG('l_ni_category: '||l_ni_category);
4909 j := g_ni_cont_out_ele_ids.FIRST;
4910 WHILE j IS NOT NULL
4911 LOOP
4912 DEBUG ('g_ni_cont_out_ele_ids(j).category: '||g_ni_cont_out_ele_ids(j).category);
4913 IF g_ni_cont_out_ele_ids(j).category = l_ni_category
4914 THEN
4915 l_balance_name := 'NI '|| g_ni_cont_out_ele_ids(j).category || ' Able UEL';
4916 DEBUG('l_balance_name: '||l_balance_name);
4917 l_bal_type_id := get_pay_bal_id (p_balance_name => l_balance_name);
4918 l_end_date := NULL;
4919 OPEN csr_get_end_date (g_pen_ele_details(i).assignment_id,
4920 LEAST(g_effective_end_date,
4921 g_pen_ele_details(i).effective_end_date,
4922 l_rec_ele_ent_details.effective_end_date));
4923 FETCH csr_get_end_date INTO l_end_date;
4924 CLOSE csr_get_end_date;
4925 DEBUG('l_end_date: '|| TO_CHAR(l_end_date, 'DD/MON/YYYY'));
4926 l_value := hr_gbbal.calc_balance (
4927 p_assignment_id=> g_pen_ele_details(i).assignment_id,
4928 p_balance_type_id=> l_bal_type_id,
4929 p_period_from_date=> GREATEST(g_effective_start_date,
4930 g_pen_ele_details(i).effective_start_date,
4931 l_rec_ele_ent_details.effective_start_date),
4932 p_event_from_date=> GREATEST(g_effective_start_date,
4933 g_pen_ele_details(i).effective_start_date,
4934 l_rec_ele_ent_details.effective_start_date),
4935 p_to_date=> l_end_date,
4936 p_action_sequence=> NULL
4937 );
4938 DEBUG ('l_value: '|| l_value);
4939 l_total_value := l_total_value + l_value;
4940
4941 l_balance_name := 'NI '|| g_ni_cont_out_ele_ids(j).category || ' Able ET';
4942 DEBUG('l_balance_name: '||l_balance_name);
4943 l_bal_type_id := get_pay_bal_id (p_balance_name => l_balance_name);
4944 l_value := hr_gbbal.calc_balance (
4945 p_assignment_id=> g_pen_ele_details(i).assignment_id,
4946 p_balance_type_id=> l_bal_type_id,
4947 p_period_from_date=> GREATEST(g_effective_start_date,
4948 g_pen_ele_details(i).effective_start_date,
4949 l_rec_ele_ent_details.effective_start_date),
4950 p_event_from_date=> GREATEST(g_effective_start_date,
4951 g_pen_ele_details(i).effective_start_date,
4952 l_rec_ele_ent_details.effective_start_date),
4953 p_to_date=> l_end_date,
4954 p_action_sequence=> NULL
4955 );
4956 DEBUG ('l_value: '|| l_value);
4957 l_total_value := l_total_value + l_value;
4958 EXIT;
4959 END IF; -- Category matches
4960 j := g_ni_cont_out_ele_ids.NEXT(j);
4961 END LOOP; -- contracted out ele ids pl/sql collection
4962 END LOOP; -- cursor loop
4963 CLOSE csr_get_ele_ent_details;
4964 END IF; -- End if of assignment id matches check ...
4965 i := g_pen_ele_details.NEXT(i);
4966 END LOOP; -- pen ele details collection
4967
4968 l_total_value := l_total_value * 100;
4969 -- Bug Fix 5021075
4970
4971 IF l_total_value > 99999999999
4972 THEN
4973 l_total_value := 99999999999;
4974 ELSIF l_total_value < 0
4975 THEN
4976 l_return := pqp_gb_tp_extract_functions.raise_extract_error
4977 (p_business_group_id => g_business_group_id
4978 ,p_assignment_id => p_assignment_id
4979 ,p_error_text => 'BEN_94556_EXT_VALUE_ERROR'
4980 ,p_error_number => 94556
4981 ,p_token1 => 'NI Earnings'
4982 ,p_fatal_flag => 'N'
4983 );
4984 END IF; -- End if of value exceed max limit check ...
4985
4986 IF l_total_value >= 0 THEN
4987 l_ni_earnings := TRIM (TO_CHAR (l_total_value, '09999999999'));
4988 ELSE
4989 l_ni_earnings := TRIM (TO_CHAR (l_total_value, 'S0999999999'));
4990 END IF;
4991 DEBUG ( 'NI Earnings: '
4992 || l_ni_earnings);
4993 debug_exit (l_proc_name);
4994 RETURN l_ni_earnings;
4995 --
4996 EXCEPTION
4997 WHEN OTHERS
4998 THEN
4999 DEBUG ( ' Others in '
5000 || l_proc_name, 'Y' -- turn trace off
5001 );
5002 RAISE;
5003 END get_ni_earnings;
5004
5005
5006 -- This function returns the additional contribution for a given assignment
5007 -- PS Additional Contribution has a balance called "Total Additional Contributions"
5008 -- (Default) or the user provided balance name in the CPX definition UDT
5009 --
5010 -- ----------------------------------------------------------------------------
5011 -- |-------------------------< get_additional_contributions >-----------------|
5012 -- ----------------------------------------------------------------------------
5013 FUNCTION get_additional_contributions (p_assignment_id IN NUMBER)
5014 RETURN VARCHAR2
5015 IS
5016
5017 --
5018 l_proc_name VARCHAR2 (60)
5019 := g_proc_name
5020 || 'get_additional_contributions';
5021 l_add_contributions VARCHAR2 (11);
5022 l_value NUMBER;
5023
5024 --
5025 BEGIN
5026 --
5027 debug_enter (l_proc_name);
5028 l_add_contributions :=
5029 get_remuneration_from_bal (
5030 p_assignment_id=> p_assignment_id,
5031 p_balance_type_id=> g_additional_cont_bal_id,
5032 p_effective_start_date=> g_effective_start_date,
5033 p_effective_end_date=> g_effective_end_date
5034 );
5035 -- Bug Fix 5021075
5036 IF TO_NUMBER(l_add_contributions) < 0 THEN
5037 l_value := pqp_gb_tp_extract_functions.raise_extract_error
5038 (p_business_group_id => g_business_group_id
5039 ,p_assignment_id => p_assignment_id
5040 ,p_error_text => 'BEN_94556_EXT_VALUE_ERROR'
5041 ,p_error_number => 94556
5042 ,p_token1 => 'Additional Contributions'
5043 ,p_fatal_flag => 'N'
5044 );
5045 END IF;
5046 DEBUG ( 'Additional Contributions: '
5047 || l_add_contributions);
5048 debug_exit (l_proc_name);
5049 RETURN l_add_contributions;
5050 --
5051 EXCEPTION
5052 WHEN OTHERS
5053 THEN
5054 DEBUG ( ' Others in '
5055 || l_proc_name, 'Y' -- turn trace off
5056 );
5057 RAISE;
5058 END get_additional_contributions;
5059
5060
5061 -- This function returns the buy back contribution for a given assignment
5062 -- PS BuyBack contribution has a balance called "Total BuyBack Contributions"
5063 -- (Default) or the user provided balance name in the CPX definition UDT
5064 --
5065 -- ----------------------------------------------------------------------------
5066 -- |-------------------------< get_buyback_contributions >-----------------|
5067 -- ----------------------------------------------------------------------------
5068 FUNCTION get_buyback_contributions (p_assignment_id IN NUMBER)
5069 RETURN VARCHAR2
5070 IS
5071
5072 --
5073 l_proc_name VARCHAR2 (60)
5074 := g_proc_name
5075 || 'get_buyback_contributions';
5076 l_buyback_contributions VARCHAR2 (11);
5077 l_value NUMBER;
5078
5079 --
5080 BEGIN
5081 --
5082 debug_enter (l_proc_name);
5083 l_buyback_contributions :=
5084 get_remuneration_from_bal (
5085 p_assignment_id=> p_assignment_id,
5086 p_balance_type_id=> g_buyback_cont_bal_id,
5087 p_effective_start_date=> g_effective_start_date,
5088 p_effective_end_date=> g_effective_end_date
5089 );
5090 -- Bug Fix 5021075
5091 IF TO_NUMBER(l_buyback_contributions) < 0 THEN
5092 l_value := pqp_gb_tp_extract_functions.raise_extract_error
5093 (p_business_group_id => g_business_group_id
5094 ,p_assignment_id => p_assignment_id
5095 ,p_error_text => 'BEN_94556_EXT_VALUE_ERROR'
5096 ,p_error_number => 94556
5097 ,p_token1 => 'BuyBack Contribution'
5098 ,p_fatal_flag => 'N'
5099 );
5100 END IF;
5101 DEBUG ( 'BuyBack Contributions: '
5102 || l_buyback_contributions);
5103 debug_exit (l_proc_name);
5104 RETURN l_buyback_contributions;
5105 --
5106 EXCEPTION
5107 WHEN OTHERS
5108 THEN
5109 DEBUG ( ' Others in '
5110 || l_proc_name, 'Y' -- turn trace off
5111 );
5112 RAISE;
5113 END get_buyback_contributions;
5114
5115
5116 --
5117 -- Added for Hour Change Report
5118 --
5119
5120
5121
5122 -- This function checks whether an assignment/person qualifies for PTHRCH CPX
5123 -- report and returns a 'Y', 'N' or 'ERROR'
5124 --
5125 -- ----------------------------------------------------------------------------
5126 -- |-----------------------< chk_employee_qual_for_pthrch >------------------|
5127 -- ----------------------------------------------------------------------------
5128
5129 FUNCTION chk_employee_qual_for_pthrch (
5130 p_business_group_id IN NUMBER -- context
5131 ,
5132 p_effective_date IN DATE -- context
5133 ,
5134 p_assignment_id IN NUMBER -- context
5135 ,
5136 p_error_number OUT NOCOPY NUMBER,
5137 p_error_text OUT NOCOPY VARCHAR2
5138 )
5139 RETURN VARCHAR2 -- Y or N
5140 IS
5141
5142 --
5143 l_inclusion_flag VARCHAR2 (20) := 'N';
5144 l_proc_name VARCHAR2 (61)
5145 := g_proc_name
5146 || 'chk_employee_qual_for_pthrch';
5147 l_secondary_asg_ids t_number;
5148 l_error_number NUMBER;
5149 l_error_text VARCHAR2 (200);
5150 l_return NUMBER;
5151 i NUMBER;
5152 l_event_details pqp_utilities.t_event_details_table_type;
5153
5154 --
5155 BEGIN
5156 debug_enter (l_proc_name);
5157 l_error_text := NULL;
5158 l_error_number := NULL;
5159 DEBUG ( 'Business Group ID: '
5160 || TO_CHAR (g_business_group_id));
5161 DEBUG ( 'Assignment ID: '
5162 || TO_CHAR (p_assignment_id));
5163 DEBUG ( 'Session Date: '
5164 || p_effective_date);
5165
5166 IF g_business_group_id IS NULL
5167 THEN
5168 g_pension_ele_ids.DELETE;
5169 g_pension_bal_name := NULL;
5170 g_pension_ele_name := NULL;
5171 g_initial_ext_date := NULL;
5172 g_emp_cont_iv_name := NULL;
5173 g_superann_refno_iv_name := NULL;
5174 g_superann_sal_bal_name := NULL;
5175 g_additional_cont_bal_name := NULL;
5176 g_buyback_cont_bal_name := NULL;
5177 g_superann_sal_bal_id := NULL;
5178 g_additional_cont_bal_id := NULL;
5179 g_buyback_cont_bal_id := NULL;
5180 g_ele_entry_details.DELETE;
5181 g_secondary_asg_ids.DELETE;
5182 g_asg_details.DELETE;
5183 g_ni_ele_details.DELETE;
5184 g_ni_ele_type_id := NULL;
5185 g_ni_cat_iv_id := NULL;
5186 g_ni_pen_iv_id := NULL;
5187 g_pen_ele_details.DELETE;
5188 g_index := 0;
5189
5190 -- Use STARTERS for starters, HOURCHANGE for hour change and ANNUAL
5191 -- for Annual report
5192 g_header_system_element := 'HOURCHANGE:';
5193 DEBUG ('Before calling set_extract_globals function');
5194 l_return :=
5195 set_extract_globals (
5196 p_assignment_id=> p_assignment_id,
5197 p_business_group_id=> p_business_group_id,
5198 p_effective_date=> ben_ext_person.g_effective_date,
5199 p_error_number=> l_error_number,
5200 p_error_text=> l_error_text
5201 );
5202
5203 IF l_return <> 0
5204 THEN
5205 DEBUG ('Function set_extract_globals function is in Error');
5206 p_error_text := l_error_text;
5207 p_error_number := l_error_number;
5208 l_inclusion_flag := 'ERROR';
5209 debug_exit (l_proc_name);
5210 RETURN l_inclusion_flag;
5211 END IF; -- End if of return <> 0 check...
5212 END IF;
5213
5214 DEBUG ('Before calling chk_is_employee_a_member function');
5215 --
5216 -- Check the person is a member
5217 --
5218
5219 g_pen_ele_details.DELETE;
5220 g_index := 0;
5221
5222 l_inclusion_flag :=
5223 chk_is_employee_a_member (
5224 p_assignment_id=> p_assignment_id,
5225 p_effective_start_date=> g_effective_start_date,
5226 p_effective_end_date=> g_effective_end_date
5227 );
5228 DEBUG ( 'Inclusion Flag: '
5229 || l_inclusion_flag);
5230 l_event_details.DELETE;
5231
5232 IF l_inclusion_flag = 'Y'
5233 AND -- One or more HOUR CHANGE events have been found
5234 pqp_utilities.get_events (
5235 p_assignment_id=> p_assignment_id,
5236 p_business_group_id=> p_business_group_id,
5237 p_process_mode=> 'ENTRY_EFFECTIVE_DATE',
5238 p_event_group_name=> 'PQP_GB_CPX_HOUR_CHANGE',
5239 p_start_date=> g_effective_start_date,
5240 p_end_date=> g_effective_end_date,
5241 t_event_details=> l_event_details -- OUT
5242 ) > 0 -- Zero
5243 THEN
5244 DEBUG ('Assignment qualifies for PTHRCH report');
5245 -- Populate assignment details
5246
5247 set_assignment_details (
5248 p_assignment_id=> p_assignment_id,
5249 p_effective_date=> LEAST (
5250 g_ele_entry_details (p_assignment_id).effective_end_date,
5251 g_effective_end_date
5252 )
5253 );
5254 DEBUG ('Get Secondary Assignments');
5255 -- Get Secondary Assignments
5256
5257 DEBUG ('Before calling all secondary assignments procedure');
5258 get_all_sec_assignments (
5259 p_assignment_id=> p_assignment_id,
5260 p_secondary_asg_ids=> l_secondary_asg_ids
5261 );
5262 i := l_secondary_asg_ids.FIRST;
5263
5264 WHILE i IS NOT NULL
5265 LOOP
5266 DEBUG ('Secondary assignment exist');
5267 DEBUG ('Check this secondary asg qualifies for PTHRCH report');
5268 DEBUG ('Before calling function chk_is_employee_a_member');
5269 l_event_details.DELETE;
5270
5271 IF chk_is_employee_a_member (
5272 p_assignment_id=> l_secondary_asg_ids (i),
5273 p_effective_start_date=> g_effective_start_date,
5274 p_effective_end_date=> g_effective_end_date
5275 ) = 'Y'
5276 AND -- One or more HOUR CHANGE events have been found
5277 pqp_utilities.get_events (
5278 p_assignment_id=> l_secondary_asg_ids (i),
5279 p_business_group_id=> p_business_group_id,
5280 p_process_mode=> 'ENTRY_EFFECTIVE_DATE',
5281 p_event_group_name=> 'PQP_GB_CPX_HOUR_CHANGE',
5282 p_start_date=> g_effective_start_date,
5283 p_end_date=> g_effective_end_date,
5284 t_event_details=> l_event_details -- OUT
5285 ) > 0 -- Zero
5286 THEN
5287 DEBUG (
5288 TO_CHAR (l_secondary_asg_ids (i))
5289 || ' Secondary assignment qualifies'
5290 );
5291 g_secondary_asg_ids (i) := l_secondary_asg_ids (i);
5292 END IF; -- End if of secondary asg check for pthrch ..
5293
5294 i := l_secondary_asg_ids.NEXT (i);
5295 END LOOP; -- End loop of secondary assignments ...
5296 --
5297 ELSE -- Either HOUR CHANGE events NOTFOUND OR flag was already N
5298 l_inclusion_flag := 'N';
5299 END IF; -- End if of inclusion Flag Check...
5300
5301 debug_exit (l_proc_name);
5302 RETURN l_inclusion_flag;
5303 EXCEPTION
5304 WHEN OTHERS
5305 THEN
5306 debug_exit ( ' Others in '
5307 || l_proc_name, 'Y' -- turn trace off
5308 );
5309 p_error_number := SQLCODE;
5310 p_error_text := SQLERRM;
5311 RAISE;
5312 END chk_employee_qual_for_pthrch;
5313
5314
5315 -- This function gets the fte value for a given assignment and effective date
5316 --
5317 -- ----------------------------------------------------------------------------
5318 -- |------------------------------< get_fte_value >---------------------------|
5319 -- ----------------------------------------------------------------------------
5320 FUNCTION get_fte_value (p_assignment_id IN NUMBER, p_effective_date IN DATE)
5321 RETURN NUMBER
5322 IS
5323
5324 --
5325 l_proc_name VARCHAR2 (60) := g_proc_name
5326 || 'get_fte_value';
5327 l_fte_value NUMBER;
5328
5329 --
5330 BEGIN
5331 --
5332 debug_enter (l_proc_name);
5333 OPEN csr_get_fte_value (p_assignment_id, p_effective_date);
5334 FETCH csr_get_fte_value INTO l_fte_value;
5335 CLOSE csr_get_fte_value;
5336 l_fte_value := NVL (l_fte_value, 0);
5337 DEBUG (
5338 TO_CHAR (p_assignment_id)
5339 || ' FTE Value: '
5340 || TO_CHAR (l_fte_value)
5341 );
5342 debug_exit (l_proc_name);
5343 RETURN l_fte_value;
5344 END get_fte_value;
5345
5346
5347 --
5348
5349 -- This function gets the part time hours or percent value for a given
5350 -- assignment
5351 --
5352 -- ----------------------------------------------------------------------------
5353 -- |-----------------------------< get_part_time_percent >--------------------|
5354 -- ----------------------------------------------------------------------------
5355 FUNCTION get_part_time_percent (p_assignment_id IN NUMBER)
5356 RETURN VARCHAR2
5357 IS
5358
5359 --
5360 l_proc_name VARCHAR2 (60)
5361 := g_proc_name
5362 || 'get_part_time_percent';
5363 l_part_time_percent VARCHAR2 (11);
5364 l_value NUMBER;
5365 i NUMBER;
5366 l_secondary_asg_ids t_number;
5367 l_return_value NUMBER;
5368
5369 --
5370 BEGIN
5371 --
5372 debug_enter (l_proc_name);
5373 DEBUG ('Primary Assignment');
5374 -- Get fte value for primary assignment
5375 l_value :=
5376 get_fte_value (
5377 p_assignment_id=> p_assignment_id,
5378 p_effective_date=> LEAST (
5379 g_ele_entry_details (p_assignment_id).effective_end_date,
5380 g_effective_end_date
5381 )
5382 );
5383 -- Check for secondary assignments
5384 DEBUG ('Secondary Assignment');
5385 get_eligible_sec_assignments (
5386 p_assignment_id=> p_assignment_id,
5387 p_secondary_asg_ids=> l_secondary_asg_ids
5388 );
5389 i := l_secondary_asg_ids.FIRST;
5390
5391 WHILE i IS NOT NULL
5392 LOOP
5393 IF g_ele_entry_details.EXISTS (i)
5394 THEN
5395 -- Get fte value for this assignment
5396 l_value :=
5397 l_value
5398 + get_fte_value (
5399 p_assignment_id=> l_secondary_asg_ids (i),
5400 p_effective_date=> GREATEST (
5401 g_ele_entry_details (
5402 l_secondary_asg_ids (i)
5403 ).effective_start_date,
5404 g_effective_start_date
5405 )
5406 );
5407 END IF; -- End if of element entry details exists check...
5408
5409 i := l_secondary_asg_ids.NEXT (i);
5410 END LOOP; -- End loop of secondary assignments ...
5411
5412 l_value := l_value * POWER (10, 10);
5413 DEBUG ( 'Value before formatting : '
5414 || TO_CHAR (l_value));
5415 -- Bug Fix 5021075
5416 IF l_value < 0 THEN
5417 l_return_value := pqp_gb_tp_extract_functions.raise_extract_error
5418 (p_business_group_id => g_business_group_id
5419 ,p_assignment_id => p_assignment_id
5420 ,p_error_text => 'BEN_94556_EXT_VALUE_ERROR'
5421 ,p_error_number => 94556
5422 ,p_token1 => 'Part Time Percent'
5423 ,p_fatal_flag => 'N'
5424 );
5425 END IF;
5426 IF l_value >= 0 THEN
5427 l_part_time_percent := TRIM (TO_CHAR (l_value, '09999999999'));
5428 ELSE
5429 l_part_time_percent := TRIM (TO_CHAR (l_value, 'S0999999999'));
5430 END IF;
5431 DEBUG ( 'Part Time Percent: '
5432 || l_part_time_percent);
5433 debug_exit (l_proc_name);
5434 RETURN l_part_time_percent;
5435 --
5436 EXCEPTION
5437 WHEN OTHERS
5438 THEN
5439 DEBUG ( ' Others in '
5440 || l_proc_name, 'Y' -- turn trace off
5441 );
5442 RAISE;
5443 END get_part_time_percent;
5444 --
5445 --
5446 END pqp_gb_cpx_extract_functions;