[Home] [Help]
PACKAGE BODY: APPS.XLA_REPORT_UTILITY_PKG
Source
1 PACKAGE BODY xla_report_utility_pkg AS
2 -- $Header: xlarputl.pkb 120.23.12020000.2 2012/07/23 07:09:46 vgopiset ship $
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 | 02/16/2009 N. K. Surana Overloading function get_transaction_id |
24 | to handle more than 50 event classes per |
25 | application id required for FSAH Customers.|
26 | 3-Mar-2010 rajose 9323360 to implement caching for CCID desc |
27 | function by using PLSQL nested table |
28 | hashing |
29 | 26/07/2011 NKSURANA 12663084: Removed the exists check on |
30 | xla_ae_headers when fetching the |
31 | reporting view names in get_transaction_id |
32 +===========================================================================*/
33
34 --=============================================================================
35 -- **************** declaraions ********************
36 --=============================================================================
37
38 TYPE t_rec IS RECORD
39 (f1 VARCHAR2(80)
40 ,f2 VARCHAR2(80));
41 TYPE t_array IS TABLE OF t_rec INDEX BY BINARY_INTEGER;
42
43 --=============================================================================
44 -- ************** forward declaraions ******************
45 --=============================================================================
46 -- none
47
48 --=============================================================================
49 -- *********** Local Trace Routine **********
50 --=============================================================================
51 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
52 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
53 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
54 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
55 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
56 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
57
58 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
59 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_report_utility_pkg';
60
61 g_log_level NUMBER;
62 g_log_enabled BOOLEAN;
63 C_NEW_LINE CONSTANT VARCHAR2(8) := fnd_global.newline;
64 C_OWNER_ORACLE CONSTANT VARCHAR2(1) := 'S';
65
66 --bug#9323360
67 TYPE t_ccid_desc IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
68 TYPE t_coaid IS TABLE OF t_ccid_desc INDEX BY BINARY_INTEGER;
69
70 g_t_cache_ccid_desc t_ccid_desc;
71 g_t_cache_coa_id t_coaid;
72 --bug#9323360
73
74 -------------------------------------------------------------------------------
75 -- constant for getting flexfield segment value description
76 -------------------------------------------------------------------------------
77 C_SEG_DESC_JOIN CONSTANT VARCHAR2(32000) :=
78 ' AND $alias$.flex_value_set_id = $flex_value_set_id$ '
79 || C_NEW_LINE
80 || ' AND $alias$.flex_value = $segment_column$ '
81 || C_NEW_LINE
82 || ' AND $alias$.parent_flex_value_low ' -- added for bug:7641746 for Dependant/Table Validated Value Set
83 ;
84
85
86 C_HINT CONSTANT VARCHAR2(240) :=
87 ' /*+ leading(gcck $fnd_flex_hint$, gl1, glb) use_nl(glb) */ ';
88
89 PROCEDURE trace
90 (p_msg IN VARCHAR2
91 ,p_level IN NUMBER
92 ,p_module IN VARCHAR2) IS
93 BEGIN
94 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
95 fnd_log.message(p_level, NVL(p_module,C_DEFAULT_MODULE));
96 ELSIF p_level >= g_log_level THEN
97 fnd_log.string(p_level, NVL(p_module,C_DEFAULT_MODULE), p_msg);
98 END IF;
99
100 EXCEPTION
101 WHEN xla_exceptions_pkg.application_exception THEN
102 RAISE;
103 WHEN OTHERS THEN
104 xla_exceptions_pkg.raise_message
105 (p_location => 'xla_report_utility_pkg.trace');
106 END trace;
107
108 --=============================================================================
109 -- *********** public procedures and functions **********
110 --=============================================================================
111 --=============================================================================
112 --
113 --
114 --
115 --
116 --
117 --
118 --
119 --
120 --
121 --
122 -- Following are public routines
123 --
124 -- 1. get_transaction_id
125 -- 2. get_acct_qualifier_segs
126 -- 3. get_ccid_desc
127 -- 4. clob_to_file
128 -- 5. get_anc_filter
129 -- 6. get_conc_segments
130 --
131 --
132 --
133 --
134 --
135 --
136 --
137 --
138 --
139 --
140 --=============================================================================
141
142
143 --=============================================================================
144 --
145 --
146 --
147 --=============================================================================
148 /*======================================================================+
149 | |
150 | Public Procedure |
151 | |
152 | get_segment_info |
153 | |
154 | Returns segment information |
155 | |
156 +======================================================================*/
157 PROCEDURE get_segment_info
158 (p_coa_id IN NUMBER
159 ,p_balancing_segment IN VARCHAR2
160 ,p_account_segment IN VARCHAR2
161 ,p_costcenter_segment IN VARCHAR2
162 ,p_management_segment IN VARCHAR2
163 ,p_intercompany_segment IN VARCHAR2
164 ,p_alias_balancing_segment IN VARCHAR2
165 ,p_alias_account_segment IN VARCHAR2
166 ,p_alias_costcenter_segment IN VARCHAR2
167 ,p_alias_management_segment IN VARCHAR2
168 ,p_alias_intercompany_segment IN VARCHAR2
169 ,p_seg_desc_column OUT NOCOPY VARCHAR2
170 ,p_seg_desc_from OUT NOCOPY VARCHAR2
171 ,p_seg_desc_join OUT NOCOPY VARCHAR2
172 ,p_hint OUT NOCOPY VARCHAR2)
173
174 IS
175
176 l_log_module VARCHAR2(240);
177
178 l_seg_desc_column VARCHAR2(32000) := ' '; -- initialized to space bug 8816030
179 l_seg_desc_from VARCHAR2(32000) := ' '; -- initialized to space bug 8816030
180 l_seg_desc_join VARCHAR2(32000) := ' '; -- initialized to space bug 8816030
181
182 l_fnd_flex_hint VARCHAR2(240) := '';
183 l_hint VARCHAR2(240);
184
185 /* following added for bug:7641746 for Dependant/Table Validated Value Set */
186
187 l_flex_value_set_id NUMBER(10) ;
188 l_validation_type CHAR(1) ;
189 l_parent_segment VARCHAR2(80);
190 l_display_flag fnd_id_flex_segments.display_flag%TYPE;
191
192 /**********************************************************************/
193 /* Following values for VALIDATION TYPE of a Value Set */
194 /* i) F : TABLE VALIDATED VALUE SET */
195 /* ii) I : INDEPENDENT VALUE SET */
196 /* iii) D : DEPENDENT VALUE SET */
197 /**********************************************************************/
198 -- Query to get the Validation Type for Value Set : bug:7641746
199 CURSOR c_validation_type(p_flex_value_set_id IN NUMBER) IS
200 select validation_type
201 from fnd_flex_value_sets
202 where flex_value_set_id = p_flex_value_set_id ;
203
204 -- Query to get the parent SEGMENT for Dependent Value Set : bug:7641746
205 CURSOR c_parent_segment_name(p_flex_value_set_id IN NUMBER) IS
206 SELECT application_column_name
207 FROM fnd_id_flex_segments
208 WHERE id_flex_code = 'GL#'
209 AND id_flex_num = p_coa_id
210 AND application_id = 101
211 AND flex_value_set_id =
212 ( SELECT parent_flex_value_set_id
213 FROM fnd_flex_value_sets
214 WHERE flex_value_set_id = p_flex_value_set_id
215 );
216
217 /* end of changes for bug:7641746 */
218
219 CURSOR C_SEG_DISP_REQ_CHECK(
220 p_application_id INTEGER,
221 p_id_flex_code VARCHAR2,
222 p_id_flex_num INTEGER,
223 p_segment_code VARCHAR2
224 )
225 IS
226 SELECT display_flag
227 FROM fnd_id_flex_segments fid
228 WHERE application_id = p_application_id
229 AND id_flex_code = p_id_flex_code
230 AND id_flex_num = p_id_flex_num
231 AND application_column_name = p_segment_code;
232
233 BEGIN
234 IF g_log_enabled THEN
235 l_log_module := C_DEFAULT_MODULE||'.get_segment_info';
236 END IF;
237
238 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
239
240 trace
241 (p_msg => 'BEGIN of get_segment_info'
242 ,p_level => C_LEVEL_PROCEDURE
243 ,p_module => l_log_module);
244
245 END IF;
246
247
248
249 ----------------------------------------------------------------------------
250 -- building code to get segment description
251 ----------------------------------------------------------------------------
252 /* start of new code for the bug:7641746 */
253
254 IF p_balancing_segment <> 'NULL' THEN
255
256 l_flex_value_set_id := xla_flex_pkg.get_segment_valueset
257 (p_application_id => 101
258 ,p_id_flex_code => 'GL#'
259 ,p_id_flex_num => p_coa_id
260 ,p_segment_code => p_balancing_segment
261 );
262
263 FOR i IN c_validation_type(l_flex_value_set_id)
264 LOOP
265 l_validation_type := i.validation_type ;
266 END LOOP;
267
268
269 IF l_validation_type = 'F' THEN -- For Table Validated Set
270
271 l_seg_desc_column := l_seg_desc_column
272 || ',NULL BALANCING_SEGMENT_DESC '
273 || C_NEW_LINE;
274
275 ELSIF l_validation_type IN ('I','D') THEN -- For Independent, Dependent Value Set
276
277 l_seg_desc_column := l_seg_desc_column
278 || ',fvbs.description BALANCING_SEGMENT_DESC '
279 || C_NEW_LINE;
280
281 l_seg_desc_from := l_seg_desc_from
282 ||',fnd_flex_values_vl fvbs '
283 || C_NEW_LINE;
284
285 --bug#7834671
286 OPEN C_SEG_DISP_REQ_CHECK(101,'GL#',p_coa_id,p_balancing_segment);
287 FETCH C_SEG_DISP_REQ_CHECK INTO l_display_flag;
288 CLOSE C_SEG_DISP_REQ_CHECK;
289
290 IF l_display_flag = 'N' THEN
291 l_seg_desc_join := l_seg_desc_join || ' AND $alias$.flex_value_set_id(+) = $flex_value_set_id$ '
292 || C_NEW_LINE
293 || ' AND $alias$.flex_value(+) = $segment_column$ '
294 || C_NEW_LINE
295 || ' AND $alias$.parent_flex_value_low(+) ';
296
297 ELSE
298 l_seg_desc_join := l_seg_desc_join||C_SEG_DESC_JOIN;
299 END IF;
300 --bug#7834671
301
302
303 IF l_validation_type = 'I' THEN
304
305 l_seg_desc_join := l_seg_desc_join||' IS NULL '|| C_NEW_LINE; -- For Independent Set, Paret_Flex_Value_Low IS NULL
306
307 ELSE
308 l_seg_desc_join := l_seg_desc_join||' = gcck.$parent_segment_column$ '|| C_NEW_LINE;
309
310 -- Get the PARENT SEGMENT on which DEPENDENT VALUE is based upon
311 FOR i IN c_parent_segment_name( l_flex_value_set_id )
312 LOOP
313 l_parent_segment := i.application_column_name ;
314 END LOOP;
315
316 l_seg_desc_join := REPLACE(l_seg_desc_join
317 ,'$parent_segment_column$'
318 ,l_parent_segment);
319
320
321
322 END IF;
323
324 l_seg_desc_join := REPLACE(l_seg_desc_join,'$alias$','fvbs');
325
326 l_seg_desc_join :=
327 REPLACE
328 (l_seg_desc_join
329 ,'$flex_value_set_id$'
330 , l_flex_value_set_id
331 );
332 l_seg_desc_join := replace(l_seg_desc_join,'$segment_column$',p_alias_balancing_segment);
333 l_fnd_flex_hint := l_fnd_flex_hint ||',fvbs' ;
334
335 ELSE
336
337 l_seg_desc_column := l_seg_desc_column
338 || ',NULL BALANCING_SEGMENT_DESC '
339 || C_NEW_LINE;
340
341 END IF ;
342 ELSE
343 l_seg_desc_column := l_seg_desc_column
344 || ',NULL BALANCING_SEGMENT_DESC '
345 || C_NEW_LINE;
346
347
348
349 END IF;
350
351 IF p_account_segment <> 'NULL' THEN
352
353 l_flex_value_set_id := xla_flex_pkg.get_segment_valueset
354 (p_application_id => 101
355 ,p_id_flex_code => 'GL#'
356 ,p_id_flex_num => p_coa_id
357 ,p_segment_code => p_account_segment
358 );
359
360 FOR i IN c_validation_type(l_flex_value_set_id)
361 LOOP
362 l_validation_type := i.validation_type ;
363 END LOOP;
364
365 IF l_validation_type = 'F' THEN -- For Table Validated Set
366
367 l_seg_desc_column := l_seg_desc_column
368 || ',NULL NATURAL_ACCOUNT_DESC '
369 || C_NEW_LINE;
370
371 ELSIF l_validation_type IN ('I','D') THEN -- For Independent, Dependent Value Set
372
373
374 l_seg_desc_column := l_seg_desc_column
375 ||',fvna.description NATURAL_ACCOUNT_DESC '
376 || C_NEW_LINE;
377
378 l_seg_desc_from := l_seg_desc_from
379 ||',fnd_flex_values_vl fvna '
380 || C_NEW_LINE;
381
382 --bug#7834671
383 OPEN C_SEG_DISP_REQ_CHECK(101,'GL#',p_coa_id,p_balancing_segment);
384 FETCH C_SEG_DISP_REQ_CHECK INTO l_display_flag;
385 CLOSE C_SEG_DISP_REQ_CHECK;
386
387 IF l_display_flag = 'N' THEN
388 l_seg_desc_join := l_seg_desc_join || ' AND $alias$.flex_value_set_id(+) = $flex_value_set_id$ '
389 || C_NEW_LINE
390 || ' AND $alias$.flex_value(+) = $segment_column$ '
391 || C_NEW_LINE
392 || ' AND $alias$.parent_flex_value_low(+) ';
393
394 ELSE
395 l_seg_desc_join := l_seg_desc_join||C_SEG_DESC_JOIN;
396 END IF;
397 --bug#7834671
398
399 IF l_validation_type = 'I' THEN
400
401 l_seg_desc_join:=l_seg_desc_join||' is null ' || C_NEW_LINE ; -- For Independent Set, Paret_Flex_Value_Low IS NULL
402 ELSE
403 l_seg_desc_join:=l_seg_desc_join||' = gcck.$parent_segment_column$ '|| C_NEW_LINE;
404
405 -- Get the PARENT SEGMENT on which DEPENDENT VALUE is based upon
406 FOR i IN c_parent_segment_name( l_flex_value_set_id )
407 LOOP
408 l_parent_segment := i.application_column_name ;
409
410 END LOOP;
411
412 l_seg_desc_join := REPLACE(l_seg_desc_join
413 ,'$parent_segment_column$'
414 ,l_parent_segment);
415
416 END IF;
417
418 l_seg_desc_join := REPLACE(l_seg_desc_join,'$alias$','fvna');
419
420 l_seg_desc_join :=
421 REPLACE
422 (l_seg_desc_join
423 ,'$flex_value_set_id$'
424 , l_flex_value_set_id
425 );
426 l_seg_desc_join := REPLACE(l_seg_desc_join,'$segment_column$',p_alias_account_segment);
427 l_fnd_flex_hint := l_fnd_flex_hint ||',fvna' ;
428
429
430 ELSE
431
432 l_seg_desc_column := l_seg_desc_column
433 || ',NULL NATURAL_ACCOUNT_DESC '
434 || C_NEW_LINE;
435
436 END IF;
437 ELSE
438 l_seg_desc_column := l_seg_desc_column
439 || ',NULL NATURAL_ACCOUNT_DESC '
440 || C_NEW_LINE;
441
442 END IF;
443
444 IF p_costcenter_segment <> 'NULL' THEN
445
446 l_flex_value_set_id := xla_flex_pkg.get_segment_valueset
447 (p_application_id => 101
448 ,p_id_flex_code => 'GL#'
449 ,p_id_flex_num => p_coa_id
450 ,p_segment_code => p_costcenter_segment
451 );
452
453 FOR i IN c_validation_type(l_flex_value_set_id)
454 LOOP
455 l_validation_type := i.validation_type ;
456 END LOOP;
457
458 IF l_validation_type = 'F' THEN -- For Table Validated Set
459
460 l_seg_desc_column := l_seg_desc_column
461 || ',NULL COST_CENTER_DESC '
462 || C_NEW_LINE;
463
464 ELSIF l_validation_type IN ('I','D') THEN -- For Independent, Dependent Value Set
465
466 l_seg_desc_column := l_seg_desc_column
467 ||',fvcc.description COST_CENTER_DESC '
468 || C_NEW_LINE;
469
470 l_seg_desc_from := l_seg_desc_from
471 ||',fnd_flex_values_vl fvcc '
472 || C_NEW_LINE;
473
474 --bug#7834671
475 OPEN C_SEG_DISP_REQ_CHECK(101,'GL#',p_coa_id,p_balancing_segment);
476 FETCH C_SEG_DISP_REQ_CHECK INTO l_display_flag;
477 CLOSE C_SEG_DISP_REQ_CHECK;
478
479 IF l_display_flag = 'N' THEN
480 l_seg_desc_join := l_seg_desc_join || ' AND $alias$.flex_value_set_id(+) = $flex_value_set_id$ '
481 || C_NEW_LINE
482 || ' AND $alias$.flex_value(+) = $segment_column$ '
483 || C_NEW_LINE
484 || ' AND $alias$.parent_flex_value_low(+) ';
485
486 ELSE
487 l_seg_desc_join := l_seg_desc_join||C_SEG_DESC_JOIN;
488 END IF;
489 --bug#7834671
490
491
492 IF l_validation_type = 'I' THEN
493
494 l_seg_desc_join:=l_seg_desc_join||' is null '|| C_NEW_LINE; -- For Independent Set, Paret_Flex_Value_Low IS NULL
495 ELSE
496 l_seg_desc_join:=l_seg_desc_join||' = gcck.$parent_segment_column$ '|| C_NEW_LINE;
497
498 -- Get the PARENT SEGMENT on which DEPENDENT VALUE is based upon
499 FOR i IN c_parent_segment_name( l_flex_value_set_id )
500 LOOP
501 l_parent_segment := i.application_column_name ;
502
503 END LOOP;
504
505 l_seg_desc_join := REPLACE(l_seg_desc_join
506 ,'$parent_segment_column$'
507 ,l_parent_segment);
508
509 END IF;
510
511 l_seg_desc_join := REPLACE(l_seg_desc_join,'$alias$','fvcc');
512
513 l_seg_desc_join :=
514 REPLACE
515 (l_seg_desc_join
516 ,'$flex_value_set_id$'
517 , l_flex_value_set_id
518 );
519 l_seg_desc_join := REPLACE(l_seg_desc_join,'$segment_column$',p_alias_costcenter_segment);
520 l_fnd_flex_hint := l_fnd_flex_hint ||',fvcc' ;
521
522 ELSE
523
524 l_seg_desc_column := l_seg_desc_column
525 || ',NULL COST_CENTER_DESC '
526 || C_NEW_LINE;
527
528 END IF;
529 ELSE
530 l_seg_desc_column := l_seg_desc_column
531 || ',NULL COST_CENTER_DESC'
532 || C_NEW_LINE;
533
534 END IF;
535
536 IF p_management_segment <> 'NULL' THEN
537
538 l_flex_value_set_id := xla_flex_pkg.get_segment_valueset
539 (p_application_id => 101
540 ,p_id_flex_code => 'GL#'
541 ,p_id_flex_num => p_coa_id
542 ,p_segment_code => p_management_segment
543 );
544
545 FOR i IN c_validation_type(l_flex_value_set_id)
546 LOOP
547 l_validation_type := i.validation_type ;
548 END LOOP;
549
550 IF l_validation_type = 'F' THEN -- For Table Validated Set
551
552 l_seg_desc_column := l_seg_desc_column
553 || ',NULL MANAGEMENT_SEGMENT_DESC '
554 || C_NEW_LINE;
555
556 ELSIF l_validation_type IN ('I','D') THEN -- For Independent, Dependent Value Set
557
558 l_seg_desc_column := l_seg_desc_column
559 ||',fvmg.description MANAGEMENT_SEGMENT_DESC '
560 || C_NEW_LINE;
561
562 l_seg_desc_from := l_seg_desc_from
563 ||',fnd_flex_values_vl fvmg '
564 || C_NEW_LINE;
565
566 --bug#7834671
567 OPEN C_SEG_DISP_REQ_CHECK(101,'GL#',p_coa_id,p_balancing_segment);
568 FETCH C_SEG_DISP_REQ_CHECK INTO l_display_flag;
569 CLOSE C_SEG_DISP_REQ_CHECK;
570
571 IF l_display_flag = 'N' THEN
572 l_seg_desc_join := l_seg_desc_join || ' AND $alias$.flex_value_set_id(+) = $flex_value_set_id$ '
573 || C_NEW_LINE
574 || ' AND $alias$.flex_value(+) = $segment_column$ '
575 || C_NEW_LINE
576 || ' AND $alias$.parent_flex_value_low(+) ';
577
578 ELSE
579 l_seg_desc_join := l_seg_desc_join||C_SEG_DESC_JOIN;
580 END IF;
581 --bug#7834671
582
583
584 IF l_validation_type = 'I' THEN
585
586 l_seg_desc_join:=l_seg_desc_join||' is null '|| C_NEW_LINE; -- For Independent Set, Paret_Flex_Value_Low IS NULL
587
588 ELSE
589 l_seg_desc_join:=l_seg_desc_join||' = gcck.$parent_segment_column$ '|| C_NEW_LINE;
590
591 -- Get the PARENT SEGMENT on which DEPENDENT VALUE is based upon
592 FOR i IN c_parent_segment_name( l_flex_value_set_id )
593 LOOP
594 l_parent_segment := i.application_column_name ;
595
596 END LOOP;
597
598 l_seg_desc_join := REPLACE(l_seg_desc_join
599 ,'$parent_segment_column$'
600 ,l_parent_segment);
601
602 END IF;
603
604 l_seg_desc_join := REPLACE(l_seg_desc_join,'$alias$','fvmg');
605
606 l_seg_desc_join :=
607 REPLACE
608 (l_seg_desc_join
609 ,'$flex_value_set_id$'
610 ,l_flex_value_set_id
611 );
612 l_seg_desc_join := REPLACE(l_seg_desc_join,'$segment_column$',p_alias_management_segment);
613 l_fnd_flex_hint := l_fnd_flex_hint ||',fvmg' ;
614
615 ELSE
616 l_seg_desc_column := l_seg_desc_column
617 || ',NULL MANAGEMENT_SEGMENT_DESC '
618 || C_NEW_LINE;
619
620 END IF;
621 ELSE
622 l_seg_desc_column := l_seg_desc_column
623 || ',NULL MANAGEMENT_SEGMENT_DESC '
624 || C_NEW_LINE;
625
626 END IF;
627
628 IF p_intercompany_segment <> 'NULL' THEN
629
630 l_flex_value_set_id := xla_flex_pkg.get_segment_valueset
631 (p_application_id => 101
632 ,p_id_flex_code => 'GL#'
633 ,p_id_flex_num => p_coa_id
634 ,p_segment_code => p_intercompany_segment
635 );
636
637
638 FOR i IN c_validation_type(l_flex_value_set_id)
639 LOOP
640 l_validation_type := i.validation_type ;
641 END LOOP;
642
643
644 IF l_validation_type = 'F' THEN
645
646
647 l_seg_desc_column := l_seg_desc_column
648 || ',NULL INTERCOMPANY_SEGMENT_DESC '
649 || C_NEW_LINE;
650
651 ELSIF l_validation_type IN ('I','D') THEN -- For Independent, Dependent Value Set
652
653
654 l_seg_desc_column := l_seg_desc_column
655 ||',fvic.description INTERCOMPANY_SEGMENT_DESC ';
656
657 l_seg_desc_from := l_seg_desc_from
658 ||',fnd_flex_values_vl fvic ';
659
660 --bug#7834671
661 OPEN C_SEG_DISP_REQ_CHECK(101,'GL#',p_coa_id,p_balancing_segment);
662 FETCH C_SEG_DISP_REQ_CHECK INTO l_display_flag;
663 CLOSE C_SEG_DISP_REQ_CHECK;
664
665 IF l_display_flag = 'N' THEN
666
667 l_seg_desc_join := l_seg_desc_join || ' AND $alias$.flex_value_set_id(+) = $flex_value_set_id$ '
668 || C_NEW_LINE
669 || ' AND $alias$.flex_value(+) = $segment_column$ '
670 || C_NEW_LINE
671 || ' AND $alias$.parent_flex_value_low(+) ';
672
673 ELSE
674
675 l_seg_desc_join := l_seg_desc_join||C_SEG_DESC_JOIN;
676 END IF;
677 --bug#7834671
678
679
680 IF l_validation_type = 'I' THEN
681
682
683 l_seg_desc_join:=l_seg_desc_join||' is null '|| C_NEW_LINE; -- For Independent Set, Paret_Flex_Value_Low IS NULL
684
685 ELSE
686 l_seg_desc_join:=l_seg_desc_join||' = gcck.$parent_segment_column$ '|| C_NEW_LINE;
687
688 -- Get the PARENT SEGMENT on which DEPENDENT VALUE is based upon
689 FOR i IN c_parent_segment_name( l_flex_value_set_id )
690 LOOP
691 l_parent_segment := i.application_column_name ;
692
693 END LOOP;
694
695 l_seg_desc_join := REPLACE(l_seg_desc_join
696 ,'$parent_segment_column$'
697 ,l_parent_segment);
698
699 END IF;
700
701 l_seg_desc_join := REPLACE(l_seg_desc_join,'$alias$','fvic');
702
703 l_seg_desc_join :=
704 REPLACE
705 (l_seg_desc_join
706 ,'$flex_value_set_id$'
707 ,l_flex_value_set_id
708 );
709 l_seg_desc_join := replace(l_seg_desc_join,'$segment_column$',p_alias_intercompany_segment);
710 l_fnd_flex_hint := l_fnd_flex_hint ||',fvic' ;
711
712 ELSE
713
714 l_seg_desc_column := l_seg_desc_column
715 || ',NULL INTERCOMPANY_SEGMENT_DESC '
716 || C_NEW_LINE;
717
718 END IF;
719
720 ELSE
721
722
723 l_seg_desc_column := l_seg_desc_column
724 || ',NULL INTERCOMPANY_SEGMENT_DESC '
725 || C_NEW_LINE;
726 END IF;
727
728 /* end of new code for the bug:7641746 */
729
730 p_seg_desc_column := l_seg_desc_column;
731 p_seg_desc_from := l_seg_desc_from;
732 p_seg_desc_join := l_seg_desc_join;
733 p_hint := l_fnd_flex_hint;
734
735 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
736
737 trace
738 (p_msg => 'END of get_segment_info'
739 ,p_level => C_LEVEL_PROCEDURE
740 ,p_module => l_log_module);
741
742 END IF;
743
744 EXCEPTION
745 WHEN xla_exceptions_pkg.application_exception THEN
746 RAISE;
747 WHEN OTHERS THEN
748 xla_exceptions_pkg.raise_message
749 (p_location => 'xla_tb_report_pvt.get_segment_info');
750 END get_segment_info;
751 --=============================================================================
752
753
754 --=============================================================================
755 --
756 --
757 --
758 --=============================================================================
759 PROCEDURE get_transaction_id
760 (p_application_id IN INTEGER
761 ,p_entity_code IN VARCHAR2
762 ,p_event_class_code IN VARCHAR2
763 ,p_reporting_view_name IN VARCHAR2
764 ,p_select_str OUT NOCOPY VARCHAR2
765 ,p_from_str OUT NOCOPY VARCHAR2
766 ,p_where_str OUT NOCOPY VARCHAR2) IS
767
768 -- Split the join between Entity Mapping and Event Mappings as Report Ends in Error
769 -- with SQL Syntax erro when User Transaction Identifiers are not provided in
770 -- Accounting Event Class Options Window (Added for Bug 11691458)
771
772 CURSOR cols_csr IS
773 (SELECT xid.transaction_id_col_name_1 trx_col_1
774 ,xid.transaction_id_col_name_2 trx_col_2
775 ,xid.transaction_id_col_name_3 trx_col_3
776 ,xid.transaction_id_col_name_4 trx_col_4
777 ,xid.source_id_col_name_1 src_col_1
778 ,xid.source_id_col_name_2 src_col_2
779 ,xid.source_id_col_name_3 src_col_3
780 ,xid.source_id_col_name_4 src_col_4
781 -- ,xem.column_name column_name
782 -- ,xem.column_title PROMPT
783 -- ,utc.data_type data_type
784 FROM xla_entity_id_mappings xid
785 -- ,xla_event_mappings_vl xem
786 -- ,user_tab_columns utc
787 WHERE xid.application_id = p_application_id
788 AND xid.entity_code = p_entity_code
789 /* AND xem.application_id = p_application_id
790 AND xem.entity_code = p_entity_code
791 AND xem.event_class_code = p_event_class_code
792 AND utc.table_name = p_reporting_view_name
793 AND utc.column_name = xem.column_name */
794 );
795 -- ORDER BY xem.user_sequence;
796
797 l_col_array t_array;
798 l_null_col_array t_array;
799 l_col_string VARCHAR2(4000) := NULL;
800 l_view_name VARCHAR2(800);
801 l_join_string VARCHAR2(4000) := NULL;
802 l_sql_string VARCHAR2(4000) := NULL;
803 l_index INTEGER;
804 l_outerjoin VARCHAR2(30);
805 l_log_module VARCHAR2(240);
806 BEGIN
807 IF g_log_enabled THEN
808 l_log_module := C_DEFAULT_MODULE||'.get_transaction_id';
809 END IF;
810 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
811 trace
812 (p_msg => 'BEGIN of procedure GET_TRANSACTION_ID'
813 ,p_level => C_LEVEL_PROCEDURE
814 ,p_module => l_log_module);
815 trace
816 (p_msg => 'p_application_id = '||p_application_id
817 ,p_level => C_LEVEL_PROCEDURE
818 ,p_module => l_log_module);
819 trace
820 (p_msg => 'p_entity_code = '||p_entity_code
821 ,p_level => C_LEVEL_PROCEDURE
822 ,p_module => l_log_module);
823 trace
824 (p_msg => 'p_event_class_code = '||p_event_class_code
825 ,p_level => C_LEVEL_PROCEDURE
826 ,p_module => l_log_module);
827 trace
828 (p_msg => 'p_reporting_view_name = '||p_reporting_view_name
829 ,p_level => C_LEVEL_PROCEDURE
830 ,p_module => l_log_module);
831 END IF;
832 ----------------------------------------------------------------------------
833 -- creating a dummy array that contains "NULL" strings
834 ----------------------------------------------------------------------------
835 FOR i IN 1..10 LOOP
836 l_null_col_array(i).f1 := 'NULL';
837 l_null_col_array(i).f2 := 'NULL';
838
839 END LOOP;
840 ----------------------------------------------------------------------------
841 -- initiating the array that contains name of the columns to be selected
842 -- from the TID View.
843 ----------------------------------------------------------------------------
844 l_col_array := l_null_col_array;
845
846 ----------------------------------------------------------------------------
847 -- creating SELECT,FROM and WHERE clause strings when the reporting view is
848 -- defined for an Event Class.
849 ----------------------------------------------------------------------------
850 IF p_reporting_view_name IS NOT NULL THEN
851 -------------------------------------------------------------------------
852 -- creating string to be added to FROM clause
853 -------------------------------------------------------------------------
854 l_view_name := ',' || p_reporting_view_name || ' TIV';
855 l_index := 0;
856 FOR c1 IN cols_csr LOOP
857 l_index := l_index + 1;
858
859 ----------------------------------------------------------------------
860 -- creating string to be added to WHERE clause
861 ----------------------------------------------------------------------
862 IF l_index = 1 THEN
863 -------------------------------------------------------------------
864 -- Bug 3389175
865 -- Following logic is build to make sure all events are reported
866 -- if debug is enabled evenif there is no data for the event in the
867 -- transaction id view.
868 -- if log enabled then
869 -- outer join to TID view
870 -- endif
871 -------------------------------------------------------------------
872 IF g_log_level <> C_LEVEL_LOG_DISABLED THEN
873 l_outerjoin := '(+)';
874 ELSE
875 l_outerjoin := NULL;
876 END IF;
877
878 IF c1.trx_col_1 IS NOT NULL THEN
879 l_join_string := l_join_string ||
880 ' AND TIV.'|| c1.trx_col_1 ||l_outerjoin ||
881 ' = ENT.'|| c1.src_col_1;
882 END IF;
883 IF c1.trx_col_2 IS NOT NULL THEN
884 l_join_string := l_join_string ||
885 ' AND TIV.'|| c1.trx_col_2 ||l_outerjoin ||
886 ' = ENT.'|| c1.src_col_2;
887 END IF;
888 IF c1.trx_col_3 IS NOT NULL THEN
889 l_join_string := l_join_string ||
890 ' AND TIV.'|| c1.trx_col_3 ||l_outerjoin ||
891 ' = ENT.'|| c1.src_col_3;
892 END IF;
893 IF c1.trx_col_4 IS NOT NULL THEN
894 l_join_string := l_join_string ||
895 ' AND TIV.'|| c1.trx_col_4 ||l_outerjoin ||
896 ' = ENT.'|| c1.src_col_4;
897 END IF;
898 END IF;
899 END LOOP;
900 l_index := 0;
901 --Check if the object is synonym or not.(Added and commented for Bug 11691458)
902 FOR c1 IN
903 (SELECT xem.column_name column_name
904 ,xem.column_title PROMPT
905 ,utc.data_type data_type
906 FROM (SELECT t.table_name , t.column_name ,t.data_type
907 FROM user_tab_columns t , user_objects o
908 WHERE t.table_name = o.object_name
909 AND o.object_name = p_reporting_view_name
910 AND o.object_type <> 'SYNONYM'
911 UNION ALL
912 SELECT dt.table_name, dt.column_name , dt.data_type
913 FROM dba_tab_columns dt
914 WHERE (dt.table_name , dt.owner)
915 IN ( SELECT s.table_name , s.table_owner
916 FROM user_synonyms s , user_objects o
917 WHERE 1 = 1
918 AND o.object_name = p_reporting_view_name
919 AND o.object_type = 'SYNONYM'
920 AND s.synonym_name = o.object_name ) ) utc,
921 xla_event_mappings_vl xem
922 WHERE xem.application_id = p_application_id
923 AND xem.entity_code = p_entity_code
924 AND xem.event_class_code = p_event_class_code
925 AND utc.column_name = xem.column_name
926 ORDER BY xem.user_sequence)
927 LOOP
928
929 l_index := l_index + 1;
930
931
932
933 ----------------------------------------------------------------------
934 -- getting the PROMPTs to be displayed
935 ----------------------------------------------------------------------
936 --l_col_array(l_index).f1 := ''''||c1.PROMPT||'''';
937 l_col_array(l_index).f1 := ''''||REPLACE (c1.PROMPT, '''', '''''')||''''; --bug7567172
938
939 ----------------------------------------------------------------------
940 -- getting the columns to be displayed
941 ----------------------------------------------------------------------
942 IF c1.data_type = 'VARCHAR2' THEN
943 l_col_array(l_index).f2 := 'TIV.'|| c1.column_name;
944
945 ELSIF c1.data_type = 'DATE' THEN
946 l_col_array(l_index).f2 := 'to_char(TIV.'|| c1.column_name
947 ||',''YYYY-MM-DD"T"hh:mi:ss'')';
948 ELSE
949 l_col_array(l_index).f2 := 'to_char(TIV.'|| c1.column_name||')';
950 END IF;
951 END LOOP;
952 END IF;
953
954 ----------------------------------------------------------------------------
955 -- building the string to be added to the SELECT clause
956 ----------------------------------------------------------------------------
957 FOR i IN 1..l_col_array.count LOOP
958 l_col_string := l_col_string || ',' ||
959 l_col_array(i).f1||' USER_TRX_IDENTIFIER_NAME_' ||TO_CHAR(i)||','||
960 l_col_array(i).f2||' USER_TRX_IDENTIFIER_VALUE_' ||TO_CHAR(i);
961 END LOOP;
962 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
963 trace
964 (p_msg => 'l_col_string = '||l_col_string
965 ,p_level => C_LEVEL_STATEMENT
966 ,p_module => l_log_module);
967 END IF;
968 -----------------------------------------------------------------------------
969 -- Following tests whether the view and columns are defined in the data base
970 -----------------------------------------------------------------------------
971 IF p_reporting_view_name IS NOT NULL THEN
972 BEGIN
973 ----------------------------------------------------------------------
974 -- build and execute a dummy query if the view name is defined for
975 -- the class
976 -- NOTE: following never fails because the cursor joins to
977 -- user_tab_columns table that will make sure that view and column
978 -- names fetched exists. This can beremoved unless we decide to go
979 -- for outerjoin on this table.
980 ----------------------------------------------------------------------
981 l_sql_string :=
982 ' SELECT ' ||
983 ' NULL dummy ' ||
984 l_col_string ||
985 ' FROM ' ||
986 ' DUAL dual ' ||
987 l_view_name ||
988 ' WHERE ROWNUM = 1 ' ;
989
990
991 EXECUTE IMMEDIATE l_sql_string;
992
993 EXCEPTION
994 WHEN OTHERS THEN
995 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
996 trace
997 (p_msg => 'Technical Warning: There seems to a problem in retreiving '||
998 'transaction identifiers from '||p_reporting_view_name
999 ,p_level => C_LEVEL_EXCEPTION
1000 ,p_module => l_log_module);
1001 END IF;
1002
1003 ----------------------------------------------------------------------
1004 -- if the above query raises an exception following clears the FROM
1005 -- and WHERE strings and creates the error to be displayed to the user
1006 ----------------------------------------------------------------------
1007 l_col_array := l_null_col_array;
1008 l_col_string := NULL;
1009 l_col_array(1).f1 := '''Error''';
1010 l_col_array(1).f2 := '''Problem with Transaction Identifier View''';
1011 l_view_name := NULL;
1012 l_join_string := NULL;
1013
1014 FOR i IN 1..l_col_array.count LOOP
1015 l_col_string := l_col_string || ',' ||
1016 l_col_array(i).f1||' USER_TRX_IDENTIFIER_NAME_' ||TO_CHAR(i)||','||
1017 l_col_array(i).f2||' USER_TRX_IDENTIFIER_VALUE_' ||TO_CHAR(i);
1018 END LOOP;
1019
1020 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1021 trace
1022 (p_msg => 'l_col_string = '||l_col_string
1023 ,p_level => C_LEVEL_STATEMENT
1024 ,p_module => l_log_module);
1025 END IF;
1026 END;
1027 END IF;
1028
1029 p_select_str := l_col_string;
1030 p_from_str := l_view_name;
1031 p_where_str := l_join_string;
1032
1033 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1034 trace
1035 (p_msg => 'p_select_str = '||p_select_str
1036 ,p_level => C_LEVEL_PROCEDURE
1037 ,p_module => l_log_module);
1038 trace
1039 (p_msg => 'p_from_str = '||p_from_str
1040 ,p_level => C_LEVEL_PROCEDURE
1041 ,p_module => l_log_module);
1042 trace
1043 (p_msg => 'p_where_str = '||p_where_str
1044 ,p_level => C_LEVEL_PROCEDURE
1045 ,p_module => l_log_module);
1046 trace
1047 (p_msg => 'End of procedure GET_TRANSACTION_ID'
1048 ,p_level => C_LEVEL_PROCEDURE
1049 ,p_module => l_log_module);
1050 END IF;
1051
1052 EXCEPTION
1053 WHEN OTHERS THEN
1054 xla_exceptions_pkg.raise_message
1055 (p_location => 'xla_report_utility_pkg.get_transaction_id ');
1056 END get_transaction_id;
1057
1058 --=============================================================================
1059 --
1060 --
1061 --
1062 --=============================================================================
1063 PROCEDURE get_acct_qualifier_segs
1064 (p_coa_id IN NUMBER
1065 ,p_balance_segment OUT NOCOPY VARCHAR2
1066 ,p_account_segment OUT NOCOPY VARCHAR2
1067 ,p_cost_center_segment OUT NOCOPY VARCHAR2
1068 ,p_management_segment OUT NOCOPY VARCHAR2
1069 ,p_intercompany_segment OUT NOCOPY VARCHAR2) IS
1070
1071
1072 l_balance_segment VARCHAR2(80);
1073 l_account_segment VARCHAR2(80);
1074 l_cost_center_segment VARCHAR2(80);
1075 l_management_segment VARCHAR2(80);
1076 l_intercompany_segment VARCHAR2(80);
1077 l_log_module VARCHAR2(240);
1078
1079 BEGIN
1080 IF g_log_enabled THEN
1081 l_log_module := C_DEFAULT_MODULE||'.get_acct_qualifier_segs';
1082 END IF;
1083 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1084 trace
1085 (p_msg => 'BEGIN of procedure GET_ACCT_QUALIFIER_SEGS'
1086 ,p_level => C_LEVEL_PROCEDURE
1087 ,p_module => l_log_module);
1088 trace
1089 (p_msg => 'p_coa_id = '||p_coa_id
1090 ,p_level => C_LEVEL_PROCEDURE
1091 ,p_module => l_log_module);
1092 END IF;
1093
1094 BEGIN
1095 SELECT application_column_name
1096 INTO l_balance_segment
1097 FROM fnd_segment_attribute_values
1098 WHERE application_id = 101
1099 AND id_flex_code = 'GL#'
1100 AND id_flex_num = p_coa_id
1101 AND attribute_value = 'Y'
1102 AND segment_attribute_type = 'GL_BALANCING';
1103 EXCEPTION
1104 WHEN no_data_found THEN
1105 l_balance_segment := 'NULL';
1106 END;
1107
1108 BEGIN
1109 SELECT application_column_name
1110 INTO l_account_segment
1111 FROM fnd_segment_attribute_values
1112 WHERE application_id = 101
1113 AND id_flex_code = 'GL#'
1114 AND id_flex_num = p_coa_id
1115 AND attribute_value = 'Y'
1116 AND segment_attribute_type = 'GL_ACCOUNT';
1117 EXCEPTION
1118 WHEN no_data_found THEN
1119 l_account_segment := 'NULL';
1120 END;
1121
1122 BEGIN
1123 SELECT application_column_name
1124 INTO l_cost_center_segment
1125 FROM fnd_segment_attribute_values
1126 WHERE application_id = 101
1127 AND id_flex_code = 'GL#'
1128 AND id_flex_num = p_coa_id
1129 AND attribute_value = 'Y'
1130 AND segment_attribute_type = 'FA_COST_CTR';
1131 EXCEPTION
1132 WHEN no_data_found THEN
1133 l_cost_center_segment := 'NULL';
1134 END;
1135
1136 BEGIN
1137 SELECT application_column_name
1138 INTO l_management_segment
1139 FROM fnd_segment_attribute_values
1140 WHERE application_id = 101
1141 AND id_flex_code = 'GL#'
1142 AND id_flex_num = p_coa_id
1143 AND attribute_value = 'Y'
1144 AND segment_attribute_type = 'GL_MANAGEMENT';
1145 EXCEPTION
1146 WHEN no_data_found THEN
1147 l_management_segment := 'NULL';
1148 END;
1149
1150 BEGIN
1151 SELECT application_column_name
1152 INTO l_intercompany_segment
1153 FROM fnd_segment_attribute_values
1154 WHERE application_id = 101
1155 AND id_flex_code = 'GL#'
1156 AND id_flex_num = p_coa_id
1157 AND attribute_value = 'Y'
1158 AND segment_attribute_type = 'GL_INTERCOMPANY';
1159 EXCEPTION
1160 WHEN no_data_found THEN
1161 l_intercompany_segment := 'NULL';
1162 END;
1163
1164 p_intercompany_segment := l_intercompany_segment;
1165 p_management_segment := l_management_segment;
1166 p_cost_center_segment := l_cost_center_segment;
1167 p_account_segment := l_account_segment;
1168 p_balance_segment := l_balance_segment;
1169
1170
1171 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1172 trace
1173 (p_msg => 'p_intercompany_segment = '||p_intercompany_segment
1174 ,p_level => C_LEVEL_PROCEDURE
1175 ,p_module => l_log_module);
1176 trace
1177 (p_msg => 'p_management_segment = '||p_management_segment
1178 ,p_level => C_LEVEL_PROCEDURE
1179 ,p_module => l_log_module);
1180 trace
1181 (p_msg => 'p_cost_center_segment = '||p_cost_center_segment
1182 ,p_level => C_LEVEL_PROCEDURE
1183 ,p_module => l_log_module);
1184 trace
1185 (p_msg => 'p_account_segment = '||p_account_segment
1186 ,p_level => C_LEVEL_PROCEDURE
1187 ,p_module => l_log_module);
1188 trace
1189 (p_msg => 'p_balance_segment = '||p_balance_segment
1190 ,p_level => C_LEVEL_PROCEDURE
1191 ,p_module => l_log_module);
1192 trace
1193 (p_msg => 'End of procedure GET_ACCT_QUALIFIER_SEGS'
1194 ,p_level => C_LEVEL_PROCEDURE
1195 ,p_module => l_log_module);
1196 END IF;
1197 EXCEPTION
1198 WHEN OTHERS THEN
1199 xla_exceptions_pkg.raise_message
1200 (p_location => 'xla_report_utility_pkg.get_acct_qualifier_segs');
1201 END get_acct_qualifier_segs;
1202
1203
1204 --=============================================================================
1205 --
1206 -- Function to get concenated description of accounting flex field
1207 --
1208 --=============================================================================
1209 FUNCTION get_ccid_desc
1210 (p_coa_id IN NUMBER
1211 ,p_ccid IN NUMBER)
1212 RETURN VARCHAR2 IS
1213 l_ccid_desc VARCHAR2(2000);
1214 l_log_module VARCHAR2(240);
1215 BEGIN
1216 IF g_log_enabled THEN
1217 l_log_module := C_DEFAULT_MODULE||'.get_ccid_desc';
1218 END IF;
1219 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1220 trace
1221 (p_msg => 'BEGIN of procedure GET_CCID_DESC'
1222 ,p_level => C_LEVEL_PROCEDURE
1223 ,p_module => l_log_module);
1224 trace
1225 (p_msg => 'p_coa_id = '||p_coa_id
1226 ,p_level => C_LEVEL_PROCEDURE
1227 ,p_module => l_log_module);
1228 trace
1229 (p_msg => 'p_ccid = '||p_ccid
1230 ,p_level => C_LEVEL_PROCEDURE
1231 ,p_module => l_log_module);
1232 END IF;
1233
1234 --returning ccid desc bug#9323360 via caching
1235 IF g_t_cache_coa_id.EXISTS(p_coa_id) THEN
1236 IF g_t_cache_coa_id(p_coa_id).EXISTS(p_ccid) THEN
1237 RETURN g_t_cache_coa_id(p_coa_id)(p_ccid);
1238 END IF;
1239 END IF;
1240 --returning ccid desc bug#9323360
1241
1242
1243 IF fnd_flex_keyval.validate_ccid
1244 ('SQLGL','GL#',p_coa_id,p_ccid) = TRUE
1245 THEN
1246 l_ccid_desc := fnd_flex_keyval.concatenated_descriptions();
1247
1248 -- bug#9323360
1249 -- used nested tables indexed by binary_integer. g_t_cache_coa_id is the parent table
1250 -- having child as description
1251 -- populate using hash key p_coa_id and p_ccid
1252
1253 g_t_cache_coa_id(p_coa_id)(p_ccid) := l_ccid_desc;
1254
1255 --bug#9323360
1256
1257 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1258 trace
1259 (p_msg => 'l_ccid_desc = '||l_ccid_desc
1260 ,p_level => C_LEVEL_PROCEDURE
1261 ,p_module => l_log_module);
1262 trace
1263 (p_msg => 'END of procedure GET_CCID_DESC'
1264 ,p_level => C_LEVEL_PROCEDURE
1265 ,p_module => l_log_module);
1266 END IF;
1267
1268 RETURN l_ccid_desc;
1269 ELSE
1270 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1271 trace
1272 (p_msg => 'l_ccid_desc = '
1273 ,p_level => C_LEVEL_PROCEDURE
1274 ,p_module => l_log_module);
1275 trace
1276 (p_msg => 'END of procedure GET_CCID_DESC'
1277 ,p_level => C_LEVEL_PROCEDURE
1278 ,p_module => l_log_module);
1279 END IF;
1280
1281 RETURN NULL;
1282 END IF;
1283 EXCEPTION
1284 WHEN OTHERS THEN
1285 xla_exceptions_pkg.raise_message
1286 (p_location => 'xla_report_utility_pkg.get_ccid_desc');
1287 END get_ccid_desc;
1288
1289
1290 --=============================================================================
1291 --
1292 -- Body for the procedure clob_to_file
1293 --
1294 --=============================================================================
1295 PROCEDURE clob_to_file
1296 (p_xml_clob IN CLOB) IS
1297
1298 l_clob_size NUMBER;
1299 l_offset NUMBER;
1300 l_chunk_size INTEGER;
1301 l_chunk VARCHAR2(32767);
1302 l_log_module VARCHAR2(240);
1303
1304 BEGIN
1305 IF g_log_enabled THEN
1306 l_log_module := C_DEFAULT_MODULE||'.clob_to_file';
1307 END IF;
1308 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1309 trace
1310 (p_msg => 'BEGIN of procedure CLOB_TO_FILE'
1311 ,p_level => C_LEVEL_PROCEDURE
1312 ,p_module => l_log_module);
1313 END IF;
1314
1315 l_clob_size := dbms_lob.getlength(p_xml_clob);
1316
1317 IF (l_clob_size = 0) THEN
1318 RETURN;
1319 END IF;
1320 l_offset := 1;
1321 l_chunk_size := 3000;
1322
1323 WHILE (l_clob_size > 0) LOOP
1324 l_chunk := dbms_lob.substr (p_xml_clob, l_chunk_size, l_offset);
1325 fnd_file.put
1326 (which => fnd_file.output
1327 ,buff => l_chunk);
1328
1329 l_clob_size := l_clob_size - l_chunk_size;
1330 l_offset := l_offset + l_chunk_size;
1331 END LOOP;
1332
1333 fnd_file.new_line(fnd_file.output,1);
1334
1335 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1336 trace
1337 (p_msg => 'END of procedure CLOB_TO_FILE'
1338 ,p_level => C_LEVEL_PROCEDURE
1339 ,p_module => l_log_module);
1340 END IF;
1341
1342 EXCEPTION
1343 WHEN OTHERS THEN
1344 xla_exceptions_pkg.raise_message
1345 (p_location => 'xla_report_utility_pkg.clob_to_file');
1346 END clob_to_file;
1347
1348
1349 --=============================================================================
1350 --
1351 -- Function to get condtions based on analytical detail values
1352 --
1353 --=============================================================================
1354 FUNCTION get_anc_filter
1355 (p_anc_level IN VARCHAR2
1356 ,p_table_alias IN VARCHAR2
1357 ,p_anc_detail_code IN VARCHAR2
1358 ,p_anc_detail_value IN VARCHAR2)
1359 RETURN VARCHAR2 IS
1360 l_column_name VARCHAR2(80);
1361 l_string VARCHAR2(2000);
1362 l_log_module VARCHAR2(240);
1363
1364 BEGIN
1365 IF g_log_enabled THEN
1366 l_log_module := C_DEFAULT_MODULE||'.get_anc_filter';
1367 END IF;
1368 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1369 trace
1370 (p_msg => 'BEGIN of function GET_ANC_FILTER'
1371 ,p_level => C_LEVEL_PROCEDURE
1372 ,p_module => l_log_module);
1373 trace
1374 (p_msg => 'p_anc_level = '||p_anc_level
1375 ,p_level => C_LEVEL_PROCEDURE
1376 ,p_module => l_log_module);
1377 trace
1378 (p_msg => 'p_table_alias = '||p_table_alias
1379 ,p_level => C_LEVEL_PROCEDURE
1380 ,p_module => l_log_module);
1381 trace
1382 (p_msg => 'p_anc_detail_code = '||p_anc_detail_code
1383 ,p_level => C_LEVEL_PROCEDURE
1384 ,p_module => l_log_module);
1385 trace
1386 (p_msg => 'p_anc_detail_value = '||p_anc_detail_value
1387 ,p_level => C_LEVEL_PROCEDURE
1388 ,p_module => l_log_module);
1389 END IF;
1390
1391 SELECT
1392 CASE data_type_code
1393 WHEN 'C' THEN 'ADV.ANALYTICAL_DETAIL_CHAR_'||TO_CHAR(grouping_order)
1394 WHEN 'D' THEN 'ADV.ANALYTICAL_DETAIL_DATE_'||TO_CHAR(grouping_order)
1395 WHEN 'N' THEN 'ADV.ANALYTICAL_DETAIL_NUMBER_'||TO_CHAR(grouping_order)
1396 ELSE NULL
1397 END CASE
1398 INTO l_column_name
1399 FROM xla_analytical_dtls_b
1400 WHERE analytical_Detail_code = p_anc_detail_code;
1401
1402 IF p_anc_level = 'H' THEN
1403 l_string := ' and exist ( '||
1404 ' select 1 from xla_ae_header_details ahd, xla_analytical_dtl_vals adv '||
1405 ' where ahd.ae_header_id = '||p_table_alias||'.ae_header_id '||
1406 ' and adv.analytical_detail_value_id = ahd.analytical_detail_value_id '||
1407 ' and adv.'||l_column_name||' = '''||p_anc_detail_value||''''||
1408 ' )';
1409 ELSIF p_anc_level = 'L' THEN
1410 l_string := ' and exist ( '||
1411 ' select 1 from xla_ae_line_details ald, xla_analytical_dtl_vals adv '||
1412 ' where ald.ae_header_id = '||p_table_alias||'.ae_header_id '||
1413 ' and ald.ae_line_num = '||p_table_alias||'.ae_line_num '||
1414 ' and adv.analytical_detail_value_id = ald.analytical_detail_value_id '||
1415 ' and adv.'||l_column_name||' = '''||p_anc_detail_value||''''||
1416 ' )';
1417 END IF;
1418
1419 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1420 trace
1421 (p_msg => 'END of function GET_ANC_FILTER'
1422 ,p_level => C_LEVEL_PROCEDURE
1423 ,p_module => l_log_module);
1424 END IF;
1425
1426 RETURN l_string;
1427
1428 EXCEPTION
1429 WHEN OTHERS THEN
1430 xla_exceptions_pkg.raise_message
1431 (p_location => 'xla_report_utility_pkg.get_anc_filter');
1432 END get_anc_filter;
1433
1434
1435 --=============================================================================
1436 --
1437 --
1438 --
1439 --=============================================================================
1440 FUNCTION is_primary_ledger (p_ledger_id IN NUMBER)
1441 RETURN NUMBER IS
1442
1443 l_ledger_id gl_ledgers.ledger_id%type;
1444
1445 BEGIN
1446
1447 select ledger_id
1448 into l_ledger_id
1449 from gl_ledgers
1450 where ledger_category_code = 'PRIMARY'
1451 and ledger_id = p_ledger_id
1452 and rownum = 1;
1453
1454 RETURN l_ledger_id;
1455
1456 EXCEPTION
1457 WHEN NO_DATA_FOUND THEN
1458 RETURN NULL;
1459 WHEN OTHERS THEN
1460 xla_exceptions_pkg.raise_message
1461 (p_location => 'xla_report_utility_pkg.unravel_ledger (fn)');
1462 END is_primary_ledger;
1463
1464
1465 --=============================================================================
1466 --
1467 --
1468 --
1469 --=============================================================================
1470 FUNCTION get_ledger_id (p_ledger_id IN NUMBER)
1471 RETURN NUMBER IS
1472
1473 l_object_type_code gl_ledgers.object_type_code%TYPE;
1474 l_ledger_id gl_ledgers.ledger_id%TYPE;
1475 l_ledger_id_out gl_ledgers.ledger_id%TYPE;
1476
1477 BEGIN
1478
1479 l_object_type_code := xla_report_utility_pkg.
1480 get_ledger_object_type(p_ledger_id);
1481
1482 IF l_object_type_code = 'L' THEN
1483
1484 select distinct primary_ledger_id
1485 into l_ledger_id_out
1486 from xla_ledger_relationships_v
1487 where ledger_id = p_ledger_id;
1488
1489
1490
1491 ELSIF l_object_type_code = 'S' THEN
1492
1493 select ledger_id
1494 into l_ledger_id
1495 from gl_ledger_set_assignments glsa
1496 where glsa.ledger_id <> p_ledger_id
1497 and glsa.ledger_set_id = p_ledger_id
1498 and rownum = 1;
1499
1500 select distinct primary_ledger_id
1501 into l_ledger_id_out
1502 from xla_ledger_relationships_v
1503 where ledger_id = l_ledger_id;
1504
1505 END IF;
1506
1507 RETURN l_ledger_id_out;
1508
1509 EXCEPTION
1510 WHEN OTHERS THEN
1511 xla_exceptions_pkg.raise_message
1512 (p_location => 'xla_report_utility_pkg.unravel_ledger (fn)');
1513 END get_ledger_id;
1514
1515 --=============================================================================
1516 --
1517 --
1518 --
1519 --=============================================================================
1520 FUNCTION get_ledger_object_type (p_ledger_id IN NUMBER)
1521 RETURN VARCHAR2 IS
1522
1523 l_object_type_code gl_ledgers.object_type_code%TYPE;
1524
1525 BEGIN
1526
1527 SELECT object_type_code
1528 INTO l_object_type_code
1529 FROM gl_ledgers
1530 WHERE ledger_id = p_ledger_id;
1531
1532 RETURN l_object_type_code;
1533
1534 EXCEPTION
1535 WHEN OTHERS THEN
1536 xla_exceptions_pkg.raise_message
1537 (p_location => 'xla_report_utility_pkg.unravel_ledger (fn)');
1538 END get_ledger_object_type;
1539
1540
1541 --=============================================================================
1542 --
1543 --
1544 --
1545 --=============================================================================
1546 FUNCTION get_transaction_id
1547 (p_resp_application_id IN NUMBER
1548 ,p_ledger_id IN NUMBER ) RETURN VARCHAR2 IS
1549 CURSOR cur_event_class IS
1550 (SELECT DISTINCT
1551 xet.application_id APPLICATION_ID
1552 ,xet.entity_code ENTITY_CODE
1553 ,xet.event_class_code EVENT_CLASS_CODE
1554 ,xeca.reporting_view_name REPORTING_VIEW_NAME
1555 FROM xla_event_types_b xet
1556 ,xla_event_class_attrs xeca
1557 WHERE xeca.entity_code = xet.entity_code
1558 AND xeca.event_class_code = xet.event_class_code
1559 AND xeca.application_id = p_resp_application_id
1560 AND xet.application_id = xeca.application_id) ; --added for bug 7688085,7707717, removed xah for bug 12663084
1561
1562 l_col_array t_array;
1563 l_null_col_array t_array;
1564 l_trx_id_str VARCHAR2(32000);
1565 l_col_string VARCHAR2(4000) := NULL;
1566 l_view_name VARCHAR2(800);
1567 l_join_string VARCHAR2(4000) := NULL;
1568 l_sql_string VARCHAR2(4000) := NULL;
1569 l_index INTEGER;
1570 l_outerjoin VARCHAR2(30);
1571 l_log_module VARCHAR2(240);
1572
1573 BEGIN
1574 IF g_log_enabled THEN
1575 l_log_module := C_DEFAULT_MODULE||'.get_transaction_id';
1576 END IF;
1577 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1578 trace
1579 (p_msg => 'BEGIN of function GET_TRANSACTION_ID'
1580 ,p_level => C_LEVEL_PROCEDURE
1581 ,p_module => l_log_module);
1582 trace
1583 (p_msg => 'p_application_id = '||p_resp_application_id
1584 ,p_level => C_LEVEL_PROCEDURE
1585 ,p_module => l_log_module);
1586 trace
1587 (p_msg => 'p_ledger_id = '||p_ledger_id
1588 ,p_level => C_LEVEL_PROCEDURE
1589 ,p_module => l_log_module);
1590 END IF;
1591 l_trx_id_str := ',CASE WHEN 1<1 THEN NULL';
1592
1593 --
1594 -- creating a dummy array that contains "NULL" strings
1595 --
1596 FOR i IN 1..10 LOOP
1597 l_null_col_array(i).f1 := 'NULL';
1598 l_null_col_array(i).f2 := 'NULL';
1599 END LOOP;
1600
1601 FOR cur_trx IN cur_event_class LOOP
1602 l_col_string := NULL;
1603 l_view_name := NULL;
1604 l_join_string := NULL;
1605
1606 IF cur_trx.entity_code NOT IN('MANUAL','THIRD_PARTY_MERGE') THEN
1607 --
1608 -- initiating the array that contains name of the columns to be selected
1609 -- from the TID View.
1610 --
1611 l_col_array := l_null_col_array;
1612 l_index := 0;
1613
1614 --
1615 -- creating SELECT,FROM and WHERE clause strings when the reporting view is
1616 -- defined for an Event Class.
1617 --
1618 -- Split the join between Entity Mapping and Event Mappings as Report Ends in Error
1619 -- with SQL Syntax erro when User Transaction Identifiers are nor provided in
1620 -- Accounting Event Class Options Window (Added for Bug 14063513)
1621
1622 IF cur_trx.reporting_view_name IS NOT NULL THEN
1623 --
1624 -- creating string to be added to FROM clause
1625 --
1626 l_view_name := cur_trx.reporting_view_name || ' TIV';
1627 FOR cols_csr IN
1628 (SELECT xid.transaction_id_col_name_1 trx_col_1
1629 ,xid.transaction_id_col_name_2 trx_col_2
1630 ,xid.transaction_id_col_name_3 trx_col_3
1631 ,xid.transaction_id_col_name_4 trx_col_4
1632 ,xid.source_id_col_name_1 src_col_1
1633 ,xid.source_id_col_name_2 src_col_2
1634 ,xid.source_id_col_name_3 src_col_3
1635 ,xid.source_id_col_name_4 src_col_4
1636 -- ,xem.column_name column_name
1637 -- ,xem.column_title PROMPT
1638 -- ,utc.data_type data_type
1639 FROM xla_entity_id_mappings xid
1640 -- ,xla_event_mappings_vl xem
1641 -- ,user_tab_columns utc
1642 WHERE xid.application_id = cur_trx.application_id
1643 AND xid.entity_code = cur_trx.entity_code)
1644 -- AND xem.application_id = cur_trx.application_id
1645 -- AND xem.entity_code = cur_trx.entity_code
1646 -- AND xem.event_class_code = cur_trx.event_class_code
1647 -- AND utc.table_name = cur_trx.reporting_view_name
1648 -- AND utc.column_name = xem.column_name
1649 --ORDER BY xem.user_sequence)
1650 LOOP
1651
1652 l_index := l_index + 1;
1653 --
1654 -- creating string to be added to WHERE clause
1655 --
1656 IF l_index = 1 THEN
1657 -----------------------------------------------------------------
1658 -- Bug 3389175
1659 -- Following logic is build to make sure all events are reported
1660 -- if debug is enabled evenif there is no data for the event in the
1661 -- transaction id view.
1662 -- if log enabled then
1663 -- outer join to TID view
1664 -- endif
1665 -----------------------------------------------------------------
1666 IF g_log_level <> C_LEVEL_LOG_DISABLED THEN
1667 l_outerjoin := '(+)';
1668 ELSE
1669 l_outerjoin := NULL;
1670 END IF;
1671
1672 IF cols_csr.trx_col_1 IS NOT NULL THEN
1673 l_join_string := l_join_string ||
1674 ' TIV.'|| cols_csr.trx_col_1 ||l_outerjoin ||
1675 ' = ENT.'|| cols_csr.src_col_1;
1676 END IF;
1677 IF cols_csr.trx_col_2 IS NOT NULL THEN
1678 l_join_string := l_join_string ||
1679 ' AND TIV.'|| cols_csr.trx_col_2 ||l_outerjoin ||
1680 ' = ENT.'|| cols_csr.src_col_2;
1681 END IF;
1682 IF cols_csr.trx_col_3 IS NOT NULL THEN
1683 l_join_string := l_join_string ||
1684 ' AND TIV.'|| cols_csr.trx_col_3 ||l_outerjoin ||
1685 ' = ENT.'|| cols_csr.src_col_3;
1686 END IF;
1687 IF cols_csr.trx_col_4 IS NOT NULL THEN
1688 l_join_string := l_join_string ||
1689 ' AND TIV.'|| cols_csr.trx_col_4 ||l_outerjoin ||
1690 ' = ENT.'|| cols_csr.src_col_4;
1691 END IF;
1692 END IF;
1693 END LOOP;
1694 l_index := 0;
1695 --Check if the object is synonym or not.(Added and commented for Bug 14063513)
1696 FOR cols_csr IN
1697 (SELECT xem.column_name column_name
1698 ,xem.column_title PROMPT
1699 ,utc.data_type data_type
1700 FROM (SELECT t.table_name , t.column_name ,t.data_type
1701 FROM user_tab_columns t , user_objects o
1702 WHERE t.table_name = o.object_name
1703 AND o.object_name = cur_trx.reporting_view_name
1704 AND o.object_type <> 'SYNONYM'
1705 UNION ALL
1706 SELECT dt.table_name, dt.column_name , dt.data_type
1707 FROM dba_tab_columns dt
1708 WHERE (dt.table_name , dt.owner)
1709 IN ( SELECT s.table_name , s.table_owner
1710 FROM user_synonyms s , user_objects o
1711 WHERE 1 = 1
1712 AND o.object_name = cur_trx.reporting_view_name
1713 AND o.object_type = 'SYNONYM'
1714 AND s.synonym_name = o.object_name ) ) utc,
1715 xla_event_mappings_vl xem
1716 WHERE xem.application_id = cur_trx.application_id
1717 AND xem.entity_code = cur_trx.entity_code
1718 AND xem.event_class_code = cur_trx.event_class_code
1719 AND utc.column_name = xem.column_name
1720 ORDER BY xem.user_sequence)
1721
1722 LOOP
1723
1724 l_index := l_index + 1;
1725 --
1726 -- getting the PROMPTs to be displayed
1727 -- Bug 5360816. Added REPLACE to handle apostophe in user prompts.
1728 --
1729 l_col_array(l_index).f1 := ''''||REPLACE(cols_csr.PROMPT,'''','''''')||'''';
1730
1731 ---
1732 -- getting the columns to be displayed
1733 ---
1734 IF cols_csr.data_type = 'VARCHAR2' THEN
1735 l_col_array(l_index).f2 := 'TIV.'|| cols_csr.column_name;
1736
1737 ELSIF cols_csr.data_type = 'DATE' THEN
1738 l_col_array(l_index).f2 := 'to_char(TIV.'|| cols_csr.column_name
1739 ||',''YYYY-MM-DD"T"hh:mi:ss'')';
1740 ELSE
1741 l_col_array(l_index).f2 := 'to_char(TIV.'|| cols_csr.column_name||')';
1742 END IF;
1743 END LOOP;
1744 END IF;
1745 --------------------------------------------------------------------------
1746 -- building the string to be added to the SELECT clause
1747 --------------------------------------------------------------------------
1748 IF l_index > 0 THEN
1749 l_col_string := l_col_string ||
1750 l_col_array(1).f1||'||''|''||'||
1751 l_col_array(1).f2;
1752
1753 FOR i IN 2..l_col_array.count LOOP
1754 l_col_string := l_col_string ||'||''|''||'||
1755 l_col_array(i).f1||'||''|''||'||
1756 l_col_array(i).f2;
1757 END LOOP;
1758
1759 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1760 trace
1761 (p_msg => 'l_col_string = '||l_col_string
1762 ,p_level => C_LEVEL_STATEMENT
1763 ,p_module => l_log_module);
1764 END IF;
1765
1766 l_trx_id_str := l_trx_id_str||
1767 ' WHEN xet.event_class_code = '''||
1768 cur_trx.event_class_code||
1769 ''' THEN ( SELECT '||l_col_string||
1770 ' FROM '||l_view_name ||' WHERE '|| l_join_string ||')';
1771 END IF;
1772 END IF;
1773 END LOOP;
1774
1775 l_trx_id_str := l_trx_id_str ||' END ';
1776 RETURN l_trx_id_str;
1777
1778 EXCEPTION
1779 WHEN OTHERS THEN
1780 xla_exceptions_pkg.raise_message
1781 (p_location => 'xla_report_utility_pkg.get_transaction_id ');
1782
1783 END get_transaction_id;
1784
1785
1786
1787 -------------------------------------------------------------------------------
1788 ------------------------------------------------------------------------------
1789 -- Added for bug 7580995
1790
1791 PROCEDURE get_transaction_id(p_resp_application_id IN NUMBER
1792 ,p_ledger_id IN NUMBER
1793 ,p_trx_identifiers_1 OUT NOCOPY VARCHAR2
1794 ,p_trx_identifiers_2 OUT NOCOPY VARCHAR2
1795 ,p_trx_identifiers_3 OUT NOCOPY VARCHAR2
1796 ,p_trx_identifiers_4 OUT NOCOPY VARCHAR2
1797 ,p_trx_identifiers_5 OUT NOCOPY VARCHAR2) IS
1798 CURSOR cur_event_class IS
1799 (SELECT DISTINCT
1800 xet.application_id APPLICATION_ID
1801 ,xet.entity_code ENTITY_CODE
1802 ,xet.event_class_code EVENT_CLASS_CODE
1803 ,xeca.reporting_view_name REPORTING_VIEW_NAME
1804 FROM xla_event_types_b xet
1805 ,xla_event_class_attrs xeca
1806 WHERE xeca.entity_code = xet.entity_code
1807 AND xeca.event_class_code = xet.event_class_code
1808 AND xeca.application_id = p_resp_application_id
1809 AND xet.application_id = xeca.application_id) ; --added for bug 7688085,7707717, removed xah for bug 12663084
1810
1811 l_col_array t_array;
1812 l_null_col_array t_array;
1813 l_trx_id_str VARCHAR2(32000);
1814 l_col_string VARCHAR2(4000) := NULL;
1815 l_view_name VARCHAR2(800);
1816 l_join_string VARCHAR2(4000) := NULL;
1817 l_sql_string VARCHAR2(4000) := NULL;
1818 l_index INTEGER;
1819 l_outerjoin VARCHAR2(30);
1820 l_log_module VARCHAR2(240);
1821 l_trx_id_str_temp VARCHAR2(32000):=NULL;
1822 l_id_num number:=1;
1823
1824 BEGIN
1825
1826 p_trx_identifiers_1 := ' ';
1827 p_trx_identifiers_2 := ' ';
1828 p_trx_identifiers_3 := ' ';
1829 p_trx_identifiers_4 := ' ';
1830 p_trx_identifiers_5 := ' ';
1831
1832 IF g_log_enabled THEN
1833 l_log_module := C_DEFAULT_MODULE||'.get_transaction_id';
1834 END IF;
1835 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1836 trace
1837 (p_msg => 'BEGIN of function GET_TRANSACTION_ID'
1838 ,p_level => C_LEVEL_PROCEDURE
1839 ,p_module => l_log_module);
1840 trace
1841 (p_msg => 'p_application_id = '||p_resp_application_id
1842 ,p_level => C_LEVEL_PROCEDURE
1843 ,p_module => l_log_module);
1844 trace
1845 (p_msg => 'p_ledger_id = '||p_ledger_id
1846 ,p_level => C_LEVEL_PROCEDURE
1847 ,p_module => l_log_module);
1848 END IF;
1849 l_trx_id_str := ',CASE WHEN 1<1 THEN NULL';
1850
1851 --
1852 -- creating a dummy array that contains "NULL" strings
1853 --
1854 FOR i IN 1..10 LOOP
1855 l_null_col_array(i).f1 := 'NULL';
1856 l_null_col_array(i).f2 := 'NULL';
1857 END LOOP;
1858
1859 FOR cur_trx IN cur_event_class LOOP
1860 l_col_string := NULL;
1861 l_view_name := NULL;
1862 l_join_string := NULL;
1863
1864 IF cur_trx.entity_code NOT IN('MANUAL','THIRD_PARTY_MERGE') THEN
1865 --
1866 -- initiating the array that contains name of the columns to be selected
1867 -- from the TID View.
1868 --
1869 l_col_array := l_null_col_array;
1870 l_index := 0;
1871
1872 --
1873 -- creating SELECT,FROM and WHERE clause strings when the reporting view is
1874 -- defined for an Event Class.
1875 --
1876 -- Split the join between Entity Mapping and Event Mappings as Report Ends in Error
1877 -- with SQL Syntax error when User Transaction Identifiers are not provided in
1878 -- Accounting Event Class Options Window (Added for Bug 14063513)
1879
1880 IF cur_trx.reporting_view_name IS NOT NULL THEN
1881 --
1882 -- creating string to be added to FROM clause
1883 --
1884 l_view_name := cur_trx.reporting_view_name || ' TIV';
1885 FOR cols_csr IN
1886 (SELECT xid.transaction_id_col_name_1 trx_col_1
1887 ,xid.transaction_id_col_name_2 trx_col_2
1888 ,xid.transaction_id_col_name_3 trx_col_3
1889 ,xid.transaction_id_col_name_4 trx_col_4
1890 ,xid.source_id_col_name_1 src_col_1
1891 ,xid.source_id_col_name_2 src_col_2
1892 ,xid.source_id_col_name_3 src_col_3
1893 ,xid.source_id_col_name_4 src_col_4
1894 -- ,xem.column_name column_name
1895 -- ,xem.column_title PROMPT
1896 -- ,utc.data_type data_type
1897 FROM xla_entity_id_mappings xid
1898 -- ,xla_event_mappings_vl xem
1899 -- ,user_tab_columns utc
1900 WHERE xid.application_id = cur_trx.application_id
1901 AND xid.entity_code = cur_trx.entity_code)
1902 -- AND xem.application_id = cur_trx.application_id
1903 -- AND xem.entity_code = cur_trx.entity_code
1904 -- AND xem.event_class_code = cur_trx.event_class_code
1905 -- AND utc.table_name = cur_trx.reporting_view_name
1906 -- AND utc.column_name = xem.column_name
1907 -- ORDER BY xem.user_sequence)
1908 LOOP
1909
1910 l_index := l_index + 1;
1911 --
1912 -- creating string to be added to WHERE clause
1913 --
1914 IF l_index = 1 THEN
1915 -----------------------------------------------------------------
1916 -- Bug 3389175
1917 -- Following logic is build to make sure all events are reported
1918 -- if debug is enabled evenif there is no data for the event in the
1919 -- transaction id view.
1920 -- if log enabled then
1921 -- outer join to TID view
1922 -- endif
1923 -----------------------------------------------------------------
1924 IF g_log_level <> C_LEVEL_LOG_DISABLED THEN
1925 l_outerjoin := '(+)';
1926 ELSE
1927 l_outerjoin := NULL;
1928 END IF;
1929
1930 IF cols_csr.trx_col_1 IS NOT NULL THEN
1931 l_join_string := l_join_string ||
1932 ' TIV.'|| cols_csr.trx_col_1 ||l_outerjoin ||
1933 ' = ENT.'|| cols_csr.src_col_1;
1934 END IF;
1935 IF cols_csr.trx_col_2 IS NOT NULL THEN
1936 l_join_string := l_join_string ||
1937 ' AND TIV.'|| cols_csr.trx_col_2 ||l_outerjoin ||
1938 ' = ENT.'|| cols_csr.src_col_2;
1939 END IF;
1940 IF cols_csr.trx_col_3 IS NOT NULL THEN
1941 l_join_string := l_join_string ||
1942 ' AND TIV.'|| cols_csr.trx_col_3 ||l_outerjoin ||
1943 ' = ENT.'|| cols_csr.src_col_3;
1944 END IF;
1945 IF cols_csr.trx_col_4 IS NOT NULL THEN
1946 l_join_string := l_join_string ||
1947 ' AND TIV.'|| cols_csr.trx_col_4 ||l_outerjoin ||
1948 ' = ENT.'|| cols_csr.src_col_4;
1949 END IF;
1950 END IF;
1951 END LOOP;
1952 l_index := 0;
1953 --Check if the object is synonym or not.(Added and commented for Bug 14063513)
1954 FOR cols_csr IN
1955 (SELECT xem.column_name column_name
1956 ,xem.column_title PROMPT
1957 ,utc.data_type data_type
1958 FROM (SELECT t.table_name , t.column_name ,t.data_type
1959 FROM user_tab_columns t , user_objects o
1960 WHERE t.table_name = o.object_name
1961 AND o.object_name = cur_trx.reporting_view_name
1962 AND o.object_type <> 'SYNONYM'
1963 UNION ALL
1964 SELECT dt.table_name, dt.column_name , dt.data_type
1965 FROM dba_tab_columns dt
1966 WHERE (dt.table_name , dt.owner)
1967 IN ( SELECT s.table_name , s.table_owner
1968 FROM user_synonyms s , user_objects o
1969 WHERE 1 = 1
1970 AND o.object_name = cur_trx.reporting_view_name
1971 AND o.object_type = 'SYNONYM'
1972 AND s.synonym_name = o.object_name ) ) utc,
1973 xla_event_mappings_vl xem
1974 WHERE xem.application_id = cur_trx.application_id
1975 AND xem.entity_code = cur_trx.entity_code
1976 AND xem.event_class_code = cur_trx.event_class_code
1977 AND utc.column_name = xem.column_name
1978 ORDER BY xem.user_sequence)
1979
1980 LOOP
1981
1982 l_index := l_index + 1;
1983 --
1984 -- getting the PROMPTs to be displayed
1985 -- Bug 5360816. Added REPLACE to handle apostophe in user prompts.
1986 --
1987 l_col_array(l_index).f1 := ''''||REPLACE(cols_csr.PROMPT,'''','''''')||'''';
1988
1989 ---
1990 -- getting the columns to be displayed
1991 ---
1992 IF cols_csr.data_type = 'VARCHAR2' THEN
1993 l_col_array(l_index).f2 := 'TIV.'|| cols_csr.column_name;
1994
1995 ELSIF cols_csr.data_type = 'DATE' THEN
1996 l_col_array(l_index).f2 := 'to_char(TIV.'|| cols_csr.column_name
1997 ||',''YYYY-MM-DD"T"hh:mi:ss'')';
1998 ELSE
1999 --Bug 12833717, added fnd_number.number_to_canonical
2000 --l_col_array(l_index).f2 := 'to_char(TIV.'|| cols_csr.column_name||')';
2001 l_col_array(l_index).f2 := 'fnd_number.number_to_canonical(TIV.'||cols_csr.column_name||')';
2002 END IF;
2003 END LOOP;
2004 END IF;
2005 --------------------------------------------------------------------------
2006 -- building the string to be added to the SELECT clause
2007 --------------------------------------------------------------------------
2008 IF l_index > 0 THEN
2009 l_col_string := l_col_string ||
2010 l_col_array(1).f1||'||''|''||'||
2011 l_col_array(1).f2;
2012
2013 FOR i IN 2..l_col_array.count LOOP
2014 l_col_string := l_col_string ||'||''|''||'||
2015 l_col_array(i).f1||'||''|''||'||
2016 l_col_array(i).f2;
2017 END LOOP;
2018
2019
2020 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2021 trace
2022 (p_msg => 'length of l_col_string = '||length(l_col_string)
2023 ,p_level => C_LEVEL_STATEMENT
2024 ,p_module => l_log_module);
2025 END IF;
2026
2027 l_trx_id_str_temp := l_trx_id_str||
2028 ' WHEN xet.event_class_code = '''||
2029 cur_trx.event_class_code||
2030 ''' THEN ( SELECT '||l_col_string||
2031 ' FROM '||l_view_name ||' WHERE '|| l_join_string ||')';
2032 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2033 trace
2034 (p_msg => 'length of l_trx_id_str_temp = '||length(l_trx_id_str_temp)
2035 ,p_level => C_LEVEL_STATEMENT
2036 ,p_module => l_log_module);
2037 END IF;
2038
2039 IF length(l_trx_id_str_temp)<=25000 then
2040 l_trx_id_str := l_trx_id_str_temp;
2041
2042 ELSE
2043 IF l_id_num = 1 then
2044 p_trx_identifiers_1 := l_trx_id_str;
2045 l_trx_id_str_temp:=NULL;
2046 l_trx_id_str:=NULL;
2047 END IF;
2048 IF l_id_num = 2 then
2049 p_trx_identifiers_2 := l_trx_id_str;
2050 l_trx_id_str_temp:=NULL;
2051 l_trx_id_str:=NULL;
2052 END IF;
2053 IF l_id_num = 3 then
2054 p_trx_identifiers_3 := l_trx_id_str;
2055 l_trx_id_str_temp:=NULL;
2056 l_trx_id_str:=NULL;
2057 END IF;
2058 IF l_id_num = 4 then
2059 p_trx_identifiers_4 := l_trx_id_str;
2060 l_trx_id_str_temp:=NULL;
2061 l_trx_id_str:=NULL;
2062 END IF;
2063 IF l_id_num = 5 then
2064 p_trx_identifiers_5 := l_trx_id_str;
2065 l_trx_id_str_temp:=NULL;
2066 l_trx_id_str:=NULL;
2067 END IF;
2068 l_trx_id_str_temp := ' WHEN xet.event_class_code = '''||
2069 cur_trx.event_class_code||
2070 ''' THEN ( SELECT '||l_col_string||
2071 ' FROM '||l_view_name ||' WHERE '|| l_join_string ||')';
2072
2073 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2074 trace
2075 (p_msg => 'inside length of l_trx_id_str_temp = '||length(l_trx_id_str_temp)
2076 ,p_level => C_LEVEL_STATEMENT
2077 ,p_module => l_log_module);
2078 END IF;
2079
2080 l_id_num := l_id_num + 1;
2081
2082 END IF;
2083
2084
2085
2086
2087 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2088 trace
2089 (p_msg => 'length of l_trx_id_str = '||length(l_trx_id_str)
2090 ,p_level => C_LEVEL_STATEMENT
2091 ,p_module => l_log_module);
2092 END IF;
2093
2094
2095
2096 END IF;
2097
2098 END IF;
2099
2100 END LOOP;
2101
2102 l_trx_id_str := l_trx_id_str ||' END '||' USERIDS';
2103
2104 if l_id_num = 1 then
2105 p_trx_identifiers_1 := l_trx_id_str;
2106 elsif l_id_num = 2 then
2107 p_trx_identifiers_2 := l_trx_id_str;
2108 elsif l_id_num = 3 then
2109 p_trx_identifiers_3 := l_trx_id_str;
2110 elsif l_id_num = 4 then
2111 p_trx_identifiers_4 := l_trx_id_str;
2112 elsif l_id_num = 5 then
2113 p_trx_identifiers_5 := l_trx_id_str;
2114 end if;
2115
2116 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2117 trace('get_transaction_id .End'
2118 ,C_LEVEL_PROCEDURE, l_log_module);
2119 END IF;
2120
2121 EXCEPTION
2122 WHEN OTHERS THEN
2123 xla_exceptions_pkg.raise_message
2124 (p_location => 'xla_report_utility_pkg.get_transaction_id ');
2125
2126 END get_transaction_id;
2127
2128 ------------------------------------------------------------------------------
2129 ------------------------------------------------------------------------------
2130
2131 --=============================================================================
2132 --
2133 --
2134 --
2135 --=============================================================================
2136 FUNCTION get_conc_segments
2137 (p_coa_id NUMBER
2138 ,p_table_alias VARCHAR2)
2139 RETURN VARCHAR2 IS
2140
2141 TYPE t_array_char IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
2142
2143 l_conc_seg_delimiter VARCHAR2(80);
2144 l_concat_segment VARCHAR2(4000);
2145 l_array t_array_char;
2146 l_log_module VARCHAR2(240);
2147
2148 CURSOR c(p_coa_id NUMBER,p_table_alias VARCHAR2) IS
2149 SELECT p_table_alias||'.'||application_column_name seg
2150 FROM fnd_id_flex_segments
2151 WHERE application_id =101
2152 AND id_flex_code ='GL#'
2153 AND id_flex_num = p_coa_id
2154 ORDER BY segment_num ;
2155
2156 BEGIN
2157
2158 IF g_log_enabled THEN
2159 l_log_module := C_DEFAULT_MODULE||'.get_conc_segments';
2160 END IF;
2161 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2162 trace
2163 (p_msg => 'BEGIN of function GET_CONC_SEGMENTS'
2164 ,p_level => C_LEVEL_PROCEDURE
2165 ,p_module => l_log_module);
2166 trace
2167 (p_msg => 'p_coa_id = '||p_coa_id
2168 ,p_level => C_LEVEL_PROCEDURE
2169 ,p_module => l_log_module);
2170 trace
2171 (p_msg => 'p_table_alias = '||p_table_alias
2172 ,p_level => C_LEVEL_PROCEDURE
2173 ,p_module => l_log_module);
2174 END IF;
2175
2176 --
2177 -- Get concatenated segment delimiter for COA
2178 --
2179
2180 SELECT '||'''||concatenated_segment_delimiter||'''||'
2181 INTO l_conc_seg_delimiter
2182 FROM fnd_id_flex_structures
2183 WHERE application_id =101
2184 AND id_flex_code ='GL#'
2185 AND id_flex_num = p_coa_id;
2186
2187 OPEN c(p_coa_id ,p_table_alias);
2188
2189 FETCH c BULK COLLECT INTO l_array;
2190
2191 CLOSE c;
2192
2193 FOR i in 1 .. l_array.count LOOP
2194 l_concat_segment := l_concat_segment||l_array(i);
2195
2196 IF i<l_array.count THEN
2197 l_concat_segment := l_concat_segment||l_conc_seg_delimiter;
2198 END IF;
2199
2200 END LOOP;
2201
2202 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2203 trace
2204 (p_msg => 'END of function GET_CONC_SEGMENTS'
2205 ,p_level => C_LEVEL_PROCEDURE
2206 ,p_module => l_log_module);
2207 END IF;
2208
2209 RETURN l_concat_segment;
2210
2211 EXCEPTION
2212 WHEN OTHERS THEN
2213 xla_exceptions_pkg.raise_message
2214 (p_location => 'xla_report_utility_pkg.get_conc_segments ');
2215
2216 END get_conc_segments;
2217
2218
2219 --=============================================================================
2220 -- *********** Initialization routine **********
2221 --=============================================================================
2222
2223 --=============================================================================
2224 --
2225 --
2226 --
2227 --
2228 --
2229 --
2230 --
2231 --
2232 --
2233 --
2234 -- Following code is executed when the package body is referenced for the first
2235 -- time
2236 --
2237 --
2238 --
2239 --
2240 --
2241 --
2242 --
2243 --
2244 --
2245 --
2246 --
2247 --
2248 --=============================================================================
2249
2250 BEGIN
2251 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2252 g_log_enabled := fnd_log.test
2253 (log_level => g_log_level
2254 ,MODULE => C_DEFAULT_MODULE);
2255
2256 IF NOT g_log_enabled THEN
2257 g_log_level := C_LEVEL_LOG_DISABLED;
2258 END IF;
2259
2260 END XLA_REPORT_UTILITY_PKG;