[Home] [Help]
PACKAGE BODY: APPS.FA_SORP_UPG_PKG
Source
1 PACKAGE BODY fa_sorp_upg_pkg AS
2 /* $Header: FAVSRUB.pls 120.4.12020000.2 2012/09/10 08:53:11 gigupta ship $ */
3
4 -- this fucntion determines if a book to be upgraded is MRC enabled or not
5 function fa_sorp_upg_mc_flag(p_book_type_code varchar2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
6 return boolean
7 is
8 v_mc_source_flag varchar2(5);
9 cursor c_mc_cur is
10 select nvl(MC_SOURCE_FLAG,'N') from FA_BOOK_CONTROLS
11 where book_type_code = p_book_type_code;
12 begin
13 open c_mc_cur;
14 fetch c_mc_cur into v_mc_source_flag;
15 close c_mc_cur;
16
17 if v_mc_source_flag = 'Y' then
18 return true;
19 else
20 return false;
21 end if;
22
23 end fa_sorp_upg_mc_flag;
24
25 FUNCTION get_ccid (p_acct_flex_struct NUMBER, p_account VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
26 RETURN NUMBER
27 IS
28 CURSOR c_ccid_cur
29 IS
30 SELECT code_combination_id
31 FROM gl_code_combinations_kfv
32 WHERE chart_of_accounts_id = p_acct_flex_struct
33 AND concatenated_segments = p_account;
34
35 v_ccid NUMBER;
36 BEGIN
37 OPEN c_ccid_cur;
38
39 FETCH c_ccid_cur
40 INTO v_ccid;
41
42 CLOSE c_ccid_cur;
43
44 RETURN v_ccid;
45 END get_ccid;
46
47 FUNCTION get_account_seg (p_acct_flex_struct NUMBER, p_ccid NUMBER, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
48 RETURN VARCHAR2
49 IS
50 CURSOR c_acct_seg_cur
51 IS
52 SELECT application_column_name
53 FROM fnd_segment_attribute_values fndsav
54 WHERE fndsav.id_flex_code = 'GL#'
55 AND fndsav.segment_attribute_type = 'GL_ACCOUNT'
56 AND fndsav.attribute_value = 'Y'
57 AND application_id = 101
58 AND fndsav.id_flex_num = p_acct_flex_struct;
59
60 v_appl_col_name VARCHAR2 (25);
61 l_string VARCHAR2 (4000);
62 v_acct VARCHAR2(25);
63 BEGIN
64 OPEN c_acct_seg_cur;
65
66 FETCH c_acct_seg_cur
67 INTO v_appl_col_name;
68
69 CLOSE c_acct_seg_cur;
70
71 l_string :=
72 'SELECT '
73 || v_appl_col_name
74 || ' from GL_CODE_COMBINATIONS_KFV where chart_of_accounts_id = '
75 || p_acct_flex_struct
76 || ' and code_combination_id = '
77 || p_ccid;
78
79 EXECUTE IMMEDIATE l_string
80 INTO v_acct;
81
82 RETURN v_acct;
83 END get_account_seg;
84
85 FUNCTION get_flex_struct (p_flex_name VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
86 RETURN NUMBER
87 IS
88 v_cat_struct_id NUMBER;
89 BEGIN
90 IF p_flex_name = 'CAT'
91 THEN
92 SELECT category_flex_structure
93 INTO v_cat_struct_id
94 FROM fa_system_controls;
95
96 RETURN (v_cat_struct_id);
97 END IF;
98 END get_flex_struct;
99
100 FUNCTION get_cat_flex (p_category_id NUMBER, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
101 RETURN VARCHAR2
102 IS
103 v_category_name VARCHAR2 (1000);
104
105 CURSOR c_cat_cur
106 IS
107 SELECT DISTINCT attribute_category_code
108 FROM fa_additions_v
109 WHERE asset_category_id = p_category_id;
110 BEGIN
111 OPEN c_cat_cur;
112
113 FETCH c_cat_cur
114 INTO v_category_name;
115
116 CLOSE c_cat_cur;
117
118 RETURN v_category_name;
119 END get_cat_flex;
120
121 function get_impairment_sorp_values(
122 p_book_type_code IN VARCHAR2,
123 p_asset_id IN NUMBER,
124 p_dist_id IN NUMBER,
125 p_period_counter IN NUMBER,
126 p_mode varchar2,
127 px_capital_adj IN OUT NOCOPY NUMBER,
128 px_general_fund IN OUT NOCOPY NUMBER
129 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN
130 IS
131 cursor c_deprn_detail_cur is
132 select itf.asset_id,
133 itf.impairment_id,
134 th.transaction_header_id,
135 adj.adjustment_type,
136 nvl(decode(adj.debit_credit_flag,'DR',(-1*adj.adjustment_amount)),0) adjustment_amount,
137 det.distribution_id
138 from
139 fa_itf_impairments itf,
140 fa_impairments imp,
141 fa_transaction_headers th,
142 fa_adjustments adj,
143 fa_deprn_detail det
144 where itf.impairment_id = imp.impairment_id
145 and itf.impairment_id = th.mass_transaction_id
146 and th.transaction_header_id = adj.transaction_header_id
147 and adj.distribution_id = det.distribution_id
148 and det.period_counter = p_period_counter
149 and det.period_counter = itf.period_counter
150 and det.asset_id = itf.asset_id
151 and itf.asset_id = p_asset_id
152 and det.book_type_code = p_book_type_code
153 and imp.status = 'POSTED'
154 and adj.adjustment_type = 'REVAL RESERVE'
155 and adj.source_type_code = 'ADJUSTMENT'
156 and substr(imp.description,1,3) = 'CEB'
157 and det.distribution_id = p_dist_id;
158
159 v_deprn_detail_cur c_deprn_detail_cur%rowtype;
160
161 cursor c_deprn_summary_cur is
162 select itf.asset_id,
163 itf.impairment_id,
164 th.transaction_header_id,
165 adj.adjustment_type,
166 nvl(decode(adj.debit_credit_flag,'DR',(-1*adj.adjustment_amount)),0) adjustment_amount
167 from
168 fa_itf_impairments itf,
169 fa_impairments imp,
170 fa_transaction_headers th,
171 fa_adjustments adj,
172 fa_deprn_summary su
173 where itf.impairment_id = imp.impairment_id
174 and itf.impairment_id = th.mass_transaction_id
175 and th.transaction_header_id = adj.transaction_header_id
176 and su.period_counter = itf.period_counter
177 and itf.asset_id = su.asset_id
178 and su.period_counter = p_period_counter
179 and itf.asset_id = p_asset_id
180 and su.book_type_code = p_book_type_code
181 and imp.status = 'POSTED'
182 and adj.adjustment_type = 'REVAL RESERVE'
183 and adj.source_type_code = 'ADJUSTMENT'
184 and substr(imp.description,1,3) = 'CEB';
185
186 v_deprn_summary_cur c_deprn_summary_cur%rowtype;
187
188 begin
189
190 if p_mode = 'D' then
191
192 open c_deprn_detail_cur;
193 fetch c_deprn_detail_cur into v_deprn_detail_cur;
194
195 if c_deprn_detail_cur%rowcount <> 0 then
196
197 px_capital_adj := v_deprn_detail_cur.adjustment_amount;
198 px_general_fund := 0;
199
200 else
201
202 px_capital_adj := 0;
203 px_general_fund := 0;
204 end if;
205
206 close c_deprn_detail_cur;
207
208 else
209
210 open c_deprn_summary_cur;
211 fetch c_deprn_summary_cur into v_deprn_summary_cur;
212
213 if c_deprn_summary_cur%rowcount <>0 then
214
215 px_capital_adj := v_deprn_summary_cur.adjustment_amount;
216 px_general_fund := 0;
217
218 else
219
220 px_capital_adj := 0;
221 px_general_fund := 0;
222 end if;
223
224 close c_deprn_summary_cur;
225
226 end if;
227
228 return true;
229 exception when others then
230 return false;
231
232 end get_impairment_sorp_values;
233
234 function get_impairment_sorp_mc(
235 p_book_type_code IN VARCHAR2,
236 p_asset_id IN NUMBER,
237 p_dist_id IN NUMBER,
238 p_period_counter IN NUMBER,
239 p_mode varchar2,
240 px_capital_adj IN OUT NOCOPY NUMBER,
241 px_general_fund IN OUT NOCOPY NUMBER,
242 p_set_of_books_id IN NUMBER
243 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN
244 IS
245 cursor c_deprn_detail_cur is
246 select itf.asset_id,
247 itf.impairment_id,
248 th.transaction_header_id,
249 adj.adjustment_type,
250 nvl(decode(adj.debit_credit_flag,'DR',(-1*adj.adjustment_amount)),0) adjustment_amount,
251 det.distribution_id
252 from
253 FA_MC_ITF_IMPAIRMENTS itf,
254 FA_MC_IMPAIRMENTS imp,
255 fa_transaction_headers th,
256 fa_adjustments adj,
257 FA_MC_DEPRN_DETAIL det
258 where itf.impairment_id = imp.impairment_id
259 and itf.impairment_id = th.mass_transaction_id
260 and th.transaction_header_id = adj.transaction_header_id
261 and adj.distribution_id = det.distribution_id
262 and det.period_counter = p_period_counter
263 and det.period_counter = itf.period_counter
264 and det.asset_id = itf.asset_id
265 and itf.asset_id = p_asset_id
266 and det.book_type_code = p_book_type_code
267 and imp.status = 'POSTED'
268 and adj.adjustment_type = 'REVAL RESERVE'
269 and adj.source_type_code = 'ADJUSTMENT'
270 and substr(imp.description,1,3) = 'CEB'
271 and det.distribution_id = p_dist_id
272 and det.set_of_books_id = NVL(p_set_of_books_id,det.set_of_books_id);
273
274 v_deprn_detail_cur c_deprn_detail_cur%rowtype;
275
276 cursor c_deprn_summary_cur is
277 select itf.asset_id,
278 itf.impairment_id,
279 th.transaction_header_id,
280 adj.adjustment_type,
281 nvl(decode(adj.debit_credit_flag,'DR',(-1*adj.adjustment_amount)),0) adjustment_amount
282 from
283 FA_MC_ITF_IMPAIRMENTS itf,
284 FA_MC_IMPAIRMENTS imp,
285 fa_transaction_headers th,
286 fa_adjustments adj,
287 FA_MC_DEPRN_SUMMARY su
288 where itf.impairment_id = imp.impairment_id
289 and itf.impairment_id = th.mass_transaction_id
290 and th.transaction_header_id = adj.transaction_header_id
291 and su.period_counter = itf.period_counter
292 and itf.asset_id = su.asset_id
293 and su.period_counter = p_period_counter
294 and itf.asset_id = p_asset_id
295 and su.book_type_code = p_book_type_code
296 and imp.status = 'POSTED'
297 and adj.adjustment_type = 'REVAL RESERVE'
298 and adj.source_type_code = 'ADJUSTMENT'
299 and substr(imp.description,1,3) = 'CEB'
300 and su.set_of_books_id = NVL(p_set_of_books_id,su.set_of_books_id);
301
302 v_deprn_summary_cur c_deprn_summary_cur%rowtype;
303
304 begin
305
306 if p_mode = 'D' then
307
308 open c_deprn_detail_cur;
309 fetch c_deprn_detail_cur into v_deprn_detail_cur;
310
311 if c_deprn_detail_cur%rowcount <> 0 then
312
313 px_capital_adj := v_deprn_detail_cur.adjustment_amount;
314 px_general_fund := 0;
315
316 else
317
318 px_capital_adj := 0;
319 px_general_fund := 0;
320 end if;
321
322 close c_deprn_detail_cur;
323
324 else
325
326 open c_deprn_summary_cur;
327 fetch c_deprn_summary_cur into v_deprn_summary_cur;
328
329 if c_deprn_summary_cur%rowcount <>0 then
330
331 px_capital_adj := v_deprn_summary_cur.adjustment_amount;
332 px_general_fund := 0;
333
334 else
335
336 px_capital_adj := 0;
337 px_general_fund := 0;
338 end if;
339
340 close c_deprn_summary_cur;
341
342 end if;
343
344 return true;
345 exception when others then
346 return false;
347
348 end get_impairment_sorp_mc;
349
350 FUNCTION get_retirement_sorp_values(
351 p_book_type_code IN VARCHAR2,
352 p_asset_id IN NUMBER,
353 p_dist_id IN NUMBER,
354 p_period_counter IN NUMBER,
355 px_capital_adj IN OUT NOCOPY NUMBER,
356 px_general_fund IN OUT NOCOPY NUMBER
357 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN
358 IS
359 l_reval_reserve_ca NUMBER;
360 l_nbv_retired_ca NUMBER;
361 l_nbv_retired_gf NUMBER;
362 l_old_dist_ca NUMBER;
363 l_old_dist_gf NUMBER;
364 BEGIN
365 px_capital_adj := 0;
366 px_general_fund := 0;
367 l_reval_reserve_ca := 0;
368 l_nbv_retired_ca := 0;
369 l_nbv_retired_gf := 0;
370 l_old_dist_ca := 0;
371 l_old_dist_gf := 0;
372 BEGIN
373 select NVL(SUM(DECODE(DEBIT_CREDIT_FLAG,'DR',-1 * ADJUSTMENT_AMOUNT,ADJUSTMENT_AMOUNT)),0)
374 into l_reval_reserve_ca
375 from fa_adjustments
376 where asset_id = p_asset_id
377 and distribution_id = nvl(p_dist_id,distribution_id)
378 and period_counter_created = p_period_counter
379 and book_type_code = p_book_type_code
380 and source_type_code = 'RETIREMENT'
381 and adjustment_type = 'REVAL RESERVE';
382 EXCEPTION
383 WHEN NO_DATA_FOUND THEN
384 l_reval_reserve_ca := 0;
385 WHEN OTHERS THEN
386 RETURN FALSE;
387 END;
388
389
390
391
392 BEGIN
393 select NVL(SUM(DECODE(DEBIT_CREDIT_FLAG,'DR',ADJUSTMENT_AMOUNT,-1 * ADJUSTMENT_AMOUNT)),0)
394 into l_nbv_retired_ca
395 from fa_adjustments
396 where asset_id = p_asset_id
397 and distribution_id = nvl(p_dist_id,distribution_id)
398 and period_counter_created = p_period_counter
399 and book_type_code = p_book_type_code
400 and source_type_code = 'RETIREMENT'
401 and adjustment_type = 'NBV RETIRED';
402 EXCEPTION
403 WHEN NO_DATA_FOUND THEN
404 l_nbv_retired_ca := 0;
405 WHEN OTHERS THEN
406 RETURN FALSE;
407 END;
408
409
410 /*8246943 - Value will be same for capital adjustment and general fund*/
411 l_nbv_retired_gf := l_nbv_retired_ca;
412
413
414 IF p_dist_id IS NOT NULL THEN
415 BEGIN
416 SELECT
417 sum(nvl(capital_adjustment, 0)),
418 sum(nvl(general_fund, 0))
419 INTO
420 l_old_dist_ca,
421 l_old_dist_gf
422 FROM FA_DISTRIBUTION_HISTORY dh_old,
423 FA_DEPRN_DETAIL sumold
424 WHERE dh_old.distribution_id = sumold.distribution_id
425 AND dh_old.book_type_code = sumold.book_type_code
426 AND dh_old.asset_id = sumold.asset_id
427 ANd dh_old.retirement_id is not null
428 AND EXISTS
429 (SELECT 1
430 FROM FA_DISTRIBUTION_HISTORY dh_new
431 WHERE dh_new.distribution_id = p_dist_id
432 AND dh_new.asset_id = dh_old.asset_id
433 AND dh_new.transaction_header_id_in = dh_old.transaction_header_id_out
434 AND dh_new.location_id = dh_old.location_id
435 AND nvl(dh_new.assigned_to, -99) = nvl(dh_old.assigned_to, -99)
436 AND dh_new.code_combination_id = dh_old.code_combination_id
437 AND dh_new.book_type_code = dh_old.book_type_code
438 )
439 AND sumold.period_counter =
440 (SELECT MAX(period_counter)
441 FROM FA_DEPRN_DETAIL
442 WHERE book_type_code = p_book_type_code
443 AND asset_id = p_asset_id
444 AND distribution_id = p_dist_id
445 AND period_counter < p_period_counter);
446 EXCEPTION
447 WHEN NO_DATA_FOUND THEN
448 l_old_dist_ca := 0;
449 l_old_dist_gf := 0;
450 END;
451 ELSE
452 l_old_dist_ca := 0;
453 l_old_dist_gf := 0;
454 END IF;
455
456 /*Bug#8246943 - placed nvl function around variables*/
457 px_capital_adj := nvl(l_nbv_retired_ca,0) + nvl(l_reval_reserve_ca,0) + nvl(l_old_dist_ca,0);
458 px_general_fund := nvl(l_nbv_retired_gf,0) + nvl(l_old_dist_gf,0);
459 RETURN TRUE;
460 EXCEPTION
461 WHEN OTHERS THEN
462 RETURN FALSE;
463 END get_retirement_sorp_values;
464
465
466 FUNCTION get_retirement_sorp_mc(
467 p_book_type_code IN VARCHAR2,
468 p_asset_id IN NUMBER,
469 p_dist_id IN NUMBER,
470 p_period_counter IN NUMBER,
471 px_capital_adj IN OUT NOCOPY NUMBER,
472 px_general_fund IN OUT NOCOPY NUMBER,
473 p_set_of_books_id IN NUMBER
474 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN
475 IS
476 l_reval_reserve_ca NUMBER;
477 l_nbv_retired_ca NUMBER;
478 l_nbv_retired_gf NUMBER;
479 l_old_dist_ca NUMBER;
480 l_old_dist_gf NUMBER;
481 BEGIN
482 px_capital_adj := 0;
483 px_general_fund := 0;
484 l_reval_reserve_ca := 0;
485 l_nbv_retired_ca := 0;
486 l_nbv_retired_gf := 0;
487 l_old_dist_ca := 0;
488 l_old_dist_gf := 0;
489 BEGIN
490 select NVL(SUM(DECODE(DEBIT_CREDIT_FLAG,'DR',-1 * ADJUSTMENT_AMOUNT,ADJUSTMENT_AMOUNT)),0)
491 into l_reval_reserve_ca
492 from fa_mc_adjustments
493 where asset_id = p_asset_id
494 and distribution_id = nvl(p_dist_id,distribution_id)
495 and period_counter_created = p_period_counter
496 and book_type_code = p_book_type_code
497 and source_type_code = 'RETIREMENT'
498 and adjustment_type = 'REVAL RESERVE'
499 and set_of_books_id = p_set_of_books_id;
500 EXCEPTION
501 WHEN NO_DATA_FOUND THEN
502 l_reval_reserve_ca := 0;
503 WHEN OTHERS THEN
504 RETURN FALSE;
505 END;
506
507
508 BEGIN
509 select NVL(SUM(DECODE(DEBIT_CREDIT_FLAG,'DR',ADJUSTMENT_AMOUNT,-1 * ADJUSTMENT_AMOUNT)),0)
510 into l_nbv_retired_ca
511 from fa_mc_adjustments
512 where asset_id = p_asset_id
513 and distribution_id = nvl(p_dist_id,distribution_id)
514 and period_counter_created = p_period_counter
515 and book_type_code = p_book_type_code
516 and source_type_code = 'RETIREMENT'
517 and adjustment_type = 'NBV RETIRED'
518 and set_of_books_id = p_set_of_books_id;
519 EXCEPTION
520 WHEN NO_DATA_FOUND THEN
521 l_nbv_retired_ca := 0;
522 WHEN OTHERS THEN
523 RETURN FALSE;
524 END;
525
526
527 /*8246943 - Value will be same for capital adjustment and general fund*/
528 l_nbv_retired_gf := l_nbv_retired_ca;
529
530
531 IF p_dist_id IS NOT NULL THEN
532
533 BEGIN
534 SELECT
535 sum(nvl(capital_adjustment, 0)),
536 sum(nvl(general_fund, 0))
537 INTO
538 l_old_dist_ca,
539 l_old_dist_gf
540 FROM FA_DISTRIBUTION_HISTORY dh_old,
541 FA_MC_DEPRN_DETAIL sumold
542 WHERE dh_old.distribution_id = sumold.distribution_id
543 AND dh_old.book_type_code = sumold.book_type_code
544 AND dh_old.asset_id = sumold.asset_id
545 ANd dh_old.retirement_id is not null
546 AND sumold.set_of_books_id = p_set_of_books_id
547 AND EXISTS
548 (SELECT 1
549 FROM FA_DISTRIBUTION_HISTORY dh_new
550 WHERE dh_new.distribution_id = p_dist_id
551 AND dh_new.asset_id = dh_old.asset_id
552 AND dh_new.transaction_header_id_in = dh_old.transaction_header_id_out
553 AND dh_new.location_id = dh_old.location_id
554 AND nvl(dh_new.assigned_to, -99) = nvl(dh_old.assigned_to, -99)
555 AND dh_new.code_combination_id = dh_old.code_combination_id
556 AND dh_new.book_type_code = dh_old.book_type_code
557 )
558 AND sumold.period_counter =
559 (SELECT MAX(period_counter)
560 FROM FA_MC_DEPRN_DETAIL
561 WHERE book_type_code = p_book_type_code
562 AND asset_id = p_asset_id
563 AND distribution_id = p_dist_id
564 AND period_counter < p_period_counter
565 AND set_of_books_id=p_set_of_books_id);
566 EXCEPTION
567 WHEN NO_DATA_FOUND THEN
568 l_old_dist_ca := 0;
569 l_old_dist_gf := 0;
570 END;
571 ELSE
572 l_old_dist_ca := 0;
573 l_old_dist_gf := 0;
574 END IF;
575
576
577 /*Bug#8246943 - placed nvl function around variables*/
578 px_capital_adj := nvl(l_nbv_retired_ca,0) + nvl(l_reval_reserve_ca,0) + nvl(l_old_dist_ca,0);
579 px_general_fund := nvl(l_nbv_retired_gf,0) + nvl(l_old_dist_gf,0);
580 RETURN TRUE;
581 EXCEPTION
582 WHEN OTHERS THEN
583 RETURN FALSE;
584 END get_retirement_sorp_mc;
585
586
587 FUNCTION fa_category_impl (
588 p_book fa_books.book_type_code%TYPE,
589 p_acct_flex_struct NUMBER,
590 p_capital_adj_acct VARCHAR2,
591 p_general_fund_acct VARCHAR2,
592 p_run_mode VARCHAR2
593 )
594 RETURN BOOLEAN
595 IS
596 CURSOR c_category_cur
597 IS
598 SELECT book_type_code, category_id, reval_amortization_acct,
599 reval_amort_account_ccid, impair_expense_acct,
600 impair_expense_account_ccid, impair_reserve_acct,
601 impair_reserve_account_ccid
602 FROM fa_category_books
603 WHERE book_type_code = p_book
604 AND (capital_adj_acct IS NULL OR general_fund_acct IS NULL);
605
606 --type v_category_cur is c_category_cur%rowtype;
607 TYPE v_category_tab_type IS TABLE OF c_category_cur%ROWTYPE;
608
609 v_category_tab v_category_tab_type;
610 l_imp_acct_chk_flag VARCHAR2 (1) := 'I';
611 l_reval_amort_acct_chk_flag VARCHAR2 (1) := 'R';
612 l_success_chk_flag VARCHAR2 (1) := 'Y';
613 v_capital_adj_acct VARCHAR2 (25);
614 v_capital_adj_ccid NUMBER;
615 v_general_fund_acct VARCHAR2 (25);
616 v_general_fund_ccid NUMBER;
617
618 CURSOR c_final_cur
619 IS
620 SELECT category_id, capital_adj_acct, capital_adj_account_ccid,
621 general_fund_acct, general_fund_account_ccid
622 FROM fa_sorp_upg_cat
623 WHERE book_type_code = p_book AND validation_flag <> 'I';
624
625 v_final_cur c_final_cur%ROWTYPE;
626 l_cat_struct NUMBER;
627 l_category_name VARCHAR2 (1000);
628 p_error_code VARCHAR2 (100);
629 p_status_msg VARCHAR2 (100);
630 BEGIN
631 p_error_code := 0;
632 p_status_msg := 'SUCCESS';
633 -- Get Capital Adjustment CCID and Account
634 v_capital_adj_ccid := get_ccid (p_acct_flex_struct, p_capital_adj_acct);
635 v_capital_adj_acct :=
636 get_account_seg (p_acct_flex_struct, v_capital_adj_ccid);
637 -- Get General Fund CCID and Account
638 v_general_fund_ccid :=
639 get_ccid (p_acct_flex_struct, p_general_fund_acct);
640 v_general_fund_acct :=
641 get_account_seg (p_acct_flex_struct, v_general_fund_ccid);
642 --Get category flex structure
643 l_cat_struct := get_flex_struct ('CAT');
644
645 DELETE FROM fa_sorp_upg_cat;
646
647 COMMIT;
648
649 OPEN c_category_cur;
650
651 FETCH c_category_cur
652 BULK COLLECT INTO v_category_tab;
653
654 IF c_category_cur%ROWCOUNT = 0
655 THEN
656 RETURN TRUE;
657 END IF;
658
659 CLOSE c_category_cur;
660
661 FOR i IN v_category_tab.FIRST .. v_category_tab.LAST
662 LOOP
663 -- get category name
664 l_category_name := get_cat_flex (v_category_tab (i).category_id);
665
666 IF v_category_tab (i).impair_expense_acct IS NULL
667 OR v_category_tab (i).impair_reserve_acct IS NULL
668 THEN
669 INSERT INTO fa_sorp_upg_cat
670 (book_type_code,
671 category_id,
672 reval_amortization_acct,
673 reval_amort_account_ccid,
674 impair_expense_acct,
675 impair_expense_account_ccid,
676 impair_reserve_acct,
677 impair_reserve_account_ccid,
678 capital_adj_acct, capital_adj_account_ccid,
679 general_fund_acct, general_fund_account_ccid,
680 validation_flag, category_flex, run_mode,
681 run_date
682 )
683 VALUES (v_category_tab (i).book_type_code,
684 v_category_tab (i).category_id,
685 v_category_tab (i).reval_amortization_acct,
686 v_category_tab (i).reval_amort_account_ccid,
687 v_category_tab (i).impair_expense_acct,
688 v_category_tab (i).impair_expense_account_ccid,
689 v_category_tab (i).impair_reserve_acct,
690 v_category_tab (i).impair_reserve_account_ccid,
691 v_capital_adj_acct, v_capital_adj_ccid,
692 v_general_fund_acct, v_general_fund_ccid,
693 l_imp_acct_chk_flag, l_category_name, p_run_mode,
694 SYSDATE
695 );
696 ELSIF ( (v_category_tab (i).reval_amortization_acct <>
697 v_capital_adj_acct
698 )
699 OR (v_category_tab (i).reval_amort_account_ccid <>
700 v_capital_adj_ccid
701 )
702 )
703 THEN
704 INSERT INTO fa_sorp_upg_cat
705 (book_type_code,
706 category_id,
707 reval_amortization_acct,
708 reval_amort_account_ccid,
709 impair_expense_acct,
710 impair_expense_account_ccid,
711 impair_reserve_acct,
712 impair_reserve_account_ccid,
713 capital_adj_acct, capital_adj_account_ccid,
714 general_fund_acct, general_fund_account_ccid,
715 validation_flag, category_flex,
716 run_mode, run_date
717 )
718 VALUES (v_category_tab (i).book_type_code,
719 v_category_tab (i).category_id,
720 v_category_tab (i).reval_amortization_acct,
721 v_category_tab (i).reval_amort_account_ccid,
722 v_category_tab (i).impair_expense_acct,
723 v_category_tab (i).impair_expense_account_ccid,
724 v_category_tab (i).impair_reserve_acct,
725 v_category_tab (i).impair_reserve_account_ccid,
726 v_capital_adj_acct, v_capital_adj_ccid,
727 v_general_fund_acct, v_general_fund_ccid,
728 l_reval_amort_acct_chk_flag, l_category_name,
729 p_run_mode, SYSDATE
730 );
731 ELSE
732 INSERT INTO fa_sorp_upg_cat
733 (book_type_code,
734 category_id,
735 reval_amortization_acct,
736 reval_amort_account_ccid,
737 impair_expense_acct,
738 impair_expense_account_ccid,
739 impair_reserve_acct,
740 impair_reserve_account_ccid,
741 capital_adj_acct, capital_adj_account_ccid,
742 general_fund_acct, general_fund_account_ccid,
743 validation_flag, category_flex, run_mode,
744 run_date
745 )
746 VALUES (v_category_tab (i).book_type_code,
747 v_category_tab (i).category_id,
748 v_category_tab (i).reval_amortization_acct,
749 v_category_tab (i).reval_amort_account_ccid,
750 v_category_tab (i).impair_expense_acct,
751 v_category_tab (i).impair_expense_account_ccid,
752 v_category_tab (i).impair_reserve_acct,
753 v_category_tab (i).impair_reserve_account_ccid,
754 v_capital_adj_acct, v_capital_adj_ccid,
755 v_general_fund_acct, v_general_fund_ccid,
756 l_success_chk_flag, l_category_name, p_run_mode,
757 SYSDATE
758 );
759 END IF;
760 END LOOP;
761
762 COMMIT;
763
764 IF p_run_mode = 'PREVIEW'
765 THEN
766 RETURN TRUE;
767 END IF;
768
769 IF p_run_mode = 'FINAL'
770 THEN
771 OPEN c_final_cur;
772
773 LOOP
774 FETCH c_final_cur
775 INTO v_final_cur;
776
777 EXIT WHEN c_final_cur%NOTFOUND;
778
779 UPDATE fa_category_books
780 SET capital_adj_acct = v_final_cur.capital_adj_acct,
781 capital_adj_account_ccid =
782 v_final_cur.capital_adj_account_ccid,
783 general_fund_acct = v_final_cur.general_fund_acct,
784 general_fund_account_ccid =
785 v_final_cur.general_fund_account_ccid,
786 last_update_date = SYSDATE
787 WHERE book_type_code = p_book
788 AND category_id = v_final_cur.category_id;
789 END LOOP;
790
791 CLOSE c_final_cur;
792
793 COMMIT;
794 RETURN TRUE;
795 END IF;
796 EXCEPTION
797 WHEN OTHERS
798 THEN
799 ROLLBACK;
800 p_error_code := SQLCODE;
801 p_status_msg := SQLERRM;
802 RETURN FALSE;
803 END fa_category_impl;
804
805 FUNCTION fa_sorp_reval_chk_fn (p_book_type_code VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
806 RETURN BOOLEAN
807 IS
808 v_period_counter NUMBER;
809 v_period_name VARCHAR2 (30);
810 v_asset_id NUMBER;
811 v_asset_number VARCHAR2 (50);
812 v_category_id NUMBER;
813 v_category_name VARCHAR2 (250);
814 v_apr_reval_rsv NUMBER;
815 v_reval_rsv NUMBER;
816 l_message VARCHAR2 (500);
817 l_status_code VARCHAR2 (5);
818
819 CURSOR c_cal_per
820 IS
821 SELECT fadep.period_counter, facalp.period_name
822 FROM fa_calendar_periods facalp,
823 fa_deprn_periods fadep,
824 fa_book_controls fabkctl
825 WHERE facalp.calendar_type = fabkctl.deprn_calendar
826 AND fabkctl.book_type_code = p_book_type_code
827 AND fadep.book_type_code = fabkctl.book_type_code
828 AND facalp.period_name = fadep.period_name
829 AND TO_DATE ('01-04-07', 'DD-MM-YY')
830 BETWEEN TO_DATE (TO_CHAR (facalp.start_date, 'DD-MM-YY'),
831 'DD-MM-YY'
832 )
833 AND TO_DATE (TO_CHAR (facalp.end_date, 'DD-MM-YY'),
834 'DD-MM-YY'
835 );
836
837 CURSOR c_asset_cur
838 IS
839 SELECT DISTINCT dep.asset_id, adda.asset_number,
840 adda.asset_category_id,
841 adda.attribute_category_code category_name
842 FROM fa_deprn_summary dep, fa_additions_v adda
843 WHERE adda.asset_id = dep.asset_id
844 AND book_type_code = p_book_type_code;
845
846 CURSOR c_deprn_apr_cur
847 IS
848 SELECT reval_reserve
849 FROM (SELECT dep.reval_reserve
850 FROM fa_deprn_summary dep
851 WHERE dep.book_type_code = p_book_type_code
852 AND dep.asset_id = v_asset_id
853 AND dep.period_counter < v_period_counter
854 ORDER BY dep.period_counter DESC)
855 WHERE ROWNUM < 2;
856
857 CURSOR c_deprn_cur
858 IS
859 SELECT dep.reval_reserve
860 FROM fa_deprn_summary dep
861 WHERE dep.book_type_code = p_book_type_code
862 AND dep.asset_id = v_asset_id
863 AND dep.period_counter =
864 (SELECT MAX (period_counter)
865 FROM fa_deprn_summary
866 WHERE book_type_code = p_book_type_code
867 AND asset_id = v_asset_id
868 GROUP BY asset_id);
869 BEGIN
870 DELETE FROM fa_sorp_reval_chk;
871
872 COMMIT;
873
874 OPEN c_asset_cur;
875
876 LOOP
877 FETCH c_asset_cur
878 INTO v_asset_id, v_asset_number, v_category_id, v_category_name;
879
880 EXIT WHEN c_asset_cur%NOTFOUND;
881
882 OPEN c_cal_per;
883
884 FETCH c_cal_per
885 INTO v_period_counter, v_period_name;
886
887 CLOSE c_cal_per;
888
889 IF v_period_counter IS NOT NULL
890 THEN
891 OPEN c_deprn_apr_cur;
892
893 FETCH c_deprn_apr_cur
894 INTO v_apr_reval_rsv;
895
896 CLOSE c_deprn_apr_cur;
897
898 IF v_apr_reval_rsv IS NULL
899 THEN
900 l_message := 'SUCCESS';
901 l_status_code := 'SD';
902 END IF;
903 ELSE
904 l_message := 'SUCCESS';
905 l_status_code := 'SP';
906 END IF;
907
908 IF (l_message IS NULL) AND (v_apr_reval_rsv <> 0)
909 THEN
910 l_message :=
911 'FAILED:Revaluation reserve on 01-APR-07 is not zero';
912 l_status_code := 'ARSV';
913 END IF;
914
915 OPEN c_deprn_cur;
916
917 FETCH c_deprn_cur
918 INTO v_reval_rsv;
919
920 CLOSE c_deprn_cur;
921
922 IF (v_reval_rsv < 0)
923 THEN
924 l_message := 'FAILED:Current revaluation reserve is negative';
925 l_status_code := 'CRSV';
926 END IF;
927
928 IF (l_message IS NULL)
929 THEN
930 l_message := 'SUCCESS';
931 l_status_code := 'S';
932 END IF;
933
934 INSERT INTO fa_sorp_reval_chk
935 (book_type_code, category_id, category_name,
936 asset_id, asset_number, apr_reval_rsv,
937 reval_rsv, status_code, status
938 )
939 VALUES (p_book_type_code, v_category_id, v_category_name,
940 v_asset_id, v_asset_number, v_apr_reval_rsv,
941 v_reval_rsv, l_status_code, l_message
942 );
943
944 l_message := NULL;
945 l_status_code := NULL;
946 END LOOP;
947
948 CLOSE c_asset_cur;
949
950 COMMIT;
951 RETURN TRUE;
952 EXCEPTION
953 WHEN OTHERS
954 THEN
955 ROLLBACK;
956 RETURN FALSE;
957 END fa_sorp_reval_chk_fn;
958
959 FUNCTION fa_sorp_upg_cagf_mc_fn (p_book_type_code VARCHAR2, p_mode VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
960 RETURN BOOLEAN
961 IS
962 l_deprn_rsv NUMBER;
963 l_impairment_rsv NUMBER;
964 l_reval_rsv NUMBER;
965 dummy_char VARCHAR2 (10);
966 dummy_bool BOOLEAN;
967 dummy_num NUMBER;
968 v_asset_id NUMBER;
969 v_capital_adj_amount NUMBER;
970 v_general_fund_amount NUMBER;
971 v_capital_adj_summary_amount NUMBER;
972 v_general_fund_summary_amount NUMBER;
973
974 CURSOR c_asset_cur
975 IS
976 SELECT DISTINCT adda.asset_id, adda.asset_number,
977 adda.description asset_description,
978 adda.asset_category_id,
979 adda.attribute_category_code category_name
980 FROM FA_MC_DEPRN_SUMMARY dep, fa_additions_v adda
981 WHERE adda.asset_id = dep.asset_id
982 AND book_type_code = p_book_type_code;
983
984 v_asset_cur c_asset_cur%ROWTYPE;
985
986 CURSOR c_book_cur
987 IS
988 SELECT date_placed_in_service, COST current_cost
989 FROM fa_books_v
990 WHERE book_type_code = p_book_type_code AND asset_id = v_asset_id;
991
992 v_book_cur c_book_cur%ROWTYPE;
993
994 CURSOR c_deprn_cur
995 IS
996 SELECT distribution_id, deprn_reserve, deprn_amount,
997 reval_amortization, impairment_amount, period_counter,set_of_books_id
998 FROM FA_MC_DEPRN_DETAIL
999 WHERE book_type_code = p_book_type_code AND asset_id = v_asset_id
1000 ORDER BY SET_OF_BOOKS_ID ASC,period_counter;
1001
1002 v_deprn_cur c_deprn_cur%ROWTYPE;
1003
1004 CURSOR c_deprn_summary_cur
1005 IS
1006 SELECT deprn_reserve, deprn_amount, reval_amortization,
1007 impairment_amount, period_counter,set_of_books_id
1008 FROM FA_MC_DEPRN_SUMMARY
1009 WHERE book_type_code = p_book_type_code AND asset_id = v_asset_id
1010 ORDER BY SET_OF_BOOKS_ID ASC,period_counter;
1011
1012 v_deprn_summary_cur c_deprn_summary_cur%ROWTYPE;
1013
1014 cursor c_fa_sorp_upg_cagf_hist_cur is
1015 select book_type_code ,
1016 asset_id,
1017 asset_number ,
1018 asset_description,
1019 date_placed_in_service,
1020 category_name,
1021 current_cost,
1022 depriciation_reserve,
1023 revaluation_reserve,
1024 impairment_reserve,
1025 capital_adjustment_acct_amount,
1026 general_fund_acct_amount
1027 from fa_sorp_upg_cagf;
1028
1029 v_fa_sorp_upg_cagf_hist_cur c_fa_sorp_upg_cagf_hist_cur%rowtype;
1030
1031 l_request_id number;
1032 v_final_cnt number;
1033 v_period_name varchar2(25);
1034
1035
1036
1037 l_imp_cap_det_value number;
1038 l_imp_gen_det_value number;
1039 l_imp_cap_sum_value number;
1040 l_imp_gen_sum_value number;
1041
1042 l_ret_cap_det_value number;
1043 l_ret_gen_det_value number;
1044 l_ret_cap_sum_value number;
1045 l_ret_gen_sum_value number;
1046
1047 l_old_sob_id NUMBER;
1048
1049 BEGIN
1050 DELETE FROM fa_sorp_upg_cagf;
1051
1052 COMMIT;
1053
1054 l_request_id := fnd_global.conc_request_id;
1055
1056 p_from := 'fa_sorp_upg_cagf';
1057 p_where := ' where 1=1';
1058 p_order_by := 'order by category_name'; -- Bug#7632825
1059 P_REQUEST_WHERE := ' AND REQUEST_ID <> '||l_request_id;
1060
1061 OPEN c_asset_cur;
1062
1063 LOOP
1064 FETCH c_asset_cur
1065 INTO v_asset_cur;
1066
1067 EXIT WHEN c_asset_cur%NOTFOUND;
1068 v_asset_id := v_asset_cur.asset_id;
1069
1070 OPEN c_book_cur;
1071
1072 FETCH c_book_cur
1073 INTO v_book_cur;
1074
1075 CLOSE c_book_cur;
1076
1077 fa_query_balances_pkg.query_balances
1078 (x_asset_id => v_asset_cur.asset_id,
1079 x_book => p_book_type_code,
1080 x_period_ctr => 0,
1081 x_dist_id => 0,
1082 x_run_mode => 'STANDARD',
1083 x_cost => dummy_num,
1084 x_deprn_rsv => l_deprn_rsv,
1085 x_reval_rsv => l_reval_rsv,
1086 x_ytd_deprn => dummy_num,
1087 x_ytd_reval_exp => dummy_num,
1088 x_reval_deprn_exp => dummy_num,
1089 x_deprn_exp => dummy_num,
1090 x_reval_amo => dummy_num,
1091 x_prod => dummy_num,
1092 x_ytd_prod => dummy_num,
1093 x_ltd_prod => dummy_num,
1094 x_adj_cost => dummy_num,
1095 x_reval_amo_basis => dummy_num,
1096 x_bonus_rate => dummy_num,
1097 x_deprn_source_code => dummy_char,
1098 x_adjusted_flag => dummy_bool,
1099 x_transaction_header_id => -1,
1100 x_bonus_deprn_rsv => dummy_num,
1101 x_bonus_ytd_deprn => dummy_num,
1102 x_bonus_deprn_amount => dummy_num,
1103 x_impairment_rsv => l_impairment_rsv,
1104 --Bug#7293626
1105 x_ytd_impairment => dummy_num,
1106 x_impairment_amount => dummy_num,
1107 x_capital_adjustment => dummy_num,
1108 x_general_fund => dummy_num,
1109 x_mrc_sob_type_code => 'P',
1110 x_set_of_books_id => null
1111 , p_log_level_rec => p_log_level_rec);
1112 l_old_sob_id := 0;
1113 OPEN c_deprn_cur;
1114
1115 LOOP
1116 FETCH c_deprn_cur
1117 INTO v_deprn_cur;
1118
1119 EXIT WHEN c_deprn_cur%NOTFOUND;
1120
1121
1122
1123 if not get_impairment_sorp_mc
1124 (p_book_type_code,
1125 v_asset_id,
1126 v_deprn_cur.distribution_id,
1127 v_deprn_cur.period_counter,
1128 'D',
1129 l_imp_cap_det_value,
1130 l_imp_gen_det_value,
1131 v_deprn_cur.set_of_books_id) then
1132 return false;
1133 end if;
1134
1135
1136 if not get_retirement_sorp_mc
1137 (p_book_type_code,
1138 v_asset_id,
1139 v_deprn_cur.distribution_id,
1140 v_deprn_cur.period_counter,
1141 l_ret_cap_det_value,
1142 l_ret_gen_det_value,
1143 v_deprn_cur.set_of_books_id) then
1144 return false;
1145 end if;
1146
1147
1148
1149
1150
1151 IF c_deprn_cur%ROWCOUNT = 1 or (l_old_sob_id <> v_deprn_cur.set_of_books_id )
1152 THEN
1153 v_capital_adj_amount :=
1154 NVL (v_deprn_cur.deprn_reserve, 0)
1155 + NVL (v_deprn_cur.impairment_amount, 0)
1156 - NVL (v_deprn_cur.reval_amortization, 0)
1157 + NVL (l_imp_cap_det_value,0)
1158 + NVL(l_ret_cap_det_value,0);
1159 v_general_fund_amount :=
1160 NVL (v_deprn_cur.deprn_reserve, 0)
1161 + NVL (v_deprn_cur.impairment_amount, 0)
1162 + NVL(l_ret_gen_det_value,0);
1163
1164 l_old_sob_id := v_deprn_cur.set_of_books_id;
1165
1166 IF p_mode = 'FINAL'
1167 THEN
1168 UPDATE FA_MC_DEPRN_DETAIL
1169 SET capital_adjustment = v_capital_adj_amount,
1170 general_fund = v_general_fund_amount
1171 WHERE distribution_id = v_deprn_cur.distribution_id
1172 AND book_type_code = p_book_type_code
1173 AND period_counter = v_deprn_cur.period_counter
1174 AND set_of_books_id = v_deprn_cur.set_of_books_id;
1175 END IF;
1176 ELSE
1177 v_capital_adj_amount :=
1178 NVL (v_capital_adj_amount, 0)
1179 + NVL (v_deprn_cur.deprn_amount, 0)
1180 + NVL (v_deprn_cur.impairment_amount, 0)
1181 - NVL (v_deprn_cur.reval_amortization, 0)
1182 + NVL (l_imp_cap_det_value,0)
1183 + NVL(l_ret_cap_det_value,0);
1184 v_general_fund_amount :=
1185 NVL (v_general_fund_amount, 0)
1186 + NVL (v_deprn_cur.deprn_amount, 0)
1187 + NVL (v_deprn_cur.impairment_amount, 0)
1188 + NVL(l_ret_gen_det_value,0);
1189
1190 IF p_mode = 'FINAL'
1191 THEN
1192 UPDATE FA_MC_DEPRN_DETAIL
1193 SET capital_adjustment = v_capital_adj_amount,
1194 general_fund = v_general_fund_amount
1195 WHERE distribution_id = v_deprn_cur.distribution_id
1196 AND book_type_code = p_book_type_code
1197 AND period_counter = v_deprn_cur.period_counter
1198 AND set_of_books_id = v_deprn_cur.set_of_books_id;
1199 END IF;
1200 END IF;
1201 END LOOP;
1202
1203 CLOSE c_deprn_cur;
1204
1205
1206
1207 IF p_mode = 'FINAL'
1208 THEN
1209 l_old_sob_id := 0;
1210 OPEN c_deprn_summary_cur;
1211
1212 LOOP
1213 FETCH c_deprn_summary_cur
1214 INTO v_deprn_summary_cur;
1215
1216 EXIT WHEN c_deprn_summary_cur%NOTFOUND;
1217
1218
1219 if not get_impairment_sorp_mc
1220 (p_book_type_code,
1221 v_asset_id,
1222 NULL,
1223 v_deprn_summary_cur.period_counter,
1224 'S',
1225 l_imp_cap_sum_value,
1226 l_imp_gen_sum_value,
1227 v_deprn_summary_cur.set_of_books_id) then
1228 return false;
1229 end if;
1230
1231 if not get_retirement_sorp_mc
1232 (p_book_type_code,
1233 v_asset_id,
1234 NULL,
1235 v_deprn_summary_cur.period_counter,
1236 l_ret_cap_det_value,
1237 l_ret_gen_det_value,
1238 v_deprn_summary_cur.set_of_books_id) then
1239 return false;
1240 end if;
1241
1242
1243
1244
1245 IF c_deprn_summary_cur%ROWCOUNT = 1 or (l_old_sob_id <> v_deprn_summary_cur.set_of_books_id)
1246 THEN
1247 v_capital_adj_summary_amount :=
1248 NVL (v_deprn_summary_cur.deprn_reserve, 0)
1249 + NVL (v_deprn_summary_cur.impairment_amount, 0)
1250 - NVL (v_deprn_summary_cur.reval_amortization, 0)
1251 + NVL(l_imp_cap_sum_value,0)
1252 + NVL(l_ret_cap_det_value,0);
1253
1254 v_general_fund_summary_amount :=
1255 NVL (v_deprn_summary_cur.deprn_reserve, 0)
1256 + NVL (v_deprn_summary_cur.impairment_amount, 0)
1257 + NVL(l_ret_gen_det_value,0);
1258
1259 l_old_sob_id := v_deprn_summary_cur.set_of_books_id;
1260
1261 UPDATE FA_MC_DEPRN_SUMMARY
1262 SET capital_adjustment = v_capital_adj_summary_amount,
1263 general_fund = v_general_fund_summary_amount
1264 WHERE asset_id = v_asset_id
1265 AND book_type_code = p_book_type_code
1266 AND period_counter = v_deprn_summary_cur.period_counter
1267 AND set_of_books_id = v_deprn_summary_cur.set_of_books_id;
1268 ELSE
1269 v_capital_adj_summary_amount :=
1270 NVL (v_capital_adj_summary_amount, 0)
1271 + NVL (v_deprn_summary_cur.deprn_amount, 0)
1272 + NVL (v_deprn_summary_cur.impairment_amount, 0)
1273 - NVL (v_deprn_summary_cur.reval_amortization, 0)
1274 + NVL(l_imp_cap_sum_value,0)
1275 + NVL(l_ret_cap_det_value,0);
1276 v_general_fund_summary_amount :=
1277 NVL (v_general_fund_summary_amount, 0)
1278 + NVL (v_deprn_summary_cur.deprn_amount, 0)
1279 + NVL (v_deprn_summary_cur.impairment_amount, 0)
1280 + NVL(l_ret_gen_det_value,0);
1281
1282 UPDATE FA_MC_DEPRN_SUMMARY
1283 SET capital_adjustment = v_capital_adj_summary_amount,
1284 general_fund = v_general_fund_summary_amount
1285 WHERE asset_id = v_asset_id
1286 AND book_type_code = p_book_type_code
1287 AND period_counter = v_deprn_summary_cur.period_counter
1288 AND set_of_books_id = v_deprn_summary_cur.set_of_books_id;
1289 END IF;
1290 END LOOP;
1291
1292 CLOSE c_deprn_summary_cur;
1293 END IF;
1294
1295 INSERT INTO fa_sorp_upg_cagf
1296 (book_type_code, asset_id, asset_number,
1297 asset_description,
1298 date_placed_in_service,
1299 category_name, current_cost,
1300 depriciation_reserve, revaluation_reserve,
1301 impairment_reserve, capital_adjustment_acct_amount,
1302 general_fund_acct_amount
1303 )
1304 VALUES (p_book_type_code, v_asset_id, v_asset_cur.asset_number,
1305 v_asset_cur.asset_description,
1306 v_book_cur.date_placed_in_service,
1307 v_asset_cur.category_name, v_book_cur.current_cost,
1308 l_deprn_rsv, l_reval_rsv,
1309 l_impairment_rsv, v_capital_adj_amount,
1310 v_general_fund_amount
1311 );
1312
1313 v_capital_adj_amount := 0;
1314 v_general_fund_amount := 0;
1315 END LOOP;
1316
1317 CLOSE c_asset_cur;
1318
1319 IF p_mode = 'FINAL' then
1320
1321
1322
1323 select period_name into v_period_name
1324 from fa_deprn_periods
1325 where book_type_code = P_FA_BOOK
1326 and period_close_date is null;
1327
1328 open c_fa_sorp_upg_cagf_hist_cur;
1329 loop
1330 fetch c_fa_sorp_upg_cagf_hist_cur into v_fa_sorp_upg_cagf_hist_cur;
1331 exit when c_fa_sorp_upg_cagf_hist_cur%notfound;
1332
1333 INSERT INTO fa_sorp_upg_cagf_hist
1334 (book_type_code,
1335 asset_id,
1336 asset_number,
1337 asset_description,
1338 date_placed_in_service,
1339 category_name,
1340 current_cost,
1341 depriciation_reserve,
1342 revaluation_reserve,
1343 impairment_reserve,
1344 capital_adjustment_acct_amount,
1345 general_fund_acct_amount,
1346 request_id,
1347 report_mode,
1348 period_name
1349 )
1350 VALUES
1351 (v_fa_sorp_upg_cagf_hist_cur.book_type_code,
1352 v_fa_sorp_upg_cagf_hist_cur.asset_id,
1353 v_fa_sorp_upg_cagf_hist_cur.asset_number,
1354 v_fa_sorp_upg_cagf_hist_cur.asset_description,
1355 v_fa_sorp_upg_cagf_hist_cur.date_placed_in_service,
1356 v_fa_sorp_upg_cagf_hist_cur.category_name,
1357 v_fa_sorp_upg_cagf_hist_cur.current_cost,
1358 v_fa_sorp_upg_cagf_hist_cur.depriciation_reserve,
1359 v_fa_sorp_upg_cagf_hist_cur.revaluation_reserve,
1360 v_fa_sorp_upg_cagf_hist_cur.impairment_reserve,
1361 v_fa_sorp_upg_cagf_hist_cur.capital_adjustment_acct_amount,
1362 v_fa_sorp_upg_cagf_hist_cur.general_fund_acct_amount,
1363 l_request_id,
1364 p_mode,
1365 v_period_name);
1366 end loop;
1367 close c_fa_sorp_upg_cagf_hist_cur;
1368
1369 END IF;
1370
1371
1372 COMMIT;
1373 RETURN TRUE;
1374 EXCEPTION
1375 WHEN OTHERS
1376 THEN
1377 ROLLBACK;
1378 RETURN FALSE;
1379 END fa_sorp_upg_cagf_mc_fn;
1380
1381 FUNCTION fa_sorp_upg_cagf_fn (p_book_type_code VARCHAR2, p_mode VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1382 RETURN BOOLEAN
1383 IS
1384 l_deprn_rsv NUMBER;
1385 l_impairment_rsv NUMBER;
1386 l_reval_rsv NUMBER;
1387 dummy_char VARCHAR2 (10);
1388 dummy_bool BOOLEAN;
1389 dummy_num NUMBER;
1390 v_asset_id NUMBER;
1391 v_capital_adj_amount NUMBER;
1392 v_general_fund_amount NUMBER;
1393 v_capital_adj_summary_amount NUMBER;
1394 v_general_fund_summary_amount NUMBER;
1395
1396 V_BOOLEAN BOOLEAN;
1397
1398 CURSOR c_asset_cur
1399 IS
1400 SELECT DISTINCT adda.asset_id, adda.asset_number,
1401 adda.description asset_description,
1402 adda.asset_category_id,
1403 adda.attribute_category_code category_name
1404 FROM fa_deprn_summary dep, fa_additions_v adda
1405 WHERE adda.asset_id = dep.asset_id
1406 AND book_type_code = p_book_type_code;
1407
1408 v_asset_cur c_asset_cur%ROWTYPE;
1409
1410 CURSOR c_book_cur
1411 IS
1412 SELECT date_placed_in_service, COST current_cost
1413 FROM fa_books_v
1414 WHERE book_type_code = p_book_type_code AND asset_id = v_asset_id;
1415
1416 v_book_cur c_book_cur%ROWTYPE;
1417
1418 CURSOR c_deprn_cur
1419 IS
1420 SELECT distribution_id, deprn_reserve, deprn_amount,
1421 reval_amortization, impairment_amount, period_counter
1422 FROM fa_deprn_detail
1423 WHERE book_type_code = p_book_type_code AND asset_id = v_asset_id
1424 ORDER BY period_counter;
1425
1426 v_deprn_cur c_deprn_cur%ROWTYPE;
1427
1428 CURSOR c_deprn_summary_cur
1429 IS
1430 SELECT deprn_reserve, deprn_amount, reval_amortization,
1431 impairment_amount, period_counter
1432 FROM fa_deprn_summary
1433 WHERE book_type_code = p_book_type_code AND asset_id = v_asset_id
1434 ORDER BY period_counter;
1435
1436 v_deprn_summary_cur c_deprn_summary_cur%ROWTYPE;
1437
1438 cursor c_fa_sorp_upg_cagf_hist_cur is
1439 select book_type_code ,
1440 asset_id,
1441 asset_number ,
1442 asset_description,
1443 date_placed_in_service,
1444 category_name,
1445 current_cost,
1446 depriciation_reserve,
1447 revaluation_reserve,
1448 impairment_reserve,
1449 capital_adjustment_acct_amount,
1450 general_fund_acct_amount
1451 from fa_sorp_upg_cagf;
1452
1453 v_fa_sorp_upg_cagf_hist_cur c_fa_sorp_upg_cagf_hist_cur%rowtype;
1454
1455 l_request_id number;
1456 v_final_cnt number;
1457 v_period_name varchar2(25);
1458
1459
1460
1461 l_imp_cap_det_value number;
1462 l_imp_gen_det_value number;
1463 l_imp_cap_sum_value number;
1464 l_imp_gen_sum_value number;
1465
1466 l_ret_cap_det_value number;
1467 l_ret_gen_det_value number;
1468 l_ret_cap_sum_value number;
1469 l_ret_gen_sum_value number;
1470
1471 BEGIN
1472
1473 fa_srvr_msg.Init_Server_Message; -- Initialize server message stack
1474 fa_debug_pkg.Initialize; -- Initialize debug message stack
1475 fa_debug_pkg.add('fa_sorp_upg_cagf_fn', 'process calculation', 'BEGINs', p_log_level_rec => p_log_level_rec);
1476
1477
1478 DELETE FROM fa_sorp_upg_cagf;
1479
1480 COMMIT;
1481
1482 l_request_id := fnd_global.conc_request_id;
1483
1484 p_from := 'fa_sorp_upg_cagf';
1485 p_where := ' where 1=1';
1486 p_order_by := 'order by category_name'; -- Bug#7632825
1487 P_REQUEST_WHERE := ' AND REQUEST_ID <> '||l_request_id;
1488
1489
1490
1491 select count(1) into v_final_cnt
1492 from fa_sorp_upg_cagf_hist
1493 where book_type_code = p_book_type_code
1494 and report_mode = p_mode;
1495
1496 IF v_final_cnt = 0 then
1497
1498
1499 OPEN c_asset_cur;
1500
1501 LOOP
1502 FETCH c_asset_cur
1503 INTO v_asset_cur;
1504
1505 EXIT WHEN c_asset_cur%NOTFOUND;
1506 v_asset_id := v_asset_cur.asset_id;
1507
1508 OPEN c_book_cur;
1509
1510 FETCH c_book_cur
1511 INTO v_book_cur;
1512
1513 CLOSE c_book_cur;
1514
1515 fa_query_balances_pkg.query_balances
1516 (x_asset_id => v_asset_cur.asset_id,
1517 x_book => p_book_type_code,
1518 x_period_ctr => 0,
1519 x_dist_id => 0,
1520 x_run_mode => 'STANDARD',
1521 x_cost => dummy_num,
1522 x_deprn_rsv => l_deprn_rsv,
1523 x_reval_rsv => l_reval_rsv,
1524 x_ytd_deprn => dummy_num,
1525 x_ytd_reval_exp => dummy_num,
1526 x_reval_deprn_exp => dummy_num,
1527 x_deprn_exp => dummy_num,
1528 x_reval_amo => dummy_num,
1529 x_prod => dummy_num,
1530 x_ytd_prod => dummy_num,
1531 x_ltd_prod => dummy_num,
1532 x_adj_cost => dummy_num,
1533 x_reval_amo_basis => dummy_num,
1534 x_bonus_rate => dummy_num,
1535 x_deprn_source_code => dummy_char,
1536 x_adjusted_flag => dummy_bool,
1537 x_transaction_header_id => -1,
1538 x_bonus_deprn_rsv => dummy_num,
1539 x_bonus_ytd_deprn => dummy_num,
1540 x_bonus_deprn_amount => dummy_num,
1541 x_impairment_rsv => l_impairment_rsv,
1542 --Bug#7293626
1543 x_ytd_impairment => dummy_num,
1544 x_impairment_amount => dummy_num,
1545 x_capital_adjustment => dummy_num,
1546 x_general_fund => dummy_num,
1547 x_mrc_sob_type_code => 'P',
1548 x_set_of_books_id => null
1549 , p_log_level_rec => p_log_level_rec);
1550
1551 OPEN c_deprn_cur;
1552
1553 LOOP
1554 FETCH c_deprn_cur
1555 INTO v_deprn_cur;
1556
1557 EXIT WHEN c_deprn_cur%NOTFOUND;
1558
1559 if not get_impairment_sorp_values
1560 (p_book_type_code,
1561 v_asset_id,
1562 v_deprn_cur.distribution_id,
1563 v_deprn_cur.period_counter,
1564 'D',
1565 l_imp_cap_det_value,
1566 l_imp_gen_det_value) then
1567 return false;
1568 end if;
1569
1570
1571 if not get_retirement_sorp_values
1572 (p_book_type_code,
1573 v_asset_id,
1574 v_deprn_cur.distribution_id,
1575 v_deprn_cur.period_counter,
1576 l_ret_cap_det_value,
1577 l_ret_gen_det_value) then
1578 return false;
1579 end if;
1580
1581
1582
1583 IF c_deprn_cur%ROWCOUNT = 1
1584 THEN
1585 v_capital_adj_amount :=
1586 NVL (v_deprn_cur.deprn_reserve, 0)
1587 + NVL (v_deprn_cur.impairment_amount, 0)
1588 - NVL (v_deprn_cur.reval_amortization, 0)
1589 + NVL (l_imp_cap_det_value,0)
1590 + NVL(l_ret_cap_det_value,0);
1591 v_general_fund_amount :=
1592 NVL (v_deprn_cur.deprn_reserve, 0)
1593 + NVL (v_deprn_cur.impairment_amount, 0)
1594 + NVL(l_ret_gen_det_value,0);
1595
1596
1597
1598 IF p_mode = 'FINAL'
1599 THEN
1600 UPDATE fa_deprn_detail
1601 SET capital_adjustment = v_capital_adj_amount,
1602 general_fund = v_general_fund_amount
1603 WHERE distribution_id = v_deprn_cur.distribution_id
1604 AND book_type_code = p_book_type_code
1605 AND period_counter = v_deprn_cur.period_counter;
1606 END IF;
1607 ELSE
1608 v_capital_adj_amount :=
1609 NVL (v_capital_adj_amount, 0)
1610 + NVL (v_deprn_cur.deprn_amount, 0)
1611 + NVL (v_deprn_cur.impairment_amount, 0)
1612 - NVL (v_deprn_cur.reval_amortization, 0)
1613 + NVL (l_imp_cap_det_value,0)
1614 + NVL(l_ret_cap_det_value,0);
1615 v_general_fund_amount :=
1616 NVL (v_general_fund_amount, 0)
1617 + NVL (v_deprn_cur.deprn_amount, 0)
1618 + NVL (v_deprn_cur.impairment_amount, 0)
1619 + NVL(l_ret_gen_det_value,0);
1620
1621 IF p_mode = 'FINAL'
1622 THEN
1623 UPDATE fa_deprn_detail
1624 SET capital_adjustment = v_capital_adj_amount,
1625 general_fund = v_general_fund_amount
1626 WHERE distribution_id = v_deprn_cur.distribution_id
1627 AND book_type_code = p_book_type_code
1628 AND period_counter = v_deprn_cur.period_counter;
1629 END IF;
1630 END IF;
1631 END LOOP;
1632
1633 CLOSE c_deprn_cur;
1634
1635
1636
1637 IF p_mode = 'FINAL'
1638 THEN
1639 OPEN c_deprn_summary_cur;
1640
1641 LOOP
1642 FETCH c_deprn_summary_cur
1643 INTO v_deprn_summary_cur;
1644
1645 EXIT WHEN c_deprn_summary_cur%NOTFOUND;
1646
1647 if not get_impairment_sorp_values
1648 (p_book_type_code,
1649 v_asset_id,
1650 NULL,
1651 v_deprn_summary_cur.period_counter,
1652 'S',
1653 l_imp_cap_sum_value,
1654 l_imp_gen_sum_value) then
1655 return false;
1656 end if;
1657
1658 if not get_retirement_sorp_values
1659 (p_book_type_code,
1660 v_asset_id,
1661 NULL,
1662 v_deprn_summary_cur.period_counter,
1663 l_ret_cap_det_value,
1664 l_ret_gen_det_value) then
1665 return false;
1666 end if;
1667
1668 IF c_deprn_summary_cur%ROWCOUNT = 1
1669 THEN
1670 v_capital_adj_summary_amount :=
1671 NVL (v_deprn_summary_cur.deprn_reserve, 0)
1672 + NVL (v_deprn_summary_cur.impairment_amount, 0)
1673 - NVL (v_deprn_summary_cur.reval_amortization, 0)
1674 + NVL(l_imp_cap_sum_value,0)
1675 + NVL(l_ret_cap_det_value,0);
1676 v_general_fund_summary_amount :=
1677 NVL (v_deprn_summary_cur.deprn_reserve, 0)
1678 + NVL (v_deprn_summary_cur.impairment_amount, 0)
1679 + NVL(l_ret_gen_det_value,0);
1680
1681 UPDATE fa_deprn_summary
1682 SET capital_adjustment = v_capital_adj_summary_amount,
1683 general_fund = v_general_fund_summary_amount
1684 WHERE asset_id = v_asset_id
1685 AND book_type_code = p_book_type_code
1686 AND period_counter = v_deprn_summary_cur.period_counter;
1687 ELSE
1688 v_capital_adj_summary_amount :=
1689 NVL (v_capital_adj_summary_amount, 0)
1690 + NVL (v_deprn_summary_cur.deprn_amount, 0)
1691 + NVL (v_deprn_summary_cur.impairment_amount, 0)
1692 - NVL (v_deprn_summary_cur.reval_amortization, 0)
1693 + NVL(l_imp_cap_sum_value,0)
1694 + NVL(l_ret_cap_det_value,0);
1695 v_general_fund_summary_amount :=
1696 NVL (v_general_fund_summary_amount, 0)
1697 + NVL (v_deprn_summary_cur.deprn_amount, 0)
1698 + NVL (v_deprn_summary_cur.impairment_amount, 0)
1699 + NVL(l_ret_gen_det_value,0);
1700
1701
1702 UPDATE fa_deprn_summary
1703 SET capital_adjustment = v_capital_adj_summary_amount,
1704 general_fund = v_general_fund_summary_amount
1705 WHERE asset_id = v_asset_id
1706 AND book_type_code = p_book_type_code
1707 AND period_counter = v_deprn_summary_cur.period_counter;
1708 END IF;
1709 END LOOP;
1710
1711 CLOSE c_deprn_summary_cur;
1712 END IF;
1713
1714 INSERT INTO fa_sorp_upg_cagf
1715 (book_type_code, asset_id, asset_number,
1716 asset_description,
1717 date_placed_in_service,
1718 category_name, current_cost,
1719 depriciation_reserve, revaluation_reserve,
1720 impairment_reserve, capital_adjustment_acct_amount,
1721 general_fund_acct_amount
1722 )
1723 VALUES (p_book_type_code, v_asset_id, v_asset_cur.asset_number,
1724 v_asset_cur.asset_description,
1725 v_book_cur.date_placed_in_service,
1726 v_asset_cur.category_name, v_book_cur.current_cost,
1727 l_deprn_rsv, l_reval_rsv,
1728 l_impairment_rsv, v_capital_adj_amount,
1729 v_general_fund_amount
1730 );
1731
1732 v_capital_adj_amount := 0;
1733 v_general_fund_amount := 0;
1734 END LOOP;
1735
1736 CLOSE c_asset_cur;
1737
1738 IF p_mode = 'FINAL' then
1739
1740
1741
1742 select period_name into v_period_name
1743 from fa_deprn_periods
1744 where book_type_code = P_FA_BOOK
1745 and period_close_date is null;
1746
1747 open c_fa_sorp_upg_cagf_hist_cur;
1748 loop
1749 fetch c_fa_sorp_upg_cagf_hist_cur into v_fa_sorp_upg_cagf_hist_cur;
1750 exit when c_fa_sorp_upg_cagf_hist_cur%notfound;
1751
1752 INSERT INTO fa_sorp_upg_cagf_hist
1753 (book_type_code,
1754 asset_id,
1755 asset_number,
1756 asset_description,
1757 date_placed_in_service,
1758 category_name,
1759 current_cost,
1760 depriciation_reserve,
1761 revaluation_reserve,
1762 impairment_reserve,
1763 capital_adjustment_acct_amount,
1764 general_fund_acct_amount,
1765 request_id,
1766 report_mode,
1767 period_name
1768 )
1769 VALUES
1770 (v_fa_sorp_upg_cagf_hist_cur.book_type_code,
1771 v_fa_sorp_upg_cagf_hist_cur.asset_id,
1772 v_fa_sorp_upg_cagf_hist_cur.asset_number,
1773 v_fa_sorp_upg_cagf_hist_cur.asset_description,
1774 v_fa_sorp_upg_cagf_hist_cur.date_placed_in_service,
1775 v_fa_sorp_upg_cagf_hist_cur.category_name,
1776 v_fa_sorp_upg_cagf_hist_cur.current_cost,
1777 v_fa_sorp_upg_cagf_hist_cur.depriciation_reserve,
1778 v_fa_sorp_upg_cagf_hist_cur.revaluation_reserve,
1779 v_fa_sorp_upg_cagf_hist_cur.impairment_reserve,
1780 v_fa_sorp_upg_cagf_hist_cur.capital_adjustment_acct_amount,
1781 v_fa_sorp_upg_cagf_hist_cur.general_fund_acct_amount,
1782 l_request_id,
1783 p_mode,
1784 v_period_name);
1785 end loop;
1786 close c_fa_sorp_upg_cagf_hist_cur;
1787
1788 END IF;
1789
1790 COMMIT;
1791
1792 IF fa_sorp_upg_mc_flag(p_book_type_code) THEN
1793 V_BOOLEAN := fa_sorp_upg_cagf_MC_FN(p_book_type_code,P_MODE);
1794 IF V_BOOLEAN THEN RETURN TRUE;
1795 ELSE RETURN FALSE;
1796 END IF;
1797
1798 END IF;
1799
1800 ELSE -- v_final_cnt = 0
1801 p_from := 'fa_sorp_upg_cagf_hist';
1802 p_where := ' where book_type_code = '||''''||p_book_type_code||''''||' and report_mode ='||''''||p_mode||'''';
1803 END IF;
1804
1805 RETURN TRUE;
1806
1807 EXCEPTION
1808 WHEN OTHERS
1809 THEN
1810 ROLLBACK;
1811 RETURN FALSE;
1812 END fa_sorp_upg_cagf_fn;
1813
1814 FUNCTION fa_sorp_upg_impreval_mc_fn(p_book VARCHAR2, p_mode VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1815 RETURN BOOLEAN
1816 IS
1817 v_impairment_id NUMBER;
1818 v_mass_reval_id NUMBER;
1819 v_mass_description VARCHAR2(250);
1820 v_description VARCHAR2(100);
1821 v_asset_id NUMBER;
1822 v_cash_id NUMBER;
1823
1824 CURSOR c_imp_asset_cur IS SELECT 'Impairment' transaction_type,
1825 i.asset_id,
1826 a.asset_number,
1827 a.description asset_description, a.asset_category_id,
1828 a.attribute_category_code category_name, i.impairment_id,
1829 i.impairment_name,
1830 NVL (i.description, 'Others') imp_description,
1831 -- Bug#7704219
1832 NVL (
1833 decode(SUBSTR (i.description, 1, 3),'CPP','CPP',
1834 'CEB','CEB',
1835 'OTH'),
1836 'OTH'
1837 ) impair_classification_type
1838 FROM fa_additions_v a, FA_MC_IMPAIRMENTS i
1839 WHERE a.asset_id = i.asset_id AND i.book_type_code = p_book;
1840
1841 v_imp_asset_cur c_imp_asset_cur%ROWTYPE;
1842
1843 CURSOR c_imp_itf_asset_cur
1844 IS
1845 SELECT impairment_amount
1846 FROM FA_MC_ITF_IMPAIRMENTS
1847 WHERE impairment_id = v_impairment_id
1848 AND asset_id = v_asset_id
1849 AND book_type_code = p_book;
1850
1851 v_imp_itf_asset_cur c_imp_itf_asset_cur%ROWTYPE;
1852
1853 cursor c_imp_deprn_asset_cur(l_book varchar2,
1854 l_asset_id number,
1855 l_period_counter number)
1856 is
1857 select capital_adjustment,
1858 general_fund
1859 from FA_MC_DEPRN_SUMMARY
1860 where book_type_code = l_book
1861 and asset_id = l_asset_id
1862 and period_counter = (select max(period_counter)
1863 from FA_MC_DEPRN_SUMMARY
1864 where book_type_code = l_book
1865 and asset_id = l_asset_id
1866 and period_counter < l_period_counter);
1867
1868 v_imp_deprn_asset_cur c_imp_deprn_asset_cur%rowtype;
1869
1870
1871 CURSOR c_imp_cash_cur
1872 IS
1873 SELECT 'Impairment' transaction_type, i.cash_generating_unit_id,
1874 a.cash_generating_unit, a.description asset_description,
1875 NULL asset_category_id, NULL category_name, i.impairment_id,
1876 i.impairment_name,
1877 NVL (i.description, 'Others') imp_description,
1878 NVL (SUBSTR (i.description, 1, 3),
1879 'OTH'
1880 ) impair_classification_type
1881 FROM fa_cash_gen_units a, FA_MC_IMPAIRMENTS i
1882 WHERE a.cash_generating_unit_id = i.cash_generating_unit_id
1883 AND i.book_type_code = p_book;
1884
1885 v_imp_cash_cur c_imp_cash_cur%ROWTYPE;
1886
1887 CURSOR c_imp_itf_cash_cur
1888 IS
1889 SELECT impairment_amount
1890 FROM FA_MC_ITF_IMPAIRMENTS
1891 WHERE impairment_id = v_impairment_id
1892 AND cash_generating_unit_id = v_cash_id
1893 AND book_type_code = p_book;
1894
1895 v_imp_itf_cash_cur c_imp_itf_cash_cur%ROWTYPE;
1896
1897 CURSOR c_reval_id_cur
1898 IS
1899 SELECT DISTINCT mass_reval_id, description
1900 FROM fa_mass_revaluations
1901 WHERE book_type_code = p_book;
1902
1903 -- Bug#7578069 cursor queries for c_reval_asset_cur and c_reval_cat_cur modified
1904
1905 CURSOR c_reval_asset_cur
1906 IS
1907 SELECT 'Revaluation' transaction_type, r.asset_id, a.asset_number,
1908 a.description asset_description, r.mass_reval_id,
1909 r.reval_percent
1910 FROM fa_additions_v a, fa_mass_revaluation_rules r,fa_mass_revaluations mr
1911 WHERE a.asset_id = r.asset_id
1912 AND r.mass_reval_id = mr.mass_reval_id
1913 AND mr.mass_reval_id = v_mass_reval_id
1914 AND r.category_id IS NULL
1915 AND mr.book_type_code = p_book;
1916
1917 v_reval_asset_cur c_reval_asset_cur%ROWTYPE;
1918
1919 CURSOR c_reval_cat_cur
1920 IS
1921 SELECT distinct 'Revaluation' transaction_type,
1922 r.category_id asset_category_id,
1923 a.attribute_category_code category_name, r.mass_reval_id,
1924 r.reval_percent
1925 FROM fa_additions_v a, fa_mass_revaluation_rules r,fa_mass_revaluations mr
1926 WHERE a.asset_category_id = r.category_id
1927 AND r.mass_reval_id = mr.mass_reval_id
1928 AND mr.mass_reval_id = v_mass_reval_id
1929 AND r.asset_id IS NULL
1930 AND mr.book_type_code = p_book;
1931
1932 v_reval_cat_cur c_reval_cat_cur%ROWTYPE;
1933
1934 CURSOR c_final_imp_asset_cur
1935 IS
1936 SELECT ID, impairment_id, imp_description, imp_class_type,
1937 imp_amount, book_type_code
1938 FROM fa_sorp_upg_impreval
1939 WHERE transaction_type = 'Impairment' AND id_type = 'A';
1940
1941 v_final_imp_asset_cur c_final_imp_asset_cur%ROWTYPE;
1942
1943 CURSOR c_final_imp_cash_cur
1944 IS
1945 SELECT ID, impairment_id, imp_description, imp_class_type,
1946 imp_amount, book_type_code
1947 FROM fa_sorp_upg_impreval
1948 WHERE transaction_type = 'Impairment' AND id_type = 'C';
1949
1950 v_final_imp_cash_cur c_final_imp_cash_cur%ROWTYPE;
1951
1952 CURSOR c_final_reval_asset_cur
1953 IS
1954 SELECT mass_reval_id, ID, reval_reason, book_type_code
1955 FROM fa_sorp_upg_impreval
1956 WHERE transaction_type = 'Revaluation' AND asset_category_id IS NULL;
1957
1958 v_final_reval_asset_cur c_final_reval_asset_cur%ROWTYPE;
1959
1960 CURSOR c_final_reval_cat_cur
1961 IS
1962 SELECT mass_reval_id, asset_category_id category_id, reval_reason,
1963 book_type_code
1964 FROM fa_sorp_upg_impreval
1965 WHERE transaction_type = 'Revaluation' AND ID IS NULL;
1966
1967 v_final_reval_cat_cur c_final_reval_cat_cur%ROWTYPE;
1968
1969 cursor c_srp_upg_impreval_hist_cur
1970 is select transaction_type ,
1971 id,
1972 name,
1973 description,
1974 asset_category_id,
1975 category_name,
1976 impairment_id,
1977 impairment_name,
1978 imp_description,
1979 imp_class_type,
1980 imp_amount,
1981 mass_reval_id,
1982 reval_percent,
1983 reval_reason,
1984 book_type_code,
1985 report_mode,
1986 id_type
1987 from fa_sorp_upg_impreval;
1988
1989 v_srp_upg_impreval_hist_cur c_srp_upg_impreval_hist_cur%rowtype;
1990
1991 l_request_id number;
1992 v_final_cnt number;
1993
1994 l_capital_amount number;
1995 l_general_fund_amount number;
1996 l_counter number;
1997
1998 BEGIN
1999 DELETE FROM fa_sorp_upg_impreval;
2000
2001 COMMIT;
2002
2003 l_request_id := fnd_global.conc_request_id;
2004
2005 p_from := 'fa_sorp_upg_impreval';
2006 p_where := ' and 1=1';
2007
2008 P_REQUEST_WHERE := ' AND REQUEST_ID <> '||l_request_id;
2009
2010
2011
2012 select count(1) into v_final_cnt
2013 from fa_sorp_upg_impreval_hist
2014 where book_type_code = p_book
2015 and report_mode = p_mode;
2016
2017 IF v_final_cnt = 0 then
2018
2019 OPEN c_imp_asset_cur;
2020
2021 LOOP
2022 FETCH c_imp_asset_cur
2023 INTO v_imp_asset_cur;
2024
2025 EXIT WHEN c_imp_asset_cur%NOTFOUND;
2026 v_impairment_id := v_imp_asset_cur.impairment_id;
2027 v_asset_id := v_imp_asset_cur.asset_id;
2028
2029 OPEN c_imp_itf_asset_cur;
2030
2031 FETCH c_imp_itf_asset_cur
2032 INTO v_imp_itf_asset_cur;
2033
2034 INSERT INTO fa_sorp_upg_impreval
2035 (transaction_type,
2036 ID,
2037 NAME,
2038 description,
2039 asset_category_id,
2040 category_name,
2041 impairment_id,
2042 impairment_name,
2043 imp_description,
2044 imp_class_type,
2045 imp_amount, book_type_code, report_mode,
2046 id_type
2047 )
2048 VALUES (v_imp_asset_cur.transaction_type,
2049 v_imp_asset_cur.asset_id,
2050 v_imp_asset_cur.asset_number,
2051 v_imp_asset_cur.asset_description,
2052 v_imp_asset_cur.asset_category_id,
2053 v_imp_asset_cur.category_name,
2054 v_imp_asset_cur.impairment_id,
2055 v_imp_asset_cur.impairment_name,
2056 v_imp_asset_cur.imp_description,
2057 v_imp_asset_cur.impair_classification_type,
2058 v_imp_itf_asset_cur.impairment_amount,
2059 p_book,
2060 p_mode,
2061 'A'
2062 );
2063
2064 CLOSE c_imp_itf_asset_cur;
2065 END LOOP;
2066
2067 CLOSE c_imp_asset_cur;
2068
2069 OPEN c_imp_cash_cur;
2070
2071 LOOP
2072 FETCH c_imp_cash_cur
2073 INTO v_imp_cash_cur;
2074
2075 EXIT WHEN c_imp_cash_cur%NOTFOUND;
2076 v_impairment_id := v_imp_cash_cur.impairment_id;
2077 v_cash_id := v_imp_cash_cur.cash_generating_unit_id;
2078
2079 OPEN c_imp_itf_cash_cur;
2080
2081 FETCH c_imp_itf_cash_cur
2082 INTO v_imp_itf_cash_cur;
2083
2084 INSERT INTO fa_sorp_upg_impreval
2085 (transaction_type,
2086 ID,
2087 NAME,
2088 description,
2089 asset_category_id,
2090 category_name,
2091 impairment_id,
2092 impairment_name,
2093 imp_description,
2094 imp_class_type,
2095 imp_amount, book_type_code, report_mode,
2096 id_type
2097 )
2098 VALUES (v_imp_cash_cur.transaction_type,
2099 v_imp_cash_cur.cash_generating_unit_id,
2100 v_imp_cash_cur.cash_generating_unit,
2101 v_imp_cash_cur.asset_description,
2102 v_imp_cash_cur.asset_category_id,
2103 v_imp_cash_cur.category_name,
2104 v_imp_cash_cur.impairment_id,
2105 v_imp_cash_cur.impairment_name,
2106 v_imp_cash_cur.imp_description,
2107 v_imp_cash_cur.impair_classification_type,
2108 v_imp_itf_cash_cur.impairment_amount, p_book, p_mode,
2109 'C'
2110 );
2111
2112 CLOSE c_imp_itf_cash_cur;
2113 END LOOP;
2114
2115 CLOSE c_imp_cash_cur;
2116
2117 OPEN c_reval_id_cur;
2118
2119 LOOP
2120 FETCH c_reval_id_cur
2121 INTO v_mass_reval_id, v_mass_description;
2122
2123 EXIT WHEN c_reval_id_cur%NOTFOUND;
2124
2125 OPEN c_reval_asset_cur;
2126
2127 LOOP
2128 FETCH c_reval_asset_cur
2129 INTO v_reval_asset_cur;
2130
2131 EXIT WHEN c_reval_asset_cur%NOTFOUND;
2132
2133 INSERT INTO fa_sorp_upg_impreval
2134 (transaction_type,
2135 ID,
2136 NAME,
2137 description,
2138 mass_reval_id, reval_percent,
2139 reval_reason, book_type_code, report_mode
2140 )
2141 VALUES (v_reval_asset_cur.transaction_type,
2142 v_reval_asset_cur.asset_id,
2143 v_reval_asset_cur.asset_number,
2144 v_reval_asset_cur.asset_description,
2145 v_mass_reval_id, v_reval_asset_cur.reval_percent,
2146 v_mass_description, p_book, p_mode
2147 );
2148 END LOOP;
2149
2150 CLOSE c_reval_asset_cur;
2151
2152 OPEN c_reval_cat_cur;
2153
2154 LOOP
2155 FETCH c_reval_cat_cur
2156 INTO v_reval_cat_cur;
2157
2158 EXIT WHEN c_reval_cat_cur%NOTFOUND;
2159
2160 INSERT INTO fa_sorp_upg_impreval
2161 (transaction_type,
2162 asset_category_id,
2163 category_name, mass_reval_id,
2164 reval_percent, reval_reason,
2165 book_type_code, report_mode
2166 )
2167 VALUES (v_reval_cat_cur.transaction_type,
2168 v_reval_cat_cur.asset_category_id,
2169 v_reval_cat_cur.category_name, v_mass_reval_id,
2170 v_reval_cat_cur.reval_percent, v_mass_description,
2171 p_book, p_mode
2172 );
2173 END LOOP;
2174
2175 CLOSE c_reval_cat_cur;
2176 END LOOP;
2177
2178 CLOSE c_reval_id_cur;
2179
2180 IF p_mode = 'FINAL'
2181 THEN
2182 OPEN c_final_imp_asset_cur;
2183
2184 LOOP
2185 FETCH c_final_imp_asset_cur
2186 INTO v_final_imp_asset_cur;
2187
2188 EXIT WHEN c_final_imp_asset_cur%NOTFOUND;
2189
2190 /*Bug# 14596931 - For records not in status PREVIEWED/POSTED there won't be any data in itf table */
2191 begin
2192 select period_counter into l_counter
2193 from fa_mc_itf_impairments
2194 where impairment_id = v_final_imp_asset_cur.impairment_id;
2195 exception
2196 when no_data_found then
2197 l_counter := 0;
2198 v_imp_deprn_asset_cur.capital_adjustment := 0;
2199 v_imp_deprn_asset_cur.general_fund := 0;
2200 end;
2201
2202 OPEN c_imp_deprn_asset_cur(p_book,v_final_imp_asset_cur.ID,l_counter);
2203 FETCH c_imp_deprn_asset_cur into v_imp_deprn_asset_cur;
2204 close c_imp_deprn_asset_cur;
2205
2206 l_capital_amount := v_final_imp_asset_cur.imp_amount+nvl(v_imp_deprn_asset_cur.capital_adjustment,0);
2207 l_general_fund_amount := v_final_imp_asset_cur.imp_amount+nvl(v_imp_deprn_asset_cur.general_fund,0);
2208
2209 UPDATE FA_MC_IMPAIRMENTS
2210 SET reason = v_final_imp_asset_cur.imp_description,
2211 impair_class = v_final_imp_asset_cur.imp_class_type,
2212 split_impair_flag = 'N'
2213 WHERE book_type_code = p_book
2214 AND impairment_id = v_final_imp_asset_cur.impairment_id
2215 AND asset_id = v_final_imp_asset_cur.ID;
2216
2217 UPDATE FA_MC_ITF_IMPAIRMENTS
2218 SET capital_adjustment = l_capital_amount,
2219 general_fund = l_general_fund_amount,
2220 split_impair_flag = 'N'
2221 WHERE book_type_code = p_book
2222 AND impairment_id = v_final_imp_asset_cur.impairment_id
2223 AND asset_id = v_final_imp_asset_cur.ID;
2224 END LOOP;
2225
2226 CLOSE c_final_imp_asset_cur;
2227
2228 OPEN c_final_imp_cash_cur;
2229
2230 LOOP
2231 FETCH c_final_imp_cash_cur
2232 INTO v_final_imp_cash_cur;
2233
2234 EXIT WHEN c_final_imp_cash_cur%NOTFOUND;
2235
2236 UPDATE FA_MC_IMPAIRMENTS
2237 SET reason = v_final_imp_cash_cur.imp_description,
2238 impair_class = v_final_imp_cash_cur.imp_class_type,
2239 split_impair_flag = 'N'
2240 WHERE book_type_code = p_book
2241 AND impairment_id = v_final_imp_cash_cur.impairment_id
2242 AND cash_generating_unit_id = v_final_imp_cash_cur.ID;
2243
2244 UPDATE FA_MC_ITF_IMPAIRMENTS
2245 SET capital_adjustment = v_final_imp_cash_cur.imp_amount,
2246 general_fund = v_final_imp_cash_cur.imp_amount,
2247 split_impair_flag = 'N'
2248 WHERE book_type_code = p_book
2249 AND impairment_id = v_final_imp_cash_cur.impairment_id
2250 AND cash_generating_unit_id = v_final_imp_cash_cur.ID;
2251 END LOOP;
2252
2253 CLOSE c_final_imp_cash_cur;
2254
2255 OPEN c_final_reval_asset_cur;
2256
2257 LOOP
2258 FETCH c_final_reval_asset_cur
2259 INTO v_final_reval_asset_cur;
2260
2261 EXIT WHEN c_final_reval_asset_cur%NOTFOUND;
2262
2263 UPDATE fa_mass_revaluation_rules
2264 SET reason = v_final_reval_asset_cur.reval_reason,
2265 value_type = 'PER',
2266 book_type_code = p_book
2267 WHERE mass_reval_id = v_final_reval_asset_cur.mass_reval_id
2268 AND asset_id = v_final_reval_asset_cur.ID;
2269 END LOOP;
2270
2271 CLOSE c_final_reval_asset_cur;
2272
2273 OPEN c_final_reval_cat_cur;
2274
2275 LOOP
2276 FETCH c_final_reval_cat_cur
2277 INTO v_final_reval_cat_cur;
2278
2279 EXIT WHEN c_final_reval_cat_cur%NOTFOUND;
2280
2281 UPDATE fa_mass_revaluation_rules
2282 SET reason = v_final_reval_cat_cur.reval_reason,
2283 value_type = 'PER',
2284 book_type_code = p_book
2285 WHERE mass_reval_id = v_final_reval_cat_cur.mass_reval_id
2286 AND category_id = v_final_reval_cat_cur.category_id;
2287 END LOOP;
2288
2289 CLOSE c_final_reval_cat_cur;
2290 END IF;
2291
2292 IF p_mode = 'FINAL' then
2293
2294
2295
2296
2297 open c_srp_upg_impreval_hist_cur;
2298 loop
2299 fetch c_srp_upg_impreval_hist_cur into v_srp_upg_impreval_hist_cur;
2300 exit when c_srp_upg_impreval_hist_cur%notfound;
2301
2302 INSERT INTO fa_sorp_upg_impreval_hist
2303 (transaction_type ,
2304 id,
2305 name,
2306 description,
2307 asset_category_id,
2308 category_name,
2309 impairment_id,
2310 impairment_name,
2311 imp_description,
2312 imp_class_type,
2313 imp_amount,
2314 mass_reval_id,
2315 reval_percent,
2316 reval_reason,
2317 book_type_code,
2318 report_mode,
2319 id_type,
2320 request_id)
2321 VALUES
2322 (v_srp_upg_impreval_hist_cur.transaction_type ,
2323 v_srp_upg_impreval_hist_cur.id,
2324 v_srp_upg_impreval_hist_cur.name,
2325 v_srp_upg_impreval_hist_cur.description,
2326 v_srp_upg_impreval_hist_cur.asset_category_id,
2327 v_srp_upg_impreval_hist_cur.category_name,
2328 v_srp_upg_impreval_hist_cur.impairment_id,
2329 v_srp_upg_impreval_hist_cur.impairment_name,
2330 v_srp_upg_impreval_hist_cur.imp_description,
2331 v_srp_upg_impreval_hist_cur.imp_class_type,
2332 v_srp_upg_impreval_hist_cur.imp_amount,
2333 v_srp_upg_impreval_hist_cur.mass_reval_id,
2334 v_srp_upg_impreval_hist_cur.reval_percent,
2335 v_srp_upg_impreval_hist_cur.reval_reason,
2336 v_srp_upg_impreval_hist_cur.book_type_code,
2337 v_srp_upg_impreval_hist_cur.report_mode,
2338 v_srp_upg_impreval_hist_cur.id_type,
2339 l_request_id);
2340 end loop;
2341 close c_srp_upg_impreval_hist_cur;
2342 END IF;
2343 ELSE -- v_final_cnt = 0
2344 p_from := 'fa_sorp_upg_impreval_hist';
2345 p_where := ' and book_type_code = '||''''||p_book||''''||' and report_mode ='||''''||p_mode||'''';
2346 END IF;
2347
2348
2349 COMMIT;
2350 RETURN TRUE;
2351 EXCEPTION
2352 WHEN OTHERS
2353 THEN
2354 ROLLBACK;
2355 RETURN FALSE;
2356 END fa_sorp_upg_impreval_mc_fn;
2357
2358 FUNCTION fa_sorp_upg_impreval_fn(p_book VARCHAR2, p_mode VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
2359 RETURN BOOLEAN
2360 IS
2361 v_impairment_id NUMBER;
2362 v_mass_reval_id NUMBER;
2363 v_mass_description VARCHAR2(250);
2364 v_description VARCHAR2(100);
2365 v_asset_id NUMBER;
2366 v_cash_id NUMBER;
2367
2368 v_boolean boolean;
2369
2370 CURSOR c_imp_asset_cur IS SELECT 'Impairment' transaction_type,
2371 i.asset_id,
2372 a.asset_number,
2373 a.description asset_description, a.asset_category_id,
2374 a.attribute_category_code category_name, i.impairment_id,
2375 i.impairment_name,
2376 NVL (i.description, 'Others') imp_description,
2377 -- Bug#7704219
2378 NVL (
2379 decode(SUBSTR (i.description, 1, 3),'CPP','CPP',
2380 'CEB','CEB',
2381 'OTH'),
2382 'OTH'
2383 ) impair_classification_type
2384 FROM fa_additions_v a, fa_impairments i
2385 WHERE a.asset_id = i.asset_id AND i.book_type_code = p_book;
2386
2387 v_imp_asset_cur c_imp_asset_cur%ROWTYPE;
2388
2389 CURSOR c_imp_itf_asset_cur
2390 IS
2391 SELECT impairment_amount
2392 FROM fa_itf_impairments
2393 WHERE impairment_id = v_impairment_id
2394 AND asset_id = v_asset_id
2395 AND book_type_code = p_book;
2396
2397 v_imp_itf_asset_cur c_imp_itf_asset_cur%ROWTYPE;
2398
2399 cursor c_imp_deprn_asset_cur(l_book varchar2,
2400 l_asset_id number,
2401 l_period_counter number)
2402 is
2403 select capital_adjustment,
2404 general_fund
2405 from fa_deprn_summary
2406 where book_type_code = l_book
2407 and asset_id = l_asset_id
2408 and period_counter = (select max(period_counter)
2409 from fa_deprn_summary
2410 where book_type_code = l_book
2411 and asset_id = l_asset_id
2412 and period_counter < l_period_counter);
2413
2414 v_imp_deprn_asset_cur c_imp_deprn_asset_cur%rowtype;
2415
2416
2417 CURSOR c_imp_cash_cur
2418 IS
2419 SELECT 'Impairment' transaction_type, i.cash_generating_unit_id,
2420 a.cash_generating_unit, a.description asset_description,
2421 NULL asset_category_id, NULL category_name, i.impairment_id,
2422 i.impairment_name,
2423 NVL (i.description, 'Others') imp_description,
2424 NVL (SUBSTR (i.description, 1, 3),
2425 'OTH'
2426 ) impair_classification_type
2427 FROM fa_cash_gen_units a, fa_impairments i
2428 WHERE a.cash_generating_unit_id = i.cash_generating_unit_id
2429 AND i.book_type_code = p_book;
2430
2431 v_imp_cash_cur c_imp_cash_cur%ROWTYPE;
2432
2433 CURSOR c_imp_itf_cash_cur
2434 IS
2435 SELECT impairment_amount
2436 FROM fa_itf_impairments
2437 WHERE impairment_id = v_impairment_id
2438 AND cash_generating_unit_id = v_cash_id
2439 AND book_type_code = p_book;
2440
2441 v_imp_itf_cash_cur c_imp_itf_cash_cur%ROWTYPE;
2442
2443 CURSOR c_reval_id_cur
2444 IS
2445 SELECT DISTINCT mass_reval_id, description
2446 FROM fa_mass_revaluations
2447 WHERE book_type_code = p_book;
2448
2449 -- Bug#7578069 cursor queries for c_reval_asset_cur and c_reval_cat_cur modified
2450
2451 CURSOR c_reval_asset_cur
2452 IS
2453 SELECT 'Revaluation' transaction_type, r.asset_id, a.asset_number,
2454 a.description asset_description, r.mass_reval_id,
2455 r.reval_percent
2456 FROM fa_additions_v a, fa_mass_revaluation_rules r,fa_mass_revaluations mr
2457 WHERE a.asset_id = r.asset_id
2458 AND r.mass_reval_id = mr.mass_reval_id
2459 AND mr.mass_reval_id = v_mass_reval_id
2460 AND r.category_id IS NULL
2461 AND mr.book_type_code = p_book;
2462
2463 v_reval_asset_cur c_reval_asset_cur%ROWTYPE;
2464
2465 CURSOR c_reval_cat_cur
2466 IS
2467 SELECT distinct 'Revaluation' transaction_type,
2468 r.category_id asset_category_id,
2469 a.attribute_category_code category_name, r.mass_reval_id,
2470 r.reval_percent
2471 FROM fa_additions_v a, fa_mass_revaluation_rules r,fa_mass_revaluations mr
2472 WHERE a.asset_category_id = r.category_id
2473 AND r.mass_reval_id = mr.mass_reval_id
2474 AND mr.mass_reval_id = v_mass_reval_id
2475 AND r.asset_id IS NULL
2476 AND mr.book_type_code = p_book;
2477
2478 v_reval_cat_cur c_reval_cat_cur%ROWTYPE;
2479
2480 CURSOR c_final_imp_asset_cur
2481 IS
2482 SELECT ID, impairment_id, imp_description, imp_class_type,
2483 imp_amount, book_type_code
2484 FROM fa_sorp_upg_impreval
2485 WHERE transaction_type = 'Impairment' AND id_type = 'A';
2486
2487 v_final_imp_asset_cur c_final_imp_asset_cur%ROWTYPE;
2488
2489 CURSOR c_final_imp_cash_cur
2490 IS
2491 SELECT ID, impairment_id, imp_description, imp_class_type,
2492 imp_amount, book_type_code
2493 FROM fa_sorp_upg_impreval
2494 WHERE transaction_type = 'Impairment' AND id_type = 'C';
2495
2496 v_final_imp_cash_cur c_final_imp_cash_cur%ROWTYPE;
2497
2498 CURSOR c_final_reval_asset_cur
2499 IS
2500 SELECT mass_reval_id, ID, reval_reason, book_type_code
2501 FROM fa_sorp_upg_impreval
2502 WHERE transaction_type = 'Revaluation' AND asset_category_id IS NULL;
2503
2504 v_final_reval_asset_cur c_final_reval_asset_cur%ROWTYPE;
2505
2506 CURSOR c_final_reval_cat_cur
2507 IS
2508 SELECT mass_reval_id, asset_category_id category_id, reval_reason,
2509 book_type_code
2510 FROM fa_sorp_upg_impreval
2511 WHERE transaction_type = 'Revaluation' AND ID IS NULL;
2512
2513 v_final_reval_cat_cur c_final_reval_cat_cur%ROWTYPE;
2514
2515 cursor c_srp_upg_impreval_hist_cur
2516 is select transaction_type ,
2517 id,
2518 name,
2519 description,
2520 asset_category_id,
2521 category_name,
2522 impairment_id,
2523 impairment_name,
2524 imp_description,
2525 imp_class_type,
2526 imp_amount,
2527 mass_reval_id,
2528 reval_percent,
2529 reval_reason,
2530 book_type_code,
2531 report_mode,
2532 id_type
2533 from fa_sorp_upg_impreval;
2534
2535 v_srp_upg_impreval_hist_cur c_srp_upg_impreval_hist_cur%rowtype;
2536
2537 l_request_id number;
2538 v_final_cnt number;
2539
2540 l_capital_amount number;
2541 l_general_fund_amount number;
2542 l_counter number;
2543
2544 BEGIN
2545
2546 if not fa_sorp_upg_mc_flag(p_book) then
2547
2548 DELETE FROM fa_sorp_upg_impreval;
2549
2550 COMMIT;
2551
2552 l_request_id := fnd_global.conc_request_id;
2553
2554 p_from := 'fa_sorp_upg_impreval';
2555 p_where := ' and 1=1';
2556
2557 P_REQUEST_WHERE := ' AND REQUEST_ID <> '||l_request_id;
2558
2559
2560
2561 select count(1) into v_final_cnt
2562 from fa_sorp_upg_impreval_hist
2563 where book_type_code = p_book
2564 and report_mode = p_mode;
2565
2566 IF v_final_cnt = 0 then
2567
2568 OPEN c_imp_asset_cur;
2569
2570 LOOP
2571 FETCH c_imp_asset_cur
2572 INTO v_imp_asset_cur;
2573
2574 EXIT WHEN c_imp_asset_cur%NOTFOUND;
2575 v_impairment_id := v_imp_asset_cur.impairment_id;
2576 v_asset_id := v_imp_asset_cur.asset_id;
2577
2578 OPEN c_imp_itf_asset_cur;
2579
2580 FETCH c_imp_itf_asset_cur
2581 INTO v_imp_itf_asset_cur;
2582
2583 INSERT INTO fa_sorp_upg_impreval
2584 (transaction_type,
2585 ID,
2586 NAME,
2587 description,
2588 asset_category_id,
2589 category_name,
2590 impairment_id,
2591 impairment_name,
2592 imp_description,
2593 imp_class_type,
2594 imp_amount, book_type_code, report_mode,
2595 id_type
2596 )
2597 VALUES (v_imp_asset_cur.transaction_type,
2598 v_imp_asset_cur.asset_id,
2599 v_imp_asset_cur.asset_number,
2600 v_imp_asset_cur.asset_description,
2601 v_imp_asset_cur.asset_category_id,
2602 v_imp_asset_cur.category_name,
2603 v_imp_asset_cur.impairment_id,
2604 v_imp_asset_cur.impairment_name,
2605 v_imp_asset_cur.imp_description,
2606 v_imp_asset_cur.impair_classification_type,
2607 v_imp_itf_asset_cur.impairment_amount,
2608 p_book,
2609 p_mode,
2610 'A'
2611 );
2612
2613 CLOSE c_imp_itf_asset_cur;
2614 END LOOP;
2615
2616 CLOSE c_imp_asset_cur;
2617
2618 OPEN c_imp_cash_cur;
2619
2620 LOOP
2621 FETCH c_imp_cash_cur
2622 INTO v_imp_cash_cur;
2623
2624 EXIT WHEN c_imp_cash_cur%NOTFOUND;
2625 v_impairment_id := v_imp_cash_cur.impairment_id;
2626 v_cash_id := v_imp_cash_cur.cash_generating_unit_id;
2627
2628 OPEN c_imp_itf_cash_cur;
2629
2630 FETCH c_imp_itf_cash_cur
2631 INTO v_imp_itf_cash_cur;
2632
2633 INSERT INTO fa_sorp_upg_impreval
2634 (transaction_type,
2635 ID,
2636 NAME,
2637 description,
2638 asset_category_id,
2639 category_name,
2640 impairment_id,
2641 impairment_name,
2642 imp_description,
2643 imp_class_type,
2644 imp_amount, book_type_code, report_mode,
2645 id_type
2646 )
2647 VALUES (v_imp_cash_cur.transaction_type,
2648 v_imp_cash_cur.cash_generating_unit_id,
2649 v_imp_cash_cur.cash_generating_unit,
2650 v_imp_cash_cur.asset_description,
2651 v_imp_cash_cur.asset_category_id,
2652 v_imp_cash_cur.category_name,
2653 v_imp_cash_cur.impairment_id,
2654 v_imp_cash_cur.impairment_name,
2655 v_imp_cash_cur.imp_description,
2656 v_imp_cash_cur.impair_classification_type,
2657 v_imp_itf_cash_cur.impairment_amount, p_book, p_mode,
2658 'C'
2659 );
2660
2661 CLOSE c_imp_itf_cash_cur;
2662 END LOOP;
2663
2664 CLOSE c_imp_cash_cur;
2665
2666 OPEN c_reval_id_cur;
2667
2668 LOOP
2669 FETCH c_reval_id_cur
2670 INTO v_mass_reval_id, v_mass_description;
2671
2672 EXIT WHEN c_reval_id_cur%NOTFOUND;
2673
2674 OPEN c_reval_asset_cur;
2675
2676 LOOP
2677 FETCH c_reval_asset_cur
2678 INTO v_reval_asset_cur;
2679
2680 EXIT WHEN c_reval_asset_cur%NOTFOUND;
2681
2682 INSERT INTO fa_sorp_upg_impreval
2683 (transaction_type,
2684 ID,
2685 NAME,
2686 description,
2687 mass_reval_id, reval_percent,
2688 reval_reason, book_type_code, report_mode
2689 )
2690 VALUES (v_reval_asset_cur.transaction_type,
2691 v_reval_asset_cur.asset_id,
2692 v_reval_asset_cur.asset_number,
2693 v_reval_asset_cur.asset_description,
2694 v_mass_reval_id, v_reval_asset_cur.reval_percent,
2695 v_mass_description, p_book, p_mode
2696 );
2697 END LOOP;
2698
2699 CLOSE c_reval_asset_cur;
2700
2701 OPEN c_reval_cat_cur;
2702
2703 LOOP
2704 FETCH c_reval_cat_cur
2705 INTO v_reval_cat_cur;
2706
2707 EXIT WHEN c_reval_cat_cur%NOTFOUND;
2708
2709 INSERT INTO fa_sorp_upg_impreval
2710 (transaction_type,
2711 asset_category_id,
2712 category_name, mass_reval_id,
2713 reval_percent, reval_reason,
2714 book_type_code, report_mode
2715 )
2716 VALUES (v_reval_cat_cur.transaction_type,
2717 v_reval_cat_cur.asset_category_id,
2718 v_reval_cat_cur.category_name, v_mass_reval_id,
2719 v_reval_cat_cur.reval_percent, v_mass_description,
2720 p_book, p_mode
2721 );
2722 END LOOP;
2723
2724 CLOSE c_reval_cat_cur;
2725 END LOOP;
2726
2727 CLOSE c_reval_id_cur;
2728
2729 IF p_mode = 'FINAL'
2730 THEN
2731 OPEN c_final_imp_asset_cur;
2732
2733 LOOP
2734 FETCH c_final_imp_asset_cur
2735 INTO v_final_imp_asset_cur;
2736
2737 EXIT WHEN c_final_imp_asset_cur%NOTFOUND;
2738 /*Bug# 14596931 - For records not in status PREVIEWED/POSTED there won't be any data in itf table */
2739 begin
2740 select period_counter into l_counter
2741 from fa_itf_impairments
2742 where impairment_id = v_final_imp_asset_cur.impairment_id;
2743 exception
2744 when no_data_found then
2745 l_counter := 0;
2746 v_imp_deprn_asset_cur.capital_adjustment := 0;
2747 v_imp_deprn_asset_cur.general_fund := 0;
2748 end;
2749 OPEN c_imp_deprn_asset_cur(p_book,v_final_imp_asset_cur.ID,l_counter);
2750 FETCH c_imp_deprn_asset_cur into v_imp_deprn_asset_cur;
2751 close c_imp_deprn_asset_cur;
2752
2753 l_capital_amount := v_final_imp_asset_cur.imp_amount+nvl(v_imp_deprn_asset_cur.capital_adjustment,0);
2754 l_general_fund_amount := v_final_imp_asset_cur.imp_amount+nvl(v_imp_deprn_asset_cur.general_fund,0);
2755
2756 UPDATE fa_impairments
2757 SET reason = v_final_imp_asset_cur.imp_description,
2758 impair_class = v_final_imp_asset_cur.imp_class_type,
2759 split_impair_flag = 'N'
2760 WHERE book_type_code = p_book
2761 AND impairment_id = v_final_imp_asset_cur.impairment_id
2762 AND asset_id = v_final_imp_asset_cur.ID;
2763
2764 UPDATE fa_itf_impairments
2765 SET capital_adjustment = l_capital_amount,
2766 general_fund = l_general_fund_amount,
2767 split_impair_flag = 'N'
2768 WHERE book_type_code = p_book
2769 AND impairment_id = v_final_imp_asset_cur.impairment_id
2770 AND asset_id = v_final_imp_asset_cur.ID;
2771 END LOOP;
2772
2773 CLOSE c_final_imp_asset_cur;
2774
2775 OPEN c_final_imp_cash_cur;
2776
2777 LOOP
2778 FETCH c_final_imp_cash_cur
2779 INTO v_final_imp_cash_cur;
2780
2781 EXIT WHEN c_final_imp_cash_cur%NOTFOUND;
2782
2783 UPDATE fa_impairments
2784 SET reason = v_final_imp_cash_cur.imp_description,
2785 impair_class = v_final_imp_cash_cur.imp_class_type,
2786 split_impair_flag = 'N'
2787 WHERE book_type_code = p_book
2788 AND impairment_id = v_final_imp_cash_cur.impairment_id
2789 AND cash_generating_unit_id = v_final_imp_cash_cur.ID;
2790
2791 UPDATE fa_itf_impairments
2792 SET capital_adjustment = v_final_imp_cash_cur.imp_amount,
2793 general_fund = v_final_imp_cash_cur.imp_amount,
2794 split_impair_flag = 'N'
2795 WHERE book_type_code = p_book
2796 AND impairment_id = v_final_imp_cash_cur.impairment_id
2797 AND cash_generating_unit_id = v_final_imp_cash_cur.ID;
2798 END LOOP;
2799
2800 CLOSE c_final_imp_cash_cur;
2801
2802 OPEN c_final_reval_asset_cur;
2803
2804 LOOP
2805 FETCH c_final_reval_asset_cur
2806 INTO v_final_reval_asset_cur;
2807
2808 EXIT WHEN c_final_reval_asset_cur%NOTFOUND;
2809
2810 UPDATE fa_mass_revaluation_rules
2811 SET reason = v_final_reval_asset_cur.reval_reason,
2812 value_type = 'PER',
2813 book_type_code = p_book
2814 WHERE mass_reval_id = v_final_reval_asset_cur.mass_reval_id
2815 AND asset_id = v_final_reval_asset_cur.ID;
2816 END LOOP;
2817
2818 CLOSE c_final_reval_asset_cur;
2819
2820 OPEN c_final_reval_cat_cur;
2821
2822 LOOP
2823 FETCH c_final_reval_cat_cur
2824 INTO v_final_reval_cat_cur;
2825
2826 EXIT WHEN c_final_reval_cat_cur%NOTFOUND;
2827
2828 UPDATE fa_mass_revaluation_rules
2829 SET reason = v_final_reval_cat_cur.reval_reason,
2830 value_type = 'PER',
2831 book_type_code = p_book
2832 WHERE mass_reval_id = v_final_reval_cat_cur.mass_reval_id
2833 AND category_id = v_final_reval_cat_cur.category_id;
2834 END LOOP;
2835
2836 CLOSE c_final_reval_cat_cur;
2837 END IF;
2838
2839 IF p_mode = 'FINAL' then
2840
2841
2842
2843
2844 open c_srp_upg_impreval_hist_cur;
2845 loop
2846 fetch c_srp_upg_impreval_hist_cur into v_srp_upg_impreval_hist_cur;
2847 exit when c_srp_upg_impreval_hist_cur%notfound;
2848
2849 INSERT INTO fa_sorp_upg_impreval_hist
2850 (transaction_type ,
2851 id,
2852 name,
2853 description,
2854 asset_category_id,
2855 category_name,
2856 impairment_id,
2857 impairment_name,
2858 imp_description,
2859 imp_class_type,
2860 imp_amount,
2861 mass_reval_id,
2862 reval_percent,
2863 reval_reason,
2864 book_type_code,
2865 report_mode,
2866 id_type,
2867 request_id)
2868 VALUES
2869 (v_srp_upg_impreval_hist_cur.transaction_type ,
2870 v_srp_upg_impreval_hist_cur.id,
2871 v_srp_upg_impreval_hist_cur.name,
2872 v_srp_upg_impreval_hist_cur.description,
2873 v_srp_upg_impreval_hist_cur.asset_category_id,
2874 v_srp_upg_impreval_hist_cur.category_name,
2875 v_srp_upg_impreval_hist_cur.impairment_id,
2876 v_srp_upg_impreval_hist_cur.impairment_name,
2877 v_srp_upg_impreval_hist_cur.imp_description,
2878 v_srp_upg_impreval_hist_cur.imp_class_type,
2879 v_srp_upg_impreval_hist_cur.imp_amount,
2880 v_srp_upg_impreval_hist_cur.mass_reval_id,
2881 v_srp_upg_impreval_hist_cur.reval_percent,
2882 v_srp_upg_impreval_hist_cur.reval_reason,
2883 v_srp_upg_impreval_hist_cur.book_type_code,
2884 v_srp_upg_impreval_hist_cur.report_mode,
2885 v_srp_upg_impreval_hist_cur.id_type,
2886 l_request_id);
2887 end loop;
2888 close c_srp_upg_impreval_hist_cur;
2889 END IF;
2890 ELSE -- v_final_cnt = 0
2891 p_from := 'fa_sorp_upg_impreval_hist';
2892 p_where := ' and book_type_code = '||''''||p_book||''''||' and report_mode ='||''''||p_mode||'''';
2893 END IF;
2894
2895
2896 COMMIT;
2897 RETURN TRUE;
2898
2899 else
2900
2901 v_boolean := fa_sorp_upg_impreval_mc_fn(p_book,p_mode);
2902 IF V_BOOLEAN THEN
2903 RETURN TRUE;
2904 ELSE
2905 RETURN FALSE;
2906 END IF;
2907
2908 end if;
2909
2910 EXCEPTION
2911 WHEN OTHERS
2912 THEN
2913 ROLLBACK;
2914 RETURN FALSE;
2915 END fa_sorp_upg_impreval_fn;
2916
2917
2918 END fa_sorp_upg_pkg;