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