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