[Home] [Help]
PACKAGE BODY: APPS.FA_TRANSFER_PUB
Source
1 PACKAGE BODY FA_TRANSFER_PUB AS
2 /* $Header: FAPTFRB.pls 120.16.12020000.3 2012/11/30 13:59:33 spooyath ship $ */
3
4 --*********************** Global constants *******************************--
5 G_PKG_NAME CONSTANT varchar2(30) := 'FA_TRANSFER_PUB';
6 G_API_NAME CONSTANT varchar2(30) := 'Transfer API';
7 G_API_VERSION CONSTANT number := 1.0;
8 g_release number := fa_cache_pkg.fazarel_release; --Bug 8477066
9
10 g_log_level_rec fa_api_types.log_level_rec_type;
11
12 --*********************** Private procedures *****************************--
13
14 FUNCTION valid_input(px_trans_rec IN OUT NOCOPY FA_API_TYPES.trans_rec_type,
15 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
16 RETURN BOOLEAN;
17
18 --*********************** Public procedures ******************************--
19
20 PROCEDURE do_transfer(p_api_version IN NUMBER,
21 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
22 p_commit IN VARCHAR2 := FND_API.G_FALSE,
23 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
24 p_calling_fn IN VARCHAR2,
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2,
28 px_trans_rec IN OUT NOCOPY FA_API_TYPES.trans_rec_type,
29 px_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type,
30 px_asset_dist_tbl IN OUT NOCOPY FA_API_TYPES.asset_dist_tbl_type)
31
32 IS
33
34 -- Bug 8252607/5475276 Cursor to get the book_type_code
35 CURSOR c_corp_book( p_asset_id number ) IS
36 SELECT bc.book_type_code
37 FROM fa_books bks,
38 fa_book_controls bc
39 WHERE bks.book_type_code = bc.distribution_source_book
40 AND bks.book_type_code = bc.book_type_code
41 AND bks.asset_id = p_asset_id
42 AND bks.transaction_header_id_out is null;
43
44 l_period_addition varchar2(1);
45 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
46
47 --10328635
48 l_asset_fin_rec FA_API_TYPES.asset_fin_rec_type;
49 --10328635
50
51 BEGIN
52
53 SAVEPOINT transfer_pub;
54
55 if (not g_log_level_rec.initialized) then
56 if (NOT fa_util_pub.get_log_level_rec (
57 x_log_level_rec => g_log_level_rec
58 )) then
59 raise FND_API.G_EXC_ERROR;
60 end if;
61 end if;
62
63 IF NOT FND_API.Compatible_API_Call(g_api_version, p_api_version,
64 g_api_name, G_PKG_NAME) then
65 RAISE FND_API.G_EXC_ERROR;
66 END IF;
67
68 if (p_init_msg_list = FND_API.G_TRUE) then
69 fa_srvr_msg.Init_Server_Message;
70 fa_debug_pkg.Initialize;
71 end if;
72
73 if (px_asset_hdr_rec.asset_id is null) then
74 fa_srvr_msg.add_message(
75 calling_fn => 'FA_TRANSFER_PUB.do_transfer',
76 name => 'FA_SHARED_ITEM_NULL',
77 token1 => 'ITEM',
78 value1 => 'Asset Id', p_log_level_rec => g_log_level_rec);
79 raise FND_API.G_EXC_ERROR;
80 end if;
81
82 -- Bug 8252607/5475276 Get the book_type_code if it is not supplied.
83 if (px_asset_hdr_rec.book_type_code is null) then
84 open c_corp_book( px_asset_hdr_rec.asset_id );
85 fetch c_corp_book into px_asset_hdr_rec.book_type_code;
86 close c_corp_book;
87
88 if px_asset_hdr_rec.book_type_code is null then
89 fa_srvr_msg.add_message
90 (calling_fn => 'FA_TRANSFER_PUB.do_transfer',
91 name => 'FA_EXP_GET_ASSET_INFO', p_log_level_rec => g_log_level_rec);
92 raise FND_API.G_EXC_ERROR;
93 end if;
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 FND_API.G_EXC_ERROR;
99 end if;
100
101 -- validate book is corporate and enabled and asset exists in book
102 if not fa_asset_val_pvt.validate_asset_book
103 (p_transaction_type_code => 'TRANSFER',
104 p_book_type_code => px_asset_hdr_rec.book_type_code,
105 p_asset_id => px_asset_hdr_rec.asset_id,
106 p_calling_fn => 'FA_TRANSFER_PUB.do_transfer', p_log_level_rec => g_log_level_rec) then
107 raise FND_API.G_EXC_ERROR;
108 end if;
109
110 --Verify if impairment has happened in same period
111 if not FA_ASSET_VAL_PVT.validate_impairment_exists
112 (p_asset_id => px_asset_hdr_rec.asset_id,
113 p_book => px_asset_hdr_rec.book_type_code,
114 p_mrc_sob_type_code => 'P',
115 p_set_of_books_id => px_asset_hdr_rec.set_of_books_id,
116 p_log_level_rec => g_log_level_rec) then
117 raise FND_API.G_EXC_ERROR;
118 end if;
119 /*phase5 This function will validate if current transaction is overlapping to any previously done impairment*/
120
121 --Add code for the 10328635
122
123 if not FA_UTIL_PVT.get_asset_fin_rec
124 (p_asset_hdr_rec => px_asset_hdr_rec
125 ,px_asset_fin_rec => l_asset_fin_rec
126 ,p_transaction_header_id => NULL
127 ,p_mrc_sob_type_code => 'P'
128 , p_log_level_rec => g_log_level_rec) then
129 raise FND_API.G_EXC_UNEXPECTED_ERROR;
130 end if;
131
132 if (not fa_cache_pkg.fazccmt(l_asset_fin_rec.deprn_method_code,
133 l_asset_fin_rec.life_in_months, p_log_level_rec => g_log_level_rec)) then
134 if (g_log_level_rec.statement_level) then
135 fa_debug_pkg.add(p_calling_fn, 'Error calling', 'fa_cache_pkg.fazccmt', p_log_level_rec => g_log_level_rec);
136 end if;
137
138 raise FND_API.G_EXC_UNEXPECTED_ERROR;
139 end if;
140
141 --End code for the 10328635
142
143 if not FA_ASSET_VAL_PVT.check_overlapping_impairment(
144 p_trans_rec => px_trans_rec,
145 p_asset_hdr_rec => px_asset_hdr_rec ,
146 p_log_level_rec => g_log_level_rec) then
147
148 fa_srvr_msg.add_message
149 (name => 'FA_OVERLAPPING_IMP_NOT_ALLOWED',
150 calling_fn => 'FA_ASSET_VAL_PVT.check_overlapping_impairment'
151 ,p_log_level_rec => g_log_level_rec);
152
153 raise FND_API.G_EXC_ERROR;
154 end if;
155 px_trans_rec.transaction_type_code := 'TRANSFER';
156 IF NOT FA_TRX_APPROVAL_PKG.faxcat(
157 X_book =>px_asset_hdr_rec.book_type_code,
158 X_asset_id =>px_asset_hdr_rec.asset_id,
159 X_trx_type =>px_trans_rec.transaction_type_code,
160 X_trx_date =>px_trans_rec.transaction_date_entered,
161 X_init_message_flag=> 'NO', p_log_level_rec => g_log_level_rec) then
162 raise FND_API.G_EXC_ERROR;
163 end if;
164
165 if not fa_asset_val_pvt.validate_period_of_addition
166 (px_asset_hdr_rec.asset_id,
167 px_asset_hdr_rec.book_type_code,
168 'ABSOLUTE',
169 l_period_addition, p_log_level_rec => g_log_level_rec) then
170 RAISE FND_API.G_EXC_ERROR;
171 end if;
172 px_asset_hdr_rec.period_of_addition := l_period_addition;
173
174 -- validate input
175 if not valid_input(px_trans_rec,
176 px_asset_hdr_rec,
177 g_log_level_rec) then
178 raise FND_API.G_EXC_ERROR;
179 end if;
180
181 -- BUG# 3325400
182 -- forcing selection of the thid here rather
183 -- then relying on table handler
184 select fa_transaction_headers_s.nextval
185 into px_trans_rec.transaction_header_id
186 from dual;
187
188 -- Bug 15877517 : Reinitialize who_info as well
189 px_trans_rec.who_info.creation_date := sysdate;
190 px_trans_rec.who_info.last_update_date := sysdate;
191
192 if not FA_DISTRIBUTION_PVT.do_distribution(
193 px_trans_rec => px_trans_rec,
194 px_asset_hdr_rec => px_asset_hdr_rec,
195 px_asset_cat_rec_new => l_asset_cat_rec,
196 px_asset_dist_tbl => px_asset_dist_tbl,
197 p_validation_level => p_validation_level, p_log_level_rec => g_log_level_rec) then
198 raise FND_API.G_EXC_ERROR;
199 end if;
200
201 /*
202 * Code hook for IAC
203 */
204 if (FA_IGI_EXT_PKG.IAC_Enabled) then
205 if not FA_IGI_EXT_PKG.Do_Transfer(
206 p_trans_rec => px_trans_rec,
207 p_asset_hdr_rec => px_asset_hdr_rec,
208 p_asset_cat_rec => l_asset_cat_rec,
209 p_calling_function =>'FA_TRANSFER_PUB.Do_Transfer') then
210 raise FND_API.G_EXC_ERROR;
211 end if;
212 end if; -- (FA_IGI_EXT_PKG.IAC_Enabled)
213
214 -- call to workflow business event
215
216 fa_business_events.raise(
217 p_event_name => 'oracle.apps.fa.transfer.asset.transfer',
218 p_event_key => px_asset_hdr_rec.asset_id || to_char(sysdate,'RRDDDSSSSS'),
219 p_parameter_name1 => 'ASSET_ID',
220 p_parameter_value1 => px_asset_hdr_rec.asset_id,
221 p_parameter_name2 => 'BOOK_TYPE_CODE',
222 p_parameter_value2 => px_asset_hdr_rec.book_type_code,
223 p_log_level_rec => g_log_level_rec);
224
225 if fa_cse_callouts_pvt.is_oat_enabled then
226 if not fa_cse_callouts_pvt.transfer(
227 p_trans_rec => px_trans_rec,
228 p_asset_hdr_rec => px_asset_hdr_rec,
229 p_asset_dist_tbl => px_asset_dist_tbl) then
230 raise FND_API.G_EXC_ERROR;
231 end if;
232 end if;
233
234
235
236 if (p_commit = FND_API.G_TRUE) then
237 COMMIT WORK;
238 end if;
239 /*
240 -- Return the status.
241 FA_SRVR_MSG.Add_Message(
242 calling_fn => 'FA_TRANSFER_PUB.do_transfer',
243 name => 'FA_SHARED_END_SUCCESS',
244 token1 => 'PROGRAM',
245 value1 => 'FA_TRANSFER_PUB.do_transfer', p_log_level_rec => g_log_level_rec); */
246
247 FND_MSG_PUB.Count_And_Get(
248 p_count => x_msg_count,
249 p_data => x_msg_data
250 );
251
252 x_return_status := FND_API.G_RET_STS_SUCCESS;
253
254
255 EXCEPTION
256 WHEN FND_API.G_EXC_ERROR THEN
257 FA_SRVR_MSG.Add_Message(
258 calling_fn => 'FA_TRANSFER_PUB.do_transfer', 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 ROLLBACK TO transfer_pub;
265 x_return_status := FND_API.G_RET_STS_ERROR;
266
267 WHEN OTHERS THEN
268 FA_SRVR_MSG.add_sql_error(
269 calling_fn => 'FA_TRANSFER_PUB.do_transfer', p_log_level_rec => g_log_level_rec);
270
271 FND_MSG_PUB.Count_And_Get(
272 p_count => x_msg_count,
273 p_data => x_msg_data
274 );
275 ROLLBACK TO transfer_pub;
276 x_return_status := FND_API.G_RET_STS_ERROR;
277
278 END;
279
280 FUNCTION valid_input(px_trans_rec IN OUT NOCOPY fa_api_types.trans_rec_type,
281 p_asset_hdr_rec IN fa_api_types.asset_hdr_rec_type, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
282 RETURN BOOLEAN IS
283
284 l_fiscal_year_name varchar2(30);
285 l_fiscal_year number;
286 l_fy_start_date date;
287 l_fy_end_date date;
288 l_count number;
289 l_max_transaction_date date;
290 l_period_rec FA_API_TYPES.period_rec_type;
291 l_override_flag varchar2(1);
292 l_transaction_date date;
293
294 BEGIN
295
296 -- check if asset is attached to hierarchy and see if it can
297 -- be override to proceed with normal transfer
298 if (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') then
299 if (not fa_cua_asset_APIS.check_override_allowed(
300 p_attribute_name => 'DISTRIBUTION',
301 p_book_type_code => p_asset_hdr_rec.book_type_code,
302 p_asset_id => p_asset_hdr_rec.asset_id,
303 x_override_flag => l_override_flag,
304 p_log_level_rec => p_log_level_rec)) then
305 fa_srvr_msg.add_message(
306 calling_fn => 'FA_TRANSFER_PUB.valid_input', p_log_level_rec => p_log_level_rec);
307 return FALSE;
308 end if;
309 -- if override flag is set to No, do not allow the transfer
310 if (l_override_flag = 'N') then
311 fa_srvr_msg.add_message(
312 calling_fn => 'FA_TRANSFER_PUB.valid_input',
313 name => 'CUA_NO_DIST_CHANGE_ALLOWED', p_log_level_rec => p_log_level_rec);
314 return FALSE;
315 end if;
316 end if;
317
318 -- check if asset is fully retired
319 if FA_ASSET_VAL_PVT.validate_fully_retired(p_asset_hdr_rec.asset_id,
320 p_asset_hdr_rec.book_type_code, p_log_level_rec => p_log_level_rec) then
321 fa_srvr_msg.add_message(
322 calling_fn => 'FA_TRANSFER_PUB.valid_input',
323 Name => 'FA_REC_RETIRED', p_log_level_rec => p_log_level_rec);
324 return FALSE;
325 end if;
326
327 if not FA_UTIL_PVT.get_period_rec
328 (p_book => p_asset_hdr_rec.book_type_code,
329 x_period_rec => l_period_rec, p_log_level_rec => p_log_level_rec) then
330 return FALSE;
331 end if;
332
333 /*Bug 8601485 - Verify if transfer date of the asset is before DPIS*/
334 if not fa_asset_val_pvt.validate_asset_transfer_date
335 (p_asset_hdr_rec => p_asset_hdr_rec,
336 p_trans_rec => px_trans_rec,
337 p_calling_fn => 'FA_TRANSFER_PUB.do_transfer',
338 p_log_level_rec => NULL) then
339 fa_srvr_msg.add_message( calling_fn => 'FA_TRANSFER_PUB.valid_input',
340 name => 'FA_MASSTFR_VALID_TFR_DPIS', p_log_level_rec => p_log_level_rec);
341 return FALSE;
342 end if;
343
344 if (px_trans_rec.transaction_date_entered is null or
345 p_asset_hdr_rec.period_of_addition = 'Y') then
346 l_transaction_date :=
347 greatest(l_period_rec.calendar_period_open_date,
348 least(sysdate,l_period_rec.calendar_period_close_date));
349 px_trans_rec.transaction_date_entered :=
350 to_date(to_char(l_transaction_date,'DD/MM/YYYY'),'DD/MM/YYYY');
351 else
352 if not fa_cache_pkg.fazcbc (X_book => p_asset_hdr_rec.book_type_code, p_log_level_rec => p_log_level_rec) then
353 fa_srvr_msg.add_message( calling_fn => 'FA_TRANSFER_PUB.valid_input', p_log_level_rec => p_log_level_rec);
354 return FALSE;
355 else
356 l_fiscal_year_name := fa_cache_pkg.fazcbc_record.fiscal_year_name;
357 l_fiscal_year := fa_cache_pkg.fazcbc_record.current_fiscal_year;
358 end if;
359
360 select start_date, end_date
361 into l_fy_start_Date, l_fy_end_date
362 from fa_fiscal_year
363 where fiscal_year = l_fiscal_year
364 and fiscal_year_name = l_fiscal_year_name;
365
366 if not FA_UTIL_PVT.get_latest_trans_date('FA_TRANSFER_PUB.valid_input',
367 p_asset_hdr_rec.asset_id,
368 p_asset_hdr_rec.book_type_code,
369 l_max_transaction_date, p_log_level_rec => p_log_level_rec) then
370 return FALSE;
371 end if;
372
373 if (px_trans_rec.transaction_date_entered <l_fy_start_date or
374 px_trans_rec.transaction_date_entered > l_fy_end_date) then
375 fa_srvr_msg.add_message(
376 calling_fn => 'FA_TRANSFER_PUB.valid_input',
377 name => 'FA_RET_DATE_MUSTBE_IN_CUR_FY', p_log_level_rec => p_log_level_rec);
378 return FALSE;
379 end if;
380
381 if (px_trans_rec.transaction_date_entered > l_period_rec.calendar_period_close_date) then
382 fa_srvr_msg.add_message(
383 calling_fn => 'FA_TRANSFER_PUB.valid_input',
384 name => 'FA_SHARED_CANNOT_FUTURE', p_log_level_rec => p_log_level_rec);
385 return FALSE;
386 end if;
387
388 if (px_trans_rec.transaction_date_entered < l_max_transaction_date) then
389 fa_srvr_msg.add_message(
390 calling_fn => 'FA_TRANSFER_PUB.valid_input',
391 name => 'FA_SHARED_OTHER_TRX_FOLLOW', p_log_level_rec => p_log_level_rec);
392 return FALSE;
393 end if;
394
395 if (px_trans_rec.transaction_date_entered <to_date('1000/01/01', 'YYYY/MM/DD')) then
396 fa_srvr_msg.add_message(
397 calling_fn => 'FA_TRANSFER_PUB.valid_input',
398 name => 'FA_YEAR_GREATER_THAN', p_log_level_rec => p_log_level_rec);
399 return FALSE;
400 end if;
401
402 -- check that only one prior period transfer is allowed in the same period
403
404 SELECT count(1)
405 INTO l_count
406 FROM FA_TRANSACTION_HEADERS th,
407 FA_DEPRN_PERIODS dp
408 WHERE th.asset_id = nvl(p_asset_hdr_rec.asset_id, -1)
409 AND th.book_type_code = nvl(p_asset_hdr_rec.book_type_code,'XX')
410 AND th.transaction_type_code||'' = 'TRANSFER'
411 AND th.transaction_date_entered < dp.calendar_period_open_date
412 AND th.date_effective > dp.period_open_date
413 AND px_trans_rec.transaction_date_entered <
414 dp.calendar_period_open_date
415 AND dp.book_type_code = nvl(p_asset_hdr_rec.book_type_code, 'XX')
416 AND dp.period_close_date IS NULL;
417
418 IF (l_count > 0) THEN
419 fa_srvr_msg.add_message(
420 calling_fn => 'FA_TRANSFER_PUB.valid_input',
421 name => 'FA_SHARED_ONE_PRIOR_PERIOD_TRX', p_log_level_rec => p_log_level_rec);
422 return FALSE;
423 end if;
424
425 -- prior period tfr is not allowed after assets' normal life complete
426
427 SELECT count(1)
428 INTO l_count
429 FROM FA_BOOKS bk, FA_DEPRN_PERIODS dp
430 WHERE bk.asset_id = p_asset_hdr_rec.asset_id
431 AND bk.book_type_code = p_asset_hdr_rec.book_type_code
432 AND nvl(period_counter_fully_reserved, 99) <>
433 nvl(period_counter_life_complete, 99)
434 AND bk.date_ineffective IS NULL
435 AND dp.book_type_code = bk.book_type_code
436 AND px_trans_rec.transaction_date_entered <
437 dp.calendar_period_open_date
438 AND dp.period_close_date IS NULL;
439
440 if (l_count > 0) THEN
441 fa_srvr_msg.add_message(
442 calling_fn => 'FA_TRANSFER_PUB.valid_input',
443 name => 'FA_NO_TRX_WHEN_LIFE_COMPLETE', p_log_level_rec => p_log_level_rec);
444 return FALSE;
445 end if;
446
447 /*Bug 8477066. If there is any pending backdated transfer already in any tax book,
448 we should not allow this backdated transfer in Corp Book. */
449 if (g_release = 11) then --Brahma
450 SELECT count(1)
451 INTO l_count
452 FROM FA_TRANSACTION_HEADERS th,
453 FA_DEPRN_PERIODS dp,
454 FA_BOOK_CONTROLS bc
455 WHERE th.asset_id = nvl(p_asset_hdr_rec.asset_id, -1)
456 AND th.book_type_code = nvl(p_asset_hdr_rec.book_type_code,'XX')
457 AND th.transaction_type_code||'' = 'TRANSFER'
458 AND th.transaction_date_entered < dp.calendar_period_open_date
459 AND th.date_effective > dp.period_open_date
460 AND px_trans_rec.transaction_date_entered < dp.calendar_period_open_date
461 AND dp.book_type_code = bc.book_type_code
462 AND dp.period_close_date IS NULL
463 AND bc.DISTRIBUTION_SOURCE_BOOK = nvl(p_asset_hdr_rec.book_type_code,'XX') ;
464
465 if (l_count > 0) THEN
466 fa_srvr_msg.add_message(
467 calling_fn => 'FA_TRANSFER_PUB.valid_input',
468 name => 'FA_TAX_PRIOR_PER_TFR', p_log_level_rec => p_log_level_rec);
469 return FALSE;
470 end if;
471 end if;
472 end if;
473
474 return TRUE;
475
476 EXCEPTION
477 when others then
478 fa_srvr_msg.add_sql_error(
479 calling_fn => 'FA_TRANSFER_PUB.valid_input', p_log_level_rec => p_log_level_rec);
480 return FALSE;
481
482 END;
483
484
485
486 END FA_TRANSFER_PUB;