[Home] [Help]
PACKAGE BODY: APPS.XLA_CMP_EXTRACT_PKG
Source
1 PACKAGE BODY xla_cmp_extract_pkg AS
2 /* $Header: xlacpext.pkb 120.60.12000000.2 2007/10/12 06:09:08 samejain 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_extract_pkg |
10 | |
11 | DESCRIPTION |
12 | This is a XLA private package, which contains all the logic required |
13 | to generate header and line cursors from AMB specifcations |
14 | |
15 | |
16 | HISTORY |
17 | 25-JUN-2002 K.Boussema Created |
18 | 10-MAR-2003 K.Boussema Made changes for the new bulk approach of the|
19 | accounting engine |
20 | 19-MAR-2003 K.Boussema Added amb_context_code |
21 | 14-APR-2003 K.Bouusema Added the error messages |
22 | 05-MAI-2003 K.Boussema Modified to retrieve data base on ledger_id |
23 | 13-MAI-2003 K.Boussema Modified the Extract according to bug 2857548|
24 | 24-JUN-2003 K.Boussema Reviewed the erro messages, bug 3022261 |
25 | 17-JUL-2003 K.Boussema Reviewed the code |
26 | 26-AUG-2003 K.Boussema Reviewed the generation of the extract to |
27 | handle the use of line_number as source |
28 | 22-SEP-2003 K.Boussema Added validation of primary keys columns |
29 | 29-SEP-2003 K.Boussema Added Error message XLA_CMP_PK_MISSING |
30 | 09-OCT-2003 K.Boussema Changed to accept AADs differents Extract |
31 | specifcations |
32 | 12-DEC-2003 K.Boussema Reviewed for bug bug 3042840 |
33 | 18-DEC-2003 K.Boussema Changed to fix bug 3042840,3307761,3268940 |
34 | 3310291 and 3320689 |
35 | 22-DEC-2003 K.Boussema Replaced Extract Validations by a call to |
36 | Extract Integrity Checker routine |
37 | 30-DEC-2003 K.Boussema Reviewed GetExtractObjects procedure |
38 | 05-JAN-2004 K.Boussema Changed GenerateLineCursor, |
39 | GenerateExtractColumns and |
40 | GenerateHeaderCursor procedures |
41 | 02-FEB-2004 K.Boussema Reviewed FlushGtTable procedure |
42 | 11-FEB-2004 K.Boussema Revised GenerateHeaderCursor procedure |
43 | 23-FEB-2004 K.Boussema Made changes for the FND_LOG. |
44 | 12-MAR-2004 K.Boussema Changed to incorporate the select of lookups |
45 | from the extract objects |
46 | 22-MAR-2004 K.Boussema Added a parameter p_module to the TRACE calls|
47 | and the procedure. |
48 | 11-MAY-2004 K.Boussema Removed the call to XLA trace routine from |
49 | trace() procedure |
50 | 20-Sep-2004 S.Singhania Made chnages for the bulk performance: |
51 | - Added routines GenerateHdrStructure and |
52 | GenerateCacheHdrSources |
53 | - Modified routines GenerateHdrVariables, |
54 | GenerateLineStructure,GenerateLineCursor,|
55 | GenerateHeaderCursor, GenerateFetchLineCu|
56 | rsor, GenerateFetchHeaderCursor |
57 | - Replace the constant C_HDR_CUR with C_HDR|
58 | _CUR_EVENT_TYPE and C_HDR_CUR_EVENT_CLASS|
59 | - Replace the constant C_LINE_CUR with C_LI|
60 | NE_CUR_EVENT_TYPE, C_LINE_CUR_EVENT_CLASS|
61 | 06-Oct-2004 K.Boussema Made changes for the Accounting Event Extract|
62 | Diagnostics feature. |
63 | 08-DEC-2004 K.Boussema Updated to change xla_extract_sources table |
64 | by xla_diag_sources |
65 | 03-Mar-2005 W.shen remove the hint in line cursor |
66 | 06-Mar-2005 W.shen Ledger Currency Project. |
67 | Remove the ledger currency level extract |
68 | object. Add ledger_id to ledger line level |
69 | object. join to ledger_id depends on alc |
70 | setting . |
71 | 08-Jun-2005 K.Boussema Reviewed C_INSERT_LINE_SOURCES_CLASS constant |
72 | to fix bug 4200257 |
73 | 21-JUL-2005 K.Boussema Reviewed to handle the two cases: |
74 | - no header Transaction objects and |
75 | - no line Transaction objects |
76 | 01-Aug-2005 W. Chan 4458381 - Public Sector Enhancement |
77 | 11-Sep-2006 V. Swapna Bug 5478323: Correct an ORA-01400 |
78 | error on the table xla_diag_sources. |
79 +===========================================================================*/
80 --
81 --
82 --+==========================================================================+
83 --| |
84 --| GLOBAL CONSTANTS |
85 --| |
86 --| |
87 --+==========================================================================+
88 --
89 --+==========================================================================+
90 --| |
91 --| Header CURSOR Template |
92 --| |
93 --+==========================================================================+
94 --
95 --
96 C_HDR_CUR_EVENT_TYPE CONSTANT VARCHAR2(10000):='
97 --
98 CURSOR header_cur
99 IS
100 SELECT /*+ leading(xet) cardinality(xet,1) */
101 -- Event Type Code: $event_type_code$
102 -- Event Class Code: $event_class_code$
103 xet.entity_id
104 , xet.legal_entity_id
105 , xet.entity_code
106 , xet.transaction_number
107 , xet.event_id
108 , xet.event_class_code
109 , xet.event_type_code
110 , xet.event_number
111 , xet.event_date
112 , xet.transaction_date
113 , xet.reference_num_1
114 , xet.reference_num_2
115 , xet.reference_num_3
116 , xet.reference_num_4
117 , xet.reference_char_1
118 , xet.reference_char_2
119 , xet.reference_char_3
120 , xet.reference_char_4
121 , xet.reference_date_1
122 , xet.reference_date_2
123 , xet.reference_date_3
124 , xet.reference_date_4
125 , xet.event_created_by
126 , xet.budgetary_control_flag $hdr_sources$
127 FROM xla_events_gt xet $hdr_tabs$
128 WHERE xet.event_date between p_pad_start_date and p_pad_end_date
129 and xet.event_type_code = C_EVENT_TYPE_CODE
130 and xet.event_status_code <> ''N'' $hdr_clauses$
131 ORDER BY event_id
132 ;
133 ';
134
135 C_HDR_CUR_EVENT_CLASS CONSTANT VARCHAR2(10000):='
136 --
137 CURSOR header_cur
138 IS
139 SELECT /*+ leading(xet) cardinality(xet,1) */
140 -- Event Class Code: $event_class_code$
141 xet.entity_id
142 ,xet.legal_entity_id
143 ,xet.entity_code
144 ,xet.transaction_number
145 ,xet.event_id
146 ,xet.event_class_code
147 ,xet.event_type_code
148 ,xet.event_number
149 ,xet.event_date
150 ,xet.transaction_date
151 ,xet.reference_num_1
152 ,xet.reference_num_2
153 ,xet.reference_num_3
154 ,xet.reference_num_4
155 ,xet.reference_char_1
156 ,xet.reference_char_2
157 ,xet.reference_char_3
158 ,xet.reference_char_4
159 ,xet.reference_date_1
160 ,xet.reference_date_2
161 ,xet.reference_date_3
162 ,xet.reference_date_4
163 ,xet.event_created_by
164 ,xet.budgetary_control_flag $hdr_sources$
165 FROM xla_events_gt xet $hdr_tabs$
166 WHERE xet.event_date between p_pad_start_date and p_pad_end_date
167 and xet.event_class_code = C_EVENT_CLASS_CODE
168 and xet.event_status_code <> ''N'' $hdr_clauses$
169 ORDER BY event_id
170 ;
171 ';
172 --
173 --
174 --+==========================================================================+
175 --| |
176 --| Line CURSOR Template |
177 --| |
178 --+==========================================================================+
179 --
180 --
181 C_LINE_CUR_EVENT_TYPE CONSTANT VARCHAR2(10000):='
182 --
183 CURSOR line_cur (x_first_event_id in number, x_last_event_id in number)
184 IS
185 SELECT /*+ leading(xet) cardinality(xet,1) */
186 -- Event Type Code: $event_type_code$
187 -- Event Class Code: $event_class_code$
188 xet.entity_id
189 ,xet.legal_entity_id
190 ,xet.entity_code
191 ,xet.transaction_number
192 ,xet.event_id
193 ,xet.event_class_code
194 ,xet.event_type_code
195 ,xet.event_number
196 ,xet.event_date
197 ,xet.transaction_date
198 ,xet.reference_num_1
199 ,xet.reference_num_2
200 ,xet.reference_num_3
201 ,xet.reference_num_4
202 ,xet.reference_char_1
203 ,xet.reference_char_2
204 ,xet.reference_char_3
205 ,xet.reference_char_4
206 ,xet.reference_date_1
207 ,xet.reference_date_2
208 ,xet.reference_date_3
209 ,xet.reference_date_4
210 ,xet.event_created_by
211 ,xet.budgetary_control_flag $line_sources$
212 FROM xla_events_gt xet $line_tabs$
213 WHERE xet.event_id between x_first_event_id and x_last_event_id
214 and xet.event_date between p_pad_start_date and p_pad_end_date
215 and xet.event_type_code = C_EVENT_TYPE_CODE
216 and xet.event_status_code <> ''N'' $line_clauses$;
217 ';
218
219 C_LINE_CUR_EVENT_CLASS CONSTANT VARCHAR2(10000):='
220 --
221 CURSOR line_cur (x_first_event_id in number, x_last_event_id in number)
222 IS
223 SELECT /*+ leading(xet) cardinality(xet,1) */
224 -- Event Class Code: $event_class_code$
225 xet.entity_id
226 ,xet.legal_entity_id
227 ,xet.entity_code
228 ,xet.transaction_number
229 ,xet.event_id
230 ,xet.event_class_code
231 ,xet.event_type_code
232 ,xet.event_number
233 ,xet.event_date
234 ,xet.transaction_date
235 ,xet.reference_num_1
236 ,xet.reference_num_2
237 ,xet.reference_num_3
238 ,xet.reference_num_4
239 ,xet.reference_char_1
240 ,xet.reference_char_2
241 ,xet.reference_char_3
242 ,xet.reference_char_4
243 ,xet.reference_date_1
244 ,xet.reference_date_2
245 ,xet.reference_date_3
246 ,xet.reference_date_4
247 ,xet.event_created_by
248 ,xet.budgetary_control_flag
249 $line_sources$
250 FROM xla_events_gt xet $line_tabs$
251 WHERE xet.event_id between x_first_event_id and x_last_event_id
252 and xet.event_date between p_pad_start_date and p_pad_end_date
253 and xet.event_class_code = C_EVENT_CLASS_CODE
254 and xet.event_status_code <> ''N'' $line_clauses$;
255 ';
256
257 --
258 -----------------------------------------------------------------------------
259 --
260 -- Accounting Event Extract Diagnostics Constants/Templates
261 --
262 ------------------------------------------------------------------------------
263 --
264 --+==========================================================================+
265 --| |
266 --| Insert header sources Template |
267 --| |
268 --+==========================================================================+
269 --
270 --
271 C_INSERT_HDR_SOURCES_EVT CONSTANT VARCHAR2(10000):='
272 --
273 INSERT INTO xla_diag_sources --hdr1
274 (
275 event_id
276 , ledger_id
277 , sla_ledger_id
278 , description_language
279 , object_name
280 , object_type_code
281 , line_number
282 , source_application_id
283 , source_type_code
284 , source_code
285 , source_value
286 , source_meaning
287 , created_by
288 , creation_date
289 , last_update_date
290 , last_updated_by
291 , last_update_login
292 , program_update_date
293 , program_application_id
294 , program_id
295 , request_id
296 )
297 SELECT
301 , p_language
298 event_id
299 , p_target_ledger_id
300 , p_sla_ledger_id
302 , object_name
303 , object_type_code
304 , line_number
305 , source_application_id
306 , source_type_code
307 , source_code
308 , SUBSTR(source_value ,1,1996)
309 , SUBSTR(source_meaning,1,200)
310 , xla_environment_pkg.g_Usr_Id
311 , TRUNC(SYSDATE)
312 , TRUNC(SYSDATE)
313 , xla_environment_pkg.g_Usr_Id
314 , xla_environment_pkg.g_Login_Id
315 , TRUNC(SYSDATE)
316 , xla_environment_pkg.g_Prog_Appl_Id
317 , xla_environment_pkg.g_Prog_Id
318 , xla_environment_pkg.g_Req_Id
319 FROM (
320 SELECT xet.event_id event_id
321 , 0 line_number
322 , CASE r
323 $object_name$
324 ELSE null
325 END object_name
326 , CASE r
327 $object_type_code$
328 ELSE null
329 END object_type_code
330 , CASE r
331 $source_application_id$
332 ELSE null
333 END source_application_id
334 , $source_type_code$ source_type_code
335 , CASE r
336 $source_code$
337 ELSE null
338 END source_code
339 , CASE r
340 $source_value$
341 ELSE null
342 END source_value
343 , $source_meaning$ source_meaning
344 FROM xla_events_gt xet $hdr_tabs$
345 ,(select rownum r from all_objects where rownum <= $source_number$ and owner = p_apps_owner)
346 WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
347 AND xet.event_type_code = C_EVENT_TYPE_CODE
348 $hdr_clauses$
349 )
350 ;
351 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
352
353 trace
354 (p_msg => ''number of header sources inserted = ''||SQL%ROWCOUNT
355 ,p_level => C_LEVEL_STATEMENT
356 ,p_module => l_log_module);
357
358 END IF;
359 --
360 ';
361 --
362 --
363 --+==========================================================================+
364 --| |
365 --| Insert header sources Template |
366 --| |
367 --+==========================================================================+
368 --
369 --
370 C_INSERT_HDR_SOURCES_CLASS CONSTANT VARCHAR2(10000):='
371 --
372 INSERT INTO xla_diag_sources --hdr2
373 (
374 event_id
375 , ledger_id
376 , sla_ledger_id
377 , description_language
378 , object_name
379 , object_type_code
380 , line_number
381 , source_application_id
382 , source_type_code
383 , source_code
384 , source_value
385 , source_meaning
386 , created_by
387 , creation_date
388 , last_update_date
389 , last_updated_by
390 , last_update_login
391 , program_update_date
392 , program_application_id
393 , program_id
394 , request_id
395 )
396 SELECT
397 event_id
398 , p_target_ledger_id
399 , p_sla_ledger_id
400 , p_language
401 , object_name
402 , object_type_code
403 , line_number
404 , source_application_id
405 , source_type_code
406 , source_code
407 , SUBSTR(source_value ,1,1996)
408 , SUBSTR(source_meaning ,1,200)
409 , xla_environment_pkg.g_Usr_Id
410 , TRUNC(SYSDATE)
411 , TRUNC(SYSDATE)
412 , xla_environment_pkg.g_Usr_Id
413 , xla_environment_pkg.g_Login_Id
414 , TRUNC(SYSDATE)
415 , xla_environment_pkg.g_Prog_Appl_Id
416 , xla_environment_pkg.g_Prog_Id
417 , xla_environment_pkg.g_Req_Id
418 FROM (
419 SELECT xet.event_id event_id
420 , 0 line_number
421 , CASE r
422 $object_name$
423 ELSE null
424 END object_name
425 , CASE r
426 $object_type_code$
427 ELSE null
428 END object_type_code
429 , CASE r
430 $source_application_id$
431 ELSE null
432 END source_application_id
433 , $source_type_code$ source_type_code
434 , CASE r
435 $source_code$
436 ELSE null
437 END source_code
438 , CASE r
439 $source_value$
440 ELSE null
441 END source_value
445 WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
442 , $source_meaning$ source_meaning
443 FROM xla_events_gt xet $hdr_tabs$
444 ,(select rownum r from all_objects where rownum <= $source_number$ and owner = p_apps_owner)
446 AND xet.event_class_code = C_EVENT_CLASS_CODE
447 $hdr_clauses$
448 )
449 ;
450 --
451 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
452
453 trace
454 (p_msg => ''number of header sources inserted = ''||SQL%ROWCOUNT
455 ,p_level => C_LEVEL_STATEMENT
456 ,p_module => l_log_module);
457
458 END IF;
459 --
460 ';
461 --
462 --
463 --+==========================================================================+
464 --| |
465 --| Insert Line sources Template |
466 --| |
467 --+==========================================================================+
468 --
469 --
470 C_INSERT_LINE_SOURCES_EVT CONSTANT VARCHAR2(10000):='
471 --
472 INSERT INTO xla_diag_sources --line1
473 (
474 event_id
475 , ledger_id
476 , sla_ledger_id
477 , description_language
478 , object_name
479 , object_type_code
480 , line_number
481 , source_application_id
482 , source_type_code
483 , source_code
484 , source_value
485 , source_meaning
486 , created_by
487 , creation_date
488 , last_update_date
489 , last_updated_by
490 , last_update_login
491 , program_update_date
492 , program_application_id
493 , program_id
494 , request_id
495 )
496 SELECT event_id
497 , p_target_ledger_id
498 , p_sla_ledger_id
499 , p_language
500 , object_name
501 , object_type_code
502 , line_number
503 , source_application_id
504 , source_type_code
505 , source_code
506 , SUBSTR(source_value,1,1996)
507 , SUBSTR(source_meaning,1,200)
508 , xla_environment_pkg.g_Usr_Id
509 , TRUNC(SYSDATE)
510 , TRUNC(SYSDATE)
511 , xla_environment_pkg.g_Usr_Id
512 , xla_environment_pkg.g_Login_Id
513 , TRUNC(SYSDATE)
514 , xla_environment_pkg.g_Prog_Appl_Id
515 , xla_environment_pkg.g_Prog_Id
516 , xla_environment_pkg.g_Req_Id
517 FROM (
518 SELECT xet.event_id event_id
519 , $line_number$ line_number
520 , CASE r
521 $object_name$
522 ELSE null
523 END object_name
524 , CASE r
525 $object_type_code$
526 ELSE null
527 END object_type_code
528 , CASE r
529 $source_application_id$
530 ELSE null
531 END source_application_id
532 , $source_type_code$ source_type_code
533 , CASE r
534 $source_code$
535 ELSE null
536 END source_code
537 , CASE r
538 $source_value$
539 ELSE null
540 END source_value
541 , $source_meaning$ source_meaning
542 FROM xla_events_gt xet $line_tabs$
543 ,(select rownum r from all_objects where rownum <= $source_number$ and owner = p_apps_owner)
544 WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
545 AND xet.event_type_code = C_EVENT_TYPE_CODE
546 $line_clauses$
547 )
548 ;
549 --
550 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
551
552 trace
553 (p_msg => ''number of line sources inserted = ''||SQL%ROWCOUNT
554 ,p_level => C_LEVEL_STATEMENT
555 ,p_module => l_log_module);
556
557 END IF;
558 ';
559 --
560 --
561 --+==========================================================================+
562 --| |
563 --| Insert Line sources Template |
564 --| |
565 --+==========================================================================+
566 --
567 --
568 C_INSERT_LINE_SOURCES_CLASS CONSTANT VARCHAR2(10000):='
569 --
570 INSERT INTO xla_diag_sources --line2
571 (
572 event_id
573 , ledger_id
574 , sla_ledger_id
575 , description_language
576 , object_name
577 , object_type_code
578 , line_number
579 , source_application_id
580 , source_type_code
581 , source_code
582 , source_value
583 , source_meaning
584 , created_by
585 , creation_date
586 , last_update_date
587 , last_updated_by
591 , program_id
588 , last_update_login
589 , program_update_date
590 , program_application_id
592 , request_id
593 )
594 SELECT event_id
595 , p_target_ledger_id
596 , p_sla_ledger_id
597 , p_language
598 , object_name
599 , object_type_code
600 , line_number
601 , source_application_id
602 , source_type_code
603 , source_code
604 , SUBSTR(source_value,1,1996)
605 , SUBSTR(source_meaning ,1,200)
606 , xla_environment_pkg.g_Usr_Id
607 , TRUNC(SYSDATE)
608 , TRUNC(SYSDATE)
609 , xla_environment_pkg.g_Usr_Id
610 , xla_environment_pkg.g_Login_Id
611 , TRUNC(SYSDATE)
612 , xla_environment_pkg.g_Prog_Appl_Id
613 , xla_environment_pkg.g_Prog_Id
614 , xla_environment_pkg.g_Req_Id
615 FROM (
616 SELECT xet.event_id event_id
617 , $line_number$ line_number
618 , CASE r
619 $object_name$
620 ELSE null
621 END object_name
622 , CASE r
623 $object_type_code$
624 ELSE null
625 END object_type_code
626 , CASE r
627 $source_application_id$
628 ELSE null
629 END source_application_id
630 , $source_type_code$ source_type_code
631 , CASE r
632 $source_code$
633 ELSE null
634 END source_code
635 , CASE r
636 $source_value$
637 ELSE null
638 END source_value
639 , $source_meaning$ source_meaning
640 FROM xla_events_gt xet $line_tabs$
641 , (select rownum r from all_objects where rownum <= $source_number$ and owner = p_apps_owner)
642 WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
643 AND xet.event_class_code = C_EVENT_CLASS_CODE
644 $line_clauses$
645 )
646 ;
647 --
648 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
649
650 trace
651 (p_msg => ''number of line sources inserted = ''||SQL%ROWCOUNT
652 ,p_level => C_LEVEL_STATEMENT
653 ,p_module => l_log_module);
654
655 END IF;
656 ';
657 --
658 --+==========================================================================+
659 --| |
660 --| PRIVATE CONSTANT |
661 --| |
662 --+==========================================================================+
663 --
664 C_HEADER CONSTANT VARCHAR2(30) := 'HEADER' ;
665 C_MLS_HEADER CONSTANT VARCHAR2(30) := 'HEADER_MLS' ;
666 C_LINE CONSTANT VARCHAR2(30) := 'LINE' ;
667 C_BC_LINE CONSTANT VARCHAR2(30) := 'LINE_BASE_CUR';
668 C_MLS_LINE CONSTANT VARCHAR2(30) := 'LINE_MLS' ;
669 --
670 C_DATE CONSTANT VARCHAR2(30) := 'DATE';
671 C_NUMBER CONSTANT VARCHAR2(30) := 'NUMBER';
672 C_VARCHAR2 CONSTANT VARCHAR2(30) := 'VARCHAR2';
673 --
674 C_NOT_ALWAYS_POPULATED CONSTANT VARCHAR2(1) := 'N';
675 C_ALWAYS_POPULATED CONSTANT VARCHAR2(1) := 'Y';
676 --
677 C_NOT_REF_OBJ CONSTANT VARCHAR2(1) := 'N';
678 --
679 g_chr_newline CONSTANT VARCHAR2(10):= xla_environment_pkg.g_chr_newline;
680 g_application_id XLA_SUBLEDGERS.APPLICATION_ID%TYPE;
681 --
682 --
683 --+==========================================================================+
684 --| |
685 --| CALL FND_LOG trace API |
686 --| |
687 --| |
688 --| |
689 --| |
690 --| |
691 --| |
692 --| |
693 --| |
694 --| |
695 --| |
696 --| |
697 --| |
698 --| |
699 --| |
700 --| |
701 --| |
705 --=============================================================================
702 --| |
703 --+==========================================================================+
704 --
706 -- *********** Local Trace Routine **********
707 --=============================================================================
708
709 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
710 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
711 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
712 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
713 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
714 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
715
716 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
717 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_cmp_extract_pkg';
718
719 g_log_level NUMBER;
720 g_log_enabled BOOLEAN;
721
722 PROCEDURE trace
723 (p_msg IN VARCHAR2
724 ,p_level IN NUMBER
725 ,p_module IN VARCHAR2)
726 IS
727 BEGIN
728 ----------------------------------------------------------------------------
729 -- Following is for FND log.
730 ----------------------------------------------------------------------------
731 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
732 fnd_log.message(p_level, p_module);
733 ELSIF p_level >= g_log_level THEN
734 fnd_log.string(p_level, p_module, p_msg);
735 END IF;
736
737 EXCEPTION
738 WHEN xla_exceptions_pkg.application_exception THEN
739 RAISE;
740 WHEN OTHERS THEN
741 xla_exceptions_pkg.raise_message
742 (p_location => 'xla_cmp_extract_pkg.trace');
743 END trace;
744
745
746 --+==========================================================================+
747 --| |
748 --| PRIVATE PROCEDURES AND FUNCTIONS |
749 --| |
750 --| |
751 --| |
752 --+==========================================================================+
753
754 FUNCTION GenerateFromHdrTabs (
755 --
756 p_array_table_name IN xla_cmp_source_pkg.t_array_VL30
757 , p_array_parent_table_index IN xla_cmp_source_pkg.t_array_ByInt
758 , p_array_table_hash IN xla_cmp_source_pkg.t_array_VL30
759 , p_array_populated_flag IN xla_cmp_source_pkg.t_array_VL1
760 , p_array_ref_obj_flag IN xla_cmp_source_pkg.t_array_VL1
761 --
762 , p_array_h_source_index IN xla_cmp_source_pkg.t_array_ByInt
763 , p_array_h_table_index IN xla_cmp_source_pkg.t_array_ByInt
764 --
765 , p_array_h_mls_source_index IN xla_cmp_source_pkg.t_array_ByInt
766 , p_array_h_mls_table_index IN xla_cmp_source_pkg.t_array_ByInt
767 --
768 , p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
769 , p_array_lookup_type IN xla_cmp_source_pkg.t_array_VL30
770 , p_array_view_application_id IN xla_cmp_source_pkg.t_array_Num
771 )
772 RETURN VARCHAR2
773 ;
774
775 FUNCTION GenerateHdrWhereClause (
776 --
777 p_array_table_name IN xla_cmp_source_pkg.t_array_VL30
778 , p_array_parent_table_index IN xla_cmp_source_pkg.t_array_ByInt
779 , p_array_table_hash IN xla_cmp_source_pkg.t_array_VL30
780 , p_array_populated_flag IN xla_cmp_source_pkg.t_array_VL1
781 --
782 , p_array_ref_obj_flag IN xla_cmp_source_pkg.t_array_VL1
783 , p_array_join_condition IN xla_cmp_source_pkg.t_array_VL2000
784 --
785 , p_array_h_source_index IN xla_cmp_source_pkg.t_array_ByInt
786 , p_array_h_table_index IN xla_cmp_source_pkg.t_array_ByInt
787 --
788 , p_array_h_mls_source_index IN xla_cmp_source_pkg.t_array_ByInt
789 , p_array_h_mls_table_index IN xla_cmp_source_pkg.t_array_ByInt
790 --
791 , p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
792 , p_array_lookup_type IN xla_cmp_source_pkg.t_array_VL30
793 , p_array_view_application_id IN xla_cmp_source_pkg.t_array_Num
794 )
795 RETURN VARCHAR2
796 ;
797 --
798 FUNCTION GenerateFromLineTabs (
799 --
800 p_array_table_name IN xla_cmp_source_pkg.t_array_VL30
801 , p_array_parent_table_index IN xla_cmp_source_pkg.t_array_ByInt
802 , p_array_table_hash IN xla_cmp_source_pkg.t_array_VL30
803 , p_array_populated_flag IN xla_cmp_source_pkg.t_array_VL1
804 --
805 , p_array_ref_obj_flag IN xla_cmp_source_pkg.t_array_VL1
806 --
807 , p_array_l_source_index IN xla_cmp_source_pkg.t_array_ByInt
808 , p_array_l_table_index IN xla_cmp_source_pkg.t_array_ByInt
809 --
810 , p_array_l_mls_source_index IN xla_cmp_source_pkg.t_array_ByInt
811 , p_array_l_mls_table_index IN xla_cmp_source_pkg.t_array_ByInt
812 --
813 , p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
814 , p_array_lookup_type IN xla_cmp_source_pkg.t_array_VL30
815 , p_array_view_application_id IN xla_cmp_source_pkg.t_array_Num
816 )
820 --
817 RETURN VARCHAR2
818 ;
819 --
821 FUNCTION GenerateLineWhereClause (
822 --
823 p_array_table_name IN xla_cmp_source_pkg.t_array_VL30
824 , p_array_parent_table_index IN xla_cmp_source_pkg.t_array_ByInt
825 , p_array_table_hash IN xla_cmp_source_pkg.t_array_VL30
826 , p_array_populated_flag IN xla_cmp_source_pkg.t_array_VL1
827 --
828 , p_array_ref_obj_flag IN xla_cmp_source_pkg.t_array_VL1
829 , p_array_join_condition IN xla_cmp_source_pkg.t_array_VL2000
830 --
831 , p_array_l_source_index IN xla_cmp_source_pkg.t_array_ByInt
832 , p_array_l_table_index IN xla_cmp_source_pkg.t_array_ByInt
833 --
834 , p_array_l_mls_source_index IN xla_cmp_source_pkg.t_array_ByInt
835 , p_array_l_mls_table_index IN xla_cmp_source_pkg.t_array_ByInt
836 --
837 , p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
838 , p_array_lookup_type IN xla_cmp_source_pkg.t_array_VL30
839 , p_array_view_application_id IN xla_cmp_source_pkg.t_array_Num
840 )
841 RETURN VARCHAR2
842 ;
843
844 --+==========================================================================+
845 --| |
846 --| CALL THE EXTRACT INTEGRITY CHECKER |
847 --| |
848 --| |
849 --| |
850 --| |
851 --| |
852 --| |
853 --| |
854 --| |
855 --| |
856 --| |
857 --| |
858 --| |
859 --| |
860 --| |
861 --| |
862 --| |
863 --| |
864 --+==========================================================================+
865 --
866 --+==========================================================================+
867 --| |
868 --| PRIVATE PROCEDURE |
869 --| |
870 --| |
871 --| |
872 --+==========================================================================+
873 --
874 PROCEDURE InitSourceArrays (
875 p_array_evt_source_index IN OUT NOCOPY xla_cmp_source_pkg.t_array_ByInt
876 , p_array_application_id IN OUT NOCOPY xla_cmp_source_pkg.t_array_Num
877 , p_array_source_code IN OUT NOCOPY xla_cmp_source_pkg.t_array_VL30
878 , p_array_source_type_code IN OUT NOCOPY xla_cmp_source_pkg.t_array_VL1
879 , p_array_datatype_code IN OUT NOCOPY xla_cmp_source_pkg.t_array_VL1
880 , p_array_translated_flag IN OUT NOCOPY xla_cmp_source_pkg.t_array_VL1
881 )
882 IS
883 --
884 Jdx BINARY_INTEGER;
885 l_array_evt_source_index xla_cmp_source_pkg.t_array_ByInt;
886 l_array_application_id xla_cmp_source_pkg.t_array_Num;
887 l_array_source_code xla_cmp_source_pkg.t_array_VL30;
888 l_array_source_type_code xla_cmp_source_pkg.t_array_VL1;
889 l_array_datatype_code xla_cmp_source_pkg.t_array_VL1;
890 l_array_translated_flag xla_cmp_source_pkg.t_array_VL1;
891 l_log_module VARCHAR2(240);
892 --
893 BEGIN
894 --
895 IF g_log_enabled THEN
896 l_log_module := C_DEFAULT_MODULE||'.InitSourceArrays';
897 END IF;
898 --
899 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
900
901 trace
902 (p_msg => 'BEGIN of InitSourceArrays'
903 ,p_level => C_LEVEL_PROCEDURE
904 ,p_module => l_log_module);
905
906 END IF;
907 --
908 -- init PL/SQL arrays
909 --
910 Jdx := 1;
911 --
912 FOR Idx IN p_array_evt_source_index.FIRST .. p_array_evt_source_index.LAST LOOP
913
914 IF p_array_evt_source_index.EXISTS(Idx) AND
915 p_array_evt_source_index(Idx) IS NOT NULL THEN
916 --
917 --
918 l_array_evt_source_index(Jdx) := Idx;
919 l_array_source_code(Jdx) := p_array_source_code(p_array_evt_source_index(Idx));
920 l_array_source_type_code(Jdx) := p_array_source_type_code(p_array_evt_source_index(Idx));
921 l_array_application_id(Jdx) := p_array_application_id(p_array_evt_source_index(Idx));
925 Jdx := Jdx + 1;
922 l_array_datatype_code(Jdx) := p_array_datatype_code(p_array_evt_source_index(Idx));
923 l_array_translated_flag(Jdx) := p_array_translated_flag(p_array_evt_source_index(Idx));
924 --
926 --
927 END IF;
928
929 END LOOP
930 ;
931 --
932 p_array_evt_source_index := l_array_evt_source_index;
933 p_array_application_id := l_array_application_id;
934 p_array_source_code := l_array_source_code;
935 p_array_source_type_code := l_array_source_type_code;
936 p_array_datatype_code := l_array_datatype_code;
937 p_array_translated_flag := l_array_translated_flag;
938 --
939 --
940 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
941
942 trace
943 (p_msg => 'END of InitSourceArrays'
944 ,p_level => C_LEVEL_PROCEDURE
945 ,p_module => l_log_module);
946
947 END IF;
948 --
949 EXCEPTION
950 WHEN xla_exceptions_pkg.application_exception THEN
951 RAISE;
952 WHEN OTHERS THEN
953 xla_exceptions_pkg.raise_message
954 (p_location => 'xla_cmp_extract_pkg.InitSourceArrays ');
955 END InitSourceArrays;
956 --
957 --+==========================================================================+
958 --| |
959 --| PRIVATE PROCEDURE |
960 --| |
961 --| |
962 --| |
963 --+==========================================================================+
964 --
965 PROCEDURE FlushGtTable (
966 p_application_id IN NUMBER
967 , p_entity_code IN VARCHAR2
968 , p_event_class_code IN VARCHAR2
969 )
970 IS
971 l_statement VARCHAR2(4000);
972 l_log_module VARCHAR2(240);
973 BEGIN
974 --
975 IF g_log_enabled THEN
976 l_log_module := C_DEFAULT_MODULE||'.FlushGtTable';
977 END IF;
978 --
979 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
980
981 trace
982 (p_msg => 'BEGIN of FlushGtTable'
983 ,p_level => C_LEVEL_PROCEDURE
984 ,p_module => l_log_module);
985
986 END IF;
987
988 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
989
990 trace(p_msg => 'SQL - DELETE FROM xla_evt_class_sources_gt '
991 ,p_level => C_LEVEL_STATEMENT
992 ,p_module => l_log_module);
993 trace(p_msg => 'p_application_id = ' ||p_application_id
994 ,p_level => C_LEVEL_STATEMENT
995 ,p_module => l_log_module);
996
997 trace(p_msg => 'p_entity_code = ' ||p_entity_code
998 ,p_level => C_LEVEL_STATEMENT
999 ,p_module => l_log_module);
1000
1001 trace(p_msg => 'p_event_class_code = ' ||p_event_class_code
1002 ,p_level => C_LEVEL_STATEMENT
1003 ,p_module => l_log_module);
1004
1005 END IF;
1006
1007 DELETE FROM xla_evt_class_sources_gt gt
1008 WHERE gt.application_id = p_application_id
1009 AND gt.entity_code = p_entity_code
1010 AND gt.event_class_code = p_event_class_code
1011 ;
1012 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1013
1014 trace
1015 (p_msg => '# rows deleted from xla_evt_class_sources_gt = '||SQL%ROWCOUNT
1016 ,p_level => C_LEVEL_STATEMENT
1017 ,p_module => l_log_module);
1018
1019 END IF;
1020 --
1021 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1022
1023 trace
1024 (p_msg => 'END of FlushGtTable'
1025 ,p_level => C_LEVEL_PROCEDURE
1026 ,p_module => l_log_module);
1027
1028 END IF;
1029 --
1030 END FlushGtTable;
1031 --
1032 --
1033 --+==========================================================================+
1034 --| |
1035 --| PRIVATE PROCEDURE |
1036 --| |
1037 --| |
1038 --| |
1039 --+==========================================================================+
1040 --
1041 PROCEDURE InsertSourcesIntoGtTable (
1042 p_application_id IN NUMBER
1043 , p_entity_code IN VARCHAR2
1044 , p_event_class_code IN VARCHAR2
1045 , p_array_evt_source_index IN xla_cmp_source_pkg.t_array_ByInt
1046 , p_array_application_id IN xla_cmp_source_pkg.t_array_Num
1047 , p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
1048 , p_array_source_type_code IN xla_cmp_source_pkg.t_array_VL1
1049 , p_array_datatype_code IN xla_cmp_source_pkg.t_array_VL1
1050 , p_array_translated_flag IN xla_cmp_source_pkg.t_array_VL1
1051 )
1052 IS
1053 --
1054 l_log_module VARCHAR2(240);
1055 BEGIN
1056 --
1057 IF g_log_enabled THEN
1061 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1058 l_log_module := C_DEFAULT_MODULE||'.InsertSourcesIntoGtTable';
1059 END IF;
1060 --
1062
1063 trace
1064 (p_msg => 'BEGIN of InsertSourcesIntoGtTable'
1065 ,p_level => C_LEVEL_PROCEDURE
1066 ,p_module => l_log_module);
1067
1068 END IF;
1069 --
1070 -- flush temporary table
1071 --
1072 FlushGtTable(
1073 p_application_id
1074 , p_entity_code
1075 , p_event_class_code
1076 );
1077 --
1078 -- insert sources in xla_evt_class_sources_gt temporary table
1079 --
1080 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1081
1082 trace
1083 (p_msg => 'SQL- Insert Into xla_evt_class_sources_gt '
1084 ,p_level => C_LEVEL_STATEMENT
1085 ,p_module => l_log_module);
1086
1087 END IF;
1088
1089 --
1090 FORALL Idx IN p_array_evt_source_index.FIRST .. p_array_evt_source_index.LAST
1091
1092 INSERT INTO xla_evt_class_sources_gt
1093 (
1094 application_id
1095 , entity_code
1096 , event_class_code
1097 , source_application_id
1098 , source_code
1099 , source_hash_id
1100 , source_datatype_code
1101 , source_level_code
1102 )
1103
1104 SELECT
1105 p_application_id
1106 , p_entity_code
1107 , p_event_class_code
1108 , xes.source_application_id
1109 , p_array_source_code(Idx)
1110 , p_array_evt_source_index(Idx)
1111
1112 , CASE p_array_datatype_code(Idx)
1113
1114 WHEN 'C' THEN C_VARCHAR2
1115
1116 WHEN 'D' THEN C_DATE
1117
1118 ELSE C_NUMBER
1119
1120 END
1121
1122 , CASE xes.level_code
1123
1124 WHEN 'C' THEN C_BC_LINE
1125 -- Added an extra decode for language column (Dimple)
1126 WHEN 'L' THEN DECODE(p_array_source_code(Idx),'LANGUAGE',C_MLS_LINE,
1127 DECODE(p_array_translated_flag(Idx)
1128 ,'Y',C_MLS_LINE
1129 ,C_LINE))
1130
1131 WHEN 'H' THEN DECODE(p_array_source_code(Idx),'LANGUAGE',C_MLS_HEADER,
1132 DECODE(p_array_translated_flag(Idx)
1133 ,'Y', C_MLS_HEADER
1134 , C_HEADER))
1135
1136 END
1137 --
1138 FROM xla_event_sources xes
1139 WHERE xes.application_id = p_application_id
1140 AND xes.entity_code = p_entity_code
1141 AND xes.event_class_code = p_event_class_code
1142 AND xes.source_code = p_array_source_code(Idx)
1143 AND xes.source_type_code = p_array_source_type_code(Idx)
1144 AND xes.source_application_id = p_array_application_id(Idx)
1145 AND xes.source_type_code = 'S'
1146 AND xes.active_flag = 'Y'
1147 -- added not exists to prevent inserting sources that are already there in GT table (Dimple)
1148 AND not exists (SELECT 'x'
1149 FROM xla_evt_class_sources_gt gt
1150 WHERE gt.application_id = xes.application_id
1151 AND gt.entity_code = xes.entity_code
1152 AND gt.event_class_code = xes.event_class_code
1153 AND gt.source_application_id = xes.source_application_id
1154 AND gt.source_code = xes.source_code)
1155 ;
1156 --
1157 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1158
1159 trace
1160 (p_msg => '# rows inserted into xla_evt_class_sources_gt = '||SQL%ROWCOUNT
1161 ,p_level => C_LEVEL_STATEMENT
1162 ,p_module => l_log_module);
1163
1164 END IF;
1165 --
1166 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1167
1168 trace
1169 (p_msg => 'END of InsertSourcesIntoGtTable'
1170 ,p_level => C_LEVEL_PROCEDURE
1171 ,p_module => l_log_module);
1172
1173 END IF;
1174 --
1175 EXCEPTION
1176 WHEN xla_exceptions_pkg.application_exception THEN
1177 RAISE;
1178 WHEN OTHERS THEN
1179 xla_exceptions_pkg.raise_message
1180 (p_location => 'xla_cmp_extract_pkg.InsertSourcesIntoGtTable ');
1181 END InsertSourcesIntoGtTable;
1182 --
1183 --
1184 --+==========================================================================+
1185 --| |
1186 --| PRIVATE PROCEDURE |
1187 --| |
1188 --| |
1189 --| |
1190 --+==========================================================================+
1191 --
1192 PROCEDURE GetSourceLevels (
1193 p_application_id IN NUMBER
1194 , p_entity_code IN VARCHAR2
1195 , p_event_class_code IN VARCHAR2
1196 , p_array_evt_source_Level OUT NOCOPY xla_cmp_source_pkg.t_array_VL1
1197 )
1198 IS
1199 --
1200 l_array_evt_source_index xla_cmp_source_pkg.t_array_ByInt;
1204 --
1201 l_array_evt_source_Level xla_cmp_source_pkg.t_array_VL1;
1202 --
1203 l_log_module VARCHAR2(240);
1205 BEGIN
1206 --
1207 IF g_log_enabled THEN
1208 l_log_module := C_DEFAULT_MODULE||'.GetSourceLevels';
1209 END IF;
1210 --
1211 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1212
1213 trace
1214 (p_msg => 'BEGIN of GetSourceLevels'
1215 ,p_level => C_LEVEL_PROCEDURE
1216 ,p_module => l_log_module);
1217
1218 END IF;
1219 --
1220 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1221
1222 trace
1223 (p_msg => 'SQL- SELECT from xla_evt_class_sources_gt '
1224 ,p_level => C_LEVEL_STATEMENT
1225 ,p_module => l_log_module);
1226
1227 END IF;
1228
1229 --
1230 SELECT gt.source_hash_id
1231 , CASE gt.source_level_code
1232 WHEN C_HEADER THEN 'H'
1233 WHEN C_MLS_HEADER THEN 'H'
1234 ELSE 'L'
1235 END
1236 BULK COLLECT INTO
1237 l_array_evt_source_index
1238 , l_array_evt_source_Level
1239 FROM xla_evt_class_sources_gt gt
1240 WHERE gt.application_id = p_application_id
1241 AND gt.entity_code = p_entity_code
1242 AND gt.event_class_code = p_event_class_code
1243 ;
1244 --
1245 --
1246 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1247
1248 trace
1249 (p_msg => '# rows inserted into xla_evt_class_sources_gt = '||SQL%ROWCOUNT
1250 ,p_level => C_LEVEL_STATEMENT
1251 ,p_module => l_log_module);
1252
1253 END IF;
1254
1255 IF l_array_evt_source_index.COUNT > 0 THEN
1256 --
1257 FOR Idx IN l_array_evt_source_index.FIRST .. l_array_evt_source_index.LAST LOOP
1258 --
1259 IF l_array_evt_source_index.EXISTS(Idx) AND
1260 l_array_evt_source_index(Idx) IS NOT NULL
1261 THEN
1262 --
1263 p_array_evt_source_Level(l_array_evt_source_index(Idx)) := l_array_evt_source_Level(Idx);
1264 --
1265 END IF;
1266 --
1267 END LOOP;
1268 END IF;
1269 --
1270 --
1271 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1272
1273 trace
1274 (p_msg => 'END of GetSourceLevels'
1275 ,p_level => C_LEVEL_PROCEDURE
1276 ,p_module => l_log_module);
1277
1278 END IF;
1279 --
1280 --
1281 EXCEPTION
1282 WHEN xla_exceptions_pkg.application_exception THEN
1283 RAISE;
1284 WHEN OTHERS THEN
1285 xla_exceptions_pkg.raise_message
1286 (p_location => 'xla_cmp_extract_pkg.GetSourceLevels ');
1287 END GetSourceLevels;
1288 --
1289 --
1290 --+==========================================================================+
1291 --| |
1292 --| PRIVATE PROCEDURE |
1293 --| |
1294 --| |
1295 --| |
1296 --+==========================================================================+
1297 --
1298 PROCEDURE GetExtractObjects (
1299 p_application_id IN NUMBER
1300 , p_entity_code IN VARCHAR2
1301 , p_event_class_code IN VARCHAR2
1302 , p_array_object_name OUT NOCOPY xla_cmp_source_pkg.t_array_VL30
1303 , p_array_parent_object_index OUT NOCOPY xla_cmp_source_pkg.t_array_ByInt
1304 , p_array_object_type OUT NOCOPY xla_cmp_source_pkg.t_array_VL30
1305 , p_array_object_hash_id OUT NOCOPY xla_cmp_source_pkg.t_array_VL30
1306 , p_array_populated_flag OUT NOCOPY xla_cmp_source_pkg.t_array_VL1
1307 , p_array_ref_obj_flag OUT NOCOPY xla_cmp_source_pkg.t_array_VL1
1308 , p_array_join_condition OUT NOCOPY xla_cmp_Source_pkg.t_array_VL2000
1309 )
1310 IS
1311 --
1312 l_array_object_name xla_cmp_source_pkg.t_array_VL30;
1313 l_array_parent_name xla_cmp_source_pkg.t_array_VL30;
1314 l_array_object_type xla_cmp_source_pkg.t_array_VL30;
1315 l_array_object_hash_id xla_cmp_source_pkg.t_array_VL30;
1316 l_array_populated_flag xla_cmp_source_pkg.t_array_VL1;
1317 --
1318 l_array_ref_obj_flag xla_cmp_source_pkg.t_array_VL1;
1319 l_array_join_condition xla_cmp_source_pkg.t_array_VL2000;
1320 l_array_parent_object_index xla_cmp_source_pkg.t_array_ByInt;
1321 TYPE t_array_by_VL30 IS TABLE OF NUMBER INDEX BY VARCHAR2(30);
1322 l_array_table_index t_array_by_VL30;
1323 --
1324 CURSOR table_cur( p_application_id NUMBER
1325 , p_entity_code VARCHAR2
1326 , p_event_class_code VARCHAR2
1327 )
1328 IS
1329 SELECT
1330 gt.extract_object_name
1331 , gt.extract_object_type_code
1332 , nvl(gt.always_populated_flag,C_NOT_ALWAYS_POPULATED)
1333 , nvl(gt.reference_object_flag,C_NOT_REF_OBJ)
1334 , gt.join_condition
1335 , nvl(ro.linked_to_ref_obj_name, ro.object_name)
1336 FROM xla_evt_class_sources_gt gt
1337 , xla_reference_objects ro
1338 WHERE gt.application_id = p_application_id
1339 AND gt.entity_code = p_entity_code
1343 AND ro.event_class_code (+)= p_event_class_code
1340 AND gt.event_class_code = p_event_class_code
1341 AND ro.application_id (+)= p_application_id
1342 AND ro.entity_code (+)= p_entity_code
1344 AND ro.reference_object_name (+)= gt.extract_object_name
1345 UNION
1346 SELECT ro1.reference_object_name
1347 , gt.extract_object_type_code
1348 , nvl(ro1.always_populated_flag,C_NOT_ALWAYS_POPULATED)
1349 , 'Y'
1350 , ro1.join_condition
1351 , nvl(ro1.linked_to_ref_obj_name, ro1.object_name)
1352 FROM xla_evt_class_sources_gt gt
1353 , xla_reference_objects ro
1354 , xla_reference_objects ro1
1355 WHERE gt.application_id = p_application_id
1356 AND gt.entity_code = p_entity_code
1357 AND gt.event_class_code = p_event_class_code
1358 AND ro.application_id = p_application_id
1359 AND ro.entity_code = p_entity_code
1360 AND ro.event_class_code = p_event_class_code
1361 AND ro.reference_object_name = gt.extract_object_name
1362 AND ro1.application_id = p_application_id
1363 AND ro1.entity_code = p_entity_code
1364 AND ro1.event_class_code = p_event_class_code
1365 AND ro1.reference_object_name = ro.linked_to_ref_obj_name
1366 UNION
1367 SELECT eo.object_name
1368 ,gt.extract_object_type_code
1369 ,nvl(eo.always_populated_flag,C_NOT_ALWAYS_POPULATED)
1370 ,'N'
1371 ,null
1372 ,null
1373 FROM xla_evt_class_sources_gt gt
1374 , xla_reference_objects ro
1375 , xla_extract_objects eo
1376 WHERE gt.application_id = p_application_id
1377 AND gt.entity_code = p_entity_code
1378 AND gt.event_class_code = p_event_class_code
1379 AND ro.application_id = p_application_id
1380 AND ro.entity_code = p_entity_code
1381 AND ro.event_class_code = p_event_class_code
1382 AND ro.reference_object_name = gt.extract_object_name
1383 AND eo.application_id = p_application_id
1384 AND eo.entity_code = p_entity_code
1385 AND eo.event_class_code = p_event_class_code
1386 AND eo.object_name = ro.object_name
1387 ;
1388 --
1389 l_log_module VARCHAR2(240);
1390 BEGIN
1391 --
1392 IF g_log_enabled THEN
1393 l_log_module := C_DEFAULT_MODULE||'.GetExtractObjects';
1394 END IF;
1395 --
1396 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1397
1398 trace
1399 (p_msg => 'BEGIN of GetExtractObjects'
1400 ,p_level => C_LEVEL_PROCEDURE
1401 ,p_module => l_log_module);
1402
1403 END IF;
1404 --
1405 --
1406 OPEN table_cur( p_application_id => p_application_id
1407 , p_entity_code => p_entity_code
1408 , p_event_class_code => p_event_class_code
1409 );
1410 --
1411 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1412
1413 trace
1414 (p_msg => 'SQL - Fetch xla_evt_class_sources_gt '
1415 ,p_level => C_LEVEL_STATEMENT
1416 ,p_module => l_log_module);
1417
1418 END IF;
1419 --
1420
1421 FETCH table_cur BULK COLLECT INTO l_array_object_name
1422 , l_array_object_type
1423 , l_array_populated_flag
1424 , l_array_ref_obj_flag
1425 , l_array_join_condition
1426 , l_array_parent_name
1427 ;
1428 --
1429
1430 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1431
1432 trace
1433 (p_msg => '# rows selected from xla_evt_class_sources_gt = '||table_cur%ROWCOUNT
1434 ,p_level => C_LEVEL_STATEMENT
1435 ,p_module => l_log_module);
1436
1437 END IF;
1438 --
1439 CLOSE table_cur;
1440
1441
1442 IF l_array_object_name.COUNT > 0 THEN
1443
1444 FOR Idx IN l_array_object_name.FIRST .. l_array_object_name.LAST LOOP
1445
1446 IF l_array_object_name.EXISTS(Idx) AND
1447 l_array_object_name(Idx) IS NOT NULL AND
1448 l_array_object_type(Idx) IS NOT NULL THEN
1449
1450 l_array_table_index(l_array_object_name(Idx)) := Idx;
1451 CASE l_array_object_type(Idx)
1452
1453 WHEN C_HEADER THEN l_array_object_hash_id(Idx) := CONCAT('h',Idx);
1454
1455 WHEN C_MLS_HEADER THEN l_array_object_hash_id(Idx) := CONCAT('hmls',Idx);
1456
1457 WHEN C_LINE THEN l_array_object_hash_id(Idx) := CONCAT('l',Idx);
1458
1459 WHEN C_BC_LINE THEN l_array_object_hash_id(Idx) := CONCAT('lbc',Idx);
1460
1461 WHEN C_MLS_LINE THEN l_array_object_hash_id(Idx) := CONCAT('lmls',Idx);
1462
1463 ELSE
1464 null;
1465 END CASE;
1466
1467 END IF;
1468
1469 END LOOP;
1470
1471 FOR Idx IN l_array_object_name.FIRST .. l_array_object_name.LAST LOOP
1472 IF l_array_parent_name.EXISTS(Idx) AND
1473 l_array_parent_name(Idx) IS NOT NULL AND
1474 l_array_table_index.EXISTS(l_array_parent_name(Idx)) AND
1475 l_array_table_index(l_array_parent_name(Idx)) IS NOT NULL THEN
1476 l_array_parent_object_index(Idx):= l_array_table_index(l_array_parent_name(Idx));
1477 END IF;
1478
1482 --
1479 END LOOP;
1480
1481 END IF;
1483 p_array_object_name := l_array_object_name;
1484 p_array_object_type := l_array_object_type;
1485 p_array_object_hash_id := l_array_object_hash_id;
1486 p_array_populated_flag := l_array_populated_flag;
1487 p_array_ref_obj_flag := l_array_ref_obj_flag;
1488 p_array_join_condition := l_array_join_condition;
1489 p_array_parent_object_index := l_array_parent_object_index;
1490 --
1491 --
1492 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1493
1494 trace
1495 (p_msg => 'END of GetExtractObjects'
1496 ,p_level => C_LEVEL_PROCEDURE
1497 ,p_module => l_log_module);
1498
1499 END IF;
1500 --
1501 --
1502 EXCEPTION
1503 WHEN xla_exceptions_pkg.application_exception THEN
1504 RAISE;
1505 WHEN OTHERS THEN
1506 xla_exceptions_pkg.raise_message
1507 (p_location => 'xla_cmp_extract_pkg.GetExtractObjects ');
1508 END GetExtractObjects;
1509 --
1510 --
1511 --+==========================================================================+
1512 --| |
1513 --| PRIVATE PROCEDURE |
1514 --| |
1515 --| |
1516 --| |
1517 --+==========================================================================+
1518 --
1519 FUNCTION GetObjectIndex (
1520 p_object_name IN VARCHAR2
1521 ,p_array_object_name IN xla_cmp_source_pkg.t_array_VL30
1522 )
1523 RETURN BINARY_INTEGER
1524 IS
1525 objectIndex BINARY_INTEGER:=NULL;
1526 l_log_module VARCHAR2(240);
1527 BEGIN
1528 --
1529 IF p_array_object_name.COUNT > 0 THEN
1530 --
1531 FOR Idx IN p_array_object_name.FIRST .. p_array_object_name.LAST LOOP
1532 --
1533 IF p_array_object_name.EXISTS(Idx) AND
1534 p_array_object_name(Idx) = p_object_name THEN
1535 --
1536 objectIndex := Idx;
1537 --
1538 END IF;
1539 --
1540 END LOOP;
1541 --
1542 END IF;
1543 --
1544 RETURN objectIndex;
1545 END GetObjectIndex;
1546 --
1547 --+==========================================================================+
1548 --| |
1549 --| PRIVATE PROCEDURE |
1550 --| |
1551 --| |
1552 --| |
1553 --+==========================================================================+
1554 --
1555 PROCEDURE GetSourcesWithExtractObject (
1556 p_application_id IN NUMBER
1557 , p_entity_code IN VARCHAR2
1558 , p_event_class_code IN VARCHAR2
1559 , p_array_object_name IN xla_cmp_source_pkg.t_array_VL30
1560 --
1561 , p_array_h_source_index OUT NOCOPY xla_cmp_source_pkg.t_array_ByInt
1562 , p_array_h_table_index OUT NOCOPY xla_cmp_source_pkg.t_array_ByInt
1563 --
1564 , p_array_h_mls_source_index OUT NOCOPY xla_cmp_source_pkg.t_array_ByInt
1565 , p_array_h_mls_table_index OUT NOCOPY xla_cmp_source_pkg.t_array_ByInt
1566 --
1567 , p_array_l_source_index OUT NOCOPY xla_cmp_source_pkg.t_array_ByInt
1568 , p_array_l_table_index OUT NOCOPY xla_cmp_source_pkg.t_array_ByInt
1569 --
1570 , p_array_l_mls_source_index OUT NOCOPY xla_cmp_source_pkg.t_array_ByInt
1571 , p_array_l_mls_table_index OUT NOCOPY xla_cmp_source_pkg.t_array_ByInt
1572 --
1573 )
1574 IS
1575 --
1576 --
1577 l_array_source_hash_id xla_cmp_source_pkg.t_array_ByInt;
1578 l_array_object_name xla_cmp_source_pkg.t_array_VL30;
1579 l_array_object_type xla_cmp_source_pkg.t_array_VL30;
1580 l_log_module VARCHAR2(240);
1581 --
1582 BEGIN
1583 --
1584 IF g_log_enabled THEN
1585 l_log_module := C_DEFAULT_MODULE||'.GetSourcesWithExtractObject';
1586 END IF;
1587 --
1588 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1589
1590 trace
1591 (p_msg => 'BEGIN of GetSourcesWithExtractObject'
1592 ,p_level => C_LEVEL_PROCEDURE
1593 ,p_module => l_log_module);
1594
1595 END IF;
1596 --
1597 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1598
1599 trace
1600 (p_msg => 'SQL- SELECT from xla_evt_class_sources_gt'
1601 ,p_level => C_LEVEL_STATEMENT
1602 ,p_module => l_log_module);
1603
1604 END IF;
1605 --
1606 --
1607 SELECT gt.source_hash_id
1608 , gt.extract_object_name
1609 , gt.extract_object_type_code
1610
1611 BULK COLLECT INTO
1612 l_array_source_hash_id
1613 , l_array_object_name
1614 , l_array_object_type
1615
1616 FROM xla_evt_class_sources_gt gt
1617 WHERE gt.application_id = p_application_id
1621 --
1618 AND gt.entity_code = p_entity_code
1619 AND gt.event_class_code = p_event_class_code
1620 ;
1622 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1623
1624 trace
1625 (p_msg => '# rows selected from xla_evt_class_sources_gt = '||SQL%ROWCOUNT
1626 ,p_level => C_LEVEL_STATEMENT
1627 ,p_module => l_log_module);
1628
1629 END IF;
1630
1631 --
1632 IF l_array_source_hash_id.COUNT > 0 THEN
1633
1634 FOR Idx IN l_array_source_hash_id.FIRST .. l_array_source_hash_id.LAST LOOP
1635
1636 CASE l_array_object_type(Idx)
1637
1638 WHEN C_HEADER THEN
1639
1640 p_array_h_source_index(l_array_source_hash_id(Idx)) := l_array_source_hash_id(Idx);
1641 p_array_h_table_index(l_array_source_hash_id(Idx)) := GetObjectIndex (
1642 l_array_object_name(Idx)
1643 , p_array_object_name
1644 );
1645 WHEN C_MLS_HEADER THEN
1646
1647 p_array_h_mls_source_index(l_array_source_hash_id(Idx)) := l_array_source_hash_id(Idx);
1648 p_array_h_mls_table_index(l_array_source_hash_id(Idx)) := GetObjectIndex (
1649 l_array_object_name(Idx)
1650 , p_array_object_name
1651 );
1652
1653 WHEN C_LINE THEN
1654
1655 p_array_l_source_index(l_array_source_hash_id(Idx)) := l_array_source_hash_id(Idx);
1656 p_array_l_table_index(l_array_source_hash_id(Idx)) := GetObjectIndex (
1657 l_array_object_name(Idx)
1658 , p_array_object_name
1659 );
1660
1661
1662 WHEN C_MLS_LINE THEN
1663
1664 p_array_l_mls_source_index(l_array_source_hash_id(Idx)) := l_array_source_hash_id(Idx);
1665 p_array_l_mls_table_index(l_array_source_hash_id(Idx)) := GetObjectIndex (
1666 l_array_object_name(Idx)
1667 , p_array_object_name
1668 );
1669
1670 ELSE
1671 null;
1672 END CASE;
1673
1674 END LOOP;
1675
1676 END IF;
1677
1678 --
1679 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1680
1681 trace
1682 (p_msg => 'END of GetSourcesWithExtractObject'
1683 ,p_level => C_LEVEL_PROCEDURE
1684 ,p_module => l_log_module);
1685
1686 END IF;
1687 --
1688 --
1689 EXCEPTION
1690 WHEN xla_exceptions_pkg.application_exception THEN
1691 RAISE;
1692 WHEN OTHERS THEN
1693 xla_exceptions_pkg.raise_message
1694 (p_location => 'xla_cmp_extract_pkg.GetSourcesWithExtractObject ');
1695 END GetSourcesWithExtractObject;
1696
1697 --
1698 --
1699 --+==========================================================================+
1700 --| |
1701 --| PRIVATE FUNCTION |
1702 --| |
1703 --| Add Oracle Join Operator (+) to join conditions |
1704 --| |
1705 --+==========================================================================+
1706 --
1707 FUNCTION AddOuterJoinOps (
1708 p_join_condition IN VARCHAR2
1709 ,p_ref_obj_name IN VARCHAR2)
1710 RETURN VARCHAR2
1711 IS
1712
1713 l_out_where_clause VARCHAR2(2000);
1714 l_in_str_lower VARCHAR2(2000);
1715 l_multiple_flag VARCHAR2(1);
1716
1717 l_col_start_pos PLS_INTEGER;
1718 l_and_pos PLS_INTEGER;
1719 l_start_pos PLS_INTEGER;
1720 i PLS_INTEGER;
1721 l_array_join_condition xla_cmp_source_pkg.t_array_VL2000;
1722 l_relation VARCHAR2(2);
1723
1724 l_log_module VARCHAR2(240);
1725
1726 BEGIN
1727 --
1728 IF g_log_enabled THEN
1729 l_log_module := C_DEFAULT_MODULE||'.AddOuterJoinOps';
1730 END IF;
1731 --
1732 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1733
1734 trace
1735 (p_msg => 'BEGIN of AddOuterJoinOps'
1736 ,p_level => C_LEVEL_PROCEDURE
1737 ,p_module => l_log_module);
1738
1739 END IF;
1740 --
1741
1742 l_multiple_flag := 'N';
1743 l_in_str_lower := lower(p_join_condition);
1744
1745 --
1746 -- Assign each join condition into l_array_join_condition (i)
1747 -- For example, for the join contion
1748 -- 'ap_inv_dist_ext_s.col1 = pa_projects.col1 AND
1749 -- ap_inv_dist_ext_s.col2 = pa_projects.col2'
1750 --
1754
1751 -- l_array_join_condition (1) stores 'ap_inv_dist_ext_s.col1 = pa_projects.col1'
1752 -- l_array_join_condition (2) stores 'ap_inv_dist_ext_s.col2 = pa_projects.col2'
1753 --
1755 --
1756 -- Check if ' and ' is found in the input strings, that is. Then assume that
1757 -- there are multiple join conditions
1758 --
1759 IF INSTRB(l_in_str_lower, ' and ') > 0 THEN
1760
1761 i := 1;
1762 l_and_pos := 1;
1763 l_start_pos := 1; -- Starting Posistion to search ' AND '
1764 l_multiple_flag := 'Y';
1765
1766
1767 WHILE l_and_pos > 0 AND i < 20 LOOP -- i <20 is to avoid inf loop
1768
1769 l_and_pos := INSTRB(l_in_str_lower, ' and ', l_start_pos);
1770
1771 --
1772 -- ' AND ' is found. l_and_pos is 0 if ' and ' is not found.
1773 --
1774 IF l_and_pos <> 0 THEN
1775 -- bug6487259 added - l_start_pos + 1
1776 l_array_join_condition(i) := SUBSTRB(p_join_condition,l_start_pos, l_and_pos - l_start_pos + 1);
1777
1778 l_start_pos := l_and_pos + 5; -- Move starting posting for the next ' AND '
1779
1780 --
1781 -- ' AND ' is not found. Assume this is the last join condition
1782 --
1783 ELSE -- the last join condition
1784 -- bug6487259 added - l_start_pos + 1
1785 l_array_join_condition(i) := SUBSTRB(p_join_condition,l_start_pos,lengthb(p_join_condition) - l_start_pos + 1);
1786
1787 END IF;
1788
1789 -- Debug
1790 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1791
1792 trace
1793 (p_msg => 'Before adding outer join operators - ' ||
1794 'l_array_join_condition(' || i || ') = ' ||
1795 l_array_join_condition (i)
1796 ,p_level => C_LEVEL_PROCEDURE
1797 ,p_module => l_log_module);
1798
1799 END IF;
1800
1801 i := i+1;
1802
1803 END LOOP;
1804
1805 ELSE -- Single join condition
1806
1807 l_multiple_flag := 'N';
1808
1809 END IF;
1810
1811
1812 --
1813 -- Add (+) to the join conditions
1814 --
1815
1816 IF l_multiple_flag = 'N' THEN
1817
1818 --
1819 -- Add the outer join operator to reference objects
1820 -- Make no change to trx object conditions
1821 --
1822 IF INSTRB(LOWER(p_join_condition),LOWER(p_ref_obj_name)||'.') > 0 THEN
1823
1824 --
1825 -- Evaluate two char relations '>=' ,'<>' first.
1826 --
1827 IF INSTRB(p_join_condition,'>=') > 0 THEN
1828 l_relation := '>=';
1829 ELSIF INSTRB(p_join_condition,'=<') > 0 THEN
1830 l_relation := '=<';
1831 ELSIF INSTRB(p_join_condition,'<>') > 0 THEN
1832 l_relation := '<>';
1833 ELSIF INSTRB(p_join_condition,'!=') > 0 THEN
1834 l_relation := '!=';
1835 ELSIF INSTRB(p_join_condition,'>') > 0 THEN
1836 l_relation := '>';
1837 ELSIF INSTRB(p_join_condition,'<') > 0 THEN
1838 l_relation := '<';
1839 ELSIF INSTRB(p_join_condition,'=') > 0 THEN
1840 l_relation := '=';
1841 END IF;
1842
1843 --
1844 -- Reference Object is located right to the equal sign
1845 -- e.g. ap_inv_dist_ext_s.project_id = pa_projects.project_id
1846 --
1847 IF INSTRB(l_in_str_lower,LOWER(p_ref_obj_name)) > INSTRB(l_in_str_lower,l_relation)
1848 THEN
1849 l_out_where_clause := p_join_condition || ' (+) ';
1850
1851 --
1852 -- Reference Object is located left to the equal sign
1853 -- e.g. pa_projects.project_id = ap_inv_dist_ext_s.project_id
1854 --
1855 ELSE
1856 l_out_where_clause := REPLACE(p_join_condition, l_relation, ' (+) ' || l_relation);
1857 END IF;
1858
1859 END IF;
1860
1861 ELSE -- l_multipel_flag = 'Y'
1862
1863 IF l_array_join_condition.COUNT >= 1 THEN
1864 FOR i IN l_array_join_condition.FIRST .. l_array_join_condition.LAST LOOP
1865
1866 --
1867 -- Add the outer join operator to reference objects
1868 --
1869 IF INSTRB(LOWER(l_array_join_condition(i)),LOWER(p_ref_obj_name)||'.') > 0 THEN
1870
1871 --
1872 -- Evaluate two char relations '>=' ,'<>' first.
1873 --
1874 IF INSTRB(l_array_join_condition(i),'>=') > 0 THEN
1875 l_relation := '>=';
1876 ELSIF INSTRB(l_array_join_condition(i),'=<') > 0 THEN
1877 l_relation := '=<';
1878 ELSIF INSTRB(l_array_join_condition(i),'<>') > 0 THEN
1879 l_relation := '<>';
1880 ELSIF INSTRB(l_array_join_condition(i),'!=') > 0 THEN
1881 l_relation := '!=';
1882 ELSIF INSTRB(l_array_join_condition(i),'>') > 0 THEN
1883 l_relation := '>';
1884 ELSIF INSTRB(l_array_join_condition(i),'<') > 0 THEN
1885 l_relation := '<';
1886 ELSIF INSTRB(l_array_join_condition(i),'=') > 0 THEN
1887 l_relation := '=';
1888 END IF;
1889
1890 --
1891 -- Reference Object is located right to the relation '=', '>', or '<'
1892 -- e.g. ap_inv_dist_ext_s.project_id = pa_projects.project_id
1893 -- e.g. 0 < pa_projects.project_id
1897 THEN
1894 --
1895 IF INSTRB(LOWER(l_array_join_condition(i)),LOWER(p_ref_obj_name)) >
1896 INSTRB(LOWER(l_array_join_condition(i)),l_relation)
1898 l_array_join_condition(i) := l_array_join_condition(i) || ' (+) ';
1899
1900 --
1901 -- Reference Object is located left to the relation '=', '>', or '<'
1902 -- e.g. pa_projects.project_id = ap_inv_dist_ext_s.project_id
1903 -- e.g. pa_projects.project_id > 0
1904 ELSE
1905 l_array_join_condition(i) :=
1906 REPLACE(l_array_join_condition(i),l_relation,' (+) ' || l_relation);
1907 END IF;
1908
1909 END IF;
1910
1911 l_out_where_clause := l_out_where_clause || l_array_join_condition(i);
1912
1913 IF i < l_array_join_condition.COUNT THEN
1914 l_out_where_clause := l_out_where_clause || ' AND ';
1915 END IF;
1916
1917 -- Debug
1918 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1919
1920 trace
1921 (p_msg => 'After adding outer join operators - ' ||
1922 'l_array_join_condition(' || i || ') = ' ||
1923 l_array_join_condition (i)
1924 ,p_level => C_LEVEL_PROCEDURE
1925 ,p_module => l_log_module);
1926
1927 END IF;
1928
1929 END LOOP;
1930 END IF;
1931 END IF;
1932
1933 --
1934 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1935
1936 trace
1937 (p_msg => 'END of AddOuterJoinOps'
1938 ,p_level => C_LEVEL_PROCEDURE
1939 ,p_module => l_log_module);
1940
1941 END IF;
1942
1943 RETURN NVL(l_out_where_clause,p_join_condition);
1944
1945 END AddOuterJoinOps;
1946
1947
1948 --
1949 --+==========================================================================+
1950 --| |
1951 --| PUBLIC FUNCTION |
1952 --| |
1953 --| |
1954 --| |
1955 --+==========================================================================+
1956 --
1957 --
1958 FUNCTION CallExtractIntegrityChecker (
1959 p_application_id IN NUMBER
1960 , p_entity_code IN VARCHAR2
1961 , p_event_class_code IN VARCHAR2
1962 , p_amb_context_code IN VARCHAR2
1963 , p_product_rule_type_code IN VARCHAR2
1964 , p_product_rule_code IN VARCHAR2
1965 --
1966 , p_array_evt_source_index IN xla_cmp_source_pkg.t_array_ByInt
1967 --
1968 , p_array_application_id IN xla_cmp_source_pkg.t_array_Num
1969 , p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
1970 , p_array_source_type_code IN xla_cmp_source_pkg.t_array_VL1
1971 , p_array_datatype_code IN xla_cmp_source_pkg.t_array_VL1
1972 , p_array_translated_flag IN xla_cmp_source_pkg.t_array_VL1
1973 --
1974 , p_array_evt_source_Level OUT NOCOPY xla_cmp_source_pkg.t_array_VL1
1975 --
1976 , p_array_object_name OUT NOCOPY xla_cmp_source_pkg.t_array_VL30
1977 , p_array_parent_object_index OUT NOCOPY xla_cmp_source_pkg.t_array_ByInt
1978 , p_array_object_type OUT NOCOPY xla_cmp_source_pkg.t_array_VL30
1979 , p_array_object_hash_id OUT NOCOPY xla_cmp_source_pkg.t_array_VL30
1980 , p_array_populated_flag OUT NOCOPY xla_cmp_source_pkg.t_array_VL1
1981 --
1982 , p_array_ref_obj_flag OUT NOCOPY xla_cmp_source_pkg.t_array_VL1
1983 , p_array_join_condition OUT NOCOPY xla_cmp_source_pkg.t_array_VL2000
1984 --
1985 , p_array_h_source_index OUT NOCOPY xla_cmp_source_pkg.t_array_ByInt
1986 , p_array_h_table_index OUT NOCOPY xla_cmp_source_pkg.t_array_ByInt
1987 , p_array_h_mls_source_index OUT NOCOPY xla_cmp_source_pkg.t_array_ByInt
1988 , p_array_h_mls_table_index OUT NOCOPY xla_cmp_source_pkg.t_array_ByInt
1989 , p_array_l_source_index OUT NOCOPY xla_cmp_source_pkg.t_array_ByInt
1990 , p_array_l_table_index OUT NOCOPY xla_cmp_source_pkg.t_array_ByInt
1991 , p_array_l_mls_source_index OUT NOCOPY xla_cmp_source_pkg.t_array_ByInt
1992 , p_array_l_mls_table_index OUT NOCOPY xla_cmp_source_pkg.t_array_ByInt
1993 )
1994 RETURN BOOLEAN
1995 IS
1996 --
1997 l_IsExtractValid BOOLEAN:=TRUE;
1998 --
1999 l_array_evt_source_index xla_cmp_source_pkg.t_array_ByInt;
2000 l_array_application_id xla_cmp_source_pkg.t_array_Num;
2001 l_array_source_code xla_cmp_source_pkg.t_array_VL30;
2002 l_array_source_type_code xla_cmp_source_pkg.t_array_VL1;
2003 l_array_datatype_code xla_cmp_source_pkg.t_array_VL1;
2004 l_array_translated_flag xla_cmp_source_pkg.t_array_VL1;
2005 --
2006 l_array_evt_source_Level xla_cmp_source_pkg.t_array_VL1;
2007 --
2008 l_array_object_name xla_cmp_source_pkg.t_array_VL30;
2009 l_array_parent_object_index xla_cmp_source_pkg.t_array_ByInt;
2010 l_array_object_type xla_cmp_source_pkg.t_array_VL30;
2011 l_array_object_hash_id xla_cmp_source_pkg.t_array_VL30;
2012 l_array_populated_flag xla_cmp_source_pkg.t_array_VL1;
2016 --
2013 --
2014 l_array_ref_obj_flag xla_cmp_source_pkg.t_array_VL1;
2015 l_array_join_condition xla_cmp_source_pkg.t_array_VL2000;
2017 l_array_h_source_index xla_cmp_source_pkg.t_array_ByInt;
2018 l_array_h_table_index xla_cmp_source_pkg.t_array_ByInt;
2019 l_array_h_mls_source_index xla_cmp_source_pkg.t_array_ByInt;
2020 l_array_h_mls_table_index xla_cmp_source_pkg.t_array_ByInt;
2021 l_array_l_source_index xla_cmp_source_pkg.t_array_ByInt;
2022 l_array_l_table_index xla_cmp_source_pkg.t_array_ByInt;
2023 l_array_l_mls_source_index xla_cmp_source_pkg.t_array_ByInt;
2024 l_array_l_mls_table_index xla_cmp_source_pkg.t_array_ByInt;
2025 l_log_module VARCHAR2(240);
2026 --
2027 BEGIN
2028 --
2029 IF g_log_enabled THEN
2030 l_log_module := C_DEFAULT_MODULE||'.CallExtractIntegrityChecker';
2031 END IF;
2032 --
2033 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2034
2035 trace
2036 (p_msg => 'BEGIN of CallExtractIntegrityChecker'
2037 ,p_level => C_LEVEL_PROCEDURE
2038 ,p_module => l_log_module);
2039
2040 END IF;
2041
2042 -- Set application ID
2043
2044 g_application_id := p_application_id;
2045 --
2046 -- init PL/SQL arrays
2047 --
2048 l_array_evt_source_index := p_array_evt_source_index;
2049 l_array_application_id := p_array_application_id ;
2050 l_array_source_code := p_array_source_code ;
2051 l_array_source_type_code := p_array_source_type_code;
2052 l_array_datatype_code := p_array_datatype_code;
2053 l_array_translated_flag := p_array_translated_flag;
2054 --
2055 IF p_array_evt_source_index.COUNT > 0 THEN
2056 --
2057 --
2058 InitSourceArrays (
2059 l_array_evt_source_index
2060 , l_array_application_id
2061 , l_array_source_code
2062 , l_array_source_type_code
2063 , l_array_datatype_code
2064 , l_array_translated_flag
2065 );
2066
2067 --
2068 -- Insert sources in xla_evt_class_sources_gt GT table
2069 --
2070 InsertSourcesIntoGtTable (
2071 p_application_id
2072 , p_entity_code
2073 , p_event_class_code
2074 , l_array_evt_source_index
2075 , l_array_application_id
2076 , l_array_source_code
2077 , l_array_source_type_code
2078 , l_array_datatype_code
2079 , l_array_translated_flag
2080 );
2081 --
2082 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2083 trace
2084 (p_msg => '-> CALL xla_extract_integrity_pkg.Validate_sources_with_extract API'
2085 ,p_level => C_LEVEL_PROCEDURE
2086 ,p_module => l_log_module);
2087
2088 END IF;
2089 --
2090 -- Call Extract Integrity Checker
2091 --
2092 l_IsExtractValid := xla_extract_integrity_pkg.Validate_sources_with_extract
2093 (p_application_id
2094 ,p_entity_code
2095 ,p_event_class_code
2096 ,p_amb_context_code
2097 ,p_product_rule_type_code
2098 ,p_product_rule_code
2099 );
2100 --
2101 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2102 IF l_IsExtractValid THEN
2103 trace
2104 (p_msg => 'l_IsExtractValid = TRUE'
2105 ,p_level => C_LEVEL_STATEMENT
2106 ,p_module => l_log_module);
2107 ELSE
2108 trace
2109 (p_msg => 'l_IsExtractValid = FALSE'
2110 ,p_level => C_LEVEL_STATEMENT
2111 ,p_module => l_log_module);
2112 END IF;
2113 END IF;
2114
2115 IF l_IsExtractValid THEN
2116 --
2117 -- Get source levels
2118 --
2119 GetSourceLevels(
2120 p_application_id
2121 , p_entity_code
2122 , p_event_class_code
2123 , l_array_evt_source_Level
2124 );
2125 --
2126 -- Get Extract Objects
2127 --
2128 GetExtractObjects(
2129 p_application_id
2130 , p_entity_code
2131 , p_event_class_code
2132 , l_array_object_name
2133 , l_array_parent_object_index
2134 , l_array_object_type
2135 , l_array_object_hash_id
2136 , l_array_populated_flag
2137 --
2138 , l_array_ref_obj_flag
2139 , l_array_join_condition
2140 );
2141 --
2142 -- Get Sources with Extract objects
2143 --
2144 GetSourcesWithExtractObject(
2145 p_application_id
2146 , p_entity_code
2147 , p_event_class_code
2148 , l_array_object_name
2149 --
2150 , l_array_h_source_index
2151 , l_array_h_table_index
2152 --
2153 , l_array_h_mls_source_index
2154 , l_array_h_mls_table_index
2155 --
2156 , l_array_l_source_index
2157 , l_array_l_table_index
2158 --
2159 , l_array_l_mls_source_index
2160 , l_array_l_mls_table_index
2161 );
2162
2163 END IF;
2164 --
2165 --
2166 p_array_evt_source_Level := l_array_evt_source_Level;
2167 --
2168 p_array_object_name := l_array_object_name;
2169 p_array_parent_object_index := l_array_parent_object_index;
2170 p_array_object_type := l_array_object_type;
2171 p_array_object_hash_id := l_array_object_hash_id;
2172 p_array_populated_flag := l_array_populated_flag;
2173 --
2174 p_array_ref_obj_flag := l_array_ref_obj_flag;
2178 p_array_h_table_index := l_array_h_table_index;
2175 p_array_join_condition := l_array_join_condition;
2176 --
2177 p_array_h_source_index := l_array_h_source_index;
2179 p_array_h_mls_source_index := l_array_h_mls_source_index;
2180 p_array_h_mls_table_index := l_array_h_mls_table_index;
2181 p_array_l_source_index := l_array_l_source_index;
2182 p_array_l_table_index := l_array_l_table_index;
2183 p_array_l_mls_source_index := l_array_l_mls_source_index;
2184 p_array_l_mls_table_index := l_array_l_mls_table_index;
2185 --
2186 --
2187 END IF;
2188 --
2189
2190 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2191 IF l_IsExtractValid THEN
2192 trace
2193 (p_msg => 'return value (l_IsExtractValid) = TRUE'
2194 ,p_level => C_LEVEL_PROCEDURE
2195 ,p_module => l_log_module);
2196 ELSE
2197 trace
2198 (p_msg => 'return value (l_IsExtractValid) = FALSE'
2199 ,p_level => C_LEVEL_PROCEDURE
2200 ,p_module => l_log_module);
2201 END IF;
2202
2203 trace
2204 (p_msg => 'END of CallExtractIntegrityChecker'
2205 ,p_level => C_LEVEL_PROCEDURE
2206 ,p_module => l_log_module);
2207 END IF;
2208
2209 RETURN l_IsExtractValid;
2210 --
2211 EXCEPTION
2212 WHEN xla_exceptions_pkg.application_exception THEN
2213 RETURN FALSE;
2214 WHEN OTHERS THEN
2215 xla_exceptions_pkg.raise_message
2216 (p_location => 'xla_cmp_extract_pkg.CallExtractIntegrityChecker ');
2217 END CallExtractIntegrityChecker;
2218 --
2219 --
2220 --+==========================================================================+
2221 --| |
2222 --| GENERATION of Accounting Event Extract |
2223 --| |
2224 --| |
2225 --| |
2226 --| |
2227 --| |
2228 --| |
2229 --| |
2230 --| |
2231 --| |
2232 --| |
2233 --| |
2234 --| |
2235 --| |
2236 --| |
2237 --| |
2238 --| |
2239 --| |
2240 --| |
2241 --| |
2242 --| |
2243 --| |
2244 --| |
2245 --| |
2246 --| |
2247 --| |
2248 --| |
2249 --| |
2250 --| |
2251 --| |
2252 --| |
2253 --| |
2254 --| |
2255 --| |
2256 --| |
2257 --| |
2258 --| |
2259 --+==========================================================================+
2260 --
2261 --
2262 --+==========================================================================+
2263 --| |
2264 --| PRIVATE function |
2265 --| |
2266 --| Generate the declaration of the sturcture for the line variables |
2267 --| |
2268 --| |
2269 --+==========================================================================+
2270 --
2271 FUNCTION GenerateLineStructure (
2275 --
2272 p_array_table_name IN xla_cmp_source_pkg.t_array_VL30
2273 --
2274 , p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
2276 , p_array_l_source_index IN xla_cmp_source_pkg.t_array_ByInt
2277 , p_array_l_table_index IN xla_cmp_source_pkg.t_array_ByInt
2278 --
2279 , p_array_l_mls_source_index IN xla_cmp_source_pkg.t_array_ByInt
2280 , p_array_l_mls_table_index IN xla_cmp_source_pkg.t_array_ByInt
2281 --
2282 )
2283 RETURN VARCHAR2
2284 IS
2285 --
2286
2287 C_LINE_STRUCTURE CONSTANT VARCHAR2(2000):=
2288 'TYPE t_array_source_$Index$ IS TABLE OF $table$.$column$%TYPE INDEX BY BINARY_INTEGER;';
2289 --
2290 l_LineTypes VARCHAR2(32000);
2291 l_LineType VARCHAR2(2000);
2292 l_log_module VARCHAR2(240);
2293 --
2294 BEGIN
2295 --
2296 IF g_log_enabled THEN
2297 l_log_module := C_DEFAULT_MODULE||'.GenerateLineStructure';
2298 END IF;
2299 --
2300 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2301
2302 trace
2303 (p_msg => 'BEGIN of GenerateLineStructure'
2304 ,p_level => C_LEVEL_PROCEDURE
2305 ,p_module => l_log_module);
2306
2307 END IF;
2308
2309 l_LineTypes:= NULL;
2310
2311 IF p_array_l_source_index. COUNT > 0 THEN
2312 --
2313 FOR Idx IN p_array_l_source_index.FIRST .. p_array_l_source_index.LAST LOOP
2314 --
2315 IF p_array_l_source_index.EXISTS(Idx) THEN
2316 --
2317 l_LineType := C_LINE_STRUCTURE;
2318 --
2319 l_LineType := REPLACE(l_LineType,'$Index$' , Idx);
2320 l_LineType := REPLACE(l_LineType,'$table$' ,
2321 p_array_table_name(p_array_l_table_index(Idx)));
2322 l_LineType := REPLACE(l_LineType,'$column$', p_array_source_code(Idx));
2323 --
2324 l_LineTypes := l_LineTypes ||g_chr_newline || l_LineType ;
2325 --
2326 END IF;
2327 --
2328 END LOOP;
2329 --
2330 END IF;
2331 --
2332 -- structure of mls line sources
2333 --
2334 IF p_array_l_mls_source_index.COUNT > 0 THEN
2335 --
2336 FOR Idx IN p_array_l_mls_source_index.FIRST .. p_array_l_mls_source_index.LAST LOOP
2337 --
2338 IF p_array_l_mls_source_index.EXISTS(Idx) THEN
2339 --
2340 l_LineType := C_LINE_STRUCTURE;
2341 --
2342 l_LineType := REPLACE(l_LineType,'$Index$' , Idx);
2343 l_LineType := REPLACE(l_LineType,'$table$' ,
2344 p_array_table_name(p_array_l_mls_table_index(Idx)));
2345 l_LineType := REPLACE(l_LineType,'$column$', p_array_source_code(Idx));
2346 --
2347 l_LineTypes := l_LineTypes || g_chr_newline || l_LineType ;
2348 --
2349 END IF;
2350 --
2351 END LOOP;
2352 --
2353 END IF;
2354 --
2355 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2356
2357 trace
2358 (p_msg => 'END of GenerateLineStructure'
2359 ,p_level => C_LEVEL_PROCEDURE
2360 ,p_module => l_log_module);
2361
2362 END IF;
2363 --
2364 RETURN l_LineTypes;
2365 EXCEPTION
2366 WHEN xla_exceptions_pkg.application_exception THEN
2367 RETURN NULL;
2368 WHEN OTHERS THEN
2369 xla_exceptions_pkg.raise_message
2370 (p_location => 'xla_cmp_extract_pkg.GenerateLineStructure ');
2371 END GenerateLineStructure;
2372 --
2373 --
2374 --+==========================================================================+
2375 --| |
2376 --| PRIVATE function |
2377 --| |
2378 --| Generate the Declaration of header variables |
2379 --| |
2380 --+==========================================================================+
2381 --
2382
2383 FUNCTION GenerateHdrVariables
2384 (p_array_h_source_index IN xla_cmp_source_pkg.t_array_ByInt
2385 ,p_array_h_mls_source_index IN xla_cmp_source_pkg.t_array_ByInt
2386 ,p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
2387 ,p_array_lookup_type IN xla_cmp_source_pkg.t_array_VL30
2388 ,p_array_view_application_id IN xla_cmp_source_pkg.t_array_Num)
2389 RETURN VARCHAR2 IS
2390
2391 C_HEADER_VAR CONSTANT VARCHAR2(2000) :='l_array_source_$Index$ t_array_source_$Index$;';
2392 C_LOOKUP_VAR CONSTANT VARCHAR2(100) :='l_array_source_$Index$_meaning t_array_lookup_meaning;';
2393
2394 l_HdrVariables VARCHAR2(32000);
2395 l_one_var VARCHAR2(2000);
2396 l_log_module VARCHAR2(240);
2397
2398 BEGIN
2399 IF g_log_enabled THEN
2400 l_log_module := C_DEFAULT_MODULE||'.GenerateHdrVariables';
2401 END IF;
2402
2403 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2404 trace
2405 (p_msg => 'BEGIN of GenerateHdrVariables'
2406 ,p_level => C_LEVEL_PROCEDURE
2407 ,p_module => l_log_module);
2408 END IF;
2409
2410 --
2411 -- declare the standard header variables
2412 --
2413 l_one_var := NULL;
2414 l_HdrVariables := l_one_var ;
2415
2419 l_one_var := C_HEADER_VAR;
2416 IF p_array_h_source_index.COUNT > 0 THEN
2417 FOR Idx IN p_array_h_source_index.FIRST .. p_array_h_source_index.LAST LOOP
2418 IF p_array_h_source_index.EXISTS(Idx) THEN
2420
2421 IF p_array_lookup_type.EXISTS(Idx) AND
2422 p_array_lookup_type(Idx) IS NOT NULL AND
2423 p_array_view_application_id.EXISTS(Idx) AND
2424 p_array_view_application_id(Idx) IS NOT NULL
2425 THEN
2426 l_one_var := l_one_var|| g_chr_newline ||C_LOOKUP_VAR;
2427 END IF;
2428 l_one_var := REPLACE(l_one_var,'$Index$' , Idx);
2429 l_HdrVariables := l_HdrVariables || g_chr_newline || l_one_var ;
2430 END IF;
2431 END LOOP;
2432 END IF;
2433
2434 --
2435 -- declare the mls line variables
2436 --
2437 IF p_array_h_mls_source_index.COUNT > 0 THEN
2438 FOR Idx IN p_array_h_mls_source_index.FIRST .. p_array_h_mls_source_index.LAST LOOP
2439 IF p_array_h_mls_source_index.EXISTS(Idx) THEN
2440 l_one_var := C_HEADER_VAR;
2441
2442 IF p_array_lookup_type.EXISTS(Idx) AND
2443 p_array_lookup_type(Idx) IS NOT NULL AND
2444 p_array_view_application_id.EXISTS(Idx) AND
2445 p_array_view_application_id(Idx) IS NOT NULL
2446 THEN
2447 l_one_var := l_one_var|| g_chr_newline ||C_LOOKUP_VAR;
2448 END IF;
2449 l_one_var := REPLACE(l_one_var,'$Index$' , Idx);
2450 l_HdrVariables := l_HdrVariables || g_chr_newline || l_one_var ;
2451 END IF;
2452 END LOOP;
2453 END IF;
2454
2455 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2456 trace
2457 (p_msg => 'END of GenerateHdrVariables'
2458 ,p_level => C_LEVEL_PROCEDURE
2459 ,p_module => l_log_module);
2460 END IF;
2461
2462 RETURN l_HdrVariables;
2463 EXCEPTION
2464 WHEN xla_exceptions_pkg.application_exception THEN
2465 RETURN NULL;
2466 WHEN OTHERS THEN
2467 xla_exceptions_pkg.raise_message
2468 (p_location => 'xla_cmp_extract_pkg.GenerateHdrVariables ');
2469 END GenerateHdrVariables;
2470 --
2471 --
2472 --+==========================================================================+
2473 --| |
2474 --| PRIVATE function |
2475 --| |
2476 --| Generate Fetch on header cursor into header variables |
2477 --| |
2478 --+==========================================================================+
2479 --
2480 FUNCTION GenerateFetchHeaderCursor (
2481 p_array_h_source_index IN xla_cmp_source_pkg.t_array_ByInt
2482 , p_array_h_mls_source_index IN xla_cmp_source_pkg.t_array_ByInt
2483 --
2484 , p_array_lookup_type IN xla_cmp_source_pkg.t_array_VL30
2485 , p_array_view_application_id IN xla_cmp_source_pkg.t_array_Num
2486 )
2487 RETURN VARCHAR2
2488 IS
2489 --
2490 l_hdr_variables VARCHAR2(32000);
2491 l_one_variable VARCHAR2(1000);
2492 --
2493 C_HDR_VAR CONSTANT VARCHAR2(1000):=' , l_array_source_$Index$';
2494 C_LOOKUP_VAR CONSTANT VARCHAR2(100) :=' , l_array_source_$Index$_meaning';
2495 --
2496 l_log_module VARCHAR2(240);
2497 BEGIN
2498 --
2499 IF g_log_enabled THEN
2500 l_log_module := C_DEFAULT_MODULE||'.GenerateFetchHeaderCursor';
2501 END IF;
2502 --
2503 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2507 ,p_level => C_LEVEL_PROCEDURE
2504
2505 trace
2506 (p_msg => 'BEGIN of GenerateFetchHeaderCursor'
2508 ,p_module => l_log_module);
2509
2510 END IF;
2511 --
2512 l_hdr_variables := null;
2513 --
2514 IF p_array_h_source_index.COUNT > 0 THEN
2515 --
2516 FOR Idx IN p_array_h_source_index.FIRST .. p_array_h_source_index.LAST LOOP
2517
2518 IF p_array_h_source_index.EXISTS(Idx) THEN
2519 --
2520 l_one_variable := C_HDR_VAR;
2521 --
2522 IF p_array_lookup_type.EXISTS(Idx) AND
2523 p_array_lookup_type(Idx) IS NOT NULL AND
2524 p_array_view_application_id.EXISTS(Idx) AND
2525 p_array_view_application_id(Idx) IS NOT NULL THEN
2526
2527 l_one_variable := l_one_variable|| g_chr_newline ||C_LOOKUP_VAR;
2528
2529 END IF;
2530 --
2531 l_one_variable := REPLACE(l_one_variable,'$Index$' , Idx);
2532 l_hdr_variables:= l_hdr_variables || g_chr_newline || l_one_variable;
2533 --
2534 END IF;
2535
2536 END LOOP;
2537 --
2538 END IF;
2539 --
2540 IF p_array_h_mls_source_index.COUNT > 0 THEN
2541 --
2542 FOR Idx IN p_array_h_mls_source_index.FIRST .. p_array_h_mls_source_index.LAST LOOP
2543 IF p_array_h_mls_source_index.EXISTS(Idx) THEN
2544 --
2545 l_one_variable := C_HDR_VAR;
2546 --
2547 IF p_array_lookup_type.EXISTS(Idx) AND
2548 p_array_lookup_type(Idx) IS NOT NULL AND
2549 p_array_view_application_id.EXISTS(Idx) AND
2550 p_array_view_application_id(Idx) IS NOT NULL THEN
2551
2552 l_one_variable := l_one_variable|| g_chr_newline ||C_LOOKUP_VAR;
2553
2554 END IF;
2555 --
2556 l_one_variable := REPLACE(l_one_variable,'$Index$' , Idx);
2557 l_hdr_variables:= l_hdr_variables || g_chr_newline || l_one_variable;
2558 --
2559 END IF;
2560 END LOOP;
2561 --
2562 END IF;
2563 --
2564 --
2565 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2566
2567 trace
2568 (p_msg => 'END of GenerateFetchHeaderCursor'
2569 ,p_level => C_LEVEL_PROCEDURE
2570 ,p_module => l_log_module);
2571
2572 END IF;
2573 --
2574 RETURN l_hdr_variables;
2575 EXCEPTION
2576 WHEN xla_exceptions_pkg.application_exception THEN
2577 RETURN NULL;
2578 WHEN OTHERS THEN
2579 xla_exceptions_pkg.raise_message
2580 (p_location => 'xla_cmp_extract_pkg.GenerateFetchHeaderCursor ');
2581 END GenerateFetchHeaderCursor;
2582 --
2583 --
2584 --+==========================================================================+
2585 --| |
2586 --| PRIVATE function |
2587 --| |
2588 --| Generate the Declaration of line variables |
2589 --| |
2590 --+==========================================================================+
2591 --
2592 --
2593 FUNCTION GenerateLineVariables(
2594 p_array_l_source_index IN xla_cmp_source_pkg.t_array_ByInt
2595 , p_array_l_mls_source_index IN xla_cmp_source_pkg.t_array_ByInt
2596 --
2597 , p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
2598 , p_array_lookup_type IN xla_cmp_source_pkg.t_array_VL30
2599 , p_array_view_application_id IN xla_cmp_source_pkg.t_array_Num
2600 )
2601 RETURN VARCHAR2
2602 IS
2603 --
2604 C_LINE_VAR CONSTANT VARCHAR2(2000) :='l_array_source_$Index$ t_array_source_$Index$;';
2605 C_LOOKUP_VAR CONSTANT VARCHAR2(100) :='l_array_source_$Index$_meaning t_array_lookup_meaning;';
2606 --
2607 l_LineVariables VARCHAR2(32000);
2608 l_one_var VARCHAR2(2000);
2609 --
2610 l_log_module VARCHAR2(240);
2611 --
2612 BEGIN
2613 --
2614 IF g_log_enabled THEN
2615 l_log_module := C_DEFAULT_MODULE||'.GenerateLineVariables';
2616 END IF;
2617 --
2618 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2619
2620 trace
2621 (p_msg => 'BEGIN of GenerateLineVariables'
2622 ,p_level => C_LEVEL_PROCEDURE
2623 ,p_module => l_log_module);
2624
2625 END IF;
2626 --
2627 -- declare the standard line variables
2628 --
2629 l_one_var := NULL;
2630 l_LineVariables := l_one_var ;
2631 --
2632 IF p_array_l_source_index.COUNT > 0 THEN
2633 --
2634 FOR Idx IN p_array_l_source_index.FIRST .. p_array_l_source_index.LAST LOOP
2635 --
2636 IF p_array_l_source_index.EXISTS(Idx) THEN
2637 --
2638 l_one_var := C_LINE_VAR;
2639 --
2640 IF p_array_lookup_type.EXISTS(Idx) AND
2641 p_array_lookup_type(Idx) IS NOT NULL AND
2642 p_array_view_application_id.EXISTS(Idx) AND
2643 p_array_view_application_id(Idx) IS NOT NULL THEN
2644
2645 l_one_var := l_one_var|| g_chr_newline ||C_LOOKUP_VAR;
2646
2647 END IF;
2648 --
2649 l_one_var := REPLACE(l_one_var,'$Index$' , Idx);
2650 l_LineVariables := l_LineVariables || g_chr_newline || l_one_var ;
2651 --
2652 END IF;
2653 --
2654 END LOOP;
2655 --
2656 END IF;
2657 --
2661 --
2658 -- declare the mls line variables
2659 --
2660 IF p_array_l_mls_source_index.COUNT > 0 THEN
2662 FOR Idx IN p_array_l_mls_source_index.FIRST .. p_array_l_mls_source_index.LAST LOOP
2663 --
2664 IF p_array_l_mls_source_index.EXISTS(Idx) THEN
2665 --
2666 l_one_var := C_LINE_VAR;
2667 --
2668 IF p_array_lookup_type.EXISTS(Idx) AND
2669 p_array_lookup_type(Idx) IS NOT NULL AND
2670 p_array_view_application_id.EXISTS(Idx) AND
2671 p_array_view_application_id(Idx) IS NOT NULL THEN
2672
2673 l_one_var := l_one_var|| g_chr_newline ||C_LOOKUP_VAR;
2674
2675 END IF;
2676 --
2677 l_one_var := REPLACE(l_one_var,'$Index$' , Idx);
2678 l_LineVariables := l_LineVariables || g_chr_newline || l_one_var ;
2679
2680 END IF;
2681 --
2682 END LOOP;
2683 --
2684 END IF;
2685 --
2686 --
2687 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2688
2689 trace
2690 (p_msg => 'END of GenerateLineVariables'
2691 ,p_level => C_LEVEL_PROCEDURE
2692 ,p_module => l_log_module);
2693
2694 END IF;
2695 --
2696 RETURN l_LineVariables;
2697 EXCEPTION
2698 WHEN xla_exceptions_pkg.application_exception THEN
2699 RETURN NULL;
2700 WHEN OTHERS THEN
2701 xla_exceptions_pkg.raise_message
2702 (p_location => 'xla_cmp_extract_pkg.GenerateLineVariables ');
2703 END GenerateLineVariables;
2704 --
2705 --
2706 --+==========================================================================+
2707 --| |
2708 --| PRIVATE function |
2709 --| |
2710 --| Generate Fetch on Line cursor into header variables |
2711 --| |
2712 --+==========================================================================+
2713 --
2714 FUNCTION GenerateFetchLineCursor(
2715 p_array_l_source_index IN xla_cmp_source_pkg.t_array_ByInt
2716 , p_array_l_mls_source_index IN xla_cmp_source_pkg.t_array_ByInt
2717 --
2718 , p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
2719 , p_array_lookup_type IN xla_cmp_source_pkg.t_array_VL30
2720 , p_array_view_application_id IN xla_cmp_source_pkg.t_array_Num
2721 )
2722 RETURN VARCHAR2
2723 IS
2724 --
2725 C_LINE_VAR CONSTANT VARCHAR2(1000):=' , l_array_source_$Index$';
2726 C_LOOKUP_VAR CONSTANT VARCHAR2(1000):=' , l_array_source_$Index$_meaning';
2727 l_LineVariables VARCHAR2(32000);
2728 l_one_var VARCHAR2(1000);
2729 --
2730 l_log_module VARCHAR2(240);
2731 --
2732 BEGIN
2733 --
2734 --
2735 IF g_log_enabled THEN
2736 l_log_module := C_DEFAULT_MODULE||'.GenerateFetchLineCursor';
2737 END IF;
2738 --
2739 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2740
2741 trace
2742 (p_msg => 'BEGIN of GenerateFetchLineCursor'
2743 ,p_level => C_LEVEL_PROCEDURE
2744 ,p_module => l_log_module);
2745
2746 END IF;
2747 --
2748 --
2749 l_one_var := ' , l_array_extract_line_num ';
2750 l_LineVariables := l_one_var ;
2751 --
2752 -- Fetch standard line variables
2753 --
2754 IF p_array_l_source_index.COUNT > 0 THEN
2755 --
2756 FOR Idx IN p_array_l_source_index.FIRST .. p_array_l_source_index.LAST LOOP
2757 --
2758 IF p_array_l_source_index.EXISTS(Idx) THEN
2759 --
2760 l_one_var := C_LINE_VAR ;
2761 --
2765 p_array_view_application_id(Idx) IS NOT NULL THEN
2762 IF p_array_lookup_type.EXISTS(Idx) AND
2763 p_array_lookup_type(Idx) IS NOT NULL AND
2764 p_array_view_application_id.EXISTS(Idx) AND
2766
2767 l_one_var := l_one_var|| g_chr_newline ||C_LOOKUP_VAR;
2768
2769 END IF;
2770 --
2771 l_one_var := REPLACE(l_one_var,'$Index$' , Idx);
2772 l_LineVariables := l_LineVariables || g_chr_newline || l_one_var ;
2773 --
2774 END IF;
2775 --
2776 END LOOP;
2777 --
2778 END IF;
2779 --
2780 -- Fetch mls line variables
2781 --
2782 IF p_array_l_mls_source_index.COUNT > 0 THEN
2783 --
2784 FOR Idx IN p_array_l_mls_source_index.FIRST .. p_array_l_mls_source_index.LAST LOOP
2785 --
2786 IF p_array_l_mls_source_index.EXISTS(Idx) THEN
2787 --
2788 l_one_var := C_LINE_VAR;
2789 --
2790 IF p_array_lookup_type.EXISTS(Idx) AND
2791 p_array_lookup_type(Idx) IS NOT NULL AND
2792 p_array_view_application_id.EXISTS(Idx) AND
2793 p_array_view_application_id(Idx) IS NOT NULL THEN
2794
2795 l_one_var := l_one_var|| g_chr_newline ||C_LOOKUP_VAR;
2796
2797 END IF;
2798 --
2799 l_one_var := REPLACE(l_one_var,'$Index$' , Idx);
2800 l_LineVariables := l_LineVariables || g_chr_newline || l_one_var ;
2801 --
2802 END IF;
2803 --
2804 END LOOP;
2805 --
2806 END IF;
2807 --
2808 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2809
2810 trace
2811 (p_msg => 'END of GenerateFetchLineCursor'
2812 ,p_level => C_LEVEL_PROCEDURE
2813 ,p_module => l_log_module);
2814
2815 END IF;
2816 --
2817 RETURN l_LineVariables;
2818 EXCEPTION
2819 WHEN xla_exceptions_pkg.application_exception THEN
2820 RETURN NULL;
2821 WHEN OTHERS THEN
2822 xla_exceptions_pkg.raise_message
2823 (p_location => 'xla_cmp_extract_pkg.GenerateFetchLineCursor ');
2824 END GenerateFetchLineCursor;
2825 --
2826 --+==========================================================================+
2827 --| |
2828 --| PRIVATE Procedure |
2829 --| |
2830 --| Get Different Extract Object used by header or Line Cursor |
2831 --| |
2832 --+==========================================================================+
2833 --
2834 PROCEDURE GetUsedExtractObject(
2835 p_array_table_index IN xla_cmp_source_pkg.t_array_ByInt
2836 , p_array_parent_table_index IN xla_cmp_source_pkg.t_array_ByInt
2837 , p_array_diff_table_index IN OUT NOCOPY xla_cmp_source_pkg.t_array_ByInt
2838 )
2839 IS
2840 --
2841 l_log_module VARCHAR2(240);
2842 i BINARY_INTEGER;
2843 --
2844 BEGIN
2845 --
2846 IF g_log_enabled THEN
2847 l_log_module := C_DEFAULT_MODULE||'.GetUsedExtractObject';
2848 END IF;
2849 --
2850 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2851
2852 trace
2853 (p_msg => 'BEGIN of GetUsedExtractObject'
2854 ,p_level => C_LEVEL_PROCEDURE
2855 ,p_module => l_log_module);
2856
2857 END IF;
2858 --
2859 IF p_array_table_index.COUNT > 0 THEN
2860 --
2861 --
2862 FOR Idx IN p_array_table_index.FIRST .. p_array_table_index.LAST LOOP
2863 --
2864 IF p_array_table_index.EXISTS(Idx) THEN
2865 i := p_array_table_index(Idx);
2866 --
2867 p_array_diff_table_index(i) := i;
2868 --
2869 WHILE (p_array_parent_table_index.exists(i) and
2870 p_array_parent_table_index(i)>0) LOOP
2871 i := p_array_parent_table_index(i);
2872 p_array_diff_table_index(i) := i;
2873 END LOOP;
2874
2875 END IF;
2876 --
2877 END LOOP;
2878 --
2879 END IF;
2880 --
2881 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2882
2883 trace
2884 (p_msg => 'END of GetUsedExtractObject'
2885 ,p_level => C_LEVEL_PROCEDURE
2886 ,p_module => l_log_module);
2887
2888 END IF;
2889 --
2890 EXCEPTION
2891 WHEN xla_exceptions_pkg.application_exception THEN
2892 RAISE;
2893 WHEN OTHERS THEN
2894 xla_exceptions_pkg.raise_message
2895 (p_location => 'xla_cmp_extract_pkg.GetUsedExtractObject ');
2896 END GetUsedExtractObject;
2897 --
2898 --
2899 --+==========================================================================+
2900 --| |
2901 --| PRIVATE FUNCTION |
2902 --| |
2903 --| Generate the column to extract for the header/line cursor |
2904 --| value of ($hdr_sources$ or $line_sources$) |
2905 --| |
2906 --+==========================================================================+
2907 --
2911 , p_array_source_index IN xla_cmp_source_pkg.t_array_ByInt
2908 FUNCTION GenerateExtractColumns(
2909 p_array_table_hash IN xla_cmp_source_pkg.t_array_VL30
2910 , p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
2912 , p_array_table_index IN xla_cmp_source_pkg.t_array_ByInt
2913 , p_array_lookup_type IN xla_cmp_source_pkg.t_array_VL30
2914 , p_array_view_application_id IN xla_cmp_source_pkg.t_array_Num
2915 )
2916 RETURN VARCHAR2
2917 IS
2918 --
2919 C_COLUMN_TO_EXTRACT CONSTANT VARCHAR2(1000):= ' , $tab$.$column$ source_$Index$' ;
2920 C_LOOKUP_COLUMN CONSTANT VARCHAR2(200):= ' , fvl$Index$.meaning source_$Index$_meaning' ;
2921 l_ColumnsToExtract VARCHAR2(32000);
2922 l_OneColumn VARCHAR2(1000);
2923 --
2924 l_log_module VARCHAR2(240);
2925 --
2926 BEGIN
2927 --
2928 IF g_log_enabled THEN
2929 l_log_module := C_DEFAULT_MODULE||'.GenerateExtractColumns';
2930 END IF;
2931 --
2932 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2933
2934 trace
2935 (p_msg => 'BEGIN of GenerateExtractColumns'
2936 ,p_level => C_LEVEL_PROCEDURE
2937 ,p_module => l_log_module);
2938
2939 END IF;
2940 --
2941 l_ColumnsToExtract := NULL;
2942 --
2943 IF p_array_source_index.COUNT > 0 THEN
2944 --
2945 FOR Idx IN p_array_source_index.FIRST .. p_array_source_index.LAST LOOP
2946 --
2947 IF p_array_source_index.EXISTS(Idx) THEN
2948 --
2949 l_OneColumn := C_COLUMN_TO_EXTRACT ;
2950 --
2951 IF p_array_lookup_type.EXISTS(Idx) AND
2952 p_array_lookup_type(Idx) IS NOT NULL AND
2953 p_array_view_application_id.EXISTS(Idx) AND
2954 p_array_view_application_id(Idx) IS NOT NULL THEN
2955
2956 l_OneColumn := l_OneColumn|| g_chr_newline ||C_LOOKUP_COLUMN;
2957
2958 END IF;
2959 --
2960 l_OneColumn := REPLACE(l_OneColumn,'$tab$',
2961 p_array_table_hash(p_array_table_index(Idx))
2962 );
2963 --
2964 l_OneColumn := REPLACE(l_OneColumn,'$column$',
2965 p_array_source_code(p_array_source_index(Idx))
2966 );
2967 --
2968 l_OneColumn := REPLACE(l_OneColumn,'$Index$',Idx);
2969 --
2970 --
2971 l_ColumnsToExtract := l_ColumnsToExtract || g_chr_newline ;
2972 l_ColumnsToExtract := l_ColumnsToExtract || l_OneColumn ;
2973 --
2974 END IF;
2975 --
2976 END LOOP;
2977 --
2978 END IF;
2979 --
2980 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2981
2982 trace
2983 (p_msg => 'END of GenerateExtractColumns'
2984 ,p_level => C_LEVEL_PROCEDURE
2985 ,p_module => l_log_module);
2986
2987 END IF;
2988 --
2989 RETURN l_ColumnsToExtract;
2990 EXCEPTION
2991 WHEN xla_exceptions_pkg.application_exception THEN
2992 RETURN NULL;
2993 WHEN OTHERS THEN
2994 xla_exceptions_pkg.raise_message
2995 (p_location => 'xla_cmp_extract_pkg.GenerateExtractColumns ');
2996 END GenerateExtractColumns;
2997 --
2998 --+==========================================================================+
2999 --| |
3000 --| PRIVATE FUNCTION |
3001 --| |
3002 --| |
3003 --+==========================================================================+
3004 --
3005 FUNCTION GenerateLookupTables(
3006 p_array_source_index IN xla_cmp_source_pkg.t_array_ByInt
3007 , p_array_table_index IN xla_cmp_source_pkg.t_array_ByInt
3008 , p_array_lookup_type IN xla_cmp_source_pkg.t_array_VL30
3009 , p_array_view_application_id IN xla_cmp_source_pkg.t_array_Num
3010 )
3011 RETURN VARCHAR2
3012 IS
3013 --
3014 C_LOOKUP_TAB CONSTANT VARCHAR2(100) :=' , fnd_lookup_values fvl$Index$';
3015 l_one_table VARCHAR2(1000);
3016 l_tables VARCHAR2(32000);
3017 --
3018 l_log_module VARCHAR2(240);
3019 --
3020 BEGIN
3021 --
3022 IF g_log_enabled THEN
3023 l_log_module := C_DEFAULT_MODULE||'.GenerateLookupTables';
3024 END IF;
3025 --
3026 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3027
3028 trace
3029 (p_msg => 'BEGIN of GenerateLookupTables'
3030 ,p_level => C_LEVEL_PROCEDURE
3031 ,p_module => l_log_module);
3032
3033 END IF;
3034 --
3035 l_tables := NULL;
3036 --
3037 IF p_array_source_index.COUNT > 0 THEN
3038 --
3039 FOR Idx IN p_array_source_index.FIRST .. p_array_source_index.LAST LOOP
3040 --
3041 IF p_array_source_index.EXISTS(Idx) AND
3042 p_array_lookup_type.EXISTS(Idx) AND
3043 p_array_lookup_type(Idx) IS NOT NULL AND
3044 p_array_view_application_id.EXISTS(Idx) AND
3045 p_array_view_application_id(Idx) IS NOT NULL THEN
3046
3050 l_tables := l_tables|| g_chr_newline || l_one_table ;
3047 l_one_table := C_LOOKUP_TAB;
3048 l_one_table := REPLACE(l_one_table,'$Index$',Idx);
3049 --
3051 --
3052 END IF;
3053 --
3054 END LOOP;
3055 --
3056 END IF;
3057 --
3058 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3059
3060 trace
3061 (p_msg => 'END of GenerateLookupTables'
3062 ,p_level => C_LEVEL_PROCEDURE
3063 ,p_module => l_log_module);
3064
3065 END IF;
3066 --
3067 RETURN l_tables;
3068 EXCEPTION
3069 WHEN xla_exceptions_pkg.application_exception THEN
3070 RETURN NULL;
3071 WHEN OTHERS THEN
3072 xla_exceptions_pkg.raise_message
3073 (p_location => 'xla_cmp_extract_pkg.GenerateLookupTables ');
3074 END GenerateLookupTables;
3075 --
3076 --
3077 --+==========================================================================+
3078 --| |
3079 --| PRIVATE FUNCTION |
3080 --| |
3081 --| |
3082 --+==========================================================================+
3083 --
3084 FUNCTION GenerateLookupClauses(
3085 p_array_source_index IN xla_cmp_source_pkg.t_array_ByInt
3086 , p_array_table_index IN xla_cmp_source_pkg.t_array_ByInt
3087 , p_array_table_name IN xla_cmp_source_pkg.t_array_VL30
3088 , p_array_table_hash IN xla_cmp_source_pkg.t_array_VL30
3089 , p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
3090 , p_array_lookup_type IN xla_cmp_source_pkg.t_array_VL30
3091 , p_array_view_application_id IN xla_cmp_source_pkg.t_array_Num
3092 )
3093 RETURN VARCHAR2
3094 IS
3095 --
3096 --
3097 C_LOOKUP_CLAUSE CONSTANT VARCHAR2(1000):=' AND fvl$Index$.lookup_type(+) = ''$lookup_type$''
3098 AND fvl$Index$.lookup_code(+) = $tab$.$source$
3099 AND fvl$Index$.view_application_id(+) = $view_application_id$
3100 AND fvl$Index$.language(+) = USERENV(''LANG'')
3101 '
3102 ;
3103 l_one_clause VARCHAR2(1000);
3104 l_clauses VARCHAR2(32000);
3105 --
3106 l_log_module VARCHAR2(240);
3107 --
3108 BEGIN
3109 --
3110 IF g_log_enabled THEN
3111 l_log_module := C_DEFAULT_MODULE||'.GenerateLookupClauses';
3112 END IF;
3113 --
3114 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3115
3116 trace
3117 (p_msg => 'BEGIN of GenerateLookupClauses'
3118 ,p_level => C_LEVEL_PROCEDURE
3119 ,p_module => l_log_module);
3120
3121 END IF;
3122 --
3123 l_clauses := NULL;
3124 --
3125 IF p_array_source_index.COUNT > 0 THEN
3126 --
3127 FOR Idx IN p_array_source_index.FIRST .. p_array_source_index.LAST LOOP
3128 --
3129
3130 IF p_array_source_index.EXISTS(Idx) AND
3131 p_array_lookup_type.EXISTS(Idx) AND
3132 p_array_lookup_type(Idx) IS NOT NULL AND
3133 p_array_view_application_id.EXISTS(Idx) AND
3134 p_array_view_application_id(Idx) IS NOT NULL
3135
3136 THEN
3137
3138 l_one_clause := C_LOOKUP_CLAUSE;
3139 l_one_clause := REPLACE(l_one_clause,'$Index$',Idx);
3143 l_one_clause := REPLACE(l_one_clause,'$tab$',p_array_table_hash(p_array_table_index(Idx)));
3140 l_one_clause := REPLACE(l_one_clause,'$lookup_type$',p_array_lookup_type(Idx));
3141 l_one_clause := REPLACE(l_one_clause,'$view_application_id$',p_array_view_application_id(Idx));
3142 l_one_clause := REPLACE(l_one_clause,'$source$',p_array_source_code(Idx));
3144 --
3145 l_clauses := l_clauses|| l_one_clause ;
3146 --
3147 END IF;
3148 --
3149 END LOOP;
3150 --
3151 END IF;
3152 --
3153 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3154
3155 trace
3156 (p_msg => 'END of GenerateLookupClauses'
3157 ,p_level => C_LEVEL_PROCEDURE
3158 ,p_module => l_log_module);
3159
3160 END IF;
3161 --
3162 RETURN l_clauses;
3163 EXCEPTION
3164 WHEN xla_exceptions_pkg.application_exception THEN
3165 RETURN NULL;
3166 WHEN OTHERS THEN
3167 xla_exceptions_pkg.raise_message
3168 (p_location => 'xla_cmp_extract_pkg.GenerateLookupClauses ');
3169 END GenerateLookupClauses;
3170 --
3171 --+==========================================================================+
3172 --| |
3173 --| PRIVATE Procedure |
3174 --| |
3175 --| Get Different Extract Object used by header or Line Cursor |
3176 --| |
3177 --+==========================================================================+
3178 --
3179 FUNCTION GetAnAlwaysPopulatedObject(
3180 p_array_table_index IN xla_cmp_source_pkg.t_array_ByInt
3181 , p_array_populated_flag IN xla_cmp_source_pkg.t_array_VL1
3182 , p_array_ref_obj_flag IN xla_cmp_source_pkg.t_array_VL1
3183 )
3184 RETURN NUMBER
3185 IS
3186 l_object_index NUMBER;
3187 l_log_module VARCHAR2(240);
3188 BEGIN
3189 --
3190 IF g_log_enabled THEN
3191 l_log_module := C_DEFAULT_MODULE||'.GetAnAlwaysPopulatedObject';
3192 END IF;
3193 --
3194 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3195
3196 trace
3197 (p_msg => 'BEGIN of GetAnAlwaysPopulatedObject'
3198 ,p_level => C_LEVEL_PROCEDURE
3199 ,p_module => l_log_module);
3200
3201 END IF;
3202 --
3203 l_object_index:= NULL;
3204 --
3205 IF p_array_table_index.COUNT > 0 THEN
3206 --
3207 FOR Idx IN p_array_table_index.FIRST .. p_array_table_index.LAST LOOP
3208 --
3209 IF p_array_table_index.EXISTS(Idx)
3210 AND l_object_index IS NULL
3211 AND p_array_populated_flag.EXISTS (Idx)
3212 AND NVL(p_array_populated_flag(Idx),'N') ='Y'
3213 AND NVL(p_array_ref_obj_flag(Idx),'N') = 'N' THEN
3214 --
3215 l_object_index := Idx;
3216 --
3217 END IF;
3218 --
3219 END LOOP;
3220
3221 END IF;
3222 --
3223 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3224
3225 trace
3226 (p_msg => 'END of GetAnAlwaysPopulatedObject'
3227 ,p_level => C_LEVEL_PROCEDURE
3228 ,p_module => l_log_module);
3229
3230 END IF;
3231 --
3232 RETURN l_object_index;
3233 EXCEPTION
3234 WHEN xla_exceptions_pkg.application_exception THEN
3235 RAISE;
3236 WHEN OTHERS THEN
3237 xla_exceptions_pkg.raise_message
3238 (p_location => 'xla_cmp_extract_pkg.GetAnAlwaysPopulatedObject ');
3239 END GetAnAlwaysPopulatedObject;
3240
3241 --+==========================================================================+
3242 --| |
3243 --| PRIVATE function |
3244 --| |
3245 --| Generate the declaration of the header Cursor : The Extract of |
3246 --| standard and MLS header sources from the Header Extract Object |
3247 --| |
3248 --| |
3252 (p_array_table_name IN xla_cmp_source_pkg.t_array_VL30
3249 --+==========================================================================+
3250 --
3251 FUNCTION GenerateHeaderCursor
3253 ,p_array_parent_table_index IN xla_cmp_source_pkg.t_array_ByInt
3254 ,p_array_table_hash IN xla_cmp_source_pkg.t_array_VL30
3255 ,p_array_populated_flag IN xla_cmp_source_pkg.t_array_VL1
3256 ,p_array_ref_obj_flag IN xla_cmp_source_pkg.t_array_VL1
3257 ,p_array_join_condition IN xla_cmp_source_pkg.t_array_vl2000
3258 ,p_array_h_source_index IN xla_cmp_source_pkg.t_array_ByInt
3259 ,p_array_h_table_index IN xla_cmp_source_pkg.t_array_ByInt
3260 ,p_array_h_mls_source_index IN xla_cmp_source_pkg.t_array_ByInt
3261 ,p_array_h_mls_table_index IN xla_cmp_source_pkg.t_array_ByInt
3262 ,p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
3263 ,p_array_lookup_type IN xla_cmp_source_pkg.t_array_VL30
3264 ,p_array_view_application_id IN xla_cmp_source_pkg.t_array_Num
3265 ,p_procedure IN VARCHAR2)
3266 RETURN VARCHAR2 IS
3267 --
3268 l_hdr_cur VARCHAR2(32000);
3269 l_hdr_sources VARCHAR2(20000);
3270 l_hdr_tabs VARCHAR2(32000);
3271 l_hdr_clauses VARCHAR2(32000);
3272 --
3273 l_hdr_source VARCHAR2(1000);
3274 l_hdr_tab VARCHAR2(10000);
3275 l_hdr_clause VARCHAR2(10000);
3276 l_hdr_ref_clause VARCHAR2(10000);
3277 --
3278 l_h_count BINARY_INTEGER;
3279 l_h_mls_count BINARY_INTEGER;
3280 --
3281 l_array_h_tab xla_cmp_source_pkg.t_array_ByInt;
3282 l_array_h_mls_tab xla_cmp_source_pkg.t_array_ByInt;
3283 --
3284 l_first_tab BINARY_INTEGER;
3285 l_first_mls_tab BINARY_INTEGER;
3286 --
3287 l_log_module VARCHAR2(240);
3288 --
3289 BEGIN
3290 IF g_log_enabled THEN
3291 l_log_module := C_DEFAULT_MODULE||'.GenerateHeaderCursor';
3292 END IF;
3293
3294 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3295 trace
3296 (p_msg => 'BEGIN of GenerateHeaderCursor'
3297 ,p_level => C_LEVEL_PROCEDURE
3298 ,p_module => l_log_module);
3299 END IF;
3300
3301 l_first_tab := NULL;
3302 l_first_mls_tab := NULL;
3303 --
3304 --
3305 l_h_count := NVL(p_array_h_source_index.COUNT ,0);
3306 l_h_mls_count := NVL(p_array_h_mls_source_index.COUNT,0);
3307 --
3308 l_hdr_clauses := NULL;
3309 l_hdr_sources := NULL;
3310 l_hdr_tabs := NULL;
3311
3312 IF l_h_count > 0 THEN
3313 --
3314 -- get standard header tables/views
3315 --
3316 GetUsedExtractObject
3317 (p_array_table_index => p_array_h_table_index
3318 ,p_array_parent_table_index => p_array_parent_table_index
3319 ,p_array_diff_table_index => l_array_h_tab);
3320
3321 --
3322 -- Get the header always populated extract object
3323 --
3324 l_first_tab :=
3325 GetAnAlwaysPopulatedObject
3326 (p_array_table_index => l_array_h_tab
3327 ,p_array_populated_flag => p_array_populated_flag
3328 ,p_array_ref_obj_flag => p_array_ref_obj_flag);
3329
3330 --
3331 -- extract standard sources
3332 --
3333 l_hdr_sources :=
3334 l_hdr_sources ||
3335 GenerateExtractColumns
3336 (p_array_table_hash => p_array_table_hash
3337 ,p_array_source_code => p_array_source_code
3338 ,p_array_source_index => p_array_h_source_index
3339 ,p_array_table_index => p_array_h_table_index
3340 ,p_array_lookup_type => p_array_lookup_type
3341 ,p_array_view_application_id => p_array_view_application_id);
3342 END IF;
3343
3344 IF l_h_mls_count > 0 THEN
3345 --
3346 -- get mls header tables/views
3347 --
3348 GetUsedExtractObject
3349 (p_array_table_index => p_array_h_mls_table_index
3350 ,p_array_parent_table_index => p_array_parent_table_index
3351 ,p_array_diff_table_index => l_array_h_mls_tab);
3352
3353 --
3354 -- Get the header mls always populated extract object
3355 --
3359 ,p_array_populated_flag => p_array_populated_flag
3356 l_first_mls_tab :=
3357 GetAnAlwaysPopulatedObject
3358 (p_array_table_index => l_array_h_mls_tab
3360 ,p_array_ref_obj_flag => p_array_ref_obj_flag);
3361
3362 --
3363 -- extract mls sources
3364 --
3365 l_hdr_sources :=
3366 l_hdr_sources ||
3367 GenerateExtractColumns
3368 (p_array_table_hash => p_array_table_hash
3369 ,p_array_source_code => p_array_source_code
3370 ,p_array_source_index => p_array_h_mls_source_index
3371 ,p_array_table_index => p_array_h_mls_table_index
3372 ,p_array_lookup_type => p_array_lookup_type
3373 ,p_array_view_application_id => p_array_view_application_id);
3374 END IF;
3375
3376 --
3377 -- generate first clause
3378 --
3379 IF l_first_tab IS NOT NULL AND l_first_mls_tab IS NOT NULL THEN
3380 l_hdr_clause :=
3381 ' AND $first_tab$.event_id = xet.event_id' ||g_chr_newline||
3382 ' AND $first_tab$.event_id = $first_mls_tab$.event_id'||g_chr_newline||
3383 ' AND $first_mls_tab$.language = p_language' ||g_chr_newline;
3384
3385 l_hdr_clause := REPLACE(l_hdr_clause,'$first_tab$',p_array_table_hash(l_first_tab));
3386 l_hdr_clause := REPLACE(l_hdr_clause,'$first_mls_tab$', p_array_table_hash(l_first_mls_tab));
3387 l_hdr_clauses := l_hdr_clauses || l_hdr_clause;
3388 l_hdr_clause := NULL;
3389 ELSIF l_first_tab IS NOT NULL AND l_first_mls_tab IS NULL THEN
3390 l_hdr_clause := ' AND $first_tab$.event_id = xet.event_id' || g_chr_newline
3391 ;
3392 l_hdr_clause := REPLACE(l_hdr_clause,'$first_tab$',p_array_table_hash(l_first_tab));
3393 l_hdr_clauses := l_hdr_clauses || l_hdr_clause;
3394 l_hdr_clause := NULL;
3395 ELSIF l_first_tab IS NULL AND l_first_mls_tab IS NOT NULL THEN
3396 l_hdr_clause :=
3397 ' AND $first_mls_tab$.event_id = xet.event_id' ||g_chr_newline||
3398 ' AND $first_mls_tab$.language = p_language' || g_chr_newline;
3399
3400 l_hdr_clause := REPLACE(l_hdr_clause,'$first_mls_tab$', p_array_table_hash(l_first_mls_tab));
3401 l_hdr_clauses := l_hdr_clauses ||l_hdr_clause;
3402 l_hdr_clause := NULL;
3403 END IF;
3404
3405 IF l_h_count > 0 THEN
3406 FOR Idx IN l_array_h_tab.FIRST .. l_array_h_tab.LAST LOOP
3407 IF l_array_h_tab.EXISTS(Idx) THEN
3408 --
3409 -- generate the from experession in the extract
3410 --
3411 l_hdr_tab := ' , $table_name$ $tab$' ;
3412
3413 l_hdr_tab := REPLACE(l_hdr_tab, '$table_name$', p_array_table_name(Idx));
3414 l_hdr_tab := REPLACE(l_hdr_tab, '$tab$', p_array_table_hash(Idx));
3415 l_hdr_tabs := l_hdr_tabs || g_chr_newline || l_hdr_tab;
3416 l_hdr_tab := NULL;
3417
3418 IF NVL(p_array_ref_obj_flag(Idx),'N') = 'N' THEN
3419 IF Idx <> NVL(l_first_tab, -1) AND
3420 nvl(p_array_populated_flag(Idx),'N') = 'Y'
3421 THEN
3422 l_hdr_clause := ' AND $tab$.event_id = $first_tab$.event_id' || g_chr_newline;
3423 ELSIF Idx <> NVL(l_first_tab,-1) AND
3424 nvl(p_array_populated_flag(Idx),'N') = 'N'
3425 THEN
3426 l_hdr_clause := ' AND $tab$.event_id (+) = $first_tab$.event_id' || g_chr_newline;
3427 END IF;
3428
3429 IF l_first_tab IS NOT NULL OR l_first_mls_tab IS NOT NULL THEN
3430 l_hdr_clause :=
3431 REPLACE
3432 (l_hdr_clause,'$first_tab$'
3433 ,p_array_table_hash(NVL(l_first_tab,l_first_mls_tab)));
3434 ELSE
3435 l_hdr_clause :=
3436 REPLACE
3437 (l_hdr_clause,'$first_tab$', 'xet');
3438 END IF;
3439
3440 ELSE -- reference objects
3441
3442 IF nvl(p_array_populated_flag(Idx),'N') = 'Y' THEN
3443 l_hdr_ref_clause := p_array_join_condition (Idx);
3444
3445 --
3446 -- Replace object names with aliases
3447 --
3451 REGEXP_REPLACE(l_hdr_ref_clause
3448 FOR j IN p_array_table_name.FIRST .. p_array_table_name.LAST LOOP
3449
3450 l_hdr_ref_clause :=
3452 ,p_array_table_name(j)
3453 ,p_array_table_hash(j)
3454 ,1 -- Position
3455 ,0 -- All Occurrences
3456 ,'im' -- i: case insensitive, m: multiple lines
3457 );
3458 END LOOP;
3459
3460 l_hdr_ref_clause := ' AND ' || l_hdr_ref_clause;
3461
3462 ELSE -- always populated flag = 'N'
3463 l_hdr_ref_clause := AddOuterJoinOps (
3464 p_join_condition => p_array_join_condition(Idx)
3465 ,p_ref_obj_name => p_array_table_name(Idx));
3466 --
3467 -- Replace object names with aliases
3468 --
3469 FOR j IN p_array_table_name.FIRST .. p_array_table_name.LAST LOOP
3470
3471 l_hdr_ref_clause :=
3472 REGEXP_REPLACE(l_hdr_ref_clause
3473 ,p_array_table_name(j)
3474 ,p_array_table_hash(j)
3475 ,1 -- Position
3476 ,0 -- All Occurrences
3477 ,'im' -- i: case insensitive, m: multiple lines
3478 );
3479 END LOOP;
3480
3481 l_hdr_ref_clause := ' AND ' || l_hdr_ref_clause;
3482
3483 END IF;
3484 END IF;
3485
3486 l_hdr_clause := REPLACE(l_hdr_clause,'$tab$', p_array_table_hash(Idx));
3487 l_hdr_clauses := l_hdr_clauses || l_hdr_clause || l_hdr_ref_clause;
3488 l_hdr_clause := NULL;
3489 l_hdr_ref_clause := NULL;
3490 END IF;
3491 END LOOP;
3492 END IF;
3493
3494 IF l_h_mls_count > 0 THEN
3495 FOR Idx IN l_array_h_mls_tab.FIRST .. l_array_h_mls_tab.LAST LOOP
3496 IF l_array_h_mls_tab.EXISTS(Idx) THEN
3497 l_hdr_tab := ' , $table_name$ $tab$' ;
3498
3499 l_hdr_tab := REPLACE(l_hdr_tab, '$table_name$', p_array_table_name(Idx));
3503
3500 l_hdr_tab := REPLACE(l_hdr_tab, '$tab$', p_array_table_hash(Idx));
3501 l_hdr_tabs := l_hdr_tabs ||g_chr_newline || l_hdr_tab;
3502 l_hdr_tab := NULL;
3504 IF Idx <> NVL(l_first_mls_tab,-1) AND
3505 nvl(p_array_populated_flag(Idx),'N') = 'Y'
3506 THEN
3507 l_hdr_clause :=
3508 ' AND $tab$.event_id = $first_tab$.event_id' || g_chr_newline||
3509 ' AND $tab$.language = p_language' || g_chr_newline;
3510 ELSIF Idx <> NVL(l_first_mls_tab,-1) AND
3511 nvl(p_array_populated_flag(Idx),'N') = 'N'
3512 THEN
3513 l_hdr_clause :=
3514 ' AND $tab$.event_id (+) = $first_tab$.event_id'|| g_chr_newline||
3515 ' AND $tab$.language (+) = p_language' || g_chr_newline;
3516 END IF;
3517
3518 IF l_first_tab IS NOT NULL OR l_first_mls_tab IS NOT NULL THEN
3519 l_hdr_clause :=
3520 REPLACE
3521 (l_hdr_clause,'$first_tab$'
3522 ,p_array_table_hash(NVL(l_first_tab,l_first_mls_tab)));
3523 ELSE
3524 l_hdr_clause :=
3525 REPLACE
3526 (l_hdr_clause,'$first_tab$', 'xet');
3527 END IF;
3528
3529 l_hdr_clause := REPLACE(l_hdr_clause,'$tab$', p_array_table_hash(Idx));
3530 l_hdr_clauses := l_hdr_clauses || l_hdr_clause;
3531 l_hdr_clause := NULL;
3532 END IF;
3533 END LOOP;
3534 END IF;
3535
3536 --
3537 -- generate the extract of lookup sources
3538 --
3539
3540 l_hdr_tabs := l_hdr_tabs || GenerateLookupTables(
3541 p_array_source_index => p_array_h_source_index
3542 , p_array_table_index => p_array_h_table_index
3543 , p_array_lookup_type => p_array_lookup_type
3544 , p_array_view_application_id => p_array_view_application_id
3545 );
3546
3547
3548 l_hdr_tabs := l_hdr_tabs || GenerateLookupTables(
3549 p_array_source_index => p_array_h_mls_source_index
3550 , p_array_table_index => p_array_h_mls_table_index
3551 , p_array_lookup_type => p_array_lookup_type
3552 , p_array_view_application_id => p_array_view_application_id
3553 );
3554
3555 l_hdr_clauses := l_hdr_clauses || GenerateLookupClauses(
3556 p_array_source_index => p_array_h_source_index
3557 , p_array_table_index => p_array_h_table_index
3558 , p_array_table_name => p_array_table_name
3559 , p_array_table_hash => p_array_table_hash
3560 , p_array_source_code => p_array_source_code
3561 , p_array_lookup_type => p_array_lookup_type
3562 , p_array_view_application_id => p_array_view_application_id
3563 );
3564
3565 l_hdr_clauses := l_hdr_clauses || GenerateLookupClauses(
3566 p_array_source_index => p_array_h_mls_source_index
3567 , p_array_table_index => p_array_h_mls_table_index
3568 , p_array_table_name => p_array_table_name
3569 , p_array_table_hash => p_array_table_hash
3570 , p_array_source_code => p_array_source_code
3571 , p_array_lookup_type => p_array_lookup_type
3572 , p_array_view_application_id => p_array_view_application_id
3573 );
3574
3575 --
3576 -- generate the declaration of the header cursor
3577 --
3578 IF p_procedure = 'EVENT_TYPE' THEN
3579 l_hdr_cur := C_HDR_CUR_EVENT_TYPE;
3580 ELSE
3581 l_hdr_cur := C_HDR_CUR_EVENT_CLASS;
3582 END IF;
3583
3587 THEN
3584 IF l_hdr_sources IS NOT NULL AND
3585 l_hdr_tabs IS NOT NULL AND
3586 l_hdr_clauses IS NOT NULL
3588
3589 l_hdr_cur := REPLACE(l_hdr_cur,'$hdr_sources$',l_hdr_sources);
3590 l_hdr_cur := REPLACE(l_hdr_cur,'$hdr_tabs$' ,l_hdr_tabs);
3591 l_hdr_cur := REPLACE(l_hdr_cur,'$hdr_clauses$',l_hdr_clauses);
3592
3593 ELSE
3594 -- l_hdr_cur := NULL;
3595 l_hdr_cur := REPLACE(l_hdr_cur,'$hdr_sources$',' ');
3596 l_hdr_cur := REPLACE(l_hdr_cur,'$hdr_tabs$' ,' ');
3597 l_hdr_cur := REPLACE(l_hdr_cur,'$hdr_clauses$',' ');
3598
3599 END IF;
3600
3601 l_hdr_sources := NULL;
3602 l_hdr_tabs := NULL;
3603 l_hdr_clauses := NULL;
3604
3605 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3606 trace
3607 (p_msg => 'l_hdr_cur: ' || SUBSTRB(l_hdr_cur,1,3989)
3608 ,p_level => C_LEVEL_PROCEDURE
3609 ,p_module => l_log_module);
3610 END IF;
3611
3612 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3613 trace
3614 (p_msg => 'END of GenerateHeaderCursor'
3615 ,p_level => C_LEVEL_PROCEDURE
3616 ,p_module => l_log_module);
3617 END IF;
3618
3619 RETURN l_hdr_cur;
3620 EXCEPTION
3621 WHEN xla_exceptions_pkg.application_exception THEN
3622 RETURN NULL;
3623 WHEN OTHERS THEN
3624 xla_exceptions_pkg.raise_message
3625 (p_location => 'xla_cmp_extract_pkg.GenerateHeaderCursor');
3626 END GenerateHeaderCursor;
3627 --
3628 --+==========================================================================+
3629 --| |
3630 --| PRIVATE function |
3631 --| |
3632 --| Generate the declaration of the Line Cursor : The Extract of |
3633 --| standard BC and MLS line sources from the Header Extract Object |
3634 --| |
3635 --| |
3636 --+==========================================================================+
3637 --
3638 FUNCTION GenerateLineCursor
3639 (p_application_id IN NUMBER
3640 ,p_array_table_name IN xla_cmp_source_pkg.t_array_VL30
3641 ,p_array_parent_table_index IN xla_cmp_source_pkg.t_array_ByInt
3642 ,p_array_table_hash IN xla_cmp_source_pkg.t_array_VL30
3643 ,p_array_populated_flag IN xla_cmp_source_pkg.t_array_VL1
3644 ,p_array_ref_obj_flag IN xla_cmp_source_pkg.t_array_VL1
3645 ,p_array_join_condition IN xla_cmp_source_pkg.t_array_VL2000
3646 ,p_array_l_source_index IN xla_cmp_source_pkg.t_array_ByInt
3647 ,p_array_l_table_index IN xla_cmp_source_pkg.t_array_ByInt
3648 ,p_array_l_mls_source_index IN xla_cmp_source_pkg.t_array_ByInt
3649 ,p_array_l_mls_table_index IN xla_cmp_source_pkg.t_array_ByInt
3650 ,p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
3651 ,p_array_lookup_type IN xla_cmp_source_pkg.t_array_VL30
3652 ,p_array_view_application_id IN xla_cmp_source_pkg.t_array_Num
3653 ,p_procedure IN VARCHAR2)
3654 RETURN VARCHAR2 IS
3655
3656 l_line_cur VARCHAR2(32000);
3657 l_line_sources VARCHAR2(20000);
3658 l_line_tabs VARCHAR2(20000);
3659 l_line_clauses VARCHAR2(20000);
3660 --
3661 l_line_source VARCHAR2(1000);
3662 l_line_tab VARCHAR2(1000);
3663 l_line_clause VARCHAR2(1000);
3664 l_line_ref_clause VARCHAR2(1000);
3665 --
3666 l_l_count BINARY_INTEGER;
3667 l_l_mls_count BINARY_INTEGER;
3668 --
3669 --
3670 l_array_l_tab xla_cmp_source_pkg.t_array_ByInt;
3671 l_array_l_mls_tab xla_cmp_source_pkg.t_array_ByInt;
3672 --
3673 l_first_tab BINARY_INTEGER;
3674 l_first_mls_tab BINARY_INTEGER;
3675 --
3676 l_called BOOLEAN;
3677 --
3678 C_LOOKUP_TAB CONSTANT VARCHAR2(100) :=', fnd_lookup_values fvl$Index$';
3679
3683 AND fvl$Index$.view_application_id(+) = $view_application_id$
3680 C_LOOKUP_CLAUSE CONSTANT VARCHAR2(1000):='
3681 AND fvl$Index$.lookup_type(+) = ''$lookup_type$''
3682 AND fvl$Index$.lookup_code(+) = $tab$.$source$
3684 AND fvl$Index$.language(+) = USERENV(''LANG'') '
3685 ;
3686
3687 C_LINE_NUMBER CONSTANT VARCHAR2(100) :=' , $tab$.LINE_NUMBER ';
3688 --
3689 cursor c_alc_enabled_flag is
3690 select alc_enabled_flag
3691 from xla_subledgers
3692 where application_id = p_application_id;
3693
3694 l_alc_enabled_flag VARCHAR2(1);
3695 l_log_module VARCHAR2(240);
3696 --
3697 BEGIN
3698 IF g_log_enabled THEN
3699 l_log_module := C_DEFAULT_MODULE||'.GenerateLineCursor';
3700 END IF;
3701
3702 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3703 trace
3704 (p_msg => 'BEGIN of GenerateLineCursor'
3705 ,p_level => C_LEVEL_PROCEDURE
3706 ,p_module => l_log_module);
3707 END IF;
3708
3709 open c_alc_enabled_flag;
3710 fetch c_alc_enabled_flag into l_alc_enabled_flag;
3711 close c_alc_enabled_flag;
3712
3713 l_l_count := NVL(p_array_l_source_index.COUNT,0);
3714 l_l_mls_count := NVL(p_array_l_mls_source_index.COUNT,0);
3715
3716 l_first_tab := NULL;
3717 l_first_mls_tab := NULL;
3718 l_called := FALSE;
3719
3720 -- l_line_sources := ' , $tab$.LINE_NUMBER ';
3721
3722 --
3723 -- get list of standard line table
3724 --
3725 IF l_l_count > 0 THEN
3726 GetUsedExtractObject
3727 (p_array_table_index => p_array_l_table_index
3728 ,p_array_parent_table_index => p_array_parent_table_index
3729 ,p_array_diff_table_index => l_array_l_tab);
3730
3731 --
3732 -- Get the line always populated extract object
3733 --
3734 l_first_tab :=
3735 GetAnAlwaysPopulatedObject
3736 (p_array_table_index => l_array_l_tab
3737 ,p_array_populated_flag => p_array_populated_flag
3738 ,p_array_ref_obj_flag => p_array_ref_obj_flag);
3739
3740 --
3741 -- Use the following to derive the line number, and avoid the loop
3742 --
3743 l_line_sources := REPLACE(C_LINE_NUMBER,'$tab$',
3744 p_array_table_hash(l_first_tab));
3745
3746 -- Commented the below code for bug 5478323
3747 --
3748 /*
3749 FOR Idx IN l_array_l_tab.FIRST .. l_array_l_tab.LAST LOOP
3750 --
3751 IF l_array_l_tab.EXISTS(Idx) THEN
3752
3753 IF NOT l_called
3754 AND NVL(p_array_ref_obj_flag(Idx),'N') = 'N' THEN
3755
3756 l_line_sources := REPLACE(C_LINE_NUMBER,'$tab$',
3757 p_array_table_hash(Idx));
3758
3759 l_called := TRUE;
3760
3761 END IF;
3762
3763 END IF;
3764 END LOOP;
3765 */
3766
3767
3768 l_line_sources :=
3769 l_line_sources ||
3770 GenerateExtractColumns
3771 (p_array_table_hash => p_array_table_hash
3772 ,p_array_source_code => p_array_source_code
3773 ,p_array_source_index => p_array_l_source_index
3774 ,p_array_table_index => p_array_l_table_index
3775 ,p_array_lookup_type => p_array_lookup_type
3779 --
3776 ,p_array_view_application_id => p_array_view_application_id);
3777 END IF;
3778
3780 -- get list of mls line table
3781 --
3782 IF l_l_mls_count > 0 THEN
3783 GetUsedExtractObject
3784 (p_array_table_index => p_array_l_mls_table_index
3785 ,p_array_parent_table_index => p_array_parent_table_index
3786 ,p_array_diff_table_index => l_array_l_mls_tab);
3787
3788 --
3789 -- Get the MLS line always populated extract object
3790 --
3791 l_first_mls_tab :=
3792 GetAnAlwaysPopulatedObject
3793 (p_array_table_index => l_array_l_mls_tab
3794 ,p_array_populated_flag => p_array_populated_flag
3795 ,P_array_ref_obj_flag => p_array_ref_obj_flag);
3796
3797 --
3798 -- extract line mls sources
3799 --
3800 l_line_sources :=
3801 l_line_sources ||
3802 GenerateExtractColumns
3803 (p_array_table_hash => p_array_table_hash
3804 ,p_array_source_code => p_array_source_code
3805 ,p_array_source_index => p_array_l_mls_source_index
3806 ,p_array_table_index => p_array_l_mls_table_index
3807 ,p_array_lookup_type => p_array_lookup_type
3808 ,p_array_view_application_id => p_array_view_application_id) ;
3809 END IF;
3810
3811 --
3812 --
3813 -- generate first clause
3814 --
3815 IF l_first_tab IS NOT NULL AND
3816 l_first_mls_tab IS NOT NULL
3817 THEN
3818 l_line_clause :=
3819 ' AND $first_tab$.event_id = xet.event_id' ||g_chr_newline||
3820 ' AND $first_tab$.event_id = $first_mls_tab$.event_id' ||g_chr_newline||
3821 ' AND $first_tab$.line_number = $first_mls_tab$.line_number'||g_chr_newline||
3822 ' AND $first_mls_tab$.language = p_language' ||g_chr_newline;
3823 IF(l_alc_enabled_flag = 'N') THEN
3824 l_line_clause := l_line_clause ||
3825 ' AND $first_tab$.ledger_id = p_sla_ledger_id' ||g_chr_newline;
3826 END IF;
3827
3828 l_line_clause := REPLACE(l_line_clause,'$first_tab$',p_array_table_hash(l_first_tab));
3829 l_line_clause := REPLACE(l_line_clause,'$first_mls_tab$', p_array_table_hash(l_first_mls_tab));
3830
3831 l_line_clauses := l_line_clauses ||l_line_clause;
3832 l_line_clause := NULL;
3833
3834 ELSIF l_first_tab IS NOT NULL AND
3835 l_first_mls_tab IS NULL
3836 THEN
3837 l_line_clause :=
3838 ' AND $first_tab$.event_id = xet.event_id' ||g_chr_newline;
3839 IF(l_alc_enabled_flag = 'N') THEN
3840 l_line_clause := l_line_clause ||
3841 ' AND $first_tab$.ledger_id = p_sla_ledger_id' ||g_chr_newline;
3842 END IF;
3843
3844 l_line_clause := REPLACE(l_line_clause,'$first_tab$',p_array_table_hash(l_first_tab));
3845 l_line_clauses := l_line_clauses ||l_line_clause;
3846 l_line_clause := NULL;
3847
3848 ELSIF l_first_tab IS NULL AND
3849 l_first_mls_tab IS NOT NULL
3850 THEN
3851 l_line_clause :=
3852 ' AND $first_mls_tab$.event_id = xet.event_id'||g_chr_newline||
3853 ' AND $first_mls_tab$.language = p_language' ||g_chr_newline;
3854
3855 l_line_clause := REPLACE(l_line_clause,'$first_mls_tab$', p_array_table_hash(l_first_mls_tab));
3856 l_line_clauses := l_line_clauses || l_line_clause;
3857 l_line_clause := NULL;
3858 END IF;
3859
3860 IF l_array_l_tab.COUNT > 0 THEN
3861 FOR Idx IN l_array_l_tab.FIRST .. l_array_l_tab.LAST LOOP
3862 IF l_array_l_tab.EXISTS(Idx) THEN
3863
3864 l_line_tab := ' , $table_name$ $tab$' ;
3865
3866 l_line_tab := REPLACE(l_line_tab, '$table_name$', p_array_table_name(Idx));
3867 l_line_tab := REPLACE(l_line_tab, '$tab$', p_array_table_hash(Idx));
3868 l_line_tabs := l_line_tabs || g_chr_newline || l_line_tab;
3869 l_line_tab := NULL;
3870
3871
3872 IF NVL(p_array_ref_obj_flag(Idx),'N') = 'N' THEN
3873
3874 IF Idx <> NVL(l_first_tab,-1) AND
3875 nvl(p_array_populated_flag(Idx),'N') = 'Y'
3876 THEN
3877 l_line_clause :=
3878 ' AND $tab$.event_id = $first_tab$.event_id' ||g_chr_newline||
3879 ' AND $tab$.line_number = $first_tab$.line_number'||g_chr_newline;
3880
3881 ELSIF Idx <> NVL(l_first_tab,-1) AND
3882 nvl(p_array_populated_flag(Idx),'N') = 'N'
3883 THEN
3884 l_line_clause :=
3885 ' AND $tab$.event_id (+) = $first_tab$.event_id' ||g_chr_newline||
3886 ' AND $tab$.line_number (+) = $first_tab$.line_number'||g_chr_newline;
3887
3888 END IF;
3889
3890 IF l_first_tab IS NOT NULL OR
3891 l_first_mls_tab IS NOT NULL
3892 THEN
3893 l_line_clause :=
3894 REPLACE
3895 (l_line_clause,'$first_tab$'
3896 ,p_array_table_hash(NVL(l_first_tab,l_first_mls_tab)));
3900 (l_line_clause,'$first_tab$', 'xet');
3897 ELSE
3898 l_line_clause :=
3899 REPLACE
3901 END IF;
3902 ELSE -- reference objects
3903 IF nvl(p_array_populated_flag(Idx),'N') = 'Y' THEN
3904 l_line_ref_clause := p_array_join_condition(Idx);
3905
3906 --
3907 -- Replace object names with aliases
3908 --
3909 FOR j IN p_array_table_name.FIRST .. p_array_table_name.LAST LOOP
3910 l_line_ref_clause :=
3911 REGEXP_REPLACE(l_line_ref_clause
3912 ,p_array_table_name(j)
3913 ,p_array_table_hash(j)
3914 ,1 -- Position
3915 ,0 -- All Occurrences
3916 ,'im' -- i: case insensitive m: multiple lines
3917 );
3918 END LOOP;
3919
3920 l_line_ref_clause := ' AND ' || l_line_ref_clause;
3921
3922 ELSE -- Always populated flag = 'N'
3923
3924 l_line_ref_clause := AddOuterJoinOps (
3925 p_join_condition => p_array_join_condition(Idx)
3926 ,p_ref_obj_name => p_array_table_name(Idx));
3927
3928 --
3929 -- Replace object names with aliases
3930 --
3931 FOR j IN p_array_table_name.FIRST .. p_array_table_name.LAST LOOP
3932 l_line_ref_clause :=
3933 REGEXP_REPLACE(l_line_ref_clause
3934 ,p_array_table_name(j)
3935 ,p_array_table_hash(j)
3936 ,1 -- Position
3937 ,0 -- All Occurrences
3938 ,'im' -- i: case insensitive m: multiple lines
3939 );
3940 END LOOP;
3941
3942 l_line_ref_clause := ' AND ' || l_line_ref_clause;
3943
3944 END IF;
3945 END IF;
3946 --
3947 l_line_clause := REPLACE(l_line_clause,'$tab$', p_array_table_hash(Idx));
3948 l_line_sources := REPLACE(l_line_sources,'$tab$', p_array_table_hash(Idx));
3949 l_line_clauses := l_line_clauses || l_line_clause || l_line_ref_clause;
3950 l_line_clause := NULL;
3951 l_line_ref_clause := NULL;
3952 END IF;
3953 END LOOP;
3954 END IF;
3955
3956 --
3957 -- MLS extract where clauses
3958 --
3959 IF l_array_l_mls_tab.COUNT > 0 THEN
3960 FOR Idx IN l_array_l_mls_tab.FIRST .. l_array_l_mls_tab.LAST LOOP
3961 IF l_array_l_mls_tab.EXISTS(Idx) THEN
3962
3963 l_line_tab := ' , $table_name$ $tab$' ;
3964
3965 l_line_tab := REPLACE(l_line_tab, '$table_name$', p_array_table_name(Idx));
3966 l_line_tab := REPLACE(l_line_tab, '$tab$', p_array_table_hash(Idx));
3967 l_line_tabs := l_line_tabs || g_chr_newline || l_line_tab;
3968 l_line_tab := NULL;
3969 --
3970 IF Idx <> NVL(l_first_mls_tab,-1) AND
3971 nvl(p_array_populated_flag(Idx),'N') = 'Y'
3972 THEN
3973 l_line_clause :=
3974 ' AND $tab$.event_id = $first_tab$.event_id' ||g_chr_newline||
3975 ' AND $tab$.line_number = $first_tab$.line_number' ||g_chr_newline||
3976 ' AND $tab$.language = p_language' ||g_chr_newline;
3977
3978 ELSIF Idx <> NVL(l_first_mls_tab,-1) AND
3979 nvl(p_array_populated_flag(Idx),'N') = 'N'
3980 THEN
3981 l_line_clause :=
3982 ' AND $tab$.event_id (+) = $first_tab$.event_id' ||g_chr_newline||
3983 ' AND $tab$.line_number (+) = $first_tab$.line_number'||g_chr_newline||
3984 ' AND $tab$.language (+) = p_language' ||g_chr_newline;
3985
3986 END IF;
3987
3988 IF l_first_tab IS NOT NULL OR
3989 l_first_mls_tab IS NOT NULL
3990 THEN
3991 l_line_clause :=
3992 REPLACE
3993 (l_line_clause,'$first_tab$'
3994 ,p_array_table_hash(NVL(l_first_tab,l_first_mls_tab)));
3995 ELSE
3996 l_line_clause :=
3997 REPLACE
3998 (l_line_clause,'$first_tab$', 'xet');
3999 END IF;
4000
4001 l_line_clause := REPLACE(l_line_clause,'$tab$', p_array_table_hash(Idx));
4002 l_line_sources := REPLACE(l_line_sources,'$tab$', p_array_table_hash(Idx));
4003 l_line_clauses := l_line_clauses || l_line_clause;
4004 l_line_clause := NULL;
4005 END IF;
4006 END LOOP;
4007 END IF;
4008
4009 --
4010 -- generate the where clauses for extract of lookup sources
4011 --
4012 l_line_tabs := l_line_tabs || GenerateLookupTables(
4013 p_array_source_index => p_array_l_source_index
4017 );
4014 , p_array_table_index => p_array_l_table_index
4015 , p_array_lookup_type => p_array_lookup_type
4016 , p_array_view_application_id => p_array_view_application_id
4018 --
4019 l_line_tabs := l_line_tabs || GenerateLookupTables(
4020 p_array_source_index => p_array_l_mls_source_index
4021 , p_array_table_index => p_array_l_mls_table_index
4022 , p_array_lookup_type => p_array_lookup_type
4023 , p_array_view_application_id => p_array_view_application_id
4024 );
4025 --
4026 l_line_clauses := l_line_clauses || GenerateLookupClauses(
4027 p_array_source_index => p_array_l_source_index
4028 , p_array_table_index => p_array_l_table_index
4029 , p_array_table_name => p_array_table_name
4030 , p_array_table_hash => p_array_table_hash
4031 , p_array_source_code => p_array_source_code
4032 , p_array_lookup_type => p_array_lookup_type
4033 , p_array_view_application_id => p_array_view_application_id
4034 );
4035 --
4036 l_line_clauses := l_line_clauses || GenerateLookupClauses(
4037 p_array_source_index => p_array_l_mls_source_index
4038 , p_array_table_index => p_array_l_mls_table_index
4039 , p_array_table_name => p_array_table_name
4040 , p_array_table_hash => p_array_table_hash
4041 , p_array_source_code => p_array_source_code
4042 , p_array_lookup_type => p_array_lookup_type
4043 , p_array_view_application_id => p_array_view_application_id
4044 );
4045 --
4046 IF p_procedure = 'EVENT_TYPE' THEN
4047 l_line_cur := C_LINE_CUR_EVENT_TYPE;
4048 ELSE
4049 l_line_cur := C_LINE_CUR_EVENT_CLASS;
4050 END IF;
4051
4052 IF l_line_sources IS NOT NULL AND
4053 l_line_tabs IS NOT NULL AND
4054 l_line_clauses IS NOT NULL
4055 THEN
4056 --
4057 l_line_cur := REPLACE(l_line_cur,'$line_sources$',l_line_sources);
4058 l_line_cur := REPLACE(l_line_cur,'$line_tabs$' ,l_line_tabs);
4059 l_line_cur := REPLACE(l_line_cur,'$line_clauses$',l_line_clauses);
4060 --
4061 ELSE
4062 --
4063 -- l_line_cur := NULL; -> bug 4492149
4064 l_line_cur := REPLACE(l_line_cur,'$line_sources$',' , 0 ');
4065 l_line_cur := REPLACE(l_line_cur,'$line_tabs$' ,' ');
4066 l_line_cur := REPLACE(l_line_cur,'$line_clauses$',' ');
4067 --
4068 END IF;
4069 --
4070 l_line_sources := NULL;
4071 l_line_tabs := NULL;
4072 l_line_clauses := NULL;
4073
4074 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4075
4076 trace
4077 (p_msg => 'l_line_cur: ' || SUBSTRB(l_line_cur,1,3988)
4078 ,p_level => C_LEVEL_PROCEDURE
4079 ,p_module => l_log_module);
4080
4081 END IF;
4082
4083
4084 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4085
4086 trace
4087 (p_msg => 'END of GenerateLineCursor'
4088 ,p_level => C_LEVEL_PROCEDURE
4089 ,p_module => l_log_module);
4090
4091 END IF;
4092 --
4093 RETURN l_line_cur;
4094 EXCEPTION
4095 WHEN xla_exceptions_pkg.application_exception THEN
4096 RETURN NULL;
4097 WHEN OTHERS THEN
4098 xla_exceptions_pkg.raise_message
4099 (p_location => 'xla_cmp_extract_pkg.GenerateLineCursor ');
4100 END GenerateLineCursor;
4101 --
4102 --
4103 --+==========================================================================+
4104 --| |
4105 --| PRIVATE function |
4106 --| |
4107 --| |
4108 --| |
4109 --+==========================================================================+
4110 --
4111 FUNCTION GenerateHdrStructure
4112 (p_array_table_name IN xla_cmp_source_pkg.t_array_VL30
4113 ,p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
4114 ,p_array_h_source_index IN xla_cmp_source_pkg.t_array_ByInt
4115 ,p_array_h_table_index IN xla_cmp_source_pkg.t_array_ByInt
4116 ,p_array_h_mls_source_index IN xla_cmp_source_pkg.t_array_ByInt
4117 ,p_array_h_mls_table_index IN xla_cmp_source_pkg.t_array_ByInt)
4118 RETURN VARCHAR2 IS
4119
4120 C_STRUCTURE CONSTANT VARCHAR2(2000):=
4121 'TYPE t_array_source_$Index$ IS TABLE OF $table$.$column$%TYPE INDEX BY BINARY_INTEGER;';
4122
4123 l_HdrTypes VARCHAR2(32000);
4124 l_HdrType VARCHAR2(2000);
4125 l_log_module VARCHAR2(240);
4126 --
4127 BEGIN
4128 IF g_log_enabled THEN
4129 l_log_module := C_DEFAULT_MODULE||'.GenerateHdrStructure';
4130 END IF;
4131
4132 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4133 trace
4137
4134 (p_msg => 'BEGIN of GenerateHdrStructure'
4135 ,p_level => C_LEVEL_PROCEDURE
4136 ,p_module => l_log_module);
4138 END IF;
4139
4140 l_HdrTypes := NULL;
4141 --
4142 -- structure of standard header sources
4143 --
4144 IF p_array_h_source_index. COUNT > 0 THEN
4145 FOR Idx IN p_array_h_source_index.FIRST .. p_array_h_source_index.LAST LOOP
4146 IF p_array_h_source_index.EXISTS(Idx) THEN
4147 l_HdrType := C_STRUCTURE;
4148 l_HdrType := REPLACE(l_HdrType,'$Index$' , Idx);
4149 l_HdrType := REPLACE(l_HdrType,'$table$' ,
4150 p_array_table_name(p_array_h_table_index(Idx)));
4151 l_HdrType := REPLACE(l_HdrType,'$column$', p_array_source_code(Idx));
4152 l_HdrTypes := l_HdrTypes ||g_chr_newline || l_HdrType ;
4153 END IF;
4154 END LOOP;
4155 END IF;
4156
4157 --
4158 -- structure of mls line sources
4159 --
4160 IF p_array_h_mls_source_index.COUNT > 0 THEN
4161 FOR Idx IN p_array_h_mls_source_index.FIRST .. p_array_h_mls_source_index.LAST LOOP
4162 IF p_array_h_mls_source_index.EXISTS(Idx) THEN
4163 l_HdrType := C_STRUCTURE;
4164 l_HdrType := REPLACE(l_HdrType,'$Index$' , Idx);
4165 l_HdrType := REPLACE(l_HdrType,'$table$' ,
4166 p_array_table_name(p_array_h_mls_table_index(Idx)));
4167 l_HdrType := REPLACE(l_HdrType,'$column$', p_array_source_code(Idx));
4168 l_HdrTypes := l_HdrTypes || g_chr_newline || l_HdrType ;
4169 END IF;
4170 END LOOP;
4171 END IF;
4172
4173 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4174 trace
4175 (p_msg => 'END of GenerateHdrStructure'
4176 ,p_level => C_LEVEL_PROCEDURE
4177 ,p_module => l_log_module);
4178 END IF;
4179
4180 RETURN l_HdrTypes;
4181 EXCEPTION
4182 WHEN xla_exceptions_pkg.application_exception THEN
4183 RETURN NULL;
4184 WHEN OTHERS THEN
4185 xla_exceptions_pkg.raise_message
4186 (p_location => 'xla_cmp_extract_pkg.GenerateHdrStructure ');
4187 END GenerateHdrStructure;
4188 --
4189 --
4190 --+==========================================================================+
4191 --| |
4192 --| PRIVATE function |
4193 --| |
4194 --| |
4195 --| |
4196 --+==========================================================================+
4197 FUNCTION GenerateCacheHdrSources
4198 (p_array_h_source_index IN xla_cmp_source_pkg.t_array_ByInt
4199 ,p_array_h_mls_source_index IN xla_cmp_source_pkg.t_array_ByInt
4200 ,p_array_lookup_type IN xla_cmp_source_pkg.t_array_VL30
4201 ,p_array_view_application_id IN xla_cmp_source_pkg.t_array_Num
4202 ,p_array_datatype_code IN OUT NOCOPY xla_cmp_source_pkg.t_array_VL1)
4203
4204 RETURN VARCHAR2 IS
4205 C_SOURCE CONSTANT VARCHAR2(2000) :='g_array_event(l_event_id).array_value_$datatype$(''source_$index$'') := l_array_source_$index$(hdr_idx);';
4206 C_SOURCE_LKP CONSTANT VARCHAR2(2000) :='g_array_event(l_event_id).array_value_char(''source_$index$_meaning'') := l_array_source_$index$_meaning(hdr_idx);';
4207
4208 l_HdrStrings VARCHAR2(32000);
4209 l_one_var VARCHAR2(2000);
4210 l_log_module VARCHAR2(240);
4211
4212 BEGIN
4213 IF g_log_enabled THEN
4214 l_log_module := C_DEFAULT_MODULE||'.GenerateCacheHdrSources';
4215 END IF;
4216
4217 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4218 trace
4219 (p_msg => 'BEGIN of GenerateCacheHdrSources'
4220 ,p_level => C_LEVEL_PROCEDURE
4221 ,p_module => l_log_module);
4222 END IF;
4223
4224 --
4225 -- declare the standard header variables
4226 --
4227 l_one_var := NULL;
4228 l_HdrStrings := l_one_var ;
4229
4230 IF p_array_h_source_index.COUNT > 0 THEN
4231 FOR Idx IN p_array_h_source_index.FIRST .. p_array_h_source_index.LAST LOOP
4232 IF p_array_h_source_index.EXISTS(Idx) THEN
4233 l_one_var := C_SOURCE;
4234
4238 p_array_view_application_id(Idx) IS NOT NULL
4235 IF p_array_lookup_type.EXISTS(Idx) AND
4236 p_array_lookup_type(Idx) IS NOT NULL AND
4237 p_array_view_application_id.EXISTS(Idx) AND
4239 THEN
4240 l_one_var := l_one_var|| g_chr_newline ||C_SOURCE_LKP;
4241 END IF;
4242 l_one_var := REPLACE(l_one_var,'$index$' ,to_char(Idx));
4243
4244 case p_array_datatype_code(Idx)
4245 when 'F' then
4246 l_one_var := REPLACE(l_one_var,'$datatype$','num') ;
4247 when 'N' then
4248 l_one_var := REPLACE(l_one_var,'$datatype$','num') ;
4249 when 'C' then
4250 l_one_var := REPLACE(l_one_var,'$datatype$','char') ;
4251 when 'D' then
4252 l_one_var := REPLACE(l_one_var,'$datatype$','date') ;
4253 else
4254 l_one_var := REPLACE(l_one_var,'$datatype$',p_array_datatype_code(Idx)) ;
4255 end case;
4256
4257
4258 l_HdrStrings := l_HdrStrings || g_chr_newline || l_one_var ;
4259 END IF;
4260 END LOOP;
4261 END IF;
4262
4263 --
4264 -- declare the mls line variables
4265 --
4266 IF p_array_h_mls_source_index.COUNT > 0 THEN
4267 FOR Idx IN p_array_h_mls_source_index.FIRST .. p_array_h_mls_source_index.LAST LOOP
4268 IF p_array_h_mls_source_index.EXISTS(Idx) THEN
4269 l_one_var := C_SOURCE;
4270
4271 IF p_array_lookup_type.EXISTS(Idx) AND
4272 p_array_lookup_type(Idx) IS NOT NULL AND
4273 p_array_view_application_id.EXISTS(Idx) AND
4274 p_array_view_application_id(Idx) IS NOT NULL
4275 THEN
4276 l_one_var := l_one_var|| g_chr_newline ||C_SOURCE_LKP;
4277 END IF;
4278 l_one_var := REPLACE(l_one_var,'$index$' , Idx);
4279
4280 case p_array_datatype_code(Idx)
4281 when 'F' then
4282 l_one_var := REPLACE(l_one_var,'$datatype$','num') ;
4283 when 'N' then
4284 l_one_var := REPLACE(l_one_var,'$datatype$','num') ;
4285 when 'C' then
4286 l_one_var := REPLACE(l_one_var,'$datatype$','char') ;
4287 when 'D' then
4288 l_one_var := REPLACE(l_one_var,'$datatype$','date') ;
4289 else
4290 l_one_var := REPLACE(l_one_var,'$datatype$',p_array_datatype_code(Idx)) ;
4291 end case;
4292
4293 l_HdrStrings := l_HdrStrings || g_chr_newline || l_one_var ;
4294 END IF;
4295 END LOOP;
4296 END IF;
4297
4298 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4299 trace
4300 (p_msg => 'END of GenerateCacheHdrSources'
4301 ,p_level => C_LEVEL_PROCEDURE
4302 ,p_module => l_log_module);
4303 END IF;
4304
4305 RETURN l_HdrStrings;
4306 EXCEPTION
4307 WHEN xla_exceptions_pkg.application_exception THEN
4308 RETURN NULL;
4309 WHEN OTHERS THEN
4310 xla_exceptions_pkg.raise_message
4311 (p_location => 'xla_cmp_extract_pkg.GenerateCacheHdrSources');
4312 END GenerateCacheHdrSources;
4313 --
4314 --===========================================================================
4315 --
4316 --
4317 --
4318 --
4319 --
4320 --
4321 --
4322 --
4323 --
4324 --
4325 -- Accounting Event Extract Diagnostics
4329 --
4326 --
4327 --
4328 --
4330 --
4331 --
4332 --
4333 --
4334 --
4335 --
4336 --
4337 --============================================================================
4338 --
4339 --+==========================================================================+
4340 --| |
4341 --| PRIVATE function |
4342 --| |
4343 --| Generate the in the header cursor the FROM expression : |
4344 --| (FROM tab1, tab2, tab3 ...) |
4345 --| |
4346 --+==========================================================================+
4347 --
4348 FUNCTION GenerateFromHdrTabs (
4349 --
4350 p_array_table_name IN xla_cmp_source_pkg.t_array_VL30
4351 , p_array_parent_table_index IN xla_cmp_source_pkg.t_array_ByInt
4352 , p_array_table_hash IN xla_cmp_source_pkg.t_array_VL30
4353 , p_array_populated_flag IN xla_cmp_source_pkg.t_array_VL1
4354 , p_array_ref_obj_flag IN xla_cmp_source_pkg.t_array_VL1
4355 --
4356 , p_array_h_source_index IN xla_cmp_source_pkg.t_array_ByInt
4357 , p_array_h_table_index IN xla_cmp_source_pkg.t_array_ByInt
4358 --
4359 , p_array_h_mls_source_index IN xla_cmp_source_pkg.t_array_ByInt
4360 , p_array_h_mls_table_index IN xla_cmp_source_pkg.t_array_ByInt
4361 --
4362 , p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
4363 , p_array_lookup_type IN xla_cmp_source_pkg.t_array_VL30
4364 , p_array_view_application_id IN xla_cmp_source_pkg.t_array_Num
4365 )
4366 RETURN VARCHAR2
4367 IS
4368 --
4369 l_hdr_tabs VARCHAR2(32000);
4370 l_hdr_tab VARCHAR2(10000);
4371 --
4372 l_h_count BINARY_INTEGER;
4373 l_h_mls_count BINARY_INTEGER;
4374 --
4375 l_array_h_tab xla_cmp_source_pkg.t_array_ByInt;
4376 l_array_h_mls_tab xla_cmp_source_pkg.t_array_ByInt;
4377 --
4378 l_first_tab BINARY_INTEGER;
4379 l_first_mls_tab BINARY_INTEGER;
4380 --
4381 l_log_module VARCHAR2(240);
4382 --
4383 BEGIN
4384 --
4385 IF g_log_enabled THEN
4386 l_log_module := C_DEFAULT_MODULE||'.GenerateFromHdrTabs';
4387 END IF;
4388 --
4389 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4390
4391 trace
4392 (p_msg => 'BEGIN of GenerateFromHdrTabs'
4393 ,p_level => C_LEVEL_PROCEDURE
4394 ,p_module => l_log_module);
4395
4396 END IF;
4397 --
4398 l_first_tab := NULL;
4399 l_first_mls_tab := NULL;
4400 --
4401 --
4402 l_h_count := NVL(p_array_h_source_index.COUNT ,0);
4403 l_h_mls_count := NVL(p_array_h_mls_source_index.COUNT,0);
4404 --
4405
4406 l_hdr_tabs := NULL;
4407 --
4408 --
4409 IF l_h_count > 0 THEN
4410 --
4411 -- get standard header tables/views
4412 --
4413 --
4414 GetUsedExtractObject( p_array_table_index => p_array_h_table_index
4415 ,p_array_parent_table_index => p_array_parent_table_index
4416 ,p_array_diff_table_index => l_array_h_tab
4417 );
4418
4419 --
4420 -- Get the header always populated extract object
4421 --
4422 l_first_tab := GetAnAlwaysPopulatedObject(
4423 p_array_table_index => l_array_h_tab
4424 , p_array_populated_flag => p_array_populated_flag
4425 , p_array_ref_obj_flag => p_array_ref_obj_flag
4426 );
4427
4428 END IF;
4429 --
4430 --
4431 --
4432 IF l_h_mls_count > 0 THEN
4433 --
4434 -- get mls header tables/views
4435 --
4436 GetUsedExtractObject( p_array_table_index => p_array_h_mls_table_index
4437 ,p_array_parent_table_index => p_array_parent_table_index
4438 ,p_array_diff_table_index => l_array_h_mls_tab
4439 );
4440 --
4441 -- Get the header mls always populated extract object
4442 --
4443 l_first_mls_tab := GetAnAlwaysPopulatedObject(
4447 );
4444 p_array_table_index => l_array_h_mls_tab
4445 , p_array_populated_flag => p_array_populated_flag
4446 , p_array_ref_obj_flag => p_array_ref_obj_flag
4448
4449 END IF;
4450 --
4451 --
4452 IF l_h_count > 0 THEN
4453
4454 FOR Idx IN l_array_h_tab.FIRST .. l_array_h_tab.LAST LOOP
4455
4456 IF l_array_h_tab.EXISTS(Idx) THEN
4457
4458 l_hdr_tab := ' , $table_name$ $tab$' ;
4459 --
4460 l_hdr_tab := REPLACE(l_hdr_tab, '$table_name$', p_array_table_name(Idx));
4461 l_hdr_tab := REPLACE(l_hdr_tab, '$tab$', p_array_table_hash(Idx));
4462 l_hdr_tabs := l_hdr_tabs || g_chr_newline || l_hdr_tab;
4463 l_hdr_tab := NULL;
4464 --
4465
4466 END IF;
4467
4468 END LOOP;
4469
4470 END IF;
4471 --
4472 --
4473 IF l_h_mls_count > 0 THEN
4474 --
4475 FOR Idx IN l_array_h_mls_tab.FIRST .. l_array_h_mls_tab.LAST LOOP
4476
4477 IF l_array_h_mls_tab.EXISTS(Idx) THEN
4478
4479 l_hdr_tab := ' , $table_name$ $tab$' ;
4480 --
4481 l_hdr_tab := REPLACE(l_hdr_tab, '$table_name$', p_array_table_name(Idx));
4482 l_hdr_tab := REPLACE(l_hdr_tab, '$tab$', p_array_table_hash(Idx));
4483 l_hdr_tabs := l_hdr_tabs ||g_chr_newline || l_hdr_tab;
4484 l_hdr_tab := NULL;
4485
4486 END IF;
4487
4488 END LOOP;
4489 --
4490 END IF;
4491
4492 --
4493 -- generate the extract of lookup sources
4494 --
4495
4496 l_hdr_tabs := l_hdr_tabs || GenerateLookupTables(
4497 p_array_source_index => p_array_h_source_index
4498 , p_array_table_index => p_array_h_table_index
4499 , p_array_lookup_type => p_array_lookup_type
4500 , p_array_view_application_id => p_array_view_application_id
4501 );
4502
4503
4504 l_hdr_tabs := l_hdr_tabs || GenerateLookupTables(
4505 p_array_source_index => p_array_h_mls_source_index
4506 , p_array_table_index => p_array_h_mls_table_index
4507 , p_array_lookup_type => p_array_lookup_type
4508 , p_array_view_application_id => p_array_view_application_id
4509 );
4510 --
4511 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4512
4513 trace
4514 (p_msg => 'END of GenerateFromHdrTabs'
4515 ,p_level => C_LEVEL_PROCEDURE
4516 ,p_module => l_log_module);
4517
4518 END IF;
4519 --
4520 RETURN l_hdr_tabs;
4521 EXCEPTION
4522 WHEN xla_exceptions_pkg.application_exception THEN
4523 RETURN NULL;
4524 WHEN OTHERS THEN
4525 xla_exceptions_pkg.raise_message
4526 (p_location => 'xla_cmp_extract_pkg.GenerateFromHdrTabs ');
4527 END GenerateFromHdrTabs;
4528 --
4529 --
4530 --+==========================================================================+
4531 --| |
4532 --| Private function |
4533 --| |
4534 --| Generate the in the header cursor the WHERE CLAUSES : |
4535 --| (WHERE col1 = col2 AND ...) |
4536 --| |
4537 --| |
4538 --+==========================================================================+
4539 --
4540 FUNCTION GenerateHdrWhereClause (
4541 --
4542 p_array_table_name IN xla_cmp_source_pkg.t_array_VL30
4543 , p_array_parent_table_index IN xla_cmp_source_pkg.t_array_ByInt
4544 , p_array_table_hash IN xla_cmp_source_pkg.t_array_VL30
4545 , p_array_populated_flag IN xla_cmp_source_pkg.t_array_VL1
4546 --
4547 , p_array_ref_obj_flag IN xla_cmp_source_pkg.t_array_VL1
4548 , p_array_join_condition IN xla_cmp_source_pkg.t_array_VL2000
4549 --
4550 , p_array_h_source_index IN xla_cmp_source_pkg.t_array_ByInt
4551 , p_array_h_table_index IN xla_cmp_source_pkg.t_array_ByInt
4552 --
4553 , p_array_h_mls_source_index IN xla_cmp_source_pkg.t_array_ByInt
4554 , p_array_h_mls_table_index IN xla_cmp_source_pkg.t_array_ByInt
4555 --
4556 , p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
4557 , p_array_lookup_type IN xla_cmp_source_pkg.t_array_VL30
4558 , p_array_view_application_id IN xla_cmp_source_pkg.t_array_Num
4559 )
4560 RETURN VARCHAR2
4561 IS
4562 --
4563 l_hdr_clauses VARCHAR2(32000);
4564 --
4565 l_hdr_clause VARCHAR2(10000);
4566 --
4567 l_hdr_ref_clause VARCHAR2(10000);
4568 --
4569 l_h_count BINARY_INTEGER;
4570 l_h_mls_count BINARY_INTEGER;
4571 --
4572 l_array_h_tab xla_cmp_source_pkg.t_array_ByInt;
4573 l_array_h_mls_tab xla_cmp_source_pkg.t_array_ByInt;
4574 --
4575 l_first_tab BINARY_INTEGER;
4576 l_first_mls_tab BINARY_INTEGER;
4577 --
4578 l_log_module VARCHAR2(240);
4579 --
4580 BEGIN
4581 --
4582 IF g_log_enabled THEN
4586 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4583 l_log_module := C_DEFAULT_MODULE||'.GenerateHdrWhereClause';
4584 END IF;
4585 --
4587
4588 trace
4589 (p_msg => 'BEGIN of GenerateHdrWhereClause'
4590 ,p_level => C_LEVEL_PROCEDURE
4591 ,p_module => l_log_module);
4592
4593 END IF;
4594 --
4595 l_first_tab := NULL;
4596 l_first_mls_tab := NULL;
4597 --
4598 l_h_count := NVL(p_array_h_source_index.COUNT ,0);
4599 l_h_mls_count := NVL(p_array_h_mls_source_index.COUNT,0);
4600 --
4601 l_hdr_clauses := NULL;
4602 --
4603 --
4604 IF l_h_count > 0 THEN
4605 --
4606 -- get standard header tables/views
4607 --
4608 --
4609 GetUsedExtractObject( p_array_table_index => p_array_h_table_index
4610 ,p_array_parent_table_index => p_array_parent_table_index
4611 ,p_array_diff_table_index => l_array_h_tab
4612 );
4613
4614 --
4615 -- Get the header always populated extract object
4616 --
4617 l_first_tab := GetAnAlwaysPopulatedObject(
4618 p_array_table_index => l_array_h_tab
4619 , p_array_populated_flag => p_array_populated_flag
4620 , p_array_ref_obj_flag => p_array_ref_obj_flag
4621 );
4622 --
4623 END IF;
4624 --
4625 --
4626 --
4627 IF l_h_mls_count > 0 THEN
4628 --
4629 -- get mls header tables/views
4630 --
4631 GetUsedExtractObject( p_array_table_index => p_array_h_mls_table_index
4632 ,p_array_parent_table_index => p_array_parent_table_index
4633 ,p_array_diff_table_index => l_array_h_mls_tab
4634 );
4635 --
4636 -- Get the header mls always populated extract object
4637 --
4638 l_first_mls_tab := GetAnAlwaysPopulatedObject(
4639 p_array_table_index => l_array_h_mls_tab
4640 , p_array_populated_flag => p_array_populated_flag
4641 , p_array_ref_obj_flag => p_array_ref_obj_flag
4642 );
4643
4644 END IF;
4645 --
4646 -- generate first clause
4647 --
4648 IF l_first_tab IS NOT NULL AND l_first_mls_tab IS NOT NULL THEN
4649
4650 l_hdr_clause := ' AND $first_tab$.event_id = xet.event_id' ||g_chr_newline
4651 ||' AND $first_tab$.event_id = $first_mls_tab$.event_id'||g_chr_newline
4652 ||' AND $first_mls_tab$.language = p_language' ||g_chr_newline
4653 ;
4654
4655 l_hdr_clause := REPLACE(l_hdr_clause,'$first_tab$',p_array_table_hash(l_first_tab));
4656 l_hdr_clause := REPLACE(l_hdr_clause,'$first_mls_tab$', p_array_table_hash(l_first_mls_tab));
4657 l_hdr_clauses := l_hdr_clauses || l_hdr_clause;
4658 l_hdr_clause := NULL;
4659 --
4660 ELSIF l_first_tab IS NOT NULL AND l_first_mls_tab IS NULL THEN
4661
4662 l_hdr_clause := ' AND $first_tab$.event_id = xet.event_id' || g_chr_newline
4663 ;
4664 l_hdr_clause := REPLACE(l_hdr_clause,'$first_tab$',p_array_table_hash(l_first_tab));
4665 l_hdr_clauses := l_hdr_clauses || l_hdr_clause;
4666 l_hdr_clause := NULL;
4667
4668 ELSIF l_first_tab IS NULL AND l_first_mls_tab IS NOT NULL THEN
4669
4670 l_hdr_clause := ' AND $first_mls_tab$.event_id = xet.event_id'|| g_chr_newline
4671 || ' AND $first_mls_tab$.language = p_language'|| g_chr_newline
4672 ;
4673 --
4674 l_hdr_clause := REPLACE(l_hdr_clause,'$first_mls_tab$', p_array_table_hash(l_first_mls_tab));
4675 l_hdr_clauses := l_hdr_clauses ||l_hdr_clause;
4676 l_hdr_clause := NULL;
4677
4678 END IF;
4679 --
4680 --
4681 IF l_h_count > 0 THEN
4682
4683 FOR Idx IN l_array_h_tab.FIRST .. l_array_h_tab.LAST LOOP
4684
4685 IF l_array_h_tab.EXISTS(Idx) THEN
4686 --
4687 IF NVL(p_array_ref_obj_flag (Idx),'N') = 'N' THEN
4688
4689 IF Idx <> NVL(l_first_tab,-1)
4690 AND nvl(p_array_populated_flag(Idx),'N') = 'Y'
4691 AND l_first_tab IS NOT NULL
4692 THEN
4693
4694 l_hdr_clause := ' AND $tab$.event_id = $first_tab$.event_id' || g_chr_newline
4695 ;
4696 l_hdr_clause := REPLACE(l_hdr_clause,'$first_tab$',p_array_table_hash(l_first_tab));
4697
4698 ELSIF Idx <> NVL(l_first_tab,-1) AND
4699 nvl(p_array_populated_flag(Idx),'N') = 'N' AND
4700 l_first_tab IS NOT NULL THEN
4701
4702 l_hdr_clause := ' AND $tab$.event_id (+) = $first_tab$.event_id' || g_chr_newline
4703 ;
4704 l_hdr_clause := REPLACE(l_hdr_clause,'$first_tab$',p_array_table_hash(l_first_tab));
4705
4709 THEN
4706 ELSIF Idx <> NVL(l_first_tab,-1) AND
4707 nvl(p_array_populated_flag(Idx),'N') = 'Y' AND
4708 l_first_mls_tab IS NOT NULL
4710
4711 l_hdr_clause := ' AND $tab$.event_id = $first_tab$.event_id' || g_chr_newline
4712 ;
4713 l_hdr_clause := REPLACE(l_hdr_clause,'$first_tab$',p_array_table_hash(l_first_mls_tab));
4714
4715 ELSIF Idx <> NVL(l_first_tab,-1) AND
4716 nvl(p_array_populated_flag(Idx),'N') = 'N' AND
4717 l_first_mls_tab IS NOT NULL THEN
4718
4719 l_hdr_clause := ' AND $tab$.event_id (+) = $first_tab$.event_id'|| g_chr_newline
4720 ;
4721 l_hdr_clause := REPLACE(l_hdr_clause,'$first_tab$',p_array_table_hash(l_first_mls_tab));
4722
4723 END IF;
4724
4725 ELSE -- reference objects
4726 IF nvl(p_array_populated_flag(Idx),'N') = 'Y' THEN
4727 l_hdr_ref_clause := p_array_join_condition(Idx);
4728
4729 --
4730 -- Replace object names with aliases
4731 --
4732 FOR j IN p_array_table_name.FIRST .. p_array_table_name.LAST LOOP
4733 l_hdr_ref_clause := REPLACE(LOWER(l_hdr_ref_clause)
4734 ,LOWER(p_array_table_name(j))
4735 ,LOWER(p_array_table_hash(j)));
4736 END LOOP;
4737
4738 l_hdr_ref_clause := ' AND ' || l_hdr_ref_clause;
4739
4740 ELSE
4741 l_hdr_ref_clause := AddOuterJoinOps(
4742 p_join_condition => p_array_join_condition(Idx)
4743 ,p_ref_obj_name => p_array_table_name(Idx));
4744
4745 --
4746 -- Replace object names with aliases
4747 --
4748 FOR j IN p_array_table_name.FIRST .. p_array_table_name.LAST LOOP
4749 l_hdr_ref_clause := REPLACE(LOWER(l_hdr_ref_clause)
4750 ,LOWER(p_array_table_name(j))
4751 ,LOWER(p_array_table_hash(j)));
4752 END LOOP;
4753
4754 l_hdr_ref_clause := ' AND ' || l_hdr_ref_clause;
4755
4756 END IF;
4757 END IF;
4758 --
4759
4760 l_hdr_clause := REPLACE(l_hdr_clause,'$tab$', p_array_table_hash(Idx));
4761 l_hdr_clauses := l_hdr_clauses || l_hdr_clause || l_hdr_ref_clause;
4762 l_hdr_clause := NULL;
4763 l_hdr_ref_clause := NULL;
4764 --
4765
4766 END IF;
4767
4768 END LOOP;
4769
4770 END IF;
4771 --
4772 --
4773 IF l_h_mls_count > 0 THEN
4774 --
4775 FOR Idx IN l_array_h_mls_tab.FIRST .. l_array_h_mls_tab.LAST LOOP
4776
4777 IF l_array_h_mls_tab.EXISTS(Idx) THEN
4778
4779 IF Idx <> NVL(l_first_mls_tab,-1) AND
4780 nvl(p_array_populated_flag(Idx),'N') = 'Y' AND
4781 l_first_mls_tab IS NOT NULL THEN
4782
4783 l_hdr_clause := ' AND $tab$.event_id = $first_tab$.event_id' || g_chr_newline
4784 || ' AND $tab$.language = $first_tab$.language' || g_chr_newline
4785 ;
4786
4787 l_hdr_clause := REPLACE(l_hdr_clause,'$first_tab$',p_array_table_hash(l_first_mls_tab));
4788 l_hdr_clause := REPLACE(l_hdr_clause,'$tab$', p_array_table_hash(Idx));
4789
4790 ELSIF Idx <> NVL(l_first_mls_tab,-1) AND
4791 nvl(p_array_populated_flag(Idx),'N') = 'N' AND
4792 l_first_mls_tab IS NOT NULL THEN
4793
4794 l_hdr_clause := ' AND $tab$.event_id (+) = $first_tab$.event_id'|| g_chr_newline
4795 || ' AND $tab$.language (+) = $first_tab$.language'|| g_chr_newline
4796 ;
4797
4798 l_hdr_clause := REPLACE(l_hdr_clause,'$first_tab$',p_array_table_hash(l_first_mls_tab));
4799 l_hdr_clause := REPLACE(l_hdr_clause,'$tab$', p_array_table_hash(Idx));
4800
4801 ELSIF Idx <> NVL(l_first_mls_tab,-1) AND
4802 nvl(p_array_populated_flag(Idx),'N') = 'Y' AND
4803 l_first_tab IS NOT NULL THEN
4804
4805 l_hdr_clause := ' AND $tab$.event_id = $first_tab$.event_id'|| g_chr_newline
4806 || ' AND $tab$.language = p_language' || g_chr_newline
4807 ;
4808
4809 l_hdr_clause := REPLACE(l_hdr_clause,'$first_tab$',p_array_table_hash(l_first_tab));
4810 l_hdr_clause := REPLACE(l_hdr_clause,'$tab$', p_array_table_hash(Idx));
4811
4812 ELSIF Idx <> NVL(l_first_mls_tab,-1) AND
4813 nvl(p_array_populated_flag(Idx),'N') = 'N' AND
4814 l_first_tab IS NOT NULL THEN
4815
4819
4816 l_hdr_clause := ' AND $tab$.event_id (+) = $first_tab$.event_id'|| g_chr_newline
4817 || ' AND $tab$.language (+) = p_language'|| g_chr_newline
4818 ;
4820 l_hdr_clause := REPLACE(l_hdr_clause,'$first_tab$',p_array_table_hash(l_first_tab));
4821 l_hdr_clause := REPLACE(l_hdr_clause,'$tab$', p_array_table_hash(Idx));
4822
4823 END IF;
4824
4825 l_hdr_clauses := l_hdr_clauses ||l_hdr_clause;
4826 l_hdr_clause := NULL;
4827 --
4828 END IF;
4829
4830 END LOOP;
4831 --
4832 END IF;
4833 --
4834
4835 l_hdr_clauses := l_hdr_clauses || GenerateLookupClauses(
4836 p_array_source_index => p_array_h_source_index
4837 , p_array_table_index => p_array_h_table_index
4838 , p_array_table_name => p_array_table_name
4839 , p_array_table_hash => p_array_table_hash
4840 , p_array_source_code => p_array_source_code
4841 , p_array_lookup_type => p_array_lookup_type
4842 , p_array_view_application_id => p_array_view_application_id
4843 );
4844
4845 l_hdr_clauses := l_hdr_clauses || GenerateLookupClauses(
4846 p_array_source_index => p_array_h_mls_source_index
4847 , p_array_table_index => p_array_h_mls_table_index
4848 , p_array_table_name => p_array_table_name
4849 , p_array_table_hash => p_array_table_hash
4850 , p_array_source_code => p_array_source_code
4851 , p_array_lookup_type => p_array_lookup_type
4852 , p_array_view_application_id => p_array_view_application_id
4853 );
4854
4855 --
4856 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4857
4858 trace
4859 (p_msg => 'l_hdr_clauses: ' || SUBSTRB(l_hdr_clauses,1, 3985)
4860 ,p_level => C_LEVEL_PROCEDURE
4861 ,p_module => l_log_module);
4862
4863 END IF;
4864 --
4865 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4866
4867 trace
4868 (p_msg => 'END of GenerateHdrWhereClause'
4869 ,p_level => C_LEVEL_PROCEDURE
4870 ,p_module => l_log_module);
4871
4872 END IF;
4873 --
4874 RETURN l_hdr_clauses;
4875 EXCEPTION
4876 WHEN xla_exceptions_pkg.application_exception THEN
4877 RETURN NULL;
4878 WHEN OTHERS THEN
4879 xla_exceptions_pkg.raise_message
4880 (p_location => 'xla_cmp_extract_pkg.GenerateHdrWhereClause ');
4881 END GenerateHdrWhereClause;
4882 --
4883 --+==========================================================================+
4884 --| |
4885 --| PRIVATE function |
4886 --| |
4887 --| |
4888 --| |
4889 --+==========================================================================+
4890 --
4891 FUNCTION GenerateFromLineTabs (
4892 --
4893 p_array_table_name IN xla_cmp_source_pkg.t_array_VL30
4894 , p_array_parent_table_index IN xla_cmp_source_pkg.t_array_ByInt
4895 , p_array_table_hash IN xla_cmp_source_pkg.t_array_VL30
4896 , p_array_populated_flag IN xla_cmp_source_pkg.t_array_VL1
4897 , p_array_ref_obj_flag IN xla_cmp_source_pkg.t_array_VL1
4898 --
4899 , p_array_l_source_index IN xla_cmp_source_pkg.t_array_ByInt
4900 , p_array_l_table_index IN xla_cmp_source_pkg.t_array_ByInt
4901 --
4902 , p_array_l_mls_source_index IN xla_cmp_source_pkg.t_array_ByInt
4903 , p_array_l_mls_table_index IN xla_cmp_source_pkg.t_array_ByInt
4904 --
4905 , p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
4906 , p_array_lookup_type IN xla_cmp_source_pkg.t_array_VL30
4907 , p_array_view_application_id IN xla_cmp_source_pkg.t_array_Num
4908 )
4909 RETURN VARCHAR2
4910 IS
4911 --
4912 l_line_tabs VARCHAR2(20000);
4913 l_line_tab VARCHAR2(1000);
4914 l_l_count BINARY_INTEGER;
4915 l_l_mls_count BINARY_INTEGER;
4916 l_array_l_tab xla_cmp_source_pkg.t_array_ByInt;
4917 l_array_l_mls_tab xla_cmp_source_pkg.t_array_ByInt;
4918 l_first_tab BINARY_INTEGER;
4919 l_first_mls_tab BINARY_INTEGER;
4920 --
4921 l_log_module VARCHAR2(240);
4922 --
4923 BEGIN
4924 --
4925 IF g_log_enabled THEN
4926 l_log_module := C_DEFAULT_MODULE||'.GenerateFromLineTabs';
4927 END IF;
4928 --
4929 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4930
4931 trace
4932 (p_msg => 'BEGIN of GenerateFromLineTabs'
4933 ,p_level => C_LEVEL_PROCEDURE
4934 ,p_module => l_log_module);
4935
4936 END IF;
4937 --
4938 l_l_count := NVL(p_array_l_source_index.COUNT,0);
4942 l_first_mls_tab := NULL;
4939 l_l_mls_count := NVL(p_array_l_mls_source_index.COUNT,0);
4940 --
4941 l_first_tab := NULL;
4943 --
4944
4945 --
4946 -- get list of standard line table
4947 --
4948 IF l_l_count > 0 THEN
4949 --
4950 GetUsedExtractObject( p_array_table_index => p_array_l_table_index
4951 ,p_array_parent_table_index => p_array_parent_table_index
4952 ,p_array_diff_table_index => l_array_l_tab
4953 );
4954 --
4955 -- Get the line always populated extract object
4956 --
4957 l_first_tab := GetAnAlwaysPopulatedObject(
4958 p_array_table_index => l_array_l_tab
4959 , p_array_populated_flag => p_array_populated_flag
4960 , p_array_ref_obj_flag => p_array_ref_obj_flag
4961 );
4962
4963 END IF;
4964
4965 --
4966 -- get list of mls line table
4967 --
4968 IF l_l_mls_count > 0 THEN
4969 --
4970 GetUsedExtractObject( p_array_table_index => p_array_l_mls_table_index
4971 ,p_array_parent_table_index => p_array_parent_table_index
4972 ,p_array_diff_table_index => l_array_l_mls_tab
4976 --
4973 );
4974 --
4975 -- Get the MLS line always populated extract object
4977 l_first_mls_tab := GetAnAlwaysPopulatedObject(
4978 p_array_table_index => l_array_l_mls_tab
4979 , p_array_populated_flag => p_array_populated_flag
4980 , p_array_ref_obj_flag => p_array_ref_obj_flag
4981 );
4982 --
4983 END IF;
4984 --
4985 --
4986 IF l_array_l_tab.COUNT > 0 THEN
4987
4988 FOR Idx IN l_array_l_tab.FIRST .. l_array_l_tab.LAST LOOP
4989
4990 IF l_array_l_tab.EXISTS(Idx) THEN
4991
4992 l_line_tab := ' , $table_name$ $tab$' ;
4993 --
4994 l_line_tab := REPLACE(l_line_tab, '$table_name$', p_array_table_name(Idx));
4995 l_line_tab := REPLACE(l_line_tab, '$tab$', p_array_table_hash(Idx));
4996 l_line_tabs := l_line_tabs || g_chr_newline || l_line_tab;
4997 l_line_tab := NULL;
4998 --
4999 --
5000 END IF;
5001
5002 END LOOP;
5003
5004 END IF;
5005 --
5006 -- MLS extract where clauses
5007 --
5008 IF l_array_l_mls_tab.COUNT > 0 THEN
5009
5010 FOR Idx IN l_array_l_mls_tab.FIRST .. l_array_l_mls_tab.LAST LOOP
5011
5012 IF l_array_l_mls_tab.EXISTS(Idx) THEN
5013
5014 l_line_tab := ' , $table_name$ $tab$' ;
5015 --
5016 l_line_tab := REPLACE(l_line_tab, '$table_name$', p_array_table_name(Idx));
5017 l_line_tab := REPLACE(l_line_tab, '$tab$', p_array_table_hash(Idx));
5018 l_line_tabs := l_line_tabs || g_chr_newline || l_line_tab;
5019 l_line_tab := NULL;
5020 --
5021
5022 END IF;
5023
5024 END LOOP;
5025
5026 END IF;
5027 --
5028 -- generate the where clauses for extract of lookup sources
5029 --
5030 l_line_tabs := l_line_tabs || GenerateLookupTables(
5031 p_array_source_index => p_array_l_source_index
5032 , p_array_table_index => p_array_l_table_index
5033 , p_array_lookup_type => p_array_lookup_type
5034 , p_array_view_application_id => p_array_view_application_id
5035 );
5036 --
5037 l_line_tabs := l_line_tabs || GenerateLookupTables(
5038 p_array_source_index => p_array_l_mls_source_index
5039 , p_array_table_index => p_array_l_mls_table_index
5040 , p_array_lookup_type => p_array_lookup_type
5041 , p_array_view_application_id => p_array_view_application_id
5042 );
5043 --
5044 --
5045 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5046
5047 trace
5048 (p_msg => 'END of GenerateFromLineTabs'
5049 ,p_level => C_LEVEL_PROCEDURE
5050 ,p_module => l_log_module);
5051
5052 END IF;
5053 --
5054 RETURN l_line_tabs;
5055 EXCEPTION
5056 WHEN xla_exceptions_pkg.application_exception THEN
5060 (p_location => 'xla_cmp_extract_pkg.GenerateFromLineTabs ');
5057 RETURN NULL;
5058 WHEN OTHERS THEN
5059 xla_exceptions_pkg.raise_message
5061 END GenerateFromLineTabs;
5062 --
5063 --+==========================================================================+
5064 --| |
5065 --| PRIVATE function |
5066 --| |
5067 --| |
5068 --| |
5069 --+==========================================================================+
5070 --
5071 FUNCTION GenerateLineWhereClause (
5072 --
5073 p_array_table_name IN xla_cmp_source_pkg.t_array_VL30
5074 , p_array_parent_table_index IN xla_cmp_source_pkg.t_array_ByInt
5075 , p_array_table_hash IN xla_cmp_source_pkg.t_array_VL30
5076 , p_array_populated_flag IN xla_cmp_source_pkg.t_array_VL1
5077 --
5078 , p_array_ref_obj_flag IN xla_cmp_source_pkg.t_array_VL1
5079 , p_array_join_condition IN xla_cmp_source_pkg.t_array_VL2000
5080
5081 --
5082 , p_array_l_source_index IN xla_cmp_source_pkg.t_array_ByInt
5083 , p_array_l_table_index IN xla_cmp_source_pkg.t_array_ByInt
5084 --
5085 , p_array_l_mls_source_index IN xla_cmp_source_pkg.t_array_ByInt
5086 , p_array_l_mls_table_index IN xla_cmp_source_pkg.t_array_ByInt
5087 --
5088 , p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
5089 , p_array_lookup_type IN xla_cmp_source_pkg.t_array_VL30
5090 , p_array_view_application_id IN xla_cmp_source_pkg.t_array_Num
5091 )
5092 RETURN VARCHAR2
5093 IS
5094 --
5095 l_line_clauses VARCHAR2(20000);
5096 l_line_clause VARCHAR2(1000);
5097 --
5098 l_line_ref_clause VARCHAR2(1000);
5099 --
5100 l_l_count BINARY_INTEGER;
5101 l_l_mls_count BINARY_INTEGER;
5102 --
5103 l_array_l_tab xla_cmp_source_pkg.t_array_ByInt;
5104 l_array_l_mls_tab xla_cmp_source_pkg.t_array_ByInt;
5105 --
5106 l_first_tab BINARY_INTEGER;
5107 l_first_mls_tab BINARY_INTEGER;
5108 --
5109 l_log_module VARCHAR2(240);
5110
5111 l_alc_enabled_flag VARCHAR2(1);
5112 --
5113 BEGIN
5114 --
5115
5116
5117 IF g_log_enabled THEN
5118 l_log_module := C_DEFAULT_MODULE||'.GenerateLineWhereClause';
5119 END IF;
5120 --
5121 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5122
5123 trace
5127
5124 (p_msg => 'BEGIN of GenerateLineWhereClause'
5125 ,p_level => C_LEVEL_PROCEDURE
5126 ,p_module => l_log_module);
5128 END IF;
5129 --
5130 select alc_enabled_flag
5131 into l_alc_enabled_flag
5132 from xla_subledgers
5133 where application_id = g_application_id;
5134
5135 l_l_count := NVL(p_array_l_source_index.COUNT,0);
5136 l_l_mls_count := NVL(p_array_l_mls_source_index.COUNT,0);
5137 --
5138 l_first_tab := NULL;
5139 l_first_mls_tab := NULL;
5140 --
5141 --
5142 -- get list of standard line table
5143 --
5144 IF l_l_count > 0 THEN
5145 --
5146 GetUsedExtractObject( p_array_table_index => p_array_l_table_index
5147 ,p_array_parent_table_index => p_array_parent_table_index
5148 ,p_array_diff_table_index => l_array_l_tab
5149 );
5150 --
5151 -- Get the line always populated extract object
5152 --
5153 l_first_tab := GetAnAlwaysPopulatedObject(
5154 p_array_table_index => l_array_l_tab
5155 , p_array_populated_flag => p_array_populated_flag
5156 , p_array_ref_obj_flag => p_array_ref_obj_flag
5157 );
5158 --
5159 END IF;
5160
5161 --
5162 -- get list of mls line table
5163 --
5164 IF l_l_mls_count > 0 THEN
5165 --
5166 GetUsedExtractObject( p_array_table_index => p_array_l_mls_table_index
5170
5167 ,p_array_parent_table_index => p_array_parent_table_index
5168 ,p_array_diff_table_index => l_array_l_mls_tab
5169 );
5171 --
5172 -- Get the MLS line always populated extract object
5173 --
5174 l_first_mls_tab := GetAnAlwaysPopulatedObject(
5175 p_array_table_index => l_array_l_mls_tab
5176 , p_array_populated_flag => p_array_populated_flag
5177 , p_array_ref_obj_flag => p_array_ref_obj_flag
5178 );
5179 --
5180 END IF;
5181 --
5182 -- generate first clause
5183 --
5184 IF l_first_tab IS NOT NULL AND
5185 l_first_mls_tab IS NOT NULL THEN
5186
5187 l_line_clause := ' AND $first_tab$.event_id = xet.event_id' ||g_chr_newline
5188 || ' AND $first_tab$.event_id = $first_mls_tab$.event_id' ||g_chr_newline
5189 || ' AND $first_tab$.line_number = $first_mls_tab$.line_number'||g_chr_newline
5190 || ' AND $first_mls_tab$.language = p_language' ||g_chr_newline
5191 || ' AND $first_tab$.ledger_id(+) = p_sla_ledger_id' ||g_chr_newline
5192 ;
5193 --
5194 l_line_clause := REPLACE(l_line_clause,'$first_tab$',p_array_table_hash(l_first_tab));
5195 l_line_clause := REPLACE(l_line_clause,'$first_mls_tab$', p_array_table_hash(l_first_mls_tab));
5196
5197 l_line_clauses := l_line_clauses ||l_line_clause;
5198 l_line_clause := NULL;
5199
5200 --
5201 ELSIF l_first_tab IS NOT NULL AND
5202 l_first_mls_tab IS NULL THEN
5203
5204 l_line_clause := ' AND $first_tab$.event_id = xet.event_id' ||g_chr_newline;
5205 IF(l_alc_enabled_flag = 'N') THEN
5206 l_line_clause := l_line_clause ||
5207 ' AND $first_tab$.ledger_id (+) = p_sla_ledger_id' ||g_chr_newline;
5208 END IF;
5209
5210 --
5211 l_line_clause := REPLACE(l_line_clause,'$first_tab$',p_array_table_hash(l_first_tab));
5212 l_line_clauses := l_line_clauses ||l_line_clause;
5213 l_line_clause := NULL;
5214
5215 ELSIF l_first_tab IS NULL AND
5216 l_first_mls_tab IS NOT NULL THEN
5217
5218 l_line_clause := ' AND $first_mls_tab$.event_id = xet.event_id' ||g_chr_newline
5219 || ' AND $first_mls_tab$.language = p_language' ||g_chr_newline
5220 ;
5221
5222 --
5223 l_line_clause := REPLACE(l_line_clause,'$first_mls_tab$', p_array_table_hash(l_first_mls_tab));
5224 l_line_clauses := l_line_clauses || l_line_clause;
5225 l_line_clause := NULL;
5226
5227 END IF;
5228 --
5229 --
5230 IF l_array_l_tab.COUNT > 0 THEN
5231
5232 FOR Idx IN l_array_l_tab.FIRST .. l_array_l_tab.LAST LOOP
5233
5234 IF l_array_l_tab.EXISTS(Idx) THEN
5235 --
5236 IF nvl(p_array_ref_obj_flag(Idx),'N') = 'N' THEN
5237
5238 IF Idx <> NVL(l_first_tab,-1) AND
5239 nvl(p_array_populated_flag(Idx),'N') = 'Y' AND
5240 l_first_tab IS NOT NULL
5241 THEN
5242
5243 l_line_clause := ' AND $tab$.event_id = $first_tab$.event_id' ||g_chr_newline
5244 || ' AND $tab$.line_number = $first_tab$.line_number'||g_chr_newline
5245 ;
5246
5247 l_line_clause := REPLACE(l_line_clause,'$first_tab$',p_array_table_hash(l_first_tab));
5248
5249 ELSIF Idx <> NVL(l_first_tab,-1) AND
5250 nvl(p_array_populated_flag(Idx),'N') = 'N' AND
5251 l_first_tab IS NOT NULL THEN
5252
5253 l_line_clause := ' AND $tab$.event_id (+) = $first_tab$.event_id' ||g_chr_newline
5254 || ' AND $tab$.line_number (+) = $first_tab$.line_number'||g_chr_newline
5255 ;
5256
5257 l_line_clause := REPLACE(l_line_clause,'$first_tab$',p_array_table_hash(l_first_tab));
5258
5259 ELSIF Idx <> NVL(l_first_tab,-1) AND
5260 nvl(p_array_populated_flag(Idx),'N') = 'Y' AND
5261 l_first_mls_tab IS NOT NULL
5262 THEN
5263
5264 l_line_clause := ' AND $tab$.event_id = $first_tab$.event_id' ||g_chr_newline
5265 || ' AND $tab$.line_number = $first_tab$.line_number'||g_chr_newline
5266 ;
5267
5268 l_line_clause := REPLACE(l_line_clause,'$first_tab$',p_array_table_hash(l_first_mls_tab));
5269
5270 ELSIF Idx <> NVL(l_first_tab,-1) AND
5271 nvl(p_array_populated_flag(Idx),'N') = 'N' AND
5272 l_first_mls_tab IS NOT NULL THEN
5273
5274 l_line_clause := ' AND $tab$.event_id (+) = $first_tab$.event_id' ||g_chr_newline
5275 || ' AND $tab$.line_number (+) = $first_tab$.line_number'||g_chr_newline
5276 ;
5277
5278 l_line_clause := REPLACE(l_line_clause,'$first_tab$',p_array_table_hash(l_first_mls_tab));
5279
5280 END IF;
5281
5282 ELSE -- reference objects
5283 IF nvl(p_array_populated_flag(Idx),'N') = 'Y' THEN
5284 l_line_ref_clause := p_array_join_condition(Idx);
5285
5286 --
5287 -- Replace object names with aliases
5288 --
5289 FOR j IN p_array_table_name.FIRST .. p_array_table_name.LAST LOOP
5290 l_line_ref_clause := REPLACE (LOWER(l_line_ref_clause)
5291 ,LOWER(p_array_table_name(j))
5292 ,LOWER(p_array_table_hash(j)));
5293 END LOOP;
5294
5295 l_line_ref_clause := ' AND ' || l_line_ref_clause;
5299 ,p_ref_obj_name => p_array_table_name(Idx));
5296 ELSE
5297 l_line_ref_clause := AddOuterJoinOps(
5298 p_join_condition => p_array_join_condition(Idx)
5300
5301 --
5302 -- Replace object names with aliases
5303 --
5304 FOR j IN p_array_table_name.FIRST .. p_array_table_name.LAST LOOP
5305 l_line_ref_clause := REPLACE (LOWER(l_line_ref_clause)
5306 ,LOWER(p_array_table_name(j))
5307 ,LOWER(p_array_table_hash(j)));
5308 END LOOP;
5309
5310 l_line_ref_clause := ' AND ' || l_line_ref_clause;
5311 END IF;
5312 END IF;
5313 --
5314 l_line_clause := REPLACE(l_line_clause,'$tab$', p_array_table_hash(Idx));
5315 l_line_clauses := l_line_clauses || l_line_clause || l_line_ref_clause;
5316 l_line_clause := NULL;
5317 l_line_ref_clause := NULL;
5318
5319
5320 END IF;
5321
5322 END LOOP;
5323
5324 END IF;
5325 --
5326 -- MLS extract where clauses
5327 --
5328 IF l_array_l_mls_tab.COUNT > 0 THEN
5329
5330 FOR Idx IN l_array_l_mls_tab.FIRST .. l_array_l_mls_tab.LAST LOOP
5331
5332 IF l_array_l_mls_tab.EXISTS(Idx) THEN
5333 --
5334 IF Idx <> NVL(l_first_mls_tab,-1) AND
5335 nvl(p_array_populated_flag(Idx),'N') = 'Y' AND
5336 l_first_mls_tab IS NOT NULL
5337 THEN
5338
5339 l_line_clause := ' AND $tab$.event_id = $first_tab$.event_id' ||g_chr_newline
5340 || ' AND $tab$.line_number = $first_tab$.line_number' ||g_chr_newline
5341 || ' AND $tab$.language = $first_tab$.language' ||g_chr_newline
5342 ;
5343
5344 l_line_clause := REPLACE(l_line_clause,'$first_tab$',p_array_table_hash(l_first_mls_tab));
5345
5346 ELSIF Idx <> NVL(l_first_mls_tab,-1) AND
5347 nvl(p_array_populated_flag(Idx),'N') = 'N' AND
5348 l_first_mls_tab IS NOT NULL THEN
5349
5350 l_line_clause := ' AND $tab$.event_id (+) = $first_tab$.event_id' ||g_chr_newline
5351 || ' AND $tab$.line_number (+) = $first_tab$.line_number'||g_chr_newline
5352 || ' AND $tab$.language (+) = $first_tab$.language' ||g_chr_newline
5353 ;
5354 l_line_clause := REPLACE(l_line_clause,'$first_tab$',p_array_table_hash(l_first_mls_tab));
5355
5356 ELSIF Idx <> NVL(l_first_mls_tab,-1) AND
5357 nvl(p_array_populated_flag(Idx),'N') = 'Y' AND
5358 l_first_tab IS NOT NULL
5359 THEN
5360
5361 l_line_clause := ' AND $tab$.event_id = $first_tab$.event_id' ||g_chr_newline
5362 || ' AND $tab$.line_number = $first_tab$.line_number'||g_chr_newline
5363 || ' AND $tab$.language = p_language' ||g_chr_newline
5364 ;
5365 l_line_clause := REPLACE(l_line_clause,'$first_tab$',p_array_table_hash(l_first_tab));
5366
5367 ELSIF Idx <> NVL(l_first_mls_tab,-1) AND
5368 nvl(p_array_populated_flag(Idx),'N') = 'N' AND
5369 l_first_tab IS NOT NULL THEN
5370
5371 l_line_clause := ' AND $tab$.event_id (+) = $first_tab$.event_id' ||g_chr_newline
5372 || ' AND $tab$.line_number (+) = $first_tab$.line_number'||g_chr_newline
5373 || ' AND $tab$.language (+) = p_language' ||g_chr_newline
5374 ;
5375
5376 l_line_clause := REPLACE(l_line_clause,'$first_tab$',p_array_table_hash(l_first_tab));
5377
5378
5379 END IF;
5380 --
5381
5382 l_line_clause := REPLACE(l_line_clause,'$tab$', p_array_table_hash(Idx));
5383 l_line_clauses := l_line_clauses || l_line_clause;
5384 l_line_clause := NULL;
5385
5386
5387 END IF;
5388
5389 END LOOP;
5390
5391 END IF;
5392 --
5393 -- generate the where clauses for extract of lookup sources
5394 --
5395 --
5396 l_line_clauses := l_line_clauses || GenerateLookupClauses(
5397 p_array_source_index => p_array_l_source_index
5398 , p_array_table_index => p_array_l_table_index
5399 , p_array_table_name => p_array_table_name
5400 , p_array_table_hash => p_array_table_hash
5401 , p_array_source_code => p_array_source_code
5402 , p_array_lookup_type => p_array_lookup_type
5403 , p_array_view_application_id => p_array_view_application_id
5404 );
5405 --
5406 l_line_clauses := l_line_clauses || GenerateLookupClauses(
5407 p_array_source_index => p_array_l_mls_source_index
5408 , p_array_table_index => p_array_l_mls_table_index
5409 , p_array_table_name => p_array_table_name
5410 , p_array_table_hash => p_array_table_hash
5411 , p_array_source_code => p_array_source_code
5412 , p_array_lookup_type => p_array_lookup_type
5413 , p_array_view_application_id => p_array_view_application_id
5414 );
5415 --
5416 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5417
5418 trace
5419 (p_msg => 'l_line_clauses: ' || SUBSTRB(l_line_clauses,1,3984)
5420 ,p_level => C_LEVEL_PROCEDURE
5421 ,p_module => l_log_module);
5422
5423 END IF;
5424 --
5425 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5426
5427 trace
5428 (p_msg => 'END of GenerateLineWhereClause'
5429 ,p_level => C_LEVEL_PROCEDURE
5430 ,p_module => l_log_module);
5431
5432 END IF;
5433 --
5434 RETURN l_line_clauses;
5438 WHEN OTHERS THEN
5435 EXCEPTION
5436 WHEN xla_exceptions_pkg.application_exception THEN
5437 RETURN NULL;
5439 xla_exceptions_pkg.raise_message
5440 (p_location => 'xla_cmp_extract_pkg.GenerateLineWhereClause ');
5441 END GenerateLineWhereClause;
5442 --
5443 --
5444 --+==========================================================================+
5445 --| |
5446 --| PRIVATE FUNCTION |
5447 --| |
5448 --| Generate the CASE expression into the SQL statements used to |
5449 --| Insert header and line sources retrieved from the extract |
5450 --| |
5451 --+==========================================================================+
5452 --
5453 FUNCTION GenerateInsertStm(
5454 p_array_table_name IN xla_cmp_source_pkg.t_array_VL30
5455 , p_array_table_type IN xla_cmp_source_pkg.t_array_VL30
5456 , p_array_table_index IN xla_cmp_source_pkg.t_array_ByInt
5457 , p_array_table_hash IN xla_cmp_source_pkg.t_array_VL30
5458 , p_array_ref_obj_flag IN xla_cmp_source_pkg.t_array_VL1
5459 , p_array_populated_flag IN xla_cmp_source_pkg.t_array_VL1
5460 --
5461 , p_array_source_index IN xla_cmp_source_pkg.t_array_ByInt
5462 , p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
5463 , p_array_application_id IN xla_cmp_source_pkg.t_array_Num
5464 , p_array_source_type_code IN xla_cmp_source_pkg.t_array_VL1
5465 , p_array_flex_value_set_id IN xla_cmp_source_pkg.t_array_Num
5466 , p_array_lookup_type IN xla_cmp_source_pkg.t_array_VL30
5467 , p_array_view_application_id IN xla_cmp_source_pkg.t_array_Num
5468 --
5469 , p_level IN VARCHAR2
5470 , p_procedure IN VARCHAR2
5471 )
5472 RETURN CLOB
5473 IS
5474
5475 --
5476 C_LINE_NUMBER CONSTANT VARCHAR2(100):= '$tab$.line_number';
5477 C_OBJECT_NAME CONSTANT VARCHAR2(200):= 'WHEN $index$ THEN ''$object_name$'' ';
5478 C_OBJECT_TYPE_CODE CONSTANT VARCHAR2(200):= 'WHEN $index$ THEN ''$object_type_code$'' ';
5479 C_SOURCE_APPL_ID CONSTANT VARCHAR2(200):= 'WHEN $index$ THEN ''$source_application_id$'' ';
5480 C_SOURCE_TYPE_CODE CONSTANT VARCHAR2(200):= '''$source_type_code$'' ';
5481 C_SOURCE_CODE CONSTANT VARCHAR2(200):= 'WHEN $index$ THEN ''$source_code$'' ';
5482 C_SOURCE_VALUE CONSTANT VARCHAR2(200):= 'WHEN $index$ THEN TO_CHAR($tab$.$source_code$)';
5483 --
5484 C_MEANING_NOT_NULL CONSTANT VARCHAR2(1000):= 'CASE r
5485 $source_meaning$
5486 ELSE null
5487 END ';
5488 C_MEANING_NULL CONSTANT VARCHAR2(100):= 'null';
5489 C_SOURCE_MEANING_LOOKUP CONSTANT VARCHAR2(200):= 'WHEN $index$ THEN fvl$Idx$.meaning';
5490 C_SOURCE_MEANING_VALSET CONSTANT VARCHAR2(1000):= 'WHEN $index$ THEN $package_name$.GetMeaning(
5491 $flex_value_set_id$
5492 ,TO_CHAR($tab$.$source_code$)
5493 ,''$source_code$''
5494 ,''$source_type_code$''
5495 ,$source_application_id$)';
5496
5497
5498
5499 --
5500 l_sql_statement CLOB;
5501
5502 l_object_name VARCHAR2(32000);
5503 l_object_type_code VARCHAR2(32000);
5504 l_source_appl_id VARCHAR2(32000);
5505 l_source_type_code VARCHAR2(32000);
5506 l_source_code VARCHAR2(32000);
5507 l_source_value VARCHAR2(32000);
5508 l_source_meaning VARCHAR2(32000);
5509 l_line_number VARCHAR2(32000);
5510 --
5511 --
5512 l_source_num NUMBER;
5513 --
5514 l_log_module VARCHAR2(240);
5515 l_space VARCHAR2(100);
5516 --
5517 l_called BOOLEAN;
5518 BEGIN
5519 --
5520 IF g_log_enabled THEN
5521 l_log_module := C_DEFAULT_MODULE||'.GenerateInsertStm';
5522 END IF;
5523 --
5524 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5525
5526 trace
5527 (p_msg => 'BEGIN of GenerateInsertStm'
5528 ,p_level => C_LEVEL_PROCEDURE
5529 ,p_module => l_log_module);
5530
5531 END IF;
5532
5533 --
5534 l_space := ' ';
5535 l_object_name := NULL;
5536 l_object_type_code := NULL;
5537 l_source_appl_id := NULL;
5538 l_source_type_code := NULL;
5539 l_source_code := NULL;
5540 l_source_value := NULL;
5541 l_source_meaning := NULL;
5542 --
5543 l_source_num:= 0;
5544 l_called := FALSE;
5545 --
5546 IF p_array_source_index.COUNT > 0 THEN
5547 --
5548 FOR idx IN p_array_table_index.FIRST..p_array_table_index.LAST LOOP
5549 IF p_array_table_index.EXISTS(idx) AND
5550 p_array_populated_flag(p_array_table_index(idx)) = 'Y' AND
5551 p_array_ref_obj_flag (p_array_table_index(idx)) = 'N' THEN
5552
5553 l_line_number := REPLACE(C_LINE_NUMBER,'$tab$',
5554 p_array_table_hash(p_array_table_index(idx))); -- Bug 5478323
5555 EXIT;
5556 END IF;
5557 END LOOP;
5558
5559 FOR Idx IN p_array_source_index.FIRST .. p_array_source_index.LAST LOOP
5560 --
5561 IF p_array_source_index.EXISTS(Idx) THEN
5562
5563 l_source_num:= l_source_num + 1 ;
5564
5565 IF l_source_type_code IS NULL THEN
5566 l_source_type_code := REPLACE(C_SOURCE_TYPE_CODE,'$source_type_code$'
5567 ,p_array_source_type_code(p_array_source_index(Idx)));
5568 END IF;
5569
5573 p_array_table_name(p_array_table_index(Idx)));
5570 l_object_name := l_object_name ||C_OBJECT_NAME||g_chr_newline||l_space;
5571
5572 l_object_name := REPLACE(l_object_name,'$object_name$',
5574
5575 l_object_name := REPLACE(l_object_name,'$index$',l_source_num);
5576 --
5577 l_object_type_code := l_object_type_code ||C_OBJECT_TYPE_CODE||g_chr_newline||l_space;
5578
5579 l_object_type_code := REPLACE(l_object_type_code,'$object_type_code$',
5580 p_array_table_type(p_array_table_index(Idx)));
5581
5582 l_object_type_code := REPLACE(l_object_type_code,'$index$',l_source_num);
5583 --
5584
5585 l_source_appl_id := l_source_appl_id ||C_SOURCE_APPL_ID||g_chr_newline||l_space;
5586
5587 l_source_appl_id := REPLACE(l_source_appl_id,'$source_application_id$'
5588 ,p_array_application_id(p_array_source_index(Idx)));
5589
5590 l_source_appl_id := REPLACE(l_source_appl_id,'$index$',l_source_num);
5591
5592 --
5593
5594 l_source_code := l_source_code ||C_SOURCE_CODE||g_chr_newline||l_space;
5595
5596 l_source_code := REPLACE(l_source_code,'$source_code$'
5597 ,p_array_source_code(p_array_source_index(Idx)));
5598
5599 l_source_code := REPLACE(l_source_code,'$index$',l_source_num);
5600
5601 --
5602 l_source_value := l_source_value ||C_SOURCE_VALUE||g_chr_newline||l_space;
5603
5604 l_source_value := REPLACE(l_source_value,'$source_code$'
5605 ,p_array_source_code(p_array_source_index(Idx)));
5606
5607 l_source_value := REPLACE(l_source_value,'$tab$'
5608 ,p_array_table_hash(p_array_table_index(Idx)));
5609
5610 l_source_value := REPLACE(l_source_value,'$index$',l_source_num);
5611 --
5612
5613 IF p_array_lookup_type.EXISTS(Idx) AND
5614 p_array_lookup_type(Idx) IS NOT NULL AND
5615 p_array_view_application_id.EXISTS(Idx) AND
5616 p_array_view_application_id(Idx) IS NOT NULL THEN
5617
5618 l_source_meaning := l_source_meaning || C_SOURCE_MEANING_LOOKUP ||g_chr_newline||l_space;
5619
5620 l_source_meaning := REPLACE(l_source_meaning,'$index$',l_source_num);
5621
5622 l_source_meaning := REPLACE(l_source_meaning,'$Idx$',Idx);
5623
5624 ELSIF p_array_flex_value_set_id.EXISTS(Idx) AND
5625 p_array_flex_value_set_id(Idx) IS NOT NULL THEN
5626
5627 l_source_meaning := l_source_meaning || C_SOURCE_MEANING_VALSET ||g_chr_newline||l_space;
5628
5629 l_source_meaning := REPLACE(l_source_meaning,'$index$',l_source_num);
5630
5631 l_source_meaning := REPLACE(l_source_meaning,'$flex_value_set_id$',p_array_flex_value_set_id(Idx));
5632
5633 l_source_meaning := REPLACE(l_source_meaning,'$tab$', p_array_table_hash(p_array_table_index(Idx)));
5634
5635 l_source_meaning := REPLACE(l_source_meaning,'$source_code$',
5636 p_array_source_code(p_array_source_index(Idx)));
5637
5638 l_source_meaning := REPLACE(l_source_meaning,'$source_type_code$',
5639 p_array_source_type_code(p_array_source_index(Idx)));
5640
5641 l_source_meaning := REPLACE(l_source_meaning,'$source_application_id$',
5642 p_array_application_id(p_array_source_index(Idx)));
5643
5644 ELSE
5645 null;
5646 END IF;
5647
5648 --
5649 END IF;
5650 --
5651 END LOOP;
5652 --
5653 END IF;
5654 --
5655
5656 --
5657
5658
5659 IF l_source_num > 0 THEN
5660
5661 IF p_level = C_HEADER AND p_procedure = 'EVENT_TYPE' THEN
5662
5663 l_sql_statement := C_INSERT_HDR_SOURCES_EVT;
5664
5665 ELSIF p_level = C_HEADER AND p_procedure = 'EVENT_CLASS' THEN
5666
5667 l_sql_statement := C_INSERT_HDR_SOURCES_CLASS;
5668
5669 ELSIF p_level = C_LINE AND p_procedure = 'EVENT_TYPE' THEN
5670
5671 l_sql_statement := C_INSERT_LINE_SOURCES_EVT;
5672
5673 ELSE
5674
5675 l_sql_statement := C_INSERT_LINE_SOURCES_CLASS;
5676
5677 END IF;
5678
5679 -- Bugfix 4417664 (replace REPLACE with replace_token)
5680
5681 l_sql_statement := xla_cmp_string_pkg.replace_token(l_sql_statement,'$source_number$' ,nvl(TO_CHAR(l_source_num),' '));
5682 l_sql_statement := xla_cmp_string_pkg.replace_token(l_sql_statement,'$line_number$' ,nvl(TO_CHAR(l_line_number),' '));
5683 l_sql_statement := xla_cmp_string_pkg.replace_token(l_sql_statement,'$object_name$' ,nvl(l_object_name,' '));
5684 l_sql_statement := xla_cmp_string_pkg.replace_token(l_sql_statement,'$object_type_code$' ,nvl(l_object_type_code ,' '));
5685 l_sql_statement := xla_cmp_string_pkg.replace_token(l_sql_statement,'$source_application_id$',nvl(l_source_appl_id,' '));
5686 l_sql_statement := xla_cmp_string_pkg.replace_token(l_sql_statement,'$source_type_code$' ,nvl(l_source_type_code,' '));
5687 l_sql_statement := xla_cmp_string_pkg.replace_token(l_sql_statement,'$source_code$' ,nvl(l_source_code,' '));
5688 l_sql_statement := xla_cmp_string_pkg.replace_token(l_sql_statement,'$source_value$' ,nvl(l_source_value,' '));
5689 IF l_source_meaning IS NOT NULL THEN
5690 --
5691 l_sql_statement := REPLACE(l_sql_statement,'$source_meaning$' ,C_MEANING_NOT_NULL);
5692 l_sql_statement := REPLACE(l_sql_statement,'$source_meaning$' ,nvl(l_source_meaning,' '));
5693 ELSE
5697 ELSE
5694 l_sql_statement := xla_cmp_string_pkg.replace_token(l_sql_statement,'$source_meaning$' ,C_MEANING_NULL);
5695 END IF;
5696
5698 l_sql_statement :=' ';
5699
5700 END IF;
5701
5702 --
5703 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5704
5705 trace
5706 (p_msg => 'END of GenerateInsertStm'
5707 ,p_level => C_LEVEL_PROCEDURE
5708 ,p_module => l_log_module);
5709
5710 END IF;
5711 --
5712 RETURN l_sql_statement;
5713 EXCEPTION
5714 WHEN VALUE_ERROR THEN
5715
5716 IF (C_LEVEL_UNEXPECTED >= g_log_level) THEN
5717 trace
5718 (p_msg => 'ERROR: XLA_CMP_COMPILER_ERROR '||sqlerrm
5719 ,p_level => C_LEVEL_UNEXPECTED
5720 ,p_module => l_log_module);
5721 END IF;
5722
5723 RAISE;
5724 WHEN xla_exceptions_pkg.application_exception THEN
5725 RETURN NULL;
5726 WHEN OTHERS THEN
5727 xla_exceptions_pkg.raise_message
5728 (p_location => 'xla_cmp_extract_pkg.GenerateInsertStm ');
5729 END GenerateInsertStm;
5730 --
5731 --+==========================================================================+
5732 --| |
5733 --| PUBLIC function |
5734 --| |
5735 --| Generate the INSERT SQL statement used by the Extract Source Values |
5736 --| Dump to insert the header source values into xla_diag_sources |
5737 --| |
5738 --| |
5739 --+==========================================================================+
5740 --
5741 FUNCTION GenerateInsertHdrSources (
5742 --
5743 p_array_table_name IN xla_cmp_source_pkg.t_array_VL30
5744 , p_array_parent_table_index IN xla_cmp_source_pkg.t_array_ByInt
5745 , p_array_table_hash IN xla_cmp_source_pkg.t_array_VL30
5746 , p_array_table_type IN xla_cmp_source_pkg.t_array_VL30
5747 , p_array_populated_flag IN xla_cmp_source_pkg.t_array_VL1
5748 --
5749 , p_array_ref_obj_flag IN xla_cmp_source_pkg.t_array_vl1
5750 , p_array_join_condition IN xla_cmp_source_pkg.t_array_vl2000
5751 --
5752 , p_array_h_source_index IN xla_cmp_source_pkg.t_array_ByInt
5753 , p_array_h_table_index IN xla_cmp_source_pkg.t_array_ByInt
5754 --
5755 , p_array_h_mls_source_index IN xla_cmp_source_pkg.t_array_ByInt
5756 , p_array_h_mls_table_index IN xla_cmp_source_pkg.t_array_ByInt
5757 --
5758 , p_array_application_id IN xla_cmp_source_pkg.t_array_Num
5759 , p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
5760 , p_array_source_type_code IN xla_cmp_source_pkg.t_array_VL1
5761 , p_array_flex_value_set_id IN xla_cmp_source_pkg.t_array_Num
5762 , p_array_lookup_type IN xla_cmp_source_pkg.t_array_VL30
5763 , p_array_view_application_id IN xla_cmp_source_pkg.t_array_Num
5764 --
5765 , p_procedure IN VARCHAR2
5766 )
5767 RETURN CLOB
5768 IS
5769 --
5770 l_sql_statement CLOB;
5771 l_log_module VARCHAR2(240);
5772 --
5773 l_array_source_index xla_cmp_source_pkg.t_array_ByInt;
5774 l_array_table_index xla_cmp_source_pkg.t_array_ByInt;
5775 --
5776 BEGIN
5777 --
5778 IF g_log_enabled THEN
5779 l_log_module := C_DEFAULT_MODULE||'.GenerateInsertHdrSources';
5780 END IF;
5781 --
5782 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5783
5784 trace
5785 (p_msg => 'BEGIN of GenerateInsertHdrSources'
5786 ,p_level => C_LEVEL_PROCEDURE
5787 ,p_module => l_log_module);
5788
5789 END IF;
5790 --
5791 IF NVL(p_array_h_source_index.COUNT,0) > 0 THEN
5792
5793 FOR Idx IN p_array_h_source_index.FIRST .. p_array_h_source_index.LAST LOOP
5794
5795 IF p_array_h_source_index.EXISTS(Idx) THEN
5796
5797 l_array_source_index(Idx) := p_array_h_source_index(Idx);
5798 l_array_table_index(Idx) := p_array_h_table_index(Idx);
5799
5800 END IF;
5801
5802 END LOOP;
5803
5804 END IF;
5805
5806 IF NVL(p_array_h_mls_source_index.COUNT,0)> 0 THEN
5807
5808 FOR Idx IN p_array_h_mls_source_index.FIRST .. p_array_h_mls_source_index.LAST LOOP
5809
5810 IF p_array_h_mls_source_index.EXISTS(Idx) THEN
5811
5812 l_array_source_index(Idx) := p_array_h_mls_source_index(Idx);
5813 l_array_table_index(Idx) := p_array_h_mls_table_index(Idx);
5814
5815 END IF;
5816
5817 END LOOP;
5818
5819 END IF;
5820
5821 IF NVL(p_array_h_source_index.COUNT,0)+ NVL(p_array_h_mls_source_index.COUNT,0) > 0 THEN
5822
5823 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
5824
5825 trace
5826 (p_msg => 'CALL GenerateInsertStm()'
5827 ,p_level => C_LEVEL_STATEMENT
5828 ,p_module => l_log_module);
5829
5830 END IF;
5831
5832 l_sql_statement := GenerateInsertStm(
5833 p_array_table_name => p_array_table_name
5834 , p_array_table_type => p_array_table_type
5835 , p_array_table_index => l_array_table_index
5836 , p_array_table_hash => p_array_table_hash
5837 , p_array_ref_obj_flag => p_array_ref_obj_flag
5838 , p_array_populated_flag => p_array_populated_flag
5839
5840 --
5844 , p_array_source_type_code => p_array_source_type_code
5841 , p_array_source_index => l_array_source_index
5842 , p_array_source_code => p_array_source_code
5843 , p_array_application_id => p_array_application_id
5845 , p_array_flex_value_set_id => p_array_flex_value_set_id
5846 , p_array_lookup_type => p_array_lookup_type
5847 , p_array_view_application_id => p_array_view_application_id
5848 --
5849 , p_level => C_HEADER
5850 , p_procedure => p_procedure
5851 );
5852
5853 -- Bugfix 4417664
5854 l_sql_statement := xla_cmp_string_pkg.replace_token( l_sql_statement,'$hdr_tabs$',
5855 nvl(GenerateFromHdrTabs (
5856 --
5857 p_array_table_name
5858 , p_array_parent_table_index
5859 , p_array_table_hash
5860 , p_array_populated_flag
5861 --
5862 , p_array_ref_obj_flag
5863 --
5864 , p_array_h_source_index
5865 , p_array_h_table_index
5866 --
5867 , p_array_h_mls_source_index
5868 , p_array_h_mls_table_index
5869 --
5870 , p_array_source_code
5871 , p_array_lookup_type
5872 , p_array_view_application_id
5873 ), ' ')
5874 );
5875
5876
5877 l_sql_statement := xla_cmp_string_pkg.replace_token( l_sql_statement,'$hdr_clauses$',
5878 nvl(GenerateHdrWhereClause (
5879 --
5880 p_array_table_name
5881 , p_array_parent_table_index
5882 , p_array_table_hash
5883 , p_array_populated_flag
5884 --
5885 , p_array_ref_obj_flag
5886 , p_array_join_condition
5887 --
5888 , p_array_h_source_index
5889 , p_array_h_table_index
5890 --
5891 , p_array_h_mls_source_index
5892 , p_array_h_mls_table_index
5893 --
5894 , p_array_source_code
5895 , p_array_lookup_type
5896 , p_array_view_application_id
5897 ),' ')
5898 );
5899
5900 ELSE
5901
5902 l_sql_statement := ' ';
5903
5904 END IF;
5905
5906 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5907
5908 trace
5909 (p_msg => 'END of GenerateInsertHdrSources'
5910 ,p_level => C_LEVEL_PROCEDURE
5911 ,p_module => l_log_module);
5912
5913 END IF;
5914 RETURN l_sql_statement;
5915 EXCEPTION
5916 WHEN VALUE_ERROR THEN
5917
5918 IF (C_LEVEL_UNEXPECTED >= g_log_level) THEN
5919 trace
5920 (p_msg => 'ERROR: XLA_CMP_COMPILER_ERROR '||sqlerrm
5921 ,p_level => C_LEVEL_UNEXPECTED
5922 ,p_module => l_log_module);
5923 END IF;
5924
5925 RAISE;
5926 WHEN xla_exceptions_pkg.application_exception THEN
5927 RAISE;
5928 WHEN OTHERS THEN
5929 xla_exceptions_pkg.raise_message
5930 (p_location => 'xla_cmp_extract_pkg.GenerateInsertHdrSources ');
5931 END GenerateInsertHdrSources;
5932
5933 --+==========================================================================+
5934 --| |
5935 --| PUBLIC function |
5936 --| |
5937 --| Generate the INSERT SQL statement used by the Extract Source Values |
5938 --| Dump to insert the line source values into xla_diag_sources |
5939 --| |
5940 --| |
5941 --+==========================================================================+
5942 --
5943 FUNCTION GenerateInsertLineSources (
5944 --
5945 p_array_table_name IN xla_cmp_source_pkg.t_array_VL30
5946 , p_array_parent_table_index IN xla_cmp_source_pkg.t_array_ByInt
5947 , p_array_table_hash IN xla_cmp_source_pkg.t_array_VL30
5948 , p_array_table_type IN xla_cmp_source_pkg.t_array_VL30
5949 , p_array_populated_flag IN xla_cmp_source_pkg.t_array_VL1
5950 --
5951 , p_array_ref_obj_flag IN xla_cmp_source_pkg.t_array_VL1
5952 , p_array_join_condition IN xla_cmp_source_pkg.t_array_VL2000
5953 --
5954 , p_array_l_source_index IN xla_cmp_source_pkg.t_array_ByInt
5955 , p_array_l_table_index IN xla_cmp_source_pkg.t_array_ByInt
5956 --
5957 , p_array_l_mls_source_index IN xla_cmp_source_pkg.t_array_ByInt
5958 , p_array_l_mls_table_index IN xla_cmp_source_pkg.t_array_ByInt
5959 --
5960 , p_array_application_id IN xla_cmp_source_pkg.t_array_Num
5961 , p_array_source_code IN xla_cmp_source_pkg.t_array_VL30
5965 , p_array_view_application_id IN xla_cmp_source_pkg.t_array_Num
5962 , p_array_source_type_code IN xla_cmp_source_pkg.t_array_VL1
5963 , p_array_flex_value_set_id IN xla_cmp_source_pkg.t_array_Num
5964 , p_array_lookup_type IN xla_cmp_source_pkg.t_array_VL30
5966 --
5967 ,p_procedure IN VARCHAR2
5968 )
5969 RETURN CLOB
5970 IS
5971 --
5972 l_sql_statement CLOB;
5973 l_log_module VARCHAR2(240);
5974 --
5975 l_array_source_index xla_cmp_source_pkg.t_array_ByInt;
5976 l_array_table_index xla_cmp_source_pkg.t_array_ByInt;
5977 --
5978 BEGIN
5979 --
5980 IF g_log_enabled THEN
5981 l_log_module := C_DEFAULT_MODULE||'.GenerateInsertLineSources';
5982 END IF;
5983 --
5984 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5985
5986 trace
5987 (p_msg => 'BEGIN of GenerateInsertLineSources'
5988 ,p_level => C_LEVEL_PROCEDURE
5989 ,p_module => l_log_module);
5990
5991 END IF;
5992 --
5993
5994 IF NVL(p_array_l_source_index.COUNT,0) > 0 THEN
5995
5996 FOR Idx IN p_array_l_source_index.FIRST .. p_array_l_source_index.LAST LOOP
5997
5998 IF p_array_l_source_index.EXISTS(Idx) THEN
5999
6000 l_array_source_index(Idx) := p_array_l_source_index(Idx);
6001 l_array_table_index(Idx) := p_array_l_table_index(Idx);
6002
6003 END IF;
6004
6005 END LOOP;
6006
6007 END IF;
6008
6009 IF NVL(p_array_l_mls_source_index.COUNT,0)> 0 THEN
6010
6011 FOR Idx IN p_array_l_mls_source_index.FIRST .. p_array_l_mls_source_index.LAST LOOP
6012
6013 IF p_array_l_mls_source_index.EXISTS(Idx) THEN
6014
6015 l_array_source_index(Idx) := p_array_l_mls_source_index(Idx);
6016 l_array_table_index(Idx) := p_array_l_mls_table_index(Idx);
6017
6018 END IF;
6019
6020 END LOOP;
6021
6022 END IF;
6023
6024 IF NVL(p_array_l_source_index.COUNT,0)+
6025 NVL(p_array_l_mls_source_index.COUNT,0)> 0 THEN
6026
6027 l_sql_statement := GenerateInsertStm(
6028 p_array_table_name => p_array_table_name
6029 , p_array_table_type => p_array_table_type
6030 , p_array_table_index => l_array_table_index
6031 , p_array_table_hash => p_array_table_hash
6032 , p_array_ref_obj_flag => p_array_ref_obj_flag
6033 , p_array_populated_flag => p_array_populated_flag
6034 --
6035 , p_array_source_index => l_array_source_index
6036 , p_array_source_code => p_array_source_code
6037 , p_array_application_id => p_array_application_id
6038 , p_array_source_type_code => p_array_source_type_code
6039 , p_array_flex_value_set_id => p_array_flex_value_set_id
6040 , p_array_lookup_type => p_array_lookup_type
6041 , p_array_view_application_id => p_array_view_application_id
6042 --
6043 , p_level => C_LINE
6044 , p_procedure => p_procedure
6045 );
6046
6047 -- Bugfix 4417664
6048 l_sql_statement := xla_cmp_string_pkg.replace_token( l_sql_statement,'$line_tabs$',
6049 nvl(GenerateFromLineTabs (
6050 --
6051 p_array_table_name
6052 , p_array_parent_table_index
6053 , p_array_table_hash
6054 , p_array_populated_flag
6055 --
6056 , p_array_ref_obj_flag
6057 --
6058 , p_array_l_source_index
6059 , p_array_l_table_index
6060 --
6061 , p_array_l_mls_source_index
6062 , p_array_l_mls_table_index
6063 --
6064 , p_array_source_code
6065 , p_array_lookup_type
6066 , p_array_view_application_id
6067 ),' ')
6068 );
6069
6070 l_sql_statement := xla_cmp_string_pkg.replace_token( l_sql_statement,'$line_clauses$',
6071 nvl(GenerateLineWhereClause (
6072 --
6073 p_array_table_name
6077 --
6074 , p_array_parent_table_index
6075 , p_array_table_hash
6076 , p_array_populated_flag
6078 , p_array_ref_obj_flag
6079 , p_array_join_condition
6080 --
6081 , p_array_l_source_index
6082 , p_array_l_table_index
6083 --
6084 , p_array_l_mls_source_index
6085 , p_array_l_mls_table_index
6086 --
6087 , p_array_source_code
6088 , p_array_lookup_type
6089 , p_array_view_application_id
6090 ),' ')
6091 );
6092
6093
6094 ELSE
6095
6096 l_sql_statement := ' ';
6097
6098 END IF;
6099
6100 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
6101
6102 trace
6103 (p_msg => 'END of GenerateInsertLineSources'
6104 ,p_level => C_LEVEL_PROCEDURE
6105 ,p_module => l_log_module);
6106
6107 END IF;
6108 RETURN l_sql_statement;
6109 EXCEPTION
6110 WHEN VALUE_ERROR THEN
6111
6112 IF (C_LEVEL_UNEXPECTED >= g_log_level) THEN
6113 trace
6114 (p_msg => 'ERROR: XLA_CMP_COMPILER_ERROR '||sqlerrm
6115 ,p_level => C_LEVEL_UNEXPECTED
6116 ,p_module => l_log_module);
6117 END IF;
6118
6119 RAISE;
6120
6121 WHEN xla_exceptions_pkg.application_exception THEN
6122 RAISE;
6123 WHEN OTHERS THEN
6124 xla_exceptions_pkg.raise_message
6125 (p_location => 'xla_cmp_extract_pkg.GenerateInsertLineSources ');
6126 END GenerateInsertLineSources;
6127
6128
6129
6130
6131
6132 --
6133 --=============================================================================
6134 --
6135 --
6136 --
6137 --
6138 --
6139 --
6140 --
6141 --
6142 --
6143 --
6144 --
6145 --
6146 --
6147 --
6148 --
6149 --
6150 --
6151 --
6152 --
6153 --
6154 --
6155 --
6156 --=============================================================================
6157 --=============================================================================
6158 -- *********** Initialization routine **********
6159 --=============================================================================
6160
6161 BEGIN
6162
6163 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6164 g_log_enabled := fnd_log.test
6165 (log_level => g_log_level
6166 ,module => C_DEFAULT_MODULE);
6167
6168 IF NOT g_log_enabled THEN
6169 g_log_level := C_LEVEL_LOG_DISABLED;
6170 END IF;
6171 --
6172 END xla_cmp_extract_pkg;