[Home] [Help]
PACKAGE BODY: APPS.XLA_TRANSACTION_ID_PKG
Source
1 PACKAGE BODY xla_transaction_id_pkg AS
2 -- $Header: xlacmtid.pkb 120.19 2006/11/01 00:38:35 weshen ship $
3 /*===========================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +============================================================================+
8 | PACKAGE NAME |
9 | xla_transaction_id_pkg |
10 | |
11 | DESCRIPTION |
12 | This package provides routines to handle transaction identifiers. |
13 | |
14 | HISTORY |
15 | 10/07/2002 S. Singhania Created |
16 | 11/30/2002 S. Singhania Added code to test the validity of the |
17 | view and its columns in the database |
18 | Added p_request_id parameter to |
19 | get_query_string. |
20 | 12/12/2002 S. Singhania Added EVT.ENTITY_ID in the select |
21 | string that is returned back from |
22 | function GET_QUERY_STRING |
23 | 06/26/2003 S. Singhania Removed the code that refers the table |
24 | XLA_ACCOUNTING_LOG. This was not |
25 | needed as events are marked with |
26 | parent request_id of accounting prog |
27 | rather than the child's request_id. |
28 | 07/22/2003 S. Singhania Added dbdrv command to the file |
29 | 08/27/2003 S. Singhania Replaced the funtion with the procedure |
30 | GET_QUERY_STRINGS so that the report |
31 | XLAACCPB.rdf can use this procedure to |
32 | build its query. bug # 3113574 |
33 | 10/13/2003 S. Singhania Added NOCOPY hint to OUT parameters in |
34 | GET_QUERY_STRINGS |
35 | 03/24/2004 S. Singhania Added local trace procedure and added |
36 | FND_LOG messages. |
37 | Bug 3389175. Added a condition to perform |
38 | outerjoin to the Trx id view if fnd log |
39 | is enabled. |
40 | 06/27/2005 W. Shen Bug 4447717. directly return if no |
41 | transaction identifier |
42 | 07/20/2005 W. Shen Change the get_transcation_identifiers |
43 | from procedure to function to return |
44 | some error result so it can be processed |
45 | 0-- success |
46 | 1-- fail |
47 | 12/30/2005 W. Chan Bug 4908407. Make user transaction date |
48 | timezone converted. |
49 | 02/27/2006 V. Kumar Bug 5013132 Using bind variable in Execute |
50 | Immediate of l_sql_str |
51 +===========================================================================*/
52
53 --=============================================================================
54 -- **************** declaraions ********************
55 --=============================================================================
56 -------------------------------------------------------------------------------
57 -- declaring types
58 -------------------------------------------------------------------------------
59
60 TYPE t_rec IS RECORD
61 (f1 VARCHAR2(80)
62 ,f2 VARCHAR2(30)
63 ,f3 VARCHAR2(240)
64 ,f4 VARCHAR2(30));
65 TYPE t_array IS TABLE OF t_rec INDEX BY BINARY_INTEGER;
66
67
68 --=============================================================================
69 -- *********** Local Trace Routine **********
70 --=============================================================================
71 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
72 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
73 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
74 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
75 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
76 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
77
78 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
79 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_transaction_id_pkg';
80
81 g_log_level NUMBER;
82 g_log_enabled BOOLEAN;
83
84 PROCEDURE trace
85 (p_msg IN VARCHAR2
86 ,p_level IN NUMBER
87 ,p_module IN VARCHAR2 DEFAULT C_DEFAULT_MODULE) IS
88 BEGIN
89 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
90 fnd_log.message(p_level, p_module);
91 ELSIF p_level >= g_log_level THEN
92 fnd_log.string(p_level, p_module, p_msg);
93 END IF;
94
95 EXCEPTION
96 WHEN xla_exceptions_pkg.application_exception THEN
97 RAISE;
98 WHEN OTHERS THEN
99 xla_exceptions_pkg.raise_message
100 (p_location => 'xla_transaction_id_pkg.trace');
101 END trace;
102
103
104 --=============================================================================
105 -- *********** public procedures and functions **********
106 --=============================================================================
107 --=============================================================================
108 --
109 --
110 --
111 --
112 --
113 --
114 --
115 --
116 --
117 --
118 -- Following are public routines
119 --
120 -- 1. get_query_strings
121 --
122 --
123 --
124 --
125 --
126 --
127 --
128 --
129 --
130 --
131 --
132 --=============================================================================
133
134 --=============================================================================
135 --
136 --
137 --
138 --=============================================================================
139
140 PROCEDURE get_query_strings
141 (p_application_id IN INTEGER
142 ,p_entity_code IN VARCHAR2
143 ,p_event_class_code IN VARCHAR2
144 ,p_reporting_view_name IN VARCHAR2
145 ,p_request_id IN NUMBER
146 ,p_select_str OUT NOCOPY VARCHAR2
147 ,p_from_str OUT NOCOPY VARCHAR2
148 ,p_where_str OUT NOCOPY VARCHAR2) IS
149 CURSOR cols_csr IS
150 (SELECT xid.transaction_id_col_name_1 trx_col_1
151 ,xid.transaction_id_col_name_2 trx_col_2
152 ,xid.transaction_id_col_name_3 trx_col_3
153 ,xid.transaction_id_col_name_4 trx_col_4
154 ,xid.source_id_col_name_1 src_col_1
155 ,xid.source_id_col_name_2 src_col_2
156 ,xid.source_id_col_name_3 src_col_3
157 ,xid.source_id_col_name_4 src_col_4
158 ,xem.column_name column_name
159 ,xem.column_title prompt
160 ,utc.data_type data_type
161 FROM xla_entity_id_mappings xid
162 ,xla_event_mappings_vl xem
163 ,user_tab_columns utc
164 WHERE xid.application_id = p_application_id
165 AND xid.entity_code = p_entity_code
166 AND xem.application_id = p_application_id
167 AND xem.entity_code = p_entity_code
168 AND xem.event_class_code = p_event_class_code
169 AND utc.table_name = p_reporting_view_name
170 AND utc.column_name = xem.column_name)
171 ORDER BY xem.user_sequence;
172
173 l_col_array t_array;
174 l_null_col_array t_array;
175 l_col_string VARCHAR2(4000) := NULL;
176 l_view_name VARCHAR2(80);
177 l_join_string VARCHAR2(4000) := NULL;
178 l_sql_string VARCHAR2(4000);
179 l_index INTEGER;
180 l_outerjoin VARCHAR2(30);
181 l_log_module VARCHAR2(240);
182 BEGIN
183 IF g_log_enabled THEN
184 l_log_module := C_DEFAULT_MODULE||'.get_query_strings';
185 END IF;
186 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
187 trace
188 (p_msg => 'BEGIN of procedure GET_QUERY_STRINGS'
189 ,p_level => C_LEVEL_PROCEDURE
190 ,p_module => l_log_module);
191 trace
192 (p_msg => 'p_application_id = '||p_application_id
193 ,p_level => C_LEVEL_PROCEDURE
194 ,p_module => l_log_module);
195 trace
196 (p_msg => 'p_entity_code = '||p_entity_code
197 ,p_level => C_LEVEL_PROCEDURE
198 ,p_module => l_log_module);
199 trace
200 (p_msg => 'p_event_class_code = '||p_event_class_code
201 ,p_level => C_LEVEL_PROCEDURE
202 ,p_module => l_log_module);
203 trace
204 (p_msg => 'p_reporting_view_name = '||p_reporting_view_name
205 ,p_level => C_LEVEL_PROCEDURE
206 ,p_module => l_log_module);
207 trace
208 (p_msg => 'p_request_id = '||p_request_id
209 ,p_level => C_LEVEL_PROCEDURE
210 ,p_module => l_log_module);
211 END IF;
212
213 ----------------------------------------------------------------------------
214 -- creating a dummy array that contains "NULL" strings
215 ----------------------------------------------------------------------------
216 FOR i IN 1..10 LOOP
217 l_null_col_array(i).f1 := 'NULL';
218 l_null_col_array(i).f2 := 'NULL';
219 l_null_col_array(i).f3 := 'NULL';
220 l_null_col_array(i).f4 := 'NULL';
221 END LOOP;
222
223 ----------------------------------------------------------------------------
224 -- initiating the array that contains name of the columns to be selected
225 -- from the TID View.
226 ----------------------------------------------------------------------------
227 l_col_array := l_null_col_array;
228
229 ----------------------------------------------------------------------------
230 -- creating SELECT,FROM and WHERE clause strings when the reporting view is
231 -- defined for an Event Class.
232 ----------------------------------------------------------------------------
233 IF p_reporting_view_name IS NOT NULL THEN
234 -------------------------------------------------------------------------
235 -- creating string to be added to FROM clause
236 -------------------------------------------------------------------------
237 l_view_name := ',' || p_reporting_view_name || ' TIV';
238
239 l_index := 0;
240 FOR c1 IN cols_csr LOOP
241 l_index := l_index + 1;
242
243 ----------------------------------------------------------------------
244 -- creating string to be added to WHERE clause
245 ----------------------------------------------------------------------
246 IF l_index = 1 THEN
247 -------------------------------------------------------------------
248 -- Bug 3389175
249 -- Following logic is build to make sure all events are reported
250 -- if debug is enabled evenif there is no data for the event in the
251 -- transaction id view.
252 -- if log enabled then
253 -- outer join to TID view
254 -- endif
255 -------------------------------------------------------------------
256 IF g_log_level <> C_LEVEL_LOG_DISABLED THEN
257 l_outerjoin := '(+)';
258 ELSE
259 l_outerjoin := NULL;
260 END IF;
261
262 IF c1.trx_col_1 IS NOT NULL THEN
263 l_join_string := l_join_string ||
264 ' AND TIV.'|| c1.trx_col_1 ||l_outerjoin ||
265 ' = ENT.'|| c1.src_col_1;
266 END IF;
267 IF c1.trx_col_2 IS NOT NULL THEN
268 l_join_string := l_join_string ||
269 ' AND TIV.'|| c1.trx_col_2 ||l_outerjoin ||
270 ' = ENT.'|| c1.src_col_2;
271 END IF;
272 IF c1.trx_col_3 IS NOT NULL THEN
273 l_join_string := l_join_string ||
274 ' AND TIV.'|| c1.trx_col_3 ||l_outerjoin ||
275 ' = ENT.'|| c1.src_col_3;
276 END IF;
277 IF c1.trx_col_4 IS NOT NULL THEN
278 l_join_string := l_join_string ||
279 ' AND TIV.'|| c1.trx_col_4 ||l_outerjoin ||
280 ' = ENT.'|| c1.src_col_4;
281 END IF;
282 END IF;
283
284 ----------------------------------------------------------------------
288
285 -- getting the PROMPTs to be displayed
286 ----------------------------------------------------------------------
287 l_col_array(l_index).f1 := ''''||c1.prompt||'''';
289 ----------------------------------------------------------------------
290 -- getting the columns to be displayed
291 ----------------------------------------------------------------------
292 IF c1.data_type = 'NUMBER' THEN
293 l_col_array(l_index).f2 := 'TIV.'|| c1.column_name;
294 ELSIF c1.data_type = 'VARCHAR2' THEN
295 l_col_array(l_index).f3 := 'TIV.'|| c1.column_name;
296 ELSIF c1.data_type = 'DATE' THEN
297 l_col_array(l_index).f4 := 'TIV.'|| c1.column_name;
298 END IF;
299 END LOOP;
300 END IF;
301
302 -----------------------------------------------------------------------------
303 -- building the string to be added to the SELECT clause
304 -----------------------------------------------------------------------------
305 FOR i IN 1..l_col_array.count LOOP
306 l_col_string := l_col_string || ',' ||
307 l_col_array(i).f1||' prompt_' ||TO_CHAR(i)||','||
308 l_col_array(i).f2||' value_num_' ||TO_CHAR(i)||','||
309 l_col_array(i).f3||' value_char_'||TO_CHAR(i)||','||
310 l_col_array(i).f4||' value_date_'||TO_CHAR(i);
311 END LOOP;
312
313 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
314 trace
315 (p_msg => 'l_col_string = '||l_col_string
316 ,p_level => C_LEVEL_STATEMENT
317 ,p_module => l_log_module);
318 END IF;
319 -----------------------------------------------------------------------------
320 -- Following tests whether the view and columns are defined in the data base
321 -----------------------------------------------------------------------------
322 IF p_reporting_view_name IS NOT NULL THEN
323 BEGIN
324 ----------------------------------------------------------------------
325 -- build and execute a dummy query if the view name is defined for
326 -- the class
327 -- NOTE: following never fails because the cursor joins to
328 -- user_tab_columns table that will make sure that view and column
329 -- names fetched exists. This can beremoved unless we decide to go
330 -- for outerjoin on this table.
331 ----------------------------------------------------------------------
332 l_sql_string :=
333 ' SELECT' ||
334 ' NULL dummy' ||
335 l_col_string ||
336 ' FROM' ||
337 ' dual dual' ||
338 l_view_name ||
339 ' WHERE' ||
340 ' ROWNUM = 1';
341
342 EXECUTE IMMEDIATE l_sql_string;
343 EXCEPTION
344 WHEN OTHERS THEN
345 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
346 trace
347 (p_msg => 'Technical Warning: There seems to a problem in retreiving '||
348 'transaction identifiers from '||p_reporting_view_name
349 ,p_level => C_LEVEL_EXCEPTION
350 ,p_module => l_log_module);
351 END IF;
352
353 ----------------------------------------------------------------------
354 -- if the above query raises an exception following clears the FROM
355 -- and WHERE strings and creates the error to be displayed to the user
356 ----------------------------------------------------------------------
357 l_col_array := l_null_col_array;
358 l_col_string := NULL;
359 l_col_array(1).f1 := '''Error''';
360 l_col_array(1).f3 := '''Problem with Transaction Identifier View''';
361 l_view_name := NULL;
362 l_join_string := NULL;
363
364 FOR i IN 1..l_col_array.count LOOP
365 l_col_string := l_col_string || ',' ||
366 l_col_array(i).f1||' prompt_' ||TO_CHAR(i)||','||
367 l_col_array(i).f2||' value_num_' ||TO_CHAR(i)||','||
368 l_col_array(i).f3||' value_char_'||TO_CHAR(i)||','||
369 l_col_array(i).f4||' value_date_'||TO_CHAR(i);
370 END LOOP;
371
372 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
373 trace
374 (p_msg => 'l_col_string = '||l_col_string
375 ,p_level => C_LEVEL_STATEMENT
376 ,p_module => l_log_module);
377 END IF;
378 END;
379 END IF;
380
381 p_select_str := l_col_string;
382 p_from_str := l_view_name;
383 p_where_str := l_join_string;
384
385 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
386 trace
387 (p_msg => 'p_select_str = '||p_select_str
388 ,p_level => C_LEVEL_PROCEDURE
389 ,p_module => l_log_module);
390 trace
391 (p_msg => 'p_from_str = '||p_from_str
392 ,p_level => C_LEVEL_PROCEDURE
393 ,p_module => l_log_module);
394 trace
398 trace
395 (p_msg => 'p_where_str = '||p_where_str
396 ,p_level => C_LEVEL_PROCEDURE
397 ,p_module => l_log_module);
399 (p_msg => 'End of procedure GET_QUERY_STRINGS'
400 ,p_level => C_LEVEL_PROCEDURE
401 ,p_module => l_log_module);
402 END IF;
403 EXCEPTION
404 WHEN OTHERS THEN
405 xla_exceptions_pkg.raise_message
406 (p_location => 'xla_ss_transaction_id_pkg.get_query_strings');
407 END get_query_strings;
408
409
410 --=============================================================================
411 --
412 --
413 --
414 --=============================================================================
415 FUNCTION get_transaction_identifiers(
416 p_application_id in INTEGER,
417 p_entity_code in VARCHAR2,
418 p_event_class_code in VARCHAR2,
419 p_event_id in INTEGER,
420 p_transactionid1_prompt out NOCOPY VARCHAR2,
421 p_transactionid1_value out NOCOPY VARCHAR2,
422 p_transactionid2_prompt out NOCOPY VARCHAR2,
423 p_transactionid2_value out NOCOPY VARCHAR2,
424 p_transactionid3_prompt out NOCOPY VARCHAR2,
425 p_transactionid3_value out NOCOPY VARCHAR2,
426 p_transactionid4_prompt out NOCOPY VARCHAR2,
427 p_transactionid4_value out NOCOPY VARCHAR2,
428 p_transactionid5_prompt out NOCOPY VARCHAR2,
429 p_transactionid5_value out NOCOPY VARCHAR2,
430 p_transactionid6_prompt out NOCOPY VARCHAR2,
431 p_transactionid6_value out NOCOPY VARCHAR2,
432 p_transactionid7_prompt out NOCOPY VARCHAR2,
433 p_transactionid7_value out NOCOPY VARCHAR2,
434 p_transactionid8_prompt out NOCOPY VARCHAR2,
435 p_transactionid8_value out NOCOPY VARCHAR2,
436 p_transactionid9_prompt out NOCOPY VARCHAR2,
437 p_transactionid9_value out NOCOPY VARCHAR2,
438 p_transactionid10_prompt out NOCOPY VARCHAR2,
439 p_transactionid10_value out NOCOPY VARCHAR2) return NUMBER is
440
441 l_reporting_view_name xla_event_class_attrs.reporting_view_name%TYPE;
442 CURSOR cols_csr IS
443 (SELECT xid.transaction_id_col_name_1 trx_col_1
444 ,xid.transaction_id_col_name_2 trx_col_2
445 ,xid.transaction_id_col_name_3 trx_col_3
446 ,xid.transaction_id_col_name_4 trx_col_4
447 ,xid.source_id_col_name_1 src_col_1
448 ,xid.source_id_col_name_2 src_col_2
449 ,xid.source_id_col_name_3 src_col_3
450 ,xid.source_id_col_name_4 src_col_4
451 ,xem.column_name column_name
452 ,xem.column_title prompt
453 ,utc.data_type data_type
454 FROM xla_entity_id_mappings xid
455 ,xla_event_mappings_vl xem
456 ,user_tab_columns utc
457 WHERE xid.application_id = p_application_id
458 AND xid.entity_code = p_entity_code
459 AND xem.application_id = p_application_id
460 AND xem.entity_code = p_entity_code
461 AND xem.event_class_code = p_event_class_code
462 AND utc.table_name = l_reporting_view_name
463 AND utc.column_name = xem.column_name)
464 ORDER BY xem.user_sequence;
465
466 l_col_array t_array;
467 l_null_col_array t_array;
468 l_col_string VARCHAR2(4000) := NULL;
469 l_view_name VARCHAR2(80);
470 l_join_string VARCHAR2(4000) := NULL;
471 l_sql_string VARCHAR2(4000);
472 l_index INTEGER;
473 l_server_timezone_id INTEGER;
474 l_client_timezone_id INTEGER;
475 l_date_format VARCHAR2(80);
476 l_log_module VARCHAR2(240);
477 Begin
478 IF g_log_enabled THEN
479 l_log_module := C_DEFAULT_MODULE||'.get_transaction_identifiers';
480 END IF;
481 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
482 trace
483 (p_msg => 'BEGIN of procedure GET_TRANSACTION_IDENTIFIERS'
484 ,p_level => C_LEVEL_PROCEDURE
485 ,p_module => l_log_module);
486 trace
487 (p_msg => 'p_application_id = '||p_application_id
488 ,p_level => C_LEVEL_PROCEDURE
489 ,p_module => l_log_module);
490 trace
491 (p_msg => 'p_entity_code = '||p_entity_code
492 ,p_level => C_LEVEL_PROCEDURE
493 ,p_module => l_log_module);
494 trace
495 (p_msg => 'p_event_class_code = '||p_event_class_code
496 ,p_level => C_LEVEL_PROCEDURE
497 ,p_module => l_log_module);
498 trace
499 (p_msg => 'p_event_id = '||p_event_id
500 ,p_level => C_LEVEL_PROCEDURE
501 ,p_module => l_log_module);
502 END IF;
503
504 select reporting_view_name
505 into l_reporting_view_name
506 from xla_event_class_attrs
507 where application_id=p_application_id
508 and entity_code=p_entity_code
509 and event_class_code=p_event_class_code;
510 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
511 trace
512 (p_msg => 'l_reporting_vie_name:'||l_reporting_view_name
513 ,p_level => C_LEVEL_STATEMENT
514 ,p_module => l_log_module);
515 END IF;
516 ----------------------------------------------------------------------------
520 l_null_col_array(i).f1 := '';
517 -- creating a dummy array that contains "NULL" strings
518 ----------------------------------------------------------------------------
519 FOR i IN 1..10 LOOP
521 l_null_col_array(i).f2 := 'NULL';
522 l_null_col_array(i).f3 := 'NULL';
523 l_null_col_array(i).f4 := 'NULL';
524 END LOOP;
525
526 ----------------------------------------------------------------------------
527 -- initiating the array that contains name of the columns to be selected
528 -- from the TID View.
529 ----------------------------------------------------------------------------
530 l_col_array := l_null_col_array;
531
532 ----------------------------------------------------------------------------
533 -- Get information for timezone conversion
534 ----------------------------------------------------------------------------
535 l_server_timezone_id := fnd_profile.value('SERVER_TIMEZONE_ID');
536 l_client_timezone_id := fnd_profile.value('CLIENT_TIMEZONE_ID');
537 l_date_format := fnd_profile.value('ICX_DATE_FORMAT_MASK');
538
539 ----------------------------------------------------------------------------
540 -- creating SELECT,FROM and WHERE clause strings when the reporting view is
541 -- defined for an Event Class.
542 ----------------------------------------------------------------------------
543 IF l_reporting_view_name IS NOT NULL THEN
544 -------------------------------------------------------------------------
545 -- creating string to be added to FROM clause
546 -------------------------------------------------------------------------
547 l_view_name := ', ' || l_reporting_view_name || ' TIV';
548
549 l_index := 0;
550 FOR c1 IN cols_csr LOOP
551 l_index := l_index + 1;
552
553 ----------------------------------------------------------------------
554 -- creating string to be added to WHERE clause
555 ----------------------------------------------------------------------
556 IF l_index = 1 THEN
557 IF c1.trx_col_1 IS NOT NULL THEN
558 l_join_string := l_join_string || ' AND TIV.'|| c1.trx_col_1 ||
559 ' = ENT.'|| c1.src_col_1;
560 END IF;
561 IF c1.trx_col_2 IS NOT NULL THEN
562 l_join_string := l_join_string || ' AND TIV.'|| c1.trx_col_2 ||
563 ' = ENT.'|| c1.src_col_2;
564 END IF;
565 IF c1.trx_col_3 IS NOT NULL THEN
566 l_join_string := l_join_string || ' AND TIV.'|| c1.trx_col_3 ||
567 ' = ENT.'|| c1.src_col_3;
568 END IF;
569 IF c1.trx_col_4 IS NOT NULL THEN
570 l_join_string := l_join_string || ' AND TIV.'|| c1.trx_col_4 ||
571 ' = ENT.'|| c1.src_col_4;
572 END IF;
573 END IF;
574
575 ----------------------------------------------------------------------
576 -- getting all the PROMPTs to be displayed
577 ----------------------------------------------------------------------
578 l_col_array(l_index).f1 := c1.prompt;
579
580 ----------------------------------------------------------------------
581 -- getting all the columns to be displayed
582 ----------------------------------------------------------------------
583 IF c1.data_type = 'VARCHAR2' THEN
584 l_col_array(l_index).f3 := 'TIV.'|| c1.column_name;
585 ELSIF c1.data_type = 'DATE' THEN
586 IF(l_server_timezone_id is null or l_client_timezone_id is null) THEN
587 l_col_array(l_index).f3 := 'to_char(TIV.'|| c1.column_name||', '''||l_date_format||' HH24:MI:SS'')';
588 ELSE
589 l_col_array(l_index).f3 := 'to_char(HZ_TIMEZONE_PUB.Convert_DateTime('||l_server_timezone_id
590 ||','||l_client_timezone_id
591 ||', TIV.'|| c1.column_name||'), '''||l_date_format||' HH24:MI:SS'')';
592 END IF;
593 ELSE
594 l_col_array(l_index).f3 := 'to_char(TIV.'|| c1.column_name||')';
595 END IF;
596 END LOOP;
597 END IF;
598
599 IF(l_index = 0) THEN
600 p_transactionid1_value:=null;
601 p_transactionid2_value:=null;
602 p_transactionid3_value:=null;
603 p_transactionid4_value:=null;
604 p_transactionid5_value:=null;
605 p_transactionid6_value:=null;
606 p_transactionid7_value:=null;
607 p_transactionid8_value:=null;
608 p_transactionid9_value:=null;
609 p_transactionid10_value:=null;
610 p_transactionid1_prompt:=null;
611 p_transactionid2_prompt:=null;
612 p_transactionid3_prompt:=null;
613 p_transactionid4_prompt:=null;
614 p_transactionid5_prompt:=null;
615 p_transactionid6_prompt:=null;
616 p_transactionid7_prompt:=null;
617 p_transactionid8_prompt:=null;
618 p_transactionid9_prompt:=null;
619 p_transactionid10_prompt:=null;
620 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
621 trace
622 (p_msg => 'no transaction identifiers, return'
623 ,p_level => C_LEVEL_PROCEDURE
624 ,p_module => l_log_module);
625 END IF;
626 return 0;
627 END IF;
628
629 -----------------------------------------------------------------------------
630 -- building the string to be added to the SELECT clause
631 -----------------------------------------------------------------------------
635 l_col_array(i).f3||' value_'||TO_CHAR(i);
632 l_col_string:=l_col_array(1).f3||' value_1';
633 FOR i IN 2..l_col_array.count LOOP
634 l_col_string := l_col_string || ',' ||
636 END LOOP;
637
638 -----------------------------------------------------------------------------
639 -- Following tests whether the view and columns are defined in the data base
640 -----------------------------------------------------------------------------
641 IF l_reporting_view_name IS NOT NULL THEN
642 BEGIN
643 ----------------------------------------------------------------------
644 -- build and execute a dummy query if the view name is defined for
645 -- the class
646 ----------------------------------------------------------------------
647 l_sql_string :=
648 ' SELECT' ||
649 ' NULL dummy, ' ||
650 l_col_string ||
651 ' FROM' ||
652 ' dual dual' ||
653 l_view_name ||
654 ' WHERE' ||
655 ' ROWNUM = 1';
656 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
657 trace
658 (p_msg => 'dummy query:'||substr(l_sql_string, 1, 1000)
659 ,p_level => C_LEVEL_STATEMENT
660 ,p_module => l_log_module);
661 trace
662 (p_msg => 'query:'||substr(l_sql_string, 1001, 1000)
663 ,p_level => C_LEVEL_STATEMENT
664 ,p_module => l_log_module);
665 trace
666 (p_msg => 'query:'||substr(l_sql_string, 2001, 1000)
667 ,p_level => C_LEVEL_STATEMENT
668 ,p_module => l_log_module);
669 trace
670 (p_msg => 'query:'||substr(l_sql_string, 3001, 990)
671 ,p_level => C_LEVEL_STATEMENT
672 ,p_module => l_log_module);
673 END IF;
674
675 EXECUTE IMMEDIATE l_sql_string;
676 EXCEPTION
677 WHEN OTHERS THEN
678 ----------------------------------------------------------------------
679 -- if the above query raises an exception following clears the FROM
680 -- and WHERE strings and creates the error to be displayed to the user
681 ----------------------------------------------------------------------
682 l_col_array := l_null_col_array;
683 l_col_string := NULL;
684 l_col_array(1).f1 := '''Error''';
685 l_col_array(1).f3 := '''Problem with Transaction Identifier View''';
686 l_view_name := NULL;
687 l_join_string := NULL;
688 l_col_string:=l_col_array(1).f3||' value_1';
689 FOR i IN 2..l_col_array.count LOOP
690 l_col_string := l_col_string || ',' ||
691 l_col_array(i).f3||' value_char_'||TO_CHAR(i);
692 END LOOP;
693 END;
694 END IF;
695
696 -----------------------------------------------------------------------------
697 -- build the actual query
698 -----------------------------------------------------------------------------
699 l_sql_string :=
700 ' SELECT ' ||
701 l_col_string ||
702 ' FROM' ||
703 ' xla_events evt' ||
704 ' ,xla_transaction_entities ent' ||
705 l_view_name ||
706 ' WHERE' ||
707 ' evt.event_id = :1 ' ||
708 ' and evt.application_id = :2 ' ||
709 ' and ent.entity_id = evt.entity_id' ||
710 ' and ent.application_id = evt.application_id ' ||
711 l_join_string;
712 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
713 trace
714 (p_msg => 'query:'||substr(l_sql_string, 1, 1000)
715 ,p_level => C_LEVEL_STATEMENT
716 ,p_module => l_log_module);
717 trace
718 (p_msg => 'query:'||substr(l_sql_string, 1001, 1000)
719 ,p_level => C_LEVEL_STATEMENT
720 ,p_module => l_log_module);
721 trace
722 (p_msg => 'query:'||substr(l_sql_string, 2001, 1000)
723 ,p_level => C_LEVEL_STATEMENT
724 ,p_module => l_log_module);
725 trace
726 (p_msg => 'query:'||substr(l_sql_string, 3001, 990)
727 ,p_level => C_LEVEL_STATEMENT
728 ,p_module => l_log_module);
729 END IF;
730
731 begin
732 EXECUTE IMMEDIATE l_sql_string into
733 p_transactionid1_value,
734 p_transactionid2_value,
735 p_transactionid3_value,
736 p_transactionid4_value,
737 p_transactionid5_value,
738 p_transactionid6_value,
739 p_transactionid7_value,
740 p_transactionid8_value,
741 p_transactionid9_value,
742 p_transactionid10_value
743 USING p_event_id
744 ,p_application_id;
745 exception
746 when NO_DATA_FOUND then
747 -- if no data found, we just show the prompt, no data
748 null;
752 (p_msg => 'end with too many rows'
749 when TOO_MANY_ROWS THEN
750 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
751 trace
753 ,p_level => C_LEVEL_PROCEDURE
754 ,p_module => l_log_module);
755 END IF;
756 return 1;
757 end;
758
759 p_transactionid1_prompt:=l_col_array(1).f1;
760 p_transactionid2_prompt:=l_col_array(2).f1;
761 p_transactionid3_prompt:=l_col_array(3).f1;
762 p_transactionid4_prompt:=l_col_array(4).f1;
763 p_transactionid5_prompt:=l_col_array(5).f1;
764 p_transactionid6_prompt:=l_col_array(6).f1;
765 p_transactionid7_prompt:=l_col_array(7).f1;
766 p_transactionid8_prompt:=l_col_array(8).f1;
767 p_transactionid9_prompt:=l_col_array(9).f1;
768 p_transactionid10_prompt:=l_col_array(10).f1;
769
770
771 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
772 trace
773 (p_msg => 'p_transactionid1_prompt = '||p_transactionid1_prompt
774 ,p_level => C_LEVEL_PROCEDURE
775 ,p_module => l_log_module);
776 trace
777 (p_msg => 'p_transactionid1_value = '||p_transactionid1_value
778 ,p_level => C_LEVEL_PROCEDURE
779 ,p_module => l_log_module);
780 trace
781 (p_msg => 'p_transactionid2_prompt = '||p_transactionid2_prompt
782 ,p_level => C_LEVEL_PROCEDURE
783 ,p_module => l_log_module);
784 trace
785 (p_msg => 'p_transactionid2_value = '||p_transactionid2_value
786 ,p_level => C_LEVEL_PROCEDURE
787 ,p_module => l_log_module);
788 trace
789 (p_msg => 'p_transactionid3_prompt = '||p_transactionid3_prompt
790 ,p_level => C_LEVEL_PROCEDURE
791 ,p_module => l_log_module);
792 trace
793 (p_msg => 'p_transactionid3_value = '||p_transactionid3_value
794 ,p_level => C_LEVEL_PROCEDURE
795 ,p_module => l_log_module);
796 trace
797 (p_msg => 'p_transactionid4_prompt = '||p_transactionid4_prompt
798 ,p_level => C_LEVEL_PROCEDURE
799 ,p_module => l_log_module);
800 trace
801 (p_msg => 'p_transactionid4_value = '||p_transactionid4_value
802 ,p_level => C_LEVEL_PROCEDURE
803 ,p_module => l_log_module);
804 trace
805 (p_msg => 'p_transactionid5_prompt = '||p_transactionid5_prompt
806 ,p_level => C_LEVEL_PROCEDURE
807 ,p_module => l_log_module);
808 trace
809 (p_msg => 'p_transactionid5_value = '||p_transactionid5_value
810 ,p_level => C_LEVEL_PROCEDURE
811 ,p_module => l_log_module);
812 trace
813 (p_msg => 'p_transactionid6_prompt = '||p_transactionid6_prompt
814 ,p_level => C_LEVEL_PROCEDURE
815 ,p_module => l_log_module);
816 trace
817 (p_msg => 'p_transactionid6_value = '||p_transactionid6_value
818 ,p_level => C_LEVEL_PROCEDURE
819 ,p_module => l_log_module);
820 trace
821 (p_msg => 'p_transactionid7_prompt = '||p_transactionid7_prompt
822 ,p_level => C_LEVEL_PROCEDURE
823 ,p_module => l_log_module);
824 trace
825 (p_msg => 'p_transactionid7_value = '||p_transactionid7_value
826 ,p_level => C_LEVEL_PROCEDURE
827 ,p_module => l_log_module);
828 trace
829 (p_msg => 'p_transactionid8_prompt = '||p_transactionid8_prompt
830 ,p_level => C_LEVEL_PROCEDURE
831 ,p_module => l_log_module);
832 trace
833 (p_msg => 'p_transactionid8_value = '||p_transactionid8_value
834 ,p_level => C_LEVEL_PROCEDURE
835 ,p_module => l_log_module);
836 trace
837 (p_msg => 'p_transactionid9_prompt = '||p_transactionid9_prompt
838 ,p_level => C_LEVEL_PROCEDURE
839 ,p_module => l_log_module);
840 trace
841 (p_msg => 'p_transactionid9_value = '||p_transactionid9_value
842 ,p_level => C_LEVEL_PROCEDURE
843 ,p_module => l_log_module);
844 trace
845 (p_msg => 'p_transactionid10_prompt = '||p_transactionid10_prompt
846 ,p_level => C_LEVEL_PROCEDURE
847 ,p_module => l_log_module);
848 trace
849 (p_msg => 'p_transactionid10_value = '||p_transactionid10_value
850 ,p_level => C_LEVEL_PROCEDURE
851 ,p_module => l_log_module);
852 trace
853 (p_msg => 'END of procedure GET_TRANSACTION_IDENTIFIERS'
854 ,p_level => C_LEVEL_PROCEDURE
855 ,p_module => l_log_module);
856 END IF;
857 return 0;
858 EXCEPTION
859 WHEN OTHERS THEN
860 xla_exceptions_pkg.raise_message
861 (p_location => 'xla_transaction_id_pkg.get_transaction_identifiers');
862 end get_transaction_identifiers;
863
864
865 --=============================================================================
866 -- *********** Initialization routine **********
867 --=============================================================================
868
869 --=============================================================================
870 --
871 --
872 --
873 --
874 --
875 --
876 --
877 --
878 --
879 --
880 -- Following code is executed when the package body is referenced for the first
881 -- time
882 --
883 --
884 --
885 --
886 --
887 --
888 --
889 --
890 --
891 --
892 --
893 --
894 --=============================================================================
895
896 BEGIN
897 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
898 g_log_enabled := fnd_log.test
899 (log_level => g_log_level
900 ,module => C_DEFAULT_MODULE);
901
902 IF NOT g_log_enabled THEN
903 g_log_level := C_LEVEL_LOG_DISABLED;
904 END IF;
905
906 END xla_transaction_id_pkg;