1 PACKAGE BODY FA_MC_UPG1_PKG AS
2 /* $Header: faxmcu1b.pls 120.7 2009/03/27 21:20:25 bridgway ship $ */
3
4 -- commit size to control number assets to process in loop
5 G_Max_Commit_Size CONSTANT NUMBER := 1000;
6 G_rbook_name VARCHAR2(30);
7
8 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
9
10 FUNCTION lock_book (
11 p_book_type_code IN VARCHAR2,
12 p_rsob_id IN NUMBER)
13 RETURN BOOLEAN IS
14 /* ************************************************************************
15 This function locks the book controls row in order to prevent running
16 conversion utility simultaneously for the same book - reporting book
17 combination. Return TRUE if lock is obtained and FALSE if unable to
18 obtain a lock.
19 ************************************************************************ */
20
21 l_converted_flag varchar2(1);
22 BEGIN
23 select mrc_converted_flag
24 into l_converted_flag
25 from fa_mc_book_controls
26 where book_type_code = p_book_type_code AND
27 set_of_books_id = p_rsob_id
28 for update of
29 mrc_converted_flag
30 NOWAIT;
31 return(TRUE);
32 EXCEPTION
33 WHEN OTHERS THEN
34 fa_srvr_msg.add_sql_error (
35 calling_fn => 'fa_mc_upg1_pkg.lock_book');
36 return(FALSE);
37 END lock_book;
38
39
40 PROCEDURE create_drop_indexes(
41 p_mode IN VARCHAR2) IS
42
43 out_oracle_schema varchar2(100);
44 out_status varchar2(100);
45 out_industry varchar2(100);
46 x boolean;
47 BEGIN
48 x := fnd_installation.get_app_info('FND', out_status,
49 out_industry, out_oracle_schema);
50
51 IF (p_mode = 'D') THEN
52 if (g_print_debug) then
53 fa_debug_pkg.add('create_drop_indexes','mode',
54 'Dropping');
55 end if;
56
57 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
58 ad_ddl.drop_index,
59 'drop index FA_MC_ADJUSTMENTS_N1','INDEX');
60
61 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
62 ad_ddl.drop_index,
63 'drop index FA_MC_ADJUSTMENTS_N2','INDEX');
64
65 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
66 ad_ddl.drop_index,
67 'drop index FA_MC_ADJUSTMENTS_N3','INDEX');
68
69 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
70 ad_ddl.drop_index,
71 'drop index FA_MC_ADJUSTMENTS_N4','INDEX');
72
73 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
74 ad_ddl.drop_index,
75 'drop index FA_MC_ASSET_INVOICES_N1','INDEX');
76
77 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
78 ad_ddl.drop_index,
79 'drop index FA_MC_BOOKS_U1','INDEX');
80
81 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
82 ad_ddl.drop_index,
83 'drop index FA_MC_BOOKS_RATES_N1','INDEX');
84
85 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
86 ad_ddl.drop_index,
87 'drop index FA_MC_BOOKS_RATES_U1','INDEX');
88
89 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
90 ad_ddl.drop_index,
91 'drop index FA_MC_BOOK_CONTROLS_U1','INDEX');
92
93 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
94 ad_ddl.drop_index,
95 'drop index FA_MC_DEFERRED_DEPRN_N1','INDEX');
96
97 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
98 ad_ddl.drop_index,
99 'drop index FA_MC_DEFERRED_DEPRN_N2','INDEX');
100
101 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
102 ad_ddl.drop_index,
103 'drop index FA_MC_DEFERRED_DEPRN_N3','INDEX');
104
105 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
106 ad_ddl.drop_index,
107 'drop index FA_MC_DEFERRED_DEPRN_N4','INDEX');
108
109 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
110 ad_ddl.drop_index,
111 'drop index FA_MC_DEPRN_DETAIL_N1','INDEX');
112
113 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
114 ad_ddl.drop_index,
115 'drop index FA_MC_DEPRN_DETAIL_N2','INDEX');
116
117 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
118 ad_ddl.drop_index,
119 'drop index FA_MC_DEPRN_DETAIL_U1','INDEX');
120
121 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
122 ad_ddl.drop_index,
123 'drop index FA_MC_DEPRN_PERIODS_U1','INDEX');
124
125 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
126 ad_ddl.drop_index,
127 'drop index FA_MC_DEPRN_PERIODS_U2','INDEX');
128
129 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
130 ad_ddl.drop_index,
131 'drop index FA_MC_DEPRN_PERIODS_U3','INDEX');
132
133 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
134 ad_ddl.drop_index,
135 'drop index FA_MC_DEPRN_SUMMARY_N1','INDEX');
136
137 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
138 ad_ddl.drop_index,
139 'drop index FA_MC_DEPRN_SUMMARY_U1','INDEX');
140
141 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
142 ad_ddl.drop_index,
143 'drop index FA_MC_RETIREMENTS_U1','INDEX');
144
145 ELSIF (p_mode = 'C') then
146 -- create all the indexes
147
148 if (g_print_debug) then
149 fa_debug_pkg.add('create_drop_indexes','mode',
150 'Creating');
151 end if;
152
153 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
154 ad_ddl.create_index,
155 'create index FA_MC_ADJUSTMENTS_N1 ON
156 FA_MC_ADJUSTMENTS(
157 DISTRIBUTION_ID,
158 BOOK_TYPE_CODE,
159 PERIOD_COUNTER_CREATED,
160 SOURCE_TYPE_CODE,
161 ADJUSTMENT_TYPE,
162 SET_OF_BOOKS_ID)', 'INDEX');
163
164 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
165 ad_ddl.create_index,
166 'create index FA_MC_ADJUSTMENTS_N2 ON
167 FA_MC_ADJUSTMENTS(
168 ASSET_ID,
169 BOOK_TYPE_CODE,
170 PERIOD_COUNTER_CREATED,
171 SET_OF_BOOKS_ID)', 'INDEX');
172
173 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
174 ad_ddl.create_index,
175 'create index FA_MC_ADJUSTMENTS_N3 ON
176 FA_MC_ADJUSTMENTS(
177 JE_HEADER_ID,
178 JE_LINE_NUM,
179 SET_OF_BOOKS_ID)', 'INDEX');
180
181 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
182 ad_ddl.create_index,
183 'create index FA_MC_ADJUSTMENTS_N4 ON
184 FA_MC_ADJUSTMENTS(
185 BOOK_TYPE_CODE,
186 PERIOD_COUNTER_CREATED,
187 SET_OF_BOOKS_ID)', 'INDEX');
188
189
190 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
191 ad_ddl.create_index,
192 'create index FA_MC_ASSET_INVOICES_N1 ON
193 FA_MC_ASSET_INVOICES(
194 ASSET_ID,
195 ASSET_INVOICE_ID,
196 SET_OF_BOOKS_ID)', 'INDEX');
197
198 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
199 ad_ddl.create_index,
200 'create unique index FA_MC_BOOKS_U1 ON
201 FA_MC_BOOKS(
202 TRANSACTION_HEADER_ID_IN,
203 SET_OF_BOOKS_ID)', 'INDEX');
204
205
206 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
207 ad_ddl.create_index,
208 'create index FA_MC_BOOKS_RATES_N1 ON
209 FA_MC_BOOKS_RATES(
210 SET_OF_BOOKS_ID,
211 TRANSACTION_HEADER_ID)', 'INDEX');
212
213 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
214 ad_ddl.create_index,
215 'create unique index FA_MC_BOOKS_RATES_U1 ON
216 FA_MC_BOOKS_RATES(
217 SET_OF_BOOKS_ID,
218 TRANSACTION_HEADER_ID,
219 INVOICE_TRANSACTION_ID)', 'INDEX');
220
221 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
222 ad_ddl.create_index,
223 'create unique index FA_MC_BOOK_CONTROLS_U1 ON
224 FA_MC_BOOK_CONTROLS(
225 SET_OF_BOOKS_ID,
226 BOOK_TYPE_CODE)', 'INDEX');
227
228 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
229 ad_ddl.create_index,
230 'create index FA_MC_DEFERRED_DEPRN_N1 ON
231 FA_MC_DEFERRED_DEPRN(
232 CORP_BOOK_TYPE_CODE,
233 TAX_BOOK_TYPE_CODE,
234 EXPENSE_JE_LINE_NUM,
235 SET_OF_BOOKS_ID)', 'INDEX');
236
237 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
238 ad_ddl.create_index,
239 'create index FA_MC_DEFERRED_DEPRN_N2 ON
240 FA_MC_DEFERRED_DEPRN(
241 CORP_BOOK_TYPE_CODE,
242 TAX_BOOK_TYPE_CODE,
243 RESERVE_JE_LINE_NUM,
244 SET_OF_BOOKS_ID)', 'INDEX');
245
246 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
247 ad_ddl.create_index,
248 'create index FA_MC_DEFERRED_DEPRN_N3 ON
249 FA_MC_DEFERRED_DEPRN(
250 JE_HEADER_ID,
251 EXPENSE_JE_LINE_NUM,
252 SET_OF_BOOKS_ID)', 'INDEX');
253
254 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
255 ad_ddl.create_index,
256 'create index FA_MC_DEFERRED_DEPRN_N4 ON
257 FA_MC_DEFERRED_DEPRN(
258 JE_HEADER_ID,
259 RESERVE_JE_LINE_NUM,
260 SET_OF_BOOKS_ID)', 'INDEX');
261
262 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
263 ad_ddl.create_index,
264 'create index FA_MC_DEPRN_DETAIL_N1 ON
265 FA_MC_DEPRN_DETAIL(
266 ASSET_ID,
267 BOOK_TYPE_CODE,
268 PERIOD_COUNTER,
269 SET_OF_BOOKS_ID)', 'INDEX');
270
271 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
272 ad_ddl.create_index,
273 'create index FA_MC_DEPRN_DETAIL_N2 ON
274 FA_MC_DEPRN_DETAIL(
275 BOOK_TYPE_CODE,
276 PERIOD_COUNTER,
277 SET_OF_BOOKS_ID)', 'INDEX');
278
279 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
280 ad_ddl.create_index,
281 'create unique index FA_MC_DEPRN_DETAIL_U1 ON
282 FA_MC_DEPRN_DETAIL(
283 DISTRIBUTION_ID,
284 ASSET_ID,
285 BOOK_TYPE_CODE,
286 PERIOD_COUNTER,
287 SET_OF_BOOKS_ID)', 'INDEX');
288
289 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
290 ad_ddl.create_index,
291 'create unique index FA_MC_DEPRN_PERIODS_U1 ON
292 FA_MC_DEPRN_PERIODS(
293 BOOK_TYPE_CODE,
294 PERIOD_NAME,
295 SET_OF_BOOKS_ID)', 'INDEX');
296
297 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
298 ad_ddl.create_index,
299 'create unique index FA_MC_DEPRN_PERIODS_U2 ON
300 FA_MC_DEPRN_PERIODS(
301 BOOK_TYPE_CODE,
302 FISCAL_YEAR,
303 PERIOD_NUM,
304 SET_OF_BOOKS_ID)', 'INDEX');
305
306 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
307 ad_ddl.create_index,
308 'create unique index FA_MC_DEPRN_PERIODS_U3 ON
309 FA_MC_DEPRN_PERIODS(
310 BOOK_TYPE_CODE,
311 PERIOD_COUNTER,
312 SET_OF_BOOKS_ID)', 'INDEX');
313 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
314 ad_ddl.create_index,
315 'create index FA_MC_DEPRN_SUMMARY_N1 ON
316 FA_MC_DEPRN_SUMMARY(
317 BOOK_TYPE_CODE,
318 PERIOD_COUNTER,
319 SET_OF_BOOKS_ID)', 'INDEX');
320
321 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
322 ad_ddl.create_index,
323 'create unique index FA_MC_DEPRN_SUMMARY_U1 ON
324 FA_MC_DEPRN_SUMMARY(
325 ASSET_ID,
326 BOOK_TYPE_CODE,
327 PERIOD_COUNTER,
328 SET_OF_BOOKS_ID)', 'INDEX');
329
330 ad_ddl.do_ddl(out_oracle_schema, 'OFA',
331 ad_ddl.create_index,
332 'create unique index FA_MC_RETIREMENTS_U1 ON
333 FA_MC_RETIREMENTS(
334 SET_OF_BOOKS_ID,
335 RETIREMENT_ID)', 'INDEX');
336 END IF;
337
338 EXCEPTION
339 WHEN OTHERS THEN
340 fa_srvr_msg.add_sql_error (
341 calling_fn => 'fa_mc_upg1_pkg.create_drop_indexes');
342 END create_drop_indexes;
343
344
345 PROCEDURE validate_setup(
346 p_book_type_code IN VARCHAR2,
347 p_reporting_book IN VARCHAR2,
348 X_from_currency OUT NOCOPY VARCHAR2,
349 X_to_currency OUT NOCOPY VARCHAR2,
350 X_rsob_id OUT NOCOPY NUMBER,
351 X_psob_id OUT NOCOPY NUMBER) IS
352 /* ************************************************************************
353 This procedure validates the set up and returns information about
354 reporting book. It checks if the book being converted is an MRC
355 source book, checks if reporting book is associated to the primary book
356 and whether the reporting book is converted or not. If setup is not ok
357 it raises the appropriate exception and errors out. If setup is ok and
358 reporting book is yet to be converted, it locks the reporting books
359 row.
360 *************************************************************************/
361
362 l_rsob_id number;
363 l_psob_id number;
364 l_mc_source_flag varchar2(1);
365 l_mrc_converted_flag varchar2(1);
366 l_status boolean;
367 l_enabled_flag varchar2(1);
368
369 mc_source_error exception;
370 rbook_setup_error exception;
371 rbook_converted_error exception;
372 lock_error exception;
373 rbook_disabled_error exception;
374
375 CURSOR check_books IS
376 SELECT
377 glsob.set_of_books_id,
378 mcbc.primary_set_of_books_id,
379 mcbc.currency_code,
380 mcbc.primary_currency_code,
381 mcbc.mrc_converted_flag,
382 mcbc.enabled_flag,
383 nvl(bc.mc_source_flag,'N')
384 FROM
385 fa_mc_book_controls mcbc,
386 gl_sets_of_books glsob,
387 fa_book_controls bc
388 WHERE
389 bc.book_type_code = p_book_type_code AND
390 mcbc.book_type_code = bc.book_type_code AND
391 bc.set_of_books_id = mcbc.primary_set_of_books_id AND
392 glsob.name = p_reporting_book AND
393 glsob.set_of_books_id = mcbc.set_of_books_id AND
394 glsob.mrc_sob_type_code = 'R';
395
396 BEGIN
397 G_rbook_name := p_reporting_book;
398 OPEN check_books;
399 FETCH check_books into X_rsob_id,
400 X_psob_id,
401 X_to_currency,
402 X_from_currency,
403 l_mrc_converted_flag,
404 l_enabled_flag,
405 l_mc_source_flag;
406 IF (check_books%NOTFOUND) THEN
407 RAISE rbook_setup_error;
408 ELSIF (l_mc_source_flag = 'N') THEN
409 RAISE mc_source_error;
410 ELSIF (l_mrc_converted_flag = 'Y') THEN
411 RAISE rbook_converted_error;
412 ELSIF (l_enabled_flag = 'N') THEN
413 RAISE rbook_disabled_error;
414 END IF;
415 CLOSE check_books;
416
417 l_status := lock_book(p_book_type_code, X_rsob_id);
418
419 IF (not l_status) THEN
420 RAISE lock_error;
421 END IF;
422
423 if (g_print_debug) then
424 fa_debug_pkg.add('validate_setup','reporting sobid',
425 X_rsob_id);
426 fa_debug_pkg.add('validate_setup','primary sobid',
427 X_psob_id);
428 fa_debug_pkg.add('validate_setup','to currency',
429 X_to_currency);
430 fa_debug_pkg.add('validate_setup','from currency',
431 X_from_currency);
432 fa_debug_pkg.add('validate_setup','mrc converted',
433 l_mrc_converted_flag);
434 end if;
435
436 EXCEPTION
437 WHEN mc_source_error THEN
438 fa_srvr_msg.add_message (
439 calling_fn => 'fa_mc_upg1_pkg.validate_setup',
440 name => 'FA_MRC_NOT_SOURCE',
441 token1 => 'BOOK',
442 value1 => p_book_type_code);
443
444 app_exception.raise_exception;
445
446 WHEN rbook_setup_error THEN
447 fa_srvr_msg.add_message (
448 calling_fn => 'fa_mc_upg1_pkg.validate_setup',
449 name => 'FA_MRC_BOOK_NOT_ASSOCIATED',
450 token1 => 'REPORTING_BOOK',
451 value1 => G_rbook_name,
452 token2 => 'BOOK',
453 value2 => p_book_type_code);
454 app_exception.raise_exception;
455
456 WHEN rbook_converted_error THEN
457 fa_srvr_msg.add_message (
458 calling_fn => 'fa_mc_upg1_pkg.validate_setup',
459 name => 'FA_MRC_BOOK_CONVERTED',
460 token1 => 'REPORTING_BOOK',
461 value1 => G_rbook_name,
462 token2 => 'BOOK',
463 value2 => p_book_type_code);
464 app_exception.raise_exception;
465
466 WHEN rbook_disabled_error THEN
467 fa_srvr_msg.add_message (
468 calling_fn => 'fa_mc_upg1_pkg.validate_setup',
469 name => 'FA_MRC_BOOK_DISABLED',
470 token1 => 'REPORTING_BOOK',
471 value1 => G_rbook_name,
472 token2 => 'BOOK',
473 value2 => p_book_type_code);
474 app_exception.raise_exception;
475
476 WHEN lock_error THEN
477 fa_srvr_msg.add_sql_error (
478 calling_fn => 'fa_mc_upg1_pkg.lock_book');
479 app_exception.raise_exception;
480
481 WHEN OTHERS THEN
482 fa_srvr_msg.add_sql_error(
483 calling_fn => 'fa_mc_upg1_pkg.validate_setup');
484 app_exception.raise_exception;
485
486 END validate_setup;
487
488
489 PROCEDURE validate_rate (
490 p_book_type_code IN VARCHAR2,
491 p_rsob_id IN NUMBER,
492 p_fixed_rate IN VARCHAR2,
493 X_fixed_conversion OUT NOCOPY VARCHAR2) IS
494 /* ************************************************************************
495 This procedure will validate fa_mc_conversion_rates table. It will
496 check to see that all assets have a conversion_basis. It also checks
497 to see if the conversion is a fixed rate conversion based on user input
498 and raises an exception when user specifies that it is a fixed rate
499 conversion and when there are variable rates for the assets. This
500 procedure will return whether this is a fixed rate conversion or not.
501 ************************************************************************ */
502
503 l_count number;
504 l_exchange_rate number;
505 l_conversion_basis varchar2(1);
506 fixed_rate_error exception;
507 conversion_basis_error exception;
508 cost_basis_error exception;
509
510 CURSOR check_rate IS
511 SELECT distinct exchange_rate, conversion_basis
512 FROM
513 fa_mc_conversion_rates
514 WHERE
515 set_of_books_id = p_rsob_id AND
516 book_type_code = p_book_type_code;
517 BEGIN
518
519 -- check if conversion basis is null for any asset
520 -- and raise error if it is the case
521
522 SELECT count(*)
523 INTO l_count
524 FROM fa_mc_conversion_rates
525 WHERE
526 set_of_books_id = p_rsob_id AND
527 book_type_code = p_book_type_code AND
528 conversion_basis is null AND
529 status in ('L','F');
530
531 IF (l_count > 0) THEN
532 RAISE conversion_basis_error;
533 END IF;
534
535 l_count := 0;
536
537 -- validate if all assets have fixed rate based on user input
538 -- of fixed rate conversion
539 -- If user indicates it is fixed rate and it is not a fixed rate
540 -- then raise error
541
542 OPEN check_rate;
543 LOOP
544 FETCH check_rate into
545 l_exchange_rate,
546 l_conversion_basis;
547 exit WHEN ((check_rate%NOTFOUND) OR (l_count > 1));
548 l_count := l_count + 1;
549 END LOOP;
550 CLOSE check_rate;
551
552 IF (SUBSTRB(p_fixed_rate,1,1) = 'Y' AND l_count > 1) THEN
553 RAISE fixed_rate_error;
554 ELSIF ((l_count = 1) AND (l_conversion_basis = 'R')) THEN
555 X_fixed_conversion := 'Y';
556 ELSE X_fixed_conversion := 'N';
557 END IF;
558
559 EXCEPTION
560
561 WHEN fixed_rate_error THEN
562 fa_srvr_msg.add_message (
563 calling_fn => 'fa_mc_upg1_pkg.validate_rate',
564 name => 'FA_MRC_NOT_FIXED_RATE');
565 app_exception.raise_exception;
566
567 WHEN conversion_basis_error THEN
568 fa_srvr_msg.add_message (
569 calling_fn => 'fa_mc_upg1_pkg.validate_rate',
570 name => 'FA_MRC_NO_BASIS');
571 app_exception.raise_exception;
572
573 WHEN OTHERS THEN
574 fa_srvr_msg.add_sql_error(
575 calling_fn => 'fa_mc_upg1_pkg.validate_rate');
576 app_exception.raise_exception;
577
578 END validate_rate;
579
580
581 PROCEDURE get_conversion_info(
582 p_book_type_code IN VARCHAR2,
583 p_psob_id IN NUMBER,
584 p_rsob_id IN NUMBER,
585 X_start_pc OUT NOCOPY NUMBER,
586 X_end_pc OUT NOCOPY NUMBER,
587 X_conv_date OUT NOCOPY DATE,
588 X_conv_type OUT NOCOPY VARCHAR2,
589 X_accounting_date OUT NOCOPY DATE) IS
590 /* ************************************************************************
591 This procedure will get the conversion information for the reporting
592 book being converted from gl_mc_book_assignments and using this info
593 also retrieves the period counters to convert based on the fiscal
594 year that corresponds to the First MRC Period in GL. This procedure
595 return the start and end period counters, conversion date and type.
596 Raises appropriate exception if it fails to retreive the conversion
597 info.
598 ************************************************************************ */
599
600 l_start_fy number;
601 l_start_pc number; -- period counter to start converting
602 l_end_pc number; -- current period counter
603 l_prior_period_num number;
604 l_pers_per_year number;
605 l_first_mrc_date date;
606 l_first_mrc_period varchar2(15);
607 l_prior_fa_period varchar2(15);
608 l_conv_type varchar2(30);
609 l_conv_date date;
610 l_first_future_period varchar2(15);
611 l_first_period_num number(15);
612
613 first_mrc_period_error exception;
614 prev_period_error exception;
615 period_info_error exception;
616 conversion_info_error exception;
617
618 l_fa_period_count number;
619 l_gl_period_count number;
620 check_fa_gl_periods boolean;
621 l_period_type VARCHAR2(15);
622 l_period_year number(15);
623
624 CURSOR get_mrc_conv_info IS
625 SELECT
626 glba.alc_init_date,
627 glba.alc_init_period,
628 glba.alc_initializing_rate_type,
629 glba.alc_initializing_rate_date,
630 glps.effective_period_num
631 FROM
632 gl_period_statuses glps,
633 gl_ledger_relationships glba
634 WHERE
635 glba.target_ledger_id = p_rsob_id AND
636 glba.source_ledger_id = p_psob_id AND
637 glps.application_id = 101 AND
638 glba.application_id = 101 AND
639 glps.ledger_id = p_psob_id AND
640 glba.relationship_type_code = 'SUBLEDGER' AND
641 glps.period_name = glba.alc_init_period;
642
643
644 CURSOR check_first_mrc_period IS
645 SELECT
646 ps.period_name,
647 ps.period_type,
648 ps.PERIOD_YEAR
649 FROM
650 gl_period_statuses ps
651 WHERE
652 ps.application_id = 101 AND
653 ps.set_of_books_id = p_psob_id AND
654 ps.effective_period_num = (
655 SELECT min(ps2.effective_period_num)
656 FROM gl_period_statuses ps2
657 WHERE
658 ps2.application_id =
659 ps.application_id AND
660 ps2.set_of_books_id =
661 ps.set_of_books_id AND
662 ps2. closing_status in
663 ('F', 'N') AND
664 ps2.effective_period_num > (
665 SELECT ps3.effective_period_num
666 FROM gl_period_statuses ps3,
667 gl_sets_of_books sb
668 WHERE ps3.application_id =
669 ps.application_id
670 AND ps3.set_of_books_id =
671 ps.set_of_books_id
672 AND ps3.period_name =
673 sb.latest_opened_period_name
674 AND sb.set_of_books_id =
675 ps.set_of_books_id));
676
677 CURSOR get_prev_period IS
678 SELECT ps.period_name, ps.end_date
679 FROM gl_period_statuses ps
680 WHERE ps.application_id = 101 AND
681 ps.set_of_books_id = p_psob_id AND
682 ps.effective_period_num = (
683 SELECT max(ps2.effective_period_num)
684 FROM gl_period_statuses ps2
685 WHERE ps2.application_id =
686 ps.application_id AND
687 ps2.set_of_books_id =
688 ps.set_of_books_id AND
689 ps2.effective_period_num <
690 l_first_period_num AND
691 ps2.adjustment_period_flag <> 'Y');
692
693 CURSOR start_end_periods IS
694 SELECT
695 dp.fiscal_year,
696 dp.period_counter,
697 dp2.period_counter
698 FROM
699 fa_deprn_periods dp,
700 fa_deprn_periods dp2,
701 fa_deprn_periods dp3,
702 fa_book_controls bc
703 WHERE
704 bc.book_type_code = p_book_type_code AND
705 dp3.period_name = l_prior_fa_period AND
706 dp3.book_type_code = bc.book_type_code AND
707 dp3.fiscal_year = dp.fiscal_year AND
708 dp.book_type_code = bc.book_type_code AND
709 /* BUG# 1483489 - need to dynamically get the period_num
710 -- bridgway 10/30/00
711
712 dp.period_num = 1 AND
713 */
714 dp.period_num =
715 (select min(period_num)
716 from fa_deprn_periods dp4
717 where dp4.book_type_code = dp.book_type_code
718 and dp4.fiscal_year = dp.fiscal_year) AND
719 dp2.book_type_code = bc.book_type_code AND
720 dp2.period_close_date is NULL;
721
722 BEGIN
723
724 OPEN get_mrc_conv_info;
725 FETCH get_mrc_conv_info into l_first_mrc_date,
726 l_first_mrc_period,
727 X_conv_type,
728 X_conv_date,
729 l_first_period_num;
730 IF (get_mrc_conv_info%NOTFOUND) THEN
731 RAISE conversion_info_error;
732 END IF;
733 CLOSE get_mrc_conv_info;
734
735 OPEN check_first_mrc_period;
736 FETCH check_first_mrc_period into l_first_future_period,
737 l_period_type,
738 l_period_year;
739 IF ((check_first_mrc_period%NOTFOUND) OR
740 (l_first_mrc_period <> l_first_future_period)) THEN
741 RAISE first_mrc_period_error;
742 END IF;
743 CLOSE check_first_mrc_period;
744
745 if (g_print_debug) then
746 fa_debug_pkg.add('get_conversion_info','checking fa gl periods',
747 'check_fa_gl_periods');
748 end if;
749
750 select ct.NUMBER_PER_FISCAL_YEAR
751 into l_fa_period_count
752 from fa_calendar_types ct,
753 fa_book_controls bc
754 where bc.book_type_code = p_book_type_code
755 and bc.deprn_calendar = ct.CALENDAR_TYPE;
756
757 select count(*)
758 into l_gl_period_count
759 from gl_period_statuses ps
760 where ps.application_id = 101
761 AND ps.set_of_books_id = p_psob_id
762 AND ps.period_year = l_period_year
763 AND ps.adjustment_period_flag <> 'Y';
764
765 if (l_fa_period_count = l_gl_period_count) then
766 check_fa_gl_periods := TRUE;
767 else check_fa_gl_periods := FALSE;
768 end if;
769
770 if check_fa_gl_periods then
771
772 OPEN get_prev_period;
773 FETCH get_prev_period INTO l_prior_fa_period, X_accounting_date;
774 IF (get_prev_period%NOTFOUND) THEN
775 RAISE prev_period_error;
776 END IF;
777 CLOSE get_prev_period;
778
779 OPEN start_end_periods;
780 FETCH start_end_periods into l_start_fy,
781 X_start_pc,
782 X_end_pc;
783 IF (start_end_periods%NOTFOUND) THEN
784 RAISE period_info_error;
785 END IF;
786 CLOSE start_end_periods;
787
788 else
789 -- select the first period and current open period of the
790 -- current fiscal year in FA
791 SELECT
792 dp.fiscal_year,
793 dp.period_counter,
794 dp2.period_counter
795 INTO l_start_fy, X_start_pc, X_end_pc
796 FROM
797 fa_deprn_periods dp2,
798 fa_deprn_periods dp
799 WHERE dp.book_type_code = p_book_type_code
800 AND dp2.book_type_code = dp.book_type_code
801 AND dp2.period_close_date is null
802 AND dp.fiscal_year = dp2.fiscal_year
803 AND dp.period_num = 1;
804
805 end if;
806
807 if (g_print_debug) then
808 fa_debug_pkg.add('get_conversion_info','l_first_mrc_period',
809 l_first_mrc_period);
810 fa_debug_pkg.add('get_conversion_info','l_prior_fa_period',
811 l_prior_fa_period );
812 fa_debug_pkg.add('get_conversion_info','start_fy', l_start_fy);
813 fa_debug_pkg.add('get_conversion_info','X_start_pc',X_start_pc);
814 fa_debug_pkg.add('get_conversion_info','X_end_pc', X_end_pc);
815 end if;
816
817 EXCEPTION
818 WHEN conversion_info_error THEN
819 fa_srvr_msg.add_message (
820 calling_fn => 'fa_mc_upg1_pkg.get_conversion_info',
821 name => 'FA_MRC_CONV_INFO_ERROR',
822 token1 => 'REPORTING_BOOK',
823 value1 => G_rbook_name);
824 app_exception.raise_exception;
825
826 WHEN first_mrc_period_error THEN
827 fa_srvr_msg.add_message (
828 calling_fn => 'fa_mc_upg1_pkg.get_conversion_info',
829 name => 'FA_MRC_NOT_FIRST_FUTURE',
830 token1 => 'FIRST_MRC_PERIOD',
831 value1 => l_first_mrc_period);
832 app_exception.raise_exception;
833
834 WHEN prev_period_error THEN
835 fa_srvr_msg.add_message (
836 calling_fn => 'fa_mc_upg1_pkg.get_conversion_info',
837 name => 'FA_MRC_PREV_PERIOD_ERR',
838 token1 => 'FIRST_MRC_PERIOD',
839 value1 => l_first_mrc_period);
840 app_exception.raise_exception;
841
842 WHEN period_info_error THEN
843 fa_srvr_msg.add_message (
844 calling_fn => 'fa_mc_upg1_pkg.get_conversion_info',
845 name => 'FA_SHARED_SEL_DEPRN_PERIODS');
846 app_exception.raise_exception;
847
848 WHEN OTHERS THEN
849 fa_srvr_msg.add_sql_error (
850 calling_fn => 'fa_mc_upg1_pkg.get_conversion_info');
851 app_exception.raise_exception;
852
853 END get_conversion_info;
854
855
856 PROCEDURE set_conversion_status(
857 p_book_type_code IN VARCHAR2,
858 p_rsob_id IN NUMBER,
859 p_start_pc IN NUMBER,
860 p_end_pc IN NUMBER,
861 p_fixed_conversion IN VARCHAR2,
862 p_mode IN VARCHAR2) IS
863 /* ************************************************************************
864 This procedure sets the conversion status in fa_mc_conversion_history
865 and fa_mc_book_controls for the Primary Book - Reporting book combination.
866 This procedure is called in different modes - select, running, converted.
867 When called in select mode, inserts a new row into conversion history
868 and sets book controls also to S. This status will then be used in
869 transaction approval to prevent transactions in the Primary Book until
870 conversion is completed - status of C. The conversion_status is used to
871 prevent running conversion before selection. The conversion_status in
872 fa_mc_conversion_history and fa_mc_book_controls will be kept in synch.
873 *************************************************************************/
874
875 BEGIN
876 IF (p_mode = 'S') THEN
877
878 -- delete row from a previous run which is out of date
879 DELETE FROM fa_mc_conversion_history
880 WHERE set_of_books_id = p_rsob_id AND
881 book_type_code = p_book_type_code;
882
883 INSERT INTO FA_MC_CONVERSION_HISTORY(
884 set_of_books_id,
885 book_type_code,
886 conversion_status,
887 period_counter_selected,
888 last_update_date)
889 VALUES(
890 p_rsob_id,
891 p_book_type_code,
892 p_mode,
893 p_end_pc,
894 sysdate);
895
896 UPDATE fa_mc_book_controls
897 SET conversion_status = p_mode
898 WHERE
899 set_of_books_id = p_rsob_id AND
900 book_type_code = p_book_type_code;
901
902 ELSIF (p_mode = 'R') THEN
903
904 UPDATE fa_mc_conversion_history
905 SET conversion_status = p_mode,
906 period_counter_start = p_start_pc,
907 last_update_date = sysdate,
908 fixed_rate_conversion = p_fixed_conversion
909 WHERE
910 set_of_books_id = p_rsob_id AND
911 book_type_code = p_book_type_code;
912
913 UPDATE fa_mc_book_controls
914 SET conversion_status = p_mode
915 WHERE
916 set_of_books_id = p_rsob_id AND
917 book_type_code = p_book_type_code;
918
919 ELSIF (p_mode = 'SE') THEN
920
921 -- called when select program ends in error. delete the record from
922 -- conversion history to force rerun of selection program phase 1
923 -- rollback assets that have been inserted into rates table since
924 -- the last commit
925
926 FND_CONCURRENT.AF_ROLLBACK;
927 DELETE FROM fa_mc_conversion_history
928 WHERE set_of_books_id = p_rsob_id AND
929 book_type_code = p_book_type_code;
930
931 UPDATE fa_mc_book_controls
932 SET conversion_status = NULL
933 WHERE set_of_books_id = p_rsob_id AND
934 book_type_code = p_book_type_code;
935
936 ELSIF (p_mode = 'RE') THEN
937 -- called when conversion program ends in error
938 -- set conversion status to Error
939
940 UPDATE fa_mc_conversion_history
941 SET conversion_status = 'E'
942 WHERE
943 set_of_books_id = p_rsob_id AND
944 book_type_code = p_book_type_code;
945
946 UPDATE fa_mc_book_controls
947 SET conversion_status = 'E'
948 WHERE set_of_books_id = p_rsob_id AND
949 book_type_code = p_book_type_code;
950
951 ELSIF (p_mode = 'C') THEN
952
953 UPDATE fa_mc_conversion_history
954 SET conversion_status = p_mode,
955 period_counter_converted = p_end_pc,
956 last_update_date = sysdate
957 WHERE
958 set_of_books_id = p_rsob_id AND
959 book_type_code = p_book_type_code;
960
961 UPDATE fa_mc_book_controls
962 SET mrc_converted_flag = 'Y',
963 last_period_counter = p_end_pc - 1,
964 conversion_status = p_mode
965 WHERE
966 set_of_books_id = p_rsob_id AND
967 book_type_code = p_book_type_code;
968 END IF;
969
970 FND_CONCURRENT.AF_COMMIT;
971
972 EXCEPTION
973 WHEN OTHERS THEN
974 fa_srvr_msg.add_sql_error (
975 calling_fn => 'fa_mc_upg1_pkg.set_conversion_status');
976 app_exception.raise_exception;
977
978 END set_conversion_status;
979
980
981 PROCEDURE get_candidate_assets(
982 p_book_type_code IN VARCHAR2,
983 p_rsob_id IN NUMBER,
984 p_start_pc IN NUMBER,
985 p_end_pc IN NUMBER,
986 p_exchange_rate IN NUMBER,
987 p_fixed_rate IN VARCHAR2,
988 X_total_assets OUT NOCOPY NUMBER) IS
989 /* ************************************************************************
990 This procedure selects all the assets in a Primary Book that need to be
991 converted for a given reporting book and inserts them into
992 fa_mc_conversion_rates. The assets selected are those that are not
993 fully retired as of the beginning of the fiscal year, represented by
994 p_start_pc, being converted. All other assets will be selected.
995 The assets are selected in two parts, those that have DEPRN rows in the
996 year being converted and those that have their last DEPRN row in a
997 prior year. LAST_PERIOD_COUNTER indicates the last period with a DEPRN row
998 for each asset and helps to avoid using max later on in conversion.
999 The assets are selected in a loop so that commit size does
1000 not get too large to prevent running out of rollback segments.
1001 ************************************************************************ */
1002 l_lock_status BOOLEAN;
1003 lock_error EXCEPTION;
1004
1005 BEGIN
1006
1007 -- lock the book controls row for book - reporting book combination
1008 l_lock_status := lock_book(
1009 p_book_type_code,
1010 p_rsob_id);
1011 IF (NOT l_lock_status) THEN
1012 RAISE lock_error;
1013 END IF;
1014
1015 -- First delete previously selected assets since if this is
1016 -- called it indicates that phase 1 is being run and we will reselect
1017 -- all the assets again as the earlier run is out of date
1018
1019 DELETE FROM fa_mc_conversion_rates
1020 WHERE set_of_books_id = p_rsob_id AND
1021 book_type_code = p_book_type_code;
1022
1023 X_total_assets := 0;
1024
1025 -- Select and insert 1000 candidate assets at a time in LOOP
1026 LOOP
1027 -- First select all assets with DEPRN rows in FY being converted
1028
1029 INSERT INTO FA_MC_CONVERSION_RATES(
1030 ASSET_ID,
1031 SET_OF_BOOKS_ID,
1032 BOOK_TYPE_CODE,
1033 EXCHANGE_RATE,
1034 COST,
1035 PRIMARY_CUR_COST,
1036 CONVERSION_BASIS,
1037 STATUS,
1038 LAST_PERIOD_COUNTER)
1039 SELECT ad.asset_id,
1040 p_rsob_id,
1041 p_book_type_code,
1042 NULL,
1043 NULL,
1044 bk.cost,
1045 DECODE(p_fixed_rate,
1046 'Y', 'R',
1047 decode(bk.cost,
1048 0, 'R',
1049 NULL)),
1050 'F',
1051 ds.period_counter
1052 FROM
1053 fa_deprn_summary ds,
1054 fa_mc_conversion_rates cr,
1055 fa_books bk,
1056 fa_additions ad
1057 WHERE
1058 bk.date_ineffective is NULL AND
1059 bk.book_type_code = p_book_type_code AND
1060 nvl(bk.period_counter_fully_retired, p_end_pc +1) >=
1061 p_start_pc AND
1062 bk.asset_id = ad.asset_id AND
1063 cr.asset_id(+) = bk.asset_id AND
1064 cr.set_of_books_id(+) = p_rsob_id AND
1065 cr.book_type_code(+) = bk.book_type_code AND
1066 cr.status is NULL AND
1067 ds.asset_id = bk.asset_id AND
1068 ds.book_type_code = bk.book_type_code AND
1069 ds.period_counter = (
1070 SELECT max(ds2.period_counter)
1071 FROM fa_deprn_summary ds2
1072 WHERE ds2.asset_id = ds.asset_id AND
1073 ds2.book_type_code =
1074 ds.book_type_code AND
1075 ds2.period_counter between p_start_pc
1076 and p_end_pc) AND
1077 rownum+0 <= G_Max_Commit_Size;
1078
1079 X_total_assets := X_total_assets + SQL%ROWCOUNT;
1080
1081 IF SQL%NOTFOUND THEN
1082 EXIT;
1083 END IF;
1084 FND_CONCURRENT.AF_COMMIT;
1085
1086 -- obtain lock again after commit
1087 l_lock_status := lock_book(
1088 p_book_type_code,
1089 p_rsob_id);
1090 IF (NOT l_lock_status) THEN
1091 RAISE lock_error;
1092 END IF;
1093
1094 END LOOP;
1095
1096 LOOP
1097 -- select assets with last DEPRN row in prior Fiscal Year
1098
1099 INSERT INTO FA_MC_CONVERSION_RATES(
1100 ASSET_ID,
1101 SET_OF_BOOKS_ID,
1102 BOOK_TYPE_CODE,
1103 EXCHANGE_RATE,
1104 COST,
1105 PRIMARY_CUR_COST,
1106 CONVERSION_BASIS,
1107 STATUS,
1108 LAST_PERIOD_COUNTER)
1109 SELECT ad.asset_id,
1110 p_rsob_id,
1111 p_book_type_code,
1112 NULL,
1113 NULL,
1114 bk.cost,
1115 DECODE(p_fixed_rate,
1116 'Y', 'R',
1117 decode(bk.cost,
1118 0, 'R',
1119 NULL)),
1120 'L',
1121 ds.period_counter
1122 FROM
1123 fa_books bk,
1124 fa_deprn_summary ds,
1125 fa_additions ad,
1126 fa_mc_conversion_rates cr
1127 WHERE
1128 bk.date_ineffective is NULL AND
1129 bk.book_type_code = p_book_type_code AND
1130 nvl(bk.period_counter_fully_retired, p_end_pc +1) >=
1131 p_start_pc AND
1132 bk.asset_id = ad.asset_id AND
1133 cr.asset_id(+) = bk.asset_id AND
1134 cr.set_of_books_id(+) = p_rsob_id AND
1135 cr.book_type_code(+) = p_book_type_code AND
1136 cr.status is NULL AND
1137 ds.asset_id = bk.asset_id AND
1138 ds.book_type_code = bk.book_type_code AND
1139 ds.period_counter = (
1140 SELECT max(ds2.period_counter)
1141 FROM fa_deprn_summary ds2
1142 WHERE ds2.asset_id = ds.asset_id AND
1143 ds2.book_type_code =
1144 ds.book_type_code) AND
1145 rownum+0 <= G_Max_Commit_Size;
1146
1147 X_total_assets := X_total_assets + SQL%ROWCOUNT;
1148
1149 IF SQL%NOTFOUND THEN
1150 EXIT;
1151 END IF;
1152 FND_CONCURRENT.AF_COMMIT;
1153
1154 l_lock_status := lock_book(
1155 p_book_type_code,
1156 p_rsob_id);
1157 IF (NOT l_lock_status) THEN
1158 RAISE lock_error;
1159 END IF;
1160
1161 END LOOP;
1162
1163 -- update history table with number of assets selected
1164 UPDATE fa_mc_conversion_history
1165 SET total_assets = X_total_assets
1166 WHERE book_type_code = p_book_type_code AND
1167 set_of_books_id = p_rsob_id;
1168 FND_CONCURRENT.AF_COMMIT;
1169
1170 EXCEPTION
1171 WHEN lock_error THEN
1172 FND_CONCURRENT.AF_ROLLBACK ;
1173 set_conversion_status(
1174 p_book_type_code,
1175 p_rsob_id,
1176 p_start_pc,
1177 p_end_pc,
1178 NULL,
1179 'SE');
1180 fa_srvr_msg.add_sql_error (
1181 calling_fn => 'fa_mc_upg1_pkg.get_candidate_assets');
1182 app_exception.raise_exception;
1183
1184 WHEN OTHERS THEN
1185 FND_CONCURRENT.AF_ROLLBACK ;
1186 set_conversion_status(
1187 p_book_type_code,
1188 p_rsob_id,
1189 p_start_pc,
1190 p_end_pc,
1191 NULL,
1192 'SE');
1193 fa_srvr_msg.add_sql_error (
1194 calling_fn => 'fa_mc_upg1_pkg.get_candidate_assets');
1195 app_exception.raise_exception;
1196
1197 END get_candidate_assets;
1198
1199
1200 PROCEDURE get_currency_precision(
1201 p_to_currency IN VARCHAR2,
1202 X_precision OUT NOCOPY NUMBER,
1203 X_mau OUT NOCOPY NUMBER) IS
1204 /* ************************************************************************
1205 This procedure gets the minimum accountable unit and precision for the
1206 reporting currency which will be used in rounding currency amounts
1207 in the conversion
1208 ************************************************************************ */
1209
1210 CURSOR precision IS
1211 SELECT
1212 fc.precision, fc.minimum_accountable_unit
1213 FROM
1214 fnd_currencies fc
1215 WHERE
1216 fc.currency_code = p_to_currency;
1217
1218 precision_error exception;
1219 BEGIN
1220 OPEN precision;
1221 FETCH precision into X_precision,
1222 X_mau;
1223
1224 IF (precision%NOTFOUND) THEN
1225 RAISE precision_error;
1226 END IF;
1227
1228 CLOSE precision;
1229
1230 EXCEPTION
1231 WHEN OTHERS THEN
1232 fa_srvr_msg.add_sql_error (
1233 calling_fn => 'fa_mc_upg1_pkg.get_currency_precision');
1234 app_exception.raise_exception;
1235
1236 END get_currency_precision;
1237
1238
1239 PROCEDURE get_rate_info(
1240 p_from_currency IN VARCHAR2,
1241 p_to_currency IN VARCHAR2,
1242 p_conv_date IN DATE,
1243 p_conv_type IN VARCHAR2,
1244 X_denominator_rate OUT NOCOPY NUMBER,
1245 X_numerator_rate OUT NOCOPY NUMBER,
1246 X_rate OUT NOCOPY NUMBER,
1247 X_relation OUT NOCOPY VARCHAR2,
1248 X_fixed_rate OUT NOCOPY VARCHAR2) IS
1249 /* ************************************************************************
1250 This procedure will obtain the triangulation information between the
1251 currency of the Primary Book and the Reporting Book based on the
1252 conversion date and type as defined in GL. The info is obtained by
1253 by calling the GL_CURRENCY_API to get the relation between the currencies
1254 and the exchange rate to use based on the conversion date. In cases where
1255 user does not provide a rate for an asset the rate obtained here will
1256 be used as default. In the case of conversion within EMU, the rate
1257 returned for denominator_rate and numerator_rate will be used.
1258 ************************************************************************ */
1259 l_fixed_rate boolean;
1260
1261 BEGIN
1262 gl_currency_api.get_relation(
1263 p_from_currency,
1264 p_to_currency,
1265 p_conv_date,
1266 l_fixed_rate,
1267 X_relation);
1268 IF (l_fixed_rate) THEN
1269 X_fixed_rate := 'Y';
1270 ELSE X_fixed_rate := 'N';
1271 END IF;
1272
1273 -- call gl api to get the exchange rate to use for assets
1274 -- with conversion basis of R but no rate specified in the
1275 -- exchange_rate column of fa_mc_conversion_rates. This is
1276 -- default exchange rate as of the init conversion date specified
1277 -- in gl for this reporting book. For EMU conversion, we will use
1278 -- X_denominator_rate and X_numerator_rate for triangulation
1279 gl_currency_api.get_triangulation_rate(
1280 p_from_currency,
1281 p_to_currency,
1282 p_conv_date,
1283 p_conv_type,
1284 X_denominator_rate,
1285 X_numerator_rate,
1286 X_rate);
1287 -- debug messages
1288 if (g_print_debug) then
1289 fa_debug_pkg.add('get_rate_info','fixed relation',
1290 l_fixed_rate);
1291 fa_debug_pkg.add('get_rate_info','relation',
1292 X_relation);
1293 fa_debug_pkg.add('get_rate_info','denominator_rate',
1294 X_denominator_rate);
1295 fa_debug_pkg.add('get_rate_info','numerator_rate',
1296 X_numerator_rate);
1297 fa_debug_pkg.add('get_rate_info','exchange rate', X_rate);
1298 end if;
1299
1300 EXCEPTION
1301 WHEN OTHERS THEN
1302 fa_srvr_msg.add_message (
1303 calling_fn => 'fa_mc_upg1_pkg.get_rate_info',
1304 name => 'FA_MRC_CONV_RATE_ERR',
1305 token1 => 'REPORTING_BOOK',
1306 value1 => G_rbook_name);
1307 fa_srvr_msg.add_sql_error (
1308 calling_fn => 'fa_mc_upg1_pkg.get_rate_info');
1309 app_exception.raise_exception;
1310
1311 END get_rate_info;
1312
1313
1314 PROCEDURE check_preview_status(
1315 p_book_type_code IN VARCHAR2,
1316 p_rsob_id IN NUMBER,
1317 p_end_pc IN NUMBER) IS
1318 /* ************************************************************************
1319 This procudure will check to see if phase1 has been run for a given
1320 Primary Book - Reporting Book combination in order to prevent running
1321 conversion before phase 1. An exception is raised in this case. It also
1322 checks to make sure that selection is not out of date(this won't happen
1323 as we prevent transactions including depreciation from being run in the
1324 Primary Book).
1325 ************************************************************************ */
1326
1327 invalid_select exception;
1328 no_select exception;
1329 in_process exception;
1330 l_period_counter number;
1331 l_count number;
1332 l_total_assets number;
1333 l_status VARCHAR2(1);
1334
1335 CURSOR check_preview IS
1336 SELECT period_counter_selected,
1337 total_assets,
1338 conversion_status
1339 FROM fa_mc_conversion_history
1340 WHERE set_of_books_id = p_rsob_id AND
1341 book_type_code = p_book_type_code AND
1342 conversion_status in ('S', 'E', 'R');
1343
1344 CURSOR check_assets IS
1345 SELECT count(*)
1346 FROM fa_mc_conversion_rates
1347 WHERE set_of_books_id = p_rsob_id AND
1348 book_type_code = p_book_type_code;
1349
1350 BEGIN
1351 OPEN check_preview;
1352 FETCH check_preview into l_period_counter, l_total_assets, l_status;
1353 IF (check_preview%NOTFOUND) THEN
1354 RAISE no_select;
1355 ELSIF (l_period_counter <> p_end_pc) THEN
1356 RAISE invalid_select;
1357 ELSIF (l_status = 'R') THEN
1358 RAISE in_process;
1359 ELSE
1360 OPEN check_assets;
1361 FETCH check_assets into l_count;
1362 CLOSE check_assets;
1363 IF (l_count <> l_total_assets) THEN
1364 raise invalid_select;
1365 END IF;
1366 END IF;
1367 CLOSE check_preview;
1368
1369 EXCEPTION
1370 WHEN invalid_select THEN
1371 fa_srvr_msg.add_message (
1372 calling_fn => 'fa_mc_upg1_pkg.check_preview_status',
1373 name => 'FA_MRC_INVALID_SELECT',
1374 token1 => 'BOOK',
1375 value1 => p_book_type_code,
1376 token2 => 'REPORTING_BOOK',
1377 value2 => G_rbook_name);
1378 app_exception.raise_exception;
1379
1380 WHEN in_process THEN
1381 fa_srvr_msg.add_message (
1382 calling_fn => 'fa_mc_upg1_pkg.check_preview_status',
1383 name => 'FA_MRC_CONV_RUNNING',
1384 token1 => 'BOOK',
1385 value1 => p_book_type_code,
1386 token2 => 'REPORTING_BOOK',
1387 value2 => G_rbook_name);
1388 app_exception.raise_exception;
1389
1390 WHEN no_select THEN
1391 fa_srvr_msg.add_message (
1392 calling_fn => 'fa_mc_upg1_pkg.check_preview_status',
1393 name => 'FA_MRC_NO_SELECT',
1394 token1 => 'BOOK',
1395 value1 => p_book_type_code,
1396 token2 => 'REPORTING_BOOK',
1397 value2 => G_rbook_name);
1398 app_exception.raise_exception;
1399
1400 WHEN OTHERS THEN
1401 fa_srvr_msg.add_sql_error (
1402 calling_fn => 'fa_mc_upg1_pkg.check_preview_status');
1403 app_exception.raise_exception;
1404
1405 END check_preview_status;
1406
1407
1408 PROCEDURE convert_reporting_book(
1409 p_book_type_code IN VARCHAR2,
1410 p_reporting_book IN VARCHAR2,
1411 p_fixed_rate IN VARCHAR2) IS
1412 /* ************************************************************************
1413 This procedure is the main routine to convert all the assets in a
1414 Primary Book to the Reporting Book. It accepts the Primary and Reporting
1415 Book as parameters and also whether it is a fixed rate conversion or
1416 not. This procedure calls all the validation routines and sets the
1417 conversion history status before calling convert assets to actually
1418 convert the records.
1419 ************************************************************************ */
1420
1421 l_psob_id number;
1422 l_rsob_id number;
1423 l_start_pc number;
1424 l_end_pc number;
1425 l_mau number;
1426 l_precision number;
1427 l_from_currency varchar2(10);
1428 l_to_currency varchar2(10);
1429 l_denominator_rate number;
1430 l_numerator_rate number;
1431 l_rate number;
1432 l_relation varchar2(15);
1433 l_conv_date date;
1434 l_conv_type varchar2(30);
1435 l_fixed_conversion varchar2(1);
1436 l_fixed_rate varchar2(1);
1437 l_accounting_date date;
1438
1439 BEGIN
1440
1441 begin
1442
1443 -- Perform validation of set up
1444 validate_setup( p_book_type_code,
1445 p_reporting_book,
1446 l_from_currency,
1447 l_to_currency,
1448 l_rsob_id,
1449 l_psob_id);
1450
1451 exception
1452
1453 when others then
1454 fa_srvr_msg.add_sql_error (
1455 calling_fn => 'fa_mc_upg1_pkg.convert_reporting_book=>validate_setup');
1456 app_exception.raise_exception;
1457
1458 end;
1459
1460 begin
1461
1462 -- get info from gl book assignments and periods to convert
1463 get_conversion_info(
1464 p_book_type_code,
1465 l_psob_id,
1466 l_rsob_id,
1467 l_start_pc,
1468 l_end_pc,
1469 l_conv_date,
1470 l_conv_type,
1471 l_accounting_date);
1472
1473 exception
1474
1475 when others then
1476 fa_srvr_msg.add_sql_error (
1477 calling_fn => 'fa_mc_upg1_pkg.convert_reporting_book=>get_conversion_info');
1478 app_exception.raise_exception;
1479
1480 end;
1481
1482 begin
1483
1484 -- get exchange rate and triangulation rate info
1485 get_rate_info(
1486 l_from_currency,
1487 l_to_currency,
1488 l_conv_date,
1489 l_conv_type,
1490 l_denominator_rate,
1491 l_numerator_rate,
1492 l_rate,
1493 l_relation,
1494 l_fixed_rate);
1495
1496 exception
1497
1498 when others then
1499 fa_srvr_msg.add_sql_error (
1500 calling_fn => 'fa_mc_upg1_pkg.convert_reporting_book=>get_rate_info');
1501 app_exception.raise_exception;
1502
1503 end;
1504
1505 begin
1506
1507 get_currency_precision(
1508 l_to_currency,
1509 l_precision,
1510 l_mau);
1511
1512 exception
1513
1514 when others then
1515 fa_srvr_msg.add_sql_error (
1516 calling_fn => 'fa_mc_upg1_pkg.convert_reporting_book=>get_currency_precision');
1517 app_exception.raise_exception;
1518
1519 end;
1520
1521 begin
1522
1523 -- check if asset selection has been run
1524 check_preview_status(
1525 p_book_type_code,
1526 l_rsob_id,
1527 l_end_pc);
1528
1529 exception
1530
1531 when others then
1532 fa_srvr_msg.add_sql_error (
1533 calling_fn => 'fa_mc_upg1_pkg.convert_reporting_book=>check_preview_status');
1534 app_exception.raise_exception;
1535
1536 end;
1537
1538 begin
1539
1540 -- validate conversion basis and rate info
1541 validate_rate (
1542 p_book_type_code,
1543 l_rsob_id,
1544 p_fixed_rate,
1545 l_fixed_conversion);
1546
1547 exception
1548
1549 when others then
1550 fa_srvr_msg.add_sql_error (
1551 calling_fn => 'fa_mc_upg1_pkg.convert_reporting_book=>validate_rate');
1552 app_exception.raise_exception;
1553
1554 end;
1555
1556 begin
1557
1558 -- set conversion status to running
1559 set_conversion_status(
1560 p_book_type_code,
1561 l_rsob_id,
1562 l_start_pc,
1563 l_end_pc,
1564 l_fixed_conversion,
1565 'R');
1566
1567 exception
1568
1569 when others then
1570 fa_srvr_msg.add_sql_error (
1571 calling_fn => 'fa_mc_upg1_pkg.convert_reporting_book=>set_conversion_status');
1572 app_exception.raise_exception;
1573
1574 end;
1575
1576 begin
1577
1578 -- convert all assets
1579 convert_assets( l_rsob_id,
1580 p_book_type_code,
1581 l_start_pc,
1582 l_end_pc,
1583 l_numerator_rate,
1584 l_denominator_rate,
1585 l_mau,
1586 l_precision,
1587 l_fixed_conversion);
1588
1589 exception
1590
1591 when others then
1592 fa_srvr_msg.add_sql_error (
1593 calling_fn => 'fa_mc_upg1_pkg.convert_reporting_book=>convert_assets');
1594 app_exception.raise_exception;
1595
1596 end;
1597
1598 EXCEPTION
1599 WHEN OTHERS THEN
1600 fa_srvr_msg.add_sql_error (
1601 calling_fn => 'fa_mc_upg1_pkg.convert_reporting_book');
1602 app_exception.raise_exception;
1603 END convert_reporting_book;
1604
1605
1606 PROCEDURE convert_assets(
1607 p_rsob_id IN NUMBER,
1608 p_book_type_code IN VARCHAR2,
1609 p_start_pc IN NUMBER,
1610 p_end_pc IN NUMBER,
1611 p_numerator_rate IN NUMBER,
1612 p_denominator_rate IN NUMBER,
1613 p_mau IN NUMBER,
1614 p_precision IN NUMBER,
1615 p_fixed_conversion IN VARCHAR2) IS
1616 /* ************************************************************************
1617 This procedure will have the main processing LOOP and calls
1618 to procedures for converting the required tables. We are
1619 converting all the tables for 1000 assets and COMMITTING.
1620 This is to try and prevent running out of rollback segments.
1621 Max commit size of 1000 is stored in the constant
1622 G_Max_Commit_Size.
1623
1624 Select assets which have DEPRN rows for the fiscal year being
1625 being converted first denoted by F and THEN select the other
1626 assets which have their last DEPRN row in prior fiscal year
1627 denoted by status of L
1628 ************************************************************************ */
1629
1630 l_assets_to_convert NUMBER; -- assets to convert in this run
1631 l_assets_processed NUMBER; -- assets processed in this run
1632
1633 l_commit_size NUMBER; -- assets to be committed in 1
1634 -- iteration of loop
1635
1636 l_deprn_assets NUMBER := 0; -- assets with DEPRN rows in fy
1637 l_no_deprn_assets NUMBER := 0; -- assets with DEPRN row in pfy
1638 l_convert_order VARCHAR2(1);
1639 l_status VARCHAR2(2);
1640 l_count number;
1641 l_converted_assets number := 0; -- assets already converted
1642 l_total_assets number := 0; -- total candidate assets
1643 index_flag BOOLEAN := FALSE;
1644
1645 l_mesg_str varchar2(512);
1646
1647 CURSOR total_assets IS
1648 SELECT
1649 count(*),
1650 status
1651 FROM
1652 fa_mc_conversion_rates cr
1653 WHERE
1654 cr.set_of_books_id = p_rsob_id AND
1655 cr.book_type_code = p_book_type_code
1656 GROUP BY status;
1657
1658 BEGIN
1659 OPEN total_assets;
1660 LOOP
1661 l_count := 0;
1662 FETCH total_assets into l_count, l_status;
1663 IF (total_assets%NOTFOUND) THEN
1664 exit;
1665 END IF;
1666 IF (l_status = 'F') THEN
1667 l_deprn_assets := l_deprn_assets + l_count;
1668 ELSIF (l_status = 'L') THEN
1669 l_no_deprn_assets := l_no_deprn_assets + l_count;
1670 ELSIF (l_status IN ('CF', 'CL')) THEN
1671 l_converted_assets := l_converted_assets + l_count;
1672 END IF;
1673 END LOOP;
1674
1675 CLOSE total_assets;
1676
1677 l_assets_to_convert := l_deprn_assets + l_no_deprn_assets;
1678 l_total_assets := l_assets_to_convert + l_converted_assets;
1679 l_assets_processed := 0;
1680 l_commit_size := 0;
1681
1682 if (g_print_debug) then
1683 fa_debug_pkg.add('convert_assets',
1684 'Number of assets with DEPRN in current fy',
1685 l_deprn_assets);
1686 fa_debug_pkg.add('convert_assets',
1687 'Number of assets with no DEPRN in current fy',
1688 l_no_deprn_assets);
1689 fa_debug_pkg.add('convert_assets',
1690 'Number of assets already converted',
1691 l_converted_assets);
1692 fa_debug_pkg.add('convert_assets',
1693 'Number of assets to convert in this run',
1694 l_assets_to_convert);
1695 fa_debug_pkg.add('convert_assets',
1696 'Total number of assets to convert',
1697 l_total_assets);
1698 end if;
1699
1700 -- select 1000 assets at a time and update the status to S to
1701 -- indicate selected for conversion
1702
1703 WHILE (l_assets_processed <> l_assets_to_convert) LOOP
1704 /*
1705 IF (l_assets_to_convert > 0) THEN
1706 create_drop_indexes('D');
1707 index_flag := TRUE;
1708 END IF;
1709 */
1710 IF (l_assets_processed < l_deprn_assets) THEN
1711
1712 UPDATE fa_mc_conversion_rates
1713 SET STATUS = 'S'
1714 WHERE set_of_books_id = p_rsob_id AND
1715 book_type_code = p_book_type_code AND
1716 STATUS = 'F' AND
1717 rownum <= G_Max_Commit_Size;
1718
1719 l_commit_size := SQL%ROWCOUNT;
1720 l_convert_order := 'F';
1721 ELSE
1722 UPDATE fa_mc_conversion_rates
1723 SET STATUS = 'S'
1724 WHERE set_of_books_id = p_rsob_id AND
1725 book_type_code = p_book_type_code AND
1726 STATUS = 'L' AND
1727 rownum <= G_Max_Commit_Size;
1728
1729 l_commit_size := SQL%ROWCOUNT;
1730 l_convert_order := 'L';
1731 END IF;
1732
1733 if (g_print_debug) then
1734 fa_debug_pkg.add('convert_assets',
1735 'Number of assets selected in this iteration',
1736 l_commit_size);
1737 fa_debug_pkg.add('convert_assets',
1738 'Converting assets with status',
1739 l_convert_order);
1740 end if;
1741
1742 fa_mc_upg2_pkg.convert_books(
1743 p_rsob_id,
1744 p_book_type_code,
1745 p_numerator_rate,
1746 p_denominator_rate,
1747 p_mau,
1748 p_precision);
1749
1750 fa_mc_upg2_pkg.insert_bks_rates(
1751 p_rsob_id,
1752 p_book_type_code,
1753 p_numerator_rate,
1754 p_denominator_rate,
1755 p_precision);
1756
1757 fa_mc_upg2_pkg.convert_invoices(
1758 p_rsob_id,
1759 p_book_type_code,
1760 p_numerator_rate,
1761 p_denominator_rate,
1762 p_mau,
1763 p_precision);
1764
1765 fa_mc_upg2_pkg.convert_adjustments(
1766 p_rsob_id,
1767 p_book_type_code,
1768 p_start_pc,
1769 p_end_pc,
1770 p_numerator_rate,
1771 p_denominator_rate,
1772 p_mau,
1773 p_precision);
1774
1775 fa_mc_upg2_pkg.convert_retirements(
1776 p_rsob_id,
1777 p_book_type_code,
1778 p_start_pc,
1779 p_end_pc,
1780 p_numerator_rate,
1781 p_denominator_rate,
1782 p_mau,
1783 p_precision);
1784
1785 fa_mc_upg2_pkg.convert_deprn_summary(
1786 p_book_type_code,
1787 p_rsob_id,
1788 p_start_pc,
1789 p_end_pc,
1790 l_convert_order,
1791 p_mau,
1792 p_precision);
1793
1794 fa_mc_upg2_pkg.convert_deprn_detail(
1795 p_rsob_id,
1796 p_book_type_code,
1797 p_mau,
1798 p_precision);
1799
1800 fa_mc_upg2_pkg.convert_deferred_deprn(
1801 p_rsob_id,
1802 p_book_type_code,
1803 p_start_pc,
1804 p_end_pc,
1805 p_numerator_rate,
1806 p_denominator_rate,
1807 p_mau,
1808 p_precision);
1809
1810
1811 -- all tables have been converted successfully for the assets selected
1812 -- update the status to converted and commit and increment
1813 -- assets processed with the numbers of assets converted
1814
1815 UPDATE fa_mc_conversion_rates
1816 SET STATUS = DECODE(l_convert_order,
1817 'F', 'CF',
1818 'L', 'CL')
1819 WHERE
1820 set_of_books_id = p_rsob_id AND
1821 book_type_code = p_book_type_code AND
1822 STATUS = 'S';
1823 FND_CONCURRENT.AF_COMMIT;
1824 l_assets_processed := l_assets_processed + l_commit_size;
1825
1826 END LOOP; -- while LOOP
1827
1828 fa_srvr_msg.add_message(
1829 calling_fn => 'fa_mc_upg1_pkg.convert_assets',
1830 name => 'FA_SHARED_NUMBER_PROCESSED',
1831 token1 => 'NUMBER',
1832 value1 => to_char(l_assets_processed));
1833
1834 if (g_print_debug) then
1835 fa_debug_pkg.add('convert_assets',
1836 'number of assets processed in this run',
1837 l_assets_processed);
1838 end if;
1839
1840 -- After all assets are converted set conversion status to C
1841 -- and mrc_converted_flag to Y by calling set_conversion_status
1842 -- convert deprn periods after all assets are converted.
1843
1844 IF ((l_converted_assets + l_assets_processed) = l_total_assets) THEN
1845 fa_mc_upg2_pkg.convert_deprn_periods (
1846 p_rsob_id,
1847 p_book_type_code,
1848 p_start_pc,
1849 p_end_pc);
1850
1851 set_conversion_status(
1852 p_book_type_code,
1853 p_rsob_id,
1854 p_start_pc,
1855 p_end_pc,
1856 p_fixed_conversion,
1857 'C');
1858
1859 if (g_print_debug) then
1860 fa_debug_pkg.add('convert_assets',
1861 'All assets converted for reporting book',
1862 l_total_assets);
1863 end if;
1864
1865 END IF;
1866 /*
1867 IF (index_flag) THEN
1868 create_drop_indexes('C');
1869 END IF;
1870 */
1871
1872 EXCEPTION
1873 WHEN OTHERS THEN
1874 -- rollback everything since last commit
1875 FND_CONCURRENT.AF_ROLLBACK ;
1876 /*
1877 IF (index_flag) THEN
1878 create_drop_indexes('C');
1879 END IF;
1880 */
1881
1882 -- set conversion_status to Error
1883 set_conversion_status(
1884 p_book_type_code,
1885 p_rsob_id,
1886 p_start_pc,
1887 p_end_pc,
1888 p_fixed_conversion,
1889 'RE');
1890 fa_srvr_msg.add_message(
1891 calling_fn => 'fa_mc_upg1_pkg.convert_assets',
1892 name => 'FA_SHARED_NUMBER_PROCESSED',
1893 token1 => 'NUMBER',
1894 value1 => to_char(l_assets_processed));
1895
1896 fa_srvr_msg.add_sql_error (
1897 calling_fn => 'fa_mc_upg1_pkg.convert_assets');
1898 app_exception.raise_exception;
1899
1900 END convert_assets;
1901
1902 PROCEDURE Write_ErrMsg_Log(
1903 msg_count IN NUMBER) IS
1904 /* ************************************************************************
1905 -- For test purpose, you may set h_coded to TRUE. Then messages will be
1906 -- printed out in encoded format instead of translated format.
1907 -- This is useful
1908 -- if you want to test the message, but if you have not registered your
1909 -- message yet in the message dictionary.
1910 -- Normally h_encoded should be set to FALSE.
1911 ************************************************************************ */
1912
1913 h_encoded varchar2(1) := fnd_api.G_FALSE;
1914 --h_encoded varchar(1):= fnd_api.G_TRUE;
1915
1916 BEGIN
1917
1918
1919 if (msg_count <= 0) then
1920 NULL;
1921 -- Commenting out the next portion, since we do not want to print message
1922 -- which we are not sure whether it is in encoded or translated format.
1923 --elsif (msg_count = 1 and msg_data IS NOT NULL) then
1924 -- fa_rx_conc_mesg_pkg.log(msg_data);
1925 else
1926 fa_rx_conc_mesg_pkg.log(
1927 fnd_msg_pub.get(fnd_msg_pub.G_FIRST, h_encoded));
1928 for i in 1..(msg_count-1) loop
1929 fa_rx_conc_mesg_pkg.log(
1930 fnd_msg_pub.get(fnd_msg_pub.G_NEXT, h_encoded));
1931 end loop;
1932 end if;
1933
1934 /*
1935 -- write using dbms output for testing only
1936 if (msg_count <= 0) then
1937 NULL;
1938 else
1939 dbms_output.put_line(fnd_msg_pub.get(fnd_msg_pub.G_FIRST, h_encoded));
1940 for i in 1..(msg_count-1) loop
1941 dbms_output.put_line(fnd_msg_pub.get(fnd_msg_pub.G_NEXT, h_encoded));
1942 end loop;
1943 end if;
1944 */
1945
1946 EXCEPTION
1947 WHEN OTHERS THEN
1948 fa_srvr_msg.add_sql_error (
1949 calling_fn => 'fa_mc_upg1_pkg.Write_Msg_Log');
1950 END Write_ErrMsg_Log;
1951
1952 PROCEDURE Write_DebugMsg_Log(
1953 p_msg_count IN NUMBER) IS
1954 /* ************************************************************************
1955 This procedure will write all the debug messages on the debug stack to
1956 the log file.
1957 ************************************************************************ */
1958
1959
1960 mesg_more boolean := TRUE;
1961 mesg1 varchar2(280);
1962 mesg2 varchar2(280);
1963 mesg3 varchar2(280);
1964 mesg4 varchar2(280);
1965 mesg5 varchar2(280);
1966 mesg6 varchar2(280);
1967 mesg7 varchar2(280);
1968 mesg8 varchar2(280);
1969 mesg9 varchar2(280);
1970 mesg10 varchar2(280);
1971
1972 BEGIN
1973 fa_rx_conc_mesg_pkg.log('');
1974 fa_rx_conc_mesg_pkg.log('*************************');
1975 fa_rx_conc_mesg_pkg.log('Dumping Debug Messages:');
1976 fa_rx_conc_mesg_pkg.log('*************************');
1977
1978 /*
1979 -- dbms output for testing only
1980 dbms_output.put_line('');
1981 dbms_output.put_line('*************************');
1982 dbms_output.put_line('Dumping Debug Messages:');
1983 dbms_output.put_line('*************************');
1984 */
1985
1986 while mesg_more loop
1987 FA_DEBUG_PKG.Get_Debug_Messages(
1988 mesg1,mesg2,mesg3,mesg4,mesg5,mesg6,mesg7,
1989 mesg8,mesg9,mesg10,
1990 mesg_more);
1991
1992 fa_rx_conc_mesg_pkg.log(mesg1);
1993 fa_rx_conc_mesg_pkg.log(mesg2);
1994 fa_rx_conc_mesg_pkg.log(mesg3);
1995 fa_rx_conc_mesg_pkg.log(mesg4);
1996 fa_rx_conc_mesg_pkg.log(mesg5);
1997 fa_rx_conc_mesg_pkg.log(mesg6);
1998 fa_rx_conc_mesg_pkg.log(mesg7);
1999 fa_rx_conc_mesg_pkg.log(mesg8);
2000 fa_rx_conc_mesg_pkg.log(mesg9);
2001 fa_rx_conc_mesg_pkg.log(mesg10);
2002 /*
2003 -- use dbms output for testing only
2004 dbms_output.put_line (mesg1);
2005 dbms_output.put_line (mesg2);
2006 dbms_output.put_line (mesg3);
2007 dbms_output.put_line (mesg4);
2008 dbms_output.put_line (mesg5);
2009 dbms_output.put_line (mesg6);
2010 dbms_output.put_line (mesg7);
2011 dbms_output.put_line (mesg8);
2012 dbms_output.put_line (mesg9);
2013 dbms_output.put_line (mesg10);
2014 */
2015 end loop;
2016 EXCEPTION
2017 WHEN OTHERS THEN
2018 fa_srvr_msg.add_sql_error (
2019 calling_fn => 'fa_mc_upg1_pkg.Write_DebugMsg_Log');
2020 RAISE;
2021 END Write_DebugMsg_Log;
2022
2023 END FA_MC_UPG1_PKG;