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