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