[Home] [Help]
PACKAGE BODY: APPS.HXT_INTERFACE_UTILITIES
Source
1 PACKAGE BODY hxt_interface_utilities AS
2 /* $Header: hxtinterfaceutil.pkb 120.3.12000000.5 2007/06/11 10:22:11 rchennur noship $ */
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 CURSOR csr_translated_info (
2513 p_meaning hr_lookups.meaning%TYPE,
2514 p_date_active hr_lookups.start_date_active%TYPE
2515 )
2516 IS
2517 SELECT lookup_code, start_date_active, end_date_active
2518 FROM hr_lookups
2519 WHERE meaning = p_meaning
2520 AND lookup_type = g_element_iv_translations
2521 AND application_id = g_per_app_id
2522 AND enabled_flag = g_lookup_enabled
2523 AND p_date_active BETWEEN NVL (start_date_active, p_date_active)
2524 AND NVL (end_date_active, p_date_active);
2525
2526 l_translated_info csr_translated_info%ROWTYPE;
2527 l_translated_iv hr_lookups.lookup_code%TYPE;
2528 l_hashed_id PLS_INTEGER;
2529 l_found_in_cache BOOLEAN := FALSE;
2530 BEGIN
2531 g_debug :=hr_utility.debug_enabled;
2532 if g_debug then
2533 l_proc := g_package
2534 || 'translated_iv';
2535 hr_utility.set_location ( 'Entering '
2536 || l_proc, 10);
2537 end if;
2538 l_hashed_id := hashval (p_iv_name);
2539
2540 IF ((caching) AND (g_iv_translations.EXISTS (l_hashed_id)))
2541 THEN
2542 IF (p_date_active
2543 BETWEEN NVL (g_iv_translations (l_hashed_id).start_date_active,
2544 p_date_active
2545 )
2546 AND NVL (g_iv_translations (l_hashed_id).end_date_active,
2547 p_date_active
2548 )
2549 )
2550 THEN
2551 l_translated_iv := g_iv_translations (l_hashed_id).lookup_code;
2552 l_found_in_cache := TRUE;
2553 END IF;
2554 END IF;
2555
2556 IF (NOT l_found_in_cache)
2557 THEN
2558 IF (csr_translated_info%ISOPEN)
2559 THEN
2560 CLOSE csr_translated_info;
2561 END IF;
2562
2563 OPEN csr_translated_info (p_iv_name, p_date_active);
2564 FETCH csr_translated_info INTO l_translated_info;
2565
2566 IF (csr_translated_info%FOUND)
2567 THEN
2568 l_translated_iv := l_translated_info.lookup_code;
2569
2570 IF (caching)
2571 THEN
2572 g_iv_translations (l_hashed_id).lookup_code :=
2573 l_translated_info.lookup_code;
2574 g_iv_translations (l_hashed_id).start_date_active :=
2575 l_translated_info.start_date_active;
2576 g_iv_translations (l_hashed_id).end_date_active :=
2577 l_translated_info.end_date_active;
2578 END IF;
2579 END IF;
2580
2581 CLOSE csr_translated_info;
2582 END IF;
2583
2584 if g_debug then
2585 hr_utility.set_location ( ' returning '
2586 || l_translated_iv, 20);
2587 hr_utility.set_location ( 'Leaving '
2588 || l_proc, 100);
2589 end if;
2590 RETURN l_translated_iv;
2591 END translated_iv;
2592
2593
2594
2595 PROCEDURE hours_iv_position (
2596 p_element_type_id IN pay_element_types_f.element_type_id%TYPE,
2597 p_effective_date IN pay_element_types_f.effective_start_date%TYPE,
2598 p_hours_iv_position OUT NOCOPY PLS_INTEGER,
2599 p_jurisdiction_iv_position OUT NOCOPY PLS_INTEGER,
2600 p_iv_type IN VARCHAR2
2601 )
2602 AS
2603 l_proc proc_name ;
2604 l_iv_table input_value_name_table;
2605 l_hours_found BOOLEAN := FALSE;
2606 l_jurisdiction_found BOOLEAN := FALSE;
2607 l_iv_tbl_idx PLS_INTEGER;
2608 BEGIN
2609 g_debug :=hr_utility.debug_enabled;
2610 if g_debug then
2611 l_proc := g_package
2612 || 'hours_iv_position';
2613 hr_utility.set_location ( 'Entering '
2614 || l_proc, 10);
2615 end if;
2616 p_hours_iv_position := NULL;
2617 p_jurisdiction_iv_position := NULL;
2618
2619
2620 l_iv_table :=
2621 element_type_ivs (p_element_type_id => p_element_type_id,
2622 p_effective_date => p_effective_date
2623 );
2624 l_iv_tbl_idx := l_iv_table.FIRST;
2625
2626 IF(p_iv_type = g_hours_iv) THEN
2627
2628 <<find_hours_iv>>
2629 LOOP
2630 EXIT find_hours_iv WHEN ( (NOT l_iv_table.EXISTS (l_iv_tbl_idx))
2631 OR (l_hours_found)
2632 );
2633 if g_debug then
2634 hr_utility.trace('in find_hours_iv');
2635 end if;
2636 IF (translated_iv (p_iv_name => l_iv_table (l_iv_tbl_idx),
2637 p_date_active => p_effective_date
2638 ) = g_hours_iv
2639 )
2640 THEN
2641 if g_debug then
2642 hr_utility.set_location ( ' "HOURS" IV found, seq = '
2643 || l_iv_tbl_idx,
2644 20
2645 );
2646 end if;
2647 l_hours_found := TRUE;
2648 p_hours_iv_position := l_iv_tbl_idx;
2649 END IF;
2650
2651 l_iv_tbl_idx := l_iv_table.NEXT (l_iv_tbl_idx);
2652 END LOOP find_hours_iv;
2653
2654 ELSIF(p_iv_type = g_jurisdiction_iv)THEN
2655
2656 <<find_jurisdiction_iv>>
2657 LOOP
2658 EXIT find_jurisdiction_iv WHEN ( (NOT l_iv_table.EXISTS (l_iv_tbl_idx))
2659 OR (l_jurisdiction_found)
2660 );
2661 if g_debug then
2662 hr_utility.trace('find_jurisdiction_iv');
2663 end if;
2664 IF (translated_iv (p_iv_name => l_iv_table (l_iv_tbl_idx),
2665 p_date_active => p_effective_date
2666 ) = g_jurisdiction_iv
2667 )
2668 THEN
2669 if g_debug then
2670 hr_utility.set_location ( ' "HOURS" IV found, seq = '
2671 || l_iv_tbl_idx,
2672 20
2673 );
2674 end if;
2675 l_jurisdiction_found := TRUE;
2676 p_jurisdiction_iv_position := l_iv_tbl_idx;
2677 END IF;
2678
2679 l_iv_tbl_idx := l_iv_table.NEXT (l_iv_tbl_idx);
2680 END LOOP find_jurisdiction_iv;
2681
2682 ELSIF(p_iv_type = g_hour_juris_iv)THEN
2683
2684 <<find_hours_juris_iv>>
2685 LOOP
2686 EXIT find_hours_juris_iv WHEN ( (NOT l_iv_table.EXISTS (l_iv_tbl_idx))
2687 OR (l_hours_found AND l_jurisdiction_found)
2688 );
2689 if g_debug then
2690 hr_utility.trace('find_hours_juris_iv');
2691 end if;
2692 IF (translated_iv (p_iv_name => l_iv_table (l_iv_tbl_idx),
2693 p_date_active => p_effective_date
2694 ) = g_hours_iv
2695 )
2696 THEN
2697 if g_debug then
2698 hr_utility.set_location ( ' "HOURS" IV found, seq = '
2699 || l_iv_tbl_idx,
2700 20
2701 );
2702 end if;
2703 l_hours_found := TRUE;
2704 p_hours_iv_position := l_iv_tbl_idx;
2705 END IF;
2706
2707 IF (translated_iv (p_iv_name => l_iv_table (l_iv_tbl_idx),
2708 p_date_active => p_effective_date
2709 ) = g_jurisdiction_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_jurisdiction_found := TRUE;
2719 p_jurisdiction_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_juris_iv;
2724 END IF;
2725
2726
2727 if g_debug then
2728 -- hr_utility.set_location ( ' returning '
2729 -- || p_jurisdiction_iv_position, 20);
2730 hr_utility.set_location ( 'Leaving '
2731 || l_proc, 100);
2732 end if;
2733 END hours_iv_position;
2734
2735
2736 FUNCTION find_element_id_in_attr_tbl (
2737 p_att_table IN hxc_generic_retrieval_pkg.t_time_attribute,
2738 p_tbb_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
2739 p_start_position IN PLS_INTEGER
2740 )
2741 RETURN pay_element_types_f.element_type_id%TYPE
2742 AS
2743 l_proc proc_name;
2744
2745 l_att_idx PLS_INTEGER
2746 := NVL (p_start_position, p_att_table.FIRST);
2747 l_element_id_found BOOLEAN := FALSE;
2748 l_element_type_id pay_element_types_f.element_type_id%TYPE := NULL;
2749 BEGIN
2750 g_debug :=hr_utility.debug_enabled;
2751 if g_debug then
2752 l_proc := g_package
2753 || 'find_element_id_in_attr_tbl';
2754 hr_utility.set_location ( 'Entering '
2755 || l_proc, 10);
2756 end if;
2757 LOOP
2758 EXIT WHEN ( NOT p_att_table.EXISTS (l_att_idx)
2759 OR (p_att_table (l_att_idx).bb_id <> p_tbb_id)
2760 OR (l_element_id_found)
2761 );
2762
2763 IF (attribute_is_element (p_att_table (l_att_idx)))
2764 THEN
2765 l_element_type_id := element_type_id (p_att_table (l_att_idx));
2766 l_element_id_found := TRUE;
2767 END IF;
2768
2769 l_att_idx := p_att_table.NEXT (l_att_idx);
2770 END LOOP;
2771
2772 if g_debug then
2773 hr_utility.set_location ( ' returning '
2774 || l_element_type_id, 30);
2775 hr_utility.set_location ( 'Leaving '
2776 || l_proc, 100);
2777 end if;
2778 RETURN l_element_type_id;
2779 END find_element_id_in_attr_tbl;
2780
2781 PROCEDURE find_other_in_attr_tbl (
2782 p_bg_id IN pay_batch_headers.business_group_id%TYPE,
2783 p_att_table IN hxc_generic_retrieval_pkg.t_time_attribute,
2784 p_tbb_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
2785 p_element_type_id IN pay_element_types_f.element_type_id%TYPE,
2786 p_cost_flex_id IN per_business_groups_perf.cost_allocation_structure%TYPE,
2787 p_effective_date IN pay_element_types_f.effective_start_date%TYPE,
2788 p_start_position IN PLS_INTEGER,
2789 p_ending_position OUT NOCOPY PLS_INTEGER, -- will return NULL if end-of-table was reached
2790 p_bee_rec IN OUT NOCOPY bee_rec
2791 )
2792 AS
2793 l_proc proc_name ;
2794 l_att_idx PLS_INTEGER
2795 := NVL (p_start_position, p_att_table.FIRST);
2796
2797 FUNCTION asg_id (
2798 p_bg_id IN pay_batch_headers.business_group_id%TYPE,
2799 p_effective_date IN DATE,
2800 p_bee_rec IN bee_rec
2801 )
2802 RETURN per_all_assignments_f.assignment_id%TYPE
2803 AS
2804 l_proc proc_name ;
2805 CURSOR csr_asg_id (
2806 p_bg_id pay_batch_headers.business_group_id%TYPE,
2807 p_effective_date DATE,
2808 p_assignment_number per_all_assignments_f.assignment_number%TYPE
2809 )
2810 IS
2811 SELECT assignment_id
2812 FROM per_all_assignments_f
2813 WHERE assignment_number = p_assignment_number
2814 AND business_group_id = p_bg_id
2815 AND p_effective_date BETWEEN effective_start_date
2816 AND effective_end_date;
2817
2818 l_asg_id per_all_assignments_f.assignment_id%TYPE;
2819 BEGIN
2820
2821 if g_debug then
2822 l_proc := g_package
2823 || 'asg_id';
2824 hr_utility.set_location ( 'Entering '
2825 || l_proc, 10);
2826 hr_utility.set_location ( 'p_bg_id = '
2827 || p_bg_id, 20);
2828 hr_utility.set_location ( 'p_effective_date = '
2829 || p_effective_date, 30);
2830 hr_utility.set_location ( 'p_bee_rec.pay_batch_line.assignment_number = '
2831 || p_bee_rec.pay_batch_line.assignment_number, 40);
2832 end if;
2833 OPEN csr_asg_id (p_bg_id,
2834 p_effective_date,
2835 p_bee_rec.pay_batch_line.assignment_number
2836 );
2837 FETCH csr_asg_id INTO l_asg_id;
2838 CLOSE csr_asg_id;
2839 if g_debug then
2840 hr_utility.set_location ( ' Returning Asg ID: '
2841 || l_asg_id, 20);
2842 hr_utility.set_location ( 'Leaving '
2843 || l_proc, 100);
2844 end if;
2845 RETURN l_asg_id;
2846 END asg_id;
2847
2848 FUNCTION asg_num (p_effective_date IN DATE, p_bee_rec IN bee_rec)
2849 RETURN per_all_assignments_f.assignment_number%TYPE
2850 AS
2851 l_proc proc_name ;
2852 CURSOR csr_asg_num (
2853 p_effective_date DATE,
2854 p_assignment_id per_all_assignments_f.assignment_id%TYPE
2855 )
2856 IS
2857 SELECT assignment_number
2858 FROM per_all_assignments_f
2859 WHERE assignment_id = p_assignment_id
2860 AND p_effective_date BETWEEN effective_start_date
2861 AND effective_end_date;
2862
2863 l_asg_num per_all_assignments_f.assignment_number%TYPE;
2864 BEGIN
2865
2866 if g_debug then
2867 l_proc := g_package
2868 || 'asg_num';
2869 hr_utility.set_location ( 'Entering '
2870 || l_proc, 10);
2871 end if;
2872 OPEN csr_asg_num (p_effective_date,
2873 p_bee_rec.pay_batch_line.assignment_id
2874 );
2875 FETCH csr_asg_num INTO l_asg_num;
2876 CLOSE csr_asg_num;
2877 if g_debug then
2878 hr_utility.set_location ( ' Returning Asg Num: '
2879 || l_asg_num, 20);
2880 hr_utility.set_location ( 'Leaving '
2881 || l_proc, 100);
2882 end if;
2883 RETURN l_asg_num;
2884 END asg_num;
2885 BEGIN
2886 g_debug :=hr_utility.debug_enabled;
2887 if g_debug then
2888 l_proc := g_package
2889 || 'find_other_in_attr_tbl';
2890 hr_utility.set_location ( 'Entering '
2891 || l_proc, 10);
2892 end if;
2893 LOOP
2894 EXIT WHEN ( NOT p_att_table.EXISTS (l_att_idx)
2895 OR (p_att_table (l_att_idx).bb_id <> p_tbb_id)
2896 );
2897
2898 -- ADDED FOR PERF ...
2899 IF (p_att_table (l_att_idx).VALUE IS NOT NULL)
2900 THEN
2901 IF (attribute_is_input_value (p_att_table (l_att_idx)))
2902 THEN
2903 convert_attr_to_ivs (p_attr_rec => p_att_table (l_att_idx
2904 ),
2905 p_element_type_id => p_element_type_id,
2906 p_effective_date => p_effective_date,
2907 p_bee_rec => p_bee_rec
2908 );
2909 END IF;
2910
2911 IF (attribute_is_cost_segment (p_att_table (l_att_idx)))
2912 THEN
2913 convert_attr_to_costsegment (p_attr_rec => p_att_table (l_att_idx
2914 ),
2915 p_cost_flex_id => p_cost_flex_id,
2916 p_bee_rec => p_bee_rec
2917 );
2918 END IF;
2919
2920 IF (attribute_is_asg_id (p_att_table (l_att_idx)))
2921 THEN
2922 p_bee_rec.pay_batch_line.assignment_id :=
2923 p_att_table (l_att_idx).VALUE;
2924 p_bee_rec.pay_batch_line.assignment_number :=
2925 asg_num (p_effective_date, p_bee_rec);
2926 END IF;
2927
2928 IF (attribute_is_asg_num (p_att_table (l_att_idx)))
2929 THEN
2930 p_bee_rec.pay_batch_line.assignment_number :=
2931 p_att_table (l_att_idx).VALUE;
2932 p_bee_rec.pay_batch_line.assignment_id :=
2933 asg_id (p_bg_id, p_effective_date, p_bee_rec);
2934 END IF;
2935 END IF;
2936
2937 l_att_idx := p_att_table.NEXT (l_att_idx);
2938 END LOOP;
2939
2940 p_ending_position := l_att_idx;
2941 if g_debug then
2942 hr_utility.set_location ( ' returning p_ending_position '
2943 || p_ending_position,
2944 20
2945 );
2946 hr_utility.set_location ( 'Leaving '
2947 || l_proc, 100);
2948 end if;
2949 END find_other_in_attr_tbl;
2950
2951 FUNCTION skip_attributes (
2952 p_att_table IN hxc_generic_retrieval_pkg.t_time_attribute,
2953 p_tbb_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
2954 p_start_position IN PLS_INTEGER
2955 )
2956 RETURN PLS_INTEGER
2957 AS
2958 l_proc proc_name ;
2959 l_att_idx PLS_INTEGER
2960 := NVL (p_start_position, p_att_table.FIRST);
2961 BEGIN
2962 g_debug :=hr_utility.debug_enabled;
2963 if g_debug then
2964 l_proc := g_package
2965 || 'skip_attributes';
2966 hr_utility.set_location ( 'Entering '
2967 || l_proc, 10);
2968 end if;
2969 LOOP
2970 EXIT WHEN ( NOT p_att_table.EXISTS (l_att_idx)
2971 OR (p_att_table (l_att_idx).bb_id <> p_tbb_id)
2972 );
2973 l_att_idx := p_att_table.NEXT (l_att_idx);
2974 END LOOP;
2975
2976 if g_debug then
2977 hr_utility.set_location ( ' returning ending position '
2978 || l_att_idx,
2979 20
2980 );
2981 hr_utility.set_location ( 'Leaving '
2982 || l_proc, 100);
2983 end if;
2984 RETURN l_att_idx;
2985 END skip_attributes;
2986
2987 /* PROCEDURE find_asg_in_attr_tbl (
2988 p_att_table IN hxc_generic_retrieval_pkg.t_time_attribute,
2989 p_tbb_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
2990 p_start_position IN PLS_INTEGER,
2991 p_bee_rec IN OUT NOCOPY bee_rec
2992 )
2993 AS
2994 l_proc proc_name := g_package
2995 || 'find_asg_in_attr_tbl';
2996 l_att_idx PLS_INTEGER := p_start_position;
2997 l_asg_id_found BOOLEAN := FALSE;
2998 l_asg_num_found BOOLEAN := FALSE;
2999 BEGIN
3000 g_debug :=hr_utility.debug_enabled;
3001 if g_debug then
3002 hr_utility.set_location ( 'Entering '
3003 || l_proc, 10);
3004 end if;
3005 LOOP
3006 EXIT WHEN ( NOT p_att_table.EXISTS (l_att_idx)
3007 OR (p_att_table (l_att_idx).bb_id <> p_tbb_id)
3008 OR (l_asg_id_found AND l_asg_num_found)
3009 );
3010
3011 IF (attribute_is_asg_id (p_att_table (l_att_idx)))
3012 THEN
3013 p_bee_rec.pay_batch_line.assignment_id :=
3014 p_att_table (l_att_idx).VALUE;
3015 l_asg_id_found := TRUE;
3016 ELSIF (attribute_is_asg_num (p_att_table (l_att_idx)))
3017 THEN
3018 p_bee_rec.pay_batch_line.assignment_number :=
3019 p_att_table (l_att_idx).VALUE;
3020 l_asg_num_found := TRUE;
3021 END IF;
3022
3023 l_att_idx := p_att_table.NEXT (l_att_idx);
3024 END LOOP;
3025
3026 if g_debug then
3027 hr_utility.set_location ( 'Leaving '
3028 || l_proc, 100);
3029 end if;
3030 END find_asg_in_attr_tbl;
3031 */
3032 FUNCTION cost_flex_structure_id (
3033 p_business_group_id IN per_all_organization_units.business_group_id%TYPE
3034 )
3035 RETURN per_business_groups_perf.cost_allocation_structure%TYPE
3036 IS
3037 l_proc proc_name;
3038
3039 l_cost_allocation_structure_id per_business_groups_perf.cost_allocation_structure%TYPE;
3040 BEGIN
3041 g_debug :=hr_utility.debug_enabled;
3042 if g_debug then
3043 l_proc := g_package
3044 || 'cost_flex_structure_id';
3045 hr_utility.set_location ( 'Entering '
3046 || l_proc, 10);
3047 end if;
3048 pay_paywsqee_pkg.populate_context_items (p_business_group_id,
3049 l_cost_allocation_structure_id
3050 );
3051 if g_debug then
3052 hr_utility.set_location ( ' Returning '
3053 || l_cost_allocation_structure_id,
3054 20
3055 );
3056 hr_utility.set_location ( 'Leaving '
3057 || l_proc, 30);
3058 end if;
3059 RETURN l_cost_allocation_structure_id;
3060 END cost_flex_structure_id;
3061
3062 FUNCTION costflex_value (
3063 p_id_flex_num IN fnd_id_flex_segments.id_flex_num%TYPE,
3064 p_segment_name IN fnd_id_flex_segments.application_column_name%TYPE,
3065 p_validation_type IN fnd_flex_value_sets.validation_type%TYPE
3066 DEFAULT g_independant,
3067 p_flex_value_id IN hxc_time_attributes.attribute1%TYPE
3068 )
3069 RETURN fnd_flex_values.flex_value%TYPE
3070 IS
3071 l_proc proc_name ;
3072
3073 CURSOR csr_flex_value (
3074 p_id_flex_num fnd_id_flex_segments.id_flex_num%TYPE,
3075 p_segment_name fnd_id_flex_segments.application_column_name%TYPE,
3076 p_validation_type fnd_flex_value_sets.validation_type%TYPE,
3077 p_flex_value_id fnd_flex_values.flex_value_id%TYPE
3078 )
3079 IS
3080 SELECT ffv.flex_value
3081 FROM fnd_id_flex_segments fifs,
3082 fnd_flex_value_sets ffvs,
3083 fnd_flex_values ffv
3084 WHERE fifs.application_id = g_pay_app_id
3085 AND fifs.id_flex_code = g_cost_flex_code
3086 AND fifs.id_flex_num = p_id_flex_num
3087 AND fifs.application_column_name = p_segment_name
3088 AND fifs.flex_value_set_id = ffvs.flex_value_set_id
3089 AND ffvs.validation_type = p_validation_type
3090 AND ffvs.flex_value_set_id = ffv.flex_value_set_id
3091 AND ffv.flex_value_id = p_flex_value_id;
3092
3093 l_costflex_value per_business_groups_perf.cost_allocation_structure%TYPE;
3094 l_found_in_cache BOOLEAN := FALSE;
3095 BEGIN
3096 g_debug :=hr_utility.debug_enabled;
3097 if g_debug then
3098 l_proc := g_package
3099 || 'costflex_value';
3100 hr_utility.set_location ( 'Entering '
3101 || l_proc, 10);
3102 end if;
3103 IF (isnumber (p_flex_value_id))
3104 THEN
3105 IF ((caching) AND (g_flex_values.EXISTS (p_flex_value_id)))
3106 THEN
3107 IF ( (p_segment_name =
3108 g_flex_values (p_flex_value_id).segment_name
3109 )
3110 AND (p_validation_type =
3111 g_flex_values (p_flex_value_id).validation_type
3112 )
3113 AND (p_id_flex_num =
3114 g_flex_values (p_flex_value_id).id_flex_num
3115 )
3116 )
3117 THEN
3118 l_costflex_value := g_flex_values (p_flex_value_id).flex_value;
3119 l_found_in_cache := TRUE;
3120 END IF;
3121 END IF;
3122
3123 IF (NOT l_found_in_cache)
3124 THEN
3125 IF (csr_flex_value%ISOPEN)
3126 THEN
3127 CLOSE csr_flex_value;
3128 END IF;
3129
3130 OPEN csr_flex_value (p_id_flex_num,
3131 p_segment_name,
3132 p_validation_type,
3133 p_flex_value_id
3134 );
3135 FETCH csr_flex_value INTO l_costflex_value;
3136
3137 IF (csr_flex_value%NOTFOUND)
3138 THEN
3139 l_costflex_value := p_flex_value_id;
3140 ELSE
3141 IF (caching)
3142 THEN
3143 g_flex_values (p_flex_value_id).segment_name :=
3144 p_segment_name;
3145 g_flex_values (p_flex_value_id).validation_type :=
3146 p_validation_type;
3147 g_flex_values (p_flex_value_id).id_flex_num :=
3148 p_id_flex_num;
3149 g_flex_values (p_flex_value_id).flex_value :=
3150 l_costflex_value;
3151 END IF;
3152 END IF;
3153
3154 CLOSE csr_flex_value;
3155 END IF;
3156 ELSE
3157 l_costflex_value := p_flex_value_id;
3158 END IF;
3159
3160 if g_debug then
3161 hr_utility.set_location ( ' returning l_costflex_value = '
3162 || l_costflex_value,
3163 20
3164 );
3165 hr_utility.set_location ( 'Leaving '
3166 || l_proc, 30);
3167 end if;
3168 RETURN l_costflex_value;
3169 END costflex_value;
3170
3171 FUNCTION costflex_concat_segments (
3172 p_cost_allocation_keyflex_id IN pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE
3173 )
3174 RETURN pay_cost_allocation_keyflex.concatenated_segments%TYPE
3175 IS
3176 l_proc proc_name;
3177
3178
3179 CURSOR csr_costflex_concat_segments (
3180 p_cost_allocation_keyflex_id pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE
3181 )
3182 IS
3183 SELECT concatenated_segments
3184 FROM pay_cost_allocation_keyflex
3185 WHERE cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
3186
3187 l_costflex_concat_segments pay_cost_allocation_keyflex.concatenated_segments%TYPE;
3188 l_found_in_cache BOOLEAN := FALSE;
3189 BEGIN
3190 g_debug :=hr_utility.debug_enabled;
3191 if g_debug then
3192 l_proc := g_package
3193 || 'costflex_concat_segments';
3194 hr_utility.set_location ( 'Entering '
3195 || l_proc, 10);
3196 end if;
3197 IF ( (caching)
3198 AND (g_concatenated_segments.EXISTS (p_cost_allocation_keyflex_id))
3199 )
3200 THEN
3201 l_costflex_concat_segments :=
3202 g_concatenated_segments (p_cost_allocation_keyflex_id).concatenated_segment;
3203 l_found_in_cache := TRUE;
3204 END IF;
3205
3206 IF (NOT l_found_in_cache)
3207 THEN
3208 IF (csr_costflex_concat_segments%ISOPEN)
3209 THEN
3210 CLOSE csr_costflex_concat_segments;
3211 END IF;
3212
3213 OPEN csr_costflex_concat_segments (p_cost_allocation_keyflex_id);
3214 FETCH csr_costflex_concat_segments INTO l_costflex_concat_segments;
3215
3216 IF ((csr_costflex_concat_segments%FOUND) AND (caching))
3217 THEN
3218 g_concatenated_segments (p_cost_allocation_keyflex_id).concatenated_segment :=
3219 l_costflex_concat_segments;
3220 END IF;
3221
3222 CLOSE csr_costflex_concat_segments;
3223 END IF;
3224
3225 if g_debug then
3226 hr_utility.set_location ( ' returning '
3227 || l_costflex_concat_segments,
3228 20
3229 );
3230 hr_utility.set_location ( 'Leaving '
3231 || l_proc, 30);
3232 end if;
3233 RETURN l_costflex_concat_segments;
3234 END costflex_concat_segments;
3235
3236 FUNCTION cost_segments_all_null (
3237 p_segment_1 IN pay_cost_allocation_keyflex.segment1%TYPE,
3238 p_segment_2 IN pay_cost_allocation_keyflex.segment2%TYPE,
3239 p_segment_3 IN pay_cost_allocation_keyflex.segment3%TYPE,
3240 p_segment_4 IN pay_cost_allocation_keyflex.segment4%TYPE,
3241 p_segment_5 IN pay_cost_allocation_keyflex.segment5%TYPE,
3242 p_segment_6 IN pay_cost_allocation_keyflex.segment6%TYPE,
3243 p_segment_7 IN pay_cost_allocation_keyflex.segment7%TYPE,
3244 p_segment_8 IN pay_cost_allocation_keyflex.segment8%TYPE,
3245 p_segment_9 IN pay_cost_allocation_keyflex.segment9%TYPE,
3246 p_segment_10 IN pay_cost_allocation_keyflex.segment10%TYPE,
3247 p_segment_11 IN pay_cost_allocation_keyflex.segment11%TYPE,
3248 p_segment_12 IN pay_cost_allocation_keyflex.segment12%TYPE,
3249 p_segment_13 IN pay_cost_allocation_keyflex.segment13%TYPE,
3250 p_segment_14 IN pay_cost_allocation_keyflex.segment14%TYPE,
3251 p_segment_15 IN pay_cost_allocation_keyflex.segment15%TYPE,
3252 p_segment_16 IN pay_cost_allocation_keyflex.segment16%TYPE,
3253 p_segment_17 IN pay_cost_allocation_keyflex.segment17%TYPE,
3254 p_segment_18 IN pay_cost_allocation_keyflex.segment18%TYPE,
3255 p_segment_19 IN pay_cost_allocation_keyflex.segment19%TYPE,
3256 p_segment_20 IN pay_cost_allocation_keyflex.segment20%TYPE,
3257 p_segment_21 IN pay_cost_allocation_keyflex.segment21%TYPE,
3258 p_segment_22 IN pay_cost_allocation_keyflex.segment22%TYPE,
3259 p_segment_23 IN pay_cost_allocation_keyflex.segment23%TYPE,
3260 p_segment_24 IN pay_cost_allocation_keyflex.segment24%TYPE,
3261 p_segment_25 IN pay_cost_allocation_keyflex.segment25%TYPE,
3262 p_segment_26 IN pay_cost_allocation_keyflex.segment26%TYPE,
3263 p_segment_27 IN pay_cost_allocation_keyflex.segment27%TYPE,
3264 p_segment_28 IN pay_cost_allocation_keyflex.segment28%TYPE,
3265 p_segment_29 IN pay_cost_allocation_keyflex.segment29%TYPE,
3266 p_segment_30 IN pay_cost_allocation_keyflex.segment30%TYPE
3267 )
3268 RETURN BOOLEAN
3269 IS
3270 l_proc proc_name;
3271
3272 l_cost_segments_all_null BOOLEAN := FALSE;
3273 BEGIN
3274 g_debug :=hr_utility.debug_enabled;
3275 if g_debug then
3276 l_proc := g_package
3277 || 'cost_segments_all_null';
3278 hr_utility.set_location ( 'Entering '
3279 || l_proc, 10);
3280 end if;
3281 IF ( (p_segment_1 IS NULL)
3282 AND (p_segment_2 IS NULL)
3283 AND (p_segment_3 IS NULL)
3284 AND (p_segment_4 IS NULL)
3285 AND (p_segment_5 IS NULL)
3286 AND (p_segment_6 IS NULL)
3287 AND (p_segment_7 IS NULL)
3288 AND (p_segment_8 IS NULL)
3289 AND (p_segment_9 IS NULL)
3290 AND (p_segment_10 IS NULL)
3291 AND (p_segment_11 IS NULL)
3292 AND (p_segment_12 IS NULL)
3293 AND (p_segment_13 IS NULL)
3294 AND (p_segment_14 IS NULL)
3295 AND (p_segment_15 IS NULL)
3296 AND (p_segment_16 IS NULL)
3297 AND (p_segment_17 IS NULL)
3298 AND (p_segment_18 IS NULL)
3299 AND (p_segment_19 IS NULL)
3300 AND (p_segment_20 IS NULL)
3301 AND (p_segment_21 IS NULL)
3302 AND (p_segment_22 IS NULL)
3303 AND (p_segment_23 IS NULL)
3304 AND (p_segment_24 IS NULL)
3305 AND (p_segment_25 IS NULL)
3306 AND (p_segment_26 IS NULL)
3307 AND (p_segment_27 IS NULL)
3308 AND (p_segment_28 IS NULL)
3309 AND (p_segment_29 IS NULL)
3310 AND (p_segment_30 IS NULL)
3311 )
3312 THEN
3313 l_cost_segments_all_null := TRUE;
3314 ELSE
3315 l_cost_segments_all_null := FALSE;
3316 END IF;
3317
3318 if g_debug then
3319 hr_utility.set_location ( 'Leaving '
3320 || l_proc, 100);
3321 end if;
3322 RETURN (l_cost_segments_all_null);
3323 END cost_segments_all_null;
3324
3325 FUNCTION cost_segments_all_null (p_bee_rec IN bee_rec)
3326 RETURN BOOLEAN
3327 IS
3328 l_proc proc_name;
3329
3330 l_cost_segments_all_null BOOLEAN := FALSE;
3331 BEGIN
3332 g_debug :=hr_utility.debug_enabled;
3333 if g_debug then
3334 l_proc := g_package
3335 || 'cost_segments_all_null';
3336 hr_utility.set_location ( 'Entering '
3337 || l_proc, 10);
3338 end if;
3339 l_cost_segments_all_null :=
3340 cost_segments_all_null (p_segment_1 => p_bee_rec.pay_batch_line.segment1,
3341 p_segment_2 => p_bee_rec.pay_batch_line.segment2,
3342 p_segment_3 => p_bee_rec.pay_batch_line.segment3,
3343 p_segment_4 => p_bee_rec.pay_batch_line.segment4,
3344 p_segment_5 => p_bee_rec.pay_batch_line.segment5,
3345 p_segment_6 => p_bee_rec.pay_batch_line.segment6,
3346 p_segment_7 => p_bee_rec.pay_batch_line.segment7,
3347 p_segment_8 => p_bee_rec.pay_batch_line.segment8,
3348 p_segment_9 => p_bee_rec.pay_batch_line.segment9,
3349 p_segment_10 => p_bee_rec.pay_batch_line.segment10,
3350 p_segment_11 => p_bee_rec.pay_batch_line.segment11,
3351 p_segment_12 => p_bee_rec.pay_batch_line.segment12,
3352 p_segment_13 => p_bee_rec.pay_batch_line.segment13,
3353 p_segment_14 => p_bee_rec.pay_batch_line.segment14,
3354 p_segment_15 => p_bee_rec.pay_batch_line.segment15,
3355 p_segment_16 => p_bee_rec.pay_batch_line.segment16,
3356 p_segment_17 => p_bee_rec.pay_batch_line.segment17,
3357 p_segment_18 => p_bee_rec.pay_batch_line.segment18,
3358 p_segment_19 => p_bee_rec.pay_batch_line.segment19,
3359 p_segment_20 => p_bee_rec.pay_batch_line.segment20,
3360 p_segment_21 => p_bee_rec.pay_batch_line.segment21,
3361 p_segment_22 => p_bee_rec.pay_batch_line.segment22,
3362 p_segment_23 => p_bee_rec.pay_batch_line.segment23,
3363 p_segment_24 => p_bee_rec.pay_batch_line.segment24,
3364 p_segment_25 => p_bee_rec.pay_batch_line.segment25,
3365 p_segment_26 => p_bee_rec.pay_batch_line.segment26,
3366 p_segment_27 => p_bee_rec.pay_batch_line.segment27,
3367 p_segment_28 => p_bee_rec.pay_batch_line.segment28,
3368 p_segment_29 => p_bee_rec.pay_batch_line.segment29,
3369 p_segment_30 => p_bee_rec.pay_batch_line.segment30
3370 );
3371 if g_debug then
3372 hr_utility.set_location ( 'Leaving '
3373 || l_proc, 100);
3374 end if;
3375 RETURN (l_cost_segments_all_null);
3376 END cost_segments_all_null;
3377
3378 FUNCTION cost_allocation_kff_id (
3379 p_business_group_id IN per_all_organization_units.business_group_id%TYPE,
3380 p_segment_1 IN pay_cost_allocation_keyflex.segment1%TYPE,
3381 p_segment_2 IN pay_cost_allocation_keyflex.segment2%TYPE,
3382 p_segment_3 IN pay_cost_allocation_keyflex.segment3%TYPE,
3383 p_segment_4 IN pay_cost_allocation_keyflex.segment4%TYPE,
3384 p_segment_5 IN pay_cost_allocation_keyflex.segment5%TYPE,
3385 p_segment_6 IN pay_cost_allocation_keyflex.segment6%TYPE,
3386 p_segment_7 IN pay_cost_allocation_keyflex.segment7%TYPE,
3387 p_segment_8 IN pay_cost_allocation_keyflex.segment8%TYPE,
3388 p_segment_9 IN pay_cost_allocation_keyflex.segment9%TYPE,
3389 p_segment_10 IN pay_cost_allocation_keyflex.segment10%TYPE,
3390 p_segment_11 IN pay_cost_allocation_keyflex.segment11%TYPE,
3391 p_segment_12 IN pay_cost_allocation_keyflex.segment12%TYPE,
3392 p_segment_13 IN pay_cost_allocation_keyflex.segment13%TYPE,
3393 p_segment_14 IN pay_cost_allocation_keyflex.segment14%TYPE,
3394 p_segment_15 IN pay_cost_allocation_keyflex.segment15%TYPE,
3395 p_segment_16 IN pay_cost_allocation_keyflex.segment16%TYPE,
3396 p_segment_17 IN pay_cost_allocation_keyflex.segment17%TYPE,
3397 p_segment_18 IN pay_cost_allocation_keyflex.segment18%TYPE,
3398 p_segment_19 IN pay_cost_allocation_keyflex.segment19%TYPE,
3399 p_segment_20 IN pay_cost_allocation_keyflex.segment20%TYPE,
3400 p_segment_21 IN pay_cost_allocation_keyflex.segment21%TYPE,
3401 p_segment_22 IN pay_cost_allocation_keyflex.segment22%TYPE,
3402 p_segment_23 IN pay_cost_allocation_keyflex.segment23%TYPE,
3403 p_segment_24 IN pay_cost_allocation_keyflex.segment24%TYPE,
3404 p_segment_25 IN pay_cost_allocation_keyflex.segment25%TYPE,
3405 p_segment_26 IN pay_cost_allocation_keyflex.segment26%TYPE,
3406 p_segment_27 IN pay_cost_allocation_keyflex.segment27%TYPE,
3407 p_segment_28 IN pay_cost_allocation_keyflex.segment28%TYPE,
3408 p_segment_29 IN pay_cost_allocation_keyflex.segment29%TYPE,
3409 p_segment_30 IN pay_cost_allocation_keyflex.segment30%TYPE
3410 )
3411 RETURN pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE
3412 IS
3413 l_proc proc_name;
3414
3415 l_cost_allocation_kff_id pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE;
3416 BEGIN
3417 g_debug :=hr_utility.debug_enabled;
3418 if g_debug then
3419 l_proc := g_package
3420 || 'cost_allocation_kff_id';
3421 hr_utility.set_location ( 'Entering '
3422 || l_proc, 10);
3423 end if;
3424 IF (NOT cost_segments_all_null (p_segment_1 => p_segment_1,
3425 p_segment_2 => p_segment_2,
3426 p_segment_3 => p_segment_3,
3427 p_segment_4 => p_segment_4,
3428 p_segment_5 => p_segment_5,
3429 p_segment_6 => p_segment_6,
3430 p_segment_7 => p_segment_7,
3431 p_segment_8 => p_segment_8,
3432 p_segment_9 => p_segment_9,
3433 p_segment_10 => p_segment_10,
3434 p_segment_11 => p_segment_11,
3435 p_segment_12 => p_segment_12,
3436 p_segment_13 => p_segment_13,
3437 p_segment_14 => p_segment_14,
3438 p_segment_15 => p_segment_15,
3439 p_segment_16 => p_segment_16,
3440 p_segment_17 => p_segment_17,
3441 p_segment_18 => p_segment_18,
3442 p_segment_19 => p_segment_19,
3443 p_segment_20 => p_segment_20,
3444 p_segment_21 => p_segment_21,
3445 p_segment_22 => p_segment_22,
3446 p_segment_23 => p_segment_23,
3447 p_segment_24 => p_segment_24,
3448 p_segment_25 => p_segment_25,
3449 p_segment_26 => p_segment_26,
3450 p_segment_27 => p_segment_27,
3451 p_segment_28 => p_segment_28,
3452 p_segment_29 => p_segment_29,
3453 p_segment_30 => p_segment_30
3454 )
3455 )
3456 THEN
3457 l_cost_allocation_kff_id :=
3458 hr_entry.maintain_cost_keyflex (p_cost_keyflex_structure => cost_flex_structure_id (p_business_group_id => p_business_group_id
3459 ),
3460 p_cost_allocation_keyflex_id => -1,
3461 p_concatenated_segments => NULL,
3462 p_summary_flag => 'N',
3463 p_start_date_active => NULL,
3464 p_end_date_active => NULL,
3465 p_segment1 => p_segment_1,
3466 p_segment2 => p_segment_2,
3467 p_segment3 => p_segment_3,
3468 p_segment4 => p_segment_4,
3469 p_segment5 => p_segment_5,
3470 p_segment6 => p_segment_6,
3471 p_segment7 => p_segment_7,
3472 p_segment8 => p_segment_8,
3473 p_segment9 => p_segment_9,
3474 p_segment10 => p_segment_10,
3475 p_segment11 => p_segment_11,
3476 p_segment12 => p_segment_12,
3477 p_segment13 => p_segment_13,
3478 p_segment14 => p_segment_14,
3479 p_segment15 => p_segment_15,
3480 p_segment16 => p_segment_16,
3481 p_segment17 => p_segment_17,
3482 p_segment18 => p_segment_18,
3483 p_segment19 => p_segment_19,
3484 p_segment20 => p_segment_20,
3485 p_segment21 => p_segment_21,
3486 p_segment22 => p_segment_22,
3487 p_segment23 => p_segment_23,
3488 p_segment24 => p_segment_24,
3489 p_segment25 => p_segment_25,
3490 p_segment26 => p_segment_26,
3491 p_segment27 => p_segment_27,
3492 p_segment28 => p_segment_28,
3493 p_segment29 => p_segment_29,
3494 p_segment30 => p_segment_30
3495 );
3496 ELSE
3497 l_cost_allocation_kff_id := NULL;
3498 END IF;
3499
3500 if g_debug then
3501 hr_utility.set_location ( ' returning '
3502 || l_cost_allocation_kff_id,
3503 20
3504 );
3505 hr_utility.set_location ( 'Leaving '
3506 || l_proc, 100);
3507 end if;
3508 RETURN (l_cost_allocation_kff_id);
3509 END cost_allocation_kff_id;
3510
3511 FUNCTION cost_allocation_kff_id (
3512 p_business_group_id IN per_all_organization_units.business_group_id%TYPE,
3513 p_bee_rec IN bee_rec
3514 )
3515 RETURN pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE
3516 IS
3517 l_proc proc_name;
3518
3519 l_cost_allocation_kff_id pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE;
3520 BEGIN
3521 g_debug :=hr_utility.debug_enabled;
3522 if g_debug then
3523 l_proc := g_package
3524 || 'cost_allocation_kff_id (p_bee_rec)';
3525 hr_utility.set_location ( 'Entering '
3526 || l_proc, 10);
3527 end if;
3528 l_cost_allocation_kff_id :=
3529 cost_allocation_kff_id (p_business_group_id => p_business_group_id,
3530 p_segment_1 => p_bee_rec.pay_batch_line.segment1,
3531 p_segment_2 => p_bee_rec.pay_batch_line.segment2,
3532 p_segment_3 => p_bee_rec.pay_batch_line.segment3,
3533 p_segment_4 => p_bee_rec.pay_batch_line.segment4,
3534 p_segment_5 => p_bee_rec.pay_batch_line.segment5,
3535 p_segment_6 => p_bee_rec.pay_batch_line.segment6,
3536 p_segment_7 => p_bee_rec.pay_batch_line.segment7,
3537 p_segment_8 => p_bee_rec.pay_batch_line.segment8,
3538 p_segment_9 => p_bee_rec.pay_batch_line.segment9,
3539 p_segment_10 => p_bee_rec.pay_batch_line.segment10,
3540 p_segment_11 => p_bee_rec.pay_batch_line.segment11,
3541 p_segment_12 => p_bee_rec.pay_batch_line.segment12,
3542 p_segment_13 => p_bee_rec.pay_batch_line.segment13,
3543 p_segment_14 => p_bee_rec.pay_batch_line.segment14,
3544 p_segment_15 => p_bee_rec.pay_batch_line.segment15,
3545 p_segment_16 => p_bee_rec.pay_batch_line.segment16,
3546 p_segment_17 => p_bee_rec.pay_batch_line.segment17,
3547 p_segment_18 => p_bee_rec.pay_batch_line.segment18,
3548 p_segment_19 => p_bee_rec.pay_batch_line.segment19,
3549 p_segment_20 => p_bee_rec.pay_batch_line.segment20,
3550 p_segment_21 => p_bee_rec.pay_batch_line.segment21,
3551 p_segment_22 => p_bee_rec.pay_batch_line.segment22,
3552 p_segment_23 => p_bee_rec.pay_batch_line.segment23,
3553 p_segment_24 => p_bee_rec.pay_batch_line.segment24,
3554 p_segment_25 => p_bee_rec.pay_batch_line.segment25,
3555 p_segment_26 => p_bee_rec.pay_batch_line.segment26,
3556 p_segment_27 => p_bee_rec.pay_batch_line.segment27,
3557 p_segment_28 => p_bee_rec.pay_batch_line.segment28,
3558 p_segment_29 => p_bee_rec.pay_batch_line.segment29,
3559 p_segment_30 => p_bee_rec.pay_batch_line.segment30
3560 );
3561 if g_debug then
3562 hr_utility.set_location ( ' returning '
3563 || l_cost_allocation_kff_id,
3564 20
3565 );
3566 hr_utility.set_location ( 'Leaving '
3567 || l_proc, 100);
3568 end if;
3569 RETURN (l_cost_allocation_kff_id);
3570 END cost_allocation_kff_id;
3571
3572 FUNCTION hours_factor (p_is_old IN BOOLEAN)
3573 RETURN NUMBER
3574 IS
3575 l_proc proc_name ;
3576 l_hours_factor NUMBER (1);
3577 BEGIN
3578 g_debug :=hr_utility.debug_enabled;
3579 if g_debug then
3580 l_proc := g_package
3581 || 'hours_factor';
3582 hr_utility.set_location ( 'Entering '
3583 || l_proc, 10);
3584 end if;
3585 IF (p_is_old)
3586 THEN
3587 l_hours_factor := -1;
3588 ELSE
3589 l_hours_factor := 1;
3590 END IF;
3591
3592 if g_debug then
3593 hr_utility.set_location ( ' Returning '
3594 || l_hours_factor, 20);
3595 hr_utility.set_location ( 'Leaving '
3596 || l_proc, 30);
3597 end if;
3598 RETURN l_hours_factor;
3599 END hours_factor;
3600
3601 /* These 3 procedures were commented out because the pipes were causing
3602 issues with the SGA. The pipe grew out-of-control for no apparent reason.
3603 Anyway, for now, we will NOT allow multithreading of this process so we do
3604 not need pipes!!!
3605 */
3606 /* PROCEDURE write_pipe_batch_info (p_batch_info IN piped_batch_info_table)
3607 IS
3608 l_proc proc_name := g_package
3609 || 'write_pipe_batch_info';
3610 l_send_message_status INTEGER;
3611 l_batch_info_idx PLS_INTEGER;
3612 BEGIN
3613 g_debug :=hr_utility.debug_enabled;
3614 if g_debug then
3615 hr_utility.set_location ( 'Entering '
3616 || l_proc, 10);
3617 end if;
3618 l_batch_info_idx := p_batch_info.FIRST;
3619 DBMS_PIPE.reset_buffer;
3620
3621 LOOP
3622 EXIT WHEN NOT p_batch_info.EXISTS (l_batch_info_idx);
3623 DBMS_PIPE.pack_message (p_batch_info (l_batch_info_idx).batch_ref);
3624 DBMS_PIPE.pack_message (
3625 p_batch_info (l_batch_info_idx).business_group_id
3626 );
3627 DBMS_PIPE.pack_message (
3628 p_batch_info (l_batch_info_idx).free_batch_suffix
3629 );
3630 l_batch_info_idx := p_batch_info.NEXT (l_batch_info_idx);
3631 END LOOP;
3632
3633 l_send_message_status :=
3634 DBMS_PIPE.send_message (
3635 pipename=> g_total_lines_pipe_name,
3636 TIMEOUT=> 0
3637 );
3638
3639 IF l_send_message_status != 0
3640 THEN
3641 NULL; -- RAISE ERROR
3642 END IF;
3643
3644 if g_debug then
3645 hr_utility.set_location ( 'Leaving '
3646 || l_proc, 100);
3647 end if;
3648 END write_pipe_batch_info;
3649
3650 FUNCTION read_pipe_batch_info
3651 RETURN piped_batch_info_table
3652 IS
3653 l_proc proc_name := g_package
3654 || 'read_pipe_batch_info';
3655 l_recieve_message_status INTEGER;
3656 l_batch_info piped_batch_info_table;
3657 l_batch_info_idx PLS_INTEGER := 1;
3658 e_pipe_empty EXCEPTION;
3659 PRAGMA EXCEPTION_INIT (e_pipe_empty, -6556);
3660 BEGIN
3661 g_debug :=hr_utility.debug_enabled;
3662 if g_debug then
3663 hr_utility.set_location ( 'Entering '
3664 || l_proc, 10);
3665 end if;
3666 l_recieve_message_status :=
3667 DBMS_PIPE.receive_message (
3668 pipename=> g_total_lines_pipe_name,
3669 TIMEOUT=> 0
3670 );
3671
3672 LOOP
3673 DBMS_PIPE.unpack_message (l_batch_info (l_batch_info_idx).batch_ref);
3674 DBMS_PIPE.unpack_message (
3675 l_batch_info (l_batch_info_idx).business_group_id
3676 );
3677 DBMS_PIPE.unpack_message (
3678 l_batch_info (l_batch_info_idx).free_batch_suffix
3679 );
3680
3681 l_batch_info_idx := l_batch_info_idx
3682 + 1;
3683 END LOOP;
3684 EXCEPTION
3685 WHEN e_pipe_empty
3686 THEN
3687 if g_debug then
3688 hr_utility.set_location (
3689 ' returning '
3690 || l_batch_info.COUNT
3691 || ' batch_info lines',
3692 20
3693 );
3694 hr_utility.set_location ( 'Leaving '
3695 || l_proc, 100);
3696 end if;
3697 DBMS_PIPE.reset_buffer;
3698 DBMS_PIPE.PURGE (hxt_interface_utilities.g_total_lines_pipe_name);
3699 RETURN l_batch_info;
3700 END read_pipe_batch_info;
3701
3702 FUNCTION purge_pipe
3703 RETURN BOOLEAN
3704 AS
3705 cannot_use_pipe EXCEPTION;
3706 PRAGMA EXCEPTION_INIT (cannot_use_pipe, -23322);
3707 l_purged BOOLEAN := FALSE;
3708 BEGIN
3709 DBMS_PIPE.PURGE (hxt_interface_utilities.g_total_lines_pipe_name);
3710 l_purged := TRUE;
3711 RETURN l_purged;
3712 EXCEPTION
3713 WHEN cannot_use_pipe
3714 THEN
3715 RETURN l_purged;
3716 WHEN OTHERS
3717 THEN
3718 RETURN l_purged;
3719 END purge_pipe;
3720 */
3721 FUNCTION free_batch_suffix (
3722 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
3723 p_bg_id IN pay_batch_headers.business_group_id%TYPE
3724 )
3725 RETURN NUMBER
3726 IS
3727 l_proc proc_name ;
3728 l_batch_info_idx PLS_INTEGER;
3729 l_batch_ref_found BOOLEAN := FALSE;
3730 l_free_batch_suffix NUMBER;
3731 BEGIN
3732 g_debug :=hr_utility.debug_enabled;
3733 if g_debug then
3734 l_proc := g_package
3735 || 'free_batch_suffix';
3736 hr_utility.set_location ( 'Entering '
3737 || l_proc, 10);
3738 end if;
3739 l_batch_info_idx := g_batch_info.FIRST;
3740
3741 IF (l_batch_info_idx IS NOT NULL)
3742 THEN
3743 LOOP
3744 EXIT WHEN NOT (g_batch_info.EXISTS (l_batch_info_idx))
3745 OR l_batch_ref_found;
3746
3747 IF ( (g_batch_info (l_batch_info_idx).batch_ref =
3748 p_batch_reference
3749 )
3750 AND (g_batch_info (l_batch_info_idx).business_group_id =
3751 p_bg_id
3752 )
3753 AND (g_batch_info (l_batch_info_idx).request_id =
3754 conc_request_id_suffix
3755 )
3756 )
3757 THEN
3758 l_free_batch_suffix :=
3759 g_batch_info (l_batch_info_idx).free_batch_suffix;
3760 g_batch_info (l_batch_info_idx).free_batch_suffix :=
3761 l_free_batch_suffix
3762 + 1;
3763 l_batch_ref_found := TRUE;
3764 END IF;
3765
3766 l_batch_info_idx := g_batch_info.NEXT (l_batch_info_idx);
3767 END LOOP;
3768 END IF;
3769
3770 IF (NOT l_batch_ref_found)
3771 THEN
3772 if g_debug then
3773 hr_utility.set_location (' new batch reference', 20);
3774 end if;
3775 l_batch_info_idx := NVL (g_batch_info.LAST, 0)
3776 + 1;
3777 g_batch_info (l_batch_info_idx).batch_ref := p_batch_reference;
3778 g_batch_info (l_batch_info_idx).business_group_id := p_bg_id;
3779 g_batch_info (l_batch_info_idx).request_id := conc_request_id_suffix;
3780 l_free_batch_suffix := 1;
3781 g_batch_info (l_batch_info_idx).free_batch_suffix :=
3782 l_free_batch_suffix
3783 + 1;
3784 END IF;
3785
3786 if g_debug then
3787 hr_utility.set_location ( 'Leaving '
3788 || l_proc, 100);
3789 end if;
3790 RETURN l_free_batch_suffix;
3791 END free_batch_suffix;
3792
3793 -- Action Changed Deleted
3794 -- --------------------------- ------- -------
3795 -- new TBB N N
3796 -- changed TBB (never xfered) N N
3797 -- changed TBB (xfered) Y N
3798 -- DELETED (never xfered) N Y
3799 -- DELETED (xfered) Y Y
3800 -- --------------------------- ------- -------
3801 FUNCTION is_changed (
3802 p_tbb_rec IN hxc_generic_retrieval_pkg.r_building_blocks
3803 )
3804 RETURN BOOLEAN
3805 AS
3806 l_proc proc_name ;
3807 l_is_changed BOOLEAN := FALSE;
3808 BEGIN
3809 g_debug :=hr_utility.debug_enabled;
3810 if g_debug then
3811 l_proc := g_package
3812 || 'is_changed';
3813 hr_utility.set_location ( 'Entering '
3814 || l_proc, 10);
3815 end if;
3816 IF (UPPER (p_tbb_rec.changed) = g_tbb_changed)
3817 THEN
3818 l_is_changed := TRUE;
3819 if g_debug then
3820 hr_utility.set_location ( ' TBB '
3821 || p_tbb_rec.bb_id
3822 || ' changed',
3823 20
3824 );
3825 end if;
3826 ELSE
3827 l_is_changed := FALSE;
3828 END IF;
3829
3830 if g_debug then
3831 hr_utility.set_location ( 'Leaving '
3832 || l_proc, 40);
3833 end if;
3834 RETURN l_is_changed;
3835 END is_changed;
3836
3837 FUNCTION is_deleted (
3838 p_tbb_rec IN hxc_generic_retrieval_pkg.r_building_blocks
3839 )
3840 RETURN BOOLEAN
3841 AS
3842 l_proc proc_name ;
3843 l_is_deleted BOOLEAN := FALSE;
3844 BEGIN
3845 g_debug :=hr_utility.debug_enabled;
3846 if g_debug then
3847 l_proc := g_package
3848 || 'is_deleted';
3849 hr_utility.set_location ( 'Entering '
3850 || l_proc, 10);
3851 end if;
3852 IF (UPPER (p_tbb_rec.deleted) = g_tbb_deleted)
3853 THEN
3854 l_is_deleted := TRUE;
3855 if g_debug then
3856 hr_utility.set_location ( ' TBB '
3857 || p_tbb_rec.bb_id
3858 || ' deleted',
3859 20
3860 );
3861 end if;
3862 ELSE
3863 l_is_deleted := FALSE;
3864 END IF;
3865
3866 if g_debug then
3867 hr_utility.set_location ( 'Leaving '
3868 || l_proc, 40);
3869 end if;
3870 RETURN l_is_deleted;
3871 END is_deleted;
3872
3873 FUNCTION is_in_sync (
3874 p_check_tbb_id hxc_time_building_blocks.time_building_block_id%TYPE,
3875 p_against_tbb_id hxc_time_building_blocks.time_building_block_id%TYPE
3876 )
3877 RETURN BOOLEAN
3878 AS
3879 l_proc proc_name ;
3880 l_is_in_sync BOOLEAN := FALSE;
3881 BEGIN
3882 g_debug :=hr_utility.debug_enabled;
3883 if g_debug then
3884 l_proc := g_package
3885 || 'is_in_sync';
3886 hr_utility.set_location ( 'Entering '
3887 || l_proc, 10);
3888 end if;
3889 IF (p_check_tbb_id = p_against_tbb_id)
3890 THEN
3891 l_is_in_sync := TRUE;
3892 ELSE
3893 l_is_in_sync := FALSE;
3894 if g_debug then
3895 hr_utility.set_location ( p_check_tbb_id
3896 || ' is NOT in sync with TBB '
3897 || p_against_tbb_id,
3898 30
3899 );
3900 end if;
3901 END IF;
3902
3903 if g_debug then
3904 hr_utility.set_location ( 'Leaving '
3905 || l_proc, 40);
3906 end if;
3907 RETURN l_is_in_sync;
3908 END is_in_sync;
3909
3910
3911
3912 FUNCTION get_geocode_from_attr_tab (
3913 p_att_table IN hxc_generic_retrieval_pkg.t_time_attribute,
3914 p_tbb_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
3915 p_start_position IN PLS_INTEGER
3916 )
3917 RETURN VARCHAR2
3918 AS
3919 l_proc proc_name;
3920
3921 l_att_idx PLS_INTEGER
3922 := NVL (p_start_position, p_att_table.FIRST);
3923 l_jurisdiction_code VARCHAR2(11):=NULL;
3924 l_state_name pay_us_states.state_name%TYPE;
3925 l_county_name pay_us_counties.county_name%TYPE;
3926 l_city_name pay_us_city_names.city_name%TYPE;
3927 l_zip_code pay_us_zip_codes.zip_start%TYPE;
3928
3929 BEGIN
3930 g_debug :=hr_utility.debug_enabled;
3931 if g_debug then
3932 l_proc := g_package
3933 || 'get_geocode_from_attr_tab';
3934 hr_utility.set_location ( 'Entering '
3935 || l_proc, 10);
3936 end if;
3937 LOOP
3938 EXIT WHEN ( NOT p_att_table.EXISTS (l_att_idx)
3939 );
3940 IF( p_att_table (l_att_idx).bb_id = p_tbb_id) THEN
3941
3942 IF(p_att_table(l_att_idx).field_name='NA_STATE_NAME') THEN
3943 l_state_name:=p_att_table(l_att_idx).value;
3944 IF(l_state_name IS NULL) THEN
3945 return '00-000-0000';
3946 END IF;
3947 END IF;
3948
3949 IF(p_att_table(l_att_idx).field_name='NA_COUNTY_NAME') THEN
3950 l_county_name:=p_att_table(l_att_idx).value;
3951 END IF;
3952
3953 IF(p_att_table(l_att_idx).field_name='NA_CITY_NAME' ) THEN
3954 l_city_name:=p_att_table(l_att_idx).value;
3955 END IF;
3956
3957 IF(p_att_table(l_att_idx).field_name='NA_ZIP_CODE' ) THEN
3958 l_zip_code:=p_att_table(l_att_idx).value;
3959 END IF;
3960
3961 END IF;
3962 l_att_idx := p_att_table.NEXT (l_att_idx);
3963 END LOOP;
3964
3965 l_jurisdiction_code:=pay_ac_utility.get_geocode(l_state_name,l_county_name,l_city_name,l_zip_code);
3966 RETURN l_jurisdiction_code;
3967 END get_geocode_from_attr_tab;
3968
3969 END hxt_interface_utilities;