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