1 PACKAGE BODY FA_REVALUATION_PUB as
2 /* $Header: FAPRVLB.pls 120.11.12020000.4 2012/12/05 12:10:10 gigupta ship $ */
3
4 --*********************** Global constants ******************************--
5
6 G_PKG_NAME CONSTANT varchar2(30) := 'FA_REVALUATION_PUB';
7 G_API_NAME CONSTANT varchar2(30) := 'Revaluation API';
8 G_API_VERSION CONSTANT number := 1.0;
9
10 g_log_level_rec fa_api_types.log_level_rec_type;
11 g_release number := fa_cache_pkg.fazarel_release;
12
13 --*********************** Private functions ******************************--
14
15 -- private declaration for books (mrc) wrapper
16
17 g_cip_cost number := 0;
18 g_cost number := 0;
19
20 FUNCTION do_all_books
21 (px_trans_rec IN OUT NOCOPY FA_API_TYPES.trans_rec_type,
22 px_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type,
23 p_asset_desc_rec IN FA_API_TYPES.asset_desc_rec_type,
24 p_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
25 p_asset_cat_rec IN FA_API_TYPES.asset_cat_rec_type,
26 p_reval_options_rec IN FA_API_TYPES.reval_options_rec_type
27 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN;
28
29
30 --*********************** Public procedures ******************************--
31
32 PROCEDURE do_reval
33 (p_api_version IN NUMBER,
34 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
35 p_commit IN VARCHAR2 := FND_API.G_FALSE,
36 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
37 p_calling_fn IN VARCHAR2,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2,
41
42 px_trans_rec IN OUT NOCOPY FA_API_TYPES.trans_rec_type,
43 px_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type,
44 p_reval_options_rec IN FA_API_TYPES.reval_options_rec_type) IS
45
46 l_reporting_flag varchar2(1);
47
48 x_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
49 x_asset_fin_mrc_tbl_new FA_API_TYPES.asset_fin_tbl_type;
50 x_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
51 x_asset_deprn_mrc_tbl_new FA_API_TYPES.asset_deprn_tbl_type;
52
53 l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
54 l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
55 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
56
57 l_calling_fn VARCHAR2(35) := 'fa_reval_pub.do_reval';
58 reval_err EXCEPTION;
59
60
61 BEGIN
62
63 SAVEPOINT do_reval;
64
65 if (not g_log_level_rec.initialized) then
66 if (NOT fa_util_pub.get_log_level_rec (
67 x_log_level_rec => g_log_level_rec
68 )) then
69 raise reval_err;
70 end if;
71 end if;
72
73 g_release := fa_cache_pkg.fazarel_release;
74
75 -- Initialize message list if p_init_msg_list is set to TRUE.
76 if (fnd_api.to_boolean(p_init_msg_list)) then
77 -- initialize error message stack.
78 fa_srvr_msg.init_server_message;
79
80 -- initialize debug message stack.
81 fa_debug_pkg.initialize;
82 end if;
83
84 -- Check version of the API
85 -- Standard call to check for API call compatibility.
86 if NOT fnd_api.compatible_api_call (
87 G_API_VERSION,
88 p_api_version,
89 G_API_NAME,
90 G_PKG_NAME
91 ) then
92 x_return_status := FND_API.G_RET_STS_ERROR;
93 raise reval_err;
94 end if;
95
96 -- call the cache for the primary transaction book
97 if NOT fa_cache_pkg.fazcbc(X_book => px_asset_hdr_rec.book_type_code, p_log_level_rec => g_log_level_rec) then
98 raise reval_err;
99 end if;
100
101 px_asset_hdr_rec.set_of_books_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
102
103 -- verify the book allows revaluation
104 if (nvl(fa_cache_pkg.fazcbc_record.allow_reval_flag, 'NO') = 'NO') then
105 fa_srvr_msg.add_message
106 (calling_fn => l_calling_fn,
107 name => 'FA_BOOK_REVAL_NOT_ALLOW', p_log_level_rec => g_log_level_rec);
108 raise reval_err;
109 end if;
110
111 -- verify the asset exist in the book already
112 if not FA_ASSET_VAL_PVT.validate_asset_book
113 (p_transaction_type_code => 'ADJUSTMENT',
114 p_book_type_code => px_asset_hdr_rec.book_type_code,
115 p_asset_id => px_asset_hdr_rec.asset_id,
116 p_calling_fn => l_calling_fn
117 , p_log_level_rec => g_log_level_rec) then
118 raise reval_err;
119 end if;
120
121 -- Account for transaction submitted from a responsibility
122 -- that is not tied to a SOB_ID by getting the value from
123 -- the book struct
124
125 -- Get the book type code P,R or N
126 if not fa_cache_pkg.fazcsob
127 (X_set_of_books_id => px_asset_hdr_rec.set_of_books_id,
128 X_mrc_sob_type_code => l_reporting_flag
129 , p_log_level_rec => g_log_level_rec) then
130 raise reval_err;
131 end if;
132
133 --Verify if impairment has happened in same period
134 if not FA_ASSET_VAL_PVT.validate_impairment_exists
135 (p_asset_id => px_asset_hdr_rec.asset_id,
136 p_book => px_asset_hdr_rec.book_type_code,
137 p_mrc_sob_type_code => l_reporting_flag,
138 p_set_of_books_id => px_asset_hdr_rec.set_of_books_id,
139 p_log_level_rec => g_log_level_rec) then
140 raise reval_err;
141 end if;
142
143 -- Error out if the program is submitted from the Reporting Responsibility
144 -- No transaction permitted directly on reporting books.
145
146 IF l_reporting_flag = 'R' THEN
147 fa_srvr_msg.add_message
148 (calling_fn => l_calling_fn,
149 name => 'MRC_OSP_INVALID_BOOK_TYPE', p_log_level_rec => g_log_level_rec);
150 raise reval_err;
151 END IF;
152
153 -- end initial MRC validation
154
155
156 -- pop the structs for the non-fin information needed for trx
157
158 if not FA_UTIL_PVT.get_asset_desc_rec
159 (p_asset_hdr_rec => px_asset_hdr_rec,
160 px_asset_desc_rec => l_asset_desc_rec
161 , p_log_level_rec => g_log_level_rec) then
162 raise reval_err;
163 end if;
164
165 if not FA_UTIL_PVT.get_asset_cat_rec
166 (p_asset_hdr_rec => px_asset_hdr_rec,
167 px_asset_cat_rec => l_asset_cat_rec,
168 p_date_effective => null
169 , p_log_level_rec => g_log_level_rec) then
170 raise reval_err;
171 end if;
172
173 if not FA_UTIL_PVT.get_asset_type_rec
174 (p_asset_hdr_rec => px_asset_hdr_rec,
175 px_asset_type_rec => l_asset_type_rec,
176 p_date_effective => null
177 , p_log_level_rec => g_log_level_rec) then
178 raise reval_err;
179 end if;
180
181 -- also check if this is the period of addition - use absolute mode for revaluations
182 -- do not allow reval on assets in the period of initial addition, but it's ok on the
183 -- asset in same period as capitalization
184
185 if not FA_ASSET_VAL_PVT.validate_period_of_addition
186 (p_asset_id => px_asset_hdr_rec.asset_id,
187 p_book => px_asset_hdr_rec.book_type_code,
188 p_mode => 'ABSOLUTE',
189 px_period_of_addition => px_asset_hdr_rec.period_of_addition, p_log_level_rec => g_log_level_rec) then
190 raise reval_err;
191 elsif (px_asset_hdr_rec.period_of_addition = 'Y' and
192 G_release = 11) then
193 fa_srvr_msg.add_message
194 (calling_fn => l_calling_fn,
195 name => 'FA_REVAL_NO_DEPRECIATED', p_log_level_rec => g_log_level_rec);
196 raise reval_err;
197 end if;
198
199 -- call the mrc wrapper for the transaction book
200
201 if not do_all_books
202 (px_trans_rec => px_trans_rec,
203 px_asset_hdr_rec => px_asset_hdr_rec ,
204 p_asset_desc_rec => l_asset_desc_rec ,
205 p_asset_type_rec => l_asset_type_rec ,
206 p_asset_cat_rec => l_asset_cat_rec ,
207 p_reval_options_rec => p_reval_options_rec ,
208 p_log_level_rec => g_log_level_rec
209 )then
210 raise reval_err;
211 end if;
212
213 -- If book is a corporate book, process cip assets and autocopy
214 -- NOTE: not implementing this for REVAL!
215
216 -- commit if p_commit is TRUE.
217 if (fnd_api.to_boolean (p_commit) and
218 p_reval_options_rec.run_mode = 'RUN') then
219 COMMIT WORK;
220 end if;
221
222 x_return_status := FND_API.G_RET_STS_SUCCESS;
223
224
225 EXCEPTION
226
227 when reval_err then
228 ROLLBACK TO do_reval;
229
230 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
231
232 FND_MSG_PUB.count_and_get (
233 p_count => x_msg_count,
234 p_data => x_msg_data
235 );
236
237 x_return_status := FND_API.G_RET_STS_ERROR;
238
239 when others then
240 ROLLBACK TO do_reval;
241
242 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
243
244 FND_MSG_PUB.count_and_get (
245 p_count => x_msg_count,
246 p_data => x_msg_data
247 );
248
249 x_return_status := FND_API.G_RET_STS_ERROR;
250
251 END do_reval;
252
253 -----------------------------------------------------------------------------
254
255 -- Books (MRC) Wrapper - called from public API above
256 --
257 -- For non mrc books, this just calls the private API with provided params
258 -- For MRC, it processes the primary and then loops through each reporting
259 -- book calling the private api for each.
260
261
262 FUNCTION do_all_books
263 (px_trans_rec IN OUT NOCOPY FA_API_TYPES.trans_rec_type,
264 px_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type,
265 p_asset_desc_rec IN FA_API_TYPES.asset_desc_rec_type,
266 p_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
267 p_asset_cat_rec IN FA_API_TYPES.asset_cat_rec_type,
268 p_reval_options_rec IN FA_API_TYPES.reval_options_rec_type
269 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN IS
270
271 -- used for calling private api for reporting books
272 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
273
274 -- used for retrieving "old" and "new" structs from private api calls
275 l_asset_fin_rec_old FA_API_TYPES.asset_fin_rec_type;
276 l_asset_deprn_rec_old FA_API_TYPES.asset_deprn_rec_type;
277
278 l_reporting_flag varchar2(1);
279
280 l_period_rec FA_API_TYPES.period_rec_type;
281 l_sob_tbl FA_CACHE_PKG.fazcrsob_sob_tbl_type;
282
283 l_transaction_date date;
284 l_return_status NUMBER;
285
286 l_calling_fn varchar2(30) := 'fa_reval_pub.do_all_books';
287 reval_err EXCEPTION;
288
289 BEGIN
290
291 -- BUG# 2247404 and 2230178 - call regardless if from a mass request
292 if not FA_TRX_APPROVAL_PKG.faxcat
293 (X_book => px_asset_hdr_rec.book_type_code,
294 X_asset_id => px_asset_hdr_rec.asset_id,
295 X_trx_type => px_trans_rec.transaction_type_code,
296 X_trx_date => px_trans_rec.transaction_date_entered,
297 X_init_message_flag => 'NO'
298 , p_log_level_rec => p_log_level_rec) then
299 raise reval_err;
300 end if;
301
302
303 -- load the period struct for current period info
304 if not FA_UTIL_PVT.get_period_rec
305 (p_book => px_asset_hdr_rec.book_type_code,
306 p_effective_date => NULL,
307 x_period_rec => l_period_rec
308 , p_log_level_rec => p_log_level_rec) then
309 raise reval_err;
310 end if;
311
312
313 -- verify asset is not fully retired ? valid for reval?
314 if fa_asset_val_pvt.validate_fully_retired
315 (p_asset_id => px_asset_hdr_rec.asset_id,
316 p_book => px_asset_hdr_rec.book_type_code, p_log_level_rec => p_log_level_rec) then
317 fa_srvr_msg.add_message
318 (name => 'FA_REC_RETIRED',
319 calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
320 raise reval_err;
321 end if;
322
323
324 -- call the sob cache to get the table of sob_ids
325 if not FA_CACHE_PKG.fazcrsob
326 (x_book_type_code => px_asset_hdr_rec.book_type_code,
327 x_sob_tbl => l_sob_tbl, p_log_level_rec => p_log_level_rec) then
328 raise reval_err;
329 end if;
330
331 -- set up the local asset_header and sob_id
332 l_asset_hdr_rec := px_asset_hdr_rec;
333
334
335 -- loop through each book starting with the primary and
336 -- call the private API for each
337 FOR l_sob_index in 0..l_sob_tbl.count LOOP
338
339 if (l_sob_index = 0) then
340 l_reporting_flag := 'P';
341 else
342 l_reporting_flag := 'R';
343 l_asset_hdr_rec.set_of_books_id := l_sob_tbl(l_sob_index);
344 end if;
345
346 -- call the cache to set the sob_id used for rounding and other lower
347 -- level code for each book.
348 if NOT fa_cache_pkg.fazcbcs(X_book => px_asset_hdr_rec.book_type_code,
349 X_set_of_books_id => l_asset_hdr_rec.set_of_books_id,
350 p_log_level_rec => p_log_level_rec) then
351 raise reval_err;
352 end if;
353
354 -- load the old structs
355 if not FA_UTIL_PVT.get_asset_fin_rec
356 (p_asset_hdr_rec => l_asset_hdr_rec,
357 px_asset_fin_rec => l_asset_fin_rec_old,
358 p_transaction_header_id => NULL,
359 p_mrc_sob_type_code => l_reporting_flag
360 , p_log_level_rec => p_log_level_rec) then raise reval_err;
361 end if;
362
363 -- set the trx date
364 -- note favrvl.opc currently only uses greatest (sysdate/cpod)
365
366 l_transaction_date := greatest(l_period_rec.calendar_period_open_date,
367 least(sysdate,l_period_rec.calendar_period_close_date));
368
369 px_trans_rec.transaction_date_entered :=
370 to_date(to_char(l_transaction_date,'DD/MM/YYYY'),'DD/MM/YYYY');
371
372 if not FA_UTIL_PVT.get_asset_deprn_rec
373 (p_asset_hdr_rec => l_asset_hdr_rec ,
374 px_asset_deprn_rec => l_asset_deprn_rec_old,
375 p_period_counter => NULL,
376 p_mrc_sob_type_code => l_reporting_flag
377 , p_log_level_rec => p_log_level_rec) then raise reval_err;
378 end if;
379
380 -- load the adj structs
381 if (l_sob_index = 0) then
382
383 -- validate changes are being made and are valid
384 if not fa_revaluation_pvt.validate_reval
385 (p_trans_rec => px_trans_rec,
386 p_asset_hdr_rec => px_asset_hdr_rec,
387 p_asset_desc_rec => p_asset_desc_rec,
388 p_asset_type_rec => p_asset_type_rec,
389 p_asset_cat_rec => p_asset_cat_rec,
390 p_asset_fin_rec_old => l_asset_fin_rec_old,
391 p_asset_deprn_rec_old => l_asset_deprn_rec_old,
392 p_reval_options_rec => p_reval_options_rec, p_log_level_rec => p_log_level_rec) then
393 raise reval_err;
394 end if;
395
396 -- no else else here for reporting as we do nothing with rate converisons, etc
397
398 end if; -- primary of reporting
399
400
401 -- call the private API for primary and reporting using the local variables for sob related info
402 -- only if the mode is RUN
403 -- if the mode is PREVIEW call the private API only for primary
404 -- spooyath
405
406
407 if (p_reval_options_rec.run_mode = 'PREVIEW') then
408
409 if (l_sob_index = 0) then
410
411 /* ER 14644811 */
412 if p_reval_options_rec.period_end_reval_flag = 'Y' then
413 if not FA_REVALUATION_PVT.process_depreciation
414 (p_trans_rec => px_trans_rec,
415 p_asset_hdr_rec => l_asset_hdr_rec ,
416 p_asset_desc_rec => p_asset_desc_rec ,
417 p_asset_type_rec => p_asset_type_rec ,
418 p_asset_cat_rec => p_asset_cat_rec ,
419 p_period_rec => l_period_rec,
420 px_asset_fin_rec_old => l_asset_fin_rec_old,
421 px_asset_deprn_rec_old => l_asset_deprn_rec_old,
422 p_reval_options_rec => p_reval_options_rec,
423 p_mrc_sob_type_code => l_reporting_flag,
424 p_log_level_rec => p_log_level_rec) then
425 raise reval_err;
426 end if;
427 end if;
428
429 if not FA_REVALUATION_PVT.do_reval
430 (px_trans_rec => px_trans_rec,
431 px_asset_hdr_rec => l_asset_hdr_rec , -- mrc
432 p_asset_desc_rec => p_asset_desc_rec ,
433 p_asset_type_rec => p_asset_type_rec ,
434 p_asset_cat_rec => p_asset_cat_rec ,
435 p_asset_fin_rec_old => l_asset_fin_rec_old, -- mrc
436 p_asset_deprn_rec_old => l_asset_deprn_rec_old, -- mrc
437 p_period_rec => l_period_rec,
438 p_mrc_sob_type_code => l_reporting_flag,
439 p_reval_options_rec => p_reval_options_rec,
440 p_calling_fn => l_calling_fn
441 , p_log_level_rec => p_log_level_rec)then
442 raise reval_err;
443 end if;
444 EXIT;
445 end if;
446 else
447 /*ER 14644811 */
448 if p_reval_options_rec.period_end_reval_flag = 'Y' then
449 if not FA_REVALUATION_PVT.process_depreciation
450 (p_trans_rec => px_trans_rec,
451 p_asset_hdr_rec => l_asset_hdr_rec ,
452 p_asset_desc_rec => p_asset_desc_rec ,
453 p_asset_type_rec => p_asset_type_rec ,
454 p_asset_cat_rec => p_asset_cat_rec ,
455 p_period_rec => l_period_rec,
456 px_asset_fin_rec_old => l_asset_fin_rec_old,
457 px_asset_deprn_rec_old => l_asset_deprn_rec_old,
458 p_reval_options_rec => p_reval_options_rec,
459 p_mrc_sob_type_code => l_reporting_flag,
460 p_log_level_rec => p_log_level_rec) then
461 raise reval_err;
462 end if;
463 end if;
464
465 if not FA_REVALUATION_PVT.do_reval
466 (px_trans_rec => px_trans_rec,
467 px_asset_hdr_rec => l_asset_hdr_rec , -- mrc
468 p_asset_desc_rec => p_asset_desc_rec ,
469 p_asset_type_rec => p_asset_type_rec ,
470 p_asset_cat_rec => p_asset_cat_rec ,
471 p_asset_fin_rec_old => l_asset_fin_rec_old, -- mrc
472 p_asset_deprn_rec_old => l_asset_deprn_rec_old, -- mrc
473 p_period_rec => l_period_rec,
474 p_mrc_sob_type_code => l_reporting_flag,
475 p_reval_options_rec => p_reval_options_rec,
476 p_calling_fn => l_calling_fn
477 , p_log_level_rec => p_log_level_rec)then
478 raise reval_err;
479 end if;
480 end if;
481
482 -- do not insert the books_rates record for reval
483 -- no group logic here (not allowed on group members)
484
485 -- should be need for an IAC hook - verify!
486
487 end loop; -- sob loop
488
489 /* ER 14644811 */
490 if g_release <> 11 and p_reval_options_rec.period_end_reval_flag = 'Y' and p_reval_options_rec.run_mode = 'RUN' then
491
492 FA_DEPRN_EVENTS_PKG.process_single_deprn_event
493 (p_book_type_code => l_asset_hdr_rec.book_type_code,
494 p_period_counter => l_period_rec.period_counter,
495 p_asset_id => l_asset_hdr_rec.asset_id,
496 p_calling_fn => l_calling_fn,
497 x_return_status => l_return_status,
498 p_log_level_rec => p_log_level_rec);
499
500 if (p_log_level_rec.statement_level) then
501 fa_debug_pkg.add(l_calling_fn,'l_return_status after process_deprn_events ', l_return_status, p_log_level_rec => p_log_level_rec);
502 end if;
503
504 if l_return_status <> 0 then
505 if (p_log_level_rec.statement_level) then
506 fa_debug_pkg.add(l_calling_fn,'Process deprn event has failed ', 'true', p_log_level_rec => p_log_level_rec);
507 end if;
508 raise reval_err;
509 end if;
510
511 end if;
512
513 return true;
514
515 EXCEPTION
516
517 WHEN reval_err THEN
518 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
519 return FALSE;
520
521 WHEN OTHERS THEN
522 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
523 return FALSE;
524
525 END do_all_books;
526
527 -----------------------------------------------------------------------------
528
529 END FA_REVALUATION_PUB;
[Home] [Help]