[Home] [Help]
PACKAGE BODY: APPS.XLA_AAD_UPLOAD_PVT
Source
1 PACKAGE BODY xla_aad_upload_pvt AS
2 /* $Header: xlaalupl.pkb 120.28 2008/07/11 06:59:07 krsankar ship $ */
3
4 --=============================================================================
5 -- **************** declaraions ********************
6 --=============================================================================
7 -------------------------------------------------------------------------------
8 -- declaring global types
9 -------------------------------------------------------------------------------
10 -------------------------------------------------------------------------------
11 -- declaring global constants
12 -------------------------------------------------------------------------------
13 C_FILE_NAME CONSTANT VARCHAR2(30):='xlaalupl.pkb';
14 C_CHAR CONSTANT VARCHAR2(1) :='
15 ';
16
17 --=============================================================================
18 -- *********** Local Trace Routine **********
19 --=============================================================================
20 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
21 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
22 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
23 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
24 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
25 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
26
27 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
28 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_aad_upload_pvt';
29
30 g_log_level NUMBER;
31 g_log_enabled BOOLEAN;
32
33 PROCEDURE trace
34 (p_msg IN VARCHAR2
35 ,p_module IN VARCHAR2
36 ,p_level IN NUMBER) IS
37 l_time varchar2(300);
38 BEGIN
39 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
40 fnd_log.message(p_level, p_module);
41 ELSIF p_level >= g_log_level THEN
42 fnd_log.string(p_level, p_module, p_msg);
43 END IF;
44 EXCEPTION
45 WHEN xla_exceptions_pkg.application_exception THEN
46 RAISE;
47
48 WHEN OTHERS THEN
49 xla_exceptions_pkg.raise_message
50 (p_location => 'xla_aad_upload_pvt.trace');
51 END trace;
52
53
54 --=============================================================================
55 -- *********** private procedures and functions **********
56 --=============================================================================
57
58 --=============================================================================
59 --
60 -- Name: submit_request
61 -- Description: This API submits the Upload Application Accounting Definitions
62 -- request
63 --
64 --=============================================================================
65 FUNCTION submit_request
66 (p_application_id IN INTEGER
67 ,p_source_pathname IN VARCHAR2
68 ,p_staging_context_code IN VARCHAR2)
69 RETURN INTEGER
70 IS
71 PRAGMA AUTONOMOUS_TRANSACTION;
72
73 CURSOR c_app_short_name IS
74 SELECT application_short_name
75 FROM fnd_application
76 WHERE application_id = p_application_id;
77
78 l_app_short_name VARCHAR2(30);
79 l_req_id INTEGER;
80 l_log_module VARCHAR2(240);
81 BEGIN
82 IF g_log_enabled THEN
83 l_log_module := C_DEFAULT_MODULE||'.submit_request';
84 END IF;
85
86 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
87 trace(p_msg => 'BEGIN of function submit_request',
88 p_module => l_log_module,
89 p_level => C_LEVEL_PROCEDURE);
90 END IF;
91
92 DELETE FROM xla_aad_loader_defns_t
93 WHERE staging_amb_context_code = p_staging_context_code;
94
95 OPEN c_app_short_name;
96 FETCH c_app_short_name INTO l_app_short_name;
97 CLOSE c_app_short_name;
98
99 l_req_id := fnd_request.submit_request
100 (application => 'XLA'
101 ,program => 'XLAAADUL'
102 ,description => NULL
103 ,start_time => NULL
104 ,sub_request => FALSE
105 ,argument1 => 'UPLOAD_PARTIAL'
106 ,argument2 => '@xla:/patch/115/import/xlaaadrule.lct'
107 ,argument3 => p_source_pathname
108 ,argument4 => 'XLA_AAD'
109 ,argument5 => 'STAGING_AMB_CONTEXT_CODE='||p_staging_context_code);
110
111 COMMIT;
112
113 IF (C_LEVEL_EVENT>= g_log_level) THEN
114 trace(p_msg => 'Submitted XLAAADUL request = '||l_req_id
115 ,p_level => C_LEVEL_EVENT
116 ,p_module => l_log_module);
117 END IF;
118
119 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
120 trace(p_msg => 'END of function submit_request',
121 p_module => l_log_module,
122 p_level => C_LEVEL_PROCEDURE);
123 END IF;
124
125 RETURN l_req_id;
126 EXCEPTION
127 WHEN OTHERS THEN
128 xla_aad_loader_util_pvt.stack_error
129 (p_appli_s_name => 'XLA'
130 ,p_msg_name => 'XLA_COMMON_ERROR'
131 ,p_token_1 => 'LOCATION'
132 ,p_value_1 => 'xla_aad_upload_pvt.submit_request'
133 ,p_token_2 => 'ERROR'
134 ,p_value_2 => 'unhandled exception');
135 RAISE;
136
137 END submit_request;
138
139 --=============================================================================
140 --
141 -- Name: upload_data
142 -- Description: This API submits a concurrent request to upload data from the
143 -- data file to the AAD Loader interface table
144 --
145 --=============================================================================
146 FUNCTION upload_data
147 (p_application_id IN INTEGER
148 ,p_source_pathname IN VARCHAR2
149 ,p_staging_context_code IN VARCHAR2)
150 RETURN VARCHAR2
151 IS
152 l_retcode VARCHAR2(30);
153 l_req_id NUMBER;
154 l_log_module VARCHAR2(240);
155 BEGIN
156 IF g_log_enabled THEN
157 l_log_module := C_DEFAULT_MODULE||'.upload_data';
158 END IF;
159
160 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
161 trace(p_msg => 'BEGIN of function upload_data',
162 p_module => l_log_module,
163 p_level => C_LEVEL_PROCEDURE);
164 END IF;
165
166 l_req_id := submit_request
167 (p_application_id => p_application_id
168 ,p_source_pathname => p_source_pathname
169 ,p_staging_context_code => p_staging_context_code);
170
171 IF (l_req_id = 0) THEN
172 RAISE FND_API.G_EXC_ERROR;
173 END IF;
174
175 l_retcode := xla_aad_loader_util_pvt.wait_for_request(p_req_id => l_req_id);
176 IF (l_retcode = 'ERROR') THEN
177 RAISE FND_API.G_EXC_ERROR;
178 END IF;
179
180 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
181 trace(p_msg => 'END of function upload_data : Return Code = '||l_retcode,
182 p_module => l_log_module,
183 p_level => C_LEVEL_PROCEDURE);
184 END IF;
185
186 RETURN 'SUCCESS';
187 EXCEPTION
188 WHEN FND_API.G_EXC_ERROR THEN
189 xla_aad_loader_util_pvt.stack_error
190 (p_appli_s_name => 'XLA'
191 ,p_msg_name => 'XLA_AAD_UPL_FNDLOAD_FAIL'
192 ,p_token_1 => 'CONC_REQUEST_ID'
193 ,p_value_1 => l_req_id
194 ,p_token_2 => 'DATA_FILE'
195 ,p_value_2 => p_source_pathname);
196
197 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
198 trace(p_msg => 'END of function upload_data : Return Code = '||l_retcode,
199 p_module => l_log_module,
200 p_level => C_LEVEL_PROCEDURE);
201 END IF;
202
203 RETURN 'ERROR';
204 WHEN OTHERS THEN
205 xla_aad_loader_util_pvt.stack_error
206 (p_appli_s_name => 'XLA'
207 ,p_msg_name => 'XLA_COMMON_ERROR'
208 ,p_token_1 => 'LOCATION'
209 ,p_value_1 => 'xla_aad_upload_pvt.upload_data'
210 ,p_token_2 => 'ERROR'
211 ,p_value_2 => 'unhandled exception');
212 RAISE;
213
214 END upload_data;
215
216 --=============================================================================
217 --
218 -- Name: validation
219 -- Description: This API validate if the uploaded data from the ldt is valid
220 --
221 --=============================================================================
222 FUNCTION validation
223 (p_application_id IN INTEGER
224 ,p_staging_context_code IN VARCHAR2)
225 RETURN VARCHAR2
226 IS
227 CURSOR c_file_size IS
228 SELECT count(*)
229 FROM xla_aad_loader_defns_t xal
230 WHERE xal.staging_amb_context_code = p_staging_context_code
231 AND xal.table_name = 'XLA_PRODUCT_RULES'
232 AND ROWNUM = 1;
233
234 CURSOR c_invalid_app IS
235 SELECT fa.application_name file_app_name
236 , fa2.application_name resp_app_name
237 FROM xla_aad_loader_defns_t xal
238 , fnd_application_vl fa
239 , fnd_application_vl fa2
240 WHERE xal.staging_amb_context_code = p_staging_context_code
241 AND xal.table_name = 'XLA_AAD'
242 AND xal.application_short_name = fa.application_short_name
243 AND fa.application_id <> p_application_id
244 AND fa2.application_id = p_application_id;
245
246 -- krsankar - Bug 6975482 - Introducing 2 new cursors for deleting duplicate data from XLA_ANALYTICAL_HDRS, XLA_ANALYTICAL_SOURCES
247 -- krsankar - Bug 7243326 - Modified to analytical_criterion_code instead of criterion_type_code
248
249 CURSOR c_del_dup_name IS
250 SELECT table_name
251 , DECODE(table_name
252 ,'XLA_ANALYTICAL_HDRS' ,analytical_criterion_code)
253 , analytical_criterion_code
254 , count(*)
255 FROM xla_aad_loader_defns_t
256 WHERE staging_amb_context_code = p_staging_context_code
257 AND table_name IN ('XLA_ANALYTICAL_HDRS')
258 GROUP BY
259 table_name
260 , DECODE(table_name
261 ,'XLA_ANALYTICAL_HDRS' ,analytical_criterion_code)
262 , analytical_criterion_code
263 HAVING count(*) > 1;
264
265
266 CURSOR c_del_dup_name_anal_sources IS
267 SELECT table_name
268 , DECODE(table_name
269 ,'XLA_ANALYTICAL_SOURCES' ,analytical_criterion_type_code||C_CHAR||
270 analytical_criterion_code||C_CHAR||
271 event_class_code)
272 , analytical_criterion_type_code
273 , analytical_criterion_code
274 , event_class_code
275 , count(*)
276 FROM xla_aad_loader_defns_t
277 WHERE staging_amb_context_code = p_staging_context_code
278 AND table_name IN ('XLA_ANALYTICAL_SOURCES')
279 GROUP BY
280 table_name
281 , DECODE(table_name
282 ,'XLA_ANALYTICAL_SOURCES' ,analytical_criterion_type_code||C_CHAR||
283 analytical_criterion_code||C_CHAR||
284 event_class_code)
285 , analytical_criterion_type_code
286 , analytical_criterion_code
287 , event_class_code
288 HAVING count(*) > 1;
289
290 -- krsankar - End of new cursor addition
291
292 CURSOR c_dup_name IS
293 SELECT table_name
294 , DECODE(table_name
295 ,'XLA_PRODUCT_RULES' ,product_rule_type_code
296 ,'XLA_LINE_DEFINITIONS' ,event_class_code||C_CHAR||
297 event_type_code||C_CHAR||
298 line_definition_owner_code
299 ,'XLA_ACCT_LINE_TYPES' ,event_class_code||C_CHAR||
300 accounting_line_type_code
301 ,'XLA_DESCRIPTIONS' ,description_type_code
302 ,'XLA_SEG_RULES' ,segment_rule_type_code
303 ,'XLA_ANALYTICAL_HDRS' ,analytical_criterion_type_code
304 ,'XLA_ANALYTICAL_DTLS' ,analytical_criterion_type_code||C_CHAR||
305 analytical_criterion_code
306 ,'XLA_MAPPING_SETS' ,NULL)
307 , name
308 , count(*)
309 FROM xla_aad_loader_defns_t
310 WHERE staging_amb_context_code = p_staging_context_code
311 AND name IS NOT NULL
312 AND table_name IN ('XLA_PRODUCT_RULES'
313 ,'XLA_LINE_DEFINITIONS'
314 ,'XLA_ACCT_LINE_TYPES'
315 ,'XLA_DESCRIPTIONS'
316 ,'XLA_SEG_RULES'
317 ,'XLA_ANALYTICAL_HDRS'
318 ,'XLA_ANALYTICAL_DTLS'
319 ,'XLA_MAPPING_SETS')
320 GROUP BY
321 table_name
322 , DECODE(table_name
323 ,'XLA_PRODUCT_RULES' ,product_rule_type_code
324 ,'XLA_LINE_DEFINITIONS' ,event_class_code||C_CHAR||
325 event_type_code||C_CHAR||
326 line_definition_owner_code
327 ,'XLA_ACCT_LINE_TYPES' ,event_class_code||C_CHAR||
328 accounting_line_type_code
329 ,'XLA_DESCRIPTIONS' ,description_type_code
330 ,'XLA_SEG_RULES' ,segment_rule_type_code
331 ,'XLA_ANALYTICAL_HDRS' ,analytical_criterion_type_code
332 ,'XLA_ANALYTICAL_DTLS' ,analytical_criterion_type_code||C_CHAR||
333 analytical_criterion_code
334 ,'XLA_MAPPING_SETS' ,NULL)
335 , name
336 HAVING count(*) > 1;
337
338 CURSOR c_dup_code IS
339 SELECT table_name
340 , DECODE(table_name
341 ,'XLA_PRODUCT_RULES' ,product_rule_type_code
342 ,'XLA_LINE_DEFINITIONS' ,event_class_code||C_CHAR||
343 event_type_code||C_CHAR||
344 line_definition_owner_code
345 ,'XLA_ACCT_LINE_TYPES' ,event_class_code||C_CHAR||
346 accounting_line_type_code
347 ,'XLA_DESCRIPTIONS' ,description_type_code
348 ,'XLA_SEG_RULES' ,segment_rule_type_code
349 ,'XLA_ANALYTICAL_HDRS' ,analytical_criterion_type_code
353 , DECODE(table_name
350 ,'XLA_ANALYTICAL_DTLS' ,analytical_criterion_type_code||C_CHAR||
351 analytical_criterion_code
352 ,'XLA_MAPPING_SETS' ,NULL)
354 ,'XLA_PRODUCT_RULES' ,product_rule_code
355 ,'XLA_LINE_DEFINITIONS' ,line_definition_code
356 ,'XLA_ACCT_LINE_TYPES' ,accounting_line_code
357 ,'XLA_DESCRIPTIONS' ,description_code
358 ,'XLA_SEG_RULES' ,segment_rule_code
359 ,'XLA_ANALYTICAL_HDRS' ,analytical_criterion_code
360 ,'XLA_ANALYTICAL_DTLS' ,analytical_detail_code
361 ,'XLA_MAPPING_SETS' ,mapping_set_code) code
362 , count(*)
363 FROM xla_aad_loader_defns_t
364 WHERE staging_amb_context_code = p_staging_context_code
365 AND table_name IN ('XLA_PRODUCT_RULES'
366 ,'XLA_LINE_DEFINITIONS'
367 ,'XLA_ACCT_LINE_TYPES'
368 ,'XLA_DESCRIPTIONS'
369 ,'XLA_SEG_RULES'
370 ,'XLA_ANALYTICAL_HDRS'
371 ,'XLA_ANALYTICAL_DTLS'
372 ,'XLA_MAPPING_SETS')
373 GROUP BY
374 table_name
375 , DECODE(table_name
376 ,'XLA_PRODUCT_RULES' ,product_rule_type_code
377 ,'XLA_LINE_DEFINITIONS' ,event_class_code||C_CHAR||
378 event_type_code||C_CHAR||
379 line_definition_owner_code
380 ,'XLA_ACCT_LINE_TYPES' ,event_class_code||C_CHAR||
381 accounting_line_type_code
382 ,'XLA_DESCRIPTIONS' ,description_type_code
383 ,'XLA_SEG_RULES' ,segment_rule_type_code
384 ,'XLA_ANALYTICAL_HDRS' ,analytical_criterion_type_code
385 ,'XLA_ANALYTICAL_DTLS' ,analytical_criterion_type_code||C_CHAR||
386 analytical_criterion_code
387 ,'XLA_MAPPING_SETS' ,NULL)
388 , DECODE(table_name
389 ,'XLA_PRODUCT_RULES' ,product_rule_code
390 ,'XLA_LINE_DEFINITIONS' ,line_definition_code
391 ,'XLA_ACCT_LINE_TYPES' ,accounting_line_code
392 ,'XLA_DESCRIPTIONS' ,description_code
393 ,'XLA_SEG_RULES' ,segment_rule_code
394 ,'XLA_ANALYTICAL_HDRS' ,analytical_criterion_code
395 ,'XLA_ANALYTICAL_DTLS' ,analytical_detail_code
396 ,'XLA_MAPPING_SETS' ,mapping_set_code)
397 HAVING count(*) > 1;
398
399 CURSOR c_invalid_coa IS
400 SELECT xal.value_ccid_id_flex_struct_code id_flex_struct_code
401 FROM xla_aad_loader_defns_t xal
402 LEFT OUTER JOIN fnd_id_flex_structures fif
403 ON fif.application_id = 101
404 AND fif.id_flex_code = 'GL#'
405 AND fif.id_flex_structure_code = xal.value_ccid_id_flex_struct_code
406 WHERE xal.staging_amb_context_code = p_staging_context_code
407 AND xal.value_ccid_id_flex_struct_code IS NOT NULL
408 AND fif.id_flex_structure_code IS NULL
409 UNION
410 SELECT xal.trans_coa_id_flex_struct_code
411 FROM xla_aad_loader_defns_t xal
412 LEFT OUTER JOIN fnd_id_flex_structures fif
413 ON fif.application_id = 101
414 AND fif.id_flex_code = 'GL#'
415 AND fif.id_flex_structure_code = xal.trans_coa_id_flex_struct_code
416 WHERE xal.staging_amb_context_code = p_staging_context_code
417 AND xal.trans_coa_id_flex_struct_code IS NOT NULL
418 AND fif.id_flex_structure_code IS NULL
419 UNION
420 SELECT xal.acct_coa_id_flex_struct_code
421 FROM xla_aad_loader_defns_t xal
422 LEFT OUTER JOIN fnd_id_flex_structures fif
423 ON fif.application_id = 101
424 AND fif.id_flex_code = 'GL#'
425 AND fif.id_flex_structure_code = xal.acct_coa_id_flex_struct_code
426 WHERE xal.staging_amb_context_code = p_staging_context_code
427 AND xal.acct_coa_id_flex_struct_code IS NOT NULL
428 AND fif.id_flex_structure_code IS NULL;
429
430 CURSOR c_invalid_value_set IS
431 SELECT xal.flex_value_set_name
432 FROM xla_aad_loader_defns_t xal
433 LEFT OUTER JOIN fnd_flex_value_sets val
434 ON val.flex_value_set_name = xal.flex_value_set_name
435 WHERE xal.staging_amb_context_code = p_staging_context_code
436 AND xal.flex_value_set_name IS NOT NULL
437 AND val.flex_value_set_id IS NULL;
438
439 CURSOR c_invalid_app_short_name IS
440 SELECT xal.source_app_short_name app_short_name
441 FROM xla_aad_loader_defns_t xal
442 LEFT OUTER JOIN fnd_application fap
443 ON fap.application_short_name = xal.source_app_short_name
444 WHERE xal.staging_amb_context_code = p_staging_context_code
445 AND xal.source_app_short_name IS NOT NULL
446 AND fap.application_id IS NULL
447 UNION
448 SELECT xal.value_source_app_short_name
449 FROM xla_aad_loader_defns_t xal
453 AND xal.value_source_app_short_name IS NOT NULL
450 LEFT OUTER JOIN fnd_application fap
451 ON fap.application_short_name = xal.value_source_app_short_name
452 WHERE xal.staging_amb_context_code = p_staging_context_code
454 AND fap.application_id IS NULL
455 UNION
456 SELECT xal.view_app_short_name
457 FROM xla_aad_loader_defns_t xal
458 LEFT OUTER JOIN fnd_application fap
459 ON fap.application_short_name = xal.view_app_short_name
460 WHERE xal.staging_amb_context_code = p_staging_context_code
461 AND xal.view_app_short_name IS NOT NULL
462 AND fap.application_id IS NULL
463 UNION
464 SELECT xal.application_short_name
465 FROM xla_aad_loader_defns_t xal
466 LEFT OUTER JOIN fnd_application fap
467 ON fap.application_short_name = xal.application_short_name
468 WHERE xal.staging_amb_context_code = p_staging_context_code
469 AND xal.application_short_name IS NOT NULL
470 AND fap.application_id IS NULL
471 UNION
472 SELECT xal.input_source_app_short_name
473 FROM xla_aad_loader_defns_t xal
474 LEFT OUTER JOIN fnd_application fap
475 ON fap.application_short_name = xal.input_source_app_short_name
476 WHERE xal.staging_amb_context_code = p_staging_context_code
477 AND xal.input_source_app_short_name IS NOT NULL
478 AND fap.application_id IS NULL
479 UNION
480 SELECT xal.value_segment_rule_appl_sn
481 FROM xla_aad_loader_defns_t xal
482 LEFT OUTER JOIN fnd_application fap
483 ON fap.application_short_name = xal.value_segment_rule_appl_sn
484 WHERE xal.staging_amb_context_code = p_staging_context_code
485 AND xal.value_segment_rule_appl_sn IS NOT NULL
486 AND fap.application_id IS NULL;
487
488 l_size INTEGER;
489 l_retcode VARCHAR2(30);
490 l_log_module VARCHAR2(240);
491 BEGIN
492 IF g_log_enabled THEN
493 l_log_module := C_DEFAULT_MODULE||'.validation';
494 END IF;
495
496 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
497 trace(p_msg => 'BEGIN of function validation',
498 p_module => l_log_module,
499 p_level => C_LEVEL_PROCEDURE);
500 END IF;
501
502 l_retcode := 'SUCCESS';
503
504 OPEN c_file_size;
505 FETCH c_file_size INTO l_size;
506 CLOSE c_file_size;
507
508 IF (C_LEVEL_ERROR >= g_log_level) THEN
509 trace(p_msg => 'LOOP: c_file_size',
510 p_module => l_log_module,
511 p_level => C_LEVEL_ERROR);
512 END IF;
513
514 IF (l_size <= 0) THEN
515 l_retcode := 'WARNING';
516 xla_aad_loader_util_pvt.stack_error
517 (p_appli_s_name => 'XLA'
518 ,p_msg_name => 'XLA_AAD_UPL_EMPTY_LDT');
519 END IF;
520
521 IF (C_LEVEL_EVENT >= g_log_level) THEN
522 trace(p_msg => 'BEGIN LOOP: c_invalid_app',
523 p_module => l_log_module,
524 p_level => C_LEVEL_EVENT);
525 END IF;
526
527 FOR l IN c_invalid_app LOOP
528 IF (C_LEVEL_ERROR >= g_log_level) THEN
529 trace(p_msg => 'LOOP: c_invalid_app',
530 p_module => l_log_module,
531 p_level => C_LEVEL_ERROR);
532 END IF;
533
534 l_retcode := 'WARNING';
535 xla_aad_loader_util_pvt.stack_error
536 (p_appli_s_name => 'XLA'
540 ,p_token_2 => 'RESP_APP_NAME'
537 ,p_msg_name => 'XLA_AAD_UPL_INV_APP'
538 ,p_token_1 => 'FILE_APP_NAME'
539 ,p_value_1 => l.file_app_name
541 ,p_value_2 => l.resp_app_name);
542 END LOOP;
543
544 IF (C_LEVEL_EVENT >= g_log_level) THEN
545 trace(p_msg => 'END LOOP: c_invalid_app',
546 p_module => l_log_module,
547 p_level => C_LEVEL_EVENT);
548 END IF;
549
550 IF (C_LEVEL_EVENT >= g_log_level) THEN
551 trace(p_msg => 'BEGIN LOOP: c_dup_name',
552 p_module => l_log_module,
553 p_level => C_LEVEL_EVENT);
554 END IF;
555
556
557 -- krsankar - Opening 2 new cursors added as part of P1 Bug 6975482.
558
559 FOR i IN c_del_dup_name LOOP
560
561 DELETE FROM xla_aad_loader_defns_t
562 WHERE staging_amb_context_code = p_staging_context_code
563 AND upper(table_name) = ('XLA_ANALYTICAL_HDRS')
564 AND analytical_criterion_code = i.analytical_criterion_code
565 AND rowid NOT IN (select max(rowid) from xla_aad_loader_defns_t
566 where staging_amb_context_code = p_staging_context_code
567 and upper(table_name) = ('XLA_ANALYTICAL_HDRS')
568 and analytical_criterion_code=i.analytical_criterion_code
569 group by analytical_criterion_code);
570
571 END LOOP;
572
573
574 FOR i IN c_del_dup_name_anal_sources LOOP
575
576 DELETE FROM xla_aad_loader_defns_t
577 WHERE staging_amb_context_code = p_staging_context_code
578 AND upper(table_name) = ('XLA_ANALYTICAL_SOURCES')
579 AND analytical_criterion_type_code = i.analytical_criterion_type_code
580 AND analytical_criterion_code = i.analytical_criterion_code
581 AND event_class_code = i.event_class_code
582 AND rowid NOT IN (select max(rowid) from xla_aad_loader_defns_t
583 where staging_amb_context_code = p_staging_context_code
584 and upper(table_name) = ('XLA_ANALYTICAL_SOURCES')
585 and analytical_criterion_type_code=i.analytical_criterion_type_code
586 and analytical_criterion_code = i.analytical_criterion_code
587 and event_class_code = i.event_class_code
588 group by analytical_criterion_type_code,analytical_criterion_code,event_class_code);
589
590 END LOOP;
591
592 -- krsankar - End of opening 2 new cursors added as part of P1 Bug 6975482.
593
594
595 FOR l IN c_dup_name LOOP
599 p_module => l_log_module,
596
597 IF (C_LEVEL_ERROR >= g_log_level) THEN
598 trace(p_msg => 'LOOP: c_dup_name',
600 p_level => C_LEVEL_ERROR);
601 END IF;
602
603 l_retcode := 'WARNING';
604 xla_aad_loader_util_pvt.stack_error
605 (p_appli_s_name => 'XLA'
606 ,p_msg_name => 'XLA_AAD_UPL_DUP_NAME'
607 ,p_token_1 => 'COMPONENT_NAME'
608 ,p_value_1 => l.name
609 ,p_token_2 => 'COMPONENT_TYPE'
610 ,p_value_2 => l.table_name);
611
612 END LOOP;
613
614 IF (C_LEVEL_EVENT >= g_log_level) THEN
615 trace(p_msg => 'END LOOP: c_dup_name',
616 p_module => l_log_module,
617 p_level => C_LEVEL_EVENT);
618 END IF;
619
620 IF (C_LEVEL_EVENT >= g_log_level) THEN
621 trace(p_msg => 'BEGIN LOOP: c_dup_code',
622 p_module => l_log_module,
623 p_level => C_LEVEL_EVENT);
624 END IF;
625
626 FOR l IN c_dup_code LOOP
627
628 IF (C_LEVEL_ERROR >= g_log_level) THEN
629 trace(p_msg => 'LOOP: c_dup_code',
630 p_module => l_log_module,
631 p_level => C_LEVEL_ERROR);
632 END IF;
633
634 l_retcode := 'WARNING';
635 xla_aad_loader_util_pvt.stack_error
636 (p_appli_s_name => 'XLA'
637 ,p_msg_name => 'XLA_AAD_UPL_DUP_CODE'
638 ,p_token_1 => 'CODE'
639 ,p_value_1 => l.code
640 ,p_token_2 => 'COMPONENT_TYPE'
641 ,p_value_2 => l.table_name);
642
643 END LOOP;
644
645 IF (C_LEVEL_EVENT >= g_log_level) THEN
646 trace(p_msg => 'END LOOP: c_dup_code',
647 p_module => l_log_module,
648 p_level => C_LEVEL_EVENT);
649 END IF;
650
651 IF (C_LEVEL_EVENT >= g_log_level) THEN
652 trace(p_msg => 'BEGIN LOOP: c_invalid_coa',
653 p_module => l_log_module,
654 p_level => C_LEVEL_EVENT);
655 END IF;
656
657 FOR l IN c_invalid_coa LOOP
658
659 IF (C_LEVEL_ERROR >= g_log_level) THEN
660 trace(p_msg => 'LOOP: c_invalid_coa',
661 p_module => l_log_module,
662 p_level => C_LEVEL_ERROR);
663 END IF;
664
665 l_retcode := 'WARNING';
666 xla_aad_loader_util_pvt.stack_error
667 (p_appli_s_name => 'XLA'
668 ,p_msg_name => 'XLA_AAD_UPL_INVALID_COA'
669 ,p_token_1 => 'STRUCT_CODE'
670 ,p_value_1 => l.id_flex_struct_code);
671
672 END LOOP;
673
674 IF (C_LEVEL_EVENT >= g_log_level) THEN
675 trace(p_msg => 'END LOOP: c_invalid_coa',
676 p_module => l_log_module,
677 p_level => C_LEVEL_EVENT);
678 END IF;
679
680 IF (C_LEVEL_EVENT >= g_log_level) THEN
681 trace(p_msg => 'BEGIN LOOP: c_invalid_value_set',
682 p_module => l_log_module,
683 p_level => C_LEVEL_EVENT);
684 END IF;
685
686 FOR l IN c_invalid_value_set LOOP
687
688 IF (C_LEVEL_ERROR >= g_log_level) THEN
689 trace(p_msg => 'LOOP: c_invalid_value_set',
690 p_module => l_log_module,
691 p_level => C_LEVEL_ERROR);
692 END IF;
693
694 l_retcode := 'WARNING';
695 xla_aad_loader_util_pvt.stack_error
696 (p_appli_s_name => 'XLA'
697 ,p_msg_name => 'XLA_AAD_UPL_INVALID_VALUE_SET'
698 ,p_token_1 => 'VALUE_SET_NAME'
699 ,p_value_1 => l.flex_value_set_name);
700
701 END LOOP;
702
703 IF (C_LEVEL_EVENT >= g_log_level) THEN
704 trace(p_msg => 'END LOOP: c_invalid_value_set',
705 p_module => l_log_module,
706 p_level => C_LEVEL_EVENT);
707 END IF;
708
709 IF (C_LEVEL_EVENT >= g_log_level) THEN
710 trace(p_msg => 'BEGIN LOOP: c_invalid_app_short_name',
711 p_module => l_log_module,
712 p_level => C_LEVEL_EVENT);
713 END IF;
714
715 FOR l IN c_invalid_app_short_name LOOP
716
717 IF (C_LEVEL_ERROR >= g_log_level) THEN
718 trace(p_msg => 'LOOP: c_invalid_app_short_name',
719 p_module => l_log_module,
720 p_level => C_LEVEL_ERROR);
721 END IF;
722
723 l_retcode := 'WARNING';
724 xla_aad_loader_util_pvt.stack_error
725 (p_appli_s_name => 'XLA'
726 ,p_msg_name => 'XLA_AAD_UPL_INVALID_APP_SN'
727 ,p_token_1 => 'VALUE_SET_NAME'
728 ,p_value_1 => 'l.app_short_name');
729
730 END LOOP;
731
732 IF (C_LEVEL_EVENT >= g_log_level) THEN
733 trace(p_msg => 'END LOOP: c_invalid_app_short_name',
734 p_module => l_log_module,
735 p_level => C_LEVEL_EVENT);
736 END IF;
737
738 IF (C_LEVEL_EVENT >= g_log_level) THEN
739 trace(p_msg => 'END of function validation',
743
740 p_module => l_log_module,
741 p_level => C_LEVEL_EVENT);
742 END IF;
744 RETURN l_retcode;
745 EXCEPTION
746
747 WHEN OTHERS THEN
748 l_retcode := 'ERROR';
749 xla_aad_loader_util_pvt.stack_error
750 (p_appli_s_name => 'XLA'
751 ,p_msg_name => 'XLA_COMMON_ERROR'
752 ,p_token_1 => 'LOCATION'
753 ,p_value_1 => 'xla_aad_upload_pvt.validation'
754 ,p_token_2 => 'ERROR'
755 ,p_value_2 => 'unhandled exception');
756 RAISE;
757 END;
758
759
760 --=============================================================================
761 --
762 -- Name: populate_descriptions
763 -- Description: This API populates the description data from the AAD Loader
764 -- interface table to the different AMB tables
765 --
766 --=============================================================================
767 PROCEDURE populate_descriptions
768 (p_application_id IN INTEGER
769 ,p_staging_context_code IN VARCHAR2)
770 IS
771 l_log_module VARCHAR2(240);
772 BEGIN
773 IF g_log_enabled THEN
774 l_log_module := C_DEFAULT_MODULE||'.populate_descriptions';
775 END IF;
776
777 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
778 trace(p_msg => 'BEGIN of procedure populate_descriptions',
779 p_module => l_log_module,
780 p_level => C_LEVEL_PROCEDURE);
781 END IF;
782
783 INSERT INTO xla_descriptions_b
784 (application_id
785 ,amb_context_code
786 ,description_type_code
787 ,description_code
788 ,transaction_coa_id
789 ,enabled_flag
790 ,creation_date
791 ,created_by
792 ,last_update_date
793 ,last_updated_by
794 ,last_update_login)
795 SELECT
796 p_application_id
797 ,p_staging_context_code
798 ,description_type_code
799 ,description_code
800 ,flex.id_flex_num
801 ,i.enabled_flag
802 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
803 ,fnd_load_util.owner_id(owner)
804 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
805 ,fnd_load_util.owner_id(owner)
806 ,0
807 FROM xla_aad_loader_defns_t i
808 ,fnd_id_flex_structures flex
809 WHERE flex.application_id(+) = 101
810 AND flex.id_flex_code(+) = 'GL#'
811 AND flex.id_flex_structure_code(+) = trans_coa_id_flex_struct_code
812 AND table_name = 'XLA_DESCRIPTIONS'
813 AND staging_amb_context_code = p_staging_context_code;
814
815 IF (C_LEVEL_EVENT >= g_log_level) THEN
816 trace(p_msg => '# insert (XLA_DESCRIPTIONS_B) = '||SQL%ROWCOUNT,
817 p_module => l_log_module,
818 p_level => C_LEVEL_EVENT);
819 END IF;
820
821 INSERT INTO xla_descriptions_tl
822 (application_id
823 ,amb_context_code
824 ,description_type_code
825 ,description_code
826 ,language
827 ,name
828 ,description
829 ,source_lang
830 ,creation_date
831 ,created_by
832 ,last_update_date
833 ,last_updated_by
834 ,last_update_login)
835 SELECT
836 p_application_id
837 ,p_staging_context_code
838 ,description_type_code
839 ,description_code
840 ,fl.language_code
841 ,name
842 ,description
843 ,USERENV('LANG')
844 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
845 ,fnd_load_util.owner_id(owner)
846 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
847 ,fnd_load_util.owner_id(owner)
848 ,0
849 FROM xla_aad_loader_defns_t xal
850 ,fnd_languages fl
851 WHERE xal.table_name = 'XLA_DESCRIPTIONS'
852 AND xal.staging_amb_context_code = p_staging_context_code
853 AND fl.installed_flag IN ('I', 'B');
854
855 IF (C_LEVEL_EVENT >= g_log_level) THEN
856 trace(p_msg => '# insert (XLA_DESCRIPTIONS_TL) = '||SQL%ROWCOUNT,
857 p_module => l_log_module,
858 p_level => C_LEVEL_EVENT);
859 END IF;
860
861 INSERT INTO xla_desc_priorities
865 ,description_code
862 (application_id
863 ,amb_context_code
864 ,description_type_code
866 ,description_prio_id
867 ,user_sequence
868 ,creation_date
869 ,created_by
870 ,last_update_date
871 ,last_updated_by
872 ,last_update_login)
873 SELECT
874 p_application_id
875 ,p_staging_context_code
876 ,description_type_code
877 ,description_code
878 ,xla_desc_priorities_s.nextval
879 ,priority_num
880 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
881 ,fnd_load_util.owner_id(owner)
882 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
883 ,fnd_load_util.owner_id(owner)
884 ,0
885 FROM xla_aad_loader_defns_t
886 WHERE table_name = 'XLA_DESC_PRIORITIES'
887 AND staging_amb_context_code = p_staging_context_code;
888
889 IF (C_LEVEL_EVENT >= g_log_level) THEN
890 trace(p_msg => '# insert (XLA_DESC_PRIORITIES) = '||SQL%ROWCOUNT,
891 p_module => l_log_module,
892 p_level => C_LEVEL_EVENT);
893 END IF;
894
895 INSERT INTO xla_descript_details_b
896 (amb_context_code
897 ,description_detail_id
898 ,description_prio_id
899 ,user_sequence
900 ,value_type_code
901 ,source_application_id
902 ,source_type_code
903 ,source_code
904 ,flexfield_segment_code
905 ,display_description_flag
906 ,creation_date
907 ,created_by
908 ,last_update_date
909 ,last_updated_by
910 ,last_update_login)
911 SELECT
912 p_staging_context_code
913 ,xla_descript_details_s.nextval
914 ,p.description_prio_id
915 ,xal.user_sequence
916 ,value_type_code
917 ,fap.application_id
918 ,source_type_code
919 ,source_code
920 ,flexfield_segment_code
921 ,display_description_flag
922 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
923 ,fnd_load_util.owner_id(owner)
924 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
925 ,fnd_load_util.owner_id(owner)
926 ,0
927 FROM xla_aad_loader_defns_t xal
928 ,xla_desc_priorities p
929 ,fnd_application fap
930 WHERE fap.application_short_name(+) = xal.source_app_short_name
931 AND p.user_sequence = xal.priority_num
932 AND p.description_type_code = xal.description_type_code
933 AND p.description_code = xal.description_code
934 AND p.amb_context_code = p_staging_context_code
935 AND p.application_id = p_application_id
936 AND table_name = 'XLA_DESCRIPT_DETAILS'
937 AND staging_amb_context_code = p_staging_context_code;
938
939 IF (C_LEVEL_EVENT >= g_log_level) THEN
940 trace(p_msg => '# insert (XLA_DESCRIPT_DETAILS_B) = '||SQL%ROWCOUNT,
941 p_module => l_log_module,
942 p_level => C_LEVEL_EVENT);
943 END IF;
944
945 INSERT INTO xla_descript_details_tl
946 (amb_context_code
947 ,description_detail_id
948 ,language
949 ,literal
950 ,source_lang
951 ,creation_date
952 ,created_by
953 ,last_update_date
954 ,last_updated_by
955 ,last_update_login)
956 SELECT
957 p_staging_context_code
958 ,xdd.description_detail_id
959 ,fl.language_code
960 ,literal
961 ,USERENV('LANG')
962 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
963 ,fnd_load_util.owner_id(owner)
964 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
965 ,fnd_load_util.owner_id(owner)
966 ,0
967 FROM xla_aad_loader_defns_t xal
968 ,xla_descript_details_b xdd
969 ,xla_desc_priorities xdp
970 ,fnd_languages fl
971 WHERE xdd.description_prio_id = xdp.description_prio_id
972 AND xdd.user_sequence = xal.user_sequence
973 AND xdp.user_sequence = xal.priority_num
974 AND xdp.description_type_code = xal.description_type_code
975 AND xdp.description_code = xal.description_code
976 AND xdp.amb_context_code = p_staging_context_code
977 AND xdp.application_id = p_application_id
978 AND xal.table_name = 'XLA_DESCRIPT_DETAILS'
979 AND xal.staging_amb_context_code = p_staging_context_code
980 AND fl.installed_flag IN ('I', 'B');
981
982 IF (C_LEVEL_EVENT >= g_log_level) THEN
983 trace(p_msg => '# insert (XLA_DESCRIPT_DETAILS_TL) = '||SQL%ROWCOUNT,
984 p_module => l_log_module,
985 p_level => C_LEVEL_EVENT);
986 END IF;
987
988 INSERT INTO xla_conditions
989 (amb_context_code
990 ,condition_id
991 ,user_sequence
992 ,application_id
993 ,description_prio_id
994 ,bracket_left_code
995 ,bracket_right_code
996 ,value_type_code
997 ,source_application_id
998 ,source_type_code
999 ,source_code
1000 ,flexfield_segment_code
1001 ,value_flexfield_segment_code
1002 ,value_source_application_id
1003 ,value_source_type_code
1004 ,value_source_code
1005 ,value_constant
1006 ,line_operator_code
1010 ,last_update_date
1007 ,logical_operator_code
1008 ,creation_date
1009 ,created_by
1011 ,last_updated_by
1012 ,last_update_login)
1013 SELECT
1014 p_staging_context_code
1015 ,xla_conditions_s.nextval
1016 ,xal.condition_num
1017 ,p_application_id
1018 ,description_prio_id
1019 ,bracket_left_code
1020 ,bracket_right_code
1021 ,value_type_code
1022 ,fap.application_id
1023 ,source_type_code
1024 ,source_code
1025 ,flexfield_segment_code
1026 ,value_flexfield_segment_code
1027 ,fap2.application_id
1028 ,value_source_type_code
1029 ,value_source_code
1030 ,value_constant
1031 ,line_operator_code
1032 ,logical_operator_code
1033 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1034 ,fnd_load_util.owner_id(owner)
1035 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1036 ,fnd_load_util.owner_id(owner)
1037 ,0
1038 FROM xla_aad_loader_defns_t xal
1039 ,xla_desc_priorities p
1040 ,fnd_application fap
1041 ,fnd_application fap2
1042 WHERE fap.application_short_name(+) = xal.source_app_short_name
1043 AND fap2.application_short_name(+)= xal.value_source_app_short_name
1044 AND p.user_sequence = xal.priority_num
1045 AND p.description_type_code = xal.description_type_code
1046 AND p.description_code = xal.description_code
1047 AND p.amb_context_code = p_staging_context_code
1048 AND p.application_id = p_application_id
1049 AND table_name = 'XLA_DESC_CONDITIONS'
1050 AND staging_amb_context_code = p_staging_context_code;
1051
1052 IF (C_LEVEL_EVENT >= g_log_level) THEN
1053 trace(p_msg => '# insert (XLA_DESC_CONDITIONS) = '||SQL%ROWCOUNT,
1054 p_module => l_log_module,
1055 p_level => C_LEVEL_EVENT);
1056 END IF;
1057
1058 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1059 trace(p_msg => 'END of procedure populate_descriptions',
1060 p_module => l_log_module,
1061 p_level => C_LEVEL_PROCEDURE);
1062 END IF;
1063
1064 EXCEPTION
1065 WHEN OTHERS THEN
1066 xla_aad_loader_util_pvt.stack_error
1067 (p_appli_s_name => 'XLA'
1068 ,p_msg_name => 'XLA_COMMON_ERROR'
1069 ,p_token_1 => 'LOCATION'
1070 ,p_value_1 => 'xla_aad_upload_pvt.populate_descriptions'
1071 ,p_token_2 => 'ERROR'
1072 ,p_value_2 => 'unhandled exception');
1073 RAISE;
1074
1075 END populate_descriptions;
1076
1077 --=============================================================================
1078 --
1079 -- Name: populate_mapping_sets
1083 --=============================================================================
1080 -- Description: This API populates the mapping set data from the AAD Loader
1081 -- interface table to the different AMB tables
1082 --
1084 FUNCTION populate_mapping_sets
1085 (p_application_id IN INTEGER
1086 ,p_staging_context_code IN VARCHAR2)
1087 RETURN VARCHAR2
1088 IS
1089 CURSOR c_ccid IS
1090 SELECT DISTINCT
1091 xal.mapping_set_code
1092 ,fif.id_flex_num
1093 ,fif.id_flex_structure_code
1094 ,xal.value_ccid_segment1
1095 ,xal.value_ccid_segment2
1096 ,xal.value_ccid_segment3
1097 ,xal.value_ccid_segment4
1098 ,xal.value_ccid_segment5
1099 ,xal.value_ccid_segment6
1100 ,xal.value_ccid_segment7
1101 ,xal.value_ccid_segment8
1102 ,xal.value_ccid_segment9
1103 ,xal.value_ccid_segment10
1104 ,xal.value_ccid_segment11
1105 ,xal.value_ccid_segment12
1106 ,xal.value_ccid_segment13
1107 ,xal.value_ccid_segment14
1108 ,xal.value_ccid_segment15
1109 ,xal.value_ccid_segment16
1110 ,xal.value_ccid_segment17
1111 ,xal.value_ccid_segment18
1112 ,xal.value_ccid_segment19
1113 ,xal.value_ccid_segment20
1114 ,xal.value_ccid_segment21
1115 ,xal.value_ccid_segment22
1116 ,xal.value_ccid_segment23
1117 ,xal.value_ccid_segment24
1118 ,xal.value_ccid_segment25
1119 ,xal.value_ccid_segment26
1120 ,xal.value_ccid_segment27
1121 ,xal.value_ccid_segment28
1122 ,xal.value_ccid_segment29
1123 ,xal.value_ccid_segment30
1124 FROM xla_aad_loader_defns_t xal
1125 ,fnd_id_flex_structures fif
1126 WHERE xal.table_name = 'XLA_MAPPING_SET_VALUES'
1127 AND xal.staging_amb_context_code = p_staging_context_code
1128 AND fif.application_id = 101
1129 AND fif.id_flex_code = 'GL#'
1130 AND fif.id_flex_structure_code = xal.value_ccid_id_flex_struct_code;
1131
1132 CURSOR c_mapping_set(p_mapping_set_code VARCHAR2) IS
1133 SELECT name
1134 FROM xla_mapping_sets_tl
1135 WHERE mapping_set_code = p_mapping_set_code
1136 AND language = USERENV('LANG');
1137
1138 l_seg FND_FLEX_EXT.SegmentArray;
1139 l_code_combination_id INTEGER;
1140 l_mapping_set_name VARCHAR2(80);
1141 l_error_found BOOLEAN;
1142 l_num_rows INTEGER;
1143 l_log_module VARCHAR2(240);
1144 BEGIN
1145 IF g_log_enabled THEN
1146 l_log_module := C_DEFAULT_MODULE||'.populate_mapping_sets';
1147 END IF;
1148
1149 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1150 trace(p_msg => 'BEGIN of procedure populate_mapping_sets',
1151 p_module => l_log_module,
1152 p_level => C_LEVEL_PROCEDURE);
1153 END IF;
1154
1155 INSERT INTO xla_mapping_sets_b
1156 (amb_context_code
1157 ,mapping_set_code
1158 ,accounting_coa_id
1159 ,value_set_id
1160 ,flexfield_assign_mode_code
1161 ,flexfield_segment_code
1162 ,enabled_flag
1163 ,view_application_id
1164 ,lookup_type
1165 ,version_num
1166 ,updated_flag
1167 ,creation_date
1168 ,created_by
1169 ,last_update_date
1170 ,last_updated_by
1171 ,last_update_login)
1172 SELECT
1173 p_staging_context_code
1174 ,mapping_set_code
1175 ,flex.id_flex_num
1176 ,val.flex_value_set_id
1177 ,flexfield_assign_mode_code
1178 ,flexfield_segment_code
1179 ,xal.enabled_flag
1180 ,fap.application_id
1181 ,lookup_type
1182 ,NVL(version_num,1)
1183 ,'N'
1184 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1185 ,fnd_load_util.owner_id(owner)
1186 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1187 ,fnd_load_util.owner_id(owner)
1188 ,0
1189 FROM xla_aad_loader_defns_t xal
1190 ,fnd_application fap
1191 ,fnd_id_flex_structures flex
1195 AND flex.id_flex_code = 'GL#'
1192 ,fnd_flex_value_sets val
1193 WHERE fap.application_short_name(+) = xal.view_app_short_name
1194 AND val.flex_value_set_name(+) = xal.flex_value_set_name
1196 AND flex.application_id = 101
1197 AND flex.id_flex_structure_code = xal.acct_coa_id_flex_struct_code
1198 AND table_name = 'XLA_MAPPING_SETS'
1199 AND staging_amb_context_code = p_staging_context_code;
1200
1201 l_num_rows := SQL%ROWCOUNT;
1202
1203 IF (C_LEVEL_EVENT >= g_log_level) THEN
1204 trace(p_msg => '# insert (XLA_MAPPING_SETS_B) = '||l_num_rows,
1205 p_module => l_log_module,
1206 p_level => C_LEVEL_EVENT);
1207 END IF;
1208
1209 IF (l_num_rows > 0) THEN
1210
1211 INSERT INTO xla_mapping_sets_tl
1212 (amb_context_code
1213 ,mapping_set_code
1214 ,language
1215 ,name
1216 ,description
1217 ,source_lang
1218 ,creation_date
1219 ,created_by
1220 ,last_update_date
1221 ,last_updated_by
1222 ,last_update_login)
1223 SELECT
1224 p_staging_context_code
1225 ,mapping_set_code
1226 ,fl.language_code
1227 ,name
1228 ,description
1229 ,USERENV('LANG')
1230 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1231 ,fnd_load_util.owner_id(owner)
1232 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1233 ,fnd_load_util.owner_id(owner)
1234 ,0
1235 FROM xla_aad_loader_defns_t xal
1236 ,fnd_languages fl
1237 WHERE xal.table_name = 'XLA_MAPPING_SETS'
1238 AND xal.staging_amb_context_code = p_staging_context_code
1239 AND fl.installed_flag IN ('I', 'B');
1240
1241 IF (C_LEVEL_EVENT >= g_log_level) THEN
1242 trace(p_msg => '# insert (XLA_MAPPING_SETS_TL) = '||SQL%ROWCOUNT,
1243 p_module => l_log_module,
1244 p_level => C_LEVEL_EVENT);
1245 END IF;
1246
1247 IF (C_LEVEL_EVENT >= g_log_level) THEN
1248 trace(p_msg => 'BEGIN LOOP - Retrieve CCID for XLA_MAPPING_SET_VALUES.view_code_combination_id',
1249 p_module => l_log_module,
1250 p_level => C_LEVEL_EVENT);
1251 END IF;
1252
1253 FOR l_ccid IN c_ccid LOOP
1254 IF (C_LEVEL_ERROR >= g_log_level) THEN
1255 trace(p_msg => 'LOOP Retrieve CCID: '||l_ccid.mapping_set_code,
1256 p_module => l_log_module,
1257 p_level => C_LEVEL_ERROR);
1258 END IF;
1259
1260 l_seg(1) := l_ccid.value_ccid_segment1;
1261 l_seg(2) := l_ccid.value_ccid_segment2;
1262 l_seg(3) := l_ccid.value_ccid_segment3;
1263 l_seg(4) := l_ccid.value_ccid_segment4;
1264 l_seg(5) := l_ccid.value_ccid_segment5;
1265 l_seg(6) := l_ccid.value_ccid_segment6;
1266 l_seg(7) := l_ccid.value_ccid_segment7;
1270 l_seg(11) := l_ccid.value_ccid_segment11;
1267 l_seg(8) := l_ccid.value_ccid_segment8;
1268 l_seg(9) := l_ccid.value_ccid_segment9;
1269 l_seg(10) := l_ccid.value_ccid_segment10;
1271 l_seg(12) := l_ccid.value_ccid_segment12;
1272 l_seg(13) := l_ccid.value_ccid_segment13;
1273 l_seg(14) := l_ccid.value_ccid_segment14;
1274 l_seg(15) := l_ccid.value_ccid_segment15;
1275 l_seg(16) := l_ccid.value_ccid_segment16;
1276 l_seg(17) := l_ccid.value_ccid_segment17;
1277 l_seg(18) := l_ccid.value_ccid_segment18;
1278 l_seg(19) := l_ccid.value_ccid_segment19;
1279 l_seg(20) := l_ccid.value_ccid_segment20;
1280 l_seg(21) := l_ccid.value_ccid_segment21;
1281 l_seg(22) := l_ccid.value_ccid_segment22;
1282 l_seg(23) := l_ccid.value_ccid_segment23;
1283 l_seg(24) := l_ccid.value_ccid_segment24;
1284 l_seg(25) := l_ccid.value_ccid_segment25;
1285 l_seg(26) := l_ccid.value_ccid_segment26;
1286 l_seg(27) := l_ccid.value_ccid_segment27;
1287 l_seg(28) := l_ccid.value_ccid_segment28;
1288 l_seg(29) := l_ccid.value_ccid_segment29;
1289 l_seg(30) := l_ccid.value_ccid_segment30;
1290
1291 IF (FND_FLEX_EXT.get_combination_id(
1292 application_short_name => 'SQLGL',
1293 key_flex_code => 'GL#',
1294 structure_number => l_ccid.id_flex_num,
1295 validation_date => null,
1296 n_segments => 30,
1297 segments => l_seg,
1298 combination_id => l_code_combination_id) = FALSE)
1299 THEN
1300
1301 OPEN c_mapping_set(l_ccid.mapping_set_code);
1302 FETCH c_mapping_set INTO l_mapping_set_name;
1303 CLOSE c_mapping_set;
1304
1305 xla_aad_loader_util_pvt.stack_error
1306 (p_appli_s_name => 'XLA'
1307 ,p_msg_name => 'XLA_AAD_IMP_INV_CCID_MS_VALUE'
1308 ,p_token_1 => 'MAPPING_SET'
1309 ,p_value_1 => l_mapping_set_name);
1310 l_error_found := TRUE;
1311 ELSE
1312 UPDATE xla_aad_loader_defns_t
1313 SET value_code_combination_id = l_code_combination_id
1314 WHERE staging_amb_context_code = p_staging_context_code
1315 AND mapping_set_code = l_ccid.mapping_set_code
1316 AND value_ccid_id_flex_struct_code = l_ccid.id_flex_structure_code
1317 AND nvl(value_ccid_segment1,C_CHAR) = nvl(l_ccid.value_ccid_segment1,C_CHAR)
1318 AND nvl(value_ccid_segment2,C_CHAR) = nvl(l_ccid.value_ccid_segment2,C_CHAR)
1319 AND nvl(value_ccid_segment3,C_CHAR) = nvl(l_ccid.value_ccid_segment3,C_CHAR)
1320 AND nvl(value_ccid_segment4,C_CHAR) = nvl(l_ccid.value_ccid_segment4,C_CHAR)
1321 AND nvl(value_ccid_segment5,C_CHAR) = nvl(l_ccid.value_ccid_segment5,C_CHAR)
1322 AND nvl(value_ccid_segment6,C_CHAR) = nvl(l_ccid.value_ccid_segment6,C_CHAR)
1323 AND nvl(value_ccid_segment7,C_CHAR) = nvl(l_ccid.value_ccid_segment7,C_CHAR)
1324 AND nvl(value_ccid_segment8,C_CHAR) = nvl(l_ccid.value_ccid_segment8,C_CHAR)
1325 AND nvl(value_ccid_segment9,C_CHAR) = nvl(l_ccid.value_ccid_segment9,C_CHAR)
1326 AND nvl(value_ccid_segment10,C_CHAR) = nvl(l_ccid.value_ccid_segment10,C_CHAR)
1327 AND nvl(value_ccid_segment11,C_CHAR) = nvl(l_ccid.value_ccid_segment11,C_CHAR)
1328 AND nvl(value_ccid_segment12,C_CHAR) = nvl(l_ccid.value_ccid_segment12,C_CHAR)
1329 AND nvl(value_ccid_segment13,C_CHAR) = nvl(l_ccid.value_ccid_segment13,C_CHAR)
1330 AND nvl(value_ccid_segment14,C_CHAR) = nvl(l_ccid.value_ccid_segment14,C_CHAR)
1331 AND nvl(value_ccid_segment15,C_CHAR) = nvl(l_ccid.value_ccid_segment15,C_CHAR)
1332 AND nvl(value_ccid_segment16,C_CHAR) = nvl(l_ccid.value_ccid_segment16,C_CHAR)
1333 AND nvl(value_ccid_segment17,C_CHAR) = nvl(l_ccid.value_ccid_segment17,C_CHAR)
1334 AND nvl(value_ccid_segment18,C_CHAR) = nvl(l_ccid.value_ccid_segment18,C_CHAR)
1335 AND nvl(value_ccid_segment19,C_CHAR) = nvl(l_ccid.value_ccid_segment19,C_CHAR)
1336 AND nvl(value_ccid_segment20,C_CHAR) = nvl(l_ccid.value_ccid_segment20,C_CHAR)
1337 AND nvl(value_ccid_segment21,C_CHAR) = nvl(l_ccid.value_ccid_segment21,C_CHAR)
1338 AND nvl(value_ccid_segment22,C_CHAR) = nvl(l_ccid.value_ccid_segment22,C_CHAR)
1339 AND nvl(value_ccid_segment23,C_CHAR) = nvl(l_ccid.value_ccid_segment23,C_CHAR)
1340 AND nvl(value_ccid_segment24,C_CHAR) = nvl(l_ccid.value_ccid_segment24,C_CHAR)
1341 AND nvl(value_ccid_segment25,C_CHAR) = nvl(l_ccid.value_ccid_segment25,C_CHAR)
1342 AND nvl(value_ccid_segment26,C_CHAR) = nvl(l_ccid.value_ccid_segment26,C_CHAR)
1343 AND nvl(value_ccid_segment27,C_CHAR) = nvl(l_ccid.value_ccid_segment27,C_CHAR)
1344 AND nvl(value_ccid_segment28,C_CHAR) = nvl(l_ccid.value_ccid_segment28,C_CHAR)
1345 AND nvl(value_ccid_segment29,C_CHAR) = nvl(l_ccid.value_ccid_segment29,C_CHAR)
1346 AND nvl(value_ccid_segment30,C_CHAR) = nvl(l_ccid.value_ccid_segment30,C_CHAR);
1347 END IF;
1348 END LOOP;
1349
1350 IF (C_LEVEL_EVENT >= g_log_level) THEN
1351 trace(p_msg => 'END LOOP - Retrieve CCID for XLA_MAPPING_SET_VALUES.view_code_combination_id',
1352 p_module => l_log_module,
1353 p_level => C_LEVEL_EVENT);
1354 END IF;
1355
1356 INSERT INTO xla_mapping_set_values
1360 ,value_constant
1357 (mapping_set_value_id
1358 ,amb_context_code
1359 ,mapping_set_code
1361 ,value_code_combination_id
1362 ,effective_date_from
1363 ,effective_date_to
1364 ,enabled_flag
1365 ,input_value_type_code
1366 ,input_value_constant
1367 ,creation_date
1368 ,created_by
1369 ,last_update_date
1370 ,last_updated_by
1371 ,last_update_login)
1372 SELECT
1373 xla_mapping_set_values_s.nextval
1374 ,p_staging_context_code
1375 ,mapping_set_code
1376 ,value_constant
1377 ,value_code_combination_id
1378 ,effective_date_from
1379 ,effective_date_to
1380 ,enabled_flag
1381 ,input_value_type_code
1382 ,input_value_constant
1383 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1384 ,fnd_load_util.owner_id(owner)
1385 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1386 ,fnd_load_util.owner_id(owner)
1387 ,0
1388 FROM xla_aad_loader_defns_t xal
1389 WHERE table_name = 'XLA_MAPPING_SET_VALUES'
1390 AND staging_amb_context_code = p_staging_context_code;
1391
1392 IF (C_LEVEL_EVENT >= g_log_level) THEN
1393 trace(p_msg => '# insert (XLA_MAPPING_SET_VALUES) = '||SQL%ROWCOUNT,
1394 p_module => l_log_module,
1395 p_level => C_LEVEL_EVENT);
1396 END IF;
1397
1398 END IF;
1399
1400 IF (l_error_found) THEN
1401 RAISE FND_API.G_EXC_ERROR;
1402 END IF;
1403
1404 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1405 trace(p_msg => 'END of procedure populate_mapping_sets',
1406 p_module => l_log_module,
1407 p_level => C_LEVEL_PROCEDURE);
1408 END IF;
1409
1410 RETURN 'SUCCESS';
1411 EXCEPTION
1412 WHEN FND_API.G_EXC_ERROR THEN
1413 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1414 trace(p_msg => 'END of procedure populate_adrs: ERROR',
1415 p_module => l_log_module,
1416 p_level => C_LEVEL_PROCEDURE);
1417 END IF;
1418
1419 RETURN 'WARNING';
1420
1421 WHEN OTHERS THEN
1422 xla_aad_loader_util_pvt.stack_error
1423 (p_appli_s_name => 'XLA'
1424 ,p_msg_name => 'XLA_COMMON_ERROR'
1425 ,p_token_1 => 'LOCATION'
1426 ,p_value_1 => 'xla_aad_upload_pvt.populate_mapping_sets'
1427 ,p_token_2 => 'ERROR'
1428 ,p_value_2 => 'unhandled exception');
1429 RAISE;
1430
1431 END populate_mapping_sets;
1432
1433 --=============================================================================
1434 --
1435 -- Name: populate_analytical_criteria
1436 -- Description: This API populates the analytical criteria data from the AAD Loader
1437 -- interface table to the different AMB tables
1438 --
1439 --=============================================================================
1440 PROCEDURE populate_analytical_criteria
1441 (p_application_id IN INTEGER
1442 ,p_staging_context_code IN VARCHAR2)
1443 IS
1444 l_num_rows INTEGER;
1445 l_log_module VARCHAR2(240);
1446 BEGIN
1447 IF g_log_enabled THEN
1448 l_log_module := C_DEFAULT_MODULE||'.populate_analytical_criteria';
1449 END IF;
1450
1451 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1452 trace(p_msg => 'BEGIN of procedure populate_analytical_criteria',
1453 p_module => l_log_module,
1454 p_level => C_LEVEL_PROCEDURE);
1455 END IF;
1456
1457 INSERT INTO xla_analytical_hdrs_b
1458 (amb_context_code
1459 ,analytical_criterion_type_code
1460 ,analytical_criterion_code
1461 ,application_id
1462 ,balancing_flag
1463 ,display_order
1464 ,enabled_flag
1465 ,year_end_carry_forward_code
1466 ,display_in_inquiries_flag
1467 ,criterion_value_code
1468 ,version_num
1469 ,updated_flag
1470 ,creation_date
1471 ,created_by
1472 ,last_update_date
1473 ,last_updated_by
1474 ,last_update_login)
1475 SELECT
1476 p_staging_context_code
1477 ,analytical_criterion_type_code
1478 ,analytical_criterion_code
1479 ,fap.application_id
1480 ,balancing_flag
1481 ,display_order
1482 ,enabled_flag
1483 ,year_end_carry_forward_code
1484 ,display_in_inquiries_flag
1485 ,criterion_value_code
1486 ,NVL(version_num,1)
1487 ,'N'
1488 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1489 ,fnd_load_util.owner_id(owner)
1490 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1491 ,fnd_load_util.owner_id(owner)
1492 ,0
1493 FROM xla_aad_loader_defns_t xal
1494 ,fnd_application fap
1495 WHERE fap.application_short_name(+) = xal.application_short_name
1496 AND table_name = 'XLA_ANALYTICAL_HDRS'
1497 AND staging_amb_context_code = p_staging_context_code;
1498
1499 l_num_rows := SQL%ROWCOUNT;
1500
1501 IF (C_LEVEL_EVENT >= g_log_level) THEN
1502 trace(p_msg => '# insert (XLA_ANALYTICAL_HDRS_B) = '||l_num_rows,
1503 p_module => l_log_module,
1504 p_level => C_LEVEL_EVENT);
1505 END IF;
1506
1510 ,analytical_criterion_type_code
1507 IF (l_num_rows > 0) THEN
1508 INSERT INTO xla_analytical_hdrs_tl
1509 (amb_context_code
1511 ,analytical_criterion_code
1512 ,language
1513 ,name
1514 ,description
1515 ,source_lang
1516 ,creation_date
1517 ,created_by
1518 ,last_update_date
1519 ,last_updated_by
1520 ,last_update_login)
1521 SELECT
1522 p_staging_context_code
1523 ,analytical_criterion_type_code
1524 ,analytical_criterion_code
1525 ,fl.language_code
1526 ,name
1527 ,description
1528 ,USERENV('LANG')
1529 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1530 ,fnd_load_util.owner_id(owner)
1531 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1532 ,fnd_load_util.owner_id(owner)
1533 ,0
1534 FROM xla_aad_loader_defns_t xal
1535 ,fnd_languages fl
1536 WHERE xal.table_name = 'XLA_ANALYTICAL_HDRS'
1537 AND xal.staging_amb_context_code = p_staging_context_code
1538 AND fl.installed_flag IN ('I', 'B');
1539
1540 IF (C_LEVEL_EVENT >= g_log_level) THEN
1541 trace(p_msg => '# insert (XLA_ANALYTICAL_HDRS_TL) = '||SQL%ROWCOUNT,
1542 p_module => l_log_module,
1543 p_level => C_LEVEL_EVENT);
1544 END IF;
1545 END IF; -- krsankar - Bug 7243326 - End of IF condition for l_num_rows for analytical_hdrs_tl table
1546
1547 INSERT INTO xla_analytical_dtls_b
1548 (amb_context_code
1549 ,analytical_criterion_type_code
1550 ,analytical_criterion_code
1551 ,analytical_detail_code
1552 ,data_type_code
1553 ,grouping_order
1554 ,creation_date
1555 ,created_by
1556 ,last_update_date
1557 ,last_updated_by
1558 ,last_update_login)
1559 SELECT
1560 p_staging_context_code
1561 ,analytical_criterion_type_code
1562 ,analytical_criterion_code
1563 ,analytical_detail_code
1564 ,data_type_code
1565 ,grouping_order
1566 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1567 ,fnd_load_util.owner_id(owner)
1568 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1569 ,fnd_load_util.owner_id(owner)
1570 ,0
1571 FROM xla_aad_loader_defns_t xal
1572 WHERE table_name = 'XLA_ANALYTICAL_DTLS'
1573 AND staging_amb_context_code = p_staging_context_code;
1574
1575 l_num_rows := SQL%ROWCOUNT;
1576
1577 IF (C_LEVEL_EVENT >= g_log_level) THEN
1578 trace(p_msg => '# insert (XLA_ANALYTICAL_DTLS_B) = '||l_num_rows,
1579 p_module => l_log_module,
1580 p_level => C_LEVEL_EVENT);
1581 END IF;
1582
1583 IF (l_num_rows > 0) THEN
1584 INSERT INTO xla_analytical_dtls_tl
1585 (amb_context_code
1586 ,analytical_criterion_type_code
1587 ,analytical_criterion_code
1588 ,analytical_detail_code
1589 ,language
1590 ,name
1591 ,description
1592 ,source_lang
1593 ,creation_date
1594 ,created_by
1595 ,last_update_date
1596 ,last_updated_by
1597 ,last_update_login)
1598 SELECT
1599 p_staging_context_code
1600 ,analytical_criterion_type_code
1601 ,analytical_criterion_code
1602 ,analytical_detail_code
1603 ,fl.language_code
1604 ,name
1605 ,description
1606 ,USERENV('LANG')
1607 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1608 ,fnd_load_util.owner_id(owner)
1609 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1610 ,fnd_load_util.owner_id(owner)
1611 ,0
1612 FROM xla_aad_loader_defns_t xal
1613 ,fnd_languages fl
1614 WHERE xal.table_name = 'XLA_ANALYTICAL_DTLS'
1615 AND xal.staging_amb_context_code = p_staging_context_code
1616 AND fl.installed_flag IN ('I', 'B');
1617
1618 IF (C_LEVEL_EVENT >= g_log_level) THEN
1619 trace(p_msg => '# insert (XLA_ANALYTICAL_DTLS_TL) = '||SQL%ROWCOUNT,
1620 p_module => l_log_module,
1621 p_level => C_LEVEL_EVENT);
1622 END IF;
1623
1624 INSERT INTO xla_analytical_sources
1625 (amb_context_code
1626 ,analytical_criterion_type_code
1627 ,analytical_criterion_code
1628 ,analytical_detail_code
1629 ,entity_code
1630 ,event_class_code
1631 ,application_id
1632 ,source_code
1633 ,source_type_code
1634 ,source_application_id
1635 ,creation_date
1636 ,created_by
1637 ,last_update_date
1638 ,last_updated_by
1639 ,last_update_login)
1640 SELECT
1641 p_staging_context_code
1642 ,analytical_criterion_type_code
1643 ,analytical_criterion_code
1644 ,analytical_detail_code
1645 ,entity_code
1646 ,event_class_code
1647 ,fap.application_id
1648 ,source_code
1649 ,source_type_code
1650 ,fap2.application_id
1651 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1652 ,fnd_load_util.owner_id(owner)
1656 FROM xla_aad_loader_defns_t xal
1653 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1654 ,fnd_load_util.owner_id(owner)
1655 ,0
1657 ,fnd_application fap
1658 ,fnd_application fap2
1659 WHERE fap.application_short_name = xal.application_short_name
1660 AND fap2.application_short_name = xal.source_app_short_name
1661 AND table_name = 'XLA_ANALYTICAL_SOURCES'
1662 AND staging_amb_context_code = p_staging_context_code;
1663
1664 IF (C_LEVEL_EVENT >= g_log_level) THEN
1665 trace(p_msg => '# insert (XLA_ANALYTICAL_SOURCES) = '||SQL%ROWCOUNT,
1666 p_module => l_log_module,
1667 p_level => C_LEVEL_EVENT);
1668 END IF;
1669 END IF; -- Detail exists
1670 -- END IF; -- Header exists -- Commented as part of Bug 7243326 as Header IF is closed immediately after analytical_hdrs_tl table
1671
1672 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1673 trace(p_msg => 'END of procedure populate_analytical_criteria',
1674 p_module => l_log_module,
1675 p_level => C_LEVEL_PROCEDURE);
1676 END IF;
1677
1678 EXCEPTION
1679 WHEN OTHERS THEN
1680 xla_aad_loader_util_pvt.stack_error
1681 (p_appli_s_name => 'XLA'
1682 ,p_msg_name => 'XLA_COMMON_ERROR'
1683 ,p_token_1 => 'LOCATION'
1684 ,p_value_1 => 'xla_aad_upload_pvt.populate_analytical_criteria'
1685 ,p_token_2 => 'ERROR'
1686 ,p_value_2 => 'unhandled exception');
1687 RAISE;
1688
1689 END populate_analytical_criteria;
1690
1691 --=============================================================================
1692 --
1693 -- Name: populate_adrs
1694 -- Description: This API populates the account derivation rule data from the AAD Loader
1695 -- interface table to the different AMB tables
1696 --
1697 --=============================================================================
1698 FUNCTION populate_adrs
1699 (p_application_id IN INTEGER
1700 ,p_staging_context_code IN VARCHAR2)
1701 RETURN VARCHAR2
1702 IS
1703 CURSOR c_ccid IS
1704 SELECT DISTINCT
1705 xal.segment_rule_type_code
1706 ,xal.segment_rule_code
1707 ,xal.user_sequence
1708 ,fif.id_flex_num
1709 ,fif.id_flex_structure_code
1710 ,xal.value_ccid_segment1
1711 ,xal.value_ccid_segment2
1712 ,xal.value_ccid_segment3
1713 ,xal.value_ccid_segment4
1714 ,xal.value_ccid_segment5
1715 ,xal.value_ccid_segment6
1716 ,xal.value_ccid_segment7
1717 ,xal.value_ccid_segment8
1718 ,xal.value_ccid_segment9
1719 ,xal.value_ccid_segment10
1720 ,xal.value_ccid_segment11
1721 ,xal.value_ccid_segment12
1722 ,xal.value_ccid_segment13
1723 ,xal.value_ccid_segment14
1724 ,xal.value_ccid_segment15
1725 ,xal.value_ccid_segment16
1726 ,xal.value_ccid_segment17
1727 ,xal.value_ccid_segment18
1728 ,xal.value_ccid_segment19
1729 ,xal.value_ccid_segment20
1730 ,xal.value_ccid_segment21
1731 ,xal.value_ccid_segment22
1732 ,xal.value_ccid_segment23
1733 ,xal.value_ccid_segment24
1734 ,xal.value_ccid_segment25
1735 ,xal.value_ccid_segment26
1736 ,xal.value_ccid_segment27
1737 ,xal.value_ccid_segment28
1738 ,xal.value_ccid_segment29
1739 ,xal.value_ccid_segment30
1740 FROM xla_aad_loader_defns_t xal
1741 ,fnd_id_flex_structures fif
1742 WHERE xal.table_name = 'XLA_SEG_RULE_DETAILS'
1743 AND xal.staging_amb_context_code = p_staging_context_code
1744 AND fif.application_id = 101
1745 AND fif.id_flex_code = 'GL#'
1746 AND fif.id_flex_structure_code = xal.value_ccid_id_flex_struct_code;
1747
1748 CURSOR c_seg_rule(p_seg_rule_type_code VARCHAR2
1749 ,p_seg_rule_code VARCHAR2) IS
1750 SELECT xsrt.name
1751 ,xlk.meaning seg_rule_owner
1752 FROM xla_seg_rules_tl xsrt
1753 ,xla_lookups xlk
1754 WHERE xsrt.segment_rule_type_code = p_seg_rule_type_code
1755 AND xsrt.segment_rule_code = p_seg_rule_code
1756 AND xsrt.amb_context_code = p_staging_context_code
1757 AND xsrt.application_id = p_application_id
1758 AND xsrt.language = USERENV('LANG')
1759 AND xlk.lookup_type = 'XLA_OWNER_TYPE'
1760 AND xlk.lookup_code = p_seg_rule_type_code;
1761
1762 i INTEGER;
1763 l_seg FND_FLEX_EXT.SegmentArray;
1764 l_code_combination_id INTEGER;
1765 l_error_found BOOLEAN;
1766 l_seg_rule_name VARCHAR2(80);
1767 l_seg_rule_owner VARCHAR2(80);
1768 l_log_module VARCHAR2(240);
1769 BEGIN
1770 IF g_log_enabled THEN
1771 l_log_module := C_DEFAULT_MODULE||'.populate_adrs';
1772 END IF;
1773
1774 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1778 END IF;
1775 trace(p_msg => 'BEGIN of procedure populate_adrs',
1776 p_module => l_log_module,
1777 p_level => C_LEVEL_PROCEDURE);
1779
1780 l_error_found := FALSE;
1781
1782 INSERT INTO xla_seg_rules_b
1783 (application_id
1784 ,amb_context_code
1785 ,segment_rule_type_code
1786 ,segment_rule_code
1787 ,transaction_coa_id
1788 ,accounting_coa_id
1789 ,flexfield_assign_mode_code
1790 ,flexfield_segment_code
1791 ,flex_value_set_id
1792 ,enabled_flag
1793 ,version_num
1794 ,updated_flag
1795 ,creation_date
1796 ,created_by
1797 ,last_update_date
1798 ,last_updated_by
1799 ,last_update_login)
1800 SELECT
1801 p_application_id
1802 ,p_staging_context_code
1803 ,segment_rule_type_code
1804 ,segment_rule_code
1805 ,fift.id_flex_num
1806 ,fifa.id_flex_num
1807 ,flexfield_assign_mode_code
1808 ,flexfield_segment_code
1809 ,val.flex_value_set_id
1810 ,xal.enabled_flag
1811 ,NVL(xal.version_num,1)
1812 ,'N'
1813 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1814 ,fnd_load_util.owner_id(owner)
1815 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1816 ,fnd_load_util.owner_id(owner)
1817 ,0
1818 FROM xla_aad_loader_defns_t xal
1819 ,fnd_id_flex_structures fift
1820 ,fnd_id_flex_structures fifa
1821 ,fnd_flex_value_sets val
1822 WHERE fift.application_id(+) = 101
1823 AND fift.id_flex_code(+) = 'GL#'
1824 AND fift.id_flex_structure_code(+) = trans_coa_id_flex_struct_code
1825 AND fifa.application_id(+) = 101
1826 AND fifa.id_flex_code(+) = 'GL#'
1827 AND fifa.id_flex_structure_code(+) = acct_coa_id_flex_struct_code
1828 AND val.flex_value_set_name(+) = xal.flex_value_set_name
1829 AND table_name = 'XLA_SEG_RULES'
1830 AND staging_amb_context_code = p_staging_context_code;
1831
1832 IF (C_LEVEL_EVENT >= g_log_level) THEN
1833 trace(p_msg => '# insert (XLA_SEG_RULES_B) = '||SQL%ROWCOUNT,
1834 p_module => l_log_module,
1835 p_level => C_LEVEL_EVENT);
1836 END IF;
1837
1838 INSERT INTO xla_seg_rules_tl
1839 (application_id
1840 ,amb_context_code
1841 ,segment_rule_type_code
1842 ,segment_rule_code
1843 ,language
1844 ,name
1845 ,description
1846 ,source_lang
1847 ,creation_date
1848 ,created_by
1849 ,last_update_date
1850 ,last_updated_by
1851 ,last_update_login)
1852 SELECT
1853 p_application_id
1854 ,p_staging_context_code
1855 ,segment_rule_type_code
1856 ,segment_rule_code
1857 ,fl.language_code
1858 ,name
1859 ,description
1860 ,USERENV('LANG')
1861 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1862 ,fnd_load_util.owner_id(owner)
1863 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1864 ,fnd_load_util.owner_id(owner)
1865 ,0
1866 FROM xla_aad_loader_defns_t xal
1867 ,fnd_languages fl
1868 WHERE xal.table_name = 'XLA_SEG_RULES'
1869 AND xal.staging_amb_context_code = p_staging_context_code
1870 AND fl.installed_flag IN ('I', 'B');
1871
1872 IF (C_LEVEL_EVENT >= g_log_level) THEN
1873 trace(p_msg => '# insert (XLA_SEG_RULES_TL) = '||SQL%ROWCOUNT,
1874 p_module => l_log_module,
1875 p_level => C_LEVEL_EVENT);
1876 END IF;
1877
1878 INSERT INTO xla_seg_rule_details
1879 (segment_rule_detail_id
1880 ,application_id
1881 ,amb_context_code
1882 ,segment_rule_type_code
1883 ,segment_rule_code
1884 ,user_sequence
1885 ,value_type_code
1886 ,value_source_application_id
1887 ,value_source_type_code
1888 ,value_source_code
1889 ,value_constant
1890 ,value_mapping_set_code
1891 ,value_flexfield_segment_code
1892 ,value_adr_version_num
1893 ,value_segment_rule_appl_id
1894 ,value_segment_rule_type_code
1895 ,value_segment_rule_code
1896 ,input_source_application_id
1897 ,input_source_type_code
1898 ,input_source_code
1899 ,creation_date
1900 ,created_by
1901 ,last_update_date
1902 ,last_updated_by
1903 ,last_update_login)
1904 SELECT
1905 xla_seg_rule_details_s.nextval
1906 ,p_application_id
1907 ,p_staging_context_code
1908 ,segment_rule_type_code
1909 ,segment_rule_code
1910 ,user_sequence
1911 ,value_type_code
1912 ,fap.application_id
1913 ,value_source_type_code
1914 ,value_source_code
1915 ,value_constant
1916 ,value_mapping_set_code
1917 ,value_flexfield_segment_code
1918 ,NVL(value_adr_version_num,0)
1919 ,NVL(fap3.application_id,
1920 CASE WHEN value_segment_rule_type_code IS NOT NULL
1921 THEN p_application_id
1922 ELSE NULL END)
1923 ,value_segment_rule_type_code
1924 ,value_segment_rule_code
1925 ,fap2.application_id
1926 ,input_source_type_code
1927 ,input_source_code
1928 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1929 ,fnd_load_util.owner_id(owner)
1930 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
1931 ,fnd_load_util.owner_id(owner)
1932 ,0
1933 FROM xla_aad_loader_defns_t xal
1937 ON fap2.application_short_name = xal.input_source_app_short_name
1934 LEFT OUTER JOIN fnd_application fap
1935 ON fap.application_short_name = xal.value_source_app_short_name
1936 LEFT OUTER JOIN fnd_application fap2
1938 LEFT OUTER JOIN fnd_application fap3
1939 ON fap3.application_short_name = xal.value_segment_rule_appl_sn
1940 WHERE table_name = 'XLA_SEG_RULE_DETAILS'
1941 AND staging_amb_context_code = p_staging_context_code;
1942
1943 IF (C_LEVEL_EVENT >= g_log_level) THEN
1944 trace(p_msg => '# insert (XLA_SEG_RULE_DETAILS) = '||SQL%ROWCOUNT,
1945 p_module => l_log_module,
1946 p_level => C_LEVEL_EVENT);
1947 END IF;
1948
1949 IF (C_LEVEL_EVENT >= g_log_level) THEN
1950 trace(p_msg => 'BEGIN LOOP - Retrieve CCID for XLA_SEG_RULE_DETAILS.view_code_combination_id',
1951 p_module => l_log_module,
1952 p_level => C_LEVEL_EVENT);
1953 END IF;
1954
1955 FOR l_ccid IN c_ccid LOOP
1956 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1957 trace(p_msg => 'LOOP Retrieve CCID: '||
1958 l_ccid.segment_rule_type_code||','||
1959 l_ccid.segment_rule_code||','||
1960 l_ccid.user_sequence||','||
1961 l_ccid.id_flex_num,
1962 p_module => l_log_module,
1963 p_level => C_LEVEL_PROCEDURE);
1964 END IF;
1965
1966 l_seg(1) := l_ccid.value_ccid_segment1;
1967 l_seg(2) := l_ccid.value_ccid_segment2;
1968 l_seg(3) := l_ccid.value_ccid_segment3;
1969 l_seg(4) := l_ccid.value_ccid_segment4;
1970 l_seg(5) := l_ccid.value_ccid_segment5;
1971 l_seg(6) := l_ccid.value_ccid_segment6;
1972 l_seg(7) := l_ccid.value_ccid_segment7;
1973 l_seg(8) := l_ccid.value_ccid_segment8;
1974 l_seg(9) := l_ccid.value_ccid_segment9;
1975 l_seg(10) := l_ccid.value_ccid_segment10;
1976 l_seg(11) := l_ccid.value_ccid_segment11;
1977 l_seg(12) := l_ccid.value_ccid_segment12;
1978 l_seg(13) := l_ccid.value_ccid_segment13;
1979 l_seg(14) := l_ccid.value_ccid_segment14;
1980 l_seg(15) := l_ccid.value_ccid_segment15;
1981 l_seg(16) := l_ccid.value_ccid_segment16;
1982 l_seg(17) := l_ccid.value_ccid_segment17;
1983 l_seg(18) := l_ccid.value_ccid_segment18;
1984 l_seg(19) := l_ccid.value_ccid_segment19;
1985 l_seg(20) := l_ccid.value_ccid_segment20;
1986 l_seg(21) := l_ccid.value_ccid_segment21;
1987 l_seg(22) := l_ccid.value_ccid_segment22;
1988 l_seg(23) := l_ccid.value_ccid_segment23;
1989 l_seg(24) := l_ccid.value_ccid_segment24;
1990 l_seg(25) := l_ccid.value_ccid_segment25;
1991 l_seg(26) := l_ccid.value_ccid_segment26;
1992 l_seg(27) := l_ccid.value_ccid_segment27;
1993 l_seg(28) := l_ccid.value_ccid_segment28;
1994 l_seg(29) := l_ccid.value_ccid_segment29;
1995 l_seg(30) := l_ccid.value_ccid_segment30;
1996
1997 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1998 FOR i IN 1..30 LOOP
1999 trace(p_msg => 'l_seg('||i||') = '||l_seg(i),
2000 p_module => l_log_module,
2001 p_level => C_LEVEL_STATEMENT);
2002 END LOOP;
2003 END IF;
2004
2005 IF (FND_FLEX_EXT.get_combination_id(
2006 application_short_name => 'SQLGL',
2007 key_flex_code => 'GL#',
2008 structure_number => l_ccid.id_flex_num,
2009 validation_date => null,
2010 n_segments => 30,
2011 segments => l_seg,
2012 combination_id => l_code_combination_id) = FALSE) THEN
2013
2014 OPEN c_seg_rule(l_ccid.segment_rule_type_code
2015 ,l_ccid.segment_rule_code);
2016 FETCH c_seg_rule INTO l_seg_rule_name, l_seg_rule_owner;
2017 CLOSE c_seg_rule;
2018
2019 xla_aad_loader_util_pvt.stack_error
2020 (p_appli_s_name => 'XLA'
2021 ,p_msg_name => 'XLA_AAD_IMP_INV_CCID_ADR_DET'
2022 ,p_token_1 => 'SEG_RULE'
2023 ,p_value_1 => l_seg_rule_name
2024 ,p_token_2 => 'OWNER'
2025 ,p_value_2 => l_seg_rule_owner
2026 ,p_token_3 => 'USER_SEQUENCE'
2027 ,p_value_3 => l_ccid.user_sequence);
2028 l_error_found := TRUE;
2029 ELSE
2030 UPDATE xla_seg_rule_details
2031 SET value_code_combination_id = l_code_combination_id
2032 WHERE amb_context_code = p_staging_context_code
2033 AND application_id = p_application_id
2034 AND segment_rule_type_code = l_ccid.segment_rule_type_code
2035 AND segment_rule_code = l_ccid.segment_rule_code
2036 AND user_sequence = l_ccid.user_sequence;
2037 END IF;
2038 END LOOP;
2039
2040 IF (C_LEVEL_EVENT >= g_log_level) THEN
2041 trace(p_msg => 'END LOOP - Retrieve CCID for XLA_SEG_RULE_DETAILS.view_code_combination_id',
2042 p_module => l_log_module,
2043 p_level => C_LEVEL_EVENT);
2044 END IF;
2045
2046 INSERT INTO xla_conditions
2047 (amb_context_code
2048 ,condition_id
2049 ,user_sequence
2050 ,application_id
2051 ,segment_rule_detail_id
2052 ,bracket_left_code
2056 ,source_type_code
2053 ,bracket_right_code
2054 ,value_type_code
2055 ,source_application_id
2057 ,source_code
2058 ,flexfield_segment_code
2059 ,value_flexfield_segment_code
2060 ,value_source_application_id
2061 ,value_source_type_code
2062 ,value_source_code
2063 ,value_constant
2064 ,line_operator_code
2065 ,logical_operator_code
2066 ,creation_date
2067 ,created_by
2068 ,last_update_date
2069 ,last_updated_by
2070 ,last_update_login)
2071 SELECT
2072 p_staging_context_code
2073 ,xla_conditions_s.nextval
2074 ,xal.condition_num
2075 ,p_application_id
2076 ,segment_rule_detail_id
2077 ,xal.bracket_left_code
2078 ,xal.bracket_right_code
2079 ,xal.value_type_code
2080 ,fap.application_id
2081 ,xal.source_type_code
2082 ,xal.source_code
2083 ,xal.flexfield_segment_code
2084 ,xal.value_flexfield_segment_code
2085 ,fap2.application_id
2086 ,xal.value_source_type_code
2087 ,xal.value_source_code
2088 ,xal.value_constant
2089 ,xal.line_operator_code
2090 ,xal.logical_operator_code
2091 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2092 ,fnd_load_util.owner_id(owner)
2093 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2094 ,fnd_load_util.owner_id(owner)
2095 ,0
2096 FROM xla_aad_loader_defns_t xal
2097 ,xla_seg_rule_details d
2098 ,fnd_application fap
2099 ,fnd_application fap2
2100 WHERE fap.application_short_name(+) = xal.source_app_short_name
2101 AND fap2.application_short_name(+)= xal.value_source_app_short_name
2102 AND d.user_sequence = xal.user_sequence
2103 AND d.segment_rule_type_code = xal.segment_rule_type_code
2104 AND d.segment_rule_code = xal.segment_rule_code
2105 AND d.amb_context_code = p_staging_context_code
2106 AND d.application_id = p_application_id
2107 AND table_name = 'XLA_ADR_CONDITIONS'
2108 AND staging_amb_context_code = p_staging_context_code;
2109
2110 IF (C_LEVEL_EVENT >= g_log_level) THEN
2111 trace(p_msg => '# insert (XLA_ADR_CONDITIONS) = '||SQL%ROWCOUNT,
2112 p_module => l_log_module,
2113 p_level => C_LEVEL_EVENT);
2114 END IF;
2115
2116 IF (l_error_found) THEN
2117 RAISE FND_API.G_EXC_ERROR;
2118 END IF;
2119
2120 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2121 trace(p_msg => 'END of procedure populate_adrs',
2122 p_module => l_log_module,
2123 p_level => C_LEVEL_PROCEDURE);
2124 END IF;
2125
2126 RETURN 'SUCCESS';
2127 EXCEPTION
2128 WHEN FND_API.G_EXC_ERROR THEN
2129 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2130 trace(p_msg => 'END of procedure populate_adrs: ERROR',
2131 p_module => l_log_module,
2132 p_level => C_LEVEL_PROCEDURE);
2133 END IF;
2134
2135 RETURN 'WARNING';
2136
2137 WHEN OTHERS THEN
2138 xla_aad_loader_util_pvt.stack_error
2139 (p_appli_s_name => 'XLA'
2140 ,p_msg_name => 'XLA_COMMON_ERROR'
2141 ,p_token_1 => 'LOCATION'
2142 ,p_value_1 => 'xla_aad_upload_pvt.populate_adrs'
2143 ,p_token_2 => 'ERROR'
2144 ,p_value_2 => 'unhandled exception');
2145 RAISE;
2146
2147 END populate_adrs;
2148
2149 --=============================================================================
2150 --
2151 -- Name: populate_journal_line_types
2152 -- Description: This API populates the journal line type data from the AAD Loader
2153 -- interface table to the different AMB tables
2154 --
2155 --=============================================================================
2156 PROCEDURE populate_journal_line_types
2157 (p_application_id IN INTEGER
2158 ,p_staging_context_code IN VARCHAR2)
2159 IS
2160 l_log_module VARCHAR2(240);
2161 BEGIN
2162 IF g_log_enabled THEN
2163 l_log_module := C_DEFAULT_MODULE||'.populate_journal_line_types';
2164 END IF;
2165
2166 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2167 trace(p_msg => 'BEGIN of procedure populate_journal_line_types',
2168 p_module => l_log_module,
2169 p_level => C_LEVEL_PROCEDURE);
2170 END IF;
2171
2172 INSERT INTO xla_acct_line_types_b
2173 (application_id
2174 ,amb_context_code
2175 ,entity_code
2176 ,event_class_code
2177 ,accounting_line_type_code
2178 ,accounting_line_code
2179 ,transaction_coa_id
2180 ,accounting_entry_type_code
2181 ,natural_side_code
2182 ,gl_transfer_mode_code
2183 ,switch_side_flag
2184 ,gain_or_loss_flag
2185 ,merge_duplicate_code
2186 ,enabled_flag
2187 ,accounting_class_code
2188 ,business_method_code
2189 ,business_class_code
2190 ,rounding_class_code
2191 ,encumbrance_type_id
2192 ,mpa_option_code
2193 ,creation_date
2194 ,created_by
2195 ,last_update_date
2196 ,last_updated_by
2197 ,last_update_login)
2198 SELECT
2199 p_application_id
2200 ,p_staging_context_code
2201 ,xal.entity_code
2202 ,xal.event_class_code
2203 ,xal.accounting_line_type_code
2204 ,xal.accounting_line_code
2205 ,flex.id_flex_num
2206 ,xal.accounting_entry_type_code
2207 ,xal.natural_side_code
2208 ,xal.gl_transfer_mode_code
2209 ,xal.switch_side_flag
2210 ,xal.inherit_desc_flag
2211 ,xal.merge_duplicate_code
2212 ,xal.enabled_flag
2213 ,xal.accounting_class_code
2214 ,NVL(xal.business_method_code,'NONE')
2215 ,xal.business_class_code
2216 ,NVL(xal.rounding_class_code,xal.accounting_class_code)
2217 ,get.encumbrance_type_id
2218 ,NVL(xal.mpa_option_code,'NONE')
2219 ,nvl(to_date(xal.orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2220 ,fnd_load_util.owner_id(xal.owner)
2221 ,nvl(to_date(xal.orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2222 ,fnd_load_util.owner_id(xal.owner)
2223 ,0
2224 FROM xla_aad_loader_defns_t xal
2225 ,fnd_id_flex_structures flex
2226 ,gl_encumbrance_types get
2227 WHERE flex.application_id(+) = 101
2228 AND flex.id_flex_code(+) = 'GL#'
2229 AND flex.id_flex_structure_code(+) = trans_coa_id_flex_struct_code
2230 AND get.encumbrance_type_key(+) = xal.encumbrance_type
2231 AND table_name = 'XLA_ACCT_LINE_TYPES'
2232 AND staging_amb_context_code = p_staging_context_code;
2233
2234 IF (C_LEVEL_EVENT >= g_log_level) THEN
2235 trace(p_msg => '# insert (XLA_ACCT_LINE_TYPES_B) = '||SQL%ROWCOUNT,
2236 p_module => l_log_module,
2237 p_level => C_LEVEL_EVENT);
2238 END IF;
2239
2240 INSERT INTO xla_acct_line_types_tl
2241 (application_id
2242 ,amb_context_code
2243 ,entity_code
2244 ,event_class_code
2245 ,accounting_line_type_code
2246 ,accounting_line_code
2247 ,language
2248 ,name
2249 ,description
2250 ,source_lang
2251 ,creation_date
2252 ,created_by
2253 ,last_update_date
2254 ,last_updated_by
2255 ,last_update_login)
2256 SELECT
2257 p_application_id
2258 ,p_staging_context_code
2259 ,entity_code
2260 ,event_class_code
2261 ,accounting_line_type_code
2262 ,accounting_line_code
2263 ,fl.language_code
2264 ,name
2265 ,description
2266 ,USERENV('LANG')
2267 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2268 ,fnd_load_util.owner_id(owner)
2269 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2270 ,fnd_load_util.owner_id(owner)
2271 ,0
2272 FROM xla_aad_loader_defns_t xal
2273 ,fnd_languages fl
2274 WHERE xal.table_name = 'XLA_ACCT_LINE_TYPES'
2275 AND xal.staging_amb_context_code = p_staging_context_code
2276 AND fl.installed_flag IN ('I', 'B');
2277
2278 IF (C_LEVEL_EVENT >= g_log_level) THEN
2279 trace(p_msg => '# insert (XLA_ACCT_LINE_TYPES_TL) = '||SQL%ROWCOUNT,
2280 p_module => l_log_module,
2281 p_level => C_LEVEL_EVENT);
2282 END IF;
2283
2284 INSERT INTO xla_jlt_acct_attrs
2285 (application_id
2286 ,amb_context_code
2287 ,event_class_code
2288 ,accounting_line_type_code
2289 ,accounting_line_code
2290 ,accounting_attribute_code
2291 ,source_application_id
2292 ,source_type_code
2296 ,created_by
2293 ,source_code
2294 ,event_class_default_flag
2295 ,creation_date
2297 ,last_update_date
2298 ,last_updated_by
2299 ,last_update_login)
2300 SELECT
2301 p_application_id
2302 ,p_staging_context_code
2303 ,event_class_code
2304 ,accounting_line_type_code
2305 ,accounting_line_code
2306 ,accounting_attribute_code
2307 ,fap.application_id
2308 ,source_type_code
2309 ,source_code
2310 ,event_class_default_flag
2311 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2312 ,fnd_load_util.owner_id(owner)
2313 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2314 ,fnd_load_util.owner_id(owner)
2315 ,0
2316 FROM xla_aad_loader_defns_t xal
2317 ,fnd_application fap
2318 WHERE fap.application_short_name(+) = xal.source_app_short_name
2319 AND table_name = 'XLA_JLT_ACCT_ATTRS'
2320 AND staging_amb_context_code = p_staging_context_code;
2321
2322 IF (C_LEVEL_EVENT >= g_log_level) THEN
2323 trace(p_msg => '# insert (XLA_JLT_ACCT_ATTRS) = '||SQL%ROWCOUNT,
2324 p_module => l_log_module,
2325 p_level => C_LEVEL_EVENT);
2326 END IF;
2327
2328 INSERT INTO xla_conditions
2329 (amb_context_code
2330 ,condition_id
2331 ,user_sequence
2332 ,application_id
2333 ,entity_code
2334 ,event_class_code
2335 ,accounting_line_type_code
2336 ,accounting_line_code
2337 ,bracket_left_code
2338 ,bracket_right_code
2339 ,value_type_code
2340 ,source_application_id
2341 ,source_type_code
2342 ,source_code
2343 ,flexfield_segment_code
2344 ,value_flexfield_segment_code
2345 ,value_source_application_id
2346 ,value_source_type_code
2347 ,value_source_code
2348 ,value_constant
2349 ,line_operator_code
2350 ,logical_operator_code
2351 ,creation_date
2352 ,created_by
2353 ,last_update_date
2354 ,last_updated_by
2355 ,last_update_login)
2356 SELECT
2357 p_staging_context_code
2358 ,xla_conditions_s.nextval
2359 ,xal.condition_num
2360 ,p_application_id
2361 ,entity_code
2362 ,event_class_code
2363 ,accounting_line_type_code
2364 ,accounting_line_code
2365 ,bracket_left_code
2366 ,bracket_right_code
2367 ,value_type_code
2368 ,fap.application_id
2369 ,source_type_code
2370 ,source_code
2371 ,flexfield_segment_code
2372 ,value_flexfield_segment_code
2373 ,fap2.application_id
2374 ,value_source_type_code
2375 ,value_source_code
2376 ,value_constant
2377 ,line_operator_code
2378 ,logical_operator_code
2379 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2380 ,fnd_load_util.owner_id(owner)
2381 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2382 ,fnd_load_util.owner_id(owner)
2383 ,0
2384 FROM xla_aad_loader_defns_t xal
2385 ,fnd_application fap
2386 ,fnd_application fap2
2387 WHERE fap.application_short_name(+) = xal.source_app_short_name
2388 AND fap2.application_short_name(+)= xal.value_source_app_short_name
2389 AND table_name = 'XLA_JLT_CONDITIONS'
2390 AND staging_amb_context_code = p_staging_context_code;
2391
2392 IF (C_LEVEL_EVENT >= g_log_level) THEN
2393 trace(p_msg => '# insert (XLA_JLT_CONDITIONS) = '||SQL%ROWCOUNT,
2394 p_module => l_log_module,
2395 p_level => C_LEVEL_EVENT);
2396 END IF;
2397
2398 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2399 trace(p_msg => 'END of procedure populate_journal_line_types',
2400 p_module => l_log_module,
2401 p_level => C_LEVEL_PROCEDURE);
2402 END IF;
2403
2404 EXCEPTION
2405 WHEN OTHERS THEN
2406 xla_aad_loader_util_pvt.stack_error
2407 (p_appli_s_name => 'XLA'
2408 ,p_msg_name => 'XLA_COMMON_ERROR'
2409 ,p_token_1 => 'LOCATION'
2410 ,p_value_1 => 'xla_aad_upload_pvt.populate_journal_line_types'
2411 ,p_token_2 => 'ERROR'
2412 ,p_value_2 => 'unhandled exception');
2413 RAISE;
2414
2415 END populate_journal_line_types;
2416
2417 --=============================================================================
2418 --
2419 -- Name: populate_jlds
2420 -- Description: This API populates the AADs data from the AAD Loader
2421 -- interface table to the different AMB tables
2422 --
2423 --=============================================================================
2424 PROCEDURE populate_jlds
2425 (p_application_id IN INTEGER
2426 ,p_staging_context_code IN VARCHAR2)
2427 IS
2428 l_log_module VARCHAR2(240);
2429 BEGIN
2430 IF g_log_enabled THEN
2431 l_log_module := C_DEFAULT_MODULE||'.populate_jlds';
2432 END IF;
2433
2434 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2435 trace(p_msg => 'BEGIN of procedure populate_jlds',
2436 p_module => l_log_module,
2437 p_level => C_LEVEL_PROCEDURE);
2438 END IF;
2439
2440 INSERT INTO xla_line_definitions_b
2441 (application_id
2445 ,line_definition_owner_code
2442 ,amb_context_code
2443 ,event_class_code
2444 ,event_type_code
2446 ,line_definition_code
2447 ,transaction_coa_id
2448 ,accounting_coa_id
2449 ,enabled_flag
2450 ,validation_status_code
2451 ,budgetary_control_flag
2452 ,object_version_number
2453 ,creation_date
2454 ,created_by
2455 ,last_update_date
2456 ,last_updated_by
2457 ,last_update_login)
2458 SELECT
2459 p_application_id
2460 ,p_staging_context_code
2461 ,event_class_code
2462 ,event_type_code
2463 ,line_definition_owner_code
2464 ,line_definition_code
2465 ,fift.id_flex_num
2466 ,fifa.id_flex_num
2467 ,xal.enabled_flag
2468 ,'N'
2469 ,budgetary_control_flag
2470 ,1
2471 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2472 ,fnd_load_util.owner_id(owner)
2473 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2474 ,fnd_load_util.owner_id(owner)
2475 ,0
2476 FROM xla_aad_loader_defns_t xal
2477 ,fnd_id_flex_structures fift
2478 ,fnd_id_flex_structures fifa
2479 WHERE fift.application_id(+) = 101
2480 AND fift.id_flex_code(+) = 'GL#'
2481 AND fift.id_flex_structure_code(+) = trans_coa_id_flex_struct_code
2482 AND fifa.application_id(+) = 101
2483 AND fifa.id_flex_code(+) = 'GL#'
2484 AND fifa.id_flex_structure_code(+) = acct_coa_id_flex_struct_code
2485 AND table_name = 'XLA_LINE_DEFINITIONS'
2486 AND staging_amb_context_code = p_staging_context_code;
2487
2488 IF (C_LEVEL_EVENT >= g_log_level) THEN
2489 trace(p_msg => '# insert (XLA_LINE_DEFINITIONS_B) = '||SQL%ROWCOUNT,
2490 p_module => l_log_module,
2491 p_level => C_LEVEL_EVENT);
2492 END IF;
2493
2494 INSERT INTO xla_line_definitions_tl
2495 (application_id
2496 ,amb_context_code
2497 ,event_class_code
2498 ,event_type_code
2499 ,line_definition_owner_code
2500 ,line_definition_code
2501 ,language
2502 ,name
2503 ,description
2504 ,source_lang
2505 ,object_version_number
2506 ,creation_date
2507 ,created_by
2508 ,last_update_date
2509 ,last_updated_by
2510 ,last_update_login)
2511 SELECT
2512 p_application_id
2513 ,p_staging_context_code
2514 ,event_class_code
2515 ,event_type_code
2516 ,line_definition_owner_code
2517 ,line_definition_code
2518 ,fl.language_code
2519 ,name
2520 ,description
2521 ,USERENV('LANG')
2522 ,1
2523 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2524 ,fnd_load_util.owner_id(owner)
2525 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2526 ,fnd_load_util.owner_id(owner)
2527 ,0
2528 FROM xla_aad_loader_defns_t xal
2529 ,fnd_languages fl
2530 WHERE xal.table_name = 'XLA_LINE_DEFINITIONS'
2531 AND xal.staging_amb_context_code = p_staging_context_code
2532 AND fl.installed_flag IN ('I', 'B');
2533
2534 IF (C_LEVEL_EVENT >= g_log_level) THEN
2535 trace(p_msg => '# insert (XLA_LINE_DEFINITIONS_TL) = '||SQL%ROWCOUNT,
2536 p_module => l_log_module,
2537 p_level => C_LEVEL_EVENT);
2538 END IF;
2539
2540 INSERT INTO xla_line_defn_jlt_assgns
2541 (application_id
2542 ,amb_context_code
2546 ,line_definition_code
2543 ,event_class_code
2544 ,event_type_code
2545 ,line_definition_owner_code
2547 ,accounting_line_type_code
2548 ,accounting_line_code
2549 ,description_type_code
2550 ,description_code
2551 ,active_flag
2552 ,inherit_desc_flag
2553 ,mpa_header_desc_type_code
2554 ,mpa_header_desc_code
2555 ,mpa_num_je_code
2556 ,mpa_gl_dates_code
2557 ,mpa_proration_code
2558 ,object_version_number
2559 ,creation_date
2560 ,created_by
2561 ,last_update_date
2562 ,last_updated_by
2563 ,last_update_login)
2564 SELECT
2565 p_application_id
2566 ,p_staging_context_code
2567 ,event_class_code
2568 ,event_type_code
2569 ,line_definition_owner_code
2570 ,line_definition_code
2571 ,accounting_line_type_code
2572 ,accounting_line_code
2573 ,description_type_code
2574 ,description_code
2575 ,active_flag
2576 ,NVL(inherit_desc_flag,'N')
2577 ,mpa_header_desc_type_code
2578 ,mpa_header_desc_code
2579 ,mpa_num_je_code
2580 ,mpa_gl_dates_code
2581 ,mpa_proration_code
2582 ,1
2583 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2584 ,fnd_load_util.owner_id(owner)
2585 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2586 ,fnd_load_util.owner_id(owner)
2587 ,0
2588 FROM xla_aad_loader_defns_t
2589 WHERE table_name = 'XLA_LINE_DEFN_JLT_ASSGNS'
2590 AND staging_amb_context_code = p_staging_context_code;
2591
2592 IF (C_LEVEL_EVENT >= g_log_level) THEN
2593 trace(p_msg => '# insert (XLA_LINE_DEFN_JLT_ASSGNS) = '||SQL%ROWCOUNT,
2594 p_module => l_log_module,
2595 p_level => C_LEVEL_EVENT);
2596 END IF;
2597
2598 INSERT INTO xla_line_defn_adr_assgns
2599 (application_id
2600 ,amb_context_code
2601 ,event_class_code
2602 ,event_type_code
2603 ,line_definition_owner_code
2604 ,line_definition_code
2605 ,accounting_line_type_code
2606 ,accounting_line_code
2607 ,flexfield_segment_code
2608 ,adr_version_num
2609 ,segment_rule_appl_id
2610 ,segment_rule_type_code
2611 ,segment_rule_code
2612 ,inherit_adr_flag
2613 ,side_code
2614 ,object_version_number
2615 ,creation_date
2616 ,created_by
2617 ,last_update_date
2618 ,last_updated_by
2619 ,last_update_login)
2620 SELECT
2621 p_application_id
2622 ,p_staging_context_code
2623 ,event_class_code
2624 ,event_type_code
2625 ,line_definition_owner_code
2626 ,line_definition_code
2627 ,accounting_line_type_code
2628 ,accounting_line_code
2629 ,flexfield_segment_code
2630 ,NVL(adr_version_num,0)
2631 ,NVL(fap.application_id,
2632 CASE WHEN segment_rule_type_code IS NOT NULL
2633 THEN p_application_id
2634 ELSE NULL END)
2635 ,segment_rule_type_code
2636 ,segment_rule_code
2637 ,NVL(inherit_adr_flag,'N')
2638 ,NVL(side_code,'NA')
2639 ,1
2640 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2641 ,fnd_load_util.owner_id(owner)
2642 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2643 ,fnd_load_util.owner_id(owner)
2644 ,0
2645 FROM xla_aad_loader_defns_t xal
2646 ,fnd_application fap
2647 WHERE fap.application_short_name(+) = xal.segment_rule_appl_sn
2648 AND xal.table_name = 'XLA_LINE_DEFN_ADR_ASSGNS'
2649 AND xal.staging_amb_context_code = p_staging_context_code;
2650
2651 IF (C_LEVEL_EVENT >= g_log_level) THEN
2652 trace(p_msg => '# insert (XLA_LINE_DEFN_ADR_ASSGNS) = '||SQL%ROWCOUNT,
2653 p_module => l_log_module,
2654 p_level => C_LEVEL_EVENT);
2655 END IF;
2656
2657 INSERT INTO xla_line_defn_ac_assgns
2658 (application_id
2659 ,amb_context_code
2660 ,event_class_code
2661 ,event_type_code
2662 ,line_definition_owner_code
2663 ,line_definition_code
2664 ,accounting_line_type_code
2665 ,accounting_line_code
2666 ,analytical_criterion_code
2667 ,analytical_criterion_type_code
2668 ,object_version_number
2669 ,creation_date
2670 ,created_by
2671 ,last_update_date
2672 ,last_updated_by
2673 ,last_update_login)
2674 SELECT
2675 p_application_id
2676 ,p_staging_context_code
2677 ,event_class_code
2678 ,event_type_code
2679 ,line_definition_owner_code
2680 ,line_definition_code
2681 ,accounting_line_type_code
2682 ,accounting_line_code
2683 ,analytical_criterion_code
2684 ,analytical_criterion_type_code
2685 ,1
2686 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2687 ,fnd_load_util.owner_id(owner)
2688 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2689 ,fnd_load_util.owner_id(owner)
2690 ,0
2691 FROM xla_aad_loader_defns_t
2692 WHERE table_name = 'XLA_LINE_DEFN_AC_ASSGNS'
2693 AND staging_amb_context_code = p_staging_context_code;
2694
2695 IF (C_LEVEL_EVENT >= g_log_level) THEN
2696 trace(p_msg => '# insert (XLA_LINE_DEFN_AC_ASSGNS) = '||SQL%ROWCOUNT,
2697 p_module => l_log_module,
2698 p_level => C_LEVEL_EVENT);
2699 END IF;
2700
2701 INSERT INTO xla_mpa_header_ac_assgns
2702 (application_id
2703 ,amb_context_code
2707 ,line_definition_code
2704 ,event_class_code
2705 ,event_type_code
2706 ,line_definition_owner_code
2708 ,accounting_line_type_code
2709 ,accounting_line_code
2710 ,analytical_criterion_code
2711 ,analytical_criterion_type_code
2712 ,object_version_number
2713 ,creation_date
2714 ,created_by
2715 ,last_update_date
2716 ,last_updated_by
2717 ,last_update_login)
2718 SELECT
2719 p_application_id
2720 ,p_staging_context_code
2721 ,event_class_code
2722 ,event_type_code
2723 ,line_definition_owner_code
2724 ,line_definition_code
2725 ,accounting_line_type_code
2726 ,accounting_line_code
2727 ,analytical_criterion_code
2728 ,analytical_criterion_type_code
2729 ,1
2730 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2731 ,fnd_load_util.owner_id(owner)
2732 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2733 ,fnd_load_util.owner_id(owner)
2734 ,0
2735 FROM xla_aad_loader_defns_t
2736 WHERE table_name = 'XLA_MPA_HEADER_AC_ASSGNS'
2737 AND staging_amb_context_code = p_staging_context_code;
2738
2739 IF (C_LEVEL_EVENT >= g_log_level) THEN
2740 trace(p_msg => '# insert (XLA_MPA_HEADER_AC_ASSGNS) = '||SQL%ROWCOUNT,
2741 p_module => l_log_module,
2742 p_level => C_LEVEL_EVENT);
2743 END IF;
2744
2745 INSERT INTO xla_mpa_jlt_assgns
2746 (application_id
2747 ,amb_context_code
2748 ,event_class_code
2749 ,event_type_code
2750 ,line_definition_owner_code
2751 ,line_definition_code
2752 ,accounting_line_type_code
2753 ,accounting_line_code
2754 ,mpa_accounting_line_type_code
2755 ,mpa_accounting_line_code
2756 ,description_type_code
2757 ,description_code
2758 ,inherit_desc_flag
2759 ,object_version_number
2760 ,creation_date
2761 ,created_by
2762 ,last_update_date
2763 ,last_updated_by
2764 ,last_update_login)
2765 SELECT
2766 p_application_id
2767 ,p_staging_context_code
2768 ,event_class_code
2769 ,event_type_code
2770 ,line_definition_owner_code
2771 ,line_definition_code
2772 ,accounting_line_type_code
2773 ,accounting_line_code
2774 ,mpa_accounting_line_type_code
2775 ,mpa_accounting_line_code
2776 ,description_type_code
2777 ,description_code
2778 ,inherit_desc_flag
2779 ,1
2780 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2781 ,fnd_load_util.owner_id(owner)
2782 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2783 ,fnd_load_util.owner_id(owner)
2784 ,0
2785 FROM xla_aad_loader_defns_t
2786 WHERE table_name = 'XLA_MPA_JLT_ASSGNS'
2787 AND staging_amb_context_code = p_staging_context_code;
2788
2789 IF (C_LEVEL_EVENT >= g_log_level) THEN
2790 trace(p_msg => '# insert (XLA_MPA_JLT_ASSGNS) = '||SQL%ROWCOUNT,
2791 p_module => l_log_module,
2792 p_level => C_LEVEL_EVENT);
2793 END IF;
2794
2795 INSERT INTO xla_mpa_jlt_ac_assgns
2796 (application_id
2797 ,amb_context_code
2798 ,event_class_code
2799 ,event_type_code
2800 ,line_definition_owner_code
2801 ,line_definition_code
2802 ,accounting_line_type_code
2803 ,accounting_line_code
2804 ,mpa_accounting_line_type_code
2805 ,mpa_accounting_line_code
2806 ,analytical_criterion_type_code
2807 ,analytical_criterion_code
2808 ,mpa_inherit_ac_flag
2809 ,object_version_number
2810 ,creation_date
2811 ,created_by
2812 ,last_update_date
2813 ,last_updated_by
2814 ,last_update_login)
2815 SELECT
2816 p_application_id
2817 ,p_staging_context_code
2818 ,event_class_code
2819 ,event_type_code
2820 ,line_definition_owner_code
2821 ,line_definition_code
2822 ,accounting_line_type_code
2823 ,accounting_line_code
2824 ,mpa_accounting_line_type_code
2825 ,mpa_accounting_line_code
2826 ,analytical_criterion_type_code
2827 ,analytical_criterion_code
2828 ,mpa_inherit_ac_flag
2829 ,1
2830 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2831 ,fnd_load_util.owner_id(owner)
2832 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2836 WHERE table_name = 'XLA_MPA_JLT_AC_ASSGNS'
2833 ,fnd_load_util.owner_id(owner)
2834 ,0
2835 FROM xla_aad_loader_defns_t
2837 AND staging_amb_context_code = p_staging_context_code;
2838
2839 IF (C_LEVEL_EVENT >= g_log_level) THEN
2840 trace(p_msg => '# insert (XLA_MPA_JLT_AC_ASSGNS) = '||SQL%ROWCOUNT,
2841 p_module => l_log_module,
2842 p_level => C_LEVEL_EVENT);
2843 END IF;
2844
2845 INSERT INTO xla_mpa_jlt_adr_assgns
2846 (application_id
2847 ,amb_context_code
2848 ,event_class_code
2849 ,event_type_code
2850 ,line_definition_owner_code
2851 ,line_definition_code
2852 ,accounting_line_type_code
2853 ,accounting_line_code
2854 ,mpa_accounting_line_type_code
2855 ,mpa_accounting_line_code
2856 ,flexfield_segment_code
2857 ,segment_rule_type_code
2858 ,segment_rule_code
2859 ,segment_rule_appl_id
2860 ,inherit_adr_flag
2861 ,object_version_number
2862 ,creation_date
2863 ,created_by
2864 ,last_update_date
2865 ,last_updated_by
2866 ,last_update_login)
2867 SELECT
2868 p_application_id
2869 ,p_staging_context_code
2870 ,xal.event_class_code
2871 ,xal.event_type_code
2872 ,xal.line_definition_owner_code
2873 ,xal.line_definition_code
2874 ,xal.accounting_line_type_code
2875 ,xal.accounting_line_code
2876 ,xal.mpa_accounting_line_type_code
2877 ,xal.mpa_accounting_line_code
2878 ,xal.flexfield_segment_code
2879 ,xal.segment_rule_type_code
2880 ,xal.segment_rule_code
2881 ,fap.application_id
2882 ,xal.inherit_adr_flag
2883 ,1
2884 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2885 ,fnd_load_util.owner_id(owner)
2886 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2887 ,fnd_load_util.owner_id(owner)
2888 ,0
2889 FROM xla_aad_loader_defns_t xal
2890 , fnd_application fap
2891 WHERE fap.application_short_name(+) = xal.segment_rule_appl_sn
2892 AND table_name = 'XLA_MPA_JLT_ADR_ASSGNS'
2893 AND staging_amb_context_code = p_staging_context_code;
2894
2895 IF (C_LEVEL_EVENT >= g_log_level) THEN
2896 trace(p_msg => '# insert (XLA_MPA_JLT_ADR_ASSGNS) = '||SQL%ROWCOUNT,
2897 p_module => l_log_module,
2898 p_level => C_LEVEL_EVENT);
2899 END IF;
2900
2901 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2902 trace(p_msg => 'END of procedure populate_jlds',
2903 p_module => l_log_module,
2904 p_level => C_LEVEL_PROCEDURE);
2905 END IF;
2906
2907 EXCEPTION
2908 WHEN OTHERS THEN
2909 xla_aad_loader_util_pvt.stack_error
2910 (p_appli_s_name => 'XLA'
2911 ,p_msg_name => 'XLA_COMMON_ERROR'
2912 ,p_token_1 => 'LOCATION'
2913 ,p_value_1 => 'xla_aad_upload_pvt.populate_jlds'
2914 ,p_token_2 => 'ERROR'
2915 ,p_value_2 => 'unhandled exception');
2916 RAISE;
2917
2918 END populate_jlds;
2919
2920 --=============================================================================
2921 --
2922 -- Name: populate_aads
2923 -- Description: This API populates the AADs data from the AAD Loader
2924 -- interface table to the different AMB tables
2925 --
2926 --=============================================================================
2927 PROCEDURE populate_aads
2928 (p_application_id IN INTEGER
2929 ,p_amb_context_code IN VARCHAR2
2933 BEGIN
2930 ,p_staging_context_code IN VARCHAR2)
2931 IS
2932 l_log_module VARCHAR2(240);
2934 IF g_log_enabled THEN
2935 l_log_module := C_DEFAULT_MODULE||'.populate_aads';
2936 END IF;
2937
2938 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2939 trace(p_msg => 'BEGIN of procedure populate_aads',
2940 p_module => l_log_module,
2941 p_level => C_LEVEL_PROCEDURE);
2942 END IF;
2943
2944 INSERT INTO xla_product_rules_b
2945 (application_id
2946 ,amb_context_code
2947 ,product_rule_type_code
2948 ,product_rule_code
2949 ,transaction_coa_id
2950 ,accounting_coa_id
2951 ,enabled_flag
2952 ,product_rule_version
2953 ,compile_status_code
2954 ,locking_status_flag
2955 ,product_rule_hash_id
2956 ,version_num
2957 ,updated_flag
2958 ,creation_date
2959 ,created_by
2960 ,last_update_date
2961 ,last_updated_by
2962 ,last_update_login)
2963 SELECT
2964 p_application_id
2965 ,p_staging_context_code
2966 ,xal.product_rule_type_code
2967 ,xal.product_rule_code
2968 ,fift.id_flex_num
2969 ,fifa.id_flex_num
2970 ,xal.enabled_flag
2971 ,xal.product_rule_version
2972 ,'N'
2973 ,xal.locking_status_flag
2974 ,xpr.product_rule_hash_id
2975 ,NVL(xal.version_num,1)
2976 ,'N'
2977 ,nvl(to_date(xal.orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2978 ,fnd_load_util.owner_id(xal.owner)
2979 ,nvl(to_date(xal.orig_last_update_date, 'YYYY/MM/DD'), sysdate)
2980 ,fnd_load_util.owner_id(xal.owner)
2981 ,0
2982 FROM xla_aad_loader_defns_t xal
2983 ,xla_product_rules_b xpr
2984 ,fnd_id_flex_structures fift
2985 ,fnd_id_flex_structures fifa
2986 WHERE fift.application_id(+) = 101
2987 AND fift.id_flex_code(+) = 'GL#'
2988 AND fift.id_flex_structure_code(+) = xal.trans_coa_id_flex_struct_code
2989 AND fifa.application_id(+) = 101
2990 AND fifa.id_flex_code(+) = 'GL#'
2991 AND fifa.id_flex_structure_code(+) = xal.acct_coa_id_flex_struct_code
2992 AND xpr.application_id(+) = p_application_id
2993 AND xpr.amb_context_code(+) = p_amb_context_code
2994 AND xpr.product_rule_type_code(+) = xal.product_rule_type_code
2995 AND xpr.product_rule_code(+) = xal.product_rule_code
2996 AND xal.table_name = 'XLA_PRODUCT_RULES'
2997 AND xal.staging_amb_context_code = p_staging_context_code;
2998
2999 IF (C_LEVEL_EVENT >= g_log_level) THEN
3000 trace(p_msg => '# insert (XLA_PRODUCT_RULES_B) = '||SQL%ROWCOUNT,
3001 p_module => l_log_module,
3002 p_level => C_LEVEL_EVENT);
3003 END IF;
3004
3005 INSERT INTO xla_product_rules_tl
3006 (application_id
3007 ,amb_context_code
3008 ,product_rule_type_code
3009 ,product_rule_code
3010 ,language
3011 ,name
3012 ,description
3013 ,source_lang
3014 ,creation_date
3015 ,created_by
3016 ,last_update_date
3017 ,last_updated_by
3018 ,last_update_login)
3019 SELECT
3020 p_application_id
3021 ,p_staging_context_code
3022 ,product_rule_type_code
3023 ,product_rule_code
3024 ,fl.language_code
3025 ,name
3026 ,description
3027 ,USERENV('LANG')
3028 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
3029 ,fnd_load_util.owner_id(owner)
3030 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
3031 ,fnd_load_util.owner_id(owner)
3032 ,0
3033 FROM xla_aad_loader_defns_t xal
3034 ,fnd_languages fl
3038
3035 WHERE xal.table_name = 'XLA_PRODUCT_RULES'
3036 AND xal.staging_amb_context_code = p_staging_context_code
3037 AND fl.installed_flag IN ('I', 'B');
3039 IF (C_LEVEL_EVENT >= g_log_level) THEN
3040 trace(p_msg => '# insert (XLA_PRODUCT_RULES_TL) = '||SQL%ROWCOUNT,
3041 p_module => l_log_module,
3042 p_level => C_LEVEL_EVENT);
3043 END IF;
3044
3045 INSERT INTO xla_prod_acct_headers
3046 (application_id
3047 ,amb_context_code
3048 ,product_rule_type_code
3049 ,product_rule_code
3050 ,entity_code
3051 ,event_class_code
3052 ,event_type_code
3053 ,description_type_code
3054 ,description_code
3055 ,accounting_required_flag
3056 ,locking_status_flag
3057 ,validation_status_code
3058 ,creation_date
3059 ,created_by
3060 ,last_update_date
3061 ,last_updated_by
3062 ,last_update_login)
3063 SELECT
3064 p_application_id
3065 ,p_staging_context_code
3066 ,product_rule_type_code
3067 ,product_rule_code
3068 ,entity_code
3069 ,event_class_code
3070 ,event_type_code
3071 ,description_type_code
3072 ,description_code
3073 ,accounting_required_flag
3074 ,locking_status_flag
3075 ,'N'
3076 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
3077 ,fnd_load_util.owner_id(owner)
3078 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
3079 ,fnd_load_util.owner_id(owner)
3080 ,0
3081 FROM xla_aad_loader_defns_t
3082 WHERE table_name = 'XLA_PROD_ACCT_HEADERS'
3083 AND staging_amb_context_code = p_staging_context_code;
3084
3085 IF (C_LEVEL_EVENT >= g_log_level) THEN
3086 trace(p_msg => '# insert (XLA_PROD_ACCT_HEADERS) = '||SQL%ROWCOUNT,
3087 p_module => l_log_module,
3088 p_level => C_LEVEL_EVENT);
3089 END IF;
3090
3091 INSERT INTO xla_aad_hdr_acct_attrs
3092 (application_id
3093 ,amb_context_code
3094 ,product_rule_type_code
3095 ,product_rule_code
3096 ,event_class_code
3097 ,event_type_code
3098 ,accounting_attribute_code
3099 ,source_application_id
3100 ,source_type_code
3101 ,source_code
3102 ,event_class_default_flag
3103 ,creation_date
3104 ,created_by
3105 ,last_update_date
3106 ,last_updated_by
3107 ,last_update_login)
3108 SELECT
3109 p_application_id
3110 ,p_staging_context_code
3111 ,product_rule_type_code
3112 ,product_rule_code
3113 ,event_class_code
3114 ,event_type_code
3115 ,accounting_attribute_code
3116 ,fap.application_id
3117 ,source_type_code
3118 ,source_code
3119 ,event_class_default_flag
3120 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
3121 ,fnd_load_util.owner_id(owner)
3122 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
3123 ,fnd_load_util.owner_id(owner)
3124 ,0
3125 FROM xla_aad_loader_defns_t xal
3126 ,fnd_application fap
3127 WHERE fap.application_short_name(+) = xal.source_app_short_name
3128 AND table_name = 'XLA_AAD_HDR_ACCT_ATTRS'
3129 AND staging_amb_context_code = p_staging_context_code;
3130
3131 IF (C_LEVEL_EVENT >= g_log_level) THEN
3132 trace(p_msg => '# insert (XLA_AAD_HDR_ACCT_ATTRS) = '||SQL%ROWCOUNT,
3133 p_module => l_log_module,
3134 p_level => C_LEVEL_EVENT);
3135 END IF;
3136
3137 INSERT INTO xla_aad_line_defn_assgns
3138 (application_id
3139 ,amb_context_code
3140 ,product_rule_type_code
3141 ,product_rule_code
3142 ,event_class_code
3143 ,event_type_code
3144 ,line_definition_owner_code
3145 ,line_definition_code
3146 ,object_version_number
3147 ,creation_date
3148 ,created_by
3149 ,last_update_date
3150 ,last_updated_by
3151 ,last_update_login)
3152 SELECT
3153 p_application_id
3154 ,p_staging_context_code
3155 ,product_rule_type_code
3156 ,product_rule_code
3157 ,event_class_code
3158 ,event_type_code
3159 ,line_definition_owner_code
3160 ,line_definition_code
3161 ,1
3162 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
3163 ,fnd_load_util.owner_id(owner)
3164 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
3165 ,fnd_load_util.owner_id(owner)
3166 ,0
3167 FROM xla_aad_loader_defns_t
3168 WHERE table_name = 'XLA_AAD_LINE_DEFN_ASSGNS'
3169 AND staging_amb_context_code = p_staging_context_code;
3170
3171 IF (C_LEVEL_EVENT >= g_log_level) THEN
3172 trace(p_msg => '# insert (XLA_AAD_LINE_DEFN_ASSGNS) = '||SQL%ROWCOUNT,
3173 p_module => l_log_module,
3174 p_level => C_LEVEL_EVENT);
3175 END IF;
3176
3177 INSERT INTO xla_aad_header_ac_assgns
3178 (application_id
3179 ,amb_context_code
3180 ,product_rule_type_code
3181 ,product_rule_code
3182 ,event_class_code
3183 ,event_type_code
3184 ,analytical_criterion_code
3185 ,analytical_criterion_type_code
3186 ,object_version_number
3187 ,creation_date
3188 ,created_by
3189 ,last_update_date
3190 ,last_updated_by
3191 ,last_update_login)
3192 SELECT
3193 p_application_id
3194 ,p_staging_context_code
3195 ,product_rule_type_code
3196 ,product_rule_code
3197 ,event_class_code
3198 ,event_type_code
3199 ,analytical_criterion_code
3200 ,analytical_criterion_type_code
3201 ,1
3202 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
3203 ,fnd_load_util.owner_id(owner)
3204 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
3205 ,fnd_load_util.owner_id(owner)
3209 AND staging_amb_context_code = p_staging_context_code;
3206 ,0
3207 FROM xla_aad_loader_defns_t
3208 WHERE table_name = 'XLA_AAD_HEADER_AC_ASSGNS'
3210
3211 IF (C_LEVEL_EVENT >= g_log_level) THEN
3212 trace(p_msg => '# insert (XLA_AAD_HEADER_AC_ASSGNS) = '||SQL%ROWCOUNT,
3213 p_module => l_log_module,
3214 p_level => C_LEVEL_EVENT);
3215 END IF;
3216
3217 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3218 trace(p_msg => 'END of procedure populate_aads',
3219 p_module => l_log_module,
3220 p_level => C_LEVEL_PROCEDURE);
3221 END IF;
3222
3223 EXCEPTION
3224 WHEN OTHERS THEN
3225 xla_aad_loader_util_pvt.stack_error
3226 (p_appli_s_name => 'XLA'
3227 ,p_msg_name => 'XLA_COMMON_ERROR'
3228 ,p_token_1 => 'LOCATION'
3229 ,p_value_1 => 'xla_aad_upload_pvt.populate_aads'
3230 ,p_token_2 => 'ERROR'
3231 ,p_value_2 => 'unhandled exception');
3232 RAISE;
3233
3234 END populate_aads;
3235
3236 --=============================================================================
3237 --
3238 -- Name: populate_acctg_method
3239 -- Description: This API populates the accounting method data from the AAD Loader
3240 -- interface table to the different AMB tables
3241 --
3242 --=============================================================================
3243 PROCEDURE populate_acctg_methods
3244 (p_application_id IN INTEGER
3245 ,p_staging_context_code IN VARCHAR2)
3246 IS
3247 l_log_module VARCHAR2(240);
3248 BEGIN
3249 IF g_log_enabled THEN
3250 l_log_module := C_DEFAULT_MODULE||'.populate_acctg_methods';
3251 END IF;
3252
3253 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3254 trace(p_msg => 'BEGIN of procedure populate_acctg_methods',
3255 p_module => l_log_module,
3256 p_level => C_LEVEL_PROCEDURE);
3257 END IF;
3258
3259 INSERT INTO xla_stage_acctg_methods
3260 (staging_amb_context_code
3261 ,accounting_method_type_code
3262 ,accounting_method_code
3263 ,name
3264 ,description
3265 ,transaction_coa_id
3266 ,accounting_coa_id
3267 ,enabled_flag
3268 ,object_version_number
3269 ,creation_date
3270 ,created_by
3271 ,last_update_date
3272 ,last_updated_by
3273 ,last_update_login)
3274 SELECT
3275 p_staging_context_code
3276 ,xal.accounting_method_type_code
3277 ,xal.accounting_method_code
3278 ,xal.name
3279 ,xal.description
3280 ,fift.id_flex_num
3281 ,fifa.id_flex_num
3282 ,xal.enabled_flag
3283 ,1
3284 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
3285 ,fnd_load_util.owner_id(owner)
3286 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
3287 ,fnd_load_util.owner_id(owner)
3288 ,0
3289 FROM xla_aad_loader_defns_t xal
3290 ,fnd_id_flex_structures fift
3291 ,fnd_id_flex_structures fifa
3292 WHERE fift.application_id(+) = 101
3293 AND fift.id_flex_code(+) = 'GL#'
3294 AND fift.id_flex_structure_code(+) = trans_coa_id_flex_struct_code
3295 AND fifa.application_id(+) = 101
3296 AND fifa.id_flex_code(+) = 'GL#'
3297 AND fifa.id_flex_structure_code(+) = acct_coa_id_flex_struct_code
3298 AND table_name = 'XLA_STAGE_ACCTG_METHODS'
3299 AND staging_amb_context_code = p_staging_context_code;
3300
3301 IF (C_LEVEL_EVENT >= g_log_level) THEN
3302 trace(p_msg => '# insert (XLA_STAGING_ACCTG_METHODS) = '||SQL%ROWCOUNT,
3303 p_module => l_log_module,
3304 p_level => C_LEVEL_EVENT);
3305 END IF;
3306
3307 INSERT INTO xla_acctg_method_rules
3308 (amb_context_code
3309 ,accounting_method_type_code
3310 ,accounting_method_code
3311 ,acctg_method_rule_id
3312 ,application_id
3313 ,product_rule_type_code
3314 ,product_rule_code
3315 ,start_date_active
3316 ,end_date_active
3317 ,creation_date
3318 ,created_by
3319 ,last_update_date
3320 ,last_updated_by
3321 ,last_update_login)
3322 SELECT
3323 p_staging_context_code
3324 ,accounting_method_type_code
3325 ,accounting_method_code
3326 ,xla_acctg_method_rules_s.nextval
3327 ,p_application_id
3328 ,product_rule_type_code
3329 ,product_rule_code
3330 ,start_date_active
3331 ,end_date_active
3332 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
3333 ,fnd_load_util.owner_id(owner)
3334 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
3335 ,fnd_load_util.owner_id(owner)
3336 ,0
3337 FROM xla_aad_loader_defns_t
3338 WHERE table_name = 'XLA_ACCTG_METHOD_RULES'
3339 AND staging_amb_context_code = p_staging_context_code;
3340
3341 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3342 trace(p_msg => 'END of procedure populate_acctg_methods',
3343 p_module => l_log_module,
3344 p_level => C_LEVEL_PROCEDURE);
3345 END IF;
3346
3347 EXCEPTION
3348 WHEN OTHERS THEN
3349 xla_aad_loader_util_pvt.stack_error
3350 (p_appli_s_name => 'XLA'
3351 ,p_msg_name => 'XLA_COMMON_ERROR'
3352 ,p_token_1 => 'LOCATION'
3353 ,p_value_1 => 'xla_aad_upload_pvt.populate_acctg_methods'
3354 ,p_token_2 => 'ERROR'
3355 ,p_value_2 => 'unhandled exception');
3356 RAISE;
3357
3358 END populate_acctg_methods;
3359
3360 --=============================================================================
3361 --
3362 -- Name: populate_history
3366 --=============================================================================
3363 -- Description: This API populates the history data from the AAD Loader
3364 -- interface table to the different AMB tables
3365 --
3367 PROCEDURE populate_history
3368 (p_staging_context_code IN VARCHAR2)
3369 IS
3370 l_log_module VARCHAR2(240);
3371 BEGIN
3372 IF g_log_enabled THEN
3373 l_log_module := C_DEFAULT_MODULE||'.populate_history';
3374 END IF;
3375
3376 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3377 trace(p_msg => 'BEGIN of procedure populate_history',
3378 p_module => l_log_module,
3379 p_level => C_LEVEL_PROCEDURE);
3380 END IF;
3381
3382 INSERT INTO xla_staging_components_h
3383 (staging_amb_context_code
3384 ,component_type_code
3385 ,component_owner_code
3386 ,component_code
3387 ,version_num
3388 ,base_version_num
3389 ,application_id
3390 ,product_rule_version
3391 ,version_comment
3392 ,leapfrog_flag
3393 ,object_version_number
3394 ,creation_date
3395 ,created_by
3396 ,last_update_date
3397 ,last_updated_by
3398 ,last_update_login)
3399 SELECT
3400 p_staging_context_code
3401 ,component_type_code
3402 ,component_owner_code
3403 ,component_code
3404 ,version_num
3405 ,base_version_num
3406 ,NVL(fap.application_id,-1)
3407 ,product_rule_version
3408 ,version_comment
3409 ,leapfrog_flag
3410 ,1
3411 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
3412 ,fnd_load_util.owner_id(owner)
3413 ,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
3414 ,fnd_load_util.owner_id(owner)
3415 ,0
3416 FROM xla_aad_loader_defns_t xal
3417 ,fnd_application fap
3418 WHERE fap.application_short_name(+) = xal.application_short_name
3419 AND table_name = 'XLA_STAGING_COMPONENTS_H'
3420 AND staging_amb_context_code = p_staging_context_code;
3421
3422 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3423 trace(p_msg => '# row populated in XLA_STAGING_COMPONENTS_H = '||SQL%ROWCOUNT,
3424 p_module => l_log_module,
3425 p_level => C_LEVEL_PROCEDURE);
3426 END IF;
3427
3428 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3429 trace(p_msg => 'END of procedure populate_history',
3430 p_module => l_log_module,
3431 p_level => C_LEVEL_PROCEDURE);
3432 END IF;
3433
3434 EXCEPTION
3435 WHEN OTHERS THEN
3436 xla_aad_loader_util_pvt.stack_error
3437 (p_appli_s_name => 'XLA'
3438 ,p_msg_name => 'XLA_COMMON_ERROR'
3439 ,p_token_1 => 'LOCATION'
3440 ,p_value_1 => 'xla_aad_upload_pvt.populate_history'
3441 ,p_token_2 => 'ERROR'
3442 ,p_value_2 => 'unhandled exception');
3443 RAISE;
3444 END populate_history;
3445
3446 --=============================================================================
3447 --
3448 -- Name: populate_data
3449 -- Description: This API populates the data from the AAD Loader interface
3450 -- table to the different AMB tables
3451 --
3452 --=============================================================================
3453 FUNCTION populate_data
3454 (p_application_id IN INTEGER
3455 ,p_amb_context_code IN VARCHAR2
3456 ,p_staging_context_code IN VARCHAR2)
3457 RETURN VARCHAR2
3458 IS
3459 l_error_found BOOLEAN;
3460 l_log_module VARCHAR2(240);
3461 BEGIN
3462 IF g_log_enabled THEN
3463 l_log_module := C_DEFAULT_MODULE||'.populate_data';
3464 END IF;
3465
3466 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3467 trace(p_msg => 'BEGIN of procedure populate_data',
3468 p_module => l_log_module,
3469 p_level => C_LEVEL_PROCEDURE);
3470 END IF;
3471
3472 l_error_found := FALSE;
3473
3474 populate_descriptions
3475 (p_application_id => p_application_id
3476 ,p_staging_context_code => p_staging_context_code);
3477
3478 IF (populate_mapping_sets
3479 (p_application_id => p_application_id
3480 ,p_staging_context_code => p_staging_context_code) = 'WARNING') THEN
3481 l_error_found := TRUE;
3482 END IF;
3483
3484 populate_analytical_criteria
3485 (p_application_id => p_application_id
3486 ,p_staging_context_code => p_staging_context_code);
3487
3488 IF (populate_adrs(p_application_id => p_application_id
3489 ,p_staging_context_code => p_staging_context_code) = 'WARNING') THEN
3490 l_error_found := TRUE;
3491 END IF;
3492
3493 populate_journal_line_types
3494 (p_application_id => p_application_id
3495 ,p_staging_context_code => p_staging_context_code);
3496
3497 populate_jlds
3498 (p_application_id => p_application_id
3499 ,p_staging_context_code => p_staging_context_code);
3500
3501 populate_aads
3502 (p_application_id => p_application_id
3503 ,p_amb_context_code => p_amb_context_code
3504 ,p_staging_context_code => p_staging_context_code);
3505
3506 populate_acctg_methods
3507 (p_application_id => p_application_id
3508 ,p_staging_context_code => p_staging_context_code);
3509
3510 populate_history(p_staging_context_code => p_staging_context_code);
3511
3512 IF (l_error_found) THEN
3513 RAISE FND_API.G_EXC_ERROR;
3514 END IF;
3515
3516 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3520 END IF;
3517 trace(p_msg => 'END of procedure populate_data',
3518 p_module => l_log_module,
3519 p_level => C_LEVEL_PROCEDURE);
3521
3522 RETURN 'SUCCESS';
3523 EXCEPTION
3524 WHEN FND_API.G_EXC_ERROR THEN
3525 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3526 trace(p_msg => 'END of procedure populate_data: ERROR',
3527 p_module => l_log_module,
3528 p_level => C_LEVEL_PROCEDURE);
3529 END IF;
3530
3531 RETURN 'WARNING';
3532
3533 WHEN OTHERS THEN
3534 xla_aad_loader_util_pvt.stack_error
3535 (p_appli_s_name => 'XLA'
3536 ,p_msg_name => 'XLA_COMMON_ERROR'
3537 ,p_token_1 => 'LOCATION'
3538 ,p_value_1 => 'xla_aad_upload_pvt.populate_data'
3539 ,p_token_2 => 'ERROR'
3540 ,p_value_2 => 'unhandled exception');
3541 RAISE;
3542
3543 END populate_data;
3544
3545
3546
3547 --=============================================================================
3548 --
3549 -- Name: post_upload
3550 -- Description: This API populates the data from the AAD Loader interface
3551 -- table to the different AMB tables
3552 --
3553 --=============================================================================
3554 FUNCTION post_upload
3555 (p_application_id IN INTEGER
3556 ,p_amb_context_code IN VARCHAR2
3557 ,p_staging_context_code IN VARCHAR2)
3558 RETURN VARCHAR2
3559 IS
3560 l_upload_status VARCHAR2(30);
3561 l_error_found BOOLEAN;
3562 l_log_module VARCHAR2(240);
3563 BEGIN
3564 IF g_log_enabled THEN
3565 l_log_module := C_DEFAULT_MODULE||'.post_upload';
3566 END IF;
3567
3568 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3569 trace(p_msg => 'BEGIN of function post_upload',
3570 p_module => l_log_module,
3571 p_level => C_LEVEL_PROCEDURE);
3572 END IF;
3573
3574 l_upload_status := validation
3575 (p_application_id => p_application_id
3576 ,p_staging_context_code => p_staging_context_code);
3577
3578 IF (l_upload_status IN ('WARNING','ERROR')) THEN
3579 RAISE FND_API.G_EXC_ERROR;
3580 END IF;
3581
3582 l_upload_status := populate_data
3583 (p_application_id => p_application_id
3584 ,p_amb_context_code => p_amb_context_code
3585 ,p_staging_context_code => p_staging_context_code);
3586
3587 IF (l_upload_status IN ('WARNING','ERROR')) THEN
3588 RAISE FND_API.G_EXC_ERROR;
3589 END IF;
3590
3591 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3592 trace(p_msg => 'END of function post_upload',
3593 p_module => l_log_module,
3594 p_level => C_LEVEL_PROCEDURE);
3595 END IF;
3596
3597 RETURN 'SUCCESS';
3598 EXCEPTION
3599 WHEN FND_API.G_EXC_ERROR THEN
3600 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3601 trace(p_msg => 'END of function post_upload: ERROR',
3602 p_module => l_log_module,
3603 p_level => C_LEVEL_PROCEDURE);
3604 END IF;
3605
3606 RETURN l_upload_status;
3607
3608 WHEN OTHERS THEN
3609 xla_aad_loader_util_pvt.stack_error
3610 (p_appli_s_name => 'XLA'
3611 ,p_msg_name => 'XLA_COMMON_ERROR'
3612 ,p_token_1 => 'LOCATION'
3613 ,p_value_1 => 'xla_aad_upload_pvt.post_upload'
3614 ,p_token_2 => 'ERROR'
3615 ,p_value_2 => 'unhandled exception');
3616 RETURN 'ERROR';
3617
3618 END post_upload;
3619
3620
3621 --=============================================================================
3622 --
3623 --
3624 --
3625 --
3626 --
3627 -- *********** public procedures and functions **********
3628 --
3629 --
3630 --
3631 --
3632 --
3633 --=============================================================================
3634
3635
3636 --=============================================================================
3637 --
3638 -- Name:
3639 -- Description:
3640 --
3641 --=============================================================================
3642 PROCEDURE upload
3643 (p_api_version IN NUMBER
3644 ,x_return_status IN OUT NOCOPY VARCHAR2
3645 ,p_application_id IN INTEGER
3646 ,p_source_pathname IN VARCHAR2
3647 ,p_amb_context_code IN VARCHAR2
3648 ,x_upload_status IN OUT NOCOPY VARCHAR2)
3649 IS
3650 l_api_name CONSTANT VARCHAR2(30) := 'upload';
3651 l_api_version CONSTANT NUMBER := 1.0;
3652 l_staging_context_code VARCHAR2(30);
3653 l_log_module VARCHAR2(240);
3654 BEGIN
3655 IF g_log_enabled THEN
3656 l_log_module := C_DEFAULT_MODULE||'.upload';
3657 END IF;
3658
3659 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3660 trace(p_msg => 'BEGIN of procedure upload',
3661 p_module => l_log_module,
3662 p_level => C_LEVEL_PROCEDURE);
3663 END IF;
3664
3665 -- Standard call to check for call compatibility.
3666 IF (NOT xla_aad_loader_util_pvt.compatible_api_call
3667 (p_current_version_number => l_api_version
3668 ,p_caller_version_number => p_api_version
3669 ,p_api_name => l_api_name
3670 ,p_pkg_name => C_DEFAULT_MODULE))
3671 THEN
3672 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3673 END IF;
3674
3675 -- Initialize variables
3676 x_return_status := FND_API.G_RET_STS_SUCCESS;
3677
3681 ,p_amb_context_code => p_amb_context_code);
3678 -- API Logic
3679 l_staging_context_code := xla_aad_loader_util_pvt.get_staging_context_code
3680 (p_application_id => p_application_id
3682
3683 x_upload_status := upload_data
3684 (p_application_id => p_application_id
3685 ,p_source_pathname => p_source_pathname
3686 ,p_staging_context_code => l_staging_context_code);
3687
3688 IF (x_upload_status IN ('ERROR','WARNING')) THEN
3689 RAISE FND_API.G_EXC_ERROR;
3690 END IF;
3691
3692 x_upload_status := post_upload
3693 (p_application_id => p_application_id
3694 ,p_amb_context_code => p_amb_context_code
3695 ,p_staging_context_code => l_staging_context_code);
3696
3697 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3698 trace(p_msg => 'END of procedure upload: x_upload_status = '||x_upload_status,
3699 p_module => l_log_module,
3700 p_level => C_LEVEL_PROCEDURE);
3701 END IF;
3702 EXCEPTION
3703 WHEN FND_API.G_EXC_ERROR THEN
3704 ROLLBACK;
3705 x_return_status := FND_API.G_RET_STS_ERROR ;
3706
3707 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3708 ROLLBACK;
3709 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3710 x_upload_status := 'ERROR';
3711
3712 WHEN OTHERS THEN
3713 ROLLBACK;
3714 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3715 x_upload_status := 'ERROR';
3716
3717 xla_aad_loader_util_pvt.stack_error
3718 (p_appli_s_name => 'XLA'
3719 ,p_msg_name => 'XLA_COMMON_ERROR'
3720 ,p_token_1 => 'LOCATION'
3721 ,p_value_1 => 'xla_aad_upload_pvt.upload'
3722 ,p_token_2 => 'ERROR'
3723 ,p_value_2 => 'unhandled exception');
3724 END upload;
3725
3726 --=============================================================================
3727 --
3728 -- Following code is executed when the package body is referenced for the first
3729 -- time
3730 --
3731 --=============================================================================
3732 BEGIN
3733 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3734 g_log_enabled := fnd_log.test
3735 (log_level => g_log_level
3736 ,module => C_DEFAULT_MODULE);
3737
3738 IF NOT g_log_enabled THEN
3739 g_log_level := C_LEVEL_LOG_DISABLED;
3740 END IF;
3741
3742 END xla_aad_upload_pvt;