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