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