[Home] [Help]
PACKAGE BODY: APPS.FA_TAX_UPLOAD_PKG
Source
1 PACKAGE BODY FA_TAX_UPLOAD_PKG as
2 /* $Header: fataxupb.pls 120.19.12010000.1 2008/07/28 13:23:41 appldev ship $ */
3
4 g_log_level_rec fa_api_types.log_level_rec_type;
5
6 PROCEDURE faxtaxup(
7 p_book_type_code IN VARCHAR2,
8 p_parent_request_id IN NUMBER,
9 p_total_requests IN NUMBER,
10 p_request_number IN NUMBER,
11 px_max_asset_id IN OUT NOCOPY NUMBER,
12 x_success_count OUT NOCOPY number,
13 x_failure_count OUT NOCOPY number,
14 x_return_status OUT NOCOPY number
15 ) IS
16
17 -- messaging
18 l_batch_size NUMBER;
19 l_loop_count NUMBER;
20 l_count NUMBER := 0;
21 p_msg_count NUMBER := 0;
22 p_msg_data VARCHAR2(512);
23 l_name VARCHAR2(30);
24 l_temp VARCHAR2(30);
25
26 -- misc
27 l_request_id NUMBER;
28 l_trx_approval BOOLEAN;
29 l_status VARCHAR2(1);
30 l_result BOOLEAN := TRUE;
31
32 -- types
33 TYPE rowid_tbl IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
34 TYPE number_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
35 TYPE date_tbl IS TABLE OF DATE INDEX BY BINARY_INTEGER;
36 TYPE v30_tbl IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
37
38 -- used for main cursor
39 l_tax_rowid rowid_tbl;
40 l_asset_id number_tbl;
41 l_asset_number v30_tbl;
42 l_asset_type v30_tbl;
43 l_adjusted_rate number_tbl;
44 l_basic_rate number_tbl;
45 l_bonus_rule v30_tbl;
46 l_ceiling_name v30_tbl;
47 l_cost number_tbl;
48 l_date_placed_in_service date_tbl;
49 l_depreciate_flag v30_tbl;
50 l_deprn_method_code v30_tbl;
51 l_itc_amount_id number_tbl;
52 l_life_in_months number_tbl;
53 l_original_cost number_tbl;
54 l_production_capacity number_tbl;
55 l_prorate_convention_code v30_tbl;
56 l_salvage_value number_tbl;
57 l_short_fiscal_year_flag v30_tbl;
58 l_conversion_date date_tbl;
59 l_original_deprn_start_date date_tbl;
60 l_fully_rsvd_revals_counter number_tbl;
61 l_unrevalued_cost number_tbl;
62 l_reval_ceiling number_tbl;
63 l_deprn_reserve number_tbl;
64 l_ytd_deprn number_tbl;
65 l_reval_amortization_basis number_tbl;
66 l_reval_reserve number_tbl;
67 l_ytd_reval_deprn_expense number_tbl;
68 l_transaction_subtype v30_tbl;
69 l_amortization_start_date date_tbl;
70 l_transaction_name v30_tbl;
71 l_attribute1 v30_tbl;
72 l_attribute2 v30_tbl;
73 l_attribute3 v30_tbl;
74 l_attribute4 v30_tbl;
75 l_attribute5 v30_tbl;
76 l_attribute6 v30_tbl;
77 l_attribute7 v30_tbl;
78 l_attribute8 v30_tbl;
79 l_attribute9 v30_tbl;
80 l_attribute10 v30_tbl;
81 l_attribute11 v30_tbl;
82 l_attribute12 v30_tbl;
83 l_attribute13 v30_tbl;
84 l_attribute14 v30_tbl;
85 l_attribute15 v30_tbl;
86 l_attribute_category_code v30_tbl;
87 l_global_attribute1 v30_tbl;
88 l_global_attribute2 v30_tbl;
89 l_global_attribute3 v30_tbl;
90 l_global_attribute4 v30_tbl;
91 l_global_attribute5 v30_tbl;
92 l_global_attribute6 v30_tbl;
93 l_global_attribute7 v30_tbl;
94 l_global_attribute8 v30_tbl;
95 l_global_attribute9 v30_tbl;
96 l_global_attribute10 v30_tbl;
97 l_global_attribute11 v30_tbl;
98 l_global_attribute12 v30_tbl;
99 l_global_attribute13 v30_tbl;
100 l_global_attribute14 v30_tbl;
101 l_global_attribute15 v30_tbl;
102 l_global_attribute16 v30_tbl;
103 l_global_attribute17 v30_tbl;
104 l_global_attribute18 v30_tbl;
105 l_global_attribute19 v30_tbl;
106 l_global_attribute20 v30_tbl;
107 l_global_attribute_category v30_tbl;
108 l_group_asset_id number_tbl;
109
110 -- used for api call
111 l_api_version NUMBER := 1.0;
112 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
113 l_commit VARCHAR2(1) := FND_API.G_FALSE;
114 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
115 l_return_status VARCHAR2(1);
116 l_mesg_count number;
117 l_mesg VARCHAR2(4000);
118 l_mesg_name VARCHAR2(30);
119
120 l_calling_fn VARCHAR2(30) := 'fa_tax_upload_pkg.faxtaxup';
121 l_string varchar2(250);
122
123 l_trans_rec FA_API_TYPES.trans_rec_type;
124 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
125 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
126 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
127 l_asset_fin_mrc_tbl_new FA_API_TYPES.asset_fin_tbl_type;
128 l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type;
129 l_inv_tbl FA_API_TYPES.inv_tbl_type;
130 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
131 l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
132 l_asset_deprn_mrc_tbl_new FA_API_TYPES.asset_deprn_tbl_type;
133 l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type;
134
135 l_asset_fin_rec_old FA_API_TYPES.asset_fin_rec_type;
136 l_asset_deprn_rec_old FA_API_TYPES.asset_deprn_rec_type;
137
138 l_deprn_exp_amort_nbv number;
139
140 CURSOR c_assets IS
141 select ti.rowid,
142 ad.asset_id,
143 ti.asset_number,
144 ad.asset_type,
145 ti.adjusted_rate,
146 ti.basic_rate,
147 ti.bonus_rule,
148 ti.ceiling_name,
149 ti.cost,
150 ti.date_placed_in_service,
151 ti.depreciate_flag,
152 ti.deprn_method_code,
153 ti.itc_amount_id,
154 ti.life_in_months,
155 ti.original_cost,
156 ti.production_capacity,
157 ti.prorate_convention_code,
158 ti.salvage_value,
159 ti.short_fiscal_year_flag,
160 ti.conversion_date,
161 ti.original_deprn_start_date,
162 ti.fully_rsvd_revals_counter,
163 ti.unrevalued_cost,
164 ti.reval_ceiling,
165 ti.deprn_reserve,
166 ti.ytd_deprn,
167 ti.reval_amortization_basis,
168 ti.reval_reserve,
169 ti.ytd_reval_deprn_expense,
170 decode(ti.amortize_nbv_flag,
171 'YES', 'AMORTIZED',
172 'EXPENSED') transaction_subtype,
173 ti.amortization_start_date,
174 nvl(ti.transaction_name, 'Tax Upload Interface') transaction_name,
175 ti.attribute1,
176 ti.attribute2,
177 ti.attribute3,
178 ti.attribute4,
179 ti.attribute5,
180 ti.attribute6,
181 ti.attribute7,
182 ti.attribute8,
183 ti.attribute9,
184 ti.attribute10,
185 ti.attribute11,
186 ti.attribute12,
187 ti.attribute13,
188 ti.attribute14,
189 ti.attribute15,
190 ti.attribute_category_code,
191 nvl(ti.global_attribute1,
192 bk.global_attribute1) global_attribute1,
193 nvl(ti.global_attribute2,
194 bk.global_attribute2) global_attribute2,
195 nvl(ti.global_attribute3,
196 bk.global_attribute3) global_attribute3,
197 nvl(ti.global_attribute4,
198 bk.global_attribute4) global_attribute4,
199 nvl(ti.global_attribute5,
200 bk.global_attribute5) global_attribute5,
201 nvl(ti.global_attribute6,
202 bk.global_attribute6) global_attribute6,
203 nvl(ti.global_attribute7,
204 bk.global_attribute7) global_attribute7,
205 nvl(ti.global_attribute8,
206 bk.global_attribute8) global_attribute8,
207 nvl(ti.global_attribute9,
208 bk.global_attribute9) global_attribute9,
209 nvl(ti.global_attribute10,
210 bk.global_attribute10) global_attribute10,
211 nvl(ti.global_attribute11,
212 bk.global_attribute11) global_attribute11,
213 nvl(ti.global_attribute12,
214 bk.global_attribute12) global_attribute12,
215 nvl(ti.global_attribute13,
216 bk.global_attribute13) global_attribute13,
217 nvl(ti.global_attribute14,
218 bk.global_attribute14) global_attribute14,
219 nvl(ti.global_attribute15,
220 bk.global_attribute15) global_attribute15,
221 nvl(ti.global_attribute16,
222 bk.global_attribute16) global_attribute16,
223 nvl(ti.global_attribute17,
224 bk.global_attribute17) global_attribute17,
225 nvl(ti.global_attribute18,
226 bk.global_attribute18) global_attribute18,
227 nvl(ti.global_attribute19,
228 bk.global_attribute19) global_attribute19,
229 nvl(ti.global_attribute20,
230 bk.global_attribute20) global_attribute20,
231 nvl(ti.global_attribute_category,
232 bk.global_attribute_category) global_attribute_category,
233 ti.group_asset_id
234 from fa_tax_interface ti,
235 fa_books bk,
236 fa_additions_b ad
237 where ti.book_type_code = p_book_type_code
238 and ti.posting_status = 'POST'
239 and ti.asset_number = ad.asset_number
240 and bk.asset_id = ad.asset_id
241 and bk.book_type_code = p_book_type_code
242 and bk.date_ineffective is null
243 and ad.asset_id > px_max_asset_id
244 -- any potential change in group will be
245 -- assigned to the first worker avoiding
246 -- the potential locking issues between workers
247 and decode(ti.group_asset_id,
248 null,
249 MOD(nvl(bk.group_asset_id, ad.asset_id), p_total_requests),
250 0) = (p_request_number - 1)
251 order by ad.asset_id;
252
253 -- Exceptions
254 done_exc EXCEPTION;
255 data_error EXCEPTION;
256 faxtaxup_err EXCEPTION;
257
258
259 BEGIN <<taxupload_main>>
260
261 if (not g_log_level_rec.initialized) then
262 if (NOT fa_util_pub.get_log_level_rec (
263 x_log_level_rec => g_log_level_rec
264 )) then
265 raise faxtaxup_err;
266 end if;
267 end if;
268
269 px_max_asset_id := nvl(px_max_asset_id, 0);
270 x_success_count := 0;
271 x_failure_count := 0;
272
273 if (px_max_asset_id = 0) then
274
275 FND_FILE.put(FND_FILE.output,'');
276 FND_FILE.new_line(FND_FILE.output,1);
277
278 -- dump out the headings
279 fnd_message.set_name('OFA', 'FA_MASSRET_REPORT_COLUMN');
280 l_string := fnd_message.get;
281
282 FND_FILE.put(FND_FILE.output,l_string);
283 FND_FILE.new_line(FND_FILE.output,1);
284
285 fnd_message.set_name('OFA', 'FA_MASSRET_REPORT_LINE');
286 l_string := fnd_message.get;
287
288 FND_FILE.put(FND_FILE.output,l_string);
289 FND_FILE.new_line(FND_FILE.output,1);
290
291 end if;
292
293
294 -- Get transaction approval and lock the book.
295 l_request_id := fnd_global.conc_request_id;
296
297 if not fa_cache_pkg.fazcbc(X_book => p_book_type_code,
298 p_log_level_rec => g_log_level_rec) then
299 raise faxtaxup_err ;
300 end if;
301
302 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
303
304 if (g_log_level_rec.statement_level) then
305 fa_debug_pkg.add(l_calling_fn,
306 'performing','fetching upload data',
307 p_log_level_rec => g_log_level_rec);
308 end if;
309
310 open c_assets;
311 fetch c_assets bulk collect
312 into l_tax_rowid ,
313 l_asset_id ,
314 l_asset_number ,
315 l_asset_type ,
316 l_adjusted_rate ,
317 l_basic_rate ,
318 l_bonus_rule ,
319 l_ceiling_name ,
320 l_cost ,
321 l_date_placed_in_service ,
322 l_depreciate_flag ,
323 l_deprn_method_code ,
324 l_itc_amount_id ,
325 l_life_in_months ,
326 l_original_cost ,
327 l_production_capacity ,
328 l_prorate_convention_code ,
329 l_salvage_value ,
330 l_short_fiscal_year_flag ,
331 l_conversion_date ,
332 l_original_deprn_start_date ,
333 l_fully_rsvd_revals_counter ,
334 l_unrevalued_cost ,
335 l_reval_ceiling ,
336 l_deprn_reserve ,
337 l_ytd_deprn ,
338 l_reval_amortization_basis ,
339 l_reval_reserve ,
340 l_ytd_reval_deprn_expense ,
341 l_transaction_subtype ,
342 l_amortization_start_date ,
343 l_transaction_name ,
344 l_attribute1 ,
345 l_attribute2 ,
346 l_attribute3 ,
347 l_attribute4 ,
348 l_attribute5 ,
349 l_attribute6 ,
350 l_attribute7 ,
351 l_attribute8 ,
352 l_attribute9 ,
353 l_attribute10 ,
354 l_attribute11 ,
355 l_attribute12 ,
356 l_attribute13 ,
357 l_attribute14 ,
358 l_attribute15 ,
359 l_attribute_category_code ,
360 l_global_attribute1 ,
361 l_global_attribute2 ,
362 l_global_attribute3 ,
363 l_global_attribute4 ,
364 l_global_attribute5 ,
365 l_global_attribute6 ,
366 l_global_attribute7 ,
367 l_global_attribute8 ,
368 l_global_attribute9 ,
369 l_global_attribute10 ,
370 l_global_attribute11 ,
371 l_global_attribute12 ,
372 l_global_attribute13 ,
373 l_global_attribute14 ,
374 l_global_attribute15 ,
375 l_global_attribute16 ,
376 l_global_attribute17 ,
377 l_global_attribute18 ,
378 l_global_attribute19 ,
379 l_global_attribute20 ,
380 l_global_attribute_category ,
381 l_group_asset_id
382 limit l_batch_size;
383 close c_assets;
384
385 if (g_log_level_rec.statement_level) then
386 fa_debug_pkg.add(l_calling_fn,
387 'performing','after fetching upload data',
388 p_log_level_rec => g_log_level_rec);
389 end if;
390
391 if l_tax_rowid.count = 0 then
392 raise done_exc;
393 end if;
394
395 for l_loop_count in 1..l_tax_rowid.count loop
396
397 -- set savepoint
398 savepoint taxup_savepoint;
399
400 -- clear the debug stack for each asset
401 FA_DEBUG_PKG.Initialize;
402 -- reset the message level to prevent bogus errors
403 FA_SRVR_MSG.Set_Message_Level(message_level => 10);
404
405 l_mesg_name := null;
406 fa_srvr_msg.add_message(
407 calling_fn => NULL,
408 name => 'FA_SHARED_ASSET_NUMBER',
409 token1 => 'NUMBER',
410 value1 => l_asset_number(l_loop_count),
411 p_log_level_rec => g_log_level_rec);
412
413 <<taxupload_records>>
414 BEGIN
415
416 -- reset the structs to null
417 l_trans_rec := NULL;
418 l_asset_hdr_rec := NULL;
419 l_asset_fin_rec_adj := NULL;
420 l_asset_fin_rec_new := NULL;
421 l_asset_fin_mrc_tbl_new.delete;
422 l_inv_trans_rec := NULL;
423 l_inv_tbl.delete;
424 l_asset_deprn_rec_adj := NULL;
425 l_asset_deprn_rec_new := NULL;
426 l_asset_deprn_mrc_tbl_new.delete;
427
428 -- reset the who info in trans rec
429 l_trans_rec.who_info.last_updated_by := FND_GLOBAL.USER_ID;
430 l_trans_rec.who_info.created_by := FND_GLOBAL.USER_ID;
431 l_trans_rec.who_info.creation_date := sysdate;
432 l_trans_rec.who_info.last_update_date := sysdate;
433 l_trans_rec.who_info.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
434 l_trans_rec.mass_reference_id := p_parent_request_id;
435 l_trans_rec.calling_interface := 'FATAXUP';
436
437 -- counter for the number of assets
438 l_count := l_count + 1;
439
440 if (g_log_level_rec.statement_level) then
441 fa_debug_pkg.add(l_calling_fn,
442 'asset_number',l_asset_number(l_loop_count),
443 p_log_level_rec => g_log_level_rec);
444 fa_debug_pkg.add(l_calling_fn,
445 'asset_id',l_asset_id(l_loop_count),
446 p_log_level_rec => g_log_level_rec);
447 end if;
448 -- Retrieve additions_info, to retrieve asset id and shared info
449 -- across books for the asset
450
451 -- verify asset is capitalized
452 if (l_asset_type(l_loop_count) not in ('CAPITALIZED', 'GROUP')) then
453 l_mesg_name := 'FA_NO_TAX_UPLOAD_CIP';
454 raise data_error;
455 end if;
456
457 -- set all the structures up with deltas
458 l_asset_hdr_rec.asset_id := l_asset_id(l_loop_count);
459 l_asset_hdr_rec.book_type_code := p_book_type_code;
460
461 l_asset_fin_rec_adj.adjusted_rate :=
462 l_adjusted_rate(l_loop_count);
463 l_asset_fin_rec_adj.basic_rate :=
464 l_basic_rate(l_loop_count);
465 l_asset_fin_rec_adj.bonus_rule :=
466 l_bonus_rule(l_loop_count);
467 l_asset_fin_rec_adj.ceiling_name :=
468 l_ceiling_name(l_loop_count);
469 l_asset_fin_rec_adj.cost :=
470 l_cost(l_loop_count);
471 l_asset_fin_rec_adj.date_placed_in_service :=
472 l_date_placed_in_service(l_loop_count);
473 l_asset_fin_rec_adj.depreciate_flag :=
474 l_depreciate_flag(l_loop_count);
475 l_asset_fin_rec_adj.deprn_method_code :=
476 l_deprn_method_code(l_loop_count);
477 l_asset_fin_rec_adj.itc_amount_id :=
478 l_itc_amount_id(l_loop_count);
479 l_asset_fin_rec_adj.life_in_months :=
480 l_life_in_months(l_loop_count);
481 l_asset_fin_rec_adj.original_cost :=
482 l_original_cost(l_loop_count);
483 l_asset_fin_rec_adj.production_capacity :=
484 l_production_capacity(l_loop_count);
485 l_asset_fin_rec_adj.prorate_convention_code :=
486 l_prorate_convention_code(l_loop_count);
487 l_asset_fin_rec_adj.salvage_value :=
488 l_salvage_value(l_loop_count);
489 l_asset_fin_rec_adj.short_fiscal_year_flag :=
490 l_short_fiscal_year_flag(l_loop_count);
491 l_asset_fin_rec_adj.conversion_date :=
492 l_conversion_date(l_loop_count);
493 l_asset_fin_rec_adj.orig_deprn_start_date :=
494 l_original_deprn_start_date(l_loop_count);
495 l_asset_fin_rec_adj.fully_rsvd_revals_counter :=
496 l_fully_rsvd_revals_counter(l_loop_count);
497 l_asset_fin_rec_adj.unrevalued_cost :=
498 l_unrevalued_cost(l_loop_count);
499 l_asset_fin_rec_adj.reval_ceiling :=
500 l_reval_ceiling(l_loop_count);
501 l_asset_deprn_rec_adj.deprn_reserve :=
502 l_deprn_reserve(l_loop_count);
503 l_asset_deprn_rec_adj.ytd_deprn :=
504 l_ytd_deprn(l_loop_count);
505 l_asset_deprn_rec_adj.reval_amortization_basis :=
506 l_reval_amortization_basis(l_loop_count);
507 l_asset_deprn_rec_adj.reval_deprn_reserve :=
508 l_reval_reserve(l_loop_count);
509 l_asset_deprn_rec_adj.reval_ytd_deprn :=
510 l_ytd_reval_deprn_expense(l_loop_count);
511 l_trans_rec.transaction_subtype :=
512 l_transaction_subtype(l_loop_count);
513 l_trans_rec.amortization_start_date := -- trx date entered?
514 l_amortization_start_date(l_loop_count);
515 l_trans_rec.transaction_name :=
516 l_transaction_name(l_loop_count);
517 l_trans_rec.desc_flex.attribute1 :=
518 l_attribute1(l_loop_count);
519 l_trans_rec.desc_flex.attribute2 :=
520 l_attribute2(l_loop_count);
521 l_trans_rec.desc_flex.attribute3 :=
522 l_attribute3(l_loop_count);
523 l_trans_rec.desc_flex.attribute4 :=
524 l_attribute4(l_loop_count);
525 l_trans_rec.desc_flex.attribute5 :=
526 l_attribute5(l_loop_count);
527 l_trans_rec.desc_flex.attribute6 :=
528 l_attribute6(l_loop_count);
529 l_trans_rec.desc_flex.attribute7 :=
530 l_attribute7(l_loop_count);
531 l_trans_rec.desc_flex.attribute8 :=
532 l_attribute8(l_loop_count);
533 l_trans_rec.desc_flex.attribute9 :=
534 l_attribute9(l_loop_count);
535 l_trans_rec.desc_flex.attribute10 :=
536 l_attribute10(l_loop_count);
537 l_trans_rec.desc_flex.attribute11 :=
538 l_attribute11(l_loop_count);
539 l_trans_rec.desc_flex.attribute12 :=
540 l_attribute12(l_loop_count);
541 l_trans_rec.desc_flex.attribute13 :=
542 l_attribute13(l_loop_count);
543 l_trans_rec.desc_flex.attribute14 :=
544 l_attribute14(l_loop_count);
545 l_trans_rec.desc_flex.attribute15 :=
546 l_attribute15(l_loop_count);
547 l_trans_rec.desc_flex.attribute_category_code :=
548 l_attribute_category_code(l_loop_count);
549 l_asset_fin_rec_adj.global_attribute1 :=
550 l_global_attribute1(l_loop_count);
551 l_asset_fin_rec_adj.global_attribute2 :=
552 l_global_attribute2(l_loop_count);
553 l_asset_fin_rec_adj.global_attribute3 :=
554 l_global_attribute3(l_loop_count);
555 l_asset_fin_rec_adj.global_attribute4 :=
556 l_global_attribute4(l_loop_count);
557 l_asset_fin_rec_adj.global_attribute5 :=
558 l_global_attribute5(l_loop_count);
559 l_asset_fin_rec_adj.global_attribute6 :=
560 l_global_attribute6(l_loop_count);
561 l_asset_fin_rec_adj.global_attribute7 :=
562 l_global_attribute7(l_loop_count);
563 l_asset_fin_rec_adj.global_attribute8 :=
564 l_global_attribute8(l_loop_count);
565 l_asset_fin_rec_adj.global_attribute9 :=
566 l_global_attribute9(l_loop_count);
567 l_asset_fin_rec_adj.global_attribute10 :=
568 l_global_attribute10(l_loop_count);
569 l_asset_fin_rec_adj.global_attribute11 :=
570 l_global_attribute11(l_loop_count);
571 l_asset_fin_rec_adj.global_attribute12 :=
572 l_global_attribute12(l_loop_count);
573 l_asset_fin_rec_adj.global_attribute13 :=
574 l_global_attribute13(l_loop_count);
575 l_asset_fin_rec_adj.global_attribute14 :=
576 l_global_attribute14(l_loop_count);
577 l_asset_fin_rec_adj.global_attribute15 :=
578 l_global_attribute15(l_loop_count);
579 l_asset_fin_rec_adj.global_attribute16 :=
580 l_global_attribute16(l_loop_count);
581 l_asset_fin_rec_adj.global_attribute17 :=
582 l_global_attribute17(l_loop_count);
583 l_asset_fin_rec_adj.global_attribute18 :=
584 l_global_attribute18(l_loop_count);
585 l_asset_fin_rec_adj.global_attribute19 :=
586 l_global_attribute19(l_loop_count);
587 l_asset_fin_rec_adj.global_attribute20 :=
588 l_global_attribute20(l_loop_count);
589 l_asset_fin_rec_adj.global_attribute_category:=
590 l_global_attribute_category(l_loop_count);
591 l_asset_fin_rec_adj.group_asset_id :=
592 l_group_asset_id(l_loop_count);
593
594 -- load the current fin and deprn info
595 if not FA_UTIL_PVT.get_asset_fin_rec
596 (p_asset_hdr_rec => l_asset_hdr_rec,
597 px_asset_fin_rec => l_asset_fin_rec_old,
598 p_transaction_header_id => NULL,
599 p_mrc_sob_type_code => 'P',
600 p_log_level_rec => g_log_level_rec) then
601 raise data_error;
602 end if;
603
604 if not FA_UTIL_PVT.get_asset_deprn_rec
605 (p_asset_hdr_rec => l_asset_hdr_rec,
606 px_asset_deprn_rec => l_asset_deprn_rec_old,
607 p_period_counter => NULL,
608 p_mrc_sob_type_code => 'P',
609 p_log_level_rec => g_log_level_rec) then
610 raise data_error;
611 end if;
612
613 -- Bug 6803812: Check whether it is period of addition
614 if not FA_ASSET_VAL_PVT.validate_period_of_addition
615 (p_asset_id => l_asset_hdr_rec.asset_id,
616 p_book => l_asset_hdr_rec.book_type_code,
617 p_mode => 'ABSOLUTE',
618 px_period_of_addition => l_asset_hdr_rec.period_of_addition,
619 p_log_level_rec => g_log_level_rec) then
620 raise data_error;
621 end if;
622
623 if (l_asset_hdr_rec.period_of_addition <> 'Y') then
624 -- now fetch any existing catchup expense in fa_adjustments
625 -- and account for this when calculating the old deprn values
626 -- only if it is not in period of addition
627 select nvl(sum(decode(debit_credit_flag,
628 'DR', adjustment_amount,
629 -adjustment_amount)), 0)
630 into l_deprn_exp_amort_nbv
631 from fa_adjustments
632 where book_type_code = l_asset_hdr_rec.book_type_code
633 and asset_id = l_asset_hdr_rec.asset_id
634 and source_type_code = 'DEPRECIATION'
635 and adjustment_type = 'EXPENSE';
636
637 l_asset_deprn_rec_old.deprn_reserve := l_asset_deprn_rec_old.deprn_reserve -
638 l_deprn_exp_amort_nbv;
639 l_asset_deprn_rec_old.ytd_deprn := l_asset_deprn_rec_old.ytd_deprn -
640 l_deprn_exp_amort_nbv;
641
642 end if;
643 -- Set all non-calculated and non-method info
644 -- the amount columns are delta's so take the difference
645 -- between upload value and current value
646
647 if (l_asset_fin_rec_adj.salvage_value is not null) then
648 if (l_asset_fin_rec_old.salvage_type = 'AMT') then
649 l_asset_fin_rec_adj.salvage_value := nvl(l_asset_fin_rec_adj.salvage_value,
650 l_asset_fin_rec_old.salvage_value) -
651 l_asset_fin_rec_old.salvage_value;
652 else
653 l_asset_fin_rec_adj.salvage_value := l_asset_fin_rec_adj.salvage_value;
654 l_asset_fin_rec_adj.salvage_type := 'AMT';
655 end if;
656 end if;
657
658 l_asset_fin_rec_adj.production_capacity := nvl(l_asset_fin_rec_adj.production_capacity,
659 l_asset_fin_rec_old.production_capacity) -
660 l_asset_fin_rec_old.production_capacity;
661 l_asset_fin_rec_adj.cost := nvl(l_asset_fin_rec_adj.cost,
662 l_asset_fin_rec_old.cost) -
663 l_asset_fin_rec_old.cost;
664 l_asset_fin_rec_adj.original_cost := nvl(l_asset_fin_rec_adj.original_cost,
665 l_asset_fin_rec_old.original_cost) -
666 l_asset_fin_rec_old.original_cost;
667 if (l_asset_fin_rec_adj.unrevalued_cost is not null) then
668 l_asset_fin_rec_adj.unrevalued_cost := l_asset_fin_rec_adj.unrevalued_cost -
669 l_asset_fin_rec_old.unrevalued_cost;
670 end if;
671
672 l_asset_fin_rec_adj.reval_ceiling := nvl(l_asset_fin_rec_adj.reval_ceiling,
673 l_asset_fin_rec_old.reval_ceiling) -
674 l_asset_fin_rec_old.reval_ceiling;
675 l_asset_deprn_rec_adj.deprn_reserve := nvl(l_asset_deprn_rec_adj.deprn_reserve,
676 l_asset_deprn_rec_old.deprn_reserve) -
677 l_asset_deprn_rec_old.deprn_reserve;
678 l_asset_deprn_rec_adj.ytd_deprn := nvl(l_asset_deprn_rec_adj.ytd_deprn,
679 l_asset_deprn_rec_old.ytd_deprn) -
680 l_asset_deprn_rec_old.ytd_deprn;
681 l_asset_deprn_rec_adj.reval_amortization_basis := nvl(l_asset_deprn_rec_adj.reval_amortization_basis,
682 l_asset_deprn_rec_old.reval_amortization_basis) -
683 l_asset_deprn_rec_old.reval_amortization_basis;
684 l_asset_deprn_rec_adj.reval_deprn_reserve := nvl(l_asset_deprn_rec_adj.reval_deprn_reserve,
685 l_asset_deprn_rec_old.reval_deprn_reserve) -
686 l_asset_deprn_rec_old.reval_deprn_reserve;
687 l_asset_deprn_rec_adj.reval_ytd_deprn := nvl(l_asset_deprn_rec_adj.reval_ytd_deprn,
688 l_asset_deprn_rec_old.reval_ytd_deprn) -
689 l_asset_deprn_rec_old.reval_ytd_deprn;
690
691 -- round those values holding currency amounts
692
693 fa_round_pkg.fa_round(l_asset_fin_rec_adj.salvage_value,
694 p_book_type_code
695 ,p_log_level_rec => g_log_level_rec);
696 fa_round_pkg.fa_round(l_asset_fin_rec_adj.cost,
697 p_book_type_code
698 ,p_log_level_rec => g_log_level_rec);
699 fa_round_pkg.fa_round(l_asset_fin_rec_adj.original_cost,
700 p_book_type_code
701 ,p_log_level_rec => g_log_level_rec);
702 fa_round_pkg.fa_round(l_asset_fin_rec_adj.unrevalued_cost,
703 p_book_type_code
704 ,p_log_level_rec => g_log_level_rec);
705 fa_round_pkg.fa_round(l_asset_fin_rec_adj.reval_ceiling,
706 p_book_type_code
707 ,p_log_level_rec => g_log_level_rec);
708 fa_round_pkg.fa_round(l_asset_deprn_rec_adj.deprn_reserve,
709 p_book_type_code
710 ,p_log_level_rec => g_log_level_rec);
711 fa_round_pkg.fa_round(l_asset_deprn_rec_adj.ytd_deprn,
712 p_book_type_code
713 ,p_log_level_rec => g_log_level_rec);
714 fa_round_pkg.fa_round(l_asset_deprn_rec_adj.reval_amortization_basis,
715 p_book_type_code
716 ,p_log_level_rec => g_log_level_rec);
717 fa_round_pkg.fa_round(l_asset_deprn_rec_adj.reval_deprn_reserve,
718 p_book_type_code
719 ,p_log_level_rec => g_log_level_rec);
720 fa_round_pkg.fa_round(l_asset_deprn_rec_adj.reval_ytd_deprn,
721 p_book_type_code
722 ,p_log_level_rec => g_log_level_rec);
723
724 -- set up other needed struct values
725 l_trans_rec.mass_reference_id := l_request_id;
726
727 -- perform the Adjustment
728 fa_adjustment_pub.do_adjustment
729 (p_api_version => l_api_version,
730 p_init_msg_list => l_init_msg_list,
731 p_commit => l_commit,
732 p_validation_level => l_validation_level,
733 x_return_status => l_return_status,
734 x_msg_count => l_mesg_count,
735 x_msg_data => l_mesg,
736 p_calling_fn => l_calling_fn,
737 px_trans_rec => l_trans_rec,
738 px_asset_hdr_rec => l_asset_hdr_rec,
739 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
740 x_asset_fin_rec_new => l_asset_fin_rec_new,
741 x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
742 px_inv_trans_rec => l_inv_trans_rec,
743 px_inv_tbl => l_inv_tbl,
744 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
745 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
746 x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
747 p_group_reclass_options_rec => l_group_reclass_options_rec
748 );
749
750 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
751 raise data_error;
752 end if;
753
754 -- flag interface record as posted
755 update fa_tax_interface
756 set posting_status = 'POSTED',
757 tax_request_id = l_request_id
758 where rowid = l_tax_rowid(l_loop_count);
759
760 -- Increment asset count and dump asset_number to the log file
761 x_success_count := x_success_count + 1;
762 write_message(l_asset_number(l_loop_count),
763 'FA_MCP_ADJUSTMENT_SUCCESS',
764 p_log_level_rec => g_log_level_rec);
765
766 -- if (g_log_level_rec.statement_level) then
767 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
768 -- end if;
769
770
771 EXCEPTION -- exceptions for taxupload_records block
772
773 when data_error then
774 x_failure_count := x_failure_count + 1;
775
776 write_message(l_asset_number(l_loop_count),
777 l_mesg_name,
778 p_log_level_rec => g_log_level_rec);
779
780 -- if (g_log_level_rec.statement_level) then
781 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
782 -- end if;
783
784 rollback to savepoint taxup_savepoint;
785
786 when others then
787 x_failure_count := x_failure_count + 1;
788
789 write_message(l_asset_number(l_loop_count),
790 'FA_TAXUP_FAIL_TRX',
791 p_log_level_rec => g_log_level_rec);
792 fa_srvr_msg.add_sql_error(
793 calling_fn => l_calling_fn
794 ,p_log_level_rec => g_log_level_rec);
795
796 -- if (g_log_level_rec.statement_level) then
797 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
798 -- end if;
799
800 rollback to savepoint taxup_savepoint;
801
802 END; -- end taxupload_records block
803
804 -- commit every batch and reset the large rollback segment
805 FND_CONCURRENT.AF_COMMIT;
806
807 end loop; -- inner loop to loop through arrays
808
809 px_max_asset_id := l_asset_id(l_asset_id.count);
810 x_return_status := 0;
811
812 EXCEPTION
813 when done_exc then
814 x_return_status := 0;
815
816 when faxtaxup_err then
817 FND_CONCURRENT.AF_ROLLBACK;
818 fa_srvr_msg.add_message(calling_fn => l_calling_fn
819 ,p_log_level_rec => g_log_level_rec);
820
821 -- Dump Debug messages when run in debug mode to log file
822 -- if (g_log_level_rec.statement_level) then
823 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
824 -- end if;
825
826 x_return_status := 2;
827
828 when others then
829 FND_CONCURRENT.AF_ROLLBACK;
830 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
831 ,p_log_level_rec => g_log_level_rec);
832
833 -- Dump Debug messages when run in debug mode to log file
834 -- if (g_log_level_rec.statement_level) then
835 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
836 -- end if;
837
838 x_return_status := 2;
839
840 END faxtaxup; -- end taxupload_main block
841
842 -----------------------------------------------------------------------------
843
844 PROCEDURE write_message
845 (p_asset_number in varchar2,
846 p_message in varchar2,
847 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
848
849
850 l_message varchar2(30);
851 l_mesg varchar2(100);
852 l_string varchar2(512);
853 l_calling_fn varchar2(40); -- conditionally populated below
854
855 BEGIN
856
857 -- first dump the message to the output file
858 -- set/translate/retrieve the mesg from fnd
859
860 l_message := nvl(p_message, 'FA_TAXUP_FAIL_TRX');
861
862 if (l_message <> 'FA_MCP_ADJUSTMENT_SUCCESS') then
863 l_calling_fn := 'fa_masschg_pkg.do_mass_change';
864 end if;
865
866 fnd_message.set_name('OFA', l_message);
867 l_mesg := substrb(fnd_message.get, 1, 100);
868
869 l_string := rpad(p_asset_number, 15) || ' ' || l_mesg;
870
871 FND_FILE.put(FND_FILE.output,l_string);
872 FND_FILE.new_line(FND_FILE.output,1);
873
874 -- now process the messages for the log file
875 fa_srvr_msg.add_message
876 (calling_fn => l_calling_fn,
877 name => l_message,
878 p_log_level_rec => p_log_level_rec);
879
880 EXCEPTION
881 when others then
882 raise;
883
884 END write_message;
885
886 END FA_TAX_UPLOAD_PKG;