[Home] [Help]
PACKAGE BODY: APPS.FA_SORP_IMPAIRMENT_PVT
Source
1 PACKAGE BODY FA_SORP_IMPAIRMENT_PVT AS
2 /* $Header: FAVSIMPB.pls 120.4 2010/12/20 12:26:00 gigupta noship $ */
3
4 --
5 -- Datatypes for pl/sql tables below
6 --
7 TYPE tab_rowid_type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
8 TYPE tab_num15_type IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
9 TYPE tab_num_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
10 TYPE tab_date_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
11 TYPE tab_char1_type IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
12 TYPE tab_char3_type IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
13 TYPE tab_char15_type IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
14 TYPE tab_char30_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
15
16
17 /*#
18 * Thus function is used to create the impairment accounting entries for SORP
19 * for a particular impairment classification type
20 * This function is invoked from the function 'create_sorp_imp_acct' during
21 * the posting of an impairment
22 * @param p_impair_class The impairment classification code for which accounting
23 must be done
24 * @param p_impair_loss_acct The impairment loss account specified by the user
25 * @param p_impairment_amount The impairment loss amount
26 * @param p_reval_reserve_adj The amount of revaluation reserve that was adjusted
27 * @param px_adj Adjustment record, needs to be prepopulated
28 * @param p_created_by Standard WHO column
29 * @param p_creation_date Standard WHO column
30 * @rep:scope private
31 * @rep:lifecycle active
32 * @rep:displayname create_acct_impair_class
33 * @rep:compatibility S
34 */
35 FUNCTION create_acct_impair_class (
36 p_impair_class IN VARCHAR2,
37 p_impair_loss_acct IN VARCHAR2,
38 p_impairment_amount IN NUMBER,
39 p_reval_reserve_adj IN NUMBER,
40 px_adj IN OUT NOCOPY FA_ADJUST_TYPE_PKG.FA_ADJ_ROW_STRUCT,
41 p_created_by IN NUMBER,
42 p_creation_date IN DATE
43 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN IS
44 pos_err EXCEPTION;
45 l_calling_fn varchar2(60) := 'create_acct_impair_class';
46 l_mode varchar2(20) := 'RUNNING POST';
47 BEGIN
48
49 IF (p_log_level_rec.statement_level) THEN
50 fa_debug_pkg.add(l_calling_fn,'p_impair_class', p_impair_class, p_log_level_rec => p_log_level_rec);
51 fa_debug_pkg.add(l_calling_fn,'p_impair_loss_acct', p_impair_loss_acct, p_log_level_rec => p_log_level_rec);
52 fa_debug_pkg.add(l_calling_fn,'p_impairment_amount', p_impairment_amount, p_log_level_rec => p_log_level_rec);
53 fa_debug_pkg.add(l_calling_fn,'p_reval_reserve_adj', p_reval_reserve_adj, p_log_level_rec => p_log_level_rec);
54 fa_debug_pkg.add(l_calling_fn,'px_adj.book_type_code', px_adj.book_type_code, p_log_level_rec => p_log_level_rec);
55 fa_debug_pkg.add(l_calling_fn,'p_created_by', p_created_by, p_log_level_rec => p_log_level_rec);
56 fa_debug_pkg.add(l_calling_fn,'p_creation_date', p_creation_date, p_log_level_rec => p_log_level_rec);
57 END IF;
58
59 --Leveling is not required for SORP entries
60 px_adj.leveling_flag := FALSE;
61
62
63 --******************************************************
64 -- SORP Accumulated Impairment
65 --******************************************************
66 px_adj.adjustment_amount := p_impairment_amount + nvl(p_reval_reserve_adj,0);
67 px_adj.adjustment_type := 'IMPAIR RESERVE';
68 px_adj.account_type := 'IMPAIR_RESERVE_ACCT';
69 px_adj.account := fa_cache_pkg.fazccb_record.impair_reserve_acct;
70 px_adj.debit_credit_flag := 'CR';
71 px_adj.insertion_order := null;
72 IF (p_log_level_rec.statement_level) THEN
73 fa_debug_pkg.add(l_calling_fn,'Calling faxinaj for '
74 , 'SORP Enabled Accumulated Impairments', p_log_level_rec => p_log_level_rec);
75 END IF;
76
77 IF NOT FA_INS_ADJUST_PKG.faxinaj (px_adj,
78 p_creation_date,
79 p_created_by, p_log_level_rec => p_log_level_rec) THEN
80 RAISE pos_err;
81 END IF;
82
83 --******************************************************
84 -- SORP Impairment Expense
85 --******************************************************
86 px_adj.adjustment_amount := p_impairment_amount;
87 px_adj.adjustment_type := 'IMPAIR EXPENSE';
88 px_adj.account_type := 'IMPAIR_EXPENSE_ACCT';
89 IF p_impair_loss_acct IS NOT NULL THEN
90 px_adj.account := p_impair_loss_acct;
91 ELSE
92 px_adj.account := fa_cache_pkg.fazccb_record.impair_expense_acct;
93 END IF;
94 px_adj.debit_credit_flag := 'DR';
95 px_adj.insertion_order := p_impair_loss_acct;
96 IF (p_log_level_rec.statement_level) THEN
97 fa_debug_pkg.add(l_calling_fn,'Calling faxinaj for '
98 , 'SORP Enabled Impairments Expense', p_log_level_rec => p_log_level_rec);
99 END IF;
100
101 IF NOT FA_INS_ADJUST_PKG.faxinaj (px_adj,
102 p_creation_date,
103 p_created_by, p_log_level_rec => p_log_level_rec) THEN
104 RAISE pos_err;
105 END IF;
106 px_adj.insertion_order := null;
107 IF (p_reval_reserve_adj <> 0) THEN
108 --******************************************************
109 -- SORP Revaluation Reserve CEB
110 --******************************************************
111 px_adj.adjustment_amount := p_reval_reserve_adj;
112 px_adj.adjustment_type := 'REVAL RESERVE';
113 px_adj.account_type := 'REVAL_RESERVE_ACCT';
114 px_adj.account := fa_cache_pkg.fazccb_record.reval_reserve_acct;
115 px_adj.debit_credit_flag := 'DR';
116
117 IF (p_log_level_rec.statement_level) THEN
118 fa_debug_pkg.add(l_calling_fn,'Calling faxinaj for '
119 , 'SORP Reval Reserve', p_log_level_rec => p_log_level_rec);
120 END IF;
121
122 IF NOT FA_INS_ADJUST_PKG.faxinaj (px_adj,
123 p_creation_date,
124 p_created_by, p_log_level_rec => p_log_level_rec) THEN
125 RAISE pos_err;
126 END IF;
127 END IF; --End t_reval_reserve_adj_amount(i) <> 0
128
129 IF NOT FA_SORP_UTIL_PVT.create_sorp_neutral_acct (
130 p_impairment_amount
131 , 'N'
132 , px_adj
133 , p_created_by
134 , p_creation_date
135 , p_log_level_rec => p_log_level_rec) THEN
136 fa_debug_pkg.add(l_calling_fn,'Error at create_sorp_neutral_acct',
137 'for SORP for type CEB', p_log_level_rec => p_log_level_rec);
138 RETURN FALSE;
139 END IF;
140
141
142 IF (p_log_level_rec.statement_level) THEN
143 fa_debug_pkg.add(l_calling_fn,'create_acct_impair_class completed', 'Success', p_log_level_rec => p_log_level_rec);
144 END IF;
145
146 --Reset the flag to True
147 px_adj.leveling_flag := TRUE;
148
149 RETURN TRUE;
150
151 EXCEPTION
152 WHEN pos_err THEN
153
154 IF (p_log_level_rec.statement_level) THEN
155 fa_debug_pkg.add(l_calling_fn,'exception at create_acct_impair_class', 'pos_err', p_log_level_rec => p_log_level_rec);
156 fa_debug_pkg.add(l_calling_fn,'sqlerrm', substrb(sqlerrm, 1, 200));
157 END IF;
158
159 RETURN FALSE;
160
161 WHEN OTHERS THEN
162
163 IF (p_log_level_rec.statement_level) THEN
164 fa_debug_pkg.add(l_calling_fn,'exception at create_acct_impair_class', 'OTHERS', p_log_level_rec => p_log_level_rec);
165 fa_debug_pkg.add(l_calling_fn,'sqlerrm', substrb(sqlerrm, 1, 200));
166 END IF;
167
168 RETURN FALSE;
169 END;
170
171
172 /*#
173 * Thus function is used to create the impairment accounting entries a SORP
174 * impairment
175 * This function is invoked from the package FA_IMPAIRMENT_POST_PVT during
176 * the posting of an impairment
177 * @param px_adj Adjustment record, needs to be prepopulated
178 * @param p_impairment_amount The impairment loss amount
179 * @param p_reval_reserve_adj The amount of revaluation reserve that was adjusted
180 * @param p_impair_class The impairment classification code for which accounting
181 must be done
182 * @param p_impair_loss_acct The impairment loss account specified by the user
183 * @param p_split_impair_flag Indicates whether the impairment is split or not
184 * @param p_split1_impair_class The impairment classification type for Split 1
185 * @param p_split1_loss_amount The impairment loss amount for Split 1
186 * @param p_split1_reval_reserve The amount of revaluation reserve that was
187 adjustment for Split 1
188 * @param p_split1_loss_acct The impairment loss account for Split 1
189 * @param p_split2_impair_class The impairment classification type for Split 2
190 * @param p_split2_loss_amount The impairment loss amount for Split 2
191 * @param p_split2_reval_reserve The amount of revaluation reserve that was
192 adjustment for Split 2
193 * @param p_split2_loss_acct The impairment loss account for Split 2
194 * @param p_split3_impair_class The impairment classification type for Split 3
195 * @param p_split3_loss_amount The impairment loss amount for Split 3
196 * @param p_split3_reval_reserve The amount of revaluation reserve that was
197 adjustment for Split 3
198 * @param p_split3_loss_acct The impairment loss account for Split 3
199 * @param p_created_by Standard WHO column
200 * @param p_creation_date Standard WHO column
201 * @rep:scope private
202 * @rep:lifecycle active
203 * @rep:displayname create_sorp_imp_acct
204 * @rep:compatibility S
205 */
206 FUNCTION create_sorp_imp_acct (
207 px_adj IN OUT NOCOPY FA_ADJUST_TYPE_PKG.FA_ADJ_ROW_STRUCT,
208 p_impairment_amount IN NUMBER,
209 p_reval_reserve_adj IN NUMBER,
210
211 p_impair_class IN VARCHAR2,
212 p_impair_loss_acct IN VARCHAR2,
213 p_split_impair_flag IN VARCHAR2,
214
215 p_split1_impair_class IN VARCHAR2,
216 p_split1_loss_amount IN NUMBER,
217 p_split1_reval_reserve IN NUMBER,
218 p_split1_loss_acct IN VARCHAR2,
219
220 p_split2_impair_class IN VARCHAR2,
221 p_split2_loss_amount IN NUMBER,
222 p_split2_reval_reserve IN NUMBER,
223 p_split2_loss_acct IN VARCHAR2,
224
225 p_split3_impair_class IN VARCHAR2,
226 p_split3_loss_amount IN NUMBER,
227 p_split3_reval_reserve IN NUMBER,
228 p_split3_loss_acct IN VARCHAR2,
229
230 p_created_by IN NUMBER,
231 p_creation_date IN DATE
232
233 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN IS
234 l_calling_fn varchar2(60) := 'create_sorp_imp_acct';
235 l_mode varchar2(20) := 'RUNNING POST';
236 BEGIN
237
238 IF (p_log_level_rec.statement_level) THEN
239 fa_debug_pkg.add(l_calling_fn,'px_adj.book_type_code', px_adj.book_type_code, p_log_level_rec => p_log_level_rec);
240 fa_debug_pkg.add(l_calling_fn,'p_impairment_amount', p_impairment_amount, p_log_level_rec => p_log_level_rec);
241 fa_debug_pkg.add(l_calling_fn,'p_reval_reserve_adj', p_reval_reserve_adj, p_log_level_rec => p_log_level_rec);
242
243 fa_debug_pkg.add(l_calling_fn,'p_impair_class', p_impair_class, p_log_level_rec => p_log_level_rec);
244 fa_debug_pkg.add(l_calling_fn,'p_impair_loss_acct', p_impair_loss_acct, p_log_level_rec => p_log_level_rec);
245 fa_debug_pkg.add(l_calling_fn,'p_split_impair_flag', p_split_impair_flag, p_log_level_rec => p_log_level_rec);
246
247 fa_debug_pkg.add(l_calling_fn,'p_split1_impair_class', p_split1_impair_class, p_log_level_rec => p_log_level_rec);
248 fa_debug_pkg.add(l_calling_fn,'p_split1_loss_amount', p_split1_loss_amount, p_log_level_rec => p_log_level_rec);
249 fa_debug_pkg.add(l_calling_fn,'p_split1_reval_reserve', p_split1_reval_reserve, p_log_level_rec => p_log_level_rec);
250 fa_debug_pkg.add(l_calling_fn,'p_split1_loss_acct', p_split1_loss_acct, p_log_level_rec => p_log_level_rec);
251
252 fa_debug_pkg.add(l_calling_fn,'p_split2_impair_class', p_split2_impair_class, p_log_level_rec => p_log_level_rec);
253 fa_debug_pkg.add(l_calling_fn,'p_split2_loss_amount', p_split2_loss_amount, p_log_level_rec => p_log_level_rec);
254 fa_debug_pkg.add(l_calling_fn,'p_split2_reval_reserve', p_split2_reval_reserve, p_log_level_rec => p_log_level_rec);
255 fa_debug_pkg.add(l_calling_fn,'p_split2_loss_acct', p_split2_loss_acct, p_log_level_rec => p_log_level_rec);
256
257 fa_debug_pkg.add(l_calling_fn,'p_split3_impair_class', p_split3_impair_class, p_log_level_rec => p_log_level_rec);
258 fa_debug_pkg.add(l_calling_fn,'p_split3_loss_amount', p_split3_loss_amount, p_log_level_rec => p_log_level_rec);
259 fa_debug_pkg.add(l_calling_fn,'p_split3_reval_reserve', p_split3_reval_reserve, p_log_level_rec => p_log_level_rec);
260 fa_debug_pkg.add(l_calling_fn,'p_split3_loss_acct', p_split3_loss_acct, p_log_level_rec => p_log_level_rec);
261
262 fa_debug_pkg.add(l_calling_fn,'p_created_by', p_created_by, p_log_level_rec => p_log_level_rec);
263 fa_debug_pkg.add(l_calling_fn,'p_creation_date', p_creation_date, p_log_level_rec => p_log_level_rec);
264 END IF;
265
266 IF p_split_impair_flag = 'Y' THEN
267
268 IF (p_log_level_rec.statement_level) THEN
269 fa_debug_pkg.add(l_calling_fn,'calling create_acct_impair_class', 'split1', p_log_level_rec => p_log_level_rec);
270 END IF;
271
272 IF p_split1_impair_class IS NOT NULL THEN
273 IF NOT create_acct_impair_class(
274 p_impair_class => p_split1_impair_class
275 , p_impair_loss_acct => p_split1_loss_acct
276 , p_impairment_amount => p_split1_loss_amount
277 , p_reval_reserve_adj => p_split1_reval_reserve
278 , px_adj => px_adj
279 , p_created_by => p_created_by
280 , p_creation_date => p_creation_date
281 , p_log_level_rec => p_log_level_rec
282 ) THEN
283 fa_debug_pkg.add(l_calling_fn,'Error at split 1 create_acct_impair_class',
284 'for SORP', p_log_level_rec => p_log_level_rec);
285 RETURN FALSE;
286 END IF;
287 END IF;
288
289 IF (p_log_level_rec.statement_level) THEN
290 fa_debug_pkg.add(l_calling_fn,'calling create_acct_impair_class', 'split2', p_log_level_rec => p_log_level_rec);
291 END IF;
292
293 IF p_split2_impair_class IS NOT NULL THEN
294 IF NOT create_acct_impair_class(
295 p_impair_class => p_split2_impair_class
296 , p_impair_loss_acct => p_split2_loss_acct
297 , p_impairment_amount => p_split2_loss_amount
298 , p_reval_reserve_adj => p_split2_reval_reserve
299 , px_adj => px_adj
300 , p_created_by => p_created_by
301 , p_creation_date => p_creation_date
302 , p_log_level_rec => p_log_level_rec
303 ) THEN
304 fa_debug_pkg.add(l_calling_fn,'Error at split 2 create_acct_impair_class',
305 'for SORP', p_log_level_rec => p_log_level_rec);
306 RETURN FALSE;
307 END IF;
308 END IF;
309
310 IF (p_log_level_rec.statement_level) THEN
311 fa_debug_pkg.add(l_calling_fn,'calling create_acct_impair_class', 'split3', p_log_level_rec => p_log_level_rec);
312 END IF;
313
314 IF p_split3_impair_class IS NOT NULL THEN
315 IF NOT create_acct_impair_class(
316 p_impair_class => p_split3_impair_class
317 , p_impair_loss_acct => p_split3_loss_acct
318 , p_impairment_amount => p_split3_loss_amount
319 , p_reval_reserve_adj => p_split3_reval_reserve
320 , px_adj => px_adj
321 , p_created_by => p_created_by
322 , p_creation_date => p_creation_date
323 , p_log_level_rec => p_log_level_rec
324 ) THEN
325 fa_debug_pkg.add(l_calling_fn,'Error at split 3 create_acct_impair_class',
326 'for SORP', p_log_level_rec => p_log_level_rec);
327 RETURN FALSE;
328 END IF;
329 END IF;
330
331 If (p_log_level_rec.statement_level) THEN
332 fa_debug_pkg.add(l_calling_fn,'create_sorp_imp_acct completed for split', 'Success', p_log_level_rec => p_log_level_rec);
333 END IF;
334
335 RETURN TRUE;
336
337 ELSE -- Impairment is not split
338
339 IF (p_log_level_rec.statement_level) THEN
340 fa_debug_pkg.add(l_calling_fn,'calling', 'create_acct_impair_class', p_log_level_rec => p_log_level_rec);
341 END IF;
342
343 IF NOT create_acct_impair_class(
344 p_impair_class => p_impair_class
345 , p_impair_loss_acct => p_impair_loss_acct
346 , p_impairment_amount => p_impairment_amount
347 , p_reval_reserve_adj => p_reval_reserve_adj
348 , px_adj => px_adj
349 , p_created_by => p_created_by
350 , p_creation_date => p_creation_date
351 , p_log_level_rec => p_log_level_rec
352
353 ) THEN
354 fa_debug_pkg.add(l_calling_fn,'Error at non split create_acct_impair_class',
355 'for SORP', p_log_level_rec => p_log_level_rec);
356 RETURN FALSE;
357 END IF;
358
359 IF (p_log_level_rec.statement_level) THEN
360 fa_debug_pkg.add(l_calling_fn,'create_acct_impair_class completed', 'Success', p_log_level_rec => p_log_level_rec);
361 END IF;
362
363 IF (p_log_level_rec.statement_level) THEN
364 fa_debug_pkg.add(l_calling_fn,'create_sorp_imp_acct completed for non split', 'Success', p_log_level_rec => p_log_level_rec);
365 END IF;
366
367 RETURN TRUE;
368
369 END IF; -- IF p_split_impair_flag = 'Y' THEN
370
371 EXCEPTION
372 WHEN OTHERS THEN
373
374 IF (p_log_level_rec.statement_level) THEN
375 fa_debug_pkg.add(l_calling_fn,'exception at create_acct_impair_class', 'OTHERS', p_log_level_rec => p_log_level_rec);
376 fa_debug_pkg.add(l_calling_fn,'sqlerrm', substrb(sqlerrm, 1, 200));
377 END IF;
378
379 RETURN FALSE;
380 END;
381
382
383 /*#
384 * Thus function is invoked during the impairment preview stage. This performs
385 * all the necessary SORP events required during impairment preview stage
386 * This function is invoked from the package FA_IMPAIRMENT_PREV_PVT during
387 * the the impairment preview phase
388 * @param p_request_id The request id of the impairment preview process
389 * @param p_impairment_id The impairment id for which processing need to be done
390 * @param @p_mrc_sob_type_code Indicates if MRC is used
391 * @param @p_book_type_code The book for which impairment was generated
392 * @param @p_precision The precision of the current in order to perform rounding
393 * @rep:scope private
394 * @rep:lifecycle active
395 * @rep:displayname sorp_processing
396 * @rep:compatibility S
397 */
398 FUNCTION sorp_processing( p_request_id IN NUMBER
399 , p_impairment_id IN NUMBER
400 , p_mrc_sob_type_code IN VARCHAR2
401 , p_set_of_books_id IN NUMBER
402 , p_book_type_code IN VARCHAR2
403 , p_precision IN NUMBER
404
405 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN IS
406
407 l_calling_fn varchar2(60) := 'imp sorp_processing';
408
409 CURSOR c_mc_impairment_details(c_impairment_id NUMBER
410 , c_book_type_code VARCHAR2) IS
411 select IMPAIRMENT_AMOUNT
412 , REVAL_RESERVE
413 , NVL(SPLIT_IMPAIR_FLAG, 'N')
414 , SPLIT1_PERCENT
415 , SPLIT2_PERCENT
416 , SPLIT3_PERCENT
417 , ASSET_ID
418 , SPLIT1_IMPAIR_CLASS
419 , SPLIT2_IMPAIR_CLASS
420 , SPLIT3_IMPAIR_CLASS
421 from fa_mc_itf_impairments
422 where impairment_id = c_impairment_id
423 and book_type_code = c_book_type_code
424 and nvl(goodwill_asset_flag, 'N') <> 'Y'
425 and set_of_books_id = p_set_of_books_id;
426
427 CURSOR c_impairment_details(c_impairment_id NUMBER
428 , c_book_type_code VARCHAR2) IS
429 select IMPAIRMENT_AMOUNT
430 , REVAL_RESERVE
431 , NVL(SPLIT_IMPAIR_FLAG, 'N')
432 , SPLIT1_PERCENT
433 , SPLIT2_PERCENT
434 , SPLIT3_PERCENT
435 , ASSET_ID
436 , SPLIT1_IMPAIR_CLASS
437 , SPLIT2_IMPAIR_CLASS
438 , SPLIT3_IMPAIR_CLASS
439 from fa_itf_impairments
440 where impairment_id = c_impairment_id
441 and book_type_code = c_book_type_code
442 and nvl(goodwill_asset_flag, 'N') <> 'Y';
443
444 -- Placeholder columns for c_impairment_details
445 t_impair_amount tab_num_type;
446 t_reval_reserve tab_num_type;
447 t_split_impair_flag tab_char1_type;
448 t_split1_percent tab_num_type;
449 t_split2_percent tab_num_type;
450 t_split3_percent tab_num_type;
451 t_asset_id tab_num_type;
452 t_split1_impair_class tab_char3_type;
453 t_split2_impair_class tab_char3_type;
454 t_split3_impair_class tab_char3_type;
455
456 -- Columns to hold the calculated split amount
457 l_split1_loss_amount NUMBER;
458 l_split2_loss_amount NUMBER;
459 l_split3_loss_amount NUMBER;
460
461 -- Variable to hold the number of splits for a given impairment
462 l_number_of_splits NUMBER;
463
464 -- Record to store the split
465 -- This is used for sorting and determining the order in which the splits
466 -- have to be processed
467 TYPE split_process_order_rec IS RECORD (split_class number
468 , split_class_code varchar2(3)
469 , split_loss_amount number
470 , split_number number
471 , reval_reserve number
472 );
473 TYPE split_process_order_tab IS TABLE OF split_process_order_rec;
474 t_split_process_order split_process_order_tab;
475 l_temp_split_process_order split_process_order_rec;
476
477 BEGIN
478
479
480 IF (p_log_level_rec.statement_level) THEN
481 fa_debug_pkg.add(l_calling_fn,'Start ', 'sorp_processing', p_log_level_rec => p_log_level_rec);
482 fa_debug_pkg.add(l_calling_fn,'p_request_id', p_request_id, p_log_level_rec => p_log_level_rec);
483 fa_debug_pkg.add(l_calling_fn,'p_impairment_id',p_impairment_id, p_log_level_rec => p_log_level_rec);
484 fa_debug_pkg.add(l_calling_fn,'p_mrc_sob_type_code ', p_mrc_sob_type_code, p_log_level_rec => p_log_level_rec);
485 fa_debug_pkg.add(l_calling_fn,'p_precision ', p_precision, p_log_level_rec => p_log_level_rec);
486 END IF;
487
488 -- This is used for sorting and determining the order in which the splits
489 -- have to be processed
490 -- Since there are a maximum of three splits, the size of this structure
491 -- can be set to 3 at the initialization time
492 t_split_process_order := split_process_order_tab();
493 t_split_process_order.extend(3);
494
495 IF (p_mrc_sob_type_code = 'R') THEN
496 OPEN c_mc_impairment_details(p_impairment_id, p_book_type_code);
497 FETCH c_mc_impairment_details BULK COLLECT INTO t_impair_amount
498 , t_reval_reserve
499 , t_split_impair_flag
500 , t_split1_percent
501 , t_split2_percent
502 , t_split3_percent
503 , t_asset_id
504 , t_split1_impair_class
505 , t_split2_impair_class
506 , t_split3_impair_class
507 ;
508
509 IF t_asset_id.count = 0 THEN
510 fa_debug_pkg.add(l_calling_fn,'p_impairment_id', p_impairment_id, p_log_level_rec => p_log_level_rec);
511 fa_debug_pkg.add(l_calling_fn,'p_book_type_code', p_book_type_code, p_log_level_rec => p_log_level_rec);
512 fa_debug_pkg.add(l_calling_fn,'Unexpected error at c_mc_impairment_details', 'FA_ITF_IMPAIRMENTS', p_log_level_rec => p_log_level_rec);
513 CLOSE c_mc_impairment_details;
514 RETURN FALSE;
515 END IF;
516
517 CLOSE c_mc_impairment_details;
518 ELSE
519 OPEN c_impairment_details(p_impairment_id, p_book_type_code);
520 FETCH c_impairment_details BULK COLLECT INTO t_impair_amount
521 , t_reval_reserve
522 , t_split_impair_flag
523 , t_split1_percent
524 , t_split2_percent
525 , t_split3_percent
526 , t_asset_id
527 , t_split1_impair_class
528 , t_split2_impair_class
529 , t_split3_impair_class
530 ;
531
532 IF t_asset_id.count = 0 THEN
533 fa_debug_pkg.add(l_calling_fn,'p_impairment_id', p_impairment_id, p_log_level_rec => p_log_level_rec);
534 fa_debug_pkg.add(l_calling_fn,'p_book_type_code', p_book_type_code, p_log_level_rec => p_log_level_rec);
535 fa_debug_pkg.add(l_calling_fn,'Unexpected error at c_impairment_details', 'FA_ITF_IMPAIRMENTS', p_log_level_rec => p_log_level_rec);
536 CLOSE c_impairment_details;
537 RETURN FALSE;
538 END IF;
539
540 CLOSE c_impairment_details;
541 END IF;
542
543 IF (p_log_level_rec.statement_level) THEN
544 fa_debug_pkg.add(l_calling_fn,'Number of records fetched', t_asset_id.count, p_log_level_rec => p_log_level_rec);
545 END IF;
546
547 FOR i IN t_asset_id.FIRST..t_asset_id.LAST LOOP
548
549 IF (p_log_level_rec.statement_level) THEN
550 fa_debug_pkg.add(l_calling_fn,'i', i, p_log_level_rec => p_log_level_rec);
551 fa_debug_pkg.add(l_calling_fn,'t_impair_amount(i)', t_impair_amount(i));
552 fa_debug_pkg.add(l_calling_fn,'t_reval_reserve(i)', t_reval_reserve(i));
553 fa_debug_pkg.add(l_calling_fn,'t_split_impair_flag(i)', t_split_impair_flag(i));
554 fa_debug_pkg.add(l_calling_fn,'t_split1_percent(i)', t_split1_percent(i));
555 fa_debug_pkg.add(l_calling_fn,'t_split2_percent(i)', t_split2_percent(i));
556 fa_debug_pkg.add(l_calling_fn,'t_split3_percent(i)', t_split3_percent(i));
557 fa_debug_pkg.add(l_calling_fn,'t_asset_id(i)', t_asset_id(i));
558 fa_debug_pkg.add(l_calling_fn,'t_split1_impair_class(i)', t_split1_impair_class(i));
559 fa_debug_pkg.add(l_calling_fn,'t_split2_impair_class(i)', t_split2_impair_class(i));
560 fa_debug_pkg.add(l_calling_fn,'t_split3_impair_class(i)', t_split3_impair_class(i));
561 END IF;
562
563 IF t_split_impair_flag(i) = 'Y' THEN -- If the impairment is split
564
565 --******************************************************************
566 --Logic A
567 --Logic to calculate the loss amounts for each split. Users define
568 --the split percentage in the WebADI. This logic is used to calculate
569 --the impairment loss amount for each split. There can be a minimum of
570 --two splits and a maximum of three splits
571 --******************************************************************
572
573 IF t_split1_impair_class(i) IS NOT NULL AND -- If two splits are defined
574 t_split2_impair_class(i) IS NOT NULL AND
575 t_split3_impair_class(i) IS NULL THEN
576 l_number_of_splits := 2;
577 l_split1_loss_amount := round(((t_impair_amount(i) * t_split1_percent(i))/100),p_precision);
578 l_split2_loss_amount := t_impair_amount(i) - l_split1_loss_amount;
579 ELSE
580 IF t_split1_impair_class(i) is NOT NULL AND -- If three splits are defined
581 t_split2_impair_class(i) is NOT NULL AND
582 t_split3_impair_class(i) is NOT NULL THEN
583 l_number_of_splits := 3;
584 l_split1_loss_amount := round(((t_impair_amount(i) * t_split1_percent(i))/100),p_precision);
585 l_split2_loss_amount := round(((t_impair_amount(i) * t_split2_percent(i))/100),p_precision);
586 l_split3_loss_amount := t_impair_amount(i) - (l_split1_loss_amount + l_split2_loss_amount);
587 END IF;
588 END IF;
589
590 IF (p_log_level_rec.statement_level) THEN
591 fa_debug_pkg.add(l_calling_fn,'l_number_of_splits', l_number_of_splits, p_log_level_rec => p_log_level_rec);
592 fa_debug_pkg.add(l_calling_fn,'l_split1_loss_amount', l_split1_loss_amount, p_log_level_rec => p_log_level_rec);
593 fa_debug_pkg.add(l_calling_fn,'l_split2_loss_amount', l_split2_loss_amount, p_log_level_rec => p_log_level_rec);
594 fa_debug_pkg.add(l_calling_fn,'l_split3_loss_amount', l_split3_loss_amount, p_log_level_rec => p_log_level_rec);
595 fa_debug_pkg.add(l_calling_fn,'t_reval_reserve(i)',t_reval_reserve(i));
596 fa_debug_pkg.add(l_calling_fn,'Calculation of split amounts', 'Completed', p_log_level_rec => p_log_level_rec);
597 END IF;
598
599 --------------------------------------------------------------------
600 -- End of Logic A
601 -- End of Logic to calculate the loss amounts for each split
602 --------------------------------------------------------------------
603
604
605
606
607 --******************************************************************
608 -- Logic B
609 -- Logic to select the order of split processing
610 -- As per the SORP Function Design Document, splits must be processed
611 -- in the following hierarchy
612 -- 1. Classification Type- First "CPP", followed by "Others", then "CEB"
613 -- 2. Amounts (Percent)- Largest first
614 -- 3. Split Number - Smallest first
615 --******************************************************************
616
617 -- Populate the record array on which sorting will be performed
618 -- Split 1
619 IF t_split1_impair_class(i) = 'CPP' THEN
620 t_split_process_order(1).split_class := 1;
621 END IF;
622 IF t_split1_impair_class(i) = 'OTH' THEN
623 t_split_process_order(1).split_class := 2;
624 END IF;
625 IF t_split1_impair_class(i) = 'CEB' THEN
626 t_split_process_order(1).split_class := 3;
627 END IF;
628 t_split_process_order(1).split_class_code := t_split1_impair_class(i);
629 t_split_process_order(1).split_loss_amount := l_split1_loss_amount;
630 t_split_process_order(1).split_number := 1;
631
632 -- Split 2
633 IF t_split2_impair_class(i) = 'CPP' THEN
634 t_split_process_order(2).split_class := 1;
635 END IF;
636 IF t_split2_impair_class(i) = 'OTH' THEN
637 t_split_process_order(2).split_class := 2;
638 END IF;
639 IF t_split2_impair_class(i) = 'CEB' THEN
640 t_split_process_order(2).split_class := 3;
641 END IF;
642 t_split_process_order(2).split_class_code := t_split2_impair_class(i);
643 t_split_process_order(2).split_loss_amount := l_split2_loss_amount;
644 t_split_process_order(2).split_number := 2;
645
646 -- Split 3
647 IF t_split3_impair_class(i) = 'CPP' THEN
648 t_split_process_order(3).split_class := 1;
649 END IF;
650 IF t_split3_impair_class(i) = 'OTH' THEN
651 t_split_process_order(3).split_class := 2;
652 END IF;
653 IF t_split3_impair_class(i) = 'CEB' THEN
654 t_split_process_order(3).split_class := 3;
655 END IF;
656 t_split_process_order(3).split_class_code := t_split3_impair_class(i);
657 t_split_process_order(3).split_loss_amount := l_split3_loss_amount;
658 t_split_process_order(3).split_number := 3;
659
660 IF (p_log_level_rec.statement_level) THEN
661 fa_debug_pkg.add(l_calling_fn,'t_split_process_order', 'Populated', p_log_level_rec => p_log_level_rec);
662 END IF;
663
664
665 -- The actual sorting logic to determine the split processing order
666 -- The sorting is done as follows:
667 -- 1. Classification Type- First "CPP", followed by "Others", then "CEB"
668 -- 2. Amounts (Percent)- Largest first
669 -- 3. Split Number - Smallest first
670 FOR j IN 1..l_number_of_splits LOOP
671 FOR k IN j+1..l_number_of_splits LOOP
672 IF t_split_process_order(j).split_class > t_split_process_order(k).split_class THEN
673 l_temp_split_process_order := t_split_process_order(j);
674 t_split_process_order(j) := t_split_process_order(k);
675 t_split_process_order(k) := l_temp_split_process_order;
676 ELSE
677 IF t_split_process_order(j).split_class = t_split_process_order(k).split_class
678 AND t_split_process_order(j).split_loss_amount < t_split_process_order(k).split_loss_amount THEN
679 l_temp_split_process_order := t_split_process_order(j);
680 t_split_process_order(j) := t_split_process_order(k);
681 t_split_process_order(k) := l_temp_split_process_order;
682 ELSE
683 IF t_split_process_order(j).split_class = t_split_process_order(k).split_class
684 AND t_split_process_order(j).split_loss_amount < t_split_process_order(k).split_loss_amount
685 AND t_split_process_order(j).split_number > t_split_process_order(k).split_number THEN
686 l_temp_split_process_order := t_split_process_order(j);
687 t_split_process_order(j) := t_split_process_order(k);
688 t_split_process_order(k) := l_temp_split_process_order;
689 END IF; --t_split_process_order(j).split_number > t_split_process_order(k).split_number
690 END IF; -- End if _split_process_order(j).split_loss_amount < t_split_process_order(k).split_loss_amount
691 END IF; -- End if t_split_process_order(j).split_class > t_split_process_order(k).split_class
692 END LOOP; -- End Loop k
693 END LOOP; -- End Loop j
694
695
696 IF (p_log_level_rec.statement_level) THEN
697 fa_debug_pkg.add(l_calling_fn,'Calculation of split processing order', 'Completed', p_log_level_rec => p_log_level_rec);
698 END IF;
699
700 --------------------------------------------------------------------
701 -- End of Logic B
702 -- End to select the order of split processing
703 --------------------------------------------------------------------
704
705
706
707 --******************************************************************
708 -- Logic C
709 -- Determine the revaluation impact and update the impairment
710 -- records
711 --******************************************************************
712
713 IF (p_log_level_rec.statement_level) THEN
714 fa_debug_pkg.add(l_calling_fn,'t_reval_reserve(i)', t_reval_reserve(i));
715 END IF;
716
717
718 IF t_reval_reserve(i) = 0 THEN
719 -- If the revaluation reserve is zero then calculation of the
720 -- revaluation reserve impact is not required
721 -- Update the interface table with the split impairment details
722 IF (p_mrc_sob_type_code = 'R') THEN
723 UPDATE FA_MC_ITF_IMPAIRMENTS ITF
724 SET SPLIT1_LOSS_AMOUNT = l_split1_loss_amount
725 , SPLIT2_LOSS_AMOUNT = l_split2_loss_amount
726 , SPLIT3_LOSS_AMOUNT = l_split3_loss_amount
727 , CAPITAL_ADJUSTMENT = NVL(CAPITAL_ADJUSTMENT,0) + NVL(IMPAIRMENT_AMOUNT,0)
728 , GENERAL_FUND = NVL(GENERAL_FUND,0) + NVL(IMPAIRMENT_AMOUNT,0)
729 , SPLIT1_PROCESS_ORDER = 0
730 , SPLIT2_PROCESS_ORDER = 0
731 , SPLIT3_PROCESS_ORDER = 0
732 , SPLIT1_REVAL_RESERVE = 0
733 , SPLIT2_REVAL_RESERVE = 0
734 , SPLIT3_REVAL_RESERVE = 0
735 WHERE IMPAIRMENT_ID = p_impairment_id
736 AND book_type_code = p_book_type_code
737 AND asset_id = t_asset_id(i)
738 AND nvl(GOODWILL_ASSET_FLAG, 'N') <> 'Y'
739 and set_of_books_id = p_set_of_books_id;
740 ELSE
741 UPDATE FA_ITF_IMPAIRMENTS ITF
742 SET SPLIT1_LOSS_AMOUNT = l_split1_loss_amount
743 , SPLIT2_LOSS_AMOUNT = l_split2_loss_amount
744 , SPLIT3_LOSS_AMOUNT = l_split3_loss_amount
745 , CAPITAL_ADJUSTMENT = NVL(CAPITAL_ADJUSTMENT,0) + NVL(IMPAIRMENT_AMOUNT,0)
746 , GENERAL_FUND = NVL(GENERAL_FUND,0) + NVL(IMPAIRMENT_AMOUNT,0)
747 , SPLIT1_PROCESS_ORDER = 0
748 , SPLIT2_PROCESS_ORDER = 0
749 , SPLIT3_PROCESS_ORDER = 0
750 , SPLIT1_REVAL_RESERVE = 0
751 , SPLIT2_REVAL_RESERVE = 0
752 , SPLIT3_REVAL_RESERVE = 0
753 WHERE IMPAIRMENT_ID = p_impairment_id
754 AND book_type_code = p_book_type_code
755 AND asset_id = t_asset_id(i)
756 AND nvl(GOODWILL_ASSET_FLAG, 'N') <> 'Y';
757 END IF;
758
759 IF (p_log_level_rec.statement_level) THEN
760 fa_debug_pkg.add(l_calling_fn,'t_reval_reserve(i)', t_reval_reserve(i));
761 fa_debug_pkg.add(l_calling_fn,'Updated FA_ITF_IMPAIRMENTS', 'UPDATED', p_log_level_rec => p_log_level_rec);
762 END IF;
763
764 ELSE -- t_reval_reserve(i) <> 0
765 IF t_reval_reserve(i) > 0 THEN
766 -- If revaluation reserve is non negative, calculate the
767 -- impact due to the revaluation reserve
768 -- This revaluation reserve must be consumed in the order
769 -- in determined in Logic B
770 FOR j IN 1..l_number_of_splits LOOP
771 IF (p_log_level_rec.statement_level) THEN
772 fa_debug_pkg.add(l_calling_fn,'j', j, p_log_level_rec => p_log_level_rec);
773 END IF;
774
775 IF (p_log_level_rec.statement_level) THEN
776 fa_debug_pkg.add(l_calling_fn,'split order j', j, p_log_level_rec => p_log_level_rec);
777 fa_debug_pkg.add(l_calling_fn,'before consumption t_split_process_order(j).reval_reserve'
778 , t_split_process_order(j).reval_reserve);
779 fa_debug_pkg.add(l_calling_fn,'before consumption t_reval_reserve(i)', t_reval_reserve(i));
780 END IF;
781
782 IF t_reval_reserve(i) > 0 THEN
783 IF t_split_process_order(j).split_loss_amount >= t_reval_reserve(i) THEN
784 t_split_process_order(j).reval_reserve := t_reval_reserve(i);
785 t_reval_reserve(i) := 0;
786 ELSE
787 t_split_process_order(j).reval_reserve := t_split_process_order(j).split_loss_amount;
788 t_reval_reserve(i) := t_reval_reserve(i) - t_split_process_order(j).split_loss_amount;
789 END IF;
790 ELSE
791 t_split_process_order(j).reval_reserve := 0;
792 END IF; -- t_reval_reserve(i) > 0
793
794 IF (p_log_level_rec.statement_level) THEN
795 fa_debug_pkg.add(l_calling_fn,'split order j', j, p_log_level_rec => p_log_level_rec);
796 fa_debug_pkg.add(l_calling_fn,'t_split_process_order(j).split_class_code', t_split_process_order(j).split_class_code);
797 fa_debug_pkg.add(l_calling_fn,'t_split_process_order(j).split_number', t_split_process_order(j).split_number);
798 fa_debug_pkg.add(l_calling_fn,'t_split_process_order(j).split_loss_amount', t_split_process_order(j).split_loss_amount);
799 fa_debug_pkg.add(l_calling_fn,'t_split_process_order(j).reval_reserve', t_split_process_order(j).reval_reserve);
800 fa_debug_pkg.add(l_calling_fn,'after consumption t_reval_reserve(i)', t_reval_reserve(i));
801 END IF;
802
803 -- Once revaluation reserve is consumed, update the
804 -- interface table with the values
805 IF t_split_process_order(j).split_number = 1 THEN
806 IF (p_mrc_sob_type_code = 'R') THEN
807 UPDATE FA_MC_ITF_IMPAIRMENTS ITF
808 SET SPLIT1_LOSS_AMOUNT = t_split_process_order(j).split_loss_amount
809 - t_split_process_order(j).reval_reserve
810 , SPLIT1_REVAL_RESERVE = t_split_process_order(j).reval_reserve
811 , SPLIT1_PROCESS_ORDER = j
812 WHERE IMPAIRMENT_ID = p_impairment_id
813 AND book_type_code = p_book_type_code
814 AND asset_id = t_asset_id(i)
815 AND nvl(GOODWILL_ASSET_FLAG, 'N') <> 'Y'
816 and set_of_books_id = p_set_of_books_id;
817 ELSE
818 UPDATE FA_ITF_IMPAIRMENTS ITF
819 SET SPLIT1_LOSS_AMOUNT = t_split_process_order(j).split_loss_amount
820 - t_split_process_order(j).reval_reserve
821 , SPLIT1_REVAL_RESERVE = t_split_process_order(j).reval_reserve
822 , SPLIT1_PROCESS_ORDER = j
823 WHERE IMPAIRMENT_ID = p_impairment_id
824 AND book_type_code = p_book_type_code
825 AND asset_id = t_asset_id(i)
826 AND nvl(GOODWILL_ASSET_FLAG, 'N') <> 'Y';
827 END IF;
828 END IF;
829 IF t_split_process_order(j).split_number = 2 THEN
830 IF (p_mrc_sob_type_code = 'R') THEN
831 UPDATE FA_MC_ITF_IMPAIRMENTS ITF
832 SET SPLIT2_LOSS_AMOUNT = t_split_process_order(j).split_loss_amount
833 - t_split_process_order(j).reval_reserve
834 , SPLIT2_REVAL_RESERVE = t_split_process_order(j).reval_reserve
835 , SPLIT2_PROCESS_ORDER = j
836 WHERE IMPAIRMENT_ID = p_impairment_id
837 AND book_type_code = p_book_type_code
838 AND asset_id = t_asset_id(i)
839 AND nvl(GOODWILL_ASSET_FLAG, 'N') <> 'Y'
840 and set_of_books_id = p_set_of_books_id;
841 ELSE
842 UPDATE FA_ITF_IMPAIRMENTS ITF
843 SET SPLIT2_LOSS_AMOUNT = t_split_process_order(j).split_loss_amount
844 - t_split_process_order(j).reval_reserve
845 , SPLIT2_REVAL_RESERVE = t_split_process_order(j).reval_reserve
846 , SPLIT2_PROCESS_ORDER = j
847 WHERE IMPAIRMENT_ID = p_impairment_id
848 AND book_type_code = p_book_type_code
849 AND asset_id = t_asset_id(i)
850 AND nvl(GOODWILL_ASSET_FLAG, 'N') <> 'Y';
851 END IF;
852 END IF;
853 IF t_split_process_order(j).split_number = 3 THEN
854 IF (p_mrc_sob_type_code = 'R') THEN
855 UPDATE FA_MC_ITF_IMPAIRMENTS ITF
856 SET SPLIT3_LOSS_AMOUNT = t_split_process_order(j).split_loss_amount
857 - t_split_process_order(j).reval_reserve
858 , SPLIT3_REVAL_RESERVE = t_split_process_order(j).reval_reserve
859 , SPLIT3_PROCESS_ORDER = j
860 WHERE IMPAIRMENT_ID = p_impairment_id
861 AND book_type_code = p_book_type_code
862 AND asset_id = t_asset_id(i)
863 AND nvl(GOODWILL_ASSET_FLAG, 'N') <> 'Y'
864 and set_of_books_id = p_set_of_books_id;
865 ELSE
866 UPDATE FA_ITF_IMPAIRMENTS ITF
867 SET SPLIT3_LOSS_AMOUNT = t_split_process_order(j).split_loss_amount
868 - t_split_process_order(j).reval_reserve
869 , SPLIT3_REVAL_RESERVE = t_split_process_order(j).reval_reserve
870 , SPLIT3_PROCESS_ORDER = j
871 WHERE IMPAIRMENT_ID = p_impairment_id
872 AND book_type_code = p_book_type_code
873 AND asset_id = t_asset_id(i)
874 AND nvl(GOODWILL_ASSET_FLAG, 'N') <> 'Y';
875 END IF;
876 END IF;
877 END LOOP;
878
879 -- Update the interface table with the remaining
880 -- revaluation reserve left after consumption
881 IF (p_mrc_sob_type_code = 'R') THEN
882 UPDATE FA_MC_ITF_IMPAIRMENTS ITF
883 SET YTD_IMPAIRMENT = NVL(YTD_IMPAIRMENT,0) - (IMPAIRMENT_AMOUNT-
884 (NVL(SPLIT1_LOSS_AMOUNT,0) + NVL(SPLIT2_LOSS_AMOUNT,0) + NVL(SPLIT3_LOSS_AMOUNT,0)))
885 , IMPAIRMENT_AMOUNT = NVL(SPLIT1_LOSS_AMOUNT,0) + NVL(SPLIT2_LOSS_AMOUNT,0) + NVL(SPLIT3_LOSS_AMOUNT,0)
886 , REVAL_RESERVE_ADJ_AMOUNT = REVAL_RESERVE - t_reval_reserve(i)
887 , REVAL_RESERVE = t_reval_reserve(i)
888 , GENERAL_FUND = NVL(GENERAL_FUND,0) + NVL(SPLIT1_LOSS_AMOUNT,0) + NVL(SPLIT2_LOSS_AMOUNT,0) + NVL(SPLIT3_LOSS_AMOUNT,0)
889 + NVL(DEPRN_ADJUSTMENT_AMOUNT,0)
890 , CAPITAL_ADJUSTMENT = NVL(CAPITAL_ADJUSTMENT,0) + NVL(SPLIT1_LOSS_AMOUNT,0)
891 + NVL(SPLIT2_LOSS_AMOUNT,0) + NVL(SPLIT3_LOSS_AMOUNT,0) + NVL(DEPRN_ADJUSTMENT_AMOUNT,0)
892 WHERE IMPAIRMENT_ID = p_impairment_id
893 AND book_type_code = p_book_type_code
894 AND asset_id = t_asset_id(i)
895 AND nvl(GOODWILL_ASSET_FLAG, 'N') <> 'Y'
896 and set_of_books_id = p_set_of_books_id;
897 ELSE
898 UPDATE FA_ITF_IMPAIRMENTS ITF
899 SET YTD_IMPAIRMENT = NVL(YTD_IMPAIRMENT,0) - (IMPAIRMENT_AMOUNT-
900 (NVL(SPLIT1_LOSS_AMOUNT,0) + NVL(SPLIT2_LOSS_AMOUNT,0) + NVL(SPLIT3_LOSS_AMOUNT,0)))
901 , IMPAIRMENT_AMOUNT = NVL(SPLIT1_LOSS_AMOUNT,0) + NVL(SPLIT2_LOSS_AMOUNT,0) + NVL(SPLIT3_LOSS_AMOUNT,0)
902 , REVAL_RESERVE_ADJ_AMOUNT = REVAL_RESERVE - t_reval_reserve(i)
903 , REVAL_RESERVE = t_reval_reserve(i)
904 , GENERAL_FUND = NVL(GENERAL_FUND,0) + NVL(SPLIT1_LOSS_AMOUNT,0) + NVL(SPLIT2_LOSS_AMOUNT,0) + NVL(SPLIT3_LOSS_AMOUNT,0)
905 + NVL(DEPRN_ADJUSTMENT_AMOUNT,0)
906 , CAPITAL_ADJUSTMENT = NVL(CAPITAL_ADJUSTMENT,0) + NVL(SPLIT1_LOSS_AMOUNT,0)
907 + NVL(SPLIT2_LOSS_AMOUNT,0) + NVL(SPLIT3_LOSS_AMOUNT,0)
908 + NVL(DEPRN_ADJUSTMENT_AMOUNT,0)
909 WHERE IMPAIRMENT_ID = p_impairment_id
910 AND book_type_code = p_book_type_code
911 AND asset_id = t_asset_id(i)
912 AND nvl(GOODWILL_ASSET_FLAG, 'N') <> 'Y';
913 END IF;
914
915 ELSE -- if reval reserve is negative
916 -- Revaluation Reserve cannot be negative for a SORP book
917 fa_debug_pkg.add(l_calling_fn,'Revaluation reserve', t_reval_reserve(i));
918 fa_debug_pkg.add(l_calling_fn,'Revaluation reserve is negative', 'True', p_log_level_rec => p_log_level_rec);
919 fa_debug_pkg.add(l_calling_fn,'Revaluation reserve cannot be negative for a SORP Book', 'Unknown error', p_log_level_rec => p_log_level_rec);
920 RETURN FALSE;
921 END IF; --t_reval_reserve(i) > 0
922 END IF; -- t_reval_reserve(i) = 0
923
924 -- Execution at this stage indicates successful processing
925 RETURN TRUE;
926
927 ELSE -- Impairment is not split
928
929 IF t_reval_reserve(i) <> 0 THEN
930 IF (p_mrc_sob_type_code = 'R') THEN
931 UPDATE FA_MC_ITF_IMPAIRMENTS ITF
932 SET
933 REVAL_RESERVE = NVL(REVAL_RESERVE,0) - least(REVAL_RESERVE, IMPAIRMENT_AMOUNT)
934 , REVAL_RESERVE_ADJ_AMOUNT = least(REVAL_RESERVE, IMPAIRMENT_AMOUNT)
935 , CAPITAL_ADJUSTMENT = NVL(CAPITAL_ADJUSTMENT,0) + NVL(IMPAIRMENT_AMOUNT,0) - least(REVAL_RESERVE, IMPAIRMENT_AMOUNT)
936 + NVL(DEPRN_ADJUSTMENT_AMOUNT,0)
937 , GENERAL_FUND = NVL(GENERAL_FUND,0) + NVL(IMPAIRMENT_AMOUNT,0)
938 - least(REVAL_RESERVE, IMPAIRMENT_AMOUNT)
939 + NVL(DEPRN_ADJUSTMENT_AMOUNT,0)
940 , IMPAIRMENT_AMOUNT = NVL(IMPAIRMENT_AMOUNT,0)
941 - least(REVAL_RESERVE, IMPAIRMENT_AMOUNT)
942 , YTD_IMPAIRMENT = NVL(YTD_IMPAIRMENT,0)
943 - least(REVAL_RESERVE, IMPAIRMENT_AMOUNT)
944 , SPLIT1_PROCESS_ORDER = -1
945 , SPLIT2_PROCESS_ORDER = -1
946 , SPLIT3_PROCESS_ORDER = -1
947 WHERE IMPAIRMENT_ID = p_impairment_id
948 AND book_type_code = p_book_type_code
949 AND asset_id = t_asset_id(i)
950 AND nvl(REVAL_RESERVE, 0) <> 0
951 AND nvl(GOODWILL_ASSET_FLAG, 'N') <> 'Y'
952 and set_of_books_id = p_set_of_books_id;
953 ELSE
954 UPDATE FA_ITF_IMPAIRMENTS ITF
955 SET
956 REVAL_RESERVE = NVL(REVAL_RESERVE,0) - least(REVAL_RESERVE, IMPAIRMENT_AMOUNT)
957 , REVAL_RESERVE_ADJ_AMOUNT = least(REVAL_RESERVE, IMPAIRMENT_AMOUNT)
958 , CAPITAL_ADJUSTMENT = NVL(CAPITAL_ADJUSTMENT,0) + NVL(IMPAIRMENT_AMOUNT,0) - least(REVAL_RESERVE, IMPAIRMENT_AMOUNT)
959 + NVL(DEPRN_ADJUSTMENT_AMOUNT,0)
960 , GENERAL_FUND = NVL(GENERAL_FUND,0) + NVL(IMPAIRMENT_AMOUNT,0)
961 - least(REVAL_RESERVE, IMPAIRMENT_AMOUNT)
962 + NVL(DEPRN_ADJUSTMENT_AMOUNT,0)
963 , IMPAIRMENT_AMOUNT = NVL(IMPAIRMENT_AMOUNT,0)
964 - least(REVAL_RESERVE, IMPAIRMENT_AMOUNT)
965 , YTD_IMPAIRMENT = NVL(YTD_IMPAIRMENT,0)
966 - least(REVAL_RESERVE, IMPAIRMENT_AMOUNT)
967 , SPLIT1_PROCESS_ORDER = -1
968 , SPLIT2_PROCESS_ORDER = -1
969 , SPLIT3_PROCESS_ORDER = -1
970 WHERE IMPAIRMENT_ID = p_impairment_id
971 AND book_type_code = p_book_type_code
972 AND asset_id = t_asset_id(i)
973 AND nvl(REVAL_RESERVE, 0) <> 0
974 AND nvl(GOODWILL_ASSET_FLAG, 'N') <> 'Y';
975 END IF;
976 ELSE -- If t_reval_reserve(i) = 0
977 IF (p_mrc_sob_type_code = 'R') THEN
978 UPDATE FA_MC_ITF_IMPAIRMENTS ITF
979 SET CAPITAL_ADJUSTMENT = NVL(CAPITAL_ADJUSTMENT,0) + NVL(IMPAIRMENT_AMOUNT,0)
980 + NVL(DEPRN_ADJUSTMENT_AMOUNT,0)
981 , GENERAL_FUND = NVL(GENERAL_FUND,0) + NVL(IMPAIRMENT_AMOUNT,0)
982 + NVL(DEPRN_ADJUSTMENT_AMOUNT,0)
983 , REVAL_RESERVE_ADJ_AMOUNT = 0
984 , SPLIT1_PROCESS_ORDER = -1
985 , SPLIT2_PROCESS_ORDER = -1
986 , SPLIT3_PROCESS_ORDER = -1
987 WHERE IMPAIRMENT_ID = p_impairment_id
988 AND book_type_code = p_book_type_code
989 AND asset_id = t_asset_id(i)
990 AND nvl(REVAL_RESERVE,0) = 0
991 AND nvl(GOODWILL_ASSET_FLAG, 'N') <> 'Y'
992 and set_of_books_id = p_set_of_books_id;
993 ELSE
994 UPDATE FA_ITF_IMPAIRMENTS ITF
995 SET CAPITAL_ADJUSTMENT = NVL(CAPITAL_ADJUSTMENT,0) + NVL(IMPAIRMENT_AMOUNT,0)
996 + NVL(DEPRN_ADJUSTMENT_AMOUNT,0)
997 , GENERAL_FUND = NVL(GENERAL_FUND,0) + NVL(IMPAIRMENT_AMOUNT,0)
998 + NVL(DEPRN_ADJUSTMENT_AMOUNT,0)
999 , REVAL_RESERVE_ADJ_AMOUNT = 0
1000 , SPLIT1_PROCESS_ORDER = -1
1001 , SPLIT2_PROCESS_ORDER = -1
1002 , SPLIT3_PROCESS_ORDER = -1
1003 WHERE IMPAIRMENT_ID = p_impairment_id
1004 AND book_type_code = p_book_type_code
1005 AND asset_id = t_asset_id(i)
1006 AND nvl(REVAL_RESERVE,0) = 0
1007 AND nvl(GOODWILL_ASSET_FLAG, 'N') <> 'Y';
1008 END IF;
1009 END IF; -- If t_reval_reserve(i) = 0
1010
1011 RETURN TRUE;
1012 END IF; -- End if t_split_impair_flag
1013 END LOOP;
1014
1015 RETURN FALSE;
1016
1017 EXCEPTION
1018 WHEN OTHERS THEN
1019 fa_debug_pkg.add(l_calling_fn,'Unknown exception', 'Others', p_log_level_rec => p_log_level_rec);
1020 fa_debug_pkg.add(l_calling_fn,'sqlerrm', substrb(sqlerrm, 1, 200));
1021 RETURN FALSE;
1022 END;
1023
1024
1025
1026 END FA_SORP_IMPAIRMENT_PVT;