[Home] [Help]
PACKAGE BODY: APPS.FA_RETIREMENT_ADJUSTMENT_PUB
Source
1 PACKAGE BODY FA_RETIREMENT_ADJUSTMENT_PUB AS
2 /* $Header: FAPRADJB.pls 120.15 2005/09/14 19:18:44 bridgway noship $ */
3
4
5 --*********************** Global constants ******************************--
6
7 G_PKG_NAME CONSTANT varchar2(30) := 'FA_ADJUSTMENT_PUB';
8 G_API_NAME CONSTANT varchar2(30) := 'Adjustment API';
9 G_API_VERSION CONSTANT number := 1.0;
10
11 g_log_level_rec fa_api_types.log_level_rec_type;
12
13
14 --*********************** Private functions ******************************--
15 -- This private function calls fa_asset_val_pvt.validate_over_depreciate
16 -- to determine this retirement adjustment is valid or not
17 FUNCTION validate_over_depreciate
18 (p_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type,
19 p_asset_type_rec FA_API_TYPES.asset_type_rec_type,
20 p_asset_fin_rec FA_API_TYPES.asset_fin_rec_type,
21 p_asset_deprn_rec FA_API_TYPES.asset_deprn_rec_type,
22 p_proceeds_of_sale NUMBER,
23 p_cost_of_removal NUMBER,
24 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN;
25
26 FUNCTION do_all_books
27 (px_trans_rec IN OUT NOCOPY FA_API_TYPES.trans_rec_type,
28 px_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type,
29 p_cost_of_removal IN NUMBER,
30 p_proceeds IN NUMBER,
31 p_cost_of_removal_ccid IN NUMBER,
32 p_proceeds_ccid IN NUMBER,
33 p_log_level_rec in fa_api_types.log_level_rec_type default null ) RETURN BOOLEAN;
34
35
36 --*********************** Public procedures ******************************--
37
38
39 PROCEDURE do_retirement_adjustment
40 (p_api_version IN NUMBER,
41 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
42 p_commit IN VARCHAR2 := FND_API.G_FALSE,
43 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
44 p_calling_fn IN VARCHAR2,
45 x_return_status OUT NOCOPY VARCHAR2,
46 x_msg_count OUT NOCOPY NUMBER,
47 x_msg_data OUT NOCOPY VARCHAR2,
48
49 px_trans_rec IN OUT NOCOPY FA_API_TYPES.trans_rec_type,
50 px_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type,
51 p_cost_of_removal IN NUMBER,
52 p_proceeds IN NUMBER,
53 p_cost_of_removal_ccid IN NUMBER DEFAULT NULL,
54 p_proceeds_ccid IN NUMBER DEFAULT NULL) IS
55
56 l_reporting_flag varchar2(1);
57
58 -- used to store original sob info upon entry into api
59 l_orig_set_of_books_id number;
60 l_orig_currency_context varchar2(64);
61
62 l_calling_fn VARCHAR2(35) := 'fa_ret_adj_pub.do_ret_adj';
63 ret_adj_err EXCEPTION;
64
65 BEGIN
66
67 SAVEPOINT do_retirement_adjustment;
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 ret_adj_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 ret_adj_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 ret_adj_err;
101 end if;
102
103 -- verify the asset exists in the book already
104 if not FA_ASSET_VAL_PVT.validate_asset_book
105 (p_transaction_type_code => 'ADJUSTMENT',
106 p_book_type_code => px_asset_hdr_rec.book_type_code,
107 p_asset_id => px_asset_hdr_rec.asset_id,
108 p_calling_fn => l_calling_fn,
109 p_log_level_rec => g_log_level_rec) then
110 raise ret_adj_err;
111 end if;
112
113 -- get the current info for the primary book
114
115 fnd_profile.get ('GL_SET_OF_BKS_ID',l_orig_set_of_books_id);
116 l_orig_currency_context := SUBSTRB(USERENV('CLIENT_INFO'),45,10);
117
118 fnd_profile.put('GL_SET_OF_BKS_ID', fa_cache_pkg.fazcbc_record.set_of_books_id);
119 fnd_client_info.set_currency_context (fa_cache_pkg.fazcbc_record.set_of_books_id);
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 => fa_cache_pkg.fazcbc_record.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 ret_adj_err;
131 end if;
132
133 -- Error out if the program is submitted from the Reporting Responsibility
134 -- No transaction permitted directly on reporting books.
135
136 IF l_reporting_flag = 'R' THEN
137 fa_srvr_msg.add_message
138 (calling_fn => l_calling_fn,
139 name => 'MRC_OSP_INVALID_BOOK_TYPE',
140 p_log_level_rec => g_log_level_rec);
141 raise ret_adj_err;
142 END IF;
143
144 -- end initial MRC validation
145
146
147
148 -- call the mrc wrapper for the transaction book
149
150 if not do_all_books
151 (px_asset_hdr_rec => px_asset_hdr_rec,
152 px_trans_rec => px_trans_rec,
153 p_cost_of_removal => p_cost_of_removal,
154 p_proceeds => p_proceeds,
155 p_cost_of_removal_ccid => p_cost_of_removal_ccid,
156 p_proceeds_ccid => p_proceeds_ccid,
157 p_log_level_rec => g_log_level_rec)then
158 raise ret_adj_err;
159 end if;
160
161 -- no auto-copy / cip in tax for group reclass transactions
162
163 -- commit if p_commit is TRUE.
164 if (fnd_api.to_boolean (p_commit)) then
165 COMMIT WORK;
166 end if;
167
168 x_return_status := FND_API.G_RET_STS_SUCCESS;
169
170 EXCEPTION
171
172 when ret_adj_err then
173 ROLLBACK TO do_retirement_adjustment;
174
175 fnd_profile.put('GL_SET_OF_BKS_ID', l_orig_set_of_books_id);
176 fnd_client_info.set_currency_context (l_orig_currency_context);
177
178 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
179 p_log_level_rec => g_log_level_rec);
180
181 -- do not retrieve / clear messaging when this is being called
182 -- from reclass api - allow calling util to dump them
183 FND_MSG_PUB.count_and_get (
184 p_count => x_msg_count,
185 p_data => x_msg_data
186 );
187 x_return_status := FND_API.G_RET_STS_ERROR;
188
189 when others then
190 ROLLBACK TO do_retirement_adjustment;
191
192 fnd_profile.put('GL_SET_OF_BKS_ID', l_orig_set_of_books_id);
193 fnd_client_info.set_currency_context (l_orig_currency_context);
194
195 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
196 ,p_log_level_rec => g_log_level_rec);
197
198 -- do not retrieve / clear messaging when this is being called
199 -- from reclass api - allow calling util to dump them
200 FND_MSG_PUB.count_and_get (
201 p_count => x_msg_count,
202 p_data => x_msg_data
203 );
204
205 x_return_status := FND_API.G_RET_STS_ERROR;
206
207 END do_retirement_adjustment;
208
209 -----------------------------------------------------------------------------
210
211 -- Books (MRC) Wrapper - called from public API above
212 --
213 -- For non mrc books, this just calls the private API with provided params
214 -- For MRC, it processes the primary and then loops through each reporting
215 -- book calling the private api for each.
216
217
218 FUNCTION do_all_books
219 (px_trans_rec IN OUT NOCOPY FA_API_TYPES.trans_rec_type,
220 px_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type,
221 p_cost_of_removal IN NUMBER,
222 p_proceeds IN NUMBER,
223 p_cost_of_removal_ccid IN NUMBER,
224 p_proceeds_ccid IN NUMBER,
225 p_log_level_rec IN fa_api_types.log_level_rec_type default null) RETURN BOOLEAN IS
226
227
228 -- used for new source asset
229 l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
230 l_mrc_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
231 l_asset_desc_rec fa_api_types.asset_desc_rec_type;
232 l_asset_type_rec fa_api_types.asset_type_rec_type;
233 l_asset_cat_rec fa_api_types.asset_cat_rec_type;
234 l_asset_fin_rec_old fa_api_types.asset_fin_rec_type;
235 l_asset_fin_rec_new fa_api_types.asset_fin_rec_type;
236 l_asset_deprn_rec_old fa_api_types.asset_deprn_rec_type;
237 l_asset_deprn_rec_new fa_api_types.asset_deprn_rec_type;
238
239 l_proceeds number;
240 l_cost_of_removal number;
241
242 l_primary_cost number; -- ??? use ???
243
244 l_period_rec FA_API_TYPES.period_rec_type;
245 l_rsob_tbl FA_CACHE_PKG.fazcrsob_sob_tbl_type;
246 l_reporting_flag varchar2(1);
247
248 l_exchange_rate number;
249 l_avg_rate number;
250
251 l_rowid varchar2(40);
252 l_return_status boolean;
253
254 l_calling_fn VARCHAR2(35) := 'fa_ret_adj_pub.do_all_books';
255 ret_adj_err exception;
256
257 BEGIN
258
259 -- load the initial values in structs
260 l_asset_hdr_rec := px_asset_hdr_rec;
261
262 px_trans_rec.transaction_type_code := 'GROUP ADJUSTMENT'; -- **** ??? ****
263 px_trans_rec.transaction_subtype := 'AMORTIZED';
264 px_trans_rec.transaction_key := 'GR';
265
266 -- we need the thid first for inserting clearing into adjustments
267 select fa_transaction_headers_s.nextval
268 into px_trans_rec.transaction_header_id
269 from dual;
270
271 -- load the period struct for current period info
272 if not FA_UTIL_PVT.get_period_rec
273 (p_book => l_asset_hdr_rec.book_type_code,
274 p_effective_date => NULL,
275 x_period_rec => l_period_rec,
276 p_log_level_rec => p_log_level_rec) then
277 raise ret_adj_err;
278 end if;
279
280 -- note that we need to investigate / determine transaction subtype and track member impacts!!!!
281 -- how to handle amort start / trx_date , etc on the member in all three scenarios
282
283 px_trans_rec.transaction_date_entered :=
284 greatest(l_period_rec.calendar_period_open_date,
285 least(sysdate,l_period_rec.calendar_period_close_date));
286
287 -- call transaction approval for source asset
288 if not FA_TRX_APPROVAL_PKG.faxcat
289 (X_book => px_asset_hdr_rec.book_type_code,
290 X_asset_id => px_asset_hdr_rec.asset_id,
291 X_trx_type => px_trans_rec.transaction_type_code,
292 X_trx_date => px_trans_rec.transaction_date_entered,
293 X_init_message_flag => 'NO',
294 p_log_level_rec => p_log_level_rec) then
295 raise ret_adj_err;
296 end if;
297
298
299 -- also check if this is the period of addition - use absolute mode for adjustments
300 -- we will only clear cost outside period of addition
301 if not FA_ASSET_VAL_PVT.validate_period_of_addition
302 (p_asset_id => l_asset_hdr_rec.asset_id,
303 p_book => l_asset_hdr_rec.book_type_code,
304 p_mode => 'ABSOLUTE',
305 px_period_of_addition => l_asset_hdr_rec.period_of_addition,
306 p_log_level_rec => p_log_level_rec) then
307 raise ret_adj_err;
308 end if;
309
310
311 -- pop the structs for the non-fin information needed for trx
312 -- source
313 if not FA_UTIL_PVT.get_asset_desc_rec
314 (p_asset_hdr_rec => l_asset_hdr_rec,
315 px_asset_desc_rec => l_asset_desc_rec,
316 p_log_level_rec => p_log_level_rec) then
317 raise ret_adj_err;
318 end if;
319
320 if not FA_UTIL_PVT.get_asset_cat_rec
321 (p_asset_hdr_rec => l_asset_hdr_rec,
322 px_asset_cat_rec => l_asset_cat_rec,
323 p_date_effective => null,
324 p_log_level_rec => p_log_level_rec) then
325 raise ret_adj_err;
326 end if;
327
328 if not FA_UTIL_PVT.get_asset_type_rec
329 (p_asset_hdr_rec => l_asset_hdr_rec,
330 px_asset_type_rec => l_asset_type_rec,
331 p_date_effective => null,
332 p_log_level_rec => p_log_level_rec) then
333 raise ret_adj_err;
334 end if;
335
336 -- Call the reporting books cache to get rep books.
337 if (NOT fa_cache_pkg.fazcrsob (
338 x_book_type_code => l_asset_hdr_rec.book_type_code,
339 x_sob_tbl => l_rsob_tbl,
340 p_log_level_rec => p_log_level_rec)) then
341 raise ret_adj_err;
342 end if;
343
344 if not FA_XLA_EVENTS_PVT.create_transaction_event
345 (p_asset_hdr_rec => px_asset_hdr_rec,
346 p_asset_type_rec => l_asset_type_rec,
347 px_trans_rec => px_trans_rec,
348 p_event_status => NULL,
349 p_calling_fn => l_calling_fn,
350 p_log_level_rec => p_log_level_rec) then
351 raise ret_adj_err;
352 end if;
353
354 fa_transaction_headers_pkg.insert_row
355 (x_rowid => l_rowid,
356 x_transaction_header_id => px_trans_rec.transaction_header_id,
357 x_book_type_code => px_asset_hdr_rec.book_type_code,
358 x_asset_id => px_asset_hdr_rec.asset_id,
359 x_transaction_type_code => px_trans_rec.transaction_type_code,
360 x_transaction_date_entered => px_trans_rec.transaction_date_entered,
361 x_date_effective => px_trans_rec.who_info.last_update_date,
362 x_last_update_date => px_trans_rec.who_info.last_update_date,
363 x_last_updated_by => px_trans_rec.who_info.last_updated_by,
364 x_transaction_name => px_trans_rec.transaction_name,
365 x_last_update_login => px_trans_rec.who_info.last_update_login,
366 x_transaction_key => px_trans_rec.transaction_key,
367 x_transaction_subtype => px_trans_rec.transaction_subtype,
368 x_amortization_start_date => px_trans_rec.amortization_start_date,
369 x_calling_interface => px_trans_rec.calling_interface,
370 x_mass_transaction_id => px_trans_rec.mass_transaction_id,
371 x_trx_reference_id => px_trans_rec.trx_reference_id,
372 x_return_status => l_return_status,
373 x_calling_fn => l_calling_fn,
374 p_log_level_rec => p_log_level_rec);
375
376
377 for l_mrc_index in 0..l_rsob_tbl.COUNT loop
378
379 l_mrc_asset_hdr_rec := l_asset_hdr_rec;
380
381 if (l_mrc_index = 0) then
382 l_mrc_asset_hdr_rec.set_of_books_id := l_asset_hdr_rec.set_of_books_id;
383 l_reporting_flag := 'P';
384 else
385 l_mrc_asset_hdr_rec.set_of_books_id := l_rsob_tbl(l_mrc_index);
386 l_reporting_flag := 'R';
387 end if;
388
389 -- Set the gl_sob profile to this reporting book
390 fnd_profile.put('GL_SET_OF_BKS_ID', l_mrc_asset_hdr_rec.set_of_books_id);
391 fnd_client_info.set_currency_context (l_mrc_asset_hdr_rec.set_of_books_id);
392
393 -- Need to always call fazcbcs
394 if (NOT fa_cache_pkg.fazcbcs (
395 X_book => l_mrc_asset_hdr_rec.book_type_code,
396 p_log_level_rec => p_log_level_rec)) then
397 raise ret_adj_err;
398 end if;
399
400 -- get the old fin and deprn information
401 if not FA_UTIL_PVT.get_asset_fin_rec
402 (p_asset_hdr_rec => l_mrc_asset_hdr_rec,
403 px_asset_fin_rec => l_asset_fin_rec_old,
404 p_transaction_header_id => NULL,
405 p_mrc_sob_type_code => l_reporting_flag,
406 p_log_level_rec => p_log_level_rec) then raise ret_adj_err;
407 end if;
408 --HH validate disabled_flag
409 if not FA_ASSET_VAL_PVT.validate_disabled_flag
410 (p_group_asset_id => l_mrc_asset_hdr_rec.asset_id,
411 p_book_type_code => l_mrc_asset_hdr_rec.book_type_code,
412 p_old_flag => l_asset_fin_rec_old.disabled_flag,
413 p_new_flag => l_asset_fin_rec_old.disabled_flag,
414 p_log_level_rec => p_log_level_rec) then
415 raise ret_adj_err;
416 end if; -- end HH
417
418 if not FA_UTIL_PVT.get_asset_deprn_rec
419 (p_asset_hdr_rec => l_mrc_asset_hdr_rec ,
420 px_asset_deprn_rec => l_asset_deprn_rec_old,
421 p_period_counter => NULL,
422 p_mrc_sob_type_code => l_reporting_flag,
423 p_log_level_rec => p_log_level_rec) then raise ret_adj_err;
424 end if;
425
426
427 -- in order to derive the transfer amount for the reporting
428 -- books, we will use a ratio of the primary amount / primary cost
429 -- for the source asset
430
431
432 if (l_mrc_index = 0) then
433 l_proceeds := p_proceeds;
434 l_cost_of_removal := p_cost_of_removal;
435 l_primary_cost := l_asset_fin_rec_old.cost;
436 else
437 if (l_primary_cost <> 0) then
438 l_proceeds := p_proceeds * (l_asset_fin_rec_old.cost / l_primary_cost);
439 l_cost_of_removal := p_cost_of_removal * (l_asset_fin_rec_old.cost / l_primary_cost);
440 else
441 -- get the latest average rate (used conditionally in some cases below)
442 if not fa_mc_util_pvt.get_latest_rate
443 (p_asset_id => l_mrc_asset_hdr_rec.asset_id,
444 p_book_type_code => l_mrc_asset_hdr_rec.book_type_code,
445 p_set_of_books_id => l_mrc_asset_hdr_rec.set_of_books_id,
446 px_rate => l_exchange_rate,
447 px_avg_exchange_rate => l_avg_rate ,
448 p_log_level_rec => p_log_level_rec) then raise ret_adj_err;
449 end if;
450
451 l_proceeds := p_proceeds * l_avg_rate;
452 l_cost_of_removal := p_cost_of_removal * l_avg_rate;
453
454 end if;
455 end if;
456
457 if not validate_over_depreciate
458 (p_asset_hdr_rec => l_mrc_asset_hdr_rec,
459 p_asset_type_rec => l_asset_type_rec,
460 p_asset_fin_rec => l_asset_fin_rec_old,
461 p_asset_deprn_rec => l_asset_deprn_rec_old,
462 p_proceeds_of_sale => l_proceeds,
463 p_cost_of_removal => l_cost_of_removal,
464 p_log_level_rec => p_log_level_rec) then
465 raise ret_adj_err;
466 end if;
467
468 -- now call the private api to do the processing
469
470 if not FA_RETIREMENT_ADJUSTMENT_PVT.do_retirement_adjustment
471 (px_trans_rec => px_trans_rec,
472 px_asset_hdr_rec => l_mrc_asset_hdr_rec,
473 p_asset_desc_rec => l_asset_desc_rec,
474 p_asset_type_rec => l_asset_type_rec,
475 p_asset_cat_rec => l_asset_cat_rec,
476 p_asset_fin_rec_old => l_asset_fin_rec_old,
477 x_asset_fin_rec_new => l_asset_fin_rec_new,
478 p_asset_deprn_rec_old => l_asset_deprn_rec_old,
479 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
480 p_period_rec => l_period_rec,
481 p_mrc_sob_type_code => l_reporting_flag,
482 p_cost_of_removal => l_cost_of_removal,
483 p_proceeds => l_proceeds,
484 p_cost_of_removal_ccid => p_cost_of_removal_ccid,
485 p_proceeds_ccid => p_proceeds_ccid,
486 p_log_level_rec => p_log_level_rec)then
487 raise ret_adj_err;
488 end if; -- do_adjustment
489
490 -- reset GL sob id to original value before moving to next book
491 fnd_profile.put('GL_SET_OF_BKS_ID', l_asset_hdr_rec.set_of_books_id);
492 fnd_client_info.set_currency_context (l_asset_hdr_rec.set_of_books_id);
493
494 end loop;
495
496 -- Reset the gl_sob profile
497 fnd_profile.put('GL_SET_OF_BKS_ID', l_asset_hdr_rec.set_of_books_id);
498 fnd_client_info.set_currency_context (l_asset_hdr_rec.set_of_books_id);
499
500 return true;
501
502 EXCEPTION
503
504 WHEN ret_adj_err THEN
505 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
506 p_log_level_rec => p_log_level_rec);
507 return FALSE;
508
509 WHEN OTHERS THEN
510 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
511 ,p_log_level_rec => p_log_level_rec);
512 return FALSE;
513
514
515 end do_all_books;
516
517 FUNCTION validate_over_depreciate
518 (p_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type,
519 p_asset_type_rec FA_API_TYPES.asset_type_rec_type,
520 p_asset_fin_rec FA_API_TYPES.asset_fin_rec_type,
521 p_asset_deprn_rec FA_API_TYPES.asset_deprn_rec_type,
522 p_proceeds_of_sale NUMBER,
523 p_cost_of_removal NUMBER,
524 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
525
526 l_deprn_reserve_new NUMBER := 0;
527 BEGIN
528
529 l_deprn_reserve_new := nvl(p_asset_deprn_rec.deprn_reserve, 0) +
530 nvl(p_proceeds_of_sale, 0) -
531 nvl(p_cost_of_removal, 0);
532
533 if (not fa_asset_val_pvt.validate_over_depreciate(
534 p_asset_hdr_rec => p_asset_hdr_rec,
535 p_asset_type => p_asset_type_rec.asset_type,
536 p_over_depreciate_option => p_asset_fin_rec.over_depreciate_option,
537 p_adjusted_recoverable_cost => p_asset_fin_rec.adjusted_recoverable_cost,
538 p_recoverable_cost => p_asset_fin_rec.recoverable_cost,
539 p_deprn_reserve_new => l_deprn_reserve_new,
540 p_log_level_rec => p_log_level_rec)) then
541
542 return false;
543 end if;
544
545 return TRUE;
546
547 END validate_over_depreciate;
548
549 END FA_RETIREMENT_ADJUSTMENT_PUB ;