[Home] [Help]
Skip to content
PACKAGE BODY: APPS.XLA_EXTRACT_INTEGRITY_PKG
Source
1 PACKAGE BODY XLA_EXTRACT_INTEGRITY_PKG AS
2 /* $Header: xlaamext.pkb 120.44 2006/08/25 20:45:48 weshen 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 | |
21 +===========================================================================*/
22
23 --=============================================================================
24 -- **************** declaraions ********************
25 --=============================================================================
26 -------------------------------------------------------------------------------
27 -- declaring private package variables
28 -------------------------------------------------------------------------------
29
30 g_creation_date DATE;
31 g_last_update_date DATE;
32 g_created_by INTEGER;
33 g_last_update_login INTEGER;
34 g_last_updated_by INTEGER;
35
36 -------------------------------------------------------------------------------
37 -- Constants
38 -------------------------------------------------------------------------------
39 C_REF_OBJECT_FLAG_N CONSTANT VARCHAR2(1) := 'N';
40 C_REF_OBJECT_FLAG_Y CONSTANT VARCHAR2(1) := 'Y';
41
42 -------------------------------------------------------------------------------
43 -- declaring private package arrays
44 -------------------------------------------------------------------------------
45 TYPE t_array_codes IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
46 TYPE t_array_type_codes IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
47 TYPE t_array_vl2000 IS table OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
48 TYPE t_array_id IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
49
50 -------------------------------------------------------------------------------
51 -- forward declarion of private procedures and functions
52 -------------------------------------------------------------------------------
53 FUNCTION Chk_primary_keys_exist
54 (p_application_id IN NUMBER
55 ,p_entity_code IN VARCHAR2
56 ,p_event_class_code IN VARCHAR2
57 ,p_amb_context_code IN VARCHAR2 DEFAULT NULL
58 ,p_product_rule_type_code IN VARCHAR2 DEFAULT NULL
59 ,p_product_rule_code IN VARCHAR2 DEFAULT NULL)
60 RETURN BOOLEAN;
61
62 FUNCTION Validate_accounting_sources
63 (p_application_id IN NUMBER
64 ,p_entity_code IN VARCHAR2
65 ,p_event_class_code IN VARCHAR2)
66 RETURN BOOLEAN;
67
68 FUNCTION Create_sources
69 (p_application_id IN NUMBER
70 ,p_entity_code IN VARCHAR2
71 ,p_event_class_code IN VARCHAR2)
72 RETURN BOOLEAN;
73
74 PROCEDURE Assign_sources
75 (p_application_id IN NUMBER
76 ,p_entity_code IN VARCHAR2
77 ,p_event_class_code IN VARCHAR2);
78
79 --=============================================================================
80 -- *********** Local Trace Routine **********
81 --=============================================================================
82 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
83 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
84 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
85 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
86 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
87 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
88
89 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_extract_integrity_pkg';
90
91 g_trace_label VARCHAR2(240);
92 g_log_level NUMBER;
93 g_log_enabled BOOLEAN;
94
95 PROCEDURE trace
96 (p_msg IN VARCHAR2
97 ,p_level IN NUMBER) IS
98
99 l_module VARCHAR2(240);
100 BEGIN
101
102 IF (g_log_level is NULL) THEN
103 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
104 END IF;
105
106 IF (g_log_level is NULL) THEN
107 g_log_enabled := fnd_log.test
108 (log_level => g_log_level
109 ,module => C_DEFAULT_MODULE);
110 END IF;
111
112 l_module := C_DEFAULT_MODULE||'.'||g_trace_label;
113
114 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
115 fnd_log.message(p_level, l_module);
116 ELSIF p_level >= g_log_level THEN
117 fnd_log.string(p_level, l_module, p_msg);
118 END IF;
119 EXCEPTION
120 WHEN xla_exceptions_pkg.application_exception THEN
121 RAISE;
122 WHEN OTHERS THEN
123 xla_exceptions_pkg.raise_message
124 (p_location => 'xla_extract_integrity_pkg.trace');
125 END trace;
126
127 --=============================================================================
128 -- *********** public procedures and functions **********
129 --=============================================================================
130 --=============================================================================
131 --
132 -- Following are the public routines:
133 --
134 -- 1. Check_extract_integrity
135 -- 2. Validate_extract_objects
136 -- 3. Validate_sources
137 -- 4. Validate_sources_with_extract
138 -- 5. set_extract_object_owner
139 --
140 --=============================================================================
141
142 /*======================================================================+
143 | |
144 | Public Function |
145 | |
146 | Check_extract_integrity |
147 | |
148 | This routine is called by the Create and Assign Sources program |
149 | to do all validations for an event class |
150 | |
151 +======================================================================*/
152 FUNCTION Check_extract_integrity
153 (p_application_id IN NUMBER
154 ,p_entity_code IN VARCHAR2
155 ,p_event_class_code IN VARCHAR2
156 ,p_processing_mode IN VARCHAR2)
157 RETURN BOOLEAN
158 IS
159
160 l_application_id NUMBER(15);
161 l_entity_code VARCHAR2(30);
162 l_event_class_code VARCHAR2(30);
163 l_return BOOLEAN := TRUE;
164
165 BEGIN
166
167 l_application_id := p_application_id;
168 l_entity_code := p_entity_code;
169 l_event_class_code := p_event_class_code;
170
171 IF (g_log_level is NULL) THEN
172 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
173 END IF;
174
175 IF (g_log_level is NULL) THEN
176 g_log_enabled := fnd_log.test
177 (log_level => g_log_level
178 ,module => C_DEFAULT_MODULE);
179 END IF;
180
181 IF (g_log_level is NULL) THEN
182 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
183 END IF;
184
185 IF (g_log_level is NULL) THEN
186 g_log_enabled := fnd_log.test
187 (log_level => g_log_level
188 ,module => C_DEFAULT_MODULE);
189 END IF;
190
191 g_trace_label :='Check_extract_integrity';
192 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
193 trace
194 (p_msg => 'Begin'
195 ,p_level => C_LEVEL_PROCEDURE);
196 trace
197 (p_msg => 'p_application_id = ' ||TO_CHAR(p_application_id)
198 ,p_level => C_LEVEL_PROCEDURE);
199 trace
200 (p_msg => 'p_entity_code = '||p_entity_code
201 ,p_level => C_LEVEL_PROCEDURE);
202 trace
203 (p_msg => 'p_event_class_code = ' ||p_event_class_code
204 ,p_level => C_LEVEL_PROCEDURE);
205 trace
206 (p_msg => 'p_processing_mode = ' ||p_processing_mode
207 ,p_level => C_LEVEL_PROCEDURE);
208 END IF;
209
210 -- Set environment settings
211 xla_environment_pkg.refresh;
212
213 -- Delete the error table for the event class
214 DELETE
215 FROM xla_amb_setup_errors
216 WHERE application_id = p_application_id
217 AND entity_code = p_entity_code
218 AND event_class_code = p_event_class_code
219 AND product_rule_code IS NULL;
220
221 -- Initialize the error package
222 Xla_amb_setup_err_pkg.initialize;
223
224 -- Get the extract object owner and store in GT table.
225 xla_extract_integrity_pkg.set_extract_object_owner
226 (p_application_id => l_application_id
227 ,p_entity_code => l_entity_code
228 ,p_event_class_code => l_event_class_code
229 );
230
231 -- Validate extract objects
232 IF NOT Xla_extract_integrity_pkg.validate_extract_objects
233 (p_application_id => l_application_id
234 ,p_entity_code => l_entity_code
235 ,p_event_class_code => l_event_class_code) THEN
236
237 l_return := FALSE;
238 END IF;
239
240 -- Validate primary keys
241 IF NOT Chk_primary_keys_exist
242 (p_application_id => l_application_id
243 ,p_entity_code => l_entity_code
244 ,p_event_class_code => l_event_class_code) THEN
245 l_return := FALSE;
246 END IF;
247
248 IF p_processing_mode = 'CREATE' THEN
249
250 -- Create sources
251 IF NOT Create_sources
252 (p_application_id => l_application_id
253 ,p_entity_code => l_entity_code
254 ,p_event_class_code => l_event_class_code) THEN
255 l_return := FALSE;
256 END IF;
257
258 -- Assign sources
259 Assign_sources
260 (p_application_id => l_application_id
261 ,p_entity_code => l_entity_code
262 ,p_event_class_code => l_event_class_code);
263
264 ELSIF p_processing_mode = 'VALIDATE' THEN
265
266 -- Validate sources with the extract objects
267 IF NOT Xla_extract_integrity_pkg.Validate_sources
268 (p_application_id => l_application_id
269 ,p_entity_code => l_entity_code
270 ,p_event_class_code => l_event_class_code) THEN
271 l_return := FALSE;
272 END IF;
273
274 -- Validate accounting sources
275 IF NOT Validate_accounting_sources
276 (p_application_id => l_application_id
277 ,p_entity_code => l_entity_code
278 ,p_event_class_code => l_event_class_code) THEN
279 l_return := FALSE;
280 END IF;
281 END IF;
282
283 -- Insert errors into the error table from the plsql array
284 Xla_amb_setup_err_pkg.insert_errors;
285 COMMIT;
286
287 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
288 trace
289 (p_msg => 'End'
290 ,p_level => C_LEVEL_PROCEDURE);
291 END IF;
292
293 RETURN l_return;
294
295 EXCEPTION
296 WHEN xla_exceptions_pkg.application_exception THEN
297 RAISE;
298 WHEN OTHERS THEN
299 xla_exceptions_pkg.raise_message
300 (p_location => 'xla_extract_integrity_pkg.check_extract_integrity');
301 END Check_extract_integrity; -- end of function
302
303 /*======================================================================+
304 | |
305 | Public Function |
306 | |
307 | Validate_extract_objects |
308 | |
309 | This routine is called to validate the extract objects |
310 | |
311 +======================================================================*/
312 FUNCTION Validate_extract_objects
313 (p_application_id IN NUMBER
314 ,p_entity_code IN VARCHAR2
315 ,p_event_class_code IN VARCHAR2
316 ,p_amb_context_code IN VARCHAR2
317 ,p_product_rule_type_code IN VARCHAR2
318 ,p_product_rule_code IN VARCHAR2)
319 RETURN BOOLEAN
320 IS
321 -- Variable Declaration
322 l_application_id NUMBER(15);
323 l_entity_code VARCHAR2(30);
324 l_event_class_code VARCHAR2(30);
325 l_amb_context_code VARCHAR2(30);
326 l_product_rule_code VARCHAR2(30);
327 l_product_rule_type_code VARCHAR2(1);
328 l_return BOOLEAN := TRUE;
329 l_exist VARCHAR2(1) := NULL;
330
331 -- Cursor Declaration
332
333 -- Check if extract objects are assigned to an event class
334
335 CURSOR c_ec_obj_exist
336 IS
337 SELECT 'x'
338 FROM xla_extract_objects e
339 WHERE application_id = p_application_id
340 AND entity_code = p_entity_code
341 AND event_class_code = p_event_class_code;
342
343 -- Get all event classes for which extract objects are not assigned
344
345 CURSOR c_aad_obj_exist
346 IS
347 SELECT h.entity_code, h.event_class_code
348 FROM xla_prod_acct_headers h
349 WHERE h.application_id = p_application_id
350 AND h.amb_context_code = p_amb_context_code
351 AND h.product_rule_type_code = p_product_rule_type_code
352 AND h.product_rule_code = p_product_rule_code
353 AND h.accounting_required_flag = 'Y'
354 AND NOT EXISTS (SELECT 'x'
355 FROM xla_extract_objects e
356 WHERE e.application_id = h.application_id
357 AND e.entity_code = h.entity_code
358 AND e.event_class_code = h.event_class_code);
359
360 l_aad_obj_exist c_aad_obj_exist%rowtype;
361
362 -- Get all extract objects for the event class that are not defined in the
363 -- database
364
365 CURSOR c_ec_objects
366 IS
367 SELECT object_name
368 ,object_type_code
369 ,C_REF_OBJECT_FLAG_N ref_object_flag
370 FROM xla_extract_objects e
371 WHERE application_id = p_application_id
372 AND entity_code = p_entity_code
373 AND event_class_code = p_event_class_code
374 AND not exists (SELECT 'x'
375 FROM xla_extract_objects_gt o
376 WHERE o.object_name = e.object_name)
377 --
378 -- Get all reference objects for the event class that are not defined in the
379 -- database
380 UNION ALL
381 SELECT r.reference_object_name
382 ,e.object_type_code
383 ,C_REF_OBJECT_FLAG_Y ref_object_flag
384 FROM xla_reference_objects r
385 ,xla_extract_objects e
386 WHERE r.application_id = p_application_id
387 AND r.entity_code = p_entity_code
388 AND r.event_class_code = p_event_class_code
389 AND e.application_id = r.application_id
390 AND e.entity_code = r.entity_code
391 AND e.event_class_code = r.event_class_code
392 AND e.object_name = r.object_name
393 AND not exists (SELECT 'x'
394 FROM xla_reference_objects_gt o
395 WHERE o.reference_object_name = r.reference_object_name);
396
397
398 l_ec_objects c_ec_objects%rowtype;
399
400 -- Get all event classes for the AAD whose extract objects are not
401 -- defined in the database
402
403 CURSOR c_aad_objects
404 IS
405 SELECT e.entity_code
406 ,e.event_class_code
407 ,e.object_name
408 ,e.object_type_code
409 ,C_REF_OBJECT_FLAG_N ref_object_flag
410 FROM xla_extract_objects e, xla_prod_acct_headers h
411 WHERE h.application_id = p_application_id
412 AND h.amb_context_code = p_amb_context_code
413 AND h.product_rule_type_code = p_product_rule_type_code
414 AND h.product_rule_code = p_product_rule_code
415 AND h.accounting_required_flag = 'Y'
416 AND e.application_id = h.application_id
417 AND e.entity_code = h.entity_code
418 AND e.event_class_code = h.event_class_code
419 AND not exists (SELECT 'x'
420 FROM xla_extract_objects_gt o
421 WHERE o.object_name = e.object_name)
422 UNION ALL
423 SELECT r.entity_code
424 ,r.event_class_code
425 ,r.reference_object_name
426 ,e.object_type_code
427 ,C_REF_OBJECT_FLAG_Y ref_object_flag
428 FROM xla_reference_objects r,
429 xla_extract_objects e,
430 xla_prod_acct_headers h
431 WHERE h.application_id = p_application_id
432 AND h.amb_context_code = p_amb_context_code
433 AND h.product_rule_type_code = p_product_rule_type_code
434 AND h.product_rule_code = p_product_rule_code
435 AND h.accounting_required_flag = 'Y'
436 AND r.application_id = h.application_id
437 AND r.entity_code = h.entity_code
438 AND r.event_class_code = h.event_class_code
439 AND e.application_id = r.application_id
440 AND e.entity_code = r.entity_code
441 AND e.event_class_code = r.event_class_code
442 AND not exists (SELECT 'x'
443 FROM xla_reference_objects_gt o
444 WHERE o.reference_object_name = r.reference_object_name);
445
446 l_aad_objects c_aad_objects%rowtype;
447 l_message_name VARCHAR2(30);
448
449 BEGIN
450
451 l_application_id := p_application_id;
452 l_entity_code := p_entity_code;
453 l_event_class_code := p_event_class_code;
454 l_amb_context_code := p_amb_context_code;
455 l_product_rule_code := p_product_rule_code;
456 l_product_rule_type_code := p_product_rule_type_code;
457
458 IF (g_log_level is NULL) THEN
459 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
460 END IF;
461
462 IF (g_log_level is NULL) THEN
463 g_log_enabled := fnd_log.test
464 (log_level => g_log_level
465 ,module => C_DEFAULT_MODULE);
466 END IF;
467
468 g_trace_label :='Validate_extract_objects';
469 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
470 trace
471 (p_msg => 'Begin'
472 ,p_level => C_LEVEL_PROCEDURE);
473 trace
474 (p_msg => 'p_application_id = ' ||TO_CHAR(p_application_id)
475 ,p_level => C_LEVEL_PROCEDURE);
476 trace
477 (p_msg => 'p_entity_code = '||p_entity_code
478 ,p_level => C_LEVEL_PROCEDURE);
479 trace
480 (p_msg => 'p_event_class_code = ' ||p_event_class_code
481 ,p_level => C_LEVEL_PROCEDURE);
482 trace
483 (p_msg => 'p_amb_context_code = '||p_amb_context_code
484 ,p_level => C_LEVEL_PROCEDURE);
485 trace
486 (p_msg => 'p_product_rule_type_code = ' ||p_product_rule_type_code
487 ,p_level => C_LEVEL_PROCEDURE);
488 trace
489 (p_msg => 'p_product_rule_code = ' ||p_product_rule_code
490 ,p_level => C_LEVEL_PROCEDURE);
491 END IF;
492
493 -- Validate extract objects for an event class
494 IF p_event_class_code is not null then
495
496 -- Check if atleast one extract object is assigned to the event class
497 OPEN c_ec_obj_exist;
498 FETCH c_ec_obj_exist
499 INTO l_exist;
500 IF c_ec_obj_exist%NOTFOUND THEN
501 Xla_amb_setup_err_pkg.stack_error
502 (p_message_name => 'XLA_AB_EC_NO_EXTRACT_OBJECTS'
503 ,p_message_type => 'E'
504 ,p_message_category => 'EVENT_CLASS'
505 ,p_category_sequence => 2
506 ,p_application_id => l_application_id
507 ,p_entity_code => l_entity_code
508 ,p_event_class_code => l_event_class_code);
509
510 l_return := FALSE;
511 END IF;
512 CLOSE c_ec_obj_exist;
513
514 -- Check if the extract objects assigned to the event class exist
515 -- in the database
516
517 OPEN c_ec_objects;
518 LOOP
519 FETCH c_ec_objects
520 INTO l_ec_objects;
521 EXIT WHEN c_ec_objects%notfound;
522
523 IF l_ec_objects.ref_object_flag = C_REF_OBJECT_FLAG_Y THEN
524 l_message_name := 'XLA_AB_REF_OBJECT_NOT_DEFINED';
525 ELSE
526 l_message_name := 'XLA_AB_EXT_OBJECT_NOT_DEFINED';
527 END IF;
528
529 Xla_amb_setup_err_pkg.stack_error
530 (p_message_name => l_message_name
531 ,p_message_type => 'E'
532 ,p_message_category => 'EXTRACT_OBJECT'
533 ,p_category_sequence => 3
534 ,p_application_id => l_application_id
535 ,p_entity_code => l_entity_code
536 ,p_event_class_code => l_event_class_code
537 ,p_extract_object_name => l_ec_objects.object_name
538 ,p_extract_object_type => l_ec_objects.object_type_code);
539
540 l_return := FALSE;
541 END LOOP;
542 CLOSE c_ec_objects;
543
544 -- Validate extract objects for an application accounting definition
545 ELSIF p_product_rule_code is not null then
546
547 -- Error all event classes that do not have extract objects assigned
548
549 OPEN c_aad_obj_exist;
550 LOOP
551 FETCH c_aad_obj_exist
552 INTO l_aad_obj_exist;
553 EXIT WHEN c_aad_obj_exist%notfound;
554 Xla_amb_setup_err_pkg.stack_error
555 (p_message_name => 'XLA_AB_EC_NO_EXTRACT_OBJECTS'
556 ,p_message_type => 'W'
557 ,p_message_category => 'EVENT_CLASS'
558 ,p_category_sequence => 2
559 ,p_application_id => l_application_id
560 ,p_entity_code => l_aad_obj_exist.entity_code
561 ,p_event_class_code => l_aad_obj_exist.event_class_code
562 ,p_amb_context_code => l_amb_context_code
563 ,p_product_rule_type_code => l_product_rule_type_code
564 ,p_product_rule_code => l_product_rule_code);
565
566 l_return := FALSE;
567 END LOOP;
568 CLOSE c_aad_obj_exist;
569
570 -- Error all event classes whose extract objects
571 -- are not defined in the database
572
573 OPEN c_aad_objects;
574 LOOP
575 FETCH c_aad_objects
576 INTO l_aad_objects;
577 EXIT WHEN c_aad_objects%notfound;
578
579 IF l_aad_objects.ref_object_flag = C_REF_OBJECT_FLAG_Y THEN
580 l_message_name := 'XLA_AB_REF_OBJECT_NOT_DEFINED';
581 ELSE
582 l_message_name := 'XLA_AB_EXT_OBJECT_NOT_DEFINED';
583 END IF;
584
585 Xla_amb_setup_err_pkg.stack_error
586 (p_message_name => 'XLA_AB_EXT_OBJECT_NOT_DEFINED'
587 ,p_message_type => 'W'
588 ,p_message_category => 'EXTRACT_OBJECT'
589 ,p_category_sequence => 3
590 ,p_application_id => l_application_id
591 ,p_entity_code => l_aad_objects.entity_code
592 ,p_event_class_code => l_aad_objects.event_class_code
593 ,p_extract_object_name => l_aad_objects.object_name
594 ,p_extract_object_type => l_aad_objects.object_type_code
595 ,p_amb_context_code => l_amb_context_code
596 ,p_product_rule_type_code => l_product_rule_type_code
597 ,p_product_rule_code => l_product_rule_code);
598
599 l_return := FALSE;
600 END LOOP;
601 CLOSE c_aad_objects;
602 END IF;
603
604 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
605 trace
606 (p_msg => 'End'
607 ,p_level => C_LEVEL_PROCEDURE);
608 END IF;
609
610 RETURN l_return;
611
612 EXCEPTION
613 WHEN xla_exceptions_pkg.application_exception THEN
614 RAISE;
615 WHEN OTHERS THEN
616 xla_exceptions_pkg.raise_message
617 (p_location => 'xla_extract_integrity_pkg.validate_extract_objects');
618 END validate_extract_objects; -- end of function
619
620 /*======================================================================+
621 | |
622 | Public Function |
623 | |
624 | Validate_sources |
625 | |
626 | This routine is called to insert all sources for an event class into |
627 | a global temporary table before calling validate_sources_with_extract |
628 | |
629 +======================================================================*/
630 FUNCTION Validate_sources
631 (p_application_id IN NUMBER
632 ,p_entity_code IN VARCHAR2
633 ,p_event_class_code IN VARCHAR2)
634 RETURN BOOLEAN
635 IS
636 -- Variable Declaration
637
638 l_application_id NUMBER(15);
639 l_entity_code VARCHAR2(30);
640 l_event_class_code VARCHAR2(30);
641 l_return BOOLEAN := TRUE;
642 l_exist VARCHAR2(1) := NULL;
643
644 -- Cursor Declaration
645
646 -- Check if GT table has any sources
647 CURSOR c_gt_sources
648 IS
649 SELECT 'x'
650 FROM xla_evt_class_sources_gt
651 WHERE application_id = p_application_id
652 AND entity_code = p_entity_code
653 AND event_class_code = p_event_class_code;
654
655 BEGIN
656
657 l_application_id := p_application_id;
658 l_entity_code := p_entity_code;
659 l_event_class_code := p_event_class_code;
660
661 IF (g_log_level is NULL) THEN
662 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
663 END IF;
664
665 IF (g_log_level is NULL) THEN
666 g_log_enabled := fnd_log.test
667 (log_level => g_log_level
668 ,module => C_DEFAULT_MODULE);
669 END IF;
670
671 g_trace_label :='Validate_Sources';
672 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
673 trace
674 (p_msg => 'Begin'
675 ,p_level => C_LEVEL_PROCEDURE);
676 trace
677 (p_msg => 'p_application_id = ' ||TO_CHAR(p_application_id)
678 ,p_level => C_LEVEL_PROCEDURE);
679 trace
680 (p_msg => 'p_entity_code = '||p_entity_code
681 ,p_level => C_LEVEL_PROCEDURE);
682 trace
683 (p_msg => 'p_event_class_code = ' ||p_event_class_code
684 ,p_level => C_LEVEL_PROCEDURE);
685 END IF;
686
687 -- Insert all sources that are assigned to the event class into the GT table
688 INSERT INTO xla_evt_class_sources_gt
689 (application_id
690 ,entity_code
691 ,event_class_code
692 ,source_application_id
693 ,source_code
694 ,source_datatype_code,source_level_code)
695 (SELECT e.application_id
696 ,e.entity_code
697 ,e.event_class_code
698 ,e.source_application_id
699 ,e.source_code
700 ,decode(s.datatype_code,'N','NUMBER',
701 'C','VARCHAR2', 'D','DATE') source_datatype_code,
702 decode(s.translated_flag,'N',
703 decode(e.source_code,'LANGUAGE',
704 decode(e.level_code,'H','HEADER_MLS','L','LINE_MLS'),
705 decode(e.level_code,'H',
706 'HEADER','L','LINE')),
707 'Y',
708 decode(e.level_code,'H','HEADER_MLS','L','LINE_MLS'))
709 source_level_code
710 FROM xla_event_sources e, xla_sources_b s
711 WHERE e.source_application_id = s.application_id
712 AND e.source_code = s.source_code
713 AND e.source_type_code = s.source_type_code
714 AND e.application_id = p_application_id
715 AND e.entity_code = p_entity_code
716 AND e.event_class_code = p_event_class_code);
717
718 OPEN c_gt_sources;
719 FETCH c_gt_sources
720 INTO l_exist;
721 IF c_gt_sources%found THEN
722
723 -- Call the function to validate all sources in the GT table
724 IF NOT Xla_extract_integrity_pkg.validate_sources_with_extract
725 (p_application_id => l_application_id
726 ,p_entity_code => l_entity_code
727 ,p_event_class_code => l_event_class_code) THEN
728 l_return := FALSE;
729 END IF;
730
731 END IF;
732 CLOSE c_gt_sources;
733
734 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
735 trace
736 (p_msg => 'End'
737 ,p_level => C_LEVEL_PROCEDURE);
738 END IF;
739
740 RETURN l_return;
741
742 EXCEPTION
743 WHEN xla_exceptions_pkg.application_exception THEN
744 RAISE;
745 WHEN OTHERS THEN
746 xla_exceptions_pkg.raise_message
747 (p_location => 'xla_extract_integrity_pkg.validate_sources');
748 END Validate_sources; -- end of function
749
750 /*======================================================================+
751 | |
752 | Public Function |
753 | |
754 | Validate_sources_with_extract |
755 | |
756 | This routine is called to validate the sources with extract objects |
757 | |
758 +======================================================================*/
759 FUNCTION Validate_sources_with_extract
760 (p_application_id IN NUMBER
761 ,p_entity_code IN VARCHAR2
762 ,p_event_class_code IN VARCHAR2
763 ,p_amb_context_code IN VARCHAR2
764 ,p_product_rule_type_code IN VARCHAR2
765 ,p_product_rule_code IN VARCHAR2)
766 RETURN BOOLEAN
767 IS
768
769 -- Variable Declaration
770 l_application_id NUMBER(15);
771 l_entity_code VARCHAR2(30);
772 l_event_class_code VARCHAR2(30);
773 l_amb_context_code VARCHAR2(30);
774 l_product_rule_code VARCHAR2(30);
775 l_product_rule_type_code VARCHAR2(1);
776 l_return BOOLEAN := TRUE;
777 l_exist VARCHAR2(1) := NULL;
778
779 -- Variables of type Array
780 l_array_pop_source_appl_id t_array_id;
781 l_array_pop_source_code t_array_codes;
782 l_array_pop_object_name t_array_codes;
783 l_array_pop_object_type t_array_codes;
784 l_array_pop_pop_flag t_array_type_codes;
785 l_array_pop_col_datatype t_array_codes;
786
787 l_array_ref_pop_source_appl_id t_array_id;
788 l_array_ref_pop_source_code t_array_codes;
789 l_array_ref_pop_object_name t_array_codes;
790 l_array_ref_pop_object_type t_array_codes;
791 l_array_ref_pop_pop_flag t_array_type_codes;
792 l_array_ref_pop_col_datatype t_array_codes;
793 l_array_ref_pop_join_condition t_array_vl2000;
794 l_array_ref_pop_linked_obj t_array_codes;
795
796 l_array_source_appl_id t_array_id;
797 l_array_source_code t_array_codes;
798 l_array_object_name t_array_codes;
799 l_array_object_type t_array_codes;
800 l_array_pop_flag t_array_type_codes;
801 l_array_col_datatype t_array_codes;
802
803 l_array_ref_source_appl_id t_array_id;
804 l_array_ref_source_code t_array_codes;
805 l_array_ref_object_name t_array_codes;
806 l_array_ref_object_type t_array_codes;
807 l_array_ref_pop_flag t_array_type_codes;
808 l_array_ref_col_datatype t_array_codes;
809 l_array_ref_join_condition t_array_vl2000;
810 l_array_ref_linked_obj t_array_codes;
811
812 l_array_dt_source_appl_id t_array_id;
813 l_array_dt_source_code t_array_codes;
814 l_array_dt_object_name t_array_codes;
815 l_array_dt_object_type t_array_codes;
816 l_array_dt_pop_flag t_array_type_codes;
817 l_array_dt_col_datatype t_array_codes;
818
819 l_array_ref_dt_source_appl_id t_array_id;
820 l_array_ref_dt_source_code t_array_codes;
821 l_array_ref_dt_object_name t_array_codes;
822 l_array_ref_dt_object_type t_array_codes;
823 l_array_ref_dt_pop_flag t_array_type_codes;
824 l_array_ref_dt_col_datatype t_array_codes;
825 l_array_ref_dt_join_condition t_array_vl2000;
826
827 -- Cursor Declaration
828
829 -- Get all extract objects for the sources whose data type match
830 -- and the always populated flag is "Yes"
831 CURSOR c_always_pop
832 IS
833 SELECT g.source_application_id, g.source_code,
834 o.object_name extract_object_name,
835 o.object_type_code extract_object_type,
836 o.always_populated_flag extract_object_pop_flag,
837 g.source_datatype_code column_datatype_code
838 FROM xla_evt_class_sources_gt g, xla_extract_objects o,
839 xla_extract_objects_gt og
840 WHERE g.application_id = o.application_id
841 AND g.entity_code = o.entity_code
842 AND g.event_class_code = o.event_class_code
843 AND g.source_level_code = o.object_type_code
844 AND g.source_application_id = o.application_id
845 AND og.object_name = o.object_name
846 AND EXISTS (
847 SELECT 1
848 FROM dba_tab_columns t
849 WHERE og.owner = t.owner
850 AND o.object_name = t.table_name
851 AND t.column_name = g.source_code
852 AND DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = G.SOURCE_DATATYPE_CODE
853 )
854 AND g.application_id = p_application_id
855 AND g.entity_code = p_entity_code
856 AND g.event_class_code = p_event_class_code
857 AND o.always_populated_flag = 'Y'
858 AND g.extract_object_name IS NULL;
859
860
861 -- Get all reference objects for the sources whose data type match
862 -- and the always populated flag is "Yes"
863 CURSOR c_ref_always_pop
864 IS
865 SELECT g.source_application_id, g.source_code, r.reference_object_name extract_object_name,
866 o.object_type_code extract_object_type,
867 r.always_populated_flag extract_object_pop_flag,
868 g.source_datatype_code column_datatype_code,
869 r.join_condition, r.linked_to_ref_obj_name
870 FROM xla_evt_class_sources_gt g, xla_reference_objects r, xla_extract_objects o,
871 xla_reference_objects_gt og
872 WHERE g.application_id = r.application_id
873 AND g.entity_code = r.entity_code
874 AND g.event_class_code = r.event_class_code
875 AND g.source_application_id = r.reference_object_appl_id
876 AND g.source_level_code = o.object_type_code
877 AND r.application_id = o.application_id
878 AND r.entity_code = o.entity_code
879 AND r.event_class_code = o.event_class_code
880 AND r.object_name = o.object_name
881 AND og.reference_object_name = r.reference_object_name
882 AND EXISTS (
883 SELECT 1
884 FROM dba_tab_columns t
885 WHERE og.owner = t.owner
886 AND r.reference_object_name = t.table_name
887 AND t.column_name = g.source_code
888 AND DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) = g.source_datatype_code
889 )
890 AND g.application_id = p_application_id
891 AND g.entity_code = p_entity_code
892 AND g.event_class_code = p_event_class_code
893 AND r.always_populated_flag = 'Y'
894 AND g.extract_object_name IS NULL;
895
896
897 -- Get all extract objects for the sources whose data type match
898 -- and the always populated flag is "No"
899 CURSOR c_same_datatype
900 IS
901 SELECT g.source_application_id, g.source_code,
902 o.object_name extract_object_name,
903 o.object_type_code extract_object_type,
904 o.always_populated_flag extract_object_pop_flag,
905 g.source_datatype_code column_datatype_code
906 FROM xla_evt_class_sources_gt g, xla_extract_objects o,
907 xla_extract_objects_gt og
908 WHERE g.application_id = o.application_id
909 AND g.entity_code = o.entity_code
910 AND g.event_class_code = o.event_class_code
911 AND g.source_level_code = o.object_type_code
912 AND g.source_application_id = o.application_id
913 AND og.object_name = o.object_name
914 AND EXISTS (
915 SELECT 1
916 FROM dba_tab_columns t
917 WHERE og.owner = t.owner
918 AND o.object_name = t.table_name
919 AND t.column_name = g.source_code
920 AND DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = g.source_datatype_code
921 )
922 AND g.application_id = p_application_id
923 AND g.entity_code = p_entity_code
924 AND g.event_class_code = p_event_class_code
925 AND g.extract_object_name IS NULL;
926
927 -- Get all reference objects for the sources whose data type match
928 -- and the always populated flag is "No"
929 CURSOR c_ref_same_datatype
930 IS
931 SELECT g.source_application_id, g.source_code,
932 r.reference_object_name extract_object_name,
933 o.object_type_code extract_object_type,
934 r.always_populated_flag extract_object_pop_flag,
935 g.source_datatype_code column_datatype_code,
936 r.join_condition, r.linked_to_ref_obj_name
937 FROM xla_evt_class_sources_gt g, xla_reference_objects r, xla_extract_objects o,
938 xla_reference_objects_gt og
939 WHERE g.application_id = r.application_id
940 AND g.entity_code = r.entity_code
941 AND g.event_class_code = r.event_class_code
942 AND g.source_application_id = r.reference_object_appl_id
943 AND g.source_level_code = o.object_type_code
944 AND r.application_id = o.application_id
945 AND r.entity_code = o.entity_code
946 AND r.event_class_code = o.event_class_code
947 AND r.object_name = o.object_name
948 AND og.reference_object_name = r.reference_object_name
949 AND EXISTS (
950 SELECT 1
951 FROM dba_tab_columns t
952 WHERE og.owner = t.owner
953 AND r.reference_object_name = t.table_name
954 AND t.column_name = g.source_code
955 AND DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) = g.source_datatype_code
956 )
957 AND g.application_id = p_application_id
958 AND g.entity_code = p_entity_code
959 AND g.event_class_code = p_event_class_code
960 AND g.extract_object_name IS NULL;
961
962
963 -- Get remainder of extract objects for the sources whose data type do not match
964 CURSOR c_diff_datatype
965 IS
966 SELECT DISTINCT
967 g.source_application_id, g.source_code,
968 o.object_name extract_object_name,
969 o.object_type_code extract_object_type,
970 o.always_populated_flag extract_object_pop_flag,
971 -- 4713242 Performance Fix
972 (SELECT DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) COLUMN_DATATYPE_CODE
973 FROM dba_tab_columns T
974 WHERE og.owner = t.owner
975 AND o.object_name = t.table_name
976 AND t.column_name = g.source_code)
977 FROM xla_evt_class_sources_gt g, xla_extract_objects o,
978 xla_extract_objects_gt og
979 WHERE g.application_id = o.application_id
980 AND g.entity_code = o.entity_code
981 AND g.event_class_code = o.event_class_code
982 AND g.source_level_code = o.object_type_code
983 AND g.source_application_id = o.application_id
984 AND og.object_name = o.object_name
985 AND g.application_id = p_application_id
986 AND g.entity_code = p_entity_code
987 AND g.event_class_code = p_event_class_code
988 AND g.extract_object_name IS NULL
989 AND EXISTS (SELECT 1
990 FROM dba_tab_columns t
991 WHERE og.owner = t.owner
992 AND o.object_name = t.table_name
993 AND t.column_name = g.source_code);
994
995 -- Get remainder of reference objects for the sources whose data type do not match
996 CURSOR c_ref_diff_datatype
997 IS
998 SELECT DISTINCT g.source_application_id
999 ,g.source_code
1000 ,r.reference_object_name extract_object_name
1001 ,o.object_type_code extract_object_type
1002 ,o.always_populated_flag extract_object_pop_flag
1003 -- 4713242 Performance Fix
1004 ,(SELECT DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) COLUMN_DATATYPE_CODE
1005 FROM dba_tab_columns T
1006 WHERE og.owner = t.owner
1007 AND r.reference_object_name = t.table_name
1008 AND t.column_name = g.source_code)
1009 ,r.join_condition
1010 FROM xla_evt_class_sources_gt g
1011 ,xla_reference_objects r
1012 ,xla_extract_objects o
1013 ,xla_reference_objects_gt og
1014 WHERE g.application_id = r.application_id
1015 AND g.entity_code = r.entity_code
1016 AND g.event_class_code = r.event_class_code
1017 AND g.source_level_code = o.object_type_code
1018 AND r.application_id = o.application_id
1019 AND r.entity_code = o.entity_code
1020 AND r.event_class_code = o.event_class_code
1021 AND r.object_name = o.object_name
1022 AND og.reference_object_name = r.reference_object_name
1023 AND g.application_id = p_application_id
1024 AND g.entity_code = p_entity_code
1025 AND g.event_class_code = p_event_class_code
1026 AND g.extract_object_name IS NULL
1027 AND EXISTS (SELECT 1
1028 FROM dba_tab_columns t
1029 WHERE og.owner = t.owner
1030 AND r.reference_object_name = t.table_name
1031 AND t.column_name = g.source_code);
1032
1033
1034 -- Get all sources from GT table with null extract object
1035 CURSOR c_null_obj
1036 IS
1037 SELECT source_application_id, source_code, source_level_code
1038 FROM xla_evt_class_sources_gt g
1039 WHERE g.application_id = p_application_id
1040 AND g.entity_code = p_entity_code
1041 AND g.event_class_code = p_event_class_code
1042 AND extract_object_name IS NULL;
1043
1044 l_null_obj c_null_obj%rowtype;
1045
1046 -- Get all sources from GT table whose datatype does not match with column datatype
1047 CURSOR c_datatype
1048 IS
1049 SELECT source_application_id, source_code, extract_object_name,
1050 extract_object_type_code
1051 FROM xla_evt_class_sources_gt g
1052 WHERE source_datatype_code <> column_datatype_code
1053 AND extract_object_name IS NOT NULL
1054 AND g.application_id = p_application_id
1055 AND g.entity_code = p_entity_code
1056 AND g.event_class_code = p_event_class_code;
1057
1058 l_datatype c_datatype%rowtype;
1059
1060 BEGIN
1061
1062 l_application_id := p_application_id;
1063 l_entity_code := p_entity_code;
1064 l_event_class_code := p_event_class_code;
1065 l_amb_context_code := p_amb_context_code;
1066 l_product_rule_code := p_product_rule_code;
1067 l_product_rule_type_code := p_product_rule_type_code;
1068
1069 g_trace_label :='Validate_sources_with_extract';
1070
1071 IF (g_log_level is NULL) THEN
1072 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1073 END IF;
1074
1075 IF (g_log_level is NULL) THEN
1076 g_log_enabled := fnd_log.test
1077 (log_level => g_log_level
1078 ,module => C_DEFAULT_MODULE);
1079 END IF;
1080
1081 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
1082 trace
1083 (p_msg => 'Begin'
1084 ,p_level => C_LEVEL_PROCEDURE);
1085 trace
1086 (p_msg => 'p_application_id = ' ||TO_CHAR(p_application_id)
1087 ,p_level => C_LEVEL_PROCEDURE);
1088 trace
1089 (p_msg => 'p_entity_code = '||p_entity_code
1090 ,p_level => C_LEVEL_PROCEDURE);
1091 trace
1092 (p_msg => 'p_event_class_code = ' ||p_event_class_code
1093 ,p_level => C_LEVEL_PROCEDURE);
1094 trace
1095 (p_msg => 'p_amb_context_code = '||p_amb_context_code
1096 ,p_level => C_LEVEL_PROCEDURE);
1097 trace
1098 (p_msg => 'p_product_rule_type_code = ' ||p_product_rule_type_code
1099 ,p_level => C_LEVEL_PROCEDURE);
1100 trace
1101 (p_msg => 'p_product_rule_code = ' ||p_product_rule_code
1102 ,p_level => C_LEVEL_PROCEDURE);
1103 END IF;
1104
1105 -- Get all extract objects which are valid with the source definition
1106 -- and the data type of source matches the column data type
1107 -- the extract object is always populated
1108
1109 OPEN c_always_pop;
1110 FETCH c_always_pop
1111 BULK COLLECT INTO l_array_pop_source_appl_id, l_array_pop_source_code,
1112 l_array_pop_object_name,l_array_pop_object_type,
1113 l_array_pop_pop_flag, l_array_pop_col_datatype;
1114
1115 -- Bulk update the GT table with the extract object name for each source
1116 IF l_array_pop_source_code.COUNT > 0 THEN
1117 FORALL i IN l_array_pop_source_code.FIRST..l_array_pop_source_code.LAST
1118 UPDATE xla_evt_class_sources_gt gt
1119 SET gt.extract_object_name = l_array_pop_object_name(i),
1120 gt.extract_object_type_code = l_array_pop_object_type(i),
1121 gt.always_populated_flag = l_array_pop_pop_flag(i),
1122 gt.column_datatype_code = l_array_pop_col_datatype(i),
1123 gt.reference_object_flag = C_REF_OBJECT_FLAG_N
1124 WHERE gt.source_application_id = l_array_pop_source_appl_id(i)
1125 AND gt.source_code = l_array_pop_source_code(i)
1126 AND gt.application_id = p_application_id
1127 AND gt.entity_code = p_entity_code
1128 AND gt.event_class_code = p_event_class_code;
1129 END IF;
1130 CLOSE c_always_pop;
1131
1132 OPEN c_ref_always_pop;
1133 FETCH c_ref_always_pop
1134 BULK COLLECT INTO l_array_ref_pop_source_appl_id,
1135 l_array_ref_pop_source_code, l_array_ref_pop_object_name,
1136 l_array_ref_pop_object_type, l_array_ref_pop_pop_flag,
1137 l_array_ref_pop_col_datatype, l_array_ref_pop_join_condition
1138 ,l_array_ref_pop_linked_obj;
1139
1140 -- Bulk update the GT table with the reference object name for each source
1141 IF l_array_ref_pop_source_code.COUNT > 0 THEN
1142 FORALL i IN l_array_ref_pop_source_code.FIRST..l_array_ref_pop_source_code.LAST
1143 UPDATE xla_evt_class_sources_gt gt
1144 SET gt.extract_object_name = l_array_ref_pop_object_name(i),
1145 gt.extract_object_type_code = l_array_ref_pop_object_type(i),
1146 gt.always_populated_flag = l_array_ref_pop_pop_flag(i),
1147 gt.column_datatype_code = l_array_ref_pop_col_datatype(i),
1148 gt.reference_object_flag = C_REF_OBJECT_FLAG_Y,
1149 gt.join_condition = l_array_ref_pop_join_condition(i)
1150 WHERE gt.source_application_id = l_array_ref_pop_source_appl_id(i)
1151 AND gt.source_code = l_array_ref_pop_source_code(i)
1152 AND gt.application_id = p_application_id
1153 AND gt.entity_code = p_entity_code
1154 AND gt.event_class_code = p_event_class_code
1155 AND l_array_ref_pop_linked_obj(i) IS NULL;
1156
1157 FORALL i IN l_array_ref_pop_source_code.FIRST..l_array_ref_pop_source_code.LAST
1158 UPDATE xla_evt_class_sources_gt gt
1159 SET gt.extract_object_name = l_array_ref_pop_object_name(i),
1160 gt.extract_object_type_code = l_array_ref_pop_object_type(i),
1161 gt.always_populated_flag = l_array_ref_pop_pop_flag(i),
1162 gt.column_datatype_code = l_array_ref_pop_col_datatype(i),
1163 gt.reference_object_flag = C_REF_OBJECT_FLAG_Y,
1164 gt.join_condition = l_array_ref_pop_join_condition(i)
1165 WHERE gt.source_application_id = l_array_ref_pop_source_appl_id(i)
1166 AND gt.source_code = l_array_ref_pop_source_code(i)
1167 AND gt.application_id = p_application_id
1168 AND gt.entity_code = p_entity_code
1169 AND gt.event_class_code = p_event_class_code
1170 AND gt.extract_object_name IS NULL
1171 AND l_array_ref_pop_linked_obj(i) IS NOT NULL;
1172 END IF;
1173 CLOSE c_ref_always_pop;
1174
1175
1176 -- Get all extract objects which are valid with the source definition
1177 -- and the data type of source matches the column data type
1178 -- and the extract object is not always populated
1179 OPEN c_same_datatype;
1180 FETCH c_same_datatype
1181 BULK COLLECT INTO l_array_source_appl_id, l_array_source_code,
1182 l_array_object_name, l_array_object_type,
1183 l_array_pop_flag, l_array_col_datatype;
1184
1185 -- Bulk update the GT table with the extract object name for each source
1186 IF l_array_source_code.COUNT > 0 THEN
1187 FORALL i IN l_array_source_code.FIRST..l_array_source_code.LAST
1188 UPDATE xla_evt_class_sources_gt gt
1189 SET gt.extract_object_name = l_array_object_name(i),
1190 gt.extract_object_type_code = l_array_object_type(i),
1191 gt.always_populated_flag = l_array_pop_flag(i),
1192 gt.column_datatype_code = l_array_col_datatype(i),
1193 gt.reference_object_flag = C_REF_OBJECT_FLAG_N
1194 WHERE gt.source_application_id = l_array_source_appl_id(i)
1195 AND gt.source_code = l_array_source_code(i)
1196 AND gt.application_id = p_application_id
1197 AND gt.entity_code = p_entity_code
1198 AND gt.event_class_code = p_event_class_code;
1199 END IF;
1200 CLOSE c_same_datatype;
1201
1202 -- Get all reference objects which are valid with the source definition
1203 -- and the data type of source matches the column data type
1204 -- and the extract object is not always populated
1205 OPEN c_ref_same_datatype;
1206 FETCH c_ref_same_datatype
1207 BULK COLLECT INTO l_array_ref_source_appl_id,
1208 l_array_ref_source_code, l_array_ref_object_name,
1209 l_array_ref_object_type, l_array_ref_pop_flag,
1210 l_array_ref_col_datatype, l_array_ref_join_condition,
1211 l_array_ref_linked_obj;
1212
1213 -- Bulk update the GT table with the reference object name for each source
1214 IF l_array_ref_source_code.COUNT > 0 THEN
1215 FORALL i IN l_array_ref_source_code.FIRST..l_array_ref_source_code.LAST
1216 UPDATE xla_evt_class_sources_gt gt
1217 SET gt.extract_object_name = l_array_ref_object_name(i),
1218 gt.extract_object_type_code = l_array_ref_object_type(i),
1219 gt.always_populated_flag = l_array_ref_pop_flag(i),
1220 gt.column_datatype_code = l_array_ref_col_datatype(i),
1221 gt.reference_object_flag = C_REF_OBJECT_FLAG_Y,
1222 gt.join_condition = l_array_ref_join_condition(i)
1223 WHERE gt.source_application_id = l_array_ref_source_appl_id(i)
1224 AND gt.source_code = l_array_ref_source_code(i)
1225 AND gt.application_id = p_application_id
1226 AND gt.entity_code = p_entity_code
1227 AND gt.event_class_code = p_event_class_code
1228 AND l_array_ref_linked_obj(i) IS NULL;
1229 FORALL i IN l_array_ref_source_code.FIRST..l_array_ref_source_code.LAST
1230 UPDATE xla_evt_class_sources_gt gt
1231 SET gt.extract_object_name = l_array_ref_object_name(i),
1232 gt.extract_object_type_code = l_array_ref_object_type(i),
1233 gt.always_populated_flag = l_array_ref_pop_flag(i),
1234 gt.column_datatype_code = l_array_ref_col_datatype(i),
1235 gt.reference_object_flag = C_REF_OBJECT_FLAG_Y,
1236 gt.join_condition = l_array_ref_join_condition(i)
1237 WHERE gt.source_application_id = l_array_ref_source_appl_id(i)
1238 AND gt.source_code = l_array_ref_source_code(i)
1239 AND gt.application_id = p_application_id
1240 AND gt.entity_code = p_entity_code
1241 AND gt.event_class_code = p_event_class_code
1242 AND gt.extract_object_name IS NULL
1243 AND l_array_ref_linked_obj(i) IS NOT NULL;
1244 END IF;
1245 CLOSE c_ref_same_datatype;
1246
1247
1248 -- Get all extract objects which are valid with the source definition
1249 -- but the data type of source may not match the column data type
1250
1251 OPEN c_diff_datatype;
1252 FETCH c_diff_datatype
1253 BULK COLLECT INTO l_array_dt_source_appl_id, l_array_dt_source_code,
1254 l_array_dt_object_name, l_array_dt_object_type,
1255 l_array_dt_pop_flag, l_array_dt_col_datatype;
1256
1257 -- Bulk update the GT table with the extract object name for each source
1258 IF l_array_dt_source_code.COUNT > 0 THEN
1259 FORALL j IN l_array_dt_source_code.FIRST..l_array_dt_source_code.LAST
1260 UPDATE xla_evt_class_sources_gt gt
1261 SET gt.extract_object_name = l_array_dt_object_name(j),
1262 gt.extract_object_type_code = l_array_dt_object_type(j),
1263 gt.always_populated_flag = l_array_dt_pop_flag(j),
1264 gt.column_datatype_code = l_array_dt_col_datatype(j),
1265 gt.reference_object_flag = C_REF_OBJECT_FLAG_N
1266 WHERE gt.source_application_id = l_array_dt_source_appl_id(j)
1267 AND gt.source_code = l_array_dt_source_code(j)
1268 AND gt.application_id = p_application_id
1269 AND gt.entity_code = p_entity_code
1270 AND gt.event_class_code = p_event_class_code;
1271 END IF;
1272 CLOSE c_diff_datatype;
1273
1274 -- Get all reference objects which are valid with the source definition
1275 -- but the data type of source may not match the column data type
1276
1277 OPEN c_ref_diff_datatype;
1278 FETCH c_ref_diff_datatype
1279 BULK COLLECT INTO l_array_ref_dt_source_appl_id, l_array_ref_dt_source_code,
1280 l_array_ref_dt_object_name, l_array_ref_dt_object_type,
1281 l_array_ref_dt_pop_flag, l_array_ref_dt_col_datatype,
1282 l_array_ref_dt_join_condition;
1283
1284 -- Bulk update the GT table with the reference object name for each source
1285 IF l_array_ref_dt_source_code.COUNT > 0 THEN
1286 FORALL j IN l_array_ref_dt_source_code.FIRST..l_array_ref_dt_source_code.LAST
1287 UPDATE xla_evt_class_sources_gt gt
1288 SET gt.extract_object_name = l_array_ref_dt_object_name(j),
1289 gt.extract_object_type_code = l_array_ref_dt_object_type(j),
1290 gt.always_populated_flag = l_array_ref_dt_pop_flag(j),
1291 gt.column_datatype_code = l_array_ref_dt_col_datatype(j),
1292 gt.reference_object_flag = C_REF_OBJECT_FLAG_Y,
1293 gt.join_condition = l_array_ref_dt_join_condition(j)
1294 WHERE gt.source_application_id = l_array_ref_dt_source_appl_id(j)
1295 AND gt.source_code = l_array_ref_dt_source_code(j)
1296 AND gt.application_id = p_application_id
1297 AND gt.entity_code = p_entity_code
1298 AND gt.event_class_code = p_event_class_code;
1299 END IF;
1300 CLOSE c_ref_diff_datatype;
1301
1302
1303 -- Error all sources that do not exist in the right extract object
1304 OPEN c_null_obj;
1305 LOOP
1306 FETCH c_null_obj
1307 INTO l_null_obj;
1308 EXIT WHEN c_null_obj%notfound;
1309 Xla_amb_setup_err_pkg.stack_error
1310 (p_message_name => 'XLA_AB_SRC_NOT_DEFINED_IN_EXT'
1311 ,p_message_type => 'E'
1312 ,p_message_category => 'EXTRACT_SOURCE'
1313 ,p_category_sequence => 4
1314 ,p_application_id => l_application_id
1315 ,p_entity_code => l_entity_code
1316 ,p_event_class_code => l_event_class_code
1317 ,p_amb_context_code => l_amb_context_code
1318 ,p_product_rule_type_code => l_product_rule_type_code
1319 ,p_product_rule_code => l_product_rule_code
1320 ,p_source_application_id => l_null_obj.source_application_id
1321 ,p_source_code => l_null_obj.source_code
1322 ,p_source_type_code => 'S'
1323 ,p_extract_object_type => l_null_obj.source_level_code);
1324
1325 l_return := FALSE;
1326 END LOOP;
1327 CLOSE c_null_obj;
1328
1329 -- Error all sources that do not match the corresponding column datatype
1330 OPEN c_datatype;
1331 LOOP
1332 FETCH c_datatype
1333 INTO l_datatype;
1334 EXIT WHEN c_datatype%notfound;
1335 Xla_amb_setup_err_pkg.stack_error
1336 (p_message_name => 'XLA_AB_SRC_DATATYPE_NOT_MATCH'
1337 ,p_message_type => 'E'
1338 ,p_message_category => 'EXTRACT_SOURCE'
1339 ,p_category_sequence => 4
1340 ,p_application_id => l_application_id
1341 ,p_entity_code => l_entity_code
1342 ,p_event_class_code => l_event_class_code
1343 ,p_amb_context_code => l_amb_context_code
1344 ,p_product_rule_type_code => l_product_rule_type_code
1345 ,p_product_rule_code => l_product_rule_code
1346 ,p_source_application_id => l_datatype.source_application_id
1347 ,p_source_code => l_datatype.source_code
1348 ,p_source_type_code => 'S'
1349 ,p_extract_object_name => l_datatype.extract_object_name
1350 ,p_extract_object_type => l_datatype.extract_object_type_code);
1351
1352 l_return := FALSE;
1353 END LOOP;
1354 CLOSE c_datatype;
1355
1356 -- Check primary keys for the extract objects when called from an AAD
1357
1358 IF p_product_rule_code IS NOT NULL THEN
1359 IF NOT Chk_primary_keys_exist
1360 (p_application_id => l_application_id
1361 ,p_entity_code => l_entity_code
1362 ,p_event_class_code => l_event_class_code
1363 ,p_amb_context_code => l_amb_context_code
1364 ,p_product_rule_type_code => l_product_rule_type_code
1365 ,p_product_rule_code => l_product_rule_code) THEN
1366
1367 l_return := FALSE;
1368
1369 END IF;
1370 END IF;
1371
1372 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
1373 trace
1374 (p_msg => 'End'
1375 ,p_level => C_LEVEL_PROCEDURE);
1376 END IF;
1377
1378 RETURN l_return;
1379
1380 EXCEPTION
1381 WHEN xla_exceptions_pkg.application_exception THEN
1382 RAISE;
1383 WHEN OTHERS THEN
1384 xla_exceptions_pkg.raise_message
1385 (p_location => 'xla_extract_integrity_pkg.validate_sources_with_extract');
1386 END validate_sources_with_extract; -- end of function
1387
1388 /*======================================================================+
1389 | |
1390 | Public Procedure |
1391 | |
1392 | Set_extract_object_owner |
1393 | |
1394 | This routine gets the owner for the extract object and stores it in |
1395 | a gt table |
1396 | |
1397 +======================================================================*/
1398
1399
1400 PROCEDURE Set_extract_object_owner
1401 (p_application_id IN NUMBER
1402 ,p_amb_context_code IN VARCHAR2
1403 ,p_product_rule_type_code IN VARCHAR2
1404 ,p_product_rule_code IN VARCHAR2
1405 ,p_entity_code IN VARCHAR2
1406 ,p_event_class_code IN VARCHAR2)
1407 IS
1408
1409 l_user VARCHAR2(30);
1410 l_object_name VARCHAR2(30);
1411 l_object_type VARCHAR2(30);
1412 l_syn_owner VARCHAR2(30);
1413 l_ref_object_flag VARCHAR2(1);
1414
1415 l_application_id NUMBER(15);
1416 l_entity_code VARCHAR2(30);
1417 l_event_class_code VARCHAR2(30);
1418 l_amb_context_code VARCHAR2(30);
1419 l_product_rule_code VARCHAR2(30);
1420 l_product_rule_type_code VARCHAR2(1);
1421
1422 CURSOR c_aad_objects
1423 IS
1424 SELECT distinct ext.object_name, C_REF_OBJECT_FLAG_N reference_object_flag
1425 FROM xla_extract_objects ext, xla_prod_acct_headers hdr
1426 WHERE ext.application_id = hdr.application_id
1427 AND ext.entity_code = hdr.entity_code
1428 AND ext.event_class_code = hdr.event_class_code
1429 AND hdr.application_id = p_application_id
1430 AND hdr.amb_context_code = p_amb_context_code
1431 AND hdr.product_rule_type_code = p_product_rule_type_code
1432 AND hdr.product_rule_code = p_product_rule_code
1433 UNION ALL
1434 SELECT distinct rfr.reference_object_name, C_REF_OBJECT_FLAG_Y reference_object_flag
1435 FROM xla_reference_objects rfr, xla_prod_acct_headers hdr
1436 WHERE rfr.application_id = hdr.application_id
1437 AND rfr.entity_code = hdr.entity_code
1438 AND rfr.event_class_code = hdr.event_class_code
1439 AND hdr.application_id = p_application_id
1440 AND hdr.amb_context_code = p_amb_context_code
1441 AND hdr.product_rule_type_code = p_product_rule_type_code
1442 AND hdr.product_rule_code = p_product_rule_code;
1443
1444 CURSOR c_object_type
1445 IS
1446 SELECT usr.object_type
1447 FROM user_objects usr
1448 WHERE usr.object_name = l_object_name;
1449
1450 CURSOR c_syn_owner
1451 IS
1452 SELECT syn.table_owner
1453 FROM user_synonyms syn
1454 WHERE syn.synonym_name = l_object_name;
1455
1456 BEGIN
1457
1458 g_trace_label :='Set_extract_object_owner';
1459
1460 IF (g_log_level is NULL) THEN
1461 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1462 END IF;
1463
1464 IF (g_log_level is NULL) THEN
1465 g_log_enabled := fnd_log.test
1466 (log_level => g_log_level
1467 ,module => C_DEFAULT_MODULE);
1468 END IF;
1469
1470 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
1471 trace
1472 (p_msg => 'Begin'
1473 ,p_level => C_LEVEL_PROCEDURE);
1474 trace
1475 (p_msg => 'p_application_id = ' ||TO_CHAR(p_application_id)
1476 ,p_level => C_LEVEL_PROCEDURE);
1477 trace
1478 (p_msg => 'p_entity_code = '||p_entity_code
1479 ,p_level => C_LEVEL_PROCEDURE);
1480 trace
1481 (p_msg => 'p_event_class_code = ' ||p_event_class_code
1482 ,p_level => C_LEVEL_PROCEDURE);
1483 trace
1484 (p_msg => 'p_amb_context_code = '||p_amb_context_code
1485 ,p_level => C_LEVEL_PROCEDURE);
1486 trace
1487 (p_msg => 'p_product_rule_type_code = ' ||p_product_rule_type_code
1488 ,p_level => C_LEVEL_PROCEDURE);
1489 trace
1490 (p_msg => 'p_product_rule_code = ' ||p_product_rule_code
1491 ,p_level => C_LEVEL_PROCEDURE);
1492 END IF;
1493
1494 l_application_id := p_application_id;
1495 l_entity_code := p_entity_code;
1496 l_event_class_code := p_event_class_code;
1497 l_amb_context_code := p_amb_context_code;
1498 l_product_rule_code := p_product_rule_code;
1499 l_product_rule_type_code := p_product_rule_type_code;
1500
1501 DELETE FROM xla_extract_objects_gt;
1502 DELETE FROM xla_reference_objects_gt;
1503
1504 -- Get owner for current schema
1505 SELECT user
1506 INTO l_user
1507 FROM DUAL;
1508
1509 IF p_product_rule_code is NULL THEN
1510
1511 -- Insert objects for an event class and current owner in GT table
1512 INSERT
1513 INTO xla_extract_objects_gt
1514 (object_name
1515 ,owner)
1516 SELECT ext.object_name, l_user
1517 FROM xla_extract_objects ext
1518 WHERE EXISTS (SELECT /*+ no_unnest */ 'c'
1519 FROM user_objects usr
1520 WHERE ext.object_name = usr.object_name
1521 AND usr.object_type <> 'SYNONYM' )
1522 AND ext.application_id = p_application_id
1523 AND entity_code = p_entity_code
1524 AND event_class_code = p_event_class_code;
1525
1526 -- Insert reference objects for an event class and current owner in GT table
1527 -- Assume duplicate objects are not used for an event class
1528 INSERT
1529 INTO xla_reference_objects_gt
1530 (reference_object_name
1531 ,owner)
1532 SELECT rfr.reference_object_name, l_user
1533 FROM xla_reference_objects rfr
1534 WHERE
1535 EXISTS (SELECT /*+ no_unnest */ 'c'
1536 FROM user_objects usr
1537 WHERE rfr.reference_object_name = usr.object_name
1538 AND usr.object_type <> 'SYNONYM' )
1539 AND rfr.application_id = p_application_id
1540 AND rfr.entity_code = p_entity_code
1541 AND rfr.event_class_code = p_event_class_code;
1542
1543 -- Insert objects for an event class and different owner in GT table
1544 INSERT
1545 INTO xla_extract_objects_gt
1546 (object_name
1547 ,owner)
1548 SELECT ext.object_name
1549 ,(SELECT syn.table_owner
1550 FROM user_objects usr
1551 ,user_synonyms syn
1552 WHERE ext.object_name = usr.object_name
1553 AND usr.object_name = syn.synonym_name
1554 AND usr.object_type = 'SYNONYM')
1555 FROM xla_extract_objects ext
1556 WHERE EXISTS (SELECT /*+ no_unnest */ 'c'
1557 FROM user_objects usr
1558 ,user_synonyms syn
1559 WHERE ext.object_name = usr.object_name
1560 AND usr.object_name = syn.synonym_name
1561 AND usr.object_type = 'SYNONYM')
1562 AND ext.application_id = p_application_id
1563 AND entity_code = p_entity_code
1564 AND event_class_code = p_event_class_code;
1565
1566 -- Insert objects for an event class and different owner in GT table
1567 -- Assume duplicate objects are not used for an event class
1568 INSERT
1569 INTO xla_reference_objects_gt
1570 (reference_object_name
1571 ,owner)
1572 SELECT rfr.reference_object_name
1573 ,(SELECT syn.table_owner
1574 FROM user_objects usr
1575 ,user_synonyms syn
1576 -- change rfr.object_name to rfr.reference_object_name, as told by dimple
1577 WHERE rfr.reference_object_name = usr.object_name
1578 AND usr.object_name = syn.synonym_name
1579 AND usr.object_type = 'SYNONYM')
1580 FROM xla_reference_objects rfr
1581 WHERE EXISTS (SELECT /*+ no_unnest */ 'c'
1582 FROM user_objects usr
1583 ,user_synonyms syn
1584 -- change rfr.object_name to rfr.reference_object_name, as told by dimple
1585 WHERE rfr.reference_object_name = usr.object_name
1586 AND usr.object_name = syn.synonym_name
1587 AND usr.object_type = 'SYNONYM')
1588 AND rfr.application_id = p_application_id
1589 AND rfr.entity_code = p_entity_code
1590 AND rfr.event_class_code = p_event_class_code;
1591
1592 ELSE
1593
1594 -- Insert objects for an AAD and owner in GT table
1595 OPEN c_aad_objects;
1596 LOOP
1597 FETCH c_aad_objects
1598 INTO l_object_name, l_ref_object_flag;
1599 EXIT WHEN c_aad_objects%notfound;
1600
1601 OPEN c_object_type;
1602 FETCH c_object_type
1603 INTO l_object_type;
1604
1605 IF l_object_type <> 'SYNONYM' THEN
1606 IF l_ref_object_flag = 'N' THEN
1607
1608 BEGIN
1609 INSERT
1610 INTO xla_extract_objects_gt
1611 (object_name
1612 ,owner)
1613 VALUES(l_object_name
1614 ,l_user);
1615 EXCEPTION
1616 WHEN OTHERS THEN
1617 Xla_amb_setup_err_pkg.stack_error
1618 (p_message_name => 'XLA_AB_EXT_OBJECT_ERROR'
1619 ,p_message_type => 'E'
1620 ,p_message_category => 'EXTRACT_OBJECT'
1621 ,p_category_sequence => 3
1622 ,p_application_id => l_application_id
1623 ,p_entity_code => l_entity_code
1624 ,p_event_class_code => l_event_class_code
1625 ,p_extract_object_name => l_object_name
1626 ,p_amb_context_code => l_amb_context_code
1627 ,p_product_rule_type_code => l_product_rule_type_code
1628 ,p_product_rule_code => l_product_rule_code);
1629
1630 END;
1631
1632 ELSE
1633 BEGIN
1634 INSERT
1635 INTO xla_reference_objects_gt
1636 (reference_object_name
1637 ,owner)
1638 VALUES(l_object_name
1639 ,l_user);
1640 EXCEPTION
1641 WHEN OTHERS THEN
1642 Xla_amb_setup_err_pkg.stack_error
1643 (p_message_name => 'XLA_AB_EXT_OBJECT_ERROR'
1644 ,p_message_type => 'E'
1645 ,p_message_category => 'EXTRACT_OBJECT'
1646 ,p_category_sequence => 3
1647 ,p_application_id => l_application_id
1648 ,p_entity_code => l_entity_code
1649 ,p_event_class_code => l_event_class_code
1650 ,p_extract_object_name => l_object_name
1651 ,p_amb_context_code => l_amb_context_code
1652 ,p_product_rule_type_code => l_product_rule_type_code
1653 ,p_product_rule_code => l_product_rule_code);
1654
1655 END;
1656 END IF;
1657 ELSE
1658 OPEN c_syn_owner;
1659 FETCH c_syn_owner
1660 INTO l_syn_owner;
1661
1662 IF l_ref_object_flag = 'N' THEN
1663 BEGIN
1664 INSERT
1665 INTO xla_extract_objects_gt
1666 (object_name
1667 ,owner)
1668 VALUES(l_object_name
1669 ,l_syn_owner);
1670 EXCEPTION
1671 WHEN OTHERS THEN
1672 Xla_amb_setup_err_pkg.stack_error
1673 (p_message_name => 'XLA_AB_EXT_OBJECT_ERROR'
1674 ,p_message_type => 'E'
1675 ,p_message_category => 'EXTRACT_OBJECT'
1676 ,p_category_sequence => 3
1677 ,p_application_id => l_application_id
1678 ,p_entity_code => l_entity_code
1679 ,p_event_class_code => l_event_class_code
1680 ,p_extract_object_name => l_object_name
1681 ,p_amb_context_code => l_amb_context_code
1682 ,p_product_rule_type_code => l_product_rule_type_code
1683 ,p_product_rule_code => l_product_rule_code);
1684
1685 END;
1686 ELSE
1687 BEGIN
1688 INSERT
1689 INTO xla_reference_objects_gt
1690 (reference_object_name
1691 ,owner)
1692 VALUES(l_object_name
1693 ,l_syn_owner);
1694 EXCEPTION
1695 WHEN OTHERS THEN
1696 Xla_amb_setup_err_pkg.stack_error
1697 (p_message_name => 'XLA_AB_EXT_OBJECT_ERROR'
1698 ,p_message_type => 'E'
1699 ,p_message_category => 'EXTRACT_OBJECT'
1700 ,p_category_sequence => 3
1701 ,p_application_id => l_application_id
1702 ,p_entity_code => l_entity_code
1703 ,p_event_class_code => l_event_class_code
1704 ,p_extract_object_name => l_object_name
1705 ,p_amb_context_code => l_amb_context_code
1706 ,p_product_rule_type_code => l_product_rule_type_code
1707 ,p_product_rule_code => l_product_rule_code);
1708
1709 END;
1710 END IF;
1711
1712 CLOSE c_syn_owner;
1713 END IF;
1714 CLOSE c_object_type;
1715 END LOOP;
1716 CLOSE c_aad_objects;
1717 END IF;
1718
1719
1720 EXCEPTION
1721 WHEN xla_exceptions_pkg.application_exception THEN
1722 RAISE;
1723 WHEN OTHERS THEN
1724 xla_exceptions_pkg.raise_message
1725 (p_location => 'xla_extract_integrity_pkg.set_extract_object_owner');
1726 END Set_extract_object_owner; -- end of procedure
1727
1728
1729 --=============================================================================
1730 -- *********** private procedures and functions **********
1731 --=============================================================================
1732 --=============================================================================
1733 --
1734 -- Following are the private routines:
1735 --
1736 -- 1. Chk_primary_keys_exist
1737 -- 2. Validate_accounting_sources
1738 -- 3. Create_sources
1739 -- 4. Assign_sources
1740 --
1741 --
1742 --=============================================================================
1743 /*======================================================================+
1744 | |
1745 | Private Function |
1746 | |
1747 | Chk_primary_keys_exist |
1748 | |
1749 | This routine checks if the primary keys are defined in the extract |
1750 | objects based on extract object type |
1751 | |
1752 +======================================================================*/
1753
1754
1755 FUNCTION Chk_primary_keys_exist
1756 (p_application_id IN NUMBER
1757 ,p_entity_code IN VARCHAR2
1758 ,p_event_class_code IN VARCHAR2
1759 ,p_amb_context_code IN VARCHAR2
1760 ,p_product_rule_type_code IN VARCHAR2
1761 ,p_product_rule_code IN VARCHAR2)
1762 RETURN BOOLEAN
1763 IS
1764
1765 -- Variable Declaration
1766 l_application_id NUMBER(15);
1767 l_entity_code VARCHAR2(30);
1768 l_event_class_code VARCHAR2(30);
1769 l_amb_context_code VARCHAR2(30);
1770 l_product_rule_code VARCHAR2(30);
1771 l_product_rule_type_code VARCHAR2(1);
1772 l_return BOOLEAN := TRUE;
1773
1774 -- Cursor Declaration
1775
1776 -- Note: No unnest hint has been added to the subquery based on recommendation
1777 -- from the performance team to improve performance
1778
1779 -- Get all extract objects for an AAD which do not have event_id column
1780 CURSOR c_aad_event_id
1781 IS
1782 SELECT distinct extract_object_name, extract_object_type_code
1783 FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og
1784 WHERE application_id = p_application_id
1785 AND entity_code = p_entity_code
1786 AND event_class_code = p_event_class_code
1787 AND extract_object_name IS NOT NULL
1788 AND extract_object_name = og.object_name
1789 AND NOT EXISTS (SELECT 'x'
1790 FROM dba_tab_columns t
1791 WHERE t.table_name = og.object_name
1792 AND og.owner = t.owner
1793 AND t.column_name = 'EVENT_ID'
1794 AND t.data_type = 'NUMBER');
1795 -- 4420371 AND t.NULLABLE = 'N');
1796
1797 l_aad_event_id c_aad_event_id%rowtype;
1798
1799 -- Get all extract objects for an AAD which do not have language column
1800 CURSOR c_aad_language
1801 IS
1802 SELECT distinct extract_object_name, extract_object_type_code
1803 FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og
1804 WHERE application_id = p_application_id
1805 AND entity_code = p_entity_code
1806 AND event_class_code = p_event_class_code
1807 AND extract_object_name IS NOT NULL
1808 AND extract_object_type_code IN ('HEADER_MLS','LINE_MLS')
1809 AND extract_object_name = og.object_name
1810 AND NOT EXISTS (SELECT 'x'
1811 FROM dba_tab_columns t
1812 WHERE t.table_name = og.object_name
1813 AND og.owner = t.owner
1814 AND t.column_name = 'LANGUAGE'
1815 AND t.data_type = 'VARCHAR2');
1816 -- 4420371 AND t.NULLABLE = 'N');
1817
1818 l_aad_language c_aad_language%rowtype;
1819
1820 -- Get all extract objects for an AAD which do not have line_number column
1821 CURSOR c_aad_line_number
1822 IS
1823 SELECT distinct extract_object_name, extract_object_type_code
1824 FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og
1825 WHERE application_id = p_application_id
1826 AND entity_code = p_entity_code
1827 AND event_class_code = p_event_class_code
1828 AND extract_object_name IS NOT NULL
1829 AND extract_object_type_code IN ('LINE','LINE_MLS')
1830 AND extract_object_name = og.object_name
1831 AND NOT EXISTS (SELECT 'x'
1832 FROM dba_tab_columns t
1833 WHERE t.table_name = og.object_name
1834 AND og.owner = t.owner
1835 AND t.column_name = 'LINE_NUMBER'
1836 AND t.data_type = 'NUMBER');
1837 -- 4420371 AND t.NULLABLE = 'N');
1838
1839 l_aad_line_number c_aad_line_number%rowtype;
1840
1841 -- Get all extract objects for an AAD which do not have ledger_id column
1842 CURSOR c_aad_ledger_id
1843 IS
1844 SELECT distinct extract_object_name, extract_object_type_code
1845 FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og, xla_subledgers app
1846 WHERE e.application_id = p_application_id
1847 AND e.entity_code = p_entity_code
1848 AND e.event_class_code = p_event_class_code
1849 AND e.extract_object_name IS NOT NULL
1850 AND e.extract_object_type_code IN ('LINE','LINE_MLS')
1851 AND e.extract_object_name = og.object_name
1852 AND e.application_id = app.application_id
1853 AND app.alc_enabled_flag = 'N'
1854 AND NOT EXISTS (SELECT 'x'
1855 FROM dba_tab_columns t
1856 WHERE t.table_name = og.object_name
1857 AND og.owner = t.owner
1858 AND t.column_name = 'LEDGER_ID'
1859 AND t.data_type = 'NUMBER');
1860
1861 l_aad_ledger_id c_aad_ledger_id%rowtype;
1862
1863 -- Get all extract objects for an event class which do not have event_id column
1864 CURSOR c_event_id
1865 IS
1866 SELECT e.object_name, object_type_code
1867 FROM xla_extract_objects e, xla_extract_objects_gt og
1868 WHERE application_id = p_application_id
1869 AND entity_code = p_entity_code
1870 AND event_class_code = p_event_class_code
1871 AND e.object_name = og.object_name
1872 AND NOT EXISTS (SELECT 'x'
1873 FROM dba_tab_columns t
1874 WHERE t.table_name = og.object_name
1875 AND og.owner = t.owner
1876 AND t.column_name = 'EVENT_ID'
1877 AND t.data_type = 'NUMBER')
1878 -- 4420371 AND t.nullable = 'N')
1879 AND EXISTS (SELECT 'y'
1880 FROM xla_extract_objects_gt a
1881 WHERE a.object_name = e.object_name);
1882
1883 l_event_id c_event_id%rowtype;
1884
1885 -- Get all extract objects for an event class which do not have language column
1886 CURSOR c_language
1887 IS
1888 SELECT e.object_name, object_type_code
1889 FROM xla_extract_objects e, xla_extract_objects_gt og
1890 WHERE application_id = p_application_id
1891 AND entity_code = p_entity_code
1892 AND event_class_code = p_event_class_code
1893 AND object_type_code IN ('HEADER_MLS','LINE_MLS')
1894 AND e.object_name = og.object_name
1895 AND NOT EXISTS (SELECT 'x'
1896 FROM dba_tab_columns t
1897 WHERE t.table_name = og.object_name
1898 AND og.owner = t.owner
1899 AND t.column_name = 'LANGUAGE'
1900 AND t.data_type = 'VARCHAR2')
1901 -- 4420371 AND t.nullable = 'N')
1902 AND EXISTS (SELECT 'y'
1903 FROM xla_extract_objects_gt a
1904 WHERE a.object_name = e.object_name);
1905
1906 l_language c_language%rowtype;
1907
1908 -- Get all extract objects for an event class which do not have line_number column
1909 CURSOR c_line_number
1910 IS
1911 SELECT e.object_name, object_type_code
1912 FROM xla_extract_objects e, xla_extract_objects_gt og
1913 WHERE application_id = p_application_id
1914 AND entity_code = p_entity_code
1915 AND event_class_code = p_event_class_code
1916 AND object_type_code IN ('LINE','LINE_MLS')
1917 AND e.object_name = og.object_name
1918 AND NOT EXISTS (SELECT 'x'
1919 FROM dba_tab_columns t
1920 WHERE t.table_name = og.object_name
1921 AND og.owner = t.owner
1922 AND t.column_name = 'LINE_NUMBER'
1923 AND t.data_type = 'NUMBER')
1924 -- 4420371 AND t.nullable = 'N')
1925 AND EXISTS (SELECT 'y'
1926 FROM xla_extract_objects_gt a
1927 WHERE a.object_name = e.object_name);
1928
1929 l_line_number c_line_number%rowtype;
1930
1931 -- Get all extract objects for an event class which do not have ledger_id column
1932 CURSOR c_ledger_id
1933 IS
1934 SELECT e.object_name, object_type_code
1935 FROM xla_extract_objects e, xla_extract_objects_gt og, xla_subledgers app
1936 WHERE e.application_id = p_application_id
1937 AND e.entity_code = p_entity_code
1938 AND e.event_class_code = p_event_class_code
1939 AND e.object_type_code IN ('LINE','LINE_MLS')
1940 AND e.object_name = og.object_name
1941 AND e.application_id = app.application_id
1942 AND app.alc_enabled_flag = 'N'
1943 AND NOT EXISTS (SELECT 'x'
1944 FROM dba_tab_columns t
1945 WHERE t.table_name = og.object_name
1946 AND og.owner = t.owner
1947 AND t.column_name = 'LEDGER_ID'
1948 AND t.data_type = 'NUMBER')
1949 AND EXISTS (SELECT 'y'
1950 FROM xla_extract_objects_gt a
1951 WHERE a.object_name = e.object_name);
1952
1953 l_ledger_id c_ledger_id%rowtype;
1954
1955 BEGIN
1956
1957 l_application_id := p_application_id;
1958 l_entity_code := p_entity_code;
1959 l_event_class_code := p_event_class_code;
1960 l_amb_context_code := p_amb_context_code;
1961 l_product_rule_code := p_product_rule_code;
1962 l_product_rule_type_code := p_product_rule_type_code;
1963
1964 g_trace_label :='Check_primary_keys_exist';
1965
1966 IF (g_log_level is NULL) THEN
1967 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1968 END IF;
1969
1970 IF (g_log_level is NULL) THEN
1971 g_log_enabled := fnd_log.test
1972 (log_level => g_log_level
1973 ,module => C_DEFAULT_MODULE);
1974 END IF;
1975
1976 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
1977 trace
1978 (p_msg => 'Begin'
1979 ,p_level => C_LEVEL_PROCEDURE);
1980 trace
1981 (p_msg => 'p_application_id = ' ||TO_CHAR(p_application_id)
1982 ,p_level => C_LEVEL_PROCEDURE);
1983 trace
1984 (p_msg => 'p_entity_code = '||p_entity_code
1985 ,p_level => C_LEVEL_PROCEDURE);
1986 trace
1987 (p_msg => 'p_event_class_code = ' ||p_event_class_code
1988 ,p_level => C_LEVEL_PROCEDURE);
1989 trace
1990 (p_msg => 'p_amb_context_code = '||p_amb_context_code
1991 ,p_level => C_LEVEL_PROCEDURE);
1992 trace
1993 (p_msg => 'p_product_rule_type_code = ' ||p_product_rule_type_code
1994 ,p_level => C_LEVEL_PROCEDURE);
1995 trace
1996 (p_msg => 'p_product_rule_code = ' ||p_product_rule_code
1997 ,p_level => C_LEVEL_PROCEDURE);
1998 END IF;
1999
2000 -- Validate for an AAD
2001 IF p_product_rule_code is not null then
2002
2003 -- Check if event_id exists with correct data type
2004 -- for all level extract objects
2005 OPEN c_aad_event_id;
2006 LOOP
2007 FETCH c_aad_event_id
2008 INTO l_aad_event_id;
2009 EXIT WHEN c_aad_event_id%NOTFOUND;
2010
2011 Xla_amb_setup_err_pkg.stack_error
2012 (p_message_name => 'XLA_AB_PK_EVENT_ID_NOT_DEFINED'
2013 ,p_message_type => 'E'
2014 ,p_message_category => 'EXTRACT_OBJECT'
2015 ,p_category_sequence => 3
2016 ,p_application_id => l_application_id
2017 ,p_entity_code => l_entity_code
2018 ,p_event_class_code => l_event_class_code
2019 ,p_amb_context_code => l_amb_context_code
2020 ,p_product_rule_type_code => l_product_rule_type_code
2021 ,p_product_rule_code => l_product_rule_code
2022 ,p_extract_object_name => l_aad_event_id.extract_object_name
2023 ,p_extract_object_type => l_aad_event_id.extract_object_type_code);
2024 l_return := FALSE;
2025
2026 END LOOP;
2027 CLOSE c_aad_event_id;
2028
2029 -- Check if the LANGUAGE exists with correct data type
2030 -- for header_mls and line_mls level extract objects
2031
2032 OPEN c_aad_language;
2033 LOOP
2034 FETCH c_aad_language
2035 INTO l_aad_language;
2036 EXIT WHEN c_aad_language%NOTFOUND;
2037
2038 Xla_amb_setup_err_pkg.stack_error
2039 (p_message_name => 'XLA_AB_PK_LANGUAGE_NOT_DEFINED'
2040 ,p_message_type => 'E'
2041 ,p_message_category => 'EXTRACT_OBJECT'
2042 ,p_category_sequence => 3
2043 ,p_application_id => l_application_id
2044 ,p_entity_code => l_entity_code
2045 ,p_event_class_code => l_event_class_code
2046 ,p_amb_context_code => l_amb_context_code
2047 ,p_product_rule_type_code => l_product_rule_type_code
2048 ,p_product_rule_code => l_product_rule_code
2049 ,p_extract_object_name => l_aad_language.extract_object_name
2050 ,p_extract_object_type => l_aad_language.extract_object_type_code);
2051 l_return := FALSE;
2052
2053 END LOOP;
2054 CLOSE c_aad_language;
2055
2056 -- Check if the LINE_NUMBER exists with correct data type
2057 -- for Line, line_mls and base_currency level extract objects
2058
2059 OPEN c_aad_line_number;
2060 LOOP
2061 FETCH c_aad_line_number
2062 INTO l_aad_line_number;
2063 EXIT WHEN c_aad_line_number%NOTFOUND;
2064
2065 Xla_amb_setup_err_pkg.stack_error
2066 (p_message_name => 'XLA_AB_PK_LINE_NUM_NOT_DEFINED'
2067 ,p_message_type => 'E'
2068 ,p_message_category => 'EXTRACT_OBJECT'
2069 ,p_category_sequence => 3
2070 ,p_application_id => l_application_id
2071 ,p_entity_code => l_entity_code
2072 ,p_event_class_code => l_event_class_code
2073 ,p_amb_context_code => l_amb_context_code
2074 ,p_product_rule_type_code => l_product_rule_type_code
2075 ,p_product_rule_code => l_product_rule_code
2076 ,p_extract_object_name => l_aad_line_number.extract_object_name
2077 ,p_extract_object_type => l_aad_line_number.extract_object_type_code);
2078 l_return := FALSE;
2079
2080 END LOOP;
2081 CLOSE c_aad_line_number;
2082
2083 -- Check if the LEDGER_ID exists with correct data type
2084 -- for base_currency level extract objects
2085
2086 OPEN c_aad_ledger_id;
2087 LOOP
2088 FETCH c_aad_ledger_id
2089 INTO l_aad_ledger_id;
2090 EXIT WHEN c_aad_ledger_id%NOTFOUND;
2091
2092 Xla_amb_setup_err_pkg.stack_error
2093 (p_message_name => 'XLA_AB_PK_LED_ID_NOT_DEFINED'
2094 ,p_message_type => 'E'
2095 ,p_message_category => 'EXTRACT_OBJECT'
2096 ,p_category_sequence => 3
2097 ,p_application_id => l_application_id
2098 ,p_entity_code => l_entity_code
2099 ,p_event_class_code => l_event_class_code
2100 ,p_amb_context_code => l_amb_context_code
2101 ,p_product_rule_type_code => l_product_rule_type_code
2102 ,p_product_rule_code => l_product_rule_code
2103 ,p_extract_object_name => l_aad_ledger_id.extract_object_name
2104 ,p_extract_object_type => l_aad_ledger_id.extract_object_type_code);
2105 l_return := FALSE;
2106
2107 END LOOP;
2108 CLOSE c_aad_ledger_id;
2109
2110 ELSE
2111 -- Validate for an event class
2112
2113 -- Check if event_id exists with correct data type
2114 -- for all level extract objects
2115 OPEN c_event_id;
2116 LOOP
2117 FETCH c_event_id
2118 INTO l_event_id;
2119 EXIT WHEN c_event_id%NOTFOUND;
2120
2121 Xla_amb_setup_err_pkg.stack_error
2122 (p_message_name => 'XLA_AB_PK_EVENT_ID_NOT_DEFINED'
2123 ,p_message_type => 'E'
2124 ,p_message_category => 'EXTRACT_OBJECT'
2125 ,p_category_sequence => 3
2126 ,p_application_id => l_application_id
2127 ,p_entity_code => l_entity_code
2128 ,p_event_class_code => l_event_class_code
2129 ,p_amb_context_code => l_amb_context_code
2130 ,p_product_rule_type_code => l_product_rule_type_code
2131 ,p_product_rule_code => l_product_rule_code
2132 ,p_extract_object_name => l_event_id.object_name
2133 ,p_extract_object_type => l_event_id.object_type_code);
2134 l_return := FALSE;
2135
2136 END LOOP;
2137 CLOSE c_event_id;
2138
2139 -- Check if the LANGUAGE exists with correct data type
2140 -- for header_mls and line_mls level extract objects
2141
2142 OPEN c_language;
2143 LOOP
2144 FETCH c_language
2145 INTO l_language;
2146 EXIT WHEN c_language%NOTFOUND;
2147
2148 Xla_amb_setup_err_pkg.stack_error
2149 (p_message_name => 'XLA_AB_PK_LANGUAGE_NOT_DEFINED'
2150 ,p_message_type => 'E'
2151 ,p_message_category => 'EXTRACT_OBJECT'
2152 ,p_category_sequence => 3
2153 ,p_application_id => l_application_id
2154 ,p_entity_code => l_entity_code
2155 ,p_event_class_code => l_event_class_code
2156 ,p_amb_context_code => l_amb_context_code
2157 ,p_product_rule_type_code => l_product_rule_type_code
2158 ,p_product_rule_code => l_product_rule_code
2159 ,p_extract_object_name => l_language.object_name
2160 ,p_extract_object_type => l_language.object_type_code);
2161 l_return := FALSE;
2162
2163 END LOOP;
2164 CLOSE c_language;
2165
2166 -- Check if the LINE_NUMBER exists with correct data type
2167 -- for Line, line_mls and base_currency level extract objects
2168
2169 OPEN c_line_number;
2170 LOOP
2171 FETCH c_line_number
2172 INTO l_line_number;
2173 EXIT WHEN c_line_number%NOTFOUND;
2174
2175 Xla_amb_setup_err_pkg.stack_error
2176 (p_message_name => 'XLA_AB_PK_LINE_NUM_NOT_DEFINED'
2177 ,p_message_type => 'E'
2178 ,p_message_category => 'EXTRACT_OBJECT'
2179 ,p_category_sequence => 3
2180 ,p_application_id => l_application_id
2181 ,p_entity_code => l_entity_code
2182 ,p_event_class_code => l_event_class_code
2183 ,p_amb_context_code => l_amb_context_code
2184 ,p_product_rule_type_code => l_product_rule_type_code
2185 ,p_product_rule_code => l_product_rule_code
2186 ,p_extract_object_name => l_line_number.object_name
2187 ,p_extract_object_type => l_line_number.object_type_code);
2188 l_return := FALSE;
2189
2190 END LOOP;
2191 CLOSE c_line_number;
2192
2193 -- Check if the LEDGER_ID exists with correct data type
2194 -- for base_currency level extract objects
2195
2196 OPEN c_ledger_id;
2197 LOOP
2198 FETCH c_ledger_id
2199 INTO l_ledger_id;
2200 EXIT WHEN c_ledger_id%NOTFOUND;
2201
2202 Xla_amb_setup_err_pkg.stack_error
2203 (p_message_name => 'XLA_AB_PK_LED_ID_NOT_DEFINED'
2204 ,p_message_type => 'E'
2205 ,p_message_category => 'EXTRACT_OBJECT'
2206 ,p_category_sequence => 3
2207 ,p_application_id => l_application_id
2208 ,p_entity_code => l_entity_code
2209 ,p_event_class_code => l_event_class_code
2210 ,p_amb_context_code => l_amb_context_code
2211 ,p_product_rule_type_code => l_product_rule_type_code
2212 ,p_product_rule_code => l_product_rule_code
2213 ,p_extract_object_name => l_ledger_id.object_name
2214 ,p_extract_object_type => l_ledger_id.object_type_code);
2215 l_return := FALSE;
2216
2217 END LOOP;
2218 CLOSE c_ledger_id;
2219 END IF;
2220
2221 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
2222 trace
2223 (p_msg => 'End'
2224 ,p_level => C_LEVEL_PROCEDURE);
2225 END IF;
2226
2227 RETURN l_return;
2228
2229 EXCEPTION
2230 WHEN xla_exceptions_pkg.application_exception THEN
2231 RAISE;
2232 WHEN OTHERS THEN
2233 xla_exceptions_pkg.raise_message
2234 (p_location => 'xla_extract_integrity_pkg.Chk_primary_keys_exist');
2235 END Chk_primary_keys_exist; -- end of function
2236
2237 /*======================================================================+
2238 | |
2239 | Private Function |
2240 | |
2241 | Validate_accounting_sources |
2242 | |
2243 | This routine validates the accounting source mappings for an event |
2244 | class |
2245 | |
2246 +======================================================================*/
2247
2248 FUNCTION Validate_accounting_sources
2249 (p_application_id IN NUMBER
2250 ,p_entity_code IN VARCHAR2
2251 ,p_event_class_code IN VARCHAR2)
2252 RETURN BOOLEAN
2253 IS
2254 -- Variable Declaration
2255
2256 l_application_id NUMBER(15);
2257 l_entity_code VARCHAR2(30);
2258 l_event_class_code VARCHAR2(30);
2259 l_return BOOLEAN := TRUE;
2260 l_exist VARCHAR2(1) := NULL;
2261 l_accounting_attribute_code VARCHAR2(30) := NULL;
2262
2263 -- Cursor Declaration
2264
2265 -- Get all required accounting sources which are not mapped for the event class
2266 CURSOR c_reqd_sources
2267 IS
2268 SELECT accounting_attribute_code
2269 FROM xla_acct_attributes_b a
2270 WHERE a.assignment_required_code = 'Y'
2271 AND NOT EXISTS (SELECT 'x'
2272 FROM xla_evt_class_acct_attrs e
2273 WHERE e.application_id = p_application_id
2274 AND e.event_class_code = p_event_class_code
2275 AND e.accounting_attribute_code = a.accounting_attribute_code
2276 AND e.default_flag = 'Y');
2277
2278 l_reqd_sources c_reqd_sources%rowtype;
2279
2280 -- Get all mappings groups that have atleast one accounting source from the
2281 -- group mapped to the event class
2282 CURSOR c_mapping_groups
2283 IS
2284 SELECT distinct assignment_group_code
2285 FROM xla_acct_attributes_b a
2286 WHERE assignment_group_code IS NOT NULL
2287 AND EXISTS (SELECT 'x'
2288 FROM xla_evt_class_acct_attrs e
2289 WHERE e.application_id = p_application_id
2290 AND e.event_class_code = p_event_class_code
2291 AND e.accounting_attribute_code = a.accounting_attribute_code
2292 AND e.default_flag = 'Y');
2293
2294 l_mapping_groups c_mapping_groups%rowtype;
2295
2296 -- Get all required accounting sources for the above group that are not
2297 -- mapped to the event class
2298 CURSOR c_group_sources
2299 IS
2300 SELECT accounting_attribute_code
2301 FROM xla_acct_attributes_b a
2302 WHERE a.assignment_required_code = 'G'
2303 AND a.assignment_group_code = l_mapping_groups.assignment_group_code
2304 AND NOT EXISTS (SELECT 'x'
2305 FROM xla_evt_class_acct_attrs e
2306 WHERE e.application_id = p_application_id
2307 AND e.event_class_code = p_event_class_code
2308 AND e.accounting_attribute_code = a.accounting_attribute_code
2309 AND e.default_flag = 'Y');
2310
2311 l_group_sources c_group_sources%rowtype;
2312
2313 -- Check if event class has budget or encumbrance enabled
2314 CURSOR c_ec_attrs
2315 IS
2316 SELECT allow_budgets_flag, allow_encumbrance_flag
2317 FROM xla_event_class_attrs e
2318 WHERE e.application_id = p_application_id
2319 AND e.entity_code = p_entity_code
2320 AND e.event_class_code = p_event_class_code;
2321
2322 l_ec_attrs c_ec_attrs%rowtype;
2323
2324 -- Check if event class has budget version id accounting source mapped
2325 CURSOR c_budget
2326 IS
2327 SELECT 'x'
2328 FROM xla_evt_class_acct_attrs e
2329 WHERE e.application_id = p_application_id
2330 AND e.event_class_code = p_event_class_code
2331 AND e.accounting_attribute_code = 'BUDGET_VERSION_ID'
2332 AND e.default_flag = 'Y';
2333
2334 -- Check if event class has encumbrance type id accounting source mapped
2335 /* 4458381
2336 CURSOR c_enc
2337 IS
2338 SELECT 'x'
2339 FROM xla_evt_class_acct_attrs e
2340 WHERE e.application_id = p_application_id
2341 AND e.event_class_code = p_event_class_code
2342 AND e.accounting_attribute_code = 'ENCUMBRANCE_TYPE_ID'
2343 AND e.default_flag = 'Y';
2344 */
2345
2346 -- Check if reversed distribution id 2 is mapped for the event class
2347 CURSOR c_rev_dist_2
2348 IS
2349 SELECT a.accounting_attribute_code, a.assignment_group_code,
2350 a.source_type_code, a.source_code
2351 FROM xla_evt_class_acct_attrs_fvl a
2352 WHERE a.application_id = p_application_id
2353 AND a.event_class_code = p_event_class_code
2354 AND a.accounting_attribute_code = 'REVERSED_DISTRIBUTION_ID2'
2355 AND default_flag = 'Y';
2356
2357 l_rev_dist_2 c_rev_dist_2%rowtype;
2358
2359 -- Check if distribution id 2 is mapped for the event class
2360 CURSOR c_dist_2
2361 IS
2362 SELECT 'x'
2363 FROM xla_evt_class_acct_attrs a
2364 WHERE a.application_id = p_application_id
2365 AND a.event_class_code = p_event_class_code
2366 AND a.accounting_attribute_code = 'DISTRIBUTION_IDENTIFIER_2'
2367 AND default_flag = 'Y';
2368
2369 -- Check if reversed distribution id 3 is mapped for the event class
2370 CURSOR c_rev_dist_3
2371 IS
2372 SELECT a.accounting_attribute_code, a.assignment_group_code
2373 FROM xla_evt_class_acct_attrs_fvl a
2374 WHERE a.application_id = p_application_id
2375 AND a.event_class_code = p_event_class_code
2376 AND a.accounting_attribute_code = 'REVERSED_DISTRIBUTION_ID3'
2377 AND default_flag = 'Y';
2378
2379 l_rev_dist_3 c_rev_dist_3%rowtype;
2380
2381 -- Check if distribution id 3 is mapped for the event class
2382 CURSOR c_dist_3
2383 IS
2384 SELECT 'x'
2385 FROM xla_evt_class_acct_attrs a
2386 WHERE a.application_id = p_application_id
2387 AND a.event_class_code = p_event_class_code
2388 AND a.accounting_attribute_code = 'DISTRIBUTION_IDENTIFIER_3'
2389 AND a.default_flag = 'Y';
2390
2391 -- Check if reversed distribution id 4 is mapped for the event class
2392 CURSOR c_rev_dist_4
2393 IS
2394 SELECT a.accounting_attribute_code, a.assignment_group_code
2395 FROM xla_evt_class_acct_attrs_fvl a
2396 WHERE a.application_id = p_application_id
2397 AND a.event_class_code = p_event_class_code
2398 AND a.accounting_attribute_code = 'REVERSED_DISTRIBUTION_ID4'
2399 AND default_flag = 'Y';
2400
2401 l_rev_dist_4 c_rev_dist_4%rowtype;
2402
2403 -- Check if distribution id 4 is mapped for the event class
2404 CURSOR c_dist_4
2405 IS
2406 SELECT 'x'
2407 FROM xla_evt_class_acct_attrs a
2408 WHERE a.application_id = p_application_id
2409 AND a.event_class_code = p_event_class_code
2410 AND a.accounting_attribute_code = 'DISTRIBUTION_IDENTIFIER_4'
2411 AND default_flag = 'Y';
2412
2413 -- Check if reversed distribution id 5 is mapped for the event class
2414 CURSOR c_rev_dist_5
2415 IS
2416 SELECT a.accounting_attribute_code, a.assignment_group_code
2417 FROM xla_evt_class_acct_attrs_fvl a
2418 WHERE a.application_id = p_application_id
2419 AND a.event_class_code = p_event_class_code
2420 AND a.accounting_attribute_code = 'REVERSED_DISTRIBUTION_ID5'
2421 AND a.default_flag = 'Y';
2422
2423 l_rev_dist_5 c_rev_dist_5%rowtype;
2424
2425 -- Check if distribution id 5 is mapped for the event class
2426 CURSOR c_dist_5
2427 IS
2428 SELECT 'x'
2429 FROM xla_evt_class_acct_attrs a
2430 WHERE a.application_id = p_application_id
2431 AND a.event_class_code = p_event_class_code
2432 AND a.accounting_attribute_code = 'DISTRIBUTION_IDENTIFIER_5'
2433 AND a.default_flag = 'Y';
2434
2435 -- Get all accounting attributes assignments that have sources that are
2436 -- not mapped to the event class
2437
2438 CURSOR c_sources
2439 IS
2440 SELECT s.accounting_attribute_code,
2441 s.source_type_code, s.source_code
2442 FROM xla_evt_class_acct_attrs s
2443 WHERE s.application_id = p_application_id
2444 AND s.event_class_code = p_event_class_code
2445 AND s.source_application_id = p_application_id
2446 AND s.source_type_code = 'S'
2447 AND NOT EXISTS (SELECT 'x'
2448 FROM xla_event_sources e
2449 WHERE e.application_id = s.application_id
2450 AND e.event_class_code = s.event_class_code
2451 AND e.source_application_id = s.source_application_id
2452 AND e.source_type_code = s.source_type_code
2453 AND e.source_code = s.source_code
2454 AND e.active_flag = 'Y');
2455
2456 l_sources c_sources%rowtype;
2457
2458 -- Get all accounting attributes assignments that have derived sources that are
2459 -- not mapped to the event class
2460
2461 CURSOR c_der_sources
2462 IS
2463 SELECT s.accounting_attribute_code, s.source_application_id,
2464 s.source_type_code, s.source_code
2465 FROM xla_evt_class_acct_attrs s
2466 WHERE s.application_id = p_application_id
2467 AND s.event_class_code = p_event_class_code
2468 AND s.source_application_id = p_application_id
2469 AND s.source_type_code = 'D';
2470
2471 l_der_sources c_der_sources%rowtype;
2472
2473
2474
2475 BEGIN
2476
2477 l_application_id := p_application_id;
2478 l_entity_code := p_entity_code;
2479 l_event_class_code := p_event_class_code;
2480
2481 g_trace_label :='Validate_accounting_sources';
2482
2483 IF (g_log_level is NULL) THEN
2484 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2485 END IF;
2486
2487 IF (g_log_level is NULL) THEN
2488 g_log_enabled := fnd_log.test
2489 (log_level => g_log_level
2490 ,module => C_DEFAULT_MODULE);
2491 END IF;
2492
2493 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
2494 trace
2495 (p_msg => 'Begin'
2496 ,p_level => C_LEVEL_PROCEDURE);
2497 trace
2498 (p_msg => 'p_application_id = ' ||TO_CHAR(p_application_id)
2499 ,p_level => C_LEVEL_PROCEDURE);
2500 trace
2501 (p_msg => 'p_entity_code = '||p_entity_code
2502 ,p_level => C_LEVEL_PROCEDURE);
2503 trace
2504 (p_msg => 'p_event_class_code = ' ||p_event_class_code
2505 ,p_level => C_LEVEL_PROCEDURE);
2506 END IF;
2507
2508 -- Check if all required accounting sources are mapped for the event class
2509
2510 OPEN c_reqd_sources;
2511 LOOP
2512 FETCH c_reqd_sources
2513 INTO l_reqd_sources;
2514 EXIT WHEN c_reqd_sources%notfound;
2515
2516 Xla_amb_setup_err_pkg.stack_error
2517 (p_message_name => 'XLA_AB_REQD_ACCT_SOURCES'
2518 ,p_message_type => 'E'
2519 ,p_message_category => 'ACCOUNTING_SOURCE'
2520 ,p_category_sequence => 5
2521 ,p_application_id => l_application_id
2522 ,p_entity_code => l_entity_code
2523 ,p_event_class_code => l_event_class_code
2524 ,p_accounting_source_code => l_reqd_sources.accounting_attribute_code);
2525
2526 l_return := FALSE;
2527 END LOOP;
2528 CLOSE c_reqd_sources;
2529
2530 -- Get all mapping groups that have atleast one accounting source
2531 -- mapped for the event class
2532
2533 OPEN c_mapping_groups;
2534 LOOP
2535 FETCH c_mapping_groups
2536 INTO l_mapping_groups;
2537 EXIT WHEN c_mapping_groups%NOTFOUND;
2538
2539 -- Check if all required sources for the group are mapped for
2540 -- the event class
2541
2542 OPEN c_group_sources;
2543 LOOP
2544 FETCH c_group_sources
2545 INTO l_group_sources;
2546 EXIT WHEN c_group_sources%NOTFOUND;
2547 Xla_amb_setup_err_pkg.stack_error
2548 (p_message_name => 'XLA_AB_REQD_GRP_SOURCES'
2549 ,p_message_type => 'E'
2550 ,p_message_category => 'ACCOUNTING_SOURCE'
2551 ,p_category_sequence => 5
2552 ,p_application_id => l_application_id
2553 ,p_entity_code => l_entity_code
2554 ,p_event_class_code => l_event_class_code
2555 ,p_accounting_source_code => l_group_sources.accounting_attribute_code
2556 ,p_accounting_group_code => l_mapping_groups.assignment_group_code);
2557
2558 l_return := FALSE;
2559 END LOOP;
2560 CLOSE c_group_sources;
2561
2562 END LOOP;
2563 CLOSE c_mapping_groups;
2564
2565 -- Get budget and encumbrance flag for the event class
2566
2567 OPEN c_ec_attrs;
2568 FETCH c_ec_attrs
2569 INTO l_ec_attrs;
2570
2571 IF l_ec_attrs.allow_budgets_flag = 'Y' THEN
2572
2573 -- Check if Budget Version Identifier is mapped for the
2574 -- event class
2575 OPEN c_budget;
2576 FETCH c_budget
2577 INTO l_exist;
2578 IF c_budget%NOTFOUND THEN
2579 Xla_amb_setup_err_pkg.stack_error
2580 (p_message_name => 'XLA_AB_BUDGET_ACCTG_SRC'
2581 ,p_message_type => 'E'
2582 ,p_message_category => 'ACCOUNTING_SOURCE'
2583 ,p_category_sequence => 5
2584 ,p_application_id => l_application_id
2585 ,p_entity_code => l_entity_code
2586 ,p_event_class_code => l_event_class_code
2587 ,p_accounting_source_code => 'BUDGET_VERSION_ID');
2588
2589 l_return := FALSE;
2590 END IF;
2591 CLOSE c_budget;
2592 END IF;
2593
2594 /* 4458381
2595 IF l_ec_attrs.allow_encumbrance_flag = 'Y' THEN
2596
2597 -- Check if Encumbrance Type Identifier is mapped for the
2598 -- event class
2599 OPEN c_enc;
2600 FETCH c_enc
2601 INTO l_exist;
2602 IF c_enc%NOTFOUND THEN
2603 Xla_amb_setup_err_pkg.stack_error
2604 (p_message_name => 'XLA_AB_ENC_ACCTG_SRC'
2605 ,p_message_type => 'E'
2606 ,p_message_category => 'ACCOUNTING_SOURCE'
2607 ,p_category_sequence => 5
2608 ,p_application_id => l_application_id
2609 ,p_entity_code => l_entity_code
2610 ,p_event_class_code => l_event_class_code
2611 ,p_accounting_source_code => 'ENCUMBRANCE_TYPE_ID');
2612
2613 l_return := FALSE;
2614 END IF;
2615 CLOSE c_enc;
2616 END IF;
2617 CLOSE c_ec_attrs;
2618 */
2619
2620 --
2621 -- Check if reversed distribution ids are mapped for a line type
2622 -- then the corresponding distribution ids are also mapped
2623 --
2624 OPEN c_rev_dist_2;
2625 FETCH c_rev_dist_2
2626 INTO l_rev_dist_2;
2627 IF c_rev_dist_2%found THEN
2628
2629 OPEN c_dist_2;
2630 FETCH c_dist_2
2631 INTO l_exist;
2632 IF c_dist_2%notfound THEN
2633
2634 Xla_amb_setup_err_pkg.stack_error
2635 (p_message_name => 'XLA_AB_EC_ACCT_REV_DIST_ID'
2636 ,p_message_type => 'E'
2637 ,p_message_category => 'ACCOUNTING_SOURCE'
2638 ,p_category_sequence => 5
2639 ,p_application_id => l_application_id
2640 ,p_entity_code => l_entity_code
2641 ,p_event_class_code => l_event_class_code
2642 ,p_accounting_source_code => l_rev_dist_2.accounting_attribute_code
2643 ,p_accounting_group_code => l_rev_dist_2.assignment_group_code);
2644
2645 l_return := FALSE;
2646 END IF;
2647 CLOSE c_dist_2;
2648 END IF;
2649 CLOSE c_rev_dist_2;
2650
2651 OPEN c_rev_dist_3;
2652 FETCH c_rev_dist_3
2653 INTO l_rev_dist_3;
2654 IF c_rev_dist_3%found THEN
2655
2656 OPEN c_dist_3;
2657 FETCH c_dist_3
2658 INTO l_exist;
2659 IF c_dist_3%notfound THEN
2660
2661 Xla_amb_setup_err_pkg.stack_error
2662 (p_message_name => 'XLA_AB_EC_ACCT_REV_DIST_ID'
2663 ,p_message_type => 'E'
2664 ,p_message_category => 'ACCOUNTING_SOURCE'
2665 ,p_category_sequence => 5
2666 ,p_application_id => l_application_id
2667 ,p_entity_code => l_entity_code
2668 ,p_event_class_code => l_event_class_code
2669 ,p_accounting_source_code => l_rev_dist_3.accounting_attribute_code
2670 ,p_accounting_group_code => l_rev_dist_3.assignment_group_code);
2671
2672 l_return := FALSE;
2673 END IF;
2674 CLOSE c_dist_3;
2675 END IF;
2676 CLOSE c_rev_dist_3;
2677
2678 OPEN c_rev_dist_4;
2679 FETCH c_rev_dist_4
2680 INTO l_rev_dist_4;
2681 IF c_rev_dist_4%found THEN
2682
2683 OPEN c_dist_4;
2684 FETCH c_dist_4
2685 INTO l_exist;
2686 IF c_dist_4%notfound THEN
2687
2688 Xla_amb_setup_err_pkg.stack_error
2689 (p_message_name => 'XLA_AB_EC_ACCT_REV_DIST_ID'
2690 ,p_message_type => 'E'
2691 ,p_message_category => 'ACCOUNTING_SOURCE'
2692 ,p_category_sequence => 5
2693 ,p_application_id => l_application_id
2694 ,p_entity_code => l_entity_code
2695 ,p_event_class_code => l_event_class_code
2696 ,p_accounting_source_code => l_rev_dist_4.accounting_attribute_code
2697 ,p_accounting_group_code => l_rev_dist_4.assignment_group_code);
2698
2699 l_return := FALSE;
2700 END IF;
2701 CLOSE c_dist_4;
2702 END IF;
2703 CLOSE c_rev_dist_4;
2704
2705 OPEN c_rev_dist_5;
2706 FETCH c_rev_dist_5
2707 INTO l_rev_dist_5;
2708 IF c_rev_dist_5%found THEN
2709
2710 OPEN c_dist_5;
2711 FETCH c_dist_5
2712 INTO l_exist;
2713 IF c_dist_5%notfound THEN
2714
2715 Xla_amb_setup_err_pkg.stack_error
2716 (p_message_name => 'XLA_AB_EC_ACCT_REV_DIST_ID'
2717 ,p_message_type => 'E'
2718 ,p_message_category => 'ACCOUNTING_SOURCE'
2719 ,p_category_sequence => 5
2720 ,p_application_id => l_application_id
2721 ,p_entity_code => l_entity_code
2722 ,p_event_class_code => l_event_class_code
2723 ,p_accounting_source_code => l_rev_dist_5.accounting_attribute_code
2724 ,p_accounting_group_code => l_rev_dist_5.assignment_group_code);
2725
2726 l_return := FALSE;
2727 END IF;
2728 CLOSE c_dist_5;
2729 END IF;
2730 CLOSE c_rev_dist_5;
2731
2732 -- check accounting attribute assignments that have derived sources
2733 -- that do not belong to the event class
2734 OPEN c_sources;
2735 LOOP
2736 FETCH c_sources
2737 INTO l_sources;
2738 EXIT WHEN c_sources%notfound;
2739
2740 Xla_amb_setup_err_pkg.stack_error
2741 (p_message_name => 'XLA_AB_EC_ACCT_ATTR_SRCE'
2742 ,p_message_type => 'E'
2743 ,p_message_category => 'ACCOUNTING_SOURCE'
2744 ,p_category_sequence => 5
2745 ,p_application_id => l_application_id
2746 ,p_entity_code => l_entity_code
2747 ,p_event_class_code => l_event_class_code
2748 ,p_accounting_source_code => l_sources.accounting_attribute_code
2749 ,p_source_type_code => l_sources.source_type_code
2750 ,p_source_code => l_sources.source_code);
2751
2752 l_return := FALSE;
2753
2754 END LOOP;
2755 CLOSE c_sources;
2756
2757 -- check accounting attribute assignments that have derived sources
2758 -- that do not belong to the event class
2759 OPEN c_der_sources;
2760 LOOP
2761 FETCH c_der_sources
2762 INTO l_der_sources;
2763 EXIT WHEN c_der_sources%notfound;
2764
2765 IF xla_sources_pkg.derived_source_is_invalid
2766 (p_application_id => l_application_id
2767 ,p_derived_source_code => l_der_sources.source_code
2768 ,p_derived_source_type_code => 'D'
2769 ,p_entity_code => p_entity_code
2770 ,p_event_class_code => p_event_class_code
2771 ,p_level => 'L') = 'TRUE' THEN
2772
2773 Xla_amb_setup_err_pkg.stack_error
2774 (p_message_name => 'XLA_AB_EC_ACCT_ATTR_SRCE'
2775 ,p_message_type => 'E'
2776 ,p_message_category => 'ACCOUNTING_SOURCE'
2777 ,p_category_sequence => 5
2778 ,p_application_id => l_application_id
2779 ,p_entity_code => p_entity_code
2780 ,p_event_class_code => p_event_class_code
2781 ,p_accounting_source_code => l_der_sources.accounting_attribute_code
2782 ,p_source_type_code => l_der_sources.source_type_code
2783 ,p_source_code => l_der_sources.source_code);
2784
2785 l_return := FALSE;
2786 END IF;
2787 END LOOP;
2788 CLOSE c_der_sources;
2789
2790
2791 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
2792 trace
2793 (p_msg => 'End'
2794 ,p_level => C_LEVEL_PROCEDURE);
2795 END IF;
2796
2797 RETURN l_return;
2798
2799 EXCEPTION
2800 WHEN xla_exceptions_pkg.application_exception THEN
2801 RAISE;
2802 WHEN OTHERS THEN
2803 xla_exceptions_pkg.raise_message
2804 (p_location => 'xla_extract_integrity_pkg.validate_accounting_sources');
2805 END Validate_accounting_sources; -- end of function
2806
2807 /*======================================================================+
2808 | |
2809 | Private Function |
2810 | |
2811 | Create_sources |
2812 | |
2813 | This routine creates sources from the extract table definition |
2814 | |
2815 +======================================================================*/
2816 FUNCTION Create_sources
2817 (p_application_id IN NUMBER
2818 ,p_entity_code IN VARCHAR2
2819 ,p_event_class_code IN VARCHAR2)
2820 RETURN BOOLEAN
2821 IS
2822
2823 -- Array Declaration
2824 l_array_source_code t_array_codes;
2825 l_array_datatype_code t_array_type_codes;
2826 l_array_visible_flag t_array_type_codes;
2827 l_array_translated_flag t_array_type_codes;
2828 l_array_tl_source_code t_array_codes;
2829
2830 l_array_ref_source_appl_id t_array_id;
2831 l_array_ref_source_code t_array_codes;
2832 l_array_ref_datatype_code t_array_type_codes;
2833 l_array_ref_visible_flag t_array_type_codes;
2834 l_array_ref_translated_flag t_array_type_codes;
2835 l_array_ref_tl_source_appl_id t_array_id;
2836 l_array_ref_tl_source_code t_array_codes;
2837
2838 -- Variable Declaration
2839
2840 l_application_id NUMBER(15);
2841 l_entity_code VARCHAR2(30);
2842 l_event_class_code VARCHAR2(30);
2843 l_return BOOLEAN := TRUE;
2844 l_language_code VARCHAR2(4);
2845 l_column_name VARCHAR2(30);
2846
2847 dml_errors EXCEPTION;
2848 PRAGMA exception_init(dml_errors, -24381);
2849
2850
2851 CURSOR c_languages
2852 IS
2853 SELECT language_code
2854 FROM fnd_languages
2855 WHERE installed_flag in ('I','B');
2856
2857 CURSOR c_mls
2858 IS
2859 SELECT distinct c.column_name
2860 FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
2861 WHERE c.table_name = e.object_name
2862 AND e.object_name = og.object_name
2863 AND og.owner = c.owner
2864 AND e.application_id = p_application_id
2865 AND e.entity_code = p_entity_code
2866 AND e.event_class_code = p_event_class_code
2867 AND e.object_type_code IN ('HEADER_MLS','LINE_MLS')
2868 AND c.data_type IN ('NUMBER','DATE')
2869 AND c.column_name NOT IN ('EVENT_ID','LINE_NUMBER','LEDGER_ID');
2870
2871
2872 CURSOR c_sources
2873 IS
2874 SELECT distinct(c.column_name) source_code
2875 ,decode(c.data_type,'VARCHAR2','C','CHAR','C','NUMBER','N','DATE','D','C') data_type_code
2876 ,decode(c.column_name,'EVENT_ID','N','LINE_NUMBER','N','LEDGER_ID','N','LANGUAGE','N','Y') visible_flag
2877 ,CASE e.object_type_code
2878 WHEN 'HEADER' THEN 'N'
2879 WHEN 'LINE' THEN 'N'
2880 ELSE decode(c.data_type,'NUMBER','N','DATE','N', decode(c.column_name,'LANGUAGE','N','Y'))
2881 END translated_flag
2882 FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
2883 WHERE c.table_name = e.object_name
2884 AND e.object_name = og.object_name
2885 AND og.owner = c.owner
2886 --
2887 -- Bug 5120836
2888 -- Do not create the LANGUAGE column from non-MLS objects
2889 --
2890 AND DECODE(e.object_type_code
2891 ,'HEADER_MLS'
2892 ,'MLS_COLUMNS'
2893 ,'LINE_MLS'
2894 ,'MLS_COLUMNS'
2895 ,c.column_name) <> 'LANGUAGE'
2896 AND e.application_id = p_application_id
2897 AND e.entity_code = p_entity_code
2898 AND e.event_class_code = p_event_class_code
2899 AND NOT EXISTS (SELECT 'x'
2900 FROM xla_sources_b s
2901 WHERE s.application_id = e.application_id
2902 AND s.source_type_code = 'S'
2903 AND s.source_code = c.column_name);
2904
2905 CURSOR c_ref_sources
2906 IS
2907 SELECT DISTINCT r.reference_object_appl_id
2908 , c.column_name source_code
2909 ,decode(c.data_type,'VARCHAR2','C','CHAR','C','NUMBER','N','DATE','D','C') data_type_code
2910 ,decode(c.column_name,'EVENT_ID','N','LINE_NUMBER','N','LEDGER_ID','N','LANGUAGE','N','Y') visible_flag
2911 ,CASE e.object_type_code
2912 WHEN 'HEADER' THEN 'N'
2913 WHEN 'LINE' THEN 'N'
2914 ELSE decode(c.data_type,'NUMBER','N','DATE','N', decode(c.column_name,'LANGUAGE','N','Y'))
2915 END translated_flag
2916 FROM dba_tab_columns c, xla_reference_objects r,
2917 xla_reference_objects_gt og, xla_extract_objects e
2918 WHERE c.table_name = r.reference_object_name
2919 AND r.reference_object_name = og.reference_object_name
2920 AND og.owner = c.owner
2921 AND r.application_id = p_application_id
2922 AND r.entity_code = p_entity_code
2923 AND r.event_class_code = p_event_class_code
2924 AND e.application_id = p_application_id
2925 AND e.entity_code = p_entity_code
2926 AND e.event_class_code = p_event_class_code
2927 AND e.object_name = r.object_name
2928 --
2929 -- Bug 5120836
2930 -- Do not create the LANGUAGE column from non-MLS objects
2931 --
2932 AND DECODE(e.object_type_code
2933 ,'HEADER_MLS'
2934 ,'MLS_COLUMNS'
2935 ,'LINE_MLS'
2936 ,'MLS_COLUMNS'
2937 ,c.column_name) <> 'LANGUAGE'
2938 AND NOT EXISTS (SELECT 'x'
2939 FROM xla_sources_b s
2940 WHERE s.application_id = r.reference_object_appl_id
2941 AND s.source_type_code = 'S'
2942 AND s.source_code = c.column_name);
2943
2944
2945 CURSOR c_tl_sources
2946 IS
2947 SELECT distinct source_code
2948 FROM xla_sources_b e
2949 WHERE e.application_id = p_application_id
2950 AND NOT EXISTS (SELECT 'x'
2951 FROM xla_sources_vl s
2952 WHERE s.application_id = e.application_id
2953 AND s.source_type_code = e.source_type_code
2954 AND s.source_code = e.source_code);
2955
2956 CURSOR c_ref_tl_sources
2957 IS
2958 SELECT distinct reference_object_appl_id, source_code
2959 FROM xla_sources_b e, xla_reference_objects r
2960 WHERE e.application_id = r.reference_object_appl_id
2961 AND r.application_id = p_application_id
2962 AND NOT EXISTS (SELECT 'x'
2963 FROM xla_sources_vl s
2964 WHERE s.application_id = r.reference_object_appl_id
2965 AND s.source_type_code = e.source_type_code
2966 AND s.source_code = e.source_code);
2967
2968 BEGIN
2969
2970 l_application_id := p_application_id;
2971 l_entity_code := p_entity_code;
2972 l_event_class_code := p_event_class_code;
2973
2974 g_trace_label :='Create_sources';
2975
2976 IF (g_creation_date is NULL) THEN
2977 g_creation_date := sysdate;
2978 END IF;
2979
2980 IF (g_last_update_date is NULL) THEN
2981 g_last_update_date := sysdate;
2982 END IF;
2983
2984 IF (g_created_by is NULL) THEN
2985 g_created_by := xla_environment_pkg.g_usr_id;
2986 END IF;
2987
2988 IF (g_last_update_login is NULL) THEN
2989 g_last_update_login := xla_environment_pkg.g_login_id;
2990 END IF;
2991
2992 IF (g_last_updated_by is NULL) THEN
2993 g_last_updated_by := xla_environment_pkg.g_usr_id;
2994 END IF;
2995
2996 IF (g_log_level is NULL) THEN
2997 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2998 END IF;
2999
3000 IF (g_log_level is NULL) THEN
3001 g_log_enabled := fnd_log.test
3002 (log_level => g_log_level
3003 ,module => C_DEFAULT_MODULE);
3004 END IF;
3005
3006 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
3007 trace
3008 (p_msg => 'Begin'
3009 ,p_level => C_LEVEL_PROCEDURE);
3010 trace
3011 (p_msg => 'p_application_id = ' ||TO_CHAR(p_application_id)
3012 ,p_level => C_LEVEL_PROCEDURE);
3013 trace
3014 (p_msg => 'p_entity_code = '||p_entity_code
3015 ,p_level => C_LEVEL_PROCEDURE);
3016 trace
3017 (p_msg => 'p_event_class_code = ' ||p_event_class_code
3018 ,p_level => C_LEVEL_PROCEDURE);
3019 END IF;
3020
3021 -- Error the columns that are not varchar2 and exist in MLS tables
3022 OPEN c_mls;
3023 LOOP
3024 FETCH c_mls
3025 INTO l_column_name;
3026 EXIT WHEN c_mls%notfound;
3027
3028 Xla_amb_setup_err_pkg.stack_error
3029 (p_message_name => 'XLA_AB_NUMBER_COL_IN_MLS'
3030 ,p_message_type => 'W'
3031 ,p_message_category => 'CREATE_SOURCE'
3032 ,p_category_sequence => 15
3033 ,p_application_id => l_application_id
3034 ,p_entity_code => l_entity_code
3035 ,p_event_class_code => l_event_class_code
3036 ,p_extract_column_name => l_column_name);
3037 l_return := FALSE;
3038
3039 END LOOP;
3040 CLOSE c_mls;
3041
3042 OPEN c_sources;
3043 FETCH c_sources
3044 BULK COLLECT INTO l_array_source_code, l_array_datatype_code, l_array_visible_flag, l_array_translated_flag;
3045
3046 -- Create sources in source_b table for all extract objects
3047 IF l_array_source_code.COUNT > 0 THEN
3048 BEGIN
3049 FORALL i IN l_array_source_code.FIRST..l_array_source_code.LAST SAVE EXCEPTIONS
3050 INSERT INTO xla_sources_b
3051 (source_code
3052 ,application_id
3053 ,source_type_code
3054 ,datatype_code
3055 ,sum_flag
3056 ,visible_flag
3057 ,enabled_flag
3058 ,creation_date
3059 ,created_by
3060 ,last_updated_by
3061 ,last_update_date
3062 ,last_update_login
3063 ,translated_flag
3064 ,key_flexfield_flag)
3065 VALUES
3066 (l_array_source_code(i)
3067 ,p_application_id
3068 ,'S'
3069 ,l_array_datatype_code(i)
3070 ,'N'
3071 ,l_array_visible_flag(i)
3072 ,'Y'
3073 ,g_creation_date
3074 ,g_created_by
3075 ,g_last_updated_by
3076 ,g_last_update_date
3077 ,g_last_update_login
3078 ,l_array_translated_flag(i)
3079 ,'N');
3080
3081 EXCEPTION
3082 WHEN dml_errors THEN
3083
3084 FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
3085 Xla_amb_setup_err_pkg.stack_error
3086 (p_message_name => 'XLA_AB_SAME_COL_DIFF_DATATYPE'
3087 ,p_message_type => 'W'
3088 ,p_message_category => 'CREATE_SOURCE'
3089 ,p_category_sequence => 15
3090 ,p_application_id => l_application_id
3091 ,p_entity_code => l_entity_code
3092 ,p_event_class_code => l_event_class_code
3093 ,p_extract_column_name => l_array_source_code(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX));
3094 END LOOP;
3095
3096 l_return := FALSE;
3097
3098 END;
3099 END IF;
3100 CLOSE c_sources;
3101
3102 OPEN c_ref_sources;
3103 FETCH c_ref_sources
3104 BULK COLLECT INTO l_array_ref_source_appl_id,
3105 l_array_ref_source_code, l_array_ref_datatype_code,
3106 l_array_ref_visible_flag, l_array_ref_translated_flag;
3107
3108 -- Create sources in source_b table for all reference objects
3109 IF l_array_ref_source_code.COUNT > 0 THEN
3110 BEGIN
3111 FORALL i IN l_array_ref_source_code.FIRST..l_array_ref_source_code.LAST SAVE EXCEPTIONS
3112 INSERT INTO xla_sources_b
3113 (source_code
3114 ,application_id
3115 ,source_type_code
3116 ,datatype_code
3117 ,sum_flag
3118 ,visible_flag
3119 ,enabled_flag
3120 ,key_flexfield_flag
3121 ,creation_date
3122 ,created_by
3123 ,last_updated_by
3124 ,last_update_date
3125 ,last_update_login
3126 ,translated_flag)
3127 VALUES
3128 (l_array_ref_source_code(i)
3129 ,l_array_ref_source_appl_id(i)
3130 ,'S'
3131 ,l_array_ref_datatype_code(i)
3132 ,'N'
3133 ,l_array_ref_visible_flag(i)
3134 ,'Y'
3135 ,'N'
3136 ,g_creation_date
3137 ,g_created_by
3138 ,g_last_updated_by
3139 ,g_last_update_date
3140 ,g_last_update_login
3141 ,l_array_ref_translated_flag(i));
3142
3143
3144 EXCEPTION
3145 WHEN dml_errors THEN
3146 FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
3147 Xla_amb_setup_err_pkg.stack_error
3148 (p_message_name => 'XLA_AB_SAME_COL_DIFF_DATATYPE'
3149 ,p_message_type => 'W'
3150 ,p_message_category => 'CREATE_SOURCE'
3151 ,p_category_sequence => 15
3152 ,p_application_id => l_application_id
3153 ,p_entity_code => l_entity_code
3154 ,p_event_class_code => l_event_class_code
3155 ,p_extract_column_name => l_array_ref_source_code(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX));
3156 END LOOP;
3157
3158 l_return := FALSE;
3159
3160 END;
3161 END IF;
3162 CLOSE c_ref_sources;
3163
3164 -- Get all sources that exist in xla_sources_b but not in xla_sources_tl
3165 OPEN c_tl_sources;
3166 FETCH c_tl_sources
3167 BULK COLLECT INTO l_array_tl_source_code;
3168
3169 IF l_array_tl_source_code.COUNT > 0 THEN
3170 -- Insert into sources_tl for all languages installed with same code and name
3171
3172 OPEN c_languages;
3173 LOOP
3174 FETCH c_languages
3175 INTO l_language_code;
3176 EXIT WHEN c_languages%notfound;
3177
3178 BEGIN
3179 FORALL i IN l_array_tl_source_code.FIRST..l_array_tl_source_code.LAST SAVE EXCEPTIONS
3180 INSERT INTO xla_sources_tl
3181 (source_code
3182 ,application_id
3183 ,source_type_code
3184 ,name
3185 ,language
3186 ,source_lang
3187 ,creation_date
3188 ,created_by
3189 ,last_updated_by
3190 ,last_update_date
3191 ,last_update_login)
3192 VALUES
3193 (l_array_tl_source_code(i)
3194 ,p_application_id
3195 ,'S'
3196 ,l_array_tl_source_code(i)
3197 ,l_language_code
3198 ,USERENV('LANG')
3199 ,g_creation_date
3200 ,g_created_by
3201 ,g_last_updated_by
3202 ,g_last_update_date
3203 ,g_last_update_login);
3204
3205 EXCEPTION
3206 WHEN dml_errors THEN
3207
3208 FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
3209 Xla_amb_setup_err_pkg.stack_error
3210 (p_message_name => 'XLA_AB_SAME_NAME_DIFF_CODE'
3211 ,p_message_type => 'E'
3212 ,p_message_category => 'CREATE_SOURCE'
3213 ,p_category_sequence => 15
3214 ,p_application_id => l_application_id
3215 ,p_entity_code => l_entity_code
3216 ,p_event_class_code => l_event_class_code
3217 ,p_extract_column_name => l_array_tl_source_code(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX)
3218 ,p_language => l_language_code);
3219 END LOOP;
3220
3221 l_return := FALSE;
3222 END;
3223 END LOOP;
3224 CLOSE c_languages;
3225 END IF;
3226 CLOSE c_tl_sources;
3227
3228 -- Get all sources that exist in xla_sources_b but not in xla_sources_tl
3229 OPEN c_ref_tl_sources;
3230 FETCH c_ref_tl_sources
3231 BULK COLLECT INTO l_array_ref_tl_source_appl_id, l_array_ref_tl_source_code;
3232
3233 IF l_array_ref_tl_source_code.COUNT > 0 THEN
3234 -- Insert into sources_tl for all languages installed with same code and name
3235
3236 OPEN c_languages;
3237 LOOP
3238 FETCH c_languages
3239 INTO l_language_code;
3240 EXIT WHEN c_languages%notfound;
3241
3242 BEGIN
3243 FORALL i IN l_array_ref_tl_source_code.FIRST..l_array_ref_tl_source_code.LAST SAVE EXCEPTIONS
3244 INSERT INTO xla_sources_tl
3245 (source_code
3246 ,application_id
3247 ,source_type_code
3248 ,name
3249 ,language
3250 ,source_lang
3251 ,creation_date
3252 ,created_by
3253 ,last_updated_by
3254 ,last_update_date
3255 ,last_update_login)
3256 VALUES
3257 (l_array_ref_tl_source_code(i)
3258 ,l_array_ref_tl_source_appl_id(i)
3259 ,'S'
3260 ,l_array_ref_tl_source_code(i)
3261 ,l_language_code
3262 ,USERENV('LANG')
3263 ,g_creation_date
3264 ,g_created_by
3265 ,g_last_updated_by
3266 ,g_last_update_date
3267 ,g_last_update_login);
3268
3269 EXCEPTION
3270 WHEN dml_errors THEN
3271
3272 FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
3273 Xla_amb_setup_err_pkg.stack_error
3274 (p_message_name => 'XLA_AB_SAME_NAME_DIFF_CODE'
3275 ,p_message_type => 'E'
3276 ,p_message_category => 'CREATE_SOURCE'
3277 ,p_category_sequence => 15
3278 ,p_application_id => l_application_id
3279 ,p_entity_code => l_entity_code
3280 ,p_event_class_code => l_event_class_code
3281 ,p_extract_column_name => l_array_ref_tl_source_code(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX)
3282 ,p_language => l_language_code);
3283 END LOOP;
3284
3285 l_return := FALSE;
3286 END;
3287 END LOOP;
3288 CLOSE c_languages;
3289 END IF;
3290 CLOSE c_ref_tl_sources;
3291
3292 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
3293 trace
3294 (p_msg => 'End'
3295 ,p_level => C_LEVEL_PROCEDURE);
3296 END IF;
3297
3298 RETURN l_return;
3299
3300 EXCEPTION
3301 WHEN xla_exceptions_pkg.application_exception THEN
3302 RAISE;
3303 WHEN OTHERS THEN
3304 xla_exceptions_pkg.raise_message
3305 (p_location => 'xla_extract_integrity_pkg.Create_sources');
3306 END Create_sources; -- end of procedure
3307
3308 /*======================================================================+
3309 | |
3310 | Private Procedure |
3311 | |
3312 | Assign_Sources |
3313 | |
3314 | This routine assigns sources from the extract table definition to the |
3315 | event class based on extract object level |
3316 | |
3317 +======================================================================*/
3318 PROCEDURE Assign_sources
3319 (p_application_id IN NUMBER
3320 ,p_entity_code IN VARCHAR2
3321 ,p_event_class_code IN VARCHAR2)
3322 IS
3323
3324 BEGIN
3325 g_trace_label :='Assign_sources';
3326
3327 IF (g_creation_date is NULL) THEN
3328 g_creation_date := sysdate;
3329 END IF;
3330
3331 IF (g_last_update_date is NULL) THEN
3332 g_last_update_date := sysdate;
3333 END IF;
3334
3335 IF (g_created_by is NULL) THEN
3336 g_created_by := xla_environment_pkg.g_usr_id;
3337 END IF;
3338
3339 IF (g_last_update_login is NULL) THEN
3340 g_last_update_login := xla_environment_pkg.g_login_id;
3341 END IF;
3342
3343 IF (g_last_updated_by is NULL) THEN
3344 g_last_updated_by := xla_environment_pkg.g_usr_id;
3345 END IF;
3346
3347 IF (g_log_level is NULL) THEN
3348 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3349 END IF;
3350
3351 IF (g_log_level is NULL) THEN
3352 g_log_enabled := fnd_log.test
3353 (log_level => g_log_level
3354 ,module => C_DEFAULT_MODULE);
3355 END IF;
3356
3357
3358 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
3359 trace
3360 (p_msg => 'Begin'
3361 ,p_level => C_LEVEL_PROCEDURE);
3362 trace
3363 (p_msg => 'p_application_id = ' ||TO_CHAR(p_application_id)
3364 ,p_level => C_LEVEL_PROCEDURE);
3365 trace
3366 (p_msg => 'p_entity_code = '||p_entity_code
3367 ,p_level => C_LEVEL_PROCEDURE);
3368 trace
3369 (p_msg => 'p_event_class_code = ' ||p_event_class_code
3370 ,p_level => C_LEVEL_PROCEDURE);
3371 END IF;
3372 -- Sources are assigned at the highest level they are
3373 -- available in an always populated extract object
3374
3375 -- Assign sources at header level to the event class
3376 -- for header extract objects that are always populated
3377 INSERT INTO xla_event_sources
3378 (source_code
3379 ,application_id
3380 ,entity_code
3381 ,event_class_code
3382 ,source_application_id
3383 ,source_type_code
3384 ,active_flag
3385 ,level_code
3386 ,creation_date
3387 ,created_by
3388 ,last_updated_by
3389 ,last_update_date
3390 ,last_update_login)
3391 (SELECT distinct (c.column_name)
3392 ,p_application_id
3393 ,p_entity_code
3394 ,p_event_class_code
3395 ,p_application_id
3396 ,'S'
3397 ,'Y'
3398 ,'H'
3399 ,g_creation_date
3400 ,g_created_by
3401 ,g_last_updated_by
3402 ,g_last_update_date
3403 ,g_last_update_login
3404 FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
3405 WHERE c.table_name = e.object_name
3406 AND og.object_name = e.object_name
3407 AND og.owner = c.owner
3408 AND e.object_type_code IN ('HEADER','HEADER_MLS')
3409 AND e.application_id = p_application_id
3410 AND e.entity_code = p_entity_code
3411 AND e.event_class_code = p_event_class_code
3412 AND e.always_populated_flag = 'Y'
3413 AND NOT EXISTS (SELECT 'x'
3414 FROM xla_event_sources s
3415 WHERE s.application_id = p_application_id
3416 AND s.entity_code = p_entity_code
3417 AND s.event_class_code = p_event_class_code
3418 AND s.source_application_id = p_application_id
3419 AND s.source_code = c.column_name));
3420
3421 -- Assign sources at header level to the event class
3422 -- for header reference objects that are always populated
3423 INSERT INTO xla_event_sources
3424 (source_code
3425 ,application_id
3426 ,entity_code
3427 ,event_class_code
3428 ,source_application_id
3429 ,source_type_code
3430 ,active_flag
3431 ,level_code
3432 ,creation_date
3433 ,created_by
3434 ,last_updated_by
3435 ,last_update_date
3436 ,last_update_login)
3437 (SELECT distinct (c.column_name)
3438 ,p_application_id
3439 ,p_entity_code
3440 ,p_event_class_code
3441 ,r.reference_object_appl_id
3442 ,'S'
3443 ,'Y'
3444 ,'H'
3445 ,g_creation_date
3446 ,g_created_by
3447 ,g_last_updated_by
3448 ,g_last_update_date
3449 ,g_last_update_login
3450 FROM dba_tab_columns c, xla_reference_objects r,
3451 xla_reference_objects_gt og, xla_extract_objects e
3452 WHERE c.table_name = r.reference_object_name
3453 AND og.reference_object_name = r.reference_object_name
3454 AND og.owner = c.owner
3455 AND e.application_id = p_application_id
3456 AND e.entity_code = p_entity_code
3457 AND e.event_class_code = p_event_class_code
3458 AND e.object_name = r.object_name
3459 AND e.object_type_code IN ('HEADER','HEADER_MLS')
3460 AND r.application_id = p_application_id
3461 AND r.entity_code = p_entity_code
3462 AND r.event_class_code = p_event_class_code
3463 AND r.always_populated_flag = 'Y'
3464 AND NOT EXISTS (SELECT 'x'
3465 FROM xla_event_sources s
3466 WHERE s.application_id = p_application_id
3467 AND s.entity_code = p_entity_code
3468 AND s.event_class_code = p_event_class_code
3469 AND s.source_application_id = r.reference_object_appl_id
3470 AND s.source_code = c.column_name));
3471
3472 -- Assign sources at line level to the event class
3473 -- for line extract objects that are always populated
3474 INSERT INTO xla_event_sources
3475 (source_code
3476 ,application_id
3477 ,entity_code
3478 ,event_class_code
3479 ,source_application_id
3480 ,source_type_code
3481 ,active_flag
3482 ,level_code
3483 ,creation_date
3484 ,created_by
3485 ,last_updated_by
3486 ,last_update_date
3487 ,last_update_login)
3488 (SELECT distinct (c.column_name)
3489 ,p_application_id
3490 ,p_entity_code
3491 ,p_event_class_code
3492 ,p_application_id
3493 ,'S'
3494 ,'Y'
3495 ,'L'
3496 ,g_creation_date
3497 ,g_created_by
3498 ,g_last_updated_by
3499 ,g_last_update_date
3500 ,g_last_update_login
3501 FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
3502 WHERE c.table_name = e.object_name
3503 AND og.object_name = e.object_name
3504 AND og.owner = c.owner
3505 AND e.object_type_code IN ('LINE','LINE_MLS')
3506 AND e.application_id = p_application_id
3507 AND e.entity_code = p_entity_code
3508 AND e.event_class_code = p_event_class_code
3509 AND e.always_populated_flag = 'Y'
3510 AND NOT EXISTS (SELECT 'x'
3511 FROM xla_event_sources s
3512 WHERE s.application_id = p_application_id
3513 AND s.entity_code = p_entity_code
3514 AND s.event_class_code = p_event_class_code
3515 AND s.source_application_id = p_application_id
3516 AND s.source_code = c.column_name));
3517
3518 -- Assign sources at line level to the event class
3519 -- for line reference objects that are always populated
3520 INSERT INTO xla_event_sources
3521 (source_code
3522 ,application_id
3523 ,entity_code
3524 ,event_class_code
3525 ,source_application_id
3526 ,source_type_code
3527 ,active_flag
3528 ,level_code
3529 ,creation_date
3530 ,created_by
3531 ,last_updated_by
3532 ,last_update_date
3533 ,last_update_login)
3534 (SELECT distinct (c.column_name)
3535 ,p_application_id
3536 ,p_entity_code
3537 ,p_event_class_code
3538 ,r.reference_object_appl_id
3539 ,'S'
3540 ,'Y'
3541 ,'L'
3542 ,g_creation_date
3543 ,g_created_by
3544 ,g_last_updated_by
3545 ,g_last_update_date
3546 ,g_last_update_login
3547 FROM dba_tab_columns c, xla_reference_objects r,
3548 xla_reference_objects_gt og, xla_extract_objects e
3549 WHERE c.table_name = r.reference_object_name
3550 AND og.reference_object_name = r.reference_object_name
3551 AND og.owner = c.owner
3552 AND e.application_id = p_application_id
3553 AND e.entity_code = p_entity_code
3554 AND e.event_class_code = p_event_class_code
3555 AND e.object_name = r.object_name
3556 AND e.object_type_code IN ('LINE','LINE_MLS')
3557 AND r.application_id = p_application_id
3558 AND r.entity_code = p_entity_code
3559 AND r.event_class_code = p_event_class_code
3560 AND r.always_populated_flag = 'Y'
3561 AND NOT EXISTS (SELECT 'x'
3562 FROM xla_event_sources s
3563 WHERE s.application_id = p_application_id
3564 AND s.entity_code = p_entity_code
3565 AND s.event_class_code = p_event_class_code
3566 AND s.source_application_id = r.reference_object_appl_id
3567 AND s.source_code = c.column_name));
3568
3569 -- Assign sources at header level to the event class
3570 -- for header extract objects that are not always populated
3571 INSERT INTO xla_event_sources
3572 (source_code
3573 ,application_id
3574 ,entity_code
3575 ,event_class_code
3576 ,source_application_id
3577 ,source_type_code
3578 ,active_flag
3579 ,level_code
3580 ,creation_date
3581 ,created_by
3582 ,last_updated_by
3583 ,last_update_date
3584 ,last_update_login)
3585 (SELECT distinct (c.column_name)
3586 ,p_application_id
3587 ,p_entity_code
3588 ,p_event_class_code
3589 ,p_application_id
3590 ,'S'
3591 ,'Y'
3592 ,'H'
3593 ,g_creation_date
3594 ,g_created_by
3595 ,g_last_updated_by
3596 ,g_last_update_date
3597 ,g_last_update_login
3598 FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
3599 WHERE c.table_name = e.object_name
3600 AND og.object_name = e.object_name
3601 AND og.owner = c.owner
3602 AND e.object_type_code IN ('HEADER','HEADER_MLS')
3603 AND e.application_id = p_application_id
3604 AND e.entity_code = p_entity_code
3605 AND e.event_class_code = p_event_class_code
3606 AND e.always_populated_flag = 'N'
3607 AND NOT EXISTS (SELECT 'x'
3608 FROM xla_event_sources s
3609 WHERE s.application_id = p_application_id
3610 AND s.entity_code = p_entity_code
3611 AND s.event_class_code = p_event_class_code
3612 AND s.source_application_id = p_application_id
3613 AND s.source_code = c.column_name));
3614
3615 -- Assign sources at header level to the event class
3616 -- for header reference objects that are not always populated
3617 INSERT INTO xla_event_sources
3618 (source_code
3619 ,application_id
3620 ,entity_code
3621 ,event_class_code
3622 ,source_application_id
3623 ,source_type_code
3624 ,active_flag
3625 ,level_code
3626 ,creation_date
3627 ,created_by
3628 ,last_updated_by
3629 ,last_update_date
3630 ,last_update_login)
3631 (SELECT distinct (c.column_name)
3632 ,p_application_id
3633 ,p_entity_code
3634 ,p_event_class_code
3635 ,r.reference_object_appl_id
3636 ,'S'
3637 ,'Y'
3638 ,'H'
3639 ,g_creation_date
3640 ,g_created_by
3641 ,g_last_updated_by
3642 ,g_last_update_date
3643 ,g_last_update_login
3644 FROM dba_tab_columns c, xla_reference_objects r,
3645 xla_reference_objects_gt og, xla_extract_objects e
3646 WHERE c.table_name = r.reference_object_name
3647 AND og.reference_object_name = r.reference_object_name
3648 AND og.owner = c.owner
3649 AND e.application_id = p_application_id
3650 AND e.entity_code = p_entity_code
3651 AND e.event_class_code = p_event_class_code
3652 AND e.object_name = r.object_name
3653 AND e.object_type_code IN ('HEADER','HEADER_MLS')
3654 AND r.application_id = p_application_id
3655 AND r.entity_code = p_entity_code
3656 AND r.event_class_code = p_event_class_code
3657 AND r.always_populated_flag = 'N'
3658 AND NOT EXISTS (SELECT 'x'
3659 FROM xla_event_sources s
3660 WHERE s.application_id = p_application_id
3661 AND s.entity_code = p_entity_code
3662 AND s.event_class_code = p_event_class_code
3663 AND s.source_application_id = r.reference_object_appl_id
3664 AND s.source_code = c.column_name));
3665
3666 -- Assign sources at line level to the event class
3667 -- for line extract objects that are not always populated
3668 INSERT INTO xla_event_sources
3669 (source_code
3670 ,application_id
3671 ,entity_code
3672 ,event_class_code
3673 ,source_application_id
3674 ,source_type_code
3675 ,active_flag
3676 ,level_code
3677 ,creation_date
3678 ,created_by
3679 ,last_updated_by
3680 ,last_update_date
3681 ,last_update_login)
3682 (SELECT distinct (c.column_name)
3683 ,p_application_id
3684 ,p_entity_code
3685 ,p_event_class_code
3686 ,p_application_id
3687 ,'S'
3688 ,'Y'
3689 ,'L'
3690 ,g_creation_date
3691 ,g_created_by
3692 ,g_last_updated_by
3693 ,g_last_update_date
3694 ,g_last_update_login
3695 FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
3696 WHERE c.table_name = e.object_name
3697 AND og.object_name = e.object_name
3698 AND og.owner = c.owner
3699 AND e.object_type_code IN ('LINE','LINE_MLS')
3700 AND e.application_id = p_application_id
3701 AND e.entity_code = p_entity_code
3702 AND e.event_class_code = p_event_class_code
3703 AND e.always_populated_flag = 'N'
3704 AND NOT EXISTS (SELECT 'x'
3705 FROM xla_event_sources s
3706 WHERE s.application_id = p_application_id
3707 AND s.entity_code = p_entity_code
3708 AND s.event_class_code = p_event_class_code
3709 AND s.source_application_id = p_application_id
3710 AND s.source_code = c.column_name));
3711
3712 -- Assign sources at line level to the event class
3713 -- for line extract objects that are not always populated
3714 INSERT INTO xla_event_sources
3715 (source_code
3716 ,application_id
3717 ,entity_code
3718 ,event_class_code
3719 ,source_application_id
3720 ,source_type_code
3721 ,active_flag
3722 ,level_code
3723 ,creation_date
3724 ,created_by
3725 ,last_updated_by
3726 ,last_update_date
3727 ,last_update_login)
3728 (SELECT distinct (c.column_name)
3729 ,p_application_id
3730 ,p_entity_code
3731 ,p_event_class_code
3732 ,r.reference_object_appl_id
3733 ,'S'
3734 ,'Y'
3735 ,'L'
3736 ,g_creation_date
3737 ,g_created_by
3738 ,g_last_updated_by
3739 ,g_last_update_date
3740 ,g_last_update_login
3741 FROM dba_tab_columns c, xla_reference_objects r,
3742 xla_reference_objects_gt og, xla_extract_objects e
3743 WHERE c.table_name = r.reference_object_name
3744 AND og.reference_object_name = r.reference_object_name
3745 AND og.owner = c.owner
3746 AND e.application_id = p_application_id
3747 AND e.entity_code = p_entity_code
3748 AND e.event_class_code = p_event_class_code
3749 AND e.object_name = r.object_name
3750 AND e.object_type_code IN ('LINE','LINE_MLS')
3751 AND r.application_id = p_application_id
3752 AND r.entity_code = p_entity_code
3753 AND r.event_class_code = p_event_class_code
3754 AND r.always_populated_flag = 'N'
3755 AND NOT EXISTS (SELECT 'x'
3756 FROM xla_event_sources s
3757 WHERE s.application_id = p_application_id
3758 AND s.entity_code = p_entity_code
3759 AND s.event_class_code = p_event_class_code
3760 AND s.source_application_id = r.reference_object_appl_id
3761 AND s.source_code = c.column_name));
3762
3763
3764 IF ((g_log_enabled = TRUE) AND (C_LEVEL_PROCEDURE >= g_log_level)) THEN
3765 trace
3766 (p_msg => 'End'
3767 ,p_level => C_LEVEL_PROCEDURE);
3768 END IF;
3769
3770 EXCEPTION
3771 WHEN xla_exceptions_pkg.application_exception THEN
3772 RAISE;
3773 WHEN OTHERS THEN
3774 xla_exceptions_pkg.raise_message
3775 (p_location => 'xla_extract_integrity_pkg.Assign_sources');
3776 END Assign_sources; -- end of procedure
3777
3778 END xla_extract_integrity_pkg;