[Home] [Help]
PACKAGE BODY: APPS.XLA_AAD_EXPORT_PVT
Source
1 PACKAGE BODY xla_aad_export_pvt AS
2 /* $Header: xlaalexp.pkb 120.18 2006/05/04 18:57:18 wychan ship $ */
3
4 --=============================================================================
5 -- **************** declaraions ********************
6 --=============================================================================
7 -------------------------------------------------------------------------------
8 -- declaring global types
9 -------------------------------------------------------------------------------
10 TYPE t_array_varchar2 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
11 TYPE t_array_int IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
12
13 -------------------------------------------------------------------------------
14 -- declaring global constants
15 -------------------------------------------------------------------------------
16 C_CHAR CONSTANT VARCHAR2(1) := '
17 ';
18
19 G_EXC_WARNING EXCEPTION;
20
21 ------------------------------------------------------------------------------
22 -- declaring global variables
23 ------------------------------------------------------------------------------
24 g_aad_groups xla_aad_group_tbl_type;
25
26 --=============================================================================
27 -- *********** Local Trace Routine **********
28 --=============================================================================
29 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
30 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
31 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
32 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
33 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
34 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
35
36 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
37 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_aad_export_pvt';
38
39 g_log_level NUMBER;
40 g_log_enabled BOOLEAN;
41
42 PROCEDURE trace
43 (p_msg IN VARCHAR2
44 ,p_module IN VARCHAR2
45 ,p_level IN NUMBER) IS
46 BEGIN
47 ----------------------------------------------------------------------------
48 -- Following is for FND log.
49 ----------------------------------------------------------------------------
50 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
51 fnd_log.message(p_level, p_module);
52 ELSIF p_level >= g_log_level THEN
53 fnd_log.string(p_level, p_module, p_msg);
54 END IF;
55 EXCEPTION
56 WHEN xla_exceptions_pkg.application_exception THEN
57 RAISE;
58
59 WHEN OTHERS THEN
60 xla_exceptions_pkg.raise_message
61 (p_location => 'xla_aad_export_pvt.trace');
62 END trace;
63
64
65 --=============================================================================
66 -- *********** private procedures and functions **********
67 --=============================================================================
68
69 --=============================================================================
70 --
71 -- Name: lock_context
72 --
73 --
74 --=============================================================================
75 FUNCTION lock_context
76 (p_application_id IN INTEGER
77 ,p_amb_context_code IN VARCHAR2)
78 RETURN VARCHAR2
79 IS
80 CURSOR c IS
81 SELECT *
82 FROM xla_appli_amb_contexts
83 WHERE application_id = p_application_id
84 AND amb_context_code = p_amb_context_code
85 FOR UPDATE OF application_id NOWAIT;
86
87 l_lock_error BOOLEAN;
88 l_retcode VARCHAR2(30);
89 l_log_module VARCHAR2(240);
90 BEGIN
91 IF g_log_enabled THEN
92 l_log_module := C_DEFAULT_MODULE||'.lock_context';
93 END IF;
94
95 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
96 trace(p_msg => 'BEGIN of function lock_context',
97 p_module => l_log_module,
98 p_level => C_LEVEL_PROCEDURE);
99 END IF;
100
101 l_retcode := 'SUCCESS';
102
103 -- Lock the staging area of the AMB context
104 l_lock_error := TRUE;
105 OPEN c;
106 CLOSE c;
107 l_lock_error := FALSE;
108
109 IF (l_retcode = 'SUCCESS') THEN
110 l_retcode := xla_aad_loader_util_pvt.lock_area
111 (p_application_id => p_application_id
112 ,p_amb_context_code => p_amb_context_code);
113
114 IF (l_retcode <> 'SUCCESS') THEN
115 xla_aad_loader_util_pvt.stack_error
116 (p_appli_s_name => 'XLA'
117 ,p_msg_name => 'XLA_AAD_EXP_LOCK_FAILED');
118 l_retcode := 'WARNING';
119 END IF;
120 END IF;
121
122 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
123 trace(p_msg => 'END of function lock_context - Return value = '||l_retcode,
124 p_module => l_log_module,
125 p_level => C_LEVEL_PROCEDURE);
126 END IF;
127
128 RETURN l_retcode;
129 EXCEPTION
130 WHEN OTHERS THEN
131 IF (c%ISOPEN) THEN
132 CLOSE c;
133 END IF;
134
135 IF (l_lock_error) THEN
136 l_retcode := 'WARNING';
137 xla_aad_loader_util_pvt.stack_error
138 (p_appli_s_name => 'XLA'
139 ,p_msg_name => 'XLA_AAD_EXP_LOCK_FAILED');
140
141 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
142 trace(p_msg => 'END of function lock_context - Return value = '||l_retcode,
143 p_module => l_log_module,
144 p_level => C_LEVEL_PROCEDURE);
145 END IF;
146
147 return l_retcode;
148 ELSE
149 xla_aad_loader_util_pvt.stack_error
150 (p_appli_s_name => 'XLA'
151 ,p_msg_name => 'XLA_COMMON_ERROR'
152 ,p_token_1 => 'LOCATION'
153 ,p_value_1 => 'xla_aad_export_pvt.lock_context'
154 ,p_token_2 => 'ERROR'
155 ,p_value_2 => 'unhandled exception');
156 RAISE;
157 END IF;
158 END lock_context;
159
160
161
162 --=============================================================================
163 --
164 -- Name: validate_standard_mode
165 -- Description: This API validate the AADs and components
166 -- Return codes:
167 -- SUCCESS - completed sucessfully
168 -- WARNING - completed with warning
169 -- ERROR - completed with error
170 --
171 --=============================================================================
172 FUNCTION validate_standard_mode
173 (p_application_id IN INTEGER
174 ,p_amb_context_code IN VARCHAR2
175 ,p_owner_type IN VARCHAR2)
176 RETURN VARCHAR2
177 IS
178 -- Ensure the AAD to be exported is modified from
179 -- one with the latest non-leapfrog version
180 -- (h.version_num > b.version_num and leapfrog_flag = 'N')
181 -- Ensure the AAD to be exported is not modified from a leapfrog version
182 -- (h.version_num = b.version_num and leapfrog_flag = 'Y')
183 CURSOR c_aad IS
184 SELECT distinct t.name
185 FROM xla_aads_h h
186 ,xla_product_rules_b b
187 ,xla_product_rules_tl t
188 WHERE t.application_id = b.application_id
189 AND t.amb_context_code = b.amb_context_code
190 AND t.product_rule_type_code = b.product_rule_type_code
191 AND t.product_rule_code = b.product_rule_code
192 AND t.language = USERENV('LANG')
193 AND ((h.version_num > b.version_num AND
194 h.leapfrog_flag = 'N') OR
195 (h.version_num = b.version_num AND
196 h.leapfrog_flag = 'Y'))
197 AND h.application_id = b.application_id
198 AND h.product_rule_type_code = b.product_rule_type_code
199 AND h.product_rule_code = b.product_rule_code
200 AND b.application_id = p_application_id
201 AND b.amb_context_code = p_amb_context_code;
202
203 -- Ensure the ADR to be exported is modified from one with the
204 -- latest non-leapfrog version
205 -- (h.version_num > b.version_num and leapfrog_flag = 'N')
206 -- Ensure the ADR to be exported is not modified from a leapfrog version
207 -- (h.version_num = b.version_num and leapfrog_flag = 'Y')
208 CURSOR c_adr IS
209 SELECT distinct t.name
210 FROM xla_amb_components_h h
211 ,xla_seg_rules_b b
212 ,xla_seg_rules_tl t
213 WHERE t.amb_context_code = b.amb_context_code
214 AND t.application_id = b.application_id
215 AND t.segment_rule_type_code = b.segment_rule_type_code
216 AND t.segment_rule_code = b.segment_rule_code
217 AND t.language = USERENV('LANG')
218 AND ((h.version_num > b.version_num AND
219 h.leapfrog_flag = 'N') OR
220 (h.version_num = b.version_num AND
221 h.leapfrog_flag = 'Y'))
222 AND h.component_type_code = 'AMB_ADR'
223 AND h.application_id = b.application_id
224 AND h.component_owner_code = b.segment_rule_type_code
225 AND h.component_code = b.segment_rule_code
226 AND b.application_id = p_application_id
227 AND b.amb_context_code = p_amb_context_code;
228
229 -- Ensure the AC to be exported is modified from one with the
230 -- latest non-leapfrog version
231 -- (h.version_num > b.version_num and leapfrog_flag = 'N')
232 -- Ensure the AC to be exported is not modified from a leapfrog version
233 -- (h.version_num = b.version_num and leapfrog_flag = 'Y')
234 /*
235 CURSOR c_ac IS
236 SELECT distinct t.name
237 FROM xla_amb_components_h h
238 ,xla_analytical_hdrs_b b
239 ,xla_analytical_hdrs_tl t
240 WHERE t.amb_context_code = b.amb_context_code
241 AND t.analytical_criterion_type_code = b.analytical_criterion_type_code
242 AND t.analytical_criterion_code = b.analytical_criterion_code
243 AND t.language = USERENV('LANG')
244 AND ((h.version_num > b.version_num AND
245 h.leapfrog_flag = 'N') OR
246 (h.version_num = b.version_num AND
247 h.leapfrog_flag = 'Y'))
248 AND h.component_type_code = 'ANALYTICAL_CRITERION'
249 AND h.component_owner_code = b.analytical_criterion_type_code
250 AND h.component_code = b.analytical_criterion_code
251 AND b.amb_context_code = p_amb_context_code
252 AND (EXISTS (SELECT 1
253 FROM xla_aad_header_ac_assgns ac
254 WHERE b.amb_context_code = ac.amb_context_code
255 AND b.analytical_criterion_type_code = ac.analytical_criterion_type_code
256 AND b.analytical_criterion_code = ac.analytical_criterion_code
257 AND ac.amb_context_code = p_amb_context_code
258 AND ac.application_id = p_application_id) OR
259 EXISTS (SELECT 1
260 FROM xla_line_defn_ac_assgns ac
261 , xla_aad_line_defn_assgns xal
262 WHERE b.amb_context_code = ac.amb_context_code
263 AND b.analytical_criterion_type_code = ac.analytical_criterion_type_code
264 AND b.analytical_criterion_code = ac.analytical_criterion_code
265 AND ac.application_id = xal.application_id
266 AND ac.amb_context_code = xal.amb_context_code
267 AND ac.event_class_code = xal.event_class_code
268 AND ac.event_type_code = xal.event_type_code
269 AND ac.line_definition_owner_code = xal.line_definition_owner_code
270 AND ac.line_definition_code = xal.line_definition_code
271 AND xal.amb_context_code = p_amb_context_code
272 AND xal.application_id = p_application_id));
273 */
274
275 -- Ensure the MS to be exported is modified from one with the
276 -- latest non-leapfrog version
277 -- (h.version_num > b.version_num and leapfrog_flag = 'N')
278 -- Ensure the MS to be exported is not modified from a leapfrog version
279 -- (h.version_num = b.version_num and leapfrog_flag = 'Y')
280 CURSOR c_ms IS
281 SELECT distinct t.name
282 FROM xla_amb_components_h h
283 ,xla_mapping_sets_b b
284 ,xla_mapping_sets_tl t
285 WHERE t.amb_context_code = b.amb_context_code
286 AND t.mapping_set_code = b.mapping_set_code
287 AND t.language = USERENV('LANG')
288 AND ((h.version_num > b.version_num AND
289 h.leapfrog_flag = 'N') OR
290 (h.version_num = b.version_num AND
291 h.leapfrog_flag = 'Y'))
292 AND h.component_type_code = 'MAPPING_SET'
293 AND h.component_code = b.mapping_set_code
294 AND b.amb_context_code = p_amb_context_code
295 AND EXISTS (SELECT 1
296 FROM xla_seg_rule_details dtl
297 , xla_line_defn_adr_assgns adr
298 , xla_aad_line_defn_assgns xal
299 WHERE b.mapping_set_code = dtl.value_mapping_set_code
300 AND dtl.amb_context_code = adr.amb_context_code
301 AND dtl.application_id = adr.segment_rule_appl_id
302 AND dtl.segment_rule_type_code = adr.segment_rule_type_code
303 AND dtl.segment_rule_code = adr.segment_rule_code
304 AND adr.application_id = xal.application_id
305 AND adr.amb_context_code = xal.amb_context_code
306 AND adr.event_class_code = xal.event_class_code
307 AND adr.event_type_code = xal.event_type_code
308 AND adr.line_definition_owner_code = xal.line_definition_owner_code
309 AND adr.line_definition_code = xal.line_definition_code
310 AND xal.amb_context_code = p_amb_context_code
311 AND xal.application_id = p_application_id);
312
313 l_retcode VARCHAR2(30);
314 l_log_module VARCHAR2(240);
315 BEGIN
316 IF g_log_enabled THEN
317 l_log_module := C_DEFAULT_MODULE||'.validate_standard_mode';
318 END IF;
319
320 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
321 trace(p_msg => 'BEGIN of function validate_standard_mode',
322 p_module => l_log_module,
323 p_level => C_LEVEL_PROCEDURE);
324 END IF;
325
326 l_retcode := 'SUCCESS';
327
328 IF (C_LEVEL_EVENT >= g_log_level) THEN
329 trace(p_msg => 'BEGIN LOOP - invalid AAD versions (base version is not latest)',
330 p_module => l_log_module,
331 p_level => C_LEVEL_EVENT);
332 END IF;
333
334 FOR l_aad IN c_aad LOOP
335 l_retcode := 'WARNING';
336 IF (C_LEVEL_ERROR >= g_log_level) THEN
337 trace(p_msg => 'LOOP - invalid AAD version: '||l_aad.name,
338 p_module => l_log_module,
339 p_level => C_LEVEL_ERROR);
340 END IF;
341
342 xla_aad_loader_util_pvt.stack_error
343 (p_appli_s_name => 'XLA'
344 ,p_msg_name => 'XLA_AAD_EXP_INV_LEAPFROG'
345 ,p_token_1 => 'PROD_RULE_NAME'
346 ,p_value_1 => l_aad.name);
347 END LOOP;
348
349 IF (C_LEVEL_EVENT >= g_log_level) THEN
350 trace(p_msg => 'END LOOP - invalid AAD versions (base version is not latest)',
351 p_module => l_log_module,
352 p_level => C_LEVEL_EVENT);
353 END IF;
354
355 /*
356 IF (C_LEVEL_EVENT >= g_log_level) THEN
357 trace(p_msg => 'BEGIN LOOP - invalid AC versions',
358 p_module => l_log_module,
359 p_level => C_LEVEL_EVENT);
360 END IF;
361
362 FOR l_ac IN c_ac LOOP
363 l_retcode := 'WARNING';
364 IF (C_LEVEL_ERROR >= g_log_level) THEN
365 trace(p_msg => 'LOOP - invalid AC version: '||l_ac.name,
366 p_module => l_log_module,
367 p_level => C_LEVEL_ERROR);
368 END IF;
369
370 xla_aad_loader_util_pvt.stack_error
371 (p_appli_s_name => 'XLA'
372 ,p_msg_name => 'XLA_AAD_EXP_INV_LEAPFROG_AC'
373 ,p_token_1 => 'ANALYTICAL_CRITERION_NAME'
374 ,p_value_1 => l_ac.name);
375 END LOOP;
376
377 IF (C_LEVEL_EVENT >= g_log_level) THEN
378 trace(p_msg => 'END LOOP - invalid AC versions (base version is not latest)',
379 p_module => l_log_module,
380 p_level => C_LEVEL_EVENT);
381 END IF;
382 */
383
384 FOR l_adr IN c_adr LOOP
385 l_retcode := 'WARNING';
386 IF (C_LEVEL_ERROR >= g_log_level) THEN
387 trace(p_msg => 'LOOP - invalid ADR version: '||l_adr.name,
388 p_module => l_log_module,
389 p_level => C_LEVEL_ERROR);
390 END IF;
391
392 xla_aad_loader_util_pvt.stack_error
393 (p_appli_s_name => 'XLA'
394 ,p_msg_name => 'XLA_AAD_EXP_INV_LEAPFROG_ADR'
395 ,p_token_1 => 'SEGMENT_RULE_NAME'
396 ,p_value_1 => l_adr.name);
397 END LOOP;
398
399 IF (C_LEVEL_EVENT >= g_log_level) THEN
400 trace(p_msg => 'END LOOP - invalid ADR versions',
401 p_module => l_log_module,
402 p_level => C_LEVEL_EVENT);
403 END IF;
404
405 IF (p_owner_type = 'C') THEN
406 IF (C_LEVEL_EVENT >= g_log_level) THEN
407 trace(p_msg => 'BEGIN LOOP - invalid export versions (MS)',
408 p_module => l_log_module,
409 p_level => C_LEVEL_EVENT);
410 END IF;
411
412 FOR l_ms IN c_ms LOOP
413 l_retcode := 'WARNING';
414 IF (C_LEVEL_ERROR >= g_log_level) THEN
415 trace(p_msg => 'LOOP - invalid export version (MS): '||l_ms.name,
416 p_module => l_log_module,
417 p_level => C_LEVEL_ERROR);
418 END IF;
419
420 xla_aad_loader_util_pvt.stack_error
421 (p_appli_s_name => 'XLA'
422 ,p_msg_name => 'XLA_AAD_EXP_INV_LEAPFROG_MS'
423 ,p_token_1 => 'MAPPING_SET_NAME'
424 ,p_value_1 => l_ms.name);
425 END LOOP;
426
427 IF (C_LEVEL_EVENT >= g_log_level) THEN
428 trace(p_msg => 'END LOOP - invalid export versions (MS)',
429 p_module => l_log_module,
430 p_level => C_LEVEL_EVENT);
431 END IF;
432
433 END IF;
434
435 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
436 trace(p_msg => 'END of function validate_standard_mode - Return value = '||l_retcode,
437 p_module => l_log_module,
438 p_level => C_LEVEL_PROCEDURE);
439 END IF;
440
441 return l_retcode;
442 EXCEPTION
443 WHEN OTHERS THEN
444 xla_aad_loader_util_pvt.stack_error
445 (p_appli_s_name => 'XLA'
446 ,p_msg_name => 'XLA_COMMON_ERROR'
447 ,p_token_1 => 'LOCATION'
448 ,p_value_1 => 'xla_aad_export_pvt.validate_standard_mode'
449 ,p_token_2 => 'ERROR'
450 ,p_value_2 => 'unhandled exception');
451 RAISE;
452
453 END validate_standard_mode;
454
455 --=============================================================================
456 --
457 -- Name: validate_leapfrog_mode
458 -- Description: This API validate the AADs and components
459 -- Return codes:
460 -- SUCCESS - completed sucessfully
461 -- WARNING - completed with warning
462 -- ERROR - completed with error
463 --
464 --=============================================================================
465 FUNCTION validate_leapfrog_mode
466 (p_application_id IN INTEGER
467 ,p_amb_context_code IN VARCHAR2
468 ,p_owner_type IN VARCHAR2)
469 RETURN VARCHAR2
470 IS
471 -- Ensure at least one AAD to be exported is not the latest non-leapfrog version
472 -- (h.version_num > b.version_num and leapfrog_flag = 'N')
473 -- or is modified from a leapfrog version
474 -- (h.version_num = b.version_num and leapfrog_flag = 'Y')
475 CURSOR c_aad IS
476 SELECT 1
477 FROM xla_aads_h h
478 ,xla_product_rules_b b
479 WHERE h.application_id = b.application_id
480 AND h.product_rule_type_code = b.product_rule_type_code
481 AND h.product_rule_code = b.product_rule_code
482 AND ((h.version_num > b.version_num AND
483 h.leapfrog_flag = 'N') OR
484 (h.version_num = b.version_num AND
485 h.leapfrog_flag = 'Y'))
486 AND b.application_id = p_application_id
487 AND b.amb_context_code = p_amb_context_code;
488
489 -- Ensure at least one ADR to be exported is not the latest non-leapfrog version
490 -- (h.version_num > b.version_num and leapfrog_flag = 'N')
491 -- or is modified from a leapfrog version
492 -- (h.version_num = b.version_num and leapfrog_flag = 'Y')
493 CURSOR c_adr IS
494 SELECT 1
495 FROM xla_amb_components_h h
496 ,xla_seg_rules_b b
497 WHERE h.component_type_code = 'AMB_ADR'
498 AND h.application_id = b.application_id
499 AND h.component_owner_code = b.segment_rule_type_code
500 AND h.component_code = b.segment_rule_code
501 AND ((h.version_num > b.version_num AND
502 h.leapfrog_flag = 'N') OR
503 (h.version_num = b.version_num AND
504 h.leapfrog_flag = 'Y'))
505 AND b.application_id = p_application_id
506 AND b.amb_context_code = p_amb_context_code;
507
508 -- Ensure at least one MS to be exported is not the latest non-leapfrog version
509 -- (h.version_num > b.version_num and leapfrog_flag = 'N')
510 -- or is modified from a leapfrog version
511 -- (h.version_num = b.version_num and leapfrog_flag = 'Y')
512 CURSOR c_ms IS
513 SELECT 1
514 FROM xla_amb_components_h h
515 ,xla_mapping_sets_b b
516 WHERE h.component_type_code = 'MAPPING_SET'
517 AND h.component_code = b.mapping_set_code
518 AND ((h.version_num > b.version_num AND
519 h.leapfrog_flag = 'N') OR
520 (h.version_num = b.version_num AND
521 h.leapfrog_flag = 'Y'))
522 AND b.amb_context_code = p_amb_context_code
523 AND EXISTS (SELECT 1
524 FROM xla_seg_rule_details dtl
525 , xla_line_defn_adr_assgns adr
526 , xla_aad_line_defn_assgns xal
527 WHERE b.mapping_set_code = dtl.value_mapping_set_code
528 AND dtl.amb_context_code = adr.amb_context_code
529 AND dtl.application_id = adr.segment_rule_appl_id
530 AND dtl.segment_rule_type_code = adr.segment_rule_type_code
531 AND dtl.segment_rule_code = adr.segment_rule_code
532 AND adr.application_id = xal.application_id
533 AND adr.amb_context_code = xal.amb_context_code
534 AND adr.event_class_code = xal.event_class_code
535 AND adr.event_type_code = xal.event_type_code
536 AND adr.line_definition_owner_code = xal.line_definition_owner_code
537 AND adr.line_definition_code = xal.line_definition_code
538 AND xal.amb_context_code = p_amb_context_code
539 AND xal.application_id = p_application_id);
540
541 l_exists INTEGER;
542 l_retcode VARCHAR2(30);
543 l_log_module VARCHAR2(240);
544 BEGIN
545 IF g_log_enabled THEN
546 l_log_module := C_DEFAULT_MODULE||'.validate_leapfrog_mode';
547 END IF;
548
549 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
550 trace(p_msg => 'BEGIN of function validate_leapfrog_mode',
551 p_module => l_log_module,
552 p_level => C_LEVEL_PROCEDURE);
553 END IF;
554
555 l_retcode := 'SUCCESS';
556
557 l_exists := NULL;
558 OPEN c_aad;
559 FETCH c_aad INTO l_exists;
560 CLOSE c_aad;
561
562 IF (l_exists IS NULL) THEN
563 OPEN c_adr;
564 FETCH c_adr INTO l_exists;
565 CLOSE c_adr;
566 END IF;
567
568 IF (l_exists IS NULL) THEN
569 OPEN c_ms;
570 FETCH c_ms INTO l_exists;
571 CLOSE c_ms;
572 END IF;
573
574 -- If none of the AMB objects is based on a leapfrog version, or based on a
575 -- not-the-latest version, LEAPFROG mode should not be used. It is a STANDARD case.
576 IF (l_exists IS NULL) THEN
577 l_retcode := 'WARNING';
578 xla_aad_loader_util_pvt.stack_error
579 (p_appli_s_name => 'XLA'
580 ,p_msg_name => 'XLA_AAD_EXP_INV_NON_LEAPFROG');
581
582 END IF;
583
584 return l_retcode;
585 EXCEPTION
586 WHEN OTHERS THEN
587 xla_aad_loader_util_pvt.stack_error
588 (p_appli_s_name => 'XLA'
589 ,p_msg_name => 'XLA_COMMON_ERROR'
590 ,p_token_1 => 'LOCATION'
591 ,p_value_1 => 'xla_aad_export_pvt.validate_leapfrog_mode'
592 ,p_token_2 => 'ERROR'
593 ,p_value_2 => 'unhandled exception');
594 RAISE;
595
596 END validate_leapfrog_mode;
597
598 --=============================================================================
599 --
600 -- Name: validate_supersede_mode
601 -- Description: This API validate the AADs and components
602 -- Return codes:
603 -- SUCCESS - completed sucessfully
604 -- WARNING - completed with warning
605 -- ERROR - completed with error
606 --
607 --=============================================================================
608 FUNCTION validate_supersede_mode
609 (p_application_id IN INTEGER
610 ,p_amb_context_code IN VARCHAR2
611 ,p_owner_type IN VARCHAR2)
612 RETURN VARCHAR2
613 IS
614 l_retcode VARCHAR2(30);
615 l_log_module VARCHAR2(240);
616 BEGIN
617 IF g_log_enabled THEN
618 l_log_module := C_DEFAULT_MODULE||'.validate_supersede_mode';
619 END IF;
620
621 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
622 trace(p_msg => 'BEGIN of function validate_supersede_mode',
623 p_module => l_log_module,
624 p_level => C_LEVEL_PROCEDURE);
625 END IF;
626
627 l_retcode := 'SUCCESS';
628
629 return l_retcode;
630 EXCEPTION
631 WHEN OTHERS THEN
632 xla_aad_loader_util_pvt.stack_error
633 (p_appli_s_name => 'XLA'
634 ,p_msg_name => 'XLA_COMMON_ERROR'
635 ,p_token_1 => 'LOCATION'
636 ,p_value_1 => 'xla_aad_export_pvt.validate_supersede_mode'
637 ,p_token_2 => 'ERROR'
638 ,p_value_2 => 'unhandled exception');
639 RAISE;
640
641 END validate_supersede_mode;
642
643 --=============================================================================
644 --
645 -- Name: validation
646 -- Description: This API validate the AADs and components
647 -- Return codes:
648 -- SUCCESS - completed sucessfully
649 -- WARNING - completed with warning
650 -- ERROR - completed with error
651 --
652 --=============================================================================
653 FUNCTION validation
654 (p_application_id IN INTEGER
655 ,p_amb_context_code IN VARCHAR2
656 ,p_owner_type IN VARCHAR2
657 ,p_versioning_mode IN VARCHAR2)
658 RETURN VARCHAR2
659 IS
660 l_retcode VARCHAR2(30);
661 l_log_module VARCHAR2(240);
662 BEGIN
663 IF g_log_enabled THEN
664 l_log_module := C_DEFAULT_MODULE||'.validation';
665 END IF;
666
667 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
668 trace(p_msg => 'BEGIN of function validation',
669 p_module => l_log_module,
670 p_level => C_LEVEL_PROCEDURE);
671 END IF;
672
673 IF (p_versioning_mode = 'STANDARD') THEN
674 l_retcode := validate_standard_mode
675 (p_application_id => p_application_id
676 ,p_amb_context_code => p_amb_context_code
677 ,p_owner_type => p_owner_type);
678 ELSIF (p_versioning_mode = 'LEAPFROG') THEN
679 l_retcode := validate_leapfrog_mode
680 (p_application_id => p_application_id
681 ,p_amb_context_code => p_amb_context_code
682 ,p_owner_type => p_owner_type);
683 ELSE -- p_versioning_mode = 'SUPERSEDE'
684 l_retcode := validate_supersede_mode
685 (p_application_id => p_application_id
686 ,p_amb_context_code => p_amb_context_code
687 ,p_owner_type => p_owner_type);
688 END IF;
689
690 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
691 trace(p_msg => 'END of function validation - Return value = '||l_retcode,
692 p_module => l_log_module,
693 p_level => C_LEVEL_PROCEDURE);
694 END IF;
695
696 return l_retcode;
697 EXCEPTION
698 WHEN OTHERS THEN
699 xla_aad_loader_util_pvt.stack_error
700 (p_appli_s_name => 'XLA'
701 ,p_msg_name => 'XLA_COMMON_ERROR'
702 ,p_token_1 => 'LOCATION'
703 ,p_value_1 => 'xla_aad_export_pvt.validation'
704 ,p_token_2 => 'ERROR'
705 ,p_value_2 => 'unhandled exception');
706 RAISE;
707
708 END validation;
709
710 --=============================================================================
711 --
712 -- Name: update_group_number
713 -- Description: This API update the product rule in global aad group arry with
714 -- the group number
715 -- Return Code:
716 -- TRUE: group number is updated
717 -- FALSE: group number is not updated
718 --
719 --=============================================================================
720 FUNCTION update_group_number
721 (p_product_rule_code VARCHAR2
722 ,p_group_number INTEGER)
723 RETURN BOOLEAN
724 IS
725 l_retcode BOOLEAN;
726 l_log_module VARCHAR2(240);
727 BEGIN
728 IF g_log_enabled THEN
729 l_log_module := C_DEFAULT_MODULE||'.update_group_number';
730 END IF;
731
732 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
733 trace(p_msg => 'BEGIN of function update_group_number: '||
734 p_product_rule_code||','||p_group_number,
735 p_module => l_log_module,
736 p_level => C_LEVEL_PROCEDURE);
737 END IF;
738
739 l_retcode := FALSE;
740
741 FOR i IN 1 .. g_aad_groups.COUNT LOOP
742 IF (g_aad_groups(i).product_rule_code = p_product_rule_code) THEN
743 IF (g_aad_groups(i).group_num <> p_group_number) THEN
744 g_aad_groups(i).group_num := p_group_number;
745 l_retcode := TRUE;
746 END IF;
747 EXIT;
748 END IF;
749 END LOOP;
750
751 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
752 trace(p_msg => 'END of function update_group_number : '||
753 'l_retcode = '||CASE WHEN l_retcode THEN 'TRUE'
754 ELSE 'FALSE' END,
755 p_module => l_log_module,
756 p_level => C_LEVEL_PROCEDURE);
757 END IF;
758
759 return l_retcode;
760 EXCEPTION
761 WHEN OTHERS THEN
762 xla_aad_loader_util_pvt.stack_error
763 (p_appli_s_name => 'XLA'
764 ,p_msg_name => 'XLA_COMMON_ERROR'
765 ,p_token_1 => 'LOCATION'
766 ,p_value_1 => 'xla_aad_export_pvt.update_group_number'
767 ,p_token_2 => 'ERROR'
768 ,p_value_2 => 'unhandled exception');
769 RAISE;
770
771 END update_group_number;
772
773 --=============================================================================
774 --
775 -- Name: group_aads
776 -- Description: This API groups the AAD with the same group number if they
777 -- shares any commom components. The group number information is
778 -- stored in the g_aad_groups global array.
779 --
780 --=============================================================================
781 PROCEDURE group_aads
782 (p_application_id IN INTEGER
783 ,p_amb_context_code IN VARCHAR2
784 ,p_owner_type IN VARCHAR2)
785 IS
786 l_curr_group_num INTEGER;
787
788 -- Cursor to return all AADs to be grouped
789 CURSOR c_aad IS
790 SELECT distinct
791 b.product_rule_code
792 ,b.version_num
793 ,b.updated_flag
794 ,NVL(h.leapfrog_flag,'N') leapfrog_flag
795 FROM xla_product_rules_b b
796 JOIN xla_aads_h h
797 ON h.product_rule_code = b.product_rule_code
798 AND h.application_id = p_application_id
799 AND h.product_rule_type_code = p_owner_type
800 JOIN (SELECT product_rule_code, max(version_num) max_version_num
801 FROM xla_aads_h
802 WHERE application_id = p_application_id
803 AND product_rule_type_code = p_owner_type
804 GROUP BY product_rule_code) h2
805 ON h.product_rule_code = h2.product_rule_code
806 AND h.version_num = h2.max_version_num
807 WHERE b.application_id = p_application_id
808 AND b.amb_context_code = p_amb_context_code
809 AND b.product_rule_type_code = p_owner_type
810 UNION
811 SELECT distinct
812 b.product_rule_code
813 ,b.version_num
814 ,b.updated_flag
815 ,NVL(h.leapfrog_flag,'N') leapfrog_flag
816 FROM xla_product_rules_b b
817 LEFT OUTER JOIN xla_aads_h h
818 ON h.product_rule_code = b.product_rule_code
819 AND h.application_id = p_application_id
820 AND h.product_rule_type_code = p_owner_type
821 WHERE b.application_id = p_application_id
822 AND b.amb_context_code = p_amb_context_code
823 AND b.product_rule_type_code = p_owner_type
824 AND h.product_rule_code IS NULL;
825
826 -- Cursor to return AADs that shares any common component with the AADs that
827 -- was assigned with the group l_curr_group_num
828 CURSOR c_aad_group IS
829 SELECT xal.product_rule_code
830 FROM xla_aad_line_defn_assgns xal
831 WHERE xal.application_id = p_application_id
832 AND xal.amb_context_code = p_amb_context_code
833 AND xal.product_rule_type_code = p_owner_type
834 AND EXISTS (SELECT 1
835 FROM xla_aad_line_defn_assgns xal2
836 ,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
837 WHERE xal2.application_id = xal.application_id
838 AND xal2.amb_context_code = xal.amb_context_code
839 AND xal2.event_class_code = xal.event_class_code
840 AND xal2.event_type_code = xal.event_type_code
841 AND xal2.line_definition_owner_code = xal.line_definition_owner_code
842 AND xal2.line_definition_code = xal.line_definition_code
843 AND xal2.product_rule_type_code = p_owner_type
844 AND xal2.product_rule_code = grp.product_rule_code
845 AND grp.group_num = l_curr_group_num)
846 UNION
847 SELECT h.product_rule_code -- header description
848 FROM xla_prod_acct_headers h
849 WHERE h.application_id = p_application_id
850 AND h.amb_context_code = p_amb_context_code
851 AND h.product_rule_type_code = p_owner_type
852 AND EXISTS (SELECT 1
853 FROM xla_prod_acct_headers pah
854 ,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
855 WHERE pah.application_id = p_application_id
856 AND pah.amb_context_code = p_amb_context_code
857 AND pah.description_type_code = h.description_type_code
858 AND pah.description_code = h.description_code
859 AND pah.product_rule_type_code = p_owner_type
860 AND pah.product_rule_code = grp.product_rule_code
861 AND grp.group_num = l_curr_group_num
862 UNION
863 SELECT 1
864 FROM xla_aad_line_defn_assgns xal
865 ,xla_line_defn_jlt_assgns xjl
866 ,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
867 WHERE xjl.application_id = p_application_id
868 AND xjl.amb_context_code = p_amb_context_code
869 AND xjl.description_type_code = h.description_type_code
870 AND xjl.description_code = h.description_code
871 AND xal.application_id = p_application_id
872 AND xal.amb_context_code = p_amb_context_code
873 AND xal.product_rule_type_code = p_owner_type
874 AND xal.product_rule_code = grp.product_rule_code
875 AND xal.event_class_code = xjl.event_class_code
876 AND xal.event_type_code = xjl.event_type_code
877 AND xal.line_definition_owner_code = xjl.line_definition_owner_code
878 AND xal.line_definition_code = xjl.line_definition_code
879 AND grp.group_num = l_curr_group_num
880 UNION
881 SELECT 1
882 FROM xla_aad_line_defn_assgns xal
883 ,xla_line_defn_jlt_assgns xjl
884 ,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
885 WHERE xjl.application_id = p_application_id
886 AND xjl.amb_context_code = p_amb_context_code
887 AND xjl.mpa_header_desc_type_code = h.description_type_code
888 AND xjl.mpa_header_desc_code = h.description_code
889 AND xal.application_id = p_application_id
890 AND xal.amb_context_code = p_amb_context_code
891 AND xal.product_rule_type_code = p_owner_type
892 AND xal.product_rule_code = grp.product_rule_code
893 AND xal.event_class_code = xjl.event_class_code
894 AND xal.event_type_code = xjl.event_type_code
895 AND xal.line_definition_owner_code = xjl.line_definition_owner_code
896 AND xal.line_definition_code = xjl.line_definition_code
897 AND grp.group_num = l_curr_group_num
898 UNION
899 SELECT 1
900 FROM xla_aad_line_defn_assgns xal
901 ,xla_mpa_jlt_assgns xjl
902 ,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
903 WHERE xjl.application_id = p_application_id
904 AND xjl.amb_context_code = p_amb_context_code
905 AND xjl.description_type_code = h.description_type_code
906 AND xjl.description_code = h.description_code
907 AND xal.application_id = p_application_id
908 AND xal.amb_context_code = p_amb_context_code
909 AND xal.product_rule_type_code = p_owner_type
910 AND xal.product_rule_code = grp.product_rule_code
911 AND xal.event_class_code = xjl.event_class_code
912 AND xal.event_type_code = xjl.event_type_code
913 AND xal.line_definition_owner_code = xjl.line_definition_owner_code
914 AND xal.line_definition_code = xjl.line_definition_code
915 AND grp.group_num = l_curr_group_num)
916 UNION
917 SELECT xal.product_rule_code
918 FROM xla_line_defn_jlt_assgns h -- line description
919 ,xla_aad_line_defn_assgns xal
920 WHERE h.application_id = xal.application_id
921 AND h.amb_context_code = xal.amb_context_code
922 AND h.event_class_code = xal.event_class_code
923 AND h.event_type_code = xal.event_type_code
924 AND h.line_definition_owner_code = xal.line_definition_owner_code
925 AND h.line_definition_code = xal.line_definition_code
926 AND xal.application_id = p_application_id
927 AND xal.amb_context_code = p_amb_context_code
928 AND xal.product_rule_type_code = p_owner_type
929 AND EXISTS (SELECT 1
930 FROM xla_prod_acct_headers pah
931 ,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
932 WHERE pah.application_id = p_application_id
933 AND pah.amb_context_code = p_amb_context_code
934 AND pah.description_type_code = h.description_type_code
935 AND pah.description_code = h.description_code
936 AND pah.product_rule_type_code = p_owner_type
937 AND pah.product_rule_code = grp.product_rule_code
938 AND grp.group_num = l_curr_group_num
939 UNION
940 SELECT 1
941 FROM xla_aad_line_defn_assgns xad
942 ,xla_line_defn_jlt_assgns xjl
943 ,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
944 WHERE xjl.application_id = p_application_id
945 AND xjl.amb_context_code = p_amb_context_code
946 AND xjl.description_type_code = h.description_type_code
947 AND xjl.description_code = h.description_code
948 AND xad.application_id = p_application_id
949 AND xad.amb_context_code = p_amb_context_code
950 AND xad.product_rule_type_code = p_owner_type
951 AND xad.product_rule_code = grp.product_rule_code
952 AND xad.event_class_code = xjl.event_class_code
953 AND xad.event_type_code = xjl.event_type_code
954 AND xad.line_definition_owner_code = xjl.line_definition_owner_code
955 AND xad.line_definition_code = xjl.line_definition_code
956 AND grp.group_num = l_curr_group_num
957 UNION
958 SELECT 1
959 FROM xla_aad_line_defn_assgns xal
960 ,xla_line_defn_jlt_assgns xjl
961 ,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
962 WHERE xjl.application_id = p_application_id
963 AND xjl.amb_context_code = p_amb_context_code
964 AND xjl.mpa_header_desc_type_code = h.description_type_code
965 AND xjl.mpa_header_desc_code = h.description_code
966 AND xal.application_id = p_application_id
967 AND xal.amb_context_code = p_amb_context_code
968 AND xal.product_rule_type_code = p_owner_type
969 AND xal.product_rule_code = grp.product_rule_code
970 AND xal.event_class_code = xjl.event_class_code
971 AND xal.event_type_code = xjl.event_type_code
972 AND xal.line_definition_owner_code = xjl.line_definition_owner_code
973 AND xal.line_definition_code = xjl.line_definition_code
974 AND grp.group_num = l_curr_group_num
975 UNION
976 SELECT 1
977 FROM xla_aad_line_defn_assgns xal
978 ,xla_mpa_jlt_assgns xjl
979 ,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
980 WHERE xjl.application_id = p_application_id
981 AND xjl.amb_context_code = p_amb_context_code
982 AND xjl.description_type_code = h.description_type_code
983 AND xjl.description_code = h.description_code
984 AND xal.application_id = p_application_id
985 AND xal.amb_context_code = p_amb_context_code
986 AND xal.product_rule_type_code = p_owner_type
987 AND xal.product_rule_code = grp.product_rule_code
988 AND xal.event_class_code = xjl.event_class_code
989 AND xal.event_type_code = xjl.event_type_code
990 AND xal.line_definition_owner_code = xjl.line_definition_owner_code
991 AND xal.line_definition_code = xjl.line_definition_code
992 AND grp.group_num = l_curr_group_num)
993 UNION
994 SELECT xal.product_rule_code
995 FROM xla_line_defn_jlt_assgns h -- MPA header description
996 ,xla_aad_line_defn_assgns xal
997 WHERE h.application_id = xal.application_id
998 AND h.amb_context_code = xal.amb_context_code
999 AND h.event_class_code = xal.event_class_code
1000 AND h.event_type_code = xal.event_type_code
1001 AND h.line_definition_owner_code = xal.line_definition_owner_code
1002 AND h.line_definition_code = xal.line_definition_code
1003 AND xal.application_id = p_application_id
1004 AND xal.amb_context_code = p_amb_context_code
1005 AND xal.product_rule_type_code = p_owner_type
1006 AND EXISTS (SELECT 1
1007 FROM xla_prod_acct_headers pah
1008 ,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
1009 WHERE pah.application_id = p_application_id
1010 AND pah.amb_context_code = p_amb_context_code
1011 AND pah.description_type_code = h.mpa_header_desc_type_code
1012 AND pah.description_code = h.mpa_header_desc_code
1013 AND pah.product_rule_type_code = p_owner_type
1014 AND pah.product_rule_code = grp.product_rule_code
1015 AND grp.group_num = l_curr_group_num
1016 UNION
1017 SELECT 1
1018 FROM xla_aad_line_defn_assgns xad
1019 ,xla_line_defn_jlt_assgns xjl
1020 ,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
1021 WHERE xjl.application_id = p_application_id
1022 AND xjl.amb_context_code = p_amb_context_code
1023 AND xjl.description_type_code = h.mpa_header_desc_type_code
1024 AND xjl.description_code = h.mpa_header_desc_code
1025 AND xad.application_id = p_application_id
1026 AND xad.amb_context_code = p_amb_context_code
1027 AND xad.product_rule_type_code = p_owner_type
1028 AND xad.product_rule_code = grp.product_rule_code
1029 AND xad.event_class_code = xjl.event_class_code
1030 AND xad.event_type_code = xjl.event_type_code
1031 AND xad.line_definition_owner_code = xjl.line_definition_owner_code
1032 AND xad.line_definition_code = xjl.line_definition_code
1033 AND grp.group_num = l_curr_group_num
1034 UNION
1035 SELECT 1
1036 FROM xla_aad_line_defn_assgns xal
1037 ,xla_line_defn_jlt_assgns xjl
1038 ,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
1039 WHERE xjl.application_id = p_application_id
1040 AND xjl.amb_context_code = p_amb_context_code
1041 AND xjl.mpa_header_desc_type_code = h.mpa_header_desc_type_code
1042 AND xjl.mpa_header_desc_code = h.mpa_header_desc_code
1043 AND xal.application_id = p_application_id
1044 AND xal.amb_context_code = p_amb_context_code
1045 AND xal.product_rule_type_code = p_owner_type
1046 AND xal.product_rule_code = grp.product_rule_code
1047 AND xal.event_class_code = xjl.event_class_code
1048 AND xal.event_type_code = xjl.event_type_code
1049 AND xal.line_definition_owner_code = xjl.line_definition_owner_code
1050 AND xal.line_definition_code = xjl.line_definition_code
1051 AND grp.group_num = l_curr_group_num
1052 UNION
1053 SELECT 1
1054 FROM xla_aad_line_defn_assgns xal
1055 ,xla_mpa_jlt_assgns xjl
1056 ,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
1057 WHERE xjl.application_id = p_application_id
1058 AND xjl.amb_context_code = p_amb_context_code
1059 AND xjl.description_type_code = h.mpa_header_desc_type_code
1060 AND xjl.description_code = h.mpa_header_desc_code
1061 AND xal.application_id = p_application_id
1062 AND xal.amb_context_code = p_amb_context_code
1063 AND xal.product_rule_type_code = p_owner_type
1064 AND xal.product_rule_code = grp.product_rule_code
1065 AND xal.event_class_code = xjl.event_class_code
1066 AND xal.event_type_code = xjl.event_type_code
1067 AND xal.line_definition_owner_code = xjl.line_definition_owner_code
1068 AND xal.line_definition_code = xjl.line_definition_code
1069 AND grp.group_num = l_curr_group_num)
1070 UNION
1071 SELECT xal.product_rule_code
1072 FROM xla_mpa_jlt_assgns h -- MPA line description
1073 ,xla_aad_line_defn_assgns xal
1074 WHERE h.application_id = xal.application_id
1075 AND h.amb_context_code = xal.amb_context_code
1076 AND h.event_class_code = xal.event_class_code
1077 AND h.event_type_code = xal.event_type_code
1078 AND h.line_definition_owner_code = xal.line_definition_owner_code
1079 AND h.line_definition_code = xal.line_definition_code
1080 AND xal.application_id = p_application_id
1081 AND xal.amb_context_code = p_amb_context_code
1082 AND xal.product_rule_type_code = p_owner_type
1083 AND EXISTS (SELECT 1
1084 FROM xla_prod_acct_headers pah
1085 ,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
1086 WHERE pah.application_id = p_application_id
1087 AND pah.amb_context_code = p_amb_context_code
1088 AND pah.description_type_code = h.description_type_code
1089 AND pah.description_code = h.description_code
1090 AND pah.product_rule_type_code = p_owner_type
1091 AND pah.product_rule_code = grp.product_rule_code
1092 AND grp.group_num = l_curr_group_num
1093 UNION
1094 SELECT 1
1095 FROM xla_aad_line_defn_assgns xad
1096 ,xla_line_defn_jlt_assgns xjl
1097 ,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
1098 WHERE xjl.application_id = p_application_id
1099 AND xjl.amb_context_code = p_amb_context_code
1100 AND xjl.description_type_code = h.description_type_code
1101 AND xjl.description_code = h.description_code
1102 AND xad.application_id = p_application_id
1103 AND xad.amb_context_code = p_amb_context_code
1104 AND xad.product_rule_type_code = p_owner_type
1105 AND xad.product_rule_code = grp.product_rule_code
1106 AND xad.event_class_code = xjl.event_class_code
1107 AND xad.event_type_code = xjl.event_type_code
1108 AND xad.line_definition_owner_code = xjl.line_definition_owner_code
1109 AND xad.line_definition_code = xjl.line_definition_code
1110 AND grp.group_num = l_curr_group_num
1111 UNION
1112 SELECT 1
1113 FROM xla_aad_line_defn_assgns xal
1114 ,xla_line_defn_jlt_assgns xjl
1115 ,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
1116 WHERE xjl.application_id = p_application_id
1117 AND xjl.amb_context_code = p_amb_context_code
1118 AND xjl.mpa_header_desc_type_code = h.description_type_code
1119 AND xjl.mpa_header_desc_code = h.description_code
1120 AND xal.application_id = p_application_id
1121 AND xal.amb_context_code = p_amb_context_code
1122 AND xal.product_rule_type_code = p_owner_type
1123 AND xal.product_rule_code = grp.product_rule_code
1124 AND xal.event_class_code = xjl.event_class_code
1125 AND xal.event_type_code = xjl.event_type_code
1126 AND xal.line_definition_owner_code = xjl.line_definition_owner_code
1127 AND xal.line_definition_code = xjl.line_definition_code
1128 AND grp.group_num = l_curr_group_num
1129 UNION
1130 SELECT 1
1131 FROM xla_aad_line_defn_assgns xal
1132 ,xla_mpa_jlt_assgns xjl
1133 ,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
1134 WHERE xjl.application_id = p_application_id
1135 AND xjl.amb_context_code = p_amb_context_code
1136 AND xjl.description_type_code = h.description_type_code
1137 AND xjl.description_code = h.description_code
1138 AND xal.application_id = p_application_id
1139 AND xal.amb_context_code = p_amb_context_code
1140 AND xal.product_rule_type_code = p_owner_type
1141 AND xal.product_rule_code = grp.product_rule_code
1142 AND xal.event_class_code = xjl.event_class_code
1143 AND xal.event_type_code = xjl.event_type_code
1144 AND xal.line_definition_owner_code = xjl.line_definition_owner_code
1145 AND xal.line_definition_code = xjl.line_definition_code
1146 AND grp.group_num = l_curr_group_num)
1147 UNION
1148 SELECT xal.product_rule_code
1149 FROM xla_line_defn_jlt_assgns h -- JLT
1150 ,xla_aad_line_defn_assgns xal
1151 WHERE h.application_id = xal.application_id
1152 AND h.amb_context_code = xal.amb_context_code
1153 AND h.event_class_code = xal.event_class_code
1154 AND h.event_type_code = xal.event_type_code
1155 AND h.line_definition_owner_code = xal.line_definition_owner_code
1156 AND h.line_definition_code = xal.line_definition_code
1157 AND xal.application_id = p_application_id
1158 AND xal.amb_context_code = p_amb_context_code
1159 AND xal.product_rule_type_code = p_owner_type
1160 AND EXISTS (SELECT 1
1161 FROM xla_aad_line_defn_assgns xad
1162 ,xla_line_defn_jlt_assgns xjl
1163 ,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
1164 WHERE xjl.application_id = p_application_id
1165 AND xjl.amb_context_code = p_amb_context_code
1166 AND xjl.event_class_code = h.event_class_code
1167 AND xjl.accounting_line_type_code = h.accounting_line_type_code
1168 AND xjl.accounting_line_code = h.accounting_line_code
1169 AND xad.event_class_code = xjl.event_class_code
1170 AND xad.event_type_code = xjl.event_type_code
1171 AND xad.line_definition_owner_code = xjl.line_definition_owner_code
1172 AND xad.line_definition_code = xjl.line_definition_code
1173 AND xad.application_id = p_application_id
1174 AND xad.amb_context_code = p_amb_context_code
1175 AND xad.product_rule_type_code = p_owner_type
1176 AND xad.product_rule_code = grp.product_rule_code
1177 AND grp.group_num = l_curr_group_num
1178 UNION
1179 SELECT 1
1180 FROM xla_aad_line_defn_assgns xad
1181 ,xla_mpa_jlt_assgns xjl
1182 ,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
1183 WHERE xjl.application_id = p_application_id
1184 AND xjl.amb_context_code = p_amb_context_code
1185 AND xjl.event_class_code = h.event_class_code
1186 AND xjl.mpa_accounting_line_type_code = h.accounting_line_type_code
1187 AND xjl.mpa_accounting_line_code = h.accounting_line_code
1188 AND xad.event_class_code = xjl.event_class_code
1189 AND xad.event_type_code = xjl.event_type_code
1190 AND xad.line_definition_owner_code = xjl.line_definition_owner_code
1191 AND xad.line_definition_code = xjl.line_definition_code
1192 AND xad.application_id = p_application_id
1193 AND xad.amb_context_code = p_amb_context_code
1194 AND xad.product_rule_type_code = p_owner_type
1195 AND xad.product_rule_code = grp.product_rule_code
1196 AND grp.group_num = l_curr_group_num)
1197 UNION
1198 SELECT xal.product_rule_code
1199 FROM xla_mpa_jlt_assgns h -- MPA JLT
1200 ,xla_aad_line_defn_assgns xal
1201 WHERE h.application_id = xal.application_id
1202 AND h.amb_context_code = xal.amb_context_code
1203 AND h.event_class_code = xal.event_class_code
1204 AND h.event_type_code = xal.event_type_code
1205 AND h.line_definition_owner_code = xal.line_definition_owner_code
1206 AND h.line_definition_code = xal.line_definition_code
1207 AND xal.application_id = p_application_id
1208 AND xal.amb_context_code = p_amb_context_code
1209 AND xal.product_rule_type_code = p_owner_type
1210 AND EXISTS (SELECT 1
1211 FROM xla_aad_line_defn_assgns xad
1212 ,xla_line_defn_jlt_assgns xjl
1213 ,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
1214 WHERE xjl.application_id = p_application_id
1215 AND xjl.amb_context_code = p_amb_context_code
1216 AND xjl.event_class_code = h.event_class_code
1217 AND xjl.accounting_line_type_code = h.accounting_line_type_code
1218 AND xjl.accounting_line_code = h.accounting_line_code
1219 AND xad.event_class_code = xjl.event_class_code
1220 AND xad.event_type_code = xjl.event_type_code
1221 AND xad.line_definition_owner_code = xjl.line_definition_owner_code
1222 AND xad.line_definition_code = xjl.line_definition_code
1223 AND xad.application_id = p_application_id
1224 AND xad.amb_context_code = p_amb_context_code
1225 AND xad.product_rule_type_code = p_owner_type
1226 AND xad.product_rule_code = grp.product_rule_code
1227 AND grp.group_num = l_curr_group_num
1228 UNION
1229 SELECT 1
1230 FROM xla_aad_line_defn_assgns xad
1231 ,xla_mpa_jlt_assgns xjl
1232 ,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
1233 WHERE xjl.application_id = p_application_id
1234 AND xjl.amb_context_code = p_amb_context_code
1235 AND xjl.event_class_code = h.event_class_code
1236 AND xjl.mpa_accounting_line_type_code = h.accounting_line_type_code
1237 AND xjl.mpa_accounting_line_code = h.accounting_line_code
1238 AND xad.event_class_code = xjl.event_class_code
1239 AND xad.event_type_code = xjl.event_type_code
1240 AND xad.line_definition_owner_code = xjl.line_definition_owner_code
1241 AND xad.line_definition_code = xjl.line_definition_code
1242 AND xad.application_id = p_application_id
1243 AND xad.amb_context_code = p_amb_context_code
1244 AND xad.product_rule_type_code = p_owner_type
1245 AND xad.product_rule_code = grp.product_rule_code
1246 AND grp.group_num = l_curr_group_num);
1247
1248 -- Cursor to return the next AAD that is not grouped
1249 CURSOR c_next_aad IS
1250 SELECT product_rule_code
1251 FROM TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type))
1252 WHERE group_num = 0;
1253
1254 l_aad_group xla_aad_group_rec_type;
1255 l_updated BOOLEAN;
1256 l_code VARCHAR2(30);
1257 l_count INTEGER;
1258 l_log_module VARCHAR2(240);
1259 BEGIN
1260 IF g_log_enabled THEN
1261 l_log_module := C_DEFAULT_MODULE||'.group_aads';
1262 END IF;
1263
1264 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1265 trace(p_msg => 'BEGIN of procedure group_aads'
1266 ,p_level => C_LEVEL_PROCEDURE
1267 ,p_module => l_log_module);
1268 END IF;
1269
1270 g_aad_groups := xla_aad_group_tbl_type();
1271
1272 l_count := 0;
1273
1274 -- Initialize the AAD array
1275 IF (C_LEVEL_EVENT >= g_log_level) THEN
1276 trace(p_msg => 'BEGIN LOOP - retrieve AADs',
1277 p_module => l_log_module,
1278 p_level => C_LEVEL_EVENT);
1279 END IF;
1280
1281 -- Insert all AADs to be grouped in the the g_aad_groups array
1282 FOR l_aad IN c_aad LOOP
1283 IF (C_LEVEL_ERROR >= g_log_level) THEN
1284 trace(p_msg => 'LOOP - AAD: '||
1285 'product_rule_code='||l_aad.product_rule_code||
1286 ',version_num='||l_aad.version_num||
1287 ',updated_flag='||l_aad.updated_flag||
1288 ',leapfrog_flag='||l_aad.leapfrog_flag
1289 ,p_module => l_log_module
1290 ,p_level => C_LEVEL_ERROR);
1291 END IF;
1292
1293 l_aad_group := xla_aad_group_rec_type
1294 (p_owner_type
1295 ,l_aad.product_rule_code
1296 ,0
1297 ,l_aad.version_num
1298 ,l_aad.updated_flag
1299 ,l_aad.leapfrog_flag
1300 ,NULL);
1301
1302 l_count := l_count + 1;
1303 g_aad_groups.EXTEND;
1304 g_aad_groups(l_count) := l_aad_group;
1305 END LOOP;
1306
1307 IF (C_LEVEL_EVENT >= g_log_level) THEN
1308 trace(p_msg => 'END LOOP - retrieve AADs',
1309 p_module => l_log_module,
1310 p_level => C_LEVEL_EVENT);
1311 END IF;
1312
1313 l_curr_group_num := 1;
1314 IF (g_aad_groups.COUNT > 0) THEN
1315 g_aad_groups(1).group_num := l_curr_group_num;
1316 END IF;
1317
1318 --
1319 -- Loop until all application accounting definitions are assigned
1320 -- with a group number
1321 --
1322 LOOP
1323 IF (C_LEVEL_ERROR >= g_log_level) THEN
1324 trace(p_msg => 'BEGIN LOOP - current group number = '||l_curr_group_num,
1325 p_module => l_log_module,
1326 p_level => C_LEVEL_ERROR);
1327 END IF;
1328 --
1329 -- Loop until no more new application accounting definitions is
1330 -- found to be sharing any journal entry setups with the
1331 -- definitions in the current group.
1332 --
1333 LOOP
1334 IF (C_LEVEL_ERROR >= g_log_level) THEN
1335 trace(p_msg => 'BEGIN LOOP - Retrieve group = '||l_curr_group_num,
1336 p_module => l_log_module,
1337 p_level => C_LEVEL_ERROR);
1338 END IF;
1339
1340 OPEN c_aad_group;
1341 l_updated := FALSE;
1342
1343 --
1344 -- Loop until all application accounting definitions that
1345 -- shares journal entry sets with the definitions in the
1346 -- current group are marked with the current group number.
1347 LOOP
1348 FETCH c_aad_group INTO l_code;
1349 EXIT WHEN c_aad_group%NOTFOUND;
1350
1351 IF (C_LEVEL_ERROR >= g_log_level) THEN
1352 trace(p_msg => 'LOOP - group = '||l_curr_group_num||
1353 ', aad = '||l_code,
1354 p_module => l_log_module,
1355 p_level => C_LEVEL_ERROR);
1356 END IF;
1357
1358 IF (update_group_number(l_code
1359 ,l_curr_group_num)) THEN
1360 l_updated := TRUE;
1361 END IF;
1362 END LOOP;
1363 CLOSE c_aad_group;
1364 --
1365 IF (NOT l_updated) THEN
1366 IF (C_LEVEL_ERROR >= g_log_level) THEN
1367 trace(p_msg => 'l_updated = FALSE, EXIT',
1368 p_module => l_log_module,
1369 p_level => C_LEVEL_ERROR);
1370 END IF;
1371
1372 EXIT;
1373 END IF;
1374 END LOOP;
1375
1376 OPEN c_next_aad;
1377 FETCH c_next_aad INTO l_code;
1378 EXIT WHEN c_next_aad%NOTFOUND;
1379
1380 IF (C_LEVEL_ERROR >= g_log_level) THEN
1381 trace(p_msg => 'Next AAD = '||l_code,
1382 p_module => l_log_module,
1383 p_level => C_LEVEL_ERROR);
1384 END IF;
1385
1386 CLOSE c_next_aad;
1387 l_curr_group_num := l_curr_group_num + 1;
1388 l_updated := update_group_number(l_code
1389 ,l_curr_group_num);
1390 END LOOP;
1391 CLOSE c_next_aad;
1392
1393 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1394 FOR i IN 1 .. g_aad_groups.COUNT LOOP
1395 trace(p_msg => 'group='||g_aad_groups(i).group_num||
1396 ' '||g_aad_groups(i).product_rule_code
1397 ,p_module => l_log_module
1398 ,p_level => C_LEVEL_PROCEDURE);
1399 END LOOP;
1400 END IF;
1401
1402 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1403 trace(p_msg => 'END of procedure group_aads'
1404 ,p_module => l_log_module
1405 ,p_level => C_LEVEL_PROCEDURE);
1406 END IF;
1407 EXCEPTION
1408 WHEN OTHERS THEN
1409 xla_aad_loader_util_pvt.stack_error
1410 (p_appli_s_name => 'XLA'
1411 ,p_msg_name => 'XLA_COMMON_ERROR'
1412 ,p_token_1 => 'LOCATION'
1413 ,p_value_1 => 'xla_aad_export_pvt.group_aads'
1414 ,p_token_2 => 'ERROR'
1415 ,p_value_2 => 'unhandled exception');
1416 RAISE;
1417
1418 END group_aads;
1419
1420
1421 --=============================================================================
1422 --
1423 -- Name: update_aad_version
1424 -- Description: This API updates the veresion of the AAD
1425 --
1426 --=============================================================================
1427 PROCEDURE update_aad_version
1428 (p_application_id IN INTEGER
1429 ,p_amb_context_code IN VARCHAR2
1430 ,p_owner_type IN VARCHAR2
1431 ,p_versioning_mode IN VARCHAR2
1432 ,p_user_version IN VARCHAR2
1433 ,p_version_comment IN VARCHAR2)
1434 IS
1435 CURSOR c_aad_version IS
1436 SELECT distinct
1437 grp.product_rule_code
1438 ,grp.version_num version_from
1439 ,(MAX(NVL(h.version_num,0)) OVER (PARTITION BY grp.group_num))+1 version_to
1440 FROM TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
1441 , xla_aads_h h
1442 WHERE h.application_id(+) = p_application_id
1443 AND h.product_rule_type_code(+) = p_owner_type
1444 AND h.product_rule_code(+) = grp.product_rule_code
1445 AND grp.group_num IN
1446 (SELECT grp2.group_num
1447 FROM TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp2
1448 WHERE grp2.updated_flag = 'Y'
1449 OR grp2.leapfrog_flag = 'Y');
1450
1451 CURSOR c_aad_unchanged IS
1452 SELECT grp.product_rule_code
1453 , max(h.version_num) version_to
1454 FROM TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
1455 , xla_aads_h h
1456 WHERE h.application_id = p_application_id
1457 AND h.product_rule_type_code = p_owner_type
1458 AND h.product_rule_code = grp.product_rule_code
1459 AND NOT EXISTS
1460 (SELECT 1
1461 FROM TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp2
1462 WHERE (grp2.updated_flag = 'Y' OR
1463 grp2.leapfrog_flag = 'Y')
1464 AND grp2.group_num = grp.group_num)
1465 GROUP BY grp.product_rule_code;
1466
1467 l_aad_codes t_array_varchar2;
1468 l_versions_from t_array_int;
1469 l_versions_to t_array_int;
1470 i INTEGER;
1471
1472 l_log_module VARCHAR2(240);
1473 BEGIN
1474 IF g_log_enabled THEN
1475 l_log_module := C_DEFAULT_MODULE||'.update_aad_version';
1476 END IF;
1477
1478 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1479 trace(p_msg => 'BEGIN of procedure update_aad_version',
1480 p_module => l_log_module,
1481 p_level => C_LEVEL_PROCEDURE);
1482 END IF;
1483
1484 group_aads
1485 (p_application_id => p_application_id
1486 ,p_amb_context_code => p_amb_context_code
1487 ,p_owner_type => p_owner_type);
1488
1489 i := 0;
1490 IF (C_LEVEL_EVENT >= g_log_level) THEN
1491 trace(p_msg => 'BEGIN LOOP - AAD versions',
1492 p_module => l_log_module,
1493 p_level => C_LEVEL_EVENT);
1494 END IF;
1495
1496 FOR l_aad IN c_aad_version LOOP
1497 IF (C_LEVEL_EVENT >= g_log_level) THEN
1498 trace(p_msg => 'LOOP - AAD version: '||
1499 'product_rule_code='||l_aad.product_rule_code||
1500 ',version_from='||l_aad.version_from||
1501 ',version_to='||l_aad.version_to
1502 ,p_module => l_log_module
1503 ,p_level => C_LEVEL_EVENT);
1504 END IF;
1505
1506 i := i + 1;
1507 l_aad_codes(i) := l_aad.product_rule_code;
1508 l_versions_from(i) := l_aad.version_from;
1509 l_versions_to(i) := l_aad.version_to;
1510 END LOOP;
1511
1512 IF (C_LEVEL_EVENT >= g_log_level) THEN
1513 trace(p_msg => 'END LOOP - AAD versions',
1514 p_module => l_log_module,
1515 p_level => C_LEVEL_EVENT);
1516 END IF;
1517
1518 FORALL i IN 1 .. l_aad_codes.COUNT
1519 INSERT INTO xla_aads_h
1520 (application_id
1521 ,product_rule_type_code
1522 ,product_rule_code
1523 ,version_num
1524 ,base_version_num
1525 ,user_version
1526 ,version_comment
1527 ,leapfrog_flag
1528 ,object_version_number
1529 ,creation_date
1530 ,created_by
1531 ,last_update_date
1532 ,last_updated_by
1533 ,last_update_login
1534 ,program_update_date
1535 ,program_application_id
1536 ,program_id
1537 ,request_id)
1538 VALUES
1539 (p_application_id
1540 ,p_owner_type
1541 ,l_aad_codes(i)
1542 ,l_versions_to(i)
1543 ,l_versions_from(i)
1544 ,p_user_version
1545 ,p_version_comment
1546 ,DECODE(p_versioning_mode,'LEAPFROG','Y','N')
1547 ,1
1548 ,sysdate
1549 ,xla_environment_pkg.g_usr_id
1550 ,sysdate
1551 ,xla_environment_pkg.g_usr_id
1552 ,xla_environment_pkg.g_login_id
1553 ,sysdate
1554 ,xla_environment_pkg.g_prog_appl_id
1555 ,xla_environment_pkg.g_prog_id
1556 ,xla_environment_pkg.g_req_Id);
1557
1558 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1559 trace(p_msg => '# row inserted in xla_aads_h = '||SQL%ROWCOUNT,
1560 p_module => l_log_module,
1561 p_level => C_LEVEL_STATEMENT);
1562 END IF;
1563
1564 IF (C_LEVEL_EVENT >= g_log_level) THEN
1565 trace(p_msg => 'BEGIN LOOP - unchanged AAD',
1566 p_module => l_log_module,
1567 p_level => C_LEVEL_EVENT);
1568 END IF;
1569
1570 FOR l_aad IN c_aad_unchanged LOOP
1571 IF (C_LEVEL_EVENT >= g_log_level) THEN
1572 trace(p_msg => 'LOOP - unchanged AAD: '||
1573 'product_rule_code='||l_aad.product_rule_code
1574 ,p_module => l_log_module
1575 ,p_level => C_LEVEL_EVENT);
1576 END IF;
1577
1578 i := i + 1;
1579 l_aad_codes(i) := l_aad.product_rule_code;
1580 l_versions_from(i) := -1;
1581 l_versions_to(i) := l_aad.version_to;
1582 END LOOP;
1583
1584 IF (C_LEVEL_EVENT >= g_log_level) THEN
1585 trace(p_msg => 'END LOOP - unchanged AAD',
1586 p_module => l_log_module,
1587 p_level => C_LEVEL_EVENT);
1588 END IF;
1589
1590 FORALL i IN 1 .. l_aad_codes.COUNT
1591 UPDATE xla_product_rules_b
1592 SET version_num = l_versions_to(i)
1593 ,updated_flag = 'N'
1594 ,product_rule_version = p_user_version
1595 ,creation_date = sysdate
1596 ,created_by = xla_environment_pkg.g_usr_id
1597 ,last_update_date = sysdate
1598 ,last_updated_by = xla_environment_pkg.g_usr_id
1599 ,last_update_login = xla_environment_pkg.g_login_id
1600 WHERE application_id = p_application_id
1601 AND amb_context_code = p_amb_context_code
1602 AND product_rule_type_code = p_owner_type
1603 AND product_rule_code = l_aad_codes(i);
1604
1605 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1606 trace(p_msg => '# row updated in xla_product_rules_b = '||SQL%ROWCOUNT,
1607 p_module => l_log_module,
1608 p_level => C_LEVEL_STATEMENT);
1609 END IF;
1610
1611 FORALL i IN 1 .. l_aad_codes.COUNT
1612 UPDATE xla_aads_h
1613 SET user_version = p_user_version
1614 , version_comment = p_version_comment
1615 , program_update_date = sysdate
1616 , program_application_id = xla_environment_pkg.g_prog_appl_id
1617 , program_id = xla_environment_pkg.g_prog_id
1618 , request_id = xla_environment_pkg.g_req_Id
1619 WHERE application_id = p_application_id
1620 AND product_rule_type_code = p_owner_type
1621 AND product_rule_code = l_aad_codes(i)
1622 AND version_num = l_versions_to(i)
1623 AND (NVL(user_version,C_CHAR) <> NVL(p_user_version,C_CHAR) OR
1624 NVL(version_comment,C_CHAR) <> NVL(p_version_comment,C_CHAR));
1625
1626 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1627 trace(p_msg => '# row updated in xla_aads_h = '||SQL%ROWCOUNT,
1628 p_module => l_log_module,
1629 p_level => C_LEVEL_STATEMENT);
1630 END IF;
1631
1632 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1633 trace(p_msg => 'END of procedure update_aad_version',
1634 p_module => l_log_module,
1635 p_level => C_LEVEL_PROCEDURE);
1636 END IF;
1637
1638 EXCEPTION
1639 WHEN OTHERS THEN
1640 xla_aad_loader_util_pvt.stack_error
1641 (p_appli_s_name => 'XLA'
1642 ,p_msg_name => 'XLA_COMMON_ERROR'
1643 ,p_token_1 => 'LOCATION'
1644 ,p_value_1 => 'xla_aad_export_pvt.update_aad_version'
1645 ,p_token_2 => 'ERROR'
1646 ,p_value_2 => 'unhandled exception');
1647 RAISE;
1648
1649 END update_aad_version;
1650
1651 --=============================================================================
1652 --
1653 -- Name: update_ac_version
1654 -- Description: This API updates the version of the analytical criteria
1655 --
1656 --=============================================================================
1657 PROCEDURE update_ac_version
1658 (p_application_id IN INTEGER
1659 ,p_amb_context_code IN VARCHAR2
1660 ,p_versioning_mode IN VARCHAR2)
1661 IS
1662 CURSOR c_ac IS
1663 SELECT b.analytical_criterion_type_code
1664 ,b.analytical_criterion_code
1665 ,b.version_num version_from
1666 ,MAX(NVL(h.version_num,0))+1 version_to
1667 FROM xla_analytical_hdrs_b b
1668 ,xla_amb_components_h h
1669 WHERE h.component_owner_code(+) = b.analytical_criterion_type_code
1670 AND h.component_code(+) = b.analytical_criterion_code
1671 AND h.component_type_code(+) = 'ANALYTICAL_CRITERION'
1672 AND b.updated_flag = 'Y'
1673 AND EXISTS
1674 (SELECT 1
1675 FROM xla_aad_header_ac_assgns a
1676 WHERE a.application_id = p_application_id
1677 AND a.amb_context_code = p_amb_context_code
1678 AND b.amb_context_code = a.amb_context_code
1679 AND b.analytical_criterion_type_code = a.analytical_criterion_type_code
1680 AND b.analytical_criterion_code = a.analytical_criterion_code
1681 UNION
1682 SELECT 1
1683 FROM xla_aad_line_defn_assgns l
1684 , xla_line_defn_ac_assgns a
1685 WHERE l.application_id = p_application_id
1686 AND l.amb_context_code = p_amb_context_code
1687 AND a.application_id = l.application_id
1688 AND a.amb_context_code = l.amb_context_code
1689 AND a.event_class_code = l.event_class_code
1690 AND a.event_type_code = l.event_type_code
1691 AND a.line_definition_owner_code = l.line_definition_owner_code
1692 AND a.line_definition_code = l.line_definition_code
1693 AND b.amb_context_code = a.amb_context_code
1694 AND b.analytical_criterion_type_code = a.analytical_criterion_type_code
1695 AND b.analytical_criterion_code = a.analytical_criterion_code)
1696 GROUP BY b.analytical_criterion_type_code, b.analytical_criterion_code, b.version_num;
1697
1698 l_ac_owner_codes t_array_varchar2;
1699 l_ac_codes t_array_varchar2;
1700 l_ac_version_from t_array_int;
1701 l_ac_version_to t_array_int;
1702 i INTEGER;
1703
1704 l_log_module VARCHAR2(240);
1705 BEGIN
1706 IF g_log_enabled THEN
1707 l_log_module := C_DEFAULT_MODULE||'.update_ac_version';
1708 END IF;
1709
1710 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1711 trace(p_msg => 'BEGIN of procedure update_ac_version',
1712 p_module => l_log_module,
1713 p_level => C_LEVEL_PROCEDURE);
1714 END IF;
1715
1716 i := 0;
1717
1718 IF (C_LEVEL_EVENT >= g_log_level) THEN
1719 trace(p_msg => 'BEGIN LOOP - retrieve analytical criteria',
1720 p_module => l_log_module,
1721 p_level => C_LEVEL_EVENT);
1722 END IF;
1723
1724 FOR l_ac in c_ac LOOP
1725 IF (C_LEVEL_EVENT >= g_log_level) THEN
1726 trace(p_msg => 'LOOP - analytical criterion = '||
1727 l_ac.analytical_criterion_type_code||','||
1728 l_ac.analytical_criterion_code||','||
1729 l_ac.version_from||','||
1730 l_ac.version_to,
1731 p_module => l_log_module,
1732 p_level => C_LEVEL_EVENT);
1733 END IF;
1734
1735 i := i + 1;
1736 l_ac_owner_codes(i) := l_ac.analytical_criterion_type_code;
1737 l_ac_codes(i) := l_ac.analytical_criterion_code;
1738 l_ac_version_from(i) := l_ac.version_from;
1739 l_ac_version_to(i) := l_ac.version_to;
1740 END LOOP;
1741
1742 FORALL i IN 1 .. l_ac_codes.COUNT
1743 INSERT INTO xla_amb_components_h
1744 (component_type_code
1745 ,component_owner_code
1746 ,component_code
1747 ,application_id
1748 ,version_num
1749 ,base_version_num
1750 ,leapfrog_flag
1751 ,object_version_number
1752 ,creation_date
1753 ,created_by
1754 ,last_update_date
1755 ,last_updated_by
1756 ,last_update_login
1757 ,program_update_date
1758 ,program_application_id
1759 ,program_id
1760 ,request_id)
1761 VALUES
1762 ('ANALYTICAL_CRITERION'
1763 ,l_ac_owner_codes(i)
1764 ,l_ac_codes(i)
1765 ,-1
1766 ,l_ac_version_to(i)
1767 ,l_ac_version_from(i)
1768 ,DECODE(p_versioning_mode,'LEAPFROG','Y','N')
1769 ,1
1770 ,sysdate
1771 ,xla_environment_pkg.g_usr_id
1772 ,sysdate
1773 ,xla_environment_pkg.g_usr_id
1774 ,xla_environment_pkg.g_login_id
1775 ,sysdate
1776 ,xla_environment_pkg.g_prog_appl_id
1777 ,xla_environment_pkg.g_prog_id
1778 ,xla_environment_pkg.g_req_Id);
1779
1780 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1781 trace(p_msg => '# row inserted in xla_amb_components_h = '||SQL%ROWCOUNT,
1782 p_module => l_log_module,
1783 p_level => C_LEVEL_STATEMENT);
1784 END IF;
1785
1786 FORALL i IN 1 .. l_ac_codes.COUNT
1787 UPDATE xla_analytical_hdrs_b
1788 SET version_num = l_ac_version_to(i)
1789 ,updated_flag = 'N'
1790 ,creation_date = sysdate
1791 ,created_by = xla_environment_pkg.g_usr_id
1792 ,last_update_date = sysdate
1793 ,last_updated_by = xla_environment_pkg.g_usr_id
1794 ,last_update_login = xla_environment_pkg.g_login_id
1795 WHERE analytical_criterion_type_code = l_ac_owner_codes(i)
1796 AND analytical_criterion_code = l_ac_codes(i)
1797 AND amb_context_code = p_amb_context_code;
1798
1799 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1800 trace(p_msg => '# row updated in xla_analytical_hdrs_b = '||SQL%ROWCOUNT,
1801 p_module => l_log_module,
1802 p_level => C_LEVEL_STATEMENT);
1803 END IF;
1804
1805 IF (C_LEVEL_EVENT >= g_log_level) THEN
1806 trace(p_msg => 'END LOOP - retrieve analytical criteria',
1807 p_module => l_log_module,
1808 p_level => C_LEVEL_EVENT);
1809 END IF;
1810
1811 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1812 trace(p_msg => 'END of procedure update_ac_version',
1813 p_module => l_log_module,
1814 p_level => C_LEVEL_PROCEDURE);
1815 END IF;
1816
1817 EXCEPTION
1818 WHEN OTHERS THEN
1819 xla_aad_loader_util_pvt.stack_error
1820 (p_appli_s_name => 'XLA'
1821 ,p_msg_name => 'XLA_COMMON_ERROR'
1822 ,p_token_1 => 'LOCATION'
1823 ,p_value_1 => 'xla_aad_export_pvt.update_ac_version'
1824 ,p_token_2 => 'ERROR'
1825 ,p_value_2 => 'unhandled exception');
1826 RAISE;
1827
1828 END update_ac_version;
1829
1830 --=============================================================================
1831 --
1832 -- Name: update_adr_version
1833 -- Description: This API updates the version of the adr of the exporting
1834 -- application that is used by any application
1835 --
1836 --=============================================================================
1837 PROCEDURE update_adr_version
1838 (p_application_id IN INTEGER
1839 ,p_amb_context_code IN VARCHAR2
1840 ,p_versioning_mode IN VARCHAR2)
1841 IS
1842 CURSOR c_adr IS
1843 SELECT b.segment_rule_type_code
1844 ,b.segment_rule_code
1845 ,b.version_num version_from
1846 ,MAX(NVL(h.version_num,0))+1 version_to
1847 FROM xla_seg_rules_b b
1848 ,xla_amb_components_h h
1849 WHERE h.application_id(+) = b.application_id
1850 AND h.component_owner_code(+) = b.segment_rule_type_code
1851 AND h.component_code(+) = b.segment_rule_code
1852 AND h.component_type_code(+) = 'AMB_ADR'
1853 AND b.amb_context_code = p_amb_context_code
1854 AND b.application_id = p_application_id
1855 AND b.updated_flag = 'Y'
1856 GROUP BY b.segment_rule_type_code, b.segment_rule_code, b.version_num;
1857
1858 l_adr_owner_codes t_array_varchar2;
1859 l_adr_codes t_array_varchar2;
1860 l_adr_version_from t_array_int;
1861 l_adr_version_to t_array_int;
1862 i INTEGER;
1863
1864 l_log_module VARCHAR2(240);
1865 BEGIN
1866 IF g_log_enabled THEN
1867 l_log_module := C_DEFAULT_MODULE||'.update_adr_version';
1868 END IF;
1869
1870 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1871 trace(p_msg => 'BEGIN of procedure update_adr_version',
1872 p_module => l_log_module,
1873 p_level => C_LEVEL_PROCEDURE);
1874 END IF;
1875
1876 i := 0;
1877
1878 IF (C_LEVEL_EVENT >= g_log_level) THEN
1879 trace(p_msg => 'BEGIN LOOP - retrieve adrs',
1880 p_module => l_log_module,
1881 p_level => C_LEVEL_EVENT);
1882 END IF;
1883
1884 FOR l_adr in c_adr LOOP
1885 IF (C_LEVEL_EVENT >= g_log_level) THEN
1886 trace(p_msg => 'LOOP - adr = '||
1887 l_adr.segment_rule_type_code||','||
1888 l_adr.segment_rule_code||','||
1889 l_adr.version_from||','||
1890 l_adr.version_to,
1891 p_module => l_log_module,
1892 p_level => C_LEVEL_EVENT);
1893 END IF;
1894
1895 i := i + 1;
1896 l_adr_owner_codes(i) := l_adr.segment_rule_type_code;
1897 l_adr_codes(i) := l_adr.segment_rule_code;
1898 l_adr_version_from(i) := l_adr.version_from;
1899 l_adr_version_to(i) := l_adr.version_to;
1900 END LOOP;
1901
1902 FORALL i IN 1 .. l_adr_codes.COUNT
1903 INSERT INTO xla_amb_components_h
1904 (application_id
1905 ,component_type_code
1906 ,component_owner_code
1907 ,component_code
1908 ,version_num
1909 ,base_version_num
1910 ,leapfrog_flag
1911 ,object_version_number
1912 ,creation_date
1913 ,created_by
1914 ,last_update_date
1915 ,last_updated_by
1916 ,last_update_login
1917 ,program_update_date
1918 ,program_application_id
1919 ,program_id
1920 ,request_id)
1921 VALUES
1922 (p_application_id
1923 ,'AMB_ADR'
1924 ,l_adr_owner_codes(i)
1925 ,l_adr_codes(i)
1926 ,l_adr_version_to(i)
1927 ,l_adr_version_from(i)
1928 ,DECODE(p_versioning_mode,'LEAPFROG','Y','N')
1929 ,1
1930 ,sysdate
1931 ,xla_environment_pkg.g_usr_id
1932 ,sysdate
1933 ,xla_environment_pkg.g_usr_id
1934 ,xla_environment_pkg.g_login_id
1935 ,sysdate
1936 ,xla_environment_pkg.g_prog_appl_id
1937 ,xla_environment_pkg.g_prog_id
1938 ,xla_environment_pkg.g_req_Id);
1939
1940 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1941 trace(p_msg => '# row inserted in xla_amb_components_h = '||SQL%ROWCOUNT,
1942 p_module => l_log_module,
1943 p_level => C_LEVEL_STATEMENT);
1944 END IF;
1945
1946 FORALL i IN 1 .. l_adr_codes.COUNT
1947 UPDATE xla_seg_rules_b
1948 SET version_num = l_adr_version_to(i)
1949 ,updated_flag = 'N'
1950 ,creation_date = sysdate
1951 ,created_by = xla_environment_pkg.g_usr_id
1952 ,last_update_date = sysdate
1953 ,last_updated_by = xla_environment_pkg.g_usr_id
1954 ,last_update_login = xla_environment_pkg.g_login_id
1955 WHERE segment_rule_type_code = l_adr_owner_codes(i)
1956 AND segment_rule_code = l_adr_codes(i)
1957 AND application_id = p_application_id
1958 AND amb_context_code = p_amb_context_code;
1959
1960 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1961 trace(p_msg => '# row updated in xla_seg_rules_b = '||SQL%ROWCOUNT,
1962 p_module => l_log_module,
1963 p_level => C_LEVEL_STATEMENT);
1964 END IF;
1965
1966 IF (C_LEVEL_EVENT >= g_log_level) THEN
1967 trace(p_msg => 'END LOOP - retrieve adrs',
1968 p_module => l_log_module,
1969 p_level => C_LEVEL_EVENT);
1970 END IF;
1971
1972 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1973 trace(p_msg => 'END of procedure update_adr_version',
1974 p_module => l_log_module,
1975 p_level => C_LEVEL_PROCEDURE);
1976 END IF;
1977
1978 EXCEPTION
1979 WHEN OTHERS THEN
1980 xla_aad_loader_util_pvt.stack_error
1981 (p_appli_s_name => 'XLA'
1982 ,p_msg_name => 'XLA_COMMON_ERROR'
1983 ,p_token_1 => 'LOCATION'
1984 ,p_value_1 => 'xla_aad_export_pvt.update_adr_version'
1985 ,p_token_2 => 'ERROR'
1986 ,p_value_2 => 'unhandled exception');
1987 RAISE;
1988
1989 END update_adr_version;
1990
1991 --=============================================================================
1992 --
1993 -- Name: update_ms_version
1994 -- Description: This API updates the version of the mapping sets
1995 --
1996 --=============================================================================
1997 PROCEDURE update_ms_version
1998 (p_application_id IN INTEGER
1999 ,p_amb_context_code IN VARCHAR2
2000 ,p_versioning_mode IN VARCHAR2)
2001 IS
2002 CURSOR c_ms IS
2003 SELECT b.mapping_set_code
2004 ,b.version_num version_from
2005 ,MAX(NVL(h.version_num,0))+1 version_to
2006 FROM xla_mapping_sets_b b
2007 ,xla_amb_components_h h
2008 WHERE h.component_code(+) = b.mapping_set_code
2009 AND h.component_type_code(+) = 'MAPPING_SET'
2010 AND b.updated_flag = 'Y'
2011 AND b.amb_context_code = p_amb_context_code
2012 GROUP BY b.mapping_set_code, b.version_num;
2013
2014 l_ms_codes t_array_varchar2;
2015 l_ms_version_from t_array_int;
2016 l_ms_version_to t_array_int;
2017 i INTEGER;
2018
2019 l_log_module VARCHAR2(240);
2020 BEGIN
2021 IF g_log_enabled THEN
2022 l_log_module := C_DEFAULT_MODULE||'.update_ms_version';
2023 END IF;
2024
2025 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2026 trace(p_msg => 'BEGIN of procedure update_ms_version',
2027 p_module => l_log_module,
2028 p_level => C_LEVEL_PROCEDURE);
2029 END IF;
2030
2031 i := 0;
2032
2033 IF (C_LEVEL_EVENT >= g_log_level) THEN
2034 trace(p_msg => 'BEGIN LOOP - retrieve mapping set',
2035 p_module => l_log_module,
2036 p_level => C_LEVEL_EVENT);
2037 END IF;
2038
2039 FOR l_ms in c_ms LOOP
2040 IF (C_LEVEL_EVENT >= g_log_level) THEN
2041 trace(p_msg => 'LOOP - mapping set = '||
2042 l_ms.mapping_set_code||','||
2043 l_ms.version_from||','||
2044 l_ms.version_to,
2045 p_module => l_log_module,
2046 p_level => C_LEVEL_EVENT);
2047 END IF;
2048
2049 i := i + 1;
2050 l_ms_codes(i) := l_ms.mapping_set_code;
2051 l_ms_version_from(i) := l_ms.version_from;
2052 l_ms_version_to(i) := l_ms.version_to;
2053 END LOOP;
2054
2055 IF (C_LEVEL_EVENT >= g_log_level) THEN
2056 trace(p_msg => 'END LOOP - retrieve mapping set',
2057 p_module => l_log_module,
2058 p_level => C_LEVEL_EVENT);
2059 END IF;
2060
2061 FORALL i IN 1 .. l_ms_codes.COUNT
2062 INSERT INTO xla_amb_components_h
2063 (component_type_code
2064 ,component_owner_code
2065 ,component_code
2066 ,application_id
2067 ,version_num
2068 ,base_version_num
2069 ,leapfrog_flag
2070 ,object_version_number
2071 ,creation_date
2072 ,created_by
2073 ,last_update_date
2074 ,last_updated_by
2075 ,last_update_login
2076 ,program_update_date
2077 ,program_application_id
2078 ,program_id
2079 ,request_id)
2080 VALUES
2081 ('MAPPING_SET'
2082 ,'X'
2083 ,l_ms_codes(i)
2084 ,-1
2085 ,l_ms_version_to(i)
2086 ,l_ms_version_from(i)
2087 ,DECODE(p_versioning_mode,'LEAPFROG','Y','N')
2088 ,1
2089 ,sysdate
2090 ,xla_environment_pkg.g_usr_id
2091 ,sysdate
2092 ,xla_environment_pkg.g_usr_id
2093 ,xla_environment_pkg.g_login_id
2094 ,sysdate
2095 ,xla_environment_pkg.g_prog_appl_id
2096 ,xla_environment_pkg.g_prog_id
2097 ,xla_environment_pkg.g_req_Id);
2098
2099 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2100 trace(p_msg => '# row inserted in xla_amb_components_h = '||SQL%ROWCOUNT,
2101 p_module => l_log_module,
2102 p_level => C_LEVEL_STATEMENT);
2103 END IF;
2104
2105 FORALL i IN 1 .. l_ms_codes.COUNT
2106 UPDATE xla_mapping_sets_b
2107 SET version_num = l_ms_version_to(i)
2108 ,updated_flag = 'N'
2109 ,creation_date = sysdate
2110 ,created_by = xla_environment_pkg.g_usr_id
2111 ,last_update_date = sysdate
2112 ,last_updated_by = xla_environment_pkg.g_usr_id
2113 ,last_update_login = xla_environment_pkg.g_login_id
2114 WHERE mapping_set_code = l_ms_codes(i)
2115 AND amb_context_code = p_amb_context_code;
2116
2117 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2118 trace(p_msg => '# row updated in xla_mapping_sets_b = '||SQL%ROWCOUNT,
2119 p_module => l_log_module,
2120 p_level => C_LEVEL_STATEMENT);
2121 END IF;
2122
2123 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2124 trace(p_msg => 'END of procedure update_ms_version',
2125 p_module => l_log_module,
2126 p_level => C_LEVEL_PROCEDURE);
2127 END IF;
2128
2129 EXCEPTION
2130 WHEN OTHERS THEN
2131 xla_aad_loader_util_pvt.stack_error
2132 (p_appli_s_name => 'XLA'
2133 ,p_msg_name => 'XLA_COMMON_ERROR'
2134 ,p_token_1 => 'LOCATION'
2135 ,p_value_1 => 'xla_aad_export_pvt.update_ms_version'
2136 ,p_token_2 => 'ERROR'
2137 ,p_value_2 => 'unhandled exception');
2138 RAISE;
2139
2140 END update_ms_version;
2141
2142 --=============================================================================
2143 --
2144 -- Name: record_log
2145 -- Description: This API records the log information to the log table
2146 --
2147 --=============================================================================
2148 PROCEDURE record_log
2149 (p_application_id IN INTEGER
2150 ,p_amb_context_code IN VARCHAR2)
2151 IS
2152 l_log_module VARCHAR2(240);
2153 BEGIN
2154 IF g_log_enabled THEN
2155 l_log_module := C_DEFAULT_MODULE||'.record_log';
2156 END IF;
2157
2158 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2159 trace(p_msg => 'BEGIN of procedure record_log',
2160 p_module => l_log_module,
2161 p_level => C_LEVEL_PROCEDURE);
2162 END IF;
2163
2164 INSERT INTO xla_aad_loader_logs
2165 (aad_loader_log_id
2166 ,amb_context_code
2167 ,application_id
2168 ,request_code
2169 ,log_type_code
2170 ,aad_application_id
2171 ,product_rule_code
2172 ,product_rule_type_code
2173 ,version_to
2174 ,object_version_number
2175 ,creation_date
2176 ,created_by
2177 ,last_update_date
2178 ,last_updated_by
2179 ,last_update_login
2180 ,program_update_date
2181 ,program_application_id
2182 ,program_id
2183 ,request_id)
2184 SELECT xla_aad_loader_logs_s.nextval
2185 ,p_amb_context_code
2186 ,p_application_id
2187 ,'EXPORT'
2188 ,'EXPORTED_AAD'
2189 ,application_id
2190 ,product_rule_code
2191 ,product_rule_type_code
2192 ,version_num
2193 ,1
2194 ,sysdate
2195 ,xla_environment_pkg.g_usr_id
2196 ,sysdate
2197 ,xla_environment_pkg.g_usr_id
2198 ,xla_environment_pkg.g_login_id
2199 ,sysdate
2200 ,xla_environment_pkg.g_prog_appl_id
2201 ,xla_environment_pkg.g_prog_id
2202 ,xla_environment_pkg.g_req_Id
2203 FROM xla_product_rules_b
2204 WHERE application_id = p_application_id
2205 AND amb_context_code = p_amb_context_code;
2206
2207 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2208 trace(p_msg => '# row inserted in xla_aad_loader_logs = '||SQL%ROWCOUNT,
2209 p_module => l_log_module,
2210 p_level => C_LEVEL_STATEMENT);
2211 END IF;
2212
2213 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2214 trace(p_msg => 'END of procedure record_log',
2215 p_module => l_log_module,
2216 p_level => C_LEVEL_PROCEDURE);
2217 END IF;
2218
2219 EXCEPTION
2220 WHEN OTHERS THEN
2221 xla_aad_loader_util_pvt.stack_error
2222 (p_appli_s_name => 'XLA'
2223 ,p_msg_name => 'XLA_COMMON_ERROR'
2224 ,p_token_1 => 'LOCATION'
2225 ,p_value_1 => 'xla_aad_export_pvt.record_log'
2226 ,p_token_2 => 'ERROR'
2227 ,p_value_2 => 'unhandled exception');
2228 RAISE;
2229
2230 END record_log;
2231
2232
2233 --=============================================================================
2234 --
2235 -- Name: pre_export
2236 -- Description: This API prepares the environment for export
2237 --
2238 --=============================================================================
2239 FUNCTION pre_export
2240 (p_application_id IN INTEGER
2241 ,p_amb_context_code IN VARCHAR2
2242 ,p_versioning_mode IN VARCHAR2
2243 ,p_user_version IN VARCHAR2
2244 ,p_version_comment IN VARCHAR2
2245 ,p_owner_type IN VARCHAR2)
2246 RETURN VARCHAR2
2247 IS
2248 l_recinfo xla_appli_amb_contexts%ROWTYPE;
2249 l_retcode VARCHAR2(30);
2250 l_log_module VARCHAR2(240);
2251 BEGIN
2252 IF g_log_enabled THEN
2253 l_log_module := C_DEFAULT_MODULE||'.pre_export';
2254 END IF;
2255
2256 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2257 trace(p_msg => 'BEGIN of function pre_export',
2258 p_module => l_log_module,
2259 p_level => C_LEVEL_PROCEDURE);
2260 END IF;
2261
2262 l_retcode := 'SUCCESS';
2263
2264 -- Lock the staging area of the AMB context
2265 l_retcode := lock_context
2266 (p_application_id => p_application_id
2267 ,p_amb_context_code => p_amb_context_code);
2268 IF (l_retcode = 'WARNING') THEN
2269 RAISE G_EXC_WARNING;
2270 ELSIF (l_retcode = 'ERROR') THEN
2271 RAISE FND_API.G_EXC_ERROR;
2272 END IF;
2273
2274 l_retcode := validation
2275 (p_application_id => p_application_id
2276 ,p_amb_context_code => p_amb_context_code
2277 ,p_owner_type => p_owner_type
2278 ,p_versioning_mode => p_versioning_mode);
2279
2280 IF (l_retcode = 'WARNING') THEN
2281 RAISE G_EXC_WARNING;
2282 ELSIF (l_retcode = 'ERROR') THEN
2283 RAISE FND_API.G_EXC_ERROR;
2284 END IF;
2285
2286 update_aad_version
2287 (p_application_id => p_application_id
2288 ,p_amb_context_code => p_amb_context_code
2289 ,p_owner_type => p_owner_type
2290 ,p_versioning_mode => p_versioning_mode
2291 ,p_user_version => p_user_version
2292 ,p_version_comment => p_version_comment);
2293
2294 update_ac_version
2295 (p_application_id => p_application_id
2296 ,p_amb_context_code => p_amb_context_code
2297 ,p_versioning_mode => p_versioning_mode);
2298
2299 update_adr_version
2300 (p_application_id => p_application_id
2301 ,p_amb_context_code => p_amb_context_code
2302 ,p_versioning_mode => p_versioning_mode);
2303
2304 IF (p_owner_type = 'C') THEN
2305 update_ms_version
2306 (p_application_id => p_application_id
2307 ,p_amb_context_code => p_amb_context_code
2308 ,p_versioning_mode => p_versioning_mode);
2309 END IF;
2310
2311 record_log
2312 (p_application_id => p_application_id
2313 ,p_amb_context_code => p_amb_context_code);
2314
2315 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2316 trace(p_msg => 'END of function pre_export - Return value = '||l_retcode,
2317 p_module => l_log_module,
2318 p_level => C_LEVEL_PROCEDURE);
2319 END IF;
2320
2321 RETURN l_retcode;
2322 EXCEPTION
2323 WHEN G_EXC_WARNING THEN
2324 RETURN 'WARNING';
2325
2326 WHEN FND_API.G_EXC_ERROR THEN
2327 RETURN 'ERROR';
2328
2329 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2330 ROLLBACK;
2331 RETURN 'ERROR';
2332
2333 WHEN OTHERS THEN
2334 ROLLBACK;
2335
2336 xla_aad_loader_util_pvt.stack_error
2337 (p_appli_s_name => 'XLA'
2338 ,p_msg_name => 'XLA_COMMON_ERROR'
2339 ,p_token_1 => 'LOCATION'
2340 ,p_value_1 => 'xla_aad_export_pvt.pre_export'
2341 ,p_token_2 => 'ERROR'
2342 ,p_value_2 => 'unhandled exception');
2343 RAISE;
2344 END pre_export;
2345
2346
2347 --=============================================================================
2348 --
2349 --
2350 --
2351 --
2352 --
2353 -- *********** public procedures and functions **********
2354 --
2355 --
2356 --
2357 --
2358 --
2359 --=============================================================================
2360
2361
2362 --=============================================================================
2363 --
2364 -- Name: export
2365 -- Description: This API exports the AADs and the components from the AMB
2366 -- context to the data file
2367 --
2368 --=============================================================================
2369 PROCEDURE export
2370 (p_api_version IN NUMBER
2371 ,x_return_status IN OUT NOCOPY VARCHAR2
2372 ,p_application_id IN VARCHAR2
2373 ,p_amb_context_code IN VARCHAR2
2374 ,p_destination_pathname IN VARCHAR2
2375 ,p_versioning_mode IN VARCHAR2
2376 ,p_user_version IN VARCHAR2
2377 ,p_version_comment IN VARCHAR2
2378 ,x_export_status IN OUT NOCOPY VARCHAR2)
2379 IS
2380 CURSOR c_app_short_name IS
2381 SELECT application_short_name
2382 FROM fnd_application
2383 WHERE application_id = p_application_id;
2384
2385 l_api_name CONSTANT VARCHAR2(30) := 'export';
2386 l_api_version CONSTANT NUMBER := 1.0;
2387 l_destination_file VARCHAR2(300);
2388 l_app_short_name VARCHAR2(30);
2389 l_owner_type VARCHAR2(1);
2390 l_log_module VARCHAR2(240);
2391 BEGIN
2392 IF g_log_enabled THEN
2393 l_log_module := C_DEFAULT_MODULE||'.export';
2394 END IF;
2395
2396 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2397 trace(p_msg => 'BEGIN of function export',
2398 p_module => l_log_module,
2399 p_level => C_LEVEL_PROCEDURE);
2400 END IF;
2401
2402 IF (NOT xla_aad_loader_util_pvt.compatible_api_call
2403 (p_current_version_number => l_api_version
2404 ,p_caller_version_number => p_api_version
2405 ,p_api_name => l_api_name
2406 ,p_pkg_name => C_DEFAULT_MODULE))
2407 THEN
2408 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2409 END IF;
2410
2411 -- Initialize global variables
2412 x_return_status := FND_API.G_RET_STS_SUCCESS;
2413
2414 -- API Logic
2415 IF (NVL(fnd_profile.value('XLA_SETUP_USER_MODE'),'C') = 'C') THEN
2416 l_owner_type := 'C';
2417 ELSE
2418 l_owner_type := 'S';
2419 END IF;
2420
2421 x_export_status := pre_export
2422 (p_application_id => p_application_id
2423 ,p_amb_context_code => p_amb_context_code
2424 ,p_versioning_mode => p_versioning_mode
2425 ,p_user_version => p_user_version
2426 ,p_version_comment => p_version_comment
2427 ,p_owner_type => l_owner_type);
2428
2429 IF (x_export_status = 'WARNING') THEN
2430 RAISE G_EXC_WARNING;
2431 END IF;
2432
2433 xla_aad_download_pvt.download
2434 (p_api_version => 1.0
2435 ,x_return_status => x_return_status
2436 ,p_application_id => p_application_id
2437 ,p_amb_context_code => p_amb_context_code
2438 ,p_destination_file => p_destination_pathname
2439 ,x_download_status => x_export_status);
2440
2441 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2442 trace(p_msg => 'END of function export - Return value = '||x_export_status,
2443 p_module => l_log_module,
2444 p_level => C_LEVEL_PROCEDURE);
2445 END IF;
2446 EXCEPTION
2447 WHEN G_EXC_WARNING THEN
2448 x_return_status := FND_API.G_RET_STS_ERROR ;
2449 x_export_status := 'WARNING';
2450
2451 WHEN FND_API.G_EXC_ERROR THEN
2452 x_return_status := FND_API.G_RET_STS_ERROR ;
2453 x_export_status := 'ERROR';
2454
2455 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2456 ROLLBACK;
2457 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2458 x_export_status := 'ERROR';
2459
2460 WHEN OTHERS THEN
2461 ROLLBACK;
2462 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2463 x_export_status := 'ERROR';
2464
2465 xla_aad_loader_util_pvt.stack_error
2466 (p_appli_s_name => 'XLA'
2467 ,p_msg_name => 'XLA_COMMON_ERROR'
2468 ,p_token_1 => 'LOCATION'
2469 ,p_value_1 => 'xla_aad_export_pvt.export'
2470 ,p_token_2 => 'ERROR'
2471 ,p_value_2 => 'unhandled exception');
2472 RAISE;
2473 END export;
2474
2475 --=============================================================================
2476 --
2477 -- Following code is executed when the package body is referenced for the first
2478 -- time
2479 --
2480 --=============================================================================
2481 BEGIN
2482 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2483 g_log_enabled := fnd_log.test
2484 (log_level => g_log_level
2485 ,module => C_DEFAULT_MODULE);
2486
2487 IF NOT g_log_enabled THEN
2488 g_log_level := C_LEVEL_LOG_DISABLED;
2489 END IF;
2490
2491 END xla_aad_export_pvt;