1 PACKAGE BODY IGI_IAC_REVAL_HIST_PKG AS
2 -- $Header: igiiarhb.pls 120.15.12000000.1 2007/08/01 16:17:07 npandya noship $
3
4 --===========================FND_LOG.START=====================================
5
6 g_state_level NUMBER;
7 g_proc_level NUMBER;
8 g_event_level NUMBER;
9 g_excep_level NUMBER;
10 g_error_level NUMBER;
11 g_unexp_level NUMBER;
12 g_path VARCHAR2(100);
13
14 --===========================FND_LOG.END=====================================
15 -- ==========================================================================
16 -- FUNCTION Insert_Rows: Function calculates the revaluation history of the
17 -- asset cost and inserts into table igi_iac_reval_history.
18 -- ==========================================================================
19
20 FUNCTION Insert_rows ( P_Asset_id Number,
21 P_Book_type_code Varchar2)
22 RETURN BOOLEAN
23 IS
24 -- main select fetching all revaluation transactions in IAC
25 CURSOR c_get_iac_transactions IS
26 SELECT Adjustment_id,transaction_header_id,
27 transaction_type_code,transaction_sub_type,
28 transaction_date_entered,
29 period_counter,adjustment_status
30 FROM IGI_IAC_TRANSACTION_HEADERS
31 WHERE asset_id = p_asset_id
32 AND book_type_code = p_book_type_code
33 AND transaction_type_code IN ('ADDITION','REVALUATION','RECLASS','ADJUSTMENT')
34 AND transaction_sub_type IN ('REVALUATION','OCCASSIONAL','INDEXED','CATCHUP','PROFESSIONAL','IMPLEMENTATION','COST')
35 AND Adjustment_status IN ( 'COMPLETE','RUN')
36 ORDER BY adjustment_id;
37
38 -- Selecting the previous adjustment id to get previous cost before revaluaton
39 CURSOR c_get_iac_Prev_transactions (P_adj_id Number , p_trans_type varchar) IS
40 SELECT *
41 FROM IGI_IAC_TRANSACTION_HEADERS
42 WHERE asset_id = p_asset_id
43 AND book_type_code = p_book_type_code
44 AND adjustment_id = ( SELECT MAX(adjustment_id)
45 FROM IGI_IAC_TRANSACTION_HEADERS
46 WHERE asset_id = p_asset_id
47 AND book_type_code = p_book_type_code
48 AND Adjustment_status IN ( 'COMPLETE','RUN')
49 AND adjustment_id < p_adj_id)
50 AND Adjustment_status IN ( 'COMPLETE','RUN');
51
52 -- to get the previous transaction for reclass
53 CURSOR c_get_iac_Prev_reclass (P_adj_id Number , p_trans_type varchar) IS
54 SELECT *
55 FROM IGI_IAC_TRANSACTION_HEADERS
56 WHERE asset_id = p_asset_id
57 AND book_type_code = p_book_type_code
58 AND adjustment_id = ( SELECT MAX(adjustment_id)
59 FROM IGI_IAC_TRANSACTION_HEADERS
60 WHERE asset_id = p_asset_id
61 AND book_type_code = p_book_type_code
62 AND Adjustment_status IN ( 'COMPLETE','RUN')
63 AND transaction_type_code = 'RECLASS'
64 AND transaction_sub_type IS NULL
65 AND adjustment_id < p_adj_id)
66 AND Adjustment_status IN ( 'COMPLETE','RUN')
67 AND transaction_type_code = 'RECLASS'
68 AND transaction_sub_type IS NULL;
69
70
71 --
72 CURSOR c_get_iac_history IS
73 SELECT *
74 FROM IGI_IAC_REVAL_HISTORY
75 WHERE asset_id = p_asset_id
76 AND book_type_code = p_book_type_code;
77
78 --- fecthing the Fa_cost if transaction is Addition or Revaluation;
79 CURSOR C_get_fa_cost ( P_adj_id igi_iac_transaction_headers.adjustment_id%type)
80 IS
81 SELECT cost,date_placed_in_service
82 FROM fa_books fb,igi_iac_transactioN_headers igth
83 WHERE fb.asset_id = p_asset_id
84 AND fb.book_type_code =p_book_type_code
85 AND fb.asset_id = igth.asset_id
86 AND fb.book_type_code = igth.book_type_code
87 AND igth.adjustment_id = p_adj_id
88 and fb.date_effective < igth.transaction_date_entered
89 and nvl(fb.date_ineffective,igth.transaction_date_entered) >= igth.transaction_date_entered;
90
91 -- bug 3394103 start 1, fetching previous fa cost for all trxs but addition and revaluation
92 CURSOR c_get_fa_cost_prev(cp_trx_hdr_id igi_iac_transaction_headers.transaction_header_id%TYPE,
93 cp_adj_id igi_iac_transaction_headers.adjustment_id%TYPE)
94 IS
95 SELECT cost,
96 date_placed_in_service
97 FROM fa_books fb,
98 igi_iac_transaction_headers igth
99 WHERE fb.asset_id = p_asset_id
100 AND fb.book_type_code =p_book_type_code
101 AND fb.asset_id = igth.asset_id
102 AND fb.book_type_code = igth.book_type_code
103 AND igth.adjustment_id = cp_adj_id
104 AND igth.transaction_header_id = cp_trx_hdr_id
105 AND igth.transaction_header_id = fb.transaction_header_id_out;
106
107
108 CURSOR c_get_fa_cost_curr(cp_trx_hdr_id igi_iac_transaction_headers.transaction_header_id%TYPE,
109 cp_adj_id igi_iac_transaction_headers.adjustment_id%TYPE)
110 IS
111 SELECT cost,
112 date_placed_in_service
113 FROM fa_books fb,
114 igi_iac_transaction_headers igth
115 WHERE fb.asset_id = p_asset_id
116 AND fb.book_type_code =p_book_type_code
117 AND fb.asset_id = igth.asset_id
118 AND fb.book_type_code = igth.book_type_code
119 AND igth.adjustment_id = cp_adj_id
120 AND igth.transaction_header_id = cp_trx_hdr_id
121 AND igth.transaction_header_id = fb.transaction_header_id_in;
122 -- bug 3394103 end 1
123
124 -- bug 3587648, start 1
125 -- declare cursors to obtain fa_cost for Reclass transactions
126 CURSOR c_get_fa_recl_cost_curr(cp_adj_id igi_iac_transaction_headers.adjustment_id%TYPE)
127 IS
128 SELECT cost,
129 date_placed_in_service
130 FROM fa_books fb,
131 igi_iac_transaction_headers igth
132 WHERE fb.asset_id = p_asset_id
133 AND fb.book_type_code = p_book_type_code
134 AND fb.asset_id = igth.asset_id
135 AND fb.book_type_code = igth.book_type_code
136 AND igth.adjustment_id_out = cp_adj_id
137 AND fb.date_effective < (SELECT fah.date_effective
138 FROM fa_asset_history fah
139 WHERE fah.transaction_header_id_in = igth.transaction_header_id
140 AND fah.asset_id = p_asset_id)
141 AND nvl(fb.date_ineffective,igth.transaction_date_entered) >= igth.transaction_date_entered;
142
143 CURSOR c_get_fa_recl_cost_prev(cp_adj_id igi_iac_transaction_headers.adjustment_id%TYPE)
144 IS
145 SELECT cost,
146 date_placed_in_service
147 FROM fa_books fb,
148 igi_iac_transactioN_headers igth
149 WHERE fb.asset_id = p_asset_id
150 AND fb.book_type_code = p_book_type_code
151 AND fb.asset_id = igth.asset_id
152 AND fb.book_type_code = igth.book_type_code
153 AND igth.adjustment_id = cp_adj_id
154 AND fb.date_effective < (SELECT fah.date_effective
155 FROM fa_asset_history fah
156 WHERE fah.transaction_header_id_in = igth.transaction_header_id
157 AND fah.asset_id = p_asset_id)
158 AND nvl(fb.date_ineffective,igth.transaction_date_entered) >= igth.transaction_date_entered;
159 -- bug 3587648, end 1
160
161 -- cusror to fecth the iac cost
162 CURSOR c_get_iac_cost(P_adjustment_id number) IS
163 SELECT SUM(adjustment_cost) Iac_cost
164 FROM igi_iac_det_balances
165 WHERE asset_id =p_asset_id
166 AND book_type_Code = p_book_type_code
167 AND adjustment_id = P_adjustment_id
168 AND NVL(active_flag,'Y') = 'Y'
169 GROUP BY asset_id,adjustment_id;
170
171 -- cursor to get adjustment type
172 Cursor c_get_adj_type(p_transaction_header_id number) IS
173 Select *
174 From fa_transaction_headers
175 Where asset_id=p_asset_id
176 and book_type_code =p_book_type_code
177 and transaction_header_id =p_transaction_header_id;
178
179
180
181 l_get_transactions c_get_iac_transactions%ROWTYPE;
182 l_get_iac_history c_get_iac_history%ROWTYPE;
183 l_get_iac_Prev_transactions c_get_iac_Prev_transactions%ROWTYPE;
184
185 l_iac_asset_history igi_iac_reval_history%ROWTYPE;
186 l_get_fa_cost c_get_fa_cost%ROWTYPE;
187 -- bug 3394103 start 2
188 l_get_fa_cost_prev c_get_fa_cost_prev%ROWTYPE;
189 l_get_fa_cost_curr c_get_fa_cost_curr%ROWTYPE;
190 -- bug 3394103 end 2
191
192 l_get_iac_cost c_get_iac_cost%ROWTYPE;
193 l_get_adj_type c_get_adj_type%rowtype;
194
195 TYPE t_Adjustment_id IS TABLE OF igi_iac_transaction_headers.adjustment_Id%TYPE;
196 TYPE t_transaction_header_id IS TABLE OF igi_iac_transaction_headers.transaction_header_id%TYPE;
197 TYPE t_transaction_type_code IS TABLE OF igi_iac_transaction_headers.transaction_type_code%TYPE;
198 TYPE t_transaction_sub_type IS TABLE OF igi_iac_transaction_headers.transaction_sub_type%TYPE;
199 TYPE t_transaction_date_entered IS TABLE OF igi_iac_transaction_headers.transaction_date_entered%TYPE;
200 TYPE t_period_counter IS TABLE OF igi_iac_transaction_headers.period_counter%TYPE;
201 TYPE t_adjustment_status IS TABLE OF igi_iac_transaction_headers.adjustment_status%TYPE;
202
203 l_adjustment_id t_adjustment_id;
204 l_transaction_header_id t_transaction_header_id;
205 l_transaction_type_code t_transaction_type_code;
206 l_transaction_sub_type t_transaction_sub_type;
207 l_transaction_date_entered t_transaction_date_entered;
208 l_period_counter t_period_counter;
209 l_adjustment_status t_adjustment_status;
210 l_get_current_adj_id number;
211
212 l_get_previous_cost number;
213 l_get_current_cost number;
214 l_prd_rec igi_iac_types.prd_rec;
215 l_prd_rec_pre igi_iac_types.prd_rec;
216 l_idx1 BINARY_INTEGER;
217 l_idx2 BINARY_INTEGER;
218
219 TYPE reval_history IS RECORD ( adjustment_id igi_iac_transaction_headers.adjustment_Id%TYPE,
220 transaction_header_id igi_iac_transaction_headers.transaction_header_id%TYPE,
221 transaction_type_code igi_iac_transaction_headers.transaction_type_code%TYPE,
222 transaction_sub_type igi_iac_transaction_headers.transaction_sub_type%TYPE,
223 transaction_date_entered igi_iac_transaction_headers.transaction_date_entered%TYPE,
224 period_counter igi_iac_transaction_headers.period_counter%TYPE,
225 adjustment_status igi_iac_transaction_headers.adjustment_status%TYPE
226 );
227
228 TYPE Iac_reval_history IS TABLE OF reval_history INDEX BY BINARY_INTEGER;
229
230 l_lac_reval_history iac_reval_history;
231
232 l_path VARCHAR2(100);
233 BEGIN
234 l_idx1 := 0;
235 l_idx2 := 0;
236 l_path := g_path||'Insert_rows';
237
238 --test records exists for the asset in the table if yes then return else
239 -- process
240 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Asset ID :' || p_asset_id);
241 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Book_type_code :' || p_book_type_code);
242 OPEN c_get_iac_history;
243 FETCH c_get_iac_history INTO l_get_iac_history;
244 IF c_get_iac_history%FOUND THEN
245 CLOSE c_get_iac_history;
246 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Records already exist for the asset ');
247 RETURN TRUE;
248 END IF;
249 CLOSE c_get_iac_history;
250
251 -- process to get the reuiqred info.
252 --Use bulk fecth for getting the transactions
253 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Start Processing ');
254 OPEN C_get_iac_transactions;
255 FETCH C_get_iac_transactions BULK COLLECT INTO l_adjustment_id,l_transaction_header_id,
256 l_transaction_type_code,l_transaction_sub_type,
257 l_transaction_date_entered,
258 l_period_counter,
259 l_adjustment_status;
260
261 FOR i IN 1..l_adjustment_id.count LOOP
262
263 l_lac_reval_history(l_idx1).adjustment_id := l_adjustment_id(i);
264 l_lac_reval_history(l_idx1).transaction_header_id := l_transaction_header_id(i);
265 l_lac_reval_history(l_idx1).transaction_type_code := l_transaction_type_code(i);
266 l_lac_reval_history(l_idx1).transaction_sub_type := l_transaction_sub_type(i);
267 l_lac_reval_history(l_idx1).transaction_date_entered := l_transaction_date_entered(i);
268 l_lac_reval_history(l_idx1).period_counter := l_period_counter(i);
269 l_lac_reval_history(l_idx1).adjustment_status := l_adjustment_status(i);
270
271 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Transaction details : ');
272 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Adjustment ID : '||l_lac_reval_history(l_idx1).adjustment_id);
273 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Transaction Header : '||l_lac_reval_history(l_idx1).transaction_header_id);
274 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Transaction Type : '||l_lac_reval_history(l_idx1).transaction_type_code);
275 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Transaction Sub Type: '||l_lac_reval_history(l_idx1).transaction_sub_type);
276 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Transaction Date : '||l_lac_reval_history(l_idx1).transaction_date_entered);
277 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Period Counter : '||l_lac_reval_history(l_idx1).period_counter);
278 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'End');
279
280 l_idx1 := l_idx1 + 1;
281 END LOOP;
282
283 FOR l_idx2 IN l_lac_reval_history.FIRST..l_lac_reval_history.LAST LOOP
284 l_get_current_adj_id := NULL;
285 l_get_previous_cost := 0;
286 l_get_current_cost := 0;
287
288 IF l_lac_reval_history(l_idx2).transaction_type_code = 'ADDITION' THEN
289 -- bug 3587648, start 2
290 -- commented out as the logic broke under certain circumstances
291 -- IF (l_lac_reval_history(l_idx2).transaction_sub_type = 'REVALUATION'
292 -- AND l_lac_reval_history(l_idx2 + 1).transaction_sub_type = 'CATCHUP') THEN
293 -- l_get_current_adj_id := l_lac_reval_history(l_idx2+1).adjustment_id;
294 --l_lac_reval_history(l_idx2) := l_lac_reval_history(l_idx2+1);
295 -- l_idx2 := l_idx2 + 1;
296 /* igi_iac_debug_pkg.debug_other_string(g_state_level,
297 l_path,
298 'Skipping ADDITION ' ||l_lac_reval_history(l_idx2).transaction_sub_type);
299 l_get_current_adj_id := NULL;
300 ELSE */
301 IF l_lac_reval_history(l_idx2).transaction_sub_type = 'REVALUATION' THEN
302 -- bug 3587648, end 2
303 l_lac_reval_history(l_idx2).transaction_sub_type := 'INDEXED';
304 l_get_current_adj_id := l_lac_reval_history(l_idx2).adjustment_id;
305 END IF;
306 END IF;
307 IF l_lac_reval_history(l_idx2).transaction_type_code = 'REVALUATION' THEN
308 IF (l_lac_reval_history(l_idx2).transaction_sub_type IN ('OCCASSIONAL','PROFESSIONAL','IMPLEMENTATION')) THEN
309 l_get_current_adj_id := l_lac_reval_history(l_idx2).adjustment_id;
310 END IF;
311 END IF;
312 IF l_lac_reval_history(l_idx2).transaction_type_code = 'RECLASS' THEN
313 -- bug 3587648, start 3
314 -- commented out as the logic broke under certain circumstances
315 -- IF (l_lac_reval_history(l_idx2).transaction_sub_type = 'REVALUATION')
316 -- AND (l_lac_reval_history(l_idx2 + 1).transaction_sub_type = 'CATCHUP') THEN
317 /* igi_iac_debug_pkg.debug_other_string(g_state_level,
318 l_path,
319 'Skipping RECLASS ' ||l_lac_reval_history(l_idx2).transaction_sub_type);
320 l_get_current_adj_id := NULL;
321 ELSE*/
322 IF (l_lac_reval_history(l_idx2).transaction_sub_type = 'REVALUATION') THEN
323 -- bug 3587648, end 3
324 l_lac_reval_history(l_idx2).transaction_sub_type := 'INDEXED';
325 l_get_current_adj_id := l_lac_reval_history(l_idx2).adjustment_id;
326 END IF;
327 END IF;
328
329 IF l_lac_reval_history(l_idx2).transaction_type_code = 'ADJUSTMENT' THEN
330 IF (l_lac_reval_history(l_idx2).transaction_sub_type = 'COST')
331 THEN
332 l_get_current_adj_id := l_lac_reval_history(l_idx2).adjustment_id;
333 END IF;
334 END IF;
335 -- fetch the IAC COST
336 IF l_get_current_adj_id IS NOT NULL THEN
337 OPEN C_get_IAC_cost(l_get_current_adj_id);
338 FETCH c_get_iac_cost INTO l_get_iac_cost;
339 IF c_get_iac_cost%FOUND THEN
340 -- fetch the FA_COST
341 -- bug 3394103 start 3
342 IF (l_lac_reval_history(l_idx2).transaction_type_code IN ('ADDITION', 'REVALUATION')) THEN
343 -- bug 3394103 end 3
344 OPEN C_get_fa_cost(l_lac_reval_history(l_idx2).adjustment_id);
345 FETCH C_get_fa_cost INTO l_get_fa_cost;
346 IF c_get_fa_cost%FOUND THEN
347 l_get_current_cost := l_get_fa_cost.cost +l_get_iac_cost.iac_cost;
348 ELSE
349 l_get_current_cost := l_get_iac_cost.iac_cost;
350 END IF;
351 CLOSE c_get_fa_cost;
352 -- bug 3394103 start 4
353 -- bug 3587648, start 4
354 ELSIF l_lac_reval_history(l_idx2).transaction_type_code = 'RECLASS' THEN
355 OPEN c_get_fa_recl_cost_curr(l_lac_reval_history(l_idx2).adjustment_id);
356 FETCH c_get_fa_recl_cost_curr INTO l_get_fa_cost_curr;
357 IF c_get_fa_recl_cost_curr%FOUND THEN
358 l_get_current_cost := l_get_fa_cost_curr.cost +l_get_iac_cost.iac_cost;
359 ELSE
360 l_get_current_cost := l_get_iac_cost.iac_cost;
361 END IF;
362 CLOSE c_get_fa_recl_cost_curr;
363 -- bug 3587648, end 4
364 ELSE -- not in addition,revaluation, reclass
365 -- for all other transactions
366 OPEN c_get_fa_cost_curr(l_lac_reval_history(l_idx2).transaction_header_id,
367 l_lac_reval_history(l_idx2).adjustment_id);
368 FETCH c_get_fa_cost_curr INTO l_get_fa_cost_curr;
369 IF c_get_fa_cost_curr%FOUND THEN
370 l_get_current_cost := l_get_fa_cost_curr.cost +l_get_iac_cost.iac_cost;
371 ELSE
372 l_get_current_cost := l_get_iac_cost.iac_cost;
373 END IF;
374 CLOSE c_get_fa_cost_curr;
375 END IF;
376 -- bug 3394103 end 4
377 IF igi_iac_common_utils.Get_Period_Info_for_Counter(p_book_type_code,
378 l_lac_reval_history(l_idx2).period_counter,
379 l_prd_rec) THEN
380 NULL;
381 END IF;
382 l_prd_rec_pre := l_prd_rec;
383 ELSE
384 RAISE NO_DATA_FOUND;
385 END IF;
386 CLOSE C_get_IAC_cost;
387
388 -- fetch the previous cost
389 IF l_lac_reval_history(l_idx2).transaction_type_code = 'ADDITION' THEN
390 l_get_previous_cost := l_get_fa_cost.cost;
391 IF igi_iac_common_utils.Get_Period_Info_for_Date(p_book_type_code,
392 l_get_fa_cost.date_placed_in_service,
393 l_prd_rec_pre ) THEN
394 NULL;
395 END IF;
396 END IF;
397 IF l_lac_reval_history(l_idx2).transaction_type_code IN ('REVALUATION','RECLASS','ADJUSTMENT') THEN
398 --fetch the previous adjustment id
399 IF l_lac_reval_history(l_idx2).transaction_type_code = 'RECLASS' THEN
400 -- get the previous transaction with transaction type as 'RECLASS'
401 -- and sub type as null and use this transaction to fetch the previous transaction
402 OPEN c_get_iac_Prev_reclass(l_lac_reval_history(l_idx2).adjustment_id,
403 l_lac_reval_history(l_idx2).transaction_type_code);
404 FETCH c_get_iac_prev_reclass INTO l_get_iac_prev_transactions;
405 IF c_get_iac_prev_reclass%FOUND THEN
406 igi_iac_debug_pkg.debug_other_string(g_state_level,
407 l_path,
408 'reclass previous transaction '|| l_get_iac_prev_transactions.adjustment_id);
409 l_lac_reval_history(l_idx2).adjustment_id :=l_get_iac_prev_transactions.adjustment_id;
410 END IF;
411 CLOSE c_get_iac_prev_reclass;
412 END IF;
413
414 IF (l_lac_reval_history(l_idx2).transaction_sub_type = 'IMPLEMENTATION') THEN
415 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'No Previous transaction ');
416 l_get_previous_cost := l_get_fa_cost.cost;
417 IF igi_iac_common_utils.Get_Period_Info_for_Date(p_book_type_code,
418 l_get_fa_cost.date_placed_in_service,
419 l_prd_rec_pre ) THEN
420 NULL;
421 END IF;
422 ELSE
423 OPEN c_get_iac_Prev_transactions(l_lac_reval_history(l_idx2).adjustment_id,
424 l_lac_reval_history(l_idx2).transaction_type_code);
425 FETCH c_get_iac_prev_transactions INTO l_get_iac_prev_transactions;
426
427 IF c_get_iac_prev_transactions%NOTFOUND THEN
428 -- fetch the IAC COST
429 -- the case may be the revaluation might be the first transaction
430 -- 1.Current period addition
431 -- 2.Implementation form MHCA
432 -- In the above two cases there is FA_COST only shown as previous cost
433 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'No Previous transaction ');
434 /*IF igi_iac_common_utils.Get_Period_Info_for_Date(p_book_type_code,
435 l_get_fa_cost.date_placed_in_service,
436 l_prd_rec_pre ) THEN
437 NULL;
438 END IF;*/
439 l_get_previous_cost := l_get_fa_cost.cost;
440 ELSE
441 igi_iac_debug_pkg.debug_other_string(g_state_level,
442 l_path,
443 'Previous transaction '|| l_get_iac_prev_transactions.adjustment_id);
444 OPEN C_get_IAC_cost(l_get_iac_prev_transactions.adjustment_id);
445 FETCH c_get_iac_cost INTO l_get_iac_cost;
446 IF c_get_iac_cost%FOUND THEN
447 -- bug 3394103 start 5
448 IF (l_lac_reval_history(l_idx2).transaction_type_code IN ('ADDITION', 'REVALUATION')) THEN
449 -- bug 3394103 end 3
450 OPEN C_get_fa_cost(l_get_iac_prev_transactions.adjustment_id);
451 FETCH C_get_fa_cost INTO l_get_fa_cost;
452 IF c_get_fa_cost%FOUND THEN
453 l_get_previous_cost := l_get_fa_cost.cost +l_get_iac_cost.iac_cost;
454 ELSE
455 l_get_previous_cost := l_get_iac_cost.iac_cost;
456 END IF;
457 CLOSE c_get_fa_cost;
458 -- bug 3587648, start 5
459 ELSIF l_lac_reval_history(l_idx2).transaction_type_code = 'RECLASS' THEN
460
461 OPEN c_get_fa_recl_cost_prev(l_lac_reval_history(l_idx2).adjustment_id);
462 FETCH c_get_fa_recl_cost_prev INTO l_get_fa_cost_prev;
463 IF c_get_fa_recl_cost_prev%FOUND THEN
464 l_get_previous_cost := l_get_fa_cost_prev.cost +l_get_iac_cost.iac_cost;
465 ELSE
466 l_get_previous_cost := l_get_iac_cost.iac_cost;
467 END IF;
468 CLOSE c_get_fa_recl_cost_prev;
469 -- bug 3587648, end 5
470 -- bug 3394103 start 4
471 ELSE
472 -- not addition, revaluation or reclass
473 -- for all other transactions
474 OPEN c_get_fa_cost_prev(l_lac_reval_history(l_idx2).transaction_header_id,
475 l_lac_reval_history(l_idx2).adjustment_id);
476 FETCH c_get_fa_cost_prev INTO l_get_fa_cost_prev;
477 IF c_get_fa_cost_prev%FOUND THEN
478
479 l_get_previous_cost := l_get_fa_cost_prev.cost +l_get_iac_cost.iac_cost;
480 ELSE
481 l_get_previous_cost := l_get_iac_cost.iac_cost;
482 END IF;
483 CLOSE c_get_fa_cost_prev;
484 IF l_lac_reval_history(l_idx2).transaction_type_code = 'ADJUSTMENT' THEN
485 OPEN c_get_adj_type(l_lac_reval_history(l_idx2).transaction_header_id);
486 FETCH c_get_adj_type INTO l_get_adj_type;
487 IF c_get_adj_type%FOUND THEN
488 IF l_get_adj_type.transaction_subtype='EXPENSED' THEN
489 IF igi_iac_common_utils.Get_Period_Info_for_Date(p_book_type_code,
490 l_get_fa_cost.date_placed_in_service,
491 l_prd_rec_pre ) THEN
492 NULL;
493 END IF;
494 ELSE
495 IF igi_iac_common_utils.Get_Period_Info_for_Date(p_book_type_code,
496 l_get_adj_type.transaction_date_entered,
497 l_prd_rec_pre ) THEN
498 NULL;
499 END IF;
500 END IF ;
501 END IF;
502 CLOSE c_get_adj_type;
503 END IF;
504 END IF;
505 -- bug 3394103 end 4
506 IF l_lac_reval_history(l_idx2).transaction_type_code = 'RECLASS' THEN
507 IF igi_iac_common_utils.Get_Period_Info_for_Date(p_book_type_code,
508 l_get_fa_cost.date_placed_in_service,
509 l_prd_rec_pre ) THEN
510 NULL;
511 END IF;
512 -- ELSE
513 -- l_prd_rec_pre := l_prd_rec;
514 END IF;
515 END IF;
516 CLOSE c_get_iac_cost;
517 END IF;
518 CLOSE c_get_iac_Prev_transactions;
519 END IF;
520
521 IF l_lac_reval_history(l_idx2).transaction_type_code = 'REVALUATION' THEN
522 IF (l_lac_reval_history(l_idx2).transaction_sub_type = 'OCCASSIONAL') THEN
523 l_lac_reval_history(l_idx2).transaction_type_code := 'OCCASIONAL';
524 l_lac_reval_history(l_idx2).transaction_sub_type := 'INDEXED';
525 ELSIF (l_lac_reval_history(l_idx2).transaction_sub_type = 'PROFESSIONAL') THEN
526 l_lac_reval_history(l_idx2).transaction_type_code := 'OCCASIONAL';
527 ELSIF (l_lac_reval_history(l_idx2).transaction_sub_type = 'IMPLEMENTATION') THEN
528 l_lac_reval_history(l_idx2).transaction_type_code := 'IMPLEMENTATION';
529 l_lac_reval_history(l_idx2).transaction_sub_type := 'IMPLEMENTATION';
530 END IF;
531 END IF;
532 IF (l_lac_reval_history(l_idx2).transaction_type_code = 'ADJUSTMENT') THEN
533 l_lac_reval_history(l_idx2).transaction_type_code := 'ADJUSTMENT';
534 l_lac_reval_history(l_idx2).transaction_sub_type := 'INDEXED';
535 END IF;
536 END IF;
537
538 INSERT INTO igi_iac_reval_history
539 ( ASSET_ID ,
540 BOOK_TYPE_CODE ,
541 ADJUSTMENT_ID ,
542 PERIOD_COUNTER ,
543 REVALUATION_TYPE ,
544 REVALUATION_METHOD ,
545 EFFECTIVE_PERIOD ,
546 PERIOD_ENTERED ,
547 PRE_REVAL_COST ,
548 NEW_REVAL_COST )
549 VALUES
550 (p_asset_id,
551 P_book_type_code,
552 l_get_current_adj_id,
553 l_lac_reval_history(l_idx2).period_counter,
554 l_lac_reval_history(l_idx2).transaction_type_code,
555 l_lac_reval_history(l_idx2).transaction_sub_type,
556 l_prd_rec_pre.period_name,
557 l_prd_rec.period_name,
558 l_get_previous_cost,
559 l_get_current_Cost );
560 END IF;
561
562 END LOOP;
563 CLOSE C_get_iac_transactions;
564 RETURN TRUE;
565
566 EXCEPTION
567 WHEN others THEN
568 begin
569 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
570 return false;
571 END;
572 END insert_rows;
573
574
575 -- ==========================================================================
576 -- FUNCTION Delete_Rows: Function deletes rows from igi_iac_reval_history
577 -- ==========================================================================
578 FUNCTION Delete_rows( P_Asset_id Number,
579 P_Book_type_code Varchar2)
580 RETURN boolean IS
581
582 CURSOR c_get_iac_history IS
583 SELECT *
584 FROM IGI_IAC_REVAL_HISTORY
585 WHERE asset_id = p_asset_id
586 AND book_type_code = p_book_type_code;
587
588 l_get_iac_history c_get_iac_history%ROWTYPE;
589 l_path VARCHAR2(100);
590
591 BEGIN
592 l_path := g_path||'Delete_rows';
593 --test records exists for the asset in the table if yes then return else
594 -- process
595
596 OPEN c_get_iac_history;
597 FETCH c_get_iac_history INTO l_get_iac_history;
598 IF c_get_iac_history%NOTFOUND THEN
599 CLOSE c_get_iac_history;
600 RETURN TRUE;
601 END IF;
602 CLOSE c_get_iac_history;
603
604 DELETE FROM igi_iac_reval_history
605 WHERE asset_id = p_asset_id
606 AND book_type_code = p_book_type_code;
607
608 RETURN TRUE;
609 EXCEPTION
610 WHEN others THEN
611 begin
612 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
613 RETURN FALSE;
614 END;
615 END delete_rows;
616
617 BEGIN
618 --===========================FND_LOG.START=====================================
619 g_state_level := FND_LOG.LEVEL_STATEMENT;
620 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
621 g_event_level := FND_LOG.LEVEL_EVENT;
622 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
623 g_error_level := FND_LOG.LEVEL_ERROR;
624 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
625 g_path := 'IGI.PLSQL.igiiarhb.IGI_IAC_REVAL_HIST_PKG.';
626 --===========================FND_LOG.END=====================================
627
628 END igi_iac_reval_hist_pkg; -- Package spec
[Home] [Help]