[Home] [Help]
PACKAGE BODY: APPS.FA_SORP_REVALUATION_PKG
Source
1 PACKAGE BODY fa_sorp_revaluation_pkg AS
2 /* $Header: FAVSRVB.pls 120.11 2011/05/19 13:15:03 gigupta noship $ */
3
4 g_temp_integer binary_integer;
5 g_temp_number number;
6
7 /*Bug#7392015 added following fucntion which will calculate deprn effect for double db
8 depreciation method */
9 Function fa_sorp_link_reval_dd(
10 p_mass_reval_id IN NUMBER,
11 p_asset_id IN NUMBER,
12 p_book_type_code IN VARCHAR2,
13 p_impairment_id IN NUMBER,
14 p_unused_imp_amount IN NUMBER,
15 p_mrc_sob_type_code IN VARCHAR2,
16 p_set_of_books_id IN NUMBER,
17 x_deprn_rsv OUT NOCOPY NUMBER,
18 x_impairment_amt OUT NOCOPY NUMBER,
19 x_impair_split_flag OUT NOCOPY VARCHAR2,
20 x_override_flag OUT NOCOPY VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
21
22 l_dpr_in FA_STD_TYPES.dpr_struct;
23 l_dpr_out FA_STD_TYPES.dpr_out_struct;
24 l_dpr_arr FA_STD_TYPES.dpr_arr_type;
25 l_dpr_row FA_STD_TYPES.FA_DEPRN_ROW_STRUCT;
26 l_status boolean;
27 l_running_mode VARCHAR2(20);
28 l_calling_fn varchar2(60) := 'fa_sorp_link_reval_dd';
29
30 l_period_counter_impaired NUMBER:=0;
31 l_impairment_date date;
32 l_split_impair_flag varchar2(3);
33 l_deprn_rsv_out number:=0;
34 L_LINKED_IMPAIR_AMOUNT number :=0;
35 l_impairment_amount number :=0;
36 l_split_impair_amt number:=0;
37 l_reval_expense number :=0;
38 l_count number :=0;
39 l_reval_amort_deprn_exp number :=0;
40 l_period_counter_revalued number :=0;
41
42 CURSOR c_impair_period_counter is
43 select PERIOD_COUNTER,IMPAIRMENT_DATE,
44 impairment_amount,
45 decode(SPLIT_IMPAIR_FLAG,'N','NO','Y','YES','NO'),
46 DECODE(INSTR (p_impairment_id, '.'),'0',impairment_amount,
47 DECODE (SUBSTR (p_impairment_id, -1,LENGTH (p_impairment_id)),
48 '1', split1_loss_amount,
49 '2', split2_loss_amount,
50 '3', split3_loss_amount
51 )) split_impairm_amount
52 from fa_ITF_impairments
53 where asset_id = p_asset_id and
54 impairment_id=fa_sorp_process_imp_id_fn(p_impairment_id) and
55 book_type_code=p_book_type_code;
56
57 CURSOR c_get_old_fin_rec is
58 select * from
59 fa_books
60 where book_type_code = p_book_type_code and
61 asset_id = p_asset_id and
62 transaction_header_id_out = (select transaction_header_id
63 from
64 fa_transaction_headers
65 where MASS_TRANSACTION_ID = fa_sorp_process_imp_id_fn(p_impairment_id));
66
67
68 CURSOR c_get_period_rec (c_start_date date) IS
69 select cp.period_num
70 , fy.fiscal_year
71 from fa_fiscal_year fy
72 , fa_calendar_periods cp
73 where cp.calendar_type = fa_cache_pkg.fazcbc_record.deprn_calendar
74 and fy.fiscal_year_name = fa_cache_pkg.fazcbc_record.fiscal_year_name
75 and cp.start_date between fy.start_date and fy.end_date
76 and c_start_date between cp.start_date and cp.end_date;
77
78 CURSOR c_get_open_period_rec IS
79 select period_num,fiscal_year,PERIOD_COUNTER
80 from fa_deprn_periods
81 where book_type_code = p_book_type_code
82 and PERIOD_CLOSE_DATE is null;
83
84 Cursor c_get_deprn_rsv_out(c_period_counter_impaired number) is
85 select SUM(decode(ADJUSTMENT_TYPE,'RESERVE',adjustment_amount,0)),
86 sum(decode(ADJUSTMENT_TYPE,'EXPENSE',adjustment_amount,0)),
87 PERIOD_COUNTER_CREATED
88 from fa_adjustments
89 where asset_id = p_asset_id AND
90 book_type_code = p_book_type_code AND
91 PERIOD_COUNTER_CREATED >= c_period_counter_impaired and
92 SOURCE_TYPE_CODE='REVALUATION'
93 group by PERIOD_COUNTER_CREATED;
94
95 CURSOR c_get_reval_expenses(c_period_counter_impaired number) is
96 select sum(REVAL_DEPRN_EXPENSE),count(*)
97 from fa_deprn_summary
98 where asset_id = p_asset_id and
99 PERIOD_COUNTER >= c_period_counter_impaired
100 and book_type_code=p_book_type_code;
101 l_c_get_old_fin_rec c_get_old_fin_rec%rowtype;
102
103 begin
104
105 if (p_log_level_rec.statement_level) then
106 fa_debug_pkg.ADD (l_calling_fn,'BEGINS','1', p_log_level_rec => p_log_level_rec);
107 fa_debug_pkg.ADD (l_calling_fn,'p_mass_reval_id',p_mass_reval_id, p_log_level_rec => p_log_level_rec);
108 fa_debug_pkg.ADD (l_calling_fn,'p_asset_id',p_asset_id, p_log_level_rec => p_log_level_rec);
109 fa_debug_pkg.ADD (l_calling_fn,'p_book_type_code',p_book_type_code, p_log_level_rec => p_log_level_rec);
110 fa_debug_pkg.ADD (l_calling_fn,'p_impairment_id',p_impairment_id, p_log_level_rec => p_log_level_rec);
111 fa_debug_pkg.ADD (l_calling_fn,'p_unused_imp_amount',p_unused_imp_amount, p_log_level_rec => p_log_level_rec);
112 end if;
113
114 open c_impair_period_counter;
115 fetch c_impair_period_counter into l_period_counter_impaired,
116 l_impairment_date,
117 l_impairment_amount,
118 l_split_impair_flag,
119 l_split_impair_amt;
120 close c_impair_period_counter;
121
122 if (p_log_level_rec.statement_level) then
123 fa_debug_pkg.ADD (l_calling_fn,'period_counter_impaired',l_period_counter_impaired, p_log_level_rec => p_log_level_rec);
124 fa_debug_pkg.ADD (l_calling_fn,'l_impairment_date',l_impairment_date, p_log_level_rec => p_log_level_rec);
125 fa_debug_pkg.ADD (l_calling_fn,'l_split_impair_flag',l_split_impair_flag, p_log_level_rec => p_log_level_rec);
126 fa_debug_pkg.ADD (l_calling_fn,'l_impairment_amount',l_impairment_amount, p_log_level_rec => p_log_level_rec);
127 end if;
128 x_impair_split_flag := l_split_impair_flag;
129 /*+++++++ Populating l_dpr_in to call faxcde ++++++++++*/
130 l_dpr_in.calendar_type := fa_cache_pkg.fazcbc_record.deprn_calendar;
131 l_dpr_in.book := p_book_type_code;
132 l_dpr_in.asset_id := p_asset_id;
133
134 l_dpr_row.asset_id := p_asset_id;
135 l_dpr_row.book := p_book_type_code;
136 l_dpr_row.period_ctr := l_period_counter_impaired;
137 l_dpr_row.dist_id := 0;
138 l_dpr_row.mrc_sob_type_code := 'P';
139
140 if (p_log_level_rec.statement_level) then
141 fa_debug_pkg.add(l_calling_fn, 'Calling', 'query_balances_int', p_log_level_rec => p_log_level_rec);
142 end if;
143 l_running_mode := 'STANDARD';
144 fa_query_balances_pkg.query_balances_int(
145 X_DPR_ROW => l_dpr_row,
146 X_RUN_MODE => l_running_mode,
147 X_DEBUG => FALSE,
148 X_SUCCESS => l_status,
149 X_CALLING_FN => l_calling_fn,
150 X_TRANSACTION_HEADER_ID => -1, p_log_level_rec => p_log_level_rec);
151
152 if (NOT l_status) then
153
154 if (p_log_level_rec.statement_level) then
155 fa_debug_pkg.add(l_calling_fn, 'ERROR',
156 'Calling fa_query_balances_pkg.query_balances_int', p_log_level_rec => p_log_level_rec);
157 end if;
158
159 --raise dpr_err;
160 end if;
161
162 if (p_log_level_rec.statement_level) then
163 fa_debug_pkg.add(l_calling_fn, 'l_dpr_row.deprn_adjust_exp', l_dpr_row.deprn_adjust_exp, p_log_level_rec => p_log_level_rec);
164 fa_debug_pkg.add(l_calling_fn, 'l_dpr_row.deprn_exp', l_dpr_row.deprn_exp, p_log_level_rec => p_log_level_rec);
165 fa_debug_pkg.add(l_calling_fn, 'l_dpr_row.deprn_rsv', l_dpr_row.deprn_rsv, p_log_level_rec => p_log_level_rec);
166 end if;
167
168 open c_get_old_fin_rec;
169 fetch c_get_old_fin_rec into l_c_get_old_fin_rec;
170 close c_get_old_fin_rec;
171
172 l_dpr_in.rec_cost := l_c_get_old_fin_rec.recoverable_cost;
173 l_dpr_in.salvage_value := l_c_get_old_fin_rec.salvage_value;
174 l_dpr_in.adj_rec_cost := l_c_get_old_fin_rec.adjusted_recoverable_cost;
175 l_dpr_in.adj_cost := l_c_get_old_fin_rec.adjusted_cost;
176 l_dpr_in.formula_factor := l_c_get_old_fin_rec.formula_factor;
177 l_dpr_in.rate_adj_factor := l_c_get_old_fin_rec.rate_adjustment_factor;
178 l_dpr_in.eofy_reserve := l_c_get_old_fin_rec.eofy_reserve;
179
180 l_dpr_in.method_code := l_c_get_old_fin_rec.deprn_method_code;
181 if(l_c_get_old_fin_rec.deprn_method_code = 'STL') then
182 x_override_flag := 'NO';
183 else
184 x_override_flag := 'YES';
185 end if;
186 l_dpr_in.life := l_c_get_old_fin_rec.life_in_months;
187 l_dpr_in.reval_amo_basis := l_c_get_old_fin_rec.reval_amortization_basis;
188
189 l_dpr_in.jdate_in_service := to_number(to_char(l_c_get_old_fin_rec.date_placed_in_service, 'J'));
190 l_dpr_in.prorate_jdate := to_number(to_char(l_c_get_old_fin_rec.prorate_date, 'J'));
191 l_dpr_in.deprn_start_jdate := to_number(to_char(l_c_get_old_fin_rec.deprn_start_date, 'J'));
192 l_dpr_in.prorate_date := l_c_get_old_fin_rec.prorate_date;
193 l_dpr_in.orig_deprn_start_date := l_c_get_old_fin_rec.original_deprn_start_date;
194
195 l_dpr_in.jdate_retired := 0;
196 l_dpr_in.ret_prorate_jdate := 0;
197
198 l_dpr_in.ltd_prod := l_dpr_row.ltd_prod;
199 l_dpr_in.ytd_deprn := l_dpr_row.ytd_deprn;
200 l_dpr_in.deprn_rsv := l_dpr_row.deprn_rsv;
201 l_dpr_in.reval_rsv := l_dpr_row.reval_rsv;
202 l_dpr_in.bonus_deprn_exp := 0;
203 l_dpr_in.bonus_ytd_deprn := l_dpr_row.bonus_ytd_deprn;
204 l_dpr_in.bonus_deprn_rsv := l_dpr_row.bonus_deprn_rsv;
205 l_dpr_in.prior_fy_exp := l_dpr_row.prior_fy_exp;
206 l_dpr_in.prior_fy_bonus_exp := l_dpr_row.prior_fy_bonus_exp;
207 l_dpr_in.impairment_exp := 0;
208 l_dpr_in.ytd_impairment := 0;--l_dpr_row.ytd_impairment;
209 l_dpr_in.impairment_rsv := 0;--l_dpr_row.impairment_rsv;
210 l_dpr_in.deprn_override_flag := fa_std_types.FA_NO_OVERRIDE;
211 l_dpr_in.rsv_known_flag := TRUE;
212 l_dpr_in.deprn_rounding_flag := 'ADJ';
213 l_dpr_in.used_by_adjustment := FALSE;
214 l_dpr_in.capital_adjustment := l_dpr_row.capital_adjustment;
215 l_dpr_in.general_fund := l_dpr_row.general_fund;
216 l_dpr_in.set_of_books_id := p_set_of_books_id;
217 l_running_mode := fa_std_types.FA_DPR_NORMAL;
218
219 if (p_log_level_rec.statement_level) then
220 fa_debug_pkg.add(l_calling_fn, 'Calling', 'fa_cache_pkg.fazccp', p_log_level_rec => p_log_level_rec);
221 end if;
222
223 if not fa_cache_pkg.fazccp(fa_cache_pkg.fazcbc_record.prorate_calendar,
224 fa_cache_pkg.fazcbc_record.fiscal_year_name,
225 l_dpr_in.prorate_jdate,
226 g_temp_number,
227 l_dpr_in.y_begin,
228 g_temp_integer, p_log_level_rec => p_log_level_rec) then
229 if (p_log_level_rec.statement_level) then
230 fa_debug_pkg.add(l_calling_fn, 'Error calling',
231 'fa_cache_pkg.fazccp', p_log_level_rec => p_log_level_rec);
232 fa_debug_pkg.add(l_calling_fn, 'fa_cache_pkg.fazcbc_record.prorate_calendar',
233 fa_cache_pkg.fazcbc_record.prorate_calendar, p_log_level_rec => p_log_level_rec);
234 fa_debug_pkg.add(l_calling_fn, 'fa_cache_pkg.fazcbc_record.fiscal_year_name',
235 fa_cache_pkg.fazcbc_record.fiscal_year_name, p_log_level_rec => p_log_level_rec);
236
237 end if;
238
239 --raise dpr_err;
240 end if;
241
242 open c_get_period_rec(l_impairment_date);
243 fetch c_get_period_rec into l_dpr_in.p_cl_begin,l_dpr_in.y_begin;
244 close c_get_period_rec;
245 open c_get_open_period_rec;
246 fetch c_get_open_period_rec into l_dpr_in.p_cl_end,l_dpr_in.y_end,l_dpr_row.period_ctr;
247 close c_get_open_period_rec;
248
249 /*Bug8221363 -- Need to calculate till last period only*/
250 if l_dpr_in.p_cl_end = 1 then
251 l_dpr_in.p_cl_end := 12;
252 l_dpr_in.y_end := l_dpr_in.y_end - 1;
253 else
254 l_dpr_in.p_cl_end := l_dpr_in.p_cl_end - 1;
255 end if;
256
257 if (p_log_level_rec.statement_level) then
258 fa_debug_pkg.ADD (l_calling_fn,'before calling faxcde','1', p_log_level_rec => p_log_level_rec);
259 end if;
260 if not FA_CDE_PKG.faxcde(l_dpr_in,
261 l_dpr_arr,
262 l_dpr_out,
263 l_running_mode, p_log_level_rec => p_log_level_rec) then
264 fa_debug_pkg.ADD (l_calling_fn,'failled running','FA_CDE_PKG.faxcde', p_log_level_rec => p_log_level_rec);
265 end if;
266
267 x_deprn_rsv := l_dpr_out.new_deprn_rsv - l_dpr_row.deprn_exp;
268 if (p_log_level_rec.statement_level) then
269 fa_debug_pkg.add(l_calling_fn, 'x_deprn_rsv calculated by faxcde call', x_deprn_rsv, p_log_level_rec => p_log_level_rec);
270 fa_debug_pkg.add(l_calling_fn, 'Calling again to get current reserve', 'query_balances_int', p_log_level_rec => p_log_level_rec);
271 end if;
272
273 l_running_mode := 'STANDARD';
274 fa_query_balances_pkg.query_balances_int(
275 X_DPR_ROW => l_dpr_row,
276 X_RUN_MODE => l_running_mode,
277 X_DEBUG => FALSE,
278 X_SUCCESS => l_status,
279 X_CALLING_FN => l_calling_fn,
280 X_TRANSACTION_HEADER_ID => -1, p_log_level_rec => p_log_level_rec);
281
282 if (NOT l_status) then
283
284 if (p_log_level_rec.statement_level) then
285 fa_debug_pkg.add(l_calling_fn, 'ERROR',
286 'Calling fa_query_balances_pkg.query_balances_int', p_log_level_rec => p_log_level_rec);
287 end if;
288 --raise dpr_err;
289 end if;
290 if (p_log_level_rec.statement_level) then
291 fa_debug_pkg.add(l_calling_fn, 'l_dpr_row.deprn_adjust_exp', l_dpr_row.deprn_adjust_exp, p_log_level_rec => p_log_level_rec);
292 fa_debug_pkg.add(l_calling_fn, 'l_dpr_row.deprn_exp', l_dpr_row.deprn_exp, p_log_level_rec => p_log_level_rec);
293 fa_debug_pkg.add(l_calling_fn, 'l_dpr_row.deprn_rsv', l_dpr_row.deprn_rsv, p_log_level_rec => p_log_level_rec);
294 end if;
295
296 /* Complex logic to calculated the deper effect */
297 /* first take difference of calculated deprn and current deprn*/
298 x_deprn_rsv := (x_deprn_rsv - l_dpr_row.deprn_rsv);
299 if (p_log_level_rec.statement_level) then
300 fa_debug_pkg.add(l_calling_fn, 'deprn effect before perious partial linked reval impact (if any) :', x_deprn_rsv);
301 end if;
302
303 /* check if any partial linking has happened,if so consider deprn reserve at that point in time to
304 as it will reversed by revaluation.*/
305 open c_get_deprn_rsv_out(l_period_counter_impaired);
306 fetch c_get_deprn_rsv_out into l_deprn_rsv_out,
307 l_reval_expense,
308 l_period_counter_revalued;
309 close c_get_deprn_rsv_out;
310 x_deprn_rsv := x_deprn_rsv - nvl(l_deprn_rsv_out,0);
311 if (p_log_level_rec.statement_level) then
312 fa_debug_pkg.add(l_calling_fn, 'deprn rsv moved out because of period revaluation if any:',l_deprn_rsv_out, p_log_level_rec => p_log_level_rec);
313 fa_debug_pkg.add(l_calling_fn, 'already linked amount l_reval_expense:', l_reval_expense, p_log_level_rec => p_log_level_rec);
314 fa_debug_pkg.add(l_calling_fn, 'already linked amount l_period_counter_revalued:', l_period_counter_revalued, p_log_level_rec => p_log_level_rec);
315 fa_debug_pkg.add(l_calling_fn, 'deprn effect reverse NOT FINAL######:', x_deprn_rsv, p_log_level_rec => p_log_level_rec);
316 end if;
317
318 /*Logic to prorate deprn effect if partial linking has happened earlier */
319
320 x_impairment_amt := 0;
321 if l_split_impair_flag = 'NO' then
322 /* p_unused_imp_amount <> l_impairment_amount will be true if already partially linked*/
323 if p_unused_imp_amount <> l_impairment_amount then
324 open c_get_reval_expenses(l_period_counter_revalued);
325 fetch c_get_reval_expenses into l_reval_amort_deprn_exp,
326 l_count;
327 close c_get_reval_expenses;
328 x_deprn_rsv := x_deprn_rsv + nvl(l_reval_expense,0)*l_count;
329 if (p_log_level_rec.statement_level) then
330 fa_debug_pkg.add(l_calling_fn, 'sum of reval amortization amount after partial link', l_reval_amort_deprn_exp, p_log_level_rec => p_log_level_rec);
331 fa_debug_pkg.add(l_calling_fn, 'count no of period l_count:', l_count, p_log_level_rec => p_log_level_rec);
332 fa_debug_pkg.add(l_calling_fn, 'deprn effect reverse NOT FINAL 3######:', x_deprn_rsv, p_log_level_rec => p_log_level_rec);
333 end if;
334 /* set x_impairment_amt which will be used in calling function for calculation*/
335 x_impairment_amt := l_impairment_amount;
336 end if;
337 elsif l_split_impair_flag = 'YES' then
338 open c_get_reval_expenses(l_period_counter_revalued);
339 fetch c_get_reval_expenses into l_reval_amort_deprn_exp,
340 l_count;
341 close c_get_reval_expenses;
342 x_deprn_rsv := nvl(l_reval_expense,0)*l_count + x_deprn_rsv + l_reval_amort_deprn_exp;
343 x_deprn_rsv := (x_deprn_rsv * p_unused_imp_amount)/l_impairment_amount;
344 if (p_log_level_rec.statement_level) then
345 fa_debug_pkg.add(l_calling_fn, 'sum of reval amortization amount after partial link', l_reval_amort_deprn_exp, p_log_level_rec => p_log_level_rec);
346 fa_debug_pkg.add(l_calling_fn, 'count no of period l_count:', l_count, p_log_level_rec => p_log_level_rec);
347 fa_debug_pkg.add(l_calling_fn, 'deprn effect reverse NOT FINAL 3######:', x_deprn_rsv, p_log_level_rec => p_log_level_rec);
348 end if;
349 x_impairment_amt := l_split_impair_amt ;
350 /* set x_impairment_amt which will be used in calling function for calculation*/
351 end if;
352 /*round before return */
353 x_deprn_rsv := round(x_deprn_rsv,2);
354 if (p_log_level_rec.statement_level) then
355 fa_debug_pkg.add(l_calling_fn, 'deprn effect reverse FINAL######:', x_deprn_rsv, p_log_level_rec => p_log_level_rec);
356 end if;
357 return true;
358 end fa_sorp_link_reval_dd;
359
360
361
362
363
364
365 /* Function takes impairment id as input.
366 For split impairments impairment id is concatenated with split numbers
367 This function removes the concatenation and is used in query joins
368 */
369
370
371
372 FUNCTION fa_sorp_process_imp_id_fn (p_impairment_id NUMBER, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
373 RETURN NUMBER
374 IS
375 v_impairment_id NUMBER;
376
377 --The cursor checks if there is '.' string the impairment id.
378 --If there is no '.' string then it means no split associated with this imapirment
379 --If '.' string is present then it removes 2 chars from concatenated impairment id
380 CURSOR c_cur
381 IS
382 SELECT DECODE (NVL (INSTR (p_impairment_id, '.'), 0),
383 0, TO_NUMBER (p_impairment_id),
384 TO_NUMBER (SUBSTR (p_impairment_id,
385 1,
386 (LENGTH (p_impairment_id) - 2)
387 )
388 )
389 )
390 FROM DUAL;
391 BEGIN
392 IF p_impairment_id = 0
393 THEN
394 RETURN 0;
395 ELSE
396 OPEN c_cur;
397
398 FETCH c_cur
399 INTO v_impairment_id;
400
401 CLOSE c_cur;
402
403 RETURN v_impairment_id;
404 END IF;
405 EXCEPTION
406 WHEN OTHERS
407 THEN
408 IF p_log_level_rec.statement_level
409 THEN
410 fa_debug_pkg.ADD ('fa_sorp_process_imp_id_fn',
411 'Error Occured',
412 'YES'
413 , p_log_level_rec => p_log_level_rec);
414 END IF;
415
416 RETURN (0);
417 END fa_sorp_process_imp_id_fn;
418
419 /* This function will calculate the effect of impairment on depriciation runs
420 */
421 FUNCTION fa_imp_deprn_eff_fn (
422 p_impairment_id NUMBER,
423 p_book_type_code VARCHAR2,
424 p_asset_id NUMBER,
425 p_reval_imp_flag VARCHAR2,
426 p_amount NUMBER
427 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
428 RETURN NUMBER
429 IS
430 -- Local variables declaration
431 v_deprn_cnt NUMBER;
432 v_book_type_code FA_SORP_ASSET_LINK_REVAL_V.book_type_code%TYPE;
433 v_asset_id FA_SORP_ASSET_LINK_REVAL_V.asset_id%TYPE;
434 v_deprn_amount NUMBER;
435 v_deprn_reserve NUMBER;
436 v_period_name VARCHAR2 (25);
437 v_period_counter_impaired NUMBER;
438 l_imp_effect NUMBER;
439 l_last_period_counter NUMBER;
440 l_dpr_row_test fa_std_types.fa_deprn_row_struct;
441 l_status_test BOOLEAN;
442 l_running_mode_test VARCHAR2 (20);
443 v_deprn_delta NUMBER;
444 v_impair_loss_amount NUMBER;
445 v_reserve_adj_amount NUMBER;
446 v_imp_flag VARCHAR2 (1);
447
448 /* There are three cursors used in this function
449 All cursors check if impairment has any splits and calcuates deprn impact based on split or non split
450
451 1.c_deprn_common_cur
452 This cursor calcuates deprn impact due to impairment on whole Impairment amount.This is used to calculate Reval Gain
453 2.c_deprn_imp_amt_cur
454 This cursor calcuates deprn impact due to impairment on calculated impairment amount
455 (Total Impairment Amount - Reval Reserve Adj Amount).
456 3.c_deprn_reval_rsv_amt_cur
457 This cursor calcuates deprn impact due to impairment on any Amount
458 */
459 CURSOR c_deprn_common_cur
460 IS
461 SELECT deprn_sum.book_type_code, deprn_sum.asset_id,
462 (deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0)), deprn_sum.deprn_reserve,
463 deprn_periods.period_name, imp.period_counter_impaired,
464 DECODE
465 (INSTR (p_impairment_id, '.'),
466 '0', ROUND (( ( ( itf.impairment_amount
467 + NVL (itf.reval_reserve_adj_amount, 0)
468 )
469 * (deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0))
470 )
471 / imp.net_book_value
472 ),
473 2
474 ),
475 DECODE (SUBSTR (p_impairment_id,
476 -1,
477 LENGTH (p_impairment_id)
478 ),
479 '1', ROUND (( ( ( itf.split1_loss_amount
480 + NVL
481 (itf.split1_reval_reserve,
482 0
483 )
484 )
485 * (deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0))
486 )
487 / imp.net_book_value
488 ),
489 2
490 ),
491 '2', ROUND (( ( ( itf.split2_loss_amount
492 + NVL
493 (itf.split2_reval_reserve,
494 0
495 )
496 )
497 * (deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0))
498 )
499 / imp.net_book_value
500 ),
501 2
502 ),
503 '3', ROUND (( ( ( itf.split3_loss_amount
504 + NVL
505 (itf.split3_reval_reserve,
506 0
507 )
508 )
509 * (deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0))
510 )
511 / imp.net_book_value
512 ),
513 2
514 )
515 )
516 ) deprn_delta
517 FROM fa_deprn_summary deprn_sum,
518 fa_deprn_periods deprn_periods,
519 fa_impairments imp,
520 fa_itf_impairments itf
521 WHERE deprn_sum.book_type_code = deprn_periods.book_type_code
522 AND deprn_sum.period_counter = deprn_periods.period_counter
523 AND deprn_sum.period_counter = imp.period_counter_impaired
524 AND deprn_sum.book_type_code = imp.book_type_code
525 AND deprn_sum.asset_id = imp.asset_id
526 AND imp.impairment_id = itf.impairment_id
527 AND imp.asset_id = itf.asset_id
528 AND imp.impairment_id =
529 fa_sorp_process_imp_id_fn (p_impairment_id);
530
531 CURSOR c_deprn_imp_amt_cur
532 IS
533 SELECT deprn_sum.book_type_code, deprn_sum.asset_id,
534 (deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0)), deprn_sum.deprn_reserve,
535 deprn_periods.period_name, imp.period_counter_impaired,
536 DECODE
537 (INSTR (p_impairment_id, '.'),
538 '0', ROUND (( ( ( itf.impairment_amount
539 - ( NVL (itf.reversed_imp_amt, 0)
540 + NVL (itf.reversed_deprn_impact, 0)
541 )
542 )
543 * (deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0))
544 )
545 / imp.net_book_value
546 ),
547 2
548 ),
549 DECODE (SUBSTR (p_impairment_id,
550 -1,
551 LENGTH (p_impairment_id)
552 ),
553 '1', ROUND
554 (( ( ( itf.split1_loss_amount
555 - ( NVL (itf.reversed_imp_amt_s1,
556 0
557 )
558 + NVL
559 (itf.reversed_deprn_impact_s1,
560 0
561 )
562 )
563 )
564 * (deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0))
565 )
566 / imp.net_book_value
567 ),
568 2
569 ),
570 '2', ROUND
571 (( ( ( itf.split2_loss_amount
572 - ( NVL (itf.reversed_imp_amt_s2,
573 0
574 )
575 + NVL
576 (itf.reversed_deprn_impact_s2,
577 0
578 )
579 )
580 )
581 * (deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0))
582 )
583 / imp.net_book_value
584 ),
585 2
586 ),
587 '3', ROUND
588 (( ( ( itf.split3_loss_amount
589 - ( NVL (itf.reversed_imp_amt_s3,
590 0
591 )
592 + NVL
593 (itf.reversed_deprn_impact_s3,
594 0
595 )
596 )
597 )
598 * (deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0))
599 )
600 / imp.net_book_value
601 ),
602 2
603 )
604 )
605 ) deprn_delta
606 FROM fa_deprn_summary deprn_sum,
607 fa_deprn_periods deprn_periods,
608 fa_impairments imp,
609 fa_itf_impairments itf
610 WHERE deprn_sum.book_type_code = deprn_periods.book_type_code
611 AND deprn_sum.period_counter = deprn_periods.period_counter
612 AND deprn_sum.period_counter = imp.period_counter_impaired
613 AND deprn_sum.book_type_code = imp.book_type_code
614 AND deprn_sum.asset_id = imp.asset_id
615 AND imp.impairment_id = itf.impairment_id
616 AND imp.asset_id = itf.asset_id
617 AND imp.impairment_id =
618 fa_sorp_process_imp_id_fn (p_impairment_id);
619
620 CURSOR c_deprn_reval_rsv_amt_cur
621 IS
622 SELECT deprn_sum.book_type_code, deprn_sum.asset_id,
623 (deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0)), deprn_sum.deprn_reserve,
624 deprn_periods.period_name, imp.period_counter_impaired,
625 ROUND (( ((p_amount) * (deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0)))
626 / imp.net_book_value
627 ),
628 2
629 ) deprn_delta
630 FROM fa_deprn_summary deprn_sum,
631 fa_deprn_periods deprn_periods,
632 fa_impairments imp,
633 fa_itf_impairments itf
634 WHERE deprn_sum.book_type_code = deprn_periods.book_type_code
635 AND deprn_sum.period_counter = deprn_periods.period_counter
636 AND deprn_sum.period_counter = imp.period_counter_impaired
637 AND deprn_sum.book_type_code = imp.book_type_code
638 AND deprn_sum.asset_id = imp.asset_id
639 AND imp.impairment_id = itf.impairment_id
640 AND imp.asset_id = itf.asset_id
641 AND imp.impairment_id =
642 fa_sorp_process_imp_id_fn (p_impairment_id);
643 BEGIN
644 l_last_period_counter := fa_cache_pkg.fazcbc_record.last_period_counter;
645
646 IF p_reval_imp_flag = 'C'
647 THEN
648 OPEN c_deprn_common_cur;
649
650 FETCH c_deprn_common_cur
651 INTO v_book_type_code, v_asset_id, v_deprn_amount, v_deprn_reserve,
652 v_period_name, v_period_counter_impaired, v_deprn_delta;
653
654 --Below query finds the number of deprn runs after the impairment
655 SELECT COUNT (1)
656 INTO v_deprn_cnt
657 FROM fa_deprn_summary deprn_sum, fa_deprn_periods deprn_periods
658 WHERE deprn_sum.book_type_code = deprn_periods.book_type_code
659 AND deprn_sum.period_counter = deprn_periods.period_counter
660 AND deprn_periods.period_close_date IS NOT NULL
661 AND deprn_sum.book_type_code = p_book_type_code
662 AND deprn_sum.asset_id = p_asset_id
663 AND deprn_sum.period_counter > v_period_counter_impaired;
664
665 IF v_deprn_cnt <> 0
666 THEN
667 --Depn impact is the deprn delta multiplied by no. of deprn runs
668 l_imp_effect := (v_deprn_cnt * v_deprn_delta);
669
670 CLOSE c_deprn_common_cur;
671
672 RETURN (l_imp_effect);
673 ELSE
674 l_imp_effect := 0;
675 RETURN (l_imp_effect);
676 END IF;
677 ELSIF p_reval_imp_flag = 'I'
678 THEN
679 SELECT DECODE (INSTR (p_impairment_id, '.'),
680 '0', ROUND (NVL (itf.impairment_amount, 0), 2),
681 DECODE (SUBSTR (p_impairment_id,
682 -1,
683 LENGTH (p_impairment_id)
684 ),
685 '1', ROUND (NVL (itf.split1_loss_amount, 0),
686 2),
687 '2', ROUND (NVL (itf.split2_loss_amount, 0),
688 2),
689 '3', ROUND (NVL (itf.split3_loss_amount, 0),
690 2)
691 )
692 ) impair_loss_amount,
693 DECODE (INSTR (p_impairment_id, '.'),
694 '0', ROUND (NVL (itf.reval_reserve_adj_amount, 0), 2),
695 DECODE (SUBSTR (p_impairment_id,
696 -1,
697 LENGTH (p_impairment_id)
698 ),
699 '1', ROUND (NVL (itf.split1_reval_reserve, 0),
700 2
701 ),
702 '2', ROUND (NVL (itf.split2_reval_reserve, 0),
703 2
704 ),
705 '3', ROUND (NVL (itf.split3_reval_reserve, 0),
706 2
707 )
708 )
709 ) reserve_adj_amount
710 INTO v_impair_loss_amount,
711 v_reserve_adj_amount
712 FROM fa_itf_impairments itf
713 WHERE impairment_id = fa_sorp_process_imp_id_fn (p_impairment_id);
714
715 OPEN c_deprn_imp_amt_cur;
716
717 FETCH c_deprn_imp_amt_cur
718 INTO v_book_type_code, v_asset_id, v_deprn_amount, v_deprn_reserve,
719 v_period_name, v_period_counter_impaired, v_deprn_delta;
720
721 --Below query finds the number of deprn runs after the impairment
722 SELECT COUNT (1)
723 INTO v_deprn_cnt
724 FROM fa_deprn_summary deprn_sum, fa_deprn_periods deprn_periods
725 WHERE deprn_sum.book_type_code = deprn_periods.book_type_code
726 AND deprn_sum.period_counter = deprn_periods.period_counter
727 AND deprn_periods.period_close_date IS NOT NULL
728 AND deprn_sum.book_type_code = p_book_type_code
729 AND deprn_sum.asset_id = p_asset_id
730 AND deprn_sum.period_counter > v_period_counter_impaired;
731
732 IF v_deprn_cnt <> 0
733 THEN
734 --Depn impact is the deprn delta multiplied by no. of deprn runs
735 l_imp_effect := (v_deprn_cnt * v_deprn_delta);
736
737 CLOSE c_deprn_imp_amt_cur;
738
739 RETURN (l_imp_effect);
740 ELSE
741 l_imp_effect := 0;
742 RETURN (l_imp_effect);
743 END IF;
744 ELSIF p_reval_imp_flag = 'R'
745 THEN
746 OPEN c_deprn_reval_rsv_amt_cur;
747
748 FETCH c_deprn_reval_rsv_amt_cur
749 INTO v_book_type_code, v_asset_id, v_deprn_amount, v_deprn_reserve,
750 v_period_name, v_period_counter_impaired, v_deprn_delta;
751
752 --Below query finds the number of deprn runs after the impairment
753 SELECT COUNT (1)
754 INTO v_deprn_cnt
755 FROM fa_deprn_summary deprn_sum, fa_deprn_periods deprn_periods
756 WHERE deprn_sum.book_type_code = deprn_periods.book_type_code
757 AND deprn_sum.period_counter = deprn_periods.period_counter
758 AND deprn_periods.period_close_date IS NOT NULL
759 AND deprn_sum.book_type_code = p_book_type_code
760 AND deprn_sum.asset_id = p_asset_id
761 AND deprn_sum.period_counter > v_period_counter_impaired;
762
763 IF v_deprn_cnt <> 0
764 THEN
765 --Depn impact is the deprn delta multiplied by no. of deprn runs
766 l_imp_effect := (v_deprn_cnt * v_deprn_delta);
767
768 CLOSE c_deprn_reval_rsv_amt_cur;
769
770 RETURN (l_imp_effect);
771 ELSE
772 l_imp_effect := 0;
773 RETURN (l_imp_effect);
774 END IF;
775 END IF;
776
777 IF p_log_level_rec.statement_level
778 THEN
779 fa_debug_pkg.ADD ('fa_imp_deprn_eff_fn',
780 'Impairment Impact on deprn',
781 l_imp_effect
782 , p_log_level_rec => p_log_level_rec);
783 END IF;
784 EXCEPTION
785 WHEN OTHERS
786 THEN
787 IF p_log_level_rec.statement_level
788 THEN
789 fa_debug_pkg.ADD ('fa_imp_deprn_eff_fn', 'Error Occured', 'YES', p_log_level_rec => p_log_level_rec);
790 END IF;
791
792 RETURN (0);
793 END fa_imp_deprn_eff_fn;
794
795 FUNCTION fa_sorp_accounting (
796 p_asset_id IN NUMBER,
797 p_request_id IN NUMBER,
798 px_adj IN OUT NOCOPY fa_adjust_type_pkg.fa_adj_row_struct,
799 p_created_by IN NUMBER,
800 p_creation_date IN DATE
801 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
802 RETURN BOOLEAN
803 AS
804 pos_err EXCEPTION;
805 l_calling_fn VARCHAR2 (60) := 'fa_sorp_accounting';
806 v_imapirment_id NUMBER;
807 v_reval_reserve_impact_flag VARCHAR2 (1);
808 v_impair_loss_impact NUMBER;
809 v_reval_reserve NUMBER;
810 v_imp_deprn_effect NUMBER;
811 l_imp_deprn_effect NUMBER;
812 v_impair_loss_acct VARCHAR2 (25);
813 v_reval_fraction NUMBER;
814 v_imp_loss_fraction NUMBER;
815 v_calc_imp_reverse_amt NUMBER;
816 v_calc_imp_rev_deprn_effect NUMBER;
817 v_rsv_reverse_amt NUMBER;
818 v_rsv_reverse_deprn_effect NUMBER;
819
820 CURSOR c_sorp_link_itf_cur
821 IS
822 SELECT impairment_id, reval_reserve_impact_flag, impair_loss_impact,
823 imp_deprn_effect, NVL (impair_loss_acct, 'N'),
824 calc_imp_reverse_amt, calc_imp_reverse_deprn_effect,
825 rsv_reverse_amt, rsv_reverse_deprn_effect
826 FROM fa_sorp_link_reval_itf
827 WHERE request_id = p_request_id
828 AND asset_id = p_asset_id
829 AND run_mode = 'RUN';
830
831 v_tmp_amt NUMBER;
832 BEGIN
833 fa_debug_pkg.ADD ('SORP_ACCOUNTING', 'START', 'START', p_log_level_rec => p_log_level_rec);
834 fa_debug_pkg.ADD ('SORP_ACCOUNTING', 'p_asset_id', p_asset_id, p_log_level_rec => p_log_level_rec);
835 fa_debug_pkg.ADD ('SORP_ACCOUNTING', 'p_request_id', p_request_id, p_log_level_rec => p_log_level_rec);
836
837 OPEN c_sorp_link_itf_cur;
838
839 FETCH c_sorp_link_itf_cur
840 INTO v_imapirment_id, v_reval_reserve_impact_flag,
841 v_impair_loss_impact, v_imp_deprn_effect, v_impair_loss_acct,
842 v_calc_imp_reverse_amt, v_calc_imp_rev_deprn_effect,
843 v_rsv_reverse_amt, v_rsv_reverse_deprn_effect;
844
845 IF v_impair_loss_acct = 'N'
846 THEN
847 v_impair_loss_acct := fa_cache_pkg.fazccb_record.impair_expense_acct;
848 END IF;
849
850 fa_debug_pkg.ADD ('SORP_ACCOUNTING',
851 'v_impair_loss_acct',
852 v_impair_loss_acct
853 , p_log_level_rec => p_log_level_rec);
854 fa_debug_pkg.ADD ('SORP_ACCOUNTING',
855 'v_REVAL_RESERVE_IMPACT_FLAG',
856 v_reval_reserve_impact_flag
857 , p_log_level_rec => p_log_level_rec);
858 fa_debug_pkg.ADD ('SORP_ACCOUNTING', 'v_imapirment_id', v_imapirment_id, p_log_level_rec => p_log_level_rec);
859
860 IF NVL (v_calc_imp_reverse_amt, 0) <> 0
861 THEN
862 px_adj.adjustment_amount := ROUND ((v_calc_imp_reverse_amt), 2);
863 px_adj.adjustment_type := 'LINK IMPAIR EXP';
864 px_adj.account_type := 'IMPAIR_EXPENSE_ACCT';
865 px_adj.ACCOUNT := v_impair_loss_acct;
866 px_adj.debit_credit_flag := 'CR';
867
868 IF NOT fa_ins_adjust_pkg.faxinaj (px_adj,
869 p_creation_date,
870 p_created_by
871 , p_log_level_rec => p_log_level_rec)
872 THEN
873 RAISE pos_err;
874 END IF;
875
876 IF NOT fa_sorp_util_pvt.create_sorp_neutral_acct
877 (ROUND ((v_calc_imp_reverse_amt
878 ),
879 2
880 ),
881 'Y',
882 px_adj,
883 p_created_by,
884 p_creation_date, p_log_level_rec
885 )
886 THEN
887 RAISE pos_err;
888 END IF;
889 END IF;
890
891 IF NVL (v_calc_imp_rev_deprn_effect, 0) <> 0
892 THEN
893 px_adj.adjustment_amount := ROUND ((v_calc_imp_rev_deprn_effect), 2);
894 px_adj.adjustment_type := 'LINK IMPAIR EXP';
895 px_adj.account_type := 'IMPAIR_EXPENSE_ACCT';
896 px_adj.ACCOUNT := v_impair_loss_acct;
897 px_adj.debit_credit_flag := 'CR';
898
899 IF NOT fa_ins_adjust_pkg.faxinaj (px_adj,
900 p_creation_date,
901 p_created_by
902 , p_log_level_rec => p_log_level_rec)
903 THEN
904 RAISE pos_err;
905 END IF;
906
907 IF NOT fa_sorp_util_pvt.create_sorp_neutral_acct
908 (ROUND ((v_calc_imp_rev_deprn_effect
909 ),
910 2
911 ),
912 'Y',
913 px_adj,
914 p_created_by,
915 p_creation_date, p_log_level_rec
916 )
917 THEN
918 RAISE pos_err;
919 END IF;
920
921 px_adj.adjustment_amount := ROUND ((v_calc_imp_rev_deprn_effect), 2);
922 px_adj.adjustment_type := 'IMPAIR OFF EXP';
923 px_adj.account_type := 'DEPRN_EXPENSE_ACCT';
924 px_adj.ACCOUNT := fa_cache_pkg.fazccb_record.deprn_expense_acct;
925 px_adj.debit_credit_flag := 'DR';
926
927 IF NOT fa_ins_adjust_pkg.faxinaj (px_adj,
928 p_creation_date,
929 p_created_by
930 , p_log_level_rec => p_log_level_rec)
931 THEN
932 RAISE pos_err;
933 END IF;
934
935 IF NOT fa_sorp_util_pvt.create_sorp_neutral_acct
936 (ROUND ((v_calc_imp_rev_deprn_effect
937 ),
938 2
939 ),
940 'N',
941 px_adj,
942 p_created_by,
943 p_creation_date, p_log_level_rec
944 )
945 THEN
946 RAISE pos_err;
947 END IF;
948 END IF;
949
950 IF NVL (v_rsv_reverse_deprn_effect, 0) <> 0
951 THEN
952 px_adj.adjustment_amount := ROUND ((v_rsv_reverse_deprn_effect), 2);
953 px_adj.adjustment_type := 'IMPAIR OFF EXP';
954 px_adj.account_type := 'DEPRN_EXPENSE_ACCT';
955 px_adj.ACCOUNT := fa_cache_pkg.fazccb_record.deprn_expense_acct;
956 px_adj.debit_credit_flag := 'DR';
957
958 IF NOT fa_ins_adjust_pkg.faxinaj (px_adj,
959 p_creation_date,
960 p_created_by
961 , p_log_level_rec => p_log_level_rec)
962 THEN
963 RAISE pos_err;
964 END IF;
965
966 IF NOT fa_sorp_util_pvt.create_sorp_neutral_acct
967 (ROUND ((v_rsv_reverse_deprn_effect
968 ),
969 2
970 ),
971 'N',
972 px_adj,
973 p_created_by,
974 p_creation_date, p_log_level_rec
975 )
976 THEN
977 RAISE pos_err;
978 END IF;
979
980 px_adj.adjustment_amount := ROUND ((v_rsv_reverse_deprn_effect), 2);
981 px_adj.adjustment_type := 'REVAL RESERVE';
982 px_adj.account_type := 'REVAL_RESERVE_ACCT';
983 px_adj.ACCOUNT := fa_cache_pkg.fazccb_record.reval_reserve_acct;
984 px_adj.debit_credit_flag := 'CR';
985
986 IF NOT fa_ins_adjust_pkg.faxinaj (px_adj,
987 p_creation_date,
988 p_created_by
989 , p_log_level_rec => p_log_level_rec)
990 THEN
991 RAISE pos_err;
992 END IF;
993 END IF;
994
995 CLOSE c_sorp_link_itf_cur;
996
997 RETURN TRUE;
998 EXCEPTION
999 WHEN pos_err
1000 THEN
1001 fa_debug_pkg.ADD (l_calling_fn,
1002 'exception at sorp accounting',
1003 'pos_err'
1004 , p_log_level_rec => p_log_level_rec);
1005 fa_debug_pkg.ADD (l_calling_fn, 'sqlerrm', SUBSTRB (SQLERRM, 1, 200));
1006 RETURN FALSE;
1007 WHEN OTHERS
1008 THEN
1009 fa_debug_pkg.ADD (l_calling_fn,
1010 'exception at sorp accounting',
1011 'OTHERS'
1012 , p_log_level_rec => p_log_level_rec);
1013 fa_debug_pkg.ADD (l_calling_fn, 'sqlerrm', SUBSTRB (SQLERRM, 1, 200));
1014 RETURN FALSE;
1015 END fa_sorp_accounting;
1016
1017 /* Procedure has logic for linking revaluations with prior impairments.
1018 It handles both asset level and category level revaluation
1019 Parameter p_reval_type takes value 'A' for asset level and value 'C' category level
1020 */
1021 PROCEDURE fa_sorp_link_reval (
1022 -- p_nbv NUMBER,
1023 p_adj_amt NUMBER,
1024 p_mass_reval_id NUMBER,
1025 p_asset_id NUMBER,
1026 p_book_type_code VARCHAR2,
1027 p_run_mode VARCHAR2,
1028 p_request_id NUMBER,
1029 p_mrc_sob_type_code VARCHAR2,
1030 p_category_id NUMBER,
1031 p_reval_type VARCHAR2,
1032 p_set_of_books_id NUMBER,
1033 x_imp_loss_impact OUT NOCOPY NUMBER,
1034 x_reval_gain OUT NOCOPY NUMBER,
1035 x_impair_loss_acct OUT NOCOPY VARCHAR2,
1036 x_temp_imp_deprn_effect OUT NOCOPY NUMBER,
1037 x_reval_rsv_deprn_effect OUT NOCOPY NUMBER
1038 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1039 IS
1040 -- Local Varibales declaration
1041 v_sum NUMBER; -- sum of impairments
1042 v_impairment_id FA_SORP_ASSET_LINK_REVAL_V.impairment_id%TYPE;
1043 v_book_type_code FA_SORP_ASSET_LINK_REVAL_V.book_type_code%TYPE;
1044 v_asset_id FA_SORP_ASSET_LINK_REVAL_V.asset_id%TYPE;
1045 v_IMP_LOSS_AMOUNT FA_SORP_ASSET_LINK_REVAL_V.IMP_LOSS_AMOUNT%Type;
1046 V_SPLIT_IMPAIR_FLAG varchar2(3);
1047 v_mass_reval_id FA_SORP_ASSET_LINK_REVAL_V.mass_reval_id%TYPE;
1048 v_reverse_imp_amt FA_SORP_ASSET_LINK_REVAL_V.unused_imp_loss_amount%TYPE;
1049 -- Amount to be reversed
1050 l_adj_amt NUMBER;
1051 -- Delta value NBV(Reval) - NBV(Current)
1052 l_new_reverse_amt NUMBER;
1053 l_temp_reverse_amt NUMBER;
1054 l_exit BOOLEAN := TRUE;
1055 v_category_id NUMBER;
1056 v_imp_impact NUMBER;
1057 v_temp_imp_impact NUMBER;
1058 l_reval_gain NUMBER;
1059 v_impair_loss_acct NUMBER;
1060 v_imp_deprn_effect NUMBER;
1061 v_temp_imp_deprn_effect NUMBER;
1062 v_reval_reserve_impact_flag VARCHAR2 (1);
1063 v_impair_class VARCHAR2 (240);
1064 v_reason VARCHAR2 (240);
1065 v_calc_imp_deprn_effect NUMBER;
1066 v_calc_imp_impact NUMBER;
1067 l_temp_reval_rsv NUMBER;
1068 l_reval_rsv_deprn_effect NUMBER;
1069 l_temp_reval_rsv_deprn_effect NUMBER;
1070 v_reval_rsv_adj_amount NUMBER;
1071 v_calc_imp_loss_amount NUMBER;
1072 l_reverse_rsv_amount NUMBER;
1073 l_temp_reverse_rsv_amount NUMBER;
1074 v_split_number NUMBER;
1075 l_reval_gain_temp NUMBER;
1076 t_1 number;
1077 t_2 number;
1078 t_3 number;
1079 t_4 number;
1080
1081 CURSOR c_asset_cur
1082 IS
1083 SELECT impairment_id, split_number, book_type_code, asset_id,
1084 IMP_LOSS_AMOUNT,decode(SPLIT_IMPAIR_FLAG,'N','NO','Y','YES','NO')
1085 /*Bug# 7392015-Added to columns for prorate */
1086 ,unused_imp_loss_amount, mass_reval_id,
1087 ( unused_imp_loss_amount
1088 - fa_imp_deprn_eff_fn (impairment_id,
1089 book_type_code,
1090 asset_id,
1091 'C',
1092 NULL
1093 )
1094 ) imp_impact, -- Impairtment Amount -- Deprn Impact due to impairment amount
1095 fa_imp_deprn_eff_fn (impairment_id,
1096 book_type_code,
1097 asset_id,
1098 'C',
1099 NULL
1100 ) imp_deprn_effect, -- Deprn Impact due to impairment amount
1101 calc_imp_loss_amount,
1102 ( calc_imp_loss_amount
1103 - fa_imp_deprn_eff_fn (impairment_id,
1104 book_type_code,
1105 asset_id,
1106 'I',
1107 NULL
1108 )
1109 ) calc_imp_impact, -- (I/E Impairment amount - depreciation impact on the I/E)
1110 fa_imp_deprn_eff_fn (impairment_id,
1111 book_type_code,
1112 asset_id,
1113 'I',
1114 NULL
1115 ) calc_imp_deprn_effect, --(depreciation impact on the I/E)
1116 impair_loss_acct, NULL category_id,
1117 reval_reserve_impact_flag, NVL (reval_rsv_adj_amount, 0),
1118 impair_class, reason
1119 FROM fa_sorp_asset_link_reval_v
1120 WHERE asset_id = p_asset_id
1121 AND book_type_code = p_book_type_code
1122 AND mass_reval_id = p_mass_reval_id
1123 AND imp_include_flag = 'Y'
1124 ORDER BY impairment_date DESC,
1125 impair_class,
1126 unused_imp_loss_amount,
1127 imp_impact;
1128
1129 CURSOR c_cat_cur
1130 IS
1131 SELECT *
1132 FROM (SELECT impairment_id, split_number, book_type_code,
1133 asset_id,unused_imp_loss_amount, mass_reval_id,
1134 ( unused_imp_loss_amount
1135 - fa_imp_deprn_eff_fn (impairment_id,
1136 book_type_code,
1137 asset_id,
1138 'C',
1139 NULL
1140 )
1141 ) imp_impact, -- Impairtment Amount -- Deprn Impact due to impairment amount
1142 fa_imp_deprn_eff_fn
1143 (impairment_id,
1144 book_type_code,
1145 asset_id,
1146 'C',
1147 NULL
1148 ) imp_deprn_effect, -- Deprn Impact due to impairment amount
1149 calc_imp_loss_amount,
1150 ( calc_imp_loss_amount
1151 - fa_imp_deprn_eff_fn (impairment_id,
1152 book_type_code,
1153 asset_id,
1154 'I',
1155 NULL
1156 )
1157 ) calc_imp_impact, -- (I/E Impairment amount - depreciation impact on the I/E)
1158 fa_imp_deprn_eff_fn
1159 (impairment_id,
1160 book_type_code,
1161 asset_id,
1162 'I',
1163 NULL
1164 ) calc_imp_deprn_effect, --(depreciation impact on the I/E)
1165 NVL (reval_rsv_adj_amount, 0), impair_loss_acct,
1166 category_id, reval_reserve_impact_flag,
1167 impair_class, reason
1168 FROM fa_sorp_cat_link_reval_v
1169 WHERE asset_id = p_asset_id
1170 AND book_type_code = p_book_type_code
1171 AND mass_reval_id = p_mass_reval_id
1172 AND category_id = p_category_id
1173 AND unused_imp_loss_amount > 0
1174 ORDER BY impairment_date DESC,
1175 impair_class,
1176 unused_imp_loss_amount,
1177 imp_impact)
1178 WHERE ROWNUM = 1;
1179
1180 x_deprn_rsv number;
1181 x_impairment_amt NUMBER;
1182 x_override_flag varchar2(3);
1183 x_impair_split_flag varchar2(3);
1184 BEGIN
1185 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'Start', 'Start', p_log_level_rec => p_log_level_rec);
1186 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'p_adj_amt', p_adj_amt, p_log_level_rec => p_log_level_rec);
1187 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1188 'p_mass_reval_id',
1189 p_mass_reval_id
1190 , p_log_level_rec => p_log_level_rec);
1191 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'p_asset_id', p_asset_id, p_log_level_rec => p_log_level_rec);
1192 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1193 'p_book_type_code',
1194 p_book_type_code
1195 , p_log_level_rec => p_log_level_rec);
1196 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'p_run_mode', p_run_mode, p_log_level_rec => p_log_level_rec);
1197 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'p_request_id', p_request_id, p_log_level_rec => p_log_level_rec);
1198 -- Adjustment Amount is the amount that can be adjusted based on amount entered for revaluation by the user and on revaluation method
1199 -- Calculation for getting adj amount in available in Reval Private package
1200 l_adj_amt := p_adj_amt;
1201 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'p_reval_type', p_reval_type, p_log_level_rec => p_log_level_rec);
1202 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'l_adj_amt', l_adj_amt, p_log_level_rec => p_log_level_rec);
1203
1204 IF p_reval_type = 'A'
1205 THEN
1206 OPEN c_asset_cur; -- Start Asset cursor
1207
1208 FETCH c_asset_cur
1209 INTO v_impairment_id, v_split_number, v_book_type_code, v_asset_id,
1210 v_IMP_LOSS_AMOUNT,V_SPLIT_IMPAIR_FLAG,
1211 v_reverse_imp_amt, v_mass_reval_id, v_imp_impact,
1212 v_imp_deprn_effect, v_calc_imp_loss_amount, v_calc_imp_impact,
1213 v_calc_imp_deprn_effect, v_impair_loss_acct, v_category_id,
1214 v_reval_reserve_impact_flag, v_reval_rsv_adj_amount,
1215 v_impair_class, v_reason;
1216
1217 -- v_reverse_imp_amt is Unused Imairment amount i.e impairment amount - prior reval reversals
1218 -- v_imp_deprn_effect is deprn impact due to impairment amount
1219 -- v_imp_impact = (v_reverse_imp_amt - v_imp_deprn_effect)
1220
1221 -- v_calc_imp_loss_amount = I/E Impairment amount
1222 -- v_calc_imp_deprn_effect = depreciation impact on the I/E
1223 -- v_calc_imp_impact = (v_calc_imp_loss_amount - v_calc_imp_deprn_effect)
1224 /*Bug#7392015 - Call to calculate deprn effect for double dd depreciation nmehtod */
1225 if (p_log_level_rec.statement_level) then
1226 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'before calling ', 'fa_sorp_link_reval_dd', p_log_level_rec => p_log_level_rec);
1227 end if;
1228 if NOT fa_sorp_link_reval_dd(p_mass_reval_id,
1229 v_asset_id,
1230 v_book_type_code,
1231 v_impairment_id,
1232 v_reverse_imp_amt,
1233 p_mrc_sob_type_code,
1234 p_set_of_books_id,
1235 x_deprn_rsv,
1236 x_impairment_amt,
1237 x_impair_split_flag,
1238 x_override_flag) THEN
1239 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'failed calling ', 'fa_sorp_link_reval_dd', p_log_level_rec => p_log_level_rec);
1240 END IF;
1241 if (p_log_level_rec.statement_level) then
1242 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'deprn reserve from fa_sorp_link_reval_dd :',x_deprn_rsv, p_log_level_rec => p_log_level_rec);
1243 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'x_impairment_amt from fa_sorp_link_reval_dd :',x_impairment_amt, p_log_level_rec => p_log_level_rec);
1244 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'x_impair_split_flag from fa_sorp_link_reval_dd :',x_impair_split_flag, p_log_level_rec => p_log_level_rec);
1245 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'x_override_flag from fa_sorp_link_reval_dd :',x_override_flag, p_log_level_rec => p_log_level_rec);
1246 end if;
1247
1248 /* if x_override_flag is 'YES' reset values calculated by existing cursor*/
1249 if nvl(x_override_flag,'NO')='YES' then
1250 if(V_SPLIT_IMPAIR_FLAG = 'NO') then
1251 v_imp_deprn_effect := x_deprn_rsv;
1252 v_imp_impact := v_reverse_imp_amt - x_deprn_rsv;
1253 if v_calc_imp_loss_amount <> 0 then
1254 v_calc_imp_deprn_effect := x_deprn_rsv;
1255 if v_reverse_imp_amt <> v_IMP_LOSS_AMOUNT then
1256 v_calc_imp_deprn_effect := (x_deprn_rsv * v_reverse_imp_amt)/v_IMP_LOSS_AMOUNT;
1257 v_calc_imp_deprn_effect := round(v_calc_imp_deprn_effect,2);
1258 end if;
1259 end if;
1260 v_calc_imp_impact := v_calc_imp_loss_amount - v_calc_imp_deprn_effect;
1261 else
1262 if v_calc_imp_loss_amount <> 0 then
1263 v_calc_imp_deprn_effect := x_deprn_rsv;
1264 v_calc_imp_impact := v_calc_imp_loss_amount - v_calc_imp_deprn_effect;
1265 end if;
1266 if v_IMP_LOSS_AMOUNT <> v_reverse_imp_amt then
1267 x_deprn_rsv := (x_deprn_rsv * v_IMP_LOSS_AMOUNT)/v_reverse_imp_amt;
1268 x_deprn_rsv :=round(x_deprn_rsv,2);
1269 end if;
1270 v_imp_deprn_effect := x_deprn_rsv;
1271 v_imp_impact := v_reverse_imp_amt - x_deprn_rsv;
1272 end if;
1273 end if;
1274
1275 IF v_reverse_imp_amt < l_adj_amt -- Start v_reverse_imp_amt < l_adj_amt
1276 THEN
1277 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1278 'v_reverse_imp_amt < l_adj_amt',
1279 v_reverse_imp_amt < l_adj_amt
1280 , p_log_level_rec => p_log_level_rec);
1281 v_temp_imp_impact := NVL (v_temp_imp_impact, 0) + v_imp_impact;
1282 v_temp_imp_deprn_effect := v_imp_deprn_effect;
1283 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1284 'v_temp_imp_impact',
1285 v_temp_imp_impact
1286 , p_log_level_rec => p_log_level_rec);
1287 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1288 'v_temp_imp_deprn_effect',
1289 v_temp_imp_deprn_effect
1290 , p_log_level_rec => p_log_level_rec);
1291 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'l_adj_amt', l_adj_amt, p_log_level_rec => p_log_level_rec);
1292 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1293 'v_calc_imp_impact',
1294 v_calc_imp_impact
1295 , p_log_level_rec => p_log_level_rec);
1296 l_reval_gain := l_adj_amt - v_calc_imp_impact;
1297 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1298 'v_reverse_imp_amt < l_adj_amt',
1299 v_reverse_imp_amt < l_adj_amt
1300 , p_log_level_rec => p_log_level_rec);
1301 x_reval_rsv_deprn_effect := 0;
1302
1303 IF v_reval_reserve_impact_flag = 'Y'
1304 THEN
1305 IF l_reval_gain < v_reval_rsv_adj_amount
1306 THEN
1307 l_reval_rsv_deprn_effect :=
1308 ROUND ((l_reval_gain / v_imp_impact) * v_imp_deprn_effect,
1309 2
1310 );
1311 l_reverse_rsv_amount := ROUND (l_reval_gain, 2);
1312 ELSIF l_reval_gain >= v_reval_rsv_adj_amount
1313 THEN
1314 l_reverse_rsv_amount := v_reval_rsv_adj_amount;
1315 l_reval_rsv_deprn_effect :=
1316 fa_imp_deprn_eff_fn (v_impairment_id,
1317 v_book_type_code,
1318 v_asset_id,
1319 'R',
1320 l_reverse_rsv_amount
1321 );
1322 l_reverse_rsv_amount :=
1323 v_reval_rsv_adj_amount - l_reval_rsv_deprn_effect;
1324 END IF;
1325
1326 x_reval_rsv_deprn_effect := l_reval_rsv_deprn_effect;
1327 ELSE
1328 l_reverse_rsv_amount := 0;
1329 l_reval_rsv_deprn_effect := 0;
1330 END IF;
1331
1332 -- Assigning values from local varibales to out parameters
1333 x_imp_loss_impact := v_temp_imp_impact;
1334 x_reval_gain := l_reval_gain;
1335 x_temp_imp_deprn_effect := v_temp_imp_deprn_effect;
1336 x_impair_loss_acct := v_impair_loss_acct;
1337
1338 INSERT INTO fa_sorp_link_reval_itf
1339 (request_id, mass_reval_id, asset_id,
1340 category_id, book_type_code,
1341 impairment_id,
1342 split_number, impairment_loss_amount,
1343 impair_loss_impact, impair_loss_acct,
1344 imp_deprn_effect, run_mode,
1345 reval_reserve_impact_flag, impair_class_type,
1346 reason,
1347 calc_imp_amount,
1348 calc_imp_deprn_effect,
1349 reval_rsv_adj_amount,
1350 reval_rsv_adj_deprn_effect,
1351 calc_imp_reverse_amt,
1352 calc_imp_reverse_deprn_effect,
1353 rsv_reverse_amt,
1354 rsv_reverse_deprn_effect,
1355 reval_gain,
1356 created_by,
1357 creation_date
1358 )
1359 VALUES (p_request_id, v_mass_reval_id, v_asset_id,
1360 v_category_id, v_book_type_code,
1361 fa_sorp_process_imp_id_fn (v_impairment_id),
1362 v_split_number, v_reverse_imp_amt,
1363 v_imp_impact, v_impair_loss_acct,
1364 v_imp_deprn_effect, p_run_mode,
1365 v_reval_reserve_impact_flag, v_impair_class,
1366 v_reason,
1367 v_calc_imp_impact,
1368 v_calc_imp_deprn_effect,
1369 v_reval_rsv_adj_amount,
1370 l_reval_rsv_deprn_effect,
1371 v_calc_imp_impact, -- calc_imp_reverse_amt - calculated impairment amount to be reversed
1372 v_calc_imp_deprn_effect, -- calc_imp_reverse_deprn_effect - calulated deprn impact due to calculated impairment amount
1373 l_reverse_rsv_amount, -- rsv_reverse_amt - Reval Reserve Adjustment amount to be reversed
1374 l_reval_rsv_deprn_effect, -- rsv_reverse_deprn_effect - deprn impact due to Reval Reserve Adjustment amount to be reversed
1375 l_reval_gain, -- reval_gain - Revaluation Gain
1376 '-1',
1377 SYSDATE
1378 );
1379
1380 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1381 'Adjustment(Delta) Amount(A)',
1382 p_adj_amt
1383 );
1384 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1385 'Impairment Loss Impact(B)',
1386 v_temp_imp_impact
1387 );
1388 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1389 'Reval Gain(C)',
1390 l_reval_gain
1391 );
1392 -- If v_sum is greater than or equal to adjustment amount
1393 ELSIF v_reverse_imp_amt >= l_adj_amt
1394 THEN
1395 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1396 'v_reverse_imp_amt >= l_adj_amt',
1397 'v_reverse_imp_amt >= l_adj_amt'
1398 , p_log_level_rec => p_log_level_rec);
1399 -- Bug#7524125
1400 IF l_adj_amt < v_calc_imp_impact
1401 THEN
1402 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1403 'l_adj_amt < v_calc_imp_impact',
1404 'l_adj_amt < v_calc_imp_impact'
1405 , p_log_level_rec => p_log_level_rec);
1406
1407 l_new_reverse_amt := l_adj_amt;
1408 v_temp_imp_deprn_effect := round(((l_adj_amt*v_imp_deprn_effect)/v_imp_impact),2);
1409 /* l_new_reverse_amt :=
1410 NVL (l_new_reverse_amt, 0)
1411 - v_temp_imp_deprn_effect; */
1412 l_reval_gain :=0;
1413 l_reval_gain_temp := 0;
1414 ELSIF l_adj_amt >= v_calc_imp_impact
1415 THEN
1416 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1417 'l_adj_amt >= v_calc_imp_impact',
1418 'l_adj_amt >= v_calc_imp_impact'
1419 , p_log_level_rec => p_log_level_rec);
1420 l_new_reverse_amt :=
1421 NVL (l_new_reverse_amt, 0)
1422 + v_calc_imp_impact;
1423 v_temp_imp_deprn_effect := v_calc_imp_deprn_effect;
1424 l_reval_gain := l_adj_amt - v_calc_imp_impact; /*11724560 */
1425 END IF;
1426
1427 x_reval_rsv_deprn_effect := 0;
1428
1429 --Commented for Bug#7524125
1430
1431 IF v_reval_reserve_impact_flag = 'Y' and l_adj_amt > v_calc_imp_impact
1432 THEN
1433 /* IF l_reval_gain < v_reval_rsv_adj_amount
1434 THEN
1435 l_reval_rsv_deprn_effect :=
1436 ROUND ((l_reval_gain / v_imp_impact) * v_imp_deprn_effect,
1437 2
1438 );
1439 l_reverse_rsv_amount := ROUND (l_reval_gain, 2);
1440 ELSIF l_reval_gain >= v_reval_rsv_adj_amount
1441 THEN */
1442 l_temp_reverse_rsv_amount :=v_reval_rsv_adj_amount;
1443
1444 fa_debug_pkg.ADD ('fa_sorp_link_reval','first time l_temp_reverse_rsv_amount',l_temp_reverse_rsv_amount, p_log_level_rec => p_log_level_rec);
1445
1446 l_temp_reval_rsv_deprn_effect :=
1447 fa_imp_deprn_eff_fn (v_impairment_id,
1448 v_book_type_code,
1449 v_asset_id,
1450 'R',
1451 l_temp_reverse_rsv_amount
1452 );
1453
1454 fa_debug_pkg.ADD ('fa_sorp_link_reval','l_temp_reval_rsv_deprn_effect',l_temp_reval_rsv_deprn_effect, p_log_level_rec => p_log_level_rec);
1455
1456 l_temp_reverse_rsv_amount := nvl(l_temp_reverse_rsv_amount,0) - nvl(l_temp_reval_rsv_deprn_effect,0);
1457
1458 fa_debug_pkg.ADD ('fa_sorp_link_reval','second time l_temp_reverse_rsv_amount',l_temp_reverse_rsv_amount, p_log_level_rec => p_log_level_rec);
1459
1460 if l_temp_reverse_rsv_amount > l_adj_amt - v_calc_imp_impact then
1461
1462 l_reverse_rsv_amount := l_adj_amt-v_calc_imp_impact;
1463
1464 fa_debug_pkg.ADD ('fa_sorp_link_reval','l_reverse_rsv_amount',l_reverse_rsv_amount, p_log_level_rec => p_log_level_rec);
1465
1466 l_reval_rsv_deprn_effect := round((l_reverse_rsv_amount*nvl(l_temp_reval_rsv_deprn_effect,0))/l_temp_reverse_rsv_amount,2);
1467
1468 fa_debug_pkg.ADD ('fa_sorp_link_reval','l_reval_rsv_deprn_effect',l_reval_rsv_deprn_effect, p_log_level_rec => p_log_level_rec);
1469 else
1470 l_reverse_rsv_amount := l_temp_reverse_rsv_amount;
1471 l_reval_rsv_deprn_effect := l_temp_reval_rsv_deprn_effect;
1472
1473 end if;
1474 --END IF;
1475
1476
1477 x_reval_rsv_deprn_effect := l_reval_rsv_deprn_effect;
1478 ELSE
1479 l_reverse_rsv_amount := 0;
1480 l_reval_rsv_deprn_effect := 0;
1481 x_reval_rsv_deprn_effect := 0;
1482 END IF;
1483
1484 /* IF NVL (l_reval_gain_temp, 0) <> 0
1485 THEN
1486 l_reval_gain := l_reval_gain_temp;
1487 END IF;*/
1488
1489 -- Assigning values from local varibales to out parameters
1490 x_imp_loss_impact := l_new_reverse_amt;
1491 x_reval_gain := l_reval_gain;
1492 x_temp_imp_deprn_effect := v_temp_imp_deprn_effect;
1493 x_impair_loss_acct := v_impair_loss_acct;
1494
1495 INSERT INTO fa_sorp_link_reval_itf
1496 (request_id, mass_reval_id, asset_id,
1497 category_id, book_type_code,
1498 impairment_id,
1499 split_number, impairment_loss_amount,
1500 impair_loss_impact, impair_loss_acct,
1501 imp_deprn_effect, run_mode,
1502 reval_reserve_impact_flag, impair_class_type,
1503 reason,
1504 calc_imp_amount,
1505 calc_imp_deprn_effect,
1506 reval_rsv_adj_amount,
1507 reval_rsv_adj_deprn_effect,
1508 calc_imp_reverse_amt,
1509 calc_imp_reverse_deprn_effect,
1510 rsv_reverse_amt,
1511 rsv_reverse_deprn_effect,
1512 reval_gain,
1513 created_by,
1514 creation_date
1515 )
1516 VALUES (p_request_id, v_mass_reval_id, v_asset_id,
1517 v_category_id, v_book_type_code,
1518 fa_sorp_process_imp_id_fn (v_impairment_id),
1519 v_split_number, v_reverse_imp_amt,
1520 v_imp_impact, v_impair_loss_acct,
1521 v_imp_deprn_effect, p_run_mode,
1522 v_reval_reserve_impact_flag, v_impair_class,
1523 v_reason,
1524 v_calc_imp_impact,
1525 v_calc_imp_deprn_effect,
1526 v_reval_rsv_adj_amount,
1527 l_reval_rsv_deprn_effect,
1528 l_new_reverse_amt, -- calc_imp_reverse_amt - calculated impairment amount to be reversed
1529 v_temp_imp_deprn_effect, -- calc_imp_reverse_deprn_effect - calulated deprn impact due to calculated impairment amount
1530 l_reverse_rsv_amount, -- rsv_reverse_amt - Reval Reserve Adjustment amount to be reversed
1531 l_reval_rsv_deprn_effect, -- rsv_reverse_deprn_effect - deprn impact due to Reval Reserve Adjustment amount to be reversed
1532 l_reval_gain, -- reval_gain - Revaluation Gain
1533 '-1',
1534 SYSDATE
1535 );
1536 END IF; -- End v_reverse_imp_amt < l_adj_amt
1537
1538 CLOSE c_asset_cur; -- End Asset cur
1539
1540 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1541 'Adjustment(Delta) Amount(A)',
1542 p_adj_amt
1543 );
1544 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1545 'Impairment Loss Impact(B)',
1546 l_new_reverse_amt
1547 );
1548 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'Reval Gain(C)',
1549 l_reval_gain);
1550 END IF;
1551
1552 IF p_reval_type = 'C'
1553 THEN
1554 OPEN c_cat_cur;
1555
1556 FETCH c_cat_cur
1557 INTO v_impairment_id, v_split_number, v_book_type_code, v_asset_id,
1558 v_reverse_imp_amt, v_mass_reval_id, v_imp_impact,
1559 v_imp_deprn_effect, v_calc_imp_loss_amount, v_calc_imp_impact,
1560 v_calc_imp_deprn_effect, v_reval_rsv_adj_amount,
1561 v_impair_loss_acct, v_category_id,
1562 v_reval_reserve_impact_flag, v_impair_class, v_reason;
1563
1564 /*Bug#7392015 - Call to calculate deprn effect for double dd depreciation nmehtod */
1565 if v_impairment_id is not null then
1566 if (p_log_level_rec.statement_level) then
1567 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'before calling ', 'fa_sorp_link_reval_dd', p_log_level_rec => p_log_level_rec);
1568 end if;
1569 if NOT fa_sorp_link_reval_dd(p_mass_reval_id,
1570 v_asset_id,
1571 v_book_type_code,
1572 v_impairment_id,
1573 v_reverse_imp_amt,
1574 p_mrc_sob_type_code,
1575 p_set_of_books_id,
1576 x_deprn_rsv,
1577 x_impairment_amt,
1578 x_impair_split_flag,
1579 x_override_flag) THEN
1580 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'failed calling ', 'fa_sorp_link_reval_dd', p_log_level_rec => p_log_level_rec);
1581 END IF;
1582 if (p_log_level_rec.statement_level) then
1583 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'deprn reserve from fa_sorp_link_reval_dd :',x_deprn_rsv, p_log_level_rec => p_log_level_rec);
1584 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'x_impairment_amt from fa_sorp_link_reval_dd :',x_impairment_amt, p_log_level_rec => p_log_level_rec);
1585 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'x_impair_split_flag from fa_sorp_link_reval_dd :',x_impair_split_flag, p_log_level_rec => p_log_level_rec);
1586 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'x_override_flag from fa_sorp_link_reval_dd :',x_override_flag, p_log_level_rec => p_log_level_rec);
1587 end if;
1588 if nvl(x_override_flag,'NO')='YES' then
1589 if nvl(x_impair_split_flag,'NO') = 'NO' then
1590 v_imp_deprn_effect := x_deprn_rsv;
1591 v_imp_impact := v_reverse_imp_amt - v_imp_deprn_effect;
1592 v_calc_imp_deprn_effect := x_deprn_rsv;
1593 if (x_impairment_amt <> 0) then
1594 v_calc_imp_deprn_effect := round((x_deprn_rsv * v_reverse_imp_amt)/x_impairment_amt,2);
1595 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'v_calc_imp_deprn_effect from fa_sorp_link_reval_dd :',v_calc_imp_deprn_effect, p_log_level_rec => p_log_level_rec);
1596 end if;
1597 v_calc_imp_impact := v_calc_imp_loss_amount - v_calc_imp_deprn_effect;
1598 elsif x_impair_split_flag = 'YES' then
1599 v_calc_imp_deprn_effect := x_deprn_rsv;
1600 v_calc_imp_impact := v_calc_imp_loss_amount - v_calc_imp_deprn_effect;
1601 v_imp_deprn_effect := x_deprn_rsv;
1602 if (x_impairment_amt <> 0) then
1603 v_imp_deprn_effect := round((x_deprn_rsv * x_impairment_amt)/v_reverse_imp_amt,2);
1604 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'v_imp_deprn_effect from fa_sorp_link_reval_dd :',v_imp_deprn_effect, p_log_level_rec => p_log_level_rec);
1605 end if;
1606 v_imp_impact := v_reverse_imp_amt - v_imp_deprn_effect;
1607 end if;
1608 end if; --nvl(x_override_flag,'NO')='YES'
1609 end if; --v_impairment_id is not null
1610 /*Bug#7392015 changes ends*/
1611
1612 IF v_reverse_imp_amt < l_adj_amt
1613 THEN
1614 v_temp_imp_impact := NVL (v_temp_imp_impact, 0) + v_imp_impact;
1615 v_temp_imp_deprn_effect := v_imp_deprn_effect;
1616 l_reval_gain := l_adj_amt - v_calc_imp_impact;
1617 x_reval_rsv_deprn_effect := 0;
1618
1619 IF v_reval_reserve_impact_flag = 'Y'
1620 THEN
1621 IF l_reval_gain < v_reval_rsv_adj_amount
1622 THEN
1623 l_reval_rsv_deprn_effect :=
1624 ROUND ((l_reval_gain / v_imp_impact) * v_imp_deprn_effect,
1625 2
1626 );
1627 l_reverse_rsv_amount := ROUND (l_reval_gain, 2);
1628 ELSIF l_reval_gain >= v_reval_rsv_adj_amount
1629 THEN
1630 l_reverse_rsv_amount := v_reval_rsv_adj_amount;
1631 l_reval_rsv_deprn_effect :=
1632 fa_imp_deprn_eff_fn (v_impairment_id,
1633 v_book_type_code,
1634 v_asset_id,
1635 'R',
1636 l_reverse_rsv_amount
1637 );
1638 l_reverse_rsv_amount :=
1639 v_reval_rsv_adj_amount - l_reval_rsv_deprn_effect;
1640 END IF;
1641
1642 x_reval_rsv_deprn_effect := l_reval_rsv_deprn_effect;
1643 ELSE
1644 l_reverse_rsv_amount := 0;
1645 l_reval_rsv_deprn_effect := 0;
1646 END IF;
1647
1648 -- Assigning values from local varibales to out parameters
1649 x_imp_loss_impact := v_temp_imp_impact;
1650 x_reval_gain := l_reval_gain;
1651 x_temp_imp_deprn_effect := v_temp_imp_deprn_effect;
1652 x_impair_loss_acct := v_impair_loss_acct;
1653
1654 INSERT INTO fa_sorp_link_reval_itf
1655 (request_id, mass_reval_id, asset_id,
1656 category_id, book_type_code,
1657 impairment_id,
1658 split_number, impairment_loss_amount,
1659 impair_loss_impact, impair_loss_acct,
1660 imp_deprn_effect, run_mode,
1661 reval_reserve_impact_flag, impair_class_type,
1662 reason,
1663 calc_imp_amount,
1664 calc_imp_deprn_effect,
1665 reval_rsv_adj_amount,
1666 reval_rsv_adj_deprn_effect,
1667 calc_imp_reverse_amt,
1668 calc_imp_reverse_deprn_effect,
1669 rsv_reverse_amt,
1670 rsv_reverse_deprn_effect,
1671 reval_gain, created_by,
1672 creation_date
1673 )
1674 VALUES (p_request_id, v_mass_reval_id, v_asset_id,
1675 v_category_id, v_book_type_code,
1676 fa_sorp_process_imp_id_fn (v_impairment_id),
1677 v_split_number, v_reverse_imp_amt,
1678 v_imp_impact, v_impair_loss_acct,
1679 v_imp_deprn_effect, p_run_mode,
1680 v_reval_reserve_impact_flag, v_impair_class,
1681 v_reason,
1682 v_calc_imp_impact,
1683 v_calc_imp_deprn_effect,
1684 v_reval_rsv_adj_amount,
1685 l_reval_rsv_deprn_effect,
1686 v_calc_imp_impact, -- calc_imp_reverse_amt - calculated impairment amount to be reversed
1687 v_temp_imp_deprn_effect, -- calc_imp_reverse_deprn_effect - calulated deprn impact due to calculated impairment amount
1688 l_reverse_rsv_amount,-- rsv_reverse_amt - Reval Reserve Adjustment amount to be reversed
1689 l_reval_rsv_deprn_effect, -- rsv_reverse_deprn_effect - deprn impact due to Reval Reserve Adjustment amount to be reversed
1690 l_reval_gain, -- reval_gain - Revaluation Gain
1691 '-1',
1692 SYSDATE
1693 );
1694
1695 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1696 'Adjustment(Delta) Amount(A)',
1697 p_adj_amt
1698 );
1699 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1700 'Impairment Loss Impact(B)',
1701 v_calc_imp_impact
1702 );
1703 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1704 'Reval Gain(C)',
1705 l_reval_gain
1706 );
1707 ELSIF v_reverse_imp_amt >= l_adj_amt
1708 THEN
1709 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1710 'v_reverse_imp_amt >= l_adj_amt',
1711 'v_reverse_imp_amt >= l_adj_amt'
1712 , p_log_level_rec => p_log_level_rec);
1713 -- Bug#7524125
1714 IF l_adj_amt < v_calc_imp_impact
1715 THEN
1716 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1717 'l_adj_amt < v_calc_imp_impact',
1718 'l_adj_amt < v_calc_imp_impact'
1719 , p_log_level_rec => p_log_level_rec);
1720
1721 l_new_reverse_amt := l_adj_amt;
1722 v_temp_imp_deprn_effect := round(((l_adj_amt*v_imp_deprn_effect)/v_imp_impact),2);
1723 /* l_new_reverse_amt :=
1724 NVL (l_new_reverse_amt, 0)
1725 - v_temp_imp_deprn_effect; */
1726 l_reval_gain :=0;
1727 l_reval_gain_temp := 0;
1728 ELSIF l_adj_amt >= v_calc_imp_impact
1729 THEN
1730 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1731 'l_adj_amt >= v_calc_imp_impact',
1732 'l_adj_amt >= v_calc_imp_impact'
1733 , p_log_level_rec => p_log_level_rec);
1734 l_new_reverse_amt :=
1735 NVL (l_new_reverse_amt, 0)
1736 + v_calc_imp_impact;
1737 v_temp_imp_deprn_effect := v_calc_imp_deprn_effect;
1738
1739 l_reval_gain := 0;
1740 END IF;
1741
1742 x_reval_rsv_deprn_effect := 0;
1743
1744 --Commented for Bug#7524125
1745
1746 IF v_reval_reserve_impact_flag = 'Y' and l_adj_amt > v_calc_imp_impact
1747 THEN
1748 /* IF l_reval_gain < v_reval_rsv_adj_amount
1749 THEN
1750 l_reval_rsv_deprn_effect :=
1751 ROUND ((l_reval_gain / v_imp_impact) * v_imp_deprn_effect,
1752 2
1753 );
1754 l_reverse_rsv_amount := ROUND (l_reval_gain, 2);
1755 ELSIF l_reval_gain >= v_reval_rsv_adj_amount
1756 THEN */
1757 l_temp_reverse_rsv_amount :=v_reval_rsv_adj_amount;
1758
1759 fa_debug_pkg.ADD ('fa_sorp_link_reval','first time l_temp_reverse_rsv_amount',l_temp_reverse_rsv_amount, p_log_level_rec => p_log_level_rec);
1760
1761 l_temp_reval_rsv_deprn_effect :=
1762 fa_imp_deprn_eff_fn (v_impairment_id,
1763 v_book_type_code,
1764 v_asset_id,
1765 'R',
1766 l_temp_reverse_rsv_amount
1767 );
1768
1769 fa_debug_pkg.ADD ('fa_sorp_link_reval','l_temp_reval_rsv_deprn_effect',l_temp_reval_rsv_deprn_effect, p_log_level_rec => p_log_level_rec);
1770
1771 l_temp_reverse_rsv_amount := nvl(l_temp_reverse_rsv_amount,0) - nvl(l_temp_reval_rsv_deprn_effect,0);
1772
1773 fa_debug_pkg.ADD ('fa_sorp_link_reval','second time l_temp_reverse_rsv_amount',l_temp_reverse_rsv_amount, p_log_level_rec => p_log_level_rec);
1774
1775 if l_temp_reverse_rsv_amount > l_adj_amt - v_calc_imp_impact then
1776
1777 l_reverse_rsv_amount := l_adj_amt-v_calc_imp_impact;
1778
1779 fa_debug_pkg.ADD ('fa_sorp_link_reval','l_reverse_rsv_amount',l_reverse_rsv_amount, p_log_level_rec => p_log_level_rec);
1780
1781 l_reval_rsv_deprn_effect := round((l_reverse_rsv_amount*nvl(l_temp_reval_rsv_deprn_effect,0))/l_temp_reverse_rsv_amount,2);
1782
1783 fa_debug_pkg.ADD ('fa_sorp_link_reval','l_reval_rsv_deprn_effect',l_reval_rsv_deprn_effect, p_log_level_rec => p_log_level_rec);
1784 else
1785 l_reverse_rsv_amount := l_temp_reverse_rsv_amount;
1786 l_reval_rsv_deprn_effect := l_temp_reval_rsv_deprn_effect;
1787
1788 end if;
1789 --END IF;
1790
1791
1792 x_reval_rsv_deprn_effect := l_reval_rsv_deprn_effect;
1793 ELSE
1794 l_reverse_rsv_amount := 0;
1795 l_reval_rsv_deprn_effect := 0;
1796 x_reval_rsv_deprn_effect := 0;
1797 END IF;
1798
1799 /* IF NVL (l_reval_gain_temp, 0) <> 0
1800 THEN
1801 l_reval_gain := l_reval_gain_temp;
1802 END IF;*/
1803
1804 -- Assigning values from local varibales to out parameters
1805 x_imp_loss_impact := l_new_reverse_amt;
1806 x_reval_gain := l_reval_gain;
1807 x_temp_imp_deprn_effect := v_temp_imp_deprn_effect;
1808 x_impair_loss_acct := v_impair_loss_acct;
1809
1810 INSERT INTO fa_sorp_link_reval_itf
1811 (request_id, mass_reval_id, asset_id,
1812 category_id, book_type_code,
1813 impairment_id,
1814 split_number, impairment_loss_amount,
1815 impair_loss_impact, impair_loss_acct,
1816 imp_deprn_effect, run_mode,
1817 reval_reserve_impact_flag, impair_class_type,
1818 reason,
1819 calc_imp_amount,
1820 calc_imp_deprn_effect,
1821 reval_rsv_adj_amount,
1822 reval_rsv_adj_deprn_effect,
1823 calc_imp_reverse_amt,
1824 calc_imp_reverse_deprn_effect,
1825 rsv_reverse_amt,
1826 rsv_reverse_deprn_effect,
1827 reval_gain,
1828 created_by,
1829 creation_date
1830 )
1831 VALUES (p_request_id, v_mass_reval_id, v_asset_id,
1832 v_category_id, v_book_type_code,
1833 fa_sorp_process_imp_id_fn (v_impairment_id),
1834 v_split_number, v_reverse_imp_amt,
1835 v_imp_impact, v_impair_loss_acct,
1836 v_imp_deprn_effect, p_run_mode,
1837 v_reval_reserve_impact_flag, v_impair_class,
1838 v_reason,
1839 v_calc_imp_impact,
1840 v_calc_imp_deprn_effect,
1841 v_reval_rsv_adj_amount,
1842 l_reval_rsv_deprn_effect,
1843 l_new_reverse_amt, -- calc_imp_reverse_amt - calculated impairment amount to be reversed
1844 v_temp_imp_deprn_effect, -- calc_imp_reverse_deprn_effect - calulated deprn impact due to calculated impairment amount
1845 l_reverse_rsv_amount,-- rsv_reverse_amt - Reval Reserve Adjustment amount to be reversed
1846 l_reval_rsv_deprn_effect, -- rsv_reverse_deprn_effect - deprn impact due to Reval Reserve Adjustment amount to be reversed
1847 l_reval_gain, -- reval_gain - Revaluation Gain
1848 '-1',
1849 SYSDATE
1850 );
1851 END IF;
1852
1853 CLOSE c_cat_cur;
1854
1855 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1856 'Adjustment(Delta) Amount(A)',
1857 p_adj_amt
1858 );
1859 fa_debug_pkg.ADD ('fa_sorp_link_reval',
1860 'Impairment Loss Impact(B)',
1861 l_new_reverse_amt
1862 );
1863 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'Reval Gain(C)',
1864 l_reval_gain);
1865 END IF;
1866
1867 fa_debug_pkg.ADD ('fa_sorp_link_reval', 'End', 'End', p_log_level_rec => p_log_level_rec);
1868 END fa_sorp_link_reval;
1869
1870 /* Procedure updates FA_ITF_IMPAIRMENTS with reversed amounts
1871
1872 */
1873 PROCEDURE fa_imp_itf_upd (
1874 p_request_id NUMBER,
1875 p_book_type_code VARCHAR2,
1876 p_asset_id NUMBER,
1877 p_last_updated_by NUMBER,
1878 p_last_update_date DATE
1879 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1880 IS
1881 CURSOR c_reval_link_itf_cur
1882 IS
1883 SELECT impairment_id, split_number, calc_imp_reverse_amt,
1884 calc_imp_reverse_deprn_effect, rsv_reverse_amt,
1885 rsv_reverse_deprn_effect
1886 FROM fa_sorp_link_reval_itf
1887 WHERE request_id = p_request_id
1888 AND book_type_code = p_book_type_code
1889 AND asset_id = p_asset_id
1890 AND run_mode = 'RUN';
1891
1892 v_impairment_id NUMBER;
1893 v_split_number NUMBER;
1894 v_calc_imp_reverse_amt NUMBER;
1895 v_calc_imp_rev_deprn_effect NUMBER;
1896 v_rsv_reverse_amt NUMBER;
1897 v_rsv_reverse_deprn_effect NUMBER;
1898 BEGIN
1899 OPEN c_reval_link_itf_cur;
1900
1901 LOOP
1902 FETCH c_reval_link_itf_cur
1903 INTO v_impairment_id, v_split_number, v_calc_imp_reverse_amt,
1904 v_calc_imp_rev_deprn_effect, v_rsv_reverse_amt,
1905 v_rsv_reverse_deprn_effect;
1906
1907 EXIT WHEN c_reval_link_itf_cur%NOTFOUND;
1908
1909 IF NVL (v_split_number, 0) = 0
1910 THEN
1911 IF NVL (v_calc_imp_reverse_amt, 0) <> 0
1912 THEN
1913 UPDATE fa_itf_impairments
1914 SET reversed_imp_amt = nvl(reversed_imp_amt,0)+v_calc_imp_reverse_amt,
1915 reversed_deprn_impact = nvl(reversed_deprn_impact,0)+v_calc_imp_rev_deprn_effect,
1916 last_updated_by = p_last_updated_by,
1917 last_update_date = p_last_update_date
1918 WHERE impairment_id = v_impairment_id;
1919 END IF;
1920
1921 IF NVL (v_rsv_reverse_amt, 0) <> 0
1922 THEN
1923 UPDATE fa_itf_impairments
1924 SET reversed_reval_amt = nvl(reversed_reval_amt,0)+v_rsv_reverse_amt,
1925 reversed_reval_impact = nvl(reversed_reval_impact,0)+v_rsv_reverse_deprn_effect,
1926 last_updated_by = p_last_updated_by,
1927 last_update_date = p_last_update_date
1928 WHERE impairment_id = v_impairment_id;
1929 END IF;
1930 ELSIF NVL (v_split_number, 0) = 1
1931 THEN
1932 IF NVL (v_calc_imp_reverse_amt, 0) <> 0
1933 THEN
1934 UPDATE fa_itf_impairments
1935 SET reversed_imp_amt_s1 = nvl(reversed_imp_amt_s1,0)+v_calc_imp_reverse_amt,
1936 reversed_deprn_impact_s1 = nvl(reversed_deprn_impact_s1,0)+v_calc_imp_rev_deprn_effect,
1937 last_updated_by = p_last_updated_by,
1938 last_update_date = p_last_update_date
1939 WHERE impairment_id = v_impairment_id;
1940 END IF;
1941
1942 IF NVL (v_rsv_reverse_amt, 0) <> 0
1943 THEN
1944 UPDATE fa_itf_impairments
1945 SET reversed_reval_amt_s1 = nvl(reversed_reval_amt_s1,0)+v_rsv_reverse_amt,
1946 reversed_reval_impact_s1 = nvl(reversed_reval_impact_s1,0)+v_rsv_reverse_deprn_effect,
1947 last_updated_by = p_last_updated_by,
1948 last_update_date = p_last_update_date
1949 WHERE impairment_id = v_impairment_id;
1950 END IF;
1951 ELSIF NVL (v_split_number, 0) = 2
1952 THEN
1953 IF NVL (v_calc_imp_reverse_amt, 0) <> 0
1954 THEN
1955 UPDATE fa_itf_impairments
1956 SET reversed_imp_amt_s2 = nvl(reversed_imp_amt_s2,0)+v_calc_imp_reverse_amt,
1957 reversed_deprn_impact_s2 = nvl(reversed_deprn_impact_s2,0)+v_calc_imp_rev_deprn_effect,
1958 last_updated_by = p_last_updated_by,
1959 last_update_date = p_last_update_date
1960 WHERE impairment_id = v_impairment_id;
1961 END IF;
1962
1963 IF NVL (v_rsv_reverse_amt, 0) <> 0
1964 THEN
1965 UPDATE fa_itf_impairments
1966 SET reversed_reval_amt_s2 = nvl(reversed_reval_amt_s2,0)+v_rsv_reverse_amt,
1967 reversed_reval_impact_s2 = nvl(reversed_reval_impact_s2,0)+v_rsv_reverse_deprn_effect,
1968 last_updated_by = p_last_updated_by,
1969 last_update_date = p_last_update_date
1970 WHERE impairment_id = v_impairment_id;
1971 END IF;
1972 ELSIF NVL (v_split_number, 0) = 3
1973 THEN
1974 IF NVL (v_calc_imp_reverse_amt, 0) <> 0
1975 THEN
1976 UPDATE fa_itf_impairments
1977 SET reversed_imp_amt_s3 = nvl(reversed_imp_amt_s3,0)+v_calc_imp_reverse_amt,
1978 reversed_deprn_impact_s3 = nvl(reversed_deprn_impact_s3,0)+v_calc_imp_rev_deprn_effect,
1979 last_updated_by = p_last_updated_by,
1980 last_update_date = p_last_update_date
1981 WHERE impairment_id = v_impairment_id;
1982 END IF;
1983
1984 IF NVL (v_rsv_reverse_amt, 0) <> 0
1985 THEN
1986 UPDATE fa_itf_impairments
1987 SET reversed_reval_amt_s3 = nvl(reversed_reval_amt_s3,0)+v_rsv_reverse_amt,
1988 reversed_reval_impact_s3 = nvl(reversed_reval_impact_s3,0)+v_rsv_reverse_deprn_effect,
1989 last_updated_by = p_last_updated_by,
1990 last_update_date = p_last_update_date
1991 WHERE impairment_id = v_impairment_id;
1992 END IF;
1993 END IF;
1994 END LOOP;
1995
1996 CLOSE c_reval_link_itf_cur;
1997 END fa_imp_itf_upd;
1998
1999 FUNCTION process_loss_reversal(
2000 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
2001 p_period_rec IN FA_API_TYPES.period_rec_type,
2002 p_mrc_sob_type_code IN VARCHAR2,
2003 p_set_of_books_id IN NUMBER,
2004 p_reval_options_rec IN FA_API_TYPES.reval_options_rec_type,
2005 p_fully_rsvd_flag IN BOOLEAN,
2006 p_flag IN VARCHAR2,
2007 px_gain_amt IN OUT NOCOPY NUMBER,
2008 px_reval_loss_exp IN OUT NOCOPY NUMBER,
2009 px_deprn_exp IN OUT NOCOPY NUMBER,
2010 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN is
2011
2012 l_calling_fn varchar2(60) := 'fa_sorp_revaluation_pkg.process_loss_reversal';
2013 c_imp_pc number;
2014
2015 CURSOR c_get_reval_losses is
2016 SELECT adj.transaction_header_id,
2017 sum(adj.adjustment_amount),
2018 adj.period_counter_created,
2019 fbs.reval_loss_balance
2020 FROM
2021 FA_ADJUSTMENTS adj,
2022 fa_books_summary fbs,
2023 fa_transaction_headers fth
2024 WHERE adj.asset_id = p_asset_hdr_rec.asset_id
2025 AND adj.book_type_code = p_asset_hdr_rec.book_type_code
2026 AND adj.adjustment_type in ('REVAL LOSS','REVAL RESERVE')
2027 AND adj.debit_credit_flag = 'DR'
2028 AND adj.asset_id = fth.asset_id
2029 AND adj.book_type_code = fth.book_type_code
2030 AND adj.transaction_header_id = fth.transaction_header_id
2031 AND fth.transaction_key = 'RL'
2032 AND adj.period_counter_created = fbs.period_counter
2033 AND fbs.asset_id = adj.asset_id
2034 AND fbs.book_type_code = adj.book_type_code
2035 AND fbs.reval_loss_balance <> 0
2036 AND (( fbs.period_counter <= c_imp_pc and p_flag = 'P') or
2037 ( fbs.period_counter > c_imp_pc and p_flag = 'A') or
2038 ( p_flag = 'G'))
2039 GROUP by adj.transaction_header_id,
2040 adj.period_counter_created,
2041 fbs.reval_loss_balance
2042 ORDER by 1;
2043
2044 CURSOR c_mc_get_reval_losses is
2045 SELECT adj.transaction_header_id,
2046 sum(adj.adjustment_amount),
2047 adj.period_counter_created,
2048 fbs.reval_loss_balance
2049 FROM
2050 FA_MC_ADJUSTMENTS adj,
2051 fa_mc_books_summary fbs,
2052 fa_transaction_headers fth
2053 WHERE adj.asset_id = p_asset_hdr_rec.asset_id
2054 AND adj.book_type_code = p_asset_hdr_rec.book_type_code
2055 AND adj.adjustment_type in ('REVAL LOSS','REVAL RESERVE')
2056 AND adj.debit_credit_flag = 'DR'
2057 AND adj.asset_id = fth.asset_id
2058 AND adj.book_type_code = fth.book_type_code
2059 AND adj.transaction_header_id = fth.transaction_header_id
2060 AND fth.transaction_key = 'RL'
2061 AND adj.set_of_books_id = p_set_of_books_id
2062 AND adj.period_counter_created = fbs.period_counter
2063 AND fbs.asset_id = adj.asset_id
2064 AND fbs.book_type_code = adj.book_type_code
2065 AND fbs.set_of_books_id = adj.set_of_books_id
2066 AND fbs.reval_loss_balance <> 0
2067 AND (( fbs.period_counter <= c_imp_pc and p_flag = 'P') or
2068 ( fbs.period_counter > c_imp_pc and p_flag = 'A') or
2069 ( p_flag = 'G'))
2070 GROUP by adj.transaction_header_id,
2071 adj.period_counter_created,
2072 fbs.reval_loss_balance
2073 ORDER by 1;
2074
2075 l_tot_reval_loss_exp number := 0;
2076 l_tot_deprn_exp number := 0;
2077 l_net_reval_loss number := 0;
2078 l_deprn_exp number := 0;
2079 l_reval_loss number := 0;
2080 l_old_pc number := 0;
2081 l_loss_cpc number;
2082 c_th_id number;
2083 c_reval_loss_amount number;
2084 c_loss_period_counter number;
2085 c_unreversed_loss_amt number;
2086 c_reval_loss_sum number;
2087 calc_error exception;
2088
2089 CURSOR c_get_sum_reval_losses is
2090 SELECT sum(adj.adjustment_amount)
2091 FROM
2092 FA_ADJUSTMENTS adj
2093 WHERE adj.asset_id = p_asset_hdr_rec.asset_id
2094 AND adj.book_type_code = p_asset_hdr_rec.book_type_code
2095 AND adj.adjustment_type = 'REVAL LOSS'
2096 AND adj.debit_credit_flag = 'DR'
2097 AND adj.period_counter_created = c_loss_period_counter
2098 AND adj.transaction_header_id > c_th_id;
2099
2100 CURSOR c_mc_get_sum_reval_losses is
2101 SELECT sum(adj.adjustment_amount)
2102 FROM
2103 FA_MC_ADJUSTMENTS adj
2104 WHERE adj.asset_id = p_asset_hdr_rec.asset_id
2105 AND adj.book_type_code = p_asset_hdr_rec.book_type_code
2106 AND adj.adjustment_type = 'REVAL LOSS'
2107 AND adj.debit_credit_flag = 'DR'
2108 AND adj.set_of_books_id = p_set_of_books_id
2109 AND adj.period_counter_created = c_loss_period_counter
2110 AND adj.transaction_header_id > c_th_id;
2111
2112 CURSOR c_get_imp_pc_a is
2113 SELECT period_counter
2114 FROM FA_DEPRN_PERIODS
2115 WHERE book_type_code = p_asset_hdr_rec.book_type_code
2116 AND period_name in
2117 (SELECT fa_period
2118 FROM fa_sorp_asset_link_reval_v
2119 WHERE asset_id = p_asset_hdr_rec.asset_id
2120 AND book_type_code = p_asset_hdr_rec.book_type_code
2121 AND mass_reval_id = p_reval_options_rec.mass_reval_id);
2122
2123 CURSOR c_get_imp_pc_c is
2124 SELECT period_counter
2125 FROM FA_DEPRN_PERIODS fdp,
2126 fa_sorp_asset_link_reval_v salrv
2127 WHERE fdp.book_type_code = p_asset_hdr_rec.book_type_code
2128 AND salrv.asset_id = p_asset_hdr_rec.asset_id
2129 AND salrv.book_type_code = p_asset_hdr_rec.book_type_code
2130 AND salrv.mass_reval_id = p_reval_options_rec.mass_reval_id
2131 AND salrv.impairment_date between fdp.calendar_period_open_Date and fdp.calendar_period_close_Date
2132 AND rownum = 1
2133 order by salrv.impairment_date ;
2134
2135 BEGIN
2136 if p_reval_options_rec.reval_type_flag = 'A' then
2137 open c_get_imp_pc_a;
2138 fetch c_get_imp_pc_a into c_imp_pc;
2139 close c_get_imp_pc_a;
2140 elsif p_reval_options_rec.reval_type_flag = 'C' then
2141 open c_get_imp_pc_c;
2142 fetch c_get_imp_pc_c into c_imp_pc;
2143 close c_get_imp_pc_c;
2144 else
2145 c_imp_pc := -1;
2146 end if;
2147
2148 if p_mrc_sob_type_code = 'P' then
2149 open c_get_reval_losses;
2150 else
2151 open c_mc_get_reval_losses;
2152 end if;
2153 loop
2154 if p_mrc_sob_type_code = 'P' then
2155 fetch c_get_reval_losses into c_th_id,c_reval_loss_amount,c_loss_period_counter,c_unreversed_loss_amt ;
2156 if ( c_get_reval_losses%notfound ) then
2157 exit;
2158 end if;
2159 else
2160 fetch c_mc_get_reval_losses into c_th_id,c_reval_loss_amount,c_loss_period_counter,c_unreversed_loss_amt ;
2161 if ( c_mc_get_reval_losses%notfound ) then
2162 exit;
2163 end if;
2164 end if;
2165
2166 /*This is to take care of cases in which multiple reval losses are performed in same period.*/
2167 if l_old_pc <> c_loss_period_counter then
2168 l_loss_cpc := 0;
2169 l_old_pc := c_loss_period_counter;
2170 end if;
2171
2172 /*to calculate remaining sum of losses (if any) occured in same period after current loss,
2173 this will be used to determine reval loss balance remaining for current loss */
2174 if p_mrc_sob_type_code = 'P' then
2175 open c_get_sum_reval_losses;
2176 fetch c_get_sum_reval_losses into c_reval_loss_sum;
2177 close c_get_sum_reval_losses;
2178 else
2179 open c_mc_get_sum_reval_losses;
2180 fetch c_mc_get_sum_reval_losses into c_reval_loss_sum;
2181 close c_mc_get_sum_reval_losses;
2182 end if;
2183
2184 IF (p_log_level_rec.statement_level) THEN
2185 fa_debug_pkg.ADD (l_calling_fn,'Transaction _header_id :: ',c_th_id, p_log_level_rec => p_log_level_rec);
2186 fa_debug_pkg.ADD (l_calling_fn,'REVAL LOSS amount :: ',c_reval_loss_amount, p_log_level_rec => p_log_level_rec);
2187 fa_debug_pkg.ADD (l_calling_fn,'period counter :: ',c_loss_period_counter, p_log_level_rec => p_log_level_rec);
2188 fa_debug_pkg.ADD (l_calling_fn,'Un reversed reval loss amount :: ',c_unreversed_loss_amt, p_log_level_rec => p_log_level_rec);
2189 fa_debug_pkg.ADD (l_calling_fn,'Reval loss reversed so far for current period counter :: ',l_loss_cpc, p_log_level_rec => p_log_level_rec);
2190 fa_debug_pkg.ADD (l_calling_fn,'Sum of losses(if any) after current :: ',c_reval_loss_sum, p_log_level_rec => p_log_level_rec);
2191 END IF;
2192
2193 c_unreversed_loss_amt := c_unreversed_loss_amt - nvl(c_reval_loss_sum,0) - l_loss_cpc;
2194 IF (p_log_level_rec.statement_level) THEN
2195 fa_debug_pkg.ADD (l_calling_fn,'Un reversed reval loss amount for current loss:: ',c_unreversed_loss_amt, p_log_level_rec => p_log_level_rec);
2196 END IF;
2197 l_reval_loss := c_reval_loss_amount ;
2198
2199 /*Need to calculated deprn effect only when some reval loss is pending for current loss row */
2200 if c_unreversed_loss_amt > 0 then
2201 if P_fully_rsvd_flag then
2202 l_deprn_exp := c_reval_loss_amount;
2203 else
2204 if not calculate_deprn_exp(p_asset_hdr_rec => p_asset_hdr_rec,
2205 p_period_rec => p_period_rec ,
2206 p_mrc_sob_type_code => p_mrc_sob_type_code,
2207 p_set_of_books_id => p_set_of_books_id,
2208 p_th_id => c_th_id,
2209 p_loss_pc => c_loss_period_counter,
2210 x_deprn_exp => l_deprn_exp,
2211 p_log_level_rec => p_log_level_rec ) then
2212 raise calc_error;
2213 end if;
2214 end if;
2215 else
2216 goto skip_row; --This loss is already reversed goto next loss
2217 end if;
2218 IF (p_log_level_rec.statement_level) THEN
2219 fa_debug_pkg.ADD (l_calling_fn,'Deprn effect :: ',l_deprn_exp, p_log_level_rec => p_log_level_rec);
2220 END IF;
2221
2222 /* if partial reversal of reval loss has happened earlier then prorate expenses accordingly.*/
2223 IF c_unreversed_loss_amt < c_reval_loss_amount THEN
2224 l_deprn_exp := ( l_deprn_exp / c_reval_loss_amount) * c_unreversed_loss_amt;
2225 l_reval_loss := c_unreversed_loss_amt;
2226 IF NOT fa_utils_pkg.faxrnd (l_deprn_exp,
2227 p_asset_hdr_rec.book_type_code,
2228 p_set_of_books_id,
2229 p_log_level_rec => p_log_level_rec)
2230 THEN
2231 RAISE calc_error;
2232 END IF;
2233 END if;
2234
2235 l_net_reval_loss := l_reval_loss - l_deprn_exp; /*Calculate net revaluation loss */
2236 IF (p_log_level_rec.statement_level) THEN
2237 fa_debug_pkg.ADD (l_calling_fn,'Deprn effect at 1 :: ',l_deprn_exp, p_log_level_rec => p_log_level_rec);
2238 fa_debug_pkg.ADD (l_calling_fn,'Reval loss amount at 1 :: ',l_reval_loss, p_log_level_rec => p_log_level_rec);
2239 fa_debug_pkg.ADD (l_calling_fn,'Net revaluation loss amount at 1 :: ',l_net_reval_loss, p_log_level_rec => p_log_level_rec);
2240 fa_debug_pkg.ADD (l_calling_fn,'Revaluation gain amount at 1 :: ',px_gain_amt, p_log_level_rec => p_log_level_rec);
2241 END IF;
2242
2243 /*if gain is less then net revaluation loss then prorate net revaluation loss and depreciation expense accordingly*/
2244 if px_gain_amt < l_net_reval_loss then
2245 l_deprn_exp := ( l_deprn_exp / l_net_reval_loss ) * px_gain_amt;
2246 l_net_reval_loss := px_gain_amt;
2247 end if;
2248 IF NOT fa_utils_pkg.faxrnd (l_deprn_exp,
2249 p_asset_hdr_rec.book_type_code,
2250 p_set_of_books_id,
2251 p_log_level_rec => p_log_level_rec)
2252 THEN
2253 RAISE calc_error;
2254 END IF;
2255
2256 l_tot_reval_loss_exp := l_tot_reval_loss_exp + l_net_reval_loss;
2257 l_tot_deprn_exp := l_tot_deprn_exp + l_deprn_exp;
2258
2259
2260 px_gain_amt := px_gain_amt - l_net_reval_loss ;
2261 l_loss_cpc := l_loss_cpc + (l_net_reval_loss + l_deprn_exp);
2262 IF (p_log_level_rec.statement_level) THEN
2263 fa_debug_pkg.ADD (l_calling_fn,'Deprn effect at 2:: ',l_deprn_exp, p_log_level_rec => p_log_level_rec);
2264 fa_debug_pkg.ADD (l_calling_fn,'Net revaluation loss amount at 2 :: ',l_net_reval_loss, p_log_level_rec => p_log_level_rec);
2265 fa_debug_pkg.ADD (l_calling_fn,'total Deprn effect :: ',l_tot_deprn_exp, p_log_level_rec => p_log_level_rec);
2266 fa_debug_pkg.ADD (l_calling_fn,'total revaluation loss expense amount :: ',l_tot_reval_loss_exp, p_log_level_rec => p_log_level_rec);
2267 fa_debug_pkg.ADD (l_calling_fn,'New revaluation gain amount :: ',px_gain_amt, p_log_level_rec => p_log_level_rec);
2268 fa_debug_pkg.ADD (l_calling_fn,'Remaining reval loss amount :: ',(l_reval_loss -(l_net_reval_loss + l_deprn_exp)), p_log_level_rec => p_log_level_rec);
2269 fa_debug_pkg.ADD (l_calling_fn,'Reval loss reversed so far for current period counter at end:: ',l_loss_cpc, p_log_level_rec => p_log_level_rec);
2270 END IF;
2271
2272 IF (p_reval_options_rec.run_mode = 'RUN') THEN
2273 IF p_mrc_sob_type_code = 'P' THEN
2274 update fa_books_summary set reval_loss_balance = reval_loss_balance - (l_net_reval_loss + l_deprn_exp)
2275 where asset_id = p_asset_hdr_rec.asset_id
2276 and book_type_code = p_asset_hdr_rec.book_type_code
2277 and period_counter >= c_loss_period_counter ;
2278 ELSE
2279 update fa_mc_books_summary set reval_loss_balance = reval_loss_balance - (l_net_reval_loss + l_deprn_exp)
2280 where asset_id = p_asset_hdr_rec.asset_id
2281 and book_type_code = p_asset_hdr_rec.book_type_code
2282 and set_of_books_id = p_set_of_books_id
2283 and period_counter >= c_loss_period_counter ;
2284 END IF;
2285 END IF;
2286 <<skip_row>>
2287 if px_gain_amt = 0 then
2288 exit;
2289 end if;
2290 end loop;
2291 if p_mrc_sob_type_code = 'P' then
2292 close c_get_reval_losses;
2293 else
2294 close c_mc_get_reval_losses;
2295 end if;
2296 px_reval_loss_exp := px_reval_loss_exp + l_tot_reval_loss_exp;
2297 px_deprn_exp := px_deprn_exp + l_tot_deprn_exp;
2298 return true;
2299 EXCEPTION
2300 WHEN calc_error THEN
2301 IF (p_log_level_rec.statement_level) THEN
2302 fa_debug_pkg.ADD (l_calling_fn,'Inside exception','calc_error', p_log_level_rec => p_log_level_rec);
2303 END IF;
2304 RETURN FALSE;
2305 WHEN OTHERS THEN
2306 IF (p_log_level_rec.statement_level) THEN
2307 fa_debug_pkg.ADD (l_calling_fn,'Inside exception','OTHERS', p_log_level_rec => p_log_level_rec);
2308 END IF;
2309 RETURN FALSE;
2310 end process_loss_reversal;
2311
2312 FUNCTION calculate_deprn_exp(
2313 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
2314 p_period_rec IN FA_API_TYPES.period_rec_type,
2315 p_mrc_sob_type_code IN VARCHAR2,
2316 p_set_of_books_id IN NUMBER,
2317 p_th_id IN NUMBER,
2318 p_loss_pc IN NUMBER,
2319 x_deprn_exp OUT NOCOPY NUMBER,
2320 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
2321
2322 l_calling_fn varchar2(60) := 'FA_SORP3_PVT.calculate_deprn_exp';
2323
2324 CURSOR c_get_pr_asset_rec Is
2325 SELECT book_type_code,asset_id,recoverable_cost,salvage_value,adjusted_recoverable_cost,adjusted_cost,old_adjusted_cost,
2326 formula_factor,rate_Adjustment_factor,eofy_reserve,deprn_method_code,life_in_months,adjusted_rate,production_capacity,
2327 adjusted_capacity,bonus_rule,ceiling_name,reval_amortization_basis,date_placed_in_service,prorate_date,original_deprn_start_date,
2328 tracking_method,period_counter_life_complete,annual_deprn_rounding_flag
2329 FROM FA_BOOKS
2330 WHERE TRANSACTION_HEADER_ID_OUT = p_th_id;
2331
2332 CURSOR c_mc_get_pr_asset_rec Is
2333 SELECT book_type_code,asset_id,recoverable_cost,salvage_value,adjusted_recoverable_cost,adjusted_cost,old_adjusted_cost,
2334 formula_factor,rate_Adjustment_factor,eofy_reserve,deprn_method_code,life_in_months,adjusted_rate,production_capacity,
2335 adjusted_capacity,bonus_rule,ceiling_name,reval_amortization_basis,date_placed_in_service,prorate_date,original_deprn_start_date,
2336 tracking_method,period_counter_life_complete,annual_deprn_rounding_flag
2337 FROM FA_MC_BOOKS
2338 WHERE TRANSACTION_HEADER_ID_OUT = p_th_id
2339 AND SET_OF_BOOKS_ID = p_set_of_books_id;
2340
2341 CURSOR c_get_asset_rec Is
2342 SELECT book_type_code,asset_id,recoverable_cost,salvage_value,adjusted_recoverable_cost,adjusted_cost,old_adjusted_cost,
2343 formula_factor,rate_Adjustment_factor,eofy_reserve,deprn_method_code,life_in_months,adjusted_rate,production_capacity,
2344 adjusted_capacity,bonus_rule,ceiling_name,reval_amortization_basis,date_placed_in_service,prorate_date,ORIGINAL_DEPRN_START_DATE,
2345 tracking_method,period_counter_life_complete,annual_deprn_rounding_flag
2346 FROM FA_BOOKS
2347 WHERE TRANSACTION_HEADER_ID_IN = p_th_id;
2348
2349 CURSOR c_mc_get_asset_rec Is
2350 SELECT book_type_code,asset_id,recoverable_cost,salvage_value,adjusted_recoverable_cost,adjusted_cost,old_adjusted_cost,
2351 formula_factor,rate_Adjustment_factor,eofy_reserve,deprn_method_code,life_in_months,adjusted_rate,production_capacity,
2352 adjusted_capacity,bonus_rule,ceiling_name,reval_amortization_basis,date_placed_in_service,prorate_date,ORIGINAL_DEPRN_START_DATE,
2353 tracking_method,period_counter_life_complete,annual_deprn_rounding_flag
2354 FROM FA_MC_BOOKS
2355 WHERE TRANSACTION_HEADER_ID_IN = p_th_id
2356 AND SET_OF_BOOKS_ID = p_set_of_books_id;
2357
2358 cursor c_get_fiscal_name is
2359 Select fiscal_year,period_num
2360 from FA_DEPRN_PERIODS
2361 where book_type_code = p_asset_hdr_rec.book_type_code
2362 and period_counter = p_loss_pc;
2363
2364 l_dpr_in FA_STD_TYPES.dpr_struct;
2365 l_dpr_out FA_STD_TYPES.dpr_out_struct;
2366 l_dpr_arr FA_STD_TYPES.dpr_arr_type;
2367 l_running_mode VARCHAR2(20);
2368 g_temp_number number;
2369 g_temp_integer binary_integer;
2370 g_temp_boolean boolean;
2371 g_temp_varchar2 varchar2(100);
2372 c_fin_rec c_get_pr_asset_rec%rowtype;
2373 l_hyp_deprn_exp number;
2374 l_act_deprn_exp number;
2375 l_beg_fiscal_yr number;
2376 l_beg_per_num number;
2377 dpr_err exception;
2378
2379 BEGIN
2380
2381 if not FA_CACHE_PKG.fazcbc(X_book => p_asset_hdr_rec.book_type_code, p_log_level_rec => p_log_level_rec) then
2382 raise dpr_err;
2383 end if;
2384
2385 if not fa_cache_pkg.fazcct(fa_cache_pkg.fazcbc_record.deprn_calendar, p_log_level_rec => p_log_level_rec) then
2386 raise dpr_err;
2387 end if;
2388
2389
2390 open c_get_fiscal_name;
2391 fetch c_get_fiscal_name into l_beg_fiscal_yr,l_beg_per_num;
2392 close c_get_fiscal_name;
2393
2394 IF (p_log_level_rec.statement_level) THEN
2395 fa_debug_pkg.add(l_calling_fn, 'Starting fiscal year',l_beg_fiscal_yr, p_log_level_rec => p_log_level_rec);
2396 fa_debug_pkg.add(l_calling_fn, 'Starting l_beg_per_num',l_beg_per_num, p_log_level_rec => p_log_level_rec);
2397 fa_debug_pkg.add(l_calling_fn, 'Current period counter',p_period_rec.period_counter, p_log_level_rec => p_log_level_rec);
2398 fa_debug_pkg.add(l_calling_fn, 'fetching','prior row', p_log_level_rec => p_log_level_rec);
2399 END IF;
2400
2401 IF p_loss_pc = p_period_rec.period_counter then
2402 x_deprn_exp := 0;
2403 return true;
2404 end if;
2405 if p_mrc_sob_type_code = 'P' then
2406 open c_get_pr_asset_rec;
2407 fetch c_get_pr_asset_rec into c_fin_rec;
2408 close c_get_pr_asset_rec;
2409 else
2410 open c_mc_get_pr_asset_rec;
2411 fetch c_mc_get_pr_asset_rec into c_fin_rec;
2412 close c_mc_get_pr_asset_rec;
2413 end if;
2414 l_dpr_in.calendar_type := fa_cache_pkg.fazcbc_record.deprn_calendar;
2415 l_dpr_in.book := c_fin_rec.book_type_code;
2416 l_dpr_in.asset_id := c_fin_rec.asset_id;
2417 l_dpr_in.rec_cost := c_fin_rec.recoverable_cost;
2418 l_dpr_in.salvage_value := c_fin_rec.salvage_value;
2419 l_dpr_in.adj_rec_cost := c_fin_rec.adjusted_recoverable_cost;
2420 l_dpr_in.adj_cost := c_fin_rec.adjusted_cost;
2421 l_dpr_in.old_adj_cost := c_fin_rec.old_adjusted_cost;
2422 l_dpr_in.formula_factor := c_fin_rec.formula_factor;
2423 l_dpr_in.rate_adj_factor := c_fin_rec.rate_Adjustment_factor;
2424 l_dpr_in.eofy_reserve := c_fin_rec.eofy_reserve;
2425 l_dpr_in.method_code := c_fin_rec.deprn_method_code;
2426 l_dpr_in.life := c_fin_rec.life_in_months;
2427 l_dpr_in.adj_rate := c_fin_rec.adjusted_rate;
2428 l_dpr_in.capacity := c_fin_rec.production_capacity;
2429 l_dpr_in.adj_capacity := c_fin_rec.adjusted_capacity;
2430 l_dpr_in.bonus_rule := c_fin_rec.bonus_rule;
2431 l_dpr_in.ceil_name := c_fin_rec.ceiling_name;
2432 l_dpr_in.reval_amo_basis := c_fin_rec.reval_amortization_basis;
2433 l_dpr_in.jdate_in_service := to_number(to_char(c_fin_rec.date_placed_in_service, 'J'));
2434 l_dpr_in.prorate_jdate := to_number(to_char(c_fin_rec.prorate_date, 'J'));
2435 l_dpr_in.deprn_start_jdate := to_number(to_char(c_fin_rec.date_placed_in_service, 'J'));
2436 l_dpr_in.prorate_date := c_fin_rec.prorate_date;
2437 l_dpr_in.orig_deprn_start_date := c_fin_rec.ORIGINAL_DEPRN_START_DATE;
2438 l_dpr_in.jdate_retired := 0;
2439 l_dpr_in.ret_prorate_jdate := 0;
2440 l_dpr_in.ltd_prod := 0;
2441 l_dpr_in.ytd_deprn := 0;
2442 l_dpr_in.deprn_rsv := 0;
2443 l_dpr_in.reval_rsv := 0;
2444 l_dpr_in.bonus_deprn_exp := 0;
2445 l_dpr_in.bonus_ytd_deprn := 0;
2446 l_dpr_in.bonus_deprn_rsv := 0;
2447 l_dpr_in.prior_fy_exp := 0;
2448 l_dpr_in.prior_fy_bonus_exp := 0;
2449 l_dpr_in.impairment_exp := 0;
2450 l_dpr_in.ytd_impairment := 0;
2451 l_dpr_in.impairment_rsv := 0;
2452 l_dpr_in.super_group_id := NULL;
2453 l_dpr_in.over_depreciate_option := NULL;
2454 l_dpr_in.tracking_method := c_fin_rec.tracking_method;
2455 l_dpr_in.pc_life_end := c_fin_rec.period_counter_life_complete;
2456 l_dpr_in.deprn_override_flag := fa_std_types.FA_NO_OVERRIDE;
2457 l_dpr_in.rsv_known_flag := TRUE;
2458 l_dpr_in.deprn_rounding_flag := 'REV';
2459 l_dpr_in.used_by_adjustment := FALSE;
2460 l_dpr_in.set_of_books_id := p_set_of_books_id;
2461 l_dpr_in.mrc_sob_type_code := p_mrc_sob_type_code; -- Bug 9700559
2462 l_running_mode := fa_std_types.FA_DPR_NORMAL;
2463
2464 l_dpr_in.y_begin := l_beg_fiscal_yr;
2465 l_dpr_in.p_cl_begin := l_beg_per_num;
2466 l_dpr_in.y_end := p_period_rec.fiscal_year;
2467 l_dpr_in.p_cl_end := p_period_rec.period_num ;
2468
2469 if l_dpr_in.p_cl_end = 1 then
2470 l_dpr_in.p_cl_end := fa_cache_pkg.fazcct_record.number_per_fiscal_year;
2471 l_dpr_in.y_end := l_dpr_in.y_end - 1;
2472 else
2473 l_dpr_in.p_cl_end := l_dpr_in.p_cl_end - 1;
2474 end if;
2475
2476 IF (p_log_level_rec.statement_level) THEN
2477 fa_debug_pkg.add(l_calling_fn, 'ending fiscal year',l_dpr_in.y_end, p_log_level_rec => p_log_level_rec);
2478 fa_debug_pkg.add(l_calling_fn, 'ending l_beg_per_num',l_dpr_in.p_cl_end, p_log_level_rec => p_log_level_rec);
2479 fa_debug_pkg.add(l_calling_fn, 'Before calling ','faxcde at 1', p_log_level_rec => p_log_level_rec);
2480 END IF;
2481
2482 if not FA_CDE_PKG.faxcde(l_dpr_in,
2483 l_dpr_arr,
2484 l_dpr_out,
2485 l_running_mode,
2486 p_log_level_rec => p_log_level_rec) then
2487 if (p_log_level_rec.statement_level) then
2488 fa_debug_pkg.add(l_calling_fn, 'Error calling','FA_CDE_PKG.faxcde', p_log_level_rec => p_log_level_rec);
2489 end if;
2490 raise dpr_err;
2491 end if;
2492
2493 l_hyp_deprn_exp := l_dpr_out.new_deprn_rsv;
2494
2495 IF NOT fa_utils_pkg.faxrnd (l_hyp_deprn_exp,
2496 p_asset_hdr_rec.book_type_code,
2497 p_set_of_books_id,
2498 p_log_level_rec => p_log_level_rec) THEN
2499 RAISE dpr_err;
2500 END IF;
2501
2502 if (p_log_level_rec.statement_level) then
2503 fa_debug_pkg.add(l_calling_fn, 'Deprn rsv without considering reval loss at 1',l_hyp_deprn_exp, p_log_level_rec => p_log_level_rec);
2504 fa_debug_pkg.add(l_calling_fn, 'fetching','reval loss row', p_log_level_rec => p_log_level_rec);
2505 end if;
2506
2507 if p_mrc_sob_type_code = 'P' then
2508 open c_get_asset_rec;
2509 fetch c_get_asset_rec into c_fin_rec;
2510 close c_get_asset_rec;
2511 else
2512 open c_mc_get_asset_rec;
2513 fetch c_mc_get_asset_rec into c_fin_rec;
2514 close c_mc_get_asset_rec;
2515 end if;
2516 l_dpr_in.calendar_type := fa_cache_pkg.fazcbc_record.deprn_calendar;
2517 l_dpr_in.book := c_fin_rec.book_type_code;
2518 l_dpr_in.asset_id := c_fin_rec.asset_id;
2519 l_dpr_in.rec_cost := c_fin_rec.recoverable_cost;
2520 l_dpr_in.salvage_value := c_fin_rec.salvage_value;
2521 l_dpr_in.adj_rec_cost := c_fin_rec.adjusted_recoverable_cost;
2522 l_dpr_in.adj_cost := c_fin_rec.adjusted_cost;
2523 l_dpr_in.old_adj_cost := c_fin_rec.old_adjusted_cost;
2524 l_dpr_in.formula_factor := c_fin_rec.formula_factor;
2525 l_dpr_in.rate_adj_factor := c_fin_rec.rate_Adjustment_factor;
2526 l_dpr_in.eofy_reserve := c_fin_rec.eofy_reserve;
2527 l_dpr_in.method_code := c_fin_rec.deprn_method_code;
2528 l_dpr_in.life := c_fin_rec.life_in_months;
2529 l_dpr_in.adj_rate := c_fin_rec.adjusted_rate;
2530 l_dpr_in.capacity := c_fin_rec.production_capacity;
2531 l_dpr_in.adj_capacity := c_fin_rec.adjusted_capacity;
2532 l_dpr_in.bonus_rule := c_fin_rec.bonus_rule;
2533 l_dpr_in.ceil_name := c_fin_rec.ceiling_name;
2534 l_dpr_in.reval_amo_basis := c_fin_rec.reval_amortization_basis;
2535 l_dpr_in.jdate_in_service := to_number(to_char(c_fin_rec.date_placed_in_service, 'J'));
2536 l_dpr_in.prorate_jdate := to_number(to_char(c_fin_rec.prorate_date, 'J'));
2537 l_dpr_in.deprn_start_jdate := to_number(to_char(c_fin_rec.date_placed_in_service, 'J'));
2538 l_dpr_in.prorate_date := c_fin_rec.prorate_date;
2539 l_dpr_in.orig_deprn_start_date := c_fin_rec.ORIGINAL_DEPRN_START_DATE;
2540 l_dpr_in.jdate_retired := 0;
2541 l_dpr_in.ret_prorate_jdate := 0;
2542 l_dpr_in.ltd_prod := 0;
2543 l_dpr_in.ytd_deprn := 0;
2544 l_dpr_in.deprn_rsv := 0;
2545 l_dpr_in.reval_rsv := 0;
2546 l_dpr_in.bonus_deprn_exp := 0;
2547 l_dpr_in.bonus_ytd_deprn := 0;
2548 l_dpr_in.bonus_deprn_rsv := 0;
2549 l_dpr_in.prior_fy_exp := 0;
2550 l_dpr_in.prior_fy_bonus_exp := 0;
2551 l_dpr_in.impairment_exp := 0;
2552 l_dpr_in.ytd_impairment := 0;
2553 l_dpr_in.impairment_rsv := 0;
2554 l_dpr_in.super_group_id := NULL;
2555 l_dpr_in.over_depreciate_option := NULL;
2556 l_dpr_in.pc_life_end := c_fin_rec.period_counter_life_complete;
2557 l_dpr_in.deprn_override_flag := fa_std_types.FA_NO_OVERRIDE;
2558 l_dpr_in.rsv_known_flag := TRUE;
2559 l_dpr_in.deprn_rounding_flag := 'REV';
2560 l_dpr_in.used_by_adjustment := FALSE;
2561 l_dpr_in.set_of_books_id := p_set_of_books_id;
2562 l_dpr_in.mrc_sob_type_code := p_mrc_sob_type_code; -- Bug 9700559
2563 l_running_mode := fa_std_types.FA_DPR_NORMAL;
2564 l_dpr_in.y_begin := l_beg_fiscal_yr;
2565 l_dpr_in.p_cl_begin := l_beg_per_num;
2566 l_dpr_in.y_end := p_period_rec.fiscal_year;
2567 l_dpr_in.p_cl_end := p_period_rec.period_num ;
2568
2569 if l_dpr_in.p_cl_end = 1 then
2570 l_dpr_in.p_cl_end := fa_cache_pkg.fazcct_record.number_per_fiscal_year;
2571 l_dpr_in.y_end := l_dpr_in.y_end - 1;
2572 else
2573 l_dpr_in.p_cl_end := l_dpr_in.p_cl_end - 1;
2574 end if;
2575
2576 if (p_log_level_rec.statement_level) then
2577 fa_debug_pkg.add(l_calling_fn, 'Before calling ','faxcde at 2', p_log_level_rec => p_log_level_rec);
2578 end if;
2579
2580 if not FA_CDE_PKG.faxcde(l_dpr_in,
2581 l_dpr_arr,
2582 l_dpr_out,
2583 l_running_mode,
2584 p_log_level_rec => p_log_level_rec) then
2585 if (p_log_level_rec.statement_level) then
2586 fa_debug_pkg.add(l_calling_fn, 'Error calling','FA_CDE_PKG.faxcde', p_log_level_rec => p_log_level_rec);
2587 end if;
2588
2589 raise dpr_err;
2590 end if;
2591
2592 l_act_deprn_exp := l_dpr_out.new_deprn_rsv;
2593
2594 IF NOT fa_utils_pkg.faxrnd (l_act_deprn_exp,
2595 p_asset_hdr_rec.book_type_code,
2596 p_set_of_books_id,
2597 p_log_level_rec => p_log_level_rec) THEN
2598 RAISE dpr_err;
2599 END IF;
2600
2601 x_deprn_exp := l_hyp_deprn_exp - l_act_deprn_exp;
2602 if (p_log_level_rec.statement_level) then
2603 fa_debug_pkg.add(l_calling_fn, 'Deprn rsv with reval loss at 2',l_hyp_deprn_exp, p_log_level_rec => p_log_level_rec);
2604 fa_debug_pkg.add(l_calling_fn, 'Deprn effect because of reval loss',x_deprn_exp, p_log_level_rec => p_log_level_rec);
2605 end if;
2606 return true;
2607 EXCEPTION
2608 WHEN dpr_err THEN
2609 IF (p_log_level_rec.statement_level) THEN
2610 fa_debug_pkg.ADD (l_calling_fn,'Inside exception','depr_err', p_log_level_rec => p_log_level_rec);
2611 END IF;
2612 RETURN FALSE;
2613 WHEN OTHERS THEN
2614 IF (p_log_level_rec.statement_level) THEN
2615 fa_debug_pkg.ADD (l_calling_fn,'Inside exception','OTHERS', p_log_level_rec => p_log_level_rec);
2616 END IF;
2617 RETURN FALSE;
2618 END calculate_deprn_exp;
2619
2620 END fa_sorp_revaluation_pkg;