[Home] [Help]
PACKAGE BODY: APPS.XLA_ADR_INTERFACE_PKG
Source
1 PACKAGE BODY xla_adr_interface_pkg AS
2 /* $Header: xlaadrin.pkb 120.4 2012/06/18 05:55:29 vgopiset ship $ */
3 /*======================================================================+
4 | Copyright (c) 1995-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_adr_interface_pkg |
10 | |
11 | DESCRIPTION |
12 | XLA ADR interface |
13 | |
14 | HISTORY |
15 | 16-AUG-2005 Jorge Larre Created |
16 | 23-DEC-2005 Jorge Larre Fix for bug 4906683 |
17 | a) Populate xla_seg_rules_b.updated_flag with 'Y'. |
18 | b) Add code to populate xla_line_defn_assgns.accounting_line_code |
19 | 28-DEC-2005 Jorge Larre Fix for bug 4906683 |
20 | a) Populate side_code with 'NA'. |
21 | b) Populate inherit_adr_flag with 'N'. |
22 | c) Populate adr_version_num with 0. |
23 | d) Populate segment_rule_appl_id with application_id. |
24 | |
25 | 15-JUN-2012 VGOPISET added call to AD_ZD_SEED.PREPARE for 12.2 as |
26 | ADR set tables need to prepared during patching|
27 +======================================================================*/
28 --
29 -- Private types
30 --
31 TYPE t_array_VL4 IS TABLE OF VARCHAR2(4) INDEX BY BINARY_INTEGER;
32 --
33 -- Private constants
34 --
35 --maximum numbers of values retrieved at a time by BULK COLLECT statements
36 C_BULK_LIMIT CONSTANT NATURAL := 1000;
37 --
38 -- Global variables
39 --
40 g_user_id INTEGER;
41 g_login_id INTEGER;
42 g_date DATE;
43 g_prog_appl_id INTEGER;
44 g_prog_id INTEGER;
45 g_req_id INTEGER;
46
47 --=============================================================================
48 -- *********** Local Trace Routine **********
49 --=============================================================================
50 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
51 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
52 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
53 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
54 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
55 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
56 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
57 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_adr_interface_pkg';
58 g_log_level NUMBER;
59 g_log_enabled BOOLEAN;
60
61 --1-STATEMENT, 2-PROCEDURE, 3-EVENT, 4-EXCEPTION, 5-ERROR, 6-UNEXPECTED
62
63 PROCEDURE trace
64 ( p_module IN VARCHAR2
65 ,p_msg IN VARCHAR2
66 ,p_level IN NUMBER
67 ) IS
68 BEGIN
69 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
70 fnd_log.message(p_level, p_module);
71 ELSIF p_level >= g_log_level THEN
72 fnd_log.string(p_level, p_module, p_msg);
73 END IF;
74
75 EXCEPTION
76 WHEN xla_exceptions_pkg.application_exception THEN
77 RAISE;
78 WHEN OTHERS THEN
79 xla_exceptions_pkg.raise_message
80 (p_location => 'xla_adr_interface_pkg.trace');
81 END trace;
82
83 --=============================================================================
84 --
85 -- Name : XLA_ZD_PREPARE
86 -- Description: PREPARE API call for Non-FND Loaders
87 --
88 --=============================================================================
89 PROCEDURE XLA_ZD_PREPARE
90 IS
91 l_log_module VARCHAR2(240);
92 BEGIN
93
94 IF g_log_enabled THEN
95 l_log_module := C_DEFAULT_MODULE||'.XLA_ZD_PREPARE';
96 END IF;
97
98 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
99 trace(p_msg => 'BEGIN of procedure XLA_ZD_PREPARE',
100 p_module => l_log_module,
101 p_level => C_LEVEL_PROCEDURE);
102 END IF;
103
104 AD_ZD_SEED.PREPARE('XLA_SEG_RULES_B');
105 AD_ZD_SEED.PREPARE('XLA_SEG_RULES_TL');
106 AD_ZD_SEED.PREPARE('XLA_SEG_RULE_DETAILS');
107 AD_ZD_SEED.PREPARE('XLA_CONDITIONS');
108 AD_ZD_SEED.PREPARE('XLA_LINE_DEFN_ADR_ASSGNS');
109
110
111 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
112 trace(p_msg => 'END of procedure XLA_ZD_PREPARE',
113 p_module => l_log_module,
114 p_level => C_LEVEL_PROCEDURE);
115 END IF;
116 END XLA_ZD_PREPARE;
117
118 PROCEDURE upload_rules
119 IS
120 /*======================================================================+
121 | |
122 | Private Procedure |
123 | |
124 | Description |
125 | ----------- |
126 | |
127 | Pseudo-code |
128 | ----------- |
129 | |
130 +======================================================================*/
131
132 CURSOR c_xla_rules_t IS -- cursor for xla_rules_t
133 SELECT *
134 FROM xla_rules_t
135 WHERE error_value = 0
136 FOR UPDATE of error_value;
137
138 CURSOR c_xla_rule_details_t IS -- cursor for xla_rule_details_t
139 SELECT *
140 FROM xla_rule_details_t
141 WHERE error_value = 0
142 FOR UPDATE of error_value;
143
144 CURSOR c_xla_conditions_t IS -- cursor for xla_conditions_t
145 SELECT *
146 FROM xla_conditions_t
147 WHERE error_value = 0
148 FOR UPDATE of error_value;
149
150 CURSOR c_xla_line_assgns_t IS -- cursor for xla_line_assgns_t
151 SELECT *
152 FROM xla_line_assgns_t
153 WHERE error_value = 0
154 FOR UPDATE of error_value;
155
156 CURSOR c_languages IS -- cursor for installed languages
157 SELECT language_code
158 FROM fnd_languages
159 WHERE installed_flag = 'I';
160
161 l_xla_rules c_xla_rules_t%ROWTYPE;
162 l_xla_rule_details c_xla_rule_details_t%ROWTYPE;
163 l_xla_conditions c_xla_conditions_t%ROWTYPE;
164 l_xla_line_assgns c_xla_line_assgns_t%ROWTYPE;
165 l_base_language VARCHAR2(4);
166 l_installed_language t_array_vl4;
167 l_user_id INTEGER;
168 l_login_id INTEGER;
169 l_date DATE;
170 l_prog_appl_id INTEGER;
171 l_prog_id INTEGER;
172 l_req_id INTEGER;
173 l_log_module VARCHAR2 (2000);
174 l_error_code NUMBER;
175
176 BEGIN
177
178 l_user_id := xla_environment_pkg.g_usr_id;
179 l_login_id := xla_environment_pkg.g_login_id;
180 l_date := SYSDATE;
181 l_prog_appl_id := xla_environment_pkg.g_prog_appl_id;
182 l_prog_id := xla_environment_pkg.g_prog_id;
183 l_req_id := xla_environment_pkg.g_req_id;
184
185 IF g_log_enabled THEN
186 l_log_module := C_DEFAULT_MODULE||'.upload_rules';
187 END IF;
188 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
189 trace
190 (p_module => l_log_module
191 ,p_msg => 'BEGIN ' || l_log_module
192 ,p_level => C_LEVEL_PROCEDURE);
193 END IF;
194
195 -- Retrieve the base language
196 SELECT language_code
197 INTO l_base_language
198 FROM fnd_languages
199 WHERE installed_flag = 'B';
200
201 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
202 trace
203 (p_module => l_log_module
204 ,p_msg => 'Base language :'
205 || l_base_language
206 ,p_level => C_LEVEL_STATEMENT
207 );
208 END IF;
209
210 --Prepare the tables for patching
211 XLA_ZD_PREPARE;
212
213 -- Retrieve the installed languages
214 OPEN c_languages;
215 FETCH c_languages BULK COLLECT INTO l_installed_language;
216 CLOSE c_languages;
217
218 /* Missing the validation of the interface tables */
219
220 /* Upload rules into SLA tables */
221 OPEN c_xla_rules_t;
222 LOOP
223 FETCH c_xla_rules_t INTO l_xla_rules;
224 EXIT WHEN c_xla_rules_t%NOTFOUND;
225 BEGIN -- Block begins
226 -- Insert data into xla_seg_rules_b
227 INSERT INTO xla_seg_rules_b
228 (application_id
229 ,amb_context_code
230 ,segment_rule_type_code
231 ,segment_rule_code
232 ,transaction_coa_id
233 ,accounting_coa_id
234 ,flexfield_assign_mode_code
235 ,flexfield_segment_code
236 ,enabled_flag
237 ,creation_date
238 ,created_by
239 ,last_update_date
240 ,last_updated_by
241 ,last_update_login
242 ,flex_value_set_id
243 ,version_num
244 ,updated_flag
245 )
246 VALUES
247 (l_xla_rules.application_id
248 ,l_xla_rules.amb_context_code
249 ,l_xla_rules.segment_rule_type_code
250 ,UPPER(l_xla_rules.segment_rule_code)
251 ,l_xla_rules.transaction_coa_id
252 ,l_xla_rules.accounting_coa_id
253 ,l_xla_rules.flexfield_assign_mode_code
254 ,l_xla_rules.flexfield_segment_code
255 ,l_xla_rules.enabled_flag
256 ,l_date
257 ,l_user_id
258 ,l_date
259 ,l_user_id
260 ,l_login_id
261 ,NULL
262 ,0
263 ,'Y'
264 );
265
266 -- Insert data in xla_seg_rules_tl, the base language
267 INSERT INTO xla_seg_rules_tl
268 (last_update_login
269 ,amb_context_code
270 ,last_update_date
271 ,last_updated_by
272 ,created_by
273 ,application_id
274 ,segment_rule_type_code
275 ,segment_rule_code
276 ,name
277 ,description
278 ,creation_date
279 ,language
280 ,source_lang
281 )
282 VALUES
283 (l_login_id
284 ,l_xla_rules.amb_context_code
285 ,l_date
286 ,l_user_id
287 ,l_user_id
288 ,l_xla_rules.application_id
289 ,l_xla_rules.segment_rule_type_code
290 ,l_xla_rules.segment_rule_code
291 ,l_xla_rules.name
292 ,l_xla_rules.description
293 ,l_date
294 ,l_base_language
295 ,l_base_language
296 );
297
298 -- Insert data in xla_seg_rules_tl, the additional languages, if any
299 IF l_installed_language.COUNT > 0 THEN
300 FOR Idx IN l_installed_language.FIRST .. l_installed_language.LAST LOOP
301 INSERT INTO xla_seg_rules_tl
302 (last_update_login
303 ,amb_context_code
304 ,last_update_date
305 ,last_updated_by
306 ,created_by
307 ,application_id
308 ,segment_rule_type_code
309 ,segment_rule_code
310 ,name
311 ,description
312 ,creation_date
313 ,language
314 ,source_lang
315 )
316 VALUES
317 (l_login_id
318 ,l_xla_rules.amb_context_code
319 ,l_date
320 ,l_user_id
321 ,l_user_id
322 ,l_xla_rules.application_id
323 ,l_xla_rules.segment_rule_type_code
324 ,l_xla_rules.segment_rule_code
325 ,l_xla_rules.name
326 ,l_xla_rules.description
327 ,l_date
328 ,l_installed_language(Idx)
329 ,l_base_language
330 );
331 END LOOP;
332 END IF;
333 -- Mark the row in the interface table as been processed
334 UPDATE xla_rules_t
335 SET error_value = 1 -- The row has been processed with no error
336 WHERE CURRENT OF c_xla_rules_t;
337 EXCEPTION
338 WHEN OTHERS THEN
339 l_error_code := SQLCODE;
340 UPDATE xla_rules_t
341 SET error_value = l_error_code -- The row has been processed with errors
342 WHERE CURRENT OF c_xla_rules_t;
343 END; -- Block ends
344 END LOOP;
345 CLOSE c_xla_rules_t;
346
347 OPEN c_xla_rule_details_t;
348 LOOP
349 FETCH c_xla_rule_details_t into l_xla_rule_details;
350 EXIT WHEN c_xla_rule_details_t%NOTFOUND;
351
352 BEGIN -- Block begins
353 -- Insert data into xla_seg_rule_details
354 INSERT INTO xla_seg_rule_details
355 (segment_rule_detail_id
356 ,application_id
357 ,amb_context_code
358 ,segment_rule_type_code
359 ,segment_rule_code
360 ,user_sequence
361 ,value_type_code
362 ,value_source_application_id
363 ,value_source_type_code
364 ,value_source_code
365 ,value_constant
366 ,value_code_combination_id
367 ,value_mapping_set_code
368 ,value_flexfield_segment_code
369 ,input_source_application_id
370 ,input_source_type_code
371 ,input_source_code
372 ,creation_date
373 ,created_by
374 ,last_update_date
375 ,last_updated_by
376 ,last_update_login
377 ,value_segment_rule_appl_id
378 ,value_segment_rule_type_code
379 ,value_segment_rule_code
380 ,value_adr_version_num
381 )
382 VALUES
383 (l_xla_rule_details.segment_rule_detail_id
384 ,l_xla_rule_details.application_id
385 ,l_xla_rule_details.amb_context_code
386 ,l_xla_rule_details.segment_rule_type_code
387 ,UPPER(l_xla_rule_details.segment_rule_code)
388 ,l_xla_rule_details.user_sequence
389 ,l_xla_rule_details.value_type_code
390 ,l_xla_rule_details.value_source_application_id
391 ,l_xla_rule_details.value_source_type_code
392 ,l_xla_rule_details.value_source_code
393 ,l_xla_rule_details.value_constant
394 ,l_xla_rule_details.value_code_combination_id
395 ,l_xla_rule_details.value_mapping_set_code
396 ,l_xla_rule_details.value_flexfield_segment_code
397 ,l_xla_rule_details.input_source_application_id
398 ,l_xla_rule_details.input_source_type_code
399 ,l_xla_rule_details.input_source_code
400 ,l_date
401 ,l_user_id
402 ,l_date
403 ,l_user_id
404 ,l_login_id
405 ,l_xla_rule_details.value_segment_rule_appl_id
406 ,l_xla_rule_details.value_segment_rule_type_code
407 ,l_xla_rule_details.value_segment_rule_code
408 ,l_xla_rule_details.value_adr_version_num
409 );
410 -- Mark the row in the interface table as been processed
411 UPDATE xla_rule_details_t
412 SET error_value = 1 -- The row has been processed with no error
413 WHERE CURRENT OF c_xla_rule_details_t;
414 EXCEPTION
415 WHEN OTHERS THEN
416 l_error_code := SQLCODE;
417 UPDATE xla_rule_details_t
418 SET error_value = l_error_code -- The row has been processed with errors
419 WHERE CURRENT OF c_xla_rule_details_t;
420 END; -- Block ends
421 END LOOP;
422 CLOSE c_xla_rule_details_t;
423
424 OPEN c_xla_conditions_t;
425 LOOP
426 FETCH c_xla_conditions_t INTO l_xla_conditions;
427 EXIT WHEN c_xla_conditions_t%NOTFOUND;
428
429 BEGIN -- Block begins
430 -- Insert data into xla_conditions
431 INSERT INTO xla_conditions
432 (condition_id
433 ,user_sequence
434 ,application_id
435 ,amb_context_code
436 ,entity_code
437 ,event_class_code
438 ,accounting_line_type_code
439 ,accounting_line_code
440 ,segment_rule_detail_id
441 ,description_prio_id
442 ,bracket_left_code
443 ,bracket_right_code
444 ,value_type_code
445 ,source_application_id
446 ,source_type_code
447 ,source_code
448 ,flexfield_segment_code
449 ,value_flexfield_segment_code
450 ,value_source_application_id
451 ,value_source_type_code
452 ,value_source_code
453 ,value_constant
454 ,line_operator_code
455 ,logical_operator_code
456 ,creation_date
457 ,created_by
458 ,last_update_date
459 ,last_updated_by
460 ,last_update_login
461 ,independent_value_constant
462 )
463 VALUES
464 (l_xla_conditions.condition_id
465 ,l_xla_conditions.user_sequence
466 ,l_xla_conditions.application_id
467 ,l_xla_conditions.amb_context_code
468 ,NULL
469 ,NULL
470 ,NULL
471 ,NULL
472 ,l_xla_conditions.segment_rule_detail_id
473 ,NULL
474 ,l_xla_conditions.bracket_left_code
475 ,l_xla_conditions.bracket_right_code
476 ,l_xla_conditions.value_type_code
477 ,l_xla_conditions.source_application_id
478 ,l_xla_conditions.source_type_code
479 ,l_xla_conditions.source_code
480 ,l_xla_conditions.flexfield_segment_code
481 ,l_xla_conditions.value_flexfield_segment_code
482 ,l_xla_conditions.value_source_application_id
483 ,l_xla_conditions.value_source_type_code
484 ,l_xla_conditions.value_source_code
485 ,l_xla_conditions.value_constant
486 ,l_xla_conditions.line_operator_code
487 ,l_xla_conditions.logical_operator_code
488 ,l_date
489 ,l_user_id
490 ,l_date
491 ,l_user_id
492 ,l_login_id
493 ,l_xla_conditions.independent_value_constant
494 );
495 -- Mark the row in the interface table as been processed
496 UPDATE xla_conditions_t
497 SET error_value = 1 -- The row has been processed with no error
498 WHERE CURRENT OF c_xla_conditions_t;
499 EXCEPTION
500 WHEN OTHERS THEN
501 l_error_code := SQLCODE;
502 UPDATE xla_conditions_t
503 SET error_value = l_error_code -- The row has been processed with errors
504 WHERE CURRENT OF c_xla_conditions_t;
505 END; -- Block ends
506 END LOOP;
507 CLOSE c_xla_conditions_t;
508
509 OPEN c_xla_line_assgns_t;
510 LOOP
511 FETCH c_xla_line_assgns_t INTO l_xla_line_assgns;
512 EXIT WHEN c_xla_line_assgns_t%NOTFOUND;
513
514 BEGIN -- Block begins
515 -- Insert data into xla_line_defn_adr_assgns
516 INSERT INTO xla_line_defn_adr_assgns
517 (amb_context_code
518 ,application_id
519 ,event_class_code
520 ,event_type_code
521 ,line_definition_owner_code
522 ,line_definition_code
523 ,accounting_line_type_code
524 ,accounting_line_code
525 ,flexfield_segment_code
526 ,segment_rule_type_code
527 ,segment_rule_code
528 ,object_version_number
529 ,creation_date
530 ,created_by
531 ,last_update_date
532 ,last_updated_by
533 ,last_update_login
534 ,side_code
535 ,inherit_adr_flag
536 ,segment_rule_appl_id
537 ,adr_version_num
538 )
539 VALUES
540 (l_xla_line_assgns.amb_context_code
541 ,l_xla_line_assgns.application_id
542 ,l_xla_line_assgns.event_class_code
543 ,l_xla_line_assgns.event_type_code
544 ,l_xla_line_assgns.line_definition_owner_code
545 ,l_xla_line_assgns.line_definition_code
546 ,l_xla_line_assgns.accounting_line_type_code
547 ,l_xla_line_assgns.accounting_line_code
548 ,l_xla_line_assgns.flexfield_segment_code
549 ,l_xla_line_assgns.segment_rule_type_code
550 ,l_xla_line_assgns.segment_rule_code
551 ,1
552 ,l_date
553 ,l_user_id
554 ,l_date
555 ,l_user_id
556 ,l_login_id
557 ,'NA'
558 ,'N'
559 ,l_xla_line_assgns.application_id
560 ,0
561 );
562 -- Mark the row in the interface table as been processed
563 UPDATE xla_line_assgns_t
564 SET error_value = 1 -- The row has been processed with no error
565 WHERE CURRENT OF c_xla_line_assgns_t;
566 EXCEPTION
567 WHEN OTHERS THEN
568 l_error_code := SQLCODE;
569 UPDATE xla_line_assgns_t
570 SET error_value = l_error_code -- The row has been processed with errors
571 WHERE CURRENT OF c_xla_line_assgns_t;
572 END; -- Block ends
573 END LOOP;
574 CLOSE c_xla_line_assgns_t;
575
576 --END LOOP;
577 --CLOSE c_xla_rules_t;
578
579 /* handle_errors; */
580
581 EXCEPTION
582 WHEN xla_exceptions_pkg.application_exception THEN
583 RAISE;
584 WHEN OTHERS THEN
585 xla_exceptions_pkg.raise_message
586 (p_location => 'xla_adr_interface_pkg.upload_rules');
587 END upload_rules;
588
589 END xla_adr_interface_pkg;