1 PACKAGE BODY PO_LOG AS
2 -- $Header: PO_LOG.plb 120.2.12020000.3 2013/02/11 00:35:42 vegajula ship $
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.
260 D_MODULE_SEPARATOR CONSTANT VARCHAR2(1) := '.';
261
262 -- Contains the string that must prefix all
263 -- module names in log messages from PO PL/SQL procedures.
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);
357 FND_PROFILE.put('AFLOG_LEVEL',TO_CHAR(p_level));
358 FND_PROFILE.put('AFLOG_FILENAME','');
359
360 -- Refresh the FND cache.
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".
464 -- VARCHAR2(100).
461 --Returns:
462 -- A string appropriate for use in any of the logging routines requiring
463 -- the module base.
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
502 , p_variable_value IN VARCHAR2
503 )
504 RETURN VARCHAR2
505 IS
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(
642 , p_variable_name IN VARCHAR2
639 p_log_level IN NUMBER
640 , p_module_base IN VARCHAR2
641 , p_module_suffix 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,
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,
682 var_to_string(p_variable_name||L_PAREN||TO_CHAR(i)||R_PAREN,
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,
817 var_to_string(p_variable_name||L_PAREN||TO_CHAR(i)||R_PAREN,
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,
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;
853
854 PROCEDURE stmt(
855 p_module_base IN VARCHAR2
856 , p_position IN NUMBER
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),
986 -------------------------------------------------------------------------------
983 p_variable_name,p_variable_value);
984 END stmt;
985
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
1020 BULK COLLECT INTO l_rowid_tbl
1021 FROM PO_SESSION_GT
1022 WHERE key = p_key
1023 ;
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 PROCEDURE stmt_all_session_gt(
1041 p_module_base IN VARCHAR2
1042 , p_position IN NUMBER
1043 , p_record_identifier IN VARCHAR2
1044 )
1045 IS
1046 update_po_log_tbl po_tbl_number;
1047 BEGIN
1048
1049 update_po_log_tbl:=po_tbl_number();
1050
1051 SELECT psgt.key
1052 BULK COLLECT INTO update_po_log_tbl
1053 FROM po_session_gt psgt
1054 WHERE psgt.index_char1=p_record_identifier ;
1055
1056
1057 FOR i in 1..update_po_log_tbl.COUNT LOOP
1058 stmt_session_gt( p_module_base => p_module_base,
1059 p_position => p_position ,
1060 p_key => update_po_log_tbl(i));
1061 END LOOP;
1062
1063 END stmt_all_session_gt;
1064
1065
1066 -------------------------------------------------------------------------------
1067 --Start of Comments
1068 --Pre-reqs: None.
1069 --Modifies: FND_LOG_MESSAGES
1070 --Locks: None.
1071 --Function:
1072 -- Logs STATEMENT-level messages containing data from the specified table.
1073 --Parameters:
1074 --IN:
1075 --p_module_base
1076 -- The log module base as obtained from get_subprogram_base().
1077 --p_position
1078 -- A location indicator for the calling subprogram.
1079 --p_table_name
1080 -- The name of the table about which to report.
1081 --p_rowid_tbl
1082 -- The rowids of the table about which to report.
1083 -- To report all rows in the table, use c_all_rows.
1084 --p_column_name_tbl
1085 -- The column names whose values for the specified rows should be reported.
1086 -- If all column values should be reported, use NULL.
1087 --End of Comments
1088 -------------------------------------------------------------------------------
1089 PROCEDURE stmt_table(
1090 p_module_base IN VARCHAR2
1091 , p_position IN NUMBER
1092 , p_table_name IN VARCHAR2
1093 , p_rowid_tbl IN PO_TBL_VARCHAR2000
1094 , p_column_name_tbl IN PO_TBL_VARCHAR30 DEFAULT NULL
1095 )
1096 IS
1097
1098 TYPE t_tbl_data_type IS TABLE OF ALL_TAB_COLUMNS.column_name%TYPE;
1099 TYPE t_tbl_varchar IS TABLE OF VARCHAR2(4000);
1100 TYPE t_ref_csr IS REF CURSOR;
1101
1102 l_column_name_tbl po_tbl_varchar30;
1103 l_data_type_tbl t_tbl_data_type;
1104
1105 l_printable_column_tbl po_tbl_varchar30;
1106
1107 l_value_rowid_tbl po_tbl_varchar2000;
1108 l_value_tbl t_tbl_varchar;
1109
1110 l_sql VARCHAR2(32767);
1111 l_char VARCHAR2(4000);
1112
1113 l_col_i PLS_INTEGER;
1114
1115 l_table_name VARCHAR2(30);
1116 l_table_owner VARCHAR2(30);
1117 l_print_column_flag BOOLEAN;
1118
1119 l_log_head VARCHAR2(2000);
1120
1121 l_rowid_tbl po_tbl_varchar2000;
1122 l_rowid_csr t_ref_csr;
1123 /*Bug 12326054*/
1124 l_status VARCHAR2(1);
1125 l_industry VARCHAR2(1);
1126 l_return_status BOOLEAN;
1127
1128 BEGIN
1129 -- TODO: Refactor this procedure.
1130 -- It was copied from the PO_DEBUG.debug_table implementation,
1131 -- and needs to be reworked a bit.
1132 --
1133 -- Ideas for refactoring:
1134 -- Get the current schema via SYS_CONTEXT('USERENV','CURRENT_SCHEMA').
1135 -- Use all_synonyms to find the base table / view.
1136 -- (maybe just use user_synonyms, if login is always APPS, regardless of current_schema)
1137 -- Use all_tab_columns to get column names.
1138 --
1139
1140 IF d_stmt THEN
1141
1142 l_printable_column_tbl := po_tbl_varchar30();
1143 l_value_rowid_tbl := po_tbl_varchar2000();
1144 l_value_tbl := t_tbl_varchar();
1145
1146 l_table_name := UPPER(p_table_name);
1147
1148 /*Bug 12326054 : Commented out hard coding owner as PO.
1149 Instead get the table owner from FND_INSTALLATION.get_app_info*/
1150 -- l_table_owner := 'PO';
1154 l_status,
1151 -- Use the refactoring ideas to get the real value for table_owner.
1152 l_return_status := FND_INSTALLATION.get_app_info
1153 ( 'PO',
1155 l_industry,
1156 l_table_owner
1157 );
1158
1159
1160 l_log_head := p_module_base||TO_CHAR(p_position)|| '.DEBUG_TABLE.' || p_table_name||'.';
1161
1162 stmt(l_log_head||'START',NULL,'Logging non-null columns only. p_rowid_tbl.COUNT',p_rowid_tbl.COUNT);
1163
1164 --<Bug 15927193 Start>
1165 SELECT
1166 tab.column_name
1167 , tab.data_type
1168 BULK COLLECT INTO
1169 l_column_name_tbl
1170 , l_data_type_tbl
1171 FROM
1172 ALL_TAB_COLUMNS tab,
1173 ALL_SYNONYMS syn
1174 WHERE tab.TABLE_NAME = syn.table_name
1175 AND tab.owner=syn.owner
1176 AND tab.owner = l_table_owner
1177 AND syn.synonym_name= l_table_name
1178 ORDER BY tab.column_id
1179 ;
1180 --<Bug 15927193 End>
1181
1182 IF (p_rowid_tbl.COUNT = 1 AND p_rowid_tbl(1) = c_all_rows(1)) THEN
1183
1184 l_rowid_tbl := po_tbl_varchar2000();
1185
1186 OPEN l_rowid_csr FOR 'SELECT rowid FROM '||l_table_name ;
1187
1188 LOOP
1189
1190 FETCH l_rowid_csr INTO l_char;
1191 EXIT WHEN l_rowid_csr%NOTFOUND;
1192
1193 l_rowid_tbl.EXTEND;
1194 l_rowid_tbl(l_rowid_tbl.COUNT) := l_char;
1195
1196 END LOOP;
1197
1198 CLOSE l_rowid_csr;
1199
1200 ELSE
1201 l_rowid_tbl := p_rowid_tbl;
1202 END IF;
1203
1204 FOR i IN 1 .. l_column_name_tbl.COUNT LOOP
1205
1206 IF (l_data_type_tbl(i) = 'NUMBER') THEN
1207
1208 l_sql := 'TO_CHAR(' || l_column_name_tbl(i) || ')';
1209
1210 ELSIF (l_data_type_tbl(i) = 'DATE') THEN
1211
1212 l_sql := 'TO_CHAR(' || l_column_name_tbl(i) || ', ''DD-MON-RRRR HH:MI:SSAM'')';
1213
1214 ELSIF (l_data_type_tbl(i) = 'VARCHAR2') THEN
1215
1216 l_sql := l_column_name_tbl(i);
1217
1218 END IF;
1219
1220 IF (l_sql IS NULL AND p_column_name_tbl IS NULL) THEN
1221
1222 stmt(l_log_head||'COLUMN',NULL,
1223 'Unprintable column: '|| l_column_name_tbl(i) || ' (' || l_data_type_tbl(i) || ')');
1224
1225 ELSIF (l_sql IS NOT NULL) THEN
1226
1227 IF (p_column_name_tbl IS NULL) THEN
1228 l_print_column_flag := TRUE;
1229 ELSE
1230 l_print_column_flag := FALSE;
1231 FOR j IN 1 .. p_column_name_tbl.COUNT LOOP
1232 IF (l_column_name_tbl(i) = UPPER(p_column_name_tbl(j))) THEN
1233 l_print_column_flag := TRUE;
1234 EXIT;
1235 END IF;
1236 END LOOP;
1237 END IF;
1238
1239 IF l_print_column_flag THEN
1240
1241 l_printable_column_tbl.EXTEND;
1242 l_printable_column_tbl(l_printable_column_tbl.COUNT) := l_column_name_tbl(i);
1243
1244 l_sql := 'SELECT SUBSTR(' || l_sql || ',1,3900)'
1245 ||' FROM ' || l_table_name
1246 ||' WHERE rowid = :b_rowid';
1247
1248 FOR j IN 1 .. l_rowid_tbl.COUNT LOOP
1249
1250 BEGIN
1251
1252 EXECUTE IMMEDIATE l_sql INTO l_char USING l_rowid_tbl(j);
1253
1254 EXCEPTION
1255 WHEN OTHERS THEN
1256 l_char := SQLERRM;
1257 END;
1258
1259 l_value_rowid_tbl.EXTEND;
1260 l_value_rowid_tbl(l_value_rowid_tbl.COUNT) := l_rowid_tbl(j);
1261
1262 l_value_tbl.EXTEND;
1263 l_value_tbl(l_value_tbl.COUNT) := l_char;
1264
1265 END LOOP;
1266
1267 END IF;
1268
1269 END IF;
1270
1271 END LOOP;
1272
1273
1274 -- Report input columns that don't exist / aren't printable.
1275
1276 IF (p_column_name_tbl IS NOT NULL) THEN
1277
1278 FOR i IN 1 .. p_column_name_tbl.COUNT LOOP
1279
1280 l_print_column_flag := FALSE;
1281
1282 FOR j IN 1 .. l_column_name_tbl.COUNT LOOP
1283 IF (UPPER(p_column_name_tbl(i)) = l_column_name_tbl(j)) THEN
1284 l_print_column_flag := TRUE;
1285 EXIT;
1286 END IF;
1287 END LOOP;
1288
1289 IF (NOT l_print_column_flag) THEN
1290 stmt(l_log_head||'COLUMN',NULL,
1291 'Non-existent column: '|| p_column_name_tbl(i));
1292 ELSE
1293
1294 l_print_column_flag := FALSE;
1295
1296 FOR j IN 1 .. l_printable_column_tbl.COUNT LOOP
1297 IF (UPPER(p_column_name_tbl(i)) = l_printable_column_tbl(j)) THEN
1298 l_print_column_flag := TRUE;
1299 EXIT;
1300 END IF;
1301 END LOOP;
1302
1303 IF (NOT l_print_column_flag) THEN
1304 stmt(l_log_head||'COLUMN',NULL,
1305 'Unprintable column: '|| l_column_name_tbl(i) || ' (unprintable data type)');
1306 END IF;
1307
1308 END IF;
1309
1310 END LOOP;
1311
1312 END IF;
1313
1314
1315 FOR i IN 1 .. l_rowid_tbl.COUNT LOOP
1316
1317 l_col_i := 1;
1318
1319 FOR j IN 1 .. l_value_rowid_tbl.COUNT LOOP
1320
1321 IF (l_value_rowid_tbl(j) = l_rowid_tbl(i)) THEN
1322
1323 IF (l_value_tbl(j) IS NOT NULL) THEN
1324 stmt(l_log_head||'ROW',NULL,
1328
1325 l_rowid_tbl(i)||'.'||l_printable_column_tbl(l_col_i),
1326 l_value_tbl(j));
1327 END IF;
1329 l_col_i := l_col_i + 1;
1330
1331 END IF;
1332
1333 END LOOP;
1334
1335 END LOOP;
1336
1337 END IF;
1338
1339 EXCEPTION
1340 WHEN OTHERS THEN
1341 IF d_stmt THEN
1342
1343 l_log_head := p_module_base||TO_CHAR(p_position)||'.DEBUG_TABLE.'||p_table_name||'.'||'EXCEPTION';
1344
1345 stmt(l_log_head,NULL,SQLERRM);
1346
1347 stmt(l_log_head,NULL,'p_rowid_tbl',p_rowid_tbl);
1348 stmt(l_log_head,NULL,'p_column_name_tbl',p_column_name_tbl);
1349 stmt(l_log_head,NULL,'l_table_owner',l_table_owner);
1350 stmt(l_log_head,NULL,'l_rowid_tbl',l_rowid_tbl);
1351 stmt(l_log_head,NULL,'l_column_name_tbl',l_column_name_tbl);
1352 stmt(l_log_head,NULL,'l_printable_column_tbl',l_printable_column_tbl);
1353 stmt(l_log_head,NULL,'l_sql',l_sql);
1354 stmt(l_log_head,NULL,'l_char',l_char);
1355 stmt(l_log_head,NULL,'l_table_name',l_table_name);
1356 stmt(l_log_head,NULL,'l_table_owner',l_table_owner);
1357
1358 END IF;
1359
1360 END stmt_table;
1361
1362 -- PROCEDURE-level logging.
1363
1364 PROCEDURE proc_begin(
1365 p_module_base IN VARCHAR2
1366 )
1367 IS
1368 BEGIN
1369 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,D_START_OF_SUBPROGRAM);
1370 END proc_begin;
1371
1372 PROCEDURE proc_begin(
1373 p_module_base IN VARCHAR2
1374 , p_parameter_name IN VARCHAR2
1375 , p_parameter_value IN VARCHAR2
1376 )
1377 IS
1378 BEGIN
1379 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,
1380 var_to_string(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 NUMBER
1387 )
1388 IS
1389 BEGIN
1390 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,
1391 var_to_string(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 DATE
1398 )
1399 IS
1400 BEGIN
1401 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,
1402 var_to_string(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 BOOLEAN
1409 )
1410 IS
1411 BEGIN
1412 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,
1413 var_to_string(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_NUMBER
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_DATE
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_VARCHAR1
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_VARCHAR5
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_begin(
1461 p_module_base IN VARCHAR2
1462 , p_parameter_name IN VARCHAR2
1463 , p_parameter_value IN PO_TBL_VARCHAR30
1464 )
1465 IS
1466 BEGIN
1467 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,
1468 p_parameter_name,p_parameter_value);
1469 END proc_begin;
1470
1471 PROCEDURE proc_begin(
1472 p_module_base IN VARCHAR2
1473 , p_parameter_name IN VARCHAR2
1474 , p_parameter_value IN PO_TBL_VARCHAR100
1475 )
1476 IS
1477 BEGIN
1478 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,
1479 p_parameter_name,p_parameter_value);
1480 END proc_begin;
1481
1482 PROCEDURE proc_begin(
1483 p_module_base IN VARCHAR2
1484 , p_parameter_name IN VARCHAR2
1485 , p_parameter_value IN PO_TBL_VARCHAR2000
1486 )
1487 IS
1488 BEGIN
1489 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,
1490 p_parameter_name,p_parameter_value);
1491 END proc_begin;
1492
1493 PROCEDURE proc_begin(
1494 p_module_base IN VARCHAR2
1495 , p_parameter_name IN VARCHAR2
1496 , p_parameter_value IN PO_TBL_VARCHAR4000
1497 )
1498 IS
1499 BEGIN
1500 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_BEGIN,
1504 PROCEDURE proc_end(
1501 p_parameter_name,p_parameter_value);
1502 END proc_begin;
1503
1505 p_module_base IN VARCHAR2
1506 )
1507 IS
1508 BEGIN
1509 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,D_END_OF_SUBPROGRAM);
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 VARCHAR2
1516 )
1517 IS
1518 BEGIN
1519 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,
1520 var_to_string(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 NUMBER
1527 )
1528 IS
1529 BEGIN
1530 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,
1531 var_to_string(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 DATE
1538 )
1539 IS
1540 BEGIN
1541 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,
1542 var_to_string(p_parameter_name,p_parameter_value));
1543 END proc_end;
1544
1545 PROCEDURE proc_end(
1546 p_module_base IN VARCHAR2
1547 , p_parameter_name IN VARCHAR2
1548 , p_parameter_value IN BOOLEAN
1549 )
1550 IS
1551 BEGIN
1552 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,
1553 var_to_string(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_NUMBER
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_DATE
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_VARCHAR1
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_VARCHAR5
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_end(
1601 p_module_base IN VARCHAR2
1602 , p_parameter_name IN VARCHAR2
1603 , p_parameter_value IN PO_TBL_VARCHAR30
1604 )
1605 IS
1606 BEGIN
1607 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,
1608 p_parameter_name,p_parameter_value);
1609 END proc_end;
1610
1611 PROCEDURE proc_end(
1612 p_module_base IN VARCHAR2
1613 , p_parameter_name IN VARCHAR2
1614 , p_parameter_value IN PO_TBL_VARCHAR100
1615 )
1616 IS
1617 BEGIN
1618 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,
1619 p_parameter_name,p_parameter_value);
1620 END proc_end;
1621
1622 PROCEDURE proc_end(
1623 p_module_base IN VARCHAR2
1624 , p_parameter_name IN VARCHAR2
1625 , p_parameter_value IN PO_TBL_VARCHAR2000
1626 )
1627 IS
1628 BEGIN
1629 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,
1630 p_parameter_name,p_parameter_value);
1631 END proc_end;
1632
1633 PROCEDURE proc_end(
1634 p_module_base IN VARCHAR2
1635 , p_parameter_name IN VARCHAR2
1636 , p_parameter_value IN PO_TBL_VARCHAR4000
1637 )
1638 IS
1639 BEGIN
1640 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_END,
1641 p_parameter_name,p_parameter_value);
1642 END proc_end;
1643
1644 PROCEDURE proc_return(
1645 p_module_base IN VARCHAR2
1646 , p_return_value IN VARCHAR2
1647 )
1648 IS
1649 BEGIN
1650 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1651 var_to_string(D_RETURN_VALUE,p_return_value));
1652 END proc_return;
1653
1654 PROCEDURE proc_return(
1655 p_module_base IN VARCHAR2
1656 , p_return_value IN NUMBER
1657 )
1658 IS
1659 BEGIN
1660 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1661 var_to_string(D_RETURN_VALUE,p_return_value));
1662 END proc_return;
1663
1664 PROCEDURE proc_return(
1665 p_module_base IN VARCHAR2
1666 , p_return_value IN DATE
1667 )
1668 IS
1669 BEGIN
1670 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1671 var_to_string(D_RETURN_VALUE,p_return_value));
1672 END proc_return;
1673
1674 PROCEDURE proc_return(
1675 p_module_base IN VARCHAR2
1676 , p_return_value IN BOOLEAN
1677 )
1678 IS
1679 BEGIN
1680 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1684 PROCEDURE proc_return(
1681 var_to_string(D_RETURN_VALUE,p_return_value));
1682 END proc_return;
1683
1685 p_module_base IN VARCHAR2
1686 , p_return_value IN PO_TBL_NUMBER
1687 )
1688 IS
1689 BEGIN
1690 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1691 D_RETURN_VALUE,p_return_value);
1692 END proc_return;
1693
1694 PROCEDURE proc_return(
1695 p_module_base IN VARCHAR2
1696 , p_return_value IN PO_TBL_DATE
1697 )
1698 IS
1699 BEGIN
1700 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1701 D_RETURN_VALUE,p_return_value);
1702 END proc_return;
1703
1704 PROCEDURE proc_return(
1705 p_module_base IN VARCHAR2
1706 , p_return_value IN PO_TBL_VARCHAR1
1707 )
1708 IS
1709 BEGIN
1710 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1711 D_RETURN_VALUE,p_return_value);
1712 END proc_return;
1713
1714 PROCEDURE proc_return(
1715 p_module_base IN VARCHAR2
1716 , p_return_value IN PO_TBL_VARCHAR5
1717 )
1718 IS
1719 BEGIN
1720 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1721 D_RETURN_VALUE,p_return_value);
1722 END proc_return;
1723
1724 PROCEDURE proc_return(
1725 p_module_base IN VARCHAR2
1726 , p_return_value IN PO_TBL_VARCHAR30
1727 )
1728 IS
1729 BEGIN
1730 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1731 D_RETURN_VALUE,p_return_value);
1732 END proc_return;
1733
1734 PROCEDURE proc_return(
1735 p_module_base IN VARCHAR2
1736 , p_return_value IN PO_TBL_VARCHAR100
1737 )
1738 IS
1739 BEGIN
1740 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1741 D_RETURN_VALUE,p_return_value);
1742 END proc_return;
1743
1744 PROCEDURE proc_return(
1745 p_module_base IN VARCHAR2
1746 , p_return_value IN PO_TBL_VARCHAR2000
1747 )
1748 IS
1749 BEGIN
1750 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1751 D_RETURN_VALUE,p_return_value);
1752 END proc_return;
1753
1754 PROCEDURE proc_return(
1755 p_module_base IN VARCHAR2
1756 , p_return_value IN PO_TBL_VARCHAR4000
1757 )
1758 IS
1759 BEGIN
1760 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RETURN,
1761 D_RETURN_VALUE,p_return_value);
1762 END proc_return;
1763
1764 PROCEDURE proc_raise(
1765 p_module_base IN VARCHAR2
1766 )
1767 IS
1768 BEGIN
1769 log(FND_LOG.LEVEL_PROCEDURE,p_module_base,D_RAISE,
1770 'Raising exception: SQLERRM = '||SQLERRM);
1771 END proc_raise;
1772
1773 -- EVENT-level logging.
1774
1775 PROCEDURE event(
1776 p_module_base IN VARCHAR2
1777 , p_position IN NUMBER
1778 , p_message_text IN VARCHAR2
1779 )
1780 IS
1781 BEGIN
1782 log(FND_LOG.LEVEL_EVENT,p_module_base,TO_CHAR(p_position),p_message_text);
1783 END event;
1784
1785 -- EXCEPTION-level logging.
1786
1787 PROCEDURE exc(
1788 p_module_base IN VARCHAR2
1789 , p_position IN NUMBER
1790 , p_message_text IN VARCHAR2 DEFAULT NULL
1791 )
1792 IS
1793 BEGIN
1794 log(FND_LOG.LEVEL_EXCEPTION,p_module_base,TO_CHAR(p_position)||'.EXCEPTION',
1795 p_message_text||';SQLERRM = '||SQLERRM);
1796 END exc;
1797
1798
1799 -----------------------------------------------------------------------------
1800 -- Package initialization.
1801 -----------------------------------------------------------------------------
1802
1803 BEGIN
1804
1805 refresh_log_flags();
1806
1807 END PO_LOG;
[Home] [Help]