[Home] [Help]
PACKAGE BODY: APPS.HXT_INTERFACE_UTILITIES
Source
1 PACKAGE BODY hxt_interface_utilities AS
2 /* $Header: hxtinterfaceutil.pkb 120.8.12020000.3 2013/03/26 14:12:55 asrajago ship $ */
3
4 -- Global package name
5 g_debug boolean := hr_utility.debug_enabled;
6 g_package CONSTANT package_name := 'hxt_interface_utilities.';
7 g_current_element pay_element_types_f.element_type_id%TYPE
8 := NULL;
9 -- GLOBAL SWITCHES --
10 -- Controls whether we do commits or not during the run
11 g_do_commit BOOLEAN := TRUE; -- Should always be TRUE except for testing!
12 -- Controls whether we use the old method of creating retro batches vs the
13 -- new method
14 g_use_old_retro_batches BOOLEAN := TRUE; -- For now, this should be set to TRUE!
15 -- Controls whether we do local caching of data or not
16 g_caching BOOLEAN := TRUE;
17 -- caching tables
18 g_element_type_ivs input_value_name_table;
19 g_iv_translations iv_translation_table;
20 g_primary_assignments primary_assignment_table;
21 g_flex_values flex_value_table;
22 g_assignment_info assignment_info_table;
23 g_concatenated_segments concatenated_segment_table;
24 g_batch_info batch_info_table;
25 -- cached variables
26 g_conc_request_id_suffix fnd_concurrent_requests.request_id%TYPE;
27 --
28 g_batchname_suffix_connector VARCHAR2 (1) := '_';
29 g_max_batch_size NUMBER := -1;
30 g_tbb_index NUMBER;
31 g_tbb NUMBER;
32
33 FUNCTION current_element
34 RETURN pay_element_types_f.element_type_id%TYPE
35 IS
36 l_proc proc_name ;
37 BEGIN
38
39 if g_debug then
40 l_proc := g_package
41 || 'current_element';
42 hr_utility.set_location ( 'Entering:'
43 || l_proc, 10);
44 hr_utility.set_location ( ' returning g_current_element = '
45 || g_current_element,
46 20
47 );
48 hr_utility.set_location ( 'Leaving:'
49 || l_proc, 100);
50 end if;
51 RETURN g_current_element;
52 END current_element;
53
54 PROCEDURE set_current_element (
55 p_current_element IN pay_element_types_f.element_type_id%TYPE
56 )
57 IS
58 l_proc proc_name ;
59 BEGIN
60
61 if g_debug then
62 l_proc := g_package
63 || 'set_current_element';
64 hr_utility.set_location ( 'Entering:'
65 || l_proc, 10);
66 hr_utility.set_location ( ' setting g_current_element to '
67 || p_current_element,
68 20
69 );
70 end if;
71 g_current_element := p_current_element;
72 if g_debug then
73 hr_utility.set_location ( 'Leaving:'
74 || l_proc, 100);
75 end if;
76 END set_current_element;
77
78 FUNCTION do_commit
79 RETURN BOOLEAN
80 IS
81 l_proc proc_name ;
82 BEGIN
83 g_debug :=hr_utility.debug_enabled;
84 if g_debug then
85 l_proc := g_package
86 || 'do_commit';
87 hr_utility.set_location ( 'Entering:'
88 || l_proc, 10);
89 end if;
90 IF (g_do_commit)
91 THEN
92 if g_debug then
93 hr_utility.set_location (' returning g_do_commit = TRUE', 20);
94 end if;
95 ELSE
96 if g_debug then
97 hr_utility.set_location (' returning g_do_commit = FALSE', 30);
98 end if;
99 END IF;
100
101 if g_debug then
102 hr_utility.set_location ( 'Leaving:'
103 || l_proc, 100);
104 end if;
105 RETURN g_do_commit;
106 END do_commit;
107
108 PROCEDURE set_do_commit (p_do_commit IN BOOLEAN)
109 IS
110 l_proc proc_name ;
111 BEGIN
112 g_debug :=hr_utility.debug_enabled;
113 if g_debug then
114 l_proc := g_package
115 || 'set_do_commit';
116 hr_utility.set_location ( 'Entering:'
117 || l_proc, 10);
118 end if;
119 IF (p_do_commit)
120 THEN
121 if g_debug then
122 hr_utility.set_location (' setting g_do_commit to TRUE', 20);
123 end if;
124 ELSE
125 if g_debug then
126 hr_utility.set_location (' setting g_do_commit to FALSE', 30);
127 end if;
128 END IF;
129
130 g_do_commit := p_do_commit;
131 if g_debug then
132 hr_utility.set_location ( 'Leaving:'
133 || l_proc, 100);
134 end if;
135 END set_do_commit;
136
137 PROCEDURE perform_commit
138 IS
139 l_proc proc_name ;
140 BEGIN
141 g_debug :=hr_utility.debug_enabled;
142 if g_debug then
143 l_proc := g_package
144 || 'perform_commit';
145 hr_utility.set_location ( 'Entering:'
146 || l_proc, 10);
147 end if;
148 IF (do_commit)
149 THEN
150 if g_debug then
151 hr_utility.set_location (' Commiting', 20);
152 end if;
153 COMMIT;
154 ELSE
155 NULL; -- No Commit
156 END IF;
157
158 if g_debug then
159 hr_utility.set_location ( 'Leaving:'
160 || l_proc, 100);
161 end if;
162 END perform_commit;
163
164 FUNCTION use_old_retro_batches
165 RETURN BOOLEAN
166 IS
167 l_proc proc_name ;
168 BEGIN
169 g_debug :=hr_utility.debug_enabled;
170 if g_debug then
171 l_proc := g_package
172 || 'use_old_retro_batches';
173 hr_utility.set_location ( 'Entering:'
174 || l_proc, 10);
175 end if;
176 IF (g_use_old_retro_batches)
177 THEN
178 if g_debug then
179 hr_utility.set_location (' returning g_use_old_retro_batches = TRUE',
180 20
181 );
182 end if;
183 ELSE
184 if g_debug then
185 hr_utility.set_location (' returning g_use_old_retro_batches = FALSE',
186 30
187 );
188 end if;
189 END IF;
190
191 if g_debug then
192 hr_utility.set_location ( 'Leaving:'
193 || l_proc, 100);
194 end if;
195 RETURN g_use_old_retro_batches;
196 END use_old_retro_batches;
197
198 PROCEDURE set_use_old_retro_batches (p_use_old_retro_batches IN BOOLEAN)
199 IS
200 l_proc proc_name ;
201 BEGIN
202 g_debug :=hr_utility.debug_enabled;
203 if g_debug then
204 l_proc := g_package
205 || 'set_use_old_retro_batches';
206 hr_utility.set_location ( 'Entering:'
207 || l_proc, 10);
208 end if;
209 IF (p_use_old_retro_batches)
210 THEN
211 if g_debug then
212 hr_utility.set_location (' setting g_use_old_retro_batches to TRUE',
213 20
214 );
215 end if;
216 ELSE
217 if g_debug then
218 hr_utility.set_location (' setting g_use_old_retro_batches to FALSE',
219 30
220 );
221 end if;
222 END IF;
223
224 g_use_old_retro_batches := p_use_old_retro_batches;
225 if g_debug then
226 hr_utility.set_location ( 'Leaving:'
227 || l_proc, 100);
228 end if;
229 END set_use_old_retro_batches;
230
231 FUNCTION caching
232 RETURN BOOLEAN
233 IS
234 l_proc proc_name ;
235 BEGIN
236
237 if g_debug then
238 l_proc := g_package
239 || 'caching';
240 hr_utility.set_location ( 'Entering:'
241 || l_proc, 10);
242 end if;
243 IF (g_caching)
244 THEN
245 if g_debug then
246 hr_utility.set_location (' returning g_caching = TRUE', 20);
247 end if;
248 ELSE
249 if g_debug then
250 hr_utility.set_location (' returning g_caching = FALSE', 30);
251 end if;
252 END IF;
253
254 if g_debug then
255 hr_utility.set_location ( 'Leaving:'
256 || l_proc, 100);
257 end if;
258 RETURN g_caching;
259 END caching;
260
261 PROCEDURE set_caching (p_caching IN BOOLEAN)
262 IS
263 l_proc proc_name ;
264 BEGIN
265
266 if g_debug then
267 l_proc := g_package
268 || 'set_caching';
269 hr_utility.set_location ( 'Entering:'
270 || l_proc, 10);
271 end if;
272 IF (p_caching)
273 THEN
274 if g_debug then
275 hr_utility.set_location (' setting g_caching to TRUE', 20);
276 end if;
277 ELSE
278 if g_debug then
279 hr_utility.set_location (' setting g_caching to FALSE', 30);
280 end if;
281 END IF;
282
283 g_caching := p_caching;
284 if g_debug then
285 hr_utility.set_location ( 'Leaving:'
286 || l_proc, 100);
287 end if;
288 END set_caching;
289
290 FUNCTION batchname_suffix_connector
291 RETURN VARCHAR2
292 IS
293 l_proc proc_name;
294
295 BEGIN
296 g_debug :=hr_utility.debug_enabled;
297 if g_debug then
298 l_proc := g_package
299 || 'batchname_suffix_connector';
300 hr_utility.set_location ( 'Entering:'
301 || l_proc, 10);
302 hr_utility.set_location ( ' returning g_batchname_suffix_connector = '
303 || g_batchname_suffix_connector,
304 20
305 );
306 hr_utility.set_location ( 'Leaving:'
307 || l_proc, 100);
308 end if;
309 RETURN g_batchname_suffix_connector;
310 END batchname_suffix_connector;
311
312 PROCEDURE set_batchname_suffix_connector (
313 p_batchname_suffix_connector IN VARCHAR2
314 )
315 IS
316 l_proc proc_name;
317
318 BEGIN
319 g_debug :=hr_utility.debug_enabled;
320 if g_debug then
321 l_proc := g_package
322 || 'set_batchname_suffix_connector';
323 hr_utility.set_location ( 'Entering:'
324 || l_proc, 10);
325 hr_utility.set_location ( ' setting g_batchname_suffix_connector to '
326 || p_batchname_suffix_connector,
327 20
328 );
329 end if;
330 g_batchname_suffix_connector := p_batchname_suffix_connector;
331 if g_debug then
332 hr_utility.set_location ( 'Leaving:'
333 || l_proc, 100);
334 end if;
335 END set_batchname_suffix_connector;
336
337 FUNCTION hashval (p_str IN VARCHAR2)
338 RETURN PLS_INTEGER
339 IS
340 l_proc proc_name ;
341 l_hashval PLS_INTEGER;
342 c_maxrange CONSTANT PLS_INTEGER := 2147483647; -- POWER (2, 31) - 1;
343 c_start CONSTANT PLS_INTEGER := 2;
344 BEGIN
345
346 if g_debug then
347 l_proc := g_package
348 || 'hashval';
349 hr_utility.set_location ( 'Entering:'
350 || l_proc, 10);
351 end if;
352 l_hashval := DBMS_UTILITY.get_hash_value (p_str, c_start, c_maxrange);
353 if g_debug then
354 hr_utility.set_location ( 'Leaving:'
355 || l_proc, 100);
356 end if;
357 RETURN l_hashval;
358 END hashval;
359
360 PROCEDURE empty_asg_cache
361 IS
362 l_proc proc_name;
363
364 l_empty_primary_assignments primary_assignment_table;
365 l_empty_assignment_info assignment_info_table;
366 BEGIN
367 g_debug :=hr_utility.debug_enabled;
368 if g_debug then
369 l_proc := g_package
370 || 'empty_asg_cache';
371 hr_utility.set_location ( 'Entering:'
372 || l_proc, 10);
373 end if;
374 g_primary_assignments := l_empty_primary_assignments;
375 g_assignment_info := l_empty_assignment_info;
376 if g_debug then
377 hr_utility.set_location ( 'Leaving:'
378 || l_proc, 100);
379 end if;
380 END empty_asg_cache;
381
382 PROCEDURE empty_batch_suffix_cache
383 IS
384 l_proc proc_name;
385
386 l_empty_batch_info batch_info_table;
387 BEGIN
388 g_debug :=hr_utility.debug_enabled;
389 if g_debug then
390 l_proc := g_package
391 || 'empty_batch_suffix_cache';
392 hr_utility.set_location ( 'Entering:'
393 || l_proc, 10);
394 end if;
395 g_batch_info := l_empty_batch_info;
396 if g_debug then
397 hr_utility.set_location ( 'Leaving:'
398 || l_proc, 100);
399 end if;
400 END empty_batch_suffix_cache;
401
402 PROCEDURE empty_cache
403 IS
404 l_proc proc_name ;
405 BEGIN
406 g_debug :=hr_utility.debug_enabled;
407 if g_debug then
408 l_proc := g_package
409 || 'empty_cache';
410 hr_utility.set_location ( 'Entering:'
411 || l_proc, 10);
412 end if;
413 empty_asg_cache;
414 if g_debug then
415 hr_utility.set_location ( 'Leaving:'
416 || l_proc, 100);
417 end if;
418 END empty_cache;
419
420 FUNCTION max_batch_size
421 RETURN NUMBER
422 IS
423 l_proc proc_name ;
424 BEGIN
425 g_debug :=hr_utility.debug_enabled;
426 if g_debug then
427 l_proc := g_package
428 || 'max_batch_size';
429 hr_utility.set_location ( 'Entering:'
430 || l_proc, 10);
431 end if;
432 IF (g_max_batch_size = -1)
433 THEN
434 g_max_batch_size := fnd_profile.VALUE (g_otl_batchsize_profile);
435 END IF;
436
437 if g_debug then
438 hr_utility.set_location ( ' returning max batch size: '
439 || g_max_batch_size,
440 20
441 );
442 hr_utility.set_location ( 'Leaving:'
443 || l_proc, 100);
444 end if;
445 RETURN g_max_batch_size;
446 END max_batch_size;
447
448 FUNCTION conc_request_id_suffix (p_from_last IN PLS_INTEGER DEFAULT 4)
449 RETURN NUMBER
450 IS
451 l_proc proc_name;
452
453 l_conc_request_id_suffix NUMBER;
454 BEGIN
455 g_debug :=hr_utility.debug_enabled;
456 if g_debug then
457 l_proc := g_package
458 || 'conc_request_id_suffix';
459 hr_utility.set_location ( 'Entering:'
460 || l_proc, 10);
461 end if;
462 IF (g_conc_request_id_suffix IS NULL)
463 THEN
464 SELECT SUBSTR (fnd_global.conc_request_id,
465 -1
466 * (LEAST (p_from_last,
467 LENGTH (fnd_global.conc_request_id)
468 )
469 )
470 )
471 INTO l_conc_request_id_suffix
472 FROM DUAL;
473
474 g_conc_request_id_suffix := l_conc_request_id_suffix;
475 ELSE
476 l_conc_request_id_suffix := g_conc_request_id_suffix;
477 END IF;
478
479 if g_debug then
480 hr_utility.set_location ( ' returning l_conc_request_id_suffix: '
481 || l_conc_request_id_suffix,
482 20
483 );
484 hr_utility.set_location ( 'Leaving:'
485 || l_proc, 100);
486 end if;
487 RETURN l_conc_request_id_suffix;
488 END conc_request_id_suffix;
489
490 FUNCTION batch_name (
491 p_batch_ref IN pay_batch_headers.batch_reference%TYPE,
492 p_bg_id IN pay_batch_headers.business_group_id%TYPE,
493 p_invalid_batch_status IN pay_batch_headers.batch_status%TYPE
494 DEFAULT NULL
495 )
496 RETURN pay_batch_headers.batch_name%TYPE
497 AS
498 l_proc proc_name ;
499
500 CURSOR csr_max_batch_id (
501 p_batch_ref pay_batch_headers.batch_reference%TYPE,
502 p_bg_id pay_batch_headers.business_group_id%TYPE,
503 p_invalid_batch_status pay_batch_headers.batch_status%TYPE
504 )
505 IS
506 SELECT MAX (pbh.batch_id) batch_id
507 FROM pay_batch_headers pbh
508 WHERE pbh.batch_reference = p_batch_ref
509 AND pbh.business_group_id = p_bg_id
510 AND ( (pbh.batch_status <> p_invalid_batch_status)
511 OR (p_invalid_batch_status IS NULL)
512 );
513
514 CURSOR csr_batch_name (p_batch_id pay_batch_headers.batch_id%TYPE)
515 IS
516 SELECT batch_name
517 FROM pay_batch_headers pbh
518 WHERE pbh.batch_id = p_batch_id;
519
520 l_batch_id pay_batch_headers.batch_id%TYPE;
521 l_batch_name pay_batch_headers.batch_name%TYPE;
522 BEGIN
523 g_debug :=hr_utility.debug_enabled;
524 if g_debug then
525 l_proc := g_package
526 || 'batch_name';
527 hr_utility.set_location ( 'Entering:'
528 || l_proc, 10);
529 end if;
530 IF (csr_max_batch_id%ISOPEN)
531 THEN
532 CLOSE csr_max_batch_id;
533 END IF;
534
535 OPEN csr_max_batch_id (p_batch_ref, p_bg_id, p_invalid_batch_status);
536 FETCH csr_max_batch_id INTO l_batch_id;
537 CLOSE csr_max_batch_id;
538
539 IF (csr_batch_name%ISOPEN)
540 THEN
541 CLOSE csr_batch_name;
542 END IF;
543
544 OPEN csr_batch_name (l_batch_id);
545 FETCH csr_batch_name INTO l_batch_name;
546 CLOSE csr_batch_name;
547 if g_debug then
548 hr_utility.set_location ( ' returning batch name: '
549 || l_batch_name, 20);
550 hr_utility.set_location ( 'Leaving:'
551 || l_proc, 100);
552 end if;
553 RETURN l_batch_name;
554 END batch_name;
555
556 FUNCTION max_batch_id (
557 p_batch_name IN pay_batch_headers.batch_name%TYPE,
558 p_bg_id IN pay_batch_headers.business_group_id%TYPE
559 )
560 RETURN pay_batch_headers.batch_id%TYPE
561 AS
562 l_proc proc_name ;
563
564 CURSOR csr_max_batch_id (
565 p_batch_name IN pay_batch_headers.batch_name%TYPE,
566 p_bg_id IN pay_batch_headers.business_group_id%TYPE
567 )
568 IS
569 SELECT MAX (pbh.batch_id) -- we need to do max because name is not unique
570 FROM pay_batch_headers pbh
571 WHERE pbh.batch_name = p_batch_name
572 AND pbh.business_group_id = p_bg_id;
573
574 l_batch_id pay_batch_headers.batch_id%TYPE;
575 BEGIN
576 g_debug :=hr_utility.debug_enabled;
577 if g_debug then
578 l_proc := g_package
579 || 'max_batch_id';
580 hr_utility.set_location ( 'Entering:'
581 || l_proc, 10);
582 end if;
583 IF (csr_max_batch_id%ISOPEN)
584 THEN
585 CLOSE csr_max_batch_id;
586 END IF;
587
588 OPEN csr_max_batch_id (p_batch_name, p_bg_id);
589 FETCH csr_max_batch_id INTO l_batch_id;
590 CLOSE csr_max_batch_id;
591 if g_debug then
592 hr_utility.set_location ( ' returning batch id: '
593 || l_batch_id, 20);
594 hr_utility.set_location ( 'Leaving:'
595 || l_proc, 100);
596 end if;
597 RETURN l_batch_id;
598 END max_batch_id;
599
600 FUNCTION max_batch_id (
601 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
602 p_bg_id IN pay_batch_headers.business_group_id%TYPE
603 )
604 RETURN pay_batch_headers.batch_id%TYPE
605 AS
606 l_proc proc_name ;
607
608 CURSOR csr_max_batch_id (
609 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
610 p_bg_id IN pay_batch_headers.business_group_id%TYPE
611 )
612 IS
613 SELECT MAX (pbh.batch_id) -- we need to do max because name is not unique
614 FROM pay_batch_headers pbh
615 WHERE pbh.batch_name = p_batch_reference
616 AND pbh.business_group_id = p_bg_id;
617
618 l_batch_id pay_batch_headers.batch_id%TYPE;
619 BEGIN
620 g_debug :=hr_utility.debug_enabled;
621 if g_debug then
622 l_proc := g_package
623 || 'max_batch_id';
624 hr_utility.set_location ( 'Entering:'
625 || l_proc, 10);
626 end if;
627 IF (csr_max_batch_id%ISOPEN)
628 THEN
629 CLOSE csr_max_batch_id;
630 END IF;
631
632 OPEN csr_max_batch_id (p_batch_reference, p_bg_id);
633 FETCH csr_max_batch_id INTO l_batch_id;
634 CLOSE csr_max_batch_id;
635 if g_debug then
636 hr_utility.set_location ( ' returning batch id: '
637 || l_batch_id, 20);
638 hr_utility.set_location ( 'Leaving:'
639 || l_proc, 100);
640 end if;
641 RETURN l_batch_id;
642 END max_batch_id;
643
644 FUNCTION count_batch_lines (p_batch_id IN pay_batch_headers.batch_id%TYPE)
645 RETURN NUMBER
646 AS
647 l_proc proc_name ;
648
649 CURSOR csr_batch_lines (p_batch_id IN pay_batch_headers.batch_name%TYPE)
650 IS
651 SELECT COUNT (pbl.batch_line_id)
652 FROM pay_batch_lines pbl
653 WHERE pbl.batch_id = p_batch_id;
654
655 l_batch_lines NUMBER;
656 BEGIN
657 g_debug :=hr_utility.debug_enabled;
658 if g_debug then
659 l_proc := g_package
660 || 'count_batch_lines';
661 hr_utility.set_location ( 'Entering:'
662 || l_proc, 10);
663 end if;
664 OPEN csr_batch_lines (p_batch_id);
665 FETCH csr_batch_lines INTO l_batch_lines;
666 CLOSE csr_batch_lines;
667 if g_debug then
668 hr_utility.set_location ( ' returning number of batch lines: '
669 || l_batch_lines,
670 20
671 );
672 hr_utility.set_location ( 'Leaving:'
673 || l_proc, 100);
674 end if;
675 RETURN l_batch_lines;
676 END count_batch_lines;
677
678 FUNCTION count_batch_lines (
679 p_batch_name IN pay_batch_headers.batch_name%TYPE,
680 p_bg_id IN pay_batch_headers.business_group_id%TYPE
681 )
682 RETURN NUMBER
683 AS
684 l_proc proc_name ;
685 l_batch_id pay_batch_headers.batch_name%TYPE;
686 l_batch_lines NUMBER;
687 BEGIN
688 g_debug :=hr_utility.debug_enabled;
689 if g_debug then
690 l_proc := g_package
691 || 'count_batch_lines';
692 hr_utility.set_location ( 'Entering:'
693 || l_proc, 10);
694 end if;
695 l_batch_id :=
696 max_batch_id (p_batch_name => p_batch_name,
697 p_bg_id => p_bg_id);
698 l_batch_lines := count_batch_lines (p_batch_id => l_batch_id);
699 if g_debug then
700 hr_utility.set_location ( ' returning number of batch lines: '
701 || l_batch_lines,
702 20
703 );
704 hr_utility.set_location ( 'Leaving:'
705 || l_proc, 100);
706 end if;
707 RETURN l_batch_lines;
708 END count_batch_lines;
709
710 FUNCTION total_batch_lines (
711 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
712 p_bg_id IN pay_batch_headers.business_group_id%TYPE
713 )
714 RETURN NUMBER
715 AS
716 l_proc proc_name ;
717
718 CURSOR csr_batch_lines (
719 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
720 p_bg_id IN pay_batch_headers.business_group_id%TYPE
721 )
722 IS
723 SELECT COUNT (pbl.batch_line_id)
724 FROM pay_batch_lines pbl, pay_batch_headers pbh
725 WHERE pbl.batch_id = pbh.batch_id
726 AND pbh.business_group_id = p_bg_id
727 AND batch_reference = p_batch_reference;
728
729 l_batch_lines NUMBER;
730 BEGIN
731 g_debug :=hr_utility.debug_enabled;
732 if g_debug then
733 l_proc := g_package
734 || 'total_batch_lines';
735 hr_utility.set_location ( 'Entering:'
736 || l_proc, 10);
737 end if;
738 OPEN csr_batch_lines (p_batch_reference, p_bg_id);
739 FETCH csr_batch_lines INTO l_batch_lines;
740 CLOSE csr_batch_lines;
741 if g_debug then
742 hr_utility.set_location ( ' returning number of batch lines: '
743 || l_batch_lines,
744 20
745 );
746 hr_utility.set_location ( 'Leaving:'
747 || l_proc, 100);
748 end if;
749 RETURN l_batch_lines;
750 END total_batch_lines;
751
752 FUNCTION max_lines_exceeded (
753 p_batch_id IN pay_batch_headers.batch_reference%TYPE
754 )
755 RETURN BOOLEAN
756 IS
757 l_proc proc_name ;
758 l_max_lines_exceeded BOOLEAN;
759 BEGIN
760 g_debug :=hr_utility.debug_enabled;
761 if g_debug then
762 l_proc := g_package
763 || 'max_lines_exceeded';
764 hr_utility.set_location ( 'Entering:'
765 || l_proc, 10);
766 end if;
767 IF (count_batch_lines (p_batch_id => p_batch_id) >= max_batch_size)
768 THEN
769 l_max_lines_exceeded := TRUE;
770 if g_debug then
771 hr_utility.set_location (' Maximum lines in batch exceeded!', 20);
772 end if;
773 ELSE
774 l_max_lines_exceeded := FALSE;
775 END IF;
776
777 if g_debug then
778 hr_utility.set_location ( 'Leaving:'
779 || l_proc, 100);
780 end if;
781 RETURN l_max_lines_exceeded;
782 END max_lines_exceeded;
783
784 PROCEDURE max_lines_exceeded (
785 p_batch_id IN pay_batch_headers.batch_reference%TYPE,
786 p_number_lines IN OUT NOCOPY PLS_INTEGER,
787 p_max_lines_exceeded OUT NOCOPY BOOLEAN
788 )
789 IS
790 l_proc proc_name ;
791 l_max_lines_exceeded BOOLEAN;
792 BEGIN
793 g_debug :=hr_utility.debug_enabled;
794 if g_debug then
795 l_proc := g_package
796 || 'max_lines_exceeded';
797 hr_utility.set_location ( 'Entering:'
798 || l_proc, 10);
799 end if;
800 IF (p_number_lines IS NULL)
801 THEN
802 p_number_lines := count_batch_lines (p_batch_id => p_batch_id);
803 END IF;
804
805 IF (p_number_lines >= max_batch_size)
806 THEN
807 p_max_lines_exceeded := TRUE;
808 if g_debug then
809 hr_utility.set_location (' Maximum lines in batch exceeded!', 20);
810 end if;
811 ELSE
812 p_max_lines_exceeded := FALSE;
813 END IF;
814
815 if g_debug then
816 hr_utility.set_location ( 'Leaving:'
817 || l_proc, 100);
818 end if;
819 END max_lines_exceeded;
820
821 FUNCTION max_lines_exceeded (
822 p_batch_ref IN pay_batch_headers.batch_reference%TYPE,
823 p_bg_id IN pay_batch_headers.business_group_id%TYPE
824 )
825 RETURN BOOLEAN
826 IS
827 l_proc proc_name ;
828 l_batch_name pay_batch_headers.batch_name%TYPE;
829 l_max_lines_exceeded BOOLEAN;
830 BEGIN
831 g_debug :=hr_utility.debug_enabled;
832 if g_debug then
833 l_proc := g_package
834 || 'max_lines_exceeded';
835 hr_utility.set_location ( 'Entering:'
836 || l_proc, 10);
837 end if;
838 l_batch_name :=
839 batch_name (p_batch_ref => p_batch_ref,
840 p_bg_id => p_bg_id,
841 p_invalid_batch_status => g_batch_status_transferred
842 );
843
844 IF (count_batch_lines (p_batch_name => l_batch_name,
845 p_bg_id => p_bg_id) > max_batch_size
846 )
847 THEN
848 l_max_lines_exceeded := TRUE;
849 if g_debug then
850 hr_utility.set_location (' Maximum lines in batch exceeded!', 20);
851 end if;
852 ELSE
853 l_max_lines_exceeded := FALSE;
854 END IF;
855
856 if g_debug then
857 hr_utility.set_location ( 'Leaving:'
858 || l_proc, 100);
859 end if;
860 RETURN l_max_lines_exceeded;
861 END max_lines_exceeded;
862
863 FUNCTION isnumber (p_value VARCHAR2)
864 RETURN BOOLEAN
865 IS
866 l_proc proc_name ;
867 l_number NUMBER;
868 l_isnumber BOOLEAN := FALSE;
869 BEGIN
870 g_debug :=hr_utility.debug_enabled;
871 if g_debug then
872 l_proc := g_package
873 || 'isnumber';
874 hr_utility.set_location ( 'Entering:'
875 || l_proc, 10);
876 end if;
877 BEGIN
878 l_number := p_value;
879 l_isnumber := TRUE;
880 EXCEPTION
881 WHEN OTHERS
882 THEN
883 if g_debug then
884 hr_utility.set_location ( 'Leaving:'
885 || l_proc, 50);
886 end if;
887 RETURN l_isnumber;
888 END;
889
890 if g_debug then
891 hr_utility.set_location ( 'Leaving:'
892 || l_proc, 100);
893 end if;
894 RETURN l_isnumber;
895 END isnumber;
896
897 FUNCTION detail_lines_retrieved (
898 p_tbb_tbl IN hxc_generic_retrieval_pkg.t_building_blocks
899 )
900 RETURN BOOLEAN
901 IS
902 l_proc proc_name;
903
904 l_detail_lines_retrieved BOOLEAN;
905 BEGIN
906 g_debug :=hr_utility.debug_enabled;
907 if g_debug then
908 l_proc := g_package
909 || 'detail_lines_retrieved';
910 hr_utility.set_location ( 'Entering:'
911 || l_proc, 10);
912 end if;
913 IF (p_tbb_tbl.COUNT <> 0)
914 THEN
915 l_detail_lines_retrieved := TRUE;
916 if g_debug then
917 hr_utility.set_location (' Detail lines retrieved!', 20);
918 end if;
919 ELSE
920 l_detail_lines_retrieved := FALSE;
921 END IF;
922
923 if g_debug then
924 hr_utility.set_location ( 'Leaving:'
925 || l_proc, 100);
926 end if;
927 RETURN l_detail_lines_retrieved;
928 END detail_lines_retrieved;
929
930 FUNCTION gre (
931 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
932 p_effective_date IN per_all_assignments_f.effective_start_date%TYPE
933 )
934 RETURN hr_soft_coding_keyflex.segment1%TYPE
935 IS
936 l_proc proc_name ;
937
938 CURSOR csr_gre (
939 p_assignment_id per_all_assignments_f.assignment_id%TYPE,
940 p_effective_date per_all_assignments_f.effective_start_date%TYPE
941 )
942 IS
943 SELECT hsck.segment1 gre
944 FROM per_all_assignments_f paaf, hr_soft_coding_keyflex hsck
945 WHERE paaf.assignment_id = p_assignment_id
946 AND p_effective_date BETWEEN paaf.effective_start_date
947 AND paaf.effective_end_date
948 AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id;
949
950 l_gre hr_soft_coding_keyflex.segment1%TYPE;
951 BEGIN
952 g_debug :=hr_utility.debug_enabled;
953 if g_debug then
954 l_proc := g_package
955 || 'gre';
956 hr_utility.set_location ( 'Entering '
957 || l_proc, 10);
958 end if;
959 IF (csr_gre%ISOPEN)
960 THEN
961 CLOSE csr_gre;
962 END IF;
963
964 OPEN csr_gre (p_assignment_id, p_effective_date);
965 FETCH csr_gre INTO l_gre;
966 CLOSE csr_gre;
967 if g_debug then
968 hr_utility.set_location ( ' returning gre = '
969 || l_gre, 90);
970 hr_utility.set_location ( 'Leaving '
971 || l_proc, 100);
972 end if;
973 RETURN l_gre;
974 END gre;
975
976 FUNCTION table_to_comma (p_asg_type IN asg_type_table)
977 RETURN VARCHAR2
978 AS
979 l_proc proc_name ;
980 l_asg_types_idx PLS_INTEGER;
981 l_var_asg_types DBMS_UTILITY.uncl_array;
982 l_tablen BINARY_INTEGER;
983 l_list VARCHAR2 (4000);
984 BEGIN
985
986 if g_debug then
987 l_proc := g_package
988 || 'table_to_comma';
989 hr_utility.set_location ( 'Entering '
990 || l_proc, 10);
991 end if;
992 l_asg_types_idx := p_asg_type.FIRST;
993
994 LOOP
995 EXIT WHEN NOT p_asg_type.EXISTS (l_asg_types_idx);
996 l_var_asg_types (l_asg_types_idx) :=
997 p_asg_type (l_asg_types_idx).asg_type;
998 l_asg_types_idx := p_asg_type.NEXT (l_asg_types_idx);
999 END LOOP;
1000
1001 DBMS_UTILITY.table_to_comma (tab => l_var_asg_types,
1002 tablen => l_tablen,
1003 LIST => l_list
1004 );
1005 if g_debug then
1006 hr_utility.set_location ( 'Leaving '
1007 || l_proc, 100);
1008 end if;
1009 RETURN l_list;
1010 END table_to_comma;
1011
1012 FUNCTION table_to_comma (p_asg_system_type IN asg_system_status_table)
1013 RETURN VARCHAR2
1014 AS
1015 l_proc proc_name ;
1016 l_asg_system_types_idx PLS_INTEGER;
1017 l_var_asg_system_types DBMS_UTILITY.uncl_array;
1018 l_tablen BINARY_INTEGER;
1019 l_list VARCHAR2 (4000);
1020 BEGIN
1021 g_debug :=hr_utility.debug_enabled;
1022 if g_debug then
1023 l_proc := g_package
1024 || 'table_to_comma';
1025 hr_utility.set_location ( 'Entering '
1026 || l_proc, 10);
1027 end if;
1028 l_asg_system_types_idx := p_asg_system_type.FIRST;
1029
1030 LOOP
1031 EXIT WHEN NOT p_asg_system_type.EXISTS (l_asg_system_types_idx);
1032 l_var_asg_system_types (l_asg_system_types_idx) :=
1033 p_asg_system_type (l_asg_system_types_idx).asg_system_status;
1034 l_asg_system_types_idx :=
1035 p_asg_system_type.NEXT (l_asg_system_types_idx);
1036 END LOOP;
1037
1038 DBMS_UTILITY.table_to_comma (tab => l_var_asg_system_types,
1039 tablen => l_tablen,
1040 LIST => l_list
1041 );
1042 if g_debug then
1043 hr_utility.set_location ( 'Leaving '
1044 || l_proc, 100);
1045 end if;
1046 RETURN l_list;
1047 END table_to_comma;
1048
1049 FUNCTION valid_assignment_type (
1050 p_asg_type IN per_all_assignments_f.assignment_type%TYPE,
1051 p_validation_tbl IN asg_type_table
1052 )
1053 RETURN BOOLEAN
1054 AS
1055 l_proc proc_name ;
1056 l_val_tbl_idx PLS_INTEGER;
1057 l_valid BOOLEAN := FALSE;
1058 BEGIN
1059 g_debug :=hr_utility.debug_enabled;
1060 if g_debug then
1061 l_proc := g_package
1062 || 'valid_assignment_type';
1063 hr_utility.set_location ( 'Entering '
1064 || l_proc, 10);
1065 end if;
1066 IF (p_validation_tbl.COUNT <> 0) -- values passed, start comparing
1067 THEN
1068 l_val_tbl_idx := p_validation_tbl.FIRST;
1069
1070 LOOP
1071 EXIT WHEN NOT (p_validation_tbl.EXISTS (l_val_tbl_idx));
1072
1073 IF (p_validation_tbl (l_val_tbl_idx).asg_type = p_asg_type)
1074 THEN
1075 l_valid := TRUE;
1076 if g_debug then
1077 hr_utility.set_location (' Valid Assignment!', 20);
1078 end if;
1079 END IF;
1080
1081 l_val_tbl_idx := p_validation_tbl.NEXT (l_val_tbl_idx);
1082 END LOOP;
1083 ELSE -- no values in table passed means everything is valid
1084 l_valid := TRUE;
1085 if g_debug then
1086 hr_utility.set_location (' Valid Assignment!', 30);
1087 end if;
1088 END IF;
1089
1090 if g_debug then
1091 hr_utility.set_location ( 'Leaving '
1092 || l_proc, 40);
1093 end if;
1094 RETURN l_valid;
1095 END valid_assignment_type;
1096
1097 FUNCTION valid_assignment_system_status (
1098 p_asg_system_status IN per_assignment_status_types.per_system_status%TYPE,
1099 p_validation_tbl IN asg_system_status_table
1100 )
1101 RETURN BOOLEAN
1102 AS
1103 l_proc proc_name;
1104
1105 l_val_tbl_idx PLS_INTEGER;
1106 l_valid BOOLEAN := FALSE;
1107 BEGIN
1108 g_debug :=hr_utility.debug_enabled;
1109 if g_debug then
1110 l_proc := g_package
1111 || 'valid_assignment_system_status';
1112 hr_utility.set_location ( 'Entering '
1113 || l_proc, 10);
1114 end if;
1115 IF (p_validation_tbl.COUNT <> 0) -- values passed, start comparing
1116 THEN
1117 l_val_tbl_idx := p_validation_tbl.FIRST;
1118
1119 LOOP
1120 EXIT WHEN NOT (p_validation_tbl.EXISTS (l_val_tbl_idx));
1121
1122 IF (p_validation_tbl (l_val_tbl_idx).asg_system_status =
1123 p_asg_system_status
1124 )
1125 THEN
1126 l_valid := TRUE;
1127 if g_debug then
1128 hr_utility.set_location (' Valid Assignment!', 20);
1129 end if;
1130 END IF;
1131
1132 l_val_tbl_idx := p_validation_tbl.NEXT (l_val_tbl_idx);
1133 END LOOP;
1134 ELSE -- no values in table passed means everything is valid
1135 l_valid := TRUE;
1136 if g_debug then
1137 hr_utility.set_location (' Valid Assignment!', 30);
1138 end if;
1139 END IF;
1140
1141 if g_debug then
1142 hr_utility.set_location ( 'Leaving '
1143 || l_proc, 40);
1144 end if;
1145 RETURN l_valid;
1146 END valid_assignment_system_status;
1147
1148 FUNCTION primary_assignment_id (
1149 p_person_id IN per_people_f.person_id%TYPE,
1150 p_effective_date IN DATE,
1151 p_valid_asg_types IN asg_type_table,
1152 p_valid_asg_status_types IN asg_system_status_table
1153 )
1154 RETURN per_all_assignments_f.assignment_id%TYPE
1155 IS
1156 l_proc proc_name ;
1157
1158 CURSOR csr_prim_asg (
1159 p_person_id per_people_f.person_id%TYPE,
1160 p_asg_type_list VARCHAR2,
1161 p_asg_system_type_list VARCHAR2,
1162 p_effective_date DATE
1163 )
1164 IS
1165 SELECT paf.assignment_id, paf.assignment_type,
1166 past.per_system_status, paf.effective_start_date,
1167 paf.effective_end_date
1168 FROM per_assignments_f paf, per_assignment_status_types past
1169 WHERE p_effective_date BETWEEN paf.effective_start_date
1170 AND paf.effective_end_date
1171 AND paf.primary_flag = c_primary_assignment
1172 AND paf.person_id = p_person_id
1173 AND ( INSTR (p_asg_type_list, paf.assignment_type) <> 0
1174 OR LENGTH (p_asg_type_list) IS NULL -- if the list is empty ALL asg are valid
1175 )
1176 AND past.assignment_status_type_id = paf.assignment_status_type_id
1177 AND ( INSTR (p_asg_system_type_list, past.per_system_status) <>
1178 0
1179 OR LENGTH (p_asg_system_type_list) IS NULL -- if the list is empty ALL asg are valid
1180 );
1181
1182 l_rec_prim_asg csr_prim_asg%ROWTYPE;
1183 l_prim_asg_id per_all_assignments_f.assignment_id%TYPE := NULL;
1184 l_found_in_cache BOOLEAN := FALSE;
1185 BEGIN
1186 g_debug :=hr_utility.debug_enabled;
1187 if g_debug then
1188 l_proc := g_package
1189 || 'primary_assignment_id';
1190 hr_utility.set_location ( 'Entering:'
1191 || l_proc, 10);
1192 end if;
1193 IF ((caching) AND (g_primary_assignments.EXISTS (p_person_id)))
1194 THEN
1195 IF ( (p_effective_date
1196 BETWEEN g_primary_assignments (p_person_id).effective_start_date
1197 AND g_primary_assignments (p_person_id).effective_end_date
1198 )
1199 AND (valid_assignment_type (g_primary_assignments (p_person_id).assignment_type,
1200 p_valid_asg_types
1201 )
1202 )
1203 AND (valid_assignment_system_status (g_primary_assignments (p_person_id
1204 ).per_system_status,
1205 p_valid_asg_status_types
1206 )
1207 )
1208 )
1209 THEN
1210 l_prim_asg_id :=
1211 g_primary_assignments (p_person_id).assignment_id;
1212 l_found_in_cache := TRUE;
1213 END IF;
1214 END IF;
1215
1216 IF (NOT l_found_in_cache)
1217 THEN
1218 IF (csr_prim_asg%ISOPEN)
1219 THEN
1220 CLOSE csr_prim_asg;
1221 END IF;
1222
1223 OPEN csr_prim_asg (p_person_id,
1224 table_to_comma (p_valid_asg_types),
1225 table_to_comma (p_valid_asg_status_types),
1226 p_effective_date
1227 );
1228 FETCH csr_prim_asg INTO l_rec_prim_asg;
1229
1230 IF (csr_prim_asg%FOUND)
1231 THEN
1232 l_prim_asg_id := l_rec_prim_asg.assignment_id;
1233
1234 IF (caching)
1235 THEN
1236 g_primary_assignments (p_person_id).assignment_id :=
1237 l_rec_prim_asg.assignment_id;
1238 g_primary_assignments (p_person_id).effective_start_date :=
1239 l_rec_prim_asg.effective_start_date;
1240 g_primary_assignments (p_person_id).effective_end_date :=
1241 l_rec_prim_asg.effective_end_date;
1242 g_primary_assignments (p_person_id).assignment_type :=
1243 l_rec_prim_asg.assignment_type;
1244 g_primary_assignments (p_person_id).per_system_status :=
1245 l_rec_prim_asg.per_system_status;
1246 END IF;
1247 END IF;
1248 END IF;
1249
1250 if g_debug then
1251 hr_utility.set_location ( ' returning '
1252 || l_prim_asg_id, 20);
1253 hr_utility.set_location ( 'Leaving:'
1254 || l_proc, 30);
1255 end if;
1256 RETURN l_prim_asg_id;
1257 END primary_assignment_id;
1258
1259 PROCEDURE get_assignment_info (
1260 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1261 p_effective_date IN per_all_assignments_f.effective_end_date%TYPE,
1262 p_assignment_number OUT NOCOPY per_all_assignments_f.assignment_number%TYPE,
1263 p_payroll_id OUT NOCOPY per_all_assignments_f.payroll_id%TYPE,
1264 p_org_id OUT NOCOPY per_all_assignments_f.organization_id%TYPE,
1265 p_location_id OUT NOCOPY per_all_assignments_f.location_id%TYPE,
1266 p_bg_id OUT NOCOPY per_all_assignments_f.business_group_id%TYPE
1267 )
1268 IS
1269 l_proc proc_name ;
1270
1271 CURSOR csr_assignment_info (
1272 p_assignment_id per_all_assignments_f.assignment_id%TYPE,
1273 p_effective_date per_all_assignments_f.effective_end_date%TYPE
1274 )
1275 IS
1276 SELECT paaf.assignment_number, paaf.payroll_id, paaf.organization_id,
1277 paaf.location_id, paaf.business_group_id,
1278 paaf.assignment_type, paaf.effective_start_date,
1279 paaf.effective_end_date
1280 FROM per_all_assignments_f paaf
1281 WHERE paaf.assignment_id = p_assignment_id
1282 AND p_effective_date BETWEEN paaf.effective_start_date
1283 AND paaf.effective_end_date;
1284
1285 l_assignment_info csr_assignment_info%ROWTYPE;
1286 l_found_in_cache BOOLEAN := FALSE;
1287 BEGIN
1288 g_debug :=hr_utility.debug_enabled;
1289 if g_debug then
1290 l_proc := g_package
1291 || 'get_assignment_info';
1292 hr_utility.set_location ( 'Entering:'
1293 || l_proc, 10);
1294 end if;
1295 IF ((caching) AND (g_assignment_info.EXISTS (p_assignment_id)))
1296 THEN
1297 IF ((p_effective_date
1298 BETWEEN g_assignment_info (p_assignment_id).effective_start_date
1299 AND g_assignment_info (p_assignment_id).effective_end_date
1300 )
1301 )
1302 THEN
1303 p_assignment_number :=
1304 g_assignment_info (p_assignment_id).assignment_number;
1305 p_payroll_id := g_assignment_info (p_assignment_id).payroll_id;
1306 p_org_id := g_assignment_info (p_assignment_id).organization_id;
1307 p_location_id := g_assignment_info (p_assignment_id).location_id;
1308 p_bg_id := g_assignment_info (p_assignment_id).business_group_id;
1309 l_found_in_cache := TRUE;
1310 END IF;
1311 END IF;
1312
1313 IF (NOT l_found_in_cache)
1314 THEN
1315 IF (csr_assignment_info%ISOPEN)
1316 THEN
1317 CLOSE csr_assignment_info;
1318 END IF;
1319
1320 OPEN csr_assignment_info (p_assignment_id, p_effective_date);
1321 FETCH csr_assignment_info INTO l_assignment_info;
1322
1323 IF (csr_assignment_info%FOUND)
1324 THEN
1325 p_assignment_number := l_assignment_info.assignment_number;
1326 p_payroll_id := l_assignment_info.payroll_id;
1327 p_org_id := l_assignment_info.organization_id;
1328 p_location_id := l_assignment_info.location_id;
1329 p_bg_id := l_assignment_info.business_group_id;
1330
1331 IF (caching)
1332 THEN
1333 g_assignment_info (p_assignment_id).effective_start_date :=
1334 l_assignment_info.effective_start_date;
1335 g_assignment_info (p_assignment_id).effective_end_date :=
1336 l_assignment_info.effective_end_date;
1337 g_assignment_info (p_assignment_id).assignment_number :=
1338 l_assignment_info.assignment_number;
1339 g_assignment_info (p_assignment_id).payroll_id :=
1340 l_assignment_info.payroll_id;
1341 g_assignment_info (p_assignment_id).organization_id :=
1342 l_assignment_info.organization_id;
1343 g_assignment_info (p_assignment_id).location_id :=
1344 l_assignment_info.location_id;
1345 g_assignment_info (p_assignment_id).business_group_id :=
1346 l_assignment_info.business_group_id;
1347 END IF;
1348 END IF;
1349
1350 CLOSE csr_assignment_info;
1351 END IF;
1352
1353 if g_debug then
1354 hr_utility.set_location ( 'Leaving:'
1355 || l_proc, 100);
1356 end if;
1357 END get_assignment_info;
1358
1359 PROCEDURE get_primary_assignment_info (
1360 p_person_id IN per_all_assignments_f.person_id%TYPE,
1361 p_effective_date IN per_all_assignments_f.effective_end_date%TYPE,
1362 p_assignment_id OUT NOCOPY per_all_assignments_f.assignment_id%TYPE,
1363 p_assignment_number OUT NOCOPY per_all_assignments_f.assignment_number%TYPE,
1364 p_payroll_id OUT NOCOPY per_all_assignments_f.payroll_id%TYPE,
1365 p_org_id OUT NOCOPY per_all_assignments_f.organization_id%TYPE,
1366 p_location_id OUT NOCOPY per_all_assignments_f.location_id%TYPE,
1367 p_bg_id OUT NOCOPY per_all_assignments_f.business_group_id%TYPE
1368 )
1369 IS
1370 l_proc proc_name;
1371
1372 l_valid_asg_types asg_type_table;
1373 l_valid_asg_status_types asg_system_status_table;
1374 BEGIN
1375 g_debug :=hr_utility.debug_enabled;
1376 if g_debug then
1377 l_proc := g_package
1378 || 'get_primary_assignment_info';
1379 hr_utility.set_location ( 'Entering:'
1380 || l_proc, 10);
1381 end if;
1382 l_valid_asg_types (1).asg_type := g_asg_type_employed;
1383 p_assignment_id :=
1384 primary_assignment_id (p_person_id => p_person_id,
1385 p_effective_date => p_effective_date,
1386 p_valid_asg_types => l_valid_asg_types,
1387 p_valid_asg_status_types => l_valid_asg_status_types
1388 );
1389 get_assignment_info (p_assignment_id => p_assignment_id,
1390 p_effective_date => p_effective_date,
1391 p_assignment_number => p_assignment_number,
1392 p_payroll_id => p_payroll_id,
1393 p_org_id => p_org_id,
1394 p_location_id => p_location_id,
1395 p_bg_id => p_bg_id
1396 );
1397 if g_debug then
1398 hr_utility.set_location ( 'Leaving:'
1399 || l_proc, 100);
1400 end if;
1401 END get_primary_assignment_info;
1402
1403 FUNCTION is_assignment (
1404 p_tbb_rec IN hxc_generic_retrieval_pkg.r_building_blocks
1405 )
1406 RETURN BOOLEAN
1407 IS
1408 l_proc proc_name ;
1409 l_is_assignment BOOLEAN := FALSE;
1410 BEGIN
1411 g_debug :=hr_utility.debug_enabled;
1412 if g_debug then
1413 l_proc := g_package
1414 || 'is_assignment';
1415 hr_utility.set_location ( 'Entering '
1416 || l_proc, 10);
1417 end if;
1418 IF (p_tbb_rec.resource_type = hxc_timecard.c_assignment_resource)
1419 THEN
1420 if g_debug then
1421 hr_utility.set_location (' resource_type = ASSIGNMENT ', 20);
1422 end if;
1423 l_is_assignment := TRUE;
1424 ELSE
1425 l_is_assignment := FALSE;
1426 END IF;
1427
1428 if g_debug then
1429 hr_utility.set_location ( 'Leaving '
1430 || l_proc, 100);
1431 end if;
1432 RETURN l_is_assignment;
1433 END is_assignment;
1434
1435 FUNCTION is_person (p_tbb_rec IN hxc_generic_retrieval_pkg.r_building_blocks)
1436 RETURN BOOLEAN
1437 IS
1438 l_proc proc_name ;
1439 l_is_person BOOLEAN := FALSE;
1440 BEGIN
1441 g_debug :=hr_utility.debug_enabled;
1442 if g_debug then
1443 l_proc := g_package
1444 || 'is_person';
1445 hr_utility.set_location ( 'Entering '
1446 || l_proc, 10);
1447 end if;
1448 IF (p_tbb_rec.resource_type = hxc_timecard.c_person_resource)
1449 THEN
1450 if g_debug then
1451 hr_utility.set_location (' resource_type = PERSON ', 20);
1452 end if;
1453 l_is_person := TRUE;
1454 ELSE
1455 l_is_person := FALSE;
1456 END IF;
1457
1458 if g_debug then
1459 hr_utility.set_location ( 'Leaving '
1460 || l_proc, 100);
1461 end if;
1462 RETURN l_is_person;
1463 END is_person;
1464
1465 PROCEDURE assignment_info (
1466 p_tbb_rec IN hxc_generic_retrieval_pkg.r_building_blocks,
1467 p_assignment_id OUT NOCOPY per_all_assignments_f.assignment_id%TYPE,
1468 p_assignment_number OUT NOCOPY per_all_assignments_f.assignment_number%TYPE
1469 )
1470 AS
1471 l_proc proc_name ;
1472 l_payroll_id per_all_assignments_f.payroll_id%TYPE;
1473 l_org_id per_all_assignments_f.organization_id%TYPE;
1474 l_location_id per_all_assignments_f.location_id%TYPE;
1475 l_bg_id per_all_assignments_f.business_group_id%TYPE;
1476 BEGIN
1477 g_debug :=hr_utility.debug_enabled;
1478 if g_debug then
1479 l_proc := g_package
1480 || 'assignment_info';
1481 hr_utility.set_location ( 'Entering '
1482 || l_proc, 10);
1483 end if;
1484 IF (is_assignment (p_tbb_rec))
1485 THEN
1486 p_assignment_id := p_tbb_rec.resource_id;
1487 /* Overkill as we are not using any of the other variables yet
1488 get_assignment_info (
1489 p_assignment_id=> p_tbb_rec.resource_id,
1490 p_effective_date=> TRUNC (p_tbb_rec.start_time),
1491 p_assignment_number=> p_assignment_number,
1492 p_payroll_id=> l_payroll_id,
1493 p_org_id=> l_org_id,
1494 p_location_id=> l_location_id,
1495 p_bg_id=> l_bg_id
1496 ); */
1497 ELSIF (is_person (p_tbb_rec))
1498 THEN
1499 get_primary_assignment_info (p_person_id => p_tbb_rec.resource_id,
1500 p_effective_date => TRUNC (p_tbb_rec.start_time
1501 ),
1502 p_assignment_id => p_assignment_id,
1503 p_assignment_number => p_assignment_number,
1504 p_payroll_id => l_payroll_id,
1505 p_org_id => l_org_id,
1506 p_location_id => l_location_id,
1507 p_bg_id => l_bg_id
1508 );
1509 ELSE
1510 NULL; -- unsupported resource_type!
1511 END IF;
1512
1513 if g_debug then
1514 hr_utility.set_location ( ' OUT p_assignment_id = '
1515 || p_assignment_id,
1516 20
1517 );
1518 hr_utility.set_location ( ' OUT p_assignment_number = '
1519 || p_assignment_number,
1520 30
1521 );
1522 hr_utility.set_location ( 'Leaving '
1523 || l_proc, 100);
1524 end if;
1525 END assignment_info;
1526
1527 FUNCTION attribute_is (
1528 p_attr_rec IN hxc_generic_retrieval_pkg.r_time_attributes,
1529 p_is_what IN hxc_mapping_components.field_name%TYPE,
1530 p_case_sensitive IN BOOLEAN DEFAULT FALSE
1531 )
1532 RETURN BOOLEAN
1533 IS
1534 l_proc proc_name ;
1535 l_it_is BOOLEAN;
1536 BEGIN
1537 g_debug :=hr_utility.debug_enabled;
1538 if g_debug then
1539 l_proc := g_package
1540 || 'attribute_is';
1541 hr_utility.set_location ( 'Entering '
1542 || l_proc, 10);
1543 end if;
1544 IF (p_case_sensitive)
1545 THEN
1546 IF (p_attr_rec.field_name LIKE p_is_what)
1547 THEN
1548 if g_debug then
1549 hr_utility.set_location ( ' (Case Sensitive) Attribute is '
1550 || p_is_what,
1551 20
1552 );
1553 end if;
1554 l_it_is := TRUE;
1555 ELSE
1556 l_it_is := FALSE;
1557 END IF;
1558 ELSE
1559 IF (UPPER (p_attr_rec.field_name) LIKE UPPER (p_is_what))
1560 THEN
1561 if g_debug then
1562 hr_utility.set_location ( ' (Case In-sensitive) Attribute is '
1563 || p_is_what,
1564 30
1565 );
1566 end if;
1567 l_it_is := TRUE;
1568 ELSE
1569 l_it_is := FALSE;
1570 END IF;
1571 END IF;
1572
1573 if g_debug then
1574 hr_utility.set_location ( 'Leaving '
1575 || l_proc, 100);
1576 end if;
1577 RETURN l_it_is;
1578 END attribute_is;
1579
1580 FUNCTION attribute_is_element (
1581 p_attr_rec IN hxc_generic_retrieval_pkg.r_time_attributes
1582 )
1583 RETURN BOOLEAN
1584 IS
1585 l_proc proc_name ;
1586 l_element BOOLEAN;
1587 BEGIN
1588 g_debug :=hr_utility.debug_enabled;
1589 if g_debug then
1590 l_proc := g_package
1591 || 'attribute_is_element';
1592 hr_utility.set_location ( 'Entering '
1593 || l_proc, 10);
1594 end if;
1595 l_element :=
1596 attribute_is (p_attr_rec => p_attr_rec,
1597 p_is_what => g_element_attribute
1598 );
1599 if g_debug then
1600 hr_utility.set_location ( 'Leaving '
1601 || l_proc, 100);
1602 end if;
1603 RETURN l_element;
1604 END attribute_is_element;
1605
1606 FUNCTION attribute_is_cost_segment (
1607 p_attr_rec IN hxc_generic_retrieval_pkg.r_time_attributes
1608 )
1609 RETURN BOOLEAN
1610 IS
1611 l_proc proc_name ;
1612 l_cost_segment BOOLEAN;
1613 BEGIN
1614 g_debug :=hr_utility.debug_enabled;
1615 if g_debug then
1616 l_proc := g_package
1617 || 'attribute_is_cost_segment';
1618 hr_utility.set_location ( 'Entering '
1619 || l_proc, 10);
1620 end if;
1621 l_cost_segment :=
1622 attribute_is (p_attr_rec => p_attr_rec,
1623 p_is_what => g_cost_attribute
1624 || g_wildcard
1625 );
1626 if g_debug then
1627 hr_utility.set_location ( 'Leaving '
1628 || l_proc, 100);
1629 end if;
1630 RETURN l_cost_segment;
1631 END attribute_is_cost_segment;
1632
1633 FUNCTION attribute_is_input_value (
1634 p_attr_rec IN hxc_generic_retrieval_pkg.r_time_attributes
1635 )
1636 RETURN BOOLEAN
1637 IS
1638 l_proc proc_name ;
1639 l_input_value BOOLEAN;
1640 BEGIN
1641 g_debug :=hr_utility.debug_enabled;
1642 if g_debug then
1643 l_proc := g_package
1644 || 'attribute_is_input_value';
1645 hr_utility.set_location ( 'Entering '
1646 || l_proc, 10);
1647 end if;
1648 l_input_value :=
1649 attribute_is (p_attr_rec => p_attr_rec,
1650 p_is_what => g_iv_attribute
1651 || g_wildcard
1652 );
1653 if g_debug then
1654 hr_utility.set_location ( 'Leaving '
1655 || l_proc, 100);
1656 end if;
1657 RETURN l_input_value;
1658 END attribute_is_input_value;
1659
1660 FUNCTION attribute_is_asg_id (
1661 p_attr_rec IN hxc_generic_retrieval_pkg.r_time_attributes
1662 )
1663 RETURN BOOLEAN
1664 IS
1665 l_proc proc_name ;
1666 l_asg_id BOOLEAN;
1667 BEGIN
1668 g_debug :=hr_utility.debug_enabled;
1669 if g_debug then
1670 l_proc := g_package
1671 || 'attribute_is_asg_id';
1672 hr_utility.set_location ( 'Entering '
1673 || l_proc, 10);
1674 end if;
1675 l_asg_id :=
1676 attribute_is (p_attr_rec => p_attr_rec,
1677 p_is_what => g_asg_id_attribute
1678 );
1679 if g_debug then
1680 hr_utility.set_location ( 'Leaving '
1681 || l_proc, 100);
1682 end if;
1683 RETURN l_asg_id;
1684 END attribute_is_asg_id;
1685
1686 FUNCTION attribute_is_asg_num (
1687 p_attr_rec IN hxc_generic_retrieval_pkg.r_time_attributes
1688 )
1689 RETURN BOOLEAN
1690 IS
1691 l_proc proc_name ;
1692 l_asg_num BOOLEAN;
1693 BEGIN
1694 g_debug :=hr_utility.debug_enabled;
1695 if g_debug then
1696 l_proc := g_package
1697 || 'attribute_is_asg_num';
1698 hr_utility.set_location ( 'Entering '
1699 || l_proc, 10);
1700 end if;
1701 l_asg_num :=
1702 attribute_is (p_attr_rec => p_attr_rec,
1703 p_is_what => g_asg_num_attribute
1704 );
1705 if g_debug then
1706 hr_utility.set_location ( 'Leaving '
1707 || l_proc, 100);
1708 end if;
1709 RETURN l_asg_num;
1710 END attribute_is_asg_num;
1711
1712 FUNCTION extract_number (
1713 p_extract_from IN max_varchar,
1714 p_sub_string IN max_varchar,
1715 p_case_sensitive IN BOOLEAN DEFAULT FALSE
1716 )
1717 RETURN PLS_INTEGER
1718 IS
1719 l_proc proc_name ;
1720 l_char_number VARCHAR2 (30);
1721 l_number PLS_INTEGER;
1722 BEGIN
1723 g_debug :=hr_utility.debug_enabled;
1724 if g_debug then
1725 l_proc := g_package
1726 || 'extract_number';
1727 hr_utility.set_location ( 'Entering '
1728 || l_proc, 10);
1729 end if;
1730 IF (p_case_sensitive)
1731 THEN
1732 l_char_number := REPLACE (p_extract_from, p_sub_string);
1733 ELSE
1734 l_char_number :=
1735 REPLACE (UPPER (p_extract_from), UPPER (p_sub_string));
1736 END IF;
1737
1738 IF isnumber (l_char_number)
1739 THEN
1740 l_number := TO_NUMBER (l_char_number);
1741 ELSE
1742 l_number := NULL;
1743 END IF;
1744
1745 if g_debug then
1746 hr_utility.set_location ( ' Returning '
1747 || l_number, 20);
1748 hr_utility.set_location ( 'Leaving '
1749 || l_proc, 30);
1750 end if;
1751 RETURN l_number;
1752 END extract_number;
1753
1754 FUNCTION cost_segment_number (
1755 p_attr_rec IN hxc_generic_retrieval_pkg.r_time_attributes
1756 )
1757 RETURN PLS_INTEGER
1758 IS
1759 l_proc proc_name;
1760
1761 l_char_cost_segment_number VARCHAR2 (30);
1762 l_cost_segment_number PLS_INTEGER;
1763 BEGIN
1764 g_debug :=hr_utility.debug_enabled;
1765 if g_debug then
1766 l_proc := g_package
1767 || 'cost_segment_number';
1768 hr_utility.set_location ( 'Entering '
1769 || l_proc, 10);
1770 end if;
1771 l_cost_segment_number :=
1772 extract_number (p_extract_from => p_attr_rec.field_name,
1773 p_sub_string => g_cost_attribute
1774 );
1775 if g_debug then
1776 hr_utility.set_location ( ' Returning '
1777 || l_cost_segment_number, 20);
1778 hr_utility.set_location ( 'Leaving '
1779 || l_proc, 30);
1780 end if;
1781 RETURN l_cost_segment_number;
1782 END cost_segment_number;
1783
1784 FUNCTION input_value_number (
1785 p_attr_rec IN hxc_generic_retrieval_pkg.r_time_attributes
1786 )
1787 RETURN PLS_INTEGER
1788 IS
1789 l_proc proc_name;
1790
1791 l_char_input_value_number VARCHAR2 (30);
1792 l_input_value_number PLS_INTEGER;
1793 BEGIN
1794 g_debug :=hr_utility.debug_enabled;
1795 if g_debug then
1796 l_proc := g_package
1797 || 'input_value_number';
1798 hr_utility.set_location ( 'Entering '
1799 || l_proc, 10);
1800 end if;
1801 l_input_value_number :=
1802 extract_number (p_extract_from => p_attr_rec.field_name,
1803 p_sub_string => g_iv_attribute
1804 );
1805 if g_debug then
1806 hr_utility.set_location ( ' Returning '
1807 || l_input_value_number, 20);
1808 hr_utility.set_location ( 'Leaving '
1809 || l_proc, 30);
1810 end if;
1811 RETURN l_input_value_number;
1812 END input_value_number;
1813
1814 FUNCTION element_type_id (
1815 p_attr_rec IN hxc_generic_retrieval_pkg.r_time_attributes
1816 )
1817 RETURN pay_element_types_f.element_type_id%TYPE
1818 IS
1819 l_proc proc_name ;
1820 l_char_element_type_id VARCHAR2 (30);
1821 l_element_type_id pay_element_types_f.element_type_id%TYPE;
1822 BEGIN
1823 g_debug :=hr_utility.debug_enabled;
1824 if g_debug then
1825 l_proc := g_package
1826 || 'element_type_id';
1827 hr_utility.set_location ( 'Entering '
1828 || l_proc, 10);
1829 end if;
1830 l_char_element_type_id :=
1831 REPLACE (UPPER (p_attr_rec.VALUE), c_element_context_prefix);
1832
1833 IF (isnumber (l_char_element_type_id))
1834 THEN
1835 l_element_type_id := TO_NUMBER (l_char_element_type_id);
1836 ELSE
1837 l_element_type_id := NULL;
1838 END IF;
1839
1840 if g_debug then
1841 hr_utility.set_location ( ' Returning '
1842 || l_element_type_id, 20);
1843 hr_utility.set_location ( 'Leaving '
1844 || l_proc, 30);
1845 end if;
1846 RETURN l_element_type_id;
1847 END element_type_id;
1848
1849 FUNCTION element_flex_context_code (
1850 p_element_type_id IN pay_element_types_f.element_type_id%TYPE
1851 )
1852 RETURN fnd_descr_flex_column_usages.descriptive_flex_context_code%TYPE
1853 IS
1854 l_proc proc_name;
1855
1856 l_element_flex_context_code fnd_descr_flex_column_usages.descriptive_flex_context_code%TYPE;
1857 BEGIN
1858 g_debug :=hr_utility.debug_enabled;
1859 if g_debug then
1860 l_proc := g_package
1861 || 'element_flex_context_code';
1862 hr_utility.set_location ( 'Entering '
1863 || l_proc, 10);
1864 end if;
1865 IF (p_element_type_id IS NOT NULL)
1866 THEN
1867 l_element_flex_context_code :=
1868 c_element_context_prefix
1869 || p_element_type_id;
1870 ELSE
1871 l_element_flex_context_code := NULL;
1872 END IF;
1873
1874 if g_debug then
1875 hr_utility.set_location ( ' Returning '
1876 || l_element_flex_context_code,
1877 20
1878 );
1879 hr_utility.set_location ( 'Leaving '
1880 || l_proc, 30);
1881 end if;
1882 RETURN l_element_flex_context_code;
1883 END element_flex_context_code;
1884
1885 FUNCTION element_name (
1886 p_ele_type_id IN pay_element_types_f.element_type_id%TYPE,
1887 p_effective_date IN pay_element_types_f.effective_start_date%TYPE
1888 )
1889 RETURN pay_element_types_f.element_name%TYPE
1890 IS
1891 l_proc proc_name ;
1892 CURSOR csr_element_name (
1893 p_ele_type_id pay_element_types_f.element_type_id%TYPE,
1894 p_effective_date pay_element_types_f.effective_start_date%TYPE
1895 )
1896 IS
1897 SELECT petft.element_name
1898 FROM pay_element_types_f petf, pay_element_types_f_tl petft
1899 WHERE petf.element_type_id = p_ele_type_id
1900 AND petft.element_type_id = petf.element_type_id
1901 AND USERENV ('LANG') = petft.LANGUAGE
1902 AND p_effective_date BETWEEN petf.effective_start_date
1903 AND petf.effective_end_date;
1904 l_element_name pay_element_types_f.element_name%TYPE;
1905 BEGIN
1906 g_debug :=hr_utility.debug_enabled;
1907 if g_debug then
1908 l_proc := g_package
1909 || 'element_name';
1910 hr_utility.set_location ( 'Entering '
1911 || l_proc, 10);
1912 end if;
1913 OPEN csr_element_name (p_ele_type_id, p_effective_date);
1914 FETCH csr_element_name INTO l_element_name;
1915 CLOSE csr_element_name;
1916 if g_debug then
1917 hr_utility.set_location ( ' Returning '
1918 || l_element_name, 20);
1919 hr_utility.set_location ( 'Leaving '
1920 || l_proc, 30);
1921 end if;
1922 RETURN l_element_name;
1923 END element_name;
1924
1925
1926 FUNCTION hours_worked (
1927 p_detail_tbb IN hxc_generic_retrieval_pkg.r_building_blocks
1928 )
1929 RETURN NUMBER
1930 AS
1931 l_proc proc_name ;
1932 l_hours_worked NUMBER;
1933 BEGIN
1934 g_debug :=hr_utility.debug_enabled;
1935 if g_debug then
1936 l_proc := g_package
1937 || 'hours_worked';
1938 hr_utility.set_location ( 'Entering '
1939 || l_proc, 10);
1940 end if;
1941 IF (p_detail_tbb.TYPE = hxc_timecard.c_measure_type)
1942 THEN
1943 l_hours_worked := p_detail_tbb.measure;
1944 ELSE
1945 l_hours_worked :=
1946 ( p_detail_tbb.stop_time
1947 - p_detail_tbb.start_time
1948 )
1949 * 24;
1950 END IF;
1951
1952 if g_debug then
1953 hr_utility.set_location ( ' Returning '
1954 || l_hours_worked, 20);
1955 hr_utility.set_location ( 'Leaving '
1956 || l_proc, 30);
1957 end if;
1958 RETURN l_hours_worked;
1959 END hours_worked;
1960
1961 FUNCTION element_type_ivs (
1962 p_element_type_id IN pay_element_types_f.element_type_id%TYPE,
1963 p_effective_date IN pay_element_types_f.effective_start_date%TYPE
1964 )
1965 RETURN input_value_name_table
1966 AS
1967 l_proc proc_name ;
1968
1969 CURSOR csr_element_type_ivs (
1970 p_element_type_id pay_element_types_f.element_type_id%TYPE,
1971 p_effective_date pay_element_types_f.effective_start_date%TYPE
1972 )
1973 IS
1974 SELECT piv.NAME
1975 FROM pay_input_values_f piv
1976 WHERE piv.element_type_id = p_element_type_id
1977 AND p_effective_date BETWEEN piv.effective_start_date
1978 AND piv.effective_end_date
1979 ORDER BY piv.display_sequence, piv.NAME;
1980
1981 l_element_type_ivs input_value_name_table;
1982 BEGIN
1983 g_debug :=hr_utility.debug_enabled;
1984 if g_debug then
1985 l_proc := g_package
1986 || 'element_type_ivs';
1987 hr_utility.set_location ( 'Entering '
1988 || l_proc, 10);
1989 end if;
1990 IF (p_element_type_id = current_element)
1991 THEN
1992 if g_debug then
1993 hr_utility.set_location (' retrieving from cache', 20);
1994 end if;
1995 l_element_type_ivs := g_element_type_ivs;
1996 ELSE
1997 if g_debug then
1998 hr_utility.set_location (' retrieving from DB', 30);
1999 end if;
2000
2001 IF (csr_element_type_ivs%ISOPEN)
2002 THEN
2003 CLOSE csr_element_type_ivs;
2004 END IF;
2005
2006 OPEN csr_element_type_ivs (p_element_type_id, p_effective_date);
2007 FETCH csr_element_type_ivs BULK COLLECT INTO l_element_type_ivs;
2008 CLOSE csr_element_type_ivs;
2009 g_element_type_ivs := l_element_type_ivs;
2010 set_current_element (p_element_type_id);
2011 END IF;
2012
2013 if g_debug then
2014 hr_utility.set_location ( ' returning # IVs'
2015 || l_element_type_ivs.COUNT,
2016 40
2017 );
2018 hr_utility.set_location ( 'Leaving '
2019 || l_proc, 100);
2020 end if;
2021 RETURN l_element_type_ivs;
2022 END element_type_ivs;
2023
2024 FUNCTION ddf_input_value_name (
2025 p_element_type_id IN pay_element_types_f.element_type_id%TYPE,
2026 p_attr_rec IN hxc_generic_retrieval_pkg.r_time_attributes
2027 )
2028 RETURN fnd_descr_flex_column_usages.end_user_column_name%TYPE
2029 AS
2030 l_proc proc_name;
2031
2032
2033 CURSOR csr_ddf_input_value_name (
2034 p_context fnd_descr_flex_column_usages.descriptive_flex_context_code%TYPE,
2035 p_field_name hxc_mapping_components.field_name%TYPE
2036 )
2037 IS
2038 SELECT fdfcu.end_user_column_name
2039 FROM fnd_descr_flex_column_usages fdfcu,
2040 hxc_mapping_components mpc
2041 WHERE fdfcu.application_id = g_hxc_app_id
2042 AND fdfcu.descriptive_flexfield_name = g_otl_info_types_ddf
2043 AND fdfcu.descriptive_flex_context_code = p_context
2044 AND fdfcu.application_column_name = mpc.SEGMENT
2045 AND UPPER (mpc.field_name) = UPPER (p_field_name);
2046
2047 l_ddf_input_value_name fnd_descr_flex_column_usages.end_user_column_name%TYPE;
2048 BEGIN
2049 g_debug :=hr_utility.debug_enabled;
2050 if g_debug then
2051 l_proc := g_package
2052 || 'ddf_input_value_name';
2053 hr_utility.set_location ( 'Entering '
2054 || l_proc, 10);
2055 hr_utility.set_location ( ' IN p_element_type_id '
2056 || p_element_type_id,
2057 20
2058 );
2059 hr_utility.set_location ( ' IN p_attr_rec.field_name '
2060 || p_attr_rec.field_name,
2061 30
2062 );
2063 end if;
2064 IF (csr_ddf_input_value_name%ISOPEN)
2065 THEN
2066 CLOSE csr_ddf_input_value_name;
2067 END IF;
2068
2069 OPEN csr_ddf_input_value_name (element_flex_context_code (p_element_type_id
2070 ),
2071 p_attr_rec.field_name
2072 );
2073 FETCH csr_ddf_input_value_name INTO l_ddf_input_value_name;
2074 CLOSE csr_ddf_input_value_name;
2075 if g_debug then
2076 hr_utility.set_location ( ' returning '
2077 || l_ddf_input_value_name, 100);
2078 hr_utility.set_location ( 'Leaving '
2079 || l_proc, 110);
2080 end if;
2081 RETURN l_ddf_input_value_name;
2082 END ddf_input_value_name;
2083
2084 FUNCTION accr_plan_added_date_ivs (
2085 p_accrual_plan_id IN pay_accrual_plans.accrual_plan_id%TYPE,
2086 p_element_type_id IN pay_element_types_f.element_type_id%TYPE,
2087 p_effective_date IN pay_element_types_f.effective_start_date%TYPE
2088 )
2089 RETURN input_value_name_table
2090 AS
2091 l_proc proc_name ;
2092
2093 -- This cursor returns IVs of an element that have been ADDED to the
2094 -- net calculation rules of an accrual plan as Date Input Values. It
2095 -- ignores the 2 net calculation rules (one Substract and one Add) that
2096 -- get created automatically when submitting the Accrual Plan. It can do
2097 -- this because these 2 IVs IDs get denormalized on the Accrual Plan table
2098 -- as pto_input_value_id (Substract) and co_input_value_id (Add). Probably
2099 -- The pto_input_value_id can even be ignored as it never seems to have a
2100 -- Date Input Value, so that will get filtered anyway by
2101 -- pncr.date_input_value_id = piv.input_value_id, but I will leave it for
2102 -- now.
2103 CURSOR csr_accrual_plan_ivs (
2104 p_accrual_plan_id pay_accrual_plans.accrual_plan_id%TYPE,
2105 p_effective_date pay_element_types_f.effective_start_date%TYPE,
2106 p_element_type_id pay_element_types_f.element_type_id%TYPE
2107 )
2108 IS
2109 SELECT DISTINCT piv.NAME
2110 FROM pay_input_values_f piv,
2111 pay_accrual_plans pap,
2112 pay_net_calculation_rules pncr
2113 WHERE piv.element_type_id = p_element_type_id
2114 AND p_effective_date BETWEEN piv.effective_start_date
2115 AND piv.effective_end_date
2116 AND pncr.date_input_value_id = piv.input_value_id
2117 AND pncr.input_value_id <> pap.pto_input_value_id
2118 AND pncr.input_value_id <> pap.co_input_value_id
2119 AND pncr.accrual_plan_id = pap.accrual_plan_id
2120 AND pap.accrual_plan_id = p_accrual_plan_id;
2121
2122 l_accrual_plan_ivs input_value_name_table;
2123 BEGIN
2124
2125 if g_debug then
2126 l_proc := g_package
2127 || 'accrual_plan_added_ivs';
2128 hr_utility.set_location ( 'Entering '
2129 || l_proc, 10);
2130 end if;
2131 IF (csr_accrual_plan_ivs%ISOPEN)
2132 THEN
2133 CLOSE csr_accrual_plan_ivs;
2134 END IF;
2135
2136 OPEN csr_accrual_plan_ivs (p_accrual_plan_id,
2137 p_effective_date,
2138 p_element_type_id
2139 );
2140 FETCH csr_accrual_plan_ivs BULK COLLECT INTO l_accrual_plan_ivs;
2141 CLOSE csr_accrual_plan_ivs;
2142 if g_debug then
2143 hr_utility.set_location ( 'Leaving '
2144 || l_proc, 100);
2145 end if;
2146 RETURN l_accrual_plan_ivs;
2147 END accr_plan_added_date_ivs;
2148
2149 FUNCTION accrual_plan_ids (
2150 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
2151 p_effective_date IN pay_element_types_f.effective_start_date%TYPE
2152 )
2153 RETURN accrual_plan_id_table
2154 AS
2155 l_proc proc_name ;
2156
2157 CURSOR csr_accrual_plan_ids (
2158 p_assignment_id per_all_assignments_f.assignment_id%TYPE,
2159 p_effective_date pay_element_types_f.effective_start_date%TYPE
2160 )
2161 IS
2162 SELECT pap.accrual_plan_id
2163 FROM pay_accrual_plans pap,
2164 pay_element_links_f pelf,
2165 pay_element_entries_f peef
2166 WHERE pelf.element_type_id = pap.accrual_plan_element_type_id
2167 AND p_effective_date BETWEEN pelf.effective_start_date
2168 AND pelf.effective_end_date
2169 AND peef.element_link_id = pelf.element_link_id
2170 AND peef.assignment_id = p_assignment_id
2171 AND p_effective_date BETWEEN peef.effective_start_date
2172 AND peef.effective_end_date
2173 ORDER BY accrual_plan_id;
2174
2175 l_accrual_plan_ids accrual_plan_id_table;
2176 BEGIN
2177 g_debug :=hr_utility.debug_enabled;
2178 if g_debug then
2179 l_proc := g_package
2180 || 'accrual_plan_ids';
2181 hr_utility.set_location ( 'Entering '
2182 || l_proc, 10);
2183 end if;
2184 IF (csr_accrual_plan_ids%ISOPEN)
2185 THEN
2186 CLOSE csr_accrual_plan_ids;
2187 END IF;
2188
2189 OPEN csr_accrual_plan_ids (p_assignment_id, p_effective_date);
2190 FETCH csr_accrual_plan_ids BULK COLLECT INTO l_accrual_plan_ids;
2191 CLOSE csr_accrual_plan_ids;
2192 if g_debug then
2193 hr_utility.set_location ( 'Leaving '
2194 || l_proc, 100);
2195 end if;
2196 RETURN l_accrual_plan_ids;
2197 END accrual_plan_ids;
2198
2199 FUNCTION accrual_plan_exists (
2200 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
2201 p_effective_date IN pay_element_types_f.effective_start_date%TYPE,
2202 p_accrual_plan_id IN pay_accrual_plans.accrual_plan_id%TYPE
2203 )
2204 RETURN BOOLEAN
2205 AS
2206 l_proc proc_name ;
2207 l_accrual_plan_ids accrual_plan_id_table;
2208 l_accrual_plan_index PLS_INTEGER;
2209 l_accrual_plan_exists BOOLEAN := FALSE;
2210 BEGIN
2211
2212 if g_debug then
2213 l_proc := g_package
2214 || 'accrual_plan_exists';
2215 hr_utility.set_location ( 'Entering '
2216 || l_proc, 10);
2217 end if;
2218 l_accrual_plan_ids :=
2219 accrual_plan_ids (p_assignment_id => p_assignment_id,
2220 p_effective_date => p_effective_date
2221 );
2222 l_accrual_plan_index := l_accrual_plan_ids.FIRST;
2223
2224 LOOP
2225 EXIT WHEN ( NOT (l_accrual_plan_ids.EXISTS (l_accrual_plan_index))
2226 OR (l_accrual_plan_exists)
2227 );
2228
2229 IF (l_accrual_plan_ids (l_accrual_plan_index) = p_accrual_plan_id)
2230 THEN
2231 if g_debug then
2232 hr_utility.set_location ( ' Accrual Plan exists'
2233 || l_proc, 20);
2234 end if;
2235 l_accrual_plan_exists := TRUE;
2236 END IF;
2237
2238 l_accrual_plan_index :=
2239 l_accrual_plan_ids.NEXT (l_accrual_plan_index);
2240 END LOOP;
2241
2242 if g_debug then
2243 hr_utility.set_location ( 'Leaving '
2244 || l_proc, 100);
2245 end if;
2246 RETURN l_accrual_plan_exists;
2247 END accrual_plan_exists;
2248
2249 PROCEDURE assign_iv (
2250 p_iv_seq IN NUMBER,
2251 p_value IN VARCHAR2,
2252 p_bee_rec IN OUT NOCOPY bee_rec
2253 )
2254 AS
2255 BEGIN
2256 IF (p_iv_seq = 1)
2257 THEN
2258 p_bee_rec.pay_batch_line.value_1 := p_value;
2259 ELSIF (p_iv_seq = 2)
2260 THEN
2261 p_bee_rec.pay_batch_line.value_2 := p_value;
2262 ELSIF (p_iv_seq = 3)
2263 THEN
2264 p_bee_rec.pay_batch_line.value_3 := p_value;
2265 ELSIF (p_iv_seq = 4)
2266 THEN
2267 p_bee_rec.pay_batch_line.value_4 := p_value;
2268 ELSIF (p_iv_seq = 5)
2269 THEN
2270 p_bee_rec.pay_batch_line.value_5 := p_value;
2271 ELSIF (p_iv_seq = 6)
2272 THEN
2273 p_bee_rec.pay_batch_line.value_6 := p_value;
2274 ELSIF (p_iv_seq = 7)
2275 THEN
2276 p_bee_rec.pay_batch_line.value_7 := p_value;
2277 ELSIF (p_iv_seq = 8)
2278 THEN
2279 p_bee_rec.pay_batch_line.value_8 := p_value;
2280 ELSIF (p_iv_seq = 9)
2281 THEN
2282 p_bee_rec.pay_batch_line.value_9 := p_value;
2283 ELSIF (p_iv_seq = 10)
2284 THEN
2285 p_bee_rec.pay_batch_line.value_10 := p_value;
2286 ELSIF (p_iv_seq = 11)
2287 THEN
2288 p_bee_rec.pay_batch_line.value_11 := p_value;
2289 ELSIF (p_iv_seq = 12)
2290 THEN
2291 p_bee_rec.pay_batch_line.value_12 := p_value;
2292 ELSIF (p_iv_seq = 13)
2293 THEN
2294 p_bee_rec.pay_batch_line.value_13 := p_value;
2295 ELSIF (p_iv_seq = 14)
2296 THEN
2297 p_bee_rec.pay_batch_line.value_14 := p_value;
2298 ELSIF (p_iv_seq = 15)
2299 THEN
2300 p_bee_rec.pay_batch_line.value_15 := p_value;
2301 END IF;
2302 END assign_iv;
2303
2304 PROCEDURE convert_attr_to_ivs (
2305 p_attr_rec IN hxc_generic_retrieval_pkg.r_time_attributes,
2306 p_element_type_id IN pay_element_types_f.element_type_id%TYPE,
2307 p_effective_date IN pay_element_types_f.effective_start_date%TYPE,
2308 p_bee_rec IN OUT NOCOPY bee_rec
2309 )
2310 AS
2311 l_proc proc_name ;
2312 l_iv_table input_value_name_table;
2313 l_iv_tbl_idx PLS_INTEGER;
2314 l_ddf_iv_name fnd_descr_flex_column_usages.end_user_column_name%TYPE;
2315 l_iv_found BOOLEAN := FALSE;
2316 l_iv_seq PLS_INTEGER;
2317 BEGIN
2318 g_debug :=hr_utility.debug_enabled;
2319 if g_debug then
2320 l_proc := g_package
2321 || 'convert_attr_to_ivs';
2322 hr_utility.set_location ( 'Entering '
2323 || l_proc, 10);
2324 end if;
2325 IF (attribute_is_input_value (p_attr_rec))
2326 THEN
2327 l_ddf_iv_name :=
2328 ddf_input_value_name (p_attr_rec => p_attr_rec,
2329 p_element_type_id => p_element_type_id
2330 );
2331 l_iv_table :=
2332 element_type_ivs (p_element_type_id => p_element_type_id,
2333 p_effective_date => p_effective_date
2334 );
2335 l_iv_tbl_idx := l_iv_table.FIRST;
2336
2337 <<compare_ivs>>
2338 LOOP
2339 EXIT compare_ivs WHEN ( (NOT l_iv_table.EXISTS (l_iv_tbl_idx))
2340 OR (l_iv_found)
2341 );
2342
2343 IF (l_iv_table (l_iv_tbl_idx) = l_ddf_iv_name)
2344 THEN
2345 if g_debug then
2346 hr_utility.set_location ( ' iv found, seq = '
2347 || l_iv_tbl_idx,
2348 20
2349 );
2350 end if;
2351 l_iv_found := TRUE;
2352 l_iv_seq := l_iv_tbl_idx;
2353 END IF;
2354
2355 l_iv_tbl_idx := l_iv_table.NEXT (l_iv_tbl_idx);
2356 END LOOP compare_ivs;
2357
2358 IF (l_iv_found)
2359 THEN
2360 assign_iv (p_iv_seq => l_iv_seq,
2361 p_value => p_attr_rec.VALUE,
2362 p_bee_rec => p_bee_rec
2363 );
2364 END IF;
2365 ELSE
2366 NULL; -- Error, you should not have called this procedure
2367 END IF;
2368
2369 if g_debug then
2370 hr_utility.set_location ( 'Leaving '
2371 || l_proc, 100);
2372 end if;
2373 END convert_attr_to_ivs;
2374
2375 PROCEDURE convert_attr_to_costsegment (
2376 p_attr_rec IN hxc_generic_retrieval_pkg.r_time_attributes,
2377 p_cost_flex_id IN per_business_groups_perf.cost_allocation_structure%TYPE,
2378 p_bee_rec IN OUT NOCOPY bee_rec
2379 )
2380 AS
2381 l_proc proc_name;
2382
2383 l_cost_segment_number PLS_INTEGER;
2384 l_value pay_batch_lines.segment1%TYPE;
2385 BEGIN
2386 g_debug :=hr_utility.debug_enabled;
2387 if g_debug then
2388 l_proc := g_package
2389 || 'convert_attr_to_costsegment';
2390 hr_utility.set_location ( 'Entering '
2391 || l_proc, 10);
2392 end if;
2393 IF (attribute_is_cost_segment (p_attr_rec))
2394 THEN
2395 l_cost_segment_number := cost_segment_number (p_attr_rec);
2396 l_value :=
2397 costflex_value (p_id_flex_num => p_cost_flex_id,
2398 p_segment_name => g_segment
2399 || l_cost_segment_number,
2400 p_flex_value_id => p_attr_rec.VALUE
2401 );
2402
2403 IF (l_cost_segment_number = 1)
2404 THEN
2405 p_bee_rec.pay_batch_line.segment1 := l_value;
2406 ELSIF (l_cost_segment_number = 2)
2407 THEN
2408 p_bee_rec.pay_batch_line.segment2 := l_value;
2409 ELSIF (l_cost_segment_number = 3)
2410 THEN
2411 p_bee_rec.pay_batch_line.segment3 := l_value;
2412 ELSIF (l_cost_segment_number = 4)
2413 THEN
2414 p_bee_rec.pay_batch_line.segment4 := l_value;
2415 ELSIF (l_cost_segment_number = 5)
2416 THEN
2417 p_bee_rec.pay_batch_line.segment5 := l_value;
2418 ELSIF (l_cost_segment_number = 6)
2419 THEN
2420 p_bee_rec.pay_batch_line.segment6 := l_value;
2421 ELSIF (l_cost_segment_number = 7)
2422 THEN
2423 p_bee_rec.pay_batch_line.segment7 := l_value;
2424 ELSIF (l_cost_segment_number = 8)
2425 THEN
2426 p_bee_rec.pay_batch_line.segment8 := l_value;
2427 ELSIF (l_cost_segment_number = 9)
2428 THEN
2429 p_bee_rec.pay_batch_line.segment9 := l_value;
2430 ELSIF (l_cost_segment_number = 10)
2431 THEN
2432 p_bee_rec.pay_batch_line.segment10 := l_value;
2433 ELSIF (l_cost_segment_number = 11)
2434 THEN
2435 p_bee_rec.pay_batch_line.segment11 := l_value;
2436 ELSIF (l_cost_segment_number = 12)
2437 THEN
2438 p_bee_rec.pay_batch_line.segment12 := l_value;
2439 ELSIF (l_cost_segment_number = 13)
2440 THEN
2441 p_bee_rec.pay_batch_line.segment13 := l_value;
2442 ELSIF (l_cost_segment_number = 14)
2443 THEN
2444 p_bee_rec.pay_batch_line.segment14 := l_value;
2445 ELSIF (l_cost_segment_number = 15)
2446 THEN
2447 p_bee_rec.pay_batch_line.segment15 := l_value;
2448 ELSIF (l_cost_segment_number = 16)
2449 THEN
2450 p_bee_rec.pay_batch_line.segment16 := l_value;
2451 ELSIF (l_cost_segment_number = 17)
2452 THEN
2453 p_bee_rec.pay_batch_line.segment17 := l_value;
2454 ELSIF (l_cost_segment_number = 18)
2455 THEN
2456 p_bee_rec.pay_batch_line.segment18 := l_value;
2457 ELSIF (l_cost_segment_number = 19)
2458 THEN
2459 p_bee_rec.pay_batch_line.segment19 := l_value;
2460 ELSIF (l_cost_segment_number = 20)
2461 THEN
2462 p_bee_rec.pay_batch_line.segment20 := l_value;
2463 ELSIF (l_cost_segment_number = 21)
2464 THEN
2465 p_bee_rec.pay_batch_line.segment21 := l_value;
2466 ELSIF (l_cost_segment_number = 22)
2467 THEN
2468 p_bee_rec.pay_batch_line.segment22 := l_value;
2469 ELSIF (l_cost_segment_number = 23)
2470 THEN
2471 p_bee_rec.pay_batch_line.segment23 := l_value;
2472 ELSIF (l_cost_segment_number = 24)
2473 THEN
2474 p_bee_rec.pay_batch_line.segment24 := l_value;
2475 ELSIF (l_cost_segment_number = 25)
2476 THEN
2477 p_bee_rec.pay_batch_line.segment25 := l_value;
2478 ELSIF (l_cost_segment_number = 26)
2479 THEN
2480 p_bee_rec.pay_batch_line.segment26 := l_value;
2481 ELSIF (l_cost_segment_number = 27)
2482 THEN
2483 p_bee_rec.pay_batch_line.segment27 := l_value;
2484 ELSIF (l_cost_segment_number = 28)
2485 THEN
2486 p_bee_rec.pay_batch_line.segment28 := l_value;
2487 ELSIF (l_cost_segment_number = 29)
2488 THEN
2489 p_bee_rec.pay_batch_line.segment29 := l_value;
2490 ELSIF (l_cost_segment_number = 30)
2491 THEN
2492 p_bee_rec.pay_batch_line.segment30 := l_value;
2493 END IF;
2494 ELSE
2495 NULL; -- you should not have called this procedure
2496 END IF;
2497
2498 if g_debug then
2499 hr_utility.set_location ( 'Leaving '
2500 || l_proc, 100);
2501 end if;
2502 END convert_attr_to_costsegment;
2503
2504 FUNCTION translated_iv (
2505 p_iv_name IN hr_lookups.meaning%TYPE,
2506 p_date_active IN hr_lookups.start_date_active%TYPE
2507 )
2508 RETURN hr_lookups.lookup_code%TYPE
2509 IS
2510 l_proc proc_name ;
2511
2512 -- Bug 14081101
2513 -- Modified the below cursor to look from fnd_lookup_values
2514 -- instead of hr_lookups
2515 /*
2516 CURSOR csr_translated_info (
2517 p_meaning hr_lookups.meaning%TYPE,
2518 p_date_active hr_lookups.start_date_active%TYPE
2519 )
2520 IS
2521 SELECT lookup_code, start_date_active, end_date_active
2522 FROM hr_lookups
2523 WHERE meaning = p_meaning
2524 AND lookup_type = g_element_iv_translations
2525 AND application_id = g_per_app_id
2526 AND enabled_flag = g_lookup_enabled
2527 AND p_date_active BETWEEN NVL (start_date_active, p_date_active)
2528 AND NVL (end_date_active, p_date_active);
2529 */
2530
2531 CURSOR csr_translated_info (
2532 p_meaning hr_lookups.meaning%TYPE,
2533 p_date_active hr_lookups.start_date_active%TYPE
2534 )
2535 IS
2536 SELECT lookup_code, start_date_active, end_date_active
2537 FROM fnd_lookup_values
2538 WHERE meaning = p_meaning
2539 AND lookup_type = g_element_iv_translations
2540 --AND application_id = g_per_app_id
2541 AND enabled_flag = g_lookup_enabled
2542 AND p_date_active BETWEEN NVL (start_date_active, p_date_active)
2543 AND NVL (end_date_active, p_date_active);
2544
2545
2546 l_translated_info csr_translated_info%ROWTYPE;
2547 l_translated_iv hr_lookups.lookup_code%TYPE;
2548 l_hashed_id PLS_INTEGER;
2549 l_found_in_cache BOOLEAN := FALSE;
2550 BEGIN
2551 g_debug :=hr_utility.debug_enabled;
2552
2553 -- Bug 13705204
2554 IF g_debug
2555 THEN
2556 hr_utility.trace('Entering Translated _iv for '||p_iv_name);
2557 hr_utility.trace('p_date_active '||p_date_active);
2558 END IF;
2559 if g_debug then
2560 l_proc := g_package
2561 || 'translated_iv';
2562 hr_utility.set_location ( 'Entering '
2563 || l_proc, 10);
2564 end if;
2565 l_hashed_id := hashval (p_iv_name);
2566
2567 IF ((caching) AND (g_iv_translations.EXISTS (l_hashed_id)))
2568 THEN
2569 IF g_debug
2570 THEN
2571 hr_utility.trace('Checking from cache ');
2572 END IF;
2573
2574 IF (p_date_active
2575 BETWEEN NVL (g_iv_translations (l_hashed_id).start_date_active,
2576 p_date_active
2577 )
2578 AND NVL (g_iv_translations (l_hashed_id).end_date_active,
2579 p_date_active
2580 )
2581 )
2582 THEN
2583 l_translated_iv := g_iv_translations (l_hashed_id).lookup_code;
2584 IF g_debug
2585 THEN
2586 hr_utility.trace(' Got the value from cache '||l_translated_iv);
2587 END IF;
2588 l_found_in_cache := TRUE;
2589
2590 ELSE
2591 -- Bug 13705204
2592 -- Control is here means that the lookup code did not exist in the Table,
2593 -- or is not active. But we still know that we checked the cache, so setting the flag right.
2594 IF g_debug
2595 THEN
2596 hr_utility.trace(' Cache exists, but value not matching ');
2597 END IF;
2598 l_found_in_cache := TRUE;
2599
2600 END IF;
2601 END IF;
2602
2603 IF (NOT l_found_in_cache)
2604 THEN
2605 IF (csr_translated_info%ISOPEN)
2606 THEN
2607 CLOSE csr_translated_info;
2608 END IF;
2609
2610 OPEN csr_translated_info (p_iv_name, p_date_active);
2611 FETCH csr_translated_info INTO l_translated_info;
2612
2613 IF (csr_translated_info%FOUND)
2614 THEN
2615 l_translated_iv := l_translated_info.lookup_code;
2616
2617 IF (caching)
2618 THEN
2619
2620 -- Bug 13705204
2621 IF g_debug
2622 THEN
2623 hr_utility.trace('Storing Value cache ');
2624 END IF;
2625 g_iv_translations (l_hashed_id).lookup_code :=
2626 l_translated_info.lookup_code;
2627 g_iv_translations (l_hashed_id).start_date_active :=
2628 l_translated_info.start_date_active;
2629 g_iv_translations (l_hashed_id).end_date_active :=
2630 l_translated_info.end_date_active;
2631 END IF;
2632 ELSE
2633 -- Bug 13705204
2634 -- We did not get the values from the SQL, but we once validated the values anyway.
2635 -- So when this IV name comes up need not query again and again for a value that is not going to return.
2636 -- Marking an empty cache here. Note the start and stop times with end of time and start of time
2637 -- respectively. This so that the above condition if p_date b/w start and end never turns true.
2638 IF g_debug
2639 THEN
2640 hr_utility.trace('Storing empty cache ');
2641 END IF;
2642
2643 g_iv_translations (l_hashed_id).lookup_code :=
2644 NULL;
2645 g_iv_translations (l_hashed_id).start_date_active :=
2646 hr_general.end_of_time;
2647 g_iv_translations (l_hashed_id).end_date_active :=
2648 hr_general.start_of_time;
2649
2650 END IF;
2651
2652 CLOSE csr_translated_info;
2653 END IF;
2654
2655 if g_debug then
2656 hr_utility.set_location ( ' returning '
2657 || l_translated_iv, 20);
2658 hr_utility.set_location ( 'Leaving '
2659 || l_proc, 100);
2660 end if;
2661 RETURN l_translated_iv;
2662 END translated_iv;
2663
2664
2665
2666 PROCEDURE hours_iv_position (
2667 p_element_type_id IN pay_element_types_f.element_type_id%TYPE,
2668 p_effective_date IN pay_element_types_f.effective_start_date%TYPE,
2669 p_hours_iv_position OUT NOCOPY PLS_INTEGER,
2670 p_jurisdiction_iv_position OUT NOCOPY PLS_INTEGER,
2671 p_iv_type IN VARCHAR2
2672 )
2673 AS
2674 l_proc proc_name ;
2675 l_iv_table input_value_name_table;
2676 l_hours_found BOOLEAN := FALSE;
2677 l_jurisdiction_found BOOLEAN := FALSE;
2678 l_iv_tbl_idx PLS_INTEGER;
2679 BEGIN
2680 g_debug :=hr_utility.debug_enabled;
2681 if g_debug then
2682 l_proc := g_package
2683 || 'hours_iv_position';
2684 hr_utility.set_location ( 'Entering '
2685 || l_proc, 10);
2686 end if;
2687 p_hours_iv_position := NULL;
2688 p_jurisdiction_iv_position := NULL;
2689
2690
2691 l_iv_table :=
2692 element_type_ivs (p_element_type_id => p_element_type_id,
2693 p_effective_date => p_effective_date
2694 );
2695 l_iv_tbl_idx := l_iv_table.FIRST;
2696
2697 IF(p_iv_type = g_hours_iv) THEN
2698
2699 <<find_hours_iv>>
2700 LOOP
2701 EXIT find_hours_iv WHEN ( (NOT l_iv_table.EXISTS (l_iv_tbl_idx))
2702 OR (l_hours_found)
2703 );
2704 if g_debug then
2705 hr_utility.trace('in find_hours_iv');
2706 end if;
2707 IF (translated_iv (p_iv_name => l_iv_table (l_iv_tbl_idx),
2708 p_date_active => p_effective_date
2709 ) = g_hours_iv
2710 )
2711 THEN
2712 if g_debug then
2713 hr_utility.set_location ( ' "HOURS" IV found, seq = '
2714 || l_iv_tbl_idx,
2715 20
2716 );
2717 end if;
2718 l_hours_found := TRUE;
2719 p_hours_iv_position := l_iv_tbl_idx;
2720 END IF;
2721
2722 l_iv_tbl_idx := l_iv_table.NEXT (l_iv_tbl_idx);
2723 END LOOP find_hours_iv;
2724
2725 ELSIF(p_iv_type = g_jurisdiction_iv)THEN
2726
2727 <<find_jurisdiction_iv>>
2728 LOOP
2729 EXIT find_jurisdiction_iv WHEN ( (NOT l_iv_table.EXISTS (l_iv_tbl_idx))
2730 OR (l_jurisdiction_found)
2731 );
2732 if g_debug then
2733 hr_utility.trace('find_jurisdiction_iv');
2734 end if;
2735 IF (translated_iv (p_iv_name => l_iv_table (l_iv_tbl_idx),
2736 p_date_active => p_effective_date
2737 ) = g_jurisdiction_iv
2738 )
2739 THEN
2740 if g_debug then
2741 hr_utility.set_location ( ' "HOURS" IV found, seq = '
2742 || l_iv_tbl_idx,
2743 20
2744 );
2745 end if;
2746 l_jurisdiction_found := TRUE;
2747 p_jurisdiction_iv_position := l_iv_tbl_idx;
2748 END IF;
2749
2750 l_iv_tbl_idx := l_iv_table.NEXT (l_iv_tbl_idx);
2751 END LOOP find_jurisdiction_iv;
2752
2753 ELSIF(p_iv_type = g_hour_juris_iv)THEN
2754
2755 <<find_hours_juris_iv>>
2756 LOOP
2757 EXIT find_hours_juris_iv WHEN ( (NOT l_iv_table.EXISTS (l_iv_tbl_idx))
2758 OR (l_hours_found AND l_jurisdiction_found)
2759 );
2760 if g_debug then
2761 hr_utility.trace('find_hours_juris_iv');
2762 end if;
2763 IF (translated_iv (p_iv_name => l_iv_table (l_iv_tbl_idx),
2764 p_date_active => p_effective_date
2765 ) = g_hours_iv
2766 )
2767 THEN
2768 if g_debug then
2769 hr_utility.set_location ( ' "HOURS" IV found, seq = '
2770 || l_iv_tbl_idx,
2771 20
2772 );
2773 end if;
2774 l_hours_found := TRUE;
2775 p_hours_iv_position := l_iv_tbl_idx;
2776 END IF;
2777
2778 IF (translated_iv (p_iv_name => l_iv_table (l_iv_tbl_idx),
2779 p_date_active => p_effective_date
2780 ) = g_jurisdiction_iv
2781 )
2782 THEN
2783 if g_debug then
2784 hr_utility.set_location ( ' "HOURS" IV found, seq = '
2785 || l_iv_tbl_idx,
2786 20
2787 );
2788 end if;
2789 l_jurisdiction_found := TRUE;
2790 p_jurisdiction_iv_position := l_iv_tbl_idx;
2791 END IF;
2792
2793 l_iv_tbl_idx := l_iv_table.NEXT (l_iv_tbl_idx);
2794 END LOOP find_hours_juris_iv;
2795 END IF;
2796
2797
2798 if g_debug then
2799 -- hr_utility.set_location ( ' returning '
2800 -- || p_jurisdiction_iv_position, 20);
2801 hr_utility.set_location ( 'Leaving '
2802 || l_proc, 100);
2803 end if;
2804 END hours_iv_position;
2805
2806
2807 FUNCTION find_element_id_in_attr_tbl (
2808 p_att_table IN hxc_generic_retrieval_pkg.t_time_attribute,
2809 p_tbb_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
2810 p_start_position IN PLS_INTEGER
2811 )
2812 RETURN pay_element_types_f.element_type_id%TYPE
2813 AS
2814 l_proc proc_name;
2815
2816 l_att_idx PLS_INTEGER
2817 := NVL (p_start_position, p_att_table.FIRST);
2818 l_element_id_found BOOLEAN := FALSE;
2819 l_element_type_id pay_element_types_f.element_type_id%TYPE := NULL;
2820 BEGIN
2821 g_debug :=hr_utility.debug_enabled;
2822 if g_debug then
2823 l_proc := g_package
2824 || 'find_element_id_in_attr_tbl';
2825 hr_utility.set_location ( 'Entering '
2826 || l_proc, 10);
2827 end if;
2828 LOOP
2829 EXIT WHEN ( NOT p_att_table.EXISTS (l_att_idx)
2830 OR (p_att_table (l_att_idx).bb_id <> p_tbb_id)
2831 OR (l_element_id_found)
2832 );
2833
2834 IF (attribute_is_element (p_att_table (l_att_idx)))
2835 THEN
2836 l_element_type_id := element_type_id (p_att_table (l_att_idx));
2837 l_element_id_found := TRUE;
2838 END IF;
2839
2840 l_att_idx := p_att_table.NEXT (l_att_idx);
2841 END LOOP;
2842
2843 if g_debug then
2844 hr_utility.set_location ( ' returning '
2845 || l_element_type_id, 30);
2846 hr_utility.set_location ( 'Leaving '
2847 || l_proc, 100);
2848 end if;
2849 RETURN l_element_type_id;
2850 END find_element_id_in_attr_tbl;
2851
2852 PROCEDURE find_other_in_attr_tbl (
2853 p_bg_id IN pay_batch_headers.business_group_id%TYPE,
2854 p_att_table IN hxc_generic_retrieval_pkg.t_time_attribute,
2855 p_tbb_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
2856 p_element_type_id IN pay_element_types_f.element_type_id%TYPE,
2857 p_cost_flex_id IN per_business_groups_perf.cost_allocation_structure%TYPE,
2858 p_effective_date IN pay_element_types_f.effective_start_date%TYPE,
2859 p_start_position IN PLS_INTEGER,
2860 p_ending_position OUT NOCOPY PLS_INTEGER, -- will return NULL if end-of-table was reached
2861 p_bee_rec IN OUT NOCOPY bee_rec
2862 )
2863 AS
2864 l_proc proc_name ;
2865 l_att_idx PLS_INTEGER
2866 := NVL (p_start_position, p_att_table.FIRST);
2867
2868 FUNCTION asg_id (
2869 p_bg_id IN pay_batch_headers.business_group_id%TYPE,
2870 p_effective_date IN DATE,
2871 p_bee_rec IN bee_rec
2872 )
2873 RETURN per_all_assignments_f.assignment_id%TYPE
2874 AS
2875 l_proc proc_name ;
2876 CURSOR csr_asg_id (
2877 p_bg_id pay_batch_headers.business_group_id%TYPE,
2878 p_effective_date DATE,
2879 p_assignment_number per_all_assignments_f.assignment_number%TYPE
2880 )
2881 IS
2882 SELECT assignment_id
2883 FROM per_all_assignments_f
2884 WHERE assignment_number = p_assignment_number
2885 AND business_group_id = p_bg_id
2886 AND p_effective_date BETWEEN effective_start_date
2887 AND effective_end_date;
2888
2889 l_asg_id per_all_assignments_f.assignment_id%TYPE;
2890 BEGIN
2891
2892 if g_debug then
2893 l_proc := g_package
2894 || 'asg_id';
2895 hr_utility.set_location ( 'Entering '
2896 || l_proc, 10);
2897 hr_utility.set_location ( 'p_bg_id = '
2898 || p_bg_id, 20);
2899 hr_utility.set_location ( 'p_effective_date = '
2900 || p_effective_date, 30);
2901 hr_utility.set_location ( 'p_bee_rec.pay_batch_line.assignment_number = '
2902 || p_bee_rec.pay_batch_line.assignment_number, 40);
2903 end if;
2904 OPEN csr_asg_id (p_bg_id,
2905 p_effective_date,
2906 p_bee_rec.pay_batch_line.assignment_number
2907 );
2908 FETCH csr_asg_id INTO l_asg_id;
2909 CLOSE csr_asg_id;
2910 if g_debug then
2911 hr_utility.set_location ( ' Returning Asg ID: '
2912 || l_asg_id, 20);
2913 hr_utility.set_location ( 'Leaving '
2914 || l_proc, 100);
2915 end if;
2916 RETURN l_asg_id;
2917 END asg_id;
2918
2919 FUNCTION asg_num (p_effective_date IN DATE, p_bee_rec IN bee_rec)
2920 RETURN per_all_assignments_f.assignment_number%TYPE
2921 AS
2922 l_proc proc_name ;
2923 CURSOR csr_asg_num (
2924 p_effective_date DATE,
2925 p_assignment_id per_all_assignments_f.assignment_id%TYPE
2926 )
2927 IS
2928 SELECT assignment_number
2929 FROM per_all_assignments_f
2930 WHERE assignment_id = p_assignment_id
2931 AND p_effective_date BETWEEN effective_start_date
2932 AND effective_end_date;
2933
2934 l_asg_num per_all_assignments_f.assignment_number%TYPE;
2935 BEGIN
2936
2937 if g_debug then
2938 l_proc := g_package
2939 || 'asg_num';
2940 hr_utility.set_location ( 'Entering '
2941 || l_proc, 10);
2942 end if;
2943 OPEN csr_asg_num (p_effective_date,
2944 p_bee_rec.pay_batch_line.assignment_id
2945 );
2946 FETCH csr_asg_num INTO l_asg_num;
2947 CLOSE csr_asg_num;
2948 if g_debug then
2949 hr_utility.set_location ( ' Returning Asg Num: '
2950 || l_asg_num, 20);
2951 hr_utility.set_location ( 'Leaving '
2952 || l_proc, 100);
2953 end if;
2954 RETURN l_asg_num;
2955 END asg_num;
2956 BEGIN
2957 g_debug :=hr_utility.debug_enabled;
2958 if g_debug then
2959 l_proc := g_package
2960 || 'find_other_in_attr_tbl';
2961 hr_utility.set_location ( 'Entering '
2962 || l_proc, 10);
2963 end if;
2964 LOOP
2965 EXIT WHEN ( NOT p_att_table.EXISTS (l_att_idx)
2966 OR (p_att_table (l_att_idx).bb_id <> p_tbb_id)
2967 );
2968
2969 -- ADDED FOR PERF ...
2970 IF (p_att_table (l_att_idx).VALUE IS NOT NULL)
2971 THEN
2972 IF (attribute_is_input_value (p_att_table (l_att_idx)))
2973 THEN
2974 convert_attr_to_ivs (p_attr_rec => p_att_table (l_att_idx
2975 ),
2976 p_element_type_id => p_element_type_id,
2977 p_effective_date => p_effective_date,
2978 p_bee_rec => p_bee_rec
2979 );
2980 END IF;
2981
2982 IF (attribute_is_cost_segment (p_att_table (l_att_idx)))
2983 THEN
2984 convert_attr_to_costsegment (p_attr_rec => p_att_table (l_att_idx
2985 ),
2986 p_cost_flex_id => p_cost_flex_id,
2987 p_bee_rec => p_bee_rec
2988 );
2989 END IF;
2990
2991 IF (attribute_is_asg_id (p_att_table (l_att_idx)))
2992 THEN
2993 p_bee_rec.pay_batch_line.assignment_id :=
2994 p_att_table (l_att_idx).VALUE;
2995 p_bee_rec.pay_batch_line.assignment_number :=
2996 asg_num (p_effective_date, p_bee_rec);
2997 END IF;
2998
2999 IF (attribute_is_asg_num (p_att_table (l_att_idx)))
3000 THEN
3001 p_bee_rec.pay_batch_line.assignment_number :=
3002 p_att_table (l_att_idx).VALUE;
3003 p_bee_rec.pay_batch_line.assignment_id :=
3004 asg_id (p_bg_id, p_effective_date, p_bee_rec);
3005 END IF;
3006 END IF;
3007
3008 l_att_idx := p_att_table.NEXT (l_att_idx);
3009 END LOOP;
3010
3011 p_ending_position := l_att_idx;
3012 if g_debug then
3013 hr_utility.set_location ( ' returning p_ending_position '
3014 || p_ending_position,
3015 20
3016 );
3017 hr_utility.set_location ( 'Leaving '
3018 || l_proc, 100);
3019 end if;
3020 END find_other_in_attr_tbl;
3021
3022 FUNCTION skip_attributes (
3023 p_att_table IN hxc_generic_retrieval_pkg.t_time_attribute,
3024 p_tbb_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
3025 p_start_position IN PLS_INTEGER
3026 )
3027 RETURN PLS_INTEGER
3028 AS
3029 l_proc proc_name ;
3030 l_att_idx PLS_INTEGER
3031 := NVL (p_start_position, p_att_table.FIRST);
3032 BEGIN
3033 g_debug :=hr_utility.debug_enabled;
3034 if g_debug then
3035 l_proc := g_package
3036 || 'skip_attributes';
3037 hr_utility.set_location ( 'Entering '
3038 || l_proc, 10);
3039 end if;
3040 LOOP
3041 EXIT WHEN ( NOT p_att_table.EXISTS (l_att_idx)
3042 OR (p_att_table (l_att_idx).bb_id <> p_tbb_id)
3043 );
3044 l_att_idx := p_att_table.NEXT (l_att_idx);
3045 END LOOP;
3046
3047 if g_debug then
3048 hr_utility.set_location ( ' returning ending position '
3049 || l_att_idx,
3050 20
3051 );
3052 hr_utility.set_location ( 'Leaving '
3053 || l_proc, 100);
3054 end if;
3055 RETURN l_att_idx;
3056 END skip_attributes;
3057
3058 /* PROCEDURE find_asg_in_attr_tbl (
3059 p_att_table IN hxc_generic_retrieval_pkg.t_time_attribute,
3060 p_tbb_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
3061 p_start_position IN PLS_INTEGER,
3062 p_bee_rec IN OUT NOCOPY bee_rec
3063 )
3064 AS
3065 l_proc proc_name := g_package
3066 || 'find_asg_in_attr_tbl';
3067 l_att_idx PLS_INTEGER := p_start_position;
3068 l_asg_id_found BOOLEAN := FALSE;
3069 l_asg_num_found BOOLEAN := FALSE;
3070 BEGIN
3071 g_debug :=hr_utility.debug_enabled;
3072 if g_debug then
3073 hr_utility.set_location ( 'Entering '
3074 || l_proc, 10);
3075 end if;
3076 LOOP
3077 EXIT WHEN ( NOT p_att_table.EXISTS (l_att_idx)
3078 OR (p_att_table (l_att_idx).bb_id <> p_tbb_id)
3079 OR (l_asg_id_found AND l_asg_num_found)
3080 );
3081
3082 IF (attribute_is_asg_id (p_att_table (l_att_idx)))
3083 THEN
3084 p_bee_rec.pay_batch_line.assignment_id :=
3085 p_att_table (l_att_idx).VALUE;
3086 l_asg_id_found := TRUE;
3087 ELSIF (attribute_is_asg_num (p_att_table (l_att_idx)))
3088 THEN
3089 p_bee_rec.pay_batch_line.assignment_number :=
3090 p_att_table (l_att_idx).VALUE;
3091 l_asg_num_found := TRUE;
3092 END IF;
3093
3094 l_att_idx := p_att_table.NEXT (l_att_idx);
3095 END LOOP;
3096
3097 if g_debug then
3098 hr_utility.set_location ( 'Leaving '
3099 || l_proc, 100);
3100 end if;
3101 END find_asg_in_attr_tbl;
3102 */
3103 FUNCTION cost_flex_structure_id (
3104 p_business_group_id IN per_all_organization_units.business_group_id%TYPE
3105 )
3106 RETURN per_business_groups_perf.cost_allocation_structure%TYPE
3107 IS
3108 l_proc proc_name;
3109
3110 l_cost_allocation_structure_id per_business_groups_perf.cost_allocation_structure%TYPE;
3111 BEGIN
3112 g_debug :=hr_utility.debug_enabled;
3113 if g_debug then
3114 l_proc := g_package
3115 || 'cost_flex_structure_id';
3116 hr_utility.set_location ( 'Entering '
3117 || l_proc, 10);
3118 end if;
3119 pay_paywsqee_pkg.populate_context_items (p_business_group_id,
3120 l_cost_allocation_structure_id
3121 );
3122 if g_debug then
3123 hr_utility.set_location ( ' Returning '
3124 || l_cost_allocation_structure_id,
3125 20
3126 );
3127 hr_utility.set_location ( 'Leaving '
3128 || l_proc, 30);
3129 end if;
3130 RETURN l_cost_allocation_structure_id;
3131 END cost_flex_structure_id;
3132
3133 FUNCTION costflex_value (
3134 p_id_flex_num IN fnd_id_flex_segments.id_flex_num%TYPE,
3135 p_segment_name IN fnd_id_flex_segments.application_column_name%TYPE,
3136 p_validation_type IN fnd_flex_value_sets.validation_type%TYPE
3137 DEFAULT g_independant,
3138 p_flex_value_id IN hxc_time_attributes.attribute1%TYPE
3139 )
3140 RETURN fnd_flex_values.flex_value%TYPE
3141 IS
3142 l_proc proc_name ;
3143
3144
3145 -- Bug 8666411
3146 -- Commented out validation type from the below cursor.
3147 -- With this query, we are letting only Independant type value sets
3148 -- to pass thru, because the calling code never passes any type parameter.
3149
3150
3151 CURSOR csr_flex_value (
3152 p_id_flex_num fnd_id_flex_segments.id_flex_num%TYPE,
3153 p_segment_name fnd_id_flex_segments.application_column_name%TYPE,
3154 p_validation_type fnd_flex_value_sets.validation_type%TYPE,
3155 p_flex_value_id fnd_flex_values.flex_value_id%TYPE
3156 )
3157 IS
3158 SELECT ffv.flex_value
3159 FROM fnd_id_flex_segments fifs,
3160 fnd_flex_value_sets ffvs,
3161 fnd_flex_values ffv
3162 WHERE fifs.application_id = g_pay_app_id
3163 AND fifs.id_flex_code = g_cost_flex_code
3164 AND fifs.id_flex_num = p_id_flex_num
3165 AND fifs.application_column_name = p_segment_name
3166 AND fifs.flex_value_set_id = ffvs.flex_value_set_id
3167 --AND ffvs.validation_type = p_validation_type
3168 AND ffvs.flex_value_set_id = ffv.flex_value_set_id
3169 AND ffv.flex_value_id = p_flex_value_id;
3170
3171 l_costflex_value per_business_groups_perf.cost_allocation_structure%TYPE;
3172 l_found_in_cache BOOLEAN := FALSE;
3173 BEGIN
3174 g_debug :=hr_utility.debug_enabled;
3175 if g_debug then
3176 l_proc := g_package
3177 || 'costflex_value';
3178 hr_utility.set_location ( 'Entering '
3179 || l_proc, 10);
3180 end if;
3181 IF (isnumber (p_flex_value_id))
3182 THEN
3183 IF ((caching) AND (g_flex_values.EXISTS (p_flex_value_id)))
3184 THEN
3185 IF ( (p_segment_name =
3186 g_flex_values (p_flex_value_id).segment_name
3187 )
3188 AND (p_validation_type =
3189 g_flex_values (p_flex_value_id).validation_type
3190 )
3191 AND (p_id_flex_num =
3192 g_flex_values (p_flex_value_id).id_flex_num
3193 )
3194 )
3195 THEN
3196 l_costflex_value := g_flex_values (p_flex_value_id).flex_value;
3197 l_found_in_cache := TRUE;
3198 END IF;
3199 END IF;
3200
3201 IF (NOT l_found_in_cache)
3202 THEN
3203 IF (csr_flex_value%ISOPEN)
3204 THEN
3205 CLOSE csr_flex_value;
3206 END IF;
3207
3208 OPEN csr_flex_value (p_id_flex_num,
3209 p_segment_name,
3210 p_validation_type,
3211 p_flex_value_id
3212 );
3213 FETCH csr_flex_value INTO l_costflex_value;
3214
3215 IF (csr_flex_value%NOTFOUND)
3216 THEN
3217 l_costflex_value := p_flex_value_id;
3218 ELSE
3219 IF (caching)
3220 THEN
3221 g_flex_values (p_flex_value_id).segment_name :=
3222 p_segment_name;
3223 g_flex_values (p_flex_value_id).validation_type :=
3224 p_validation_type;
3225 g_flex_values (p_flex_value_id).id_flex_num :=
3226 p_id_flex_num;
3227 g_flex_values (p_flex_value_id).flex_value :=
3228 l_costflex_value;
3229 END IF;
3230 END IF;
3231
3232 CLOSE csr_flex_value;
3233 END IF;
3234 ELSE
3235 l_costflex_value := p_flex_value_id;
3236 END IF;
3237
3238 if g_debug then
3239 hr_utility.set_location ( ' returning l_costflex_value = '
3240 || l_costflex_value,
3241 20
3242 );
3243 hr_utility.set_location ( 'Leaving '
3244 || l_proc, 30);
3245 end if;
3246 RETURN l_costflex_value;
3247 END costflex_value;
3248
3249 FUNCTION costflex_concat_segments (
3250 p_cost_allocation_keyflex_id IN pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE
3251 )
3252 RETURN pay_cost_allocation_keyflex.concatenated_segments%TYPE
3253 IS
3254 l_proc proc_name;
3255
3256
3257 CURSOR csr_costflex_concat_segments (
3258 p_cost_allocation_keyflex_id pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE
3259 )
3260 IS
3261 SELECT concatenated_segments
3262 FROM pay_cost_allocation_keyflex
3263 WHERE cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
3264
3265 l_costflex_concat_segments pay_cost_allocation_keyflex.concatenated_segments%TYPE;
3266 l_found_in_cache BOOLEAN := FALSE;
3267 BEGIN
3268 g_debug :=hr_utility.debug_enabled;
3269 if g_debug then
3270 l_proc := g_package
3271 || 'costflex_concat_segments';
3272 hr_utility.set_location ( 'Entering '
3273 || l_proc, 10);
3274 end if;
3275 IF ( (caching)
3276 AND (g_concatenated_segments.EXISTS (p_cost_allocation_keyflex_id))
3277 )
3278 THEN
3279 l_costflex_concat_segments :=
3280 g_concatenated_segments (p_cost_allocation_keyflex_id).concatenated_segment;
3281 l_found_in_cache := TRUE;
3282 END IF;
3283
3284 IF (NOT l_found_in_cache)
3285 THEN
3286 IF (csr_costflex_concat_segments%ISOPEN)
3287 THEN
3288 CLOSE csr_costflex_concat_segments;
3289 END IF;
3290
3291 OPEN csr_costflex_concat_segments (p_cost_allocation_keyflex_id);
3292 FETCH csr_costflex_concat_segments INTO l_costflex_concat_segments;
3293
3294 IF ((csr_costflex_concat_segments%FOUND) AND (caching))
3295 THEN
3296 g_concatenated_segments (p_cost_allocation_keyflex_id).concatenated_segment :=
3297 l_costflex_concat_segments;
3298 END IF;
3299
3300 CLOSE csr_costflex_concat_segments;
3301 END IF;
3302
3303 if g_debug then
3304 hr_utility.set_location ( ' returning '
3305 || l_costflex_concat_segments,
3306 20
3307 );
3308 hr_utility.set_location ( 'Leaving '
3309 || l_proc, 30);
3310 end if;
3311 RETURN l_costflex_concat_segments;
3312 END costflex_concat_segments;
3313
3314 FUNCTION cost_segments_all_null (
3315 p_segment_1 IN pay_cost_allocation_keyflex.segment1%TYPE,
3316 p_segment_2 IN pay_cost_allocation_keyflex.segment2%TYPE,
3317 p_segment_3 IN pay_cost_allocation_keyflex.segment3%TYPE,
3318 p_segment_4 IN pay_cost_allocation_keyflex.segment4%TYPE,
3319 p_segment_5 IN pay_cost_allocation_keyflex.segment5%TYPE,
3320 p_segment_6 IN pay_cost_allocation_keyflex.segment6%TYPE,
3321 p_segment_7 IN pay_cost_allocation_keyflex.segment7%TYPE,
3322 p_segment_8 IN pay_cost_allocation_keyflex.segment8%TYPE,
3323 p_segment_9 IN pay_cost_allocation_keyflex.segment9%TYPE,
3324 p_segment_10 IN pay_cost_allocation_keyflex.segment10%TYPE,
3325 p_segment_11 IN pay_cost_allocation_keyflex.segment11%TYPE,
3326 p_segment_12 IN pay_cost_allocation_keyflex.segment12%TYPE,
3327 p_segment_13 IN pay_cost_allocation_keyflex.segment13%TYPE,
3328 p_segment_14 IN pay_cost_allocation_keyflex.segment14%TYPE,
3329 p_segment_15 IN pay_cost_allocation_keyflex.segment15%TYPE,
3330 p_segment_16 IN pay_cost_allocation_keyflex.segment16%TYPE,
3331 p_segment_17 IN pay_cost_allocation_keyflex.segment17%TYPE,
3332 p_segment_18 IN pay_cost_allocation_keyflex.segment18%TYPE,
3333 p_segment_19 IN pay_cost_allocation_keyflex.segment19%TYPE,
3334 p_segment_20 IN pay_cost_allocation_keyflex.segment20%TYPE,
3335 p_segment_21 IN pay_cost_allocation_keyflex.segment21%TYPE,
3336 p_segment_22 IN pay_cost_allocation_keyflex.segment22%TYPE,
3337 p_segment_23 IN pay_cost_allocation_keyflex.segment23%TYPE,
3338 p_segment_24 IN pay_cost_allocation_keyflex.segment24%TYPE,
3339 p_segment_25 IN pay_cost_allocation_keyflex.segment25%TYPE,
3340 p_segment_26 IN pay_cost_allocation_keyflex.segment26%TYPE,
3341 p_segment_27 IN pay_cost_allocation_keyflex.segment27%TYPE,
3342 p_segment_28 IN pay_cost_allocation_keyflex.segment28%TYPE,
3343 p_segment_29 IN pay_cost_allocation_keyflex.segment29%TYPE,
3344 p_segment_30 IN pay_cost_allocation_keyflex.segment30%TYPE
3345 )
3346 RETURN BOOLEAN
3347 IS
3348 l_proc proc_name;
3349
3350 l_cost_segments_all_null BOOLEAN := FALSE;
3351 BEGIN
3352 g_debug :=hr_utility.debug_enabled;
3353 if g_debug then
3354 l_proc := g_package
3355 || 'cost_segments_all_null';
3356 hr_utility.set_location ( 'Entering '
3357 || l_proc, 10);
3358 end if;
3359 IF ( (p_segment_1 IS NULL)
3360 AND (p_segment_2 IS NULL)
3361 AND (p_segment_3 IS NULL)
3362 AND (p_segment_4 IS NULL)
3363 AND (p_segment_5 IS NULL)
3364 AND (p_segment_6 IS NULL)
3365 AND (p_segment_7 IS NULL)
3366 AND (p_segment_8 IS NULL)
3367 AND (p_segment_9 IS NULL)
3368 AND (p_segment_10 IS NULL)
3369 AND (p_segment_11 IS NULL)
3370 AND (p_segment_12 IS NULL)
3371 AND (p_segment_13 IS NULL)
3372 AND (p_segment_14 IS NULL)
3373 AND (p_segment_15 IS NULL)
3374 AND (p_segment_16 IS NULL)
3375 AND (p_segment_17 IS NULL)
3376 AND (p_segment_18 IS NULL)
3377 AND (p_segment_19 IS NULL)
3378 AND (p_segment_20 IS NULL)
3379 AND (p_segment_21 IS NULL)
3380 AND (p_segment_22 IS NULL)
3381 AND (p_segment_23 IS NULL)
3382 AND (p_segment_24 IS NULL)
3383 AND (p_segment_25 IS NULL)
3384 AND (p_segment_26 IS NULL)
3385 AND (p_segment_27 IS NULL)
3386 AND (p_segment_28 IS NULL)
3387 AND (p_segment_29 IS NULL)
3388 AND (p_segment_30 IS NULL)
3389 )
3390 THEN
3391 l_cost_segments_all_null := TRUE;
3392 ELSE
3393 l_cost_segments_all_null := FALSE;
3394 END IF;
3395
3396 if g_debug then
3397 hr_utility.set_location ( 'Leaving '
3398 || l_proc, 100);
3399 end if;
3400 RETURN (l_cost_segments_all_null);
3401 END cost_segments_all_null;
3402
3403 FUNCTION cost_segments_all_null (p_bee_rec IN bee_rec)
3404 RETURN BOOLEAN
3405 IS
3406 l_proc proc_name;
3407
3408 l_cost_segments_all_null BOOLEAN := FALSE;
3409 BEGIN
3410 g_debug :=hr_utility.debug_enabled;
3411 if g_debug then
3412 l_proc := g_package
3413 || 'cost_segments_all_null';
3414 hr_utility.set_location ( 'Entering '
3415 || l_proc, 10);
3416 end if;
3417 l_cost_segments_all_null :=
3418 cost_segments_all_null (p_segment_1 => p_bee_rec.pay_batch_line.segment1,
3419 p_segment_2 => p_bee_rec.pay_batch_line.segment2,
3420 p_segment_3 => p_bee_rec.pay_batch_line.segment3,
3421 p_segment_4 => p_bee_rec.pay_batch_line.segment4,
3422 p_segment_5 => p_bee_rec.pay_batch_line.segment5,
3423 p_segment_6 => p_bee_rec.pay_batch_line.segment6,
3424 p_segment_7 => p_bee_rec.pay_batch_line.segment7,
3425 p_segment_8 => p_bee_rec.pay_batch_line.segment8,
3426 p_segment_9 => p_bee_rec.pay_batch_line.segment9,
3427 p_segment_10 => p_bee_rec.pay_batch_line.segment10,
3428 p_segment_11 => p_bee_rec.pay_batch_line.segment11,
3429 p_segment_12 => p_bee_rec.pay_batch_line.segment12,
3430 p_segment_13 => p_bee_rec.pay_batch_line.segment13,
3431 p_segment_14 => p_bee_rec.pay_batch_line.segment14,
3432 p_segment_15 => p_bee_rec.pay_batch_line.segment15,
3433 p_segment_16 => p_bee_rec.pay_batch_line.segment16,
3434 p_segment_17 => p_bee_rec.pay_batch_line.segment17,
3435 p_segment_18 => p_bee_rec.pay_batch_line.segment18,
3436 p_segment_19 => p_bee_rec.pay_batch_line.segment19,
3437 p_segment_20 => p_bee_rec.pay_batch_line.segment20,
3438 p_segment_21 => p_bee_rec.pay_batch_line.segment21,
3439 p_segment_22 => p_bee_rec.pay_batch_line.segment22,
3440 p_segment_23 => p_bee_rec.pay_batch_line.segment23,
3441 p_segment_24 => p_bee_rec.pay_batch_line.segment24,
3442 p_segment_25 => p_bee_rec.pay_batch_line.segment25,
3443 p_segment_26 => p_bee_rec.pay_batch_line.segment26,
3444 p_segment_27 => p_bee_rec.pay_batch_line.segment27,
3445 p_segment_28 => p_bee_rec.pay_batch_line.segment28,
3446 p_segment_29 => p_bee_rec.pay_batch_line.segment29,
3447 p_segment_30 => p_bee_rec.pay_batch_line.segment30
3448 );
3449 if g_debug then
3450 hr_utility.set_location ( 'Leaving '
3451 || l_proc, 100);
3452 end if;
3453 RETURN (l_cost_segments_all_null);
3454 END cost_segments_all_null;
3455
3456 FUNCTION cost_allocation_kff_id (
3457 p_business_group_id IN per_all_organization_units.business_group_id%TYPE,
3458 p_segment_1 IN pay_cost_allocation_keyflex.segment1%TYPE,
3459 p_segment_2 IN pay_cost_allocation_keyflex.segment2%TYPE,
3460 p_segment_3 IN pay_cost_allocation_keyflex.segment3%TYPE,
3461 p_segment_4 IN pay_cost_allocation_keyflex.segment4%TYPE,
3462 p_segment_5 IN pay_cost_allocation_keyflex.segment5%TYPE,
3463 p_segment_6 IN pay_cost_allocation_keyflex.segment6%TYPE,
3464 p_segment_7 IN pay_cost_allocation_keyflex.segment7%TYPE,
3465 p_segment_8 IN pay_cost_allocation_keyflex.segment8%TYPE,
3466 p_segment_9 IN pay_cost_allocation_keyflex.segment9%TYPE,
3467 p_segment_10 IN pay_cost_allocation_keyflex.segment10%TYPE,
3468 p_segment_11 IN pay_cost_allocation_keyflex.segment11%TYPE,
3469 p_segment_12 IN pay_cost_allocation_keyflex.segment12%TYPE,
3470 p_segment_13 IN pay_cost_allocation_keyflex.segment13%TYPE,
3471 p_segment_14 IN pay_cost_allocation_keyflex.segment14%TYPE,
3472 p_segment_15 IN pay_cost_allocation_keyflex.segment15%TYPE,
3473 p_segment_16 IN pay_cost_allocation_keyflex.segment16%TYPE,
3474 p_segment_17 IN pay_cost_allocation_keyflex.segment17%TYPE,
3475 p_segment_18 IN pay_cost_allocation_keyflex.segment18%TYPE,
3476 p_segment_19 IN pay_cost_allocation_keyflex.segment19%TYPE,
3477 p_segment_20 IN pay_cost_allocation_keyflex.segment20%TYPE,
3478 p_segment_21 IN pay_cost_allocation_keyflex.segment21%TYPE,
3479 p_segment_22 IN pay_cost_allocation_keyflex.segment22%TYPE,
3480 p_segment_23 IN pay_cost_allocation_keyflex.segment23%TYPE,
3481 p_segment_24 IN pay_cost_allocation_keyflex.segment24%TYPE,
3482 p_segment_25 IN pay_cost_allocation_keyflex.segment25%TYPE,
3483 p_segment_26 IN pay_cost_allocation_keyflex.segment26%TYPE,
3484 p_segment_27 IN pay_cost_allocation_keyflex.segment27%TYPE,
3485 p_segment_28 IN pay_cost_allocation_keyflex.segment28%TYPE,
3486 p_segment_29 IN pay_cost_allocation_keyflex.segment29%TYPE,
3487 p_segment_30 IN pay_cost_allocation_keyflex.segment30%TYPE
3488 )
3489 RETURN pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE
3490 IS
3491 l_proc proc_name;
3492
3493 l_cost_allocation_kff_id pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE;
3494 BEGIN
3495 g_debug :=hr_utility.debug_enabled;
3496 if g_debug then
3497 l_proc := g_package
3498 || 'cost_allocation_kff_id';
3499 hr_utility.set_location ( 'Entering '
3500 || l_proc, 10);
3501 end if;
3502 IF (NOT cost_segments_all_null (p_segment_1 => p_segment_1,
3503 p_segment_2 => p_segment_2,
3504 p_segment_3 => p_segment_3,
3505 p_segment_4 => p_segment_4,
3506 p_segment_5 => p_segment_5,
3507 p_segment_6 => p_segment_6,
3508 p_segment_7 => p_segment_7,
3509 p_segment_8 => p_segment_8,
3510 p_segment_9 => p_segment_9,
3511 p_segment_10 => p_segment_10,
3512 p_segment_11 => p_segment_11,
3513 p_segment_12 => p_segment_12,
3514 p_segment_13 => p_segment_13,
3515 p_segment_14 => p_segment_14,
3516 p_segment_15 => p_segment_15,
3517 p_segment_16 => p_segment_16,
3518 p_segment_17 => p_segment_17,
3519 p_segment_18 => p_segment_18,
3520 p_segment_19 => p_segment_19,
3521 p_segment_20 => p_segment_20,
3522 p_segment_21 => p_segment_21,
3523 p_segment_22 => p_segment_22,
3524 p_segment_23 => p_segment_23,
3525 p_segment_24 => p_segment_24,
3526 p_segment_25 => p_segment_25,
3527 p_segment_26 => p_segment_26,
3528 p_segment_27 => p_segment_27,
3529 p_segment_28 => p_segment_28,
3530 p_segment_29 => p_segment_29,
3531 p_segment_30 => p_segment_30
3532 )
3533 )
3534 THEN
3535 l_cost_allocation_kff_id :=
3536 hr_entry.maintain_cost_keyflex (p_cost_keyflex_structure => cost_flex_structure_id (p_business_group_id => p_business_group_id
3537 ),
3538 p_cost_allocation_keyflex_id => -1,
3539 p_concatenated_segments => NULL,
3540 p_summary_flag => 'N',
3541 p_start_date_active => NULL,
3542 p_end_date_active => NULL,
3543 p_segment1 => p_segment_1,
3544 p_segment2 => p_segment_2,
3545 p_segment3 => p_segment_3,
3546 p_segment4 => p_segment_4,
3547 p_segment5 => p_segment_5,
3548 p_segment6 => p_segment_6,
3549 p_segment7 => p_segment_7,
3550 p_segment8 => p_segment_8,
3551 p_segment9 => p_segment_9,
3552 p_segment10 => p_segment_10,
3553 p_segment11 => p_segment_11,
3554 p_segment12 => p_segment_12,
3555 p_segment13 => p_segment_13,
3556 p_segment14 => p_segment_14,
3557 p_segment15 => p_segment_15,
3558 p_segment16 => p_segment_16,
3559 p_segment17 => p_segment_17,
3560 p_segment18 => p_segment_18,
3561 p_segment19 => p_segment_19,
3562 p_segment20 => p_segment_20,
3563 p_segment21 => p_segment_21,
3564 p_segment22 => p_segment_22,
3565 p_segment23 => p_segment_23,
3566 p_segment24 => p_segment_24,
3567 p_segment25 => p_segment_25,
3568 p_segment26 => p_segment_26,
3569 p_segment27 => p_segment_27,
3570 p_segment28 => p_segment_28,
3571 p_segment29 => p_segment_29,
3572 p_segment30 => p_segment_30
3573 );
3574 ELSE
3575 l_cost_allocation_kff_id := NULL;
3576 END IF;
3577
3578 if g_debug then
3579 hr_utility.set_location ( ' returning '
3580 || l_cost_allocation_kff_id,
3581 20
3582 );
3583 hr_utility.set_location ( 'Leaving '
3584 || l_proc, 100);
3585 end if;
3586 RETURN (l_cost_allocation_kff_id);
3587 END cost_allocation_kff_id;
3588
3589 FUNCTION cost_allocation_kff_id (
3590 p_business_group_id IN per_all_organization_units.business_group_id%TYPE,
3591 p_bee_rec IN bee_rec
3592 )
3593 RETURN pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE
3594 IS
3595 l_proc proc_name;
3596
3597 l_cost_allocation_kff_id pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE;
3598 BEGIN
3599 g_debug :=hr_utility.debug_enabled;
3600 if g_debug then
3601 l_proc := g_package
3602 || 'cost_allocation_kff_id (p_bee_rec)';
3603 hr_utility.set_location ( 'Entering '
3604 || l_proc, 10);
3605 end if;
3606 l_cost_allocation_kff_id :=
3607 cost_allocation_kff_id (p_business_group_id => p_business_group_id,
3608 p_segment_1 => p_bee_rec.pay_batch_line.segment1,
3609 p_segment_2 => p_bee_rec.pay_batch_line.segment2,
3610 p_segment_3 => p_bee_rec.pay_batch_line.segment3,
3611 p_segment_4 => p_bee_rec.pay_batch_line.segment4,
3612 p_segment_5 => p_bee_rec.pay_batch_line.segment5,
3613 p_segment_6 => p_bee_rec.pay_batch_line.segment6,
3614 p_segment_7 => p_bee_rec.pay_batch_line.segment7,
3615 p_segment_8 => p_bee_rec.pay_batch_line.segment8,
3616 p_segment_9 => p_bee_rec.pay_batch_line.segment9,
3617 p_segment_10 => p_bee_rec.pay_batch_line.segment10,
3618 p_segment_11 => p_bee_rec.pay_batch_line.segment11,
3619 p_segment_12 => p_bee_rec.pay_batch_line.segment12,
3620 p_segment_13 => p_bee_rec.pay_batch_line.segment13,
3621 p_segment_14 => p_bee_rec.pay_batch_line.segment14,
3622 p_segment_15 => p_bee_rec.pay_batch_line.segment15,
3623 p_segment_16 => p_bee_rec.pay_batch_line.segment16,
3624 p_segment_17 => p_bee_rec.pay_batch_line.segment17,
3625 p_segment_18 => p_bee_rec.pay_batch_line.segment18,
3626 p_segment_19 => p_bee_rec.pay_batch_line.segment19,
3627 p_segment_20 => p_bee_rec.pay_batch_line.segment20,
3628 p_segment_21 => p_bee_rec.pay_batch_line.segment21,
3629 p_segment_22 => p_bee_rec.pay_batch_line.segment22,
3630 p_segment_23 => p_bee_rec.pay_batch_line.segment23,
3631 p_segment_24 => p_bee_rec.pay_batch_line.segment24,
3632 p_segment_25 => p_bee_rec.pay_batch_line.segment25,
3633 p_segment_26 => p_bee_rec.pay_batch_line.segment26,
3634 p_segment_27 => p_bee_rec.pay_batch_line.segment27,
3635 p_segment_28 => p_bee_rec.pay_batch_line.segment28,
3636 p_segment_29 => p_bee_rec.pay_batch_line.segment29,
3637 p_segment_30 => p_bee_rec.pay_batch_line.segment30
3638 );
3639 if g_debug then
3640 hr_utility.set_location ( ' returning '
3641 || l_cost_allocation_kff_id,
3642 20
3643 );
3644 hr_utility.set_location ( 'Leaving '
3645 || l_proc, 100);
3646 end if;
3647 RETURN (l_cost_allocation_kff_id);
3648 END cost_allocation_kff_id;
3649
3650 FUNCTION hours_factor (p_is_old IN BOOLEAN)
3651 RETURN NUMBER
3652 IS
3653 l_proc proc_name ;
3654 l_hours_factor NUMBER (1);
3655 BEGIN
3656 g_debug :=hr_utility.debug_enabled;
3657 if g_debug then
3658 l_proc := g_package
3659 || 'hours_factor';
3660 hr_utility.set_location ( 'Entering '
3661 || l_proc, 10);
3662 end if;
3663 IF (p_is_old)
3664 THEN
3665 l_hours_factor := -1;
3666 ELSE
3667 l_hours_factor := 1;
3668 END IF;
3669
3670 if g_debug then
3671 hr_utility.set_location ( ' Returning '
3672 || l_hours_factor, 20);
3673 hr_utility.set_location ( 'Leaving '
3674 || l_proc, 30);
3675 end if;
3676 RETURN l_hours_factor;
3677 END hours_factor;
3678
3679 /* These 3 procedures were commented out because the pipes were causing
3680 issues with the SGA. The pipe grew out-of-control for no apparent reason.
3681 Anyway, for now, we will NOT allow multithreading of this process so we do
3682 not need pipes!!!
3683 */
3684 /* PROCEDURE write_pipe_batch_info (p_batch_info IN piped_batch_info_table)
3685 IS
3686 l_proc proc_name := g_package
3687 || 'write_pipe_batch_info';
3688 l_send_message_status INTEGER;
3689 l_batch_info_idx PLS_INTEGER;
3690 BEGIN
3691 g_debug :=hr_utility.debug_enabled;
3692 if g_debug then
3693 hr_utility.set_location ( 'Entering '
3694 || l_proc, 10);
3695 end if;
3696 l_batch_info_idx := p_batch_info.FIRST;
3697 DBMS_PIPE.reset_buffer;
3698
3699 LOOP
3700 EXIT WHEN NOT p_batch_info.EXISTS (l_batch_info_idx);
3701 DBMS_PIPE.pack_message (p_batch_info (l_batch_info_idx).batch_ref);
3702 DBMS_PIPE.pack_message (
3703 p_batch_info (l_batch_info_idx).business_group_id
3704 );
3705 DBMS_PIPE.pack_message (
3706 p_batch_info (l_batch_info_idx).free_batch_suffix
3707 );
3708 l_batch_info_idx := p_batch_info.NEXT (l_batch_info_idx);
3709 END LOOP;
3710
3711 l_send_message_status :=
3712 DBMS_PIPE.send_message (
3713 pipename=> g_total_lines_pipe_name,
3714 TIMEOUT=> 0
3715 );
3716
3717 IF l_send_message_status != 0
3718 THEN
3719 NULL; -- RAISE ERROR
3720 END IF;
3721
3722 if g_debug then
3723 hr_utility.set_location ( 'Leaving '
3724 || l_proc, 100);
3725 end if;
3726 END write_pipe_batch_info;
3727
3728 FUNCTION read_pipe_batch_info
3729 RETURN piped_batch_info_table
3730 IS
3731 l_proc proc_name := g_package
3732 || 'read_pipe_batch_info';
3733 l_recieve_message_status INTEGER;
3734 l_batch_info piped_batch_info_table;
3735 l_batch_info_idx PLS_INTEGER := 1;
3736 e_pipe_empty EXCEPTION;
3737 PRAGMA EXCEPTION_INIT (e_pipe_empty, -6556);
3738 BEGIN
3739 g_debug :=hr_utility.debug_enabled;
3740 if g_debug then
3741 hr_utility.set_location ( 'Entering '
3742 || l_proc, 10);
3743 end if;
3744 l_recieve_message_status :=
3745 DBMS_PIPE.receive_message (
3746 pipename=> g_total_lines_pipe_name,
3747 TIMEOUT=> 0
3748 );
3749
3750 LOOP
3751 DBMS_PIPE.unpack_message (l_batch_info (l_batch_info_idx).batch_ref);
3752 DBMS_PIPE.unpack_message (
3753 l_batch_info (l_batch_info_idx).business_group_id
3754 );
3755 DBMS_PIPE.unpack_message (
3756 l_batch_info (l_batch_info_idx).free_batch_suffix
3757 );
3758
3759 l_batch_info_idx := l_batch_info_idx
3760 + 1;
3761 END LOOP;
3762 EXCEPTION
3763 WHEN e_pipe_empty
3764 THEN
3765 if g_debug then
3766 hr_utility.set_location (
3767 ' returning '
3768 || l_batch_info.COUNT
3769 || ' batch_info lines',
3770 20
3771 );
3772 hr_utility.set_location ( 'Leaving '
3773 || l_proc, 100);
3774 end if;
3775 DBMS_PIPE.reset_buffer;
3776 DBMS_PIPE.PURGE (hxt_interface_utilities.g_total_lines_pipe_name);
3777 RETURN l_batch_info;
3778 END read_pipe_batch_info;
3779
3780 FUNCTION purge_pipe
3781 RETURN BOOLEAN
3782 AS
3783 cannot_use_pipe EXCEPTION;
3784 PRAGMA EXCEPTION_INIT (cannot_use_pipe, -23322);
3785 l_purged BOOLEAN := FALSE;
3786 BEGIN
3787 DBMS_PIPE.PURGE (hxt_interface_utilities.g_total_lines_pipe_name);
3788 l_purged := TRUE;
3789 RETURN l_purged;
3790 EXCEPTION
3791 WHEN cannot_use_pipe
3792 THEN
3793 RETURN l_purged;
3794 WHEN OTHERS
3795 THEN
3796 RETURN l_purged;
3797 END purge_pipe;
3798 */
3799 FUNCTION free_batch_suffix (
3800 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
3801 p_bg_id IN pay_batch_headers.business_group_id%TYPE
3802 )
3803 RETURN NUMBER
3804 IS
3805 l_proc proc_name ;
3806 l_batch_info_idx PLS_INTEGER;
3807 l_batch_ref_found BOOLEAN := FALSE;
3808 l_free_batch_suffix NUMBER;
3809 BEGIN
3810 g_debug :=hr_utility.debug_enabled;
3811 if g_debug then
3812 l_proc := g_package
3813 || 'free_batch_suffix';
3814 hr_utility.set_location ( 'Entering '
3815 || l_proc, 10);
3816 end if;
3817 l_batch_info_idx := g_batch_info.FIRST;
3818
3819 IF (l_batch_info_idx IS NOT NULL)
3820 THEN
3821 LOOP
3822 EXIT WHEN NOT (g_batch_info.EXISTS (l_batch_info_idx))
3823 OR l_batch_ref_found;
3824
3825 IF ( (g_batch_info (l_batch_info_idx).batch_ref =
3826 p_batch_reference
3827 )
3828 AND (g_batch_info (l_batch_info_idx).business_group_id =
3829 p_bg_id
3830 )
3831 AND (g_batch_info (l_batch_info_idx).request_id =
3832 conc_request_id_suffix
3833 )
3834 )
3835 THEN
3836 l_free_batch_suffix :=
3837 g_batch_info (l_batch_info_idx).free_batch_suffix;
3838 g_batch_info (l_batch_info_idx).free_batch_suffix :=
3839 l_free_batch_suffix
3840 + 1;
3841 l_batch_ref_found := TRUE;
3842 END IF;
3843
3844 l_batch_info_idx := g_batch_info.NEXT (l_batch_info_idx);
3845 END LOOP;
3846 END IF;
3847
3848 IF (NOT l_batch_ref_found)
3849 THEN
3850 if g_debug then
3851 hr_utility.set_location (' new batch reference', 20);
3852 end if;
3853 l_batch_info_idx := NVL (g_batch_info.LAST, 0)
3854 + 1;
3855 g_batch_info (l_batch_info_idx).batch_ref := p_batch_reference;
3856 g_batch_info (l_batch_info_idx).business_group_id := p_bg_id;
3857 g_batch_info (l_batch_info_idx).request_id := conc_request_id_suffix;
3858 l_free_batch_suffix := 1;
3859 g_batch_info (l_batch_info_idx).free_batch_suffix :=
3860 l_free_batch_suffix
3861 + 1;
3862 END IF;
3863
3864 if g_debug then
3865 hr_utility.set_location ( 'Leaving '
3866 || l_proc, 100);
3867 end if;
3868 RETURN l_free_batch_suffix;
3869 END free_batch_suffix;
3870
3871 -- Action Changed Deleted
3872 -- --------------------------- ------- -------
3873 -- new TBB N N
3874 -- changed TBB (never xfered) N N
3875 -- changed TBB (xfered) Y N
3876 -- DELETED (never xfered) N Y
3877 -- DELETED (xfered) Y Y
3878 -- --------------------------- ------- -------
3879 FUNCTION is_changed (
3880 p_tbb_rec IN hxc_generic_retrieval_pkg.r_building_blocks
3881 )
3882 RETURN BOOLEAN
3883 AS
3884 l_proc proc_name ;
3885 l_is_changed BOOLEAN := FALSE;
3886 BEGIN
3887 g_debug :=hr_utility.debug_enabled;
3888 if g_debug then
3889 l_proc := g_package
3890 || 'is_changed';
3891 hr_utility.set_location ( 'Entering '
3892 || l_proc, 10);
3893 end if;
3894 IF (UPPER (p_tbb_rec.changed) = g_tbb_changed)
3895 THEN
3896 l_is_changed := TRUE;
3897 if g_debug then
3898 hr_utility.set_location ( ' TBB '
3899 || p_tbb_rec.bb_id
3900 || ' changed',
3901 20
3902 );
3903 end if;
3904 ELSE
3905 l_is_changed := FALSE;
3906 END IF;
3907
3908 if g_debug then
3909 hr_utility.set_location ( 'Leaving '
3910 || l_proc, 40);
3911 end if;
3912 RETURN l_is_changed;
3913 END is_changed;
3914
3915 FUNCTION is_deleted (
3916 p_tbb_rec IN hxc_generic_retrieval_pkg.r_building_blocks
3917 )
3918 RETURN BOOLEAN
3919 AS
3920 l_proc proc_name ;
3921 l_is_deleted BOOLEAN := FALSE;
3922 BEGIN
3923 g_debug :=hr_utility.debug_enabled;
3924 if g_debug then
3925 l_proc := g_package
3926 || 'is_deleted';
3927 hr_utility.set_location ( 'Entering '
3928 || l_proc, 10);
3929 end if;
3930 IF (UPPER (p_tbb_rec.deleted) = g_tbb_deleted)
3931 THEN
3932 l_is_deleted := TRUE;
3933 if g_debug then
3934 hr_utility.set_location ( ' TBB '
3935 || p_tbb_rec.bb_id
3936 || ' deleted',
3937 20
3938 );
3939 end if;
3940 ELSE
3941 l_is_deleted := FALSE;
3942 END IF;
3943
3944 if g_debug then
3945 hr_utility.set_location ( 'Leaving '
3946 || l_proc, 40);
3947 end if;
3948 RETURN l_is_deleted;
3949 END is_deleted;
3950
3951 FUNCTION is_in_sync (
3952 p_check_tbb_id hxc_time_building_blocks.time_building_block_id%TYPE,
3953 p_against_tbb_id hxc_time_building_blocks.time_building_block_id%TYPE
3954 )
3955 RETURN BOOLEAN
3956 AS
3957 l_proc proc_name ;
3958 l_is_in_sync BOOLEAN := FALSE;
3959 BEGIN
3960 g_debug :=hr_utility.debug_enabled;
3961 if g_debug then
3962 l_proc := g_package
3963 || 'is_in_sync';
3964 hr_utility.set_location ( 'Entering '
3965 || l_proc, 10);
3966 end if;
3967 IF (p_check_tbb_id = p_against_tbb_id)
3968 THEN
3969 l_is_in_sync := TRUE;
3970 ELSE
3971 l_is_in_sync := FALSE;
3972 if g_debug then
3973 hr_utility.set_location ( p_check_tbb_id
3974 || ' is NOT in sync with TBB '
3975 || p_against_tbb_id,
3976 30
3977 );
3978 end if;
3979 END IF;
3980
3981 if g_debug then
3982 hr_utility.set_location ( 'Leaving '
3983 || l_proc, 40);
3984 end if;
3985 RETURN l_is_in_sync;
3986 END is_in_sync;
3987
3988
3989
3990 FUNCTION get_geocode_from_attr_tab (
3991 p_att_table IN hxc_generic_retrieval_pkg.t_time_attribute,
3992 p_tbb_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
3993 p_start_position IN PLS_INTEGER
3994 )
3995 RETURN VARCHAR2
3996 AS
3997 l_proc proc_name;
3998
3999 l_att_idx PLS_INTEGER
4000 := NVL (p_start_position, p_att_table.FIRST);
4001 l_jurisdiction_code VARCHAR2(11):=NULL;
4002 l_state_name pay_us_states.state_name%TYPE;
4003 l_county_name pay_us_counties.county_name%TYPE;
4004 l_city_name pay_us_city_names.city_name%TYPE;
4005 l_zip_code pay_us_zip_codes.zip_start%TYPE;
4006
4007 BEGIN
4008 g_debug :=hr_utility.debug_enabled;
4009 if g_debug then
4010 l_proc := g_package
4011 || 'get_geocode_from_attr_tab';
4012 hr_utility.set_location ( 'Entering '
4013 || l_proc, 10);
4014 end if;
4015 LOOP
4016 EXIT WHEN ( NOT p_att_table.EXISTS (l_att_idx)
4017 );
4018 IF( p_att_table (l_att_idx).bb_id = p_tbb_id) THEN
4019
4020 IF(p_att_table(l_att_idx).field_name='NA_STATE_NAME') THEN
4021 l_state_name:=p_att_table(l_att_idx).value;
4022 IF(l_state_name IS NULL) THEN
4023 return '00-000-0000';
4024 END IF;
4025 END IF;
4026
4027 IF(p_att_table(l_att_idx).field_name='NA_COUNTY_NAME') THEN
4028 l_county_name:=p_att_table(l_att_idx).value;
4029 END IF;
4030
4031 IF(p_att_table(l_att_idx).field_name='NA_CITY_NAME' ) THEN
4032 l_city_name:=p_att_table(l_att_idx).value;
4033 END IF;
4034
4035 IF(p_att_table(l_att_idx).field_name='NA_ZIP_CODE' ) THEN
4036 l_zip_code:=p_att_table(l_att_idx).value;
4037 END IF;
4038
4039 END IF;
4040 l_att_idx := p_att_table.NEXT (l_att_idx);
4041 END LOOP;
4042
4043 l_jurisdiction_code:=pay_ac_utility.get_geocode(l_state_name,l_county_name,l_city_name,l_zip_code);
4044 RETURN l_jurisdiction_code;
4045 END get_geocode_from_attr_tab;
4046
4047 END hxt_interface_utilities;