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