[Home] [Help]
PACKAGE BODY: APPS.XLA_TAB_ACCT_TYPES_F_PKG
Source
1 PACKAGE BODY xla_tab_acct_types_f_pkg AS
2 /* $Header: xlathtabact.pkb 120.6.12010000.1 2008/07/29 10:10:36 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_tab_acct_types |
10 | |
11 | DESCRIPTION |
12 | Forms PL/SQL Wrapper for xla_tab_acct_types |
13 | |
14 | HISTORY |
15 | Generated from XLAUTB. |
16 | 01-SEP-2005 Jorge Larre |
17 | Add procedure translate_row and load_row to use with FNDLOAD in |
18 | conjunction with the file xlatabseed.lct. Bug 4590464. |
19 | |
20 +======================================================================*/
21
22 --=======================================================================
23 -- *********** Local Trace Routine **********
24 --=======================================================================
25 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
26 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
27 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
28 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
29 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
30 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
31
32 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
33 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_acctg_methods_f_pkg';
34
35 g_log_level NUMBER;
36 g_log_enabled BOOLEAN;
37
38 PROCEDURE trace
39 (p_msg IN VARCHAR2
40 ,p_module IN VARCHAR2
41 ,p_level IN NUMBER) IS
42 BEGIN
43 ------------------------------------------------------------------------
44 -- Following is for FND log.
45 ------------------------------------------------------------------------
46 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
47 fnd_log.message(p_level, p_module);
48 ELSIF p_level >= g_log_level THEN
49 fnd_log.string(p_level, p_module, p_msg);
50 END IF;
51
52 EXCEPTION
53 WHEN xla_exceptions_pkg.application_exception THEN
54 RAISE;
55
56 WHEN OTHERS THEN
57 xla_exceptions_pkg.raise_message
58 (p_location => 'xla_acctg_methods_f_pkg.trace');
59 END trace;
60
61
62 /*======================================================================+
63 | |
64 | Procedure insert_row |
65 | |
66 +======================================================================*/
67
68 procedure INSERT_ROW (
69 X_ROWID in out NOCOPY VARCHAR2,
70 X_APPLICATION_ID in NUMBER,
71 X_ACCOUNT_TYPE_CODE in VARCHAR2,
72 X_ENABLED_FLAG in VARCHAR2,
73 X_RULE_ASSIGNMENT_CODE in VARCHAR2,
74 X_COMPILE_STATUS_CODE in VARCHAR2,
75 X_OBJECT_NAME_AFFIX in VARCHAR2,
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 ) is
84 cursor C is select ROWID from XLA_TAB_ACCT_TYPES_B
85 where APPLICATION_ID = X_APPLICATION_ID
86 and ACCOUNT_TYPE_CODE = X_ACCOUNT_TYPE_CODE
87 ;
88 begin
89 insert into XLA_TAB_ACCT_TYPES_B (
90 APPLICATION_ID,
91 ACCOUNT_TYPE_CODE,
92 ENABLED_FLAG,
93 RULE_ASSIGNMENT_CODE,
94 COMPILE_STATUS_CODE,
95 OBJECT_NAME_AFFIX,
96 CREATION_DATE,
97 CREATED_BY,
98 LAST_UPDATE_DATE,
99 LAST_UPDATED_BY,
100 LAST_UPDATE_LOGIN
101 ) values (
102 X_APPLICATION_ID,
103 X_ACCOUNT_TYPE_CODE,
104 X_ENABLED_FLAG,
105 X_RULE_ASSIGNMENT_CODE,
106 X_COMPILE_STATUS_CODE,
107 X_OBJECT_NAME_AFFIX,
108 X_CREATION_DATE,
109 X_CREATED_BY,
110 X_LAST_UPDATE_DATE,
111 X_LAST_UPDATED_BY,
112 X_LAST_UPDATE_LOGIN
113 );
114
115 insert into XLA_TAB_ACCT_TYPES_TL (
116 APPLICATION_ID,
117 ACCOUNT_TYPE_CODE,
118 NAME,
119 DESCRIPTION,
120 CREATION_DATE,
121 CREATED_BY,
122 LAST_UPDATE_DATE,
123 LAST_UPDATED_BY,
124 LAST_UPDATE_LOGIN,
125 LANGUAGE,
126 SOURCE_LANG
127 ) select
128 X_APPLICATION_ID,
129 X_ACCOUNT_TYPE_CODE,
130 X_NAME,
131 X_DESCRIPTION,
132 X_CREATION_DATE,
133 X_CREATED_BY,
134 X_LAST_UPDATE_DATE,
135 X_LAST_UPDATED_BY,
136 X_LAST_UPDATE_LOGIN,
137 L.LANGUAGE_CODE,
138 userenv('LANG')
139 from FND_LANGUAGES L
140 where L.INSTALLED_FLAG in ('I', 'B')
141 and not exists
142 (select NULL
143 from XLA_TAB_ACCT_TYPES_TL T
144 where T.APPLICATION_ID = X_APPLICATION_ID
145 and T.ACCOUNT_TYPE_CODE = X_ACCOUNT_TYPE_CODE
146 and T.LANGUAGE = L.LANGUAGE_CODE);
147
148 open c;
149 fetch c into X_ROWID;
150 if (c%notfound) then
151 close c;
152 raise no_data_found;
153 end if;
154 close c;
155
156 end INSERT_ROW;
157
158
159 /*======================================================================+
160 | |
161 | Procedure lock_row |
162 | |
163 +======================================================================*/
164
165 procedure LOCK_ROW (
166 X_APPLICATION_ID in NUMBER,
167 X_ACCOUNT_TYPE_CODE in VARCHAR2,
168 X_ENABLED_FLAG in VARCHAR2,
169 X_RULE_ASSIGNMENT_CODE in VARCHAR2,
170 X_COMPILE_STATUS_CODE in VARCHAR2,
171 X_OBJECT_NAME_AFFIX in VARCHAR2,
172 X_NAME in VARCHAR2,
173 X_DESCRIPTION in VARCHAR2
174 ) is
175 cursor c is select
176 ENABLED_FLAG,
177 RULE_ASSIGNMENT_CODE,
178 COMPILE_STATUS_CODE,
179 OBJECT_NAME_AFFIX
180 from XLA_TAB_ACCT_TYPES_B
181 where APPLICATION_ID = X_APPLICATION_ID
182 and ACCOUNT_TYPE_CODE = X_ACCOUNT_TYPE_CODE
183 for update of APPLICATION_ID nowait;
184 recinfo c%rowtype;
185
186 cursor c1 is select
187 NAME,
188 DESCRIPTION,
189 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
190 from XLA_TAB_ACCT_TYPES_TL
191 where APPLICATION_ID = X_APPLICATION_ID
192 and ACCOUNT_TYPE_CODE = X_ACCOUNT_TYPE_CODE
193 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
194 for update of APPLICATION_ID nowait;
195 begin
196 open c;
197 fetch c into recinfo;
198 if (c%notfound) then
199 close c;
200 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
201 app_exception.raise_exception;
202 end if;
203 close c;
204 if ( (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
205 AND (recinfo.RULE_ASSIGNMENT_CODE = X_RULE_ASSIGNMENT_CODE)
206 AND ( (recinfo.COMPILE_STATUS_CODE = X_COMPILE_STATUS_CODE)
207 OR (recinfo.COMPILE_STATUS_CODE IS NULL AND X_COMPILE_STATUS_CODE IS NULL)
208 )
209 AND ( (recinfo.OBJECT_NAME_AFFIX = X_OBJECT_NAME_AFFIX)
210 OR (recinfo.OBJECT_NAME_AFFIX IS NULL AND X_OBJECT_NAME_AFFIX IS NULL)
211 )
212 ) then
213 null;
214 else
215 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
216 app_exception.raise_exception;
217 end if;
218
219 for tlinfo in c1 loop
220 if (tlinfo.BASELANG = 'Y') then
221 if ( (tlinfo.NAME = X_NAME)
222 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
223 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
224 ) then
225 null;
226 else
227 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
228 app_exception.raise_exception;
229 end if;
230 end if;
231 end loop;
232 return;
233 end LOCK_ROW;
234
235
236 /*======================================================================+
237 | |
238 | Procedure update_row |
239 | |
240 +======================================================================*/
241
242 procedure UPDATE_ROW (
243 X_APPLICATION_ID in NUMBER,
244 X_ACCOUNT_TYPE_CODE in VARCHAR2,
245 X_ENABLED_FLAG in VARCHAR2,
246 X_RULE_ASSIGNMENT_CODE in VARCHAR2,
247 X_COMPILE_STATUS_CODE in VARCHAR2,
248 X_OBJECT_NAME_AFFIX in VARCHAR2,
249 X_NAME in VARCHAR2,
250 X_DESCRIPTION in VARCHAR2,
251 X_LAST_UPDATE_DATE in DATE,
252 X_LAST_UPDATED_BY in NUMBER,
253 X_LAST_UPDATE_LOGIN in NUMBER
254 ) is
255 begin
256 update XLA_TAB_ACCT_TYPES_B set
257 ENABLED_FLAG = X_ENABLED_FLAG,
258 RULE_ASSIGNMENT_CODE = X_RULE_ASSIGNMENT_CODE,
259 COMPILE_STATUS_CODE = X_COMPILE_STATUS_CODE,
260 OBJECT_NAME_AFFIX = X_OBJECT_NAME_AFFIX,
261 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
262 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
263 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
264 where APPLICATION_ID = X_APPLICATION_ID
265 and ACCOUNT_TYPE_CODE = X_ACCOUNT_TYPE_CODE;
266
267 if (sql%notfound) then
268 raise no_data_found;
269 end if;
270
271 update XLA_TAB_ACCT_TYPES_TL set
272 NAME = X_NAME,
273 DESCRIPTION = X_DESCRIPTION,
274 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
275 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
276 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
277 SOURCE_LANG = userenv('LANG')
278 where APPLICATION_ID = X_APPLICATION_ID
279 and ACCOUNT_TYPE_CODE = X_ACCOUNT_TYPE_CODE
280 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
281
282 if (sql%notfound) then
283 raise no_data_found;
284 end if;
285 end UPDATE_ROW;
286
287
288 /*======================================================================+
289 | |
290 | Procedure delete_row |
291 | |
292 +======================================================================*/
293 procedure DELETE_ROW (
294 X_APPLICATION_ID in NUMBER,
295 X_ACCOUNT_TYPE_CODE in VARCHAR2
296 ) is
297 begin
298 delete from XLA_TAB_ACCT_TYPES_TL
299 where APPLICATION_ID = X_APPLICATION_ID
300 and ACCOUNT_TYPE_CODE = X_ACCOUNT_TYPE_CODE;
301
302 if (sql%notfound) then
303 raise no_data_found;
304 end if;
305
306 delete from XLA_TAB_ACCT_TYPES_B
307 where APPLICATION_ID = X_APPLICATION_ID
308 and ACCOUNT_TYPE_CODE = X_ACCOUNT_TYPE_CODE;
309
310 if (sql%notfound) then
311 raise no_data_found;
312 end if;
313 end DELETE_ROW;
314
315
316 /*======================================================================+
317 | |
318 | Procedure add_language |
319 | |
320 +======================================================================*/
321
322 procedure ADD_LANGUAGE
323 is
324 begin
325 delete from XLA_TAB_ACCT_TYPES_TL T
326 where not exists
327 (select NULL
328 from XLA_TAB_ACCT_TYPES_B B
329 where B.APPLICATION_ID = T.APPLICATION_ID
330 and B.ACCOUNT_TYPE_CODE = T.ACCOUNT_TYPE_CODE
331 );
332
333 update XLA_TAB_ACCT_TYPES_TL T set (
334 NAME,
335 DESCRIPTION
336 ) = (select
337 B.NAME,
338 B.DESCRIPTION
339 from XLA_TAB_ACCT_TYPES_TL B
340 where B.APPLICATION_ID = T.APPLICATION_ID
341 and B.ACCOUNT_TYPE_CODE = T.ACCOUNT_TYPE_CODE
342 and B.LANGUAGE = T.SOURCE_LANG)
343 where (
344 T.APPLICATION_ID,
345 T.ACCOUNT_TYPE_CODE,
346 T.LANGUAGE
347 ) in (select
348 SUBT.APPLICATION_ID,
349 SUBT.ACCOUNT_TYPE_CODE,
350 SUBT.LANGUAGE
351 from XLA_TAB_ACCT_TYPES_TL SUBB, XLA_TAB_ACCT_TYPES_TL SUBT
352 where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
353 and SUBB.ACCOUNT_TYPE_CODE = SUBT.ACCOUNT_TYPE_CODE
354 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
355 and (SUBB.NAME <> SUBT.NAME
356 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
357 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
358 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
359 ));
360
361 insert into XLA_TAB_ACCT_TYPES_TL (
362 APPLICATION_ID,
363 ACCOUNT_TYPE_CODE,
364 NAME,
365 DESCRIPTION,
366 CREATION_DATE,
367 CREATED_BY,
368 LAST_UPDATE_DATE,
369 LAST_UPDATED_BY,
370 LAST_UPDATE_LOGIN,
371 LANGUAGE,
372 SOURCE_LANG
373 ) select /*+ ORDERED */
374 B.APPLICATION_ID,
375 B.ACCOUNT_TYPE_CODE,
376 B.NAME,
377 B.DESCRIPTION,
378 B.CREATION_DATE,
379 B.CREATED_BY,
380 B.LAST_UPDATE_DATE,
381 B.LAST_UPDATED_BY,
382 B.LAST_UPDATE_LOGIN,
383 L.LANGUAGE_CODE,
384 B.SOURCE_LANG
385 from XLA_TAB_ACCT_TYPES_TL B, FND_LANGUAGES L
386 where L.INSTALLED_FLAG in ('I', 'B')
387 and B.LANGUAGE = userenv('LANG')
388 and not exists
389 (select NULL
390 from XLA_TAB_ACCT_TYPES_TL T
391 where T.APPLICATION_ID = B.APPLICATION_ID
392 and T.ACCOUNT_TYPE_CODE = B.ACCOUNT_TYPE_CODE
393 and T.LANGUAGE = L.LANGUAGE_CODE);
394 end ADD_LANGUAGE;
395
396 /*======================================================================+
397 | |
398 | Procedure translate_row |
399 | To be used by FNDLOAD |
400 | |
401 +======================================================================*/
402 PROCEDURE translate_row
403 (p_application_short_name IN VARCHAR2
404 ,p_account_type_code IN VARCHAR2
405 ,p_name IN VARCHAR2
406 ,p_description IN VARCHAR2
407 ,p_owner IN VARCHAR2
408 ,p_last_update_date IN VARCHAR2
409 ,p_custom_mode IN VARCHAR2)
410 IS
411 CURSOR c_app_id IS
412 SELECT application_id
413 FROM fnd_application
414 WHERE application_short_name = p_application_short_name;
415
416 l_application_id INTEGER;
417 l_rowid ROWID;
418 l_exist VARCHAR2(1);
422 db_ludate DATE; -- entity update date in db
419 f_luby NUMBER; -- entity owner in file
420 f_ludate DATE; -- entity update date in file
421 db_luby NUMBER; -- entity owner in db
423 l_log_module VARCHAR2(240);
424 BEGIN
425
426 IF g_log_enabled THEN
427 l_log_module := C_DEFAULT_MODULE||'.translate_row';
428 END IF;
429
430 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
431 trace(p_msg => 'BEGIN of procedure translate_row',
432 p_module => l_log_module,
433 p_level => C_LEVEL_PROCEDURE);
434 END IF;
435
436 -- Translate owner to file_last_updated_by
437 f_luby := fnd_load_util.owner_id(p_owner);
438
439 -- Translate char last_update_date to date
440 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
441
442 OPEN c_app_id;
443 FETCH c_app_id INTO l_application_id;
444 CLOSE c_app_id;
445
446 BEGIN
447 SELECT last_updated_by, last_update_date
448 INTO db_luby, db_ludate
449 FROM xla_tab_acct_types_tl
450 WHERE application_id = l_application_id
451 AND account_type_code = p_account_type_code
452 AND language = userenv('LANG');
453
454 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
455 db_ludate, p_custom_mode)) then
456 UPDATE xla_tab_acct_types_tl
457 SET name = p_name
458 ,description = p_description
459 ,last_update_date = f_ludate
460 ,last_updated_by = f_luby
461 ,last_update_login = 0
462 ,source_lang = userenv('LANG')
463 WHERE userenv('LANG') IN (language, source_lang)
464 AND application_id = l_application_id
465 AND account_type_code = p_account_type_code;
466
467 END IF;
468
469
470
471 END;
472
473 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
474 trace(p_msg => 'END of procedure translate_row',
475 p_module => l_log_module,
476 p_level => C_LEVEL_PROCEDURE);
477 END IF;
478
479
480
481 END translate_row;
482
483 /*======================================================================+
484 | |
485 | Procedure load_row |
486 | To be used by FNDLOAD |
487 | |
488 +======================================================================*/
489 PROCEDURE load_row
490 (p_application_short_name IN VARCHAR2
491 ,p_account_type_code IN VARCHAR2
492 ,p_enabled_flag IN VARCHAR2
493 ,p_rule_assignment_code IN VARCHAR2
494 ,p_compile_status_code IN VARCHAR2
495 ,p_object_name_affix IN VARCHAR2
496 ,p_name IN VARCHAR2
497 ,p_description IN VARCHAR2
498 ,p_owner IN VARCHAR2
499 ,p_last_update_date IN VARCHAR2)
500
501 IS
502 CURSOR c_app_id IS
503 SELECT application_id
504 FROM fnd_application
505 WHERE application_short_name = p_application_short_name;
506
507 l_application_id INTEGER;
508 l_rowid ROWID;
509 l_exist VARCHAR2(1);
510 f_luby NUMBER; -- entity owner in file
511 f_ludate DATE; -- entity update date in file
512 db_luby NUMBER; -- entity owner in db
513 db_ludate DATE; -- entity update date in db
514 l_log_module VARCHAR2(240);
515 BEGIN
516
517 IF g_log_enabled THEN
518 l_log_module := C_DEFAULT_MODULE||'.load_row';
519 END IF;
520
521 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
522 trace(p_msg => 'BEGIN of procedure load_row',
523 p_module => l_log_module,
524 p_level => C_LEVEL_PROCEDURE);
525 END IF;
526
527 -- Translate owner to file_last_updated_by
528 f_luby := fnd_load_util.owner_id(p_owner);
529
530 -- Translate char last_update_date to date
531 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
532
533 OPEN c_app_id;
534 FETCH c_app_id INTO l_application_id;
535 CLOSE c_app_id;
536
537 BEGIN
538
539 SELECT last_updated_by, last_update_date
540 INTO db_luby, db_ludate
541 FROM xla_tab_acct_types_vl
542 WHERE application_id = l_application_id
543 AND account_type_code = p_account_type_code;
544
545 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, NULL)) THEN
546 xla_tab_acct_types_f_pkg.update_row
547 (x_application_id => l_application_id
548 ,x_account_type_code => p_account_type_code
549 ,x_enabled_flag => p_enabled_flag
550 ,x_rule_assignment_code => p_rule_assignment_code
551 ,x_compile_status_code => p_compile_status_code
555 ,x_last_update_date => f_ludate
552 ,x_object_name_affix => p_object_name_affix
553 ,x_name => p_name
554 ,x_description => p_description
556 ,x_last_updated_by => f_luby
557 ,x_last_update_login => 0);
558
559 END IF;
560
561 EXCEPTION
562 WHEN NO_DATA_FOUND THEN
563 xla_tab_acct_types_f_pkg.insert_row
564 (x_rowid => l_rowid
565 ,x_application_id => l_application_id
566 ,x_account_type_code => p_account_type_code
567 ,x_enabled_flag => p_enabled_flag
568 ,x_rule_assignment_code => p_rule_assignment_code
569 ,x_compile_status_code => p_compile_status_code
570 ,x_object_name_affix => p_object_name_affix
571 ,x_name => p_name
572 ,x_description => p_description
573 ,x_creation_date => f_ludate
574 ,x_created_by => f_luby
575 ,x_last_update_date => f_ludate
576 ,x_last_updated_by => f_luby
577 ,x_last_update_login => 0);
578 END;
579
580 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
581 trace(p_msg => 'END of procedure load_row',
582 p_module => l_log_module,
583 p_level => C_LEVEL_PROCEDURE);
584 END IF;
585
586 EXCEPTION
587 WHEN NO_DATA_FOUND THEN
588 null;
589 WHEN OTHERS THEN
590 xla_exceptions_pkg.raise_message
591 (p_location => 'xla_tab_acct_types_f_pkg.load_row');
592
593 END load_row;
594
595 end xla_tab_acct_types_f_pkg;