[Home] [Help]
PACKAGE BODY: APPS.XLA_SUBLEDGERS_F_PKG
Source
1 PACKAGE BODY XLA_SUBLEDGERS_F_PKG AS
2 /* $Header: xlatbapp.pkb 120.29 2006/08/25 23:53:28 wychan 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_je_source_name IN VARCHAR2
913 ,x_valuation_method_flag IN VARCHAR2
914 ,x_drilldown_procedure_name IN VARCHAR2
915 ,x_security_function_name IN VARCHAR2
916 ,x_control_account_type_code IN VARCHAR2
917 ,x_alc_enabled_flag IN VARCHAR2
918 ,x_last_update_date IN DATE
919 ,x_last_updated_by IN NUMBER
920 ,x_last_update_login IN NUMBER)
921
922 IS
923 l_log_module VARCHAR2(240);
924 BEGIN
925
926 IF g_log_enabled THEN
927 l_log_module := C_DEFAULT_MODULE||'.update_row';
928 END IF;
929
930 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
931 trace(p_msg => 'BEGIN of procedure update_row',
932 p_module => l_log_module,
933 p_level => C_LEVEL_PROCEDURE);
934 END IF;
935
936 UPDATE xla_subledgers
937 SET
938 last_update_date = x_last_update_date
939 ,application_id = x_application_id
940 ,je_source_name = x_je_source_name
941 ,valuation_method_flag = x_valuation_method_flag
942 ,drilldown_procedure_name = x_drilldown_procedure_name
943 ,security_function_name = x_security_function_name
944 ,control_account_type_code = x_control_account_type_code
945 ,alc_enabled_flag = x_alc_enabled_flag
946 ,last_updated_by = x_last_updated_by
947 ,last_update_login = x_last_update_login
948 WHERE application_id = x_application_id
949 ;
950
951 IF (SQL%NOTFOUND) THEN
952 RAISE NO_DATA_FOUND;
953 END IF;
954
955 --
956 -- Update the Transaction Security mechanism for the subledger application
957 --
958 xla_security_pkg.set_subledger_security(
959 p_application_id => x_application_id
960 ,p_security_function_name => x_security_function_name);
961
962 -- Add a new partition if not already exsits
963 update_partitions(p_app_id => x_application_id
964 ,p_action => 'ADD');
965
966 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
967 trace(p_msg => 'END of procedure update_row',
968 p_module => l_log_module,
969 p_level => C_LEVEL_PROCEDURE);
970 END IF;
971
972 END update_row;
973
974 /*======================================================================+
975 | |
976 | Procedure delete_row |
977 | |
978 +======================================================================*/
979 PROCEDURE delete_row
980 (x_application_id IN NUMBER)
981 IS
982 CURSOR c2 IS
983 SELECT application_short_name
984 FROM fnd_application
985 WHERE application_id = x_application_id;
986
987 l_schema VARCHAR2(30);
988 l_app_short_name VARCHAR2(30);
989 l_log_module VARCHAR2(240);
990 BEGIN
991
992 IF g_log_enabled THEN
993 l_log_module := C_DEFAULT_MODULE||'.delete_row';
994 END IF;
995
996 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
997 trace(p_msg => 'BEGIN of procedure delete_row',
998 p_module => l_log_module,
999 p_level => C_LEVEL_PROCEDURE);
1000 END IF;
1001
1002 OPEN c2;
1003 FETCH c2 INTO l_app_short_name;
1004 if (c2%NOTFOUND) then
1005 CLOSE c2;
1006 RAISE NO_DATA_FOUND;
1007 end if;
1008 CLOSE c2;
1009
1010 --
1011 -- Remove the Transaction Security mechanism for the subledger application
1012 --
1013 xla_security_pkg.set_subledger_security(
1014 p_application_id => x_application_id
1015 ,p_security_function_name => null);
1016
1017 xla_event_class_grps_f_pkg.delete_row
1018 (x_application_id => x_application_id
1019 ,x_event_class_group_code => C_MANUAL);
1020
1021 xla_event_class_attrs_f_pkg.delete_row
1022 (x_application_id => x_application_id
1023 ,x_entity_code => C_MANUAL
1024 ,x_event_class_code => C_MANUAL);
1025
1026 xla_event_types_f_pkg.delete_row
1027 (x_application_id => x_application_id
1028 ,x_entity_code => C_MANUAL
1029 ,x_event_class_code => C_MANUAL
1030 ,x_event_type_code => C_MANUAL);
1031
1032 DELETE FROM xla_entity_id_mappings
1033 WHERE application_id = x_application_id
1034 AND entity_code = C_MANUAL
1035 ;
1036
1037 xla_event_classes_f_pkg.delete_row
1038 (x_application_id => x_application_id
1039 ,x_entity_code => C_MANUAL
1040 ,x_event_class_code => C_MANUAL);
1041
1042 xla_entity_types_f_pkg.delete_row
1043 (x_application_id => x_application_id
1044 ,x_entity_code => C_MANUAL);
1045
1046 delete_tpm
1047 (p_application_id => x_application_id);
1048
1049 DELETE FROM xla_subledgers
1050 WHERE application_id = x_application_id;
1051
1052 IF (SQL%NOTFOUND) THEN
1053 RAISE NO_DATA_FOUND;
1054 END IF;
1055
1056 -- Drop partitions
1057 update_partitions(p_app_id => x_application_id
1058 ,p_action => 'DROP');
1059
1060 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1061 trace(p_msg => 'END of procedure delete_row',
1062 p_module => l_log_module,
1063 p_level => C_LEVEL_PROCEDURE);
1064 END IF;
1065
1066 EXCEPTION
1067 WHEN xla_exceptions_pkg.application_exception THEN
1068 ROLLBACK;
1069 RAISE;
1070
1071 WHEN OTHERS THEN
1072 ROLLBACK;
1073 xla_exceptions_pkg.raise_message
1074 (p_location => 'xla_subledgers_f_pkg.delete_row');
1075 END delete_row;
1076
1077 /*======================================================================+
1078 | |
1079 | Name: load_row |
1080 | Description: To be used by FNDLOAD to upload a row to the table |
1081 | |
1082 +======================================================================*/
1083 PROCEDURE load_row
1084 (p_application_short_name IN VARCHAR2
1085 ,p_je_source_name IN VARCHAR2
1086 ,p_valuation_method_flag IN VARCHAR2
1087 ,p_drilldown_procedure_name IN VARCHAR2
1088 ,p_security_function_name IN VARCHAR2
1089 ,p_application_type_code IN VARCHAR2
1090 ,p_alc_enabled_flag IN VARCHAR2
1091 ,p_control_account_type_code IN VARCHAR2
1092 ,p_owner IN VARCHAR2
1093 ,p_last_update_date IN VARCHAR2)
1094 IS
1095 CURSOR c_app_id IS
1096 SELECT application_id
1097 FROM fnd_application
1098 WHERE application_short_name = p_application_short_name;
1099
1100 CURSOR c_journal_source IS
1101 SELECT je_source_name
1102 FROM gl_je_sources
1103 WHERE je_source_key = p_je_source_name;
1104
1105 l_application_id INTEGER;
1106 l_je_source_name VARCHAR2(30);
1107 l_rowid ROWID;
1108 l_exist VARCHAR2(1);
1109 f_luby NUMBER; -- entity owner in file
1110 f_ludate DATE; -- entity update date in file
1111 db_luby NUMBER; -- entity owner in db
1112 db_ludate DATE; -- entity update date in db
1113 l_log_module VARCHAR2(240);
1114 BEGIN
1115
1116 IF g_log_enabled THEN
1117 l_log_module := C_DEFAULT_MODULE||'.load_row';
1118 END IF;
1119
1120 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1121 trace(p_msg => 'BEGIN of procedure load_row',
1122 p_module => l_log_module,
1123 p_level => C_LEVEL_PROCEDURE);
1124 END IF;
1125
1126 -- Translate owner to file_last_updated_by
1127 f_luby := fnd_load_util.owner_id(p_owner);
1128
1129 -- Translate char last_update_date to date
1130 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
1131
1132 OPEN c_app_id;
1133 FETCH c_app_id INTO l_application_id;
1134 CLOSE c_app_id;
1135
1136 OPEN c_journal_source;
1137 FETCH c_journal_source INTO l_je_source_name;
1138 CLOSE c_journal_source;
1139
1140 BEGIN
1141
1142 SELECT last_updated_by, last_update_date
1143 INTO db_luby, db_ludate
1144 FROM xla_subledgers
1145 WHERE application_id = l_application_id;
1146
1147 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, null)) then
1148 xla_subledgers_f_pkg.update_row
1149 (x_application_id => l_application_id
1150 ,x_je_source_name => l_je_source_name
1151 ,x_valuation_method_flag => p_valuation_method_flag
1152 ,x_drilldown_procedure_name => p_drilldown_procedure_name
1153 ,x_security_function_name => p_security_function_name
1154 ,x_control_account_type_code => p_control_account_type_code
1155 ,x_alc_enabled_flag => p_alc_enabled_flag
1156 ,x_last_update_date => f_ludate
1157 ,x_last_updated_by => f_luby
1158 ,x_last_update_login => 0);
1159
1160 END IF;
1161
1162 EXCEPTION
1163 WHEN NO_DATA_FOUND THEN
1164 xla_subledgers_f_pkg.insert_row
1165 (x_rowid => l_rowid
1166 ,x_application_id => l_application_id
1167 ,x_application_type_code => p_application_type_code
1168 ,x_je_source_name => l_je_source_name
1169 ,x_valuation_method_flag => p_valuation_method_flag
1170 ,x_drilldown_procedure_name => p_drilldown_procedure_name
1171 ,x_security_function_name => p_security_function_name
1172 ,x_control_account_type_code => p_control_account_type_code
1173 ,x_alc_enabled_flag => p_alc_enabled_flag
1174 ,x_creation_date => f_ludate
1175 ,x_created_by => f_luby
1176 ,x_last_update_date => f_ludate
1177 ,x_last_updated_by => f_luby
1178 ,x_last_update_login => 0);
1179
1180 END;
1181
1182 -- Fix bug 5416476
1183 UPDATE xla_product_rules_b
1184 SET compile_status_code = 'N'
1185 WHERE application_id = l_application_id;
1186
1187 UPDATE xla_prod_acct_headers
1188 SET validation_status_code = 'N'
1189 WHERE application_id = l_application_id;
1190
1191 UPDATE xla_line_definitions_b
1192 SET validation_status_code = 'N'
1193 WHERE application_id = l_application_id;
1194
1195 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1196 trace(p_msg => 'END of procedure load_row',
1197 p_module => l_log_module,
1198 p_level => C_LEVEL_PROCEDURE);
1199 END IF;
1200
1201 EXCEPTION
1202 WHEN NO_DATA_FOUND THEN
1203 null;
1204 WHEN OTHERS THEN
1205 xla_exceptions_pkg.raise_message
1206 (p_location => 'xla_subledgers_f_pkg.load_row');
1207
1208 END load_row;
1209
1210 --=============================================================================
1211 --
1212 -- Following code is executed when the package body is referenced for the first
1213 -- time
1214 --
1215 --=============================================================================
1216 BEGIN
1217 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1218 g_log_enabled := fnd_log.test
1219 (log_level => g_log_level
1220 ,module => C_DEFAULT_MODULE);
1221
1222 IF NOT g_log_enabled THEN
1223 g_log_level := C_LEVEL_LOG_DISABLED;
1224 END IF;
1225
1226
1227 END xla_subledgers_f_pkg;