[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_PSI_ALLOWANCE_HISTORY
Source
1 PACKAGE BODY PQP_GB_PSI_ALLOWANCE_HISTORY AS
2 -- /* $Header: pqpgbpsiall.pkb 120.4.12010000.7 2009/02/11 06:40:27 namgoyal ship $ */
3
4
5
6 -- Exceptions
7 hr_application_error exception;
8 pragma exception_init (hr_application_error, -20001);
9
10
11 g_nested_level NUMBER(5) := pqp_utilities.g_nested_level;
12
13 -- ----------------------------------------------------------------------------
14 -- |--------------------------------< debug >---------------------------------|
15 -- ----------------------------------------------------------------------------
16
17 PROCEDURE DEBUG (p_trace_message IN VARCHAR2, p_trace_location IN NUMBER)
18 IS
19
20 --
21 BEGIN
22 --
23
24 pqp_utilities.DEBUG (
25 p_trace_message => p_trace_message
26 ,p_trace_location => p_trace_location
27 );
28 --
29 END DEBUG;
30
31
32 -- This procedure is used for debug purposes
33 -- debug_enter checks the debug flag and sets the trace on/off
34 --
35 -- ----------------------------------------------------------------------------
36 -- |----------------------------< debug_enter >-------------------------------|
37 -- ----------------------------------------------------------------------------
38
39 PROCEDURE debug_enter (p_proc_name IN VARCHAR2, p_trace_on IN VARCHAR2)
40 IS
41 BEGIN
42 --
43 IF g_debug THEN
44 IF pqp_utilities.g_nested_level = 0 THEN
45 hr_utility.trace_on(NULL, 'REQID'); -- Pipe name REQIDnnnnn
46 END IF;
47 pqp_utilities.debug_enter (
48 p_proc_name => p_proc_name
49 ,p_trace_on => p_trace_on
50 );
51 END IF;
52 --
53
54 END debug_enter;
55
56
57 -- This procedure is used for debug purposes
58 --
59 -- ----------------------------------------------------------------------------
60 -- |----------------------------< debug_exit >--------------------------------|
61 -- ----------------------------------------------------------------------------
62
63 PROCEDURE debug_exit (p_proc_name IN VARCHAR2, p_trace_off IN VARCHAR2)
64 IS
65 BEGIN
66 --
67 IF g_debug THEN
68 pqp_utilities.debug_exit (
69 p_proc_name => p_proc_name
70 ,p_trace_off => p_trace_off
71 );
72
73 IF pqp_utilities.g_nested_level = 0 THEN
74 hr_utility.trace_off;
75 END IF;
76 END IF;
77 --
78 END debug_exit;
79
80 -- This procedure is used for debug purposes
81 --
82 -- ----------------------------------------------------------------------------
83 -- |----------------------------< debug_others >------------------------------|
84 -- ----------------------------------------------------------------------------
85
86 PROCEDURE debug_others (p_proc_name IN VARCHAR2, p_proc_step IN NUMBER)
87 IS
88 BEGIN
89 --
90 pqp_utilities.debug_others (
91 p_proc_name => p_proc_name
92 ,p_proc_step => p_proc_step
93 );
94 --
95 END debug_others;
96
97
98
99
100 -- This procedure is used to clear all cached global variables
101 --
102 -- ----------------------------------------------------------------------------
103 -- |----------------------------< clear_cache >-------------------------------|
104 -- ----------------------------------------------------------------------------
105 PROCEDURE clear_cache
106 IS
107 --
108 l_proc_name VARCHAR2(80) := g_proc_name || 'clear_cache';
109 l_proc_step PLS_INTEGER;
110 --
111 BEGIN
112 --
113 IF g_debug
114 THEN
115 l_proc_step := 10;
116 debug_enter(l_proc_name);
117 END IF;
118
119 -- start clearing globals
120 g_business_group_id := NULL;
121 g_assignment_id := NULL;
122 g_person_id := NULL;
123 g_person_dtl := NULL;
124 g_assignment_dtl := NULL;
125 g_effective_date := NULL;
126 g_extract_type := NULL;
127
128 g_current_run := NULL;
129 g_current_layout := NULL;
130 g_altkey := NULL;
131
132 -- globals set by set_shared_globals
133 g_paypoint := NULL;
134 g_cutover_date := NULL;
135 g_ext_dfn_id := NULL;
136
137 --
138
139 IF g_debug
140 THEN
141 debug_exit(l_proc_name);
142 END IF;
143 EXCEPTION
144 WHEN others THEN
145 IF SQLCODE <> hr_utility.hr_error_number
146 THEN
147 debug_others (l_proc_name, l_proc_step);
148 IF g_debug
149 THEN
150 DEBUG ( 'Leaving: '
151 || l_proc_name, -999);
152 END IF;
153 fnd_message.raise_error;
154 ELSE
155 RAISE;
156 END IF;
157 END clear_cache;
158
159
160
161 -- ----------------------------------------------------------------------------
162 -- |------------------------< set_allowance_history_globals >-----------------|
163 -- ----------------------------------------------------------------------------
164 PROCEDURE set_allowance_history_globals
165 (p_business_group_id IN NUMBER
166 ,p_effective_date IN DATE
167 ,p_assignment_id IN NUMBER
168 )
169 IS
170 --
171
172 l_proc_name VARCHAR2(61):=
173 g_proc_name||'set_allowance_history_globals';
174
175 l_rate_name VARCHAR2(80);
176 l_rate_code VARCHAR2(80);
177 l_sal_ele_fte_attr VARCHAR2(80) := NULL;
178
179 --
180 BEGIN
181
182 debug_enter(l_proc_name);
183
184 debug('Entering set_allowance_history_globals ...',10);
185 debug ('p_business_group_id:'||p_business_group_id);
186 debug ('p_effective_date:'||p_effective_date);
187 debug ('p_assignment_id:'||p_assignment_id);
188
189 PQP_GB_PSI_FUNCTIONS.get_elements_of_info_type
190 (p_information_type => 'PQP_GB_PENSERV_ALLOWANCE_INFO' -- IN VARCHAR2
191 --,p_input_value => 'CLAIM DATE' -- IN VARCHAR2 DEFAULT 'PAY VALUE'
192 );
193
194 debug('p_business_group_id: '||p_business_group_id,10);
195
196 pqp_gb_psi_functions.get_rate_usr_func_name
197 (
198 p_business_group_id => p_business_group_id
199 ,p_legislation_code => 'GB' -- g_legislation_code
200 ,p_interface_name => 'ALLOWANCE'
201 ,p_rate_name => l_rate_name
202 ,p_rate_code => l_rate_code
203 ,p_usr_rate_function => g_user_rate_function
204 ,p_sal_ele_fte_attr => l_sal_ele_fte_attr -- dummy, not used
205 );
206
207
208 debug('Exiting set_allowance_history_globals ...',60);
209 debug_exit(l_proc_name);
210
211 EXCEPTION
212 WHEN others THEN
213 IF SQLCODE <> hr_utility.hr_error_number
214 THEN
215 debug_others (l_proc_name, 10);
216 IF g_debug
217 THEN
218 DEBUG ( 'Leaving: '
219 || l_proc_name, -999);
220 END IF;
221 fnd_message.raise_error;
222 ELSE
223 RAISE;
224 END IF;
225
226 END set_allowance_history_globals;
227
228
229 -- ----------------------------------------------------------------------------
230 -- |-----------------------< set_assignment_globals >--------------------------|
231 -- ----------------------------------------------------------------------------
232 PROCEDURE set_assignment_globals
233 (
234 p_assignment_id IN NUMBER
235 ,p_effective_date IN DATE
236 )
237 IS
238 l_proc_name varchar2(72) := g_proc_name||'.set_assignment_globals';
239
240 CURSOR csr_start_date
241 IS
242 select DECODE(PER.CURRENT_EMPLOYEE_FLAG,'Y',PPS.DATE_START,NULL)
243 from per_all_people_f PER, per_periods_of_service PPS
244 where per.person_id = g_person_id
245 and pps.person_id = g_person_id
246 and rownum=1
247 order by per.effective_start_date;
248
249 BEGIN -- set_assignment_globals
250 debug_enter(l_proc_name);
251 debug('Inputs are: ',10);
252 debug('p_assignment_id: '||p_assignment_id,10);
253 debug('p_effective_date: '||to_char(p_effective_date,'dd/mm/yyyy'),10);
254
255 -- set the global events table
256 g_pay_proc_evt_tab := ben_ext_person.g_pay_proc_evt_tab;
257
258 -- set global assignment_id
259 g_assignment_id := p_assignment_id;
260 debug('g_assignment_id: '||g_assignment_id,10);
261
262 g_person_id := PQP_GB_PSI_FUNCTIONS.get_current_extract_person
263 (
264 p_assignment_id => p_assignment_id
265 );
266
267 --set the assignment start date
268 OPEN csr_start_date;
269 FETCH csr_start_date INTO g_assg_start_date;
270 CLOSE csr_start_date;
271
272 PQP_GB_PSI_FUNCTIONS.init_st_end_date_glob();
273
274 debug_exit(l_proc_name);
275 EXCEPTION
276 WHEN others THEN
277 IF SQLCODE <> hr_utility.hr_error_number
278 THEN
279 debug_others (l_proc_name, 10);
280 IF g_debug
281 THEN
282 DEBUG ( 'Leaving: '
283 || l_proc_name, -999);
284 END IF;
285 fnd_message.raise_error;
286 ELSE
287 RAISE;
288 END IF;
289 END set_assignment_globals;
290
291
292
293 FUNCTION is_curr_evt_processed RETURN BOOLEAN
294 IS
295 l_proc varchar2(72) := g_proc_name||'.is_curr_evt_processed';
296 l_prev_event_dtl_rec ben_ext_person.t_detailed_output_tab_rec;
297 l_flag VARCHAR2(1);
298 BEGIN
299 debug_enter(l_proc);
300 IF g_prev_event_dtl_rec.dated_table_id IS NOT NULL THEN
301 l_prev_event_dtl_rec := g_pay_proc_evt_tab(ben_ext_person.g_chg_pay_evt_index);
302 l_prev_event_dtl_rec.change_mode := g_prev_event_dtl_rec.change_mode;
303 l_flag := 'Y';
304 IF l_prev_event_dtl_rec.dated_table_id <> g_prev_event_dtl_rec.dated_table_id THEN
305 l_flag := 'N';
306 ELSIF l_prev_event_dtl_rec.datetracked_event <> g_prev_event_dtl_rec.datetracked_event THEN
307 l_flag := 'N';
308 ELSIF l_prev_event_dtl_rec.update_type <> g_prev_event_dtl_rec.update_type THEN
309 l_flag := 'N';
310 ELSIF l_prev_event_dtl_rec.surrogate_key <> g_prev_event_dtl_rec.surrogate_key THEN
311 l_flag := 'N';
312 ELSIF l_prev_event_dtl_rec.column_name <> g_prev_event_dtl_rec.column_name THEN
313 l_flag := 'N';
314 ELSIF l_prev_event_dtl_rec.effective_date <> g_prev_event_dtl_rec.effective_date THEN
315 l_flag := 'N';
316 ELSIF l_prev_event_dtl_rec.old_value <> g_prev_event_dtl_rec.old_value THEN
317 l_flag := 'N';
318 ELSIF l_prev_event_dtl_rec.new_value <> g_prev_event_dtl_rec.new_value THEN
319 l_flag := 'N';
320 ELSIF l_prev_event_dtl_rec.change_values <> g_prev_event_dtl_rec.change_values THEN
321 l_flag := 'N';
322 ELSIF l_prev_event_dtl_rec.proration_type <> g_prev_event_dtl_rec.proration_type THEN
323 l_flag := 'N';
324 ELSIF l_prev_event_dtl_rec.event_group_id <> g_prev_event_dtl_rec.event_group_id THEN
325 l_flag := 'N';
326 ELSIF l_prev_event_dtl_rec.actual_date <> g_prev_event_dtl_rec.actual_date THEN
327 l_flag := 'N';
328 END IF;
329
330 IF l_flag = 'Y' THEN
331 debug('Event already processed',30);
332 debug_exit(l_proc);
333 RETURN TRUE;
334 ELSE
335 g_prev_event_dtl_rec := g_pay_proc_evt_tab(ben_ext_person.g_chg_pay_evt_index);
336 END IF;
337 ELSE
338 debug('First event');
339 g_prev_event_dtl_rec := g_pay_proc_evt_tab(ben_ext_person.g_chg_pay_evt_index);
340 END IF;
341
342 debug_exit(l_proc);
343 RETURN FALSE;
344 END is_curr_evt_processed;
345 ----
346
347
348
349
350 -- ----------------------------------------------------------------------------
351 -- |------------------< all_cutover_ext_criteria >---------------------|
352 -- ----------------------------------------------------------------------------
353 FUNCTION all_cutover_ext_criteria
354 (
355 p_business_group_id IN NUMBER
356 ,p_assignment_id IN NUMBER
357 ,p_effective_date IN DATE
358 )RETURN VARCHAR2
359 IS
360 l_include VARCHAR2(1) := 'Y';
361 l_proc_name VARCHAR2(80) := g_proc_name ||'all_cutover_ext_criteria';
362 l_debug VARCHAR2(1);
363 l_error NUMBER;
364 BEGIN
365
366 debug_enter(l_proc_name);
367 debug('Inputs are: ');
368 debug('p_business_group_id: '||p_business_group_id);
369 debug('p_assignment_id: '||p_assignment_id);
370 debug('p_effective_date: '||to_char(p_effective_date,'dd/mm/yyyy'));
371 -- reset salary globals
372 g_current_layout := 'CUTOVER';
373 g_current_run := 'CUTOVER';
374 g_effective_date := p_effective_date;
375
376 IF g_business_group_id IS NULL
377 THEN
378
379 clear_cache;
380
381 -- set the global debug value
382 g_debug := pqp_gb_psi_functions.check_debug(p_business_group_id);
383
384 debug('Inputs are: ');
385 debug('p_business_group_id: '||p_business_group_id);
386 debug('p_assignment_id: '||p_assignment_id);
387 debug('p_effective_date: '||to_char(p_effective_date,'dd/mm/yyyy'));
388
389 PQP_GB_PSI_FUNCTIONS.set_shared_globals
390 (p_business_group_id => p_business_group_id
391 ,p_paypoint => g_paypoint
392 ,p_cutover_date => g_cutover_date
393 ,p_ext_dfn_id => g_ext_dfn_id
394 );
395
396 set_allowance_history_globals
397 (
398 p_business_group_id => p_business_group_id
399 ,p_assignment_id => p_assignment_id
400 ,p_effective_date => p_effective_date
401 );
402
403 g_business_group_id := p_business_group_id;
404 g_legislation_code := 'GB';
405
406 --Raise extract exceptions which are stored while checking for the setup
407 debug('Raising the set-up errors, with input parameter as S',10);
408 PQP_GB_PSI_FUNCTIONS.raise_extract_exceptions(p_extract_type => 'S');
409
410 END IF;
411
412 l_include := PQP_GB_PSI_FUNCTIONS.chk_penserver_basic_criteria
413 (p_business_group_id => p_business_group_id
414 ,p_effective_date => p_effective_date
415 ,p_assignment_id => p_assignment_id
416 ,p_person_dtl => g_curr_person_dtls
417 ,p_assignment_dtl => g_curr_assg_dtls
418 );
419
420 IF l_include = 'N' THEN
421 debug('Returning : '||l_include,30);
422 debug_exit(l_proc_name);
423 return l_include;
424 END IF; --IF l_include = 'N'
425
426 IF g_assignment_id IS NULL
427 OR p_assignment_id <> nvl(g_assignment_id,0) THEN
428
429 set_assignment_globals
430 (
431 p_assignment_id => p_assignment_id
432 ,p_effective_date => p_effective_date
433 );
434 END IF;
435
436 /*
437 IF l_include = 'N'
438 OR NOT set_curr_row_values() THEN
439 --current event is not accepted
440 l_include := 'N';
441 debug('Returning : '||l_include,20);
442 debug_exit(l_proc_name);
443 return l_include;
444 END IF;
445 */
446
447 debug('Returning : '||l_include,20);
448 debug_exit(l_proc_name);
449 RETURN l_include;
450 EXCEPTION
451 WHEN others THEN
452 IF SQLCODE <> hr_utility.hr_error_number
453 THEN
454 debug_others (l_proc_name, 10);
455 IF g_debug
456 THEN
457 DEBUG ( 'Leaving: '
458 || l_proc_name, -999);
459 END IF;
460 fnd_message.raise_error;
461 ELSE
462 RAISE;
463 END IF;
464 END all_cutover_ext_criteria;
465
466
467 --For bug 7829676: Added new function
468 -- ----------------------------------------------------------------------------
469 -- |---------------------< is_next_allow_code_same >---------------------|
470 --This function checks if there is an allowance element attached on the next day,
471 --which has same allowance code, as the allowance element which is currently
472 --getting end dated. If true, then we don't want the end date record to be
473 --reported
474 -- ----------------------------------------------------------------------------
475 FUNCTION is_next_allow_code_same(p_element_entry_id IN NUMBER,
476 p_eve_eff_date IN DATE,
477 p_assignment_id IN NUMBER
478 )
479 RETURN BOOLEAN
480 IS
481 --Cursor to get allowance code of current Allowance element
482 CURSOR csr_curr_ele_allow_code
483 IS
484 Select info.eei_information2
485 From pay_element_entries_f ent,
486 pay_element_type_extra_info info
487 Where info.eei_information_category = 'PQP_GB_PENSERV_ALLOWANCE_INFO'
488 and info.element_type_id = ent.element_type_id
489 and ent.element_entry_id = p_element_entry_id
490 and p_eve_eff_date BETWEEN ent.effective_start_date AND effective_end_date;
491
492 --Cursor to get the element attached on the following day
493 CURSOR csr_get_new_ele
494 IS
495 Select element_type_id
496 From pay_element_entries_f
497 Where assignment_id = p_assignment_id
498 and effective_start_date = p_eve_eff_date+1;
499
500 --Cursor to check if the new element is an allownance element and having a same
501 --allowance code.
502 CURSOR csr_match_allowance_code(c_element_type_id NUMBER, c_allow_code VARCHAR2)
503 IS
504 Select 'x'
505 From pay_element_type_rules elerule,
506 pay_event_group_usages eveusg,
507 pay_event_groups evegrp,
508 pay_element_type_extra_info elextra
509 Where elerule.element_type_id = c_element_type_id
510 and elerule.element_set_id = eveusg.element_set_id
511 and eveusg.event_group_id = evegrp.event_group_id
512 and evegrp.event_group_name = 'PQP_GB_PSI_ALL_ELEMENT_ENTRIES'
513 and elextra.element_type_id = elerule.element_type_id
514 and elextra.eei_information_category = 'PQP_GB_PENSERV_ALLOWANCE_INFO'
515 and elextra.eei_information2 = c_allow_code;
516
517 --Declare variables
518 l_allow_code VARCHAR2(20);
519 l_return_flag BOOLEAN := FALSE;
520 l_new_element_type_id NUMBER;
521 l_exists VARCHAR2(5);
522
523 BEGIN
524 debug('Entering: is_next_allow_code_same',100);
525 debug('p_element_entry_id :'||p_element_entry_id,100);
526 debug('p_eve_eff_date :'||p_eve_eff_date,100);
527 debug('p_assignment_id :'||p_assignment_id,100);
528
529 --get allowance code of current Allowance element
530 OPEN csr_curr_ele_allow_code;
531 FETCH csr_curr_ele_allow_code INTO l_allow_code;
532
533 IF csr_curr_ele_allow_code%NOTFOUND
534 OR l_allow_code IS NULL
535 THEN
536 CLOSE csr_curr_ele_allow_code;
537 l_return_flag := FALSE;
538 debug('No Allownace code found for this element',101);
539 debug('Return False', 101);
540 RETURN l_return_flag;
541 END IF;
542
543 debug('l_allow_code :'||l_allow_code,101);
544 CLOSE csr_curr_ele_allow_code;
545
546 --get the element attached on the following day and match the
547 --allownace code
548 OPEN csr_get_new_ele;
549 FETCH csr_get_new_ele INTO l_new_element_type_id;
550
551 IF csr_get_new_ele%NOTFOUND
552 THEN
553 CLOSE csr_get_new_ele;
554 l_return_flag := FALSE;
555 debug('No element attached on next day',102);
556 debug('Return False', 102);
557 RETURN l_return_flag;
558 END IF;
559
560 LOOP
561 debug('l_allow_code :'||l_allow_code,101);
562 OPEN csr_match_allowance_code(l_new_element_type_id, l_allow_code);
563 FETCH csr_match_allowance_code INTO l_exists;
564
565 IF csr_match_allowance_code%FOUND
566 THEN
567 CLOSE csr_match_allowance_code;
568 l_return_flag := TRUE;
569 debug('Allowance code is same for new element',103);
570 debug('Set Return flag to TRUE', 103);
571 EXIT;
572 END IF;
573
574 CLOSE csr_match_allowance_code;
575
576 FETCH csr_get_new_ele INTO l_new_element_type_id;
577 EXIT WHEN csr_get_new_ele%NOTFOUND;
578
579 END LOOP;
580 CLOSE csr_get_new_ele;
581
582 RETURN l_return_flag;
583
584 EXCEPTION
585 WHEN others THEN
586 IF SQLCODE <> hr_utility.hr_error_number
587 THEN
588 debug_others ('is_next_allow_code_same', 10);
589 IF g_debug
590 THEN
591 DEBUG ('Leaving: is_next_allow_code_same', -999);
592 END IF;
593 fnd_message.raise_error;
594 ELSE
595 RAISE;
596 END IF;
597 END is_next_allow_code_same;
598
599 -- ----------------------------------------------------------------------------
600 -- |---------------------< all_periodic_ext_criteria >---------------------|
601 -- ----------------------------------------------------------------------------
602 FUNCTION all_periodic_ext_criteria
603 (
604 p_business_group_id IN NUMBER
605 ,p_assignment_id IN NUMBER
606 ,p_effective_date IN DATE
607 )RETURN VARCHAR2
608 IS
609 l_include VARCHAR2(1) := 'Y';
610 l_proc_name VARCHAR2(80) := g_proc_name ||'all_periodic_ext_criteria';
611 l_error NUMBER;
612 l_curr_evt_index NUMBER;
613 l_return VARCHAR2(1) := 'Y';
614 -- For Bug 6082338
615 l_dated_table_id pay_dated_tables.dated_table_id%TYPE;
616 l_chg_table_name VARCHAR2(61);
617 l_chg_column_name pay_event_updates.column_name%TYPE;
618 l_update_type pay_datetracked_events.update_type%TYPE;
619
620 --For bug 7158117: Added new cursor
621 Cursor csr_get_ele_end_date (c_element_entry_id number)
622 IS
623 Select max(effective_end_date)
624 From PAY_ELEMENT_ENTRIES_F
625 Where element_entry_id = c_element_entry_id;
626
627 l_surrogate_key NUMBER;
628 l_ele_end_date DATE;
629 --For bug 7158117: End
630
631 --For bug 7229852: Added new cursor
632 CURSOR csr_get_atd
633 IS
634 Select actual_termination_date
635 From per_all_assignments_f asg, per_periods_of_service per
636 Where asg.assignment_id = p_assignment_id
637 And per.period_of_service_id = asg.period_of_service_id
638 AND p_effective_date between asg.effective_start_date and asg.effective_end_date;
639
640 l_eve_effective_date DATE;
641 l_actual_termination_date DATE;
642 --For bug 7229852: End
643
644 BEGIN --all_periodic_ext_criteria
645
646 debug_enter(l_proc_name);
647
648 debug('Inputs are: ',10);
649 debug('p_business_group_id: '||p_business_group_id,10);
650 debug('p_assignment_id: '||p_assignment_id,10);
651 debug('p_effective_date: '||to_char(p_effective_date,'dd/mm/yyyy'),10);
652
653 g_current_layout := 'PERIODIC';
654 g_current_run := 'PERIODIC';
655 g_effective_date := p_effective_date;
656
657 --
658 IF g_business_group_id IS NULL
659 OR p_business_group_id <> nvl(g_business_group_id,0) THEN
660
661 -- clear cache
662 clear_cache;
663
664 -- for trace switching ON/OFF
665 g_debug := PQP_GB_PSI_FUNCTIONS.check_debug(p_business_group_id);
666 -- setting shared globals
667 -- 1) paypoint
668 -- 2) cutover date
669 -- 3) extract def id
670 PQP_GB_PSI_FUNCTIONS.set_shared_globals
671 (p_business_group_id => p_business_group_id
672 ,p_paypoint => g_paypoint -- OUT
673 ,p_cutover_date => g_cutover_date -- OUT
674 ,p_ext_dfn_id => g_ext_dfn_id -- OUT
675 );
676
677 -- setting extract specific globals
678 set_allowance_history_globals
679 (p_business_group_id => p_business_group_id
680 ,p_assignment_id => p_assignment_id
681 ,p_effective_date => p_effective_date
682 );
683
684 g_business_group_id := p_business_group_id;
685 g_legislation_code := 'GB';
686
687 debug('now raise setup exceptions ...',15);
688 -- raise setup errors and warnings
689 PQP_GB_PSI_FUNCTIONS.raise_extract_exceptions(p_extract_type => 'S');
690
691 END IF; --IF g_business_group_id IS NULL
692
693 g_current_run := 'PERIODIC';
694 g_current_layout := 'PERIODIC';
695
696 debug('g_current_run :'||g_current_run);
697
698 debug('calling the basic criteria for this person assignment');
699 -- calling the basic criteria for this person assignment
700 l_return :=
701 PQP_GB_PSI_FUNCTIONS.chk_penserver_basic_criteria
702 (p_business_group_id => p_business_group_id
703 ,p_effective_date => p_effective_date
704 ,p_assignment_id => p_assignment_id
705 ,p_person_dtl => g_person_dtl
706 ,p_assignment_dtl => g_assignment_dtl
707 );
708
709 debug ('p_assignment_id:'||p_assignment_id);
710 debug('l_return: '||l_return);
711
712
713 IF l_return <> 'N'
714 THEN
715 debug('Calling the common include event proc');
716 -- set the global events table
717 g_pay_proc_evt_tab := ben_ext_person.g_pay_proc_evt_tab;
718 l_curr_evt_index := ben_ext_person.g_chg_pay_evt_index;
719
720 debug('----------');
721 debug('Record :'||l_curr_evt_index);
722 debug('----------');
723 debug('dated_table_id :'||g_pay_proc_evt_tab(l_curr_evt_index).dated_table_id ,20);
724 debug('datetracked_event :'||g_pay_proc_evt_tab(l_curr_evt_index).datetracked_event,20);
725 debug('surrogate_key :'||g_pay_proc_evt_tab(l_curr_evt_index).surrogate_key ,20);
726 debug('column_name :'||g_pay_proc_evt_tab(l_curr_evt_index).column_name ,20);
727 debug('update_type :'||g_pay_proc_evt_tab(l_curr_evt_index).update_type ,20);
728 debug('effective_date :'||to_char(g_pay_proc_evt_tab(l_curr_evt_index).effective_date,'DD/MM/YYYY'),20);
729 debug('actual_date :'||to_char(g_pay_proc_evt_tab(l_curr_evt_index).actual_date,'DD/MM/YYYY'),20);
730 debug('old_value :'||g_pay_proc_evt_tab(l_curr_evt_index).old_value ,20);
731 debug('new_value :'||g_pay_proc_evt_tab(l_curr_evt_index).new_value ,20);
732 debug('change_values :'||g_pay_proc_evt_tab(l_curr_evt_index).change_values ,20);
733 debug('proration_type :'||g_pay_proc_evt_tab(l_curr_evt_index).proration_type ,20);
734 debug('change_mode :'||g_pay_proc_evt_tab(l_curr_evt_index).change_mode ,20);
735 -- For Bug 6082338
736 l_dated_table_id := g_pay_proc_evt_tab(l_curr_evt_index).dated_table_id;
737 l_chg_column_name := g_pay_proc_evt_tab(l_curr_evt_index).column_name;
738 l_update_type := g_pay_proc_evt_tab(l_curr_evt_index).update_type;
739 l_chg_table_name := pqp_gb_psi_functions.get_dated_table_name(l_dated_table_id);
740
741 IF (l_chg_table_name = 'PER_ASSIGNMENT_BUDGET_VALUES_F'
742 AND
743 l_chg_column_name = 'EFFECTIVE_END_DATE'
744 AND
745 l_update_type = 'C') THEN
746 l_return := 'N';
747 debug('Returning : '||l_return,22);
748 debug_exit(l_proc_name);
749 return l_return;
750 END IF;
751
752 --For bug 7158117: Added condn to supress end date record
753 IF (l_chg_table_name = 'PAY_ELEMENT_ENTRIES_F'
754 AND l_chg_column_name = 'EFFECTIVE_END_DATE'
755 AND l_update_type = 'E')
756 THEN
757 l_surrogate_key := g_pay_proc_evt_tab(l_curr_evt_index).surrogate_key;
758 debug('l_surrogate_key :'||l_surrogate_key,23);
759
760 --For bug 7229852: Start
761 l_eve_effective_date := g_pay_proc_evt_tab(l_curr_evt_index).effective_date;
762 debug('l_eve_effective_date :'||l_eve_effective_date,23);
763 --For bug 7229852: End
764
765 OPEN csr_get_ele_end_date(l_surrogate_key);
766 FETCH csr_get_ele_end_date INTO l_ele_end_date;
767 CLOSE csr_get_ele_end_date;
768
769 debug('l_ele_end_date :'||l_ele_end_date,23);
770 --debug('hr_api.g_eot :'||hr_api.g_eot,23);
771
772 --For bug 7229852: Changed logic
773 OPEN csr_get_atd;
774 FETCH csr_get_atd INTO l_actual_termination_date;
775 CLOSE csr_get_atd;
776
777 debug('l_actual_termination_date :'||l_actual_termination_date,23);
778
779 IF l_ele_end_date <> l_eve_effective_date
780 OR
781 (l_actual_termination_date IS NOT NULL
782 AND
783 l_actual_termination_date < l_eve_effective_date)
784 THEN
785 l_return := 'N';
786 debug('Returning : '||l_return,23);
787 debug_exit(l_proc_name);
788 return l_return;
789 END IF;
790
791 --For bug 7829676: Start
792 IF is_next_allow_code_same(l_surrogate_key,
793 l_eve_effective_date,
794 p_assignment_id)
795 THEN
796 l_return := 'N';
797 debug('Returning : '||l_return,23);
798 debug_exit(l_proc_name);
799 return l_return;
800 END IF;
801 --For bug 7829676: End
802
803 END IF;
804 --For bug 7158117: End
805
806 --For Bug 7149468: Start
807 g_leaver_event := 'N';
808
809 --For Bug 7229852: Start
810 g_act_term_date := NULL;
811 --For Bug 7229852: End
812
813 IF (l_chg_table_name = 'PER_ALL_ASSIGNMENTS_F'
814 AND l_chg_column_name = 'ASSIGNMENT_STATUS_TYPE_ID'
815 AND l_update_type in ('U','C') --For bug 7229852: Added Correction event
816 )
817 THEN
818 g_leaver_event := 'Y';
819
820 --For Bug 7229852: Start
821 OPEN csr_get_atd;
822 FETCH csr_get_atd INTO g_act_term_date;
823 CLOSE csr_get_atd;
824
825 debug('g_act_term_date :'||g_act_term_date,23);
826 --For Bug 7229852: End
827
828 END IF;
829
830 debug('g_leaver_event : '||g_leaver_event ,22);
831 --For Bug 7149468: End
832
833
834 IF is_curr_evt_processed()
835 THEN
836 l_return := 'N';
837 debug('Returning : '||l_return,20);
838 debug_exit(l_proc_name);
839 return l_return;
840 END IF;
841
842 l_return := pqp_gb_psi_functions.include_event
843 (p_actual_date => g_pay_proc_evt_tab(l_curr_evt_index).actual_date
844 ,p_effective_date => g_pay_proc_evt_tab(l_curr_evt_index).effective_date
845 );
846
847 pqp_gb_psi_functions.process_retro_event();
848
849 debug ('p_assignment_id:'||p_assignment_id);
850 debug('include_event returned: '||l_return);
851
852 END IF;
853
854
855 -- IF l_return <> 'N' THEN
856 pqp_gb_psi_functions.g_effective_date := p_effective_date;
857
858 -- set assignment globals
859 IF g_assignment_id IS NULL
860 OR p_assignment_id <> nvl(g_assignment_id,0) THEN
861
862 set_assignment_globals
863 (
864 p_assignment_id => p_assignment_id
865 ,p_effective_date => p_effective_date
866 );
867 END IF;
868 -- END IF;
869
870 debug('l_return: '||l_return);
871
872 debug_exit(l_proc_name);
873 RETURN l_return;
874
875 EXCEPTION
876 WHEN others THEN
877 IF SQLCODE <> hr_utility.hr_error_number
878 THEN
879 debug_others (l_proc_name, 10);
880 IF g_debug
881 THEN
882 DEBUG ( 'Leaving: '
883 || l_proc_name, -999);
884 END IF;
885 fnd_message.raise_error;
886 ELSE
887 RAISE;
888 END IF;
889 END all_periodic_ext_criteria;
890 ---
891
892 -- ----------------------------------------------------------------------------
893 -- |---------------------------< get_allowance_code >--------------------------|
894 -- Description:
895 -- ----------------------------------------------------------------------------
896 FUNCTION get_allowance_code
897 (p_output OUT NOCOPY VARCHAR2
898 )RETURN NUMBER
899 IS
900 l_proc_name varchar2(72) := g_proc_name||'.get_allowance_code';
901 l_return NUMBER;
902 BEGIN
903 debug_enter(l_proc_name);
904
905 IF PQP_GB_PSI_FUNCTIONS.g_elements_of_info_type.exists(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id) THEN
906 g_allowance_code
907 := PQP_GB_PSI_FUNCTIONS.g_elements_of_info_type(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id).eei_information2;
908 ELSE
909 g_allowance_code := ' ';
910 END IF;
911
912 IF NOT pqp_gb_psi_functions.is_alphanumeric(g_allowance_code) THEN
913 debug('ERROR: the allowance code is non-alphanumeric',20);
914 l_return := PQP_GB_PSI_FUNCTIONS.raise_extract_error
915 (p_error_number => 94557
916 ,p_error_text => 'BEN_94557_INVALID_CODE'
917 ,p_token1 => 'Allowance'
918 ,p_token2 =>
919 PQP_GB_PSI_FUNCTIONS.g_elements_of_info_type(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id).element_name
920 ,p_token3 => g_allowance_code
921 );
922 END IF;
923
924
925 p_output := g_allowance_code;
926
927 debug_exit(l_proc_name);
928 return 0;
929 EXCEPTION
930 WHEN others THEN
931 IF SQLCODE <> hr_utility.hr_error_number
932 THEN
933 debug_others (l_proc_name, 10);
934 IF g_debug
935 THEN
936 DEBUG ( 'Leaving: '
937 || l_proc_name, -999);
938 END IF;
939 fnd_message.raise_error;
940 ELSE
941 RAISE;
942 END IF;
943 END get_allowance_code;
944
945
946
947 -- ----------------------------------------------------------------------------
948 -- |---------------------------< chk_dup_allow_types >--------------------------|
949 -- Description:
950 -- ----------------------------------------------------------------------------
951 FUNCTION chk_dup_allow_types
952 (p_assignment_id IN NUMBER
953 ,p_effective_date IN DATE
954 ) RETURN NUMBER
955 IS
956
957 cursor csr_check_dup_allow_types
958 is
959 select 1
960 from pay_element_entries_f pee, pay_element_type_extra_info petei
961 where pee.assignment_id = p_assignment_id
962 and pee.element_type_id = petei.element_type_id
963 and p_effective_date between pee.effective_start_date and pee.effective_end_date
964 and pee.element_entry_id <> PQP_GB_PSI_FUNCTIONS.g_curr_element_entry_id
965 and petei.information_type = 'PQP_GB_PENSERV_ALLOWANCE_INFO'
966 and petei.eei_information2 = g_allowance_code
967 and rownum=1;
968
969 l_proc_name varchar2(72) := g_proc_name||'.chk_dup_allow_types';
970 l_return NUMBER;
971 l_result NUMBER;
972
973 BEGIN
974 debug_enter(l_proc_name);
975
976 OPEN csr_check_dup_allow_types;
977 FETCH csr_check_dup_allow_types into l_result;
978 IF csr_check_dup_allow_types%NOTFOUND
979 THEN l_result := 0;
980 END IF;
981 CLOSE csr_check_dup_allow_types;
982
983 IF l_result = 1
984 THEN
985 debug('WARNING: Duplicate Allowance Type on same date');
986 l_return := PQP_GB_PSI_FUNCTIONS.raise_extract_warning
987 (p_error_number => 94595
988 ,p_error_text => 'BEN_94595_DUP_ALLOW_TYPE'
989 ,p_token1 => to_char(p_effective_date,'dd/mm/yyyy')
990 );
991 END IF;
992
993 debug_exit(l_proc_name);
994 return 0;
995 EXCEPTION
996 WHEN others THEN
997 IF SQLCODE <> hr_utility.hr_error_number
998 THEN
999 debug_others (l_proc_name, 10);
1000 IF g_debug
1001 THEN
1002 DEBUG ( 'Leaving: '
1003 || l_proc_name, -999);
1004 END IF;
1005 fnd_message.raise_error;
1006 ELSE
1007 RAISE;
1008 END IF;
1009 END chk_dup_allow_types;
1010
1011
1012 -- ----------------------------------------------------------------------------
1013 -- |---------------------------< get_allowance_ind_flag >--------------------------|
1014 -- Description:
1015 -- ----------------------------------------------------------------------------
1016 FUNCTION get_allowance_ind_flag
1017 (p_output OUT NOCOPY VARCHAR2
1018 )RETURN NUMBER
1019 IS
1020 l_proc_name varchar2(72) := g_proc_name||'get_allowance_ind_flag';
1021 BEGIN
1022 debug_enter(l_proc_name);
1023
1024 IF PQP_GB_PSI_FUNCTIONS.g_elements_of_info_type.exists(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id) THEN
1025 p_output
1026 := PQP_GB_PSI_FUNCTIONS.g_elements_of_info_type(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id).eei_information5;
1027 END IF;
1028
1029 IF p_output IS NULL
1030 THEN
1031 p_output := 'N';
1032 END IF;
1033
1034 debug_exit(l_proc_name);
1035 return 0;
1036 EXCEPTION
1037 WHEN others THEN
1038 IF SQLCODE <> hr_utility.hr_error_number
1039 THEN
1040 debug_others (l_proc_name, 10);
1041 IF g_debug
1042 THEN
1043 DEBUG ( 'Leaving: '
1044 || l_proc_name, -999);
1045 END IF;
1046 fnd_message.raise_error;
1047 ELSE
1048 RAISE;
1049 END IF;
1050 END get_allowance_ind_flag;
1051
1052
1053
1054 -- ----------------------------------------------------------------------------
1055 -- |---------------------< get_notional_allowance_rate >----------------------|
1056 -- Description:
1057 -- ----------------------------------------------------------------------------
1058 FUNCTION get_notional_allowance_rate
1059 (p_business_group_id IN NUMBER
1060 ,p_effective_date IN DATE
1061 ,p_assignment_id IN NUMBER
1062 ,p_output OUT NOCOPY VARCHAR2
1063 ) RETURN number
1064 IS
1065 l_proc_name varchar2(72) := g_proc_name||'get_notional_allowance_rate';
1066 l_include NUMBER;
1067 l_custom_function VARCHAR2(100) := 'get_user_notional_pay';
1068 BEGIN
1069 debug_enter(l_proc_name);
1070
1071 debug('PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id : '||PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id,10);
1072
1073 IF g_is_spread_bonus_yn = 'N' THEN
1074 IF g_allowance_end_dated_today <> 'Y'
1075 THEN
1076 l_include := PQP_GB_PSI_FUNCTIONS.get_notional_pay
1077 (p_assignment_id => p_assignment_id -- IN NUMBER
1078 ,p_business_group_id => p_business_group_id -- IN NUMBER
1079 ,p_effective_date => p_effective_date -- IN DATE
1080 ,p_name =>
1081 PQP_GB_PSI_FUNCTIONS.g_elements_of_info_type(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id).element_name
1082 -- IN VARCHAR2
1083 ,p_rt_element => 'E'
1084 ,p_rate => p_output -- OUT
1085 ,p_custom_function => g_user_rate_function -- IN VARCHAR2 DEFAULT NULL
1086 ,p_allowance_code => g_allowance_code -- IN VARCHAR2 DEFAULT NULL
1087 ,p_allowance_pet_id => PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id -- IN NUMBER DEFAULT NULL
1088 );
1089
1090 ELSE
1091 p_output := '0';
1092 END IF;
1093 ELSE
1094 p_output := pqp_gb_psi_functions.get_element_payment
1095 (p_assignment_id => p_assignment_id
1096 ,p_element_entry_id => PQP_GB_PSI_FUNCTIONS.g_curr_element_entry_id
1097 ,p_element_type_id => PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id
1098 ,p_effective_date => p_effective_date
1099 );
1100
1101 END IF;
1102
1103 g_notional_rate := p_output;
1104
1105
1106 debug_exit(l_proc_name);
1107 return 0;
1108 EXCEPTION
1109 WHEN others THEN
1110 IF SQLCODE <> hr_utility.hr_error_number
1111 THEN
1112 debug_others (l_proc_name, 10);
1113 IF g_debug
1114 THEN
1115 DEBUG ( 'Leaving: '
1116 || l_proc_name, -999);
1117 END IF;
1118 fnd_message.raise_error;
1119 ELSE
1120 RAISE;
1121 END IF;
1122 END get_notional_allowance_rate;
1123
1124
1125
1126 -- ----------------------------------------------------------------------------
1127 -- |---------------------< get_allowance_actual_pay >----------------------|
1128 -- Description:
1129 -- ----------------------------------------------------------------------------
1130 FUNCTION get_allowance_actual_pay
1131 (p_business_group_id IN NUMBER
1132 ,p_assignment_id IN NUMBER
1133 ,p_notional_pay IN NUMBER
1134 ,p_effective_date IN DATE
1135 ,p_output OUT NOCOPY VARCHAR2
1136 ) RETURN number
1137 IS
1138 l_proc_name varchar2(72) := g_proc_name||'get_allowance_actual_pay';
1139 l_include NUMBER;
1140 BEGIN
1141 debug_enter(l_proc_name);
1142
1143 IF g_is_spread_bonus_yn = 'N' THEN
1144 IF g_allowance_end_dated_today <> 'Y'
1145 THEN
1146 l_include := PQP_GB_PSI_FUNCTIONS.get_actual_pay
1147 (
1148 p_assignment_id => p_assignment_id -- IN NUMBER
1149 ,p_notional_pay => g_notional_rate -- IN NUMBER
1150 ,p_effective_date => p_effective_date -- IN DATE
1151 ,p_output => p_output -- OUT NOCOPY VARCHAR2
1152 );
1153 ELSE
1154 p_output := '0';
1155 END IF;
1156 ELSE
1157 p_output := g_notional_rate;
1158 g_allowance_actual_pay := g_notional_rate;
1159 END IF;
1160
1161 debug_exit(l_proc_name);
1162 return 0;
1163 EXCEPTION
1164 WHEN others THEN
1165 IF SQLCODE <> hr_utility.hr_error_number
1166 THEN
1167 debug_others (l_proc_name, 10);
1168 IF g_debug
1169 THEN
1170 DEBUG ( 'Leaving: '
1171 || l_proc_name, -999);
1172 END IF;
1173 fnd_message.raise_error;
1174 ELSE
1175 RAISE;
1176 END IF;
1177 END get_allowance_actual_pay;
1178
1179
1180 -- ----------------------------------------------------------------------------
1181 -- |---------------------< get_allowance_start_date >----------------------|
1182 -- Description:
1183 -- ----------------------------------------------------------------------------
1184 FUNCTION get_allowance_start_date
1185 (p_effective_date IN DATE
1186 ,p_output OUT NOCOPY VARCHAR2
1187 ) RETURN number
1188 IS
1189 l_proc_name varchar2(72) := g_proc_name||'get_allowance_start_date';
1190 l_include NUMBER;
1191 l_claim_date VARCHAR2(60);
1192 l_date VARCHAR2(30);
1193 l_start_date DATE;
1194 l_return NUMBER;
1195
1196 BEGIN
1197 debug_enter(l_proc_name);
1198
1199
1200 IF PQP_GB_PSI_FUNCTIONS.g_salary_ended_today = 'Y'
1201 OR PQP_GB_PSI_FUNCTIONS.g_allowance_has_end_dated = 'Y'
1202 THEN
1203 g_allowance_end_dated_today := 'Y';
1204 ELSE
1205 g_allowance_end_dated_today := 'N';
1206 END IF;
1207
1208 debug('PQP_GB_PSI_FUNCTIONS.g_salary_ended_today :' || PQP_GB_PSI_FUNCTIONS.g_salary_ended_today,20);
1209 debug('PQP_GB_PSI_FUNCTIONS.g_allowance_has_end_dated :' || PQP_GB_PSI_FUNCTIONS.g_allowance_has_end_dated,20);
1210 debug('g_allowance_end_dated_today :' || g_allowance_end_dated_today,20);
1211 debug('p_effective_date :' || p_effective_date,20);
1212
1213
1214 IF g_is_spread_bonus_yn = 'N' THEN
1215 IF g_current_layout <> 'CUTOVER'
1216 THEN
1217 --For Bug 7149468:Start
1218 IF g_allowance_end_dated_today = 'Y'
1219 THEN
1220 IF g_leaver_event = 'N'
1221 THEN
1222 p_output := to_char(p_effective_date + 1,'DD/MM/YYYY');
1223 ELSE
1224 --For bug 7229852: Replaced effective date with ATD+1
1225 p_output := to_char(g_act_term_date + 1,'DD/MM/YYYY');
1226 END IF;
1227 ELSE
1228 --For Bug 7149468:End
1229 p_output := to_char(p_effective_date,'DD/MM/YYYY');
1230 --For Bug 7149468:Start
1231 END IF;
1232 --For Bug 7149468:End
1233 ELSE
1234 OPEN csr_get_start_date_cut
1235 (p_element_entry_id => PQP_GB_PSI_FUNCTIONS.g_curr_element_entry_id
1236 );
1237 FETCH csr_get_start_date_cut INTO l_start_date;
1238 IF csr_get_start_date_cut%NOTFOUND
1239 THEN
1240 p_output := NULL;
1241 debug('csr_get_start_date_cut NOTFOUND');
1242 ELSE
1243 p_output := to_char(l_start_date,'DD/MM/YYYY');
1244 END IF;
1245 CLOSE csr_get_start_date_cut;
1246 END IF;
1247 ELSE
1248 debug('g_claim_date :' || g_claim_date);
1249 p_output := to_char( (fnd_date.canonical_to_date(g_claim_date) + (7 - to_char(fnd_date.canonical_to_date(g_claim_date), 'D')) - 6),'DD/MM/YYYY');
1250 debug('p_output :' || p_output,99);
1251
1252 END IF;
1253
1254 debug_exit(l_proc_name);
1255 return 0;
1256 EXCEPTION
1257 WHEN others THEN
1258 IF SQLCODE <> hr_utility.hr_error_number
1259 THEN
1260 debug_others (l_proc_name, 10);
1261 IF g_debug
1262 THEN
1263 DEBUG ( 'Leaving: '
1264 || l_proc_name, -999);
1265 END IF;
1266 fnd_message.raise_error;
1267 ELSE
1268 RAISE;
1269 END IF;
1270 END get_allowance_start_date;
1271
1272
1273 -- ----------------------------------------------------------------------------
1274 -- |---------------------< get_allowance_end_date >----------------------|
1275 -- Description:
1276 -- ----------------------------------------------------------------------------
1277 FUNCTION get_allowance_end_date
1278 (p_effective_date IN DATE
1279 ,p_output OUT NOCOPY VARCHAR2
1280 ) RETURN number
1281 IS
1282 l_proc_name varchar2(72) := g_proc_name||'get_allowance_end_date';
1283 l_claim_date VARCHAR2(60);
1284 l_date VARCHAR2(30);
1285 l_return NUMBER;
1286 l_include NUMBER;
1287
1288 BEGIN
1289 debug_enter(l_proc_name);
1290
1291 IF g_allowance_end_dated_today = 'Y'
1292 THEN
1293 --For bug 7829676: Commented following section to make
1294 --the end date field blank
1295 /*
1296 --For Bug 7149468: Start
1297 IF g_leaver_event = 'N'
1298 THEN
1299 p_output := to_char(p_effective_date + 1,'DD/MM/YYYY');
1300 ELSE
1301 --For Bug 7149468: End
1302
1303 --For bug 7229852: Replaced effective date with ATD+1
1304 p_output := to_char(g_act_term_date + 1,'DD/MM/YYYY');
1305
1306 --For Bug 7149468:Start
1307 END IF;
1308 --For Bug 7149468:End
1309 ELSE
1310 */
1311 p_output := ' ';
1312 END IF;
1313
1314
1315 IF g_is_spread_bonus_yn = 'Y' THEN
1316 debug('g_claim_date :' || g_claim_date);
1317 p_output := to_char( fnd_date.canonical_to_date(g_claim_date) + (7 - to_char(fnd_date.canonical_to_date(g_claim_date), 'D')),'DD/MM/YYYY');
1318 debug('p_output :' || p_output,99);
1319 END IF;
1320
1321 debug_exit(l_proc_name);
1322 return 0;
1323 EXCEPTION
1324 WHEN others THEN
1325 IF SQLCODE <> hr_utility.hr_error_number
1326 THEN
1327 debug_others (l_proc_name, 10);
1328 IF g_debug
1329 THEN
1330 DEBUG ( 'Leaving: '
1331 || l_proc_name, -999);
1332 END IF;
1333 fnd_message.raise_error;
1334 ELSE
1335 RAISE;
1336 END IF;
1337 END get_allowance_end_date;
1338
1339 -- ----------------------------------------------------------------------------
1340 -- |------------------------< allowance_history_main >-------------------------|
1341 -- ----------------------------------------------------------------------------
1342
1343 FUNCTION allowance_history_main
1344 (p_business_group_id IN NUMBER -- context
1345 ,p_effective_date IN DATE -- context
1346 ,p_assignment_id IN NUMBER -- context
1347 ,p_rule_parameter IN VARCHAR2 -- parameter
1348 ,p_output OUT NOCOPY VARCHAR2
1349 )
1350 RETURN number IS
1351 --
1352
1353 l_proc_name VARCHAR2(61):=
1354 g_proc_name||'allowance_history_main';
1355 l_value NUMBER;
1356 l_effective_date DATE;
1357 l_return VARCHAR2(1) := 'Y';
1358 l_output_value NUMBER;
1359 --
1360 BEGIN
1361
1362 debug_enter(l_proc_name);
1363
1364 -- switch on the trace
1365
1366 debug('Entering allowance_history_main ...',0);
1367 debug('p_business_group_id'||p_business_group_id,1);
1368 debug('p_effective_date'||p_effective_date,1);
1369 debug('p_assignment_id'|| p_assignment_id,1);
1370 debug('p_rule_parameter'||p_rule_parameter,1);
1371
1372
1373 -- select the function call based on the parameter being passed to the rule
1374 IF p_rule_parameter = 'AllowanceStartDate' THEN
1375 -- setting some globals which need to be set for the 1st data element
1376 -- and to be used by later data elements
1377
1378 IF PQP_GB_PSI_FUNCTIONS.g_elements_of_info_type.exists(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id) THEN
1379 g_is_spread_bonus_yn
1380 := PQP_GB_PSI_FUNCTIONS.g_elements_of_info_type(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id).eei_information6;
1381 END IF;
1382
1383 IF g_is_spread_bonus_yn IS NULL
1384 THEN
1385 g_is_spread_bonus_yn := 'N';
1386 END IF;
1387
1388 IF g_is_spread_bonus_yn = 'Y' THEN
1389 OPEN csr_get_entry_value
1390 (c_effective_date => p_effective_date
1391 ,c_element_entry_id => PQP_GB_PSI_FUNCTIONS.g_curr_element_entry_id
1392 ,c_input_value => 'CLAIM DATE' -- DEFAULT 'PAY VALUE'
1393 );
1394 FETCH csr_get_entry_value INTO g_claim_date;
1395 IF csr_get_entry_value%NOTFOUND
1396 OR g_claim_date IS NULL
1397 THEN
1398 l_return := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1399 (p_error_number => 94532
1400 ,p_error_text => 'BEN_94532_NO_ENTRY_VALUE'
1401 ,p_token1 =>
1402 pqp_gb_psi_functions.g_elements_of_info_type(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id).element_name
1403 || '(Spread Bonus)'
1404 ,p_token2 => 'CLAIM DATE'
1405 ,p_token3 => to_char(p_effective_date,'DD/MM/YYYY')
1406 );
1407
1408 g_claim_date := NULL;
1409
1410 END IF;
1411 CLOSE csr_get_entry_value;
1412 END IF;
1413 --
1414 l_value := get_allowance_start_date
1415 (p_effective_date => p_effective_date
1416 ,p_output => p_output
1417 );
1418 ELSIF p_rule_parameter = 'AllowanceCode' THEN
1419 l_value := get_allowance_code
1420 (
1421 p_output => p_output
1422 );
1423 -- check for presence of this allowance code on this assignment
1424
1425 l_value := chk_dup_allow_types
1426 (p_assignment_id => p_assignment_id
1427 ,p_effective_date => p_effective_date
1428 );
1429
1430 ELSIF p_rule_parameter = 'NotionalAllowanceRate' THEN
1431 l_value := get_notional_allowance_rate
1432 (p_business_group_id => p_business_group_id
1433 ,p_effective_date => p_effective_date
1434 ,p_assignment_id => p_assignment_id
1435 ,p_output => p_output -- OUT
1436 );
1437 --
1438 l_output_value := fnd_number.canonical_to_number(p_output);
1439
1440 -- !!! IMP - new error message
1441 IF p_output IS NULL THEN
1442 -- raise error that the bonus amount is null and value will not be reported.
1443 debug('ERROR: No Allowance Amount');
1444 l_value := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1445 (p_error_number => 94566
1446 ,p_error_text => 'BEN_94566_NO_ALLOWANCE_AMOUNT'
1447 ,p_token1 =>
1448 PQP_GB_PSI_FUNCTIONS.g_elements_of_info_type(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id).element_name
1449 ,p_token2 => to_char(p_effective_date,'dd/mm/yyyy')
1450 );
1451
1452 -- bugfix 5055150
1453 -- null value, hence setting to zero
1454 l_output_value := 0;
1455
1456 ELSIF NOT ( l_output_value >= -999999.99 AND l_output_value <= 9999999.99 ) THEN
1457 -- raise error that the bonus amount is out of range
1458 debug('ERROR: Allowance Amount out of range: '||p_output,20);
1459 l_value := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1460 (p_error_number => 94568
1461 ,p_error_text => 'BEN_94568_INV_ALLOWANCE_AMOUNT'
1462 ,p_token1 =>
1463 PQP_GB_PSI_FUNCTIONS.g_elements_of_info_type(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id).element_name
1464 ,p_token2 => to_char(p_effective_date,'dd/mm/yyyy')
1465 ,p_token3 => p_output
1466 );
1467 END IF;
1468
1469 IF l_output_value < 0 THEN
1470 p_output := rtrim(ltrim(to_char(l_output_value,'S099999D99')));
1471 ELSE
1472 p_output := rtrim(ltrim(to_char(l_output_value,'0999999D99')));
1473 END IF;
1474 --
1475 ELSIF p_rule_parameter = 'AllowanceIndustrialFlag' THEN
1476 l_value := get_allowance_ind_flag
1477 (
1478 p_output => p_output
1479 );
1480 ELSIF p_rule_parameter = 'AllowanceEndDate' THEN
1481 l_value := get_allowance_end_date
1482 (p_effective_date => p_effective_date
1483 ,p_output => p_output
1484 );
1485
1486 ELSIF p_rule_parameter = 'ActualAllowancePay' THEN
1487 l_value := get_allowance_actual_pay
1488 (p_business_group_id => p_business_group_id
1489 ,p_assignment_id => p_assignment_id
1490 ,p_notional_pay => g_notional_rate
1491 ,p_effective_date => p_effective_date
1492 ,p_output => p_output
1493 );
1494
1495 l_output_value := fnd_number.canonical_to_number(p_output);
1496
1497 IF l_output_value < 0 THEN
1498 p_output := rtrim(ltrim(to_char(l_output_value,'S09999999D99')));
1499 ELSE
1500 p_output := rtrim(ltrim(to_char(l_output_value,'099999999D99')));
1501 END IF;
1502
1503 ELSIF p_rule_parameter = 'AllowanceEEId' THEN
1504 p_output := PQP_GB_PSI_FUNCTIONS.g_curr_element_entry_id;
1505
1506 ELSE
1507 p_output := '';
1508 END IF;
1509
1510
1511 debug_exit(l_proc_name);
1512 RETURN 0;
1513
1514
1515 EXCEPTION
1516 WHEN others THEN
1517 IF SQLCODE <> hr_utility.hr_error_number
1518 THEN
1519 debug_others (l_proc_name, 10);
1520 IF g_debug
1521 THEN
1522 DEBUG ( 'Leaving: '
1523 || l_proc_name, -999);
1524 END IF;
1525 fnd_message.raise_error;
1526 ELSE
1527 RAISE;
1528 END IF;
1529
1530 END allowance_history_main;
1531
1532
1533 -- ----------------------------------------------------------------------------
1534 -- |------------------------< allowance_post_processing >---------------------|
1535 -- ----------------------------------------------------------------------------
1536
1537 FUNCTION allowance_post_processing RETURN VARCHAR2
1538 IS
1539
1540 l_proc_name VARCHAR2(61):=
1541 g_proc_name||'allowance_post_processing';
1542
1543 BEGIN -- basic_data_post_proc_rule
1544
1545 debug_enter(l_proc_name);
1546
1547 PQP_GB_PSI_FUNCTIONS.common_post_process(g_business_group_id);
1548
1549 debug_exit(l_proc_name);
1550 RETURN 'Y';
1551
1552 EXCEPTION
1553 WHEN others THEN
1554 IF SQLCODE <> hr_utility.hr_error_number
1555 THEN
1556 debug_others (l_proc_name, 10);
1557 IF g_debug
1558 THEN
1559 DEBUG ( 'Leaving: '
1560 || l_proc_name, -999);
1561 END IF;
1562 fnd_message.raise_error;
1563 ELSE
1564 RAISE;
1565 END IF;
1566
1567 END allowance_post_processing; -- allowance_post_proc_rule
1568
1569 END PQP_GB_PSI_ALLOWANCE_HISTORY;