[Home] [Help]
PACKAGE BODY: APPS.IGI_IAC_REVAL_CRUD
Source
1 PACKAGE BODY IGI_IAC_REVAL_CRUD AS
2 -- $Header: igiiardb.pls 120.20.12000000.5 2007/11/19 15:37:38 pakumare ship $
3 l_rec igi_iac_revaluation_rates%rowtype; -- create this for quicker access via sql navigator
4
5 --===========================FND_LOG.START=====================================
6 g_state_level NUMBER;
7 g_proc_level NUMBER;
8 g_event_level NUMBER;
9 g_excep_level NUMBER;
10 g_error_level NUMBER;
11 g_unexp_level NUMBER;
12 g_path VARCHAR2(100);
13 --===========================FND_LOG.END=======================================
14
15 cursor c_exists (cp_period_counter in number
16 , cp_asset_id in number
17 , cp_book_type_code in varchar2
18 ) is
19 select cumulative_reval_factor, current_reval_factor
20 from igi_iac_asset_balances
21 where asset_id = cp_asset_id
22 and book_type_code = cp_book_type_code
23 and period_counter = cp_period_counter
24 ;
25 procedure do_commit is
26 begin
27 if IGI_IAC_REVAL_UTILITIES.debug then
28 rollback;
29 else
30 commit;
31 end if;
32 end;
33
34 function create_exceptions
35 ( fp_reval_exceptions in out NOCOPY IGI_IAC_TYPES.iac_reval_exception_line
36 , fp_revaluation_id in NUMBER
37 )
38 return boolean is
39
40 l_Category_id number;
41 l_login_id number ;
42 l_user_id number ;
43 l_fp_reval_exceptions IGI_IAC_TYPES.iac_reval_exception_line;
44 l_path varchar2(150) ;
45 begin
46 l_login_id := fnd_global.login_id;
47 l_user_id := fnd_global.user_id;
48 l_path := g_path||'create_exceptions';
49
50 -- for NOCOPY.
51 l_fp_reval_exceptions := fp_reval_exceptions;
52
53 delete from igi_iac_exceptions
54 where asset_id = fp_reval_exceptions.asset_id
55 and book_type_code = fp_reval_exceptions.book_type_code
56 and revaluation_id = fp_revaluation_id;
57
58 select asset_category_id
59 into l_category_id
60 from fa_additions
61 where asset_id = fp_reval_exceptions.asset_id;
62
63 insert into igi_iac_exceptions
64 (
65 revaluation_id ,
66 asset_id ,
67 category_id ,
68 book_type_code ,
69 exception_message ,
70 created_by ,
71 creation_date ,
72 last_update_login ,
73 last_update_date ,
74 last_updated_by )
75 values
76 (
77 fp_revaluation_id
78 , fp_reval_exceptions.asset_id
79 , l_category_id
80 , fp_reval_exceptions.book_type_code
81 , fp_reval_exceptions.reason
82 , l_user_id
83 , sysdate
84 , l_login_id
85 , sysdate
86 , l_user_id
87 ) ;
88
89 if sql%found then
90 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Inserted into exceptions');
91 else
92 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'No records to insert');
93 end if;
94
95 return true;
96
97 EXCEPTION
98 WHEN OTHERS THEN
99 fp_reval_exceptions := l_fp_reval_exceptions;
100 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
101 return FALSE;
102 end;
103
104 function create_txn_headers
105 ( fp_reval_params in out NOCOPY IGI_IAC_TYPES.iac_reval_params
106 , fp_second_set in boolean )
107 return boolean is
108
109 l_rowid rowid;
110 l_max_adjustment_id number;
111 l_adjustment_id igi_iac_transaction_headers.adjustment_id%TYPE;
112 l_reval_type_flag varchar2(1);
113 l_fp_reval_params IGI_IAC_TYPES.iac_reval_params;
114 l_path varchar2(150) ;
115 begin
116 l_max_adjustment_id := -1;
117 l_path := g_path||'create_txn_headers';
118
119 -- for NOCOPY.
120 l_fp_reval_params := fp_reval_params;
121
122 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'begin create_txn_headers');
123 /* create one transaction header id for each Reval engine pass */
124 /* this should obsolete the previous one... */
125
126 if fp_reval_params.reval_control.revaluation_mode not in ( 'P','L')
127 then
128 return true;
129 end if;
130
131 if fp_reval_params.reval_control.transaction_type_code in ( 'ADDITION','RECLASS') then
132 l_reval_type_flag := 'C';
133 else
134 l_reval_type_flag := fp_reval_params.reval_asset_rules.revaluation_type;
135 end if;
136
137 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+Getting the latest adjustment id');
138 begin
139 select adjustment_id
140 into l_max_adjustment_id
141 from igi_iac_transaction_headers
142 where adjustment_id_out is null
143 and book_type_code = fp_reval_params.reval_asset_params.book_type_code
144 and asset_id = fp_reval_params.reval_asset_params.asset_id
145 ;
146 exception when others then
147 l_max_adjustment_id := -1;
148 end;
149
150 IGI_IAC_TRANS_HEADERS_PKG.insert_row (
151 x_rowid => l_rowid ,
152 x_adjustment_id => l_adjustment_id,
153 x_transaction_header_id => NULL,
154 x_adjustment_id_out => null,
155 x_transaction_type_code => fp_reval_params.reval_control.transaction_type_code,
156 x_transaction_date_entered => sysdate,
157 x_mass_refrence_id => fp_reval_params.reval_asset_params.revaluation_id,
158 x_transaction_sub_type => fp_reval_params.reval_control.transaction_sub_type,
159 x_book_type_code => fp_reval_params.reval_asset_params.book_type_code,
160 x_asset_id => fp_reval_params.reval_asset_params.asset_id,
161 x_category_id => fp_reval_params.reval_asset_params.category_id,
162 x_adj_deprn_start_date => fp_reval_params.fa_asset_info.deprn_start_date,
163 x_revaluation_type_flag => l_reval_type_flag ,
164 x_adjustment_status => fp_reval_params.reval_control.adjustment_status,
165 x_period_counter => fp_reval_params.reval_asset_params.period_counter,
166 x_mode => 'R',
167 x_event_id => Null
168 );
169
170 IF l_max_adjustment_id <> -1 then
171 IGI_IAC_TRANS_HEADERS_PKG.update_row (
172 x_prev_adjustment_id => l_max_adjustment_id,
173 x_adjustment_id => l_adjustment_id,
174 x_mode => 'R'
175 );
176 END IF;
177
178 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+adjustment id '|| l_adjustment_id);
179
180 if fp_second_set then
181 fp_reval_params.reval_asset_params.second_set_adjustment_id := l_adjustment_id;
182 else
183 fp_reval_params.reval_asset_params.first_set_adjustment_id := l_adjustment_id;
184 end if;
185
186 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'end create_txn_headers');
187
188 return true;
189
190 exception when others then
191 fp_reval_params := l_fp_reval_params;
192 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
193 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'end create_txn_headers');
194 return false;
195 end;
196
197
198 procedure create_balance (pp_period_counter in number
199 , pp_reval_output_asset in IGI_IAC_TYPES.iac_reval_output_asset
200 ) is
201
202 l_exists boolean;
203 l_rowid varchar2(40);
204 l_path varchar2(150);
205 begin
206 l_exists := false;
207 l_rowid := null;
208 l_path := g_path||'create_balance';
209
210 for l_ex in c_exists (cp_period_counter => pp_period_counter
211 , cp_asset_id => pp_reval_output_asset.asset_id
212 , cp_book_type_code => pp_reval_output_asset.book_type_code
213 ) loop
214 l_exists := true;
215 end loop;
216 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+balance for period counter '|| pp_period_counter);
217 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+ ASSET BALANCES');
218 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+ x_asset_id =>'|| pp_reval_output_asset.asset_id);
219 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+ x_book_type_code => '|| pp_reval_output_asset.book_type_code);
220 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+ x_period_counter => '||pp_period_counter);
221 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+ x_net_book_value => '||pp_reval_output_asset.net_book_value);
222 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+ x_adjusted_cost => '||pp_reval_output_asset.adjusted_cost);
223 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+ x_operating_acct =>'|| pp_reval_output_asset.operating_acct);
224 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+ x_reval_reserve => '||pp_reval_output_asset.reval_reserve);
225 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+ x_deprn_amount => '||pp_reval_output_asset.deprn_amount);
226 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+ x_deprn_reserve => '||pp_reval_output_asset.deprn_reserve);
227 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+ x_backlog_deprn_reserve => '||pp_reval_output_asset.backlog_deprn_reserve);
228 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+ x_general_fund => '||pp_reval_output_asset.general_fund);
229 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+ x_last_reval_date => '||pp_reval_output_asset.last_reval_date);
230 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+ x_current_reval_factor => '||pp_reval_output_asset.current_reval_factor);
231 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+ x_cumulative_reval_factor => '||pp_reval_output_asset.cumulative_reval_factor);
232
233 if l_exists then
234 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+balance record already exists, so update');
235 IGI_IAC_ASSET_BALANCES_PKG.update_row (
236 x_asset_id => pp_reval_output_asset.asset_id,
237 x_book_type_code => pp_reval_output_asset.book_type_code,
238 x_period_counter => pp_period_counter,
239 x_net_book_value => pp_reval_output_asset.net_book_value,
240 x_adjusted_cost => pp_reval_output_asset.adjusted_cost,
241 x_operating_acct => pp_reval_output_asset.operating_acct,
242 x_reval_reserve => pp_reval_output_asset.reval_reserve,
243 x_deprn_amount => pp_reval_output_asset.deprn_amount,
244 x_deprn_reserve => pp_reval_output_asset.deprn_reserve,
245 x_backlog_deprn_reserve => pp_reval_output_asset.backlog_deprn_reserve,
246 x_general_fund => pp_reval_output_asset.general_fund,
247 x_last_reval_date => pp_reval_output_asset.last_reval_date,
248 x_current_reval_factor => pp_reval_output_asset.current_reval_factor,
249 x_cumulative_reval_factor => pp_reval_output_asset.cumulative_reval_factor,
250 x_mode => 'R'
251 );
252 else
253 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+balance record does not exist, so insert');
254 IGI_IAC_ASSET_BALANCES_PKG.insert_row (
255 x_rowid => l_rowid,
256 x_asset_id => pp_reval_output_asset.asset_id,
257 x_book_type_code => pp_reval_output_asset.book_type_code,
258 x_period_counter => pp_period_counter,
259 x_net_book_value => pp_reval_output_asset.net_book_value,
260 x_adjusted_cost => pp_reval_output_asset.adjusted_cost,
261 x_operating_acct => pp_reval_output_asset.operating_acct,
262 x_reval_reserve => pp_reval_output_asset.reval_reserve,
263 x_deprn_amount => pp_reval_output_asset.deprn_amount,
264 x_deprn_reserve => pp_reval_output_asset.deprn_reserve,
265 x_backlog_deprn_reserve => pp_reval_output_asset.backlog_deprn_reserve,
266 x_general_fund => pp_reval_output_asset.general_fund,
267 x_last_reval_date => pp_reval_output_asset.last_reval_date,
268 x_current_reval_factor => pp_reval_output_asset.current_reval_factor,
269 x_cumulative_reval_factor => pp_reval_output_asset.cumulative_reval_factor,
270 x_mode => 'R'
271 );
272 end if;
273 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+completed entry of balances');
274 end;
275
276 function copy_balances
277 ( fp_asset_id in number
278 , fp_book_type_code in varchar2
279 , fp_period_counter in number
280 , fp_target_period_counter in number
281 )
282 return boolean is
283 cursor c_bal is
284 select *
285 from igi_iac_asset_balances
286 where asset_id = fp_Asset_id
287 and book_type_code = fp_book_type_code
288 and period_counter = fp_period_counter
289 ;
290 l_path varchar2(150);
291 begin
292 l_path := g_path||'copy_balances';
293 for l_bal in c_bal loop
294 create_balance (pp_period_counter => fp_target_period_counter
295 ,pp_reval_output_asset => l_bal
296 );
297
298 end loop;
299 return true;
300 end;
301
302 function create_asset_balances
303 ( fp_reval_params in out NOCOPY IGI_IAC_TYPES.iac_reval_params
304 , fp_second_set in boolean )
305 return boolean is
306 l_rowid rowid;
307 l_processed boolean;
308
309 l_prev_cumm_rate number;
310 l_curr_cumm_rate number;
311 l_period_counter number;
312 l_fp_reval_params igi_iac_types.iac_reval_params;
313
314 l_path varchar2(150);
315 begin
316
317 l_processed := false;
318 l_prev_cumm_rate := 1;
319 l_curr_cumm_rate := 1;
320 l_period_counter := fp_Reval_params.reval_output_asset.period_counter;
321 l_path := g_path||'create_asset_balances';
322
323 -- for NOCOPY
324 l_fp_reval_params := fp_reval_params;
325
326 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'begin create_asset_balances');
327 if fp_reval_params.reval_control.revaluation_mode not in ('L','R') then
328 return true;
329 end if;
330
331 create_balance (pp_period_counter => l_period_counter
332 ,pp_reval_output_asset => fp_reval_params.reval_output_asset
333 );
334
335 create_balance (pp_period_counter => l_period_counter +1
336 ,pp_reval_output_asset => fp_reval_params.reval_output_asset
337 );
338
339 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'end create_asset_balances');
340
341 return true;
342
343 EXCEPTION
344 WHEN OTHERS THEN
345 fp_reval_params := l_fp_reval_params;
346 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
347 return FALSE;
348 end;
349
350
351 function get_prev_det_balances (fp_adjustment_id in number
352 , fp_distribution_id in number
353 , fp_asset_id in number
354 , fp_book_type_code in varchar2
355 , fp_period_counter in number
356 , fp_transaction_sub_type in varchar2
357 , fp_det_balances out NOCOPY igi_iac_det_balances%ROWTYPE
358 )
359 return boolean is
360
361 l_fp_det_balances igi_iac_det_balances%ROWTYPE;
362 l_success boolean ;
363 transaction_sub_type igi_iac_transaction_headers.TRANSACTION_SUB_TYPE%TYPE;
364 period_counter number;
365
366 cursor c_prev_bal (period_counter number) is
367 select bal.*
368 from igi_iac_det_balances bal
369 where bal.adjustment_id = fp_adjustment_id
370 and bal.distribution_id = fp_distribution_id
371 and bal.asset_id = fp_asset_id
372 and bal.period_counter <= period_counter
373 and bal.book_type_code = fp_book_type_code
374 ;
375
376 cursor c_reval ( p_book_type_code varchar2, p_period_counter number, p_asset_id number) is
377 SELECT 1
378 FROM dual
379 WHERE EXISTS(
380 SELECT 1
381 FROM igi_iac_transaction_headers th, igi_iac_det_balances db
382 WHERE th.transaction_sub_type = 'IMPLEMENTATION'
383 AND th.adjustment_id = db.adjustment_id
384 AND db.book_type_code = p_book_type_code
385 AND db.period_counter = p_period_counter
386 AND db.book_type_code = th.book_type_code
387 AND db.asset_id = th.asset_id
388 AND db.asset_id = p_asset_id)
389 ;
390
391 l_reval_exists c_reval%rowtype;
392
393 /*
394 -- Need a function to check whether the distribution exists
395 */
396
397 l_path varchar2(150);
398
399 function AlreadyExists ( p_distribution_id in number
400 , p_asset_id in number
401 , p_book_type_code in varchar2
402 )
403 return boolean is
404 cursor c_exists is
405 select distinct 'x'
406 from igi_iac_det_Balances
407 where asset_id = p_asset_id
408 and distribution_id = p_distribution_id
409 and book_type_code = p_book_type_code
410 ;
411 l_status boolean;
412
413 l_path varchar2(150);
414 begin
415 l_status := false;
416 l_path := g_path||'AlreadyExists';
417
418 for l_exists in c_exists loop
419 l_status := true;
420 end loop;
421 return l_status;
422 exception when others then
423 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
424 return false;
425 end;
426 begin
427 l_success := false;
428 l_path := g_path||'get_prev_det_balances';
429 -- for NOCOPY.
430 l_fp_det_balances := fp_det_balances;
431
432 if (nvl(fp_adjustment_id,-1) = -1 )
433 OR (NOT AlreadyExists
434 ( p_distribution_id => fp_distribution_id
435 , p_asset_id => fp_asset_id
436 , p_book_type_code => fp_book_type_code
437 )
438 )
439 then
440 fp_det_balances.asset_id := fp_asset_id;
441 fp_det_balances.book_type_code := fp_book_type_code;
442 fp_det_balances.period_counter := fp_period_counter;
443 fp_det_balances.adjustment_id := nvl( fp_adjustment_id, -1);
444 fp_det_balances.distribution_id := fp_distribution_id;
445 fp_det_balances.adjustment_cost := 0;
446 fp_det_balances.net_book_value := 0;
447 fp_det_balances.reval_reserve_cost := 0;
448 fp_det_balances.reval_reserve_backlog := 0;
449 fp_det_balances.reval_reserve_gen_fund := 0;
450 fp_det_balances.reval_reserve_net := 0;
451 fp_det_balances.operating_acct_cost := 0;
452 fp_det_balances.operating_acct_backlog := 0;
453 fp_det_balances.operating_acct_net := 0;
454 fp_det_balances.deprn_period := 0;
455 fp_det_balances.deprn_ytd := 0;
456 fp_det_balances.deprn_reserve := 0;
457 fp_det_balances.deprn_reserve_backlog := 0;
458
459 l_success := true;
460 else
461 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+adjustment_id .. ' ||fp_adjustment_id);
462 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+distribution_id .. ' ||fp_distribution_id);
463 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+period counter.... ' ||fp_period_counter);
464
465 period_counter := fp_period_counter;
466
467 select TRANSACTION_SUB_TYPE
468 into transaction_sub_type
469 from igi_iac_transaction_headers
470 where ADJUSTMENT_ID = fp_adjustment_id;
471
472 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Checking if the previous adjustment was Implementation');
473
474 IF transaction_sub_type = 'IMPLEMENTATION' THEN
475 period_counter := period_counter + 1;
476 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Incrementing Period Counter to allow revaluation after Implementation');
477 END IF;
478
479 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Checking if Transaction ttype is Professional');
480
481 IF fp_transaction_sub_type <> 'PROFESSIONAL' THEN
482 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Transaction type is not Professional');
483 OPEN c_reval (fp_book_type_code, period_counter, fp_asset_id);
484 FETCH c_reval INTO l_reval_exists;
485 IF c_reval%FOUND THEN
486 CLOSE c_reval;
487 RETURN l_success;
488 END IF;
489 CLOSE c_reval;
490 END IF;
491
492 for l_prev in c_prev_bal (period_counter) loop
493 fp_det_balances := l_prev;
494 fp_det_balances.period_counter := fp_period_counter;
495 l_success := true;
496 end loop;
497 end if;
498 return l_success;
499
500 exception when others then
501 fp_det_balances := l_fp_det_balances;
502 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
503 return false;
504 end;
505
506 procedure round_det_balances ( fp_det_balances in out NOCOPY IGI_IAC_TYPES.iac_det_balances )
507 is
508 l_book_type_code igi_iac_det_balances.book_type_code%TYPE;
509 l_fp_det_balances igi_iac_types.iac_det_balances;
510 l_path varchar2(150);
511
512 procedure Do_Rounding ( pp_amount in out NOCOPY number ) is
513 begin
514 if not igi_iac_common_utils.iac_round ( x_amount => pp_amount
515 , x_book => l_book_type_Code
516 )
517 then
518 pp_amount := round( pp_amount, 2);
519 end if;
520 exception when others then
521 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Rounding is wrong');
522 pp_amount := round( pp_amount, 2);
523 end;
524 begin
525 l_book_type_code := fp_det_balances.book_type_code ;
526 l_path := g_path||'round_det_balances';
527
528 -- for NOCOPY.
529 l_fp_det_balances := fp_det_balances;
530
531 Do_rounding (fp_det_balances.adjustment_cost );
532 Do_rounding (fp_det_balances.net_book_value );
533 Do_rounding (fp_det_balances.reval_reserve_cost );
534 Do_rounding (fp_det_balances.reval_reserve_backlog ) ;
535 Do_rounding (fp_det_balances.reval_reserve_gen_fund );
536 Do_rounding (fp_det_balances.reval_reserve_net ) ;
537 Do_rounding (fp_det_balances.operating_acct_cost ) ;
538 Do_rounding (fp_det_balances.operating_acct_backlog ) ;
539 Do_rounding (fp_det_balances.operating_acct_net ) ;
540 Do_rounding (fp_det_balances.operating_acct_ytd ) ;
541 Do_rounding (fp_det_balances.deprn_period );
542 Do_rounding (fp_det_balances.deprn_ytd ) ;
543 Do_rounding (fp_det_balances.deprn_reserve ) ;
544 Do_rounding (fp_det_balances.deprn_reserve_backlog ) ;
545 Do_rounding (fp_det_balances.general_fund_per ) ;
546 Do_rounding (fp_det_balances.general_fund_acc ) ;
547
548 EXCEPTION
549 WHEN OTHERS THEN
550 fp_det_balances := l_fp_det_balances;
551 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
552 Raise;
553 end;
554
555 procedure round_fa_figures ( fp_fa_hist in out NOCOPY IGI_IAC_TYPES.fa_hist_asset_info
556 , fp_det_balances in IGI_IAC_TYPES.iac_det_balances )
557 is
558 l_book_type_code igi_iac_det_balances.book_type_code%TYPE;
559 l_fp_fa_hist igi_iac_types.fa_hist_asset_info;
560 l_path varchar2(150);
561
562 procedure Do_Rounding ( pp_amount in out NOCOPY number ) is
563 l_path varchar2(150);
564 begin
565 l_path := g_path||'round_fa_figures.Do_Rounding';
566 if not igi_iac_common_utils.iac_round ( x_amount => pp_amount
567 , x_book => l_book_type_Code
568 )
569 then
570 pp_amount := round( pp_amount, 2);
571 end if;
572 exception when others then
573 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Rounding is wrong');
574 pp_amount := round( pp_amount, 2);
575 end;
576
577 begin
578 l_book_type_code := fp_det_balances.book_type_code ;
579 l_path := g_path||'round_fa_figures';
580
581 -- for NOCOPY
582 l_fp_fa_hist := fp_fa_hist;
583
584 Do_rounding (fp_fa_hist.deprn_reserve );
585 Do_rounding (fp_fa_hist.ytd_deprn );
586 Do_rounding (fp_fa_hist.deprn_amount );
587
588 EXCEPTION
589 WHEN OTHERS THEN
590 fp_fa_hist := l_fp_fa_hist;
591 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
592 Raise;
593 end;
594
595 procedure verify_det_balances ( fp_det_balances in out NOCOPY IGI_IAC_TYPES.iac_det_balances
596 , fp_prev_cum_factor in number
597 , fp_curr_cum_factor in number
598 ) is
599
600 l_fp_det_balances igi_iac_types.iac_det_balances;
601 l_path varchar2(150);
602 procedure initialize ( pp_amount in out NOCOPY number ) is
603 begin
604 pp_amount := 0;
605 end;
606
607 begin
608 l_path := g_path||'verify_det_balances';
609 -- for NOCOPY.
610 l_fp_det_balances := fp_det_balances;
611
612 if fp_prev_cum_factor >= 1 and fp_curr_cum_factor >= 1
613 then
614 fp_det_balances.operating_acct_cost := 0;
615
616 fp_det_balances.reval_reserve_net := nvl(fp_det_balances.reval_reserve_cost,0)
617 - nvl(fp_det_balances.reval_reserve_backlog,0)
618 - nvl(fp_det_balances.reval_reserve_gen_fund,0) ;
619 else
620 fp_det_balances.reval_reserve_net := 0;
621 fp_det_balances.reval_reserve_cost := 0;
622
623 fp_det_balances.operating_acct_net := nvl(fp_det_balances.operating_acct_cost,0)
624 - nvl(fp_det_balances.operating_acct_backlog,0) ;
625 end if;
626
627 fp_det_balances.net_book_value := nvl(fp_det_balances.adjustment_cost ,0)
628 - nvl(fp_det_balances.deprn_reserve,0)
629 - nvl(fp_det_balances.deprn_reserve_backlog,0);
630 EXCEPTION
631 WHEN OTHERS THEN
632 fp_det_balances := l_fp_det_balances;
633 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
634 Raise;
635 end;
636
637 procedure add_det_balances ( fp_det_balances in out NOCOPY IGI_IAC_TYPES.iac_det_balances
638 , fp_det_delta in IGI_IAC_TYPES.iac_det_balances
639 )
640 is
641 l_fp_det_balances IGI_IAC_TYPES.iac_det_balances;
642 l_path varchar2(150);
643 begin
644 l_path := g_path||'add_det_balances';
645 -- for NOCOPY
646 l_fp_det_balances := fp_det_balances;
647
648 fp_det_balances.cumulative_reval_factor := fp_det_delta.cumulative_reval_factor;
649 fp_det_balances.current_reval_factor := fp_det_delta.current_reval_factor;
650 fp_det_balances.adjustment_id := fp_Det_delta.adjustment_id;
651 fp_det_balances.active_flag := fp_det_delta.active_flag;
652
653 fp_det_balances.adjustment_cost := nvl(fp_det_balances.adjustment_cost,0)
654 + nvl(fp_det_delta.adjustment_cost,0) ;
655 fp_det_balances.reval_reserve_cost := nvl(fp_det_balances.reval_reserve_cost,0)
656 + nvl(fp_det_delta.reval_reserve_cost,0) ;
657 fp_det_balances.reval_reserve_backlog := nvl(fp_det_balances.reval_reserve_backlog,0)
658 + nvl(fp_det_delta.reval_reserve_backlog,0) ;
659 fp_det_balances.reval_reserve_gen_fund := nvl(fp_det_balances.reval_reserve_gen_fund,0)
660 + nvl(fp_det_delta.reval_reserve_gen_fund,0) ;
661 fp_det_balances.reval_reserve_net := nvl(fp_det_balances.reval_reserve_cost,0)
662 - nvl(fp_det_balances.reval_reserve_backlog,0)
663 - nvl(fp_det_balances.reval_reserve_gen_fund,0) ;
664 fp_det_balances.operating_acct_cost := nvl(fp_det_balances.operating_acct_cost,0)
665 + nvl(fp_det_delta.operating_acct_cost,0) ;
666 fp_det_balances.operating_acct_backlog := nvl(fp_det_balances.operating_acct_backlog,0)
667 + nvl(fp_det_delta.operating_acct_backlog,0) ;
668 fp_det_balances.operating_acct_net := fp_det_balances.operating_acct_cost
669 - fp_det_balances.operating_acct_backlog ;
670 fp_det_balances.operating_acct_ytd := nvl(fp_det_balances.operating_acct_ytd ,0)
671 + fp_det_balances.operating_acct_net;
672 if fp_det_balances.period_counter = fp_det_delta.period_counter then
673 fp_det_balances.general_fund_per := nvl(fp_det_balances.general_fund_per ,0)
674 + nvl(fp_det_delta.general_fund_per,0) ;
675 else
676 fp_det_balances.general_fund_per := nvl(fp_det_delta.general_fund_per,0) ;
677 end if;
678
679 fp_det_balances.deprn_period := nvl(fp_det_delta.deprn_period,0) ;
680 fp_det_balances.deprn_ytd := nvl(fp_det_balances.deprn_ytd,0);/* YTD Proration*/
681 fp_det_balances.deprn_reserve := nvl(fp_det_balances.deprn_reserve,0)
682 + nvl(fp_det_delta.deprn_reserve,0) ;
683 fp_det_balances.deprn_reserve_backlog := nvl(fp_det_balances.deprn_reserve_backlog ,0)
684 + nvl(fp_det_delta.deprn_reserve_backlog,0) ;
685
686 fp_det_balances.general_fund_acc := nvl(fp_det_balances.general_fund_acc ,0)
687 + nvl(fp_det_delta.general_fund_acc,0) ;
688 EXCEPTION WHEN OTHERS THEN
689 fp_det_balances := l_fp_det_balances;
690 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
691 Raise;
692 end;
693
694 procedure remaining_det_balances ( fp_det_balances in out NOCOPY IGI_IAC_TYPES.iac_det_balances
695 , fp_det_delta in IGI_IAC_TYPES.iac_det_balances
696 )
697 is
698 l_fp_det_balances IGI_IAC_TYPES.iac_det_balances;
699 l_path varchar2(150);
700 begin
701 l_path := g_path||'remaining_det_balances';
702 -- for NOCOPY.
703 l_fp_det_balances := fp_det_balances;
704
705 fp_det_balances.adjustment_cost := nvl(fp_det_balances.adjustment_cost,0)
706 - nvl(fp_det_delta.adjustment_cost,0) ;
707 fp_det_balances.reval_reserve_cost := nvl(fp_det_balances.reval_reserve_cost,0)
708 - nvl(fp_det_delta.reval_reserve_cost,0) ;
709 fp_det_balances.reval_reserve_backlog := nvl(fp_det_balances.reval_reserve_backlog,0)
710 - nvl(fp_det_delta.reval_reserve_backlog,0) ;
711 fp_det_balances.reval_reserve_gen_fund := nvl(fp_det_balances.reval_reserve_gen_fund,0)
712 - nvl(fp_det_delta.reval_reserve_gen_fund,0) ;
713 fp_det_balances.reval_reserve_net := nvl(fp_det_balances.reval_reserve_cost,0)
714 - nvl(fp_det_balances.reval_reserve_backlog,0)
715 - nvl(fp_det_balances.reval_reserve_gen_fund,0) ;
716 fp_det_balances.operating_acct_cost := nvl(fp_det_balances.operating_acct_cost,0)
717 - nvl(fp_det_delta.operating_acct_cost,0) ;
718 fp_det_balances.operating_acct_backlog := nvl(fp_det_balances.operating_acct_backlog,0)
719 - nvl(fp_det_delta.operating_acct_backlog,0) ;
720 fp_det_balances.operating_acct_net := fp_det_balances.operating_acct_cost
721 - fp_det_balances.operating_acct_backlog ;
722 fp_det_balances.operating_acct_ytd := nvl(fp_det_balances.operating_acct_ytd ,0)
723 - fp_det_delta.operating_acct_ytd;
724 fp_det_balances.deprn_period := nvl(fp_det_balances.deprn_period,0)
725 - nvl(fp_det_delta.deprn_period,0) ;
726 fp_det_balances.deprn_ytd := nvl(fp_det_balances.deprn_ytd ,0)
727 - nvl(fp_det_delta.deprn_ytd,0) ;
728 fp_det_balances.deprn_reserve := nvl(fp_det_balances.deprn_reserve ,0)
729 - nvl(fp_det_delta.deprn_reserve,0) ;
730 fp_det_balances.deprn_reserve_backlog := nvl(fp_det_balances.deprn_reserve_backlog ,0)
731 - nvl(fp_det_delta.deprn_reserve_backlog,0) ;
732 fp_det_balances.general_fund_per := nvl(fp_det_balances.general_fund_per ,0)
733 - nvl(fp_det_delta.general_fund_per,0) ;
734 fp_det_balances.general_fund_acc := nvl(fp_det_balances.general_fund_acc ,0)
735 - nvl(fp_det_delta.general_fund_acc,0) ;
736 EXCEPTION WHEN OTHERS THEN
737 fp_det_balances := l_fp_det_balances;
738 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
739 Raise;
740 end;
741
742 procedure display_det_balances ( fp_det_balances in out NOCOPY IGI_IAC_TYPES.iac_det_balances )
743 is
744 l_path varchar2(150);
745 begin
746 l_path := g_path||'display_det_balances';
747 --return;
748 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Adjustment Cost '||fp_det_balances.adjustment_cost);
749 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'NBV '||fp_det_balances.net_book_value);
750 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Reval Rsv Cost '||fp_det_balances.reval_reserve_cost);
751 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Reval Rsv Blog '||fp_det_balances.reval_reserve_backlog);
752 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Reval Rsv Gfun '||fp_det_balances.reval_reserve_gen_fund);
753 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Reval Rsv Net '||fp_det_balances.reval_reserve_net);
754 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Oper Acc cost '||fp_det_balances.operating_acct_cost);
755 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Oper Acc Blog '||fp_det_balances.operating_acct_backlog);
756 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Oper Acc NEt '||fp_det_balances.operating_acct_net);
757 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Oper Acc YTD '||fp_det_balances.operating_acct_ytd);
758 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Deprn period '||fp_det_balances.deprn_period);
759 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Deprn YTD '||fp_det_balances.deprn_ytd);
760 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Deprn Rsv '||fp_det_balances.deprn_reserve);
761 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Deprn Rsv Blog'||fp_det_balances.deprn_reserve_backlog);
762 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Gen Fund period '||fp_det_balances.general_fund_per);
763 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Gen Fund Acc '||fp_det_balances.general_fund_acc);
764 return;
765 end;
766
767
768 function create_det_balances_private ( fp_det_balances in IGI_IAC_TYPES.iac_det_balances )
769 return boolean is
770 l_det_balances IGI_IAC_TYPES.iac_det_balances;
771 l_rowid varchar2(300);
772 l_path varchar2(150);
773 begin
774 l_det_balances := fp_det_balances;
775 l_path := g_path||'create_det_balances_private';
776
777 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+DET BALANCES');
778 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_adjustment_id => '|| l_det_balances.adjustment_id);
779 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_asset_id =>'|| l_det_balances.asset_id);
780 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_distribution_id => '||l_det_balances.distribution_id);
781 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_book_type_code => '||l_det_balances.book_type_code);
782 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_period_counter => '||l_det_balances.period_counter);
783 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_adjustment_cost => '||l_det_balances.adjustment_cost);
784 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_net_book_value => '||l_det_balances.net_book_value);
785 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_reval_reserve_cost => '||l_det_balances.reval_reserve_cost);
786 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_reval_reserve_backlog => '||l_det_balances.reval_reserve_backlog);
787 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_reval_reserve_gen_fund => '||l_det_balances.reval_reserve_gen_fund);
788 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_reval_reserve_net => '||l_det_balances.reval_reserve_net);
789 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_operating_acct_cost => '||l_det_balances.operating_acct_cost);
790 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_operating_acct_backlog => '||l_det_balances.operating_acct_backlog);
791 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_operating_acct_net => '||l_det_balances.operating_acct_net);
792 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_operating_acct_ytd => '||l_det_balances.operating_acct_ytd);
793 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_deprn_period => '||l_det_balances.deprn_period);
794 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_deprn_ytd => '||l_det_balances.deprn_ytd);
795 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_deprn_reserve => '||l_det_balances.deprn_reserve);
796 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_deprn_reserve_backlog => '||l_det_balances.deprn_reserve_backlog);
797 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_general_fund_per => '||l_det_balances.general_fund_per);
798 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_general_fund_acc => '||l_det_balances.general_fund_acc);
799 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_last_reval_date => '||l_det_balances.last_reval_date);
800 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_current_reval_factor => '||l_det_balances.current_reval_factor);
801 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_cumulative_reval_factor => '||l_det_balances.cumulative_reval_factor);
802 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_active_flag => '||l_det_balances.active_flag);
803
804
805
806 IGI_IAC_DET_BALANCES_PKG.insert_row (
807 x_rowid => l_rowid,
808 x_adjustment_id => l_det_balances.adjustment_id,
809 x_asset_id => l_det_balances.asset_id,
810 x_distribution_id => l_det_balances.distribution_id,
811 x_book_type_code => l_det_balances.book_type_code,
812 x_period_counter => l_det_balances.period_counter,
813 x_adjustment_cost => l_det_balances.adjustment_cost,
814 x_net_book_value => l_det_balances.net_book_value,
815 x_reval_reserve_cost => l_det_balances.reval_reserve_cost,
816 x_reval_reserve_backlog => l_det_balances.reval_reserve_backlog,
817 x_reval_reserve_gen_fund => l_det_balances.reval_reserve_gen_fund,
818 x_reval_reserve_net => l_det_balances.reval_reserve_net,
819 x_operating_acct_cost => l_det_balances.operating_acct_cost,
820 x_operating_acct_backlog => l_det_balances.operating_acct_backlog,
821 x_operating_acct_net => l_det_balances.operating_acct_net,
822 x_operating_acct_ytd => 0, -- l_det_balances.operating_acct_ytd,
823 x_deprn_period => l_det_balances.deprn_period,
824 x_deprn_ytd => l_det_balances.deprn_ytd,
825 x_deprn_reserve => l_det_balances.deprn_reserve,
826 x_deprn_reserve_backlog => l_det_balances.deprn_reserve_backlog,
827 x_general_fund_per => l_det_balances.general_fund_per,
828 x_general_fund_acc => l_det_balances.general_fund_acc,
829 x_last_reval_date => l_det_balances.last_reval_date,
830 x_current_reval_factor => l_det_balances.current_reval_factor,
831 x_cumulative_reval_factor => l_det_balances.cumulative_reval_factor,
832 x_active_flag => l_det_balances.active_flag,
833 x_mode => 'R' );
834
835 return true;
836 exception when others then
837 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
838 return false;
839 end;
840
841 function create_fa_figures_private ( fp_det_balances in IGI_IAC_TYPES.iac_det_balances
842 , fp_fa_balances in IGI_IAC_TYPES.fa_hist_asset_info
843 )
844 return boolean is
845 l_det_balances IGI_IAC_TYPES.iac_det_balances;
846 l_fa_balances IGI_IAC_TYPES.fa_hist_asset_info;
847 l_rowid varchar2(300);
848 l_path varchar2(150);
849 begin
850 l_det_balances := fp_det_balances;
851 l_fa_balances := fp_fa_balances;
852 l_path := g_path||'create_fa_figures_private';
853
854 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+FA FIGURES');
855 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_adjustment_id => '|| l_det_balances.adjustment_id);
856 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_asset_id =>'|| l_det_balances.asset_id);
857 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_distribution_id => '||l_det_balances.distribution_id);
858 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_book_type_code => '||l_det_balances.book_type_code);
859 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_period_counter => '||l_det_balances.period_counter);
860 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_deprn_period => '||l_fa_balances.deprn_amount);
861 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_deprn_reserve => '||l_fa_balances.deprn_reserve);
862 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_ytd_deprn => '||l_fa_balances.ytd_deprn);
863 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_active_flag => '||l_det_balances.active_flag);
864
865 IGI_IAC_FA_DEPRN_PKG.insert_row (
866 x_rowid => l_rowid,
867 x_adjustment_id => l_det_balances.adjustment_id,
868 x_asset_id => l_det_balances.asset_id,
869 x_distribution_id => l_det_balances.distribution_id,
870 x_book_type_code => l_det_balances.book_type_code,
871 x_period_counter => l_det_balances.period_counter,
872 x_deprn_period => l_fa_balances.deprn_amount,
873 x_deprn_ytd => l_fa_balances.ytd_deprn,
874 x_deprn_reserve => l_fa_balances.deprn_reserve,
875 x_active_flag => l_det_balances.active_flag,
876 x_mode => 'R' );
877
878 return true;
879 exception when others then
880 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
881 return false;
882 end;
883
884 FUNCTION forward_inactive_det_balances(p_asset_id igi_iac_det_balances.asset_id%TYPE,
885 p_book_type_code igi_iac_det_balances.book_type_code%TYPE,
886 p_adjustment_id igi_iac_det_balances.adjustment_id%TYPE,
887 p_period_counter igi_iac_det_balances.period_counter%TYPE,
888 p_iac_inactive_dists_ytd IN OUT NOCOPY igi_iac_det_balances.deprn_ytd%TYPE,
889 p_fa_inactive_dists_ytd IN OUT NOCOPY igi_iac_fa_deprn.deprn_ytd%TYPE)
890 RETURN BOOLEAN IS
891
892 CURSOR c_get_prev_adjustment IS
893 SELECT max(adjustment_id)
894 FROM igi_iac_transaction_headers
895 WHERE book_type_code = p_book_type_code
896 AND asset_id = p_asset_id
897 AND adjustment_id < p_adjustment_id
898 AND adjustment_status NOT IN ('PREVIEW','OBSOLETE');
899
900 -- cursor to retrieve the inactive distributions that will be rolled forward
901 CURSOR c_get_iac_inactive_dists(cp_adjustment_id igi_iac_det_balances.adjustment_id%TYPE) IS
902 SELECT iidb.adjustment_id,
903 iidb.distribution_id,
904 iidb.adjustment_cost,
905 iidb.net_book_value,
906 iidb.reval_reserve_cost,
907 iidb.reval_reserve_backlog,
908 iidb.reval_reserve_gen_fund,
909 iidb.reval_reserve_net,
910 iidb.operating_acct_cost,
911 iidb.operating_acct_backlog,
912 iidb.operating_acct_net,
913 iidb.operating_acct_ytd,
914 iidb.deprn_period,
915 iidb.deprn_ytd,
916 iidb.deprn_reserve,
917 iidb.deprn_reserve_backlog,
918 iidb.general_fund_per,
919 iidb.general_fund_acc,
920 iidb.active_flag,
921 iidb.last_reval_date,
922 iidb.current_reval_factor,
923 iidb.cumulative_reval_factor
924 FROM igi_iac_det_balances iidb
925 WHERE iidb.adjustment_id = cp_adjustment_id
926 AND iidb.asset_id = p_asset_id
927 AND iidb.book_type_code = p_book_type_code
928 AND nvl(iidb.active_flag,'Y') = 'N';
929
930 -- Cursor to fetch depreciation balances from
931 -- igi_iac_fa_deprn for inactive distributions
932 CURSOR c_get_fa_inactive_dists(cp_adjustment_id igi_iac_fa_deprn.adjustment_id%TYPE)
933 IS
934 SELECT iifd.distribution_id,
935 iifd.deprn_period,
936 iifd.deprn_ytd,
937 iifd.deprn_reserve,
938 iifd.active_flag
939 FROM igi_iac_fa_deprn iifd
940 WHERE iifd.adjustment_id = cp_adjustment_id
941 AND iifd.book_type_code = p_book_type_code
942 AND iifd.asset_id = p_asset_id
943 AND nvl(iifd.active_flag,'Y') = 'N';
944
945 -- local variables
946 l_prev_adjustment_id igi_iac_transaction_headers.adjustment_id%TYPE;
947 l_rowid varchar2(40);
948 l_path varchar2(150);
949 l_iac_inactive_dists_ytd number;
950 l_fa_inactive_dists_ytd number;
951 BEGIN
952 l_path := g_path||'forward_inactive_det_balances';
953 l_prev_adjustment_id := NULL;
954 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+Start Processing inactive distributions');
955 l_iac_inactive_dists_ytd := 0;
956 l_fa_inactive_dists_ytd := 0;
957
958 OPEN c_get_prev_adjustment;
959 FETCH c_get_prev_adjustment INTO l_prev_adjustment_id;
960 CLOSE c_get_prev_adjustment;
961
962 IF l_prev_adjustment_id IS NULL THEN
963 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+No inactive distributions to carry forward');
964 RETURN TRUE;
965 END IF;
966
967 FOR l_iac_inactive_dist IN c_get_iac_inactive_dists(l_prev_adjustment_id) LOOP
968 -- insert into igi_iac_det_balances with reinstatement adjustment_id
969 l_rowid := NULL;
970 IGI_IAC_DET_BALANCES_PKG.Insert_Row(
971 x_rowid => l_rowid,
972 x_adjustment_id => p_adjustment_id,
973 x_asset_id => p_asset_id,
974 x_book_type_code => p_book_type_code,
975 x_distribution_id => l_iac_inactive_dist.distribution_id,
976 x_period_counter => p_period_counter,
977 x_adjustment_cost => l_iac_inactive_dist.adjustment_cost,
978 x_net_book_value => l_iac_inactive_dist.net_book_value,
979 x_reval_reserve_cost => l_iac_inactive_dist.reval_reserve_cost,
980 x_reval_reserve_backlog => l_iac_inactive_dist.reval_reserve_backlog,
981 x_reval_reserve_gen_fund => l_iac_inactive_dist.reval_reserve_gen_fund,
982 x_reval_reserve_net => l_iac_inactive_dist.reval_reserve_net,
983 x_operating_acct_cost => l_iac_inactive_dist.operating_acct_cost,
984 x_operating_acct_backlog => l_iac_inactive_dist.operating_acct_backlog,
985 x_operating_acct_net => l_iac_inactive_dist.operating_acct_net,
986 x_operating_acct_ytd => l_iac_inactive_dist.operating_acct_ytd,
987 x_deprn_period => l_iac_inactive_dist.deprn_period,
988 x_deprn_ytd => l_iac_inactive_dist.deprn_ytd,
989 x_deprn_reserve => l_iac_inactive_dist.deprn_reserve,
990 x_deprn_reserve_backlog => l_iac_inactive_dist.deprn_reserve_backlog,
991 x_general_fund_per => l_iac_inactive_dist.general_fund_per,
992 x_general_fund_acc => l_iac_inactive_dist.general_fund_acc,
993 x_last_reval_date => l_iac_inactive_dist.last_reval_date,
994 x_current_reval_factor => l_iac_inactive_dist.current_reval_factor,
995 x_cumulative_reval_factor => l_iac_inactive_dist.cumulative_reval_factor,
996 x_active_flag => l_iac_inactive_dist.active_flag,
997 x_mode => 'R' );
998 l_iac_inactive_dists_ytd := l_iac_inactive_dists_ytd + l_iac_inactive_dist.deprn_ytd;
999 END LOOP;
1000
1001 FOR l_fa_inactive_dist IN c_get_fa_inactive_dists(l_prev_adjustment_id) LOOP
1002 -- insert into igi_iac_fa_deprn with the new adjustment_id
1003 l_rowid := NULL;
1004 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
1005 x_rowid => l_rowid,
1006 x_book_type_code => p_book_type_code,
1007 x_asset_id => p_asset_id,
1008 x_period_counter => p_period_counter,
1009 x_adjustment_id => p_adjustment_id,
1010 x_distribution_id => l_fa_inactive_dist.distribution_id,
1011 x_deprn_period => l_fa_inactive_dist.deprn_period,
1012 x_deprn_ytd => l_fa_inactive_dist.deprn_ytd,
1013 x_deprn_reserve => l_fa_inactive_dist.deprn_reserve,
1014 x_active_flag => l_fa_inactive_dist.active_flag,
1015 x_mode => 'R' );
1016 l_fa_inactive_dists_ytd := l_fa_inactive_dists_ytd + l_fa_inactive_dist.deprn_ytd;
1017 END LOOP;
1018
1019 p_iac_inactive_dists_ytd := l_iac_inactive_dists_ytd;
1020 p_fa_inactive_dists_ytd := l_fa_inactive_dists_ytd;
1021
1022 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+End Processing inactive distributions');
1023 return true;
1024 exception when others then
1025 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
1026 return false;
1027 END forward_inactive_det_balances;
1028
1029 FUNCTION Create_Inactive_Det_Balances(p_asset_id igi_iac_det_balances.asset_id%TYPE,
1030 p_book_type_code igi_iac_det_balances.book_type_code%TYPE,
1031 p_adjustment_id igi_iac_det_balances.adjustment_id%TYPE,
1032 p_period_counter igi_iac_det_balances.period_counter%TYPE,
1033 p_asset_iac_ytd IN OUT NOCOPY igi_iac_det_balances.deprn_ytd%TYPE,
1034 p_asset_fa_ytd IN OUT NOCOPY igi_iac_fa_deprn.deprn_ytd%TYPE,
1035 p_YTD_prorate_dists_tab igi_iac_types.prorate_dists_tab,
1036 p_YTD_prorate_dists_idx binary_integer)
1037 RETURN BOOLEAN IS
1038
1039 l_rowid varchar2(40);
1040 l_path varchar2(150);
1041 l_YTD_prorate_dists_tab igi_iac_types.prorate_dists_tab;
1042 l_YTD_prorate_dists_idx binary_integer;
1043 idx_YTD binary_integer;
1044 l_dist_iac_ytd number;
1045 l_dist_fa_ytd number;
1046
1047 BEGIN
1048 l_path := g_path||'create_inactive_det_balances';
1049 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+Start Processing inactive distributions');
1050 l_YTD_prorate_dists_tab := p_YTD_prorate_dists_tab;
1051
1052 idx_YTD := l_YTD_prorate_dists_tab.FIRST;
1053 WHILE idx_YTD <= l_YTD_prorate_dists_tab.LAST LOOP
1054 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Distribution Id:'||l_YTD_prorate_dists_tab(idx_YTD).distribution_id);
1055 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Active_flag:'||l_YTD_prorate_dists_tab(idx_YTD).active_flag);
1056
1057 IF l_YTD_prorate_dists_tab(idx_YTD).active_flag = 'N' THEN
1058 l_dist_iac_ytd := l_YTD_prorate_dists_tab(idx_YTD).ytd_prorate_factor * p_asset_iac_ytd;
1059 l_dist_fa_ytd := l_YTD_prorate_dists_tab(idx_YTD).ytd_prorate_factor * p_asset_fa_ytd;
1060
1061 IF NOT igi_iac_common_utils.iac_round ( x_amount => l_dist_iac_ytd
1062 , x_book => p_book_type_code) then
1063 RETURN FALSE;
1064 END IF;
1065 IF NOT igi_iac_common_utils.iac_round ( x_amount => l_dist_fa_ytd
1066 , x_book => p_book_type_code) then
1067 RETURN FALSE;
1068 END IF;
1069
1070 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Inserting into igi_iac_det_balances');
1071 l_rowid := NULL;
1072 IGI_IAC_DET_BALANCES_PKG.Insert_Row(
1073 x_rowid => l_rowid,
1074 x_adjustment_id => p_adjustment_id,
1075 x_asset_id => p_asset_id,
1076 x_book_type_code => p_book_type_code,
1077 x_distribution_id => l_YTD_prorate_dists_tab(idx_YTD).distribution_id,
1078 x_period_counter => p_period_counter,
1079 x_adjustment_cost => 0,
1080 x_net_book_value => 0,
1081 x_reval_reserve_cost => 0,
1082 x_reval_reserve_backlog => 0,
1083 x_reval_reserve_gen_fund => 0,
1084 x_reval_reserve_net => 0,
1085 x_operating_acct_cost => 0,
1086 x_operating_acct_backlog => 0,
1087 x_operating_acct_net => 0,
1088 x_operating_acct_ytd => 0,
1089 x_deprn_period => 0,
1090 x_deprn_ytd => l_dist_iac_ytd,
1091 x_deprn_reserve => 0,
1092 x_deprn_reserve_backlog => 0,
1093 x_general_fund_per => 0,
1094 x_general_fund_acc => 0,
1095 x_last_reval_date => null,
1096 x_current_reval_factor => 0,
1097 x_cumulative_reval_factor => 0,
1098 x_active_flag => 'N',
1099 x_mode => 'R' );
1100
1101 -- insert into igi_iac_fa_deprn with the new adjustment_id
1102 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Inserting into igi_iac_fa_deprn');
1103 l_rowid := NULL;
1104 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
1105 x_rowid => l_rowid,
1106 x_book_type_code => p_book_type_code,
1107 x_asset_id => p_asset_id,
1108 x_period_counter => p_period_counter,
1109 x_adjustment_id => p_adjustment_id,
1110 x_distribution_id => l_YTD_prorate_dists_tab(idx_YTD).distribution_id,
1111 x_deprn_period => 0,
1112 x_deprn_ytd => l_dist_fa_ytd,
1113 x_deprn_reserve => 0,
1114 x_active_flag => 'N',
1115 x_mode => 'R' );
1116
1117 END IF;
1118 idx_ytd := l_YTD_prorate_dists_tab.Next(idx_ytd);
1119 END LOOP;
1120 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+End Processing inactive distributions');
1121 RETURN TRUE;
1122
1123 EXCEPTION WHEN others then
1124 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
1125 return false;
1126 END Create_Inactive_Det_Balances;
1127
1128 function create_det_balances ( fp_reval_params in out NOCOPY IGI_IAC_TYPES.iac_reval_params
1129 , fp_second_set in boolean
1130 )
1131 return boolean is
1132
1133 cursor c_get_dist_deprn(cp_distribution_id number) is
1134 select (nvl(deprn_amount,0) - nvl(deprn_adjustment_amount,0)) deprn_amount,
1135 deprn_reserve
1136 from fa_deprn_detail
1137 where book_type_code = fp_reval_params.reval_asset_params.book_type_code
1138 and asset_id = fp_reval_params.reval_asset_params.asset_id
1139 and distribution_id = cp_distribution_id
1140 and period_counter = (select max(period_counter)
1141 from fa_deprn_summary
1142 where book_type_code = fp_reval_params.reval_asset_params.book_type_code
1143 and asset_id = fp_reval_params.reval_asset_params.asset_id);
1144
1145 cursor c_get_dist_ytd(cp_distribution_id fa_deprn_detail.distribution_id%TYPE) is
1146 select sum(nvl(fdd.deprn_amount,0)-nvl(fdd.deprn_adjustment_amount,0)) deprn_YTD
1147 from fa_deprn_detail fdd
1148 where fdd.book_type_code = fp_reval_params.reval_asset_params.book_type_code
1149 and fdd.asset_id = fp_reval_params.reval_asset_params.asset_id
1150 and fdd.distribution_id = cp_distribution_id
1151 and fdd.period_counter in (select period_counter from fa_deprn_periods
1152 where book_type_code = fp_reval_params.reval_asset_params.book_type_code
1153 and fiscal_year = (select decode(period_num,1,fiscal_year-1,fiscal_year)
1154 from fa_deprn_periods
1155 where period_close_date is NULL
1156 and book_type_code = fp_reval_params.reval_asset_params.book_type_code))
1157 group by fdd.asset_id,fdd.distribution_id;
1158
1159 cursor c_get_prev_fa_deprn(cp_adjustment_id igi_iac_fa_deprn.adjustment_id%TYPE,
1160 cp_distribution_id igi_iac_fa_deprn.distribution_id%TYPE) is
1161 select iifd.deprn_period, iifd.deprn_ytd, iifd.deprn_reserve
1162 from igi_iac_fa_deprn iifd
1163 where iifd.asset_id = fp_reval_params.reval_asset_params.asset_id
1164 and iifd.book_type_code = fp_reval_params.reval_asset_params.book_type_code
1165 and iifd.adjustment_id = cp_adjustment_id
1166 and iifd.distribution_id = cp_distribution_id;
1167
1168 l_rowid varchar2(40);
1169 l_adjustment_id igi_iac_transaction_headers.adjustment_id%TYPE;
1170 l_reval_factor_curr number;
1171 l_reval_factor_cumm number;
1172 l_prev_factor_cumm number;
1173 l_prorate_dists_tab igi_iac_types.prorate_dists_tab;
1174 l_prorate_dists_idx binary_integer;
1175
1176 l_processed boolean;
1177 idx binary_integer;
1178 l_prev_adj_id number;
1179
1180 l_operatg_blog number;
1181 l_reserve_blog number;
1182 l_deprn_blog number;
1183 l_reserve_cost number;
1184 l_operatg_ytd number;
1185 l_operatg_ytd_bal number;
1186 l_operatg_net number;
1187 l_operatg_cost number;
1188 l_deprn_ytd number;
1189 l_old_deprn_ytd number;
1190 l_old_gen_fund number;
1191
1192 l_db IGI_IAC_TYPES.iac_det_balances;
1193 l_db_fa IGI_IAC_TYPES.fa_hist_asset_info;
1194
1195 l_prev_db IGI_IAC_TYPES.iac_det_balances;
1196 l_remaining IGI_IAC_TYPES.iac_det_balances;
1197 l_remaining_fa IGI_IAC_TYPES.fa_hist_asset_info;
1198 l_total IGI_IAC_TYPES.iac_det_balances;
1199 l_total_fa IGI_IAC_TYPES.fa_hist_asset_info;
1200
1201 /*
1202 Note : l_asset_level_calc is an important structure
1203 -- If this is catchup, it needs to use the reval output asset information.
1204 -- if this is reval, it needs to use the reval mvmt asset information
1205 -- if this is reclass, it needs to use reval output asset information.
1206 */
1207 l_asset_level_calc IGI_IAC_TYPES.iac_reval_output_asset;
1208 l_factor number;
1209 l_ytd_factor number;
1210
1211 l_fp_reval_params IGI_IAC_TYPES.iac_reval_params;
1212
1213 /* YTD Revaluation proration */
1214 l_deprn_ytd_total number;
1215 l_remaining_deprn_ytd number;
1216 l_dist_deprn_ytd number;
1217 l_iac_inactive_dists_ytd number;
1218 l_fa_inactive_dists_ytd number;
1219 l_YTD_prorate_dists_tab igi_iac_types.prorate_dists_tab;
1220 l_YTD_prorate_dists_idx binary_integer;
1221 idx_YTD binary_integer;
1222
1223 l_path varchar2(150);
1224 begin
1225 l_processed := false;
1226 idx := 1;
1227 l_operatg_blog := 0;
1228 l_reserve_blog := 0;
1229 l_deprn_blog := 0;
1230 l_reserve_cost := 0;
1231 l_operatg_ytd := 0;
1232 l_operatg_ytd_bal := 0;
1233 l_operatg_net := 0;
1234 l_operatg_cost := 0;
1235 l_deprn_ytd := 0;
1236 l_old_deprn_ytd := 0;
1237 l_old_gen_fund := 0;
1238 l_deprn_ytd_total := 0;
1239 l_remaining_deprn_ytd := 0;
1240 l_dist_deprn_ytd := 0;
1241 l_iac_inactive_dists_ytd := 0;
1242 l_fa_inactive_dists_ytd := 0;
1243 l_path := g_path||'create_det_balances';
1244 -- for NOCOPY.
1245 l_fp_reval_params := fp_reval_params;
1246
1247 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'begin create_det_balances');
1248 /* check if the reval mode is proper */
1249 if fp_reval_params.reval_control.revaluation_mode not in ( 'P','L')
1250 then
1251 return true;
1252 end if;
1253 /* if this revaluation use the movement information, else use output information */
1254 if fp_reval_params.reval_control.transaction_type_code = 'REVALUATION' OR fp_second_set then
1255 l_asset_level_calc := fp_reval_params.reval_output_asset_mvmt;
1256 l_asset_level_calc.deprn_amount := fp_reval_params.reval_output_asset.deprn_amount;
1257 else
1258 l_asset_level_calc := fp_reval_params.reval_output_asset;
1259 end if;
1260 l_operatg_net := l_asset_level_calc.operating_acct;
1261
1262 if fp_second_set then
1263 l_adjustment_id := fp_reval_params.reval_asset_params.second_set_adjustment_id ;
1264 l_reval_factor_curr := fp_reval_params.reval_curr_rate_info_next.current_reval_factor;
1265 l_reval_factor_cumm := fp_reval_params.reval_curr_rate_info_next.cumulative_reval_factor;
1266 l_operatg_ytd := fp_reval_params.reval_asset_params.curr_ytd_opacc_next;
1267 l_deprn_ytd_total := fp_reval_params.reval_asset_params.curr_ytd_deprn_next;/* YTD Proraion for det_balances*/
1268 else
1269 l_adjustment_id := fp_reval_params.reval_asset_params.first_set_adjustment_id ;
1270 l_reval_factor_curr := fp_reval_params.reval_curr_rate_info_first.current_reval_factor;
1271 l_reval_factor_cumm := fp_reval_params.reval_curr_rate_info_first.cumulative_reval_factor;
1272 l_operatg_ytd := fp_reval_params.reval_asset_params.curr_ytd_opacc_first;
1273 l_deprn_ytd_total := fp_reval_params.reval_asset_params.curr_ytd_deprn_first; /* YTD Proraion det_balances*/
1274 end if;
1275 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+l_deprn_ytd : '||l_deprn_ytd);
1276 l_prev_factor_cumm := fp_reval_params.reval_prev_rate_info.cumulative_reval_factor;
1277 l_deprn_ytd := fp_reval_params.reval_asset_params.ytd_deprn_mvmt;/* YTD proration for accounting*/
1278
1279
1280 if l_adjustment_id = 0 then
1281 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'+adjustment id is not set');
1282 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'end create_det_balances');
1283 return false;
1284 end if;
1285 /* make the call to the common utils to get the array of distributions */
1286 /* this would be overriden very soon by the correct distributions array program */
1287 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+proration of active dists');
1288 IF NOT IGI_IAC_REVAL_UTILITIES.prorate_dists ( fp_asset_id => fp_reval_params.reval_asset_params.asset_id
1289 , fp_book_type_code => fp_reval_params.reval_asset_params.book_type_code
1290 , fp_current_period_counter => fp_reval_params.reval_asset_params.period_counter
1291 , fp_prorate_dists_tab => l_prorate_dists_tab
1292 , fp_prorate_dists_idx => l_prorate_dists_idx
1293 )
1294 THEN
1295 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'+error IGI_IAC_REVAL_UTILITIES.prorate_dists');
1296 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'end create_det_balances');
1297 return false;
1298 END IF;
1299
1300 IF (fp_reval_params.reval_control.transaction_type_code = 'REVALUATION' AND
1301 fp_reval_params.reval_control.first_time_flag) THEN
1302 IF NOT IGI_IAC_REVAL_UTILITIES.prorate_all_dists_YTD ( fp_asset_id => fp_reval_params.reval_asset_params.asset_id
1303 , fp_book_type_code => fp_reval_params.reval_asset_params.book_type_code
1304 , fp_current_period_counter => fp_reval_params.reval_asset_params.period_counter
1305 , fp_prorate_dists_tab => l_YTD_prorate_dists_tab
1306 , fp_prorate_dists_idx => l_YTD_prorate_dists_idx
1307 )
1308 THEN
1309 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'+error IGI_IAC_REVAL_UTILITIES.prorate_all_dists_YTD');
1310 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'end create_det_balances');
1311 return false;
1312 END IF;
1313 ELSE
1314 IF NOT IGI_IAC_REVAL_UTILITIES.prorate_active_dists_YTD ( fp_asset_id => fp_reval_params.reval_asset_params.asset_id
1315 , fp_book_type_code => fp_reval_params.reval_asset_params.book_type_code
1316 , fp_current_period_counter => fp_reval_params.reval_asset_params.period_counter
1317 , fp_prorate_dists_tab => l_YTD_prorate_dists_tab
1318 , fp_prorate_dists_idx => l_YTD_prorate_dists_idx
1319 )
1320 THEN
1321 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'+error IGI_IAC_REVAL_UTILITIES.prorate_active_dists_YTD');
1322 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'end create_det_balances');
1323 return false;
1324 END IF;
1325 END IF;
1326
1327 /*** Logic is as follows :
1328 *****************************
1329 a. get the movement info at the asset level.
1330 b. prorate the movement info across the distributions
1331 c. perform rounding and track the balance remaining.
1332 d. if not last distribution
1333 movement info := use the rounded info
1334 else
1335 movement info := remaining balance info
1336 end if;
1337 create accounting info using the movement info
1338 e. add the movement info to prev values to give current values.
1339 f. create det new balance entries
1340 *******************************
1341 ***/
1342 if not fp_second_set then
1343 begin
1344 select nvl(max(adjustment_id),-1)
1345 into l_prev_adj_id
1346 from igi_iac_transaction_headers
1347 where asset_id = fp_reval_params.reval_asset_params.asset_id
1348 and book_type_code = fp_reval_params.reval_asset_params.book_type_code
1349 and adjustment_status in ( 'RUN','COMPLETE')
1350 and adjustment_id < l_adjustment_id
1351 ;
1352 /*
1353 l_prev_adj_id := igi_iac_reval_utilities.latest_adjustment
1354 ( fp_book_type_code => fp_reval_params.reval_asset_params.book_type_code
1355 , fp_asset_id => fp_reval_params.reval_asset_params.asset_id);
1356 */
1357 exception
1358 when others then
1359 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'+get latest_transaction api failed.');
1360 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,sqlerrm);
1361 l_prev_adj_id := -1;
1362 end;
1363 else
1364 l_prev_adj_id := fp_reval_params.reval_asset_params.first_set_adjustment_id;
1365 end if;
1366
1367 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+get the previous adjustment id '|| l_prev_adj_id);
1368
1369 /* get the total figures for the asset from det balances perspective */
1370 if l_reval_factor_cumm >= 1 and l_prev_factor_cumm >= 1 then
1371 l_operatg_cost := 0;
1372 l_operatg_net := l_asset_level_calc.operating_acct;
1373 l_operatg_blog := l_operatg_cost - l_asset_level_calc.operating_acct;
1374
1375 l_deprn_blog := l_asset_level_calc.backlog_deprn_reserve;
1376 l_reserve_blog := l_asset_level_calc.backlog_deprn_reserve + l_asset_level_calc.operating_acct;
1377 l_reserve_cost := l_asset_level_calc.adjusted_cost;
1378 else
1379
1380 l_operatg_net := l_asset_level_calc.operating_acct;
1381 l_operatg_blog := l_asset_level_calc.backlog_deprn_reserve + l_asset_level_calc.general_fund;
1382 l_operatg_cost := l_operatg_net + l_operatg_blog;
1383 l_deprn_blog := l_asset_level_calc.backlog_deprn_reserve;
1384 l_reserve_cost := 0;
1385 l_reserve_blog := l_reserve_cost - l_asset_level_calc.general_fund;
1386 end if;
1387 l_total_fa := fp_reval_params.fa_asset_info;
1388 IF fp_reval_params.fa_asset_info.period_counter_fully_reserved IS NOT NULL
1389 AND fp_reval_params.reval_asset_params.period_counter > fp_reval_params.fa_asset_info.last_period_counter THEN
1390 l_total_fa.deprn_amount := 0;
1391 l_total.deprn_period := 0;
1392 END IF;
1393
1394 l_total.adjustment_id := l_adjustment_id;
1395 l_total.asset_id := fp_reval_params.reval_asset_params.asset_id;
1396 l_total.distribution_id := -1;
1397 l_total.book_type_code := fp_reval_params.reval_asset_params.book_type_code;
1398 l_total.period_counter := fp_reval_params.reval_asset_params.period_counter;
1399 l_total.adjustment_cost := l_asset_level_calc.adjusted_cost;
1400 l_total.reval_reserve_cost := l_reserve_cost;
1401 l_total.reval_reserve_backlog := l_reserve_blog;
1402 l_total.reval_reserve_gen_fund := l_asset_level_calc.general_fund;
1403 l_total.reval_reserve_net := l_asset_level_calc.reval_reserve;
1404 l_total.operating_acct_cost := l_operatg_cost ;
1405 l_total.operating_acct_backlog := l_operatg_blog;
1406 l_total.operating_acct_net := l_operatg_net;
1407 l_total.operating_acct_ytd := l_operatg_ytd;
1408 l_total.deprn_period := l_asset_level_calc.deprn_amount;
1409 l_total.deprn_ytd := l_deprn_ytd;/* YTD proration for accounting */
1410
1411 l_total.deprn_reserve := l_asset_level_calc.deprn_reserve;
1412 l_total.deprn_reserve_backlog := l_deprn_blog;
1413
1414 if l_reval_factor_cumm >= 1 and l_prev_factor_cumm >= 1 then
1415 l_total.general_fund_per := l_asset_level_calc.deprn_amount;
1416 else
1417 l_total.general_fund_per := 0;
1418 end if;
1419 l_total.general_fund_acc := l_asset_level_calc.general_fund;
1420 l_total.last_reval_date := sysdate;
1421 l_total.current_reval_factor := l_reval_factor_curr;
1422 l_total.cumulative_reval_factor := l_reval_factor_cumm;
1423 l_total.net_book_value := l_total.adjustment_cost - l_total.deprn_reserve -
1424 l_total.deprn_reserve_backlog;
1425 l_total.active_flag := NULL;
1426
1427 -- round_det_balances ( l_total ) ;
1428 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+l_total.deprn_ytd : '||l_total.deprn_ytd);
1429 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+FA deprn period : '|| l_total_fa.deprn_amount);
1430 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+FA deprn YTD :'||l_total_fa.ytd_deprn);
1431 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Display unrounded figures for the asset');
1432 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'-------------------------------------------------------------');
1433 display_det_balances ( l_total );
1434 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'-------------------------------------------------------------');
1435
1436 IF NOT Forward_inactive_det_balances(fp_reval_params.reval_asset_params.asset_id,
1437 fp_reval_params.reval_asset_params.book_type_code,
1438 l_adjustment_id,
1439 fp_reval_params.reval_asset_params.period_counter,
1440 l_iac_inactive_dists_ytd,
1441 l_fa_inactive_dists_ytd) THEN
1442 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'+forward inactive detail balances Failed');
1443 return false;
1444 ELSE
1445 l_deprn_ytd_total := l_deprn_ytd_total - l_iac_inactive_dists_ytd; /* YTD for igi_iac_det_balances */
1446 l_total_fa.ytd_deprn := l_total_fa.ytd_deprn - l_fa_inactive_dists_ytd; /* YTD for igi_iac_fa_deprn */
1447 END IF;
1448
1449 IF (fp_reval_params.reval_control.transaction_type_code = 'REVALUATION' AND
1450 fp_reval_params.reval_control.first_time_flag) THEN
1451
1452 l_fa_inactive_dists_ytd := 0;
1453 IF NOT create_inactive_det_balances(fp_reval_params.reval_asset_params.asset_id,
1454 fp_reval_params.reval_asset_params.book_type_code,
1455 l_adjustment_id,
1456 fp_reval_params.reval_asset_params.period_counter,
1457 l_deprn_ytd_total,
1458 l_total_fa.ytd_deprn,
1459 l_YTD_prorate_dists_tab,
1460 l_YTD_prorate_dists_idx) THEN
1461 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'+create inactive detail balances Failed');
1462 return false;
1463 END IF;
1464 END IF;
1465
1466 if not igi_iac_common_utils.iac_round ( x_amount => l_deprn_ytd_total
1467 , x_book => fp_reval_params.reval_asset_params.book_type_code) then
1468 return false;
1469 end if;
1470
1471 l_remaining := l_total;
1472 l_remaining_fa := l_total_fa;
1473 l_remaining_deprn_ytd := l_deprn_ytd_total;/* YTD proration*/
1474
1475
1476 idx := l_prorate_dists_tab.FIRST;
1477 WHILE idx <= l_prorate_dists_tab.LAST LOOP
1478 l_factor := l_prorate_dists_tab ( idx ).normal_prorate_factor ;
1479 l_ytd_factor := 0 ;
1480
1481 IF fp_reval_params.reval_control.transaction_type_code = 'RECLASS' THEN
1482 l_ytd_factor := l_factor;
1483 ELSE
1484 idx_YTD := l_YTD_prorate_dists_tab.FIRST;
1485 WHILE idx_YTD <= l_YTD_prorate_dists_tab.LAST LOOP
1486 IF l_prorate_dists_tab(idx).distribution_id = l_YTD_prorate_dists_tab(idx_YTD).distribution_id THEN
1487 l_ytd_factor := l_YTD_prorate_dists_tab(idx_YTD).ytd_prorate_factor;
1488 EXIT;
1489 END IF;
1490 idx_ytd := l_YTD_prorate_dists_tab.Next(idx_ytd);
1491 END LOOP;
1492 END IF;
1493
1494 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+prorate distribution factor '|| l_factor);
1495 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+prorate YTD distribution factor '|| l_ytd_factor);
1496 l_processed := true;
1497
1498 /* for each distribution, prorate the asset mvmt information */
1499 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+mvmt cost '|| fp_reval_params.reval_output_asset_mvmt.adjusted_cost);
1500
1501 if l_reval_factor_cumm >= 1 and l_prev_factor_cumm >= 1 then
1502 l_reserve_blog := l_factor * l_total.reval_reserve_backlog;
1503 l_operatg_blog := l_factor * l_total.operating_acct_backlog;
1504 l_operatg_cost := 0;
1505 l_operatg_net := l_operatg_cost - l_operatg_blog;
1506 l_reserve_cost := l_factor * l_total.adjustment_cost;
1507 l_deprn_blog := l_factor * l_total.deprn_reserve_backlog;
1508 else
1509 l_operatg_blog := l_factor * l_total.operating_acct_backlog;
1510 l_reserve_blog := l_factor * l_total.reval_reserve_backlog;
1511 l_operatg_cost := l_factor * l_total.operating_acct_cost;
1512 l_operatg_net := l_operatg_cost - l_operatg_blog;
1513 l_reserve_cost := 0;
1514 l_deprn_blog := l_factor * l_total.deprn_reserve_backlog;
1515 end if;
1516 l_db_fa := l_remaining_fa;
1517
1518 begin
1519 select nvl(operating_acct_ytd,0)
1520 into l_operatg_ytd_bal
1521 from igi_iac_det_balances
1522 where asset_id = fp_reval_params.reval_asset_params.asset_id
1523 and distribution_id = l_prorate_dists_tab( idx ).distribution_id
1524 and book_type_code = fp_reval_params.reval_asset_params.book_type_code
1525 and adjustment_id = l_prev_adj_id
1526 and l_prev_adj_id <> -1
1527 ;
1528 exception when others then
1529 l_operatg_ytd_bal := 0;
1530 end;
1531 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+ l_total.deprn_ytd : '||l_total.deprn_ytd);
1532 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+ FA deprn period : '|| l_total_fa.deprn_amount);
1533 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+ FA deprn YTD :'||l_total_fa.ytd_deprn);
1534
1535 if idx <> l_prorate_dists_tab.LAST THEN
1536
1537 if l_reval_factor_cumm >= 1 and l_prev_factor_cumm >= 1
1538 then
1539 null;
1540 else
1541 l_operatg_ytd_bal := l_operatg_ytd_bal + l_operatg_net;
1542 end if;
1543 l_db.adjustment_id := l_adjustment_id;
1544 l_db.asset_id := fp_reval_params.reval_asset_params.asset_id;
1545 l_db.distribution_id := l_prorate_dists_tab( idx ).distribution_id;
1546 l_db.book_type_code := fp_reval_params.reval_asset_params.book_type_code;
1547 l_db.period_counter := fp_reval_params.reval_asset_params.period_counter;
1548 l_db.adjustment_cost := l_factor * l_total.adjustment_cost;
1549 l_db.net_book_value := l_factor * l_total.net_book_value;
1550 l_db.reval_reserve_cost := l_reserve_cost;
1551 l_db.reval_reserve_backlog := l_reserve_blog;
1552 l_db.reval_reserve_gen_fund := l_factor * l_total.reval_reserve_gen_fund;
1553 l_db.reval_reserve_net := l_factor * l_total.reval_reserve_net;
1554 l_db.operating_acct_cost := l_operatg_cost;
1555 l_db.operating_acct_backlog := l_operatg_blog;
1556 l_db.operating_acct_net := l_operatg_net;
1557 l_db.operating_acct_ytd := l_operatg_ytd_bal;
1558 l_db.deprn_period := l_factor * l_total.deprn_period;
1559 l_db.deprn_ytd := l_factor * l_total.deprn_ytd; /* YTD for EXPENSE accounting */
1560 l_db.deprn_reserve := l_factor * l_total.deprn_reserve;
1561 l_db.deprn_reserve_backlog := l_deprn_blog;
1562 l_db.general_fund_per := l_factor * l_total.general_fund_per;
1563 l_db.general_fund_acc := l_factor * l_total.general_fund_acc;
1564 l_db.last_reval_date := fp_reval_params.reval_asset_params.revaluation_date;
1565 l_db.current_reval_factor := l_reval_factor_curr;
1566 l_db.cumulative_reval_factor := l_reval_factor_cumm;
1567 l_db.active_flag := l_prorate_dists_tab ( idx ).active_flag;
1568
1569 l_dist_deprn_ytd := l_deprn_ytd_total * l_ytd_factor; /* YTD proration for igi_iac_det_balances */
1570
1571 if not igi_iac_common_utils.iac_round ( x_amount => l_dist_deprn_ytd
1572 , x_book => fp_reval_params.reval_asset_params.book_type_code ) then
1573 return false;
1574 end if;
1575
1576 l_db_fa := l_total_fa; /* initalize */
1577
1578 l_db_fa.ytd_deprn := l_total_fa.ytd_deprn * l_ytd_factor; /* YTD for igi_iac_fa_deprn */
1579 l_db_fa.deprn_amount := l_total_fa.deprn_amount * l_factor;
1580 l_db_fa.deprn_reserve := l_total_fa.deprn_reserve * l_factor;
1581
1582 round_fa_figures ( fp_fa_hist => l_db_fa , fp_det_balances => l_db );
1583
1584 l_remaining_fa.ytd_deprn := l_remaining_fa.ytd_deprn - l_db_fa.ytd_deprn;
1585 l_remaining_fa.deprn_amount := l_remaining_fa.deprn_amount - l_db_fa.deprn_amount;
1586 l_remaining_fa.deprn_reserve := l_remaining_fa.deprn_reserve - l_db_fa.deprn_reserve;
1587
1588 verify_det_balances ( fp_det_balances => l_db
1589 , fp_prev_cum_factor => l_prev_factor_cumm
1590 , fp_curr_cum_factor => l_reval_factor_cumm
1591 );
1592 remaining_det_balances ( fp_det_balances => l_remaining , fp_det_delta => l_db );
1593 l_remaining_deprn_ytd := l_remaining_deprn_ytd - l_dist_deprn_ytd;/* YTD proration*/
1594 round_det_balances ( l_db ) ;
1595 else
1596 l_db.adjustment_id := l_adjustment_id;
1597 l_db.asset_id := fp_reval_params.reval_asset_params.asset_id;
1598 l_db.distribution_id := l_prorate_dists_tab( idx ).distribution_id;
1599 l_db.book_type_code := fp_reval_params.reval_asset_params.book_type_code;
1600 l_db.period_counter := fp_reval_params.reval_asset_params.period_counter;
1601 l_db.adjustment_cost := l_remaining.adjustment_cost;
1602 l_db.net_book_value := l_remaining.net_book_value;
1603 l_db.reval_reserve_cost := l_remaining.reval_reserve_cost;
1604 l_db.reval_reserve_backlog := l_remaining.reval_reserve_backlog ;
1605 l_db.reval_reserve_gen_fund := l_remaining.reval_reserve_gen_fund;
1606 l_db.reval_reserve_net := l_remaining.reval_reserve_net;
1607 l_db.operating_acct_cost := l_remaining.operating_acct_cost;
1608 l_db.operating_acct_backlog := l_remaining.operating_acct_backlog;
1609 l_db.operating_acct_net := l_remaining.operating_acct_net ;
1610 l_db.operating_acct_ytd := l_remaining.operating_acct_ytd ;
1611 l_db.deprn_period := l_remaining.deprn_period;
1612 l_db.deprn_ytd := l_factor * l_total.deprn_ytd; /* For EXPENSE accounting */
1613 l_db.deprn_reserve := l_remaining.deprn_reserve;
1614 l_db.deprn_reserve_backlog := l_remaining.deprn_reserve_backlog;
1615 l_db.general_fund_per := l_remaining.general_fund_per;
1616 l_db.general_fund_acc := l_remaining.general_fund_acc;
1617 l_db.last_reval_date := fp_reval_params.reval_asset_params.revaluation_date;
1618 l_db.current_reval_factor := l_reval_factor_curr;
1619 l_db.cumulative_reval_factor := l_reval_factor_cumm;
1620 l_db.active_flag := l_prorate_dists_tab ( idx ).active_flag;
1621
1622 l_dist_deprn_ytd := l_deprn_ytd_total * l_ytd_factor; /* YTD for igi_iac_det_balances */
1623
1624 verify_det_balances ( fp_det_balances => l_db
1625 , fp_prev_cum_factor => l_prev_factor_cumm
1626 , fp_curr_cum_factor => l_reval_factor_cumm
1627 );
1628 round_det_balances ( l_db ) ;
1629
1630 l_db_fa := l_total_fa; /* initalize */
1631
1632 l_db_fa.ytd_deprn := l_total_fa.ytd_deprn * l_ytd_factor; /* YTD for igi_iac_fa_deprn */
1633 l_db_fa.deprn_amount := l_remaining_fa.deprn_amount;
1634 l_db_fa.deprn_reserve := l_remaining_fa.deprn_reserve;
1635
1636 if not igi_iac_common_utils.iac_round ( x_amount => l_dist_deprn_ytd
1637 , x_book => fp_reval_params.reval_asset_params.book_type_code ) then
1638 return false;
1639 end if;
1640
1641 round_fa_figures ( fp_fa_hist => l_db_fa , fp_det_balances => l_db );
1642 end if;
1643
1644 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Display figures for '|| l_prorate_dists_tab( idx ).distribution_id);
1645 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'-------------------------------------------------------------');
1646 display_det_balances ( l_db ) ;
1647 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'-------------------------------------------------------------');
1648 /* now create the accounting entries using the mvmt info */
1649
1650 IF NOT IGI_IAC_REVAL_ACCOUNTING.create_iac_acctg
1651 ( fp_det_balances => l_db
1652 , fp_create_acctg_flag => fp_reval_params.reval_control.create_acctg_entries,
1653 p_event_id => null
1654 )
1655 THEN
1656 return false;
1657 END IF;
1658
1659 /* This code is not required for RECLASS now.
1660 if fp_reval_params.reval_control.transaction_type_code IN ('RECLASS') then
1661 l_prev_adj_id :=-1;
1662
1663 end if;
1664 End of commenting for RECLASS code */
1665
1666 /* now get the previous entry */
1667 if not get_prev_det_balances
1668 ( fp_adjustment_id => l_prev_adj_id
1669 , fp_distribution_id => l_db.distribution_id
1670 , fp_asset_id => l_db.asset_id
1671 , fp_book_type_code => l_db.book_type_code
1672 , fp_period_counter => l_db.period_counter
1673 , fp_transaction_sub_type => fp_reval_params.reval_control.transaction_sub_type
1674 , fp_det_balances => l_prev_db
1675 )
1676 then
1677 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'+unable to get previous entry from det balances');
1678 return false;
1679 end if;
1680
1681
1682 /* grab the ytd value of the prev entry */
1683 l_old_deprn_ytd := nvl(l_prev_db.deprn_ytd,0);
1684 l_old_gen_fund := nvl(l_prev_db.general_fund_acc,0);
1685
1686
1687 l_prev_db.deprn_ytd := l_dist_deprn_ytd; /* YTD proration*/
1688 /* now add the curr mvmt to the prev entry to give final figures */
1689 add_det_balances ( fp_det_balances => l_prev_db , fp_det_delta => l_db );
1690
1691 /* verify the net figures prior to inserting */
1692 verify_det_balances ( fp_det_balances => l_prev_db
1693 , fp_prev_cum_factor => l_prev_factor_cumm
1694 , fp_curr_cum_factor => l_reval_factor_cumm
1695 );
1696
1697 /* ensure that the depreciation expense is calculated properly */
1698 if nvl(l_prev_db.active_flag,'Y') = 'N' then
1699 l_prev_db.deprn_period := 0;
1700 l_prev_db.general_fund_per := 0;
1701 l_prev_db.general_fund_acc := 0;
1702 else
1703 if l_reval_factor_cumm = 1 then
1704 l_prev_db.deprn_period := 0;
1705 end if;
1706 l_prev_db.general_fund_per := l_prev_db.general_fund_acc - l_old_gen_fund;
1707 end if;
1708 /* now prev db is updated with the current movemnt, so create new record */
1709 if ( not create_det_balances_private ( fp_det_balances => l_prev_db ) )
1710 then
1711 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'error in table handler for det balances');
1712 return false;
1713 end if;
1714
1715 if ( not create_fa_figures_private ( fp_det_balances => l_prev_db
1716 , fp_fa_balances => l_db_fa
1717 ) )
1718 then
1719 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'error in table handler for fa det balances');
1720 return false;
1721 end if;
1722
1723 IF idx < l_prorate_dists_tab.LAST THEN
1724 idx := l_prorate_dists_tab.NEXT( idx );
1725 ELSE
1726 EXIT;
1727 END IF;
1728
1729 END LOOP;
1730
1731 if l_processed then
1732 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+successful creation of the det balances records.');
1733 else
1734 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'some/all distributions failed to be processed');
1735 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'+error creation of the det balances records.');
1736 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'end create_det_balances');
1737 return false;
1738 end if;
1739
1740 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'end create_det_balances');
1741 return true;
1742 exception when others then
1743 fp_reval_params := l_fp_reval_params;
1744 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
1745 return false;
1746 end;
1747
1748 function create_reval_rates
1749 ( fp_reval_params in out NOCOPY IGI_IAC_TYPES.iac_reval_params
1750 , fp_second_set in boolean )
1751 return boolean is
1752 l_reval_rates igi_iac_revaluation_rates%ROWTYPE;
1753 l_adjustment_id igi_iac_revaluation_rates.adjustment_id%TYPE;
1754 l_fp_reval_params IGI_IAC_TYPES.iac_reval_params;
1755 l_path varchar2(150);
1756 begin
1757 l_path := g_path||'create_reval_rates';
1758 -- for NOCOPY
1759 l_fp_reval_params := fp_reval_params;
1760
1761 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'begin create reval rates');
1762 -- modified by sekhar
1763 if (fp_reval_params.reval_control.transaction_type_code NOT IN ('RECLASS','ADDITION')) then
1764 if fp_reval_params.reval_control.revaluation_mode <> 'P' then
1765 return true;
1766 end if;
1767 end if;
1768 -- modified by sekhar
1769
1770 if fp_second_set then
1771 l_reval_rates := fp_reval_params.reval_curr_rate_info_next;
1772 l_adjustment_id := fp_Reval_params.reval_asset_params.second_set_adjustment_id;
1773 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+using next set for create reval rates');
1774 else
1775 l_adjustment_id := fp_Reval_params.reval_asset_params.first_set_adjustment_id;
1776 l_reval_rates := fp_reval_params.reval_curr_rate_info_first;
1777 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+using first set for create reval rates');
1778 end if;
1779
1780 if l_adjustment_id = 0 then
1781 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'+adjustments not generated yet.');
1782 return false;
1783 end if;
1784 /*
1785 -- change of logic.
1786 -- if the revaluation is still in preview, do not set latest record flag
1787 -- do this only in the run mode.
1788 */
1789 if (fp_reval_params.reval_control.transaction_type_code NOT IN ('RECLASS','ADDITION')) then
1790 if fp_reval_params.reval_control.revaluation_mode = 'P' then
1791 l_reval_rates.processed_flag := 'Y';
1792 l_reval_rates.latest_record := 'N';
1793 end if;
1794 end if;
1795
1796
1797
1798 insert into igi_iac_revaluation_Rates
1799 (
1800 asset_id
1801 ,book_type_code
1802 ,revaluation_id
1803 ,period_counter
1804 ,reval_type
1805 ,current_reval_factor
1806 ,cumulative_reval_factor
1807 ,processed_flag
1808 ,adjustment_id
1809 ,latest_record
1810 ,created_by
1811 ,creation_date
1812 ,last_update_login
1813 ,last_update_date
1814 ,last_updated_by
1815 )
1816 values
1817 (
1818 l_reval_rates.asset_id
1819 ,l_reval_rates.book_type_code
1820 ,l_reval_rates.revaluation_id
1821 ,l_reval_rates.period_counter
1822 ,l_reval_rates.reval_type
1823 ,l_reval_rates.current_reval_factor
1824 ,l_reval_rates.cumulative_reval_factor
1825 ,l_reval_rates.processed_flag
1826 ,l_adjustment_id
1827 ,l_reval_rates.latest_record
1828 ,l_reval_rates.created_by
1829 ,l_reval_rates.creation_date
1830 ,l_reval_rates.last_update_login
1831 ,l_reval_rates.last_update_date
1832 ,l_reval_rates.last_updated_by
1833 );
1834 if sql%found then
1835 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+inserted previous set reval rates');
1836 else
1837 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'+failed insert of reval rates');
1838 end if;
1839
1840 return true;
1841
1842 exception when others then
1843 fp_reval_params := l_fp_reval_params;
1844 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
1845 return FALSE;
1846 end;
1847
1848 function update_reval_rates ( fp_adjustment_id in number )
1849 return boolean is
1850 cursor c_txns is
1851 select asset_id, book_type_code
1852 from igi_iac_transaction_headers
1853 where adjustment_id = fp_adjustment_id
1854 ;
1855 l_path varchar2(150);
1856 begin
1857 l_path := g_path||'update_reval_rates';
1858 for l_txns in c_txns loop
1859
1860 update igi_iac_revaluation_rates
1861 set latest_record = 'N'
1862 where asset_id = l_txns.asset_id
1863 and book_type_code = l_txns.book_type_code
1864 and processed_flag = 'Y'
1865 and adjustment_id <> fp_adjustment_id
1866 ;
1867 if sql%found then
1868 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+ updated previous set reval rates');
1869 else
1870 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'+ failed update of reval rates');
1871 end if;
1872
1873 update igi_iac_revaluation_rates
1874 set latest_record = 'Y'
1875 where asset_id = l_txns.asset_id
1876 and book_type_code = l_txns.book_type_code
1877 and processed_flag = 'Y'
1878 and adjustment_id = fp_adjustment_id
1879 ;
1880 if sql%found then
1881 return true;
1882 else
1883 return false;
1884 end if;
1885
1886 end loop;
1887 exception when others then
1888 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
1889 return false;
1890 end;
1891
1892 function crud_iac_tables
1893 ( fp_reval_params in out NOCOPY IGI_IAC_TYPES.iac_reval_params
1894 , fp_second_set in boolean )
1895 return boolean is
1896
1897 l_fp_reval_params IGI_IAC_TYPES.iac_reval_params;
1898 l_path varchar2(150);
1899 begin
1900 l_path := g_path||'crud_iac_tables';
1901 -- for NOCOPY.
1902 l_fp_reval_params := fp_reval_params;
1903
1904 if not fp_reval_params.reval_control.crud_allowed then
1905 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+create, update or delete is not allowed.');
1906 return true;
1907 end if;
1908
1909 if nvl(fp_reval_params.reval_control.calling_program,'REVALUATION')
1910 in ('UPGRADE','IMPLEMENTATION')
1911 then
1912 if not igi_iac_reval_impl_crud.crud_iac_tables( fp_reval_params => fp_reval_params
1913 , fp_second_set => fp_second_set )
1914 then
1915 return false;
1916 end if;
1917 return true;
1918 end if;
1919
1920 if not create_txn_headers
1921 ( fp_reval_params => fp_reval_params
1922 , fp_second_set => fp_second_set
1923 )
1924 then
1925 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'+create txn headers Failed');
1926 return false;
1927 end if;
1928
1929 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Calling Create Det balances');
1930 begin
1931 if not create_det_balances
1932 ( fp_reval_params => fp_reval_params
1933 , fp_second_set => fp_second_set
1934 )
1935 then
1936 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'+create det balances');
1937 return false;
1938 end if;
1939 exception when others then
1940 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'error in det balances');
1941 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,sqlerrm);
1942 return false;
1943 end;
1944 if fp_reval_params.reval_control.modify_balances then
1945 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+updating asset balances');
1946 if not create_asset_balances
1947 ( fp_reval_params => fp_reval_params
1948 , fp_second_set => fp_second_set
1949 )
1950 then
1951 return false;
1952 end if;
1953 end if;
1954
1955 /* last step of the process */
1956
1957
1958 if not create_reval_rates
1959 ( fp_reval_params => fp_reval_params
1960 , fp_second_set => fp_second_set
1961 )
1962 then
1963 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'+failed insert into reval rates');
1964 return false;
1965 end if;
1966
1967 return true;
1968
1969 exception when others then
1970 fp_reval_params := l_fp_reval_params;
1971 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
1972 return false;
1973 end;
1974
1975 function reval_status_to_previewed
1976 ( fp_reval_id in out NOCOPY IGI_IAC_REVALUATIONS.REVALUATION_ID%TYPE )
1977 return boolean is
1978 pragma autonomous_transaction;
1979 begin
1980 update igi_iac_revaluations
1981 set status = IGI_IAC_TYPES.gc_previewed_status
1982 where revaluation_id = fp_reval_id
1983 ;
1984 if sql%found then
1985 do_commit;
1986 return true;
1987 else
1988 rollback;
1989 return false;
1990 end if;
1991 end;
1992
1993 function reval_status_to_failed_pre
1994 ( fp_reval_id in out NOCOPY IGI_IAC_REVALUATIONS.REVALUATION_ID%TYPE )
1995 return boolean is
1996 pragma autonomous_transaction;
1997 begin
1998 update igi_iac_revaluations
1999 set status = IGI_IAC_TYPES.gc_failedpre_status
2000 where revaluation_id = fp_reval_id
2001 ;
2002 if sql%found then
2003 do_commit;
2004 return true;
2005 else
2006 rollback;
2007 return false;
2008 end if;
2009 end;
2010
2011 function reval_status_to_completed
2012 ( fp_reval_id in out NOCOPY IGI_IAC_REVALUATIONS.REVALUATION_ID%TYPE,
2013 p_event_id in number)
2014 return boolean is
2015 pragma autonomous_transaction;
2016 begin
2017 if p_event_id is not null then
2018 update igi_iac_revaluations
2019 set status = IGI_IAC_TYPES.gc_completed_status,
2020 event_id = p_event_id
2021 where revaluation_id = fp_reval_id
2022 and event_id is null;
2023 else
2024 update igi_iac_revaluations
2025 set status = IGI_IAC_TYPES.gc_completed_status
2026 where revaluation_id = fp_reval_id;
2027 end if;
2028
2029 if sql%found then
2030 do_commit;
2031 return true;
2032 else
2033 rollback;
2034 return false;
2035 end if;
2036 end;
2037
2038 function reval_status_to_failed_run
2039 ( fp_reval_id in out NOCOPY IGI_IAC_REVALUATIONS.REVALUATION_ID%TYPE )
2040 return boolean is
2041 pragma autonomous_transaction;
2042 begin
2043 update igi_iac_revaluations
2044 set status = IGI_IAC_TYPES.gc_failedrun_status
2045 where revaluation_id = fp_reval_id
2046 ;
2047 if sql%found then
2048 do_commit;
2049 return true;
2050 else
2051 rollback;
2052 return false;
2053 end if;
2054 end;
2055
2056 function allow_transfer_to_gl
2057 ( fp_reval_id in IGI_IAC_REVALUATIONS.REVALUATION_ID%TYPE
2058 , fp_book_type_code in IGI_IAC_REVALUATIONS.BOOK_TYPE_CODE%TYPE
2059 , fp_asset_id in IGI_IAC_TRANSACTION_HEADERS.ASSET_ID%TYPE
2060 )
2061 return boolean is
2062 cursor c_check is
2063 select 'x'
2064 from igi_iac_reval_asset_rules
2065 where revaluation_factor <> 1
2066 and asset_id = fp_asset_id
2067 and book_type_code = fp_book_type_code
2068 and revaluation_id = fp_reval_id
2069 ;
2070 l_can_update boolean;
2071 l_path varchar2(150);
2072 begin
2073 l_path := g_path||'allow_transfer_to_gl';
2074 l_can_update := false;
2075 for l_check in c_check loop
2076 l_can_update := true;
2077 end loop;
2078 if l_can_update then
2079 update igi_iac_adjustments
2080 set transfer_to_gl_flag = 'Y'
2081 where adjustment_id in ( select adjustment_id
2082 from igi_iac_transaction_headers
2083 where mass_reference_id = fp_reval_id
2084 and book_type_code = fp_book_type_code
2085 and asset_id = fp_asset_id
2086 )
2087 and asset_id = fp_asset_id
2088 and book_type_code = fp_book_type_code
2089 and transfer_to_gl_flag = 'N'
2090 ;
2091 if sql%found then
2092 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Updated Transfer_to_gl_flag');
2093 else
2094 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'No records found to update transfer_to_gl_flag');
2095 end if;
2096 end if;
2097 return true;
2098 end;
2099
2100 --Added for SLA uptake. This function will update tables with event_id.
2101 function stamp_sla_event
2102 ( fp_reval_id in IGI_IAC_REVALUATIONS.REVALUATION_ID%TYPE
2103 , fp_book_type_code in IGI_IAC_REVALUATIONS.BOOK_TYPE_CODE%TYPE
2104 , fp_event_id in IGI_IAC_REVALUATIONS.EVENT_ID%TYPE
2105 )
2106 return boolean is
2107 cursor c_check is
2108 select 'x'
2109 from igi_iac_reval_asset_rules
2110 where revaluation_factor <> 1
2111 and book_type_code = fp_book_type_code
2112 and revaluation_id = fp_reval_id;
2113 l_can_update boolean;
2114 l_path varchar2(150);
2115 begin
2116 l_path := g_path||'stamp_sla_event';
2117 l_can_update := false;
2118 for l_check in c_check loop
2119 l_can_update := true;
2120 end loop;
2121 if l_can_update then
2122 update igi_iac_adjustments
2123 set event_id = fp_event_id
2124 where adjustment_id in ( select adjustment_id
2125 from igi_iac_transaction_headers
2126 where mass_reference_id = fp_reval_id
2127 and book_type_code = fp_book_type_code
2128 )
2129 and book_type_code = fp_book_type_code
2130 and transfer_to_gl_flag = 'Y';
2131
2132 if sql%found then
2133 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'stamped event_id in igi_iac_adjustments');
2134 else
2135 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'No records found to stamp event_id in igi_iac_adjustments');
2136 end if;
2137
2138 update igi_iac_transaction_headers
2139 set event_id = fp_event_id
2140 where mass_reference_id = fp_reval_id
2141 and book_type_code = fp_book_type_code;
2142
2143 if sql%found then
2144 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'stamped event_id in igi_iac_transaction_headers');
2145 else
2146 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'No records found to stamp event_id in igi_iac_transaction_headers');
2147 end if;
2148 end if;
2149 return true;
2150 end;
2151 --Added for SLA uptake. This function will update tables with event_id.
2152
2153 function adjustment_status_to_run
2154 ( fp_reval_id in IGI_IAC_REVALUATIONS.REVALUATION_ID%TYPE
2155 , fp_asset_id in IGI_IAC_TRANSACTION_HEADERS.ASSET_ID%TYPE
2156 )
2157 return boolean is
2158 begin
2159 update igi_iac_transaction_headers
2160 set adjustment_status = 'RUN'
2161 where mass_Reference_id = fp_reval_id
2162 and transaction_type_code = 'REVALUATION'
2163 and asset_id = fp_asset_id
2164 and adjustment_status = 'PREVIEW'
2165 ;
2166 if sql%found then
2167 return true;
2168 else
2169 return false;
2170 end if;
2171 end;
2172
2173 function adjustment_status_to_obsolete
2174 ( fp_reval_id in IGI_IAC_REVALUATIONS.REVALUATION_ID%TYPE
2175 , fp_asset_id in IGI_IAC_TRANSACTION_HEADERS.ASSET_ID%TYPE
2176 )
2177 return boolean is
2178 begin
2179 update igi_iac_transaction_headers
2180 set adjustment_status = 'OBSOLETE'
2181 where mass_Reference_id = fp_reval_id
2182 and transaction_type_code = 'REVALUATION'
2183 and asset_id = fp_asset_id
2184 and adjustment_status = 'PREVIEW'
2185 ;
2186 if sql%found then
2187 return true;
2188 else
2189 return false;
2190 end if;
2191 end;
2192
2193 function update_balances
2194 ( fp_reval_id in IGI_IAC_REVALUATIONS.REVALUATION_ID%TYPE
2195 , fp_asset_id in IGI_IAC_TRANSACTION_HEADERS.ASSET_ID%TYPE
2196 , fp_period_counter in IGI_IAC_TRANSACTION_HEADERS.PERIOD_COUNTER%TYPE
2197 , fp_book_type_code in IGI_IAC_TRANSACTION_HEADERS.BOOK_TYPE_CODE%TYPE
2198 )
2199 return boolean is
2200
2201 l_bal_net_book_value IGI_IAC_ASSET_BALANCES.NET_BOOK_VALUE%TYPE;
2202 l_bal_adjusted_cost IGI_IAC_ASSET_BALANCES.adjusted_cost%TYPE;
2203 l_bal_operating_acct IGI_IAC_ASSET_BALANCES.operating_acct%TYPE;
2204 l_bal_reval_reserve IGI_IAC_ASSET_BALANCES.reval_reserve%TYPE;
2205 l_bal_deprn_reserve IGI_IAC_ASSET_BALANCES.deprn_reserve%TYPE;
2206 l_bal_deprn_amount IGI_IAC_ASSET_BALANCES.deprn_amount%TYPE;
2207 l_bal_backlog_deprn_reserve IGI_IAC_ASSET_BALANCES.backlog_deprn_reserve%TYPE;
2208 l_bal_general_fund IGI_IAC_ASSET_BALANCES.general_fund%TYPE;
2209 l_cumm_reval_factor IGI_IAC_ASSET_BALANCES.cumulative_reval_factor%TYPE;
2210 l_reval_factor IGI_IAC_ASSET_BALANCES.current_reval_factor%TYPE;
2211 l_reval_date date;
2212 l_output igi_iac_types.iac_reval_output_asset;
2213
2214 cursor c_asset_bal is
2215 select
2216 nvl(net_book_value,0) net_book_value
2217 ,nvl(adjusted_cost,0) adjusted_cost
2218 ,nvl(operating_acct,0) operating_acct
2219 ,nvl(reval_reserve,0) reval_reserve
2220 ,nvl(deprn_amount,0) deprn_amount
2221 ,nvl(deprn_reserve,0) deprn_reserve
2222 ,nvl(backlog_deprn_reserve,0) backlog_deprn_reserve
2223 ,nvl(general_fund,0) general_fund
2224 ,last_reval_date
2225 ,current_reval_factor
2226 ,cumulative_reval_factor
2227 from igi_iac_asset_balances
2228 where asset_id = fp_asset_id
2229 and book_type_code = fp_book_type_code
2230 and period_counter = ( select max(period_counter)
2231 from igi_iac_asset_balances
2232 where asset_id = fp_asset_id
2233 and book_type_code = fp_book_type_code
2234 and period_counter <= fp_period_counter )
2235 ;
2236
2237 cursor c_asset_det is
2238 select
2239 nvl(sum(net_book_value),0) net_book_value
2240 ,nvl(sum(adjustment_cost),0) adjusted_cost
2241 ,nvl(sum(operating_acct_net),0) operating_acct
2242 ,nvl(sum(reval_reserve_net),0) reval_reserve
2243 ,nvl(sum(deprn_period),0) deprn_amount
2244 ,nvl(sum(deprn_reserve),0) deprn_reserve
2245 ,nvl(sum(deprn_reserve_backlog),0) backlog_deprn_reserve
2246 ,nvl(sum(nvl(reval_reserve_gen_fund,0)),0) general_fund
2247 from igi_iac_det_balances
2248 where asset_id = fp_asset_id
2249 and book_type_code = fp_book_type_code
2250 and period_counter = fp_period_counter
2251 and adjustment_id in ( select adjustment_id
2252 from igi_iac_transaction_headers
2253 where asset_id = fp_asset_id
2254 and period_counter = fp_period_counter
2255 and transaction_type_code = 'REVALUATION'
2256 and mass_reference_id = fp_reval_id
2257 and adjustment_id_out is null
2258 )
2259 ;
2260
2261 cursor c_reval_info is
2262 select iar.revaluation_id, iar.revaluation_date
2263 , iirar.revaluation_factor
2264 from igi_iac_revaluations iar
2265 , igi_iac_reval_asset_rules iirar
2266 where iar.revaluation_id = fp_reval_id
2267 and iirar.revaluation_id = fp_reval_id
2268 and iirar.asset_id = fp_asset_id
2269 and iirar.book_type_code = iar.book_type_code
2270 ;
2271
2272 l_path varchar2(150);
2273
2274 begin
2275 l_path := g_path||'update_balances';
2276 -- 1. Get the old balance from igi_iac_asset_balances
2277 l_bal_net_book_value := 0;
2278 l_bal_adjusted_cost := 0;
2279 l_bal_operating_acct := 0;
2280 l_bal_reval_reserve := 0;
2281 l_bal_deprn_reserve := 0;
2282 l_bal_deprn_amount := 0;
2283 l_bal_backlog_deprn_reserve := 0;
2284 l_bal_general_fund := 0;
2285 l_cumm_reval_factor := 1;
2286
2287 for l_bal in c_asset_bal loop
2288 l_cumm_reval_factor := l_bal.cumulative_reval_factor;
2289 end loop;
2290
2291 -- 2. Get the sum from igi_iac_det_balances
2292 for l_det in c_asset_det loop
2293 -- 3. add (1) and (2) to get the final result.
2294 l_bal_net_book_value := l_bal_net_book_value + l_det.net_book_value;
2295 l_bal_adjusted_cost := l_bal_adjusted_cost + l_det.adjusted_cost;
2296 l_bal_operating_acct := l_bal_operating_acct + l_det.operating_acct;
2297 l_bal_reval_reserve := l_bal_reval_reserve + l_det.reval_reserve;
2298 l_bal_deprn_reserve := l_bal_deprn_reserve + l_det.deprn_reserve;
2299 l_bal_deprn_amount := l_bal_deprn_amount + l_det.deprn_amount;
2300 l_bal_backlog_deprn_reserve := l_bal_backlog_deprn_reserve + l_det.backlog_deprn_reserve;
2301 l_bal_general_fund := l_bal_general_fund + l_det.general_fund;
2302
2303 end loop;
2304
2305 for l_info in c_reval_info loop
2306 l_reval_date := l_info.revaluation_date;
2307 l_reval_factor := l_info.revaluation_factor;
2308 l_cumm_reval_factor := l_cumm_reval_factor * l_info.revaluation_factor;
2309 end loop;
2310
2311
2312 begin
2313 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'begin create_asset_balances');
2314
2315 l_output.asset_id := fp_asset_id;
2316 l_output.book_type_code := fp_book_type_code;
2317 l_output.period_counter := fp_period_counter;
2318 l_output.net_book_value := l_bal_net_book_value;
2319 l_output.adjusted_cost := l_bal_adjusted_cost;
2320 l_output.operating_acct := l_bal_operating_acct;
2321 l_output.reval_reserve := l_bal_reval_reserve;
2322 l_output.deprn_amount := l_bal_deprn_amount;
2323 l_output.deprn_reserve := l_bal_deprn_reserve;
2324 l_output.backlog_deprn_reserve := l_bal_backlog_deprn_reserve;
2325 l_output.general_fund := l_bal_general_fund;
2326 l_output.last_reval_date := l_reval_date;
2327 l_output.current_reval_factor := l_reval_factor;
2328 l_output.cumulative_reval_factor := l_cumm_reval_factor;
2329
2330 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'insert/update first record');
2331 create_balance (pp_period_counter => fp_period_counter
2332 , pp_reval_output_asset => l_output
2333 ) ;
2334 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'insert/update second record');
2335 create_balance (pp_period_counter => fp_period_counter+1
2336 , pp_reval_output_asset => l_output
2337 ) ;
2338 end;
2339 return true;
2340
2341 exception when others then
2342 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
2343 return false;
2344 end;
2345 BEGIN
2346 --===========================FND_LOG.START=====================================
2347 g_state_level := FND_LOG.LEVEL_STATEMENT;
2348 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
2349 g_event_level := FND_LOG.LEVEL_EVENT;
2350 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
2351 g_error_level := FND_LOG.LEVEL_ERROR;
2352 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
2353 g_path := 'IGI.PLSQL.igiiardb.IGI_IAC_REVAL_CRUD.';
2354 --===========================FND_LOG.END=======================================
2355 END;
2356