[Home] [Help]
PACKAGE BODY: APPS.XLA_TB_DEFINITION_PVT
Source
1 PACKAGE BODY XLA_TB_DEFINITION_PVT AS
2 /* $Header: xlathtbdfn.pkb 120.5.12010000.1 2008/07/29 10:10:42 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_tb_definition_PVT |
10 | |
11 | Description |
12 | This is a XLA package, which contains all the logic required |
13 | to maintain trial balance report definitions |
14 | |
15 | |
16 | HISTORY |
17 | 17-AUG-2005 M.Asada Created |
18 +===========================================================================*/
19
20 C_PACKAGE_NAME CONSTANT VARCHAR2(30) := 'xla_tb_definition_PVT';
21
22
23 --
24 --
25 --+==========================================================================+
26 --| |
27 --| PUBLIC PROCEDURE |
28 --| |
29 --| Create trial balance report definitions |
30 --| |
31 --| |
32 --+==========================================================================+
33 --
34 --
35 PROCEDURE Load_Row
36 (p_definition_code IN VARCHAR2
37 ,p_object_version_number IN VARCHAR2
38 ,p_name IN VARCHAR2
39 ,p_description IN VARCHAR2
40 ,p_ledger_short_name IN vARCHAR2
41 ,p_enabled_flag IN VARCHAR2
42 ,p_balance_side_code IN VARCHAR2
43 ,p_defined_by_code IN VARCHAR2
44 ,p_definition_status_code IN VARCHAR2
45 ,p_defn_owner_code IN VARCHAR2
46 ,p_last_update_date IN VARCHAR2
47 ,p_owner IN VARCHAR2
48 ,p_custom_mode IN VARCHAR2) IS
49
50 CURSOR c_def IS
51 SELECT definition_code
52 ,object_version_number
53 ,last_updated_by
54 ,last_update_date
55 FROM xla_tb_definitions_b
56 WHERE definition_code = p_definition_code;
57
58 CURSOR c_ledger IS
59 SELECT ledger_id
60 FROM gl_ledgers
61 WHERE short_name = p_ledger_short_name;
62
63 l_ledger_id INTEGER;
64 l_definition_code VARCHAR2(30);
65 l_last_updated_by NUMBER; -- owner in file
66 l_last_update_date DATE; -- last update date in file
67 l_db_object_version_number NUMBER; -- object version number in db
68 l_db_last_updated_by NUMBER; -- owner in db
69 l_db_last_update_date DATE; -- last update date in db
70 l_rowid ROWID;
71
72 BEGIN
73
74 l_last_updated_by := fnd_load_util.owner_id(p_owner);
75 l_last_update_date := NVL(TO_DATE(p_last_update_date, 'YYYY/MM/DD'), SYSDATE);
76
77 OPEN c_ledger;
78 FETCH c_ledger
79 INTO l_ledger_id;
80 CLOSE c_ledger;
81
82 OPEN c_def;
83 FETCH c_def
84 INTO l_definition_code
85 ,l_db_object_version_number
86 ,l_db_last_updated_by
87 ,l_db_last_update_date;
88
89 IF (c_def%NOTFOUND) THEN
90
91 l_db_object_version_number := TO_NUMBER(p_object_version_number);
92
93 Insert_Row (
94 p_rowid => l_rowid
95 ,p_definition_code => p_definition_code
96 ,p_object_version_number => l_db_object_version_number
97 ,p_ledger_id => l_ledger_id
98 ,p_enabled_flag => p_enabled_flag
99 ,p_balance_side_code => p_balance_side_code
100 ,p_defined_by_code => p_defined_by_code
101 ,p_definition_status_code => p_definition_status_code
102 ,p_name => p_name
103 ,p_description => p_description
104 ,p_defn_owner_code => p_defn_owner_code
105 ,p_creation_Date => l_last_update_date
106 ,p_Created_By => l_last_updated_by
107 ,p_Last_Update_Date => l_last_update_date
108 ,p_Last_Updated_By => l_last_updated_by
109 ,p_Last_Update_Login => 0);
110
111 ELSE
112 --
113 -- Update columns if allowed (Base)
114 --
115 IF (fnd_load_util.upload_test(
116 p_file_id => l_last_updated_by
117 ,p_file_lud => l_last_update_date
118 ,p_db_id => l_db_last_updated_by
119 ,p_db_lud => l_db_last_update_date
120 ,p_custom_mode => p_custom_mode))
121 THEN
122
123 Update_Row (
124 p_definition_code => p_definition_code
125 ,p_object_version_number => l_db_object_version_number
126 ,p_ledger_id => l_ledger_id
127 ,p_enabled_flag => p_enabled_flag
128 ,p_balance_side_code => p_balance_side_code
129 ,p_defined_by_code => p_defined_by_code
130 ,p_definition_status_code => p_definition_status_code
131 ,p_name => p_name
132 ,p_description => p_description
133 ,p_defn_owner_code => p_defn_owner_code
134 ,p_last_update_date => l_last_update_date
135 ,p_last_updated_by => l_last_updated_by
136 ,p_last_update_Login => 0);
137
138 END IF;
139
140 END IF;
141
142 CLOSE c_def;
143
144 EXCEPTION
145 WHEN xla_exceptions_pkg.application_exception THEN
146 RAISE;
147 WHEN OTHERS THEN
148 xla_exceptions_pkg.raise_message
149 ('XLA' , 'XLA_COMMON_FAILURE'
150 ,'LOCATION' , C_PACKAGE_NAME || '.' || 'load_row'
151 ,'ERROR' , sqlerrm);
152 END Load_Row;
153
154 --
155 --
156 --+==========================================================================+
157 --| |
158 --| PUBLIC PROCEDURE |
159 --| |
160 --| Create trial balance report definitions |
161 --| |
162 --| |
163 --+==========================================================================+
164 --
165 --
166 PROCEDURE Insert_Row
167 (p_rowid IN OUT NOCOPY VARCHAR2
168 ,p_definition_code IN VARCHAR2
169 ,p_object_version_number IN NUMBER
170 ,p_ledger_id IN NUMBER
171 ,p_enabled_flag IN VARCHAR2
172 ,p_balance_side_code IN VARCHAR2
173 ,p_defined_by_code IN VARCHAR2
174 ,p_definition_status_code IN VARCHAR2
175 ,p_name IN VARCHAR2
176 ,p_description IN VARCHAR2
177 ,p_defn_owner_code IN VARCHAR2
178 ,p_creation_date IN DATE
179 ,p_created_by IN NUMBER
180 ,p_last_update_date IN DATE
181 ,p_last_updated_by IN NUMBER
182 ,p_last_update_login IN NUMBER) IS
183
184
185 CURSOR c_tb_b IS
186 SELECT rowid
187 FROM xla_tb_definitions_b
188 WHERE definition_code = p_definition_code;
189
190 BEGIN
191
192 IF p_definition_code IS NULL THEN
193 RAISE no_data_found;
194 END IF;
195
196 INSERT INTO xla_tb_definitions_b
197 (
198 definition_code
199 ,object_version_number
200 ,ledger_id
201 ,enabled_flag
202 ,defined_by_code
203 ,balance_side_code
204 ,definition_status_code
205 ,owner_code
206 ,created_by
207 ,creation_date
208 ,last_updated_by
209 ,last_update_date
210 ,last_update_login
211 )
212 VALUES
213 (
214 p_definition_code
215 ,1 -- Ignore p_object_version_number
216 ,p_ledger_id
217 ,p_enabled_flag
218 ,p_defined_by_code
219 ,p_balance_side_code
220 ,p_definition_status_code
221 ,p_defn_owner_code
222 ,p_created_by
223 ,p_creation_date
224 ,p_last_updated_by
225 ,p_last_update_date
226 ,p_last_update_login
227 );
228
229 INSERT INTO xla_tb_definitions_tl
230 (
231 definition_code
232 ,name
233 ,description
234 ,created_by
235 ,creation_date
236 ,last_updated_by
237 ,last_update_date
238 ,last_update_login
239 ,language
240 ,source_lang
241 )
242 SELECT
243 p_definition_code
244 ,p_name
245 ,p_description
246 ,p_created_by
247 ,p_creation_date
248 ,p_last_updated_by
249 ,p_last_update_date
250 ,p_last_update_login
251 ,l.language_code
252 ,userenv('LANG')
253 FROM fnd_languages l
254 WHERE l.installed_flag in ('I', 'B')
255 AND NOT EXISTS
256 (SELECT NULL
257 FROM xla_tb_definitions_tl t
258 WHERE t.definition_code = p_definition_code
259 AND t.language = l.language_code);
260
261
262 OPEN c_tb_b;
263 FETCH c_tb_b INTO p_rowid;
264
265 IF (c_tb_b%notfound) then
266 CLOSE c_tb_b;
267 RAISE no_data_found;
268 END IF;
269 CLOSE c_tb_b;
270
271 EXCEPTION
272 WHEN xla_exceptions_pkg.application_exception THEN
273 RAISE;
274 WHEN OTHERS THEN
275 xla_exceptions_pkg.raise_message
276 ('XLA' , 'XLA_COMMON_FAILURE'
277 ,'LOCATION' , C_PACKAGE_NAME || '.' || 'insert_row'
278 ,'ERROR' , sqlerrm);
279 END Insert_Row;
280
281 --
282 --
283 --+==========================================================================+
284 --| |
285 --| PUBLIC PROCEDURE |
286 --| |
287 --| Update trial balance report definitions |
288 --| |
289 --| |
290 --+==========================================================================+
291 --
292 --
293 PROCEDURE Update_Row
294 (p_definition_code IN VARCHAR2
295 ,p_object_version_number IN OUT NOCOPY NUMBER
296 ,p_ledger_id IN NUMBER
297 ,p_enabled_flag IN VARCHAR2
298 ,p_balance_side_code IN VARCHAR2
299 ,p_defined_by_code IN VARCHAR2
300 ,p_definition_status_code IN VARCHAR2
301 ,p_name IN VARCHAR2
302 ,p_description IN VARCHAR2
303 ,p_defn_owner_code IN VARCHAR2
304 ,p_last_update_date IN VARCHAR2
305 ,p_last_updated_by IN VARCHAR2
306 ,p_last_update_login IN VARCHAR2) IS
307
308 l_object_version_number NUMBER;
309
310
311 BEGIN
312
313 --
314 -- If -1 is passed, this API update existing record without
315 -- comparing object_version_number pased to th API
316 -- (cf. Datamodel Standard)
317 --
318 IF p_object_version_number = -1 THEN
319
320 --
321 -- Allow update. Increment the database's OVN by 1
322 --
323 SELECT object_version_number
324 INTO l_object_version_number
325 FROM xla_tb_definitions_b
326 WHERE definition_code = p_definition_code;
327
328 l_object_version_number := l_object_version_number + 1;
329
330 ELSE
331
332 --
333 -- Lock the row. Allow update only if the database's OVN equals the one
334 -- passed in.
335 --
336 -- If update is allowed, increment the database's OVN by 1.
337 -- Otherwise, raise an error.
338 --
339
340 SELECT object_version_number
341 INTO l_object_version_number
342 FROM xla_tb_definitions_b
343 WHERE definition_code = p_definition_code
344 FOR UPDATE;
345
346 IF (l_object_version_number = p_object_version_number) THEN
347
348 l_object_version_number := l_object_version_number + 1;
349
350 ELSE
351
352 --
353 -- record already updated
354 --
355 fnd_message.set_name('XLA','XLA_COMMON_ROW_UPDATED');
356 xla_exceptions_pkg.raise_exception;
357
358 END IF;
359
360 END IF;
361
362 UPDATE xla_tb_definitions_b
363 SET object_version_number = l_object_version_number
364 ,ledger_id = p_ledger_id
365 ,enabled_flag = p_enabled_flag
366 ,balance_side_code = p_balance_side_code
367 ,defined_by_code = p_defined_by_code
368 ,definition_status_code = p_definition_status_code
369 ,owner_code = p_defn_owner_code
370 ,last_update_date = p_last_update_date
371 ,last_updated_by = p_last_updated_by
372 ,last_update_login = p_last_update_login
373 WHERE definition_code = p_definition_code;
374
375 IF (sql%NOTFOUND) THEN
376 RAISE no_data_found;
377 END IF;
378
379 UPDATE xla_tb_definitions_tl
380 SET name = p_name
381 ,description = p_description
382 ,last_update_date = p_last_update_date
383 ,last_updated_by = p_last_updated_by
387 AND userenv('LANG') IN (language, source_lang);
384 ,last_update_login = p_last_update_login
385 ,source_lang = userenv('LANG')
386 WHERE definition_code = p_definition_code
388
389 IF (sql%notfound) THEN
390 RAISE no_data_found;
391 END IF;
392
393 p_object_version_number := l_object_version_number;
394
395 EXCEPTION
396 WHEN xla_exceptions_pkg.application_exception THEN
397 RAISE;
398 WHEN OTHERS THEN
399 xla_exceptions_pkg.raise_message
400 ('XLA' , 'XLA_COMMON_FAILURE'
401 ,'LOCATION' , C_PACKAGE_NAME || '.' || 'update_row'
402 ,'ERROR' , sqlerrm);
403 END Update_Row;
404
405 --
406 --
407 --+==========================================================================+
408 --| |
409 --| PUBLIC PROCEDURE |
410 --| |
411 --| Delete trial balance report definitions |
412 --| |
413 --| |
414 --+==========================================================================+
415 --
416 --
417 PROCEDURE Delete_Row
418 (p_definition_code IN VARCHAR2) IS
419 BEGIN
420
421 DELETE FROM xla_tb_defn_details
422 WHERE definition_code = p_definition_code;
423
424 DELETE FROM xla_tb_definitions_tl
425 WHERE definition_code = p_definition_code;
426
427 IF SQL%NOTFOUND then
428 RAISE no_data_found;
429 END IF;
430
431 DELETE FROM xla_tb_definitions_b
432 WHERE definition_code = p_definition_code;
433
434 IF (sql%notfound) then
435 RAISE no_data_found;
436 END IF;
437
438 drop_partition
439 (p_definition_code => p_definition_code);
440
441 EXCEPTION
442 WHEN xla_exceptions_pkg.application_exception THEN
443 RAISE;
444 WHEN OTHERS THEN
445 xla_exceptions_pkg.raise_message
446 ('XLA' , 'XLA_COMMON_FAILURE'
447 ,'LOCATION' , C_PACKAGE_NAME || '.' || 'delete_row'
448 ,'ERROR' , sqlerrm);
449 END Delete_Row;
450
451 --
452 --
453 --+==========================================================================+
454 --| |
455 --| PUBLIC PROCEDURE |
456 --| |
457 --| Add language rows |
458 --| |
459 --| |
460 --+==========================================================================+
461 --
462 --
463 PROCEDURE Add_Language IS
464
465
466 BEGIN
467
468 DELETE FROM xla_tb_definitions_tl t
469 WHERE NOT EXISTS
470 (SELECT NULL
471 FROM xla_tb_definitions_b b
472 WHERE b.definition_code = t.definition_code
473 );
474
475 UPDATE xla_tb_definitions_tl t
476 SET (
477 NAME
478 ,description
479 ) =
480 (
481 SELECT b.NAME
482 ,b.description
483 FROM xla_tb_definitions_tl b
484 WHERE b.definition_code = t.definition_code
485 AND b.language = t.source_lang)
486 WHERE (
487 t.definition_code
488 ,t.language
489 ) IN (SELECT subt.definition_code
490 ,subt.language
491 FROM xla_tb_definitions_tl subb
492 ,xla_tb_definitions_tl subt
493 WHERE subb.definition_code = subt.definition_code
494 AND subb.language = subt.source_lang
495 AND (subb.NAME <> subt.NAME
496 OR subb.description <> subt.description
497 OR (subb.description IS NULL AND subt.description IS NOT NULL)
498 OR (subb.description IS NOT NULL AND subt.description IS NULL)
499 )
500 );
501
502 INSERT INTO xla_tb_definitions_tl
503 (
504 definition_code
505 ,name
506 ,description
507 ,creation_date
508 ,created_by
509 ,last_update_date
511 ,last_update_login
512 ,language
513 ,source_lang
514 )
515 SELECT /*+ ORDERED */
516 b.definition_code
517 ,b.name
518 ,b.description
519 ,b.creation_date
520 ,b.created_by
521 ,b.last_update_date
522 ,b.last_updated_by
523 ,b.last_update_login
524 ,l.language_code
525 ,b.source_lang
526 FROM xla_tb_definitions_tl b, fnd_languages l
527 WHERE l.installed_flag IN ('I', 'B')
528 AND b.language = userenv('LANG')
529 AND NOT EXISTS
530 (SELECT NULL
531 FROM xla_tb_definitions_tl t
532 WHERE t.definition_code = b.definition_code
533 AND t.language = l.language_code);
534
535 EXCEPTION
536 WHEN xla_exceptions_pkg.application_exception THEN
537 RAISE;
538 WHEN OTHERS THEN
539 xla_exceptions_pkg.raise_message
540 ('XLA' , 'XLA_COMMON_FAILURE'
541 ,'LOCATION' , C_PACKAGE_NAME || '.' || 'add_language'
542 ,'ERROR' , sqlerrm);
543 END Add_Language;
544
545
546 --
547 --
548 --+==========================================================================+
549 --| |
550 --| PUBLIC PROCEDURE |
551 --| |
552 --| Update translateable attributes of trial balance report definitions |
553 --| |
554 --| |
555 --+==========================================================================+
556 --
557 --
558 PROCEDURE Translate_Row
559 (p_definition_code IN VARCHAR2
560 ,p_name IN VARCHAR2
561 ,p_description IN VARCHAR2
562 ,p_last_update_date IN NUMBER
563 ,p_owner IN VARCHAR2
564 ,p_custom_mode IN VARCHAR2) IS
565
566 CURSOR c_tl IS
567 SELECT last_updated_by
568 ,last_update_date
569 FROM xla_tb_definitions_tl
570 WHERE definition_code = p_definition_code
571 AND LANGUAGE = userenv('LANG');
572
573 l_definition_code VARCHAR2(30);
574 l_last_updated_by NUMBER; -- owner in file
575 l_last_update_date DATE; -- last update date in file
576 l_db_last_updated_by NUMBER; -- owner in db
577 l_db_last_update_date DATE; -- last update date in db
578
579 BEGIN
580 l_last_updated_by := fnd_load_util.owner_id(p_owner);
581 l_last_update_date := NVL(TO_DATE(p_last_update_date, 'YYYY/MM/DD'), SYSDATE);
582
583 OPEN c_tl;
584 FETCH c_tl
585 INTO l_db_last_updated_by
586 ,l_db_last_update_date;
587
588 IF (c_tl%NOTFOUND) THEN
589 NULL;
590 ELSE
591
592 IF fnd_load_util.upload_test(
593 p_file_id => l_last_updated_by
594 ,p_file_lud => l_last_update_date
595 ,p_db_id => l_db_last_updated_by
596 ,p_db_lud => l_db_last_update_date
597 ,p_custom_mode => p_custom_mode)
598 THEN
599 UPDATE xla_tb_definitions_tl
600 SET name = p_name
601 ,description = p_description
602 ,last_updated_by = l_last_updated_by
603 ,last_update_date = l_last_update_date
604 ,last_update_login = 0
605 ,source_lang = userenv('LANG')
606 WHERE definition_code = p_definition_code
607 AND userenv('LANG') IN (language, source_lang);
608
609 END IF;
610
611 END IF;
612
613
614 END Translate_Row;
615
616 PROCEDURE Drop_Partition
617 (p_definition_code IN VARCHAR2) IS
618
619 l_schema VARCHAR2(30);
620 l_status VARCHAR2(30);
621 l_industry VARCHAR2(30);
622 BEGIN
623
624 IF (FND_INSTALLATION.get_app_info
625 (application_short_name => 'XLA'
626 ,status => l_status
627 ,industry => l_industry
628 ,oracle_schema => l_schema))
629 THEN
630
631 l_schema := l_schema || '.';
632
633 ELSE
634
635 l_schema := '';
636
637 END IF;
638
643 WHEN xla_exceptions_pkg.application_exception THEN
510 ,last_updated_by
639 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema ||'xla_trial_balance drop partition '
640 ||p_definition_code;
641
642 EXCEPTION
644 RAISE;
645 WHEN OTHERS THEN
646 xla_exceptions_pkg.raise_message
647 ('XLA' , 'XLA_COMMON_FAILURE'
648 ,'LOCATION' , C_PACKAGE_NAME || '.' || 'drop_partition'
649 ,'ERROR' , sqlerrm);
650 END Drop_Partition;
651
652
653 END XLA_TB_DEFINITION_PVT; -- end of package spec