[Home] [Help]
PACKAGE BODY: APPS.IGI_IAC_RECLASS_PKG
Source
1 PACKAGE BODY IGI_IAC_RECLASS_PKG AS
2 /* $Header: igiiarlb.pls 120.27.12010000.2 2010/06/25 06:48:13 schakkin ship $ */
3 -- A Global variable to set the debug mode
4 debug_reclass BOOLEAN;
5 l_output_dir varchar2(255);
6 l_debug_log varchar2(255);
7 l_debug_output varchar2(255);
8 l_debug_print Boolean;
9
10 -- global vlaue for allow reval index and prof flag
11 ALLOW_INDEX_REVAL_FLAG BOOLEAN;
12 ALLOW_PROF_REVAL_FLAG BOOLEAN;
13 SAME_PRICE_INDEX_FLAG BOOLEAN;
14 DIFF_PRICE_INDEX_FLAG BOOLEAN;
15
16 l_trans_rec FA_API_TYPES.trans_rec_type;
17 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
18 l_asset_cat_rec_old FA_API_TYPES.asset_cat_rec_type;
19 l_asset_cat_rec_new FA_API_TYPES.asset_cat_rec_type;
20 l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
21 l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
22 l_calling_function varchar2(250);
23 l_deprn_reserve_amount NUmber;
24 chr_newline VARCHAR2(8);
25 l_deprn_ytd number; --- for ytd previous old active distributions
26
27 --===========================FND_LOG.START=====================================
28
29 g_state_level NUMBER;
30 g_proc_level NUMBER;
31 g_event_level NUMBER;
32 g_excep_level NUMBER;
33 g_error_level NUMBER;
34 g_unexp_level NUMBER;
35 g_path VARCHAR2(100);
36
37 --===========================FND_LOG.END=====================================
38
39 /*
40 * BOOLTOCHAR
41 *
42 * A utility function to convert boolean values to char to print in
43 * debug statements
44 */
45 FUNCTION BOOLTOCHAR(value IN BOOLEAN) RETURN VARCHAR2
46 IS
47 BEGIN
48 IF (value) THEN
49 RETURN 'TRUE';
50 ELSE
51 RETURN 'FALSE';
52 END IF;
53 END BOOLTOCHAR;
54
55 PROCEDURE do_round ( p_amount in out NOCOPY number, p_book_type_code in varchar2) is
56 l_path varchar2(150) := g_path||'do_round(p_amount,p_book_type_code)';
57 l_amount number := p_amount;
58 l_amount_old number := p_amount;
59 --l_path varchar2(150) := g_path||'do_round';
60 begin
61 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'--- Inside Round() ---');
62 IF IGI_IAC_COMMON_UTILS.Iac_Round(X_Amount => l_amount, X_Book => p_book_type_code)
63 THEN
64 p_amount := l_amount;
65 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'IGI_IAC_COMMON_UTILS.Iac_Round is TRUE');
66 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_amount = '||p_amount);
67 ELSE
68 p_amount := round( l_amount, 2);
69 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'IGI_IAC_COMMON_UTILS.Iac_Round is FALSE');
70 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_amount = '||p_amount);
71 END IF;
72 exception when others then
73 p_amount := l_amount_old;
74 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
75 Raise;
76 END;
77
78 -- ======================================================================
79 -- CATEGORY VALIDATION
80 -- ======================================================================
81 FUNCTION Do_validate_category
82 RETURN boolean is
83 l_return_value boolean;
84 l_original_category_id number;
85 l_new_category_id number;
86 l_old_cap_flag fa_categories.capitalize_flag%type;
87 l_old_cat_type fa_categories.category_type%type;
88 l_new_cap_flag fa_categories.capitalize_flag%type;
89 l_new_cat_type fa_categories.category_type%type;
90 l_old_index_reval_flag igi_iac_category_books.allow_indexed_reval_flag%Type;
91 l_old_prof_reval_flag igi_iac_category_books.allow_prof_reval_flag%Type;
92 l_new_index_reval_flag igi_iac_category_books.allow_indexed_reval_flag%Type;
93 l_new_prof_reval_flag igi_iac_category_books.allow_prof_reval_flag%Type;
94 l_path_name VARCHAR2(150);
95
96
97 -- cursor to validate category linked to iac book
98 Cursor get_category_id(c_category_id Number)is
99 SELECT category_id,allow_indexed_reval_flag,allow_prof_reval_flag
100 FROM igi_iac_category_books
101 WHERE book_type_code = l_asset_hdr_rec.book_type_code
102 AND category_id = c_category_id;
103
104 --cursor to get the category capitalized
105 Cursor get_cat_cap_flag(c_category_id Number) is
106 SELECT capitalize_flag,category_type
107 FROM fa_categories
108 WHERE category_id = c_category_id;
109
110
111 BEGIN
112 l_path_name := g_path||'do_validate_category';
113 l_return_value := False;
114 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
115 p_full_path => l_path_name,
116 p_string => ' + Enter validate category');
117
118 -- check if same category or null category
119 IF ((l_asset_cat_rec_old.category_id = l_asset_cat_rec_new.category_id)or
120 (l_asset_cat_rec_old.category_id is null or l_asset_cat_rec_new.category_id is null))
121 THEN
122 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
123 p_full_path => l_path_name,
124 p_string => ' + Same category old and new or null value');
125
126 Return false;
127 End if;
128 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
129 p_full_path => l_path_name,
130 p_string => ' + different categories old ' || l_asset_cat_rec_old.category_id
131 || 'and new' ||l_asset_cat_rec_new.category_id );
132
133 /**
134 --validate if both categories are added to IAC book controls
135 **/
136 -- A record should exisit in iac category books for both categories for same book_typec_code
137 -- check for original category id
138 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
139 p_full_path => l_path_name,
140 p_string => ' + orinignal category id iac book test ..'||l_asset_cat_rec_old.category_id);
141
142 Open get_category_id(l_asset_cat_rec_old.category_id);
143 Fetch get_category_id into l_original_category_id,
144 l_old_index_reval_flag,
145 l_old_prof_reval_flag ;
146 IF NOT get_category_id%FOUND THEN
147 -- Raise error message that category is not exisiting in iac book
148 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
149 p_full_path => l_path_name,
150 p_string => ' +error message that category is not exisiting in iac book '||l_asset_cat_rec_old.category_id);
151
152 Close get_category_id;
153 Return false;
154 END IF;
155 Close get_category_id;
156
157 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
158 p_full_path => l_path_name,
159 p_string => ' + Original category exisits in iac book '||l_asset_cat_rec_old.category_id);
160
161 Open get_category_id(l_asset_cat_rec_new.category_id);
162 Fetch get_category_id into l_original_category_id,
163 l_new_index_reval_flag,
164 l_new_prof_reval_flag ;
165 IF NOT get_category_id%FOUND THEN
166 -- Raise error message that category is not exisiting in iac book
167 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
168 p_full_path => l_path_name,
169 p_string => ' +error message that category is not exisiting in iac book '||l_asset_cat_rec_new.category_id);
170 Close get_category_id;
171 Return false;
172 END IF;
173 Close get_category_id;
174
175 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
176 p_full_path => l_path_name,
177 p_string => ' + New category exisits in iac book '||l_asset_cat_rec_old.category_id);
178
179 /**
180 --validate if both categories are captitalized
181 **/
182 -- for the old category
183 open get_cat_cap_flag(l_asset_cat_rec_old.category_id);
184 Fetch get_cat_cap_flag into l_old_cap_flag,
185 l_old_cat_type;
186 Close get_cat_cap_flag;
187 -- for new category
188 open get_cat_cap_flag(l_asset_cat_rec_new.category_id);
189 Fetch get_cat_cap_flag into l_new_cap_flag,
190 l_new_cat_type;
191 Close get_cat_cap_flag;
192 --check the category flags
193 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
194 p_full_path => l_path_name,
195 p_string => ' + check for category captilized falgs');
196
197 IF (l_old_cap_flag = 'YES') THEN
198 IF (l_old_cap_flag <> l_new_cap_flag) THEN
199 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
200 p_full_path => l_path_name,
201 p_string => ' + both the categories not captilized falgs');
202
203 Return False;
204 End IF;
205 END IF;
206
207 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
208 p_full_path => l_path_name,
209 p_string => ' + check for category captilized falgs success');
210
211 /**
212 --Set the indexed and profesional flag for revaluation
213 **/
214 --set the allow index reval flag
215 IF l_new_index_reval_flag ='Y' THEN
216 allow_index_reval_flag := True;
217 ELSE
218 allow_index_reval_flag := False;
219 END IF;
220 --set the prof reval flag
221 IF l_new_prof_reval_flag ='Y' THEN
222 allow_prof_reval_flag := True;
223 ELSE
224 allow_prof_reval_flag := False;
225 END IF;
226
227 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
228 p_full_path => l_path_name,
229 p_string => ' + set the indexed and prof flags ');
230 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
231 p_full_path => l_path_name,
232 p_string => ' + set the indexed flag ' || l_new_index_reval_flag );
233 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
234 p_full_path => l_path_name,
235 p_string => ' + set the prof flag ' ||l_new_prof_reval_flag );
236 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
237 p_full_path => l_path_name,
238 p_string => ' + EXIT Validate category' );
239
240 Return True;
241
242 END; -- do validate category
243
244
245 -- ======================================================================
246 -- ASSET VALIDATION
247 -- ======================================================================
248 FUNCTION Do_Validate_Asset
249 RETURN boolean is
250
251 -- cursor for to check asset type
252 /* Cursor to find out NOCOPY if the asset is revalued atleast once */
253
254 CURSOR c_asset_revalued(c_asset_id IGI_IAC_ASSET_BALANCES.asset_id%type) IS
255 SELECT 'X'
256 FROM igi_iac_asset_balances
257 WHERE asset_id=l_asset_hdr_rec.asset_id
258 AND book_type_code = l_asset_hdr_rec.book_type_code;
259
260 l_asset_revalued c_asset_revalued%rowtype;
261 l_path_name VARCHAR2(150);
262
263 BEGIN
264 l_path_name := g_path||'do_validate_asset';
265 -- check if revaluation atleast once
266 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
267 p_full_path => l_path_name,
268 p_string => ' + check if revaluation atleast once' );
269 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
270 p_full_path => l_path_name,
271 p_string => ' + check if revaluation atleast once ...' || l_asset_hdr_rec.asset_id );
272 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
273 p_full_path => l_path_name,
274 p_string => ' + check if revaluation atleast once ...' || l_asset_hdr_rec.book_type_code);
275
276 /* Check IF Asset is revalued at least once */
277 open c_asset_revalued(l_asset_hdr_rec.asset_id);
278 fetch c_asset_revalued into l_asset_revalued;
279 IF c_asset_revalued%NOTFOUND THEN
280 close c_asset_revalued;
281 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
282 p_full_path => l_path_name,
283 p_string => '+ No IAC revaluation atleast once');
284
285 RETURN(FALSE);
286 END IF;
287
288 close c_asset_revalued;
289
290 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
291 p_full_path => l_path_name,
292 p_string => ' + check if revaluation atleast once success' );
293
294 IF NOT (l_asset_type_rec.asset_type in ('CAPITALIZED') ) THEN
295 -- ERROR MESSAGE
296 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
297 p_full_path => l_path_name,
298 p_string => ' + NOT CAPITALIZED ASSET' );
299 return FALSE;
300 END IF;
301 Return True;
302 END; -- do validate asset
303
304 /*
305 +===============================================================================+
306 | Procedure To Insert Data into IGI_IAC_TRANSACTION_HEADERS |
307 ================================================================================+
308 */
309 PROCEDURE Insert_data_trans_hdr(p_adjustment_id in out NOCOPY IGI_IAC_TRANSACTION_HEADERS.adjustment_id%type,
310 p_transaction_header_id in IGI_IAC_TRANSACTION_HEADERS.transaction_header_id%type,
311 p_adjustment_id_out in IGI_IAC_TRANSACTION_HEADERS.adjustment_id_out%type,
312 p_transaction_type_code in IGI_IAC_TRANSACTION_HEADERS.transaction_type_code%type,
313 p_transaction_date_entered in IGI_IAC_TRANSACTION_HEADERS.transaction_date_entered%type,
314 p_mass_reference_id in IGI_IAC_TRANSACTION_HEADERS.mass_reference_id%type,
315 p_book_type_code in IGI_IAC_TRANSACTION_HEADERS.book_type_code%type,
316 p_asset_id in IGI_IAC_TRANSACTION_HEADERS.asset_id%type,
317 p_revaluation_flag in IGI_IAC_TRANSACTION_HEADERS.revaluation_type_flag%type,
318 p_adjustment_status in IGI_IAC_TRANSACTION_HEADERS.adjustment_status%type,
319 p_category_id in IGI_IAC_TRANSACTION_HEADERS.category_id%type,
320 p_period_counter in IGI_IAC_TRANSACTION_HEADERS.period_counter%type,
321 p_event_id in number
322 ) IS
323
324
325
326
327 l_rowid VARCHAR2(25);
328 l_mesg VARCHAR2(500);
329 l_path_name VARCHAR2(150);
330
331 BEGIN
332 l_path_name := g_path||'insert_data_trans_hdr';
333
334
335 /* Call the TBH for inserting data into IGI_IAC_TRANSACTION_HEADERS */
336
337 IGI_IAC_TRANS_HEADERS_PKG.insert_row(
338 x_rowid =>l_rowid,
339 x_adjustment_id =>p_adjustment_id ,
340 x_transaction_header_id =>p_transaction_header_id ,
341 x_adjustment_id_out =>p_adjustment_id_out,
342 x_transaction_type_code =>p_transaction_type_code ,
343 x_transaction_date_entered =>p_transaction_date_entered ,
344 x_mass_refrence_id =>p_mass_reference_id,
345 x_transaction_sub_type =>null,
346 x_book_type_code =>p_book_type_code,
347 x_asset_id =>p_asset_id,
348 x_category_id =>p_category_id,
349 x_adj_deprn_start_date =>null,
350 x_revaluation_type_flag =>p_revaluation_flag,
351 x_adjustment_status =>p_adjustment_status,
352 x_period_counter =>p_period_counter,
353 x_mode =>'R',
354 x_event_id =>p_event_id
355 );
356
357
358 EXCEPTION
359 WHEN OTHERS THEN
360 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
361
362 l_mesg:=SQLERRM;
363 FA_SRVR_MSG.Add_Message(
364 Calling_FN => l_calling_function ,
365 Name => 'IGI_IAC_EXCEPTION',
366 TOKEN1 => 'PACKAGE',
367 VALUE1 => 'RECLASS',
368 TOKEN2 => 'ERROR_MESSAGE',
369 VALUE2 => l_mesg,
370 APPLICATION => 'IGI');
371
372 rollback;
373
374 END insert_data_trans_hdr;
375
376
377
378 /*
379 +===============================================================================+
380 | Procedure to insert data into IGI_IAC_DET_BALANCES |
381 ================================================================================+
382 */
383
384 PROCEDURE insert_data_det(p_adjustment_id in IGI_IAC_DET_BALANCES.adjustment_id%type,
385 p_asset_id in IGI_IAC_DET_BALANCES.asset_id%type,
386 p_distribution_id in IGI_IAC_DET_BALANCES.distribution_id%type,
387 p_period_counter in IGI_IAC_DET_BALANCES.period_counter%type,
388 p_book_type_code in IGI_IAC_DET_BALANCES.book_type_code%type,
389 p_adjusted_cost in IGI_IAC_DET_BALANCES.adjustment_cost%type,
390 p_net_book_value in IGI_IAC_DET_BALANCES.net_book_value%type,
391 p_reval_reserve in IGI_IAC_DET_BALANCES.reval_reserve_cost%type,
392 p_reval_reserve_gen_fund in IGI_IAC_DET_BALANCES.reval_reserve_gen_fund%type,
393 p_reval_reserve_backlog in IGI_IAC_DET_BALANCES.reval_reserve_backlog%type,
394 p_reval_reserve_net in IGI_IAC_DET_BALANCES.reval_reserve_net%type,
395 p_op_acct in IGI_IAC_DET_BALANCES.operating_acct_cost%type,
396 p_op_acct_net in IGI_IAC_DET_BALANCES.operating_acct_net%type,
397 p_deprn_reserve in IGI_IAC_DET_BALANCES.deprn_reserve%type,
398 p_deprn_reserve_backlog in IGI_IAC_DET_BALANCES.deprn_reserve_backlog%type,
399 p_deprn_ytd in IGI_IAC_DET_BALANCES.deprn_ytd%type,
400 p_deprn_period in IGI_IAC_DET_BALANCES.deprn_period%type,
401 p_gen_fund_acc in IGI_IAC_DET_BALANCES.general_fund_acc%type,
402 p_gen_fund_per in IGI_IAC_DET_BALANCES.general_fund_acc%type,
403 p_current_reval_factor in IGI_IAC_DET_BALANCES.current_reval_factor%type,
404 p_cumulative_reval_factor in IGI_IAC_DET_BALANCES.cumulative_reval_factor%type,
405 p_reval_flag in IGI_IAC_DET_BALANCES.active_flag%type,
406 p_op_acct_ytd in IGI_IAC_DET_BALANCES.operating_acct_ytd%type,
407 p_operating_acct_backlog in IGI_IAC_DET_BALANCES.operating_acct_backlog%type,
408 p_last_reval_date in IGI_IAC_DET_BALANCES.last_reval_date%type
409 ) IS
410
411 l_rowid VARCHAR2(25);
412 l_mesg VARCHAR2(500);
413 l_path_name VARCHAR2(150);
414 BEGIN
415 l_path_name := g_path||'insert_data_det';
416
417 /* Call to TBH for insert into IGI_IAC_DET_BALANCES */
418 IGI_IAC_DET_BALANCES_PKG.insert_row(
419 x_rowid =>l_rowid,
420 x_adjustment_id =>p_adjustment_id,
421 x_asset_id =>p_asset_id,
422 x_distribution_id =>p_distribution_id,
423 x_book_type_code =>p_book_type_code,
424 x_period_counter =>p_period_counter,
425 x_adjustment_cost =>p_adjusted_cost,
426 x_net_book_value =>p_net_book_value,
427 x_reval_reserve_cost =>p_reval_reserve,
428 x_reval_reserve_backlog =>p_reval_reserve_backlog,
429 x_reval_reserve_gen_fund =>p_reval_reserve_gen_fund,
430 -- Bug 2767992 Sekhar Modified for reval reserve net
431 x_reval_reserve_net =>p_reval_reserve_net,
432 x_operating_acct_cost =>p_op_acct,
433 x_operating_acct_backlog =>p_operating_acct_backlog,
434 x_operating_acct_net =>p_op_acct_net,
435 x_operating_acct_ytd =>p_op_acct_ytd,
436 x_deprn_period =>p_deprn_period,
437 x_deprn_ytd =>p_deprn_ytd,
438 x_deprn_reserve =>p_deprn_reserve,
439 x_deprn_reserve_backlog =>p_deprn_reserve_backlog,
440 x_general_fund_per =>p_gen_fund_per,
441 x_general_fund_acc =>p_gen_fund_acc,
442 x_last_reval_date =>p_last_reval_date,
443 x_current_reval_factor =>p_current_reval_factor,
444 x_cumulative_reval_factor =>p_cumulative_reval_factor,
445 x_active_flag =>p_reval_flag,
446 x_mode =>'R'
447 );
448
449 EXCEPTION
450 WHEN OTHERS THEN
451 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
452
453 l_mesg:=SQLERRM;
454 FA_SRVR_MSG.Add_Message(
455 Calling_FN => l_calling_function ,
456 Name => 'IGI_IAC_EXCEPTION',
457 TOKEN1 => 'PACKAGE',
458 VALUE1 => 'RECLASS',
459 TOKEN2 => 'ERROR_MESSAGE',
460 VALUE2 => l_mesg,
461 APPLICATION => 'IGI');
462
463 rollback;
464 END insert_data_det;
465
466 /*
467 +===============================================================================+
468 | Procedure to prorate the amounts based on the units assigned |
469 ================================================================================+
470 */
471 PROCEDURE Prorate_amount_for_dist(P_dist_id in FA_DISTRIBUTION_HISTORY.DISTRIBUTION_ID%type,
472 P_units_dist in number,
473 P_units_total in number,
474 P_reval_reserve in out NOCOPY number,
475 P_general_fund in out NOCOPY number,
476 P_backlog_deprn in out NOCOPY number,
477 P_deprn_reserve in out NOCOPY number,
478 P_adjusted_cost in out NOCOPY number,
479 P_net_book_value in out NOCOPY number,
480 P_deprn_per in out NOCOPY number,
481 P_op_acct in out NOCOPY number,
482 p_ytd_deprn in out NOCOPY number,
483 p_op_acct_ytd in out NOCOPY number,
484 p_event_id in number
485
486 ) IS
487
488 prorate_factor number;
489 l_mesg VARCHAR2(500);
490 l_path_name VARCHAR2(150);
491
492 BEGIN
493
494 l_path_name := g_path||'prorate_amount_for_dist';
495
496 /*Prorate the various amounts between for the given distribution*/
497
498 Prorate_factor := p_units_dist/P_units_total;
499 P_reval_reserve := P_reval_reserve* Prorate_factor ;
500 P_reval_reserve := round(P_reval_reserve,2);
501 P_general_fund := P_general_fund * Prorate_factor ;
502 P_general_fund := round(P_general_fund,2);
503 P_backlog_deprn := P_backlog_deprn* Prorate_factor ;
504 P_backlog_deprn := round(P_backlog_deprn,2);
505 P_deprn_reserve := P_deprn_reserve* Prorate_factor ;
506 P_deprn_reserve := round(P_deprn_reserve,2);
507 P_adjusted_cost := P_adjusted_cost* Prorate_factor ;
508 P_adjusted_cost := round(P_adjusted_cost,2);
509 P_net_book_value := P_net_book_value* Prorate_factor ;
510 P_net_book_value := round(P_net_book_value,2);
511 P_deprn_per := P_deprn_per* Prorate_factor;
512 P_deprn_per := round(P_deprn_per,2);
513 P_op_acct := p_op_acct*Prorate_factor;
514 P_op_acct := round(P_op_acct,2);
515 P_ytd_deprn := p_ytd_deprn*Prorate_factor;
516 P_ytd_deprn := round(P_ytd_deprn,2);
517 P_op_acct_ytd := p_op_acct_ytd*Prorate_factor;
518 P_op_acct_ytd := round(P_op_acct_ytd,2);
519
520 EXCEPTION
521 WHEN OTHERS THEN
522 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
523
524 l_mesg:=SQLERRM;
525 FA_SRVR_MSG.Add_Message(
526 Calling_FN => l_calling_function ,
527 Name => 'IGI_IAC_EXCEPTION',
528 TOKEN1 => 'PACKAGE',
529 VALUE1 => 'RECLASS',
530 TOKEN2 => 'ERROR_MESSAGE',
531 VALUE2 => l_mesg,
532 APPLICATION => 'IGI');
533
534 END prorate_amount_for_dist;
535
536
537 procedure create_acctg_entry ( l_ccid in number
538 , p_amount in number
539 , l_adjust_type in varchar2
540 , l_cr_dr_flag in varchar2
541 , l_set_of_books_id in number
542 , fp_det_balances in igi_iac_det_balances%rowtype
543 , l_adjust_offset_type in varchar2 Default Null
544 , l_Report_CCID in varchar2 Default Null
545 , p_event_id in number
546 )
547 is
548 l_rowid varchar2(30);
549 l_units_assigned number;
550 l_path_name VARCHAR2(150);
551 begin
552 l_rowid := null;
553 l_path_name := g_path||'create_acctg_entry';
554 if p_amount = 0 then
555 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
556 p_full_path => l_path_name,
557 p_string => '+amount is 0, accounting entries skipped');
558
559 return;
560 end if;
561
562 select units_assigned
563 into l_units_assigned
564 from fa_distribution_history
565 where book_type_code = fp_det_balances.book_type_code
566 and asset_id = fp_det_balances.asset_id
567 and distribution_id = fp_det_balances.distribution_id
568 ;
569
570 IGI_IAC_ADJUSTMENTS_PKG.insert_row (
571 x_rowid => l_rowid,
572 x_adjustment_id => fp_det_balances.adjustment_id,
573 x_book_type_code => fp_det_balances.book_type_code,
574 x_code_combination_id => l_ccid,
575 x_set_of_books_id => l_set_of_books_id,
576 x_dr_cr_flag => l_cr_dr_flag,
577 x_amount => p_amount,
578 x_adjustment_type => l_adjust_type,
579 x_transfer_to_gl_flag => 'Y',
580 x_units_assigned => l_units_assigned,
581 x_asset_id => fp_det_balances.asset_id,
582 x_distribution_id => fp_det_balances.distribution_id,
583 x_period_counter => fp_det_balances.period_counter,
584 x_adjustment_offset_type => l_adjust_offset_type,
585 x_report_ccid => l_report_ccid,
586 x_mode => 'R',
587 x_event_id => p_event_id
588 ) ;
589
590 end;
591
592 function create_iac_acctg
593 ( fp_det_balances in IGI_IAC_DET_BALANCES%ROWTYPE
594 , fp_create_acctg_flag in boolean
595 , fp_adjustement_type in Varchar2
596 , l_Adjust_offset_type in varchar2 Default Null
597 , l_Report_CCID in varchar2 Default Null
598 , p_event_id in number
599 )
600 return boolean is
601 l_rowid rowid;
602 l_sob_id number;
603 l_coa_id number;
604 l_currency varchar2(30);
605 l_precision number;
606 l_dr_ccid gl_code_combinations.code_combination_id%type;
607 l_cr_ccid gl_code_combinations.code_combination_id%type;
608 l_revl_rsv_ccid gl_code_combinations.code_combination_id%type;
609 l_blog_rsv_ccid gl_code_combinations.code_combination_id%TYPE;
610 l_op_exp_ccid gl_code_combinations.code_combination_id%TYPE;
611 l_gen_fund_ccid gl_code_combinations.code_combination_id%TYPE;
612 l_asset_cost_ccid gl_code_combinations.code_combination_id%TYPE;
613 l_deprn_rsv_ccid gl_code_combinations.code_combination_id%TYPE;
614 l_deprn_exp_ccid gl_code_combinations.code_combination_id%TYPE;
615 l_cr_dr_flag_cost varchar(2);
616 l_cr_dr_flag_reval_reserve varchar(2);
617 l_cr_dr_flag_gen_fund varchar(2);
618 l_cr_dr_flag_blog varchar(2);
619 l_cr_dr_flag_op varchar(2);
620 l_cr_dr_flag_exp varchar(2);
621 l_cr_dr_flag_reserve varchar(2);
622 l_path_name VARCHAR2(150);
623
624 procedure check_ccid ( p_ccid_desc in varchar2) is
625 l_path_name VARCHAR2(150);
626 begin
627 l_path_name := g_path||'check_ccid';
628 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
629 p_full_path => l_path_name,
630 p_string => '+acctg creation for '||p_ccid_desc||' failed');
631 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
632 p_full_path => l_path_name,
633 p_string => 'error create_iac_acctg');
634 end;
635
636 begin
637 l_path_name := g_path||'create_iac_acctg';
638
639 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
640 p_full_path => l_path_name,
641 p_string => 'begin create_iac_acctg');
642 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
643 p_full_path => l_path_name,
644 p_string => '+ Det Balances');
645 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
646 p_full_path => l_path_name,
647 p_string => '+ book type code'||fp_det_balances.book_type_code);
648 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
649 p_full_path => l_path_name,
650 p_string => '+ asset id'||fp_det_balances.asset_id);
651 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
652 p_full_path => l_path_name,
653 p_string => '+ distribution id '||fp_det_balances.distribution_id);
654 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
655 p_full_path => l_path_name,
656 p_string => '+ adjustment id '||fp_det_balances.adjustment_id);
657 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
658 p_full_path => l_path_name,
659 p_string => '+ reval reserve '||fp_det_balances.reval_reserve_cost);
660 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
661 p_full_path => l_path_name,
662 p_string => '+ reserve backlog '||fp_det_balances.reval_reserve_backlog);
663 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
664 p_full_path => l_path_name,
665 p_string => '+ gen fund'||fp_det_balances.reval_reserve_gen_fund);
666 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
667 p_full_path => l_path_name,
668 p_string => '+ acct cost'||fp_det_balances.operating_acct_cost);
669 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
670 p_full_path => l_path_name,
671 p_string => '+ op acct backlog'||fp_det_balances.operating_acct_backlog);
672 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
673 p_full_path => l_path_name,
674 p_string => '+ deprn ytd'||fp_det_balances.deprn_ytd);
675 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
676 p_full_path => l_path_name,
677 p_string => '+ rserv back deprn '||fp_det_balances.deprn_reserve_backlog);
678
679
680 if not fp_create_acctg_flag then
681 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
682 p_full_path => l_path_name,
683 p_string => '+acctg creation not allowed');
684 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
685 p_full_path => l_path_name,
686 p_string => 'end create_iac_acctg');
687
688 return true;
689 end if;
690
691 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
692 p_full_path => l_path_name,
693 p_string => '+acctg creation get gl information');
694
695 if not IGI_IAC_COMMON_UTILS.GET_BOOK_GL_INFO
696 ( X_BOOK_TYPE_CODE => fp_det_balances.book_type_code
697 , SET_OF_BOOKS_ID => l_sob_id
698 , CHART_OF_ACCOUNTS_ID => l_coa_id
699 , CURRENCY => l_currency
700 , PRECISION => l_precision
701 )
702 then
703 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
704 p_full_path => l_path_name,
705 p_string => '+acctg creation unable to get gl info');
706 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
707 p_full_path => l_path_name,
708 p_string => 'end create_iac_acctg');
709
710 return false;
711 end if;
712 --
713 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
714 p_full_path => l_path_name,
715 p_string => '+acctg creation get all accounts');
716 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
717 p_full_path => l_path_name,
718 p_string => '+distribution id '|| fp_det_balances.distribution_id );
719
720 IF NOT IGI_IAC_COMMON_UTILS.get_account_ccid
721 ( X_book_type_code => fp_det_balances.book_type_code
722 , X_asset_id => fp_det_balances.asset_id
723 , X_distribution_id => fp_det_balances.distribution_id
724 ,X_TRANSACTION_HEADER_ID => l_trans_rec.TRANSACTION_HEADER_ID
725 ,X_calling_function => 'RECLASS'
726 , X_account_type => 'REVAL_RESERVE_ACCT'
727 , account_ccid => l_revl_rsv_ccid
728 )
729 THEN
730 check_ccid ( 'reval reserve');
731 return false;
732 END IF;
733 IF NOT IGI_IAC_COMMON_UTILS.get_account_ccid
734 ( X_book_type_code => fp_det_balances.book_type_code
735 , X_asset_id => fp_det_balances.asset_id
736 , X_distribution_id => fp_det_balances.distribution_id
737 ,X_TRANSACTION_HEADER_ID => l_trans_rec.TRANSACTION_HEADER_ID
738 ,X_calling_function => 'RECLASS'
739 , X_account_type => 'BACKLOG_DEPRN_RSV_ACCT'
740 , account_ccid => l_blog_rsv_ccid
741 )
742 THEN
743 check_ccid ( 'backlog deprn reserve');
744 return false;
745 END IF;
746 IF NOT IGI_IAC_COMMON_UTILS.get_account_ccid
747 ( X_book_type_code => fp_det_balances.book_type_code
748 , X_asset_id => fp_det_balances.asset_id
749 , X_distribution_id => fp_det_balances.distribution_id
750 ,X_TRANSACTION_HEADER_ID => l_trans_rec.TRANSACTION_HEADER_ID
751 ,X_calling_function => 'RECLASS'
752 , X_account_type => 'OPERATING_EXPENSE_ACCT'
753 , account_ccid => l_op_exp_ccid
754 )
755 THEN
756 check_ccid ( 'operating account');
757 return false;
758 END IF;
759 IF NOT IGI_IAC_COMMON_UTILS.get_account_ccid
760 ( X_book_type_code => fp_det_balances.book_type_code
761 , X_asset_id => fp_det_balances.asset_id
762 , X_distribution_id => fp_det_balances.distribution_id
763 ,X_TRANSACTION_HEADER_ID => l_trans_rec.TRANSACTION_HEADER_ID
764 ,X_calling_function => 'RECLASS'
765 , X_account_type => 'GENERAL_FUND_ACCT'
766 , account_ccid => l_gen_fund_ccid
767 )
768 THEN
769 check_ccid ( 'general fund account');
770 return false;
771 END IF;
772 IF NOT IGI_IAC_COMMON_UTILS.get_account_ccid
773 ( X_book_type_code => fp_det_balances.book_type_code
774 , X_asset_id => fp_det_balances.asset_id
775 , X_distribution_id => fp_det_balances.distribution_id
776 ,X_TRANSACTION_HEADER_ID => l_trans_rec.TRANSACTION_HEADER_ID
777 ,X_calling_function => 'RECLASS'
778 , X_account_type => 'ASSET_COST_ACCT'
779 , account_ccid => l_asset_cost_ccid
780 )
781 THEN
782 check_ccid ( 'asset cost account');
783 return false;
784 END IF;
785 IF NOT IGI_IAC_COMMON_UTILS.get_account_ccid
786 ( X_book_type_code => fp_det_balances.book_type_code
787 , X_asset_id => fp_det_balances.asset_id
788 , X_distribution_id => fp_det_balances.distribution_id
789 ,X_TRANSACTION_HEADER_ID => l_trans_rec.TRANSACTION_HEADER_ID
790 ,X_calling_function => 'RECLASS'
791 , X_account_type => 'DEPRN_RESERVE_ACCT'
792 , account_ccid => l_deprn_rsv_ccid
793 )
794 THEN
795 check_ccid ( 'deprn reserve account');
796 return false;
797 END IF;
798 IF NOT IGI_IAC_COMMON_UTILS.get_account_ccid
799 ( X_book_type_code => fp_det_balances.book_type_code
800 , X_asset_id => fp_det_balances.asset_id
801 , X_distribution_id => fp_det_balances.distribution_id
802 ,X_TRANSACTION_HEADER_ID => l_trans_rec.TRANSACTION_HEADER_ID
803 ,X_calling_function => 'RECLASS'
804 , X_account_type => 'DEPRN_EXPENSE_ACCT'
805 , account_ccid => l_deprn_exp_ccid
806 )
807 THEN
808 check_ccid ( 'deprn reserve account');
809 return false;
810 END IF;
811
812 begin
813 If fp_adjustement_type = 'OLD' Then
814
815 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
816 p_full_path => l_path_name,
817 p_string => '+acctg creation cost vs reval reserve');
818
819 /*create_acctg_entry ( l_dr_ccid => l_asset_cost_ccid
820 , l_cr_ccid => l_revl_rsv_ccid
821 , p_amount => fp_det_balances.reval_reserve_cost
822 , l_dr_adjust_type => 'COST'
823 , l_cr_adjust_type => 'REVAL RESERVE'
824 , l_set_of_books_id => l_sob_id
825 , fp_det_balances => fp_det_balances
826 );*/
827
828 create_acctg_entry ( l_ccid => l_asset_cost_ccid
829 , p_amount => fp_det_balances.reval_reserve_cost
830 , l_adjust_type => 'COST'
831 , l_cr_dr_flag => 'CR'
832 , l_set_of_books_id => l_sob_id
833 , fp_det_balances => fp_det_balances
834 , l_adjust_offset_type => 'REVAL RESERVE'
835 , l_report_ccid => l_revl_rsv_ccid
836 , p_event_id => p_event_id
837 );
838
839 create_acctg_entry ( l_ccid => l_revl_rsv_ccid
840 , p_amount => fp_det_balances.reval_reserve_cost
841 , l_adjust_type => 'REVAL RESERVE'
842 , l_cr_dr_flag => 'DR'
843 , l_set_of_books_id => l_sob_id
844 , fp_det_balances => fp_det_balances
845 , l_adjust_offset_type => 'COST'
846 , l_report_ccid => Null
847 , p_event_id => p_event_id
848 );
849
850 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
851 p_full_path => l_path_name,
852 p_string => '+acctg creation reval reserve vs backlog reserve');
853
854 /*create_acctg_entry ( l_dr_ccid => l_revl_rsv_ccid
855 , l_cr_ccid => l_blog_rsv_ccid
856 , p_amount => fp_det_balances.reval_reserve_backlog
857 , l_dr_adjust_type => 'REVAL RESERVE'
858 , l_cr_adjust_type => 'BL RESERVE'
859 , l_set_of_books_id => l_sob_id
860 , fp_det_balances => fp_det_balances
861 );*/
862 create_acctg_entry ( l_ccid => l_revl_rsv_ccid
863 , p_amount => fp_det_balances.reval_reserve_backlog
864 , l_adjust_type => 'REVAL RESERVE'
865 , l_cr_dr_flag => 'CR'
866 , l_set_of_books_id => l_sob_id
867 , fp_det_balances => fp_det_balances
868 , l_adjust_offset_type => 'BL RESERVE'
869 , l_report_ccid => Null
870 , p_event_id => p_event_id
871 );
872
873 create_acctg_entry ( l_ccid => l_blog_rsv_ccid
874 , p_amount => fp_det_balances.reval_reserve_backlog
875 , l_adjust_type => 'BL RESERVE'
876 , l_cr_dr_flag => 'DR'
877 , l_set_of_books_id => l_sob_id
878 , fp_det_balances => fp_det_balances
879 , l_adjust_offset_type => 'REVAL RESERVE'
880 , l_report_ccid => l_revl_rsv_ccid
881 , p_event_id => p_event_id
882 );
883
884 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
885 p_full_path => l_path_name,
886 p_string => '+acctg creation reval reserve vs gen fund');
887
888
889 /*create_acctg_entry ( l_dr_ccid => l_revl_rsv_ccid
890 , l_cr_ccid => l_gen_fund_ccid
891 , p_amount => fp_det_balances.reval_reserve_gen_fund
892 , l_dr_adjust_type => 'REVAL RESERVE'
893 , l_cr_adjust_type => 'GENERAL FUND'
894 , l_set_of_books_id => l_sob_id
895 , fp_det_balances => fp_det_balances
896 );*/
897 create_acctg_entry ( l_ccid => l_revl_rsv_ccid
898 , p_amount => fp_det_balances.reval_reserve_gen_fund
899 , l_adjust_type => 'REVAL RESERVE'
900 , l_cr_dr_flag => 'CR'
901 , l_set_of_books_id => l_sob_id
902 , fp_det_balances => fp_det_balances
903 , l_adjust_offset_type => 'GENERAL FUND'
904 , l_report_ccid => Null
905 , p_event_id => p_event_id
906 );
907
908 create_acctg_entry ( l_ccid => l_gen_fund_ccid
909 , p_amount => fp_det_balances.reval_reserve_gen_fund
910 , l_adjust_type => 'GENERAL FUND'
911 , l_cr_dr_flag => 'DR'
912 , l_set_of_books_id => l_sob_id
913 , fp_det_balances => fp_det_balances
914 , l_adjust_offset_type => 'REVAL RESERVE'
915 , l_report_ccid => l_revl_rsv_ccid
916 , p_event_id => p_event_id
917 );
918
919 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
920 p_full_path => l_path_name,
921 p_string => '+acctg creation op account vs cost');
922
923 /*create_acctg_entry ( l_dr_ccid => l_op_exp_ccid
924 , l_cr_ccid => l_asset_cost_ccid
925 , p_amount => fp_det_balances.operating_acct_cost
926 , l_dr_adjust_type => 'OP ACCOUNT'
927 , l_cr_adjust_type => 'COST'
928 , l_set_of_books_id => l_sob_id
929 , fp_det_balances => fp_det_balances
930 );*/
931 IF Diff_price_index_flag Then
932 create_acctg_entry ( l_ccid => l_op_exp_ccid
933 , p_amount => fp_det_balances.operating_acct_cost
934 , l_adjust_type => 'OP EXPENSE'
935 , l_cr_dr_flag => 'DR'
936 , l_set_of_books_id => l_sob_id
937 , fp_det_balances => fp_det_balances
938 , l_adjust_offset_type => 'COST'
939 , l_report_ccid => Null
940 , p_event_id => p_event_id
941 );
942 End if;
943 create_acctg_entry ( l_ccid => l_asset_cost_ccid
944 , p_amount => fp_det_balances.operating_acct_cost
945 , l_adjust_type => 'COST'
946 , l_cr_dr_flag => 'CR'
947 , l_set_of_books_id => l_sob_id
948 , fp_det_balances => fp_det_balances
949 , l_adjust_offset_type => 'OP EXPENSE'
950 , l_report_ccid => l_op_exp_ccid
951 , p_event_id => p_event_id
952 );
953
954 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
955 p_full_path => l_path_name,
956 p_string => '+acctg creation backlog vs op account');
957
958 /*create_acctg_entry ( l_dr_ccid => l_blog_rsv_ccid
959 , l_cr_ccid => l_op_exp_ccid
960 , p_amount => fp_det_balances.operating_acct_backlog
961 , l_dr_adjust_type => 'BL RESERVE'
962 , l_cr_adjust_type => 'OP ACCOUNT'
963 , l_set_of_books_id => l_sob_id
964 , fp_det_balances => fp_det_balances
965 );*/
966 create_acctg_entry ( l_ccid => l_blog_rsv_ccid
967 , p_amount => fp_det_balances.operating_acct_backlog
968 , l_adjust_type => 'BL RESERVE'
969 , l_cr_dr_flag => 'DR'
970 , l_set_of_books_id => l_sob_id
971 , fp_det_balances => fp_det_balances
972 , l_adjust_offset_type => 'OP EXPENSE'
973 , l_report_ccid => l_op_exp_ccid
974 , p_event_id => p_event_id
975 );
976
977 IF Diff_price_index_flag Then
978 create_acctg_entry ( l_ccid => l_op_exp_ccid
979 , p_amount => fp_det_balances.operating_acct_backlog
980 , l_adjust_type => 'OP EXPENSE'
981 , l_cr_dr_flag => 'CR'
982 , l_set_of_books_id => l_sob_id
983 , fp_det_balances => fp_det_balances
984 , l_adjust_offset_type => 'BL RESERVE'
985 , l_report_ccid => Null
986 , p_event_id => p_event_id
987 );
988 End if;
989
990 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
991 p_full_path => l_path_name,
992 p_string => '+acctg creation deprn reserve vs deprn expense');
993
994 create_acctg_entry ( l_ccid => l_deprn_rsv_ccid
995 , p_amount => fp_det_balances.deprn_reserve
996 , l_adjust_type => 'RESERVE'
997 , l_cr_dr_flag => 'DR'
998 , l_set_of_books_id => l_sob_id
999 , fp_det_balances => fp_det_balances
1000 , l_adjust_offset_type => 'EXPENSE'
1001 , l_report_ccid => Null
1002 , p_event_id => p_event_id
1003 );
1004
1005
1006
1007 /* bug 2439006 additional dep_resrver account created old category depreciation expense negation
1008 entry to the new category*/
1009 IF Diff_price_index_flag Then
1010
1011 create_acctg_entry ( l_ccid => l_deprn_exp_ccid
1012 , p_amount => fp_det_balances.deprn_reserve
1013 , l_adjust_type => 'EXPENSE'
1014 , l_cr_dr_flag => 'CR'
1015 , l_set_of_books_id => l_sob_id
1016 , fp_det_balances => fp_det_balances
1017 , l_adjust_offset_type => 'RESERVE'
1018 , l_report_ccid => Null
1019 , p_event_id => p_event_id
1020 );
1021
1022 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1023 p_full_path => l_path_name,
1024 p_string => '+acctg creation depreciation expenese for old ');
1025
1026 create_acctg_entry ( l_ccid => l_deprn_exp_ccid
1027 , p_amount => fp_det_balances.deprn_reserve
1028 , l_adjust_type => 'EXPENSE'
1029 , l_cr_dr_flag => 'DR'
1030 , l_set_of_books_id => l_sob_id
1031 , fp_det_balances => fp_det_balances
1032 , l_adjust_offset_type => 'RESERVE'
1033 , l_report_ccid => Null
1034 , p_event_id => p_event_id
1035 );
1036
1037 l_deprn_reserve_amount:= fp_det_balances.deprn_reserve;
1038 End if;
1039
1040 /* bug 2439006 YTD not required now beacuse of above accounting entry*/
1041
1042 /*
1043 debug(0,'+acctg creation depreciation expenese for YTD');
1044 create_acctg_entry ( l_ccid => l_deprn_exp_ccid
1045 , p_amount => fp_det_balances.deprn_ytd
1046 , l_adjust_type => 'EXPENSE'
1047 , l_cr_dr_flag => 'DR'
1048 , l_set_of_books_id => l_sob_id
1049 , fp_det_balances => fp_det_balances
1050 );*/
1051
1052
1053 /* debug(0,'+acctg creation backlog reserve vs deprn reserve');
1054
1055 create_acctg_entry ( l_ccid => l_blog_rsv_ccid
1056 , p_amount => fp_det_balances.reval_reserve_backlog
1057 , l_adjust_type => 'BL RESERVE'
1058 , l_cr_dr_flag => 'CR'
1059 , l_set_of_books_id => l_sob_id
1060 , fp_det_balances => fp_det_balances
1061 );
1062
1063 create_acctg_entry ( l_ccid => l_deprn_rsv_ccid
1064 , p_amount => fp_det_balances.reval_reserve_backlog
1065 , l_adjust_type => 'RESERVE'
1066 , l_cr_dr_flag => 'DR'
1067 , l_set_of_books_id => l_sob_id
1068 , fp_det_balances => fp_det_balances
1069 );*/
1070 -- end if;
1071
1072 elsif fp_adjustement_type = 'NEW' THEN
1073
1074 IF Diff_price_index_flag Then
1075
1076 /* bug 2439006 YTD not required now beacuse of above accounting entry*/
1077 /* debug(0,'+acctg creation depreciation expenese for YTD');
1078 create_acctg_entry ( l_ccid => l_deprn_exp_ccid
1079 , p_amount => fp_det_balances.deprn_ytd
1080 , l_adjust_type => 'EXPENSE'
1081 , l_cr_dr_flag => 'CR'
1082 , l_set_of_books_id => l_sob_id
1083 , fp_det_balances => fp_det_balances
1084 );*/
1085
1086 /* bug 2439006 additional dep_resrver account created old category depreciation expense negation
1087 entry to the new category*/
1088
1089 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1090 p_full_path => l_path_name,
1091 p_string => '+acctg creation depreciation expenese for new ');
1092
1093 create_acctg_entry ( l_ccid => l_deprn_exp_ccid
1094 , p_amount => l_deprn_reserve_amount
1095 , l_adjust_type => 'EXPENSE'
1096 , l_cr_dr_flag => 'CR'
1097 , l_set_of_books_id => l_sob_id
1098 , fp_det_balances => fp_det_balances
1099 , l_adjust_offset_type => 'RESERVE'
1100 , l_report_ccid => Null
1101 , p_event_id => p_event_id
1102 );
1103
1104
1105 ELSE
1106
1107 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1108 p_full_path => l_path_name,
1109 p_string => '+acctg creation cost vs reval reserve');
1110 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1111 p_full_path => l_path_name,
1112 p_string => '+acctg creation cost vs reval reserve');
1113
1114 /*create_acctg_entry ( l_dr_ccid => l_asset_cost_ccid
1115 , l_cr_ccid => l_revl_rsv_ccid
1116 , p_amount => fp_det_balances.reval_reserve_cost
1117 , l_dr_adjust_type => 'COST'
1118 , l_cr_adjust_type => 'REVAL RESERVE'
1119 , l_set_of_books_id => l_sob_id
1120 , fp_det_balances => fp_det_balances
1121 );*/
1122
1123 create_acctg_entry ( l_ccid => l_asset_cost_ccid
1124 , p_amount => fp_det_balances.reval_reserve_cost
1125 , l_adjust_type => 'COST'
1126 , l_cr_dr_flag => 'DR'
1127 , l_set_of_books_id => l_sob_id
1128 , fp_det_balances => fp_det_balances
1129 , l_adjust_offset_type => 'REVAL RESERVE'
1130 , l_report_ccid => l_revl_rsv_ccid
1131 , p_event_id => p_event_id
1132 );
1133 create_acctg_entry ( l_ccid => l_revl_rsv_ccid
1134 , p_amount => fp_det_balances.reval_reserve_cost
1135 , l_adjust_type => 'REVAL RESERVE'
1136 , l_cr_dr_flag => 'CR'
1137 , l_set_of_books_id => l_sob_id
1138 , fp_det_balances => fp_det_balances
1139 , l_adjust_offset_type => 'COST'
1140 , l_report_ccid => Null
1141 , p_event_id => p_event_id
1142 );
1143
1144 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1145 p_full_path => l_path_name,
1146 p_string => '+acctg creation reval reserve vs backlog reserve');
1147
1148 /*create_acctg_entry ( l_dr_ccid => l_revl_rsv_ccid
1149 , l_cr_ccid => l_blog_rsv_ccid
1150 , p_amount => fp_det_balances.reval_reserve_backlog
1151 , l_dr_adjust_type => 'REVAL RESERVE'
1152 , l_cr_adjust_type => 'BL RESERVE'
1153 , l_set_of_books_id => l_sob_id
1154 , fp_det_balances => fp_det_balances
1155 );*/
1156 create_acctg_entry ( l_ccid => l_revl_rsv_ccid
1157 , p_amount => fp_det_balances.reval_reserve_backlog
1158 , l_adjust_type => 'REVAL RESERVE'
1159 , l_cr_dr_flag => 'DR'
1160 , l_set_of_books_id => l_sob_id
1161 , fp_det_balances => fp_det_balances
1162 , l_adjust_offset_type => 'BL RESERVE'
1163 , l_report_ccid => Null
1164 , p_event_id => p_event_id
1165 );
1166
1167 create_acctg_entry ( l_ccid => l_blog_rsv_ccid
1168 , p_amount => fp_det_balances.reval_reserve_backlog
1169 , l_adjust_type => 'BL RESERVE'
1170 , l_cr_dr_flag => 'CR'
1171 , l_set_of_books_id => l_sob_id
1172 , fp_det_balances => fp_det_balances
1173 , l_adjust_offset_type => 'REVAL RESERVE'
1174 , l_report_ccid => l_revl_rsv_ccid
1175 , p_event_id => p_event_id
1176 );
1177
1178 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1179 p_full_path => l_path_name,
1180 p_string => '+acctg creation reval reserve vs gen fund');
1181
1182 /*create_acctg_entry ( l_dr_ccid => l_revl_rsv_ccid
1183 , l_cr_ccid => l_gen_fund_ccid
1184 , p_amount => fp_det_balances.reval_reserve_gen_fund
1185 , l_dr_adjust_type => 'REVAL RESERVE'
1186 , l_cr_adjust_type => 'GENERAL FUND'
1187 , l_set_of_books_id => l_sob_id
1188 , fp_det_balances => fp_det_balances
1189 );*/
1190 create_acctg_entry ( l_ccid => l_revl_rsv_ccid
1191 , p_amount => fp_det_balances.reval_reserve_gen_fund
1192 , l_adjust_type => 'REVAL RESERVE'
1193 , l_cr_dr_flag => 'DR'
1194 , l_set_of_books_id => l_sob_id
1195 , fp_det_balances => fp_det_balances
1196 , l_adjust_offset_type => 'GENERAL FUND'
1197 , l_report_ccid => Null
1198 , p_event_id => p_event_id
1199 );
1200
1201 create_acctg_entry ( l_ccid => l_gen_fund_ccid
1202 , p_amount => fp_det_balances.reval_reserve_gen_fund
1203 , l_adjust_type => 'GENERAL FUND'
1204 , l_cr_dr_flag => 'CR'
1205 , l_set_of_books_id => l_sob_id
1206 , fp_det_balances => fp_det_balances
1207 , l_adjust_offset_type => 'REVAL RESERVE'
1208 , l_report_ccid => l_revl_rsv_ccid
1209 , p_event_id => p_event_id
1210 );
1211
1212 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1213 p_full_path => l_path_name,
1214 p_string => '+acctg creation op account vs cost');
1215
1216 /*create_acctg_entry ( l_dr_ccid => l_op_exp_ccid
1217 , l_cr_ccid => l_asset_cost_ccid
1218 , p_amount => fp_det_balances.operating_acct_cost
1219 , l_dr_adjust_type => 'OP ACCOUNT'
1220 , l_cr_adjust_type => 'COST'
1221 , l_set_of_books_id => l_sob_id
1222 , fp_det_balances => fp_det_balances
1223 );*/
1224
1225 /* create_acctg_entry ( l_ccid => l_op_exp_ccid
1226 , p_amount => fp_det_balances.operating_acct_cost
1227 , l_adjust_type => 'OP EXPENSE'
1228 , l_cr_dr_flag => 'CR'
1229 , l_set_of_books_id => l_sob_id
1230 , fp_det_balances => fp_det_balances
1231 );*/
1232
1233 create_acctg_entry ( l_ccid => l_asset_cost_ccid
1234 , p_amount => fp_det_balances.operating_acct_cost
1235 , l_adjust_type => 'COST'
1236 , l_cr_dr_flag => 'DR'
1237 , l_set_of_books_id => l_sob_id
1238 , fp_det_balances => fp_det_balances
1239 , l_adjust_offset_type => 'OP EXPENSE'
1240 , l_report_ccid => l_op_exp_ccid,
1241 p_event_id => p_event_id
1242 );
1243
1244 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1245 p_full_path => l_path_name,
1246 p_string => '+acctg creation backlog vs op account');
1247
1248 /*create_acctg_entry ( l_dr_ccid => l_blog_rsv_ccid
1249 , l_cr_ccid => l_op_exp_ccid
1250 , p_amount => fp_det_balances.operating_acct_backlog
1251 , l_dr_adjust_type => 'BL RESERVE'
1252 , l_cr_adjust_type => 'OP EXPENSE'
1253 , l_set_of_books_id => l_sob_id
1254 , fp_det_balances => fp_det_balances
1255 );*/
1256 create_acctg_entry ( l_ccid => l_blog_rsv_ccid
1257 , p_amount => fp_det_balances.operating_acct_backlog
1258 , l_adjust_type => 'BL RESERVE'
1259 , l_cr_dr_flag => 'CR'
1260 , l_set_of_books_id => l_sob_id
1261 , fp_det_balances => fp_det_balances
1262 , l_adjust_offset_type => 'OP EXPENSE'
1263 , l_report_ccid => l_op_exp_ccid
1264 , p_event_id => p_event_id
1265 );
1266
1267
1268 /* create_acctg_entry ( l_ccid => l_op_exp_ccid
1269 , p_amount => fp_det_balances.operating_acct_backlog
1270 , l_adjust_type => 'OP EXPENSE'
1271 , l_cr_dr_flag => 'DR'
1272 , l_set_of_books_id => l_sob_id
1273 , fp_det_balances => fp_det_balances
1274 );*/
1275
1276 -- removing these entries as per bug 2483321
1277 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1278 p_full_path => l_path_name,
1279 p_string => '+acctg creation deprn reserve vs deprn expense');
1280
1281 create_acctg_entry ( l_ccid => l_deprn_rsv_ccid
1282 , p_amount => fp_det_balances.deprn_reserve
1283 , l_adjust_type => 'RESERVE'
1284 , l_cr_dr_flag => 'CR'
1285 , l_set_of_books_id => l_sob_id
1286 , fp_det_balances => fp_det_balances
1287 , l_adjust_offset_type => 'EXPENSE'
1288 , l_report_ccid => Null
1289 , p_event_id => p_event_id
1290 );
1291 /* create_acctg_entry ( l_ccid => l_deprn_exp_ccid
1292 , p_amount => fp_det_balances.deprn_reserve
1293 , l_adjust_type => 'EXPENSE'
1294 , l_cr_dr_flag => 'DR'
1295 , l_set_of_books_id => l_sob_id
1296 , fp_det_balances => fp_det_balances
1297 );*/
1298
1299 /* debug(0,'+acctg creation depreciation expenese for YTD');
1300 create_acctg_entry ( l_ccid => l_deprn_exp_ccid
1301 , p_amount => fp_det_balances.deprn_ytd
1302 , l_adjust_type => 'EXPENSE'
1303 , l_cr_dr_flag => 'CR'
1304 , l_set_of_books_id => l_sob_id
1305 , fp_det_balances => fp_det_balances
1306 );*/
1307
1308
1309 /* debug(0,'+acctg creation backlog reserve vs deprn reserve');
1310
1311 create_acctg_entry ( l_ccid => l_blog_rsv_ccid
1312 , p_amount => fp_det_balances.reval_reserve_backlog
1313 , l_adjust_type => 'BL RESERVE'
1314 , l_cr_dr_flag => 'CR'
1315 , l_set_of_books_id => l_sob_id
1316 , fp_det_balances => fp_det_balances
1317 );
1318
1319 create_acctg_entry ( l_ccid => l_deprn_rsv_ccid
1320 , p_amount => fp_det_balances.reval_reserve_backlog
1321 , l_adjust_type => 'RESERVE'
1322 , l_cr_dr_flag => 'DR'
1323 , l_set_of_books_id => l_sob_id
1324 , fp_det_balances => fp_det_balances
1325 ); */
1326 end if;
1327
1328 end if;
1329 end;
1330
1331 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1332 p_full_path => l_path_name,
1333 p_string => 'end acctg creation');
1334 return true;
1335 end;
1336
1337 -- ======================================================================
1338 -- FUNCTION Do_No_Index_Reval
1339 -- fix for bug 3356588 - processing an asset when the new category that it
1340 -- has been reclassified to has indexed revaluation switched off
1341 -- Code does:
1342 -- 1. Create a new transaction header line for the asset for the new cat
1343 -- 2. Reverse the accounting entries for the existing dist ids.
1344 -- 3. Does not create accounting entries for the new dists.
1345 -- 4. The det balances for the existing dists are brought forward only
1346 -- with YTD values
1347 -- 5. The det balances for the new dists are all 0 with cumulative reval
1348 -- factor of 1
1349 -- 6. The asset balances for the asset are all 0 with cumulative reval
1350 -- factor of 1
1351 -- ======================================================================
1352 FUNCTION Do_No_Index_Reval(p_event_id in number) RETURN BOOLEAN
1353 IS
1354
1355 /* Cursor to select all distributions for an asset that are active and the distribution
1356 impacted by the reclass */
1357
1358 CURSOR c_all_dist IS
1359 SELECT distribution_id,
1360 transaction_header_id_in,
1361 units_assigned
1362 FROM fa_distribution_history
1363 WHERE asset_id=l_asset_hdr_rec.asset_id
1364 AND book_type_code=l_asset_hdr_rec.book_type_code
1365 AND transaction_header_id_out=l_trans_rec.transaction_header_id;
1366
1367 /* Cursor to select the distribution(s) undergoing the reclasss */
1368
1369 CURSOR c_old_dist(c_dist_id IN FA_DISTRIBUTION_HISTORY.distribution_id%TYPE) IS
1370 SELECT distribution_id
1371 FROM fa_distribution_history
1372 WHERE asset_id=l_asset_hdr_rec.asset_id
1373 AND book_type_code=l_asset_hdr_rec.book_type_code
1374 AND transaction_header_id_out=l_trans_rec.transaction_header_id
1375 AND distribution_id=c_dist_id;
1376
1377 /* Cursor to select the new distribution(s) undergoing the reclasss */
1378
1379 CURSOR c_new_dist(c_dist_id IN FA_DISTRIBUTION_HISTORY.distribution_id%TYPE) IS
1380 SELECT distribution_id
1381 FROM fa_distribution_history
1382 WHERE asset_id=l_asset_hdr_rec.asset_id
1383 AND book_type_code=l_asset_hdr_rec.book_type_code
1384 AND transaction_header_id_in=l_trans_rec.transaction_header_id
1385 AND distribution_id=c_dist_id;
1386
1387 /* Cursor to select the details of impacted distribution in this reclass*/
1388
1389 CURSOR c_impacted_dist(c_imp_dist_id FA_DISTRIBUTION_HISTORY.distribution_id%TYPE) IS
1390 SELECT a.distribution_id,
1391 a.units_assigned
1392 FROM fa_distribution_history a, fa_distribution_history b
1393 WHERE a.asset_id=l_asset_hdr_rec.asset_id
1394 AND a.book_type_code=l_asset_hdr_rec.book_type_code
1395 AND a.asset_id = b.asset_id
1396 AND a.book_type_code = b.book_type_code
1397 AND a.transaction_header_id_in=l_trans_rec.transaction_header_id
1398 AND b.transaction_header_id_out = l_trans_rec.transaction_header_id
1399 AND nvl(a.location_id,-1) = nvl(b.location_id,-1)
1400 AND a.units_assigned = b.units_assigned
1401 AND b.distribution_id=c_imp_dist_id;
1402
1403 CURSOR c_impacted_dist_new(c_imp_dist_id FA_DISTRIBUTION_HISTORY.distribution_id%TYPE ,
1404 c_imp_dist_id_new FA_DISTRIBUTION_HISTORY.distribution_id%TYPE) IS
1405 SELECT a.distribution_id,
1406 a.units_assigned
1407 FROM fa_distribution_history a, fa_distribution_history b
1408 WHERE a.asset_id=l_asset_hdr_rec.asset_id
1409 AND a.book_type_code=l_asset_hdr_rec.book_type_code
1410 AND a.asset_id = b.asset_id
1411 AND a.book_type_code = b.book_type_code
1412 AND a.transaction_header_id_in=l_trans_rec.transaction_header_id
1413 AND b.transaction_header_id_out = l_trans_rec.transaction_header_id
1414 AND nvl(a.location_id,-1) = nvl(b.location_id,-1)
1415 AND a.units_assigned = b.units_assigned
1416 AND b.distribution_id=c_imp_dist_id
1417 AND a.distribution_id > c_imp_dist_id_new;
1418
1419 /* Cursor to select adjustment_id or the previous transaction */
1420
1421 CURSOR c_prev_data IS
1422 SELECT a.rowid,a.adjustment_id
1423 FROM igi_iac_transaction_headers a
1424 WHERE a.adjustment_id_out IS NULL
1425 AND a.asset_id = l_asset_hdr_rec.asset_id;
1426
1427
1428 /* Cursor to find the amounts that need to be transferres to the new dist
1429 created by reclass */
1430
1431 CURSOR c_amounts(c_period_counter IN IGI_IAC_ASSET_BALANCES.period_counter%TYPE) IS
1432 SELECT *
1433 FROM igi_iac_asset_balances
1434 WHERE asset_id=l_asset_hdr_rec.asset_id
1435 AND book_type_code=l_asset_hdr_rec.book_type_code
1436 AND period_counter=(SELECT max(period_counter)
1437 FROM igi_iac_asset_balances
1438 WHERE asset_id=l_asset_hdr_rec.asset_id
1439 AND book_type_code=l_asset_hdr_rec.book_type_code);
1440
1441 /* Cursor to find the total number of units for the asset itself ( active) */
1442
1443 CURSOR c_units IS
1444 SELECT sum(units_assigned)
1445 FROM fa_distribution_history
1446 WHERE asset_id=l_asset_hdr_rec.asset_id
1447 AND book_type_code=l_asset_hdr_rec.book_type_code
1448 AND transaction_header_id_out IS NULL;
1449
1450 /* Cursor to find the total no of units involved in the transfer of old dist to new */
1451
1452 CURSOR c_dist_units IS
1453 SELECT sum(units_assigned)
1454 FROM fa_distribution_history
1455 WHERE asset_id=l_asset_hdr_rec.asset_id
1456 AND book_type_code=l_asset_hdr_rec.book_type_code
1457 AND transaction_header_id_in=l_trans_rec.transaction_header_id;
1458
1459 /* Cursor for ytd deprn */
1460
1461 CURSOR c_ytd_deprn(c_start_counter IGI_IAC_ASSET_BALANCES.period_counter%TYPE
1462 ,c_current_counter IGI_IAC_ASSET_BALANCES.period_counter%TYPE) IS
1463 SELECT nvl(sum(deprn_amount),0) deprn_amount
1464 FROM igi_iac_asset_balances
1465 WHERE book_type_code=l_asset_hdr_rec.book_type_code
1466 AND asset_id=l_asset_hdr_rec.asset_id
1467 AND period_counter between c_start_counter and c_current_counter;
1468
1469 /* Cursor for operating account ytd */
1470
1471 CURSOR c_op_acct_ytd(c_start_counter IGI_IAC_ASSET_BALANCES.period_counter%TYPE
1472 ,c_current_counter IGI_IAC_ASSET_BALANCES.period_counter%TYPE) IS
1473 SELECT nvl(sum(operating_acct),0) operating_acct
1474 FROM igi_iac_asset_balances
1475 WHERE book_type_code=l_asset_hdr_rec.book_type_code
1476 AND asset_id=l_asset_hdr_rec.asset_id
1477 AND period_counter BETWEEN c_start_counter AND c_current_counter;
1478
1479 /* Cursor to select the start period number for a given fiscal year */
1480
1481 CURSOR c_start_period_counter(c_fiscal_year FA_DEPRN_PERIODS.fiscal_year%TYPE) IS
1482 SELECT (number_per_fiscal_year*c_fiscal_year)+1
1483 FROM fa_calendar_types
1484 WHERE calendar_type=(SELECT deprn_calendar
1485 FROM fa_book_controls
1486 WHERE book_type_code=l_asset_hdr_rec.book_type_code);
1487
1488 /* Cursor to select the adjustment_id from the sequence for a new record */
1489
1490 CURSOR c_adj_id IS
1491 SELECT igi_iac_transaction_headers_s.nextval
1492 FROM dual;
1493
1494 /* To select the asset's deprn_expense from fa_books */
1495
1496 CURSOR c_deprn_expense(c_period_counter FA_DEPRN_SUMMARY.period_counter%TYPE) IS
1497 SELECT deprn_amount
1498 FROM fa_deprn_summary
1499 WHERE book_type_code = l_asset_hdr_rec.book_type_code
1500 AND period_counter = c_period_counter
1501 AND asset_id=l_asset_hdr_rec.asset_id;
1502
1503 /* Cursor to select the reval reserve backlog,op acct backlog and gen fund per for the dist */
1504
1505 CURSOR c_backlog_data(c_current_period_Counter fa_deprn_periods.period_counter%TYPE) IS
1506 SELECT sum(nvl(iadb.reval_reserve_backlog,0)) reval_reserve_backlog,
1507 sum(nvl(iadb.operating_acct_backlog,0)) operating_acct_backlog,
1508 sum(nvl(iadb.general_fund_per,0)) general_fund_per
1509 FROM igi_iac_det_balances iadb,fa_distribution_history fdh
1510 WHERE iadb.book_type_code = l_asset_hdr_rec.book_type_code
1511 AND iadb.period_counter = c_current_period_counter
1512 AND iadb.asset_id=l_asset_hdr_rec.asset_id
1513 AND iadb.asset_id=fdh.asset_id
1514 AND iadb.book_type_code =fdh.book_type_code
1515 AND fdh.transaction_header_id_out=l_trans_rec.transaction_header_id
1516 AND fdh.distribution_id=iadb.distribution_id;
1517
1518 /* To find the asset number */
1519
1520 CURSOR c_asset_num IS
1521 SELECT asset_number
1522 FROM fa_additions
1523 WHERE asset_id=l_asset_hdr_rec.asset_id;
1524
1525 /* get the closing det balances record */
1526 CURSOR c_closing_det_balances( p_old_dist IGI_IAC_ADJUSTMENTS.distribution_id%TYPE,
1527 p_adjustment_id IGI_IAC_ADJUSTMENTS.adjustment_id%TYPE)IS
1528 SELECT *
1529 FROM igi_iac_det_balances
1530 WHERE asset_id=l_asset_hdr_rec.asset_id
1531 AND book_type_code = l_asset_hdr_rec.book_type_code
1532 AND distribution_id = p_old_dist
1533 AND adjustment_id = p_adjustment_id;
1534
1535 /* Cursor to get incactive distributuiions to be carried forward */
1536 CURSOR get_all_prev_inactive_dist (C_prev_data_adjustment_id NUMBER)
1537 IS
1538 SELECT *
1539 FROM igi_iac_det_balances
1540 WHERE asset_id=l_asset_hdr_rec.asset_id
1541 AND book_type_code = l_asset_hdr_rec.book_type_code
1542 AND adjustment_id = C_prev_data_adjustment_id;
1543 -- AND nvl(active_flag,'Y') = 'N';
1544
1545
1546 /* Enhancemnet 2480915 Cursor to fetch the igi_fa_deprn deatils */
1547 CURSOR c_get_deprn_dist (c_book_type_code VARCHAR2,
1548 c_asset_id NUMBER,
1549 c_distribution_id NUMBER,
1550 c_adjustment_id NUMBER)
1551 IS
1552 SELECT *
1553 FROM IGI_IAC_FA_DEPRN
1554 WHERE book_type_code = c_book_type_code
1555 AND asset_id = c_asset_id
1556 AND Distribution_id = c_distribution_id
1557 AND adjustment_id = c_adjustment_id;
1558
1559
1560 -- l_asset_revalued c_asset_revalued%rowtype;
1561 l_impacted_dist c_impacted_dist%ROWTYPE;
1562 l_amounts c_amounts%ROWTYPE;
1563 l_backlog_data c_backlog_data%ROWTYPE;
1564 l_get_deprn_dist c_get_deprn_dist%ROWTYPE;
1565 l_old_dist c_old_dist%ROWTYPE;
1566 l_dist_units FA_DISTRIBUTION_HISTORY.units_assigned%TYPE;
1567 l_prd_rec IGI_IAC_TYPES.prd_rec;
1568 l_prd_rec_prior IGI_IAC_TYPES.prd_rec;
1569
1570 l_prev_data c_prev_data%ROWTYPE;
1571 l_adj_id IGI_IAC_ADJUSTMENTS.adjustment_id%TYPE;
1572 l_current_period_counter FA_DEPRN_PERIODS.period_counter%TYPE;
1573 l_start_period_counter FA_DEPRN_PERIODS.period_counter%TYPE;
1574
1575 l_asset_num FA_ADDITIONS.asset_number%TYPE;
1576 l_units FA_DISTRIBUTION_HISTORY.units_assigned%TYPE;
1577 l_reval_reserve IGI_IAC_DET_BALANCES.reval_reserve_cost%TYPE;
1578 l_general_fund IGI_IAC_DET_BALANCES.general_fund_acc%TYPE;
1579 l_Backlog_deprn_reserve IGI_IAC_DET_BALANCES.deprn_reserve_backlog%TYPE;
1580 l_deprn_reserve IGI_IAC_DET_BALANCES.deprn_reserve%TYPE;
1581 l_adjusted_cost IGI_IAC_DET_BALANCES.adjustment_cost%TYPE;
1582 l_net_book_value IGI_IAC_DET_BALANCES.net_book_value%TYPE;
1583 l_deprn_per IGI_IAC_DET_BALANCES.deprn_period%TYPE;
1584 l_ytd_deprn IGI_IAC_DET_BALANCES.deprn_ytd%TYPE;
1585 l_op_acct IGI_IAC_DET_BALANCES.operating_acct_ytd%TYPE;
1586 l_op_acct_ytd IGI_IAC_DET_BALANCES.operating_acct_ytd%TYPE;
1587 l_general_fund_per IGI_IAC_DET_BALANCES.general_fund_per%TYPE;
1588 l_reval_reserve_backlog IGI_IAC_DET_BALANCES.reval_reserve_backlog%TYPE;
1589 l_operating_acct_backlog IGI_IAC_DET_BALANCES.operating_acct_backlog%TYPE;
1590 l_reval_ccid IGI_IAC_ADJUSTMENTS.code_combination_id%TYPE;
1591 l_gen_fund_ccid IGI_IAC_ADJUSTMENTS.code_combination_id%TYPE;
1592 l_backlog_ccid IGI_IAC_ADJUSTMENTS.code_combination_id%TYPE;
1593 l_deprn_ccid IGI_IAC_ADJUSTMENTS.code_combination_id%TYPE;
1594 l_cost_ccid IGI_IAC_ADJUSTMENTS.code_combination_id%TYPE;
1595 l_prior_period_counter IGI_IAC_TRANSACTION_HEADERS.period_counter%TYPE;
1596 l_historic_deprn_expense FA_DEPRN_SUMMARY.deprn_amount%TYPE;
1597 l_Expense_diff IGI_IAC_DET_BALANCES.deprn_period%TYPE;
1598 l_expense_ccid IGI_IAC_ADJUSTMENTS.code_combination_id%TYPE;
1599 l_closing_det_balances IGI_IAC_DET_BALANCES%ROWTYPE;
1600 l_adjustment_id IGI_IAC_ADJUSTMENTS.adjustment_id%TYPE;
1601 l_get_all_prev_dist get_all_prev_inactive_dist%ROWTYPE;
1602
1603 l_rowid ROWID;
1604 l_return_value BOOLEAN;
1605 l_Prorate_factor NUMBER;
1606 l_deprn_expense NUMBER;
1607 x VARCHAR2(100);
1608 prior_period VARCHAR2(100);
1609 l_mesg VARCHAR2(500);
1610 l_adjustment_id_out NUMBER;
1611 l_prev_adjustment_id NUMBER;
1612 l_transaction_type_code VARCHAR2(50);
1613 l_transaction_id NUMBER ;
1614 l_mass_reference_id NUMBER ;
1615 l_adjustment_status VARCHAR2(50);
1616 l_path_name VARCHAR2(150);
1617
1618 BEGIN
1619 l_transaction_type_code := NULL;
1620 l_adjustment_status := NULL ;
1621 l_path_name := g_path||'do_no_index_reval';
1622
1623 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1624 p_full_path => l_path_name,
1625 p_string => ' + Index revaluation OFF - In Do_No_Index_Reval');
1626
1627 /* Store the previous transaction adjustment id */
1628
1629 -- get the latest transaction
1630 IF NOT igi_iac_common_utils.Get_Latest_Transaction (
1631 X_book_type_code => l_asset_hdr_rec.book_type_code,
1632 X_asset_id => l_asset_hdr_rec.asset_id,
1633 X_Transaction_Type_Code => l_transaction_type_code,
1634 X_Transaction_Id => l_transaction_id,
1635 X_Mass_Reference_ID => l_mass_reference_id,
1636 X_Adjustment_Id => l_adjustment_id_out,
1637 X_Prev_Adjustment_Id => l_prev_adjustment_id,
1638 X_Adjustment_Status => l_adjustment_status) THEN
1639 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
1640 p_full_path => l_path_name,
1641 p_string => '*** Error in fetching the latest transaction');
1642 RETURN FALSE;
1643 END IF;
1644
1645 -- Get the current open period
1646 IF igi_iac_common_utils.get_open_period_info(l_asset_hdr_rec.book_type_code,l_prd_rec) THEN
1647 l_current_period_counter:=l_prd_rec.period_counter;
1648 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1649 p_full_path => l_path_name,
1650 p_string => ' + Current OPen Period counter' ||l_prd_rec.period_counter );
1651 END IF;
1652
1653 -- Fetch the adjustment id from the sequence
1654 OPEN c_adj_id;
1655 FETCH c_adj_id INTO l_adj_id;
1656 CLOSE c_adj_id;
1657
1658 -- Insert into transaction headers
1659 insert_data_trans_hdr(l_adj_id,
1660 l_trans_rec.transaction_header_id,
1661 NULL,
1662 l_trans_rec.transaction_type_code,
1663 l_trans_rec.transaction_date_entered,
1664 l_trans_rec.mass_reference_id,
1665 l_asset_hdr_rec.book_type_code,
1666 l_asset_hdr_rec.Asset_id,
1667 NULL,
1668 'COMPLETE',
1669 l_asset_cat_rec_old.category_id,
1670 -- l_asset_cat_rec_new.category_id,
1671 l_current_period_counter,
1672 p_event_id
1673 );
1674
1675 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1676 p_full_path => l_path_name,
1677 p_string => ' + After Insert into trans header' );
1678
1679 -- Need to close the previous transaction to pick the latest catgeory --
1680 IGI_IAC_TRANS_HEADERS_PKG.update_row (
1681 x_prev_adjustment_id =>l_adjustment_id_out,
1682 x_adjustment_id =>l_adj_id,
1683 x_mode =>'R'
1684 );
1685
1686 OPEN c_units;
1687 FETCH c_units INTO l_units;
1688 CLOSE c_units;
1689
1690 OPEN c_dist_units;
1691 FETCH c_dist_units INTO l_dist_units;
1692 CLOSE c_dist_units;
1693
1694 l_impacted_dist := NULL;
1695 FOR l_all_dist IN c_all_dist
1696 LOOP
1697 OPEN c_start_period_counter(l_prd_rec.fiscal_year);
1698 FETCH c_start_period_counter INTO l_start_period_counter;
1699 CLOSE c_start_period_counter;
1700
1701 OPEN c_ytd_deprn(l_start_period_counter,l_prd_rec.period_counter);
1702 FETCH c_ytd_deprn INTO l_ytd_deprn;
1703 CLOSE c_ytd_deprn;
1704
1705 OPEN c_op_acct_ytd(l_start_period_counter,l_prd_rec.period_counter);
1706 FETCH c_op_acct_ytd INTO l_op_acct_ytd;
1707 CLOSE c_op_acct_ytd;
1708
1709 OPEN c_old_dist(l_all_dist.distribution_id);
1710 FETCH c_old_dist INTO l_old_dist;
1711 CLOSE c_old_dist;
1712
1713 IF (l_impacted_dist.distribution_id IS NULL ) THEN
1714 OPEN c_impacted_dist(l_all_dist.distribution_id);
1715 FETCH c_impacted_dist INTO l_impacted_dist;
1716 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1717 p_full_path => l_path_name,
1718 p_string => ' + impacted distribution id ' || l_all_dist.distribution_id);
1719 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1720 p_full_path => l_path_name,
1721 p_string => ' + new impacted distribution id ' || l_impacted_dist.distribution_id);
1722
1723 CLOSE c_impacted_dist;
1724 ELSE
1725 OPEN c_impacted_dist_new(l_all_dist.distribution_id,l_impacted_dist.distribution_id);
1726 FETCH c_impacted_dist_new INTO l_impacted_dist;
1727 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1728 p_full_path => l_path_name,
1729 p_string => ' + impacted distribution id ' || l_all_dist.distribution_id);
1730 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1731 p_full_path => l_path_name,
1732 p_string => ' + new impacted distribution id ' || l_impacted_dist.distribution_id);
1733
1734 CLOSE c_impacted_dist_new;
1735 END IF;
1736
1737 OPEN c_old_dist(l_all_dist.distribution_id);
1738 FETCH c_old_dist INTO l_old_dist;
1739 IF c_old_dist%NOTFOUND THEN
1740 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1741 p_full_path => l_path_name,
1742 p_string => ' No old distribution for asset found ' || l_old_dist.distribution_id);
1743 CLOSE c_old_dist;
1744 RETURN FALSE;
1745 END IF;
1746 CLOSE c_old_dist;
1747 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1748 p_full_path => l_path_name,
1749 p_string => ' + no old distribution for asset ' || l_old_dist.distribution_id);
1750
1751 /* get the closing det balances record form iac det balances */
1752 l_adjustment_id :=l_prev_adjustment_id;
1753
1754 OPEN c_closing_det_balances(l_old_dist.distribution_id,l_prev_adjustment_id);
1755 FETCH c_closing_det_balances INTO l_closing_det_balances;
1756 IF c_closing_det_balances%NOTFOUND THEN
1757 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1758 p_full_path => l_path_name,
1759 p_string => ' + old distruibution id ' || l_old_dist.distribution_id);
1760 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1761 p_full_path => l_path_name,
1762 p_string => ' + adjustement id '||l_prev_adjustment_id);
1763 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1764 p_full_path => l_path_name,
1765 p_string => ' Could not find the IAC det balances record ');
1766 CLOSE c_closing_det_balances;
1767 RETURN FALSE;
1768 END IF;
1769
1770 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1771 p_full_path => l_path_name,
1772 p_string => ' + old distruibution id ' || l_old_dist.distribution_id);
1773 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1774 p_full_path => l_path_name,
1775 p_string => ' + adjustement id '||l_prev_adjustment_id);
1776
1777 CLOSE c_closing_det_balances;
1778
1779
1780 l_closing_det_balances.adjustment_id := l_adj_id;
1781 l_closing_det_balances.period_counter := l_current_period_counter;
1782 l_deprn_reserve_amount:= 0;
1783
1784 UPDATE igi_iac_transaction_headers
1785 SET category_id = l_asset_cat_rec_old.category_id
1786 WHERE adjustment_id = l_adj_id;
1787
1788 -- reverse the accounting entries for the existing dist ids
1789 IF create_iac_acctg ( l_closing_det_balances,TRUE,'OLD',p_event_id => p_event_id) THEN
1790 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1791 p_full_path => l_path_name,
1792 p_string => '+Accounting entries created for old');
1793 ELSE
1794 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1795 p_full_path => l_path_name,
1796 p_string => 'Failed to create Accounting entries');
1797 RETURN FALSE;
1798 END IF;
1799
1800 -- bring the existing dists with their YTD values
1801 insert_data_det(p_adjustment_id =>l_adj_id,
1802 p_asset_id =>l_asset_hdr_rec.asset_id,
1803 p_distribution_id =>l_closing_det_balances.distribution_id,
1804 p_period_counter =>l_closing_det_balances.period_counter,
1805 p_book_type_code =>l_asset_hdr_rec.book_type_code,
1806 p_adjusted_cost =>0,
1807 p_net_book_value =>0,
1808 p_reval_reserve =>0,
1809 p_reval_reserve_gen_fund =>0,
1810 p_reval_reserve_backlog =>0,
1811 p_reval_reserve_net =>0,
1812 p_op_acct =>0,
1813 p_op_acct_net =>0,
1814 p_deprn_reserve =>0,
1815 p_deprn_reserve_backlog =>0,
1816 p_deprn_ytd =>l_closing_det_balances.DEPRN_YTD,
1817 p_deprn_period =>0,
1818 p_gen_fund_acc =>0,
1819 p_gen_fund_per =>0,
1820 p_current_reval_factor =>l_closing_det_balances.current_reval_factor,
1821 p_cumulative_reval_factor =>l_closing_det_balances.cumulative_reval_factor,
1822 p_reval_flag => 'N',
1823 p_op_acct_ytd =>l_closing_det_balances.OPERATING_ACCT_YTD,
1824 p_operating_acct_backlog =>0,
1825 p_last_reval_date =>l_closing_det_balances.last_reval_date);
1826
1827 -- enchancement 2480915 maintiain the ytd values ---
1828 OPEN c_get_deprn_dist (l_asset_hdr_rec.book_type_code,
1829 l_asset_hdr_rec.asset_id,
1830 l_closing_det_balances.distribution_id ,
1831 l_adjustment_id_out );
1832 FETCH c_get_deprn_dist INTO l_get_deprn_dist;
1833 IF c_get_deprn_dist%FOUND THEN
1834 -- Call to TBH for insert into IGI_IAC_ADJUSTMENTS
1835 IGI_IAC_FA_DEPRN_PKG.insert_row(
1836 x_rowid =>l_rowid,
1837 x_adjustment_id =>l_adj_id,
1838 x_book_type_code =>l_asset_hdr_rec.book_type_code,
1839 x_asset_id =>l_asset_hdr_rec.asset_id,
1840 x_distribution_id =>l_closing_det_balances.distribution_id,
1841 x_period_counter =>l_closing_det_balances.period_counter,
1842 x_deprn_period => 0,
1843 x_deprn_ytd =>l_get_deprn_dist.deprn_ytd,
1844 x_deprn_reserve => 0,
1845 x_active_flag => 'N',
1846 x_mode =>'R'
1847 );
1848
1849 END IF;
1850 CLOSE c_get_deprn_dist;
1851 -- enchancement 2480915 maintiain the ytd values ---
1852 -- before performing the new distribution entries update with new catgeory
1853 UPDATE igi_iac_transaction_headers
1854 SET category_id = l_asset_cat_rec_new.category_id
1855 WHERE adjustment_id = l_adj_id;
1856
1857 -- create entries in igi_iac_det_balances for the new dist
1858 insert_data_det(p_adjustment_id =>l_adj_id,
1859 p_asset_id =>l_asset_hdr_rec.asset_id,
1860 p_distribution_id =>l_impacted_dist.distribution_id,
1861 p_period_counter =>l_current_period_counter,
1862 p_book_type_code =>l_asset_hdr_rec.book_type_code,
1863 p_adjusted_cost =>0,
1864 p_net_book_value =>0,
1865 p_reval_reserve =>0,
1866 p_reval_reserve_gen_fund =>0,
1867 p_reval_reserve_backlog =>0,
1868 p_reval_reserve_net =>0,
1869 p_op_acct =>0,
1870 p_op_acct_net =>0,
1871 p_deprn_reserve =>0,
1872 p_deprn_reserve_backlog =>0,
1873 p_deprn_ytd =>0,
1874 p_deprn_period =>0,
1875 p_gen_fund_acc =>0,
1876 p_gen_fund_per =>0,
1877 p_current_reval_factor =>1,
1878 p_cumulative_reval_factor =>1,
1879 p_reval_flag => NULL,
1880 p_op_acct_ytd =>0,
1881 p_operating_acct_backlog =>0,
1882 p_last_reval_date =>l_closing_det_balances.last_reval_date);
1883
1884 -- enchancement 2480915 maintiain the FA YTD values ---
1885 OPEN c_get_deprn_dist (l_asset_hdr_rec.book_type_code,
1886 l_asset_hdr_rec.asset_id,
1887 l_old_dist.distribution_id ,
1888 l_adjustment_id_out );
1889 FETCH c_get_deprn_dist INTO l_get_deprn_dist;
1890 IF c_get_deprn_dist%FOUND THEN
1891 /* Call to TBH for insert into IGI_IAC_ADJUSTMENTS */
1892 IGI_IAC_FA_DEPRN_PKG.insert_row(
1893 x_rowid =>l_rowid,
1894 x_adjustment_id =>l_adj_id,
1895 x_book_type_code =>l_asset_hdr_rec.book_type_code,
1896 x_asset_id =>l_asset_hdr_rec.asset_id,
1897 x_distribution_id =>l_impacted_dist.distribution_id,
1898 x_period_counter =>l_current_period_counter,
1899 x_deprn_period => 0,
1900 x_deprn_ytd => 0,
1901 x_deprn_reserve => 0,
1902 x_active_flag => NULL,
1903 x_mode =>'R'
1904 );
1905
1906 END IF;
1907 CLOSE c_get_deprn_dist;
1908 -- enchancement 2480915 maintiain the ytd values ---
1909 END LOOP;
1910 /* End of loop for all active distributions */
1911
1912 /* Update the asset balanaces to zero in case new category has no indexed revalutions */
1913
1914 IF NOT (ALLOW_INDEX_REVAL_FLAG) THEN
1915 igi_iac_asset_balances_pkg.update_row(
1916 X_asset_id => l_asset_hdr_rec.asset_id,
1917 X_book_type_code => l_asset_hdr_rec.book_type_code,
1918 X_period_counter => l_current_period_counter ,
1919 X_net_book_value => 0,
1920 X_adjusted_cost => 0,
1921 X_operating_acct => 0,
1922 X_reval_reserve => 0,
1923 X_deprn_amount => 0,
1924 X_deprn_reserve => 0,
1925 X_backlog_deprn_reserve => 0,
1926 X_general_fund => 0,
1927 X_last_reval_date => Null,
1928 X_current_reval_factor => 1,
1929 X_cumulative_reval_factor => 1) ;
1930
1931 END IF;
1932
1933 /* get the ytd sum of the active distributiions*/
1934 l_deprn_ytd := 0;
1935 /* bring forward all the inactive distributions to the current adjustment */
1936 FOR l_get_all_prev_dist in get_all_prev_inactive_dist (l_prev_adjustment_id) LOOP
1937 IF l_get_all_prev_dist.active_flag = 'N' THEN
1938 /*create a record with new adjustment id */
1939 insert_data_det(p_adjustment_id =>l_adj_id,
1940 p_asset_id =>l_asset_hdr_rec.asset_id,
1941 p_distribution_id =>l_get_all_prev_dist.distribution_id,
1942 p_period_counter =>l_current_period_counter,
1943 p_book_type_code =>l_get_all_prev_dist.book_type_code,
1944 p_adjusted_cost =>l_get_all_prev_dist.adjustment_cost,
1945 p_net_book_value =>l_get_all_prev_dist.net_book_value,
1946 p_reval_reserve =>l_get_all_prev_dist.reval_reserve_cost,
1947 p_reval_reserve_gen_fund =>l_get_all_prev_dist.reval_reserve_gen_fund,
1948 p_reval_reserve_backlog =>l_get_all_prev_dist.reval_reserve_backlog,
1949 p_op_acct =>l_get_all_prev_dist.OPERATING_ACCT_COST,
1950 p_deprn_reserve =>l_get_all_prev_dist.deprn_reserve,
1951 p_deprn_reserve_backlog =>l_get_all_prev_dist.deprn_reserve_backlog,
1952 p_deprn_ytd => l_get_all_prev_dist.deprn_ytd,
1953 p_reval_reserve_net =>l_get_all_prev_dist.reval_reserve_net,
1954 p_op_acct_net =>l_get_all_prev_dist.OPERATING_ACCT_net,
1955 p_deprn_period =>l_get_all_prev_dist.deprn_period,
1956 p_gen_fund_acc =>l_get_all_prev_dist.general_fund_acc,
1957 p_gen_fund_per =>l_get_all_prev_dist.general_fund_per,
1958 p_current_reval_factor =>l_get_all_prev_dist.current_reval_factor,
1959 p_cumulative_reval_factor =>l_get_all_prev_dist.cumulative_reval_factor,
1960 p_reval_flag => l_get_all_prev_dist.active_flag,
1961 p_op_acct_ytd =>l_get_all_prev_dist.OPERATING_ACCT_YTD,
1962 p_operating_acct_backlog =>l_get_all_prev_dist.OPERATING_ACCT_BACKLOG,
1963 p_last_reval_date =>l_get_all_prev_dist.last_reval_date);
1964
1965 -- enchancement 2480915 maintiain the ytd values ---
1966 Open c_get_deprn_dist (l_asset_hdr_rec.book_type_code,
1967 l_asset_hdr_rec.asset_id,
1968 l_get_all_prev_dist.distribution_id ,
1969 l_adjustment_id_out );
1970 Fetch c_get_deprn_dist into l_get_deprn_dist;
1971 IF c_get_deprn_dist%FOUND THEN
1972 /* Call to TBH for insert into IGI_IAC_ADJUSTMENTS */
1973 IGI_IAC_FA_DEPRN_PKG.insert_row(
1974 x_rowid =>l_rowid,
1975 x_adjustment_id =>l_adj_id,
1976 x_book_type_code =>l_asset_hdr_rec.book_type_code,
1977 x_asset_id =>l_asset_hdr_rec.asset_id,
1978 x_distribution_id =>l_get_all_prev_dist.distribution_id,
1979 x_period_counter =>l_closing_det_balances.period_counter,
1980 x_deprn_period => l_get_deprn_dist.deprn_period,
1981 x_deprn_ytd =>l_get_deprn_dist.deprn_ytd,
1982 x_deprn_reserve =>l_get_deprn_dist.deprn_reserve,
1983 x_active_flag => 'N',
1984 x_mode =>'R'
1985 );
1986
1987 END IF;
1988 Close c_get_deprn_dist;
1989 -- enchancement 2480915 maintiain the ytd values ---
1990 ELSE
1991 l_deprn_ytd := l_deprn_ytd + l_get_all_prev_dist.deprn_ytd;
1992 END IF;
1993
1994 END LOOP; -- inactive distributions
1995
1996 RETURN(TRUE);
1997
1998
1999 EXCEPTION
2000 WHEN OTHERS THEN
2001 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
2002 l_mesg:=SQLERRM;
2003 FA_SRVR_MSG.Add_Message(
2004 Calling_FN => l_calling_function ,
2005 Name => 'IGI_IAC_EXCEPTION',
2006 TOKEN1 => 'PACKAGE',
2007 VALUE1 => 'Reclass-Do_No_Index_Reval',
2008 TOKEN2 => 'ERROR_MESSAGE',
2009 VALUE2 => l_mesg,
2010 APPLICATION => 'IGI');
2011
2012 RETURN(FALSE);
2013
2014 END; --do_no_index_reval
2015
2016
2017 -- ======================================================================
2018 -- SAME PRICE INDEX RECLASS
2019 -- function do all that needed for if both catgeories old and new has same
2020 -- price index
2021 -- ======================================================================
2022 FUNCTION do_same_price_index(p_event_id in number)
2023 Return boolean is
2024
2025 /* Cursor to select all distributions for an asset that are active and the distribution
2026 impacted by the reclass */
2027
2028 CURSOR c_all_dist IS
2029 SELECT distribution_id,
2030 transaction_header_id_in,
2031 units_assigned
2032 FROM fa_distribution_history
2033 WHERE asset_id=l_asset_hdr_rec.asset_id
2034 AND book_type_code=l_asset_hdr_rec.book_type_code
2035 AND transaction_header_id_out=l_trans_rec.transaction_header_id;
2036
2037 /* Cursor to select the distribution(s) undergoing the reclasss */
2038
2039 CURSOR c_old_dist(c_dist_id in FA_DISTRIBUTION_HISTORY.distribution_id%type) IS
2040 SELECT distribution_id
2041 FROM fa_distribution_history
2042 WHERE asset_id=l_asset_hdr_rec.asset_id
2043 AND book_type_code=l_asset_hdr_rec.book_type_code
2044 AND transaction_header_id_out=l_trans_rec.transaction_header_id
2045 AND distribution_id=c_dist_id;
2046
2047 /* Cursor to select the new distribution(s) undergoing the reclasss */
2048
2049 CURSOR c_new_dist(c_dist_id in FA_DISTRIBUTION_HISTORY.distribution_id%type) IS
2050 SELECT distribution_id
2051 FROM fa_distribution_history
2052 WHERE asset_id=l_asset_hdr_rec.asset_id
2053 AND book_type_code=l_asset_hdr_rec.book_type_code
2054 AND transaction_header_id_in=l_trans_rec.transaction_header_id
2055 AND distribution_id=c_dist_id;
2056
2057 /* Cursor to select the details of impacted distribution in this reclass*/
2058
2059 CURSOR c_impacted_dist(c_imp_dist_id FA_DISTRIBUTION_HISTORY.distribution_id%type) IS
2060 SELECT a.distribution_id,
2061 a.units_assigned
2062 FROM fa_distribution_history a, fa_distribution_history b
2063 WHERE a.asset_id=l_asset_hdr_rec.asset_id
2064 AND a.book_type_code=l_asset_hdr_rec.book_type_code
2065 AND a.asset_id = b.asset_id
2066 AND a.book_type_code = b.book_type_code
2067 AND a.transaction_header_id_in=l_trans_rec.transaction_header_id
2068 AND b.transaction_header_id_out = l_trans_rec.transaction_header_id
2069 AND nvl(a.location_id,-1) = nvl(b.location_id,-1)
2070 AND a.units_assigned = b.units_assigned
2071 AND b.distribution_id=c_imp_dist_id;
2072
2073 CURSOR c_impacted_dist_new(c_imp_dist_id FA_DISTRIBUTION_HISTORY.distribution_id%type ,
2074 c_imp_dist_id_new FA_DISTRIBUTION_HISTORY.distribution_id%type) IS
2075 SELECT a.distribution_id,
2076 a.units_assigned
2077 FROM fa_distribution_history a, fa_distribution_history b
2078 WHERE a.asset_id=l_asset_hdr_rec.asset_id
2079 AND a.book_type_code=l_asset_hdr_rec.book_type_code
2080 AND a.asset_id = b.asset_id
2081 AND a.book_type_code = b.book_type_code
2082 AND a.transaction_header_id_in=l_trans_rec.transaction_header_id
2083 AND b.transaction_header_id_out = l_trans_rec.transaction_header_id
2084 AND nvl(a.location_id,-1) = nvl(b.location_id,-1)
2085 AND a.units_assigned = b.units_assigned
2086 AND b.distribution_id=c_imp_dist_id
2087 AND a.distribution_id > c_imp_dist_id_new;
2088
2089 /* Cursor to select adjustment_id or the previous transaction */
2090
2091 CURSOR c_prev_data IS
2092 SELECT a.rowid,a.adjustment_id
2093 FROM igi_iac_transaction_headers a
2094 WHERE a.adjustment_id_out is null
2095 and a.asset_id = l_asset_hdr_rec.asset_id;
2096
2097
2098 /* Cursor to find the amounts that need to be transferres to the new dist
2099 created by reclass */
2100
2101 CURSOR c_amounts(c_period_counter in IGI_IAC_ASSET_BALANCES.period_counter%type) IS
2102 SELECT *
2103 FROM igi_iac_asset_balances
2104 WHERE asset_id=l_asset_hdr_rec.asset_id
2105 AND book_type_code=l_asset_hdr_rec.book_type_code
2106 AND period_counter=(select max(period_counter)
2107 from igi_iac_asset_balances
2108 WHERE asset_id=l_asset_hdr_rec.asset_id
2109 AND book_type_code=l_asset_hdr_rec.book_type_code)
2110 ;
2111
2112 /* Cursor to find period counter from which the reclass is valid
2113 Needed only in case of prior period transfers
2114
2115 CURSOR c_prior_period_counter(c_trx_date in FA_DEPRN_PERIODS.period_open_date%type) IS
2116 SELECT period_counter
2117 FROM fa_deprn_periods
2118 WHERE c_trx_date between period_open_date and period_close_date
2119 AND book_type_code=p_asset_hdr_rec.book_type_code; */
2120
2121 /* Cursor to find the total number of units for the asset itself ( active) */
2122
2123 CURSOR c_units IS
2124 SELECT sum(units_assigned)
2125 FROM fa_distribution_history
2126 WHERE asset_id=l_asset_hdr_rec.asset_id
2127 AND book_type_code=l_asset_hdr_rec.book_type_code
2128 AND transaction_header_id_out is null;
2129
2130 /* Cursor to find the total no of units involved in the transfer of old dist to new */
2131
2132 CURSOR c_dist_units IS
2133 SELECT sum(units_assigned)
2134 FROM fa_distribution_history
2135 WHERE asset_id=l_asset_hdr_rec.asset_id
2136 AND book_type_code=l_asset_hdr_rec.book_type_code
2137 AND transaction_header_id_in=l_trans_rec.transaction_header_id;
2138
2139 /* Cursor for ytd deprn */
2140
2141 CURSOR c_ytd_deprn(c_start_counter IGI_IAC_ASSET_BALANCES.period_counter%type
2142 ,c_current_counter IGI_IAC_ASSET_BALANCES.period_counter%type) IS
2143 SELECT nvl(sum(deprn_amount),0) deprn_amount
2144 FROM igi_iac_asset_balances
2145 WHERE book_type_code=l_asset_hdr_rec.book_type_code
2146 AND asset_id=l_asset_hdr_rec.asset_id
2147 AND period_counter between c_start_counter and c_current_counter;
2148
2149 /* Cursor for operating account ytd */
2150
2151 CURSOR c_op_acct_ytd(c_start_counter IGI_IAC_ASSET_BALANCES.period_counter%type
2152 ,c_current_counter IGI_IAC_ASSET_BALANCES.period_counter%type) IS
2153 SELECT nvl(sum(operating_acct),0) operating_acct
2154 FROM igi_iac_asset_balances
2155 WHERE book_type_code=l_asset_hdr_rec.book_type_code
2156 AND asset_id=l_asset_hdr_rec.asset_id
2157 AND period_counter between c_start_counter and c_current_counter;
2158
2159 /* Cursor to select the start period number for a given fiscal year */
2160
2161 CURSOR c_start_period_counter(c_fiscal_year FA_DEPRN_PERIODS.fiscal_year%type) IS
2162 SELECT (number_per_fiscal_year*c_fiscal_year)+1
2163 FROM fa_calendar_types
2164 WHERE calendar_type=(select deprn_calendar from fa_book_controls
2165 where book_type_code=l_asset_hdr_rec.book_type_code);
2166
2167 /* Cursor to select the adjustment_id from the sequence for a new record */
2168
2169 CURSOR c_adj_id IS
2170 SELECT igi_iac_transaction_headers_s.nextval
2171 FROM dual;
2172
2173 /* To select the asset's deprn_expense from fa_books */
2174
2175 CURSOR c_deprn_expense(c_period_counter FA_DEPRN_SUMMARY.period_counter%type) IS
2176 SELECT deprn_amount
2177 FROM fa_deprn_summary
2178 WHERE book_type_code = l_asset_hdr_rec.book_type_code
2179 AND period_counter = c_period_counter
2180 AND asset_id=l_asset_hdr_rec.asset_id;
2181
2182 /* Cursor to select the reval reserve backlog,op acct backlog and gen fund per for the dist */
2183
2184 CURSOR c_backlog_data(c_current_period_Counter fa_deprn_periods.period_counter%type) IS
2185 SELECT sum(nvl(iadb.reval_reserve_backlog,0)) reval_reserve_backlog,
2186 sum(nvl(iadb.operating_acct_backlog,0)) operating_acct_backlog,
2187 sum(nvl(iadb.general_fund_per,0)) general_fund_per
2188 FROM igi_iac_det_balances iadb,fa_distribution_history fdh
2189 WHERE iadb.book_type_code = l_asset_hdr_rec.book_type_code
2190 AND iadb.period_counter = c_current_period_counter
2191 AND iadb.asset_id=l_asset_hdr_rec.asset_id
2192 AND iadb.asset_id=fdh.asset_id
2193 AND iadb.book_type_code =fdh.book_type_code
2194 AND fdh.transaction_header_id_out=l_trans_rec.transaction_header_id
2195 AND fdh.distribution_id=iadb.distribution_id;
2196
2197 /* To find the asset number */
2198
2199 CURSOR c_asset_num IS
2200 SELECT asset_number
2201 FROM fa_additions
2202 WHERE asset_id=l_asset_hdr_rec.asset_id;
2203
2204 /* get the closing det balances record */
2205 CURSOR c_closing_det_balances( p_old_dist IGI_IAC_ADJUSTMENTS.distribution_id%type,
2206 p_adjustment_id IGI_IAC_ADJUSTMENTS.adjustment_id%type)is
2207 SELECT *
2208 FROM igi_iac_det_balances
2209 WHERE asset_id=l_asset_hdr_rec.asset_id
2210 AND book_type_code = l_asset_hdr_rec.book_type_code
2211 AND distribution_id = p_old_dist
2212 AND adjustment_id = p_adjustment_id;
2213
2214 /* Cursor to get incactive distributuiions to be carried forward */
2215 CURSOR get_all_prev_inactive_dist (C_prev_data_adjustment_id Number)
2216 is
2217 SELECT *
2218 FROM igi_iac_det_balances
2219 WHERE asset_id=l_asset_hdr_rec.asset_id
2220 AND book_type_code = l_asset_hdr_rec.book_type_code
2221 AND adjustment_id = C_prev_data_adjustment_id;
2222 -- AND nvl(active_flag,'Y') = 'N';
2223
2224
2225 /* Enhancemnet 2480915 Cursor to fetch the igi_fa_deprn deatils */
2226 CURSOR c_get_deprn_dist (c_book_type_code Varchar2,
2227 c_asset_id Number,
2228 c_distribution_id Number,
2229 c_adjustment_id Number) is
2230 SELECT *
2231 FROM IGI_IAC_FA_DEPRN
2232 WHERE book_type_code = c_book_type_code
2233 AND asset_id = c_asset_id
2234 AND Distribution_id = c_distribution_id
2235 AND adjustment_id = c_adjustment_id;
2236
2237
2238 -- l_asset_revalued c_asset_revalued%rowtype;
2239 l_impacted_dist c_impacted_dist%rowtype;
2240 l_amounts c_amounts%rowtype;
2241 l_backlog_data c_backlog_data%rowtype;
2242 l_get_deprn_dist c_get_deprn_dist%rowtype;
2243 l_old_dist c_old_dist%rowtype;
2244 l_dist_units FA_DISTRIBUTION_HISTORY.units_assigned%type;
2245 l_prd_rec IGI_IAC_TYPES.prd_rec;
2246 l_prd_rec_prior IGI_IAC_TYPES.prd_rec;
2247
2248 l_prev_data c_prev_data%rowtype;
2249 l_adj_id IGI_IAC_ADJUSTMENTS.adjustment_id%type;
2250 l_current_period_counter FA_DEPRN_PERIODS.period_counter%type;
2251 l_start_period_counter FA_DEPRN_PERIODS.period_counter%type;
2252
2253 l_asset_num FA_ADDITIONS.asset_number%type;
2254 l_units FA_DISTRIBUTION_HISTORY.units_assigned%type;
2255 l_reval_reserve IGI_IAC_DET_BALANCES.reval_reserve_cost%type;
2256 l_general_fund IGI_IAC_DET_BALANCES.general_fund_acc%type;
2257 l_Backlog_deprn_reserve IGI_IAC_DET_BALANCES.deprn_reserve_backlog%type;
2258 l_deprn_reserve IGI_IAC_DET_BALANCES.deprn_reserve%type;
2259 l_adjusted_cost IGI_IAC_DET_BALANCES.adjustment_cost%type;
2260 l_net_book_value IGI_IAC_DET_BALANCES.net_book_value%type;
2261 l_deprn_per IGI_IAC_DET_BALANCES.deprn_period%type;
2262 l_ytd_deprn IGI_IAC_DET_BALANCES.deprn_ytd%type;
2263 l_op_acct IGI_IAC_DET_BALANCES.operating_acct_ytd%type;
2264 l_op_acct_ytd IGI_IAC_DET_BALANCES.operating_acct_ytd%type;
2265 l_general_fund_per IGI_IAC_DET_BALANCES.general_fund_per%type;
2266 l_reval_reserve_backlog IGI_IAC_DET_BALANCES.reval_reserve_backlog%type;
2267 l_operating_acct_backlog IGI_IAC_DET_BALANCES.operating_acct_backlog%type;
2268 l_reval_ccid IGI_IAC_ADJUSTMENTS.code_combination_id%type;
2269 l_gen_fund_ccid IGI_IAC_ADJUSTMENTS.code_combination_id%type;
2270 l_backlog_ccid IGI_IAC_ADJUSTMENTS.code_combination_id%type;
2271 l_deprn_ccid IGI_IAC_ADJUSTMENTS.code_combination_id%type;
2272 l_cost_ccid IGI_IAC_ADJUSTMENTS.code_combination_id%type;
2273 l_prior_period_counter IGI_IAC_TRANSACTION_HEADERS.period_counter%type;
2274 l_historic_deprn_expense FA_DEPRN_SUMMARY.deprn_amount%type;
2275 l_Expense_diff IGI_IAC_DET_BALANCES.deprn_period%type;
2276 l_expense_ccid IGI_IAC_ADJUSTMENTS.code_combination_id%type;
2277 l_closing_det_balances IGI_IAC_DET_BALANCES%ROWTYPE;
2278 l_adjustment_id IGI_IAC_ADJUSTMENTS.adjustment_id%type;
2279 l_get_all_prev_dist get_all_prev_inactive_dist%ROWTYPE;
2280
2281 l_rowid rowid;
2282 l_return_value boolean;
2283 l_Prorate_factor number;
2284 l_deprn_expense number;
2285 x varchar2(100);
2286 prior_period Varchar2(100);
2287 l_mesg VARCHAR2(500);
2288 l_adjustment_id_out Number;
2289 l_prev_adjustment_id Number;
2290 l_transaction_type_code varchar2(50);
2291 l_transaction_id number ;
2292 l_mass_reference_id number ;
2293 l_adjustment_status varchar2(50);
2294 l_path_name VARCHAR2(150);
2295
2296 BEGIN
2297 l_transaction_type_code := Null;
2298 l_adjustment_status := null ;
2299 l_path_name := g_path||'do_same_price_index';
2300
2301 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2302 p_full_path => l_path_name,
2303 p_string => ' + Same price index begin ');
2304
2305 /* Store the previous transaction adjustment id */
2306 /* open c_prev_data;
2307 fetch c_prev_data into l_prev_data;
2308 IF NOT c_prev_data%FOUND THEN
2309 debug(0,' + Fetch the previous transaction adjustment id does not exisit');
2310 END IF;
2311
2312 close c_prev_data;*/
2313
2314 IF NOT igi_iac_common_utils.Get_Latest_Transaction (
2315 X_book_type_code => l_asset_hdr_rec.book_type_code,
2316 X_asset_id => l_asset_hdr_rec.asset_id,
2317 X_Transaction_Type_Code => l_transaction_type_code,
2318 X_Transaction_Id => l_transaction_id,
2319 X_Mass_Reference_ID => l_mass_reference_id,
2320 X_Adjustment_Id => l_adjustment_id_out,
2321 X_Prev_Adjustment_Id => l_prev_adjustment_id,
2322 X_Adjustment_Status => l_adjustment_status) THEN
2323 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
2324 p_full_path => l_path_name,
2325 p_string => '*** Error in fetching the latest transaction');
2326
2327 return FALSE;
2328 END IF;
2329
2330 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2331 p_full_path => l_path_name,
2332 p_string => ' + Fetch the previous transaction adjustment id '||l_prev_adjustment_id);
2333
2334
2335 /*Get the open period and create new transaction record in the transaction headers table */
2336 IF igi_iac_common_utils.get_open_period_info(l_asset_hdr_rec.book_type_code,l_prd_rec) THEN
2337 l_current_period_counter:=l_prd_rec.period_counter;
2338 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2339 p_full_path => l_path_name,
2340 p_string => ' + Current OPen Period counter' ||l_prd_rec.period_counter );
2341
2342 END IF;
2343
2344 /*Fetch the adjustment id from the sequence*/
2345 open c_adj_id;
2346 fetch c_adj_id into l_adj_id;
2347 close c_adj_id;
2348
2349 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2350 p_full_path => l_path_name,
2351 p_string => ' + before Insert into trans header' );
2352
2353 /*Insert into transaction headers */
2354 insert_data_trans_hdr(l_adj_id,
2355 l_trans_rec.transaction_header_id,
2356 NULL,
2357 l_trans_rec.transaction_type_code,
2358 l_trans_rec.transaction_date_entered,
2359 l_trans_rec.mass_reference_id,
2360 l_asset_hdr_rec.book_type_code,
2361 l_asset_hdr_rec.Asset_id,
2362 null,
2363 'COMPLETE',
2364 l_asset_cat_rec_old.category_id,
2365 -- l_asset_cat_rec_new.category_id,
2366 l_current_period_counter,
2367 p_event_id
2368 );
2369
2370 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2371 p_full_path => l_path_name,
2372 p_string => ' + After Insert into trans header' );
2373
2374 /* To select the backlog data for the old distribution to be prorated into new */
2375 -- Bug 2588308 sekhar --
2376 -- Need to close the previous transaction to pick the latest catgeory --
2377
2378 IGI_IAC_TRANS_HEADERS_PKG.update_row (
2379 x_prev_adjustment_id =>l_adjustment_id_out,
2380 x_adjustment_id =>l_adj_id,
2381 x_mode =>'R'
2382 );
2383
2384
2385 open c_backlog_data(l_current_period_counter);
2386 fetch c_backlog_data into l_backlog_data;
2387 close c_backlog_data;
2388
2389
2390 /* Distribution(s) involved in the transfer (old and new) and the non impacted ones*/
2391
2392 open c_amounts(l_current_period_counter);
2393 fetch c_amounts into l_amounts;
2394 close c_amounts;
2395
2396 open c_units;
2397 fetch c_units into l_units;
2398 close c_units;
2399
2400 open c_dist_units;
2401 fetch c_dist_units into l_dist_units;
2402 close c_dist_units;
2403 l_impacted_dist := Null;
2404 FOR l_all_dist in c_all_dist
2405 loop
2406
2407 /*l_reval_reserve:=l_amounts.reval_reserve;
2408 l_general_fund:=l_amounts.general_fund;
2409 l_Backlog_deprn_reserve:=l_amounts.backlog_deprn_reserve;
2410 l_deprn_reserve:=l_amounts.deprn_reserve;
2411 l_adjusted_cost:=l_amounts.adjusted_cost;
2412 l_net_book_value:=l_amounts.net_book_value;
2413 l_deprn_per:=l_amounts.deprn_amount;
2414 l_op_acct:=l_amounts.operating_acct;*/
2415
2416 open c_start_period_counter(l_prd_rec.fiscal_year);
2417 fetch c_start_period_counter into l_start_period_counter;
2418 close c_start_period_counter;
2419
2420
2421 open c_ytd_deprn(l_start_period_counter,l_prd_rec.period_counter);
2422 fetch c_ytd_deprn into l_ytd_deprn;
2423 close c_ytd_deprn;
2424
2425 open c_op_acct_ytd(l_start_period_counter,l_prd_rec.period_counter);
2426 fetch c_op_acct_ytd into l_op_acct_ytd;
2427 close c_op_acct_ytd;
2428
2429 open c_old_dist(l_all_dist.distribution_id);
2430 fetch c_old_dist into l_old_dist;
2431 close c_old_dist;
2432 If (l_impacted_dist.distribution_id IS NULL ) Then
2433 open c_impacted_dist(l_all_dist.distribution_id);
2434 fetch c_impacted_dist into l_impacted_dist;
2435 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2436 p_full_path => l_path_name,
2437 p_string => ' + impacted distribution id ' || l_all_dist.distribution_id);
2438 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2439 p_full_path => l_path_name,
2440 p_string => ' + new impacted distribution id ' || l_impacted_dist.distribution_id);
2441 close c_impacted_dist;
2442 Else
2443 open c_impacted_dist_new(l_all_dist.distribution_id,l_impacted_dist.distribution_id);
2444 fetch c_impacted_dist_new into l_impacted_dist;
2445 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2446 p_full_path => l_path_name,
2447 p_string => ' + impacted distribution id ' || l_all_dist.distribution_id);
2448 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2449 p_full_path => l_path_name,
2450 p_string => ' + new impacted distribution id ' || l_impacted_dist.distribution_id);
2451
2452 close c_impacted_dist_new;
2453 End if;
2454
2455 open c_old_dist(l_all_dist.distribution_id);
2456 fetch c_old_dist into l_old_dist;
2457 IF c_old_dist%NOTFOUND THEN
2458 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2459 p_full_path => l_path_name,
2460 p_string => ' No old distribution for asset found ' || l_old_dist.distribution_id);
2461
2462 close c_old_dist;
2463 return false;
2464 End if;
2465 close c_old_dist;
2466 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2467 p_full_path => l_path_name,
2468 p_string => ' + no old distribution for asset ' || l_old_dist.distribution_id);
2469
2470 /* get the closing det balances record form iac det balances */
2471 l_adjustment_id :=l_prev_adjustment_id;
2472 -- open c_closing_det_balances(l_old_dist,l_adjustment_id);
2473
2474 open c_closing_det_balances(l_old_dist.distribution_id,l_prev_adjustment_id);
2475 fetch c_closing_det_balances into l_closing_det_balances;
2476 IF c_closing_det_balances%NOTFOUND THEN
2477 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2478 p_full_path => l_path_name,
2479 p_string => ' + old distruibution id ' || l_old_dist.distribution_id);
2480 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2481 p_full_path => l_path_name,
2482 p_string => ' + adjustement id '||l_prev_adjustment_id);
2483 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2484 p_full_path => l_path_name,
2485 p_string => ' Could not find the IAC det balances record ');
2486
2487 close c_closing_det_balances;
2488 return false;
2489 End if;
2490 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2491 p_full_path => l_path_name,
2492 p_string => ' + old distruibution id ' || l_old_dist.distribution_id);
2493 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2494 p_full_path => l_path_name,
2495 p_string => ' + adjustement id '||l_prev_adjustment_id);
2496
2497 close c_closing_det_balances;
2498
2499
2500 l_closing_det_balances.adjustment_id := l_adj_id;
2501 l_closing_det_balances.period_counter := l_current_period_counter;
2502 l_deprn_reserve_amount:= 0;
2503
2504 Update igi_iac_transaction_headers
2505 set category_id = l_asset_cat_rec_old.category_id
2506 where adjustment_id = l_adj_id;
2507
2508 if create_iac_acctg ( l_closing_det_balances,TRUE,'OLD',p_event_id => p_event_id) Then
2509 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2510 p_full_path => l_path_name,
2511 p_string => '+Accounting entries created for old');
2512 else
2513 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2514 p_full_path => l_path_name,
2515 p_string => 'Failed to create Accounting entries');
2516
2517 return false;
2518 end if;
2519 /* IF (DIFF_PRICE_INDEX_FLAG) AND
2520 (ALLOW_INDEX_REVAL_FLAG OR ALLOW_PROF_REVAL_FLAG) THEN
2521 l_closing_det_balances.DEPRN_YTD := 0;
2522 END IF;*/
2523
2524
2525 insert_data_det(p_adjustment_id =>l_adj_id,
2526 p_asset_id =>l_asset_hdr_rec.asset_id,
2527 p_distribution_id =>l_closing_det_balances.distribution_id,
2528 p_period_counter =>l_closing_det_balances.period_counter,
2529 p_book_type_code =>l_asset_hdr_rec.book_type_code,
2530 p_adjusted_cost =>0,
2531 p_net_book_value =>0,
2532 p_reval_reserve =>0,
2533 p_reval_reserve_gen_fund =>0,
2534 p_reval_reserve_backlog =>0,
2535 p_reval_reserve_net =>0,
2536 p_op_acct =>0,
2537 p_op_acct_net =>0,
2538 p_deprn_reserve =>0,
2539 p_deprn_reserve_backlog =>0,
2540 p_deprn_ytd =>l_closing_det_balances.DEPRN_YTD,
2541 p_deprn_period =>0,
2542 p_gen_fund_acc =>0,
2543 p_gen_fund_per =>0,
2544 p_current_reval_factor =>l_closing_det_balances.current_reval_factor,
2545 p_cumulative_reval_factor =>l_closing_det_balances.cumulative_reval_factor,
2546 p_reval_flag => 'N',
2547 p_op_acct_ytd =>0,
2548 p_operating_acct_backlog =>0,
2549 p_last_reval_date =>l_closing_det_balances.last_reval_date);
2550
2551
2552 -- enchancement 2480915 maintiain the ytd values ---
2553 Open c_get_deprn_dist (l_asset_hdr_rec.book_type_code,
2554 l_asset_hdr_rec.asset_id,
2555 l_closing_det_balances.distribution_id ,
2556 l_adjustment_id_out );
2557 Fetch c_get_deprn_dist into l_get_deprn_dist;
2558 IF c_get_deprn_dist%FOUND THEN
2559 /* Call to TBH for insert into IGI_IAC_ADJUSTMENTS */
2560 IGI_IAC_FA_DEPRN_PKG.insert_row(
2561 x_rowid =>l_rowid,
2562 x_adjustment_id =>l_adj_id,
2563 x_book_type_code =>l_asset_hdr_rec.book_type_code,
2564 x_asset_id =>l_asset_hdr_rec.asset_id,
2565 x_distribution_id =>l_closing_det_balances.distribution_id,
2566 x_period_counter =>l_closing_det_balances.period_counter,
2567 x_deprn_period => 0,
2568 x_deprn_ytd =>l_get_deprn_dist.deprn_ytd,
2569 x_deprn_reserve => 0,
2570 x_active_flag => 'N',
2571 x_mode =>'R'
2572 );
2573
2574 END IF;
2575 Close c_get_deprn_dist;
2576 -- enchancement 2480915 maintiain the ytd values ---
2577
2578
2579
2580
2581 -- before performing the new distribution account entries update with new catgeory
2582 Update igi_iac_transaction_headers
2583 set category_id = l_asset_cat_rec_new.category_id
2584 where adjustment_id = l_adj_id;
2585
2586
2587 IF (DIFF_PRICE_INDEX_FLAG) AND
2588 (ALLOW_INDEX_REVAL_FLAG OR ALLOW_PROF_REVAL_FLAG) THEN
2589
2590 l_closing_det_balances.distribution_id := l_impacted_dist.distribution_id;
2591 if create_iac_acctg ( l_closing_det_balances,TRUE,'NEW',p_event_id => p_event_id) Then
2592 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2593 p_full_path => l_path_name,
2594 p_string => '+Accounting entries created for new ');
2595
2596 else
2597 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2598 p_full_path => l_path_name,
2599 p_string => 'Failed to create Accounting entries ');
2600
2601 return false;
2602 end if;
2603 end if;
2604 /* debug(0,'Create Det balances for new dist for diff price index');
2605 debug(0,'dist id'||l_closing_det_balances.distribution_id );
2606 debug(0,'adjustment id '|| l_adj_id);
2607 debug(0,'period counter'|| l_closing_det_balances.period_counter);*/
2608
2609
2610 /* insert_data_det(p_adjustment_id =>l_adj_id,
2611 p_asset_id =>l_asset_hdr_rec.asset_id,
2612 p_distribution_id =>l_impacted_dist.distribution_id,
2613 p_period_counter =>l_closing_det_balances.period_counter,
2614 p_book_type_code =>l_asset_hdr_rec.book_type_code,
2615 p_adjusted_cost =>0,
2616 p_net_book_value =>0,
2617 p_reval_reserve =>0,
2618 p_reval_reserve_gen_fund =>0,
2619 p_reval_reserve_backlog =>0,
2620 p_op_acct =>0,
2621 p_deprn_reserve =>0,
2622 p_deprn_reserve_backlog =>0,
2623 p_deprn_ytd =>0,
2624 p_deprn_period =>0,
2625 p_gen_fund_acc =>0,
2626 p_gen_fund_per =>0,
2627 p_current_reval_factor =>0,
2628 p_cumulative_reval_factor =>0,
2629 p_reval_flag => Null,
2630 p_op_acct_ytd =>0,
2631 p_operating_acct_backlog =>0,
2632 p_last_reval_date =>l_closing_det_balances.last_reval_date);
2633 END IF;*/
2634
2635 /* Create a det balances record for the new distribution only for same price index*/
2636
2637 IF (SAME_PRICE_INDEX_FLAG) AND
2638 (ALLOW_INDEX_REVAL_FLAG OR ALLOW_PROF_REVAL_FLAG) THEN
2639
2640
2641 l_closing_det_balances.distribution_id := l_impacted_dist.distribution_id;
2642 l_closing_det_balances.period_counter := l_current_period_counter;
2643 --accounting entry for YTD
2644 if create_iac_acctg ( l_closing_det_balances,TRUE,'NEW',p_event_id => p_event_id) Then
2645 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2646 p_full_path => l_path_name,
2647 p_string => '+Accounting entries created');
2648
2649 else
2650 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2651 p_full_path => l_path_name,
2652 p_string => 'Failed to create Accounting entries ');
2653
2654 return false;
2655 end if;
2656 l_closing_det_balances.deprn_YTD := 0;
2657
2658 insert_data_det(p_adjustment_id =>l_adj_id,
2659 p_asset_id =>l_asset_hdr_rec.asset_id,
2660 p_distribution_id =>l_impacted_dist.distribution_id,
2661 p_period_counter =>l_current_period_counter,
2662 p_book_type_code =>l_asset_hdr_rec.book_type_code,
2663 p_adjusted_cost =>l_closing_det_balances.adjustment_cost,
2664 p_net_book_value =>l_closing_det_balances.net_book_value,
2665 p_reval_reserve =>l_closing_det_balances.reval_reserve_cost,
2666 p_reval_reserve_gen_fund =>l_closing_det_balances.reval_reserve_gen_fund,
2667 p_reval_reserve_backlog =>l_closing_det_balances.reval_reserve_backlog,
2668 -- Bug 2767992 Sekhar Modified for reval reserve net
2669 p_reval_reserve_net =>l_closing_det_balances.reval_reserve_net,
2670 p_op_acct =>l_closing_det_balances.OPERATING_ACCT_COST,
2671 p_op_acct_net =>l_closing_det_balances.OPERATING_ACCT_net,
2672 p_deprn_reserve =>l_closing_det_balances.deprn_reserve,
2673 p_deprn_reserve_backlog =>l_closing_det_balances.deprn_reserve_backlog,
2674 p_deprn_ytd =>l_closing_det_balances.deprn_YTD,
2675 p_deprn_period =>l_closing_det_balances.deprn_period,
2676 p_gen_fund_acc =>l_closing_det_balances.general_fund_acc,
2677 p_gen_fund_per =>l_closing_det_balances.general_fund_per,
2678 p_current_reval_factor =>l_closing_det_balances.current_reval_factor,
2679 p_cumulative_reval_factor =>l_closing_det_balances.cumulative_reval_factor,
2680 p_reval_flag => Null,
2681 p_op_acct_ytd =>l_closing_det_balances.OPERATING_ACCT_YTD,
2682 p_operating_acct_backlog =>l_closing_det_balances.OPERATING_ACCT_BACKLOG,
2683 p_last_reval_date =>l_closing_det_balances.last_reval_date);
2684
2685
2686 -- enchancement 2480915 maintiain the ytd values ---
2687 Open c_get_deprn_dist (l_asset_hdr_rec.book_type_code,
2688 l_asset_hdr_rec.asset_id,
2689 l_old_dist.distribution_id ,
2690 l_adjustment_id_out );
2691 Fetch c_get_deprn_dist into l_get_deprn_dist;
2692 IF c_get_deprn_dist%FOUND THEN
2693 /* Call to TBH for insert into IGI_IAC_ADJUSTMENTS */
2694 IGI_IAC_FA_DEPRN_PKG.insert_row(
2695 x_rowid =>l_rowid,
2696 x_adjustment_id =>l_adj_id,
2697 x_book_type_code =>l_asset_hdr_rec.book_type_code,
2698 x_asset_id =>l_asset_hdr_rec.asset_id,
2699 x_distribution_id =>l_closing_det_balances.distribution_id,
2700 x_period_counter =>l_closing_det_balances.period_counter,
2701 x_deprn_period => l_get_deprn_dist.deprn_period,
2702 x_deprn_ytd =>0,
2703 x_deprn_reserve =>l_get_deprn_dist.deprn_reserve,
2704 x_active_flag => Null,
2705 x_mode =>'R'
2706 );
2707
2708 END IF;
2709 Close c_get_deprn_dist;
2710 -- enchancement 2480915 maintiain the ytd values ---
2711
2712
2713 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2714 p_full_path => l_path_name,
2715 p_string => ' + After insert into det balances ' || l_old_dist.distribution_id);
2716
2717 open c_closing_det_balances(l_impacted_dist.distribution_id,l_adj_id);
2718 fetch c_closing_det_balances into l_closing_det_balances;
2719 IF c_closing_det_balances%NOTFOUND THEN
2720 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2721 p_full_path => l_path_name,
2722 p_string => ' Could not find the IAC records ');
2723
2724 close c_closing_det_balances;
2725 return false;
2726 End if;
2727
2728 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2729 p_full_path => l_path_name,
2730 p_string => ' + old distruibution id ' || l_impacted_dist.distribution_id);
2731 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2732 p_full_path => l_path_name,
2733 p_string => ' + adjustement id '|| l_adj_id);
2734
2735
2736 close c_closing_det_balances;
2737
2738 ELSIF (NOT (ALLOW_INDEX_REVAL_FLAG OR ALLOW_PROF_REVAL_FLAG)) THEN
2739
2740 l_closing_det_balances.distribution_id := l_impacted_dist.distribution_id;
2741 insert_data_det(p_adjustment_id =>l_adj_id,
2742 p_asset_id =>l_asset_hdr_rec.asset_id,
2743 p_distribution_id =>l_impacted_dist.distribution_id,
2744 p_period_counter =>l_current_period_counter,
2745 p_book_type_code =>l_asset_hdr_rec.book_type_code,
2746 p_adjusted_cost =>0,
2747 p_net_book_value =>0,
2748 p_reval_reserve =>0,
2749 p_reval_reserve_net =>0,
2750 p_reval_reserve_gen_fund =>0,
2751 p_reval_reserve_backlog =>0,
2752 p_op_acct =>0,
2753 p_op_acct_net =>0,
2754 p_deprn_reserve =>0,
2755 p_deprn_reserve_backlog =>0,
2756 p_deprn_ytd =>0,
2757 p_deprn_period =>0,
2758 p_gen_fund_acc =>0,
2759 p_gen_fund_per =>0,
2760 p_current_reval_factor =>0,
2761 p_cumulative_reval_factor =>0,
2762 p_reval_flag => Null,
2763 p_op_acct_ytd =>0,
2764 p_operating_acct_backlog =>0,
2765 p_last_reval_date =>l_closing_det_balances.last_reval_date);
2766
2767
2768 END IF;
2769 /* End of loop for insert into det_balances table */
2770
2771 End Loop;
2772 /* End of loop for all active distributions */
2773
2774
2775 /* Update the asset balanaces to zero in case new category is has no prof and indexed revalutions */
2776
2777 IF NOT (ALLOW_INDEX_REVAL_FLAG OR ALLOW_PROF_REVAL_FLAG) THEN
2778 igi_iac_asset_balances_pkg.update_row(
2779 X_asset_id => l_asset_hdr_rec.asset_id,
2780 X_book_type_code => l_asset_hdr_rec.book_type_code,
2781 X_period_counter => l_current_period_counter ,
2782 X_net_book_value => 0,
2783 X_adjusted_cost => 0,
2784 X_operating_acct => 0,
2785 X_reval_reserve => 0,
2786 X_deprn_amount => 0,
2787 X_deprn_reserve => 0,
2788 X_backlog_deprn_reserve => 0,
2789 X_general_fund => 0,
2790 X_last_reval_date => Null,
2791 X_current_reval_factor => 0,
2792 X_cumulative_reval_factor => 0) ;
2793
2794 END IF;
2795
2796 /* get the ytd sum of the active distributiions*/
2797 l_deprn_ytd := 0;
2798 /* bring forward all the inactive distributions to the current adjustment */
2799 FOR l_get_all_prev_dist in get_all_prev_inactive_dist (l_prev_adjustment_id) LOOP
2800
2801
2802 IF l_get_all_prev_dist.active_flag = 'N' THEN
2803 /*create a record with new distribution id */
2804 /*IF (DIFF_PRICE_INDEX_FLAG) AND
2805 (ALLOW_INDEX_REVAL_FLAG OR ALLOW_PROF_REVAL_FLAG) THEN
2806 l_get_all_prev_dist.deprn_ytd := 0;
2807 END IF;*/
2808
2809 insert_data_det(p_adjustment_id =>l_adj_id,
2810 p_asset_id =>l_asset_hdr_rec.asset_id,
2811 p_distribution_id =>l_get_all_prev_dist.distribution_id,
2812 p_period_counter =>l_current_period_counter,
2813 p_book_type_code =>l_get_all_prev_dist.book_type_code,
2814 p_adjusted_cost =>l_get_all_prev_dist.adjustment_cost,
2815 p_net_book_value =>l_get_all_prev_dist.net_book_value,
2816 p_reval_reserve =>l_get_all_prev_dist.reval_reserve_cost,
2817 p_reval_reserve_gen_fund =>l_get_all_prev_dist.reval_reserve_gen_fund,
2818 p_reval_reserve_backlog =>l_get_all_prev_dist.reval_reserve_backlog,
2819 p_op_acct =>l_get_all_prev_dist.OPERATING_ACCT_COST,
2820 p_deprn_reserve =>l_get_all_prev_dist.deprn_reserve,
2821 p_deprn_reserve_backlog =>l_get_all_prev_dist.deprn_reserve_backlog,
2822 p_deprn_ytd => l_get_all_prev_dist.deprn_ytd,
2823 -- Bug 2767992 Sekhar Modified for reval reserve net
2824 p_reval_reserve_net =>l_get_all_prev_dist.reval_reserve_net,
2825 p_op_acct_net =>l_get_all_prev_dist.OPERATING_ACCT_net,
2826 -- p_deprn_ytd => 0,
2827 p_deprn_period =>l_get_all_prev_dist.deprn_period,
2828 p_gen_fund_acc =>l_get_all_prev_dist.general_fund_acc,
2829 p_gen_fund_per =>l_get_all_prev_dist.general_fund_per,
2830 p_current_reval_factor =>l_get_all_prev_dist.current_reval_factor,
2831 p_cumulative_reval_factor =>l_get_all_prev_dist.cumulative_reval_factor,
2832 p_reval_flag => l_get_all_prev_dist.active_flag,
2833 p_op_acct_ytd =>l_get_all_prev_dist.OPERATING_ACCT_YTD,
2834 p_operating_acct_backlog =>l_get_all_prev_dist.OPERATING_ACCT_BACKLOG,
2835 p_last_reval_date =>l_get_all_prev_dist.last_reval_date);
2836
2837 -- enchancement 2480915 maintiain the ytd values ---
2838 Open c_get_deprn_dist (l_asset_hdr_rec.book_type_code,
2839 l_asset_hdr_rec.asset_id,
2840 l_get_all_prev_dist.distribution_id ,
2841 l_adjustment_id_out );
2842 Fetch c_get_deprn_dist into l_get_deprn_dist;
2843 IF c_get_deprn_dist%FOUND THEN
2844 /* Call to TBH for insert into IGI_IAC_ADJUSTMENTS */
2845 IGI_IAC_FA_DEPRN_PKG.insert_row(
2846 x_rowid =>l_rowid,
2847 x_adjustment_id =>l_adj_id,
2848 x_book_type_code =>l_asset_hdr_rec.book_type_code,
2849 x_asset_id =>l_asset_hdr_rec.asset_id,
2850 x_distribution_id =>l_get_all_prev_dist.distribution_id,
2851 x_period_counter =>l_closing_det_balances.period_counter,
2852 x_deprn_period => l_get_deprn_dist.deprn_period,
2853 x_deprn_ytd =>l_get_deprn_dist.deprn_ytd,
2854 x_deprn_reserve =>l_get_deprn_dist.deprn_reserve,
2855 x_active_flag => 'N',
2856 x_mode =>'R'
2857 );
2858
2859 END IF;
2860 Close c_get_deprn_dist;
2861 -- enchancement 2480915 maintiain the ytd values ---
2862
2863
2864 ELSE
2865 l_deprn_ytd := l_deprn_ytd + l_get_all_prev_dist.deprn_ytd;
2866 END IF;
2867
2868 END LOOP;
2869
2870
2871
2872 /*Terminate the previous active row with the adjustment_id_out in transaction_headers table*/
2873 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2874 p_full_path => l_path_name,
2875 p_string => ' close the prevooius adjusment');
2876 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2877 p_full_path => l_path_name,
2878 p_string => 'old adjusment id ' || l_adjustment_id_out);
2879 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2880 p_full_path => l_path_name,
2881 p_string => 'current adjusment id ' || l_adj_id);
2882
2883 IGI_IAC_TRANS_HEADERS_PKG.update_row (
2884 x_prev_adjustment_id =>l_adjustment_id_out,
2885 x_adjustment_id =>l_adj_id,
2886 x_mode =>'R'
2887 );
2888
2889 RETURN(TRUE);
2890
2891
2892 EXCEPTION
2893 WHEN OTHERS THEN
2894 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
2895 l_mesg:=SQLERRM;
2896 FA_SRVR_MSG.Add_Message(
2897 Calling_FN => l_calling_function ,
2898 Name => 'IGI_IAC_EXCEPTION',
2899 TOKEN1 => 'PACKAGE',
2900 VALUE1 => 'Reclass',
2901 TOKEN2 => 'ERROR_MESSAGE',
2902 VALUE2 => l_mesg,
2903 APPLICATION => 'IGI');
2904
2905 RETURN(FALSE);
2906
2907 END; --do_same_price_index
2908
2909
2910 PROCEDURE Debug_Period(p_period igi_iac_types.prd_rec) IS
2911 l_path_name VARCHAR2(150);
2912 BEGIN
2913 l_path_name := g_path||'debug_period';
2914 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2915 p_full_path => l_path_name,
2916 p_string => ' Period counter :'||to_char(p_period.period_counter));
2917 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2918 p_full_path => l_path_name,
2919 p_string => ' Period Num :'||to_char(p_period.period_num));
2920 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2921 p_full_path => l_path_name,
2922 p_string => ' Fiscal Year :'||to_char(p_period.fiscal_year));
2923 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2924 p_full_path => l_path_name,
2925 p_string => ' Period Name :'||p_period.period_name);
2926
2927 END Debug_Period;
2928
2929 PROCEDURE Debug_Asset(p_asset igi_iac_types.iac_reval_input_asset) IS
2930 l_path_name VARCHAR2(150);
2931 BEGIN
2932 l_path_name := g_path||'debug_asset';
2933 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2934 p_full_path => l_path_name,
2935 p_string => ' Net book value :'||to_char(p_asset.net_book_value));
2936 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2937 p_full_path => l_path_name,
2938 p_string => ' Adjusted Cost :'||to_char(p_asset.adjusted_cost));
2939 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2940 p_full_path => l_path_name,
2941 p_string => ' Operating Account :'||to_char(p_asset.operating_acct));
2942 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2943 p_full_path => l_path_name,
2944 p_string => ' Reval Reserve :'||to_char(p_asset.reval_reserve));
2945 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2946 p_full_path => l_path_name,
2947 p_string => ' Deprn Amount :'||to_char(p_asset.deprn_amount));
2948 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2949 p_full_path => l_path_name,
2950 p_string => ' Deprn Reserve :'||to_char(p_asset.deprn_reserve));
2951 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2952 p_full_path => l_path_name,
2953 p_string => ' Backlog Deprn Reserve :'||to_char(p_asset.backlog_deprn_reserve));
2954 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2955 p_full_path => l_path_name,
2956 p_string => ' General Fund :'||to_char(p_asset.general_fund));
2957 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2958 p_full_path => l_path_name,
2959 p_string => ' Current Reval Factor :'||to_char(p_asset.current_reval_factor));
2960 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2961 p_full_path => l_path_name,
2962 p_string => ' Cumulative Reval Factor :'||to_char(p_asset.Cumulative_reval_factor));
2963
2964 END Debug_Asset;
2965
2966 -- ======================================================================
2967 -- DIFFERENT PRICE INDEX RECLASS
2968 -- function do all that needed for if both catgeories old and new has same
2969 -- price index
2970 -- ======================================================================
2971 FUNCTION Do_Revaluation_Catchup(
2972 p_book_type_code VARCHAR2,
2973 p_asset_id NUMBER,
2974 p_category_id NUMBER,
2975 p_calling_function VARCHAR2,
2976 p_event_id number
2977 ) return BOOLEAN IS
2978
2979 CURSOR c_allow_indexed_reval_flag IS
2980 SELECT allow_indexed_reval_flag
2981 FROM igi_iac_category_books
2982 WHERE book_type_code = p_book_type_code
2983 AND category_id = p_category_id;
2984
2985 CURSOR c_period_num_for_catchup IS
2986 SELECT period_num_for_catchup
2987 FROM igi_iac_book_controls
2988 WHERE book_type_code = p_book_type_code;
2989
2990 CURSOR c_prof_occ_reval_periods is
2991 SELECT revaluation_type,revaluation_period,revaluation_factor,new_cost,current_cost
2992 FROM igi_iac_revaluations rev,
2993 igi_iac_reval_asset_rules rul
2994 WHERE rev.revaluation_id = rul.revaluation_id
2995 AND rev.book_type_code = l_asset_hdr_rec.BOOK_TYPE_CODE
2996 AND rev.book_type_code = rul.book_type_code
2997 AND rul.revaluation_type in ('O','P')
2998 -- bug 2844230 Sekhar
2999 -- Not required unable to fecth previous revaluations
3000 -- AND rul.category_id = l_asset_cat_rec_new.category_id
3001 AND rul.asset_id = l_asset_hdr_rec.ASSET_ID
3002 order by revaluation_period;
3003
3004 /* Cursor to get fully reserved, fully retired info for the asset from FA */
3005 CURSOR c_fa_books(p_asset_id fa_books.asset_id%TYPE) IS
3006 SELECT salvage_value,cost
3007 FROM fa_books
3008 WHERE book_type_code = p_book_type_code
3009 AND asset_id = p_asset_id
3010 AND transactioN_header_id_out is NULL ;
3011
3012
3013 l_dpis_period_counter NUMBER;
3014 l_open_period igi_iac_types.prd_rec;
3015 l_period_info igi_iac_types.prd_rec;
3016 l_allow_indexed_reval_flag igi_iac_category_books.allow_indexed_reval_flag%TYPE;
3017 l_period_num_for_catchup igi_iac_book_controls.period_num_for_catchup%TYPE;
3018 l_idx1 BINARY_INTEGER;
3019 l_idx2 BINARY_INTEGER;
3020 l_reval_control igi_iac_types.iac_reval_control_tab;
3021 l_reval_asset_params igi_iac_types.iac_reval_asset_params_tab;
3022 l_reval_input_asset igi_iac_types.iac_reval_asset_tab;
3023 l_reval_output_asset igi_iac_types.iac_reval_asset_tab;
3024 l_reval_output_asset_mvmt igi_iac_types.iac_reval_asset_tab;
3025 l_reval_asset_rules igi_iac_types.iac_reval_asset_rules_tab;
3026 l_prev_rate_info igi_iac_types.iac_reval_rates_tab;
3027 l_curr_rate_info_first igi_iac_types.iac_reval_rates_tab;
3028 l_curr_rate_info_next igi_iac_types.iac_reval_rates_tab;
3029 l_curr_rate_info igi_iac_types.iac_reval_rates_tab;
3030 l_reval_exceptions igi_iac_types.iac_reval_exceptions_tab;
3031 l_fa_asset_info igi_iac_types.iac_reval_fa_asset_info_tab;
3032 l_reval_params igi_iac_types.iac_reval_params;
3033 l_reval_asset igi_iac_types.iac_reval_input_asset;
3034 l_reval_asset_out igi_iac_types.iac_reval_output_asset;
3035 l_revaluation_id igi_iac_revaluations.revaluation_id%TYPE;
3036 l_user_id NUMBER;
3037 l_login_id NUMBER;
3038 l_current_reval_factor igi_iac_asset_balances.current_reval_factor%TYPE;
3039 l_cumulative_reval_factor igi_iac_asset_balances.cumulative_reval_factor%TYPE;
3040 l_last_reval_period igi_iac_asset_balances.period_counter%TYPE;
3041 l_prof_occ_reval_periods c_prof_occ_reval_periods%ROWTYPE;
3042 l_rowid VARCHAR2(25);
3043 /* Bug 2961656 vgadde 08-Jul-2003 Start(1) */
3044 l_fa_deprn_amount_py NUMBER;
3045 l_fa_deprn_amount_cy NUMBER;
3046 l_last_asset_period NUMBER;
3047 l_salvage_value Number;
3048 l_cost Number;
3049 /* Bug 2961656 vgadde 08-Jul-2003 End(1) */
3050 l_path_name VARCHAR2(150);
3051 BEGIN
3052 l_idx1 := 0;
3053 l_idx2 := 0;
3054 l_user_id := fnd_global.user_id;
3055 l_login_id := fnd_global.login_id;
3056 l_path_name := g_path||'do_revaluation_catchup';
3057
3058 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3059 p_full_path => l_path_name,
3060 p_string => '=========================================');
3061 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3062 p_full_path => l_path_name,
3063 p_string => 'Start of IAC Prior Additions Processing....');
3064 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3065 p_full_path => l_path_name,
3066 p_string => '-----Parameters from FA code hook-----------');
3067 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3068 p_full_path => l_path_name,
3069 p_string => ' Book type code :'||p_book_type_code);
3070 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3071 p_full_path => l_path_name,
3072 p_string => ' Category Id :'||to_char(p_category_id));
3073 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3074 p_full_path => l_path_name,
3075 p_string => ' Asset Id :'||to_char(p_asset_id));
3076 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3077 p_full_path => l_path_name,
3078 p_string => '--------------------------------------------');
3079
3080
3081 OPEN c_allow_indexed_reval_flag;
3082 FETCH c_allow_indexed_reval_flag INTO l_allow_indexed_reval_flag;
3083 CLOSE c_allow_indexed_reval_flag;
3084
3085 /* Debug(' Allow Indexed reval flag :'||l_allow_indexed_reval_flag);
3086 IF (l_allow_indexed_reval_flag = 'N') THEN
3087 return TRUE;
3088 END IF;*/
3089
3090 IF NOT igi_iac_common_utils.get_dpis_period_counter(p_book_type_code,
3091 p_asset_id,
3092 l_dpis_period_counter) THEN
3093 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
3094 p_full_path => l_path_name,
3095 p_string => '*** Error in Fetching DPIS period counter');
3096 return FALSE;
3097 END IF;
3098
3099 IF NOT igi_iac_common_utils.get_open_period_info(p_book_type_code,
3100 l_open_period) THEN
3101 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
3102 p_full_path => l_path_name,
3103 p_string => '*** Error in Fetching Open period info for book');
3104 return FALSE;
3105 END IF;
3106
3107 OPEN c_period_num_for_catchup;
3108 FETCH c_period_num_for_catchup INTO l_period_num_for_catchup;
3109 CLOSE c_period_num_for_catchup;
3110
3111
3112 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3113 p_full_path => l_path_name,
3114 p_string => ' Revaluation catchup period for the book :'||to_char(l_period_num_for_catchup));
3115 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3116 p_full_path => l_path_name,
3117 p_string => '------- Revaluation catchup periods for the asset -------');
3118
3119 /* get the first revaluation period */
3120 OPEN c_prof_occ_reval_periods;
3121 FETCH c_prof_occ_reval_periods into l_prof_occ_reval_periods;
3122 IF NOT c_prof_occ_reval_periods%FOUND THEN
3123 l_prof_occ_reval_periods.revaluation_period :=l_open_period.period_counter-1;
3124 END IF;
3125 CLOSE c_prof_occ_reval_periods;
3126
3127 /* do revaluations till the first period of revaluations found in the asset rules*/
3128
3129 FOR l_period_counter IN l_dpis_period_counter..l_open_period.period_counter-1 LOOP
3130
3131 IF NOT igi_iac_common_utils.get_period_info_for_counter(p_book_type_code,
3132 l_period_counter,
3133 l_period_info) THEN
3134 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
3135 p_full_path => l_path_name,
3136 p_string => '*** Error in fetching period info');
3137
3138 return FALSE;
3139 END IF;
3140
3141
3142
3143 IF (l_period_num_for_catchup = l_period_info.period_num) THEN
3144 Debug_Period(l_period_info);
3145 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3146 p_full_path => l_path_name,
3147 p_string => 'catch up Reval period ' || to_char(l_period_counter));
3148
3149 l_idx1 := l_idx1 + 1;
3150 l_reval_control(l_idx1).revaluation_mode := 'L'; -- Live Mode
3151 l_reval_asset_rules(l_idx1).revaluation_type := 'O'; -- Occasional
3152 l_reval_asset_params(l_idx1).asset_id := p_asset_id;
3153 l_reval_asset_params(l_idx1).category_id := p_category_id;
3154 l_reval_asset_params(l_idx1).book_type_code := p_book_type_code;
3155 l_reval_asset_params(l_idx1).period_counter := l_period_counter;
3156
3157 END IF;
3158 END LOOP;
3159
3160 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3161 p_full_path => l_path_name,
3162 p_string => '------------------------------------------------------');
3163
3164 /* Get the number of professional revaluations done and intialize the structure */
3165 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3166 p_full_path => l_path_name,
3167 p_string => 'Get the number of professional revaluations done and intialize the structure');
3168
3169 IF l_idx1 = 0 then
3170 FOR l_period_counter IN l_dpis_period_counter..l_open_period.period_counter-1 LOOP
3171
3172 IF NOT igi_iac_common_utils.get_period_info_for_counter(p_book_type_code,
3173 l_period_counter,
3174 l_period_info) THEN
3175 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
3176 p_full_path => l_path_name,
3177 p_string => '*** Error in fetching period info');
3178
3179 return FALSE;
3180 END IF;
3181
3182 FOR l_prof_occ_reval_periods in c_prof_occ_reval_periods LOOP
3183
3184 IF (l_prof_occ_reval_periods.revaluation_period = l_period_counter) THEN
3185 Debug_Period(l_period_info);
3186 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3187 p_full_path => l_path_name,
3188 p_string => ' Reval period ' || to_char(l_period_counter));
3189 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3190 p_full_path => l_path_name,
3191 p_string => ' Reval type ' || l_prof_occ_reval_periods.revaluation_type);
3192 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3193 p_full_path => l_path_name,
3194 p_string => ' Reval new cost '|| l_prof_occ_reval_periods.new_cost);
3195 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3196 p_full_path => l_path_name,
3197 p_string => ' Reval current cost '|| l_prof_occ_reval_periods.current_cost);
3198
3199 l_idx1 := l_idx1 + 1;
3200 l_reval_control(l_idx1).revaluation_mode := 'L'; -- Live Mode
3201 l_reval_asset_rules(l_idx1).revaluation_type :=l_prof_occ_reval_periods.revaluation_type; -- Occasional
3202 l_reval_asset_params(l_idx1).asset_id := p_asset_id;
3203 l_reval_asset_params(l_idx1).category_id := p_category_id;
3204 l_reval_asset_params(l_idx1).book_type_code := p_book_type_code;
3205 l_reval_asset_params(l_idx1).period_counter := l_period_counter;
3206 l_reval_asset_rules(l_idx1).new_cost := l_prof_occ_reval_periods.new_cost;
3207 l_reval_asset_rules(l_idx1).current_cost :=l_prof_occ_reval_periods.current_cost;
3208
3209 END IF;
3210 END LOOP;
3211 END LOOP;
3212 END IF;
3213
3214 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3215 p_full_path => l_path_name,
3216 p_string => '------------------------------------------------------');
3217 /* Get the number of professional revaluations done and intialize the structure */
3218
3219
3220 IF (l_idx1 = 0) THEN /* No catch-up required */
3221 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3222 p_full_path => l_path_name,
3223 p_string => ' No revaluation catchup periods found');
3224
3225 return TRUE;
3226 END IF;
3227
3228 /* Bug 2961656 vgadde 08-Jul-2003 Start(2) */
3229 IF NOT igi_iac_catchup_pkg.get_FA_Deprn_Expense(p_asset_id,
3230 p_book_type_code,
3231 l_open_period.period_counter,
3232 'RECLASS',
3233 NULL,
3234 NULL,
3235 l_fa_deprn_amount_py,
3236 l_fa_deprn_amount_cy,
3237 l_last_asset_period) THEN
3238 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
3239 p_full_path => l_path_name,
3240 p_string => '*** Error in get_FA_Deprn_Expense function');
3241
3242 return FALSE;
3243 END IF;
3244 /* Bug 2961656 vgadde 08-Jul-2003 End(2) */
3245 /*Salavge value correction*/
3246 -- resreve
3247 OPEN c_fa_books(p_asset_id);
3248 FETCH c_fa_books into l_salvage_value,
3249 l_cost;
3250 CLOSE c_fa_books;
3251 IF l_salvage_value <> 0 Then
3252 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3253 p_full_path => l_path_name,
3254 p_string => '+Salavge Value Correction deprn_amount_py before :' ||l_fa_deprn_amount_py);
3255
3256 -- deprn amount l_fa_deprn_amount_py
3257 IF NOT igi_iac_salvage_pkg.correction(p_asset_id => p_asset_id,
3258 P_book_type_code =>p_book_type_code,
3259 P_value=>l_fa_deprn_amount_py,
3260 P_cost=>l_cost,
3261 P_salvage_value=>l_salvage_value,
3262 P_calling_program=>'RECLASS') THEN
3263 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
3264 p_full_path => l_path_name,
3265 p_string => '+Salvage Value Correction Failed : ');
3266
3267 return false;
3268 END IF;
3269
3270 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3271 p_full_path => l_path_name,
3272 p_string => '+Salavge Value Correction deprn_amount_py after :' ||l_fa_deprn_amount_py );
3273 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3274 p_full_path => l_path_name,
3275 p_string => '+Salavge Value Correction deprn_amount_cy before :' ||l_fa_deprn_amount_cy);
3276
3277
3278 -- deprn l_fa_deprn_amount_cy
3279 IF NOT igi_iac_salvage_pkg.correction(p_asset_id => p_asset_id,
3280 P_book_type_code =>p_book_type_code,
3281 P_value=>l_fa_deprn_amount_cy,
3282 P_cost=>l_cost,
3283 P_salvage_value=>l_salvage_value,
3284 P_calling_program=>'RECLASS') THEN
3285
3286 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
3287 p_full_path => l_path_name,
3288 p_string => '+Salvage Value Correction Failed : ');
3289
3290 return false;
3291 END IF;
3292
3293 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3294 p_full_path => l_path_name,
3295 p_string => '+Salavge Value Correction deprn_amount_cy after :' ||l_fa_deprn_amount_cy);
3296
3297 END IF;
3298 /*salvage value correction*/
3299
3300 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3301 p_full_path => l_path_name,
3302 p_string => ' Calling Revaluation Initialization program ');
3303
3304 IF NOT igi_iac_catchup_pkg.do_reval_init_struct(l_open_period.period_counter,
3305 l_reval_control,
3306 l_reval_asset_params,
3307 l_reval_input_asset,
3308 l_reval_output_asset,
3309 l_reval_output_asset_mvmt,
3310 l_reval_asset_rules,
3311 l_prev_rate_info,
3312 l_curr_rate_info_first,
3313 l_curr_rate_info_next,
3314 l_curr_rate_info,
3315 l_reval_exceptions,
3316 l_fa_asset_info,
3317 l_fa_deprn_amount_py, -- For bug 2961656
3318 l_fa_deprn_amount_cy, -- For bug 2961656
3319 l_last_asset_period, -- For bug 2961656
3320 'RECLASS') THEN
3321
3322 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3323 p_full_path => l_path_name,
3324 p_string => '*** Error in catchup pkg for revaluation initialization');
3325
3326 return FALSE;
3327 END IF;
3328
3329 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3330 p_full_path => l_path_name,
3331 p_string => ' Back from Revaluation Initialization');
3332
3333 FOR l_idx2 IN 1..l_idx1 LOOP
3334
3335 IF (l_idx2 <> 1) THEN
3336
3337 l_reval_asset := l_reval_output_asset(l_idx2 - 1);
3338
3339 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3340 p_full_path => l_path_name,
3341 p_string => ' Doing depreciation catchup for the periods between revaluation');
3342
3343 IF NOT igi_iac_catchup_pkg.do_deprn_catchup(l_reval_asset_params(l_idx2 - 1).period_counter +1,
3344 l_reval_asset_params(l_idx2).period_counter +1,
3345 l_open_period.period_counter,
3346 FALSE,
3347 'RECLASS',
3348 l_fa_deprn_amount_py, -- For bug 2961656
3349 l_fa_deprn_amount_cy, -- For bug 2961656
3350 l_last_asset_period, -- For bug 2961656
3351 NULL, -- For bug 2961656
3352 NULL, -- For bug 2961656
3353 l_reval_asset ,
3354 p_event_id => p_event_id)THEN
3355 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
3356 p_full_path => l_path_name,
3357 p_string => '*** Error in depreciation catchup');
3358
3359 return FALSE;
3360 END IF;
3361
3362 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3363 p_full_path => l_path_name,
3364 p_string => 'Back from depreciation catchup');
3365
3366
3367 l_current_reval_factor := l_reval_input_asset(l_idx2).current_reval_factor;
3368 l_cumulative_reval_factor := l_reval_input_asset(l_idx2).cumulative_reval_factor;
3369 l_reval_input_asset(l_idx2) := l_reval_asset;
3370 l_reval_input_asset(l_idx2).current_reval_factor := l_current_reval_factor;
3371 l_reval_input_asset(l_idx2).cumulative_reval_factor := l_cumulative_reval_factor;
3372 END IF;
3373
3374 IF (l_idx2 = l_idx1) THEN
3375 /* Last revaluation - Insert records into revaluation tables*/
3376 /* SELECT igi_iac_revaluations_s.nextval
3377 INTO l_revaluation_id
3378 FROM DUAL;
3379 Debug(0,'Last Revaluation - Inseting into igi_iac_revaluations');
3380 Debug(0,'Revaluation Id :'||to_char(l_revaluation_id));
3381
3382
3383
3384 INSERT INTO igi_iac_revaluations
3385 (revaluation_id,
3386 book_type_code,
3387 revaluation_date,
3388 revaluation_period,
3389 status,
3390 reval_request_id,
3391 create_request_id,
3392 calling_program,
3393 last_update_date,
3394 created_by,
3395 last_update_login,
3396 last_updated_by,
3397 creation_date)
3398 VALUES (l_revaluation_id,
3399 p_book_type_code,
3400 sysdate,
3401 l_reval_asset_params(l_idx1).period_counter,
3402 'NEW',
3403 NULL,
3404 NULL,
3405 'RECLASS',
3406 sysdate,
3407 l_user_id,
3408 l_login_id,
3409 l_user_id,
3410 sysdate);
3411
3412 Debug(0,'Inserting into igi_iac_reval_asset_rules');
3413 INSERT INTO igi_iac_reval_asset_rules
3414 (revaluation_id,
3415 book_type_code,
3416 category_id,
3417 asset_id,
3418 revaluation_factor,
3419 revaluation_type,
3420 new_cost,
3421 current_cost,
3422 selected_for_reval_flag,
3423 selected_for_calc_flag,
3424 created_by,
3425 creation_date,
3426 last_update_login,
3427 last_update_date,
3428 last_updated_by,
3429 allow_prof_update)
3430 VALUES (l_revaluation_id,
3431 l_reval_asset_params(l_idx1).book_type_code,
3432 l_reval_asset_params(l_idx1).category_id,
3433 l_reval_asset_params(l_idx1).asset_id,
3434 l_reval_asset_rules(l_idx1).revaluation_factor,
3435 l_reval_asset_rules(l_idx1).revaluation_type,
3436 l_reval_asset_rules(l_idx1).new_cost,
3437 l_reval_input_asset(l_idx2).adjusted_cost,
3438 'Y',
3439 'N',
3440 l_user_id,
3441 sysdate,
3442 l_login_id,
3443 sysdate,
3444 l_user_id,
3445 NULL);*/
3446
3447 /* Last revaluation - Insert records into revaluation tables*/
3448
3449 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3450 p_full_path => l_path_name,
3451 p_string => ' Last Revaluation - Inserting into igi_iac_revaluations');
3452
3453
3454 l_rowid := NULL;
3455 l_revaluation_id := NULL;
3456
3457 igi_iac_revaluations_pkg.insert_row
3458 (l_rowid,
3459 l_revaluation_id,
3460 p_book_type_code,
3461 sysdate,
3462 l_reval_asset_params(l_idx1).period_counter,
3463 'NEW',
3464 NULL,
3465 NULL,
3466 'ADDITION',
3467 X_event_id => p_event_id);
3468
3469 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3470 p_full_path => l_path_name,
3471 p_string => ' Revaluation Id :'||to_char(l_revaluation_id));
3472
3473 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3474 p_full_path => l_path_name,
3475 p_string => ' Inserting into igi_iac_reval_asset_rules');
3476
3477 l_rowid := NULL;
3478 igi_iac_reval_asset_rules_pkg.insert_row
3479 (l_rowid,
3480 l_revaluation_id,
3481 l_reval_asset_params(l_idx1).book_type_code,
3482 l_reval_asset_params(l_idx1).category_id,
3483 l_reval_asset_params(l_idx1).asset_id,
3484 l_reval_asset_rules(l_idx1).revaluation_factor,
3485 l_reval_asset_rules(l_idx1).revaluation_type,
3486 l_reval_asset_rules(l_idx1).new_cost,
3487 l_reval_input_asset(l_idx2).adjusted_cost,
3488 'Y',
3489 'N',
3490 NULL);
3491
3492
3493 l_last_reval_period := l_reval_asset_params(l_idx2).period_counter;
3494 l_reval_asset_params(l_idx2).period_counter := l_open_period.period_counter ;
3495 l_reval_input_asset(l_idx2).period_counter := l_open_period.period_counter ;
3496 l_reval_asset_params(l_idx2).revaluation_id := l_revaluation_id;
3497 l_reval_asset_rules(l_idx2).revaluation_id := l_revaluation_id;
3498
3499 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3500 p_full_path => l_path_name,
3501 p_string => ' Period counter passed to Reval CRUD :'||to_char(l_open_period.period_counter ));
3502
3503
3504 END IF;
3505
3506 l_reval_params.reval_control := l_reval_control(l_idx2);
3507 l_reval_params.reval_asset_params := l_reval_asset_params(l_idx2);
3508 l_reval_params.reval_input_asset := l_reval_input_asset(l_idx2);
3509 l_reval_params.reval_output_asset := l_reval_input_asset(l_idx2);
3510 l_reval_params.reval_output_asset_mvmt := l_reval_output_asset_mvmt(l_idx2);
3511 l_reval_params.reval_asset_rules := l_reval_asset_rules(l_idx2);
3512 l_reval_params.reval_prev_rate_info := l_prev_rate_info(l_idx2);
3513 l_reval_params.reval_curr_rate_info_first := l_curr_rate_info_first(l_idx2);
3514 l_reval_params.reval_curr_rate_info_next := l_curr_rate_info_next(l_idx2);
3515 l_reval_params.reval_asset_exceptions := l_reval_exceptions(l_idx2);
3516 l_reval_params.fa_asset_info := l_fa_asset_info(l_idx2);
3517
3518 /* call revaluation processing function here */
3519
3520 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3521 p_full_path => l_path_name,
3522 p_string => ' Input asset balances to revaluation program');
3523
3524
3525 Debug_Asset(l_reval_input_asset(l_idx2));
3526
3527 IF NOT igi_iac_reval_wrapper.do_reval_calc_asset(l_reval_params,
3528 l_reval_asset_out) THEN
3529 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
3530 p_full_path => l_path_name,
3531 p_string => '*** Error in Revaluation Program');
3532
3533 return FALSE;
3534 END IF;
3535
3536 l_current_reval_factor := l_reval_output_asset(l_idx2).current_reval_factor;
3537 l_cumulative_reval_factor := l_reval_output_asset(l_idx2).cumulative_reval_factor;
3538 l_reval_output_asset(l_idx2) := l_reval_asset_out;
3539 l_reval_output_asset(l_idx2).current_reval_factor := l_current_reval_factor;
3540 l_reval_output_asset(l_idx2).cumulative_reval_factor := l_cumulative_reval_factor;
3541
3542 /* Bug 2425856 vgadde 20/06/2002 Start(1) */
3543 BEGIN
3544 IF (l_idx2 = l_idx1) THEN /* Last Revaluation */
3545 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3546 p_full_path => l_path_name,
3547 p_string => ' Last revaluation period :'||to_char(l_last_reval_period));
3548 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3549 p_full_path => l_path_name,
3550 p_string => ' Revaluation Id :'||to_char(l_revaluation_id));
3551
3552
3553 UPDATE igi_iac_revaluation_rates
3554 SET period_counter = l_last_reval_period
3555 WHERE revaluation_id = l_revaluation_id
3556 AND asset_id = p_asset_id
3557 AND book_type_code = p_book_type_code;
3558
3559 IF SQL%FOUND then
3560 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3561 p_full_path => l_path_name,
3562 p_string => ' Records in reval rates updated for correct period');
3563 ELSE
3564 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3565 p_full_path => l_path_name,
3566 p_string => '*** No record found in reval rates table to update');
3567
3568 return FALSE;
3569 END IF;
3570 END IF;
3571 END;
3572 /* Bug 2425856 vgadde 20/06/2002 End(1) */
3573
3574 END LOOP;
3575
3576 IF (l_last_reval_period < l_open_period.period_counter) THEN
3577
3578 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3579 p_full_path => l_path_name,
3580 p_string => ' Doing the final catchup for depreciation');
3581
3582
3583 l_reval_asset := l_reval_output_asset(l_idx1);
3584 IF NOT igi_iac_catchup_pkg.do_deprn_catchup(l_last_reval_period + 1,
3585 l_open_period.period_counter,
3586 l_open_period.period_counter,
3587 TRUE,
3588 'RECLASS',
3589 l_fa_deprn_amount_py, -- For bug 2961656
3590 l_fa_deprn_amount_cy, -- For bug 2961656
3591 l_last_asset_period, -- For bug 2961656
3592 NULL, -- For bug 2961656
3593 NULL, -- For bug 2961656
3594 l_reval_asset ,
3595 p_event_id => p_event_id)THEN
3596 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
3597 p_full_path => l_path_name,
3598 p_string => '*** Error in depreciation catchup for final run' );
3599
3600 return FALSE;
3601 END IF;
3602
3603 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3604 p_full_path => l_path_name,
3605 p_string => ' Output from final catchup');
3606
3607 Debug_Asset(l_reval_asset);
3608
3609 END IF;
3610
3611 -- Code added by Venkat Gadde
3612 BEGIN
3613 UPDATE IGI_IAC_ADJUSTMENTS
3614 SET EVENT_ID = P_EVENT_ID
3615 WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
3616 AND ASSET_ID = P_ASSET_ID
3617 AND PERIOD_COUNTER = l_open_period.period_counter
3618 AND ADJUSTMENT_ID IN (SELECT ADJUSTMENT_ID
3619 FROM IGI_IAC_TRANSACTION_HEADERS
3620 WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
3621 AND ASSET_ID = P_ASSET_ID
3622 AND PERIOD_COUNTER = l_open_period.period_counter
3623 AND TRANSACTION_TYPE_CODE = 'RECLASS'
3624 AND EVENT_ID IS NULL);
3625
3626 UPDATE IGI_IAC_TRANSACTION_HEADERS
3627 SET EVENT_ID = P_EVENT_ID
3628 WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
3629 AND ASSET_ID = P_ASSET_ID
3630 AND PERIOD_COUNTER = l_open_period.period_counter
3631 AND TRANSACTION_TYPE_CODE = 'RECLASS'
3632 AND EVENT_ID IS NULL;
3633
3634 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3635 p_full_path => l_path_name,
3636 p_string => ' Updated all reclass trasactions with event_id');
3637 EXCEPTION
3638 WHEN NO_DATA_FOUND THEN
3639 NULL;
3640 END;
3641 -- End of code added by Venkat Gadde
3642
3643 return TRUE;
3644
3645 EXCEPTION
3646 WHEN OTHERS THEN
3647 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
3648 return FALSE;
3649
3650
3651 END Do_Revaluation_Catchup;
3652
3653
3654 FUNCTION Do_Diff_Price_Index(p_event_id number)
3655 Return BOOLEAN IS
3656
3657 /* bug 2502128 need to update the reval rates ..only one record should have staus = 'Y' for an asset */
3658 Cursor C_Reval_Rates is
3659 SELECT max(adjustment_id)
3660 FROM igi_iac_transaction_headers ith
3661 WHERE ith.book_type_code =l_asset_hdr_rec.book_type_code
3662 AND ith.asset_id = l_asset_hdr_rec.asset_id
3663 AND (ith.transaction_type_code = 'RECLASS' AND ith.Transaction_sub_type ='REVALUATION');
3664
3665 l_units Number;
3666 l_get_latest_adjustment_id number;
3667 l_adjustment_id_out number;
3668 l_transaction_type_code varchar2(50);
3669 l_transaction_id number ;
3670 l_mass_reference_id number;
3671 l_adjustment_status varchar2(50);
3672 l_path_name VARCHAR2(150);
3673
3674 BEGIN
3675 l_transaction_type_code := Null;
3676 l_adjustment_status := null ;
3677 l_path_name := g_path||'do_diff_price_index';
3678
3679 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3680 p_full_path => l_path_name,
3681 p_string => 'Enter The Do different price index');
3682
3683 l_get_latest_adjustment_id:=0;
3684 /* Create a new transaction header for RECLASS reversal of the det balances and
3685 create the adjustments accordingly */
3686 /* to do this call the same price index */
3687 IF Do_same_price_index(p_event_id) then
3688 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3689 p_full_path => l_path_name,
3690 p_string => '+DIFF PRICE INDEX RECLASS REVERSAL SUCCESS');
3691 ELSE
3692 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3693 p_full_path => l_path_name,
3694 p_string => 'DIFFERENT PRICE INDEX RECLASS NEGATION FAILED ');
3695
3696 return false;
3697 End IF;
3698
3699 /* From the dpis to the current period to do the revalutions and professional revalutions
3700 call the catchup program*/
3701 /*revaluation program will create the entries reuiqred in det balanes and asset balances*/
3702
3703 IF (DIFF_PRICE_INDEX_FLAG) AND
3704 (ALLOW_INDEX_REVAL_FLAG OR ALLOW_PROF_REVAL_FLAG) THEN
3705
3706 IF NOT Do_Revaluation_Catchup(l_asset_hdr_rec.book_type_code,
3707 l_asset_hdr_rec.asset_id,
3708 l_asset_cat_rec_new.category_id,
3709 'RECLASS',
3710 p_event_id => p_event_id)THEN
3711
3712 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3713 p_full_path => l_path_name,
3714 p_string => 'FAILED TO DO DIFFERENT PRICE INDEX RECLASSIFICATION');
3715
3716 return false;
3717 End IF;
3718 END IF;
3719
3720 /* bug 2502128 need to update the reval rates ..only one record should have staus = 'Y' for an asset */
3721 OPEN C_Reval_Rates;
3722 FETCH C_Reval_Rates into l_get_latest_adjustment_id;
3723 CLOSE C_Reval_Rates;
3724 IF NOT IGI_IAC_REVAL_CRUD.update_reval_rates (fp_adjustment_id => l_get_latest_adjustment_id) THEN
3725 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3726 p_full_path => l_path_name,
3727 p_string => 'FAILED UPDATE REVAL RATES');
3728 END IF;
3729
3730 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3731 p_full_path => l_path_name,
3732 p_string => 'SUCESSFULL DIFF PRICE INDEX');
3733
3734 return True;
3735 END;-- do diff price index
3736
3737
3738
3739
3740 -- ======================================================================
3741 -- PRINT PARAMETER VALUES
3742 -- ======================================================================
3743 /*=========================================================================+*/
3744 PROCEDURE Print_Parameter_values is
3745 l_path_name VARCHAR2(150);
3746 BEGIN
3747 l_path_name := g_path||'print_parameter_values';
3748 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3749 p_full_path => l_path_name,
3750 p_string => '+PARAMTER VALUES RECEIVED TO Do_RECLASS');
3751 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3752 p_full_path => l_path_name,
3753 p_string => ' +ASSET_TRANS_HDR_REC');
3754 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3755 p_full_path => l_path_name,
3756 p_string => ' +TRANSACTION_HEADER_ID.......... '||l_trans_rec.TRANSACTION_HEADER_ID );
3757 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3758 p_full_path => l_path_name,
3759 p_string => ' +TRANSACTION_TYPE_CODE.......... '||l_trans_rec.TRANSACTION_TYPE_CODE );
3760 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3761 p_full_path => l_path_name,
3762 p_string => ' +TRANSACTION_DATE_ENTERED....... '||l_trans_rec.TRANSACTION_DATE_ENTERED );
3763 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3764 p_full_path => l_path_name,
3765 p_string => ' +TRANSACTION_NAME............... '||l_trans_rec.TRANSACTION_NAME);
3766 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3767 p_full_path => l_path_name,
3768 p_string => ' +SOURCE_TRANSACTION_HEADER_ID... '||l_trans_rec.SOURCE_TRANSACTION_HEADER_ID);
3769 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3770 p_full_path => l_path_name,
3771 p_string => ' +MASS_REFERENCE_ID.............. '|| l_trans_rec.MASS_REFERENCE_ID);
3772 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3773 p_full_path => l_path_name,
3774 p_string => ' +TRANSACTION_SUBTYPE............ '|| l_trans_rec.TRANSACTION_SUBTYPE);
3775 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3776 p_full_path => l_path_name,
3777 p_string => ' +TRANSACTION_KEY................ '|| l_trans_rec.TRANSACTION_KEY);
3778 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3779 p_full_path => l_path_name,
3780 p_string => ' +AMORTIZATION_START_DATE........ '||l_trans_rec.AMORTIZATION_START_DATE);
3781 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3782 p_full_path => l_path_name,
3783 p_string => ' +CALLING_INTERFACE.............. '||l_trans_rec.CALLING_INTERFACE);
3784 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3785 p_full_path => l_path_name,
3786 p_string => ' +MASS_TRANSACTION_ID............ '||l_trans_rec.MASS_TRANSACTION_ID);
3787 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3788 p_full_path => l_path_name,
3789 p_string => ' +ASSET_HDR_REC');
3790 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3791 p_full_path => l_path_name,
3792 p_string => ' +ASSET_ID....................... '|| l_asset_hdr_rec.ASSET_ID );
3793 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3794 p_full_path => l_path_name,
3795 p_string => ' +BOOK_TYPE_CODE................. '|| l_asset_hdr_rec.BOOK_TYPE_CODE );
3796 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3797 p_full_path => l_path_name,
3798 p_string => ' +SET_OF_BOOKS_ID................ '|| l_asset_hdr_rec.SET_OF_BOOKS_ID);
3799 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3800 p_full_path => l_path_name,
3801 p_string => ' +PERIOD_OF_ADDITION............. '||l_asset_hdr_rec.PERIOD_OF_ADDITION);
3802 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3803 p_full_path => l_path_name,
3804 p_string => ' +ASSET_CAT_REC_OLD');
3805 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3806 p_full_path => l_path_name,
3807 p_string => ' +CATEGORY_ID.................... '|| l_asset_cat_rec_old.category_id);
3808 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3809 p_full_path => l_path_name,
3810 p_string => ' +ASSET_CAT_REC_NEW');
3811 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3812 p_full_path => l_path_name,
3813 p_string => ' +CATEGORY_ID.................... '|| l_asset_cat_rec_new.category_id);
3814 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3815 p_full_path => l_path_name,
3816 p_string => ' +ASSET_TYPE_REC');
3817 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3818 p_full_path => l_path_name,
3819 p_string => ' +ASSET_TYPE..................... '|| l_asset_type_rec.asset_type );
3820 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3821 p_full_path => l_path_name,
3822 p_string => ' +CALLING FUNCTION................... ' || l_calling_function);
3823 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3824 p_full_path => l_path_name,
3825 p_string => '+PARAMTER VALUES RECEIVED TO Do_RECLASS');
3826 END;
3827
3828
3829
3830
3831 -- ======================================================================
3832 -- RECLASS
3833 -- ======================================================================
3834 /*=========================================================================+
3835 | Function Name: |
3836 | Do_Reclass |
3837 | |
3838 | Description: |
3839 | This IAC hook function is to process reclassification for IAC and |
3840 | called from Reclass API(FA_RECLASS_PUB.Do_Reclass). |
3841 | |
3842 +=========================================================================*/
3843
3844 FUNCTION Do_Reclass(
3845 p_trans_rec FA_API_TYPES.trans_rec_type,
3846 p_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type,
3847 p_asset_cat_rec_old FA_API_TYPES.asset_cat_rec_type,
3848 p_asset_cat_rec_new FA_API_TYPES.asset_cat_rec_type,
3849 p_asset_desc_rec FA_API_TYPES.asset_desc_rec_type,
3850 p_asset_type_rec FA_API_TYPES.asset_type_rec_type,
3851 p_calling_function VARCHAR2,
3852 p_event_id number --R12 uptake
3853 ) return BOOLEAN is
3854
3855 l_old_price_index igi_iac_category_books.price_index_id%type;
3856 l_new_price_index igi_iac_category_books.price_index_id%type;
3857
3858 -- cursor to get the price index of the asset
3859 Cursor get_price_index(c_category_id number) is
3860 SELECT price_index_id
3861 FROM igi_iac_category_books
3862 WHERE book_type_code = l_asset_hdr_rec.book_type_code
3863 AND category_id = c_category_id;
3864
3865
3866 l_set_of_books_id Number;
3867 l_chart_of_accounts_id Number;
3868 l_currency Varchar2(5);
3869 l_precision Number;
3870 l_prd_rec igi_iac_types.prd_rec;
3871 l_path_name VARCHAR2(150);
3872
3873 BEGIN
3874 l_set_of_books_id :=0;
3875 l_chart_of_accounts_id :=0;
3876 l_precision :=0;
3877 l_path_name := g_path||'do_reclass';
3878
3879 /* igi_iac_debug_pkg.debug_on('RECLASS');
3880 igi_iac_debug_pkg.debug(0,'Creating a message log file for RECLASS');
3881 igi_iac_debug_pkg.debug(0,'Date '||sysdate);
3882 igi_iac_debug_pkg.debug(0,'Calling function '||p_calling_function);
3883 igi_iac_debug_pkg.debug(0,'Entry for IAC reclass');*/
3884 --
3885 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3886 p_full_path => l_path_name,
3887 p_string => 'Creating a message log file for RECLASS');
3888 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3889 p_full_path => l_path_name,
3890 p_string => 'Date '||sysdate);
3891 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3892 p_full_path => l_path_name,
3893 p_string => 'Calling function '||p_calling_function);
3894 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3895 p_full_path => l_path_name,
3896 p_string => 'Entry for IAC reclass');
3897
3898 --validate the IAC book
3899 IF Not (igi_iac_common_utils.is_iac_book(p_asset_hdr_rec.book_type_code)) THEN
3900 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3901 p_full_path => l_path_name,
3902 p_string => 'Not an IAC book ..'||p_asset_hdr_rec.book_type_code);
3903
3904 Return True;
3905 End if;
3906
3907 IF NOT igi_iac_common_utils.populate_iac_fa_deprn_data(p_asset_hdr_rec.book_type_code,
3908 'RECLASS') THEN
3909 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3910 p_full_path => l_path_name,
3911 p_string => '*** Error in Synchronizing Depreciation Data ***');
3912
3913 return FALSE;
3914 END IF;
3915
3916
3917 -- assign the parameters to local vlaues;
3918 l_trans_rec :=p_trans_rec;
3919 l_asset_hdr_rec := p_asset_hdr_rec;
3920 l_asset_cat_rec_old :=p_asset_cat_rec_old;
3921 l_asset_cat_rec_new :=p_asset_cat_rec_new;
3922 l_asset_desc_rec:= p_asset_desc_rec;
3923 l_asset_type_rec:= p_asset_type_rec;
3924 l_calling_function := p_calling_function;
3925
3926 Print_Parameter_values;
3927
3928 /* return ture if the reclass being done in the same period asset added */
3929 IF l_asset_hdr_rec.PERIOD_OF_ADDITION = 'Y' THEN
3930 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3931 p_full_path => l_path_name,
3932 p_string => 'RECLASS in the same period as asset Added');
3933
3934 Return True;
3935 END IF;
3936
3937 --set the default vlaues missing
3938 -- set of books id
3939 IF l_asset_hdr_rec.set_of_books_id is null then
3940 IF NOT igi_iac_common_utils.get_book_gl_info (l_asset_hdr_rec.book_type_code,
3941 l_set_of_books_id,
3942 l_chart_of_accounts_id,
3943 l_currency,
3944 l_precision ) THEN
3945 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
3946 p_full_path => l_path_name,
3947 p_string => '+Set of books Failed');
3948 FA_SRVR_MSG.Add_Message(
3949 CALLING_FN => p_calling_function,
3950 NAME => 'IGI_IAC_NO_SET_OF_BOOKS_INFO',
3951 TRANSLATE => TRUE,
3952 APPLICATION => 'IGI');
3953 Return False;
3954 END IF;
3955
3956 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3957 p_full_path => l_path_name,
3958 p_string => '+Set of books value is set to ....'|| l_set_of_books_id);
3959
3960 l_asset_hdr_rec.set_of_books_id := l_set_of_books_id;
3961 END IF;
3962
3963 -- validate the category
3964 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3965 p_full_path => l_path_name,
3966 p_string => '+Validate categories');
3967 IF not do_validate_category THEN
3968 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
3969 p_full_path => l_path_name,
3970 p_string => '+Failed Validate categories');
3971 FA_SRVR_MSG.Add_Message(
3972 CALLING_FN => p_calling_function,
3973 NAME => 'IGI_IAC_CATEGORY_VALIDATION',
3974 TRANSLATE => TRUE,
3975 APPLICATION => 'IGI');
3976 Return False;
3977 END IF;
3978
3979 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3980 p_full_path => l_path_name,
3981 p_string => '+Validate categories successful');
3982
3983 -- validate the asset
3984 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
3985 p_full_path => l_path_name,
3986 p_string => '+Validate Asset');
3987
3988 IF NOT do_validate_asset THEN
3989 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
3990 p_full_path => l_path_name,
3991 p_string => '+Failed Validate Asset');
3992
3993 FA_SRVR_MSG.Add_Message(
3994 CALLING_FN =>p_calling_function ,
3995 NAME => 'IGI_IAC_ASSET_VALIDATION',
3996 TRANSLATE => TRUE,
3997 APPLICATION => 'IGI');
3998 Return TRUE;
3999 END IF;
4000
4001 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
4002 p_full_path => l_path_name,
4003 p_string => '+Validate Asset successfully');
4004
4005 -- Check whether adjustments exist in the open period
4006 -- If Adjustments exists then stop the Reclassification
4007 IF IGI_IAC_COMMON_UTILS.Is_Asset_Adjustment_Done(l_asset_hdr_rec.book_type_code,
4008 l_asset_hdr_rec.asset_id) THEN
4009
4010 FA_SRVR_MSG.Add_Message(
4011 Calling_FN => l_calling_function ,
4012 Name => 'IGI_IAC_ADJUSTMENT_EXCEPTION',
4013 TRANSLATE => TRUE,
4014 APPLICATION => 'IGI');
4015
4016 RETURN FALSE;
4017 END IF;
4018 -- compare the price indexes of the categories
4019 -- get the old price index
4020 Open get_price_index(l_asset_cat_rec_old.category_id);
4021 Fetch get_price_index into l_old_price_index;
4022 Close get_price_index;
4023
4024 -- get the price index for new category
4025 Open get_price_index(l_asset_cat_rec_new.category_id);
4026 Fetch get_price_index into l_new_price_index;
4027 Close get_price_index;
4028
4029 -- compare and call the reclass accordingly
4030 -- bug 3356588
4031 IF (ALLOW_INDEX_REVAL_FLAG) THEN
4032
4033 IF l_old_price_index = l_new_Price_index THEN
4034
4035 -- call the same price index
4036 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
4037 p_full_path => l_path_name,
4038 p_string => '+SAME PRICE INDEX');
4039
4040
4041 SAME_PRICE_INDEX_FLAG := TRUE;
4042 DIFF_PRICE_INDEX_FLAG:= FALSE;
4043 IF do_same_price_index(p_event_id) THEN
4044 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
4045 p_full_path => l_path_name,
4046 p_string => '+SAME PRICE INDEX SUCCESS');
4047 ELSE
4048 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
4049 p_full_path => l_path_name,
4050 p_string => 'SAME PRICE INDEX IAC RECLASSIFICATION FAILED');
4051
4052 FA_SRVR_MSG.Add_Message(
4053 CALLING_FN =>p_calling_function ,
4054 NAME => 'IGI_IAC_SAME_PRICE_FAILED',
4055 TRANSLATE => TRUE,
4056 APPLICATION =>'IGI');
4057 return false;
4058 end if; -- do_same_price_index
4059 -- Null;
4060 ELSE
4061 SAME_PRICE_INDEX_FLAG := FALSE;
4062 DIFF_PRICE_INDEX_FLAG:= TRUE;
4063 IF Do_Diff_Price_Index(p_event_id => p_event_id) THEN
4064 -- call the different price index
4065 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
4066 p_full_path => l_path_name,
4067 p_string => '+DIFFERENT PRICE INDEX');
4068
4069 return True;
4070 ELSE
4071 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
4072 p_full_path => l_path_name,
4073 p_string => '+DIFFERENT PRICE INDEX RETURNED FALSE');
4074 return false;
4075 END IF;
4076 END IF;
4077 ELSE -- ALLOW_INDEX_REVAL_FALG is off
4078 -- call do_no_index_reval function that processes the asset when
4079 -- the category has indexed revaluation switched off
4080 IF Do_No_Index_Reval(p_event_id => p_event_id) THEN
4081 RETURN TRUE;
4082 ELSE
4083 RETURN FALSE;
4084 END IF;
4085 END IF;
4086 Return TRUE;
4087
4088 END;
4089 BEGIN
4090 debug_reclass := FALSE;
4091 l_debug_print := FALSE;
4092
4093 -- global vlaue for allow reval index and prof flag
4094 ALLOW_INDEX_REVAL_FLAG := FALSE;
4095 ALLOW_PROF_REVAL_FLAG := FALSE;
4096 SAME_PRICE_INDEX_FLAG := FALSE;
4097 DIFF_PRICE_INDEX_FLAG := FALSE;
4098
4099 l_calling_function := 'FA_RECLASS_PVT.do_reclass';
4100 chr_newline := fnd_global.newline;
4101
4102 --===========================FND_LOG.START=====================================
4103 g_state_level := FND_LOG.LEVEL_STATEMENT;
4104 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
4105 g_event_level := FND_LOG.LEVEL_EVENT;
4106 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
4107 g_error_level := FND_LOG.LEVEL_ERROR;
4108 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
4109 g_path := 'IGI.PLSQL.igiiarlb.igi_iac_reclass_pkg.';
4110 --===========================FND_LOG.END=====================================
4111
4112
4113 END; --reclass package