1 PACKAGE BODY IGI_IAC_CREATE_ASSETS AS
2 -- $Header: igiiacab.pls 120.17.12010000.2 2010/06/24 10:46:39 schakkin ship $
3
4
5 --===========================FND_LOG.START=====================================
6
7 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
8 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
9 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
10 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
11 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
12 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
13 g_path VARCHAR2(100) := 'IGI.PLSQL.igiiacab.IGI_IAC_CREATE_ASSETS.';
14
15 --===========================FND_LOG.END=====================================
16 PROCEDURE do_round ( p_amount in out NOCOPY number, p_book_type_code in varchar2) is
17 l_path varchar2(150) := g_path||'do_round(p_amount,p_book_type_code)';
18 l_amount number := p_amount;
19 l_amount_old number := p_amount;
20 --l_path varchar2(150) := g_path||'do_round';
21 begin
22 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'--- Inside Round() ---');
23 IF IGI_IAC_COMMON_UTILS.Iac_Round(X_Amount => l_amount, X_Book => p_book_type_code)
24 THEN
25 p_amount := l_amount;
26 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'IGI_IAC_COMMON_UTILS.Iac_Round is TRUE');
27 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_amount = '||p_amount);
28 ELSE
29 p_amount := round( l_amount, 2);
30 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'IGI_IAC_COMMON_UTILS.Iac_Round is FALSE');
31 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_amount = '||p_amount);
32 END IF;
33 exception when others then
34 p_amount := l_amount_old;
35 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
36 Raise;
37 END;
38
39 /* to be deleted from spec and body */
40 PROCEDURE log(p_mesg IN VARCHAR2)IS
41
42 BEGIN
43 IF FND_PROFILE.VALUE('IGI_DEBUG_OPTION') = 'Y' THEN
44 fnd_file.put_line(fnd_file.log, p_mesg);
45 END IF;
46 END;
47
48
49 PROCEDURE get_adjusted_cost(p_asset_id IN NUMBER,
50 p_period_counter IN NUMBER,
51 p_book IN VARCHAR2,
52 l_adjusted_cost OUT NOCOPY NUMBER)
53 IS
54
55 /* bug 3451539 start 1
56 -- no longer required
57 CURSOR c_fully_reserved(p_book IN VARCHAR2,
58 p_asset_id IN NUMBER)
59 IS
60
61 SELECT 'X'
62 FROM fa_books
63 WHERE book_type_code = p_book
64 AND asset_id = p_asset_id
65 AND period_counter_fully_reserved IS NOT NULL;
66
67 l_dummy VARCHAR2(1);
68
69 bug 3451539 end 1*/
70
71 BEGIN
72
73 /* bug 3451539 start 2, no longer required
74 OPEN c_fully_reserved(p_book,
75 p_asset_id);
76 FETCH c_fully_reserved
77 INTO l_dummy;
78
79 IF c_fully_reserved%FOUND THEN
80
81 SELECT adjusted_cost
82 INTO l_adjusted_cost
83 FROM igi_iac_asset_balances
84 WHERE book_type_code = p_book
85 AND asset_id = p_asset_id
86 AND period_counter = (SELECT MAX(period_counter)
87 FROM igi_iac_asset_balances
88 WHERE book_type_code = p_book
89 AND asset_id = p_asset_id);
90
91 CLOSE c_fully_reserved;
92
93 ELSE
94
95 SELECT adjusted_cost
96 INTO l_adjusted_cost
97 FROM igi_iac_asset_balances
98 WHERE book_type_code = p_book
99 AND asset_id = p_asset_id
100 AND period_counter = p_period_counter;
101
102
103 CLOSE c_fully_reserved;
104
105 END IF;
106 bug 3451539 end 2 */
107
108 -- bug 3451539, start 3
109 -- This will bring the latest iac cost for any type
110 -- of asset - depreciating, non depreciating or fully
111 -- reserved
112
113 SELECT adjusted_cost
114 INTO l_adjusted_cost
115 FROM igi_iac_asset_balances
116 WHERE book_type_code = p_book
117 AND asset_id = p_asset_id
118 AND period_counter = (SELECT MAX(period_counter)
119 FROM igi_iac_asset_balances
120 WHERE book_type_code = p_book
121 AND asset_id = p_asset_id);
122
123 -- bug 3451539, end 3
124
125 EXCEPTION
126 WHEN NO_DATA_FOUND THEN
127 l_adjusted_cost :=0;
128
129 WHEN OTHERS
130
131 THEN null;
132 END;
133
134 PROCEDURE insert_exceptions(p_revaluation_id IN igi_iac_revaluations.revaluation_id%TYPE,
135 p_asset_id IN igi_iac_reval_asset_rules.asset_id%TYPE,
136 p_category_id IN igi_iac_reval_categories.category_id%TYPE,
137 p_book_type_code IN igi_iac_revaluations.book_type_code%TYPE,
138 p_exception_type IN VARCHAR2
139 )
140
141 IS
142 l_user_id NUMBER;
143 /*changed the hardcoded message to seeded*/
144 /*for bug no 2647561 by shsaxena*/
145 l_str VARCHAR(2000);
146 BEGIN
147 -- enable the exception handler to log negative assets as well
148 IF (p_exception_type = 'PERIOD_INDEX') THEN
149 l_user_id := fnd_global.user_id;
150 fnd_message.set_name('IGI','IGI_IAC_INVALID_PRICE_INDEX');
151 igi_iac_debug_pkg.debug_other_msg(g_error_level,g_path||'insert_exceptions',FALSE);
152 l_str:=fnd_message.get;
153 fnd_file.put_line(FND_FILE.LOG,l_str);
154 ELSIF (p_exception_type = 'NEGATIVE_ASSET') THEN
155 l_user_id := fnd_global.user_id;
156 fnd_message.set_name('IGI','IGI_IAC_NEGATIVE_ASSETS');
157 igi_iac_debug_pkg.debug_other_msg(g_error_level,g_path||'insert_exceptions',FALSE);
158 l_str:=fnd_message.get;
159 fnd_file.put_line(FND_FILE.LOG,l_str);
160 END IF;
161
162 INSERT INTO igi_iac_exceptions (revaluation_id,
163 asset_id,
164 category_id,
165 book_type_code,
166 exception_message,
167 created_by,
168 creation_date,
169 last_Update_date,
170 last_updated_by)
171 SELECT p_revaluation_id,
172 p_asset_id,
173 p_category_id,
174 p_book_type_code,
175 l_str,
176 l_user_id,
177 sysdate,
178 sysdate,
179 l_user_id
180 FROM sys.dual
181 WHERE NOT EXISTS(SELECT 'X'
182 FROM igi_iac_exceptions
183 WHERE revaluation_id = p_revaluation_id
184 AND book_type_code = p_book_type_code
185 AND category_id = p_category_id
186 AND asset_id = p_asset_id);
187
188 EXCEPTION
189 WHEN OTHERS THEN
190 NULL;
191 END;
192
193 --- Start of Create Assets Procedure
194
195 PROCEDURE get_assets( errbuf OUT NOCOPY VARCHAR2
196 , retcode OUT NOCOPY NUMBER
197 , p_revaluation_id IN NUMBER
198 , p_book_type_code IN VARCHAR2
199 , p_revaluation_date IN DATE
200 )
201 IS
202
203 l_reval_period_counter NUMBER;
204 l_reval_factor igi_iac_reval_asset_rules.revaluation_factor%TYPE:= 1;
205 l_user_id NUMBER ;
206 l_login_id NUMBER := fnd_global.login_id;
207 l_reval_period_name VARCHAR(100);
208 l_lastest_closed_per_name VARCHAR2(300);
209 l_reval_price_inxed_value NUMBER;
210 l_last_closed_index_value NUMBER;
211 l_quiet BOOLEAN;
212 l_period_counter NUMBER;
213 l_get_open_period igi_iac_types.prd_rec;
214 l_get_reval_period igi_iac_types.prd_rec;
215 l_book igi_iac_revaluations.book_type_code%TYPE;
216 l_get_record_from_date igi_iac_types.prd_rec;
217 l_get_counter_from_date NUMBER;
218 l_date_placed_in_service DATE;
219 l_get_closed_period igi_iac_types.prd_rec;
220 l_line varchar2(300);
221 l_new_cost igi_iac_reval_asset_rules.new_cost%TYPE;
222 l_current_cost igi_iac_reval_asset_rules.new_cost%TYPE;
223 l_cost BOOLEAN;
224 l_adjusted_cost NUMBER;
225
226 TYPE asset_id_tbl_type IS TABLE OF FA_ADDITIONS.ASSET_ID%TYPE
227 INDEX BY BINARY_INTEGER;
228
229 TYPE cost_tbl_type IS TABLE OF FA_BOOKS.COST%TYPE
230 INDEX BY BINARY_INTEGER;
231
232 l_asset_id asset_id_tbl_type;
233 l_cost_tbl cost_tbl_type;
234 l_loop_count number;
235 l_commit_cnt number := 0;
236
237 CURSOR c_get_categories IS
238 SELECT rc.category_id,
239 DECODE(cb.allow_indexed_reval_flag,'Y','O',
240 DECODE(cb.allow_prof_reval_flag,'Y','P')) reval_type,
241 NVL(cb.allow_indexed_reval_flag, 'N')allow_indexed_reval_flag,
242 NVL(cb.allow_prof_reval_flag, 'N')allow_prof_reval_flag,
243 rc.revaluation_id
244 FROM igi_iac_reval_categories rc, igi_iac_category_books cb
245 WHERE rc.category_id = cb.category_id
246 AND rc.book_type_code = cb.book_type_code
247 AND rc.book_type_code = p_book_type_code
248 AND rc.revaluation_id = p_revaluation_id
249 AND rc.select_category ='Y'
250 AND ( NVL(cb.allow_indexed_reval_flag,'N') = 'Y'
251 OR NVL(cb.allow_prof_reval_flag, 'N')='Y');
252
253 CURSOR c_get_assets(p_cat_id IN fa_additions.asset_category_id%TYPE,
254 p_period_counter IN NUMBER,
255 p_revaluation_id IN igi_iac_revaluations.revaluation_id%TYPE,
256 p_allow_indexed IN igi_iac_category_books.allow_indexed_reval_flag%TYPE,
257 p_allow_prof IN igi_iac_category_books.allow_prof_reval_flag%TYPE
258 ) IS
259 SELECT a.asset_id,
260 b.cost
261 FROM fa_additions a,
262 fa_books b
263 WHERE a.asset_id = b.asset_id
264 AND b.book_type_code = p_book_type_code
265 AND a.asset_category_id = p_cat_id
266 AND a.asset_type <> 'CIP' -- bug 3416315
267 AND b.transaction_header_id_out IS NULL
268 AND b.date_placed_in_service <= p_revaluation_date
269 AND NOT EXISTS(SELECT 'X'
270 FROM igi_iac_revaluation_rates rr,
271 igi_iac_revaluations r
272 WHERE r.revaluation_id = rr.revaluation_id
273 AND rr.asset_id = a.asset_id
274 AND r.book_type_code = rr.book_type_code
275 AND r.book_type_code = p_book_type_code
276 AND r.status IN ('PREVIEWED','COMPLETED','UPDATED','FAILED_RUN')
277 AND rr.period_counter = p_period_counter
278 AND rr.reval_type = 'O'
279 AND p_allow_indexed = 'Y'
280 AND p_allow_prof = 'N')
281 AND NOT EXISTS(SELECT 'X'
282 FROM igi_iac_reval_asset_rules ar
283 WHERE a.asset_id = ar.asset_id
284 AND ar. revaluation_id = p_revaluation_id)
285 AND NOT EXISTS(SELECT 'X'
286 FROM fa_transaction_headers t,
287 fa_retirements r
288 WHERE t.book_type_code = b.book_type_code
289 AND t.asset_id = a.asset_id
290 AND t.transaction_header_id = r.transaction_header_id_in
291 AND r.transaction_header_id_out IS NULL
292 AND t.transaction_type_code = 'FULL RETIREMENT'
293 );
294
295 CURSOR c_get_last_reval_period_count(p_asset_id IN igi_iac_revaluation_rates.asset_id%TYPE) IS
296 SELECT period_counter
297 FROM igi_iac_revaluation_rates
298 WHERE asset_id = p_asset_id
299 AND latest_record = 'Y'
300 AND book_type_code = p_book_type_code;
301
302 CURSOR c_get_date_place_in_service(p_asset_id IN fa_books.asset_id%TYPE) IS
303 SELECT date_placed_in_service
304 FROM fa_books
305 WHERE asset_id = p_asset_id
306 AND book_type_code = p_book_type_code
307 AND date_ineffective IS NULL;
308
309 BEGIN
310
311 l_user_id := fnd_global.user_id;
312
313 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets','starting get assets');
314 -- get the period counter from the :books.revaluation_date
315 IF igi_iac_common_utils.get_period_info_for_date(p_book_type_code,
316 p_revaluation_date,
317 l_get_record_from_date) THEN
318
319 l_get_counter_from_date:=l_get_record_from_date.period_counter;
320
321 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets','got period counter');
322 END IF;
323
324 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets','got period counter');
325 FOR r_get_categories IN c_get_categories
326 LOOP
327 OPEN c_get_assets(r_get_categories.category_id,
328 l_get_counter_from_date,
329 r_get_categories.revaluation_id,
330 r_get_categories.allow_indexed_reval_flag,
331 r_get_categories.allow_prof_reval_flag);
332 FETCH c_get_assets BULK COLLECT INTO
333 l_asset_id, l_cost_tbl;
334
335 CLOSE c_get_assets;
336
337 FOR l_loop_count IN 1..l_asset_id.count
338 LOOP
339 -- check if the asset is a negative asset, if it is then
340 -- log it into the exceptions table and do not process it
341 IF (l_cost_tbl(l_loop_count) < 0) THEN
342 insert_exceptions(p_revaluation_id,
343 l_asset_id(l_loop_count),
344 r_get_categories.category_id,
345 p_book_type_code,
346 'NEGATIVE_ASSET'
347 );
348 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets','insert_exceptions called');
349 ELSE
350 -- if revaluation type = 'O' (Indexed) then calculate the reval_rate
351 IF r_get_categories.reval_type = 'O' THEN
352 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets','reval type is O');
353 -- first get the period counter for the last revaluation on this asset
354 OPEN c_get_last_reval_period_count(l_asset_id(l_loop_count));
355 FETCH c_get_last_reval_period_count
356 INTO l_reval_period_counter;
357 IF c_get_last_reval_period_count%FOUND THEN
358 CLOSE c_get_last_reval_period_count;
359 -- then get the period_name using get_period_for_counter
360 IF IGI_IAC_COMMON_UTILS.Get_Period_Info_For_Counter(p_book_type_code,
361 l_reval_period_counter,
362 l_get_reval_period) THEN
363 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets',
364 'asset getting price index for last reval period');
365 l_reval_period_name := l_get_reval_period.period_name;
366 -- then get the price index using get_price_index_value
367 IF IGI_IAC_COMMON_UTILS.Get_Price_Index_Value(p_book_type_code,
368 l_asset_id(l_loop_count),
369 l_reval_period_name,
370 l_reval_price_inxed_value)
371 THEN null;
372 END IF;
373 END IF;
374 ELSE -- no revaluation on this asset, so get the date placed in service
375 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets','no revaluation,
376 so get date placed in service');
377 CLOSE c_get_last_reval_period_count;
378 OPEN c_get_date_place_in_service(l_asset_id(l_loop_count));
379 FETCH c_get_date_place_in_service
380 INTO l_date_placed_in_service;
381 IF c_get_date_place_in_service%FOUND THEN
382 CLOSE c_get_date_place_in_service;
383 -- get the period_name using get_period_for_date
384 IF IGI_IAC_COMMON_UTILS.Get_Period_Info_For_Date(p_book_type_code,
385 l_date_placed_in_service,
386 l_get_reval_period) THEN
387 l_reval_period_name := l_get_reval_period.period_name;
388 -- then get the price index using get_price_index_value
389 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets','get index for date placed
390 in service');
391 IF IGI_IAC_COMMON_UTILS.Get_Price_Index_Value(p_book_type_code,
392 l_asset_id(l_loop_count),
393 l_reval_period_name,
394 l_reval_price_inxed_value)
395 THEN null;
396 END IF;
397 END IF;
398 ELSE
399 CLOSE c_get_date_place_in_service;
400 END IF; --fetch c_get_date_placed_in_service
401 END IF; -- fetch c_get_laset_reval_period_count
402 -- now get the latest closed period number
403 -- first get the open period
404 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets','get last closed period');
405 IF IGI_IAC_COMMON_UTILS.Get_Open_Period_Info(p_book_type_code,
406 l_get_open_period)THEN
407 null;
408 END IF;
409 -- Then get the period_name for the closed period
410 IF IGI_IAC_COMMON_UTILS.Get_Period_Info_For_Counter(p_book_type_code,
411 l_get_open_period.period_counter-1,
412 l_get_closed_period) THEN
413 l_lastest_closed_per_name:=l_get_closed_period.period_name;
414 END IF;
415 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets','get price index for last closed period');
416 -- then get the proce index for the latest closed period using coomon utils
417 IF IGI_IAC_COMMON_UTILS.Get_Price_Index_Value(p_book_type_code,
418 l_asset_id(l_loop_count),
419 l_lastest_closed_per_name,
420 l_last_closed_index_value) THEN
421 -- calculate the reval_rate by dividing the last_closed_index_value
422 -- by the l_reval_price_inxed_value
423 l_reval_factor := (l_last_closed_index_value/l_reval_price_inxed_value);
424 END IF;
425 -- Now get the adjusted cost
426 get_adjusted_cost(l_asset_id(l_loop_count),
427 l_get_counter_from_date,
428 p_book_type_code,
429 l_adjusted_cost);
430 -- Now calculate the current cost
431 l_current_cost:=l_cost_tbl(l_loop_count)+l_adjusted_cost;
432 -- Now calculate the new cost
433 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets','calculate new cost');
434 l_new_cost:=((l_cost_tbl(l_loop_count)+l_adjusted_cost)*l_reval_factor);
435 do_round(l_new_cost,p_book_type_code);
436 -- Now round hte new cost
437 l_cost := igi_iac_common_utils.iac_round(l_new_cost,p_book_type_code) ;
438 ELSE -- reval_type = 'P'
439 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets','getting adjusted cost for P asset');
440 get_adjusted_cost(l_asset_id(l_loop_count),
441 l_get_counter_from_date,
442 p_book_type_code,
443 l_adjusted_cost);
444 -- Now calculate the current cost
445 l_current_cost:=l_cost_tbl(l_loop_count)+l_adjusted_cost;
446 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets','professional allowed only');
447 l_new_cost:=(l_cost_tbl(l_loop_count)+l_adjusted_cost);
448 END IF; --is r_get_categories.reval_type = 'O'
449 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets','checking if l_last_closed_index_value = 9999.99');
450 IF l_last_closed_index_value = 9999.99 AND
451 r_get_categories.reval_type = 'O' THEN
452 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets','l_last_closed_index_value = 9999.99');
453 insert_exceptions(p_revaluation_id,
454 l_asset_id(l_loop_count),
455 r_get_categories.category_id,
456 p_book_type_code,
457 'PERIOD_INDEX'
458 );
459 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets','insert_exceptions called');
460 ELSE -- l_last_closed_index_value not equal to 9999.99
461 -- insert into igi_iac_reval_asset_rules
462 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets','inserting into igi_iac_reval_asset_rules');
463 IF (r_get_categories.reval_type = 'P' ) OR
464 (r_get_categories.allow_prof_reval_flag = 'N' AND
465 r_get_categories.allow_indexed_reval_flag = 'Y' AND
466 l_reval_factor<>1) OR
467 (r_get_categories.allow_prof_reval_flag = 'Y' AND
468 r_get_categories.allow_indexed_reval_flag = 'Y')THEN
469
470 INSERT INTO igi_iac_reval_asset_rules
471 (REVALUATION_ID,
472 BOOK_TYPE_CODE,
473 CATEGORY_ID,
474 ASSET_ID,
475 REVALUATION_FACTOR,
476 REVALUATION_TYPE,
477 NEW_COST,
478 CURRENT_COST,
479 SELECTED_FOR_REVAL_FLAG,
480 CREATED_BY,
481 CREATION_DATE,
482 LAST_UPDATE_DATE,
483 LAST_UPDATED_BY)
484 VALUES(p_revaluation_id,
485 p_book_type_code,
486 r_get_categories.category_id,
487 l_asset_id(l_loop_count),
488 l_reval_factor,
489 r_get_categories.reval_type,
490 l_new_cost,
491 l_current_cost,
492 'Y',
493 l_user_id,
494 sysdate,
495 sysdate,
496 l_user_id);
497
498 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets','end of insert for this asset');
499 END IF; -- insert asset
500 END IF; -- last closed index 9999.99
501 l_commit_cnt := l_commit_cnt + 1;
502 IF l_commit_cnt=1000 THEN
503 commit;
504 l_commit_cnt := 0;
505 END IF;
506 END IF; -- negative assets
507 END LOOP;
508 END LOOP;
509
510 errbuf := 'Normal completion';
511 retcode := 0;
512
513 commit;
514 EXCEPTION WHEN OTHERS THEN
515 errbuf := SQLERRM;
516 retcode := 2;
517 igi_iac_debug_pkg.debug_unexpected_msg(g_path||'get_assets');
518 END get_assets;
519 END; -- package
[Home] [Help]