[Home] [Help]
PACKAGE BODY: APPS.FA_MASSCP_PKG
Source
1 PACKAGE BODY FA_MASSCP_PKG as
2 /* $Header: FAMCPB.pls 120.68.12020000.3 2012/09/28 16:56:29 spooyath ship $ */
3
4 G_success_count number;
5 G_failure_count number;
6 G_warning_count number;
7 G_fatal_error boolean := FALSE;
8 G_request_id number;
9 G_times_called number := 0;
10
11 g_release number := fa_cache_pkg.fazarel_release;
12
13 g_log_level_rec fa_api_types.log_level_rec_type;
14
15 TYPE num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
16 TYPE date_tbl IS TABLE OF DATE INDEX BY BINARY_INTEGER;
17 TYPE v30_tbl IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
18
19 -- following variables added to prevent transactions from copying
20 -- if a prior one failed
21
22 g_asset_error1_tbl num_tbl; -- incremental used for bulk insert
23 g_asset_error2_tbl num_tbl; -- indexes by asset id used for exists checks
24
25 PROCEDURE do_mass_copy (
26 p_book_type_code IN VARCHAR2,
27 p_period_name IN VARCHAR2,
28 p_period_counter IN NUMBER,
29 p_mode IN NUMBER,
30 p_loop_count IN NUMBER,
31 p_parent_request_id IN NUMBER,
32 p_total_requests IN NUMBER,
33 p_request_number IN NUMBER,
34 x_success_count OUT NOCOPY number,
35 x_warning_count OUT NOCOPY number,
36 x_failure_count OUT NOCOPY number,
37 x_return_status OUT NOCOPY number) IS
38
39 -- used for bulk fetching
40 l_batch_size number;
41 l_loop_count number := 0;
42
43 -- misc
44 l_calling_fn varchar2(40) := 'fa_masscp_pkg.do_mass_copy';
45
46 -- used for error counts etc
47
48 rbs_name VARCHAR2(30);
49 sql_stmt VARCHAR2(100);
50
51 l_return_status VARCHAR2(1);
52 l_msg_count NUMBER;
53 l_msg_data VARCHAR2(4000);
54
55 -- used for trx info
56 l_rowid v30_tbl;
57 l_asset_id num_tbl;
58 l_asset_number v30_tbl;
59 l_asset_type v30_tbl;
60 l_transaction_type_code v30_tbl;
61 l_corp_thid num_tbl;
62 l_tax_thid num_tbl;
63 l_asset_id_fail num_tbl;
64
65 l_cip_in_tax_add number;
66 l_string varchar2(250);
67 l_orig_src_trx_header_id number;
68
69 fa_asset_id_fail_tab fa_num15_tbl_type;
70 l_process_status v30_tbl;
71
72 l_prior_thid num_tbl;
73
74 done_exc EXCEPTION;
75 masscp_err EXCEPTION;
76 error_found_trx EXCEPTION;
77 error_found_fatal_trx EXCEPTION;
78
79
80 -- This cursor now drives off the temp table loaded in allocate workers
81 -- for parallelization / allocation
82 cursor c_trx (p_parent_request_id number,
83 p_request_number number,
84 p_process_order number) is
85 select fpw.rowid,
86 fpw.asset_id,
87 fpw.asset_number,
88 fpw.asset_type,
89 fpw.transaction_type_code,
90 fpw.corp_transaction_header_id,
91 fpw.tax_transaction_header_id,
92 af.asset_id same_asset_id_fail
93 from fa_parallel_workers fpw,
94 fa_asset_failures_gt af
95 where fpw.request_id = p_parent_request_id
96 and fpw.process_status = 'UNASSIGNED'
97 and fpw.worker_number = p_request_number
98 and fpw.process_order = p_process_order
99 and af.asset_id(+) = fpw.asset_id
100 order by fpw.corp_transaction_header_id;
101
102 -- BUG# 5128900
103 -- finds all adjustments in period of addition in between
104 -- last copied trx and the current addition being copied
105 cursor c_prior_adjs (p_asset_id number,
106 p_corp_book varchar2,
107 p_start_thid number,
108 p_end_thid number) is
109 select transaction_header_id
110 from fa_transaction_headers
111 where asset_id = p_asset_id
112 and book_type_code = p_corp_book
113 and transaction_type_code = 'ADDITION/VOID'
114 and transaction_header_id > p_start_thid
115 and transaction_header_id < p_end_thid
116 order by transaction_header_id;
117
118 -- Bug 5864939
119 cursor c_last_copied_trx (p_asset_id number) is
120 select source_transaction_header_id
121 from fa_transaction_headers
122 where book_type_code = p_book_type_code
123 and asset_id = p_asset_id
124 and source_transaction_header_id is not null
125 order by transaction_header_id desc;
126
127 BEGIN
128
129 G_request_id := p_parent_request_id;
130 G_times_called := G_times_called + 1;
131 g_asset_error1_tbl.delete;
132 g_asset_error2_tbl.delete;
133
134 x_success_count := 0;
135 x_failure_count := 0;
136 x_warning_count := 0;
137
138 G_success_count := 0;
139 G_failure_count := 0;
140 G_warning_count := 0;
141
142 if (not g_log_level_rec.initialized) then
143 if (NOT fa_util_pub.get_log_level_rec (
144 x_log_level_rec => g_log_level_rec
145 )) then
146 raise masscp_err;
147 end if;
148 end if;
149
150 g_release := fa_cache_pkg.fazarel_release;
151
152 -- get book information
153 if not fa_cache_pkg.fazcbc(X_book => p_book_type_code, p_log_level_rec => g_log_level_rec) then
154 raise masscp_err;
155 end if;
156
157 if (g_times_called = 1) then
158
159 if (fa_cache_pkg.fazcbc_record.copy_additions_flag <> 'YES') then
160 fnd_message.set_name('OFA', 'FA_MCP_SHARED_NO_COPY');
161 fnd_message.set_token('TYPE','ADDITIONS',FALSE);
162 l_string := fnd_message.get;
163 FND_FILE.put(FND_FILE.output,l_string);
164 FND_FILE.new_line(FND_FILE.output,1);
165 end if;
166
167 if (fa_cache_pkg.fazcbc_record.copy_adjustments_flag <> 'YES') then
168 fnd_message.set_name('OFA', 'FA_MCP_SHARED_NO_COPY');
169 fnd_message.set_token('TYPE','ADJUSTMENTS',FALSE);
170 l_string := fnd_message.get;
171 FND_FILE.put(FND_FILE.output,l_string);
172 FND_FILE.new_line(FND_FILE.output,1);
173 end if;
174
175 if (fa_cache_pkg.fazcbc_record.copy_retirements_flag <> 'YES') then
176 fnd_message.set_name('OFA', 'FA_MCP_SHARED_NO_COPY');
177 fnd_message.set_token('TYPE','RETIREMENTS',FALSE);
178 l_string := fnd_message.get;
179 FND_FILE.put(FND_FILE.output,l_string);
180 FND_FILE.new_line(FND_FILE.output,1);
181 end if;
182
183 if (nvl(fa_cache_pkg.fazcbc_record.allow_group_deprn_flag, 'N') = 'Y' and
184 nvl(fa_cache_pkg.fazcbc_record.copy_group_addition_flag, 'N') <> 'Y') then
185 fnd_message.set_name('OFA', 'FA_MCP_SHARED_NO_COPY');
186 fnd_message.set_token('TYPE','GROUP ADDITIONS',FALSE);
187 l_string := fnd_message.get;
188 FND_FILE.put(FND_FILE.output,l_string);
189 FND_FILE.new_line(FND_FILE.output,1);
190 end if;
191
192 FND_FILE.put(FND_FILE.output,'');
193 FND_FILE.new_line(FND_FILE.output,1);
194
195 -- dump out the headings
196 fnd_message.set_name('OFA', 'FA_MCP_REPORT_COLUMN');
197 l_string := fnd_message.get;
198
199 FND_FILE.put(FND_FILE.output,l_string);
200 FND_FILE.new_line(FND_FILE.output,1);
201
202 fnd_message.set_name('OFA', 'FA_MCP_REPORT_LINES');
203 l_string := fnd_message.get;
204
205 FND_FILE.put(FND_FILE.output,l_string);
206 FND_FILE.new_line(FND_FILE.output,1);
207
208 end if;
209
210
211 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
212
213 if(g_log_level_rec.statement_level) then
214 fa_debug_pkg.add(l_calling_fn, 'opening c_trx_parent cursor at', sysdate, p_log_level_rec => g_log_level_rec);
215 end if;
216
217 open c_trx(p_parent_request_id => p_parent_request_id,
218 p_request_number => p_request_number,
219 p_process_order => p_loop_count);
220
221 fetch c_trx bulk collect
222 into l_rowid,
223 l_asset_id,
224 l_asset_number,
225 l_asset_type,
226 l_transaction_type_code,
227 l_corp_thid,
228 l_tax_thid,
229 l_asset_id_fail
230 limit l_batch_size;
231
232 close c_trx;
233
234 if (g_log_level_rec.statement_level) then
235 fa_debug_pkg.add('test',
236 'after fetch thid count is',
237 l_corp_thid.count, p_log_level_rec => g_log_level_rec);
238 end if;
239
240
241 -- exit the bulk fetch loop when no more rows are retrived
242 if l_corp_thid.count = 0 then
243 raise done_exc;
244 end if;
245
246 -- dump any debug messages from above
247 if (g_log_level_rec.statement_level) then
248 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
249 end if;
250
251 for l_loop_count in 1..l_corp_thid.count loop
252
253 -- clear the debug stack for each asset
254 FA_DEBUG_PKG.Initialize;
255 -- reset the message level to prevent bogus errors
256 FA_SRVR_MSG.Set_Message_Level(message_level => 10, p_log_level_rec => g_log_level_rec);
257
258 BEGIN
259
260
261 -- display the asset/thid in the log for matching back to report
262 fa_srvr_msg.add_message
263 (calling_fn => null,
264 name => 'FA_SHARED_ASSET_NUMBER',
265 token1 => 'NUMBER',
266 value1 => l_asset_number(l_loop_count),
267 p_log_level_rec => g_log_level_rec);
268
269 fa_srvr_msg.add_message
270 (calling_fn => null,
271 name => 'FA_MCP_SHARED_FAILED_THID',
272 token1 => 'SOURCE_THID',
273 value1 => l_corp_thid(l_loop_count),
274 p_log_level_rec => g_log_level_rec);
275
276
277 -- BUG# 2521472
278 -- need to account for cip-in-tax scenario where
279 -- the asset was capitalized and adjusted in the
280 -- period of addition
281 --
282 -- CIP ADDITION/VOID -> ADDITION/VOID -> ADDITION
283 --
284 -- in such a case, the addition row needs to be
285 -- processed as an adjustment by mass copy
286
287 -- R12 conditional handling
288 -- removing this logic as part of BUG# 5128900
289 -- as the VOID logic is obsolete for adjustments
290 -- in R12 - an adjustment will always have that trx_type
291
292 -- call the appropriate preocedure for the given transaction
293
294 l_orig_src_trx_header_id := null;
295
296 if (l_transaction_type_code(l_loop_count) = 'ADDITION' and
297 l_tax_thid(l_loop_count) is not null and
298 G_release = 11) then
299
300 if (g_log_level_rec.statement_level) then
301 fa_debug_pkg.add(l_calling_fn,
302 'entering',
303 'logic for ADDITION and tax exists', p_log_level_rec => g_log_level_rec);
304 end if;
305
306 select source_transaction_header_id
307 into l_orig_src_trx_header_id
308 from fa_transaction_headers
309 where transaction_header_id = l_tax_thid(l_loop_count);
310
311 -- Bug 5864939 start
312 -- If l_orig_src_trx_header_id is null populate it with the
313 -- transaction_header_id of the last transaction copied from corp book.
314 if (l_orig_src_trx_header_id is null) then
315
316 open c_last_copied_trx (l_asset_id(l_loop_count));
317
318 fetch c_last_copied_trx
319 into l_orig_src_trx_header_id;
320
321 -- If tax book contains no transaction copied from
322 -- corp book error out.
323 if (c_last_copied_trx%notfound) then
324 close c_last_copied_trx;
325 if (g_log_level_rec.statement_level) then
326 fa_debug_pkg.add(l_calling_fn,
327 'Tax Book contains',
328 'no transaction copied from corp book', p_log_level_rec => g_log_level_rec);
329 end if;
330 raise error_found_trx;
331 end if;
332
333 close c_last_copied_trx;
334 end if;
335 -- Bug 5864939 end
336
337 if (g_log_level_rec.statement_level) then
338 fa_debug_pkg.add(l_calling_fn,
339 'Last copied corp book txn',
340 l_orig_src_trx_header_id, p_log_level_rec => g_log_level_rec);
341 end if;
342
343
344 if (l_orig_src_trx_header_id < l_corp_thid(l_loop_count)) then
345 if (g_log_level_rec.statement_level) then
346 fa_debug_pkg.add(l_calling_fn,
347 'processing ADDITION as ADJUSTMENT using source thid of ',
348 l_orig_src_trx_header_id, p_log_level_rec => g_log_level_rec);
349 end if;
350
351 -- BUG# 5128900
352 -- loop through all VOIDs in between the last copied
353 -- transaction and the ADDITION selected
354 if (g_asset_error2_tbl.exists(l_asset_id(l_loop_count)) or
355 l_asset_id_fail(l_loop_count) is not null ) then
356
357 write_message
358 (p_asset_number => l_asset_number(l_loop_count),
359 p_thid => l_corp_thid(l_loop_count),
360 p_message => 'FA_MCP_PRIOR_TRX_FAILED',
361 p_token => NULL,
362 p_value => NULL,
363 p_mode => 'F');
364
365 raise error_found_fatal_trx;
366 end if;
367
368 if (g_log_level_rec.statement_level) then
369 fa_debug_pkg.add(l_calling_fn,
370 'finding in between trxs ',
371 '', p_log_level_rec => g_log_level_rec);
372 fa_debug_pkg.add(l_calling_fn,
373 'asset id ',
374 l_asset_id(l_loop_count));
375 fa_debug_pkg.add(l_calling_fn,
376 'corp book',
377 fa_cache_pkg.fazcbc_record.distribution_source_book, p_log_level_rec => g_log_level_rec);
378 fa_debug_pkg.add(l_calling_fn,
379 'start thid',
380 l_orig_src_trx_header_id, p_log_level_rec => g_log_level_rec);
381 fa_debug_pkg.add(l_calling_fn,
382 'end thid ',
383 l_corp_thid(l_loop_count));
384
385 end if;
386
387 open c_prior_adjs (p_asset_id => l_asset_id(l_loop_count),
388 p_corp_book => fa_cache_pkg.fazcbc_record.distribution_source_book,
389 p_start_thid => l_orig_src_trx_header_id,
390 p_end_thid => l_corp_thid(l_loop_count));
391
392 fetch c_prior_adjs bulk collect
393 into l_prior_thid;
394
395 close c_prior_adjs;
396
397 if (l_prior_thid.count = 0) then
398 if (g_log_level_rec.statement_level) then
399 fa_debug_pkg.add(l_calling_fn,
400 'no ADDITION/VOIDs found',
401 '', p_log_level_rec => g_log_level_rec);
402 end if;
403 end if;
404
405 for x in 1..l_prior_thid.count loop
406
407 -- Bug 5888273 Start
408 -- Consider each call to mcp_adjustment as a separate
409 -- Transaction (record) and handle the exceptions here itself
410 BEGIN
411 if (g_log_level_rec.statement_level) then
412 fa_debug_pkg.add(l_calling_fn,
413 'in loop',
414 '', p_log_level_rec => g_log_level_rec);
415 fa_debug_pkg.add(l_calling_fn,
416 'calling mcp_adjustment with thid of ',
417 l_prior_thid(x));
418 end if;
419
420 mcp_adjustment (
421 p_corp_thid => l_prior_thid(x),
422 p_asset_id => l_asset_id(l_loop_count),
423 p_asset_number => l_asset_number(l_loop_count),
424 p_tax_book => p_book_type_code,
425 x_return_status => l_return_status);
426
427
428 if (l_return_status = FND_API.G_RET_STS_ERROR) then
429 raise error_found_trx;
430 elsif (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
431 raise error_found_fatal_trx;
432 else
433 -- do not set the status on the dependant trxs here!
434 -- l_process_status(l_loop_count) := 'SUCCESS';
435 null;
436 end if;
437
438 EXCEPTION
439
440 WHEN error_found_trx THEN
441 FND_CONCURRENT.AF_ROLLBACK;
442
443 l_process_status(l_loop_count) := 'WARNING';
444 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
445
446 WHEN error_found_fatal_trx THEN
447 FND_CONCURRENT.AF_ROLLBACK;
448
449 l_process_status(l_loop_count) := 'FAILURE';
450 g_asset_error1_tbl(g_asset_error1_tbl.count + 1) := l_asset_id(l_loop_count);
451 g_asset_error2_tbl(l_asset_id(l_loop_count)) := l_asset_id(l_loop_count);
452
453 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
454
455 WHEN OTHERS THEN
456 FND_CONCURRENT.AF_ROLLBACK;
457
458 l_process_status(l_loop_count) := 'FAILURE';
459 g_asset_error1_tbl(g_asset_error1_tbl.count + 1) := l_asset_id(l_loop_count);
460 g_asset_error2_tbl(l_asset_id(l_loop_count)) := l_asset_id(l_loop_count);
461
462 g_fatal_error := TRUE;
463 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
464
465 END;
466
467 if (g_log_level_rec.statement_level) then
468 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
469 end if;
470
471 -- FND_CONCURRENT.AF_COMMIT each record
472 FND_CONCURRENT.AF_COMMIT;
473
474 --- Bug 5888273 end
475 end loop;
476
477 -- now continue with the transaction in question
478 l_transaction_type_code(l_loop_count) := 'ADJUSTMENT';
479 end if;
480
481 end if;
482
483 -- call the appropriate preocedure for the given transaction
484
485 if (l_transaction_type_code(l_loop_count) = 'PARTIAL RETIREMENT' or
486 l_transaction_type_code(l_loop_count) = 'FULL RETIREMENT' or
487 l_transaction_type_code(l_loop_count) = 'REINSTATEMENT' ) then
488
489 if (g_asset_error2_tbl.exists(l_asset_id(l_loop_count)) or
490 l_asset_id_fail(l_loop_count) is not null ) then
491
492 write_message
493 (p_asset_number => l_asset_number(l_loop_count),
494 p_thid => l_corp_thid(l_loop_count),
495 p_message => 'FA_MCP_PRIOR_TRX_FAILED',
496 p_token => NULL,
497 p_value => NULL,
498 p_mode => 'F');
499
500 raise error_found_fatal_trx;
501 end if;
502
503 mcp_retirement (
504 p_corp_thid => l_corp_thid(l_loop_count),
505 p_asset_id => l_asset_id(l_loop_count),
506 p_asset_number => l_asset_number(l_loop_count),
507 p_tax_book => p_book_type_code,
508 x_return_status => l_return_status);
509
510 elsif (l_transaction_type_code(l_loop_count) = 'ADDITION' or
511 l_transaction_type_code(l_loop_count) = 'GROUP ADDITION') then
512
513 -- note we could check for parent exist here,
514 -- but this shoudl be a rare case, so we allow
515 -- api to trap it and report fatal error instead
516
517 mcp_addition (
518 p_corp_thid => l_corp_thid(l_loop_count),
519 p_asset_id => l_asset_id(l_loop_count),
520 p_asset_number => l_asset_number(l_loop_count),
521 p_tax_book => p_book_type_code,
522 p_asset_type => l_asset_type(l_loop_count),
523 x_return_status => l_return_status);
524
525 elsif (l_transaction_type_code(l_loop_count) = 'ADJUSTMENT') then -- adjustment
526
527 if (g_asset_error2_tbl.exists(l_asset_id(l_loop_count)) or
528 l_asset_id_fail(l_loop_count) is not null ) then
529
530 write_message
531 (p_asset_number => l_asset_number(l_loop_count),
532 p_thid => l_corp_thid(l_loop_count),
533 p_message => 'FA_MCP_PRIOR_TRX_FAILED',
534 p_token => NULL,
535 p_value => NULL,
536 p_mode => 'F');
537
538 raise error_found_fatal_trx;
539 end if;
540
541 mcp_adjustment (
542 p_corp_thid => l_corp_thid(l_loop_count),
543 p_asset_id => l_asset_id(l_loop_count),
544 p_asset_number => l_asset_number(l_loop_count),
545 p_tax_book => p_book_type_code,
546 x_return_status => l_return_status);
547 else
548 raise error_found_trx;
549 end if;
550
551 if (l_return_status = FND_API.G_RET_STS_ERROR) then
552 raise error_found_trx;
553 elsif (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
554 raise error_found_fatal_trx;
555 else
556 l_process_status(l_loop_count) := 'SUCCESS';
557 end if;
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 -- do not set the fatal error flag here!
565 WHEN error_found_trx THEN
566 FND_CONCURRENT.AF_ROLLBACK;
567
568 l_process_status(l_loop_count) := 'WARNING';
569 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
570 if (g_log_level_rec.statement_level) then
571 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
572 end if;
573
574 WHEN error_found_fatal_trx THEN
575 FND_CONCURRENT.AF_ROLLBACK;
576
577 l_process_status(l_loop_count) := 'FAILURE';
578 g_asset_error1_tbl(g_asset_error1_tbl.count + 1) := l_asset_id(l_loop_count);
579 g_asset_error2_tbl(l_asset_id(l_loop_count)) := l_asset_id(l_loop_count);
580
581 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
582 if (g_log_level_rec.statement_level) then
583 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
584 end if;
585
586 WHEN OTHERS THEN
587 FND_CONCURRENT.AF_ROLLBACK;
588
589 l_process_status(l_loop_count) := 'FAILURE';
590 g_asset_error1_tbl(g_asset_error1_tbl.count + 1) := l_asset_id(l_loop_count);
591 g_asset_error2_tbl(l_asset_id(l_loop_count)) := l_asset_id(l_loop_count);
592
593 g_fatal_error := TRUE;
594 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
595 if (g_log_level_rec.statement_level) then
596 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
597 end if;
598
599 END; -- asset level block
600
601 -- FND_CONCURRENT.AF_COMMIT each record
602 FND_CONCURRENT.AF_COMMIT;
603
604 end loop; -- array loop
605
606 -- now flags the rows process status accordingly
607 forall i in 1..l_rowid.count
608 update fa_parallel_workers mct
609 set process_status = l_process_status(i)
610 where rowid = l_rowid(i);
611
612 if (g_log_level_rec.statement_level) then
613 fa_debug_pkg.add(l_calling_fn, 'rows updated in fa_parallel_workersfor status', sql%rowcount);
614 end if;
615
616 FND_CONCURRENT.AF_COMMIT;
617
618
619
620 -- now insert all failures into the error table for subsequent loops
621
622 fa_asset_id_fail_tab := fa_num15_tbl_type();
623
624 for i in 1..g_asset_error1_tbl.count loop
625
626 fa_asset_id_fail_tab.EXTEND;
627 fa_asset_id_fail_tab(fa_asset_id_fail_tab.last) := g_asset_error1_tbl(i);
628
629 end loop;
630
631 -- since it's possible the same asset could be picked up in multiple
632 -- loops, we are using minus here to insure we don't raise ora-1
633
634 insert into fa_asset_failures_gt (asset_id)
635 select distinct column_value
636 from TABLE(CAST(fa_asset_id_fail_tab AS fa_num15_tbl_type)) trx
637 minus
638 select asset_id
639 from fa_asset_failures_gt;
640
641 if (g_log_level_rec.statement_level) then
642 fa_debug_pkg.add(l_calling_fn, 'rows inserted into fa_asset_failures', g_asset_error1_tbl.count, p_log_level_rec => g_log_level_rec);
643 end if;
644
645
646 x_success_count := G_success_count;
647 x_warning_count := G_warning_count;
648 x_failure_count := G_failure_count;
649
650 x_return_status := 0;
651
652 EXCEPTION
653 when done_exc then
654 FND_CONCURRENT.AF_ROLLBACK;
655
656 x_success_count := G_success_count;
657 x_warning_count := G_warning_count;
658 x_failure_count := G_failure_count;
659
660 x_return_status := 0;
661
662 when masscp_err then
663 FND_CONCURRENT.AF_ROLLBACK;
664
665 x_success_count := G_success_count;
666 x_warning_count := G_warning_count;
667 x_failure_count := G_failure_count;
668
669 fa_srvr_msg.add_message (calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
670 x_return_status := 2;
671
672 when others then
673 FND_CONCURRENT.AF_ROLLBACK;
674
675 x_success_count := G_success_count;
676 x_warning_count := G_warning_count;
677 x_failure_count := G_failure_count;
678
679 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
680 x_return_status := 2;
681
682 END do_mass_copy;
683
684 ----------------------------------------------------------------
685
686 procedure mcp_addition
687 (p_corp_thid IN NUMBER,
688 p_asset_id IN NUMBER,
689 p_asset_number IN VARCHAR2,
690 p_tax_book IN VARCHAR2,
691 p_asset_type IN VARCHAR2,
692 x_return_status OUT NOCOPY VARCHAR2) IS
693
694 -- local variables
695 l_count NUMBER;
696 l_valid BOOLEAN;
697 l_category_id number;
698
699 -- used for api call
700 l_api_version NUMBER := 1.0;
701 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
702 l_commit VARCHAR2(1) := FND_API.G_FALSE;
703 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
704 l_return_status VARCHAR2(1);
705 l_mesg_count number;
706 l_mesg VARCHAR2(4000);
707
708 -- local messaging
709 l_mesg_name VARCHAR2(30);
710 l_token varchar2(40);
711 l_value varchar2(40);
712 l_calling_fn VARCHAR2(30) := 'fa_masscp_pkg.mcp_addition';
713
714 l_trans_rec FA_API_TYPES.trans_rec_type;
715 l_dist_trans_rec FA_API_TYPES.trans_rec_type;
716 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
717 l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
718 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
719 l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
720 l_asset_fin_rec FA_API_TYPES.asset_fin_rec_type;
721 l_asset_deprn_rec FA_API_TYPES.asset_deprn_rec_type;
722 l_asset_dist_rec FA_API_TYPES.asset_dist_rec_type;
723 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
724 l_inv_tbl FA_API_TYPES.inv_tbl_type;
725 l_asset_hierarchy_rec FA_API_TYPES.asset_hierarchy_rec_type;
726
727 l_corp_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
728 l_corp_asset_fin_rec FA_API_TYPES.asset_fin_rec_type;
729
730 val_err1 EXCEPTION; -- invalid, non fatal
731 add_err1 EXCEPTION; -- warning
732 add_err2 EXCEPTION; -- fatal
733
734 BEGIN
735
736 if NOT fa_cache_pkg.fazcbc(X_book => p_tax_book, p_log_level_rec => g_log_level_rec) then
737 raise add_err1;
738 end if;
739
740 if (p_asset_type = 'GROUP' and
741 nvl(fa_cache_pkg.fazcbc_record.copy_group_addition_flag, 'N') = 'N') then
742
743 l_mesg_name := 'FA_MCP_SHARED_NO_COPY';
744 l_token := 'TYPE';
745 l_value := 'GROUP ADDITIONS';
746
747 raise add_err1;
748 elsif (p_asset_type <> 'GROUP' and
749 fa_cache_pkg.fazcbc_record.copy_additions_flag = 'NO') then
750
751 l_mesg_name := 'FA_MCP_SHARED_NO_COPY';
752 l_token := 'TYPE';
753 l_value := 'ADDITIONS';
754
755 raise add_err1;
756 end if;
757
758 -- get prorate
759 -- percent salvage stuff (handled in API)
760 -- start logic form fampvt
761
762 BEGIN
763
764 -- cat not in tax (handled in api but placing here to avoid fatal error)
765 select asset_category_id
766 into l_category_id
767 from fa_additions_b
768 where asset_id = p_asset_id;
769
770 if not fa_cache_pkg.fazccb (
771 X_Book => p_tax_book,
772 X_Cat_Id => l_category_id
773 , p_log_level_rec => g_log_level_rec) then
774 l_mesg_name := 'FA_MCP_CAT_NOT_IN_TAX';
775 raise val_err1;
776 end if;
777
778 -- cursor to check asset added flags etc used below
779 -- retire pending - invalid for additions
780 -- pending unit adj - obsolete
781
782 -- already exists in tax
783 -- (this is handled in api automatically so not needed
784 -- but including to avoid fatal error)
785
786 if not fa_asset_val_pvt.validate_asset_book
787 (p_transaction_type_code => 'ADDITION',
788 p_book_type_code => p_tax_book,
789 p_asset_id => p_asset_id,
790 p_calling_fn => l_calling_fn
791 , p_log_level_rec => g_log_level_rec)then
792 l_mesg_name := 'FA_MCP_ASSET_IN_TAX_ALREADY';
793 raise val_err1;
794 end if;
795
796 -- valid prorate date (API) - intentionally treat as fatal
797
798 -- verify asset was capitalized and retired in same period
799 -- if so, don't copy the addition
800
801 select count(*)
802 into l_count
803 from fa_books corp_bk,
804 fa_deprn_periods dp,
805 fa_transaction_headers corp_th
806 where corp_bk.transaction_header_id_in = corp_th.transaction_header_id
807 and corp_bk.book_type_code = fa_cache_pkg.fazcbc_record.distribution_source_book
808 and corp_bk.asset_id = p_asset_id
809 and corp_bk.book_type_code = dp.book_type_code
810 and corp_bk.period_counter_capitalized = dp.period_counter
811 and corp_th.date_effective between
812 dp.period_open_date and nvl(dp.period_close_date, sysdate)
813 and corp_th.transaction_type_code like '%RETIREMENT';
814
815 if (l_count <> 0 and G_release = 11) then
816 l_mesg_name := 'FA_MCP_CHECK_ASSET_CAP';
817 raise val_err1;
818 end if;
819
820
821 -- Check if PRODUCTION rsr in tax, but not in corp
822 -- handled in the calculation engine already
823 -- note: previously this did not result in fatal error,
824 -- but leaving as is for now.
825
826 l_valid := TRUE;
827
828 EXCEPTION
829 when val_err1 then
830 l_valid := FALSE;
831 when others then
832 l_valid := FALSE;
833 END;
834
835 if (l_valid) then
836
837 -- logic from famppc
838 -- select corp value
839 -- do salvage calc for japan
840
841 -- ceiling stuff (from cbd) should be handled in api
842 -- copy itc from corp???? yikes
843
844 -- end famppc
845
846 -- rounding falg - handled in api
847 -- remaining life for child - api
848 -- Salvage Value Requirement for Japan (handled in api)
849
850 -- short tax values - handled in api
851
852 -- load the structs
853 l_asset_hdr_rec.asset_id := p_asset_id;
854 l_asset_hdr_rec.book_type_code := p_tax_book;
855
856 l_trans_rec.source_transaction_header_id := p_corp_thid;
857 l_trans_rec.calling_interface := 'FAMCP';
858 l_trans_rec.mass_reference_id := G_request_id;
859
860 -- BUG# 2707210
861 -- need to load the values from corp thid otherwise
862 -- if books aren't in sync, the tax addition will always
863 -- get the current corporate cost (even from later periods)
864 -- deriving this here as initialize code in api will just
865 -- get current info
866
867 select decode(p_asset_type,
868 'GROUP', 0,
869 cost),
870 date_placed_in_service,
871 group_asset_id,
872 salvage_type,
873 percent_salvage_value,
874 salvage_value
875 into l_asset_fin_rec.cost,
876 l_asset_fin_rec.date_placed_in_service,
877 l_asset_fin_rec.group_asset_id,
878 l_asset_fin_rec.salvage_type,
879 l_asset_fin_rec.percent_salvage_value,
880 l_asset_fin_rec.salvage_value
881 from fa_books
882 where asset_id = p_asset_id
883 and book_type_code = fa_cache_pkg.fazcbc_record.distribution_source_book
884 and transaction_header_id_in = p_corp_thid;
885
886 -- set the group asset and salvage information according to options
887 -- selected in book controls. last option will force null inside the addition api
888
889 if (nvl(fa_cache_pkg.fazcbc_record.copy_group_assignment_flag, 'N') = 'N') then
890 l_asset_fin_rec.group_asset_id := null;
891 end if;
892
893 if (nvl(fa_cache_pkg.fazcbc_record.copy_salvage_value_flag, 'NO') = 'NO') then
894 l_asset_fin_rec.salvage_type := null;
895 l_asset_fin_rec.percent_salvage_value := null;
896 l_asset_fin_rec.salvage_value := null;
897 end if;
898
899 -- for group assets we will copy the group related flags from the
900 -- corporate book - if we offer category defaulting, we can
901 -- change this
902
903 if (p_asset_type = 'GROUP') then
904 l_corp_asset_hdr_rec.asset_id := p_asset_id;
905 l_corp_asset_hdr_rec.book_type_code := fa_cache_pkg.fazcbc_record.distribution_source_book;
906
907 if not FA_UTIL_PVT.get_asset_fin_rec
908 (p_asset_hdr_rec => l_corp_asset_hdr_rec,
909 px_asset_fin_rec => l_corp_asset_fin_rec,
910 p_transaction_header_id => p_corp_thid,
911 p_mrc_sob_type_code => 'P'
912 , p_log_level_rec => g_log_level_rec) then raise add_err1;
913 end if;
914
915 --HH Validate disabled_flag
916 --We don't want to copy from/to a disabled group.
917 if not FA_ASSET_VAL_PVT.validate_disabled_flag
918 (p_group_asset_id => l_corp_asset_hdr_rec.asset_id,
919 p_book_type_code => l_corp_asset_hdr_rec.book_type_code,
920 p_old_flag => l_corp_asset_fin_rec.disabled_flag,
921 p_new_flag => l_corp_asset_fin_rec.disabled_flag
922 , p_log_level_rec => g_log_level_rec) then
923 l_mesg_name := 'FA_MCP_GRP_DISABLED';
924 raise add_err1;
925 end if; --End HH
926
927 l_asset_fin_rec.cost := 0;
928 --HH add disabled_flag as null
929 l_asset_fin_rec.disabled_flag := NULL;
930
931 if not fa_cache_pkg.fazccbd (X_book => p_tax_book,
932 X_cat_id => l_category_id,
933 X_jdpis => to_number(to_char(l_asset_fin_rec.date_placed_in_service, 'J')),
934 p_log_level_rec => g_log_level_rec) then
935 raise add_err1;
936 end if;
937
938 if fa_cache_pkg.fazccbd_record.recognize_gain_loss is not null or
939 fa_cache_pkg.fazccbd_record.terminal_gain_loss is not null or
940 fa_cache_pkg.fazccbd_record.tracking_method is not null or
941 fa_cache_pkg.fazccbd_record.excess_allocation_option is not null or
942 fa_cache_pkg.fazccbd_record.allocate_to_fully_rsv_flag is not null or
943 fa_cache_pkg.fazccbd_record.Recapture_Reserve_Flag is not null or
944 fa_cache_pkg.fazccbd_record.LIMIT_PROCEEDS_FLAG is not null or
945 fa_cache_pkg.fazccbd_record.member_rollup_flag is not null or
946 fa_cache_pkg.fazccbd_record.depreciation_option is not null then
947
948 l_asset_fin_rec.recognize_gain_loss := fa_cache_pkg.fazccbd_record.recognize_gain_loss;
949 l_asset_fin_rec.recapture_reserve_flag := fa_cache_pkg.fazccbd_record.recapture_reserve_flag;
950 l_asset_fin_rec.limit_proceeds_flag := fa_cache_pkg.fazccbd_record.limit_proceeds_flag;
951 l_asset_fin_rec.terminal_gain_loss := fa_cache_pkg.fazccbd_record.terminal_gain_loss;
952 l_asset_fin_rec.tracking_method := fa_cache_pkg.fazccbd_record.tracking_method;
953 l_asset_fin_rec.allocate_to_fully_rsv_flag := fa_cache_pkg.fazccbd_record.allocate_to_fully_rsv_flag;
954 l_asset_fin_rec.allocate_to_fully_ret_flag := fa_cache_pkg.fazccbd_record.allocate_to_fully_rsv_flag;
955 l_asset_fin_rec.excess_allocation_option := fa_cache_pkg.fazccbd_record.excess_allocation_option;
956 l_asset_fin_rec.depreciation_option := fa_cache_pkg.fazccbd_record.depreciation_option;
957 l_asset_fin_rec.member_rollup_flag := fa_cache_pkg.fazccbd_record.member_rollup_flag;
958 l_asset_fin_rec.exclude_fully_rsv_flag := l_corp_asset_fin_rec.exclude_fully_rsv_flag;
959 else
960 l_asset_fin_rec.recognize_gain_loss := l_corp_asset_fin_rec.recognize_gain_loss;
961 l_asset_fin_rec.recapture_reserve_flag := l_corp_asset_fin_rec.recapture_reserve_flag;
962 l_asset_fin_rec.limit_proceeds_flag := l_corp_asset_fin_rec.limit_proceeds_flag;
963 l_asset_fin_rec.terminal_gain_loss := l_corp_asset_fin_rec.terminal_gain_loss;
964 l_asset_fin_rec.tracking_method := l_corp_asset_fin_rec.tracking_method;
965 l_asset_fin_rec.allocate_to_fully_rsv_flag := l_corp_asset_fin_rec.allocate_to_fully_rsv_flag;
966 l_asset_fin_rec.allocate_to_fully_ret_flag := l_corp_asset_fin_rec.allocate_to_fully_ret_flag;
967 l_asset_fin_rec.excess_allocation_option := l_corp_asset_fin_rec.excess_allocation_option;
968 l_asset_fin_rec.depreciation_option := l_corp_asset_fin_rec.depreciation_option;
969 l_asset_fin_rec.member_rollup_flag := l_corp_asset_fin_rec.member_rollup_flag;
970 l_asset_fin_rec.exclude_fully_rsv_flag := l_corp_asset_fin_rec.exclude_fully_rsv_flag;
971 end if;
972
973 l_asset_fin_rec.over_depreciate_option := NULL;
974 l_asset_fin_rec.super_group_id := l_corp_asset_fin_rec.super_group_id;
975 l_asset_fin_rec.reduction_rate := l_corp_asset_fin_rec.reduction_rate;
976 l_asset_fin_rec.reduce_addition_flag := l_corp_asset_fin_rec.reduce_addition_flag;
977 l_asset_fin_rec.reduce_adjustment_flag := l_corp_asset_fin_rec.reduce_adjustment_flag;
978 l_asset_fin_rec.reduce_retirement_flag := l_corp_asset_fin_rec.reduce_retirement_flag;
979 end if;
980
981
982 FA_ADDITION_PUB.do_addition
983 (p_api_version => 1.0,
984 p_init_msg_list => FND_API.G_FALSE,
985 p_commit => FND_API.G_FALSE,
986 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
987 x_return_status => l_return_status,
988 x_msg_count => l_mesg_count,
989 x_msg_data => l_mesg,
990 p_calling_fn => null,
991 px_trans_rec => l_trans_rec,
992 px_dist_trans_rec => l_dist_trans_rec,
993 px_asset_hdr_rec => l_asset_hdr_rec,
994 px_asset_desc_rec => l_asset_desc_rec,
995 px_asset_type_rec => l_asset_type_rec,
996 px_asset_cat_rec => l_asset_cat_rec,
997 px_asset_hierarchy_rec => l_asset_hierarchy_rec,
998 px_asset_fin_rec => l_asset_fin_rec,
999 px_asset_deprn_rec => l_asset_deprn_rec,
1000 px_asset_dist_tbl => l_asset_dist_tbl,
1001 px_inv_tbl => l_inv_tbl
1002 );
1003
1004 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1005 l_mesg_name := 'FA_MCP_FAIL_THID';
1006 raise add_err2;
1007 end if;
1008 else --invalid
1009 raise add_err1;
1010 end if; -- if valid
1011
1012
1013 -- dump success to log
1014 if (p_asset_type = 'GROUP') then
1015 l_mesg_name := 'FA_MCP_GRP_ADDITION_SUCCESS';
1016 else
1017 l_mesg_name := 'FA_MCP_ADDITION_SUCCESS';
1018 end if;
1019
1020 write_message
1021 (p_asset_number => p_asset_number,
1022 p_thid => p_corp_thid,
1023 p_message => l_mesg_name,
1024 p_token => l_token,
1025 p_value => l_value,
1026 p_mode => 'S');
1027
1028 X_return_status := FND_API.G_RET_STS_SUCCESS;
1029
1030 EXCEPTION
1031 when add_err1 then
1032 -- non-fatal
1033 write_message
1034 (p_asset_number => p_asset_number,
1035 p_thid => p_corp_thid,
1036 p_message => l_mesg_name,
1037 p_token => l_token,
1038 p_value => l_value,
1039 p_mode => 'W');
1040 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1041 x_return_status := FND_API.G_RET_STS_ERROR;
1042
1043 when add_err2 then
1044 -- fatal
1045 write_message
1046 (p_asset_number => p_asset_number,
1047 p_thid => p_corp_thid,
1048 p_message => l_mesg_name,
1049 p_token => l_token,
1050 p_value => l_value,
1051 p_mode => 'F');
1052 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1053 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1054
1055 when others then
1056 -- fatal
1057 fa_srvr_msg.add_sql_error(calling_fn => null, p_log_level_rec => g_log_level_rec);
1058 write_message
1059 (p_asset_number => p_asset_number,
1060 p_thid => p_corp_thid,
1061 p_message => 'FA_MCP_FAIL_THID',
1062 p_token => null,
1063 p_value => null,
1064 p_mode => 'F');
1065 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1066 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1067
1068
1069 END mcp_addition;
1070
1071
1072 ----------------------------------------------------------------
1073
1074 procedure mcp_adjustment
1075 (p_corp_thid IN NUMBER,
1076 p_asset_id IN NUMBER,
1077 p_asset_number IN VARCHAR2,
1078 p_tax_book IN VARCHAR2,
1079 x_return_status OUT NOCOPY VARCHAR2) IS
1080
1081 -- local variables
1082 l_copy_abs_cost_flag varchar2(1);
1083 l_trx_date_entered date;
1084 l_trx_subtype varchar2(9);
1085 l_delta_cost number;
1086 l_delta_salvage_value number;
1087 l_delta_salvage_percent number;
1088 l_change_in_salvage_type boolean;
1089 l_valid_salvage_change boolean;
1090 l_salvage_change boolean;
1091
1092 l_delta_capacity number;
1093 l_cost_sign NUMBER;
1094 l_rec_cost_sign NUMBER;
1095 l_fraction NUMBER;
1096 l_precision NUMBER;
1097 l_percent_salvage NUMBER;
1098 l_tax_new_salvage_type VARCHAR2(30);
1099 l_tax_new_salvage_value NUMBER;
1100 l_tax_new_salvage_percent NUMBER;
1101
1102
1103 -- used for method cache
1104 l_corp_old_rsr VARCHAR2(10);
1105 l_corp_new_rsr VARCHAR2(10);
1106 l_tax_rsr VARCHAR2(10);
1107
1108 l_valid BOOLEAN; -- used for the validation from fapmvt
1109 l_count NUMBER;
1110
1111
1112 -- used for getting current and old values for corp and tax
1113 l_asset_id number;
1114 l_category_id number;
1115 l_parent_asset number;
1116 l_tax_dpis date;
1117 l_corp_old_cost number;
1118 l_corp_old_salvage_type varchar2(30);
1119 l_corp_old_salvage_value number;
1120 l_corp_old_salvage_percent number;
1121 l_corp_old_capacity number;
1122 l_corp_old_unrevalued_cost number;
1123 l_corp_new_cost number;
1124 l_corp_new_salvage_type varchar2(30);
1125 l_corp_new_salvage_value number;
1126 l_corp_new_salvage_percent number;
1127 l_corp_new_capacity number;
1128 l_corp_new_unrevalued_cost number;
1129 l_corp_old_deprn_method_code varchar2(15);
1130 l_corp_new_deprn_method_code varchar2(15);
1131 l_corp_old_life number;
1132 l_corp_new_life number;
1133
1134 -- new group code
1135 l_corp_old_group_asset_id number;
1136 l_corp_new_group_asset_id number;
1137
1138 l_tax_cost number;
1139 l_tax_salvage_type varchar2(30);
1140 l_tax_salvage_value number;
1141 l_tax_salvage_percent number;
1142 l_tax_capacity number;
1143 l_tax_unrevalued_cost number;
1144 l_tax_deprn_method_code varchar2(15);
1145 l_tax_life number;
1146 l_tax_life_complete number;
1147 l_tax_group_asset_id number;
1148
1149 -- variables and structs used for api call
1150 l_api_version NUMBER := 1.0;
1151 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
1152 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1153 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
1154 l_return_status VARCHAR2(1);
1155 l_mesg_count number;
1156 l_mesg VARCHAR2(4000);
1157
1158 -- local messaging
1159 l_mesg_name VARCHAR2(30);
1160 l_token varchar2(40);
1161 l_value varchar2(40);
1162 l_calling_fn VARCHAR2(30) := 'fa_masscp_pkg.mcp_adjustment';
1163
1164 l_trans_rec FA_API_TYPES.trans_rec_type;
1165 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
1166 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
1167 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
1168 l_asset_fin_mrc_tbl_new FA_API_TYPES.asset_fin_tbl_type;
1169 l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type;
1170 l_inv_tbl FA_API_TYPES.inv_tbl_type;
1171 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
1172 l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
1173 l_asset_deprn_mrc_tbl_new FA_API_TYPES.asset_deprn_tbl_type;
1174 l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type;
1175 l_group_change boolean;
1176 l_group_reclass_type FA_TRX_REFERENCES.src_transaction_subtype%type;
1177
1178
1179 val_err1 EXCEPTION; -- invalid
1180 val_err2 EXCEPTION; -- invalid but ok for capacity
1181
1182 adj_err1 EXCEPTION; -- warning
1183 adj_err2
1184 EXCEPTION; -- fatal
1185 --Bug6332519
1186 l_amortization_start_date date;
1187 --Added corp_th.amortization_start_date to the following cursor
1188 CURSOR c_adjustment IS
1189 select corp_th.asset_id,
1190 ad.asset_category_id,
1191 corp_th.transaction_date_entered,
1192 corp_th.amortization_start_date,
1193 nvl(corp_th.transaction_subtype, 'EXPENSED'),
1194 tax_bk.date_placed_in_service, -- changed as shouldn't this be tax for ccbd cache
1195 nvl(ad.parent_asset_id, -1),
1196 corp_bk_old.cost,
1197 corp_bk_old.salvage_type,
1198 corp_bk_old.salvage_value,
1199 corp_bk_old.percent_salvage_value,
1200 nvl(corp_bk_old.production_capacity, 0),
1201 corp_bk_old.unrevalued_cost,
1202 corp_bk_new.cost,
1203 corp_bk_new.salvage_type,
1204 corp_bk_new.salvage_value,
1205 corp_bk_new.percent_salvage_value,
1206 nvl(corp_bk_new.production_capacity, 0),
1207 corp_bk_new.unrevalued_cost,
1208 corp_bk_old.deprn_method_code,
1209 corp_bk_new.deprn_method_code,
1210 corp_bk_old.life_in_months,
1211 corp_bk_new.life_in_months,
1212 corp_bk_old.group_asset_id,
1213 corp_bk_new.group_asset_id,
1214 tax_bk.cost,
1215 tax_bk.salvage_type,
1216 tax_bk.salvage_value,
1217 tax_bk.percent_salvage_value,
1218 nvl(tax_bk.production_capacity, 0),
1219 tax_bk.unrevalued_cost,
1220 tax_bk.deprn_method_code,
1221 tax_bk.life_in_months,
1222 decode(tax_bk.period_counter_fully_reserved,null,
1223 (nvl(tax_bk.period_counter_life_complete,0)), 0),
1224 tax_bk.group_asset_id
1225 from fa_asset_history ah,
1226 fa_transaction_headers corp_th,
1227 fa_additions_b ad,
1228 fa_books corp_bk_new,
1229 fa_books corp_bk_old,
1230 fa_books tax_bk
1231 where corp_th.transaction_header_id = p_corp_thid
1232 and corp_th.asset_id = ah.asset_id
1233 and ah.date_ineffective is null
1234 and ah.asset_type = 'CAPITALIZED'
1235 and ad.asset_id = corp_th.asset_id
1236 and corp_bk_new.transaction_header_id_in = p_corp_thid
1237 and corp_bk_old.transaction_header_id_out = p_corp_thid
1238 and tax_bk.asset_id = corp_th.asset_id
1239 and tax_bk.book_type_code = p_tax_book
1240 and tax_bk.date_ineffective is null;
1241
1242 cursor c_trx_subtype is
1243 select ref.src_transaction_subtype
1244 from FA_TRX_REFERENCES ref, fa_transaction_headers th
1245 where th.transaction_header_id = p_corp_thid
1246 and ref.trx_reference_id = th.trx_reference_id;
1247
1248 BEGIN
1249
1250 if NOT fa_cache_pkg.fazcbc(X_book => p_tax_book, p_log_level_rec => g_log_level_rec) then
1251 raise adj_err1;
1252 end if;
1253
1254 -- get the copy absolute cost profile option
1255 fnd_profile.get('FA_MCP_ALL_COST_ADJ', l_copy_abs_cost_flag);
1256 --Bug6332519
1257 -- Added l_amortization_start_date
1258 open c_adjustment;
1259 fetch c_adjustment
1260 into l_asset_id,
1261 l_category_id,
1262 l_trx_date_entered,
1263 l_amortization_start_date,
1264 l_trx_subtype,
1265 l_tax_dpis,
1266 l_parent_asset,
1267 l_corp_old_cost,
1268 l_corp_old_salvage_type,
1269 l_corp_old_salvage_value,
1270 l_corp_old_salvage_percent,
1271 l_corp_old_capacity,
1272 l_corp_old_unrevalued_cost,
1273 l_corp_new_cost,
1274 l_corp_new_salvage_type,
1275 l_corp_new_salvage_value,
1276 l_corp_new_salvage_percent,
1277 l_corp_new_capacity,
1278 l_corp_new_unrevalued_cost,
1279 l_corp_old_deprn_method_code,
1280 l_corp_new_deprn_method_code,
1281 l_corp_old_life,
1282 l_corp_new_life,
1283 l_corp_old_group_asset_id,
1284 l_corp_new_group_asset_id,
1285 l_tax_cost,
1286 l_tax_salvage_type,
1287 l_tax_salvage_value,
1288 l_tax_salvage_percent,
1289 l_tax_capacity,
1290 l_tax_unrevalued_cost,
1291 l_tax_deprn_method_code,
1292 l_tax_life,
1293 l_tax_life_complete,
1294 l_tax_group_asset_id;
1295 if (c_adjustment%notfound) then
1296 close c_adjustment;
1297 l_mesg_name := 'FA_MCP_ASSET_NOT_IN_TAX';
1298 raise adj_err1;
1299 end if;
1300 close c_adjustment;
1301
1302 -- BUG# 2661925
1303 -- need to check if the trx date is in the future
1304 -- to account for various calendars, reject if so
1305 -- this new logic replaces the following fix to redefault date:
1306
1307 -- BUG# 2428815, if transaction date falls in a future period,
1308 -- then reset it to the normal defaulting mechanism using the
1309 -- current period note that the tax period was the last one
1310 -- loaded into the deprn period cache via the call to
1311 -- get_deprn_period above.
1312
1313 if (l_trx_date_entered > fa_cache_pkg.fazcdp_record.calendar_period_close_date) then
1314 l_mesg_name := 'FA_MCP_SHARED_FUTURE_COPY';
1315
1316 raise adj_err1;
1317 end if;
1318
1319 -- set the deltas - salvage derived later for japan requirements
1320
1321 l_delta_cost := l_corp_new_cost -
1322 l_corp_old_cost;
1323 l_delta_capacity := nvl(l_corp_new_capacity,0) -
1324 nvl(l_corp_old_capacity,0);
1325
1326 if (fa_cache_pkg.fazcbc_record.copy_adjustments_flag = 'NO' and
1327 l_delta_capacity = 0) then
1328
1329 l_mesg_name := 'FA_MCP_SHARED_NO_COPY';
1330 l_token := 'TYPE';
1331 l_value := 'ADJUSTMENTS';
1332
1333 raise adj_err1;
1334 end if;
1335
1336
1337 if(l_trx_subtype = 'AMORTIZED' and
1338 fa_cache_pkg.fazcbc_record.amortize_flag = 'NO' and
1339 l_delta_capacity = 0) then
1340
1341 l_mesg_name := 'FA_MCP_SHARED_NO_COPY';
1342 l_token := 'TYPE';
1343 l_value := 'AMORTIZED ADJUSTMENTS';
1344
1345 raise adj_err1;
1346 end if;
1347
1348 -- =====================================================================
1349 -- MacDonald's ER
1350 -- we force the sub type to EXPENSED if it is AMORTIZED and the copy flag is set
1351 -- fa_cache_pkg.fazcbc_record.amortize_flag = 'NO' - should we check for this?
1352 --
1353 if(l_trx_subtype = 'AMORTIZED' and
1354 NVL(fa_cache_pkg.fazcbc_record.copy_amort_adaj_exp_flag,'N') = 'Y' AND
1355 fa_cache_pkg.fazcbc_record.copy_adjustments_flag = 'YES' ) then
1356 l_trx_subtype := 'EXPENSED';
1357 end if;
1358 -- =======================================================================
1359
1360 -- here's where we handle salvage change
1361 -- drastically changed for group
1362 -- further changed for BUG# 4725962
1363
1364 if (l_corp_old_salvage_type <> l_corp_new_salvage_type or
1365 (nvl(l_corp_old_salvage_value, 0) <> nvl(l_corp_new_salvage_value, 0) and
1366 l_corp_old_salvage_type = 'AMT') or
1367 (nvl(l_corp_old_salvage_percent, 0) <> nvl(l_corp_new_salvage_percent, 0) and
1368 l_corp_old_salvage_type = 'PCT')) then
1369
1370 l_salvage_change := TRUE;
1371
1372 if ((l_tax_salvage_type = 'AMT' and
1373 nvl(l_corp_old_salvage_value, 0) <> nvl(l_tax_salvage_value, 0)) or
1374 (l_tax_salvage_type = 'PCT' and
1375 nvl(l_corp_old_salvage_percent, 0) <> nvl(l_tax_salvage_percent, 0)) or
1376 (l_tax_salvage_type <> l_corp_old_salvage_type)) then
1377
1378 l_valid_salvage_change := FALSE;
1379 l_delta_salvage_value := null;
1380 l_delta_salvage_percent := null;
1381 l_tax_new_salvage_type := null;
1382
1383 else
1384
1385 if (l_corp_old_salvage_type <> l_corp_new_salvage_type) then
1386 l_delta_salvage_value := nvl(l_corp_new_salvage_value, 0);
1387 l_delta_salvage_percent := nvl(l_corp_new_salvage_percent, 0);
1388 else
1389 l_delta_salvage_value := nvl(l_corp_new_salvage_value, 0) -
1390 nvl(l_corp_old_salvage_value, 0);
1391
1392 l_delta_salvage_percent := nvl(l_corp_new_salvage_percent, 0) -
1393 nvl(l_corp_old_salvage_percent, 0);
1394 end if;
1395
1396 l_tax_new_salvage_type := l_corp_new_salvage_type;
1397
1398 -- if no effective change, clear the values
1399 if (l_tax_new_salvage_type = 'PCT') then
1400 l_delta_salvage_value := null;
1401 elsif (l_tax_new_salvage_type = 'AMT') then
1402 l_delta_salvage_percent := null;
1403 end if;
1404
1405 l_valid_salvage_change := TRUE;
1406
1407 end if;
1408 else
1409
1410 l_salvage_change := FALSE;
1411 l_valid_salvage_change := FALSE;
1412 l_delta_salvage_value := null;
1413 l_delta_salvage_percent := null;
1414 l_tax_new_salvage_type := null;
1415
1416 end if;
1417 if ( nvl(l_corp_old_group_asset_id,-99) <> nvl(l_corp_new_group_asset_id,-99) ) then
1418 l_group_change := TRUE;
1419 else
1420 l_group_change := FALSE;
1421 end if;
1422
1423
1424 if (g_log_level_rec.statement_level) then
1425 fa_debug_pkg.add(l_calling_fn, 'l_tax_new_salvage_type', l_tax_new_salvage_type, p_log_level_rec => g_log_level_rec);
1426 fa_debug_pkg.add(l_calling_fn, 'l_delta_salvage_value', l_delta_salvage_value, p_log_level_rec => g_log_level_rec);
1427 fa_debug_pkg.add(l_calling_fn, 'l_delta_salvage_percent', l_delta_salvage_percent, p_log_level_rec => g_log_level_rec);
1428 fa_debug_pkg.add(l_calling_fn, 'l_valid_salvage_change', l_valid_salvage_change, p_log_level_rec => g_log_level_rec);
1429 fa_debug_pkg.add(l_calling_fn, 'l_salvage_change', l_salvage_change, p_log_level_rec => g_log_level_rec);
1430 fa_debug_pkg.add(l_calling_fn, 'l_group_change', l_group_change, p_log_level_rec => g_log_level_rec);
1431 end if;
1432
1433
1434
1435 -- most validation is handled in the adjustment api itself
1436 -- including salvage defaulting, etc ** double check **
1437
1438 -- start validation (from fampvt in fampck.lpc)
1439 BEGIN
1440
1441 -- start shared
1442 -- check h_ind, cat not in tax - done in api
1443 -- big select merged into one
1444 -- verify that the assets was not previously amortized - done in api
1445 -- check for pending unit adj - obsolete
1446 -- check for cap in this same period as ret - used only for additions
1447
1448 -- start non addition
1449 -- asset not in tax - handled in above cursor
1450 -- check prorate - not needed handled in api
1451
1452 -- check if trxs follow (old alias th4) - keeping for non-fatal
1453 -- BUG# 3028986
1454 -- removing as there is no reason to prevent overlaps for
1455 -- adjustment transactions: expensed will always use date defaulting
1456 -- logic and amortized can always overlap now with faxaam
1457
1458
1459 -- pending ret/reinstate
1460 -- non reistatement
1461 -- check fully retired in tax (done in api)
1462 -- check if manual retirements were ever entered in tax
1463 select count (*)
1464 into l_count
1465 from fa_transaction_headers th
1466 where th.book_type_code = p_tax_book
1467 and th.asset_id = l_asset_id
1468 and th.transaction_type_code in
1469 ('FULL RETIREMENT', 'PARTIAL RETIREMENT', 'REINSTATEMENT')
1470 and th.source_transaction_header_id is null;
1471
1472 if l_count <> 0 then
1473 l_mesg_name := 'FA_MCP_RET_MANUAL_TAX';
1474 raise val_err1;
1475 end if;
1476
1477 -- end shared
1478 -- begin add only
1479 -- begin adjustment only
1480 -- costs retrieved in select above
1481 -- ALL EXCLUSIONS THAT DO NOT PERMIT CAPACITY ADJUSTMENTS
1482 -- MUST BE CHECKED BEFORE ALLOWING THE CAPACITY ADJUSTMENT
1483 -- EXCEPTIONS (valid = INVALID, capacity_adj_flag = TRUE,
1484 -- which will continue the copy of the capacity ONLY!)
1485
1486 -- check costs
1487
1488 -- Cannot copy salvage value if that is the only change
1489 -- and copy salvage value is not 'YES'
1490
1491 -- Cannot copy SV adjustments where CORP SV before adj <> TAX SV
1492 -- modified for group enhancements to account for changes
1493 -- in type and percentage as well
1494
1495 if (nvl(fa_cache_pkg.fazcbc_record.copy_salvage_value_flag, 'NO') = 'NO') then
1496
1497 if ((l_delta_cost = 0) and
1498 (l_delta_capacity = 0) and
1499 (l_salvage_change)) then
1500 l_mesg_name := 'FA_MCP_SHARED_NO_COPY';
1501 l_token := 'TYPE';
1502 l_value := 'SALVAGE VALUE ADJUSTMENTS';
1503 raise val_err1;
1504 else
1505 -- continue if other portion of adjustment is valid
1506 l_delta_salvage_value := null;
1507 l_delta_salvage_percent := null;
1508 l_tax_new_salvage_type := null;
1509 l_valid_salvage_change := FALSE;
1510 end if;
1511
1512 elsif (l_salvage_change and
1513 not l_valid_salvage_change) then
1514 if((l_delta_cost = 0) and
1515 (l_delta_capacity = 0)) then
1516 l_mesg_name := 'FA_MCP_DIFF_SV_TAX_CORP';
1517 raise val_err1;
1518 else
1519 -- continue if other portion of adjustment is valid
1520 l_delta_salvage_value := null;
1521 l_delta_salvage_percent := null;
1522 l_tax_new_salvage_type := null;
1523 end if;
1524 end if;
1525
1526 -- Cannot do adjustment if asset is beyond useful life
1527 -- (tax's period_counter_life_complete is not null).
1528
1529 if (l_tax_life_complete <> 0) then
1530 l_mesg_name := 'FA_MCP_PAST_USEFUL_LIFE';
1531 raise val_err1;
1532 end if;
1533
1534
1535 -- Cannot copy adjustments where corp method is prod, but tax is not
1536 -- a prod method
1537
1538 -- Get rsr for old corp deprn_method
1539 if not fa_cache_pkg.fazccmt
1540 (X_method => l_corp_old_deprn_method_code,
1541 X_life => l_corp_old_life
1542 , p_log_level_rec => g_log_level_rec) then
1543 l_mesg_name := 'FA_MCP_FAIL_THID';
1544 raise val_err1;
1545 end if;
1546
1547 l_corp_old_rsr := fa_cache_pkg.fazccmt_record.rate_source_rule;
1548
1549 -- Get rsr for new corp deprn_method
1550 if not fa_cache_pkg.fazccmt
1551 (X_method => l_corp_new_deprn_method_code,
1552 X_life => l_corp_new_life
1553 , p_log_level_rec => g_log_level_rec) then
1554 l_mesg_name := 'FA_MCP_FAIL_THID';
1555 raise val_err1;
1556 end if;
1557
1558 l_corp_new_rsr := fa_cache_pkg.fazccmt_record.rate_source_rule;
1559
1560 -- Get rsr for tax.deprn_method
1561 if not fa_cache_pkg.fazccmt
1562 (X_method => l_tax_deprn_method_code,
1563 X_life => l_tax_life
1564 , p_log_level_rec => g_log_level_rec) then
1565 l_mesg_name := 'FA_MCP_FAIL_THID';
1566 raise val_err1;
1567 end if;
1568
1569 l_tax_rsr := fa_cache_pkg.fazccmt_record.rate_source_rule;
1570
1571
1572 -- Cannot copy adjustments that are not cost adjustments,
1573 -- OR salvage value adjustments
1574 -- OR production capacity adjustments
1575
1576 -- Also don't copy method adjustments that result in
1577 -- capacity changes (ie. non-prod to prod method)
1578
1579 if ((l_delta_cost = 0) and
1580 (not l_valid_salvage_change) and
1581 (l_delta_capacity = 0) and
1582 (not l_group_change)) or
1583 ((l_corp_old_rsr <> l_corp_new_rsr) and
1584 (l_delta_capacity <> 0)) then
1585
1586 l_mesg_name := 'FA_MCP_INVALID_ADJ_COPY';
1587 raise val_err1;
1588 end if;
1589
1590 -- Cannot copy prod cap change if TAX asset not production
1591 if ((l_tax_rsr <> l_corp_new_rsr) and
1592 l_delta_capacity <> 0) then
1593 l_mesg_name := 'FA_MCP_CANNOT_ADJ_PC_TAX';
1594 raise val_err1;
1595 end if;
1596
1597 -- Cannot copy adjustments where corp prod cap <> tax prod cap
1598 if (l_corp_old_capacity <> l_tax_capacity) then
1599 l_mesg_name := 'FA_MCP_DIFF_PROD_CAP';
1600 raise val_err1;
1601 end if;
1602
1603 -- TESTS FOR EXCLUSIONS FROM THIS POINT ON CAN ALLOW
1604 -- CAPACITY ADJUSTMENT COPY TO CONTINUE IF THEY FAIL
1605 -- (valid = INVALID, capacity_adj_flag = TRUE)
1606
1607 -- Amortized adjustments are not allowed in TAX book
1608 if (fa_cache_pkg.fazcbc_record.amortize_flag = 'NO' and
1609 l_trx_subtype = 'AMORTIZED') then
1610
1611 l_mesg_name := 'FA_MCP_NO_AMORT_ADJS';
1612 l_trx_subtype := 'EXPENSED';
1613 raise val_err2;
1614 end if;
1615
1616
1617 -- Exp adj is not allowed after Amort adj in TAX BOOK
1618 -- this is handled in api, how about cap - should be able to pu in adj engine ???
1619 if l_trx_subtype = 'EXPENSED' then
1620 if not FA_ASSET_VAL_PVT.validate_exp_after_amort
1621 (p_asset_id => l_asset_id,
1622 p_book => p_tax_book
1623 , p_log_level_rec => g_log_level_rec) then
1624
1625 l_mesg_name := 'FA_MCP_EXPENSE_AFTER_AMORT';
1626 l_trx_subtype := 'AMORTIZED';
1627 raise val_err2;
1628
1629 end if; -- amort exist
1630 end if; -- expensed
1631
1632 -- done capacity adj specific checks
1633 -- checks for negative recoverable cost
1634 -- handled in adjustment api ???if not maybe add it???
1635
1636 l_cost_sign := sign(l_corp_new_cost);
1637
1638
1639 -- modifications to salvage as part of group
1640 -- removing orginal validations on cost sign changes here
1641 -- this will be caught by new flag in book controls and by api in such rare cases
1642
1643 -- only check costs when the absolute profile is not enabled
1644 if (nvl(l_copy_abs_cost_flag, 'N') <> 'Y') then
1645
1646 -- Cannot copy cost adjustments where corp unrev cost <> tax unrev cost
1647 if (l_delta_cost <> 0 and
1648 l_corp_old_unrevalued_cost <> l_tax_unrevalued_cost) then
1649
1650 l_mesg_name := 'FA_MCP_DIFF_UNREV_COST';
1651 raise val_err1;
1652 end if;
1653
1654 -- Cannot copy adjustments that have TAX cost that
1655 -- are different than before-change CORP cost
1656 if (l_corp_old_cost <> l_tax_cost) then
1657 l_mesg_name := 'FA_MCP_DIFF_COST';
1658 raise val_err1;
1659 end if;
1660
1661 end if;
1662
1663 if (l_group_change) then
1664 if (nvl(fa_cache_pkg.fazcbc_record.copy_group_change_flag, 'N') = 'N' ) then
1665 l_mesg_name := 'FA_MCP_SHARED_NO_COPY';
1666 l_token := 'TYPE';
1667 l_value := 'GROUP CHANGE ADJUSTMENTS';
1668 raise val_err1;
1669 end if;
1670 if (nvl(l_corp_old_group_asset_id,-99) <> nvl(l_tax_group_asset_id,-99)) then
1671 l_mesg_name := 'FA_MCP_DIFF_GROUP';
1672 raise val_err1;
1673 end if;
1674
1675 open c_trx_subtype;
1676 fetch c_trx_subtype into l_group_reclass_type;
1677 close c_trx_subtype;
1678 if l_group_reclass_type like '%MANUAL%' then
1679 l_mesg_name := 'FA_MCP_GROUP_ADJ_MANUAL';
1680 raise val_err1;
1681 end if;
1682
1683 end if;
1684
1685
1686 -- check dated adjustment -- should this be handled in the api????
1687 --
1688 -- BUG# 2799286
1689 -- removing overlapping validation - no need to prevent this as the
1690 -- new amort package can handle it and i is too restrictive
1691 -- when trying to copy group reclasses - BMR
1692
1693
1694 EXCEPTION
1695 when val_err1 then
1696 l_valid := FALSE;
1697 l_delta_capacity := 0;
1698
1699 when val_err2 then
1700 l_valid := FALSE;
1701
1702 when others then
1703 l_valid := FALSE;
1704 l_delta_capacity := 0;
1705
1706 END; -- end logic from fampvt
1707
1708
1709 if (not l_valid and l_delta_capacity = 0) then
1710 -- invalid
1711 raise adj_err1;
1712 elsif (not l_valid and l_delta_capacity <> 0) then
1713 -- if capacity adjustment only, don't copy cost
1714 l_delta_cost := 0;
1715 end if;
1716
1717
1718 -- load the info (no longer needed, only deltas)
1719 -- all checks and settings for 0 cost shoudl be handled in api
1720 -- begin calc stuff from fampms.lpc only called for
1721 -- non-capacity adjustment
1722
1723 -- insure prior derived values are cleared for salvage if not valid
1724 if (not l_valid) then
1725 l_delta_salvage_value := null;
1726 l_delta_salvage_percent := null;
1727 l_tax_new_salvage_type := null;
1728 end if; -- end if valid
1729
1730
1731 -- itc - should be handled in api
1732
1733 -- end calcstuff from famppc in fampms.lpc
1734
1735
1736 -- start more from fampaj before insert
1737 -- setting cost / rec/salvage to 0 should be handled in api
1738
1739 -- removed for group
1740 -- Salvage Value Requirement for Japan (SHOULD THIS BE HANDLED IN API?!?!)
1741 -- If valid = FALSE, then this is a special case of copy capacity
1742 -- adjustments, and only capacity should be copied
1743 -- Here is the place rounding up occur
1744
1745
1746 /* removing this as we will not copy reclasses in phase 1 */
1747
1748 -- group change logic
1749 -- group reclasses will be performed when the asset shared the same group
1750 -- association in corp and tax and when there was a change due to this
1751 -- adjustment in corp. Currently any such reclass will be copied using
1752 -- the same amortization start date as in the corporate book ??? VERIFY ???
1753
1754 if (l_group_change) then
1755
1756 -- get the corporate amortization start date for the
1757 -- corporate change and use it here
1758
1759 select m.amortization_start_date
1760 into l_trans_rec.amortization_start_date
1761 from fa_transaction_headers m
1762 where m.transaction_header_id = p_corp_thid;
1763
1764 -- set the group asset id for tax
1765 l_asset_fin_rec_adj.group_asset_id := nvl(l_corp_new_group_asset_id, FND_API.G_MISS_NUM);
1766 l_group_reclass_options_rec.group_reclass_type := 'CALC';
1767 l_group_reclass_options_rec.transfer_flag := 'YES';
1768
1769 end if;
1770
1771
1772 -- validation ok, load the structs and process the adjustment
1773 l_trans_rec.transaction_date_entered := l_trx_date_entered;
1774 --Bug6332519
1775 l_trans_rec.amortization_start_date := l_amortization_start_date;
1776
1777 l_trans_rec.transaction_type_code := 'ADJUSTMENT';
1778 l_trans_rec.transaction_subtype := l_trx_subtype;
1779 l_trans_rec.source_transaction_header_id := p_corp_thid;
1780 l_trans_rec.calling_interface := 'FAMCP';
1781 l_trans_rec.mass_reference_id := G_request_id;
1782
1783 l_asset_hdr_rec.asset_id := l_asset_id;
1784 l_asset_hdr_rec.book_type_code := p_tax_book;
1785 l_asset_fin_rec_adj.cost := l_delta_cost;
1786 l_asset_fin_rec_adj.salvage_type := l_tax_new_salvage_type;
1787 l_asset_fin_rec_adj.salvage_value := l_delta_salvage_value;
1788 l_asset_fin_rec_adj.percent_salvage_value := l_delta_salvage_percent;
1789
1790 l_asset_fin_rec_adj.production_capacity := l_delta_capacity;
1791
1792 if (g_log_level_rec.statement_level) then
1793 fa_debug_pkg.add(l_calling_fn, 'l_asset_fin_rec_adj.salvage_type', l_asset_fin_rec_adj.salvage_type, p_log_level_rec => g_log_level_rec);
1794 fa_debug_pkg.add(l_calling_fn, 'l_asset_fin_rec_adj.salvage_value', l_asset_fin_rec_adj.salvage_value, p_log_level_rec => g_log_level_rec);
1795 fa_debug_pkg.add(l_calling_fn, 'l_asset_fin_rec_adj.percent_salvage_value', l_asset_fin_rec_adj.percent_salvage_value, p_log_level_rec => g_log_level_rec);
1796 end if;
1797
1798 FA_ADJUSTMENT_PUB.do_adjustment
1799 (p_api_version => 1.0,
1800 p_init_msg_list => FND_API.G_FALSE,
1801 p_commit => FND_API.G_FALSE,
1802 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1803 x_return_status => l_return_status,
1804 x_msg_count => l_mesg_count,
1805 x_msg_data => l_mesg,
1806 p_calling_fn => l_calling_fn,
1807 px_trans_rec => l_trans_rec,
1808 px_asset_hdr_rec => l_asset_hdr_rec,
1809 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
1810 x_asset_fin_rec_new => l_asset_fin_rec_new,
1811 x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
1812 px_inv_trans_rec => l_inv_trans_rec,
1813 px_inv_tbl => l_inv_tbl,
1814 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
1815 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
1816 x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
1817 p_group_reclass_options_rec => l_group_reclass_options_rec
1818 );
1819
1820 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1821 l_mesg_name := 'FA_MCP_FAIL_THID';
1822 raise adj_err2;
1823 end if;
1824
1825
1826 -- dump success message
1827 l_mesg_name := 'FA_MCP_ADJUSTMENT_SUCCESS';
1828 write_message
1829 (p_asset_number => p_asset_number,
1830 p_thid => p_corp_thid,
1831 p_message => l_mesg_name,
1832 p_token => l_token,
1833 p_value => l_value,
1834 p_mode => 'S');
1835
1836 X_return_status := FND_API.G_RET_STS_SUCCESS;
1837
1838 EXCEPTION
1839 when adj_err1 then
1840 -- non-fatal
1841 write_message
1842 (p_asset_number => p_asset_number,
1843 p_thid => p_corp_thid,
1844 p_message => l_mesg_name,
1845 p_token => l_token,
1846 p_value => l_value,
1847 p_mode => 'W');
1848
1849 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1850 x_return_status := FND_API.G_RET_STS_ERROR;
1851
1852 when adj_err2 then
1853 -- fatal
1854 write_message
1855 (p_asset_number => p_asset_number,
1856 p_thid => p_corp_thid,
1857 p_message => l_mesg_name,
1858 p_token => l_token,
1859 p_value => l_value,
1860 p_mode => 'F');
1861
1862 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1863 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1864
1865 when others then
1866 -- fatal
1867 fa_srvr_msg.add_sql_error(calling_fn => null, p_log_level_rec => g_log_level_rec);
1868 write_message
1869 (p_asset_number => p_asset_number,
1870 p_thid => p_corp_thid,
1871 p_message => 'FA_MCP_FAIL_THID',
1872 p_token => null,
1873 p_value => null,
1874 p_mode => 'F');
1875
1876 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1877 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1878
1879
1880 END mcp_adjustment;
1881
1882
1883 ----------------------------------------------------------------
1884
1885 procedure mcp_retirement
1886 (p_corp_thid IN NUMBER,
1887 p_asset_id IN NUMBER,
1888 p_asset_number IN VARCHAR2,
1889 p_tax_book IN VARCHAR2,
1890 x_return_status OUT NOCOPY VARCHAR2) IS
1891
1892
1893 -- local variables
1894 l_valid BOOLEAN;
1895 l_count NUMBER;
1896 l_jdpis NUMBER;
1897 l_trx_date_entered DATE;
1898 l_date_effective DATE;
1899 l_transaction_type_code VARCHAR2(30);
1900 l_category_id NUMBER;
1901 l_asset_number VARCHAR2(30);
1902 l_old_corp_cost NUMBER;
1903 l_corp_cost_retired NUMBER;
1904 l_tax_cost NUMBER;
1905 l_tax_dpis DATE;
1906 l_tax_pc_fully_ret NUMBER;
1907 l_period_of_addition VARCHAR2(1);
1908 l_ret_status VARCHAR2(30);
1909 l_tax_cost_retired NUMBER;
1910 l_tax_reinst_thid NUMBER; -- 8364239
1911
1912 l_cost_of_removal number;
1913 l_proceeds_of_sale number;
1914 l_retirement_type_code varchar2(15);
1915 l_itc_recapture_id number(15);
1916 l_reference_num varchar2(15);
1917 l_sold_to varchar2(30);
1918 l_trade_in_asset_id number(15);
1919
1920 -- used for api call
1921 l_api_version NUMBER := 1.0;
1922 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
1923 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1924 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
1925 l_return_status VARCHAR2(1);
1926 l_msg_count number;
1927 l_msg_data VARCHAR2(4000);
1928
1929 -- local messaging
1930 l_mesg_name VARCHAR2(30);
1931 l_token varchar2(40);
1932 l_value varchar2(40);
1933 l_calling_fn VARCHAR2(30) := 'fa_masscp_pkg.mcp_retirement';
1934
1935 l_trans_rec FA_API_TYPES.trans_rec_type;
1936 l_dist_trans_rec FA_API_TYPES.trans_rec_type;
1937 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
1938 l_asset_retire_rec FA_API_TYPES.asset_retire_rec_type;
1939 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
1940 l_subcomp_tbl FA_API_TYPES.subcomp_tbl_type;
1941 l_inv_tbl FA_API_TYPES.inv_tbl_type;
1942
1943 -- exceptions
1944 val_err EXCEPTION;
1945 ret_err1 EXCEPTION;
1946 ret_err2 EXCEPTION;
1947
1948
1949 -- cursors
1950 cursor c_ret_id is
1951 select retirement_id,status
1952 from fa_retirements
1953 where book_type_code = p_tax_book
1954 and asset_id = p_asset_id
1955 order by retirement_id desc;
1956
1957 cursor c_retirement (p_asset_id number,
1958 p_corp_thid number,
1959 p_tax_book varchar2,
1960 p_corp_book varchar2) is
1961 select corp_th.transaction_date_entered,
1962 corp_th.date_effective,
1963 corp_th.transaction_type_code,
1964 ah.category_id,
1965 ad.asset_number,
1966 corp_bk.cost,
1967 corp_rt.cost_retired,
1968 corp_rt.retirement_id,
1969 tax_bk.cost,
1970 tax_bk.date_placed_in_service,
1971 tax_bk.period_counter_fully_retired,
1972 corp_rt.cost_of_removal,
1973 corp_rt.proceeds_of_sale,
1974 corp_rt.retirement_type_code,
1975 corp_rt.itc_recapture_id,
1976 corp_rt.reference_num,
1977 corp_rt.sold_to,
1978 corp_rt.trade_in_asset_id
1979 from fa_transaction_headers corp_th,
1980 fa_books corp_bk,
1981 fa_books tax_bk,
1982 fa_retirements corp_rt,
1983 fa_additions_b ad,
1984 fa_asset_history ah
1985 where corp_th.transaction_header_id = p_corp_thid
1986 and corp_th.asset_id = ah.asset_id
1987 and corp_th.date_effective < nvl(ah.date_ineffective,
1988 sysdate)
1989 and corp_th.date_effective >= ah.date_effective
1990 and corp_th.transaction_header_id = corp_bk.transaction_header_id_out
1991 and corp_th.transaction_header_id = decode(corp_th.transaction_type_code,
1992 'REINSTATEMENT', corp_rt.transaction_header_id_out,
1993 corp_rt.transaction_header_id_in)
1994 and corp_rt.asset_id = p_asset_id
1995 and corp_rt.book_type_code = p_corp_book
1996 and tax_bk.asset_id = p_asset_id
1997 and tax_bk.book_type_code = p_tax_book
1998 and tax_bk.date_ineffective is null
1999 and ah.asset_type = 'CAPITALIZED'
2000 and ad.asset_id = corp_th.asset_id;
2001
2002 BEGIN
2003
2004 if NOT fa_cache_pkg.fazcbc(X_book => p_tax_book, p_log_level_rec => g_log_level_rec) then
2005 raise ret_err1;
2006 end if;
2007
2008 if (fa_cache_pkg.fazcbc_record.copy_retirements_flag = 'NO') then
2009
2010 l_mesg_name := 'FA_MCP_SHARED_NO_COPY';
2011 l_token := 'TYPE';
2012 l_value := 'RETIREMENTS';
2013
2014 raise ret_err1;
2015 end if;
2016
2017 -- verify the asset exists in the book
2018 if (g_log_level_rec.statement_level) then
2019 fa_debug_pkg.add('test',
2020 'getting',
2021 'c_retirement cursor', p_log_level_rec => g_log_level_rec);
2022 end if;
2023
2024 l_trans_rec.source_transaction_header_id := p_corp_thid;
2025 l_trans_rec.calling_interface := 'FAMCP';
2026 l_trans_rec.mass_reference_id := G_request_id;
2027
2028 -- get basic info including corp ret info and tax cost, etc
2029 open c_retirement(p_asset_id => p_asset_id,
2030 p_corp_thid => p_corp_thid,
2031 p_tax_book => p_tax_book,
2032 p_corp_book => fa_cache_pkg.fazcbc_record.distribution_source_book);
2033
2034 fetch c_retirement
2035 into l_trx_date_entered,
2036 l_date_effective,
2037 l_transaction_type_code,
2038 l_category_id,
2039 l_asset_number,
2040 l_old_corp_cost,
2041 l_corp_cost_retired,
2042 l_asset_retire_rec.retirement_id,
2043 l_tax_cost,
2044 l_tax_dpis,
2045 l_tax_pc_fully_ret,
2046 l_cost_of_removal,
2047 l_proceeds_of_sale,
2048 l_retirement_type_code,
2049 l_itc_recapture_id,
2050 l_reference_num,
2051 l_sold_to,
2052 l_trade_in_asset_id;
2053
2054 -- BUG# 2818124
2055 -- do not raise fatal error here...
2056 if c_retirement%NOTFOUND then
2057 close c_retirement;
2058 l_mesg_name := 'FA_MCP_ASSET_NOT_IN_TAX'; -- 'FA_MCP_RET_SELECT_DEFAULTS';
2059 raise ret_err1;
2060 end if;
2061
2062 close c_retirement;
2063
2064 -- BUG# 2661925
2065 -- need to check if the trx date is in the future
2066 -- to account for various calendars, reject if so
2067 -- this new logic replaces the following fix to redefault date:
2068
2069 -- BUG# 2447234, if transaction date falls in a future period,
2070 -- then reset it to the normal defaulting mechanism using the
2071 -- current period note that the tax period was the last one
2072 -- loaded into the deprn period cache via the call to
2073 -- get_deprn_period above.
2074
2075 if (l_trx_date_entered > fa_cache_pkg.fazcdp_record.calendar_period_close_date) then
2076 l_mesg_name := 'FA_MCP_SHARED_FUTURE_COPY';
2077
2078 raise ret_err1;
2079 end if;
2080
2081 if (g_log_level_rec.statement_level) then
2082 fa_debug_pkg.add('test',
2083 'getting',
2084 'category cache', p_log_level_rec => g_log_level_rec);
2085 end if;
2086
2087 -- call the category cache
2088 l_jdpis := to_number(to_char(l_tax_dpis, 'J'));
2089
2090 if not fa_cache_pkg.fazccbd (X_book => p_tax_book,
2091 X_cat_id => l_category_id,
2092 X_jdpis => l_jdpis, p_log_level_rec => g_log_level_rec) then
2093 l_mesg_name := 'FA_MCP_FAIL_THID';
2094 raise ret_err2;
2095 end if;
2096
2097 if (g_log_level_rec.statement_level) then
2098 fa_debug_pkg.add('test',
2099 'doing',
2100 'validation', p_log_level_rec => g_log_level_rec);
2101 end if;
2102
2103 -- validation logic (from fampvt)
2104 BEGIN
2105
2106 -- get the basic asset and transaction info (broken up)
2107 -- asset exists (accounted for above)
2108 -- check for subsequent transactions
2109
2110 -- BugNo:348403, we should copy the prior period retirement
2111 -- tranaction which asset was already reinstated in TAX book
2112
2113 -- BUG# 3126641
2114 -- changing if condition as it was backwards and not fully
2115 -- doing what it was supposed to do (need to make
2116 -- sure that no transaction other then previous rets
2117 -- are impacted
2118
2119 if (l_transaction_type_code = 'PARTIAL RETIREMENT' or
2120 l_transaction_type_code = 'FULL RETIREMENT') then
2121
2122 select count(*)
2123 into l_count
2124 from fa_transaction_headers th,
2125 fa_retirements ret
2126 where th.book_type_code = p_tax_book
2127 and th.asset_id = p_asset_id
2128 and ret.book_type_code(+) = p_tax_book
2129 and ret.asset_id(+) = p_asset_id
2130 and th.transaction_header_id = ret.transaction_header_id_in(+)
2131 and ret.status(+) not in ('REINSTATE', 'DELETED')
2132 and transaction_type_code not in ('ADDITION/VOID', 'CIP ADDITION VOID')
2133 and th.transaction_date_entered > l_trx_date_entered;
2134
2135 else
2136
2137 select count(*)
2138 into l_count
2139 from fa_transaction_headers
2140 where asset_id = p_asset_id
2141 and book_type_code = p_tax_book
2142 and transaction_type_code not in ('ADDITION/VOID', 'CIP ADDITION VOID')
2143 and transaction_date_entered > l_trx_date_entered;
2144
2145 end if;
2146
2147 -- BUG# 3235346
2148 -- need to do this for reinstatement as well
2149
2150 if (l_count > 0) then
2151
2152 -- l_mesg_name := 'FA_SHARED_OTHER_TRX_FOLLOW';
2153 -- raise val_err;
2154 -- BUG# 3092853
2155 -- we need to reset trx date in case adjustments
2156 -- have been copied/entered in the same period
2157 -- where the transaction date was cal_per_close.
2158 -- only do this if date_retired equals falls in
2159 -- the same period as CPD.
2160
2161 if (l_trx_date_entered <= fa_cache_pkg.fazcdp_record.calendar_period_close_date and
2162 l_trx_date_entered >= fa_cache_pkg.fazcdp_record.calendar_period_open_date) then
2163
2164 -- BUG# 4212279
2165 -- don't allow a change where prorate date would change
2166 -- as a result
2167
2168 select count(*)
2169 into l_count
2170 from fa_conventions conv1,
2171 fa_conventions conv2,
2172 fa_calendar_periods cal1,
2173 fa_calendar_periods cal2
2174 where conv1.prorate_convention_code =
2175 fa_cache_pkg.fazccbd_record.retirement_prorate_convention
2176 and conv2.prorate_convention_code =
2177 fa_cache_pkg.fazccbd_record.retirement_prorate_convention
2178 and l_trx_date_entered
2179 between conv1.start_date and conv1.end_date
2180 and fa_cache_pkg.fazcdp_record.calendar_period_close_date
2181 between conv2.start_date and conv2.end_date
2182 and cal1.calendar_type = fa_cache_pkg.fazcbc_record.prorate_calendar
2183 and cal2.calendar_type = fa_cache_pkg.fazcbc_record.prorate_calendar
2184 and conv1.prorate_date between cal1.start_date and cal1.end_date
2185 and conv2.prorate_date between cal2.start_date and cal2.end_date
2186 and cal1.end_date = cal2.end_date;
2187
2188 if (l_count > 0) then
2189 l_trx_date_entered :=
2190 fa_cache_pkg.fazcdp_record.calendar_period_close_date;
2191 end if;
2192
2193 -- if date is not redefaulted, API will trap the
2194 -- overlap and return failure
2195
2196 end if;
2197 end if;
2198
2199 if (g_log_level_rec.statement_level) then
2200 fa_debug_pkg.add('test',
2201 'doing',
2202 'validation part 2', p_log_level_rec => g_log_level_rec);
2203 end if;
2204
2205 -- pending ret reinstate
2206 select count(*)
2207 into l_count
2208 from fa_retirements
2209 where book_type_code = p_tax_book
2210 and asset_id = p_asset_id
2211 and status in ('REINSTATE', 'PENDING');
2212
2213 if (l_count > 0) then
2214 l_mesg_name := 'FA_SHARED_PENDING_RETIREMENT';
2215 raise val_err;
2216 end if;
2217
2218
2219 if (g_log_level_rec.statement_level) then
2220 fa_debug_pkg.add('test',
2221 'doing',
2222 'validation for period of addition', p_log_level_rec => g_log_level_rec);
2223 end if;
2224
2225 -- Check retirements (moved in retirement code below)
2226 -- Check reinstatements (moved into reinstate code below)
2227 -- add this period
2228 if not FA_ASSET_VAL_PVT.validate_period_of_addition
2229 (p_asset_id => p_asset_id,
2230 p_book => p_tax_book,
2231 p_mode => 'ABSOLUTE',
2232 px_period_of_addition => l_period_of_addition, p_log_level_rec => g_log_level_rec) then
2233 l_mesg_name := 'FA_MCP_FAIL_THID';
2234 raise val_err;
2235 end if;
2236
2237 if (l_period_of_addition = 'Y' and
2238 G_release = 11) then
2239 l_mesg_name := 'FA_MCP_ADD_RET_SAME_PERIOD';
2240 raise val_err;
2241 end if;
2242
2243 -- BUG# 6905121 no longer validate period of capitalization
2244 if (g_log_level_rec.statement_level) then
2245 fa_debug_pkg.add('test',
2246 'doing',
2247 'after validating period of addition', p_log_level_rec => g_log_level_rec);
2248 end if;
2249
2250 l_valid := TRUE;
2251
2252
2253 EXCEPTION
2254 when val_err then
2255 l_valid := FALSE;
2256
2257 when others then
2258 l_valid := FALSE;
2259
2260 END;
2261
2262 -- end validation from fampvt
2263
2264 if (l_valid) then
2265
2266 if (g_log_level_rec.statement_level) then
2267 fa_debug_pkg.add('test',
2268 'trx',
2269 'is valid', p_log_level_rec => g_log_level_rec);
2270 end if;
2271
2272 -- code from within the insert ret function (famprt.lpc)
2273 if l_transaction_type_code = 'REINSTATEMENT' then
2274
2275 --Reinitialize l_count as it comes in as 0
2276 l_count := -1;
2277
2278 -- HH: 8364239
2279 BEGIN
2280 select rtx.transaction_header_id_out
2281 into l_tax_reinst_thid
2282 from fa_retirements rt, fa_transaction_headers th,
2283 fa_retirements rtx
2284 where rt.transaction_header_id_out = p_corp_thid
2285 and th.book_type_code = p_tax_book
2286 and th.asset_id = p_asset_id
2287 and th.transaction_type_code
2288 in ('FULL RETIREMENT', 'PARTIAL RETIREMENT')
2289 and th.source_transaction_header_id = rt.transaction_header_id_in
2290 and rtx.book_type_code = th.book_type_code
2291 and rtx.transaction_header_id_in = th.transaction_header_id;
2292 EXCEPTION
2293 WHEN NO_DATA_FOUND THEN
2294 l_count :=0;
2295 END;
2296
2297 if (g_log_level_rec.statement_level) then
2298 fa_debug_pkg.add('Checking reinst.','l_tax_reinst_thid',l_tax_reinst_thid);
2299 fa_debug_pkg.add('Checking reinst.','l_count',l_count);
2300 end if;
2301
2302 -- verify asset has been retired
2303 if (l_count = 0) then
2304 l_mesg_name := 'FA_MCP_REIN_NO_RET';
2305 raise ret_err1;
2306 end if;
2307
2308 -- Check that the asset was not manually reinstated in tax.
2309 if (nvl(l_tax_reinst_thid,0) > 0) then
2310 l_mesg_name := 'FA_MCP_RET_MANUAL_TAX';
2311 raise ret_err1;
2312 end if;
2313 -- eHH: 8364239
2314
2315 -- all we need when calling the api is the retirement_id loaded
2316 -- and nothing else it is all done in the api including asset_hdr
2317 open c_ret_id;
2318 fetch c_ret_id into l_asset_retire_rec.retirement_id,l_ret_status; --bug fix 5743332
2319 if c_ret_id%NOTFOUND then
2320 close c_ret_id;
2321 l_mesg_name := 'FA_MCP_FAIL_GET_FA_RETIRE';
2322 raise ret_err2;
2323 end if;
2324 close c_ret_id;
2325
2326 /* bug fix 5743332
2327 -- Can't reinstate if asset is already reinstated
2328 select status
2329 into l_ret_status
2330 from fa_retirements
2331 where book_type_code = p_tax_book
2332 and asset_id = p_asset_id
2333 and retirement_id = l_asset_retire_rec.retirement_id;
2334 */
2335 if (l_ret_status = 'REINSTATE' or
2336 l_ret_status = 'DELETED') then
2337 l_mesg_name := 'FA_MCP_RET_MANUAL_TAX';
2338 raise ret_err1;
2339 end if;
2340
2341
2342 -- insure we run gainloss
2343 l_asset_retire_rec.calculate_gain_loss := FND_API.G_TRUE;
2344 if (g_log_level_rec.statement_level) then
2345 fa_debug_pkg.add('test',
2346 'calling',
2347 'do reinstatement', p_log_level_rec => g_log_level_rec);
2348 end if;
2349
2350 if (g_log_level_rec.statement_level) then
2351 fa_debug_pkg.add('test',
2352 'calling',
2353 'do retirement', p_log_level_rec => g_log_level_rec);
2354 fa_debug_pkg.add('test',
2355 'souce thid before call',
2356 l_trans_rec.source_transaction_header_id, p_log_level_rec => g_log_level_rec);
2357 end if;
2358
2359 FA_RETIREMENT_PUB.do_reinstatement
2360 (p_api_version => l_api_version,
2361 p_init_msg_list => l_init_msg_list,
2362 p_commit => l_commit,
2363 p_validation_level => l_validation_level,
2364 p_calling_fn => l_calling_fn,
2365 x_return_status => l_return_status,
2366 x_msg_count => l_msg_count,
2367 x_msg_data => l_msg_data,
2368 px_trans_rec => l_trans_rec,
2369 px_asset_hdr_rec => l_asset_hdr_rec,
2370 px_asset_retire_rec => l_asset_retire_rec,
2371 p_asset_dist_tbl => l_asset_dist_tbl,
2372 p_subcomp_tbl => l_subcomp_tbl,
2373 p_inv_tbl => l_inv_tbl
2374 );
2375
2376 else -- full or partial retirement
2377
2378 -- validation from fampvt
2379 -- verify asset is not already fully retired
2380 if (l_tax_pc_fully_ret is not null) then
2381 l_mesg_name := 'FA_MCP_RET_MANUAL_TAX';
2382 raise ret_err1;
2383 end if;
2384
2385 -- verify there have never been any manual retirements
2386 select count(*)
2387 into l_count
2388 from fa_transaction_headers th
2389 where th.book_type_code = p_tax_book
2390 and th.asset_id = p_asset_id
2391 and th.transaction_type_code in
2392 ('FULL RETIREMENT', 'PARTIAL RETIREMENT', 'REINSTATEMENT')
2393 and th.source_transaction_header_id is null;
2394
2395 if (l_count > 0) then
2396 l_mesg_name := 'FA_MCP_RET_MANUAL_TAX';
2397 raise ret_err1;
2398 end if;
2399
2400 -- call famppc (does nothing but get the costs)
2401 -- get dist source book /cost retired from corp (not needed , use pop util)
2402
2403 -- pop the retirement struct with corp vales
2404 -- uses the retirement id for lookup
2405
2406 if (g_log_level_rec.statement_level) then
2407 fa_debug_pkg.add('test',
2408 'l_asset_retire_rec.retirement_id',
2409 l_asset_retire_rec.retirement_id, p_log_level_rec => g_log_level_rec);
2410 end if;
2411
2412 if not fa_util_pvt.get_asset_retire_rec
2413 (px_asset_retire_rec => l_asset_retire_rec,
2414 p_mrc_sob_type_code => 'P',
2415 p_set_of_books_id => null,
2416 p_log_level_rec => g_log_level_rec) then
2417 l_mesg_name := 'FA_MCP_FAIL_THID';
2418 raise ret_err2;
2419 end if;
2420
2421 if (l_old_corp_cost = 0) then
2422 l_tax_cost_retired := l_tax_cost;
2423 else
2424 l_tax_cost_retired := l_tax_cost * (l_asset_retire_rec.cost_retired / l_old_corp_cost);
2425 end if;
2426
2427 -- round cost retired
2428 -- Bug 8643319: Passed the sob_id from cache
2429 if not fa_utils_pkg.faxrnd
2430 (l_tax_cost_retired,
2431 p_tax_book,
2432 fa_cache_pkg.fazcbc_record.set_of_books_id,
2433 p_log_level_rec => g_log_level_rec) then
2434 l_mesg_name := 'FA_MCP_FAIL_THID';
2435 raise ret_err2;
2436 end if;
2437
2438 -- BUG# 3610820
2439 -- do not fail here, but trap condition , dump error and warn
2440 if (l_tax_cost_retired = 0 and l_tax_cost <> 0) then
2441 l_mesg_name := 'FA_RET_COST_TOO_BIG';
2442 raise ret_err1; -- non-fatal
2443 end if;
2444
2445 -- set up the tax specific values in the retirement structure
2446 -- need to double check if some of these might be defaulted from api
2447
2448 l_asset_retire_rec.cost_retired := l_tax_cost_retired;
2449 l_asset_retire_rec.status := 'PENDING';
2450 l_asset_retire_rec.retirement_prorate_convention :=
2451 fa_cache_pkg.fazccbd_record.retirement_prorate_convention;
2452 l_asset_retire_rec.retirement_id := NULL;
2453 l_asset_retire_rec.units_retired := NULL;
2454
2455 -- BUG# 2737472
2456 -- need to set stl_method_code, etc
2457 if (fa_cache_pkg.fazccbd_record.use_stl_retirements_flag = 'YES') then
2458 l_asset_retire_rec.detail_info.stl_method_code := fa_cache_pkg.fazccbd_record.stl_method_code;
2459 l_asset_retire_rec.detail_info.stl_life_in_months := fa_cache_pkg.fazccbd_record.stl_life_in_months;
2460 end if;
2461
2462 l_asset_retire_rec.cost_of_removal := l_cost_of_removal;
2463 l_asset_retire_rec.proceeds_of_sale := l_proceeds_of_sale;
2464 l_asset_retire_rec.retirement_type_code := l_retirement_type_code;
2465 l_asset_retire_rec.detail_info.itc_recapture_id := l_itc_recapture_id;
2466 l_asset_retire_rec.reference_num := l_reference_num;
2467 l_asset_retire_rec.sold_to := l_sold_to;
2468 l_asset_retire_rec.trade_in_asset_id := l_trade_in_asset_id;
2469
2470 l_asset_hdr_rec.asset_id := p_asset_id;
2471 l_asset_hdr_rec.book_type_code := p_tax_book;
2472
2473
2474 -- BUG# 2447234
2475 -- reset the date_retired to the transaction_date in case it
2476 -- fell in a future period in respect to tax and was thus
2477 -- redefaulted above
2478
2479 l_asset_retire_rec.date_retired := l_trx_date_entered;
2480
2481 -- insure we run gainloss
2482 l_asset_retire_rec.calculate_gain_loss := FND_API.G_TRUE;
2483
2484 -- call the appropriate api
2485 -- passing calc_gain_loss flag as true
2486
2487 if (g_log_level_rec.statement_level) then
2488 fa_debug_pkg.add('test',
2489 'calling',
2490 'do retirement', p_log_level_rec => g_log_level_rec);
2491 fa_debug_pkg.add('test',
2492 'souce thid before call',
2493 l_trans_rec.source_transaction_header_id, p_log_level_rec => g_log_level_rec);
2494 end if;
2495
2496 FA_RETIREMENT_PUB.do_retirement
2497 (p_api_version => l_api_version,
2498 p_init_msg_list => l_init_msg_list,
2499 p_commit => l_commit,
2500 p_validation_level => l_validation_level,
2501 p_calling_fn => l_calling_fn,
2502 x_return_status => l_return_status,
2503 x_msg_count => l_msg_count,
2504 x_msg_data => l_msg_data,
2505 px_trans_rec => l_trans_rec,
2506 px_dist_trans_rec => l_dist_trans_rec,
2507 px_asset_hdr_rec => l_asset_hdr_rec,
2508 px_asset_retire_rec => l_asset_retire_rec,
2509 p_asset_dist_tbl => l_asset_dist_tbl,
2510 p_subcomp_tbl => l_subcomp_tbl,
2511 p_inv_tbl => l_inv_tbl
2512 );
2513
2514 end if; -- ret vs. reinstate
2515
2516 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
2517 l_mesg_name := 'FA_MCP_FAIL_THID';
2518 raise ret_err2;
2519 end if;
2520
2521 else -- invalid
2522 raise ret_err1;
2523 end if; -- valid
2524
2525 IF (l_transaction_type_code = 'REINSTATEMENT') THEN
2526 l_mesg_name := 'FA_MCP_REINSTATE_SUCCESS';
2527 ELSE
2528 l_mesg_name := 'FA_MCP_RETIRE_SUCCESS';
2529 END IF;
2530
2531 write_message
2532 (p_asset_number => p_asset_number,
2533 p_thid => p_corp_thid,
2534 p_message => l_mesg_name,
2535 p_token => l_token,
2536 p_value => l_value,
2537 p_mode => 'S');
2538
2539 X_return_status := FND_API.G_RET_STS_SUCCESS;
2540
2541 EXCEPTION
2542 when ret_err1 then
2543 -- non-fatal
2544 write_message
2545 (p_asset_number => p_asset_number,
2546 p_thid => p_corp_thid,
2547 p_message => l_mesg_name,
2548 p_token => l_token,
2549 p_value => l_value,
2550 p_mode => 'W');
2551
2552 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
2553 x_return_status := FND_API.G_RET_STS_ERROR;
2554
2555 when ret_err2 then
2556 -- fatal
2557 write_message
2558 (p_asset_number => p_asset_number,
2559 p_thid => p_corp_thid,
2560 p_message => l_mesg_name,
2561 p_token => l_token,
2562 p_value => l_value,
2563 p_mode => 'F');
2564
2565 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
2566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2567
2568 when others then
2569 -- fatal
2570 fa_srvr_msg.add_sql_error(calling_fn => null, p_log_level_rec => g_log_level_rec);
2571 write_message
2572 (p_asset_number => p_asset_number,
2573 p_thid => p_corp_thid,
2574 p_message => 'FA_MCP_FAIL_THID',
2575 p_token => null,
2576 p_value => null,
2577 p_mode => 'F');
2578
2579 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
2580 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2581
2582 END mcp_retirement;
2583
2584 ----------------------------------------------------------------
2585
2586 -- this is used to maintaint the old execution report seperately
2587 -- from the log. Only the main message will be dumped to out file.
2588 -- all messaging and debug will be demped to the log file
2589
2590 PROCEDURE write_message
2591 (p_asset_number in varchar2,
2592 p_thid in number,
2593 p_message in varchar2,
2594 p_token in varchar2,
2595 p_value in varchar2,
2596 p_mode in varchar2) IS
2597
2598 l_asset_number varchar2(50);
2599 l_thid varchar2(20);
2600 l_mesg varchar2(100);
2601 l_string varchar2(512);
2602 l_calling_fn varchar2(40);
2603 l_return_char number;
2604
2605 BEGIN
2606
2607 -- only pass calling_fn for failures
2608 if p_mode = 'F' then
2609 -- l_calling_fn := 'fa_masscp_pkg.write_message';
2610 G_fatal_error := TRUE;
2611 G_failure_count := G_failure_count + 1;
2612 elsif p_mode = 'W' then
2613 G_warning_count := G_warning_count + 1;
2614 else
2615 G_success_count := G_success_count + 1;
2616 end if;
2617
2618 -- first dump the message to the output file
2619 -- set/translate/retrieve the mesg from fnd
2620
2621 fnd_message.set_name('OFA', p_message);
2622 if p_token is not null then
2623 fnd_message.set_token(p_token, p_value);
2624 end if;
2625
2626 -- get the message but only display up to
2627 -- the return character (if it exists)
2628 -- for nicer formatting in the exception report
2629
2630 l_mesg := substrb(fnd_message.get, 1, 100);
2631 l_return_char := instrb(l_mesg, fnd_global.local_chr(10));
2632
2633 if (l_return_char > 0) then
2634 l_mesg := substrb(l_mesg, 1, l_return_char - 1);
2635 end if;
2636
2637 l_asset_number := rpad(p_asset_number, 15);
2638 l_thid := rpad(to_char(p_thid), 20);
2639 l_string := l_asset_number || ' ' || l_thid || ' ' || l_mesg;
2640
2641 FND_FILE.put(FND_FILE.output,l_string);
2642 FND_FILE.new_line(FND_FILE.output,1);
2643
2644 -- now process the message for the log file
2645 fa_srvr_msg.add_message
2646 (calling_fn => l_calling_fn,
2647 name => p_message,
2648 token1 => p_token,
2649 value1 => p_value, p_log_level_rec => g_log_level_rec);
2650
2651 EXCEPTION
2652 when others then
2653 raise;
2654 END ;
2655
2656 ----------------------------------------------------------------
2657
2658 -- This function will select all candidate transactions in a single
2659 -- shot (no longer distinguishes between parent / child). The primary
2660 -- cursors have removed logic for checking if parent or group exist.
2661 -- We will only stripe the worker number based on the following order:
2662 --
2663 -- In the initial phase, group / parent /child assets will all to
2664 -- worker 1 until we get response from perf team on how to better
2665 -- handle these hierarchies
2666
2667 PROCEDURE allocate_workers (
2668 p_book_type_code IN VARCHAR2,
2669 p_period_name IN VARCHAR2,
2670 p_period_counter IN NUMBER,
2671 p_mode IN NUMBER,
2672 p_parent_request_id IN NUMBER,
2673 p_total_requests IN NUMBER,
2674 x_return_status OUT NOCOPY NUMBER) IS
2675
2676 -- find all top level parent assets for use by next cursor
2677 -- regardless if parent belongs to group or not
2678 -- also include non-child assets here too
2679
2680 -- note that parent asset is is only populated for addition trxs to start with
2681 -- thus this can only pull a given asset once for parent not null,
2682 -- but for null parent id, assets can come back multiple times, thus distinct
2683
2684 cursor c_parent_assets (p_parent_request_id number) is
2685 select /*+ parallel(fpw_p) parallel(fpw_c) */
2686 distinct
2687 fpw_p.asset_id,
2688 fpw_p.worker_number
2689 from fa_parallel_workers fpw_p,
2690 fa_parallel_workers fpw_c
2691 where fpw_p.request_id = p_parent_request_id
2692 and fpw_p.transaction_type_code = 'ADDITION'
2693 and (fpw_p.parent_asset_id is null or
2694 not exists
2695 (select 1
2696 from fa_parallel_workers fpw_p1
2697 where fpw_p1.request_id = p_parent_request_id
2698 and fpw_p1.asset_id = fpw_p.parent_asset_id
2699 and fpw_p1.transaction_type_code = 'ADDITION'))
2700 and fpw_c.request_id = fpw_p.request_id
2701 and fpw_c.parent_asset_id = fpw_p.asset_id
2702 and fpw_c.transaction_type_code = 'ADDITION';
2703
2704
2705 -- allocates all child assets to workers
2706
2707 -- note that it's technically possible to have a child in the middle
2708 -- of the hierarchy already assigned due to a group to a worker
2709 -- other than the parent. It's also possible to have children
2710 -- assigned to different groups in the same fashion.
2711 --
2712 -- currently this should not be a common scenario, but if it
2713 -- arises, we will have to introduce logic to deal with it
2714 -- which will be similar to that in FAMPSLTFRB.pls for
2715 -- src and dest assets with different groups.
2716
2717 -- since join is by ADDITION, there is no need for distinct usage here
2718
2719 cursor c_child_assets (p_parent_asset_id number,
2720 p_parent_request_id number) is
2721 select
2722 fpw1.asset_id,
2723 level
2724 from fa_parallel_workers fpw1
2725 start with fpw1.asset_id = p_parent_asset_id
2726 and fpw1.request_id = p_parent_request_id
2727 and fpw1.transaction_type_code = 'ADDITION'
2728 connect by prior fpw1.asset_id = fpw1.parent_asset_id
2729 and prior fpw1.request_id = fpw1.request_id
2730 and prior fpw1.transaction_type_code = 'ADDITION';
2731
2732
2733 -- local variables
2734 l_corp_period_rec FA_API_TYPES.period_rec_type;
2735 l_tax_period_rec FA_API_TYPES.period_rec_type;
2736
2737
2738 -- Used for bulk fetching
2739 l_batch_size number := 200;
2740
2741 -- used for subsequent parent / group updates
2742 l_asset_id num_tbl;
2743 l_worker_number num_tbl;
2744
2745 l_child_asset_id num_tbl;
2746 l_child_worker_number num_tbl;
2747 l_child_process_order num_tbl;
2748
2749 l_group_increment number := 0;
2750 l_date_effective date; -- bug fix 5900321
2751 fa_trx_types_tab fa_char30_tbl_type;
2752
2753 -- Bug 14661641:
2754 l_schema varchar2(50);
2755 l_status varchar2(100);
2756 l_industry varchar2(100);
2757
2758 l_calling_fn varchar2(40) := 'fa_masscp_pkg.allocate_workers';
2759 masscp_err exception;
2760
2761 BEGIN
2762
2763
2764 if (not g_log_level_rec.initialized) then
2765 if (NOT fa_util_pub.get_log_level_rec (
2766 x_log_level_rec => g_log_level_rec
2767 )) then
2768 raise masscp_err;
2769 end if;
2770 end if;
2771
2772 if(g_log_level_rec.statement_level) then
2773 fa_debug_pkg.add(l_calling_fn, 'at beginning of', 'worker allocation', p_log_level_rec => g_log_level_rec);
2774 end if;
2775
2776 x_return_status := 0;
2777
2778 -- get corp book information
2779 if not fa_cache_pkg.fazcbc(X_book => p_book_type_code, p_log_level_rec => g_log_level_rec) then
2780 raise masscp_err;
2781 end if;
2782
2783 -- get corp period info
2784 if not FA_UTIL_PVT.get_period_rec
2785 (p_book => fa_cache_pkg.fazcbc_record.distribution_source_book,
2786 p_period_counter => p_period_counter,
2787 x_period_rec => l_corp_period_rec
2788 , p_log_level_rec => g_log_level_rec) then
2789 raise masscp_err;
2790 end if;
2791
2792 -- get tax period info
2793 if not FA_UTIL_PVT.get_period_rec
2794 (p_book => p_book_type_code,
2795 x_period_rec => l_tax_period_rec
2796 , p_log_level_rec => g_log_level_rec) then
2797 raise masscp_err;
2798 end if;
2799
2800 -- determine transactions available for copying
2801
2802 fa_trx_types_tab := fa_char30_tbl_type();
2803
2804 if (fa_cache_pkg.fazcbc_record.copy_additions_flag = 'YES') then
2805 fa_trx_types_tab.EXTEND;
2806 fa_trx_types_tab(fa_trx_types_tab.last) := 'ADDITION';
2807 else
2808 fa_srvr_msg.add_message
2809 (calling_fn => l_calling_fn,
2810 name => 'FA_MCP_SHARED_NO_COPY',
2811 token1 => 'TYPE',
2812 value1 => 'ADDITIONS', p_log_level_rec => g_log_level_rec);
2813 end if;
2814
2815 if (fa_cache_pkg.fazcbc_record.copy_adjustments_flag = 'YES') then
2816 fa_trx_types_tab.EXTEND;
2817 fa_trx_types_tab(fa_trx_types_tab.last) := 'ADJUSTMENT';
2818 else
2819 fa_srvr_msg.add_message
2820 (calling_fn => l_calling_fn,
2821 name => 'FA_MCP_SHARED_NO_COPY',
2822 token1 => 'TYPE',
2823 value1 => 'ADJUSTMENTS', p_log_level_rec => g_log_level_rec);
2824 end if;
2825
2826 if (fa_cache_pkg.fazcbc_record.copy_retirements_flag = 'YES') then
2827 fa_trx_types_tab.EXTEND;
2828 fa_trx_types_tab(fa_trx_types_tab.last) := 'FULL RETIREMENT';
2829
2830 fa_trx_types_tab.EXTEND;
2831 fa_trx_types_tab(fa_trx_types_tab.last) := 'PARTIAL RETIREMENT';
2832
2833 fa_trx_types_tab.EXTEND;
2834 fa_trx_types_tab(fa_trx_types_tab.last) := 'REINSTATEMENT';
2835 else
2836 fa_srvr_msg.add_message
2837 (calling_fn => l_calling_fn,
2838 name => 'FA_MCP_SHARED_NO_COPY',
2839 token1 => 'TYPE',
2840 value1 => 'RETIREMENTS', p_log_level_rec => g_log_level_rec);
2841 end if;
2842
2843 if (nvl(fa_cache_pkg.fazcbc_record.allow_group_deprn_flag, 'N') = 'Y') then
2844 if (nvl(fa_cache_pkg.fazcbc_record.copy_group_addition_flag, 'N') = 'Y') then
2845 fa_trx_types_tab.EXTEND;
2846 fa_trx_types_tab(fa_trx_types_tab.last) := 'GROUP ADDITION';
2847 else
2848 fa_srvr_msg.add_message
2849 (calling_fn => l_calling_fn,
2850 name => 'FA_MCP_SHARED_NO_COPY',
2851 token1 => 'TYPE',
2852 value1 => 'GROUP ADDITIONS', p_log_level_rec => g_log_level_rec);
2853 end if;
2854 end if;
2855
2856
2857
2858 -- load the mass copy table with all transactions to be copied
2859 -- statement loads initial values for worker/order based on group / mod only
2860 -- parent / child logic will fire later and update the relevant children accordingly
2861
2862 if (p_mode = 1) then
2863
2864 -- skip if additions are not selected for copying
2865 if (fa_cache_pkg.fazcbc_record.copy_additions_flag <> 'YES') then
2866 x_return_status := 0;
2867 return;
2868 end if;
2869
2870 if(g_log_level_rec.statement_level) then
2871 fa_debug_pkg.add(l_calling_fn, 'inserting initial transactions at', sysdate, p_log_level_rec => g_log_level_rec);
2872 end if;
2873
2874 -- bug fix 5900321 (Initial Mass Copy copies capitalized assets to wrong fiscal year and period in TAX book)
2875 l_date_effective := nvl(l_corp_period_rec.period_close_date, sysdate);
2876 -- End bug fix 5900321
2877
2878 insert into fa_parallel_workers
2879 (request_id ,
2880 asset_id ,
2881 asset_number ,
2882 asset_type ,
2883 asset_category_id ,
2884 parent_asset_id ,
2885 book_type_code ,
2886 transaction_date_entered ,
2887 corp_transaction_header_id ,
2888 tax_transaction_header_id ,
2889 transaction_type_code ,
2890 old_group_asset_id ,
2891 new_group_asset_id ,
2892 worker_number ,
2893 process_order ,
2894 process_status )
2895 select p_parent_request_id,
2896 assets.asset_id,
2897 assets.asset_number,
2898 assets.asset_type,
2899 assets.asset_category_id,
2900 assets.parent_asset_id,
2901 p_book_type_code,
2902 assets.date_placed_in_service,
2903 assets.transaction_header_id_in,
2904 NULL tax_transaction_header_id,
2905 'ADDITION' transaction_type_code,
2906 NULL,
2907 decode(fa_cache_pkg.fazcbc_record.copy_group_assignment_flag,
2908 'Y', assets.group_asset_id,
2909 cbd.group_asset_id),
2910 decode(asset_type, 'GROUP', 1,
2911 decode(fa_cache_pkg.fazcbc_record.copy_group_assignment_flag,
2912 'Y', decode(assets.group_asset_id,
2913 null, mod(assets.asset_id, p_total_requests) + 1,
2914 1),
2915 decode(cbd.group_asset_id,
2916 null, mod(assets.asset_id, p_total_requests) + 1,
2917 1))),
2918 decode(asset_type, 'GROUP', 1,
2919 decode(fa_cache_pkg.fazcbc_record.copy_group_assignment_flag,
2920 'Y', decode(assets.group_asset_id,
2921 null, 1,
2922 2),
2923 decode(cbd.group_asset_id,
2924 null, 1,
2925 2))),
2926 'UNASSIGNED'
2927 from (select ad.asset_id,
2928 ad.asset_number,
2929 -- ad.asset_type,
2930 ah.asset_type, -- bug fix 5900321
2931 ad.asset_category_id,
2932 ad.parent_asset_id,
2933 books.book_type_code,
2934 books.group_asset_id,
2935 books.date_placed_in_service,
2936 books.transaction_header_id_in,
2937 books.period_counter_fully_retired
2938 from fa_books books,
2939 fa_additions_b ad,
2940 fa_deprn_periods dp,
2941 fa_asset_history ah -- bug fix 5900321
2942 where books.date_effective <= nvl(l_corp_period_rec.period_close_date, sysdate)
2943 and nvl(books.date_ineffective, sysdate) > nvl(l_corp_period_rec.period_close_date, sysdate - 1)
2944 and books.book_type_code = fa_cache_pkg.fazcbc_record.distribution_source_book
2945
2946 -- bug fix 5900321 (Initial Mass Copy copies capitalized assets to wrong fiscal year and period in TAX book)
2947 and ah.asset_id = books.asset_id
2948 and ah.date_effective <= l_date_effective
2949 and nvl(ah.date_ineffective, sysdate+1) > l_date_effective
2950 and ah.asset_type in ('CAPITALIZED', 'GROUP')
2951 -- End bug fix 5900321
2952
2953 and dp.book_type_code (+) = fa_cache_pkg.fazcbc_record.distribution_source_book
2954 and dp.period_counter (+) = books.period_counter_fully_retired
2955 and nvl(dp.period_counter,
2956 l_corp_period_rec.period_counter + 1) > l_corp_period_rec.period_counter
2957 and ad.asset_type in ('CAPITALIZED', 'GROUP')
2958 and ad.asset_id = books.asset_id) assets,
2959 fa_books taxbk,
2960 fa_category_book_defaults cbd
2961 where taxbk.asset_id(+) = assets.asset_id
2962 and taxbk.book_type_code(+) = p_book_type_code
2963 and taxbk.transaction_header_id_out(+) is null
2964 and taxbk.asset_id is null
2965 and cbd.category_id(+) = assets.asset_category_id
2966 and cbd.book_type_code(+) = p_book_type_code
2967 and assets.date_placed_in_service between cbd.start_dpis(+) and nvl(cbd.end_dpis(+), assets.date_placed_in_service);
2968
2969
2970 else
2971
2972 if (g_log_level_rec.statement_level) then
2973 fa_debug_pkg.add(l_calling_fn, 'opening c_per_trx_child cursor at', sysdate, p_log_level_rec => g_log_level_rec);
2974 end if;
2975
2976 insert into fa_parallel_workers
2977 (request_id ,
2978 asset_id ,
2979 asset_number ,
2980 asset_type ,
2981 asset_category_id ,
2982 parent_asset_id ,
2983 book_type_code ,
2984 transaction_date_entered ,
2985 corp_transaction_header_id ,
2986 tax_transaction_header_id ,
2987 transaction_type_code ,
2988 old_group_asset_id ,
2989 new_group_asset_id ,
2990 worker_number ,
2991 process_order ,
2992 process_status )
2993 select p_parent_request_id,
2994 assets.asset_id,
2995 assets.asset_number,
2996 assets.asset_type,
2997 assets.asset_category_id,
2998 decode(tax_bk.transaction_header_id_in, -- if asset exists in tax, parent is irrelevant
2999 null, assets.parent_asset_id,
3000 null),
3001 p_book_type_code,
3002 assets.transaction_date_entered,
3003 assets.transaction_header_id,
3004 tax_bk.transaction_header_id_in,
3005 assets.transaction_type_code,
3006 tax_bk.group_asset_id,
3007 decode(tax_bk.asset_id,
3008 null, decode(fa_cache_pkg.fazcbc_record.copy_group_assignment_flag,
3009 'Y', nvl(new_group_asset_id, cbd.group_asset_id),
3010 cbd.group_asset_id),
3011 tax_bk.group_asset_id),
3012 decode(asset_type,
3013 'GROUP', 1,
3014 decode(tax_bk.asset_id,
3015 null, decode(fa_cache_pkg.fazcbc_record.copy_group_assignment_flag,
3016 'Y', decode(nvl(new_group_asset_id, cbd.group_asset_id),
3017 null, mod(assets.asset_id, p_total_requests) + 1,
3018 1),
3019 decode(cbd.group_asset_id,
3020 null, mod(assets.asset_id, p_total_requests) + 1,
3021 1)),
3022 decode(tax_bk.group_asset_id,
3023 null, mod(assets.asset_id, p_total_requests) + 1,
3024 1))),
3025 decode(asset_type,
3026 'GROUP', 1,
3027 decode(tax_bk.asset_id,
3028 null, decode(fa_cache_pkg.fazcbc_record.copy_group_assignment_flag,
3029 'Y', decode(nvl(new_group_asset_id, cbd.group_asset_id),
3030 null, 1,
3031 2),
3032 decode(cbd.group_asset_id,
3033 null, 1,
3034 2)),
3035 decode(tax_bk.group_asset_id,
3036 null, 1,
3037 2))),
3038 'UNASSIGNED'
3039 from (select ad.asset_id,
3040 ad.asset_number,
3041 ad.asset_type,
3042 ad.asset_category_id,
3043 ad.parent_asset_id,
3044 corp_th.transaction_date_entered,
3045 corp_th.transaction_header_id,
3046 corp_th.transaction_type_code,
3047 corp_bk_old.group_asset_id old_group_asset_id,
3048 corp_bk.group_asset_id new_group_asset_id
3049 from fa_additions_b ad,
3050 fa_transaction_headers corp_th,
3051 fa_books corp_bk,
3052 fa_books corp_bk_old,
3053 TABLE(CAST(fa_trx_types_tab AS fa_char30_tbl_type)) trx
3054 where corp_th.book_type_code = fa_cache_pkg.fazcbc_record.distribution_source_book
3055 and corp_th.transaction_type_code = trx.column_value
3056 and corp_th.date_effective <= nvl(l_corp_period_rec.period_close_date, sysdate)
3057 and corp_th.date_effective >= l_corp_period_rec.period_open_date
3058 and corp_th.source_transaction_header_id is null
3059 and ad.asset_type in('CAPITALIZED', 'GROUP')
3060 and ad.asset_id = corp_th.asset_id
3061 and corp_bk.asset_id = corp_th.asset_id
3062 and corp_bk.book_type_code = corp_th.book_type_code
3063 and corp_bk.transaction_header_id_in = corp_th.transaction_header_id
3064 and corp_bk_old.asset_id(+) = corp_th.asset_id
3065 and corp_bk_old.book_type_code(+) = corp_th.book_type_code
3066 and corp_bk_old.transaction_header_id_out(+) = corp_th.transaction_header_id) assets,
3067 fa_transaction_headers tax_th,
3068 fa_books tax_bk,
3069 fa_category_book_defaults cbd
3070 where tax_th.book_type_code(+) = p_book_type_code
3071 and tax_th.asset_id(+) = assets.asset_id
3072 and tax_th.source_transaction_header_id(+) = assets.transaction_header_id
3073 and tax_th.source_transaction_header_id is null
3074 and tax_bk.asset_id(+) = assets.asset_id
3075 and tax_bk.book_type_code(+) = p_book_type_code
3076 and tax_bk.transaction_header_id_out(+) is null
3077 and cbd.category_id(+) = assets.asset_category_id
3078 and cbd.book_type_code(+) = p_book_type_code
3079 and assets.transaction_date_entered between cbd.start_dpis(+) and nvl(cbd.end_dpis(+), assets.transaction_date_entered);
3080
3081 end if;
3082
3083 if (g_log_level_rec.statement_level) then
3084 fa_debug_pkg.add(l_calling_fn, 'rows inserted into fa_parallel_workers', sql%rowcount);
3085 end if;
3086
3087 FND_CONCURRENT.AF_COMMIT;
3088 /* Bug 9020567 */
3089 /* When Data volumes are hign in fa_parallel_workers table we need to */
3090 /* analze this table to compute statistics for performance reason*/
3091
3092 if (g_log_level_rec.statement_level) then
3093 fa_debug_pkg.add(l_calling_fn, 'Getting schema name', 'OFA', p_log_level_rec => g_log_level_rec);
3094 end if;
3095
3096 -- Bug 14661641: Get FA schema
3097 if not (fnd_installation.get_app_info (
3098 application_short_name => 'OFA',
3099 status => l_status,
3100 industry => l_industry,
3101 oracle_schema => l_schema)) then
3102 raise masscp_err;
3103 end if;
3104
3105 if (g_log_level_rec.statement_level) then
3106 fa_debug_pkg.add(l_calling_fn, 'Schema name', l_schema, p_log_level_rec => g_log_level_rec);
3107 end if;
3108
3109 EXECUTE IMMEDIATE 'begin sys.dbms_stats.gather_table_stats(
3110 :1, '
3111 ||'''fa_parallel_workers'''
3112 ||',estimate_percent=>100, cascade=>TRUE); end;' using l_schema;
3113
3114 if (g_log_level_rec.statement_level) then
3115 fa_debug_pkg.add(l_calling_fn, 'After gather statistics', l_schema, p_log_level_rec => g_log_level_rec);
3116 end if;
3117
3118 -- increase the process order for group if applicable
3119 if nvl(fa_cache_pkg.fazcbc_record.allow_group_deprn_flag, 'N') = 'Y' then
3120 l_group_increment := 1;
3121 end if; -- group
3122
3123
3124
3125 -- find all top level parent/non-parent/orphan assets for use by next cursor
3126 -- regardless if parent belongs to group or not
3127
3128 open c_parent_assets (p_parent_request_id);
3129
3130 loop
3131
3132 fetch c_parent_assets bulk collect
3133 into l_asset_id,
3134 l_worker_number
3135 limit l_batch_size;
3136
3137 if (g_log_level_rec.statement_level) then
3138 fa_debug_pkg.add(l_calling_fn, 'rows fetched for parents, non-children and orphaned children', l_asset_id.count, p_log_level_rec => g_log_level_rec);
3139 end if;
3140
3141 if (l_asset_id.count = 0) then
3142 exit;
3143 end if;
3144
3145 -- allocates all child assets to workers
3146 -- note that it's technically possible to have a child in the middle
3147 -- of the hierarchy already assigned due to group - open for now...
3148
3149 for x in 1..l_asset_id.count loop
3150
3151 open c_child_assets (l_asset_id(x),
3152 p_parent_request_id);
3153
3154 loop
3155
3156 fetch c_child_assets bulk collect
3157 into l_child_asset_id,
3158 l_child_process_order;
3159
3160 if (g_log_level_rec.statement_level) then
3161 fa_debug_pkg.add(l_calling_fn, 'rows fetched for child assets', l_child_asset_id.count, p_log_level_rec => g_log_level_rec);
3162 end if;
3163
3164 if (l_child_asset_id.count = 0) then
3165 exit;
3166 end if;
3167
3168 for i in 1..l_child_asset_id.count loop
3169 l_child_worker_number(i) := l_worker_number(x);
3170 end loop;
3171
3172 -- note update by asset id instead of rowid is intentional
3173 -- we need all lines for the asset to go to the same worker
3174 forall i in 1..l_child_asset_id.count
3175 update fa_parallel_workers
3176 set worker_number = l_child_worker_number(i),
3177 process_order = l_child_process_order(i) + l_group_increment
3178 where request_id = p_parent_request_id
3179 and asset_id = l_child_asset_id(i);
3180
3181 end loop;
3182
3183 close c_child_assets;
3184
3185 end loop;
3186
3187 end loop;
3188
3189 close c_parent_assets;
3190
3191 if (g_log_level_rec.statement_level) then
3192 fa_debug_pkg.add(l_calling_fn, 'done process parent and child cursors', '', p_log_level_rec => g_log_level_rec);
3193 end if;
3194
3195 FND_CONCURRENT.AF_COMMIT;
3196
3197 -- dump any debug messages from above
3198 if (g_log_level_rec.statement_level) then
3199 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
3200 end if;
3201
3202
3203 x_return_status := 0;
3204
3205 EXCEPTION
3206 WHEN masscp_err THEN
3207 FND_CONCURRENT.AF_ROLLBACK;
3208 fa_srvr_msg.add_message (calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
3209
3210 -- dump any debug messages from above
3211 if (g_log_level_rec.statement_level) then
3212 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
3213 end if;
3214
3215
3216 X_return_status := 2;
3217
3218 WHEN OTHERS THEN
3219 FND_CONCURRENT.AF_ROLLBACK;
3220 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
3221
3222 -- dump any debug messages from above
3223 if (g_log_level_rec.statement_level) then
3224 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
3225 end if;
3226
3227 x_return_status := 2;
3228
3229 END allocate_workers;
3230
3231 END fa_masscp_pkg;