[Home] [Help]
PACKAGE BODY: APPS.XLA_EXTRACT_INTEGRITY_PKG
Source
1 PACKAGE BODY XLA_EXTRACT_INTEGRITY_PKG AS
2 /* $Header: xlaamext.pkb 120.46.12020000.3 2013/03/29 14:36:52 sragadde ship $ */
3 /*===========================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +============================================================================+
8 | PACKAGE NAME |
9 | xla_extract_integrity_pkg |
10 | |
11 | DESCRIPTION |
12 | This is the body of the package that checks the extract integrity |
13 | for an event class and creates sources and source assignments for the |
14 | event class if required |
15 | |
16 | HISTORY |
17 | 12/16/2003 Dimple Shah Created |
18 | 06/08/2005 S. Singhania Bug 4420371. This reversed the changes |
19 | done to fix 3851636 |
20 | 20/01/2012 VGOPISET 13344804 performance issue with source |
21 | population for the Primary and Reference|
22 | Extract Objects from DBA tables. |
23 | 30/03/2013 VGOPISET 16418232 performance issue with |
24 | when object synonymn case |
25 +===========================================================================*/
26
27 --=============================================================================
28 -- **************** declaraions ********************
29 --=============================================================================
30 -------------------------------------------------------------------------------
31 -- declaring private package variables
32 -------------------------------------------------------------------------------
33
34 g_creation_date DATE;
35 g_last_update_date DATE;
36 g_created_by INTEGER;
37 g_last_update_login INTEGER;
38 g_last_updated_by INTEGER;
39
40 -------------------------------------------------------------------------------
41 -- Constants
42 -------------------------------------------------------------------------------
43 C_REF_OBJECT_FLAG_N CONSTANT VARCHAR2(1) := 'N';
44 C_REF_OBJECT_FLAG_Y CONSTANT VARCHAR2(1) := 'Y';
45
46 -------------------------------------------------------------------------------
47 -- declaring private package arrays
48 -------------------------------------------------------------------------------
49 TYPE t_array_codes IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
50 TYPE t_array_type_codes IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
51 TYPE t_array_vl2000 IS table OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
52 TYPE t_array_id IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
53
54 -------------------------------------------------------------------------------
55 -- forward declarion of private procedures and functions
56 -------------------------------------------------------------------------------
57 FUNCTION Chk_primary_keys_exist
58 (p_application_id IN NUMBER
59 ,p_entity_code IN VARCHAR2
60 ,p_event_class_code IN VARCHAR2
61 ,p_amb_context_code IN VARCHAR2 DEFAULT NULL
62 ,p_product_rule_type_code IN VARCHAR2 DEFAULT NULL
63 ,p_product_rule_code IN VARCHAR2 DEFAULT NULL)
64 RETURN BOOLEAN;
65
66 FUNCTION Validate_accounting_sources
67 (p_application_id IN NUMBER
68 ,p_entity_code IN VARCHAR2
69 ,p_event_class_code IN VARCHAR2)
70 RETURN BOOLEAN;
71
72 FUNCTION Create_sources
73 (p_application_id IN NUMBER
74 ,p_entity_code IN VARCHAR2
75 ,p_event_class_code IN VARCHAR2)
76 RETURN BOOLEAN;
77
78 PROCEDURE Assign_sources
79 (p_application_id IN NUMBER
80 ,p_entity_code IN VARCHAR2
81 ,p_event_class_code IN VARCHAR2);
82
83 --=============================================================================
84 -- *********** Local Trace Routine **********
85 --=============================================================================
86 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
87 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
88 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
89 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
90 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
91 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
92
93 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_extract_integrity_pkg';
94
95 g_trace_label VARCHAR2(240);
96 g_log_level NUMBER;
97 g_log_enabled BOOLEAN;
98
99 PROCEDURE trace
100 (p_msg IN VARCHAR2
101 ,p_level IN NUMBER) IS
102
103 l_module VARCHAR2(240);
104 BEGIN
105
106 IF (g_log_level is NULL) THEN
107 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
108 END IF;
109
110 IF (g_log_level is NULL) THEN
111 g_log_enabled := fnd_log.test
112 (log_level => g_log_level
113 ,module => C_DEFAULT_MODULE);
114 END IF;
115
116 l_module := C_DEFAULT_MODULE||'.'||g_trace_label;
117
118 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
119 fnd_log.message(p_level, l_module);
120 ELSIF p_level >= g_log_level THEN
121 fnd_log.string(p_level, l_module, p_msg);
122 END IF;
123 EXCEPTION
124 WHEN xla_exceptions_pkg.application_exception THEN
125 RAISE;
126 WHEN OTHERS THEN
127 xla_exceptions_pkg.raise_message
128 (p_location => 'xla_extract_integrity_pkg.trace');
129 END trace;
130
131 --=============================================================================
132 -- *********** public procedures and functions **********
133 --=============================================================================
134 --=============================================================================
135 --
136 -- Following are the public routines:
137 --
138 -- 1. Check_extract_integrity
139 -- 2. Validate_extract_objects
140 -- 3. Validate_sources
141 -- 4. Validate_sources_with_extract
142 -- 5. set_extract_object_owner
143 --
144 --=============================================================================
145
146 /*======================================================================+
147 | |
148 | Public Function |
149 | |
150 | Check_extract_integrity |
151 | |
152 | This routine is called by the Create and Assign Sources program |
153 | to do all validations for an event class |
154 | |
155 +======================================================================*/
156 FUNCTION Check_extract_integrity
157 (p_application_id IN NUMBER
158 ,p_entity_code IN VARCHAR2
159 ,p_event_class_code IN VARCHAR2
160 ,p_processing_mode IN VARCHAR2)
161 RETURN BOOLEAN
162 IS
163
164 l_application_id NUMBER(15);
165 l_entity_code VARCHAR2(30);
166 l_event_class_code VARCHAR2(30);
167 l_return BOOLEAN := TRUE;
168
169 BEGIN
170
171 l_application_id := p_application_id;
172 l_entity_code := p_entity_code;
173 l_event_class_code := p_event_class_code;
174
175 IF (g_log_level is NULL) THEN
176 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
177 END IF;
178
179 IF (g_log_level is NULL) THEN
180 g_log_enabled := fnd_log.test
181 (log_level => g_log_level
182 ,module => C_DEFAULT_MODULE);
183 END IF;
184
185 IF (g_log_level is NULL) THEN
186 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
187 END IF;
188
189 IF (g_log_level is NULL) THEN
190 g_log_enabled := fnd_log.test
191 (log_level => g_log_level
192 ,module => C_DEFAULT_MODULE);
193 END IF;
194
195 g_trace_label :='Check_extract_integrity';
196 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
197 trace
198 (p_msg => 'Begin'
199 ,p_level => C_LEVEL_PROCEDURE);
200 trace
201 (p_msg => 'p_application_id = ' ||TO_CHAR(p_application_id)
202 ,p_level => C_LEVEL_PROCEDURE);
203 trace
204 (p_msg => 'p_entity_code = '||p_entity_code
205 ,p_level => C_LEVEL_PROCEDURE);
206 trace
207 (p_msg => 'p_event_class_code = ' ||p_event_class_code
208 ,p_level => C_LEVEL_PROCEDURE);
209 trace
210 (p_msg => 'p_processing_mode = ' ||p_processing_mode
211 ,p_level => C_LEVEL_PROCEDURE);
212 END IF;
213
214 -- Set environment settings
215 xla_environment_pkg.refresh;
216
217 -- Delete the error table for the event class
218 DELETE
219 FROM xla_amb_setup_errors
220 WHERE application_id = p_application_id
221 AND entity_code = p_entity_code
222 AND event_class_code = p_event_class_code
223 AND product_rule_code IS NULL;
224
225 -- Initialize the error package
226 Xla_amb_setup_err_pkg.initialize;
227
228 -- Get the extract object owner and store in GT table.
229 xla_extract_integrity_pkg.set_extract_object_owner
230 (p_application_id => l_application_id
231 ,p_entity_code => l_entity_code
232 ,p_event_class_code => l_event_class_code
233 );
234
235 -- Validate extract objects
236 IF NOT Xla_extract_integrity_pkg.validate_extract_objects
237 (p_application_id => l_application_id
238 ,p_entity_code => l_entity_code
239 ,p_event_class_code => l_event_class_code) THEN
240
241 l_return := FALSE;
242 END IF;
243
244 -- Validate primary keys
245 IF NOT Chk_primary_keys_exist
246 (p_application_id => l_application_id
247 ,p_entity_code => l_entity_code
248 ,p_event_class_code => l_event_class_code) THEN
249 l_return := FALSE;
250 END IF;
251
252 IF p_processing_mode = 'CREATE' THEN
253
254 -- Create sources
255 IF NOT Create_sources
256 (p_application_id => l_application_id
257 ,p_entity_code => l_entity_code
258 ,p_event_class_code => l_event_class_code) THEN
259 l_return := FALSE;
260 END IF;
261
262 -- Assign sources
263 Assign_sources
264 (p_application_id => l_application_id
265 ,p_entity_code => l_entity_code
266 ,p_event_class_code => l_event_class_code);
267
268 ELSIF p_processing_mode = 'VALIDATE' THEN
269
270 -- Validate sources with the extract objects
271 IF NOT Xla_extract_integrity_pkg.Validate_sources
272 (p_application_id => l_application_id
273 ,p_entity_code => l_entity_code
274 ,p_event_class_code => l_event_class_code) THEN
275 l_return := FALSE;
276 END IF;
277
278 -- Validate accounting sources
279 IF NOT Validate_accounting_sources
280 (p_application_id => l_application_id
281 ,p_entity_code => l_entity_code
282 ,p_event_class_code => l_event_class_code) THEN
283 l_return := FALSE;
284 END IF;
285 END IF;
286
287 -- Insert errors into the error table from the plsql array
288 Xla_amb_setup_err_pkg.insert_errors;
289 COMMIT;
290
291 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
292 trace
293 (p_msg => 'End'
294 ,p_level => C_LEVEL_PROCEDURE);
295 END IF;
296
297 RETURN l_return;
298
299 EXCEPTION
300 WHEN xla_exceptions_pkg.application_exception THEN
301 RAISE;
302 WHEN OTHERS THEN
303 xla_exceptions_pkg.raise_message
304 (p_location => 'xla_extract_integrity_pkg.check_extract_integrity');
305 END Check_extract_integrity; -- end of function
306
307 /*======================================================================+
308 | |
309 | Public Function |
310 | |
311 | Validate_extract_objects |
312 | |
313 | This routine is called to validate the extract objects |
314 | |
315 +======================================================================*/
316 FUNCTION Validate_extract_objects
317 (p_application_id IN NUMBER
318 ,p_entity_code IN VARCHAR2
319 ,p_event_class_code IN VARCHAR2
320 ,p_amb_context_code IN VARCHAR2
321 ,p_product_rule_type_code IN VARCHAR2
322 ,p_product_rule_code IN VARCHAR2)
323 RETURN BOOLEAN
324 IS
325 -- Variable Declaration
326 l_application_id NUMBER(15);
327 l_entity_code VARCHAR2(30);
328 l_event_class_code VARCHAR2(30);
329 l_amb_context_code VARCHAR2(30);
330 l_product_rule_code VARCHAR2(30);
331 l_product_rule_type_code VARCHAR2(1);
332 l_return BOOLEAN := TRUE;
333 l_exist VARCHAR2(1) := NULL;
334
335 -- Cursor Declaration
336
337 -- Check if extract objects are assigned to an event class
338
339 CURSOR c_ec_obj_exist
340 IS
341 SELECT 'x'
342 FROM xla_extract_objects e
343 WHERE application_id = p_application_id
344 AND entity_code = p_entity_code
345 AND event_class_code = p_event_class_code;
346
347 -- Get all event classes for which extract objects are not assigned
348
349 CURSOR c_aad_obj_exist
350 IS
351 SELECT h.entity_code, h.event_class_code
352 FROM xla_prod_acct_headers h
353 WHERE h.application_id = p_application_id
354 AND h.amb_context_code = p_amb_context_code
355 AND h.product_rule_type_code = p_product_rule_type_code
356 AND h.product_rule_code = p_product_rule_code
357 AND h.accounting_required_flag = 'Y'
358 AND NOT EXISTS (SELECT 'x'
359 FROM xla_extract_objects e
360 WHERE e.application_id = h.application_id
361 AND e.entity_code = h.entity_code
362 AND e.event_class_code = h.event_class_code);
363
364 l_aad_obj_exist c_aad_obj_exist%rowtype;
365
366 -- Get all extract objects for the event class that are not defined in the
367 -- database
368
369 CURSOR c_ec_objects
370 IS
371 SELECT object_name
372 ,object_type_code
373 ,C_REF_OBJECT_FLAG_N ref_object_flag
374 FROM xla_extract_objects e
375 WHERE application_id = p_application_id
376 AND entity_code = p_entity_code
377 AND event_class_code = p_event_class_code
378 AND not exists (SELECT 'x'
379 FROM xla_extract_objects_gt o
380 WHERE o.object_name = e.object_name)
381 --
382 -- Get all reference objects for the event class that are not defined in the
383 -- database
384 UNION ALL
385 SELECT r.reference_object_name
386 ,e.object_type_code
387 ,C_REF_OBJECT_FLAG_Y ref_object_flag
388 FROM xla_reference_objects r
389 ,xla_extract_objects e
390 WHERE r.application_id = p_application_id
391 AND r.entity_code = p_entity_code
392 AND r.event_class_code = p_event_class_code
393 AND e.application_id = r.application_id
394 AND e.entity_code = r.entity_code
395 AND e.event_class_code = r.event_class_code
396 AND e.object_name = r.object_name
397 AND not exists (SELECT 'x'
398 FROM xla_reference_objects_gt o
399 WHERE o.reference_object_name = r.reference_object_name);
400
401
402 l_ec_objects c_ec_objects%rowtype;
403
404 -- Get all event classes for the AAD whose extract objects are not
405 -- defined in the database
406
407 CURSOR c_aad_objects
408 IS
409 SELECT e.entity_code
410 ,e.event_class_code
411 ,e.object_name
412 ,e.object_type_code
413 ,C_REF_OBJECT_FLAG_N ref_object_flag
414 FROM xla_extract_objects e, xla_prod_acct_headers h
415 WHERE h.application_id = p_application_id
416 AND h.amb_context_code = p_amb_context_code
417 AND h.product_rule_type_code = p_product_rule_type_code
418 AND h.product_rule_code = p_product_rule_code
419 AND h.accounting_required_flag = 'Y'
420 AND e.application_id = h.application_id
421 AND e.entity_code = h.entity_code
422 AND e.event_class_code = h.event_class_code
423 AND not exists (SELECT 'x'
424 FROM xla_extract_objects_gt o
425 WHERE o.object_name = e.object_name)
426 UNION ALL
427 SELECT r.entity_code
428 ,r.event_class_code
429 ,r.reference_object_name
430 ,e.object_type_code
431 ,C_REF_OBJECT_FLAG_Y ref_object_flag
432 FROM xla_reference_objects r,
433 xla_extract_objects e,
434 xla_prod_acct_headers h
435 WHERE h.application_id = p_application_id
436 AND h.amb_context_code = p_amb_context_code
437 AND h.product_rule_type_code = p_product_rule_type_code
438 AND h.product_rule_code = p_product_rule_code
439 AND h.accounting_required_flag = 'Y'
440 AND r.application_id = h.application_id
441 AND r.entity_code = h.entity_code
442 AND r.event_class_code = h.event_class_code
443 AND e.application_id = r.application_id
444 AND e.entity_code = r.entity_code
445 AND e.event_class_code = r.event_class_code
446 AND not exists (SELECT 'x'
447 FROM xla_reference_objects_gt o
448 WHERE o.reference_object_name = r.reference_object_name);
449
450 l_aad_objects c_aad_objects%rowtype;
451 l_message_name VARCHAR2(30);
452
453 BEGIN
454
455 l_application_id := p_application_id;
456 l_entity_code := p_entity_code;
457 l_event_class_code := p_event_class_code;
458 l_amb_context_code := p_amb_context_code;
459 l_product_rule_code := p_product_rule_code;
460 l_product_rule_type_code := p_product_rule_type_code;
461
462 IF (g_log_level is NULL) THEN
463 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
464 END IF;
465
466 IF (g_log_level is NULL) THEN
467 g_log_enabled := fnd_log.test
468 (log_level => g_log_level
469 ,module => C_DEFAULT_MODULE);
470 END IF;
471
472 g_trace_label :='Validate_extract_objects';
473 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
474 trace
475 (p_msg => 'Begin'
476 ,p_level => C_LEVEL_PROCEDURE);
477 trace
478 (p_msg => 'p_application_id = ' ||TO_CHAR(p_application_id)
479 ,p_level => C_LEVEL_PROCEDURE);
480 trace
481 (p_msg => 'p_entity_code = '||p_entity_code
482 ,p_level => C_LEVEL_PROCEDURE);
483 trace
484 (p_msg => 'p_event_class_code = ' ||p_event_class_code
485 ,p_level => C_LEVEL_PROCEDURE);
486 trace
487 (p_msg => 'p_amb_context_code = '||p_amb_context_code
488 ,p_level => C_LEVEL_PROCEDURE);
489 trace
490 (p_msg => 'p_product_rule_type_code = ' ||p_product_rule_type_code
491 ,p_level => C_LEVEL_PROCEDURE);
492 trace
493 (p_msg => 'p_product_rule_code = ' ||p_product_rule_code
494 ,p_level => C_LEVEL_PROCEDURE);
495 END IF;
496
497 -- Validate extract objects for an event class
498 IF p_event_class_code is not null then
499
500 -- Check if atleast one extract object is assigned to the event class
501 OPEN c_ec_obj_exist;
502 FETCH c_ec_obj_exist
503 INTO l_exist;
504 IF c_ec_obj_exist%NOTFOUND THEN
505 Xla_amb_setup_err_pkg.stack_error
506 (p_message_name => 'XLA_AB_EC_NO_EXTRACT_OBJECTS'
507 ,p_message_type => 'E'
508 ,p_message_category => 'EVENT_CLASS'
509 ,p_category_sequence => 2
510 ,p_application_id => l_application_id
511 ,p_entity_code => l_entity_code
512 ,p_event_class_code => l_event_class_code);
513
514 l_return := FALSE;
515 END IF;
516 CLOSE c_ec_obj_exist;
517
518 -- Check if the extract objects assigned to the event class exist
519 -- in the database
520
521 OPEN c_ec_objects;
522 LOOP
523 FETCH c_ec_objects
524 INTO l_ec_objects;
525 EXIT WHEN c_ec_objects%notfound;
526
527 IF l_ec_objects.ref_object_flag = C_REF_OBJECT_FLAG_Y THEN
528 l_message_name := 'XLA_AB_REF_OBJECT_NOT_DEFINED';
529 ELSE
530 l_message_name := 'XLA_AB_EXT_OBJECT_NOT_DEFINED';
531 END IF;
532
533 Xla_amb_setup_err_pkg.stack_error
534 (p_message_name => l_message_name
535 ,p_message_type => 'E'
536 ,p_message_category => 'EXTRACT_OBJECT'
537 ,p_category_sequence => 3
538 ,p_application_id => l_application_id
539 ,p_entity_code => l_entity_code
540 ,p_event_class_code => l_event_class_code
541 ,p_extract_object_name => l_ec_objects.object_name
542 ,p_extract_object_type => l_ec_objects.object_type_code);
543
544 l_return := FALSE;
545 END LOOP;
546 CLOSE c_ec_objects;
547
548 -- Validate extract objects for an application accounting definition
549 ELSIF p_product_rule_code is not null then
550
551 -- Error all event classes that do not have extract objects assigned
552
553 OPEN c_aad_obj_exist;
554 LOOP
555 FETCH c_aad_obj_exist
556 INTO l_aad_obj_exist;
557 EXIT WHEN c_aad_obj_exist%notfound;
558 Xla_amb_setup_err_pkg.stack_error
559 (p_message_name => 'XLA_AB_EC_NO_EXTRACT_OBJECTS'
560 ,p_message_type => 'W'
561 ,p_message_category => 'EVENT_CLASS'
562 ,p_category_sequence => 2
563 ,p_application_id => l_application_id
564 ,p_entity_code => l_aad_obj_exist.entity_code
565 ,p_event_class_code => l_aad_obj_exist.event_class_code
566 ,p_amb_context_code => l_amb_context_code
567 ,p_product_rule_type_code => l_product_rule_type_code
568 ,p_product_rule_code => l_product_rule_code);
569
570 l_return := FALSE;
571 END LOOP;
572 CLOSE c_aad_obj_exist;
573
574 -- Error all event classes whose extract objects
575 -- are not defined in the database
576
577 OPEN c_aad_objects;
578 LOOP
579 FETCH c_aad_objects
580 INTO l_aad_objects;
581 EXIT WHEN c_aad_objects%notfound;
582
583 IF l_aad_objects.ref_object_flag = C_REF_OBJECT_FLAG_Y THEN
584 l_message_name := 'XLA_AB_REF_OBJECT_NOT_DEFINED';
585 ELSE
586 l_message_name := 'XLA_AB_EXT_OBJECT_NOT_DEFINED';
587 END IF;
588
589 Xla_amb_setup_err_pkg.stack_error
590 (p_message_name => 'XLA_AB_EXT_OBJECT_NOT_DEFINED'
591 ,p_message_type => 'W'
592 ,p_message_category => 'EXTRACT_OBJECT'
593 ,p_category_sequence => 3
594 ,p_application_id => l_application_id
595 ,p_entity_code => l_aad_objects.entity_code
596 ,p_event_class_code => l_aad_objects.event_class_code
597 ,p_extract_object_name => l_aad_objects.object_name
598 ,p_extract_object_type => l_aad_objects.object_type_code
599 ,p_amb_context_code => l_amb_context_code
600 ,p_product_rule_type_code => l_product_rule_type_code
601 ,p_product_rule_code => l_product_rule_code);
602
603 l_return := FALSE;
604 END LOOP;
605 CLOSE c_aad_objects;
606 END IF;
607
608 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
609 trace
610 (p_msg => 'End'
611 ,p_level => C_LEVEL_PROCEDURE);
612 END IF;
613
614 RETURN l_return;
615
616 EXCEPTION
617 WHEN xla_exceptions_pkg.application_exception THEN
618 RAISE;
619 WHEN OTHERS THEN
620 xla_exceptions_pkg.raise_message
621 (p_location => 'xla_extract_integrity_pkg.validate_extract_objects');
622 END validate_extract_objects; -- end of function
623
624 /*======================================================================+
625 | |
626 | Public Function |
627 | |
628 | Validate_sources |
629 | |
630 | This routine is called to insert all sources for an event class into |
631 | a global temporary table before calling validate_sources_with_extract |
632 | |
633 +======================================================================*/
634 FUNCTION Validate_sources
635 (p_application_id IN NUMBER
636 ,p_entity_code IN VARCHAR2
637 ,p_event_class_code IN VARCHAR2)
638 RETURN BOOLEAN
639 IS
640 -- Variable Declaration
641
642 l_application_id NUMBER(15);
643 l_entity_code VARCHAR2(30);
644 l_event_class_code VARCHAR2(30);
645 l_return BOOLEAN := TRUE;
646 l_exist VARCHAR2(1) := NULL;
647
648 -- Cursor Declaration
649
650 -- Check if GT table has any sources
651 CURSOR c_gt_sources
652 IS
653 SELECT 'x'
654 FROM xla_evt_class_sources_gt
655 WHERE application_id = p_application_id
656 AND entity_code = p_entity_code
657 AND event_class_code = p_event_class_code;
658
659 BEGIN
660
661 l_application_id := p_application_id;
662 l_entity_code := p_entity_code;
663 l_event_class_code := p_event_class_code;
664
665 IF (g_log_level is NULL) THEN
666 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
667 END IF;
668
669 IF (g_log_level is NULL) THEN
670 g_log_enabled := fnd_log.test
671 (log_level => g_log_level
672 ,module => C_DEFAULT_MODULE);
673 END IF;
674
675 g_trace_label :='Validate_Sources';
676 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
677 trace
678 (p_msg => 'Begin'
679 ,p_level => C_LEVEL_PROCEDURE);
680 trace
681 (p_msg => 'p_application_id = ' ||TO_CHAR(p_application_id)
682 ,p_level => C_LEVEL_PROCEDURE);
683 trace
684 (p_msg => 'p_entity_code = '||p_entity_code
685 ,p_level => C_LEVEL_PROCEDURE);
686 trace
687 (p_msg => 'p_event_class_code = ' ||p_event_class_code
688 ,p_level => C_LEVEL_PROCEDURE);
689 END IF;
690
691 -- Insert all sources that are assigned to the event class into the GT table
692 INSERT INTO xla_evt_class_sources_gt
693 (application_id
694 ,entity_code
695 ,event_class_code
696 ,source_application_id
697 ,source_code
698 ,source_datatype_code,source_level_code)
699 (SELECT e.application_id
700 ,e.entity_code
701 ,e.event_class_code
702 ,e.source_application_id
703 ,e.source_code
704 ,decode(s.datatype_code,'N','NUMBER',
705 'C','VARCHAR2', 'D','DATE') source_datatype_code,
706 decode(s.translated_flag,'N',
707 decode(e.source_code,'LANGUAGE',
708 decode(e.level_code,'H','HEADER_MLS','L','LINE_MLS'),
709 decode(e.level_code,'H',
710 'HEADER','L','LINE')),
711 'Y',
712 decode(e.level_code,'H','HEADER_MLS','L','LINE_MLS'))
713 source_level_code
714 FROM xla_event_sources e, xla_sources_b s
715 WHERE e.source_application_id = s.application_id
716 AND e.source_code = s.source_code
717 AND e.source_type_code = s.source_type_code
718 AND e.application_id = p_application_id
719 AND e.entity_code = p_entity_code
720 AND e.event_class_code = p_event_class_code);
721
722 OPEN c_gt_sources;
723 FETCH c_gt_sources
724 INTO l_exist;
725 IF c_gt_sources%found THEN
726
727 -- Call the function to validate all sources in the GT table
728 IF NOT Xla_extract_integrity_pkg.validate_sources_with_extract
729 (p_application_id => l_application_id
730 ,p_entity_code => l_entity_code
731 ,p_event_class_code => l_event_class_code) THEN
732 l_return := FALSE;
733 END IF;
734
735 END IF;
736 CLOSE c_gt_sources;
737
738 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
739 trace
740 (p_msg => 'End'
741 ,p_level => C_LEVEL_PROCEDURE);
742 END IF;
743
744 RETURN l_return;
745
746 EXCEPTION
747 WHEN xla_exceptions_pkg.application_exception THEN
748 RAISE;
749 WHEN OTHERS THEN
750 xla_exceptions_pkg.raise_message
751 (p_location => 'xla_extract_integrity_pkg.validate_sources');
752 END Validate_sources; -- end of function
753
754 /*======================================================================+
755 | |
756 | Public Function |
757 | |
758 | Validate_sources_with_extract |
759 | |
760 | This routine is called to validate the sources with extract objects |
761 | |
762 +======================================================================*/
763 FUNCTION Validate_sources_with_extract
764 (p_application_id IN NUMBER
765 ,p_entity_code IN VARCHAR2
766 ,p_event_class_code IN VARCHAR2
767 ,p_amb_context_code IN VARCHAR2
768 ,p_product_rule_type_code IN VARCHAR2
769 ,p_product_rule_code IN VARCHAR2)
770 RETURN BOOLEAN
771 IS
772
773 -- Variable Declaration
774 l_application_id NUMBER(15);
775 l_entity_code VARCHAR2(30);
776 l_event_class_code VARCHAR2(30);
777 l_amb_context_code VARCHAR2(30);
778 l_product_rule_code VARCHAR2(30);
779 l_product_rule_type_code VARCHAR2(1);
780 l_return BOOLEAN := TRUE;
781 l_exist VARCHAR2(1) := NULL;
782
783 -- Variables of type Array
784 l_array_pop_source_appl_id t_array_id;
785 l_array_pop_source_code t_array_codes;
786 l_array_pop_object_name t_array_codes;
787 l_array_pop_object_type t_array_codes;
788 l_array_pop_pop_flag t_array_type_codes;
789 l_array_pop_col_datatype t_array_codes;
790
791 l_array_ref_pop_source_appl_id t_array_id;
792 l_array_ref_pop_source_code t_array_codes;
793 l_array_ref_pop_object_name t_array_codes;
794 l_array_ref_pop_object_type t_array_codes;
795 l_array_ref_pop_pop_flag t_array_type_codes;
796 l_array_ref_pop_col_datatype t_array_codes;
797 l_array_ref_pop_join_condition t_array_vl2000;
798 l_array_ref_pop_linked_obj t_array_codes;
799
800 l_array_source_appl_id t_array_id;
801 l_array_source_code t_array_codes;
802 l_array_object_name t_array_codes;
803 l_array_object_type t_array_codes;
804 l_array_pop_flag t_array_type_codes;
805 l_array_col_datatype t_array_codes;
806
807 l_array_ref_source_appl_id t_array_id;
808 l_array_ref_source_code t_array_codes;
809 l_array_ref_object_name t_array_codes;
810 l_array_ref_object_type t_array_codes;
811 l_array_ref_pop_flag t_array_type_codes;
812 l_array_ref_col_datatype t_array_codes;
813 l_array_ref_join_condition t_array_vl2000;
814 l_array_ref_linked_obj t_array_codes;
815
816 l_array_dt_source_appl_id t_array_id;
817 l_array_dt_source_code t_array_codes;
818 l_array_dt_object_name t_array_codes;
819 l_array_dt_object_type t_array_codes;
820 l_array_dt_pop_flag t_array_type_codes;
821 l_array_dt_col_datatype t_array_codes;
822
823 l_array_ref_dt_source_appl_id t_array_id;
824 l_array_ref_dt_source_code t_array_codes;
825 l_array_ref_dt_object_name t_array_codes;
826 l_array_ref_dt_object_type t_array_codes;
827 l_array_ref_dt_pop_flag t_array_type_codes;
828 l_array_ref_dt_col_datatype t_array_codes;
829 l_array_ref_dt_join_condition t_array_vl2000;
830
831 -- Cursor Declaration
832
833 -- Get all extract objects for the sources whose data type match
834 -- and the always populated flag is "Yes"
835 -- Added and commented for bug 15938563
836 /* CURSOR c_always_pop
837 IS
838 SELECT g.source_application_id, g.source_code,
839 o.object_name extract_object_name,
840 o.object_type_code extract_object_type,
841 o.always_populated_flag extract_object_pop_flag,
842 g.source_datatype_code column_datatype_code
843 FROM xla_evt_class_sources_gt g, xla_extract_objects o,
844 xla_extract_objects_gt og
845 WHERE g.application_id = o.application_id
846 AND g.entity_code = o.entity_code
847 AND g.event_class_code = o.event_class_code
848 AND g.source_level_code = o.object_type_code
849 AND g.source_application_id = o.application_id
850 AND og.object_name = o.object_name
851 AND EXISTS (
852 -- SELECT /*+ no_unnest */ --1 --added for bug13344804
853 /* FROM dba_tab_columns t
854 WHERE og.owner = t.owner
855 AND o.object_name = t.table_name
856 AND t.column_name = g.source_code
857 AND DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = G.SOURCE_DATATYPE_CODE
858 )
859 AND g.application_id = p_application_id
860 AND g.entity_code = p_entity_code
861 AND g.event_class_code = p_event_class_code
862 AND o.always_populated_flag = 'Y'
863 AND g.extract_object_name IS NULL; */
864
865 CURSOR c_always_pop
866 IS
867 SELECT g.source_application_id, g.source_code,
868 o.object_name extract_object_name,
869 o.object_type_code extract_object_type,
870 o.always_populated_flag extract_object_pop_flag,
871 g.source_datatype_code column_datatype_code
872 FROM xla_evt_class_sources_gt g, xla_extract_objects o,
873 xla_extract_objects_gt og
874 WHERE g.application_id = o.application_id
875 AND g.entity_code = o.entity_code
876 AND g.event_class_code = o.event_class_code
877 AND g.source_level_code = o.object_type_code
878 AND g.source_application_id = o.application_id
879 AND og.object_name = o.object_name
880 AND EXISTS (
881 SELECT /*+ no_unnest */ 1 --added for bug13344804
882 FROM dba_tab_columns t,user_objects uo
883 WHERE og.owner = t.owner
884 AND o.object_name = t.table_name
885 AND uo.object_name = o.object_name
886 AND uo.object_type <> 'SYNONYM'
887 AND t.column_name = g.source_code
888 AND DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = G.SOURCE_DATATYPE_CODE
889 UNION ALL
890 --SELECT /*+ no_unnest */ 1 --added for bug13344804
891 --FROM dba_tab_columns t,user_synonyms s,user_objects uo
892 --WHERE s.table_owner = t.owner
893 --AND s.table_name = t.table_name
894 --AND s.synonym_name =o.object_name
895 --AND uo.object_name = o.object_name
896 --AND uo.object_type = 'SYNONYM'
897 --AND t.column_name = g.source_code
898 --AND DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = G.SOURCE_DATATYPE_CODE
899 SELECT /*+ no_unnest */ 1
900 FROM USER_SYNONYMS S,USER_OBJECTS UO
901 WHERE 1=1
902 AND UO.OBJECT_NAME = O.OBJECT_NAME
903 AND UO.OBJECT_TYPE = 'SYNONYM'
904 AND S.SYNONYM_NAME = UO.OBJECT_NAME
905 AND exists (select /*+ no_unnest */ 'x'
906 from DBA_TAB_COLUMNS T
907 where S.TABLE_OWNER = T.OWNER
908 AND S.TABLE_NAME = T.TABLE_NAME
909 AND T.COLUMN_NAME = G.SOURCE_CODE
910 and DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = G.SOURCE_DATATYPE_CODE
911 )
912
913 )
914 AND g.application_id = p_application_id
915 AND g.entity_code = p_entity_code
916 AND g.event_class_code = p_event_class_code
917 AND o.always_populated_flag = 'Y'
918 AND g.extract_object_name IS NULL;
919
920
921
922 -- Get all reference objects for the sources whose data type match
923 -- and the always populated flag is "Yes"
924 -- Added and commented for bug 15938563
925 /*
926 CURSOR c_ref_always_pop
927 IS
928 SELECT g.source_application_id, g.source_code, r.reference_object_name extract_object_name,
929 o.object_type_code extract_object_type,
930 r.always_populated_flag extract_object_pop_flag,
931 g.source_datatype_code column_datatype_code,
932 r.join_condition, r.linked_to_ref_obj_name
933 FROM xla_evt_class_sources_gt g, xla_reference_objects r, xla_extract_objects o,
934 xla_reference_objects_gt og
935 WHERE g.application_id = r.application_id
936 AND g.entity_code = r.entity_code
937 AND g.event_class_code = r.event_class_code
938 AND g.source_application_id = r.reference_object_appl_id
939 AND g.source_level_code = o.object_type_code
940 AND r.application_id = o.application_id
941 AND r.entity_code = o.entity_code
942 AND r.event_class_code = o.event_class_code
943 AND r.object_name = o.object_name
944 AND og.reference_object_name = r.reference_object_name
945 AND EXISTS (
946 -- SELECT /*+ no_unnest */-- 1 --added for bug13344804
947 /* FROM dba_tab_columns t
948 WHERE og.owner = t.owner
949 AND r.reference_object_name = t.table_name
950 AND t.column_name = g.source_code
951 AND DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) = g.source_datatype_code
952 )
953 AND g.application_id = p_application_id
954 AND g.entity_code = p_entity_code
955 AND g.event_class_code = p_event_class_code
956 AND r.always_populated_flag = 'Y'
957 AND g.extract_object_name IS NULL;
958 */
959 CURSOR c_ref_always_pop
960 IS
961 SELECT g.source_application_id, g.source_code, r.reference_object_name extract_object_name,
962 o.object_type_code extract_object_type,
963 r.always_populated_flag extract_object_pop_flag,
964 g.source_datatype_code column_datatype_code,
965 r.join_condition, r.linked_to_ref_obj_name
966 FROM xla_evt_class_sources_gt g, xla_reference_objects r, xla_extract_objects o,
967 xla_reference_objects_gt og
968 WHERE g.application_id = r.application_id
969 AND g.entity_code = r.entity_code
970 AND g.event_class_code = r.event_class_code
971 AND g.source_application_id = r.reference_object_appl_id
972 AND g.source_level_code = o.object_type_code
973 AND r.application_id = o.application_id
974 AND r.entity_code = o.entity_code
975 AND r.event_class_code = o.event_class_code
976 AND r.object_name = o.object_name
977 AND og.reference_object_name = r.reference_object_name
978 AND EXISTS (
979 SELECT /*+ no_unnest */ 1 --added for bug13344804
980 FROM dba_tab_columns t,user_objects uo
981 WHERE og.owner = t.owner
982 AND r.reference_object_name = t.table_name
983 AND uo.object_name = r.reference_object_name
984 AND uo.object_type <> 'SYNONYM'
985 AND t.column_name = g.source_code
986 AND DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) = g.source_datatype_code
987 UNION ALL
988 --SELECT /*+ no_unnest */ 1 --added for bug13344804
989 --FROM dba_tab_columns t,user_synonyms s,user_objects uo
990 --WHERE s.table_owner = t.owner
991 --AND s.table_name = t.table_name
992 --AND s.synonym_name =r.reference_object_name
993 --AND uo.object_name = r.reference_object_name
994 --AND uo.object_type = 'SYNONYM'
995 --AND t.column_name = g.source_code
996 --AND DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) = g.source_datatype_code
997 SELECT /*+ no_unnest */ 1
998 FROM USER_SYNONYMS S,USER_OBJECTS UO
999 WHERE 1=1
1000 AND UO.OBJECT_NAME = r.reference_object_name
1001 AND UO.OBJECT_TYPE = 'SYNONYM'
1002 AND S.SYNONYM_NAME = UO.OBJECT_NAME
1003 AND exists (select /*+ no_unnest */ 'x'
1004 from DBA_TAB_COLUMNS T
1005 where S.TABLE_OWNER = T.OWNER
1006 AND S.TABLE_NAME = T.TABLE_NAME
1007 AND T.COLUMN_NAME = G.SOURCE_CODE
1008 and DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = G.SOURCE_DATATYPE_CODE
1009 )
1010
1011 )
1012 AND g.application_id = p_application_id
1013 AND g.entity_code = p_entity_code
1014 AND g.event_class_code = p_event_class_code
1015 AND r.always_populated_flag = 'Y'
1016 AND g.extract_object_name IS NULL;
1017
1018
1019 -- Get all extract objects for the sources whose data type match
1020 -- and the always populated flag is "No"
1021 -- Added and commented for bug 15938563
1022 /*
1023 CURSOR c_same_datatype
1024 IS
1025 SELECT g.source_application_id, g.source_code,
1026 o.object_name extract_object_name,
1027 o.object_type_code extract_object_type,
1028 o.always_populated_flag extract_object_pop_flag,
1029 g.source_datatype_code column_datatype_code
1030 FROM xla_evt_class_sources_gt g, xla_extract_objects o,
1031 xla_extract_objects_gt og
1032 WHERE g.application_id = o.application_id
1033 AND g.entity_code = o.entity_code
1034 AND g.event_class_code = o.event_class_code
1035 AND g.source_level_code = o.object_type_code
1036 AND g.source_application_id = o.application_id
1037 AND og.object_name = o.object_name
1038 AND EXISTS (
1039 -- SELECT /*+ no_unnest */-- 1 --added for bug13344804
1040 /* FROM dba_tab_columns t
1041 WHERE og.owner = t.owner
1042 AND o.object_name = t.table_name
1043 AND t.column_name = g.source_code
1044 AND DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = g.source_datatype_code
1045 )
1046 AND g.application_id = p_application_id
1047 AND g.entity_code = p_entity_code
1048 AND g.event_class_code = p_event_class_code
1049 AND g.extract_object_name IS NULL; */
1050 CURSOR c_same_datatype
1051 IS
1052 SELECT g.source_application_id, g.source_code,
1053 o.object_name extract_object_name,
1054 o.object_type_code extract_object_type,
1055 o.always_populated_flag extract_object_pop_flag,
1056 g.source_datatype_code column_datatype_code
1057 FROM xla_evt_class_sources_gt g, xla_extract_objects o,
1058 xla_extract_objects_gt og
1059 WHERE g.application_id = o.application_id
1060 AND g.entity_code = o.entity_code
1061 AND g.event_class_code = o.event_class_code
1062 AND g.source_level_code = o.object_type_code
1063 AND g.source_application_id = o.application_id
1064 AND og.object_name = o.object_name
1065 AND EXISTS (
1066 SELECT /*+ no_unnest */ 1 --added for bug13344804
1067 FROM dba_tab_columns t,user_objects uo
1068 WHERE og.owner = t.owner
1069 AND o.object_name = t.table_name
1070 AND uo.object_name = o.object_name
1071 AND uo.object_type <> 'SYNONYM'
1072 AND t.column_name = g.source_code
1073 AND DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = g.source_datatype_code
1074 UNION ALL
1075 --SELECT /*+ no_unnest */ 1 --added for bug13344804
1076 --FROM dba_tab_columns t,user_synonyms s,user_objects uo
1077 --WHERE s.table_owner = t.owner
1078 --AND s.table_name = t.table_name
1079 --AND s.synonym_name =o.object_name
1080 --AND uo.object_name = o.object_name
1081 --AND uo.object_type = 'SYNONYM'
1082 --AND t.column_name = g.source_code
1083 --AND DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = g.source_datatype_code
1084 SELECT /*+ no_unnest */ 1
1085 FROM USER_SYNONYMS S,USER_OBJECTS UO
1086 WHERE 1=1
1087 AND UO.OBJECT_NAME = o.object_name
1088 AND UO.OBJECT_TYPE = 'SYNONYM'
1089 AND S.SYNONYM_NAME = UO.OBJECT_NAME
1090 AND exists (select /*+ no_unnest */ 'x'
1091 from DBA_TAB_COLUMNS T
1092 where S.TABLE_OWNER = T.OWNER
1093 AND S.TABLE_NAME = T.TABLE_NAME
1094 AND T.COLUMN_NAME = G.SOURCE_CODE
1095 and DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = G.SOURCE_DATATYPE_CODE
1096 )
1097 )
1098 AND g.application_id = p_application_id
1099 AND g.entity_code = p_entity_code
1100 AND g.event_class_code = p_event_class_code
1101 AND g.extract_object_name IS NULL;
1102
1103
1104 -- Get all reference objects for the sources whose data type match
1105 -- and the always populated flag is "No"
1106 -- Added and commented for bug 15938563
1107 /*
1108 CURSOR c_ref_same_datatype
1109 IS
1110 SELECT g.source_application_id, g.source_code,
1111 r.reference_object_name extract_object_name,
1112 o.object_type_code extract_object_type,
1113 r.always_populated_flag extract_object_pop_flag,
1114 g.source_datatype_code column_datatype_code,
1115 r.join_condition, r.linked_to_ref_obj_name
1116 FROM xla_evt_class_sources_gt g, xla_reference_objects r, xla_extract_objects o,
1117 xla_reference_objects_gt og
1118 WHERE g.application_id = r.application_id
1119 AND g.entity_code = r.entity_code
1120 AND g.event_class_code = r.event_class_code
1121 AND g.source_application_id = r.reference_object_appl_id
1122 AND g.source_level_code = o.object_type_code
1123 AND r.application_id = o.application_id
1124 AND r.entity_code = o.entity_code
1125 AND r.event_class_code = o.event_class_code
1126 AND r.object_name = o.object_name
1127 AND og.reference_object_name = r.reference_object_name
1128 AND EXISTS (
1129 -- SELECT /*+ no_unnest */ --1 --added for bug13344804
1130 /* FROM dba_tab_columns t
1131 WHERE og.owner = t.owner
1132 AND r.reference_object_name = t.table_name
1133 AND t.column_name = g.source_code
1134 AND DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) = g.source_datatype_code
1135 )
1136 AND g.application_id = p_application_id
1137 AND g.entity_code = p_entity_code
1138 AND g.event_class_code = p_event_class_code
1139 AND g.extract_object_name IS NULL; */
1140 CURSOR c_ref_same_datatype
1141 IS
1142 SELECT g.source_application_id, g.source_code,
1143 r.reference_object_name extract_object_name,
1144 o.object_type_code extract_object_type,
1145 r.always_populated_flag extract_object_pop_flag,
1146 g.source_datatype_code column_datatype_code,
1147 r.join_condition, r.linked_to_ref_obj_name
1148 FROM xla_evt_class_sources_gt g, xla_reference_objects r, xla_extract_objects o,
1149 xla_reference_objects_gt og
1150 WHERE g.application_id = r.application_id
1151 AND g.entity_code = r.entity_code
1152 AND g.event_class_code = r.event_class_code
1153 AND g.source_application_id = r.reference_object_appl_id
1154 AND g.source_level_code = o.object_type_code
1155 AND r.application_id = o.application_id
1156 AND r.entity_code = o.entity_code
1157 AND r.event_class_code = o.event_class_code
1158 AND r.object_name = o.object_name
1159 AND og.reference_object_name = r.reference_object_name
1160 AND EXISTS (
1161 SELECT /*+ no_unnest */ 1 --added for bug13344804
1162 FROM dba_tab_columns t,user_objects uo
1163 WHERE og.owner = t.owner
1164 AND r.reference_object_name = t.table_name
1165 AND uo.object_name = r.reference_object_name
1166 AND uo.object_type <> 'SYNONYM'
1167 AND t.column_name = g.source_code
1168 AND DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) = g.source_datatype_code
1169 UNION ALL
1170 --SELECT /*+ no_unnest */ 1 --added for bug13344804
1171 --FROM dba_tab_columns t,user_synonyms s,user_objects uo
1172 --WHERE s.table_owner = t.owner
1173 --AND s.table_name = t.table_name
1174 --AND s.synonym_name =r.reference_object_name
1175 --AND uo.object_name = r.reference_object_name
1176 --AND uo.object_type = 'SYNONYM'
1177 --AND t.column_name = g.source_code
1178 --AND DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) = g.source_datatype_code
1179 SELECT /*+ no_unnest */ 1
1180 FROM USER_SYNONYMS S,USER_OBJECTS UO
1181 WHERE 1=1
1182 AND UO.OBJECT_NAME = r.reference_object_name
1183 AND UO.OBJECT_TYPE = 'SYNONYM'
1184 AND S.SYNONYM_NAME = UO.OBJECT_NAME
1185 AND exists (select /*+ no_unnest */ 'x'
1186 from DBA_TAB_COLUMNS T
1187 where S.TABLE_OWNER = T.OWNER
1188 AND S.TABLE_NAME = T.TABLE_NAME
1189 AND T.COLUMN_NAME = G.SOURCE_CODE
1190 and DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = G.SOURCE_DATATYPE_CODE
1191 )
1192
1193 )
1194 AND g.application_id = p_application_id
1195 AND g.entity_code = p_entity_code
1196 AND g.event_class_code = p_event_class_code
1197 AND g.extract_object_name IS NULL;
1198
1199
1200 -- Get remainder of extract objects for the sources whose data type do not match
1201 -- Added and commented for bug 15938563
1202 /*
1203 CURSOR c_diff_datatype
1204 IS
1205 SELECT DISTINCT
1206 g.source_application_id, g.source_code,
1207 o.object_name extract_object_name,
1208 o.object_type_code extract_object_type,
1209 o.always_populated_flag extract_object_pop_flag,
1210 -- 4713242 Performance Fix
1211 (SELECT DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) COLUMN_DATATYPE_CODE
1212 FROM dba_tab_columns T
1213 WHERE og.owner = t.owner
1214 AND o.object_name = t.table_name
1215 AND t.column_name = g.source_code)
1216 FROM xla_evt_class_sources_gt g, xla_extract_objects o,
1217 xla_extract_objects_gt og
1218 WHERE g.application_id = o.application_id
1219 AND g.entity_code = o.entity_code
1220 AND g.event_class_code = o.event_class_code
1221 AND g.source_level_code = o.object_type_code
1222 AND g.source_application_id = o.application_id
1223 AND og.object_name = o.object_name
1224 AND g.application_id = p_application_id
1225 AND g.entity_code = p_entity_code
1226 AND g.event_class_code = p_event_class_code
1227 AND g.extract_object_name IS NULL
1228 AND EXISTS (SELECT /*+ no_unnest */-- 1 --added for bug13344804
1229 /* FROM dba_tab_columns t
1230 WHERE og.owner = t.owner
1231 AND o.object_name = t.table_name
1232 AND t.column_name = g.source_code); */
1233
1234 CURSOR c_diff_datatype
1235 IS
1236 SELECT DISTINCT
1237 g.source_application_id, g.source_code,
1238 o.object_name extract_object_name,
1239 o.object_type_code extract_object_type,
1240 o.always_populated_flag extract_object_pop_flag,
1241 -- 4713242 Performance Fix
1242 (SELECT DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) COLUMN_DATATYPE_CODE
1243 FROM dba_tab_columns T,user_objects uo
1244 WHERE og.owner = t.owner
1245 AND uo.object_name = t.table_name
1246 AND uo.object_name = o.object_name
1247 AND uo.object_type <> 'SYNONYM'
1248 AND t.column_name = g.source_code
1249 UNION ALL
1250 SELECT DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) COLUMN_DATATYPE_CODE
1251 FROM dba_tab_columns T,user_objects uo,user_synonyms s
1252 WHERE t.table_name = s.table_name
1253 AND s.synonym_name = uo.object_name
1254 AND s.table_owner = t.owner
1255 AND uo.object_name = o.object_name
1256 AND uo.object_type = 'SYNONYM'
1257 AND t.column_name = g.source_code
1258 )
1259 FROM xla_evt_class_sources_gt g, xla_extract_objects o,
1260 xla_extract_objects_gt og
1261 WHERE g.application_id = o.application_id
1262 AND g.entity_code = o.entity_code
1263 AND g.event_class_code = o.event_class_code
1264 AND g.source_level_code = o.object_type_code
1265 AND g.source_application_id = o.application_id
1266 AND og.object_name = o.object_name
1267 AND g.application_id = p_application_id
1268 AND g.entity_code = p_entity_code
1269 AND g.event_class_code = p_event_class_code
1270 AND g.extract_object_name IS NULL
1271 AND EXISTS (SELECT /*+ no_unnest */ 1 --added for bug13344804
1272 FROM dba_tab_columns t,user_objects uo
1273 WHERE og.owner = t.owner
1274 AND o.object_name = t.table_name
1275 AND uo.object_name = o.object_name
1276 AND uo.object_type <> 'SYNONYM'
1277 AND t.column_name = g.source_code
1278 UNION ALL
1279 --SELECT /*+ no_unnest */ 1 --added for bug13344804
1280 --FROM dba_tab_columns t,user_synonyms s,user_objects uo
1281 --WHERE s.table_owner = t.owner
1282 --AND s.table_name = t.table_name
1283 --AND s.synonym_name =o.object_name
1284 --AND uo.object_name = o.object_name
1285 --AND uo.object_type = 'SYNONYM'
1286 --AND t.column_name = g.source_code
1287 SELECT /*+ no_unnest */ 1
1288 FROM USER_SYNONYMS S,USER_OBJECTS UO
1289 WHERE 1=1
1290 AND UO.OBJECT_NAME = o.object_name
1291 AND UO.OBJECT_TYPE = 'SYNONYM'
1292 AND S.SYNONYM_NAME = UO.OBJECT_NAME
1293 AND exists (select /*+ no_unnest */ 'x'
1294 from DBA_TAB_COLUMNS T
1295 where S.TABLE_OWNER = T.OWNER
1296 AND S.TABLE_NAME = T.TABLE_NAME
1297 AND T.COLUMN_NAME = G.SOURCE_CODE
1298 )
1299 );
1300
1301 -- Get remainder of reference objects for the sources whose data type do not match
1302 -- Added and commented for bug 15938563
1303 /*
1304 CURSOR c_ref_diff_datatype
1305 IS
1306 SELECT DISTINCT g.source_application_id
1307 ,g.source_code
1308 ,r.reference_object_name extract_object_name
1309 ,o.object_type_code extract_object_type
1310 ,o.always_populated_flag extract_object_pop_flag
1311 -- 4713242 Performance Fix
1312 ,(SELECT DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) COLUMN_DATATYPE_CODE
1313 FROM dba_tab_columns T
1314 WHERE og.owner = t.owner
1315 AND r.reference_object_name = t.table_name
1316 AND t.column_name = g.source_code)
1317 ,r.join_condition
1318 FROM xla_evt_class_sources_gt g
1319 ,xla_reference_objects r
1320 ,xla_extract_objects o
1321 ,xla_reference_objects_gt og
1322 WHERE g.application_id = r.application_id
1323 AND g.entity_code = r.entity_code
1324 AND g.event_class_code = r.event_class_code
1325 AND g.source_level_code = o.object_type_code
1326 AND r.application_id = o.application_id
1327 AND r.entity_code = o.entity_code
1328 AND r.event_class_code = o.event_class_code
1329 AND r.object_name = o.object_name
1330 AND og.reference_object_name = r.reference_object_name
1331 AND g.application_id = p_application_id
1332 AND g.entity_code = p_entity_code
1333 AND g.event_class_code = p_event_class_code
1334 AND g.extract_object_name IS NULL
1335 AND EXISTS (SELECT /*+ no_unnest */-- 1 --added for bug13344804
1336 /* FROM dba_tab_columns t
1337 WHERE og.owner = t.owner
1338 AND r.reference_object_name = t.table_name
1339 AND t.column_name = g.source_code); */
1340 CURSOR c_ref_diff_datatype
1341 IS
1342 SELECT DISTINCT g.source_application_id
1343 ,g.source_code
1344 ,r.reference_object_name extract_object_name
1345 ,o.object_type_code extract_object_type
1346 ,o.always_populated_flag extract_object_pop_flag
1347 -- 4713242 Performance Fix
1348 ,(SELECT DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) COLUMN_DATATYPE_CODE
1349 FROM dba_tab_columns T,user_objects uo
1350 WHERE og.owner = t.owner
1351 AND r.reference_object_name = t.table_name
1352 AND uo.object_name = r.reference_object_name
1353 AND uo.object_type <> 'SYNONYM'
1354 AND t.column_name = g.source_code
1355 UNION ALL
1356 SELECT DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) COLUMN_DATATYPE_CODE
1357 FROM dba_tab_columns T,user_objects uo,user_synonyms s
1358 WHERE s.table_owner = t.owner
1359 AND s.table_name = t.table_name
1360 AND s.synonym_name = uo.object_name
1361 AND uo.object_name = r.reference_object_name
1362 AND uo.object_type = 'SYNONYM'
1363 AND t.column_name = g.source_code)
1364 ,r.join_condition
1365 FROM xla_evt_class_sources_gt g
1366 ,xla_reference_objects r
1367 ,xla_extract_objects o
1368 ,xla_reference_objects_gt og
1369 WHERE g.application_id = r.application_id
1370 AND g.entity_code = r.entity_code
1371 AND g.event_class_code = r.event_class_code
1372 AND g.source_level_code = o.object_type_code
1373 AND r.application_id = o.application_id
1374 AND r.entity_code = o.entity_code
1375 AND r.event_class_code = o.event_class_code
1376 AND r.object_name = o.object_name
1377 AND og.reference_object_name = r.reference_object_name
1378 AND g.application_id = p_application_id
1379 AND g.entity_code = p_entity_code
1380 AND g.event_class_code = p_event_class_code
1381 AND g.extract_object_name IS NULL
1382 AND EXISTS (SELECT /*+ no_unnest */ 1 --added for bug13344804
1383 FROM dba_tab_columns t,user_objects uo
1384 WHERE og.owner = t.owner
1385 AND r.reference_object_name = t.table_name
1386 AND uo.object_name = r.reference_object_name
1387 AND uo.object_type <> 'SYNONYM'
1388 AND t.column_name = g.source_code
1389 UNION ALL
1390 --SELECT /*+ no_unnest */ 1 --added for bug13344804
1391 --FROM dba_tab_columns t,user_synonyms s,user_objects uo
1392 --WHERE s.table_owner = t.owner
1393 --AND s.table_name = t.table_name
1394 --AND s.synonym_name =r.reference_object_name
1395 --AND uo.object_name = r.reference_object_name
1396 --AND uo.object_type = 'SYNONYM'
1397 --AND t.column_name = g.source_code
1398 SELECT /*+ no_unnest */ 1
1399 FROM USER_SYNONYMS S,USER_OBJECTS UO
1400 WHERE 1=1
1401 AND UO.OBJECT_NAME = r.reference_object_name
1402 AND UO.OBJECT_TYPE = 'SYNONYM'
1403 AND S.SYNONYM_NAME = UO.OBJECT_NAME
1404 AND exists (select /*+ no_unnest */ 'x'
1405 from DBA_TAB_COLUMNS T
1406 where S.TABLE_OWNER = T.OWNER
1407 AND S.TABLE_NAME = T.TABLE_NAME
1408 AND T.COLUMN_NAME = G.SOURCE_CODE
1409 )
1410 );
1411
1412
1413 -- Get all sources from GT table with null extract object
1414 CURSOR c_null_obj
1415 IS
1416 SELECT source_application_id, source_code, source_level_code
1417 FROM xla_evt_class_sources_gt g
1418 WHERE g.application_id = p_application_id
1419 AND g.entity_code = p_entity_code
1420 AND g.event_class_code = p_event_class_code
1421 AND extract_object_name IS NULL;
1422
1423 l_null_obj c_null_obj%rowtype;
1424
1425 -- Get all sources from GT table whose datatype does not match with column datatype
1426 CURSOR c_datatype
1427 IS
1428 SELECT source_application_id, source_code, extract_object_name,
1429 extract_object_type_code
1430 FROM xla_evt_class_sources_gt g
1431 WHERE source_datatype_code <> column_datatype_code
1432 AND extract_object_name IS NOT NULL
1433 AND g.application_id = p_application_id
1434 AND g.entity_code = p_entity_code
1435 AND g.event_class_code = p_event_class_code;
1436
1437 l_datatype c_datatype%rowtype;
1438
1439 BEGIN
1440
1441 l_application_id := p_application_id;
1442 l_entity_code := p_entity_code;
1443 l_event_class_code := p_event_class_code;
1444 l_amb_context_code := p_amb_context_code;
1445 l_product_rule_code := p_product_rule_code;
1446 l_product_rule_type_code := p_product_rule_type_code;
1447
1448 g_trace_label :='Validate_sources_with_extract';
1449
1450 IF (g_log_level is NULL) THEN
1451 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1452 END IF;
1453
1454 IF (g_log_level is NULL) THEN
1455 g_log_enabled := fnd_log.test
1456 (log_level => g_log_level
1457 ,module => C_DEFAULT_MODULE);
1458 END IF;
1459
1460 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
1461 trace
1462 (p_msg => 'Begin'
1463 ,p_level => C_LEVEL_PROCEDURE);
1464 trace
1465 (p_msg => 'p_application_id = ' ||TO_CHAR(p_application_id)
1466 ,p_level => C_LEVEL_PROCEDURE);
1467 trace
1468 (p_msg => 'p_entity_code = '||p_entity_code
1469 ,p_level => C_LEVEL_PROCEDURE);
1470 trace
1471 (p_msg => 'p_event_class_code = ' ||p_event_class_code
1472 ,p_level => C_LEVEL_PROCEDURE);
1473 trace
1474 (p_msg => 'p_amb_context_code = '||p_amb_context_code
1475 ,p_level => C_LEVEL_PROCEDURE);
1476 trace
1477 (p_msg => 'p_product_rule_type_code = ' ||p_product_rule_type_code
1478 ,p_level => C_LEVEL_PROCEDURE);
1479 trace
1480 (p_msg => 'p_product_rule_code = ' ||p_product_rule_code
1481 ,p_level => C_LEVEL_PROCEDURE);
1482 END IF;
1483
1484 -- Get all extract objects which are valid with the source definition
1485 -- and the data type of source matches the column data type
1486 -- the extract object is always populated
1487
1488 OPEN c_always_pop;
1489 FETCH c_always_pop
1490 BULK COLLECT INTO l_array_pop_source_appl_id, l_array_pop_source_code,
1491 l_array_pop_object_name,l_array_pop_object_type,
1492 l_array_pop_pop_flag, l_array_pop_col_datatype;
1493
1494 -- Bulk update the GT table with the extract object name for each source
1495 IF l_array_pop_source_code.COUNT > 0 THEN
1496 FORALL i IN l_array_pop_source_code.FIRST..l_array_pop_source_code.LAST
1497 UPDATE xla_evt_class_sources_gt gt
1498 SET gt.extract_object_name = l_array_pop_object_name(i),
1499 gt.extract_object_type_code = l_array_pop_object_type(i),
1500 gt.always_populated_flag = l_array_pop_pop_flag(i),
1501 gt.column_datatype_code = l_array_pop_col_datatype(i),
1502 gt.reference_object_flag = C_REF_OBJECT_FLAG_N
1503 WHERE gt.source_application_id = l_array_pop_source_appl_id(i)
1504 AND gt.source_code = l_array_pop_source_code(i)
1505 AND gt.application_id = p_application_id
1506 AND gt.entity_code = p_entity_code
1507 AND gt.event_class_code = p_event_class_code;
1508 END IF;
1509 CLOSE c_always_pop;
1510
1511 OPEN c_ref_always_pop;
1512 FETCH c_ref_always_pop
1513 BULK COLLECT INTO l_array_ref_pop_source_appl_id,
1514 l_array_ref_pop_source_code, l_array_ref_pop_object_name,
1515 l_array_ref_pop_object_type, l_array_ref_pop_pop_flag,
1516 l_array_ref_pop_col_datatype, l_array_ref_pop_join_condition
1517 ,l_array_ref_pop_linked_obj;
1518
1519 -- Bulk update the GT table with the reference object name for each source
1520 IF l_array_ref_pop_source_code.COUNT > 0 THEN
1521 FORALL i IN l_array_ref_pop_source_code.FIRST..l_array_ref_pop_source_code.LAST
1522 UPDATE xla_evt_class_sources_gt gt
1523 SET gt.extract_object_name = l_array_ref_pop_object_name(i),
1524 gt.extract_object_type_code = l_array_ref_pop_object_type(i),
1525 gt.always_populated_flag = l_array_ref_pop_pop_flag(i),
1526 gt.column_datatype_code = l_array_ref_pop_col_datatype(i),
1527 gt.reference_object_flag = C_REF_OBJECT_FLAG_Y,
1528 gt.join_condition = l_array_ref_pop_join_condition(i)
1529 WHERE gt.source_application_id = l_array_ref_pop_source_appl_id(i)
1530 AND gt.source_code = l_array_ref_pop_source_code(i)
1531 AND gt.application_id = p_application_id
1532 AND gt.entity_code = p_entity_code
1533 AND gt.event_class_code = p_event_class_code
1534 AND l_array_ref_pop_linked_obj(i) IS NULL;
1535
1536 FORALL i IN l_array_ref_pop_source_code.FIRST..l_array_ref_pop_source_code.LAST
1537 UPDATE xla_evt_class_sources_gt gt
1538 SET gt.extract_object_name = l_array_ref_pop_object_name(i),
1539 gt.extract_object_type_code = l_array_ref_pop_object_type(i),
1540 gt.always_populated_flag = l_array_ref_pop_pop_flag(i),
1541 gt.column_datatype_code = l_array_ref_pop_col_datatype(i),
1542 gt.reference_object_flag = C_REF_OBJECT_FLAG_Y,
1543 gt.join_condition = l_array_ref_pop_join_condition(i)
1544 WHERE gt.source_application_id = l_array_ref_pop_source_appl_id(i)
1545 AND gt.source_code = l_array_ref_pop_source_code(i)
1546 AND gt.application_id = p_application_id
1547 AND gt.entity_code = p_entity_code
1548 AND gt.event_class_code = p_event_class_code
1549 AND gt.extract_object_name IS NULL
1550 AND l_array_ref_pop_linked_obj(i) IS NOT NULL;
1551 END IF;
1552 CLOSE c_ref_always_pop;
1553
1554
1555 -- Get all extract objects which are valid with the source definition
1556 -- and the data type of source matches the column data type
1557 -- and the extract object is not always populated
1558 OPEN c_same_datatype;
1559 FETCH c_same_datatype
1560 BULK COLLECT INTO l_array_source_appl_id, l_array_source_code,
1561 l_array_object_name, l_array_object_type,
1562 l_array_pop_flag, l_array_col_datatype;
1563
1564 -- Bulk update the GT table with the extract object name for each source
1565 IF l_array_source_code.COUNT > 0 THEN
1566 FORALL i IN l_array_source_code.FIRST..l_array_source_code.LAST
1567 UPDATE xla_evt_class_sources_gt gt
1568 SET gt.extract_object_name = l_array_object_name(i),
1569 gt.extract_object_type_code = l_array_object_type(i),
1570 gt.always_populated_flag = l_array_pop_flag(i),
1571 gt.column_datatype_code = l_array_col_datatype(i),
1572 gt.reference_object_flag = C_REF_OBJECT_FLAG_N
1573 WHERE gt.source_application_id = l_array_source_appl_id(i)
1574 AND gt.source_code = l_array_source_code(i)
1575 AND gt.application_id = p_application_id
1576 AND gt.entity_code = p_entity_code
1577 AND gt.event_class_code = p_event_class_code;
1578 END IF;
1579 CLOSE c_same_datatype;
1580
1581 -- Get all reference objects which are valid with the source definition
1582 -- and the data type of source matches the column data type
1583 -- and the extract object is not always populated
1584 OPEN c_ref_same_datatype;
1585 FETCH c_ref_same_datatype
1586 BULK COLLECT INTO l_array_ref_source_appl_id,
1587 l_array_ref_source_code, l_array_ref_object_name,
1588 l_array_ref_object_type, l_array_ref_pop_flag,
1589 l_array_ref_col_datatype, l_array_ref_join_condition,
1590 l_array_ref_linked_obj;
1591
1592 -- Bulk update the GT table with the reference object name for each source
1593 IF l_array_ref_source_code.COUNT > 0 THEN
1594 FORALL i IN l_array_ref_source_code.FIRST..l_array_ref_source_code.LAST
1595 UPDATE xla_evt_class_sources_gt gt
1596 SET gt.extract_object_name = l_array_ref_object_name(i),
1597 gt.extract_object_type_code = l_array_ref_object_type(i),
1598 gt.always_populated_flag = l_array_ref_pop_flag(i),
1599 gt.column_datatype_code = l_array_ref_col_datatype(i),
1600 gt.reference_object_flag = C_REF_OBJECT_FLAG_Y,
1601 gt.join_condition = l_array_ref_join_condition(i)
1602 WHERE gt.source_application_id = l_array_ref_source_appl_id(i)
1603 AND gt.source_code = l_array_ref_source_code(i)
1604 AND gt.application_id = p_application_id
1605 AND gt.entity_code = p_entity_code
1606 AND gt.event_class_code = p_event_class_code
1607 AND l_array_ref_linked_obj(i) IS NULL;
1608 FORALL i IN l_array_ref_source_code.FIRST..l_array_ref_source_code.LAST
1609 UPDATE xla_evt_class_sources_gt gt
1610 SET gt.extract_object_name = l_array_ref_object_name(i),
1611 gt.extract_object_type_code = l_array_ref_object_type(i),
1612 gt.always_populated_flag = l_array_ref_pop_flag(i),
1613 gt.column_datatype_code = l_array_ref_col_datatype(i),
1614 gt.reference_object_flag = C_REF_OBJECT_FLAG_Y,
1615 gt.join_condition = l_array_ref_join_condition(i)
1616 WHERE gt.source_application_id = l_array_ref_source_appl_id(i)
1617 AND gt.source_code = l_array_ref_source_code(i)
1618 AND gt.application_id = p_application_id
1619 AND gt.entity_code = p_entity_code
1620 AND gt.event_class_code = p_event_class_code
1621 AND gt.extract_object_name IS NULL
1622 AND l_array_ref_linked_obj(i) IS NOT NULL;
1623 END IF;
1624 CLOSE c_ref_same_datatype;
1625
1626
1627 -- Get all extract objects which are valid with the source definition
1628 -- but the data type of source may not match the column data type
1629
1630 OPEN c_diff_datatype;
1631 FETCH c_diff_datatype
1632 BULK COLLECT INTO l_array_dt_source_appl_id, l_array_dt_source_code,
1633 l_array_dt_object_name, l_array_dt_object_type,
1634 l_array_dt_pop_flag, l_array_dt_col_datatype;
1635
1636 -- Bulk update the GT table with the extract object name for each source
1637 IF l_array_dt_source_code.COUNT > 0 THEN
1638 FORALL j IN l_array_dt_source_code.FIRST..l_array_dt_source_code.LAST
1639 UPDATE xla_evt_class_sources_gt gt
1640 SET gt.extract_object_name = l_array_dt_object_name(j),
1641 gt.extract_object_type_code = l_array_dt_object_type(j),
1642 gt.always_populated_flag = l_array_dt_pop_flag(j),
1643 gt.column_datatype_code = l_array_dt_col_datatype(j),
1644 gt.reference_object_flag = C_REF_OBJECT_FLAG_N
1645 WHERE gt.source_application_id = l_array_dt_source_appl_id(j)
1646 AND gt.source_code = l_array_dt_source_code(j)
1647 AND gt.application_id = p_application_id
1648 AND gt.entity_code = p_entity_code
1649 AND gt.event_class_code = p_event_class_code;
1650 END IF;
1651 CLOSE c_diff_datatype;
1652
1653 -- Get all reference objects which are valid with the source definition
1654 -- but the data type of source may not match the column data type
1655
1656 OPEN c_ref_diff_datatype;
1657 FETCH c_ref_diff_datatype
1658 BULK COLLECT INTO l_array_ref_dt_source_appl_id, l_array_ref_dt_source_code,
1659 l_array_ref_dt_object_name, l_array_ref_dt_object_type,
1660 l_array_ref_dt_pop_flag, l_array_ref_dt_col_datatype,
1661 l_array_ref_dt_join_condition;
1662
1663 -- Bulk update the GT table with the reference object name for each source
1664 IF l_array_ref_dt_source_code.COUNT > 0 THEN
1665 FORALL j IN l_array_ref_dt_source_code.FIRST..l_array_ref_dt_source_code.LAST
1666 UPDATE xla_evt_class_sources_gt gt
1667 SET gt.extract_object_name = l_array_ref_dt_object_name(j),
1668 gt.extract_object_type_code = l_array_ref_dt_object_type(j),
1669 gt.always_populated_flag = l_array_ref_dt_pop_flag(j),
1670 gt.column_datatype_code = l_array_ref_dt_col_datatype(j),
1671 gt.reference_object_flag = C_REF_OBJECT_FLAG_Y,
1672 gt.join_condition = l_array_ref_dt_join_condition(j)
1673 WHERE gt.source_application_id = l_array_ref_dt_source_appl_id(j)
1674 AND gt.source_code = l_array_ref_dt_source_code(j)
1675 AND gt.application_id = p_application_id
1676 AND gt.entity_code = p_entity_code
1677 AND gt.event_class_code = p_event_class_code;
1678 END IF;
1679 CLOSE c_ref_diff_datatype;
1680
1681
1682 -- Error all sources that do not exist in the right extract object
1683 OPEN c_null_obj;
1684 LOOP
1685 FETCH c_null_obj
1686 INTO l_null_obj;
1687 EXIT WHEN c_null_obj%notfound;
1688 Xla_amb_setup_err_pkg.stack_error
1689 (p_message_name => 'XLA_AB_SRC_NOT_DEFINED_IN_EXT'
1690 ,p_message_type => 'E'
1691 ,p_message_category => 'EXTRACT_SOURCE'
1692 ,p_category_sequence => 4
1693 ,p_application_id => l_application_id
1694 ,p_entity_code => l_entity_code
1695 ,p_event_class_code => l_event_class_code
1696 ,p_amb_context_code => l_amb_context_code
1697 ,p_product_rule_type_code => l_product_rule_type_code
1698 ,p_product_rule_code => l_product_rule_code
1699 ,p_source_application_id => l_null_obj.source_application_id
1700 ,p_source_code => l_null_obj.source_code
1701 ,p_source_type_code => 'S'
1702 ,p_extract_object_type => l_null_obj.source_level_code);
1703
1704 l_return := FALSE;
1705 END LOOP;
1706 CLOSE c_null_obj;
1707
1708 -- Error all sources that do not match the corresponding column datatype
1709 OPEN c_datatype;
1710 LOOP
1711 FETCH c_datatype
1712 INTO l_datatype;
1713 EXIT WHEN c_datatype%notfound;
1714 Xla_amb_setup_err_pkg.stack_error
1715 (p_message_name => 'XLA_AB_SRC_DATATYPE_NOT_MATCH'
1716 ,p_message_type => 'E'
1717 ,p_message_category => 'EXTRACT_SOURCE'
1718 ,p_category_sequence => 4
1719 ,p_application_id => l_application_id
1720 ,p_entity_code => l_entity_code
1721 ,p_event_class_code => l_event_class_code
1722 ,p_amb_context_code => l_amb_context_code
1723 ,p_product_rule_type_code => l_product_rule_type_code
1724 ,p_product_rule_code => l_product_rule_code
1725 ,p_source_application_id => l_datatype.source_application_id
1726 ,p_source_code => l_datatype.source_code
1727 ,p_source_type_code => 'S'
1728 ,p_extract_object_name => l_datatype.extract_object_name
1729 ,p_extract_object_type => l_datatype.extract_object_type_code);
1730
1731 l_return := FALSE;
1732 END LOOP;
1733 CLOSE c_datatype;
1734
1735 -- Check primary keys for the extract objects when called from an AAD
1736
1737 IF p_product_rule_code IS NOT NULL THEN
1738 IF NOT Chk_primary_keys_exist
1739 (p_application_id => l_application_id
1740 ,p_entity_code => l_entity_code
1741 ,p_event_class_code => l_event_class_code
1742 ,p_amb_context_code => l_amb_context_code
1743 ,p_product_rule_type_code => l_product_rule_type_code
1744 ,p_product_rule_code => l_product_rule_code) THEN
1745
1746 l_return := FALSE;
1747
1748 END IF;
1749 END IF;
1750
1751 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
1752 trace
1753 (p_msg => 'End'
1754 ,p_level => C_LEVEL_PROCEDURE);
1755 END IF;
1756
1757 RETURN l_return;
1758
1759 EXCEPTION
1760 WHEN xla_exceptions_pkg.application_exception THEN
1761 RAISE;
1762 WHEN OTHERS THEN
1763 xla_exceptions_pkg.raise_message
1764 (p_location => 'xla_extract_integrity_pkg.validate_sources_with_extract');
1765 END validate_sources_with_extract; -- end of function
1766
1767 /*======================================================================+
1768 | |
1769 | Public Procedure |
1770 | |
1771 | Set_extract_object_owner |
1772 | |
1773 | This routine gets the owner for the extract object and stores it in |
1774 | a gt table |
1775 | |
1776 +======================================================================*/
1777
1778
1779 PROCEDURE Set_extract_object_owner
1780 (p_application_id IN NUMBER
1781 ,p_amb_context_code IN VARCHAR2
1782 ,p_product_rule_type_code IN VARCHAR2
1783 ,p_product_rule_code IN VARCHAR2
1784 ,p_entity_code IN VARCHAR2
1785 ,p_event_class_code IN VARCHAR2)
1786 IS
1787
1788 l_user VARCHAR2(30);
1789 l_object_name VARCHAR2(30);
1790 l_object_type VARCHAR2(30);
1791 l_syn_owner VARCHAR2(30);
1792 l_ref_object_flag VARCHAR2(1);
1793
1794 l_application_id NUMBER(15);
1795 l_entity_code VARCHAR2(30);
1796 l_event_class_code VARCHAR2(30);
1797 l_amb_context_code VARCHAR2(30);
1798 l_product_rule_code VARCHAR2(30);
1799 l_product_rule_type_code VARCHAR2(1);
1800
1801 CURSOR c_aad_objects
1802 IS
1803 SELECT distinct ext.object_name, C_REF_OBJECT_FLAG_N reference_object_flag
1804 FROM xla_extract_objects ext, xla_prod_acct_headers hdr
1805 WHERE ext.application_id = hdr.application_id
1806 AND ext.entity_code = hdr.entity_code
1807 AND ext.event_class_code = hdr.event_class_code
1808 AND hdr.application_id = p_application_id
1809 AND hdr.amb_context_code = p_amb_context_code
1810 AND hdr.product_rule_type_code = p_product_rule_type_code
1811 AND hdr.product_rule_code = p_product_rule_code
1812 UNION ALL
1813 SELECT distinct rfr.reference_object_name, C_REF_OBJECT_FLAG_Y reference_object_flag
1814 FROM xla_reference_objects rfr, xla_prod_acct_headers hdr
1815 WHERE rfr.application_id = hdr.application_id
1816 AND rfr.entity_code = hdr.entity_code
1817 AND rfr.event_class_code = hdr.event_class_code
1818 AND hdr.application_id = p_application_id
1819 AND hdr.amb_context_code = p_amb_context_code
1820 AND hdr.product_rule_type_code = p_product_rule_type_code
1821 AND hdr.product_rule_code = p_product_rule_code;
1822
1823 CURSOR c_object_type
1824 IS
1825 SELECT usr.object_type
1826 FROM user_objects usr
1827 WHERE usr.object_name = l_object_name;
1828
1829 CURSOR c_syn_owner
1830 IS
1831 SELECT syn.table_owner
1832 FROM user_synonyms syn
1833 WHERE syn.synonym_name = l_object_name;
1834
1835 BEGIN
1836
1837 g_trace_label :='Set_extract_object_owner';
1838
1839 IF (g_log_level is NULL) THEN
1840 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1841 END IF;
1842
1843 IF (g_log_level is NULL) THEN
1844 g_log_enabled := fnd_log.test
1845 (log_level => g_log_level
1846 ,module => C_DEFAULT_MODULE);
1847 END IF;
1848
1849 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
1850 trace
1851 (p_msg => 'Begin'
1852 ,p_level => C_LEVEL_PROCEDURE);
1853 trace
1854 (p_msg => 'p_application_id = ' ||TO_CHAR(p_application_id)
1855 ,p_level => C_LEVEL_PROCEDURE);
1856 trace
1857 (p_msg => 'p_entity_code = '||p_entity_code
1858 ,p_level => C_LEVEL_PROCEDURE);
1859 trace
1860 (p_msg => 'p_event_class_code = ' ||p_event_class_code
1861 ,p_level => C_LEVEL_PROCEDURE);
1862 trace
1863 (p_msg => 'p_amb_context_code = '||p_amb_context_code
1864 ,p_level => C_LEVEL_PROCEDURE);
1865 trace
1866 (p_msg => 'p_product_rule_type_code = ' ||p_product_rule_type_code
1867 ,p_level => C_LEVEL_PROCEDURE);
1868 trace
1869 (p_msg => 'p_product_rule_code = ' ||p_product_rule_code
1870 ,p_level => C_LEVEL_PROCEDURE);
1871 END IF;
1872
1873 l_application_id := p_application_id;
1874 l_entity_code := p_entity_code;
1875 l_event_class_code := p_event_class_code;
1876 l_amb_context_code := p_amb_context_code;
1877 l_product_rule_code := p_product_rule_code;
1878 l_product_rule_type_code := p_product_rule_type_code;
1879
1880 DELETE FROM xla_extract_objects_gt;
1881 DELETE FROM xla_reference_objects_gt;
1882
1883 -- Get owner for current schema
1884 SELECT user
1885 INTO l_user
1886 FROM DUAL;
1887
1888 IF p_product_rule_code is NULL THEN
1889
1890 -- Insert objects for an event class and current owner in GT table
1891 INSERT
1892 INTO xla_extract_objects_gt
1893 (object_name
1894 ,owner)
1895 SELECT ext.object_name, l_user
1896 FROM xla_extract_objects ext
1897 WHERE EXISTS (SELECT /*+ no_unnest */ 'c'
1898 FROM user_objects usr
1899 WHERE ext.object_name = usr.object_name
1900 AND usr.object_type <> 'SYNONYM' )
1901 AND ext.application_id = p_application_id
1902 AND entity_code = p_entity_code
1903 AND event_class_code = p_event_class_code;
1904
1905 -- Insert reference objects for an event class and current owner in GT table
1906 -- Assume duplicate objects are not used for an event class
1907 INSERT
1908 INTO xla_reference_objects_gt
1909 (reference_object_name
1910 ,owner)
1911 SELECT rfr.reference_object_name, l_user
1912 FROM xla_reference_objects rfr
1913 WHERE
1914 EXISTS (SELECT /*+ no_unnest */ 'c'
1915 FROM user_objects usr
1916 WHERE rfr.reference_object_name = usr.object_name
1917 AND usr.object_type <> 'SYNONYM' )
1918 AND rfr.application_id = p_application_id
1919 AND rfr.entity_code = p_entity_code
1920 AND rfr.event_class_code = p_event_class_code;
1921
1922 -- Insert objects for an event class and different owner in GT table
1923 INSERT
1924 INTO xla_extract_objects_gt
1925 (object_name
1926 ,owner)
1927 SELECT ext.object_name
1928 ,(SELECT syn.table_owner
1929 FROM user_objects usr
1930 ,user_synonyms syn
1931 WHERE ext.object_name = usr.object_name
1932 AND ext.object_name = syn.synonym_name
1933 AND usr.object_type = 'SYNONYM')
1934 FROM xla_extract_objects ext
1935 WHERE EXISTS (SELECT /*+ no_unnest */ 'c'
1936 FROM user_objects usr
1937 ,user_synonyms syn
1938 WHERE ext.object_name = usr.object_name
1939 AND ext.object_name = syn.synonym_name
1940 AND usr.object_type = 'SYNONYM')
1941 AND ext.application_id = p_application_id
1942 AND entity_code = p_entity_code
1943 AND event_class_code = p_event_class_code;
1944
1945 -- Insert objects for an event class and different owner in GT table
1946 -- Assume duplicate objects are not used for an event class
1947 INSERT
1948 INTO xla_reference_objects_gt
1949 (reference_object_name
1950 ,owner)
1951 SELECT rfr.reference_object_name
1952 ,(SELECT syn.table_owner
1953 FROM user_objects usr
1954 ,user_synonyms syn
1955 -- change rfr.object_name to rfr.reference_object_name, as told by dimple
1956 WHERE rfr.reference_object_name = usr.object_name
1957 AND rfr.reference_object_name = syn.synonym_name
1958 AND usr.object_type = 'SYNONYM')
1959 FROM xla_reference_objects rfr
1960 WHERE EXISTS (SELECT /*+ no_unnest */ 'c'
1961 FROM user_objects usr
1962 ,user_synonyms syn
1963 -- change rfr.object_name to rfr.reference_object_name, as told by dimple
1964 WHERE rfr.reference_object_name = usr.object_name
1965 AND rfr.reference_object_name = syn.synonym_name
1966 AND usr.object_type = 'SYNONYM')
1967 AND rfr.application_id = p_application_id
1968 AND rfr.entity_code = p_entity_code
1969 AND rfr.event_class_code = p_event_class_code;
1970
1971 ELSE
1972
1973 -- Insert objects for an AAD and owner in GT table
1974 OPEN c_aad_objects;
1975 LOOP
1976 FETCH c_aad_objects
1977 INTO l_object_name, l_ref_object_flag;
1978 EXIT WHEN c_aad_objects%notfound;
1979
1980 OPEN c_object_type;
1981 FETCH c_object_type
1982 INTO l_object_type;
1983
1984 IF l_object_type <> 'SYNONYM' THEN
1985 IF l_ref_object_flag = 'N' THEN
1986
1987 BEGIN
1988 INSERT
1989 INTO xla_extract_objects_gt
1990 (object_name
1991 ,owner)
1992 VALUES(l_object_name
1993 ,l_user);
1994 EXCEPTION
1995 WHEN OTHERS THEN
1996 Xla_amb_setup_err_pkg.stack_error
1997 (p_message_name => 'XLA_AB_EXT_OBJECT_ERROR'
1998 ,p_message_type => 'E'
1999 ,p_message_category => 'EXTRACT_OBJECT'
2000 ,p_category_sequence => 3
2001 ,p_application_id => l_application_id
2002 ,p_entity_code => l_entity_code
2003 ,p_event_class_code => l_event_class_code
2004 ,p_extract_object_name => l_object_name
2005 ,p_amb_context_code => l_amb_context_code
2006 ,p_product_rule_type_code => l_product_rule_type_code
2007 ,p_product_rule_code => l_product_rule_code);
2008
2009 END;
2010
2011 ELSE
2012 BEGIN
2013 INSERT
2014 INTO xla_reference_objects_gt
2015 (reference_object_name
2016 ,owner)
2017 VALUES(l_object_name
2018 ,l_user);
2019 EXCEPTION
2020 WHEN OTHERS THEN
2021 Xla_amb_setup_err_pkg.stack_error
2022 (p_message_name => 'XLA_AB_EXT_OBJECT_ERROR'
2023 ,p_message_type => 'E'
2024 ,p_message_category => 'EXTRACT_OBJECT'
2025 ,p_category_sequence => 3
2026 ,p_application_id => l_application_id
2027 ,p_entity_code => l_entity_code
2028 ,p_event_class_code => l_event_class_code
2029 ,p_extract_object_name => l_object_name
2030 ,p_amb_context_code => l_amb_context_code
2031 ,p_product_rule_type_code => l_product_rule_type_code
2032 ,p_product_rule_code => l_product_rule_code);
2033
2034 END;
2035 END IF;
2036 ELSE
2037 OPEN c_syn_owner;
2038 FETCH c_syn_owner
2039 INTO l_syn_owner;
2040
2041 IF l_ref_object_flag = 'N' THEN
2042 BEGIN
2043 INSERT
2044 INTO xla_extract_objects_gt
2045 (object_name
2046 ,owner)
2047 VALUES(l_object_name
2048 ,l_syn_owner);
2049 EXCEPTION
2050 WHEN OTHERS THEN
2051 Xla_amb_setup_err_pkg.stack_error
2052 (p_message_name => 'XLA_AB_EXT_OBJECT_ERROR'
2053 ,p_message_type => 'E'
2054 ,p_message_category => 'EXTRACT_OBJECT'
2055 ,p_category_sequence => 3
2056 ,p_application_id => l_application_id
2057 ,p_entity_code => l_entity_code
2058 ,p_event_class_code => l_event_class_code
2059 ,p_extract_object_name => l_object_name
2060 ,p_amb_context_code => l_amb_context_code
2061 ,p_product_rule_type_code => l_product_rule_type_code
2062 ,p_product_rule_code => l_product_rule_code);
2063
2064 END;
2065 ELSE
2066 BEGIN
2067 INSERT
2068 INTO xla_reference_objects_gt
2069 (reference_object_name
2070 ,owner)
2071 VALUES(l_object_name
2072 ,l_syn_owner);
2073 EXCEPTION
2074 WHEN OTHERS THEN
2075 Xla_amb_setup_err_pkg.stack_error
2076 (p_message_name => 'XLA_AB_EXT_OBJECT_ERROR'
2077 ,p_message_type => 'E'
2078 ,p_message_category => 'EXTRACT_OBJECT'
2079 ,p_category_sequence => 3
2080 ,p_application_id => l_application_id
2081 ,p_entity_code => l_entity_code
2082 ,p_event_class_code => l_event_class_code
2083 ,p_extract_object_name => l_object_name
2084 ,p_amb_context_code => l_amb_context_code
2085 ,p_product_rule_type_code => l_product_rule_type_code
2086 ,p_product_rule_code => l_product_rule_code);
2087
2088 END;
2089 END IF;
2090
2091 CLOSE c_syn_owner;
2092 END IF;
2093 CLOSE c_object_type;
2094 END LOOP;
2095 CLOSE c_aad_objects;
2096 END IF;
2097
2098
2099 EXCEPTION
2100 WHEN xla_exceptions_pkg.application_exception THEN
2101 RAISE;
2102 WHEN OTHERS THEN
2103 xla_exceptions_pkg.raise_message
2104 (p_location => 'xla_extract_integrity_pkg.set_extract_object_owner');
2105 END Set_extract_object_owner; -- end of procedure
2106
2107
2108 --=============================================================================
2109 -- *********** private procedures and functions **********
2110 --=============================================================================
2111 --=============================================================================
2112 --
2113 -- Following are the private routines:
2114 --
2115 -- 1. Chk_primary_keys_exist
2116 -- 2. Validate_accounting_sources
2117 -- 3. Create_sources
2118 -- 4. Assign_sources
2119 --
2120 --
2121 --=============================================================================
2122 /*======================================================================+
2123 | |
2124 | Private Function |
2125 | |
2126 | Chk_primary_keys_exist |
2127 | |
2128 | This routine checks if the primary keys are defined in the extract |
2129 | objects based on extract object type |
2130 | |
2131 +======================================================================*/
2132
2133
2134 FUNCTION Chk_primary_keys_exist
2135 (p_application_id IN NUMBER
2136 ,p_entity_code IN VARCHAR2
2137 ,p_event_class_code IN VARCHAR2
2138 ,p_amb_context_code IN VARCHAR2
2139 ,p_product_rule_type_code IN VARCHAR2
2140 ,p_product_rule_code IN VARCHAR2)
2141 RETURN BOOLEAN
2142 IS
2143
2144 -- Variable Declaration
2145 l_application_id NUMBER(15);
2146 l_entity_code VARCHAR2(30);
2147 l_event_class_code VARCHAR2(30);
2148 l_amb_context_code VARCHAR2(30);
2149 l_product_rule_code VARCHAR2(30);
2150 l_product_rule_type_code VARCHAR2(1);
2151 l_return BOOLEAN := TRUE;
2152
2153 -- Cursor Declaration
2154
2155 -- Note: No unnest hint has been added to the subquery based on recommendation
2156 -- from the performance team to improve performance
2157
2158 -- Get all extract objects for an AAD which do not have event_id column
2159 -- Added and commented for bug 15938563
2160 /*
2161 CURSOR c_aad_event_id
2162 IS
2163 SELECT distinct extract_object_name, extract_object_type_code
2164 FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og
2165 WHERE application_id = p_application_id
2166 AND entity_code = p_entity_code
2167 AND event_class_code = p_event_class_code
2168 AND extract_object_name IS NOT NULL
2169 AND extract_object_name = og.object_name
2170 AND NOT EXISTS (SELECT 'x'
2171 FROM dba_tab_columns t
2172 WHERE t.table_name = og.object_name
2173 AND og.owner = t.owner
2174 AND t.column_name = 'EVENT_ID'
2175 AND t.data_type = 'NUMBER');
2176 -- 4420371 AND t.NULLABLE = 'N');
2177 */
2178 CURSOR c_aad_event_id
2179 IS
2180 SELECT distinct extract_object_name, extract_object_type_code
2181 FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og
2182 WHERE application_id = p_application_id
2183 AND entity_code = p_entity_code
2184 AND event_class_code = p_event_class_code
2185 AND extract_object_name IS NOT NULL
2186 AND extract_object_name = og.object_name
2187 AND NOT EXISTS (SELECT 'x'
2188 FROM dba_tab_columns t, user_objects uo
2189 WHERE t.table_name = og.object_name
2190 AND og.owner = t.owner
2191 AND uo.object_name = og.object_name
2192 AND uo.object_type <> 'SYNONYM'
2193 AND t.column_name = 'EVENT_ID'
2194 AND t.data_type = 'NUMBER'
2195 UNION ALL
2196 --SELECT 'x'
2197 -- FROM dba_tab_columns t,user_synonyms s,user_objects uo
2198 -- WHERE s.table_owner = t.owner
2199 -- AND s.table_name = t.table_name
2200 -- AND s.synonym_name =og.object_name
2201 -- AND uo.object_name = og.object_name
2202 -- AND uo.object_type = 'SYNONYM'
2203 -- AND t.column_name = 'EVENT_ID'
2204 -- AND t.data_type = 'NUMBER'
2205 SELECT /*+ no_unnest */ 'x'
2206 FROM USER_SYNONYMS S,USER_OBJECTS UO
2207 WHERE 1=1
2208 AND UO.OBJECT_NAME = og.object_name
2209 AND UO.OBJECT_TYPE = 'SYNONYM'
2210 AND S.SYNONYM_NAME = UO.OBJECT_NAME
2211 AND exists (select /*+ no_unnest */ 'x'
2212 from DBA_TAB_COLUMNS T
2213 where S.TABLE_OWNER = T.OWNER
2214 AND S.TABLE_NAME = T.TABLE_NAME
2215 AND T.COLUMN_NAME = 'EVENT_ID'
2216 and t.data_type = 'NUMBER'
2217 )
2218 );
2219 -- 4420371 AND t.NULLABLE = 'N');
2220
2221
2222 l_aad_event_id c_aad_event_id%rowtype;
2223
2224 -- Get all extract objects for an AAD which do not have language column
2225 -- Added and commented for bug 15938563
2226 /*
2227 CURSOR c_aad_language
2228 IS
2229 SELECT distinct extract_object_name, extract_object_type_code
2230 FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og
2231 WHERE application_id = p_application_id
2232 AND entity_code = p_entity_code
2233 AND event_class_code = p_event_class_code
2234 AND extract_object_name IS NOT NULL
2235 AND extract_object_type_code IN ('HEADER_MLS','LINE_MLS')
2236 AND extract_object_name = og.object_name
2237 AND NOT EXISTS (SELECT 'x'
2238 FROM dba_tab_columns t
2239 WHERE t.table_name = og.object_name
2240 AND og.owner = t.owner
2241 AND t.column_name = 'LANGUAGE'
2242 AND t.data_type = 'VARCHAR2');
2243 -- 4420371 AND t.NULLABLE = 'N'); */
2244 CURSOR c_aad_language
2245 IS
2246 SELECT distinct extract_object_name, extract_object_type_code
2247 FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og
2248 WHERE application_id = p_application_id
2249 AND entity_code = p_entity_code
2250 AND event_class_code = p_event_class_code
2251 AND extract_object_name IS NOT NULL
2252 AND extract_object_type_code IN ('HEADER_MLS','LINE_MLS')
2253 AND extract_object_name = og.object_name
2254 AND NOT EXISTS (SELECT 'x'
2255 FROM dba_tab_columns t, user_objects uo
2256 WHERE t.table_name = og.object_name
2257 AND og.owner = t.owner
2258 AND uo.object_name = og.object_name
2259 AND uo.object_type <> 'SYNONYM'
2260 AND t.column_name = 'LANGUAGE'
2261 AND t.data_type = 'VARCHAR2'
2262 UNION ALL
2263 --SELECT 'x'
2264 -- FROM dba_tab_columns t,user_synonyms s,user_objects uo
2265 -- WHERE s.table_owner = t.owner
2266 -- AND s.table_name = t.table_name
2267 -- AND s.synonym_name =og.object_name
2268 -- AND uo.object_name = og.object_name
2269 -- AND uo.object_type = 'SYNONYM'
2270 -- AND t.column_name = 'LANGUAGE'
2271 -- AND t.data_type = 'VARCHAR2'
2272 SELECT /*+ no_unnest */ 'x'
2273 FROM USER_SYNONYMS S,USER_OBJECTS UO
2274 WHERE 1=1
2275 AND UO.OBJECT_NAME = og.object_name
2276 AND UO.OBJECT_TYPE = 'SYNONYM'
2277 AND S.SYNONYM_NAME = UO.OBJECT_NAME
2278 AND exists (select /*+ no_unnest */ 'x'
2279 from DBA_TAB_COLUMNS T
2280 where S.TABLE_OWNER = T.OWNER
2281 AND S.TABLE_NAME = T.TABLE_NAME
2282 AND T.COLUMN_NAME = 'LANGUAGE'
2283 and t.data_type = 'VARCHAR2'
2284 )
2285 );
2286 -- 4420371 AND t.NULLABLE = 'N');
2287
2288
2289
2290 l_aad_language c_aad_language%rowtype;
2291
2292 -- Get all extract objects for an AAD which do not have line_number column
2293 -- Added and commented for bug 15938563
2294 /*
2295 CURSOR c_aad_line_number
2296 IS
2297 SELECT distinct extract_object_name, extract_object_type_code
2298 FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og
2299 WHERE application_id = p_application_id
2300 AND entity_code = p_entity_code
2301 AND event_class_code = p_event_class_code
2302 AND extract_object_name IS NOT NULL
2303 AND extract_object_type_code IN ('LINE','LINE_MLS')
2304 AND extract_object_name = og.object_name
2305 AND NOT EXISTS (SELECT 'x'
2306 FROM dba_tab_columns t
2307 WHERE t.table_name = og.object_name
2308 AND og.owner = t.owner
2309 AND t.column_name = 'LINE_NUMBER'
2310 AND t.data_type = 'NUMBER');
2311 -- 4420371 AND t.NULLABLE = 'N'); */
2312 CURSOR c_aad_line_number
2313 IS
2314 SELECT distinct extract_object_name, extract_object_type_code
2315 FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og
2316 WHERE application_id = p_application_id
2317 AND entity_code = p_entity_code
2318 AND event_class_code = p_event_class_code
2319 AND extract_object_name IS NOT NULL
2320 AND extract_object_type_code IN ('LINE','LINE_MLS')
2321 AND extract_object_name = og.object_name
2322 AND NOT EXISTS (SELECT 'x'
2323 FROM dba_tab_columns t, user_objects uo
2324 WHERE t.table_name = og.object_name
2325 AND og.owner = t.owner
2326 AND uo.object_name = og.object_name
2327 AND uo.object_type <> 'SYNONYM'
2328 AND t.column_name = 'LINE_NUMBER'
2329 AND t.data_type = 'NUMBER'
2330 UNION ALL
2331 --SELECT 'x'
2332 -- FROM dba_tab_columns t,user_synonyms s,user_objects uo
2333 -- WHERE s.table_owner = t.owner
2334 -- AND s.table_name = t.table_name
2335 -- AND s.synonym_name =og.object_name
2336 -- AND uo.object_name = og.object_name
2337 -- AND uo.object_type = 'SYNONYM'
2338 -- AND t.column_name = 'LINE_NUMBER'
2339 -- AND t.data_type = 'NUMBER'
2340 SELECT /*+ no_unnest */ 'x'
2341 FROM USER_SYNONYMS S,USER_OBJECTS UO
2342 WHERE 1=1
2343 AND UO.OBJECT_NAME = og.object_name
2344 AND UO.OBJECT_TYPE = 'SYNONYM'
2345 AND S.SYNONYM_NAME = UO.OBJECT_NAME
2346 AND exists (select /*+ no_unnest */ 'x'
2347 from DBA_TAB_COLUMNS T
2348 where S.TABLE_OWNER = T.OWNER
2349 AND S.TABLE_NAME = T.TABLE_NAME
2350 AND T.COLUMN_NAME = 'LINE_NUMBER'
2351 and t.data_type = 'NUMBER'
2352 )
2353 );
2354 -- 4420371 AND t.NULLABLE = 'N');
2355
2356
2357 l_aad_line_number c_aad_line_number%rowtype;
2358
2359 -- Get all extract objects for an AAD which do not have ledger_id column
2360 -- Added and commented for bug 15938563
2361 /*
2362 CURSOR c_aad_ledger_id
2363 IS
2364 SELECT distinct extract_object_name, extract_object_type_code
2365 FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og, xla_subledgers app
2366 WHERE e.application_id = p_application_id
2367 AND e.entity_code = p_entity_code
2368 AND e.event_class_code = p_event_class_code
2369 AND e.extract_object_name IS NOT NULL
2370 AND e.extract_object_type_code IN ('LINE','LINE_MLS')
2371 AND e.extract_object_name = og.object_name
2372 AND e.application_id = app.application_id
2373 AND app.alc_enabled_flag = 'N'
2374 AND NOT EXISTS (SELECT 'x'
2375 FROM dba_tab_columns t
2376 WHERE t.table_name = og.object_name
2377 AND og.owner = t.owner
2378 AND t.column_name = 'LEDGER_ID'
2379 AND t.data_type = 'NUMBER'); */
2380 CURSOR c_aad_ledger_id
2381 IS
2382 SELECT distinct extract_object_name, extract_object_type_code
2383 FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og, xla_subledgers app
2384 WHERE e.application_id = p_application_id
2385 AND e.entity_code = p_entity_code
2386 AND e.event_class_code = p_event_class_code
2387 AND e.extract_object_name IS NOT NULL
2388 AND e.extract_object_type_code IN ('LINE','LINE_MLS')
2389 AND e.extract_object_name = og.object_name
2390 AND e.application_id = app.application_id
2391 AND app.alc_enabled_flag = 'N'
2392 AND NOT EXISTS (SELECT 'x'
2393 FROM dba_tab_columns t, user_objects uo
2394 WHERE t.table_name = og.object_name
2395 AND og.owner = t.owner
2396 AND uo.object_name = og.object_name
2397 AND uo.object_type <> 'SYNONYM'
2398 AND t.column_name = 'LEDGER_ID'
2399 AND t.data_type = 'NUMBER'
2400 UNION ALL
2401 --SELECT 'x'
2402 -- FROM dba_tab_columns t,user_synonyms s,user_objects uo
2403 -- WHERE s.table_owner = t.owner
2404 -- AND s.table_name = t.table_name
2405 -- AND s.synonym_name =og.object_name
2406 -- AND uo.object_name = og.object_name
2407 -- AND uo.object_type = 'SYNONYM'
2408 -- AND t.column_name = 'LEDGER_ID'
2409 -- AND t.data_type = 'NUMBER'
2410 SELECT /*+ no_unnest */ 'x'
2411 FROM USER_SYNONYMS S,USER_OBJECTS UO
2412 WHERE 1=1
2413 AND UO.OBJECT_NAME = og.object_name
2414 AND UO.OBJECT_TYPE = 'SYNONYM'
2415 AND S.SYNONYM_NAME = UO.OBJECT_NAME
2416 AND exists (select /*+ no_unnest */ 'x'
2417 from DBA_TAB_COLUMNS T
2418 where S.TABLE_OWNER = T.OWNER
2419 AND S.TABLE_NAME = T.TABLE_NAME
2420 AND T.COLUMN_NAME = 'LEDGER_ID'
2421 and t.data_type = 'NUMBER'
2422 )
2423 );
2424
2425
2426 l_aad_ledger_id c_aad_ledger_id%rowtype;
2427
2428 -- Get all extract objects for an event class which do not have event_id column
2429 -- Added and commented for bug 15938563
2430 /*
2431 CURSOR c_event_id
2432 IS
2433 SELECT e.object_name, object_type_code
2434 FROM xla_extract_objects e, xla_extract_objects_gt og
2435 WHERE application_id = p_application_id
2436 AND entity_code = p_entity_code
2437 AND event_class_code = p_event_class_code
2438 AND e.object_name = og.object_name
2439 AND NOT EXISTS (SELECT 'x'
2440 FROM dba_tab_columns t
2441 WHERE t.table_name = og.object_name
2442 AND og.owner = t.owner
2443 AND t.column_name = 'EVENT_ID'
2444 AND t.data_type = 'NUMBER')
2445 -- 4420371 AND t.nullable = 'N')
2446 AND EXISTS (SELECT 'y'
2447 FROM xla_extract_objects_gt a
2448 WHERE a.object_name = e.object_name); */
2449 CURSOR c_event_id
2450 IS
2451 SELECT e.object_name, object_type_code
2452 FROM xla_extract_objects e, xla_extract_objects_gt og
2453 WHERE application_id = p_application_id
2454 AND entity_code = p_entity_code
2455 AND event_class_code = p_event_class_code
2456 AND e.object_name = og.object_name
2457 AND NOT EXISTS (SELECT 'x'
2458 FROM dba_tab_columns t, user_objects uo
2459 WHERE t.table_name = og.object_name
2460 AND og.owner = t.owner
2461 AND uo.object_name = og.object_name
2462 AND uo.object_type <> 'SYNONYM'
2463 AND t.column_name = 'EVENT_ID'
2464 AND t.data_type = 'NUMBER'
2465 UNION ALL
2466 --SELECT 'x'
2467 -- FROM dba_tab_columns t,user_synonyms s,user_objects uo
2468 -- WHERE s.table_owner = t.owner
2469 -- AND s.table_name = t.table_name
2470 -- AND s.synonym_name =og.object_name
2471 -- AND uo.object_name = og.object_name
2472 -- AND uo.object_type = 'SYNONYM'
2473 -- AND t.column_name = 'EVENT_ID'
2474 -- AND t.data_type = 'NUMBER'
2475 SELECT /*+ no_unnest */ 'x'
2476 FROM USER_SYNONYMS S,USER_OBJECTS UO
2477 WHERE 1=1
2478 AND UO.OBJECT_NAME = og.object_name
2479 AND UO.OBJECT_TYPE = 'SYNONYM'
2480 AND S.SYNONYM_NAME = UO.OBJECT_NAME
2481 AND exists (select /*+ no_unnest */ 'x'
2482 from DBA_TAB_COLUMNS T
2483 where S.TABLE_OWNER = T.OWNER
2484 AND S.TABLE_NAME = T.TABLE_NAME
2485 AND T.COLUMN_NAME = 'EVENT_ID'
2486 and t.data_type = 'NUMBER'
2487 )
2488 )
2489 -- 4420371 AND t.nullable = 'N')
2490 AND EXISTS (SELECT 'y'
2491 FROM xla_extract_objects_gt a
2492 WHERE a.object_name = e.object_name);
2493
2494
2495 l_event_id c_event_id%rowtype;
2496
2497 -- Get all extract objects for an event class which do not have language column
2498 -- Added and commented for bug 15938563
2499 /*
2500 CURSOR c_language
2501 IS
2502 SELECT e.object_name, object_type_code
2503 FROM xla_extract_objects e, xla_extract_objects_gt og
2504 WHERE application_id = p_application_id
2505 AND entity_code = p_entity_code
2506 AND event_class_code = p_event_class_code
2507 AND object_type_code IN ('HEADER_MLS','LINE_MLS')
2508 AND e.object_name = og.object_name
2509 AND NOT EXISTS (SELECT 'x'
2510 FROM dba_tab_columns t
2511 WHERE t.table_name = og.object_name
2512 AND og.owner = t.owner
2513 AND t.column_name = 'LANGUAGE'
2514 AND t.data_type = 'VARCHAR2')
2515 -- 4420371 AND t.nullable = 'N')
2516 AND EXISTS (SELECT 'y'
2517 FROM xla_extract_objects_gt a
2518 WHERE a.object_name = e.object_name); */
2519 CURSOR c_language
2520 IS
2521 SELECT e.object_name, object_type_code
2522 FROM xla_extract_objects e, xla_extract_objects_gt og
2523 WHERE application_id = p_application_id
2524 AND entity_code = p_entity_code
2525 AND event_class_code = p_event_class_code
2526 AND object_type_code IN ('HEADER_MLS','LINE_MLS')
2527 AND e.object_name = og.object_name
2528 AND NOT EXISTS (SELECT 'x'
2529 FROM dba_tab_columns t, user_objects uo
2530 WHERE t.table_name = og.object_name
2531 AND og.owner = t.owner
2532 AND uo.object_name = og.object_name
2533 AND uo.object_type <> 'SYNONYM'
2534 AND t.column_name = 'LANGUAGE'
2535 AND t.data_type = 'VARCHAR2'
2536 UNION ALL
2537 --SELECT 'x'
2538 -- FROM dba_tab_columns t,user_synonyms s,user_objects uo
2539 -- WHERE s.table_owner = t.owner
2540 -- AND s.table_name = t.table_name
2541 -- AND s.synonym_name =og.object_name
2542 -- AND uo.object_name = og.object_name
2543 -- AND uo.object_type = 'SYNONYM'
2544 -- AND t.column_name = 'LANGUAGE'
2545 -- AND t.data_type = 'VARCHAR2'
2546 SELECT /*+ no_unnest */ 'x'
2547 FROM USER_SYNONYMS S,USER_OBJECTS UO
2548 WHERE 1=1
2549 AND UO.OBJECT_NAME = og.object_name
2550 AND UO.OBJECT_TYPE = 'SYNONYM'
2551 AND S.SYNONYM_NAME = UO.OBJECT_NAME
2552 AND exists (select /*+ no_unnest */ 'x'
2553 from DBA_TAB_COLUMNS T
2554 where S.TABLE_OWNER = T.OWNER
2555 AND S.TABLE_NAME = T.TABLE_NAME
2556 AND T.COLUMN_NAME = 'LANGUAGE'
2557 and t.data_type = 'VARCHAR2'
2558 )
2559 )
2560 -- 4420371 AND t.nullable = 'N')
2561 AND EXISTS (SELECT 'y'
2562 FROM xla_extract_objects_gt a
2563 WHERE a.object_name = e.object_name);
2564
2565
2566 l_language c_language%rowtype;
2567
2568 -- Get all extract objects for an event class which do not have line_number column
2569 -- Added and commented for bug 15938563
2570 /*
2571 CURSOR c_line_number
2572 IS
2573 SELECT e.object_name, object_type_code
2574 FROM xla_extract_objects e, xla_extract_objects_gt og
2575 WHERE application_id = p_application_id
2576 AND entity_code = p_entity_code
2577 AND event_class_code = p_event_class_code
2578 AND object_type_code IN ('LINE','LINE_MLS')
2579 AND e.object_name = og.object_name
2580 AND NOT EXISTS (SELECT 'x'
2581 FROM dba_tab_columns t
2582 WHERE t.table_name = og.object_name
2583 AND og.owner = t.owner
2584 AND t.column_name = 'LINE_NUMBER'
2585 AND t.data_type = 'NUMBER')
2586 -- 4420371 AND t.nullable = 'N')
2587 AND EXISTS (SELECT 'y'
2588 FROM xla_extract_objects_gt a
2589 WHERE a.object_name = e.object_name); */
2590 CURSOR c_line_number
2591 IS
2592 SELECT e.object_name, object_type_code
2593 FROM xla_extract_objects e, xla_extract_objects_gt og
2594 WHERE application_id = p_application_id
2595 AND entity_code = p_entity_code
2596 AND event_class_code = p_event_class_code
2597 AND object_type_code IN ('LINE','LINE_MLS')
2598 AND e.object_name = og.object_name
2599 AND NOT EXISTS (SELECT 'x'
2600 FROM dba_tab_columns t, user_objects uo
2601 WHERE t.table_name = og.object_name
2602 AND og.owner = t.owner
2603 AND uo.object_name = og.object_name
2604 AND uo.object_type <> 'SYNONYM'
2605 AND t.column_name = 'LINE_NUMBER'
2606 AND t.data_type = 'NUMBER'
2607 UNION ALL
2608 --SELECT 'x'
2609 -- FROM dba_tab_columns t,user_synonyms s,user_objects uo
2610 -- WHERE s.table_owner = t.owner
2611 -- AND s.table_name = t.table_name
2612 -- AND s.synonym_name =og.object_name
2613 -- AND uo.object_name = og.object_name
2614 -- AND uo.object_type = 'SYNONYM'
2615 -- AND t.column_name = 'LINE_NUMBER'
2616 -- AND t.data_type = 'NUMBER'
2617 SELECT /*+ no_unnest */ 'x'
2618 FROM USER_SYNONYMS S,USER_OBJECTS UO
2619 WHERE 1=1
2620 AND UO.OBJECT_NAME = og.object_name
2621 AND UO.OBJECT_TYPE = 'SYNONYM'
2622 AND S.SYNONYM_NAME = UO.OBJECT_NAME
2623 AND exists (select /*+ no_unnest */ 'x'
2624 from DBA_TAB_COLUMNS T
2625 where S.TABLE_OWNER = T.OWNER
2626 AND S.TABLE_NAME = T.TABLE_NAME
2627 AND T.COLUMN_NAME = 'LINE_NUMBER'
2628 and t.data_type = 'NUMBER'
2629 )
2630 )
2631 -- 4420371 AND t.nullable = 'N')
2632 AND EXISTS (SELECT 'y'
2633 FROM xla_extract_objects_gt a
2634 WHERE a.object_name = e.object_name);
2635
2636
2637 l_line_number c_line_number%rowtype;
2638
2639 -- Get all extract objects for an event class which do not have ledger_id column
2640 -- Added and commented for bug 15938563
2641 /* CURSOR c_ledger_id
2642 IS
2643 SELECT e.object_name, object_type_code
2644 FROM xla_extract_objects e, xla_extract_objects_gt og, xla_subledgers app
2645 WHERE e.application_id = p_application_id
2646 AND e.entity_code = p_entity_code
2647 AND e.event_class_code = p_event_class_code
2648 AND e.object_type_code IN ('LINE','LINE_MLS')
2649 AND e.object_name = og.object_name
2650 AND e.application_id = app.application_id
2651 AND app.alc_enabled_flag = 'N'
2652 AND NOT EXISTS (SELECT 'x'
2653 FROM dba_tab_columns t
2654 WHERE t.table_name = og.object_name
2655 AND og.owner = t.owner
2656 AND t.column_name = 'LEDGER_ID'
2657 AND t.data_type = 'NUMBER')
2658 AND EXISTS (SELECT 'y'
2659 FROM xla_extract_objects_gt a
2660 WHERE a.object_name = e.object_name); */
2661 CURSOR c_ledger_id
2662 IS
2663 SELECT e.object_name, object_type_code
2664 FROM xla_extract_objects e, xla_extract_objects_gt og, xla_subledgers app
2665 WHERE e.application_id = p_application_id
2666 AND e.entity_code = p_entity_code
2667 AND e.event_class_code = p_event_class_code
2668 AND e.object_type_code IN ('LINE','LINE_MLS')
2669 AND e.object_name = og.object_name
2670 AND e.application_id = app.application_id
2671 AND app.alc_enabled_flag = 'N'
2672 AND NOT EXISTS (SELECT 'x'
2673 FROM dba_tab_columns t, user_objects uo
2674 WHERE t.table_name = og.object_name
2675 AND og.owner = t.owner
2676 AND uo.object_name = og.object_name
2677 AND uo.object_type <> 'SYNONYM'
2678 AND t.column_name = 'LEDGER_ID'
2679 AND t.data_type = 'NUMBER'
2680 UNION ALL
2681 --SELECT 'x'
2682 -- FROM dba_tab_columns t,user_synonyms s,user_objects uo
2683 -- WHERE s.table_owner = t.owner
2684 -- AND s.table_name = t.table_name
2685 -- AND s.synonym_name =og.object_name
2686 -- AND uo.object_name = og.object_name
2687 -- AND uo.object_type = 'SYNONYM'
2688 -- AND t.column_name = 'LEDGER_ID'
2689 -- AND t.data_type = 'NUMBER'
2690 SELECT /*+ no_unnest */ 'x'
2691 FROM USER_SYNONYMS S,USER_OBJECTS UO
2692 WHERE 1=1
2693 AND UO.OBJECT_NAME = og.object_name
2694 AND UO.OBJECT_TYPE = 'SYNONYM'
2695 AND S.SYNONYM_NAME = UO.OBJECT_NAME
2696 AND exists (select /*+ no_unnest */ 'x'
2697 from DBA_TAB_COLUMNS T
2698 where S.TABLE_OWNER = T.OWNER
2699 AND S.TABLE_NAME = T.TABLE_NAME
2700 AND T.COLUMN_NAME = 'LEDGER_ID'
2701 and t.data_type = 'NUMBER'
2702 ))
2703 AND EXISTS (SELECT 'y'
2704 FROM xla_extract_objects_gt a
2705 WHERE a.object_name = e.object_name);
2706
2707
2708 l_ledger_id c_ledger_id%rowtype;
2709
2710 BEGIN
2711
2712 l_application_id := p_application_id;
2713 l_entity_code := p_entity_code;
2714 l_event_class_code := p_event_class_code;
2715 l_amb_context_code := p_amb_context_code;
2716 l_product_rule_code := p_product_rule_code;
2717 l_product_rule_type_code := p_product_rule_type_code;
2718
2719 g_trace_label :='Check_primary_keys_exist';
2720
2721 IF (g_log_level is NULL) THEN
2722 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2723 END IF;
2724
2725 IF (g_log_level is NULL) THEN
2726 g_log_enabled := fnd_log.test
2727 (log_level => g_log_level
2728 ,module => C_DEFAULT_MODULE);
2729 END IF;
2730
2731 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
2732 trace
2733 (p_msg => 'Begin'
2734 ,p_level => C_LEVEL_PROCEDURE);
2735 trace
2736 (p_msg => 'p_application_id = ' ||TO_CHAR(p_application_id)
2737 ,p_level => C_LEVEL_PROCEDURE);
2738 trace
2739 (p_msg => 'p_entity_code = '||p_entity_code
2740 ,p_level => C_LEVEL_PROCEDURE);
2741 trace
2742 (p_msg => 'p_event_class_code = ' ||p_event_class_code
2743 ,p_level => C_LEVEL_PROCEDURE);
2744 trace
2745 (p_msg => 'p_amb_context_code = '||p_amb_context_code
2746 ,p_level => C_LEVEL_PROCEDURE);
2747 trace
2748 (p_msg => 'p_product_rule_type_code = ' ||p_product_rule_type_code
2749 ,p_level => C_LEVEL_PROCEDURE);
2750 trace
2751 (p_msg => 'p_product_rule_code = ' ||p_product_rule_code
2752 ,p_level => C_LEVEL_PROCEDURE);
2753 END IF;
2754
2755 -- Validate for an AAD
2756 IF p_product_rule_code is not null then
2757
2758 -- Check if event_id exists with correct data type
2759 -- for all level extract objects
2760 OPEN c_aad_event_id;
2761 LOOP
2762 FETCH c_aad_event_id
2763 INTO l_aad_event_id;
2764 EXIT WHEN c_aad_event_id%NOTFOUND;
2765
2766 Xla_amb_setup_err_pkg.stack_error
2767 (p_message_name => 'XLA_AB_PK_EVENT_ID_NOT_DEFINED'
2768 ,p_message_type => 'E'
2769 ,p_message_category => 'EXTRACT_OBJECT'
2770 ,p_category_sequence => 3
2771 ,p_application_id => l_application_id
2772 ,p_entity_code => l_entity_code
2773 ,p_event_class_code => l_event_class_code
2774 ,p_amb_context_code => l_amb_context_code
2775 ,p_product_rule_type_code => l_product_rule_type_code
2776 ,p_product_rule_code => l_product_rule_code
2777 ,p_extract_object_name => l_aad_event_id.extract_object_name
2778 ,p_extract_object_type => l_aad_event_id.extract_object_type_code);
2779 l_return := FALSE;
2780
2781 END LOOP;
2782 CLOSE c_aad_event_id;
2783
2784 -- Check if the LANGUAGE exists with correct data type
2785 -- for header_mls and line_mls level extract objects
2786
2787 OPEN c_aad_language;
2788 LOOP
2789 FETCH c_aad_language
2790 INTO l_aad_language;
2791 EXIT WHEN c_aad_language%NOTFOUND;
2792
2793 Xla_amb_setup_err_pkg.stack_error
2794 (p_message_name => 'XLA_AB_PK_LANGUAGE_NOT_DEFINED'
2795 ,p_message_type => 'E'
2796 ,p_message_category => 'EXTRACT_OBJECT'
2797 ,p_category_sequence => 3
2798 ,p_application_id => l_application_id
2799 ,p_entity_code => l_entity_code
2800 ,p_event_class_code => l_event_class_code
2801 ,p_amb_context_code => l_amb_context_code
2802 ,p_product_rule_type_code => l_product_rule_type_code
2803 ,p_product_rule_code => l_product_rule_code
2804 ,p_extract_object_name => l_aad_language.extract_object_name
2805 ,p_extract_object_type => l_aad_language.extract_object_type_code);
2806 l_return := FALSE;
2807
2808 END LOOP;
2809 CLOSE c_aad_language;
2810
2811 -- Check if the LINE_NUMBER exists with correct data type
2812 -- for Line, line_mls and base_currency level extract objects
2813
2814 OPEN c_aad_line_number;
2815 LOOP
2816 FETCH c_aad_line_number
2817 INTO l_aad_line_number;
2818 EXIT WHEN c_aad_line_number%NOTFOUND;
2819
2820 Xla_amb_setup_err_pkg.stack_error
2821 (p_message_name => 'XLA_AB_PK_LINE_NUM_NOT_DEFINED'
2822 ,p_message_type => 'E'
2823 ,p_message_category => 'EXTRACT_OBJECT'
2824 ,p_category_sequence => 3
2825 ,p_application_id => l_application_id
2826 ,p_entity_code => l_entity_code
2827 ,p_event_class_code => l_event_class_code
2828 ,p_amb_context_code => l_amb_context_code
2829 ,p_product_rule_type_code => l_product_rule_type_code
2830 ,p_product_rule_code => l_product_rule_code
2831 ,p_extract_object_name => l_aad_line_number.extract_object_name
2832 ,p_extract_object_type => l_aad_line_number.extract_object_type_code);
2833 l_return := FALSE;
2834
2835 END LOOP;
2836 CLOSE c_aad_line_number;
2837
2838 -- Check if the LEDGER_ID exists with correct data type
2839 -- for base_currency level extract objects
2840
2841 OPEN c_aad_ledger_id;
2842 LOOP
2843 FETCH c_aad_ledger_id
2844 INTO l_aad_ledger_id;
2845 EXIT WHEN c_aad_ledger_id%NOTFOUND;
2846
2847 Xla_amb_setup_err_pkg.stack_error
2848 (p_message_name => 'XLA_AB_PK_LED_ID_NOT_DEFINED'
2849 ,p_message_type => 'E'
2850 ,p_message_category => 'EXTRACT_OBJECT'
2851 ,p_category_sequence => 3
2852 ,p_application_id => l_application_id
2853 ,p_entity_code => l_entity_code
2854 ,p_event_class_code => l_event_class_code
2855 ,p_amb_context_code => l_amb_context_code
2856 ,p_product_rule_type_code => l_product_rule_type_code
2857 ,p_product_rule_code => l_product_rule_code
2858 ,p_extract_object_name => l_aad_ledger_id.extract_object_name
2859 ,p_extract_object_type => l_aad_ledger_id.extract_object_type_code);
2860 l_return := FALSE;
2861
2862 END LOOP;
2863 CLOSE c_aad_ledger_id;
2864
2865 ELSE
2866 -- Validate for an event class
2867
2868 -- Check if event_id exists with correct data type
2869 -- for all level extract objects
2870 OPEN c_event_id;
2871 LOOP
2872 FETCH c_event_id
2873 INTO l_event_id;
2874 EXIT WHEN c_event_id%NOTFOUND;
2875
2876 Xla_amb_setup_err_pkg.stack_error
2877 (p_message_name => 'XLA_AB_PK_EVENT_ID_NOT_DEFINED'
2878 ,p_message_type => 'E'
2879 ,p_message_category => 'EXTRACT_OBJECT'
2880 ,p_category_sequence => 3
2881 ,p_application_id => l_application_id
2882 ,p_entity_code => l_entity_code
2883 ,p_event_class_code => l_event_class_code
2884 ,p_amb_context_code => l_amb_context_code
2885 ,p_product_rule_type_code => l_product_rule_type_code
2886 ,p_product_rule_code => l_product_rule_code
2887 ,p_extract_object_name => l_event_id.object_name
2888 ,p_extract_object_type => l_event_id.object_type_code);
2889 l_return := FALSE;
2890
2891 END LOOP;
2892 CLOSE c_event_id;
2893
2894 -- Check if the LANGUAGE exists with correct data type
2895 -- for header_mls and line_mls level extract objects
2896
2897 OPEN c_language;
2898 LOOP
2899 FETCH c_language
2900 INTO l_language;
2901 EXIT WHEN c_language%NOTFOUND;
2902
2903 Xla_amb_setup_err_pkg.stack_error
2904 (p_message_name => 'XLA_AB_PK_LANGUAGE_NOT_DEFINED'
2905 ,p_message_type => 'E'
2906 ,p_message_category => 'EXTRACT_OBJECT'
2907 ,p_category_sequence => 3
2908 ,p_application_id => l_application_id
2909 ,p_entity_code => l_entity_code
2910 ,p_event_class_code => l_event_class_code
2911 ,p_amb_context_code => l_amb_context_code
2912 ,p_product_rule_type_code => l_product_rule_type_code
2913 ,p_product_rule_code => l_product_rule_code
2914 ,p_extract_object_name => l_language.object_name
2915 ,p_extract_object_type => l_language.object_type_code);
2916 l_return := FALSE;
2917
2918 END LOOP;
2919 CLOSE c_language;
2920
2921 -- Check if the LINE_NUMBER exists with correct data type
2922 -- for Line, line_mls and base_currency level extract objects
2923
2924 OPEN c_line_number;
2925 LOOP
2926 FETCH c_line_number
2927 INTO l_line_number;
2928 EXIT WHEN c_line_number%NOTFOUND;
2929
2930 Xla_amb_setup_err_pkg.stack_error
2931 (p_message_name => 'XLA_AB_PK_LINE_NUM_NOT_DEFINED'
2932 ,p_message_type => 'E'
2933 ,p_message_category => 'EXTRACT_OBJECT'
2934 ,p_category_sequence => 3
2935 ,p_application_id => l_application_id
2936 ,p_entity_code => l_entity_code
2937 ,p_event_class_code => l_event_class_code
2938 ,p_amb_context_code => l_amb_context_code
2939 ,p_product_rule_type_code => l_product_rule_type_code
2940 ,p_product_rule_code => l_product_rule_code
2941 ,p_extract_object_name => l_line_number.object_name
2942 ,p_extract_object_type => l_line_number.object_type_code);
2943 l_return := FALSE;
2944
2945 END LOOP;
2946 CLOSE c_line_number;
2947
2948 -- Check if the LEDGER_ID exists with correct data type
2949 -- for base_currency level extract objects
2950
2951 OPEN c_ledger_id;
2952 LOOP
2953 FETCH c_ledger_id
2954 INTO l_ledger_id;
2955 EXIT WHEN c_ledger_id%NOTFOUND;
2956
2957 Xla_amb_setup_err_pkg.stack_error
2958 (p_message_name => 'XLA_AB_PK_LED_ID_NOT_DEFINED'
2959 ,p_message_type => 'E'
2960 ,p_message_category => 'EXTRACT_OBJECT'
2961 ,p_category_sequence => 3
2962 ,p_application_id => l_application_id
2963 ,p_entity_code => l_entity_code
2964 ,p_event_class_code => l_event_class_code
2965 ,p_amb_context_code => l_amb_context_code
2966 ,p_product_rule_type_code => l_product_rule_type_code
2967 ,p_product_rule_code => l_product_rule_code
2968 ,p_extract_object_name => l_ledger_id.object_name
2969 ,p_extract_object_type => l_ledger_id.object_type_code);
2970 l_return := FALSE;
2971
2972 END LOOP;
2973 CLOSE c_ledger_id;
2974 END IF;
2975
2976 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
2977 trace
2978 (p_msg => 'End'
2979 ,p_level => C_LEVEL_PROCEDURE);
2980 END IF;
2981
2982 RETURN l_return;
2983
2984 EXCEPTION
2985 WHEN xla_exceptions_pkg.application_exception THEN
2986 RAISE;
2987 WHEN OTHERS THEN
2988 xla_exceptions_pkg.raise_message
2989 (p_location => 'xla_extract_integrity_pkg.Chk_primary_keys_exist');
2990 END Chk_primary_keys_exist; -- end of function
2991
2992 /*======================================================================+
2993 | |
2994 | Private Function |
2995 | |
2996 | Validate_accounting_sources |
2997 | |
2998 | This routine validates the accounting source mappings for an event |
2999 | class |
3000 | |
3001 +======================================================================*/
3002
3003 FUNCTION Validate_accounting_sources
3004 (p_application_id IN NUMBER
3005 ,p_entity_code IN VARCHAR2
3006 ,p_event_class_code IN VARCHAR2)
3007 RETURN BOOLEAN
3008 IS
3009 -- Variable Declaration
3010
3011 l_application_id NUMBER(15);
3012 l_entity_code VARCHAR2(30);
3013 l_event_class_code VARCHAR2(30);
3014 l_return BOOLEAN := TRUE;
3015 l_exist VARCHAR2(1) := NULL;
3016 l_accounting_attribute_code VARCHAR2(30) := NULL;
3017
3018 -- Cursor Declaration
3019
3020 -- Get all required accounting sources which are not mapped for the event class
3021 CURSOR c_reqd_sources
3022 IS
3023 SELECT accounting_attribute_code
3024 FROM xla_acct_attributes_b a
3025 WHERE a.assignment_required_code = 'Y'
3026 AND NOT EXISTS (SELECT 'x'
3027 FROM xla_evt_class_acct_attrs e
3028 WHERE e.application_id = p_application_id
3029 AND e.event_class_code = p_event_class_code
3030 AND e.accounting_attribute_code = a.accounting_attribute_code
3031 AND e.default_flag = 'Y');
3032
3033 l_reqd_sources c_reqd_sources%rowtype;
3034
3035 -- Get all mappings groups that have atleast one accounting source from the
3036 -- group mapped to the event class
3037 CURSOR c_mapping_groups
3038 IS
3039 SELECT distinct assignment_group_code
3040 FROM xla_acct_attributes_b a
3041 WHERE assignment_group_code IS NOT NULL
3042 AND EXISTS (SELECT 'x'
3043 FROM xla_evt_class_acct_attrs e
3044 WHERE e.application_id = p_application_id
3045 AND e.event_class_code = p_event_class_code
3046 AND e.accounting_attribute_code = a.accounting_attribute_code
3047 AND e.default_flag = 'Y');
3048
3049 l_mapping_groups c_mapping_groups%rowtype;
3050
3051 -- Get all required accounting sources for the above group that are not
3052 -- mapped to the event class
3053 CURSOR c_group_sources
3054 IS
3055 SELECT accounting_attribute_code
3056 FROM xla_acct_attributes_b a
3057 WHERE a.assignment_required_code = 'G'
3058 AND a.assignment_group_code = l_mapping_groups.assignment_group_code
3059 AND NOT EXISTS (SELECT 'x'
3060 FROM xla_evt_class_acct_attrs e
3061 WHERE e.application_id = p_application_id
3062 AND e.event_class_code = p_event_class_code
3063 AND e.accounting_attribute_code = a.accounting_attribute_code
3064 AND e.default_flag = 'Y');
3065
3066 l_group_sources c_group_sources%rowtype;
3067
3068 -- Check if event class has budget or encumbrance enabled
3069 CURSOR c_ec_attrs
3070 IS
3071 SELECT allow_budgets_flag, allow_encumbrance_flag
3072 FROM xla_event_class_attrs e
3073 WHERE e.application_id = p_application_id
3074 AND e.entity_code = p_entity_code
3075 AND e.event_class_code = p_event_class_code;
3076
3077 l_ec_attrs c_ec_attrs%rowtype;
3078
3079 -- Check if event class has budget version id accounting source mapped
3080 CURSOR c_budget
3081 IS
3082 SELECT 'x'
3083 FROM xla_evt_class_acct_attrs e
3084 WHERE e.application_id = p_application_id
3085 AND e.event_class_code = p_event_class_code
3086 AND e.accounting_attribute_code = 'BUDGET_VERSION_ID'
3087 AND e.default_flag = 'Y';
3088
3089 -- Check if event class has encumbrance type id accounting source mapped
3090 /* 4458381
3091 CURSOR c_enc
3092 IS
3093 SELECT 'x'
3094 FROM xla_evt_class_acct_attrs e
3095 WHERE e.application_id = p_application_id
3096 AND e.event_class_code = p_event_class_code
3097 AND e.accounting_attribute_code = 'ENCUMBRANCE_TYPE_ID'
3098 AND e.default_flag = 'Y';
3099 */
3100
3101 -- Check if reversed distribution id 2 is mapped for the event class
3102 CURSOR c_rev_dist_2
3103 IS
3104 SELECT a.accounting_attribute_code, a.assignment_group_code,
3105 a.source_type_code, a.source_code
3106 FROM xla_evt_class_acct_attrs_fvl a
3107 WHERE a.application_id = p_application_id
3108 AND a.event_class_code = p_event_class_code
3109 AND a.accounting_attribute_code = 'REVERSED_DISTRIBUTION_ID2'
3110 AND default_flag = 'Y';
3111
3112 l_rev_dist_2 c_rev_dist_2%rowtype;
3113
3114 -- Check if distribution id 2 is mapped for the event class
3115 CURSOR c_dist_2
3116 IS
3117 SELECT 'x'
3118 FROM xla_evt_class_acct_attrs a
3119 WHERE a.application_id = p_application_id
3120 AND a.event_class_code = p_event_class_code
3121 AND a.accounting_attribute_code = 'DISTRIBUTION_IDENTIFIER_2'
3122 AND default_flag = 'Y';
3123
3124 -- Check if reversed distribution id 3 is mapped for the event class
3125 CURSOR c_rev_dist_3
3126 IS
3127 SELECT a.accounting_attribute_code, a.assignment_group_code
3128 FROM xla_evt_class_acct_attrs_fvl a
3129 WHERE a.application_id = p_application_id
3130 AND a.event_class_code = p_event_class_code
3131 AND a.accounting_attribute_code = 'REVERSED_DISTRIBUTION_ID3'
3132 AND default_flag = 'Y';
3133
3134 l_rev_dist_3 c_rev_dist_3%rowtype;
3135
3136 -- Check if distribution id 3 is mapped for the event class
3137 CURSOR c_dist_3
3138 IS
3139 SELECT 'x'
3140 FROM xla_evt_class_acct_attrs a
3141 WHERE a.application_id = p_application_id
3142 AND a.event_class_code = p_event_class_code
3143 AND a.accounting_attribute_code = 'DISTRIBUTION_IDENTIFIER_3'
3144 AND a.default_flag = 'Y';
3145
3146 -- Check if reversed distribution id 4 is mapped for the event class
3147 CURSOR c_rev_dist_4
3148 IS
3149 SELECT a.accounting_attribute_code, a.assignment_group_code
3150 FROM xla_evt_class_acct_attrs_fvl a
3151 WHERE a.application_id = p_application_id
3152 AND a.event_class_code = p_event_class_code
3153 AND a.accounting_attribute_code = 'REVERSED_DISTRIBUTION_ID4'
3154 AND default_flag = 'Y';
3155
3156 l_rev_dist_4 c_rev_dist_4%rowtype;
3157
3158 -- Check if distribution id 4 is mapped for the event class
3159 CURSOR c_dist_4
3160 IS
3161 SELECT 'x'
3162 FROM xla_evt_class_acct_attrs a
3163 WHERE a.application_id = p_application_id
3164 AND a.event_class_code = p_event_class_code
3165 AND a.accounting_attribute_code = 'DISTRIBUTION_IDENTIFIER_4'
3166 AND default_flag = 'Y';
3167
3168 -- Check if reversed distribution id 5 is mapped for the event class
3169 CURSOR c_rev_dist_5
3170 IS
3171 SELECT a.accounting_attribute_code, a.assignment_group_code
3172 FROM xla_evt_class_acct_attrs_fvl a
3173 WHERE a.application_id = p_application_id
3174 AND a.event_class_code = p_event_class_code
3175 AND a.accounting_attribute_code = 'REVERSED_DISTRIBUTION_ID5'
3176 AND a.default_flag = 'Y';
3177
3178 l_rev_dist_5 c_rev_dist_5%rowtype;
3179
3180 -- Check if distribution id 5 is mapped for the event class
3181 CURSOR c_dist_5
3182 IS
3183 SELECT 'x'
3184 FROM xla_evt_class_acct_attrs a
3185 WHERE a.application_id = p_application_id
3186 AND a.event_class_code = p_event_class_code
3187 AND a.accounting_attribute_code = 'DISTRIBUTION_IDENTIFIER_5'
3188 AND a.default_flag = 'Y';
3189
3190 -- Get all accounting attributes assignments that have sources that are
3191 -- not mapped to the event class
3192
3193 CURSOR c_sources
3194 IS
3195 SELECT s.accounting_attribute_code,
3196 s.source_type_code, s.source_code
3197 FROM xla_evt_class_acct_attrs s
3198 WHERE s.application_id = p_application_id
3199 AND s.event_class_code = p_event_class_code
3200 AND s.source_application_id = p_application_id
3201 AND s.source_type_code = 'S'
3202 AND NOT EXISTS (SELECT 'x'
3203 FROM xla_event_sources e
3204 WHERE e.application_id = s.application_id
3205 AND e.event_class_code = s.event_class_code
3206 AND e.source_application_id = s.source_application_id
3207 AND e.source_type_code = s.source_type_code
3208 AND e.source_code = s.source_code
3209 AND e.active_flag = 'Y');
3210
3211 l_sources c_sources%rowtype;
3212
3213 -- Get all accounting attributes assignments that have derived sources that are
3214 -- not mapped to the event class
3215
3216 CURSOR c_der_sources
3217 IS
3218 SELECT s.accounting_attribute_code, s.source_application_id,
3219 s.source_type_code, s.source_code
3220 FROM xla_evt_class_acct_attrs s
3221 WHERE s.application_id = p_application_id
3222 AND s.event_class_code = p_event_class_code
3223 AND s.source_application_id = p_application_id
3224 AND s.source_type_code = 'D';
3225
3226 l_der_sources c_der_sources%rowtype;
3227
3228
3229
3230 BEGIN
3231
3232 l_application_id := p_application_id;
3233 l_entity_code := p_entity_code;
3234 l_event_class_code := p_event_class_code;
3235
3236 g_trace_label :='Validate_accounting_sources';
3237
3238 IF (g_log_level is NULL) THEN
3239 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3240 END IF;
3241
3242 IF (g_log_level is NULL) THEN
3243 g_log_enabled := fnd_log.test
3244 (log_level => g_log_level
3245 ,module => C_DEFAULT_MODULE);
3246 END IF;
3247
3248 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
3249 trace
3250 (p_msg => 'Begin'
3251 ,p_level => C_LEVEL_PROCEDURE);
3252 trace
3253 (p_msg => 'p_application_id = ' ||TO_CHAR(p_application_id)
3254 ,p_level => C_LEVEL_PROCEDURE);
3255 trace
3256 (p_msg => 'p_entity_code = '||p_entity_code
3257 ,p_level => C_LEVEL_PROCEDURE);
3258 trace
3259 (p_msg => 'p_event_class_code = ' ||p_event_class_code
3260 ,p_level => C_LEVEL_PROCEDURE);
3261 END IF;
3262
3263 -- Check if all required accounting sources are mapped for the event class
3264
3265 OPEN c_reqd_sources;
3266 LOOP
3267 FETCH c_reqd_sources
3268 INTO l_reqd_sources;
3269 EXIT WHEN c_reqd_sources%notfound;
3270
3271 Xla_amb_setup_err_pkg.stack_error
3272 (p_message_name => 'XLA_AB_REQD_ACCT_SOURCES'
3273 ,p_message_type => 'E'
3274 ,p_message_category => 'ACCOUNTING_SOURCE'
3275 ,p_category_sequence => 5
3276 ,p_application_id => l_application_id
3277 ,p_entity_code => l_entity_code
3278 ,p_event_class_code => l_event_class_code
3279 ,p_accounting_source_code => l_reqd_sources.accounting_attribute_code);
3280
3281 l_return := FALSE;
3282 END LOOP;
3283 CLOSE c_reqd_sources;
3284
3285 -- Get all mapping groups that have atleast one accounting source
3286 -- mapped for the event class
3287
3288 OPEN c_mapping_groups;
3289 LOOP
3290 FETCH c_mapping_groups
3291 INTO l_mapping_groups;
3292 EXIT WHEN c_mapping_groups%NOTFOUND;
3293
3294 -- Check if all required sources for the group are mapped for
3295 -- the event class
3296
3297 OPEN c_group_sources;
3298 LOOP
3299 FETCH c_group_sources
3300 INTO l_group_sources;
3301 EXIT WHEN c_group_sources%NOTFOUND;
3302 Xla_amb_setup_err_pkg.stack_error
3303 (p_message_name => 'XLA_AB_REQD_GRP_SOURCES'
3304 ,p_message_type => 'E'
3305 ,p_message_category => 'ACCOUNTING_SOURCE'
3306 ,p_category_sequence => 5
3307 ,p_application_id => l_application_id
3308 ,p_entity_code => l_entity_code
3309 ,p_event_class_code => l_event_class_code
3310 ,p_accounting_source_code => l_group_sources.accounting_attribute_code
3311 ,p_accounting_group_code => l_mapping_groups.assignment_group_code);
3312
3313 l_return := FALSE;
3314 END LOOP;
3315 CLOSE c_group_sources;
3316
3317 END LOOP;
3318 CLOSE c_mapping_groups;
3319
3320 -- Get budget and encumbrance flag for the event class
3321
3322 OPEN c_ec_attrs;
3323 FETCH c_ec_attrs
3324 INTO l_ec_attrs;
3325
3326 IF l_ec_attrs.allow_budgets_flag = 'Y' THEN
3327
3328 -- Check if Budget Version Identifier is mapped for the
3329 -- event class
3330 OPEN c_budget;
3331 FETCH c_budget
3332 INTO l_exist;
3333 IF c_budget%NOTFOUND THEN
3334 Xla_amb_setup_err_pkg.stack_error
3335 (p_message_name => 'XLA_AB_BUDGET_ACCTG_SRC'
3336 ,p_message_type => 'E'
3337 ,p_message_category => 'ACCOUNTING_SOURCE'
3338 ,p_category_sequence => 5
3339 ,p_application_id => l_application_id
3340 ,p_entity_code => l_entity_code
3341 ,p_event_class_code => l_event_class_code
3342 ,p_accounting_source_code => 'BUDGET_VERSION_ID');
3343
3344 l_return := FALSE;
3345 END IF;
3346 CLOSE c_budget;
3347 END IF;
3348
3349 /* 4458381
3350 IF l_ec_attrs.allow_encumbrance_flag = 'Y' THEN
3351
3352 -- Check if Encumbrance Type Identifier is mapped for the
3353 -- event class
3354 OPEN c_enc;
3355 FETCH c_enc
3356 INTO l_exist;
3357 IF c_enc%NOTFOUND THEN
3358 Xla_amb_setup_err_pkg.stack_error
3359 (p_message_name => 'XLA_AB_ENC_ACCTG_SRC'
3360 ,p_message_type => 'E'
3361 ,p_message_category => 'ACCOUNTING_SOURCE'
3362 ,p_category_sequence => 5
3363 ,p_application_id => l_application_id
3364 ,p_entity_code => l_entity_code
3365 ,p_event_class_code => l_event_class_code
3366 ,p_accounting_source_code => 'ENCUMBRANCE_TYPE_ID');
3367
3368 l_return := FALSE;
3369 END IF;
3370 CLOSE c_enc;
3371 END IF;
3372 CLOSE c_ec_attrs;
3373 */
3374
3375 --
3376 -- Check if reversed distribution ids are mapped for a line type
3377 -- then the corresponding distribution ids are also mapped
3378 --
3379 OPEN c_rev_dist_2;
3380 FETCH c_rev_dist_2
3381 INTO l_rev_dist_2;
3382 IF c_rev_dist_2%found THEN
3383
3384 OPEN c_dist_2;
3385 FETCH c_dist_2
3386 INTO l_exist;
3387 IF c_dist_2%notfound THEN
3388
3389 Xla_amb_setup_err_pkg.stack_error
3390 (p_message_name => 'XLA_AB_EC_ACCT_REV_DIST_ID'
3391 ,p_message_type => 'E'
3392 ,p_message_category => 'ACCOUNTING_SOURCE'
3393 ,p_category_sequence => 5
3394 ,p_application_id => l_application_id
3395 ,p_entity_code => l_entity_code
3396 ,p_event_class_code => l_event_class_code
3397 ,p_accounting_source_code => l_rev_dist_2.accounting_attribute_code
3398 ,p_accounting_group_code => l_rev_dist_2.assignment_group_code);
3399
3400 l_return := FALSE;
3401 END IF;
3402 CLOSE c_dist_2;
3403 END IF;
3404 CLOSE c_rev_dist_2;
3405
3406 OPEN c_rev_dist_3;
3407 FETCH c_rev_dist_3
3408 INTO l_rev_dist_3;
3409 IF c_rev_dist_3%found THEN
3410
3411 OPEN c_dist_3;
3412 FETCH c_dist_3
3413 INTO l_exist;
3414 IF c_dist_3%notfound THEN
3415
3416 Xla_amb_setup_err_pkg.stack_error
3417 (p_message_name => 'XLA_AB_EC_ACCT_REV_DIST_ID'
3418 ,p_message_type => 'E'
3419 ,p_message_category => 'ACCOUNTING_SOURCE'
3420 ,p_category_sequence => 5
3421 ,p_application_id => l_application_id
3422 ,p_entity_code => l_entity_code
3423 ,p_event_class_code => l_event_class_code
3424 ,p_accounting_source_code => l_rev_dist_3.accounting_attribute_code
3425 ,p_accounting_group_code => l_rev_dist_3.assignment_group_code);
3426
3427 l_return := FALSE;
3428 END IF;
3429 CLOSE c_dist_3;
3430 END IF;
3431 CLOSE c_rev_dist_3;
3432
3433 OPEN c_rev_dist_4;
3434 FETCH c_rev_dist_4
3435 INTO l_rev_dist_4;
3436 IF c_rev_dist_4%found THEN
3437
3438 OPEN c_dist_4;
3439 FETCH c_dist_4
3440 INTO l_exist;
3441 IF c_dist_4%notfound THEN
3442
3443 Xla_amb_setup_err_pkg.stack_error
3444 (p_message_name => 'XLA_AB_EC_ACCT_REV_DIST_ID'
3445 ,p_message_type => 'E'
3446 ,p_message_category => 'ACCOUNTING_SOURCE'
3447 ,p_category_sequence => 5
3448 ,p_application_id => l_application_id
3449 ,p_entity_code => l_entity_code
3450 ,p_event_class_code => l_event_class_code
3451 ,p_accounting_source_code => l_rev_dist_4.accounting_attribute_code
3452 ,p_accounting_group_code => l_rev_dist_4.assignment_group_code);
3453
3454 l_return := FALSE;
3455 END IF;
3456 CLOSE c_dist_4;
3457 END IF;
3458 CLOSE c_rev_dist_4;
3459
3460 OPEN c_rev_dist_5;
3461 FETCH c_rev_dist_5
3462 INTO l_rev_dist_5;
3463 IF c_rev_dist_5%found THEN
3464
3465 OPEN c_dist_5;
3466 FETCH c_dist_5
3467 INTO l_exist;
3468 IF c_dist_5%notfound THEN
3469
3470 Xla_amb_setup_err_pkg.stack_error
3471 (p_message_name => 'XLA_AB_EC_ACCT_REV_DIST_ID'
3472 ,p_message_type => 'E'
3473 ,p_message_category => 'ACCOUNTING_SOURCE'
3474 ,p_category_sequence => 5
3475 ,p_application_id => l_application_id
3476 ,p_entity_code => l_entity_code
3477 ,p_event_class_code => l_event_class_code
3478 ,p_accounting_source_code => l_rev_dist_5.accounting_attribute_code
3479 ,p_accounting_group_code => l_rev_dist_5.assignment_group_code);
3480
3481 l_return := FALSE;
3482 END IF;
3483 CLOSE c_dist_5;
3484 END IF;
3485 CLOSE c_rev_dist_5;
3486
3487 -- check accounting attribute assignments that have derived sources
3488 -- that do not belong to the event class
3489 OPEN c_sources;
3490 LOOP
3491 FETCH c_sources
3492 INTO l_sources;
3493 EXIT WHEN c_sources%notfound;
3494
3495 Xla_amb_setup_err_pkg.stack_error
3496 (p_message_name => 'XLA_AB_EC_ACCT_ATTR_SRCE'
3497 ,p_message_type => 'E'
3498 ,p_message_category => 'ACCOUNTING_SOURCE'
3499 ,p_category_sequence => 5
3500 ,p_application_id => l_application_id
3501 ,p_entity_code => l_entity_code
3502 ,p_event_class_code => l_event_class_code
3503 ,p_accounting_source_code => l_sources.accounting_attribute_code
3504 ,p_source_type_code => l_sources.source_type_code
3505 ,p_source_code => l_sources.source_code);
3506
3507 l_return := FALSE;
3508
3509 END LOOP;
3510 CLOSE c_sources;
3511
3512 -- check accounting attribute assignments that have derived sources
3513 -- that do not belong to the event class
3514 OPEN c_der_sources;
3515 LOOP
3516 FETCH c_der_sources
3517 INTO l_der_sources;
3518 EXIT WHEN c_der_sources%notfound;
3519
3520 IF xla_sources_pkg.derived_source_is_invalid
3521 (p_application_id => l_application_id
3522 ,p_derived_source_code => l_der_sources.source_code
3523 ,p_derived_source_type_code => 'D'
3524 ,p_entity_code => p_entity_code
3525 ,p_event_class_code => p_event_class_code
3526 ,p_level => 'L') = 'TRUE' THEN
3527
3528 Xla_amb_setup_err_pkg.stack_error
3529 (p_message_name => 'XLA_AB_EC_ACCT_ATTR_SRCE'
3530 ,p_message_type => 'E'
3531 ,p_message_category => 'ACCOUNTING_SOURCE'
3532 ,p_category_sequence => 5
3533 ,p_application_id => l_application_id
3534 ,p_entity_code => p_entity_code
3535 ,p_event_class_code => p_event_class_code
3536 ,p_accounting_source_code => l_der_sources.accounting_attribute_code
3537 ,p_source_type_code => l_der_sources.source_type_code
3538 ,p_source_code => l_der_sources.source_code);
3539
3540 l_return := FALSE;
3541 END IF;
3542 END LOOP;
3543 CLOSE c_der_sources;
3544
3545
3546 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
3547 trace
3548 (p_msg => 'End'
3549 ,p_level => C_LEVEL_PROCEDURE);
3550 END IF;
3551
3552 RETURN l_return;
3553
3554 EXCEPTION
3555 WHEN xla_exceptions_pkg.application_exception THEN
3556 RAISE;
3557 WHEN OTHERS THEN
3558 xla_exceptions_pkg.raise_message
3559 (p_location => 'xla_extract_integrity_pkg.validate_accounting_sources');
3560 END Validate_accounting_sources; -- end of function
3561
3562 /*======================================================================+
3563 | |
3564 | Private Function |
3565 | |
3566 | Create_sources |
3567 | |
3568 | This routine creates sources from the extract table definition |
3569 | |
3570 +======================================================================*/
3571 FUNCTION Create_sources
3572 (p_application_id IN NUMBER
3573 ,p_entity_code IN VARCHAR2
3574 ,p_event_class_code IN VARCHAR2)
3575 RETURN BOOLEAN
3576 IS
3577
3578 -- Array Declaration
3579 l_array_source_code t_array_codes;
3580 l_array_datatype_code t_array_type_codes;
3581 l_array_visible_flag t_array_type_codes;
3582 l_array_translated_flag t_array_type_codes;
3583 l_array_tl_source_code t_array_codes;
3584
3585 l_array_ref_source_appl_id t_array_id;
3586 l_array_ref_source_code t_array_codes;
3587 l_array_ref_datatype_code t_array_type_codes;
3588 l_array_ref_visible_flag t_array_type_codes;
3589 l_array_ref_translated_flag t_array_type_codes;
3590 l_array_ref_tl_source_appl_id t_array_id;
3591 l_array_ref_tl_source_code t_array_codes;
3592
3593 -- Variable Declaration
3594
3595 l_application_id NUMBER(15);
3596 l_entity_code VARCHAR2(30);
3597 l_event_class_code VARCHAR2(30);
3598 l_return BOOLEAN := TRUE;
3599 l_language_code VARCHAR2(4);
3600 l_column_name VARCHAR2(30);
3601
3602 dml_errors EXCEPTION;
3603 PRAGMA exception_init(dml_errors, -24381);
3604
3605
3606 CURSOR c_languages
3607 IS
3608 SELECT language_code
3609 FROM fnd_languages
3610 WHERE installed_flag in ('I','B');
3611 -- Added and commented for bug 15938563
3612 /*
3613 CURSOR c_mls
3614 IS
3615 SELECT distinct c.column_name
3616 FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
3617 WHERE c.table_name = e.object_name
3618 AND e.object_name = og.object_name
3619 AND og.owner = c.owner
3620 AND e.application_id = p_application_id
3621 AND e.entity_code = p_entity_code
3622 AND e.event_class_code = p_event_class_code
3623 AND e.object_type_code IN ('HEADER_MLS','LINE_MLS')
3624 AND c.data_type IN ('NUMBER','DATE')
3625 AND c.column_name NOT IN ('EVENT_ID','LINE_NUMBER','LEDGER_ID');
3626
3627 CURSOR c_sources
3628 IS
3629 SELECT distinct(c.column_name) source_code
3630 ,decode(c.data_type,'VARCHAR2','C','CHAR','C','NUMBER','N','DATE','D','C') data_type_code
3631 ,decode(c.column_name,'EVENT_ID','N','LINE_NUMBER','N','LEDGER_ID','N','LANGUAGE','N','Y') visible_flag
3632 ,CASE e.object_type_code
3633 WHEN 'HEADER' THEN 'N'
3634 WHEN 'LINE' THEN 'N'
3635 ELSE decode(c.data_type,'NUMBER','N','DATE','N', decode(c.column_name,'LANGUAGE','N','Y'))
3636 END translated_flag
3637 FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
3638 WHERE c.table_name = e.object_name
3639 AND e.object_name = og.object_name
3640 AND og.owner = c.owner
3641 --
3642 -- Bug 5120836
3643 -- Do not create the LANGUAGE column from non-MLS objects
3644 --
3645 AND DECODE(e.object_type_code
3646 ,'HEADER_MLS'
3647 ,'MLS_COLUMNS'
3648 ,'LINE_MLS'
3649 ,'MLS_COLUMNS'
3650 ,c.column_name) <> 'LANGUAGE'
3651 AND e.application_id = p_application_id
3652 AND e.entity_code = p_entity_code
3653 AND e.event_class_code = p_event_class_code
3654 AND NOT EXISTS (SELECT 'x'
3655 FROM xla_sources_b s
3656 WHERE s.application_id = e.application_id
3657 AND s.source_type_code = 'S'
3658 AND s.source_code = c.column_name);
3659
3660 CURSOR c_ref_sources
3661 IS
3662 SELECT DISTINCT r.reference_object_appl_id
3663 , c.column_name source_code
3664 ,decode(c.data_type,'VARCHAR2','C','CHAR','C','NUMBER','N','DATE','D','C') data_type_code
3665 ,decode(c.column_name,'EVENT_ID','N','LINE_NUMBER','N','LEDGER_ID','N','LANGUAGE','N','Y') visible_flag
3666 ,CASE e.object_type_code
3667 WHEN 'HEADER' THEN 'N'
3668 WHEN 'LINE' THEN 'N'
3669 ELSE decode(c.data_type,'NUMBER','N','DATE','N', decode(c.column_name,'LANGUAGE','N','Y'))
3670 END translated_flag
3671 FROM dba_tab_columns c, xla_reference_objects r,
3672 xla_reference_objects_gt og, xla_extract_objects e
3673 WHERE c.table_name = r.reference_object_name
3674 AND r.reference_object_name = og.reference_object_name
3675 AND og.owner = c.owner
3676 AND r.application_id = p_application_id
3677 AND r.entity_code = p_entity_code
3678 AND r.event_class_code = p_event_class_code
3679 AND e.application_id = p_application_id
3680 AND e.entity_code = p_entity_code
3681 AND e.event_class_code = p_event_class_code
3682 AND e.object_name = r.object_name
3683 --
3684 -- Bug 5120836
3685 -- Do not create the LANGUAGE column from non-MLS objects
3686 --
3687 AND DECODE(e.object_type_code
3688 ,'HEADER_MLS'
3689 ,'MLS_COLUMNS'
3690 ,'LINE_MLS'
3691 ,'MLS_COLUMNS'
3692 ,c.column_name) <> 'LANGUAGE'
3693 AND NOT EXISTS (SELECT 'x'
3694 FROM xla_sources_b s
3695 WHERE s.application_id = r.reference_object_appl_id
3696 AND s.source_type_code = 'S'
3697 AND s.source_code = c.column_name); */
3698 CURSOR c_mls
3699 IS
3700 SELECT distinct c.column_name
3701 FROM dba_tab_columns c,user_objects uo,xla_extract_objects e, xla_extract_objects_gt og
3702 WHERE c.table_name = e.object_name
3703 AND uo.object_name = e.object_name
3704 AND uo.object_type <> 'SYNONYM'
3705 AND e.object_name = og.object_name
3706 AND og.owner = c.owner
3707 AND e.application_id = p_application_id
3708 AND e.entity_code = p_entity_code
3709 AND e.event_class_code = p_event_class_code
3710 AND e.object_type_code IN ('HEADER_MLS','LINE_MLS')
3711 AND c.data_type IN ('NUMBER','DATE')
3712 AND c.column_name NOT IN ('EVENT_ID','LINE_NUMBER','LEDGER_ID')
3713 UNION ALL
3714 SELECT distinct c.column_name
3715 FROM dba_tab_columns c,user_objects uo,user_synonyms s,xla_extract_objects e, xla_extract_objects_gt og
3716 WHERE s.table_owner = c.owner
3717 AND s.table_name = c.table_name
3718 AND s.synonym_name = e.object_name
3719 AND uo.object_name = e.object_name
3720 AND uo.object_type = 'SYNONYM'
3721 AND e.object_name = og.object_name
3722 AND e.application_id = p_application_id
3723 AND e.entity_code = p_entity_code
3724 AND e.event_class_code = p_event_class_code
3725 AND e.object_type_code IN ('HEADER_MLS','LINE_MLS')
3726 AND c.data_type IN ('NUMBER','DATE')
3727 AND c.column_name NOT IN ('EVENT_ID','LINE_NUMBER','LEDGER_ID');
3728
3729
3730 CURSOR c_sources
3731 IS
3732 SELECT distinct(c.column_name) source_code
3733 ,decode(c.data_type,'VARCHAR2','C','CHAR','C','NUMBER','N','DATE','D','C') data_type_code
3734 ,decode(c.column_name,'EVENT_ID','N','LINE_NUMBER','N','LEDGER_ID','N','LANGUAGE','N','Y') visible_flag
3735 ,CASE e.object_type_code
3736 WHEN 'HEADER' THEN 'N'
3737 WHEN 'LINE' THEN 'N'
3738 ELSE decode(c.data_type,'NUMBER','N','DATE','N', decode(c.column_name,'LANGUAGE','N','Y'))
3739 END translated_flag
3740 FROM dba_tab_columns c,user_objects uo, xla_extract_objects e, xla_extract_objects_gt og
3741 WHERE c.table_name = e.object_name
3742 AND uo.object_name = e.object_name
3743 AND uo.object_type <> 'SYNONYM'
3744 AND e.object_name = og.object_name
3745 AND og.owner = c.owner
3746 --
3747 -- Bug 5120836
3748 -- Do not create the LANGUAGE column from non-MLS objects
3749 --
3750 AND DECODE(e.object_type_code
3751 ,'HEADER_MLS'
3752 ,'MLS_COLUMNS'
3753 ,'LINE_MLS'
3754 ,'MLS_COLUMNS'
3755 ,c.column_name) <> 'LANGUAGE'
3756 AND e.application_id = p_application_id
3757 AND e.entity_code = p_entity_code
3758 AND e.event_class_code = p_event_class_code
3759 AND NOT EXISTS (SELECT 'x'
3760 FROM xla_sources_b s
3761 WHERE s.application_id = e.application_id
3762 AND s.source_type_code = 'S'
3763 AND s.source_code = c.column_name)
3764 UNION ALL
3765 SELECT distinct(c.column_name) source_code
3766 ,decode(c.data_type,'VARCHAR2','C','CHAR','C','NUMBER','N','DATE','D','C') data_type_code
3767 ,decode(c.column_name,'EVENT_ID','N','LINE_NUMBER','N','LEDGER_ID','N','LANGUAGE','N','Y') visible_flag
3768 ,CASE e.object_type_code
3769 WHEN 'HEADER' THEN 'N'
3770 WHEN 'LINE' THEN 'N'
3771 ELSE decode(c.data_type,'NUMBER','N','DATE','N', decode(c.column_name,'LANGUAGE','N','Y'))
3772 END translated_flag
3773 FROM dba_tab_columns c,user_objects uo,user_synonyms s, xla_extract_objects e, xla_extract_objects_gt og
3774 WHERE s.table_owner = c.owner
3775 AND c.table_name = s.table_name
3776 AND uo.object_name = s.synonym_name
3777 AND uo.object_name =e.object_name
3778 AND uo.object_type = 'SYNONYM'
3779 AND e.object_name = og.object_name
3780 --
3781 -- Bug 5120836
3782 -- Do not create the LANGUAGE column from non-MLS objects
3783 --
3784 AND DECODE(e.object_type_code
3785 ,'HEADER_MLS'
3786 ,'MLS_COLUMNS'
3787 ,'LINE_MLS'
3788 ,'MLS_COLUMNS'
3789 ,c.column_name) <> 'LANGUAGE'
3790 AND e.application_id = p_application_id
3791 AND e.entity_code = p_entity_code
3792 AND e.event_class_code = p_event_class_code
3793 AND NOT EXISTS (SELECT 'x'
3794 FROM xla_sources_b s
3795 WHERE s.application_id = e.application_id
3796 AND s.source_type_code = 'S'
3797 AND s.source_code = c.column_name);
3798
3799 CURSOR c_ref_sources
3800 IS
3801 SELECT DISTINCT r.reference_object_appl_id
3802 , c.column_name source_code
3803 ,decode(c.data_type,'VARCHAR2','C','CHAR','C','NUMBER','N','DATE','D','C') data_type_code
3804 ,decode(c.column_name,'EVENT_ID','N','LINE_NUMBER','N','LEDGER_ID','N','LANGUAGE','N','Y') visible_flag
3805 ,CASE e.object_type_code
3806 WHEN 'HEADER' THEN 'N'
3807 WHEN 'LINE' THEN 'N'
3808 ELSE decode(c.data_type,'NUMBER','N','DATE','N', decode(c.column_name,'LANGUAGE','N','Y'))
3809 END translated_flag
3810 FROM dba_tab_columns c,user_objects uo, xla_reference_objects r,
3811 xla_reference_objects_gt og, xla_extract_objects e
3812 WHERE c.table_name = r.reference_object_name
3813 AND uo.object_name = r.reference_object_name
3814 AND uo.object_type <> 'SYNONYM'
3815 AND r.reference_object_name = og.reference_object_name
3816 AND og.owner = c.owner
3817 AND r.application_id = p_application_id
3818 AND r.entity_code = p_entity_code
3819 AND r.event_class_code = p_event_class_code
3820 AND e.application_id = p_application_id
3821 AND e.entity_code = p_entity_code
3822 AND e.event_class_code = p_event_class_code
3823 AND e.object_name = r.object_name
3824 --
3825 -- Bug 5120836
3826 -- Do not create the LANGUAGE column from non-MLS objects
3827 --
3828 AND DECODE(e.object_type_code
3829 ,'HEADER_MLS'
3830 ,'MLS_COLUMNS'
3831 ,'LINE_MLS'
3832 ,'MLS_COLUMNS'
3833 ,c.column_name) <> 'LANGUAGE'
3834 AND NOT EXISTS (SELECT 'x'
3835 FROM xla_sources_b s
3836 WHERE s.application_id = r.reference_object_appl_id
3837 AND s.source_type_code = 'S'
3838 AND s.source_code = c.column_name)
3839 UNION ALL
3840 SELECT DISTINCT r.reference_object_appl_id
3841 , c.column_name source_code
3842 ,decode(c.data_type,'VARCHAR2','C','CHAR','C','NUMBER','N','DATE','D','C') data_type_code
3843 ,decode(c.column_name,'EVENT_ID','N','LINE_NUMBER','N','LEDGER_ID','N','LANGUAGE','N','Y') visible_flag
3844 ,CASE e.object_type_code
3845 WHEN 'HEADER' THEN 'N'
3846 WHEN 'LINE' THEN 'N'
3847 ELSE decode(c.data_type,'NUMBER','N','DATE','N', decode(c.column_name,'LANGUAGE','N','Y'))
3848 END translated_flag
3849 FROM dba_tab_columns c,user_objects uo,user_synonyms s, xla_reference_objects r,
3850 xla_reference_objects_gt og, xla_extract_objects e
3851 WHERE s.table_owner = c.owner
3852 AND c.table_name = s.table_name
3853 AND uo.object_name = s.synonym_name
3854 AND uo.object_name = r.reference_object_name
3855 AND uo.object_type = 'SYNONYM'
3856 AND r.reference_object_name = og.reference_object_name
3857 AND r.application_id = p_application_id
3858 AND r.entity_code = p_entity_code
3859 AND r.event_class_code = p_event_class_code
3860 AND e.application_id = p_application_id
3861 AND e.entity_code = p_entity_code
3862 AND e.event_class_code = p_event_class_code
3863 AND e.object_name = r.object_name
3864 --
3865 -- Bug 5120836
3866 -- Do not create the LANGUAGE column from non-MLS objects
3867 --
3868 AND DECODE(e.object_type_code
3869 ,'HEADER_MLS'
3870 ,'MLS_COLUMNS'
3871 ,'LINE_MLS'
3872 ,'MLS_COLUMNS'
3873 ,c.column_name) <> 'LANGUAGE'
3874 AND NOT EXISTS (SELECT 'x'
3875 FROM xla_sources_b s
3876 WHERE s.application_id = r.reference_object_appl_id
3877 AND s.source_type_code = 'S'
3878 AND s.source_code = c.column_name);
3879
3880
3881 CURSOR c_tl_sources
3882 IS
3883 SELECT distinct source_code
3884 FROM xla_sources_b e
3885 WHERE e.application_id = p_application_id
3886 AND NOT EXISTS (SELECT 'x'
3887 FROM xla_sources_vl s
3888 WHERE s.application_id = e.application_id
3889 AND s.source_type_code = e.source_type_code
3890 AND s.source_code = e.source_code);
3891
3892 CURSOR c_ref_tl_sources
3893 IS
3894 SELECT distinct reference_object_appl_id, source_code
3895 FROM xla_sources_b e, xla_reference_objects r
3896 WHERE e.application_id = r.reference_object_appl_id
3897 AND r.application_id = p_application_id
3898 AND NOT EXISTS (SELECT 'x'
3899 FROM xla_sources_vl s
3900 WHERE s.application_id = r.reference_object_appl_id
3901 AND s.source_type_code = e.source_type_code
3902 AND s.source_code = e.source_code);
3903
3904 BEGIN
3905
3906 l_application_id := p_application_id;
3907 l_entity_code := p_entity_code;
3908 l_event_class_code := p_event_class_code;
3909
3910 g_trace_label :='Create_sources';
3911
3912 IF (g_creation_date is NULL) THEN
3913 g_creation_date := sysdate;
3914 END IF;
3915
3916 IF (g_last_update_date is NULL) THEN
3917 g_last_update_date := sysdate;
3918 END IF;
3919
3920 IF (g_created_by is NULL) THEN
3921 g_created_by := xla_environment_pkg.g_usr_id;
3922 END IF;
3923
3924 IF (g_last_update_login is NULL) THEN
3925 g_last_update_login := xla_environment_pkg.g_login_id;
3926 END IF;
3927
3928 IF (g_last_updated_by is NULL) THEN
3929 g_last_updated_by := xla_environment_pkg.g_usr_id;
3930 END IF;
3931
3932 IF (g_log_level is NULL) THEN
3933 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3934 END IF;
3935
3936 IF (g_log_level is NULL) THEN
3937 g_log_enabled := fnd_log.test
3938 (log_level => g_log_level
3939 ,module => C_DEFAULT_MODULE);
3940 END IF;
3941
3942 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
3943 trace
3944 (p_msg => 'Begin'
3945 ,p_level => C_LEVEL_PROCEDURE);
3946 trace
3947 (p_msg => 'p_application_id = ' ||TO_CHAR(p_application_id)
3948 ,p_level => C_LEVEL_PROCEDURE);
3949 trace
3950 (p_msg => 'p_entity_code = '||p_entity_code
3951 ,p_level => C_LEVEL_PROCEDURE);
3952 trace
3953 (p_msg => 'p_event_class_code = ' ||p_event_class_code
3954 ,p_level => C_LEVEL_PROCEDURE);
3955 END IF;
3956
3957 -- Error the columns that are not varchar2 and exist in MLS tables
3958 OPEN c_mls;
3959 LOOP
3960 FETCH c_mls
3961 INTO l_column_name;
3962 EXIT WHEN c_mls%notfound;
3963
3964 Xla_amb_setup_err_pkg.stack_error
3965 (p_message_name => 'XLA_AB_NUMBER_COL_IN_MLS'
3966 ,p_message_type => 'W'
3967 ,p_message_category => 'CREATE_SOURCE'
3968 ,p_category_sequence => 15
3969 ,p_application_id => l_application_id
3970 ,p_entity_code => l_entity_code
3971 ,p_event_class_code => l_event_class_code
3972 ,p_extract_column_name => l_column_name);
3973 l_return := FALSE;
3974
3975 END LOOP;
3976 CLOSE c_mls;
3977
3978 OPEN c_sources;
3979 FETCH c_sources
3980 BULK COLLECT INTO l_array_source_code, l_array_datatype_code, l_array_visible_flag, l_array_translated_flag;
3981
3982 -- Create sources in source_b table for all extract objects
3983 IF l_array_source_code.COUNT > 0 THEN
3984 BEGIN
3985 FORALL i IN l_array_source_code.FIRST..l_array_source_code.LAST SAVE EXCEPTIONS
3986 INSERT INTO xla_sources_b
3987 (source_code
3988 ,application_id
3989 ,source_type_code
3990 ,datatype_code
3991 ,sum_flag
3992 ,visible_flag
3993 ,enabled_flag
3994 ,creation_date
3995 ,created_by
3996 ,last_updated_by
3997 ,last_update_date
3998 ,last_update_login
3999 ,translated_flag
4000 ,key_flexfield_flag)
4001 VALUES
4002 (l_array_source_code(i)
4003 ,p_application_id
4004 ,'S'
4005 ,l_array_datatype_code(i)
4006 ,'N'
4007 ,l_array_visible_flag(i)
4008 ,'Y'
4009 ,g_creation_date
4010 ,g_created_by
4011 ,g_last_updated_by
4012 ,g_last_update_date
4013 ,g_last_update_login
4014 ,l_array_translated_flag(i)
4015 ,'N');
4016
4017 EXCEPTION
4018 WHEN dml_errors THEN
4019
4020 FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
4021 Xla_amb_setup_err_pkg.stack_error
4022 (p_message_name => 'XLA_AB_SAME_COL_DIFF_DATATYPE'
4023 ,p_message_type => 'W'
4024 ,p_message_category => 'CREATE_SOURCE'
4025 ,p_category_sequence => 15
4026 ,p_application_id => l_application_id
4027 ,p_entity_code => l_entity_code
4028 ,p_event_class_code => l_event_class_code
4029 ,p_extract_column_name => l_array_source_code(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX));
4030 END LOOP;
4031
4032 l_return := FALSE;
4033
4034 END;
4035 END IF;
4036 CLOSE c_sources;
4037
4038 OPEN c_ref_sources;
4039 FETCH c_ref_sources
4040 BULK COLLECT INTO l_array_ref_source_appl_id,
4041 l_array_ref_source_code, l_array_ref_datatype_code,
4042 l_array_ref_visible_flag, l_array_ref_translated_flag;
4043
4044 -- Create sources in source_b table for all reference objects
4045 IF l_array_ref_source_code.COUNT > 0 THEN
4046 BEGIN
4047 FORALL i IN l_array_ref_source_code.FIRST..l_array_ref_source_code.LAST SAVE EXCEPTIONS
4048 INSERT INTO xla_sources_b
4049 (source_code
4050 ,application_id
4051 ,source_type_code
4052 ,datatype_code
4053 ,sum_flag
4054 ,visible_flag
4055 ,enabled_flag
4056 ,key_flexfield_flag
4057 ,creation_date
4058 ,created_by
4059 ,last_updated_by
4060 ,last_update_date
4061 ,last_update_login
4062 ,translated_flag)
4063 VALUES
4064 (l_array_ref_source_code(i)
4065 ,l_array_ref_source_appl_id(i)
4066 ,'S'
4067 ,l_array_ref_datatype_code(i)
4068 ,'N'
4069 ,l_array_ref_visible_flag(i)
4070 ,'Y'
4071 ,'N'
4072 ,g_creation_date
4073 ,g_created_by
4074 ,g_last_updated_by
4075 ,g_last_update_date
4076 ,g_last_update_login
4077 ,l_array_ref_translated_flag(i));
4078
4079
4080 EXCEPTION
4081 WHEN dml_errors THEN
4082 FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
4083 Xla_amb_setup_err_pkg.stack_error
4084 (p_message_name => 'XLA_AB_SAME_COL_DIFF_DATATYPE'
4085 ,p_message_type => 'W'
4086 ,p_message_category => 'CREATE_SOURCE'
4087 ,p_category_sequence => 15
4088 ,p_application_id => l_application_id
4089 ,p_entity_code => l_entity_code
4090 ,p_event_class_code => l_event_class_code
4091 ,p_extract_column_name => l_array_ref_source_code(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX));
4092 END LOOP;
4093
4094 l_return := FALSE;
4095
4096 END;
4097 END IF;
4098 CLOSE c_ref_sources;
4099
4100 -- Get all sources that exist in xla_sources_b but not in xla_sources_tl
4101 OPEN c_tl_sources;
4102 FETCH c_tl_sources
4103 BULK COLLECT INTO l_array_tl_source_code;
4104
4105 IF l_array_tl_source_code.COUNT > 0 THEN
4106 -- Insert into sources_tl for all languages installed with same code and name
4107
4108 OPEN c_languages;
4109 LOOP
4110 FETCH c_languages
4111 INTO l_language_code;
4112 EXIT WHEN c_languages%notfound;
4113
4114 BEGIN
4115 FORALL i IN l_array_tl_source_code.FIRST..l_array_tl_source_code.LAST SAVE EXCEPTIONS
4116 INSERT INTO xla_sources_tl
4117 (source_code
4118 ,application_id
4119 ,source_type_code
4120 ,name
4121 ,language
4122 ,source_lang
4123 ,creation_date
4124 ,created_by
4125 ,last_updated_by
4126 ,last_update_date
4127 ,last_update_login)
4128 VALUES
4129 (l_array_tl_source_code(i)
4130 ,p_application_id
4131 ,'S'
4132 ,l_array_tl_source_code(i)
4133 ,l_language_code
4134 ,USERENV('LANG')
4135 ,g_creation_date
4136 ,g_created_by
4137 ,g_last_updated_by
4138 ,g_last_update_date
4139 ,g_last_update_login);
4140
4141 EXCEPTION
4142 WHEN dml_errors THEN
4143
4144 FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
4145 Xla_amb_setup_err_pkg.stack_error
4146 (p_message_name => 'XLA_AB_SAME_NAME_DIFF_CODE'
4147 ,p_message_type => 'E'
4148 ,p_message_category => 'CREATE_SOURCE'
4149 ,p_category_sequence => 15
4150 ,p_application_id => l_application_id
4151 ,p_entity_code => l_entity_code
4152 ,p_event_class_code => l_event_class_code
4153 ,p_extract_column_name => l_array_tl_source_code(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX)
4154 ,p_language => l_language_code);
4155 END LOOP;
4156
4157 l_return := FALSE;
4158 END;
4159 END LOOP;
4160 CLOSE c_languages;
4161 END IF;
4162 CLOSE c_tl_sources;
4163
4164 -- Get all sources that exist in xla_sources_b but not in xla_sources_tl
4165 OPEN c_ref_tl_sources;
4166 FETCH c_ref_tl_sources
4167 BULK COLLECT INTO l_array_ref_tl_source_appl_id, l_array_ref_tl_source_code;
4168
4169 IF l_array_ref_tl_source_code.COUNT > 0 THEN
4170 -- Insert into sources_tl for all languages installed with same code and name
4171
4172 OPEN c_languages;
4173 LOOP
4174 FETCH c_languages
4175 INTO l_language_code;
4176 EXIT WHEN c_languages%notfound;
4177
4178 BEGIN
4179 FORALL i IN l_array_ref_tl_source_code.FIRST..l_array_ref_tl_source_code.LAST SAVE EXCEPTIONS
4180 INSERT INTO xla_sources_tl
4181 (source_code
4182 ,application_id
4183 ,source_type_code
4184 ,name
4185 ,language
4186 ,source_lang
4187 ,creation_date
4188 ,created_by
4189 ,last_updated_by
4190 ,last_update_date
4191 ,last_update_login)
4192 VALUES
4193 (l_array_ref_tl_source_code(i)
4194 ,l_array_ref_tl_source_appl_id(i)
4195 ,'S'
4196 ,l_array_ref_tl_source_code(i)
4197 ,l_language_code
4198 ,USERENV('LANG')
4199 ,g_creation_date
4200 ,g_created_by
4201 ,g_last_updated_by
4202 ,g_last_update_date
4203 ,g_last_update_login);
4204
4205 EXCEPTION
4206 WHEN dml_errors THEN
4207
4208 FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
4209 Xla_amb_setup_err_pkg.stack_error
4210 (p_message_name => 'XLA_AB_SAME_NAME_DIFF_CODE'
4211 ,p_message_type => 'E'
4212 ,p_message_category => 'CREATE_SOURCE'
4213 ,p_category_sequence => 15
4214 ,p_application_id => l_application_id
4215 ,p_entity_code => l_entity_code
4216 ,p_event_class_code => l_event_class_code
4217 ,p_extract_column_name => l_array_ref_tl_source_code(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX)
4218 ,p_language => l_language_code);
4219 END LOOP;
4220
4221 l_return := FALSE;
4222 END;
4223 END LOOP;
4224 CLOSE c_languages;
4225 END IF;
4226 CLOSE c_ref_tl_sources;
4227
4228 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
4229 trace
4230 (p_msg => 'End'
4231 ,p_level => C_LEVEL_PROCEDURE);
4232 END IF;
4233
4234 RETURN l_return;
4235
4236 EXCEPTION
4237 WHEN xla_exceptions_pkg.application_exception THEN
4238 RAISE;
4239 WHEN OTHERS THEN
4240 xla_exceptions_pkg.raise_message
4241 (p_location => 'xla_extract_integrity_pkg.Create_sources');
4242 END Create_sources; -- end of procedure
4243
4244 /*======================================================================+
4245 | |
4246 | Private Procedure |
4247 | |
4248 | Assign_Sources |
4249 | |
4250 | This routine assigns sources from the extract table definition to the |
4251 | event class based on extract object level |
4252 | |
4253 +======================================================================*/
4254 PROCEDURE Assign_sources
4255 (p_application_id IN NUMBER
4256 ,p_entity_code IN VARCHAR2
4257 ,p_event_class_code IN VARCHAR2)
4258 IS
4259
4260 BEGIN
4261 g_trace_label :='Assign_sources';
4262
4263 IF (g_creation_date is NULL) THEN
4264 g_creation_date := sysdate;
4265 END IF;
4266
4267 IF (g_last_update_date is NULL) THEN
4268 g_last_update_date := sysdate;
4269 END IF;
4270
4271 IF (g_created_by is NULL) THEN
4272 g_created_by := xla_environment_pkg.g_usr_id;
4273 END IF;
4274
4275 IF (g_last_update_login is NULL) THEN
4276 g_last_update_login := xla_environment_pkg.g_login_id;
4277 END IF;
4278
4279 IF (g_last_updated_by is NULL) THEN
4280 g_last_updated_by := xla_environment_pkg.g_usr_id;
4281 END IF;
4282
4283 IF (g_log_level is NULL) THEN
4284 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4285 END IF;
4286
4287 IF (g_log_level is NULL) THEN
4288 g_log_enabled := fnd_log.test
4289 (log_level => g_log_level
4290 ,module => C_DEFAULT_MODULE);
4291 END IF;
4292
4293
4294 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
4295 trace
4296 (p_msg => 'Begin'
4297 ,p_level => C_LEVEL_PROCEDURE);
4298 trace
4299 (p_msg => 'p_application_id = ' ||TO_CHAR(p_application_id)
4300 ,p_level => C_LEVEL_PROCEDURE);
4301 trace
4302 (p_msg => 'p_entity_code = '||p_entity_code
4303 ,p_level => C_LEVEL_PROCEDURE);
4304 trace
4305 (p_msg => 'p_event_class_code = ' ||p_event_class_code
4306 ,p_level => C_LEVEL_PROCEDURE);
4307 END IF;
4308 -- Sources are assigned at the highest level they are
4309 -- available in an always populated extract object
4310
4311 -- Assign sources at header level to the event class
4312 -- for header extract objects that are always populated
4313 -- Added and commented for bug 15938563
4314 /*
4315 INSERT INTO xla_event_sources
4316 (source_code
4317 ,application_id
4318 ,entity_code
4319 ,event_class_code
4320 ,source_application_id
4321 ,source_type_code
4322 ,active_flag
4323 ,level_code
4324 ,creation_date
4325 ,created_by
4326 ,last_updated_by
4327 ,last_update_date
4328 ,last_update_login)
4329 (SELECT distinct (c.column_name)
4330 ,p_application_id
4331 ,p_entity_code
4332 ,p_event_class_code
4333 ,p_application_id
4334 ,'S'
4335 ,'Y'
4336 ,'H'
4337 ,g_creation_date
4338 ,g_created_by
4339 ,g_last_updated_by
4340 ,g_last_update_date
4341 ,g_last_update_login
4342 FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
4343 WHERE c.table_name = e.object_name
4344 AND og.object_name = e.object_name
4345 AND og.owner = c.owner
4346 AND e.object_type_code IN ('HEADER','HEADER_MLS')
4347 AND e.application_id = p_application_id
4348 AND e.entity_code = p_entity_code
4349 AND e.event_class_code = p_event_class_code
4350 AND e.always_populated_flag = 'Y'
4351 AND NOT EXISTS (SELECT 'x'
4352 FROM xla_event_sources s
4353 WHERE s.application_id = p_application_id
4354 AND s.entity_code = p_entity_code
4355 AND s.event_class_code = p_event_class_code
4356 AND s.source_application_id = p_application_id
4357 AND s.source_code = c.column_name));
4358
4359 -- Assign sources at header level to the event class
4360 -- for header reference objects that are always populated
4361 INSERT INTO xla_event_sources
4362 (source_code
4363 ,application_id
4364 ,entity_code
4365 ,event_class_code
4366 ,source_application_id
4367 ,source_type_code
4368 ,active_flag
4369 ,level_code
4370 ,creation_date
4371 ,created_by
4372 ,last_updated_by
4373 ,last_update_date
4374 ,last_update_login)
4375 (SELECT distinct (c.column_name)
4376 ,p_application_id
4377 ,p_entity_code
4378 ,p_event_class_code
4379 ,r.reference_object_appl_id
4380 ,'S'
4381 ,'Y'
4382 ,'H'
4383 ,g_creation_date
4384 ,g_created_by
4385 ,g_last_updated_by
4386 ,g_last_update_date
4387 ,g_last_update_login
4388 FROM dba_tab_columns c, xla_reference_objects r,
4389 xla_reference_objects_gt og, xla_extract_objects e
4390 WHERE c.table_name = r.reference_object_name
4391 AND og.reference_object_name = r.reference_object_name
4392 AND og.owner = c.owner
4393 AND e.application_id = p_application_id
4394 AND e.entity_code = p_entity_code
4395 AND e.event_class_code = p_event_class_code
4396 AND e.object_name = r.object_name
4397 AND e.object_type_code IN ('HEADER','HEADER_MLS')
4398 AND r.application_id = p_application_id
4399 AND r.entity_code = p_entity_code
4400 AND r.event_class_code = p_event_class_code
4401 AND r.always_populated_flag = 'Y'
4402 AND NOT EXISTS (SELECT 'x'
4403 FROM xla_event_sources s
4404 WHERE s.application_id = p_application_id
4405 AND s.entity_code = p_entity_code
4406 AND s.event_class_code = p_event_class_code
4407 AND s.source_application_id = r.reference_object_appl_id
4408 AND s.source_code = c.column_name));
4409
4410 -- Assign sources at line level to the event class
4411 -- for line extract objects that are always populated
4412 INSERT INTO xla_event_sources
4413 (source_code
4414 ,application_id
4415 ,entity_code
4416 ,event_class_code
4417 ,source_application_id
4418 ,source_type_code
4419 ,active_flag
4420 ,level_code
4421 ,creation_date
4422 ,created_by
4423 ,last_updated_by
4424 ,last_update_date
4425 ,last_update_login)
4426 (SELECT distinct (c.column_name)
4427 ,p_application_id
4428 ,p_entity_code
4429 ,p_event_class_code
4430 ,p_application_id
4431 ,'S'
4432 ,'Y'
4433 ,'L'
4434 ,g_creation_date
4435 ,g_created_by
4436 ,g_last_updated_by
4437 ,g_last_update_date
4438 ,g_last_update_login
4439 FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
4440 WHERE c.table_name = e.object_name
4441 AND og.object_name = e.object_name
4442 AND og.owner = c.owner
4443 AND e.object_type_code IN ('LINE','LINE_MLS')
4444 AND e.application_id = p_application_id
4445 AND e.entity_code = p_entity_code
4446 AND e.event_class_code = p_event_class_code
4447 AND e.always_populated_flag = 'Y'
4448 AND NOT EXISTS (SELECT 'x'
4449 FROM xla_event_sources s
4450 WHERE s.application_id = p_application_id
4451 AND s.entity_code = p_entity_code
4452 AND s.event_class_code = p_event_class_code
4453 AND s.source_application_id = p_application_id
4454 AND s.source_code = c.column_name));
4455
4456 -- Assign sources at line level to the event class
4457 -- for line reference objects that are always populated
4458 INSERT INTO xla_event_sources
4459 (source_code
4460 ,application_id
4461 ,entity_code
4462 ,event_class_code
4463 ,source_application_id
4464 ,source_type_code
4465 ,active_flag
4466 ,level_code
4467 ,creation_date
4468 ,created_by
4469 ,last_updated_by
4470 ,last_update_date
4471 ,last_update_login)
4472 (SELECT distinct (c.column_name)
4473 ,p_application_id
4474 ,p_entity_code
4475 ,p_event_class_code
4476 ,r.reference_object_appl_id
4477 ,'S'
4478 ,'Y'
4479 ,'L'
4480 ,g_creation_date
4481 ,g_created_by
4482 ,g_last_updated_by
4483 ,g_last_update_date
4484 ,g_last_update_login
4485 FROM dba_tab_columns c, xla_reference_objects r,
4486 xla_reference_objects_gt og, xla_extract_objects e
4487 WHERE c.table_name = r.reference_object_name
4488 AND og.reference_object_name = r.reference_object_name
4489 AND og.owner = c.owner
4490 AND e.application_id = p_application_id
4491 AND e.entity_code = p_entity_code
4492 AND e.event_class_code = p_event_class_code
4493 AND e.object_name = r.object_name
4494 AND e.object_type_code IN ('LINE','LINE_MLS')
4495 AND r.application_id = p_application_id
4496 AND r.entity_code = p_entity_code
4497 AND r.event_class_code = p_event_class_code
4498 AND r.always_populated_flag = 'Y'
4499 AND NOT EXISTS (SELECT 'x'
4500 FROM xla_event_sources s
4501 WHERE s.application_id = p_application_id
4502 AND s.entity_code = p_entity_code
4503 AND s.event_class_code = p_event_class_code
4504 AND s.source_application_id = r.reference_object_appl_id
4505 AND s.source_code = c.column_name));
4506
4507 -- Assign sources at header level to the event class
4508 -- for header extract objects that are not always populated
4509 INSERT INTO xla_event_sources
4510 (source_code
4511 ,application_id
4512 ,entity_code
4513 ,event_class_code
4514 ,source_application_id
4515 ,source_type_code
4516 ,active_flag
4517 ,level_code
4518 ,creation_date
4519 ,created_by
4520 ,last_updated_by
4521 ,last_update_date
4522 ,last_update_login)
4523 (SELECT distinct (c.column_name)
4524 ,p_application_id
4525 ,p_entity_code
4526 ,p_event_class_code
4527 ,p_application_id
4528 ,'S'
4529 ,'Y'
4530 ,'H'
4531 ,g_creation_date
4532 ,g_created_by
4533 ,g_last_updated_by
4534 ,g_last_update_date
4535 ,g_last_update_login
4536 FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
4537 WHERE c.table_name = e.object_name
4538 AND og.object_name = e.object_name
4539 AND og.owner = c.owner
4540 AND e.object_type_code IN ('HEADER','HEADER_MLS')
4541 AND e.application_id = p_application_id
4542 AND e.entity_code = p_entity_code
4543 AND e.event_class_code = p_event_class_code
4544 AND e.always_populated_flag = 'N'
4545 AND NOT EXISTS (SELECT 'x'
4546 FROM xla_event_sources s
4547 WHERE s.application_id = p_application_id
4548 AND s.entity_code = p_entity_code
4549 AND s.event_class_code = p_event_class_code
4550 AND s.source_application_id = p_application_id
4551 AND s.source_code = c.column_name));
4552
4553 -- Assign sources at header level to the event class
4554 -- for header reference objects that are not always populated
4555 INSERT INTO xla_event_sources
4556 (source_code
4557 ,application_id
4558 ,entity_code
4559 ,event_class_code
4560 ,source_application_id
4561 ,source_type_code
4562 ,active_flag
4563 ,level_code
4564 ,creation_date
4565 ,created_by
4566 ,last_updated_by
4567 ,last_update_date
4568 ,last_update_login)
4569 (SELECT distinct (c.column_name)
4570 ,p_application_id
4571 ,p_entity_code
4572 ,p_event_class_code
4573 ,r.reference_object_appl_id
4574 ,'S'
4575 ,'Y'
4576 ,'H'
4577 ,g_creation_date
4578 ,g_created_by
4579 ,g_last_updated_by
4580 ,g_last_update_date
4581 ,g_last_update_login
4582 FROM dba_tab_columns c, xla_reference_objects r,
4583 xla_reference_objects_gt og, xla_extract_objects e
4584 WHERE c.table_name = r.reference_object_name
4585 AND og.reference_object_name = r.reference_object_name
4586 AND og.owner = c.owner
4587 AND e.application_id = p_application_id
4588 AND e.entity_code = p_entity_code
4589 AND e.event_class_code = p_event_class_code
4590 AND e.object_name = r.object_name
4591 AND e.object_type_code IN ('HEADER','HEADER_MLS')
4592 AND r.application_id = p_application_id
4593 AND r.entity_code = p_entity_code
4594 AND r.event_class_code = p_event_class_code
4595 AND r.always_populated_flag = 'N'
4596 AND NOT EXISTS (SELECT 'x'
4597 FROM xla_event_sources s
4598 WHERE s.application_id = p_application_id
4599 AND s.entity_code = p_entity_code
4600 AND s.event_class_code = p_event_class_code
4601 AND s.source_application_id = r.reference_object_appl_id
4602 AND s.source_code = c.column_name));
4603
4604 -- Assign sources at line level to the event class
4605 -- for line extract objects that are not always populated
4606 INSERT INTO xla_event_sources
4607 (source_code
4608 ,application_id
4609 ,entity_code
4610 ,event_class_code
4611 ,source_application_id
4612 ,source_type_code
4613 ,active_flag
4614 ,level_code
4615 ,creation_date
4616 ,created_by
4617 ,last_updated_by
4618 ,last_update_date
4619 ,last_update_login)
4620 (SELECT distinct (c.column_name)
4621 ,p_application_id
4622 ,p_entity_code
4623 ,p_event_class_code
4624 ,p_application_id
4625 ,'S'
4626 ,'Y'
4627 ,'L'
4628 ,g_creation_date
4629 ,g_created_by
4630 ,g_last_updated_by
4631 ,g_last_update_date
4632 ,g_last_update_login
4633 FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
4634 WHERE c.table_name = e.object_name
4635 AND og.object_name = e.object_name
4636 AND og.owner = c.owner
4637 AND e.object_type_code IN ('LINE','LINE_MLS')
4638 AND e.application_id = p_application_id
4639 AND e.entity_code = p_entity_code
4640 AND e.event_class_code = p_event_class_code
4641 AND e.always_populated_flag = 'N'
4642 AND NOT EXISTS (SELECT 'x'
4643 FROM xla_event_sources s
4644 WHERE s.application_id = p_application_id
4645 AND s.entity_code = p_entity_code
4646 AND s.event_class_code = p_event_class_code
4647 AND s.source_application_id = p_application_id
4648 AND s.source_code = c.column_name));
4649
4650 -- Assign sources at line level to the event class
4651 -- for line extract objects that are not always populated
4652 INSERT INTO xla_event_sources
4653 (source_code
4654 ,application_id
4655 ,entity_code
4656 ,event_class_code
4657 ,source_application_id
4658 ,source_type_code
4659 ,active_flag
4660 ,level_code
4661 ,creation_date
4662 ,created_by
4663 ,last_updated_by
4664 ,last_update_date
4665 ,last_update_login)
4666 (SELECT distinct (c.column_name)
4667 ,p_application_id
4668 ,p_entity_code
4669 ,p_event_class_code
4670 ,r.reference_object_appl_id
4671 ,'S'
4672 ,'Y'
4673 ,'L'
4674 ,g_creation_date
4675 ,g_created_by
4676 ,g_last_updated_by
4677 ,g_last_update_date
4678 ,g_last_update_login
4679 FROM dba_tab_columns c, xla_reference_objects r,
4680 xla_reference_objects_gt og, xla_extract_objects e
4681 WHERE c.table_name = r.reference_object_name
4682 AND og.reference_object_name = r.reference_object_name
4683 AND og.owner = c.owner
4684 AND e.application_id = p_application_id
4685 AND e.entity_code = p_entity_code
4686 AND e.event_class_code = p_event_class_code
4687 AND e.object_name = r.object_name
4688 AND e.object_type_code IN ('LINE','LINE_MLS')
4689 AND r.application_id = p_application_id
4690 AND r.entity_code = p_entity_code
4691 AND r.event_class_code = p_event_class_code
4692 AND r.always_populated_flag = 'N'
4693 AND NOT EXISTS (SELECT 'x'
4694 FROM xla_event_sources s
4695 WHERE s.application_id = p_application_id
4696 AND s.entity_code = p_entity_code
4697 AND s.event_class_code = p_event_class_code
4698 AND s.source_application_id = r.reference_object_appl_id
4699 AND s.source_code = c.column_name)); */
4700 -- Assign sources at header level to the event class
4701 -- for header extract objects that are always populated
4702 INSERT INTO xla_event_sources
4703 (source_code
4704 ,application_id
4705 ,entity_code
4706 ,event_class_code
4707 ,source_application_id
4708 ,source_type_code
4709 ,active_flag
4710 ,level_code
4711 ,creation_date
4712 ,created_by
4713 ,last_updated_by
4714 ,last_update_date
4715 ,last_update_login)
4716 (SELECT distinct (c.column_name)
4717 ,p_application_id
4718 ,p_entity_code
4719 ,p_event_class_code
4720 ,p_application_id
4721 ,'S'
4722 ,'Y'
4723 ,'H'
4724 ,g_creation_date
4725 ,g_created_by
4726 ,g_last_updated_by
4727 ,g_last_update_date
4728 ,g_last_update_login
4729 FROM dba_tab_columns c,user_objects uo, xla_extract_objects e, xla_extract_objects_gt og
4730 WHERE c.table_name = e.object_name
4731 AND uo.object_name = e.object_name
4732 AND uo.object_type <> 'SYNONYM'
4733 AND og.object_name = e.object_name
4734 AND og.owner = c.owner
4735 AND e.object_type_code IN ('HEADER','HEADER_MLS')
4736 AND e.application_id = p_application_id
4737 AND e.entity_code = p_entity_code
4738 AND e.event_class_code = p_event_class_code
4739 AND e.always_populated_flag = 'Y'
4740 AND NOT EXISTS (SELECT 'x'
4741 FROM xla_event_sources s
4742 WHERE s.application_id = p_application_id
4743 AND s.entity_code = p_entity_code
4744 AND s.event_class_code = p_event_class_code
4745 AND s.source_application_id = p_application_id
4746 AND s.source_code = c.column_name)
4747 UNION ALL
4748 SELECT distinct (c.column_name)
4749 ,p_application_id
4750 ,p_entity_code
4751 ,p_event_class_code
4752 ,p_application_id
4753 ,'S'
4754 ,'Y'
4755 ,'H'
4756 ,g_creation_date
4757 ,g_created_by
4758 ,g_last_updated_by
4759 ,g_last_update_date
4760 ,g_last_update_login
4761 FROM dba_tab_columns c,user_objects uo,user_synonyms s, xla_extract_objects e, xla_extract_objects_gt og
4762 WHERE s.table_owner = c.owner
4763 AND c.table_name = s.table_name
4764 AND uo.object_name = s.synonym_name
4765 AND uo.object_name = e.object_name
4766 AND uo.object_type = 'SYNONYM'
4767 AND og.object_name = e.object_name
4768 --AND og.owner = c.owner
4769 AND e.object_type_code IN ('HEADER','HEADER_MLS')
4770 AND e.application_id = p_application_id
4771 AND e.entity_code = p_entity_code
4772 AND e.event_class_code = p_event_class_code
4773 AND e.always_populated_flag = 'Y'
4774 AND NOT EXISTS (SELECT 'x'
4775 FROM xla_event_sources s
4776 WHERE s.application_id = p_application_id
4777 AND s.entity_code = p_entity_code
4778 AND s.event_class_code = p_event_class_code
4779 AND s.source_application_id = p_application_id
4780 AND s.source_code = c.column_name));
4781
4782 -- Assign sources at header level to the event class
4783 -- for header reference objects that are always populated
4784 INSERT INTO xla_event_sources
4785 (source_code
4786 ,application_id
4787 ,entity_code
4788 ,event_class_code
4789 ,source_application_id
4790 ,source_type_code
4791 ,active_flag
4792 ,level_code
4793 ,creation_date
4794 ,created_by
4795 ,last_updated_by
4796 ,last_update_date
4797 ,last_update_login)
4798 (SELECT distinct (c.column_name)
4799 ,p_application_id
4800 ,p_entity_code
4801 ,p_event_class_code
4802 ,r.reference_object_appl_id
4803 ,'S'
4804 ,'Y'
4805 ,'H'
4806 ,g_creation_date
4807 ,g_created_by
4808 ,g_last_updated_by
4809 ,g_last_update_date
4810 ,g_last_update_login
4811 FROM dba_tab_columns c, xla_reference_objects r,user_objects uo,
4812 xla_reference_objects_gt og, xla_extract_objects e
4813 WHERE c.table_name = r.reference_object_name
4814 AND og.reference_object_name = r.reference_object_name
4815 AND og.owner = c.owner
4816 AND uo.object_name = r.reference_object_name
4817 AND uo.object_type <> 'SYNONYM'
4818 AND e.application_id = p_application_id
4819 AND e.entity_code = p_entity_code
4820 AND e.event_class_code = p_event_class_code
4821 AND e.object_name = r.object_name
4822 AND e.object_type_code IN ('HEADER','HEADER_MLS')
4823 AND r.application_id = p_application_id
4824 AND r.entity_code = p_entity_code
4825 AND r.event_class_code = p_event_class_code
4826 AND r.always_populated_flag = 'Y'
4827 AND NOT EXISTS (SELECT 'x'
4828 FROM xla_event_sources s
4829 WHERE s.application_id = p_application_id
4830 AND s.entity_code = p_entity_code
4831 AND s.event_class_code = p_event_class_code
4832 AND s.source_application_id = r.reference_object_appl_id
4833 AND s.source_code = c.column_name)
4834 UNION ALL
4835 SELECT distinct (c.column_name)
4836 ,p_application_id
4837 ,p_entity_code
4838 ,p_event_class_code
4839 ,r.reference_object_appl_id
4840 ,'S'
4841 ,'Y'
4842 ,'H'
4843 ,g_creation_date
4844 ,g_created_by
4845 ,g_last_updated_by
4846 ,g_last_update_date
4847 ,g_last_update_login
4848 FROM dba_tab_columns c, xla_reference_objects r,user_objects uo, user_synonyms s,xla_reference_objects_gt og, xla_extract_objects e
4849 WHERE c.table_name = s.table_name
4850 AND og.reference_object_name = r.reference_object_name
4851 AND s.table_owner = c.owner
4852 AND uo.object_name = s.synonym_name
4853 AND uo.object_name = r.reference_object_name
4854 AND uo.object_type = 'SYNONYM'
4855 AND e.application_id = p_application_id
4856 AND e.entity_code = p_entity_code
4857 AND e.event_class_code = p_event_class_code
4858 AND e.object_name = r.object_name
4859 AND e.object_type_code IN ('HEADER','HEADER_MLS')
4860 AND r.application_id = p_application_id
4861 AND r.entity_code = p_entity_code
4862 AND r.event_class_code = p_event_class_code
4863 AND r.always_populated_flag = 'Y'
4864 AND NOT EXISTS (SELECT 'x'
4865 FROM xla_event_sources s
4866 WHERE s.application_id = p_application_id
4867 AND s.entity_code = p_entity_code
4868 AND s.event_class_code = p_event_class_code
4869 AND s.source_application_id = r.reference_object_appl_id
4870 AND s.source_code = c.column_name));
4871
4872 -- Assign sources at line level to the event class
4873 -- for line extract objects that are always populated
4874 INSERT INTO xla_event_sources
4875 (source_code
4876 ,application_id
4877 ,entity_code
4878 ,event_class_code
4879 ,source_application_id
4880 ,source_type_code
4881 ,active_flag
4882 ,level_code
4883 ,creation_date
4884 ,created_by
4885 ,last_updated_by
4886 ,last_update_date
4887 ,last_update_login)
4888 (SELECT distinct (c.column_name)
4889 ,p_application_id
4890 ,p_entity_code
4891 ,p_event_class_code
4892 ,p_application_id
4893 ,'S'
4894 ,'Y'
4895 ,'L'
4896 ,g_creation_date
4897 ,g_created_by
4898 ,g_last_updated_by
4899 ,g_last_update_date
4900 ,g_last_update_login
4901 FROM dba_tab_columns c,user_objects uo, xla_extract_objects e, xla_extract_objects_gt og
4902 WHERE c.table_name = e.object_name
4903 AND uo.object_name = e.object_name
4904 AND uo.object_type <> 'SYNONYM'
4905 AND og.object_name = e.object_name
4906 AND og.owner = c.owner
4907 AND e.object_type_code IN ('LINE','LINE_MLS')
4908 AND e.application_id = p_application_id
4909 AND e.entity_code = p_entity_code
4910 AND e.event_class_code = p_event_class_code
4911 AND e.always_populated_flag = 'Y'
4912 AND NOT EXISTS (SELECT 'x'
4913 FROM xla_event_sources s
4914 WHERE s.application_id = p_application_id
4915 AND s.entity_code = p_entity_code
4916 AND s.event_class_code = p_event_class_code
4917 AND s.source_application_id = p_application_id
4918 AND s.source_code = c.column_name)
4919 UNION ALL
4920 SELECT distinct (c.column_name)
4921 ,p_application_id
4922 ,p_entity_code
4923 ,p_event_class_code
4924 ,p_application_id
4925 ,'S'
4926 ,'Y'
4927 ,'L'
4928 ,g_creation_date
4929 ,g_created_by
4930 ,g_last_updated_by
4931 ,g_last_update_date
4932 ,g_last_update_login
4933 FROM dba_tab_columns c, user_objects uo,user_synonyms s,xla_extract_objects e, xla_extract_objects_gt og
4934 WHERE s.table_owner = c.owner
4935 AND c.table_name = s.table_name
4936 AND uo.object_name = s.synonym_name
4937 AND uo.object_name = e.object_name
4938 AND uo.object_type = 'SYNONYM'
4939 AND og.object_name = e.object_name
4940 --AND og.owner = c.owner
4941 AND e.object_type_code IN ('LINE','LINE_MLS')
4942 AND e.application_id = p_application_id
4943 AND e.entity_code = p_entity_code
4944 AND e.event_class_code = p_event_class_code
4945 AND e.always_populated_flag = 'Y'
4946 AND NOT EXISTS (SELECT 'x'
4947 FROM xla_event_sources s
4948 WHERE s.application_id = p_application_id
4949 AND s.entity_code = p_entity_code
4950 AND s.event_class_code = p_event_class_code
4951 AND s.source_application_id = p_application_id
4952 AND s.source_code = c.column_name));
4953
4954 -- Assign sources at line level to the event class
4955 -- for line reference objects that are always populated
4956 INSERT INTO xla_event_sources
4957 (source_code
4958 ,application_id
4959 ,entity_code
4960 ,event_class_code
4961 ,source_application_id
4962 ,source_type_code
4963 ,active_flag
4964 ,level_code
4965 ,creation_date
4966 ,created_by
4967 ,last_updated_by
4968 ,last_update_date
4969 ,last_update_login)
4970 (SELECT distinct (c.column_name)
4971 ,p_application_id
4972 ,p_entity_code
4973 ,p_event_class_code
4974 ,r.reference_object_appl_id
4975 ,'S'
4976 ,'Y'
4977 ,'L'
4978 ,g_creation_date
4979 ,g_created_by
4980 ,g_last_updated_by
4981 ,g_last_update_date
4982 ,g_last_update_login
4983 FROM dba_tab_columns c, xla_reference_objects r,user_objects uo,
4984 xla_reference_objects_gt og, xla_extract_objects e
4985 WHERE c.table_name = r.reference_object_name
4986 AND uo.object_name = r.reference_object_name
4987 AND uo.object_type <> 'SYNONYM'
4988 AND og.reference_object_name = r.reference_object_name
4989 AND og.owner = c.owner
4990 AND e.application_id = p_application_id
4991 AND e.entity_code = p_entity_code
4992 AND e.event_class_code = p_event_class_code
4993 AND e.object_name = r.object_name
4994 AND e.object_type_code IN ('LINE','LINE_MLS')
4995 AND r.application_id = p_application_id
4996 AND r.entity_code = p_entity_code
4997 AND r.event_class_code = p_event_class_code
4998 AND r.always_populated_flag = 'Y'
4999 AND NOT EXISTS (SELECT 'x'
5000 FROM xla_event_sources s
5001 WHERE s.application_id = p_application_id
5002 AND s.entity_code = p_entity_code
5003 AND s.event_class_code = p_event_class_code
5004 AND s.source_application_id = r.reference_object_appl_id
5005 AND s.source_code = c.column_name)
5006 UNION ALL
5007 SELECT distinct (c.column_name)
5008 ,p_application_id
5009 ,p_entity_code
5010 ,p_event_class_code
5011 ,r.reference_object_appl_id
5012 ,'S'
5013 ,'Y'
5014 ,'L'
5015 ,g_creation_date
5016 ,g_created_by
5017 ,g_last_updated_by
5018 ,g_last_update_date
5019 ,g_last_update_login
5020 FROM dba_tab_columns c, xla_reference_objects r,user_objects uo,user_synonyms s,
5021 xla_reference_objects_gt og, xla_extract_objects e
5022 WHERE c.table_name = s.table_name
5023 AND uo.object_name = s.synonym_name
5024 AND uo.object_name = r.reference_object_name
5025 AND uo.object_type = 'SYNONYM'
5026 AND og.reference_object_name = r.reference_object_name
5027 AND s.table_owner = c.owner
5028 AND e.application_id = p_application_id
5029 AND e.entity_code = p_entity_code
5030 AND e.event_class_code = p_event_class_code
5031 AND e.object_name = r.object_name
5032 AND e.object_type_code IN ('LINE','LINE_MLS')
5033 AND r.application_id = p_application_id
5034 AND r.entity_code = p_entity_code
5035 AND r.event_class_code = p_event_class_code
5036 AND r.always_populated_flag = 'Y'
5037 AND NOT EXISTS (SELECT 'x'
5038 FROM xla_event_sources s
5039 WHERE s.application_id = p_application_id
5040 AND s.entity_code = p_entity_code
5041 AND s.event_class_code = p_event_class_code
5042 AND s.source_application_id = r.reference_object_appl_id
5043 AND s.source_code = c.column_name));
5044
5045 -- Assign sources at header level to the event class
5046 -- for header extract objects that are not always populated
5047 INSERT INTO xla_event_sources
5048 (source_code
5049 ,application_id
5050 ,entity_code
5051 ,event_class_code
5052 ,source_application_id
5053 ,source_type_code
5054 ,active_flag
5055 ,level_code
5056 ,creation_date
5057 ,created_by
5058 ,last_updated_by
5059 ,last_update_date
5060 ,last_update_login)
5061 (SELECT distinct (c.column_name)
5062 ,p_application_id
5063 ,p_entity_code
5064 ,p_event_class_code
5065 ,p_application_id
5066 ,'S'
5067 ,'Y'
5068 ,'H'
5069 ,g_creation_date
5070 ,g_created_by
5071 ,g_last_updated_by
5072 ,g_last_update_date
5073 ,g_last_update_login
5074 FROM dba_tab_columns c, user_objects uo,xla_extract_objects e, xla_extract_objects_gt og
5075 WHERE c.table_name = e.object_name
5076 AND uo.object_name = e.object_name
5077 AND uo.object_type <> 'SYNONYM'
5078 AND og.object_name = e.object_name
5079 AND og.owner = c.owner
5080 AND e.object_type_code IN ('HEADER','HEADER_MLS')
5081 AND e.application_id = p_application_id
5082 AND e.entity_code = p_entity_code
5083 AND e.event_class_code = p_event_class_code
5084 AND e.always_populated_flag = 'N'
5085 AND NOT EXISTS (SELECT 'x'
5086 FROM xla_event_sources s
5087 WHERE s.application_id = p_application_id
5088 AND s.entity_code = p_entity_code
5089 AND s.event_class_code = p_event_class_code
5090 AND s.source_application_id = p_application_id
5091 AND s.source_code = c.column_name)
5092 UNION ALL
5093 SELECT distinct (c.column_name)
5094 ,p_application_id
5095 ,p_entity_code
5096 ,p_event_class_code
5097 ,p_application_id
5098 ,'S'
5099 ,'Y'
5100 ,'H'
5101 ,g_creation_date
5102 ,g_created_by
5103 ,g_last_updated_by
5104 ,g_last_update_date
5105 ,g_last_update_login
5106 FROM dba_tab_columns c, user_objects uo,user_synonyms s,xla_extract_objects e, xla_extract_objects_gt og
5107 WHERE s.table_owner = c.owner
5108 AND c.table_name = s.table_name
5109 AND uo.object_name = s.synonym_name
5110 AND uo.object_name = e.object_name
5111 AND uo.object_type = 'SYNONYM'
5112 AND og.object_name = e.object_name
5113 --AND og.owner = c.owner
5114 AND e.object_type_code IN ('HEADER','HEADER_MLS')
5115 AND e.application_id = p_application_id
5116 AND e.entity_code = p_entity_code
5117 AND e.event_class_code = p_event_class_code
5118 AND e.always_populated_flag = 'N'
5119 AND NOT EXISTS (SELECT 'x'
5120 FROM xla_event_sources s
5121 WHERE s.application_id = p_application_id
5122 AND s.entity_code = p_entity_code
5123 AND s.event_class_code = p_event_class_code
5124 AND s.source_application_id = p_application_id
5125 AND s.source_code = c.column_name));
5126
5127 -- Assign sources at header level to the event class
5128 -- for header reference objects that are not always populated
5129 INSERT INTO xla_event_sources
5130 (source_code
5131 ,application_id
5132 ,entity_code
5133 ,event_class_code
5134 ,source_application_id
5135 ,source_type_code
5136 ,active_flag
5137 ,level_code
5138 ,creation_date
5139 ,created_by
5140 ,last_updated_by
5141 ,last_update_date
5142 ,last_update_login)
5143 (SELECT distinct (c.column_name)
5144 ,p_application_id
5145 ,p_entity_code
5146 ,p_event_class_code
5147 ,r.reference_object_appl_id
5148 ,'S'
5149 ,'Y'
5150 ,'H'
5151 ,g_creation_date
5152 ,g_created_by
5153 ,g_last_updated_by
5154 ,g_last_update_date
5155 ,g_last_update_login
5156 FROM dba_tab_columns c,user_objects uo, xla_reference_objects r,
5157 xla_reference_objects_gt og, xla_extract_objects e
5158 WHERE c.table_name = r.reference_object_name
5159 AND uo.object_name = r.reference_object_name
5160 AND uo.object_type <> 'SYNONYM'
5161 AND og.reference_object_name = r.reference_object_name
5162 AND og.owner = c.owner
5163 AND e.application_id = p_application_id
5164 AND e.entity_code = p_entity_code
5165 AND e.event_class_code = p_event_class_code
5166 AND e.object_name = r.object_name
5167 AND e.object_type_code IN ('HEADER','HEADER_MLS')
5168 AND r.application_id = p_application_id
5169 AND r.entity_code = p_entity_code
5170 AND r.event_class_code = p_event_class_code
5171 AND r.always_populated_flag = 'N'
5172 AND NOT EXISTS (SELECT 'x'
5173 FROM xla_event_sources s
5174 WHERE s.application_id = p_application_id
5175 AND s.entity_code = p_entity_code
5176 AND s.event_class_code = p_event_class_code
5177 AND s.source_application_id = r.reference_object_appl_id
5178 AND s.source_code = c.column_name)
5179 UNION ALL
5180 SELECT distinct (c.column_name)
5181 ,p_application_id
5182 ,p_entity_code
5183 ,p_event_class_code
5184 ,r.reference_object_appl_id
5185 ,'S'
5186 ,'Y'
5187 ,'H'
5188 ,g_creation_date
5189 ,g_created_by
5190 ,g_last_updated_by
5191 ,g_last_update_date
5192 ,g_last_update_login
5193 FROM dba_tab_columns c,user_objects uo,user_synonyms s, xla_reference_objects r,
5194 xla_reference_objects_gt og, xla_extract_objects e
5195 WHERE s.table_owner = c.owner
5196 AND c.table_name = s.table_name
5197 AND uo.object_name = s.synonym_name
5198 AND uo.object_name = r.reference_object_name
5199 AND uo.object_type = 'SYNONYM'
5200 AND og.reference_object_name = r.reference_object_name
5201 -- AND og.owner = c.owner
5202 AND e.application_id = p_application_id
5203 AND e.entity_code = p_entity_code
5204 AND e.event_class_code = p_event_class_code
5205 AND e.object_name = r.object_name
5206 AND e.object_type_code IN ('HEADER','HEADER_MLS')
5207 AND r.application_id = p_application_id
5208 AND r.entity_code = p_entity_code
5209 AND r.event_class_code = p_event_class_code
5210 AND r.always_populated_flag = 'N'
5211 AND NOT EXISTS (SELECT 'x'
5212 FROM xla_event_sources s
5213 WHERE s.application_id = p_application_id
5214 AND s.entity_code = p_entity_code
5215 AND s.event_class_code = p_event_class_code
5216 AND s.source_application_id = r.reference_object_appl_id
5217 AND s.source_code = c.column_name));
5218
5219 -- Assign sources at line level to the event class
5220 -- for line extract objects that are not always populated
5221 INSERT INTO xla_event_sources
5222 (source_code
5223 ,application_id
5224 ,entity_code
5225 ,event_class_code
5226 ,source_application_id
5227 ,source_type_code
5228 ,active_flag
5229 ,level_code
5230 ,creation_date
5231 ,created_by
5232 ,last_updated_by
5233 ,last_update_date
5234 ,last_update_login)
5235 (SELECT distinct (c.column_name)
5236 ,p_application_id
5237 ,p_entity_code
5238 ,p_event_class_code
5239 ,p_application_id
5240 ,'S'
5241 ,'Y'
5242 ,'L'
5243 ,g_creation_date
5244 ,g_created_by
5245 ,g_last_updated_by
5246 ,g_last_update_date
5247 ,g_last_update_login
5248 FROM dba_tab_columns c,user_objects uo, xla_extract_objects e, xla_extract_objects_gt og
5249 WHERE c.table_name = e.object_name
5250 AND uo.object_name = e.object_name
5251 AND uo.object_type <> 'SYNONYM'
5252 AND og.object_name = e.object_name
5253 AND og.owner = c.owner
5254 AND e.object_type_code IN ('LINE','LINE_MLS')
5255 AND e.application_id = p_application_id
5256 AND e.entity_code = p_entity_code
5257 AND e.event_class_code = p_event_class_code
5258 AND e.always_populated_flag = 'N'
5259 AND NOT EXISTS (SELECT 'x'
5260 FROM xla_event_sources s
5261 WHERE s.application_id = p_application_id
5262 AND s.entity_code = p_entity_code
5263 AND s.event_class_code = p_event_class_code
5264 AND s.source_application_id = p_application_id
5265 AND s.source_code = c.column_name)
5266 UNION ALL
5267 SELECT distinct (c.column_name)
5268 ,p_application_id
5269 ,p_entity_code
5270 ,p_event_class_code
5271 ,p_application_id
5272 ,'S'
5273 ,'Y'
5274 ,'L'
5275 ,g_creation_date
5276 ,g_created_by
5277 ,g_last_updated_by
5278 ,g_last_update_date
5279 ,g_last_update_login
5280 FROM dba_tab_columns c, user_objects uo,user_synonyms s,xla_extract_objects e, xla_extract_objects_gt og
5281 WHERE s.table_owner = c.owner
5282 AND c.table_name = s.table_name
5283 AND uo.object_name = s.synonym_name
5284 AND uo.object_name = e.object_name
5285 AND uo.object_type = 'SYNONYM'
5286 AND og.object_name = e.object_name
5287 -- AND og.owner = c.owner
5288 AND e.object_type_code IN ('LINE','LINE_MLS')
5289 AND e.application_id = p_application_id
5290 AND e.entity_code = p_entity_code
5291 AND e.event_class_code = p_event_class_code
5292 AND e.always_populated_flag = 'N'
5293 AND NOT EXISTS (SELECT 'x'
5294 FROM xla_event_sources s
5295 WHERE s.application_id = p_application_id
5296 AND s.entity_code = p_entity_code
5297 AND s.event_class_code = p_event_class_code
5298 AND s.source_application_id = p_application_id
5299 AND s.source_code = c.column_name));
5300
5301 -- Assign sources at line level to the event class
5302 -- for line extract objects that are not always populated
5303 INSERT INTO xla_event_sources
5304 (source_code
5305 ,application_id
5306 ,entity_code
5307 ,event_class_code
5308 ,source_application_id
5309 ,source_type_code
5310 ,active_flag
5311 ,level_code
5312 ,creation_date
5313 ,created_by
5314 ,last_updated_by
5315 ,last_update_date
5316 ,last_update_login)
5317 (SELECT distinct (c.column_name)
5318 ,p_application_id
5319 ,p_entity_code
5320 ,p_event_class_code
5321 ,r.reference_object_appl_id
5322 ,'S'
5323 ,'Y'
5324 ,'L'
5325 ,g_creation_date
5326 ,g_created_by
5327 ,g_last_updated_by
5328 ,g_last_update_date
5329 ,g_last_update_login
5330 FROM dba_tab_columns c,user_objects uo, xla_reference_objects r,
5331 xla_reference_objects_gt og, xla_extract_objects e
5332 WHERE c.table_name = r.reference_object_name
5333 AND uo.object_name = r.reference_object_name
5334 AND uo.object_type <> 'SYNONYM'
5335 AND og.reference_object_name = r.reference_object_name
5336 AND og.owner = c.owner
5337 AND e.application_id = p_application_id
5338 AND e.entity_code = p_entity_code
5339 AND e.event_class_code = p_event_class_code
5340 AND e.object_name = r.object_name
5341 AND e.object_type_code IN ('LINE','LINE_MLS')
5342 AND r.application_id = p_application_id
5343 AND r.entity_code = p_entity_code
5344 AND r.event_class_code = p_event_class_code
5345 AND r.always_populated_flag = 'N'
5346 AND NOT EXISTS (SELECT 'x'
5347 FROM xla_event_sources s
5348 WHERE s.application_id = p_application_id
5349 AND s.entity_code = p_entity_code
5350 AND s.event_class_code = p_event_class_code
5351 AND s.source_application_id = r.reference_object_appl_id
5352 AND s.source_code = c.column_name)
5353 UNION ALL
5354
5355 SELECT distinct (c.column_name)
5356 ,p_application_id
5357 ,p_entity_code
5358 ,p_event_class_code
5359 ,r.reference_object_appl_id
5360 ,'S'
5361 ,'Y'
5362 ,'L'
5363 ,g_creation_date
5364 ,g_created_by
5365 ,g_last_updated_by
5366 ,g_last_update_date
5367 ,g_last_update_login
5368 FROM dba_tab_columns c,user_objects uo,user_synonyms s, xla_reference_objects r,
5369 xla_reference_objects_gt og, xla_extract_objects e
5370 WHERE s.table_owner = c.owner
5371 AND c.table_name = s.table_name
5372 AND uo.object_name = s.synonym_name
5373 AND uo.object_name = r.reference_object_name
5374 AND uo.object_type = 'SYNONYM'
5375 AND og.reference_object_name = r.reference_object_name
5376 -- AND og.owner = c.owner
5377 AND e.application_id = p_application_id
5378 AND e.entity_code = p_entity_code
5379 AND e.event_class_code = p_event_class_code
5380 AND e.object_name = r.object_name
5381 AND e.object_type_code IN ('LINE','LINE_MLS')
5382 AND r.application_id = p_application_id
5383 AND r.entity_code = p_entity_code
5384 AND r.event_class_code = p_event_class_code
5385 AND r.always_populated_flag = 'N'
5386 AND NOT EXISTS (SELECT 'x'
5387 FROM xla_event_sources s
5388 WHERE s.application_id = p_application_id
5389 AND s.entity_code = p_entity_code
5390 AND s.event_class_code = p_event_class_code
5391 AND s.source_application_id = r.reference_object_appl_id
5392 AND s.source_code = c.column_name));
5393
5394
5395
5396 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
5397 trace
5398 (p_msg => 'End'
5399 ,p_level => C_LEVEL_PROCEDURE);
5400 END IF;
5401
5402 EXCEPTION
5403 WHEN xla_exceptions_pkg.application_exception THEN
5404 RAISE;
5405 WHEN OTHERS THEN
5406 xla_exceptions_pkg.raise_message
5407 (p_location => 'xla_extract_integrity_pkg.Assign_sources');
5408 END Assign_sources; -- end of procedure
5409
5410 END xla_extract_integrity_pkg;