[Home] [Help]
PACKAGE BODY: APPS.FA_DEPRN_ROLLBACK_PUB
Source
1 PACKAGE BODY FA_DEPRN_ROLLBACK_PUB AS
2 /* $Header: FAPDRBB.pls 120.9.12010000.1 2008/07/28 13:12:56 appldev ship $ */
3
4 --*********************** Global constants *******************************--
5 G_PKG_NAME CONSTANT varchar2(30) := 'FA_DEPRN_ROLLBACK_PUB';
6 G_API_NAME CONSTANT varchar2(30) := 'Depreciation Rollback API';
7 G_API_VERSION CONSTANT number := 1.0;
8
9 g_log_level_rec fa_api_types.log_level_rec_type; -- Bug:5475024
10
11 --*********************** Private functions ******************************--
12
13 FUNCTION do_all_books
14 (px_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type,
15 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN; -- Bug:5475024
16
17 --*********************** Public procedures ******************************--
18 procedure do_rollback (
19 -- Standard Parameters --
20 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 x_return_status OUT NOCOPY VARCHAR2,
25 x_msg_count OUT NOCOPY NUMBER,
26 x_msg_data OUT NOCOPY VARCHAR2,
27 p_calling_fn IN VARCHAR2,
28 -- Asset Object --
29 px_asset_hdr_rec IN OUT NOCOPY fa_api_types.asset_hdr_rec_type
30 ) as
31
32 l_asset_type_rec fa_api_types.asset_type_rec_type;
33 l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
34 l_group_asset_id number;
35 l_tracking_method varchar2(30);
36
37 -- used to store original sob info upon entry into api
38 l_orig_set_of_books_id number;
39 l_orig_currency_context varchar2(64);
40
41 l_calling_fn varchar2(40) := 'fa_deprn_rollback_pub.do_rollback';
42
43 rb_err exception; -- sets return status
44
45
46 cursor c_members (p_book_type_code varchar2,
47 p_group_asset_id number) is
48 select asset_id
49 from fa_books
50 where group_asset_id = p_group_asset_id
51 and book_type_code = p_book_type_code
52 and transaction_header_id_out is null;
53
54 begin
55
56 SAVEPOINT do_rollback;
57
58 -- Bug:5475024
59 if (not g_log_level_rec.initialized) then
60 if (NOT fa_util_pub.get_log_level_rec (
61 x_log_level_rec => g_log_level_rec
62 )) then
63 raise rb_err;
64 end if;
65 end if;
66
67 -- Initialize message list if p_init_msg_list is set to TRUE.
68 if (fnd_api.to_boolean(p_init_msg_list)) then
69 -- initialize error message stack.
70 fa_srvr_msg.init_server_message;
71
72 -- initialize debug message stack.
73 fa_debug_pkg.initialize;
74 end if;
75
76 fnd_profile.get ('GL_SET_OF_BKS_ID',l_orig_set_of_books_id);
77 l_orig_currency_context := SUBSTRB(USERENV('CLIENT_INFO'),45,10);
78
79 -- Check version of the API
80 -- Standard call to check for API call compatibility.
81 if (NOT fnd_api.compatible_api_call (
82 G_API_VERSION,
83 p_api_version,
84 G_API_NAME,
85 G_PKG_NAME
86 )) then
87 raise rb_err;
88 end if;
89
90 -- Call the cache for the primary transaction book
91 if (NOT fa_cache_pkg.fazcbc (X_book => px_asset_hdr_rec.book_type_code,
92 p_log_level_rec => g_log_level_rec)) then -- Bug:5475024
93 raise rb_err;
94 end if;
95
96 px_asset_hdr_rec.set_of_books_id :=
97 fa_cache_pkg.fazcbc_record.set_of_books_id;
98
99 if (p_validation_level = FND_API.G_VALID_LEVEL_FULL) then
100
101 null;
102
103 end if;
104
105 -- Bug:5475024
106 -- verify the asset exist in the book already
107 if not FA_ASSET_VAL_PVT.validate_asset_book
108 (p_transaction_type_code => 'DEPRECIATION',
109 p_book_type_code => px_asset_hdr_rec.book_type_code,
110 p_asset_id => px_asset_hdr_rec.asset_id,
111 p_calling_fn => l_calling_fn,
112 p_log_level_rec => g_log_level_rec) then
113 raise rb_err;
114 end if;
115
116 if not FA_UTIL_PVT.get_asset_type_rec
117 (p_asset_hdr_rec => px_asset_hdr_rec,
118 px_asset_type_rec => l_asset_type_rec,
119 p_date_effective => null,
120 p_log_level_rec => g_log_level_rec -- Bug:5475024
121 ) then
122 raise rb_err;
123 end if;
124
125 -- get group info for processing
126 select group_asset_id,
127 tracking_method
128 into l_group_asset_id,
129 l_tracking_method
130 from fa_books
131 where asset_id = px_asset_hdr_rec.asset_id
132 and book_type_code = px_asset_hdr_rec.book_type_code
133 and transaction_header_id_out is null;
134
135 -- call the routine to loop through primary and reporting books
136 if not do_all_books
137 (px_asset_hdr_rec => px_asset_hdr_rec,
138 p_log_level_rec => g_log_level_rec) then -- Bug:5475024
139 raise rb_err;
140 end if;
141
142 l_asset_hdr_rec := px_asset_hdr_rec;
143
144 if (l_asset_type_rec.asset_type = 'GROUP' and
145 l_tracking_method is not null) then
146
147 for c_rec in c_members (p_book_type_code => px_asset_hdr_rec.book_type_code,
148 p_group_asset_id => px_asset_hdr_rec.asset_id) loop
149
150 l_asset_hdr_rec.asset_id := c_rec.asset_id;
151
152 if not do_all_books
153 (px_asset_hdr_rec => l_asset_hdr_rec,
154 p_log_level_rec => g_log_level_rec) then -- Bug:5475024
155 raise rb_err;
156 end if;
157
158 end loop;
159
160
161 elsif (l_group_asset_id is not null) then
162
163 l_asset_hdr_rec.asset_id := l_group_asset_id;
164
165 if not do_all_books
166 (px_asset_hdr_rec => l_asset_hdr_rec,
167 p_log_level_rec => g_log_level_rec) then -- Bug:5475024
168 raise rb_err;
169 end if;
170
171 if (l_tracking_method = 'ALLOCATE') then
172 --Bug6680499 changed px_asset_hdr_rec.asset_id to l_asset_hdr_rec.asset_id
173 for c_rec in c_members (p_book_type_code => px_asset_hdr_rec.book_type_code,
174 p_group_asset_id => l_asset_hdr_rec.asset_id) loop
175
176 l_asset_hdr_rec.asset_id := c_rec.asset_id;
177
178 if not do_all_books
179 (px_asset_hdr_rec => l_asset_hdr_rec,
180 p_log_level_rec => g_log_level_rec) then -- Bug:5475024
181 raise rb_err;
182 end if;
183
184 end loop;
185
186 end if;
187 end if;
188
189 -- commit if p_commit is TRUE.
190 if (fnd_api.to_boolean (p_commit)) then
191 COMMIT WORK;
192 end if;
193
194 -- Reset the gl_sob profile
195 fnd_profile.put('GL_SET_OF_BKS_ID', l_orig_set_of_books_id);
196 fnd_client_info.set_currency_context (l_orig_currency_context);
197
198
199 -- Standard call to get message count and if count is 1 get message info.
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_SUCCESS;
206
207 exception
208 when rb_err then
209
210 ROLLBACK TO do_rollback;
211
212 -- set back to original environment when the procedure is finished
213 fnd_profile.put('GL_SET_OF_BKS_ID', l_orig_set_of_books_id);
214 fnd_client_info.set_currency_context (l_orig_currency_context);
215
216 fa_srvr_msg.add_message
217 (calling_fn => l_calling_fn,
218 p_log_level_rec => g_log_level_rec); -- Bug:5475024
219
220 FND_MSG_PUB.count_and_get (
221 p_count => x_msg_count,
222 p_data => x_msg_data
223 );
224
225 x_return_status := FND_API.G_RET_STS_ERROR;
226
227 when others then
228
229 ROLLBACK TO do_rollback;
230
231 -- set back to original environment when the procedure is finished
232 fnd_profile.put('GL_SET_OF_BKS_ID', l_orig_set_of_books_id);
233 fnd_client_info.set_currency_context (l_orig_currency_context);
234
235 fa_srvr_msg.add_sql_error
236 (calling_fn => l_calling_fn,
237 p_log_level_rec => g_log_level_rec); -- Bug:5475024
238
239 FND_MSG_PUB.count_and_get (
240 p_count => x_msg_count,
241 p_data => x_msg_data
242 );
243
244 x_return_status := FND_API.G_RET_STS_ERROR;
245
246 end do_rollback;
247
248 -----------------------------------------------------------------------------
249
250 -- Books (MRC) Wrapper - called from public API above
251 --
252 -- For non mrc books, this just calls the private API with provided params
253 -- For MRC, it processes the primary and then loops through each reporting
254 -- book calling the private api for each.
255
256
257 FUNCTION do_all_books
258 (px_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type,
259 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS -- Bug:5475024
260
261 l_mrc_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
262 l_period_rec fa_api_types.period_rec_type;
263 l_rsob_tbl fa_cache_pkg.fazcrsob_sob_tbl_type;
264 l_mrc_sob_type_code varchar2(1);
265
266 l_deprn_source_info XLA_EVENTS_PUB_PKG.t_event_source_info;
267 l_security_context XLA_EVENTS_PUB_PKG.t_security;
268
269
270 l_event_id number;
271 l_rev_event_id number;
272
273 l_deprn_run_id number;
274 l_event_status varchar2(1);
275 l_deprn_count number;
276 l_sysdate date;
277
278 l_calling_fn varchar2(60) := 'fa_deprn_rollback_pub.do_all_books';
279 rb_err exception;
280
281 BEGIN
282
283
284 -- Set the gl_sob profile to this book
285 fnd_profile.put('GL_SET_OF_BKS_ID', px_asset_hdr_rec.set_of_books_id);
286 fnd_client_info.set_currency_context (px_asset_hdr_rec.set_of_books_id);
287 l_sysdate := sysdate;
288
289 if (NOT FA_UTIL_PVT.get_period_rec (
290 p_book => px_asset_hdr_rec.book_type_code,
291 p_effective_date => NULL,
292 x_period_rec => l_period_rec,
293 p_log_level_rec => p_log_level_rec -- Bug:5475024
294 )) then
295 raise rb_err;
296 end if;
297
298 -- see if any rows actually exist in any currencies
299
300 BEGIN
301
302 select event_id, deprn_run_id
303 into l_event_id,
304 l_deprn_run_id
305 from fa_deprn_events_v de
306 where de.book_type_code = px_asset_hdr_rec.book_type_code
307 and de.asset_id = px_asset_hdr_rec.asset_id
308 and de.period_counter = l_period_rec.period_counter
309 and de.reversal_event_id is null;
310
311 if (g_log_level_rec.statement_level) then -- Bug:5475024
312 fa_debug_pkg.add(l_calling_fn, 'l_event_id', l_event_id,
313 p_log_level_rec => p_log_level_rec);
314 fa_debug_pkg.add(l_calling_fn, 'l_deprn_run_id', l_deprn_run_id,
315 p_log_level_rec => p_log_level_rec);
316 end if;
317
318 EXCEPTION
319
320 WHEN NO_DATA_FOUND THEN
321 if (g_log_level_rec.statement_level) then -- Bug:5475024
322 fa_debug_pkg.add(l_calling_fn, 'no event found', 'for this asset',
323 p_log_level_rec => p_log_level_rec);
324 end if;
325 END;
326
327
328
329 -- call transaction approval for primary books only
330 -- note that we don't need all the logic in faxcat since no transaction
331 -- would be allowed on a transaction in the open period. We need to insure
332 -- two things:
333 -- 1) no pending mass transaction on the book. (which could be mass deprn rollback)
334 -- 2) no deprn run ongoing since closing the book and rolling back deprn
335 -- on one or more of the already processed assets would not be good combination
336
337 if (NOT fa_trx_approval_pkg.faxcat (
338 X_book => px_asset_hdr_rec.book_type_code,
339 X_asset_id => px_asset_hdr_rec.asset_id,
340 X_trx_type => 'RB_DEP',
341 X_trx_date => sysdate,
342 X_init_message_flag => 'NO',
343 p_log_level_rec => p_log_level_rec -- Bug:5475024
344 )) then
345 raise rb_err;
346 end if;
347
348 if (l_event_id is not null) then
349 l_deprn_source_info.application_id := 140;
350 l_deprn_source_info.ledger_id := px_asset_hdr_rec.set_of_books_id;
351 l_deprn_source_info.source_id_int_1 := px_asset_hdr_rec.asset_id ;
352 l_deprn_source_info.source_id_char_1 := px_asset_hdr_rec.book_type_code;
353 l_deprn_source_info.source_id_int_2 := l_period_rec.period_counter;
354 l_deprn_source_info.source_id_int_3 := l_deprn_run_id;
355 l_deprn_source_info.entity_type_code := 'DEPRECIATION';
356
357 if (g_log_level_rec.statement_level) then -- Bug:5475024
358 fa_debug_pkg.add(l_calling_fn, 'calling get event status for event ', l_event_id,
359 p_log_level_rec => p_log_level_rec);
360 end if;
361
362 -- check the event status
363 l_event_status := XLA_EVENTS_PUB_PKG.get_event_status
364 (p_event_source_info => l_deprn_source_info,
365 p_event_id => l_event_id,
366 p_valuation_method => px_asset_hdr_rec.book_type_code,
367 p_security_context => l_security_context);
368
369 if (g_log_level_rec.statement_level) then -- Bug:5475024
370 fa_debug_pkg.add(l_calling_fn, 'event status ', l_event_status,
371 p_log_level_rec => p_log_level_rec);
372 end if;
373
374 if (l_event_status = XLA_EVENTS_PUB_PKG.C_EVENT_PROCESSED) then
375
376 -- create the reversal event
377 l_rev_event_id := xla_events_pub_pkg.create_event
378 (p_event_source_info => l_deprn_source_info,
379 p_event_type_code => 'ROLLBACK_DEPRECIATION',
380 p_event_date => l_period_rec.calendar_period_close_date,
381 p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
382 p_event_number => NULL,
383 p_reference_info => NULL,
384 p_valuation_method => px_asset_hdr_rec.book_type_code,
385 p_security_context => l_security_context);
386
387 if (g_log_level_rec.statement_level) then -- Bug:5475024
388 fa_debug_pkg.add(l_calling_fn, 'rollback event id', l_event_id,
389 p_log_level_rec => p_log_level_rec);
390 end if;
391
392 elsif (l_event_status = XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED) then
393
394 if (g_log_level_rec.statement_level) then -- Bug:5475024
395 fa_debug_pkg.add(l_calling_fn, 'deleting event', l_event_id,
396 p_log_level_rec => p_log_level_rec);
397 end if;
398
399 XLA_EVENTS_PUB_PKG.delete_event
400 (p_event_source_info => l_deprn_source_info,
401 p_event_id => l_event_id,
402 p_valuation_method => px_asset_hdr_rec.book_type_code,
403 p_security_context => l_security_context);
404
405 else
406 raise rb_err;
407 end if;
408
409 end if; -- event is not null
410
411 if (g_log_level_rec.statement_level) then -- Bug:5475024
412 fa_debug_pkg.add(l_calling_fn, 'entering ', 'main logic',
413 p_log_level_rec => p_log_level_rec);
414 end if;
415
416
417 if (NOT fa_cache_pkg.fazcrsob (
418 x_book_type_code => px_asset_hdr_rec.book_type_code,
419 x_sob_tbl => l_rsob_tbl,
420 p_log_level_rec => p_log_level_rec -- Bug:5475024
421 )) then
422 raise rb_err;
423 end if;
424
425 for mrc_index in 0..l_rsob_tbl.COUNT loop
426
427 l_mrc_asset_hdr_rec := px_asset_hdr_rec;
428
429 -- if the counter mrc_index is at 0, then process incoming
430 -- book else iterate through reporting books
431 if (mrc_index = 0) then
432 l_mrc_asset_hdr_rec.set_of_books_id :=
433 px_asset_hdr_rec.set_of_books_id;
434 l_mrc_sob_type_code := 'P';
435 else
436 l_mrc_asset_hdr_rec.set_of_books_id :=
437 l_rsob_tbl(mrc_index);
438 l_mrc_sob_type_code := 'R';
439 end if;
440
441 -- Set the gl_sob profile to this reporting book
442 fnd_profile.put('GL_SET_OF_BKS_ID',
443 l_mrc_asset_hdr_rec.set_of_books_id);
444 fnd_client_info.set_currency_context (
445 l_mrc_asset_hdr_rec.set_of_books_id);
446
447 -- Need to always call fazcbcs
448 if (NOT fa_cache_pkg.fazcbcs (
449 X_book => l_mrc_asset_hdr_rec.book_type_code,
450 p_log_level_rec => p_log_level_rec -- Bug:5475024
451 )) then
452 raise rb_err;
453 end if;
454
455 -- check to make sure there are deprn rows for the
456 -- currency in question
457
458 if (mrc_index = 0) then
459 select count(*)
460 into l_deprn_count
461 from fa_deprn_summary ds
462 where ds.book_type_code = px_asset_hdr_rec.book_type_code
463 and ds.asset_id = px_asset_hdr_rec.asset_id
464 and ds.period_counter = l_period_rec.period_counter;
465 else
466 select count(*)
467 into l_deprn_count
468 from fa_deprn_summary_mrc_v ds
469 where ds.book_type_code = px_asset_hdr_rec.book_type_code
470 and ds.asset_id = px_asset_hdr_rec.asset_id
471 and ds.period_counter = l_period_rec.period_counter;
472 end if;
473
474 -- now rollback deprn
475 if not fa_deprn_rollback_pvt.do_rollback
476 (p_asset_hdr_rec => px_asset_hdr_rec,
477 p_period_rec => l_period_rec,
478 p_deprn_run_id => l_deprn_run_id,
479 p_reversal_event_id => l_rev_event_id,
480 p_reversal_date => l_sysdate,
481 p_deprn_exists_count => l_deprn_count,
482 p_mrc_sob_type_code => l_mrc_sob_type_code,
483 p_calling_fn => l_calling_fn,
484 p_log_level_rec => p_log_level_rec) then -- Bug:5475024
485 raise rb_err;
486 end if;
487 end loop;
488
489
490 -- Bug 6391045
491 -- Code hook for IAC
492
493 if (FA_IGI_EXT_PKG.IAC_Enabled) then
494 if not FA_IGI_EXT_PKG.Do_Rollback_Deprn(
495 p_asset_hdr_rec => px_asset_hdr_rec,
496 p_period_rec => l_period_rec,
497 p_deprn_run_id => l_deprn_run_id,
498 p_reversal_event_id => l_rev_event_id,
499 p_reversal_date => l_sysdate,
500 p_deprn_exists_count => l_deprn_count,
501 p_calling_function => l_calling_fn) then
502 raise rb_err;
503 end if;
504 end if; -- (FA_IGI_EXT_PKG.IAC_Enabled)
505
506
507
508 return true;
509
510 EXCEPTION
511
512 WHEN rb_ERR THEN
513 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
514 p_log_level_rec => p_log_level_rec); -- Bug:5475024
515 return FALSE;
516
517 WHEN OTHERS THEN
518 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn,
519 p_log_level_rec => p_log_level_rec); -- Bug:5475024
520 return FALSE;
521
522 END do_all_books;
523
524 END FA_DEPRN_ROLLBACK_PUB;