[Home] [Help]
PACKAGE BODY: APPS.XLA_MAPPING_SETS_F_PKG
Source
1 PACKAGE BODY xla_mapping_sets_f_pkg AS
2 /* $Header: xlathmps.pkb 120.24.12010000.1 2008/07/29 10:10:13 appldev ship $ */
3 /*======================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_mapping_sets |
10 | |
11 | DESCRIPTION |
12 | Forms PL/SQL Wrapper for xla_mapping_sets |
13 | |
14 | HISTORY |
15 | Generated from XLAUTB. |
16 | |
17 +======================================================================*/
18
19 --=============================================================================
20 -- *********** Local Trace Routine **********
21 --=============================================================================
22 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
23 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
24 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
25 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
26 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
27 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
28
29 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
30 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_mapping_sets_f_pkg';
31
32 g_log_level NUMBER;
33 g_log_enabled BOOLEAN;
34
35 PROCEDURE trace
36 (p_msg IN VARCHAR2
37 ,p_module IN VARCHAR2
38 ,p_level IN NUMBER) IS
39 BEGIN
40 ----------------------------------------------------------------------------
41 -- Following is for FND log.
42 ----------------------------------------------------------------------------
43 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
44 fnd_log.message(p_level, p_module);
45 ELSIF p_level >= g_log_level THEN
46 fnd_log.string(p_level, p_module, p_msg);
47 END IF;
48
49 EXCEPTION
50 WHEN xla_exceptions_pkg.application_exception THEN
51 RAISE;
52
53 WHEN OTHERS THEN
54 xla_exceptions_pkg.raise_message
55 (p_location => 'xla_mapping_sets_f_pkg.trace');
56 END trace;
57
58
59 /*======================================================================+
60 | |
61 | Procedure insert_row |
62 | |
63 +======================================================================*/
64 PROCEDURE insert_row
65 (x_rowid IN OUT NOCOPY VARCHAR2
66 ,x_mapping_set_code IN VARCHAR2
67 ,x_amb_context_code IN VARCHAR2
68 ,x_view_application_id IN NUMBER
69 ,x_lookup_type IN VARCHAR2
70 ,x_accounting_coa_id IN NUMBER
71 ,x_value_set_id IN NUMBER
72 ,x_flexfield_assign_mode_code IN VARCHAR2
73 ,x_flexfield_segment_code IN VARCHAR2
74 ,x_enabled_flag IN VARCHAR2
75 ,x_flex_value_set_id IN NUMBER
76 ,x_name IN VARCHAR2
77 ,x_description IN VARCHAR2
78 ,x_creation_date IN DATE
79 ,x_created_by IN NUMBER
80 ,x_last_update_date IN DATE
81 ,x_last_updated_by IN NUMBER
82 ,x_last_update_login IN NUMBER)
83
84 IS
85
86 CURSOR c IS
87 SELECT rowid
88 FROM xla_mapping_sets_b
89 WHERE mapping_set_code = x_mapping_set_code
90 AND amb_context_code = x_amb_context_code
91 ;
92
93 l_log_module VARCHAR2(240);
94 BEGIN
95 IF g_log_enabled THEN
96 l_log_module := C_DEFAULT_MODULE||'.insert_row';
97 END IF;
98
99 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
100 trace(p_msg => 'BEGIN of procedure insert_row',
101 p_module => l_log_module,
102 p_level => C_LEVEL_PROCEDURE);
103 END IF;
104
105
106 INSERT INTO xla_mapping_sets_b
107 (creation_date
108 ,created_by
109 ,amb_context_code
110 ,view_application_id
111 ,lookup_type
112 ,mapping_set_code
113 ,accounting_coa_id
114 ,value_set_id
115 ,flexfield_assign_mode_code
116 ,flexfield_segment_code
117 ,enabled_flag
118 ,flex_value_set_id
119 ,updated_flag
120 ,version_num
121 ,last_update_date
122 ,last_updated_by
123 ,last_update_login)
124 VALUES
125 (x_creation_date
126 ,x_created_by
127 ,x_amb_context_code
128 ,x_view_application_id
129 ,x_lookup_type
130 ,x_mapping_set_code
131 ,x_accounting_coa_id
132 ,x_value_set_id
133 ,x_flexfield_assign_mode_code
134 ,x_flexfield_segment_code
135 ,x_enabled_flag
136 ,x_flex_value_set_id
137 ,'Y'
138 ,0
139 ,x_last_update_date
140 ,x_last_updated_by
141 ,x_last_update_login)
142 ;
143
144 INSERT INTO xla_mapping_sets_tl
145 (amb_context_code
146 ,mapping_set_code
147 ,name
148 ,description
149 ,creation_date
150 ,created_by
151 ,last_update_date
152 ,last_updated_by
153 ,last_update_login
154 ,language
155 ,source_lang)
156 SELECT
157 x_amb_context_code
158 ,x_mapping_set_code
159 ,x_name
160 ,x_description
161 ,x_creation_date
162 ,x_created_by
163 ,x_last_update_date
164 ,x_last_updated_by
165 ,x_last_update_login
166 ,l.language_code
167 ,USERENV('LANG')
168 FROM fnd_languages l
169 WHERE l.installed_flag IN ('I', 'B')
170 AND NOT EXISTS
171 (SELECT NULL
172 FROM xla_mapping_sets_tl t
173 WHERE t.mapping_set_code = x_mapping_set_code
174 AND t.amb_context_code = x_amb_context_code
175 AND t.language = l.language_code);
176
177 OPEN c;
178 FETCH c INTO x_rowid;
179
180 IF (c%NOTFOUND) THEN
181 CLOSE c;
182 RAISE NO_DATA_FOUND;
183 END IF;
184 CLOSE c;
185
186 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
187 trace(p_msg => 'END of procedure insert_row',
188 p_module => l_log_module,
189 p_level => C_LEVEL_PROCEDURE);
190 END IF;
191
192 END insert_row;
193
194 /*======================================================================+
195 | |
196 | Procedure lock_row |
197 | |
198 +======================================================================*/
199 PROCEDURE lock_row
200 (x_mapping_set_code IN VARCHAR2
201 ,x_amb_context_code IN VARCHAR2
202 ,x_view_application_id IN NUMBER
203 ,x_lookup_type IN VARCHAR2
204 ,x_accounting_coa_id IN NUMBER
205 ,x_value_set_id IN NUMBER
206 ,x_flexfield_assign_mode_code IN VARCHAR2
207 ,x_flexfield_segment_code IN VARCHAR2
208 ,x_enabled_flag IN VARCHAR2
209 ,x_flex_value_set_id IN NUMBER
210 ,x_name IN VARCHAR2
211 ,x_description IN VARCHAR2)
212
213 IS
214
215 CURSOR c IS
216 SELECT amb_context_code
217 ,view_application_id
218 ,lookup_type
219 ,accounting_coa_id
220 ,value_set_id
221 ,flexfield_assign_mode_code
222 ,flexfield_segment_code
223 ,enabled_flag
224 ,flex_value_set_id
225 FROM xla_mapping_sets_b
226 WHERE mapping_set_code = x_mapping_set_code
227 AND amb_context_code = x_amb_context_code
228 FOR UPDATE OF mapping_set_code NOWAIT;
229
230 recinfo c%ROWTYPE;
231
232 CURSOR c1 IS
233 SELECT amb_context_code
234 ,name
235 ,description
236 ,DECODE(language , USERENV('LANG'), 'Y', 'N') baselang
237 FROM xla_mapping_sets_tl
238 WHERE mapping_set_code = X_mapping_set_code
239 AND amb_context_code = X_amb_context_code
240 AND USERENV('LANG') IN (language ,source_lang)
241 FOR UPDATE OF mapping_set_code NOWAIT;
242
243 l_log_module VARCHAR2(240);
244 BEGIN
245 IF g_log_enabled THEN
246 l_log_module := C_DEFAULT_MODULE||'.lock_row';
247 END IF;
248
249 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
250 trace(p_msg => 'BEGIN of procedure lock_row',
251 p_module => l_log_module,
252 p_level => C_LEVEL_PROCEDURE);
253 END IF;
254
255
256 OPEN c;
257 FETCH c INTO recinfo;
258
259 IF (c%NOTFOUND) THEN
260 CLOSE c;
261 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
262 app_exception.raise_exception;
263 END IF;
264 CLOSE c;
265
266 IF ( ((recinfo.view_application_id = X_view_application_id)
267 OR ((recinfo.view_application_id IS NULL)
268 AND (x_view_application_id IS NULL)))
269 AND ((recinfo.lookup_type = X_lookup_type)
270 OR ((recinfo.lookup_type IS NULL)
271 AND (x_lookup_type IS NULL)))
272 AND ((recinfo.accounting_coa_id = X_accounting_coa_id)
273 OR ((recinfo.accounting_coa_id IS NULL)
274 AND (x_accounting_coa_id IS NULL)))
275 AND ((recinfo.value_set_id = X_value_set_id)
276 OR ((recinfo.value_set_id IS NULL)
277 AND (x_value_set_id IS NULL)))
278 AND (recinfo.flexfield_assign_mode_code = x_flexfield_assign_mode_code)
279 AND ((recinfo.flexfield_segment_code = X_flexfield_segment_code)
280 OR ((recinfo.flexfield_segment_code IS NULL)
281 AND (x_flexfield_segment_code IS NULL)))
282 AND ((recinfo.flex_value_set_id = X_flex_value_set_id)
283 OR ((recinfo.flex_value_set_id IS NULL)
284 AND (x_flex_value_set_id IS NULL)))
285 AND (recinfo.enabled_flag = x_enabled_flag)
286 ) THEN
287 NULL;
288 ELSE
289 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
290 app_exception.raise_exception;
291 END IF;
292
293 FOR tlinfo IN c1 LOOP
294 IF (tlinfo.baselang = 'Y') THEN
295 IF ( (tlinfo.name = X_name)
296 AND ((tlinfo.description = X_description)
297 OR ((tlinfo.description is null)
298 AND (X_description is null)))
299 ) THEN
300 NULL;
301 ELSE
302 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
303 app_exception.raise_exception;
304 END IF;
305 END IF;
306 END LOOP;
307
308
309 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
310 trace(p_msg => 'END of procedure lock_row',
311 p_module => l_log_module,
312 p_level => C_LEVEL_PROCEDURE);
313 END IF;
314
315 END lock_row;
316
317 /*======================================================================+
318 | |
319 | Procedure update_row |
320 | |
321 +======================================================================*/
322 PROCEDURE update_row
323 (x_mapping_set_code IN VARCHAR2
324 ,x_amb_context_code IN VARCHAR2
325 ,x_view_application_id IN NUMBER
326 ,x_lookup_type IN VARCHAR2
327 ,x_accounting_coa_id IN NUMBER
328 ,x_value_set_id IN NUMBER
329 ,x_flexfield_assign_mode_code IN VARCHAR2
330 ,x_flexfield_segment_code IN VARCHAR2
331 ,x_enabled_flag IN VARCHAR2
332 ,x_flex_value_set_id IN NUMBER
333 ,x_name IN VARCHAR2
334 ,x_description IN VARCHAR2
335 ,x_last_update_date IN DATE
336 ,x_last_updated_by IN NUMBER
337 ,x_last_update_login IN NUMBER)
338
339 IS
340
341 l_log_module VARCHAR2(240);
342 BEGIN
343 IF g_log_enabled THEN
344 l_log_module := C_DEFAULT_MODULE||'.update_row';
345 END IF;
346
347 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
348 trace(p_msg => 'BEGIN of procedure update_row',
349 p_module => l_log_module,
350 p_level => C_LEVEL_PROCEDURE);
351 END IF;
352
353 UPDATE xla_mapping_sets_b
354 SET
355 last_update_date = x_last_update_date
356 ,view_application_id = x_view_application_id
357 ,lookup_type = x_lookup_type
358 ,accounting_coa_id = x_accounting_coa_id
359 ,value_set_id = x_value_set_id
360 ,flexfield_assign_mode_code = x_flexfield_assign_mode_code
361 ,flexfield_segment_code = x_flexfield_segment_code
362 ,enabled_flag = x_enabled_flag
363 ,flex_value_set_id = x_flex_value_set_id
364 ,updated_flag = 'Y'
365 ,last_updated_by = x_last_updated_by
366 ,last_update_login = x_last_update_login
367 WHERE mapping_set_code = X_mapping_set_code
368 AND amb_context_code = X_amb_context_code;
369
370 IF (SQL%NOTFOUND) THEN
371 RAISE NO_DATA_FOUND;
372 END IF;
373
374 UPDATE xla_mapping_sets_tl
375 SET
376 last_update_date = x_last_update_date
377 ,name = X_name
378 ,description = X_description
379 ,last_updated_by = x_last_updated_by
380 ,last_update_login = x_last_update_login
381 ,source_lang = USERENV('LANG')
382 WHERE mapping_set_code = X_mapping_set_code
383 AND amb_context_code = X_amb_context_code
384 AND USERENV('LANG') IN (language, source_lang);
385
386 IF (SQL%NOTFOUND) THEN
387 RAISE NO_DATA_FOUND;
388 END IF;
389
390 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
391 trace(p_msg => 'END of procedure update_row',
392 p_module => l_log_module,
393 p_level => C_LEVEL_PROCEDURE);
394 END IF;
395
396 END update_row;
397
398 /*======================================================================+
399 | |
400 | Procedure delete_row |
401 | |
402 +======================================================================*/
403 PROCEDURE delete_row
404 (x_mapping_set_code IN VARCHAR2
405 ,x_amb_context_code IN VARCHAR2)
406
407 IS
408
409 l_log_module VARCHAR2(240);
410 BEGIN
411 IF g_log_enabled THEN
412 l_log_module := C_DEFAULT_MODULE||'.delete_row';
413 END IF;
414
415 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
416 trace(p_msg => 'BEGIN of procedure delete_row',
417 p_module => l_log_module,
418 p_level => C_LEVEL_PROCEDURE);
419 END IF;
420
421 DELETE FROM xla_mapping_sets_tl
422 WHERE mapping_set_code = x_mapping_set_code
423 AND amb_context_code = x_amb_context_code;
424
425
426 IF (SQL%NOTFOUND) THEN
427 RAISE NO_DATA_FOUND;
428 END IF;
429
430 DELETE FROM xla_mapping_sets_b
431 WHERE mapping_set_code = x_mapping_set_code
432 AND amb_context_code = x_amb_context_code;
433
434
435 IF (SQL%NOTFOUND) THEN
436 RAISE NO_DATA_FOUND;
437 END IF;
438
439
440 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
441 trace(p_msg => 'END of procedure delete_row',
442 p_module => l_log_module,
443 p_level => C_LEVEL_PROCEDURE);
444 END IF;
445
446 END delete_row;
447
448 /*======================================================================+
449 | |
450 | Procedure add_language |
451 | |
452 +======================================================================*/
453 PROCEDURE add_language
454
455 IS
456
457 l_log_module VARCHAR2(240);
458 BEGIN
459 IF g_log_enabled THEN
460 l_log_module := C_DEFAULT_MODULE||'.add_language';
461 END IF;
462
463 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
464 trace(p_msg => 'BEGIN of procedure add_language',
465 p_module => l_log_module,
466 p_level => C_LEVEL_PROCEDURE);
467 END IF;
468
469
470 DELETE FROM xla_mapping_sets_tl T
471 WHERE NOT EXISTS
472 (SELECT NULL
473 FROM xla_mapping_sets_b b
474 WHERE b.mapping_set_code = t.mapping_set_code
475 AND b.amb_context_code = t.amb_context_code);
476
477 UPDATE xla_mapping_sets_tl t
478 SET (name
479 ,description)
480 = (SELECT b.name
481 ,b.description
482 FROM xla_mapping_sets_tl b
483 WHERE b.mapping_set_code = t.mapping_set_code
484 AND b.amb_context_code = t.amb_context_code
485 AND b.language = t.source_lang)
486 WHERE (t.mapping_set_code
487 ,t.amb_context_code
488 ,t.language)
489 IN (SELECT subt.mapping_set_code
490 ,subt.amb_context_code
491 ,subt.language
492 FROM xla_mapping_sets_tl subb
493 ,xla_mapping_sets_tl subt
494 WHERE subb.mapping_set_code = subt.mapping_set_code
495 AND subb.amb_context_code = subt.amb_context_code
496 AND subb.language = subt.source_lang
497 AND (SUBB.name <> SUBT.name
498 OR SUBB.description <> SUBT.description
499 OR (subb.description IS NULL
500 AND subt.description IS NOT NULL)
501 OR (subb.description IS NOT NULL
502 AND subt.description IS NULL)
503 ))
504 ;
505
506 INSERT INTO xla_mapping_sets_tl
507 (amb_context_code
508 ,mapping_set_code
509 ,name
510 ,description
511 ,creation_date
512 ,created_by
513 ,last_update_date
514 ,last_updated_by
515 ,last_update_login
516 ,language
517 ,source_lang)
518 SELECT /*+ ORDERED */
519 b.amb_context_code
520 ,b.mapping_set_code
521 ,b.name
522 ,b.description
523 ,b.creation_date
524 ,b.created_by
525 ,b.last_update_date
526 ,b.last_updated_by
527 ,b.last_update_login
528 ,l.language_code
529 ,b.source_lang
530 FROM xla_mapping_sets_tl b
531 ,fnd_languages l
532 WHERE l.installed_flag IN ('I', 'B')
533 AND b.language = userenv('LANG')
534 AND NOT EXISTS
535 (SELECT NULL
536 FROM xla_mapping_sets_tl t
537 WHERE t.mapping_set_code = b.mapping_set_code
538 AND t.amb_context_code = b.amb_context_code
539 AND t.language = l.language_code);
540
541 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
542 trace(p_msg => 'END of procedure add_language',
543 p_module => l_log_module,
544 p_level => C_LEVEL_PROCEDURE);
545 END IF;
546
547 END add_language;
548
549 --=============================================================================
550 --
551 -- Name: translate_row
552 -- Description: To be used by FNDLOAD to upload a translated row
553 --
554 --=============================================================================
555 PROCEDURE translate_row
556 (p_amb_context_code IN VARCHAR2
557 ,p_mapping_set_code IN VARCHAR2
558 ,p_name IN VARCHAR2
559 ,p_description IN VARCHAR2
560 ,p_owner IN VARCHAR2
561 ,p_last_update_date IN VARCHAR2
562 ,p_custom_mode IN VARCHAR2)
563 IS
564 l_rowid ROWID;
565 l_exist VARCHAR2(1);
566 f_luby NUMBER; -- entity owner in file
567 f_ludate DATE; -- entity update date in file
568 db_luby NUMBER; -- entity owner in db
569 db_ludate DATE; -- entity update date in db
570 l_log_module VARCHAR2(240);
571 BEGIN
572
573 IF g_log_enabled THEN
574 l_log_module := C_DEFAULT_MODULE||'.translate_row';
575 END IF;
576
577 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
578 trace(p_msg => 'BEGIN of procedure translate_row',
579 p_module => l_log_module,
580 p_level => C_LEVEL_PROCEDURE);
581 END IF;
582
583 -- Translate owner to file_last_updated_by
584 f_luby := fnd_load_util.owner_id(p_owner);
585
586 -- Translate char last_update_date to date
587 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
588
589 BEGIN
590 SELECT last_updated_by, last_update_date
591 INTO db_luby, db_ludate
592 FROM xla_mapping_sets_tl
593 WHERE amb_context_code = p_amb_context_code
594 AND mapping_set_code = p_mapping_set_code
595 AND language = userenv('LANG');
596
597 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
598 db_ludate, p_custom_mode)) then
599 UPDATE xla_mapping_sets_tl
600 SET name = p_name
601 ,description = p_description
602 ,last_update_date = f_ludate
603 ,last_updated_by = f_luby
604 ,last_update_login = 0
605 ,source_lang = userenv('LANG')
606 WHERE userenv('LANG') IN (language, source_lang)
607 AND amb_context_code = p_amb_context_code
608 AND mapping_set_code = p_mapping_set_code;
609
610 END IF;
611
612
613 END;
614
615 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
616 trace(p_msg => 'END of procedure translate_row',
617 p_module => l_log_module,
618 p_level => C_LEVEL_PROCEDURE);
619 END IF;
620
621
622
623 END translate_row;
624
625
626 --=============================================================================
627 --
628 -- Following code is executed when the package body is referenced for the first
629 -- time
630 --
631 --=============================================================================
632 BEGIN
633 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
634 g_log_enabled := fnd_log.test
635 (log_level => g_log_level
636 ,module => C_DEFAULT_MODULE);
637
638 IF NOT g_log_enabled THEN
639 g_log_level := C_LEVEL_LOG_DISABLED;
640 END IF;
641
642 end xla_mapping_sets_f_PKG;