1 package body hr_utility as
2 /* $Header: pyutilty.pkb 120.1.12010000.2 2008/12/12 10:54:18 sathkris ship $ */
3 /*
4 Copyright (c) Oracle Corporation (UK) Ltd 1993.
5 All rights reserved
6
7 Name: hr_utility
8
9 Description: See package specification.
10
11 Change List
12 ----------
13 SuSivasu 24-OCT-2005 Carry out the check for the fnd log is enabled
14 before calling the fnd_log.string as this procedure
15 does the same check.
16 dkerr 17-FEB-2005 4192532 : truncate procedure_name parameter
17 at 128 characters.
18 nbristow 28-JAN-2005 Added PAY_LOG option to dump to the
19 Log file.
20 dkerr 11-DEC-2002 Added NOCOPY hint to OUT and IN OUT parameters
21 dkerr 03-DEC-2002 Added debug_enabled()
22 dkerr 13-MAY-2002 Added support for AOL logging to trace() and
23 trace_udf()
24 dkerr 09-MAY-2002 Code workaround for RDBMS 2367861 in
25 write_trace_file()
26
27 nbristow 28-FEB-2002 Added dbdrv statements.
28 nbristow 28-FEB-2002 Changed read_trace_table to have a smaller PL/SQL
29 table. NT could not support a large table.
30 PZWALKER 06-SEP-2001 change if statement in set_location_trace local
31 procedures to enable parallel operation of
32 HR Trace and AOL Logging
33 PZWALKER 05-SEP-2001 Performance Changes:
34 1) Added the following new PRIVATE global
35 variables
36 g_error_stage_aol, g_error_in_procedure_aol
37 2) The set_location procedure has been re-written
38 for performance:
39 - no local variables are initialised
40 - all global package variables are fully
41 qualified
42 3) Removed AOL logging code from main block in
43 set_location to local procedure
44
45 Moved code from from local procedure
46 set_location_trace to procedure
47 set_location_hr_trace_internal to enable
48 code to be called from log_at.. procedures
49
50 Changes to switch_logging_on, switch_logging_off:
51 1) Replaced raise_application_error call with
52 hr_utility.raise_error
53 2) Removed insert/delete into/from fnd_sessions
54
55 Changes to log_at... procedures
56 1) Replaced if-then-else checks for p_message
57 with nvl call
58 2) Removed local variable l_message
59 3) added local procedure set_location_trace
60 4) added call to set_location_hr_trace_internal
61 5) added lower() calls to set module names to
62 lower case
63
64 PZWALKER 24-AUG-2001 Added AOL Logging to set_location()
65 added following procedures for AOL logging:
66 log_at_statement_level()
67 log_at_procedure_level()
68 log_at_event_level()
69 log_at_exception_level()
70 log_at_error_level()
71 log_at_unexpected_level()
72 switch_logging_on()
73 switch_logging_off()
74 changed != to <>
75 NBRISTOW 18-JUL-2001 Added read_trace_table to improve PYUPIP
76 performance.
77 DKERR 24-SEP-1999 Call dbms_output.enable(1000000) if trace dest
78 set appropriately
79 DHARRIS 23-AUG-1999 115.12
80 Performance changes for set_location which
81 comprise of:
82 1) Added the following new PRIVATE global variables
83 g_trace_on, g_sl_x, g_sl_mess_text
84 2) Set the g_trace_on boolean trace indicator variable
85 to TRUE in private_trace_on.
86 3) Set the g_trace_on boolean trace indicator variable
87 to FALSE in trace_off.
88 4) The set_location procedure has been re-written for
89 performance:
90 - no local variables are initialised
91 - all global package variables are fully qualified
92 - the new sub-procedure set_location_trace is only
93 - called when trace is on.
94 TBATTOO 24-MAY-1999 changed chk_product_install to use hr_legislation_installations
95 MREID 18-MAY-1999 Added I to language code check for 11i
96 DKERR 13-MAY-1999 Replaced chr() with fnd_global.local_chr()
97 which is portable.
98 DKERR 12-May-1999 trace/set_location can use dbms_output.
99 scgrant 21-Apr-1999 Multi-radix changes.
100 DKERR 23-MAR-1999 Added set_trace_options and support for
101 writing to the rdbms trace file.
102 MREID 06-NOV-1998 Added language parameter to chk_product_install
103 DKERR 19-AUG-1998 523846: changed substr to substrb
104 DKERR 20-JUL-1998 Changes to trace to ensure compatiblity with NT
105 Added trace_udf to allow trace statements
106 to be added to formula.
107 MREID 02-JUL-1998 Change to chk_product_install - only accept
108 fully installed.
109 ACHAUHAN 17-OCT-1997 Changed the chk_product_install to check
110 the fnd_application table against the
111 application short name and not the application
112 name. If the product is passed as
113 'Oracle Payroll' then the application short
114 name is formed as 'PAY'.
115
116 D Kerr 22-SEP-1997 Fix to get_message to ensure it works in
117 the same way as R10. ie calling it still leaves
118 the message on the FND message stack.
119
120 D Kerr 27-JUL-1997 R11 changes.
121 WARNING : Not compatible with R10
122
123 Removed most of the code from message routines as it
124 was either accessing obsoleted tables or was non NLS compliant
125 or both. The code now uses FND_MESSAGE routines rather than
126 accessing tables directly. Package globals are still maintained
127 for use in get_message and get_message_details.
128
129 Removed : show_error, get_token_details.
130
131 110.1 10-JUL-1997 M.J.Hoyes Bug 513048. Switched the cursors in the
132 10.7 version 40.14 set_message procedure to validate against
133 fnd_new_messages and then fnd_messages
134 rather than vice versa. Re coded the
135 set message token procedure to use cursors
136 rather than PLSQL blocks and switched the
137 first table to drive off to fnd_new_messages.
138
139 K Mundair 02-JUN-1997 Added procedure chk_product_install
140 D Kerr 29-Nov-1996 New trace functionality
141 1. trace_on now takes a parameter which can
142 be used to give an alternative name to
143 the pipe.
144 2. read_trace_pipe routine moves the pipe
145 handling code from PYUPIP to this package.
146 T Mathers 18-Aug-1996 Changed message and message token code
147 to use fnd_new_messages id there is no row
148 in fnd_messages. Behave as normal if not in
149 both.
150 D Kerr 08-Jul-1996 Set a message in the case of pipe timeout
151 D Kerr 03-Jul-1996 Added timeout parameter to send_message calls
152 in trace and set_location.
153 D Harris 24-Jun-1995 merged trace with set_location to improve
154 performance.
155 T Mathers 28-Jun-1995 Added joins to fnd_current_language_view
156 for fnd_messages cursor in set_message
157 and set_message_token. WWBUG#288067
158 D Harris 22-Jun-1995 Increased g_message_text from 240 to
159 2048 (max length required for use
160 in dbms_standard.raise_application_error).
161 P Gowers 28-NOV-1994 G1682: Always call fnd_message.set_name()
162 D Harris 07-OCT-1994 Increment length of set_location pipe
163 details from 40 to 72.
164 D Harris 16-SEP-1993 Changes made for 10x compatibility for
165 forms 4 and aol api's.
166 P Gowers 04-MAY-1993 Handle hint messages in set_message
167 P Gowers 05-APR-1993 Add get_message_details, get_token_details
168 P Gowers 02-MAR-1993 Add set_message_token which translates token
169 P Gowers 20-JAN-1993 Big bang.
170
171 Sathkris 12-DEC-2008 Added get_icx_val function
172
173
174 */
175
176 --
177 -- Private Package variables
178 --
179 g_warning_flag boolean default FALSE;
180 g_pipe_session varchar2(30);
181 g_trace_coverage varchar2(1); -- null (same as 'F'), 'F' or 'T'
182
183 -- DK 23-MAR-99 This feature is new
184 -- The default destination is DBMS_PIPE but may also be
185 -- 1. TRACE_FILE
186 -- 2. TBD ..
187 g_trace_dest varchar2(30) default 'DBMS_PIPE';
188
189 -- [115.12 START] DH 23-AUG-1999
190 -- Performance changes for set_location
191 g_trace_on BOOLEAN := FALSE; -- determines if trace is on
192 g_sl_x INTEGER;
193 g_sl_mess_text VARCHAR2(200);
194 -- [115.12 END]
195
196 --
197 -- Error location variables - help to locate oracle errors
198 --
199 g_error_stage number := 0 ;
200 g_error_in_procedure varchar2(128) := null ;
201 g_error_stage_aol varchar2(128) := null ;
202 g_error_in_procedure_aol varchar2(128) := null ;
203
204 -- These variables are needed to support get_message and get_message_details
205 g_message_name varchar2(30);
206 g_message_number number;
207 g_message_applid number;
208 g_msg_prefix varchar2(30) := fnd_message.get_string('FND','AFDICT_APP_PREFIX');
209
210 -- NAME
211 --
212 -- who_called_me
213 --
214 -- DESCRIPTION
215 --
216 -- Returns details of the invoking program unit call at a given depth
217 -- in the call stack.
218 --
219 -- PARAMETERS
220 --
221 -- str OUT varchar2
222 --
223 -- String identifying line number in calling package
224 -- eg HRENTMNT.LINE234
225 --
226 -- call_depth IN number
227 --
228 -- The depth from this function in the call stack for which details
229 -- are returned.
230 --
231 -- 1 ME
232 -- 2 MY Caller
233 -- 3 Their Caller
234 -- 4 Their Caller's Caller
235 --
236 -- NOTES
237 --
238 -- This procedure is taken from OWA_UTIL (privutil.sql) package - passing
239 -- a single out parameter and adding an additional parameter which reports
240 -- on the given invoker in the call stack.
241 -- Note this routine will not return the calling function if the current pl/sql
242 -- context was invokved via an RPC. Unfortunately this includes clients
243 -- with embedded pl/sql engines such as Forms and Reports.
244 -- See bugs 1011954, 505441 and 1089472 for details.
245 --
246 -- If this routine is public most clients will want call_depth 3 (the default)
247 -- supporting the use of this procedure embedded in a utility procedure and
248 -- returns the name of callers to that utility function.
249 --
250 -- The routine in this package which call WHO_CALLED_ME passes a call_depth
251 -- value of 4 because, for performance reasons, it is itself in a local
252 -- procedure of such a utility function (hr_utility.trace())
253 --
254 -- It doesn't look feasible to return the name of the PU within the calling
255 -- package so we return just the package name and line number. There may be
256 -- value in adding the version number of the package, or if called from
257 -- FastFormula the name of the formula.
258 --
259 --
260 procedure who_called_me (str out nocopy varchar2,
261 call_depth in number default 3) is
262 owner varchar2(100);
263 name varchar2(100);
264 lineno number ;
265 caller_t varchar2(100);
266
267 call_stack varchar2(4096);
268 n number;
269 found_stack BOOLEAN default FALSE;
270 line varchar2(255);
271 t varchar2(255);
272 cnt number := 0;
273 begin
274 --
275
276 --
277 -- If stack unavailable then return a tag indicating the current
278 -- pl/sql stack is the result of an RPC invocation.
279 -- See bugs 1011954, 505441 and 1089472.
280 --
281 call_stack := dbms_utility.format_call_stack;
282 if ( call_stack is null ) then
283 str := 'RPC-CALL';
284 return;
285 end if;
286
287 loop
288
289 n := instr( call_stack, fnd_global.local_chr(10) );
290 exit when ( cnt = call_depth or n is NULL or n = 0 );
291 --
292 line := substr( call_stack, 1, n-1 );
293 call_stack := substr( call_stack, n+1 );
294 --
295 if ( NOT found_stack ) then
296 if ( line like '%handle%number%name%' ) then
297 found_stack := TRUE;
298 end if;
299 else
300 cnt := cnt + 1;
301 if ( cnt = call_depth ) then
302 -- Fix 718865
303 --lineno := to_number(substr( line, 13, 6 ));
304 --line := substr( line, 21 );
305 n := instr(line, ' ');
306 if (n > 0)
307 then
308 t := ltrim(substr(line, n));
309 n := instr(t, ' ');
310 end if;
311 if (n > 0)
312 then
313 lineno := to_number(substr(t, 1, n - 1));
314 line := ltrim(substr(t, n));
315 else
316 lineno := 0;
317 end if;
318 if ( line like 'pr%' ) then
319 n := length( 'procedure ' );
320 elsif ( line like 'fun%' ) then
321 n := length( 'function ' );
322 elsif ( line like 'package body%' ) then
323 n := length( 'package body ' );
324 elsif ( line like 'pack%' ) then
325 n := length( 'package ' );
326 else
327 n := length( 'anonymous block ' );
328 end if;
329 caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
330 line := substr( line, n );
331 n := instr( line, '.' );
332 owner := ltrim(rtrim(substr( line, 1, n-1 )));
333 name := ltrim(rtrim(substr( line, n+1 )));
334 end if;
335 end if;
336 end loop;
337
338 str := name || '.LINE' || lineno ;
339
340 end who_called_me ;
341
342 procedure write_trace_file (p_text in varchar2 ) is
343 pragma autonomous_transaction ;
344 begin
345
346 --
347 -- Write change to trace file
348 --
349
350 -- Bug 2367861
351 --
352 -- Passing an empty but not null string causes server core dump
353 -- in 9.0.1. The intent is to give a blank line in the output
354 -- which can be achieved with a space character.
355 --
356 -- 13-MAY-2002 - add length restriction. ksdwrt max length is undocumented
357 -- 1100 can cause a coredump, 900 seems to work but for the use for which
358 -- hr_utility.trace() was originally designed, truncating at 255 seems
359 -- safe - particularly when there is the alternative of AOL's log feature
360 -- allows 4000 characters
361 --
362 sys.dbms_system.ksdwrt(1,substr(nvl(p_text,' '),1,255));
363
364 --
365 -- Flush changes immediately. Potentially slow ??
366 --
367 sys.dbms_system.ksdfls;
368 end;
369 --
370 ---------------------------------- trace ------------------------------
371 /*
372 NAME
373 trace - output text string (if tracing on)
374 */
375 procedure trace_local(trace_data in varchar2) is
376 l_x integer;
377 l_caller varchar2(100);
378 begin
379
380 --
381 --Optimize for fnd_log - uncomment if required
382 --
383 if fnd_log.g_current_runtime_level <= fnd_log.level_statement
384 then
385 --
386
387 who_called_me(l_caller,4);
388
389 -- A couple of modules call hr_trace('') in order to separate groups of
390 -- related trace() calls. As it stands fnd_log.string won't accept a
391 -- null value but instead raises an ORA-1400 with no fnd_message
392 -- context back to forms. fnd_log.string will accept a ' ' however
393 -- it seems risky to leave that in case the routine rtrims. For now
394 -- we pass a '.' and may file bug.
395
396 fnd_log.string(fnd_log.level_statement,
397 lower('per.plsql.'||l_caller),
398 nvl(trace_data,'.'));
399 --
400 --Optimize for fnd_log - uncomment if required
401 --
402 end if;
403 --
404
405 -- output information to pipe if tracing is enabled
406 if g_pipe_session is not null then
407 if g_trace_dest = 'DBMS_PIPE'
408 then
409 dbms_pipe.pack_message( trace_data );
410 l_x := dbms_pipe.send_message( pipename => g_pipe_session,
411 timeout => PIPE_PUT_TIMEOUT );
412 if ( l_x <> 0 ) then
413
414 -- Don't call other functions here if they call trace
415 -- Set a message so that the error is visible in forms.
416 fnd_message.set_name('PAY','HR_51356_UTILITY_PIPE_TIMEOUT');
417 fnd_message.raise_error ;
418
419 end if;
420
421 elsif g_trace_dest = 'DBMS_OUTPUT' then
422
423 -- DK 14-MAY-2002 Truncate at 255 to prevent error
424 dbms_output.put_line(substr(trace_data,1,255));
425 --
426 elsif g_trace_dest = 'PAY_LOG' then
427 --
428 pay_proc_logging.PY_LOG_TXT(
429 p_logging_type => pay_proc_logging.PY_HRTRACE,
430 p_print_string => substr(trace_data,1,3000));
431 --
432 else
433
434 write_trace_file(trace_data);
435
436 end if;
437 end if;
438 end trace_local;
439
440 procedure trace (trace_data in varchar2) is
441 pragma autonomous_transaction ;
442 begin
443
444 if fnd_log.g_current_runtime_level > fnd_log.level_statement
445 and NOT hr_utility.g_trace_on
446 then
447 return ;
448 else
449 trace_local(trace_data);
450 end if;
451
452 end trace;
453 ---------------------------------- trace_on ------------------------------
454 /*
455 NAME
456 trace_on - enables output to debugging pipe
457 DESCRIPTION
458
459 If the session_identifier is not entered then the name of the pipe
460 defaults to PIPE<session_id>.
461 Otherwise it is set in the following way
462
463 PID - PID<process id>.
464 'process id' is the PID column from v$process
465
466 REQID - REQID<conc. request id>.
467 'conc. request id' is taken from the CONC_REQUEST_ID profile option
468
469
470 WEB - WEB<icx session id>
471 'icx session id' is taken from ICX_SESSIONS
472
473 If any other value is passed then this is used as the pipe name.
474
475 Ideally a pipe receiver should be started before running this statement.
476 Note that an unmonitored client process will hang once the default max
477 pipesize has been reached. This is currently 8192 bytes.
478 The timeout period for unread pipes is set to PIPE_PUT_TIMEOUT seconds
479
480 NOTES
481 In order to allow this routine to be called from Forms which uses
482 PL/SQL v1 overloads and a private implementation are used to handle
483 the defaulting
484 */
485 procedure private_trace_on(trace_mode in varchar2 default null,
486 session_identifier in varchar2 default null ) is
487
488 --Retrieves the session_id value from ICX_SESSIONS. As web server is only
489 --optionally installed need to use DBMS_SQL
490 function get_web_id return number is
491 l_cursor integer;
492 l_retval varchar2(40) := 'NULL'; -- Need to initialize
493 l_ignore number ;
494 begin
495
496 l_cursor := dbms_sql.open_cursor ;
497 dbms_sql.parse(l_cursor,
498 'begin :retval := icx_sec.getID(icx_sec.pv_session_id);end;',
499 dbms_sql.v7);
500 dbms_sql.bind_variable(l_cursor,':retval',l_retval);
501 l_ignore := dbms_sql.execute(l_cursor);
502 dbms_sql.variable_value(l_cursor,':retval' , l_retval ) ;
503
504 return( fnd_number.canonical_to_number(l_retval) ) ;
505
506 end get_web_id ;
507
508 -- Retrieves the current process id from V$PROCESS
509 function get_process_id return number is
510 l_retval number := null ;
511 cursor getpid is
512 select p.pid
513 from v$process p,
514 v$session s
515 where s.paddr = p.addr
516 and s.audsid = userenv('sessionid');
517 begin
518
519 open getpid;
520 fetch getpid into l_retval ;
521 close getpid ;
522
523 return ( l_retval ) ;
524
525 end get_process_id ;
526
527
528 begin
529
530 if ( session_identifier is null )
531 then
532
533 select 'PIPE' || userenv('sessionid')
534 into g_pipe_session
535 from dual;
536
537 elsif ( session_identifier = 'PID' )
538 then
539
540
541 g_pipe_session := session_identifier||to_char(get_process_id);
542
543 elsif ( session_identifier = 'REQID' )
544 then
545
546 g_pipe_session := session_identifier||fnd_profile.value('CONC_REQUEST_ID');
547
548 elsif ( session_identifier = 'WEB' )
549 then
550
551 g_pipe_session := session_identifier||to_char(get_web_id) ;
552
553 else
554
555 g_pipe_session := session_identifier ;
556
557 end if;
558
559 g_trace_coverage := trace_mode;
560
561 -- [115.12 START] DH 23-AUG-1999
562 -- set the global TRACE BOOLEAN indicator to TRUE
563 g_trace_on := TRUE;
564 -- [115.12 END]
565
566 end private_trace_on;
567 procedure trace_on is
568 begin
569 private_trace_on;
570 end trace_on ;
571 procedure trace_on (trace_mode in varchar2 ) is
572 begin
573 private_trace_on(trace_mode) ;
574 end trace_on ;
575 procedure trace_on (trace_mode in varchar2 ,
576 session_identifier in varchar2 ) is
577 begin
578 private_trace_on(trace_mode,session_identifier) ;
579 end trace_on ;
580 ---------------------------------- trace_off ------------------------------
581 /*
582 NAME
583 trace_off - disables output to debugging pipe
584 DESCRIPTION
585 don't use until a pipe receiver has been started
586 */
587 procedure trace_off is
588 begin
589 g_pipe_session := null;
590
591 -- [115.12 START] DH 23-AUG-1999
592 -- set the global TRACE BOOLEAN indicator to FALSE
593 g_trace_on := FALSE;
594 -- [115.12 END]
595
596 end trace_off;
597 ---------------------------------- hr_error ----------------------------------
598 /*
599 NAME
600 hr_error - Returns the equivalent sqlcode of hr_error exception
601 DESCRIPTION
602 Needed because forms 3.0/4.0 cannot handle package exception 'hr_error'
603 function hr_error return number is
604 begin
605 return HR_ERROR_NUMBER;
606 end hr_error;
607 */
608 -------------------------- set_location_hr_trace_internal ----------------------
609 /*
610 NAME
611 set_location_hr_trace_internal
612 DESCRIPTION
613 Sets package variables to store location name and stage number which
614 enables unexpected errors to be located more easily
615 This procedure incorporates the trace procedure for performance reasons
616 */
617 -- [115.16 START] PW 05-SEP-2001
618 -- This code was moved from local procedure set_location_trace declared
619 -- in procedure set_location to enable it to be called from log_at..
620 -- procedures
621
622 PROCEDURE set_location_hr_trace_internal IS
623 BEGIN
624 hr_utility.g_sl_mess_text := RPAD(hr_utility.g_error_in_procedure,72)||
625 TO_CHAR(hr_utility.g_error_stage);
626 IF hr_utility.g_trace_dest = 'DBMS_PIPE' THEN
627 dbms_pipe.pack_message(hr_utility.g_sl_mess_text);
628 hr_utility.g_sl_x := dbms_pipe.send_message
629 (pipename => hr_utility.g_pipe_session
630 ,timeout => PIPE_PUT_TIMEOUT);
631 IF (hr_utility.g_sl_x <> 0) THEN
632 -- Don't call other functions here if they call trace
633 -- Set a message so that the error is visible in forms.
634 fnd_message.set_name('PAY','HR_51356_UTILITY_PIPE_TIMEOUT');
635 fnd_message.raise_error ;
636 END IF;
637 ELSIF hr_utility.g_trace_dest = 'DBMS_OUTPUT' THEN
638 dbms_output.put_line(hr_utility.g_sl_mess_text);
639 --
640 ELSIF hr_utility.g_trace_dest = 'PAY_LOG' then
641 --
642 pay_proc_logging.PY_LOG_TXT(
643 p_logging_type => pay_proc_logging.PY_HRTRACE,
644 p_print_string => substr(hr_utility.g_sl_mess_text,1,3000));
645 --
646 ELSE
647 write_trace_file(hr_utility.g_sl_mess_text);
648 END IF;
649 END set_location_hr_trace_internal;
650 -------------------------------- set_location --------------------------------
651 /*
652 NAME
653 set_location
654 DESCRIPTION
655 Sets package variables to store location name and stage number which
656 enables unexpected errors to be located more easily
657 This procedure incorporates the trace procedure for performance reasons
658 */
659 -- [115.12 START] DH 23-AUG-1999
660 -- The set_location procedure has been re-written for performance
661 -- no local variables are initialised
662 -- all global package variables are fully qualified
663 -- the new sub-procedure set_location_trace is only called
664 -- when trace is on.
665 PROCEDURE set_location (procedure_name IN VARCHAR2, stage IN NUMBER) IS
666 -- sub-procedure for AOL Logging
667 PROCEDURE set_location_trace IS
668 BEGIN
669 if fnd_log.g_current_runtime_level<=fnd_log.level_procedure then
670
671 --
672 -- 4192532 Callers to set_location may be passing in very long
673 -- text strings for the procedure_name param. In these cases the
674 -- hr_utility.trace() should have been used instead.
675 -- Defensively we truncate the procedure_name at 128 characters
676 -- which should be long enough for correct use of set_location()
677 --
678 hr_utility.g_error_in_procedure_aol
679 := substr(LTRIM(procedure_name),1,128);
680
681 if SUBSTR(hr_utility.g_error_in_procedure_aol,1,10)
682 ='Entering: ' then
683 fnd_log.string(fnd_log.level_procedure,'per.plsql.'
684 ||LTRIM(SUBSTR(hr_utility.g_error_in_procedure_aol,11))
685 ||'.entering',to_char(stage));
686 elsif SUBSTR(hr_utility.g_error_in_procedure_aol,1,9)
687 ='Leaving: ' then
688 fnd_log.string(fnd_log.level_procedure,'per.plsql.'
689 ||LTRIM(SUBSTR(hr_utility.g_error_in_procedure_aol,10))
690 ||'.leaving',to_char(stage));
691 elsif fnd_log.g_current_runtime_level <=fnd_log.level_statement then
692 hr_utility.g_error_stage_aol := TO_CHAR(stage);
693 fnd_log.string(fnd_log.level_statement,
694 'per.plsql.'||hr_utility.g_error_in_procedure_aol
695 ||'.'||hr_utility.g_error_stage_aol,
696 hr_utility.g_error_stage_aol);
697 end if;
698 end if;
699 if hr_utility.g_trace_on THEN
700 hr_utility.g_error_stage := stage;
701
702 -- 4192532
703 hr_utility.g_error_in_procedure := substr(procedure_name,1,128);
704
705 hr_utility.set_location_hr_trace_internal;
706 end if;
707 END set_location_trace;
708 BEGIN
709 -- Check for Logging
710 if fnd_log.g_current_runtime_level>fnd_log.level_procedure
711 and NOT hr_utility.g_trace_on then
712 RETURN;
713 else
714 set_location_trace;
715 END IF;
716 END set_location;
717 -- [115.12 END]
718 ----------------------------- clear_message --------------------------------
719 /*
720 NAME
721 clear_message
722 DESCRIPTION
723 Clears message globals
724 */
725 procedure clear_message is
726 begin
727
728 hr_utility.g_message_number := null;
729 hr_utility.g_message_name := null;
730 fnd_message.clear ;
731
732 end clear_message;
733 -------------------------------- set_message --------------------------------
734 /*
735 NAME
736 set_message
737 DESCRIPTION
738 Calls FND_MESSAGE.SET_NAME and sets the message name and application id as
739 package globals.
740
741 */
742 procedure set_message (applid in number, l_message_name in varchar2) is
743 --
744 begin
745
746 g_message_name := l_message_name;
747 g_message_applid := applid;
748 --
749 fnd_message.set_name( hr_general.get_application_short_name(applid),
750 l_message_name );
751
752 end set_message;
753 --
754 ------------------------------ set_message_token ------------------------------
755 /*
756 NAME
757 set_message_token
758 DESCRIPTION
759 Sets message token. Just calls AOL routine.
760 */
761 procedure set_message_token (l_token_name in varchar2,
762 l_token_value in varchar2) is
763 begin
764
765 fnd_message.set_token(l_token_name, l_token_value, translate => false );
766
767 end set_message_token;
768 ------------------------------ set_message_token ------------------------------
769 /*
770 NAME
771 set_message_token
772 DESCRIPTION
773 Overloaded: Sets up a translated message token
774 Note that the application id passed a parameter is ignored. The FND_MESSAGE
775 routine uses the application of the last message that was set.
776 */
777 procedure set_message_token (l_applid in number,
778 l_token_name in varchar2,
779 l_token_message in varchar2)
780 is
781 begin
782
783 fnd_message.set_token(l_token_name,l_token_message, translate => true );
784
785 end set_message_token;
786 -------------------------------- get_message --------------------------------
787 /*
788 NAME
789 get_message
790 DESCRIPTION
791 Assembles the current message text and returns it. This is different to
792 FND_MESSAGE.GET in that it prefixes the text with 'APP-nnnnn' where
793 nnnnn is the zero padded message number
794
795 Note that after calling the FND get routines we put the message back
796 onto the 'stack' so that the routine can be called more than once.
797
798 */
799 function get_message return varchar2 is
800
801 l_msg_encoded varchar2(2048) ;
802 l_msg_appl varchar2(40) ;
803 l_msg_name varchar2(80) ;
804 l_msg_number number ;
805 l_pos1 number ;
806 l_pos2 number ;
807 l_msg_text varchar2(2048) ;
808
809 begin
810
811 -- After retrieving the message it mut be set again so that
812 -- subsequent calls to 'get' routines work.
813 --
814 l_msg_encoded := fnd_message.get_encoded ;
815 fnd_message.set_encoded(l_msg_encoded);
816
817 if ( l_msg_encoded is null )
818 then
819 return null;
820 end if;
821
822
823 -- Extract the message application and name from the encoded string
824 -- so that we can get the message number for display purposes.
825 l_pos1 := instr(l_msg_encoded,fnd_global.local_chr(12));
826 l_pos2 := instr(l_msg_encoded,fnd_global.local_chr(12),1,2);
827 l_msg_appl := substrb(l_msg_encoded,1,l_pos1 -1 );
828 l_msg_name := substrb(l_msg_encoded,
829 l_pos1 +1,
830 l_pos2 - l_pos1 - 1);
831 l_msg_number := fnd_message.get_number(l_msg_appl,l_msg_name);
832
833
834 if ( l_msg_number is null or l_msg_number = 0 ) then
835
836 l_msg_text := fnd_message.get ;
837
838 else
839
840 -- Assemble the message in the form 'APP-nnnnn : message text'
841 -- The number of zeros is AFD_MSG_NUM_BYTES
842 l_msg_text := g_msg_prefix||'-'||to_char(l_msg_number,'FM00000')||' '
843 ||fnd_message.get;
844
845 end if;
846
847 -- Put the message back so that it is available in other contexts
848 fnd_message.set_encoded(l_msg_encoded);
849
850 return(l_msg_text) ;
851
852 end get_message;
853 ---------------------------- get_message_details ----------------------------
854 /*
855 NAME
856 get_message_details
857 DESCRIPTION
858 Gets the name and the application short name of the message last set
859 Ideally would use FND_MESSAGE.RETRIEVE but this is AOL only at present.
860 */
861 procedure get_message_details (msg_name in out nocopy varchar2,
862 msg_appl in out nocopy varchar2) is
863 begin
864 msg_appl := 'FND';
865 if hr_utility.g_message_name is null then
866 msg_name := 'NO_MESSAGE';
867 else
868 msg_name := g_message_name;
869 msg_appl := hr_general.get_application_short_name(g_message_applid) ;
870 end if;
871 end get_message_details;
872 -------------------------------- set_warning --------------------------------
873 /*
874 NAME
875 set_warning
876 DESCRIPTION
877 Sets the package warning flag to indicate that a warning has occurred
878 */
879 procedure set_warning is
880 begin
881 g_warning_flag:=TRUE;
882 end set_warning;
883 -------------------------------- check_warning --------------------------------
884 /*
885 NAME
886 check_warning
887 DESCRIPTION
888 Returns the value of the warning flag
889 */
890 function check_warning return boolean is
891 begin
892 return g_warning_flag;
893 end check_warning;
894 -------------------------------- clear_warning --------------------------------
895 /*
896 NAME
897 clear_warning
898 DESCRIPTION
899 Resets the package warning flag
900 */
901 procedure clear_warning is
902 begin
903 g_warning_flag:=FALSE;
904 end clear_warning;
905 -------------------------------- oracle_error --------------------------------
906 /*
907 NAME
908 oracle_error
909 DESCRIPTION
910 Sets generic oracle error message and passes the sqlcode, and error
911 location information
912 */
913 procedure oracle_error (oracode in number) is
914 begin
915 set_message (801,'HR_ORACLE_ERROR');
916 set_message_token ('PROCEDURE', g_error_in_procedure);
917 set_message_token ('TABLE', to_char(g_error_stage));
918 set_message_token ('ORA_MESG', 'ORA' || to_char(oracode));
919 -- reset error location information
920 g_error_stage:=0;
921 g_error_in_procedure:= null;
922 exception
923 -- this gets called from top level exception handler, so never want
924 -- to leave this function with an exception, otherwise we will always
925 -- get 'Unhandled exception' error
926 when others then
927 null;
928 end oracle_error;
929 -------------------------------- raise_error --------------------------------
930 /*
931 NAME
932 raise_error
933 DESCRIPTION
934 Performs raise_application_error but always with the same error number
935 HR_ERROR_NUMBER for consistency
936 */
937 procedure raise_error is
938 begin
939 raise_application_error (hr_utility.hr_error_number, hr_utility.get_message);
940 -- app_exception.raise_exception;
941 end raise_error;
942 -------------------------------- fnd_insert --------------------------------
943 /*
944 NAME
945 fnd_insert
946 DESCRIPTION
947 Inserts a row into FND_SESSIONS for the date passed for the current
948 session id
949 */
950 procedure fnd_insert (l_effective_date in date) is
951 begin
952 insert into fnd_sessions (session_id, effective_date)
953 values (userenv('SESSIONID'), trunc(l_effective_date));
954 end fnd_insert;
955
956 -------------------------------- read_trace_pipe -----------------------------
957 /*
958 NAME
959 read_trace_pipe
960 DESCRIPTION
961 Reads the next message from the named pipe.
962
963 If the pipename is PIPEnnnn then after the given timeout period.
964 The routine will check whether the corresponding session still
965 exists. Support for other types may be added later.
966
967 PARAMETERS
968
969 p_pipename Name of the pipe
970 p_timeout Timeout period. When it is reached the routine
971 will check whether the given session still exists.
972 p_status The return status from DBMS_PIPE.RECEIEVE_MESSAGE
973 p_retval The text retrieved from the pipe
974 */
975 -- See header - this overload provided to w/a NT bug.
976 procedure read_trace_pipe(p_pipename in varchar2,
977 p_status in out nocopy number,
978 p_retval in out nocopy varchar2 ) is
979 begin
980 read_trace_pipe(p_pipename,
981 PIPE_READ_TIMEOUT,
982 p_status,
983 p_retval ) ;
984 end read_trace_pipe;
985
986 procedure read_trace_pipe(p_pipename in varchar2,
987 p_timeout in number,
988 p_status in out nocopy number,
989 p_retval in out nocopy varchar2 ) is
990 s integer;
991 t integer;
992 num number;
993 dt date;
994 chr varchar2(2000);
995 l_dummy varchar2(2000);
996
997 cursor get_session is
998 select 1
999 from v$session
1000 where audsid = replace(p_pipename,'PIPE')
1001 and status <> 'KILLED' ;
1002
1003 begin
1004 chr := null;
1005 loop
1006 s := dbms_pipe.receive_message(p_pipename,p_timeout);
1007 if ( (s = 1) and (p_pipename like 'PIPE%')) then
1008 open get_session ;
1009 fetch get_session into l_dummy ;
1010 if get_session%notfound then
1011 close get_session ;
1012 exit ;
1013 else
1014 close get_session ;
1015 end if;
1016 else
1017 exit ;
1018 end if ;
1019 end loop ;
1020
1021 if s = 0 then
1022 t := 0;
1023 t := dbms_pipe.next_item_type;
1024 if t = 9 then
1025 dbms_pipe.unpack_message(chr);
1026 elsif t = 6 then
1027 dbms_pipe.unpack_message(num);
1028 chr := fnd_number.number_to_canonical(num);
1029 elsif t = 12 then
1030 dbms_pipe.unpack_message(dt);
1031 chr := to_char(dt);
1032 end if;
1033 end if;
1034
1035 p_status := s;
1036 p_retval := chr;
1037
1038 end read_trace_pipe;
1039 --
1040 -------------------------------- read_trace_table -----------------------------
1041 /*
1042 NAME
1043 read_trace_table
1044 DESCRIPTION
1045 Reads the next message from the named pipe into a PL/SQL table.
1046
1047 If the pipename is PIPEnnnn then after the given timeout period.
1048 The routine will check whether the corresponding session still
1049 exists. Support for other types may be added later.
1050
1051 PARAMETERS
1052
1053 p_pipename Name of the pipe
1054 p_status The return status from DBMS_PIPE.RECEIEVE_MESSAGE
1055 p_retval The text PL/SQL table containing the messages
1056 p_messages The maximum number of entries that should be placed
1057 in the PL/SQL table.
1058 p_cnt_mess The number of entries actually created in PL/SQL
1059 table.
1060 */
1061 procedure read_trace_table(p_pipename in varchar2,
1062 p_status in out nocopy number,
1063 p_retval in out nocopy t_varchar180,
1064 p_messages in number,
1065 p_cnt_mess in out nocopy number ) is
1066 s integer;
1067 t integer;
1068 num number;
1069 dt date;
1070 stri varchar2(2000);
1071 l_dummy varchar2(2000);
1072 l_rettab t_varchar180;
1073 int_cnt number;
1074 complete boolean;
1075 --
1076 cursor get_session is
1077 select 1
1078 from v$session
1079 where audsid = replace(p_pipename,'PIPE')
1080 and status <> 'KILLED' ;
1081 --
1082 begin
1083 l_rettab.delete;
1084 int_cnt := 0;
1085 complete := FALSE;
1086 while (complete = FALSE) loop
1087 --
1088 int_cnt := int_cnt + 1;
1089 s := dbms_pipe.receive_message(p_pipename,5);
1090 if ( (s = 1) and (p_pipename like 'PIPE%')) then
1091 open get_session ;
1092 fetch get_session into l_dummy ;
1093 if get_session%notfound then
1094 close get_session ;
1095 s := 6;
1096 else
1097 close get_session ;
1098 end if;
1099 end if;
1100
1101 if s = 0 then
1102 t := 0;
1103 t := dbms_pipe.next_item_type;
1104 if t = 9 then
1105 dbms_pipe.unpack_message(stri);
1106 l_rettab(int_cnt) := substr(stri, 1, 180);
1107 elsif t = 6 then
1108 dbms_pipe.unpack_message(num);
1109 l_rettab(int_cnt) := substr(fnd_number.number_to_canonical(num), 1, 180);
1110 elsif t = 12 then
1111 dbms_pipe.unpack_message(dt);
1112 l_rettab(int_cnt) := substr(to_char(dt), 1, 180);
1113 end if;
1114 end if;
1115 --
1116 if (int_cnt = p_messages) then
1117 complete := TRUE;
1118 else
1119 if (s <> 0) then
1120 complete := TRUE;
1121 end if;
1122 end if;
1123 end loop;
1124 --
1125 p_status := s;
1126 p_retval := l_rettab;
1127 p_cnt_mess := l_rettab.count;
1128 --
1129 end read_trace_table;
1130 -------------------------------- get_trace_id --------------------------------
1131 /*
1132 NAME
1133 get_trace_id
1134 DESCRIPTION
1135 Returns the name of the PIPE that HR trace statements are written
1136 to.
1137 */
1138 function get_trace_id return varchar2 is
1139 begin
1140
1141 return ( g_pipe_session ) ;
1142
1143 end get_trace_id ;
1144 -------------------------------- trace_udf --------------------------------
1145 /*
1146 NAME
1147 trace_udf
1148 DESCRIPTION
1149 Performs the same as trace() but written as a fuction to allow it
1150 to be used as a UDF.
1151 Returns the value 'TRUE' if tracing is enabled otherwise 'FALSE'
1152 */
1153 function trace_udf (trace_data in varchar2) return varchar2 is
1154 retval boolean ;
1155 pragma autonomous_transaction ;
1156 begin
1157
1158
1159 --
1160 -- 13-MAY-2002 Check for AOL logging being enabled.
1161 -- replicates logic of trace() so that trace_local() can
1162 -- be called directly.
1163 --
1164 retval := (fnd_log.g_current_runtime_level = 1) or hr_utility.g_trace_on ;
1165
1166 if (retval) then
1167 trace_local(trace_data) ;
1168 end if;
1169
1170 return( hr_general.bool_to_char(retval) ) ;
1171
1172 end trace_udf ;
1173
1174 -------------------------------- chk_product_install -------------------------
1175 /*
1176 NAME
1177 chk_product_install
1178 DESCRIPTION
1179 Checks whether the product specified is installed for the legislation
1180 specified
1181 PARAMETERS
1182 p_product Short name of the application e.g. PAY,PER,...
1183 p_legislation Legislation code(US,GB...)
1184 */
1185 function chk_product_install (
1186 p_product VARCHAR2,
1187 p_legislation VARCHAR2,
1188 p_language VARCHAR2) return boolean is
1189
1190 --
1191
1192 v_chk VARCHAR2(1);
1193 l_short_name VARCHAR2(10);
1194 v_language VARCHAR2(1);
1195
1196 begin
1197
1198 if p_product = 'Oracle Payroll' then
1199 l_short_name := 'PAY';
1200 elsif p_product = 'Oracle Human Resources' then
1201 l_short_name := 'PER';
1202 else
1203 l_short_name := p_product;
1204 end if;
1205
1206
1207 select 'x'
1208 into v_chk
1209 from hr_legislation_installations
1210 where l_short_name=application_short_name
1211 and nvl(p_legislation,'x')=nvl(legislation_code,'x')
1212 and (status='I' or action is not NULL);
1213
1214 if v_chk = 'x' then
1215 return(true);
1216 else
1217 return(false);
1218 end if;
1219
1220 exception
1221 when no_data_found then
1222 return(false);
1223
1224 --
1225 end chk_product_install;
1226 --
1227 function chk_product_install (
1228 p_product VARCHAR2,
1229 p_legislation VARCHAR2
1230 ) return boolean is
1231 begin
1232 --
1233 return(chk_product_install(p_product, p_legislation, 'US'));
1234 --
1235 end chk_product_install;
1236
1237 procedure set_trace_options (p_options in varchar2 ) is
1238 l_trace_dest varchar2(80) := 'TRACE_DEST:' ;
1239 begin
1240 if (instr(p_options,l_trace_dest) = 1 )
1241 then
1242 g_trace_dest := replace(p_options,l_trace_dest);
1243
1244 -- DK 99-09-24
1245 -- If the user has set dbms_output then
1246 -- we set the buffer to be the maximum size. Ideally
1247 -- the buffer size would only be set if it was the default
1248 -- size but there doesn't seem to be an easy way of doing that
1249 --
1250 if ( g_trace_dest = 'DBMS_OUTPUT' )
1251 then
1252 dbms_output.enable(1000000);
1253 end if;
1254
1255 end if;
1256 end;
1257
1258 ---------------------------- log_at_statement_level -------------------------
1259 /*
1260 NAME
1261 log_at_statement_level
1262
1263 DESCRIPTION
1264
1265 Used for low level logging messages giving maximum detail
1266 Example: Copying string from buffer xyz to buffer zyx
1267
1268 PARAMETERS
1269
1270 p_product Short name of the application e.g. 'pay', 'per',...
1271
1272 p_procedure_name name of calling procedure including package name
1273 eg. package_name.procedure_name
1274
1275 p_label A unique name for the part within the procedure. The major
1276 reason for providing the label is to make a module name uniquely
1277 identify exactly one log call. This will allow support analysts
1278 or programmers who look at logs to know exactly which piece of code
1279 produced your message, even without looking at the message (which
1280 may be translated). So make labels for each log statement unique
1281 within a routine.
1282 If it is desired to group a number of log calls from different
1283 routines and files into a group that can be enabled or disabled
1284 atomically, this can be done with a two part label. The first part
1285 would be the functional group name, and the second part would be
1286 the unique code location. For instance, descriptive flexfield
1287 validation code might have several log calls in different places
1288 with labels desc_flex_val.check_value,
1289 desc_flex_val.display_window, and desc_flex_val.parse_code. Those
1290 would all be enabled by enabling module fnd.%.desc_flex_val even
1291 though they all exist in different code locations.
1292 Examples: begin, lookup_app_id, parse_sql_failed,
1293 myfeature.done_exec
1294
1295 p_message This is the string that will actually be written to the log file.
1296 It will be crafted by the programmer to clearly tell the reader
1297 whatever information needs to be conveyed about the state of the
1298 code execution.
1299 if p_message is omitted the message will default to p_label
1300 */
1301 procedure log_at_statement_level
1302 (p_product IN VARCHAR2
1303 ,p_procedure_name IN VARCHAR2
1304 ,p_label IN VARCHAR2
1305 ,p_message IN VARCHAR2 default null ) IS
1306 PROCEDURE set_location_trace IS
1307 BEGIN
1308 if fnd_log.g_current_runtime_level<=fnd_log.level_statement then
1309 fnd_log.string(fnd_log.level_statement,lower(p_product||'.plsql.'
1310 ||p_procedure_name)||'.'||p_label,nvl(p_message,p_label));
1311 end if;
1312 if hr_utility.g_trace_on THEN
1313 hr_utility.g_error_stage := p_label;
1314 hr_utility.g_error_in_procedure := p_procedure_name;
1315 hr_utility.set_location_hr_trace_internal;
1316 end if;
1317 END set_location_trace;
1318 BEGIN
1319 -- Check for Logging
1320 if fnd_log.g_current_runtime_level>fnd_log.level_statement
1321 and NOT hr_utility.g_trace_on then
1322 RETURN;
1323 else
1324 set_location_trace;
1325 END IF;
1326 END log_at_statement_level;
1327 ---------------------------- log_at_procedure_level -------------------------
1328 /*
1329 NAME
1330 log_at_procedure_level
1331
1332 DESCRIPTION
1333
1334 Used to log messages called upon entry and/or exit from a routine
1335 Example: Entering routine fdllov()
1336
1337 PARAMETERS
1338
1339 p_product Short name of the application e.g. 'pay', 'per',...
1340
1341 p_procedure_name name of calling procedure including package name
1342 eg. package_name.procedure_name
1343
1344 p_label A unique name for the part within the procedure. The major
1345 reason for providing the label is to make a module name uniquely
1346 identify exactly one log call. This will allow support analysts
1347 or programmers who look at logs to know exactly which piece of code
1348 produced your message, even without looking at the message (which
1349 may be translated). So make labels for each log statement unique
1350 within a routine.
1351 If it is desired to group a number of log calls from different
1352 routines and files into a group that can be enabled or disabled
1353 atomically, this can be done with a two part label. The first part
1354 would be the functional group name, and the second part would be
1355 the unique code location. For instance, descriptive flexfield
1356 validation code might have several log calls in different places
1357 with labels desc_flex_val.check_value,
1358 desc_flex_val.display_window, and desc_flex_val.parse_code. Those
1359 would all be enabled by enabling module fnd.%.desc_flex_val even
1360 though they all exist in different code locations.
1361 Examples: begin, lookup_app_id, parse_sql_failed,
1362 myfeature.done_exec
1363
1364 p_message This is the string that will actually be written to the log file.
1365 It will be crafted by the programmer to clearly tell the reader
1366 whatever information needs to be conveyed about the state of the
1367 code execution.
1368 if p_message is omitted the message will default to p_label
1369 */
1370 procedure log_at_procedure_level
1371 (p_product IN VARCHAR2
1372 ,p_procedure_name IN VARCHAR2
1373 ,p_label IN VARCHAR2
1374 ,p_message IN VARCHAR2 default null ) IS
1375 PROCEDURE set_location_trace IS
1376 BEGIN
1377 if fnd_log.g_current_runtime_level<=fnd_log.level_procedure then
1378 fnd_log.string(fnd_log.level_procedure,lower(p_product||'.plsql.'
1379 ||p_procedure_name)||'.'||p_label,nvl(p_message,p_label));
1380 end if;
1381 if hr_utility.g_trace_on THEN
1382 hr_utility.g_error_stage := p_label;
1383 hr_utility.g_error_in_procedure := p_procedure_name;
1384 hr_utility.set_location_hr_trace_internal;
1385 end if;
1386 END set_location_trace;
1387 BEGIN
1388 -- Check for Logging
1389 if fnd_log.g_current_runtime_level>fnd_log.level_procedure
1390 and NOT hr_utility.g_trace_on then
1391 RETURN;
1392 else
1393 set_location_trace;
1394 END IF;
1395 END log_at_procedure_level;
1396 -------------------------------- log_at_event_level -------------------------
1397 /*
1398 NAME
1399 log_at_event_level
1400
1401 DESCRIPTION
1402
1403 Used for high level logging message
1404 Examples: User pressed "Abort" button
1405 Beginning establishment of apps security session
1406
1407 PARAMETERS
1408
1409 p_product Short name of the application e.g. 'pay', 'per',...
1410
1411 p_procedure_name name of calling procedure including package name
1412 eg. package_name.procedure_name
1413
1414 p_label A unique name for the part within the procedure. The major
1415 reason for providing the label is to make a module name uniquely
1416 identify exactly one log call. This will allow support analysts
1417 or programmers who look at logs to know exactly which piece of code
1418 produced your message, even without looking at the message (which
1419 may be translated). So make labels for each log statement unique
1420 within a routine.
1421 If it is desired to group a number of log calls from different
1422 routines and files into a group that can be enabled or disabled
1423 atomically, this can be done with a two part label. The first part
1424 would be the functional group name, and the second part would be
1425 the unique code location. For instance, descriptive flexfield
1426 validation code might have several log calls in different places
1427 with labels desc_flex_val.check_value,
1428 desc_flex_val.display_window, and desc_flex_val.parse_code. Those
1429 would all be enabled by enabling module fnd.%.desc_flex_val even
1430 though they all exist in different code locations.
1431 Examples: begin, lookup_app_id, parse_sql_failed,
1432 myfeature.done_exec
1433
1434 p_message This is the string that will actually be written to the log file.
1435 It will be crafted by the programmer to clearly tell the reader
1436 whatever information needs to be conveyed about the state of the
1437 code execution.
1438 if p_message is omitted the message will default to p_label
1439 */
1440 procedure log_at_event_level
1441 (p_product IN VARCHAR2
1442 ,p_procedure_name IN VARCHAR2
1443 ,p_label IN VARCHAR2
1444 ,p_message IN VARCHAR2 default null ) IS
1445 PROCEDURE set_location_trace IS
1446 BEGIN
1447 if fnd_log.g_current_runtime_level<=fnd_log.level_event then
1448 fnd_log.string(fnd_log.level_event,lower(p_product||'.plsql.'
1449 ||p_procedure_name)||'.'||p_label,nvl(p_message,p_label));
1450 end if;
1451 if hr_utility.g_trace_on THEN
1452 hr_utility.g_error_stage := p_label;
1453 hr_utility.g_error_in_procedure := p_procedure_name;
1454 hr_utility.set_location_hr_trace_internal;
1455 end if;
1456 END set_location_trace;
1457 BEGIN
1458 -- Check for Logging
1459 if fnd_log.g_current_runtime_level>fnd_log.level_event
1460 and NOT hr_utility.g_trace_on then
1461 RETURN;
1462 else
1463 set_location_trace;
1464 END IF;
1465 END log_at_event_level;
1466 ----------------------------- log_at_exception_level -------------------------
1467 /*
1468 NAME
1469 log_at_exception_level
1470
1471 DESCRIPTION
1472
1473 Used to to log a message when an internal routine is returning a failure
1474 code or exception, but the error does not necessarily indicate a
1475 problem at the user's level.
1476
1477 Examples: Profile ABC not found,
1478 Networking routine XYZ could not connect; retrying.
1479 File not found (in a low-level file routine)
1480 Database error (in a low-level database routine like afupi)
1481 PARAMETERS
1482
1483 p_product Short name of the application e.g. 'pay', 'per',...
1484
1485 p_procedure_name name of calling procedure including package name
1486 eg. package_name.procedure_name
1487
1488 p_label A unique name for the part within the procedure. The major
1489 reason for providing the label is to make a module name uniquely
1490 identify exactly one log call. This will allow support analysts
1491 or programmers who look at logs to know exactly which piece of code
1492 produced your message, even without looking at the message (which
1493 may be translated). So make labels for each log statement unique
1494 within a routine.
1495 If it is desired to group a number of log calls from different
1496 routines and files into a group that can be enabled or disabled
1497 atomically, this can be done with a two part label. The first part
1498 would be the functional group name, and the second part would be
1499 the unique code location. For instance, descriptive flexfield
1500 validation code might have several log calls in different places
1501 with labels desc_flex_val.check_value,
1502 desc_flex_val.display_window, and desc_flex_val.parse_code. Those
1503 would all be enabled by enabling module fnd.%.desc_flex_val even
1504 though they all exist in different code locations.
1505 Examples: begin, lookup_app_id, parse_sql_failed,
1506 myfeature.done_exec
1507
1508 p_message This is the string that will actually be written to the log file.
1509 It will be crafted by the programmer to clearly tell the reader
1510 whatever information needs to be conveyed about the state of the
1511 code execution.
1512 if p_message is omitted the message will default to p_label
1513 */
1514 procedure log_at_exception_level
1515 (p_product IN VARCHAR2
1516 ,p_procedure_name IN VARCHAR2
1517 ,p_label IN VARCHAR2
1518 ,p_message IN VARCHAR2 default null ) IS
1519 PROCEDURE set_location_trace IS
1520 BEGIN
1521 if fnd_log.g_current_runtime_level<=fnd_log.level_exception then
1522 fnd_log.string(fnd_log.level_exception,lower(p_product||'.plsql.'
1523 ||p_procedure_name)||'.'||p_label,nvl(p_message,p_label));
1524 end if;
1525 if hr_utility.g_trace_on THEN
1526 hr_utility.g_error_stage := p_label;
1527 hr_utility.g_error_in_procedure := p_procedure_name;
1528 hr_utility.set_location_hr_trace_internal;
1529 end if;
1530 END set_location_trace;
1531 BEGIN
1532 -- Check for Logging
1533 if fnd_log.g_current_runtime_level>fnd_log.level_exception
1534 and NOT hr_utility.g_trace_on then
1535 RETURN;
1536 else
1537 set_location_trace;
1538 END IF;
1539 END log_at_exception_level;
1540 -------------------------------- log_at_error_level -------------------------
1541 /*
1542 NAME
1543 log_at_error_level
1544 DESCRIPTION
1545 An error message to the user; logged automatically by Message
1546 Dict calls to "Error()" routines, but can also be logged
1547 by other code.
1548
1549 Examples: User entered a duplicate value for field XYZ
1550 Invalid apps username or password at Signon screen.
1551 Function not available
1552
1553 PARAMETERS
1554
1555 p_product Short name of the application e.g. 'pay', 'per',...
1556
1557 p_procedure_name name of calling procedure including package name
1558 eg. package_name.procedure_name
1559
1560 p_label A unique name for the part within the procedure. The major
1561 reason for providing the label is to make a module name uniquely
1562 identify exactly one log call. This will allow support analysts
1563 or programmers who look at logs to know exactly which piece of code
1564 produced your message, even without looking at the message (which
1565 may be translated). So make labels for each log statement unique
1566 within a routine.
1567 If it is desired to group a number of log calls from different
1568 routines and files into a group that can be enabled or disabled
1569 atomically, this can be done with a two part label. The first part
1570 would be the functional group name, and the second part would be
1571 the unique code location. For instance, descriptive flexfield
1572 validation code might have several log calls in different places
1573 with labels desc_flex_val.check_value,
1574 desc_flex_val.display_window, and desc_flex_val.parse_code. Those
1575 would all be enabled by enabling module fnd.%.desc_flex_val even
1576 though they all exist in different code locations.
1577 Examples: begin, lookup_app_id, parse_sql_failed,
1578 myfeature.done_exec
1579
1580 p_message This is the string that will actually be written to the log file.
1581 It will be crafted by the programmer to clearly tell the reader
1582 whatever information needs to be conveyed about the state of the
1583 code execution.
1584 if p_message is omitted the message will default to p_label
1585 */
1586 procedure log_at_error_level
1587 (p_product IN VARCHAR2
1588 ,p_procedure_name IN VARCHAR2
1589 ,p_label IN VARCHAR2
1590 ,p_message IN VARCHAR2 default null ) IS
1591 PROCEDURE set_location_trace IS
1592 BEGIN
1593 if fnd_log.g_current_runtime_level<=fnd_log.level_error then
1594 fnd_log.string(fnd_log.level_error,lower(p_product||'.plsql.'
1595 ||p_procedure_name)||'.'||p_label,nvl(p_message,p_label));
1596 end if;
1597 if hr_utility.g_trace_on THEN
1598 hr_utility.g_error_stage := p_label;
1599 hr_utility.g_error_in_procedure := p_procedure_name;
1600 hr_utility.set_location_hr_trace_internal;
1601 end if;
1602 END set_location_trace;
1603 BEGIN
1604 -- Check for Logging
1605 if fnd_log.g_current_runtime_level>fnd_log.level_error
1606 and NOT hr_utility.g_trace_on then
1607 RETURN;
1608 else
1609 set_location_trace;
1610 END IF;
1611 END log_at_error_level;
1612 ----------------------------- log_at_unexpected_level -----------------------
1613 /*
1614 NAME
1615 log_at_unexpected_level
1616
1617 DESCRIPTION
1618
1619 An unexpected situation occurred which is likely to indicate
1620 or cause instabilities in the runtime behavior, and which
1621 the System Administrator needs to take action on.
1622 Note to developers: Think very carefully before logging
1623 messages at this level; Administrators are going to get worried
1624 and file high priority bugs if your code logs at this level
1625 frequently.
1626
1627 Examples: Out of memory, Required file not found, Data integrity error
1628 Network integrity error, Internal error, Fatal database error
1629
1630 PARAMETERS
1631
1632 p_product Short name of the application e.g. 'pay', 'per',...
1633
1634 p_procedure_name name of calling procedure including package name
1635 eg. package_name.procedure_name
1636
1637 p_label A unique name for the part within the procedure. The major
1638 reason for providing the label is to make a module name uniquely
1639 identify exactly one log call. This will allow support analysts
1640 or programmers who look at logs to know exactly which piece of code
1641 produced your message, even without looking at the message (which
1642 may be translated). So make labels for each log statement unique
1643 within a routine.
1644 If it is desired to group a number of log calls from different
1645 routines and files into a group that can be enabled or disabled
1646 atomically, this can be done with a two part label. The first part
1647 would be the functional group name, and the second part would be
1648 the unique code location. For instance, descriptive flexfield
1649 validation code might have several log calls in different places
1650 with labels desc_flex_val.check_value,
1651 desc_flex_val.display_window, and desc_flex_val.parse_code. Those
1652 would all be enabled by enabling module fnd.%.desc_flex_val even
1653 though they all exist in different code locations.
1654 Examples: begin, lookup_app_id, parse_sql_failed,
1655 myfeature.done_exec
1656
1657 p_message This is the string that will actually be written to the log file.
1658 It will be crafted by the programmer to clearly tell the reader
1659 whatever information needs to be conveyed about the state of the
1660 code execution.
1661 if p_message is omitted the message will default to p_label
1662 */
1663 procedure log_at_unexpected_level
1664 (p_product IN VARCHAR2
1665 ,p_procedure_name IN VARCHAR2
1666 ,p_label IN VARCHAR2
1667 ,p_message IN VARCHAR2 default null ) IS
1668 PROCEDURE set_location_trace IS
1669 BEGIN
1670 if fnd_log.g_current_runtime_level<=fnd_log.level_unexpected then
1671 fnd_log.string(fnd_log.level_unexpected,lower(p_product||'.plsql.'
1672 ||p_procedure_name)||'.'||p_label,nvl(p_message,p_label));
1673 end if;
1674 if hr_utility.g_trace_on THEN
1675 hr_utility.g_error_stage := p_label;
1676 hr_utility.g_error_in_procedure := p_procedure_name;
1677 hr_utility.set_location_hr_trace_internal;
1678 end if;
1679 END set_location_trace;
1680 BEGIN
1681 -- Check for Logging
1682 if fnd_log.g_current_runtime_level>fnd_log.level_unexpected
1683 and NOT hr_utility.g_trace_on then
1684 RETURN;
1685 else
1686 set_location_trace;
1687 END IF;
1688 END log_at_unexpected_level;
1689
1690 -------------------------------- switch_logging_on -------------------------
1691 /*
1692 NAME
1693 switch_logging_on
1694
1695 DESCRIPTION
1696
1697 Turns on AOL debug message logging at specified level when not using
1698 standard applications login (eg sqlplus session). Logging is enabled
1699 for a user by setting user profile options. The user and responsibility
1700 can be specified with p_user_id and p_responsibility_id .If p_user_id
1701 is not specified the user will default to SYSADMIN. If p_responsibility_id
1702 is not specified the responsibility will default to the first
1703 responsibility in the list of responsibilities for the user ordered by
1704 responsibility_id.
1705
1706 PARAMETERS
1707
1708 p_logging_level: possible values: FND_LOG.LEVEL_UNEXPECTED
1709 FND_LOG.LEVEL_ERROR
1710 FND_LOG.LEVEL_EXCEPTION
1711 FND_LOG.LEVEL_EVENT
1712 FND_LOG.LEVEL_PROCEDURE
1713 FND_LOG.LEVEL_STATEMENT
1714 default is FND_LOG.LEVEL_STATEMENT
1715 p_user_id: user id for which logging will be enabled
1716 p_responsibility_id: responsibility id for which logging will
1717 be enabled
1718 */
1719
1720 procedure switch_logging_on
1721 (p_logging_level in number default fnd_log.level_statement
1722 ,p_user_id in number default null
1723 ,p_responsibility_id in number default null) is
1724 --
1725 -- we'll be updating user profile values so use an autonomous transaction
1726 pragma autonomous_transaction;
1727 --
1728 l_b boolean;
1729 l_user_id number;
1730 l_responsibility_id number;
1731 l_application_id number;
1732 --
1733 cursor c_sysadmin_usr is
1734 select usr.user_id
1735 from fnd_user usr
1736 where usr.user_name = 'SYSADMIN';
1737 --
1738 cursor c_get_responsibility_id(p_user_id number) is
1739 select fur.responsibility_id, fur.responsibility_application_id
1740 from fnd_user_resp_groups fur
1741 where fur.user_id = p_user_id
1742 order by fur.responsibility_id;
1743 --
1744 cursor c_get_application_id(p_resp_id number) is
1745 select resp.application_id
1746 from fnd_responsibility resp
1747 where resp.responsibility_id = p_resp_id;
1748 --
1749 BEGIN
1750 -- if user_id is null then set to sysadmin user
1751 if p_user_id is null then
1752 OPEN c_sysadmin_usr;
1753 FETCH c_sysadmin_usr INTO l_user_id;
1754 if c_sysadmin_usr%notfound then
1755 CLOSE c_sysadmin_usr;
1756 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1757 hr_utility.set_message_token('PROCEDURE', 'hr_utility.switch_logging_on');
1758 hr_utility.set_message_token('STEP','1');
1759 hr_utility.raise_error;
1760 else
1761 CLOSE c_sysadmin_usr;
1762 end if;
1763 else
1764 l_user_id := p_user_id;
1765 end if;
1766 --
1767 -- if responsibility_id is null then select first resp in
1768 -- list ordered by resp key
1769 if p_responsibility_id is null then
1770 OPEN c_get_responsibility_id(l_user_id);
1771 FETCH c_get_responsibility_id INTO l_responsibility_id,
1772 l_application_id;
1773 if c_get_responsibility_id%notfound then
1774 CLOSE c_get_responsibility_id;
1775 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1776 hr_utility.set_message_token('PROCEDURE', 'hr_utility.switch_logging_on');
1777 hr_utility.set_message_token('STEP','2');
1778 hr_utility.raise_error;
1779 else
1780 CLOSE c_get_responsibility_id;
1781 end if;
1782 else
1783 l_responsibility_id := p_responsibility_id;
1784 open c_get_application_id(p_responsibility_id);
1785 fetch c_get_application_id into l_application_id;
1786 if c_get_application_id%notfound then
1787 close c_get_application_id;
1788 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1789 hr_utility.set_message_token('PROCEDURE', 'hr_utility.switch_logging_on');
1790 hr_utility.set_message_token('STEP','3');
1791 hr_utility.raise_error;
1792 else
1793 close c_get_application_id;
1794 end if;
1795 end if;
1796
1797 -- initialize user/resp
1798 fnd_global.apps_initialize(l_user_id,
1799 l_responsibility_id,
1800 l_application_id);
1801 --
1802 -- set user profiles to enable logging and set level
1803 l_b:=fnd_profile.save_user('AFLOG_ENABLED','Y');
1804 l_b:=fnd_profile.save_user('AFLOG_LEVEL',to_char(p_logging_level));
1805 --
1806 -- re-initialize to enable user profiles
1807 fnd_global.apps_initialize(l_user_id,
1808 l_responsibility_id,
1809 l_application_id);
1810 --
1811 -- commit profile option settings
1812 commit;
1813 --
1814 END switch_logging_on;
1815 --
1816 -------------------------------- switch_logging_off -------------------------
1817 /*
1818 NAME
1819 switch_logging_off
1820
1821 DESCRIPTION
1822 Turns off AOL debug messaging previously turned on by calling
1823 switch_logging_on. Logging is disabled by setting user profile
1824 options for the user defined in the prior call to switch_logging_on.
1825 If switch_logging_on is not called before calling
1826 switch_logging_off, the user is set to 'SYSADMIN'.
1827
1828 */
1829 procedure switch_logging_off is
1830 --
1831 -- we'll be updating user profile values so use an autonomous transaction
1832 pragma autonomous_transaction;
1833 --
1834 l_b boolean;
1835 l_user_id number;
1836 l_responsibility_id number;
1837 l_application_id number;
1838 l_temp_responsibility_id number;
1839 l_temp_application_id number;
1840 --
1841 cursor c_sysadmin_usr is
1842 select usr.user_id
1843 from fnd_user usr
1844 where usr.user_name = 'SYSADMIN';
1845 --
1846 cursor c_get_responsibility_id(p_user_id number) is
1847 select fur.responsibility_id, fur.responsibility_application_id
1848 from fnd_user_resp_groups fur
1849 where fur.user_id = p_user_id
1850 order by fur.responsibility_id;
1851 --
1852 cursor c_get_application_id(p_resp_id number) is
1853 select resp.application_id
1854 from fnd_responsibility resp
1855 where resp.responsibility_id = p_resp_id;
1856 --
1857 BEGIN
1858 --
1859 l_user_id := fnd_global.user_id;
1860 -- if user_id is not set then set to sysadmin user
1861 if l_user_id = -1 then
1862 OPEN c_sysadmin_usr;
1863 FETCH c_sysadmin_usr INTO l_user_id;
1864 if c_sysadmin_usr%notfound then
1865 CLOSE c_sysadmin_usr;
1866 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1867 hr_utility.set_message_token('PROCEDURE', 'hr_utility.switch_logging_off');
1868 hr_utility.set_message_token('STEP','1');
1869 hr_utility.raise_error;
1870 else
1871 CLOSE c_sysadmin_usr;
1872 end if;
1873 end if;
1874 --
1875 l_responsibility_id := fnd_global.resp_id;
1876 -- if responsibility_id is null then select first resp in list
1877 -- ordered by resp key
1878 if l_responsibility_id = -1 then
1879 OPEN c_get_responsibility_id(l_user_id);
1880 FETCH c_get_responsibility_id INTO l_responsibility_id, l_application_id;
1881 if c_get_responsibility_id%notfound then
1882 CLOSE c_get_responsibility_id;
1883 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1884 hr_utility.set_message_token('PROCEDURE', 'hr_utility.switch_logging_off');
1885 hr_utility.set_message_token('STEP','2');
1886 hr_utility.raise_error;
1887 else
1888 CLOSE c_get_responsibility_id;
1889 end if;
1890 else
1891 open c_get_application_id(l_responsibility_id);
1892 fetch c_get_application_id into l_application_id;
1893 if c_get_application_id%notfound then
1894 close c_get_application_id;
1895 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1896 hr_utility.set_message_token('PROCEDURE', 'hr_utility.switch_logging_off');
1897 hr_utility.set_message_token('STEP','3');
1898 hr_utility.raise_error;
1899 else
1900 close c_get_application_id;
1901 end if;
1902 end if;
1903 --
1904 -- initialize as user/resp
1905 fnd_global.apps_initialize(l_user_id,
1906 l_responsibility_id,
1907 l_application_id);
1908 --
1909 -- set user profiles to disable logging and set level
1910 l_b:=fnd_profile.save_user('AFLOG_ENABLED','N');
1911 l_b:=fnd_profile.save_user('AFLOG_LEVEL',null);
1912 --
1913 -- re-initialize to enable user profiles
1914 fnd_global.apps_initialize(l_user_id,
1915 l_responsibility_id,
1916 l_application_id);
1917 --
1918 -- commit profile option settings
1919 commit;
1920 --
1921 END switch_logging_off;
1922
1923 ---------------------------- debug_enabled -------------------------
1924 /*
1925 NAME
1926 debug_enabled
1927
1928 DESCRIPTION
1929 Please see package specification for a description and usage examples.
1930 */
1931 function debug_enabled return boolean is
1932 begin
1933 return( fnd_log.g_current_runtime_level <= fnd_log.level_procedure
1934 or hr_utility.g_trace_on ) ;
1935 end debug_enabled;
1936
1937 /*Function added to get the ICX Attribute value*/
1938 FUNCTION get_icx_val(p_attribute_name varchar2,p_session_id number)
1939 RETURN VARCHAR2
1940 IS
1941 p_value varchar2(250);
1942
1943 CURSOR csr_prof_value(p_att_name varchar2,p_sess_id number) is
1944 SELECT value
1945 FROM icx_session_attributes
1946 WHERE session_id = p_sess_id
1947 AND name = p_att_name;
1948
1949
1950 BEGIN
1951
1952 OPEN csr_prof_value(p_attribute_name,p_session_id);
1953 FETCH csr_prof_value INTO p_value;
1954 CLOSE csr_prof_value;
1955
1956 RETURN p_value;
1957
1958 EXCEPTION
1959 WHEN OTHERS THEN
1960 RETURN '0';
1961
1962 END;
1963
1964 end hr_utility;
[Home] [Help]