DBA Data[Home] [Help]

PACKAGE BODY: APPS.XLA_CMP_SOURCE_PKG

Source


1 PACKAGE BODY xla_cmp_source_pkg AS
2 /* $Header: xlacpscs.pkb 120.29.12020000.2 2012/10/22 09:18:45 vkanteti ship $   */
3 /*===========================================================================+
4 |             Copyright (c) 2001-2002 Oracle Corporation                     |
5 |                       Redwood Shores, CA, USA                              |
6 |                         All rights reserved.                               |
7 +============================================================================+
8 | PACKAGE NAME                                                               |
9 |     xla_cmp_source_pkg                                                     |
10 |                                                                            |
11 | DESCRIPTION                                                                |
12 |     This is a XLA private package, which contains all the logic required   |
13 |     to cache the sources and entities/objects defined in the AMB           |
14 |                                                                            |
15 |                                                                            |
16 | HISTORY                                                                    |
17 |     25-JUN-2002 K.Boussema    Created                                      |
18 |     14-JAN-2003 K.Boussema    Added 'dbdrv' command                        |
19 |     13-MAR-2003 K.Boussema    Made changes for the new bulk approach of the|
20 |                               accounting engine                            |
21 |                               Changed GetObjParm                           |
22 |     19-APR-2003 K.Boussema    Included Error messages                      |
23 |     02-JUN-2003 K.Boussema    Modified to fix bug 2975670 and bug 2729143  |
24 |     24-JUN-2003 K.Boussema    Reviewed GetSystemSource function bug2990642 |
25 |     17-JUL-2003 K.Boussema    Reviewd the code                             |
26 |     19-NOV-2003 K.Boussema    Updated the function InsertDerivedSource to  |
27 |                               fix bug 3263242                              |
28 |     20-NOV-2003 K.Boussema    Changed to fix bug 3266355 and bug 3269101   |
29 |     18-DEC-2003 K.Boussema    Changed to fix bug 3042840,3307761,3268940   |
30 |                               3310291 and 3320689                          |
31 |     23-FEB-2004 K.Boussema    Made changes for the FND_LOG.                |
32 |     12-MAR-2004 K.Boussema    Changed to incorporate the select of lookups |
33 |                               from the extract objects                     |
34 |     22-MAR-2004 K.Boussema    Added a parameter p_module to the TRACE calls|
35 |                               and the procedure.                           |
36 |     11-MAY-2004 K.Boussema    Removed the call to XLA trace routine from   |
37 |                               trace() procedure                            |
38 |     02-JUN-2004 A.Quaglia     Added get_obj_parm_for_tab                   |
39 |                               Modified GetObjParm                          |
40 |     21-Sep-2004 S.Singhania   Made ffg changes for the Bulk Performance:   |
41 |                                 - Modified routines GetStandardSource and  |
42 |                                   GetMeaningSource.                        |
43 |     06-Oct-2004 K.Boussema    Made changes for the Accounting Event Extract|
44 |                               Diagnostics feature.                         |
45 |     07-Mar-2005 K.Boussema    Changed for ADR-enhancements.                |
46 |     18-Oct-2012 VKANTETI      Bug 14756735 - Added distinct to the query   |
47 |                               in get_meaning_statement function            |
48 +===========================================================================*/
49 --
50 --
51 --+==========================================================================+
52 --|                                                                          |
53 --| Global CONSTANTS                                                         |
54 --|                                                                          |
55 --+==========================================================================+
56 --
57 --
58 g_chr_newline      CONSTANT VARCHAR2(10):= xla_environment_pkg.g_chr_newline;
59 --
60 C_UNTRANSLATED                     CONSTANT    VARCHAR2(1)  := 'N';
61 C_TRANSLATED                       CONSTANT    VARCHAR2(1)  := 'Y';
62 --
63 --
64 C_SYSTEM_SOURCE_TYPE               CONSTANT    VARCHAR2(1)  := 'Y';
65 C_SEEDED_SOURCE_TYPE               CONSTANT    VARCHAR2(1)  := 'S';
66 C_CUSTOM_SOURCE_TYPE               CONSTANT    VARCHAR2(1)  := 'D';
67 --
68 --
69 C_DATE_DATATYPE                    CONSTANT    VARCHAR2(1)  := 'D';
70 C_NUMBER_DATATYPE                  CONSTANT    VARCHAR2(1)  := 'N';
71 C_INTEGER_DATATYPE                 CONSTANT    VARCHAR2(1)  := 'I';
72 C_CHAR_DATATYPE                    CONSTANT    VARCHAR2(1)  := 'C';
73 C_FLEXFIELD_DATATYPE               CONSTANT    VARCHAR2(1)  := 'F';
74 
75 --Addition for the Transaction Account Builder
76 g_component_type                   VARCHAR2(30);
77 
78 --
79 --
80 --+==========================================================================+
81 --|                                                                          |
82 --|                                                                          |
83 --|                                                                          |
84 --|    Generate GetMeaning API for the source associated to value set        |
85 --|                                                                          |
86 --|                                                                          |
87 --|                                                                          |
88 --|                                                                          |
89 --|                                                                          |
90 --|                                                                          |
91 --|                                                                          |
92 --|                                                                          |
93 --|                                                                          |
94 --|                                                                          |
95 --|                                                                          |
96 --+==========================================================================+
97 --
98 --======================================================================
99 --
100 -- get_meaning function
101 --
102 C_GET_MEANING_FTC_NULL                CONSTANT      VARCHAR2(10000):= '
103 FUNCTION GetMeaning (
104   p_flex_value_set_id               IN INTEGER
105 , p_flex_value                      IN VARCHAR2
106 , p_source_code                     IN VARCHAR2
107 , p_source_type_code                IN VARCHAR2
108 , p_source_application_id           IN INTEGER
109 )
110 RETURN VARCHAR2
111 IS
112 BEGIN
113 --
114 RETURN NULL ;
115 --
116 EXCEPTION
117   WHEN xla_exceptions_pkg.application_exception THEN
118       RAISE;
119   WHEN OTHERS THEN
120        xla_exceptions_pkg.raise_message
121            (p_location => ''$package_name$.GetMeaning'');
122 END GetMeaning;
123 --
124 ';
125 --
126 --
127 -- get_meaning function
128 --
129 C_GET_MEANING_FTC                    CONSTANT      VARCHAR2(32000):= '
130 --
131 ---------------------------------------
132 --
133 -- PUBLIC FUNCTION
134 --         GetMeaning
135 --
136 ---------------------------------------
137 FUNCTION GetMeaning (
138   p_flex_value_set_id               IN INTEGER
139 , p_flex_value                      IN VARCHAR2
140 , p_source_code                     IN VARCHAR2
141 , p_source_type_code                IN VARCHAR2
142 , p_source_application_id           IN INTEGER
143 )
144 RETURN VARCHAR2
145 IS
146 l_meaning_meaning                     VARCHAR2(4000);
147 l_Idx                                 INTEGER;
148 l_log_module                          VARCHAR2(240);
149 --
150 BEGIN
151 IF g_log_enabled THEN
152       l_log_module := C_DEFAULT_MODULE||''.GetMeaning'';
153 END IF;
154 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
155       trace
156          (p_msg      => ''BEGIN of GetMeaning''||
157                         ''p_flex_value_set_id = ''||p_flex_value_set_id||
158                         ''p_flex_value = ''||p_flex_value
159          ,p_level    => C_LEVEL_PROCEDURE
160          ,p_module   => l_log_module);
161 END IF;
162 --
163 -- Commenting out as part of bug 7592259
164 --l_array_meaning := xla_ae_sources_pkg.g_array_meaning;
165 --
166 IF p_flex_value IS NULL THEN
167 
168   l_meaning_meaning := NULL;
169 
170 ELSE
171 
172  CASE p_flex_value_set_id
173       $body$
174  ELSE
175          l_meaning_meaning  := NULL;
176          xla_ae_journal_entry_pkg.g_global_status      :=  xla_ae_journal_entry_pkg.C_INVALID;
177          xla_accounting_err_pkg.build_message
178                         (p_appli_s_name            => ''XLA''
179                         ,p_msg_name                => ''XLA_AP_FLEX_VALUE_MEANING''
180                         ,p_token_1                 => ''VALUE_SET_NAME''
181                         ,p_value_1                 =>  xla_flex_pkg.get_value_set_name(p_flex_value_set_id)
182                         ,p_token_2                 => ''FLEX_VALUE''
183                         ,p_value_2                 =>  p_flex_value
184                         ,p_token_3                 => ''SOURCE_NAME''
185                         ,p_value_3                 =>  xla_ae_sources_pkg.GetSourceName(
186                                                      p_source_code
187                                                    , p_source_type_code
188                                                    , p_source_application_id
189                                                      )
190                         ,p_token_4                 => ''PRODUCT_NAME''
191                         ,p_value_4                 => xla_ae_journal_entry_pkg.g_cache_event.application_name
192                         ,p_entity_id               => xla_ae_journal_entry_pkg.g_cache_event.entity_id
193                         ,p_event_id                => xla_ae_journal_entry_pkg.g_cache_event.event_id
194                         ,p_ledger_id               => xla_ae_journal_entry_pkg.g_cache_event.target_ledger_id
195         );
196       IF (C_LEVEL_ERROR >= g_log_level) THEN
197                          trace
198                             (p_msg      => ''ERROR: XLA_AP_FLEX_VALUE_MEANING''
199                             ,p_level    => C_LEVEL_ERROR
200                             ,p_module   => l_log_module);
204 END IF;
201      END IF;
202  END CASE;
203 --
205 --
206 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
207 
208       trace
209          (p_msg      => ''return value. meaning = ''||l_meaning_meaning
210          ,p_level    => C_LEVEL_PROCEDURE
211          ,p_module   => l_log_module);
212 
213       trace
214          (p_msg      => ''END of GetMeaning''
215          ,p_level    => C_LEVEL_PROCEDURE
216          ,p_module   => l_log_module);
217 END IF;
218 --
219 --xla_ae_sources_pkg.g_array_meaning := l_array_meaning ;
220 --
221 RETURN l_meaning_meaning ;
222 --
223 EXCEPTION
224 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
225  --
226       xla_ae_journal_entry_pkg.g_global_status      :=  xla_ae_journal_entry_pkg.C_INVALID;
227       xla_accounting_err_pkg.build_message
228                 (p_appli_s_name            => ''XLA''
229                 ,p_msg_name                => ''XLA_AP_FLEX_VALUE_MEANING''
230                 ,p_token_1                 => ''VALUE_SET_NAME''
231                 ,p_value_1                 =>  xla_flex_pkg.get_value_set_name(p_flex_value_set_id)
232                 ,p_token_2                 => ''FLEX_VALUE''
233                 ,p_value_2                 =>  p_flex_value
234                 ,p_token_3                 => ''SOURCE_NAME''
235                 ,p_value_3                 =>  xla_ae_sources_pkg.GetSourceName(
236                                                      p_source_code
237                                                    , p_source_type_code
238                                                    , p_source_application_id
239                                                      )
240                 ,p_token_4                 => ''PRODUCT_NAME''
241                 ,p_value_4                 => xla_ae_journal_entry_pkg.g_cache_event.application_name
242                 ,p_entity_id               => xla_ae_journal_entry_pkg.g_cache_event.entity_id
243                 ,p_event_id                => xla_ae_journal_entry_pkg.g_cache_event.event_id
244                 ,p_ledger_id               => xla_ae_journal_entry_pkg.g_cache_event.target_ledger_id
245         );
246 
247      IF (C_LEVEL_ERROR >= g_log_level) THEN
248                          trace
249                             (p_msg      => ''ERROR: XLA_AP_FLEX_VALUE_MEANING''
250                             ,p_level    => C_LEVEL_ERROR
251                             ,p_module   => l_log_module);
252      END IF;
253 
254      RETURN NULL;
255   WHEN xla_exceptions_pkg.application_exception THEN
256       RAISE;
257   WHEN OTHERS THEN
258        xla_exceptions_pkg.raise_message
259            (p_location => ''$package_name$.GetMeaning'');
260 END GetMeaning;
261 --
262 ';
263 --
264 --
265 --=====================================================================
266 
267 C_WHEN_THEN         CONSTANT      VARCHAR2(4000):= '
268    WHEN $flex_value_set_id$ THEN
269 
270     IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
271 
275          ,p_module   => l_log_module);
272       trace
273          (p_msg      => ''-> CALL  DBMS_UTILITY.get_hash_value''
274          ,p_level    => C_LEVEL_PROCEDURE
276 
277     END IF;
278 
279     l_Idx := DBMS_UTILITY.get_hash_value(TO_CHAR(p_flex_value),1,1073741824);
280 
281     IF xla_ae_sources_pkg.g_array_meaning.EXISTS($flex_value_set_id$) AND
282        xla_ae_sources_pkg.g_array_meaning($flex_value_set_id$).array_flex_value.EXISTS(l_Idx) AND
283        xla_ae_sources_pkg.g_array_meaning($flex_value_set_id$).array_flex_value(l_Idx)   = p_flex_value
284        THEN
285 
286        l_meaning_meaning    := xla_ae_sources_pkg.g_array_meaning($flex_value_set_id$).array_meaning(l_Idx);
287 
288     ELSE
289 
290      $meaning_meaning$
291      xla_ae_sources_pkg.g_array_meaning($flex_value_set_id$).array_flex_value(l_Idx)       := p_flex_value;
292      xla_ae_sources_pkg.g_array_meaning($flex_value_set_id$).array_meaning(l_Idx)          := l_meaning_meaning;
296 --
293 
294     END IF;
295 ';
297 --
298 --=============================================================================
299 --               *********** Local Trace Routine **********
300 --=============================================================================
301 
302 C_LEVEL_STATEMENT     CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
303 C_LEVEL_PROCEDURE     CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
304 C_LEVEL_EVENT         CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
305 C_LEVEL_EXCEPTION     CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
306 C_LEVEL_ERROR         CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
307 C_LEVEL_UNEXPECTED    CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
308 
309 C_LEVEL_LOG_DISABLED  CONSTANT NUMBER := 99;
310 C_DEFAULT_MODULE      CONSTANT VARCHAR2(240) := 'xla.plsql.xla_cmp_source_pkg';
311 
312 g_log_level           NUMBER;
313 g_log_enabled         BOOLEAN;
314 
315 PROCEDURE trace
316            (p_msg                        IN VARCHAR2
317            ,p_level                      IN NUMBER
318            ,p_module                     IN VARCHAR2) IS
319 BEGIN
320 ----------------------------------------------------------------------------
321 -- Following is for FND log.
322 ----------------------------------------------------------------------------
323 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
324           fnd_log.message(p_level, p_module);
325 ELSIF p_level >= g_log_level THEN
326           fnd_log.string(p_level, p_module, p_msg);
327 END IF;
328 
329 EXCEPTION
330        WHEN xla_exceptions_pkg.application_exception THEN
331           RAISE;
332        WHEN OTHERS THEN
333           xla_exceptions_pkg.raise_message
334              (p_location   => 'xla_cmp_source_pkg.trace');
335 END trace;
336 --
337 
338 /*-------------------------------------------------------+
339 | Private function                                       |
340 |                                                        |
341 |   GetDataType                                          |
342 |                                                        |
343 |   It generates the source datatype in the AAD packages |
344 |                                                        |
345 +--------------------------------------------------------*/
346 FUNCTION GetDataType(p_Datatype_code IN VARCHAR2)
347 RETURN VARCHAR2
348 IS
349 l_log_module         VARCHAR2(240);
350 BEGIN
351 IF g_log_enabled THEN
352       l_log_module := C_DEFAULT_MODULE||'.GetDataType';
353 END IF;
354   CASE p_Datatype_code
355     WHEN C_NUMBER_DATATYPE    THEN  RETURN 'NUMBER';
356     WHEN C_INTEGER_DATATYPE   THEN  RETURN 'PLS_INTEGER';
357     WHEN C_FLEXFIELD_DATATYPE THEN  RETURN 'NUMBER';
358     WHEN C_DATE_DATATYPE      THEN  RETURN 'DATE';
359     WHEN C_CHAR_DATATYPE      THEN  RETURN 'VARCHAR2';
360     ELSE
361        IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
362           trace
363                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR : invalid datatype ='|| p_Datatype_code
364                ,p_level    => C_LEVEL_EXCEPTION
365                ,p_module   => l_log_module);
366        END IF;
367        RETURN NULL;
368   END CASE;
369 END GetDataType;
370 
371 /*------------------------------------------------------------+
372 | Private function                                            |
373 |                                                             |
374 |   GetStandardSource                                         |
375 |                                                             |
376 |   It generates the call to standard sources in AAD packages |
377 |   according to the p_variable value                         |
378 |                                                             |
379 +------------------------------------------------------------*/
380 
381 FUNCTION GetStandardSource(
382    p_Index                        IN BINARY_INTEGER
383  , p_array_source_code            IN t_array_VL30
384  , p_array_datatype_code          IN t_array_VL1
385  , p_variable                     IN VARCHAR2
386  )
387 RETURN VARCHAR2
388 IS
389 l_source             VARCHAR2(10000);
390 l_variable           VARCHAR2(1);
391 l_error_message      VARCHAR2(4000);
392 l_log_module         VARCHAR2(240);
393 invalid_source       EXCEPTION;
394 BEGIN
395 IF g_log_enabled THEN
396       l_log_module := C_DEFAULT_MODULE||'.GetStandardSource';
397 END IF;
398 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
399       trace
400          (p_msg      => 'BEGIN of GetStandardSource '
401          ,p_level    => C_LEVEL_PROCEDURE
402          ,p_module   => l_log_module);
403 
404 END IF;
405 
406 l_variable := NVL(p_variable,'N');
407 l_source   := NULL;
408 
409 IF  NOT p_array_source_code.EXISTS(p_Index) OR
410     p_array_source_code(p_Index)  IS NULL THEN
411     l_error_message := 'Source Index '||p_Index||' does not exist in the source cache';
412     raise invalid_source;
413 END IF;
414 
415 CASE  l_variable
416    WHEN 'H' THEN    -- header variable
417       l_source := 'g_array_event(l_event_id).array_value_$datatype$(''source_$index$'')';
418       l_source := REPLACE(l_source, '$index$', p_index);
419       CASE p_array_datatype_code(p_Index)
420            WHEN 'F' THEN l_source  := REPLACE(l_source,'$datatype$','num') ;
421            WHEN 'N' THEN l_source  := REPLACE(l_source,'$datatype$','num') ;
422            WHEN 'C' THEN l_source  := REPLACE(l_source,'$datatype$','char') ;
423            WHEN 'D' THEN l_source  := REPLACE(l_source,'$datatype$','date') ;
424            ELSE l_source  := REPLACE(l_source,'$datatype$',p_array_datatype_code(p_Index)) ;
425       END CASE;
429       l_error_message := 'Invalid parameter variable =' ||p_variable||' for source Index ='||p_Index;
426    WHEN 'L' THEN   l_source  := 'l_array_source_'||p_Index||'(Idx)';
427    WHEN 'N' THEN   l_source  := 'p_source_'||p_Index;
428    ELSE
430       RAISE invalid_source;
431 END CASE;
432 
433 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
434       trace
435          (p_msg      => 'END of GetStandardSource '
436          ,p_level    => C_LEVEL_PROCEDURE
437          ,p_module   => l_log_module);
438 
439 END IF;
440 RETURN l_source;
441 EXCEPTION
442    WHEN invalid_source THEN
443    IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
444          trace
445             (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR ='||l_error_message
446             ,p_level    => C_LEVEL_EXCEPTION
447             ,p_module   => l_log_module);
448    END IF;
449    RETURN NULL;
450    WHEN xla_exceptions_pkg.application_exception   THEN
451     IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
452             trace
453                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||sqlerrm
454                ,p_level    => C_LEVEL_EXCEPTION
455                ,p_module   => l_log_module);
456     END IF;
457     RETURN NULL;
458    WHEN OTHERS    THEN
459       xla_exceptions_pkg.raise_message
460          (p_location => 'xla_cmp_source_pkg.GetStandardSource');
461 END GetStandardSource;
462 
463 /*----------------------------------------------------------+
464 | Private function                                          |
465 |                                                           |
466 |   GetStandardSource                                       |
467 |                                                           |
468 |  It generates the call to source meanings in AAD packages |
469 |  according to the p_variable value                        |
470 |                                                           |
471 +----------------------------------------------------------*/
472 
473 FUNCTION GetMeaningSource(
474     p_Index                        IN BINARY_INTEGER
475   , p_array_source_code            IN t_array_VL30
476   , p_variable                     IN VARCHAR2
477  )
478 RETURN VARCHAR2
479 IS
480 l_source             VARCHAR2(10000);
481 l_variable           VARCHAR2(1);
482 l_log_module         VARCHAR2(240);
483 l_error_message      VARCHAR2(4000);
484 invalid_source       EXCEPTION;
485 BEGIN
486 IF g_log_enabled THEN
487       l_log_module := C_DEFAULT_MODULE||'.GetMeaningSource';
488 END IF;
489 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
490       trace
491          (p_msg      => 'BEGIN of GetMeaningSource '
492          ,p_level    => C_LEVEL_PROCEDURE
493          ,p_module   => l_log_module);
494 END IF;
495 
496 l_variable := NVL(p_variable,'N');
497 l_source   := NULL;
498 
499 IF NOT p_array_source_code.EXISTS(p_Index) OR
500     p_array_source_code(p_Index) IS NULL THEN
501     l_error_message := 'Source Index '||p_Index||' does not exist in the source cache';
502     RAISE invalid_source;
503 END IF;
504 
505 CASE  l_variable
506     WHEN 'H' THEN
507      l_source := 'g_array_event(l_event_id).array_value_char(''source_$index$_meaning'')';
508      l_source := REPLACE(l_source, '$index$', p_index);
509     WHEN 'L' THEN  l_source  := 'l_array_source_'||p_Index||'_meaning(Idx)';
510     WHEN 'N' THEN  l_source  := 'p_source_'||p_Index||'_meaning';
511     ELSE
512       IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
513          l_error_message := ' p_variable value ='||l_variable||' passed is invalid';
514          trace
515             (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR'
516             ,p_level    => C_LEVEL_EXCEPTION
517             ,p_module   => l_log_module);
518        END IF;
519       l_source  := 'NULL';
520 END CASE;
521 
522 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
523       trace
527 
524          (p_msg      => 'END of GetMeaningSource '
525          ,p_level    => C_LEVEL_PROCEDURE
526          ,p_module   => l_log_module);
528 END IF;
529 RETURN l_source;
530 EXCEPTION
531    WHEN invalid_source THEN
532    IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
533          trace
534             (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||l_error_message
535             ,p_level    => C_LEVEL_EXCEPTION
536             ,p_module   => l_log_module);
537    END IF;
538    RETURN 'NULL';
539    WHEN xla_exceptions_pkg.application_exception   THEN
540    IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
541             trace
542                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||sqlerrm
543                ,p_level    => C_LEVEL_EXCEPTION
544                ,p_module   => l_log_module);
545     END IF;
546     RETURN 'NULL';
547    WHEN OTHERS    THEN
548       xla_exceptions_pkg.raise_message
549          (p_location => 'xla_cmp_source_pkg.GetMeaningSource');
550 END GetMeaningSource;
551 
552 /*----------------------------------------------------------+
553 | Private function                                          |
554 |                                                           |
555 |   GetSystemSource                                         |
556 |                                                           |
557 |  It generates the call to system sources in AAD packages  |
558 |                                                           |
559 +----------------------------------------------------------*/
560 
561 FUNCTION GetSystemSource(
562    p_Index                        IN BINARY_INTEGER
563  , p_rec_sources                  IN t_rec_sources
564  )
565 RETURN VARCHAR2
566 IS
567 
568 C_SYSTEM_SOURCE                      CONSTANT     VARCHAR2(10000) := '
569 xla_ae_sources_pkg.GetSystemSource$type$(
570    p_source_code           => ''$source$''
571  , p_source_type_code      => ''$source_type_code$''
572  , p_source_application_id =>  $source_application_id$
573 )';
574 
575 l_source                VARCHAR2(10000);
576 l_error_message         VARCHAR2(4000);
577 invalid_source          EXCEPTION;
578 l_log_module            VARCHAR2(240);
579 BEGIN
580 IF g_log_enabled THEN
581       l_log_module := C_DEFAULT_MODULE||'.GetSystemSource';
582 END IF;
583 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
584       trace
585          (p_msg      => 'BEGIN of GetSystemSource'
586          ,p_level    => C_LEVEL_PROCEDURE
587          ,p_module   => l_log_module);
588 END IF;
589 
590 IF NOT p_rec_sources.array_source_code.EXISTS(p_Index) OR
591     p_rec_sources.array_source_code(p_Index) IS NULL THEN
592     l_error_message := 'Source Index '||p_Index||' does not exist in the source cache';
596 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
593     RAISE invalid_source;
594 END IF;
595 
597       trace
601          ,p_level    => C_LEVEL_STATEMENT
598          (p_msg      => ' source code = '||p_rec_sources.array_source_code(p_Index)||
599                         ' - source type code = '||p_rec_sources.array_source_type_code(p_Index)||
600                         ' - source application id = '||p_rec_sources.array_application_id(p_Index)
602          ,p_module   => l_log_module);
603 END IF;
604 
605 l_source  := C_SYSTEM_SOURCE;
606 l_source  := REPLACE(l_source,'$source$'               , p_rec_sources.array_source_code(p_Index));
607 l_source  := REPLACE(l_source,'$source_type_code$'     , p_rec_sources.array_source_type_code(p_Index));
608 l_source  := REPLACE(l_source,'$source_application_id$', p_rec_sources.array_application_id(p_Index));
609 CASE p_rec_sources.array_datatype_code(p_Index)
610 
611  WHEN C_NUMBER_DATATYPE    THEN l_source  := REPLACE(l_source,'$type$'  ,'Num');
612  WHEN C_DATE_DATATYPE      THEN l_source  := REPLACE(l_source,'$type$'  ,'Date');
613  WHEN C_CHAR_DATATYPE      THEN l_source  := REPLACE(l_source,'$type$'  ,'Char');
614  WHEN C_FLEXFIELD_DATATYPE THEN l_source  := REPLACE(l_source,'$type$'  ,'Num');
615  WHEN C_INTEGER_DATATYPE   THEN l_source  := REPLACE(l_source,'$type$'  ,'Num');
616  ELSE
617     l_source:= NULL;
618     l_error_message:= 'Invalid datatype '||p_rec_sources.array_datatype_code(p_Index)
619                     ||' for the system source code '
620                     ||p_rec_sources.array_source_code(p_Index);
621     RAISE invalid_source;
622 END CASE;
623 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
624 
625       trace
626          (p_msg      => 'END of GetSystemSource'
627          ,p_level    => C_LEVEL_PROCEDURE
628          ,p_module   => l_log_module);
629 
630 END IF;
631 RETURN l_source;
632 EXCEPTION
633    WHEN invalid_source THEN
634         IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
635             trace
636                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||l_error_message
637                ,p_level    => C_LEVEL_EXCEPTION
638                ,p_module   => l_log_module);
639         END IF;
640         RETURN NULL;
641    WHEN xla_exceptions_pkg.application_exception   THEN
642         IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
643             trace
644                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||sqlerrm
645                ,p_level    => C_LEVEL_EXCEPTION
646                ,p_module   => l_log_module);
647         END IF;
648         RETURN NULL;
649    WHEN OTHERS    THEN
650       xla_exceptions_pkg.raise_message
651          (p_location => 'xla_cmp_source_pkg.GetSystemSource');
652 END GetSystemSource;
653 
654 /*----------------------------------------------------------+
655 | Private function                                          |
656 |                                                           |
657 |   GetCustomSource                                         |
658 |                                                           |
659 |  It generates the call to custom sources in AAD packages  |
660 |                                                           |
661 +----------------------------------------------------------*/
662 
663 FUNCTION GetCustomSource(
664     p_Index                        IN  BINARY_INTEGER
665   , p_rec_sources                  IN  OUT NOCOPY t_rec_sources
666   , p_variable                     IN  VARCHAR2 DEFAULT NULL
667  )
668 RETURN VARCHAR2
669 IS
670 CURSOR source_cur ( p_source_code            VARCHAR2
671                   , p_source_type_code       VARCHAR2
672                   , p_source_application_id  NUMBER)
673 IS
674 SELECT DISTINCT
675        xsp.ref_source_application_id
676      , xsp.ref_source_type_code
677      , xsp.ref_source_code
678      , xsp.parameter_type_code
679      , xsp.constant_value
680      , xsp.user_sequence
681 FROM   xla_source_params     xsp
682 WHERE  xsp.source_code         = p_source_code
683    AND xsp.source_type_code    = p_source_type_code
684    AND xsp.application_id      = p_source_application_id
685 ORDER BY user_sequence
686 ;
687 
688 l_source                VARCHAR2(32000);
689 l_parms                 VARCHAR2(32000);
690 l_first                 BOOLEAN;
691 l_Idx                   BINARY_INTEGER;
692 l_rec_sources           t_rec_sources;
693 l_error_message         VARCHAR2(4000);
694 invalid_source          EXCEPTION;
695 l_log_module            VARCHAR2(240);
696 BEGIN
697 IF g_log_enabled THEN
698       l_log_module := C_DEFAULT_MODULE||'.GetCustomSource';
699 END IF;
700 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
701       trace
702          (p_msg      => 'BEGIN of GetCustomSource'
703          ,p_level    => C_LEVEL_PROCEDURE
704          ,p_module   => l_log_module);
705 END IF;
706 
707 l_rec_sources := p_rec_sources;
708 l_source      := NULL;
709 l_parms       := NULL;
710 
711 IF NOT l_rec_sources.array_source_code.EXISTS(p_Index) OR
712     l_rec_sources.array_source_code(p_Index) IS NULL THEN
713    l_error_message := 'Source Index '||p_Index||' does not exist in the source cache';
714    RAISE invalid_source;
715 END IF;
716 
717 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
718       trace
719          (p_msg      => ' source code = '||l_rec_sources.array_source_code(p_Index)||
720                         ' - source type code = '||l_rec_sources.array_source_type_code(p_Index)||
721                         ' - source application id = '||l_rec_sources.array_application_id(p_Index)
722          ,p_level    => C_LEVEL_STATEMENT
723          ,p_module   => l_log_module);
724 END IF;
725 
726 IF l_rec_sources.array_plsql_function(p_Index) IS NOT NULL THEN
727 
728    l_first := TRUE;
729 
730    FOR source_rec IN  source_cur(
731          p_source_code           => l_rec_sources.array_source_code(p_Index)
735     LOOP
732        , p_source_type_code      => l_rec_sources.array_source_type_code(p_Index)
733        , p_source_application_id => l_rec_sources.array_application_id(p_Index)
734                              )
736 
737         IF l_first THEN
738            l_parms := l_parms || g_chr_newline;
739            l_first := FALSE;
740         ELSE
741            l_parms := l_parms || g_chr_newline ||', ';
742         END IF;
743 
744         IF source_rec.parameter_type_code IN (C_NUMBER_DATATYPE ,
745                                               C_INTEGER_DATATYPE,
746                                               C_FLEXFIELD_DATATYPE) THEN
747 
748           l_parms := l_parms || source_rec.constant_value ;
749 
750         ELSIF source_rec.parameter_type_code = C_DATE_DATATYPE THEN
751 
755 
752           l_parms := l_parms || 'fnd_date.canonical_to_date('''||source_rec.constant_value ||''')';
753 
754         ELSIF source_rec.parameter_type_code = C_CHAR_DATATYPE THEN
756           l_parms := l_parms || ''''||REPLACE(source_rec.constant_value,'''','''''')|| '''';
757 
758         ELSIF source_rec.parameter_type_code ='S' OR
759              (source_rec.parameter_type_code = 'Y' AND
760               source_rec.ref_source_application_id =602)  THEN
761 
762           l_Idx := CacheSource (
763                      p_source_code           => source_rec.ref_source_code
764                    , p_source_type_code      => source_rec.ref_source_type_code
765                    , p_source_application_id => source_rec.ref_source_application_id
766                    , p_rec_sources           => l_rec_sources
767                     );
768 
769           l_parms := l_parms || GenerateSource(
770                                  p_Index           => l_Idx
771                                , p_rec_sources     => l_rec_sources
772                                , p_variable        => p_variable
773                                , p_translated_flag => 'N'
774                               );
775         END IF;
776     END LOOP;
777 
778    l_source  := l_rec_sources.array_plsql_function(p_Index);
779    IF l_parms IS NOT NULL THEN
780       l_source := l_source ||'('|| l_parms|| ')';
781    END IF;
782 
783 ELSE
784    l_error_message := 'Invalid custom source definition= '||
785                       ' - source code = '||l_rec_sources.array_source_code(p_Index)||
786                       ' - source type code = '||l_rec_sources.array_source_type_code(p_Index)||
787                       ' - source application id = '||l_rec_sources.array_application_id(p_Index);
788    RAISE invalid_source;
789 END IF;
790 --
791 --
792 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
793       trace
794          (p_msg      => 'END of GetCustomSource'
795          ,p_level    => C_LEVEL_PROCEDURE
796          ,p_module   => l_log_module);
797 END IF;
798 p_rec_sources := l_rec_sources;
799 RETURN l_source;
800 EXCEPTION
801    WHEN invalid_source THEN
802       p_rec_sources := l_rec_sources;
803       IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
804          trace
805             (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR ='||l_error_message
806             ,p_level    => C_LEVEL_EXCEPTION
807             ,p_module   => l_log_module);
808       END IF;
809       RETURN NULL;
810    WHEN xla_exceptions_pkg.application_exception   THEN
811       p_rec_sources := l_rec_sources;
812       IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
813             trace
814                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||sqlerrm
815                ,p_level    => C_LEVEL_EXCEPTION
816                ,p_module   => l_log_module);
817        END IF;
818        RAISE;
819    WHEN OTHERS    THEN
820       xla_exceptions_pkg.raise_message
821          (p_location => 'xla_cmp_source_pkg.GetCustomSource');
822 END GetCustomSource;
823 
824 /*----------------------------------------------------------+
825 | Private function                                          |
826 |                                                           |
827 |   GetValueSetSource                                       |
828 |                                                           |
829 |  It generates the call to custom sources in AAD packages  |
830 |                                                           |
831 +----------------------------------------------------------*/
832 
833 FUNCTION GetValueSetSource(
834      p_Index                        IN  BINARY_INTEGER
835    , p_rec_sources                  IN  OUT NOCOPY t_rec_sources
836    , p_variable                     IN VARCHAR2 DEFAULT NULL
837    , p_translated_flag              IN VARCHAR2 DEFAULT NULL
838  )
839 RETURN VARCHAR2
840 IS
841 
842 C_VALUE_SET                   CONSTANT     VARCHAR2(10000) := '
843 GetMeaning (
844   p_flex_value_set_id        => $flex_value_set_id$
845 , p_flex_value               => TO_CHAR($source$)
846 , p_source_code              => ''$source_code$''
850 ';
847 , p_source_type_code         => ''$source_type_code$''
848 , p_source_application_id    => $source_application_id$
849 )
851 
852 l_source                VARCHAR2(32000);
853 l_rec_sources           t_rec_sources;
854 l_error_message         VARCHAR2(4000);
855 invalid_source          EXCEPTION;
856 l_log_module            VARCHAR2(240);
857 BEGIN
858 IF g_log_enabled THEN
859       l_log_module := C_DEFAULT_MODULE||'.GetValueSetSource';
860 END IF;
861 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
862       trace
863          (p_msg      => 'BEGIN of GetValueSetSource'
864          ,p_level    => C_LEVEL_PROCEDURE
865          ,p_module   => l_log_module);
866 END IF;
867 
868 l_rec_sources := p_rec_sources;
869 l_source      := NULL;
870 
871 IF  NOT l_rec_sources.array_source_code.EXISTS(p_Index) OR
872      l_rec_sources.array_source_code(p_Index) IS NULL THEN
873      l_error_message := 'Source Index '||p_Index||' does not exist in the source cache';
874      RAISE invalid_source;
875 END IF;
876 
877 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
878       trace
879          (p_msg      => ' source code = '||l_rec_sources.array_source_code(p_Index)||
880                         ' - source type code = '||l_rec_sources.array_source_type_code(p_Index)||
881                         ' - source application id = '||l_rec_sources.array_application_id(p_Index)||
882                         ' - flex_value_set_id = ' ||l_rec_sources.array_flex_value_set_id(p_Index)
883          ,p_level    => C_LEVEL_STATEMENT
884          ,p_module   => l_log_module);
885 END IF;
886 
887 IF (NVL(p_translated_flag,'N') = 'Y' ) THEN
888 
889   -- get source meaning
890   l_source    := C_VALUE_SET ;
891   l_source    := REPLACE(l_source,'$flex_value_set_id$',
892                        l_rec_sources.array_flex_value_set_id(p_Index));
893   l_source    := REPLACE(l_source,'$source_code$',
894                        l_rec_sources.array_source_code(p_Index));
895   l_source    := REPLACE(l_source,'$source_type_code$',
896                        l_rec_sources.array_source_type_code(p_Index));
897   l_source    := REPLACE(l_source,'$source_application_id$',
898                        l_rec_sources.array_application_id(p_Index));
899 
900 ELSE --  get source code
901   l_source    := '$source$';
902 END IF;
903 
904 
905 CASE l_rec_sources.array_source_type_code(p_Index)
906 
907     WHEN C_CUSTOM_SOURCE_TYPE THEN
908 
909         l_source    := REPLACE(l_source,'$source$',
910                                GetCustomSource(
914                                 ));
911                                   p_Index
912                                 , l_rec_sources
913                                 , p_variable
915 
916     WHEN C_SEEDED_SOURCE_TYPE THEN
917 
918        l_source    := REPLACE(l_source,'$source$',
919                              GetStandardSource(
920                                  p_Index
921                                , l_rec_sources.array_source_code
922                                , l_rec_sources.array_datatype_code
923                                , p_variable
924                                 ));
925 
926     WHEN  C_SYSTEM_SOURCE_TYPE THEN
927 
928        l_source :=REPLACE(l_source,'$source$',
929                                GetSystemSource(
930                                  p_Index
931                                , l_rec_sources
932                                 ));
933 
934     ELSE
935 
936        l_source    := NULL;
937        l_error_message:= SUBSTR('Invalid source type code '
938                             ||l_rec_sources.array_source_type_code(p_Index)
939                             ||' defined for source '
940                             ||l_rec_sources.array_source_code(p_Index),1,4000);
941 
942        RAISE invalid_source;
943 END CASE;
947 l_source  := REPLACE(l_source,'$product_name$',
944 
945 l_source  := REPLACE(l_source,'$source_name$',
946                   REPLACE(l_rec_sources.array_source_name(p_Index),'''',''''''));
948                   xla_cmp_pad_pkg.GetApplicationName (l_rec_sources.array_application_id(p_Index)));
949 
950 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
951    trace
952        (p_msg      => 'END of GetValueSetSource'
953        ,p_level    => C_LEVEL_PROCEDURE
954        ,p_module   => l_log_module);
955 
956 END IF;
957 p_rec_sources := l_rec_sources;
958 RETURN l_source;
959 EXCEPTION
960     WHEN invalid_source THEN
961       p_rec_sources := l_rec_sources;
962       IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
963          trace
964             (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR ='||l_error_message
965             ,p_level    => C_LEVEL_EXCEPTION
966             ,p_module   => l_log_module);
967       END IF;
968       RETURN NULL;
969    WHEN xla_exceptions_pkg.application_exception   THEN
970       p_rec_sources := l_rec_sources;
971       IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
972             trace
973                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||sqlerrm
974                ,p_level    => C_LEVEL_EXCEPTION
975                ,p_module   => l_log_module);
976        END IF;
977        RAISE;
978    WHEN OTHERS    THEN
979       xla_exceptions_pkg.raise_message
980          (p_location => 'xla_cmp_source_pkg.GetValueSetSource');
981 END GetValueSetSource;
982 
983 /*----------------------------------------------------------+
984 | Private function                                          |
985 |                                                           |
986 |   GetLookupSource                                         |
987 |                                                           |
988 |  It generates the call to lookup sources in AAD packages  |
989 |                                                           |
990 +----------------------------------------------------------*/
991 
992 FUNCTION GetLookupSource(
993          p_Index                        IN BINARY_INTEGER
994        , p_rec_sources                  IN  OUT NOCOPY t_rec_sources
995        , p_variable                     IN VARCHAR2 DEFAULT NULL
996        , p_translated_flag              IN VARCHAR2 DEFAULT NULL
997 )
998 RETURN VARCHAR2
999 IS
1000 C_LOOKUP_SOURCE           CONSTANT     VARCHAR2(10000) := '
1001 xla_ae_sources_pkg.GetLookupMeaning(
1002   p_lookup_code            => TO_CHAR($source$)
1003 , p_lookup_type            => ''$lookup_code$''
1004 , p_view_application_id    => $view_application_id$
1005 , p_source_code            => ''$source_code$''
1006 , p_source_type_code       => ''$source_type_code$''
1007 , p_source_application_id  => $source_application_id$
1008 )
1009 ';
1010 --
1011 C_LOOKUP_MEANING          CONSTANT     VARCHAR2(10000) := '
1012 ValidateLookupMeaning(
1013   p_meaning                => $source_meaning$
1014 , p_lookup_code            => TO_CHAR($source$)
1015 , p_lookup_type            => ''$lookup_code$''
1016 , p_source_code            => ''$source_code$''
1017 , p_source_type_code       => ''$source_type_code$''
1018 , p_source_application_id  => $source_application_id$
1019 )
1020 ';
1021 
1022 l_source                VARCHAR2(32000);
1023 l_rec_sources           t_rec_sources;
1024 invalid_source          EXCEPTION;
1025 l_error_message         VARCHAR2(4000);
1026 l_log_module            VARCHAR2(240);
1027 BEGIN
1028 IF g_log_enabled THEN
1029       l_log_module := C_DEFAULT_MODULE||'.GetLookupSource';
1030 END IF;
1031 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1032 
1033       trace
1034          (p_msg      => 'BEGIN of GetLookupSource'
1035          ,p_level    => C_LEVEL_PROCEDURE
1036          ,p_module   => l_log_module);
1037 
1038 END IF;
1039 l_rec_sources := p_rec_sources;
1040 l_source      := NULL;
1041 
1042 IF   NOT l_rec_sources.array_source_code.EXISTS(p_Index) OR
1043      l_rec_sources.array_source_code(p_Index)  IS NULL THEN
1044      l_error_message := 'Source Index :'||p_Index||' does not exist in the source cache';
1045     RAISE invalid_source;
1046 END IF;
1047 
1048 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1049       trace
1050          (p_msg      => ' source code = '||l_rec_sources.array_source_code(p_Index)||
1051                         ' - source type code = '||l_rec_sources.array_source_type_code(p_Index)||
1052                         ' - source application id = '||l_rec_sources.array_application_id(p_Index)||
1053                         ' - lookup code='||l_rec_sources.array_lookup_type(p_Index)||
1054                         ' - view application id='||l_rec_sources.array_view_application_id(p_Index)
1055          ,p_level    => C_LEVEL_STATEMENT
1056          ,p_module   => l_log_module);
1057 END IF;
1058 
1059 IF ( NVL(p_translated_flag,'N') = 'Y' )
1060 THEN
1061 
1062    CASE l_rec_sources.array_source_type_code(p_Index)
1063 
1064    WHEN C_SEEDED_SOURCE_TYPE THEN
1065 
1066      l_source    := C_LOOKUP_MEANING ;
1067 
1068      l_source    := REPLACE(l_source,'$source$',
1069                              GetStandardSource(
1070                               p_Index
1071                             , l_rec_sources.array_source_code
1072                             , l_rec_sources.array_datatype_code
1073                             , p_variable
1074                             ));
1075 
1076      l_source    := REPLACE(l_source,'$source_meaning$',
1077                             GetMeaningSource(
1078                               p_Index
1079                             , l_rec_sources.array_source_code
1080                             , p_variable
1081                              ));
1082 
1083    WHEN C_CUSTOM_SOURCE_TYPE THEN
1087     l_source    := REPLACE(l_source,'$source$',
1084 
1085     l_source    := C_LOOKUP_SOURCE ;
1086 
1088                             GetCustomSource(
1089                               p_Index
1090                             , l_rec_sources
1091                             , p_variable
1092                              ));
1093 
1094   WHEN C_SYSTEM_SOURCE_TYPE THEN
1095 
1096         l_source    := C_LOOKUP_SOURCE ;
1097 
1098           l_source :=REPLACE(l_source,'$source$',
1099                              GetSystemSource(
1100                                 p_Index
1101                               , l_rec_sources
1102                               ));
1103   ELSE
1104       l_error_message:= SUBSTR('Invalid source type code '
1105                             ||l_rec_sources.array_source_type_code(p_Index)
1106                             ||' defined for source '
1107                             ||l_rec_sources.array_source_code(p_Index),1,4000);
1108       RAISE invalid_source;
1109   END CASE;
1110 
1111   l_source  := REPLACE(l_source,'$lookup_code$'          ,
1112                 l_rec_sources.array_lookup_type(p_Index));
1113   l_source  := REPLACE(l_source,'$view_application_id$'  ,
1114                 l_rec_sources.array_view_application_id(p_Index));
1115   l_source  := REPLACE(l_source,'$source_code$'          ,
1116                 l_rec_sources.array_source_code(p_Index));
1117   l_source  := REPLACE(l_source,'$source_type_code$'     ,
1118                 l_rec_sources.array_source_type_code(p_Index));
1119   l_source  := REPLACE(l_source,'$source_application_id$',
1120                 l_rec_sources.array_application_id(p_Index));
1121 
1122 ELSE
1123 
1124 CASE l_rec_sources.array_source_type_code(p_Index)
1125     WHEN C_CUSTOM_SOURCE_TYPE THEN
1126 
1127           l_source    :=  GetCustomSource(
1128                                   p_Index
1129                                 , l_rec_sources
1130                                 , p_variable
1131                                 );
1132 
1133     WHEN C_SEEDED_SOURCE_TYPE THEN
1134 
1135          l_source    :=  GetStandardSource(
1136                               p_Index
1137                             , l_rec_sources.array_source_code
1138                             , l_rec_sources.array_datatype_code
1139                             , p_variable
1140                          );
1141 
1142 
1143     WHEN C_SYSTEM_SOURCE_TYPE THEN
1144 
1145            l_source    :=  GetSystemSource(
1146                                 p_Index
1147                               , l_rec_sources
1148                               );
1149 
1150    ELSE
1151      l_error_message:= SUBSTR('Invalid source type code '
1152                             ||l_rec_sources.array_source_type_code(p_Index)
1153                             ||' defined for source '
1154                             ||l_rec_sources.array_source_code(p_Index),1,4000);
1155 
1156      RAISE invalid_source;
1157    END CASE;
1158 
1159    l_source  := REPLACE(l_source,'$source_name$',
1160          REPLACE(l_rec_sources.array_source_name(p_Index),'''',''''''));
1161    l_source  := REPLACE(l_source,'$product_name$',
1162         xla_cmp_pad_pkg.GetApplicationName (l_rec_sources.array_application_id(p_Index)));
1163 END IF;
1164 
1165 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1166 
1167         trace
1168            (p_msg      => 'END of GetLookupSource'
1169            ,p_level    => C_LEVEL_PROCEDURE
1170            ,p_module   => l_log_module);
1171 
1172 END IF;
1173 p_rec_sources := l_rec_sources;
1174 RETURN l_source;
1175 EXCEPTION
1176    WHEN invalid_source THEN
1177       p_rec_sources := l_rec_sources;
1178       IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1179          trace
1180             (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR ='||l_error_message
1181             ,p_level    => C_LEVEL_EXCEPTION
1182             ,p_module   => l_log_module);
1183       END IF;
1184       RETURN 'NULL';
1185    WHEN xla_exceptions_pkg.application_exception   THEN
1186       p_rec_sources := l_rec_sources;
1187       IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1188             trace
1189                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||sqlerrm
1190                ,p_level    => C_LEVEL_EXCEPTION
1191                ,p_module   => l_log_module);
1192        END IF;
1193        RAISE;
1194    WHEN OTHERS    THEN
1195       xla_exceptions_pkg.raise_message
1196          (p_location => 'xla_cmp_source_pkg.GetLookupSource');
1197 END GetLookupSource;
1198 
1199 /*----------------------------------------------------------+
1200 | Private function                                          |
1201 |                                                           |
1202 |   GetKeyFlexfieldSource                                   |
1203 |                                                           |
1204 |  It generates the call to key flexfield sources in AAD    |
1205 |  packages                                                 |
1206 |                                                           |
1207 +----------------------------------------------------------*/
1208 --
1209 FUNCTION GetKeyFlexfieldSource  (
1210    p_Index                        IN BINARY_INTEGER
1211  , p_rec_sources                  IN OUT NOCOPY t_rec_sources
1212  , p_variable                     IN VARCHAR2 DEFAULT NULL
1213 )
1214 RETURN VARCHAR2
1215 IS
1216 
1217 C_KEY_FLEXFIELD          CONSTANT     VARCHAR2(1000) :=
1218  'TO_NUMBER($source$)'
1219 ;
1220 
1221 C_KEY_FLEXFIELD_SEGMENT   CONSTANT     VARCHAR2(1000) :=
1222  'TO_CHAR($source$)'
1223 ;
1224 
1225 l_source                VARCHAR2(32000);
1226 l_rec_sources           t_rec_sources;
1227 l_error_message         VARCHAR2(4000);
1231 IF g_log_enabled THEN
1228 invalid_source          EXCEPTION;
1229 l_log_module            VARCHAR2(240);
1230 BEGIN
1232       l_log_module := C_DEFAULT_MODULE||'.GetKeyFlexfieldSource';
1233 END IF;
1234 
1235 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1236 
1237       trace
1238          (p_msg      => 'BEGIN of GetKeyFlexfieldSource'
1239          ,p_level    => C_LEVEL_PROCEDURE
1240          ,p_module   => l_log_module);
1241 
1242 END IF;
1243 l_rec_sources := p_rec_sources;
1244 l_source      := NULL;
1245 
1246 IF NOT l_rec_sources.array_source_code.EXISTS(p_Index) OR
1247    l_rec_sources.array_source_code(p_Index) IS NULL  THEN
1248      l_error_message := 'Source Index :'||p_Index||' does not exist in the source cache';
1249      raise invalid_source;
1250 END IF;
1251 
1252 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1253       trace
1254          (p_msg      => ' source code = '||l_rec_sources.array_source_code(p_Index)||
1255                         ' - source type code = '||l_rec_sources.array_source_type_code(p_Index)||
1256                         ' - source application id = '||l_rec_sources.array_application_id(p_Index) ||
1257                         ' - key_flexfield_flag = '||l_rec_sources.array_key_flexfield_flag(p_Index) ||
1258                         ' - flexfield_appl_id = '||l_rec_sources.array_flexfield_appl_id(p_Index)||
1259                         ' - id_flex_code = '||l_rec_sources.array_id_flex_code(p_Index)
1260          ,p_level    => C_LEVEL_STATEMENT
1261          ,p_module   => l_log_module);
1262 END IF;
1263 
1264 CASE l_rec_sources.array_datatype_code(p_Index)
1265     WHEN  C_NUMBER_DATATYPE THEN l_source   := C_KEY_FLEXFIELD ;
1266     WHEN 'I'                THEN l_source   := C_KEY_FLEXFIELD ;
1267     WHEN 'F'                THEN l_source   := C_KEY_FLEXFIELD ;
1268     WHEN C_CHAR_DATATYPE    THEN l_source   := C_KEY_FLEXFIELD_SEGMENT ;
1269 ELSE
1270       l_error_message := 'Invalid key flexfield source data type :'
1271                          ||l_rec_sources.array_datatype_code(p_Index);
1272 
1273       RAISE invalid_source;
1274 END CASE;
1275 
1276 CASE l_rec_sources.array_source_type_code(p_Index)
1277 
1278        WHEN C_SEEDED_SOURCE_TYPE THEN
1279             l_source    := REPLACE(l_source,'$source$',
1280                               GetStandardSource(
1281                                   p_Index
1282                                 , l_rec_sources.array_source_code
1283                                 , l_rec_sources.array_datatype_code
1284                                 , p_variable
1285                                 ));
1286        WHEN C_CUSTOM_SOURCE_TYPE THEN
1287             l_source    := REPLACE(l_source,'$source$',
1288                               GetCustomSource(
1289                                    p_Index
1290                                  , l_rec_sources
1291                                  , p_variable
1292 	                          ));
1293 
1294       WHEN C_SYSTEM_SOURCE_TYPE THEN
1295             l_source :=REPLACE(l_source,'$source$',
1296                           GetSystemSource(
1297                              p_Index
1298                            , l_rec_sources
1299                           ));
1300       ELSE
1301 
1302            l_error_message:= SUBSTR('Invalid source type code '
1303                             ||l_rec_sources.array_source_type_code(p_Index)
1304                             ||' defined for source '
1305                             ||l_rec_sources.array_source_code(p_Index),1,1000);
1306 
1307            RAISE invalid_source;
1308 END CASE;
1309 
1310 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1311         trace
1312            (p_msg      => 'END of GetKeyFlexfieldSource'
1313            ,p_level    => C_LEVEL_PROCEDURE
1314            ,p_module   => l_log_module);
1315 END IF;
1316 p_rec_sources := l_rec_sources;
1317 RETURN l_source;
1318 EXCEPTION
1319    WHEN invalid_source THEN
1320       p_rec_sources := l_rec_sources;
1321       IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1322          trace
1323             (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR ='||l_error_message
1324             ,p_level    => C_LEVEL_EXCEPTION
1325             ,p_module   => l_log_module);
1326       END IF;
1327       RETURN 'NULL';
1328    WHEN xla_exceptions_pkg.application_exception   THEN
1329       p_rec_sources := l_rec_sources;
1330       IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1331             trace
1332                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||sqlerrm
1333                ,p_level    => C_LEVEL_EXCEPTION
1334                ,p_module   => l_log_module);
1335        END IF;
1336        RAISE;
1337    WHEN OTHERS    THEN
1338       xla_exceptions_pkg.raise_message
1339          (p_location => 'xla_cmp_source_pkg.GetKeyFlexfieldSource');
1340 END GetKeyFlexfieldSource ;
1341 
1342 
1343 /*----------------------------------------------------------+
1344 | Public function                                           |
1345 |                                                           |
1346 |   GenerateSource                                          |
1347 |                                                           |
1348 |  It drives the generation of the sources in the AAD       |
1349 |  packages.                                                |
1350 |                                                           |
1351 +----------------------------------------------------------*/
1352 
1353 FUNCTION GenerateSource(
1354        p_Index                        IN  BINARY_INTEGER
1355      , p_rec_sources                  IN OUT NOCOPY t_rec_sources
1356      , p_variable                     IN VARCHAR2 DEFAULT NULL
1357      , p_translated_flag              IN VARCHAR2 DEFAULT NULL
1358 )
1359 RETURN VARCHAR2
1360 IS
1361 l_source                VARCHAR2(32000);
1365 l_log_module            VARCHAR2(240);
1362 l_rec_sources           t_rec_sources;
1363 l_error_message         VARCHAR2(4000);
1364 invalid_source          EXCEPTION;
1366 BEGIN
1367 IF g_log_enabled THEN
1368       l_log_module := C_DEFAULT_MODULE||'.GenerateSource';
1369 END IF;
1370 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1371 
1372       trace
1373          (p_msg      => 'BEGIN of GenerateSource'
1374          ,p_level    => C_LEVEL_PROCEDURE
1375          ,p_module   => l_log_module);
1376 
1377 END IF;
1378 
1379 l_rec_sources := p_rec_sources;
1380 l_source      := NULL;
1381 
1382 IF NOT l_rec_sources.array_source_code.EXISTS(p_Index)   OR
1383    l_rec_sources.array_source_code(p_Index)      IS NULL OR
1384    l_rec_sources.array_source_type_code(p_Index) IS NULL OR
1385    l_rec_sources.array_application_id(p_Index)  IS NULL
1386    THEN
1387      l_error_message := 'Source Index :'||p_Index||' does not exist in the source cache';
1388      RAISE invalid_source;
1389 END IF;
1390 
1391 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1392       trace
1393          (p_msg      => ' source code = '||l_rec_sources.array_source_code(p_Index)||
1394                         ' - source type code = '||l_rec_sources.array_source_type_code(p_Index)||
1395                         ' - source application id = '||l_rec_sources.array_application_id(p_Index)
1396          ,p_level    => C_LEVEL_STATEMENT
1397          ,p_module   => l_log_module);
1398 END IF;
1399 
1400 
1401 IF    l_rec_sources.array_flex_value_set_id(p_Index)   IS NOT NULL THEN
1402 
1403               l_source :=GetValueSetSource(
1404                               p_Index
1405                             , l_rec_sources
1406                             , p_variable
1407                             , p_translated_flag
1408                            ) ;
1409 
1410 
1411 ELSIF l_rec_sources.array_lookup_type(p_Index)         IS NOT NULL AND
1412       l_rec_sources.array_view_application_id(p_Index) IS NOT NULL THEN
1413 
1414              l_source := GetLookupSource(
1415                              p_Index
1416                            , l_rec_sources
1417                            , p_variable
1418                            , p_translated_flag
1419                           );
1420 
1421 
1422 ELSIF nvl(l_rec_sources.array_key_flexfield_flag(p_Index),'N') ='Y' AND
1423       l_rec_sources.array_flexfield_appl_id(p_Index)    IS NOT NULL AND
1424       l_rec_sources.array_id_flex_code(p_Index)         IS NOT NULL THEN
1425 
1426 
1427              l_source := GetKeyFlexfieldSource(
1428                              p_Index
1429                            , l_rec_sources
1430                            , p_variable
1431                           );
1432 
1433 
1434 ELSIF l_rec_sources.array_source_type_code(p_Index)  = C_SEEDED_SOURCE_TYPE   THEN
1435 
1436              l_source :=GetStandardSource(
1437                              p_Index
1438                            , l_rec_sources.array_source_code
1439                            , l_rec_sources.array_datatype_code
1440                            , p_variable
1441                          );
1442 
1443 
1444 ELSIF l_rec_sources.array_source_type_code(p_Index)  = C_SYSTEM_SOURCE_TYPE   THEN
1445 
1446                l_source :=GetSystemSource(
1447                              p_Index
1448                            , l_rec_sources
1449                           );
1450 
1451 
1452 -- custom source
1453 ELSIF l_rec_sources.array_source_type_code(p_Index)    = C_CUSTOM_SOURCE_TYPE THEN
1454       l_source := GetCustomSource(
1455                                p_Index
1456                              , l_rec_sources
1457                              , p_variable
1458                              );
1459 
1460 ELSE
1461    l_error_message:= SUBSTR('Invalid Definition for source code: '
1462                             ||l_rec_sources.array_source_code(p_Index)
1463                             ||' and source type: '
1464                             ||l_rec_sources.array_source_type_code(p_Index),1,1000);
1465    RAISE invalid_source;
1466 END IF;
1467 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1468 
1469         trace
1470            (p_msg      => 'END of GenerateSource'
1471            ,p_level    => C_LEVEL_PROCEDURE
1472            ,p_module   => l_log_module);
1473 
1474 END IF;
1475 p_rec_sources := l_rec_sources;
1476 RETURN l_source;
1477 EXCEPTION
1478    WHEN invalid_source THEN
1479       p_rec_sources := l_rec_sources;
1480       IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1481          trace
1482             (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR ='||l_error_message
1483             ,p_level    => C_LEVEL_EXCEPTION
1484             ,p_module   => l_log_module);
1485       END IF;
1486       RETURN 'NULL';
1487    WHEN xla_exceptions_pkg.application_exception   THEN
1488       p_rec_sources := l_rec_sources;
1489       IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1490             trace
1491                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||sqlerrm
1492                ,p_level    => C_LEVEL_EXCEPTION
1493                ,p_module   => l_log_module);
1494        END IF;
1495        RAISE;
1496    WHEN OTHERS    THEN
1497       xla_exceptions_pkg.raise_message
1498          (p_location => 'xla_cmp_source_pkg.GenerateSource');
1499 END GenerateSource;
1500 
1501 
1502 /*----------------------------------------------------------+
1503 | Public function                                           |
1504 |                                                           |
1505 |   GenerateParameters                                      |
1506 |                                                           |
1507 |  It generates the AAD procedures/functions parameters:    |
1511 
1508 |  p_sourc_1 IN VARCHAR2, p_source_2 IN NUMBER, .....       |
1509 |                                                           |
1510 +----------------------------------------------------------*/
1512 FUNCTION GenerateParameters   (
1513     p_array_source_index  IN t_array_ByInt
1514   , p_rec_sources         IN t_rec_sources
1515  )
1516 RETURN VARCHAR2
1517 IS
1518 C_PARAMETER      CONSTANT VARCHAR2(1000):= ' , p_source_$Index$            IN $datatype$';
1519 C_MNG_PARAMETER  CONSTANT VARCHAR2(1000):= ' , p_source_$Index$_meaning    IN VARCHAR2';
1520 l_parameters              VARCHAR2(32000);
1521 l_one_parameter           VARCHAR2(32000);
1522 l_error_message           VARCHAR2(4000);
1523 l_log_module              VARCHAR2(240);
1524 invalid_source            EXCEPTION;
1525 BEGIN
1526 IF g_log_enabled THEN
1527       l_log_module := C_DEFAULT_MODULE||'.GenerateParameters';
1528 END IF;
1529 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1530       trace
1531          (p_msg      => 'BEGIN of GenerateParameters = '||p_array_source_index.COUNT
1532          ,p_level    => C_LEVEL_PROCEDURE
1533          ,p_module   => l_log_module);
1534 END IF;
1535 
1536 l_parameters    := NULL;
1537 
1538 IF p_array_source_index.COUNT > 0   THEN
1539 
1540   FOR Idx IN p_array_source_index.FIRST .. p_array_source_index.LAST LOOP
1541 
1542     IF p_array_source_index.EXISTS(Idx) THEN
1543 
1544        IF NOT p_rec_sources.array_source_code.EXISTS(Idx) THEN
1545 
1546          l_error_message:= SUBSTR('Source does not exist in the cache : '||Idx, 1,1000);
1547          RAISE invalid_source;
1548 
1549        ELSIF  p_rec_sources.array_source_code(Idx) IS NULL OR
1550               p_rec_sources.array_source_type_code(Idx) <> 'S' OR
1551               p_rec_sources.array_datatype_code(Idx) IS NULL THEN
1552 
1553           l_error_message:= SUBSTR('Invalid source: '||Idx
1554                             ||' - '||p_rec_sources.array_source_code(Idx)
1555                             ||' - '||p_rec_sources.array_source_type_code(Idx)
1556                             ||' - '||p_rec_sources.array_datatype_code(Idx),1,1000);
1557 
1558           RAISE invalid_source;
1559 
1560        END IF;
1561 
1562        l_one_parameter := null;
1563        -- display source_name as a comment
1564        IF p_rec_sources.array_source_name.EXISTS(Idx) AND
1565           p_rec_sources.array_source_name(Idx) IS NOT NULL
1566        THEN
1567           l_one_parameter := l_one_parameter ||'--'||p_rec_sources.array_source_name(Idx) ;
1568        END IF;
1569 
1570        l_one_parameter  := l_one_parameter ||g_chr_newline || C_PARAMETER ;
1571 
1572        --If compiling for an AAD generate also the meaning parameter
1573        IF g_component_type = 'TAB_ADR'
1574        THEN
1575           NULL;
1576        ELSE --lookup source
1577           IF p_rec_sources.array_lookup_type.EXISTS(Idx)              AND
1578              p_rec_sources.array_lookup_type(Idx)         IS NOT NULL AND
1579              p_rec_sources.array_view_application_id.EXISTS(Idx)      AND
1580              p_rec_sources.array_view_application_id(Idx) IS NOT NULL
1581           THEN
1582             l_one_parameter  := l_one_parameter|| g_chr_newline || C_MNG_PARAMETER;
1583 
1584           END IF;
1585        END IF;
1586 
1587        l_one_parameter  := REPLACE(l_one_parameter,'$Index$',Idx);
1588 
1589        l_one_parameter  := REPLACE(l_one_parameter,'$datatype$',
1590                                   GetDataType(p_rec_sources.array_datatype_code(Idx)));
1591        l_parameters := l_parameters || g_chr_newline || l_one_parameter;
1592     --
1593     END IF;
1594 END LOOP;
1595 END IF;
1596 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1597       trace
1598          (p_msg      => 'END of GenerateParameters'
1599          ,p_level    => C_LEVEL_PROCEDURE
1600          ,p_module   => l_log_module);
1601 END IF;
1602 RETURN l_parameters;
1603 EXCEPTION
1604     WHEN invalid_source  THEN
1605         IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1606            trace
1607             (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR= '||l_error_message
1608             ,p_level    => C_LEVEL_EXCEPTION
1609             ,p_module   => l_log_module);
1610          END IF;
1611         RETURN NULL;
1612     WHEN VALUE_ERROR THEN
1613       IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1614             trace
1615                 (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR= '||sqlerrm
1616                 ,p_level    => C_LEVEL_EXCEPTION
1617                 ,p_module   => l_log_module);
1618       END IF;
1619       RAISE;
1620  WHEN xla_exceptions_pkg.application_exception   THEN
1621      IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1622             trace
1623                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||sqlerrm
1624                ,p_level    => C_LEVEL_EXCEPTION
1625                ,p_module   => l_log_module);
1626      END IF;
1627      RAISE;
1628    WHEN OTHERS    THEN
1629       xla_exceptions_pkg.raise_message
1630          (p_location => 'xla_cmp_source_pkg.GenerateParameters ');
1631 END GenerateParameters;
1632 
1633 /*----------------------------------------------------------+
1634 | Public function                                           |
1635 |                                                           |
1636 |   get_obj_parm_for_tab                                    |
1637 |                                                           |
1638 |  It generates the AAD procedures/functions parameters:    |
1639 |  p_sourc_1 IN VARCHAR2, p_source_2 IN NUMBER, .....       |
1640 |                                                           |
1641 |  Note that this function is used only for TAB             |
1642 +----------------------------------------------------------*/
1643 
1644 FUNCTION get_obj_parm_for_tab   (
1648 RETURN VARCHAR2
1645     p_array_source_index           IN t_array_ByInt
1646   , p_rec_sources                  IN t_rec_sources
1647  )
1649 IS
1650 l_return_value           VARCHAR2(32767);
1651 l_log_module             VARCHAR2(240);
1652 
1653 BEGIN
1654    IF g_log_enabled THEN
1655       l_log_module := C_DEFAULT_MODULE||'.get_obj_parm_for_tab';
1656    END IF;
1657 
1658    IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1659 
1660       trace
1661          (p_msg      => 'BEGIN ' || l_log_module
1662          ,p_level    => C_LEVEL_PROCEDURE
1663          ,p_module   => l_log_module);
1664 
1665    END IF;
1666 
1667    --Set the global variable to component type TAD_ADR
1668    g_component_type := 'TAB_ADR';
1669 
1670    l_return_value := GenerateParameters
1671                       (
1672                        p_array_source_index  => p_array_source_index
1673                       ,p_rec_sources         => p_rec_sources
1674                      );
1675 
1676 
1677    IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1678 
1679       trace
1680          (p_msg      => 'END ' || l_log_module
1681          ,p_level    => C_LEVEL_PROCEDURE
1682          ,p_module   => l_log_module);
1683 
1684    END IF;
1685 
1686    RETURN l_return_value;
1687 EXCEPTION
1688  WHEN xla_exceptions_pkg.application_exception   THEN
1689        IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1690             trace
1691                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||sqlerrm
1692                ,p_level    => C_LEVEL_EXCEPTION
1693                ,p_module   => l_log_module);
1694         END IF;
1695         RETURN NULL;
1696    WHEN OTHERS    THEN
1697       xla_exceptions_pkg.raise_message
1698          (p_location => 'xla_cmp_source_pkg.get_obj_parm_for_tab ');
1699 END get_obj_parm_for_tab;
1700 
1701 --
1702 --===========================================================================
1703 --
1704 --
1705 --
1706 --
1707 --
1708 --       Procedures and functions to cache source definitions and
1709 --       to index each sources defined in a AAD.
1710 --
1711 --
1712 --
1713 --
1714 --
1715 --===========================================================================
1716 
1717 /*--------------------------------------------------------------+
1718 | Private function                                              |
1719 |                                                               |
1720 |   IsSourceInCache                                             |
1721 |                                                               |
1722 |   Boolean function returns TRUE, if the source exists in the  |
1723 |   source cache, otherwise FALSE. It also returns the position |
1724 |   where the source is cached                                  |
1725 +--------------------------------------------------------------*/
1726 
1727 FUNCTION IsSourceInCache(
1728    p_source_code              IN VARCHAR2
1732  , p_position                OUT NOCOPY BINARY_INTEGER
1729  , p_source_type_code         IN VARCHAR2
1730  , p_source_application_id    IN NUMBER
1731  , p_rec_sources              IN t_rec_sources
1733 )
1734 RETURN BOOLEAN
1735 IS
1736 l_position           BINARY_INTEGER;
1737 l_exist              BOOLEAN;
1738 l_log_module         VARCHAR2(240);
1739 BEGIN
1740 IF g_log_enabled THEN
1744 l_position    := 1;
1741       l_log_module := C_DEFAULT_MODULE||'.IsSourceInCache';
1742 END IF;
1743 
1745 l_exist       := FALSE;
1746 
1747 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1748 
1749         trace
1750          (p_msg      => ' p_source_code = '||p_source_code||
1751                         ',p_source_type_code = '||p_source_type_code||
1752                         ',p_source_application_id = '||p_source_application_id||
1753                         ',number of sources in cache = '||p_rec_sources.array_source_code.COUNT
1754          ,p_level    => C_LEVEL_STATEMENT
1755          ,p_module   => l_log_module);
1756 END IF;
1757 
1758 WHILE NOT l_exist AND l_position <= p_rec_sources.array_source_code.COUNT        LOOP
1759 
1760    IF p_rec_sources.array_source_code.EXISTS(l_position)                          AND
1761       p_rec_sources.array_source_code(l_position)       = p_source_code           AND
1762       p_rec_sources.array_source_type_code(l_position)  = p_source_type_code      AND
1763       p_rec_sources.array_application_id(l_position)    = p_source_application_id
1764    THEN
1765      l_exist   := TRUE;
1766    ELSE
1767      l_position := l_position + 1;
1768    END IF;
1769 
1770 END LOOP;
1771 
1772 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1773 
1774         trace
1775          (p_msg      => ' l_exist = '||CASE l_exist
1776                                         WHEN TRUE THEN 'TRUE'
1777                                         ELSE 'FALSE'
1778                                        END ||
1779                         ' , position = '||l_position
1780          ,p_level    => C_LEVEL_STATEMENT
1781          ,p_module   => l_log_module);
1782 END IF;
1783 p_position := l_position;
1784 RETURN l_exist;
1785 EXCEPTION
1786 WHEN OTHERS THEN
1787    IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1788             trace
1789                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||sqlerrm
1790                ,p_level    => C_LEVEL_EXCEPTION
1791                ,p_module   => l_log_module);
1792    END IF;
1793    RETURN FALSE;
1794 END IsSourceInCache;
1795 
1796 /*--------------------------------------------------------------+
1797 | Private function                                              |
1798 |                                                               |
1799 |   CacheCustomSource                                           |
1800 |                                                               |
1801 |   Caches the sources assigned to Customer source              |
1802 +--------------------------------------------------------------*/
1803 
1804 PROCEDURE CacheCustomSource (
1805    p_source_code                  IN VARCHAR2
1806  , p_source_type_code             IN VARCHAR2
1807  , p_source_application_id        IN NUMBER
1808  , p_rec_sources                  IN OUT NOCOPY t_rec_sources
1809 )
1810 IS
1811 CURSOR source_cur ( p_source_code            VARCHAR2
1812                   , p_source_type_code       VARCHAR2
1813                   , p_source_application_id  NUMBER) IS
1814 SELECT DISTINCT
1815        xsp.ref_source_application_id
1816      , xsp.ref_source_type_code
1817      , xsp.ref_source_code
1818 FROM   xla_source_params       xsp
1819 WHERE  xsp.source_code         = p_source_code
1820    AND xsp.source_type_code    = p_source_type_code
1821    AND xsp.application_id      = p_source_application_id
1822    AND xsp.parameter_type_code = 'S'             --not constant
1823    AND xsp.ref_source_application_id   IS NOT NULL
1824    AND xsp.ref_source_type_code        IS NOT NULL
1825    AND xsp.ref_source_code             IS NOT NULL
1826 ;
1827 --
1828 source_rec           source_cur%ROWTYPE;
1829 l_position           BINARY_INTEGER;
1830 l_log_module         VARCHAR2(240);
1831 l_rec_sources        t_rec_sources;
1832 BEGIN
1833 --
1834 IF g_log_enabled THEN
1835       l_log_module := C_DEFAULT_MODULE||'.CacheCustomSource';
1836 END IF;
1837 --
1838 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1839 
1840       trace
1841          (p_msg      => 'BEGIN of CacheCustomSource'
1842          ,p_level    => C_LEVEL_PROCEDURE
1843          ,p_module   => l_log_module);
1844 
1845 END IF;
1846 
1847 l_rec_sources  := p_rec_sources;
1848 
1849 FOR source_rec IN  source_cur(
1850          p_source_code           => p_source_code
1851        , p_source_type_code      => p_source_type_code
1855    --
1852        , p_source_application_id => p_source_application_id
1853                              )
1854 LOOP
1856    l_position :=  CacheSource (
1857                 p_source_code           => source_rec.ref_source_code
1858               , p_source_type_code      => source_rec.ref_source_type_code
1859               , p_source_application_id => source_rec.ref_source_application_id
1860               , p_rec_sources           => l_rec_sources
1861              );
1862 
1863 END LOOP;
1864 
1865 p_rec_sources  := l_rec_sources;
1866 
1867 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1868       trace
1869          (p_msg      => 'END of CacheCustomSource'
1870          ,p_level    => C_LEVEL_PROCEDURE
1871          ,p_module   => l_log_module);
1872 END IF;
1873 EXCEPTION
1874    WHEN xla_exceptions_pkg.application_exception   THEN
1875         IF source_cur%ISOPEN THEN CLOSE source_cur; END IF;
1876         p_rec_sources  := l_rec_sources;
1877         IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1878             trace
1879                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||sqlerrm
1880                ,p_level    => C_LEVEL_EXCEPTION
1881                ,p_module   => l_log_module);
1882         END IF;
1883         RAISE;
1884    WHEN OTHERS    THEN
1885       IF source_cur%ISOPEN THEN CLOSE source_cur; END IF;
1886       p_rec_sources  := l_rec_sources;
1887       xla_exceptions_pkg.raise_message
1888          (p_location => 'xla_cmp_source_pkg.CacheCustomSource');
1889 END CacheCustomSource;
1890 
1891 /*--------------------------------------------------------------+
1892 | Public function                                               |
1893 |                                                               |
1894 |   CacheCustomSource                                           |
1895 |                                                               |
1896 |   Caches the source definitions in the p_rec_sources cache    |
1897 +--------------------------------------------------------------*/
1898 
1899 FUNCTION CacheSource (
1900     p_source_code                  IN VARCHAR2
1901   , p_source_type_code             IN VARCHAR2
1902   , p_source_application_id        IN NUMBER
1903   , p_rec_sources                  IN OUT NOCOPY t_rec_sources
1904   )
1905 RETURN BINARY_INTEGER
1906 IS
1907 l_exists             BOOLEAN;
1908 l_position           BINARY_INTEGER;
1909 l_log_module         VARCHAR2(240);
1910 BEGIN
1911 IF g_log_enabled THEN
1912       l_log_module := C_DEFAULT_MODULE||'.CacheSource';
1913 END IF;
1914 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1915 
1916       trace
1917          (p_msg      => 'BEGIN of CacheSource'
1918          ,p_level    => C_LEVEL_PROCEDURE
1919          ,p_module   => l_log_module);
1920 
1921 END IF;
1922 
1923 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1924         trace
1925          (p_msg      => ' p_source_code = '||p_source_code||
1926                         ' - p_source_type_code = '||p_source_type_code||
1927                         ' - p_source_application_id = '||p_source_application_id
1928          ,p_level    => C_LEVEL_STATEMENT
1929          ,p_module   => l_log_module);
1930 END IF;
1931 
1932 l_exists := IsSourceInCache(
1933                 p_source_code
1934               , p_source_type_code
1935               , p_source_application_id
1936               , p_rec_sources
1937               , l_position) ;
1938 
1939 IF NOT l_exists THEN
1940 
1941    -- cache source definition
1942          SELECT  DISTINCT
1943                  xsb.source_code
1944                , xsb.source_type_code
1945                , xsb.application_id
1946                , xst.name
1947                , xsb.datatype_code
1948                , xsb.plsql_function_name
1949                , xsb.flex_value_set_id
1950                , xsb.translated_flag
1951                , xsb.lookup_type
1952                , xsb.view_application_id
1953                , xsb.key_flexfield_flag
1954                , xsb.flexfield_application_id
1955                , DECODE (xsb.flexfield_application_id
1956                           , NULL,  NULL
1957                           , fnd.application_short_name )
1958                , xsb.id_flex_code
1959                , xsb.segment_code
1960 
1961          INTO  p_rec_sources.array_source_code(l_position)
1962              , p_rec_sources.array_source_type_code(l_position)
1963              , p_rec_sources.array_application_id(l_position)
1964              , p_rec_sources.array_source_name(l_position)
1965              , p_rec_sources.array_datatype_code(l_position)
1966              , p_rec_sources.array_plsql_function(l_position)
1967              , p_rec_sources.array_flex_value_set_id(l_position)
1968              , p_rec_sources.array_translated_flag(l_position)
1969              , p_rec_sources.array_lookup_type(l_position)
1970              , p_rec_sources.array_view_application_id(l_position)
1971              , p_rec_sources.array_key_flexfield_flag(l_position)
1972              , p_rec_sources.array_flexfield_appl_id(l_position)
1973              , p_rec_sources.array_appl_short_name(l_position)
1974              , p_rec_sources.array_id_flex_code(l_position)
1975              , p_rec_sources.array_segment_code (l_position)
1976          FROM   xla_sources_b   xsb
1977               , xla_sources_tl  xst
1978               , fnd_application fnd
1979          WHERE  xsb.source_code         = p_source_code
1980             AND xsb.source_type_code    = p_source_type_code
1981             AND xsb.application_id      = p_source_application_id
1982             AND xsb.source_code         = xst.source_code
1983             AND xsb.source_type_code    = xst.source_type_code
1984             AND xsb.application_id      = xst.application_id (+)
1985             AND xst.language         (+)= USERENV('LANG')
1989 
1986             AND xsb.enabled_flag        = 'Y'
1987             AND nvl(xsb.flexfield_application_id,p_source_application_id) = fnd.application_id (+)
1988          ;
1990       IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1991         trace
1992          (p_msg      => 'Cached in '||l_position||
1993                         ' : p_source_code = '||p_rec_sources.array_source_code(l_position)||
1994                         ',p_source_type_code = '||p_rec_sources.array_source_type_code(l_position)||
1995                         ',p_source_application_id = '||p_rec_sources.array_application_id(l_position) ||
1996                         ', name ='||p_rec_sources.array_source_name(l_position)||
1997                         ',datatype_code= '||p_rec_sources.array_datatype_code(l_position)
1998          ,p_level    => C_LEVEL_STATEMENT
1999          ,p_module   => l_log_module);
2000 
2001        trace
2002          (p_msg      => ' lookup_type= '||p_rec_sources.array_lookup_type(l_position)||
2003                         ',view_application_id= '||p_rec_sources.array_view_application_id(l_position)||
2004                         ',flex_value_set_id= '||p_rec_sources.array_flex_value_set_id(l_position)||
2005                         ',key_flexfield_flag= '||p_rec_sources.array_key_flexfield_flag(l_position)||
2006                         ',flexfield_application_id='|| p_rec_sources.array_flexfield_appl_id(l_position)||
2007                         ',appl_short_name ='||p_rec_sources.array_appl_short_name(l_position) ||
2008                         ',id_flex_code='||p_rec_sources.array_id_flex_code(l_position) ||
2009                         ',segment_code='||p_rec_sources.array_segment_code (l_position)
2010          ,p_level    => C_LEVEL_STATEMENT
2011          ,p_module   => l_log_module);
2012       END IF;
2013 
2014     -- cache source defined in custom source
2015    IF p_source_type_code = C_CUSTOM_SOURCE_TYPE THEN
2016 
2017        CacheCustomSource (
2018                              p_source_code                  => p_source_code
2019                            , p_source_type_code             => p_source_type_code
2020                            , p_source_application_id        => p_source_application_id
2021                            , p_rec_sources                  => p_rec_sources
2022                             );
2023    END IF;
2024 
2025 ELSE
2026   null;
2027   IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2028         trace
2029          (p_msg      => 'Exist in ='||l_position
2030          ,p_level    => C_LEVEL_STATEMENT
2031          ,p_module   => l_log_module);
2032   END IF;
2033 END IF;
2034 
2035 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2036       trace
2037          (p_msg      => 'END of CacheSource '
2038          ,p_level    => C_LEVEL_PROCEDURE
2039          ,p_module   => l_log_module);
2040 
2041 END IF;
2042 RETURN l_position;
2043 EXCEPTION
2044    WHEN NO_DATA_FOUND THEN
2045      IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2046             trace
2047                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||sqlerrm
2048                ,p_level    => C_LEVEL_EXCEPTION
2049                ,p_module   => l_log_module);
2050         END IF;
2051         RAISE;
2052    WHEN TOO_MANY_ROWS THEN
2053     IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2054             trace
2055                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||sqlerrm
2059         RAISE;
2056                ,p_level    => C_LEVEL_EXCEPTION
2057                ,p_module   => l_log_module);
2058         END IF;
2060    WHEN xla_exceptions_pkg.application_exception   THEN
2061 
2062         IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2063             trace
2064                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||sqlerrm
2065                ,p_level    => C_LEVEL_EXCEPTION
2066                ,p_module   => l_log_module);
2067         END IF;
2068         RAISE;
2069    WHEN OTHERS    THEN
2070 
2071       xla_exceptions_pkg.raise_message
2072          (p_location => 'xla_cmp_source_pkg.CacheSource');
2073 END CacheSource;
2074 
2075 /*--------------------------------------------------------------+
2076 | Private function                                              |
2077 |                                                               |
2078 |   StackCustomSource                                           |
2079 |                                                               |
2080 |   Caches the custom source in the p_array_source_index cache  |
2081 +--------------------------------------------------------------*/
2082 PROCEDURE StackCustomSource (
2083    p_source_code                  IN VARCHAR2
2084  , p_source_type_code             IN VARCHAR2
2085  , p_source_application_id        IN NUMBER
2086  , p_array_source_index           IN OUT NOCOPY t_array_ByInt
2087  , p_rec_sources                  IN OUT NOCOPY t_rec_sources
2088 )
2089 IS
2090 --
2091 CURSOR source_cur ( p_source_code            VARCHAR2
2092                   , p_source_type_code       VARCHAR2
2093                   , p_source_application_id  NUMBER)
2094 IS
2095 SELECT DISTINCT
2096        xsp.ref_source_application_id
2097      , xsp.ref_source_type_code
2098      , xsp.ref_source_code
2099 FROM   xla_source_params     xsp
2100 WHERE  xsp.source_code         = p_source_code
2101    AND xsp.source_type_code    = p_source_type_code
2102    AND xsp.application_id      = p_source_application_id
2103    AND xsp.parameter_type_code = 'S'
2104    AND xsp.ref_source_application_id IS NOT NULL
2105    AND xsp.ref_source_type_code IS NOT NULL
2106    AND xsp.ref_source_code IS NOT NULL
2107 ;
2108 --
2109 source_rec           source_cur%ROWTYPE;
2110 l_position           BINARY_INTEGER;
2111 l_rec_sources        t_rec_sources;
2112 l_log_module         VARCHAR2(240);
2113 BEGIN
2114 IF g_log_enabled THEN
2115       l_log_module := C_DEFAULT_MODULE||'.StackCustomSource';
2116 END IF;
2117 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2118       trace
2119          (p_msg      => 'BEGIN of StackCustomSource'
2120          ,p_level    => C_LEVEL_PROCEDURE
2121          ,p_module   => l_log_module);
2122 
2123 END IF;
2124 
2125 l_rec_sources := p_rec_sources;
2126 
2127 FOR source_rec IN  source_cur(
2128          p_source_code           => p_source_code
2129        , p_source_type_code      => p_source_type_code
2130        , p_source_application_id => p_source_application_id )
2131 LOOP
2132 
2133    l_position:=  StackSource(
2134        p_source_code            => source_rec.ref_source_code
2135      , p_source_type_code       => source_rec.ref_source_type_code
2136      , p_source_application_id  => source_rec.ref_source_application_id
2137      , p_array_source_index     => p_array_source_index
2138      , p_rec_sources            => l_rec_sources
2139      );
2140 
2141 END LOOP;
2142 
2143 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2144       trace
2145          (p_msg      => 'END of StackCustomSource'
2146          ,p_level    => C_LEVEL_PROCEDURE
2147          ,p_module   => l_log_module);
2148 
2149 END IF;
2150 p_rec_sources := l_rec_sources;
2151 EXCEPTION
2152    WHEN xla_exceptions_pkg.application_exception   THEN
2153         IF source_cur%ISOPEN THEN CLOSE source_cur; END IF;
2154         p_rec_sources := l_rec_sources;
2155         IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2156             trace
2157                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||sqlerrm
2158                ,p_level    => C_LEVEL_EXCEPTION
2159                ,p_module   => l_log_module);
2160         END IF;
2161         RAISE;
2162    WHEN OTHERS    THEN
2163       IF source_cur%ISOPEN THEN CLOSE source_cur; END IF;
2164       p_rec_sources := l_rec_sources;
2165       xla_exceptions_pkg.raise_message
2166          (p_location => 'xla_cmp_source_pkg.StackCustomSource');
2167 END StackCustomSource;
2168 
2169 /*--------------------------------------------------------------+
2170 | Public function                                               |
2171 |                                                               |
2172 |   StackSource                                                 |
2173 |                                                               |
2174 |    Caches the source in the p_array_source_index cache        |
2175 +--------------------------------------------------------------*/
2176 
2177 FUNCTION StackSource(
2178     p_source_code                  IN VARCHAR2
2179   , p_source_type_code             IN VARCHAR2
2180   , p_source_application_id        IN NUMBER
2181   , p_array_source_index           IN OUT NOCOPY t_array_ByInt
2182   , p_rec_sources                  IN OUT NOCOPY t_rec_sources
2183   )
2184 RETURN BINARY_INTEGER
2185 IS
2186 l_position             BINARY_INTEGER;
2187 l_rec_sources          t_rec_sources;
2191 IF g_log_enabled THEN
2188 l_array_source_index   t_array_ByInt;
2189 l_log_module           VARCHAR2(240);
2190 BEGIN
2192       l_log_module := C_DEFAULT_MODULE||'.StackSource';
2193 END IF;
2194 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2195       trace
2196          (p_msg      => 'BEGIN of StackSource'
2197          ,p_level    => C_LEVEL_PROCEDURE
2198          ,p_module   => l_log_module);
2199 END IF;
2200 --
2201 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2202       trace
2203          (p_msg      => 'Stack up : p_source_code = '||p_source_code||
2204                         ' - p_source_type_code = '||p_source_type_code||
2205                         ' - p_source_application_id = '||p_source_application_id
2206          ,p_level    => C_LEVEL_STATEMENT
2207          ,p_module   => l_log_module);
2208 END IF;
2209 
2210 l_rec_sources        := p_rec_sources;
2211 l_array_source_index := p_array_source_index;
2212 
2213 l_position :=  CacheSource (
2214                p_source_code           => p_source_code
2215              , p_source_type_code      => p_source_type_code
2216              , p_source_application_id => p_source_application_id
2217              , p_rec_sources           => l_rec_sources
2218             );
2219 
2220 CASE  p_source_type_code
2221 
2225 
2222      WHEN C_SEEDED_SOURCE_TYPE  THEN  l_array_source_index(l_position):= l_position;
2223      WHEN C_SYSTEM_SOURCE_TYPE  THEN  null;
2224      WHEN C_CUSTOM_SOURCE_TYPE  THEN
2226        StackCustomSource (
2227                       p_source_code           => p_source_code
2228                     , p_source_type_code      => p_source_type_code
2229                     , p_source_application_id => p_source_application_id
2230                     , p_array_source_index    => l_array_source_index
2231                     , p_rec_sources           => l_rec_sources
2232                    );
2233      ELSE null;
2234 
2235 END CASE;
2236 
2237 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2238       trace
2239          (p_msg      => 'END of StackSource'
2240          ,p_level    => C_LEVEL_PROCEDURE
2241          ,p_module   => l_log_module);
2242 END IF;
2243 
2244 p_rec_sources        := l_rec_sources;
2245 p_array_source_index := l_array_source_index;
2246 RETURN l_position;
2247 EXCEPTION
2248    WHEN xla_exceptions_pkg.application_exception   THEN
2249         p_rec_sources        := l_rec_sources;
2250         p_array_source_index := l_array_source_index;
2251         IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2252             trace
2253                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||sqlerrm
2254                ,p_level    => C_LEVEL_EXCEPTION
2255                ,p_module   => l_log_module);
2256         END IF;
2257         RETURN NULL;
2258    WHEN OTHERS    THEN
2259       p_rec_sources        := l_rec_sources;
2260       p_array_source_index := l_array_source_index;
2261       xla_exceptions_pkg.raise_message
2262          (p_location => 'xla_cmp_source_pkg.StackSource');
2263 END StackSource;
2264 
2265 --+==========================================================================+
2266 --|                                                                          |
2267 --|                                                                          |
2268 --|                                                                          |
2269 --|                      cache AMB Objects/ AMB Entity Dico                  |
2270 --|                                                                          |
2271 --|                                                                          |
2272 --|                                                                          |
2273 --|                                                                          |
2274 --+==========================================================================+
2275 
2276 /*--------------------------------------------------------------+
2277 | Private function                                              |
2278 |                                                               |
2279 |    IsObjectInAADCache                                         |
2280 |                                                               |
2281 |    Boolean function returns TRUE if the object exists in the  |
2282 |    AMB objects cache, FALSE otherwise. It also returns the    |
2283 |    object position in the cache                               |
2284 +--------------------------------------------------------------*/
2285 
2286 FUNCTION IsObjectInAADCache (
2287     p_object                       IN VARCHAR2
2288   , p_object_code                  IN VARCHAR2
2289   , p_object_type_code             IN VARCHAR2
2290   , p_application_id               IN NUMBER
2291   , p_event_class_code             IN VARCHAR2
2292   , p_event_type_code              IN VARCHAR2
2293   , p_line_definition_code         IN VARCHAR2
2297 )
2294   , p_line_definition_owner_code   IN VARCHAR2
2295   , p_rec_aad_objects              IN t_rec_aad_objects
2296   , p_position                     OUT NOCOPY BINARY_INTEGER
2298 RETURN BOOLEAN
2299 IS
2300 l_position          BINARY_INTEGER;
2301 l_exist             BOOLEAN;
2302 l_log_module        VARCHAR2(240);
2303 BEGIN
2304 IF g_log_enabled THEN
2305       l_log_module := C_DEFAULT_MODULE||'.IsObjectInAADCache';
2306 END IF;
2307 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2308       trace
2309          (p_msg      => 'BEGIN of IsObjectInAADCache'
2310          ,p_level    => C_LEVEL_PROCEDURE
2311          ,p_module   => l_log_module);
2312 END IF;
2313 
2314 l_position:= 1;
2315 l_exist   := FALSE;
2316 
2317 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2318           trace(p_msg    => ' object_type = '||p_object   ||
2319                             ' - object_code = '||p_object_code      ||
2320                                                 '-'||p_object_type_code||
2321                             ' - application_id ='||p_application_id||
2322                             ' - line_definition = '  ||p_line_definition_code     ||
2323                                                 '-'||p_line_definition_owner_code ||
2324                             ' - event_class_code = '   ||p_event_class_code   ||
2325                             ' - event_type_code = '   ||p_event_type_code
2326 
2327                ,p_level  => C_LEVEL_STATEMENT
2328                ,p_module => l_log_module);
2329 
2330  END IF;
2331 
2332 IF p_line_definition_code IS NOT NULL AND p_line_definition_owner_code IS NOT NULL THEN
2333 --journal line type
2334 WHILE (NOT l_exist AND  l_position <= nvl(p_rec_aad_objects.array_object_code.LAST,0) )
2335 LOOP
2336 
2337   IF p_rec_aad_objects.array_object(l_position)                = p_object                       AND
2338      p_rec_aad_objects.array_object_code(l_position)           = p_object_code                  AND
2339      p_rec_aad_objects.array_object_type_code(l_position)      = p_object_type_code             AND
2340      p_rec_aad_objects.array_object_appl_id(l_position)        = p_application_id               AND
2341      p_rec_aad_objects.array_object_jld_code(l_position)       = p_line_definition_code         AND
2342      p_rec_aad_objects.array_object_jld_type_code(l_position)  = p_line_definition_owner_code   AND
2343      p_rec_aad_objects.array_object_class(l_position)          = p_event_class_code             AND
2344      p_rec_aad_objects.array_object_event(l_position)          = p_event_type_code
2345   THEN
2346      l_exist := TRUE;
2347    ELSE
2348      l_position := l_position + 1 ;
2349    END IF;
2350 
2351 END LOOP;
2352 
2353 ELSE
2354  --not journal line type
2355  WHILE (NOT l_exist AND  l_position <= nvl(p_rec_aad_objects.array_object_code.LAST,0) )
2356  LOOP
2357 
2358   IF p_rec_aad_objects.array_object(l_position)                = p_object                       AND
2359      p_rec_aad_objects.array_object_code(l_position)           = p_object_code                  AND
2360      p_rec_aad_objects.array_object_type_code(l_position)      = p_object_type_code             AND
2361      p_rec_aad_objects.array_object_appl_id(l_position)        = p_application_id
2362 
2363   THEN
2364      l_exist := TRUE;
2365    ELSE
2369  END LOOP;
2366      l_position := l_position + 1 ;
2367    END IF;
2368 
2370 
2371 END IF;
2372 
2373 
2374 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2375         trace
2376          (p_msg      => ' l_exist = '||CASE l_exist
2377                                         WHEN TRUE THEN 'TRUE'
2378                                         ELSE 'FALSE'
2379                                        END ||
2380                         ' - position = '||l_position
2381          ,p_level    => C_LEVEL_STATEMENT
2382          ,p_module   => l_log_module);
2383 END IF;
2384 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2385       trace
2386          (p_msg      => 'END of IsObjectInAADCache'
2387          ,p_level    => C_LEVEL_PROCEDURE
2388          ,p_module   => l_log_module);
2389 END IF;
2390 p_position:= l_position;
2391 RETURN l_exist;
2392 EXCEPTION
2393    WHEN xla_exceptions_pkg.application_exception   THEN
2394         IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2395             trace
2396                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||sqlerrm
2397                ,p_level    => C_LEVEL_EXCEPTION
2398                ,p_module   => l_log_module);
2399         END IF;
2400         RETURN FALSE;
2401    WHEN OTHERS    THEN
2402       xla_exceptions_pkg.raise_message
2403          (p_location => 'xla_cmp_source_pkg.IsObjectInAADCache');
2404 END IsObjectInAADCache;
2405 
2406 /*--------------------------------------------------------------+
2407 | Public function                                               |
2408 |                                                               |
2409 |       CacheAADObject                                          |
2410 |                                                               |
2411 |   Cache the AMB objects in the global cache:p_rec_aad_objects |
2412 +--------------------------------------------------------------*/
2413 
2414 FUNCTION CacheAADObject (
2415     p_object                       IN VARCHAR2
2416   , p_object_code                  IN VARCHAR2
2417   , p_object_type_code             IN VARCHAR2
2418   , p_application_id               IN NUMBER
2419   , p_event_class_code             IN VARCHAR2
2420   , p_event_type_code              IN VARCHAR2
2421   , p_line_definition_code         IN VARCHAR2
2422   , p_line_definition_owner_code   IN VARCHAR2
2423   , p_array_source_index           IN t_array_ByInt
2424   , p_rec_aad_objects              IN OUT NOCOPY t_rec_aad_objects
2425 )
2426 RETURN BINARY_INTEGER
2427 IS
2428 l_position             BINARY_INTEGER;
2429 l_exist                BOOLEAN;
2430 l_rec_aad_objects      t_rec_aad_objects;
2431 l_array_source         t_array_ByInt;
2432 l_log_module           VARCHAR2(240);
2433 BEGIN
2434 IF g_log_enabled THEN
2435       l_log_module := C_DEFAULT_MODULE||'.CacheAADObject';
2436 END IF;
2437 --
2438 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2439 
2440       trace
2441          (p_msg      => 'BEGIN of CacheAADObject'
2442          ,p_level    => C_LEVEL_PROCEDURE
2443          ,p_module   => l_log_module);
2444 
2445 END IF;
2446 
2447 l_rec_aad_objects := p_rec_aad_objects;
2448 
2449    l_exist:= IsObjectInAADCache (
2450                           p_object
2451                         , p_object_code
2452                         , p_object_type_code
2453                         , p_application_id
2454                         , p_event_class_code
2455                         , p_event_type_code
2456                         , p_line_definition_code
2457                         , p_line_definition_owner_code
2458                         , l_rec_aad_objects
2459                         , l_position
2460                         );
2461 
2462 IF NOT l_exist THEN
2463  -- cache object definition in l_position
2464  IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2465         trace
2466          (p_msg      => 'Cached in position = '||l_position
2467          ,p_level    => C_LEVEL_STATEMENT
2468          ,p_module   => l_log_module);
2469   END IF;
2470  l_rec_aad_objects.array_object(l_position)               := p_object             ;
2471  l_rec_aad_objects.array_object_code(l_position)          := p_object_code        ;
2472  l_rec_aad_objects.array_object_type_code(l_position)     := p_object_type_code   ;
2473  l_rec_aad_objects.array_object_appl_id(l_position)       := p_application_id     ;
2474  l_rec_aad_objects.array_object_jld_code(l_position)      := p_line_definition_code;
2475  l_rec_aad_objects.array_object_jld_type_code(l_position) := p_line_definition_owner_code ;
2476  l_rec_aad_objects.array_object_class(l_position)         := p_event_class_code   ;
2477  l_rec_aad_objects.array_object_event(l_position)         := p_event_type_code    ;
2478  l_rec_aad_objects.array_array_object(l_position)         := p_array_source_index ;
2479 END IF;
2480 
2481 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2482       trace
2483          (p_msg      => 'END of CacheAADObject'
2484          ,p_level    => C_LEVEL_PROCEDURE
2485          ,p_module   => l_log_module);
2486 END IF;
2487 p_rec_aad_objects := l_rec_aad_objects;
2488 RETURN l_position;
2489 EXCEPTION
2490    WHEN xla_exceptions_pkg.application_exception   THEN
2491         p_rec_aad_objects := l_rec_aad_objects;
2492         IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2493             trace
2494                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||sqlerrm
2495                ,p_level    => C_LEVEL_EXCEPTION
2496                ,p_module   => l_log_module);
2500       xla_exceptions_pkg.raise_message
2497         END IF;
2498         RETURN NULL;
2499    WHEN OTHERS    THEN
2501          (p_location => 'xla_cmp_source_pkg.CacheAADObject');
2502 END CacheAADObject;
2503 
2504 
2505 /*--------------------------------------------------------------+
2506 | Public function                                               |
2507 |                                                               |
2508 |      GetSourcesInAADObject                                    |
2509 |                                                               |
2510 |  Returns the sources defined in an AMB objects. It returns    |
2511 |  the source list in p_array_source_Index and  retrieves       |
2512 |  the source list from the AMB object cache                    |
2513 +--------------------------------------------------------------*/
2514 
2515 PROCEDURE GetSourcesInAADObject (
2516   p_object                       IN VARCHAR2
2517 , p_object_code                  IN VARCHAR2
2518 , p_object_type_code             IN VARCHAR2
2519 , p_application_id               IN NUMBER
2520 , p_event_class_code             IN VARCHAR2
2521 , p_event_type_code              IN VARCHAR2
2522 , p_line_definition_code         IN VARCHAR2
2523 , p_line_definition_owner_code   IN VARCHAR2
2527 IS
2524 , p_array_source_Index           IN OUT NOCOPY t_array_ByInt
2525 , p_rec_aad_objects              IN t_rec_aad_objects
2526 )
2528 l_array_source           t_array_ByInt;
2529 l_position               BINARY_INTEGER;
2530 l_exist                  BOOLEAN;
2531 l_log_module             VARCHAR2(240);
2532 BEGIN
2533 IF g_log_enabled THEN
2534       l_log_module := C_DEFAULT_MODULE||'.GetSourcesInAADObject';
2535 END IF;
2536 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2537       trace
2538          (p_msg      => 'BEGIN of GetSourcesInAADObject ='||p_array_source_Index.COUNT
2539          ,p_level    => C_LEVEL_PROCEDURE
2540          ,p_module   => l_log_module);
2541 END IF;
2542 
2543     l_exist := IsObjectInAADCache (
2544                            p_object
2545                          , p_object_code
2546                          , p_object_type_code
2547                          , p_application_id
2548                          , p_event_class_code
2549                          , p_event_type_code
2550                          , p_line_definition_code
2551                          , p_line_definition_owner_code
2552                          , p_rec_aad_objects
2553                          , l_position
2554                            );
2555 
2556 IF l_exist THEN
2557    l_array_source := p_rec_aad_objects.array_array_object(l_position);
2558    IF  l_array_source.COUNT > 0 THEN
2559      FOR Idx IN l_array_source.FIRST .. l_array_source.LAST LOOP
2560        IF l_array_source.EXISTS(Idx) THEN
2561           p_array_source_Index(Idx) := Idx;
2562        END IF;
2563      END LOOP;
2564    END IF;
2565 END IF;
2566 
2567 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2568       trace
2569          (p_msg      => 'END of GetSourcesInAADObject='||p_array_source_Index.COUNT
2570          ,p_level    => C_LEVEL_PROCEDURE
2571          ,p_module   => l_log_module);
2572 END IF;
2573 EXCEPTION
2574    WHEN xla_exceptions_pkg.application_exception   THEN
2575         IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2576             trace
2577                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||sqlerrm
2578                ,p_level    => C_LEVEL_EXCEPTION
2579                ,p_module   => l_log_module);
2580         END IF;
2581         RAISE;
2582    WHEN OTHERS    THEN
2583       xla_exceptions_pkg.raise_message
2584          (p_location => 'xla_cmp_source_pkg.GetSourcesInAADObject');
2585 END GetSourcesInAADObject;
2586 
2590 |                                                                       |
2587 /*======================================================================+
2588 |                                                                       |
2589 | Public Function                                                       |
2591 | GetAADObjectPosition                                                  |
2592 |                                                                       |
2593 |  It returns the position/index of the AMB object in the cache         |
2594 |                                                                       |
2595 +======================================================================*/
2596 FUNCTION GetAADObjectPosition (
2597     p_object                       IN VARCHAR2
2598   , p_object_code                  IN VARCHAR2
2599   , p_object_type_code             IN VARCHAR2
2600   , p_application_id               IN NUMBER
2601   , p_event_class_code             IN VARCHAR2
2602   , p_event_type_code              IN VARCHAR2
2603   , p_line_definition_code         IN VARCHAR2
2604   , p_line_definition_owner_code   IN VARCHAR2
2605   , p_rec_aad_objects              IN t_rec_aad_objects
2606 )
2607 RETURN BINARY_INTEGER
2608 IS
2609 l_position           BINARY_INTEGER;
2610 l_exist              BOOLEAN;
2611 l_log_module         VARCHAR2(240);
2612 BEGIN
2613 IF g_log_enabled THEN
2614       l_log_module := C_DEFAULT_MODULE||'.GetAADObjectPosition';
2615 END IF;
2616 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2617 
2618       trace
2619          (p_msg      => 'BEGIN of GetAADObjectPosition'
2620          ,p_level    => C_LEVEL_PROCEDURE
2621          ,p_module   => l_log_module);
2622 
2623 END IF;
2624 
2625 l_exist := IsObjectInAADCache (
2626                            p_object
2627                          , p_object_code
2628                          , p_object_type_code
2629                          , p_application_id
2630                          , p_event_class_code
2631                          , p_event_type_code
2632                          , p_line_definition_code
2633                          , p_line_definition_owner_code
2634                          , p_rec_aad_objects
2635                          , l_position
2636                            );
2637 
2638 IF NOT l_exist THEN l_position  := NULL; END IF;
2639 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2640 
2641       trace
2642          (p_msg      => 'END of GetAADObjectPosition'
2643          ,p_level    => C_LEVEL_PROCEDURE
2644          ,p_module   => l_log_module);
2645 
2646 END IF;
2647 RETURN l_position;
2648 EXCEPTION
2649    WHEN xla_exceptions_pkg.application_exception   THEN
2650         IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2651             trace
2652                (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR = '||sqlerrm
2653                ,p_level    => C_LEVEL_EXCEPTION
2654                ,p_module   => l_log_module);
2655         END IF;
2656         RETURN NULL;
2657    WHEN OTHERS    THEN
2658       xla_exceptions_pkg.raise_message
2659          (p_location => 'xla_cmp_source_pkg.GetAADObjectPosition');
2660 END GetAADObjectPosition;
2661 --
2662 --
2663 --+==========================================================================+
2664 --|                                                                          |
2665 --|                                                                          |
2666 --|                                                                          |
2667 --|    Generate GetMeaning API for the source associated to value set        |
2668 --|                                                                          |
2669 --|                                                                          |
2670 --|                                                                          |
2671 --|                                                                          |
2672 --|                                                                          |
2673 --|                                                                          |
2674 --|                                                                          |
2675 --|                                                                          |
2676 --|                                                                          |
2680 --
2677 --|                                                                          |
2678 --|                                                                          |
2679 --+==========================================================================+
2681 /*======================================================================+
2682 |                                                                       |
2683 | Public Function                                                       |
2684 |                                                                       |
2685 | id_column_is_null                                                     |
2686 |                                                                       |
2687 | Returns true if the id column is null                                 |
2688 |                                                                       |
2689 +======================================================================*/
2690 FUNCTION  id_column_is_null
2691   (p_flex_value_set_id               IN  NUMBER)
2692 RETURN BOOLEAN
2693 IS
2694 
2695    l_id_column_name   varchar2(240);
2696    l_return           boolean;
2697    l_log_module         VARCHAR2(240);
2698 BEGIN
2699 IF g_log_enabled THEN
2700       l_log_module := C_DEFAULT_MODULE||'.id_column_is_null';
2701 END IF;
2702 --
2703 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2704 
2705       trace
2706          (p_msg      => 'BEGIN of id_column_is_null'
2707          ,p_level    => C_LEVEL_PROCEDURE
2708          ,p_module   => l_log_module);
2709 
2710       trace
2711          (p_msg      => 'p_flex_value_set_id = '||p_flex_value_set_id
2712          ,p_level    => C_LEVEL_PROCEDURE
2713          ,p_module   => l_log_module);
2714 
2715 
2716 END IF;
2717 
2718    SELECT id_column_name
2719      INTO l_id_column_name
2720      FROM fnd_flex_validation_tables
2721     WHERE flex_value_set_id = p_flex_value_set_id;
2722 
2723    IF l_id_column_name is null THEN
2724       l_return := TRUE;
2725    ELSE
2726       l_return := FALSE;
2727    END IF;
2728 
2729 --
2730 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2731 
2732       trace
2733          (p_msg      => 'END of id_column_is_null'
2734          ,p_level    => C_LEVEL_PROCEDURE
2735          ,p_module   => l_log_module);
2736 
2737 END IF;
2738 
2739 RETURN l_return;
2740 
2741 EXCEPTION
2742 WHEN OTHERS THEN
2743    RETURN FALSE;
2744 END id_column_is_null;
2745 --
2746 --
2747 /*======================================================================+
2748 |                                                                       |
2749 | Public Function                                                       |
2750 |                                                                       |
2751 |   flex_values_exist                                                   |
2752 |                                                                       |
2753 | Returns true if source associated to flex value set exist             |
2754 |                                                                       |
2755 +======================================================================*/
2756 FUNCTION  flex_values_exist
2757   (p_array_flex_value_set_id      IN xla_cmp_source_pkg.t_array_Num)
2758 RETURN BOOLEAN
2759 IS
2760 l_return             boolean:= FALSE;
2761 l_log_module         VARCHAR2(240);
2762 BEGIN
2763 IF g_log_enabled THEN
2764       l_log_module := C_DEFAULT_MODULE||'.flex_values_exist';
2765 END IF;
2766 --
2767 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2768 
2769       trace
2770          (p_msg      => 'BEGIN of flex_values_exist'
2771          ,p_level    => C_LEVEL_PROCEDURE
2772          ,p_module   => l_log_module);
2773 
2774 END IF;
2775 
2779   --
2776 IF p_array_flex_value_set_id.COUNT > 0 THEN
2777 
2778   FOR Idx IN p_array_flex_value_set_id.FIRST .. p_array_flex_value_set_id.LAST LOOP
2780     IF p_array_flex_value_set_id.EXISTS(Idx) AND
2781        p_array_flex_value_set_id(Idx) IS NOT NULL THEN
2782 
2783        l_return:= TRUE;
2784 
2785     END IF;
2786   --
2787   END LOOP;
2788 
2789 END IF;
2790 
2791 --
2792 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2793 
2794       trace
2795          (p_msg      => 'END of flex_values_exist'
2796          ,p_level    => C_LEVEL_PROCEDURE
2797          ,p_module   => l_log_module);
2798 
2799 END IF;
2800 
2801 RETURN l_return;
2802 
2803 EXCEPTION
2804 WHEN OTHERS THEN
2805    RETURN FALSE;
2806 END flex_values_exist;
2807 --
2808 /*======================================================================+
2809 |                                                                       |
2810 | Public Function                                                       |
2811 |                                                                       |
2812 | get_meaning_statement                                                 |
2813 |                                                                       |
2814 |                                                                       |
2815 +======================================================================*/
2816 FUNCTION  get_meaning_statement(p_flex_value_set_id        IN  NUMBER)
2817 RETURN VARCHAR2
2818 IS
2819 --
2820 l_validation_type                    VARCHAR2(1);
2821 l_statement                          VARCHAR2(4000);
2822 l_statement_run                      VARCHAR2(1000);
2823 l_additional_where_clause            VARCHAR2(4000);
2824 l_value_column_name                  VARCHAR2(4000);
2825 l_application_table_name             VARCHAR2(4000);
2826 l_id_column_name                     VARCHAR2(4000);
2827 l_number                             NUMBER;
2828 l_log_module                         VARCHAR2(240);
2829 --
2830 BEGIN
2831 IF g_log_enabled THEN
2832       l_log_module := C_DEFAULT_MODULE||'.get_meaning_statement';
2833 END IF;
2834 --
2835 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2836 
2837       trace
2838          (p_msg      => 'BEGIN of get_meaning_statement'
2839          ,p_level    => C_LEVEL_PROCEDURE
2840          ,p_module   => l_log_module);
2841 
2842 END IF;
2843 
2844  SELECT   validation_type
2845    INTO   l_validation_type
2846    FROM   fnd_flex_value_sets
2847    WHERE  flex_value_set_id = p_flex_value_set_id;
2848 
2849    IF l_validation_type in ('I','X') THEN
2850       --
2851       -- Independant value set
2852       --
2853       l_statement_run := 'SELECT flex_value_meaning'
2854                          ||  xla_environment_pkg.g_chr_newline
2855                          || '     INTO   l_meaning_meaning'
2856                          ||  xla_environment_pkg.g_chr_newline
2857                          || '     FROM   fnd_flex_values_vl'
2858                          ||  xla_environment_pkg.g_chr_newline
2859                          ||'     WHERE  flex_value_set_id      = p_flex_value_set_id'
2860                          ||  xla_environment_pkg.g_chr_newline
2861                          || '     AND  flex_value             = p_flex_value'
2862                          ||  xla_environment_pkg.g_chr_newline
2863                          ||  '     ;'
2864                          ;
2865 
2866    ELSIF l_validation_type = 'F' THEN
2867 
2868       IF id_column_is_null(p_flex_value_set_id) THEN
2869 
2870          l_statement_run := 'l_meaning_meaning := p_flex_value;';
2871 
2872       ELSE
2873 
2874          SELECT additional_where_clause
2875               , value_column_name
2876               , application_table_name
2877               , id_column_name
2878            INTO l_additional_where_clause
2879               , l_value_column_name
2880               , l_application_table_name
2881               , l_id_column_name
2882            FROM fnd_flex_validation_tables
2883           WHERE flex_value_set_id = p_flex_value_set_id;
2884 
2885          IF l_additional_where_clause is not null THEN
2886 
2887             l_additional_where_clause :=
2888                 Ltrim(l_additional_where_clause);
2889 
2890             l_number := Instr(Upper(l_additional_where_clause),'ORDER BY ');
2891             IF (l_number = 1) THEN
2892                l_additional_where_clause := null;
2893             ELSE
2894                l_number := Instr(Upper(l_additional_where_clause),'WHERE ');
2895 
2896                IF (l_number = 1) THEN
2897                   l_additional_where_clause :=
2898                     Substr(l_additional_where_clause,7);
2899                ELSE
2900                   l_additional_where_clause := l_additional_where_clause;
2901                END IF;
2902             END IF;
2903          END IF;
2904 
2905          IF l_additional_where_clause is null THEN
2906 
2907             --
2908             -- Table value set
2909             --
2910             l_statement:= 'SELECT distinct '|| l_value_column_name  --14756735
2911                           ||  xla_environment_pkg.g_chr_newline
2912                           || '     INTO   l_meaning_meaning'
2913                           ||  xla_environment_pkg.g_chr_newline
2914                           ||  '     FROM   '||l_application_table_name
2915                           ||  xla_environment_pkg.g_chr_newline
2916                           ||  '     WHERE  '||l_id_column_name  || ' = p_flex_value'
2917                           ||  xla_environment_pkg.g_chr_newline
2918                           || '     ;'
2919                           ;
2920          ELSE
2921             --
2922             -- Table value set
2926                ||  xla_environment_pkg.g_chr_newline
2923             --
2924 
2925             l_statement:= 'SELECT distinct '||l_value_column_name  --14756735
2927                || '     INTO   l_meaning_meaning'
2928                ||  xla_environment_pkg.g_chr_newline
2929                ||  '     FROM   '||l_application_table_name
2930                ||  xla_environment_pkg.g_chr_newline
2931                ||  '     WHERE  '||l_id_column_name  || ' = p_flex_value'
2932                ||  '      AND  '||l_additional_where_clause
2933                ||  '     ;'
2934                ;
2935 
2936          END IF;
2937 
2938         IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2939 
2940          trace
2941              (p_msg      => '>> EXECUTE dynamic SQL = '||l_statement
2942              ,p_level    => C_LEVEL_STATEMENT
2943              ,p_module   => l_log_module);
2944 
2945         END IF;
2946         --
2947         --
2948         l_statement_run := l_statement ;
2949         l_statement_run := l_statement_run  || xla_environment_pkg.g_chr_newline
2950                           ;
2951 
2952       END IF;
2953  --
2954   END IF;
2955 
2956 --
2957 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2958 
2959       trace
2960          (p_msg      => 'END of get_meaning_statement'
2961          ,p_level    => C_LEVEL_PROCEDURE
2962          ,p_module   => l_log_module);
2963 
2964 END IF;
2965 
2966 RETURN l_statement_run;
2967 EXCEPTION
2968 WHEN OTHERS THEN
2969    RETURN NULL;
2970 END get_meaning_statement;
2971 --
2972 --+==========================================================================+
2973 --|                                                                          |
2974 --| Private Function                                                         |
2975 --|                                                                          |
2976 --+==========================================================================+
2977 --
2978 FUNCTION GenerateGetMeaningFct(
2979   p_package_name                 IN VARCHAR2
2980 , p_array_flex_value_set_id      IN xla_cmp_source_pkg.t_array_Num
2981 )
2982 RETURN VARCHAR2
2983 IS
2984 --
2985 l_API                VARCHAR2(32000);
2986 l_body               VARCHAR2(30000);
2987 l_when               VARCHAR2(10000);
2988 l_log_module         VARCHAR2(240);
2989 --
2990 BEGIN
2991 --
2992 IF g_log_enabled THEN
2993       l_log_module := C_DEFAULT_MODULE||'.GenerateGetMeaningFct';
2994 END IF;
2995 --
2996 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2997 
2998       trace
2999          (p_msg      => 'BEGIN of GenerateGetMeaningFct'
3000          ,p_level    => C_LEVEL_PROCEDURE
3001          ,p_module   => l_log_module);
3002 
3003 END IF;
3004 --
3005 l_API       := C_GET_MEANING_FTC;
3006 l_body      := NULL;
3007 
3008 FOR Idx IN p_array_flex_value_set_id.FIRST .. p_array_flex_value_set_id.LAST LOOP
3009 
3010    IF p_array_flex_value_set_id.EXISTS(Idx) AND
3011       p_array_flex_value_set_id(Idx) IS NOT NULL THEN
3012 
3013       l_when := C_WHEN_THEN;
3014       l_when := REPLACE(l_when,'$flex_value_set_id$',TO_CHAR(p_array_flex_value_set_id(Idx)));
3015       l_when := REPLACE(l_when,'$meaning_meaning$',get_meaning_statement(p_array_flex_value_set_id(Idx)));
3016       l_body := l_body || l_when;
3017 
3018    END IF;
3019 
3020 END LOOP;
3021 --
3022 l_API     := REPLACE(l_API ,'$body$',l_body);
3023 --
3024 l_API     := REPLACE(l_API ,'$package_name$',p_package_name);
3025 --
3026 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3027 
3028       trace
3029          (p_msg      => 'END of GenerateGetMeaningFct'
3030          ,p_level    => C_LEVEL_PROCEDURE
3031          ,p_module   => l_log_module);
3032 
3033 END IF;
3034 --
3035 RETURN l_API;
3036 --
3037 EXCEPTION
3038    WHEN VALUE_ERROR THEN
3039         IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3040          trace
3041             (p_msg      => 'ERROR: XLA_CMP_COMPILER_ERROR'
3042             ,p_level    => C_LEVEL_EXCEPTION
3043             ,p_module   => l_log_module);
3044         END IF;
3045         xla_exceptions_pkg.raise_message
3046                                    ('XLA'
3047                                    ,'XLA_CMP_COMPILER_ERROR'
3048                                    ,'PROCEDURE'
3049                                    ,'xla_cmp_source_pkg.GenerateGetMeaningFct'
3050                                    ,'ERROR'
3051                                    , sqlerrm
3052                                    );
3053 
3054    WHEN xla_exceptions_pkg.application_exception   THEN
3055         RETURN NULL;
3056    WHEN OTHERS THEN
3057       xla_exceptions_pkg.raise_message
3058          (p_location => 'xla_cmp_source_pkg.GenerateGetMeaningFct');
3059 END GenerateGetMeaningFct;
3060 
3061 --+==========================================================================+
3062 --|                                                                          |
3063 --| Private Function                                                         |
3064 --|                                                                          |
3065 --+==========================================================================+
3066 --
3067 --
3068 FUNCTION GenerateGetMeaning(
3069   p_package_name                 IN VARCHAR2
3070 , p_array_flex_value_set_id      IN xla_cmp_source_pkg.t_array_Num
3071 , p_IsCompiled                   IN OUT NOCOPY BOOLEAN
3072 )
3073 RETURN DBMS_SQL.VARCHAR2S
3074 IS
3075 --
3076 l_array_api                    DBMS_SQL.VARCHAR2S;
3077 l_API                          VARCHAR2(32000);
3078 --
3079 l_IsCompiled                   BOOLEAN;
3080 l_log_module                   VARCHAR2(240);
3084 IF g_log_enabled THEN
3081 --
3082 BEGIN
3083 --
3085       l_log_module := C_DEFAULT_MODULE||'.GenerateGetMeaning';
3086 END IF;
3087 --
3088 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3089 
3090       trace
3091          (p_msg      => 'BEGIN of GenerateGetMeaning'
3092          ,p_level    => C_LEVEL_PROCEDURE
3093          ,p_module   => l_log_module);
3094 
3095 END IF;
3096 --
3097 -- Init global variables
3098 --
3099 l_array_api    := xla_cmp_string_pkg.g_null_varchar2s;
3100 --
3101 --
3102 l_API  := GenerateGetMeaningFct(
3103   p_package_name                 => p_package_name
3104 , p_array_flex_value_set_id      => p_array_flex_value_set_id
3105 )
3106 ;
3107 --
3108 p_IsCompiled    := (l_API IS NOT NULL);
3109 --
3110 --
3111 xla_cmp_string_pkg.CreateString(
3112                       p_package_text  => l_API
3113                      ,p_array_string  => l_array_api
3114                      );
3115 
3116 --
3117 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3118 
3119       trace
3120          (p_msg      => 'END of GenerateGetMeaning'
3121          ,p_level    => C_LEVEL_PROCEDURE
3122          ,p_module   => l_log_module);
3123 
3124 END IF;
3125 --
3126 RETURN l_array_api ;
3127 --
3128 EXCEPTION
3129    WHEN xla_exceptions_pkg.application_exception   THEN
3130         p_IsCompiled := FALSE;
3131         RETURN xla_cmp_string_pkg.g_null_varchar2s;
3132    WHEN OTHERS THEN
3133       p_IsCompiled := FALSE;
3134       xla_exceptions_pkg.raise_message
3135          (p_location => 'xla_cmp_source_pkg.GenerateGetMeaning');
3136 END GenerateGetMeaning;
3137 --
3138 --+==========================================================================+
3139 --|                                                                          |
3140 --| Public Function                                                          |
3141 --|                                                                          |
3142 --+==========================================================================+
3143 --
3144 FUNCTION GenerateGetMeaningAPI(
3145   p_package_name                 IN VARCHAR2
3146 , p_array_flex_value_set_id      IN xla_cmp_source_pkg.t_array_Num
3147 , p_package_body                 OUT NOCOPY DBMS_SQL.VARCHAR2S
3148 )
3149 RETURN BOOLEAN
3150 IS
3151 l_API                DBMS_SQL.VARCHAR2S;
3152 l_IsCompiled         BOOLEAN;
3153 l_log_module         VARCHAR2(240);
3154 BEGIN
3155 --
3156 IF g_log_enabled THEN
3157       l_log_module := C_DEFAULT_MODULE||'.GenerateGetMeaningAPI';
3158 END IF;
3159 --
3160 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3161 
3162       trace
3163          (p_msg      => 'BEGIN of GenerateGetMeaningAPI'
3164          ,p_level    => C_LEVEL_PROCEDURE
3165          ,p_module   => l_log_module);
3166 
3167 END IF;
3168 --
3169 -- Init global variables
3170 --
3171 
3172 l_IsCompiled   := TRUE;
3173 l_API          := xla_cmp_string_pkg.g_null_varchar2s;
3174 --
3175 IF flex_values_exist(p_array_flex_value_set_id) THEN
3176 --
3177 l_API  := GenerateGetMeaning(
3178    p_package_name              =>  p_package_name
3179  , p_array_flex_value_set_id   =>  p_array_flex_value_set_id
3180  , p_IsCompiled                =>  l_IsCompiled
3181 );
3182 --
3183 ELSE
3184 --
3185 xla_cmp_string_pkg.CreateString(
3186                       p_package_text  => REPLACE(C_GET_MEANING_FTC_NULL
3187                                                 ,'$package_name$'
3188                                                 , p_package_name)
3189                      ,p_array_string  => l_API
3190                      );
3191 
3192 END IF;
3193 --
3194 p_package_body := l_API ;
3195 --
3196 --
3197 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3198    IF l_IsCompiled THEN
3199       trace
3200          (p_msg      => 'return value (l_IsCompiled) = TRUE'
3201          ,p_level    => C_LEVEL_PROCEDURE
3202          ,p_module   => l_log_module);
3203    ELSE
3204       trace
3205          (p_msg      => 'return value (l_IsCompiled) = FALSE'
3206          ,p_level    => C_LEVEL_PROCEDURE
3207          ,p_module   => l_log_module);
3208    END IF;
3209 
3210    trace
3211       (p_msg      => 'END of GenerateGetMeaningAPI'
3212       ,p_level    => C_LEVEL_PROCEDURE
3213       ,p_module   => l_log_module);
3214 END IF;
3215 
3216 RETURN l_IsCompiled;
3217 EXCEPTION
3218 WHEN xla_exceptions_pkg.application_exception   THEN
3219    RETURN FALSE;
3220 WHEN OTHERS THEN
3221    xla_exceptions_pkg.raise_message
3222       (p_location => 'xla_cmp_source_pkg.GenerateGetMeaningAPI');
3223 END GenerateGetMeaningAPI;
3224 --=============================================================================
3225 --
3226 --
3227 --
3228 --
3229 --
3230 --
3231 --
3232 --
3233 --
3234 --
3235 --
3236 --
3237 --
3238 --
3239 --
3240 --
3241 --
3242 --
3243 --
3244 --
3245 --
3246 --
3247 --=============================================================================
3248 --=============================================================================
3249 --          *********** Initialization routine **********
3250 --=============================================================================
3251 
3252 BEGIN
3253 
3254    g_log_level      := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3255    g_log_enabled    := fnd_log.test
3256                           (log_level  => g_log_level
3257                           ,module     => C_DEFAULT_MODULE);
3258 
3259    IF NOT g_log_enabled  THEN
3263 --
3260       g_log_level := C_LEVEL_LOG_DISABLED;
3261    END IF;
3262 
3264 END xla_cmp_source_pkg;