[Home] [Help]
PACKAGE BODY: APPS.JL_ZZ_FA_FUNCTIONS_PKG
Source
1 PACKAGE BODY jl_zz_fa_functions_pkg AS
2 /* $Header: jlzzfafb.pls 120.11 2006/09/20 17:13:59 abuissa ship $ */
3
4 /* ======================================================================*
5 | FND Logging infrastructure |
6 * ======================================================================*/
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JL_ZZ_FA_FUNCTIONS_PKG';
8 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
10 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
11 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
12 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
13 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
14 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
15 G_MODULE_NAME CONSTANT VARCHAR2(80) := 'JL.PLSQL.JL_ZZ_FA_FUNCTIONS_PKG.';
16
17
18 TYPE temp_plsql_table is table of JL_AR_FA_EXHIBIT_REPORT%ROWTYPE index by binary_integer;
19 report_table temp_plsql_table;
20
21 /*+=========================================================================+
22 | PUBLIC FUNCTION |
23 | middle_month |
24 | p_add_month_number Month in which the Addition took place.|
25 | p_ret_month_number Month in which the Retirement took |
26 | place. |
27 | p_include_dpis Include DPIS month in the periods of |
28 | use calculation. |
29 | p_include_ret Include retirement month in the periods|
30 | of use calculation. |
31 | |
32 | NOTES |
33 | Middle Month Function: Assets added or sold in the current FY are |
34 | adjusted until the half of the period of use. The half of the period |
35 | of use is obtained from the Middle Month Tables. |
36 | |
37 | 08-Nov-00 S. Vaze This procedure is now written due to changes|
38 | in the requirement # 1561112. |
39 | The p_add_month_number 0 in addition |
40 | signifies Addition in the previous year. |
41 | The p_ret_month_number 13 in retirement |
42 | signifies Asset is not retired yet. |
43 +=========================================================================+*/
44 FUNCTION middle_month (p_add_month_number IN NUMBER
45 , p_ret_month_number IN NUMBER
46 , p_include_dpis IN VARCHAR2
47 , p_include_ret IN VARCHAR2) RETURN NUMBER IS
48
49 l_middle_month NUMBER;
50 l_mou NUMBER;
51 l_add_month_number NUMBER;
52 l_ret_month_number NUMBER;
53
54 BEGIN
55
56 IF (p_ret_month_number < p_add_month_number) THEN
57 l_middle_month := -1;
58 RETURN (l_middle_month);
59 END IF;
60 IF (p_ret_month_number = p_add_month_number) THEN
61 l_middle_month := p_add_month_number;
62 RETURN (l_middle_month);
63 END IF;
64
65 l_mou := periods_of_use (p_add_month_number
66 , p_ret_month_number
67 , p_include_dpis
68 , p_include_ret);
69
70 l_add_month_number := p_add_month_number;
71 l_ret_month_number := p_ret_month_number;
72 --
73 -- OPTION : Include DPIS month and include retirement month
74 -- In this option all the previous year additions are treated as if they
75 -- are done in the first month.
76 --
77 IF p_include_dpis = 'Y' and p_include_ret = 'Y'
78 THEN
79 IF p_add_month_number = 0
80 THEN
81 l_add_month_number := 1;
82 END IF;
83
84 IF p_add_month_number = 12 and p_ret_month_number = 13
85 THEN
86 l_middle_month := l_add_month_number;
87 RETURN (l_middle_month);
88 END IF;
89
90 l_middle_month := trunc(l_mou/2) + l_add_month_number - 1;
91 END IF;
92
93 --
94 -- OPTION : Include DPIS month and exclude retirement month
95 -- In this option all the previous year additions are treated as if they
96 -- are done in the first month.
97 --
98 IF p_include_dpis = 'Y' and p_include_ret = 'N'
99 THEN
100 IF l_mou = 1
101 THEN
102 l_middle_month := trunc(l_mou/2) + l_add_month_number;
103 RETURN (l_middle_month);
104 END IF;
105
106 IF p_add_month_number = 0
107 THEN
108 l_add_month_number := 1;
109 END IF;
110
111 l_middle_month := trunc(l_mou/2) + l_add_month_number - 1;
112 END IF;
113
114 --
115 -- OPTION : Exclude DPIS month and include retirement month
116 --
117 IF p_include_dpis = 'N' and p_include_ret = 'Y'
118 THEN
119 l_middle_month := trunc(l_mou/2) + l_add_month_number;
120 END IF;
121
122 --
123 -- OPTION : Exclude DPIS month and exclude retirement month
124 --
125 IF p_include_dpis = 'N' and p_include_ret = 'N'
126 THEN
127 l_middle_month := trunc(l_mou/2) + l_add_month_number;
128 END IF;
129
130 RETURN (l_middle_month);
131
132 END middle_month;
133
134 /*+=========================================================================+
135 | PUBLIC FUNCTION |
136 | periods_of_use |
137 | p_add_month_number Month in which the Addition took place.|
138 | p_ret_month_number Month in which the Retirement took |
139 | place. |
140 | p_include_dpis Include DPIS month in the periods of |
141 | use calculation. |
142 | p_include_ret Include retirement month in the periods|
143 | of use calculation. |
144 | |
145 | NOTES |
146 | Periods of Use Function. It gets the months of use of the asset |
147 | for the different criterias such as consider Date places in |
148 | service or prorrate date, and including the month of the retirement |
149 | or not. |
150 | 04-Dec-00 C. Leyva New function to get the Months of Use |
151 | of the asset. |
152 +=========================================================================+*/
153 FUNCTION periods_of_use (p_add_month_number IN NUMBER
154 , p_ret_month_number IN NUMBER
155 , p_include_dpis IN VARCHAR2
156 , p_include_ret IN VARCHAR2) RETURN NUMBER IS
157
158 l_mou NUMBER := null;
159 l_add_month_number NUMBER;
160 l_ret_month_number NUMBER;
161
162 BEGIN
163
164 l_add_month_number := p_add_month_number;
165 l_ret_month_number := p_ret_month_number;
166 --
167 -- OPTION : Include DPIS month and retirement month
168 -- In this option all the previous year additions are treated as if they
169 -- are done in the first month.
170 -- All the unretired assets are treated as if they are retired in the
171 -- last month.
172 --
173 IF p_include_dpis = 'Y' and p_include_ret = 'Y'
174 THEN
175 IF p_add_month_number = 0
176 THEN
177 l_add_month_number := 1;
178 END IF;
179
180 IF p_ret_month_number = 13
181 THEN
182 l_ret_month_number := 12;
183 END IF;
184
185 l_mou := l_ret_month_number - l_add_month_number + 1;
186 END IF;
187
188 --
189 -- OPTION : Include DPIS month and exclude retirement month
190 --
191 IF p_include_dpis = 'Y' and p_include_ret = 'N'
192 THEN
193 IF p_add_month_number = 0
194 THEN
195 l_add_month_number := 1;
196 END IF;
197
198 l_mou := l_ret_month_number - l_add_month_number;
199 END IF;
200
201 --
202 -- OPTION : Exclude DPIS month and include retirement month
203 --
204 IF p_include_dpis = 'N' and p_include_ret = 'Y'
205 THEN
206 IF p_ret_month_number = 13
207 THEN
208 l_ret_month_number := 12;
209 END IF;
210
211 l_mou := l_ret_month_number - l_add_month_number;
212 END IF;
213
214 --
215 -- OPTION : Exclude DPIS month and exclude retirement month
216 --
217 IF p_include_dpis = 'N' and p_include_ret = 'N'
218 THEN
219 l_mou := l_ret_month_number - l_add_month_number - 1;
220 END IF;
221
222 RETURN (l_mou);
223 END periods_of_use;
224
225
226 /*+=========================================================================+
227 | PUBLIC FUNCTION |
228 | asset_cost |
229 | p_book_type_code IN Depreciation Book |
230 | p_asset_id IN Asset |
231 | p_period_counter IN Period |
232 | p_asset_cost OUT Asset cost for this particular period and |
233 | depreciation book. |
234 | Returns |
235 | Number 0 Normal completion |
236 | 1 Abnormal completion |
237 | |
238 | NOTES |
239 | Given an asset, a depreciation book and a depreciation period, |
240 | returns the asset's cost at the end of the period for that |
241 | depreciation book. |
242 | |
243 | |
244 +=========================================================================+*/
245 FUNCTION asset_cost (p_book_type_code IN VARCHAR2
246 , p_asset_id IN NUMBER
247 , p_period_counter IN NUMBER
248 , p_asset_cost IN OUT NOCOPY NUMBER
249 , p_mrcsobtype IN VARCHAR2 DEFAULT 'P')
250 RETURN NUMBER IS
251
252 normal CONSTANT NUMBER := 0;
253 error CONSTANT NUMBER := 1;
254 l_transaction_header_id NUMBER;
255 l_period_counter NUMBER;
256
257 BEGIN
258
259 l_period_counter := p_period_counter;
260 BEGIN
261 ------------------------------------------------------------
262 -- The latest transaction performed on an asset in a --
263 -- particular period is the one that defines the cost at --
264 -- the end of that period. --
265 -- Bug 3101070: Due to mrc changes, this function is --
266 -- completely re-written. View jl_zz_fa_books_periods_v --
267 -- has been replaced with the source code of the view. --
268 ------------------------------------------------------------
269 SELECT max(th.transaction_header_id) ,
270 max(dp.period_counter)
271 INTO l_transaction_header_id,
272 l_period_counter
273 FROM fa_books fb ,
274 fa_calendar_periods cp ,
275 fa_deprn_periods dp ,
276 fa_transaction_headers th ,
277 fa_asset_history ah ,
278 fa_additions ad ,
279 fa_book_controls bc
280 WHERE ah.asset_id = ad.asset_id
281 AND fb.book_type_code = bc.book_type_code
282 AND fb.asset_id = ad.asset_id
283 AND fb.transaction_header_id_in = th.transaction_header_id
284 AND dp.book_type_code = bc.book_type_code
285 AND cp.calendar_type = bc.deprn_calendar
286 AND th.asset_id = ad.asset_id
287 AND th.book_type_code= dp.book_type_code
288 AND th.transaction_header_id >= ah.transaction_header_id_in
289 AND th.transaction_header_id < nvl(ah.transaction_header_id_out, th.transaction_header_id + 1)
290 AND th.transaction_date_entered between cp.start_date and cp.end_date
291 AND th.date_effective between dp.period_open_date and nvl(dp.period_close_date,th.date_effective)
292 AND bc.book_type_code = p_book_type_code
293 AND ad.asset_id = p_asset_id
294 AND dp.period_counter <= p_period_counter;
295 END;
296
297 IF p_mrcsobtype = 'R' THEN
298 BEGIN
299 SELECT fb.cost
300 INTO p_asset_cost
301 FROM fa_books_mrc_v fb ,
302 fa_calendar_periods cp ,
303 fa_deprn_periods_mrc_v dp ,
304 fa_transaction_headers th ,
305 fa_asset_history ah ,
306 fa_additions ad ,
307 fa_book_controls_mrc_v bc
308 WHERE ah.asset_id = ad.asset_id
309 AND fb.book_type_code = bc.book_type_code
310 AND fb.asset_id = ad.asset_id
311 AND fb.transaction_header_id_in = th.transaction_header_id
312 AND dp.book_type_code = bc.book_type_code
313 AND cp.calendar_type = bc.deprn_calendar
314 AND th.asset_id = ad.asset_id
315 AND th.book_type_code= dp.book_type_code
316 AND th.transaction_header_id >= ah.transaction_header_id_in
317 AND th.transaction_header_id < nvl(ah.transaction_header_id_out, th.transaction_header_id + 1)
318 AND th.transaction_date_entered between cp.start_date and cp.end_date
319 AND th.date_effective between dp.period_open_date and nvl(dp.period_close_date,th.date_effective)
320 AND bc.book_type_code = p_book_type_code
321 AND ad.asset_id = p_asset_id
322 AND dp.period_counter = l_period_counter
323 AND th.transaction_header_id = l_transaction_header_id;
324 END;
325 ELSE
326 BEGIN
327 SELECT fb.cost
328 INTO p_asset_cost
329 FROM fa_books fb ,
330 fa_calendar_periods cp ,
331 fa_deprn_periods dp ,
332 fa_transaction_headers th ,
333 fa_asset_history ah ,
334 fa_additions ad ,
335 fa_book_controls bc
336 WHERE ah.asset_id = ad.asset_id
337 AND fb.book_type_code = bc.book_type_code
338 AND fb.asset_id = ad.asset_id
339 AND fb.transaction_header_id_in = th.transaction_header_id
340 AND dp.book_type_code = bc.book_type_code
341 AND cp.calendar_type = bc.deprn_calendar
342 AND th.asset_id = ad.asset_id
343 AND th.book_type_code= dp.book_type_code
344 AND th.transaction_header_id >= ah.transaction_header_id_in
345 AND th.transaction_header_id < nvl(ah.transaction_header_id_out, th.transaction_header_id + 1)
346 AND th.transaction_date_entered between cp.start_date and cp.end_date
347 AND th.date_effective between dp.period_open_date and nvl(dp.period_close_date,th.date_effective)
348 AND bc.book_type_code = p_book_type_code
349 AND ad.asset_id = p_asset_id
350 AND dp.period_counter = l_period_counter
351 AND th.transaction_header_id = l_transaction_header_id;
352 END;
353 END IF;
354
355 RETURN (normal);
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 RETURN (error);
360
361 END asset_cost;
362
363 /*+=========================================================================+
364 | PUBLIC FUNCTION |
365 | asset_desc |
366 | p_asset_number IN Asset |
367 | |
368 | Returns |
369 | p_asset_desc Asset Description |
370 | |
371 | NOTES |
372 | Given an asset, returns the asset's description. |
373 | |
374 | |
375 +=========================================================================+*/
376 FUNCTION asset_desc (p_asset_number IN VARCHAR2)
377 RETURN VARCHAR2 IS
378
379 p_asset_desc fa_additions.description%TYPE;
380
381 BEGIN
382
383 BEGIN
384 ------------------------------------------------------------
385 -- The following sql will return the description of the --
386 -- given asset. --
387 ------------------------------------------------------------
388 SELECT faa.description
389 INTO p_asset_desc
390 FROM fa_additions faa
391 WHERE faa.asset_number = p_asset_number;
392 END;
393
394 RETURN (p_asset_desc);
395
396 EXCEPTION
397 WHEN OTHERS THEN
398 RETURN (null);
399
400 END asset_desc;
401
402 /*+=========================================================================+
403 | PRIVATE PROCEDURE |
404 | Procedure: |
405 | |
406 | Get the revaluation amount for a given asset and a period |
407 | |
408 +=========================================================================+*/
409 PROCEDURE get_adjust_amount (p_book_type_code IN VARCHAR2
410 , p_asset_id IN NUMBER
411 , p_period_counter IN NUMBER
412 , p_transaction_type IN VARCHAR2
413 , p_adjustment_type IN VARCHAR2
414 , p_adjustment IN OUT NOCOPY NUMBER
415 , p_mrcsobtype IN VARCHAR2) IS
416 l_source_type_code VARCHAR2(30);
417
418 BEGIN
419 IF p_transaction_type in ('FULL RETIREMENT', 'PARTIAL RETIREMENT', 'REINSTATEMENT') THEN
420 l_source_type_code := 'RETIREMENT';
421 ELSIF p_transaction_type = 'ADDITION' THEN
422 l_source_type_code := 'ADDITION';
423 ELSIF p_transaction_type = 'CIP ADDITION' THEN
424 l_source_type_code := 'CIP ADDITION';
425 ELSIF p_transaction_type = 'ADJUSTMENT' THEN
426 IF p_adjustment_type IN ('COST', 'CIP COST') THEN
427 l_source_type_code := 'ADJUSTMENT';
428 ELSIF p_adjustment_type IN ('EXPENSE') THEN
429 l_source_type_code := 'DEPRECIATION';
430 END IF;
431 ELSIF p_transaction_type = 'CIP ADJUSTMENT' THEN
432 l_source_type_code := 'CIP ADJUSTMENT';
433 ELSIF p_transaction_type = 'RECLASS' THEN
434 l_source_type_code := 'RECLASS';
435 ELSIF p_transaction_type = 'CIP RECLASS' THEN
436 l_source_type_code := 'CIP RECLASS';
437 END IF;
438
439 IF p_mrcsobtype = 'R' THEN
440 IF p_transaction_type in ('RECLASS','CIP RECLASS') THEN
441 BEGIN
442 SELECT nvl(sum(decode(debit_credit_flag,'DR',adjustment_amount,0)),0)
443 INTO p_adjustment
444 FROM fa_adjustments_mrc_v
445 WHERE book_type_code = p_book_type_code
446 AND asset_id = p_asset_id
447 AND period_counter_created = p_period_counter
448 AND source_type_code = l_source_type_code
449 AND adjustment_type = p_adjustment_type;
450 EXCEPTION
451 WHEN OTHERS THEN
452 p_adjustment := 0;
453 END;
454 ELSE
455 BEGIN
456 SELECT nvl(sum(decode(debit_credit_flag,'DR',adjustment_amount,-1 * adjustment_amount)),0)
457 INTO p_adjustment
458 FROM fa_adjustments_mrc_v
459 WHERE book_type_code = p_book_type_code
460 AND asset_id = p_asset_id
461 AND period_counter_created = p_period_counter
462 AND source_type_code = l_source_type_code
463 AND adjustment_type = p_adjustment_type;
464 EXCEPTION
465 WHEN OTHERS THEN
466 p_adjustment := 0;
467 END;
468 END IF;
469 ELSE
470 IF p_transaction_type in ('RECLASS','CIP RECLASS') THEN
471 BEGIN
472 SELECT nvl(sum(decode(debit_credit_flag,'DR',adjustment_amount,0)),0)
473 INTO p_adjustment
474 FROM fa_adjustments
475 WHERE book_type_code = p_book_type_code
476 AND asset_id = p_asset_id
477 AND period_counter_created = p_period_counter
478 AND source_type_code = l_source_type_code
479 AND adjustment_type = p_adjustment_type;
480 EXCEPTION
481 WHEN OTHERS THEN
482 p_adjustment := 0;
483 END;
484 ELSE
485 BEGIN
486 SELECT nvl(sum(decode(debit_credit_flag,'DR',adjustment_amount,-1 * adjustment_amount)),0)
487 INTO p_adjustment
488 FROM fa_adjustments
489 WHERE book_type_code = p_book_type_code
490 AND asset_id = p_asset_id
491 AND period_counter_created = p_period_counter
492 AND source_type_code = l_source_type_code
493 AND adjustment_type = p_adjustment_type;
494 EXCEPTION
495 WHEN OTHERS THEN
496 p_adjustment := 0;
497 END;
498 END IF;
499 END IF;
500
501 END get_adjust_amount;
502
503 /*+=========================================================================+
504 | PRIVATE PROCEDURE |
505 | Procedure: |
506 | |
507 | Get the historical info at the begin of the reporting period for a |
508 | given asset |
509 | |
510 +=========================================================================+*/
511 PROCEDURE get_asset_info_beg_period_mrc ( p_hist_book_type_code IN VARCHAR2
512 , p_asset_id IN NUMBER
513 , p_period_counter_from IN NUMBER
514 , p_period_counter_to IN NUMBER
515 , p_historical_cost_begin_period IN OUT NOCOPY NUMBER
516 , p_accum_depr_begin_period IN OUT NOCOPY NUMBER) IS
517
518 BEGIN
519 BEGIN
520 SELECT nvl(bk.cost,0)
521 INTO p_historical_cost_begin_period
522 FROM fa_books_mrc_v bk,
523 fa_deprn_periods_mrc_v dp
524 WHERE bk.book_type_code = p_hist_book_type_code
525 AND bk.asset_id = p_asset_id
526 AND bk.book_type_code = dp.book_type_code
527 AND dp.period_open_date between bk.date_effective and nvl(bk.date_ineffective,dp.period_open_date)
528 AND dp.period_counter = p_period_counter_from;
529 EXCEPTION
530 WHEN OTHERS THEN
531 p_historical_cost_begin_period := 0;
532 END;
533
534 BEGIN
535 SELECT NVL(a.deprn_reserve - a.ytd_deprn,0)
536 INTO p_accum_depr_begin_period
537 FROM fa_deprn_summary_mrc_v a
538 WHERE a.book_type_code = p_hist_book_type_code
539 AND a.asset_id = p_asset_id
540 AND a.period_counter between p_period_counter_from AND p_period_counter_to
541 AND a.deprn_source_code = 'BOOKS';
542 EXCEPTION
543 WHEN OTHERS THEN
544 p_accum_depr_begin_period := 0;
545 END;
546
547 -- Bug 3128957:
548 --
549 IF p_accum_depr_begin_period = 0 THEN
550 BEGIN
551 SELECT NVL(a.deprn_reserve,0)
552 INTO p_accum_depr_begin_period
553 FROM fa_deprn_summary_mrc_v a
554 WHERE a.book_type_code = p_hist_book_type_code
555 AND a.asset_id = p_asset_id
556 AND a.period_counter in (SELECT max(b.period_counter)
557 FROM fa_deprn_summary_mrc_v b
558 WHERE b.book_type_code = p_hist_book_type_code
559 AND b.asset_id = p_asset_id
560 AND b.period_counter <= p_period_counter_from - 1);
561 EXCEPTION
562 WHEN OTHERS THEN
563 p_accum_depr_begin_period := 0;
564 END;
565 END IF;
566
567 END get_asset_info_beg_period_mrc;
568
569
570 /*+=========================================================================+
571 | PRIVATE PROCEDURE |
572 | Procedure: |
573 | |
574 | Get the historical and adjusted cost at the end of the reporting |
575 | period for a given asset |
576 | |
577 +=========================================================================+*/
578 PROCEDURE get_asset_info_end_period_mrc ( p_hist_book_type_code IN VARCHAR2
579 , p_adj_book_type_code IN VARCHAR2
580 , p_asset_id IN NUMBER
581 , p_period_counter_from IN NUMBER
582 , p_period_counter_to IN NUMBER
583 , p_historical_cost_end_period IN OUT NOCOPY NUMBER
584 , p_adjusted_cost_end_period IN OUT NOCOPY NUMBER
585 , p_hist_accum_depr_end_period IN OUT NOCOPY NUMBER
586 , p_adj_accum_depr_end_period IN OUT NOCOPY NUMBER
587 , p_depr_rpt_period IN OUT NOCOPY NUMBER) IS
588 l_depr_rpt_period number;
589 l_api_name CONSTANT VARCHAR2(30) := 'GET_ASSET_INFO_END_PERIOD_MRC';
590
591
592 BEGIN
593 BEGIN
594 SELECT nvl(bk.cost,0)
595 INTO p_historical_cost_end_period
596 FROM fa_books_mrc_v bk,
597 fa_deprn_periods_mrc_v dp
598 WHERE bk.book_type_code = p_hist_book_type_code
599 AND bk.asset_id = p_asset_id
600 AND bk.book_type_code = dp.book_type_code
601 AND dp.period_close_date between bk.date_effective and nvl(bk.date_ineffective,dp.period_close_date)
602 AND dp.period_counter = p_period_counter_to;
603 EXCEPTION
604 WHEN OTHERS THEN
605 p_historical_cost_end_period := 0;
606 END;
607
608 BEGIN
609 SELECT nvl(bk.cost,0)
610 INTO p_adjusted_cost_end_period
611 FROM fa_books_mrc_v bk,
612 fa_deprn_periods_mrc_v dp
613 WHERE bk.book_type_code = p_adj_book_type_code
614 AND bk.asset_id = p_asset_id
615 AND bk.book_type_code = dp.book_type_code
616 AND dp.period_close_date between bk.date_effective and nvl(bk.date_ineffective,dp.period_close_date)
617 AND dp.period_counter = p_period_counter_to;
618 EXCEPTION
619 WHEN OTHERS THEN
620 p_adjusted_cost_end_period := 0;
621 END;
622
623 -- Bug 3128957:
624 --
625 BEGIN
626 SELECT NVL(a.deprn_reserve,0)
627 INTO p_hist_accum_depr_end_period
628 FROM fa_deprn_summary_mrc_v a
629 WHERE a.book_type_code = p_hist_book_type_code
630 AND a.asset_id = p_asset_id
631 AND a.period_counter in (SELECT max(b.period_counter)
632 FROM fa_deprn_summary_mrc_v b
633 WHERE b.book_type_code = p_hist_book_type_code
634 AND b.asset_id = p_asset_id
635 AND b.period_counter <= p_period_counter_to);
636 EXCEPTION
637 WHEN OTHERS THEN
638 p_hist_accum_depr_end_period := 0;
639 END;
640
641 BEGIN
642 SELECT NVL(a.deprn_reserve,0)
643 INTO p_adj_accum_depr_end_period
644 FROM fa_deprn_summary_mrc_v a
645 WHERE a.book_type_code = p_adj_book_type_code
646 AND a.asset_id = p_asset_id
647 AND a.period_counter in (SELECT max(b.period_counter)
648 FROM fa_deprn_summary_mrc_v b
649 WHERE b.book_type_code = p_adj_book_type_code
650 AND b.asset_id = p_asset_id
651 AND b.period_counter <= p_period_counter_to);
652 EXCEPTION
653 WHEN OTHERS THEN
654 p_adj_accum_depr_end_period := 0;
655 END;
656
657 -- Bug 3128957: End of changes
658 --
659
660 l_depr_rpt_period := 0;
661 p_depr_rpt_period := 0;
662 BEGIN
663 SELECT NVL(a.ytd_deprn,0)
664 INTO l_depr_rpt_period
665 FROM fa_deprn_summary_mrc_v a
666 WHERE a.book_type_code = p_hist_book_type_code
667 AND a.asset_id = p_asset_id
668 AND a.deprn_source_code = 'BOOKS'
669 AND a.period_counter between p_period_counter_from and p_period_counter_to;
670
671 EXCEPTION
672 WHEN NO_DATA_FOUND THEN
673 l_depr_rpt_period := 0;
674 WHEN OTHERS THEN
675 l_depr_rpt_period := 0;
676 END;
677
678 BEGIN
679 SELECT NVL(SUM(NVL(a.deprn_amount,0)),0)
680 INTO p_depr_rpt_period
681 FROM fa_deprn_summary_mrc_v a
682 WHERE a.book_type_code = p_hist_book_type_code
683 AND a.asset_id = p_asset_id
684 AND a.deprn_source_code = 'DEPRN'
685 AND a.period_counter between p_period_counter_from and p_period_counter_to;
686 EXCEPTION
687 WHEN NO_DATA_FOUND THEN
688 p_depr_rpt_period := 0;
689 WHEN OTHERS THEN
690 p_depr_rpt_period := 0;
691 END;
692 p_depr_rpt_period := p_depr_rpt_period + l_depr_rpt_period;
693
694 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
695 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
696 END IF;
697
698 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
699 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_depr_rpt_period := '||to_char(p_depr_rpt_period));
700 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_depr_rpt_period := '||to_char(p_depr_rpt_period));
701 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_depr_rpt_period := '||to_char(p_depr_rpt_period));
702 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_depr_rpt_period := '||to_char(p_depr_rpt_period));
703 END IF;
704
705 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
706 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
707 END IF;
708
709 END get_asset_info_end_period_mrc;
710
711 /*+=========================================================================+
712 | PRIVATE PROCEDURE |
713 | Procedure: |
714 | |
715 | Get the historical info at the begin of the reporting period for a |
716 | given asset |
717 | |
718 +=========================================================================+*/
719 PROCEDURE get_asset_info_beg_period ( p_hist_book_type_code IN VARCHAR2
720 , p_asset_id IN NUMBER
721 , p_period_counter_from IN NUMBER
722 , p_period_counter_to IN NUMBER
723 , p_historical_cost_begin_period IN OUT NOCOPY NUMBER
724 , p_accum_depr_begin_period IN OUT NOCOPY NUMBER) IS
725
726 l_api_name CONSTANT VARCHAR2(30) := 'GET_ASSET_INFO_BEG_PERIOD';
727
728 BEGIN
729
730 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
731 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
732 END IF;
733
734 BEGIN
735 SELECT nvl(bk.cost,0)
736 INTO p_historical_cost_begin_period
737 FROM fa_books bk,
738 fa_deprn_periods dp
739 WHERE bk.book_type_code = p_hist_book_type_code
740 AND bk.asset_id = p_asset_id
741 AND bk.book_type_code = dp.book_type_code
742 AND dp.period_open_date between bk.date_effective and nvl(bk.date_ineffective,dp.period_open_date)
743 AND dp.period_counter = p_period_counter_from;
744 EXCEPTION
745 WHEN OTHERS THEN
746 p_historical_cost_begin_period := 0;
747 END;
748
749 BEGIN
750 SELECT NVL(a.deprn_reserve - a.ytd_deprn,0)
751 INTO p_accum_depr_begin_period
752 FROM fa_deprn_summary a
753 WHERE a.book_type_code = p_hist_book_type_code
754 AND a.asset_id = p_asset_id
755 AND a.period_counter between p_period_counter_from AND p_period_counter_to
756 AND a.deprn_source_code = 'BOOKS';
757 EXCEPTION
758 WHEN OTHERS THEN
759 p_accum_depr_begin_period := 0;
760 END;
761
762 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
763 FND_FILE.PUT_LINE(FND_FILE.LOG,'NEW 1: p_accum_depr_beg_period := '||to_char(p_accum_depr_begin_period));
764 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'NEW 1: p_accum_depr_beg_period := '||to_char(p_accum_depr_begin_period));
765 END IF;
766
767 -- Bug 3128957:
768 --
769 IF p_accum_depr_begin_period = 0 THEN
770 BEGIN
771 SELECT NVL(a.deprn_reserve,0)
772 INTO p_accum_depr_begin_period
773 FROM fa_deprn_summary a
774 WHERE a.book_type_code = p_hist_book_type_code
775 AND a.asset_id = p_asset_id
776 AND a.period_counter in (SELECT max(b.period_counter)
777 FROM fa_deprn_summary b
778 WHERE b.book_type_code = p_hist_book_type_code
779 AND b.asset_id = p_asset_id
780 AND b.period_counter <= p_period_counter_from - 1);
781 EXCEPTION
782 WHEN OTHERS THEN
783 p_accum_depr_begin_period := 0;
784 END;
785 END IF;
786
787 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
788 FND_FILE.PUT_LINE(FND_FILE.LOG,'NEW 1: p_accum_depr_beg_period := '||to_char(p_accum_depr_begin_period));
789 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'NEW 2: p_accum_depr_beg_period := '||to_char(p_accum_depr_begin_period));
790 END IF;
791
792 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
793 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
794 END IF;
795
796 END get_asset_info_beg_period;
797
798
799 /*+=========================================================================+
800 | PRIVATE PROCEDURE |
801 | Procedure: |
802 | |
803 | Get the historical and adjusted cost at the end of the reporting |
804 | period for a given asset |
805 | |
806 +=========================================================================+*/
807 PROCEDURE get_asset_info_end_period ( p_hist_book_type_code IN VARCHAR2
808 , p_adj_book_type_code IN VARCHAR2
809 , p_asset_id IN NUMBER
810 , p_period_counter_from IN NUMBER
811 , p_period_counter_to IN NUMBER
812 , p_historical_cost_end_period IN OUT NOCOPY NUMBER
813 , p_adjusted_cost_end_period IN OUT NOCOPY NUMBER
814 , p_hist_accum_depr_end_period IN OUT NOCOPY NUMBER
815 , p_adj_accum_depr_end_period IN OUT NOCOPY NUMBER
816 , p_depr_rpt_period IN OUT NOCOPY NUMBER) IS
817 l_depr_rpt_period number;
818 l_api_name CONSTANT VARCHAR2(30) := 'GET_ASSET_INFO_END_PERIOD';
819
820
821 BEGIN
822 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
823 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
824 END IF;
825
826 BEGIN
827 SELECT nvl(bk.cost,0)
828 INTO p_historical_cost_end_period
829 FROM fa_books bk,
830 fa_deprn_periods dp
831 WHERE bk.book_type_code = p_hist_book_type_code
832 AND bk.asset_id = p_asset_id
833 AND bk.book_type_code = dp.book_type_code
834 AND dp.period_close_date between bk.date_effective and nvl(bk.date_ineffective,dp.period_close_date)
835 AND dp.period_counter = p_period_counter_to;
836 EXCEPTION
837 WHEN OTHERS THEN
838 p_historical_cost_end_period := 0;
839 END;
840
841 BEGIN
842 SELECT nvl(bk.cost,0)
843 INTO p_adjusted_cost_end_period
844 FROM fa_books bk,
845 fa_deprn_periods dp
846 WHERE bk.book_type_code = p_adj_book_type_code
847 AND bk.asset_id = p_asset_id
848 AND bk.book_type_code = dp.book_type_code
849 AND dp.period_close_date between bk.date_effective and nvl(bk.date_ineffective,dp.period_close_date)
850 AND dp.period_counter = p_period_counter_to;
851 EXCEPTION
852 WHEN OTHERS THEN
853 p_adjusted_cost_end_period := 0;
854 END;
855
856 -- Bug 3128957:
857 --
858 BEGIN
859 SELECT NVL(a.deprn_reserve,0)
860 INTO p_hist_accum_depr_end_period
861 FROM fa_deprn_summary a
862 WHERE a.book_type_code = p_hist_book_type_code
863 AND a.asset_id = p_asset_id
864 AND a.period_counter in (SELECT max(b.period_counter)
865 FROM fa_deprn_summary b
866 WHERE b.book_type_code = p_hist_book_type_code
867 AND b.asset_id = p_asset_id
868 AND b.period_counter <= p_period_counter_to);
869 EXCEPTION
870 WHEN OTHERS THEN
871 p_hist_accum_depr_end_period := 0;
872 END;
873
874 BEGIN
875 SELECT NVL(a.deprn_reserve,0)
876 INTO p_adj_accum_depr_end_period
877 FROM fa_deprn_summary a
878 WHERE a.book_type_code = p_adj_book_type_code
879 AND a.asset_id = p_asset_id
880 AND a.period_counter in (SELECT max(b.period_counter)
881 FROM fa_deprn_summary b
882 WHERE b.book_type_code = p_adj_book_type_code
883 AND b.asset_id = p_asset_id
884 AND b.period_counter <= p_period_counter_to);
885 EXCEPTION
886 WHEN OTHERS THEN
887 p_adj_accum_depr_end_period := 0;
888 END;
889
890 -- Bug 3128957: End of changes
891 --
892
893 l_depr_rpt_period := 0;
894 p_depr_rpt_period := 0;
895 BEGIN
896 SELECT NVL(a.ytd_deprn,0)
897 INTO l_depr_rpt_period
898 FROM fa_deprn_summary a
899 WHERE a.book_type_code = p_hist_book_type_code
900 AND a.asset_id = p_asset_id
901 AND a.deprn_source_code = 'BOOKS'
902 AND a.period_counter between p_period_counter_from and p_period_counter_to;
903
904 EXCEPTION
905 WHEN NO_DATA_FOUND THEN
906 l_depr_rpt_period := 0;
907 WHEN OTHERS THEN
908 l_depr_rpt_period := 0;
909 END;
910
911 BEGIN
912 SELECT NVL(SUM(NVL(a.deprn_amount,0)),0)
913 INTO p_depr_rpt_period
914 FROM fa_deprn_summary a
915 WHERE a.book_type_code = p_hist_book_type_code
916 AND a.asset_id = p_asset_id
917 AND a.deprn_source_code = 'DEPRN'
918 AND a.period_counter between p_period_counter_from and p_period_counter_to;
919 EXCEPTION
920 WHEN NO_DATA_FOUND THEN
921 p_depr_rpt_period := 0;
922 WHEN OTHERS THEN
923 p_depr_rpt_period := 0;
924 END;
925 p_depr_rpt_period := p_depr_rpt_period + l_depr_rpt_period;
926 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
927 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_depr_rpt_period := '||to_char(p_depr_rpt_period));
928 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_depr_rpt_period := '||to_char(p_depr_rpt_period));
929 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_depr_rpt_period := '||to_char(p_depr_rpt_period));
930 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_depr_rpt_period := '||to_char(p_depr_rpt_period));
931 END IF;
932
933 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
934 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
935 END IF;
936
937 END get_asset_info_end_period;
938
939 /*+=========================================================================+
940 | PRIVATE PROCEDURE |
941 | Procedure: |
942 | |
943 | Get the remaining reval coefficient |
944 | |
945 +=========================================================================+*/
946 PROCEDURE get_remain_reval_coefficient ( p_adj_book_type_code IN VARCHAR2
947 , p_asset_id IN NUMBER
948 , p_date_placed_in_service IN DATE
949 , p_period_counter_from IN NUMBER
950 , p_period_counter_to IN NUMBER
951 , p_remain_reval_coefficient IN OUT NOCOPY NUMBER) IS
952 l_category_id NUMBER;
953 l_price_index NUMBER;
954 l_reval_date DATE;
955 l_index_value_from NUMBER;
956 l_index_value_to NUMBER;
957 BEGIN
958 BEGIN
959 SELECT ah.category_id
960 INTO l_category_id
961 FROM fa_asset_history ah,
962 fa_deprn_periods dp
963 WHERE ah.asset_id = p_asset_id
964 AND dp.book_type_code = p_adj_book_type_code
965 AND dp.period_counter = p_period_counter_from
966 AND dp.period_close_date between ah.date_effective and nvl(ah.date_ineffective, dp.period_close_date);
967 EXCEPTION WHEN OTHERS THEN
968 l_category_id := 0;
969 END;
970
971 BEGIN
972 SELECT price_index_id
973 INTO l_price_index
974 FROM fa_category_book_defaults a, fa_price_indexes b
975 WHERE a.book_type_code = p_adj_book_type_code
976 AND a.category_id = l_category_id
977 AND p_date_placed_in_service >= a.start_dpis
978 AND p_date_placed_in_service <= NVL(a.end_dpis,p_date_placed_in_service)
979 AND a.price_index_name = b.price_index_name;
980 EXCEPTION WHEN OTHERS THEN
981 l_price_index := 0;
982 END;
983
984 BEGIN
985 SELECT rev.reval_date
986 INTO l_reval_date
987 FROM fa_mass_revaluations rev,
988 fa_deprn_periods dp
989 WHERE dp.period_counter = p_period_counter_from
990 AND dp.book_type_code = p_adj_book_type_code
991 AND dp.book_type_code = rev.book_type_code
992 AND dp.calendar_period_open_date <= rev.reval_date
993 AND rev.reval_date <= nvl(dp.calendar_period_close_date, rev.reval_date)
994 AND rev.status = 'COMPLETED';
995 EXCEPTION WHEN OTHERS THEN
996 l_reval_date := null;
997 END;
998
999 BEGIN
1000 SELECT price_index_value
1001 INTO l_index_value_from
1002 FROM fa_price_index_values
1003 WHERE price_index_id = l_price_index
1004 AND l_reval_date BETWEEN from_date AND nvl(to_date,l_reval_date);
1005 EXCEPTION WHEN OTHERS THEN
1006 l_index_value_from := 0;
1007 END;
1008
1009 BEGIN
1010 SELECT max(rev.reval_date)
1011 INTO l_reval_date
1012 FROM fa_mass_revaluations rev,
1013 fa_deprn_periods dp
1014 WHERE dp.period_counter between p_period_counter_from and p_period_counter_to
1015 AND dp.book_type_code = p_adj_book_type_code
1016 AND dp.book_type_code = rev.book_type_code
1017 AND dp.calendar_period_open_date <= rev.reval_date
1018 AND rev.reval_date <= nvl(dp.calendar_period_close_date, rev.reval_date)
1019 AND rev.status = 'COMPLETED';
1020 EXCEPTION WHEN OTHERS THEN
1021 l_reval_date := null;
1022 END;
1023
1024 BEGIN
1025 SELECT price_index_value
1026 INTO l_index_value_to
1027 FROM fa_price_index_values
1028 WHERE price_index_id = l_price_index
1029 AND l_reval_date BETWEEN from_date AND nvl(to_date,l_reval_date);
1030 EXCEPTION WHEN OTHERS THEN
1031 l_index_value_to := 0;
1032 END;
1033
1034 IF l_index_value_from = 0 THEN
1035 p_remain_reval_coefficient := 1;
1036 ELSE
1037 p_remain_reval_coefficient := l_index_value_to/l_index_value_from;
1038 END IF;
1039
1040 END get_remain_reval_coefficient;
1041
1042 /*+=========================================================================+
1043 | PRIVATE PROCEDURE |
1044 | Procedure: |
1045 | |
1046 | Get the cost coefficient |
1047 | |
1048 +=========================================================================+*/
1049 PROCEDURE get_cost_coefficient ( p_hist_book_type_code IN VARCHAR2
1050 , p_adj_book_type_code IN VARCHAR2
1051 , p_asset_id IN NUMBER
1052 , p_period_counter_from IN NUMBER
1053 , p_period_counter_to IN NUMBER
1054 , p_historical_cost_begin_period IN OUT NOCOPY NUMBER
1055 , p_accum_depr_begin_period IN OUT NOCOPY NUMBER
1056 , p_historical_cost_end_period IN OUT NOCOPY NUMBER
1057 , p_adjusted_cost_end_period IN OUT NOCOPY NUMBER
1058 , p_hist_accum_depr_end_period IN OUT NOCOPY NUMBER
1059 , p_adj_accum_depr_end_period IN OUT NOCOPY NUMBER
1060 , p_depr_rpt_period IN OUT NOCOPY NUMBER
1061 , p_cost_coefficient IN OUT NOCOPY NUMBER
1062 , p_depr_coefficient IN OUT NOCOPY NUMBER
1063 , p_mrcsobtype IN VARCHAR2) IS
1064
1065 l_date_placed_in_service DATE;
1066 l_hist_cost_retirement NUMBER;
1067 l_adj_cost_retirement NUMBER;
1068 l_period_counter_fully_retired NUMBER;
1069 l_remain_reval_coefficient NUMBER;
1070 l_api_name CONSTANT VARCHAR2(30) := 'GET_COST_COEFFICIENT';
1071
1072
1073 BEGIN
1074 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1075 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1076 END IF;
1077
1078 IF p_mrcsobtype = 'R' THEN
1079 get_asset_info_beg_period_mrc ( p_hist_book_type_code
1080 , p_asset_id
1081 , p_period_counter_from
1082 , p_period_counter_to
1083 , p_historical_cost_begin_period
1084 , p_accum_depr_begin_period);
1085 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1086 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_historical_cost_beg_period := '||to_char(p_historical_cost_begin_period));
1087 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_historical_cost_beg_period := '||to_char(p_historical_cost_begin_period));
1088 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_accum_depr_beg_period := '||to_char(p_accum_depr_begin_period));
1089 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_accum_depr_beg_period := '||to_char(p_accum_depr_begin_period));
1090 END IF;
1091
1092 get_asset_info_end_period_mrc ( p_hist_book_type_code
1093 , p_adj_book_type_code
1094 , p_asset_id
1095 , p_period_counter_from
1096 , p_period_counter_to
1097 , p_historical_cost_end_period
1098 , p_adjusted_cost_end_period
1099 , p_hist_accum_depr_end_period
1100 , p_adj_accum_depr_end_period
1101 , p_depr_rpt_period);
1102 ELSE
1103 get_asset_info_beg_period ( p_hist_book_type_code
1104 , p_asset_id
1105 , p_period_counter_from
1106 , p_period_counter_to
1107 , p_historical_cost_begin_period
1108 , p_accum_depr_begin_period);
1109 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1110 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_historical_cost_beg_period := '||to_char(p_historical_cost_begin_period));
1111 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_historical_cost_beg_period := '||to_char(p_historical_cost_begin_period));
1112 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_accum_depr_beg_period := '||to_char(p_accum_depr_begin_period));
1113 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_accum_depr_beg_period := '||to_char(p_accum_depr_begin_period));
1114 END IF;
1115
1116 get_asset_info_end_period ( p_hist_book_type_code
1117 , p_adj_book_type_code
1118 , p_asset_id
1119 , p_period_counter_from
1120 , p_period_counter_to
1121 , p_historical_cost_end_period
1122 , p_adjusted_cost_end_period
1123 , p_hist_accum_depr_end_period
1124 , p_adj_accum_depr_end_period
1125 , p_depr_rpt_period);
1126
1127 END IF;
1128
1129 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1130 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_historical_cost_end_period := '||to_char(p_historical_cost_end_period));
1131 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_historical_cost_end_period := '||to_char(p_historical_cost_end_period));
1132 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_adjusted_cost_end_period := '||to_char(p_adjusted_cost_end_period));
1133 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_adjusted_cost_end_period := '||to_char(p_adjusted_cost_end_period));
1134 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_hist_accum_depr_end_period := '||to_char(p_hist_accum_depr_end_period));
1135 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_hist_accum_depr_end_period := '||to_char(p_hist_accum_depr_end_period));
1136 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_adj_accum_depr_end_period := '||to_char(p_adj_accum_depr_end_period));
1137 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_adj_accum_depr_end_period := '||to_char(p_adj_accum_depr_end_period));
1138 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_depr_rpt_period := '||to_char(p_depr_rpt_period));
1139 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_depr_rpt_period := '||to_char(p_depr_rpt_period));
1140 END IF;
1141
1142 IF p_historical_cost_end_period <> 0 THEN
1143 p_cost_coefficient := p_adjusted_cost_end_period / p_historical_cost_end_period;
1144 END IF;
1145
1146 IF p_historical_cost_end_period = 0 THEN
1147 /* Full retirement case */
1148 /* Find the cost at the last retirement period */
1149 IF p_mrcsobtype = 'R' THEN
1150 BEGIN
1151 SELECT bk.cost,
1152 bk.period_counter_fully_retired
1153 INTO l_hist_cost_retirement,
1154 l_period_counter_fully_retired
1155 FROM fa_books_mrc_v bk, fa_transaction_headers th
1156 WHERE bk.book_type_code = p_hist_book_type_code
1157 AND bk.asset_id = p_asset_id
1158 AND bk.period_counter_fully_retired = (SELECT max(bk1.period_counter_fully_retired)
1159 FROM fa_books bk1
1160 WHERE bk1.book_type_code = bk.book_type_code
1161 AND bk1.asset_id = bk.asset_id
1162 AND bk1.period_counter_fully_retired
1163 between p_period_counter_from and p_period_counter_to)
1164 AND bk.book_type_code = th.book_type_code
1165 AND bk.asset_id = th.asset_id
1166 AND bk.transaction_header_id_out = th.transaction_header_id
1167 AND th.transaction_type_code = 'FULL RETIREMENT';
1168 EXCEPTION WHEN OTHERS THEN
1169 l_hist_cost_retirement := 0;
1170 l_period_counter_fully_retired := 0;
1171 END;
1172
1173 BEGIN
1174 SELECT bk.cost,
1175 bk.date_placed_in_service
1176 INTO l_adj_cost_retirement,
1177 l_date_placed_in_service
1178 FROM fa_books_mrc_v bk, fa_transaction_headers th
1179 WHERE bk.book_type_code = p_adj_book_type_code
1180 AND bk.asset_id = p_asset_id
1181 AND bk.period_counter_fully_retired = (SELECT max(bk1.period_counter_fully_retired)
1182 FROM fa_books bk1
1183 WHERE bk1.book_type_code = bk.book_type_code
1184 AND bk1.asset_id = bk.asset_id
1185 AND bk1.period_counter_fully_retired
1186 between p_period_counter_from and p_period_counter_to)
1187 AND bk.book_type_code = th.book_type_code
1188 AND bk.asset_id = th.asset_id
1189 AND bk.transaction_header_id_out = th.transaction_header_id
1190 AND th.transaction_type_code = 'FULL RETIREMENT';
1191 EXCEPTION WHEN OTHERS THEN
1192 l_adj_cost_retirement := 0;
1193 END;
1194 ELSE
1195 BEGIN
1196 SELECT bk.cost,
1197 bk.period_counter_fully_retired
1198 INTO l_hist_cost_retirement,
1199 l_period_counter_fully_retired
1200 FROM fa_books bk, fa_transaction_headers th
1201 WHERE bk.book_type_code = p_hist_book_type_code
1202 AND bk.asset_id = p_asset_id
1203 AND bk.period_counter_fully_retired = (SELECT max(bk1.period_counter_fully_retired)
1204 FROM fa_books bk1
1205 WHERE bk1.book_type_code = bk.book_type_code
1206 AND bk1.asset_id = bk.asset_id
1207 AND bk1.period_counter_fully_retired
1208 between p_period_counter_from and p_period_counter_to)
1209 AND bk.book_type_code = th.book_type_code
1210 AND bk.asset_id = th.asset_id
1211 AND bk.transaction_header_id_out = th.transaction_header_id
1212 AND th.transaction_type_code = 'FULL RETIREMENT';
1213 EXCEPTION WHEN OTHERS THEN
1214 l_hist_cost_retirement := 0;
1215 l_period_counter_fully_retired := 0;
1216 END;
1217
1218 BEGIN
1219 SELECT bk.cost,
1220 bk.date_placed_in_service
1221 INTO l_adj_cost_retirement,
1222 l_date_placed_in_service
1223 FROM fa_books bk, fa_transaction_headers th
1224 WHERE bk.book_type_code = p_adj_book_type_code
1225 AND bk.asset_id = p_asset_id
1226 AND bk.period_counter_fully_retired = (SELECT max(bk1.period_counter_fully_retired)
1227 FROM fa_books bk1
1228 WHERE bk1.book_type_code = bk.book_type_code
1229 AND bk1.asset_id = bk.asset_id
1230 AND bk1.period_counter_fully_retired
1231 between p_period_counter_from and p_period_counter_to)
1232 AND bk.book_type_code = th.book_type_code
1233 AND bk.asset_id = th.asset_id
1234 AND bk.transaction_header_id_out = th.transaction_header_id
1235 AND th.transaction_type_code = 'FULL RETIREMENT';
1236 EXCEPTION WHEN OTHERS THEN
1237 l_adj_cost_retirement := 0;
1238 END;
1239 END IF;
1240
1241 IF l_hist_cost_retirement <> 0 THEN
1242 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1243 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_period_counter_fully_retired := '||to_char(l_period_counter_fully_retired));
1244 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_period_counter_fully_retired := '||to_char(l_period_counter_fully_retired));
1245 END IF;
1246 get_remain_reval_coefficient ( p_adj_book_type_code
1247 , p_asset_id
1248 , l_date_placed_in_service
1249 , l_period_counter_fully_retired
1250 , p_period_counter_to
1251 , l_remain_reval_coefficient
1252 );
1253
1254 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1255 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_remain_reval_coefficient := '||to_char(l_remain_reval_coefficient));
1256 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_remain_reval_coefficient := '||to_char(l_remain_reval_coefficient));
1257 END IF;
1258
1259 p_cost_coefficient := l_remain_reval_coefficient * l_adj_cost_retirement / l_hist_cost_retirement;
1260
1261 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1262 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_cost_coefficient := '||to_char(p_cost_coefficient));
1263 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_cost_coefficient := '||to_char(p_cost_coefficient));
1264 END IF;
1265
1266 ELSE
1267 p_cost_coefficient := 0;
1268 END IF;
1269 END IF;
1270
1271 IF p_hist_accum_depr_end_period <> 0 THEN
1272 p_depr_coefficient := p_adj_accum_depr_end_period / p_hist_accum_depr_end_period;
1273 ELSE
1274 p_depr_coefficient := p_cost_coefficient;
1275 END IF;
1276
1277 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1278 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_cost_coefficient := '||to_char(p_cost_coefficient));
1279 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_cost_coefficient := '||to_char(p_cost_coefficient));
1280 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_depr_coefficient := '||to_char(p_depr_coefficient));
1281 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_depr_coefficient := '||to_char(p_depr_coefficient));
1282 END IF;
1283
1284 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1285 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1286 END IF;
1287
1288 END get_cost_coefficient;
1289
1290 /*+=========================================================================+
1291 | PRIVATE PROCEDURE |
1292 | Procedure: |
1293 | |
1294 | Get exhibit group for the category |
1295 | |
1296 +=========================================================================+*/
1297 PROCEDURE get_exhibit_group ( p_category_id IN NUMBER
1298 , p_asset_id IN NUMBER
1299 , p_corp_book IN VARCHAR2
1300 , p_asset_type IN VARCHAR2
1301 , p_exhibit_group_id IN OUT NOCOPY NUMBER) IS
1302
1303 l_category_id NUMBER;
1304 l_api_name CONSTANT VARCHAR2(30) := 'GET_EXHIBIT_GROUP';
1305
1306 BEGIN
1307
1308 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1309 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1310 END IF;
1311
1312 l_category_id := p_category_id;
1313
1314 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1315 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_asset_id = '||to_char(p_asset_id));
1316 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_asset_id = '||to_char(p_asset_id));
1317 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_category_id = '||to_char(p_category_id));
1318 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_category_id = '||to_char(p_category_id));
1319 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_asset_type = '||p_asset_type);
1320 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_asset_type = '||p_asset_type);
1321 END IF;
1322
1323 IF p_asset_type = 'CIP' THEN
1324 BEGIN
1325 SELECT exb.exhibit_group_id
1326 INTO p_exhibit_group_id
1327 FROM jl_ar_fa_exhibit_groups exb
1328 WHERE exb.cip_group = 'Y';
1329 EXCEPTION WHEN OTHERS THEN
1330 p_exhibit_group_id := 0;
1331 END;
1332 ELSE
1333 p_exhibit_group_id := 0;
1334 BEGIN
1335 SELECT cat.global_attribute16
1336 INTO p_exhibit_group_id
1337 FROM fa_category_books cat
1338 WHERE cat.book_type_code = p_corp_book
1339 AND cat.category_id = l_category_id;
1340 EXCEPTION WHEN OTHERS THEN
1341 p_exhibit_group_id := 0;
1342 END;
1343 END IF;
1344
1345 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1346 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_exhibit_group_id = '||to_char(p_exhibit_group_id));
1347 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_exhibit_group_id = '||to_char(p_exhibit_group_id));
1348 END IF;
1349
1350 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1351 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1352 END IF;
1353
1354 END get_exhibit_group;
1355
1356 /*+=========================================================================+
1357 | PRIVATE PROCEDURE |
1358 | Procedure: |
1359 | |
1360 | Get the old category |
1361 | |
1362 +=========================================================================+*/
1363 PROCEDURE get_old_category ( p_asset_id IN NUMBER
1364 , p_transaction_header_id IN NUMBER
1365 , p_transaction_type_code IN VARCHAR2
1366 , p_old_category_id IN OUT NOCOPY NUMBER
1367 , p_position IN OUT NOCOPY NUMBER) IS
1368
1369 row_count NUMBER;
1370 l_cip_exhibit_group_id NUMBER;
1371 old_cat_not_found BOOLEAN;
1372 l_asset_type VARCHAR2(30);
1373 l_api_name CONSTANT VARCHAR2(30) := 'GET_OLD_CATEGORY';
1374
1375
1376 BEGIN
1377
1378 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1379 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1380 END IF;
1381
1382 p_old_category_id := 0;
1383 p_position := 0;
1384
1385 BEGIN
1386 SELECT ah.category_id,
1387 ah.asset_type
1388 INTO p_old_category_id,
1389 l_asset_type
1390 FROM fa_asset_history ah
1391 WHERE ah.asset_id = p_asset_id
1392 AND ah.transaction_header_id_out = p_transaction_header_id;
1393 EXCEPTION WHEN OTHERS THEN
1394 p_old_category_id := 0;
1395 END;
1396
1397 IF p_transaction_type_code = 'ADDITION' AND l_asset_type <> 'CIP' THEN
1398 p_old_category_id := 0;
1399 END IF;
1400
1401 BEGIN
1402 SELECT exb.exhibit_group_id
1403 INTO l_cip_exhibit_group_id
1404 FROM jl_ar_fa_exhibit_groups exb
1405 WHERE exb.cip_group = 'Y';
1406 EXCEPTION WHEN OTHERS THEN
1407 l_cip_exhibit_group_id := 0;
1408 END;
1409
1410 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1411 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_cip_exhibit_group_id := '||to_char(l_cip_exhibit_group_id));
1412 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_cip_exhibit_group_id := '||to_char(l_cip_exhibit_group_id));
1413 END IF;
1414
1415 IF ((l_asset_type = 'CIP') OR (p_old_category_id <> 0)) THEN
1416
1417 row_count := 1;
1418 old_cat_not_found := TRUE;
1419 FOR row_count IN nvl(report_table.first,0) .. nvl(report_table.last,0) LOOP
1420 IF report_table.exists(row_count) THEN
1421 IF report_table(row_count).category_id = p_old_category_id THEN
1422 /* This is Capitalization or CIP RECLASS because previous asset_type = CIP*/
1423 IF l_asset_type = 'CIP' AND report_table(row_count).exhibit_group_id = l_cip_exhibit_group_id THEN
1424 p_position := row_count;
1425 old_cat_not_found := FALSE;
1426 exit;
1427 END IF;
1428 IF l_asset_type <> 'CIP' THEN -- This is RECLASS transaction
1429 p_position := row_count;
1430 old_cat_not_found := FALSE;
1431 exit;
1432 END IF;
1433 END IF;
1434 END IF;
1435 END LOOP;
1436 IF old_cat_not_found THEN
1437 p_position := 0;
1438 END IF;
1439 IF p_transaction_type_code = 'ADDITION' AND l_asset_type = 'CIP' THEN
1440 p_old_category_id := -1;
1441 END IF;
1442 END IF;
1443
1444 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1445 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_position = '||to_char(p_position));
1446 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_position = '||to_char(p_position));
1447 END IF;
1448
1449 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1450 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1451 END IF;
1452
1453 END get_old_category;
1454
1455 PROCEDURE insert_db_records IS
1456 row_count BINARY_INTEGER;
1457 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_DB_RECORDS';
1458
1459 BEGIN
1460
1461 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1462 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1463 END IF;
1464
1465 row_count := 1;
1466 FOR row_count IN report_table.first .. report_table.last LOOP
1467 IF report_table.exists(row_count) THEN
1468
1469 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1470 FND_FILE.PUT_LINE(FND_FILE.LOG,'exhibit_group_id('||to_char(row_count)||'):= '||to_char(report_table(row_count).exhibit_group_id));
1471 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'exhibit_group_id('||to_char(row_count)||'):= '||to_char(report_table(row_count).exhibit_group_id));
1472 FND_FILE.PUT_LINE(FND_FILE.LOG,'category_id('||to_char(row_count)||'):= '||to_char(report_table(row_count).category_id));
1473 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'category_id('||to_char(row_count)||'):= '||to_char(report_table(row_count).category_id));
1474 FND_FILE.PUT_LINE(FND_FILE.LOG,'asset_id('||to_char(row_count)||'):= '||to_char(report_table(row_count).asset_id));
1475 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'asset_id('||to_char(row_count)||'):= '||to_char(report_table(row_count).asset_id));
1476 FND_FILE.PUT_LINE(FND_FILE.LOG,'report_table(1).begin_cost := '||to_char(report_table(row_count).begin_cost));
1477 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'report_table(1).begin_cost := '||to_char(report_table(row_count).begin_cost));
1478 FND_FILE.PUT_LINE(FND_FILE.LOG,'additions('||to_char(row_count)||'):= '||to_char(report_table(row_count).additions));
1479 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'additions('||to_char(row_count)||'):= '||to_char(report_table(row_count).additions));
1480 FND_FILE.PUT_LINE(FND_FILE.LOG,'retirements('||to_char(row_count)||'):= '||to_char(report_table(row_count).retirements));
1481 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'retirements('||to_char(row_count)||'):= '||to_char(report_table(row_count).retirements));
1482 FND_FILE.PUT_LINE(FND_FILE.LOG,'transfers('||to_char(row_count)||'):= '||to_char(report_table(row_count).transfers));
1483 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'transfers('||to_char(row_count)||'):= '||to_char(report_table(row_count).transfers));
1484 FND_FILE.PUT_LINE(FND_FILE.LOG,'end_cost('||to_char(row_count)||'):= '||to_char(report_table(row_count).end_cost));
1485 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'end_cost('||to_char(row_count)||'):= '||to_char(report_table(row_count).end_cost));
1486 FND_FILE.PUT_LINE(FND_FILE.LOG,'report_table(1).begin_accum_depr := '||to_char(report_table(row_count).begin_accum_depr));
1487 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'report_table(1).begin_accum_depr := '||to_char(report_table(row_count).begin_accum_depr));
1488 END IF;
1489
1490 insert into JL_AR_FA_EXHIBIT_REPORT ( conc_request_id
1491 , exhibit_group_id
1492 , category_id
1493 , asset_id
1494 , begin_cost
1495 , additions
1496 , retirements
1497 , transfers
1498 , end_cost
1499 , begin_accum_depr
1500 , accum_depr_retirements
1501 , accum_depr_transfers
1502 , accum_depr_rpt_period
1503 , deprn_reserve
1504 , creation_date
1505 , created_by
1506 , last_update_date
1507 , last_updated_by
1508 , last_update_login
1509 )
1510 values ( report_table(row_count).conc_request_id
1511 , report_table(row_count).exhibit_group_id
1512 , report_table(row_count).category_id
1513 , report_table(row_count).asset_id
1514 , report_table(row_count).begin_cost
1515 , report_table(row_count).additions
1516 , report_table(row_count).retirements
1517 , report_table(row_count).transfers
1518 , report_table(row_count).end_cost
1519 , report_table(row_count).begin_accum_depr
1520 , report_table(row_count).accum_depr_retirements
1521 , report_table(row_count).accum_depr_transfers
1522 , report_table(row_count).accum_depr_rpt_period
1523 , report_table(row_count).deprn_reserve
1524 , sysdate
1525 , fnd_global.user_id
1526 , sysdate
1527 , fnd_global.user_id
1528 , fnd_global.user_id
1529 );
1530 END IF;
1531 END LOOP;
1532
1533 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1534 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1535 END IF;
1536
1537 END insert_db_records;
1538
1539 /*+=========================================================================+
1540 | PUBLIC PROCEDURE |
1541 | populate_FA_Exhibit_Data |
1542 | p_book_type_code IN Depreciation Book |
1543 | p_conc_request_id IN Concurrent Request Id |
1544 | p_period_counter_from IN Earliest Period on the report |
1545 | p_period_counter_to IN Latest Period on the report |
1546 | |
1547 | NOTES |
1548 | G |
1549 | |
1550 | |
1551 +=========================================================================+*/
1552 PROCEDURE populate_FA_Exhibit_Data (p_tax_book IN VARCHAR2,
1553 p_corp_book IN VARCHAR2,
1554 p_conc_request_id IN NUMBER,
1555 p_period_counter_from IN NUMBER,
1556 p_period_counter_to IN NUMBER,
1557 p_mrcsobtype IN VARCHAR2 DEFAULT 'P') IS
1558
1559 l_api_name CONSTANT VARCHAR2(30) := 'FA_EXHIBIT_DATA';
1560
1561 l_ignore_retirement VARCHAR2(1);
1562 l_ignore_reinstatement VARCHAR2(1);
1563 l_exhibit_group_id number;
1564 l_current_category_id number;
1565 l_old_category_id number;
1566 l_category_id number;
1567 l_asset_id number;
1568 l_period_counter number;
1569 l_historical_cost_begin_period number;
1570 l_historical_cost_end_period number;
1571 l_adjusted_cost_end_period number;
1572 l_accum_depr_begin_period number;
1573 l_hist_accum_depr_end_period number;
1574 l_adj_accum_depr_end_period number;
1575 l_depr_rpt_period number;
1576 l_cost_coefficient number;
1577 l_depr_coefficient number;
1578 l_reval_cost number;
1579 l_adjustment number;
1580 i number;
1581 j number;
1582 l_date_placed_in_service DATE;
1583 l_remain_reval_coefficient NUMBER;
1584
1585 -- Bug 4956193. Variables added to add logic to support multiple Adjustments in same period.
1586
1587 l_book_type_code_old fa_transaction_headers.book_type_code%type;
1588 l_asset_id_old fa_transaction_headers.asset_id%type;
1589 l_period_counter_old fa_deprn_periods.period_counter%type;
1590 l_transaction_type_code_old fa_transaction_headers.transaction_type_code%type;
1591 l_adj_already_calculated_flag varchar2(1);
1592
1593 -- Create ref cursors for bug 3101070
1594 Type report_row IS RECORD
1595 (
1596 asset_id fa_transaction_headers.asset_id%type,
1597 category_id fa_asset_history.category_id%type,
1598 period_counter fa_deprn_periods.period_counter%type,
1599 book_type_code fa_transaction_headers.book_type_code%type,
1600 transaction_type_code fa_transaction_headers.transaction_type_code%type,
1601 transaction_header_id fa_transaction_headers.transaction_header_id%type,
1602 asset_type fa_asset_history.asset_type%type,
1603 units fa_asset_history.units%type
1604 );
1605
1606 TYPE report_ref_cur is REF CURSOR;
1607 fetch_txns_for_rpt_period report_ref_cur;
1608 asset_txns_rec report_row;
1609
1610 ------------------------------------------------------------
1611 -- Cursor: fetch_txns_for_rpt_period --
1612 -- --
1613 -- Fetch all the asset transactions happened in the --
1614 -- reporting period. --
1615 -- --
1616 ------------------------------------------------------------
1617
1618 BEGIN
1619
1620 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1621 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1622 END IF;
1623
1624 l_exhibit_group_id := 0;
1625 l_category_id := 0;
1626 l_asset_id := 0;
1627 l_period_counter := 0;
1628
1629 -- Bug 4956193. Variables added to add logic to support multiple Adjustments in same period.
1630 l_book_type_code_old := null;
1631 l_asset_id_old := null;
1632 l_period_counter_old := null;
1633 l_transaction_type_code_old := null;
1634 l_adj_already_calculated_flag := 'N';
1635
1636
1637 delete from JL_AR_FA_EXHIBIT_REPORT;
1638
1639 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1640 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start');
1641 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Start');
1642 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_tax_book = '||p_tax_book);
1643 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_tax_book = '||p_tax_book);
1644 END IF;
1645
1646 IF p_mrcsobtype = 'R' THEN
1647 OPEN fetch_txns_for_rpt_period FOR
1648 SELECT th.asset_id,
1649 ah.category_id,
1650 dp.period_counter,
1651 th.book_type_code,
1652 th.transaction_type_code,
1653 th.transaction_header_id,
1654 ah.asset_type,
1655 nvl (ah.units,0)
1656 FROM fa_transaction_headers th,
1657 fa_deprn_periods_mrc_v dp,
1658 fa_asset_history ah
1659 WHERE dp.book_type_code = p_corp_book
1660 AND dp.period_counter between p_period_counter_from and p_period_counter_to
1661 AND th.book_type_code = dp.book_type_code
1662 AND dp.period_open_date <= th.date_effective
1663 AND th.date_effective <= dp.period_close_date
1664 AND th.transaction_type_code IN ('TRANSFER OUT',
1665 'RECLASS',
1666 'CIP RECLASS',
1667 -- 'TRANSFER',
1668 -- 'CIP TRANSFER',
1669 'UNIT ADJUSTMENT',
1670 'ADDITION',
1671 'CIP ADDITION',
1672 'ADJUSTMENT',
1673 'CIP ADJUSTMENT',
1674 'FULL RETIREMENT',
1675 'PARTIAL RETIREMENT',
1676 'REINSTATEMENT',
1677 -- 'TRANSFER IN',
1678 'ADDITION/VOID'
1679 )
1680 AND ah.asset_id = th.asset_id
1681 AND ah.date_effective <= th.date_effective
1682 AND th.date_effective < nvl (ah.date_ineffective, th.date_effective + 1)
1683 UNION
1684 SELECT ah.asset_id,
1685 ah.category_id,
1686 dp.period_counter - 1,
1687 dp.book_type_code,
1688 null,
1689 -1,
1690 ah.asset_type,
1691 nvl (ah.units,0)
1692 FROM fa_books bk,
1693 fa_deprn_periods_mrc_v dp,
1694 fa_asset_history ah
1695 WHERE dp.book_type_code = p_corp_book
1696 AND bk.book_type_code = dp.book_type_code
1697 AND bk.asset_id = ah.asset_id
1698 AND dp.period_counter = p_period_counter_from
1699 AND dp.period_open_date between ah.date_effective AND nvl(ah.date_ineffective,dp.period_open_date)
1700 AND dp.period_open_date between bk.date_effective AND nvl(bk.date_ineffective,dp.period_open_date)
1701 ORDER BY 1,3,5;
1702 ELSE
1703 OPEN fetch_txns_for_rpt_period FOR
1704 SELECT th.asset_id,
1705 ah.category_id,
1706 dp.period_counter,
1707 th.book_type_code,
1708 th.transaction_type_code,
1709 th.transaction_header_id,
1710 ah.asset_type,
1711 nvl (ah.units,0)
1712 FROM fa_transaction_headers th,
1713 fa_deprn_periods dp,
1714 fa_asset_history ah
1715 WHERE dp.book_type_code = p_corp_book
1716 AND dp.period_counter between p_period_counter_from and p_period_counter_to
1717 AND th.book_type_code = dp.book_type_code
1718 AND dp.period_open_date <= th.date_effective
1719 AND th.date_effective <= dp.period_close_date
1720 AND th.transaction_type_code IN ('TRANSFER OUT',
1721 'RECLASS',
1722 'CIP RECLASS',
1723 -- 'TRANSFER',
1724 -- 'CIP TRANSFER',
1725 'UNIT ADJUSTMENT',
1726 'ADDITION',
1727 'CIP ADDITION',
1728 'ADJUSTMENT',
1729 'CIP ADJUSTMENT',
1730 'FULL RETIREMENT',
1731 'PARTIAL RETIREMENT',
1732 'REINSTATEMENT',
1733 -- 'TRANSFER IN',
1734 'ADDITION/VOID'
1735 )
1736 AND ah.asset_id = th.asset_id
1737 AND ah.date_effective <= th.date_effective
1738 AND th.date_effective < nvl (ah.date_ineffective, th.date_effective + 1)
1739 UNION
1740 SELECT ah.asset_id,
1741 ah.category_id,
1742 dp.period_counter - 1,
1743 dp.book_type_code,
1744 null,
1745 -1,
1746 ah.asset_type,
1747 nvl (ah.units,0)
1748 FROM fa_books bk,
1749 fa_deprn_periods dp,
1750 fa_asset_history ah
1751 WHERE dp.book_type_code = p_corp_book
1752 AND bk.book_type_code = dp.book_type_code
1753 AND bk.asset_id = ah.asset_id
1754 AND dp.period_counter = p_period_counter_from
1755 AND dp.period_open_date between ah.date_effective AND nvl(ah.date_ineffective,dp.period_open_date)
1756 AND dp.period_open_date between bk.date_effective AND nvl(bk.date_ineffective,dp.period_open_date)
1757 ORDER BY 1,3,5;
1758 END IF;
1759
1760 LOOP
1761 FETCH fetch_txns_for_rpt_period INTO asset_txns_rec;
1762 EXIT WHEN fetch_txns_for_rpt_period%NOTFOUND
1763 OR fetch_txns_for_rpt_period%NOTFOUND IS NULL;
1764
1765 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1766 FND_FILE.PUT_LINE(FND_FILE.LOG,'**Next Record****');
1767 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, '**Next Record****');
1768 FND_FILE.PUT_LINE(FND_FILE.LOG,'asset_txns_rec.category_id := '||to_char(asset_txns_rec.category_id));
1769 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'asset_txns_rec.category_id := '||to_char(asset_txns_rec.category_id));
1770 FND_FILE.PUT_LINE(FND_FILE.LOG,'asset_txns_rec.asset_id := '||to_char(asset_txns_rec.asset_id));
1771 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'asset_txns_rec.asset_id := '||to_char(asset_txns_rec.asset_id));
1772 FND_FILE.PUT_LINE(FND_FILE.LOG,'asset_txns_rec.transaction_type_code := '||asset_txns_rec.transaction_type_code);
1773 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'asset_txns_rec.transaction_type_code := '||asset_txns_rec.transaction_type_code);
1774 FND_FILE.PUT_LINE(FND_FILE.LOG,'asset_txns_rec.period_counter := '||to_char(asset_txns_rec.period_counter));
1775 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'asset_txns_rec.period_counter := '||to_char(asset_txns_rec.period_counter));
1776 END IF;
1777
1778 IF ((asset_txns_rec.asset_id <> l_asset_id) OR (l_category_id <> asset_txns_rec.category_id)) THEN
1779 IF (asset_txns_rec.asset_id <> l_asset_id) THEN
1780 IF l_asset_id <> 0 THEN
1781 insert_db_records;
1782 report_table.delete;
1783 END IF;
1784 get_cost_coefficient ( p_corp_book
1785 , p_tax_book
1786 , asset_txns_rec.asset_id
1787 , p_period_counter_from
1788 , p_period_counter_to
1789 , l_historical_cost_begin_period
1790 , l_accum_depr_begin_period
1791 , l_historical_cost_end_period
1792 , l_adjusted_cost_end_period
1793 , l_hist_accum_depr_end_period
1794 , l_adj_accum_depr_end_period
1795 , l_depr_rpt_period
1796 , l_cost_coefficient
1797 , l_depr_coefficient
1798 , p_mrcsobtype
1799 );
1800
1801 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1802 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_historical_cost_beg_period := '||to_char(l_historical_cost_begin_period));
1803 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_historical_cost_beg_period := '||to_char(l_historical_cost_begin_period));
1804 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_accum_depr_beg_period := '||to_char(l_accum_depr_begin_period));
1805 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_accum_depr_beg_period := '||to_char(l_accum_depr_begin_period));
1806 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_historical_cost_end_period := '||to_char(l_historical_cost_end_period));
1807 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_historical_cost_end_period := '||to_char(l_historical_cost_end_period));
1808 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_adjusted_cost_end_period := '||to_char(l_adjusted_cost_end_period));
1809 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_adjusted_cost_end_period := '||to_char(l_adjusted_cost_end_period));
1810 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_hist_accum_depr_end_period := '||to_char(l_hist_accum_depr_end_period));
1811 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_hist_accum_depr_end_period := '||to_char(l_hist_accum_depr_end_period));
1812 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_adj_accum_depr_end_period := '||to_char(l_adj_accum_depr_end_period));
1813 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_adj_accum_depr_end_period := '||to_char(l_adj_accum_depr_end_period));
1814 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_depr_rpt_period := '||to_char(l_depr_rpt_period));
1815 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_depr_rpt_period := '||to_char(l_depr_rpt_period));
1816 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_cost_coefficient := '||to_char(l_cost_coefficient));
1817 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_cost_coefficient := '||to_char(l_cost_coefficient));
1818 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_depr_coefficient := '||to_char(l_depr_coefficient));
1819 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_depr_coefficient := '||to_char(l_depr_coefficient));
1820 END IF;
1821
1822 l_asset_id := asset_txns_rec.asset_id;
1823 i := 1;
1824
1825 /* If the first transaction hit in this loop is RECLASS or CAPITALIZATION(ADDITION) then we need to create additional record for old category */
1826
1827 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1828 FND_FILE.PUT_LINE(FND_FILE.LOG,'asset_txns_rec.transaction_type_code := ' || asset_txns_rec.transaction_type_code );
1829 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'asset_txns_rec.transaction_type_code := ' || asset_txns_rec.transaction_type_code );
1830 END IF;
1831
1832 IF asset_txns_rec.transaction_type_code in ('RECLASS','CIP RECLASS','ADDITION') THEN
1833 get_old_category ( asset_txns_rec.asset_id
1834 , asset_txns_rec.transaction_header_id
1835 , asset_txns_rec.transaction_type_code
1836 , l_old_category_id
1837 , j);
1838 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1839 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_tax_book = '||p_tax_book);
1840 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'p_tax_book = '||p_tax_book);
1841 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_old_category_id = '||to_char(l_old_category_id));
1842 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_old_category_id = '||to_char(l_old_category_id));
1843 END IF;
1844
1845 /* If old category returned is -1, then Transaction in consideration is Capitalization. */
1846 IF l_old_category_id = -1 THEN
1847 get_exhibit_group ( asset_txns_rec.category_id
1848 , asset_txns_rec.asset_id
1849 , p_corp_book
1850 , 'CIP'
1851 , l_exhibit_group_id);
1852 report_table(i).conc_request_id := p_conc_request_id;
1853 report_table(i).exhibit_group_id := l_exhibit_group_id;
1854 report_table(i).category_id := asset_txns_rec.category_id;
1855 report_table(i).asset_id := l_asset_id;
1856 report_table(i).additions := 0;
1857 report_table(i).retirements := 0;
1858 report_table(i).transfers := 0;
1859 report_table(i).accum_depr_retirements := 0;
1860 report_table(i).accum_depr_transfers := 0;
1861 report_table(i).end_cost := 0;
1862 report_table(i).accum_depr_rpt_period := 0;
1863 report_table(i).deprn_reserve := 0;
1864 ELSIF l_old_category_id > 0 THEN
1865 get_exhibit_group ( l_old_category_id
1866 , asset_txns_rec.asset_id
1867 , p_corp_book
1868 , asset_txns_rec.asset_type
1869 , l_exhibit_group_id);
1870 report_table(i).conc_request_id := p_conc_request_id;
1871 report_table(i).exhibit_group_id := l_exhibit_group_id;
1872 report_table(i).category_id := l_old_category_id;
1873 report_table(i).asset_id := l_asset_id;
1874 report_table(i).additions := 0;
1875 report_table(i).retirements := 0;
1876 report_table(i).transfers := 0;
1877 report_table(i).accum_depr_retirements := 0;
1878 report_table(i).accum_depr_transfers := 0;
1879 report_table(i).end_cost := 0;
1880 report_table(i).accum_depr_rpt_period := 0;
1881 report_table(i).deprn_reserve := 0;
1882 ELSE
1883 l_category_id := asset_txns_rec.category_id;
1884 get_exhibit_group ( l_category_id
1885 , asset_txns_rec.asset_id
1886 , p_corp_book
1887 , asset_txns_rec.asset_type
1888 , l_exhibit_group_id);
1889 END IF;
1890 ELSE
1891 l_category_id := asset_txns_rec.category_id;
1892 get_exhibit_group ( l_category_id
1893 , asset_txns_rec.asset_id
1894 , p_corp_book
1895 , asset_txns_rec.asset_type
1896 , l_exhibit_group_id);
1897 END IF;
1898
1899 report_table(i).begin_cost := l_cost_coefficient * l_historical_cost_begin_period;
1900 report_table(i).begin_accum_depr := l_depr_coefficient * l_accum_depr_begin_period;
1901
1902 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1903 FND_FILE.PUT_LINE(FND_FILE.LOG,'report_table(1).begin_cost := '||to_char(report_table(i).begin_cost));
1904 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'report_table(1).begin_cost := '||to_char(report_table(i).begin_cost));
1905 FND_FILE.PUT_LINE(FND_FILE.LOG,'report_table(1).begin_accum_depr := '||to_char(report_table(i).begin_accum_depr));
1906 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'report_table(1).begin_accum_depr := '||to_char(report_table(i).begin_accum_depr));
1907 END IF;
1908
1909 END IF;
1910 IF (asset_txns_rec.category_id <> l_category_id) THEN
1911 IF l_category_id <> 0 THEN
1912 i := i + 1;
1913 report_table(i).begin_cost := 0;
1914 report_table(i).begin_accum_depr := 0;
1915 l_category_id := asset_txns_rec.category_id;
1916 get_exhibit_group ( l_category_id
1917 , asset_txns_rec.asset_id
1918 , p_corp_book
1919 , asset_txns_rec.asset_type
1920 , l_exhibit_group_id);
1921 END IF;
1922 END IF;
1923
1924 /* Find the category at the end of the last reporting period */
1925 BEGIN
1926 SELECT ah.category_id
1927 INTO l_current_category_id
1928 FROM fa_asset_history ah,
1929 fa_deprn_periods dp
1930 WHERE ah.asset_id = asset_txns_rec.asset_id
1931 AND dp.book_type_code = p_corp_book
1932 AND dp.period_close_date between ah.date_effective and nvl(ah.date_ineffective,dp.period_close_date)
1933 AND dp.period_counter = p_period_counter_to;
1934 EXCEPTION WHEN OTHERS THEN
1935 l_current_category_id := 0;
1936 END;
1937 IF l_category_id = l_current_category_id THEN
1938 report_table(i).end_cost := l_adjusted_cost_end_period;
1939 report_table(i).accum_depr_rpt_period := l_depr_coefficient * l_depr_rpt_period;
1940 report_table(i).deprn_reserve := l_adj_accum_depr_end_period;
1941 ELSE
1942 report_table(i).end_cost := 0;
1943 report_table(i).accum_depr_rpt_period := 0;
1944 report_table(i).deprn_reserve := 0;
1945 END IF;
1946 report_table(i).conc_request_id := p_conc_request_id;
1947 report_table(i).exhibit_group_id := l_exhibit_group_id;
1948 report_table(i).category_id := asset_txns_rec.category_id;
1949 report_table(i).asset_id := asset_txns_rec.asset_id;
1950 report_table(i).additions := 0;
1951 report_table(i).retirements := 0;
1952 report_table(i).transfers := 0;
1953 report_table(i).accum_depr_retirements := 0;
1954 report_table(i).accum_depr_transfers := 0;
1955 END IF;
1956
1957 IF asset_txns_rec.transaction_type_code in ('ADDITION', 'CIP ADDITION') THEN
1958 IF asset_txns_rec.transaction_type_code in ('ADDITION') THEN
1959 /* Check if this ADDITION transaction has happened due to Capitalization */
1960 get_old_category ( asset_txns_rec.asset_id
1961 , asset_txns_rec.transaction_header_id
1962 , asset_txns_rec.transaction_type_code
1963 , l_old_category_id
1964 , j);
1965
1966 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1967 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_old_category_id := ' || to_char(l_old_category_id));
1968 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_old_category_id := ' || to_char(l_old_category_id));
1969 END IF;
1970
1971 IF l_old_category_id <> -1 THEN
1972
1973 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1974 FND_FILE.PUT_LINE(FND_FILE.LOG,'Regular Addition');
1975 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Regular Addition');
1976 END IF;
1977
1978 get_adjust_amount (asset_txns_rec.book_type_code
1979 , asset_txns_rec.asset_id
1980 , asset_txns_rec.period_counter
1981 , asset_txns_rec.transaction_type_code
1982 , 'COST'
1983 , l_adjustment
1984 , p_mrcsobtype
1985 );
1986
1987 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1988 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_adjustment := ' || to_char(l_adjustment));
1989 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_adjustment := ' || to_char(l_adjustment));
1990 END IF;
1991
1992 report_table(i).additions := report_table(i).additions + l_cost_coefficient * l_adjustment;
1993
1994 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1995 FND_FILE.PUT_LINE(FND_FILE.LOG,'additions := ' || to_char(report_table(i).additions));
1996 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'additions := ' || to_char(report_table(i).additions));
1997 END IF;
1998
1999 END IF;
2000 ELSIF asset_txns_rec.transaction_type_code in ('CIP ADDITION') THEN
2001 get_adjust_amount (asset_txns_rec.book_type_code
2002 , asset_txns_rec.asset_id
2003 , asset_txns_rec.period_counter
2004 , asset_txns_rec.transaction_type_code
2005 , 'CIP COST'
2006 , l_adjustment
2007 , p_mrcsobtype
2008 );
2009
2010 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2011 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_adjustment := ' || to_char(l_adjustment));
2012 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_adjustment := ' || to_char(l_adjustment));
2013 END IF;
2014
2015 report_table(i).additions := report_table(i).additions + l_cost_coefficient * l_adjustment;
2016
2017 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2018 FND_FILE.PUT_LINE(FND_FILE.LOG,'additions := ' || to_char(report_table(i).additions));
2019 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'additions := ' || to_char(report_table(i).additions));
2020 END IF;
2021 END IF;
2022 END IF;
2023
2024 IF asset_txns_rec.transaction_type_code in ('FULL RETIREMENT','PARTIAL RETIREMENT') THEN
2025
2026 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2027 FND_FILE.PUT_LINE(FND_FILE.LOG,'In FULL/PARTIAL RETIREMENT');
2028 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'In FULL/PARTIAL RETIREMENT');
2029 END IF;
2030
2031 /* Ignore the re-instated retirements */
2032 l_ignore_retirement := 'N';
2033 BEGIN
2034 SELECT 'Y'
2035 INTO l_ignore_retirement
2036 FROM fa_retirements ret
2037 WHERE ret.book_type_code = asset_txns_rec.book_type_code
2038 AND ret.asset_id = asset_txns_rec.asset_id
2039 AND ret.transaction_header_id_in = asset_txns_rec.transaction_header_id
2040 AND ret.status = 'DELETED'
2041 AND EXISTS (SELECT th.transaction_header_id
2042 FROM fa_transaction_headers th,
2043 fa_deprn_periods dp
2044 WHERE dp.book_type_code = asset_txns_rec.book_type_code
2045 AND dp.period_counter between p_period_counter_from and p_period_counter_to
2046 AND th.book_type_code = dp.book_type_code
2047 AND dp.period_open_date <= th.date_effective
2048 AND th.date_effective <= dp.period_close_date
2049 AND th.transaction_header_id = ret.transaction_header_id_out);
2050 EXCEPTION
2051 WHEN NO_DATA_FOUND THEN
2052 l_ignore_retirement := 'N';
2053 WHEN OTHERS THEN
2054 l_ignore_retirement := 'N';
2055 END;
2056 IF l_ignore_retirement = 'N' THEN
2057 get_adjust_amount (asset_txns_rec.book_type_code
2058 , asset_txns_rec.asset_id
2059 , asset_txns_rec.period_counter
2060 , asset_txns_rec.transaction_type_code
2061 , 'COST'
2062 , l_adjustment
2063 , p_mrcsobtype
2064 );
2065
2066 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2067 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_adjustment := ' || to_char(l_adjustment));
2068 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_adjustment := ' || to_char(l_adjustment));
2069 END IF;
2070
2071 report_table(i).retirements := report_table(i).retirements + l_cost_coefficient * l_adjustment;
2072
2073 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2074 FND_FILE.PUT_LINE(FND_FILE.LOG,'retirements := ' || to_char(report_table(i).retirements));
2075 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'retirements := ' || to_char(report_table(i).retirements));
2076 END IF;
2077
2078 get_adjust_amount (asset_txns_rec.book_type_code
2079 , asset_txns_rec.asset_id
2080 , asset_txns_rec.period_counter
2081 , asset_txns_rec.transaction_type_code
2082 , 'RESERVE'
2083 , l_adjustment
2084 , p_mrcsobtype
2085 );
2086
2087 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2088 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_adjustment := ' || to_char(l_adjustment));
2089 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_adjustment := ' || to_char(l_adjustment));
2090 END IF;
2091
2092 report_table(i).accum_depr_retirements := report_table(i).accum_depr_retirements - l_depr_coefficient * l_adjustment;
2093
2094 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2095 FND_FILE.PUT_LINE(FND_FILE.LOG,'accum_depr_retirements := ' || to_char(report_table(i).accum_depr_retirements));
2096 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'accum_depr_retirements := ' || to_char(report_table(i).accum_depr_retirements));
2097 END IF;
2098
2099 END IF;
2100 END IF;
2101
2102 IF asset_txns_rec.transaction_type_code in ('REINSTATEMENT') THEN
2103 /* Ignore the re-instatement if retirement is also considered in the reporting time frame */
2104 l_ignore_reinstatement := 'N';
2105 BEGIN
2106 SELECT 'Y'
2107 INTO l_ignore_reinstatement
2108 FROM fa_retirements ret
2109 WHERE ret.book_type_code = asset_txns_rec.book_type_code
2110 AND ret.asset_id = asset_txns_rec.asset_id
2111 AND ret.transaction_header_id_out = asset_txns_rec.transaction_header_id
2112 AND ret.status = 'DELETED'
2113 AND EXISTS (SELECT th.transaction_header_id
2114 FROM fa_transaction_headers th,
2115 fa_deprn_periods dp
2116 WHERE dp.book_type_code = asset_txns_rec.book_type_code
2117 AND dp.period_counter between p_period_counter_from and p_period_counter_to
2118 AND th.book_type_code = dp.book_type_code
2119 AND dp.period_open_date <= th.date_effective
2120 AND th.date_effective <= dp.period_close_date
2121 AND th.transaction_header_id = ret.transaction_header_id_in);
2122 EXCEPTION
2123 WHEN NO_DATA_FOUND THEN
2124 l_ignore_reinstatement := 'N';
2125 WHEN OTHERS THEN
2126 l_ignore_reinstatement := 'N';
2127 END;
2128 IF l_ignore_reinstatement = 'N' THEN
2129 get_adjust_amount (asset_txns_rec.book_type_code
2130 , asset_txns_rec.asset_id
2131 , asset_txns_rec.period_counter
2132 , asset_txns_rec.transaction_type_code
2133 , 'COST'
2134 , l_adjustment
2135 , p_mrcsobtype
2136 );
2137
2138 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2139 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_adjustment := ' || to_char(l_adjustment));
2140 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_adjustment := ' || to_char(l_adjustment));
2141 END IF;
2142
2143 report_table(i).retirements := report_table(i).retirements - l_cost_coefficient * l_adjustment;
2144
2145 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2146 FND_FILE.PUT_LINE(FND_FILE.LOG,'retirements := ' || to_char(report_table(i).retirements));
2147 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'retirements := ' || to_char(report_table(i).retirements));
2148 END IF;
2149
2150 get_adjust_amount (asset_txns_rec.book_type_code
2151 , asset_txns_rec.asset_id
2152 , asset_txns_rec.period_counter
2153 , asset_txns_rec.transaction_type_code
2154 , 'RESERVE'
2155 , l_adjustment
2156 , p_mrcsobtype
2157 );
2158
2159 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2160 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_adjustment := ' || to_char(l_adjustment));
2161 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_adjustment := ' || to_char(l_adjustment));
2162 END IF;
2163
2164 report_table(i).accum_depr_retirements := report_table(i).accum_depr_retirements + l_depr_coefficient * l_adjustment;
2165
2166 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2167 FND_FILE.PUT_LINE(FND_FILE.LOG,'accum_depr_retirements := ' || to_char(report_table(i).accum_depr_retirements));
2168 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'accum_depr_retirements := ' || to_char(report_table(i).accum_depr_retirements));
2169 END IF;
2170
2171 END IF;
2172 END IF;
2173
2174 IF ((asset_txns_rec.transaction_type_code in ('RECLASS','CIP RECLASS')) OR
2175 (asset_txns_rec.transaction_type_code = 'ADDITION' AND l_old_category_id = -1)) THEN
2176
2177 IF (asset_txns_rec.transaction_type_code = 'ADDITION' AND l_old_category_id = -1) THEN
2178
2179 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2180 FND_FILE.PUT_LINE(FND_FILE.LOG,'Capitalization of the asset');
2181 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Capitalization of the asset');
2182 END IF;
2183
2184 i := i + 1;
2185
2186 get_exhibit_group ( asset_txns_rec.category_id
2187 , asset_txns_rec.asset_id
2188 , p_corp_book
2189 , asset_txns_rec.asset_type
2190 , l_exhibit_group_id);
2191
2192 report_table(i).conc_request_id := p_conc_request_id;
2193 report_table(i).exhibit_group_id := l_exhibit_group_id;
2194 report_table(i).category_id := asset_txns_rec.category_id;
2195 report_table(i).asset_id := asset_txns_rec.asset_id;
2196 report_table(i).begin_cost := 0;
2197 report_table(i).additions := 0;
2198 report_table(i).retirements := 0;
2199 report_table(i).transfers := 0;
2200 report_table(i).begin_accum_depr := 0;
2201 report_table(i).accum_depr_retirements := 0;
2202 report_table(i).accum_depr_transfers := 0;
2203 /* Since the asset is capitalized, we need to move the end cost to 'CAPITALIZED' row
2204 and set the end cost of CIP row for the asset to zero */
2205 report_table(i).end_cost := report_table(j).end_cost;
2206 report_table(i).accum_depr_rpt_period := report_table(j).accum_depr_rpt_period;
2207 report_table(i).deprn_reserve := report_table(j).deprn_reserve;
2208
2209 report_table(j).end_cost := 0;
2210 report_table(j).accum_depr_rpt_period := 0;
2211 report_table(j).deprn_reserve := 0;
2212 ELSE
2213 get_old_category ( asset_txns_rec.asset_id
2214 , asset_txns_rec.transaction_header_id
2215 , asset_txns_rec.transaction_type_code
2216 , l_old_category_id
2217 , j);
2218 END IF;
2219 get_adjust_amount (asset_txns_rec.book_type_code
2220 , asset_txns_rec.asset_id
2221 , asset_txns_rec.period_counter
2222 , asset_txns_rec.transaction_type_code
2223 , 'COST'
2224 , l_adjustment
2225 , p_mrcsobtype
2226 );
2227
2228 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2229 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_adjustment := ' || to_char(l_adjustment));
2230 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_adjustment := ' || to_char(l_adjustment));
2231 FND_FILE.PUT_LINE(FND_FILE.LOG,'j := ' || to_char(j));
2232 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'j := ' || to_char(j));
2233 FND_FILE.PUT_LINE(FND_FILE.LOG,'i := ' || to_char(i));
2234 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'i := ' || to_char(i));
2235 END IF;
2236
2237 report_table(i).transfers := report_table(i).transfers + l_cost_coefficient * l_adjustment;
2238
2239 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2240 FND_FILE.PUT_LINE(FND_FILE.LOG,'transfers To := ' || to_char(report_table(i).transfers));
2241 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'transfers To := ' || to_char(report_table(i).transfers));
2242 END IF;
2243
2244 report_table(j).transfers := report_table(j).transfers - l_cost_coefficient * l_adjustment;
2245
2246 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2247 FND_FILE.PUT_LINE(FND_FILE.LOG,'transfers From := ' || to_char(report_table(j).transfers));
2248 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'transfers From := ' || to_char(report_table(j).transfers));
2249 END IF;
2250
2251 IF report_table(i).begin_accum_depr > 0 THEN
2252 get_adjust_amount (asset_txns_rec.book_type_code
2253 , asset_txns_rec.asset_id
2254 , asset_txns_rec.period_counter
2255 , asset_txns_rec.transaction_type_code
2256 , 'RESERVE'
2257 , l_adjustment
2258 , p_mrcsobtype
2259 );
2260
2261 report_table(i).accum_depr_transfers := report_table(i).accum_depr_transfers + (l_depr_coefficient * l_adjustment);
2262
2263 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2264 FND_FILE.PUT_LINE(FND_FILE.LOG,'accum_depr_transfers := ' || to_char(report_table(i).accum_depr_transfers));
2265 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'accum_depr_transfers := ' || to_char(report_table(i).accum_depr_transfers));
2266 END IF;
2267
2268 report_table(j).accum_depr_transfers := report_table(j).accum_depr_transfers - (l_depr_coefficient * l_adjustment);
2269 END IF;
2270 IF (asset_txns_rec.transaction_type_code = 'ADDITION' AND l_old_category_id = -1) THEN
2271
2272 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2273 FND_FILE.PUT_LINE(FND_FILE.LOG,'Capitalization of the asset ends');
2274 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Capitalization of the asset ends');
2275 END IF;
2276
2277 END IF;
2278 END IF;
2279
2280 IF asset_txns_rec.transaction_type_code in ('ADJUSTMENT', 'CIP ADJUSTMENT') THEN
2281 -----------------------------------------------------------------------------------------------------------
2282 -- BUG 4856193. Logic has been added to avoid looping throguh all the rows in FA_TRANSACTION_HEADERS for
2283 -- multiple Adjustments. An asset can have multiple adjustments for the same period and book.
2284 -- The function get_adjust_amount already returns the summary of all the adjustments for a
2285 -- given period. So no need to summarize again for each adjustment row.
2286 -----------------------------------------------------------------------------------------------------------
2287
2288 IF asset_txns_rec.book_type_code = l_book_type_code_old AND
2289 asset_txns_rec.asset_id = l_asset_id_old AND
2290 asset_txns_rec.period_counter = l_period_counter_old AND
2291 asset_txns_rec.transaction_type_code = l_transaction_type_code_old
2292 THEN
2293 l_adj_already_calculated_flag := 'Y';
2294 ELSE
2295 l_adj_already_calculated_flag := 'N';
2296 l_book_type_code_old := asset_txns_rec.book_type_code;
2297 l_asset_id_old := asset_txns_rec.asset_id;
2298 l_period_counter_old := asset_txns_rec.period_counter;
2299 l_transaction_type_code_old := asset_txns_rec.transaction_type_code;
2300 END IF;
2301
2302 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2303 FND_FILE.PUT_LINE(FND_FILE.LOG,'Bug 4956193: Adjustment calculated flag:'||l_adj_already_calculated_flag);
2304 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Bug 4956193: Adjustment calculated flag:'||l_adj_already_calculated_flag);
2305 END IF;
2306
2307 -----------------------------------------------------------------------------
2308 -- Bug 4856193. Only if Adjustment has not been calculated yet, calculate it.
2309 -----------------------------------------------------------------------------
2310 IF l_adj_already_calculated_flag = 'N' THEN
2311 IF asset_txns_rec.transaction_type_code in ('ADJUSTMENT') THEN
2312 get_adjust_amount ( asset_txns_rec.book_type_code
2313 , asset_txns_rec.asset_id
2314 , asset_txns_rec.period_counter
2315 , asset_txns_rec.transaction_type_code
2316 , 'COST'
2317 , l_adjustment
2318 , p_mrcsobtype
2319 );
2320 ELSIF asset_txns_rec.transaction_type_code in ('CIP ADJUSTMENT') THEN
2321 get_adjust_amount ( asset_txns_rec.book_type_code
2322 , asset_txns_rec.asset_id
2323 , asset_txns_rec.period_counter
2324 , asset_txns_rec.transaction_type_code
2325 , 'CIP COST'
2326 , l_adjustment
2327 , p_mrcsobtype
2328 );
2329 END IF;
2330
2331 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2332 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_adjustment := ' || to_char(l_adjustment));
2333 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_adjustment := ' || to_char(l_adjustment));
2334 END IF;
2335
2336 IF l_adjustment > 0 THEN
2337 report_table(i).additions := report_table(i).additions + l_cost_coefficient * l_adjustment;
2338
2339 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2340 FND_FILE.PUT_LINE(FND_FILE.LOG,'Addition adjustments := ' || to_char(report_table(i).additions));
2341 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Addition adjustments := ' || to_char(report_table(i).additions));
2342 END IF;
2343
2344 ELSE
2345 report_table(i).retirements := report_table(i).retirements - l_cost_coefficient * l_adjustment;
2346
2347 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2348 FND_FILE.PUT_LINE(FND_FILE.LOG,'Retirement adjustments := ' || to_char(report_table(i).retirements));
2349 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Retirement adjustments := ' || to_char(report_table(i).retirements));
2350 END IF;
2351
2352 END IF;
2353
2354 get_adjust_amount (asset_txns_rec.book_type_code
2355 , asset_txns_rec.asset_id
2356 , asset_txns_rec.period_counter
2357 , asset_txns_rec.transaction_type_code
2358 , 'EXPENSE'
2359 , l_adjustment
2360 , p_mrcsobtype
2361 );
2362
2363 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2364 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_adjustment := ' || to_char(l_adjustment));
2365 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'l_adjustment := ' || to_char(l_adjustment));
2366 END IF;
2367
2368 IF l_adjustment < 0 THEN
2369 report_table(i).accum_depr_retirements := report_table(i).accum_depr_retirements + (l_depr_coefficient * l_adjustment);
2370
2371 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2372 FND_FILE.PUT_LINE(FND_FILE.LOG,'accum_depr_retirements := ' || to_char(report_table(i).accum_depr_retirements));
2373 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'accum_depr_retirements := ' || to_char(report_table(i).accum_depr_retirements));
2374 END IF;
2375
2376 END IF;
2377 END IF;
2378 END IF;
2379
2380 END LOOP;
2381 insert_db_records;
2382
2383 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2384 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2385 END IF;
2386
2387 END Populate_FA_Exhibit_Data;
2388
2389 END jl_zz_fa_functions_pkg;