[Home] [Help]
PACKAGE BODY: APPS.XLA_ACCT_SETUP_PKG
Source
1 PACKAGE BODY xla_acct_setup_pkg AS
2 -- $Header: xlasuaoi.pkb 120.17.12000000.2 2007/07/24 15:22:31 jlarre ship $
3 /*===========================================================================+
4 | Copyright (c) 1995-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +============================================================================+
8 | PACKAGE NAME |
9 | xla_acct_setup_pkg |
10 | |
11 | DESCRIPTION |
12 | XLA Accounting Options Setup |
13 | The package defaults values for the accounting setup options. |
14 | |
15 | HISTORY |
16 | 06-Feb-03 Dimple Shah Created |
17 | 12-Jun-03 S. Singhania Fixed FND messages (bug # 3001156) |
18 | 15-Jun-03 S. Singhania Fixed the FETCH statement for c_applications in|
19 | setup_options. |
20 | 10-Jul-03 S. Singhania Changed table name from XLA_EVENT_CLASSES_ATTR |
21 | to XLA_EVENT_CLASS_ATTRS |
22 | 21-Aug-03 S. Joshi Removed COPY_DOC_SEQUENCE_FLAG |
23 | 05-Sep-03 S. Singhania Fix for bug # 3128896. Modified the procedure |
24 | INSERT_LEDGER_OPTIONS to set correct default |
25 | for the option 'General Ledger Journal Entry |
26 | Summarization' |
27 | 28-Sep-03 S. Singhania Made changes for enhancing the package to |
28 | include APIs for event_class setups (3151792)|
29 | - Added global variables and the API |
30 | SET_DEFAULT_VALUES |
31 | - Added APIs PERFORM_EVENT_CLASS_SETUP and |
32 | DELETE_EVENT_CLASS_SETUP |
33 | - Modified following procedures: |
34 | INSERT_JE_CATEGORY, SETUP_OPTIONS |
35 | Minor changes in following procedures: |
36 | SETUP_LEDGER_OPTIONS, INSERT_LAUNCH_OPTIONS, |
37 | INSERT_LEDGER_OPTIONS |
38 | 18-Nov-03 S. Singhania Changed the default values for 'g_porcesses' |
39 | and 'g_processing_unit_size' to 1 and 1000 |
40 | respectively in routine SET_DEFAULT_VALUES. |
41 | (Bug # 3259247). |
42 | 10-Dec-03 S. Singhania Added the API PERFORM_APPLICATION_SETUP_CP for |
43 | the concurrent program. (Bug 3229146). |
44 | 17-Jun-04 S. Singhania Added UPGRADE_LEDGER_OPTIONS API for AX upgrade|
45 | 17-JUN-04 S. Singhania Fixed GSCC warnings for File.Sql.35 |
46 | 18-JUN-04 S. Singhania Added more validations to the API |
47 | UPGRADE_LEDGER_OPTIONS |
48 | 01-NOV-04 S. Singhania Made changes for Valuation Method Enhancements:|
49 | - Added g_capture_event_flag |
50 | - Modified SET_DEFAULT_VALUES, SETUP_OPTIONS,|
51 | INSERT_LEDGER_OPTIONS and |
52 | PERFORM_APPLICATION_SETUP_CP |
53 | 19-Aug-05 V.Swapna Removed alc_enabled_flag(bug #4364830) |
54 | 24-JUL-2007 Jorge Larre Bug 5582560 |
55 | The program loops on applications and sets the global variable |
56 | g_capture_event_flag based on the valuation method. This is done in a |
57 | simple IF with no else clause for the other case, so after the first |
58 | application meets the condition, the global variable remains set for |
59 | all the other applications. Solution: add an ELSE clause to the IF. |
60 +===========================================================================*/
61
62 --=============================================================================
63 -- **************** declaraions ********************
64 --=============================================================================
65 g_accounting_mode_code VARCHAR2(1);
66 g_acctg_mode_override_flag VARCHAR2(1);
67 g_summary_report_flag VARCHAR2(1);
68 g_summary_report_override_flag VARCHAR2(1);
69 g_submit_transfer_to_gl_flag VARCHAR2(1);
70 g_submit_xfer_override_flag VARCHAR2(1);
71 g_submit_gl_post_flag VARCHAR2(1);
72 g_submit_gl_post_override_flag VARCHAR2(1);
73 g_error_limit NUMBER;
74 g_processes NUMBER;
75 g_processing_unit_size NUMBER;
76
77 g_transfer_to_gl_mode_code VARCHAR2(1);
78 g_acct_reversal_option_code VARCHAR2(30);
79 g_enabled_flag VARCHAR2(1);
80 g_capture_event_flag VARCHAR2(1);
81
82 -------------------------------------------------------------------------------
83 -- forward declarion of private procedures and functions
84 -------------------------------------------------------------------------------
85
86 PROCEDURE insert_launch_options
87 (p_ledger_id IN NUMBER
88 ,p_application_id IN NUMBER);
89
90 PROCEDURE insert_ledger_options
91 (p_ledger_id IN NUMBER
92 ,p_application_id IN NUMBER);
93
94 PROCEDURE insert_je_category
95 (p_ledger_id IN NUMBER
96 ,p_application_id IN NUMBER
97 ,p_event_class_code IN VARCHAR2);
98
99 PROCEDURE check_primary_ledger_options
100 (p_primary_ledger_id IN NUMBER
101 ,p_application_id IN NUMBER);
102
103 FUNCTION check_ledger_currency
104 (p_primary_ledger_id IN NUMBER
105 ,p_ledger_id IN NUMBER
106 ,p_application_id IN NUMBER)
107 RETURN BOOLEAN;
108
109 PROCEDURE setup_options
110 (p_primary_ledger_id IN NUMBER
111 ,p_ledger_id IN NUMBER
112 ,p_application_id IN NUMBER
113 ,p_valuation_method_flag IN VARCHAR2
114 ,p_event_class_code IN VARCHAR2);
115
116 PROCEDURE set_default_values;
117
118 --=============================================================================
119 -- *********** Local Trace Routine **********
120 --=============================================================================
121 g_debug_flag VARCHAR2(1);
122
123 PROCEDURE trace
124 (p_msg IN VARCHAR2
125 ,p_level IN NUMBER) IS
126 BEGIN
127 IF g_debug_flag = 'Y' THEN
128 xla_utility_pkg.trace
129 (p_msg
130 ,p_level);
131 END IF;
132 EXCEPTION
133 WHEN xla_exceptions_pkg.application_exception THEN
134 RAISE;
135 WHEN OTHERS THEN
136 xla_exceptions_pkg.raise_message
137 (p_location => 'xla_acct_setup_pub_pkg.trace');
138 END trace;
139
140
141 --=============================================================================
142 -- *********** public procedures and functions **********
143 --=============================================================================
144 --=============================================================================
145 --
146 --
147 --
148 --
149 --
150 --
151 --
152 --
153 --
154 --
155 -- Following are the public routines
156 --
157 -- 1. setup_ledger_options
158 -- 2. check_acctg_method_for_ledger
159 -- 3. perform_event_class_setup
160 -- 4. delete_event_class_setup
161 -- 5. perform_application_setup_cp
162 --
163 --
164 --
165 --
166 --
167 --
168 --
169 --
170 --
171 --
172 --
173 --
174 --=============================================================================
175
176 --=============================================================================
177 --
178 -- Sets up ledger options for all subledger applications and given ledger
179 --
180 --=============================================================================
181 PROCEDURE setup_ledger_options
182 (p_primary_ledger_id IN NUMBER
183 ,p_ledger_id IN NUMBER) IS
184
185 CURSOR csr_applications IS
186 SELECT application_id
187 ,valuation_method_flag
188 FROM xla_subledgers;
189
190 BEGIN
191 trace('> xla_acct_setup_pkg.setup_ledger_options' , 10);
192 trace('ledger_id = '||p_ledger_id , 20);
193 trace('primary_ledger_id = '||p_primary_ledger_id , 20);
194
195 ----------------------------------------------------------------------------
196 -- Call routine to set the default values.
197 ----------------------------------------------------------------------------
198 set_default_values;
199
200 ----------------------------------------------------------------------------
201 -- Check if accounting method is valid for the ledger
202 ----------------------------------------------------------------------------
203 check_acctg_method_for_ledger
204 (p_primary_ledger_id => p_primary_ledger_id
205 ,p_ledger_id => p_ledger_id);
206
207 ----------------------------------------------------------------------------
208 -- Loop through all applications
209 ----------------------------------------------------------------------------
210 FOR c1 IN csr_applications LOOP
211 -------------------------------------------------------------------------
212 -- Validate and setup the ledger options for each application
213 -------------------------------------------------------------------------
214 setup_options
215 (p_primary_ledger_id => p_primary_ledger_id
216 ,p_ledger_id => p_ledger_id
217 ,p_application_id => c1.application_id
218 ,p_valuation_method_flag => c1.valuation_method_flag
219 ,p_event_class_code => NULL);
220
221 END LOOP;
222
223 trace('< xla_acct_setup_pkg.setup_ledger_options' , 10);
224
225 EXCEPTION
226 WHEN xla_exceptions_pkg.application_exception THEN
227 RAISE;
228 WHEN OTHERS THEN
229 xla_exceptions_pkg.raise_message
230 (p_location => 'xla_acct_setup_pkg.setup_ledger_options');
231
232 END setup_ledger_options;
233
234
235
236 --=============================================================================
237 --
238 -- Checks if a valid accounting method is attached to the ledger
239 --
240 --=============================================================================
241 PROCEDURE check_acctg_method_for_ledger
242 (p_primary_ledger_id IN NUMBER
243 ,p_ledger_id IN NUMBER) IS
244
245 l_primary_ledger_id NUMBER(38);
246 l_ledger_id NUMBER(38);
247 l_ledger_name VARCHAR2(30) := NULL;
248 l_pr_ledger_name VARCHAR2(30) := NULL;
249 l_accounting_method_name VARCHAR2(80) := NULL;
250 l_accounting_method_type VARCHAR2(80) := NULL;
251
252 CURSOR c_ledger IS
253 SELECT chart_of_accounts_id, sla_accounting_method_code, sla_accounting_method_type
254 FROM xla_gl_ledgers_v
255 WHERE ledger_id = p_ledger_id;
256
257 l_ledger c_ledger%rowtype;
258
259 CURSOR c_trx_coa IS
260 SELECT transaction_coa_id
261 FROM xla_acctg_methods_b
262 WHERE accounting_method_type_code = l_ledger.sla_accounting_method_type
263 AND accounting_method_code = l_ledger.sla_accounting_method_code;
264
265 l_trx_coa c_trx_coa%rowtype;
266
267 CURSOR c_pr_ledger_coa IS
268 SELECT chart_of_accounts_id
269 FROM xla_gl_ledgers_v
270 WHERE ledger_id = p_primary_ledger_id;
271
272 l_pr_ledger_coa c_pr_ledger_coa%rowtype;
273
274 BEGIN
275 trace('> xla_acct_setup_pkg.check_acctg_method_for_ledger' , 10);
276 trace('ledger_id = '||p_ledger_id , 20);
277 trace('primary_ledger_id = '||p_primary_ledger_id , 20);
278
279 l_primary_ledger_id := p_primary_ledger_id;
280 l_ledger_id := p_ledger_id;
281
282
283 OPEN c_ledger;
284 FETCH c_ledger INTO l_ledger;
285
286 IF c_ledger%notfound or l_ledger.sla_accounting_method_code is NULL THEN
287 xla_validations_pkg.get_ledger_name
288 (p_ledger_id => l_ledger_id
289 ,p_ledger_name => l_ledger_name);
290
291 -------------------------------------------------------------------------
292 -- Raise error
293 -------------------------------------------------------------------------
294 xla_exceptions_pkg.raise_message
295 (p_appli_s_name => 'XLA'
296 ,p_msg_name => 'XLA_SU_NO_SLA_METHOD'
297 ,p_token_1 => 'LEDGER_NAME'
298 ,p_value_1 => l_ledger_name);
299 ELSE
300 OPEN c_trx_coa;
301 FETCH c_trx_coa INTO l_trx_coa;
302 CLOSE c_trx_coa;
303
304 IF l_trx_coa.transaction_coa_id is not null THEN
305 IF p_ledger_id = p_primary_ledger_id THEN
306 -------------------------------------------------------------------
307 -- Following should never happen. If this happens this is a bug.
308 -- That's the reason this is does not have a proper messge.
309 -------------------------------------------------------------------
310 IF l_trx_coa.transaction_coa_id <> l_ledger.chart_of_accounts_id THEN
311
312 xla_validations_pkg.get_ledger_name
313 (p_ledger_id => l_primary_ledger_id
314 ,p_ledger_name => l_pr_ledger_name);
315
316 xla_validations_pkg.get_accounting_method_info
317 (p_accounting_method_type_code => l_ledger.sla_accounting_method_type
318 ,p_accounting_method_code => l_ledger.sla_accounting_method_code
319 ,p_accounting_method_name => l_accounting_method_name
320 ,p_accounting_method_type => l_accounting_method_type);
321
322 ----------------------------------------------------------------
323 -- Raise error
324 ----------------------------------------------------------------
325 xla_exceptions_pkg.raise_message
326 (p_appli_s_name => 'XLA'
327 ,p_msg_name => 'XLA_COMMON_ERROR'
328 ,p_token_1 => 'ERROR'
329 ,p_value_1 => 'Transaction COA mismatched with ledger COA for '||l_pr_ledger_name
330 ,p_token_2 => 'LOCATION'
331 ,p_value_2 => 'xla_acct_setup_pkgcheck_acctg_method_for_ledger');
332 END IF;
333 ELSE
334 -------------------------------------------------------------------
335 -- Ledger is secondary, so check the chart of accounts of primary
336 -- ledger
337 -------------------------------------------------------------------
338 OPEN c_pr_ledger_coa;
339 FETCH c_pr_ledger_coa INTO l_pr_ledger_coa;
340 CLOSE c_pr_ledger_coa;
341
342 -------------------------------------------------------------------
343 -- Following should never happen. If this happens this is a bug.
347
344 -- That's the reason this is does not have a proper messge.
345 -------------------------------------------------------------------
346 IF l_trx_coa.transaction_coa_id <> l_pr_ledger_coa.chart_of_accounts_id THEN
348 xla_validations_pkg.get_ledger_name
349 (p_ledger_id => l_ledger_id
350 ,p_ledger_name => l_ledger_name);
351
352 xla_validations_pkg.get_ledger_name
353 (p_ledger_id => l_primary_ledger_id
354 ,p_ledger_name => l_pr_ledger_name);
355
356 xla_validations_pkg.get_accounting_method_info
357 (p_accounting_method_type_code => l_ledger.sla_accounting_method_type
358 ,p_accounting_method_code => l_ledger.sla_accounting_method_code
359 ,p_accounting_method_name => l_accounting_method_name
360 ,p_accounting_method_type => l_accounting_method_type);
361
362 ----------------------------------------------------------------
363 -- Raise error
364 ----------------------------------------------------------------
365 xla_exceptions_pkg.raise_message
366 (p_appli_s_name => 'XLA'
367 ,p_msg_name => 'XLA_COMMON_ERROR'
368 ,p_token_1 => 'ERROR'
369 ,p_value_1 => 'Transaction COA mismatched with the primary ledger COA for '||l_ledger_name
370 ,p_token_2 => 'LOCATION'
371 ,p_value_2 => 'xla_acct_setup_pkgcheck_acctg_method_for_ledger');
372 END IF;
373 END IF;
374 END IF;
375 END IF;
376 CLOSE c_ledger;
377
378 trace('< xla_acct_setup_pkg.check_acctg_method_for_ledger' , 10);
379
380 EXCEPTION
381 WHEN xla_exceptions_pkg.application_exception THEN
382 IF c_ledger%ISOPEN THEN
383 CLOSE c_ledger;
384 END IF;
385 IF c_trx_coa%ISOPEN THEN
386 CLOSE c_trx_coa;
387 END IF;
388 IF c_pr_ledger_coa%ISOPEN THEN
389 CLOSE c_pr_ledger_coa;
390 END IF;
391 RAISE;
392 WHEN OTHERS THEN
393 IF c_ledger%ISOPEN THEN
394 CLOSE c_ledger;
395 END IF;
396 IF c_trx_coa%ISOPEN THEN
397 CLOSE c_trx_coa;
398 END IF;
399 IF c_pr_ledger_coa%ISOPEN THEN
400 CLOSE c_pr_ledger_coa;
401 END IF;
402 xla_exceptions_pkg.raise_message
403 (p_location => 'xla_acct_setup_pkg.check_acctg_method_for_ledger');
404 END check_acctg_method_for_ledger;
405
406
407
408 --=============================================================================
409 --
410 --
411 --
412 --=============================================================================
413 PROCEDURE perform_event_class_setup
414 (p_application_id IN NUMBER
415 ,p_event_class_code IN VARCHAR2) IS
416 CURSOR csr_ledgers IS
417 SELECT xlr.ledger_id ledger_id
418 ,xlr.primary_ledger_id primary_ledger_id
419 ,DECODE(xlo.ledger_id,NULL,'N','Y') ledger_setup_flag
420 FROM xla_ledger_relationships_v xlr
421 ,xla_ledger_options xlo
422 WHERE xlr.ledger_category_code IN ('PRIMARY','SECONDARY')
423 AND xlr.sla_accounting_method_code IS NOT NULL
424 AND xlo.application_id(+) = p_application_id
425 AND xlo.ledger_id (+) = xlr.ledger_id
426 ORDER BY xlr.ledger_category_code;
427 l_valutation_method_flag VARCHAR2(1);
428
429 BEGIN
430 trace('> xla_acct_setup_pkg.perform_event_class_setup' , 10);
431 trace('p_event_class_code = '||p_event_class_code , 20);
432 trace('p_application_id = '||p_application_id , 20);
433
434 SELECT valuation_method_flag
435 INTO l_valutation_method_flag
436 FROM xla_subledgers
437 WHERE application_id = p_application_id;
438
439 trace('valuation_method_flag = '||l_valutation_method_flag , 40);
440
441
442 FOR c1 IN csr_ledgers LOOP
443 IF c1.ledger_setup_flag = 'N' THEN
444 ----------------------------------------------------------------------
445 -- Call routine to set the default values.
446 ----------------------------------------------------------------------
447 set_default_values;
448
449 ----------------------------------------------------------------------
450 -- Check if accounting method is valid for the ledger
451 ----------------------------------------------------------------------
452 check_acctg_method_for_ledger
453 (p_primary_ledger_id => c1.primary_ledger_id
454 ,p_ledger_id => c1.ledger_id);
455
456 ----------------------------------------------------------------------
457 -- Call API to perform the setup of options.
458 ----------------------------------------------------------------------
459 setup_options
460 (p_primary_ledger_id => c1.primary_ledger_id
461 ,p_ledger_id => c1.ledger_id
462 ,p_application_id => p_application_id
463 ,p_valuation_method_flag => l_valutation_method_flag
467 (p_ledger_id => c1.ledger_id
464 ,p_event_class_code => p_event_class_code);
465 ELSE
466 insert_je_category
468 ,p_application_id => p_application_id
469 ,p_event_class_code => p_event_class_code);
470 END IF;
471
472 END LOOP;
473 trace('< xla_acct_setup_pkg.perform_event_class_setup' , 10);
474 EXCEPTION
475 WHEN xla_exceptions_pkg.application_exception THEN
476 RAISE;
477 WHEN OTHERS THEN
478 xla_exceptions_pkg.raise_message
479 (p_location => 'xla_acct_setup_pkg.perform_event_class_setup');
480 END perform_event_class_setup;
481
482
483 --=============================================================================
484 --
485 --
486 --
487 --=============================================================================
488 PROCEDURE delete_event_class_setup
489 (p_application_id IN NUMBER
490 ,p_event_class_code IN VARCHAR2) IS
491 BEGIN
492 trace('> xla_acct_setup_pkg.delete_event_class_setup' , 10);
493 trace('p_event_class_code = '||p_event_class_code , 20);
494 trace('p_application_id = '||p_application_id , 20);
495
496 ----------------------------------------------------------------------------
497 -- Delete from xla_je_categories table.
498 ----------------------------------------------------------------------------
499 DELETE FROM xla_je_categories
500 WHERE application_id = p_application_id
501 AND event_class_code = p_event_class_code;
502 trace('Number of rows deleted = '||SQL%ROWCOUNT , 40);
503
504 trace('< xla_acct_setup_pkg.delete_event_class_setup' , 10);
505 EXCEPTION
506 WHEN xla_exceptions_pkg.application_exception THEN
507 RAISE;
508 WHEN OTHERS THEN
509 xla_exceptions_pkg.raise_message
510 (p_location => 'xla_acct_setup_pkg.delete_event_class_setup');
511 END delete_event_class_setup;
512
513
514
515 --=============================================================================
516 --
517 -- This API is a registered concurrent program that can be executed for one/all
518 -- applications registered with XLA, to compelete the subledger's accounting
519 -- setup for defined ledgers.
520 -- It performs following tasks for the application(s):
521 -- 1. Deletes from xla_je_categories the event classes that do not
522 -- exist in xla_event_classes.
523 -- 2. Inserts into xla_launch_options for ledgers that do not exist there.
524 -- 3. Inserts into xla_ledger_options for ledgers that do not exist there.
525 -- 4. Inserts into xla_je_categories the event classes that were not setup
526 -- earlier.
527 --
528 --=============================================================================
529 PROCEDURE perform_application_setup_cp
530 (p_errbuf OUT NOCOPY VARCHAR2
531 ,p_retcode OUT NOCOPY NUMBER
532 ,p_application_id IN NUMBER) IS
533 CURSOR csr_applications IS
534 SELECT application_id
535 ,application_name
536 FROM xla_subledgers_fvl
537 WHERE application_id = NVL(p_application_id,application_id);
538
539 l_sysdate DATE;
540 BEGIN
541 xla_utility_pkg.activate('SRS_DBP', 'xla_acct_setup_pkg.perform_application_setup_cp');
542
543 trace('> xla_acct_setup_pkg.perform_application_setup_cp', 20);
544 trace('p_application_id = '||p_application_id,20);
545
546 xla_environment_pkg.refresh;
547
548 l_sysdate := sysdate;
549
550 ----------------------------------------------------------------------------
551 -- Calling the API to set the default values for the different options
552 ----------------------------------------------------------------------------
553 set_default_values;
554
555 FOR c1 IN csr_applications LOOP
556
557 trace('Updating Subledger Accounting Options for application = '||
558 c1.application_name, 20);
559
560 -------------------------------------------------------------------------
561 -- Deleting from xla_je_categories all the event classes for the
562 -- application that has been deleted from AMB tables.
563 -------------------------------------------------------------------------
564 trace('Deleting orphan rows from xla_je_categories for event classes '||
565 'that do not exist.....',20);
566
567 DELETE
568 FROM xla_je_categories xjc
569 WHERE application_id = c1.application_id
570 AND NOT EXISTS
571 (SELECT 1
572 FROM xla_event_classes_b
573 WHERE application_id = xjc.application_id
574 AND event_class_code = xjc.event_class_code);
575
576 trace('Number of rows deleted = '||SQL%ROWCOUNT,30);
577
578
579 -------------------------------------------------------------------------
580 -- Inserting into xla_launch_options table rows for the application and
581 -- all the eligible ledgers.
582 -------------------------------------------------------------------------
586 INSERT INTO xla_launch_options
583 trace('Inserting rows in xla_launch_options for the ledgers '||
584 'that are not already setup',20);
585
587 (application_id
588 ,ledger_id
589 ,accounting_mode_code
590 ,accounting_mode_override_flag
591 ,summary_report_flag
592 ,summary_report_override_flag
593 ,submit_transfer_to_gl_flag
594 ,submit_transfer_override_flag
595 ,submit_gl_post_flag
596 ,submit_gl_post_override_flag
597 ,error_limit
598 ,processes
599 ,processing_unit_size
600 ,creation_date
601 ,created_by
602 ,last_update_date
603 ,last_updated_by
604 ,last_update_login)
605 (SELECT DISTINCT
606 c1.application_id
607 ,xlr.ledger_id
608 ,g_accounting_mode_code
609 ,g_acctg_mode_override_flag
610 ,g_summary_report_flag
611 ,g_summary_report_override_flag
612 ,g_submit_transfer_to_gl_flag
613 ,g_submit_xfer_override_flag
614 ,g_submit_gl_post_flag
615 ,g_submit_gl_post_override_flag
616 ,g_error_limit
617 ,g_processes
618 ,g_processing_unit_size
619 ,l_sysdate
620 ,xla_environment_pkg.g_usr_id
621 ,l_sysdate
622 ,xla_environment_pkg.g_usr_id
623 ,xla_environment_pkg.g_login_id
624 FROM
625 xla_ledger_relationships_v xlr
626 ,xla_subledgers xsl
627 ,xla_acctg_methods_b xam
628 ,gl_ledgers gll
629 WHERE
630 xlr.ledger_category_code IN ('PRIMARY','SECONDARY')
631 AND xlr.sla_accounting_method_code IS NOT NULL
632 AND xsl.application_id = c1.application_id
633 AND xlr.ledger_category_code = DECODE(xsl.valuation_method_flag
634 ,'N','PRIMARY'
635 ,'Y',xlr.ledger_category_code)
636 AND xam.accounting_method_code = xlr.sla_accounting_method_code
637 AND xam.accounting_method_type_code = xlr.sla_accounting_method_type
638 AND gll.ledger_id = xlr.primary_ledger_id
639 AND NVL(xam.transaction_coa_id
640 ,gll.chart_of_accounts_id) = gll.chart_of_accounts_id
641 AND NOT EXISTS (SELECT 1
642 FROM xla_launch_options
643 WHERE ledger_id = xlr.ledger_id
644 AND application_id = xsl.application_id));
645
646 trace('Number of rows inserted = '||SQL%ROWCOUNT,30);
647
648
649
650 -------------------------------------------------------------------------
651 -- Inserting into xla_ledger_options table rows for the application and
652 -- all the eligible ledgers.
653 -------------------------------------------------------------------------
654 trace('Inserting rows in xla_ledger_options for the ledgers '||
655 'that are not already setup',20);
656
657 -------------------------------------------------------------------------
658 -- The value of 'transfer_to_gl_mode_code' is decided based on the value
659 -- of column gl_ledgers.net_income_code_combination_id, which decides if
660 -- 'Daily Balances' are enabled for the ledger or not. (Bug 3128896).
661 -------------------------------------------------------------------------
662 INSERT INTO xla_ledger_options
663 (application_id
664 ,ledger_id
665 ,transfer_to_gl_mode_code
666 ,acct_reversal_option_code
667 ,capture_event_flag
668 ,rounding_rule_code
669 ,enabled_flag
670 ,creation_date
671 ,created_by
672 ,last_update_date
673 ,last_updated_by
674 ,last_update_login
675 --,merge_acct_option_code
676 )
677 (SELECT DISTINCT
678 c1.application_id
679 ,xlr.ledger_id
680 ,decode(gl2.net_income_code_combination_id,NULL,'P','A')
681 ,g_acct_reversal_option_code
682 ,DECODE(xsl.valuation_method_flag
683 ,'Y','Y'
684 ,DECODE(xlr.ledger_category_code
685 ,'PRIMARY', 'Y'
686 ,'N')
687 )
688 ,'NEAREST'
689 ,g_enabled_flag
690 ,l_sysdate
691 ,xla_environment_pkg.g_usr_id
692 ,l_sysdate
693 ,xla_environment_pkg.g_usr_id
694 ,xla_environment_pkg.g_login_id
695 --,'NONE'
696 FROM
697 xla_ledger_relationships_v xlr
698 ,xla_subledgers xsl
699 ,xla_acctg_methods_b xam
700 ,gl_ledgers gll
701 ,gl_ledgers gl2
702 WHERE
703 xlr.ledger_category_code IN ('PRIMARY','SECONDARY')
704 AND xlr.sla_accounting_method_code IS NOT NULL
705 AND xsl.application_id = c1.application_id
709 AND gl2.ledger_id = xlr.ledger_id
706 AND xam.accounting_method_code = xlr.sla_accounting_method_code
707 AND xam.accounting_method_type_code = xlr.sla_accounting_method_type
708 AND gll.ledger_id = xlr.primary_ledger_id
710 AND NVL(xam.transaction_coa_id
711 ,gll.chart_of_accounts_id) = gll.chart_of_accounts_id
712 AND NOT EXISTS (SELECT 1
713 FROM xla_ledger_options
714 WHERE ledger_id = xlr.ledger_id
715 AND application_id = xsl.application_id));
716
717 trace('Number of rows inserted = '||SQL%ROWCOUNT,30);
718
719
720
721 -------------------------------------------------------------------------
722 -- Inserting into xla_je_categories table rows for all the eligible
723 -- classes for the application and all the eligible ledgers.
724 -------------------------------------------------------------------------
725 trace('Inserting rows in xla_je_categories for the event classes and ledgers '||
726 'that are not already setup',20);
727
728 INSERT INTO xla_je_categories
729 (application_id
730 ,ledger_id
731 ,entity_code
732 ,event_class_code
733 ,je_category_name
734 ,creation_date
735 ,created_by
736 ,last_update_date
737 ,last_updated_by
738 ,last_update_login)
739 (SELECT DISTINCT
740 c1.application_id
741 ,xlr.ledger_id
742 ,xec.entity_code
743 ,xec.event_class_code
744 ,xec.je_category_name
745 ,l_sysdate
746 ,xla_environment_pkg.g_usr_id
747 ,l_sysdate
748 ,xla_environment_pkg.g_usr_id
749 ,xla_environment_pkg.g_login_id
750 FROM
751 xla_ledger_relationships_v xlr
752 ,xla_subledgers xsl
753 ,xla_acctg_methods_b xam
754 ,gl_ledgers gll
755 ,xla_event_class_attrs xec
756 WHERE
757 xlr.ledger_category_code IN ('PRIMARY','SECONDARY')
758 AND xlr.sla_accounting_method_code IS NOT NULL
759 AND xsl.application_id = c1.application_id
760 AND xam.accounting_method_code = xlr.sla_accounting_method_code
761 AND xam.accounting_method_type_code = xlr.sla_accounting_method_type
762 AND gll.ledger_id = xlr.primary_ledger_id
763 AND NVL(xam.transaction_coa_id
764 ,gll.chart_of_accounts_id) = gll.chart_of_accounts_id
765 AND xec.application_id = xsl.application_id
766 AND NOT EXISTS (SELECT 1
767 FROM xla_je_categories
768 WHERE application_id = xsl.application_id
769 AND ledger_id = xlr.ledger_id
770 AND entity_code = xec.entity_code
771 AND event_class_code = xec.event_class_code));
772
773 trace('Number of rows inserted = '||SQL%ROWCOUNT,30);
774
775 END LOOP;
776
777 COMMIT;
778 ----------------------------------------------------------------------------
779 -- set out variables
780 ----------------------------------------------------------------------------
781 p_retcode := 0;
782 p_errbuf := NULL;
783
784 trace('< xla_acct_setup_pkg.perform_application_setup_cp', 20);
785 xla_utility_pkg.deactivate('xla_acct_setup_pkg.perform_application_setup_cp');
786 EXCEPTION
787 WHEN xla_exceptions_pkg.application_exception THEN
788 ----------------------------------------------------------------------------
789 -- set out variables
790 ----------------------------------------------------------------------------
791 p_retcode := 2;
792 p_errbuf := xla_messages_pkg.get_message;
793
794 xla_utility_pkg.print_logfile(p_errbuf);
795 trace('< xla_acct_setup_pkg.perform_application_setup_cp (exception)', 20);
796 xla_utility_pkg.deactivate('xla_acct_setup_pkg.perform_application_setup_cp');
797 WHEN OTHERS THEN
798 ----------------------------------------------------------------------------
799 -- set out variables
800 ----------------------------------------------------------------------------
801 p_retcode := 2;
802 p_errbuf := sqlerrm;
803
804 xla_utility_pkg.print_logfile(p_errbuf);
805 trace('< xla_acct_setup_pkg.perform_application_setup_cp (exception)', 20);
806 xla_utility_pkg.deactivate('xla_acct_setup_pkg.perform_application_setup_cp');
807 END perform_application_setup_cp;
808
809
810
811 --=============================================================================
812 -- *********** private procedures and functions **********
813 --=============================================================================
814 --=============================================================================
815 --
816 --
817 --
818 --
819 --
820 --
821 --
822 --
823 --
824 --
825 -- Following are the private routines
826 --
830 -- 4. check_primary_ledger_options
827 -- 1. insert_launch_options
828 -- 2. insert_ledger_options
829 -- 3. insert_je_category
831 -- 5. check_ledger_currency
832 -- 6. setup_options
833 -- 7. set_default_values
834 --
835 --
836 --
837 --
838 --
839 --
840 --
841 --
842 --
843 --
844 --
845 --
846 --=============================================================================
847 --=============================================================================
848 --
849 --
850 --
851 --=============================================================================
852 PROCEDURE insert_launch_options
853 (p_ledger_id IN NUMBER
854 ,p_application_id IN NUMBER) IS
855
856 l_exist VARCHAR2(1);
857
858 CURSOR c_launch_options IS
859 SELECT 'x'
860 FROM xla_launch_options
861 WHERE application_id = p_application_id
862 AND ledger_id = p_ledger_id;
863
864 BEGIN
865 trace('> xla_acct_setup_pkg.insert_launch_options' , 10);
866 trace('ledger_id = '||p_ledger_id , 20);
867 trace('application_id = '||p_application_id , 20);
868
869 OPEN c_launch_options;
870 FETCH c_launch_options INTO l_exist ;
871
872 IF c_launch_options%NOTFOUND THEN
873 INSERT INTO xla_launch_options
874 (application_id
875 ,ledger_id
876 ,accounting_mode_code
877 ,accounting_mode_override_flag
878 ,summary_report_flag
879 ,summary_report_override_flag
880 ,submit_transfer_to_gl_flag
881 ,submit_transfer_override_flag
882 ,submit_gl_post_flag
883 ,submit_gl_post_override_flag
884 ,error_limit
885 ,processes
886 ,processing_unit_size
887 ,creation_date
888 ,created_by
889 ,last_update_date
890 ,last_updated_by
891 ,last_update_login)
892 VALUES
893 (p_application_id
894 ,p_ledger_id
895 ,g_accounting_mode_code
896 ,g_acctg_mode_override_flag
897 ,g_summary_report_flag
898 ,g_summary_report_override_flag
899 ,g_submit_transfer_to_gl_flag
900 ,g_submit_xfer_override_flag
901 ,g_submit_gl_post_flag
902 ,g_submit_gl_post_override_flag
903 ,g_error_limit
904 ,g_processes
905 ,g_processing_unit_size
906 ,sysdate
907 ,xla_environment_pkg.g_usr_id
908 ,sysdate
909 ,xla_environment_pkg.g_usr_id
910 ,xla_environment_pkg.g_login_id);
911 END IF;
912
913 CLOSE c_launch_options;
914
915 trace('< xla_acct_setup_pkg.insert_launch_options' , 10);
916 EXCEPTION
917 WHEN xla_exceptions_pkg.application_exception THEN
918 IF c_launch_options%ISOPEN THEN
919 CLOSE c_launch_options;
920 END IF;
921 RAISE;
922 WHEN OTHERS THEN
923 IF c_launch_options%ISOPEN THEN
924 CLOSE c_launch_options;
925 END IF;
926 xla_exceptions_pkg.raise_message
927 (p_location => 'xla_acct_setup_pkg.insert_launch_options');
928 END insert_launch_options;
929
930
931 --=============================================================================
932 --
933 -- Insert into ledger options
934 --
935 --=============================================================================
936 PROCEDURE insert_ledger_options
937 (p_ledger_id IN NUMBER
938 ,p_application_id IN NUMBER) IS
939 l_exist VARCHAR2(1);
940
941 CURSOR c_ledger_options IS
942 SELECT 'x'
943 FROM xla_ledger_options
944 WHERE application_id = p_application_id
945 AND ledger_id = p_ledger_id;
946
947 BEGIN
948 trace('> xla_acct_setup_pkg.insert_ledger_options' , 10);
949 trace('ledger_id = '||p_ledger_id , 20);
950 trace('application_id = '||p_application_id , 20);
951
952 OPEN c_ledger_options;
953 FETCH c_ledger_options INTO l_exist ;
954
955 IF c_ledger_options%notfound THEN
956 -------------------------------------------------------------------------
957 -- Added following to decide the default value for the
958 -- 'General Ledger Journal Entry Summarization' option based on if the
959 -- Daily Balance is enabled or not for the ledger. (bug # 3128896)
960 -------------------------------------------------------------------------
961 IF g_transfer_to_gl_mode_code IS NULL THEN
962 SELECT decode(net_income_code_combination_id,NULL,'P','A')
963 INTO g_transfer_to_gl_mode_code
964 FROM gl_ledgers
965 WHERE ledger_id = p_ledger_id;
966 END IF;
967
968 INSERT INTO xla_ledger_options
969 (application_id
970 ,ledger_id
971 ,transfer_to_gl_mode_code
972 ,acct_reversal_option_code
973 ,capture_event_flag
974 ,rounding_rule_code
975 ,enabled_flag
976 ,creation_date
977 ,created_by
978 ,last_update_date
982 )
979 ,last_updated_by
980 ,last_update_login
981 --,merge_acct_option_code
983 VALUES
984 (p_application_id
985 ,p_ledger_id
986 ,g_transfer_to_gl_mode_code
987 ,g_acct_reversal_option_code
988 ,g_capture_event_flag
989 ,'NEAREST'
990 ,g_enabled_flag
991 ,sysdate
992 ,xla_environment_pkg.g_usr_id
993 ,sysdate
994 ,xla_environment_pkg.g_usr_id
995 ,xla_environment_pkg.g_login_id
996 --,'NONE'
997 );
998 END IF;
999 CLOSE c_ledger_options;
1000
1001 trace('< xla_acct_setup_pkg.insert_ledger_options' , 10);
1002 EXCEPTION
1003 WHEN xla_exceptions_pkg.application_exception THEN
1004 IF c_ledger_options%ISOPEN THEN
1005 CLOSE c_ledger_options;
1006 END IF;
1007 RAISE;
1008 WHEN OTHERS THEN
1009 IF c_ledger_options%ISOPEN THEN
1010 CLOSE c_ledger_options;
1011 END IF;
1012 xla_exceptions_pkg.raise_message
1013 (p_location => 'xla_acct_setup_pkg.insert_ledger_options');
1014 END insert_ledger_options;
1015
1016
1017 --=============================================================================
1018 --
1019 -- Insert into je category
1020 --
1021 --=============================================================================
1022 PROCEDURE insert_je_category
1023 (p_ledger_id IN NUMBER
1024 ,p_application_id IN NUMBER
1025 ,p_event_class_code IN VARCHAR2) IS
1026 CURSOR csr_event_classes IS
1027 SELECT xeca.entity_code
1028 ,xeca.event_class_code
1029 ,xeca.je_category_name
1030 FROM xla_event_class_attrs xeca
1031 WHERE xeca.application_id = p_application_id
1032 AND xeca.event_class_code = NVL(p_event_class_code,xeca.event_class_code)
1033 AND xeca.event_class_code NOT IN
1034 (SELECT event_class_code
1035 FROM xla_je_categories xjc
1036 WHERE xjc.application_id = p_application_id
1037 AND xjc.ledger_id = p_ledger_id);
1038
1039 BEGIN
1040 trace('> xla_acct_setup_pkg.insert_je_category' , 10);
1041 trace('p_ledger_id = '||p_ledger_id , 20);
1042 trace('p_application_id = '||p_application_id , 20);
1043 trace('p_event_class_code = '||p_event_class_code , 20);
1044
1045 FOR c1 IN csr_event_classes LOOP
1046 INSERT INTO xla_je_categories
1047 (application_id
1048 ,ledger_id
1049 ,entity_code
1050 ,event_class_code
1051 ,je_category_name
1052 ,creation_date
1053 ,created_by
1054 ,last_update_date
1055 ,last_updated_by
1056 ,last_update_login)
1057 VALUES
1058 (p_application_id
1059 ,p_ledger_id
1060 ,c1.entity_code
1061 ,c1.event_class_code
1062 ,c1.je_category_name
1063 ,sysdate
1064 ,xla_environment_pkg.g_usr_id
1065 ,sysdate
1066 ,xla_environment_pkg.g_usr_id
1067 ,xla_environment_pkg.g_login_id);
1068 END LOOP;
1069
1070 trace('< xla_acct_setup_pkg.insert_je_category' , 10);
1071
1072 EXCEPTION
1073 WHEN xla_exceptions_pkg.application_exception THEN
1074 RAISE;
1075 WHEN OTHERS THEN
1076 xla_exceptions_pkg.raise_message
1077 (p_location => 'xla_acct_setup_pkg.insert_je_category');
1078 END insert_je_category;
1079
1080
1081 --=============================================================================
1082 --
1083 -- Checks if the primary ledger options have been inserted for the application
1084 --
1085 --=============================================================================
1086 PROCEDURE check_primary_ledger_options
1087 (p_primary_ledger_id IN NUMBER
1088 ,p_application_id IN NUMBER) IS
1089 l_exist VARCHAR2(1) := null;
1090 l_application_name VARCHAR2(240) := null;
1091 l_pr_ledger_name VARCHAR2(30) := null;
1092 l_application_id NUMBER(38);
1093
1094 CURSOR c_launch_options IS
1095 SELECT 'x'
1096 FROM xla_launch_options
1097 WHERE application_id = p_application_id
1098 AND ledger_id = p_primary_ledger_id;
1099
1100 BEGIN
1101 trace('> xla_acct_setup_pkg.check_primary_ledger_options' , 10);
1102 trace('primary_ledger_id = '||p_primary_ledger_id , 20);
1103 trace('application_id = '||p_application_id , 20);
1104
1105 l_application_id := p_application_id;
1106
1107 OPEN c_launch_options;
1108 FETCH c_launch_options INTO l_exist;
1109 IF c_launch_options%notfound THEN
1110
1111 xla_validations_pkg.get_ledger_name
1112 (p_ledger_id => p_primary_ledger_id
1113 ,p_ledger_name => l_pr_ledger_name);
1114
1115 xla_validations_pkg.get_application_name
1116 (p_application_id => l_application_id
1117 ,p_application_name => l_application_name);
1118
1119 -------------------------------------------------------------------------
1120 -- Raise error
1124 ,p_msg_name => 'XLA_SU_NO_PRIMARY_SETUP'
1121 -------------------------------------------------------------------------
1122 xla_exceptions_pkg.raise_message
1123 (p_appli_s_name => 'XLA'
1125 ,p_token_1 => 'APPLICATION_NAME'
1126 ,p_value_1 => l_application_name);
1127
1128 END IF;
1129 CLOSE c_launch_options;
1130
1131 trace('< xla_acct_setup_pkg.check_primary_ledger_options' , 10);
1132
1133 EXCEPTION
1134 WHEN xla_exceptions_pkg.application_exception THEN
1135 IF c_launch_options%ISOPEN THEN
1136 CLOSE c_launch_options;
1137 END IF;
1138 RAISE;
1139 WHEN OTHERS THEN
1140 IF c_launch_options%ISOPEN THEN
1141 CLOSE c_launch_options;
1142 END IF;
1143 xla_exceptions_pkg.raise_message
1144 (p_location => 'xla_acct_setup_pkg.check_primary_ledger_options');
1145 END check_primary_ledger_options;
1146
1147
1148 --=============================================================================
1149 --
1150 -- Checks if the ledger currency is same as primary ledger currency
1151 --
1152 --=============================================================================
1153 FUNCTION check_ledger_currency
1154 (p_primary_ledger_id IN NUMBER
1155 ,p_ledger_id IN NUMBER
1156 ,p_application_id IN NUMBER)
1157 RETURN BOOLEAN IS
1158
1159 l_primary_ledger_id NUMBER(38);
1160 l_ledger_id NUMBER(38);
1161 l_application_id NUMBER(38);
1162 l_return BOOLEAN;
1163 l_pr_currency_code VARCHAR2(15) := null;
1164 l_currency_code VARCHAR2(15) := null;
1165
1166 CURSOR c_ledger_currency(p_ledger_id IN NUMBER) IS
1167 SELECT currency_code
1168 FROM xla_gl_ledgers_v
1169 WHERE ledger_id = p_ledger_id;
1170
1171 BEGIN
1172 trace('> xla_acct_setup_pkg.check_ledger_currency' , 10);
1173 trace('primary_ledger_id = '||p_primary_ledger_id , 20);
1174 trace('ledger_id = '||p_ledger_id , 20);
1175
1176 l_primary_ledger_id := p_primary_ledger_id;
1177 l_ledger_id := p_ledger_id;
1178 l_application_id := p_application_id;
1179 l_return := FALSE;
1180
1181 OPEN c_ledger_currency(p_ledger_id => l_primary_ledger_id);
1182 FETCH c_ledger_currency INTO l_pr_currency_code;
1183 CLOSE c_ledger_currency;
1184
1185 OPEN c_ledger_currency(p_ledger_id => l_ledger_id);
1186 FETCH c_ledger_currency INTO l_currency_code;
1187 CLOSE c_ledger_currency;
1188
1189 IF l_currency_code <> l_pr_currency_code THEN
1190 l_return := FALSE;
1191 ELSE
1192 l_return := TRUE;
1193 END IF;
1194
1195 trace('< xla_acct_setup_pkg.check_ledger_currency' , 10);
1196
1197 RETURN l_return;
1198
1199 EXCEPTION
1200 WHEN xla_exceptions_pkg.application_exception THEN
1201 RAISE;
1202 WHEN OTHERS THEN
1203 xla_exceptions_pkg.raise_message
1204 (p_location => 'xla_acct_setup_pkg.check_ledger_currency');
1205 END check_ledger_currency;
1206
1207
1208 --=============================================================================
1209 --
1210 -- Sets up ledger options for a subledger application and ledger
1211 --
1212 --=============================================================================
1213
1214 PROCEDURE setup_options
1215 (p_primary_ledger_id IN NUMBER
1216 ,p_ledger_id IN NUMBER
1217 ,p_application_id IN NUMBER
1218 ,p_valuation_method_flag IN VARCHAR2
1219 ,p_event_class_code IN VARCHAR2) IS
1220 BEGIN
1221 trace('> xla_acct_setup_pkg.setup_options' , 10);
1222 trace('p_ledger_id = '||p_ledger_id , 20);
1223 trace('p_primary_ledger_id = '||p_primary_ledger_id , 20);
1224 trace('p_application_id = '||p_application_id , 20);
1225
1226 ----------------------------------------------------------------------------
1227 -- The default value for g_capture_event_flag is changed to 'N' for
1228 -- secondary ledgers and standard applications.
1229 -- Add the ELSE clause to reset the value to its default in case it has
1230 -- been changed for the previous application.
1231 ----------------------------------------------------------------------------
1232 IF p_ledger_id <> p_primary_ledger_id AND
1233 p_valuation_method_flag = 'N'
1234 THEN
1235 g_capture_event_flag := 'N';
1236 ELSE
1237 g_capture_event_flag := 'Y';
1238 END IF;
1239
1240 ----------------------------------------------------------------------------
1241 -- Check if ledger is primary or secondary
1242 ----------------------------------------------------------------------------
1243 IF p_primary_ledger_id = p_ledger_id THEN
1244 -------------------------------------------------------------------------
1245 -- Ledger is primary, so insert into xla_launch_options
1246 -------------------------------------------------------------------------
1247 insert_launch_options
1248 (p_ledger_id => p_ledger_id
1249 ,p_application_id => p_application_id);
1250
1251 -------------------------------------------------------------------------
1255 (p_ledger_id => p_ledger_id
1252 -- Insert into xla_ledger_options
1253 -------------------------------------------------------------------------
1254 insert_ledger_options
1256 ,p_application_id => p_application_id);
1257
1258 -------------------------------------------------------------------------
1259 -- Insert into xla_je_categories
1260 -------------------------------------------------------------------------
1261 insert_je_category
1262 (p_ledger_id => p_ledger_id
1263 ,p_application_id => p_application_id
1264 ,p_event_class_code => p_event_class_code);
1265 ELSE
1266 -------------------------------------------------------------------------
1267 -- Ledger is secondary, check if primary ledger is inserted
1268 -------------------------------------------------------------------------
1269 check_primary_ledger_options
1270 (p_primary_ledger_id => p_primary_ledger_id
1271 ,p_application_id => p_application_id);
1272
1273 IF p_valuation_method_flag = 'Y' THEN
1274 -------------------------------------------------------------------
1275 -- Consider secondary ledger as primary, insert into
1276 -- xla_launch_options
1277 -------------------------------------------------------------------
1278 insert_launch_options
1279 (p_ledger_id => p_ledger_id
1280 ,p_application_id => p_application_id);
1281 END IF;
1282
1283 ----------------------------------------------------------------------
1284 -- Insert into xla_ledger_options
1285 ----------------------------------------------------------------------
1286 insert_ledger_options
1287 (p_ledger_id => p_ledger_id
1288 ,p_application_id => p_application_id);
1289
1290 ----------------------------------------------------------------------
1291 -- Insert into xla_je_categories
1292 ----------------------------------------------------------------------
1293 insert_je_category
1294 (p_ledger_id => p_ledger_id
1295 ,p_application_id => p_application_id
1296 ,p_event_class_code => p_event_class_code);
1297 END IF;
1298
1299
1300 trace('< xla_acct_setup_pkg.setup_options' , 10);
1301
1302 EXCEPTION
1303 WHEN xla_exceptions_pkg.application_exception THEN
1304 RAISE;
1305 WHEN OTHERS THEN
1306 xla_exceptions_pkg.raise_message
1307 (p_location => 'xla_acct_setup_pkg.setup_options');
1308 END setup_options;
1309
1310
1311
1312 --=============================================================================
1313 --
1314 --
1315 --
1316 --=============================================================================
1317 PROCEDURE set_default_values IS
1318 BEGIN
1319 trace('> xla_acct_setup_pkg.set_default_values' , 10);
1320
1321 g_accounting_mode_code := 'F';
1322 g_acctg_mode_override_flag := 'Y';
1323 g_summary_report_flag := 'N';
1324 g_summary_report_override_flag := 'Y';
1325 g_submit_transfer_to_gl_flag := 'Y';
1326 g_submit_xfer_override_flag := 'Y';
1327 g_submit_gl_post_flag := 'N';
1328 g_submit_gl_post_override_flag := 'Y';
1329 g_capture_event_flag := 'Y';
1330 g_error_limit := NULL;
1331
1332 ----------------------------------------------------------------------------
1333 -- The value for g_processes and g_processing_unit_size is initilaized to
1334 -- 1 and 1000 respectively. (Bug # 3259247)
1335 ----------------------------------------------------------------------------
1336 g_processes := 1;
1337 g_processing_unit_size := 1000;
1338
1339 ----------------------------------------------------------------------------
1340 -- g_transfer_to_gl_mode_code should be set to null here because its value
1341 -- is determined based on gl_ledgers.net_income_code_combination_id in the
1342 -- routine INSERT_LEDGER_OPTIONS.
1343 ----------------------------------------------------------------------------
1344 g_transfer_to_gl_mode_code := NULL;
1345 g_acct_reversal_option_code := 'SIDE';
1346 g_enabled_flag := 'Y';
1347
1348 trace('< xla_acct_setup_pkg.set_default_values' , 10);
1349 EXCEPTION
1350 WHEN xla_exceptions_pkg.application_exception THEN
1351 RAISE;
1352 WHEN OTHERS THEN
1353 xla_exceptions_pkg.raise_message
1354 (p_location => 'xla_acct_setup_pkg.set_default_values');
1355 END set_default_values;
1356
1357
1358
1359 --=============================================================================
1360 --
1361 --
1362 --
1363 --=============================================================================
1364 PROCEDURE upgrade_ledger_options
1365 (p_application_id IN NUMBER
1366 ,p_ledger_id IN NUMBER
1367 ,p_acct_mode_code IN VARCHAR2
1368 ,p_acct_mode_override_flag IN VARCHAR2
1369 ,p_summary_report_flag IN VARCHAR2
1370 ,p_summary_report_override_flag IN VARCHAR2
1371 ,p_submit_xfer_to_gl_flag IN VARCHAR2
1375 ,p_stop_on_error IN VARCHAR2
1372 ,p_submit_xfer_override_flag IN VARCHAR2
1373 ,p_submit_gl_post_flag IN VARCHAR2
1374 ,p_submit_gl_post_override_flag IN VARCHAR2
1376 ,p_error_limit IN NUMBER
1377 ,p_processes IN NUMBER
1378 ,p_processing_unit_size IN NUMBER
1379 ,p_transfer_to_gl_mode_code IN VARCHAR2
1380 ,p_acct_reversal_option_code IN VARCHAR2) IS
1381 BEGIN
1382 trace('> xla_acct_setup_pkg.upgrade_ledger_options' , 10);
1383 trace('ledger_id = '||p_ledger_id , 20);
1384 trace('p_application_id = '||p_application_id , 20);
1385
1386 ----------------------------------------------------------------------------
1387 -- Validate parameters
1388 ----------------------------------------------------------------------------
1389 IF p_acct_mode_code IS NOT NULL AND p_acct_mode_code NOT IN ('D','F') THEN
1390 xla_exceptions_pkg.raise_message
1391 (p_appli_s_name => 'XLA'
1392 ,p_msg_name => 'XLA_COMMON_ERROR'
1393 ,p_token_1 => 'LOCATION'
1394 ,p_value_1 => 'upgrade_ledger_options'
1395 ,p_token_2 => 'ERROR'
1396 ,p_value_2 => 'The value '||p_acct_mode_code||' is invalid for p_acct_mode_code');
1397 END IF;
1398
1399 IF (p_acct_mode_override_flag IS NOT NULL AND p_acct_mode_override_flag NOT IN ('Y','N'))
1400 THEN
1401 xla_exceptions_pkg.raise_message
1402 (p_appli_s_name => 'XLA'
1403 ,p_msg_name => 'XLA_COMMON_ERROR'
1404 ,p_token_1 => 'LOCATION'
1405 ,p_value_1 => 'upgrade_ledger_options'
1406 ,p_token_2 => 'ERROR'
1407 ,p_value_2 => 'The value '||p_acct_mode_override_flag||
1408 ' is invalid for p_acct_mode_override_flag');
1409 END IF;
1410
1411 IF p_summary_report_flag IS NOT NULL AND p_summary_report_flag NOT IN ('D','S','N') THEN
1412 xla_exceptions_pkg.raise_message
1413 (p_appli_s_name => 'XLA'
1414 ,p_msg_name => 'XLA_COMMON_ERROR'
1415 ,p_token_1 => 'LOCATION'
1416 ,p_value_1 => 'upgrade_ledger_options'
1417 ,p_token_2 => 'ERROR'
1418 ,p_value_2 => 'The value '||p_summary_report_flag||' is invalid for p_summary_report_flag');
1419 END IF;
1420
1421 IF (p_summary_report_override_flag IS NOT NULL AND p_summary_report_override_flag NOT IN ('Y','N'))
1422 THEN
1423 xla_exceptions_pkg.raise_message
1424 (p_appli_s_name => 'XLA'
1425 ,p_msg_name => 'XLA_COMMON_ERROR'
1426 ,p_token_1 => 'LOCATION'
1427 ,p_value_1 => 'upgrade_ledger_options'
1428 ,p_token_2 => 'ERROR'
1429 ,p_value_2 => 'The value '||p_summary_report_override_flag||
1430 ' is invalid for p_summary_report_override_flag');
1431 END IF;
1432
1433 IF (p_submit_xfer_to_gl_flag IS NOT NULL AND p_submit_xfer_to_gl_flag NOT IN ('Y','N'))
1434 THEN
1435 xla_exceptions_pkg.raise_message
1436 (p_appli_s_name => 'XLA'
1437 ,p_msg_name => 'XLA_COMMON_ERROR'
1438 ,p_token_1 => 'LOCATION'
1439 ,p_value_1 => 'upgrade_ledger_options'
1440 ,p_token_2 => 'ERROR'
1441 ,p_value_2 => 'The value '||p_submit_xfer_to_gl_flag||
1442 ' is invalid for p_submit_xfer_to_gl_flag');
1443 END IF;
1444
1445 IF (p_submit_xfer_override_flag IS NOT NULL AND p_submit_xfer_override_flag NOT IN ('Y','N'))
1446 THEN
1447 xla_exceptions_pkg.raise_message
1448 (p_appli_s_name => 'XLA'
1449 ,p_msg_name => 'XLA_COMMON_ERROR'
1450 ,p_token_1 => 'LOCATION'
1451 ,p_value_1 => 'upgrade_ledger_options'
1452 ,p_token_2 => 'ERROR'
1453 ,p_value_2 => 'The value '||p_submit_xfer_override_flag||
1454 ' is invalid for p_submit_xfer_override_flag');
1455 END IF;
1456
1457 IF (p_submit_gl_post_flag IS NOT NULL AND p_submit_gl_post_flag NOT IN ('Y','N'))
1458 THEN
1459 xla_exceptions_pkg.raise_message
1460 (p_appli_s_name => 'XLA'
1461 ,p_msg_name => 'XLA_COMMON_ERROR'
1462 ,p_token_1 => 'LOCATION'
1463 ,p_value_1 => 'upgrade_ledger_options'
1464 ,p_token_2 => 'ERROR'
1465 ,p_value_2 => 'The value '||p_submit_gl_post_flag||
1466 ' is invalid for p_submit_gl_post_flag');
1467 END IF;
1468
1469 IF (p_submit_gl_post_override_flag IS NOT NULL AND p_submit_gl_post_override_flag NOT IN ('Y','N'))
1470 THEN
1471 xla_exceptions_pkg.raise_message
1472 (p_appli_s_name => 'XLA'
1473 ,p_msg_name => 'XLA_COMMON_ERROR'
1474 ,p_token_1 => 'LOCATION'
1475 ,p_value_1 => 'upgrade_ledger_options'
1476 ,p_token_2 => 'ERROR'
1477 ,p_value_2 => 'The value '||p_submit_gl_post_override_flag||
1478 ' is invalid for p_submit_gl_post_override_flag');
1479 END IF;
1480
1481 IF (p_stop_on_error IS NOT NULL AND p_stop_on_error NOT IN ('Y','N'))
1482 THEN
1483 xla_exceptions_pkg.raise_message
1484 (p_appli_s_name => 'XLA'
1485 ,p_msg_name => 'XLA_COMMON_ERROR'
1486 ,p_token_1 => 'LOCATION'
1487 ,p_value_1 => 'upgrade_ledger_options'
1488 ,p_token_2 => 'ERROR'
1492 IF (p_stop_on_error = 'Y' AND (p_error_limit IS NULL OR p_error_limit < 1))
1489 ,p_value_2 => 'The value '||p_stop_on_error||' is invalid for p_stop_on_error');
1490 END IF;
1491
1493 THEN
1494 xla_exceptions_pkg.raise_message
1495 (p_appli_s_name => 'XLA'
1496 ,p_msg_name => 'XLA_COMMON_ERROR'
1497 ,p_token_1 => 'LOCATION'
1498 ,p_value_1 => 'upgrade_ledger_options'
1499 ,p_token_2 => 'ERROR'
1500 ,p_value_2 => 'With p_stop_on_error = Y, a positive integer value should be '||
1501 'passed for p_error_limit');
1502 END IF;
1503
1504 IF (p_stop_on_error = 'N' AND p_error_limit IS NOT NULL)
1505 THEN
1506 xla_exceptions_pkg.raise_message
1507 (p_appli_s_name => 'XLA'
1508 ,p_msg_name => 'XLA_COMMON_ERROR'
1509 ,p_token_1 => 'LOCATION'
1510 ,p_value_1 => 'upgrade_ledger_options'
1511 ,p_token_2 => 'ERROR'
1512 ,p_value_2 => 'With p_stop_on_error = N, a NULL value should be '||
1513 'passed for p_error_limit');
1514 END IF;
1515
1516 IF p_processes < 1 THEN
1517 xla_exceptions_pkg.raise_message
1518 (p_appli_s_name => 'XLA'
1519 ,p_msg_name => 'XLA_COMMON_ERROR'
1520 ,p_token_1 => 'LOCATION'
1521 ,p_value_1 => 'upgrade_ledger_options'
1522 ,p_token_2 => 'ERROR'
1523 ,p_value_2 => 'The value '||p_processes||' is invalid for p_processes');
1524 END IF;
1525
1526 IF p_processing_unit_size < 1 THEN
1527 xla_exceptions_pkg.raise_message
1528 (p_appli_s_name => 'XLA'
1529 ,p_msg_name => 'XLA_COMMON_ERROR'
1530 ,p_token_1 => 'LOCATION'
1531 ,p_value_1 => 'upgrade_ledger_options'
1532 ,p_token_2 => 'ERROR'
1533 ,p_value_2 => 'The value '||p_processing_unit_size||' is invalid for p_processing_unit_size');
1534 END IF;
1535
1536 IF (p_transfer_to_gl_mode_code IS NOT NULL AND p_transfer_to_gl_mode_code NOT IN ('A','D','P'))
1537 THEN
1538 xla_exceptions_pkg.raise_message
1539 (p_appli_s_name => 'XLA'
1540 ,p_msg_name => 'XLA_COMMON_ERROR'
1541 ,p_token_1 => 'LOCATION'
1542 ,p_value_1 => 'upgrade_ledger_options'
1543 ,p_token_2 => 'ERROR'
1544 ,p_value_2 => 'The value '||p_transfer_to_gl_mode_code||
1545 ' is invalid for p_transfer_to_gl_mode_code');
1546 END IF;
1550 xla_exceptions_pkg.raise_message
1547
1548 IF (p_acct_reversal_option_code IS NOT NULL AND p_acct_reversal_option_code NOT IN ('SIDE','SIGN'))
1549 THEN
1551 (p_appli_s_name => 'XLA'
1552 ,p_msg_name => 'XLA_COMMON_ERROR'
1553 ,p_token_1 => 'LOCATION'
1554 ,p_value_1 => 'upgrade_ledger_options'
1555 ,p_token_2 => 'ERROR'
1556 ,p_value_2 => 'The value '||p_acct_reversal_option_code||
1557 ' is invalid for p_acct_reversal_option_code');
1558 END IF;
1559
1560 ----------------------------------------------------------------------------
1561 -- Update launch options, if there is already launch options for the
1562 -- ledger and the application
1563 ----------------------------------------------------------------------------
1564 UPDATE xla_launch_options SET
1565 accounting_mode_code = NVL(p_acct_mode_code, accounting_mode_code)
1566 ,accounting_mode_override_flag = NVL(p_acct_mode_override_flag, accounting_mode_override_flag)
1567 ,summary_report_flag = NVL(p_summary_report_flag, summary_report_flag)
1568 ,summary_report_override_flag = NVL(p_summary_report_override_flag, summary_report_override_flag)
1569 ,submit_transfer_to_gl_flag = NVL(p_submit_xfer_to_gl_flag, submit_transfer_to_gl_flag)
1570 ,submit_transfer_override_flag = NVL(p_submit_xfer_override_flag, submit_transfer_override_flag)
1571 ,submit_gl_post_flag = NVL(p_submit_gl_post_flag, submit_gl_post_flag)
1572 ,submit_gl_post_override_flag = NVL(p_submit_gl_post_override_flag,submit_gl_post_override_flag)
1573 ,error_limit = DECODE(p_stop_on_error, null, error_limit, p_error_limit)
1574 ,processes = NVL(p_processes, processes)
1575 ,processing_unit_size = NVL(p_processing_unit_size, processing_unit_size)
1576 ,last_update_date = sysdate
1577 ,last_updated_by = xla_environment_pkg.g_usr_id
1578 ,last_update_login = xla_environment_pkg.g_login_id
1579 WHERE ledger_id = p_ledger_id
1580 AND application_id = p_application_id;
1581
1582 ----------------------------------------------------------------------------
1583 -- Update ledger options, if there is already ledger options for the
1584 -- ledger and the application
1585 ----------------------------------------------------------------------------
1586 UPDATE xla_ledger_options SET
1587 transfer_to_gl_mode_code = NVL(p_transfer_to_gl_mode_code, transfer_to_gl_mode_code)
1588 ,acct_reversal_option_code = NVL(p_acct_reversal_option_code, acct_reversal_option_code)
1589 ,last_update_date = sysdate
1590 ,last_updated_by = xla_environment_pkg.g_usr_id
1591 ,last_update_login = xla_environment_pkg.g_login_id
1592 WHERE ledger_id = p_ledger_id
1593 AND application_id = p_application_id;
1594
1595 ----------------------------------------------------------------------------
1596 -- raise exception if no row in ledger opitons is updated. The reason:
1597 -- a. invalid application or
1598 -- b. invalid ledger
1599 ----------------------------------------------------------------------------
1600 IF SQL%ROWCOUNT = 0 THEN
1601 xla_exceptions_pkg.raise_message
1602 (p_appli_s_name => 'XLA'
1603 ,p_msg_name => 'XLA_COMMON_ERROR'
1604 ,p_token_1 => 'LOCATION'
1605 ,p_value_1 => 'upgrade_ledger_options'
1606 ,p_token_2 => 'ERROR'
1607 ,p_value_2 => 'Either the application is not registered or '||
1608 'setups are missing for the ledger and application');
1609 END IF;
1610
1611 trace('< xla_acct_setup_pkg.upgrade_ledger_options' , 10);
1612
1613 EXCEPTION
1614 WHEN xla_exceptions_pkg.application_exception THEN
1615 RAISE;
1616 WHEN OTHERS THEN
1617 xla_exceptions_pkg.raise_message
1618 (p_location => 'xla_acct_setup_pkg.upgrade_ledger_options');
1619 END upgrade_ledger_options;
1620
1621
1622 --=============================================================================
1623 --
1624 --
1625 --
1626 --
1627 --
1628 --
1629 --
1630 --
1631 --
1632 --
1633 -- Following gets executed when the package body is loaded first time
1634 --
1635 --
1636 --
1637 --
1638 --
1639 --
1640 --
1641 --
1642 --
1643 --
1644 --
1645 --
1646 --
1647 --=============================================================================
1648 BEGIN
1649 g_debug_flag := NVL(fnd_profile.value('XLA_DEBUG_TRACE'),'N');
1650
1651
1652 END xla_acct_setup_pkg;