[Home] [Help]
PACKAGE BODY: APPS.XLA_AAD_OVERWRITE_PVT
Source
1 PACKAGE BODY xla_aad_overwrite_pvt AS
2 /* $Header: xlaalovw.pkb 120.14 2006/06/28 19:36:32 wychan ship $ */
3
4 --=============================================================================
5 -- **************** declaraions ********************
6 --=============================================================================
7 -------------------------------------------------------------------------------
8 -- declaring global types
9 -------------------------------------------------------------------------------
10 TYPE t_array_varchar2 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
11
12 -------------------------------------------------------------------------------
13 -- declaring global constants
14 -------------------------------------------------------------------------------
15 ------------------------------------------------------------------------------
16 -- declaring global variables
17 ------------------------------------------------------------------------------
18 g_amb_context_code VARCHAR2(30);
19 g_staging_context_code VARCHAR2(30);
20 g_application_id INTEGER;
21 g_force_flag VARCHAR2(1);
22 g_ac_updated BOOLEAN;
23
24 G_EXC_WARNING EXCEPTION;
25
26 --=============================================================================
27 -- *********** Local Trace Routine **********
28 --=============================================================================
29 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
30 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
31 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
32 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
33 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
34 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
35
36 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
37 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_aad_overwrite_pvt';
38
39 g_log_level NUMBER;
40 g_log_enabled BOOLEAN;
41
42 PROCEDURE trace
43 (p_msg IN VARCHAR2
44 ,p_module IN VARCHAR2
45 ,p_level IN NUMBER) IS
46 l_time varchar2(300);
47 BEGIN
48 ----------------------------------------------------------------------------
49 -- Following is for FND log.
50 ----------------------------------------------------------------------------
51 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
52 fnd_log.message(p_level, p_module);
53 ELSIF p_level >= g_log_level THEN
54 fnd_log.string(p_level, p_module, p_msg);
55 END IF;
56 EXCEPTION
57 WHEN OTHERS THEN
58 xla_exceptions_pkg.raise_message
59 (p_location => 'xla_aad_overwrite_pvt.trace');
60
61 END trace;
62
63
64 --=============================================================================
65 -- *********** private procedures and functions **********
66 --=============================================================================
67
68 --=============================================================================
69 --
70 -- Name: validation
71 -- Description: This API validate the AADs and components
72 -- Return codes:
73 -- SUCCESS - completed sucessfully
74 -- WARNING - completed with warning
75 -- ERROR - completed with error
76 --
77 --=============================================================================
78 FUNCTION validation
79 RETURN VARCHAR2
80 IS
81 CURSOR c_aad IS
82 SELECT t.name
83 FROM xla_product_rules_b w
84 ,xla_product_rules_b s
85 ,xla_product_rules_tl t
86 WHERE s.version_num < w.version_num
87 AND t.language = USERENV('LANG')
88 AND t.application_id = w.application_id
89 AND t.amb_context_code = w.amb_context_code
90 AND t.product_rule_type_code = w.product_rule_type_code
91 AND t.product_rule_code = w.product_rule_code
92 AND w.application_id = s.application_id
93 AND w.product_rule_type_code = s.product_rule_type_code
94 AND w.product_rule_code = s.product_rule_code
95 AND w.amb_context_code = g_amb_context_code
96 AND s.application_id = g_application_id
97 AND s.amb_context_code = g_staging_context_code;
98
99 CURSOR c_ms IS
100 SELECT t.name
101 FROM xla_mapping_sets_b w
102 ,xla_mapping_sets_b s
103 ,xla_mapping_sets_tl t
104 WHERE s.version_num < w.version_num
105 AND t.language = USERENV('LANG')
106 AND t.amb_context_code = w.amb_context_code
107 AND t.mapping_set_code = w.mapping_set_code
108 AND s.mapping_set_code = w.mapping_set_code
109 AND w.amb_context_code = g_amb_context_code
110 AND s.amb_context_code = g_staging_context_code;
111
112 CURSOR c_adr IS
113 SELECT t.name
114 FROM xla_seg_rules_b w
115 ,xla_seg_rules_b s
116 ,xla_seg_rules_tl t
117 WHERE s.version_num < w.version_num
118 AND t.language = USERENV('LANG')
119 AND t.amb_context_code = w.amb_context_code
120 AND t.application_id = w.application_id
121 AND t.segment_rule_type_code = w.segment_rule_type_code
122 AND t.segment_rule_code = w.segment_rule_code
123 AND s.application_id = w.application_id
124 AND s.segment_rule_type_code = w.segment_rule_type_code
125 AND s.segment_rule_code = w.segment_rule_code
126 AND w.amb_context_code = g_amb_context_code
127 AND w.application_id = g_application_id
128 AND s.amb_context_code = g_staging_context_code;
129
130 CURSOR c_ac IS
131 SELECT t.name
132 FROM xla_analytical_hdrs_b w
133 ,xla_analytical_hdrs_b s
134 ,xla_analytical_hdrs_tl t
135 WHERE s.version_num < w.version_num
136 AND t.language = USERENV('LANG')
137 AND t.amb_context_code = w.amb_context_code
138 AND t.analytical_criterion_type_code = w.analytical_criterion_type_code
139 AND t.analytical_criterion_code = w.analytical_criterion_code
140 AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
141 AND s.analytical_criterion_code = w.analytical_criterion_code
142 AND w.amb_context_code = g_amb_context_code
143 AND s.amb_context_code = g_staging_context_code;
144
145 l_retcode VARCHAR2(30);
146 l_log_module VARCHAR2(240);
147
148 BEGIN
149 IF g_log_enabled THEN
150 l_log_module := C_DEFAULT_MODULE||'.validation';
151 END IF;
152
153 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
154 trace(p_msg => 'BEGIN of function validation',
155 p_module => l_log_module,
156 p_level => C_LEVEL_PROCEDURE);
157 END IF;
158
159 l_retcode := 'SUCCESS';
160
161 l_retcode := xla_aad_loader_util_pvt.validate_adr_compatibility
162 (p_application_id => g_application_id
163 ,p_amb_context_code => g_amb_context_code
164 ,p_staging_context_code => g_staging_context_code);
165
166 IF (g_force_flag <> 'Y') THEN
167 IF (C_LEVEL_EVENT >= g_log_level) THEN
168 trace(p_msg => 'BEGIN LOOP - invalid AAD versions',
169 p_module => l_log_module,
170 p_level => C_LEVEL_EVENT);
171 END IF;
172
173 FOR l_aad IN c_aad LOOP
174 IF (C_LEVEL_ERROR >= g_log_level) THEN
175 trace(p_msg => 'LOOP - invalid AAD version: '||l_aad.name,
176 p_module => l_log_module,
177 p_level => C_LEVEL_ERROR);
178 END IF;
179
180 l_retcode := 'WARNING';
181 xla_aad_loader_util_pvt.stack_error
182 (p_appli_s_name => 'XLA'
183 ,p_msg_name => 'XLA_AAD_OVW_INV_AAD_VERS'
184 ,p_token_1 => 'PROD_RULE_NAME'
185 ,p_value_1 => l_aad.name);
186 END LOOP;
187
188 IF (C_LEVEL_EVENT >= g_log_level) THEN
189 trace(p_msg => 'END LOOP - invalid AAD versions',
190 p_module => l_log_module,
191 p_level => C_LEVEL_EVENT);
192 END IF;
193
194 IF (C_LEVEL_EVENT >= g_log_level) THEN
195 trace(p_msg => 'BEGIN LOOP - invalid MS versions',
196 p_module => l_log_module,
197 p_level => C_LEVEL_EVENT);
198 END IF;
199
200 FOR l_ms IN c_ms LOOP
201 IF (C_LEVEL_ERROR >= g_log_level) THEN
202 trace(p_msg => 'LOOP - invalid MS version: '||l_ms.name,
203 p_module => l_log_module,
204 p_level => C_LEVEL_ERROR);
205 END IF;
206
207 l_retcode := 'WARNING';
208 xla_aad_loader_util_pvt.stack_error
209 (p_appli_s_name => 'XLA'
210 ,p_msg_name => 'XLA_AAD_OVW_INV_MS_VERS'
211 ,p_token_1 => 'MAPPING_SET_NAME'
212 ,p_value_1 => l_ms.name);
213 END LOOP;
214
215 IF (C_LEVEL_EVENT >= g_log_level) THEN
216 trace(p_msg => 'END LOOP - invalid MS versions',
217 p_module => l_log_module,
218 p_level => C_LEVEL_EVENT);
219 END IF;
220
221 IF (C_LEVEL_EVENT >= g_log_level) THEN
222 trace(p_msg => 'BEGIN LOOP - invalid ADR versions',
223 p_module => l_log_module,
224 p_level => C_LEVEL_EVENT);
225 END IF;
226
227 FOR l_adr IN c_adr LOOP
228 IF (C_LEVEL_ERROR >= g_log_level) THEN
229 trace(p_msg => 'LOOP - invalid ADR version: '||l_adr.name,
230 p_module => l_log_module,
231 p_level => C_LEVEL_ERROR);
232 END IF;
233
234 l_retcode := 'WARNING';
235 xla_aad_loader_util_pvt.stack_error
236 (p_appli_s_name => 'XLA'
237 ,p_msg_name => 'XLA_AAD_OVW_INV_ADR_VERS'
238 ,p_token_1 => 'SEGMENT_RULE_NAME'
239 ,p_value_1 => l_adr.name);
240 END LOOP;
241
242 IF (C_LEVEL_EVENT >= g_log_level) THEN
243 trace(p_msg => 'END LOOP - invalid ADR versions',
244 p_module => l_log_module,
245 p_level => C_LEVEL_EVENT);
246 END IF;
247
248 IF (C_LEVEL_EVENT >= g_log_level) THEN
249 trace(p_msg => 'BEGIN LOOP - invalid AC versions',
250 p_module => l_log_module,
251 p_level => C_LEVEL_EVENT);
252 END IF;
253
254 FOR l_ac IN c_ac LOOP
255 IF (C_LEVEL_ERROR >= g_log_level) THEN
256 trace(p_msg => 'LOOP - invalid AC version: '||l_ac.name,
257 p_module => l_log_module,
258 p_level => C_LEVEL_ERROR);
259 END IF;
260
261 l_retcode := 'WARNING';
262 xla_aad_loader_util_pvt.stack_error
263 (p_appli_s_name => 'XLA'
264 ,p_msg_name => 'XLA_AAD_OVW_INV_AC_VERS'
265 ,p_token_1 => 'ANALYTICAL_CRITERION_NAME'
266 ,p_value_1 => l_ac.name);
267 END LOOP;
268
269 IF (C_LEVEL_EVENT >= g_log_level) THEN
270 trace(p_msg => 'END LOOP - invalid AC versions',
271 p_module => l_log_module,
272 p_level => C_LEVEL_EVENT);
273 END IF;
274 END IF;
275
276 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
277 trace(p_msg => 'END of function validation - Return value = '||l_retcode,
278 p_module => l_log_module,
279 p_level => C_LEVEL_PROCEDURE);
280 END IF;
281
282 return l_retcode;
283 EXCEPTION
284 WHEN OTHERS THEN
285 xla_aad_loader_util_pvt.stack_error
286 (p_appli_s_name => 'XLA'
287 ,p_msg_name => 'XLA_COMMON_ERROR'
288 ,p_token_1 => 'LOCATION'
289 ,p_value_1 => 'xla_aad_overwrite_pvt.validation'
290 ,p_token_2 => 'ERROR'
291 ,p_value_2 => 'unhandled exception');
292 RAISE;
293
294 END validation;
295
296
297 --=============================================================================
298 --
299 -- Name: record_log
300 -- Description: This API records the overwritten application accounting
301 -- definitions into the log table
302 --
303 --=============================================================================
304 PROCEDURE record_log
305 IS
306 l_log_module VARCHAR2(240);
307 BEGIN
308 IF g_log_enabled THEN
309 l_log_module := C_DEFAULT_MODULE||'.record_log';
310 END IF;
311
312 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
313 trace(p_msg => 'BEGIN of procedure record_log',
314 p_module => l_log_module,
315 p_level => C_LEVEL_PROCEDURE);
316 END IF;
317
318 INSERT INTO xla_aad_loader_logs
319 (aad_loader_log_id
320 ,amb_context_code
321 ,application_id
322 ,request_code
323 ,log_type_code
324 ,aad_application_id
325 ,product_rule_code
326 ,product_rule_type_code
327 ,version_to
328 ,object_version_number
329 ,creation_date
330 ,created_by
331 ,last_update_date
332 ,last_updated_by
333 ,last_update_login
334 ,program_update_date
335 ,program_application_id
336 ,program_id
337 ,request_id)
338 SELECT xla_aad_loader_logs_s.nextval
339 ,g_amb_context_code
340 ,g_application_id
341 ,'IMPORT'
342 ,'OVERWRITTEN_AAD'
343 ,s.application_id
344 ,s.product_rule_code
345 ,s.product_rule_type_code
346 ,s.version_num
347 ,1
348 ,sysdate
349 ,xla_environment_pkg.g_usr_id
350 ,sysdate
351 ,xla_environment_pkg.g_usr_id
352 ,xla_environment_pkg.g_login_id
353 ,sysdate
354 ,xla_environment_pkg.g_prog_appl_id
355 ,xla_environment_pkg.g_prog_id
356 ,xla_environment_pkg.g_req_Id
357 FROM xla_product_rules_b s
358 WHERE s.application_id = g_application_id
359 AND s.amb_context_code = g_staging_context_code;
360
361 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
362 trace(p_msg => '# row inserted into xla_aad_loader_logs = '||SQL%ROWCOUNT,
363 p_module => l_log_module,
364 p_level => C_LEVEL_STATEMENT);
365 END IF;
366
367 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
368 trace(p_msg => 'END of procedure record_log',
369 p_module => l_log_module,
370 p_level => C_LEVEL_PROCEDURE);
371 END IF;
372
373 EXCEPTION
374 WHEN OTHERS THEN
375 xla_aad_loader_util_pvt.stack_error
376 (p_appli_s_name => 'XLA'
377 ,p_msg_name => 'XLA_COMMON_ERROR'
378 ,p_token_1 => 'LOCATION'
379 ,p_value_1 => 'xla_aad_overwrite_pvt.record_log'
380 ,p_token_2 => 'ERROR'
381 ,p_value_2 => 'unhandled exception');
382 RAISE;
383
384 END record_log;
385
386
387 --=============================================================================
388 --
389 -- Name: pre_overwrite
390 -- Description: This API prepares the environment for overwrite
391 --
392 --=============================================================================
393 FUNCTION pre_overwrite
394 RETURN VARCHAR2
395 IS
396 CURSOR c IS
397 SELECT *
398 FROM xla_appli_amb_contexts
399 WHERE application_id = g_application_id
400 AND amb_context_code = g_amb_context_code
401 FOR UPDATE OF application_id NOWAIT;
402
403 l_lock_error BOOLEAN;
404 l_recinfo xla_appli_amb_contexts%ROWTYPE;
405 l_retcode VARCHAR2(30);
406 l_log_module VARCHAR2(240);
407 BEGIN
408 IF g_log_enabled THEN
409 l_log_module := C_DEFAULT_MODULE||'.pre_overwrite';
410 END IF;
411
412 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
413 trace(p_msg => 'BEGIN of function pre_overwrite',
414 p_module => l_log_module,
415 p_level => C_LEVEL_PROCEDURE);
416 END IF;
417
418 l_retcode := 'SUCCESS';
419
420 -- Lock the staging area of the AMB context
421 l_lock_error := TRUE;
422 OPEN c;
423 CLOSE c;
424 l_lock_error := FALSE;
425
426 IF (l_retcode = 'SUCCESS') THEN
427 l_retcode := xla_aad_loader_util_pvt.lock_area
428 (p_application_id => g_application_id
429 ,p_amb_context_code => g_amb_context_code);
430
431 IF (l_retcode <> 'SUCCESS') THEN
432 xla_aad_loader_util_pvt.stack_error
433 (p_appli_s_name => 'XLA'
434 ,p_msg_name => 'XLA_AAD_OVW_LOCK_FAILED');
435 l_retcode := 'WARNING';
436 END IF;
437 END IF;
438
439 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
440 trace(p_msg => 'END of function pre_overwrite - Return value = '||l_retcode,
441 p_module => l_log_module,
442 p_level => C_LEVEL_PROCEDURE);
443 END IF;
444
445 RETURN l_retcode;
446 EXCEPTION
447 WHEN OTHERS THEN
448 IF (c%ISOPEN) THEN
449 CLOSE c;
450 END IF;
451
452 IF (l_lock_error) THEN
453 l_retcode := 'WARNING';
454
455 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
456 trace(p_msg => 'END of function pre_overwrite - Return value = '||l_retcode,
457 p_module => l_log_module,
458 p_level => C_LEVEL_PROCEDURE);
459 END IF;
460
461 xla_aad_loader_util_pvt.stack_error
462 (p_appli_s_name => 'XLA'
463 ,p_msg_name => 'XLA_AAD_OVW_LOCK_FAILED');
464
465 RETURN l_retcode;
466 ELSE
467 xla_aad_loader_util_pvt.stack_error
468 (p_appli_s_name => 'XLA'
469 ,p_msg_name => 'XLA_COMMON_ERROR'
470 ,p_token_1 => 'LOCATION'
471 ,p_value_1 => 'xla_aad_overwrite_pvt.pre_overwrite'
472 ,p_token_2 => 'ERROR'
473 ,p_value_2 => 'unhandled exception');
474 RAISE;
475 END IF;
476
477 END pre_overwrite;
478
479
480 --=============================================================================
481 --
482 -- Name: purge_mapping_sets
483 -- Description: This API deletes the mapping sets from the working area
484 -- if it exists in the working area.
485 --
486 --=============================================================================
487 PROCEDURE purge_mapping_sets
488 IS
489 CURSOR c_ms IS
490 SELECT bs.mapping_set_code
491 ,bw.version_num version_from
492 ,bs.version_num version_to
493 FROM xla_mapping_sets_b bs
494 ,xla_mapping_sets_b bw
495 WHERE bs.mapping_set_code = bw.mapping_set_code
496 AND bs.amb_context_code = g_staging_context_code
497 AND bw.amb_context_code = g_amb_context_code;
498
499 l_mapping_sets xla_component_tbl_type;
500 l_mapping_set xla_component_rec_type;
501 l_ms_codes t_array_varchar2;
502 i INTEGER;
503
504 l_log_module VARCHAR2(240);
505 BEGIN
506 IF g_log_enabled THEN
507 l_log_module := C_DEFAULT_MODULE||'.purge_mapping_sets';
508 END IF;
509
510 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
511 trace(p_msg => 'BEGIN of procedure purge_mapping_sets',
512 p_module => l_log_module,
513 p_level => C_LEVEL_PROCEDURE);
514 END IF;
515
516 i := 0;
517 l_mapping_sets := xla_component_tbl_type();
518
519 IF (C_LEVEL_EVENT >= g_log_level) THEN
520 trace(p_msg => 'BEGIN LOOP - retrieve mapping set',
521 p_module => l_log_module,
522 p_level => C_LEVEL_EVENT);
523 END IF;
524
525 FOR l_ms IN c_ms LOOP
526 IF (C_LEVEL_EVENT >= g_log_level) THEN
527 trace(p_msg => 'LOOP - mapping set = '||l_ms.mapping_set_code,
528 p_module => l_log_module,
529 p_level => C_LEVEL_EVENT);
530 END IF;
531
532 i := i + 1;
533 l_mapping_set := xla_component_rec_type
534 (NULL
535 ,l_ms.mapping_set_code
536 ,l_ms.version_from
537 ,l_ms.version_to);
538 l_mapping_sets.extend;
539 l_mapping_sets(i) := l_mapping_set;
540 l_ms_codes(i) := l_ms.mapping_set_code;
541 END LOOP;
542
543 IF (C_LEVEL_EVENT >= g_log_level) THEN
544 trace(p_msg => 'END LOOP - retrieve mapping set: # retrieve = '||i,
545 p_module => l_log_module,
546 p_level => C_LEVEL_EVENT);
547 END IF;
548
549 IF (i > 0) THEN
550 INSERT INTO xla_aad_loader_logs
551 (aad_loader_log_id
552 ,amb_context_code
553 ,application_id
554 ,request_code
555 ,log_type_code
556 ,aad_application_id
557 ,component_type_code
558 ,component_code
559 ,version_from
560 ,version_to
561 ,object_version_number
562 ,creation_date
563 ,created_by
564 ,last_update_date
565 ,last_updated_by
566 ,last_update_login
567 ,program_update_date
568 ,program_application_id
569 ,program_id
570 ,request_id)
571 SELECT xla_aad_loader_logs_s.nextval
572 ,g_amb_context_code
573 ,g_application_id
574 ,'IMPORT'
575 ,'MERGED_SETUP'
576 ,d.application_id
577 ,'AMB_MS'
578 ,ms.component_code
579 ,ms.version_from
580 ,ms.version_to
581 ,1
582 ,sysdate
583 ,xla_environment_pkg.g_usr_id
584 ,sysdate
585 ,xla_environment_pkg.g_usr_id
586 ,xla_environment_pkg.g_login_id
587 ,sysdate
588 ,xla_environment_pkg.g_prog_appl_id
589 ,xla_environment_pkg.g_prog_id
590 ,xla_environment_pkg.g_req_Id
591 FROM xla_seg_rule_details d
592 ,TABLE(CAST(l_mapping_sets AS xla_component_tbl_type)) ms
593 WHERE d.amb_context_code = g_amb_context_code
594 AND d.application_id <> g_application_id
595 AND d.value_mapping_set_code = ms.component_code;
596
597 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
598 trace(p_msg => '# row inserted into xla_aad_loader_logs = '||SQL%ROWCOUNT,
599 p_module => l_log_module,
600 p_level => C_LEVEL_STATEMENT);
601 END IF;
602
603 FORALL i IN 1 .. l_ms_codes.COUNT
604 DELETE FROM xla_mapping_set_values
605 WHERE mapping_set_code = l_ms_codes(i)
606 AND amb_context_code = g_amb_context_code;
607
608 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
609 trace(p_msg => '# row deleted into xla_mapping_set_values = '||SQL%ROWCOUNT,
610 p_module => l_log_module,
611 p_level => C_LEVEL_STATEMENT);
612 END IF;
613
614 FORALL i IN 1 .. l_ms_codes.COUNT
615 DELETE FROM xla_mapping_sets_tl
616 WHERE mapping_set_code = l_ms_codes(i)
617 AND amb_context_code = g_amb_context_code;
618
619 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
620 trace(p_msg => '# row deleted into xla_mapping_sets_tl = '||SQL%ROWCOUNT,
621 p_module => l_log_module,
622 p_level => C_LEVEL_STATEMENT);
623 END IF;
624
625 FORALL i IN 1 .. l_ms_codes.COUNT
626 DELETE FROM xla_mapping_sets_b
627 WHERE mapping_set_code = l_ms_codes(i)
628 AND amb_context_code = g_amb_context_code;
629
630 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
631 trace(p_msg => '# row deleted into xla_mapping_sets_b = '||SQL%ROWCOUNT,
632 p_module => l_log_module,
633 p_level => C_LEVEL_STATEMENT);
634 END IF;
635
636 END IF;
637
638 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
639 trace(p_msg => 'END of procedure purge_mapping_sets',
640 p_module => l_log_module,
641 p_level => C_LEVEL_PROCEDURE);
642 END IF;
643
644 EXCEPTION
645 WHEN OTHERS THEN
646 xla_aad_loader_util_pvt.stack_error
647 (p_appli_s_name => 'XLA'
648 ,p_msg_name => 'XLA_COMMON_ERROR'
649 ,p_token_1 => 'LOCATION'
650 ,p_value_1 => 'xla_aad_overwrite_pvt.purge_mapping_sets'
651 ,p_token_2 => 'ERROR'
652 ,p_value_2 => 'unhandled exception');
653 RAISE;
654
655 END purge_mapping_sets;
656
657
658 --=============================================================================
659 --
660 -- Name: purge_analytical_criteria
661 -- Description: This API deletes the analytical criteria from the working area
662 -- if it exists in the working area.
663 --
664 --=============================================================================
665 PROCEDURE purge_analytical_criteria
666 IS
667 CURSOR c_ac IS
668 SELECT s.analytical_criterion_type_code
669 ,s.analytical_criterion_code
670 ,w.version_num version_from
671 ,s.version_num version_to
672 FROM xla_analytical_hdrs_b s
673 ,xla_analytical_hdrs_b w
674 WHERE s.analytical_criterion_type_code = w.analytical_criterion_type_code
675 AND s.analytical_criterion_code = w.analytical_criterion_code
676 AND s.amb_context_code = g_staging_context_code
677 AND w.amb_context_code = g_amb_context_code
678 UNION
679 SELECT w.analytical_criterion_type_code
680 ,w.analytical_criterion_code
681 ,NULL
682 ,NULL
683 FROM xla_analytical_hdrs_b w
684 WHERE w.application_id = g_application_id
685 AND w.amb_context_code = g_amb_context_code;
686
687 l_analytical_criteria xla_component_tbl_type;
688 l_analytical_criterion xla_component_rec_type;
689 l_ac_codes t_array_varchar2;
690 l_ac_type_codes t_array_varchar2;
691 i INTEGER;
692 l_log_module VARCHAR2(240);
693 BEGIN
694 IF g_log_enabled THEN
695 l_log_module := C_DEFAULT_MODULE||'.purge_analytical_criteria';
696 END IF;
697
698 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
699 trace(p_msg => 'BEGIN of procedure purge_analytical_criteria',
700 p_module => l_log_module,
701 p_level => C_LEVEL_PROCEDURE);
702 END IF;
703
704 i := 0;
705 l_analytical_criteria := xla_component_tbl_type();
706
707 IF (C_LEVEL_EVENT >= g_log_level) THEN
708 trace(p_msg => 'BEGIN LOOP - retrieve analytical criteria',
709 p_module => l_log_module,
710 p_level => C_LEVEL_EVENT);
711 END IF;
712
713 FOR l_ac IN c_ac LOOP
714 IF (C_LEVEL_EVENT >= g_log_level) THEN
715 trace(p_msg => 'LOOP - analytical criterion = '||
716 l_ac.analytical_criterion_type_code||','||
717 l_ac.analytical_criterion_code||','||
718 NVL(l_ac.version_from,'')||','||
719 NVL(l_ac.version_to,''),
720 p_module => l_log_module,
721 p_level => C_LEVEL_EVENT);
722 END IF;
723
724 i := i + 1;
725 l_analytical_criterion := xla_component_rec_type
726 (l_ac.analytical_criterion_type_code
727 ,l_ac.analytical_criterion_code
728 ,l_ac.version_from
729 ,l_ac.version_to);
730 l_analytical_criteria.extend;
731 l_analytical_criteria(i) := l_analytical_criterion;
732
733 l_ac_type_codes(i) := l_ac.analytical_criterion_type_code;
734 l_ac_codes(i) := l_ac.analytical_criterion_code;
735 END LOOP;
736
737 IF (C_LEVEL_EVENT >= g_log_level) THEN
738 trace(p_msg => 'END LOOP - retrieve analytical criteria: # retrieve = '||i,
739 p_module => l_log_module,
740 p_level => C_LEVEL_EVENT);
741 END IF;
742
743 IF (i>0) THEN
744 g_ac_updated := TRUE;
745
746 INSERT INTO xla_aad_loader_logs
747 (aad_loader_log_id
748 ,amb_context_code
749 ,application_id
750 ,request_code
751 ,log_type_code
752 ,aad_application_id
753 ,component_type_code
754 ,component_owner_code
755 ,component_code
756 ,version_from
757 ,version_to
758 ,object_version_number
759 ,creation_date
760 ,created_by
761 ,last_update_date
762 ,last_updated_by
763 ,last_update_login
764 ,program_update_date
765 ,program_application_id
766 ,program_id
767 ,request_id)
768 SELECT xla_aad_loader_logs_s.nextval
769 ,g_amb_context_code
770 ,g_application_id
771 ,'IMPORT'
772 ,'MERGED_SETUP'
773 ,application_id
774 ,'AMB_AC'
775 ,analytical_criterion_type_code
776 ,analytical_criterion_code
777 ,version_from
778 ,version_to
779 ,1
780 ,sysdate
781 ,xla_environment_pkg.g_usr_id
782 ,sysdate
783 ,xla_environment_pkg.g_usr_id
784 ,xla_environment_pkg.g_login_id
785 ,sysdate
786 ,xla_environment_pkg.g_prog_appl_id
787 ,xla_environment_pkg.g_prog_id
788 ,xla_environment_pkg.g_req_Id
789 FROM (SELECT a.application_id
790 ,a.analytical_criterion_type_code
791 ,a.analytical_criterion_code
792 ,ac.version_from
793 ,ac.version_to
794 FROM xla_aad_header_ac_assgns a
795 ,TABLE(CAST(l_analytical_criteria AS xla_component_tbl_type)) ac
796 WHERE a.amb_context_code = g_amb_context_code
797 AND a.application_id <> g_application_id
798 AND a.analytical_criterion_type_code = ac.component_owner_code
799 AND a.analytical_criterion_code = ac.component_code
800 AND ac.version_from IS NOT NULL
801 UNION
802 SELECT a.application_id
803 ,a.analytical_criterion_type_code
804 ,a.analytical_criterion_code
805 ,ac.version_from
806 ,ac.version_to
807 FROM xla_line_defn_ac_assgns a
808 ,TABLE(CAST(l_analytical_criteria AS xla_component_tbl_type)) ac
809 WHERE a.amb_context_code = g_amb_context_code
810 AND a.application_id <> g_application_id
811 AND a.analytical_criterion_type_code = ac.component_owner_code
812 AND a.analytical_criterion_code = ac.component_code
813 AND ac.version_from IS NOT NULL);
814
815 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
816 trace(p_msg => '# row insert into xla_aad_loader_logs = '||SQL%ROWCOUNT,
817 p_module => l_log_module,
818 p_level => C_LEVEL_STATEMENT);
819 END IF;
820
821 FORALL i IN 1 .. l_ac_codes.COUNT
822 DELETE FROM xla_analytical_sources
823 WHERE analytical_criterion_type_code = l_ac_type_codes(i)
824 AND analytical_criterion_code = l_ac_codes(i)
825 AND amb_context_code = g_amb_context_code;
826
827 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
828 trace(p_msg => '# row deleted into xla_analytical_sources = '||SQL%ROWCOUNT,
829 p_module => l_log_module,
830 p_level => C_LEVEL_STATEMENT);
831 END IF;
832
833 FORALL i IN 1 .. l_ac_codes.COUNT
834 DELETE FROM xla_analytical_dtls_tl
835 WHERE analytical_criterion_type_code = l_ac_type_codes(i)
836 AND analytical_criterion_code = l_ac_codes(i)
837 AND amb_context_code = g_amb_context_code;
838
839 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
840 trace(p_msg => '# row deleted into xla_analytical_dtls_tl = '||SQL%ROWCOUNT,
841 p_module => l_log_module,
842 p_level => C_LEVEL_STATEMENT);
843 END IF;
844
845 FORALL i IN 1 .. l_ac_codes.COUNT
846 DELETE FROM xla_analytical_dtls_b
847 WHERE analytical_criterion_type_code = l_ac_type_codes(i)
848 AND analytical_criterion_code = l_ac_codes(i)
849 AND amb_context_code = g_amb_context_code;
850
851 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
852 trace(p_msg => '# row deleted into xla_analytical_dtls_b = '||SQL%ROWCOUNT,
853 p_module => l_log_module,
854 p_level => C_LEVEL_STATEMENT);
855 END IF;
856
857 FORALL i IN 1 .. l_ac_codes.COUNT
858 DELETE FROM xla_analytical_hdrs_tl
859 WHERE analytical_criterion_type_code = l_ac_type_codes(i)
860 AND analytical_criterion_code = l_ac_codes(i)
861 AND amb_context_code = g_amb_context_code;
862
863 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
864 trace(p_msg => '# row deleted into xla_analytical_hdrs_tl = '||SQL%ROWCOUNT,
865 p_module => l_log_module,
866 p_level => C_LEVEL_STATEMENT);
867 END IF;
868
869 FORALL i IN 1 .. l_ac_codes.COUNT
870 DELETE FROM xla_analytical_hdrs_b
871 WHERE analytical_criterion_type_code = l_ac_type_codes(i)
872 AND analytical_criterion_code = l_ac_codes(i)
873 AND amb_context_code = g_amb_context_code;
874
875 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
876 trace(p_msg => '# row deleted into xla_analytical_hdrs_b = '||SQL%ROWCOUNT,
877 p_module => l_log_module,
878 p_level => C_LEVEL_STATEMENT);
879 END IF;
880
881 END IF;
882
883 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
884 trace(p_msg => 'END of procedure purge_analytical_criteria',
885 p_module => l_log_module,
886 p_level => C_LEVEL_PROCEDURE);
887 END IF;
888
889 EXCEPTION
890 WHEN OTHERS THEN
891 xla_aad_loader_util_pvt.stack_error
892 (p_appli_s_name => 'XLA'
893 ,p_msg_name => 'XLA_COMMON_ERROR'
894 ,p_token_1 => 'LOCATION'
895 ,p_value_1 => 'xla_aad_overwrite_pvt.purge_analytical_criteria'
896 ,p_token_2 => 'ERROR'
897 ,p_value_2 => 'unhandled exception');
898 RAISE;
899
900 END purge_analytical_criteria;
901
902 --=============================================================================
903 --
904 -- Name: purge_adr
905 -- Description: This API deletes the ADRs from the working area
906 -- if it exists in the working area.
907 --
908 --=============================================================================
909 PROCEDURE purge_adr
910 IS
911 CURSOR c_adr IS
912 SELECT s.segment_rule_type_code
913 ,s.segment_rule_code
914 ,w.version_num version_from
915 ,s.version_num version_to
916 FROM xla_seg_rules_b s
917 ,xla_seg_rules_b w
918 WHERE s.application_id = w.application_id
919 AND s.segment_rule_type_code = w.segment_rule_type_code
920 AND s.segment_rule_code = w.segment_rule_code
921 AND s.amb_context_code = g_staging_context_code
922 AND w.amb_context_code = g_amb_context_code
923 UNION
924 SELECT w.segment_rule_type_code
925 ,w.segment_rule_code
926 ,NULL
927 ,NULL
928 FROM xla_seg_rules_b w
929 WHERE w.application_id = g_application_id
930 AND w.amb_context_code = g_amb_context_code;
931
932 l_adrs xla_component_tbl_type;
933 l_adr xla_component_rec_type;
934 l_adr_codes t_array_varchar2;
935 l_adr_type_codes t_array_varchar2;
936 i INTEGER;
937 l_log_module VARCHAR2(240);
938 BEGIN
939 IF g_log_enabled THEN
940 l_log_module := C_DEFAULT_MODULE||'.purge_adr';
941 END IF;
942
943 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
944 trace(p_msg => 'BEGIN of procedure purge_adr',
945 p_module => l_log_module,
946 p_level => C_LEVEL_PROCEDURE);
947 END IF;
948
949 i := 0;
950 l_adrs := xla_component_tbl_type();
951
952 IF (C_LEVEL_EVENT >= g_log_level) THEN
953 trace(p_msg => 'BEGIN LOOP - retrieve ADR',
954 p_module => l_log_module,
955 p_level => C_LEVEL_EVENT);
956 END IF;
957
958 FOR l_comp IN c_adr LOOP
959 IF (C_LEVEL_EVENT >= g_log_level) THEN
960 trace(p_msg => 'LOOP - ADR = '||
961 l_comp.segment_rule_type_code||','||
962 l_comp.segment_rule_code||','||
963 NVL(l_comp.version_from,'')||','||
964 NVL(l_comp.version_to,''),
965 p_module => l_log_module,
966 p_level => C_LEVEL_EVENT);
967 END IF;
968
969 i := i + 1;
970 l_adr := xla_component_rec_type
971 (l_comp.segment_rule_type_code
972 ,l_comp.segment_rule_code
973 ,l_comp.version_from
974 ,l_comp.version_to);
975 l_adrs.extend;
976 l_adrs(i) := l_adr;
977
978 l_adr_type_codes(i) := l_comp.segment_rule_type_code;
979 l_adr_codes(i) := l_comp.segment_rule_code;
980 END LOOP;
981
982 IF (C_LEVEL_EVENT >= g_log_level) THEN
983 trace(p_msg => 'END LOOP - retrieve ADRs: # retrieve = '||i,
984 p_module => l_log_module,
985 p_level => C_LEVEL_EVENT);
986 END IF;
987
988 IF (i>0) THEN
989 INSERT INTO xla_aad_loader_logs
990 (aad_loader_log_id
991 ,amb_context_code
992 ,application_id
993 ,request_code
994 ,log_type_code
995 ,aad_application_id
996 ,component_type_code
997 ,component_owner_code
998 ,component_code
999 ,version_from
1000 ,version_to
1001 ,object_version_number
1002 ,creation_date
1003 ,created_by
1004 ,last_update_date
1005 ,last_updated_by
1006 ,last_update_login
1007 ,program_update_date
1008 ,program_application_id
1009 ,program_id
1010 ,request_id)
1011 SELECT xla_aad_loader_logs_s.nextval
1012 ,g_amb_context_code
1013 ,g_application_id
1014 ,'IMPORT'
1015 ,'MERGED_SETUP'
1016 ,application_id
1017 ,'AMB_ADR'
1018 ,segment_rule_type_code
1019 ,segment_rule_code
1020 ,version_from
1021 ,version_to
1022 ,1
1023 ,sysdate
1024 ,xla_environment_pkg.g_usr_id
1025 ,sysdate
1026 ,xla_environment_pkg.g_usr_id
1027 ,xla_environment_pkg.g_login_id
1028 ,sysdate
1029 ,xla_environment_pkg.g_prog_appl_id
1030 ,xla_environment_pkg.g_prog_id
1031 ,xla_environment_pkg.g_req_Id
1032 FROM (SELECT a.application_id
1033 ,a.segment_rule_type_code
1034 ,a.segment_rule_code
1035 ,adr.version_from
1036 ,adr.version_to
1037 FROM xla_line_defn_adr_assgns a
1038 ,TABLE(CAST(l_adrs AS xla_component_tbl_type)) adr
1039 WHERE a.amb_context_code = g_amb_context_code
1040 AND a.application_id <> g_application_id
1041 AND a.segment_rule_appl_id = g_application_id
1042 AND a.segment_rule_type_code = adr.component_owner_code
1043 AND a.segment_rule_code = adr.component_code
1044 AND adr.version_from IS NOT NULL
1045 UNION
1046 SELECT a.application_id
1047 ,a.segment_rule_type_code
1048 ,a.segment_rule_code
1049 ,adr.version_from
1050 ,adr.version_to
1051 FROM xla_seg_rule_details a
1052 ,TABLE(CAST(l_adrs AS xla_component_tbl_type)) adr
1053 WHERE a.amb_context_code = g_amb_context_code
1054 AND a.application_id <> g_application_id
1055 AND a.value_segment_rule_appl_id = g_application_id
1056 AND a.value_segment_rule_type_code = adr.component_owner_code
1057 AND a.value_segment_rule_code = adr.component_code
1058 AND adr.version_from IS NOT NULL);
1059
1060 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1061 trace(p_msg => '# row insert into xla_aad_loader_logs = '||SQL%ROWCOUNT,
1062 p_module => l_log_module,
1063 p_level => C_LEVEL_STATEMENT);
1064 END IF;
1065
1066 FORALL i IN 1 .. l_adr_codes.COUNT
1067 DELETE FROM xla_conditions
1068 WHERE segment_rule_detail_id IN
1069 (SELECT segment_rule_detail_id
1070 FROM xla_seg_rule_details
1071 WHERE application_id = g_application_id
1072 AND amb_context_code = g_amb_context_code
1073 AND segment_rule_type_code = l_adr_type_codes(i)
1074 AND segment_rule_code = l_adr_codes(i));
1075
1076 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1077 trace(p_msg => '# row deleted into xla_conditions = '||SQL%ROWCOUNT,
1078 p_module => l_log_module,
1079 p_level => C_LEVEL_STATEMENT);
1080 END IF;
1081
1082 FORALL i IN 1 .. l_adr_codes.COUNT
1083 DELETE FROM xla_seg_rule_details
1084 WHERE application_id = g_application_id
1085 AND amb_context_code = g_amb_context_code
1086 AND segment_rule_type_code = l_adr_type_codes(i)
1087 AND segment_rule_code = l_adr_codes(i);
1088
1089 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1090 trace(p_msg => '# row deleted into xla_seg_rule_details = '||SQL%ROWCOUNT,
1091 p_module => l_log_module,
1092 p_level => C_LEVEL_STATEMENT);
1093 END IF;
1094
1095 FORALL i IN 1 .. l_adr_codes.COUNT
1096 DELETE FROM xla_seg_rules_tl
1097 WHERE application_id = g_application_id
1098 AND amb_context_code = g_amb_context_code
1099 AND segment_rule_type_code = l_adr_type_codes(i)
1100 AND segment_rule_code = l_adr_codes(i);
1101
1102 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1103 trace(p_msg => '# row deleted into xla_seg_rules_tl = '||SQL%ROWCOUNT,
1104 p_module => l_log_module,
1105 p_level => C_LEVEL_STATEMENT);
1106 END IF;
1107
1108 FORALL i IN 1 .. l_adr_codes.COUNT
1109 DELETE FROM xla_seg_rules_b
1110 WHERE application_id = g_application_id
1111 AND amb_context_code = g_amb_context_code
1112 AND segment_rule_type_code = l_adr_type_codes(i)
1113 AND segment_rule_code = l_adr_codes(i);
1114
1115 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1116 trace(p_msg => '# row deleted into xla_seg_rules_b = '||SQL%ROWCOUNT,
1117 p_module => l_log_module,
1118 p_level => C_LEVEL_STATEMENT);
1119 END IF;
1120
1121 END IF;
1122
1123 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1124 trace(p_msg => 'END of procedure purge_adr',
1125 p_module => l_log_module,
1126 p_level => C_LEVEL_PROCEDURE);
1127 END IF;
1128
1129 EXCEPTION
1130 WHEN OTHERS THEN
1131 xla_aad_loader_util_pvt.stack_error
1132 (p_appli_s_name => 'XLA'
1133 ,p_msg_name => 'XLA_COMMON_ERROR'
1134 ,p_token_1 => 'LOCATION'
1135 ,p_value_1 => 'xla_aad_overwrite_pvt.purge_adr'
1136 ,p_token_2 => 'ERROR'
1137 ,p_value_2 => 'unhandled exception');
1138 RAISE;
1139
1140 END purge_adr;
1141
1142 --=============================================================================
1143 --
1144 -- Name: purge_adr_reference
1145 -- Description: This API deletes any reference to the ADR that no longer exist
1146 -- in the staging area
1147 --=============================================================================
1148 PROCEDURE purge_adr_reference
1149 IS
1150 l_count INTEGER;
1151 l_log_module VARCHAR2(240);
1152 BEGIN
1153 IF g_log_enabled THEN
1154 l_log_module := C_DEFAULT_MODULE||'.purge_adr_reference';
1155 END IF;
1156
1157 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1158 trace(p_msg => 'BEGIN of procedure purge_adr_reference',
1159 p_module => l_log_module,
1160 p_level => C_LEVEL_PROCEDURE);
1161 END IF;
1162
1163 INSERT INTO xla_aad_loader_logs
1164 (aad_loader_log_id
1165 ,amb_context_code
1166 ,application_id
1167 ,request_code
1168 ,log_type_code
1169 ,aad_application_id
1170 ,component_type_code
1171 ,component_owner_code
1172 ,component_code
1173 ,object_version_number
1174 ,creation_date
1175 ,created_by
1176 ,last_update_date
1177 ,last_updated_by
1178 ,last_update_login
1179 ,program_update_date
1180 ,program_application_id
1181 ,program_id
1182 ,request_id)
1183 SELECT xla_aad_loader_logs_s.nextval
1184 ,g_amb_context_code
1185 ,g_application_id
1186 ,'IMPORT'
1187 ,'DELETED_SETUP'
1188 ,application_id
1189 ,'AMB_ADR'
1190 ,segment_rule_type_code
1191 ,segment_rule_code
1192 ,1
1193 ,sysdate
1194 ,xla_environment_pkg.g_usr_id
1195 ,sysdate
1196 ,xla_environment_pkg.g_usr_id
1197 ,xla_environment_pkg.g_login_id
1198 ,sysdate
1199 ,xla_environment_pkg.g_prog_appl_id
1200 ,xla_environment_pkg.g_prog_id
1201 ,xla_environment_pkg.g_req_Id
1202 FROM xla_line_defn_adr_assgns xld
1203 WHERE xld.amb_context_code = g_amb_context_code
1204 AND xld.application_id <> g_application_id
1205 AND xld.segment_rule_appl_id = g_application_id
1206 AND NOT EXISTS (SELECT 1
1207 FROM xla_seg_rules_b s
1208 WHERE s.amb_context_code = g_staging_context_code
1209 AND s.application_id = xld.segment_rule_appl_id
1210 AND s.segment_rule_type_code = xld.segment_rule_type_code
1211 AND s.segment_rule_code = xld.segment_rule_code);
1212
1213 l_count := SQL%ROWCOUNT;
1214 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1215 trace(p_msg => '# row inserted to xla_aad_loader_log = '||l_count,
1216 p_module => l_log_module,
1217 p_level => C_LEVEL_PROCEDURE);
1218 END IF;
1219
1220 IF (l_count > 0) THEN
1221 DELETE FROM xla_line_defn_adr_assgns xld
1222 WHERE xld.amb_context_code = g_amb_context_code
1223 AND xld.segment_rule_appl_id = g_application_id
1224 AND NOT EXISTS (SELECT 1
1225 FROM xla_seg_rules_b s
1226 WHERE s.amb_context_code = g_staging_context_code
1227 AND s.application_id = xld.segment_rule_appl_id
1228 AND s.segment_rule_type_code = xld.segment_rule_type_code
1229 AND s.segment_rule_code = xld.segment_rule_code);
1230
1231 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1232 trace(p_msg => '# row inserted to xla_aad_loader_log = '||SQL%ROWCOUNT,
1233 p_module => l_log_module,
1234 p_level => C_LEVEL_PROCEDURE);
1235 END IF;
1236 END IF;
1237
1238 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1239 trace(p_msg => 'END of procedure purge_adr_reference',
1240 p_module => l_log_module,
1241 p_level => C_LEVEL_PROCEDURE);
1242 END IF;
1243
1244 EXCEPTION
1245 WHEN OTHERS THEN
1246 xla_aad_loader_util_pvt.stack_error
1247 (p_appli_s_name => 'XLA'
1248 ,p_msg_name => 'XLA_COMMON_ERROR'
1249 ,p_token_1 => 'LOCATION'
1250 ,p_value_1 => 'xla_aad_overwrite_pvt.purge_adr_reference'
1251 ,p_token_2 => 'ERROR'
1252 ,p_value_2 => 'unhandled exception');
1253 RAISE;
1254
1255 END purge_adr_reference;
1256
1257
1258 --=============================================================================
1259 --
1260 -- Name: move_components
1261 -- Description: This API moves the different components from staging to working
1262 -- area.
1263 --
1264 --=============================================================================
1265 PROCEDURE move_components
1266 IS
1267 l_count INTEGER;
1268 l_log_module VARCHAR2(240);
1269 BEGIN
1270 IF g_log_enabled THEN
1271 l_log_module := C_DEFAULT_MODULE||'.move_components';
1272 END IF;
1273
1274 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1275 trace(p_msg => 'BEGIN of procedure move_components',
1276 p_module => l_log_module,
1277 p_level => C_LEVEL_PROCEDURE);
1278 END IF;
1279
1280 -- Move journal line types
1281 UPDATE xla_acct_line_types_b
1282 SET amb_context_code = g_amb_context_code
1283 WHERE amb_context_code = g_staging_context_code;
1284
1285 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1286 trace(p_msg => '# row moved in xla_acct_line_types_b = '||SQL%ROWCOUNT,
1287 p_module => l_log_module,
1288 p_level => C_LEVEL_STATEMENT);
1289 END IF;
1290
1291 UPDATE xla_acct_line_types_tl
1292 SET amb_context_code = g_amb_context_code
1293 WHERE amb_context_code = g_staging_context_code;
1294
1295 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1296 trace(p_msg => '# row moved in xla_acct_line_types_tl = '||SQL%ROWCOUNT,
1297 p_module => l_log_module,
1298 p_level => C_LEVEL_STATEMENT);
1299 END IF;
1300
1301 UPDATE xla_jlt_acct_attrs
1302 SET amb_context_code = g_amb_context_code
1303 WHERE amb_context_code = g_staging_context_code;
1304
1305 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1306 trace(p_msg => '# row moved in xla_jlt_acct_attrs = '||SQL%ROWCOUNT,
1307 p_module => l_log_module,
1308 p_level => C_LEVEL_STATEMENT);
1309 END IF;
1310
1311 -- Move journal entry descriptions
1312 UPDATE xla_descriptions_b
1313 SET amb_context_code = g_amb_context_code
1314 WHERE amb_context_code = g_staging_context_code;
1315
1316 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1317 trace(p_msg => '# row moved in xla_descriptions_b = '||SQL%ROWCOUNT,
1318 p_module => l_log_module,
1319 p_level => C_LEVEL_STATEMENT);
1320 END IF;
1321
1322 UPDATE xla_descriptions_tl
1323 SET amb_context_code = g_amb_context_code
1324 WHERE amb_context_code = g_staging_context_code;
1325
1326 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1327 trace(p_msg => '# row moved in xla_descriptions_tl = '||SQL%ROWCOUNT,
1328 p_module => l_log_module,
1329 p_level => C_LEVEL_STATEMENT);
1330 END IF;
1331
1332 UPDATE xla_desc_priorities
1333 SET amb_context_code = g_amb_context_code
1334 WHERE amb_context_code = g_staging_context_code;
1335
1336 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1337 trace(p_msg => '# row moved in xla_desc_priorities = '||SQL%ROWCOUNT,
1338 p_module => l_log_module,
1339 p_level => C_LEVEL_STATEMENT);
1340 END IF;
1341
1342 UPDATE xla_descript_details_b
1343 SET amb_context_code = g_amb_context_code
1344 WHERE amb_context_code = g_staging_context_code;
1345
1346 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1347 trace(p_msg => '# row moved in xla_descript_details_b = '||SQL%ROWCOUNT,
1348 p_module => l_log_module,
1349 p_level => C_LEVEL_STATEMENT);
1350 END IF;
1351
1352 UPDATE xla_descript_details_tl
1353 SET amb_context_code = g_amb_context_code
1354 WHERE amb_context_code = g_staging_context_code;
1355
1356 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1357 trace(p_msg => '# row moved in xla_descript_details_tl = '||SQL%ROWCOUNT,
1358 p_module => l_log_module,
1359 p_level => C_LEVEL_STATEMENT);
1360 END IF;
1361
1362 -- Move account derivation rules
1363 UPDATE xla_seg_rules_b
1364 SET amb_context_code = g_amb_context_code
1365 WHERE amb_context_code = g_staging_context_code;
1366
1367 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1368 trace(p_msg => '# row moved in xla_seg_rules_b = '||SQL%ROWCOUNT,
1369 p_module => l_log_module,
1370 p_level => C_LEVEL_STATEMENT);
1371 END IF;
1372
1373 UPDATE xla_seg_rules_tl
1374 SET amb_context_code = g_amb_context_code
1375 WHERE amb_context_code = g_staging_context_code;
1376
1377 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1378 trace(p_msg => '# row moved in xla_seg_rules_tl = '||SQL%ROWCOUNT,
1379 p_module => l_log_module,
1380 p_level => C_LEVEL_STATEMENT);
1381 END IF;
1382
1383 UPDATE xla_seg_rule_details
1384 SET amb_context_code = g_amb_context_code
1385 WHERE amb_context_code = g_staging_context_code;
1386
1387 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1388 trace(p_msg => '# row moved in xla_seg_rule_details = '||SQL%ROWCOUNT,
1389 p_module => l_log_module,
1390 p_level => C_LEVEL_STATEMENT);
1391 END IF;
1392
1393 -- Move mapping sets
1394 UPDATE xla_mapping_sets_b
1395 SET amb_context_code = g_amb_context_code
1396 WHERE amb_context_code = g_staging_context_code;
1397
1398 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1399 trace(p_msg => '# row moved in xla_mapping_sets_b = '||SQL%ROWCOUNT,
1400 p_module => l_log_module,
1401 p_level => C_LEVEL_STATEMENT);
1402 END IF;
1403
1404 UPDATE xla_mapping_sets_tl
1405 SET amb_context_code = g_amb_context_code
1406 WHERE amb_context_code = g_staging_context_code;
1407
1408 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1409 trace(p_msg => '# row moved in xla_mapping_sets_tl = '||SQL%ROWCOUNT,
1410 p_module => l_log_module,
1411 p_level => C_LEVEL_STATEMENT);
1412 END IF;
1413
1414 UPDATE xla_mapping_set_values
1415 SET amb_context_code = g_amb_context_code
1416 WHERE amb_context_code = g_staging_context_code;
1417
1418 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1419 trace(p_msg => '# row moved in xla_mapping_set_values = '||SQL%ROWCOUNT,
1420 p_module => l_log_module,
1421 p_level => C_LEVEL_STATEMENT);
1422 END IF;
1423
1424 -- Move analytical criteria
1425 UPDATE xla_analytical_hdrs_b
1426 SET amb_context_code = g_amb_context_code
1427 WHERE amb_context_code = g_staging_context_code;
1428
1429 l_count := SQL%ROWCOUNT;
1430 IF (l_count > 0) THEN
1431 g_ac_updated := TRUE;
1432 END IF;
1433
1434 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1435 trace(p_msg => '# row moved in xla_analytical_hdrs_b = '||l_count,
1436 p_module => l_log_module,
1437 p_level => C_LEVEL_STATEMENT);
1438 END IF;
1439
1440 UPDATE xla_analytical_hdrs_tl
1441 SET amb_context_code = g_amb_context_code
1442 WHERE amb_context_code = g_staging_context_code;
1443
1444 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1445 trace(p_msg => '# row moved in xla_analytical_hdrs_tl = '||SQL%ROWCOUNT,
1446 p_module => l_log_module,
1447 p_level => C_LEVEL_STATEMENT);
1448 END IF;
1449
1450 UPDATE xla_analytical_dtls_b
1451 SET amb_context_code = g_amb_context_code
1452 WHERE amb_context_code = g_staging_context_code;
1453
1454 l_count := SQL%ROWCOUNT;
1455 IF (l_count > 0) THEN
1456 g_ac_updated := TRUE;
1457 END IF;
1458
1459 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1460 trace(p_msg => '# row moved in xla_analytical_dtls_b = '||l_count,
1461 p_module => l_log_module,
1462 p_level => C_LEVEL_STATEMENT);
1463 END IF;
1464
1465 UPDATE xla_analytical_dtls_tl
1466 SET amb_context_code = g_amb_context_code
1467 WHERE amb_context_code = g_staging_context_code;
1468
1469 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1470 trace(p_msg => '# row moved in xla_analytical_dtls_tl = '||SQL%ROWCOUNT,
1471 p_module => l_log_module,
1472 p_level => C_LEVEL_STATEMENT);
1473 END IF;
1474
1475 UPDATE xla_analytical_sources
1476 SET amb_context_code = g_amb_context_code
1477 WHERE amb_context_code = g_staging_context_code;
1478
1479 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1480 trace(p_msg => '# row moved in xla_analytical_sources = '||SQL%ROWCOUNT,
1481 p_module => l_log_module,
1482 p_level => C_LEVEL_STATEMENT);
1483 END IF;
1484
1485 -- Move conditions
1486 UPDATE xla_conditions
1487 SET amb_context_code = g_amb_context_code
1488 WHERE amb_context_code = g_staging_context_code;
1489
1490 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1491 trace(p_msg => '# row moved in xla_conditions = '||SQL%ROWCOUNT,
1492 p_module => l_log_module,
1493 p_level => C_LEVEL_STATEMENT);
1494 END IF;
1495
1496 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1497 trace(p_msg => 'END of procedure move_components',
1498 p_module => l_log_module,
1499 p_level => C_LEVEL_PROCEDURE);
1500 END IF;
1501
1502 EXCEPTION
1503 WHEN OTHERS THEN
1504 xla_aad_loader_util_pvt.stack_error
1505 (p_appli_s_name => 'XLA'
1506 ,p_msg_name => 'XLA_COMMON_ERROR'
1507 ,p_token_1 => 'LOCATION'
1508 ,p_value_1 => 'xla_aad_overwrite_pvt.move_components'
1509 ,p_token_2 => 'ERROR'
1510 ,p_value_2 => 'unhandled exception');
1511 RAISE;
1512
1513 END move_components;
1514
1515 --=============================================================================
1516 --
1517 -- Name: move_jlds
1518 -- Description: This API moves the JLDs and its assignments from staging to
1519 -- working area
1520 --
1521 --=============================================================================
1522 PROCEDURE move_jlds
1523 IS
1524 l_log_module VARCHAR2(240);
1525 BEGIN
1526 IF g_log_enabled THEN
1527 l_log_module := C_DEFAULT_MODULE||'.move_jlds';
1528 END IF;
1529
1530 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1531 trace(p_msg => 'BEGIN of procedure move_jlds',
1532 p_module => l_log_module,
1533 p_level => C_LEVEL_PROCEDURE);
1534 END IF;
1535
1536 UPDATE xla_line_definitions_b
1537 SET amb_context_code = g_amb_context_code
1538 WHERE amb_context_code = g_staging_context_code;
1539
1540 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1541 trace(p_msg => '# row moved in xla_line_definitions_b = '||SQL%ROWCOUNT,
1542 p_module => l_log_module,
1543 p_level => C_LEVEL_STATEMENT);
1544 END IF;
1545
1546 UPDATE xla_line_definitions_tl
1547 SET amb_context_code = g_amb_context_code
1548 WHERE amb_context_code = g_staging_context_code;
1549
1550 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1551 trace(p_msg => '# row moved in xla_line_definitions_tl = '||SQL%ROWCOUNT,
1552 p_module => l_log_module,
1553 p_level => C_LEVEL_STATEMENT);
1554 END IF;
1555
1556 UPDATE xla_line_defn_jlt_assgns
1557 SET amb_context_code = g_amb_context_code
1558 WHERE amb_context_code = g_staging_context_code;
1559
1560 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1561 trace(p_msg => '# row moved in xla_line_defn_jlt_assgns = '||SQL%ROWCOUNT,
1562 p_module => l_log_module,
1563 p_level => C_LEVEL_STATEMENT);
1564 END IF;
1565
1566 UPDATE xla_line_defn_adr_assgns
1567 SET amb_context_code = g_amb_context_code
1568 WHERE amb_context_code = g_staging_context_code;
1569
1570 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1571 trace(p_msg => '# row moved in xla_line_defn_adr_assgns = '||SQL%ROWCOUNT,
1572 p_module => l_log_module,
1573 p_level => C_LEVEL_STATEMENT);
1574 END IF;
1575
1576 UPDATE xla_line_defn_ac_assgns
1577 SET amb_context_code = g_amb_context_code
1578 WHERE amb_context_code = g_staging_context_code;
1579
1580 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1581 trace(p_msg => '# row moved in xla_line_defn_ac_assgns = '||SQL%ROWCOUNT,
1582 p_module => l_log_module,
1583 p_level => C_LEVEL_STATEMENT);
1584 END IF;
1585
1586 UPDATE xla_mpa_jlt_assgns
1587 SET amb_context_code = g_amb_context_code
1588 WHERE amb_context_code = g_staging_context_code;
1589
1590 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1591 trace(p_msg => '# row moved in xla_mpa_jlt_assgns = '||SQL%ROWCOUNT,
1592 p_module => l_log_module,
1593 p_level => C_LEVEL_STATEMENT);
1594 END IF;
1595
1596 UPDATE xla_mpa_header_ac_assgns
1597 SET amb_context_code = g_amb_context_code
1598 WHERE amb_context_code = g_staging_context_code;
1599
1600 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1601 trace(p_msg => '# row moved in xla_mpa_header_ac_assgns = '||SQL%ROWCOUNT,
1602 p_module => l_log_module,
1603 p_level => C_LEVEL_STATEMENT);
1604 END IF;
1605
1606 UPDATE xla_mpa_jlt_adr_assgns
1607 SET amb_context_code = g_amb_context_code
1608 WHERE amb_context_code = g_staging_context_code;
1609
1610 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1611 trace(p_msg => '# row moved in xla_mpa_jlt_adr_assgns = '||SQL%ROWCOUNT,
1612 p_module => l_log_module,
1613 p_level => C_LEVEL_STATEMENT);
1614 END IF;
1615
1616 UPDATE xla_mpa_jlt_ac_assgns
1617 SET amb_context_code = g_amb_context_code
1618 WHERE amb_context_code = g_staging_context_code;
1619
1620 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1621 trace(p_msg => '# row moved in xla_mpa_jlt_ac_assgns = '||SQL%ROWCOUNT,
1622 p_module => l_log_module,
1623 p_level => C_LEVEL_STATEMENT);
1624 END IF;
1625
1626 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1627 trace(p_msg => 'END of procedure move_jlds',
1628 p_module => l_log_module,
1629 p_level => C_LEVEL_PROCEDURE);
1630 END IF;
1631
1632 EXCEPTION
1633 WHEN OTHERS THEN
1634 xla_aad_loader_util_pvt.stack_error
1635 (p_appli_s_name => 'XLA'
1636 ,p_msg_name => 'XLA_COMMON_ERROR'
1637 ,p_token_1 => 'LOCATION'
1638 ,p_value_1 => 'xla_aad_overwrite_pvt.move_jlds'
1639 ,p_token_2 => 'ERROR'
1640 ,p_value_2 => 'unhandled exception');
1641 RAISE;
1642
1643 END move_jlds;
1644
1645
1646 --=============================================================================
1647 --
1648 -- Name: move_aads
1649 -- Description: This API moves the AADs and its assignments from staging to
1650 -- working area
1651 --
1652 --=============================================================================
1653 PROCEDURE move_aads
1654 IS
1655 l_log_module VARCHAR2(240);
1656 BEGIN
1657 IF g_log_enabled THEN
1658 l_log_module := C_DEFAULT_MODULE||'.move_aads';
1659 END IF;
1660
1661 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1662 trace(p_msg => 'BEGIN of procedure move_aads',
1663 p_module => l_log_module,
1664 p_level => C_LEVEL_PROCEDURE);
1665 END IF;
1666
1667 -- Move accounting definitions
1668 UPDATE xla_product_rules_b
1669 SET amb_context_code = g_amb_context_code
1670 WHERE amb_context_code = g_staging_context_code;
1671
1672 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1673 trace(p_msg => '# row moved in xla_product_rules_b = '||SQL%ROWCOUNT,
1674 p_module => l_log_module,
1675 p_level => C_LEVEL_STATEMENT);
1676 END IF;
1677
1678 UPDATE xla_product_rules_tl
1679 SET amb_context_code = g_amb_context_code
1680 WHERE amb_context_code = g_staging_context_code;
1681
1682 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1683 trace(p_msg => '# row moved in xla_product_rules_tl = '||SQL%ROWCOUNT,
1684 p_module => l_log_module,
1685 p_level => C_LEVEL_STATEMENT);
1686 END IF;
1687
1688 -- Move header assignment
1689 UPDATE xla_prod_acct_headers
1690 SET amb_context_code = g_amb_context_code
1691 WHERE amb_context_code = g_staging_context_code;
1692
1693 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1694 trace(p_msg => '# row moved in xla_prod_acct_headers = '||SQL%ROWCOUNT,
1695 p_module => l_log_module,
1696 p_level => C_LEVEL_STATEMENT);
1697 END IF;
1698
1699 UPDATE xla_aad_hdr_acct_attrs
1700 SET amb_context_code = g_amb_context_code
1701 WHERE amb_context_code = g_staging_context_code;
1702
1703 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1704 trace(p_msg => '# row moved in xla_aad_hdr_acct_attrs = '||SQL%ROWCOUNT,
1705 p_module => l_log_module,
1706 p_level => C_LEVEL_STATEMENT);
1707 END IF;
1708
1709 UPDATE xla_aad_header_ac_assgns
1710 SET amb_context_code = g_amb_context_code
1711 WHERE amb_context_code = g_staging_context_code;
1712
1713 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1714 trace(p_msg => '# row moved in xla_aad_header_ac_assgns = '||SQL%ROWCOUNT,
1715 p_module => l_log_module,
1716 p_level => C_LEVEL_STATEMENT);
1717 END IF;
1718
1719 UPDATE xla_aad_line_defn_assgns
1720 SET amb_context_code = g_amb_context_code
1721 WHERE amb_context_code = g_staging_context_code;
1722
1723 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1724 trace(p_msg => '# row moved in xla_aad_line_defn_assgns = '||SQL%ROWCOUNT,
1725 p_module => l_log_module,
1726 p_level => C_LEVEL_STATEMENT);
1727 END IF;
1728
1729 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1730 trace(p_msg => 'END of procedure move_aads',
1731 p_module => l_log_module,
1732 p_level => C_LEVEL_PROCEDURE);
1733 END IF;
1734
1735 EXCEPTION
1736 WHEN OTHERS THEN
1737 xla_aad_loader_util_pvt.stack_error
1738 (p_appli_s_name => 'XLA'
1739 ,p_msg_name => 'XLA_COMMON_ERROR'
1740 ,p_token_1 => 'LOCATION'
1741 ,p_value_1 => 'xla_aad_overwrite_pvt.move_aads'
1742 ,p_token_2 => 'ERROR'
1743 ,p_value_2 => 'unhandled exception');
1744 RAISE;
1745
1746 END move_aads;
1747
1748 --=============================================================================
1749 --
1750 -- Name: move_acctg_methods
1751 -- Description: This API copies the accounting methods from the staging to the
1752 -- working area if not already exists. Then it moves the
1753 -- accounting method rules from the staging to the working area.
1754 --
1755 --=============================================================================
1756 PROCEDURE move_acctg_methods
1757 IS
1758 l_log_module VARCHAR2(240);
1759 BEGIN
1760 IF g_log_enabled THEN
1761 l_log_module := C_DEFAULT_MODULE||'.move_acctg_methods';
1762 END IF;
1763
1764 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1765 trace(p_msg => 'BEGIN of procedure move_acctg_methods',
1766 p_module => l_log_module,
1767 p_level => C_LEVEL_PROCEDURE);
1768 END IF;
1769
1770 INSERT INTO xla_acctg_methods_b
1771 (accounting_method_type_code
1772 ,accounting_method_code
1773 ,transaction_coa_id
1774 ,accounting_coa_id
1775 ,enabled_flag
1776 ,creation_date
1777 ,created_by
1778 ,last_update_date
1779 ,last_updated_by
1780 ,last_update_login)
1781 SELECT
1782 s.accounting_method_type_code
1783 ,s.accounting_method_code
1784 ,s.transaction_coa_id
1785 ,s.accounting_coa_id
1786 ,s.enabled_flag
1787 ,sysdate
1788 ,xla_environment_pkg.g_usr_id
1789 ,sysdate
1790 ,xla_environment_pkg.g_usr_id
1791 ,xla_environment_pkg.g_login_id
1792 FROM xla_stage_acctg_methods s
1793 LEFT OUTER JOIN xla_acctg_methods_b w
1794 ON w.accounting_method_type_code = s.accounting_method_type_code
1795 AND w.accounting_method_code = s.accounting_method_code
1796 WHERE s.staging_amb_context_code = g_staging_context_code
1797 AND w.accounting_method_type_code IS NULL;
1798
1799 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1800 trace(p_msg => '# row inserted in xla_acctg_methods_b = '||SQL%ROWCOUNT,
1801 p_module => l_log_module,
1802 p_level => C_LEVEL_STATEMENT);
1803 END IF;
1804
1805 INSERT INTO xla_acctg_methods_tl
1806 (accounting_method_type_code
1807 ,accounting_method_code
1808 ,language
1809 ,name
1810 ,description
1811 ,source_lang
1812 ,creation_date
1813 ,created_by
1814 ,last_update_date
1815 ,last_updated_by
1816 ,last_update_login)
1817 SELECT
1818 s.accounting_method_type_code
1819 ,s.accounting_method_code
1820 ,fl.language_code
1821 ,s.name
1822 ,s.description
1823 ,USERENV('LANG')
1824 ,sysdate
1825 ,xla_environment_pkg.g_usr_id
1826 ,sysdate
1827 ,xla_environment_pkg.g_usr_id
1828 ,xla_environment_pkg.g_login_id
1829 FROM xla_stage_acctg_methods s
1830 JOIN fnd_languages fl
1831 ON fl.installed_flag IN ('I', 'B')
1832 LEFT OUTER JOIN xla_acctg_methods_tl w
1833 ON w.accounting_method_type_code = s.accounting_method_type_code
1834 AND w.accounting_method_code = s.accounting_method_code
1835 AND w.language = fl.language_code
1836 WHERE s.staging_amb_context_code = g_staging_context_code
1837 AND w.accounting_method_type_code IS NULL;
1838
1839 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1840 trace(p_msg => '# row inserted in xla_acctg_methods_tl = '||SQL%ROWCOUNT,
1841 p_module => l_log_module,
1842 p_level => C_LEVEL_STATEMENT);
1843 END IF;
1844
1845 UPDATE xla_acctg_method_rules
1846 SET amb_context_code = g_amb_context_code
1847 WHERE amb_context_code = g_staging_context_code;
1848
1849 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1850 trace(p_msg => '# row moved in xla_acctg_method_rules = '||SQL%ROWCOUNT,
1851 p_module => l_log_module,
1852 p_level => C_LEVEL_STATEMENT);
1853 END IF;
1854
1855 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1856 trace(p_msg => 'END of procedure move_acctg_methods',
1857 p_module => l_log_module,
1858 p_level => C_LEVEL_PROCEDURE);
1859 END IF;
1860
1861 EXCEPTION
1862 WHEN OTHERS THEN
1863 xla_aad_loader_util_pvt.stack_error
1864 (p_appli_s_name => 'XLA'
1865 ,p_msg_name => 'XLA_COMMON_ERROR'
1866 ,p_token_1 => 'LOCATION'
1867 ,p_value_1 => 'xla_aad_overwrite_pvt.move_acctg_methods'
1868 ,p_token_2 => 'ERROR'
1869 ,p_value_2 => 'unhandled exception');
1870 RAISE;
1871
1872 END move_acctg_methods;
1873
1874 --=============================================================================
1875 --
1876 --
1877 --
1878 --
1879 --
1880 -- *********** public procedures and functions **********
1881 --
1882 --
1883 --
1884 --
1885 --
1886 --=============================================================================
1887
1888 --=============================================================================
1889 --
1890 -- Name: overwrite
1891 -- Description: This API overwrite the AADs and its components from the
1892 -- staging area to the working area of an AMB context
1893 --
1894 --=============================================================================
1895 PROCEDURE overwrite
1896 (p_api_version IN NUMBER
1897 ,x_return_status IN OUT NOCOPY VARCHAR2
1898 ,p_application_id IN INTEGER
1899 ,p_amb_context_code IN VARCHAR2
1900 ,p_force_flag IN VARCHAR2
1901 ,p_compile_flag IN VARCHAR2
1902 ,x_overwrite_status IN OUT NOCOPY VARCHAR2)
1903 IS
1904 l_api_name CONSTANT VARCHAR2(30) := 'overwrite';
1905 l_api_version CONSTANT NUMBER := 1.0;
1906
1907 l_staging_context_code VARCHAR2(30);
1908 l_retcode VARCHAR2(30);
1909 l_log_module VARCHAR2(240);
1910 BEGIN
1911 IF g_log_enabled THEN
1912 l_log_module := C_DEFAULT_MODULE||'.overwrite';
1913 END IF;
1914
1915 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1916 trace(p_msg => 'BEGIN of function overwrite: '||
1917 'p_application_id = '||p_application_id||
1918 ', p_amb_context_code = '||p_amb_context_code||
1919 ', p_force_flag = '||p_force_flag,
1920 p_module => l_log_module,
1921 p_level => C_LEVEL_PROCEDURE);
1922 END IF;
1923
1924 l_staging_context_code := xla_aad_loader_util_pvt.get_staging_context_code
1925 (p_application_id => p_application_id
1926 ,p_amb_context_code => p_amb_context_code);
1927
1928 xla_aad_overwrite_pvt.overwrite
1929 (p_api_version => p_api_version
1930 ,x_return_status => x_return_status
1931 ,p_application_id => p_application_id
1932 ,p_amb_context_code => p_amb_context_code
1933 ,p_staging_context_code => l_staging_context_code
1934 ,p_force_flag => p_force_flag
1935 ,p_compile_flag => p_compile_flag
1936 ,x_overwrite_status => x_overwrite_status);
1937
1938 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1939 trace(p_msg => 'END of function overwrite - Return value = '||x_overwrite_status,
1940 p_module => l_log_module,
1941 p_level => C_LEVEL_PROCEDURE);
1942 END IF;
1943 EXCEPTION
1944 WHEN G_EXC_WARNING THEN
1945 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1946 x_overwrite_status := 'WARNING';
1947
1948 WHEN FND_API.G_EXC_ERROR THEN
1949 x_return_status := FND_API.G_RET_STS_ERROR ;
1950 x_overwrite_status := 'ERROR';
1951
1952 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1953 ROLLBACK;
1954 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1955 x_overwrite_status := 'ERROR';
1956
1957 WHEN OTHERS THEN
1958 ROLLBACK;
1959 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1960 x_overwrite_status := 'ERROR';
1961
1962 xla_aad_loader_util_pvt.stack_error
1963 (p_appli_s_name => 'XLA'
1964 ,p_msg_name => 'XLA_COMMON_ERROR'
1965 ,p_token_1 => 'LOCATION'
1966 ,p_value_1 => 'xla_aad_overwrite_pvt.overwrite'
1967 ,p_token_2 => 'ERROR'
1968 ,p_value_2 => 'unhandled exception');
1969 END overwrite;
1970
1971
1972 --=============================================================================
1973 --
1974 -- Name: overwrite
1975 -- Description: This API overwrite the AADs and its components from the
1976 -- staging area to the working area of an AMB context
1977 --
1978 --=============================================================================
1979 PROCEDURE overwrite
1980 (p_api_version IN NUMBER
1981 ,x_return_status IN OUT NOCOPY VARCHAR2
1982 ,p_application_id IN INTEGER
1983 ,p_amb_context_code IN VARCHAR2
1984 ,p_staging_context_code IN VARCHAR2
1985 ,p_force_flag IN VARCHAR2
1986 ,p_compile_flag IN VARCHAR2
1987 ,x_overwrite_status IN OUT NOCOPY VARCHAR2)
1988 IS
1989 l_api_name CONSTANT VARCHAR2(30) := 'overwrite';
1990 l_api_version CONSTANT NUMBER := 1.0;
1991
1992 l_retcode VARCHAR2(30);
1993 l_log_module VARCHAR2(240);
1994 BEGIN
1995 IF g_log_enabled THEN
1996 l_log_module := C_DEFAULT_MODULE||'.overwrite';
1997 END IF;
1998
1999 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2000 trace(p_msg => 'BEGIN of function overwrite: '||
2001 'p_application_id = '||p_application_id||
2002 ', p_amb_context_code = '||p_amb_context_code||
2003 ', p_force_flag = '||p_force_flag,
2004 p_module => l_log_module,
2005 p_level => C_LEVEL_PROCEDURE);
2006 END IF;
2007
2008 -- Standard call to check for call compatibility.
2009 IF (NOT xla_aad_loader_util_pvt.compatible_api_call
2010 (p_current_version_number => l_api_version
2011 ,p_caller_version_number => p_api_version
2012 ,p_api_name => l_api_name
2013 ,p_pkg_name => C_DEFAULT_MODULE))
2014 THEN
2015 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2016 END IF;
2017
2018 -- Initialize global variables
2019 x_return_status := FND_API.G_RET_STS_SUCCESS;
2020
2021 g_application_id := p_application_id;
2022 g_amb_context_code := p_amb_context_code;
2023 g_force_flag := p_force_flag;
2024 g_ac_updated := FALSE;
2025 g_staging_context_code := p_staging_context_code;
2026
2027 -- API Logic
2028 x_overwrite_status := pre_overwrite;
2029 IF (x_overwrite_status = 'WARNING') THEN
2030 RAISE G_EXC_WARNING;
2031 ELSIF (x_overwrite_status = 'ERROR') THEN
2032 RAISE FND_API.G_EXC_ERROR;
2033 END IF;
2034
2035 x_overwrite_status := validation;
2036 IF (x_overwrite_status = 'WARNING') THEN
2037 RAISE G_EXC_WARNING;
2038 ELSIF (x_overwrite_status = 'ERROR') THEN
2039 RAISE FND_API.G_EXC_ERROR;
2040 END IF;
2041
2042 record_log;
2043
2044 -- Clean up working area
2045 xla_aad_loader_util_pvt.purge
2046 (p_application_id => g_application_id
2047 ,p_amb_context_code => g_amb_context_code);
2048
2049 purge_mapping_sets;
2050 purge_analytical_criteria;
2051 purge_adr;
2052 purge_adr_reference;
2053
2054 -- Move AADs from staging to working area
2055 move_components;
2056 move_jlds;
2057 move_aads;
2058 move_acctg_methods;
2059
2060 -- Update AAD and component histories
2061 xla_aad_loader_util_pvt.merge_history
2062 (p_application_id => g_application_id
2063 ,p_staging_context_code => g_staging_context_code);
2064
2065 IF (g_ac_updated) THEN
2066 xla_aad_loader_util_pvt.rebuild_ac_views;
2067 END IF;
2068
2069 IF (p_compile_flag = 'Y') THEN
2070 IF (NOT xla_aad_loader_util_pvt.compile
2071 (p_application_id => g_application_id
2072 ,p_amb_context_code => g_amb_context_code)) THEN
2073 RAISE G_EXC_WARNING;
2074 END IF;
2075 END IF;
2076
2077 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2078 trace(p_msg => 'END of function overwrite - Return value = '||x_overwrite_status,
2079 p_module => l_log_module,
2080 p_level => C_LEVEL_PROCEDURE);
2081 END IF;
2082 EXCEPTION
2083 WHEN G_EXC_WARNING THEN
2084 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2085 x_overwrite_status := 'WARNING';
2086
2087 WHEN FND_API.G_EXC_ERROR THEN
2088 x_return_status := FND_API.G_RET_STS_ERROR ;
2089 x_overwrite_status := 'ERROR';
2090
2091 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2092 ROLLBACK;
2093 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2094 x_overwrite_status := 'ERROR';
2095
2096 WHEN OTHERS THEN
2097 ROLLBACK;
2098 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2099 x_overwrite_status := 'ERROR';
2100
2101 xla_aad_loader_util_pvt.stack_error
2102 (p_appli_s_name => 'XLA'
2103 ,p_msg_name => 'XLA_COMMON_ERROR'
2104 ,p_token_1 => 'LOCATION'
2105 ,p_value_1 => 'xla_aad_overwrite_pvt.overwrite'
2106 ,p_token_2 => 'ERROR'
2107 ,p_value_2 => 'unhandled exception');
2108 END overwrite;
2109
2110 --=============================================================================
2111 --
2112 -- Following code is executed when the package body is referenced for the first
2113 -- time
2114 --
2115 --=============================================================================
2116 BEGIN
2117 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2118 g_log_enabled := fnd_log.test
2119 (log_level => g_log_level
2120 ,module => C_DEFAULT_MODULE);
2121
2122 IF NOT g_log_enabled THEN
2123 g_log_level := C_LEVEL_LOG_DISABLED;
2124 END IF;
2125
2126 END xla_aad_overwrite_pvt;