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