1 PACKAGE BODY XLA_MO_REPORTING_API AS
2 /* $Header: XLAMORPB.pls 120.12 2011/12/06 10:24:26 nmikkili ship $ */
3
4 g_max_num NUMBER DEFAULT 100;
5 g_pred VARCHAR2(2000) DEFAULT NULL;
6 g_reporting_level VARCHAR2(10) DEFAULT '3000';
7 g_reporting_entity_id NUMBER;
8
9
10
11 --
12 -- Generic_Error (Internal)
13 --
14 -- Set error message and raise exception for unexpected sql errors.
15 --
16 PROCEDURE Generic_Error
17 ( routine IN VARCHAR2
18 , errcode IN NUMBER
19 , errmsg IN VARCHAR2
20 )
21 IS
22 BEGIN
23 FND_MESSAGE.SET_NAME('FND', 'SQL_PLSQL_ERROR');
24 FND_MESSAGE.SET_TOKEN('ROUTINE', routine);
25 FND_MESSAGE.SET_TOKEN('ERRNO', errcode);
26 FND_MESSAGE.SET_TOKEN('REASON', errmsg);
27 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
28 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, routine, FALSE);
29 END IF;
30 APP_EXCEPTION.RAISE_EXCEPTION;
31 EXCEPTION
32 WHEN OTHERS THEN RAISE;
33 END;
34
35
36
37
38 --
39 -- App_Error (Internal)
40 --
41 PROCEDURE App_Error
42 ( routine IN VARCHAR2
43 , error_name IN VARCHAR2
44 , token1 IN VARCHAR2 DEFAULT NULL
45 , value1 IN VARCHAR2 DEFAULT NULL
46 , token2 IN VARCHAR2 DEFAULT NULL
47 , value2 IN VARCHAR2 DEFAULT NULL
48 , token3 IN VARCHAR2 DEFAULT NULL
49 , value3 IN VARCHAR2 DEFAULT NULL
50 , token4 IN VARCHAR2 DEFAULT NULL
51 , value4 IN VARCHAR2 DEFAULT NULL
52 )
53 IS
54 BEGIN
55 fnd_message.set_name('XLA',error_name);
56
57 IF (token1 IS NOT NULL AND value1 IS NOT NULL)
58 THEN
59 fnd_message.set_token(token1,value1);
60 END IF;
61
62 IF (token2 IS NOT NULL AND value2 IS NOT NULL)
63 THEN
64 fnd_message.set_token(token2,value2);
65 END IF;
66
67 IF (token3 IS NOT NULL AND value3 IS NOT NULL)
68 THEN
69 fnd_message.set_token(token3,value3);
70 END IF;
71
72 IF (token4 IS NOT NULL AND value4 IS NOT NULL)
73 THEN
74 fnd_message.set_token(token4,value4);
75 END IF;
76
77 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
78 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, routine, FALSE);
79 END IF;
80
81 app_exception.raise_exception;
82
83 EXCEPTION
84 WHEN OTHERS THEN
85 Generic_Error( 'xla.plsql.XLA_MO_REPORTING_API.APP_ERROR'
86 , sqlcode
87 , sqlerrm);
88 END App_Error;
89
90
91
92
93 --
94 -- Use this procedure to initialize the reporting API.
95 --
96 PROCEDURE Initialize
97 ( p_reporting_level IN VARCHAR2 DEFAULT '3000'
98 , p_reporting_entity_id IN NUMBER
99 , p_pred_type IN VARCHAR2 DEFAULT 'AUTO'
100 )
101 IS
102
103 BEGIN
104 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
105 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
106 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.begin',
107 'Calling PL/SQL procedure XLA_MO_REPORTING_API.INITIALIZE:'||
108 ' p_reporting_level=>'||p_reporting_level||
109 ',p_reporting_entity_id=>'||p_reporting_entity_id||
110 ',p_pred_type=>'||p_pred_type);
111 END IF;
112
113 initialize(p_reporting_level,
114 p_reporting_entity_id,
115 p_pred_type,
116 'Y');
117
118 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
119 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
120 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.end',
121 'Returning from PL/SQL procedure XLA_MO_REPORTING_API.INITIALIZE:');
122
123 END IF;
124
125 END Initialize;
126
127
128 --
129 -- Get predicate
130 --
131 FUNCTION Get_Predicate
132 ( p_alias IN VARCHAR2 DEFAULT NULL
133 , p_hint IN VARCHAR2 DEFAULT NULL
134 , p_variable_override IN VARCHAR2 DEFAULT ' :p_reporting_entity_id '
135 )
136 RETURN VARCHAR2
137 IS
138
139 l_return_pred VARCHAR2(2000) DEFAULT NULL;
140
141 BEGIN
142
143 l_return_pred := replace( g_pred
144 , '/*ALIAS*/'
145 , p_alias||'.');
146
147 l_return_pred := replace( l_return_pred
148 , '/*HINT*/'
149 , '/*+ '||p_hint||' */');
150
151 l_return_pred := replace( l_return_pred
152 , ':p_reporting_entity_id'
153 , p_variable_override);
154
155 RETURN l_return_pred;
156
157 EXCEPTION
158 WHEN OTHERS THEN
159 Generic_Error( 'xla.plsql.XLA_MO_REPORTING_API.GET_PREDICATE'
160 , sqlcode
161 , sqlerrm);
162
163 END Get_Predicate;
164
165
166 --
167 -- Get the reporting level name
168 --
169 FUNCTION Get_Reporting_Level_Name
170 RETURN VARCHAR2
174 BEGIN
171 IS
172 l_reporting_level_name fnd_lookups.meaning%TYPE;
173
175 SELECT meaning
176 INTO l_reporting_level_name
177 FROM fnd_lookups
178 WHERE lookup_type = 'XLA_MO_REPORTING_LEVEL'
179 AND lookup_code = g_reporting_level;
180
181 RETURN l_reporting_level_name;
182
183 EXCEPTION
184 WHEN OTHERS THEN
185 Generic_Error( 'xla.plsql.XLA_MO_REPORTING_API.GET_REPORTING_LEVEL_NAME'
186 , sqlcode
187 , sqlerrm);
188
189 END Get_Reporting_Level_Name;
190
191
192
193
194 --
195 -- Get the reporting entity name
196 --
197 FUNCTION Get_Reporting_Entity_Name
198 RETURN VARCHAR2
199 IS
200 l_reporting_entity_name xla_mo_reporting_entities_v
201 .entity_name%TYPE;
202
203 BEGIN
204
205 IF g_reporting_level = '1000'
206 THEN
207 SELECT name
208 INTO l_reporting_entity_name
209 FROM gl_sets_of_books
210 WHERE set_of_books_id = g_reporting_entity_id;
211 ELSE
212 SELECT name
213 INTO l_reporting_entity_name
214 FROM hr_all_organization_units
215 WHERE organization_id = g_reporting_entity_id;
216 END IF;
217
218 RETURN l_reporting_entity_name;
219
220 EXCEPTION
221 WHEN OTHERS THEN
222 Generic_Error( 'xla.plsql.XLA_MO_REPORTING_API.GET_REPORTING_ENTITY_NAME'
223 , sqlcode
224 , sqlerrm);
225
226 END Get_Reporting_Entity_Name;
227
228
229
230
231 --
232 -- Validate the reporting level
233 --
234 PROCEDURE Validate_Reporting_Level
235 ( p_reporting_level IN VARCHAR2 )
236 IS
237 l_top_reporting_level fnd_profile_option_values.profile_option_value%TYPE;
238
239 CURSOR l_check_reporting_level
240 ( x_reporting_level IN VARCHAR2
241 , x_top_reporting_level IN VARCHAR2
242 )
243 IS
244 SELECT 1
245 FROM fnd_lookups lp
246 WHERE lookup_type = 'XLA_MO_REPORTING_LEVEL'
247 AND lookup_code = x_reporting_level
248 AND TO_NUMBER(lookup_code) >=
249 TO_NUMBER(x_top_reporting_level);
250
251 l_dummy NUMBER;
252
253 BEGIN
254 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
255 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
256 'xla.plsql.XLA_MO_REPORTING_API.VALIDATE_REPORTING_LEVEL.begin',
257 'Calling PL/SQL procedure XLA_MO_REPORTING_API.VALIDATE_REPORTING_LEVEL:'||
258 ' p_reporting_level=>'||p_reporting_level);
259 END IF;
260
261 l_top_reporting_level := fnd_profile.value('XLA_MO_TOP_REPORTING_LEVEL');
262
263 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
264 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
265 'xla.plsql.XLA_MO_REPORTING_API.VALIDATE_REPORTING_LEVEL.config',
266 'MO: Top Reporting Level='||l_top_reporting_level);
267 END IF;
268
269 OPEN l_check_reporting_level( p_reporting_level, l_top_reporting_level);
270 FETCH l_check_reporting_level INTO l_dummy;
271
272 IF (l_check_reporting_level%NOTFOUND )
273 THEN
274 CLOSE l_check_reporting_level;
275 App_Error( 'xla.plsql.XLA_MO_REPORTING_API.VALIDATE_REPORTING_LEVEL'
276 , 'XLA_MO_RPTAPI_LEVEL'
277 , 'REPORTING_LEVEL', p_reporting_level
278 , 'TOP_REPORTING_LEVEL', l_top_reporting_level);
279
280 END IF;
281
282 CLOSE l_check_reporting_level;
283
284 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
285 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
286 'xla.plsql.XLA_MO_REPORTING_API.VALIDATE_REPORTING_LEVEL.end',
287 'Returning from PL/SQL procedure '||
288 'XLA_MO_REPORTING_API.VALIDATE_REPORTING_LEVEL');
289 END IF;
290
291 EXCEPTION
292 WHEN OTHERS THEN
293 Generic_Error( 'xla.plsql.XLA_MO_REPORTING_API.VALIDATE_REPORTING_LEVEL'
294 , sqlcode
295 , sqlerrm);
296
297 END Validate_Reporting_Level;
298
299
300
301
302 --
303 -- Validate the reporting entity
304 --
305 PROCEDURE Validate_Reporting_Entity
306 ( p_reporting_level IN VARCHAR2
307 , p_reporting_entity_id IN NUMBER
308 )
309 IS
310
311 CURSOR l_check_reporting_entity
312 ( x_reporting_level IN VARCHAR2
313 , x_reporting_entity_id IN NUMBER
314 )
315 IS
316 SELECT 1
317 FROM XLA_MO_REPORTING_ENTITIES_V
318 WHERE reporting_level = p_reporting_level
319 /* Commented out NOCOPY the code below, since it does not work for reporting set
320 of books. Intead, making a call to validate_reporting_level to make sure
321 that p_reporting_level value is within the allowed value for profile option
322 value for MO: Top Reporting Level.
323 AND DECODE( fnd_profile.value_wnps('XLA_MO_TOP_REPORTING_LEVEL')
324 , '1000', set_of_books_id
325 , '2000', legal_entity_id
326 , '3000', operating_unit_id) =
327 ( SELECT DECODE( fnd_profile.value_wnps('XLA_MO_TOP_REPORTING_LEVEL')
328 , '1000', TO_NUMBER(org_information3)
329 , '2000', TO_NUMBER(org_information2)
330 , '3000', organization_id )
334 )
331 FROM hr_organization_information
332 WHERE organization_id = fnd_profile.value_wnps('ORG_ID')
333 AND org_information_context = 'Operating Unit Information'
335 */
336 AND entity_id = x_reporting_entity_id;
337
338 l_dummy NUMBER;
339
340 BEGIN
341 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
342 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
343 'xla.plsql.XLA_MO_REPORTING_API.VALIDATE_REPORTING_ENTITY.begin',
344 'Calling PL/SQL procedure XLA_MO_REPORTING_API.VALIDATE_REPORTING_ENTITY:'||
345 ' p_reporting_level=>'||p_reporting_level||
346 ',p_reporting_entity_id=>'||p_reporting_entity_id);
347 END IF;
348
349 validate_reporting_level(p_reporting_level);
350
351 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
352 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
353 'xla.plsql.XLA_MO_REPORTING_API.VALIDATE_REPORTING_ENTITY.rep_level_valid',
354 'Reporting level is valid');
355 END IF;
356
357 OPEN l_check_reporting_entity ( p_reporting_level, p_reporting_entity_id );
358 FETCH l_check_reporting_entity INTO l_dummy;
359
360 IF ( l_check_reporting_entity%NOTFOUND )
361 THEN
362 CLOSE l_check_reporting_entity;
363 App_Error('xla.plsql.XLA_MO_REPORTING_API.VALIDATE_REPORTING_ENTITY',
364 'XLA_MO_RPTAPI_ENTITY',
365 'REPORTING_ENTITY',
366 p_reporting_entity_id);
367 END IF;
368
369 CLOSE l_check_reporting_entity;
370
371 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
372 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
373 'xla.plsql.XLA_MO_REPORTING_API.VALIDATE_REPORTING_ENTITY.end',
374 'Returning from PL/SQL procedure '||
375 'XLA_MO_REPORTING_API.VALIDATE_REPORTING_ENTITY:');
376 END IF;
377
378 EXCEPTION
379 WHEN OTHERS THEN
380 Generic_Error( 'xla.plsql.XLA_MO_REPORTING_API.VALIDATE_REPORTING_ENTITY'
381 , sqlcode
382 , sqlerrm);
383
384 END Validate_Reporting_Entity;
385
386 --
387 -- DESCRIPTION
388 -- This procedure initializes the reporting API.
389 --
390 -- The parameter p_use_nvl indicates whether the NVL should be used
391 -- around ORG_ID in the generated predicate. In a non Multi-Org
392 -- environment the NVL is always used (i.e. p_use_nvl is ignored).
393 -- In a Multi-Org environment p_use_nvl should be set to 'Y' unless
394 -- there are performance reasons not to use it. Keep in mind that
395 -- for some tables NVL must always be used because they may contain
396 -- null ORG_ID values. It is up to the caller of this procedure to
397 -- determine whether this is the case. See bug 3025408.
398 --
399 -- PARAMETERS
400 -- p_reporting_level - The reporting level (1000, 2000, 3000
401 -- for set of books, legal entity and operating
402 -- unit respectively).
403 -- p_reporting_entity_id - The identifier of the reporting entity.
404 -- p_pred_type - The type of the generated predicate.
405 -- (AUTO, EXISTS, IN_LIST, IN_SELECT, INSTR)
406 -- p_use_nvl - Set it to 'N' to eliminate NVL from the
407 -- predicate. Any other value will include
408 -- the NVL function.
409 --
410 PROCEDURE Initialize
411 ( p_reporting_level IN VARCHAR2 DEFAULT '3000'
412 , p_reporting_entity_id IN NUMBER
413 , p_pred_type IN VARCHAR2 DEFAULT 'AUTO'
414 , p_use_nvl IN VARCHAR2
415 )
416 IS
417
418 l_count NUMBER := 0;
419 l_pred VARCHAR2(2000) DEFAULT NULL;
420 l_pred_type VARCHAR2(30) DEFAULT 'EXISTS';
421 l_multi_org_enabled fnd_product_groups.multi_org_flag%TYPE;
422
423 l_sob_type VARCHAR2(1);
424 l_reporting_entity_id NUMBER(15);
425
426 l_use_nvl VARCHAR2(1);
427
428 BEGIN
429 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
430 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
431 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.begin',
432 'Calling PL/SQL procedure XLA_MO_REPORTING_API.INITIALIZE:'||
433 ' p_reporting_level=>'||p_reporting_level||
434 ',p_reporting_entity_id=>'||p_reporting_entity_id||
435 ',p_pred_type=>'||p_pred_type||
436 ',p_use_nvl=>'||p_use_nvl);
437 END IF;
438
439 SELECT nvl(multi_org_flag, 'N')
440 INTO l_multi_org_enabled
441 FROM fnd_product_groups
442 WHERE product_group_id = 1;
443
444 IF (l_multi_org_enabled = 'Y' AND p_use_nvl = 'N') THEN
445 l_use_nvl := 'N';
446 ELSE
447 l_use_nvl := 'Y';
448 END IF;
449
450 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
451 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
452 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.use_nvl',
453 'nvl(fnd_product_groups.multi_org_flag,''N'')='||l_multi_org_enabled||
454 ',l_use_nvl='||l_use_nvl);
455 END IF;
456
457 -- Validate_Reporting_Level( p_reporting_level );
458 -- Validate_Reporting_Entity( p_reporting_level, p_reporting_entity_id );
459
460 g_reporting_level := p_reporting_level;
461 g_reporting_entity_id := p_reporting_entity_id;
462
463 l_reporting_entity_id := p_reporting_entity_id;
467 -- association of primary and reporting set of books in HR.
464 -- Check if the SOB is Primary or Reporting if reporting level is 1000
465 -- If reporting, we need to get the primary set of books and then use that
466 -- value to get the operating unit information from HR tables. There is no
468 IF p_reporting_level = '1000' then
469 gl_mc_info.get_sob_type(p_reporting_entity_id, l_sob_type);
470
471 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
472 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
473 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.get_sob_type',
474 'gl_mc_info.get_sob_type='||l_sob_type);
475 END IF;
476
477 IF l_sob_type = 'R' then
478
479 -- Use MRC API to get the primary set of books. The API will return the
480 -- first primary set of books if a reportign SOb is assigned to multiple
481 -- primary SOB. MRC team mentioned that this is not supported currently.
482 -- When this is supported both MRC and our code needs to be changed.
483 -- a given reporting set of books.
484 l_reporting_entity_id :=
485 gl_mc_info.get_primary_set_of_books_id(p_reporting_entity_id);
486
487 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
488 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
489 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.primary_sob_id',
490 'gl_mc_info.primary_set_of_books_id='||l_reporting_entity_id);
491 END IF;
492
493 END IF;
494 END IF;
495
496 IF ( p_pred_type = 'AUTO'
497 OR p_pred_type = 'INSTR'
498 OR p_pred_type IS NULL
499 )
500 THEN
501
502 --
503 --8803371 Added hint to force the usage of index
504
505 SELECT /*+ INDEX(HR_ORGANIZATION_INFORMATION HR_ORGANIZATION_INFORMATIO_FK1) */ count(*)
506 INTO l_count
507 FROM hr_organization_information
508 WHERE org_information_context = 'Operating Unit Information'
509 AND DECODE( p_reporting_level
510 , '1000' , org_information3
511 , '2000' , org_information2) =
512 to_char(l_reporting_entity_id); -- bug 9108714
513
514 IF ( l_count <= g_max_num )
515 THEN
516 l_pred_type := 'INSTR';
517 ELSE
518 l_pred_type := 'EXISTS';
519 END IF;
520
521 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
522 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
523 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.pred_type',
524 'Determined predicate type:'||
525 ' l_count='||l_count||
526 ',g_max_num='||g_max_num||
527 ',l_pred_type='||l_pred_type);
528 END IF;
529
530 ELSIF ( p_pred_type = 'EXISTS'
531 OR p_pred_type = 'IN_SELECT'
532 OR p_pred_type = 'IN_LIST'
533 )
534 THEN
535
536 l_pred_type := p_pred_type;
537
538 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
539 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
540 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.pred_type',
541 'Determined predicate type: '||
542 'l_pred_type='||l_pred_type);
543 END IF;
544
545 END IF;
546
547
548 IF ( p_reporting_level = '3000' ) /* Operating Unit Level */ THEN
549
550 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
551 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
552 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.pred_text',
553 'Generating predicate for reporting level 3000');
554 END IF;
555
556 IF (l_use_nvl = 'Y') THEN
557 l_pred := ' AND NVL(/*ALIAS*/ORG_ID, :p_reporting_entity_id) = '
558 ||':p_reporting_entity_id ';
559 ELSE
560 l_pred := ' AND /*ALIAS*/ORG_ID = :p_reporting_entity_id ';
561 END IF;
562
563 ELSIF ( ( p_reporting_level = '2000' /* Legal Entity Level */
564 OR p_reporting_level = '1000' /* Set of Books Level */
565 ) AND l_pred_type = 'IN_LIST' ) THEN
566
567 --Bug 13116125
568 --reverted from EXISTS to IN for IN_LIST replaced IN logic with EXISTS for performance reasons
569
570 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
571 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
572 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.pred_text',
573 'Generating IN_LIST predicate for reporting level 1000/2000');
574 END IF;
575
576 IF (l_use_nvl = 'Y') THEN
577 l_pred := ' AND (NVL(/*ALIAS*/ORG_ID, -99) IN (-99,';
578 ELSE
579 l_pred := ' AND (/*ALIAS*/ORG_ID in (';
580 END IF;
581
582 FOR l_org_rec IN
583 (SELECT organization_id
584 FROM hr_organization_information
585 WHERE org_information_context = 'Operating Unit Information'
586 AND DECODE( p_reporting_level
587 , '1000' , org_information3
588 , '2000' , org_information2
589 ) = to_char(l_reporting_entity_id) )
590 LOOP
591
592 l_pred := l_pred || l_org_rec.organization_id || ',';
593
594 END LOOP;
595
596 l_pred := rtrim(l_pred, ','); -- remove trailing comma
597 --Bug 13116125
598
599 l_pred := l_pred||')) ';
600
601 --
602
603 ELSIF ( ( p_reporting_level = '2000'
604 OR p_reporting_level = '1000' )
605 AND l_pred_type = 'INSTR' ) THEN
606
607 -- replaced INSTR logic with EXISTS for performance reasons
608
609 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
610 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
611 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.pred_text',
612 'Generating EXISTS predicate for reporting level 1000/2000');
613 END IF;
614
615 IF (l_use_nvl = 'Y') THEN
616 l_pred := ' AND (/*ALIAS*/org_id IS NULL OR ';
617 ELSE
618 l_pred := ' AND ( ';
619 END IF;
620
621 l_pred := l_pred
622 ||'EXISTS '
623 ||'( SELECT /*HINT*/ 1'
624 || ' FROM hr_organization_information org_info'
625 || ' WHERE /*ALIAS*/org_id = org_info.organization_id'
626 || ' AND org_info.org_information_context = ''Operating Unit Information''';
627
628 IF (p_reporting_level = '1000')
629 THEN
630 --bug 9108714
631 l_pred := l_pred||' AND org_info.org_information3 = TO_CHAR('
632 || l_reporting_entity_id || ')';
633
634 ELSIF (p_reporting_level = '2000')
635 THEN
636 --bug 9108714
637 l_pred := l_pred||' AND org_info.org_information2 = TO_CHAR(:p_reporting_entity_id)' ;
638
639 END IF;
640
641 l_pred := l_pred||')) ';
642
643 --
644
645 ELSIF ( ( p_reporting_level = '2000'
646 OR p_reporting_level = '1000' )
647 AND l_pred_type = 'EXISTS' ) THEN
648
649 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
650 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
651 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.pred_text',
652 'Generating EXISTS predicate for reporting level 1000/2000');
653 END IF;
654
655 IF (l_use_nvl = 'Y') THEN
656 l_pred := ' AND (/*ALIAS*/org_id IS NULL OR ';
657 ELSE
658 l_pred := ' AND ( ';
659 END IF;
660
661 l_pred := l_pred
662 ||'EXISTS '
663 ||'( SELECT /*HINT*/ 1'
664 || ' FROM hr_organization_information org_info'
665 || ' WHERE /*ALIAS*/org_id = org_info.organization_id'
666 || ' AND org_info.org_information_context = ''Operating Unit Information''';
667
668 IF (p_reporting_level = '1000')
669 THEN
670 --bug 9108714
671 l_pred := l_pred||' AND org_info.org_information3 = TO_CHAR('
672 || l_reporting_entity_id || ')';
673
674 ELSIF (p_reporting_level = '2000')
675 THEN
676 --bug 9108714
677 l_pred := l_pred||' AND org_info.org_information2 = TO_CHAR(:p_reporting_entity_id)' ;
678
679 END IF;
680
681 l_pred := l_pred||')) ';
682
683 ELSIF ( ( p_reporting_level = '2000'
684 OR p_reporting_level = '1000' )
685 AND l_pred_type = 'IN_SELECT' ) THEN
686
687 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
688 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
689 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.pred_text',
690 'Generating IN_SELECT predicate for reporting level 1000/2000');
691 END IF;
692
693 IF (l_use_nvl = 'Y') THEN
694 l_pred := ' AND (NVL(/*ALIAS*/org_id, -99) IN '
695 ||'( SELECT -99'
696 || ' FROM DUAL'
697 || ' UNION'
698 || ' SELECT /*HINT*/ organization_id'
699 || ' FROM hr_organization_information'
700 || ' WHERE org_information_context = ''Operating Unit Information''';
701 ELSE
702 l_pred := ' AND (/*ALIAS*/org_id IN '
703 ||'( SELECT /*HINT*/ organization_id'
704 || ' FROM hr_organization_information'
705 || ' WHERE org_information_context = ''Operating Unit Information''';
706 END IF;
707
708 IF (p_reporting_level = '1000')
709 THEN
710 --bug 9108714
711 l_pred := l_pred||' AND org_information3 = TO_CHAR('
712 || l_reporting_entity_id || ')';
713
714 ELSIF (p_reporting_level = '2000')
715 THEN
716 --bug 9108714
717 l_pred := l_pred||' AND org_information2 = TO_CHAR(:p_reporting_entity_id)' ;
718
719 END IF;
720
721 l_pred := l_pred||')) ';
722
723 END IF;
724
725 g_pred := l_pred;
726
727 l_pred := NULL;
728
729 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
730 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
731 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.end',
732 'Returning from PL/SQL procedure XLA_MO_REPORTING_API.INITIALIZE:'||
733 ' g_reporting_level='||g_reporting_level||
734 ',g_reporting_entity_id='||g_reporting_entity_id||
735 ',g_max_num='||g_max_num||
736 ',g_pred='||g_pred);
737 END IF;
738
739 EXCEPTION
740 WHEN OTHERS THEN
741 Generic_Error( 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE'
742 , sqlcode
743 , sqlerrm);
744
745 END initialize;
746
747
748 END XLA_MO_REPORTING_API;
[Home] [Help]