[Home] [Help]
PACKAGE BODY: APPS.FA_MASS_REVAL_PKG
Source
1 PACKAGE BODY FA_MASS_REVAL_PKG as
2 /* $Header: FAMRVLB.pls 120.13.12020000.2 2012/11/30 11:03:17 gigupta ship $ */
3
4 g_release number := fa_cache_pkg.fazarel_release;
5
6 G_times_called number := 0;
7
8 G_book_type_code varchar2(30);
9 G_description varchar2(80);
10 G_reval_date date;
11 G_def_reval_fully_rsvd_flag varchar2(3);
12 G_def_life_extension_factor number;
13 G_def_life_extension_ceiling number;
14 G_def_max_fully_rsvd_revals number;
15 G_status varchar2(10);
16 G_last_request_id number;
17 G_attribute1 varchar2(150);
18 G_attribute2 varchar2(150);
19 G_attribute3 varchar2(150);
20 G_attribute4 varchar2(150);
21 G_attribute5 varchar2(150);
22 G_attribute6 varchar2(150);
23 G_attribute7 varchar2(150);
24 G_attribute8 varchar2(150);
25 G_attribute9 varchar2(150);
26 G_attribute10 varchar2(150);
27 G_attribute11 varchar2(150);
28 G_attribute12 varchar2(150);
29 G_attribute13 varchar2(150);
30 G_attribute14 varchar2(150);
31 G_attribute15 varchar2(150);
32 G_attribute_category_code varchar2(30);
33 G_created_by number;
34 G_creation_date date;
35 G_last_updated_by number;
36 G_last_update_date date;
37 G_last_update_login number;
38 G_global_attribute1 varchar2(150);
39 G_global_attribute2 varchar2(150);
40 G_global_attribute3 varchar2(150);
41 G_global_attribute4 varchar2(150);
42 G_global_attribute5 varchar2(150);
43 G_global_attribute6 varchar2(150);
44 G_global_attribute7 varchar2(150);
45 G_global_attribute8 varchar2(150);
46 G_global_attribute9 varchar2(150);
47 G_global_attribute10 varchar2(150);
48 G_global_attribute11 varchar2(150);
49 G_global_attribute12 varchar2(150);
50 G_global_attribute13 varchar2(150);
51 G_global_attribute14 varchar2(150);
52 G_global_attribute15 varchar2(150);
53 G_global_attribute16 varchar2(150);
54 G_global_attribute17 varchar2(150);
55 G_global_attribute18 varchar2(150);
56 G_global_attribute19 varchar2(150);
57 G_global_attribute20 varchar2(150);
58 G_global_attribute_category varchar2(30);
59 G_def_revalue_cip_assets_flag varchar2(1);
60 G_period_end_reval_flag varchar2(1); /* ER 14644811 */
61
62 G_period_rec FA_API_TYPES.period_rec_type;
63
64 G_batch_size number;
65
66 g_log_level_rec fa_api_types.log_level_rec_type;
67
68 PROCEDURE do_mass_reval (
69 p_mass_reval_id IN NUMBER,
70 p_mode IN VARCHAR2,
71 p_loop_count IN NUMBER,
72 p_parent_request_id IN NUMBER,
73 p_total_requests IN NUMBER,
74 p_request_number IN NUMBER,
75 x_success_count OUT NOCOPY number,
76 x_failure_count OUT NOCOPY number,
77 x_return_status OUT NOCOPY number) IS
78
79 -- used for bulk fetching
80 l_loop_count number;
81
82 -- local variables
83 l_period_of_addition varchar2(1);
84
85 -- local variables
86 TYPE v150_tbl IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
87 TYPE v30_tbl IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
88 TYPE num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
89 TYPE date_tbl IS TABLE OF DATE INDEX BY BINARY_INTEGER;
90
91 -- for main selection
92 l_rowid v30_tbl;
93 l_asset_id num_tbl;
94 l_asset_number v30_tbl;
95 l_asset_type v30_tbl;
96 l_asset_category_id num_tbl;
97 l_current_units num_tbl;
98 l_reval_percent num_tbl;
99 l_value_type v30_tbl; -- Bug#6666666 SORP
100 l_mass_reval_id num_tbl; -- Bug#6666666 SORP
101 l_linked_flag v30_tbl; -- Bug#6666666 SORP
102 l_reval_type_flag v30_tbl; -- Bug#6666666 SORP
103 l_override_defaults_flag v30_tbl;
104 l_reval_fully_rsvd_flag v30_tbl;
105 l_life_extension_factor num_tbl;
106 l_life_extension_ceiling num_tbl;
107 l_max_fully_rsvd_revals num_tbl;
108 l_r_attribute1 v150_tbl;
109 l_r_attribute2 v150_tbl;
110 l_r_attribute3 v150_tbl;
111 l_r_attribute4 v150_tbl;
112 l_r_attribute5 v150_tbl;
113 l_r_attribute6 v150_tbl;
114 l_r_attribute7 v150_tbl;
115 l_r_attribute8 v150_tbl;
116 l_r_attribute9 v150_tbl;
117 l_r_attribute10 v150_tbl;
118 l_r_attribute11 v150_tbl;
119 l_r_attribute12 v150_tbl;
120 l_r_attribute13 v150_tbl;
121 l_r_attribute14 v150_tbl;
122 l_r_attribute15 v150_tbl;
123 l_r_attribute_category_code v30_tbl;
124 l_reval_attribute_category v30_tbl;
125 l_revalue_cip_assets_flag v30_tbl;
126
127 -- variables and structs used for api call
128 l_api_version NUMBER := 1.0;
129 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
130 l_commit VARCHAR2(1) := FND_API.G_FALSE;
131 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
132 l_return_status VARCHAR2(1);
133 l_mesg_count number;
134 l_mesg VARCHAR2(4000);
135 l_calling_fn VARCHAR2(30) := 'fa_mass_reval_pkg.do_reval';
136 l_string varchar2(250);
137
138 l_asset_fin_rec_old FA_API_TYPES.asset_fin_rec_type;
139
140 l_trans_rec FA_API_TYPES.trans_rec_type;
141 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
142 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
143 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
144
145 l_reval_options_rec FA_API_TYPES.reval_options_rec_type;
146
147 l_mesg_name VARCHAR2(30);
148
149 l_process_status v30_tbl;
150
151 -- main cursors
152 -- asset based
153
154 cursor c_assets is
155 select pw.rowid,
156 pw.asset_id,
157 pw.asset_number,
158 pw.asset_type,
159 ad.asset_category_id,
160 ad.current_units,
161 rr.reval_percent,
162 rr.value_type, -- Bug#6666666 SORP
163 rr.mass_reval_id, -- Bug#6666666 SORP
164 rr.linked_flag, -- Bug#6666666 SORP
165 'A' "reval_type_flag",-- Bug#6666666 SORP
166 rr.override_defaults_flag,
167 DECODE(rr.override_defaults_flag,
168 'YES', rr.reval_fully_rsvd_flag,
169 g_def_reval_fully_rsvd_flag),
170 DECODE(rr.override_defaults_flag,
171 'YES', rr.life_extension_factor,
172 g_def_life_extension_factor),
173 DECODE(rr.override_defaults_flag,
174 'YES', rr.life_extension_ceiling,
175 g_def_life_extension_ceiling),
176 DECODE(rr.override_defaults_flag,
177 'YES', rr.max_fully_rsvd_revals,
178 g_def_max_fully_rsvd_revals),
179 rr.attribute1,
180 rr.attribute2,
181 rr.attribute3,
182 rr.attribute4,
183 rr.attribute5,
184 rr.attribute6,
185 rr.attribute7,
186 rr.attribute8,
187 rr.attribute9,
188 rr.attribute10,
189 rr.attribute11,
190 rr.attribute12,
191 rr.attribute13,
192 rr.attribute14,
193 rr.attribute15,
194 rr.attribute_category_code,
195 rr.reval_attribute_category,
196 DECODE(rr.override_defaults_flag,
197 'YES', rr.revalue_cip_assets_flag,
198 g_def_revalue_cip_assets_flag)
199 FROM fa_parallel_workers pw,
200 fa_mass_revaluation_rules rr,
201 fa_additions_b ad
202 WHERE pw.request_id = p_parent_request_id
203 AND pw.worker_number = p_request_number
204 AND pw.process_status = 'UNPROCESSED'
205 AND pw.asset_category_id is null
206 AND rr.mass_reval_id = p_mass_reval_id
207 AND rr.asset_id = pw.asset_id
208 AND ad.asset_id = pw.asset_id;
209
210
211 cursor c_assets_cat is
212 select pw.rowid,
213 pw.asset_id,
214 pw.asset_number,
215 pw.asset_type,
216 ad.asset_category_id,
217 ad.current_units,
218 rr.reval_percent,
219 rr.value_type, -- Bug#6666666 SORP
220 rr.mass_reval_id, -- Bug#6666666 SORP
221 rr.linked_flag, -- Bug#6666666 SORP
222 'C' "reval_type_flag", -- Bug#6666666 SORP
223 rr.override_defaults_flag,
224 DECODE(rr.override_defaults_flag,
225 'YES', rr.reval_fully_rsvd_flag,
226 g_def_reval_fully_rsvd_flag),
227 DECODE(rr.override_defaults_flag,
228 'YES', rr.life_extension_factor,
229 g_def_life_extension_factor),
230 DECODE(rr.override_defaults_flag,
231 'YES', rr.life_extension_ceiling,
232 g_def_life_extension_ceiling),
233 DECODE(rr.override_defaults_flag,
234 'YES', rr.max_fully_rsvd_revals,
235 g_def_max_fully_rsvd_revals),
236 rr.attribute1,
237 rr.attribute2,
238 rr.attribute3,
239 rr.attribute4,
240 rr.attribute5,
241 rr.attribute6,
242 rr.attribute7,
243 rr.attribute8,
244 rr.attribute9,
245 rr.attribute10,
246 rr.attribute11,
247 rr.attribute12,
248 rr.attribute13,
249 rr.attribute14,
250 rr.attribute15,
251 rr.attribute_category_code,
252 rr.reval_attribute_category,
253 DECODE(rr.override_defaults_flag,
254 'YES', rr.revalue_cip_assets_flag,
255 g_def_revalue_cip_assets_flag)
256 FROM fa_parallel_workers pw,
257 fa_mass_revaluation_rules rr,
258 fa_additions_b ad
259 WHERE pw.request_id = p_parent_request_id
260 AND pw.worker_number = p_request_number
261 AND pw.process_status = 'UNPROCESSED'
262 AND pw.asset_category_id is not null
263 AND rr.mass_reval_id = p_mass_reval_id
264 AND rr.category_id = pw.asset_category_id
265 AND ad.asset_id = pw.asset_id;
266
267 done_exc EXCEPTION;
268 massrvl_err EXCEPTION;
269 reval_err EXCEPTION;
270
271 BEGIN
272
273
274 if (not g_log_level_rec.initialized) then
275 if (NOT fa_util_pub.get_log_level_rec (
276 x_log_level_rec => g_log_level_rec
277 )) then
278 raise massrvl_err;
279 end if;
280 end if;
281
282 g_release := fa_cache_pkg.fazarel_release;
283
284 if (g_log_level_rec.statement_level) then
285 fa_debug_pkg.add(l_calling_fn, 'at begin', '', p_log_level_rec => g_log_level_rec);
286 end if;
287
288 G_times_called := G_times_called + 1;
289
290 x_success_count := 0;
291 x_failure_count := 0;
292
293 if (g_log_level_rec.statement_level) then
294 fa_debug_pkg.add(l_calling_fn, 'before init', '', p_log_level_rec => g_log_level_rec);
295 end if;
296
297 if (G_times_called = 1) then
298
299 FND_FILE.put(FND_FILE.output,'');
300 FND_FILE.new_line(FND_FILE.output,1);
301
302 -- dump out the headings
303 fnd_message.set_name('OFA', 'FA_MASSRET_REPORT_COLUMN');
304 l_string := fnd_message.get;
305
306 FND_FILE.put(FND_FILE.output,l_string);
307 FND_FILE.new_line(FND_FILE.output,1);
308
309 fnd_message.set_name('OFA', 'FA_MASSRET_REPORT_LINE');
310 l_string := fnd_message.get;
311
312 FND_FILE.put(FND_FILE.output,l_string);
313 FND_FILE.new_line(FND_FILE.output,1);
314
315 -- get mass reval info
316 if not get_mass_reval_info (p_mass_reval_id => p_mass_reval_id) then
317 raise massrvl_err;
318 end if;
319
320 -- initial book control validation
321 if (fa_cache_pkg.fazcbc_record.allow_reval_flag <> 'YES') then
322 fa_srvr_msg.add_message
323 (calling_fn => l_calling_fn,
324 name => 'FA_BOOK_REVAL_NOT_ALLOW', p_log_level_rec => g_log_level_rec);
325 raise massrvl_err;
326 elsif (fa_cache_pkg.fazcbc_record.date_ineffective is not null) then
327 fa_srvr_msg.add_message
328 (calling_fn => l_calling_fn,
329 name => 'FA_DATA_ERR_MASS_REVAL', p_log_level_rec => g_log_level_rec);
330 raise massrvl_err;
331 end if;
332
333 G_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
334
335 end if;
336
337 if (g_log_level_rec.statement_level) then
338 fa_debug_pkg.add(l_calling_fn, 'after init', '', p_log_level_rec => g_log_level_rec);
339 end if;
340
341 if p_loop_count = 1 then -- asset level first
342
343 if (g_log_level_rec.statement_level) then
344 fa_debug_pkg.add(l_calling_fn, 'opening c_assets cursor at', sysdate, p_log_level_rec => g_log_level_rec);
345 end if;
346
347 OPEN c_assets;
348 FETCH c_assets BULK COLLECT INTO
349 l_rowid ,
350 l_asset_id ,
351 l_asset_number ,
352 l_asset_type ,
353 l_asset_category_id ,
354 l_current_units ,
355 l_reval_percent ,
356 l_value_type , -- Bug#6666666 SORP
357 l_mass_reval_id , -- Bug#6666666 SORP
358 l_linked_flag , -- Bug#6666666 SORP
359 l_reval_type_flag , -- Bug#6666666 SORP
360 l_override_defaults_flag ,
361 l_reval_fully_rsvd_flag ,
362 l_life_extension_factor ,
363 l_life_extension_ceiling ,
364 l_max_fully_rsvd_revals ,
365 l_r_attribute1 ,
366 l_r_attribute2 ,
367 l_r_attribute3 ,
368 l_r_attribute4 ,
369 l_r_attribute5 ,
370 l_r_attribute6 ,
371 l_r_attribute7 ,
372 l_r_attribute8 ,
373 l_r_attribute9 ,
374 l_r_attribute10 ,
375 l_r_attribute11 ,
376 l_r_attribute12 ,
377 l_r_attribute13 ,
378 l_r_attribute14 ,
379 l_r_attribute15 ,
380 l_r_attribute_category_code ,
381 l_reval_attribute_category ,
382 l_revalue_cip_assets_flag
383 LIMIT G_batch_size;
384
385 close c_assets;
386
387
388 else -- category level
389
390 if (g_log_level_rec.statement_level) then
391 fa_debug_pkg.add(l_calling_fn, 'opening c_assets_cat cursor at', sysdate, p_log_level_rec => g_log_level_rec);
392 end if;
393
394 OPEN c_assets_cat;
395 FETCH c_assets_cat BULK COLLECT INTO
396 l_rowid ,
397 l_asset_id ,
398 l_asset_number ,
399 l_asset_type ,
400 l_asset_category_id ,
401 l_current_units ,
402 l_reval_percent ,
403 l_value_type , -- Bug#6666666 SORP
404 l_mass_reval_id , -- Bug#6666666 SORP
405 l_linked_flag , -- Bug#6666666 SORP
406 l_reval_type_flag , -- Bug#6666666 SORP
407 l_override_defaults_flag ,
408 l_reval_fully_rsvd_flag ,
409 l_life_extension_factor ,
410 l_life_extension_ceiling ,
411 l_max_fully_rsvd_revals ,
412 l_r_attribute1 ,
413 l_r_attribute2 ,
414 l_r_attribute3 ,
415 l_r_attribute4 ,
416 l_r_attribute5 ,
417 l_r_attribute6 ,
418 l_r_attribute7 ,
419 l_r_attribute8 ,
420 l_r_attribute9 ,
421 l_r_attribute10 ,
422 l_r_attribute11 ,
423 l_r_attribute12 ,
424 l_r_attribute13 ,
425 l_r_attribute14 ,
426 l_r_attribute15 ,
427 l_r_attribute_category_code ,
428 l_reval_attribute_category ,
429 l_revalue_cip_assets_flag
430 LIMIT G_batch_size;
431
432 close c_assets_cat;
433
434
435 end if;
436
437 if (g_log_level_rec.statement_level) then
438 fa_debug_pkg.add('test',
439 'after fetch asset count is',
440 l_rowid.count, p_log_level_rec => g_log_level_rec);
441 end if;
442
443 if (l_asset_id.count = 0) then
444 raise done_exc;
445 end if;
446
447
448 for l_loop_count in 1..l_asset_id.count loop
449
450 -- clear the debug stack for each asset
451 FA_DEBUG_PKG.Initialize;
452 -- reset the message level to prevent bogus errors
453 FA_SRVR_MSG.Set_Message_Level(message_level => 10, p_log_level_rec => g_log_level_rec);
454
455 l_mesg_name := null;
456
457 BEGIN
458
459 -- Check that assets do not have transactions dated after the
460 -- request was submitted
461
462 /* not sure if this is needed
463 if l_date_effective(l_loop_count) >= l_mass_date_effective then
464 l_mesg_name := 'FA_MASSCHG_DATE';
465 raise reval_err;
466 end if;
467 */
468
469 -- R12 conditional handling
470 if (G_release = 11 and
471 not FA_ASSET_VAL_PVT.validate_period_of_addition
472 (p_asset_id => l_asset_id(l_loop_count),
473 p_book => G_book_type_code,
474 p_mode => 'ABSOLUTE',
475 px_period_of_addition => l_period_of_addition,
476 p_log_level_rec => g_log_level_rec)) then
477 raise reval_err;
478 elsif (l_period_of_addition = 'Y') then
479 l_mesg_name := 'FA_REVAL_NO_DEPRECIATED';
480 raise reval_err;
481 end if;
482
483
484 -- validation ok, null out then load the structs and process the adjustment
485 l_trans_rec := NULL;
486 l_asset_hdr_rec := NULL;
487 l_reval_options_rec := NULL;
488
489 -- reset the who info in trans rec
490 l_trans_rec.who_info.last_update_date := sysdate;
491 l_trans_rec.who_info.last_updated_by := FND_GLOBAL.USER_ID;
492 l_trans_rec.who_info.created_by := FND_GLOBAL.USER_ID;
493 l_trans_rec.who_info.creation_date := sysdate;
494 l_trans_rec.who_info.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
495 l_trans_rec.mass_reference_id := p_parent_request_id;
496 l_trans_rec.calling_interface := 'FAMRVL';
497 l_trans_rec.mass_transaction_id := p_mass_reval_id;
498
499 l_trans_rec.transaction_date_entered := G_reval_date; -- does this need validation like date effective above
500 l_trans_rec.transaction_name := substr(G_description , 1, 30); -- Bug#8602476
501 -- asset header struct
502 l_asset_hdr_rec.asset_id := l_asset_id(l_loop_count);
503 l_asset_hdr_rec.book_type_code := G_book_type_code;
504
505 -- reval options struct
506 -- the flags must be converted from 3 char to 1
507
508 l_reval_options_rec.REVAL_PERCENT := l_reval_percent(l_loop_count);
509 l_reval_options_rec.value_type := l_value_type(l_loop_count); --Bug#6666666 SORP
510 l_reval_options_rec.mass_reval_id := l_mass_reval_id(l_loop_count); --Bug#6666666 SORP
511 l_reval_options_rec.linked_flag := l_linked_flag(l_loop_count); --Bug#6666666 SORP
512 l_reval_options_rec.reval_type_flag := l_reval_type_flag(l_loop_count); --Bug#6666666 SORP
513
514 if (nvl(l_override_defaults_flag(l_loop_count), 'NO') = 'YES') then
515 l_reval_options_rec.OVERRIDE_DEFAULTS_FLAG := 'Y';
516 else
517 l_reval_options_rec.OVERRIDE_DEFAULTS_FLAG := 'N';
518 end if;
519
520 if (nvl(l_reval_fully_rsvd_flag(l_loop_count), 'NO') = 'YES') then
521 l_reval_options_rec.REVAL_FULLY_RSVD_FLAG := 'Y';
522 else
523 l_reval_options_rec.REVAL_FULLY_RSVD_FLAG := 'N';
524 end if;
525
526 l_reval_options_rec.LIFE_EXTENSION_FACTOR := l_life_extension_factor(l_loop_count);
527 l_reval_options_rec.LIFE_EXTENSION_CEILING := l_life_extension_ceiling(l_loop_count);
528 l_reval_options_rec.MAX_FULLY_RSVD_REVALS := l_max_fully_rsvd_revals(l_loop_count);
529 l_reval_options_rec.RUN_MODE := p_mode;
530 l_reval_options_rec.period_end_reval_flag := G_period_end_reval_flag;/*ER 14644811 */
531
532 -- call the reval api now
533 FA_REVALUATION_PUB.do_reval
534 (p_api_version => l_api_version,
535 p_init_msg_list => l_init_msg_list,
536 p_commit => l_commit,
537 p_validation_level => l_validation_level,
538 x_return_status => l_return_status,
539 x_msg_count => l_mesg_count,
540 x_msg_data => l_mesg,
541 p_calling_fn => l_calling_fn,
542 px_trans_rec => l_trans_rec,
543 px_asset_hdr_rec => l_asset_hdr_rec,
544 p_reval_options_rec => l_reval_options_rec
545 );
546
547 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
548 l_mesg_name := null;
549 raise reval_err;
550 end if;
551
552 x_success_count := x_success_count + 1;
553 l_process_status(l_loop_count) := 'SUCCESS';
554
555 write_message(l_asset_number(l_loop_count),
556 'FA_MCP_SHARED_SUCCEED',
557 p_mode);
558
559 if (g_log_level_rec.statement_level) then
560 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
561 end if;
562
563 EXCEPTION
564 when reval_err then
565 FND_CONCURRENT.AF_ROLLBACK;
566
567 l_process_status(l_loop_count) := 'FAILURE';
568 x_failure_count := x_failure_count + 1;
569
570 write_message(l_asset_number(l_loop_count),
571 l_mesg_name,
572 p_mode);
573 if (g_log_level_rec.statement_level) then
574 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
575 end if;
576
577 when others then
578 FND_CONCURRENT.AF_ROLLBACK;
579
580 l_process_status(l_loop_count) := 'FAILURE';
581 x_failure_count := x_failure_count + 1;
582
583 write_message(l_asset_number(l_loop_count),
584 null,
585 p_mode);
586
587 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
588
589 if (g_log_level_rec.statement_level) then
590 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
591 end if;
592
593 END;
594
595 -- FND_CONCURRENT.AF_COMMIT each record
596 FND_CONCURRENT.AF_COMMIT;
597
598 fa_debug_pkg.add(l_calling_fn,
599 'asset_id : ', l_asset_id(l_asset_id.count));
600 fa_debug_pkg.add(l_calling_fn,
601 'count : ',(l_asset_id.count));
602
603 end loop; -- main bulk fetch loop
604
605 if (g_log_level_rec.statement_level) then
606 fa_debug_pkg.add(l_calling_fn, 'updating fa_parallel_workers for status', '', p_log_level_rec => g_log_level_rec);
607 end if;
608
609 -- now flags the rows process status accordingly
610 forall i in 1..l_rowid.count
611 update fa_parallel_workers fpw
612 set process_status = l_process_status(i)
613 where rowid = l_rowid(i);
614
615 if (g_log_level_rec.statement_level) then
616 fa_debug_pkg.add(l_calling_fn, 'rows updated in fa_parallel_workers for status', l_rowid.count, p_log_level_rec => g_log_level_rec);
617 end if;
618
619 x_return_status := 0;
620
621 if (p_mode = 'PREVIEW') then
622 write_preview_messages;
623 end if;
624
625 if (g_log_level_rec.statement_level) then
626 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
627 end if;
628
629
630 EXCEPTION
631 when done_exc then
632 x_return_status := 0;
633
634 when massrvl_err then
635 FND_CONCURRENT.AF_ROLLBACK;
636 if (g_log_level_rec.statement_level) then
637 fa_debug_pkg.add(l_calling_fn, 'in massrvl_err main', '', p_log_level_rec => g_log_level_rec);
638 end if;
639
640 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
641 if (g_log_level_rec.statement_level) then
642 FA_DEBUG_PKG.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
643 end if;
644 x_return_status := 2;
645
646 if (p_mode = 'PREVIEW') then
647 write_preview_messages;
648 end if;
649
650 when others then
651 FND_CONCURRENT.AF_ROLLBACK;
652
653 if (g_log_level_rec.statement_level) then
654 fa_debug_pkg.add(l_calling_fn, 'in massrvl_err when otherx', '', p_log_level_rec => g_log_level_rec);
655 end if;
656
657 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
658 if (g_log_level_rec.statement_level) then
659 FA_DEBUG_PKG.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
660 end if;
661 x_return_status := 2;
662
663 if (p_mode = 'PREVIEW') then
664 write_preview_messages;
665 end if;
666
667 END do_mass_reval;
668
669 -----------------------------------------------------------------------------
670
671 PROCEDURE write_message
672 (p_asset_number in varchar2,
673 p_message in varchar2,
674 p_mode in varchar2) IS
675
676 l_token varchar2(15);
677 l_value varchar2(15);
678
679 l_message varchar2(30);
680 l_mesg varchar2(100);
681 l_string varchar2(512);
682 l_calling_fn varchar2(40); -- conditionally populated below
683
684 BEGIN
685
686 -- first dump the message to the output file
687 -- set/translate/retrieve the mesg from fnd
688
689 l_message := nvl(p_message, 'FA_MCP_FAIL_ACTION');
690
691 if (l_message <> 'FA_MCP_SHARED_SUCCEED') then
692 l_calling_fn := 'fa_mass_reval_pkg.do_mass_reval';
693 end if;
694
695 if (l_message = 'FA_MCP_SHARED_SUCCEED' or
696 l_message = 'FA_MCP_FAIL_ACTION') then
697 l_token := 'ASSET';
698 l_value := p_asset_number;
699 end if;
700
701 if (p_mode = 'RUN') then
702 fnd_message.set_name('OFA', l_message);
703 if (l_message = 'FA_MCP_SHARED_SUCCEED' or
704 l_message = 'FA_MCP_FAIL_ACTION') then
705 fnd_message.set_token(l_token, l_value);
706 end if;
707
708 l_mesg := substrb(fnd_message.get, 1, 100);
709
710 l_string := rpad(p_asset_number, 15) || ' ' || l_mesg;
711
712 FND_FILE.put(FND_FILE.output,l_string);
713 FND_FILE.new_line(FND_FILE.output,1);
714
715 end if;
716
717 -- now process the messages for the log file
718 fa_srvr_msg.add_message
719 (calling_fn => l_calling_fn,
720 token1 => l_token,
721 value1 => l_value,
722 name => l_message, p_log_level_rec => g_log_level_rec);
723
724 EXCEPTION
725 when others then
726 raise;
727
728 END write_message;
729
730 -----------------------------------------------------------------------------
731
732 PROCEDURE write_preview_messages IS
733
734 l_msg_count number;
735
736 BEGIN
737
738 l_msg_count := fnd_msg_pub.count_msg;
739
740 if (l_msg_count > 0) then
741
742 fa_rx_conc_mesg_pkg.log(
743 fnd_msg_pub.get(fnd_msg_pub.G_FIRST, fnd_api.G_FALSE));
744
745 for i in 1..(l_msg_count-1) loop
746 fa_rx_conc_mesg_pkg.log(
747 fnd_msg_pub.get(fnd_msg_pub.G_NEXT, fnd_api.G_FALSE));
748 end loop;
749
750 end if;
751
752 -- clear the stack
753 fnd_msg_pub.delete_msg();
754
755 EXCEPTION
756 WHEN OTHERS THEN
757 NULL;
758 END;
759
760 -----------------------------------------------------------------------------
761
762 FUNCTION get_mass_reval_info (p_mass_reval_id number) RETURN BOOLEAN IS
763
764 -- mass reval info
765 cursor c_mass_reval_info is
766 select mrvl.BOOK_TYPE_CODE ,
767 mrvl.DESCRIPTION ,
768 mrvl.REVAL_DATE ,
769 mrvl.DEFAULT_REVAL_FULLY_RSVD_FLAG ,
770 mrvl.DEFAULT_LIFE_EXTENSION_FACTOR ,
771 mrvl.DEFAULT_LIFE_EXTENSION_CEILING ,
772 mrvl.DEFAULT_MAX_FULLY_RSVD_REVALS ,
773 mrvl.STATUS ,
774 mrvl.LAST_REQUEST_ID ,
775 mrvl.ATTRIBUTE1 ,
776 mrvl.ATTRIBUTE2 ,
777 mrvl.ATTRIBUTE3 ,
778 mrvl.ATTRIBUTE4 ,
779 mrvl.ATTRIBUTE5 ,
780 mrvl.ATTRIBUTE6 ,
781 mrvl.ATTRIBUTE7 ,
782 mrvl.ATTRIBUTE8 ,
783 mrvl.ATTRIBUTE9 ,
784 mrvl.ATTRIBUTE10 ,
785 mrvl.ATTRIBUTE11 ,
786 mrvl.ATTRIBUTE12 ,
787 mrvl.ATTRIBUTE13 ,
788 mrvl.ATTRIBUTE14 ,
789 mrvl.ATTRIBUTE15 ,
790 mrvl.ATTRIBUTE_CATEGORY_CODE ,
791 mrvl.CREATED_BY ,
792 mrvl.CREATION_DATE ,
793 mrvl.LAST_UPDATED_BY ,
794 mrvl.LAST_UPDATE_DATE ,
795 mrvl.LAST_UPDATE_LOGIN ,
796 mrvl.GLOBAL_ATTRIBUTE1 ,
797 mrvl.GLOBAL_ATTRIBUTE2 ,
798 mrvl.GLOBAL_ATTRIBUTE3 ,
799 mrvl.GLOBAL_ATTRIBUTE4 ,
800 mrvl.GLOBAL_ATTRIBUTE5 ,
801 mrvl.GLOBAL_ATTRIBUTE6 ,
802 mrvl.GLOBAL_ATTRIBUTE7 ,
803 mrvl.GLOBAL_ATTRIBUTE8 ,
804 mrvl.GLOBAL_ATTRIBUTE9 ,
805 mrvl.GLOBAL_ATTRIBUTE10 ,
806 mrvl.GLOBAL_ATTRIBUTE11 ,
807 mrvl.GLOBAL_ATTRIBUTE12 ,
808 mrvl.GLOBAL_ATTRIBUTE13 ,
809 mrvl.GLOBAL_ATTRIBUTE14 ,
810 mrvl.GLOBAL_ATTRIBUTE15 ,
811 mrvl.GLOBAL_ATTRIBUTE16 ,
812 mrvl.GLOBAL_ATTRIBUTE17 ,
813 mrvl.GLOBAL_ATTRIBUTE18 ,
814 mrvl.GLOBAL_ATTRIBUTE19 ,
815 mrvl.GLOBAL_ATTRIBUTE20 ,
816 mrvl.GLOBAL_ATTRIBUTE_CATEGORY ,
817 mrvl.REVALUE_CIP_ASSETS_FLAG ,
818 mrvl.period_end_reval_flag /*ER 14644811 */
819 from fa_mass_revaluations mrvl
820 where mrvl.mass_reval_id = p_mass_reval_id;
821
822 l_calling_fn VARCHAR2(60) := 'fa_mass_reval_pkg.get_mass_reval_info';
823 massrvl_err exception;
824
825 BEGIN
826
827 -- get the massrvl info
828 open c_mass_reval_info;
829 fetch c_mass_reval_info
830 into G_book_type_code ,
831 G_description ,
832 G_reval_date ,
833 G_def_reval_fully_rsvd_flag ,
834 G_def_life_extension_factor ,
835 G_def_life_extension_ceiling ,
836 G_def_max_fully_rsvd_revals ,
837 G_status ,
838 G_last_request_id ,
839 G_attribute1 ,
840 G_attribute2 ,
841 G_attribute3 ,
842 G_attribute4 ,
843 G_attribute5 ,
844 G_attribute6 ,
845 G_attribute7 ,
846 G_attribute8 ,
847 G_attribute9 ,
848 G_attribute10 ,
849 G_attribute11 ,
850 G_attribute12 ,
851 G_attribute13 ,
852 G_attribute14 ,
853 G_attribute15 ,
854 G_attribute_category_code ,
855 G_created_by ,
856 G_creation_date ,
857 G_last_updated_by ,
858 G_last_update_date ,
859 G_last_update_login ,
860 G_global_attribute1 ,
861 G_global_attribute2 ,
862 G_global_attribute3 ,
863 G_global_attribute4 ,
864 G_global_attribute5 ,
865 G_global_attribute6 ,
866 G_global_attribute7 ,
867 G_global_attribute8 ,
868 G_global_attribute9 ,
869 G_global_attribute10 ,
870 G_global_attribute11 ,
871 G_global_attribute12 ,
872 G_global_attribute13 ,
873 G_global_attribute14 ,
874 G_global_attribute15 ,
875 G_global_attribute16 ,
876 G_global_attribute17 ,
877 G_global_attribute18 ,
878 G_global_attribute19 ,
879 G_global_attribute20 ,
880 G_global_attribute_category ,
881 G_def_revalue_cip_assets_flag ,
882 G_period_end_reval_flag ;
883
884 if (c_mass_reval_info%NOTFOUND) then
885 close c_mass_reval_info;
886 fa_srvr_msg.add_message
887 (calling_fn => l_calling_fn,
888 name => 'FA_DATA_ERR_MASS_REVAL', p_log_level_rec => g_log_level_rec);
889 raise massrvl_err;
890 end if;
891 close c_mass_reval_info;
892
893 -- get book information
894 if not fa_cache_pkg.fazcbc(X_book => g_book_type_code, p_log_level_rec => g_log_level_rec) then
895 raise massrvl_err;
896 end if;
897
898 -- load the period struct for current period info
899 if not FA_UTIL_PVT.get_period_rec
900 (p_book => G_book_type_code,
901 p_effective_date => NULL,
902 x_period_rec => G_period_rec
903 , p_log_level_rec => g_log_level_rec) then raise massrvl_err;
904 end if;
905
906 G_batch_size := nvl(fa_cache_pkg.fa_batch_size, 1000);
907
908 return true;
909
910 EXCEPTION
911 WHEN massrvl_err THEN
912 fa_srvr_msg.add_message (calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
913 return false;
914
915 WHEN OTHERS THEN
916 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
917 return false;
918
919 END get_mass_reval_info;
920
921 -----------------------------------------------------------------------------
922
923 -- This function will select all candidate transactions in a single
924 -- shot. The primary-- We will only stripe the worker number based
925 -- on asset_id as group/parent are irrelavant to reval
926
927 PROCEDURE allocate_workers (
928 p_book_type_code IN VARCHAR2,
929 p_mass_reval_id IN NUMBER,
930 p_mode IN VARCHAR2,
931 p_parent_request_id IN NUMBER,
932 p_total_requests IN NUMBER,
933 x_return_status OUT NOCOPY NUMBER) AS
934
935
936 -- local variables
937 l_corp_period_rec FA_API_TYPES.period_rec_type;
938 l_tax_period_rec FA_API_TYPES.period_rec_type;
939 l_min_period_counter number(15);
940
941 l_calling_fn varchar2(40) := 'fa_mass_reval_pkg.allocate_workers';
942
943 -- Used for bulk fetching
944 massrvl_err exception;
945
946 BEGIN
947
948 if (not g_log_level_rec.initialized) then
949 if (NOT fa_util_pub.get_log_level_rec (
950 x_log_level_rec => g_log_level_rec
951 )) then
952 raise massrvl_err;
953 end if;
954 end if;
955
956 g_release := fa_cache_pkg.fazarel_release;
957
958 if (g_log_level_rec.statement_level) then
959 fa_debug_pkg.add(l_calling_fn, 'at beginning of', 'worker allocation', p_log_level_rec => g_log_level_rec);
960 end if;
961
962 x_return_status := 0;
963
964 -- get mass reval info
965 if not get_mass_reval_info (p_mass_reval_id => p_mass_reval_id) then
966 raise massrvl_err;
967 end if;
968
969 if (g_log_level_rec.statement_level) then
970 fa_debug_pkg.add(l_calling_fn, 'inserting initial transactions at', sysdate, p_log_level_rec => g_log_level_rec);
971 end if;
972
973 -- NOTE: on first cursor, we do not want to insert category
974 -- as it's used to indicate asset/category later on
975
976 if (g_log_level_rec.statement_level) then
977 fa_debug_pkg.add(l_calling_fn, 'rows inserted into fa_parallel_workers - asset based', sql%rowcount);
978 end if;
979
980 -- R12 conditional handling
981 if (G_release = 11) then
982 l_min_period_counter := G_period_rec.period_counter - 1;
983 else
984 l_min_period_counter := G_period_rec.period_counter;
985 end if;
986
987 -- asset based
988 insert into fa_parallel_workers
989 (request_id ,
990 asset_id ,
991 asset_number ,
992 asset_type ,
993 -- asset_category_id ,
994 book_type_code ,
995 worker_number ,
996 process_order ,
997 process_status )
998 select p_parent_request_id,
999 ad.asset_id,
1000 ad.asset_number,
1001 ad.asset_type,
1002 -- ad.asset_category_id,
1003 p_book_type_code,
1004 mod(ad.asset_id, p_total_requests) + 1,
1005 1,
1006 'UNPROCESSED'
1007 FROM fa_additions_b ad,
1008 fa_books bk,
1009 fa_deprn_summary ds,
1010 fa_mass_revaluation_rules rr
1011 WHERE rr.mass_reval_id = p_mass_reval_id
1012 AND rr.category_id IS NULL
1013 AND rr.asset_id is not null
1014 AND ad.asset_id = rr.asset_id
1015 AND bk.asset_id = rr.asset_id
1016 AND bk.book_type_code = G_book_type_code
1017 AND bk.transaction_header_id_out IS NULL
1018 AND bk.group_asset_id IS NULL
1019 AND bk.period_counter_fully_retired IS NULL
1020 AND NVL(bk.period_counter_fully_reserved, 99) = NVL(bk.
1021 period_counter_life_complete, 99)
1022 AND bk.conversion_date IS NULL
1023 AND ad.asset_type = DECODE(NVL(rr.revalue_cip_assets_flag,
1024 g_def_revalue_cip_assets_flag), NULL, 'CAPITALIZED', 'N', 'CAPITALIZED',
1025 ad.asset_type)
1026 AND ad.asset_type <> 'GROUP'
1027 AND ds.asset_id = rr.asset_id
1028 AND ds.book_type_code = G_book_type_code
1029 AND ds.deprn_source_code = 'BOOKS'
1030 AND ds.period_counter < l_min_period_counter
1031 AND NOT EXISTS (SELECT 1
1032 FROM fa_books oldbk
1033 WHERE oldbk.asset_id = rr.asset_id
1034 AND oldbk.book_type_code = G_book_type_code
1035 AND oldbk.date_ineffective IS NOT NULL
1036 AND oldbk.group_asset_id IS NOT NULL)
1037 AND NOT EXISTS (SELECT 1
1038 FROM fa_transaction_headers th_rev
1039 WHERE th_rev.asset_id = rr.asset_id
1040 AND th_rev.book_type_code = G_book_type_code
1041 AND th_rev.transaction_type_code = 'REVALUATION'
1042 AND th_rev.mass_transaction_id = p_mass_reval_id);
1043
1044 if (g_log_level_rec.statement_level) then
1045 fa_debug_pkg.add(l_calling_fn, 'rows inserted into fa_parallel_workers2', sql%rowcount);
1046 end if;
1047
1048
1049 -- category based
1050 insert into fa_parallel_workers
1051 (request_id ,
1052 asset_id ,
1053 asset_number ,
1054 asset_type ,
1055 asset_category_id ,
1056 book_type_code ,
1057 worker_number ,
1058 process_order ,
1059 process_status )
1060 select p_parent_request_id,
1061 ad.asset_id,
1062 ad.asset_number,
1063 ad.asset_type,
1064 ad.asset_category_id,
1065 p_book_type_code,
1066 mod(ad.asset_id, p_total_requests) + 1,
1067 1,
1068 'UNPROCESSED'
1069 FROM fa_additions_b ad,
1070 fa_books bk,
1071 fa_deprn_summary ds,
1072 fa_mass_revaluation_rules rr
1073 WHERE rr.mass_reval_id = p_mass_reval_id
1074 AND rr.category_id = ad.asset_category_id
1075 AND rr.category_id IS not NULL
1076 AND rr.asset_id is null
1077 AND bk.asset_id = ad.asset_id
1078 AND bk.book_type_code = G_book_type_code
1079 AND bk.transaction_header_id_out IS NULL
1080 AND bk.group_asset_id IS NULL
1081 AND bk.period_counter_fully_retired IS NULL
1082 AND NVL(bk.period_counter_fully_reserved, 99) =
1083 NVL(bk.period_counter_life_complete, 99)
1084 AND bk.conversion_date IS NULL
1085 AND ad.asset_type = DECODE(NVL(rr.revalue_cip_assets_flag,
1086 g_def_revalue_cip_assets_flag),
1087 NULL, 'CAPITALIZED',
1088 'N', 'CAPITALIZED',
1089 ad.asset_type)
1090 AND ad.asset_type <> 'GROUP'
1091 AND ds.asset_id = ad.asset_id
1092 AND ds.book_type_code = G_book_type_code
1093 AND ds.deprn_source_code = 'BOOKS'
1094 AND ds.period_counter < l_min_period_counter
1095 AND NOT EXISTS (SELECT 1
1096 FROM fa_books oldbk
1097 WHERE oldbk.asset_id = ad.asset_id
1098 AND oldbk.book_type_code = G_book_type_code
1099 AND oldbk.date_ineffective IS NOT NULL
1100 AND oldbk.group_asset_id IS NOT NULL)
1101 AND NOT EXISTS (SELECT 1
1102 FROM fa_transaction_headers th_rev
1103 WHERE th_rev.asset_id = ad.asset_id
1104 AND th_rev.book_type_code = G_book_type_code
1105 AND th_rev.transaction_type_code = 'REVALUATION'
1106 AND th_rev.mass_transaction_id = p_mass_reval_id)
1107 AND NOT EXISTS (SELECT 1
1108 FROM fa_parallel_workers pw
1109 WHERE pw.request_id = p_parent_request_id
1110 AND pw.asset_id = ad.asset_id);
1111
1112 FND_CONCURRENT.AF_COMMIT;
1113
1114 X_return_status := 0;
1115
1116 EXCEPTION
1117 WHEN massrvl_err THEN
1118 FND_CONCURRENT.AF_ROLLBACK;
1119 fa_srvr_msg.add_message (calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1120 X_return_status := 2;
1121
1122 WHEN OTHERS THEN
1123 FND_CONCURRENT.AF_ROLLBACK;
1124 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1125 x_return_status := 2;
1126
1127 END allocate_workers;
1128
1129 ----------------------------------------------------------------
1130
1131
1132 END FA_MASS_REVAL_PKG;
1133