[Home] [Help]
Skip to content
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.6 2008/08/05 14:06:01 ubhat 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 -- ----------------------------------------------------------------------------
468 -- |---------------------< all_periodic_ext_criteria >---------------------|
469 -- ----------------------------------------------------------------------------
470 FUNCTION all_periodic_ext_criteria
471 (
472 p_business_group_id IN NUMBER
473 ,p_assignment_id IN NUMBER
474 ,p_effective_date IN DATE
475 )RETURN VARCHAR2
476 IS
477 l_include VARCHAR2(1) := 'Y';
478 l_proc_name VARCHAR2(80) := g_proc_name ||'all_periodic_ext_criteria';
479 l_error NUMBER;
480 l_curr_evt_index NUMBER;
481 l_return VARCHAR2(1) := 'Y';
482 -- For Bug 6082338
483 l_dated_table_id pay_dated_tables.dated_table_id%TYPE;
484 l_chg_table_name VARCHAR2(61);
485 l_chg_column_name pay_event_updates.column_name%TYPE;
486 l_update_type pay_datetracked_events.update_type%TYPE;
487
488 --For bug 7158117: Added new cursor
489 Cursor csr_get_ele_end_date (c_element_entry_id number)
490 IS
491 Select max(effective_end_date)
492 From PAY_ELEMENT_ENTRIES_F
493 Where element_entry_id = c_element_entry_id;
494
495 l_surrogate_key NUMBER;
496 l_ele_end_date DATE;
497 --For bug 7158117: End
498
499 --For bug 7229852: Added new cursor
500 CURSOR csr_get_atd
501 IS
502 Select actual_termination_date
503 From per_all_assignments_f asg, per_periods_of_service per
504 Where asg.assignment_id = p_assignment_id
505 And per.period_of_service_id = asg.period_of_service_id
506 AND p_effective_date between asg.effective_start_date and asg.effective_end_date;
507
508 l_eve_effective_date DATE;
509 l_actual_termination_date DATE;
510 --For bug 7229852: End
511
512 BEGIN --all_periodic_ext_criteria
513
514 debug_enter(l_proc_name);
515
516 debug('Inputs are: ',10);
517 debug('p_business_group_id: '||p_business_group_id,10);
518 debug('p_assignment_id: '||p_assignment_id,10);
519 debug('p_effective_date: '||to_char(p_effective_date,'dd/mm/yyyy'),10);
520
521 g_current_layout := 'PERIODIC';
522 g_current_run := 'PERIODIC';
523 g_effective_date := p_effective_date;
524
525 --
526 IF g_business_group_id IS NULL
527 OR p_business_group_id <> nvl(g_business_group_id,0) THEN
528
529 -- clear cache
530 clear_cache;
531
532 -- for trace switching ON/OFF
533 g_debug := PQP_GB_PSI_FUNCTIONS.check_debug(p_business_group_id);
534 -- setting shared globals
535 -- 1) paypoint
536 -- 2) cutover date
537 -- 3) extract def id
538 PQP_GB_PSI_FUNCTIONS.set_shared_globals
539 (p_business_group_id => p_business_group_id
540 ,p_paypoint => g_paypoint -- OUT
541 ,p_cutover_date => g_cutover_date -- OUT
542 ,p_ext_dfn_id => g_ext_dfn_id -- OUT
543 );
544
545 -- setting extract specific globals
546 set_allowance_history_globals
547 (p_business_group_id => p_business_group_id
548 ,p_assignment_id => p_assignment_id
549 ,p_effective_date => p_effective_date
550 );
551
552 g_business_group_id := p_business_group_id;
553 g_legislation_code := 'GB';
554
555 debug('now raise setup exceptions ...',15);
556 -- raise setup errors and warnings
557 PQP_GB_PSI_FUNCTIONS.raise_extract_exceptions(p_extract_type => 'S');
558
559 END IF; --IF g_business_group_id IS NULL
560
561 g_current_run := 'PERIODIC';
562 g_current_layout := 'PERIODIC';
563
564 debug('g_current_run :'||g_current_run);
565
566 debug('calling the basic criteria for this person assignment');
567 -- calling the basic criteria for this person assignment
568 l_return :=
569 PQP_GB_PSI_FUNCTIONS.chk_penserver_basic_criteria
570 (p_business_group_id => p_business_group_id
571 ,p_effective_date => p_effective_date
572 ,p_assignment_id => p_assignment_id
573 ,p_person_dtl => g_person_dtl
574 ,p_assignment_dtl => g_assignment_dtl
575 );
576
577 debug ('p_assignment_id:'||p_assignment_id);
578 debug('l_return: '||l_return);
579
580
581 IF l_return <> 'N'
582 THEN
583 debug('Calling the common include event proc');
584 -- set the global events table
585 g_pay_proc_evt_tab := ben_ext_person.g_pay_proc_evt_tab;
586 l_curr_evt_index := ben_ext_person.g_chg_pay_evt_index;
587
588 debug('----------');
589 debug('Record :'||l_curr_evt_index);
590 debug('----------');
591 debug('dated_table_id :'||g_pay_proc_evt_tab(l_curr_evt_index).dated_table_id ,20);
592 debug('datetracked_event :'||g_pay_proc_evt_tab(l_curr_evt_index).datetracked_event,20);
593 debug('surrogate_key :'||g_pay_proc_evt_tab(l_curr_evt_index).surrogate_key ,20);
594 debug('column_name :'||g_pay_proc_evt_tab(l_curr_evt_index).column_name ,20);
595 debug('update_type :'||g_pay_proc_evt_tab(l_curr_evt_index).update_type ,20);
596 debug('effective_date :'||to_char(g_pay_proc_evt_tab(l_curr_evt_index).effective_date,'DD/MM/YYYY'),20);
597 debug('actual_date :'||to_char(g_pay_proc_evt_tab(l_curr_evt_index).actual_date,'DD/MM/YYYY'),20);
598 debug('old_value :'||g_pay_proc_evt_tab(l_curr_evt_index).old_value ,20);
599 debug('new_value :'||g_pay_proc_evt_tab(l_curr_evt_index).new_value ,20);
600 debug('change_values :'||g_pay_proc_evt_tab(l_curr_evt_index).change_values ,20);
601 debug('proration_type :'||g_pay_proc_evt_tab(l_curr_evt_index).proration_type ,20);
602 debug('change_mode :'||g_pay_proc_evt_tab(l_curr_evt_index).change_mode ,20);
603 -- For Bug 6082338
604 l_dated_table_id := g_pay_proc_evt_tab(l_curr_evt_index).dated_table_id;
605 l_chg_column_name := g_pay_proc_evt_tab(l_curr_evt_index).column_name;
606 l_update_type := g_pay_proc_evt_tab(l_curr_evt_index).update_type;
607 l_chg_table_name := pqp_gb_psi_functions.get_dated_table_name(l_dated_table_id);
608
609 IF (l_chg_table_name = 'PER_ASSIGNMENT_BUDGET_VALUES_F'
610 AND
611 l_chg_column_name = 'EFFECTIVE_END_DATE'
612 AND
613 l_update_type = 'C') THEN
614 l_return := 'N';
615 debug('Returning : '||l_return,22);
616 debug_exit(l_proc_name);
617 return l_return;
618 END IF;
619
620 --For bug 7158117: Added condn to supress end date record
621 IF (l_chg_table_name = 'PAY_ELEMENT_ENTRIES_F'
622 AND l_chg_column_name = 'EFFECTIVE_END_DATE'
623 AND l_update_type = 'E')
624 THEN
625 l_surrogate_key := g_pay_proc_evt_tab(l_curr_evt_index).surrogate_key;
626 debug('l_surrogate_key :'||l_surrogate_key,23);
627
628 --For bug 7229852: Start
629 l_eve_effective_date := g_pay_proc_evt_tab(l_curr_evt_index).effective_date;
630 debug('l_eve_effective_date :'||l_eve_effective_date,23);
631 --For bug 7229852: End
632
633 OPEN csr_get_ele_end_date(l_surrogate_key);
634 FETCH csr_get_ele_end_date INTO l_ele_end_date;
635 CLOSE csr_get_ele_end_date;
636
637 debug('l_ele_end_date :'||l_ele_end_date,23);
638 --debug('hr_api.g_eot :'||hr_api.g_eot,23);
639
640 --For bug 7229852: Changed logic
641 OPEN csr_get_atd;
642 FETCH csr_get_atd INTO l_actual_termination_date;
643 CLOSE csr_get_atd;
644
645 debug('l_actual_termination_date :'||l_actual_termination_date,23);
646
647 IF l_ele_end_date <> l_eve_effective_date
648 OR
649 (l_actual_termination_date IS NOT NULL
650 AND
651 l_actual_termination_date < l_eve_effective_date)
652 THEN
653 l_return := 'N';
654 debug('Returning : '||l_return,23);
655 debug_exit(l_proc_name);
656 return l_return;
657 END IF;
658 END IF;
659 --For bug 7158117: End
660
661 --For Bug 7149468: Start
662 g_leaver_event := 'N';
663
664 --For Bug 7229852: Start
665 g_act_term_date := NULL;
666 --For Bug 7229852: End
667
668 IF (l_chg_table_name = 'PER_ALL_ASSIGNMENTS_F'
669 AND l_chg_column_name = 'ASSIGNMENT_STATUS_TYPE_ID'
670 AND l_update_type in ('U','C') --For bug 7229852: Added Correction event
671 )
672 THEN
673 g_leaver_event := 'Y';
674
675 --For Bug 7229852: Start
676 OPEN csr_get_atd;
677 FETCH csr_get_atd INTO g_act_term_date;
678 CLOSE csr_get_atd;
679
680 debug('g_act_term_date :'||g_act_term_date,23);
681 --For Bug 7229852: End
682
683 END IF;
684
685 debug('g_leaver_event : '||g_leaver_event ,22);
686 --For Bug 7149468: End
687
688
689 IF is_curr_evt_processed()
690 THEN
691 l_return := 'N';
692 debug('Returning : '||l_return,20);
693 debug_exit(l_proc_name);
694 return l_return;
695 END IF;
696
697 l_return := pqp_gb_psi_functions.include_event
698 (p_actual_date => g_pay_proc_evt_tab(l_curr_evt_index).actual_date
699 ,p_effective_date => g_pay_proc_evt_tab(l_curr_evt_index).effective_date
700 );
701
702 pqp_gb_psi_functions.process_retro_event();
703
704 debug ('p_assignment_id:'||p_assignment_id);
705 debug('include_event returned: '||l_return);
706
707 END IF;
708
709
710 -- IF l_return <> 'N' THEN
711 pqp_gb_psi_functions.g_effective_date := p_effective_date;
712
713 -- set assignment globals
714 IF g_assignment_id IS NULL
715 OR p_assignment_id <> nvl(g_assignment_id,0) THEN
716
717 set_assignment_globals
718 (
719 p_assignment_id => p_assignment_id
720 ,p_effective_date => p_effective_date
721 );
722 END IF;
723 -- END IF;
724
725 debug('l_return: '||l_return);
726
727 debug_exit(l_proc_name);
728 RETURN l_return;
729
730 EXCEPTION
731 WHEN others THEN
732 IF SQLCODE <> hr_utility.hr_error_number
733 THEN
734 debug_others (l_proc_name, 10);
735 IF g_debug
736 THEN
737 DEBUG ( 'Leaving: '
738 || l_proc_name, -999);
739 END IF;
740 fnd_message.raise_error;
741 ELSE
742 RAISE;
743 END IF;
744 END all_periodic_ext_criteria;
745 ---
746
747 -- ----------------------------------------------------------------------------
748 -- |---------------------------< get_allowance_code >--------------------------|
749 -- Description:
750 -- ----------------------------------------------------------------------------
751 FUNCTION get_allowance_code
752 (p_output OUT NOCOPY VARCHAR2
753 )RETURN NUMBER
754 IS
755 l_proc_name varchar2(72) := g_proc_name||'.get_allowance_code';
756 l_return NUMBER;
757 BEGIN
758 debug_enter(l_proc_name);
759
760 IF PQP_GB_PSI_FUNCTIONS.g_elements_of_info_type.exists(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id) THEN
761 g_allowance_code
762 := PQP_GB_PSI_FUNCTIONS.g_elements_of_info_type(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id).eei_information2;
763 ELSE
764 g_allowance_code := ' ';
765 END IF;
766
767 IF NOT pqp_gb_psi_functions.is_alphanumeric(g_allowance_code) THEN
768 debug('ERROR: the allowance code is non-alphanumeric',20);
769 l_return := PQP_GB_PSI_FUNCTIONS.raise_extract_error
770 (p_error_number => 94557
771 ,p_error_text => 'BEN_94557_INVALID_CODE'
772 ,p_token1 => 'Allowance'
773 ,p_token2 =>
774 PQP_GB_PSI_FUNCTIONS.g_elements_of_info_type(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id).element_name
775 ,p_token3 => g_allowance_code
776 );
777 END IF;
778
779
780 p_output := g_allowance_code;
781
782 debug_exit(l_proc_name);
783 return 0;
784 EXCEPTION
785 WHEN others THEN
786 IF SQLCODE <> hr_utility.hr_error_number
787 THEN
788 debug_others (l_proc_name, 10);
789 IF g_debug
790 THEN
791 DEBUG ( 'Leaving: '
792 || l_proc_name, -999);
793 END IF;
794 fnd_message.raise_error;
795 ELSE
796 RAISE;
797 END IF;
798 END get_allowance_code;
799
800
801
802 -- ----------------------------------------------------------------------------
803 -- |---------------------------< chk_dup_allow_types >--------------------------|
804 -- Description:
805 -- ----------------------------------------------------------------------------
806 FUNCTION chk_dup_allow_types
807 (p_assignment_id IN NUMBER
808 ,p_effective_date IN DATE
809 ) RETURN NUMBER
810 IS
811
812 cursor csr_check_dup_allow_types
813 is
814 select 1
815 from pay_element_entries_f pee, pay_element_type_extra_info petei
816 where pee.assignment_id = p_assignment_id
817 and pee.element_type_id = petei.element_type_id
818 and p_effective_date between pee.effective_start_date and pee.effective_end_date
819 and pee.element_entry_id <> PQP_GB_PSI_FUNCTIONS.g_curr_element_entry_id
820 and petei.information_type = 'PQP_GB_PENSERV_ALLOWANCE_INFO'
821 and petei.eei_information2 = g_allowance_code
822 and rownum=1;
823
824 l_proc_name varchar2(72) := g_proc_name||'.chk_dup_allow_types';
825 l_return NUMBER;
826 l_result NUMBER;
827
828 BEGIN
829 debug_enter(l_proc_name);
830
831 OPEN csr_check_dup_allow_types;
832 FETCH csr_check_dup_allow_types into l_result;
833 IF csr_check_dup_allow_types%NOTFOUND
834 THEN l_result := 0;
835 END IF;
836 CLOSE csr_check_dup_allow_types;
837
838 IF l_result = 1
839 THEN
840 debug('WARNING: Duplicate Allowance Type on same date');
841 l_return := PQP_GB_PSI_FUNCTIONS.raise_extract_warning
842 (p_error_number => 94595
843 ,p_error_text => 'BEN_94595_DUP_ALLOW_TYPE'
844 ,p_token1 => to_char(p_effective_date,'dd/mm/yyyy')
845 );
846 END IF;
847
848 debug_exit(l_proc_name);
849 return 0;
850 EXCEPTION
851 WHEN others THEN
852 IF SQLCODE <> hr_utility.hr_error_number
853 THEN
854 debug_others (l_proc_name, 10);
855 IF g_debug
856 THEN
857 DEBUG ( 'Leaving: '
858 || l_proc_name, -999);
859 END IF;
860 fnd_message.raise_error;
861 ELSE
862 RAISE;
863 END IF;
864 END chk_dup_allow_types;
865
866
867 -- ----------------------------------------------------------------------------
868 -- |---------------------------< get_allowance_ind_flag >--------------------------|
869 -- Description:
870 -- ----------------------------------------------------------------------------
871 FUNCTION get_allowance_ind_flag
872 (p_output OUT NOCOPY VARCHAR2
873 )RETURN NUMBER
874 IS
875 l_proc_name varchar2(72) := g_proc_name||'get_allowance_ind_flag';
876 BEGIN
877 debug_enter(l_proc_name);
878
879 IF PQP_GB_PSI_FUNCTIONS.g_elements_of_info_type.exists(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id) THEN
880 p_output
881 := PQP_GB_PSI_FUNCTIONS.g_elements_of_info_type(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id).eei_information5;
882 END IF;
883
884 IF p_output IS NULL
885 THEN
886 p_output := 'N';
887 END IF;
888
889 debug_exit(l_proc_name);
890 return 0;
891 EXCEPTION
892 WHEN others THEN
893 IF SQLCODE <> hr_utility.hr_error_number
894 THEN
895 debug_others (l_proc_name, 10);
896 IF g_debug
897 THEN
898 DEBUG ( 'Leaving: '
899 || l_proc_name, -999);
900 END IF;
901 fnd_message.raise_error;
902 ELSE
903 RAISE;
904 END IF;
905 END get_allowance_ind_flag;
906
907
908
909 -- ----------------------------------------------------------------------------
910 -- |---------------------< get_notional_allowance_rate >----------------------|
911 -- Description:
912 -- ----------------------------------------------------------------------------
913 FUNCTION get_notional_allowance_rate
914 (p_business_group_id IN NUMBER
915 ,p_effective_date IN DATE
916 ,p_assignment_id IN NUMBER
917 ,p_output OUT NOCOPY VARCHAR2
918 ) RETURN number
919 IS
920 l_proc_name varchar2(72) := g_proc_name||'get_notional_allowance_rate';
921 l_include NUMBER;
922 l_custom_function VARCHAR2(100) := 'get_user_notional_pay';
923 BEGIN
924 debug_enter(l_proc_name);
925
926 debug('PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id : '||PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id,10);
927
928 IF g_is_spread_bonus_yn = 'N' THEN
929 IF g_allowance_end_dated_today <> 'Y'
930 THEN
931 l_include := PQP_GB_PSI_FUNCTIONS.get_notional_pay
932 (p_assignment_id => p_assignment_id -- IN NUMBER
933 ,p_business_group_id => p_business_group_id -- IN NUMBER
934 ,p_effective_date => p_effective_date -- IN DATE
935 ,p_name =>
936 PQP_GB_PSI_FUNCTIONS.g_elements_of_info_type(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id).element_name
937 -- IN VARCHAR2
938 ,p_rt_element => 'E'
939 ,p_rate => p_output -- OUT
940 ,p_custom_function => g_user_rate_function -- IN VARCHAR2 DEFAULT NULL
941 ,p_allowance_code => g_allowance_code -- IN VARCHAR2 DEFAULT NULL
942 ,p_allowance_pet_id => PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id -- IN NUMBER DEFAULT NULL
943 );
944
945 ELSE
946 p_output := '0';
947 END IF;
948 ELSE
949 p_output := pqp_gb_psi_functions.get_element_payment
950 (p_assignment_id => p_assignment_id
951 ,p_element_entry_id => PQP_GB_PSI_FUNCTIONS.g_curr_element_entry_id
952 ,p_element_type_id => PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id
953 ,p_effective_date => p_effective_date
954 );
955
956 END IF;
957
958 g_notional_rate := p_output;
959
960
961 debug_exit(l_proc_name);
962 return 0;
963 EXCEPTION
964 WHEN others THEN
965 IF SQLCODE <> hr_utility.hr_error_number
966 THEN
967 debug_others (l_proc_name, 10);
968 IF g_debug
969 THEN
970 DEBUG ( 'Leaving: '
971 || l_proc_name, -999);
972 END IF;
973 fnd_message.raise_error;
974 ELSE
975 RAISE;
976 END IF;
977 END get_notional_allowance_rate;
978
979
980
981 -- ----------------------------------------------------------------------------
982 -- |---------------------< get_allowance_actual_pay >----------------------|
983 -- Description:
984 -- ----------------------------------------------------------------------------
985 FUNCTION get_allowance_actual_pay
986 (p_business_group_id IN NUMBER
987 ,p_assignment_id IN NUMBER
988 ,p_notional_pay IN NUMBER
989 ,p_effective_date IN DATE
990 ,p_output OUT NOCOPY VARCHAR2
991 ) RETURN number
992 IS
993 l_proc_name varchar2(72) := g_proc_name||'get_allowance_actual_pay';
994 l_include NUMBER;
995 BEGIN
996 debug_enter(l_proc_name);
997
998 IF g_is_spread_bonus_yn = 'N' THEN
999 IF g_allowance_end_dated_today <> 'Y'
1000 THEN
1001 l_include := PQP_GB_PSI_FUNCTIONS.get_actual_pay
1002 (
1003 p_assignment_id => p_assignment_id -- IN NUMBER
1004 ,p_notional_pay => g_notional_rate -- IN NUMBER
1005 ,p_effective_date => p_effective_date -- IN DATE
1006 ,p_output => p_output -- OUT NOCOPY VARCHAR2
1007 );
1008 ELSE
1009 p_output := '0';
1010 END IF;
1011 ELSE
1012 p_output := g_notional_rate;
1013 g_allowance_actual_pay := g_notional_rate;
1014 END IF;
1015
1016 debug_exit(l_proc_name);
1017 return 0;
1018 EXCEPTION
1019 WHEN others THEN
1020 IF SQLCODE <> hr_utility.hr_error_number
1021 THEN
1022 debug_others (l_proc_name, 10);
1023 IF g_debug
1024 THEN
1025 DEBUG ( 'Leaving: '
1026 || l_proc_name, -999);
1027 END IF;
1028 fnd_message.raise_error;
1029 ELSE
1030 RAISE;
1031 END IF;
1032 END get_allowance_actual_pay;
1033
1034
1035 -- ----------------------------------------------------------------------------
1036 -- |---------------------< get_allowance_start_date >----------------------|
1037 -- Description:
1038 -- ----------------------------------------------------------------------------
1039 FUNCTION get_allowance_start_date
1040 (p_effective_date IN DATE
1041 ,p_output OUT NOCOPY VARCHAR2
1042 ) RETURN number
1043 IS
1044 l_proc_name varchar2(72) := g_proc_name||'get_allowance_start_date';
1045 l_include NUMBER;
1046 l_claim_date VARCHAR2(60);
1047 l_date VARCHAR2(30);
1048 l_start_date DATE;
1049 l_return NUMBER;
1050
1051 BEGIN
1052 debug_enter(l_proc_name);
1053
1054
1055 IF PQP_GB_PSI_FUNCTIONS.g_salary_ended_today = 'Y'
1056 OR PQP_GB_PSI_FUNCTIONS.g_allowance_has_end_dated = 'Y'
1057 THEN
1058 g_allowance_end_dated_today := 'Y';
1059 ELSE
1060 g_allowance_end_dated_today := 'N';
1061 END IF;
1062
1063 debug('PQP_GB_PSI_FUNCTIONS.g_salary_ended_today :' || PQP_GB_PSI_FUNCTIONS.g_salary_ended_today,20);
1064 debug('PQP_GB_PSI_FUNCTIONS.g_allowance_has_end_dated :' || PQP_GB_PSI_FUNCTIONS.g_allowance_has_end_dated,20);
1065 debug('g_allowance_end_dated_today :' || g_allowance_end_dated_today,20);
1066 debug('p_effective_date :' || p_effective_date,20);
1067
1068
1069 IF g_is_spread_bonus_yn = 'N' THEN
1070 IF g_current_layout <> 'CUTOVER'
1071 THEN
1072 --For Bug 7149468:Start
1073 IF g_allowance_end_dated_today = 'Y'
1074 THEN
1075 IF g_leaver_event = 'N'
1076 THEN
1077 p_output := to_char(p_effective_date + 1,'DD/MM/YYYY');
1078 ELSE
1079 --For bug 7229852: Replaced effective date with ATD+1
1080 p_output := to_char(g_act_term_date + 1,'DD/MM/YYYY');
1081 END IF;
1082 ELSE
1083 --For Bug 7149468:End
1084 p_output := to_char(p_effective_date,'DD/MM/YYYY');
1085 --For Bug 7149468:Start
1086 END IF;
1087 --For Bug 7149468:End
1088 ELSE
1089 OPEN csr_get_start_date_cut
1090 (p_element_entry_id => PQP_GB_PSI_FUNCTIONS.g_curr_element_entry_id
1091 );
1092 FETCH csr_get_start_date_cut INTO l_start_date;
1093 IF csr_get_start_date_cut%NOTFOUND
1094 THEN
1095 p_output := NULL;
1096 debug('csr_get_start_date_cut NOTFOUND');
1097 ELSE
1098 p_output := to_char(l_start_date,'DD/MM/YYYY');
1099 END IF;
1100 CLOSE csr_get_start_date_cut;
1101 END IF;
1102 ELSE
1103 debug('g_claim_date :' || g_claim_date);
1104 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');
1105 debug('p_output :' || p_output,99);
1106
1107 END IF;
1108
1109 debug_exit(l_proc_name);
1110 return 0;
1111 EXCEPTION
1112 WHEN others THEN
1113 IF SQLCODE <> hr_utility.hr_error_number
1114 THEN
1115 debug_others (l_proc_name, 10);
1116 IF g_debug
1117 THEN
1118 DEBUG ( 'Leaving: '
1119 || l_proc_name, -999);
1120 END IF;
1121 fnd_message.raise_error;
1122 ELSE
1123 RAISE;
1124 END IF;
1125 END get_allowance_start_date;
1126
1127
1128 -- ----------------------------------------------------------------------------
1129 -- |---------------------< get_allowance_end_date >----------------------|
1130 -- Description:
1131 -- ----------------------------------------------------------------------------
1132 FUNCTION get_allowance_end_date
1133 (p_effective_date IN DATE
1134 ,p_output OUT NOCOPY VARCHAR2
1135 ) RETURN number
1136 IS
1137 l_proc_name varchar2(72) := g_proc_name||'get_allowance_end_date';
1138 l_claim_date VARCHAR2(60);
1139 l_date VARCHAR2(30);
1140 l_return NUMBER;
1141 l_include NUMBER;
1142
1143 BEGIN
1144 debug_enter(l_proc_name);
1145
1146 IF g_allowance_end_dated_today = 'Y'
1147 THEN
1148 --For Bug 7149468: Start
1149 IF g_leaver_event = 'N'
1150 THEN
1151 p_output := to_char(p_effective_date + 1,'DD/MM/YYYY');
1152 ELSE
1153 --For Bug 7149468: End
1154
1155 --For bug 7229852: Replaced effective date with ATD+1
1156 p_output := to_char(g_act_term_date + 1,'DD/MM/YYYY');
1157
1158 --For Bug 7149468:Start
1159 END IF;
1160 --For Bug 7149468:End
1161 ELSE
1162 p_output := ' ';
1163 END IF;
1164
1165
1166 IF g_is_spread_bonus_yn = 'Y' THEN
1167 debug('g_claim_date :' || g_claim_date);
1168 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');
1169 debug('p_output :' || p_output,99);
1170 END IF;
1171
1172 debug_exit(l_proc_name);
1173 return 0;
1174 EXCEPTION
1175 WHEN others THEN
1176 IF SQLCODE <> hr_utility.hr_error_number
1177 THEN
1178 debug_others (l_proc_name, 10);
1179 IF g_debug
1180 THEN
1181 DEBUG ( 'Leaving: '
1182 || l_proc_name, -999);
1183 END IF;
1184 fnd_message.raise_error;
1185 ELSE
1186 RAISE;
1187 END IF;
1188 END get_allowance_end_date;
1189
1190 -- ----------------------------------------------------------------------------
1191 -- |------------------------< allowance_history_main >-------------------------|
1192 -- ----------------------------------------------------------------------------
1193
1194 FUNCTION allowance_history_main
1195 (p_business_group_id IN NUMBER -- context
1196 ,p_effective_date IN DATE -- context
1197 ,p_assignment_id IN NUMBER -- context
1198 ,p_rule_parameter IN VARCHAR2 -- parameter
1199 ,p_output OUT NOCOPY VARCHAR2
1200 )
1201 RETURN number IS
1202 --
1203
1204 l_proc_name VARCHAR2(61):=
1205 g_proc_name||'allowance_history_main';
1206 l_value NUMBER;
1207 l_effective_date DATE;
1208 l_return VARCHAR2(1) := 'Y';
1209 l_output_value NUMBER;
1210 --
1211 BEGIN
1212
1213 debug_enter(l_proc_name);
1214
1215 -- switch on the trace
1216
1217 debug('Entering allowance_history_main ...',0);
1218 debug('p_business_group_id'||p_business_group_id,1);
1219 debug('p_effective_date'||p_effective_date,1);
1220 debug('p_assignment_id'|| p_assignment_id,1);
1221 debug('p_rule_parameter'||p_rule_parameter,1);
1222
1223
1224 -- select the function call based on the parameter being passed to the rule
1225 IF p_rule_parameter = 'AllowanceStartDate' THEN
1226 -- setting some globals which need to be set for the 1st data element
1227 -- and to be used by later data elements
1228
1229 IF PQP_GB_PSI_FUNCTIONS.g_elements_of_info_type.exists(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id) THEN
1230 g_is_spread_bonus_yn
1231 := PQP_GB_PSI_FUNCTIONS.g_elements_of_info_type(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id).eei_information6;
1232 END IF;
1233
1234 IF g_is_spread_bonus_yn IS NULL
1235 THEN
1236 g_is_spread_bonus_yn := 'N';
1237 END IF;
1238
1239 IF g_is_spread_bonus_yn = 'Y' THEN
1240 OPEN csr_get_entry_value
1241 (c_effective_date => p_effective_date
1242 ,c_element_entry_id => PQP_GB_PSI_FUNCTIONS.g_curr_element_entry_id
1243 ,c_input_value => 'CLAIM DATE' -- DEFAULT 'PAY VALUE'
1244 );
1245 FETCH csr_get_entry_value INTO g_claim_date;
1246 IF csr_get_entry_value%NOTFOUND
1247 OR g_claim_date IS NULL
1248 THEN
1249 l_return := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1250 (p_error_number => 94532
1251 ,p_error_text => 'BEN_94532_NO_ENTRY_VALUE'
1252 ,p_token1 =>
1253 pqp_gb_psi_functions.g_elements_of_info_type(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id).element_name
1254 || '(Spread Bonus)'
1255 ,p_token2 => 'CLAIM DATE'
1256 ,p_token3 => to_char(p_effective_date,'DD/MM/YYYY')
1257 );
1258
1259 g_claim_date := NULL;
1260
1261 END IF;
1262 CLOSE csr_get_entry_value;
1263 END IF;
1264 --
1265 l_value := get_allowance_start_date
1266 (p_effective_date => p_effective_date
1267 ,p_output => p_output
1268 );
1269 ELSIF p_rule_parameter = 'AllowanceCode' THEN
1270 l_value := get_allowance_code
1271 (
1272 p_output => p_output
1273 );
1274 -- check for presence of this allowance code on this assignment
1275
1276 l_value := chk_dup_allow_types
1277 (p_assignment_id => p_assignment_id
1278 ,p_effective_date => p_effective_date
1279 );
1280
1281 ELSIF p_rule_parameter = 'NotionalAllowanceRate' THEN
1282 l_value := get_notional_allowance_rate
1283 (p_business_group_id => p_business_group_id
1284 ,p_effective_date => p_effective_date
1285 ,p_assignment_id => p_assignment_id
1286 ,p_output => p_output -- OUT
1287 );
1288 --
1289 l_output_value := fnd_number.canonical_to_number(p_output);
1290
1291 -- !!! IMP - new error message
1292 IF p_output IS NULL THEN
1293 -- raise error that the bonus amount is null and value will not be reported.
1294 debug('ERROR: No Allowance Amount');
1295 l_value := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1296 (p_error_number => 94566
1297 ,p_error_text => 'BEN_94566_NO_ALLOWANCE_AMOUNT'
1298 ,p_token1 =>
1299 PQP_GB_PSI_FUNCTIONS.g_elements_of_info_type(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id).element_name
1300 ,p_token2 => to_char(p_effective_date,'dd/mm/yyyy')
1301 );
1302
1303 -- bugfix 5055150
1304 -- null value, hence setting to zero
1305 l_output_value := 0;
1306
1307 ELSIF NOT ( l_output_value >= -999999.99 AND l_output_value <= 9999999.99 ) THEN
1308 -- raise error that the bonus amount is out of range
1309 debug('ERROR: Allowance Amount out of range: '||p_output,20);
1310 l_value := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1311 (p_error_number => 94568
1312 ,p_error_text => 'BEN_94568_INV_ALLOWANCE_AMOUNT'
1313 ,p_token1 =>
1314 PQP_GB_PSI_FUNCTIONS.g_elements_of_info_type(PQP_GB_PSI_FUNCTIONS.g_curr_element_type_id).element_name
1315 ,p_token2 => to_char(p_effective_date,'dd/mm/yyyy')
1316 ,p_token3 => p_output
1317 );
1318 END IF;
1319
1320 IF l_output_value < 0 THEN
1321 p_output := rtrim(ltrim(to_char(l_output_value,'S099999D99')));
1322 ELSE
1323 p_output := rtrim(ltrim(to_char(l_output_value,'0999999D99')));
1324 END IF;
1325 --
1326 ELSIF p_rule_parameter = 'AllowanceIndustrialFlag' THEN
1327 l_value := get_allowance_ind_flag
1328 (
1329 p_output => p_output
1330 );
1331 ELSIF p_rule_parameter = 'AllowanceEndDate' THEN
1332 l_value := get_allowance_end_date
1333 (p_effective_date => p_effective_date
1334 ,p_output => p_output
1335 );
1336
1337 ELSIF p_rule_parameter = 'ActualAllowancePay' THEN
1338 l_value := get_allowance_actual_pay
1339 (p_business_group_id => p_business_group_id
1340 ,p_assignment_id => p_assignment_id
1341 ,p_notional_pay => g_notional_rate
1342 ,p_effective_date => p_effective_date
1343 ,p_output => p_output
1344 );
1345
1346 l_output_value := fnd_number.canonical_to_number(p_output);
1347
1348 IF l_output_value < 0 THEN
1349 p_output := rtrim(ltrim(to_char(l_output_value,'S09999999D99')));
1350 ELSE
1351 p_output := rtrim(ltrim(to_char(l_output_value,'099999999D99')));
1352 END IF;
1353
1354 ELSIF p_rule_parameter = 'AllowanceEEId' THEN
1355 p_output := PQP_GB_PSI_FUNCTIONS.g_curr_element_entry_id;
1356
1357 ELSE
1358 p_output := '';
1359 END IF;
1360
1361
1362 debug_exit(l_proc_name);
1363 RETURN 0;
1364
1365
1366 EXCEPTION
1367 WHEN others THEN
1368 IF SQLCODE <> hr_utility.hr_error_number
1369 THEN
1370 debug_others (l_proc_name, 10);
1371 IF g_debug
1372 THEN
1373 DEBUG ( 'Leaving: '
1374 || l_proc_name, -999);
1375 END IF;
1376 fnd_message.raise_error;
1377 ELSE
1378 RAISE;
1379 END IF;
1380
1381 END allowance_history_main;
1382
1383
1384 -- ----------------------------------------------------------------------------
1385 -- |------------------------< allowance_post_processing >---------------------|
1386 -- ----------------------------------------------------------------------------
1387
1388 FUNCTION allowance_post_processing RETURN VARCHAR2
1389 IS
1390
1391 l_proc_name VARCHAR2(61):=
1392 g_proc_name||'allowance_post_processing';
1393
1394 BEGIN -- basic_data_post_proc_rule
1395
1396 debug_enter(l_proc_name);
1397
1398 PQP_GB_PSI_FUNCTIONS.common_post_process(g_business_group_id);
1399
1400 debug_exit(l_proc_name);
1401 RETURN 'Y';
1402
1403 EXCEPTION
1404 WHEN others THEN
1405 IF SQLCODE <> hr_utility.hr_error_number
1406 THEN
1407 debug_others (l_proc_name, 10);
1408 IF g_debug
1409 THEN
1410 DEBUG ( 'Leaving: '
1411 || l_proc_name, -999);
1412 END IF;
1413 fnd_message.raise_error;
1414 ELSE
1415 RAISE;
1416 END IF;
1417
1418 END allowance_post_processing; -- allowance_post_proc_rule
1419
1420 END PQP_GB_PSI_ALLOWANCE_HISTORY;