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