[Home] [Help]
PACKAGE BODY: APPS.XLA_AAD_LOADER_UTIL_PVT
Source
1 PACKAGE BODY xla_aad_loader_util_pvt AS
2 /* $Header: xlaalutl.pkb 120.16 2008/07/21 15:34:47 krsankar ship $ */
3
4 --=============================================================================
5 -- **************** declaraions ********************
6 --=============================================================================
7 -------------------------------------------------------------------------------
8 -- declaring global types
9 -------------------------------------------------------------------------------
10 TYPE t_array_int IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
11 TYPE t_array_msg IS TABLE OF VARCHAR2(2400) INDEX BY BINARY_INTEGER;
12 TYPE t_array_varchar30 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
13
14 -------------------------------------------------------------------------------
15 -- declaring global constants
16 -------------------------------------------------------------------------------
17 ------------------------------------------------------------------------------
18 -- declaring global variables
19 ------------------------------------------------------------------------------
20 g_err_count INTEGER;
21 g_err_nums t_array_int;
22 g_err_msgs t_array_msg;
23
24 --=============================================================================
25 -- *********** Local Trace Routine **********
26 --=============================================================================
27 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
28 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
29 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
30 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
31 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
32 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
33
34 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
35 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_aad_loader_util_pvt';
36
37 g_log_level NUMBER;
38 g_log_enabled BOOLEAN;
39
40 PROCEDURE trace
41 (p_msg IN VARCHAR2
42 ,p_module IN VARCHAR2
43 ,p_level IN NUMBER) IS
44 BEGIN
45 ----------------------------------------------------------------------------
46 -- Following is for FND log.
47 ----------------------------------------------------------------------------
48 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
49 fnd_log.message(p_level, p_module);
50 ELSIF p_level >= g_log_level THEN
51 fnd_log.string(p_level, p_module, p_msg);
52 END IF;
53 EXCEPTION
54 WHEN xla_exceptions_pkg.application_exception THEN
55 RAISE;
56
57 WHEN OTHERS THEN
58 xla_exceptions_pkg.raise_message
59 (p_location => 'xla_aad_loader_util_pvt.trace');
60 END trace;
61
62
63 --=============================================================================
64 -- *********** private procedures and functions **********
65 --=============================================================================
66
67 --=============================================================================
68 --
69 -- Name: create_staging_context_code
70 -- Description:
71 --
72 --=============================================================================
73 FUNCTION create_staging_context_code
74 (p_application_id INTEGER
75 ,p_amb_context_code VARCHAR2)
76 RETURN VARCHAR2
77 IS
78 PRAGMA AUTONOMOUS_TRANSACTION;
79
80 l_temp_code VARCHAR2(80);
81 l_code VARCHAR2(30);
82 l_log_module VARCHAR2(240);
83 BEGIN
84 IF g_log_enabled THEN
85 l_log_module := C_DEFAULT_MODULE||'.';
86 END IF;
87
88 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
89 trace(p_msg => 'BEGIN of procedure create_staging_context_code: '||
90 'p_amb_context_code = '||p_amb_context_code,
91 p_module => l_log_module,
92 p_level => C_LEVEL_PROCEDURE);
93 END IF;
94
95 WHILE (l_code IS NULL) LOOP
96 IF (C_LEVEL_EVENT >= g_log_level) THEN
97 trace(p_msg => 'staging context code not found',
98 p_module => l_log_module,
99 p_level => C_LEVEL_EVENT);
100 END IF;
101
102 SELECT TO_CHAR(systimestamp,'SSSSSFF') INTO l_temp_code FROM dual;
103 l_temp_code := substr(p_amb_context_code,1,12) || '_'||
104 p_application_id || '_S_' ||l_temp_code;
105 l_code := substr(l_temp_code,1,30);
106
107 IF (C_LEVEL_EVENT >= g_log_level) THEN
108 trace(p_msg => 'Staging amb context code: '||
109 l_code,
110 p_module => l_log_module,
111 p_level => C_LEVEL_EVENT);
112 END IF;
113
114 INSERT INTO xla_appli_amb_contexts
115 (application_id
116 ,amb_context_code
117 ,staging_amb_context_code
118 ,updated_flag
119 ,last_analyzed_date
120 ,batch_name
121 ,object_version_number
122 ,creation_date
123 ,created_by
124 ,last_update_date
125 ,last_updated_by
126 ,last_update_login)
127 SELECT
128 p_application_id
129 ,p_amb_context_code
130 ,l_code
131 ,'Y'
132 ,NULL
133 ,NULL
134 ,1
135 ,sysdate
136 ,xla_environment_pkg.g_usr_id
137 ,sysdate
138 ,xla_environment_pkg.g_usr_id
139 ,xla_environment_pkg.g_login_id
140 FROM dual
141 WHERE NOT EXISTS (SELECT 1
142 FROM xla_appli_amb_contexts
143 WHERE staging_amb_context_code = l_code);
144
145 IF (SQL%ROWCOUNT = 0) THEN
146 l_code := NULL;
147 END IF;
148 END LOOP;
149
150 COMMIT;
151
152 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
153 trace(p_msg => 'END of procedure create_staging_context_code: '||
154 'staging AMB context code = '||l_code,
155 p_module => l_log_module,
156 p_level => C_LEVEL_PROCEDURE);
157 END IF;
158 return l_code;
159 EXCEPTION
160 WHEN OTHERS THEN
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_loader_util_pvt.create_staging_context_code'
166 ,p_token_2 => 'ERROR'
167 ,p_value_2 => 'unhandled exception');
168 RAISE;
169
170 END create_staging_context_code;
171
172
173 --=============================================================================
174 --
175 --
176 --
177 --
178 --
179 -- *********** public procedures and functions **********
180 --
181 --
182 --
183 --
184 --
185 --=============================================================================
186
187 --=============================================================================
188 --
189 -- Name: purge
190 -- Description: This API purge all application accounting definitions and its
191 -- component from a specified AMB context code except mapping sets
192 -- and analytical criteria.
193 --
194 --=============================================================================
195 PROCEDURE purge
196 (p_application_id INTEGER
197 ,p_amb_context_code VARCHAR2)
198 IS
199 l_log_module VARCHAR2(240);
200 BEGIN
201 IF g_log_enabled THEN
202 l_log_module := C_DEFAULT_MODULE||'.purge';
203 END IF;
204
205 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
206 trace(p_msg => 'BEGIN of procedure purge: '||
207 'application_id = '||p_application_id||
208 ', amb_context_code = '||p_amb_context_code,
209 p_module => l_log_module,
210 p_level => C_LEVEL_PROCEDURE);
211 END IF;
212
213 --
214 -- Delete accounting method rules
215 --
216 DELETE FROM xla_acctg_method_rules
217 WHERE application_id = p_application_id
218 AND amb_context_code = p_amb_context_code;
219
220 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
221 trace(p_msg => '# record deleted from xla_acctg_method_rules = '||SQL%ROWCOUNT,
222 p_module => l_log_module,
223 p_level => C_LEVEL_STATEMENT);
224 END IF;
225
226 --
227 -- Delete application accounting definition assignments
228 --
229 DELETE FROM xla_line_defn_ac_assgns
230 WHERE application_id = p_application_id
231 AND amb_context_code = p_amb_context_code;
232
233 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
234 trace(p_msg => '# record deleted from xla_line_defn_ac_assgns = '||SQL%ROWCOUNT,
235 p_module => l_log_module,
236 p_level => C_LEVEL_STATEMENT);
237 END IF;
238
239 DELETE FROM xla_line_defn_adr_assgns
240 WHERE application_id = p_application_id
241 AND amb_context_code = p_amb_context_code;
242
243 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
244 trace(p_msg => '# record deleted from xla_line_defn_adr_assgns = '||SQL%ROWCOUNT,
245 p_module => l_log_module,
246 p_level => C_LEVEL_STATEMENT);
247 END IF;
248
249 DELETE FROM xla_line_defn_jlt_assgns
250 WHERE application_id = p_application_id
251 AND amb_context_code = p_amb_context_code;
252
253 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
254 trace(p_msg => '# record deleted from xla_line_defn_jlt_assgns = '||SQL%ROWCOUNT,
255 p_module => l_log_module,
256 p_level => C_LEVEL_STATEMENT);
257 END IF;
258
259 DELETE FROM xla_line_definitions_b
260 WHERE application_id = p_application_id
261 AND amb_context_code = p_amb_context_code;
262
263 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
264 trace(p_msg => '# record deleted from xla_line_definitions_b = '||SQL%ROWCOUNT,
265 p_module => l_log_module,
266 p_level => C_LEVEL_STATEMENT);
267 END IF;
268
269 DELETE FROM xla_line_definitions_tl
270 WHERE application_id = p_application_id
271 AND amb_context_code = p_amb_context_code;
272
273 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
274 trace(p_msg => '# record deleted from xla_line_definitions_tl = '||SQL%ROWCOUNT,
275 p_module => l_log_module,
276 p_level => C_LEVEL_STATEMENT);
277 END IF;
278
279 DELETE FROM xla_aad_line_defn_assgns
280 WHERE application_id = p_application_id
281 AND amb_context_code = p_amb_context_code;
282
283 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
284 trace(p_msg => '# record deleted from xla_aad_line_defn_assgns = '||SQL%ROWCOUNT,
285 p_module => l_log_module,
286 p_level => C_LEVEL_STATEMENT);
287 END IF;
288
289 DELETE FROM xla_aad_header_ac_assgns
290 WHERE application_id = p_application_id
291 AND amb_context_code = p_amb_context_code;
292
293 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
294 trace(p_msg => '# record deleted from xla_aad_header_ac_assgns = '||SQL%ROWCOUNT,
295 p_module => l_log_module,
296 p_level => C_LEVEL_STATEMENT);
297 END IF;
298
299 DELETE FROM xla_mpa_header_ac_assgns
300 WHERE application_id = p_application_id
301 AND amb_context_code = p_amb_context_code;
302
303 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
304 trace(p_msg => '# record deleted from xla_mpa_header_ac_assgns = '||SQL%ROWCOUNT,
305 p_module => l_log_module,
306 p_level => C_LEVEL_STATEMENT);
307 END IF;
308
309 DELETE FROM xla_mpa_jlt_adr_assgns
310 WHERE application_id = p_application_id
311 AND amb_context_code = p_amb_context_code;
312
313 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
314 trace(p_msg => '# record deleted from xla_mpa_jlt_adr_assgns = '||SQL%ROWCOUNT,
315 p_module => l_log_module,
316 p_level => C_LEVEL_STATEMENT);
317 END IF;
318
319 DELETE FROM xla_mpa_jlt_ac_assgns
320 WHERE application_id = p_application_id
321 AND amb_context_code = p_amb_context_code;
322
323 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
324 trace(p_msg => '# record deleted from xla_mpa_jlt_ac_assgns = '||SQL%ROWCOUNT,
325 p_module => l_log_module,
326 p_level => C_LEVEL_STATEMENT);
327 END IF;
328
329 DELETE FROM xla_mpa_jlt_assgns
330 WHERE application_id = p_application_id
331 AND amb_context_code = p_amb_context_code;
332
333 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
334 trace(p_msg => '# record deleted from xla_mpa_jlt_assgns = '||SQL%ROWCOUNT,
335 p_module => l_log_module,
336 p_level => C_LEVEL_STATEMENT);
337 END IF;
338
339 DELETE FROM xla_aad_hdr_acct_attrs
340 WHERE application_id = p_application_id
341 AND amb_context_code = p_amb_context_code;
342
343 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
344 trace(p_msg => '# record deleted from xla_aad_hdr_acct_attrs = '||SQL%ROWCOUNT,
345 p_module => l_log_module,
346 p_level => C_LEVEL_STATEMENT);
347 END IF;
348
349 DELETE FROM xla_prod_acct_headers
350 WHERE application_id = p_application_id
351 AND amb_context_code = p_amb_context_code;
352
353 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
354 trace(p_msg => '# record deleted from xla_prod_acct_headers = '||SQL%ROWCOUNT,
355 p_module => l_log_module,
356 p_level => C_LEVEL_STATEMENT);
357 END IF;
358
359 --
360 -- Delete application accounting definitions
361 --
362 DELETE FROM xla_product_rules_tl
363 WHERE application_id = p_application_id
364 AND amb_context_code = p_amb_context_code;
365
366 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
367 trace(p_msg => '# record deleted from xla_product_rules_tl = '||SQL%ROWCOUNT,
371
368 p_module => l_log_module,
369 p_level => C_LEVEL_STATEMENT);
370 END IF;
372 DELETE FROM xla_product_rules_b
373 WHERE application_id = p_application_id
374 AND amb_context_code = p_amb_context_code;
375
376 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
377 trace(p_msg => '# record deleted from xla_product_rules_b = '||SQL%ROWCOUNT,
378 p_module => l_log_module,
379 p_level => C_LEVEL_STATEMENT);
380 END IF;
381
382 --
383 -- Delete conditions
384 --
385 DELETE FROM xla_conditions
386 WHERE application_id = p_application_id
387 AND amb_context_code = p_amb_context_code
388 AND segment_rule_detail_id IS NULL; -- bug 4367287: delete ADR on demand
389
390 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
391 trace(p_msg => '# record deleted from xla_conditions = '||SQL%ROWCOUNT,
392 p_module => l_log_module,
393 p_level => C_LEVEL_STATEMENT);
394 END IF;
395
396 --
397 -- Delete account derivation rules
398 --
399 /* Bug 4367287 - the ADR is deleted on demand
400
401 DELETE FROM xla_seg_rule_details
402 WHERE application_id = p_application_id
403 AND amb_context_code = p_amb_context_code;
404
405 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
406 trace(p_msg => '# record deleted from xla_seg_rule_details = '||SQL%ROWCOUNT,
407 p_module => l_log_module,
408 p_level => C_LEVEL_STATEMENT);
409 END IF;
410
411 DELETE FROM xla_seg_rules_tl
412 WHERE application_id = p_application_id
413 AND amb_context_code = p_amb_context_code;
414
415 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
416 trace(p_msg => '# record deleted from xla_seg_rules_tl = '||SQL%ROWCOUNT,
417 p_module => l_log_module,
418 p_level => C_LEVEL_STATEMENT);
419 END IF;
420
421 DELETE FROM xla_seg_rules_b
422 WHERE application_id = p_application_id
423 AND amb_context_code = p_amb_context_code;
424
425 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
426 trace(p_msg => '# record deleted from xla_seg_rules_b = '||SQL%ROWCOUNT,
427 p_module => l_log_module,
428 p_level => C_LEVEL_STATEMENT);
429 END IF;
430 */
431
432 --
433 -- Delete descriptions
434 --
435 DELETE FROM xla_descript_details_tl
436 WHERE description_detail_id IN
437 (SELECT description_detail_id
438 FROM xla_descript_details_b dd
439 ,xla_desc_priorities dp
440 WHERE dd.description_prio_id = dp.description_prio_id
441 AND dp.application_id = p_application_id
442 AND dp.amb_context_code = p_amb_context_code);
443
444 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
445 trace(p_msg => '# record deleted from xla_descript_details_tl = '||SQL%ROWCOUNT,
446 p_module => l_log_module,
447 p_level => C_LEVEL_STATEMENT);
448 END IF;
449
450 DELETE FROM xla_descript_details_b
451 WHERE description_prio_id IN
452 (SELECT description_prio_id
453 FROM xla_desc_priorities
454 WHERE application_id = p_application_id
455 AND amb_context_code = p_amb_context_code);
456
457 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
458 trace(p_msg => '# record deleted from xla_descript_details_b = '||SQL%ROWCOUNT,
459 p_module => l_log_module,
460 p_level => C_LEVEL_STATEMENT);
461 END IF;
462
463 DELETE FROM xla_desc_priorities
464 WHERE application_id = p_application_id
465 AND amb_context_code = p_amb_context_code;
466
467 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
468 trace(p_msg => '# record deleted from xla_desc_priorities = '||SQL%ROWCOUNT,
469 p_module => l_log_module,
470 p_level => C_LEVEL_STATEMENT);
471 END IF;
472
473 DELETE FROM xla_descriptions_tl
474 WHERE application_id = p_application_id
475 AND amb_context_code = p_amb_context_code;
476
477 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
478 trace(p_msg => '# record deleted from xla_descriptions_tl = '||SQL%ROWCOUNT,
479 p_module => l_log_module,
480 p_level => C_LEVEL_STATEMENT);
481 END IF;
482
483 DELETE FROM xla_descriptions_b
484 WHERE application_id = p_application_id
485 AND amb_context_code = p_amb_context_code;
486
487 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
488 trace(p_msg => '# record deleted from xla_descriptions_b = '||SQL%ROWCOUNT,
489 p_module => l_log_module,
490 p_level => C_LEVEL_STATEMENT);
491 END IF;
492
493 --
494 -- Delete journal line types
495 --
496 DELETE FROM xla_jlt_acct_attrs
497 WHERE application_id = p_application_id
498 AND amb_context_code = p_amb_context_code;
499
500 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
501 trace(p_msg => '# record deleted from xla_jlt_acct_attrs = '||SQL%ROWCOUNT,
502 p_module => l_log_module,
503 p_level => C_LEVEL_STATEMENT);
504 END IF;
505
506 DELETE FROM xla_acct_line_types_tl
507 WHERE application_id = p_application_id
511 trace(p_msg => '# record deleted from xla_acct_line_types_tl = '||SQL%ROWCOUNT,
508 AND amb_context_code = p_amb_context_code;
509
510 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
512 p_module => l_log_module,
513 p_level => C_LEVEL_STATEMENT);
514 END IF;
515
516 DELETE FROM xla_acct_line_types_b
517 WHERE application_id = p_application_id
518 AND amb_context_code = p_amb_context_code;
519
520 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
521 trace(p_msg => '# record deleted from xla_acct_line_types_b = '||SQL%ROWCOUNT,
522 p_module => l_log_module,
523 p_level => C_LEVEL_STATEMENT);
524 END IF;
525
526 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
527 trace(p_msg => 'END of procedure purge',
528 p_module => l_log_module,
529 p_level => C_LEVEL_PROCEDURE);
530 END IF;
531 EXCEPTION
532 WHEN OTHERS THEN
533 xla_aad_loader_util_pvt.stack_error
534 (p_appli_s_name => 'XLA'
535 ,p_msg_name => 'XLA_COMMON_ERROR'
536 ,p_token_1 => 'LOCATION'
537 ,p_value_1 => 'xla_aad_loader_util_pvt.purge'
538 ,p_token_2 => 'ERROR'
539 ,p_value_2 => 'unhandled exception');
540 RAISE;
541
542 END purge;
543
544 --=============================================================================
545 --
546 -- Name: lock_area
547 -- Description: This API locks all the records in amb tables of the amb context.
548 --
549 --=============================================================================
550 FUNCTION lock_area
551 (p_application_id INTEGER
552 ,p_amb_context_code VARCHAR2)
553 RETURN VARCHAR2
554 IS
555 CURSOR c_product_rules_b IS
556 SELECT *
557 FROM xla_product_rules_b
558 WHERE application_id = p_application_id
559 AND amb_context_code = p_amb_context_code
560 FOR UPDATE NOWAIT;
561
562 CURSOR c_product_rules_tl IS
563 SELECT *
564 FROM xla_product_rules_tl
565 WHERE application_id = p_application_id
566 AND amb_context_code = p_amb_context_code
567 FOR UPDATE NOWAIT;
568
569 CURSOR c_prod_acct_headers IS
570 SELECT *
571 FROM xla_prod_acct_headers
572 WHERE application_id = p_application_id
573 AND amb_context_code = p_amb_context_code
574 FOR UPDATE NOWAIT;
575
576 CURSOR c_aad_hdr_acct_attrs IS
577 SELECT *
578 FROM xla_aad_hdr_acct_attrs
579 WHERE application_id = p_application_id
580 AND amb_context_code = p_amb_context_code
581 FOR UPDATE NOWAIT;
582
583 CURSOR c_aad_header_ac_assgns IS
584 SELECT *
585 FROM xla_aad_header_ac_assgns
586 WHERE application_id = p_application_id
587 AND amb_context_code = p_amb_context_code
588 FOR UPDATE NOWAIT;
589
590 CURSOR c_mpa_header_ac_assgns IS
591 SELECT *
592 FROM xla_mpa_header_ac_assgns
593 WHERE application_id = p_application_id
594 AND amb_context_code = p_amb_context_code
595 FOR UPDATE NOWAIT;
596
597 CURSOR c_mpa_jlt_ac_assgns IS
598 SELECT *
599 FROM xla_mpa_jlt_ac_assgns
600 WHERE application_id = p_application_id
601 AND amb_context_code = p_amb_context_code
602 FOR UPDATE NOWAIT;
603
604 CURSOR c_mpa_jlt_adr_assgns IS
605 SELECT *
606 FROM xla_mpa_jlt_adr_assgns
607 WHERE application_id = p_application_id
608 AND amb_context_code = p_amb_context_code
609 FOR UPDATE NOWAIT;
610
611 CURSOR c_mpa_jlt_assgns IS
612 SELECT *
613 FROM xla_mpa_jlt_assgns
614 WHERE application_id = p_application_id
615 AND amb_context_code = p_amb_context_code
616 FOR UPDATE NOWAIT;
617
618 CURSOR c_aad_line_defn_assgns IS
619 SELECT *
620 FROM xla_aad_line_defn_assgns
621 WHERE application_id = p_application_id
622 AND amb_context_code = p_amb_context_code
623 FOR UPDATE NOWAIT;
624
625 CURSOR c_line_definitions_b IS
626 SELECT *
627 FROM xla_line_definitions_b
628 WHERE application_id = p_application_id
629 AND amb_context_code = p_amb_context_code
630 FOR UPDATE NOWAIT;
631
632 CURSOR c_line_definitions_tl IS
633 SELECT *
634 FROM xla_line_definitions_tl
635 WHERE application_id = p_application_id
636 AND amb_context_code = p_amb_context_code
637 FOR UPDATE NOWAIT;
638
639 CURSOR c_line_defn_jlt_assgns IS
640 SELECT *
641 FROM xla_line_defn_jlt_assgns
642 WHERE application_id = p_application_id
643 AND amb_context_code = p_amb_context_code
644 FOR UPDATE NOWAIT;
645
646 CURSOR c_line_defn_adr_assgns IS
647 SELECT *
648 FROM xla_line_defn_adr_assgns
649 WHERE application_id = p_application_id
650 AND amb_context_code = p_amb_context_code
654 SELECT *
651 FOR UPDATE NOWAIT;
652
653 CURSOR c_line_defn_ac_assgns IS
655 FROM xla_line_defn_ac_assgns
656 WHERE application_id = p_application_id
657 AND amb_context_code = p_amb_context_code
658 FOR UPDATE NOWAIT;
659
660 CURSOR c_seg_rules_b IS
661 SELECT *
662 FROM xla_seg_rules_b
663 WHERE application_id = p_application_id
664 AND amb_context_code = p_amb_context_code
665 FOR UPDATE NOWAIT;
666
667 CURSOR c_seg_rules_tl IS
668 SELECT *
669 FROM xla_seg_rules_tl
670 WHERE application_id = p_application_id
671 AND amb_context_code = p_amb_context_code
672 FOR UPDATE NOWAIT;
673
674 CURSOR c_seg_rule_details IS
675 SELECT *
676 FROM xla_seg_rule_details
677 WHERE application_id = p_application_id
678 AND amb_context_code = p_amb_context_code
679 FOR UPDATE NOWAIT;
680
681 CURSOR c_acct_line_types_b IS
682 SELECT *
683 FROM xla_acct_line_types_b
684 WHERE application_id = p_application_id
685 AND amb_context_code = p_amb_context_code
686 FOR UPDATE NOWAIT;
687
688 CURSOR c_acct_line_types_tl IS
689 SELECT *
690 FROM xla_acct_line_types_tl
691 WHERE application_id = p_application_id
692 AND amb_context_code = p_amb_context_code
693 FOR UPDATE NOWAIT;
694
695 CURSOR c_jlt_acct_attrs IS
696 SELECT *
697 FROM xla_jlt_acct_attrs
698 WHERE application_id = p_application_id
699 AND amb_context_code = p_amb_context_code
700 FOR UPDATE NOWAIT;
701
702 CURSOR c_descriptions_b IS
703 SELECT *
704 FROM xla_descriptions_b
705 WHERE application_id = p_application_id
706 AND amb_context_code = p_amb_context_code
707 FOR UPDATE NOWAIT;
708
709 CURSOR c_descriptions_tl IS
710 SELECT *
711 FROM xla_descriptions_tl
712 WHERE application_id = p_application_id
713 AND amb_context_code = p_amb_context_code
714 FOR UPDATE NOWAIT;
715
716 CURSOR c_desc_priorities IS
717 SELECT *
718 FROM xla_desc_priorities
719 WHERE application_id = p_application_id
720 AND amb_context_code = p_amb_context_code
721 FOR UPDATE NOWAIT;
722
723 CURSOR c_descript_details_tl IS
724 SELECT *
725 FROM xla_descript_details_b b
726 ,xla_descript_details_tl t
727 ,xla_desc_priorities p
728 WHERE t.description_detail_id = b.description_detail_id
729 AND b.description_prio_id = p.description_prio_id
730 AND p.application_id = p_application_id
731 AND p.amb_context_code = p_amb_context_code
732 FOR UPDATE NOWAIT;
733
734 CURSOR c_conditions IS
735 SELECT *
736 FROM xla_conditions
737 WHERE application_id = p_application_id
738 AND amb_context_code = p_amb_context_code
739 FOR UPDATE NOWAIT;
740
741 CURSOR c_mapping_sets IS
742 SELECT *
743 FROM xla_mapping_sets_b b
744 ,xla_mapping_sets_tl t
745 ,xla_seg_rule_details s
746 WHERE t.mapping_set_code = b.mapping_set_code
747 AND b.mapping_set_code = s.value_mapping_set_code
748 AND s.application_id = p_application_id
749 AND s.amb_context_code = p_amb_context_code
750 FOR UPDATE NOWAIT;
751
752 CURSOR c_mapping_set_values IS
753 SELECT *
754 FROM xla_mapping_set_values b
755 ,xla_seg_rule_details s
756 WHERE b.mapping_set_code = s.value_mapping_set_code
757 AND s.application_id = p_application_id
758 AND s.amb_context_code = p_amb_context_code
759 FOR UPDATE NOWAIT;
760
761 /*CURSOR c_analytical_hdrs IS
762 SELECT *
763 FROM xla_analytical_hdrs_b b
764 ,xla_analytical_hdrs_tl t
765 WHERE t.analytical_criterion_type_code = b.analytical_criterion_type_code
766 AND t.analytical_criterion_code = b.analytical_criterion_code
767 AND t.amb_context_code = b.amb_context_code
768 AND b.amb_context_code = p_amb_context_code
769 AND EXISTS (SELECT 1
770 FROM xla_aad_header_ac_assgns xah
771 WHERE xah.analytical_criterion_type_code = b.analytical_criterion_type_code
772 AND xah.analytical_criterion_code = b.analytical_criterion_code
773 AND xah.amb_context_code = b.amb_context_code
774 AND xah.application_id = p_application_id
775 AND xah.amb_context_code = p_amb_context_code
776 UNION
777 SELECT 1
778 FROM xla_line_defn_ac_assgns xld
779 WHERE xld.analytical_criterion_type_code = b.analytical_criterion_type_code
780 AND xld.analytical_criterion_code = b.analytical_criterion_code
781 AND xld.amb_context_code = b.amb_context_code
785 SELECT 1
782 AND xld.application_id = p_application_id
783 AND xld.amb_context_code = p_amb_context_code
784 UNION
786 FROM xla_mpa_header_ac_assgns xld
787 WHERE xld.analytical_criterion_type_code = b.analytical_criterion_type_code
788 AND xld.analytical_criterion_code = b.analytical_criterion_code
789 AND xld.amb_context_code = b.amb_context_code
790 AND xld.application_id = p_application_id
791 AND xld.amb_context_code = p_amb_context_code
792 UNION
793 SELECT 1
794 FROM xla_mpa_jlt_ac_assgns xld
795 WHERE xld.analytical_criterion_type_code = b.analytical_criterion_type_code
796 AND xld.analytical_criterion_code = b.analytical_criterion_code
797 AND xld.amb_context_code = b.amb_context_code
798 AND xld.application_id = p_application_id
799 AND xld.amb_context_code = p_amb_context_code)
800 FOR UPDATE NOWAIT;*/
801
802 /* CURSOR c_analytical_dtls IS
803 SELECT *
804 FROM xla_analytical_dtls_b b
805 ,xla_analytical_dtls_tl t
806 WHERE t.analytical_criterion_type_code = b.analytical_criterion_type_code
807 AND t.analytical_criterion_code = b.analytical_criterion_code
808 AND t.amb_context_code = b.amb_context_code
809 AND b.amb_context_code = p_amb_context_code
810 AND EXISTS (SELECT 1
811 FROM xla_aad_header_ac_assgns xah
812 WHERE xah.analytical_criterion_type_code = b.analytical_criterion_type_code
813 AND xah.analytical_criterion_code = b.analytical_criterion_code
814 AND xah.amb_context_code = b.amb_context_code
815 AND xah.application_id = p_application_id
816 AND xah.amb_context_code = p_amb_context_code
817 UNION
818 SELECT 1
819 FROM xla_line_defn_ac_assgns xld
820 WHERE xld.analytical_criterion_type_code = b.analytical_criterion_type_code
821 AND xld.analytical_criterion_code = b.analytical_criterion_code
822 AND xld.amb_context_code = b.amb_context_code
823 AND xld.application_id = p_application_id
824 AND xld.amb_context_code = p_amb_context_code
825 UNION
826 SELECT 1
827 FROM xla_mpa_header_ac_assgns xld
828 WHERE xld.analytical_criterion_type_code = b.analytical_criterion_type_code
829 AND xld.analytical_criterion_code = b.analytical_criterion_code
830 AND xld.amb_context_code = b.amb_context_code
831 AND xld.application_id = p_application_id
832 AND xld.amb_context_code = p_amb_context_code
833 UNION
834 SELECT 1
835 FROM xla_mpa_jlt_ac_assgns xld
836 WHERE xld.analytical_criterion_type_code = b.analytical_criterion_type_code
837 AND xld.analytical_criterion_code = b.analytical_criterion_code
838 AND xld.amb_context_code = b.amb_context_code
839 AND xld.application_id = p_application_id
840 AND xld.amb_context_code = p_amb_context_code)
841 FOR UPDATE NOWAIT;*/
842
843 /*CURSOR c_analytical_sources IS
844 SELECT *
845 FROM xla_analytical_sources b
846 WHERE b.amb_context_code = p_amb_context_code
847 AND EXISTS (SELECT 1
848 FROM xla_aad_header_ac_assgns xah
849 WHERE xah.analytical_criterion_type_code = b.analytical_criterion_type_code
850 AND xah.analytical_criterion_code = b.analytical_criterion_code
851 AND xah.amb_context_code = b.amb_context_code
852 AND xah.application_id = p_application_id
853 AND xah.amb_context_code = p_amb_context_code
854 UNION
855 SELECT 1
856 FROM xla_line_defn_ac_assgns xld
857 WHERE xld.analytical_criterion_type_code = b.analytical_criterion_type_code
858 AND xld.analytical_criterion_code = b.analytical_criterion_code
859 AND xld.amb_context_code = b.amb_context_code
860 AND xld.application_id = p_application_id
861 AND xld.amb_context_code = p_amb_context_code
862 UNION
863 SELECT 1
864 FROM xla_mpa_header_ac_assgns xld
865 WHERE xld.analytical_criterion_type_code = b.analytical_criterion_type_code
866 AND xld.analytical_criterion_code = b.analytical_criterion_code
870 UNION
867 AND xld.amb_context_code = b.amb_context_code
868 AND xld.application_id = p_application_id
869 AND xld.amb_context_code = p_amb_context_code
871 SELECT 1
872 FROM xla_mpa_jlt_ac_assgns xld
873 WHERE xld.analytical_criterion_type_code = b.analytical_criterion_type_code
874 AND xld.analytical_criterion_code = b.analytical_criterion_code
875 AND xld.amb_context_code = b.amb_context_code
876 AND xld.application_id = p_application_id
877 AND xld.amb_context_code = p_amb_context_code)
878 FOR UPDATE NOWAIT;*/
879
880 l_dummy INTEGER;
881 l_retcode VARCHAR2(30);
882 l_log_module VARCHAR2(240);
883 BEGIN
884 IF g_log_enabled THEN
885 l_log_module := C_DEFAULT_MODULE||'.lock_area';
886 END IF;
887
888 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
889 trace(p_msg => 'BEGIN of procedure lock_area',
890 p_module => l_log_module,
891 p_level => C_LEVEL_PROCEDURE);
892 END IF;
893
894 l_retcode := 'SUCCESS';
895
896 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
897 trace(p_msg => 'lock c_product_rules_b',
898 p_module => l_log_module,
899 p_level => C_LEVEL_STATEMENT);
900 END IF;
901
902 OPEN c_product_rules_b;
903 CLOSE c_product_rules_b;
904
905 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
906 trace(p_msg => 'lock c_product_rules_tl',
907 p_module => l_log_module,
908 p_level => C_LEVEL_STATEMENT);
909 END IF;
910
911 OPEN c_product_rules_tl;
912 CLOSE c_product_rules_tl;
913
914 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
915 trace(p_msg => 'lock c_prod_acct_headers',
916 p_module => l_log_module,
917 p_level => C_LEVEL_STATEMENT);
918 END IF;
919
920 OPEN c_prod_acct_headers;
921 CLOSE c_prod_acct_headers;
922
923 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
924 trace(p_msg => 'lock c_aad_hdr_acct_attrs',
925 p_module => l_log_module,
926 p_level => C_LEVEL_STATEMENT);
927 END IF;
928
929 OPEN c_aad_hdr_acct_attrs;
930 CLOSE c_aad_hdr_acct_attrs;
931
932 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
933 trace(p_msg => 'lock c_aad_header_ac_assgns',
934 p_module => l_log_module,
935 p_level => C_LEVEL_STATEMENT);
936 END IF;
937
938 OPEN c_aad_header_ac_assgns;
939 CLOSE c_aad_header_ac_assgns;
940
941 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
942 trace(p_msg => 'lock c_mpa_header_ac_assgns',
943 p_module => l_log_module,
944 p_level => C_LEVEL_STATEMENT);
945 END IF;
946
947 OPEN c_mpa_header_ac_assgns;
948 CLOSE c_mpa_header_ac_assgns;
949
950 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
951 trace(p_msg => 'lock c_mpa_jlt_ac_assgns',
952 p_module => l_log_module,
953 p_level => C_LEVEL_STATEMENT);
954 END IF;
955
956 OPEN c_mpa_jlt_ac_assgns;
957 CLOSE c_mpa_jlt_ac_assgns;
958
959 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
960 trace(p_msg => 'lock c_mpa_jlt_adr_assgns',
961 p_module => l_log_module,
962 p_level => C_LEVEL_STATEMENT);
963 END IF;
964
965 OPEN c_mpa_jlt_adr_assgns;
966 CLOSE c_mpa_jlt_adr_assgns;
967
968 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
969 trace(p_msg => 'lock c_mpa_jlt_assgns',
970 p_module => l_log_module,
971 p_level => C_LEVEL_STATEMENT);
972 END IF;
973
974 OPEN c_mpa_jlt_assgns;
975 CLOSE c_mpa_jlt_assgns;
976
977 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
978 trace(p_msg => 'lock c_aad_line_defn_assgns',
979 p_module => l_log_module,
980 p_level => C_LEVEL_STATEMENT);
981 END IF;
982
983 OPEN c_aad_line_defn_assgns;
984 CLOSE c_aad_line_defn_assgns;
985
986 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
987 trace(p_msg => 'lock c_line_definitions_b',
988 p_module => l_log_module,
989 p_level => C_LEVEL_STATEMENT);
990 END IF;
991
992 OPEN c_line_definitions_b;
993 CLOSE c_line_definitions_b;
994
995 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
996 trace(p_msg => 'lock c_line_definitions_tl',
997 p_module => l_log_module,
998 p_level => C_LEVEL_STATEMENT);
999 END IF;
1000
1001 OPEN c_line_definitions_tl;
1002 CLOSE c_line_definitions_tl;
1003
1004 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1005 trace(p_msg => 'lock c_line_defn_jlt_assgns',
1006 p_module => l_log_module,
1007 p_level => C_LEVEL_STATEMENT);
1008 END IF;
1009
1010 OPEN c_line_defn_jlt_assgns;
1011 CLOSE c_line_defn_jlt_assgns;
1012
1013 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1017 END IF;
1014 trace(p_msg => 'lock c_line_defn_adr_assgns',
1015 p_module => l_log_module,
1016 p_level => C_LEVEL_STATEMENT);
1018
1019 OPEN c_line_defn_adr_assgns;
1020 CLOSE c_line_defn_adr_assgns;
1021
1022 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1023 trace(p_msg => 'lock c_line_defn_ac_assgns',
1024 p_module => l_log_module,
1025 p_level => C_LEVEL_STATEMENT);
1026 END IF;
1027
1028 OPEN c_line_defn_ac_assgns;
1029 CLOSE c_line_defn_ac_assgns;
1030
1031 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1032 trace(p_msg => 'lock c_seg_rules_b',
1033 p_module => l_log_module,
1034 p_level => C_LEVEL_STATEMENT);
1035 END IF;
1036
1037 OPEN c_seg_rules_b;
1038 CLOSE c_seg_rules_b;
1039
1040 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1041 trace(p_msg => 'lock c_seg_rules_tl',
1042 p_module => l_log_module,
1043 p_level => C_LEVEL_STATEMENT);
1044 END IF;
1045
1046 OPEN c_seg_rules_tl;
1047 CLOSE c_seg_rules_tl;
1048
1049 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1050 trace(p_msg => 'lock c_seg_rule_details',
1051 p_module => l_log_module,
1052 p_level => C_LEVEL_STATEMENT);
1053 END IF;
1054
1055 OPEN c_seg_rule_details;
1056 CLOSE c_seg_rule_details;
1057
1058 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1059 trace(p_msg => 'lock c_acct_line_types_b',
1060 p_module => l_log_module,
1061 p_level => C_LEVEL_STATEMENT);
1062 END IF;
1063
1064 OPEN c_acct_line_types_b;
1065 CLOSE c_acct_line_types_b;
1066
1067 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1068 trace(p_msg => 'lock c_acct_line_types_tl',
1069 p_module => l_log_module,
1070 p_level => C_LEVEL_STATEMENT);
1071 END IF;
1072
1073 OPEN c_acct_line_types_tl;
1074 CLOSE c_acct_line_types_tl;
1075
1076 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1077 trace(p_msg => 'lock c_jlt_acct_attrs',
1078 p_module => l_log_module,
1079 p_level => C_LEVEL_STATEMENT);
1080 END IF;
1081
1082 OPEN c_jlt_acct_attrs;
1083 CLOSE c_jlt_acct_attrs;
1084
1085 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1086 trace(p_msg => 'lock c_descriptions_b',
1087 p_module => l_log_module,
1088 p_level => C_LEVEL_STATEMENT);
1089 END IF;
1090
1091 OPEN c_descriptions_b;
1092 CLOSE c_descriptions_b;
1093
1094 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1095 trace(p_msg => 'lock c_descriptions_tl',
1096 p_module => l_log_module,
1097 p_level => C_LEVEL_STATEMENT);
1098 END IF;
1099
1100 OPEN c_descriptions_tl;
1101 CLOSE c_descriptions_tl;
1102
1103 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1104 trace(p_msg => 'lock c_desc_priorities',
1105 p_module => l_log_module,
1106 p_level => C_LEVEL_STATEMENT);
1107 END IF;
1108
1109 OPEN c_desc_priorities;
1110 CLOSE c_desc_priorities;
1111
1112 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1113 trace(p_msg => 'lock c_descript_details_tl',
1114 p_module => l_log_module,
1115 p_level => C_LEVEL_STATEMENT);
1116 END IF;
1117
1118 OPEN c_descript_details_tl;
1119 CLOSE c_descript_details_tl;
1120
1121 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1122 trace(p_msg => 'lock c_conditions',
1123 p_module => l_log_module,
1124 p_level => C_LEVEL_STATEMENT);
1125 END IF;
1126
1127 OPEN c_conditions;
1128 CLOSE c_conditions;
1129
1130 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1131 trace(p_msg => 'lock c_mapping_sets',
1132 p_module => l_log_module,
1133 p_level => C_LEVEL_STATEMENT);
1134 END IF;
1135
1136 OPEN c_mapping_sets;
1137 CLOSE c_mapping_sets;
1138
1139 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1140 trace(p_msg => 'lock c_mapping_set_values',
1141 p_module => l_log_module,
1142 p_level => C_LEVEL_STATEMENT);
1143 END IF;
1144
1145 OPEN c_mapping_set_values;
1146 CLOSE c_mapping_set_values;
1147
1148 /*IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1149 trace(p_msg => 'lock c_analytical_hdrs',
1150 p_module => l_log_module,
1151 p_level => C_LEVEL_STATEMENT);
1152 END IF;*/
1153
1154 /*OPEN c_analytical_hdrs;
1155 CLOSE c_analytical_hdrs;*/
1156
1157 /* IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1158 trace(p_msg => 'lock c_analytical_dtls',
1159 p_module => l_log_module,
1160 p_level => C_LEVEL_STATEMENT);
1161 END IF;
1162
1163 OPEN c_analytical_dtls;
1164 CLOSE c_analytical_dtls;*/
1165
1166 /*IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1167 trace(p_msg => 'lock c_analytical_sources',
1168 p_module => l_log_module,
1169 p_level => C_LEVEL_STATEMENT);
1170 END IF;
1171
1172 OPEN c_analytical_sources;
1173 CLOSE c_analytical_sources;*/
1174
1178 p_level => C_LEVEL_PROCEDURE);
1175 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1176 trace(p_msg => 'END of procedure lock_area',
1177 p_module => l_log_module,
1179 END IF;
1180 RETURN l_retcode;
1181 EXCEPTION
1182 WHEN xla_exceptions_pkg.application_exception THEN
1183 RAISE;
1184
1185 WHEN OTHERS THEN
1186 RETURN 'ERROR';
1187
1188 END lock_area;
1189
1190 --=============================================================================
1191 --
1192 -- Name: get_staging_context_code
1193 -- Description: This API retrieves the staging context code of an AMB context.
1194 -- If it does not already have one, one is created.
1195 --
1196 --=============================================================================
1197 FUNCTION get_staging_context_code
1198 (p_application_id INTEGER
1199 ,p_amb_context_code VARCHAR2)
1200 RETURN VARCHAR2
1201 IS
1202 CURSOR c IS
1203 SELECT staging_amb_context_code
1204 FROM xla_appli_amb_contexts
1205 WHERE application_id = p_application_id
1206 AND amb_context_code = p_amb_context_code;
1207
1208 l_temp_code VARCHAR2(80);
1209 l_staging_amb_context_code VARCHAR2(30);
1210
1211 CURSOR c_exists IS
1212 SELECT 1
1213 FROM xla_appli_amb_contexts
1214 WHERE staging_amb_context_code = l_staging_amb_context_code;
1215
1216 l_log_module VARCHAR2(240);
1217 BEGIN
1218 IF g_log_enabled THEN
1219 l_log_module := C_DEFAULT_MODULE||'.get_staging_context_code';
1220 END IF;
1221
1222 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1223 trace(p_msg => 'BEGIN of procedure get_staging_context_code: '||
1224 'p_amb_context_code = '||p_amb_context_code,
1225 p_module => l_log_module,
1226 p_level => C_LEVEL_PROCEDURE);
1227 END IF;
1228
1229 OPEN c;
1230 FETCH c INTO l_staging_amb_context_code;
1231 CLOSE c;
1232
1233 IF (l_staging_amb_context_code IS NULL) THEN
1234 l_staging_amb_context_code := create_staging_context_code
1235 (p_amb_context_code => p_amb_context_code
1236 ,p_application_id => p_application_id);
1237 END IF;
1238
1239 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1240 trace(p_msg => 'END of procedure get_staging_context_code: '||
1241 'return code = '||l_staging_amb_context_code,
1242 p_module => l_log_module,
1243 p_level => C_LEVEL_PROCEDURE);
1244 END IF;
1245 return l_staging_amb_context_code;
1246 EXCEPTION
1247 WHEN OTHERS THEN
1248 xla_aad_loader_util_pvt.stack_error
1249 (p_appli_s_name => 'XLA'
1250 ,p_msg_name => 'XLA_COMMON_ERROR'
1251 ,p_token_1 => 'LOCATION'
1252 ,p_value_1 => 'xla_aad_loader_util_pvt.get_staging_context_code'
1253 ,p_token_2 => 'ERROR'
1254 ,p_value_2 => 'unhandled exception');
1255 RAISE;
1256
1257 END get_staging_context_code;
1258
1259 --=============================================================================
1260 --
1261 -- Name: merge_history
1262 -- Description:
1263 --
1264 --=============================================================================
1265 PROCEDURE merge_history
1266 (p_application_id INTEGER
1267 ,p_staging_context_code VARCHAR2)
1268 IS
1269 l_log_module VARCHAR2(240);
1270 BEGIN
1271 IF g_log_enabled THEN
1272 l_log_module := C_DEFAULT_MODULE||'.merge_history';
1273 END IF;
1274
1275 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1276 trace(p_msg => 'BEGIN of procedure merge_history',
1277 p_module => l_log_module,
1278 p_level => C_LEVEL_PROCEDURE);
1279 END IF;
1280
1281 /*
1282 MERGE INTO xla_aads_h h
1283 USING xla_staging_components_h s
1284 ON (h.application_id = s.application_id
1285 AND h.product_rule_type_code = s.component_owner_code
1286 AND h.product_rule_code = s.component_code
1287 AND h.version_num = s.version_num
1288 AND s.application_id = p_application_id
1289 AND s.staging_amb_context_code = p_staging_context_code
1290 AND s.component_type_code = 'AAD')
1291 WHEN MATCHED THEN
1292 UPDATE SET base_version_num = s.base_version_num
1293 , user_version = s.user_version
1294 , version_comment = s.version_comment
1295 , leapfrog_flag = s.leapfrog_flag;
1296 */
1297
1298 UPDATE xla_aads_h h
1299 SET (base_version_num
1300 ,user_version
1301 ,version_comment
1302 ,leapfrog_flag) =
1303 (SELECT NVL(s.base_version_num, h2.base_version_num)
1304 ,NVL(s.product_rule_version, h2.user_version)
1305 ,NVL(s.version_comment, h2.version_comment)
1306 ,NVL(s.leapfrog_flag, h2.leapfrog_flag)
1307 FROM xla_aads_h h2
1308 , xla_staging_components_h s
1309 WHERE h.application_id = h2.application_id
1310 AND h.product_rule_type_code = h2.product_rule_type_code
1311 AND h.product_rule_code = h2.product_rule_code
1315 AND h2.product_rule_code = s.component_code(+)
1312 AND h.version_num = h2.version_num
1313 AND h2.application_id = s.application_id(+)
1314 AND h2.product_rule_type_code = s.component_owner_code(+)
1316 AND h2.version_num = s.version_num(+)
1317 AND s.application_id(+) = p_application_id
1318 AND s.staging_amb_context_code(+) = p_staging_context_code
1319 AND s.component_type_code(+) = 'AAD');
1320
1321
1322 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1323 trace(p_msg => '# xla_aads_h updated = '||SQL%ROWCOUNT,
1324 p_module => l_log_module,
1325 p_level => C_LEVEL_PROCEDURE);
1326 END IF;
1327
1328 INSERT INTO xla_aads_h
1329 (application_id
1330 ,product_rule_type_code
1331 ,product_rule_code
1332 ,version_num
1333 ,base_version_num
1334 ,user_version
1335 ,version_comment
1336 ,leapfrog_flag
1337 ,object_version_number
1338 ,creation_date
1339 ,created_by
1340 ,last_update_date
1341 ,last_updated_by
1342 ,last_update_login
1343 ,program_update_date
1344 ,program_application_id
1345 ,program_id
1346 ,request_id)
1347 SELECT p_application_id
1348 ,s.component_owner_code
1349 ,s.component_code
1350 ,s.version_num
1351 ,s.base_version_num
1352 ,s.product_rule_version
1353 ,s.version_comment
1354 ,s.leapfrog_flag
1355 ,1
1356 ,sysdate
1357 ,xla_environment_pkg.g_usr_id
1358 ,sysdate
1359 ,xla_environment_pkg.g_usr_id
1360 ,xla_environment_pkg.g_login_id
1361 ,sysdate
1362 ,xla_environment_pkg.g_prog_appl_id
1363 ,xla_environment_pkg.g_prog_id
1364 ,xla_environment_pkg.g_req_Id
1365 FROM xla_staging_components_h s
1366 WHERE s.application_id = p_application_id
1367 AND s.staging_amb_context_code = p_staging_context_code
1368 AND s.component_type_code = 'AAD'
1369 AND NOT EXISTS
1370 (SELECT 1
1371 FROM xla_aads_h h
1372 WHERE h.application_id = s.application_id
1373 AND h.product_rule_type_code = s.component_owner_code
1374 AND h.product_rule_code = s.component_code
1375 AND h.version_num = s.version_num);
1376
1377 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1378 trace(p_msg => '# xla_aads_h inserted = '||SQL%ROWCOUNT,
1379 p_module => l_log_module,
1380 p_level => C_LEVEL_PROCEDURE);
1381 END IF;
1382
1383 /*
1384 MERGE INTO xla_amb_components_h h
1385 USING xla_staging_components_h s
1386 ON (h.component_type_code = s.component_type_code
1387 AND h.component_owner_code = s.component_owner_code
1388 AND h.component_code = s.component_code
1389 AND h.version_num = s.version_num
1390 AND s.staging_amb_context_code = p_staging_context_code
1391 AND s.component_type_code <> 'AAD')
1392 WHEN MATCHED THEN
1393 UPDATE SET base_version_num = s.base_version_num;
1394 */
1395
1396 UPDATE xla_amb_components_h h
1397 SET base_version_num =
1398 (SELECT nvl(s.base_version_num, h2.base_version_num)
1399 FROM xla_amb_components_h h2
1400 , xla_staging_components_h s
1401 WHERE h.component_type_code = h2.component_type_code
1402 AND h.component_owner_code = h2.component_owner_code
1403 AND h.component_code = h2.component_code
1404 AND h.application_id = h2.application_id
1405 AND h.version_num = h2.version_num
1406 AND h2.component_type_code = s.component_type_code(+)
1407 AND h2.component_owner_code = s.component_owner_code(+)
1408 AND h2.component_code = s.component_code(+)
1409 AND h2.application_id = s.application_id(+)
1410 AND h2.version_num = s.version_num(+)
1411 AND s.staging_amb_context_code(+) = p_staging_context_code
1412 AND s.component_type_code(+) <> 'AAD');
1413
1414 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1415 trace(p_msg => '# xla_amb_components_h updated = '||SQL%ROWCOUNT,
1416 p_module => l_log_module,
1417 p_level => C_LEVEL_PROCEDURE);
1418 END IF;
1419
1420 INSERT INTO xla_amb_components_h h
1421 (component_type_code
1422 ,component_owner_code
1423 ,component_code
1424 ,application_id
1425 ,version_num
1426 ,base_version_num
1427 ,leapfrog_flag
1428 ,object_version_number
1429 ,creation_date
1430 ,created_by
1434 ,program_update_date
1431 ,last_update_date
1432 ,last_updated_by
1433 ,last_update_login
1435 ,program_application_id
1436 ,program_id
1437 ,request_id)
1438 SELECT
1439 s.component_type_code
1440 ,s.component_owner_code
1441 ,s.component_code
1442 ,NVL(s.application_id,-1)
1443 ,s.version_num
1444 ,s.base_version_num
1445 ,s.leapfrog_flag
1446 ,1
1447 ,sysdate
1448 ,xla_environment_pkg.g_usr_id
1449 ,sysdate
1450 ,xla_environment_pkg.g_usr_id
1451 ,xla_environment_pkg.g_login_id
1452 ,sysdate
1453 ,xla_environment_pkg.g_prog_appl_id
1454 ,xla_environment_pkg.g_prog_id
1455 ,xla_environment_pkg.g_req_Id
1456 FROM xla_staging_components_h s
1457 WHERE s.staging_amb_context_code = p_staging_context_code
1458 AND s.component_type_code <> 'AAD'
1459 AND NOT EXISTS
1460 (SELECT 1
1461 FROM xla_amb_components_h h
1462 WHERE h.component_type_code = s.component_type_code
1463 AND h.component_owner_code = s.component_owner_code
1464 AND h.component_code = s.component_code
1465 AND h.application_id = NVL(s.application_id,-1)
1466 AND h.version_num = s.version_num);
1467
1468 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1469 trace(p_msg => '# xla_amb_components_h inserted = '||SQL%ROWCOUNT,
1470 p_module => l_log_module,
1471 p_level => C_LEVEL_PROCEDURE);
1472 END IF;
1473
1474 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1475 trace(p_msg => 'END of procedure merge_history',
1476 p_module => l_log_module,
1477 p_level => C_LEVEL_PROCEDURE);
1478 END IF;
1479
1480 EXCEPTION
1481 WHEN OTHERS THEN
1482 xla_aad_loader_util_pvt.stack_error
1483 (p_appli_s_name => 'XLA'
1484 ,p_msg_name => 'XLA_COMMON_ERROR'
1485 ,p_token_1 => 'LOCATION'
1486 ,p_value_1 => 'xla_aad_loader_util_pvt.merge_history'
1487 ,p_token_2 => 'ERROR'
1488 ,p_value_2 => 'unhandled exception');
1489 RAISE;
1490
1491 END merge_history;
1492
1493 --=============================================================================
1494 --
1495 -- Name: get_segment
1496 -- Description:
1497 --
1498 --=============================================================================
1499 FUNCTION get_segment
1500 (p_chart_of_accounts_id INTEGER
1501 ,p_code_combination_id INTEGER
1502 ,p_segment_num INTEGER)
1503 RETURN VARCHAR2
1504 IS
1505 l_num_segments INTEGER;
1506 l_ret_segment VARCHAR2(25);
1507 l_seg FND_FLEX_EXT.SegmentArray;
1508
1509 l_log_module VARCHAR2(240);
1510 BEGIN
1511 IF g_log_enabled THEN
1512 l_log_module := C_DEFAULT_MODULE||'.get_segment: '||p_segment_num;
1513 END IF;
1514
1515 IF ( FND_FLEX_EXT.get_segments(
1516 application_short_name => 'SQLGL',
1517 key_flex_code => 'GL#',
1518 structure_number => p_chart_of_accounts_id,
1519 combination_id => p_code_combination_id,
1520 n_segments => l_num_segments,
1521 segments => l_seg) = FALSE) THEN
1522 IF (C_LEVEL_ERROR >= g_log_level) THEN
1523 trace(p_msg => 'Cannot get segment: FND_FLEX_EXT.get_segments',
1524 p_module => l_log_module,
1525 p_level => C_LEVEL_ERROR);
1526 END IF;
1527 l_ret_segment := NULL;
1528 ELSE
1529 BEGIN
1530 l_ret_segment := l_seg(p_segment_num);
1531 EXCEPTION
1532 WHEN OTHERS THEN
1533 l_ret_segment := NULL;
1534 END;
1535 END IF;
1536
1537 RETURN l_ret_segment;
1538
1539 EXCEPTION
1540 WHEN OTHERS THEN
1541 xla_aad_loader_util_pvt.stack_error
1542 (p_appli_s_name => 'XLA'
1543 ,p_msg_name => 'XLA_COMMON_ERROR'
1544 ,p_token_1 => 'LOCATION'
1545 ,p_value_1 => 'xla_aad_loader_util_pvt.get_segment'
1546 ,p_token_2 => 'ERROR'
1547 ,p_value_2 => 'unhandled exception');
1548 RAISE;
1549
1550 END get_segment;
1551
1552 --=============================================================================
1553 --
1554 -- Name: reset_errors
1555 -- Description: This API deletes the error from the log table and
1556 -- resets the error stack
1557 --
1558 --=============================================================================
1559 PROCEDURE reset_errors
1560 (p_application_id INTEGER
1561 ,p_amb_context_code VARCHAR2
1562 ,p_request_code VARCHAR2)
1563 IS
1564 PRAGMA AUTONOMOUS_TRANSACTION;
1565
1566 l_log_module VARCHAR2(240);
1567 BEGIN
1568 IF g_log_enabled THEN
1572 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1569 l_log_module := C_DEFAULT_MODULE||'.reset_errors';
1570 END IF;
1571
1573 trace(p_msg => 'BEGIN of procedure reset_errors:'||
1574 ' p_application_id = '||p_application_id||
1575 ', p_amb_context_code = '||p_amb_context_code||
1576 ', p_request_code = '||p_request_code,
1577 p_module => l_log_module,
1578 p_level => C_LEVEL_PROCEDURE);
1579 END IF;
1580
1581 DELETE FROM xla_aad_loader_logs
1582 WHERE application_id = p_application_id
1583 AND amb_context_code = p_amb_context_code
1584 AND request_code = p_request_code;
1585
1586 IF (C_LEVEL_EVENT >= g_log_level) THEN
1587 trace(p_msg => '# row deleted into xla_aad_loader_logs = '||SQL%ROWCOUNT,
1588 p_module => l_log_module,
1589 p_level => C_LEVEL_EVENT);
1590 END IF;
1591
1592 COMMIT;
1593
1594 g_err_count := 0;
1595 g_err_nums.DELETE;
1596 g_err_msgs.DELETE;
1597
1598 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1599 trace(p_msg => 'END of procedure reset_errors',
1600 p_module => l_log_module,
1601 p_level => C_LEVEL_PROCEDURE);
1602 END IF;
1603 EXCEPTION
1604 WHEN OTHERS THEN
1605 xla_aad_loader_util_pvt.stack_error
1606 (p_appli_s_name => 'XLA'
1607 ,p_msg_name => 'XLA_COMMON_ERROR'
1608 ,p_token_1 => 'LOCATION'
1609 ,p_value_1 => 'xla_aad_loader_util_pvt.reset_errors'
1610 ,p_token_2 => 'ERROR'
1611 ,p_value_2 => 'unhandled exception');
1612 RAISE;
1613
1614 END reset_errors;
1615
1616
1617 --=============================================================================
1618 --
1619 -- Name: stack_errors
1620 -- Description: This API stacks the error to the error array
1621 --
1622 --=============================================================================
1623 PROCEDURE stack_error
1624 (p_appli_s_name VARCHAR2
1625 ,p_msg_name VARCHAR2)
1626 IS
1627 BEGIN
1628 stack_error
1629 (p_appli_s_name => p_appli_s_name
1630 ,p_msg_name => p_msg_name
1631 ,p_token_1 => NULL
1632 ,p_value_1 => NULL);
1633 END;
1634
1635 PROCEDURE stack_error
1636 (p_appli_s_name VARCHAR2
1637 ,p_msg_name VARCHAR2
1638 ,p_token_1 VARCHAR2
1639 ,p_value_1 VARCHAR2)
1640 IS
1641 BEGIN
1642 stack_error
1643 (p_appli_s_name => p_appli_s_name
1644 ,p_msg_name => p_msg_name
1645 ,p_token_1 => p_token_1
1646 ,p_value_1 => p_value_1
1647 ,p_token_2 => NULL
1648 ,p_value_2 => NULL);
1649 END;
1650
1651 PROCEDURE stack_error
1652 (p_appli_s_name VARCHAR2
1653 ,p_msg_name VARCHAR2
1654 ,p_token_1 VARCHAR2
1655 ,p_value_1 VARCHAR2
1656 ,p_token_2 VARCHAR2
1657 ,p_value_2 VARCHAR2)
1658 IS
1659 BEGIN
1660 stack_error
1661 (p_appli_s_name => p_appli_s_name
1662 ,p_msg_name => p_msg_name
1663 ,p_token_1 => p_token_1
1664 ,p_value_1 => p_value_1
1665 ,p_token_2 => p_token_2
1666 ,p_value_2 => p_value_2
1667 ,p_token_3 => NULL
1668 ,p_value_3 => NULL);
1669 END;
1670
1671 PROCEDURE stack_error
1672 (p_appli_s_name VARCHAR2
1673 ,p_msg_name VARCHAR2
1674 ,p_token_1 VARCHAR2
1675 ,p_value_1 VARCHAR2
1676 ,p_token_2 VARCHAR2
1677 ,p_value_2 VARCHAR2
1678 ,p_token_3 VARCHAR2
1679 ,p_value_3 VARCHAR2)
1680 IS
1681 BEGIN
1682 stack_error
1683 (p_appli_s_name => p_appli_s_name
1684 ,p_msg_name => p_msg_name
1685 ,p_token_1 => p_token_1
1686 ,p_value_1 => p_value_1
1687 ,p_token_2 => p_token_2
1688 ,p_value_2 => p_value_2
1689 ,p_token_3 => p_token_3
1690 ,p_value_3 => p_value_3
1691 ,p_token_4 => NULL
1692 ,p_value_4 => NULL);
1693 END;
1694
1695 PROCEDURE stack_error
1696 (p_appli_s_name VARCHAR2
1697 ,p_msg_name VARCHAR2
1698 ,p_token_1 VARCHAR2
1699 ,p_value_1 VARCHAR2
1700 ,p_token_2 VARCHAR2
1701 ,p_value_2 VARCHAR2
1702 ,p_token_3 VARCHAR2
1703 ,p_value_3 VARCHAR2
1704 ,p_token_4 VARCHAR2
1705 ,p_value_4 VARCHAR2)
1706 IS
1707 l_msg_number INTEGER;
1708 l_log_module VARCHAR2(240);
1709 BEGIN
1710 IF g_log_enabled THEN
1711 l_log_module := C_DEFAULT_MODULE||'.stack_error';
1712 END IF;
1713
1714 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1715 trace(p_msg => 'BEGIN of procedure stack_error'
1716 ,p_level => C_LEVEL_PROCEDURE
1717 ,p_module => l_log_module);
1718 trace(p_msg => 'p_appli_s_name = '||p_appli_s_name
1722 ,p_level => C_LEVEL_PROCEDURE
1719 ,p_level => C_LEVEL_PROCEDURE
1720 ,p_module => l_log_module);
1721 trace(p_msg => 'p_msg_name = '||p_msg_name
1723 ,p_module => l_log_module);
1724 END IF;
1725
1726 IF (p_token_4 IS NOT NULL and p_value_4 IS NOT NULL) THEN
1727 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1728 trace(p_msg => 'p_token_1 = '||p_token_1
1729 ,p_level => C_LEVEL_PROCEDURE
1730 ,p_module => l_log_module);
1731 trace(p_msg => 'p_value_1 = '||p_value_1
1732 ,p_level => C_LEVEL_PROCEDURE
1733 ,p_module => l_log_module);
1734 trace(p_msg => 'p_token_2 = '||p_token_2
1735 ,p_level => C_LEVEL_PROCEDURE
1736 ,p_module => l_log_module);
1737 trace(p_msg => 'p_value_2 = '||p_value_2
1738 ,p_level => C_LEVEL_PROCEDURE
1739 ,p_module => l_log_module);
1740 trace(p_msg => 'p_token_3 = '||p_token_3
1741 ,p_level => C_LEVEL_PROCEDURE
1742 ,p_module => l_log_module);
1743 trace(p_msg => 'p_value_3 = '||p_value_3
1744 ,p_level => C_LEVEL_PROCEDURE
1745 ,p_module => l_log_module);
1746 trace(p_msg => 'p_token_4 = '||p_token_4
1747 ,p_level => C_LEVEL_PROCEDURE
1748 ,p_module => l_log_module);
1749 trace(p_msg => 'p_value_4 = '||p_value_4
1750 ,p_level => C_LEVEL_PROCEDURE
1751 ,p_module => l_log_module);
1752 END IF;
1753
1754 xla_messages_pkg.build_message
1755 (p_appli_s_name => p_appli_s_name
1756 ,p_msg_name => p_msg_name
1757 ,p_token_1 => p_token_1
1758 ,p_value_1 => p_value_1
1759 ,p_token_2 => p_token_2
1760 ,p_value_2 => p_value_2
1761 ,p_token_3 => p_token_3
1762 ,p_value_3 => p_value_3
1763 ,p_token_4 => p_token_4
1764 ,p_value_4 => p_value_4);
1765
1766 ELSIF (p_token_3 IS NOT NULL and p_value_3 IS NOT NULL) THEN
1767 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1768 trace(p_msg => 'p_token_1 = '||p_token_1
1769 ,p_level => C_LEVEL_PROCEDURE
1770 ,p_module => l_log_module);
1771 trace(p_msg => 'p_value_1 = '||p_value_1
1772 ,p_level => C_LEVEL_PROCEDURE
1773 ,p_module => l_log_module);
1774 trace(p_msg => 'p_token_2 = '||p_token_2
1775 ,p_level => C_LEVEL_PROCEDURE
1776 ,p_module => l_log_module);
1777 trace(p_msg => 'p_value_2 = '||p_value_2
1778 ,p_level => C_LEVEL_PROCEDURE
1779 ,p_module => l_log_module);
1780 trace(p_msg => 'p_token_3 = '||p_token_3
1781 ,p_level => C_LEVEL_PROCEDURE
1782 ,p_module => l_log_module);
1783 trace(p_msg => 'p_value_3 = '||p_value_3
1784 ,p_level => C_LEVEL_PROCEDURE
1785 ,p_module => l_log_module);
1786 END IF;
1787 xla_messages_pkg.build_message
1788 (p_appli_s_name => p_appli_s_name
1789 ,p_msg_name => p_msg_name
1790 ,p_token_1 => p_token_1
1791 ,p_value_1 => p_value_1
1792 ,p_token_2 => p_token_2
1793 ,p_value_2 => p_value_2
1794 ,p_token_3 => p_token_3
1795 ,p_value_3 => p_value_3);
1796
1797 ELSIF (p_token_2 IS NOT NULL and p_value_2 IS NOT NULL) THEN
1798 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1799 trace(p_msg => 'p_token_1 = '||p_token_1
1800 ,p_level => C_LEVEL_PROCEDURE
1801 ,p_module => l_log_module);
1802 trace(p_msg => 'p_value_1 = '||p_value_1
1803 ,p_level => C_LEVEL_PROCEDURE
1804 ,p_module => l_log_module);
1805 trace(p_msg => 'p_token_2 = '||p_token_2
1806 ,p_level => C_LEVEL_PROCEDURE
1807 ,p_module => l_log_module);
1808 trace(p_msg => 'p_value_2 = '||p_value_2
1809 ,p_level => C_LEVEL_PROCEDURE
1810 ,p_module => l_log_module);
1811 END IF;
1812
1813 xla_messages_pkg.build_message
1814 (p_appli_s_name => p_appli_s_name
1815 ,p_msg_name => p_msg_name
1816 ,p_token_1 => p_token_1
1817 ,p_value_1 => p_value_1
1818 ,p_token_2 => p_token_2
1819 ,p_value_2 => p_value_2);
1820
1821 ELSIF (p_token_1 IS NOT NULL and p_value_1 IS NOT NULL) THEN
1822 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1823 trace(p_msg => 'p_token_1 = '||p_token_1
1824 ,p_level => C_LEVEL_PROCEDURE
1825 ,p_module => l_log_module);
1826 trace(p_msg => 'p_value_1 = '||p_value_1
1827 ,p_level => C_LEVEL_PROCEDURE
1828 ,p_module => l_log_module);
1829 END IF;
1830
1831 xla_messages_pkg.build_message
1835 ,p_value_1 => p_value_1);
1832 (p_appli_s_name => p_appli_s_name
1833 ,p_msg_name => p_msg_name
1834 ,p_token_1 => p_token_1
1836
1837 ELSE
1838 xla_messages_pkg.build_message
1839 (p_appli_s_name => p_appli_s_name
1840 ,p_msg_name => p_msg_name);
1841 END IF;
1842
1843 l_msg_number := fnd_message.get_number
1844 (appin => p_appli_s_name
1845 ,namein => p_msg_name);
1846
1847 g_err_count := g_err_count + 1;
1848 g_err_msgs(g_err_count) := fnd_message.get();
1849 g_err_nums(g_err_count) := l_msg_number;
1850
1851 IF (C_LEVEL_ERROR >= g_log_level) THEN
1852 trace(p_msg => 'g_err_count = '||g_err_count
1853 ,p_level => C_LEVEL_ERROR
1854 ,p_module => l_log_module);
1855 trace(p_msg => 'g_err_msgs(g_err_count) = '||g_err_msgs(g_err_count)
1856 ,p_level => C_LEVEL_ERROR
1857 ,p_module => l_log_module);
1858 trace(p_msg => 'g_err_nums(g_err_count) = '||g_err_nums(g_err_count)
1859 ,p_level => C_LEVEL_ERROR
1860 ,p_module => l_log_module);
1861 END IF;
1862
1863 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1864 trace(p_msg => 'END of procedure stack_error'
1865 ,p_module => l_log_module
1866 ,p_level => C_LEVEL_PROCEDURE);
1867 END IF;
1868 EXCEPTION
1869 WHEN xla_exceptions_pkg.application_exception THEN
1870 RAISE;
1871
1872 WHEN OTHERS THEN
1873 xla_exceptions_pkg.raise_message
1874 (p_location => 'xla_aad_loader_util_pvt.stack_error');
1875
1876 END stack_error;
1877
1878 --=============================================================================
1879 --
1880 -- Name: insert_errors
1881 -- Description: This API inserts the errors from the array to the error table
1882 --
1883 --=============================================================================
1884 PROCEDURE insert_errors
1885 (p_application_id INTEGER
1886 ,p_amb_context_code VARCHAR2
1887 ,p_request_code VARCHAR2)
1888 IS
1889 l_log_module VARCHAR2(240);
1890 BEGIN
1891 IF g_log_enabled THEN
1892 l_log_module := C_DEFAULT_MODULE||'.insert_errors';
1893 END IF;
1894
1895 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1896 trace(p_msg => 'BEGIN of procedure insert_errors'
1897 ,p_level => C_LEVEL_PROCEDURE
1898 ,p_module => l_log_module);
1899 END IF;
1900
1901 FORALL i IN 1 .. g_err_msgs.COUNT
1902 INSERT INTO xla_aad_loader_logs
1903 (aad_loader_log_id
1904 ,amb_context_code
1905 ,application_id
1906 ,request_code
1907 ,log_type_code
1908 ,encoded_message
1909 ,message_num
1910 ,object_version_number
1911 ,creation_date
1912 ,created_by
1913 ,last_update_date
1914 ,last_updated_by
1915 ,last_update_login
1916 ,program_update_date
1917 ,program_application_id
1918 ,program_id
1919 ,request_id)
1920 VALUES
1921 (xla_aad_loader_logs_s.nextval
1922 ,p_amb_context_code
1923 ,p_application_id
1924 ,p_request_code
1925 ,'ERROR'
1926 ,g_err_msgs(i)
1927 ,g_err_nums(i)
1928 ,1
1929 ,sysdate
1930 ,xla_environment_pkg.g_usr_id
1931 ,sysdate
1932 ,xla_environment_pkg.g_usr_id
1933 ,xla_environment_pkg.g_login_id
1934 ,sysdate
1935 ,xla_environment_pkg.g_prog_appl_id
1936 ,xla_environment_pkg.g_prog_id
1937 ,xla_environment_pkg.g_req_Id);
1938
1939 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1940 trace(p_msg => '# errors inserted into xla_aad_loader_logs = '||SQL%ROWCOUNT
1941 ,p_module => l_log_module
1942 ,p_level => C_LEVEL_STATEMENT);
1943 END IF;
1944
1945 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1946 trace(p_msg => 'END of procedure insert_errors'
1947 ,p_module => l_log_module
1948 ,p_level => C_LEVEL_PROCEDURE);
1949 END IF;
1950 EXCEPTION
1951 WHEN OTHERS THEN
1952 xla_aad_loader_util_pvt.stack_error
1953 (p_appli_s_name => 'XLA'
1954 ,p_msg_name => 'XLA_COMMON_ERROR'
1955 ,p_token_1 => 'LOCATION'
1956 ,p_value_1 => 'xla_aad_loader_util_pvt.insert_errors'
1957 ,p_token_2 => 'ERROR'
1958 ,p_value_2 => 'unhandled exception');
1959 RAISE;
1960
1961 END insert_errors;
1962
1963 --=============================================================================
1964 --
1965 -- Name: wait_for_request
1966 -- Description: This API waits for the Upload Application Accounting
1967 -- Definitions request to be completed
1968 --
1969 --=============================================================================
1970 FUNCTION wait_for_request
1971 (p_req_id INTEGER)
1972 RETURN VARCHAR2
1973 IS
1974 l_btemp BOOLEAN;
1975 l_phase VARCHAR2(30);
1979 l_message VARCHAR2(240);
1976 l_status VARCHAR2(30);
1977 l_dphase VARCHAR2(30);
1978 l_dstatus VARCHAR2(30);
1980 l_retcode VARCHAR2(30);
1981 l_log_module VARCHAR2(240);
1982 BEGIN
1983 IF g_log_enabled THEN
1984 l_log_module := C_DEFAULT_MODULE||'.wait_for_request';
1985 END IF;
1986
1987 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1988 trace(p_msg => 'BEGIN of function wait_for_request',
1989 p_module => l_log_module,
1990 p_level => C_LEVEL_PROCEDURE);
1991 END IF;
1992
1993 l_retcode := 'SUCCESS';
1994
1995 l_btemp := fnd_concurrent.wait_for_request
1996 (request_id => p_req_id
1997 ,interval => 30
1998 ,phase => l_phase
1999 ,status => l_status
2000 ,dev_phase => l_dphase
2001 ,dev_status => l_dstatus
2002 ,message => l_message);
2003
2004 IF NOT l_btemp THEN
2005 IF (C_LEVEL_ERROR>= g_log_level) THEN
2006 trace(p_msg => 'FND_CONCURRENT.WAIT_FOR_REQUEST returned FALSE'
2007 ,p_level => C_LEVEL_ERROR
2008 ,p_module => l_log_module);
2009 END IF;
2010
2011 l_retcode := 'ERROR';
2012 xla_aad_loader_util_pvt.stack_error
2013 (p_appli_s_name => 'XLA'
2014 ,p_msg_name => 'XLA_COMMON_ERROR'
2015 ,p_token_1 => 'LOCATION'
2016 ,p_value_1 => 'xla_aad_loader_util_pvt.wait_for_request'
2017 ,p_token_2 => 'ERROR'
2018 ,p_value_2 =>
2019 'Technical problem : FND_CONCURRENT.WAIT_FOR_REQUEST returned FALSE');
2020 ELSE
2021 IF (C_LEVEL_EVENT >= g_log_level) THEN
2022 trace(p_msg => 'request completed with status = '||l_status
2023 ,p_level => C_LEVEL_EVENT
2024 ,p_module => l_log_module);
2025 END IF;
2026
2027 -- If the return code is 'NORMAL', return SUCCESS
2028 -- For all other status other than WARNING, return ERROR
2029 IF (l_dstatus = 'NORMAL') THEN
2030 l_retcode := 'SUCCESS';
2031 ELSIF (l_dstatus = 'WARNING') THEN
2032 l_retcode := 'WARNING';
2033 ELSE
2034 l_retcode := 'ERROR';
2035 END IF;
2036
2037 END IF;
2038
2039 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2040 trace(p_msg => 'END of function wait_for_request : Return Code = '||l_retcode,
2041 p_module => l_log_module,
2042 p_level => C_LEVEL_PROCEDURE);
2043 END IF;
2044 return l_retcode;
2045 EXCEPTION
2046 WHEN OTHERS THEN
2047 xla_aad_loader_util_pvt.stack_error
2048 (p_appli_s_name => 'XLA'
2049 ,p_msg_name => 'XLA_COMMON_ERROR'
2050 ,p_token_1 => 'LOCATION'
2051 ,p_value_1 => 'xla_aad_loader_util_pvt.wait_for_request'
2052 ,p_token_2 => 'ERROR'
2053 ,p_value_2 => 'unhandled exception');
2054 RAISE;
2055
2056 END wait_for_request;
2057
2058 --=============================================================================
2059 --
2060 -- Name: submit_compile_report_request
2061 -- Description:
2062 --
2063 --=============================================================================
2064 FUNCTION submit_compile_report_request
2065 (p_application_id IN INTEGER)
2066 RETURN INTEGER
2067 IS
2068 PRAGMA AUTONOMOUS_TRANSACTION;
2069
2070 l_req_id INTEGER;
2071 l_log_module VARCHAR2(240);
2072 BEGIN
2073 IF g_log_enabled THEN
2074 l_log_module := C_DEFAULT_MODULE||'.submit_compile_report_request';
2075 END IF;
2076
2077 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2078 trace(p_msg => 'BEGIN of procedure submit_compile_report_request',
2079 p_module => l_log_module,
2080 p_level => C_LEVEL_PROCEDURE);
2081 END IF;
2082
2083 l_req_id := fnd_request.submit_request
2084 (application => 'XLA'
2085 ,program => 'XLAABACR'
2086 ,argument1 => NULL
2087 ,argument2 => NULL
2088 ,argument3 => TO_CHAR(p_application_id)
2089 ,argument4 => 'Y'
2090 ,argument5 => NULL
2091 ,argument6 => NULL
2092 ,argument7 => 'N');
2093 COMMIT;
2094
2095 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2096 trace(p_msg => 'END of function submit_compile_report_request - request id = '||l_req_id,
2097 p_module => l_log_module,
2098 p_level => C_LEVEL_PROCEDURE);
2099 END IF;
2100
2101 RETURN l_req_id;
2102 EXCEPTION
2103 WHEN OTHERS THEN
2104 xla_aad_loader_util_pvt.stack_error
2105 (p_appli_s_name => 'XLA'
2106 ,p_msg_name => 'XLA_COMMON_ERROR'
2107 ,p_token_1 => 'LOCATION'
2108 ,p_value_1 => 'xla_aad_loader_util_pvt.submit_compile_report_request'
2109 ,p_token_2 => 'ERROR'
2110 ,p_value_2 => 'unhandled exception');
2111 RAISE;
2112
2113 END;
2114
2115 --=============================================================================
2116 --
2117 -- Name: compile
2121 FUNCTION compile
2118 -- Description: This API compiles all AADs for an application in an AMB context
2119 --
2120 --=============================================================================
2122 (p_amb_context_code IN VARCHAR2
2123 ,p_application_id IN INTEGER)
2124 RETURN BOOLEAN
2125 IS
2126 l_req_id INTEGER;
2127 l_retcode BOOLEAN;
2128 l_log_module VARCHAR2(240);
2129 BEGIN
2130 IF g_log_enabled THEN
2131 l_log_module := C_DEFAULT_MODULE||'.compile';
2132 END IF;
2133
2134 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2135 trace(p_msg => 'BEGIN of procedure compile',
2136 p_module => l_log_module,
2137 p_level => C_LEVEL_PROCEDURE);
2138 END IF;
2139
2140 -- Initialize global variables
2141 l_retcode := TRUE;
2142
2143 -- Compile each AADs and recorded its compilation and validation statuses
2144 l_req_id := submit_compile_report_request
2145 (p_application_id => p_application_id);
2146
2147 IF (l_req_id = 0) THEN
2148 l_retcode := FALSE;
2149 xla_aad_loader_util_pvt.stack_error
2150 (p_appli_s_name => 'XLA'
2151 ,p_msg_name => 'XLA_COMMON_ERROR'
2152 ,p_token_1 => 'LOCATION'
2153 ,p_value_1 => 'xla_aad_loader_util_pvt.compile'
2154 ,p_token_2 => 'ERROR'
2155 ,p_value_2 => 'Unable to submit compilation report request');
2156 END IF;
2157
2158 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2159 trace(p_msg => 'END of function compile - l_retcode = '||
2160 CASE WHEN l_retcode THEN 'TRUE' ELSE 'FALSE' END ,
2161 p_module => l_log_module,
2162 p_level => C_LEVEL_PROCEDURE);
2163 END IF;
2164
2165 RETURN l_retcode;
2166 EXCEPTION
2167
2168 WHEN OTHERS THEN
2169 xla_aad_loader_util_pvt.stack_error
2170 (p_appli_s_name => 'XLA'
2171 ,p_msg_name => 'XLA_COMMON_ERROR'
2172 ,p_token_1 => 'LOCATION'
2173 ,p_value_1 => 'xla_aad_loader_util_pvt.compile'
2174 ,p_token_2 => 'ERROR'
2175 ,p_value_2 => 'unhandled exception');
2176 RAISE;
2177
2178 END;
2179
2180
2181
2182 --=============================================================================
2183 --
2184 -- Name: compatible_api_call
2185 -- Description:
2186 --
2187 --=============================================================================
2188 FUNCTION compatible_api_call
2189 (p_current_version_number NUMBER
2190 ,p_caller_version_number NUMBER
2191 ,p_api_name VARCHAR2
2192 ,p_pkg_name VARCHAR2)
2193 RETURN BOOLEAN
2194 IS
2195 l_error_text VARCHAR2(2000);
2196 l_retcode BOOLEAN;
2197 l_log_module VARCHAR2(240);
2198 BEGIN
2199 IF g_log_enabled THEN
2200 l_log_module := C_DEFAULT_MODULE||'.compatible_api_call';
2201 END IF;
2202
2203 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2204 trace(p_msg => 'BEGIN of procedure compatible_api_call',
2205 p_module => l_log_module,
2206 p_level => C_LEVEL_PROCEDURE);
2207 END IF;
2208
2209 l_retcode := TRUE;
2210 IF (NOT fnd_api.compatible_api_call
2211 (p_current_version_number => p_current_version_number
2212 ,p_caller_version_number => p_caller_version_number
2213 ,p_api_name => p_api_name
2214 ,p_pkg_name => p_pkg_name)) THEN
2215
2216 l_error_text := fnd_msg_pub.get(fnd_msg_pub.G_FIRST, FND_API.G_FALSE);
2217
2218 xla_aad_loader_util_pvt.stack_error
2219 (p_appli_s_name => 'XLA'
2220 ,p_msg_name => 'XLA_COMMON_ERROR'
2221 ,p_token_1 => 'LOCATION'
2222 ,p_value_1 => 'xla_aad_loader_util_pvt.compatible_api_call'
2223 ,p_token_2 => 'ERROR'
2224 ,p_value_2 => l_error_text);
2225
2226 l_retcode := FALSE;
2227 END IF;
2228
2229 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2230 trace(p_msg => 'END of function compatible_api_call',
2231 p_module => l_log_module,
2232 p_level => C_LEVEL_PROCEDURE);
2233 END IF;
2234 RETURN l_retcode;
2235 EXCEPTION
2236 WHEN OTHERS THEN
2237 xla_aad_loader_util_pvt.stack_error
2238 (p_appli_s_name => 'XLA'
2239 ,p_msg_name => 'XLA_COMMON_ERROR'
2240 ,p_token_1 => 'LOCATION'
2241 ,p_value_1 => 'xla_aad_loader_util_pvt.compatible_api_call'
2242 ,p_token_2 => 'ERROR'
2243 ,p_value_2 => 'unhandled exception');
2244 RAISE;
2245
2246 END;
2247
2248 --=============================================================================
2249 --
2250 -- Name: purge_subledger_seed
2251 -- Description: This API purge the SLA-related seed data for the subledger
2252 --
2253 --=============================================================================
2254 PROCEDURE purge_subledger_seed
2255 (p_api_version IN NUMBER
2259 IS
2256 ,x_return_status IN OUT NOCOPY VARCHAR2
2257 ,p_application_id IN INTEGER
2258 )
2260 l_api_name CONSTANT VARCHAR2(30) := 'purge_subledger_seed';
2261 l_api_version CONSTANT NUMBER := 1.0;
2262
2263 l_schema VARCHAR2(30);
2264 l_short_name VARCHAR2(30);
2265 l_status VARCHAR2(30);
2266 l_industry VARCHAR2(30);
2267
2268 l_log_module VARCHAR2(240);
2269 BEGIN
2270 IF g_log_enabled THEN
2271 l_log_module := C_DEFAULT_MODULE||'.purge_subledger_seed';
2272 END IF;
2273
2274 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2275 trace(p_msg => 'BEGIN of function purge_subledger_seed',
2276 p_module => l_log_module,
2277 p_level => C_LEVEL_PROCEDURE);
2278 END IF;
2279
2280 -- Standard call to check for call compatibility.
2281 IF (NOT xla_aad_loader_util_pvt.compatible_api_call
2282 (p_current_version_number => l_api_version
2283 ,p_caller_version_number => p_api_version
2284 ,p_api_name => l_api_name
2285 ,p_pkg_name => C_DEFAULT_MODULE))
2286 THEN
2287 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2288 END IF;
2289
2290 -- Initialize global variables
2291 x_return_status := FND_API.G_RET_STS_SUCCESS;
2292
2293 SELECT application_short_name
2294 INTO l_short_name
2295 FROM fnd_application
2296 WHERE application_id = p_application_id;
2297
2298 DELETE FROM xla_event_mappings_b WHERE application_id = p_application_id;
2299 DELETE FROM xla_event_mappings_tl WHERE event_mapping_id IN
2300 (SELECT event_mapping_id
2301 FROM xla_event_mappings_b
2302 WHERE application_id = p_application_id);
2303
2304 DELETE FROM xla_event_class_grps_b WHERE application_id = p_application_id;
2305 DELETE FROM xla_event_class_grps_tl WHERE application_id = p_application_id;
2306
2307 DELETE FROM xla_entity_id_mappings WHERE application_id = p_application_id;
2308 DELETE FROM xla_event_class_attrs WHERE application_id = p_application_id;
2309 DELETE FROM xla_event_sources WHERE application_id = p_application_id;
2310 DELETE FROM xla_extract_objects WHERE application_id = p_application_id;
2311 DELETE FROM xla_reference_objects WHERE application_id = p_application_id;
2312 DELETE FROM xla_source_params WHERE application_id = p_application_id;
2313 DELETE FROM xla_evt_class_acct_attrs WHERE application_id = p_application_id;
2314
2315 DELETE FROM xla_event_types_tl WHERE application_id = p_application_id;
2316 DELETE FROM xla_event_types_b WHERE application_id = p_application_id;
2317 DELETE FROM xla_event_classes_tl WHERE application_id = p_application_id;
2318 DELETE FROM xla_event_classes_b WHERE application_id = p_application_id;
2319 DELETE FROM xla_entity_types_tl WHERE application_id = p_application_id;
2320 DELETE FROM xla_entity_types_b WHERE application_id = p_application_id;
2321
2322 DELETE FROM xla_sources_tl WHERE application_id = p_application_id;
2323 DELETE FROM xla_sources_b WHERE application_id = p_application_id;
2324
2325 DELETE FROM xla_subledgers WHERE application_id = p_application_id;
2326
2327 IF (FND_INSTALLATION.get_app_info
2328 (application_short_name => 'XLA'
2329 ,status => l_status
2333 ELSE
2330 ,industry => l_industry
2331 ,oracle_schema => l_schema)) THEN
2332 l_schema := l_schema || '.';
2334 l_schema := '';
2335 END IF;
2336
2337 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'XLA_AE_HEADERS drop partition '||l_short_name;
2338 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'XLA_AE_LINES drop partition '||l_short_name;
2339 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'XLA_DISTRIBUTION_LINKS drop partition '||l_short_name;
2340
2341 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2342 trace(p_msg => 'END of function purge_subledger_seed',
2343 p_module => l_log_module,
2344 p_level => C_LEVEL_PROCEDURE);
2345 END IF;
2346 EXCEPTION
2347 WHEN NO_DATA_FOUND THEN
2348 ROLLBACK;
2349 x_return_status := FND_API.G_RET_STS_ERROR ;
2350
2351 WHEN FND_API.G_EXC_ERROR THEN
2352 ROLLBACK;
2353 x_return_status := FND_API.G_RET_STS_ERROR ;
2354
2355 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2356 ROLLBACK;
2357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2358
2359 WHEN OTHERS THEN
2360 ROLLBACK;
2361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2362
2363 END purge_subledger_seed;
2364
2365
2366 --=============================================================================
2367 --
2368 -- Name: purge_aad
2369 -- Description: This API purge the application accounting definition of an
2370 -- application for an AMB context
2371 --
2372 --=============================================================================
2373 PROCEDURE purge_aad
2374 (p_api_version IN NUMBER
2375 ,x_return_status IN OUT NOCOPY VARCHAR2
2376 ,p_application_id IN INTEGER
2377 ,p_amb_context_code IN VARCHAR2
2378 )
2379 IS
2380 CURSOR c_staging_context_code IS
2381 SELECT staging_amb_context_code
2382 FROM xla_appli_amb_contexts
2383 WHERE amb_context_code = p_amb_context_code
2384 AND application_id = p_application_id;
2385
2386 l_staging_context_code VARCHAR2(30);
2387
2388 l_api_name CONSTANT VARCHAR2(30) := 'purge_aad';
2389 l_api_version CONSTANT NUMBER := 1.0;
2390 l_log_module VARCHAR2(240);
2391 BEGIN
2392 IF g_log_enabled THEN
2393 l_log_module := C_DEFAULT_MODULE||'.purge_aad';
2394 END IF;
2395
2396 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2397 trace(p_msg => 'BEGIN of function purge_aad: '||
2398 'p_application_id = '||p_application_id||
2399 ', p_amb_context_code = '||p_amb_context_code,
2400 p_module => l_log_module,
2401 p_level => C_LEVEL_PROCEDURE);
2402 END IF;
2403 IF (NOT xla_aad_loader_util_pvt.compatible_api_call
2404 (p_current_version_number => l_api_version
2405 ,p_caller_version_number => p_api_version
2406 ,p_api_name => l_api_name
2407 ,p_pkg_name => C_DEFAULT_MODULE))
2408 THEN
2409 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2410 END IF;
2411
2412 -- Initialize global variables
2413 x_return_status := FND_API.G_RET_STS_SUCCESS;
2414
2415 -- API logic
2416 OPEN c_staging_context_code;
2417 FETCH c_staging_context_code INTO l_staging_context_code;
2418 CLOSE c_staging_context_code;
2422 p_module => l_log_module,
2419
2420 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2421 trace(p_msg => 'l_staging_context_code = '||l_staging_context_code,
2423 p_level => C_LEVEL_PROCEDURE);
2424 END IF;
2425
2426 IF (l_staging_context_code IS NOT NULL) THEN
2427 DELETE FROM xla_aad_loader_defns_t
2428 WHERE staging_amb_context_code = l_staging_context_code;
2429
2430 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2431 trace(p_msg => '# xla_aad_loader_defns_t deleted = '||SQL%ROWCOUNT,
2432 p_module => l_log_module,
2433 p_level => C_LEVEL_PROCEDURE);
2434 END IF;
2435
2436 DELETE FROM xla_appli_amb_contexts
2437 WHERE staging_amb_context_code = l_staging_context_code;
2438
2439 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2440 trace(p_msg => '# xla_appli_amb_contexts deleted = '||SQL%ROWCOUNT,
2441 p_module => l_log_module,
2442 p_level => C_LEVEL_PROCEDURE);
2443 END IF;
2444
2445 DELETE FROM xla_staging_components_h
2446 WHERE staging_amb_context_code = l_staging_context_code;
2447
2448 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2449 trace(p_msg => '# xla_staging_components_h deleted = '||SQL%ROWCOUNT,
2450 p_module => l_log_module,
2451 p_level => C_LEVEL_PROCEDURE);
2452 END IF;
2453
2454 DELETE FROM xla_stage_acctg_methods
2455 WHERE staging_amb_context_code = l_staging_context_code;
2456
2457 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2458 trace(p_msg => '# xla_stage_acctg_methods deleted = '||SQL%ROWCOUNT,
2459 p_module => l_log_module,
2460 p_level => C_LEVEL_PROCEDURE);
2461 END IF;
2462 ELSE
2463 l_staging_context_code := '';
2464
2465 DELETE FROM xla_aad_loader_defns_t
2466 WHERE staging_amb_context_code = p_amb_context_code;
2467
2468 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2469 trace(p_msg => '# xla_aad_loader_defns_t deleted = '||SQL%ROWCOUNT,
2470 p_module => l_log_module,
2471 p_level => C_LEVEL_PROCEDURE);
2472 END IF;
2473
2474 DELETE FROM xla_appli_amb_contexts
2475 WHERE staging_amb_context_code = p_amb_context_code;
2476
2477 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2478 trace(p_msg => '# xla_appli_amb_contexts deleted = '||SQL%ROWCOUNT,
2479 p_module => l_log_module,
2480 p_level => C_LEVEL_PROCEDURE);
2481 END IF;
2482
2483 DELETE FROM xla_staging_components_h
2484 WHERE staging_amb_context_code = p_amb_context_code;
2485
2486 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2487 trace(p_msg => '# xla_staging_components_h deleted = '||SQL%ROWCOUNT,
2488 p_module => l_log_module,
2489 p_level => C_LEVEL_PROCEDURE);
2490 END IF;
2491
2492 DELETE FROM xla_stage_acctg_methods
2493 WHERE staging_amb_context_code = p_amb_context_code;
2494
2495 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2496 trace(p_msg => '# xla_stage_acctg_methods deleted = '||SQL%ROWCOUNT,
2497 p_module => l_log_module,
2498 p_level => C_LEVEL_PROCEDURE);
2499 END IF;
2500 END IF;
2501
2502 DELETE FROM xla_amb_setup_errors
2503 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2504 AND application_id = p_application_id;
2505
2506 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2507 trace(p_msg => '# xla_amb_setup_errors deleted = '||SQL%ROWCOUNT,
2508 p_module => l_log_module,
2509 p_level => C_LEVEL_PROCEDURE);
2510 END IF;
2511
2512 DELETE FROM xla_acctg_method_rules
2513 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2514 AND application_id = p_application_id;
2515
2516 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2517 trace(p_msg => '# xla_acctg_method_rules deleted = '||SQL%ROWCOUNT,
2518 p_module => l_log_module,
2519 p_level => C_LEVEL_PROCEDURE);
2520 END IF;
2521
2522 DELETE FROM xla_aad_hdr_acct_attrs
2523 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2524 AND application_id = p_application_id;
2525
2526 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2527 trace(p_msg => '# xla_aad_hdr_acct_attrs deleted = '||SQL%ROWCOUNT,
2528 p_module => l_log_module,
2529 p_level => C_LEVEL_PROCEDURE);
2530 END IF;
2531
2532 DELETE FROM xla_aad_header_ac_assgns
2533 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2534 AND application_id = p_application_id;
2535
2536 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2537 trace(p_msg => '# xla_aad_header_ac_assgns deleted = '||SQL%ROWCOUNT,
2538 p_module => l_log_module,
2539 p_level => C_LEVEL_PROCEDURE);
2540 END IF;
2541
2542 DELETE FROM xla_mpa_header_ac_assgns
2543 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2544 AND application_id = p_application_id;
2545
2546 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2550 END IF;
2547 trace(p_msg => '# xla_mpa_header_ac_assgns deleted = '||SQL%ROWCOUNT,
2548 p_module => l_log_module,
2549 p_level => C_LEVEL_PROCEDURE);
2551
2552 DELETE FROM xla_mpa_jlt_adr_assgns
2553 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2554 AND application_id = p_application_id;
2555
2556 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2557 trace(p_msg => '# xla_mpa_jlt_adr_assgns deleted = '||SQL%ROWCOUNT,
2558 p_module => l_log_module,
2559 p_level => C_LEVEL_PROCEDURE);
2560 END IF;
2561
2562 DELETE FROM xla_mpa_jlt_ac_assgns
2563 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2564 AND application_id = p_application_id;
2565
2566 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2567 trace(p_msg => '# xla_mpa_jlt_ac_assgns deleted = '||SQL%ROWCOUNT,
2568 p_module => l_log_module,
2569 p_level => C_LEVEL_PROCEDURE);
2570 END IF;
2571
2572 DELETE FROM xla_mpa_jlt_assgns
2573 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2574 AND application_id = p_application_id;
2575
2576 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2577 trace(p_msg => '# xla_mpa_jlt_assgns deleted = '||SQL%ROWCOUNT,
2578 p_module => l_log_module,
2579 p_level => C_LEVEL_PROCEDURE);
2580 END IF;
2581
2582 DELETE FROM xla_aad_line_defn_assgns
2583 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2584 AND application_id = p_application_id;
2585
2586 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2587 trace(p_msg => '# xla_aad_line_defn_assgns deleted = '||SQL%ROWCOUNT,
2588 p_module => l_log_module,
2589 p_level => C_LEVEL_PROCEDURE);
2590 END IF;
2591
2592 DELETE FROM xla_jlt_acct_attrs
2593 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2594 AND application_id = p_application_id;
2595
2596 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2597 trace(p_msg => '# xla_jlt_acct_attrs deleted = '||SQL%ROWCOUNT,
2598 p_module => l_log_module,
2599 p_level => C_LEVEL_PROCEDURE);
2600 END IF;
2601
2602 DELETE FROM xla_line_defn_ac_assgns
2603 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2604 AND application_id = p_application_id;
2605
2606 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2607 trace(p_msg => '# xla_line_defn_ac_assgns deleted = '||SQL%ROWCOUNT,
2608 p_module => l_log_module,
2609 p_level => C_LEVEL_PROCEDURE);
2610 END IF;
2611
2612 DELETE FROM xla_line_defn_adr_assgns
2613 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2614 AND application_id = p_application_id;
2615
2616 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2617 trace(p_msg => '# xla_line_defn_adr_assgns deleted = '||SQL%ROWCOUNT,
2618 p_module => l_log_module,
2619 p_level => C_LEVEL_PROCEDURE);
2620 END IF;
2621
2622 DELETE FROM xla_line_defn_jlt_assgns
2623 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2624 AND application_id = p_application_id;
2625
2626 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2627 trace(p_msg => '# xla_line_defn_jlt_assgns deleted = '||SQL%ROWCOUNT,
2628 p_module => l_log_module,
2629 p_level => C_LEVEL_PROCEDURE);
2630 END IF;
2631
2632 DELETE FROM xla_line_definitions_tl
2633 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2634 AND application_id = p_application_id;
2635
2636 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2637 trace(p_msg => '# xla_line_definitions_tl deleted = '||SQL%ROWCOUNT,
2638 p_module => l_log_module,
2639 p_level => C_LEVEL_PROCEDURE);
2640 END IF;
2641
2642 DELETE FROM xla_line_definitions_b
2643 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2644 AND application_id = p_application_id;
2645
2646 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2647 trace(p_msg => '# xla_line_definitions_b deleted = '||SQL%ROWCOUNT,
2648 p_module => l_log_module,
2649 p_level => C_LEVEL_PROCEDURE);
2650 END IF;
2651
2652 DELETE FROM xla_prod_acct_headers
2653 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2654 AND application_id = p_application_id;
2655
2656 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2657 trace(p_msg => '# xla_prod_acct_headers deleted = '||SQL%ROWCOUNT,
2658 p_module => l_log_module,
2662 DELETE FROM xla_product_rules_tl
2659 p_level => C_LEVEL_PROCEDURE);
2660 END IF;
2661
2663 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2664 AND application_id = p_application_id;
2665
2666 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2667 trace(p_msg => '# xla_product_rules_tl deleted = '||SQL%ROWCOUNT,
2668 p_module => l_log_module,
2669 p_level => C_LEVEL_PROCEDURE);
2670 END IF;
2671
2672 DELETE FROM xla_product_rules_b
2673 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2674 AND application_id = p_application_id;
2675
2676 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2677 trace(p_msg => '# xla_product_rules_b deleted = '||SQL%ROWCOUNT,
2678 p_module => l_log_module,
2679 p_level => C_LEVEL_PROCEDURE);
2680 END IF;
2681
2682 DELETE FROM xla_conditions
2683 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2684 AND application_id = p_application_id;
2685
2686 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2687 trace(p_msg => '# xla_conditions deleted = '||SQL%ROWCOUNT,
2688 p_module => l_log_module,
2689 p_level => C_LEVEL_PROCEDURE);
2690 END IF;
2691
2692 DELETE FROM xla_analytical_sources b
2693 WHERE EXISTS (SELECT 1
2694 FROM xla_analytical_hdrs_b h
2695 WHERE b.analytical_criterion_type_code = h.analytical_criterion_type_code
2696 AND b.analytical_criterion_code = h.analytical_criterion_code
2697 AND b.amb_context_code = h.amb_context_code
2698 AND h.amb_context_code IN (p_amb_context_code, l_staging_context_code)
2699 AND h.application_id = p_application_id);
2700
2701 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2702 trace(p_msg => '# xla_analytical_sources deleted = '||SQL%ROWCOUNT,
2703 p_module => l_log_module,
2704 p_level => C_LEVEL_PROCEDURE);
2705 END IF;
2706
2707 DELETE FROM xla_analytical_dtls_tl b
2708 WHERE EXISTS (SELECT 1
2709 FROM xla_analytical_hdrs_b h
2710 WHERE b.analytical_criterion_type_code = h.analytical_criterion_type_code
2711 AND b.analytical_criterion_code = h.analytical_criterion_code
2712 AND b.amb_context_code = h.amb_context_code
2713 AND h.amb_context_code IN (p_amb_context_code, l_staging_context_code)
2714 AND h.application_id = p_application_id);
2715
2716 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2717 trace(p_msg => '# xla_analytical_dtls_tl deleted = '||SQL%ROWCOUNT,
2718 p_module => l_log_module,
2719 p_level => C_LEVEL_PROCEDURE);
2720 END IF;
2721
2722 DELETE FROM xla_analytical_dtls_b b
2723 WHERE EXISTS (SELECT 1
2724 FROM xla_analytical_hdrs_b h
2725 WHERE b.analytical_criterion_type_code = h.analytical_criterion_type_code
2726 AND b.analytical_criterion_code = h.analytical_criterion_code
2727 AND b.amb_context_code = h.amb_context_code
2731 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2728 AND h.amb_context_code IN (p_amb_context_code, l_staging_context_code)
2729 AND h.application_id = p_application_id);
2730
2732 trace(p_msg => '# xla_analytical_dtls_b deleted = '||SQL%ROWCOUNT,
2733 p_module => l_log_module,
2734 p_level => C_LEVEL_PROCEDURE);
2735 END IF;
2736
2737 DELETE FROM xla_analytical_hdrs_tl b
2738 WHERE EXISTS (SELECT 1
2739 FROM xla_analytical_hdrs_b h
2740 WHERE b.analytical_criterion_type_code = h.analytical_criterion_type_code
2741 AND b.analytical_criterion_code = h.analytical_criterion_code
2742 AND b.amb_context_code = h.amb_context_code
2743 AND h.amb_context_code IN (p_amb_context_code, l_staging_context_code)
2744 AND h.application_id = p_application_id);
2745
2746 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2747 trace(p_msg => '# xla_analytical_hdrs_tl deleted = '||SQL%ROWCOUNT,
2748 p_module => l_log_module,
2749 p_level => C_LEVEL_PROCEDURE);
2750 END IF;
2751
2752 DELETE FROM xla_analytical_hdrs_b
2753 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2754 AND application_id = p_application_id;
2755
2756 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2757 trace(p_msg => '# xla_analytical_hdrs_b deleted = '||SQL%ROWCOUNT,
2758 p_module => l_log_module,
2759 p_level => C_LEVEL_PROCEDURE);
2760 END IF;
2761
2762 DELETE FROM xla_seg_rule_details
2763 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2764 AND application_id = p_application_id;
2765
2766 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2767 trace(p_msg => '# xla_seg_rule_details deleted = '||SQL%ROWCOUNT,
2768 p_module => l_log_module,
2769 p_level => C_LEVEL_PROCEDURE);
2770 END IF;
2771
2772 DELETE FROM xla_seg_rules_tl
2773 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2774 AND application_id = p_application_id;
2775
2776 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2777 trace(p_msg => '# xla_seg_rules_tl deleted = '||SQL%ROWCOUNT,
2778 p_module => l_log_module,
2779 p_level => C_LEVEL_PROCEDURE);
2780 END IF;
2781
2782 DELETE FROM xla_seg_rules_b
2783 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2784 AND application_id = p_application_id;
2785
2786 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2787 trace(p_msg => '# xla_seg_rules_b deleted = '||SQL%ROWCOUNT,
2788 p_module => l_log_module,
2789 p_level => C_LEVEL_PROCEDURE);
2790 END IF;
2791
2792 DELETE FROM xla_descript_details_tl
2793 WHERE description_detail_id IN
2794 (SELECT d.description_detail_id
2795 FROM xla_descript_details_b d
2796 ,xla_desc_priorities p
2797 WHERE d.description_prio_id = p.description_prio_id
2798 AND p.application_id = p_application_id
2799 AND p.amb_context_code IN (p_amb_context_code, l_staging_context_code));
2800
2801 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2802 trace(p_msg => '# xla_descript_details_tl deleted = '||SQL%ROWCOUNT,
2803 p_module => l_log_module,
2804 p_level => C_LEVEL_PROCEDURE);
2805 END IF;
2806
2807 DELETE FROM xla_descript_details_b
2808 WHERE description_prio_id IN
2809 (SELECT description_prio_id
2810 FROM xla_desc_priorities
2811 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2812 AND application_id = p_application_id);
2813
2814 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2815 trace(p_msg => '# xla_descript_details_b deleted = '||SQL%ROWCOUNT,
2816 p_module => l_log_module,
2817 p_level => C_LEVEL_PROCEDURE);
2818 END IF;
2819
2820 DELETE FROM xla_desc_priorities
2821 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2822 AND application_id = p_application_id;
2823
2824 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2825 trace(p_msg => '# xla_desc_priorities deleted = '||SQL%ROWCOUNT,
2826 p_module => l_log_module,
2827 p_level => C_LEVEL_PROCEDURE);
2828 END IF;
2829
2830 DELETE FROM xla_descriptions_tl
2831 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2832 AND application_id = p_application_id;
2833
2834 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2835 trace(p_msg => '# xla_descriptions_tl deleted = '||SQL%ROWCOUNT,
2836 p_module => l_log_module,
2837 p_level => C_LEVEL_PROCEDURE);
2838 END IF;
2839
2840 DELETE FROM xla_descriptions_b
2841 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2842 AND application_id = p_application_id;
2843
2844 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2845 trace(p_msg => '# xla_descriptions_b deleted = '||SQL%ROWCOUNT,
2846 p_module => l_log_module,
2847 p_level => C_LEVEL_PROCEDURE);
2848 END IF;
2849
2850 DELETE FROM xla_acct_line_types_tl
2851 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2852 AND application_id = p_application_id;
2853
2854 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2855 trace(p_msg => '# xla_acct_line_types_tl deleted = '||SQL%ROWCOUNT,
2856 p_module => l_log_module,
2857 p_level => C_LEVEL_PROCEDURE);
2858 END IF;
2859
2860 DELETE FROM xla_acct_line_types_b
2861 WHERE amb_context_code IN (p_amb_context_code, l_staging_context_code)
2865 trace(p_msg => '# xla_acct_line_types_b deleted = '||SQL%ROWCOUNT,
2862 AND application_id = p_application_id;
2863
2864 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2866 p_module => l_log_module,
2867 p_level => C_LEVEL_PROCEDURE);
2868 END IF;
2869
2870 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2871 trace(p_msg => 'END of function purge_aad',
2872 p_module => l_log_module,
2873 p_level => C_LEVEL_PROCEDURE);
2874 END IF;
2875 EXCEPTION
2876 WHEN FND_API.G_EXC_ERROR THEN
2877 ROLLBACK;
2878 x_return_status := FND_API.G_RET_STS_ERROR ;
2879
2880 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2881 ROLLBACK;
2882 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2883
2884 WHEN OTHERS THEN
2885 ROLLBACK;
2886 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2887
2888 xla_aad_loader_util_pvt.stack_error
2889 (p_appli_s_name => 'XLA'
2890 ,p_msg_name => 'XLA_COMMON_ERROR'
2891 ,p_token_1 => 'LOCATION'
2892 ,p_value_1 => 'xla_aad_util_pvt.purge_aad'
2893 ,p_token_2 => 'ERROR'
2894 ,p_value_2 => 'unhandled exception');
2895
2896 END purge_aad;
2897
2898 --=============================================================================
2899 --
2900 -- Name: rebuild_ac_views
2901 -- Description: This API rebuild the view_column_name for the analytical detail
2902 -- and rebuild the views.
2903 --
2904 --=============================================================================
2905 PROCEDURE rebuild_ac_views
2906 IS
2907 CURSOR c_acs IS
2908 SELECT analytical_criterion_type_code
2909 , analytical_criterion_code
2910 , amb_context_code
2911 FROM xla_analytical_hdrs_b;
2912
2913 l_ret_value INTEGER;
2914 l_log_module VARCHAR2(240);
2915 BEGIN
2916 IF g_log_enabled THEN
2917 l_log_module := C_DEFAULT_MODULE||'.rebuild_ac_views';
2918 END IF;
2919
2920 FOR l_ac IN c_acs LOOP
2921
2922 l_ret_value := xla_analytical_criteria_pkg.compile_criterion
2923 (p_anacri_code => l_ac.analytical_criterion_code
2924 ,p_anacri_type_code => l_ac.analytical_criterion_type_code
2925 ,p_amb_context_code => l_ac.amb_context_code);
2926
2927 END LOOP;
2928
2929 l_ret_value := xla_analytical_criteria_pkg.build_criteria_view;
2930
2931 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2932 trace(p_msg => 'BEGIN of function rebuild_ac_views',
2933 p_module => l_log_module,
2934 p_level => C_LEVEL_PROCEDURE);
2935 END IF;
2936
2937
2938 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2939 trace(p_msg => 'END of function rebuild_ac_views',
2940 p_module => l_log_module,
2941 p_level => C_LEVEL_PROCEDURE);
2942 END IF;
2943 EXCEPTION
2944
2945 WHEN OTHERS THEN
2946 xla_aad_loader_util_pvt.stack_error
2947 (p_appli_s_name => 'XLA'
2948 ,p_msg_name => 'XLA_COMMON_ERROR'
2949 ,p_token_1 => 'LOCATION'
2950 ,p_value_1 => 'xla_aad_util_pvt.rebuild_ac_views'
2951 ,p_token_2 => 'ERROR'
2952 ,p_value_2 => 'unhandled exception');
2953 RAISE;
2954
2955
2956 END;
2957
2958 --=============================================================================
2959 --
2960 -- Name: validate_adr_compatibility
2961 -- Description: This API validate if the AAD includes any ADR from other
2962 -- application that has incompatible version
2963 --
2964 --=============================================================================
2965 FUNCTION validate_adr_compatibility
2966 (p_application_id IN INTEGER
2967 ,p_amb_context_code IN VARCHAR2
2968 ,p_staging_context_code IN VARCHAR2
2969 ) RETURN VARCHAR2
2970 IS
2971 CURSOR c_invalid_adrs IS
2972 SELECT app.application_name
2973 , xst.name segment_rule_name
2974 , lk1.meaning segment_rule_owner
2975 , xld.adr_version_num version_num
2976 FROM xla_line_defn_adr_assgns xld
2977 , xla_seg_rules_b xsr
2978 , xla_seg_rules_tl xst
2979 , fnd_application_vl app
2980 , xla_lookups lk1
2981 WHERE xld.amb_context_code = p_staging_context_code
2982 AND xld.segment_rule_appl_id <> p_application_id
2983 AND xsr.amb_context_code = p_amb_context_code
2984 AND xsr.application_id = xld.segment_rule_appl_id
2985 AND xsr.segment_rule_type_code = xld.segment_rule_type_code
2986 AND xsr.segment_rule_code = xld.segment_rule_code
2987 AND xsr.version_num < xld.adr_version_num
2988 AND app.application_id = xld.segment_rule_appl_id
2989 AND xst.application_id = xld.segment_rule_appl_id
2990 AND xst.segment_rule_type_code = xld.segment_rule_type_code
2991 AND xst.segment_rule_code = xld.segment_rule_code
2992 AND xst.language = USERENV('LANG')
2993 AND lk1.lookup_type = 'XLA_OWNER_TYPE'
2994 AND lk1.lookup_code = xld.segment_rule_type_code
2995 UNION
2996 SELECT app.application_name
2997 , xst.name segment_rule_name
2998 , lk1.meaning segment_rule_owner
2999 , xsd.value_adr_version_num
3000 FROM xla_seg_rule_details xsd
3001 , xla_seg_rules_b xsr
3002 , xla_seg_rules_tl xst
3003 , fnd_application_vl app
3004 , xla_lookups lk1
3008 AND xsr.application_id = xsd.value_segment_rule_appl_id
3005 WHERE xsd.amb_context_code = p_staging_context_code
3006 AND xsd.value_segment_rule_appl_id <> p_application_id
3007 AND xsr.amb_context_code = p_amb_context_code
3009 AND xsr.segment_rule_type_code = xsd.value_segment_rule_type_code
3010 AND xsr.segment_rule_code = xsd.value_segment_rule_code
3011 AND xsr.version_num < xsd.value_adr_version_num
3012 AND app.application_id = xsd.value_segment_rule_appl_id
3013 AND xst.application_id = xsd.value_segment_rule_appl_id
3014 AND xst.segment_rule_type_code = xsd.value_segment_rule_type_code
3015 AND xst.segment_rule_code = xsd.value_segment_rule_code
3016 AND xst.language = USERENV('LANG')
3017 AND lk1.lookup_type = 'XLA_OWNER_TYPE'
3018 AND lk1.lookup_code = xsd.value_segment_rule_type_code;
3019
3020 l_retcode VARCHAR2(30);
3021 l_log_module VARCHAR2(240);
3022 BEGIN
3023 IF g_log_enabled THEN
3024 l_log_module := C_DEFAULT_MODULE||'.validate_adr_compatibility';
3025 END IF;
3026
3027 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3028 trace(p_msg => 'BEGIN of function validate_adr_compatibility: '||
3029 'p_application_id = '||p_application_id||
3030 ', p_amb_context_code = '||p_amb_context_code,
3031 p_module => l_log_module,
3032 p_level => C_LEVEL_PROCEDURE);
3033 END IF;
3034
3035 l_retcode := 'SUCCESS';
3036
3037 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3038 trace(p_msg => 'BEGIN LOOP: c_invalid_adr ',
3039 p_module => l_log_module,
3040 p_level => C_LEVEL_PROCEDURE);
3041 END IF;
3042
3043 FOR l_err in c_invalid_adrs LOOP
3044 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3045 trace(p_msg => 'LOOP: c_invalid_adr - '||
3046 l_err.application_name||','||
3047 l_err.segment_rule_name||','||
3048 l_err.segment_rule_owner||','||
3049 l_err.version_num,
3050 p_module => l_log_module,
3051 p_level => C_LEVEL_PROCEDURE);
3052 END IF;
3053
3054 l_retcode := 'ERROR';
3055 xla_aad_loader_util_pvt.stack_error
3056 (p_appli_s_name => 'XLA'
3057 ,p_msg_name => 'XLA_AAD_INCOMPATIBLE_ADR_VERS'
3058 ,p_token_1 => 'APP_NAME'
3059 ,p_value_1 => l_err.application_name
3060 ,p_token_2 => 'SEGMENT_RULE_NAME'
3061 ,p_value_2 => l_err.segment_rule_name
3062 ,p_token_3 => 'SEGMENT_RULE_OWNER'
3063 ,p_value_3 => l_err.segment_rule_owner
3064 ,p_token_4 => 'VERSION_NUM'
3065 ,p_value_4 => l_err.version_num);
3066
3067 END LOOP;
3068
3069 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3070 trace(p_msg => 'END LOOP: c_invalid_adr ',
3071 p_module => l_log_module,
3072 p_level => C_LEVEL_PROCEDURE);
3073 END IF;
3074
3075 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3076 trace(p_msg => 'END of function validate_adr_compatibility',
3077 p_module => l_log_module,
3078 p_level => C_LEVEL_PROCEDURE);
3079 END IF;
3080
3081 return l_retcode;
3082 EXCEPTION
3083
3084 WHEN OTHERS THEN
3085 xla_aad_loader_util_pvt.stack_error
3086 (p_appli_s_name => 'XLA'
3087 ,p_msg_name => 'XLA_COMMON_ERROR'
3088 ,p_token_1 => 'LOCATION'
3089 ,p_value_1 => 'xla_aad_util_pvt.validate_adr_compatibility'
3090 ,p_token_2 => 'ERROR'
3091 ,p_value_2 => 'unhandled exception');
3092 RAISE;
3093
3094 END validate_adr_compatibility;
3095
3096 --=============================================================================
3097 --
3098 -- Name: purge_history
3099 -- Description: This API reset the version of the AADs, ADRs, etc of an
3100 -- application to 0 and clear all its version history.
3101 --
3102 --=============================================================================
3103 PROCEDURE purge_history
3104 (p_api_version IN NUMBER
3105 ,x_return_status IN OUT NOCOPY VARCHAR2
3106 ,p_application_id IN INTEGER)
3107 IS
3108 l_api_name CONSTANT VARCHAR2(30) := 'purge_history';
3109 l_api_version CONSTANT NUMBER := 1.0;
3110 l_log_module VARCHAR2(240);
3111 BEGIN
3112 IF g_log_enabled THEN
3113 l_log_module := C_DEFAULT_MODULE||'.purge_history';
3114 END IF;
3115
3116 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3117 trace(p_msg => 'BEGIN of function purge_history: '||
3118 'p_application_id = '||p_application_id,
3119 p_module => l_log_module,
3120 p_level => C_LEVEL_PROCEDURE);
3121 END IF;
3122
3123 IF (NOT xla_aad_loader_util_pvt.compatible_api_call
3124 (p_current_version_number => l_api_version
3125 ,p_caller_version_number => p_api_version
3126 ,p_api_name => l_api_name
3127 ,p_pkg_name => C_DEFAULT_MODULE))
3128 THEN
3129 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3130 END IF;
3131
3132 -- Initialize global variables
3133 x_return_status := FND_API.G_RET_STS_SUCCESS;
3134
3135 DELETE FROM xla_aads_h
3136 WHERE application_id = p_application_id;
3137
3138 DELETE FROM xla_amb_components_h
3139 WHERE application_id = p_application_id;
3140
3144 WHERE application_id = p_application_id;
3141 UPDATE xla_product_rules_b
3142 SET version_num = 0
3143 , updated_flag = 'Y'
3145
3146 UPDATE xla_seg_rules_b
3147 SET version_num = 0
3148 , updated_flag = 'Y'
3149 WHERE application_id = p_application_id;
3150
3151 UPDATE xla_analytical_hdrs_b
3152 SET version_num = 0
3153 , updated_flag = 'Y'
3154 WHERE application_id = p_application_id;
3155
3156 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3157 trace(p_msg => 'END of function purge_history',
3158 p_module => l_log_module,
3159 p_level => C_LEVEL_PROCEDURE);
3160 END IF;
3161 EXCEPTION
3162
3163 WHEN FND_API.G_EXC_ERROR THEN
3164 ROLLBACK;
3165 x_return_status := FND_API.G_RET_STS_ERROR ;
3166
3167 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3168 ROLLBACK;
3169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3170
3171 WHEN OTHERS THEN
3172 ROLLBACK;
3173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3174
3175 END purge_history;
3176
3177
3178
3179
3180
3181 --=============================================================================
3182 --
3183 -- Following code is executed when the package body is referenced for the first
3184 -- time
3185 --
3186 --=============================================================================
3187 BEGIN
3188 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3189 g_log_enabled := fnd_log.test
3190 (log_level => g_log_level
3191 ,module => C_DEFAULT_MODULE);
3192
3193 IF NOT g_log_enabled THEN
3194 g_log_level := C_LEVEL_LOG_DISABLED;
3195 END IF;
3196
3197 END xla_aad_loader_util_pvt;