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