1 PACKAGE BODY pay_ca_balance_view_pkg AS
2 /* $Header: pycabalv.pkb 115.4 2004/02/17 03:47:35 sdahiya noship $ */
3
4
5 --------- TExt from US Tax balance Package-------------------------------------
6
7 -- BHOMAN todo
8 bh_bal_error EXCEPTION;
9
10
11 --------------------------------------------------------------------------------
12 -- PROCEDURE debug_init
13 --------------------------------------------------------------------------------
14 PROCEDURE debug_init
15 IS
16 BEGIN
17 DebugOn := FALSE;
18 DebugLineCount := 0;
19 DebugLineMax := 50000;
20 END;
21
22 --------------------------------------------------------------------------------
23 -- PROCEDURE debug_msg
24 --------------------------------------------------------------------------------
25 PROCEDURE debug_msg( p_debug_message IN VARCHAR2 )
26 IS
27 BEGIN
28 if DebugOn then
29 DebugList(DebugLineCount) := to_char(sysdate, 'DDMONHH24:MI:SS: ') || rtrim(p_debug_message);
30 DebugLineCount := DebugLineCount + 1;
31
32 if DebugLineCount > DebugLineMax then
33 debug_wrap;
34 end if;
35 end if;
36 END;
37
38 --------------------------------------------------------------------------------
39 -- PROCEDURE debug_err
40 --------------------------------------------------------------------------------
41 PROCEDURE debug_err( p_debug_message IN VARCHAR2 )
42 IS
43 BEGIN
44 if DebugOn then
45 debug_msg('***ERROR: ' || p_debug_message);
46 end if;
47 END;
48
49 --------------------------------------------------------------------------------
50 -- PROCEDURE debug_wrap
51 --------------------------------------------------------------------------------
52 PROCEDURE debug_wrap
53 IS
54 l_new_start NUMBER;
55 l_source_line NUMBER;
56 l_dest_line NUMBER;
57 l_last_line NUMBER;
58 BEGIN
59 -- BHOMAN todo: wrap by moving last xxx lines down,
60 -- for now, just leave lines allocated, but start
61 -- count over.
62 l_last_line := DebugLineCount - 1;
63 DebugLineCount := 0;
64 debug_msg('debug list wrapped!');
65
66 if DebugLineCount < 20 then
67 return;
68 end if;
69
70 l_new_start := round(((l_last_line + 5) / 2), 0);
71 l_dest_line := 0;
72
73 for l_source_line in l_new_start .. l_last_line LOOP
74
75 DebugList(l_dest_line) := DebugList(l_source_line);
76 l_dest_line := l_dest_line + 1;
77
78 end LOOP;
79
80 END;
81
82 --------------------------------------------------------------------------------
83 -- PROCEDURE debug_reset
84 --------------------------------------------------------------------------------
85 PROCEDURE debug_reset
86 IS
87 BEGIN
88 -- just leave lines allocated, but start count over.
89 DebugLineCount := 0;
90 debug_msg('debug list reset!');
91 if DebugOn then
92 hr_utility.trace(' Debug is currently on');
93 else
94 hr_utility.trace(' Debug is currently off');
95 end if;
96
97 END;
98
99 --------------------------------------------------------------------------------
100 -- PROCEDURE debug_dump
101 --------------------------------------------------------------------------------
102 PROCEDURE debug_dump(p_start_line IN NUMBER,
103 p_end_line IN NUMBER)
104 IS
105 l_start_line NUMBER;
106 l_end_line NUMBER;
107 l_tmp_line VARCHAR2(120);
108 BEGIN
109
110 hr_utility.trace('');
111 hr_utility.trace('');
112 if DebugLineCount = 0 then
113 hr_utility.trace('DEBUG DUMP: no lines to dump');
114 if DebugOn then
115 hr_utility.trace(' Debug is currently on');
116 else
117 hr_utility.trace(' Debug is currently off');
118 end if;
119 hr_utility.trace('');
120 hr_utility.trace('');
121 return;
122 end if;
123
124 l_start_line := p_start_line;
125 l_end_line := p_end_line;
126
127 if l_start_line < 0 then
128 l_start_line := 0;
129 hr_utility.trace('start line negative: ' ||
130 p_start_line || ', using 0.');
131 end if;
132
133 if l_start_line > DebugLineCount then
134 l_start_line := 0;
135 hr_utility.trace('start line too high: ' ||
136 p_start_line || ', using 0.');
137 end if;
138
139 if l_end_line >= DebugLineCount then
140 l_end_line := (DebugLineCount - 1);
141 hr_utility.trace('end line too high: ' ||
142 p_end_line || ', using end of list: ' ||
143 l_end_line);
144 end if;
145
146 if l_end_line < l_start_line then
147 l_end_line := (l_start_line + 1);
148 hr_utility.trace('end line lower than start line: ' ||
149 p_end_line ||
150 ', using start line + 1: ' ||
151 l_end_line);
152 end if;
153
154 hr_utility.trace('DEBUG DUMP: lines: ' ||
155 l_start_line ||
156 ' to ' ||
157 l_end_line);
158 if DebugOn then
159 hr_utility.trace(' Debug is currently on');
160 else
161 hr_utility.trace(' Debug is currently off');
162 end if;
163
164 FOR l_current_line in l_start_line .. l_end_line LOOP
165 begin
166 l_tmp_line := DebugList(l_current_line);
167 hr_utility.trace(l_current_line ||
168 ': ' ||
169 rtrim(l_tmp_line));
170 exception
171 when no_data_found then
172 hr_utility.trace('Error retrieving debug line!');
173 return;
174 end;
175 END LOOP;
176
177 hr_utility.trace('');
178 hr_utility.trace('');
179 hr_utility.trace('END OF DEBUG DUMP');
180 hr_utility.trace('');
181 hr_utility.trace('');
182
183 END;
184
185 --------------------------------------------------------------------------------
186 -- PROCEDURE debug_dump
187 --------------------------------------------------------------------------------
188 PROCEDURE debug_dump
189 IS
190 BEGIN
191 if DebugLineCount <= 0 then
192 debug_dump(0, 0);
193 end if;
194 debug_dump(0, (DebugLineCount - 1));
195 END;
196
197 --------------------------------------------------------------------------------
198 -- PROCEDURE debug_dump_like
199 --------------------------------------------------------------------------------
200 PROCEDURE debug_dump_like(p_string_like IN VARCHAR2)
201 IS
202 l_start_line NUMBER;
203 l_end_line NUMBER;
204 l_tmp_line VARCHAR2(120);
205 BEGIN
206
207 hr_utility.trace('');
208 hr_utility.trace('');
209 if DebugLineCount = 0 then
210 hr_utility.trace('DEBUG DUMP LIKE: no lines to dump');
211 if DebugOn then
212 hr_utility.trace(' Debug is currently on');
213 else
214 hr_utility.trace(' Debug is currently off');
215 end if;
216 hr_utility.trace('');
217 hr_utility.trace('');
218 return;
219 end if;
220
221 l_start_line := 0;
222 l_end_line := (DebugLineCount - 1);
223
224 if l_end_line < l_start_line then
225 l_end_line := (l_start_line + 1);
226 end if;
227
228 hr_utility.trace('DEBUG DUMP LINES LIKE: ' || p_string_like);
229 if DebugOn then
230 hr_utility.trace(' Debug is currently on');
231 else
232 hr_utility.trace(' Debug is currently off');
233 end if;
234
235 FOR l_current_line in l_start_line .. l_end_line LOOP
236 begin
237 l_tmp_line := DebugList(l_current_line);
238 if l_tmp_line like ('%' || p_string_like || '%') then
239 hr_utility.trace(l_current_line || ': ' ||
240 rtrim(l_tmp_line));
241 end if;
242 exception
243 when no_data_found then
244 hr_utility.trace('Error retrieving debug line!');
245 return;
246 end;
247 END LOOP;
248
249 hr_utility.trace('');
250 hr_utility.trace('');
251 hr_utility.trace('END OF DEBUG DUMP LIKE');
252 hr_utility.trace('');
253 hr_utility.trace('');
254
255 END;
256
257 --------------------------------------------------------------------------------
258 -- PROCEDURE debug_dump_err
259 --------------------------------------------------------------------------------
260 PROCEDURE debug_dump_err
261 IS
262 BEGIN
263 debug_dump_like('ERR');
264 END;
265
266 --------------------------------------------------------------------------------
267 -- PROCEDURE debug_dump_to_trace
268 --
269 -- Same as debug_dump, but uses hr_utility.trace.
270 --
271 -- p_trace_id is passed to hr_utility.trace_on, see pyutilty.pkb
272 -- for description of this parameter.
273 --
274 --------------------------------------------------------------------------------
275 PROCEDURE debug_dump_to_trace(p_trace_id IN VARCHAR2 DEFAULT NULL)
276 IS
277 l_start_line NUMBER;
278 l_end_line NUMBER;
279 l_tmp_line VARCHAR2(120);
280 l_existing_trace_id VARCHAR2(32);
281 BEGIN
282
283 -- only trace if there are debug lines
284 if DebugLineCount = 0 then
285 debug_msg('DEBUG TRACE: no lines to trace');
286 if DebugOn then
287 debug_msg(' Debug is currently on');
288 else
289 debug_msg(' Debug is currently off');
290 end if;
291 return;
292 end if;
293
294 if DebugLineCount < 0 then
295 debug_msg(' Invalid debug lines, caanot trace');
296 return;
297 end if;
298
299 -- see if trace is already on, null trace ID means no
300 l_existing_trace_id := hr_utility.get_trace_id;
301
302 -- turn on trace (if not already on)
303 if l_existing_trace_id is NULL then
304 hr_utility.trace_on(NULL, p_trace_id);
305 end if;
306
307 hr_utility.trace('======================================');
308 hr_utility.trace('BALANCE VIEW DEBUG LINES');
309
310 l_start_line := 0;
311 l_end_line := (DebugLineCount - 1);
312
313 hr_utility.trace('DEBUG DUMP: lines: ' ||
314 l_start_line ||
315 ' to ' ||
316 l_end_line);
317 if DebugOn then
318 hr_utility.trace(' Debug is currently on');
319 else
320 hr_utility.trace(' Debug is currently off');
321 end if;
322
323 FOR l_current_line in l_start_line .. l_end_line LOOP
324 begin
325 l_tmp_line := DebugList(l_current_line);
326 hr_utility.trace(l_current_line ||
327 ': ' ||
328 rtrim(l_tmp_line));
329 exception
330 when no_data_found then
331 hr_utility.trace('Error retrieving debug line!');
332 -- turn off trace, but only if *we* turned it on
333 if l_existing_trace_id is not NULL then
334 hr_utility.trace_off;
335 end if;
336 return;
337 end;
338 END LOOP;
339
340 hr_utility.trace(' ');
341 hr_utility.trace(' ');
342 hr_utility.trace('END OF BALANCE VIEW DEBUG LINES');
343 hr_utility.trace('=========================================');
344
345 -- turn the trace back off, if it was not already on
346 if l_existing_trace_id is NULL then
347 hr_utility.trace_off;
348 end if;
349
350 END;
351
352 --------------------------------------------------------------------------------
353 -- FUNCTION debug_get_line
354 --------------------------------------------------------------------------------
355 FUNCTION debug_get_line
356 (p_line_number IN NUMBER)
357 return VARCHAR2 IS
358 l_tmp_line VARCHAR2(120);
359 BEGIN
360 if p_line_number < 0 then
361 return NULL;
362 end if;
363 if p_line_number >= DebugLineCount then
364 return NULL;
365 end if;
366
367 begin
368 l_tmp_line := DebugList(p_line_number);
369 return l_tmp_line;
370 exception
371 when no_data_found then
372 hr_utility.trace('Error retrieving debug line!');
373 return NULL;
374 end;
375 END;
376
377 --------------------------------------------------------------------------------
378 -- FUNCTION debug_get_count
379 --------------------------------------------------------------------------------
380 FUNCTION debug_get_count
381 return NUMBER IS
382 BEGIN
383 return DebugLineCount;
384 END;
385 --------------------------------------------------------------------------------
386 -- PROCEDURE debug_set_max
387 --------------------------------------------------------------------------------
388 PROCEDURE debug_set_max( p_max IN NUMBER)
389 IS
390 BEGIN
391 if p_max <= 0 then
392 debug_err( '******** DEBUG MAX TOO LOW: ' ||
393 DebugLineMax ||
394 ' using default of 50000');
395 DebugLineMax := 50000;
396 else
397 DebugLineMax := p_max;
398 end if;
399 debug_msg( '******** DEBUG MAX SET TO ' || DebugLineMax);
400 END;
401
402 -- BHOMAN - moved debug_msg def above debug_toggle def
403 --------------------------------------------------------------------------------
404 -- PROCEDURE debug_toggle
405 --------------------------------------------------------------------------------
406 PROCEDURE debug_toggle
407 IS
408 BEGIN
409 if DebugOn then
410 debug_msg( '******** DEBUGGING STOPPED ***********' );
411 end if;
412 DebugOn := not DebugOn;
413 if DebugOn then
414 debug_msg( '******** DEBUGGING STARTED ***********' );
415 end if;
416 END;
417
418 --------------------------------------------------------------------------------
419 -- PROCEDURE debug_on
420 --------------------------------------------------------------------------------
421 PROCEDURE debug_on
422 IS
423 BEGIN
424 DebugOn := TRUE;
425 debug_msg( '******** DEBUGGING STARTED ***********' );
426 END;
427
428 --------------------------------------------------------------------------------
429 -- PROCEDURE debug_off
430 --------------------------------------------------------------------------------
431 PROCEDURE debug_off
432 IS
433 BEGIN
434 if DebugOn then
435 debug_msg( '******** DEBUGGING STOPPED ***********' );
436 end if;
437 DebugOn := FALSE;
438 END;
439
440 --------------------------------------------------------------------------------
441 -- SESSION VARIABLE FUNCTIONS
442 --------------------------------------------------------------------------------
443 --
444 -- The following functions use the PLSQL index by tables:
445 -- SessionVarNames, table of VARCHAR2(64); and SessionVarValues,
446 -- also index by table of VARCHAR2(64).
447 -- index by VARCHAR2;
448
449 --------------------------------------------------------------------------------
450 -- The Functions get and set session_var force the name parameter to upper case
451 -- before storing in, or searching the table. The value is not converted.
452 --
453 --------------------------------------------------------------------------------
454 -- FUNCTION get_session_var
455 --
456 -- get_session_var performs a (dumb) linear search of the names table for
457 -- p_name. If found, it returns the value from the values table at the
458 -- corresponding index. This dumb method should be performant for small
459 -- amounts of variables (< 100 ?). Larger amounts would require review
460 -- of alternative methods. Of course, if PLSQL ever supports indexing
461 -- of PLSQL tables by VARCHAR, or other types besides BINARY INTEGER, this
462 -- would not be an issue.
463 --
464 -- NOTE the exception when a value is not found at the same index as
465 -- where a name is found. Such an event would signal serious problems
466 -- with the state of the tables. We should consider throwing an
467 -- HR or other exception if this ever happes.
468 --
469 -- NOTE that since we do not allow NULL values for session vars, the
470 -- get_session_var function can be used to test whether a var is set:
471 -- if it returns NULL, the var is not set.
472 --
473 FUNCTION get_session_var
474 (p_name VARCHAR2)
475 return VARCHAR2 is
476 l_temp_name VARCHAR2(150);
477 l_temp_value VARCHAR2(150);
478 l_loop_count NUMBER;
479 BEGIN
480
481 -- no NULL names
482 if p_name is NULL then
483 return NULL;
484 end if;
485
486 -- check whether var tables are empty
487 if SessionVarCount = 0 then
488 return NULL;
489 end if;
490
491 -- force upper case name, remove leading/trailin spaces
492 l_temp_name := rtrim(ltrim(upper(p_name)));
493
494 -- search name table
495 -- NOTE the level of PLSQL certified for 10.7 Apps does not support
496 -- use of NEXT .. LAST attributes on PLSQL index by tables for WNDS
497 -- pure code. So we use hard coded table row count to drive our search.
498 FOR l_loop_count IN 1 .. SessionVarCount LOOP
499 if SessionVarNames(l_loop_count) = l_temp_name then
500 -- debug trace - found our var
501 -- use exception in case values is out of sync with names
502 -- NOTE: the level of PLSQL certified with 10.7 Apps does
503 -- not support using the exists attribute, so we must use
504 -- an exception here.
505 begin
506 l_temp_value := SessionVarValues(l_loop_count);
507 exception
508 when no_data_found then
509 -- index not found, big problem here - should trhow exception
510 --
511 return NULL;
512 end;
513 return l_temp_value;
514 end if;
515 END LOOP;
516
517 -- debug trace
518 return NULL;
519 END get_session_var;
520
521 --------------------------------------------------------------------------------
522 -- FUNCTION get_session_var - overloaded with p_default. Calls base
523 -- get_session_var; but if var is not set, returns p_default.
524 --
525 FUNCTION get_session_var
526 (p_name VARCHAR2,
527 p_default VARCHAR)
528 return VARCHAR2 is
529 l_temp_value VARCHAR2(150);
530 BEGIN
531 l_temp_value := NULL;
532
533 -- use base function to get value
534 l_temp_value := get_session_var(p_name);
535
536 -- if NULL value, return p_default
537 if l_temp_value is NULL then
538 return p_default;
539 end if;
540 END get_session_var;
541
542 --------------------------------------------------------------------------------
543 -- PROCEDURE set_session_var
544 --
545 -- Sets name and value in session var tables. Converts name to upper case,
546 -- does *not* convert value.
547 --
548 -- The set function does not store NULL names or values. However,
549 -- an attempt to set a NULL value will result in the variable of
550 -- that name being deleted from both the names and values tables.
551 --
552 PROCEDURE set_session_var
553 (p_name IN VARCHAR2,
554 p_value IN VARCHAR2)
555 IS
556 l_temp_name VARCHAR2(150);
557 l_temp_value VARCHAR2(150);
558 l_loop_count NUMBER;
559 BEGIN
560 -- no NULL names
561 if p_name is NULL then
562 return;
563 end if;
564
565 -- force upper case name, remove leading/trailin spaces
566 l_temp_name := rtrim(ltrim(upper(p_name)));
567
568 /*
569 hr_utility.trace('set session var, p_name: ' || p_name ||
570 ', l_temp_name: ' || l_temp_name ||
571 ', p_value: ' || p_value);
572 */
573 if SessionVarCount > 0 then
574 -- search name table, we may already have this var
575 -- NOTE the level of PLSQL certified for 10.7 Apps does not support
576 -- use of NEXT .. LAST attributes on PLSQL index by tables for WNDS
577 -- pure code. So we use hard coded table row count to drive our search.
578 FOR l_loop_count IN 1 .. SessionVarCount LOOP
579 if SessionVarNames(l_loop_count) = l_temp_name then
580 -- debug trace - found our var
581 -- set value table row to new value
582 SessionVarValues(l_loop_count) := p_value;
583 -- and return
584 return;
585 end if;
586 END LOOP;
587 end if;
588
589 -- not found in names table, make new entries
590 -- debug trace - log new var count
591 SessionVarCount := SessionVarCount + 1;
592 SessionVarNames(SessionVarCount) := l_temp_name;
593 SessionVarValues(SessionVarCount) := p_value;
594
595 END set_session_var;
596
597 --------------------------------------------------------------------------------
598 PROCEDURE clear_session_vars
599 IS
600 BEGIN
601 -- delete tavbles by assigning empty tables. The level of PLSQL
602 -- supported with 10.7 does not support the delete attribute on
603 -- whole tables.
604 SessionVarNames := EmptyTable;
605 SessionVarValues := EmptyTable;
606 SessionVarCount := 0;
607 END clear_session_vars;
608
609 --------------------------------------------------------------------------------
610 -- FUNCTION get_view_mode
611 --------------------------------------------------------------------------------
612 FUNCTION get_view_mode
613 return VARCHAR2 is
614 BEGIN
615 return balance_view_mode;
616 END;
617 --------------------------------------------------------------------------------
618 --------------------------------------------------------------------------------
619 -- PROCEDURE set_view_mode
620 --------------------------------------------------------------------------------
621 PROCEDURE set_view_mode
622 ( p_view_mode IN VARCHAR2)
623 IS
624 BEGIN
625 debug_msg('===========================================');
626 debug_msg('Enter set_view_mode' );
627 debug_msg(' p_view_mode: ' || p_view_mode );
628 -- force NULL to default 'ASG'
629 if p_view_mode in ('ASG', 'PER', 'GRE') then
630 balance_view_mode := p_view_mode;
631 elsif p_view_mode is NULL then
632 debug_msg(' NULL param, defaulting to ' || 'ASG');
633 balance_view_mode := 'ASG';
634 else
635 debug_err(' invalid param, defaulting to ' || 'ASG');
636 balance_view_mode := 'ASG';
637 end if;
638 return;
639 END;
640 --------------------------------------------------------------------------------
641 -- FUNCTION get_calc_all_timetypes_flag
642 --------------------------------------------------------------------------------
643 FUNCTION get_calc_all_timetypes_flag
644 return NUMBER is
645 BEGIN
646 return CalcAllTimeTypes;
647 END;
648 --------------------------------------------------------------------------------
649 --------------------------------------------------------------------------------
650 -- PROCEDURE set_calc_all_timetypes_flag
651 --------------------------------------------------------------------------------
652 PROCEDURE set_calc_all_timetypes_flag
653 ( p_calc_all IN NUMBER)
654 IS
655 BEGIN
656 debug_msg('===========================================');
657 debug_msg('Enter set_calc_all_timetypes_flag' );
658 if (p_calc_all <> 1) AND (p_calc_all <> 0) then
659 debug_msg(' p_calc_all must be 1 or 0, defaulting to 0');
660 CalcAllTimeTypes := 0;
661 else
662 CalcAllTimeTypes := p_calc_all;
663 end if;
664 debug_msg(' p_calc_all set to ' || to_char(CalcAllTimeTypes));
665 END;
666 --------------------------------------------------------------------------------
667 -- FUNCTION ALBStart_is_found
668 --------------------------------------------------------------------------------
669 function ALBStart_is_found
670 (p_bindex IN NUMBER)
671 return BOOLEAN is
672 l_temp_val number;
673 begin
674 begin
675 l_temp_val := ALBStart(p_bindex);
676 exception
677 when no_data_found then
678 -- index not found
679 --
680 return FALSE;
681 end;
682 return TRUE;
683 end;
684
685 --------------------------------------------------------------------------------
686 -- FUNCTION ALB2CStart_is_found
687 --------------------------------------------------------------------------------
688 function ALB2CStart_is_found
689 (p_bindex IN NUMBER)
690 return BOOLEAN is
691 l_temp_val number;
692 begin
693 begin
694 l_temp_val := ALB2CStart(p_bindex);
695 exception
696 when no_data_found then
697 -- index not found
698 --
699 return FALSE;
700 end;
701
702 return TRUE;
703 end;
704
705 --------------------------------------------------------------------------------
706 -- FUNCTION PLBStart_is_found
707 --------------------------------------------------------------------------------
708 function PLBStart_is_found
709 (p_bindex IN NUMBER)
710 return BOOLEAN is
711 l_temp_val number;
712 begin
713 begin
714 l_temp_val := PLBStart(p_bindex);
715 exception
716 when no_data_found then
717 -- index not found
718 --
719 return FALSE;
720 end;
721
722 return TRUE;
723 end;
724
725 --------------------------------------------------------------------------------
726 -- FUNCTION PLB2CStart_is_found
727 --------------------------------------------------------------------------------
728 function PLB2CStart_is_found
729 (p_bindex IN NUMBER)
730 return BOOLEAN is
731 l_temp_val number;
732 begin
733 begin
734 l_temp_val := PLB2CStart(p_bindex);
735 exception
736 when no_data_found then
737 -- index not found
738 --
739 return FALSE;
740 end;
741
742 return TRUE;
743 end;
744
745 --------------------------------------------------------------------------------
746 -- FUNCTION CtxValueByPos_is_found
747 --------------------------------------------------------------------------------
748 function CtxValueByPos_is_found
749 (p_bindex IN NUMBER)
750 return BOOLEAN is
751 l_temp_val pay_balance_context_values.value%type;
752 begin
753 begin
754 l_temp_val := CtxValueByPos(p_bindex);
755 exception
756 when no_data_found then
757 -- index not found
758 --
759 return FALSE;
760 end;
761
762 return TRUE;
763 end;
764
765 --------------------------------------------------------------------------------
766 -- FUNCTION DBCDimName_is_found
767 --------------------------------------------------------------------------------
768 function DBCDimName_is_found
769 (p_bindex IN NUMBER)
770 return BOOLEAN is
771 l_temp_val pay_balance_dimensions.dimension_name%type;
772 begin
773 begin
774 l_temp_val := DBCDimName(p_bindex);
775 exception
776 when no_data_found then
777 -- index not found
778 --
779 return FALSE;
780 end;
781
782 return TRUE;
783 end;
784
785 --------------------------------------------------------------------------------
786 -- PROCEDURE clear_contexts
787 --------------------------------------------------------------------------------
788 PROCEDURE clear_contexts
789 IS
790 l_ctxid NUMBER;
791 BEGIN
792 --
793 -- Just null out any cached context values. Keep context_id
794 -- information to avoid rerunning any SQL.
795 --
796 -- if CtxValueByPos.exists(pos_assignment_action_id) then
797 if CtxValueByPos_is_found(pos_assignment_action_id) then
798 l_ctxid := CtxIdByPos(pos_assignment_action_id);
799 CtxValueByPos(pos_assignment_action_id) := null;
800 CtxValueById(l_ctxid) := null;
801 end if;
802 --
803 -- if CtxValueByPos.exists(pos_jurisdiction_code) then
804 if CtxValueByPos_is_found(pos_jurisdiction_code) then
805 l_ctxid := CtxIdByPos(pos_jurisdiction_code);
806 CtxValueByPos(pos_jurisdiction_code) := null;
807 CtxValueById(l_ctxid) := null;
808 end if;
809 --
810 if CtxValueByPos_is_found(pos_tax_unit_id) then
811 l_ctxid := CtxIdByPos(pos_tax_unit_id);
812 CtxValueByPos(pos_tax_unit_id) := null;
813 CtxValueById(l_ctxid) := null;
814 end if;
815 --
816 if CtxValueByPos_is_found(pos_tax_group) then
817 l_ctxid := CtxIdByPos(pos_tax_group);
818 CtxValueByPos(pos_tax_group) := null;
819 CtxValueById(l_ctxid) := null;
820 end if;
821 --
822 if CtxValueByPos_is_found(pos_date_earned) then
823 l_ctxid := CtxIdByPos(pos_date_earned);
824 CtxValueByPos(pos_date_earned) := null;
825 CtxValueById(l_ctxid) := null;
826 end if;
827 --
828 if CtxValueByPos_is_found(pos_balance_date) then
829 l_ctxid := CtxIdByPos(pos_balance_date);
830 CtxValueByPos(pos_balance_date) := null;
831 CtxValueById(l_ctxid) := null;
832 end if;
833 --
834 if CtxValueByPos_is_found(pos_business_group_id) then
835 l_ctxid := CtxIdByPos(pos_business_group_id);
836 CtxValueByPos(pos_business_group_id) := null;
837 CtxValueById(l_ctxid) := null;
838 end if;
839 --
840 if CtxValueByPos_is_found(pos_source_id) then
841 l_ctxid := CtxIdByPos(pos_source_id);
842 CtxValueByPos(pos_source_id) := null;
843 CtxValueById(l_ctxid) := null;
844 end if;
845 END;
846
847 --------------------------------------------------------------------------------
848 -- PROCEDURE set_context
849 --------------------------------------------------------------------------------
850 PROCEDURE set_context
851 (
852 p_context_name IN VARCHAR2,
853 p_context_value IN VARCHAR2
854 )
855 IS
856 l_pos NUMBER;
857 l_ctxid NUMBER;
858 l_gre_table_count NUMBER;
859 l_tax_group VARCHAR2(150);
860 --
861 BEGIN
862 debug_msg( '===========================================');
863 debug_msg( 'Enter set_context' );
864 debug_msg( 'p_context_name: ' || p_context_name );
865 debug_msg( 'p_context_value: ' || p_context_value );
866
867 l_pos := pos_invalid;
868
869 -- Look through list of supported contexts.
870 if p_context_name = 'ASSIGNMENT_ACTION_ID' then
871 l_pos := pos_assignment_action_id;
872 elsif p_context_name = 'JURISDICTION_CODE' then
873 l_pos := pos_jurisdiction_code;
874 elsif p_context_name = 'TAX_UNIT_ID' then
875 l_pos := pos_tax_unit_id;
876 elsif p_context_name = 'TAX_GROUP' then
877 l_pos := pos_tax_group;
878 elsif p_context_name = 'DATE_EARNED' then
879 l_pos := pos_date_earned;
880 elsif p_context_name = 'BUSINESS_GROUP_ID' then
881 l_pos := pos_business_group_id;
882 elsif p_context_name = 'BALANCE_DATE' then
883 l_pos := pos_balance_date;
884 elsif p_context_name = 'SOURCE_ID' then
885 l_pos := pos_source_id;
886 end if;
887
888 debug_msg( 'l_pos = ' || l_pos );
889
890 if l_pos <> pos_invalid then
891 if CtxValueByPos_is_found(l_pos) then
892 -- Use existing information.
893 l_ctxid := CtxIdByPos(l_pos);
894 CtxValueByPos(l_pos) := p_context_value;
895 CtxValueById(l_ctxid) := p_context_value;
896 else
897 -- No existing information, so go to the database.
898 begin
899 select context_id
900 into l_ctxid
901 from ff_contexts
902 where context_name = p_context_name;
903 exception
904 when no_data_found then
905 debug_err( 'set_context: context_name not found: '
906 || p_context_name );
907 l_ctxid := null;
908 end;
909 if l_ctxid is not null then
910 CtxIdByPos(l_pos) := l_ctxid;
911 CtxNameByPos(l_pos) := p_context_name;
912 CtxValueByPos(l_pos) := p_context_value;
913 CtxIdById(l_ctxid) := l_ctxid;
914 CtxNameById(l_ctxid) := p_context_name;
915 CtxValueById(l_ctxid) := p_context_value;
916 end if;
917 end if;
918
919 -- Need to set the TAX_GROUP for the TAX_UNIT_ID.
920 -- Added decode for Canadian Tax Group.
921 if l_pos = pos_tax_unit_id then
922 -- TCL check to see if a row for the tax_unit_id is stored in the
923 -- session_gre_table. If it is use it. If not then
924 -- the 'TAX_GROUP' session variable is set via the query.
925 l_tax_group := null;
926
927 if session_gre_table.count > 0 then -- we have data in the table.
928 FOR i in session_gre_table.first .. session_gre_table.last
929 LOOP
930 if session_gre_table(i).tax_unit_id = to_number(p_context_value)then
931 l_tax_group := session_gre_table(i).tax_group_name;
932 exit;
933 end if;
934 END LOOP;
935 end if;
936
937 if l_tax_group IS NULL then
938 begin
939 select decode(hoi_bg.org_information9,
940 'US', hoi_gre.org_information5,
941 'CA', hoi_gre.org_information4)
942 into l_tax_group
943 from
944 hr_organization_information hoi_bg, -- Business Group
945 hr_organization_information hoi_gre, -- US or CA Context
946 hr_all_organization_units hou -- GRE
947 where hou.organization_id = to_number(p_context_value)
948 and hoi_gre.organization_id = hou.organization_id
949 and hoi_bg.organization_id = hou.business_group_id
950 and hoi_gre.org_information_context =
951 decode(hoi_bg.org_information9,
952 'US', 'Federal Tax Rules',
953 'CA', 'Canada Employer Identification',
954 'Not Applicable')
955 and hoi_bg.org_information_context = 'Business Group Information';
956
957 exception
958 when no_data_found then
959 -- BHOMAN this exception occurs with pmadore text case
960 -- raise bh_bal_error;
961 l_tax_group := null;
962 end;
963 if l_tax_group is null then
964 l_tax_group := 'No Tax Group';
965 end if;
966 l_gre_table_count := session_gre_table.count;
967 session_gre_table(l_gre_table_count).tax_unit_id := to_number(p_context_value);
968 session_gre_table(l_gre_table_count).tax_group_name := l_tax_group;
969
970 end if;
971 set_context( 'TAX_GROUP', l_tax_group );
972
973 end if;
974 end if;
975 debug_msg( 'Exiting set_context' );
976 END set_context;
977
978 --------------------------------------------------------------------------------
979 -- PROCEDURE get_context
980 --------------------------------------------------------------------------------
981 FUNCTION get_context
982 (
983 p_context_name IN VARCHAR2
984 )
985 return VARCHAR2 is
986 l_pos NUMBER;
987 l_context_value VARCHAR2(64);
988 BEGIN
989 debug_msg( '===========================================');
990 debug_msg( 'Enter get_context' );
991 debug_msg( 'p_context_name: ' || p_context_name );
992
993 l_pos := pos_invalid;
994
995 -- Look through list of supported contexts.
996 if p_context_name = 'ASSIGNMENT_ACTION_ID' then
997 l_pos := pos_assignment_action_id;
998 elsif p_context_name = 'JURISDICTION_CODE' then
999 l_pos := pos_jurisdiction_code;
1000 elsif p_context_name = 'TAX_UNIT_ID' then
1001 l_pos := pos_tax_unit_id;
1002 elsif p_context_name = 'TAX_GROUP' then
1003 l_pos := pos_tax_group;
1004 elsif p_context_name = 'DATE_EARNED' then
1005 l_pos := pos_date_earned;
1006 elsif p_context_name = 'BALANCE_DATE' then
1007 l_pos := pos_balance_date;
1008 elsif p_context_name = 'BUSINESS_GROUP_ID' then
1009 l_pos := pos_business_group_id;
1010 elsif p_context_name = 'SOURCE_ID' then
1011 l_pos := pos_source_id;
1012 end if;
1013
1014 debug_msg( 'l_pos = ' || l_pos );
1015
1016 if l_pos = pos_invalid then
1017 debug_err( 'get_context: invalid context: ' || p_context_name);
1018 return NULL;
1019 end if;
1020
1021
1022 if CtxValueByPos_is_found(l_pos) then
1023 -- Use existing information.
1024 l_context_value := CtxValueByPos(l_pos);
1025 debug_msg( 'l_context_value = ' || l_context_value );
1026 return l_context_value;
1027 end if;
1028
1029 debug_msg( 'context ' || p_context_name || ' is not set');
1030 return NULL;
1031
1032 END get_context;
1033
1034 --------------------------------------------------------------------------------
1035 -- PROCEDURE dump_context
1036 --------------------------------------------------------------------------------
1037 PROCEDURE dump_context
1038 (
1039 p_known_name IN VARCHAR2,
1040 p_pos IN NUMBER
1041 )
1042 IS
1043 l_pos NUMBER;
1044 l_ctxid NUMBER;
1045 --
1046 BEGIN
1047 l_pos := p_pos;
1048 hr_utility.trace('');
1049 hr_utility.trace('==== CONTEXT KNOWN AS: ' || p_known_name);
1050 if CtxValueByPos_is_found(l_pos) then
1051 l_ctxid := CtxIdByPos(l_pos);
1052 hr_utility.trace(' context id: ' || l_ctxid);
1053 hr_utility.trace(' name by pos: ' || CtxNameByPos(l_pos));
1054 hr_utility.trace(' val by pos: ' || CtxValueByPos(l_pos));
1055 hr_utility.trace(' id by pos: ' || CtxIdByPos(l_pos));
1056 hr_utility.trace(' val by id: ' || CtxValueById(l_ctxid));
1057 hr_utility.trace(' id by id: ' || CtxIdById(l_ctxid));
1058 hr_utility.trace(' name by id: ' || CtxNameById(l_ctxid));
1059 else
1060 hr_utility.trace(' NOT SET ');
1061 end if;
1062 hr_utility.trace('');
1063
1064 debug_msg( 'Exiting dump_context' );
1065 END dump_context;
1066
1067 --------------------------------------------------------------------------------
1068 -- PROCEDURE dump_context
1069 --------------------------------------------------------------------------------
1070 PROCEDURE dump_context
1071 IS
1072 l_pos NUMBER;
1073 l_ctxid NUMBER;
1074 --
1075 BEGIN
1076 debug_msg( '===========================================');
1077 debug_msg( 'Enter dump_context' );
1078
1079 dump_context('ASSIGNMENT_ACTION_ID', pos_assignment_action_id);
1080 dump_context('JURISDICTION_CODE', pos_jurisdiction_code);
1081 dump_context('TAX_UNIT_ID', pos_tax_unit_id);
1082 dump_context('TAX_GROUP', pos_tax_group);
1083 dump_context('DATE_EARNED', pos_date_earned);
1084 dump_context('BUSINESS_GROUP_ID', pos_business_group_id);
1085 dump_context('BALANCE_DATE', pos_balance_date);
1086 dump_context('SOURCE_ID', pos_source_id);
1087
1088 debug_msg( 'Exiting dump_context' );
1089 END dump_context;
1090
1091 --------------------------------------------------------------------------------
1092 -- PROCEDURE clear_asgbal_cache
1093 --------------------------------------------------------------------------------
1094 PROCEDURE clear_asgbal_cache
1095 IS
1096 BEGIN
1097 MaxALBIndex := 0;
1098
1099 ALBStart := ZeroBounds;
1100 ALBEnd := ZeroBounds;
1101 ALBLatBalId := ZeroLatBalId;
1102 ALBAssActId := ZeroAssActId;
1103 ALBValue := ZeroValue;
1104 ALBActSeq := ZeroActSeq;
1105 ALBEffDate := ZeroEffDate;
1106 ALBExpAssActId := ZeroExpAssActId;
1107 ALBExpValue := ZeroExpValue;
1108 ALBExpActSeq := ZeroExpActSeq;
1109 ALBExpEffDate := ZeroExpEffDate;
1110
1111 MaxALB2CIndex := 0;
1112
1113 ALB2CStart := ZeroBounds;
1114 ALB2CEnd := ZeroBounds;
1115 ALB2CtxId := ZeroCtxId;
1116 ALB2CtxName := ZeroCtxName;
1117 ALB2CtxValue := ZeroCtxValue;
1118
1119 cached_assignment_id := -1;
1120
1121 END clear_asgbal_cache;
1122
1123 --------------------------------------------------------------------------------
1124 -- PROCEDURE clear_perbal_cache
1125 --------------------------------------------------------------------------------
1126 PROCEDURE clear_perbal_cache
1127 IS
1128 BEGIN
1129
1130 MaxPLBIndex := 0;
1131
1132 PLBStart := ZeroBounds;
1133 PLBEnd := ZeroBounds;
1134 PLBLatBalId := ZeroLatBalId;
1135 PLBAssActId := ZeroAssActId;
1136 PLBValue := ZeroValue;
1137 PLBActSeq := ZeroActSeq;
1138 PLBEffDate := ZeroEffDate;
1139 PLBExpAssActId := ZeroExpAssActId;
1140 PLBExpValue := ZeroExpValue;
1141 PLBExpActSeq := ZeroExpActSeq;
1142 PLBExpEffDate := ZeroExpEffDate;
1143
1144 MaxPLB2CIndex := 0;
1145
1146 PLB2CStart := ZeroBounds;
1147 PLB2CEnd := ZeroBounds;
1148 PLB2CtxId := ZeroCtxId;
1149 PLB2CtxName := ZeroCtxName;
1150 PLB2CtxValue := ZeroCtxValue;
1151
1152 cached_person_id := -1;
1153 END clear_perbal_cache;
1154
1155 --------------------------------------------------------------------------------
1156 -- FUNCTION get_value
1157 -- Lowest level (and therefore most dangerous) get_value call. Don't want
1158 -- users to call this. Provides the option not to cache latest balance values
1159 -- (p_dont_cache <> 0), or not use latest balance values (p_always_get_dbi
1160 -- <> 0). The p_date_mode and p_effective_date arguments are specifically
1161 -- for calling from get_value in date mode (p_date_mode <> 0).
1162 --------------------------------------------------------------------------------
1163 FUNCTION get_value
1164 (
1165 p_assignment_action_id IN NUMBER,
1166 p_defined_balance_id IN NUMBER,
1167 p_dont_cache IN NUMBER,
1168 p_always_get_dbi IN NUMBER,
1169 p_date_mode IN NUMBER,
1170 p_effective_date IN DATE
1171 )
1172 RETURN NUMBER IS
1173 balance NUMBER;
1174 l_assignment_id NUMBER;
1175 l_person_id NUMBER;
1176 l_action_sequence NUMBER;
1177 l_assact_effdate DATE;
1178 l_run_route varchar2(5);
1179 l_run_route_bool boolean;
1180 l_temp_num number;
1181 --
1182 l_dimension_type pay_balance_dimensions.dimension_type%type;
1183 l_dbi_suffix pay_balance_dimensions.database_item_suffix%type;
1184 l_dimension_name pay_balance_dimensions.dimension_name%type;
1185 l_balance_type_id pay_defined_balances.balance_type_id%type;
1186 l_jurisdiction_level pay_balance_types.jurisdiction_level%type;
1187 l_leg_code pay_balance_dimensions.legislation_code%type;
1188
1189 CURSOR csr_context_exists IS
1190 SELECT 1
1191 FROM ff_contexts cxt
1192 WHERE context_name = 'SOURCE_ID';
1193
1194 BEGIN
1195
1196 debug_msg( '===========================================');
1197 debug_msg( 'Entered get_value' );
1198 debug_msg( 'p_assignment_action_id = ' || p_assignment_action_id );
1199 debug_msg( 'p_defined_balance_id = ' || p_defined_balance_id );
1200 debug_msg( 'p_dont_cache = ' || p_dont_cache );
1201 debug_msg( 'p_always_get_dbi = ' || p_always_get_dbi );
1202 debug_msg( 'p_date_mode = ' || p_date_mode );
1203 debug_msg( 'p_effective_date = ' || p_effective_date );
1204 --
1205 -- Get details for this defined_balance_id.
1206 --
1207 -- BHOMAN - todo - since below changed from DBC to DBCDimName,
1208 -- consider whether DimName table was init'd to satisfy the exists
1209 -- clause
1210 debug_msg( 'get_value: looking in DBCache for details of defined_balance '
1211 || p_defined_balance_id
1212 || ' ...');
1213
1214 -- check for the 'RUN_ROUTE' parameter_name in the pay_action_parameters
1215 -- table to determine if we want to call the run_result route instead of
1216 -- the run_balance route.
1217 begin
1218
1219 select parameter_value
1220 into l_run_route
1221 from PAY_ACTION_PARAMETERS
1222 where parameter_name = 'RUN_ROUTE';
1223
1224 exception
1225 WHEN others then
1226 l_run_route := 'FALSE';
1227 end;
1228
1229 IF l_run_route <> 'TRUE' THEN
1230 l_run_route_bool := false;
1231 ELSE
1232 l_run_route_bool := true;
1233 END IF;
1234
1235
1236 if DBCDimName_is_found(p_defined_balance_id) then
1237 --
1238 l_dbi_suffix := DBCDbiSuffix(p_defined_balance_id);
1239 l_balance_type_id := DBCBalTypeId(p_defined_balance_id);
1240 l_dimension_type := DBCDimType(p_defined_balance_id);
1241
1242 --
1243 -- JGOSWAMI - quick fix for Canadian Patch but needs to get l_leg_code value from cache.
1244 --
1245 begin
1246 select pbd.legislation_code
1247 into l_leg_code
1248 from pay_defined_balances pdb,
1249 pay_balance_dimensions pbd
1250 where pdb.defined_balance_id = p_defined_balance_id
1251 and pbd.balance_dimension_id = pdb.balance_dimension_id;
1252 exception
1253 when no_data_found then
1254 -- Invalid legislation code.
1255 --
1256 l_leg_code := null;
1257 debug_msg( 'No such legislation code: ' || l_leg_code );
1258 --
1259 end;
1260
1261 debug_msg( 'get_value: DBCache details found for _defined_balance_id: '
1262 || p_defined_balance_id);
1263 debug_msg( ' dbi_suffix: ' || l_dbi_suffix);
1264 debug_msg( ' balance_type_id: ' || l_balance_type_id);
1265 debug_msg( ' dimension_type: ' || l_dimension_type);
1266 debug_msg( ' l_leg_code: ' || l_leg_code);
1267 else
1268 -- Need to go to database.
1269 --
1270 debug_msg( 'get_value: details not found in DBCache, going to database...' );
1271 --
1272 begin
1273 select pdb.balance_type_id,
1274 pbd.database_item_suffix,
1275 pbd.dimension_type,
1276 pbd.dimension_name,
1277 pbt.jurisdiction_level,
1278 pbd.legislation_code
1279 into l_balance_type_id,
1280 l_dbi_suffix,
1281 l_dimension_type,
1282 l_dimension_name,
1283 l_jurisdiction_level,
1284 l_leg_code
1285 from pay_defined_balances pdb,
1286 pay_balance_dimensions pbd,
1287 pay_balance_types pbt
1288 where pdb.defined_balance_id = p_defined_balance_id
1289 and pbd.balance_dimension_id = pdb.balance_dimension_id
1290 and pbt.balance_type_id = pdb.balance_type_id;
1291 exception
1292 when no_data_found then
1293 -- Invalid defined_balance_id.
1294 --
1295 debug_msg( 'No such defined_balance_id: ' || p_defined_balance_id );
1296 debug_msg( 'Exit get_value' );
1297 --
1298 balance := null;
1299 return balance;
1300 end;
1301
1302 -- Update the Defined Balance Cache.
1303 DBCBalTypeId(p_defined_balance_id) := l_balance_type_id;
1304 DBCJurisLvl(p_defined_balance_id) := l_jurisdiction_level;
1305 DBCDbiSuffix(p_defined_balance_id) := l_dbi_suffix;
1306 DBCDimType(p_defined_balance_id) := l_dimension_type;
1307 DBCDimName(p_defined_balance_id) := l_dimension_name;
1308 debug_msg( 'get_value: DBCache details updated for _defined_balance_id: '
1309 || p_defined_balance_id);
1310 debug_msg( ' balance_type_id: ' || l_balance_type_id);
1311 debug_msg( ' dbi_suffix: ' || l_dbi_suffix);
1312 debug_msg( ' dimension_type: ' || l_dimension_type);
1313 debug_msg( ' l_dimension_name: ' || l_dimension_name);
1314 debug_msg( ' jurisdiction_level: ' || l_jurisdiction_level);
1315 end if;
1316 --
1317 -- set session variable for legislation code, so that when run_route is
1318 -- called appropriate CA or US route can be called.
1319 --
1320 set_session_var('LEG_CODE',l_leg_code);
1321 --
1322 set_context( 'ASSIGNMENT_ACTION_ID', p_assignment_action_id );
1323 begin
1324 select paa.assignment_id, paa.action_sequence, ppa.effective_date
1325 into l_assignment_id, l_action_sequence, l_assact_effdate
1326 from pay_assignment_actions paa,
1327 pay_payroll_actions ppa
1328 where paa.assignment_action_id = p_assignment_action_id
1329 and paa.payroll_action_id = ppa.payroll_action_id;
1330 exception
1331 when no_data_found then
1332 --
1333 debug_msg( 'Could not find assignment_action_id: ' ||
1334 p_assignment_action_id );
1335 debug_msg( 'Exit get_value' );
1336 --
1337 return null;
1338 end;
1339
1340 ----------------------------------------------------------------------
1341 -- Run the route if the caller requested it (p_always_get_dbi <> 0) or
1342 -- if the balance type is one of:
1343 -- N: not fed, not stored
1344 -- F: fed and not stored
1345 -- R: Run level
1346 ----------------------------------------------------------------------
1347 if (p_always_get_dbi <> 0) or
1348 (DBCDimType(p_defined_balance_id) in ( 'N', 'F', 'R' )) then
1349 --
1350 debug_msg( 'Go to route, dimension_type = ' || DBCDimType(p_defined_balance_id) );
1351 --
1352 -- balance := goto_route( p_assignment_action_id, p_defined_balance_id );
1353
1354 IF CtxNameByPos(pos_balance_date) is not null then
1355 pay_balance_pkg.set_context(CtxNameByPos(pos_balance_date),
1356 CtxValueByPos(pos_balance_date));
1357 END IF;
1358
1359 IF CtxNameByPos(pos_tax_unit_id) is not null then
1360 pay_balance_pkg.set_context(CtxNameByPos(pos_tax_unit_id),
1361 CtxValueByPos(pos_tax_unit_id));
1362 END IF;
1363
1364 IF CtxNameByPos(pos_jurisdiction_code) is not null then
1365 pay_balance_pkg.set_context(CtxNameByPos(pos_jurisdiction_code),
1366 CtxValueByPos(pos_jurisdiction_code));
1367 END IF;
1368
1369 /*
1370 * If the SOURCE_ID context exists but has no value set it to NULL
1371 */
1372 OPEN csr_context_exists;
1373 FETCH csr_context_exists INTO l_temp_num;
1374 IF csr_context_exists%FOUND THEN
1375 IF NOT CtxValueByPos_is_found(pos_source_id) THEN
1376 pay_balance_pkg.set_context('SOURCE_ID',
1377 NULL);
1378 ELSE
1379 pay_balance_pkg.set_context(CtxNameByPos(pos_source_id),
1380 CtxValueByPos(pos_source_id));
1381
1382 END IF;
1383
1384 END IF;
1385 CLOSE csr_context_exists;
1386
1387
1388 IF CtxNameByPos(pos_tax_group) is not null then
1389 pay_balance_pkg.set_context(CtxNameByPos(pos_tax_group),
1390 CtxValueByPos(pos_tax_group));
1391 END IF;
1392
1393 IF CtxNameByPos(pos_date_earned) is not null then
1394 pay_balance_pkg.set_context(CtxNameByPos(pos_date_earned),
1395 CtxValueByPos(pos_date_earned));
1396 END IF;
1397
1398
1399 balance := pay_balance_pkg.get_value(
1400 p_defined_balance_id => p_defined_balance_id
1401 ,p_assignment_action_id => p_assignment_action_id
1402 ,p_get_rr_route => l_run_route_bool
1403 ,p_get_rb_route => FALSE);
1404
1405 ----------------------------------------------------------------
1406 -- Need to expiry check Person and Assignment level balances, if
1407 -- get_value is being called in date mode.
1408 ----------------------------------------------------------------
1409 if (balance <> 0) and (p_date_mode <> 0) and
1410 (DBCDimType(p_defined_balance_id) in ('P', 'A')) then
1411 if date_expired( p_assignment_action_id, p_assignment_action_id,
1412 l_assact_effdate, p_effective_date,
1413 DBCDimName(p_defined_balance_id), 1) then
1414 balance := 0;
1415 end if;
1416 end if;
1417
1418 debug_msg( 'Exit get_value, balance = ' || balance );
1419 return balance;
1420 end if;
1421
1422 -------------------------------
1423 -- Try to get a latest balance.
1424 -------------------------------
1425 --
1426 debug_msg( 'Get a latest balance' );
1427 --
1428 if DBCDimType(p_defined_balance_id) = 'A' then
1429 debug_msg( 'Assignment latest balance' );
1430 /* balance := goto_asg_latest_balances( p_assignment_action_id,
1431 p_defined_balance_id,
1432 l_assignment_id,
1433 l_action_sequence,
1434 l_assact_effdate,
1435 p_dont_cache,
1436 p_date_mode,
1437 p_effective_date );
1438 */
1439
1440 IF CtxNameByPos(pos_balance_date) is not null then
1441 pay_balance_pkg.set_context(CtxNameByPos(pos_balance_date),
1442 CtxValueByPos(pos_balance_date));
1443 END IF;
1444
1445 IF CtxNameByPos(pos_tax_unit_id) is not null then
1446 pay_balance_pkg.set_context(CtxNameByPos(pos_tax_unit_id),
1447 CtxValueByPos(pos_tax_unit_id));
1448 END IF;
1449
1450 IF CtxNameByPos(pos_jurisdiction_code) is not null then
1451 pay_balance_pkg.set_context(CtxNameByPos(pos_jurisdiction_code),
1452 CtxValueByPos(pos_jurisdiction_code));
1453 END IF;
1454
1455 /*
1456 * If the SOURCE_ID context exists but has no value set it to NULL
1457 */
1458 OPEN csr_context_exists;
1459 FETCH csr_context_exists INTO l_temp_num;
1460 IF csr_context_exists%FOUND THEN
1461 IF NOT CtxValueByPos_is_found(pos_source_id) THEN
1462 pay_balance_pkg.set_context('SOURCE_ID',
1463 NULL);
1464 ELSE
1465 pay_balance_pkg.set_context(CtxNameByPos(pos_source_id),
1466 CtxValueByPos(pos_source_id));
1467
1468 END IF;
1469
1470 END IF;
1471 CLOSE csr_context_exists;
1472
1473 IF CtxNameByPos(pos_tax_group) is not null then
1474 pay_balance_pkg.set_context(CtxNameByPos(pos_tax_group),
1475 CtxValueByPos(pos_tax_group));
1476 END IF;
1477
1478 IF CtxNameByPos(pos_date_earned) is not null then
1479 pay_balance_pkg.set_context(CtxNameByPos(pos_date_earned),
1480 CtxValueByPos(pos_date_earned));
1481 END IF;
1482
1483 balance := pay_balance_pkg.get_value(
1484 p_defined_balance_id => p_defined_balance_id
1485 ,p_assignment_action_id => p_assignment_action_id
1486 ,p_get_rr_route => l_run_route_bool
1487 ,p_get_rb_route => FALSE);
1488
1489
1490 elsif DBCDimType(p_defined_balance_id) = 'P' then
1491 debug_msg( 'Person latest balance' );
1492 --
1493 -- The following SQL must succeed because l_assignment_id
1494 -- is a valid assignment_id at this stage.
1495 --
1496 /* select distinct person_id
1497 into l_person_id
1498 from per_assignments_f
1499 where assignment_id = l_assignment_id;
1500 debug_msg( 'Person id ' || l_person_id || ' for assignment id ' ||
1501 l_assignment_id );
1502 balance := goto_per_latest_balances( p_assignment_action_id,
1503 p_defined_balance_id,
1504 l_person_id,
1505 l_action_sequence,
1506 l_assact_effdate,
1507 p_dont_cache,
1508 p_date_mode,
1509 p_effective_date );
1510 */
1511
1512 IF CtxNameByPos(pos_balance_date) is not null then
1513 pay_balance_pkg.set_context(CtxNameByPos(pos_balance_date),
1514 CtxValueByPos(pos_balance_date));
1515 END IF;
1516
1517 IF CtxNameByPos(pos_tax_unit_id) is not null then
1518 pay_balance_pkg.set_context(CtxNameByPos(pos_tax_unit_id),
1519 CtxValueByPos(pos_tax_unit_id));
1520 END IF;
1521
1522 IF CtxNameByPos(pos_jurisdiction_code) is not null then
1523 pay_balance_pkg.set_context(CtxNameByPos(pos_jurisdiction_code),
1524 CtxValueByPos(pos_jurisdiction_code));
1525 END IF;
1526
1527 /*
1528 * If the SOURCE_ID context exists but has no value set it to NULL
1529 */
1530 OPEN csr_context_exists;
1531 FETCH csr_context_exists INTO l_temp_num;
1532 IF csr_context_exists%FOUND THEN
1533 IF NOT CtxValueByPos_is_found(pos_source_id) THEN
1534 pay_balance_pkg.set_context('SOURCE_ID',
1535 NULL);
1536 ELSE
1537 pay_balance_pkg.set_context(CtxNameByPos(pos_source_id),
1538 CtxValueByPos(pos_source_id));
1539
1540 END IF;
1541
1542 END IF;
1543 CLOSE csr_context_exists;
1544
1545 IF CtxNameByPos(pos_tax_group) is not null then
1546 pay_balance_pkg.set_context(CtxNameByPos(pos_tax_group),
1547 CtxValueByPos(pos_tax_group));
1548 END IF;
1549
1550 IF CtxNameByPos(pos_date_earned) is not null then
1551 pay_balance_pkg.set_context(CtxNameByPos(pos_date_earned),
1552 CtxValueByPos(pos_date_earned));
1553 END IF;
1554
1555 balance := pay_balance_pkg.get_value(
1556 p_defined_balance_id => p_defined_balance_id
1557 ,p_assignment_action_id => p_assignment_action_id
1558 ,p_get_rr_route => l_run_route_bool
1559 ,p_get_rb_route => FALSE);
1560
1561
1562 else
1563 debug_msg( 'Invalid latest balance type: ' ||
1564 DBCDimType(p_defined_balance_id) );
1565 balance := null;
1566 end if;
1567
1568 return balance;
1569 END;
1570
1571 --------------------------------------------------------------------------------
1572 -- FUNCTION get_value
1573 -- assignment_action mode, caller has the option of not using the latest
1574 -- balance values (p_always_get_dbi <> 0), or not caching latest balance
1575 -- values (p_dont_cache <> 0).
1576 --------------------------------------------------------------------------------
1577 FUNCTION get_value
1578 (
1579 p_assignment_action_id IN NUMBER,
1580 p_defined_balance_id IN NUMBER,
1581 p_dont_cache IN NUMBER,
1582 p_always_get_dbi IN NUMBER
1583 )
1584 RETURN NUMBER IS
1585 l_balance NUMBER;
1586 BEGIN
1587 l_balance := get_value( p_assignment_action_id, p_defined_balance_id,
1588 p_dont_cache, p_always_get_dbi, 0, null );
1589 return l_balance;
1590 END get_value;
1591
1592 --------------------------------------------------------------------------------
1593 -- FUNCTION get_value
1594 -- assignment_action mode, uses and caches latest balance values.
1595 --------------------------------------------------------------------------------
1596 FUNCTION get_value
1597 (
1598 p_assignment_action_id IN NUMBER,
1599 p_defined_balance_id IN NUMBER
1600 )
1601 RETURN NUMBER IS
1602 l_balance NUMBER;
1603 BEGIN
1604 l_balance :=
1605 get_value( p_assignment_action_id, p_defined_balance_id, 0, 0, 0, null );
1606 return l_balance;
1607 END get_value;
1608
1609 --------------------------------------------------------------------------------
1610 -- FUNCTION get_value
1611 -- date mode, uses the latest balance values with caching.
1612 --------------------------------------------------------------------------------
1613 FUNCTION get_value
1614 (
1615 p_assignment_id IN NUMBER,
1616 p_defined_balance_id IN NUMBER,
1617 p_effective_date IN DATE
1618 )
1619 RETURN NUMBER IS
1620 l_balance NUMBER;
1621 BEGIN
1622 l_balance :=
1623 get_value( p_assignment_id, p_defined_balance_id, p_effective_date, 0 );
1624 return l_balance;
1625 END get_value;
1626
1627 --------------------------------------------------------------------------------
1628 -- FUNCTION get_value
1629 -- date mode, uses the latest balance values with the option of turning
1630 -- caching off (p_dont_cache <> 0).
1631 --------------------------------------------------------------------------------
1632 FUNCTION get_value
1633 (
1634 p_assignment_id IN NUMBER,
1635 p_defined_balance_id IN NUMBER,
1636 p_effective_date IN DATE,
1637 p_dont_cache IN NUMBER
1638 )
1639 RETURN NUMBER IS
1640 l_assignment_action_id NUMBER;
1641 l_balance NUMBER;
1642 --
1643 -- Cursor for getting the latest sequenced assignment_action_id for the
1644 -- assignment_id and date.
1645 --
1646 CURSOR get_latest_assactid( p_assignment_id IN NUMBER,
1647 p_effective_date IN DATE ) IS
1648 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||
1649 paa.assignment_action_id),16))
1650 from pay_assignment_actions paa,
1651 pay_payroll_actions ppa
1652 where paa.assignment_id = p_assignment_id
1653 and ppa.payroll_action_id = paa.payroll_action_id
1654 and ppa.effective_date <= p_effective_date
1655 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B' );
1656 BEGIN
1657 begin
1658 open get_latest_assactid( p_assignment_id, p_effective_date );
1659 fetch get_latest_assactid into l_assignment_action_id;
1660 close get_latest_assactid;
1661 exception
1662 when others then
1663 if get_latest_assactid%isopen then
1664 close get_latest_assactid;
1665 end if;
1666 raise;
1667 end;
1668 if l_assignment_action_id is null then
1669 debug_msg( 'get_value (date mode) did not find assignment_action_id' );
1670 debug_msg( ' p_assignment_id = ' || p_assignment_id );
1671 debug_msg( ' p_effective_date = ' || p_effective_date );
1672 l_balance := null;
1673 else
1674 l_balance := get_value( l_assignment_action_id, p_defined_balance_id,
1675 p_dont_cache, 0, 1, p_effective_date );
1676 end if;
1677 return l_balance;
1678 END get_value;
1679
1680 --------------------------------------------------------------------------------
1681 -- Expiry Checking Code (mostly taken from pyusexc.pkb).
1682 --------------------------------------------------------------------------------
1683 /*---------------------------- next_period -----------------------------------
1684 NAME
1685 next_period
1686 DESCRIPTION
1687 Given a date and a payroll action id, returns the date of the day after
1688 the end of the containing pay period.
1689 NOTES
1690 <none>
1691 */
1692 FUNCTION next_period
1693 (
1694 p_assactid IN NUMBER,
1695 p_date IN DATE
1696 ) RETURN DATE is
1697 l_return_val DATE := NULL;
1698 BEGIN
1699 select TP.end_date + 1
1700 into l_return_val
1701 from per_time_periods TP,
1702 pay_payroll_actions PACT,
1703 pay_assignment_actions ASSACT
1704 where ASSACT.assignment_action_id = p_assactid
1705 and PACT.payroll_action_id = ASSACT.payroll_action_id
1706 and PACT.payroll_id = TP.payroll_id
1707 and p_date between TP.start_date and TP.end_date;
1708
1709 RETURN l_return_val;
1710
1711 END next_period;
1712
1713 /*---------------------------- next_month ------------------------------------
1714 NAME
1715 next_month
1716 DESCRIPTION
1717 Given a date, returns the date of the first day of the next month.
1718 NOTES
1719 <none>
1720 */
1721 FUNCTION next_month
1722 (
1723 p_date IN DATE
1724 ) return DATE is
1725 BEGIN
1726
1727 RETURN trunc(add_months(p_date,1),'MM');
1728
1729 END next_month;
1730
1731 /*--------------------------- next_quarter -----------------------------------
1732 NAME
1733 next_quarter
1734 DESCRIPTION
1735 Given a date, returns the date of the first day of the next calendar
1736 quarter.
1737 NOTES
1738 <none>
1739 */
1740 FUNCTION next_quarter
1741 (
1742 p_date IN DATE
1743 ) RETURN DATE is
1744 BEGIN
1745
1746 RETURN trunc(add_months(p_date,3),'Q');
1747
1748 END next_quarter;
1749
1750 /*---------------------------- next_year ------------------------------------
1751 NAME
1752 next_year
1753 DESCRIPTION
1754 Given a date, returns the date of the first day of the next calendar
1755 year.
1756 NOTES
1757 <none>
1758 */
1759 FUNCTION next_year
1760 (
1761 p_date IN DATE
1762 ) RETURN DATE is
1763 BEGIN
1764
1765 RETURN trunc(add_months(p_date,12),'Y');
1766
1767 END next_year;
1768
1769 /*------------------------- next_fiscal_quarter -----------------------------
1770 NAME
1771 next_fiscal_quarter
1772 DESCRIPTION
1773 Given a date, returns the date of the first day of the next fiscal
1774 quarter.
1775 NOTES
1776 <none>
1777 */
1778 FUNCTION next_fiscal_quarter
1779 (
1780 p_beg_of_fiscal_year IN DATE,
1781 p_date IN DATE
1782 ) RETURN DATE is
1783
1784 -- get offset of fiscal year start in relative months and days
1785 l_fy_rel_month NUMBER(2) := to_char(p_beg_of_fiscal_year, 'MM') - 1;
1786 l_fy_rel_day NUMBER(2) := to_char(p_beg_of_fiscal_year, 'DD') - 1;
1787
1788 BEGIN
1789
1790 RETURN (add_months(next_quarter(add_months(p_date, -l_fy_rel_month)
1791 - l_fy_rel_day),
1792 l_fy_rel_month) + l_fy_rel_day);
1793
1794 END next_fiscal_quarter;
1795
1796 /*--------------------------- next_fiscal_year ------------------------------
1797 NAME
1798 next_fiscal_year
1799 DESCRIPTION
1800 Given a date, returns the date of the first day of the next fiscal year.
1801 NOTES
1802 <none>
1803 */
1804 FUNCTION next_fiscal_year
1805 (
1806 p_beg_of_fiscal_year IN DATE,
1807 p_date IN DATE
1808 ) RETURN DATE is
1809
1810 -- get offset of fiscal year start relative to calendar year
1811 l_fiscal_year_offset NUMBER(3) := to_char(p_beg_of_fiscal_year, 'DDD') - 1;
1812
1813 BEGIN
1814
1815 RETURN (next_year(p_date - l_fiscal_year_offset) + l_fiscal_year_offset);
1816
1817 END next_fiscal_year;
1818 /*------------------------------ date_expired --------------------------------
1819 NAME
1820 date_expired
1821 DESCRIPTION
1822 Expiry checking code for the following date-related dimensions:
1823 Assignment/Person/neither and GRE/not GRE and
1824 Run/Period TD/Month/Quarter TD/Year TD/Fiscal Quarter TD/
1825 Fiscal Year TD
1826 NOTES
1827 This function assumes the date portion of the dimension name
1828 is always at the end to allow accurate identification since
1829 this is used for many dimensions.
1830 */
1831 FUNCTION date_expired
1832 (
1833 p_owner_assignment_action_id in number, -- assact created balance.
1834 p_user_assignment_action_id in number, -- current assact.
1835 p_owner_effective_date in date, -- eff date of balance.
1836 p_user_effective_date in date, -- eff date of current run.
1837 p_dimension_name in varchar2, -- balance dimension name.
1838 p_date_mode in number -- running in date mode.
1839 )
1840 RETURN BOOLEAN IS
1841
1842 l_beg_of_fiscal_year DATE := NULL;
1843 l_expiry_date DATE := NULL;
1844
1845 BEGIN
1846
1847 IF p_dimension_name like '%Run' THEN
1848 -- must check for special case: if payroll action id's are the same,
1849 -- then don't expire. This facilitates meaningful access of these
1850 -- balances outside of runs.
1851 IF p_date_mode = 0 THEN
1852 IF p_owner_assignment_action_id <> p_user_assignment_action_id THEN
1853 l_expiry_date := p_user_effective_date; -- always must expire.
1854 ELSE
1855 RETURN FALSE;
1856 END IF;
1857 ELSE
1858 IF ( p_user_effective_date <> p_owner_effective_date ) OR
1859 ( p_owner_assignment_action_id <> p_user_assignment_action_id ) THEN
1860 l_expiry_date := p_user_effective_date; -- always must expire.
1861 ELSE
1862 RETURN FALSE;
1863 END IF;
1864 END IF;
1865
1866 ELSIF p_dimension_name like '%Payments%' THEN
1867 -- must check for special case: if payroll action id's are the same,
1868 -- then don't expire. This facilitates meaningful access of these
1869 -- balances outside of runs.
1870 IF p_date_mode = 0 THEN
1871 IF p_owner_assignment_action_id <> p_user_assignment_action_id THEN
1872 l_expiry_date := p_user_effective_date; -- always must expire.
1873 ELSE
1874 RETURN FALSE;
1875 END IF;
1876 ELSE
1877 IF ( p_user_effective_date <> p_owner_effective_date ) OR
1878 ( p_owner_assignment_action_id <> p_user_assignment_action_id ) THEN
1879 l_expiry_date := p_user_effective_date; -- always must expire.
1880 ELSE
1881 RETURN FALSE;
1882 END IF;
1883 END IF;
1884
1885 ELSIF p_dimension_name like '%Period to Date' THEN
1886 l_expiry_date := next_period(p_owner_assignment_action_id,
1887 p_owner_effective_date);
1888
1889 ELSIF p_dimension_name like '%Month' THEN
1890 l_expiry_date := next_month(p_owner_effective_date);
1891
1892 ELSIF p_dimension_name like '%Fiscal Quarter to Date' THEN
1893 SELECT fnd_date.canonical_to_date(org_information11)
1894 INTO l_beg_of_fiscal_year
1895 FROM pay_payroll_actions PACT,
1896 pay_assignment_actions ASSACT,
1897 hr_organization_information HOI
1898 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
1899 AND HOI.organization_id = PACT.business_group_id
1900 AND PACT.payroll_action_id = ASSACT.payroll_action_id
1901 AND ASSACT.assignment_action_id = p_owner_assignment_action_id;
1902
1903 l_expiry_date := next_fiscal_quarter(l_beg_of_fiscal_year,
1904 p_owner_effective_date);
1905
1906 ELSIF p_dimension_name like '%Fiscal Year to Date' THEN
1907 SELECT fnd_date.canonical_to_date(org_information11)
1908 INTO l_beg_of_fiscal_year
1909 FROM pay_payroll_actions PACT,
1910 pay_assignment_actions ASSACT,
1911 hr_organization_information HOI
1912 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
1913 AND HOI.organization_id = PACT.business_group_id
1914 AND PACT.payroll_action_id = ASSACT.payroll_action_id
1915 AND ASSACT.assignment_action_id = p_owner_assignment_action_id;
1916
1917 l_expiry_date := next_fiscal_year(l_beg_of_fiscal_year,
1918 p_owner_effective_date);
1919
1920 ELSIF p_dimension_name like '%Quarter to Date' THEN
1921 l_expiry_date := next_quarter(p_owner_effective_date);
1922
1923 ELSIF p_dimension_name like '%Year to Date' THEN
1924 l_expiry_date := next_year(p_owner_effective_date);
1925
1926 ELSIF p_dimension_name like '%Lifetime to Date' THEN
1927 RETURN FALSE;
1928
1929 /*
1930 ELSE
1931 hr_utility.set_message(801, 'NO_EXP_CHECK_FOR_THIS_DIMENSION');
1932 hr_utility.raise_error;
1933 */
1934
1935 END IF;
1936
1937 RETURN p_user_effective_date >= l_expiry_date;
1938
1939 END date_expired;
1940
1941 /*
1942 ** current_time
1943 **
1944 ** Returns a number like 10660377
1945 ** which is the current time expressed
1946 ** in seconds since 1-Jan-97
1947 */
1948 FUNCTION CurrentTime
1949 RETURN INTEGER
1950 IS
1951 v_now varchar2(16);
1952 v_return integer;
1953 v_seconds integer;
1954 v_minutes integer;
1955 v_hours integer;
1956 v_days integer;
1957 v_base integer :=
1958 to_char ( to_date ( '01/12/1997', 'DD/MM/YYYY' ), 'J' );
1959 begin
1960 select to_char ( sysdate, 'J HH24:MI:SS' )
1961 into v_now
1962 from dual;
1963
1964 v_days := to_number ( substr ( v_now, 1, 7 ) );
1965 v_hours := to_number ( substr ( v_now, 9, 2 ) );
1966 v_minutes := to_number ( substr ( v_now, 12, 2 ) );
1967 v_seconds := to_number ( substr ( v_now, 15, 2 ) );
1968
1969 --hr_utility.trace ( chr(10) || /* for sanity check */
1970 -- to_char ( v_hours ) || ':' ||
1971 -- to_char ( v_minutes ) || ':' ||
1972 -- to_char ( v_seconds ) ||
1973 -- chr(10)
1974 -- );
1975 v_return := (
1976 (
1977 (
1978 (
1979 (
1980 ( v_days - v_base ) * 24
1981 ) + v_hours
1982 ) * 60
1983 ) + v_minutes
1984 ) * 60
1985 ) + v_seconds;
1986 return v_return;
1987 end CurrentTime;
1988
1989
1990 --------------------------------------------------------------------------------
1991 -- Initialisation Code
1992 --------------------------------------------------------------------------------
1993 BEGIN
1994 -- Initialise the context lists.
1995 set_context( 'ASSIGNMENT_ACTION_ID', null );
1996 set_context( 'JURISDICTION_CODE', null );
1997 set_context( 'TAX_UNIT_ID', null );
1998 set_context( 'TAX_GROUP_ID', null );
1999 set_context( 'DATE_EARNED', null );
2000 set_context( 'BALANCE_DATE', null );
2001 set_context( 'BUSINESS_GROUP_ID', null );
2002 -- Initialise the debug message state.
2003 debug_init;
2004 -- set view mode
2005 set_view_mode('ASG');
2006 -- Initialise the SessionVarCount
2007 SessionVarCount := 0;
2008
2009 END pay_ca_balance_view_pkg;
[Home] [Help]