[Home] [Help]
PACKAGE BODY: APPS.XLA_SUBLEDGERS_F_PKG
Source
1 PACKAGE BODY XLA_SUBLEDGERS_F_PKG AS
2 /* $Header: xlatbapp.pkb 120.29.12010000.2 2009/12/28 09:23:33 vkasina ship $ */
3 /*======================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_subledgers |
10 | |
11 | DESCRIPTION |
12 | Forms PL/SQL Wrapper for xla_subledgers |
13 | |
14 | HISTORY |
15 | 6/11/2002 W Chan Created. |
16 | 10/09/2003 W Chan Fix bug 3175882 - call security API on update |
17 | 10/09/2003 W Chan Fix bug 3175319 - create table partition when |
18 | register application |
19 | |
20 +======================================================================*/
21
22 -------------------------------------------------------------------------------
23 -- declaring private constants
24 -------------------------------------------------------------------------------
25 C_MANUAL CONSTANT VARCHAR2(10) := 'MANUAL';
26 C_TPM CONSTANT VARCHAR2(30) := 'THIRD_PARTY_MERGE';
27
28
29 --=============================================================================
30 -- *********** Local Trace Routine **********
31 --=============================================================================
32 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
33 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
34 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
35 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
36 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
37 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
38
39 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
40 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_subledgers_f_pkg';
41
42 g_debug_flag VARCHAR2(1) :=
43 NVL(fnd_profile.value('XLA_DEBUG_TRACE'),'N');
44
45 g_log_level NUMBER;
46 g_log_enabled BOOLEAN;
47
48 PROCEDURE trace
49 (p_msg IN VARCHAR2
50 ,p_module IN VARCHAR2
51 ,p_level IN NUMBER) IS
52 BEGIN
53 ----------------------------------------------------------------------------
54 -- Following is for FND log.
55 ----------------------------------------------------------------------------
56 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
57 fnd_log.message(p_level, p_module);
58 ELSIF p_level >= g_log_level THEN
59 fnd_log.string(p_level, p_module, p_msg);
60 END IF;
61
62 EXCEPTION
63 WHEN xla_exceptions_pkg.application_exception THEN
64 RAISE;
65
66 WHEN OTHERS THEN
67 xla_exceptions_pkg.raise_message
68 (p_location => 'xla_subledgers_f_pkg.trace');
69 END trace;
70
71 --=============================================================================
72 --
73 --
74 --
75 --
76 -- *********** private procedures and functions **********
77 --
78 --
79 --
80 --
81 --=============================================================================
82
83 --=============================================================================
84 --
85 -- Name: get_schema
86 -- Description: Retrieve the schema name for XLA
87 --
88 -- Return: If schema is found, the schema name is returned. Else, null is
89 -- returned.
90 --
91 --=============================================================================
92 FUNCTION get_schema
93 RETURN VARCHAR2
94 IS
95 l_status VARCHAR2(30);
96 l_industry VARCHAR2(30);
97 l_schema VARCHAR2(30);
98 l_retcode BOOLEAN;
99
100 l_log_module VARCHAR2(240);
101 BEGIN
102 IF g_log_enabled THEN
103 l_log_module := C_DEFAULT_MODULE||'.get_schema';
104 END IF;
105
106 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
107 trace(p_msg => 'BEGIN of function get_schema',
108 p_module => l_log_module,
109 p_level => C_LEVEL_PROCEDURE);
110 END IF;
111
112 IF (NOT FND_INSTALLATION.get_app_info
113 (application_short_name => 'XLA'
114 ,status => l_status
115 ,industry => l_industry
116 ,oracle_schema => l_schema)) THEN
117 l_schema := NULL;
118 END IF;
119
120 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
121 trace(p_msg => 'END of function get_schema',
122 p_module => l_log_module,
123 p_level => C_LEVEL_PROCEDURE);
124 END IF;
125
126 RETURN l_schema;
127 EXCEPTION
128 WHEN xla_exceptions_pkg.application_exception THEN
129 RAISE;
130
131 WHEN OTHERS THEN
132 xla_exceptions_pkg.raise_message
133 (p_location => 'xla_subledgers_f_pkg.get_schema');
134
135 END get_schema;
136
137 /*======================================================================+
138 | |
139 | NAME: update_one_partition |
140 | Description: The procedure updates the partition to one partitioined |
141 | table. |
142 | |
143 +======================================================================*/
144
145 PROCEDURE update_one_partition
146 (p_app_id IN INTEGER
147 ,p_app_short_name IN VARCHAR2
148 ,p_schema IN VARCHAR2
149 ,p_table IN VARCHAR2
150 ,p_action IN VARCHAR2)
151 IS
152 --partition_exists EXCEPTION;
153 --PRAGMA EXCEPTION_INIT(partition_exists,-14312);
154
155 l_log_module VARCHAR2(240);
156 BEGIN
157 IF g_log_enabled THEN
158 l_log_module := C_DEFAULT_MODULE||'.update_one_partition';
159 END IF;
160
161 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
162 trace(p_msg => 'BEGIN of procedure update_one_partition',
163 p_module => l_log_module,
164 p_level => C_LEVEL_PROCEDURE);
165 trace(p_msg => 'p_table = '||p_table,
166 p_module => l_log_module,
167 p_level => C_LEVEL_PROCEDURE);
168 END IF;
169
170 BEGIN
171 IF (p_action = 'ADD') THEN
172 EXECUTE IMMEDIATE
173 'ALTER TABLE '||p_schema||' '||p_table||' ADD PARTITION '||p_app_short_name||
174 ' VALUES ('||p_app_id||' )';
175 ELSE
176 EXECUTE IMMEDIATE
177 'ALTER TABLE '||p_schema||' '||p_table||' DROP PARTITION '||p_app_short_name;
178 END IF;
179
180 EXCEPTION
181 WHEN OTHERS THEN
182 NULL;
183 END;
184
185 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
186 trace(p_msg => 'END of procedure update_one_partition',
187 p_module => l_log_module,
188 p_level => C_LEVEL_PROCEDURE);
189 END IF;
190
191 EXCEPTION
192 WHEN xla_exceptions_pkg.application_exception THEN
193 RAISE;
194 END;
195
196
197 /*======================================================================+
198 | |
199 | NAME: update_partitions |
200 | Description: The procedure updates the partition to all partitioined |
201 | tables. |
202 | |
203 +======================================================================*/
204
205 PROCEDURE update_partitions
206 ( p_app_id IN INTEGER
207 ,p_action IN VARCHAR2
208 ) IS
209
210 CURSOR c IS
211 SELECT application_short_name
212 FROM fnd_application
213 WHERE application_id = p_app_id;
214
215 l_schema VARCHAR2(30);
216 l_app_short_name VARCHAR2(30);
217 l_log_module VARCHAR2(240);
218 BEGIN
219 IF g_log_enabled THEN
220 l_log_module := C_DEFAULT_MODULE||'.update_partitions';
221 END IF;
222
223 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
224 trace(p_msg => 'BEGIN of procedure update_partitions',
225 p_module => l_log_module,
226 p_level => C_LEVEL_PROCEDURE);
227 trace(p_msg => 'p_app_id = '||p_app_id,
228 p_module => l_log_module,
229 p_level => C_LEVEL_PROCEDURE);
230 trace(p_msg => 'p_action = '||p_action,
231 p_module => l_log_module,
232 p_level => C_LEVEL_PROCEDURE);
233 END IF;
234
235 OPEN c;
236 FETCH c INTO l_app_short_name;
237 if (c%NOTFOUND) then
238 CLOSE c;
239 RAISE NO_DATA_FOUND;
240 end if;
241 CLOSE c;
242
243 l_schema := get_schema;
244 IF (l_schema IS NULL) THEN
245 l_schema := '';
246 ELSE
247 l_schema := l_schema || '.';
248 END IF;
249
250 -- Add partition
251 update_one_partition
252 (p_app_id => p_app_id
253 ,p_app_short_name => l_app_short_name
254 ,p_schema => l_schema
255 ,p_table => 'XLA_TRANSACTION_ENTITIES'
256 ,p_action => p_action);
257
258 update_one_partition
259 (p_app_id => p_app_id
260 ,p_app_short_name => l_app_short_name
261 ,p_schema => l_schema
262 ,p_table => 'XLA_EVENTS'
263 ,p_action => p_action);
264
265 update_one_partition
266 (p_app_id => p_app_id
267 ,p_app_short_name => l_app_short_name
268 ,p_schema => l_schema
269 ,p_table => 'XLA_AE_HEADERS'
270 ,p_action => p_action);
271
272 update_one_partition
273 (p_app_id => p_app_id
274 ,p_app_short_name => l_app_short_name
275 ,p_schema => l_schema
276 ,p_table => 'XLA_AE_LINES'
277 ,p_action => p_action);
278
279 update_one_partition
280 (p_app_id => p_app_id
281 ,p_app_short_name => l_app_short_name
282 ,p_schema => l_schema
283 ,p_table => 'XLA_DISTRIBUTION_LINKS'
284 ,p_action => p_action);
285
286 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
287 trace(p_msg => 'END of procedure update_partitions',
288 p_module => l_log_module,
289 p_level => C_LEVEL_PROCEDURE);
290 END IF;
291
292 EXCEPTION
293 WHEN xla_exceptions_pkg.application_exception THEN
294 RAISE;
295 END;
296
297 /*======================================================================+
298 | |
299 | NAME: insert_tpm |
300 | Description: The procedure inserts special event information for |
301 | third party merge. |
302 | |
303 +======================================================================*/
304
305 PROCEDURE insert_tpm
306 (p_application_id IN NUMBER
307 ,p_creation_date IN DATE
308 ,p_created_by IN NUMBER
309 ,p_last_update_date IN DATE
310 ,p_last_updated_by IN NUMBER
311 ,p_last_update_login IN NUMBER)IS
312
313 l_row_id VARCHAR2(80);
314 l_log_module VARCHAR2(240);
315 BEGIN
316
317 IF g_log_enabled THEN
318 l_log_module := C_DEFAULT_MODULE||'.update_one_partition';
319 END IF;
320
321 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
322 trace(p_msg => 'BEGIN of procedure update_one_partition',
323 p_module => l_log_module,
324 p_level => C_LEVEL_PROCEDURE);
325 trace(p_msg => 'p_application_id = '||p_application_id,
326 p_module => l_log_module,
327 p_level => C_LEVEL_PROCEDURE);
328 END IF;
329
330 --
331 -- Create Third Party Merge event entity
332 --
333 xla_entity_types_f_pkg.insert_row(
334 x_rowid => l_row_id
335 ,x_application_id => p_application_id
336 ,x_entity_code => C_TPM
337 ,x_enabled_flag => 'Y'
338 ,x_enable_gapless_events_flag => 'N'
339 ,x_name => 'Third Party Merge'
340 ,x_description => 'Special event entity created for third party merge entry'
341 ,x_creation_date => p_creation_date
342 ,x_created_by => p_created_by
343 ,x_last_update_date => p_last_update_date
344 ,x_last_updated_by => p_last_updated_by
345 ,x_last_update_login => p_last_update_login);
346
347 --
348 -- Create Third Party Merge entity id mapping
349 --
350 INSERT INTO xla_entity_id_mappings
351 (application_id
352 ,entity_code
353 ,creation_date
354 ,created_by
355 ,last_update_date
356 ,last_updated_by
357 ,last_update_login)
358 VALUES
359 (p_application_id
360 ,C_TPM
361 ,p_creation_date
362 ,p_created_by
363 ,p_last_update_date
364 ,p_last_updated_by
365 ,p_last_update_login);
366
367 --
368 -- Create Third Party Merge event class
369 --
370 xla_event_classes_f_pkg.insert_row(
371 x_rowid => l_row_id
372 ,x_application_id => p_application_id
373 ,x_entity_code => C_TPM
374 ,x_event_class_code => C_TPM
375 ,x_enabled_flag => 'Y'
376 ,x_name => 'Third Party Merge'
377 ,x_description => 'Special event class created for third party merge entry'
378 ,x_creation_date => p_creation_date
379 ,x_created_by => p_created_by
380 ,x_last_update_date => p_last_update_date
381 ,x_last_updated_by => p_last_updated_by
382 ,x_last_update_login => p_last_update_login);
383
384 --
385 -- Create Full Merge event type
386 --
387 xla_event_types_f_pkg.insert_row(
388 x_rowid => l_row_id
389 ,x_application_id => p_application_id
390 ,x_entity_code => C_TPM
391 ,x_event_class_code => C_TPM
392 ,x_event_type_code => 'FULL_MERGE'
393 ,x_accounting_flag => 'Y'
394 ,x_tax_flag => 'N'
395 ,x_enabled_flag => 'Y'
396 ,x_name => 'Full Merge'
397 ,x_description => 'Special event type created for third party merge entry'
398 ,x_creation_date => p_creation_date
399 ,x_created_by => p_created_by
400 ,x_last_update_date => p_last_update_date
401 ,x_last_updated_by => p_last_updated_by
402 ,x_last_update_login => p_last_update_login);
403
404 --
405 -- Create Partial Merge event type
406 --
407 xla_event_types_f_pkg.insert_row(
408 x_rowid => l_row_id
409 ,x_application_id => p_application_id
410 ,x_entity_code => C_TPM
411 ,x_event_class_code => C_TPM
412 ,x_event_type_code => 'PARTIAL_MERGE'
413 ,x_accounting_flag => 'Y'
414 ,x_tax_flag => 'N'
415 ,x_enabled_flag => 'Y'
416 ,x_name => 'Partial Merge'
417 ,x_description => 'Special event type created for third party merge entry'
418 ,x_creation_date => p_creation_date
419 ,x_created_by => p_created_by
420 ,x_last_update_date => p_last_update_date
421 ,x_last_updated_by => p_last_updated_by
422 ,x_last_update_login => p_last_update_login);
423
424 --
425 -- Create special event class group for manual
426 --
427 xla_event_class_grps_f_pkg.insert_row(
428 x_rowid => l_row_id
429 ,x_application_id => p_application_id
430 ,x_event_class_group_code => C_TPM
431 ,x_enabled_flag => 'Y'
432 ,x_name => 'Third Party Merge'
433 ,x_description => 'Special event class group created for third party merge entry'
434 ,x_creation_date => p_creation_date
435 ,x_created_by => p_created_by
436 ,x_last_update_date => p_last_update_date
437 ,x_last_updated_by => p_last_updated_by
438 ,x_last_update_login => p_last_update_login);
439
440 --
441 -- Create special event class attrs for manual
442 --
443 xla_event_class_attrs_f_pkg.insert_row(
444 x_rowid => l_row_id
445 ,x_application_id => p_application_id
446 ,x_entity_code => C_TPM
447 ,x_event_class_code => C_TPM
448 ,x_event_class_group_code => C_TPM
449 ,x_je_category_name => 'Other'
450 ,x_reporting_view_name => null
451 ,x_allow_actuals_flag => 'Y'
452 ,x_allow_budgets_flag => 'N'
453 ,x_allow_encumbrance_flag => 'N'
454 ,x_calculate_acctd_amts_flag => 'N'
455 ,x_calculate_g_l_amts_flag => 'N'
456 ,x_creation_date => p_creation_date
457 ,x_created_by => p_created_by
458 ,x_last_update_date => p_last_update_date
459 ,x_last_updated_by => p_last_updated_by
460 ,x_last_update_login => p_last_update_login);
461
462 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
463 trace(p_msg => 'END of procedure insert_tpm',
464 p_module => l_log_module,
465 p_level => C_LEVEL_PROCEDURE);
466 END IF;
467
468 EXCEPTION
469 WHEN xla_exceptions_pkg.application_exception THEN
470 RAISE;
471 END insert_tpm;
472
473 /*======================================================================+
474 | |
475 | NAME: insert_tpm |
476 | Description: The procedure deletes special event information for |
477 | third party merge. |
478 | |
479 +======================================================================*/
480 PROCEDURE delete_tpm
481 (p_application_id IN NUMBER)
482 IS
483
484 BEGIN
485
486 xla_event_class_grps_f_pkg.delete_row
487 (x_application_id => p_application_id
488 ,x_event_class_group_code => C_TPM);
489
490 xla_event_class_attrs_f_pkg.delete_row
491 (x_application_id => p_application_id
492 ,x_entity_code => C_TPM
493 ,x_event_class_code => C_TPM);
494
495 xla_event_types_f_pkg.delete_row
496 (x_application_id => p_application_id
497 ,x_entity_code => C_TPM
498 ,x_event_class_code => C_TPM
499 ,x_event_type_code => 'FULL_MERGE');
500
501 xla_event_types_f_pkg.delete_row
502 (x_application_id => p_application_id
503 ,x_entity_code => C_TPM
504 ,x_event_class_code => C_TPM
505 ,x_event_type_code => 'PARTIAL_MERGE');
506
507 DELETE FROM xla_entity_id_mappings
508 WHERE application_id = p_application_id
509 AND entity_code = C_TPM
510 ;
511
512 xla_event_classes_f_pkg.delete_row
513 (x_application_id => p_application_id
514 ,x_entity_code => C_TPM
515 ,x_event_class_code => C_TPM);
516
517 xla_entity_types_f_pkg.delete_row
518 (x_application_id => p_application_id
519 ,x_entity_code => C_TPM);
520
521 EXCEPTION
522 WHEN xla_exceptions_pkg.application_exception THEN
523 RAISE;
524 END delete_tpm;
525
526 --=============================================================================
527 --
528 --
529 --
530 --
531 -- *********** public procedures and functions **********
532 --
533 --
534 --
535 --
536 --=============================================================================
537
538 /*======================================================================+
539 | |
540 | Procedure insert_row |
541 | |
542 +======================================================================*/
543 PROCEDURE insert_row
544 (x_rowid IN OUT NOCOPY VARCHAR2
545 ,x_application_id IN NUMBER
546 ,x_application_type_code IN VARCHAR2
547 ,x_je_source_name IN VARCHAR2
548 ,x_valuation_method_flag IN VARCHAR2
549 ,x_drilldown_procedure_name IN VARCHAR2
550 ,x_security_function_name IN VARCHAR2
551 ,x_control_account_type_code IN VARCHAR2
552 ,x_alc_enabled_flag IN VARCHAR2
553 ,x_creation_date IN DATE
554 ,x_created_by IN NUMBER
555 ,x_last_update_date IN DATE
556 ,x_last_updated_by IN NUMBER
557 ,x_last_update_login IN NUMBER)
558
559 IS
560
561 CURSOR c IS
562 SELECT rowid
563 FROM xla_subledgers
564 WHERE application_id = x_application_id;
565
566 CURSOR c1 IS
567 SELECT rowid
568 FROM xla_entity_id_mappings
569 WHERE application_id = x_application_id AND
570 entity_code = C_MANUAL;
571
572 CURSOR c2 IS
573 SELECT application_short_name
574 FROM fnd_application
575 WHERE application_id = x_application_id;
576
577 l_event_entity_row_id VARCHAR2(240);
578 l_event_class_row_id VARCHAR2(240);
579 l_event_type_row_id VARCHAR2(240);
580 l_event_class_attr_row_id VARCHAR2(240);
581 l_event_class_grps_row_id VARCHAR2(240);
582 l_app_short_name VARCHAR2(30);
583 l_schema VARCHAR2(30);
584 l_rowid VARCHAR2(80);
585
586 l_log_module VARCHAR2(240);
587 BEGIN
588
589 IF g_log_enabled THEN
590 l_log_module := C_DEFAULT_MODULE||'.insert_row';
591 END IF;
592
593 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
594 trace(p_msg => 'BEGIN of procedure insert_row',
595 p_module => l_log_module,
596 p_level => C_LEVEL_PROCEDURE);
597 END IF;
598
599 OPEN c2;
600 FETCH c2 INTO l_app_short_name;
601 if (c2%NOTFOUND) then
602 CLOSE c2;
603 RAISE NO_DATA_FOUND;
604 end if;
605 CLOSE c2;
606
607 INSERT INTO xla_subledgers
608 (creation_date
609 ,created_by
610 ,application_id
611 ,application_type_code
612 ,je_source_name
613 ,valuation_method_flag
614 ,drilldown_procedure_name
615 ,security_function_name
616 ,control_account_type_code
617 ,alc_enabled_flag
618 ,last_update_date
619 ,last_updated_by
620 ,last_update_login)
621 VALUES
622 (x_creation_date
623 ,x_created_by
624 ,x_application_id
625 ,x_application_type_code
626 ,x_je_source_name
627 ,x_valuation_method_flag
628 ,x_drilldown_procedure_name
629 ,x_security_function_name
630 ,x_control_account_type_code
631 ,x_alc_enabled_flag
632 ,x_last_update_date
633 ,x_last_updated_by
634 ,x_last_update_login);
635
636 OPEN c;
637 FETCH c INTO x_rowid;
638
639 IF (c%NOTFOUND) THEN
640 CLOSE c;
641 RAISE NO_DATA_FOUND;
642 END IF;
643 CLOSE c;
644
645 --
646 -- Initiate the Transaction Security mechanism for the subledger application
647 --
648 xla_security_pkg.set_subledger_security(
649 p_application_id => x_application_id
650 ,p_security_function_name => x_security_function_name);
651
652 --
653 -- Create special event entity for manual entries
654 --
655 xla_entity_types_f_pkg.insert_row(
656 x_rowid => l_event_entity_row_id
657 ,x_application_id => x_application_id
658 ,x_entity_code => C_MANUAL
659 ,x_enabled_flag => 'Y'
660 ,x_enable_gapless_events_flag => 'N'
661 ,x_name => 'Manual'
662 ,x_description => 'Special event entity created for manual entry'
663 ,x_creation_date => x_creation_date
664 ,x_created_by => x_created_by
665 ,x_last_update_date => x_last_update_date
666 ,x_last_updated_by => x_last_updated_by
667 ,x_last_update_login => x_last_update_login);
668
669 --
670 -- Create special entity id mapping for manual entries
671 --
672 INSERT INTO xla_entity_id_mappings
673 (application_id
674 ,entity_code
675 ,creation_date
676 ,created_by
677 ,last_update_date
678 ,last_updated_by
679 ,last_update_login)
680 VALUES
681 (x_application_id
682 ,C_MANUAL
683 ,x_creation_date
684 ,x_created_by
685 ,x_last_update_date
686 ,x_last_updated_by
687 ,x_last_update_login);
688
689 OPEN c1;
690 FETCH c1 INTO l_rowid;
691
692 IF (c1%NOTFOUND) THEN
693 CLOSE c1;
694 RAISE NO_DATA_FOUND;
695 END IF;
696 CLOSE c1;
697
698 --
699 -- Create special event class for manual entries
700 --
701 xla_event_classes_f_pkg.insert_row(
702 x_rowid => l_event_class_row_id
703 ,x_application_id => x_application_id
704 ,x_entity_code => C_MANUAL
705 ,x_event_class_code => C_MANUAL
706 ,x_enabled_flag => 'Y'
707 ,x_name => 'Manual'
708 ,x_description => 'Special event class created for manual entry'
709 ,x_creation_date => x_creation_date
710 ,x_created_by => x_created_by
711 ,x_last_update_date => x_last_update_date
712 ,x_last_updated_by => x_last_updated_by
713 ,x_last_update_login => x_last_update_login);
714
715 --
716 -- Create special event type for manual entries
717 --
718 xla_event_types_f_pkg.insert_row(
719 x_rowid => l_event_type_row_id
720 ,x_application_id => x_application_id
721 ,x_entity_code => C_MANUAL
722 ,x_event_class_code => C_MANUAL
723 ,x_event_type_code => C_MANUAL
724 ,x_accounting_flag => 'Y'
725 ,x_tax_flag => 'Y'
726 ,x_enabled_flag => 'Y'
727 ,x_name => 'Manual'
728 ,x_description => 'Special event type created for manual entry'
729 ,x_creation_date => x_creation_date
730 ,x_created_by => x_created_by
731 ,x_last_update_date => x_last_update_date
732 ,x_last_updated_by => x_last_updated_by
733 ,x_last_update_login => x_last_update_login);
734
735 --
736 -- Create special event class group for manual
737 --
738 xla_event_class_grps_f_pkg.insert_row(
739 x_rowid => l_event_class_grps_row_id
740 ,x_application_id => x_application_id
741 ,x_event_class_group_code => C_MANUAL
742 ,x_enabled_flag => 'Y'
743 ,x_name => 'Manual'
744 ,x_description => 'Special event class group created for manual entry'
745 ,x_creation_date => x_creation_date
746 ,x_created_by => x_created_by
747 ,x_last_update_date => x_last_update_date
748 ,x_last_updated_by => x_last_updated_by
749 ,x_last_update_login => x_last_update_login);
750
751 --
752 -- Create special event class attrs for manual
753 --
754 xla_event_class_attrs_f_pkg.insert_row(
755 x_rowid => l_event_class_attr_row_id
756 ,x_application_id => x_application_id
757 ,x_entity_code => C_MANUAL
758 ,x_event_class_code => C_MANUAL
759 ,x_event_class_group_code => C_MANUAL
760 ,x_je_category_name => 'Other'
761 ,x_reporting_view_name => null
762 ,x_allow_actuals_flag => 'Y'
763 ,x_allow_budgets_flag => 'Y'
764 ,x_allow_encumbrance_flag => 'Y'
765 ,x_calculate_acctd_amts_flag => 'N'
766 ,x_calculate_g_l_amts_flag => 'N'
767 ,x_creation_date => x_creation_date
768 ,x_created_by => x_created_by
769 ,x_last_update_date => x_last_update_date
770 ,x_last_updated_by => x_last_updated_by
771 ,x_last_update_login => x_last_update_login);
772
773
774 --
775 -- Create Third Party Merge Special Events
776 --
777 insert_tpm(
778 p_application_id => x_application_id
779 ,p_creation_date => x_creation_date
780 ,p_created_by => x_created_by
781 ,p_last_update_date => x_last_update_date
782 ,p_last_updated_by => x_last_updated_by
783 ,p_last_update_login => x_last_update_login);
784
785 -- Add a new partition
786 update_partitions(p_app_id => x_application_id
787 ,p_action => 'ADD');
788
789 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
790 trace(p_msg => 'END of procedure insert_row',
791 p_module => l_log_module,
792 p_level => C_LEVEL_PROCEDURE);
793 END IF;
794
795 EXCEPTION
796 WHEN xla_exceptions_pkg.application_exception THEN
797 ROLLBACK;
798 if (c%ISOPEN) then
799 close c;
800 end if;
801 if (c1%ISOPEN) then
802 close c1;
803 end if;
804 if (c2%ISOPEN) then
805 close c2;
806 end if;
807 RAISE;
808 WHEN OTHERS THEN
809 ROLLBACK;
810 if (c%ISOPEN) then
811 close c;
812 end if;
813 if (c1%ISOPEN) then
814 close c1;
815 end if;
816 if (c2%ISOPEN) then
817 close c2;
818 end if;
819 xla_exceptions_pkg.raise_message
820 (p_location => 'xla_subledgers_f_pkg.insert_row');
821
822 END insert_row;
823
824 /*======================================================================+
825 | |
826 | Procedure lock_row |
827 | |
828 +======================================================================*/
829 PROCEDURE lock_row
830 (x_application_id IN NUMBER
831 ,x_application_type_code IN VARCHAR2
832 ,x_je_source_name IN VARCHAR2
833 ,x_valuation_method_flag IN VARCHAR2
834 ,x_drilldown_procedure_name IN VARCHAR2
835 ,x_security_function_name IN VARCHAR2
836 ,x_control_account_type_code IN VARCHAR2
837 ,x_alc_enabled_flag IN VARCHAR2)
838
839 IS
840
841 CURSOR c IS
842 SELECT application_id
843 ,application_type_code
844 ,je_source_name
845 ,valuation_method_flag
846 ,drilldown_procedure_name
847 ,security_function_name
848 ,control_account_type_code
849 ,alc_enabled_flag
850 FROM xla_subledgers
851 WHERE application_id = x_application_id
852 FOR UPDATE OF application_id NOWAIT;
853
854 recinfo c%ROWTYPE;
855
856 l_log_module VARCHAR2(240);
857 BEGIN
858
859 IF g_log_enabled THEN
860 l_log_module := C_DEFAULT_MODULE||'.lock_row';
861 END IF;
862
863 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
864 trace(p_msg => 'BEGIN of procedure lock_row',
865 p_module => l_log_module,
866 p_level => C_LEVEL_PROCEDURE);
867 END IF;
868
869 OPEN c;
870 FETCH c INTO recinfo;
871
872 IF (c%NOTFOUND) THEN
873 CLOSE c;
874 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
875 app_exception.raise_exception;
876 END IF;
877 CLOSE c;
878
879 IF ( (recinfo.application_id = x_application_id)
880 AND (recinfo.application_type_code = x_application_type_code)
881 AND (recinfo.je_source_name = x_je_source_name)
882 AND (recinfo.valuation_method_flag = x_valuation_method_flag)
883 AND ((recinfo.drilldown_procedure_name = x_drilldown_procedure_name) OR
884 (recinfo.drilldown_procedure_name IS NULL AND x_drilldown_procedure_name IS NULL))
885 AND ((recinfo.security_function_name = x_security_function_name) OR
886 (recinfo.security_function_name IS NULL AND x_security_function_name IS NULL))
887 AND ((recinfo.control_account_type_code = x_control_account_type_code) OR
888 (recinfo.control_account_type_code IS NULL AND x_control_account_type_code IS NULL))
889 AND (recinfo.alc_enabled_flag = x_alc_enabled_flag)
890 ) THEN
891 NULL;
892 ELSE
893 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
894 app_exception.raise_exception;
895 END IF;
896
897 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
898 trace(p_msg => 'END of procedure lock_row',
899 p_module => l_log_module,
900 p_level => C_LEVEL_PROCEDURE);
901 END IF;
902
903 END lock_row;
904
905 /*======================================================================+
906 | |
907 | Procedure update_row |
908 | |
909 +======================================================================*/
910 PROCEDURE update_row
911 (x_application_id IN NUMBER
912 ,x_application_type_code IN VARCHAR2 DEFAULT NULL
913 ,x_je_source_name IN VARCHAR2
914 ,x_valuation_method_flag IN VARCHAR2
915 ,x_drilldown_procedure_name IN VARCHAR2
916 ,x_security_function_name IN VARCHAR2
917 ,x_control_account_type_code IN VARCHAR2
918 ,x_alc_enabled_flag IN VARCHAR2
919 ,x_last_update_date IN DATE
920 ,x_last_updated_by IN NUMBER
921 ,x_last_update_login IN NUMBER)
922
923 IS
924 l_log_module VARCHAR2(240);
925 BEGIN
926
927 IF g_log_enabled THEN
928 l_log_module := C_DEFAULT_MODULE||'.update_row';
929 END IF;
930
931 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
932 trace(p_msg => 'BEGIN of procedure update_row',
933 p_module => l_log_module,
934 p_level => C_LEVEL_PROCEDURE);
935 END IF;
936
937 UPDATE xla_subledgers
938 SET
939 last_update_date = x_last_update_date
940 ,application_type_code = nvl(x_application_type_code,application_type_code)
941 ,je_source_name = x_je_source_name
942 ,valuation_method_flag = x_valuation_method_flag
943 ,drilldown_procedure_name = x_drilldown_procedure_name
944 ,security_function_name = x_security_function_name
945 ,control_account_type_code = x_control_account_type_code
946 ,alc_enabled_flag = x_alc_enabled_flag
947 ,last_updated_by = x_last_updated_by
948 ,last_update_login = x_last_update_login
949 WHERE application_id = x_application_id
950 ;
951
952 IF (SQL%NOTFOUND) THEN
953 RAISE NO_DATA_FOUND;
954 END IF;
955
956 --
957 -- Update the Transaction Security mechanism for the subledger application
958 --
959 xla_security_pkg.set_subledger_security(
960 p_application_id => x_application_id
961 ,p_security_function_name => x_security_function_name);
962
963 -- Add a new partition if not already exsits
964 update_partitions(p_app_id => x_application_id
965 ,p_action => 'ADD');
966
967 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
968 trace(p_msg => 'END of procedure update_row',
969 p_module => l_log_module,
970 p_level => C_LEVEL_PROCEDURE);
971 END IF;
972
973 END update_row;
974
975 /*======================================================================+
976 | |
977 | Procedure delete_row |
978 | |
979 +======================================================================*/
980 PROCEDURE delete_row
981 (x_application_id IN NUMBER)
982 IS
983 CURSOR c2 IS
984 SELECT application_short_name
985 FROM fnd_application
986 WHERE application_id = x_application_id;
987
988 l_schema VARCHAR2(30);
989 l_app_short_name VARCHAR2(30);
990 l_log_module VARCHAR2(240);
991 BEGIN
992
993 IF g_log_enabled THEN
994 l_log_module := C_DEFAULT_MODULE||'.delete_row';
995 END IF;
996
997 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
998 trace(p_msg => 'BEGIN of procedure delete_row',
999 p_module => l_log_module,
1000 p_level => C_LEVEL_PROCEDURE);
1001 END IF;
1002
1003 OPEN c2;
1004 FETCH c2 INTO l_app_short_name;
1005 if (c2%NOTFOUND) then
1006 CLOSE c2;
1007 RAISE NO_DATA_FOUND;
1008 end if;
1009 CLOSE c2;
1010
1011 --
1012 -- Remove the Transaction Security mechanism for the subledger application
1013 --
1014 xla_security_pkg.set_subledger_security(
1015 p_application_id => x_application_id
1016 ,p_security_function_name => null);
1017
1018 xla_event_class_grps_f_pkg.delete_row
1019 (x_application_id => x_application_id
1020 ,x_event_class_group_code => C_MANUAL);
1021
1022 xla_event_class_attrs_f_pkg.delete_row
1023 (x_application_id => x_application_id
1024 ,x_entity_code => C_MANUAL
1025 ,x_event_class_code => C_MANUAL);
1026
1027 xla_event_types_f_pkg.delete_row
1028 (x_application_id => x_application_id
1029 ,x_entity_code => C_MANUAL
1030 ,x_event_class_code => C_MANUAL
1031 ,x_event_type_code => C_MANUAL);
1032
1033 DELETE FROM xla_entity_id_mappings
1034 WHERE application_id = x_application_id
1035 AND entity_code = C_MANUAL
1036 ;
1037
1038 xla_event_classes_f_pkg.delete_row
1039 (x_application_id => x_application_id
1040 ,x_entity_code => C_MANUAL
1041 ,x_event_class_code => C_MANUAL);
1042
1043 xla_entity_types_f_pkg.delete_row
1044 (x_application_id => x_application_id
1045 ,x_entity_code => C_MANUAL);
1046
1047 delete_tpm
1048 (p_application_id => x_application_id);
1049
1050 DELETE FROM xla_subledgers
1051 WHERE application_id = x_application_id;
1052
1053 IF (SQL%NOTFOUND) THEN
1054 RAISE NO_DATA_FOUND;
1055 END IF;
1056
1057 -- Drop partitions
1058 update_partitions(p_app_id => x_application_id
1059 ,p_action => 'DROP');
1060
1061 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1062 trace(p_msg => 'END of procedure delete_row',
1063 p_module => l_log_module,
1064 p_level => C_LEVEL_PROCEDURE);
1065 END IF;
1066
1067 EXCEPTION
1068 WHEN xla_exceptions_pkg.application_exception THEN
1069 ROLLBACK;
1070 RAISE;
1071
1072 WHEN OTHERS THEN
1073 ROLLBACK;
1074 xla_exceptions_pkg.raise_message
1075 (p_location => 'xla_subledgers_f_pkg.delete_row');
1076 END delete_row;
1077
1078 /*======================================================================+
1079 | |
1080 | Name: load_row |
1081 | Description: To be used by FNDLOAD to upload a row to the table |
1082 | |
1083 +======================================================================*/
1084 PROCEDURE load_row
1085 (p_application_short_name IN VARCHAR2
1086 ,p_je_source_name IN VARCHAR2
1087 ,p_valuation_method_flag IN VARCHAR2
1088 ,p_drilldown_procedure_name IN VARCHAR2
1089 ,p_security_function_name IN VARCHAR2
1090 ,p_application_type_code IN VARCHAR2
1091 ,p_alc_enabled_flag IN VARCHAR2
1092 ,p_control_account_type_code IN VARCHAR2
1093 ,p_owner IN VARCHAR2
1094 ,p_last_update_date IN VARCHAR2)
1095 IS
1096 CURSOR c_app_id IS
1097 SELECT application_id
1098 FROM fnd_application
1099 WHERE application_short_name = p_application_short_name;
1100
1101 CURSOR c_journal_source IS
1102 SELECT je_source_name
1103 FROM gl_je_sources
1104 WHERE je_source_key = p_je_source_name;
1105
1106 l_application_id INTEGER;
1107 l_je_source_name VARCHAR2(30);
1108 l_rowid ROWID;
1109 l_exist VARCHAR2(1);
1110 f_luby NUMBER; -- entity owner in file
1111 f_ludate DATE; -- entity update date in file
1112 db_luby NUMBER; -- entity owner in db
1113 db_ludate DATE; -- entity update date in db
1114 l_log_module VARCHAR2(240);
1115 BEGIN
1116
1117 IF g_log_enabled THEN
1118 l_log_module := C_DEFAULT_MODULE||'.load_row';
1119 END IF;
1120
1121 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1122 trace(p_msg => 'BEGIN of procedure load_row',
1123 p_module => l_log_module,
1124 p_level => C_LEVEL_PROCEDURE);
1125 END IF;
1126
1127 -- Translate owner to file_last_updated_by
1128 f_luby := fnd_load_util.owner_id(p_owner);
1129
1130 -- Translate char last_update_date to date
1131 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
1132
1133 OPEN c_app_id;
1134 FETCH c_app_id INTO l_application_id;
1135 CLOSE c_app_id;
1136
1137 OPEN c_journal_source;
1138 FETCH c_journal_source INTO l_je_source_name;
1139 CLOSE c_journal_source;
1140
1141 BEGIN
1142
1143 SELECT last_updated_by, last_update_date
1144 INTO db_luby, db_ludate
1145 FROM xla_subledgers
1146 WHERE application_id = l_application_id;
1147
1148 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, null)) then
1149 xla_subledgers_f_pkg.update_row
1150 (x_application_id => l_application_id
1151 ,x_application_type_code => p_application_type_code
1152 ,x_je_source_name => l_je_source_name
1153 ,x_valuation_method_flag => p_valuation_method_flag
1154 ,x_drilldown_procedure_name => p_drilldown_procedure_name
1155 ,x_security_function_name => p_security_function_name
1156 ,x_control_account_type_code => p_control_account_type_code
1157 ,x_alc_enabled_flag => p_alc_enabled_flag
1158 ,x_last_update_date => f_ludate
1159 ,x_last_updated_by => f_luby
1160 ,x_last_update_login => 0);
1161
1162 END IF;
1163
1164 EXCEPTION
1165 WHEN NO_DATA_FOUND THEN
1166 xla_subledgers_f_pkg.insert_row
1167 (x_rowid => l_rowid
1168 ,x_application_id => l_application_id
1169 ,x_application_type_code => p_application_type_code
1170 ,x_je_source_name => l_je_source_name
1171 ,x_valuation_method_flag => p_valuation_method_flag
1172 ,x_drilldown_procedure_name => p_drilldown_procedure_name
1173 ,x_security_function_name => p_security_function_name
1174 ,x_control_account_type_code => p_control_account_type_code
1175 ,x_alc_enabled_flag => p_alc_enabled_flag
1176 ,x_creation_date => f_ludate
1177 ,x_created_by => f_luby
1178 ,x_last_update_date => f_ludate
1179 ,x_last_updated_by => f_luby
1180 ,x_last_update_login => 0);
1181
1182 END;
1183
1184 -- Fix bug 5416476
1185 UPDATE xla_product_rules_b
1186 SET compile_status_code = 'N'
1187 WHERE application_id = l_application_id;
1188
1189 UPDATE xla_prod_acct_headers
1190 SET validation_status_code = 'N'
1191 WHERE application_id = l_application_id;
1192
1193 UPDATE xla_line_definitions_b
1194 SET validation_status_code = 'N'
1195 WHERE application_id = l_application_id;
1196
1197 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1198 trace(p_msg => 'END of procedure load_row',
1199 p_module => l_log_module,
1200 p_level => C_LEVEL_PROCEDURE);
1201 END IF;
1202
1203 EXCEPTION
1204 WHEN NO_DATA_FOUND THEN
1205 null;
1206 WHEN OTHERS THEN
1207 xla_exceptions_pkg.raise_message
1208 (p_location => 'xla_subledgers_f_pkg.load_row');
1209
1210 END load_row;
1211
1212 --=============================================================================
1213 --
1214 -- Following code is executed when the package body is referenced for the first
1215 -- time
1216 --
1217 --=============================================================================
1218 BEGIN
1219 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1220 g_log_enabled := fnd_log.test
1221 (log_level => g_log_level
1222 ,module => C_DEFAULT_MODULE);
1223
1224 IF NOT g_log_enabled THEN
1225 g_log_level := C_LEVEL_LOG_DISABLED;
1226 END IF;
1227
1228
1229 END xla_subledgers_f_pkg;