[Home] [Help]
PACKAGE BODY: APPS.XLA_AAD_MERGE_PVT
Source
1 PACKAGE BODY xla_aad_merge_pvt AS
2 /* $Header: xlaalmer.pkb 120.20.12010000.3 2009/02/11 11:31:22 ssawhney ship $ */
3
4 --=============================================================================
5 -- **************** declaraions ********************
6 --=============================================================================
7 -------------------------------------------------------------------------------
8 -- declaring global types
9 -------------------------------------------------------------------------------
10 TYPE t_array_varchar30 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
11 TYPE t_array_varchar80 IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
12 TYPE t_array_int IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
13
14 -------------------------------------------------------------------------------
15 -- declaring global constants
16 -------------------------------------------------------------------------------
17 ------------------------------------------------------------------------------
18 -- declaring global variables
19 ------------------------------------------------------------------------------
20 g_amb_context_code VARCHAR2(30);
21 g_staging_context_code VARCHAR2(30);
22 g_application_id INTEGER;
23 g_user_type_code VARCHAR2(30);
24 g_analyzed_flag VARCHAR2(1);
25 g_compile_flag VARCHAR2(1);
26 g_usr_id INTEGER;
27 g_login_id INTEGER;
28
29 C_OWNER_SYSTEM CONSTANT VARCHAR2(1) := 'S';
30 C_DATE CONSTANT DATE := TO_DATE('1','j');
31 C_NUM CONSTANT NUMBER := 9.99E125;
32 C_CHAR CONSTANT VARCHAR2(1) := '
33 ';
34
35 G_EXC_WARNING EXCEPTION;
36
37 --=============================================================================
38 -- *********** Local Trace Routine **********
39 --=============================================================================
40 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
41 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
42 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
43 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
44 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
45 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
46
47 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
48 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_aad_merge_pvt';
49
50 g_log_level NUMBER;
51 g_log_enabled BOOLEAN;
52
53 PROCEDURE trace
54 (p_msg IN VARCHAR2
55 ,p_module IN VARCHAR2
56 ,p_level IN NUMBER) IS
57 l_time varchar2(300);
58 BEGIN
59 ----------------------------------------------------------------------------
60 -- Following is for FND log.
61 ----------------------------------------------------------------------------
62 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
63 fnd_log.message(p_level, p_module);
64 ELSIF p_level >= g_log_level THEN
65 fnd_log.string(p_level, p_module, p_msg);
66 END IF;
67 EXCEPTION
68 WHEN OTHERS THEN
69 xla_exceptions_pkg.raise_message
70 (p_location => 'xla_aad_merge_pvt.trace');
71
72 END trace;
73
74
75 --=============================================================================
76 -- *********** private procedures and functions **********
77 --=============================================================================
78
79 --=============================================================================
80 --
81 -- Name: pre_merge
82 -- Description: This API prepares the environment for merge
83 --
84 --=============================================================================
85 FUNCTION pre_merge
86 RETURN VARCHAR2
87 IS
88 CURSOR c IS
89 SELECT *
90 FROM xla_appli_amb_contexts
91 WHERE application_id = g_application_id
92 AND amb_context_code = g_amb_context_code
93 FOR UPDATE OF application_id NOWAIT;
94
95 l_lock_error BOOLEAN;
96 l_recinfo xla_appli_amb_contexts%ROWTYPE;
97 l_retcode VARCHAR2(30);
98 l_log_module VARCHAR2(240);
99 BEGIN
100 IF g_log_enabled THEN
101 l_log_module := C_DEFAULT_MODULE||'.pre_merge';
102 END IF;
103
104 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
105 trace(p_msg => 'BEGIN of function pre_merge',
106 p_module => l_log_module,
107 p_level => C_LEVEL_PROCEDURE);
108 END IF;
109
110 l_retcode := 'SUCCESS';
111
112 -- Begin API Logic
113
114 -- Lock the staging area of the AMB context
115 l_lock_error := TRUE;
116 OPEN c;
117 CLOSE c;
118 l_lock_error := FALSE;
119
120 IF (l_retcode = 'SUCCESS') THEN
121 l_retcode := xla_aad_loader_util_pvt.lock_area
122 (p_application_id => g_application_id
123 ,p_amb_context_code => g_amb_context_code);
124
125 IF (l_retcode <> 'SUCCESS') THEN
126 xla_aad_loader_util_pvt.stack_error
127 (p_appli_s_name => 'XLA'
128 ,p_msg_name => 'XLA_AAD_MGR_LOCK_FAILED');
129 l_retcode := 'WARNING';
130 END IF;
131 END IF;
132
133 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
134 trace(p_msg => 'END of function pre_merge - Return value = '||l_retcode,
135 p_module => l_log_module,
136 p_level => C_LEVEL_PROCEDURE);
137 END IF;
138
139 RETURN l_retcode;
140 EXCEPTION
141 WHEN OTHERS THEN
142 IF (c%ISOPEN) THEN
143 CLOSE c;
144 END IF;
145
146 IF (l_lock_error) THEN
147 l_retcode := 'WARNING';
148
149 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
150 trace(p_msg => 'END of function pre_merge - Return value = '||l_retcode,
151 p_module => l_log_module,
152 p_level => C_LEVEL_PROCEDURE);
153 END IF;
154
155 xla_aad_loader_util_pvt.stack_error
156 (p_appli_s_name => 'XLA'
157 ,p_msg_name => 'XLA_AAD_MGR_LOCK_FAILED');
158
159 RETURN l_retcode;
160 ELSE
161 xla_aad_loader_util_pvt.stack_error
162 (p_appli_s_name => 'XLA'
163 ,p_msg_name => 'XLA_COMMON_ERROR'
164 ,p_token_1 => 'LOCATION'
165 ,p_value_1 => 'xla_aad_merge_pvt.pre_merge'
166 ,p_token_2 => 'ERROR'
167 ,p_value_2 => 'unhandled exception');
168 RAISE;
169 END IF;
170
171 END pre_merge;
172
173
174 --=============================================================================
175 --
176 -- Name: validation
177 -- Description: This API validate the AADs and components
178 -- Return codes:
179 -- SUCCESS - completed sucessfully
180 -- ERROR - completed with error
181 --
182 --=============================================================================
183 FUNCTION validation
184 RETURN VARCHAR2
185 IS
186
187 CURSOR c_updated IS
188 SELECT 1
189 FROM xla_appli_amb_contexts
190 WHERE amb_context_code = g_amb_context_code
191 AND application_id = g_application_id
192 AND updated_flag = 'N';
193
194 -- Return if any AAD has a higher version in the working area then the
195 -- original version of the one in the staging area
196 CURSOR c_invalid_versions IS
197 SELECT distinct t.name
198 FROM xla_product_rules_b w
199 , xla_product_rules_b s
200 , xla_staging_components_h h
201 , xla_product_rules_tl t
202 WHERE w.version_num > h.version_num
203 AND w.amb_context_code = g_amb_context_code
204 AND w.application_id = g_application_id
205 AND w.product_rule_type_code = s.product_rule_type_code
206 AND w.product_rule_code = s.product_rule_code
207 --
208 AND t.application_id = w.application_id
209 AND t.amb_context_code = w.amb_context_code
210 AND t.product_rule_type_code = w.product_rule_type_code
211 AND t.product_rule_code = w.product_rule_code
212 AND t.language = USERENV('LANG')
213 --
214 AND h.staging_amb_context_code = g_staging_context_code
215 AND h.application_id = g_application_id
216 AND h.component_owner_code = s.product_rule_type_code
217 AND h.component_code = s.product_rule_code
218 AND h.component_type_code = 'AAD'
219 AND h.version_num = s.version_num
220 --
221 AND s.amb_context_code = g_staging_context_code
222 AND s.application_id = g_application_id;
223
224 l_exists INTEGER;
225 l_retcode VARCHAR2(30);
226 l_log_module VARCHAR2(240);
227
228 BEGIN
229 IF g_log_enabled THEN
230 l_log_module := C_DEFAULT_MODULE||'.validation';
231 END IF;
232
233 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
234 trace(p_msg => 'BEGIN of function validation',
235 p_module => l_log_module,
236 p_level => C_LEVEL_PROCEDURE);
237 END IF;
238
239 l_retcode := 'SUCCESS';
240
241 l_retcode := xla_aad_loader_util_pvt.validate_adr_compatibility
242 (p_application_id => g_application_id
243 ,p_amb_context_code => g_amb_context_code
244 ,p_staging_context_code => g_staging_context_code);
245
246 IF (g_analyzed_flag = 'Y') THEN
247
248 -- If merge analysis was run and the AAD/setups are modified since merge
249 -- analysis, return FALSE
250 OPEN c_updated;
251 FETCH c_updated INTO l_exists;
252 IF (c_updated%NOTFOUND) THEN
253 l_retcode := 'WARNING';
254 xla_aad_loader_util_pvt.stack_error
255 (p_appli_s_name => 'XLA'
256 ,p_msg_name => 'XLA_AAD_MER_AMB_UPDATED');
257 END IF;
258 CLOSE c_updated;
259 ELSE
260
261 -- If merge analysis is not run, make sure no AAD has a higher version in
262 -- working area than the original version of the one in the staging area
263 FOR l_err in c_invalid_versions LOOP
264 l_retcode := 'WARNING';
265 xla_aad_loader_util_pvt.stack_error
266 (p_appli_s_name => 'XLA'
267 ,p_msg_name => 'XLA_AAD_MER_INVALID_AAD_VERS'
268 ,p_token_1 => 'PROD_RULE_NAME'
269 ,p_value_1 => l_err.name);
270 END LOOP;
271 END IF;
272
273 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
274 trace(p_msg => 'END of function validation - Return value = '||l_retcode,
275 p_module => l_log_module,
276 p_level => C_LEVEL_PROCEDURE);
277 END IF;
278
279 return l_retcode;
280 EXCEPTION
281 WHEN OTHERS THEN
282 xla_aad_loader_util_pvt.stack_error
283 (p_appli_s_name => 'XLA'
284 ,p_msg_name => 'XLA_COMMON_ERROR'
285 ,p_token_1 => 'LOCATION'
286 ,p_value_1 => 'xla_aad_merge_pvt.validation'
287 ,p_token_2 => 'ERROR'
288 ,p_value_2 => 'Unhandled exception');
289 RAISE;
290
291 END validation;
292
293 --=============================================================================
294 --
295 -- Name: clean_oracle_aads
296 -- Description:
297 --
298 --=============================================================================
299 PROCEDURE clean_oracle_aads
300 IS
301 CURSOR c_all_comps IS
302 SELECT w.product_rule_code, w.version_num
303 FROM xla_product_rules_b w
304 WHERE w.application_id = g_application_id
305 AND w.amb_context_code = g_amb_context_code
306 AND w.product_rule_type_code = C_OWNER_SYSTEM
307 AND NOT EXISTS ( SELECT 1
308 FROM xla_product_rules_b s
309 WHERE s.application_id = g_application_id
310 AND s.amb_context_code = g_staging_context_code
311 AND s.product_rule_type_code = C_OWNER_SYSTEM
312 AND s.product_rule_code = w.product_rule_code);
313
314 l_codes t_array_varchar30;
315 l_version_nums t_array_int;
316
317 l_log_module VARCHAR2(240);
318 BEGIN
319 IF g_log_enabled THEN
320 l_log_module := C_DEFAULT_MODULE||'.clean_oracle_aads';
321 END IF;
322
323 IF (g_analyzed_flag = 'Y') THEN
324 null;
325 ELSE
326 OPEN c_all_comps;
327 FETCH c_all_comps BULK COLLECT INTO l_codes, l_version_nums;
328 CLOSE c_all_comps;
329
330 END IF;
331
332 -- Insert log
333 FORALL i IN 1..l_codes.COUNT
334 INSERT INTO xla_aad_loader_logs
335 (aad_loader_log_id
336 ,amb_context_code
337 ,application_id
338 ,request_code
339 ,log_type_code
340 ,aad_application_id
341 ,product_rule_code
342 ,product_rule_type_code
343 ,version_to
344 ,object_version_number
345 ,creation_date
346 ,created_by
347 ,last_update_date
348 ,last_updated_by
349 ,last_update_login
350 ,program_update_date
351 ,program_application_id
352 ,program_id
353 ,request_id)
354 VALUES
355 (xla_aad_loader_logs_s.nextval
356 ,g_amb_context_code
357 ,g_application_id
358 ,'IMPORT'
359 ,'DELETED_AAD'
360 ,g_application_id
361 ,l_codes(i)
362 ,C_OWNER_SYSTEM
363 ,l_version_nums(i)
364 ,1
365 ,sysdate
366 ,xla_environment_pkg.g_usr_id
367 ,sysdate
368 ,xla_environment_pkg.g_usr_id
369 ,xla_environment_pkg.g_login_id
370 ,sysdate
371 ,xla_environment_pkg.g_prog_appl_id
372 ,xla_environment_pkg.g_prog_id
373 ,xla_environment_pkg.g_req_Id);
374
375 -- Delete JLD aasignment that is no longer assigned to the header
376 DELETE FROM xla_aad_line_defn_assgns w
377 WHERE application_id = g_application_id
378 AND amb_context_code = g_amb_context_code
379 AND product_rule_type_code = C_OWNER_SYSTEM
380 AND NOT EXISTS
381 (SELECT 1
382 FROM xla_aad_line_defn_assgns s
383 WHERE s.application_id = g_application_id
384 AND s.amb_context_code = g_staging_context_code
385 AND s.product_rule_type_code = C_OWNER_SYSTEM
386 AND s.product_rule_code = w.product_rule_code
387 AND s.event_class_code = w.event_class_code
388 AND s.event_type_code = w.event_type_code
389 AND s.line_definition_owner_code = w.line_definition_owner_code
390 AND s.line_definition_code = w.line_definition_code);
391
392 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
393 trace(p_msg => '# xla_aad_line_defn_assgns deleted = '||SQL%ROWCOUNT,
394 p_module => l_log_module,
395 p_level => C_LEVEL_STATEMENT);
396 END IF;
397
398 -- Delete AC assignment that is no longer assigned to the header
399 DELETE FROM xla_aad_header_ac_assgns w
400 WHERE application_id = g_application_id
401 AND amb_context_code = g_amb_context_code
402 AND product_rule_type_code = C_OWNER_SYSTEM
403 AND NOT EXISTS
407 AND s.amb_context_code = g_staging_context_code
404 (SELECT 1
405 FROM xla_aad_header_ac_assgns s
406 WHERE s.application_id = g_application_id
408 AND s.product_rule_type_code = C_OWNER_SYSTEM
409 AND s.product_rule_code = w.product_rule_code
410 AND s.event_class_code = w.event_class_code
411 AND s.event_type_code = w.event_type_code
412 AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
413 AND s.analytical_criterion_code = w.analytical_criterion_code);
414
415 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
416 trace(p_msg => '# xla_aad_header_ac_assgns deleted = '||SQL%ROWCOUNT,
417 p_module => l_log_module,
418 p_level => C_LEVEL_STATEMENT);
419 END IF;
420
421 DELETE FROM xla_aad_hdr_acct_attrs w
422 WHERE application_id = g_application_id
423 AND amb_context_code = g_amb_context_code
424 AND product_rule_type_code = C_OWNER_SYSTEM
425 AND NOT EXISTS
426 (SELECT 1
427 FROM xla_aad_hdr_acct_attrs s
428 WHERE s.application_id = g_application_id
429 AND s.amb_context_code = g_staging_context_code
430 AND s.product_rule_type_code = C_OWNER_SYSTEM
431 AND s.product_rule_code = w.product_rule_code
432 AND s.event_class_code = w.event_class_code
433 AND s.event_type_code = w.event_type_code
434 AND s.accounting_attribute_code = w.accounting_attribute_code);
435
436 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
437 trace(p_msg => '# xla_aad_hdr_acct_attrs deleted = '||SQL%ROWCOUNT,
438 p_module => l_log_module,
439 p_level => C_LEVEL_STATEMENT);
440 END IF;
441
442 DELETE FROM xla_prod_acct_headers w
443 WHERE application_id = g_application_id
444 AND amb_context_code = g_amb_context_code
445 AND product_rule_type_code = C_OWNER_SYSTEM
446 AND NOT EXISTS
447 (SELECT 1
448 FROM xla_prod_acct_headers s
449 WHERE s.application_id = g_application_id
450 AND s.amb_context_code = g_staging_context_code
451 AND s.product_rule_type_code = C_OWNER_SYSTEM
452 AND s.product_rule_code = w.product_rule_code
453 AND s.event_class_code = w.event_class_code
454 AND s.event_type_code = w.event_type_code);
455
456 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
457 trace(p_msg => '# xla_prod_acct_headers deleted = '||SQL%ROWCOUNT,
458 p_module => l_log_module,
459 p_level => C_LEVEL_STATEMENT);
460 END IF;
461
462 -- Delete AAD that is not in the staging area
463 FORALL i IN 1..l_codes.COUNT
464 DELETE FROM xla_product_rules_tl w
465 WHERE application_id = g_application_id
466 AND amb_context_code = g_amb_context_code
467 AND product_rule_type_code = C_OWNER_SYSTEM
468 AND product_rule_code = l_codes(i);
469
470 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
471 trace(p_msg => '# xla_product_rules_tl deleted = '||SQL%ROWCOUNT,
472 p_module => l_log_module,
473 p_level => C_LEVEL_STATEMENT);
474 END IF;
475
476 FORALL i IN 1..l_codes.COUNT
477 DELETE FROM xla_product_rules_b w
478 WHERE application_id = g_application_id
479 AND amb_context_code = g_amb_context_code
480 AND product_rule_type_code = C_OWNER_SYSTEM
481 AND product_rule_code = l_codes(i);
482
483 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
484 trace(p_msg => '# xla_product_rules_b deleted = '||SQL%ROWCOUNT,
485 p_module => l_log_module,
486 p_level => C_LEVEL_STATEMENT);
487 END IF;
488
489 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
490 trace(p_msg => 'BEGIN of procedure clean_oracle_aads',
491 p_module => l_log_module,
492 p_level => C_LEVEL_PROCEDURE);
493 END IF;
494
495 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
496 trace(p_msg => 'END of procedure clean_oracle_aads',
497 p_module => l_log_module,
498 p_level => C_LEVEL_PROCEDURE);
499 END IF;
500
501 EXCEPTION
502 WHEN OTHERS THEN
503 xla_aad_loader_util_pvt.stack_error
504 (p_appli_s_name => 'XLA'
505 ,p_msg_name => 'XLA_COMMON_ERROR'
506 ,p_token_1 => 'LOCATION'
507 ,p_value_1 => 'xla_aad_merge_pvt.clean_oracle_aads'
508 ,p_token_2 => 'ERROR'
509 ,p_value_2 => 'unhandled exception');
510 RAISE;
511
512 END clean_oracle_aads;
513
514
515 --=============================================================================
516 --
517 -- Name: clean_oracle_jlds
518 -- Description:
519 --
520 --=============================================================================
521 PROCEDURE clean_oracle_jlds
522 IS
523 CURSOR c_all_comps IS
524 SELECT w.event_class_code
528 WHERE w.application_id = g_application_id
525 , w.event_type_code
526 , w.line_definition_code
527 FROM xla_line_definitions_b w
529 AND w.amb_context_code = g_amb_context_code
530 AND w.line_definition_owner_code = C_OWNER_SYSTEM
531 AND NOT EXISTS ( SELECT 1
532 FROM xla_line_definitions_b s
533 WHERE s.application_id = g_application_id
534 AND s.amb_context_code = g_staging_context_code
535 AND s.event_class_code = w.event_class_code
536 AND s.event_type_code = w.event_type_code
537 AND s.line_definition_owner_code = C_OWNER_SYSTEM
538 AND s.line_definition_code = w.line_definition_code);
539
540 l_event_class_codes t_array_varchar30;
541 l_event_type_codes t_array_varchar30;
542 l_codes t_array_varchar30;
543
544 l_log_module VARCHAR2(240);
545 BEGIN
546 IF g_log_enabled THEN
547 l_log_module := C_DEFAULT_MODULE||'.clean_oracle_jlds';
548 END IF;
549
550 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
551 trace(p_msg => 'BEGIN of procedure clean_oracle_jlds',
552 p_module => l_log_module,
553 p_level => C_LEVEL_PROCEDURE);
554 END IF;
555
556 IF (g_analyzed_flag = 'Y') THEN
557 null;
558 ELSE
559 OPEN c_all_comps;
560 FETCH c_all_comps BULK COLLECT INTO l_event_class_codes
561 , l_event_type_codes
562 , l_codes;
563 CLOSE c_all_comps;
564
565 END IF;
566
567 -- Delete JLD assignment for those JLD no longer exist
568 FORALL i IN 1..l_codes.COUNT
569 DELETE FROM xla_aad_line_defn_assgns w
570 WHERE application_id = g_application_id
571 AND amb_context_code = g_amb_context_code
572 AND event_class_code = l_event_class_codes(i)
573 AND event_type_code = l_event_type_codes(i)
574 AND line_definition_owner_code = C_OWNER_SYSTEM
575 AND line_definition_code = l_codes(i);
576
577 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
578 trace(p_msg => '# xla_aad_line_defn_assgns deleted = '||SQL%ROWCOUNT,
579 p_module => l_log_module,
580 p_level => C_LEVEL_STATEMENT);
581 END IF;
582
583 DELETE FROM xla_line_defn_ac_assgns w
584 WHERE application_id = g_application_id
585 AND amb_context_code = g_amb_context_code
586 AND line_definition_owner_code = C_OWNER_SYSTEM
587 AND NOT EXISTS
588 (SELECT 1
589 FROM xla_line_defn_ac_assgns s
590 WHERE s.application_id = g_application_id
591 AND s.amb_context_code = g_staging_context_code
592 AND s.event_class_code = w.event_class_code
593 AND s.event_type_code = w.event_type_code
594 AND s.line_definition_owner_code = C_OWNER_SYSTEM
595 AND s.line_definition_code = w.line_definition_code
596 AND s.accounting_line_type_code = w.accounting_line_type_code
597 AND s.accounting_line_code = w.accounting_line_code
598 AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
599 AND s.analytical_criterion_code = w.analytical_criterion_code);
600
601 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
602 trace(p_msg => '# xla_line_defn_ac_assgns deleted = '||SQL%ROWCOUNT,
603 p_module => l_log_module,
604 p_level => C_LEVEL_STATEMENT);
605 END IF;
606
607 DELETE FROM xla_line_defn_adr_assgns w
608 WHERE application_id = g_application_id
609 AND amb_context_code = g_amb_context_code
610 AND line_definition_owner_code = C_OWNER_SYSTEM
611 AND NOT EXISTS
612 (SELECT 1
613 FROM xla_line_defn_adr_assgns s
614 WHERE s.application_id = g_application_id
615 AND s.amb_context_code = g_staging_context_code
616 AND s.event_class_code = w.event_class_code
617 AND s.event_type_code = w.event_type_code
618 AND s.line_definition_owner_code = C_OWNER_SYSTEM
619 AND s.line_definition_code = w.line_definition_code
620 AND s.accounting_line_type_code = w.accounting_line_type_code
621 AND s.accounting_line_code = w.accounting_line_code
622 AND s.flexfield_segment_code = w.flexfield_segment_code);
623
624 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
625 trace(p_msg => '# xla_line_defn_adr_assgns deleted = '||SQL%ROWCOUNT,
626 p_module => l_log_module,
627 p_level => C_LEVEL_STATEMENT);
628 END IF;
629
630 DELETE FROM xla_line_defn_jlt_assgns w
631 WHERE application_id = g_application_id
632 AND amb_context_code = g_amb_context_code
633 AND line_definition_owner_code = C_OWNER_SYSTEM
634 AND NOT EXISTS
635 (SELECT 1
636 FROM xla_line_defn_jlt_assgns s
637 WHERE s.application_id = g_application_id
641 AND s.line_definition_owner_code = C_OWNER_SYSTEM
638 AND s.amb_context_code = g_staging_context_code
639 AND s.event_class_code = w.event_class_code
640 AND s.event_type_code = w.event_type_code
642 AND s.line_definition_code = w.line_definition_code
643 AND s.accounting_line_type_code = w.accounting_line_type_code
644 AND s.accounting_line_code = w.accounting_line_code);
645
646 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
647 trace(p_msg => '# xla_line_defn_jlt_assgns deleted = '||SQL%ROWCOUNT,
648 p_module => l_log_module,
649 p_level => C_LEVEL_STATEMENT);
650 END IF;
651
652 DELETE FROM xla_mpa_jlt_assgns w
653 WHERE application_id = g_application_id
654 AND amb_context_code = g_amb_context_code
655 AND line_definition_owner_code = C_OWNER_SYSTEM
656 AND NOT EXISTS
657 (SELECT 1
658 FROM xla_mpa_jlt_assgns s
659 WHERE s.application_id = g_application_id
660 AND s.amb_context_code = g_staging_context_code
661 AND s.event_class_code = w.event_class_code
662 AND s.event_type_code = w.event_type_code
663 AND s.line_definition_owner_code = C_OWNER_SYSTEM
664 AND s.line_definition_code = w.line_definition_code
665 AND s.accounting_line_type_code = w.accounting_line_type_code
666 AND s.accounting_line_code = w.accounting_line_code
667 AND s.mpa_accounting_line_type_code = w.mpa_accounting_line_type_code
668 AND s.mpa_accounting_line_code = w.mpa_accounting_line_code);
669
670 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
671 trace(p_msg => '# xla_mpa_jlt_assgns deleted = '||SQL%ROWCOUNT,
672 p_module => l_log_module,
673 p_level => C_LEVEL_STATEMENT);
674 END IF;
675
676 DELETE FROM xla_mpa_header_ac_assgns w
677 WHERE application_id = g_application_id
678 AND amb_context_code = g_amb_context_code
679 AND line_definition_owner_code = C_OWNER_SYSTEM
680 AND NOT EXISTS
681 (SELECT 1
682 FROM xla_mpa_header_ac_assgns s
683 WHERE s.application_id = g_application_id
684 AND s.amb_context_code = g_staging_context_code
685 AND s.event_class_code = w.event_class_code
686 AND s.event_type_code = w.event_type_code
687 AND s.line_definition_owner_code = C_OWNER_SYSTEM
688 AND s.line_definition_code = w.line_definition_code
689 AND s.accounting_line_type_code = w.accounting_line_type_code
690 AND s.accounting_line_code = w.accounting_line_code
691 AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
692 AND s.analytical_criterion_code = w.analytical_criterion_code);
693
694 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
695 trace(p_msg => '# xla_mpa_header_ac_assgns deleted = '||SQL%ROWCOUNT,
696 p_module => l_log_module,
697 p_level => C_LEVEL_STATEMENT);
698 END IF;
699
700 DELETE FROM xla_mpa_jlt_adr_assgns w
701 WHERE application_id = g_application_id
702 AND amb_context_code = g_amb_context_code
703 AND line_definition_owner_code = C_OWNER_SYSTEM
704 AND NOT EXISTS
705 (SELECT 1
706 FROM xla_mpa_jlt_adr_assgns s
707 WHERE s.application_id = g_application_id
708 AND s.amb_context_code = g_staging_context_code
709 AND s.event_class_code = w.event_class_code
710 AND s.event_type_code = w.event_type_code
711 AND s.line_definition_owner_code = C_OWNER_SYSTEM
712 AND s.line_definition_code = w.line_definition_code
713 AND s.accounting_line_type_code = w.accounting_line_type_code
714 AND s.accounting_line_code = w.accounting_line_code
715 AND s.mpa_accounting_line_type_code = w.mpa_accounting_line_type_code
716 AND s.mpa_accounting_line_code = w.mpa_accounting_line_code
717 AND s.flexfield_segment_code = w.flexfield_segment_code);
718
719 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
723 END IF;
720 trace(p_msg => '# xla_mpa_jlt_adr_assgns deleted = '||SQL%ROWCOUNT,
721 p_module => l_log_module,
722 p_level => C_LEVEL_STATEMENT);
724
725 DELETE FROM xla_mpa_jlt_ac_assgns w
726 WHERE application_id = g_application_id
727 AND amb_context_code = g_amb_context_code
728 AND line_definition_owner_code = C_OWNER_SYSTEM
729 AND NOT EXISTS
730 (SELECT 1
731 FROM xla_mpa_jlt_ac_assgns s
732 WHERE s.application_id = g_application_id
733 AND s.amb_context_code = g_staging_context_code
734 AND s.event_class_code = w.event_class_code
735 AND s.event_type_code = w.event_type_code
736 AND s.line_definition_owner_code = C_OWNER_SYSTEM
737 AND s.line_definition_code = w.line_definition_code
738 AND s.accounting_line_type_code = w.accounting_line_type_code
739 AND s.accounting_line_code = w.accounting_line_code
740 AND s.mpa_accounting_line_type_code = w.mpa_accounting_line_type_code
741 AND s.mpa_accounting_line_code = w.mpa_accounting_line_code
742 AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
743 AND s.analytical_criterion_code = w.analytical_criterion_code);
744
745 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
746 trace(p_msg => '# xla_mpa_jlt_ac_assgns deleted = '||SQL%ROWCOUNT,
747 p_module => l_log_module,
748 p_level => C_LEVEL_STATEMENT);
749 END IF;
750
751 FORALL i IN 1..l_codes.COUNT
752 DELETE FROM xla_line_definitions_tl w
753 WHERE application_id = g_application_id
754 AND amb_context_code = g_amb_context_code
755 AND event_class_code = l_event_class_codes(i)
756 AND event_type_code = l_event_type_codes(i)
757 AND line_definition_owner_code = C_OWNER_SYSTEM
758 AND line_definition_code = l_codes(i);
759
760 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
761 trace(p_msg => '# xla_line_definitions_tl deleted = '||SQL%ROWCOUNT,
762 p_module => l_log_module,
763 p_level => C_LEVEL_STATEMENT);
764 END IF;
765
766 FORALL i IN 1..l_codes.COUNT
767 DELETE FROM xla_line_definitions_b w
768 WHERE application_id = g_application_id
769 AND amb_context_code = g_amb_context_code
770 AND event_class_code = l_event_class_codes(i)
771 AND event_type_code = l_event_type_codes(i)
772 AND line_definition_owner_code = C_OWNER_SYSTEM
773 AND line_definition_code = l_codes(i);
774
775 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
776 trace(p_msg => '# xla_line_definitions_b deleted = '||SQL%ROWCOUNT,
777 p_module => l_log_module,
778 p_level => C_LEVEL_STATEMENT);
779 END IF;
780
781 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
782 trace(p_msg => 'END of procedure clean_oracle_jlds',
783 p_module => l_log_module,
784 p_level => C_LEVEL_PROCEDURE);
785 END IF;
786
787 EXCEPTION
788 WHEN OTHERS THEN
789 xla_aad_loader_util_pvt.stack_error
790 (p_appli_s_name => 'XLA'
791 ,p_msg_name => 'XLA_COMMON_ERROR'
792 ,p_token_1 => 'LOCATION'
793 ,p_value_1 => 'xla_aad_merge_pvt.clean_oracle_jlds'
794 ,p_token_2 => 'ERROR'
795 ,p_value_2 => 'unhandled exception');
796 RAISE;
797
798 END clean_oracle_jlds;
799
800
801 --=============================================================================
802 --
803 -- Name: clean_oracle_jlts
804 -- Description:
805 --
806 --=============================================================================
807 PROCEDURE clean_oracle_jlts
808 IS
809 CURSOR c_all_comps IS
810 SELECT work.event_class_code
811 , work.accounting_line_code
812 FROM xla_acct_line_types_b work
813 WHERE work.application_id = g_application_id
814 AND work.amb_context_code = g_amb_context_code
815 AND work.accounting_line_type_code = C_OWNER_SYSTEM
816 AND NOT EXISTS ( SELECT 1
817 FROM xla_acct_line_types_b stage
818 WHERE stage.application_id = g_application_id
819 AND stage.amb_context_code = g_staging_context_code
820 AND stage.event_class_code = work.event_class_code
821 AND stage.accounting_line_type_code = C_OWNER_SYSTEM
822 AND stage.accounting_line_code = work.accounting_line_code);
823
824 l_event_class_codes t_array_varchar30;
825 l_codes t_array_varchar30;
826
827 l_log_module VARCHAR2(240);
828 BEGIN
829 IF g_log_enabled THEN
830 l_log_module := C_DEFAULT_MODULE||'.clean_oracle_jlts';
831 END IF;
832
833 IF (g_analyzed_flag = 'Y') THEN
834 null;
835 ELSE
836 OPEN c_all_comps;
840
837 FETCH c_all_comps BULK COLLECT INTO l_event_class_codes
838 , l_codes;
839 CLOSE c_all_comps;
841 END IF;
842
843 IF (l_codes.COUNT > 0) THEN
844
845 FORALL i IN 1..l_codes.COUNT
846 DELETE FROM xla_mpa_jlt_adr_assgns w
847 WHERE application_id = g_application_id
848 AND amb_context_code = g_amb_context_code
849 AND event_class_code = l_event_class_codes(i)
850 AND accounting_line_type_code = C_OWNER_SYSTEM
851 AND accounting_line_code = l_codes(i);
852
853 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
854 trace(p_msg => '# xla_mpa_jlt_adr_assgns deleted = '||SQL%ROWCOUNT,
855 p_module => l_log_module,
856 p_level => C_LEVEL_STATEMENT);
857 END IF;
858
859 FORALL i IN 1..l_codes.COUNT
860 DELETE FROM xla_mpa_jlt_ac_assgns w
861 WHERE application_id = g_application_id
862 AND amb_context_code = g_amb_context_code
863 AND event_class_code = l_event_class_codes(i)
864 AND accounting_line_type_code = C_OWNER_SYSTEM
865 AND accounting_line_code = l_codes(i);
866
867 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
868 trace(p_msg => '# xla_mpa_jlt_ac_assgns deleted = '||SQL%ROWCOUNT,
869 p_module => l_log_module,
870 p_level => C_LEVEL_STATEMENT);
871 END IF;
872
873 FORALL i IN 1..l_codes.COUNT
874 DELETE FROM xla_mpa_header_ac_assgns w
875 WHERE application_id = g_application_id
876 AND amb_context_code = g_amb_context_code
877 AND event_class_code = l_event_class_codes(i)
878 AND accounting_line_type_code = C_OWNER_SYSTEM
879 AND accounting_line_code = l_codes(i);
880
881 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
882 trace(p_msg => '# xla_mpa_header_ac_assgns deleted = '||SQL%ROWCOUNT,
883 p_module => l_log_module,
884 p_level => C_LEVEL_STATEMENT);
885 END IF;
886
887 FORALL i IN 1..l_codes.COUNT
888 DELETE FROM xla_mpa_jlt_assgns w
889 WHERE application_id = g_application_id
890 AND amb_context_code = g_amb_context_code
891 AND event_class_code = l_event_class_codes(i)
892 AND accounting_line_type_code = C_OWNER_SYSTEM
893 AND accounting_line_code = l_codes(i);
894
895 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
896 trace(p_msg => '# xla_mpa_jlt_assgns deleted = '||SQL%ROWCOUNT,
897 p_module => l_log_module,
898 p_level => C_LEVEL_STATEMENT);
899 END IF;
900
901 FORALL i IN 1..l_codes.COUNT
902 DELETE FROM xla_line_defn_adr_assgns w
903 WHERE application_id = g_application_id
904 AND amb_context_code = g_amb_context_code
905 AND event_class_code = l_event_class_codes(i)
906 AND accounting_line_type_code = C_OWNER_SYSTEM
907 AND accounting_line_code = l_codes(i);
908
909 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
910 trace(p_msg => '# xla_line_defn_adr_assgns deleted = '||SQL%ROWCOUNT,
911 p_module => l_log_module,
912 p_level => C_LEVEL_STATEMENT);
913 END IF;
914
915 FORALL i IN 1..l_codes.COUNT
916 DELETE FROM xla_line_defn_ac_assgns w
917 WHERE application_id = g_application_id
918 AND amb_context_code = g_amb_context_code
919 AND event_class_code = l_event_class_codes(i)
920 AND accounting_line_type_code = C_OWNER_SYSTEM
921 AND accounting_line_code = l_codes(i);
922
923 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
924 trace(p_msg => '# xla_line_defn_ac_assgns deleted = '||SQL%ROWCOUNT,
925 p_module => l_log_module,
926 p_level => C_LEVEL_STATEMENT);
927 END IF;
928
929 FORALL i IN 1..l_codes.COUNT
930 DELETE FROM xla_line_defn_jlt_assgns w
931 WHERE application_id = g_application_id
932 AND amb_context_code = g_amb_context_code
933 AND event_class_code = l_event_class_codes(i)
934 AND accounting_line_type_code = C_OWNER_SYSTEM
935 AND accounting_line_code = l_codes(i);
936
937 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
938 trace(p_msg => '# xla_line_defn_jlt_assgns deleted = '||SQL%ROWCOUNT,
939 p_module => l_log_module,
940 p_level => C_LEVEL_STATEMENT);
941 END IF;
942
943 FORALL i IN 1..l_codes.COUNT
944 DELETE FROM xla_jlt_acct_attrs w
945 WHERE application_id = g_application_id
946 AND amb_context_code = g_amb_context_code
947 AND event_class_code = l_event_class_codes(i)
948 AND accounting_line_type_code = C_OWNER_SYSTEM
949 AND accounting_line_code = l_codes(i);
950
951 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
952 trace(p_msg => '# xla_jlt_acct_attrs deleted = '||SQL%ROWCOUNT,
953 p_module => l_log_module,
954 p_level => C_LEVEL_STATEMENT);
955 END IF;
956
957 FORALL i IN 1..l_codes.COUNT
958 DELETE FROM xla_acct_line_types_b w
959 WHERE application_id = g_application_id
960 AND amb_context_code = g_amb_context_code
961 AND event_class_code = l_event_class_codes(i)
962 AND accounting_line_type_code = C_OWNER_SYSTEM
966 trace(p_msg => '# xla_acct_line_types_b deleted = '||SQL%ROWCOUNT,
963 AND accounting_line_code = l_codes(i);
964
965 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
967 p_module => l_log_module,
968 p_level => C_LEVEL_STATEMENT);
969 END IF;
970
971 FORALL i IN 1..l_codes.COUNT
972 DELETE FROM xla_acct_line_types_tl w
973 WHERE application_id = g_application_id
974 AND amb_context_code = g_amb_context_code
975 AND event_class_code = l_event_class_codes(i)
976 AND accounting_line_type_code = C_OWNER_SYSTEM
977 AND accounting_line_code = l_codes(i);
978
979 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
980 trace(p_msg => '# xla_acct_line_types_tl deleted = '||SQL%ROWCOUNT,
981 p_module => l_log_module,
982 p_level => C_LEVEL_STATEMENT);
983 END IF;
984
985 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
986 trace(p_msg => 'BEGIN of procedure clean_oracle_jlts',
987 p_module => l_log_module,
988 p_level => C_LEVEL_PROCEDURE);
989 END IF;
990
991 END IF;
992
993 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
994 trace(p_msg => 'END of procedure clean_oracle_jlts',
995 p_module => l_log_module,
996 p_level => C_LEVEL_PROCEDURE);
997 END IF;
998
999 EXCEPTION
1000 WHEN OTHERS THEN
1001 xla_aad_loader_util_pvt.stack_error
1002 (p_appli_s_name => 'XLA'
1003 ,p_msg_name => 'XLA_COMMON_ERROR'
1004 ,p_token_1 => 'LOCATION'
1005 ,p_value_1 => 'xla_aad_merge_pvt.clean_oracle_jlts'
1006 ,p_token_2 => 'ERROR'
1007 ,p_value_2 => 'unhandled exception');
1008 RAISE;
1009
1010 END clean_oracle_jlts;
1011
1012
1013 --=============================================================================
1014 --
1015 -- Name: clean_oracle_descriptions
1016 -- Description:
1017 --
1018 --=============================================================================
1019 PROCEDURE clean_oracle_descriptions
1020 IS
1021 CURSOR c_all_comps IS
1022 SELECT work.description_code
1023 FROM xla_descriptions_b work
1024 WHERE work.application_id = g_application_id
1025 AND work.amb_context_code = g_amb_context_code
1026 AND work.description_type_code = C_OWNER_SYSTEM
1027 AND NOT EXISTS ( SELECT 1
1028 FROM xla_descriptions_b stage
1029 WHERE stage.application_id = g_application_id
1030 AND stage.amb_context_code = g_staging_context_code
1031 AND stage.description_type_code = C_OWNER_SYSTEM
1032 AND stage.description_code = work.description_code);
1033
1034 l_codes t_array_varchar30;
1035
1036 l_log_module VARCHAR2(240);
1037 BEGIN
1038 IF g_log_enabled THEN
1039 l_log_module := C_DEFAULT_MODULE||'.clean_oracle_descriptions';
1040 END IF;
1041
1042 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1043 trace(p_msg => 'BEGIN of procedure clean_oracle_descriptions',
1044 p_module => l_log_module,
1045 p_level => C_LEVEL_PROCEDURE);
1046 END IF;
1047
1048 IF (g_analyzed_flag = 'Y') THEN
1049 null;
1050 ELSE
1051 OPEN c_all_comps;
1052 FETCH c_all_comps BULK COLLECT INTO l_codes;
1053 CLOSE c_all_comps;
1054
1055 END IF;
1056
1057 IF (l_codes.COUNT > 0) THEN
1058 FORALL i IN 1..l_codes.COUNT
1059 UPDATE xla_line_defn_jlt_assgns
1060 SET description_type_code = NULL
1061 , description_code = NULL
1062 WHERE application_id = g_application_id
1063 AND amb_context_code = g_amb_context_code
1064 AND description_type_code = C_OWNER_SYSTEM
1065 AND description_code = l_codes(i);
1066
1067 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1068 trace(p_msg => '# xla_line_defn_jlt_assgns clear description = '||SQL%ROWCOUNT,
1069 p_module => l_log_module,
1070 p_level => C_LEVEL_STATEMENT);
1071 END IF;
1072
1073 FORALL i IN 1..l_codes.COUNT
1074 UPDATE xla_prod_acct_headers
1075 SET description_type_code = NULL
1076 , description_code = NULL
1077 WHERE application_id = g_application_id
1078 AND amb_context_code = g_amb_context_code
1079 AND description_type_code = C_OWNER_SYSTEM
1080 AND description_code = l_codes(i);
1081
1082 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1083 trace(p_msg => '# xla_prod_acct_headers clear description = '||SQL%ROWCOUNT,
1084 p_module => l_log_module,
1085 p_level => C_LEVEL_STATEMENT);
1086 END IF;
1087
1088 FORALL i IN 1..l_codes.COUNT
1089 DELETE FROM xla_descript_details_tl w
1090 WHERE description_detail_id IN
1091 (SELECT description_detail_id
1092 FROM xla_descript_details_b d
1093 , xla_desc_priorities p
1094 WHERE d.description_prio_id = p.description_prio_id
1095 AND p.application_id = g_application_id
1096 AND p.amb_context_code = g_amb_context_code
1097 AND p.description_type_code = C_OWNER_SYSTEM
1098 AND p.description_code = l_codes(i));
1099
1103 p_level => C_LEVEL_STATEMENT);
1100 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1101 trace(p_msg => '# xla_descript_details_tl deleted = '||SQL%ROWCOUNT,
1102 p_module => l_log_module,
1104 END IF;
1105
1106 FORALL i IN 1..l_codes.COUNT
1107 DELETE FROM xla_descript_details_b w
1108 WHERE description_prio_id IN
1109 (SELECT description_prio_id
1110 FROM xla_desc_priorities p
1111 WHERE p.application_id = g_application_id
1112 AND p.amb_context_code = g_amb_context_code
1113 AND p.description_type_code = C_OWNER_SYSTEM
1114 AND p.description_code = l_codes(i));
1115
1116 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1117 trace(p_msg => '# xla_descript_details_b deleted = '||SQL%ROWCOUNT,
1118 p_module => l_log_module,
1119 p_level => C_LEVEL_STATEMENT);
1120 END IF;
1121
1122 FORALL i IN 1..l_codes.COUNT
1123 DELETE FROM xla_desc_priorities w
1124 WHERE application_id = g_application_id
1125 AND amb_context_code = g_amb_context_code
1126 AND description_type_code = C_OWNER_SYSTEM
1127 AND description_code = l_codes(i);
1128
1129 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1130 trace(p_msg => '# xla_desc_priorities deleted = '||SQL%ROWCOUNT,
1131 p_module => l_log_module,
1132 p_level => C_LEVEL_STATEMENT);
1133 END IF;
1134
1135 FORALL i IN 1..l_codes.COUNT
1136 DELETE FROM xla_descriptions_tl w
1137 WHERE application_id = g_application_id
1138 AND amb_context_code = g_amb_context_code
1139 AND description_type_code = C_OWNER_SYSTEM
1140 AND description_code = l_codes(i);
1141
1142 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1143 trace(p_msg => '# xla_descriptions_tl deleted = '||SQL%ROWCOUNT,
1144 p_module => l_log_module,
1145 p_level => C_LEVEL_STATEMENT);
1146 END IF;
1147
1148 FORALL i IN 1..l_codes.COUNT
1149 DELETE FROM xla_descriptions_b w
1150 WHERE application_id = g_application_id
1151 AND amb_context_code = g_amb_context_code
1152 AND description_type_code = C_OWNER_SYSTEM
1153 AND description_code = l_codes(i);
1154
1155 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1156 trace(p_msg => '# xla_descriptions_b deleted = '||SQL%ROWCOUNT,
1157 p_module => l_log_module,
1158 p_level => C_LEVEL_STATEMENT);
1159 END IF;
1160
1161 END IF;
1162
1163 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1164 trace(p_msg => 'END of procedure clean_oracle_descriptions',
1165 p_module => l_log_module,
1166 p_level => C_LEVEL_PROCEDURE);
1167 END IF;
1168
1169 EXCEPTION
1170 WHEN OTHERS THEN
1171 xla_aad_loader_util_pvt.stack_error
1172 (p_appli_s_name => 'XLA'
1173 ,p_msg_name => 'XLA_COMMON_ERROR'
1174 ,p_token_1 => 'LOCATION'
1175 ,p_value_1 => 'xla_aad_merge_pvt.clean_oracle_descriptions'
1176 ,p_token_2 => 'ERROR'
1177 ,p_value_2 => 'unhandled exception');
1178 RAISE;
1179
1180 END clean_oracle_descriptions;
1181
1182
1183 --=============================================================================
1184 --
1185 -- Name: clean_oracle_adrs
1186 -- Description:
1187 --
1188 --=============================================================================
1189 PROCEDURE clean_oracle_adrs
1190 IS
1191 -- Retrieve the Oracle adr to be deleted
1192 CURSOR c_all_comps IS
1193 SELECT work.segment_rule_code
1194 FROM xla_seg_rules_b work
1195 WHERE work.application_id = g_application_id
1196 AND work.amb_context_code = g_amb_context_code
1197 AND work.segment_rule_type_code = C_OWNER_SYSTEM
1198 AND NOT EXISTS
1199 (SELECT 1
1200 FROM xla_seg_rules_b stage
1201 WHERE stage.application_id = g_application_id
1202 AND stage.amb_context_code = g_staging_context_code
1203 AND stage.segment_rule_type_code = C_OWNER_SYSTEM
1204 AND stage.segment_rule_code = work.segment_rule_code);
1205
1206 l_codes t_array_varchar30;
1207 i INTEGER;
1208
1209 l_log_module VARCHAR2(240);
1210 BEGIN
1211 IF g_log_enabled THEN
1212 l_log_module := C_DEFAULT_MODULE||'.clean_oracle_adrs';
1213 END IF;
1214
1215 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1216 trace(p_msg => 'BEGIN of procedure clean_oracle_adrs',
1217 p_module => l_log_module,
1218 p_level => C_LEVEL_PROCEDURE);
1219 END IF;
1220
1221 IF (g_analyzed_flag = 'Y') THEN
1222 null;
1223 ELSE
1224 OPEN c_all_comps;
1225 FETCH c_all_comps BULK COLLECT INTO l_codes;
1226 CLOSE c_all_comps;
1227
1228 END IF;
1229
1230 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1231 trace(p_msg => '# ADRs to be deleted = '||l_codes.COUNT,
1232 p_module => l_log_module,
1233 p_level => C_LEVEL_PROCEDURE);
1234 END IF;
1235
1236 -- Record if the deleted AAD is used by any other application
1237 IF (l_codes.COUNT > 0) THEN
1238
1242 ,amb_context_code
1239 FORALL i IN 1..l_codes.COUNT
1240 INSERT INTO xla_aad_loader_logs
1241 (aad_loader_log_id
1243 ,application_id
1244 ,request_code
1245 ,log_type_code
1246 ,aad_application_id
1247 ,component_type_code
1248 ,component_owner_code
1249 ,component_code
1250 ,object_version_number
1251 ,creation_date
1252 ,created_by
1253 ,last_update_date
1254 ,last_updated_by
1255 ,last_update_login
1256 ,program_update_date
1257 ,program_application_id
1258 ,program_id
1259 ,request_id)
1260 SELECT xla_aad_loader_logs_s.nextval
1261 ,g_amb_context_code
1262 ,g_application_id
1263 ,'IMPORT'
1264 ,'DELETED_SETUP'
1265 ,application_id
1266 ,'AMB_ADR'
1267 ,C_OWNER_SYSTEM
1268 ,l_codes(i)
1269 ,1
1270 ,sysdate
1271 ,xla_environment_pkg.g_usr_id
1272 ,sysdate
1273 ,xla_environment_pkg.g_usr_id
1274 ,xla_environment_pkg.g_login_id
1275 ,sysdate
1276 ,xla_environment_pkg.g_prog_appl_id
1277 ,xla_environment_pkg.g_prog_id
1278 ,xla_environment_pkg.g_req_Id
1279 FROM (SELECT application_id
1280 FROM xla_seg_rule_details s
1281 WHERE application_id <> g_application_id
1282 AND amb_context_code = g_amb_context_code
1283 AND value_segment_rule_appl_id = g_application_id
1284 AND value_segment_rule_type_code = C_OWNER_SYSTEM
1285 AND value_segment_rule_code = l_codes(i)
1286 UNION
1287 SELECT application_id
1288 FROM xla_line_defn_adr_assgns
1289 WHERE application_id <> g_application_id
1290 AND amb_context_code = g_amb_context_code
1291 AND segment_rule_appl_id = g_application_id
1292 AND segment_rule_type_code = C_OWNER_SYSTEM
1293 AND segment_rule_code = l_codes(i)
1294 UNION
1295 SELECT application_id
1296 FROM xla_mpa_jlt_adr_assgns
1297 WHERE application_id <> g_application_id
1298 AND amb_context_code = g_amb_context_code
1299 AND segment_rule_appl_id = g_application_id
1300 AND segment_rule_type_code = C_OWNER_SYSTEM
1301 AND segment_rule_code = l_codes(i));
1302
1303 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1304 trace(p_msg => '# xla_aad_loader_logs inserted = '||SQL%ROWCOUNT,
1305 p_module => l_log_module,
1306 p_level => C_LEVEL_STATEMENT);
1307 END IF;
1308
1309 -- Delete the reference to Oracle ADR to be deleted
1310 FORALL i IN 1..l_codes.COUNT
1311 DELETE FROM xla_seg_rule_details d
1312 WHERE amb_context_code = g_amb_context_code
1313 AND value_segment_rule_appl_id = g_application_id
1314 AND value_segment_rule_type_code = C_OWNER_SYSTEM
1315 AND value_segment_rule_code = l_codes(i);
1316
1317 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1318 trace(p_msg => '# xla_seg_rules_details (value) deleted = '||SQL%ROWCOUNT,
1319 p_module => l_log_module,
1320 p_level => C_LEVEL_STATEMENT);
1321 END IF;
1322
1323 FORALL i IN 1..l_codes.COUNT
1324 DELETE FROM xla_mpa_jlt_adr_assgns w
1325 WHERE amb_context_code = g_amb_context_code
1326 AND segment_rule_appl_id = g_application_id
1327 AND segment_rule_type_code = C_OWNER_SYSTEM
1328 AND segment_rule_code = l_codes(i);
1329
1330 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1331 trace(p_msg => '# xla_mpa_jlt_adr_assgns deleted = '||SQL%ROWCOUNT,
1332 p_module => l_log_module,
1333 p_level => C_LEVEL_STATEMENT);
1334 END IF;
1335
1336 FORALL i IN 1..l_codes.COUNT
1337 DELETE FROM xla_line_defn_adr_assgns w
1338 WHERE amb_context_code = g_amb_context_code
1339 AND segment_rule_appl_id = g_application_id
1340 AND segment_rule_type_code = C_OWNER_SYSTEM
1341 AND segment_rule_code = l_codes(i);
1342
1343 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1344 trace(p_msg => '# xla_line_defn_adr_assgns deleted = '||SQL%ROWCOUNT,
1345 p_module => l_log_module,
1346 p_level => C_LEVEL_STATEMENT);
1347 END IF;
1348
1349 -- Delete the ADR
1350 FORALL i IN 1..l_codes.COUNT
1351 DELETE FROM xla_seg_rule_details w
1352 WHERE application_id = g_application_id
1353 AND amb_context_code = g_amb_context_code
1354 AND segment_rule_type_code = C_OWNER_SYSTEM
1355 AND segment_rule_code = l_codes(i);
1356
1357 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1358 trace(p_msg => '# xla_seg_rule_details deleted = '||SQL%ROWCOUNT,
1359 p_module => l_log_module,
1360 p_level => C_LEVEL_STATEMENT);
1361 END IF;
1362
1363 FORALL i IN 1..l_codes.COUNT
1364 DELETE FROM xla_seg_rules_tl w
1365 WHERE application_id = g_application_id
1366 AND amb_context_code = g_amb_context_code
1367 AND segment_rule_type_code = C_OWNER_SYSTEM
1368 AND segment_rule_code = l_codes(i);
1369
1370 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1374 END IF;
1371 trace(p_msg => '# xla_seg_rules_tl deleted = '||SQL%ROWCOUNT,
1372 p_module => l_log_module,
1373 p_level => C_LEVEL_STATEMENT);
1375
1376 FORALL i IN 1..l_codes.COUNT
1377 DELETE FROM xla_seg_rules_b w
1378 WHERE application_id = g_application_id
1379 AND amb_context_code = g_amb_context_code
1380 AND segment_rule_type_code = C_OWNER_SYSTEM
1381 AND segment_rule_code = l_codes(i);
1382
1383 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1384 trace(p_msg => '#xla_seg_rules_b deleted = '||SQL%ROWCOUNT,
1385 p_module => l_log_module,
1386 p_level => C_LEVEL_STATEMENT);
1387 END IF;
1388
1389 END IF;
1390
1391 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1392 trace(p_msg => 'END of procedure clean_oracle_adrs',
1393 p_module => l_log_module,
1394 p_level => C_LEVEL_PROCEDURE);
1395 END IF;
1396
1397 EXCEPTION
1398 WHEN OTHERS THEN
1399 xla_aad_loader_util_pvt.stack_error
1400 (p_appli_s_name => 'XLA'
1401 ,p_msg_name => 'XLA_COMMON_ERROR'
1402 ,p_token_1 => 'LOCATION'
1403 ,p_value_1 => 'xla_aad_merge_pvt.clean_oracle_adrs'
1404 ,p_token_2 => 'ERROR'
1405 ,p_value_2 => 'unhandled exception');
1406 RAISE;
1407
1408 END clean_oracle_adrs;
1409
1410
1411 --=============================================================================
1412 --
1413 -- Name: clean_oracle_acs
1414 -- Description:
1415 --
1416 --=============================================================================
1417 PROCEDURE clean_oracle_acs
1418 IS
1419 /*CURSOR c_all_comps IS
1420 SELECT w.analytical_criterion_code
1421 FROM xla_analytical_hdrs_b w
1422 WHERE w.amb_context_code = g_amb_context_code
1423 AND w.application_id = g_application_id
1424 AND w.analytical_criterion_type_code = C_OWNER_SYSTEM
1425 AND NOT EXISTS ( SELECT 1
1426 FROM xla_analytical_hdrs_b s
1427 WHERE s.amb_context_code = g_staging_context_code
1428 AND s.application_id = g_application_id
1429 AND s.analytical_criterion_type_code = C_OWNER_SYSTEM
1430 AND s.analytical_criterion_code = w.analytical_criterion_code);
1431
1432 l_codes t_array_varchar30;*/ -- commented bug6696939
1433
1434 l_log_module VARCHAR2(240);
1435 BEGIN
1436 IF g_log_enabled THEN
1437 l_log_module := C_DEFAULT_MODULE||'.clean_oracle_acs';
1438 END IF;
1439
1440 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1441 trace(p_msg => 'BEGIN of procedure clean_oracle_acs',
1442 p_module => l_log_module,
1443 p_level => C_LEVEL_PROCEDURE);
1444 END IF;
1445
1446 /*IF (g_analyzed_flag = 'Y') THEN
1447 null;
1448 ELSE
1449 OPEN c_all_comps;
1450 FETCH c_all_comps BULK COLLECT INTO l_codes;
1451 CLOSE c_all_comps;
1452
1453 END IF;
1454
1455 -- Delete reference to the AC
1456 FORALL i IN 1..l_codes.COUNT
1457 DELETE FROM xla_aad_header_ac_assgns w
1458 WHERE application_id = g_application_id
1459 AND amb_context_code = g_amb_context_code
1460 AND analytical_criterion_type_code = C_OWNER_SYSTEM
1461 AND analytical_criterion_code = l_codes(i);
1462
1463 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1464 trace(p_msg => '# xla_aad_header_ac_assgns deleted = '||SQL%ROWCOUNT,
1465 p_module => l_log_module,
1466 p_level => C_LEVEL_STATEMENT);
1467 END IF;
1468
1469 FORALL i IN 1..l_codes.COUNT
1470 DELETE FROM xla_line_defn_ac_assgns w
1471 WHERE amb_context_code = g_amb_context_code
1472 AND analytical_criterion_type_code = C_OWNER_SYSTEM
1473 AND analytical_criterion_code = l_codes(i);
1474
1475 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1476 trace(p_msg => '# xla_line_defn_ac_assgns deleted = '||SQL%ROWCOUNT,
1477 p_module => l_log_module,
1478 p_level => C_LEVEL_STATEMENT);
1479 END IF;
1480
1481 FORALL i IN 1..l_codes.COUNT
1482 DELETE FROM xla_mpa_header_ac_assgns w
1483 WHERE amb_context_code = g_amb_context_code
1484 AND analytical_criterion_type_code = C_OWNER_SYSTEM
1485 AND analytical_criterion_code = l_codes(i);
1486
1487 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1488 trace(p_msg => '# xla_mpa_header_ac_assgns deleted = '||SQL%ROWCOUNT,
1489 p_module => l_log_module,
1490 p_level => C_LEVEL_STATEMENT);
1491 END IF;
1492
1493 FORALL i IN 1..l_codes.COUNT
1494 DELETE FROM xla_mpa_jlt_ac_assgns w
1495 WHERE amb_context_code = g_amb_context_code
1496 AND analytical_criterion_type_code = C_OWNER_SYSTEM
1497 AND analytical_criterion_code = l_codes(i);
1498
1499 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1500 trace(p_msg => '# xla_mpa_jlt_ac_assgns deleted = '||SQL%ROWCOUNT,
1501 p_module => l_log_module,
1502 p_level => C_LEVEL_STATEMENT);
1503 END IF;*/ -- commented bug6696939
1504
1508 AND analytical_criterion_type_code = C_OWNER_SYSTEM
1505 -- Delete the AC
1506 DELETE FROM xla_analytical_sources w
1507 WHERE amb_context_code = g_amb_context_code
1509 AND application_id = g_application_id -- added bug6696939
1510 AND NOT EXISTS
1511 (SELECT 1
1512 FROM xla_analytical_sources s
1513 WHERE s.amb_context_code = g_staging_context_code
1514 AND s.application_id = g_application_id
1515 AND s.entity_code = w.entity_code
1516 AND s.event_class_code = w.event_class_code
1517 AND s.source_application_id = w.source_application_id
1518 AND s.source_type_code = w.source_type_code
1519 AND s.source_code = w.source_code
1520 AND s.analytical_criterion_type_code = C_OWNER_SYSTEM
1521 AND s.analytical_criterion_code = w.analytical_criterion_code
1522 AND s.analytical_detail_code = w.analytical_detail_code);
1523
1524 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1525 trace(p_msg => '# xla_analytical_sources deleted = '||SQL%ROWCOUNT,
1526 p_module => l_log_module,
1527 p_level => C_LEVEL_STATEMENT);
1528 END IF;
1529
1530 /*DELETE FROM xla_analytical_dtls_tl w
1531 WHERE amb_context_code = g_amb_context_code
1532 AND analytical_criterion_type_code = C_OWNER_SYSTEM
1533 AND NOT EXISTS
1534 (SELECT 1
1535 FROM xla_analytical_dtls_b s
1536 WHERE s.amb_context_code = g_staging_context_code
1537 AND s.analytical_criterion_type_code = C_OWNER_SYSTEM
1538 AND s.analytical_criterion_code = w.analytical_criterion_code
1539 AND s.analytical_detail_code = w.analytical_detail_code);
1540
1541 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1542 trace(p_msg => '# xla_analytical_dtls_tl deleted = '||SQL%ROWCOUNT,
1543 p_module => l_log_module,
1544 p_level => C_LEVEL_STATEMENT);
1545 END IF;
1546
1547 DELETE FROM xla_analytical_dtls_b w
1548 WHERE amb_context_code = g_amb_context_code
1549 AND analytical_criterion_type_code = C_OWNER_SYSTEM
1550 AND NOT EXISTS
1551 (SELECT 1
1552 FROM xla_analytical_dtls_b s
1553 WHERE s.amb_context_code = g_staging_context_code
1554 AND s.analytical_criterion_type_code = C_OWNER_SYSTEM
1555 AND s.analytical_criterion_code = w.analytical_criterion_code
1556 AND s.analytical_detail_code = w.analytical_detail_code);
1557
1558 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1559 trace(p_msg => '# xla_analytical_dtls_b deleted = '||SQL%ROWCOUNT,
1560 p_module => l_log_module,
1561 p_level => C_LEVEL_STATEMENT);
1562 END IF;
1563
1564 FORALL i IN 1..l_codes.COUNT
1565 DELETE FROM xla_analytical_hdrs_tl w
1566 WHERE amb_context_code = g_amb_context_code
1567 AND analytical_criterion_type_code = C_OWNER_SYSTEM
1568 AND analytical_criterion_code = l_codes(i);
1569
1570 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1571 trace(p_msg => '# xla_analytical_hdrs_tl deleted = '||SQL%ROWCOUNT,
1572 p_module => l_log_module,
1573 p_level => C_LEVEL_STATEMENT);
1574 END IF;
1575
1576 FORALL i IN 1..l_codes.COUNT
1577 DELETE FROM xla_analytical_hdrs_b w
1578 WHERE amb_context_code = g_amb_context_code
1579 AND analytical_criterion_type_code = C_OWNER_SYSTEM
1580 AND analytical_criterion_code = l_codes(i);
1581
1582 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1583 trace(p_msg => '# xla_analytical_hdrs_b deleted = '||SQL%ROWCOUNT,
1584 p_module => l_log_module,
1585 p_level => C_LEVEL_STATEMENT);
1586 END IF;*/ -- commented bug6696939
1587
1588 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1589 trace(p_msg => 'END of procedure clean_oracle_acs',
1590 p_module => l_log_module,
1591 p_level => C_LEVEL_PROCEDURE);
1592 END IF;
1593
1594 EXCEPTION
1595 WHEN OTHERS THEN
1596 xla_aad_loader_util_pvt.stack_error
1597 (p_appli_s_name => 'XLA'
1598 ,p_msg_name => 'XLA_COMMON_ERROR'
1599 ,p_token_1 => 'LOCATION'
1600 ,p_value_1 => 'xla_aad_merge_pvt.clean_oracle_acs'
1601 ,p_token_2 => 'ERROR'
1602 ,p_value_2 => 'unhandled exception');
1603 RAISE;
1604
1605 END clean_oracle_acs;
1606
1607
1608 --=============================================================================
1609 --
1610 -- Name: clean_oracle_components
1611 -- Description:
1612 --
1613 --=============================================================================
1614 PROCEDURE clean_oracle_components
1615 IS
1616 l_log_module VARCHAR2(240);
1617 BEGIN
1618 IF g_log_enabled THEN
1619 l_log_module := C_DEFAULT_MODULE||'.clean_oracle_components';
1620 END IF;
1621
1622 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1623 trace(p_msg => 'BEGIN of procedure clean_oracle_components',
1624 p_module => l_log_module,
1625 p_level => C_LEVEL_PROCEDURE);
1626 END IF;
1627
1628 clean_oracle_aads;
1632 clean_oracle_adrs;
1629 clean_oracle_jlds;
1630 clean_oracle_jlts;
1631 clean_oracle_descriptions;
1633 clean_oracle_acs;
1634
1635 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1636 trace(p_msg => 'END of procedure clean_oracle_components',
1637 p_module => l_log_module,
1638 p_level => C_LEVEL_PROCEDURE);
1639 END IF;
1640
1641 EXCEPTION
1642 WHEN OTHERS THEN
1643 xla_aad_loader_util_pvt.stack_error
1644 (p_appli_s_name => 'XLA'
1645 ,p_msg_name => 'XLA_COMMON_ERROR'
1646 ,p_token_1 => 'LOCATION'
1647 ,p_value_1 => 'xla_aad_merge_pvt.clean_oracle_components'
1648 ,p_token_2 => 'ERROR'
1649 ,p_value_2 => 'unhandled exception');
1650 RAISE;
1651
1652 END clean_oracle_components;
1653
1654 --=============================================================================
1655 --
1656 -- Name: merge_aads
1657 -- Description: Merge AADs from staging to working area
1658 --
1659 --=============================================================================
1660 PROCEDURE merge_aads
1661 IS
1662 l_log_module VARCHAR2(240);
1663 BEGIN
1664 IF g_log_enabled THEN
1665 l_log_module := C_DEFAULT_MODULE||'.merge_aads';
1666 END IF;
1667
1668 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1669 trace(p_msg => 'BEGIN of procedure merge_aads',
1670 p_module => l_log_module,
1671 p_level => C_LEVEL_PROCEDURE);
1672 END IF;
1673
1674 IF (g_analyzed_flag = 'Y') THEN
1675 null;
1676 ELSE
1677 -- record log
1678 INSERT INTO xla_aad_loader_logs
1679 (aad_loader_log_id
1680 ,amb_context_code
1681 ,application_id
1682 ,request_code
1683 ,log_type_code
1684 ,aad_application_id
1685 ,product_rule_code
1686 ,product_rule_type_code
1687 ,version_from
1688 ,version_to
1689 ,object_version_number
1690 ,creation_date
1691 ,created_by
1692 ,last_update_date
1693 ,last_updated_by
1694 ,last_update_login
1695 ,program_update_date
1696 ,program_application_id
1697 ,program_id
1698 ,request_id)
1699 SELECT xla_aad_loader_logs_s.nextval
1700 ,g_amb_context_code
1701 ,g_application_id
1702 ,'IMPORT'
1703 ,'MERGED_AAD'
1704 ,g_application_id
1705 ,w.product_rule_code
1706 ,w.product_rule_type_code
1707 ,w.version_num
1708 ,s.version_num
1709 ,1
1710 ,sysdate
1711 ,xla_environment_pkg.g_usr_id
1712 ,sysdate
1713 ,xla_environment_pkg.g_usr_id
1714 ,xla_environment_pkg.g_login_id
1715 ,sysdate
1716 ,xla_environment_pkg.g_prog_appl_id
1717 ,xla_environment_pkg.g_prog_id
1718 ,xla_environment_pkg.g_req_Id
1719 FROM xla_product_rules_b s
1720 , xla_product_rules_b w
1721 WHERE s.application_id = g_application_id
1722 AND s.amb_context_code = g_staging_context_code
1723 AND w.application_id = g_application_id
1724 AND w.amb_context_code = g_amb_context_code
1725 AND w.product_rule_type_code = s.product_rule_type_code
1726 AND w.product_rule_code = s.product_rule_code;
1727
1728 -- Delete the AAD from the working area if it already exists in the
1729 -- staging area
1730 DELETE FROM xla_product_rules_b w
1731 WHERE application_id = g_application_id
1732 AND amb_context_code = g_amb_context_code
1733 AND EXISTS (SELECT 1
1734 FROM xla_product_rules_b s
1735 WHERE s.application_id = g_application_id
1736 AND s.amb_context_code = g_staging_context_code
1737 AND s.product_rule_type_code = w.product_rule_type_code
1738 AND s.product_rule_code = w.product_rule_code);
1739
1740 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1741 trace(p_msg => '# xla_product_rules_b deleted : '||SQL%ROWCOUNT,
1742 p_module => l_log_module,
1743 p_level => C_LEVEL_STATEMENT);
1744 END IF;
1745
1746 DELETE FROM xla_product_rules_tl w
1747 WHERE application_id = g_application_id
1748 AND amb_context_code = g_amb_context_code
1749 AND EXISTS (SELECT 1
1750 FROM xla_product_rules_tl s
1751 WHERE s.application_id = g_application_id
1752 AND s.amb_context_code = g_staging_context_code
1753 AND s.product_rule_type_code = w.product_rule_type_code
1754 AND s.product_rule_code = w.product_rule_code
1755 AND s.language = w.language);
1756
1757 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1758 trace(p_msg => '# xla_product_rules_tl deleted : '||SQL%ROWCOUNT,
1759 p_module => l_log_module,
1760 p_level => C_LEVEL_STATEMENT);
1761 END IF;
1765 AND amb_context_code = g_amb_context_code
1762
1763 DELETE FROM xla_prod_acct_headers w
1764 WHERE application_id = g_application_id
1766 AND EXISTS (SELECT 1
1767 FROM xla_prod_acct_headers s
1768 WHERE s.application_id = g_application_id
1769 AND s.amb_context_code = g_staging_context_code
1770 AND s.product_rule_type_code = w.product_rule_type_code
1771 AND s.product_rule_code = w.product_rule_code
1772 AND s.event_class_code = w.event_class_code
1773 AND s.event_type_code = w.event_type_code);
1774
1775 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1776 trace(p_msg => '# xla_prod_acct_headers deleted : '||SQL%ROWCOUNT,
1777 p_module => l_log_module,
1778 p_level => C_LEVEL_STATEMENT);
1779 END IF;
1780
1781 DELETE FROM xla_aad_line_defn_assgns w
1782 WHERE application_id = g_application_id
1783 AND amb_context_code = g_amb_context_code
1784 AND EXISTS (SELECT 1
1785 FROM xla_aad_line_defn_assgns s
1786 WHERE s.application_id = g_application_id
1787 AND s.amb_context_code = g_staging_context_code
1788 AND s.product_rule_type_code = w.product_rule_type_code
1789 AND s.product_rule_code = w.product_rule_code
1790 AND s.event_class_code = w.event_class_code
1791 AND s.event_type_code = w.event_type_code
1792 AND s.line_definition_owner_code = w.line_definition_owner_code
1793 AND s.line_definition_code = w.line_definition_code);
1794
1795 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1796 trace(p_msg => '# xla_aad_line_defn_assgns deleted : '||SQL%ROWCOUNT,
1797 p_module => l_log_module,
1798 p_level => C_LEVEL_STATEMENT);
1799 END IF;
1800
1801 DELETE FROM xla_aad_hdr_acct_attrs w
1802 WHERE application_id = g_application_id
1803 AND amb_context_code = g_amb_context_code
1804 AND EXISTS (SELECT 1
1805 FROM xla_aad_hdr_acct_attrs s
1806 WHERE s.application_id = g_application_id
1807 AND s.amb_context_code = g_staging_context_code
1808 AND s.product_rule_type_code = w.product_rule_type_code
1809 AND s.product_rule_code = w.product_rule_code
1810 AND s.event_class_code = w.event_class_code
1811 AND s.event_type_code = w.event_type_code
1812 AND s.accounting_attribute_code = w.accounting_attribute_code);
1813
1814 DELETE FROM xla_aad_header_ac_assgns w
1815 WHERE application_id = g_application_id
1816 AND amb_context_code = g_amb_context_code
1817 AND EXISTS (SELECT 1
1818 FROM xla_aad_header_ac_assgns s
1819 WHERE s.application_id = g_application_id
1820 AND s.amb_context_code = g_staging_context_code
1821 AND s.event_class_code = w.event_class_code
1822 AND s.event_type_code = w.event_type_code
1823 AND s.product_rule_type_code = w.product_rule_type_code
1824 AND s.product_rule_code = w.product_rule_code
1825 AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
1826 AND s.analytical_criterion_code = w.analytical_criterion_code);
1827
1828 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1829 trace(p_msg => '# xla_aad_header_ac_assgns deleted : '||SQL%ROWCOUNT,
1830 p_module => l_log_module,
1831 p_level => C_LEVEL_STATEMENT);
1832 END IF;
1833
1834 END IF;
1835
1836 -- Move the AAD from staging area to working area
1837 UPDATE xla_product_rules_b
1838 SET amb_context_code = g_amb_context_code
1839 WHERE application_id = g_application_id
1840 AND amb_context_code = g_staging_context_code;
1841
1842 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1843 trace(p_msg => '# xla_product_rules_b updated : '||SQL%ROWCOUNT,
1844 p_module => l_log_module,
1845 p_level => C_LEVEL_STATEMENT);
1846 END IF;
1847
1848 UPDATE xla_product_rules_tl w
1849 SET amb_context_code = g_amb_context_code
1850 WHERE application_id = g_application_id
1851 AND amb_context_code = g_staging_context_code
1852 AND NOT EXISTS (SELECT 1
1853 FROM xla_product_rules_tl s
1854 WHERE s.application_id = g_application_id
1855 AND s.amb_context_code = g_amb_context_code
1856 AND s.product_rule_type_code = w.product_rule_type_code
1857 AND s.name = w.name
1858 AND s.language = w.language);
1859
1860 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1861 trace(p_msg => '# xla_product_rules_tl 1 updated : '||SQL%ROWCOUNT,
1862 p_module => l_log_module,
1863 p_level => C_LEVEL_STATEMENT);
1864 END IF;
1865
1866 UPDATE xla_product_rules_tl w
1870 AND amb_context_code = g_staging_context_code
1867 SET amb_context_code = g_amb_context_code
1868 , name = substr('('||product_rule_code||') '||name,1,80)
1869 WHERE application_id = g_application_id
1871 AND EXISTS (SELECT 1
1872 FROM xla_product_rules_tl s
1873 WHERE s.application_id = g_application_id
1874 AND s.amb_context_code = g_amb_context_code
1875 AND s.product_rule_type_code = w.product_rule_type_code
1876 AND s.name = w.name
1877 AND s.language = w.language);
1878
1879 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1880 trace(p_msg => '# xla_product_rules_tl 2 updated : '||SQL%ROWCOUNT,
1881 p_module => l_log_module,
1882 p_level => C_LEVEL_STATEMENT);
1883 END IF;
1884
1885 UPDATE xla_prod_acct_headers
1886 SET amb_context_code = g_amb_context_code
1887 WHERE application_id = g_application_id
1888 AND amb_context_code = g_staging_context_code;
1889
1890 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1891 trace(p_msg => '# xla_prod_acct_headers updated : '||SQL%ROWCOUNT,
1892 p_module => l_log_module,
1893 p_level => C_LEVEL_STATEMENT);
1894 END IF;
1895
1896 UPDATE xla_aad_line_defn_assgns
1897 SET amb_context_code = g_amb_context_code
1898 WHERE application_id = g_application_id
1899 AND amb_context_code = g_staging_context_code;
1900
1901 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1902 trace(p_msg => '# xla_aad_line_defn_assgns updated : '||SQL%ROWCOUNT,
1903 p_module => l_log_module,
1904 p_level => C_LEVEL_STATEMENT);
1905 END IF;
1906
1907 UPDATE xla_aad_hdr_acct_attrs
1908 SET amb_context_code = g_amb_context_code
1909 WHERE application_id = g_application_id
1910 AND amb_context_code = g_staging_context_code;
1911
1912 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1913 trace(p_msg => '# xla_aad_hdr_acct_attrs updated : '||SQL%ROWCOUNT,
1914 p_module => l_log_module,
1915 p_level => C_LEVEL_STATEMENT);
1916 END IF;
1917
1918 UPDATE xla_aad_header_ac_assgns
1919 SET amb_context_code = g_amb_context_code
1920 WHERE application_id = g_application_id
1921 AND amb_context_code = g_staging_context_code;
1922
1923 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1924 trace(p_msg => '# xla_aad_header_ac_assgns updated : '||SQL%ROWCOUNT,
1925 p_module => l_log_module,
1926 p_level => C_LEVEL_STATEMENT);
1927 END IF;
1928
1929 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1930 trace(p_msg => 'END of procedure merge_aads',
1931 p_module => l_log_module,
1932 p_level => C_LEVEL_PROCEDURE);
1933 END IF;
1934
1935 EXCEPTION
1936 WHEN OTHERS THEN
1937 xla_aad_loader_util_pvt.stack_error
1938 (p_appli_s_name => 'XLA'
1939 ,p_msg_name => 'XLA_COMMON_ERROR'
1940 ,p_token_1 => 'LOCATION'
1941 ,p_value_1 => 'xla_aad_merge_pvt.merge_aads'
1942 ,p_token_2 => 'ERROR'
1943 ,p_value_2 => 'unhandled exception');
1944 RAISE;
1945
1946 END merge_aads;
1947
1948
1949 --=============================================================================
1950 --
1951 -- Name: merge_journal_line_defns
1952 -- Description: Merge journal line definitions from staging to working area
1953 --
1954 --=============================================================================
1955 PROCEDURE merge_journal_line_defns
1956 IS
1957 l_log_module VARCHAR2(240);
1958 BEGIN
1959 IF g_log_enabled THEN
1960 l_log_module := C_DEFAULT_MODULE||'.merge_journal_line_defns';
1961 END IF;
1962
1963 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1964 trace(p_msg => 'BEGIN of procedure merge_journal_line_defns',
1965 p_module => l_log_module,
1966 p_level => C_LEVEL_PROCEDURE);
1967 END IF;
1968
1969 IF (g_analyzed_flag = 'Y') THEN
1970 null;
1971
1972 ELSE
1973
1974 -- Delete the journal line definitions from the working area if it already
1975 -- exists in the staging area
1976 DELETE FROM xla_line_definitions_b w
1977 WHERE application_id = g_application_id
1978 AND amb_context_code = g_amb_context_code
1979 AND EXISTS (SELECT 1
1980 FROM xla_line_definitions_b s
1981 WHERE s.application_id = g_application_id
1982 AND s.amb_context_code = g_staging_context_code
1983 AND s.event_class_code = w.event_class_code
1984 AND s.event_type_code = w.event_type_code
1985 AND s.line_definition_owner_code = w.line_definition_owner_code
1986 AND s.line_definition_code = w.line_definition_code);
1987
1988 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1989 trace(p_msg => '# xla_line_definitions_b deleted : '||SQL%ROWCOUNT,
1990 p_module => l_log_module,
1991 p_level => C_LEVEL_STATEMENT);
1992 END IF;
1993
1994 DELETE FROM xla_line_definitions_tl w
1998 FROM xla_line_definitions_tl s
1995 WHERE application_id = g_application_id
1996 AND amb_context_code = g_amb_context_code
1997 AND EXISTS (SELECT 1
1999 WHERE s.application_id = g_application_id
2000 AND s.amb_context_code = g_staging_context_code
2001 AND s.event_class_code = w.event_class_code
2002 AND s.event_type_code = w.event_type_code
2003 AND s.line_definition_owner_code = w.line_definition_owner_code
2004 AND s.line_definition_code = w.line_definition_code
2005 AND s.language = w.language);
2006
2007 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2008 trace(p_msg => '# xla_line_definitions_tl deleted : '||SQL%ROWCOUNT,
2009 p_module => l_log_module,
2010 p_level => C_LEVEL_STATEMENT);
2011 END IF;
2012
2013 DELETE FROM xla_line_defn_jlt_assgns w
2014 WHERE application_id = g_application_id
2015 AND amb_context_code = g_amb_context_code
2016 AND EXISTS (SELECT 1
2017 FROM xla_line_defn_jlt_assgns s
2018 WHERE s.application_id = g_application_id
2019 AND s.amb_context_code = g_staging_context_code
2020 AND s.event_class_code = w.event_class_code
2021 AND s.event_type_code = w.event_type_code
2022 AND s.line_definition_owner_code = w.line_definition_owner_code
2023 AND s.line_definition_code = w.line_definition_code
2024 AND s.accounting_line_type_code = w.accounting_line_type_code
2025 AND s.accounting_line_code = w.accounting_line_code);
2026
2027 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2028 trace(p_msg => '# xla_line_defn_jlt_assgns deleted : '||SQL%ROWCOUNT,
2029 p_module => l_log_module,
2030 p_level => C_LEVEL_STATEMENT);
2031 END IF;
2032
2033 -- ADR assignment is not merged, but overwritten, if the JLD exists in the
2034 -- staging area.
2035 DELETE FROM xla_line_defn_adr_assgns w
2036 WHERE application_id = g_application_id
2037 AND amb_context_code = g_amb_context_code
2038 AND EXISTS (SELECT 1
2039 FROM xla_line_defn_jlt_assgns s
2040 WHERE s.application_id = g_application_id
2041 AND s.amb_context_code = g_staging_context_code
2042 AND s.event_class_code = w.event_class_code
2043 AND s.event_type_code = w.event_type_code
2044 AND s.line_definition_owner_code = w.line_definition_owner_code
2045 AND s.line_definition_code = w.line_definition_code
2046 AND s.accounting_line_type_code = w.accounting_line_type_code
2047 AND s.accounting_line_code = w.accounting_line_code);
2048 --AND s.flexfield_segment_code = w.flexfield_segment_code);
2049
2050 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2051 trace(p_msg => '# xla_line_defn_adr_assgns deleted : '||SQL%ROWCOUNT,
2052 p_module => l_log_module,
2053 p_level => C_LEVEL_STATEMENT);
2054 END IF;
2055
2056 DELETE FROM xla_line_defn_ac_assgns w
2057 WHERE application_id = g_application_id
2058 AND amb_context_code = g_amb_context_code
2059 AND EXISTS (SELECT 1
2060 FROM xla_line_defn_ac_assgns s
2061 WHERE s.application_id = g_application_id
2062 AND s.amb_context_code = g_staging_context_code
2063 AND s.event_class_code = w.event_class_code
2064 AND s.event_type_code = w.event_type_code
2065 AND s.line_definition_owner_code = w.line_definition_owner_code
2066 AND s.line_definition_code = w.line_definition_code
2067 AND s.accounting_line_type_code = w.accounting_line_type_code
2068 AND s.accounting_line_code = w.accounting_line_code
2069 AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
2070 AND s.analytical_criterion_code = w.analytical_criterion_code);
2071
2072 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2073 trace(p_msg => '# xla_line_defn_ac_assgns deleted : '||SQL%ROWCOUNT,
2074 p_module => l_log_module,
2075 p_level => C_LEVEL_STATEMENT);
2076 END IF;
2077
2078 DELETE FROM xla_mpa_jlt_assgns w
2079 WHERE application_id = g_application_id
2080 AND amb_context_code = g_amb_context_code
2081 AND EXISTS (
2082 SELECT 1
2083 FROM xla_acct_line_types_b xal
2084 WHERE xal.application_id = w.application_id
2085 AND xal.amb_context_code = w.amb_context_code
2086 AND xal.event_class_code = w.event_class_code
2087 AND xal.accounting_line_type_code = w.accounting_line_type_code
2088 AND xal.accounting_line_code = w.accounting_line_code
2089 AND xal.mpa_option_code = 'ACCRUAL');
2090
2094 p_level => C_LEVEL_STATEMENT);
2091 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2092 trace(p_msg => '# xla_mpa_jlt_assgns deleted : '||SQL%ROWCOUNT,
2093 p_module => l_log_module,
2095 END IF;
2096
2097 DELETE FROM xla_mpa_header_ac_assgns w
2098 WHERE application_id = g_application_id
2099 AND amb_context_code = g_amb_context_code
2100 AND EXISTS (
2101 SELECT 1
2102 FROM xla_mpa_header_ac_assgns s
2103 WHERE s.application_id = g_application_id
2104 AND s.amb_context_code = g_staging_context_code
2105 AND s.event_class_code = w.event_class_code
2106 AND s.event_type_code = w.event_type_code
2107 AND s.line_definition_owner_code = w.line_definition_owner_code
2108 AND s.line_definition_code = w.line_definition_code
2109 AND s.accounting_line_type_code = w.accounting_line_type_code
2110 AND s.accounting_line_code = w.accounting_line_code
2111 AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
2112 AND s.analytical_criterion_code = w.analytical_criterion_code
2113 UNION
2114 SELECT 1
2115 FROM xla_acct_line_types_b s
2116 WHERE s.application_id = g_application_id
2117 AND s.amb_context_code = g_staging_context_code
2118 AND s.event_class_code = w.event_class_code
2119 AND s.accounting_line_type_code = w.accounting_line_type_code
2120 AND s.accounting_line_code = w.accounting_line_code
2121 AND s.mpa_option_code = 'NONE');
2122
2123 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2124 trace(p_msg => '# xla_mpa_header_ac_assgns deleted : '||SQL%ROWCOUNT,
2125 p_module => l_log_module,
2126 p_level => C_LEVEL_STATEMENT);
2127 END IF;
2128
2129 -- ADR assignments are not merged, but overwritten, if the MPA JLT exists in
2130 -- the staging area
2131 DELETE FROM xla_mpa_jlt_adr_assgns w
2132 WHERE application_id = g_application_id
2133 AND amb_context_code = g_amb_context_code
2134 AND EXISTS (
2135 SELECT 1
2136 FROM xla_mpa_jlt_assgns s
2137 WHERE s.application_id = g_application_id
2138 AND s.amb_context_code = g_staging_context_code
2139 AND s.event_class_code = w.event_class_code
2140 AND s.event_type_code = w.event_type_code
2141 AND s.line_definition_owner_code = w.line_definition_owner_code
2142 AND s.line_definition_code = w.line_definition_code
2143 AND s.accounting_line_type_code = w.accounting_line_type_code
2144 AND s.accounting_line_code = w.accounting_line_code
2145 AND s.mpa_accounting_line_type_code = w.mpa_accounting_line_type_code
2146 AND s.mpa_accounting_line_code = w.mpa_accounting_line_code
2147 UNION
2148 SELECT 1
2149 FROM xla_acct_line_types_b s
2150 WHERE s.application_id = g_application_id
2151 AND s.amb_context_code = g_staging_context_code
2152 AND s.event_class_code = w.event_class_code
2153 AND s.accounting_line_type_code = w.accounting_line_type_code
2154 AND s.accounting_line_code = w.accounting_line_code
2155 AND s.mpa_option_code = 'NONE');
2156
2157 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2158 trace(p_msg => '# xla_mpa_jlt_adr_assgns deleted : '||SQL%ROWCOUNT,
2159 p_module => l_log_module,
2160 p_level => C_LEVEL_STATEMENT);
2161 END IF;
2162
2163 DELETE FROM xla_mpa_jlt_ac_assgns w
2164 WHERE application_id = g_application_id
2165 AND amb_context_code = g_amb_context_code
2166 AND EXISTS (
2167 SELECT 1
2168 FROM xla_mpa_jlt_ac_assgns s
2169 WHERE s.application_id = g_application_id
2170 AND s.amb_context_code = g_staging_context_code
2171 AND s.event_class_code = w.event_class_code
2172 AND s.event_type_code = w.event_type_code
2173 AND s.line_definition_owner_code = w.line_definition_owner_code
2174 AND s.line_definition_code = w.line_definition_code
2175 AND s.accounting_line_type_code = w.accounting_line_type_code
2176 AND s.accounting_line_code = w.accounting_line_code
2177 AND s.mpa_accounting_line_type_code = w.mpa_accounting_line_type_code
2178 AND s.mpa_accounting_line_code = w.mpa_accounting_line_code
2179 AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
2180 AND s.analytical_criterion_code = w.analytical_criterion_code
2181 UNION
2182 SELECT 1
2183 FROM xla_acct_line_types_b s
2184 WHERE s.application_id = g_application_id
2185 AND s.amb_context_code = g_staging_context_code
2186 AND s.event_class_code = w.event_class_code
2187 AND s.accounting_line_type_code = w.accounting_line_type_code
2188 AND s.accounting_line_code = w.accounting_line_code
2192 trace(p_msg => '# xla_mpa_jlt_ac_assgns deleted : '||SQL%ROWCOUNT,
2189 AND s.mpa_option_code = 'NONE');
2190
2191 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2193 p_module => l_log_module,
2194 p_level => C_LEVEL_STATEMENT);
2195 END IF;
2196
2197 END IF;
2198
2199 -- Move the journal line definitions from staging area to working area
2200 UPDATE xla_line_definitions_b
2201 SET amb_context_code = g_amb_context_code
2202 WHERE application_id = g_application_id
2203 AND amb_context_code = g_staging_context_code;
2204
2205 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2206 trace(p_msg => '# xla_line_definitions_b updated : '||SQL%ROWCOUNT,
2207 p_module => l_log_module,
2208 p_level => C_LEVEL_STATEMENT);
2209 END IF;
2210
2211 UPDATE xla_line_definitions_tl w
2212 SET amb_context_code = g_amb_context_code
2213 WHERE application_id = g_application_id
2214 AND amb_context_code = g_staging_context_code
2215 AND NOT EXISTS (SELECT 1
2216 FROM xla_line_definitions_tl s
2217 WHERE s.application_id = g_application_id
2218 AND s.amb_context_code = g_amb_context_code
2219 AND s.event_class_code = w.event_class_code
2220 AND s.event_type_code = w.event_type_code
2221 AND s.line_definition_owner_code = w.line_definition_owner_code
2222 AND s.name = w.name
2223 AND s.language = w.language);
2224
2225 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2226 trace(p_msg => '# xla_line_definitions_tl 1 updated : '||SQL%ROWCOUNT,
2227 p_module => l_log_module,
2228 p_level => C_LEVEL_STATEMENT);
2229 END IF;
2230
2231 UPDATE xla_line_definitions_tl w
2232 SET amb_context_code = g_amb_context_code
2233 , name = substr('('||line_definition_code||') '||name,1,80)
2234 WHERE application_id = g_application_id
2235 AND amb_context_code = g_staging_context_code
2236 AND EXISTS (SELECT 1
2237 FROM xla_line_definitions_tl s
2238 WHERE s.application_id = g_application_id
2239 AND s.amb_context_code = g_amb_context_code
2240 AND s.event_class_code = w.event_class_code
2241 AND s.event_type_code = w.event_type_code
2242 AND s.line_definition_owner_code = w.line_definition_owner_code
2243 AND s.name = w.name
2244 AND s.language = w.language);
2245
2246 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2247 trace(p_msg => '# xla_line_definitions_tl 2 updated : '||SQL%ROWCOUNT,
2248 p_module => l_log_module,
2249 p_level => C_LEVEL_STATEMENT);
2250 END IF;
2251
2252 UPDATE xla_line_defn_jlt_assgns
2253 SET amb_context_code = g_amb_context_code
2254 WHERE application_id = g_application_id
2255 AND amb_context_code = g_staging_context_code;
2256
2257 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2258 trace(p_msg => '# xla_line_defn_jlt_assgns updated : '||SQL%ROWCOUNT,
2259 p_module => l_log_module,
2260 p_level => C_LEVEL_STATEMENT);
2261 END IF;
2262
2263 UPDATE xla_line_defn_adr_assgns
2264 SET amb_context_code = g_amb_context_code
2265 WHERE application_id = g_application_id
2266 AND amb_context_code = g_staging_context_code;
2267
2268 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2269 trace(p_msg => '# xla_line_defn_adr_assgns updated : '||SQL%ROWCOUNT,
2270 p_module => l_log_module,
2271 p_level => C_LEVEL_STATEMENT);
2272 END IF;
2273
2274 UPDATE xla_line_defn_ac_assgns
2275 SET amb_context_code = g_amb_context_code
2276 WHERE application_id = g_application_id
2277 AND amb_context_code = g_staging_context_code;
2278
2279 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2280 trace(p_msg => '# xla_line_defn_ac_assgns updated : '||SQL%ROWCOUNT,
2281 p_module => l_log_module,
2282 p_level => C_LEVEL_STATEMENT);
2283 END IF;
2284
2285 UPDATE xla_mpa_jlt_assgns
2286 SET amb_context_code = g_amb_context_code
2287 WHERE application_id = g_application_id
2288 AND amb_context_code = g_staging_context_code;
2289
2290 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2291 trace(p_msg => '# xla_mpa_jlt_assgns updated : '||SQL%ROWCOUNT,
2292 p_module => l_log_module,
2293 p_level => C_LEVEL_STATEMENT);
2294 END IF;
2295
2296 UPDATE xla_mpa_header_ac_assgns
2297 SET amb_context_code = g_amb_context_code
2298 WHERE application_id = g_application_id
2299 AND amb_context_code = g_staging_context_code;
2300
2301 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2302 trace(p_msg => '# xla_mpa_header_ac_assgns updated : '||SQL%ROWCOUNT,
2303 p_module => l_log_module,
2304 p_level => C_LEVEL_STATEMENT);
2305 END IF;
2306
2307 UPDATE xla_mpa_jlt_adr_assgns
2308 SET amb_context_code = g_amb_context_code
2309 WHERE application_id = g_application_id
2310 AND amb_context_code = g_staging_context_code;
2311
2315 p_level => C_LEVEL_STATEMENT);
2312 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2313 trace(p_msg => '# xla_mpa_jlt_adr_assgns updated : '||SQL%ROWCOUNT,
2314 p_module => l_log_module,
2316 END IF;
2317
2318 -- For AC that is not going to be overwritten by those in staging area, it
2319 -- must not exist in the staging area. Therefore, it must not be inherited.
2320 UPDATE xla_mpa_jlt_ac_assgns
2321 SET mpa_inherit_ac_flag = 'N'
2322 WHERE application_id = g_application_id
2323 AND amb_context_code = g_amb_context_code;
2324
2325 UPDATE xla_mpa_jlt_ac_assgns
2326 SET amb_context_code = g_amb_context_code
2327 WHERE application_id = g_application_id
2328 AND amb_context_code = g_staging_context_code;
2329
2330 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2331 trace(p_msg => '# xla_mpa_jlt_ac_assgns updated : '||SQL%ROWCOUNT,
2332 p_module => l_log_module,
2333 p_level => C_LEVEL_STATEMENT);
2334 END IF;
2335
2336 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2337 trace(p_msg => 'END of procedure merge_journal_line_defns',
2338 p_module => l_log_module,
2339 p_level => C_LEVEL_PROCEDURE);
2340 END IF;
2341
2342 EXCEPTION
2343 WHEN OTHERS THEN
2344 xla_aad_loader_util_pvt.stack_error
2345 (p_appli_s_name => 'XLA'
2346 ,p_msg_name => 'XLA_COMMON_ERROR'
2347 ,p_token_1 => 'LOCATION'
2348 ,p_value_1 => 'xla_aad_merge_pvt.merge_journal_line_defns'
2349 ,p_token_2 => 'ERROR'
2350 ,p_value_2 => 'unhandled exception');
2351 RAISE;
2352
2353 END merge_journal_line_defns;
2354
2355
2356 --=============================================================================
2357 --
2358 -- Name: merge_journal_line_types
2359 -- Description: Merge journal line types from staging to working area
2360 --
2361 --=============================================================================
2362 PROCEDURE merge_journal_line_types
2363 IS
2364
2365 l_log_module VARCHAR2(240);
2366 BEGIN
2367 IF g_log_enabled THEN
2368 l_log_module := C_DEFAULT_MODULE||'.merge_journal_line_types';
2369 END IF;
2370
2371 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2372 trace(p_msg => 'BEGIN of procedure merge_journal_line_types',
2373 p_module => l_log_module,
2374 p_level => C_LEVEL_PROCEDURE);
2375 END IF;
2376
2377 IF (g_analyzed_flag = 'Y') THEN
2378 null;
2379
2380 ELSE
2381
2382 -- Delete the journal line types from the working area if it already
2383 -- exists in the staging area
2384 DELETE FROM xla_acct_line_types_b w
2385 WHERE application_id = g_application_id
2386 AND amb_context_code = g_amb_context_code
2387 AND EXISTS (SELECT 1
2388 FROM xla_acct_line_types_b s
2389 WHERE s.application_id = g_application_id
2390 AND s.amb_context_code = g_staging_context_code
2391 AND s.event_class_code = w.event_class_code
2392 AND s.accounting_line_type_code = w.accounting_line_type_code
2393 AND s.accounting_line_code = w.accounting_line_code);
2394
2395 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2396 trace(p_msg => '# xla_acct_line_types_b delete : '||SQL%ROWCOUNT,
2397 p_module => l_log_module,
2398 p_level => C_LEVEL_STATEMENT);
2399 END IF;
2400
2401 DELETE FROM xla_acct_line_types_tl w
2402 WHERE application_id = g_application_id
2403 AND amb_context_code = g_amb_context_code
2404 AND EXISTS (SELECT 1
2405 FROM xla_acct_line_types_tl s
2406 WHERE s.application_id = g_application_id
2407 AND s.amb_context_code = g_staging_context_code
2408 AND s.event_class_code = w.event_class_code
2409 AND s.accounting_line_type_code = w.accounting_line_type_code
2410 AND s.accounting_line_code = w.accounting_line_code
2411 AND s.language = w.language);
2412
2413 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2414 trace(p_msg => '# xla_acct_line_types_tl delete : '||SQL%ROWCOUNT,
2415 p_module => l_log_module,
2416 p_level => C_LEVEL_STATEMENT);
2417 END IF;
2418
2419 DELETE FROM xla_jlt_acct_attrs w
2420 WHERE application_id = g_application_id
2421 AND amb_context_code = g_amb_context_code
2422 AND EXISTS (SELECT 1
2423 FROM xla_jlt_acct_attrs s
2424 WHERE s.application_id = g_application_id
2425 AND s.amb_context_code = g_staging_context_code
2426 AND s.event_class_code = w.event_class_code
2427 AND s.accounting_line_type_code = w.accounting_line_type_code
2428 AND s.accounting_line_code = w.accounting_line_code);
2429
2430 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2431 trace(p_msg => '# xla_jlt_acct_attrs delete : '||SQL%ROWCOUNT,
2432 p_module => l_log_module,
2433 p_level => C_LEVEL_STATEMENT);
2434 END IF;
2435
2439 AND EXISTS (SELECT 1
2436 DELETE FROM xla_conditions w
2437 WHERE application_id = g_application_id
2438 AND amb_context_code = g_amb_context_code
2440 FROM xla_acct_line_types_b s
2441 WHERE s.application_id = g_application_id
2442 AND s.amb_context_code = g_staging_context_code
2443 AND s.event_class_code = w.event_class_code
2444 AND s.accounting_line_type_code = w.accounting_line_type_code
2445 AND s.accounting_line_code = w.accounting_line_code);
2446
2447 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2448 trace(p_msg => '# xla_jlt_acct_attrs delete : '||SQL%ROWCOUNT,
2449 p_module => l_log_module,
2450 p_level => C_LEVEL_STATEMENT);
2451 END IF;
2452
2453 END IF;
2454
2455 -- Move the journal line types from staging area to working area
2456 UPDATE xla_acct_line_types_b
2457 SET amb_context_code = g_amb_context_code
2458 WHERE application_id = g_application_id
2459 AND amb_context_code = g_staging_context_code;
2460
2461 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2462 trace(p_msg => '# xla_acct_line_types_b updated : '||SQL%ROWCOUNT,
2463 p_module => l_log_module,
2464 p_level => C_LEVEL_STATEMENT);
2465 END IF;
2466
2467 UPDATE xla_acct_line_types_tl w
2468 SET amb_context_code = g_amb_context_code
2469 WHERE application_id = g_application_id
2470 AND amb_context_code = g_staging_context_code
2471 AND NOT EXISTS (SELECT 1
2472 FROM xla_acct_line_types_tl s
2473 WHERE s.application_id = g_application_id
2474 AND s.amb_context_code = g_amb_context_code
2475 AND s.event_class_code = w.event_class_code
2476 AND s.accounting_line_type_code = w.accounting_line_type_code
2477 AND s.name = w.name
2478 AND s.language = w.language);
2479
2480 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2481 trace(p_msg => '# xla_acct_line_types_tl 1 updated : '||SQL%ROWCOUNT,
2482 p_module => l_log_module,
2483 p_level => C_LEVEL_STATEMENT);
2484 END IF;
2485
2486 UPDATE xla_acct_line_types_tl w
2487 SET amb_context_code = g_amb_context_code
2488 , name = substr('('||w.accounting_line_code||') '||name,1,80)
2489 WHERE application_id = g_application_id
2490 AND amb_context_code = g_staging_context_code
2491 AND EXISTS (SELECT 1
2492 FROM xla_acct_line_types_tl s
2493 WHERE s.application_id = g_application_id
2494 AND s.amb_context_code = g_amb_context_code
2495 AND s.event_class_code = w.event_class_code
2496 AND s.accounting_line_type_code = w.accounting_line_type_code
2497 AND s.name = w.name
2498 AND s.language = w.language);
2499
2500 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2501 trace(p_msg => '# xla_acct_line_types_tl 2 updated : '||SQL%ROWCOUNT,
2502 p_module => l_log_module,
2503 p_level => C_LEVEL_STATEMENT);
2504 END IF;
2505
2506 UPDATE xla_jlt_acct_attrs
2507 SET amb_context_code = g_amb_context_code
2508 WHERE application_id = g_application_id
2509 AND amb_context_code = g_staging_context_code;
2510
2511 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2512 trace(p_msg => '# xla_jlt_acct_attrs updated : '||SQL%ROWCOUNT,
2513 p_module => l_log_module,
2514 p_level => C_LEVEL_STATEMENT);
2515 END IF;
2516
2517 UPDATE xla_conditions
2518 SET amb_context_code = g_amb_context_code
2519 WHERE amb_context_code = g_staging_context_code
2520 AND application_id = g_application_id
2521 AND accounting_line_code IS NOT NULL;
2522
2523 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2524 trace(p_msg => '# xla_conditions updated : '||SQL%ROWCOUNT,
2525 p_module => l_log_module,
2526 p_level => C_LEVEL_STATEMENT);
2527 END IF;
2528
2529 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2530 trace(p_msg => 'END of procedure merge_journal_line_types',
2531 p_module => l_log_module,
2532 p_level => C_LEVEL_PROCEDURE);
2533 END IF;
2534
2535 EXCEPTION
2536 WHEN OTHERS THEN
2537 xla_aad_loader_util_pvt.stack_error
2538 (p_appli_s_name => 'XLA'
2539 ,p_msg_name => 'XLA_COMMON_ERROR'
2540 ,p_token_1 => 'LOCATION'
2541 ,p_value_1 => 'xla_aad_merge_pvt.merge_journal_line_types'
2542 ,p_token_2 => 'ERROR'
2543 ,p_value_2 => 'unhandled exception');
2544 RAISE;
2545
2546 END merge_journal_line_types;
2547
2548
2549 --=============================================================================
2550 --
2551 -- Name: merge_descriptions
2552 -- Description: Merge descriptions from staging to working area
2553 --
2554 --=============================================================================
2555 PROCEDURE merge_descriptions
2556 IS
2557 l_log_module VARCHAR2(240);
2558 BEGIN
2562
2559 IF g_log_enabled THEN
2560 l_log_module := C_DEFAULT_MODULE||'.merge_descriptions';
2561 END IF;
2563 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2564 trace(p_msg => 'BEGIN of procedure merge_descriptions',
2565 p_module => l_log_module,
2566 p_level => C_LEVEL_PROCEDURE);
2567 END IF;
2568
2569 IF (g_analyzed_flag = 'Y') THEN
2570 null;
2571
2572 ELSE
2573
2574 -- Delete the descriptions from the working area if it already
2575 -- exists in the staging area
2576 DELETE FROM xla_descriptions_b w
2577 WHERE application_id = g_application_id
2578 AND amb_context_code = g_amb_context_code
2579 AND EXISTS (SELECT 1
2580 FROM xla_descriptions_b s
2581 WHERE s.application_id = g_application_id
2582 AND s.amb_context_code = g_staging_context_code
2583 AND s.description_type_code = w.description_type_code
2584 AND s.description_code = w.description_code);
2585
2586 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2587 trace(p_msg => '# xla_descriptions_b delete : '||SQL%ROWCOUNT,
2588 p_module => l_log_module,
2589 p_level => C_LEVEL_STATEMENT);
2590 END IF;
2591
2592 DELETE FROM xla_descriptions_tl w
2593 WHERE application_id = g_application_id
2594 AND amb_context_code = g_amb_context_code
2595 AND EXISTS (SELECT 1
2596 FROM xla_descriptions_tl s
2597 WHERE s.application_id = g_application_id
2598 AND s.amb_context_code = g_staging_context_code
2599 AND s.description_type_code = w.description_type_code
2600 AND s.description_code = w.description_code
2601 AND s.language = w.language);
2602
2603 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2604 trace(p_msg => '# xla_descriptions_tl delete : '||SQL%ROWCOUNT,
2605 p_module => l_log_module,
2606 p_level => C_LEVEL_STATEMENT);
2607 END IF;
2608
2609 DELETE FROM xla_desc_priorities w
2610 WHERE application_id = g_application_id
2611 AND amb_context_code = g_amb_context_code
2612 AND EXISTS (SELECT 1
2613 FROM xla_desc_priorities s
2614 WHERE s.application_id = g_application_id
2615 AND s.amb_context_code = g_staging_context_code
2616 AND s.description_type_code = w.description_type_code
2617 AND s.description_code = w.description_code);
2618
2619 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2620 trace(p_msg => '# xla_desc_priorities delete : '||SQL%ROWCOUNT,
2621 p_module => l_log_module,
2622 p_level => C_LEVEL_STATEMENT);
2623 END IF;
2624
2625 DELETE FROM xla_conditions
2626 WHERE description_prio_id IN
2627 (SELECT w.description_prio_id
2628 FROM xla_desc_priorities w
2629 , xla_desc_priorities s
2630 WHERE s.application_id = g_application_id
2631 AND s.amb_context_code = g_staging_context_code
2632 AND w.application_id = g_application_id
2633 AND w.amb_context_code = g_amb_context_code
2634 AND w.description_type_code = s.description_type_code
2635 AND w.description_code = s.description_code);
2636
2637 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2638 trace(p_msg => '# xla_conditions delete : '||SQL%ROWCOUNT,
2639 p_module => l_log_module,
2640 p_level => C_LEVEL_STATEMENT);
2641 END IF;
2642
2643 DELETE FROM xla_descript_details_b
2644 WHERE description_prio_id IN
2645 (SELECT w.description_prio_id
2646 FROM xla_desc_priorities w
2647 , xla_desc_priorities s
2648 WHERE s.application_id = g_application_id
2649 AND s.amb_context_code = g_staging_context_code
2650 AND w.application_id = g_application_id
2651 AND w.amb_context_code = g_amb_context_code
2652 AND w.description_type_code = s.description_type_code
2653 AND w.description_code = s.description_code);
2654
2655 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2656 trace(p_msg => '# xla_descript_details_b delete : '||SQL%ROWCOUNT,
2657 p_module => l_log_module,
2658 p_level => C_LEVEL_STATEMENT);
2659 END IF;
2660
2661 DELETE FROM xla_descript_details_tl w
2662 WHERE description_detail_id IN
2663 (SELECT description_detail_id
2664 FROM xla_descript_details_b d
2665 , xla_desc_priorities w
2666 , xla_desc_priorities s
2667 WHERE d.description_prio_id = w.description_prio_id
2668 AND s.application_id = g_application_id
2669 AND s.amb_context_code = g_staging_context_code
2670 AND s.application_id = g_application_id
2671 AND s.amb_context_code = g_amb_context_code
2672 AND w.description_type_code = s.description_type_code
2676 trace(p_msg => '# xla_descript_details_tl delete : '||SQL%ROWCOUNT,
2673 AND w.description_code = s.description_code);
2674
2675 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2677 p_module => l_log_module,
2678 p_level => C_LEVEL_STATEMENT);
2679 END IF;
2680
2681 END IF;
2682
2683 -- Move the descriptions from staging area to working area
2684 UPDATE xla_descriptions_b
2685 SET amb_context_code = g_amb_context_code
2686 WHERE application_id = g_application_id
2687 AND amb_context_code = g_staging_context_code;
2688
2689 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2690 trace(p_msg => '# xla_descriptions_b updated : '||SQL%ROWCOUNT,
2691 p_module => l_log_module,
2692 p_level => C_LEVEL_STATEMENT);
2693 END IF;
2694
2695 UPDATE xla_descriptions_tl w
2696 SET amb_context_code = g_amb_context_code
2697 WHERE application_id = g_application_id
2698 AND amb_context_code = g_staging_context_code
2699 AND NOT EXISTS (SELECT 1
2700 FROM xla_descriptions_tl s
2701 WHERE s.application_id = g_application_id
2702 AND s.amb_context_code = g_amb_context_code
2703 AND s.description_type_code = w.description_type_code
2704 AND s.name = w.name
2705 AND s.language = w.language);
2706
2707 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2708 trace(p_msg => '# xla_descriptions_tl 1 updated : '||SQL%ROWCOUNT,
2709 p_module => l_log_module,
2710 p_level => C_LEVEL_STATEMENT);
2711 END IF;
2712
2713 UPDATE xla_descriptions_tl w
2714 SET amb_context_code = g_amb_context_code
2715 , name = substr('('||w.description_code||') '||name,1,80)
2716 WHERE application_id = g_application_id
2717 AND amb_context_code = g_staging_context_code
2718 AND EXISTS (SELECT 1
2719 FROM xla_descriptions_tl s
2720 WHERE s.application_id = g_application_id
2721 AND s.amb_context_code = g_amb_context_code
2722 AND s.description_type_code = w.description_type_code
2723 AND s.name = w.name
2724 AND s.language = w.language);
2725
2726
2727 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2728 trace(p_msg => '# xla_descriptions_tl 2 updated : '||SQL%ROWCOUNT,
2729 p_module => l_log_module,
2730 p_level => C_LEVEL_STATEMENT);
2731 END IF;
2732
2733 UPDATE xla_desc_priorities
2734 SET amb_context_code = g_amb_context_code
2735 WHERE application_id = g_application_id
2736 AND amb_context_code = g_staging_context_code;
2737
2738 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2739 trace(p_msg => '# xla_desc_priorities updated : '||SQL%ROWCOUNT,
2740 p_module => l_log_module,
2741 p_level => C_LEVEL_STATEMENT);
2742 END IF;
2743
2744 UPDATE xla_conditions
2745 SET amb_context_code = g_amb_context_code
2746 WHERE amb_context_code = g_staging_context_code
2747 AND application_id = g_application_id
2748 AND description_prio_id IS NOT NULL;
2749
2750 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2751 trace(p_msg => '# xla_conditions updated : '||SQL%ROWCOUNT,
2752 p_module => l_log_module,
2753 p_level => C_LEVEL_STATEMENT);
2754 END IF;
2755
2756 UPDATE xla_descript_details_b
2757 SET amb_context_code = g_amb_context_code
2758 WHERE amb_context_code = g_staging_context_code;
2759
2760 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2761 trace(p_msg => '# xla_descript_details_b updated : '||SQL%ROWCOUNT,
2762 p_module => l_log_module,
2763 p_level => C_LEVEL_STATEMENT);
2764 END IF;
2765
2766 UPDATE xla_descript_details_tl
2767 SET amb_context_code = g_amb_context_code
2768 WHERE amb_context_code = g_staging_context_code;
2769
2770 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2771 trace(p_msg => '# xla_descript_details_tl updated : '||SQL%ROWCOUNT,
2772 p_module => l_log_module,
2773 p_level => C_LEVEL_STATEMENT);
2774 END IF;
2775
2776 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2777 trace(p_msg => 'END of procedure merge_descriptions',
2778 p_module => l_log_module,
2779 p_level => C_LEVEL_PROCEDURE);
2780 END IF;
2781
2782 EXCEPTION
2783 WHEN OTHERS THEN
2784 xla_aad_loader_util_pvt.stack_error
2785 (p_appli_s_name => 'XLA'
2786 ,p_msg_name => 'XLA_COMMON_ERROR'
2787 ,p_token_1 => 'LOCATION'
2788 ,p_value_1 => 'xla_aad_merge_pvt.merge_descriptions'
2789 ,p_token_2 => 'ERROR'
2790 ,p_value_2 => 'unhandled exception');
2791 RAISE;
2792
2793 END merge_descriptions;
2794
2795
2796 --=============================================================================
2797 --
2798 -- Name: merge_analytical_criteria
2799 -- Description: Merge analytical criteria from staging to working area
2800 -- Changes:
2801 -- 8230704 and 7692291 Simran: overriding the changes done as part of bug 7243326
2805 -- Retrieve the AC to be merged
2802 --=============================================================================
2803 PROCEDURE merge_analytical_criteria
2804 IS
2806 CURSOR c_ac IS
2807 SELECT s.analytical_criterion_type_code, s.analytical_criterion_code
2808 FROM xla_analytical_hdrs_b s
2809 , xla_analytical_hdrs_b w
2810 WHERE s.amb_context_code = g_staging_context_code
2811 AND w.amb_context_code(+) = g_amb_context_code
2812 AND s.analytical_criterion_type_code = w.analytical_criterion_type_code(+)
2813 AND s.analytical_criterion_code = w.analytical_criterion_code(+)
2814 AND s.version_num >= w.version_num(+);
2815
2816 -- Added cursor for DTLs as part of bug 7243326 - This is mainly for AR where AR does not have a HDR and following DELETES and UPDATES dont loop
2817
2818 CURSOR c_ac_dtls IS
2819 SELECT s.analytical_criterion_type_code, s.analytical_criterion_code,s.ANALYTICAL_DETAIL_CODE
2820 FROM xla_analytical_dtls_b s
2821 , xla_analytical_dtls_b w
2822 WHERE s.amb_context_code = g_staging_context_code
2823 AND w.amb_context_code(+) = g_amb_context_code
2824 AND s.analytical_criterion_type_code = w.analytical_criterion_type_code(+)
2825 AND s.analytical_criterion_code = w.analytical_criterion_code(+)
2826 AND s.ANALYTICAL_DETAIL_CODE = w.ANALYTICAL_DETAIL_CODE(+) ;
2827
2828 CURSOR c_ac_src IS
2829 SELECT s.analytical_criterion_type_code, s.analytical_criterion_code,s.ANALYTICAL_DETAIL_CODE,
2830 s.event_class_code,s.entity_code,s.source_code,s.source_type_code
2831 FROM xla_analytical_sources s
2832 , xla_analytical_sources w
2833 WHERE s.amb_context_code = g_staging_context_code
2834 AND w.amb_context_code(+) = g_amb_context_code
2835 AND s.analytical_criterion_type_code = w.analytical_criterion_type_code(+)
2836 AND s.analytical_criterion_code = w.analytical_criterion_code(+)
2837 AND s.ANALYTICAL_DETAIL_CODE = w.ANALYTICAL_DETAIL_CODE(+)
2838 AND s.event_class_code = w.event_class_code(+)
2839 AND s.entity_code = w.entity_code(+)
2840 AND s.source_code = w.source_code(+)
2841 AND s.source_type_code = w.source_type_code(+);
2842
2843
2844 l_ac_detail_code t_array_varchar30;
2845 l_ac_dtl_type_codes t_array_varchar30;
2846 l_ac_dtl_codes t_array_varchar30;
2847 l_ac_src_event_class t_array_varchar30;
2848 l_ac_src_entity_code t_array_varchar30;
2849 l_ac_src_source_code t_array_varchar30;
2850 l_ac_src_source_type_code t_array_varchar30;
2851
2852 l_ac_type_codes t_array_varchar30;
2853 l_ac_codes t_array_varchar30;
2854 l_log_module VARCHAR2(240);
2855 l_num_rows NUMBER;
2856 BEGIN
2857 IF g_log_enabled THEN
2858 l_log_module := C_DEFAULT_MODULE||'.merge_analytical_criteria';
2859 END IF;
2860
2861 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2862 trace(p_msg => 'BEGIN of procedure merge_analytical_criteria',
2863 p_module => l_log_module,
2864 p_level => C_LEVEL_PROCEDURE);
2865 END IF;
2866
2867 IF (g_analyzed_flag = 'Y') THEN
2868 null;
2869
2870 ELSE
2871
2872 /* 8230704 and 7692291: all the 3 tables are independant of each other. AR ldt wouldnt load any header as all headers
2873 r right now with 200 application_id. But AR ldt would load details and would load all sources.
2874 Need to keep sources separate, as the delete should remove only those sources that r in the ldt NOT sources that
2875 belong to a header/dtl loaded.
2876 */
2877
2878 OPEN c_ac;
2879 FETCH c_ac BULK COLLECT INTO l_ac_type_codes, l_ac_codes;
2880 CLOSE c_ac;
2881
2882 OPEN c_ac_dtls ;
2883 FETCH c_ac_dtls BULK COLLECT INTO l_ac_dtl_type_codes, l_ac_dtl_codes, l_ac_detail_code;
2884 CLOSE c_ac_dtls ;
2885
2886 OPEN c_ac_src ;
2887 FETCH c_ac_src BULK COLLECT INTO l_ac_dtl_type_codes, l_ac_dtl_codes,
2888 l_ac_detail_code, l_ac_src_event_class,l_ac_src_entity_code,
2889 l_ac_src_source_code,l_ac_src_source_type_code;
2890 CLOSE c_ac_src ;
2891
2892 -- Delete the ACs from the working area for the AC to be merged
2893 FORALL i IN 1..l_ac_codes.COUNT
2894 DELETE FROM xla_analytical_hdrs_b w
2895 WHERE amb_context_code = g_amb_context_code
2896 AND analytical_criterion_type_code = l_ac_type_codes(i)
2897 AND analytical_criterion_code = l_ac_codes(i)
2898 AND EXISTS
2899 ( SELECT 1
2900 FROM xla_analytical_hdrs_b s
2901 WHERE s.amb_context_code = g_staging_context_code
2902 AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
2903 AND s.analytical_criterion_code = w.analytical_criterion_code );
2904
2905 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2906 trace(p_msg => '# xla_analytical_hdrs_b delete : '||SQL%ROWCOUNT,
2907 p_module => l_log_module,
2908 p_level => C_LEVEL_STATEMENT);
2909 END IF;
2910
2911 FORALL i IN 1..l_ac_codes.COUNT
2912 DELETE FROM xla_analytical_hdrs_tl w
2913 WHERE amb_context_code = g_amb_context_code
2914 AND analytical_criterion_type_code = l_ac_type_codes(i)
2918 FROM xla_analytical_hdrs_tl s
2915 AND analytical_criterion_code = l_ac_codes(i)
2916 AND EXISTS
2917 ( SELECT 1
2919 WHERE s.amb_context_code = g_staging_context_code
2920 AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
2921 AND s.analytical_criterion_code = w.analytical_criterion_code
2922 AND s.language = w.language);
2923
2924 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2925 trace(p_msg => '# xla_analytical_hdrs_tl delete : '||SQL%ROWCOUNT,
2926 p_module => l_log_module,
2927 p_level => C_LEVEL_STATEMENT);
2928 END IF;
2929
2930 -- Delete the AC dtlss from the working area for the AC dtls to be merged
2931 FORALL i IN 1..l_ac_dtl_codes.COUNT
2932 DELETE FROM xla_analytical_dtls_b w
2933 WHERE amb_context_code = g_amb_context_code
2934 AND analytical_criterion_type_code = l_ac_dtl_type_codes(i)
2935 AND analytical_criterion_code = l_ac_dtl_codes(i)
2936 AND analytical_detail_code = l_ac_detail_code(i)
2937 AND EXISTS
2938 ( SELECT 1
2939 FROM xla_analytical_dtls_b s
2940 WHERE s.amb_context_code = g_staging_context_code
2941 AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
2942 AND s.analytical_criterion_code = w.analytical_criterion_code
2943 AND s.analytical_detail_code = w.analytical_detail_code);
2944
2945 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2946 trace(p_msg => '# xla_analytical_dtls_b delete : '||SQL%ROWCOUNT,
2947 p_module => l_log_module,
2948 p_level => C_LEVEL_STATEMENT);
2949 END IF;
2950
2951 FORALL i IN 1..l_ac_dtl_codes.COUNT
2952 DELETE FROM xla_analytical_dtls_tl w
2953 WHERE amb_context_code = g_amb_context_code
2954 AND analytical_criterion_type_code = l_ac_dtl_type_codes(i)
2955 AND analytical_criterion_code = l_ac_dtl_codes(i)
2956 AND analytical_detail_code = l_ac_detail_code(i)
2957 AND EXISTS
2958 ( SELECT 1
2959 FROM xla_analytical_dtls_tl s
2960 WHERE s.amb_context_code = g_staging_context_code
2961 AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
2962 AND s.analytical_criterion_code = w.analytical_criterion_code
2963 AND s.analytical_detail_code = w.analytical_detail_code
2964 AND s.language = w.language);
2965
2966 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2967 trace(p_msg => '# xla_analytical_dtls_tl delete : '||SQL%ROWCOUNT,
2968 p_module => l_log_module,
2969 p_level => C_LEVEL_STATEMENT);
2970 END IF;
2971
2972 FORALL i IN 1..l_ac_src_event_class.COUNT
2973 DELETE FROM xla_analytical_sources w
2974 WHERE amb_context_code = g_amb_context_code
2975 AND analytical_criterion_type_code = l_ac_dtl_type_codes(i)
2976 AND analytical_criterion_code = l_ac_dtl_codes(i)
2977 AND event_class_code =l_ac_src_event_class(i)
2978 AND ENTITY_CODE =l_ac_src_entity_code(i)
2979 AND SOURCE_CODE =l_ac_src_source_code(i)
2980 AND SOURCE_TYPE_CODE =l_ac_src_source_type_code(i)
2981 AND EXISTS
2982 ( SELECT 1
2983 FROM xla_analytical_sources s
2984 WHERE s.amb_context_code = g_staging_context_code
2985 AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
2986 AND s.analytical_criterion_code = w.analytical_criterion_code
2987 AND s.entity_code = w.entity_code
2988 AND s.event_class_code = w.event_class_code
2989 AND s.source_code = w.source_code
2990 AND s.source_type_code = w.source_type_code);
2991 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2992 trace(p_msg => '# xla_analytical_sources delete : '||SQL%ROWCOUNT,
2993 p_module => l_log_module,
2994 p_level => C_LEVEL_STATEMENT);
2995 END IF;
2996
2997 END IF;
2998
2999 -- Move the analytical criteria from staging area to working area
3000 FORALL i IN 1..l_ac_codes.COUNT
3001 UPDATE xla_analytical_hdrs_b
3002 SET amb_context_code = g_amb_context_code
3003 WHERE amb_context_code = g_staging_context_code
3004 AND analytical_criterion_type_code = l_ac_type_codes(i)
3005 AND analytical_criterion_code = l_ac_codes(i);
3006
3007
3008 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3009 trace(p_msg => '# xla_analytical_hdrs_b updated : '||SQL%ROWCOUNT,
3010 p_module => l_log_module,
3011 p_level => C_LEVEL_STATEMENT);
3012 END IF;
3013
3014 FORALL i IN 1..l_ac_codes.COUNT
3015 UPDATE xla_analytical_hdrs_tl s
3016 SET amb_context_code = g_amb_context_code
3017 WHERE amb_context_code = g_staging_context_code
3018 AND analytical_criterion_type_code = l_ac_type_codes(i)
3019 AND analytical_criterion_code = l_ac_codes(i)
3020 AND NOT EXISTS (SELECT 1
3021 FROM xla_analytical_hdrs_tl w
3022 WHERE w.amb_context_code = g_amb_context_code
3026
3023 AND w.analytical_criterion_type_code = s.analytical_criterion_type_code
3024 AND w.name = s.name
3025 AND w.language = s.language);
3027 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3028 trace(p_msg => '# xla_analytical_hdrs_tl 1 updated : '||SQL%ROWCOUNT,
3029 p_module => l_log_module,
3030 p_level => C_LEVEL_STATEMENT);
3031 END IF;
3032
3033 FORALL i IN 1..l_ac_codes.COUNT
3034 UPDATE xla_analytical_hdrs_tl s
3035 SET amb_context_code = g_amb_context_code
3036 , name = substr('('||s.analytical_criterion_code||') '||name,1,80)
3037 WHERE amb_context_code = g_staging_context_code
3038 AND analytical_criterion_type_code = l_ac_type_codes(i)
3039 AND analytical_criterion_code = l_ac_codes(i)
3040 AND EXISTS (SELECT 1
3041 FROM xla_analytical_hdrs_tl w
3042 WHERE w.amb_context_code = g_amb_context_code
3043 AND w.analytical_criterion_type_code = s.analytical_criterion_type_code
3044 AND w.name = s.name
3045 AND w.language = s.language);
3046
3047 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3048 trace(p_msg => '# xla_analytical_hdrs_tl 2 updated : '||SQL%ROWCOUNT,
3049 p_module => l_log_module,
3050 p_level => C_LEVEL_STATEMENT);
3051 END IF;
3052
3053 FORALL i IN 1..l_ac_dtl_codes.COUNT
3054 UPDATE xla_analytical_dtls_b
3055 SET amb_context_code = g_amb_context_code
3056 WHERE amb_context_code = g_staging_context_code
3057 AND analytical_criterion_type_code = l_ac_dtl_type_codes(i)
3058 AND analytical_criterion_code = l_ac_dtl_codes(i)
3059 AND analytical_detail_code = l_ac_detail_code(i);
3060
3061 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3062 trace(p_msg => '# xla_analytical_dtls_b updated : '||SQL%ROWCOUNT,
3063 p_module => l_log_module,
3064 p_level => C_LEVEL_STATEMENT);
3065 END IF;
3066
3067 FORALL i IN 1..l_ac_dtl_codes.COUNT
3068 UPDATE xla_analytical_dtls_tl s
3069 SET amb_context_code = g_amb_context_code
3070 WHERE amb_context_code = g_staging_context_code
3071 AND analytical_criterion_type_code = l_ac_dtl_type_codes(i)
3072 AND analytical_criterion_code = l_ac_dtl_codes(i)
3073 AND analytical_detail_code = l_ac_detail_code(i)
3074 AND NOT EXISTS (SELECT 1
3075 FROM xla_analytical_dtls_tl w
3076 WHERE w.amb_context_code = g_amb_context_code
3077 AND w.analytical_criterion_type_code = s.analytical_criterion_type_code
3078 AND s.analytical_criterion_code = s.analytical_criterion_code
3079 AND s.analytical_detail_code = s.analytical_detail_code
3080 AND w.name = s.name
3081 AND w.language = s.language);
3082
3083 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3084 trace(p_msg => '# xla_analytical_dtls_tl 1 updated : '||SQL%ROWCOUNT,
3085 p_module => l_log_module,
3086 p_level => C_LEVEL_STATEMENT);
3087 END IF;
3088
3089 FORALL i IN 1..l_ac_dtl_codes.COUNT
3090 UPDATE xla_analytical_dtls_tl w
3091 SET amb_context_code = g_amb_context_code
3092 , name = substr('('||w.analytical_detail_code||') '||name,1,80)
3093 WHERE amb_context_code = g_staging_context_code
3094 AND analytical_criterion_type_code = l_ac_dtl_type_codes(i)
3095 AND analytical_criterion_code = l_ac_dtl_codes(i)
3096 AND analytical_detail_code = l_ac_detail_code(i)
3097 AND EXISTS (SELECT 1
3098 FROM xla_analytical_dtls_tl s
3099 WHERE s.amb_context_code = g_amb_context_code
3100 AND w.analytical_criterion_type_code = s.analytical_criterion_type_code
3101 AND s.analytical_criterion_code = s.analytical_criterion_code
3102 AND s.analytical_detail_code = s.analytical_detail_code
3103 AND s.name = w.name
3104 AND s.language = w.language);
3105
3106 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3107 trace(p_msg => '# xla_analytical_dtls_tl 2 updated : '||SQL%ROWCOUNT,
3108 p_module => l_log_module,
3109 p_level => C_LEVEL_STATEMENT);
3110 END IF;
3111
3112 FORALL i IN 1..l_ac_src_event_class.COUNT
3113 UPDATE xla_analytical_sources
3114 SET amb_context_code = g_amb_context_code
3115 WHERE amb_context_code = g_staging_context_code
3116 AND analytical_criterion_type_code = l_ac_dtl_type_codes(i)
3117 AND analytical_criterion_code = l_ac_dtl_codes(i)
3118 AND event_class_code =l_ac_src_event_class(i)
3119 AND ENTITY_CODE =l_ac_src_entity_code(i)
3120 AND SOURCE_CODE =l_ac_src_source_code(i)
3121 AND SOURCE_TYPE_CODE =l_ac_src_source_type_code(i);
3122
3123 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3124 trace(p_msg => '# xla_analytical_sources updated : '||SQL%ROWCOUNT,
3125 p_module => l_log_module,
3126 p_level => C_LEVEL_STATEMENT);
3127 END IF;
3128
3129 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3133 END IF;
3130 trace(p_msg => 'END of procedure merge_analytical_criteria',
3131 p_module => l_log_module,
3132 p_level => C_LEVEL_PROCEDURE);
3134
3135 EXCEPTION
3136 WHEN OTHERS THEN
3137 xla_aad_loader_util_pvt.stack_error
3138 (p_appli_s_name => 'XLA'
3139 ,p_msg_name => 'XLA_COMMON_ERROR'
3140 ,p_token_1 => 'LOCATION'
3141 ,p_value_1 => 'xla_aad_merge_pvt.merge_analytical_criteria'
3142 ,p_token_2 => 'ERROR'
3143 ,p_value_2 => 'unhandled exception');
3144 RAISE;
3145
3146 END merge_analytical_criteria;
3147
3148
3149 --=============================================================================
3150 --
3151 -- Name: merge_adrs
3152 -- Description: Merge ADRs from staging to working area
3153 --
3154 --=============================================================================
3155 PROCEDURE merge_adrs
3156 IS
3157 CURSOR c_adr IS
3158 SELECT s.segment_rule_type_code, s.segment_rule_code
3159 FROM xla_seg_rules_b w
3160 , xla_seg_rules_b s
3161 WHERE s.application_id = g_application_id
3162 AND s.amb_context_code = g_staging_context_code
3163 AND s.segment_rule_type_code = w.segment_rule_type_code
3164 AND s.segment_rule_code = w.segment_rule_code
3165 AND w.application_id = g_application_id
3166 AND w.amb_context_code = g_amb_context_code;
3167
3168 l_adr_type_codes t_array_varchar30;
3169 l_adr_codes t_array_varchar30;
3170 l_log_module VARCHAR2(240);
3171 BEGIN
3172 IF g_log_enabled THEN
3173 l_log_module := C_DEFAULT_MODULE||'.merge_adrs';
3174 END IF;
3175
3176 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3177 trace(p_msg => 'BEGIN of procedure merge_adrs',
3178 p_module => l_log_module,
3179 p_level => C_LEVEL_PROCEDURE);
3180 END IF;
3181
3182 IF (g_analyzed_flag = 'Y') THEN
3183 null;
3184
3185 ELSE
3186
3187 OPEN c_adr;
3188 FETCH c_adr BULK COLLECT INTO l_adr_type_codes, l_adr_codes;
3189 CLOSE c_adr;
3190
3191 IF (l_adr_codes.COUNT > 0) THEN
3192 -- Delete the ADRs from the working area to be merged
3193 FORALL i IN 1..l_adr_codes.COUNT
3194 DELETE FROM xla_conditions c
3195 WHERE amb_context_code = g_amb_context_code
3196 AND application_id = g_application_id
3197 AND EXISTS (SELECT 1
3198 FROM xla_seg_rule_details w
3199 WHERE c.segment_rule_detail_id = w.segment_rule_detail_id
3200 AND w.application_id = g_application_id
3201 AND w.amb_context_code = g_amb_context_code
3202 AND w.segment_rule_type_code = l_adr_type_codes(i)
3203 AND w.segment_rule_code = l_adr_codes(i));
3204
3205 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3206 trace(p_msg => '# xla_conditions delete : '||SQL%ROWCOUNT,
3207 p_module => l_log_module,
3208 p_level => C_LEVEL_STATEMENT);
3209 END IF;
3210
3211 FORALL i IN 1..l_adr_codes.COUNT
3212 DELETE FROM xla_seg_rule_details w
3213 WHERE amb_context_code = g_amb_context_code
3214 AND application_id = g_application_id
3215 AND segment_rule_type_code = l_adr_type_codes(i)
3216 AND segment_rule_code = l_adr_codes(i);
3217
3218 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3219 trace(p_msg => '# xla_seg_rule_details delete : '||SQL%ROWCOUNT,
3220 p_module => l_log_module,
3221 p_level => C_LEVEL_STATEMENT);
3222 END IF;
3223
3224 FORALL i IN 1..l_adr_codes.COUNT
3225 DELETE FROM xla_seg_rules_tl w
3226 WHERE amb_context_code = g_amb_context_code
3227 AND application_id = g_application_id
3228 AND segment_rule_type_code = l_adr_type_codes(i)
3229 AND segment_rule_code = l_adr_codes(i);
3230
3231 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3232 trace(p_msg => '# xla_seg_rules_tl delete : '||SQL%ROWCOUNT,
3233 p_module => l_log_module,
3234 p_level => C_LEVEL_STATEMENT);
3235 END IF;
3236
3237 FORALL i IN 1..l_adr_codes.COUNT
3238 DELETE FROM xla_seg_rules_b w
3239 WHERE amb_context_code = g_amb_context_code
3240 AND application_id = g_application_id
3241 AND segment_rule_type_code = l_adr_type_codes(i)
3242 AND segment_rule_code = l_adr_codes(i);
3243
3244 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3245 trace(p_msg => '# xla_seg_rules_b delete : '||SQL%ROWCOUNT,
3246 p_module => l_log_module,
3247 p_level => C_LEVEL_STATEMENT);
3248 END IF;
3249
3250 END IF;
3251
3252 END IF;
3253
3254 -- Move the ADRs from staging area to working area
3255 UPDATE xla_seg_rules_b
3256 SET amb_context_code = g_amb_context_code
3257 WHERE amb_context_code = g_staging_context_code
3258 AND application_id = g_application_id;
3259
3260 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3261 trace(p_msg => '# xla_seg_rules_b updated : '||SQL%ROWCOUNT,
3265
3262 p_module => l_log_module,
3263 p_level => C_LEVEL_STATEMENT);
3264 END IF;
3266 UPDATE xla_seg_rules_tl w
3267 SET amb_context_code = g_amb_context_code
3268 WHERE amb_context_code = g_staging_context_code
3269 AND application_id = g_application_id
3270 AND NOT EXISTS (SELECT 1
3271 FROM xla_seg_rules_tl s
3272 WHERE s.amb_context_code = g_amb_context_code
3273 AND s.segment_rule_type_code = w.segment_rule_type_code
3274 AND s.name = w.name
3275 AND s.language = w.language);
3276
3277 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3278 trace(p_msg => '# xla_seg_rules_tl 1 updated : '||SQL%ROWCOUNT,
3279 p_module => l_log_module,
3280 p_level => C_LEVEL_STATEMENT);
3281 END IF;
3282
3283 UPDATE xla_seg_rules_tl w
3284 SET amb_context_code = g_amb_context_code
3285 , name = substr('('||w.segment_rule_code||') '||name,1,80)
3286 WHERE amb_context_code = g_staging_context_code
3287 AND application_id = g_application_id
3288 AND EXISTS (SELECT 1
3289 FROM xla_seg_rules_tl s
3290 WHERE s.amb_context_code = g_amb_context_code
3291 AND s.segment_rule_type_code = w.segment_rule_type_code
3292 AND s.name = w.name
3293 AND s.language = w.language);
3294
3295 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3296 trace(p_msg => '# xla_seg_rules_tl 1 updated : '||SQL%ROWCOUNT,
3297 p_module => l_log_module,
3298 p_level => C_LEVEL_STATEMENT);
3299 END IF;
3300
3301 UPDATE xla_seg_rule_details
3302 SET amb_context_code = g_amb_context_code
3303 WHERE amb_context_code = g_staging_context_code
3304 AND application_id = g_application_id;
3305
3306 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3307 trace(p_msg => '# xla_seg_rule_details updated : '||SQL%ROWCOUNT,
3308 p_module => l_log_module,
3309 p_level => C_LEVEL_STATEMENT);
3310 END IF;
3311
3312 UPDATE xla_conditions
3313 SET amb_context_code = g_amb_context_code
3314 WHERE amb_context_code = g_staging_context_code
3315 AND application_id = g_application_id
3316 AND segment_rule_detail_id IS NOT NULL;
3317
3318 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3319 trace(p_msg => '# xla_conditions updated : '||SQL%ROWCOUNT,
3320 p_module => l_log_module,
3321 p_level => C_LEVEL_STATEMENT);
3322 END IF;
3323
3324 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3325 trace(p_msg => 'END of procedure merge_adrs',
3326 p_module => l_log_module,
3327 p_level => C_LEVEL_PROCEDURE);
3328 END IF;
3329
3330 EXCEPTION
3331 WHEN OTHERS THEN
3332 xla_aad_loader_util_pvt.stack_error
3333 (p_appli_s_name => 'XLA'
3334 ,p_msg_name => 'XLA_COMMON_ERROR'
3335 ,p_token_1 => 'LOCATION'
3336 ,p_value_1 => 'xla_aad_merge_pvt.merge_adrs'
3337 ,p_token_2 => 'ERROR'
3338 ,p_value_2 => 'unhandled exception');
3339 RAISE;
3340
3341 END merge_adrs;
3342
3343
3344 --=============================================================================
3345 --
3346 -- Name: merge_mapping_sets
3347 -- Description: Merge mapping sets from staging area to the working area
3348 -- if the version number of the one in the staging area is higher
3349 -- or equal to the one in the working area
3350 --
3351 --=============================================================================
3352 PROCEDURE merge_mapping_sets
3353 IS
3354 -- Retrieve the mapping sets to be merged
3355 CURSOR c_ms IS
3356 SELECT s.mapping_set_code
3357 FROM xla_mapping_sets_b s
3358 , xla_mapping_sets_b w
3359 WHERE s.amb_context_code = g_staging_context_code
3360 AND w.amb_context_code(+) = g_amb_context_code
3361 AND s.mapping_set_code = w.mapping_set_code(+)
3362 AND s.version_num >= w.version_num(+);
3363
3364 l_ms t_array_varchar30;
3365 l_log_module VARCHAR2(240);
3366 BEGIN
3367 IF g_log_enabled THEN
3368 l_log_module := C_DEFAULT_MODULE||'.merge_mapping_sets';
3369 END IF;
3370
3371 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3372 trace(p_msg => 'BEGIN of procedure merge_mapping_sets',
3373 p_module => l_log_module,
3374 p_level => C_LEVEL_PROCEDURE);
3375 END IF;
3376
3377 IF (g_analyzed_flag = 'Y') THEN
3378 null;
3379
3380 ELSE
3381
3382 OPEN c_ms;
3383 FETCH c_ms BULK COLLECT INTO l_ms;
3384 CLOSE c_ms;
3385
3386 -- Delete the MSs from the working area to be merged
3387 FORALL i in 1 .. l_ms.count
3388 DELETE FROM xla_mapping_sets_b w
3389 WHERE amb_context_code = g_amb_context_code
3390 AND mapping_set_code = l_ms(i);
3391
3392 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3393 trace(p_msg => '# xla_mapping_sets_b delete : '||SQL%ROWCOUNT,
3394 p_module => l_log_module,
3398 FORALL i in 1 .. l_ms.count
3395 p_level => C_LEVEL_STATEMENT);
3396 END IF;
3397
3399 DELETE FROM xla_mapping_sets_tl w
3400 WHERE amb_context_code = g_amb_context_code
3401 AND mapping_set_code = l_ms(i)
3402 AND EXISTS (SELECT 1
3403 FROM xla_mapping_sets_tl s
3404 WHERE s.amb_context_code = g_staging_context_code
3405 AND s.mapping_set_code = w.mapping_set_code
3406 AND s.language = w.language);
3407
3408 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3409 trace(p_msg => '# xla_mapping_sets_tl delete : '||SQL%ROWCOUNT,
3410 p_module => l_log_module,
3411 p_level => C_LEVEL_STATEMENT);
3412 END IF;
3413
3414 FORALL i in 1 .. l_ms.count
3415 DELETE FROM xla_mapping_set_values w
3416 WHERE amb_context_code = g_amb_context_code
3417 AND mapping_set_code = l_ms(i);
3418
3419 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3420 trace(p_msg => '# xla_mapping_set_values delete : '||SQL%ROWCOUNT,
3421 p_module => l_log_module,
3422 p_level => C_LEVEL_STATEMENT);
3423 END IF;
3424
3425 END IF;
3426
3427 -- Move the mapping sets from staging area to working area
3428 FORALL i in 1 .. l_ms.count
3429 UPDATE xla_mapping_sets_b
3430 SET amb_context_code = g_amb_context_code
3431 WHERE amb_context_code = g_staging_context_code
3432 AND mapping_set_code = l_ms(i);
3433
3434 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3435 trace(p_msg => '# xla_mapping_sets_b updated : '||SQL%ROWCOUNT,
3436 p_module => l_log_module,
3437 p_level => C_LEVEL_STATEMENT);
3438 END IF;
3439
3440 FORALL i in 1 .. l_ms.count
3441 UPDATE xla_mapping_sets_tl s
3442 SET amb_context_code = g_amb_context_code
3443 WHERE amb_context_code = g_staging_context_code
3444 AND mapping_set_code = l_ms(i)
3445 AND NOT EXISTS (SELECT 1
3446 FROM xla_mapping_sets_tl w
3447 WHERE w.amb_context_code = g_amb_context_code
3448 AND w.name = s.name
3449 AND w.language = s.language);
3450
3451 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3452 trace(p_msg => '# xla_mapping_sets_tl 1 updated : '||SQL%ROWCOUNT,
3453 p_module => l_log_module,
3454 p_level => C_LEVEL_STATEMENT);
3455 END IF;
3456
3457 FORALL i in 1 .. l_ms.count
3458 UPDATE xla_mapping_sets_tl s
3459 SET amb_context_code = g_amb_context_code
3460 , name = substr('('||s.mapping_set_code||') '||name,1,80)
3461 WHERE amb_context_code = g_staging_context_code
3462 AND mapping_set_code = l_ms(i)
3463 AND EXISTS (SELECT 1
3464 FROM xla_mapping_sets_tl w
3465 WHERE w.amb_context_code = g_amb_context_code
3466 AND w.name = s.name
3467 AND w.language = s.language);
3468
3469 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3470 trace(p_msg => '# xla_mapping_sets_tl 2 updated : '||SQL%ROWCOUNT,
3471 p_module => l_log_module,
3472 p_level => C_LEVEL_STATEMENT);
3473 END IF;
3474
3475 FORALL i in 1 .. l_ms.count
3476 UPDATE xla_mapping_set_values
3477 SET amb_context_code = g_amb_context_code
3478 WHERE amb_context_code = g_staging_context_code
3479 AND mapping_set_code = l_ms(i);
3480
3481 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3482 trace(p_msg => '# xla_mapping_set_values updated : '||SQL%ROWCOUNT,
3483 p_module => l_log_module,
3484 p_level => C_LEVEL_STATEMENT);
3485 END IF;
3486
3487 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3488 trace(p_msg => 'END of procedure merge_mapping_sets',
3489 p_module => l_log_module,
3490 p_level => C_LEVEL_PROCEDURE);
3491 END IF;
3492
3493 EXCEPTION
3494 WHEN OTHERS THEN
3495 xla_aad_loader_util_pvt.stack_error
3496 (p_appli_s_name => 'XLA'
3497 ,p_msg_name => 'XLA_COMMON_ERROR'
3498 ,p_token_1 => 'LOCATION'
3499 ,p_value_1 => 'xla_aad_merge_pvt.merge_mapping_sets'
3500 ,p_token_2 => 'ERROR'
3501 ,p_value_2 => 'unhandled exception');
3502 RAISE;
3503
3504 END merge_mapping_sets;
3505
3506
3507 --=============================================================================
3508 --
3509 -- Bug 4685287 addition.
3510 -- Name: merge_acctg_methods
3511 -- Description: This API copies the accounting methods from the staging to the
3512 -- working area if not already exists. Then it moves the
3513 -- accounting method rules from the staging to the working area
3514 -- if no other accounting method rules have been assigned to the
3515 -- method for the application and destination context.
3516 --
3517 --=============================================================================
3518 PROCEDURE merge_acctg_methods
3519 IS
3520 l_log_module VARCHAR2(240);
3521 BEGIN
3522 IF g_log_enabled THEN
3523 l_log_module := C_DEFAULT_MODULE||'.merge_acctg_methods';
3524 END IF;
3525
3529 p_level => C_LEVEL_PROCEDURE);
3526 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3527 trace(p_msg => 'BEGIN of procedure merge_acctg_methods',
3528 p_module => l_log_module,
3530 END IF;
3531
3532 INSERT INTO xla_acctg_methods_b
3533 (accounting_method_type_code
3534 ,accounting_method_code
3535 ,transaction_coa_id
3536 ,accounting_coa_id
3537 ,enabled_flag
3538 ,creation_date
3539 ,created_by
3540 ,last_update_date
3541 ,last_updated_by
3542 ,last_update_login)
3543 SELECT s.accounting_method_type_code
3544 ,s.accounting_method_code
3545 ,s.transaction_coa_id
3546 ,s.accounting_coa_id
3547 ,s.enabled_flag
3548 ,sysdate
3549 ,xla_environment_pkg.g_usr_id
3550 ,sysdate
3551 ,xla_environment_pkg.g_usr_id
3552 ,xla_environment_pkg.g_login_id
3553 FROM xla_stage_acctg_methods s
3554 LEFT OUTER JOIN xla_acctg_methods_b w
3555 ON w.accounting_method_type_code = s.accounting_method_type_code
3556 AND w.accounting_method_code = s.accounting_method_code
3557 WHERE s.staging_amb_context_code = g_staging_context_code
3558 AND w.accounting_method_type_code IS NULL;
3559
3560 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3561 trace(p_msg => '# row inserted in xla_acctg_methods_b = '||SQL%ROWCOUNT,
3562 p_module => l_log_module,
3563 p_level => C_LEVEL_STATEMENT);
3564 END IF;
3565
3566 INSERT INTO xla_acctg_methods_tl
3567 (accounting_method_type_code
3568 ,accounting_method_code
3569 ,language
3570 ,name
3571 ,description
3572 ,source_lang
3573 ,creation_date
3574 ,created_by
3575 ,last_update_date
3576 ,last_updated_by
3577 ,last_update_login)
3578 SELECT s.accounting_method_type_code
3579 ,s.accounting_method_code
3580 ,fl.language_code
3581 ,s.name
3582 ,s.description
3583 ,USERENV('LANG')
3584 ,sysdate
3585 ,xla_environment_pkg.g_usr_id
3586 ,sysdate
3587 ,xla_environment_pkg.g_usr_id
3588 ,xla_environment_pkg.g_login_id
3589 FROM xla_stage_acctg_methods s
3590 JOIN fnd_languages fl
3591 ON fl.installed_flag IN ('I', 'B')
3592 LEFT OUTER JOIN xla_acctg_methods_tl w
3593 ON w.accounting_method_type_code = s.accounting_method_type_code
3594 AND w.accounting_method_code = s.accounting_method_code
3595 AND w.language = fl.language_code
3596 WHERE s.staging_amb_context_code = g_staging_context_code
3597 AND w.accounting_method_type_code IS NULL;
3598
3599 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3600 trace(p_msg => '# row inserted in xla_acctg_methods_tl = '||SQL%ROWCOUNT,
3601 p_module => l_log_module,
3602 p_level => C_LEVEL_STATEMENT);
3603 END IF;
3604
3605 UPDATE xla_acctg_method_rules xamr
3606 SET amb_context_code = g_amb_context_code
3607 WHERE amb_context_code = g_staging_context_code
3608 AND NOT EXISTS (SELECT 1
3609 FROM xla_acctg_method_rules xamr2
3610 WHERE xamr2.amb_context_code = g_amb_context_code
3611 AND xamr2.accounting_method_type_code = xamr.accounting_method_type_code
3612 AND xamr2.accounting_method_code = xamr.accounting_method_code
3613 AND xamr2.application_id = g_application_id);
3614
3615 INSERT INTO xla_aad_loader_logs
3616 (aad_loader_log_id
3617 ,amb_context_code
3618 ,application_id
3619 ,request_code
3620 ,log_type_code
3621 ,aad_application_id
3622 ,product_rule_code
3623 ,product_rule_type_code
3624 ,component_owner_code
3625 ,component_code
3626 ,object_version_number
3627 ,creation_date
3628 ,created_by
3629 ,last_update_date
3630 ,last_updated_by
3631 ,last_update_login
3632 ,program_update_date
3633 ,program_application_id
3634 ,program_id
3635 ,request_id)
3636 SELECT xla_aad_loader_logs_s.nextval
3637 ,g_amb_context_code
3638 ,g_application_id
3639 ,'IMPORT'
3640 ,'UNMERGE_AAD_IN_SLAM'
3641 ,g_application_id
3642 ,product_rule_code
3643 ,product_rule_type_code
3644 ,accounting_method_type_code
3645 ,accounting_method_code
3646 ,1
3647 ,sysdate
3648 ,xla_environment_pkg.g_usr_id
3649 ,sysdate
3650 ,xla_environment_pkg.g_usr_id
3651 ,xla_environment_pkg.g_login_id
3652 ,sysdate
3653 ,xla_environment_pkg.g_prog_appl_id
3654 ,xla_environment_pkg.g_prog_id
3655 ,xla_environment_pkg.g_req_Id
3656 FROM (SELECT distinct product_rule_type_code
3657 , product_rule_code
3658 , accounting_method_type_code
3659 , accounting_method_code
3660 FROM xla_acctg_method_rules
3661 WHERE amb_context_code = g_staging_context_code);
3662
3663 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3667 END IF;
3664 trace(p_msg => '# row inserted in xla_acctg_method_rules = '||SQL%ROWCOUNT,
3665 p_module => l_log_module,
3666 p_level => C_LEVEL_STATEMENT);
3668
3669 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3670 trace(p_msg => 'END of procedure merge_acctg_methods',
3671 p_module => l_log_module,
3672 p_level => C_LEVEL_PROCEDURE);
3673 END IF;
3674
3675 EXCEPTION
3676 WHEN OTHERS THEN
3677 xla_aad_loader_util_pvt.stack_error
3678 (p_appli_s_name => 'XLA'
3679 ,p_msg_name => 'XLA_COMMON_ERROR'
3680 ,p_token_1 => 'LOCATION'
3681 ,p_value_1 => 'xla_aad_merge_pvt.merge_acctg_methods'
3682 ,p_token_2 => 'ERROR'
3683 ,p_value_2 => 'unhandled exception');
3684 RAISE;
3685
3686 END merge_acctg_methods;
3687
3688
3689 --=============================================================================
3690 --
3691 -- Name: merge_aads_and_setups
3692 -- Description: This API merge the AADs and journal entry setups
3693 --
3694 --=============================================================================
3695 PROCEDURE merge_aads_and_setups
3696 IS
3697 l_log_module VARCHAR2(240);
3698 BEGIN
3699 IF g_log_enabled THEN
3700 l_log_module := C_DEFAULT_MODULE||'.merge_aads_and_setups';
3701 END IF;
3702
3703 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3704 trace(p_msg => 'BEGIN of procedure merge_aads_and_setups',
3705 p_module => l_log_module,
3706 p_level => C_LEVEL_PROCEDURE);
3707 END IF;
3708
3709 IF (g_analyzed_flag = 'Y') THEN
3710 null;
3711 /*
3712 duplicate_journal_line_defns;
3713 duplicate_journal_line_types;
3714 duplicate_descriptions;
3715 duplicate_analytical_criteria;
3716 duplicate_mapping_sets;
3717 duplicate_adrs;
3718 */
3719 END IF;
3720
3721 IF (g_user_type_code = 'C') THEN
3722 clean_oracle_components;
3723 END IF;
3724
3725 -- Merge AADs and journal entry setups
3726 merge_aads;
3727 merge_journal_line_defns;
3728 merge_journal_line_types;
3729 merge_descriptions;
3730 merge_analytical_criteria;
3731 merge_mapping_sets;
3732 merge_adrs;
3733 merge_acctg_methods; -- Bug 4685287 addition.
3734
3735 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3736 trace(p_msg => 'END of procedure merge_aads_and_setups',
3737 p_module => l_log_module,
3738 p_level => C_LEVEL_PROCEDURE);
3739 END IF;
3740
3741 EXCEPTION
3742 WHEN OTHERS THEN
3743 xla_aad_loader_util_pvt.stack_error
3744 (p_appli_s_name => 'XLA'
3745 ,p_msg_name => 'XLA_COMMON_ERROR'
3746 ,p_token_1 => 'LOCATION'
3747 ,p_value_1 => 'xla_aad_merge_pvt.merge_aads_and_setups'
3748 ,p_token_2 => 'ERROR'
3749 ,p_value_2 => 'Unhandled exception');
3750 RAISE;
3751
3752 END merge_aads_and_setups;
3753
3754
3755 --=============================================================================
3756 --
3757 -- Name: purge_mapping_sets
3758 -- Description:
3759 --
3760 --=============================================================================
3761 PROCEDURE purge_mapping_sets
3762 IS
3763 l_log_module VARCHAR2(240);
3764 BEGIN
3765 IF g_log_enabled THEN
3766 l_log_module := C_DEFAULT_MODULE||'.purge_mapping_sets';
3767 END IF;
3768
3769 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3770 trace(p_msg => 'BEGIN of procedure purge_mapping_sets',
3771 p_module => l_log_module,
3772 p_level => C_LEVEL_PROCEDURE);
3773 END IF;
3774
3775 DELETE FROM xla_mapping_set_values
3776 WHERE amb_context_code = g_staging_context_code;
3777
3778 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3779 trace(p_msg => '# xla_mapping_set_values delete : '||SQL%ROWCOUNT,
3780 p_module => l_log_module,
3781 p_level => C_LEVEL_STATEMENT);
3782 END IF;
3783
3784 DELETE FROM xla_mapping_sets_tl
3785 WHERE amb_context_code = g_staging_context_code;
3786
3787 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3788 trace(p_msg => '# xla_mapping_sets_tl delete : '||SQL%ROWCOUNT,
3789 p_module => l_log_module,
3790 p_level => C_LEVEL_STATEMENT);
3791 END IF;
3792
3793 DELETE FROM xla_mapping_sets_b
3794 WHERE amb_context_code = g_staging_context_code;
3795
3796 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3797 trace(p_msg => '# xla_mapping_sets_b delete : '||SQL%ROWCOUNT,
3798 p_module => l_log_module,
3799 p_level => C_LEVEL_STATEMENT);
3800 END IF;
3801
3802 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3803 trace(p_msg => 'END of procedure purge_mapping_sets',
3804 p_module => l_log_module,
3805 p_level => C_LEVEL_PROCEDURE);
3806 END IF;
3807
3808 EXCEPTION
3809 WHEN OTHERS THEN
3810 xla_aad_loader_util_pvt.stack_error
3811 (p_appli_s_name => 'XLA'
3812 ,p_msg_name => 'XLA_COMMON_ERROR'
3813 ,p_token_1 => 'LOCATION'
3814 ,p_value_1 => 'xla_aad_merge_pvt.purge_mapping_sets'
3818
3815 ,p_token_2 => 'ERROR'
3816 ,p_value_2 => 'unhandled exception');
3817 RAISE;
3819 END purge_mapping_sets;
3820
3821
3822 --=============================================================================
3823 --
3824 -- Name: purge_analytical_criteria
3825 -- Description:
3826 --
3827 --=============================================================================
3828 PROCEDURE purge_analytical_criteria
3829 IS
3830 l_log_module VARCHAR2(240);
3831 BEGIN
3832 IF g_log_enabled THEN
3833 l_log_module := C_DEFAULT_MODULE||'.purge_analytical_criteria';
3834 END IF;
3835
3836 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3837 trace(p_msg => 'BEGIN of procedure purge_analytical_criteria',
3838 p_module => l_log_module,
3839 p_level => C_LEVEL_PROCEDURE);
3840 END IF;
3841
3842 DELETE FROM xla_analytical_sources
3843 WHERE amb_context_code = g_staging_context_code;
3844
3845 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3846 trace(p_msg => '# xla_analytical_sources delete : '||SQL%ROWCOUNT,
3847 p_module => l_log_module,
3848 p_level => C_LEVEL_STATEMENT);
3849 END IF;
3850
3851 DELETE FROM xla_analytical_dtls_tl
3852 WHERE amb_context_code = g_staging_context_code;
3853
3854 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3855 trace(p_msg => '# xla_analytical_dtls_tl delete : '||SQL%ROWCOUNT,
3856 p_module => l_log_module,
3857 p_level => C_LEVEL_STATEMENT);
3858 END IF;
3859
3860 DELETE FROM xla_analytical_dtls_b
3861 WHERE amb_context_code = g_staging_context_code;
3862
3863 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3864 trace(p_msg => '# xla_analytical_dtls_b delete : '||SQL%ROWCOUNT,
3865 p_module => l_log_module,
3866 p_level => C_LEVEL_STATEMENT);
3867 END IF;
3868
3869 DELETE FROM xla_analytical_hdrs_tl
3870 WHERE amb_context_code = g_staging_context_code;
3871
3872 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3873 trace(p_msg => '# xla_analytical_hdrs_tl delete : '||SQL%ROWCOUNT,
3874 p_module => l_log_module,
3875 p_level => C_LEVEL_STATEMENT);
3876 END IF;
3877
3878 DELETE FROM xla_analytical_hdrs_b
3879 WHERE amb_context_code = g_staging_context_code;
3880
3881 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3882 trace(p_msg => '# xla_analytical_hdrs_b delete : '||SQL%ROWCOUNT,
3883 p_module => l_log_module,
3884 p_level => C_LEVEL_STATEMENT);
3885 END IF;
3886
3887 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3888 trace(p_msg => 'END of procedure purge_analytical_criteria',
3889 p_module => l_log_module,
3890 p_level => C_LEVEL_PROCEDURE);
3891 END IF;
3892
3893 EXCEPTION
3894 WHEN OTHERS THEN
3895 xla_aad_loader_util_pvt.stack_error
3896 (p_appli_s_name => 'XLA'
3897 ,p_msg_name => 'XLA_COMMON_ERROR'
3898 ,p_token_1 => 'LOCATION'
3899 ,p_value_1 => 'xla_aad_merge_pvt.purge_analytical_criteria'
3900 ,p_token_2 => 'ERROR'
3901 ,p_value_2 => 'unhandled exception');
3902 RAISE;
3903
3904 END purge_analytical_criteria;
3905
3906
3907 --=============================================================================
3908 --
3909 -- Name: purge_adrs
3910 -- Description:
3911 --
3912 --=============================================================================
3913 PROCEDURE purge_adrs
3914 IS
3915 l_log_module VARCHAR2(240);
3916 BEGIN
3917 IF g_log_enabled THEN
3918 l_log_module := C_DEFAULT_MODULE||'.purge_adrs';
3919 END IF;
3920
3921 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3922 trace(p_msg => 'BEGIN of procedure purge_adrs',
3923 p_module => l_log_module,
3924 p_level => C_LEVEL_PROCEDURE);
3925 END IF;
3926
3927 DELETE FROM xla_conditions
3928 WHERE amb_context_code = g_staging_context_code
3929 AND segment_rule_detail_id IS NOT NULl;
3930
3931 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3932 trace(p_msg => '# xla_conditions delete : '||SQL%ROWCOUNT,
3933 p_module => l_log_module,
3934 p_level => C_LEVEL_STATEMENT);
3935 END IF;
3936
3937 DELETE FROM xla_seg_rule_details
3938 WHERE amb_context_code = g_staging_context_code;
3939
3940 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3941 trace(p_msg => '# xla_seg_rules_details delete : '||SQL%ROWCOUNT,
3942 p_module => l_log_module,
3943 p_level => C_LEVEL_STATEMENT);
3944 END IF;
3945
3946 DELETE FROM xla_seg_rules_tl
3947 WHERE amb_context_code = g_staging_context_code;
3948
3949 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3950 trace(p_msg => '# xla_seg_rules_tl delete : '||SQL%ROWCOUNT,
3951 p_module => l_log_module,
3952 p_level => C_LEVEL_STATEMENT);
3953 END IF;
3954
3955 DELETE FROM xla_seg_rules_b
3956 WHERE amb_context_code = g_staging_context_code;
3957
3958 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3959 trace(p_msg => '# xla_seg_rules_b delete : '||SQL%ROWCOUNT,
3960 p_module => l_log_module,
3961 p_level => C_LEVEL_STATEMENT);
3962 END IF;
3963
3967 p_level => C_LEVEL_PROCEDURE);
3964 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3965 trace(p_msg => 'END of procedure purge_adrs',
3966 p_module => l_log_module,
3968 END IF;
3969
3970 EXCEPTION
3971 WHEN OTHERS THEN
3972 xla_aad_loader_util_pvt.stack_error
3973 (p_appli_s_name => 'XLA'
3974 ,p_msg_name => 'XLA_COMMON_ERROR'
3975 ,p_token_1 => 'LOCATION'
3976 ,p_value_1 => 'xla_aad_merge_pvt.purge_adrs'
3977 ,p_token_2 => 'ERROR'
3978 ,p_value_2 => 'unhandled exception');
3979 RAISE;
3980
3981 END purge_adrs;
3982
3983
3984 --=============================================================================
3985 --
3986 -- Name: purge_staging_area
3987 -- Description:
3988 --
3989 --=============================================================================
3990 PROCEDURE purge_staging_area
3991 IS
3992 l_log_module VARCHAR2(240);
3993 BEGIN
3994 IF g_log_enabled THEN
3995 l_log_module := C_DEFAULT_MODULE||'.purge_staging_area';
3996 END IF;
3997
3998 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3999 trace(p_msg => 'BEGIN of procedure purge_staging_area',
4000 p_module => l_log_module,
4001 p_level => C_LEVEL_PROCEDURE);
4002 END IF;
4003
4004 xla_aad_loader_util_pvt.purge
4005 (p_application_id => g_application_id
4006 ,p_amb_context_code => g_staging_context_code);
4007
4008 purge_mapping_sets;
4009 purge_analytical_criteria;
4010 purge_adrs;
4011
4012 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4013 trace(p_msg => 'END of procedure purge_staging_area',
4014 p_module => l_log_module,
4015 p_level => C_LEVEL_PROCEDURE);
4016 END IF;
4017
4018 EXCEPTION
4019 WHEN OTHERS THEN
4020 xla_aad_loader_util_pvt.stack_error
4021 (p_appli_s_name => 'XLA'
4022 ,p_msg_name => 'XLA_COMMON_ERROR'
4023 ,p_token_1 => 'LOCATION'
4024 ,p_value_1 => 'xla_aad_merge_pvt.purge_staging_area'
4025 ,p_token_2 => 'ERROR'
4026 ,p_value_2 => 'unhandled exception');
4027 RAISE;
4028
4029 END purge_staging_area;
4030
4031
4032 --=============================================================================
4033 --
4034 -- Name: template_api
4035 -- Description:
4036 --
4037 --=============================================================================
4038 PROCEDURE template_api
4039 IS
4040 l_log_module VARCHAR2(240);
4041 BEGIN
4042 IF g_log_enabled THEN
4043 l_log_module := C_DEFAULT_MODULE||'.template_api';
4044 END IF;
4045
4046 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4047 trace(p_msg => 'BEGIN of procedure template_api',
4048 p_module => l_log_module,
4049 p_level => C_LEVEL_PROCEDURE);
4050 END IF;
4051
4052 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4053 trace(p_msg => 'END of procedure template_api',
4054 p_module => l_log_module,
4055 p_level => C_LEVEL_PROCEDURE);
4056 END IF;
4057
4058 EXCEPTION
4059 WHEN OTHERS THEN
4060 xla_aad_loader_util_pvt.stack_error
4061 (p_appli_s_name => 'XLA'
4062 ,p_msg_name => 'XLA_COMMON_ERROR'
4063 ,p_token_1 => 'LOCATION'
4064 ,p_value_1 => 'xla_aad_merge_pvt.template_api'
4065 ,p_token_2 => 'ERROR'
4066 ,p_value_2 => 'unhandled exception');
4067 RAISE;
4068
4069 END template_api;
4070
4071
4072
4073 --=============================================================================
4074 --
4075 --
4076 --
4077 --
4078 --
4079 -- *********** public procedures and functions **********
4080 --
4081 --
4082 --
4083 --
4084 --
4085 --=============================================================================
4086
4087 PROCEDURE merge
4088 (p_api_version IN NUMBER
4089 ,x_return_status IN OUT NOCOPY VARCHAR2
4090 ,p_application_id IN INTEGER
4091 ,p_amb_context_code IN VARCHAR2
4092 ,p_analyzed_flag IN VARCHAR2
4093 ,p_compile_flag IN VARCHAR2
4094 ,x_merge_status IN OUT NOCOPY VARCHAR2)
4095 IS
4096 l_api_name CONSTANT VARCHAR2(30) := 'merge';
4097 l_api_version CONSTANT NUMBER := 1.0;
4098
4099 l_staging_context_code VARCHAR2(30);
4100 l_retcode VARCHAR2(30);
4101 l_log_module VARCHAR2(240);
4102 BEGIN
4103 IF g_log_enabled THEN
4104 l_log_module := C_DEFAULT_MODULE||'.merge';
4105 END IF;
4106
4107 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4108 trace(p_msg => 'BEGIN of function merge: '||
4109 'p_application_id = '||p_application_id||
4110 ', p_amb_context_code = '||p_amb_context_code||
4111 ', p_analyzed_flag = '||p_analyzed_flag||
4112 ', p_compile_flag = '||p_compile_flag,
4113 p_module => l_log_module,
4117 l_staging_context_code := xla_aad_loader_util_pvt.get_staging_context_code
4114 p_level => C_LEVEL_PROCEDURE);
4115 END IF;
4116
4118 (p_application_id => p_application_id
4119 ,p_amb_context_code => p_amb_context_code);
4120
4121 xla_aad_merge_pvt.merge
4122 (p_api_version => p_api_version
4123 ,x_return_status => x_return_status
4124 ,p_application_id => p_application_id
4125 ,p_amb_context_code => p_amb_context_code
4126 ,p_staging_context_code => l_staging_context_code
4127 ,p_analyzed_flag => p_analyzed_flag
4128 ,p_compile_flag => p_compile_flag
4129 ,x_merge_status => x_merge_status);
4130
4131 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4132 trace(p_msg => 'END of function merge - Return value = '||x_merge_status,
4133 p_module => l_log_module,
4134 p_level => C_LEVEL_PROCEDURE);
4135 END IF;
4136
4137 EXCEPTION
4138 WHEN G_EXC_WARNING THEN
4139 x_return_status := FND_API.G_RET_STS_SUCCESS ;
4140 x_merge_status := 'ERROR';
4141
4142 WHEN FND_API.G_EXC_ERROR THEN
4143 x_return_status := FND_API.G_RET_STS_ERROR ;
4144 x_merge_status := 'ERROR';
4145
4146 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4147 ROLLBACK;
4148 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4149 x_merge_status := 'ERROR';
4150
4151 WHEN OTHERS THEN
4152 ROLLBACK;
4153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4154 x_merge_status := 'ERROR';
4155
4156 xla_aad_loader_util_pvt.stack_error
4157 (p_appli_s_name => 'XLA'
4158 ,p_msg_name => 'XLA_COMMON_ERROR'
4159 ,p_token_1 => 'LOCATION'
4160 ,p_value_1 => 'xla_aad_merge_pvt.merge'
4161 ,p_token_2 => 'ERROR'
4162 ,p_value_2 => 'unhandled exception');
4163
4164 --RAISE;
4165
4166 END merge;
4167
4168
4169 --=============================================================================
4170 --
4171 -- Name: merge
4172 -- Description: This API merges the AADs and its components from the
4173 -- staging area to the working area of an AMB context
4174 --
4175 --=============================================================================
4176 PROCEDURE merge
4177 (p_api_version IN NUMBER
4178 ,x_return_status IN OUT NOCOPY VARCHAR2
4179 ,p_application_id IN INTEGER
4180 ,p_amb_context_code IN VARCHAR2
4181 ,p_staging_context_code IN VARCHAR2
4182 ,p_analyzed_flag IN VARCHAR2
4183 ,p_compile_flag IN VARCHAR2
4184 ,x_merge_status IN OUT NOCOPY VARCHAR2)
4185 IS
4186 l_api_name CONSTANT VARCHAR2(30) := 'merge';
4187 l_api_version CONSTANT NUMBER := 1.0;
4188
4189 l_retcode VARCHAR2(30);
4190 l_log_module VARCHAR2(240);
4191 BEGIN
4192 IF g_log_enabled THEN
4193 l_log_module := C_DEFAULT_MODULE||'.merge';
4194 END IF;
4195
4196 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4197 trace(p_msg => 'BEGIN of function merge: '||
4198 'p_application_id = '||p_application_id||
4199 ', p_amb_context_code = '||p_amb_context_code||
4200 ', p_analyzed_flag = '||p_analyzed_flag||
4201 ', p_compile_flag = '||p_compile_flag,
4202 p_module => l_log_module,
4203 p_level => C_LEVEL_PROCEDURE);
4204 END IF;
4205
4206 -- Standard call to check for call compatibility.
4207 IF (NOT xla_aad_loader_util_pvt.compatible_api_call
4208 (p_current_version_number => l_api_version
4209 ,p_caller_version_number => p_api_version
4210 ,p_api_name => l_api_name
4211 ,p_pkg_name => C_DEFAULT_MODULE))
4212 THEN
4213 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4214 END IF;
4215
4216 -- Initialize global variables
4217 x_return_status := FND_API.G_RET_STS_SUCCESS;
4218
4219 g_usr_id := xla_environment_pkg.g_usr_id;
4220 g_login_id := xla_environment_pkg.g_login_id;
4221 g_application_id := p_application_id;
4222 g_amb_context_code := p_amb_context_code;
4223 g_analyzed_flag := p_analyzed_flag;
4224 g_compile_flag := p_compile_flag;
4225 g_staging_context_code := p_staging_context_code;
4226 g_user_type_code := NVL(fnd_profile.value('XLA_SETUP_USER_MODE'),'C');
4227
4228 -- API Logic
4229 x_merge_status := pre_merge;
4230 IF (x_merge_status = 'WARNING') THEN
4231 RAISE G_EXC_WARNING;
4232 END IF;
4233
4234 x_merge_status := validation;
4235 IF (x_merge_status = 'WARNING') THEN
4236 RAISE G_EXC_WARNING;
4237 END IF;
4238
4239 merge_aads_and_setups;
4240
4241 xla_aad_loader_util_pvt.merge_history
4242 (p_application_id => g_application_id
4243 ,p_staging_context_code => g_staging_context_code);
4244
4245 purge_staging_area;
4246
4247 xla_aad_loader_util_pvt.rebuild_ac_views;
4248
4249 IF (p_compile_flag = 'Y') THEN
4253 RAISE G_EXC_WARNING;
4250 IF (NOT xla_aad_loader_util_pvt.compile
4251 (p_application_id => g_application_id
4252 ,p_amb_context_code => g_amb_context_code)) THEN
4254 END IF;
4255 END IF;
4256
4257 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4258 trace(p_msg => 'END of function merge - Return value = '||x_merge_status,
4259 p_module => l_log_module,
4260 p_level => C_LEVEL_PROCEDURE);
4261 END IF;
4262 EXCEPTION
4263 WHEN G_EXC_WARNING THEN
4264 x_return_status := FND_API.G_RET_STS_SUCCESS ;
4265 x_merge_status := 'ERROR';
4266
4267 WHEN FND_API.G_EXC_ERROR THEN
4268 x_return_status := FND_API.G_RET_STS_ERROR ;
4269 x_merge_status := 'ERROR';
4270
4271 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4272 ROLLBACK;
4273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4274 x_merge_status := 'ERROR';
4275
4276 WHEN OTHERS THEN
4277 ROLLBACK;
4278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4279 x_merge_status := 'ERROR';
4280
4281 xla_aad_loader_util_pvt.stack_error
4282 (p_appli_s_name => 'XLA'
4283 ,p_msg_name => 'XLA_COMMON_ERROR'
4284 ,p_token_1 => 'LOCATION'
4285 ,p_value_1 => 'xla_aad_merge_pvt.merge'
4286 ,p_token_2 => 'ERROR'
4287 ,p_value_2 => 'unhandled exception');
4288
4289 --RAISE;
4290
4291 END merge;
4292
4293 --=============================================================================
4294 --
4295 -- Following code is executed when the package body is referenced for the first
4296 -- time
4297 --
4298 --=============================================================================
4299 BEGIN
4300 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4301 g_log_enabled := fnd_log.test
4302 (log_level => g_log_level
4303 ,module => C_DEFAULT_MODULE);
4304
4305 IF NOT g_log_enabled THEN
4306 g_log_level := C_LEVEL_LOG_DISABLED;
4307 END IF;
4308
4309 END xla_aad_merge_pvt;