1 PACKAGE BODY PO_LOG AS
2 -- $Header: PO_LOG.plb 120.0 2005/06/02 01:57:55 appldev noship $
3
4 --
5 -- For more details on the PO logging strategy,
6 -- see the document at
7 -- /podev/po/internal/standards/logging/logging.xml
8 --
9
10 /******************************************************************************
11 ******************************************************************************
12
13 Quick example use reference
14 ---------------------------
15
16 CREATE OR REPLACE PACKAGE BODY MY_PACKAGE AS
17
18 D_PACKAGE_BASE CONSTANT VARCHAR2(50) := PO_LOG.get_package_base('MY_PACKAGE');
19
20 D_my_proc CONSTANT VARCHAR2(100) :=
21 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'my_proc');
22
23 PROCEDURE my_proc(
24 p_in IN PO_TBL_NUMBER
25 , p_inout IN OUT NOCOPY NUMBER
26 , p_out OUT NOCOPY VARCHAR2
27 )
28 IS
29 d_mod CONSTANT VARCHAR2(100) := D_my_proc;
30 d_position NUMBER := 0;
31 BEGIN
32
33 IF PO_LOG.d_proc THEN
34 PO_LOG.proc_begin(d_mod,'p_in',p_in);
35 PO_LOG.proc_begin(d_mod,'p_inout',p_inout);
36 END IF;
37
38 d_position := 1;
39
40 -- do some stuff...
41
42 d_position := 123;
43 IF PO_LOG.d_stmt THEN
44 PO_LOG.stmt(d_mod,d_position,'did some stuff');
45 END IF;
46
47 -- do some more stuff...
48
49 IF PO_LOG.d_proc THEN
50 PO_LOG.proc_end(d_mod,'p_inout',p_inout);
51 PO_LOG.proc_end(d_mod,'p_out',p_out);
52 END IF;
53
54 EXCEPTION
55 WHEN OTHERS THEN
56 IF PO_LOG.d_exc THEN
57 PO_LOG.exc(d_mod,d_position,NULL);
58 END IF;
59 RAISE;
60
61 END my_proc;
62
63 END MY_PACKAGE;
64
65
66 Summary of how to use the logging routines
67 ------------------------------------------
68
69 All logging routines take a module_base. The module base should
70 be derived through calls to get_package_base and get_subprogram_base.
71 These should be stored in CONSTANT package variables instead of
72 being derived directly in the procedure, as the cost of invoking
73 another procedure for each procedure invocation can be avoided.
74
75 For the STATEMENT and PROCEDURE logging routines, signatures are provided
76 that take variable names and variable values. These are convenient ways
77 to log several parameters or variables without having to concatenate
78 big log messages with name/value delimiters, converting variables into
79 strings, etc.
80
81 Use of the different levels of logging statements should follow the
82 following guidelines:
83
84 1-STATEMENT - PO_LOG.stmt()
85
86 Use STATEMENT-level for general debugging messages within the body
87 of a method / procedure.
88
89 Dynamic SQL must be recorded with STATEMENT-level logging, as well
90 as all bind variables that are used while executing the SQL.
91
92 2-PROCEDURE - PO_LOG.proc_xxx()
93
94 At the very beginning of every method/procedure, and immediately
95 before every exit point of the procedure (return statements, thrown
96 exceptions, normal "fall of the end"), PROCEDURE-level logs should
97 be written. PROCEDURE-level messages should also record all of the
98 input and output of a procedure.
99
100 At the beginning of a procedure/function, proc_begin() should be
101 called for each IN or IN OUT parameter, or just for the module if it
102 has no such parameters.
103
104 At the end of a procedure/function, proc_end()/proc_return() should be
105 called, again for each IN OUT or OUT parameter, return value, or just
106 for the module if it has none of these. Be sure to call these routines
107 before each RETURN or normal RAISE statement in the procedure/function.
108
109 3-EVENT - PO_LOG.event()
110
111 At the beginning and end of a "flow", EVENT-level messages should be
112 logged. EVENT-level messages should be used at the beginning/end of
113 APIs, user-initiated actions (Form submits), concurrent requests,
114 workflows, and so on. EVENT-level logging should occur both before
115 and after calling another product's API. EVENT-level messages should
116 also be used to mark milestones within a larger flow, e.g. "document
117 locked", "validations completed", "document updated". EVENT-level
118 messages should surround all COMMITs, SAVEPOINTS, and ROLLBACKs.
119
120 4-EXCEPTION - PO_LOG.exc()
121
122 An EXCEPTION-level message should be logged any time an "exception"
123 occurs in the code. In PL/SQL, such a message should be logged at
124 the beginning of every (EXCEPTION) WHEN xxx THEN block unless the
125 exception is expected as part of the normal code flow, such as
126 NO_DATA_FOUND queries that are expected. If the PL/SQL exception is
127 expected, it should have a STATEMENT-level log message at the
128 beginning of the block instead of an EXCEPTION-level message.
129
130 EXCEPTION-level messages should also be recorded if the code
131 encounters a condition that was not expected from a programming
132 perspective. This may include the default section of a switch
133 statement or the ELSE clause of a series of IF-ELSE IF
134 conditions. Furthermore, an EXCEPTION-level message should be logged
135 by any code that silently recovers from an unexpected
136 condition. Examples of this include fixing API parameters, like
137 setting the parameter to 'N' if it was NULL, or turning null into
138 the empty String "" to make code more robust.
139
140 EXCEPTION-level messages can be monitored and used to improve an
141 application's performance and robustness.
142
143 ******************************************************************************
144 ******************************************************************************
145 */
146
147 /******************************************************************************
148
149 Debug logging notes
150 (from Oracle Applications Logging Framework Guide, 8/21/03):
151
152 ...
153 In some rare circumstances, for example, if you are debugging an
154 issue, you may need to manually initialize the PL/SQL layer logging
155 for the current session. From the SQL*Prompt, you could do this by
156 calling:
157
158 FND_GLOBAL.APPS_INITIALIZE(fnd_user_id, fnd_resp_id, fnd_appl_id);
159 fnd_profile.put('AFLOG_ENABLED', 'Y');
160 fnd_profile.put('AFLOG_MODULE', '%');
161 fnd_profile.put('AFLOG_LEVEL','1');
162 fnd_profile.put('AFLOG_FILENAME', '');
163 fnd_log_repository.init;
164
165 Do not ship any code with these calls! Shipping code that internally
166 hard codes Log Properties is a severe P1 bug.
167 ...
168
169 -- Note:
170 -- The above calls do not modify the stored values in the database.
171 -- Instead, they update the cached runtime values.
172
173 ...
174 Using Database Profile Options to Configure Logging
175
176 To enable logging using database profile options, set the following
177 profile options at the desired profile option level:
178
179 Profile Option Name User Specified Name Sample Value
180 AFLOG_ENABLED FND: Debug Log Enabled "Y"
181 AFLOG_MODULE FND: Debug Log Module "%"
182 AFLOG_LEVEL FND: Debug Log Level "ERROR"
183 AFLOG_FILENAME FND: Debug Log Filename "/path/to/apps.log"
184
185
186 ******************************************************************************
187
188 Debugging example:
189
190 This method was used to debug PO_DOCUMENT_FUNDS_PVT.do_unreserve.
191
192 The following code was executed:
193
194
195 DECLARE
196 l_return_status VARCHAR2(2000);
197 l_po_return_code VARCHAR2(2000);
198 l_online_report_id NUMBER;
199
200 d_mod VARCHAR2(100);
201
202 BEGIN
203
204 FND_GLOBAL.APPS_INITIALIZE(1318, 50063, 201); -- operations, vision services, purchasing
205
206 PO_LOG.enable_logging('po.plsql.SBULL%,po.plsql.%ENCUMBRANCE%,po.plsql.PO_DOCUMENT_FUNDS%');
207
208 d_mod := PO_LOG.get_subprogram_base(PO_LOG.get_package_base('SBULL'),'TEST');
209
210 PO_LOG.proc_begin(d_mod);
211
212 PO_DOCUMENT_FUNDS_PVT.do_unreserve(
213 x_return_status => l_return_status
214 , p_doc_type => PO_DOCUMENT_FUNDS_PVT.g_doc_type_PO
215 , p_doc_subtype => PO_DOCUMENT_FUNDS_PVT.g_doc_subtype_STANDARD
216 , p_doc_level => PO_DOCUMENT_FUNDS_PVT.g_doc_level_HEADER
217 , p_doc_level_id => 20604
218 , p_use_enc_gt_flag => PO_DOCUMENT_FUNDS_PVT.g_parameter_NO
219 , p_validate_document => PO_DOCUMENT_FUNDS_PVT.g_parameter_YES
220 , p_override_funds => PO_DOCUMENT_FUNDS_PVT.g_parameter_USE_PROFILE
221 , p_use_gl_date => PO_DOCUMENT_FUNDS_PVT.g_parameter_USE_PROFILE
222 , p_override_date => SYSDATE
223 , p_employee_id => NULL
224 , x_po_return_code => l_po_return_code
225 , x_online_report_id => l_online_report_id
226 );
227
228 PO_LOG.proc_end(d_mod);
229
230 END;
231
232
233 After the code was executed, the messages were inspected like so:
234
235 select module,message_text,timestamp, log_sequence
236 from fnd_log_messages
237 where log_sequence
238 between
239 ( select max(log_sequence)
240 from fnd_log_messages
241 where module = 'po.plsql.SBULL.TEST.BEGIN'
242 )
243 and
244 ( select max(log_sequence)
245 from fnd_log_messages
246 where module = 'po.plsql.SBULL.TEST.END'
247 )
248 order by log_sequence
249 ;
250
251 ******************************************************************************/
252
253
254
255 ---------------------------------------------------------------------------
256 -- CONSTANTS
257 ---------------------------------------------------------------------------
258
259 -- Used to separate tokens in the module string.
263 -- module names in log messages from PO PL/SQL procedures.
260 D_MODULE_SEPARATOR CONSTANT VARCHAR2(1) := '.';
261
262 -- Contains the string that must prefix all
264 D_MODULE_PREFIX CONSTANT VARCHAR2(9) := 'po.plsql.';
265
266 -- Used in the message text of an array's count.
267 D_COUNT CONSTANT VARCHAR2(6) := '.COUNT';
268
269 -- Used in the message text of array elements.
270 L_PAREN CONSTANT VARCHAR2(1) := '(';
271
272 -- Used in the message text of array elements.
273 R_PAREN CONSTANT VARCHAR2(1) := ')';
274
275 -- Used in the module of procedure messages.
276 D_BEGIN CONSTANT VARCHAR2(5) := 'BEGIN';
277
278 -- Used in the module of procedure messages.
279 D_END CONSTANT VARCHAR2(3) := 'END';
280
281 -- Used in the module of procedure messages.
282 D_RETURN CONSTANT VARCHAR2(6) := 'RETURN';
283
284 -- Used in the module of procedure messages.
285 D_RAISE CONSTANT VARCHAR2(5) := 'RAISE';
286
287 -- Used in the text of procedure messages.
288 D_START_OF_SUBPROGRAM CONSTANT VARCHAR2(20) := 'Start of subprogram.';
289
290 -- Used in the text of procedure messages.
291 D_END_OF_SUBPROGRAM CONSTANT VARCHAR2(18) := 'End of subprogram.';
292
293 -- Used in the text of procedure messages.
294 D_RETURN_VALUE CONSTANT VARCHAR2(12) := 'Return value';
295
296
297 ---------------------------------------------------------------------------
298 -- Modules for debugging.
299 ---------------------------------------------------------------------------
300
301 -- The module base for this package.
302 D_PACKAGE_BASE CONSTANT VARCHAR2(50) := get_package_base('PO_LOG');
303
304 -- The module base for the subprogram.
305 D_MOD_refresh_log_flags CONSTANT VARCHAR2(100) :=
306 get_subprogram_base(D_PACKAGE_BASE,'refresh_log_flags');
307
308
309 ---------------------------------------------------------------------------
310 -- PROCEDURES AND FUNCTIONS
311 ---------------------------------------------------------------------------
312
313
314 -------------------------------------------------------------------------------
315 --Start of Comments
316 --Pre-reqs: None.
317 --Modifies: The AFLOG_xxx profile options and variables in this package.
318 --Locks: None.
319 --Function:
320 --
321 -- *** DO NOT USE IN PRODUCTION CODE! ***
322 --
323 -- This procedure updates the FND Logging profile options and the cached
324 -- values in this package to enable logging for the current responsibility
325 -- for the session.
326 --
327 -- This should only be used while trying to debug a problem, as a
328 -- substitute for setting the FND profile options.
329 --
330 --Parameters:
331 --IN:
332 --p_module
333 -- A comma-separated list of module criteria
334 -- for which logging should be enabled.
335 -- Example: 'po.plsql.PO_DOCUMENT_FUNDS%,po.plsql.PO_ENCUMBRANCE%'
336 --p_level
337 -- The lowest level for which logging should be enabled.
338 -- Use the following values:
339 -- 1 - STATEMENT
340 -- 2 - PROCEDURE
341 -- 3 - EVENT
342 -- 4 - EXCEPTION
343 -- 5 - ERROR
344 -- 6 - UNEXPECTED
345 --End of Comments
346 -------------------------------------------------------------------------------
347 PROCEDURE enable_logging(
348 p_module IN VARCHAR2 DEFAULT 'p'||'o.%' -- GSCC File.Sql.6
349 , p_level IN NUMBER DEFAULT 1
350 )
351 IS
352 BEGIN
353
354 -- Set the FND profile option values.
355 FND_PROFILE.put('AFLOG_ENABLED','Y');
356 FND_PROFILE.put('AFLOG_MODULE',p_module);
360 -- Refresh the FND cache.
357 FND_PROFILE.put('AFLOG_LEVEL',TO_CHAR(p_level));
358 FND_PROFILE.put('AFLOG_FILENAME','');
359
361 FND_LOG_REPOSITORY.init();
362
363 -- Refresh the PO cache.
364 refresh_log_flags();
365
366 END enable_logging;
367
368
369 -------------------------------------------------------------------------------
370 --Start of Comments
371 --Pre-reqs: None.
372 --Modifies: The d_xxx public package variables.
373 --Locks: None.
374 --Function:
375 -- Updates the d_xxx variables with indicators of whether or not
376 -- logging is enabled at the particular level.
377 --
378 -- This procedure should be called whenever the Apps context changes,
379 -- as the determination of whether or not logging is enabled depends
380 -- on profile values that can change with different responsibilities.
381 -- This can be accomplished by adding a hook into
382 -- FND_GLOBAL.APPS_INITIALIZE via the FND_PRODUCT_INITIALIZATION table.
383 --
384 -- This procedure will also be called during package intialization.
385 --
386 --End of Comments
387 -------------------------------------------------------------------------------
388 PROCEDURE refresh_log_flags
389 IS
390 l_current_log_level NUMBER;
391 BEGIN
392
393 IF (FND_PROFILE.VALUE('AFLOG_ENABLED') = 'Y') THEN
394 l_current_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
395 d_stmt := (l_current_log_level <= FND_LOG.LEVEL_STATEMENT);
396 d_proc := (l_current_log_level <= FND_LOG.LEVEL_PROCEDURE);
397 d_event := (l_current_log_level <= FND_LOG.LEVEL_EVENT);
398 d_exc := (l_current_log_level <= FND_LOG.LEVEL_EXCEPTION);
399 d_error := (l_current_log_level <= FND_LOG.LEVEL_ERROR);
400 d_unexp := (l_current_log_level <= FND_LOG.LEVEL_UNEXPECTED);
401 ELSE
402 d_stmt := FALSE;
403 d_proc := FALSE;
404 d_event := FALSE;
405 d_exc := FALSE;
406 d_error := FALSE;
407 d_unexp := FALSE;
408 END IF;
409
410 IF d_proc THEN
411 proc_end(D_MOD_refresh_log_flags);
412 END IF;
413
414 EXCEPTION
415 WHEN OTHERS THEN
416 NULL;
417
418 END refresh_log_flags;
419
420
421 -------------------------------------------------------------------------------
422 --Start of Comments
423 --Pre-reqs: None.
424 --Modifies: None.
425 --Locks: None.
426 --Function:
427 -- Appends the package name to the PL/SQL base module and adds
428 -- module separators.
429 --Parameters:
430 --IN:
431 --p_package_name The name of the package, for example, "PO_LOG".
432 --Returns:
433 -- A string appropriate for passing into get_subprogram_base().
434 -- VARCHAR2(50).
435 --End of Comments
436 -------------------------------------------------------------------------------
437 FUNCTION get_package_base(
438 p_package_name IN VARCHAR2
439 )
440 RETURN VARCHAR2
441 IS
442 BEGIN
443 RETURN D_MODULE_PREFIX || UPPER(p_package_name) || D_MODULE_SEPARATOR;
444 END get_package_base;
445
446
447 -------------------------------------------------------------------------------
448 --Start of Comments
449 --Pre-reqs: None.
450 --Modifies: None.
451 --Locks: None.
452 --Function:
453 -- Appends the subprogram name to the package base and adds
454 -- module separators.
455 --Parameters:
456 --IN:
457 --p_package_base The module string up to the package name, as returned
458 -- by get_package_base().
459 --p_subprogram_name The name of the subprogram,
460 -- for example, "get_subprogram_base".
461 --Returns:
462 -- A string appropriate for use in any of the logging routines requiring
463 -- the module base.
464 -- VARCHAR2(100).
465 --End of Comments
466 -------------------------------------------------------------------------------
467 FUNCTION get_subprogram_base(
468 p_package_base IN VARCHAR2
469 , p_subprogram_name IN VARCHAR2
470 )
471 RETURN VARCHAR2
472 IS
473 BEGIN
474 RETURN p_package_base || UPPER(p_subprogram_name) || D_MODULE_SEPARATOR;
475 END get_subprogram_base;
476
477
478
479 -------------------------------------------------------------------------------
480 -- Generic logging routines.
481 -------------------------------------------------------------------------------
482
483 PROCEDURE log(
484 p_log_level IN NUMBER
485 , p_module_base IN VARCHAR2
486 , p_module_suffix IN VARCHAR2
487 , p_message_text IN VARCHAR2
488 )
489 IS
490 BEGIN
491 IF (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
492 FND_LOG.string(p_log_level,p_module_base||p_module_suffix,p_message_text);
493 END IF;
494 EXCEPTION
495 WHEN OTHERS THEN
496 NULL;
497 END log;
498
499
500 FUNCTION var_to_string(
501 p_variable_name IN VARCHAR2
505 IS
502 , p_variable_value IN VARCHAR2
503 )
504 RETURN VARCHAR2
506 BEGIN
507 IF (p_variable_value IS NULL) THEN
508 RETURN p_variable_name || ' is null';
509 ELSE
510 RETURN p_variable_name || ' = ' || p_variable_value;
511 END IF;
512 END var_to_string;
513
514
515 FUNCTION var_to_string(
516 p_variable_name IN VARCHAR2
517 , p_variable_value IN NUMBER
518 )
519 RETURN VARCHAR2
520 IS
521 BEGIN
522 RETURN var_to_string(p_variable_name,TO_CHAR(p_variable_value));
523 END var_to_string;
524
525
526 FUNCTION var_to_string(
527 p_variable_name IN VARCHAR2
528 , p_variable_value IN DATE
529 )
530 RETURN VARCHAR2
531 IS
532 BEGIN
533 RETURN var_to_string(p_variable_name,
534 TO_CHAR(p_variable_value,'DD-MON-RRRR HH:MI:SSAM'));
535 END var_to_string;
536
537
538 FUNCTION var_to_string(
539 p_variable_name IN VARCHAR2
540 , p_variable_value IN BOOLEAN
541 )
542 RETURN VARCHAR2
543 IS
544 l_varchar_value VARCHAR2(5);
545 BEGIN
546 IF (p_variable_value IS NULL) THEN
547 l_varchar_value := TO_CHAR(NULL);
548 ELSIF p_variable_value THEN
549 l_varchar_value := 'TRUE';
550 ELSE
551 l_varchar_value := 'FALSE';
552 END IF;
553 RETURN var_to_string(p_variable_name,l_varchar_value);
554 END var_to_string;
555
556
557 PROCEDURE decode_level_suffix(
558 x_log_level IN OUT NOCOPY NUMBER
559 , x_module_suffix IN OUT NOCOPY VARCHAR2
560 )
561 IS
562 BEGIN
563 IF (x_log_level IN (c_PROC_BEGIN,c_PROC_END,c_PROC_RETURN,c_PROC_RAISE)) THEN
564 x_module_suffix :=
565 CASE x_log_level
566 WHEN c_PROC_BEGIN THEN D_BEGIN||D_MODULE_SEPARATOR||x_module_suffix
567 WHEN c_PROC_END THEN D_END||D_MODULE_SEPARATOR||x_module_suffix
568 WHEN c_PROC_RETURN THEN D_RETURN||D_MODULE_SEPARATOR||x_module_suffix
569 WHEN c_PROC_RAISE THEN D_RAISE||D_MODULE_SEPARATOR||x_module_suffix
570 ELSE x_module_suffix
571 END;
572 x_log_level := FND_LOG.LEVEL_PROCEDURE;
573 END IF;
574 END decode_level_suffix;
575
576 ----------------------------------------------------------------------
577 -- This routine can be used for any logging level,
578 -- so that different procedures do not need to be
579 -- created for each kind of logging
580 -- (stmt/proc_begin/proc_end/proc_return/...).
581 -- @param p_log_level Use one of the package constants:
582 -- c_STMT, c_PROC_BEGIN, c_PROC_END, c_PROC_RETURN, etc.
583 ----------------------------------------------------------------------
584 PROCEDURE log(
585 p_log_level IN NUMBER
586 , p_module_base IN VARCHAR2
587 , p_module_suffix IN VARCHAR2
588 , p_variable_name IN VARCHAR2
589 , p_variable_value IN PO_VALIDATION_RESULTS_TYPE
590 )
591 IS
592 l_log_level NUMBER;
593 l_module_suffix VARCHAR2(4000);
594 BEGIN
595 l_log_level := p_log_level;
596 l_module_suffix := p_module_suffix;
597 decode_level_suffix(l_log_level,l_module_suffix);
598 IF (p_variable_value IS NULL) THEN
599 log(l_log_level,p_module_base,l_module_suffix,
600 var_to_string(p_variable_name,TO_CHAR(NULL)));
601 ELSE
602 log(l_log_level,p_module_base,l_module_suffix,
603 var_to_string(p_variable_name||D_COUNT,p_variable_value.result_type.COUNT));
604 END IF;
605 EXCEPTION
606 WHEN OTHERS THEN
607 NULL;
608 END log;
609
610
611 PROCEDURE log(
612 p_log_level IN NUMBER
613 , p_module_base IN VARCHAR2
614 , p_module_suffix IN VARCHAR2
615 , p_variable_name IN VARCHAR2
616 , p_variable_value IN PO_TBL_NUMBER
617 )
618 IS
619 BEGIN
620 IF (p_variable_value IS NULL) THEN
621 log(p_log_level,p_module_base,p_module_suffix,
622 var_to_string(p_variable_name,TO_CHAR(NULL)));
623 ELSE
624 log(p_log_level,p_module_base,p_module_suffix,
625 var_to_string(p_variable_name||D_COUNT,p_variable_value.COUNT));
626 FOR i IN 1 .. p_variable_value.COUNT LOOP
627 log(p_log_level,p_module_base,p_module_suffix,
628 var_to_string(p_variable_name||L_PAREN||TO_CHAR(i)||R_PAREN,
629 p_variable_value(i)));
630 END LOOP;
631 END IF;
632 EXCEPTION
633 WHEN OTHERS THEN
634 NULL;
635 END log;
636
637
638 PROCEDURE log(
639 p_log_level IN NUMBER
640 , p_module_base IN VARCHAR2
641 , p_module_suffix IN VARCHAR2
642 , p_variable_name IN VARCHAR2
643 , p_variable_value IN PO_TBL_DATE
644 )
645 IS
646 BEGIN
647 IF (p_variable_value IS NULL) THEN
648 log(p_log_level,p_module_base,p_module_suffix,
649 var_to_string(p_variable_name,TO_CHAR(NULL)));
650 ELSE
651 log(p_log_level,p_module_base,p_module_suffix,
652 var_to_string(p_variable_name||D_COUNT,p_variable_value.COUNT));
653 FOR i IN 1 .. p_variable_value.COUNT LOOP
654 log(p_log_level,p_module_base,p_module_suffix,
655 var_to_string(p_variable_name||L_PAREN||TO_CHAR(i)||R_PAREN,
656 p_variable_value(i)));
657 END LOOP;
658 END IF;
659 EXCEPTION
660 WHEN OTHERS THEN
661 NULL;
662 END log;
663
664
665 PROCEDURE log(
666 p_log_level IN NUMBER
667 , p_module_base IN VARCHAR2
668 , p_module_suffix IN VARCHAR2
669 , p_variable_name IN VARCHAR2
670 , p_variable_value IN PO_TBL_VARCHAR1
671 )
672 IS
673 BEGIN
674 IF (p_variable_value IS NULL) THEN
675 log(p_log_level,p_module_base,p_module_suffix,
676 var_to_string(p_variable_name,TO_CHAR(NULL)));
677 ELSE
678 log(p_log_level,p_module_base,p_module_suffix,
682 var_to_string(p_variable_name||L_PAREN||TO_CHAR(i)||R_PAREN,
679 var_to_string(p_variable_name||D_COUNT,p_variable_value.COUNT));
680 FOR i IN 1 .. p_variable_value.COUNT LOOP
681 log(p_log_level,p_module_base,p_module_suffix,
683 p_variable_value(i)));
684 END LOOP;
685 END IF;
686 EXCEPTION
687 WHEN OTHERS THEN
688 NULL;
689 END log;
690
691
692 PROCEDURE log(
693 p_log_level IN NUMBER
694 , p_module_base IN VARCHAR2
695 , p_module_suffix IN VARCHAR2
696 , p_variable_name IN VARCHAR2
697 , p_variable_value IN PO_TBL_VARCHAR5
698 )
699 IS
700 BEGIN
701 IF (p_variable_value IS NULL) THEN
702 log(p_log_level,p_module_base,p_module_suffix,
703 var_to_string(p_variable_name,TO_CHAR(NULL)));
704 ELSE
705 log(p_log_level,p_module_base,p_module_suffix,
706 var_to_string(p_variable_name||D_COUNT,p_variable_value.COUNT));
707 FOR i IN 1 .. p_variable_value.COUNT LOOP
708 log(p_log_level,p_module_base,p_module_suffix,
709 var_to_string(p_variable_name||L_PAREN||TO_CHAR(i)||R_PAREN,
710 p_variable_value(i)));
711 END LOOP;
712 END IF;
713 EXCEPTION
714 WHEN OTHERS THEN
715 NULL;
716 END log;
717
718
719 PROCEDURE log(
720 p_log_level IN NUMBER
721 , p_module_base IN VARCHAR2
722 , p_module_suffix IN VARCHAR2
723 , p_variable_name IN VARCHAR2
724 , p_variable_value IN PO_TBL_VARCHAR30
725 )
726 IS
727 BEGIN
728 IF (p_variable_value IS NULL) THEN
729 log(p_log_level,p_module_base,p_module_suffix,
730 var_to_string(p_variable_name,TO_CHAR(NULL)));
731 ELSE
732 log(p_log_level,p_module_base,p_module_suffix,
733 var_to_string(p_variable_name||D_COUNT,p_variable_value.COUNT));
734 FOR i IN 1 .. p_variable_value.COUNT LOOP
735 log(p_log_level,p_module_base,p_module_suffix,
736 var_to_string(p_variable_name||L_PAREN||TO_CHAR(i)||R_PAREN,
737 p_variable_value(i)));
738 END LOOP;
739 END IF;
740 EXCEPTION
741 WHEN OTHERS THEN
742 NULL;
743 END log;
744
745
746 PROCEDURE log(
747 p_log_level IN NUMBER
748 , p_module_base IN VARCHAR2
749 , p_module_suffix IN VARCHAR2
750 , p_variable_name IN VARCHAR2
751 , p_variable_value IN PO_TBL_VARCHAR100
752 )
753 IS
754 BEGIN
755 IF (p_variable_value IS NULL) THEN
756 log(p_log_level,p_module_base,p_module_suffix,
757 var_to_string(p_variable_name,TO_CHAR(NULL)));
758 ELSE
759 log(p_log_level,p_module_base,p_module_suffix,
760 var_to_string(p_variable_name||D_COUNT,p_variable_value.COUNT));
761 FOR i IN 1 .. p_variable_value.COUNT LOOP
762 log(p_log_level,p_module_base,p_module_suffix,
763 var_to_string(p_variable_name||L_PAREN||TO_CHAR(i)||R_PAREN,
764 p_variable_value(i)));
765 END LOOP;
766 END IF;
767 EXCEPTION
768 WHEN OTHERS THEN
769 NULL;
770 END log;
771
772
773 PROCEDURE log(
774 p_log_level IN NUMBER
775 , p_module_base IN VARCHAR2
776 , p_module_suffix IN VARCHAR2
777 , p_variable_name IN VARCHAR2
778 , p_variable_value IN PO_TBL_VARCHAR2000
779 )
780 IS
781 BEGIN
782 IF (p_variable_value IS NULL) THEN
783 log(p_log_level,p_module_base,p_module_suffix,
784 var_to_string(p_variable_name,TO_CHAR(NULL)));
785 ELSE
786 log(p_log_level,p_module_base,p_module_suffix,
787 var_to_string(p_variable_name||D_COUNT,p_variable_value.COUNT));
788 FOR i IN 1 .. p_variable_value.COUNT LOOP
789 log(p_log_level,p_module_base,p_module_suffix,
790 var_to_string(p_variable_name||L_PAREN||TO_CHAR(i)||R_PAREN,
791 p_variable_value(i)));
792 END LOOP;
793 END IF;
794 EXCEPTION
795 WHEN OTHERS THEN
796 NULL;
797 END log;
798
799
800 PROCEDURE log(
801 p_log_level IN NUMBER
802 , p_module_base IN VARCHAR2
803 , p_module_suffix IN VARCHAR2
804 , p_variable_name IN VARCHAR2
805 , p_variable_value IN PO_TBL_VARCHAR4000
806 )
807 IS
808 BEGIN
809 IF (p_variable_value IS NULL) THEN
810 log(p_log_level,p_module_base,p_module_suffix,
811 var_to_string(p_variable_name,TO_CHAR(NULL)));
812 ELSE
813 log(p_log_level,p_module_base,p_module_suffix,
814 var_to_string(p_variable_name||D_COUNT,p_variable_value.COUNT));
815 FOR i IN 1 .. p_variable_value.COUNT LOOP
816 log(p_log_level,p_module_base,p_module_suffix,
817 var_to_string(p_variable_name||L_PAREN||TO_CHAR(i)||R_PAREN,
818 p_variable_value(i)));
819 END LOOP;
820 END IF;
821 EXCEPTION
822 WHEN OTHERS THEN
823 NULL;
824 END log;
825
826
827
828 -------------------------------------------------------------------------------
829 -- STATEMENT-level logging.
830 -------------------------------------------------------------------------------
831
832 PROCEDURE stmt(
833 p_module_base IN VARCHAR2
834 , p_position IN NUMBER
835 , p_message_text IN VARCHAR2 DEFAULT NULL
836 )
837 IS
838 BEGIN
839 log(FND_LOG.LEVEL_STATEMENT,p_module_base,TO_CHAR(p_position),p_message_text);
840 END stmt;
841
842 PROCEDURE stmt(
843 p_module_base IN VARCHAR2
844 , p_position IN NUMBER
845 , p_variable_name IN VARCHAR2
846 , p_variable_value IN VARCHAR2
847 )
848 IS
849 BEGIN
850 log(FND_LOG.LEVEL_STATEMENT,p_module_base,TO_CHAR(p_position),
851 var_to_string(p_variable_name,p_variable_value));
852 END stmt;
856 , p_position IN NUMBER
853
854 PROCEDURE stmt(
855 p_module_base IN VARCHAR2
857 , p_variable_name IN VARCHAR2
858 , p_variable_value IN NUMBER
859 )
860 IS
861 BEGIN
862 log(FND_LOG.LEVEL_STATEMENT,p_module_base,TO_CHAR(p_position),
863 var_to_string(p_variable_name,p_variable_value));
864 END stmt;
865
866 PROCEDURE stmt(
867 p_module_base IN VARCHAR2
868 , p_position IN NUMBER
869 , p_variable_name IN VARCHAR2
870 , p_variable_value IN DATE
871 )
872 IS
873 BEGIN
874 log(FND_LOG.LEVEL_STATEMENT,p_module_base,TO_CHAR(p_position),
875 var_to_string(p_variable_name,p_variable_value));
876 END stmt;
877
878 PROCEDURE stmt(
879 p_module_base IN VARCHAR2
880 , p_position IN NUMBER
881 , p_variable_name IN VARCHAR2
882 , p_variable_value IN BOOLEAN
883 )
884 IS
885 BEGIN
886 log(FND_LOG.LEVEL_STATEMENT,p_module_base,TO_CHAR(p_position),
887 var_to_string(p_variable_name,p_variable_value));
888 END stmt;
889
890 PROCEDURE stmt(
891 p_module_base IN VARCHAR2
892 , p_position IN NUMBER
893 , p_variable_name IN VARCHAR2
894 , p_variable_value IN PO_TBL_NUMBER
895 )
896 IS
897 BEGIN
898 log(FND_LOG.LEVEL_STATEMENT,p_module_base,TO_CHAR(p_position),
899 p_variable_name,p_variable_value);
900 END stmt;
901
902 PROCEDURE stmt(
903 p_module_base IN VARCHAR2
904 , p_position IN NUMBER
905 , p_variable_name IN VARCHAR2
906 , p_variable_value IN PO_TBL_DATE
907 )
908 IS
909 BEGIN
910 log(FND_LOG.LEVEL_STATEMENT,p_module_base,TO_CHAR(p_position),
911 p_variable_name,p_variable_value);
912 END stmt;
913
914 PROCEDURE stmt(
915 p_module_base IN VARCHAR2
916 , p_position IN NUMBER
917 , p_variable_name IN VARCHAR2
918 , p_variable_value IN PO_TBL_VARCHAR1
919 )
920 IS
921 BEGIN
922 log(FND_LOG.LEVEL_STATEMENT,p_module_base,TO_CHAR(p_position),
923 p_variable_name,p_variable_value);
924 END stmt;
925
926 PROCEDURE stmt(
927 p_module_base IN VARCHAR2
928 , p_position IN NUMBER
929 , p_variable_name IN VARCHAR2
930 , p_variable_value IN PO_TBL_VARCHAR5
931 )
932 IS
933 BEGIN
934 log(FND_LOG.LEVEL_STATEMENT,p_module_base,TO_CHAR(p_position),
935 p_variable_name,p_variable_value);
936 END stmt;
937
938 PROCEDURE stmt(
939 p_module_base IN VARCHAR2
940 , p_position IN NUMBER
941 , p_variable_name IN VARCHAR2
942 , p_variable_value IN PO_TBL_VARCHAR30
943 )
944 IS
945 BEGIN
946 log(FND_LOG.LEVEL_STATEMENT,p_module_base,TO_CHAR(p_position),
947 p_variable_name,p_variable_value);
948 END stmt;
949
950 PROCEDURE stmt(
951 p_module_base IN VARCHAR2
952 , p_position IN NUMBER
953 , p_variable_name IN VARCHAR2
954 , p_variable_value IN PO_TBL_VARCHAR100
955 )
956 IS
957 BEGIN
958 log(FND_LOG.LEVEL_STATEMENT,p_module_base,TO_CHAR(p_position),
959 p_variable_name,p_variable_value);
960 END stmt;
961
962 PROCEDURE stmt(
963 p_module_base IN VARCHAR2
964 , p_position IN NUMBER
965 , p_variable_name IN VARCHAR2
966 , p_variable_value IN PO_TBL_VARCHAR2000
967 )
968 IS
969 BEGIN
970 log(FND_LOG.LEVEL_STATEMENT,p_module_base,TO_CHAR(p_position),
971 p_variable_name,p_variable_value);
972 END stmt;
973
974 PROCEDURE stmt(
975 p_module_base IN VARCHAR2
976 , p_position IN NUMBER
977 , p_variable_name IN VARCHAR2
978 , p_variable_value IN PO_TBL_VARCHAR4000
979 )
980 IS
981 BEGIN
982 log(FND_LOG.LEVEL_STATEMENT,p_module_base,TO_CHAR(p_position),
983 p_variable_name,p_variable_value);
984 END stmt;
985
986 -------------------------------------------------------------------------------
987 --Start of Comments
988 --Pre-reqs: None.
989 --Modifies: FND_LOG_MESSAGES
990 --Locks: None.
991 --Function:
992 -- Logs STATEMENT-level messages containing data from PO_SESSION_GT.
993 --Parameters:
994 --IN:
995 --p_module_base
996 -- The log module base as obtained from get_subprogram_base().
997 --p_position
998 -- A location indicator for the calling subprogram.
999 --p_key
1000 -- Indicates which rows from PO_SESSION_GT to report.
1001 -- All rows WHERE PO_SESSION_GT.key = p_key will be reported.
1002 --p_column_name_tbl
1003 -- The column names whose values for the specified rows should be reported.
1004 -- If all column values should be reported, use NULL.
1005 --End of Comments
1006 -------------------------------------------------------------------------------
1007 PROCEDURE stmt_session_gt(
1008 p_module_base IN VARCHAR2
1009 , p_position IN NUMBER
1010 , p_key IN NUMBER
1011 , p_column_name_tbl IN PO_TBL_VARCHAR30 DEFAULT NULL
1012 )
1013 IS
1014 l_rowid_tbl PO_TBL_VARCHAR2000;
1015 BEGIN
1016
1017 IF d_stmt THEN
1018
1019 SELECT rowid
1023 ;
1020 BULK COLLECT INTO l_rowid_tbl
1021 FROM PO_SESSION_GT
1022 WHERE key = p_key
1024
1025 stmt_table(
1026 p_module_base => p_module_base
1027 , p_position => p_position
1028 , p_table_name => 'PO_SESSION_GT'
1029 , p_rowid_tbl => l_rowid_tbl
1030 , p_column_name_tbl => p_column_name_tbl
1031 );
1032
1033 END IF;
1034
1035 EXCEPTION
1036 WHEN OTHERS THEN
1037 NULL;
1038 END stmt_session_gt;
1039
1040 -------------------------------------------------------------------------------
1041 --Start of Comments
1042 --Pre-reqs: None.
1043 --Modifies: FND_LOG_MESSAGES
1044 --Locks: None.
1045 --Function:
1046 -- Logs STATEMENT-level messages containing data from the specified table.
1047 --Parameters:
1048 --IN:
1049 --p_module_base
1050 -- The log module base as obtained from get_subprogram_base().
1051 --p_position
1052 -- A location indicator for the calling subprogram.
1053 --p_table_name
1054 -- The name of the table about which to report.
1055 --p_rowid_tbl
1056 -- The rowids of the table about which to report.
1057 -- To report all rows in the table, use c_all_rows.
1058 --p_column_name_tbl
1059 -- The column names whose values for the specified rows should be reported.
1060 -- If all column values should be reported, use NULL.
1061 --End of Comments
1062 -------------------------------------------------------------------------------
1063 PROCEDURE stmt_table(
1064 p_module_base IN VARCHAR2
1065 , p_position IN NUMBER
1066 , p_table_name IN VARCHAR2
1067 , p_rowid_tbl IN PO_TBL_VARCHAR2000
1068 , p_column_name_tbl IN PO_TBL_VARCHAR30 DEFAULT NULL
1069 )
1070 IS
1071
1072 TYPE t_tbl_data_type IS TABLE OF ALL_TAB_COLUMNS.column_name%TYPE;
1073 TYPE t_tbl_varchar IS TABLE OF VARCHAR2(4000);
1074 TYPE t_ref_csr IS REF CURSOR;
1075
1076 l_column_name_tbl po_tbl_varchar30;
1077 l_data_type_tbl t_tbl_data_type;
1078
1079 l_printable_column_tbl po_tbl_varchar30;
1080
1081 l_value_rowid_tbl po_tbl_varchar2000;
1082 l_value_tbl t_tbl_varchar;
1083
1084 l_sql VARCHAR2(32767);
1085 l_char VARCHAR2(4000);
1086
1087 l_col_i PLS_INTEGER;
1088
1089 l_table_name VARCHAR2(30);
1090 l_table_owner VARCHAR2(30);
1091 l_print_column_flag BOOLEAN;
1092
1093 l_log_head VARCHAR2(2000);
1094
1095 l_rowid_tbl po_tbl_varchar2000;
1096 l_rowid_csr t_ref_csr;
1097
1098 BEGIN
1099 -- TODO: Refactor this procedure.
1100 -- It was copied from the PO_DEBUG.debug_table implementation,
1101 -- and needs to be reworked a bit.
1102 --
1103 -- Ideas for refactoring:
1104 -- Get the current schema via SYS_CONTEXT('USERENV','CURRENT_SCHEMA').
1105 -- Use all_synonyms to find the base table / view.
1106 -- (maybe just use user_synonyms, if login is always APPS, regardless of current_schema)
1107 -- Use all_tab_columns to get column names.
1108 --
1109
1110 IF d_stmt THEN
1111
1112 l_printable_column_tbl := po_tbl_varchar30();
1113 l_value_rowid_tbl := po_tbl_varchar2000();
1114 l_value_tbl := t_tbl_varchar();
1115
1116 l_table_name := UPPER(p_table_name);
1117
1118 -- Use the refactoring ideas to get the real value for table_owner.
1119 l_table_owner := 'PO';
1120
1121 l_log_head := p_module_base||TO_CHAR(p_position)|| '.DEBUG_TABLE.' || p_table_name||'.';
1122
1123 stmt(l_log_head||'START',NULL,'Logging non-null columns only. p_rowid_tbl.COUNT',p_rowid_tbl.COUNT);
1124
1125 SELECT
1126 column_name
1127 , data_type
1128 BULK COLLECT INTO
1129 l_column_name_tbl
1130 , l_data_type_tbl
1131 FROM
1132 ALL_TAB_COLUMNS
1133 WHERE table_name = l_table_name
1134 AND owner = l_table_owner
1135 ORDER BY column_id
1136 ;
1137
1138 IF (p_rowid_tbl.COUNT = 1 AND p_rowid_tbl(1) = c_all_rows(1)) THEN
1139
1140 l_rowid_tbl := po_tbl_varchar2000();
1141
1142 OPEN l_rowid_csr FOR 'SELECT rowid FROM '||l_table_name ;
1143
1144 LOOP
1145
1146 FETCH l_rowid_csr INTO l_char;
1147 EXIT WHEN l_rowid_csr%NOTFOUND;
1148
1149 l_rowid_tbl.EXTEND;
1150 l_rowid_tbl(l_rowid_tbl.COUNT) := l_char;
1151
1152 END LOOP;
1153
1154 CLOSE l_rowid_csr;
1155
1156 ELSE
1157 l_rowid_tbl := p_rowid_tbl;
1158 END IF;
1159
1160 FOR i IN 1 .. l_column_name_tbl.COUNT LOOP
1161
1162 IF (l_data_type_tbl(i) = 'NUMBER') THEN
1163
1164 l_sql := 'TO_CHAR(' || l_column_name_tbl(i) || ')';
1165
1166 ELSIF (l_data_type_tbl(i) = 'DATE') THEN
1167
1168 l_sql := 'TO_CHAR(' || l_column_name_tbl(i) || ', ''DD-MON-RRRR HH:MI:SSAM'')';
1169
1170 ELSIF (l_data_type_tbl(i) = 'VARCHAR2') THEN
1171
1172 l_sql := l_column_name_tbl(i);
1173
1174 END IF;
1175
1176 IF (l_sql IS NULL AND p_column_name_tbl IS NULL) THEN
1177
1178 stmt(l_log_head||'COLUMN',NULL,
1179 'Unprintable column: '|| l_column_name_tbl(i) || ' (' || l_data_type_tbl(i) || ')');
1180
1181 ELSIF (l_sql IS NOT NULL) THEN
1182
1183 IF (p_column_name_tbl IS NULL) THEN
1184 l_print_column_flag := TRUE;
1185 ELSE
1186 l_print_column_flag := FALSE;
1187 FOR j IN 1 .. p_column_name_tbl.COUNT LOOP
1188 IF (l_column_name_tbl(i) = UPPER(p_column_name_tbl(j))) THEN
1189 l_print_column_flag := TRUE;
1190 EXIT;
1194
1191 END IF;
1192 END LOOP;
1193 END IF;
1195 IF l_print_column_flag THEN
1196
1197 l_printable_column_tbl.EXTEND;
1198 l_printable_column_tbl(l_printable_column_tbl.COUNT) := l_column_name_tbl(i);
1199
1200 l_sql := 'SELECT SUBSTR(' || l_sql || ',1,3900)'
1201 ||' FROM ' || l_table_name
1202 ||' WHERE rowid = :b_rowid';
1203
1204 FOR j IN 1 .. l_rowid_tbl.COUNT LOOP
1205
1206 BEGIN
1207
1208 EXECUTE IMMEDIATE l_sql INTO l_char USING l_rowid_tbl(j);
1209
1210 EXCEPTION
1211 WHEN OTHERS THEN
1212 l_char := SQLERRM;
1213 END;
1214
1215 l_value_rowid_tbl.EXTEND;
1216 l_value_rowid_tbl(l_value_rowid_tbl.COUNT) := l_rowid_tbl(j);
1217
1218 l_value_tbl.EXTEND;
1219 l_value_tbl(l_value_tbl.COUNT) := l_char;
1220
1221 END LOOP;
1222
1223 END IF;
1224
1225 END IF;
1226
1227 END LOOP;
1228
1229
1230 -- Report input columns that don't exist / aren't printable.
1231
1232 IF (p_column_name_tbl IS NOT NULL) THEN
1233
1234 FOR i IN 1 .. p_column_name_tbl.COUNT LOOP
1235
1236 l_print_column_flag := FALSE;
1237
1238 FOR j IN 1 .. l_column_name_tbl.COUNT LOOP
1239 IF (UPPER(p_column_name_tbl(i)) = l_column_name_tbl(j)) THEN
1240 l_print_column_flag := TRUE;
1241 EXIT;
1242 END IF;
1243 END LOOP;
1244
1245 IF (NOT l_print_column_flag) THEN
1246 stmt(l_log_head||'COLUMN',NULL,
1247 'Non-existent column: '|| p_column_name_tbl(i));
1248 ELSE
1249
1250 l_print_column_flag := FALSE;
1251
1252 FOR j IN 1 .. l_printable_column_tbl.COUNT LOOP
1253 IF (UPPER(p_column_name_tbl(i)) = l_printable_column_tbl(j)) THEN
1254 l_print_column_flag := TRUE;
1255 EXIT;
1256 END IF;
1257 END LOOP;
1258
1259 IF (NOT l_print_column_flag) THEN
1260 stmt(l_log_head||'COLUMN',NULL,
1261 'Unprintable column: '|| l_column_name_tbl(i) || ' (unprintable data type)');
1262 END IF;
1263
1264 END IF;
1265
1266 END LOOP;
1267
1268 END IF;
1269
1270
1271 FOR i IN 1 .. l_rowid_tbl.COUNT LOOP
1272
1273 l_col_i := 1;
1274
1275 FOR j IN 1 .. l_value_rowid_tbl.COUNT LOOP
1276
1277 IF (l_value_rowid_tbl(j) = l_rowid_tbl(i)) THEN
1278
1279 IF (l_value_tbl(j) IS NOT NULL) THEN
1280 stmt(l_log_head||'ROW',NULL,
1281 l_rowid_tbl(i)||'.'||l_printable_column_tbl(l_col_i),
1282 l_value_tbl(j));
1283 END IF;
1284
1285 l_col_i := l_col_i + 1;
1286
1287 END IF;
1288
1289 END LOOP;
1290
1291 END LOOP;
1292
1293 END IF;
1294
1295 EXCEPTION
1296 WHEN OTHERS THEN
1297 IF d_stmt THEN
1298
1299 l_log_head := p_module_base||TO_CHAR(p_position)||'.DEBUG_TABLE.'||p_table_name||'.'||'EXCEPTION';
1300
1301 stmt(l_log_head,NULL,SQLERRM);
1302
1303 stmt(l_log_head,NULL,'p_rowid_tbl',p_rowid_tbl);
1304 stmt(l_log_head,NULL,'p_column_name_tbl',p_column_name_tbl);
1305 stmt(l_log_head,NULL,'l_table_owner',l_table_owner);
1306 stmt(l_log_head,NULL,'l_rowid_tbl',l_rowid_tbl);
1307 stmt(l_log_head,NULL,'l_column_name_tbl',l_column_name_tbl);
1308 stmt(l_log_head,NULL,'l_printable_column_tbl',l_printable_column_tbl);
1309 stmt(l_log_head,NULL,'l_sql',l_sql);
1310 stmt(l_log_head,NULL,'l_char',l_char);
1311 stmt(l_log_head,NULL,'l_table_name',l_table_name);
1312 stmt(l_log_head,NULL,'l_table_owner',l_table_owner);
1313
1314 END IF;
1315
1316 END stmt_table;
1317
1318 -- PROCEDURE-level logging.
1319
1320 PROCEDURE proc_begin(
1321 p_module_base IN VARCHAR2
1322 )
1323 IS
1324 BEGIN
1325 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,D_START_OF_SUBPROGRAM);
1326 END proc_begin;
1327
1328 PROCEDURE proc_begin(
1329 p_module_base IN VARCHAR2
1330 , p_parameter_name IN VARCHAR2
1331 , p_parameter_value IN VARCHAR2
1332 )
1333 IS
1334 BEGIN
1335 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,
1336 var_to_string(p_parameter_name,p_parameter_value));
1337 END proc_begin;
1338
1339 PROCEDURE proc_begin(
1340 p_module_base IN VARCHAR2
1341 , p_parameter_name IN VARCHAR2
1342 , p_parameter_value IN NUMBER
1343 )
1344 IS
1345 BEGIN
1346 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,
1347 var_to_string(p_parameter_name,p_parameter_value));
1348 END proc_begin;
1349
1350 PROCEDURE proc_begin(
1351 p_module_base IN VARCHAR2
1352 , p_parameter_name IN VARCHAR2
1353 , p_parameter_value IN DATE
1354 )
1355 IS
1356 BEGIN
1357 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,
1358 var_to_string(p_parameter_name,p_parameter_value));
1359 END proc_begin;
1360
1361 PROCEDURE proc_begin(
1362 p_module_base IN VARCHAR2
1363 , p_parameter_name IN VARCHAR2
1364 , p_parameter_value IN BOOLEAN
1365 )
1366 IS
1367 BEGIN
1371
1368 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,
1369 var_to_string(p_parameter_name,p_parameter_value));
1370 END proc_begin;
1372 PROCEDURE proc_begin(
1373 p_module_base IN VARCHAR2
1374 , p_parameter_name IN VARCHAR2
1375 , p_parameter_value IN PO_TBL_NUMBER
1376 )
1377 IS
1378 BEGIN
1379 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,
1380 p_parameter_name,p_parameter_value);
1381 END proc_begin;
1382
1383 PROCEDURE proc_begin(
1384 p_module_base IN VARCHAR2
1385 , p_parameter_name IN VARCHAR2
1386 , p_parameter_value IN PO_TBL_DATE
1387 )
1388 IS
1389 BEGIN
1390 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,
1391 p_parameter_name,p_parameter_value);
1392 END proc_begin;
1393
1394 PROCEDURE proc_begin(
1395 p_module_base IN VARCHAR2
1396 , p_parameter_name IN VARCHAR2
1397 , p_parameter_value IN PO_TBL_VARCHAR1
1398 )
1399 IS
1400 BEGIN
1401 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,
1402 p_parameter_name,p_parameter_value);
1403 END proc_begin;
1404
1405 PROCEDURE proc_begin(
1406 p_module_base IN VARCHAR2
1407 , p_parameter_name IN VARCHAR2
1408 , p_parameter_value IN PO_TBL_VARCHAR5
1409 )
1410 IS
1411 BEGIN
1412 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,
1413 p_parameter_name,p_parameter_value);
1414 END proc_begin;
1415
1416 PROCEDURE proc_begin(
1417 p_module_base IN VARCHAR2
1418 , p_parameter_name IN VARCHAR2
1419 , p_parameter_value IN PO_TBL_VARCHAR30
1420 )
1421 IS
1422 BEGIN
1423 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,
1424 p_parameter_name,p_parameter_value);
1425 END proc_begin;
1426
1427 PROCEDURE proc_begin(
1428 p_module_base IN VARCHAR2
1429 , p_parameter_name IN VARCHAR2
1430 , p_parameter_value IN PO_TBL_VARCHAR100
1431 )
1432 IS
1433 BEGIN
1434 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,
1435 p_parameter_name,p_parameter_value);
1436 END proc_begin;
1437
1438 PROCEDURE proc_begin(
1439 p_module_base IN VARCHAR2
1440 , p_parameter_name IN VARCHAR2
1441 , p_parameter_value IN PO_TBL_VARCHAR2000
1442 )
1443 IS
1444 BEGIN
1445 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,
1446 p_parameter_name,p_parameter_value);
1447 END proc_begin;
1448
1449 PROCEDURE proc_begin(
1450 p_module_base IN VARCHAR2
1451 , p_parameter_name IN VARCHAR2
1452 , p_parameter_value IN PO_TBL_VARCHAR4000
1453 )
1454 IS
1455 BEGIN
1456 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,
1457 p_parameter_name,p_parameter_value);
1458 END proc_begin;
1459
1460 PROCEDURE proc_end(
1461 p_module_base IN VARCHAR2
1462 )
1463 IS
1464 BEGIN
1465 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,D_END_OF_SUBPROGRAM);
1466 END proc_end;
1467
1468 PROCEDURE proc_end(
1469 p_module_base IN VARCHAR2
1470 , p_parameter_name IN VARCHAR2
1471 , p_parameter_value IN VARCHAR2
1472 )
1473 IS
1474 BEGIN
1475 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,
1476 var_to_string(p_parameter_name,p_parameter_value));
1477 END proc_end;
1478
1479 PROCEDURE proc_end(
1480 p_module_base IN VARCHAR2
1481 , p_parameter_name IN VARCHAR2
1482 , p_parameter_value IN NUMBER
1483 )
1484 IS
1485 BEGIN
1486 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,
1487 var_to_string(p_parameter_name,p_parameter_value));
1488 END proc_end;
1489
1490 PROCEDURE proc_end(
1491 p_module_base IN VARCHAR2
1492 , p_parameter_name IN VARCHAR2
1493 , p_parameter_value IN DATE
1494 )
1495 IS
1496 BEGIN
1497 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,
1498 var_to_string(p_parameter_name,p_parameter_value));
1499 END proc_end;
1500
1501 PROCEDURE proc_end(
1502 p_module_base IN VARCHAR2
1503 , p_parameter_name IN VARCHAR2
1504 , p_parameter_value IN BOOLEAN
1505 )
1506 IS
1507 BEGIN
1508 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,
1509 var_to_string(p_parameter_name,p_parameter_value));
1510 END proc_end;
1511
1512 PROCEDURE proc_end(
1513 p_module_base IN VARCHAR2
1514 , p_parameter_name IN VARCHAR2
1515 , p_parameter_value IN PO_TBL_NUMBER
1516 )
1517 IS
1518 BEGIN
1519 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,
1520 p_parameter_name,p_parameter_value);
1521 END proc_end;
1522
1523 PROCEDURE proc_end(
1524 p_module_base IN VARCHAR2
1525 , p_parameter_name IN VARCHAR2
1526 , p_parameter_value IN PO_TBL_DATE
1527 )
1528 IS
1529 BEGIN
1530 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,
1531 p_parameter_name,p_parameter_value);
1532 END proc_end;
1533
1534 PROCEDURE proc_end(
1535 p_module_base IN VARCHAR2
1536 , p_parameter_name IN VARCHAR2
1537 , p_parameter_value IN PO_TBL_VARCHAR1
1538 )
1539 IS
1540 BEGIN
1541 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,
1542 p_parameter_name,p_parameter_value);
1543 END proc_end;
1544
1545 PROCEDURE proc_end(
1549 )
1546 p_module_base IN VARCHAR2
1547 , p_parameter_name IN VARCHAR2
1548 , p_parameter_value IN PO_TBL_VARCHAR5
1550 IS
1551 BEGIN
1552 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,
1553 p_parameter_name,p_parameter_value);
1554 END proc_end;
1555
1556 PROCEDURE proc_end(
1557 p_module_base IN VARCHAR2
1558 , p_parameter_name IN VARCHAR2
1559 , p_parameter_value IN PO_TBL_VARCHAR30
1560 )
1561 IS
1562 BEGIN
1563 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,
1564 p_parameter_name,p_parameter_value);
1565 END proc_end;
1566
1567 PROCEDURE proc_end(
1568 p_module_base IN VARCHAR2
1569 , p_parameter_name IN VARCHAR2
1570 , p_parameter_value IN PO_TBL_VARCHAR100
1571 )
1572 IS
1573 BEGIN
1574 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,
1575 p_parameter_name,p_parameter_value);
1576 END proc_end;
1577
1578 PROCEDURE proc_end(
1579 p_module_base IN VARCHAR2
1580 , p_parameter_name IN VARCHAR2
1581 , p_parameter_value IN PO_TBL_VARCHAR2000
1582 )
1583 IS
1584 BEGIN
1585 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,
1586 p_parameter_name,p_parameter_value);
1587 END proc_end;
1588
1589 PROCEDURE proc_end(
1590 p_module_base IN VARCHAR2
1591 , p_parameter_name IN VARCHAR2
1592 , p_parameter_value IN PO_TBL_VARCHAR4000
1593 )
1594 IS
1595 BEGIN
1596 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,
1597 p_parameter_name,p_parameter_value);
1598 END proc_end;
1599
1600 PROCEDURE proc_return(
1601 p_module_base IN VARCHAR2
1602 , p_return_value IN VARCHAR2
1603 )
1604 IS
1605 BEGIN
1606 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1607 var_to_string(D_RETURN_VALUE,p_return_value));
1608 END proc_return;
1609
1610 PROCEDURE proc_return(
1611 p_module_base IN VARCHAR2
1612 , p_return_value IN NUMBER
1613 )
1614 IS
1615 BEGIN
1616 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1617 var_to_string(D_RETURN_VALUE,p_return_value));
1618 END proc_return;
1619
1620 PROCEDURE proc_return(
1621 p_module_base IN VARCHAR2
1622 , p_return_value IN DATE
1623 )
1624 IS
1625 BEGIN
1626 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1627 var_to_string(D_RETURN_VALUE,p_return_value));
1628 END proc_return;
1629
1630 PROCEDURE proc_return(
1631 p_module_base IN VARCHAR2
1632 , p_return_value IN BOOLEAN
1633 )
1634 IS
1635 BEGIN
1636 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1637 var_to_string(D_RETURN_VALUE,p_return_value));
1638 END proc_return;
1639
1640 PROCEDURE proc_return(
1641 p_module_base IN VARCHAR2
1642 , p_return_value IN PO_TBL_NUMBER
1643 )
1644 IS
1645 BEGIN
1646 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1647 D_RETURN_VALUE,p_return_value);
1648 END proc_return;
1649
1650 PROCEDURE proc_return(
1651 p_module_base IN VARCHAR2
1652 , p_return_value IN PO_TBL_DATE
1653 )
1654 IS
1655 BEGIN
1656 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1657 D_RETURN_VALUE,p_return_value);
1658 END proc_return;
1659
1660 PROCEDURE proc_return(
1661 p_module_base IN VARCHAR2
1662 , p_return_value IN PO_TBL_VARCHAR1
1663 )
1664 IS
1665 BEGIN
1666 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1667 D_RETURN_VALUE,p_return_value);
1668 END proc_return;
1669
1670 PROCEDURE proc_return(
1671 p_module_base IN VARCHAR2
1672 , p_return_value IN PO_TBL_VARCHAR5
1673 )
1674 IS
1675 BEGIN
1676 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1677 D_RETURN_VALUE,p_return_value);
1678 END proc_return;
1679
1680 PROCEDURE proc_return(
1681 p_module_base IN VARCHAR2
1682 , p_return_value IN PO_TBL_VARCHAR30
1683 )
1684 IS
1685 BEGIN
1686 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1687 D_RETURN_VALUE,p_return_value);
1688 END proc_return;
1689
1690 PROCEDURE proc_return(
1691 p_module_base IN VARCHAR2
1692 , p_return_value IN PO_TBL_VARCHAR100
1693 )
1694 IS
1695 BEGIN
1696 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1697 D_RETURN_VALUE,p_return_value);
1698 END proc_return;
1699
1700 PROCEDURE proc_return(
1701 p_module_base IN VARCHAR2
1702 , p_return_value IN PO_TBL_VARCHAR2000
1703 )
1704 IS
1705 BEGIN
1706 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1707 D_RETURN_VALUE,p_return_value);
1708 END proc_return;
1709
1710 PROCEDURE proc_return(
1711 p_module_base IN VARCHAR2
1712 , p_return_value IN PO_TBL_VARCHAR4000
1713 )
1714 IS
1715 BEGIN
1716 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1717 D_RETURN_VALUE,p_return_value);
1718 END proc_return;
1719
1720 PROCEDURE proc_raise(
1721 p_module_base IN VARCHAR2
1722 )
1723 IS
1724 BEGIN
1725 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RAISE,
1726 'Raising exception: SQLERRM = '||SQLERRM);
1727 END proc_raise;
1728
1729 -- EVENT-level logging.
1730
1731 PROCEDURE event(
1732 p_module_base IN VARCHAR2
1733 , p_position IN NUMBER
1734 , p_message_text IN VARCHAR2
1735 )
1736 IS
1737 BEGIN
1738 log(FND_LOG.LEVEL_EVENT,p_module_base,TO_CHAR(p_position),p_message_text);
1739 END event;
1740
1741 -- EXCEPTION-level logging.
1742
1743 PROCEDURE exc(
1744 p_module_base IN VARCHAR2
1745 , p_position IN NUMBER
1746 , p_message_text IN VARCHAR2 DEFAULT NULL
1747 )
1748 IS
1749 BEGIN
1750 log(FND_LOG.LEVEL_EXCEPTION,p_module_base,TO_CHAR(p_position)||'.EXCEPTION',
1751 p_message_text||';SQLERRM = '||SQLERRM);
1752 END exc;
1753
1754
1755 -----------------------------------------------------------------------------
1756 -- Package initialization.
1757 -----------------------------------------------------------------------------
1758
1759 BEGIN
1760
1761 refresh_log_flags();
1762
1763 END PO_LOG;