[Home] [Help]
PACKAGE BODY: APPS.XLA_REPORT_UTILITY_PKG
Source
1 PACKAGE BODY xla_report_utility_pkg AS
2 -- $Header: xlarputl.pkb 120.11 2006/07/18 06:29:01 vinaykum noship $
3 /*===========================================================================+
4 | Copyright (c) 2003 Oracle Corporation BelmFont, California, USA |
5 | ALL rights reserved. |
6 +============================================================================+
7 | FILENAME |
8 | xlarputl.pkb |
9 | |
10 | PACKAGE NAME |
11 | xla_report_utility_pkg |
12 | |
13 | DESCRIPTION |
14 | Package body. This provides routines that support reports |
15 | |
16 | HISTORY |
17 | 04/15/2005 V. Kumar Created |
18 | 04/27/2005 V. Kumar Bug:4309818 increased the size of t_rec.f2 |
19 | 06/03/2005 V. Kumar Updated get_transaction_id to include NULL |
20 | columns for undefined user trx identifier |
21 | 12/23/2005 V. Kumar Added function get_transaction_id |
22 | 06/23/2006 V. Kumar Added function get_conc_segments |
23 +===========================================================================*/
24
25 --=============================================================================
26 -- **************** declaraions ********************
27 --=============================================================================
28
29 TYPE t_rec IS RECORD
30 (f1 VARCHAR2(80)
31 ,f2 VARCHAR2(80));
32 TYPE t_array IS TABLE OF t_rec INDEX BY BINARY_INTEGER;
33
34 --=============================================================================
35 -- ************** forward declaraions ******************
36 --=============================================================================
37 -- none
38
39 --=============================================================================
40 -- *********** Local Trace Routine **********
41 --=============================================================================
42 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
43 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
44 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
45 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
46 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
47 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
48
49 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
50 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_report_utility_pkg';
51
52 g_log_level NUMBER;
53 g_log_enabled BOOLEAN;
54
55 PROCEDURE trace
56 (p_msg IN VARCHAR2
57 ,p_level IN NUMBER
58 ,p_module IN VARCHAR2) IS
59 BEGIN
60 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
61 fnd_log.message(p_level, NVL(p_module,C_DEFAULT_MODULE));
62 ELSIF p_level >= g_log_level THEN
63 fnd_log.string(p_level, NVL(p_module,C_DEFAULT_MODULE), p_msg);
64 END IF;
65
66 EXCEPTION
67 WHEN xla_exceptions_pkg.application_exception THEN
68 RAISE;
69 WHEN OTHERS THEN
70 xla_exceptions_pkg.raise_message
71 (p_location => 'xla_report_utility_pkg.trace');
72 END trace;
73
74 --=============================================================================
75 -- *********** public procedures and functions **********
76 --=============================================================================
77 --=============================================================================
78 --
79 --
80 --
81 --
82 --
83 --
84 --
85 --
86 --
87 --
88 -- Following are public routines
89 --
90 -- 1. get_transaction_id
91 -- 2. get_acct_qualifier_segs
92 -- 3. get_ccid_desc
93 -- 4. clob_to_file
94 -- 5. get_anc_filter
95 -- 6. get_conc_segments
96 --
97 --
98 --
99 --
100 --
101 --
102 --
103 --
104 --
105 --
106 --=============================================================================
107
108
109 --=============================================================================
110 --
111 --
112 --
113 --=============================================================================
114 PROCEDURE get_transaction_id
115 (p_application_id IN INTEGER
116 ,p_entity_code IN VARCHAR2
117 ,p_event_class_code IN VARCHAR2
118 ,p_reporting_view_name IN VARCHAR2
119 ,p_select_str OUT NOCOPY VARCHAR2
120 ,p_from_str OUT NOCOPY VARCHAR2
121 ,p_where_str OUT NOCOPY VARCHAR2) IS
122 CURSOR cols_csr IS
123 (SELECT xid.transaction_id_col_name_1 trx_col_1
124 ,xid.transaction_id_col_name_2 trx_col_2
125 ,xid.transaction_id_col_name_3 trx_col_3
126 ,xid.transaction_id_col_name_4 trx_col_4
127 ,xid.source_id_col_name_1 src_col_1
128 ,xid.source_id_col_name_2 src_col_2
129 ,xid.source_id_col_name_3 src_col_3
130 ,xid.source_id_col_name_4 src_col_4
131 ,xem.column_name column_name
132 ,xem.column_title PROMPT
133 ,utc.data_type data_type
134 FROM xla_entity_id_mappings xid
135 ,xla_event_mappings_vl xem
136 ,user_tab_columns utc
137 WHERE xid.application_id = p_application_id
138 AND xid.entity_code = p_entity_code
139 AND xem.application_id = p_application_id
140 AND xem.entity_code = p_entity_code
141 AND xem.event_class_code = p_event_class_code
142 AND utc.table_name = p_reporting_view_name
143 AND utc.column_name = xem.column_name)
144 ORDER BY xem.user_sequence;
145
146 l_col_array t_array;
147 l_null_col_array t_array;
148 l_col_string VARCHAR2(4000) := NULL;
149 l_view_name VARCHAR2(800);
150 l_join_string VARCHAR2(4000) := NULL;
151 l_sql_string VARCHAR2(4000) := NULL;
152 l_index INTEGER;
153 l_outerjoin VARCHAR2(30);
154 l_log_module VARCHAR2(240);
155 BEGIN
156 IF g_log_enabled THEN
157 l_log_module := C_DEFAULT_MODULE||'.get_transaction_id';
158 END IF;
159 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
160 trace
161 (p_msg => 'BEGIN of procedure GET_TRANSACTION_ID'
162 ,p_level => C_LEVEL_PROCEDURE
163 ,p_module => l_log_module);
164 trace
165 (p_msg => 'p_application_id = '||p_application_id
166 ,p_level => C_LEVEL_PROCEDURE
167 ,p_module => l_log_module);
168 trace
169 (p_msg => 'p_entity_code = '||p_entity_code
170 ,p_level => C_LEVEL_PROCEDURE
171 ,p_module => l_log_module);
172 trace
173 (p_msg => 'p_event_class_code = '||p_event_class_code
174 ,p_level => C_LEVEL_PROCEDURE
175 ,p_module => l_log_module);
176 trace
177 (p_msg => 'p_reporting_view_name = '||p_reporting_view_name
178 ,p_level => C_LEVEL_PROCEDURE
179 ,p_module => l_log_module);
180 END IF;
181 ----------------------------------------------------------------------------
182 -- creating a dummy array that contains "NULL" strings
183 ----------------------------------------------------------------------------
184 FOR i IN 1..10 LOOP
185 l_null_col_array(i).f1 := 'NULL';
186 l_null_col_array(i).f2 := 'NULL';
187
188 END LOOP;
189 ----------------------------------------------------------------------------
190 -- initiating the array that contains name of the columns to be selected
191 -- from the TID View.
192 ----------------------------------------------------------------------------
193 l_col_array := l_null_col_array;
194
195 ----------------------------------------------------------------------------
196 -- creating SELECT,FROM and WHERE clause strings when the reporting view is
197 -- defined for an Event Class.
198 ----------------------------------------------------------------------------
199 IF p_reporting_view_name IS NOT NULL THEN
200 -------------------------------------------------------------------------
201 -- creating string to be added to FROM clause
202 -------------------------------------------------------------------------
203 l_view_name := ',' || p_reporting_view_name || ' TIV';
204 l_index := 0;
205 FOR c1 IN cols_csr LOOP
206 l_index := l_index + 1;
207
208 ----------------------------------------------------------------------
209 -- creating string to be added to WHERE clause
210 ----------------------------------------------------------------------
211 IF l_index = 1 THEN
212 -------------------------------------------------------------------
213 -- Bug 3389175
214 -- Following logic is build to make sure all events are reported
215 -- if debug is enabled evenif there is no data for the event in the
216 -- transaction id view.
217 -- if log enabled then
218 -- outer join to TID view
219 -- endif
220 -------------------------------------------------------------------
221 IF g_log_level <> C_LEVEL_LOG_DISABLED THEN
222 l_outerjoin := '(+)';
223 ELSE
224 l_outerjoin := NULL;
225 END IF;
226
227 IF c1.trx_col_1 IS NOT NULL THEN
228 l_join_string := l_join_string ||
229 ' AND TIV.'|| c1.trx_col_1 ||l_outerjoin ||
230 ' = ENT.'|| c1.src_col_1;
231 END IF;
232 IF c1.trx_col_2 IS NOT NULL THEN
233 l_join_string := l_join_string ||
234 ' AND TIV.'|| c1.trx_col_2 ||l_outerjoin ||
235 ' = ENT.'|| c1.src_col_2;
236 END IF;
237 IF c1.trx_col_3 IS NOT NULL THEN
238 l_join_string := l_join_string ||
239 ' AND TIV.'|| c1.trx_col_3 ||l_outerjoin ||
240 ' = ENT.'|| c1.src_col_3;
241 END IF;
242 IF c1.trx_col_4 IS NOT NULL THEN
243 l_join_string := l_join_string ||
244 ' AND TIV.'|| c1.trx_col_4 ||l_outerjoin ||
245 ' = ENT.'|| c1.src_col_4;
246 END IF;
247 END IF;
248
249 ----------------------------------------------------------------------
250 -- getting the PROMPTs to be displayed
251 ----------------------------------------------------------------------
252 l_col_array(l_index).f1 := ''''||c1.PROMPT||'''';
253
254 ----------------------------------------------------------------------
255 -- getting the columns to be displayed
256 ----------------------------------------------------------------------
257 IF c1.data_type = 'VARCHAR2' THEN
258 l_col_array(l_index).f2 := 'TIV.'|| c1.column_name;
259
260 ELSIF c1.data_type = 'DATE' THEN
261 l_col_array(l_index).f2 := 'to_char(TIV.'|| c1.column_name
262 ||',''YYYY-MM-DD"T"hh:mi:ss'')';
263 ELSE
264 l_col_array(l_index).f2 := 'to_char(TIV.'|| c1.column_name||')';
265 END IF;
266 END LOOP;
267 END IF;
268
269 ----------------------------------------------------------------------------
270 -- building the string to be added to the SELECT clause
271 ----------------------------------------------------------------------------
272 FOR i IN 1..l_col_array.count LOOP
273 l_col_string := l_col_string || ',' ||
274 l_col_array(i).f1||' USER_TRX_IDENTIFIER_NAME_' ||TO_CHAR(i)||','||
275 l_col_array(i).f2||' USER_TRX_IDENTIFIER_VALUE_' ||TO_CHAR(i);
276 END LOOP;
277 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
278 trace
279 (p_msg => 'l_col_string = '||l_col_string
280 ,p_level => C_LEVEL_STATEMENT
281 ,p_module => l_log_module);
282 END IF;
283 -----------------------------------------------------------------------------
284 -- Following tests whether the view and columns are defined in the data base
285 -----------------------------------------------------------------------------
286 IF p_reporting_view_name IS NOT NULL THEN
287 BEGIN
288 ----------------------------------------------------------------------
289 -- build and execute a dummy query if the view name is defined for
290 -- the class
291 -- NOTE: following never fails because the cursor joins to
292 -- user_tab_columns table that will make sure that view and column
293 -- names fetched exists. This can beremoved unless we decide to go
294 -- for outerjoin on this table.
295 ----------------------------------------------------------------------
296 l_sql_string :=
297 ' SELECT ' ||
298 ' NULL dummy ' ||
299 l_col_string ||
300 ' FROM ' ||
301 ' DUAL dual ' ||
302 l_view_name ||
303 ' WHERE ROWNUM = 1 ' ;
304
305
306 EXECUTE IMMEDIATE l_sql_string;
307
308 EXCEPTION
309 WHEN OTHERS THEN
310 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
311 trace
312 (p_msg => 'Technical Warning: There seems to a problem in retreiving '||
313 'transaction identifiers from '||p_reporting_view_name
314 ,p_level => C_LEVEL_EXCEPTION
315 ,p_module => l_log_module);
316 END IF;
317
318 ----------------------------------------------------------------------
319 -- if the above query raises an exception following clears the FROM
320 -- and WHERE strings and creates the error to be displayed to the user
321 ----------------------------------------------------------------------
322 l_col_array := l_null_col_array;
323 l_col_string := NULL;
324 l_col_array(1).f1 := '''Error''';
325 l_col_array(1).f2 := '''Problem with Transaction Identifier View''';
326 l_view_name := NULL;
327 l_join_string := NULL;
328
329 FOR i IN 1..l_col_array.count LOOP
330 l_col_string := l_col_string || ',' ||
331 l_col_array(i).f1||' USER_TRX_IDENTIFIER_NAME_' ||TO_CHAR(i)||','||
332 l_col_array(i).f2||' USER_TRX_IDENTIFIER_VALUE_' ||TO_CHAR(i);
333 END LOOP;
334
335 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
336 trace
337 (p_msg => 'l_col_string = '||l_col_string
338 ,p_level => C_LEVEL_STATEMENT
339 ,p_module => l_log_module);
340 END IF;
341 END;
342 END IF;
343
344 p_select_str := l_col_string;
345 p_from_str := l_view_name;
346 p_where_str := l_join_string;
347
348 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
349 trace
350 (p_msg => 'p_select_str = '||p_select_str
351 ,p_level => C_LEVEL_PROCEDURE
352 ,p_module => l_log_module);
353 trace
354 (p_msg => 'p_from_str = '||p_from_str
355 ,p_level => C_LEVEL_PROCEDURE
356 ,p_module => l_log_module);
357 trace
358 (p_msg => 'p_where_str = '||p_where_str
359 ,p_level => C_LEVEL_PROCEDURE
360 ,p_module => l_log_module);
361 trace
362 (p_msg => 'End of procedure GET_TRANSACTION_ID'
363 ,p_level => C_LEVEL_PROCEDURE
364 ,p_module => l_log_module);
365 END IF;
366
367 EXCEPTION
368 WHEN OTHERS THEN
369 xla_exceptions_pkg.raise_message
370 (p_location => 'xla_report_utility_pkg.get_transaction_id ');
371 END get_transaction_id;
372
373 --=============================================================================
374 --
375 --
376 --
377 --=============================================================================
378 PROCEDURE get_acct_qualifier_segs
379 (p_coa_id IN NUMBER
380 ,p_balance_segment OUT NOCOPY VARCHAR2
381 ,p_account_segment OUT NOCOPY VARCHAR2
382 ,p_cost_center_segment OUT NOCOPY VARCHAR2
383 ,p_management_segment OUT NOCOPY VARCHAR2
384 ,p_intercompany_segment OUT NOCOPY VARCHAR2) IS
385
386
387 l_balance_segment VARCHAR2(80);
388 l_account_segment VARCHAR2(80);
389 l_cost_center_segment VARCHAR2(80);
390 l_management_segment VARCHAR2(80);
391 l_intercompany_segment VARCHAR2(80);
392 l_log_module VARCHAR2(240);
393
394 BEGIN
395 IF g_log_enabled THEN
396 l_log_module := C_DEFAULT_MODULE||'.get_acct_qualifier_segs';
397 END IF;
398 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
399 trace
400 (p_msg => 'BEGIN of procedure GET_ACCT_QUALIFIER_SEGS'
401 ,p_level => C_LEVEL_PROCEDURE
402 ,p_module => l_log_module);
403 trace
404 (p_msg => 'p_coa_id = '||p_coa_id
405 ,p_level => C_LEVEL_PROCEDURE
406 ,p_module => l_log_module);
407 END IF;
408
409 BEGIN
410 SELECT application_column_name
411 INTO l_balance_segment
412 FROM fnd_segment_attribute_values
413 WHERE application_id = 101
414 AND id_flex_code = 'GL#'
415 AND id_flex_num = p_coa_id
416 AND attribute_value = 'Y'
417 AND segment_attribute_type = 'GL_BALANCING';
418 EXCEPTION
419 WHEN no_data_found THEN
420 l_balance_segment := 'NULL';
421 END;
422
423 BEGIN
424 SELECT application_column_name
425 INTO l_account_segment
426 FROM fnd_segment_attribute_values
427 WHERE application_id = 101
428 AND id_flex_code = 'GL#'
429 AND id_flex_num = p_coa_id
430 AND attribute_value = 'Y'
431 AND segment_attribute_type = 'GL_ACCOUNT';
432 EXCEPTION
433 WHEN no_data_found THEN
434 l_account_segment := 'NULL';
435 END;
436
437 BEGIN
438 SELECT application_column_name
439 INTO l_cost_center_segment
440 FROM fnd_segment_attribute_values
441 WHERE application_id = 101
442 AND id_flex_code = 'GL#'
443 AND id_flex_num = p_coa_id
444 AND attribute_value = 'Y'
445 AND segment_attribute_type = 'FA_COST_CTR';
446 EXCEPTION
447 WHEN no_data_found THEN
448 l_cost_center_segment := 'NULL';
449 END;
450
451 BEGIN
452 SELECT application_column_name
453 INTO l_management_segment
454 FROM fnd_segment_attribute_values
455 WHERE application_id = 101
456 AND id_flex_code = 'GL#'
457 AND id_flex_num = p_coa_id
458 AND attribute_value = 'Y'
459 AND segment_attribute_type = 'GL_MANAGEMENT';
460 EXCEPTION
461 WHEN no_data_found THEN
462 l_management_segment := 'NULL';
463 END;
464
465 BEGIN
466 SELECT application_column_name
467 INTO l_intercompany_segment
468 FROM fnd_segment_attribute_values
469 WHERE application_id = 101
470 AND id_flex_code = 'GL#'
471 AND id_flex_num = p_coa_id
472 AND attribute_value = 'Y'
473 AND segment_attribute_type = 'GL_INTERCOMPANY';
474 EXCEPTION
475 WHEN no_data_found THEN
476 l_intercompany_segment := 'NULL';
477 END;
478
479 p_intercompany_segment := l_intercompany_segment;
480 p_management_segment := l_management_segment;
481 p_cost_center_segment := l_cost_center_segment;
482 p_account_segment := l_account_segment;
483 p_balance_segment := l_balance_segment;
484
485
486 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
487 trace
488 (p_msg => 'p_intercompany_segment = '||p_intercompany_segment
489 ,p_level => C_LEVEL_PROCEDURE
490 ,p_module => l_log_module);
491 trace
492 (p_msg => 'p_management_segment = '||p_management_segment
493 ,p_level => C_LEVEL_PROCEDURE
494 ,p_module => l_log_module);
495 trace
496 (p_msg => 'p_cost_center_segment = '||p_cost_center_segment
497 ,p_level => C_LEVEL_PROCEDURE
498 ,p_module => l_log_module);
499 trace
500 (p_msg => 'p_account_segment = '||p_account_segment
501 ,p_level => C_LEVEL_PROCEDURE
502 ,p_module => l_log_module);
503 trace
504 (p_msg => 'p_balance_segment = '||p_balance_segment
505 ,p_level => C_LEVEL_PROCEDURE
506 ,p_module => l_log_module);
507 trace
508 (p_msg => 'End of procedure GET_ACCT_QUALIFIER_SEGS'
509 ,p_level => C_LEVEL_PROCEDURE
510 ,p_module => l_log_module);
511 END IF;
512 EXCEPTION
513 WHEN OTHERS THEN
514 xla_exceptions_pkg.raise_message
515 (p_location => 'xla_report_utility_pkg.get_acct_qualifier_segs');
516 END get_acct_qualifier_segs;
517
518
519 --=============================================================================
520 --
521 -- Function to get concenated description of accounting flex field
522 --
523 --=============================================================================
524 FUNCTION get_ccid_desc
525 (p_coa_id IN NUMBER
526 ,p_ccid IN NUMBER)
527 RETURN VARCHAR2 IS
528 l_ccid_desc VARCHAR2(2000);
529 l_log_module VARCHAR2(240);
530 BEGIN
531 IF g_log_enabled THEN
532 l_log_module := C_DEFAULT_MODULE||'.get_ccid_desc';
533 END IF;
534 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
535 trace
536 (p_msg => 'BEGIN of procedure GET_CCID_DESC'
537 ,p_level => C_LEVEL_PROCEDURE
538 ,p_module => l_log_module);
539 trace
540 (p_msg => 'p_coa_id = '||p_coa_id
541 ,p_level => C_LEVEL_PROCEDURE
542 ,p_module => l_log_module);
543 trace
544 (p_msg => 'p_ccid = '||p_ccid
545 ,p_level => C_LEVEL_PROCEDURE
546 ,p_module => l_log_module);
547 END IF;
548
549 IF fnd_flex_keyval.validate_ccid
550 ('SQLGL','GL#',p_coa_id,p_ccid) = TRUE
551 THEN
552 l_ccid_desc := fnd_flex_keyval.concatenated_descriptions();
553
554 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
555 trace
556 (p_msg => 'l_ccid_desc = '||l_ccid_desc
557 ,p_level => C_LEVEL_PROCEDURE
558 ,p_module => l_log_module);
559 trace
560 (p_msg => 'END of procedure GET_CCID_DESC'
561 ,p_level => C_LEVEL_PROCEDURE
562 ,p_module => l_log_module);
566 ELSE
563 END IF;
564
565 RETURN l_ccid_desc;
567 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
568 trace
569 (p_msg => 'l_ccid_desc = '
570 ,p_level => C_LEVEL_PROCEDURE
571 ,p_module => l_log_module);
572 trace
573 (p_msg => 'END of procedure GET_CCID_DESC'
574 ,p_level => C_LEVEL_PROCEDURE
575 ,p_module => l_log_module);
576 END IF;
577
578 RETURN NULL;
579 END IF;
580 EXCEPTION
581 WHEN OTHERS THEN
582 xla_exceptions_pkg.raise_message
583 (p_location => 'xla_report_utility_pkg.get_ccid_desc');
584 END get_ccid_desc;
585
586
587 --=============================================================================
588 --
589 -- Body for the procedure clob_to_file
590 --
591 --=============================================================================
592 PROCEDURE clob_to_file
593 (p_xml_clob IN CLOB) IS
594
595 l_clob_size NUMBER;
596 l_offset NUMBER;
597 l_chunk_size INTEGER;
598 l_chunk VARCHAR2(32767);
599 l_log_module VARCHAR2(240);
600
601 BEGIN
602 IF g_log_enabled THEN
603 l_log_module := C_DEFAULT_MODULE||'.clob_to_file';
604 END IF;
605 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
606 trace
607 (p_msg => 'BEGIN of procedure CLOB_TO_FILE'
608 ,p_level => C_LEVEL_PROCEDURE
609 ,p_module => l_log_module);
610 END IF;
611
612 l_clob_size := dbms_lob.getlength(p_xml_clob);
613
614 IF (l_clob_size = 0) THEN
615 RETURN;
616 END IF;
617 l_offset := 1;
618 l_chunk_size := 3000;
619
620 WHILE (l_clob_size > 0) LOOP
621 l_chunk := dbms_lob.substr (p_xml_clob, l_chunk_size, l_offset);
622 fnd_file.put
623 (which => fnd_file.output
624 ,buff => l_chunk);
625
626 l_clob_size := l_clob_size - l_chunk_size;
627 l_offset := l_offset + l_chunk_size;
628 END LOOP;
629
630 fnd_file.new_line(fnd_file.output,1);
631
632 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
633 trace
634 (p_msg => 'END of procedure CLOB_TO_FILE'
635 ,p_level => C_LEVEL_PROCEDURE
636 ,p_module => l_log_module);
637 END IF;
638
639 EXCEPTION
640 WHEN OTHERS THEN
641 xla_exceptions_pkg.raise_message
642 (p_location => 'xla_report_utility_pkg.clob_to_file');
643 END clob_to_file;
644
645
646 --=============================================================================
647 --
648 -- Function to get condtions based on analytical detail values
649 --
650 --=============================================================================
651 FUNCTION get_anc_filter
652 (p_anc_level IN VARCHAR2
653 ,p_table_alias IN VARCHAR2
654 ,p_anc_detail_code IN VARCHAR2
655 ,p_anc_detail_value IN VARCHAR2)
656 RETURN VARCHAR2 IS
657 l_column_name VARCHAR2(80);
658 l_string VARCHAR2(2000);
659 l_log_module VARCHAR2(240);
660
661 BEGIN
662 IF g_log_enabled THEN
663 l_log_module := C_DEFAULT_MODULE||'.get_anc_filter';
664 END IF;
665 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
666 trace
667 (p_msg => 'BEGIN of function GET_ANC_FILTER'
668 ,p_level => C_LEVEL_PROCEDURE
669 ,p_module => l_log_module);
670 trace
671 (p_msg => 'p_anc_level = '||p_anc_level
672 ,p_level => C_LEVEL_PROCEDURE
673 ,p_module => l_log_module);
674 trace
675 (p_msg => 'p_table_alias = '||p_table_alias
676 ,p_level => C_LEVEL_PROCEDURE
677 ,p_module => l_log_module);
678 trace
679 (p_msg => 'p_anc_detail_code = '||p_anc_detail_code
680 ,p_level => C_LEVEL_PROCEDURE
681 ,p_module => l_log_module);
682 trace
683 (p_msg => 'p_anc_detail_value = '||p_anc_detail_value
684 ,p_level => C_LEVEL_PROCEDURE
685 ,p_module => l_log_module);
686 END IF;
687
688 SELECT
689 CASE data_type_code
690 WHEN 'C' THEN 'ADV.ANALYTICAL_DETAIL_CHAR_'||TO_CHAR(grouping_order)
691 WHEN 'D' THEN 'ADV.ANALYTICAL_DETAIL_DATE_'||TO_CHAR(grouping_order)
692 WHEN 'N' THEN 'ADV.ANALYTICAL_DETAIL_NUMBER_'||TO_CHAR(grouping_order)
693 ELSE NULL
694 END CASE
695 INTO l_column_name
696 FROM xla_analytical_dtls_b
697 WHERE analytical_Detail_code = p_anc_detail_code;
698
699 IF p_anc_level = 'H' THEN
700 l_string := ' and exist ( '||
701 ' select 1 from xla_ae_header_details ahd, xla_analytical_dtl_vals adv '||
702 ' where ahd.ae_header_id = '||p_table_alias||'.ae_header_id '||
703 ' and adv.analytical_detail_value_id = ahd.analytical_detail_value_id '||
704 ' and adv.'||l_column_name||' = '''||p_anc_detail_value||''''||
705 ' )';
706 ELSIF p_anc_level = 'L' THEN
707 l_string := ' and exist ( '||
708 ' select 1 from xla_ae_line_details ald, xla_analytical_dtl_vals adv '||
709 ' where ald.ae_header_id = '||p_table_alias||'.ae_header_id '||
710 ' and ald.ae_line_num = '||p_table_alias||'.ae_line_num '||
711 ' and adv.analytical_detail_value_id = ald.analytical_detail_value_id '||
715
712 ' and adv.'||l_column_name||' = '''||p_anc_detail_value||''''||
713 ' )';
714 END IF;
716 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
717 trace
718 (p_msg => 'END of function GET_ANC_FILTER'
719 ,p_level => C_LEVEL_PROCEDURE
720 ,p_module => l_log_module);
721 END IF;
722
723 RETURN l_string;
724
725 EXCEPTION
726 WHEN OTHERS THEN
727 xla_exceptions_pkg.raise_message
728 (p_location => 'xla_report_utility_pkg.get_anc_filter');
729 END get_anc_filter;
730
731
732 --=============================================================================
733 --
734 --
735 --
736 --=============================================================================
737 FUNCTION is_primary_ledger (p_ledger_id IN NUMBER)
738 RETURN NUMBER IS
739
740 l_ledger_id gl_ledgers.ledger_id%type;
741
742 BEGIN
743
744 select ledger_id
745 into l_ledger_id
746 from gl_ledgers
747 where ledger_category_code = 'PRIMARY'
748 and ledger_id = p_ledger_id
749 and rownum = 1;
750
751 RETURN l_ledger_id;
752
753 EXCEPTION
754 WHEN NO_DATA_FOUND THEN
755 RETURN NULL;
756 WHEN OTHERS THEN
757 xla_exceptions_pkg.raise_message
758 (p_location => 'xla_report_utility_pkg.unravel_ledger (fn)');
759 END is_primary_ledger;
760
761
762 --=============================================================================
763 --
764 --
765 --
766 --=============================================================================
767 FUNCTION get_ledger_id (p_ledger_id IN NUMBER)
768 RETURN NUMBER IS
769
770 l_object_type_code gl_ledgers.object_type_code%TYPE;
771 l_ledger_id gl_ledgers.ledger_id%TYPE;
772 l_ledger_id_out gl_ledgers.ledger_id%TYPE;
773
774 BEGIN
775
776 l_object_type_code := xla_report_utility_pkg.
777 get_ledger_object_type(p_ledger_id);
778
779 IF l_object_type_code = 'L' THEN
780
781 select distinct primary_ledger_id
782 into l_ledger_id_out
783 from xla_ledger_relationships_v
784 where ledger_id = p_ledger_id;
785
786
787
788 ELSIF l_object_type_code = 'S' THEN
789
790 select ledger_id
791 into l_ledger_id
792 from gl_ledger_set_assignments glsa
793 where glsa.ledger_id <> p_ledger_id
794 and glsa.ledger_set_id = p_ledger_id
795 and rownum = 1;
796
797 select distinct primary_ledger_id
798 into l_ledger_id_out
799 from xla_ledger_relationships_v
800 where ledger_id = l_ledger_id;
801
802 END IF;
803
804 RETURN l_ledger_id_out;
805
806 EXCEPTION
807 WHEN OTHERS THEN
808 xla_exceptions_pkg.raise_message
809 (p_location => 'xla_report_utility_pkg.unravel_ledger (fn)');
810 END get_ledger_id;
811
812 --=============================================================================
813 --
814 --
815 --
816 --=============================================================================
817 FUNCTION get_ledger_object_type (p_ledger_id IN NUMBER)
818 RETURN VARCHAR2 IS
819
820 l_object_type_code gl_ledgers.object_type_code%TYPE;
821
822 BEGIN
823
824 SELECT object_type_code
825 INTO l_object_type_code
826 FROM gl_ledgers
827 WHERE ledger_id = p_ledger_id;
828
829 RETURN l_object_type_code;
830
831 EXCEPTION
832 WHEN OTHERS THEN
833 xla_exceptions_pkg.raise_message
834 (p_location => 'xla_report_utility_pkg.unravel_ledger (fn)');
835 END get_ledger_object_type;
836
837
838 --=============================================================================
839 --
840 --
841 --
842 --=============================================================================
843 FUNCTION get_transaction_id
844 (p_resp_application_id IN NUMBER
845 ,p_ledger_id IN NUMBER ) RETURN VARCHAR2 IS
846 CURSOR cur_event_class IS
847 (SELECT DISTINCT
848 aeh.application_id APPLICATION_ID
849 ,xet.entity_code ENTITY_CODE
850 ,xet.event_class_code EVENT_CLASS_CODE
851 ,xeca.reporting_view_name REPORTING_VIEW_NAME
852 FROM xla_ae_headers aeh
853 ,xla_event_types_b xet
854 ,xla_event_class_attrs xeca
855 WHERE xet.application_id = aeh.application_id
856 AND xet.event_type_code = aeh.event_type_code
857 AND xeca.application_id = aeh.application_id
858 AND xeca.entity_code = xet.entity_code
859 AND xeca.event_class_code = xet.event_class_code
860 AND aeh.ledger_id = p_ledger_id
861 AND aeh.application_id = p_resp_application_id );
862
863 l_col_array t_array;
864 l_null_col_array t_array;
865 l_trx_id_str VARCHAR2(32000);
866 l_col_string VARCHAR2(4000) := NULL;
867 l_view_name VARCHAR2(800);
868 l_join_string VARCHAR2(4000) := NULL;
869 l_sql_string VARCHAR2(4000) := NULL;
870 l_index INTEGER;
871 l_outerjoin VARCHAR2(30);
872 l_log_module VARCHAR2(240);
873
874 BEGIN
875 IF g_log_enabled THEN
876 l_log_module := C_DEFAULT_MODULE||'.get_transaction_id';
877 END IF;
878 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
879 trace
880 (p_msg => 'BEGIN of function GET_TRANSACTION_ID'
884 (p_msg => 'p_application_id = '||p_resp_application_id
881 ,p_level => C_LEVEL_PROCEDURE
882 ,p_module => l_log_module);
883 trace
885 ,p_level => C_LEVEL_PROCEDURE
886 ,p_module => l_log_module);
887 trace
888 (p_msg => 'p_ledger_id = '||p_ledger_id
889 ,p_level => C_LEVEL_PROCEDURE
890 ,p_module => l_log_module);
891 END IF;
892 l_trx_id_str := ',CASE WHEN 1<1 THEN NULL';
893
894 --
895 -- creating a dummy array that contains "NULL" strings
896 --
897 FOR i IN 1..10 LOOP
898 l_null_col_array(i).f1 := 'NULL';
899 l_null_col_array(i).f2 := 'NULL';
900 END LOOP;
901
902 FOR cur_trx IN cur_event_class LOOP
903 l_col_string := NULL;
904 l_view_name := NULL;
905 l_join_string := NULL;
906
907 IF cur_trx.entity_code NOT IN('MANUAL','THIRD_PARTY_MERGE') THEN
908 --
909 -- initiating the array that contains name of the columns to be selected
910 -- from the TID View.
911 --
912 l_col_array := l_null_col_array;
913 l_index := 0;
914
915 --
916 -- creating SELECT,FROM and WHERE clause strings when the reporting view is
917 -- defined for an Event Class.
918 --
919
920 IF cur_trx.reporting_view_name IS NOT NULL THEN
921 --
922 -- creating string to be added to FROM clause
923 --
924 l_view_name := cur_trx.reporting_view_name || ' TIV';
925 FOR cols_csr IN
926 (SELECT xid.transaction_id_col_name_1 trx_col_1
927 ,xid.transaction_id_col_name_2 trx_col_2
928 ,xid.transaction_id_col_name_3 trx_col_3
929 ,xid.transaction_id_col_name_4 trx_col_4
930 ,xid.source_id_col_name_1 src_col_1
931 ,xid.source_id_col_name_2 src_col_2
932 ,xid.source_id_col_name_3 src_col_3
933 ,xid.source_id_col_name_4 src_col_4
934 ,xem.column_name column_name
935 ,xem.column_title PROMPT
936 ,utc.data_type data_type
937 FROM xla_entity_id_mappings xid
938 ,xla_event_mappings_vl xem
939 ,user_tab_columns utc
940 WHERE xid.application_id = cur_trx.application_id
941 AND xid.entity_code = cur_trx.entity_code
942 AND xem.application_id = cur_trx.application_id
943 AND xem.entity_code = cur_trx.entity_code
944 AND xem.event_class_code = cur_trx.event_class_code
945 AND utc.table_name = cur_trx.reporting_view_name
946 AND utc.column_name = xem.column_name
947 ORDER BY xem.user_sequence)
948 LOOP
949
950 l_index := l_index + 1;
951 --
952 -- creating string to be added to WHERE clause
953 --
954 IF l_index = 1 THEN
955 -----------------------------------------------------------------
956 -- Bug 3389175
957 -- Following logic is build to make sure all events are reported
958 -- if debug is enabled evenif there is no data for the event in the
959 -- transaction id view.
960 -- if log enabled then
961 -- outer join to TID view
962 -- endif
963 -----------------------------------------------------------------
964 IF g_log_level <> C_LEVEL_LOG_DISABLED THEN
965 l_outerjoin := '(+)';
966 ELSE
967 l_outerjoin := NULL;
968 END IF;
969
970 IF cols_csr.trx_col_1 IS NOT NULL THEN
971 l_join_string := l_join_string ||
972 ' TIV.'|| cols_csr.trx_col_1 ||l_outerjoin ||
973 ' = ENT.'|| cols_csr.src_col_1;
974 END IF;
975 IF cols_csr.trx_col_2 IS NOT NULL THEN
976 l_join_string := l_join_string ||
977 ' AND TIV.'|| cols_csr.trx_col_2 ||l_outerjoin ||
978 ' = ENT.'|| cols_csr.src_col_2;
979 END IF;
980 IF cols_csr.trx_col_3 IS NOT NULL THEN
981 l_join_string := l_join_string ||
982 ' AND TIV.'|| cols_csr.trx_col_3 ||l_outerjoin ||
983 ' = ENT.'|| cols_csr.src_col_3;
984 END IF;
985 IF cols_csr.trx_col_4 IS NOT NULL THEN
986 l_join_string := l_join_string ||
987 ' AND TIV.'|| cols_csr.trx_col_4 ||l_outerjoin ||
988 ' = ENT.'|| cols_csr.src_col_4;
989 END IF;
990 END IF;
991 --
992 -- getting the PROMPTs to be displayed
993 -- Bug 5360816. Added REPLACE to handle apostophe in user prompts.
994 --
995 l_col_array(l_index).f1 := ''''||REPLACE(cols_csr.PROMPT,'''','''''')||'''';
996
997 ---
998 -- getting the columns to be displayed
999 ---
1000 IF cols_csr.data_type = 'VARCHAR2' THEN
1001 l_col_array(l_index).f2 := 'TIV.'|| cols_csr.column_name;
1005 ||',''YYYY-MM-DD"T"hh:mi:ss'')';
1002
1003 ELSIF cols_csr.data_type = 'DATE' THEN
1004 l_col_array(l_index).f2 := 'to_char(TIV.'|| cols_csr.column_name
1006 ELSE
1007 l_col_array(l_index).f2 := 'to_char(TIV.'|| cols_csr.column_name||')';
1008 END IF;
1009 END LOOP;
1010 END IF;
1011 --------------------------------------------------------------------------
1012 -- building the string to be added to the SELECT clause
1013 --------------------------------------------------------------------------
1014 IF l_index > 0 THEN
1015 l_col_string := l_col_string ||
1016 l_col_array(1).f1||'||''|''||'||
1017 l_col_array(1).f2;
1018
1019 FOR i IN 2..l_col_array.count LOOP
1020 l_col_string := l_col_string ||'||''|''||'||
1021 l_col_array(i).f1||'||''|''||'||
1022 l_col_array(i).f2;
1023 END LOOP;
1024
1025 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1026 trace
1027 (p_msg => 'l_col_string = '||l_col_string
1028 ,p_level => C_LEVEL_STATEMENT
1029 ,p_module => l_log_module);
1030 END IF;
1031
1032 l_trx_id_str := l_trx_id_str||
1033 ' WHEN xet.event_class_code = '''||
1034 cur_trx.event_class_code||
1035 ''' THEN ( SELECT '||l_col_string||
1036 ' FROM '||l_view_name ||' WHERE '|| l_join_string ||')';
1037 END IF;
1038 END IF;
1039 END LOOP;
1040
1041 l_trx_id_str := l_trx_id_str ||' END ';
1042 RETURN l_trx_id_str;
1043
1044 EXCEPTION
1045 WHEN OTHERS THEN
1046 xla_exceptions_pkg.raise_message
1047 (p_location => 'xla_report_utility_pkg.get_transaction_id ');
1048
1049 END get_transaction_id;
1050
1051 --=============================================================================
1052 --
1053 --
1054 --
1055 --=============================================================================
1056 FUNCTION get_conc_segments
1057 (p_coa_id NUMBER
1058 ,p_table_alias VARCHAR2)
1059 RETURN VARCHAR2 IS
1060
1061 TYPE t_array_char IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
1062
1063 l_conc_seg_delimiter VARCHAR2(80);
1064 l_concat_segment VARCHAR2(4000);
1065 l_array t_array_char;
1066 l_log_module VARCHAR2(240);
1067
1068 CURSOR c(p_coa_id NUMBER,p_table_alias VARCHAR2) IS
1069 SELECT p_table_alias||'.'||application_column_name seg
1070 FROM fnd_id_flex_segments
1071 WHERE application_id =101
1072 AND id_flex_code ='GL#'
1073 AND id_flex_num = p_coa_id
1074 ORDER BY segment_num ;
1075
1076 BEGIN
1077
1078 IF g_log_enabled THEN
1079 l_log_module := C_DEFAULT_MODULE||'.get_conc_segments';
1080 END IF;
1081 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1082 trace
1083 (p_msg => 'BEGIN of function GET_CONC_SEGMENTS'
1084 ,p_level => C_LEVEL_PROCEDURE
1085 ,p_module => l_log_module);
1086 trace
1087 (p_msg => 'p_coa_id = '||p_coa_id
1088 ,p_level => C_LEVEL_PROCEDURE
1089 ,p_module => l_log_module);
1090 trace
1091 (p_msg => 'p_table_alias = '||p_table_alias
1092 ,p_level => C_LEVEL_PROCEDURE
1093 ,p_module => l_log_module);
1094 END IF;
1095
1096 --
1097 -- Get concatenated segment delimiter for COA
1098 --
1099
1100 SELECT '||'''||concatenated_segment_delimiter||'''||'
1101 INTO l_conc_seg_delimiter
1102 FROM fnd_id_flex_structures
1103 WHERE application_id =101
1104 AND id_flex_code ='GL#'
1105 AND id_flex_num = p_coa_id;
1106
1107 OPEN c(p_coa_id ,p_table_alias);
1108
1109 FETCH c BULK COLLECT INTO l_array;
1110
1111 CLOSE c;
1112
1113 FOR i in 1 .. l_array.count LOOP
1114 l_concat_segment := l_concat_segment||l_array(i);
1115
1116 IF i<l_array.count THEN
1117 l_concat_segment := l_concat_segment||l_conc_seg_delimiter;
1118 END IF;
1119
1120 END LOOP;
1121
1122 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1123 trace
1124 (p_msg => 'END of function GET_CONC_SEGMENTS'
1125 ,p_level => C_LEVEL_PROCEDURE
1126 ,p_module => l_log_module);
1127 END IF;
1128
1129 RETURN l_concat_segment;
1130
1131 EXCEPTION
1132 WHEN OTHERS THEN
1133 xla_exceptions_pkg.raise_message
1134 (p_location => 'xla_report_utility_pkg.get_conc_segments ');
1135
1136 END get_conc_segments;
1137
1138
1139 --=============================================================================
1140 -- *********** Initialization routine **********
1141 --=============================================================================
1142
1143 --=============================================================================
1144 --
1145 --
1146 --
1147 --
1148 --
1149 --
1150 --
1151 --
1152 --
1153 --
1154 -- Following code is executed when the package body is referenced for the first
1155 -- time
1156 --
1157 --
1158 --
1159 --
1160 --
1161 --
1162 --
1163 --
1164 --
1165 --
1166 --
1167 --
1168 --=============================================================================
1169
1170 BEGIN
1171 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1172 g_log_enabled := fnd_log.test
1173 (log_level => g_log_level
1174 ,MODULE => C_DEFAULT_MODULE);
1175
1176 IF NOT g_log_enabled THEN
1177 g_log_level := C_LEVEL_LOG_DISABLED;
1178 END IF;
1179
1180 END XLA_REPORT_UTILITY_PKG;