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