[Home] [Help]
PACKAGE BODY: APPS.FA_PROCESS_IMPAIRMENT_PKG
Source
1 PACKAGE BODY FA_PROCESS_IMPAIRMENT_PKG AS
2 /* $Header: FAPIMPB.pls 120.13.12020000.3 2012/09/12 11:21:31 gigupta ship $ */
3
4 g_log_level_rec fa_api_types.log_level_rec_type;
5 g_release number := fa_cache_pkg.fazarel_release; /*Bug# 8394781- */
6
7 --*********************** Private functions ******************************--
8 FUNCTION assign_workers (p_request_id IN NUMBER,
9 p_book_type_code IN VARCHAR2,
10 p_total_requests IN NUMBER,
11 p_period_rec IN FA_API_TYPES.period_rec_type,
12 p_prev_sysdate IN DATE,
13 p_login_id IN NUMBER,
14 p_transaction_date IN DATE,
15 p_set_of_books_id In NUMBER,
16 p_mrc_sob_type_code IN VARCHAR2,
17 p_calling_fn IN VARCHAR2) RETURN BOOLEAN;
18
19 FUNCTION check_je_post (p_book_type_code IN VARCHAR2,
20 p_period_rec IN FA_API_TYPES.period_rec_type,
21 p_mrc_sob_type_code IN VARCHAR2,
22 p_set_of_books_id In NUMBER,
23 p_calling_fn IN VARCHAR2)
24 RETURN BOOLEAN;
25
26 FUNCTION rollback_impairment (p_book_type_code IN VARCHAR2,
27 p_request_id IN NUMBER,
28 p_mode IN VARCHAR2,
29 p_impairment_id IN NUMBER,
30 p_calling_fn IN VARCHAR2,
31 p_mrc_sob_type_code IN VARCHAR2,
32 p_set_of_books_id In NUMBER) /* Bug 6437003 added p_mrc_sob_type_code to check for Set of Books type */
33 RETURN BOOLEAN;
34
35
36 PROCEDURE process_impairments(
37 errbuf OUT NOCOPY VARCHAR2,
38 retcode OUT NOCOPY NUMBER,
39 p_book_type_code IN VARCHAR2,
40 p_mode IN VARCHAR2,
41 p_impairment_id IN NUMBER DEFAULT NULL,
42 p_parent_request_id IN NUMBER DEFAULT NULL,
43 p_total_requests IN NUMBER DEFAULT NULL,
44 p_request_number IN NUMBER DEFAULT NULL,
45 p_set_of_books_id IN NUMBER DEFAULT NULL,
46 p_mrc_sob_type_code IN VARCHAR2 DEFAULT NULL) IS
47 l_calling_fn varchar2(60) := 'fa_process_impairment_pkg.process_impairment';
48 l_calling_fn2 varchar2(60) := 'process_impairment';
49
50 l_iso_lang varchar2(255);
51 l_iso_territory varchar2(255);
52
53 --Removed c_get_nls_lang and changed c_get_iso_values for 13718241
54
55 CURSOR c_get_iso_values is
56 SELECT LOWER (iso_language), UPPER(iso_territory)
57 FROM fnd_languages
58 WHERE language_code = NVL (USERENV ('LANG'),'US');
59
60 l_transaction_date date;
61
62 l_set_of_books_id NUMBER;
63 --
64 -- Get period information for impairment date
65 --
66 CURSOR c_get_period_rec IS
67 select fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM period_counter
68 , cp.start_date calendar_period_open_date
69 , cp.end_date calendar_period_close_date
70 , cp.period_num period_num
71 , fy.fiscal_year fiscal_year
72 from fa_book_controls bc
73 , fa_fiscal_year fy
74 , fa_calendar_types ct
75 , fa_calendar_periods cp
76 where bc.book_type_code = p_book_type_code
77 and bc.deprn_calendar = ct.calendar_type
78 and bc.fiscal_year_name = fy.fiscal_year_name
79 and ct.fiscal_year_name = bc.fiscal_year_name
80 and ct.calendar_type = cp.calendar_type
81 and cp.start_date between fy.start_date and fy.end_date
82 and bc.last_period_counter + 1 >= fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM
83 and l_transaction_date between cp.start_date and cp.end_date;
84
85 CURSOR c_get_mc_period_rec IS
86 select fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM period_counter
87 , cp.start_date calendar_period_open_date
88 , cp.end_date calendar_period_close_date
89 , cp.period_num period_num
90 , fy.fiscal_year fiscal_year
91 from fa_book_controls bc
92 , fa_mc_book_controls mbc
93 , fa_fiscal_year fy
94 , fa_calendar_types ct
95 , fa_calendar_periods cp
96 where bc.book_type_code = p_book_type_code
97 and mbc.book_type_code = p_book_type_code
98 and mbc.set_of_books_id = l_set_of_books_id
99 and bc.deprn_calendar = ct.calendar_type
100 and bc.fiscal_year_name = fy.fiscal_year_name
101 and ct.fiscal_year_name = bc.fiscal_year_name
102 and ct.calendar_type = cp.calendar_type
103 and cp.start_date between fy.start_date and fy.end_date
104 and bc.last_period_counter + 1 >= fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM
105 and l_transaction_date between cp.start_date and cp.end_date;
106
107
108 TYPE tab_num15_type IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
109 TYPE tab_date_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
110
111 l_rbs_name VARCHAR2(30);
112 l_sql_stmt varchar2(101);
113
114 l_msg_count NUMBER := 0;
115 l_msg_data VARCHAR2(2000) := NULL;
116
117 l_sob_tbl FA_CACHE_PKG.fazcrsob_sob_tbl_type;
118 l_total_requests BINARY_INTEGER;
119 t_request_id tab_num15_type;
120 l_request_id number(15);
121 l_internal_mode VARCHAR2(30);
122 l_mrc_sob_type_code VARCHAR2(1);
123
124 l_period_rec FA_API_TYPES.period_rec_type;
125 l_imp_period_rec FA_API_TYPES.period_rec_type;
126 t_imp_date tab_date_type;
127
128 l_sysdate date := sysdate;
129 l_login_id number(15) := fnd_global.user_id;
130
131 l_phase VARCHAR2(80);
132 l_status VARCHAR2(80);
133 l_dev_phase VARCHAR2(15);
134 l_dev_status VARCHAR2(30);
135 l_message VARCHAR2(240);
136
137
138 l_temp_char VARCHAR2(30);
139
140 l_return_code BOOLEAN;
141 l_primary_sob NUMBER(15);
142 l_request_id2 NUMBER(15);
143
144 l_new_count NUMBER;
145
146 imp_err exception;
147
148 l_temp number;
149 l_asset_id FA_IMPAIRMENT_PREV_PVT.tab_num_type; -- Bug# 7000391
150 l_nbv_value FA_IMPAIRMENT_PREV_PVT.tab_num_type; -- Bug# 7000391
151 l_ret_code number; -- Bug# 7000391
152
153 /*Bug# 8394781- */
154 --Bug# 14595406 - Modified query
155 CURSOR c_get_asset_id(c_request_id number,c_period_counter number) IS
156 select asset_id
157 from fa_impairments imp
158 where imp.book_type_code = p_book_type_code
159 and imp.request_id = c_request_id
160 and imp.asset_id is not null
161 and not exists
162 (select 'POSTED'
163 from fa_impairments imp2
164 where status = 'POSTED'
165 and ((imp2.asset_id = imp.asset_id) or
166 imp2.cash_generating_unit_id = (select cash_generating_unit_id
167 from fa_books fbs
168 where fbs.asset_id = imp.asset_id
169 and fbs.book_type_code = imp.book_type_code
170 and fbs.transaction_header_id_out is null))
171 and imp2.book_type_code = p_book_type_code
172 AND PERIOD_COUNTER_IMPAIRED = c_period_counter )
173 UNION
174 select bk.asset_id
175 from fa_impairments imp,
176 fa_books bk
177 where bk.cash_generating_unit_id = imp.cash_generating_unit_id
178 and bk.book_type_code = imp.book_type_code
179 and imp.book_type_code = p_book_type_code
180 and imp.request_id = c_request_id
181 and imp .asset_id is null
182 and not exists
183 (select 'POSTED'
184 from fa_impairments imp2
185 where status = 'POSTED'
186 and ((imp2.cash_generating_unit_id = imp.cash_generating_unit_id) or
187 imp2.asset_id in (select asset_id
188 from fa_books fbs
189 where fbs.cash_generating_unit_id = imp.cash_generating_unit_id
190 and fbs.book_type_code = imp.book_type_code
191 and fbs.transaction_header_id_out is null))
192 and imp2.book_type_code = p_book_type_code
193 and PERIOD_COUNTER_IMPAIRED = c_period_counter );
194
195 -- variables for deprn rollback
196 l_return_status VARCHAR2(1);
197 l_deprn_run boolean := false;
198 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
199 /*Bug# 8394781 - */
200 x_return_status number := 0; --8666930
201
202 BEGIN
203
204 -- set rollback segment if profile option is set
205 fnd_profile.get('FA_LARGE_ROLLBACK_SEGMENT', l_rbs_name);
206
207 if (l_rbs_name is not null) THEN
208 l_sql_stmt := 'SET TRANSACTION USE ROLLBACK SEGMENT ' || l_rbs_name;
209 execute immediate l_sql_stmt;
210 end if;
211
212
213 if (not g_log_level_rec.initialized) then
214 if (NOT fa_util_pub.get_log_level_rec (
215 x_log_level_rec => g_log_level_rec
216 )) then
217 raise imp_err;
218 end if;
219 end if;
220
221 fa_srvr_msg.Init_Server_Message; -- Initialize server message stack
222 fa_debug_pkg.Initialize; -- Initialize debug message stack
223
224 if (g_log_level_rec.statement_level) then
225 fa_debug_pkg.add(l_calling_fn, 'process_impairment', 'BEGIN', p_log_level_rec => g_log_level_rec);
226 fa_debug_pkg.add(l_calling_fn, 'p_book_type_code', p_book_type_code, p_log_level_rec => g_log_level_rec);
227 fa_debug_pkg.add(l_calling_fn, 'p_mode', p_mode, p_log_level_rec => g_log_level_rec);
228 fa_debug_pkg.add(l_calling_fn, 'p_impairment_id', p_impairment_id, p_log_level_rec => g_log_level_rec);
229 fa_debug_pkg.add(l_calling_fn, 'p_parent_request_id', p_parent_request_id, p_log_level_rec => g_log_level_rec);
230 fa_debug_pkg.add(l_calling_fn, 'p_total_requests', p_total_requests, p_log_level_rec => g_log_level_rec);
231 fa_debug_pkg.add(l_calling_fn, 'p_request_number', p_request_number, p_log_level_rec => g_log_level_rec);
232 fa_debug_pkg.add(l_calling_fn, 'p_set_of_books_id', p_set_of_books_id, p_log_level_rec => g_log_level_rec);
233 fa_debug_pkg.add(l_calling_fn, 'p_mrc_sob_type_code', p_mrc_sob_type_code, p_log_level_rec => g_log_level_rec);
234 end if;
235
236 if (g_log_level_rec.statement_level) then
237 fa_debug_pkg.add('set_rollback_seg','Rollback Segment is', l_rbs_name, p_log_level_rec => g_log_level_rec);
238 end if;
239
240 l_request_id := fnd_global.conc_request_id;
241 -- Bug 7651572: Initialize l_mrc_sob_type_code in case we call imp_err
242 l_mrc_sob_type_code := p_mrc_sob_type_code; -- Bug 7651572
243
244 if (p_parent_request_id is null) or (l_request_id = -1)
245 or (p_mode = 'PREVIEW')
246 then
247 --
248 -- PARENT or STANDALONE
249 --
250
251 if p_mode = 'PREVIEW' then
252 l_internal_mode := 'RUNNING DEPRN';
253 elsif p_mode = 'POST' or
254 p_impairment_id is not null then
255 l_internal_mode := 'RUNNING POST';
256 elsif p_mode = 'ROLLBACK' then
257 l_internal_mode := 'DELETING POST';
258 else
259 if (g_log_level_rec.statement_level) then
260 fa_debug_pkg.add(l_calling_fn,'Wrong mode', p_mode, p_log_level_rec => g_log_level_rec);
261 fa_debug_pkg.add(l_calling_fn,'impairment_id', p_impairment_id, p_log_level_rec => g_log_level_rec);
262 end if;
263
264 end if;
265
266 fnd_profile.get('FA_NUM_PARALLEL_REQUESTS', l_temp_char);
267 l_total_requests := nvl(l_temp_char, 1);
268
269 if (g_log_level_rec.statement_level) then
270 fa_debug_pkg.add(l_calling_fn,'Total Requests', l_total_requests, p_log_level_rec => g_log_level_rec);
271 end if;
272
273 if not FA_CACHE_PKG.fazcbc(X_book => p_book_type_code, p_log_level_rec => g_log_level_rec) then
274 raise imp_err;
275 end if;
276
277 l_primary_sob := fa_cache_pkg.fazcbc_record.set_of_books_id;
278 fa_debug_pkg.add(l_calling_fn,'Total l_primary_sob', l_primary_sob, p_log_level_rec => g_log_level_rec);
279
280 -- call the sob cache to get the table of sob_ids
281 if not FA_CACHE_PKG.fazcrsob(
282 x_book_type_code => p_book_type_code,
283 x_sob_tbl => l_sob_tbl, p_log_level_rec => g_log_level_rec) then
284 raise imp_err;
285 end if;
286
287 if not FA_UTIL_PVT.get_period_rec(
288 p_book => p_book_type_code,
289 p_effective_date => NULL,
290 x_period_rec => l_period_rec, p_log_level_rec => g_log_level_rec) then
291 raise imp_err;
292 end if;
293
294 if (g_log_level_rec.statement_level) then
295 fa_debug_pkg.add(l_calling_fn,'l_period_rec.period_counter', l_period_rec.period_counter, p_log_level_rec => g_log_level_rec);
296 end if;
297
298 if p_impairment_id is null then
299 UPDATE FA_IMPAIRMENTS imp
300 SET imp.STATUS = l_internal_mode
301 , imp.REQUEST_ID = l_request_id
302 , imp.PERIOD_COUNTER_IMPAIRED =
303 (select nvl(imp.PERIOD_COUNTER_IMPAIRED,
304 fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM)
305 from fa_fiscal_year fy
306 , fa_calendar_types ct
307 , fa_calendar_periods cp
308 where fa_cache_pkg.fazcbc_record.deprn_calendar = ct.calendar_type
309 and fa_cache_pkg.fazcbc_record.fiscal_year_name = fy.fiscal_year_name
310 and ct.fiscal_year_name = fa_cache_pkg.fazcbc_record.fiscal_year_name
311 and ct.calendar_type = cp.calendar_type
312 and cp.start_date between fy.start_date and fy.end_date
313 and fa_cache_pkg.fazcbc_record.last_period_counter + 1 >=
314 fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM
315 and imp.impairment_date between cp.start_date and cp.end_date)
316 WHERE imp.STATUS = p_mode
317 -- Bug#7264536 - To handle situation when impairment is posted simultaneously for different books
318 AND imp.BOOK_TYPE_CODE = p_book_type_code
319 RETURNING imp.IMPAIRMENT_DATE BULK COLLECT INTO t_imp_date;
320 else
321 UPDATE FA_IMPAIRMENTS imp
322 SET imp.STATUS = l_internal_mode
323 , imp.REQUEST_ID = l_request_id
324 , imp.PERIOD_COUNTER_IMPAIRED =
325 (select nvl(imp.PERIOD_COUNTER_IMPAIRED,
326 fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM)
327 from fa_fiscal_year fy
328 , fa_calendar_types ct
329 , fa_calendar_periods cp
330 where fa_cache_pkg.fazcbc_record.deprn_calendar = ct.calendar_type
331 and fa_cache_pkg.fazcbc_record.fiscal_year_name = fy.fiscal_year_name
332 and ct.fiscal_year_name = fa_cache_pkg.fazcbc_record.fiscal_year_name
333 and ct.calendar_type = cp.calendar_type
334 and cp.start_date between fy.start_date and fy.end_date
335 and fa_cache_pkg.fazcbc_record.last_period_counter + 1 >=
336 fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM
337 and imp.impairment_date between cp.start_date and cp.end_date)
338 WHERE imp.impairment_id = p_impairment_id
339 RETURNING imp.IMPAIRMENT_DATE BULK COLLECT INTO t_imp_date;
340 end if;
341
342 if sql%rowcount = 0 and
343 p_mode = 'PREVIEW' then
344
345 SELECT count(IMPAIRMENT_ID)
346 INTO l_new_count
347 FROM FA_IMPAIRMENTS
348 WHERE STATUS = 'NEW'
349 AND REQUEST_ID is null;
350
351 --
352 -- Check to see this user is just creating NEW impairment or not
353 if l_new_count > 0 then
354
355 if (g_log_level_rec.statement_level) then
356 fa_debug_pkg.add(l_calling_fn, 'This must be New impairment', 'No process required', p_log_level_rec => g_log_level_rec);
357 end if;
358
359 -- Dump Debug messages when run in debug mode to log file
360 if (g_log_level_rec.statement_level) then
361 fa_debug_pkg.Write_Debug_Log;
362 end if;
363
364 fa_srvr_msg.add_message(
365 calling_fn => l_calling_fn,
366 name => 'FA_SHARED_END_SUCCESS',
367 token1 => 'PROGRAM',
368 value1 => 'FAPIMP', p_log_level_rec => g_log_level_rec);
369
370 fa_srvr_msg.Write_Msg_Log(1, null, p_log_level_rec => g_log_level_rec);
371
372 -- Program needs to finish successfully even though there was nothing to do
373 -- return success to concurrent manager
374 retcode := 0;
375
376 return;
377 else
378 if (g_log_level_rec.statement_level) then
379 fa_debug_pkg.add(l_calling_fn, 'No impairment to post', 'Check Impairment', p_log_level_rec => g_log_level_rec);
380 end if;
381
382 raise imp_err;
383 end if;
384
385 elsif sql%rowcount = 0 then
386 if (g_log_level_rec.statement_level) then
387 fa_debug_pkg.add(l_calling_fn, 'No impairment to post', 'Check Impairment', p_log_level_rec => g_log_level_rec);
388 end if;
389
390 raise imp_err;
391 end if;
392
393
394 COMMIT;
395
396 if (g_log_level_rec.statement_level) then
397 fa_debug_pkg.add(l_calling_fn,'Number of sob', l_sob_tbl.count, p_log_level_rec => g_log_level_rec);
398 end if;
399 /*Bug#8590767 */
400 if FA_IGI_EXT_PKG.IAC_Enabled then
401 if (g_log_level_rec.statement_level) then
402 fa_debug_pkg.add(l_calling_fn,'IAC is enabled..','Checking book is IAC enabled or not.', p_log_level_rec => g_log_level_rec);
403 end if;
404 /*Bug# 8887223 - to check if book is IAC enabled. */
405 if IGI_IAC_COMMON_UTILS.is_iac_book(p_book_type_code) then
406 fa_srvr_msg.add_message(
407 calling_fn => l_calling_fn,
408 name => 'FA_IMPAIR_IAC_ENABLED',
409 p_log_level_rec => g_log_level_rec);
410 if (g_log_level_rec.statement_level) then
411 fa_debug_pkg.add(l_calling_fn,'Failed...IAC Enabled','TRUE', p_log_level_rec => g_log_level_rec);
412 end if;
413 raise imp_err;
414 end if;
415 end if;
416
417 FOR l_sob_index in 0..l_sob_tbl.count LOOP -- sob loop
418
419 l_transaction_date := nvl(t_imp_date(1), greatest(l_period_rec.calendar_period_open_date,
420 least(trunc(sysdate),
421 l_period_rec.calendar_period_close_date))) ;
422
423 if (g_log_level_rec.statement_level) then
424 fa_debug_pkg.add(l_calling_fn,'Current Period', l_period_rec.period_name, p_log_level_rec => g_log_level_rec);
425 end if;
426
427 if (l_sob_index = 0) then
428 l_mrc_sob_type_code := 'P';
429 l_set_of_books_id := l_primary_sob ;
430 if l_transaction_date < l_period_rec.calendar_period_open_date then
431 OPEN c_get_period_rec;
432 FETCH c_get_period_rec INTO l_imp_period_rec.period_counter
433 , l_imp_period_rec.calendar_period_open_date
434 , l_imp_period_rec.calendar_period_close_date
435 , l_imp_period_rec.period_num
436 , l_imp_period_rec.fiscal_year;
437 CLOSE c_get_period_rec;
438 else
439 l_imp_period_rec := l_period_rec;
440 end if;
441 else
442 l_mrc_sob_type_code := 'R';
443 l_set_of_books_id := l_sob_tbl(l_sob_index);
444
445 end if;
446
447 if (g_log_level_rec.statement_level) then
448 fa_debug_pkg.add(l_calling_fn,'l_set_of_books_id', l_set_of_books_id, p_log_level_rec => g_log_level_rec);
449 end if;
450
451 if p_mode = 'PREVIEW' then
452
453 --Bug#7594562 - To check whether deprn has run or not for current open period
454 -- - if deprn is already run throw error.
455 /*Bug# 8394781- */
456 if g_release = 11 then
457 if not FA_CHK_BOOKSTS_PKG.faxcdr(X_book => p_book_type_code, p_log_level_rec => g_log_level_rec) then
458 raise imp_err;
459 end if;
460 else
461 l_asset_hdr_rec.book_type_code := p_book_type_code;
462 For l_get_asset_id in c_get_asset_id(l_request_id,l_period_rec.period_counter)
463 loop
464 l_asset_hdr_rec.asset_id := l_get_asset_id.asset_id;
465
466 FA_DEPRN_ROLLBACK_PUB.do_rollback
467
468 (p_api_version => 1.0,
469 p_init_msg_list => FND_API.G_FALSE,
470 p_commit => FND_API.G_FALSE,
471 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
472 x_return_status => l_return_status,
473 x_msg_count => l_msg_count,
474 x_msg_data => l_msg_data,
475 p_calling_fn => l_calling_fn,
476 px_asset_hdr_rec => l_asset_hdr_rec);
477
478 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
479 raise imp_err;
480 else
481 if (g_log_level_rec.statement_level) then
482 fa_debug_pkg.add(l_calling_fn,
483 'Successfully rolled back deprn asset:',l_get_asset_id.asset_id,g_log_level_rec);
484 end if;
485 end if;
486 end loop;
487 end if;
488 /*Bug# 8394781- end */
489
490 --
491 -- Populate FA_ITF_IMPAIRMENTS with worker_ids etc
492 --
493 if not assign_workers (p_request_id => l_request_id,
494 p_book_type_code => p_book_type_code,
495 p_total_requests => l_total_requests,
496 p_period_rec => l_imp_period_rec,
497 p_prev_sysdate => l_sysdate,
498 p_login_id => l_login_id,
499 p_transaction_date => l_transaction_date,
500 p_set_of_books_id => l_set_of_books_id,
501 p_mrc_sob_type_code => l_mrc_sob_type_code,
502 p_calling_fn => l_calling_fn) then
503 raise imp_err;
504 end if;
505 elsif p_mode = 'ROLLBACK' then
506 --
507 -- check to see if je has been rolled back
508 --
509 if G_release = 11 then
510 if not check_je_post (p_book_type_code => p_book_type_code,
511 p_period_rec => l_period_rec,
512 p_mrc_sob_type_code => l_mrc_sob_type_code,
513 p_set_of_books_id => l_set_of_books_id,
514 p_calling_fn => l_calling_fn) then
515 raise imp_err;
516 end if;
517 end if;
518 end if;
519
520
521 -- For now, only sigle processing is only option
522 -- if l_request_id <> -1 and l_total_requests > 1 then
523 if l_request_id <> -1 and l_total_requests < 1 then
524 if (g_log_level_rec.statement_level) then
525 fa_debug_pkg.add(l_calling_fn,'Pararell process', 'Start', p_log_level_rec => g_log_level_rec);
526 end if;
527 -- *****************
528 -- ************* *****************
529 -- Parallel Requests
530 -- ************* *****************
531 -- *****************
532
533 for i in 1..l_total_requests loop
534
535 t_request_id(i) := FND_REQUEST.SUBMIT_REQUEST(
536 application => 'OFA'
537 , program => 'FAPIMP'
538 , argument1 => p_book_type_code
539 , argument2 => l_internal_mode
540 , argument3 => l_request_id
541 , argument4 => l_total_requests
542 , argument5 => i
543 , argument6 => l_set_of_books_id
544 , argument7 => l_mrc_sob_type_code);
545 end loop;
546
547 for i in 1..l_total_requests loop
548 if not FND_CONCURRENT.WAIT_FOR_REQUEST(
549 request_id => t_request_id(i)
550 , interval => 60
551 , phase => l_phase
552 , status => l_status
553 , dev_phase => l_dev_phase
554 , dev_status => l_dev_status
555 , message => l_message) then
556 raise imp_err;
557 end if;
558 end loop;
559
560 if (g_log_level_rec.statement_level) then
561 fa_debug_pkg.add(l_calling_fn,'Pararell process', 'End', p_log_level_rec => g_log_level_rec);
562 end if;
563
564 end if;
565
566 if (g_log_level_rec.statement_level) then
567 fa_debug_pkg.add(l_calling_fn,'Setting MRC related info', 'Start', p_log_level_rec => g_log_level_rec);
568 end if;
569
570 if (l_sob_index <> 0) then
571
572 if l_transaction_date < l_period_rec.calendar_period_open_date then
573 OPEN c_get_mc_period_rec;
574 FETCH c_get_mc_period_rec INTO l_imp_period_rec.period_counter
575 , l_imp_period_rec.calendar_period_open_date
576 , l_imp_period_rec.calendar_period_close_date
577 , l_imp_period_rec.period_num
578 , l_imp_period_rec.fiscal_year;
579 CLOSE c_get_mc_period_rec;
580 end if;
581
582 end if;
583
584 -- call the cache to set the sob_id used for rounding and other lower
585 -- level code for each book.
586 if NOT fa_cache_pkg.fazcbcs(X_book => p_book_type_code,
587 X_set_of_books_id => l_set_of_books_id,
588 p_log_level_rec => g_log_level_rec) then
589 raise imp_err;
590 end if;
591
592
593 -- For now, only sigle processing is only option
594 -- if (l_request_id = -1) or (l_total_requests = 1) then
595 if (l_request_id = -1) or ( l_total_requests >= 1 ) then
596 if (g_log_level_rec.statement_level) then
597 fa_debug_pkg.add(l_calling_fn,'Standalone Mode', p_mode, p_log_level_rec => g_log_level_rec);
598 end if;
599 -- ****************
600 -- ************* *****************
601 -- Comand Line Mode
602 -- ************* *****************
603 -- ****************
604 if p_mode = 'PREVIEW' then
605 -- *************
606 -- ************* *****************
607 -- Preview Phase
608 -- ************* *****************
609 -- *************
610 if not FA_IMPAIRMENT_PREV_PVT.process_depreciation(
611 p_request_id => l_request_id,
612 p_book_type_code => p_book_type_code,
613 p_worker_id => 0,
614 p_period_rec => l_period_rec,
615 p_imp_period_rec => l_imp_period_rec,
616 p_mrc_sob_type_code => l_mrc_sob_type_code,
617 p_set_of_books_id => l_set_of_books_id,
618 p_calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec) then
619 if (g_log_level_rec.statement_level) then
620 fa_debug_pkg.add(l_calling_fn,'Failed calling', 'process_depreciation', p_log_level_rec => g_log_level_rec);
621 end if;
622
623 raise imp_err;
624 end if;
625 elsif p_mode = 'POST' then
626 -- ************
627 -- ************* *****************
628 -- Post Phase
629 -- ************* *****************
630 -- ************
631 --8666930
632 if l_mrc_sob_type_code = 'R' then
633 UPDATE FA_MC_IMPAIRMENTS imp
634 SET imp.STATUS = l_internal_mode
635 , imp.REQUEST_ID = l_request_id
636 , imp.PERIOD_COUNTER_IMPAIRED = l_period_rec.PERIOD_COUNTER
637 WHERE imp.SET_OF_BOOKS_ID = l_set_of_books_id
638 AND imp.BOOK_TYPE_CODE = p_book_type_code
639 AND exists (SELECT IMPAIRMENT_ID
640 FROM FA_IMPAIRMENTS imp2
641 WHERE imp2.status = l_internal_mode
642 AND imp2.BOOK_TYPE_CODE = p_book_type_code
643 AND imp2.impairment_id = IMP.IMPAIRMENT_ID);
644 end if;
645 if not FA_IMPAIRMENT_POST_PVT.process_post(
646 p_request_id => l_request_id,
647 p_book_type_code => p_book_type_code,
648 p_period_rec => l_period_rec,
649 p_worker_id => 0,
650 p_mrc_sob_type_code => l_mrc_sob_type_code,
651 p_set_of_books_id => l_set_of_books_id,
652 p_calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec) then
653 if (g_log_level_rec.statement_level) then
654 fa_debug_pkg.add(l_calling_fn,'Failed calling', 'process_post', p_log_level_rec => g_log_level_rec);
655 end if;
656
657 raise imp_err;
658 end if;
659
660 elsif p_mode = 'ROLLBACK' then
661 --8666930
662 if l_mrc_sob_type_code = 'R' then
663 UPDATE FA_MC_IMPAIRMENTS imp
664 SET imp.STATUS = l_internal_mode
665 , imp.REQUEST_ID = l_request_id
666 , imp.PERIOD_COUNTER_IMPAIRED = l_period_rec.PERIOD_COUNTER
667 WHERE imp.SET_OF_BOOKS_ID = l_set_of_books_id
668 AND imp.BOOK_TYPE_CODE = p_book_type_code
669 AND exists (SELECT IMPAIRMENT_ID
670 FROM FA_IMPAIRMENTS imp2
671 WHERE imp2.status = l_internal_mode
672 AND imp2.BOOK_TYPE_CODE = p_book_type_code
673 AND imp2.impairment_id = IMP.IMPAIRMENT_ID);
674 end if;
675 if not FA_IMPAIRMENT_DELETE_PVT.delete_post(
676 p_request_id => l_request_id,
677 p_book_type_code => p_book_type_code,
678 p_period_rec => l_period_rec,
679 p_worker_id => 0,
680 p_mrc_sob_type_code => l_mrc_sob_type_code,
681 p_set_of_books_id => l_set_of_books_id,
682 p_calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec) then
683 raise imp_err;
684 end if;
685 end if;
686
687 end if; --
688
689 if p_mode = 'PREVIEW' then
690 -- ****************************************************
691 -- Calculate total NBV and Allocation Phase
692 -- ****************************************************
693
694 if not FA_IMPAIRMENT_PREV_PVT.calc_total_nbv(
695 p_request_id => l_request_id
696 , p_book_type_code => p_book_type_code
697 , p_transaction_date => l_transaction_date
698 -- , p_period_rec => l_period_rec
699 , p_period_rec => l_imp_period_rec
700 , p_mrc_sob_type_code => l_mrc_sob_type_code
701 , p_set_of_books_id => l_set_of_books_id
702 , p_calling_fn => l_calling_fn
703 , p_asset_id => l_asset_id
704 , p_nbv => l_nbv_value, p_log_level_rec => g_log_level_rec) then
705 if (g_log_level_rec.statement_level) then
706 fa_debug_pkg.add(l_calling_fn,'Failed calling', 'calc_total_nbv', p_log_level_rec => g_log_level_rec);
707 end if;
708
709 raise imp_err;
710 end if;
711
712 --
713 -- Calculating catch-up is necessary only for back dated impairment
714 --
715 if (l_imp_period_rec.period_counter < l_period_rec.period_counter) then
716 -- *************************************************
717 -- Calculate catch-up expense due to bd impairment
718 -- *************************************************
719
720 if not FA_IMPAIRMENT_PREV_PVT.calculate_catchup(
721 p_request_id => l_request_id
722 , p_book_type_code => p_book_type_code
723 , p_worker_id => 0
724 , p_period_rec => l_period_rec
725 , p_imp_period_rec => l_imp_period_rec
726 , p_mrc_sob_type_code => l_mrc_sob_type_code
727 -- BMR , p_set_of_books_id => l_set_of_books_id
728 , p_calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec) then
729 if (g_log_level_rec.statement_level) then
730 fa_debug_pkg.add(l_calling_fn,'Failed calling', 'calculate_catchup', p_log_level_rec => g_log_level_rec);
731 end if;
732 raise imp_err;
733 end if;
734
735 end if;
736
737 else
738 null;
739 end if;
740
741 END LOOP; -- sob loop
742
743 if (g_log_level_rec.statement_level) then
744 fa_debug_pkg.add(l_calling_fn,'After sob loop', sysdate, p_log_level_rec => g_log_level_rec);
745 end if;
746
747
748 elsif (p_parent_request_id is not null) then
749
750 if not FA_CACHE_PKG.fazcbc(X_book => p_book_type_code, p_log_level_rec => g_log_level_rec) then
751 raise imp_err;
752 end if;
753
754 -- call the cache to set the sob_id used for rounding and other lower
755 -- level code for each book.
756 if not FA_CACHE_PKG.fazcbcs(X_book => p_book_type_code,
757 X_set_of_books_id => l_set_of_books_id,
758 p_log_level_rec => g_log_level_rec) then
759 raise imp_err;
760 end if;
761
762 if not FA_UTIL_PVT.get_period_rec(
763 p_book => p_book_type_code,
764 p_effective_date => NULL,
765 x_period_rec => l_period_rec, p_log_level_rec => g_log_level_rec) then
766 raise imp_err;
767 end if;
768
769 if p_mode = 'RUNNING DEPRN' then
770 -- *************
771 -- ************* *****************
772 -- Preview Phase
773 -- ************* *****************
774 -- *************
775 if not FA_IMPAIRMENT_PREV_PVT.process_depreciation(
776 p_request_id => p_parent_request_id,
777 p_book_type_code => p_book_type_code,
778 p_worker_id => p_request_number,
779 p_period_rec => l_period_rec,
780 p_imp_period_rec => l_imp_period_rec,
781 p_mrc_sob_type_code => p_mrc_sob_type_code,
782 p_set_of_books_id => l_set_of_books_id,
783 p_calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec) then
784 raise imp_err;
785 end if;
786 elsif p_mode = 'RUNNING POST' then
787 -- ************
788 -- ************* *****************
789 -- Post Phase
790 -- ************* *****************
791 -- ************
792 if not FA_IMPAIRMENT_POST_PVT.process_post(
793 p_request_id => p_parent_request_id,
794 p_book_type_code => p_book_type_code,
795 p_period_rec => l_period_rec,
796 p_worker_id => p_request_number,
797 p_mrc_sob_type_code => p_mrc_sob_type_code,
798 p_set_of_books_id => l_set_of_books_id,
799 p_calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec) then
800 raise imp_err;
801 end if;
802 elsif p_mode = 'DELETING POST' then
803 if not FA_IMPAIRMENT_DELETE_PVT.delete_post(
804 p_request_id => p_parent_request_id,
805 p_book_type_code => p_book_type_code,
806 p_period_rec => l_period_rec,
807 p_worker_id => p_request_number,
808 p_mrc_sob_type_code => l_mrc_sob_type_code,
809 p_set_of_books_id => l_set_of_books_id,
810 p_calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec) then
811 raise imp_err;
812 end if;
813 end if;
814
815 end if; -- (p_parent_request_id is not null) or (l_request_id = -1)
816
817
818 -- ###########################################################
819 -- ###########################################################
820 -- ###########################################################
821 -- This may need to relocated somewhere to show some of them
822 -- are succeeded and some of them are failed.
823 -- ###########################################################
824 -- ###########################################################
825 -- ###########################################################
826 if p_mode = 'PREVIEW' then
827 l_internal_mode := 'PREVIEWED';
828 elsif p_mode = 'POST' then
829 /*8666930 - Need to process deprn event here after processing is done for both primary and reporting books
830 this is to create same depreciation event for both primary and reporting book */
831 if g_release <> 11 then
832 FA_DEPRN_EVENTS_PKG.process_deprn_events(p_book_type_code,
833 l_period_rec.period_counter,
834 1, /*Change the parameter if parallel programing is enabled */
835 1,
836 0,
837 x_return_status);
838 if (g_log_level_rec.statement_level) then
839 fa_debug_pkg.add(l_calling_fn,'x_return_status after process_deprn_events ', x_return_status, p_log_level_rec => g_log_level_rec);
840 end if;
841 if x_return_status <> 0 then
842 if (g_log_level_rec.statement_level) then
843 fa_debug_pkg.add(l_calling_fn,'Process deprn event has failed ', 'true', p_log_level_rec => g_log_level_rec);
844 end if;
845 raise imp_err;
846 end if;
847 end if;
848 l_internal_mode := 'POSTED';
849 elsif p_mode = 'ROLLBACK' then
850 l_internal_mode := 'DELETED';
851 end if;
852
853 if (g_log_level_rec.statement_level) then
854 fa_debug_pkg.add(l_calling_fn,'Updating status ', p_mode||' to '||l_internal_mode, p_log_level_rec => g_log_level_rec);
855 end if;
856
857
858
859 UPDATE FA_IMPAIRMENTS
860 SET STATUS = l_internal_mode
861 WHERE REQUEST_ID = l_request_id
862 AND PERIOD_COUNTER_IMPAIRED = l_imp_period_rec.period_counter;
863
864 --Bug# 7045739 start to update status to Deprn Failed if multiple rows are uploaded.
865 UPDATE FA_IMPAIRMENTS IMP
866 SET STATUS='RUNNING DEPRN FAILED'
867 WHERE REQUEST_ID = l_request_id
868 AND EXISTS
869 (SELECT 'DUPLICATE RECORD'
870 FROM FA_ITF_IMPAIRMENTS ITF
871 WHERE ITF.PERIOD_OF_ADDITION_FLAG = 'F'
872 AND ITF.IMPAIRMENT_ID = IMP.IMPAIRMENT_ID);
873 --Bug# 7045739 end
874 /*8666930 start - There could be more than one set_of_books_id attached.Need to update for all */
875 FOR l_sob_index in 0..l_sob_tbl.count LOOP
876 fa_debug_pkg.add(l_calling_fn,'GIRIRAJ', 'inside for', p_log_level_rec => g_log_level_rec);
877 fa_debug_pkg.add(l_calling_fn,'GIRIRAJ l_sob_index', l_sob_index, p_log_level_rec => g_log_level_rec);
878 if (l_sob_index = 0) then
879 l_mrc_sob_type_code := 'P';
880 l_set_of_books_id := l_primary_sob ;
881 if l_transaction_date < l_period_rec.calendar_period_open_date then
882 OPEN c_get_period_rec;
883 FETCH c_get_period_rec INTO l_imp_period_rec.period_counter
884 , l_imp_period_rec.calendar_period_open_date
885 , l_imp_period_rec.calendar_period_close_date
886 , l_imp_period_rec.period_num
887 , l_imp_period_rec.fiscal_year;
888 CLOSE c_get_period_rec;
889 else
890 l_imp_period_rec := l_period_rec;
891 end if;
892 else
893 l_mrc_sob_type_code := 'R';
894 l_set_of_books_id := l_sob_tbl(l_sob_index);
895 end if;
896 /* bug #6658765 - added if condition*/
897 if l_mrc_sob_type_code = 'R' then
898 UPDATE FA_MC_IMPAIRMENTS
899 SET STATUS = l_internal_mode
900 WHERE REQUEST_ID = l_request_id
901 AND PERIOD_COUNTER_IMPAIRED = l_imp_period_rec.period_counter
902 AND SET_OF_BOOKS_ID = l_set_of_books_id;
903
904 --Bug# 7045739 start to update status to Deprn Failed if multiple rows are uploaded.
905 UPDATE FA_MC_IMPAIRMENTS IMP
906 SET STATUS='RUNNING DEPRN FAILED'
907 WHERE REQUEST_ID = l_request_id
908 AND SET_OF_BOOKS_ID = l_set_of_books_id
909 AND IMPAIRMENT_ID IN
910 (SELECT IMPAIRMENT_ID
911 FROM FA_MC_ITF_IMPAIRMENTS
912 WHERE PERIOD_OF_ADDITION_FLAG = 'F'
913 AND REQUEST_ID = l_request_id
914 AND SET_OF_BOOKS_ID = l_set_of_books_id);
915 --Bug# 7045739 end
916 end if;
917 END LOOP;
918 --8666930 end
919 COMMIT;
920 -- ###########################################################
921 -- ###########################################################
922 -- ###########################################################
923
924
925 if (l_internal_mode in ('PREVIEWED', 'POSTED')) and
926 (l_request_id <> -1) then
927
928 -- Get iso language and territory
929 OPEN c_get_iso_values;
930 FETCH c_get_iso_values INTO l_iso_lang, l_iso_territory;
931 CLOSE c_get_iso_values;
932
933 if (g_log_level_rec.statement_level) then
934 fa_debug_pkg.add(l_calling_fn,'iso lang', l_iso_lang, p_log_level_rec => g_log_level_rec);
935 fa_debug_pkg.add(l_calling_fn,'iso Territory', l_iso_territory, p_log_level_rec => g_log_level_rec);
936 end if;
937 if nvl(fa_cache_pkg.fazcbc_record.sorp_enabled_flag,'N') <> 'Y' then
938
939 l_return_code := FND_REQUEST.add_layout (
940 template_appl_name => 'OFA'
941 , template_code => 'FAXRASIM'
942 , template_language => l_iso_lang
943 , template_territory => l_iso_territory
944 , output_format => 'PDF');
945 else
946 l_return_code := FND_REQUEST.add_layout (
947 template_appl_name => 'OFA'
948 , template_code => 'FAXSRPIM'
949 , template_language => l_iso_lang
950 , template_territory => l_iso_territory
951 , output_format => 'PDF');
952 end if;
953 if (g_log_level_rec.statement_level) then
954 fa_debug_pkg.add(l_calling_fn,'Submitting Report', l_internal_mode, p_log_level_rec => g_log_level_rec);
955 end if;
956
957 --
958 -- Submitting Asset Impairment Report
959 --
960 l_request_id2 := FND_REQUEST.SUBMIT_REQUEST(
961 application => 'OFA'
962 , program => 'FAXRASIM'
963 , argument1 => p_book_type_code
964 , argument2 => l_primary_sob -- sob id
965 , argument3 => l_period_rec.period_counter -- per ctr
966 , argument4 => null -- imp id
967 , argument5 => null -- cgu id
968 , argument6 => l_request_id -- req id
969 , argument7 => l_internal_mode); -- status
970
971 end if; -- (l_internal_mode in ('PREVIEWED', 'POSTED'))
972
973 if (g_log_level_rec.statement_level) then
974 fa_debug_pkg.add(l_calling_fn,'l_request_id', l_request_id, p_log_level_rec => g_log_level_rec);
975 end if;
976
977
978 -- Dump Debug messages when run in debug mode to log file
979 if (g_log_level_rec.statement_level) then
980 fa_debug_pkg.Write_Debug_Log;
981 end if;
982
983 --Bug# 7000391 start
984 l_ret_code:=0;
985 for i in 1..l_asset_id.count LOOP
986 if l_nbv_value(i) = 0 then
987 fa_srvr_msg.add_message(
988 calling_fn => null,
989 name => 'FA_IMPAIR_UPLOAD_WARN',
990 token1 => 'FA_ASSET_ID',
991 value1 => '' || l_asset_id(i),
992 p_log_level_rec => g_log_level_rec);
993
994 l_ret_code:=1;
995 -- Bug# 7045739 start - when multiple rows are uploaded for an asset in same request.
996 elsif l_nbv_value(i) = -1 then
997 fa_srvr_msg.add_message(
998 calling_fn => null,
999 name => 'FA_IMPAIR_MULTI_ASSET_UPLOAD',
1000 token1 => 'FA_ASSET_ID',
1001 value1 => '' || l_asset_id(i),
1002 p_log_level_rec => g_log_level_rec);
1003
1004 l_ret_code:=1;
1005 -- Bug# 7045739 end
1006 --Bug#7594562 - When an impairment is already posted in current period for an asset.
1007 elsif l_nbv_value(i) = -2 then
1008 fa_srvr_msg.add_message(
1009 calling_fn => null,
1010 name => 'FA_IMPAIR_ROLLBACK_ASSET',
1011 token1 => 'FA_ASSET_ID',
1012 value1 => '' || l_asset_id(i),
1013 p_log_level_rec => g_log_level_rec);
1014
1015 l_ret_code:=1;
1016 --Bug#7594562 end
1017 /*Bug#8555199 - Negative impairment loss amount is not allowed*/
1018 elsif l_nbv_value(i) = -3 then
1019 fa_srvr_msg.add_message(
1020 calling_fn => null,
1021 name => 'FA_NEG_IMPAIR_LOSS_AMOUNT',
1022 token1 => 'FA_ASSET_ID',
1023 value1 => '' || l_asset_id(i),
1024 p_log_level_rec => g_log_level_rec);
1025
1026 l_ret_code:=1;
1027 --Bug#8614268 - Impairment Accounts not defined.
1028 elsif l_nbv_value(i) = -4 then
1029 fa_srvr_msg.add_message(
1030 calling_fn => null,
1031 name => 'FA_IMPAIR_ACCTS_NOT_DEFINED',
1032 token1 => 'FA_ASSET_ID',
1033 value1 => '' || l_asset_id(i),
1034 p_log_level_rec => g_log_level_rec);
1035
1036 l_ret_code:=1;
1037 --Bug#8614268 end
1038 end if;
1039 end loop;
1040
1041 if l_ret_code = 1 then --Bug# 7000391 added if condition
1042 fa_srvr_msg.add_message(
1043 calling_fn => null,
1044 name => 'FA_SHARED_END_WITH_WARNING',
1045 token1 => 'PROGRAM',
1046 value1 => 'FAPIMP', p_log_level_rec => g_log_level_rec);
1047 FND_MSG_PUB.Count_And_Get(
1048 p_count => l_msg_count,
1049 p_data => l_msg_data);
1050
1051 fa_srvr_msg.Write_Msg_Log(l_msg_count, l_msg_data, p_log_level_rec => g_log_level_rec);
1052 --return warning to concurrent manager
1053 retcode := 1;
1054 else --Bug# 7000391 end
1055 fa_srvr_msg.add_message(
1056 calling_fn => l_calling_fn,
1057 name => 'FA_SHARED_END_SUCCESS',
1058 token1 => 'PROGRAM',
1059 value1 => 'FAPIMP', p_log_level_rec => g_log_level_rec);
1060
1061 -- FND_MSG_PUB.Count_And_Get(
1062 -- p_count => l_msg_count,
1063 -- p_data => l_msg_data);
1064
1065 fa_srvr_msg.Write_Msg_Log(1, null, p_log_level_rec => g_log_level_rec);
1066
1067 -- return success to concurrent manager
1068 retcode := 0;
1069 END IF; --Bug# 7000391
1070 EXCEPTION
1071 WHEN imp_err THEN
1072 ROLLBACK WORK;
1073
1074 if (p_parent_request_id is null) then
1075 if not rollback_impairment(p_book_type_code => p_book_type_code
1076 , p_request_id => l_request_id
1077 , p_mode => p_mode
1078 , p_impairment_id => p_impairment_id
1079 , p_calling_fn => l_calling_fn
1080 , p_mrc_sob_type_code => l_mrc_sob_type_code
1081 , p_set_of_books_id => p_set_of_books_id) then /* Bug 6437003 added p_mrc_sob_type_code to check for set of books type code*/
1082 if (g_log_level_rec.statement_level) then
1083 fa_debug_pkg.add(l_calling_fn, 'calling rollback_impairment', 'FAILED', p_log_level_rec => g_log_level_rec);
1084 end if;
1085
1086 end if;
1087 end if;
1088 fa_srvr_msg.add_message(
1089 calling_fn => 'fa_process_impairment_pkg.do_process_impairment',
1090 name => 'FA_SHARED_END_WITH_ERROR',
1091 token1 => 'PROGRAM',
1092 value1 => 'FAPIMP', p_log_level_rec => g_log_level_rec);
1093
1094 if (g_log_level_rec.statement_level) then
1095 fa_debug_pkg.Write_Debug_Log;
1096 end if;
1097 FND_MSG_PUB.Count_And_Get(
1098 p_count => l_msg_count,
1099 p_data => l_msg_data);
1100 fa_srvr_msg.Write_Msg_Log(l_msg_count, l_msg_data, p_log_level_rec => g_log_level_rec);
1101 -- return failure to concurrent manager
1102 retcode := 2;
1103
1104 WHEN OTHERS THEN
1105 ROLLBACK WORK;
1106
1107 if (p_parent_request_id is null) then
1108 if not rollback_impairment(p_book_type_code => p_book_type_code
1109 , p_request_id => l_request_id
1110 , p_mode => p_mode
1111 , p_impairment_id => p_impairment_id
1112 , p_calling_fn => l_calling_fn
1113 , p_mrc_sob_type_code => l_mrc_sob_type_code
1114 , p_set_of_books_id => p_set_of_books_id) then /* Bug 6437003 added p_mrc_sob_type_code to check for set of books type code*/
1115 if (g_log_level_rec.statement_level) then
1116 fa_debug_pkg.add(l_calling_fn, 'calling rollback_impairment', 'FAILED', p_log_level_rec => g_log_level_rec);
1117 end if;
1118
1119 end if;
1120 end if;
1121
1122 fa_srvr_msg.add_sql_error (
1123 calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1124 fa_srvr_msg.add_message(
1125 calling_fn => l_calling_fn,
1126 name => 'FA_SHARED_END_WITH_ERROR',
1127 token1 => 'PROGRAM',
1128 value1 => 'FAPIMP', p_log_level_rec => g_log_level_rec);
1129
1130 if (g_log_level_rec.statement_level) then
1131 fa_debug_pkg.Write_Debug_Log;
1132 end if;
1133 FND_MSG_PUB.Count_And_Get(
1134 p_count => l_msg_count,
1135 p_data => l_msg_data);
1136 fa_srvr_msg.Write_Msg_Log(l_msg_count, l_msg_data, p_log_level_rec => g_log_level_rec);
1137 -- return failure to concurrent manager
1138 retcode := 2;
1139 if (g_log_level_rec.statement_level) then
1140 fa_debug_pkg.add(l_calling_fn,'EXCEPTION(OTHERS)', sqlerrm);
1141 end if;
1142 END process_impairments;
1143
1144
1145 FUNCTION assign_workers (p_request_id IN NUMBER,
1146 p_book_type_code IN VARCHAR2,
1147 p_total_requests IN NUMBER,
1148 p_period_rec IN FA_API_TYPES.period_rec_type,
1149 p_prev_sysdate IN DATE,
1150 p_login_id IN NUMBER,
1151 p_transaction_date IN DATE,
1152 p_set_of_books_id In NUMBER,
1153 p_mrc_sob_type_code IN VARCHAR2,
1154 p_calling_fn IN VARCHAR2)
1155 RETURN BOOLEAN IS
1156
1157 -- l_calling_fn varchar2(50) := 'fa_process_impairment_pkg.assign_workers';
1158 l_calling_fn varchar2(50) := 'assign_workers';
1159
1160 l_process_order number(15);
1161 l_mode varchar2(30) := 'RUNNING DEPRN';
1162
1163 CURSOR c_get_currency_info(c_set_of_books_id number) IS
1164 SELECT curr.precision
1165 FROM fnd_currencies curr
1166 , gl_sets_of_books sob
1167 WHERE sob.set_of_books_id = c_set_of_books_id
1168 AND curr.currency_code = sob.currency_code;
1169
1170 l_exchange_date date;
1171 l_rate number;
1172 l_precision number;
1173 l_mrc_nsp number; -- converted net selling price
1174 l_mrc_viu number; -- converted value in use
1175 l_mrc_gwa number; -- converted goodwill amount
1176
1177 agn_err EXCEPTION;
1178 BEGIN
1179 if (g_log_level_rec.statement_level) then
1180 fa_debug_pkg.add(l_calling_fn, 'assign_workers', 'BEGIN: '||to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'));
1181 end if;
1182
1183 --
1184 -- Copy primary book impairment over fa_impairment_mrc_v
1185 --
1186 if p_mrc_sob_type_code = 'R' then
1187
1188 l_exchange_date := p_transaction_date;
1189
1190 OPEN c_get_currency_info(p_set_of_books_id);
1191 FETCH c_get_currency_info INTO l_precision;
1192 CLOSE c_get_currency_info;
1193
1194
1195 -- Need to get exchange rate
1196 if not FA_MC_UTIL_PVT.get_trx_rate
1197 (p_prim_set_of_books_id => fa_cache_pkg.fazcbc_record.set_of_books_id,
1198 p_reporting_set_of_books_id => p_set_of_books_id,
1199 px_exchange_date => l_exchange_date,
1200 p_book_type_code => p_book_type_code,
1201 px_rate => l_rate, p_log_level_rec => g_log_level_rec)then
1202 raise agn_err;
1203 end if;
1204
1205
1206 -- round values
1207
1208 insert into fa_mc_impairments(
1209 IMPAIRMENT_ID
1210 , IMPAIRMENT_NAME
1211 , DESCRIPTION
1212 , REQUEST_ID
1213 , STATUS
1214 , BOOK_TYPE_CODE
1215 , CASH_GENERATING_UNIT_ID
1216 , ASSET_ID
1217 , NET_BOOK_VALUE
1218 , NET_SELLING_PRICE
1219 , VALUE_IN_USE
1220 , GOODWILL_ASSET_ID
1221 , GOODWILL_AMOUNT
1222 , USER_DATE
1223 , IMPAIRMENT_DATE
1224 , PERIOD_COUNTER_IMPAIRED
1225 , IMPAIRMENT_AMOUNT
1226 , DATE_INEFFECTIVE
1227 , CREATION_DATE
1228 , CREATED_BY
1229 , LAST_UPDATE_DATE
1230 , LAST_UPDATED_BY
1231 , LAST_UPDATE_LOGIN
1232 , SET_OF_BOOKS_ID
1233 , IMPAIR_CLASS -- Start of Bug 6666666
1234 , REASON
1235 , IMPAIR_LOSS_ACCT
1236 , SPLIT_IMPAIR_FLAG
1237 , SPLIT1_IMPAIR_CLASS
1238 , SPLIT1_REASON
1239 , SPLIT1_PERCENT
1240 , SPLIT1_LOSS_ACCT
1241 , SPLIT2_IMPAIR_CLASS
1242 , SPLIT2_REASON
1243 , SPLIT2_PERCENT
1244 , SPLIT2_LOSS_ACCT
1245 , SPLIT3_IMPAIR_CLASS
1246 , SPLIT3_REASON
1247 , SPLIT3_PERCENT
1248 , SPLIT3_LOSS_ACCT -- End of Bug 6666666
1249
1250 ) select IMPAIRMENT_ID
1251 , IMPAIRMENT_NAME
1252 , DESCRIPTION
1253 , p_request_id -- REQUEST_ID
1254 , STATUS
1255 , p_book_type_code -- BOOK_TYPE_CODE
1256 , CASH_GENERATING_UNIT_ID
1257 , ASSET_ID
1258 , round(NET_BOOK_VALUE*l_rate, l_precision) -- NET_BOOK_VALUE
1259 , round(NET_SELLING_PRICE*l_rate, l_precision) -- NET_SELLING_PRICE
1260 , round(VALUE_IN_USE*l_rate, l_precision) -- VALUE_IN_USE
1261 , GOODWILL_ASSET_ID
1262 , round(GOODWILL_AMOUNT*l_rate, l_precision) -- GOODWILL_AMOUNT
1263 , USER_DATE
1264 , IMPAIRMENT_DATE
1265 , PERIOD_COUNTER_IMPAIRED
1266 , round(IMPAIRMENT_AMOUNT*l_rate, l_precision) -- IMPAIRMENT_AMOUNT
1267 , null -- DATE_INEFFECTIVE
1268 , CREATION_DATE
1269 , CREATED_BY
1270 , LAST_UPDATE_DATE
1271 , LAST_UPDATED_BY
1272 , null -- LAST_UPDATE_LOGIN
1273 , p_set_of_books_id --SET_OF_BOOKS_ID
1274 , IMPAIR_CLASS -- Start of Bug 6666666
1275 , REASON
1276 , IMPAIR_LOSS_ACCT
1277 , SPLIT_IMPAIR_FLAG
1278 , SPLIT1_IMPAIR_CLASS
1279 , SPLIT1_REASON
1280 , SPLIT1_PERCENT
1281 , SPLIT1_LOSS_ACCT
1282 , SPLIT2_IMPAIR_CLASS
1283 , SPLIT2_REASON
1284 , SPLIT2_PERCENT
1285 , SPLIT2_LOSS_ACCT
1286 , SPLIT3_IMPAIR_CLASS
1287 , SPLIT3_REASON
1288 , SPLIT3_PERCENT
1289 , SPLIT3_LOSS_ACCT -- End of Bug 6666666
1290 from fa_impairments -- 8666930 changed to fa_impairments from fa_mc_impairments
1291 where request_id = p_request_id
1292 and book_type_code = p_book_type_code
1293 and PERIOD_COUNTER_IMPAIRED = p_period_rec.period_counter
1294 -- and set_of_books_id = p_set_of_books_id --8666930 commented
1295 ;
1296 end if;
1297
1298
1299 if p_mrc_sob_type_code = 'R' then
1300 --
1301 -- Primary Book
1302 --
1303 --Bug# 7292608 When updating uploaded impairment,existing row needs to be deleted first
1304 delete from fa_mc_itf_impairments itmp
1305 where set_of_books_id = p_set_of_books_id
1306 and exists
1307 (select 'Uploaded impairment'
1308 from fa_mc_books bk , fa_mc_impairments imp
1309 where imp.book_type_code = p_book_type_code
1310 and imp.status = l_mode
1311 and imp.request_id = p_request_id
1312 and imp.set_of_books_id = p_set_of_books_id
1313 and bk.book_type_code = p_book_type_code
1314 and bk.set_of_books_id = p_set_of_books_id
1315 and (bk.period_counter_fully_retired is null or bk.adjustment_required_status <> 'NONE')
1316 /* Bug#7581881 Removed condition on fully reserve asset and show warning */
1317 and bk.deprn_start_date <= p_period_rec.calendar_period_close_date
1318 and (imp.asset_id = bk.asset_id or
1319 bk.cash_generating_unit_id = imp.cash_generating_unit_id)
1320 and bk.transaction_header_id_out is null
1321 and itmp.impairment_id = imp.impairment_id);
1322 --Bug6433799
1323 --Added the Set_of_books_id
1324 insert into fa_mc_itf_impairments(
1325 SET_OF_BOOKS_ID
1326 , REQUEST_ID
1327 , IMPAIRMENT_ID
1328 , BOOK_TYPE_CODE
1329 , ASSET_ID
1330 , CASH_GENERATING_UNIT_ID
1331 , GOODWILL_ASSET_FLAG
1332 , ADJUSTED_COST
1333 , PERIOD_COUNTER
1334 , COST
1335 , IMPAIRMENT_AMOUNT
1336 , YTD_IMPAIRMENT
1337 , impairment_reserve
1338 , CREATION_DATE
1339 , CREATED_BY
1340 , LAST_UPDATE_DATE
1341 , LAST_UPDATED_BY
1342 , IMPAIRMENT_DATE
1343 , WORKER_ID
1344 , PROCESS_ORDER
1345 , IMPAIR_CLASS -- Start of Bug 6666666
1346 , REASON
1347 , IMPAIR_LOSS_ACCT
1348 , SPLIT_IMPAIR_FLAG
1349 , SPLIT1_IMPAIR_CLASS
1350 , SPLIT1_REASON
1351 , SPLIT1_PERCENT
1352 , SPLIT1_LOSS_ACCT
1353 , SPLIT2_IMPAIR_CLASS
1354 , SPLIT2_REASON
1355 , SPLIT2_PERCENT
1356 , SPLIT2_LOSS_ACCT
1357 , SPLIT3_IMPAIR_CLASS
1358 , SPLIT3_REASON
1359 , SPLIT3_PERCENT
1360 , SPLIT3_LOSS_ACCT -- End of Bug 6666666
1361 ) select p_set_of_books_id --SET_OF_BOOKS_ID
1362 , p_request_id --REQUEST_ID
1363 , imp.impairment_id --IMPAIRMENT_ID
1364 , p_book_type_code --BOOK_TYPE_CODE
1365 , bk.ASSET_ID --ASSET_ID
1366 , imp.CASH_GENERATING_UNIT_ID --CASH_GENERATING_UNIT_ID
1367 , decode(bk.asset_id,imp.goodwill_asset_id, 'Y', null) --GOODWILL_ASSET_FLAG
1368 , bk.ADJUSTED_COST --ADJUSTED_COST
1369 , p_period_rec.period_counter --PERIOD_COUNTER
1370 , bk.COST --COST
1371 , 0 -- IMPAIRMENT_AMOUNT
1372 , 0 --YTD_IMPAIRMENT
1373 , 0 --impairment_reserve
1374 , p_prev_sysdate --CREATION_DATE
1375 , p_login_id --CREATED_BY
1376 , p_prev_sysdate --LAST_UPDATE_DATE
1377 , p_login_id --LAST_UPDATED_BY
1378 , nvl(imp.impairment_date, p_transaction_date) --IMPAIRMENT_DATE
1379 , 0 --mod(rank() over(order by bk.asset_id), p_total_requests) --WORKER_ID --Bug5736200
1380 , l_process_order --PROCESS_ORDER
1381 , imp.IMPAIR_CLASS -- Start of Bug 6666666
1382 , imp.REASON
1383 , imp.IMPAIR_LOSS_ACCT
1384 , imp.SPLIT_IMPAIR_FLAG
1385 , imp.SPLIT1_IMPAIR_CLASS
1386 , imp.SPLIT1_REASON
1387 , imp.SPLIT1_PERCENT
1388 , imp.SPLIT1_LOSS_ACCT
1389 , imp.SPLIT2_IMPAIR_CLASS
1390 , imp.SPLIT2_REASON
1391 , imp.SPLIT2_PERCENT
1392 , imp.SPLIT2_LOSS_ACCT
1393 , imp.SPLIT3_IMPAIR_CLASS
1394 , imp.SPLIT3_REASON
1395 , imp.SPLIT3_PERCENT
1396 , imp.SPLIT3_LOSS_ACCT -- End of Bug 6666666
1397 from fa_mc_books bk
1398 , fa_mc_impairments imp
1399 where imp.book_type_code = p_book_type_code
1400 and imp.status = l_mode
1401 and imp.request_id = p_request_id
1402 and imp.set_of_books_id = p_set_of_books_id
1403 and bk.book_type_code = p_book_type_code
1404 and (bk.period_counter_fully_retired is null or bk.adjustment_required_status <> 'NONE')
1405 /* Bug#7581881 Removed condition on fully reserve asset and show warning */
1406 and bk.deprn_start_date <= p_period_rec.calendar_period_close_date
1407 and (imp.asset_id = bk.asset_id or
1408 bk.cash_generating_unit_id = imp.cash_generating_unit_id)
1409 and bk.transaction_header_id_out is null
1410 and bk.set_of_books_id = p_set_of_books_id;
1411
1412
1413 if sql%rowcount = 0 then
1414 if (g_log_level_rec.statement_level) then
1415 fa_debug_pkg.add(l_calling_fn, 'No rows to assign', 'Check Impairment', p_log_level_rec => g_log_level_rec);
1416 fa_debug_pkg.add(l_calling_fn, 'p_book_type_code', p_book_type_code, p_log_level_rec => g_log_level_rec);
1417 fa_debug_pkg.add(l_calling_fn, 'l_mode', l_mode, p_log_level_rec => g_log_level_rec);
1418 fa_debug_pkg.add(l_calling_fn, 'p_period_rec.period_close_date', p_period_rec.period_close_date, p_log_level_rec => g_log_level_rec);
1419 fa_debug_pkg.add(l_calling_fn, 'p_prev_sysdate', p_prev_sysdate, p_log_level_rec => g_log_level_rec);
1420 end if;
1421
1422 raise agn_err;
1423 end if;
1424
1425 commit;
1426
1427 --Bug# 7292608 When updating uploaded impairment,existing row needs to be deleted first
1428 delete from fa_mc_itf_impairments itmp
1429 where set_of_books_id = p_set_of_books_id
1430 and exists
1431 (select 'Uploaded impairment'
1432 from fa_mc_books bk , fa_mc_impairments imp
1433 where imp.book_type_code = p_book_type_code
1434 and imp.status = l_mode
1435 and imp.request_id = p_request_id
1436 and imp.set_of_books_id = p_set_of_books_id
1437 and bk.book_type_code = p_book_type_code
1438 and bk.set_of_books_id = p_set_of_books_id
1439 and (bk.period_counter_fully_retired is null or bk.adjustment_required_status <> 'NONE')
1440 /* Bug#7581881 Removed condition on fully reserve asset and show warning */
1441 and bk.deprn_start_date <= p_period_rec.calendar_period_close_date
1442 and imp.goodwill_asset_id = bk.asset_id
1443 and bk.transaction_header_id_out is null
1444 and itmp.impairment_id = imp.impairment_id);
1445
1446 -- Insert goodwill asset record into itf table
1447 insert into fa_mc_itf_impairments(
1448 SET_OF_BOOKS_ID
1449 , REQUEST_ID
1450 , IMPAIRMENT_ID
1451 , BOOK_TYPE_CODE
1452 , ASSET_ID
1453 , CASH_GENERATING_UNIT_ID
1454 , GOODWILL_ASSET_FLAG
1455 , ADJUSTED_COST
1456 , PERIOD_COUNTER
1457 , COST
1458 , IMPAIRMENT_AMOUNT
1459 , YTD_IMPAIRMENT
1460 , impairment_reserve
1461 , CREATION_DATE
1462 , CREATED_BY
1463 , LAST_UPDATE_DATE
1464 , LAST_UPDATED_BY
1465 , IMPAIRMENT_DATE
1466 , WORKER_ID
1467 , PROCESS_ORDER
1468 , IMPAIR_CLASS -- Start of Bug 6666666
1469 , REASON
1470 , IMPAIR_LOSS_ACCT
1471 , SPLIT_IMPAIR_FLAG
1472 , SPLIT1_IMPAIR_CLASS
1473 , SPLIT1_REASON
1474 , SPLIT1_PERCENT
1475 , SPLIT1_LOSS_ACCT
1476 , SPLIT2_IMPAIR_CLASS
1477 , SPLIT2_REASON
1478 , SPLIT2_PERCENT
1479 , SPLIT2_LOSS_ACCT
1480 , SPLIT3_IMPAIR_CLASS
1481 , SPLIT3_REASON
1482 , SPLIT3_PERCENT
1483 , SPLIT3_LOSS_ACCT -- End of Bug 6666666
1484 ) select
1485 p_set_of_books_id
1486 , p_request_id --REQUEST_ID
1487 , imp.impairment_id --IMPAIRMENT_ID
1488 , p_book_type_code --BOOK_TYPE_CODE
1489 , bk.ASSET_ID --ASSET_ID
1490 , bk.CASH_GENERATING_UNIT_ID --CASH_GENERATING_UNIT_ID
1491 , 'Y' --GOODWILL_ASSET_FLAG
1492 , bk.ADJUSTED_COST --ADJUSTED_COST
1493 , p_period_rec.period_counter --PERIOD_COUNTER
1494 , bk.COST --COST
1495 , imp.GOODWILL_AMOUNT --IMPAIRMENT_AMOUNT
1496 , imp.GOODWILL_AMOUNT --YTD_IMPAIRMENT
1497 , imp.GOODWILL_AMOUNT --impairment_reserve
1498 , p_prev_sysdate --CREATION_DATE
1499 , p_login_id --CREATED_BY
1500 , p_prev_sysdate --LAST_UPDATE_DATE
1501 , p_login_id --LAST_UPDATED_BY
1502 , nvl(imp.impairment_date, p_transaction_date) --IMPAIRMENT_DATE
1503 , 0 --mod(rank() over(order by bk.asset_id), p_total_requests) --WORKER_ID --Bug5736200
1504 , l_process_order --PROCESS_ORDER
1505 , imp.IMPAIR_CLASS -- Start of Bug 6666666
1506 , imp.REASON
1507 , imp.IMPAIR_LOSS_ACCT
1508 , imp.SPLIT_IMPAIR_FLAG
1509 , imp.SPLIT1_IMPAIR_CLASS
1510 , imp.SPLIT1_REASON
1511 , imp.SPLIT1_PERCENT
1512 , imp.SPLIT1_LOSS_ACCT
1513 , imp.SPLIT2_IMPAIR_CLASS
1514 , imp.SPLIT2_REASON
1515 , imp.SPLIT2_PERCENT
1516 , imp.SPLIT2_LOSS_ACCT
1517 , imp.SPLIT3_IMPAIR_CLASS
1518 , imp.SPLIT3_REASON
1519 , imp.SPLIT3_PERCENT
1520 , imp.SPLIT3_LOSS_ACCT -- End of Bug 6666666
1521 from fa_mc_books bk
1522 , fa_mc_impairments imp
1523 where imp.book_type_code = p_book_type_code
1524 and imp.status = l_mode
1525 and imp.request_id = p_request_id
1526 and imp.set_of_books_id = p_set_of_books_id
1527 and bk.book_type_code = p_book_type_code
1528 and bk.set_of_books_id = p_set_of_books_id
1529 and (bk.period_counter_fully_retired is null or bk.adjustment_required_status <> 'NONE')
1530 /* Bug#7581881 Removed condition on fully reserve asset and show warning */
1531 and bk.deprn_start_date <= p_period_rec.calendar_period_close_date
1532 and imp.goodwill_asset_id = bk.asset_id
1533 and bk.transaction_header_id_out is null;
1534
1535
1536
1537
1538 else
1539 --
1540 -- Primary Book
1541 --
1542 --Bug# 7292608 When updating uploaded impairment,existing row needs to be deleted first
1543 delete from fa_itf_impairments itmp
1544 where exists
1545 (select 'Uploaded impairment'
1546 from fa_books bk , fa_impairments imp
1547 where imp.book_type_code = p_book_type_code
1548 and imp.status = l_mode
1549 and imp.request_id = p_request_id
1550 and bk.book_type_code = p_book_type_code
1551 and (bk.period_counter_fully_retired is null or bk.adjustment_required_status <> 'NONE')
1552 /* Bug#7581881 Removed condition on fully reserve asset and show warning */
1553 and bk.deprn_start_date <= p_period_rec.calendar_period_close_date
1554 and (imp.asset_id = bk.asset_id or
1555 bk.cash_generating_unit_id = imp.cash_generating_unit_id)
1556 and bk.transaction_header_id_out is null
1557 and itmp.impairment_id = imp.impairment_id);
1558
1559 insert into fa_itf_impairments(
1560 REQUEST_ID
1561 , IMPAIRMENT_ID
1562 , BOOK_TYPE_CODE
1563 , ASSET_ID
1564 , CASH_GENERATING_UNIT_ID
1565 , GOODWILL_ASSET_FLAG
1566 , ADJUSTED_COST
1567 , PERIOD_COUNTER
1568 , COST
1569 , IMPAIRMENT_AMOUNT
1570 , YTD_IMPAIRMENT
1571 , impairment_reserve
1572 , CREATION_DATE
1573 , CREATED_BY
1574 , LAST_UPDATE_DATE
1575 , LAST_UPDATED_BY
1576 , IMPAIRMENT_DATE
1577 , WORKER_ID
1578 , PROCESS_ORDER
1579 , IMPAIR_CLASS -- Start of Bug 6666666
1580 , REASON
1581 , IMPAIR_LOSS_ACCT
1582 , SPLIT_IMPAIR_FLAG
1583 , SPLIT1_IMPAIR_CLASS
1584 , SPLIT1_REASON
1585 , SPLIT1_PERCENT
1586 , SPLIT1_LOSS_ACCT
1587 , SPLIT2_IMPAIR_CLASS
1588 , SPLIT2_REASON
1589 , SPLIT2_PERCENT
1590 , SPLIT2_LOSS_ACCT
1591 , SPLIT3_IMPAIR_CLASS
1592 , SPLIT3_REASON
1593 , SPLIT3_PERCENT
1594 , SPLIT3_LOSS_ACCT -- End of Bug 6666666
1595 ) select p_request_id --REQUEST_ID
1596 , imp.impairment_id --IMPAIRMENT_ID
1597 , p_book_type_code --BOOK_TYPE_CODE
1598 , bk.ASSET_ID --ASSET_ID
1599 , imp.CASH_GENERATING_UNIT_ID --CASH_GENERATING_UNIT_ID
1600 , decode(bk.asset_id,imp.goodwill_asset_id, 'Y', null) --GOODWILL_ASSET_FLAG
1601 , bk.ADJUSTED_COST --ADJUSTED_COST
1602 , p_period_rec.period_counter --PERIOD_COUNTER
1603 , bk.COST --COST
1604 , 0 -- IMPAIRMENT_AMOUNT
1605 , 0 --YTD_IMPAIRMENT
1606 , 0 --impairment_reserve
1607 , p_prev_sysdate --CREATION_DATE
1608 , p_login_id --CREATED_BY
1609 , p_prev_sysdate --LAST_UPDATE_DATE
1610 , p_login_id --LAST_UPDATED_BY
1611 , nvl(imp.impairment_date, p_transaction_date) --IMPAIRMENT_DATE
1612 , 0 --mod(rank() over(order by bk.asset_id), p_total_requests) --WORKER_ID --Bug5736200
1613 , l_process_order --PROCESS_ORDER
1614 , imp.IMPAIR_CLASS -- Start of Bug 6666666
1615 , imp.REASON
1616 , imp.IMPAIR_LOSS_ACCT
1617 , imp.SPLIT_IMPAIR_FLAG
1618 , imp.SPLIT1_IMPAIR_CLASS
1619 , imp.SPLIT1_REASON
1620 , imp.SPLIT1_PERCENT
1621 , imp.SPLIT1_LOSS_ACCT
1622 , imp.SPLIT2_IMPAIR_CLASS
1623 , imp.SPLIT2_REASON
1624 , imp.SPLIT2_PERCENT
1625 , imp.SPLIT2_LOSS_ACCT
1626 , imp.SPLIT3_IMPAIR_CLASS
1627 , imp.SPLIT3_REASON
1628 , imp.SPLIT3_PERCENT
1629 , imp.SPLIT3_LOSS_ACCT -- End of Bug 6666666
1630 from fa_books bk
1631 , fa_impairments imp
1632 where imp.book_type_code = p_book_type_code
1633 and imp.status = l_mode
1634 and imp.request_id = p_request_id
1635 and bk.book_type_code = p_book_type_code
1636 and (bk.period_counter_fully_retired is null or bk.adjustment_required_status <> 'NONE')
1637 /* Bug#7581881 Removed condition on fully reserve asset and show warning */
1638 and bk.deprn_start_date <= p_period_rec.calendar_period_close_date
1639 and (imp.asset_id = bk.asset_id or
1640 bk.cash_generating_unit_id = imp.cash_generating_unit_id)
1641 and bk.transaction_header_id_out is null;
1642
1643 if sql%rowcount = 0 then
1644 if (g_log_level_rec.statement_level) then
1645 fa_debug_pkg.add(l_calling_fn, 'No rows to assign', 'Check Impairment', p_log_level_rec => g_log_level_rec);
1646 fa_debug_pkg.add(l_calling_fn, 'p_book_type_code', p_book_type_code, p_log_level_rec => g_log_level_rec);
1647 fa_debug_pkg.add(l_calling_fn, 'l_mode', l_mode, p_log_level_rec => g_log_level_rec);
1648 fa_debug_pkg.add(l_calling_fn, 'p_period_rec.period_close_date', p_period_rec.period_close_date, p_log_level_rec => g_log_level_rec);
1649 fa_debug_pkg.add(l_calling_fn, 'p_prev_sysdate', p_prev_sysdate, p_log_level_rec => g_log_level_rec);
1650 end if;
1651
1652 raise agn_err;
1653 end if;
1654
1655 commit;
1656
1657 --Bug# 7292608 When updating uploaded impairment,existing row needs to be deleted first
1658 delete from fa_itf_impairments itmp
1659 where exists
1660 (select 'Uploaded impairment'
1661 from fa_books bk , fa_impairments imp
1662 where imp.book_type_code = p_book_type_code
1663 and imp.status = l_mode
1664 and imp.request_id = p_request_id
1665 and bk.book_type_code = p_book_type_code
1666 and (bk.period_counter_fully_retired is null or bk.adjustment_required_status <> 'NONE')
1667 /* Bug#7581881 Removed condition on fully reserve asset and show warning */
1668 and bk.deprn_start_date <= p_period_rec.calendar_period_close_date
1669 and imp.goodwill_asset_id = bk.asset_id
1670 and bk.transaction_header_id_out is null
1671 and itmp.impairment_id = imp.impairment_id);
1672
1673 -- Insert goodwill asset record into itf table
1674 insert into fa_itf_impairments(
1675 REQUEST_ID
1676 , IMPAIRMENT_ID
1677 , BOOK_TYPE_CODE
1678 , ASSET_ID
1679 , CASH_GENERATING_UNIT_ID
1680 , GOODWILL_ASSET_FLAG
1681 , ADJUSTED_COST
1682 , PERIOD_COUNTER
1683 , COST
1684 , IMPAIRMENT_AMOUNT
1685 , YTD_IMPAIRMENT
1686 , impairment_reserve
1687 , CREATION_DATE
1688 , CREATED_BY
1689 , LAST_UPDATE_DATE
1690 , LAST_UPDATED_BY
1691 , IMPAIRMENT_DATE
1692 , WORKER_ID
1693 , PROCESS_ORDER
1694 , IMPAIR_CLASS -- Start of Bug 6666666
1695 , REASON
1696 , IMPAIR_LOSS_ACCT
1697 , SPLIT_IMPAIR_FLAG
1698 , SPLIT1_IMPAIR_CLASS
1699 , SPLIT1_REASON
1700 , SPLIT1_PERCENT
1701 , SPLIT1_LOSS_ACCT
1702 , SPLIT2_IMPAIR_CLASS
1703 , SPLIT2_REASON
1704 , SPLIT2_PERCENT
1705 , SPLIT2_LOSS_ACCT
1706 , SPLIT3_IMPAIR_CLASS
1707 , SPLIT3_REASON
1708 , SPLIT3_PERCENT
1709 , SPLIT3_LOSS_ACCT -- End of Bug 6666666
1710 ) select
1711 p_request_id --REQUEST_ID
1712 , imp.impairment_id --IMPAIRMENT_ID
1713 , p_book_type_code --BOOK_TYPE_CODE
1714 , bk.ASSET_ID --ASSET_ID
1715 , bk.CASH_GENERATING_UNIT_ID --CASH_GENERATING_UNIT_ID
1716 , 'Y' --GOODWILL_ASSET_FLAG
1717 , bk.ADJUSTED_COST --ADJUSTED_COST
1718 , p_period_rec.period_counter --PERIOD_COUNTER
1719 , bk.COST --COST
1720 , imp.GOODWILL_AMOUNT --IMPAIRMENT_AMOUNT
1721 , imp.GOODWILL_AMOUNT --YTD_IMPAIRMENT
1722 , imp.GOODWILL_AMOUNT --impairment_reserve
1723 , p_prev_sysdate --CREATION_DATE
1724 , p_login_id --CREATED_BY
1725 , p_prev_sysdate --LAST_UPDATE_DATE
1726 , p_login_id --LAST_UPDATED_BY
1727 , nvl(imp.impairment_date, p_transaction_date) --IMPAIRMENT_DATE
1728 , 0 --mod(rank() over(order by bk.asset_id), p_total_requests) --WORKER_ID --Bug5736200
1729 , l_process_order --PROCESS_ORDER
1730 , imp.IMPAIR_CLASS -- Start of Bug 6666666
1731 , imp.REASON
1732 , imp.IMPAIR_LOSS_ACCT
1733 , imp.SPLIT_IMPAIR_FLAG
1734 , imp.SPLIT1_IMPAIR_CLASS
1735 , imp.SPLIT1_REASON
1736 , imp.SPLIT1_PERCENT
1737 , imp.SPLIT1_LOSS_ACCT
1738 , imp.SPLIT2_IMPAIR_CLASS
1739 , imp.SPLIT2_REASON
1740 , imp.SPLIT2_PERCENT
1741 , imp.SPLIT2_LOSS_ACCT
1742 , imp.SPLIT3_IMPAIR_CLASS
1743 , imp.SPLIT3_REASON
1744 , imp.SPLIT3_PERCENT
1745 , imp.SPLIT3_LOSS_ACCT -- End of Bug 6666666
1746 from fa_books bk
1747 , fa_impairments imp
1748 where imp.book_type_code = p_book_type_code
1749 and imp.status = l_mode
1750 and imp.request_id = p_request_id
1751 and bk.book_type_code = p_book_type_code
1752 and (bk.period_counter_fully_retired is null or bk.adjustment_required_status <> 'NONE')
1753 /* Bug#7581881 Removed condition on fully reserve asset and show warning */
1754 and bk.deprn_start_date <= p_period_rec.calendar_period_close_date
1755 and imp.goodwill_asset_id = bk.asset_id
1756 and bk.transaction_header_id_out is null;
1757
1758 end if;
1759
1760 commit;
1761
1762 if (g_log_level_rec.statement_level) then
1763 fa_debug_pkg.add(l_calling_fn, 'assign_workers', 'END: '||to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'));
1764 end if;
1765
1766 return TRUE;
1767 EXCEPTION
1768
1769 WHEN agn_err THEN
1770 if (g_log_level_rec.statement_level) then
1771 fa_debug_pkg.add(l_calling_fn, 'EXCEPTION', 'agn_err', p_log_level_rec => g_log_level_rec);
1772 end if;
1773 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1774 return FALSE;
1775
1776 WHEN OTHERS THEN
1777 if (g_log_level_rec.statement_level) then
1778 fa_debug_pkg.add(l_calling_fn, 'EXCEPTION', 'OTHERS', p_log_level_rec => g_log_level_rec);
1779 end if;
1780 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1781 return FALSE;
1782
1783 END assign_workers;
1784
1785 FUNCTION check_je_post (p_book_type_code IN VARCHAR2,
1786 p_period_rec IN FA_API_TYPES.period_rec_type,
1787 p_mrc_sob_type_code IN VARCHAR2,
1788 p_set_of_books_id IN NUMBER,
1789 p_calling_fn IN VARCHAR2)
1790 RETURN BOOLEAN IS
1791
1792 -- l_calling_fn varchar2(50) := 'fa_process_impairment_pkg.check_je_post';
1793 l_calling_fn varchar2(50) := 'check_je_post';
1794
1795
1796 l_je_post_count number(15);
1797 l_dp_post_count number(15);
1798
1799
1800 chk_err EXCEPTION;
1801 BEGIN
1802 if (g_log_level_rec.statement_level) then
1803 fa_debug_pkg.add(l_calling_fn, 'Begin', p_book_type_code, p_log_level_rec => g_log_level_rec);
1804 end if;
1805
1806 if (p_mrc_sob_type_code = 'R') then
1807 select count(*)
1808 into l_je_post_count
1809 from fa_journal_entries je,
1810 fa_mc_book_controls bc
1811 where bc.book_type_code = p_book_type_code
1812 and bc.set_of_books_id = p_set_of_books_id
1813 and je.set_of_books_id = p_set_of_books_id
1814 and bc.book_type_code = je.book_type_code
1815 and je.period_counter = p_period_rec.period_counter
1816 and je.je_status in ('C', 'E')
1817 and ((addition_batch_id is NOT NULL) or
1818 (adjustment_batch_id is NOT NULL) or
1819 (depreciation_batch_id is NOT NULL) or
1820 (reclass_batch_id is NOT NULL) or
1821 (retirement_batch_id is NOT NULL) or
1822 (reval_batch_id is NOT NULL) or
1823 (transfer_batch_id is NOT NULL) or
1824 (cip_addition_batch_id is NOT NULL) or
1825 (cip_adjustment_batch_id is NOT NULL) or
1826 (cip_reclass_batch_id is NOT NULL) or
1827 (cip_retirement_batch_id is NOT NULL) or
1828 (cip_reval_batch_id is NOT NULL) or
1829 (cip_transfer_batch_id is NOT NULL) or
1830 (deprn_adjustment_batch_id is NOT NULL));
1831
1832 select count(*)
1833 into l_dp_post_count
1834 from fa_mc_deprn_periods
1835 where book_type_code = p_book_type_code
1836 and period_counter = p_period_rec.period_counter
1837 and set_of_books_id = p_set_of_books_id
1838 and ((addition_batch_id is NOT NULL) or
1839 (adjustment_batch_id is NOT NULL) or
1840 (depreciation_batch_id is NOT NULL) or
1841 (reclass_batch_id is NOT NULL) or
1842 (retirement_batch_id is NOT NULL) or
1843 (reval_batch_id is NOT NULL) or
1844 (transfer_batch_id is NOT NULL) or
1845 (cip_addition_batch_id is NOT NULL) or
1846 (cip_adjustment_batch_id is NOT NULL) or
1847 (cip_reclass_batch_id is NOT NULL) or
1848 (cip_retirement_batch_id is NOT NULL) or
1849 (cip_reval_batch_id is NOT NULL) or
1850 (cip_transfer_batch_id is NOT NULL) or
1851 (deprn_adjustment_batch_id is NOT NULL));
1852
1853 else
1854 select count(*)
1855 into l_je_post_count
1856 from fa_journal_entries je,
1857 fa_book_controls bc
1858 where bc.book_type_code = p_book_type_code
1859 and bc.set_of_books_id = je.set_of_books_id
1860 and bc.book_type_code = je.book_type_code
1861 and je.period_counter = p_period_rec.period_counter
1862 and je.je_status in ('C', 'E')
1863 and ((addition_batch_id is NOT NULL) or
1864 (adjustment_batch_id is NOT NULL) or
1865 (depreciation_batch_id is NOT NULL) or
1866 (reclass_batch_id is NOT NULL) or
1867 (retirement_batch_id is NOT NULL) or
1868 (reval_batch_id is NOT NULL) or
1869 (transfer_batch_id is NOT NULL) or
1870 (cip_addition_batch_id is NOT NULL) or
1871 (cip_adjustment_batch_id is NOT NULL) or
1872 (cip_reclass_batch_id is NOT NULL) or
1873 (cip_retirement_batch_id is NOT NULL) or
1874 (cip_reval_batch_id is NOT NULL) or
1875 (cip_transfer_batch_id is NOT NULL) or
1876 (deprn_adjustment_batch_id is NOT NULL));
1877
1878 select count(*)
1879 into l_dp_post_count
1880 from fa_deprn_periods
1881 where book_type_code = p_book_type_code
1882 and period_counter = p_period_rec.period_counter
1883 and ((addition_batch_id is NOT NULL) or
1884 (adjustment_batch_id is NOT NULL) or
1885 (depreciation_batch_id is NOT NULL) or
1886 (reclass_batch_id is NOT NULL) or
1887 (retirement_batch_id is NOT NULL) or
1888 (reval_batch_id is NOT NULL) or
1889 (transfer_batch_id is NOT NULL) or
1890 (cip_addition_batch_id is NOT NULL) or
1891 (cip_adjustment_batch_id is NOT NULL) or
1892 (cip_reclass_batch_id is NOT NULL) or
1893 (cip_retirement_batch_id is NOT NULL) or
1894 (cip_reval_batch_id is NOT NULL) or
1895 (cip_transfer_batch_id is NOT NULL) or
1896 (deprn_adjustment_batch_id is NOT NULL));
1897
1898 end if;
1899
1900 if (l_je_post_count <> 0) OR (l_dp_post_count <> 0) then
1901 fa_srvr_msg.add_message(calling_fn =>l_calling_fn,
1902 name =>'FA_RJE_ROLLBACK_JE_NOT_RUN2', p_log_level_rec => g_log_level_rec);
1903 raise chk_err;
1904 end if;
1905
1906
1907
1908
1909 if (g_log_level_rec.statement_level) then
1910 fa_debug_pkg.add(l_calling_fn, 'End', 'Success', p_log_level_rec => g_log_level_rec);
1911 end if;
1912
1913 return TRUE;
1914 EXCEPTION
1915
1916 WHEN chk_err THEN
1917 if (g_log_level_rec.statement_level) then
1918 fa_debug_pkg.add(l_calling_fn, 'EXCEPTION', 'chk_err', p_log_level_rec => g_log_level_rec);
1919 end if;
1920 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1921 return FALSE;
1922
1923 WHEN OTHERS THEN
1924 if (g_log_level_rec.statement_level) then
1925 fa_debug_pkg.add(l_calling_fn, 'EXCEPTION', 'OTHERS', p_log_level_rec => g_log_level_rec);
1926 end if;
1927 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1928 return FALSE;
1929
1930 END check_je_post;
1931
1932 FUNCTION rollback_impairment (p_book_type_code IN VARCHAR2,
1933 p_request_id IN NUMBER,
1934 p_mode IN VARCHAR2,
1935 p_impairment_id IN NUMBER,
1936 p_calling_fn IN VARCHAR2,
1937 p_mrc_sob_type_code IN VARCHAR2,
1938 p_set_of_books_id IN NUMBER) /* Bug 6437003 added p_mrc_sob_type_code to check for set of books type code*/
1939 RETURN BOOLEAN IS
1940
1941 l_calling_fn varchar2(50) := 'FA_PROCESS_IMPAIRMENT_PKG.rollback_impairment';
1942
1943 l_new_status varchar2(30);
1944 l_status varchar2(30);
1945
1946 rbi_err EXCEPTION;
1947 BEGIN
1948
1949 if (g_log_level_rec.statement_level) then
1950 fa_debug_pkg.add(l_calling_fn, 'BEGIN', ' ', p_log_level_rec => g_log_level_rec);
1951 fa_debug_pkg.add(l_calling_fn, 'p_book_type_code', p_book_type_code, p_log_level_rec => g_log_level_rec);
1952 fa_debug_pkg.add(l_calling_fn, 'p_request_id', p_request_id, p_log_level_rec => g_log_level_rec);
1953 fa_debug_pkg.add(l_calling_fn, 'p_mode', p_mode, p_log_level_rec => g_log_level_rec);
1954 fa_debug_pkg.add(l_calling_fn, 'p_impairment_id', p_impairment_id, p_log_level_rec => g_log_level_rec);
1955 fa_debug_pkg.add(l_calling_fn, 'p_mrc_sob_type_code', p_mrc_sob_type_code, p_log_level_rec => g_log_level_rec); /* Bug 6437003 displayed the value of p_mrc_sob_type_code */
1956 end if;
1957
1958 if p_mode = 'PREVIEW' then
1959 l_new_status := 'RUNNING DEPRN FAILED';
1960 l_status := 'RUNNING DEPRN';
1961 elsif p_mode = 'POST' or p_impairment_id is not null then
1962 l_new_status := 'POST FAILED';
1963 l_status := 'RUNNING POST';
1964 else
1965 if (g_log_level_rec.statement_level) then
1966 fa_debug_pkg.add(l_calling_fn, 'Not valid mode', ' rollback failed', p_log_level_rec => g_log_level_rec);
1967 raise rbi_err;
1968 end if;
1969 end if;
1970
1971 update fa_impairments
1972 set status = l_new_status
1973 where status = l_status
1974 and request_id = p_request_id;
1975
1976 if p_mrc_sob_type_code = 'R' then /* Bug 6437003 added condition to check for set of books type code*/
1977 update fa_mc_impairments
1978 set status = l_new_status
1979 where status = l_status
1980 and request_id = p_request_id
1981 and set_of_books_id = p_set_of_books_id;
1982 end if;
1983
1984 COMMIT; --Bug#7594562 - to commit the new status before exit.
1985
1986 if (g_log_level_rec.statement_level) then
1987 fa_debug_pkg.add(l_calling_fn, 'End', 'Success', p_log_level_rec => g_log_level_rec);
1988 end if;
1989
1990 return TRUE;
1991 EXCEPTION
1992
1993 WHEN rbi_err THEN
1994 if (g_log_level_rec.statement_level) then
1995 fa_debug_pkg.add(l_calling_fn, 'EXCEPTION', 'rbi_err', p_log_level_rec => g_log_level_rec);
1996 fa_debug_pkg.add(l_calling_fn, 'sqlerrm', sqlerrm, p_log_level_rec => g_log_level_rec);
1997 end if;
1998 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1999 return FALSE;
2000
2001 /* bug #6658765 - Added NO_DATA_FOUND */
2002 WHEN NO_DATA_FOUND THEN
2003 NULL;
2004
2005 WHEN OTHERS THEN
2006 if (g_log_level_rec.statement_level) then
2007 fa_debug_pkg.add(l_calling_fn, 'EXCEPTION', 'OTHERS', p_log_level_rec => g_log_level_rec);
2008 fa_debug_pkg.add(l_calling_fn, 'sqlerrm', sqlerrm, p_log_level_rec => g_log_level_rec);
2009 end if;
2010 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
2011 return FALSE;
2012
2013 END rollback_impairment;
2014
2015
2016 END FA_PROCESS_IMPAIRMENT_PKG;