1 PACKAGE BODY XLA_MO_REPORTING_API AS
2 /* $Header: XLAMORPB.pls 120.8 2006/12/01 10:29:31 esayyed 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
171 IS
172 l_reporting_level_name fnd_lookups.meaning%TYPE;
173
174 BEGIN
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 )
331 FROM hr_organization_information
332 WHERE organization_id = fnd_profile.value_wnps('ORG_ID')
333 AND org_information_context = 'Operating Unit Information'
334 )
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,
455 END IF;
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);
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;
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
467 -- association of primary and reporting set of books in HR.
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 SELECT count(*)
503 INTO l_count
504 FROM hr_organization_information
505 WHERE org_information_context = 'Operating Unit Information'
506 AND DECODE( p_reporting_level
507 , '1000' , TO_NUMBER(org_information3)
508 , '2000' , TO_NUMBER(org_information2)) =
509 l_reporting_entity_id;
510
511 IF ( l_count <= g_max_num )
512 THEN
513 l_pred_type := 'INSTR';
514 ELSE
515 l_pred_type := 'EXISTS';
516 END IF;
517
518 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
519 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
520 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.pred_type',
521 'Determined predicate type:'||
522 ' l_count='||l_count||
523 ',g_max_num='||g_max_num||
524 ',l_pred_type='||l_pred_type);
525 END IF;
526
527 ELSIF ( p_pred_type = 'EXISTS'
528 OR p_pred_type = 'IN_SELECT'
529 OR p_pred_type = 'IN_LIST'
530 )
531 THEN
532
533 l_pred_type := p_pred_type;
534
535 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
536 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
537 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.pred_type',
538 'Determined predicate type: '||
539 'l_pred_type='||l_pred_type);
540 END IF;
541
542 END IF;
543
544
545 IF ( p_reporting_level = '3000' ) /* Operating Unit Level */ THEN
546
547 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
548 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
549 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.pred_text',
550 'Generating predicate for reporting level 3000');
551 END IF;
552
553 IF (l_use_nvl = 'Y') THEN
554 l_pred := ' AND NVL(/*ALIAS*/ORG_ID, :p_reporting_entity_id) = '
555 ||':p_reporting_entity_id ';
556 ELSE
557 l_pred := ' AND /*ALIAS*/ORG_ID = :p_reporting_entity_id ';
558 END IF;
559
560 ELSIF ( ( p_reporting_level = '2000' /* Legal Entity Level */
561 OR p_reporting_level = '1000' /* Set of Books Level */
562 ) AND l_pred_type = 'IN_LIST' ) THEN
563
564 -- replaced IN logic with EXISTS for performance reasons
565
566 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
567 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
568 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.pred_text',
569 'Generating EXISTS predicate for reporting level 1000/2000');
570 END IF;
571
572 IF (l_use_nvl = 'Y') THEN
573 l_pred := ' AND (/*ALIAS*/org_id IS NULL OR ';
574 ELSE
575 l_pred := ' AND ( ';
576 END IF;
577
578 l_pred := l_pred
579 ||'EXISTS '
580 ||'( SELECT /*HINT*/ 1'
581 || ' FROM hr_organization_information org_info'
582 || ' WHERE /*ALIAS*/org_id = org_info.organization_id'
586 THEN
583 || ' AND org_info.org_information_context = ''Operating Unit Information''';
584
585 IF (p_reporting_level = '1000')
587 l_pred := l_pred||' AND TO_NUMBER(org_info.org_information3) = '
588 || l_reporting_entity_id;
589
590 ELSIF (p_reporting_level = '2000')
591 THEN
592 l_pred := l_pred||' AND TO_NUMBER(org_info.org_information2) = '
593 || ' :p_reporting_entity_id';
594
595 END IF;
596
597 l_pred := l_pred||')) ';
598
599 --
600
601 ELSIF ( ( p_reporting_level = '2000'
602 OR p_reporting_level = '1000' )
603 AND l_pred_type = 'INSTR' ) THEN
604
605 -- replaced INSTR logic with EXISTS for performance reasons
606
607 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
608 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
609 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.pred_text',
610 'Generating EXISTS predicate for reporting level 1000/2000');
611 END IF;
612
613 IF (l_use_nvl = 'Y') THEN
614 l_pred := ' AND (/*ALIAS*/org_id IS NULL OR ';
615 ELSE
616 l_pred := ' AND ( ';
617 END IF;
618
619 l_pred := l_pred
620 ||'EXISTS '
621 ||'( SELECT /*HINT*/ 1'
622 || ' FROM hr_organization_information org_info'
623 || ' WHERE /*ALIAS*/org_id = org_info.organization_id'
624 || ' AND org_info.org_information_context = ''Operating Unit Information''';
625
626 IF (p_reporting_level = '1000')
627 THEN
628 l_pred := l_pred||' AND TO_NUMBER(org_info.org_information3) = '
629 || l_reporting_entity_id;
630
631 ELSIF (p_reporting_level = '2000')
632 THEN
633 l_pred := l_pred||' AND TO_NUMBER(org_info.org_information2) = '
634 || ' :p_reporting_entity_id';
635
636 END IF;
637
638 l_pred := l_pred||')) ';
639
640 --
641
642 ELSIF ( ( p_reporting_level = '2000'
643 OR p_reporting_level = '1000' )
644 AND l_pred_type = 'EXISTS' ) THEN
645
646 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
647 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
648 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.pred_text',
649 'Generating EXISTS predicate for reporting level 1000/2000');
650 END IF;
651
652 IF (l_use_nvl = 'Y') THEN
653 l_pred := ' AND (/*ALIAS*/org_id IS NULL OR ';
654 ELSE
655 l_pred := ' AND ( ';
656 END IF;
657
658 l_pred := l_pred
659 ||'EXISTS '
660 ||'( SELECT /*HINT*/ 1'
661 || ' FROM hr_organization_information org_info'
662 || ' WHERE /*ALIAS*/org_id = org_info.organization_id'
663 || ' AND org_info.org_information_context = ''Operating Unit Information''';
664
665 IF (p_reporting_level = '1000')
666 THEN
667 l_pred := l_pred||' AND TO_NUMBER(org_info.org_information3) = '
668 || l_reporting_entity_id;
669
670 ELSIF (p_reporting_level = '2000')
671 THEN
672 l_pred := l_pred||' AND TO_NUMBER(org_info.org_information2) = '
673 || ' :p_reporting_entity_id';
674
675 END IF;
676
677 l_pred := l_pred||')) ';
678
679 ELSIF ( ( p_reporting_level = '2000'
680 OR p_reporting_level = '1000' )
681 AND l_pred_type = 'IN_SELECT' ) THEN
682
683 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
684 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
685 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.pred_text',
686 'Generating IN_SELECT predicate for reporting level 1000/2000');
687 END IF;
688
689 IF (l_use_nvl = 'Y') THEN
690 l_pred := ' AND (NVL(/*ALIAS*/org_id, -99) IN '
691 ||'( SELECT -99'
692 || ' FROM DUAL'
693 || ' UNION'
694 || ' SELECT /*HINT*/ organization_id'
695 || ' FROM hr_organization_information'
696 || ' WHERE org_information_context = ''Operating Unit Information''';
697 ELSE
698 l_pred := ' AND (/*ALIAS*/org_id IN '
699 ||'( SELECT /*HINT*/ organization_id'
700 || ' FROM hr_organization_information'
701 || ' WHERE org_information_context = ''Operating Unit Information''';
702 END IF;
703
704 IF (p_reporting_level = '1000')
705 THEN
706 l_pred := l_pred||' AND TO_NUMBER(org_information3) = '
707 || l_reporting_entity_id;
708
709 ELSIF (p_reporting_level = '2000')
710 THEN
711 l_pred := l_pred||' AND TO_NUMBER(org_information2) = '
712 || ' :p_reporting_entity_id';
713
714 END IF;
715
716 l_pred := l_pred||')) ';
717
718 END IF;
719
720 g_pred := l_pred;
721
722 l_pred := NULL;
723
724 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
725 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
726 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE.end',
727 'Returning from PL/SQL procedure XLA_MO_REPORTING_API.INITIALIZE:'||
728 ' g_reporting_level='||g_reporting_level||
729 ',g_reporting_entity_id='||g_reporting_entity_id||
730 ',g_max_num='||g_max_num||
731 ',g_pred='||g_pred);
732 END IF;
733
734 EXCEPTION
735 WHEN OTHERS THEN
736 Generic_Error( 'xla.plsql.XLA_MO_REPORTING_API.INITIALIZE'
737 , sqlcode
738 , sqlerrm);
739
740 END initialize;
741
742
743 END XLA_MO_REPORTING_API;
[Home] [Help]