[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_PSI_HRS_HISTORY
Source
1 PACKAGE BODY pqp_gb_psi_hrs_history
2 -- /* $Header: pqpgbpsihrs.pkb 120.0.12000000.2 2007/02/13 13:48:38 mseshadr noship $ */
3 AS
4 -- ----------------------------------------------------------------------------
5 -- |--------------------------------< debug >---------------------------------|
6 -- ----------------------------------------------------------------------------
7
8 PROCEDURE DEBUG(p_trace_message IN VARCHAR2, p_trace_location IN NUMBER)
9 IS
10 --
11 BEGIN
12 --
13
14 pqp_utilities.DEBUG(
15 p_trace_message => p_trace_message
16 ,p_trace_location => p_trace_location
17 );
18 --
19 END DEBUG;
20
21 -- This procedure is used for debug purposes
22 -- debug_enter checks the debug flag and sets the trace on/off
23 --
24 -- ----------------------------------------------------------------------------
25 -- |----------------------------< debug_enter >-------------------------------|
26 -- ----------------------------------------------------------------------------
27
28 PROCEDURE debug_enter(p_proc_name IN VARCHAR2, p_trace_on IN VARCHAR2)
29 IS
30 BEGIN
31 --
32 IF pqp_utilities.g_nested_level = 0
33 THEN
34 hr_utility.trace_on(NULL, 'REQID'); -- Pipe name REQIDnnnnn
35 END IF;
36
37 pqp_utilities.debug_enter(p_proc_name => p_proc_name
38 ,p_trace_on => p_trace_on);
39 --
40 END debug_enter;
41
42 -- This procedure is used for debug purposes
43 --
44 -- ----------------------------------------------------------------------------
45 -- |----------------------------< debug_exit >--------------------------------|
46 -- ----------------------------------------------------------------------------
47
48 PROCEDURE debug_exit(p_proc_name IN VARCHAR2, p_trace_off IN VARCHAR2)
49 IS
50 BEGIN
51 --
52 pqp_utilities.debug_exit(p_proc_name => p_proc_name
53 ,p_trace_off => p_trace_off);
54
55 -- debug enter sets trace ON when g_trace = 'Y' and nested level = 0
56 -- so we must turn it off for the same condition
57 -- Also turn off tracing when the override flag of p_trace_off has been passed as Y
58 IF pqp_utilities.g_nested_level = 0
59 THEN
60 hr_utility.trace_off;
61 END IF; -- (g_nested_level = 0
62
63 --
64 END debug_exit;
65
66 -- This procedure is used for debug purposes
67 --
68 -- ----------------------------------------------------------------------------
69 -- |----------------------------< debug_others >------------------------------|
70 -- ----------------------------------------------------------------------------
71
72 PROCEDURE debug_others(p_proc_name IN VARCHAR2, p_proc_step IN NUMBER)
73 IS
74 BEGIN
75 --
76 pqp_utilities.debug_others(p_proc_name => p_proc_name
77 ,p_proc_step => p_proc_step);
78 --
79 END debug_others;
80
81 -- This procedure is used to clear all cached global variables
82 --
83 -- ----------------------------------------------------------------------------
84 -- |----------------------------< clear_cache >-------------------------------|
85 -- ----------------------------------------------------------------------------
86 PROCEDURE clear_cache
87 IS
88 --
89 l_proc_name VARCHAR2(80) := g_proc_name || 'clear_cache';
90 l_proc_step PLS_INTEGER;
91 --
92 BEGIN
93 --
94 IF g_debug
95 THEN
96 l_proc_step := 10;
97 debug_enter(l_proc_name);
98 END IF;
99
100 -- Clear all global variables first
101 g_business_group_id := NULL;
102 g_effective_date := NULL;
103 g_extract_type := NULL;
104 g_paypoint := NULL;
105 g_cutover_date := NULL;
106 g_ext_dfn_id := NULL;
107 g_active_asg_sts_id := NULL;
108 g_terminate_asg_sts_id := NULL;
109 g_prev_pay_proc_evnts := NULL;
110 -- Clear all global collections
111 g_tab_event_map_cv.DELETE;
112 g_tab_asg_status.DELETE;
113 g_tab_pen_sch_map_cv.DELETE;
114 g_tab_pen_ele_ids.DELETE;
115 g_tab_prs_dfn_cv.DELETE;
116 g_tab_dated_table.DELETE;
117
118 IF g_debug
119 THEN
120 debug_exit(l_proc_name);
121 END IF;
122 EXCEPTION
123 WHEN OTHERS
124 THEN
125 IF SQLCODE <> hr_utility.hr_error_number
126 THEN
127 debug_others(l_proc_name, l_proc_step);
128
129 IF g_debug
130 THEN
131 DEBUG('Leaving: ' || l_proc_name, -999);
132 END IF;
133
134 fnd_message.raise_error;
135 ELSE
136 RAISE;
137 END IF;
138 END clear_cache;
139
140 -- This procedure returns the assignment status details
141 -- for a given assignment status
142 -- ----------------------------------------------------------------------------
143 -- |----------------------< get_asg_status_type >-----------------------------|
144 -- ----------------------------------------------------------------------------
145 PROCEDURE get_asg_status_type(
146 p_per_system_status IN per_assignment_status_types.per_system_status%TYPE
147 ,p_rec_asg_sts_dtls OUT NOCOPY csr_get_asg_sts_dtls%ROWTYPE
148 )
149 IS
150 --
151
152 l_proc_name VARCHAR2(80) := g_proc_name || 'get_asg_status_type';
153 l_proc_step PLS_INTEGER;
154 l_rec_asg_sts_dtls csr_get_asg_sts_dtls%ROWTYPE;
155 --
156 BEGIN
157 --
158 IF g_debug
159 THEN
160 l_proc_step := 10;
161 debug_enter(l_proc_name);
162 DEBUG('p_per_system_status: ' || p_per_system_status);
163 END IF;
164
165 OPEN csr_get_asg_sts_dtls(p_per_system_status);
166 FETCH csr_get_asg_sts_dtls INTO l_rec_asg_sts_dtls;
167 CLOSE csr_get_asg_sts_dtls;
168 p_rec_asg_sts_dtls := l_rec_asg_sts_dtls;
169
170 IF g_debug
171 THEN
172 DEBUG(
173 'assignment_status_type_id: '
174 || l_rec_asg_sts_dtls.assignment_status_type_id
175 );
176 DEBUG('user_status: ' || l_rec_asg_sts_dtls.user_status);
177 l_proc_step := 20;
178 debug_exit(l_proc_name);
179 END IF;
180 EXCEPTION
181 WHEN OTHERS
182 THEN
183 clear_cache;
184
185 IF SQLCODE <> hr_utility.hr_error_number
186 THEN
187 debug_others(l_proc_name, l_proc_step);
188
189 IF g_debug
190 THEN
191 DEBUG('Leaving: ' || l_proc_name, -999);
192 END IF;
193
194 fnd_message.raise_error;
195 ELSE
196 RAISE;
197 END IF;
198 END get_asg_status_type;
199
200 -- This function returns user status
201 -- for a given assignment status
202 -- ----------------------------------------------------------------------------
203 -- |----------------------< get_asg_status_type >-----------------------------|
204 -- ----------------------------------------------------------------------------
205 FUNCTION get_asg_status_type(p_asg_sts_type_id IN NUMBER)
206 RETURN per_assignment_status_types.user_status%TYPE
207 IS
208 --
209 CURSOR csr_get_asg_sts_dtls
210 IS
211 SELECT user_status
212 FROM per_assignment_status_types
213 WHERE assignment_status_type_id = p_asg_sts_type_id;
214
215 l_proc_name VARCHAR2(80) := g_proc_name || 'get_asg_status_type';
216 l_proc_step PLS_INTEGER;
217 l_user_status per_assignment_status_types.user_status%TYPE;
218 --
219 BEGIN
220 --
221 IF g_debug
222 THEN
223 l_proc_step := 10;
224 debug_enter(l_proc_name);
225 DEBUG('p_asg_sts_type_id: ' || p_asg_sts_type_id);
226 END IF;
227
228 OPEN csr_get_asg_sts_dtls;
229 FETCH csr_get_asg_sts_dtls INTO l_user_status;
230 CLOSE csr_get_asg_sts_dtls;
231
232 IF g_debug
233 THEN
234 DEBUG('user_status: ' || l_user_status);
235 l_proc_step := 20;
236 debug_exit(l_proc_name);
237 END IF;
238
239 RETURN l_user_status;
240 EXCEPTION
241 WHEN OTHERS
242 THEN
243 clear_cache;
244
245 IF SQLCODE <> hr_utility.hr_error_number
246 THEN
247 debug_others(l_proc_name, l_proc_step);
248
249 IF g_debug
250 THEN
251 DEBUG('Leaving: ' || l_proc_name, -999);
252 END IF;
253
254 fnd_message.raise_error;
255 ELSE
256 RAISE;
257 END IF;
258 END get_asg_status_type;
259
260 -- This procedure populates pay dated tables with dated table id
261 -- and table name so that it can be used in the change event
262 -- collection
263 -- ----------------------------------------------------------------------------
264 -- |----------------------------< set_dated_table_collection >----------------|
265 -- ----------------------------------------------------------------------------
266 PROCEDURE set_dated_table_collection
267 IS
268 --
269 l_proc_name VARCHAR2(80)
270 := g_proc_name || 'set_dated_table_collection';
271 l_proc_step PLS_INTEGER;
272 l_rec_dated_table csr_get_dated_table_info%ROWTYPE;
273 l_tab_dated_table t_dated_table;
274 i NUMBER;
275 l_table_name t_varchar2;
276 --
277 BEGIN
278 --
279 IF g_debug
280 THEN
281 l_proc_step := 10;
282 debug_enter(l_proc_name);
283 END IF;
284
285 i := 1;
286 l_table_name(i) := 'PER_ALL_ASSIGNMENTS_F';
287 i := i + 1;
288 l_table_name(i) := 'PAY_ELEMENT_ENTRIES_F';
289 i := i + 1;
290 l_table_name(i) := 'PER_ALL_PEOPLE_F';
291 i := i + 1;
292 l_table_name(i) := 'PER_ASSIGNMENT_BUDGET_VALUES_F';
293 i := i + 1;
294 l_table_name(i) := 'PQP_ASSIGNMENT_ATTRIBUTES_F';
295
296 WHILE i > 0
297 LOOP
298 IF g_debug
299 THEN
300 l_proc_step := 20;
301 DEBUG(l_proc_name, l_proc_step);
302 DEBUG('l_table_name(' || i || '): ' || l_table_name(i));
303 END IF;
304
305 OPEN csr_get_dated_table_info(l_table_name(i));
306 FETCH csr_get_dated_table_info INTO l_rec_dated_table;
307 CLOSE csr_get_dated_table_info;
308 l_tab_dated_table(l_rec_dated_table.dated_table_id) :=
309 l_rec_dated_table;
310
311 IF g_debug
312 THEN
313 l_proc_step := 30;
314 DEBUG(l_proc_name, l_proc_step);
315 DEBUG('dated_table_id: ' || l_rec_dated_table.dated_table_id);
316 DEBUG('Table Name: ' || l_rec_dated_table.table_name);
317 DEBUG('Surrogate Key Col: ' || l_rec_dated_table.surrogate_key_name);
318 END IF;
319
320 i := i - 1;
321 END LOOP;
322
323 -- set the global
324 g_tab_dated_table := l_tab_dated_table;
325
326 IF g_debug
327 THEN
328 l_proc_step := 40;
329 debug_exit(l_proc_name);
330 END IF;
331 EXCEPTION
332 WHEN OTHERS
333 THEN
334 clear_cache;
335
336 IF SQLCODE <> hr_utility.hr_error_number
337 THEN
338 debug_others(l_proc_name, l_proc_step);
339
340 IF g_debug
341 THEN
342 DEBUG('Leaving: ' || l_proc_name, -999);
343 END IF;
344
345 fnd_message.raise_error;
346 ELSE
347 RAISE;
348 END IF;
349 END set_dated_table_collection;
350
351 --
352
353 -- This procedure is used to populate event groups collection
354 -- for service history
355 -- ----------------------------------------------------------------------------
356 -- |----------------------------< set_event_group_collection >----------------|
357 -- ----------------------------------------------------------------------------
358 PROCEDURE set_event_group_collection
359 IS
360 --
361 l_proc_name VARCHAR2(80)
362 := g_proc_name || 'set_event_group_collection';
363 l_proc_step PLS_INTEGER;
364 l_rec_event_group csr_get_event_group_info%ROWTYPE;
365 l_tab_event_group t_event_group;
366 i NUMBER;
367 l_event_group t_varchar2;
368 --
369 BEGIN
370 --
371 IF g_debug
372 THEN
373 l_proc_step := 10;
374 debug_enter(l_proc_name);
375 END IF;
376
377 i := 1;
378 l_event_group(i) := 'PQP_GB_PSI_FTE_VALUE';
379 i := i + 1;
380 l_event_group(i) := 'PQP_GB_PSI_ASSIGNMENT_STATUS';
381 -- i := i + 1;
382 -- l_event_group(i) := 'PQP_GB_PSI_NEW_HIRE';
383 -- i := i + 1;
384 -- l_event_group(i) := 'PQP_GB_PSI_NI_NUMBER';
385 -- i := i + 1;
386 -- l_event_group(i) := 'PQP_GB_PSI_ASSIGNMENT_NUMBER';
387 i := i + 1;
388 l_event_group(i) := 'PQP_GB_PSI_EMP_TERMINATIONS';
389 i := i + 1;
390 l_event_group(i) := 'PQP_GB_PSI_SAL_CONTRACT';
391
392 WHILE i > 0
393 LOOP
394 IF g_debug
395 THEN
396 l_proc_step := 20;
397 DEBUG(l_proc_name, l_proc_step);
398 DEBUG('l_event_group(' || i || '): ' || l_event_group(i));
399 END IF;
400
401 OPEN csr_get_event_group_info(l_event_group(i));
402 FETCH csr_get_event_group_info INTO l_rec_event_group;
403
404 IF csr_get_event_group_info%NOTFOUND
405 THEN
406 -- Raise an error
407 pqp_gb_psi_functions.store_extract_exceptions(
408 p_extract_type => 'PART_TIME_HOURS'
409 ,p_error_number => 94423
410 ,p_error_text => 'BEN_94423_EXT_PSI_NO_EVNT_GRP'
411 ,p_token1 => l_event_group(i)
412 ,p_error_warning_flag => 'E'
413 );
414 END IF;
415
416 CLOSE csr_get_event_group_info;
417 l_tab_event_group(l_rec_event_group.event_group_id) :=
418 l_rec_event_group;
419
420 IF g_debug
421 THEN
422 l_proc_step := 30;
423 DEBUG(l_proc_name, l_proc_step);
424 DEBUG('event_group_id: ' || l_rec_event_group.event_group_id);
425 DEBUG('event_group_name: ' || l_rec_event_group.event_group_name);
426 DEBUG('event_group_type: ' || l_rec_event_group.event_group_type);
427 END IF;
428
429 i := i - 1;
430 END LOOP;
431
432 -- set the global
433 g_tab_event_group := l_tab_event_group;
434
435 IF g_debug
436 THEN
437 l_proc_step := 40;
438 debug_exit(l_proc_name);
439 END IF;
440 EXCEPTION
441 WHEN OTHERS
442 THEN
443 clear_cache;
444
445 IF SQLCODE <> hr_utility.hr_error_number
446 THEN
447 debug_others(l_proc_name, l_proc_step);
448
449 IF g_debug
450 THEN
451 DEBUG('Leaving: ' || l_proc_name, -999);
452 END IF;
453
454 fnd_message.raise_error;
455 ELSE
456 RAISE;
457 END IF;
458 END set_event_group_collection;
459
460 -- This procedure is used to set any globals needed for this extract
461 --
462 -- ----------------------------------------------------------------------------
463 -- |----------------------------< set_hrs_history_globals >-------------------|
464 -- ----------------------------------------------------------------------------
465 PROCEDURE set_hrs_history_globals(
466 p_business_group_id IN NUMBER
467 ,p_effective_date IN DATE
468 )
469 IS
470 --
471 l_proc_name VARCHAR2(80)
472 := g_proc_name || 'set_hrs_history_globals';
473 l_proc_step PLS_INTEGER;
474 l_input_value_name pay_input_values_f.NAME%TYPE;
475 l_input_value_id NUMBER;
476 l_element_type_id NUMBER;
477 l_tab_config_values pqp_utilities.t_config_values;
478 i NUMBER;
479 l_rec_asg_sts_dtls csr_get_asg_sts_dtls%ROWTYPE;
480 --
481 BEGIN
482 --
483 IF g_debug
484 THEN
485 l_proc_step := 10;
486 debug_enter(l_proc_name);
487 DEBUG('p_business_group_id: ' || p_business_group_id);
488 DEBUG('p_effective_date: '
489 || TO_CHAR(p_effective_date, 'DD/MON/YYYY'));
490 END IF;
491
492 -- set global variables
493 g_business_group_id := p_business_group_id;
494
495 g_effective_date := p_effective_date;
496
497 IF g_debug
498 THEN
499 l_proc_step := 20;
500 DEBUG(l_proc_name, l_proc_step);
501 END IF;
502
503 -- Get the assignment status type id for
504 -- active assignments
505 get_asg_status_type(
506 p_per_system_status => 'ACTIVE_ASSIGN'
507 ,p_rec_asg_sts_dtls => l_rec_asg_sts_dtls
508 );
509 g_active_asg_sts_id :=
510 l_rec_asg_sts_dtls.assignment_status_type_id;
511
512 IF g_debug
513 THEN
514 l_proc_step := 30;
515 DEBUG(l_proc_name, l_proc_step);
516 END IF;
517
518 -- Get the assignment status type id for
519 -- terminations
520 get_asg_status_type(
521 p_per_system_status => 'TERM_ASSIGN'
522 ,p_rec_asg_sts_dtls => l_rec_asg_sts_dtls
523 );
524 g_terminate_asg_sts_id :=
525 l_rec_asg_sts_dtls.assignment_status_type_id;
526
527 IF g_debug
528 THEN
529 l_proc_step := 40;
530 DEBUG(l_proc_name, l_proc_step);
531 END IF;
532
533 IF g_extract_type = 'PERIODIC'
534 THEN
535 IF g_debug
536 THEN
537 l_proc_step := 50;
538 DEBUG(l_proc_name, l_proc_step);
539 END IF;
540
541 -- populated dated table ids
542 set_dated_table_collection;
543
544 -- populate event group colleciton
545 IF g_debug
546 THEN
547 l_proc_step := 60;
548 DEBUG(l_proc_name, l_proc_step);
549 END IF;
550
551 set_event_group_collection;
552 END IF; -- End if of extract type = periodic check ...
553
554 IF g_debug
555 THEN
556 l_proc_step := 70;
557 DEBUG('g_business_group_id: ' || g_business_group_id);
558 DEBUG('g_effective_date: '
559 || TO_CHAR(g_effective_date, 'DD/MON/YYYY'));
560 DEBUG('g_extract_type: ' || g_extract_type);
561 DEBUG('g_active_asg_sts_id: ' || g_active_asg_sts_id);
562 DEBUG('g_terminate_asg_sts_id: ' || g_terminate_asg_sts_id);
563 debug_exit(l_proc_name);
564 END IF;
565 EXCEPTION
566 WHEN OTHERS
567 THEN
568 clear_cache;
569
570 IF SQLCODE <> hr_utility.hr_error_number
571 THEN
572 debug_others(l_proc_name, l_proc_step);
573
574 IF g_debug
575 THEN
576 DEBUG('Leaving: ' || l_proc_name, -999);
577 END IF;
578
579 fnd_message.raise_error;
580 ELSE
581 RAISE;
582 END IF;
583 END set_hrs_history_globals;
584
585 -- This procedure gets assignment budget value information
586 -- for a given assignment as at an effective date
587 -- ----------------------------------------------------------------------------
588 -- |---------------------< get_abv_details >----------------------------------|
589 -- ----------------------------------------------------------------------------
590 PROCEDURE get_abv_details (p_assignment_id IN NUMBER
591 ,p_effective_date IN DATE
592 ,p_rec_abv_dtls OUT NOCOPY csr_abv_dtls%ROWTYPE
593 )
594 IS
595 --
596 l_proc_name VARCHAR2(80) := g_proc_name || 'get_abv_details';
597 l_proc_step PLS_INTEGER;
598 l_rec_abv_dtls csr_abv_dtls%ROWTYPE;
599 l_value NUMBER;
600 --
601 BEGIN
602 --
603 IF g_debug
604 THEN
605 --
606 l_proc_step := 10;
607 debug_enter(l_proc_name);
608 debug('p_assignment_id: '||p_assignment_id);
609 debug('p_effective_date: '||TO_CHAR(p_effective_date, 'DD/MON/YYYY'));
610 END IF;
611
612 OPEN csr_abv_dtls (p_assignment_id
613 ,p_effective_date
614 );
615 FETCH csr_abv_dtls INTO l_rec_abv_dtls;
616 IF csr_abv_dtls%NOTFOUND THEN
617 -- Raise an error
618 IF g_debug THEN
619 --
620 l_proc_step := 20;
621 debug(l_proc_name, l_proc_step);
622 debug('Raise abv not found error');
623 END IF;
624 -- Raise data error
625 l_value :=
626 pqp_gb_psi_functions.raise_extract_error(
627 p_error_number => 94479
628 ,p_error_text => 'BEN_94479_EXT_PSI_REQ_FLD_MISS'
629 ,p_token1 => 'FTE'
630 );
631 END IF;
632 CLOSE csr_abv_dtls;
633 p_rec_abv_dtls := l_rec_abv_dtls;
634
635 IF g_debug
636 THEN
637 l_proc_step := 30;
638 debug('l_rec_abv_dtls.assignment_id: '|| l_rec_abv_dtls.assignment_id);
639 debug('l_rec_abv_dtls.effective_start_date: '||TO_CHAR(l_rec_abv_dtls.effective_start_date, 'DD/MON/YYYY'));
640 debug('l_rec_abv_dtls.effective_end_date: '||TO_CHAR(l_rec_abv_dtls.effective_end_date, 'DD/MON/YYYY'));
641 debug('l_rec_abv_dtls.unit: '||l_rec_abv_dtls.unit);
642 debug('l_rec_abv_dtls.value: '||l_rec_abv_dtls.value);
643 debug('l_rec_abv_dtls.assignment_budget_value_id: '||l_rec_abv_dtls.assignment_budget_value_id);
644 debug_exit(l_proc_name);
645 END IF;
646 EXCEPTION
647 WHEN OTHERS
648 THEN
649 clear_cache;
650
651 IF SQLCODE <> hr_utility.hr_error_number
652 THEN
653 debug_others(l_proc_name, l_proc_step);
654
655 IF g_debug
656 THEN
657 DEBUG('Leaving: ' || l_proc_name, -999);
658 END IF;
659
660 fnd_message.raise_error;
661 ELSE
662 RAISE;
663 END IF;
664 END get_abv_details;
665
666 -- This procedure gets assignment budget value information
667 -- for a given assignment as at an effective date
668 -- ----------------------------------------------------------------------------
669 -- |---------------------< get_abv_details >----------------------------------|
670 -- ----------------------------------------------------------------------------
671 PROCEDURE get_abv_details (p_abv_id IN NUMBER
672 ,p_effective_date IN DATE
673 ,p_rec_abv_dtls OUT NOCOPY csr_abv_dtls%ROWTYPE
674 )
675 IS
676 --
677 -- Cursor to get abv details
678 CURSOR csr_get_abv_dtls
679 IS
680 SELECT assignment_budget_value_id
681 ,assignment_id
682 ,effective_start_date
683 ,effective_end_date
684 ,unit
685 ,value
686 FROM per_assignment_budget_values_f
687 WHERE assignment_budget_value_id = p_abv_id
688 AND p_effective_date BETWEEN effective_start_date
689 AND effective_end_date;
690
691 l_proc_name VARCHAR2(80) := g_proc_name || 'get_abv_details';
692 l_proc_step PLS_INTEGER;
693 l_rec_abv_dtls csr_abv_dtls%ROWTYPE;
694 l_value NUMBER;
695 --
696 BEGIN
697 --
698 IF g_debug
699 THEN
700 --
701 l_proc_step := 10;
702 debug_enter(l_proc_name);
703 debug('p_abv_id: '||p_abv_id);
704 debug('p_effective_date: '||TO_CHAR(p_effective_date, 'DD/MON/YYYY'));
705 END IF;
706
707 OPEN csr_get_abv_dtls;
708 FETCH csr_get_abv_dtls INTO l_rec_abv_dtls;
709 CLOSE csr_get_abv_dtls;
710 p_rec_abv_dtls := l_rec_abv_dtls;
711
712 IF g_debug
713 THEN
714 l_proc_step := 20;
715 debug('l_rec_abv_dtls.assignment_id: '|| l_rec_abv_dtls.assignment_id);
716 debug('l_rec_abv_dtls.effective_start_date: '||TO_CHAR(l_rec_abv_dtls.effective_start_date, 'DD/MON/YYYY'));
717 debug('l_rec_abv_dtls.effective_end_date: '||TO_CHAR(l_rec_abv_dtls.effective_end_date, 'DD/MON/YYYY'));
718 debug('l_rec_abv_dtls.unit: '||l_rec_abv_dtls.unit);
719 debug('l_rec_abv_dtls.value: '||l_rec_abv_dtls.value);
720 debug('l_rec_abv_dtls.assignment_budget_value_id: '||l_rec_abv_dtls.assignment_budget_value_id);
721 debug_exit(l_proc_name);
722 END IF;
723 EXCEPTION
724 WHEN OTHERS
725 THEN
726 clear_cache;
727
728 IF SQLCODE <> hr_utility.hr_error_number
729 THEN
730 debug_others(l_proc_name, l_proc_step);
731
732 IF g_debug
733 THEN
734 DEBUG('Leaving: ' || l_proc_name, -999);
735 END IF;
736
737 fnd_message.raise_error;
738 ELSE
739 RAISE;
740 END IF;
741 END get_abv_details;
742
743 -- This procedure gets full time hours and part time hours
744 -- information for a given assignment
745 -- ----------------------------------------------------------------------------
746 -- |---------------------< get_hrs_data >-------------------------------------|
747 -- ----------------------------------------------------------------------------
748 PROCEDURE get_hrs_data (p_assignment_id IN NUMBER
749 ,p_fte IN NUMBER
750 ,p_ft_hours OUT NOCOPY NUMBER
751 ,p_pt_hours OUT NOCOPY NUMBER
752 ,p_hours_type OUT NOCOPY VARCHAR2
753 )
754 IS
755 --
756 CURSOR csr_get_aat_details
757 IS
758 SELECT assignment_attribute_id
759 ,effective_start_date
760 ,effective_end_date
761 ,contract_type
762 FROM pqp_assignment_attributes_f
763 WHERE assignment_id = p_assignment_id
764 AND g_effective_date BETWEEN effective_start_date
765 AND effective_end_date;
766
767 l_proc_name VARCHAR2(80) := g_proc_name || 'get_hrs_data';
768 l_proc_step PLS_INTEGER;
769 l_ft_hours NUMBER;
770 l_pt_hours NUMBER;
771 l_rec_aat_dtls csr_get_aat_details%ROWTYPE;
772 l_period_divisor NUMBER;
773 l_return NUMBER;
774 l_error_msg VARCHAR2(2000);
775 l_value NUMBER;
776 l_hours_type pay_user_column_instances_f.value%TYPE;
777
778 --
779 BEGIN
780 --
781 IF g_debug
782 THEN
783 l_proc_step := 10;
784 debug_enter(l_proc_name);
785 debug('p_assignment_id: '||p_assignment_id);
786 debug('p_fte: '|| p_fte);
787 END IF;
788
789 -- Get the normal hours and frequency
790 -- for this assignment
791 l_pt_hours := 0;
792
793 IF g_debug THEN
794 debug('l_pt_hours: '||l_pt_hours);
795 debug('g_assignment_dtl.frequency: '||g_assignment_dtl.frequency);
796 END IF;
797
798 -- Get the contract type on assignment attributes
799 OPEN csr_get_aat_details;
800 FETCH csr_get_aat_details INTO l_rec_aat_dtls;
801
802 IF csr_get_aat_details%NOTFOUND THEN
803 -- Raise an error
804 IF g_debug THEN
805 --
806 l_proc_step := 20;
807 debug(l_proc_name, l_proc_step);
808 debug('Raise aat not found error');
809 END IF;
810 -- Raise data error
811 l_value :=
812 pqp_gb_psi_functions.raise_extract_error(
813 p_error_number => 94479
814 ,p_error_text => 'BEN_94479_EXT_PSI_REQ_FLD_MISS'
815 ,p_token1 => 'Assignment Attribute'
816 );
817 ELSIF l_rec_aat_dtls.contract_type IS NULL THEN
818 -- Raise an error
819 IF g_debug THEN
820 --
821 l_proc_step := 30;
822 debug(l_proc_name, l_proc_step);
823 debug('Raise contract type is null error');
824 END IF;
825 -- Raise data error
826 l_value :=
827 pqp_gb_psi_functions.raise_extract_error(
828 p_error_number => 94479
829 ,p_error_text => 'BEN_94479_EXT_PSI_REQ_FLD_MISS'
830 ,p_token1 => 'Contract Type'
831 );
832 ELSE -- contract type exists
833 IF g_debug
834 THEN
835 l_proc_step := 40;
836 debug(l_proc_name, l_proc_step);
837 debug('l_rec_aat_dtls.contract_type: '||l_rec_aat_dtls.contract_type);
838 debug('l_rec_aat_dtls.assignment_attribute_id: '||l_rec_aat_dtls.assignment_attribute_id);
839 debug('l_rec_aat_dtls.effective_start_date: '||TO_CHAR(l_rec_aat_dtls.effective_start_date, 'DD/MON/YYYY'));
840 debug('l_rec_aat_dtls.effective_end_date: '||TO_CHAR(l_rec_aat_dtls.effective_end_date, 'DD/MON/YYYY'));
841 END IF;
842
843 -- Get hours type
844 l_return := pqp_utilities.pqp_gb_get_table_value
845 (p_business_group_id => g_business_group_id
846 ,p_effective_date => g_effective_date
847 ,p_table_name => 'PQP_CONTRACT_TYPES'
848 ,p_column_name => 'Penserver P/T Hours Type'
849 ,p_row_name => l_rec_aat_dtls.contract_type
850 ,p_value => l_hours_type
851 ,p_error_msg => l_error_msg
852 );
853 IF g_debug
854 THEN
855 debug('l_return: '||l_return);
856 debug('l_hours_type: '||l_hours_type);
857 END IF;
858
859 IF l_return = -1 THEN
860 -- Raise error
861 l_value :=
862 pqp_gb_psi_functions.raise_extract_error(
863 p_error_number => 94479
864 ,p_error_text => 'BEN_94479_EXT_PSI_REQ_FLD_MISS'
865 ,p_token1 => 'Penserver P/T Hours Type'
866 );
867 ELSE
868 IF UPPER(l_hours_type) = 'G' OR
869 UPPER(l_hours_type) = 'GROSS'
870 THEN
871 l_hours_type := 'G';
872 ELSIF UPPER(l_hours_type) = 'N' OR
873 UPPER(l_hours_type) = 'NET'
874 THEN
875 l_hours_type := 'N';
876 ELSIF l_hours_type IS NULL
877 THEN
878 -- Raise error
879 l_value :=
880 pqp_gb_psi_functions.raise_extract_error(
881 p_error_number => 94479
882 ,p_error_text => 'BEN_94479_EXT_PSI_REQ_FLD_MISS'
883 ,p_token1 => 'Penserver P/T Hours Type'
884 );
885 ELSE
886 l_value :=
887 pqp_gb_psi_functions.raise_extract_error(
888 p_error_number => 94636
889 ,p_error_text => 'BEN_94636_EXT_PSI_INV_HRS_TYPE'
890 ,p_token1 => l_hours_type
891 ,p_token2 => l_rec_aat_dtls.contract_type
892 );
893 END IF;
894 END IF; -- End if of l_return = -1 for P/T Hours Type check ...
895 -- Check whether frequency is not weekly
896 IF g_assignment_dtl.frequency <> 'W' THEN
897 IF g_debug
898 THEN
899 l_proc_step := 50;
900 debug('g_assignment_dtl.frequency: '|| g_assignment_dtl.frequency);
901 END IF;
902 -- Get period divisor value
903
904 l_return := pqp_utilities.pqp_gb_get_table_value
905 (p_business_group_id => g_business_group_id
906 ,p_effective_date => g_effective_date
907 ,p_table_name => 'PQP_CONTRACT_TYPES'
908 ,p_column_name => 'Period Divisor'
909 ,p_row_name => l_rec_aat_dtls.contract_type
910 ,p_value => l_period_divisor
911 ,p_error_msg => l_error_msg
912 );
913 IF g_debug
914 THEN
915 debug('l_return: '||l_return);
916 debug('l_period_divisor: '||l_period_divisor);
917 END IF;
918 IF l_return = -1 THEN
919 -- Raise error
920 l_value :=
921 pqp_gb_psi_functions.raise_extract_error(
922 p_error_number => 94479
923 ,p_error_text => 'BEN_94479_EXT_PSI_REQ_FLD_MISS'
924 ,p_token1 => 'Period Divisor'
925 );
926 ELSE
927 l_pt_hours := g_assignment_dtl.normal_hours;
928 l_pt_hours := ROUND((l_pt_hours * l_period_divisor)/52, 2);
929 END IF; -- End if of l_return = -1 check ...
930 ELSE -- frequency is weekly
931
932 l_pt_hours := g_assignment_dtl.normal_hours;
933
934 END IF; -- End if of frequency <> W check ...
935 END IF; -- End if of aat details not found check ...
936 IF g_debug
937 THEN
938 l_proc_step := 60;
939 debug(l_proc_name, l_proc_step);
940 debug('l_pt_hours: '||l_pt_hours);
941 END IF;
942
943 l_ft_hours := ROUND((l_pt_hours/p_fte), 2);
944
945 p_ft_hours := l_ft_hours;
946 p_pt_hours := l_pt_hours;
947 p_hours_type := l_hours_type;
948
949
950 IF g_debug
951 THEN
952 l_proc_step := 70;
953 debug('l_ft_hours: '||l_ft_hours);
954 debug('l_hours_type: '||l_hours_type);
955 debug_exit(l_proc_name);
956 END IF;
957 EXCEPTION
958 WHEN OTHERS
959 THEN
960 clear_cache;
961
962 IF SQLCODE <> hr_utility.hr_error_number
963 THEN
964 debug_others(l_proc_name, l_proc_step);
965
966 IF g_debug
967 THEN
968 DEBUG('Leaving: ' || l_proc_name, -999);
969 END IF;
970
971 fnd_message.raise_error;
972 ELSE
973 RAISE;
974 END IF;
975 END get_hrs_data;
976
977
978 -- This function is used to get part time hours history data
979 -- for an assignment as of a cutover date
980 -- ----------------------------------------------------------------------------
981 -- |---------------------< get_asg_hrs_cutover_data >-------------------------|
982 -- ----------------------------------------------------------------------------
983 FUNCTION get_asg_hrs_cutover_data(p_assignment_id IN NUMBER)
984 RETURN VARCHAR2
985 IS
986 --
987 l_proc_name VARCHAR2(80)
988 := g_proc_name || 'get_asg_hrs_cutover_data';
989 l_proc_step PLS_INTEGER;
990 l_include_flag VARCHAR2(10);
991 l_rec_abv_dtls csr_abv_dtls%ROWTYPE;
992 l_ft_hours NUMBER;
993 l_pt_hours NUMBER;
994 l_hours_type pay_user_column_instances_f.value%TYPE;
995 --
996 BEGIN
997 --
998 IF g_debug
999 THEN
1000 --
1001 l_proc_step := 10;
1002 debug_enter(l_proc_name);
1003 DEBUG('p_assignment_id: ' || p_assignment_id);
1004 END IF;
1005
1006 l_include_flag := 'N';
1007
1008 -- Get the FTE value as at the cutover date whether this person
1009 -- will have to be reported
1010 get_abv_details
1011 (p_assignment_id => p_assignment_id
1012 ,p_effective_date => g_effective_date
1013 ,p_rec_abv_dtls => l_rec_abv_dtls
1014 );
1015
1016
1017 IF l_rec_abv_dtls.value < 1 AND l_rec_abv_dtls.value > 0 AND
1018 l_rec_abv_dtls.value IS NOT NULL
1019 THEN
1020 -- This is a part time employee
1021 -- report this person
1022 IF g_debug
1023 THEN
1024 --
1025 l_proc_step := 20;
1026 debug(l_proc_name, l_proc_step);
1027 END IF;
1028
1029 l_include_flag := 'Y';
1030 g_start_date := l_rec_abv_dtls.effective_start_date;
1031
1032 IF l_rec_abv_dtls.effective_end_date <> hr_api.g_eot AND
1033 l_rec_abv_dtls.effective_end_date <= ben_ext_person.g_effective_date
1034 THEN
1035 g_end_date := l_rec_abv_dtls.effective_end_date;
1036 END IF; -- end date not eot check ...
1037
1038 get_hrs_data (p_assignment_id => p_assignment_id
1039 ,p_fte => l_rec_abv_dtls.value
1040 ,p_ft_hours => l_ft_hours
1041 ,p_pt_hours => l_pt_hours
1042 ,p_hours_type => l_hours_type
1043 );
1044 g_ft_hours := l_ft_hours;
1045 g_pt_hours := l_pt_hours;
1046 g_hours_type := l_hours_type;
1047 END IF; -- End if of fte value < 1 check ...
1048
1049 IF g_debug
1050 THEN
1051 l_proc_step := 30;
1052 DEBUG('l_include_flag: '||l_include_flag);
1053 DEBUG('g_ft_hours: ' || g_ft_hours);
1054 DEBUG('g_pt_hours: ' || g_pt_hours);
1055 DEBUG('g_hours_type: ' || g_hours_type);
1056 DEBUG('g_start_date: '
1057 || TO_CHAR(g_start_date, 'DD/MON/YYYY'));
1058 DEBUG('g_end_date: '
1059 || TO_CHAR(g_end_date, 'DD/MON/YYYY'));
1060 debug_exit(l_proc_name);
1061 END IF;
1062 RETURN l_include_flag;
1063 EXCEPTION
1064 WHEN OTHERS
1065 THEN
1066 clear_cache;
1067
1068 IF SQLCODE <> hr_utility.hr_error_number
1069 THEN
1070 debug_others(l_proc_name, l_proc_step);
1071
1072 IF g_debug
1073 THEN
1074 DEBUG('Leaving: ' || l_proc_name, -999);
1075 END IF;
1076
1077 fnd_message.raise_error;
1078 ELSE
1079 RAISE;
1080 END IF;
1081 END get_asg_hrs_cutover_data;
1082
1083 -- This function is used to evaluate fte event
1084 -- for an assignment for periodic changes
1085 -- ----------------------------------------------------------------------------
1086 -- |---------------------< eval_fte_event >-----------------------------------|
1087 -- ----------------------------------------------------------------------------
1088 FUNCTION eval_fte_event(p_assignment_id IN NUMBER
1089 ,p_table_name IN VARCHAR2
1090 ,p_surrogate_key IN NUMBER
1091 )
1092 RETURN VARCHAR2
1093 IS
1094 --
1095 l_proc_name VARCHAR2(80) := g_proc_name || 'eval_fte_event';
1096 l_proc_step PLS_INTEGER;
1097 l_rec_abv_dtls csr_abv_dtls%ROWTYPE;
1098 l_ft_hours NUMBER;
1099 l_pt_hours NUMBER;
1100 l_include_flag VARCHAR2(10);
1101 l_hours_type pay_user_column_instances_f.value%TYPE;
1102 --
1103 BEGIN
1104 --
1105 IF g_debug
1106 THEN
1107 l_proc_step := 10;
1108 debug_enter(l_proc_name);
1109 DEBUG('p_assignment_id: '||p_assignment_id);
1110 DEBUG('p_table_name: '||p_table_name);
1111 DEBUG('p_surrogate_key: '||p_surrogate_key);
1112 END IF;
1113
1114 l_include_flag := 'N';
1115
1116 IF p_table_name = 'PER_ASSIGNMENT_BUDGET_VALUES_F'
1117 THEN
1118 IF g_debug
1119 THEN
1120 l_proc_step := 20;
1121 DEBUG(l_proc_name, l_proc_step);
1122 END IF;
1123 get_abv_details
1124 (p_abv_id => p_surrogate_key
1125 ,p_effective_date => g_effective_date
1126 ,p_rec_abv_dtls => l_rec_abv_dtls
1127 );
1128 IF l_rec_abv_dtls.unit = 'FTE'
1129 THEN
1130 IF l_rec_abv_dtls.value < 1 AND l_rec_abv_dtls.value > 0 AND
1131 l_rec_abv_dtls.value IS NOT NULL
1132 THEN
1133 -- This is a part time employee
1134 -- report this person
1135 IF g_debug
1136 THEN
1137 --
1138 l_proc_step := 30;
1139 debug(l_proc_name, l_proc_step);
1140 END IF;
1141
1142 l_include_flag := 'Y';
1143 g_start_date := l_rec_abv_dtls.effective_start_date;
1144
1145 IF l_rec_abv_dtls.effective_end_date <> hr_api.g_eot AND
1146 l_rec_abv_dtls.effective_end_date <= ben_ext_person.g_effective_date
1147 THEN
1148 g_end_date := l_rec_abv_dtls.effective_end_date;
1149 END IF; -- end date not eot check ...
1150
1151 get_hrs_data (p_assignment_id => p_assignment_id
1152 ,p_fte => l_rec_abv_dtls.value
1153 ,p_ft_hours => l_ft_hours
1154 ,p_pt_hours => l_pt_hours
1155 ,p_hours_type => l_hours_type
1156 );
1157 g_ft_hours := l_ft_hours;
1158 g_pt_hours := l_pt_hours;
1159 g_hours_type := l_hours_type;
1160 END IF; -- End if of fte value < 1 check ...
1161 END IF; -- End if of unit is fte check ...
1162 ELSIF p_table_name = 'PER_ALL_ASSIGNMENTS_F'
1163 THEN
1164 IF g_debug
1165 THEN
1166 l_proc_step := 40;
1167 DEBUG(l_proc_name, l_proc_step);
1168 END IF;
1169 l_include_flag := get_asg_hrs_cutover_data
1170 (p_assignment_id => p_assignment_id);
1171 END IF; -- End if of table name = assignment budget values check ...
1172
1173 IF g_debug
1174 THEN
1175 l_proc_step := 50;
1176 DEBUG('l_include_flag: '||l_include_flag);
1177 DEBUG('g_ft_hours: ' || g_ft_hours);
1178 DEBUG('g_pt_hours: ' || g_pt_hours);
1179 DEBUG('g_hours_type: ' || g_hours_type);
1180 DEBUG('g_start_date: '
1181 || TO_CHAR(g_start_date, 'DD/MON/YYYY'));
1182 DEBUG('g_end_date: '
1183 || TO_CHAR(g_end_date, 'DD/MON/YYYY'));
1184 debug_exit(l_proc_name);
1185 END IF;
1186 RETURN l_include_flag;
1187 EXCEPTION
1188 WHEN OTHERS
1189 THEN
1190 clear_cache;
1191
1192 IF SQLCODE <> hr_utility.hr_error_number
1193 THEN
1194 debug_others(l_proc_name, l_proc_step);
1195
1196 IF g_debug
1197 THEN
1198 DEBUG('Leaving: ' || l_proc_name, -999);
1199 END IF;
1200
1201 fnd_message.raise_error;
1202 ELSE
1203 RAISE;
1204 END IF;
1205 END eval_fte_event;
1206
1207 -- This function is used to get part time hours history data
1208 -- for an assignment for periodic changes
1209 -- ----------------------------------------------------------------------------
1210 -- |---------------------< get_asg_hrs_periodic_data >------------------------|
1211 -- ----------------------------------------------------------------------------
1212 FUNCTION get_asg_hrs_periodic_data(p_assignment_id IN NUMBER)
1213 RETURN VARCHAR2
1214 IS
1215 --
1216 l_proc_name VARCHAR2(80)
1217 := g_proc_name || 'get_asg_hrs_periodic_data';
1218 l_proc_step PLS_INTEGER;
1219 l_include_flag VARCHAR2(10);
1220 l_rec_abv_dtls csr_abv_dtls%ROWTYPE;
1221 l_ft_hours NUMBER;
1222 l_pt_hours NUMBER;
1223 l_tab_pay_proc_evnts ben_ext_person.t_detailed_output_table;
1224 l_event_group_id NUMBER;
1225 l_event_group_name pay_event_groups.event_group_name%TYPE;
1226 l_dated_table_id NUMBER;
1227 l_table_name pay_dated_tables.table_name%TYPE;
1228 l_curr_status_type_id NUMBER;
1229 l_prev_status_type_id NUMBER;
1230 l_return VARCHAR2(10);
1231 l_leaver_date DATE;
1232 l_surrogate_key NUMBER;
1233 l_process_flag VARCHAR2(10);
1234 l_assignment_id NUMBER;
1235 --
1236 BEGIN
1237 --
1238 IF g_debug
1239 THEN
1240 --
1241 l_proc_step := 10;
1242 debug_enter(l_proc_name);
1243 DEBUG('p_assignment_id: ' || p_assignment_id);
1244 END IF;
1245
1246 l_include_flag := 'N';
1247
1248 l_tab_pay_proc_evnts := ben_ext_person.g_pay_proc_evt_tab;
1249
1250 IF l_tab_pay_proc_evnts.COUNT > 0
1251 THEN
1252 IF g_debug
1253 THEN
1254 l_proc_step := 20;
1255 DEBUG(l_proc_name, l_proc_step);
1256 END IF;
1257
1258 IF g_debug
1259 THEN
1260 l_proc_step := 30;
1261 DEBUG(l_proc_name, l_proc_step);
1262 DEBUG('g_event_counter :' || g_event_counter);
1263 DEBUG(
1264 'dated_table_id :'
1265 || l_tab_pay_proc_evnts(g_event_counter).dated_table_id
1266 );
1267 DEBUG(
1268 'datetracked_event :'
1269 || l_tab_pay_proc_evnts(g_event_counter).datetracked_event
1270 );
1271 DEBUG(
1272 'update type: '
1273 || l_tab_pay_proc_evnts(g_event_counter).update_type
1274 );
1275 DEBUG(
1276 'surrogate_key :'
1277 || l_tab_pay_proc_evnts(g_event_counter).surrogate_key
1278 );
1279 DEBUG(
1280 'column_name :'
1281 || l_tab_pay_proc_evnts(g_event_counter).column_name
1282 );
1283 DEBUG(
1284 'effective_date :'
1285 || TO_CHAR(
1286 l_tab_pay_proc_evnts(g_event_counter).effective_date
1287 ,'DD/MON/YYYY'
1288 )
1289 );
1290 DEBUG(
1291 'old_value :'
1292 || l_tab_pay_proc_evnts(g_event_counter).old_value
1293 );
1294 DEBUG(
1295 'new_value :'
1296 || l_tab_pay_proc_evnts(g_event_counter).new_value
1297 );
1298 DEBUG(
1299 'change_values :'
1300 || l_tab_pay_proc_evnts(g_event_counter).change_values
1301 );
1302 DEBUG(
1303 'proration_type :'
1304 || l_tab_pay_proc_evnts(g_event_counter).proration_type
1305 );
1306 DEBUG(
1307 'change_mode :'
1308 || l_tab_pay_proc_evnts(g_event_counter).change_mode
1309 );
1310 DEBUG(
1311 'event_group_id :'
1312 || l_tab_pay_proc_evnts(g_event_counter).event_group_id
1313 );
1314 DEBUG(
1315 'next_evt_start_date: '
1316 || TO_CHAR(
1317 l_tab_pay_proc_evnts(g_event_counter).next_evt_start_date
1318 ,'DD/MON/YYYY'
1319 )
1320 );
1321 DEBUG(
1322 'actual_date: '
1323 || TO_CHAR(
1324 l_tab_pay_proc_evnts(g_event_counter).actual_date
1325 ,'DD/MON/YYYY'
1326 )
1327 );
1328 DEBUG('g_prev_pay_proc_evnts.dated_table_id: '
1329 || g_prev_pay_proc_evnts.dated_table_id
1330 );
1331 END IF;
1332
1333 IF g_prev_pay_proc_evnts.dated_table_id IS NOT NULL THEN
1334
1335 --
1336 IF l_tab_pay_proc_evnts(g_event_counter).dated_table_id <>
1337 g_prev_pay_proc_evnts.dated_table_id OR
1338 l_tab_pay_proc_evnts(g_event_counter).datetracked_event <>
1339 g_prev_pay_proc_evnts.datetracked_event OR
1340 l_tab_pay_proc_evnts(g_event_counter).update_type <>
1341 g_prev_pay_proc_evnts.update_type OR
1342 l_tab_pay_proc_evnts(g_event_counter).surrogate_key <>
1343 g_prev_pay_proc_evnts.surrogate_key OR
1344 l_tab_pay_proc_evnts(g_event_counter).column_name <>
1345 g_prev_pay_proc_evnts.column_name OR
1346 l_tab_pay_proc_evnts(g_event_counter).effective_date <>
1347 g_prev_pay_proc_evnts.effective_date OR
1348 l_tab_pay_proc_evnts(g_event_counter).old_value <>
1349 g_prev_pay_proc_evnts.old_value OR
1350 l_tab_pay_proc_evnts(g_event_counter).new_value <>
1351 g_prev_pay_proc_evnts.new_value OR
1352 l_tab_pay_proc_evnts(g_event_counter).change_values <>
1353 g_prev_pay_proc_evnts.change_values OR
1354 l_tab_pay_proc_evnts(g_event_counter).proration_type <>
1355 g_prev_pay_proc_evnts.proration_type OR
1356 l_tab_pay_proc_evnts(g_event_counter).event_group_id <>
1357 g_prev_pay_proc_evnts.event_group_id OR
1358 l_tab_pay_proc_evnts(g_event_counter).actual_date <>
1359 g_prev_pay_proc_evnts.actual_date
1360 THEN
1361
1362 l_process_flag := 'Y';
1363 ELSE
1364 l_process_flag := 'N';
1365 END IF;
1366 ELSE
1367 l_process_flag := 'Y';
1368 END IF; -- End if of dated table id not null check ...
1369
1370 IF g_debug THEN
1371 DEBUG('l_process_flag: ' || l_process_flag);
1372 END IF;
1373
1374 g_prev_pay_proc_evnts := l_tab_pay_proc_evnts(g_event_counter);
1375
1376
1377 g_tab_pay_proc_evnts := l_tab_pay_proc_evnts;
1378 -- Check whether we are interested in this event
1379 l_event_group_id :=
1380 l_tab_pay_proc_evnts(g_event_counter).event_group_id;
1381
1382 IF g_tab_event_group.EXISTS(l_event_group_id) AND l_process_flag = 'Y'
1383 THEN
1384 IF g_debug
1385 THEN
1386 l_proc_step := 40;
1387 DEBUG(l_proc_name, l_proc_step);
1388 END IF;
1389
1390 l_return :=
1391 pqp_gb_psi_functions.include_event(
1392 p_actual_date => l_tab_pay_proc_evnts(g_event_counter).actual_date
1393 ,p_effective_date => l_tab_pay_proc_evnts(g_event_counter).effective_date
1394 );
1395
1396 IF g_debug
1397 THEN
1398 l_proc_step := 50;
1399 DEBUG(l_proc_name, l_proc_step);
1400 DEBUG('l_return: ' || l_return);
1401 END IF;
1402
1403 IF l_return = 'Y'
1404 THEN
1405 -- We are interested in this event
1406 l_dated_table_id :=
1407 l_tab_pay_proc_evnts(g_event_counter).dated_table_id;
1408 l_table_name :=
1409 g_tab_dated_table(l_dated_table_id).table_name;
1410 l_event_group_name :=
1411 g_tab_event_group(l_event_group_id).event_group_name;
1412
1413 IF g_debug
1414 THEN
1415 l_proc_step := 60;
1416 DEBUG(l_proc_name, l_proc_step);
1417 DEBUG('l_event_group_name: ' || l_event_group_name);
1418 DEBUG('l_dated_table_id: ' || l_dated_table_id);
1419 DEBUG('l_table_name: ' || l_table_name);
1420 END IF;
1421
1422 -- PQP_GB_PSI_FTE_VALUE
1423 -- PQP_GB_PSI_ASSIGNMENT_STATUS
1424 -- PQP_GB_PSI_NI_NUMBER
1425 -- PQP_GB_PSI_ASSIGNMENT_NUMBER
1426 -- PQP_GB_PSI_EMP_TERMINATIONS
1427 -- PQP_GB_PSI_SAL_CONTRACT
1428
1429 -- Check whether event group relates to assignment number
1430 -- or NI Number change in which case get the part time hour
1431 -- information as of the event date
1432 -- IF l_event_group_name = 'PQP_GB_PSI_NI_NUMBER' OR
1433 -- l_event_group_name = 'PQP_GB_PSI_ASSIGNMENT_NUMBER'
1434 -- THEN
1435 -- IF g_debug
1436 -- THEN
1437 -- l_proc_step := 70;
1438 -- DEBUG(l_proc_name, l_proc_step);
1439 -- END IF;
1440 -- l_include_flag := get_asg_hrs_cutover_data
1441 -- (p_assignment_id => p_assignment_id);
1442 -- ELSIF l_event_group_name = 'PQP_GB_PSI_NEW_HIRE'
1443 -- THEN
1444 -- -- This is a new hire event (includes rehires)
1445 -- -- Evaluate new joiners
1446 -- -- We are only interested in primary assignments
1447 -- l_assignment_id :=
1448 -- fnd_number.canonical_to_number(l_tab_pay_proc_evnts(g_event_counter).surrogate_key);
1449 --
1450 -- IF g_debug
1451 -- THEN
1452 -- l_proc_step := 80;
1453 -- DEBUG(l_proc_name, l_proc_step);
1454 -- DEBUG('l_assignment_id: ' || l_assignment_id);
1455 -- END IF;
1456 --
1457 -- IF l_assignment_id = p_assignment_id
1458 -- THEN
1459 -- l_include_flag := get_asg_hrs_cutover_data
1460 -- (p_assignment_id => p_assignment_id);
1461 -- END IF; -- End if of l_assignment_id = p_assignment_id check ...
1462
1463 IF l_event_group_name = 'PQP_GB_PSI_ASSIGNMENT_STATUS'
1464 THEN -- Assignment status event group
1465 IF g_debug
1466 THEN
1467 l_proc_step := 90;
1468 DEBUG(l_proc_name, l_proc_step);
1469 END IF;
1470
1471 l_curr_status_type_id :=
1472 fnd_number.canonical_to_number(l_tab_pay_proc_evnts(g_event_counter).new_value);
1473 l_prev_status_type_id :=
1474 fnd_number.canonical_to_number(l_tab_pay_proc_evnts(g_event_counter).old_value);
1475 IF l_curr_status_type_id = g_terminate_asg_sts_id
1476 THEN
1477 IF g_debug
1478 THEN
1479 l_proc_step := 100;
1480 DEBUG(l_proc_name, l_proc_step);
1481 DEBUG('l_curr_status_type_id: '||l_curr_status_type_id);
1482 DEBUG('l_prev_status_type_id: '||l_prev_status_type_id);
1483 END IF;
1484 l_include_flag := get_asg_hrs_cutover_data
1485 (p_assignment_id => p_assignment_id);
1486 END IF;
1487 ELSIF l_event_group_name = 'PQP_GB_PSI_EMP_TERMINATIONS'
1488 THEN -- Terminations
1489 IF g_debug
1490 THEN
1491 l_proc_step := 110;
1492 DEBUG(l_proc_name, l_proc_step);
1493 END IF;
1494
1495 IF pqp_gb_psi_functions.chk_is_employee_a_leaver(
1496 p_assignment_id => p_assignment_id
1497 ,p_effective_date => g_effective_date
1498 ,p_leaver_date => l_leaver_date
1499 ) = 'Y'
1500 THEN
1501 IF g_debug
1502 THEN
1503 l_proc_step := 120;
1504 DEBUG(l_proc_name, l_proc_step);
1505 END IF;
1506 l_include_flag := get_asg_hrs_cutover_data
1507 (p_assignment_id => p_assignment_id);
1508 END IF; -- End if of chk is employee a leaver check ...
1509 ELSIF l_event_group_name = 'PQP_GB_PSI_FTE_VALUE'
1510 THEN
1511 IF g_debug
1512 THEN
1513 l_proc_step := 130;
1514 DEBUG(l_proc_name, l_proc_step);
1515 END IF;
1516 l_surrogate_key :=
1517 fnd_number.canonical_to_number(l_tab_pay_proc_evnts(g_event_counter).surrogate_key);
1518
1519 l_include_flag := eval_fte_event
1520 (p_assignment_id => p_assignment_id
1521 ,p_table_name => l_table_name
1522 ,p_surrogate_key => l_surrogate_key
1523 );
1524 ELSIF l_event_group_name = 'PQP_GB_PSI_SAL_CONTRACT'
1525 THEN
1526 IF g_debug
1527 THEN
1528 l_proc_step := 140;
1529 DEBUG(l_proc_Name, l_proc_step);
1530 END IF;
1531 l_include_flag := get_asg_hrs_cutover_data
1532 (p_assignment_id => p_assignment_id);
1533 END IF; -- Event group name check ...
1534 END IF; -- End if of l_return = 'Y' check ...
1535 END IF; -- event group exists check ...
1536 END IF; -- Event collection count > 0 check ...
1537 IF g_debug
1538 THEN
1539 l_proc_step := 150;
1540 DEBUG('l_include_flag: '||l_include_flag);
1541 DEBUG('g_ft_hours: ' || g_ft_hours);
1542 DEBUG('g_pt_hours: ' || g_pt_hours);
1543 DEBUG('g_hours_type: ' || g_hours_type);
1544 DEBUG('g_start_date: '
1545 || TO_CHAR(g_start_date, 'DD/MON/YYYY'));
1546 DEBUG('g_end_date: '
1547 || TO_CHAR(g_end_date, 'DD/MON/YYYY'));
1548 debug_exit(l_proc_name);
1549 END IF;
1550
1551 RETURN l_include_flag;
1552 EXCEPTION
1553 WHEN OTHERS
1554 THEN
1555 clear_cache;
1556
1557 IF SQLCODE <> hr_utility.hr_error_number
1558 THEN
1559 debug_others(l_proc_name, l_proc_step);
1560
1561 IF g_debug
1562 THEN
1563 DEBUG('Leaving: ' || l_proc_name, -999);
1564 END IF;
1565
1566 fnd_message.raise_error;
1567 ELSE
1568 RAISE;
1569 END IF;
1570 END get_asg_hrs_periodic_data;
1571
1572 -- This function is used to evaluate assignments that
1573 -- qualify for penserver part time hours history cutover interface
1574 -- ----------------------------------------------------------------------------
1575 -- |---------------------< chk_hrs_cutover_criteria -----------------|
1576 -- ----------------------------------------------------------------------------
1577 FUNCTION chk_hrs_cutover_criteria(
1578 p_business_group_id IN NUMBER
1579 ,p_effective_date IN DATE
1580 ,p_assignment_id IN NUMBER
1581 )
1582 RETURN VARCHAR2
1583 IS
1584 --
1585 l_proc_name VARCHAR2(80)
1586 := g_proc_name || 'chk_hrs_cutover_criteria';
1587 l_proc_step PLS_INTEGER;
1588 l_include_flag VARCHAR2(10);
1589 l_debug VARCHAR2(10);
1590 i NUMBER;
1591 --
1592 BEGIN
1593 --
1594 IF g_business_group_id IS NULL
1595 THEN
1596 -- Always clear cache before proceeding to set globals
1597 clear_cache;
1598 g_debug := pqp_gb_psi_functions.check_debug(p_business_group_id);
1599 END IF; -- End if of g_business_group_id is Null check ...
1600
1601 IF g_debug
1602 THEN
1603 l_proc_step := 10;
1604 debug_enter(l_proc_name);
1605 debug('p_business_group_id: '|| p_business_group_id);
1606 debug('p_effective_date: '||TO_CHAR(p_effective_date, 'DD/MON/YYYY'));
1607 debug('p_assignment_id: '||p_assignment_id);
1608 END IF;
1609
1610 l_include_flag := 'N';
1611
1612 IF g_business_group_id IS NULL
1613 THEN
1614
1615 IF g_debug
1616 THEN
1617 DEBUG('g_business_group_id: ' || g_business_group_id);
1618 l_proc_step := 20;
1619 DEBUG(l_proc_name, l_proc_step);
1620 END IF;
1621
1622 -- set shared globals
1623 pqp_gb_psi_functions.set_shared_globals(
1624 p_business_group_id => p_business_group_id
1625 ,p_paypoint => g_paypoint
1626 ,p_cutover_date => g_cutover_date
1627 ,p_ext_dfn_id => g_ext_dfn_id
1628 );
1629
1630 g_extract_type := 'CUTOVER';
1631
1632 IF g_debug
1633 THEN
1634 l_proc_step := 30;
1635 DEBUG(l_proc_name, l_proc_step);
1636 DEBUG('g_paypoint: ' || g_paypoint);
1637 DEBUG('g_cutover_date: '
1638 || TO_CHAR(g_cutover_date, 'DD/MON/YYYY'));
1639 DEBUG('g_ext_dfn_id: ' || g_ext_dfn_id);
1640 END IF;
1641
1642 -- set extract global variables
1643 set_hrs_history_globals(
1644 p_business_group_id => p_business_group_id
1645 ,p_effective_date => p_effective_date
1646 );
1647
1648 IF g_debug
1649 THEN
1650 l_proc_step := 40;
1651 DEBUG(l_proc_name, l_proc_step);
1652 END IF;
1653
1654 -- Raise Extract Exceptions
1655 pqp_gb_psi_functions.raise_extract_exceptions('S');
1656 END IF; -- End if of business group id is null check ...
1657
1658 g_start_date := NULL;
1659 g_end_date := NULL;
1660 g_pt_hours := NULL;
1661 g_ft_hours := NULL;
1662 g_hours_type := NULL;
1663
1664 -- Check penserver basic criteria
1665 IF g_debug
1666 THEN
1667 l_proc_step := 50;
1668 DEBUG(l_proc_name, l_proc_step);
1669 END IF;
1670
1671 l_include_flag :=
1672 pqp_gb_psi_functions.chk_penserver_basic_criteria(
1673 p_business_group_id => g_business_group_id
1674 ,p_effective_date => g_effective_date
1675 ,p_assignment_id => p_assignment_id
1676 ,p_person_dtl => g_person_dtl
1677 ,p_assignment_dtl => g_assignment_dtl
1678 );
1679
1680 IF g_debug
1681 THEN
1682 l_proc_step := 60;
1683 DEBUG(l_proc_name, l_proc_step);
1684 DEBUG('l_include_flag: ' || l_include_flag);
1685 DEBUG('g_extract_type: ' || g_extract_type);
1686 END IF;
1687
1688 IF l_include_flag = 'Y'
1689 THEN
1690 IF g_debug
1691 THEN
1692 l_proc_step := 70;
1693 DEBUG(l_proc_name, l_proc_step);
1694 END IF;
1695
1696 l_include_flag := get_asg_hrs_cutover_data(p_assignment_id => p_assignment_id);
1697 END IF; -- End if of l_include_flag = 'Y' check ...
1698
1699 IF g_debug
1700 THEN
1701 l_proc_step := 80;
1702 DEBUG('l_include_flag: ' || l_include_flag);
1703 debug_exit(l_proc_name);
1704 END IF;
1705
1706 RETURN l_include_flag;
1707 EXCEPTION
1708 WHEN OTHERS
1709 THEN
1710 clear_cache;
1711
1712 IF SQLCODE <> hr_utility.hr_error_number
1713 THEN
1714 debug_others(l_proc_name, l_proc_step);
1715
1716 IF g_debug
1717 THEN
1718 DEBUG('Leaving: ' || l_proc_name, -999);
1719 END IF;
1720
1721 fnd_message.raise_error;
1722 ELSE
1723 RAISE;
1724 END IF;
1725 END chk_hrs_cutover_criteria;
1726
1727 -- This function is used to evaluate assignments that
1728 -- qualify for penserver part time hours history periodic interface
1729 -- ----------------------------------------------------------------------------
1730 -- |---------------------< chk_hrs_periodic_criteria -----------------|
1731 -- ----------------------------------------------------------------------------
1732 FUNCTION chk_hrs_periodic_criteria(
1733 p_business_group_id IN NUMBER
1734 ,p_effective_date IN DATE
1735 ,p_assignment_id IN NUMBER
1736 )
1737 RETURN VARCHAR2
1738 IS
1739 --
1740 l_proc_name VARCHAR2(80)
1741 := g_proc_name || 'chk_hrs_periodic_criteria';
1742 l_proc_step PLS_INTEGER;
1743 l_include_flag VARCHAR2(10);
1744 l_debug VARCHAR2(10);
1745 i NUMBER;
1746 --
1747 BEGIN
1748 --
1749 --
1750 IF g_business_group_id IS NULL
1751 THEN
1752 -- Always clear cache before proceeding to set globals
1753 clear_cache;
1754 g_debug := pqp_gb_psi_functions.check_debug(p_business_group_id);
1755 END IF; -- End if of g_business_group_id is Null check ...
1756
1757 IF g_debug
1758 THEN
1759 l_proc_step := 10;
1760 debug_enter(l_proc_name);
1761 debug('p_business_group_id: '|| p_business_group_id);
1762 debug('p_effective_date: '||TO_CHAR(p_effective_date, 'DD/MON/YYYY'));
1763 debug('p_assignment_id: '||p_assignment_id);
1764 END IF;
1765
1766 l_include_flag := 'N';
1767
1768 IF g_business_group_id IS NULL
1769 THEN
1770
1771 IF g_debug
1772 THEN
1773 DEBUG('g_business_group_id: ' || g_business_group_id);
1774 l_proc_step := 20;
1775 DEBUG(l_proc_name, l_proc_step);
1776 END IF;
1777
1778 -- set shared globals
1779 pqp_gb_psi_functions.set_shared_globals(
1780 p_business_group_id => p_business_group_id
1781 ,p_paypoint => g_paypoint
1782 ,p_cutover_date => g_cutover_date
1783 ,p_ext_dfn_id => g_ext_dfn_id
1784 );
1785
1786 g_extract_type := 'PERIODIC';
1787
1788 IF g_debug
1789 THEN
1790 l_proc_step := 30;
1791 DEBUG(l_proc_name, l_proc_step);
1792 DEBUG('g_paypoint: ' || g_paypoint);
1793 DEBUG('g_cutover_date: '
1794 || TO_CHAR(g_cutover_date, 'DD/MON/YYYY'));
1795 DEBUG('g_ext_dfn_id: ' || g_ext_dfn_id);
1796 END IF;
1797
1798 -- set extract global variables
1799 set_hrs_history_globals(
1800 p_business_group_id => p_business_group_id
1801 ,p_effective_date => p_effective_date
1802 );
1803
1804 IF g_debug
1805 THEN
1806 l_proc_step := 40;
1807 DEBUG(l_proc_name, l_proc_step);
1808 END IF;
1809
1810 -- Raise Extract Exceptions
1811 pqp_gb_psi_functions.raise_extract_exceptions('S');
1812 END IF; -- End if of business group id is null check ...
1813
1814 g_start_date := NULL;
1815 g_end_date := NULL;
1816 g_pt_hours := NULL;
1817 g_ft_hours := NULL;
1818 g_hours_type := NULL;
1819 g_effective_date := p_effective_date;
1820 g_event_counter := ben_ext_person.g_chg_pay_evt_index;
1821
1822
1823 -- Check penserver basic criteria
1824 IF g_debug
1825 THEN
1826 l_proc_step := 50;
1827 DEBUG(l_proc_name, l_proc_step);
1828 END IF;
1829
1830 l_include_flag :=
1831 pqp_gb_psi_functions.chk_penserver_basic_criteria(
1832 p_business_group_id => g_business_group_id
1833 ,p_effective_date => g_effective_date
1834 ,p_assignment_id => p_assignment_id
1835 ,p_person_dtl => g_person_dtl
1836 ,p_assignment_dtl => g_assignment_dtl
1837 );
1838
1839 IF g_debug
1840 THEN
1841 l_proc_step := 60;
1842 DEBUG(l_proc_name, l_proc_step);
1843 DEBUG('l_include_flag: ' || l_include_flag);
1844 DEBUG('g_extract_type: ' || g_extract_type);
1845 DEBUG('g_effective_date: '||TO_CHAR(g_effective_date, 'DD/MON/YYYY'));
1846 DEBUG('g_event_counter: '||g_event_counter);
1847 END IF;
1848
1849 IF l_include_flag = 'Y'
1850 THEN
1851 IF g_debug
1852 THEN
1853 l_proc_step := 70;
1854 DEBUG(l_proc_name, l_proc_step);
1855 END IF;
1856
1857 l_include_flag := get_asg_hrs_periodic_data(p_assignment_id => p_assignment_id);
1858
1859 IF l_include_flag = 'Y' THEN
1860 IF g_debug
1861 THEN
1862 l_proc_step := 80;
1863 DEBUG(l_proc_name, l_proc_step);
1864 END IF;
1865 pqp_gb_psi_functions.process_retro_event;
1866 END IF;
1867 END IF; -- End if of l_include_flag = 'Y' check ...
1868
1869 IF g_debug
1870 THEN
1871 l_proc_step := 100;
1872 DEBUG('l_include_flag: ' || l_include_flag);
1873 debug_exit(l_proc_name);
1874 END IF;
1875
1876 RETURN l_include_flag;
1877 EXCEPTION
1878 WHEN OTHERS
1879 THEN
1880 clear_cache;
1881
1882 IF SQLCODE <> hr_utility.hr_error_number
1883 THEN
1884 debug_others(l_proc_name, l_proc_step);
1885
1886 IF g_debug
1887 THEN
1888 DEBUG('Leaving: ' || l_proc_name, -999);
1889 END IF;
1890
1891 fnd_message.raise_error;
1892 ELSE
1893 RAISE;
1894 END IF;
1895 END chk_hrs_periodic_criteria;
1896
1897 -- This function is used to get part time hours history data
1898 -- for an assignment
1899 -- ----------------------------------------------------------------------------
1900 -- |---------------------< get_hrs_history_data >-------------------------|
1901 -- ----------------------------------------------------------------------------
1902 FUNCTION get_hrs_history_data(
1903 p_business_group_id IN NUMBER
1904 ,p_effective_date IN DATE
1905 ,p_assignment_id IN NUMBER
1906 ,p_rule_parameter IN VARCHAR2
1907 )
1908 RETURN VARCHAR2
1909 IS
1910 --
1911 l_proc_name VARCHAR2(80)
1912 := g_proc_name || 'get_hrs_history_data';
1913 l_proc_step PLS_INTEGER;
1914 l_return_value VARCHAR2(150);
1915 l_value NUMBER;
1916 --
1917 BEGIN
1918 --
1919 IF g_debug
1920 THEN
1921 l_proc_step := 10;
1922 debug_enter(l_proc_name);
1923 DEBUG('p_business_group_id: ' || p_business_group_id);
1924 DEBUG('p_effective_date: ' || TO_CHAR(p_effective_date, 'DD/MON/YYYY'));
1925 DEBUG('p_assignment_id: ' || p_assignment_id);
1926 DEBUG('p_rule_parameter: ' || p_rule_parameter);
1927 END IF;
1928
1929 IF p_rule_parameter = 'StartDate' THEN
1930 l_return_value := fnd_date.date_to_canonical(g_start_date);
1931 IF g_start_date IS NULL THEN
1932 IF g_debug
1933 THEN
1934 DEBUG('Raise Data Error: Start Date Missing');
1935 END IF;
1936
1937 -- Raise data error
1938 l_value :=
1939 pqp_gb_psi_functions.raise_extract_error(
1940 p_error_number => 94479
1941 ,p_error_text => 'BEN_94479_EXT_PSI_REQ_FLD_MISS'
1942 ,p_token1 => 'Start Date'
1943 );
1944 END IF;
1945 ELSIF p_rule_parameter = 'EndDate' THEN
1946 l_return_value := fnd_date.date_to_canonical(g_end_date);
1947 ELSIF p_rule_parameter = 'PartTimeHours' THEN
1948 IF g_pt_hours < 0 THEN
1949 l_return_value := TRIM(TO_CHAR(g_pt_hours,'S09D99'));
1950 ELSE
1951 l_return_value := TRIM(TO_CHAR(g_pt_hours,'099D99'));
1952 END IF;
1953 IF g_pt_hours IS NULL
1954 THEN
1955 IF g_debug
1956 THEN
1957 DEBUG('Raise Data Error: Part Time Hours is Missing');
1958 END IF;
1959
1960 -- Raise data error
1961 l_value :=
1962 pqp_gb_psi_functions.raise_extract_error(
1963 p_error_number => 94479
1964 ,p_error_text => 'BEN_94479_EXT_PSI_REQ_FLD_MISS'
1965 ,p_token1 => 'Part Time Hours'
1966 );
1967 END IF;
1968 ELSIF p_rule_parameter = 'FullTimeHours' THEN
1969 IF g_ft_hours < 0 THEN
1970 l_return_value := TRIM(TO_CHAR(g_ft_hours,'S09D99'));
1971 ELSE
1972 l_return_value := TRIM(TO_CHAR(g_ft_hours,'099D99'));
1973 END IF;
1974 IF g_ft_hours IS NULL
1975 THEN
1976 IF g_debug
1977 THEN
1978 DEBUG('Raise Data Error: Full Time Hours is missing');
1979 END IF;
1980
1981 -- Raise data error
1982 l_value :=
1983 pqp_gb_psi_functions.raise_extract_error(
1984 p_error_number => 94479
1985 ,p_error_text => 'BEN_94479_EXT_PSI_REQ_FLD_MISS'
1986 ,p_token1 => 'Full Time Hours'
1987 );
1988 END IF;
1989 ELSIF p_rule_parameter = 'HoursType' THEN
1990 l_return_value := TRIM(RPAD(g_hours_type,1,' '));
1991 END IF; -- End if of rule parameter check ...
1992
1993 IF g_debug
1994 THEN
1995 l_proc_step := 30;
1996 DEBUG('l_return_value: ' || l_return_value);
1997 debug_exit(l_proc_name);
1998 END IF;
1999
2000 RETURN l_return_value;
2001 EXCEPTION
2002 WHEN OTHERS
2003 THEN
2004 clear_cache;
2005
2006 IF SQLCODE <> hr_utility.hr_error_number
2007 THEN
2008 debug_others(l_proc_name, l_proc_step);
2009
2010 IF g_debug
2011 THEN
2012 DEBUG('Leaving: ' || l_proc_name, -999);
2013 END IF;
2014
2015 fnd_message.raise_error;
2016 ELSE
2017 RAISE;
2018 END IF;
2019 END get_hrs_history_data;
2020
2021 -- This function is used for post processing in part time hours history interface
2022 -- ----------------------------------------------------------------------------
2023 -- |---------------------< hrs_history_post_process >---------------------|
2024 -- ----------------------------------------------------------------------------
2025 FUNCTION hrs_history_post_process(p_ext_rslt_id IN NUMBER)
2026 RETURN VARCHAR2
2027 IS
2028 --
2029 l_proc_name VARCHAR2(80)
2030 := g_proc_name || 'hrs_history_post_process';
2031 l_proc_step PLS_INTEGER;
2032 l_return_value VARCHAR2(100);
2033 --
2034 BEGIN
2035 --
2036 IF g_debug
2037 THEN
2038 l_proc_step := 10;
2039 debug_enter(l_proc_name);
2040 END IF;
2041
2042 -- pqp_gb_psi_functions.raise_extract_exceptions('DE');
2043 pqp_gb_psi_functions.common_post_process(
2044 p_business_group_id => g_business_group_id
2045 );
2046
2047 IF g_debug
2048 THEN
2049 l_proc_step := 20;
2050 debug_exit(l_proc_name);
2051 END IF;
2052
2053 RETURN l_return_value;
2054 EXCEPTION
2055 WHEN OTHERS
2056 THEN
2057 clear_cache;
2058
2059 IF SQLCODE <> hr_utility.hr_error_number
2060 THEN
2061 debug_others(l_proc_name, l_proc_step);
2062
2063 IF g_debug
2064 THEN
2065 DEBUG('Leaving: ' || l_proc_name, -999);
2066 END IF;
2067
2068 fnd_message.raise_error;
2069 ELSE
2070 RAISE;
2071 END IF;
2072 END hrs_history_post_process;
2073 END pqp_gb_psi_hrs_history;