[Home] [Help]
PACKAGE BODY: APPS.XLA_SOURCES_F_PKG
Source
1 PACKAGE BODY xla_sources_f_pkg AS
2 /* $Header: xlathsou.pkb 120.25.12010000.1 2008/07/29 10:10:26 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_sources |
10 | |
11 | DESCRIPTION |
12 | Forms PL/SQL Wrapper for xla_sources |
13 | |
14 | HISTORY |
15 | Generated from XLAUTB. |
16 | |
17 +======================================================================*/
18 -- Constants
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 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
27 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_sources_f_pkg';
28 -- Global variables for debugging
29 g_log_level PLS_INTEGER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
30 g_log_enabled BOOLEAN := fnd_log.test
31 (log_level => g_log_level
32 ,module => C_DEFAULT_MODULE);
33
34
35 PROCEDURE trace (p_msg IN VARCHAR2
36 ,p_level IN NUMBER
37 ,p_module IN VARCHAR2) IS
38 BEGIN
39
40 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
41 fnd_log.message(p_level, p_module);
42 ELSIF p_level >= g_log_level THEN
43 fnd_log.string(p_level, p_module, p_msg);
44 END IF;
45 EXCEPTION
46 WHEN xla_exceptions_pkg.application_exception THEN
47 RAISE;
48 WHEN OTHERS THEN
49 xla_exceptions_pkg.raise_message
50 (p_location => 'xla_sources_f_pkg.trace');
51 END trace;
52
53 /*======================================================================+
54 | |
55 | Procedure insert_row |
56 | |
57 +======================================================================*/
58 PROCEDURE insert_row
59 (x_rowid IN OUT NOCOPY VARCHAR2
60 ,x_application_id IN NUMBER
61 ,x_source_code IN VARCHAR2
62 ,x_source_type_code IN VARCHAR2
63 ,x_plsql_function_name IN VARCHAR2
64 ,x_flex_value_set_id IN NUMBER
65 ,x_sum_flag IN VARCHAR2
66 ,x_visible_flag IN VARCHAR2
67 ,x_translated_flag IN VARCHAR2
68 ,x_lookup_type IN VARCHAR2
69 ,x_view_application_id IN NUMBER
70 ,x_datatype_code IN VARCHAR2
71 ,x_enabled_flag IN VARCHAR2
72 ,x_key_flexfield_flag IN VARCHAR2
73 ,x_segment_code IN VARCHAR2
74 ,x_flexfield_application_id IN NUMBER
75 ,x_id_flex_code IN VARCHAR2
76 ,x_name IN VARCHAR2
77 ,x_description IN VARCHAR2
78 ,x_source_column_name IN VARCHAR2
79 ,x_source_table_name IN VARCHAR2
80 ,x_creation_date IN DATE
81 ,x_created_by IN NUMBER
82 ,x_last_update_date IN DATE
83 ,x_last_updated_by IN NUMBER
84 ,x_last_update_login IN NUMBER)
85
86 IS
87
88 CURSOR c IS
89 SELECT rowid
90 FROM xla_sources_b
91 WHERE application_id = x_application_id
92 AND source_code = x_source_code
93 AND source_type_code = x_source_type_code
94 ;
95
96 BEGIN
97 xla_utility_pkg.trace('> xla_sources_f_pkg.insert_row' ,20);
98
99 INSERT INTO xla_sources_b
100 (creation_date
101 ,created_by
102 ,plsql_function_name
103 ,source_type_code
104 ,application_id
105 ,flex_value_set_id
106 ,sum_flag
107 ,visible_flag
108 ,translated_flag
109 ,lookup_type
110 ,view_application_id
111 ,datatype_code
112 ,source_code
113 ,enabled_flag
114 ,key_flexfield_flag
115 ,segment_code
116 ,flexfield_application_id
117 ,id_flex_code
118 ,source_table_name
119 ,source_column_name
120 ,last_update_date
121 ,last_updated_by
122 ,last_update_login)
123 VALUES
124 (x_creation_date
125 ,x_created_by
126 ,x_plsql_function_name
127 ,x_source_type_code
128 ,x_application_id
129 ,x_flex_value_set_id
130 ,x_sum_flag
131 ,x_visible_flag
132 ,x_translated_flag
133 ,x_lookup_type
134 ,x_view_application_id
135 ,x_datatype_code
136 ,x_source_code
137 ,x_enabled_flag
138 ,x_key_flexfield_flag
139 ,x_segment_code
140 ,x_flexfield_application_id
141 ,x_id_flex_code
142 ,x_source_table_name
143 ,x_source_column_name
144 ,x_last_update_date
145 ,x_last_updated_by
146 ,x_last_update_login)
147 ;
148
149 INSERT INTO xla_sources_tl
150 (name
151 ,description
152 ,creation_date
153 ,created_by
154 ,last_update_date
155 ,last_updated_by
156 ,last_update_login
157 ,application_id
158 ,source_type_code
159 ,source_code
160 ,language
161 ,source_lang)
162 SELECT
163 x_name
164 ,x_description
165 ,x_creation_date
166 ,x_created_by
167 ,x_last_update_date
168 ,x_last_updated_by
169 ,x_last_update_login
170 ,x_application_id
171 ,x_source_type_code
172 ,x_source_code
173 ,l.language_code
174 ,USERENV('LANG')
175 FROM fnd_languages l
176 WHERE l.installed_flag IN ('I', 'B')
177 AND NOT EXISTS
178 (SELECT NULL
179 FROM xla_sources_tl t
180 WHERE t.application_id = x_application_id
181 AND t.source_code = x_source_code
182 AND t.source_type_code = x_source_type_code
183 AND t.language = l.language_code);
184
185 OPEN c;
186 FETCH c INTO x_rowid;
187
188 IF (c%NOTFOUND) THEN
189 CLOSE c;
190 RAISE NO_DATA_FOUND;
191 END IF;
192 CLOSE c;
193
194 xla_utility_pkg.trace('< xla_sources_f_pkg.insert_row' ,20);
195
196 EXCEPTION
197 WHEN OTHERS THEN
198 xla_exceptions_pkg.raise_message
199 (p_location => 'xla_sources_f_pkg.insert_row');
200
201 END insert_row;
202
203 /*======================================================================+
204 | |
205 | Procedure lock_row |
206 | |
207 +======================================================================*/
208 PROCEDURE lock_row
209 (x_application_id IN NUMBER
210 ,x_source_code IN VARCHAR2
211 ,x_source_type_code IN VARCHAR2
212 ,x_plsql_function_name IN VARCHAR2
213 ,x_flex_value_set_id IN NUMBER
214 ,x_sum_flag IN VARCHAR2
215 ,x_visible_flag IN VARCHAR2
216 ,x_translated_flag IN VARCHAR2
217 ,x_lookup_type IN VARCHAR2
218 ,x_view_application_id IN NUMBER
219 ,x_datatype_code IN VARCHAR2
220 ,x_enabled_flag IN VARCHAR2
221 ,x_key_flexfield_flag IN VARCHAR2
222 ,x_segment_code IN VARCHAR2
223 ,x_flexfield_application_id IN NUMBER
224 ,x_id_flex_code IN VARCHAR2
225 ,x_name IN VARCHAR2
226 ,x_description IN VARCHAR2)
227
228 IS
229
230 l_log_module VARCHAR2(240);
231 CURSOR c IS
232 SELECT plsql_function_name
233 ,flex_value_set_id
234 ,sum_flag
235 ,visible_flag
236 ,translated_flag
237 ,lookup_type
238 ,view_application_id
239 ,datatype_code
240 ,enabled_flag
241 ,key_flexfield_flag
242 ,segment_code
243 ,flexfield_application_id
244 ,id_flex_code
245 FROM xla_sources_b
246 WHERE application_id = x_application_id
247 AND source_code = x_source_code
248 AND source_type_code = x_source_type_code
249 FOR UPDATE OF application_id NOWAIT;
250
251 recinfo c%ROWTYPE;
252
253 CURSOR c1 IS
254 SELECT language
255 ,name
256 ,description
257 ,DECODE(language , USERENV('LANG'), 'Y', 'N') baselang
258 FROM xla_sources_tl
259 WHERE application_id = X_application_id
260 AND source_code = X_source_code
261 AND source_type_code = X_source_type_code
262 AND USERENV('LANG') IN (language ,source_lang)
263 FOR UPDATE OF application_id NOWAIT;
264
265 BEGIN
266
267 IF g_log_enabled THEN
268 l_log_module := C_DEFAULT_MODULE||'.lock_row';
269 END IF;
270 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
271 trace('lock_row.Begin',C_LEVEL_PROCEDURE,l_log_module);
272 END IF;
273
274 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
275 trace('------------------------------------------------------------------',C_LEVEL_STATEMENT,l_log_module);
276 trace('x_application_id = ' || x_application_id ,C_LEVEL_STATEMENT,l_log_module);
277 trace('x_source_code = ' || x_source_code ,C_LEVEL_STATEMENT,l_log_module);
278 trace('x_source_type_code = ' || x_source_type_code ,C_LEVEL_STATEMENT,l_log_module);
279 trace('x_plsql_function_name = ' || x_plsql_function_name ,C_LEVEL_STATEMENT,l_log_module);
280 trace('x_flex_value_set_id = ' || x_flex_value_set_id ,C_LEVEL_STATEMENT,l_log_module);
281 trace('x_sum_flag = ' || x_sum_flag ,C_LEVEL_STATEMENT,l_log_module);
282 trace('x_visible_flag = ' || x_visible_flag ,C_LEVEL_STATEMENT,l_log_module);
283 trace('x_translated_flag = ' || x_translated_flag ,C_LEVEL_STATEMENT,l_log_module);
284 trace('x_lookup_type = ' || x_lookup_type ,C_LEVEL_STATEMENT,l_log_module);
285 trace('x_view_application_id = ' || x_view_application_id ,C_LEVEL_STATEMENT,l_log_module);
286 trace('x_datatype_code = ' || x_datatype_code ,C_LEVEL_STATEMENT,l_log_module);
287 trace('x_enabled_flag = ' || x_enabled_flag ,C_LEVEL_STATEMENT,l_log_module);
288 trace('x_key_flexfield_flag = ' || x_key_flexfield_flag ,C_LEVEL_STATEMENT,l_log_module);
289 trace('x_segment_code = ' || x_segment_code ,C_LEVEL_STATEMENT,l_log_module);
290 trace('x_flexfield_application_id = ' || x_flexfield_application_id ,C_LEVEL_STATEMENT,l_log_module);
291 trace('x_id_flex_code = ' || x_id_flex_code ,C_LEVEL_STATEMENT,l_log_module);
292 trace('x_name = ' || x_name ,C_LEVEL_STATEMENT,l_log_module);
293 trace('x_description = ' || x_description ,C_LEVEL_STATEMENT,l_log_module);
294 trace('------------------------------------------------------------------',C_LEVEL_STATEMENT,l_log_module);
295 END IF;
296
297 OPEN c;
298 FETCH c INTO recinfo;
299
300 IF (c%NOTFOUND) THEN
301 CLOSE c;
302 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
303 app_exception.raise_exception;
304 END IF;
305 CLOSE c;
306
307 IF ( ((recinfo.plsql_function_name = X_plsql_function_name)
308 OR ((recinfo.plsql_function_name IS NULL)
309 AND (x_plsql_function_name IS NULL)))
310 AND ((recinfo.flex_value_set_id = X_flex_value_set_id)
311 OR ((recinfo.flex_value_set_id IS NULL)
312 AND (x_flex_value_set_id IS NULL)))
313 AND (recinfo.sum_flag = x_sum_flag)
314 AND (recinfo.visible_flag = x_visible_flag)
315 AND ((recinfo.translated_flag = X_translated_flag)
316 OR ((recinfo.translated_flag IS NULL)
317 AND (x_translated_flag IS NULL)))
318 AND ((recinfo.lookup_type = X_lookup_type)
319 OR ((recinfo.lookup_type IS NULL)
320 AND (x_lookup_type IS NULL)))
321 AND ((recinfo.view_application_id = X_view_application_id)
322 OR ((recinfo.view_application_id IS NULL)
323 AND (x_view_application_id IS NULL)))
324 AND (recinfo.datatype_code = x_datatype_code)
325 AND (recinfo.enabled_flag = x_enabled_flag)
326 AND (recinfo.key_flexfield_flag = x_key_flexfield_flag)
327 AND ((recinfo.segment_code = X_segment_code)
328 OR ((recinfo.segment_code IS NULL)
329 AND (x_segment_code IS NULL)))
330 AND ((recinfo.flexfield_application_id = X_flexfield_application_id)
331 OR ((recinfo.flexfield_application_id IS NULL)
332 AND (x_flexfield_application_id IS NULL)))
333 AND ((recinfo.id_flex_code = X_id_flex_code)
334 OR ((recinfo.id_flex_code IS NULL)
335 AND (x_id_flex_code IS NULL)))
336 ) THEN
337
338 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
339 trace('lock_row successful.',C_LEVEL_STATEMENT,l_log_module);
340 END IF;
341 ELSE
342 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
343 trace('lock_row failed. ',C_LEVEL_STATEMENT,l_log_module);
344 END IF;
345 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
346 app_exception.raise_exception;
347 END IF;
348
349 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
350 trace('Before Loop tlinfo',C_LEVEL_STATEMENT,l_log_module);
351 END IF;
352
353 FOR tlinfo IN c1 LOOP
354 IF (tlinfo.baselang = 'Y') THEN
355 IF ( (tlinfo.name = X_name)
356 AND ((tlinfo.description = X_description)
357 OR ((tlinfo.description is null)
358 AND (X_description is null)))
359 ) THEN
360 NULL;
361 ELSE
362 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
363 trace('tlinfo failed',C_LEVEL_STATEMENT,l_log_module);
364 trace('tlinfo.name = ' || tlinfo.name,C_LEVEL_STATEMENT,l_log_module);
365 trace('X_name = ' || X_name,C_LEVEL_STATEMENT,l_log_module);
366 trace('tlinfo.description = ' || tlinfo.description ,C_LEVEL_STATEMENT,l_log_module);
367 trace('X_description = ' || X_description ,C_LEVEL_STATEMENT,l_log_module);
368 END IF;
369
370 IF tlinfo.name <> X_name THEN
371 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
372 trace('Name is different ',C_LEVEL_STATEMENT,l_log_module);
373 END IF;
374 END IF;
375 IF tlinfo.description <> X_description THEN
376 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
377 trace('description is different ',C_LEVEL_STATEMENT,l_log_module);
378 END IF;
379 END IF;
380
381 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
382 app_exception.raise_exception;
383 END IF;
384 END IF;
385 END LOOP;
386
387 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
388 trace('lock_row.End',C_LEVEL_PROCEDURE,l_Log_module);
389 END IF;
390 RETURN;
391
392 END lock_row;
393
394 /*======================================================================+
395 | |
396 | Procedure update_row |
397 | |
398 +======================================================================*/
399 PROCEDURE update_row
400 (x_application_id IN NUMBER
401 ,x_source_code IN VARCHAR2
402 ,x_source_type_code IN VARCHAR2
403 ,x_plsql_function_name IN VARCHAR2
404 ,x_flex_value_set_id IN NUMBER
405 ,x_sum_flag IN VARCHAR2
406 ,x_visible_flag IN VARCHAR2
407 ,x_translated_flag IN VARCHAR2
408 ,x_lookup_type IN VARCHAR2
409 ,x_view_application_id IN NUMBER
410 ,x_datatype_code IN VARCHAR2
411 ,x_enabled_flag IN VARCHAR2
412 ,x_key_flexfield_flag IN VARCHAR2
413 ,x_segment_code IN VARCHAR2
414 ,x_flexfield_application_id IN NUMBER
415 ,x_id_flex_code IN VARCHAR2
416 ,x_name IN VARCHAR2
417 ,x_description IN VARCHAR2
418 ,x_source_column_name IN VARCHAR2
419 ,x_source_table_name IN VARCHAR2
420 ,x_last_update_date IN DATE
421 ,x_last_updated_by IN NUMBER
422 ,x_last_update_login IN NUMBER)
423
424 IS
425
426 BEGIN
427 xla_utility_pkg.trace('> xla_sources_f_pkg.update_row' ,20);
428 UPDATE xla_sources_b
429 SET
430 last_update_date = x_last_update_date
431 ,plsql_function_name = x_plsql_function_name
432 ,flex_value_set_id = x_flex_value_set_id
433 ,sum_flag = x_sum_flag
434 ,visible_flag = x_visible_flag
435 ,translated_flag = x_translated_flag
436 ,lookup_type = x_lookup_type
437 ,view_application_id = x_view_application_id
438 ,datatype_code = x_datatype_code
439 ,enabled_flag = x_enabled_flag
440 ,key_flexfield_flag = x_key_flexfield_flag
441 ,segment_code = x_segment_code
442 ,flexfield_application_id = x_flexfield_application_id
443 ,id_flex_code = x_id_flex_code
444 ,source_column_name = x_source_column_name
445 ,source_table_name = x_source_table_name
446 ,last_updated_by = x_last_updated_by
447 ,last_update_login = x_last_update_login
448 WHERE application_id = X_application_id
449 AND source_code = X_source_code
450 AND source_type_code = X_source_type_code;
451
452 IF (SQL%NOTFOUND) THEN
453 RAISE NO_DATA_FOUND;
454 END IF;
455
456 UPDATE xla_sources_tl
457 SET
458 last_update_date = x_last_update_date
459 ,name = X_name
460 ,description = X_description
461 ,last_updated_by = x_last_updated_by
462 ,last_update_login = x_last_update_login
463 ,source_lang = USERENV('LANG')
464 WHERE application_id = X_application_id
465 AND source_code = X_source_code
466 AND source_type_code = X_source_type_code
467 AND USERENV('LANG') IN (language, source_lang);
468
469 IF (SQL%NOTFOUND) THEN
470 RAISE NO_DATA_FOUND;
471 END IF;
472
473 xla_utility_pkg.trace('< xla_sources_f_pkg.update_row' ,20);
474 END update_row;
475
476 /*======================================================================+
477 | |
478 | Procedure delete_row |
479 | |
480 +======================================================================*/
481 PROCEDURE delete_row
482 (x_application_id IN NUMBER
483 ,x_source_code IN VARCHAR2
484 ,x_source_type_code IN VARCHAR2)
485
486 IS
487
488 BEGIN
489 xla_utility_pkg.trace('> xla_sources_f_pkg.delete_row' ,20);
490 DELETE FROM xla_sources_tl
491 WHERE application_id = x_application_id
492 AND source_code = x_source_code
493 AND source_type_code = x_source_type_code;
494
495
496 IF (SQL%NOTFOUND) THEN
497 RAISE NO_DATA_FOUND;
498 END IF;
499
500 DELETE FROM xla_sources_b
501 WHERE application_id = x_application_id
502 AND source_code = x_source_code
503 AND source_type_code = x_source_type_code;
504
505
506 IF (SQL%NOTFOUND) THEN
507 RAISE NO_DATA_FOUND;
508 END IF;
509
510
511 xla_utility_pkg.trace('< xla_sources_f_pkg.delete_row' ,20);
512 END delete_row;
513
514 /*======================================================================+
515 | |
516 | Procedure add_language |
517 | |
518 +======================================================================*/
519 PROCEDURE add_language
520
521 IS
522
523 BEGIN
524 xla_utility_pkg.trace('> xla_sources_f_pkg.add_language' ,20);
525
526 DELETE FROM xla_sources_tl T
527 WHERE NOT EXISTS
528 (SELECT NULL
529 FROM xla_sources_b b
530 WHERE b.application_id = t.application_id
531 AND b.source_code = t.source_code
532 AND b.source_type_code = t.source_type_code);
533
534 UPDATE xla_sources_tl t
535 SET (name
536 ,description)
537 = (SELECT b.name
538 ,b.description
539 FROM xla_sources_tl b
540 WHERE b.application_id = t.application_id
541 AND b.source_code = t.source_code
542 AND b.source_type_code = t.source_type_code
543 AND b.language = t.source_lang)
544 WHERE (t.application_id
545 ,t.source_code
546 ,t.source_type_code
547 ,t.language)
548 IN (SELECT subt.application_id
549 ,subt.source_code
550 ,subt.source_type_code
551 ,subt.language
552 FROM xla_sources_tl subb
553 ,xla_sources_tl subt
554 WHERE subb.application_id = subt.application_id
555 AND subb.source_code = subt.source_code
556 AND subb.source_type_code = subt.source_type_code
557 AND subb.language = subt.source_lang
558 AND (SUBB.name <> SUBT.name
559 OR SUBB.description <> SUBT.description
560 OR (subb.description IS NULL
561 AND subt.description IS NOT NULL)
562 OR (subb.description IS NOT NULL
563 AND subt.description IS NULL)
564 ))
565 ;
566
567 INSERT INTO xla_sources_tl
568 (name
569 ,description
570 ,creation_date
571 ,created_by
572 ,last_update_date
573 ,last_updated_by
574 ,last_update_login
575 ,application_id
576 ,source_type_code
577 ,source_code
578 ,language
579 ,source_lang)
580 SELECT /*+ ORDERED */
581 b.name
582 ,b.description
583 ,b.creation_date
584 ,b.created_by
585 ,b.last_update_date
586 ,b.last_updated_by
587 ,b.last_update_login
588 ,b.application_id
589 ,b.source_type_code
590 ,b.source_code
591 ,l.language_code
592 ,b.source_lang
593 FROM xla_sources_tl b
594 ,fnd_languages l
595 WHERE l.installed_flag IN ('I', 'B')
596 AND b.language = userenv('LANG')
597 AND NOT EXISTS
598 (SELECT NULL
599 FROM xla_sources_tl t
600 WHERE t.application_id = b.application_id
601 AND t.source_code = b.source_code
602 AND t.source_type_code = b.source_type_code
603 AND t.language = l.language_code);
604
605 xla_utility_pkg.trace('< xla_sources_f_pkg.add_language' ,20);
606 END add_language;
607
608 /*======================================================================+
609 | |
610 | Procedure load_row |
611 | |
612 +======================================================================*/
613 PROCEDURE load_row
614 (p_appl_short_name IN VARCHAR2
615 ,p_source_code IN VARCHAR2
616 ,p_source_type_code IN VARCHAR2
617 ,p_datatype_code IN VARCHAR2
618 ,p_plsql_function_name IN VARCHAR2
619 ,p_flex_value_set_name IN VARCHAR2
620 ,p_sum_flag IN VARCHAR2
621 ,p_visible_flag IN VARCHAR2
622 ,p_translated_flag IN VARCHAR2
623 ,p_enabled_flag IN VARCHAR2
624 ,p_view_appl_short_name IN VARCHAR2
625 ,p_lookup_type IN VARCHAR2
626 ,p_key_flexfield_flag IN VARCHAR2
627 ,p_segment_code IN VARCHAR2
628 ,p_flex_appl_short_name IN VARCHAR2
629 ,p_id_flex_code IN VARCHAR2
630 ,p_name IN VARCHAR2
631 ,p_description IN VARCHAR2
632 ,p_owner IN VARCHAR2
633 ,p_source_column_name IN VARCHAR2
634 ,p_source_table_name IN VARCHAR2
635 ,p_last_update_date IN VARCHAR2
636 ,p_custom_mode IN VARCHAR2)
637 IS
638
639 l_view_application_id number(38);
640 l_flex_application_id number(38);
641 l_application_id number(38);
642 l_flex_value_set_id number(38);
643 l_rowid ROWID;
644 l_exist VARCHAR2(1);
645 f_luby number(38); -- entity owner in file
646 f_ludate date; -- entity update date in file
647 db_luby number(38); -- entity owner in db
648 db_ludate date; -- entity update date in db
649
650 CURSOR c_appl
651 IS
652 SELECT application_id
653 FROM fnd_application
654 WHERE application_short_name = p_appl_short_name;
655
656 CURSOR c_view_appl
657 IS
658 SELECT application_id
659 FROM fnd_application
660 WHERE application_short_name = p_view_appl_short_name;
661
662 CURSOR c_flex
663 IS
664 SELECT flex_value_set_id
665 FROM fnd_flex_value_sets
666 WHERE flex_value_set_name = p_flex_value_set_name;
667
668 CURSOR c_flex_appl
669 IS
670 SELECT application_id
671 FROM fnd_application
672 WHERE application_short_name = p_flex_appl_short_name;
673
674 BEGIN
675
676 OPEN c_appl;
677 FETCH c_appl
678 INTO l_application_id;
679 CLOSE c_appl;
680
681 OPEN c_view_appl;
682 FETCH c_view_appl
683 INTO l_view_application_id;
684 CLOSE c_view_appl;
685
686 OPEN c_flex;
687 FETCH c_flex
688 INTO l_flex_value_set_id;
689 CLOSE c_flex;
690
691 OPEN c_flex_appl;
692 FETCH c_flex_appl
693 INTO l_flex_application_id;
694 CLOSE c_flex_appl;
695
696 -- Translate owner to file_last_updated_by
697 f_luby := fnd_load_util.owner_id(p_owner);
698
699 -- Translate char last_update_date to date
700 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
701
702 BEGIN
703
704 SELECT last_updated_by, last_update_date
705 INTO db_luby, db_ludate
706 FROM xla_sources_vl
707 WHERE application_id = l_application_id
708 AND source_code = p_source_code
709 AND source_type_code = p_source_type_code;
710
711 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
712 db_ludate, null)) then
713 xla_sources_f_pkg.update_row
714 (x_application_id => l_application_id
715 ,x_source_code => p_source_code
716 ,x_source_type_code => p_source_type_code
717 ,x_plsql_function_name => p_plsql_function_name
718 ,x_flex_value_set_id => l_flex_value_set_id
719 ,x_sum_flag => p_sum_flag
720 ,x_visible_flag => p_visible_flag
721 ,x_translated_flag => p_translated_flag
722 ,x_lookup_type => p_lookup_type
723 ,x_view_application_id => l_view_application_id
724 ,x_datatype_code => p_datatype_code
725 ,x_enabled_flag => p_enabled_flag
726 ,x_key_flexfield_flag => p_key_flexfield_flag
727 ,x_segment_code => p_segment_code
728 ,x_flexfield_application_id => l_flex_application_id
729 ,x_id_flex_code => p_id_flex_code
730 ,x_name => p_name
731 ,x_description => p_description
732 ,x_source_column_name => p_source_column_name
733 ,x_source_table_name => p_source_table_name
734 ,x_last_update_date => f_ludate
735 ,x_last_updated_by => f_luby
736 ,x_last_update_login => 0);
737
738 END IF;
739
740 EXCEPTION
741
742 WHEN NO_DATA_FOUND THEN
743 xla_sources_f_pkg.insert_row
744 (x_rowid => l_rowid
745 ,x_application_id => l_application_id
746 ,x_source_code => p_source_code
747 ,x_source_type_code => p_source_type_code
748 ,x_plsql_function_name => p_plsql_function_name
749 ,x_flex_value_set_id => l_flex_value_set_id
750 ,x_sum_flag => p_sum_flag
751 ,x_visible_flag => p_visible_flag
752 ,x_translated_flag => p_translated_flag
753 ,x_lookup_type => p_lookup_type
754 ,x_view_application_id => l_view_application_id
755 ,x_datatype_code => p_datatype_code
756 ,x_enabled_flag => p_enabled_flag
757 ,x_key_flexfield_flag => p_key_flexfield_flag
758 ,x_segment_code => p_segment_code
759 ,x_flexfield_application_id => l_flex_application_id
760 ,x_id_flex_code => p_id_flex_code
761 ,x_name => p_name
762 ,x_description => p_description
763 ,x_source_column_name => p_source_column_name
764 ,x_source_table_name => p_source_table_name
765 ,x_creation_date => f_ludate
766 ,x_created_by => f_luby
767 ,x_last_update_date => f_ludate
768 ,x_last_updated_by => f_luby
769 ,x_last_update_login => 0);
770
771 END;
772
773 EXCEPTION
774 WHEN NO_DATA_FOUND THEN
775 null;
776 WHEN OTHERS THEN
777 xla_exceptions_pkg.raise_message
778 (p_location => 'xla_sources_f_pkg.load_row');
779
780 END load_row;
781
782 /*======================================================================+
783 | |
784 | Procedure translate_row |
785 | |
786 +======================================================================*/
787 PROCEDURE translate_row
788 (p_appl_short_name IN VARCHAR2
789 ,p_source_code IN VARCHAR2
790 ,p_source_type_code IN VARCHAR2
791 ,p_name IN VARCHAR2
792 ,p_description IN VARCHAR2
793 ,p_owner IN VARCHAR2
794 ,p_last_update_date IN VARCHAR2
795 ,p_custom_mode IN VARCHAR2)
796 IS
797
798 l_view_application_id number(38);
799 l_application_id number(38);
800 l_row_id ROWID;
801 l_exist VARCHAR2(1);
802 f_luby number(38); -- entity owner in file
803 f_ludate date; -- entity update date in file
804 db_luby number(38); -- entity owner in db
805 db_ludate date; -- entity update date in db
806
807 CURSOR c_appl
808 IS
809 SELECT application_id
810 FROM fnd_application
811 WHERE application_short_name = p_appl_short_name;
812
813 BEGIN
814
815 OPEN c_appl;
816 FETCH c_appl
817 INTO l_application_id;
818 CLOSE c_appl;
819
820 -- Translate owner to file_last_updated_by
821 f_luby := fnd_load_util.owner_id(p_owner);
822
823 -- Translate char last_update_date to date
824 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
825
826 BEGIN
827 SELECT last_updated_by, last_update_date
828 INTO db_luby, db_ludate
829 FROM xla_sources_tl
830 WHERE application_id = l_application_id
831 AND source_code = p_source_code
832 AND source_type_code = p_source_type_code
833 AND language = userenv('LANG');
834
835 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
836 db_ludate, p_custom_mode)) then
837 UPDATE xla_sources_tl
838 SET name = p_name
839 ,description = p_description
840 ,last_update_date = f_ludate
841 ,last_updated_by = f_luby
842 ,last_update_login = 0
843 ,source_lang = userenv('LANG')
844 WHERE userenv('LANG') IN (language, source_lang)
845 AND application_id = l_application_id
846 AND source_code = p_source_code
847 AND source_type_code = p_source_type_code;
848
849 END IF;
850
851
852 END;
853
854
855
856 END translate_row;
857 BEGIN
858 -- l_log_module := C_DEFAULT_MODULE;
859 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
860 g_log_enabled := fnd_log.test
861 (log_level => g_log_level
862 ,module => C_DEFAULT_MODULE);
863
864 IF NOT g_log_enabled THEN
865 g_log_level := C_LEVEL_LOG_DISABLED;
866 END IF;
867 end xla_sources_f_PKG;