[Home] [Help]
PACKAGE BODY: APPS.FA_TAX_UPLOAD_PKG
Source
1 PACKAGE BODY FA_TAX_UPLOAD_PKG as
2 /* $Header: fataxupb.pls 120.29.12020000.2 2012/07/19 12:40:06 spooyath ship $ */
3
4 TYPE g_msg_err_rec IS RECORD(asset_number VARCHAR2(30)
5 ,exception_code VARCHAR2(10)
6 );
7 TYPE g_msg_err_tbl IS TABLE OF g_msg_err_rec INDEX BY BINARY_INTEGER;
8
9 g_err_msg g_msg_err_tbl;
10 g_count binary_integer := 1;
11 g_log_level_rec fa_api_types.log_level_rec_type;
12
13 g_release number := fa_cache_pkg.fazarel_release;
14
15 ----------------------------------------------------------------
16 -- Changes made as per the ER No.s 6606548 and 6606552 by Vkukutam
17 ----------------------------------------------------------------
18 PROCEDURE faxtaxup(
19 p_book_type_code IN VARCHAR2,
20 p_parent_request_id IN NUMBER,
21 p_total_requests IN NUMBER,
22 p_request_number IN NUMBER,
23 px_max_asset_id IN OUT NOCOPY NUMBER,
24 x_success_count OUT NOCOPY number,
25 x_failure_count OUT NOCOPY number,
26 x_return_status OUT NOCOPY number ) IS
27
28 -- messaging
29 l_batch_size NUMBER;
30 l_loop_count NUMBER;
31 l_count NUMBER := 0;
32 p_msg_count NUMBER := 0;
33 p_msg_data VARCHAR2(512);
34 l_name VARCHAR2(30);
35 l_temp VARCHAR2(30);
36
37 -- misc
38 l_debug boolean;
39 l_request_id NUMBER;
40 l_trx_approval BOOLEAN;
41 sql_stmt VARCHAR2(101);
42 l_status VARCHAR2(1);
43 l_result BOOLEAN := TRUE;
44
45 -- Changes made as per the ER No.s 6606548 and 6606552 by Vkukutam Start
46 lc_error_flag VARCHAR2(10);
47 l_asset_num VARCHAR2(100);
48 l_exception_err VARCHAR2(100);
49 --Changes made as per the ER No.s 6606548 and 6606552 by Vkukutam End
50
51 -- types
52 TYPE rowid_tbl IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
53 TYPE number_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
54 TYPE date_tbl IS TABLE OF DATE INDEX BY BINARY_INTEGER;
55 TYPE v30_tbl IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
56
57 -- used for main cursor
58
59 -- Changes made as per the ER No.s 6606548 and 6606552 by Vkukutam Start
60 l_nbv_at_switch number_tbl;
61 l_prior_deprn_limit_amount number_tbl;
62 l_period_full_reserve v30_tbl;
63 l_prior_deprn_method v30_tbl;
64 l_period_extd_deprn v30_tbl;
65 l_prior_deprn_limit number_tbl;
66 l_prior_basic_rate number_tbl;
67 l_prior_adjusted_rate number_tbl;
68 l_prior_life_in_months number_tbl;
69 l_prior_deprn_limit_type v30_tbl;
70 ln_err_cnt number := 0;
71
72 -- Changes made as per the ER No.s 6606548 and 6606552 by Vkukutam End
73
74 l_tax_rowid rowid_tbl;
75 l_asset_id number_tbl;
76 l_asset_number v30_tbl;
77 l_asset_type v30_tbl;
78 l_adjusted_rate number_tbl;
79 l_basic_rate number_tbl;
80 l_bonus_rule v30_tbl;
81 l_ceiling_name v30_tbl;
82 l_cost number_tbl;
83 l_date_placed_in_service date_tbl;
84 l_depreciate_flag v30_tbl;
85 l_deprn_method_code v30_tbl;
86 l_itc_amount_id number_tbl;
87 l_life_in_months number_tbl;
88 l_original_cost number_tbl;
89 l_old_cost number_tbl;
90 l_production_capacity number_tbl;
91 l_prorate_convention_code v30_tbl;
92 l_salvage_value number_tbl;
93 l_short_fiscal_year_flag v30_tbl;
94 l_conversion_date date_tbl;
95 l_original_deprn_start_date date_tbl;
96 l_fully_rsvd_revals_counter number_tbl;
97 l_unrevalued_cost number_tbl;
98 l_reval_ceiling number_tbl;
99 l_deprn_reserve number_tbl;
100 l_ytd_deprn number_tbl;
101 l_reval_amortization_basis number_tbl;
102 l_reval_reserve number_tbl;
103 l_ytd_reval_deprn_expense number_tbl;
104 l_transaction_subtype v30_tbl;
105 l_amortization_start_date date_tbl;
106 l_transaction_name v30_tbl;
107 l_attribute1 v30_tbl;
108 l_attribute2 v30_tbl;
109 l_attribute3 v30_tbl;
110 l_attribute4 v30_tbl;
111 l_attribute5 v30_tbl;
112 l_attribute6 v30_tbl;
113 l_attribute7 v30_tbl;
114 l_attribute8 v30_tbl;
115 l_attribute9 v30_tbl;
116 l_attribute10 v30_tbl;
117 l_attribute11 v30_tbl;
118 l_attribute12 v30_tbl;
119 l_attribute13 v30_tbl;
120 l_attribute14 v30_tbl;
121 l_attribute15 v30_tbl;
122 l_attribute_category_code v30_tbl;
123 l_global_attribute1 v30_tbl;
124 l_global_attribute2 v30_tbl;
125 l_global_attribute3 v30_tbl;
126 l_global_attribute4 v30_tbl;
127 l_global_attribute5 v30_tbl;
128 l_global_attribute6 v30_tbl;
129 l_global_attribute7 v30_tbl;
130 l_global_attribute8 v30_tbl;
131 l_global_attribute9 v30_tbl;
132 l_global_attribute10 v30_tbl;
133 l_global_attribute11 v30_tbl;
134 l_global_attribute12 v30_tbl;
135 l_global_attribute13 v30_tbl;
136 l_global_attribute14 v30_tbl;
137 l_global_attribute15 v30_tbl;
138 l_global_attribute16 v30_tbl;
139 l_global_attribute17 v30_tbl;
140 l_global_attribute18 v30_tbl;
141 l_global_attribute19 v30_tbl;
142 l_global_attribute20 v30_tbl;
143 l_global_attribute_category v30_tbl;
144 l_group_asset_id number_tbl;
145
146 -- used for api call
147 l_api_version NUMBER := 1.0;
148 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
149 l_commit VARCHAR2(1) := FND_API.G_FALSE;
150 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
151 l_return_status VARCHAR2(1);
152 l_mesg_count number;
153 l_mesg VARCHAR2(4000);
154 l_mesg_name VARCHAR2(30);
155
156 l_calling_fn VARCHAR2(30) := 'fa_tax_upload_pkg.faxtaxup';
157 l_string varchar2(250);
158 l_trans_rec FA_API_TYPES.trans_rec_type;
159 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
160 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
161 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
162 l_asset_fin_mrc_tbl_new FA_API_TYPES.asset_fin_tbl_type;
163 l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type;
164 l_inv_tbl FA_API_TYPES.inv_tbl_type;
165 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
166 l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
167 l_asset_deprn_mrc_tbl_new FA_API_TYPES.asset_deprn_tbl_type;
168 l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type;
169
170 l_asset_fin_rec_old FA_API_TYPES.asset_fin_rec_type;
171 l_asset_deprn_rec_old FA_API_TYPES.asset_deprn_rec_type;
172
173 l_deprn_exp_amort_nbv number;
174
175 --Bug# 7608030 start
176 l_p_rsv_counter number;
177 l_ext_period_counter number;
178 l_fiscal_yr number;
179 l_period_num number;
180 l_num_fy_yr number;
181 l_period_end_dt date;
182 l_current_period_dt date;
183 l_default_dt date := to_date('01-04-2007','DD-MM-RRRR');
184 --#Bug 7608030 end
185
186 CURSOR c_assets IS
187 select ti.rowid,
188 ti.asset_id,
189 ti.asset_number,
190 ti.asset_type,
191 ti.adjusted_rate,
192 ti.basic_rate,
193 ti.bonus_rule,
194 ti.ceiling_name,
195 ti.cost,
196 ti.date_placed_in_service,
197 ti.depreciate_flag,
198 ti.deprn_method_code,
199 ti.itc_amount_id,
200 ti.life_in_months,
201 ti.original_cost,
202 bk.cost,
203 ti.production_capacity,
204 ti.prorate_convention_code,
205 ti.salvage_value,
206 ti.short_fiscal_year_flag,
207 ti.conversion_date,
208 ti.original_deprn_start_date,
209 ti.fully_rsvd_revals_counter,
210 ti.unrevalued_cost,
211 ti.reval_ceiling,
212 ti.deprn_reserve,
213 ti.ytd_deprn,
214 ti.reval_amortization_basis,
215 ti.reval_reserve,
216 ti.ytd_reval_deprn_expense,
217 decode(ti.amortize_nbv_flag,
218 'YES', 'AMORTIZED',
219 'EXPENSED') transaction_subtype,
220 ti.amortization_start_date,
221 nvl(ti.transaction_name, 'Tax Upload Interface') transaction_name,
222 ti.attribute1,
223 ti.attribute2,
224 ti.attribute3,
225 ti.attribute4,
226 ti.attribute5,
227 ti.attribute6,
228 ti.attribute7,
229 ti.attribute8,
230 ti.attribute9,
231 ti.attribute10,
232 ti.attribute11,
233 ti.attribute12,
234 ti.attribute13,
235 ti.attribute14,
236 ti.attribute15,
237 ti.attribute_category_code,
238 nvl(ti.global_attribute1,
239 bk.global_attribute1) global_attribute1,
240 nvl(ti.global_attribute2,
241 bk.global_attribute2) global_attribute2,
242 nvl(ti.global_attribute3,
243 bk.global_attribute3) global_attribute3,
244 nvl(ti.global_attribute4,
245 bk.global_attribute4) global_attribute4,
246 nvl(ti.global_attribute5,
247 bk.global_attribute5) global_attribute5,
248 nvl(ti.global_attribute6,
249 bk.global_attribute6) global_attribute6,
250 nvl(ti.global_attribute7,
251 bk.global_attribute7) global_attribute7,
252 nvl(ti.global_attribute8,
253 bk.global_attribute8) global_attribute8,
254 nvl(ti.global_attribute9,
255 bk.global_attribute9) global_attribute9,
256 nvl(ti.global_attribute10,
257 bk.global_attribute10) global_attribute10,
258 nvl(ti.global_attribute11,
259 bk.global_attribute11) global_attribute11,
260 nvl(ti.global_attribute12,
261 bk.global_attribute12) global_attribute12,
262 nvl(ti.global_attribute13,
263 bk.global_attribute13) global_attribute13,
264 nvl(ti.global_attribute14,
265 bk.global_attribute14) global_attribute14,
266 nvl(ti.global_attribute15,
267 bk.global_attribute15) global_attribute15,
268 nvl(ti.global_attribute16,
269 bk.global_attribute16) global_attribute16,
270 nvl(ti.global_attribute17,
271 bk.global_attribute17) global_attribute17,
272 nvl(ti.global_attribute18,
273 bk.global_attribute18) global_attribute18,
274 nvl(ti.global_attribute19,
275 bk.global_attribute19) global_attribute19,
276 nvl(ti.global_attribute20,
277 bk.global_attribute20) global_attribute20,
278 nvl(ti.global_attribute_category,
279 bk.global_attribute_category) global_attribute_category,
280 ti.group_asset_id,
281 ti.nbv_at_switch, -- Changes made as per the ER No.s 6606548 and 6606552 by Vkukutam Start
282 ti.prior_deprn_limit_amount,
283 ti.period_full_reserve,
284 ti.prior_deprn_method,
285 ti.period_extd_deprn,
286 ti.prior_deprn_limit,
287 ti.prior_basic_rate,
288 ti.prior_adjusted_rate,
289 ti.prior_life_in_months,
290 ti.prior_deprn_limit_type -- Changes made as per the ER No.s 6606548 and 6606552 by Vkukutam End
291 from fa_tax_interface ti,
292 fa_books bk
293 where ti.book_type_code = p_book_type_code
294 and ti.posting_status = 'POST'
295 and ti.tax_request_id = p_parent_request_id
296 and ti.worker_id = p_request_number
297 and bk.asset_id = ti.asset_id
298 and bk.book_type_code = p_book_type_code
299 and bk.transaction_header_id_out is null
300 order by ti.asset_id;
301
302 -- Bug 7698030 start open period
303 cursor l_curr_open_period(p_book_type_code in varchar2
304 )
305 is
306 select fdp.calendar_period_close_date
307 from fa_book_controls fbc
308 ,fa_deprn_periods fdp
309 where fbc.book_type_code = fdp.book_type_code
310 and fdp.period_counter = fbc.last_period_counter+1
311 and fbc.book_type_code = p_book_type_code;
312
313 --7608030 full reserv counter
314 cursor l_period_info(p_book_type_code in varchar2
315 ,p_period in varchar2
316 )
317 is
318 select fcp.end_date
319 ,ffy.fiscal_year
320 ,fcp.period_num
321 ,fct.number_per_fiscal_year
322 from fa_fiscal_year ffy
323 ,fa_book_controls fbc
324 ,fa_calendar_periods fcp
325 ,fa_calendar_types fct
326 where ffy.fiscal_year_name = fbc.fiscal_year_name
327 and ffy.fiscal_year_name = fct.fiscal_year_name
328 and fbc.book_type_code = p_book_type_code
329 and fcp.calendar_type = fct.calendar_type
330 and fct.calendar_type = fbc.deprn_calendar
331 and fcp.start_date >= ffy.start_date
332 and fcp.end_date <= ffy.end_date
333 and upper(fcp.period_name) = upper(p_period);
334
335 --Bug 7698030 end
336
337 -- Exceptions
338 done_exc EXCEPTION;
339 data_error EXCEPTION;
340 faxtaxup_err EXCEPTION;
341 -- Changes made as per the ER No.s 6606548 and 6606552 by Vkukutam Start
342 l_japan_tax_reform varchar2(1) := fnd_profile.value('FA_JAPAN_TAX_REFORMS');
343 l_extended_deprn_period number;
344 l_pc_fully_reserved number;
345 l_limit_amt number;
346 -- Changes made as per the ER No.s 6606548 and 6606552 by Vkukutam End
347
348 --Added for 9371739
349 l_book_ytd NUMBER := 0;
350 l_book_rsv NUMBER := 0;
351
352 BEGIN <<taxupload_main>>
353
354 px_max_asset_id := nvl(px_max_asset_id, 0);
355 x_success_count := 0;
356 x_failure_count := 0;
357
358
359 if (not g_log_level_rec.initialized) then
360 if (NOT fa_util_pub.get_log_level_rec (
361 x_log_level_rec => g_log_level_rec
362 )) then
363 raise faxtaxup_err;
364 end if;
365 end if;
366
367 g_release := fa_cache_pkg.fazarel_release;
368
369
370 if (g_log_level_rec.statement_level) then
371 l_debug := TRUE;
372 else
373 l_debug := FALSE;
374 end if;
375
376 -- Get transaction approval and lock the book.
377 l_request_id := fnd_global.conc_request_id;
378
379 if not fa_cache_pkg.fazcbc(X_book => p_book_type_code, p_log_level_rec => g_log_level_rec) then
380 raise faxtaxup_err ;
381 end if;
382
383 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
384
385 if (l_debug) then
386 fa_debug_pkg.add(l_calling_fn,
387 'performing','fetching upload data', p_log_level_rec => g_log_level_rec);
388 end if;
389
390 open c_assets;
391 fetch c_assets bulk collect
392 into l_tax_rowid ,
393 l_asset_id ,
394 l_asset_number ,
395 l_asset_type ,
396 l_adjusted_rate ,
397 l_basic_rate ,
398 l_bonus_rule ,
399 l_ceiling_name ,
400 l_cost ,
401 l_date_placed_in_service ,
402 l_depreciate_flag ,
403 l_deprn_method_code ,
404 l_itc_amount_id ,
405 l_life_in_months ,
406 l_original_cost ,
407 l_old_cost ,
408 l_production_capacity ,
409 l_prorate_convention_code ,
410 l_salvage_value ,
411 l_short_fiscal_year_flag ,
412 l_conversion_date ,
413 l_original_deprn_start_date ,
414 l_fully_rsvd_revals_counter ,
415 l_unrevalued_cost ,
416 l_reval_ceiling ,
417 l_deprn_reserve ,
418 l_ytd_deprn ,
419 l_reval_amortization_basis ,
420 l_reval_reserve ,
421 l_ytd_reval_deprn_expense ,
422 l_transaction_subtype ,
423 l_amortization_start_date ,
424 l_transaction_name ,
425 l_attribute1 ,
426 l_attribute2 ,
427 l_attribute3 ,
428 l_attribute4 ,
429 l_attribute5 ,
430 l_attribute6 ,
431 l_attribute7 ,
432 l_attribute8 ,
433 l_attribute9 ,
434 l_attribute10 ,
435 l_attribute11 ,
436 l_attribute12 ,
437 l_attribute13 ,
438 l_attribute14 ,
439 l_attribute15 ,
440 l_attribute_category_code ,
441 l_global_attribute1 ,
442 l_global_attribute2 ,
443 l_global_attribute3 ,
444 l_global_attribute4 ,
445 l_global_attribute5 ,
446 l_global_attribute6 ,
447 l_global_attribute7 ,
448 l_global_attribute8 ,
449 l_global_attribute9 ,
450 l_global_attribute10 ,
451 l_global_attribute11 ,
452 l_global_attribute12 ,
453 l_global_attribute13 ,
454 l_global_attribute14 ,
455 l_global_attribute15 ,
456 l_global_attribute16 ,
457 l_global_attribute17 ,
458 l_global_attribute18 ,
459 l_global_attribute19 ,
460 l_global_attribute20 ,
461 l_global_attribute_category ,
462 l_group_asset_id ,
463 l_nbv_at_switch , -- Changes made as per the ER No.s 6606548 and 6606552 by Vkukutam Start
464 l_prior_deprn_limit_amount ,
465 l_period_full_reserve ,
466 l_prior_deprn_method ,
467 l_period_extd_deprn ,
468 l_prior_deprn_limit ,
469 l_prior_basic_rate ,
470 l_prior_adjusted_rate ,
471 l_prior_life_in_months ,
472 l_prior_deprn_limit_type -- Changes made as per the ER No.s 6606548 and 6606552 by Vkukutam End
473 limit l_batch_size;
474 close c_assets;
475
476 if (l_debug) then
477 fa_debug_pkg.add(l_calling_fn,
478 'performing','after fetching upload data', p_log_level_rec => g_log_level_rec);
479 end if;
480
481 if l_tax_rowid.count = 0 then
482 raise done_exc;
483 end if;
484
485 for l_loop_count in 1..l_tax_rowid.count loop
486
487 -- set savepoint
488 savepoint taxup_savepoint;
489
490 -- clear the debug stack for each asset
491 FA_DEBUG_PKG.Initialize;
492 -- reset the message level to prevent bogus errors
493 FA_SRVR_MSG.Set_Message_Level(message_level => 10, p_log_level_rec => g_log_level_rec);
494
495 l_mesg_name := null;
496 fa_srvr_msg.add_message(
497 calling_fn => NULL,
498 name => 'FA_SHARED_ASSET_NUMBER',
499 token1 => 'NUMBER',
500 value1 => l_asset_number(l_loop_count),
501 p_log_level_rec => g_log_level_rec);
502
503 <<taxupload_records>>
504 BEGIN
505
506 -- reset the structs to null
507
508 l_trans_rec := NULL;
509 l_asset_hdr_rec := NULL;
510 l_asset_fin_rec_adj := NULL;
511 l_asset_fin_rec_new := NULL;
512 l_asset_fin_mrc_tbl_new.delete;
513 l_inv_trans_rec := NULL;
514 l_inv_tbl.delete;
515 l_asset_deprn_rec_adj := NULL;
516 l_asset_deprn_rec_new := NULL;
517 l_asset_deprn_mrc_tbl_new.delete;
518
519 -- reset the who info in trans rec
520 l_trans_rec.who_info.last_updated_by := FND_GLOBAL.USER_ID;
521 l_trans_rec.who_info.created_by := FND_GLOBAL.USER_ID;
522 l_trans_rec.who_info.creation_date := sysdate;
523 l_trans_rec.who_info.last_update_date := sysdate;
524 l_trans_rec.who_info.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
525 l_trans_rec.mass_reference_id := p_parent_request_id;
526 l_trans_rec.calling_interface := 'FATAXUP';
527
528
529
530 -- Changes made as per the ER No.s 6606548 and 6606552 by Vkukutam Start
531 -- JP_TAX_2012
532 if l_deprn_method_code(l_loop_count) like 'JP-2%0DB%' then
533 l_asset_fin_rec_adj.nbv_at_switch := l_nbv_at_switch(l_loop_count);
534 l_asset_fin_rec_adj.period_full_reserve := l_period_full_reserve(l_loop_count);
535 l_asset_fin_rec_adj.prior_deprn_limit_amount := NULL;
536 l_asset_fin_rec_adj.prior_deprn_method := NULL;
537 l_asset_fin_rec_adj.period_extd_deprn := NULL;
538 l_asset_fin_rec_adj.prior_deprn_limit := NULL;
539 l_asset_fin_rec_adj.prior_basic_rate := NULL;
540 l_asset_fin_rec_adj.prior_adjusted_rate := NULL;
541 l_asset_fin_rec_adj.prior_life_in_months := NULL;
542 l_asset_fin_rec_adj.prior_deprn_limit_type := NULL;
543 elsif l_deprn_method_code(l_loop_count) = 'JP-STL-EXTND' then
544 l_asset_fin_rec_adj.nbv_at_switch := NULL;
545 l_asset_fin_rec_adj.period_full_reserve := l_period_full_reserve(l_loop_count);
546 l_asset_fin_rec_adj.prior_deprn_limit_amount := l_prior_deprn_limit_amount(l_loop_count);
547 l_asset_fin_rec_adj.prior_deprn_method := l_prior_deprn_method(l_loop_count);
548 l_asset_fin_rec_adj.period_extd_deprn := l_period_extd_deprn(l_loop_count);
549 l_asset_fin_rec_adj.prior_deprn_limit := l_prior_deprn_limit(l_loop_count);
550 l_asset_fin_rec_adj.prior_basic_rate := l_prior_basic_rate(l_loop_count);
551 l_asset_fin_rec_adj.prior_adjusted_rate := l_prior_adjusted_rate(l_loop_count);
552 l_asset_fin_rec_adj.prior_life_in_months := l_prior_life_in_months(l_loop_count);
553 l_asset_fin_rec_adj.prior_deprn_limit_type := l_prior_deprn_limit_type(l_loop_count);
554 if l_prior_deprn_limit_type(l_loop_count) = 'AMT' then
555 l_asset_fin_rec_adj.prior_deprn_limit := NULL;
556 elsif l_prior_deprn_limit_type(l_loop_count) = 'PCT' then
557 l_asset_fin_rec_adj.prior_deprn_limit_amount := NULL;
558 else
559 l_asset_fin_rec_adj.prior_deprn_limit := NULL;
560 l_asset_fin_rec_adj.prior_deprn_limit_amount := NULL;
561 end if;
562 else
563 l_asset_fin_rec_adj.nbv_at_switch := l_nbv_at_switch(l_loop_count);
564 l_asset_fin_rec_adj.period_full_reserve := l_period_full_reserve(l_loop_count);
565 l_asset_fin_rec_adj.prior_deprn_limit_amount := NULL;
566 l_asset_fin_rec_adj.prior_deprn_method := NULL;
567 l_asset_fin_rec_adj.period_extd_deprn := NULL;
568 l_asset_fin_rec_adj.prior_deprn_limit := NULL;
569 l_asset_fin_rec_adj.prior_basic_rate := NULL;
570 l_asset_fin_rec_adj.prior_adjusted_rate := NULL;
571 l_asset_fin_rec_adj.prior_life_in_months := NULL;
572 l_asset_fin_rec_adj.prior_deprn_limit_type := NULL;
573 end if;
574 -- Changes made as per the ER No.s 6606548 and 6606552 by Vkukutam End
575
576 -- counter for the number of assets
577 l_count := l_count + 1;
578
579 if (l_debug) then
580 fa_debug_pkg.add(l_calling_fn,
581 'asset_number',l_asset_number(l_loop_count));
582 fa_debug_pkg.add(l_calling_fn,
583 'asset_id',l_asset_id(l_loop_count));
584 end if;
585 -- Retrieve additions_info, to retrieve asset id and shared info
586 -- across books for the asset
587
588 -- verify asset is capitalized
589 if (l_asset_type(l_loop_count) not in ('CAPITALIZED', 'GROUP')) then
590 l_mesg_name := 'FA_NO_TAX_UPLOAD_CIP';
591 raise data_error;
592 end if;
593
594 -- set all the structures up with deltas
595 l_asset_hdr_rec.asset_id := l_asset_id(l_loop_count);
596 l_asset_hdr_rec.book_type_code := p_book_type_code;
597
598 l_asset_fin_rec_adj.adjusted_rate :=
599 l_adjusted_rate(l_loop_count);
600 l_asset_fin_rec_adj.basic_rate :=
601 l_basic_rate(l_loop_count);
602 l_asset_fin_rec_adj.bonus_rule :=
603 l_bonus_rule(l_loop_count);
604 l_asset_fin_rec_adj.ceiling_name :=
605 l_ceiling_name(l_loop_count);
606 l_asset_fin_rec_adj.cost :=
607 l_cost(l_loop_count);
608 l_asset_fin_rec_adj.date_placed_in_service :=
609 l_date_placed_in_service(l_loop_count);
610 l_asset_fin_rec_adj.depreciate_flag :=
611 l_depreciate_flag(l_loop_count);
612 l_asset_fin_rec_adj.deprn_method_code :=
613 l_deprn_method_code(l_loop_count);
614 l_asset_fin_rec_adj.itc_amount_id :=
615 l_itc_amount_id(l_loop_count);
616 l_asset_fin_rec_adj.life_in_months :=
617 l_life_in_months(l_loop_count);
618 l_asset_fin_rec_adj.original_cost :=
619 l_original_cost(l_loop_count);
620 l_asset_fin_rec_adj.production_capacity :=
621 l_production_capacity(l_loop_count);
622 l_asset_fin_rec_adj.prorate_convention_code :=
623 l_prorate_convention_code(l_loop_count);
624 l_asset_fin_rec_adj.salvage_value :=
625 l_salvage_value(l_loop_count);
626 l_asset_fin_rec_adj.short_fiscal_year_flag :=
627 l_short_fiscal_year_flag(l_loop_count);
628 l_asset_fin_rec_adj.conversion_date :=
629 l_conversion_date(l_loop_count);
630 l_asset_fin_rec_adj.orig_deprn_start_date :=
631 l_original_deprn_start_date(l_loop_count);
632 l_asset_fin_rec_adj.fully_rsvd_revals_counter :=
633 l_fully_rsvd_revals_counter(l_loop_count);
634 l_asset_fin_rec_adj.unrevalued_cost :=
635 l_unrevalued_cost(l_loop_count);
636 l_asset_fin_rec_adj.reval_ceiling :=
637 l_reval_ceiling(l_loop_count);
638 l_asset_deprn_rec_adj.deprn_reserve :=
639 l_deprn_reserve(l_loop_count);
640 l_asset_deprn_rec_adj.ytd_deprn :=
641 l_ytd_deprn(l_loop_count);
642 l_asset_deprn_rec_adj.reval_amortization_basis :=
643 l_reval_amortization_basis(l_loop_count);
644 l_asset_deprn_rec_adj.reval_deprn_reserve :=
645 l_reval_reserve(l_loop_count);
646 l_asset_deprn_rec_adj.reval_ytd_deprn :=
647 l_ytd_reval_deprn_expense(l_loop_count);
648 l_trans_rec.transaction_subtype :=
649 l_transaction_subtype(l_loop_count);
650 l_trans_rec.amortization_start_date := -- trx date entered?
651 l_amortization_start_date(l_loop_count);
652 l_trans_rec.transaction_name :=
653 l_transaction_name(l_loop_count);
654 l_trans_rec.desc_flex.attribute1 :=
655 l_attribute1(l_loop_count);
656 l_trans_rec.desc_flex.attribute2 :=
657 l_attribute2(l_loop_count);
658 l_trans_rec.desc_flex.attribute3 :=
659 l_attribute3(l_loop_count);
660 l_trans_rec.desc_flex.attribute4 :=
661 l_attribute4(l_loop_count);
662 l_trans_rec.desc_flex.attribute5 :=
663 l_attribute5(l_loop_count);
664 l_trans_rec.desc_flex.attribute6 :=
665 l_attribute6(l_loop_count);
666 l_trans_rec.desc_flex.attribute7 :=
667 l_attribute7(l_loop_count);
668 l_trans_rec.desc_flex.attribute8 :=
669 l_attribute8(l_loop_count);
670 l_trans_rec.desc_flex.attribute9 :=
671 l_attribute9(l_loop_count);
672 l_trans_rec.desc_flex.attribute10 :=
673 l_attribute10(l_loop_count);
674 l_trans_rec.desc_flex.attribute11 :=
675 l_attribute11(l_loop_count);
676 l_trans_rec.desc_flex.attribute12 :=
677 l_attribute12(l_loop_count);
678 l_trans_rec.desc_flex.attribute13 :=
679 l_attribute13(l_loop_count);
680 l_trans_rec.desc_flex.attribute14 :=
681 l_attribute14(l_loop_count);
682 l_trans_rec.desc_flex.attribute15 :=
683 l_attribute15(l_loop_count);
684 l_trans_rec.desc_flex.attribute_category_code :=
685 l_attribute_category_code(l_loop_count);
686 l_asset_fin_rec_adj.global_attribute1 :=
687 l_global_attribute1(l_loop_count);
688 l_asset_fin_rec_adj.global_attribute2 :=
689 l_global_attribute2(l_loop_count);
690 l_asset_fin_rec_adj.global_attribute3 :=
691 l_global_attribute3(l_loop_count);
692 l_asset_fin_rec_adj.global_attribute4 :=
693 l_global_attribute4(l_loop_count);
694 l_asset_fin_rec_adj.global_attribute5 :=
695 l_global_attribute5(l_loop_count);
696 l_asset_fin_rec_adj.global_attribute6 :=
697 l_global_attribute6(l_loop_count);
698 l_asset_fin_rec_adj.global_attribute7 :=
699 l_global_attribute7(l_loop_count);
700 l_asset_fin_rec_adj.global_attribute8 :=
701 l_global_attribute8(l_loop_count);
702 l_asset_fin_rec_adj.global_attribute9 :=
703 l_global_attribute9(l_loop_count);
704 l_asset_fin_rec_adj.global_attribute10 :=
705 l_global_attribute10(l_loop_count);
706 l_asset_fin_rec_adj.global_attribute11 :=
707 l_global_attribute11(l_loop_count);
708 l_asset_fin_rec_adj.global_attribute12 :=
709 l_global_attribute12(l_loop_count);
710 l_asset_fin_rec_adj.global_attribute13 :=
711 l_global_attribute13(l_loop_count);
712 l_asset_fin_rec_adj.global_attribute14 :=
713 l_global_attribute14(l_loop_count);
714 l_asset_fin_rec_adj.global_attribute15 :=
715 l_global_attribute15(l_loop_count);
716 l_asset_fin_rec_adj.global_attribute16 :=
717 l_global_attribute16(l_loop_count);
718 l_asset_fin_rec_adj.global_attribute17 :=
719 l_global_attribute17(l_loop_count);
720 l_asset_fin_rec_adj.global_attribute18 :=
721 l_global_attribute18(l_loop_count);
722 l_asset_fin_rec_adj.global_attribute19 :=
723 l_global_attribute19(l_loop_count);
724 l_asset_fin_rec_adj.global_attribute20 :=
725 l_global_attribute20(l_loop_count);
726 l_asset_fin_rec_adj.global_attribute_category:=
727 l_global_attribute_category(l_loop_count);
728 l_asset_fin_rec_adj.group_asset_id :=
729 l_group_asset_id(l_loop_count);
730
731 -- load the current fin and deprn info
732 if not FA_UTIL_PVT.get_asset_fin_rec
733 (p_asset_hdr_rec => l_asset_hdr_rec,
734 px_asset_fin_rec => l_asset_fin_rec_old,
735 p_transaction_header_id => NULL,
736 p_mrc_sob_type_code => 'P'
737 , p_log_level_rec => g_log_level_rec) then
738 raise data_error;
739 end if;
740
741 if not FA_UTIL_PVT.get_asset_deprn_rec
742 (p_asset_hdr_rec => l_asset_hdr_rec,
743 px_asset_deprn_rec => l_asset_deprn_rec_old,
744 p_period_counter => NULL,
745 p_mrc_sob_type_code => 'P'
746 , p_log_level_rec => g_log_level_rec) then
747 raise data_error;
748 end if;
749
750 -- Bug 6803812: Check whether it is period of addition
751 if not FA_ASSET_VAL_PVT.validate_period_of_addition
752 (p_asset_id => l_asset_hdr_rec.asset_id,
753 p_book => l_asset_hdr_rec.book_type_code,
754 p_mode => 'ABSOLUTE',
755 px_period_of_addition => l_asset_hdr_rec.period_of_addition,
756 p_log_level_rec => g_log_level_rec) then
757 raise data_error;
758 end if;
759
760 if (l_asset_hdr_rec.period_of_addition <> 'Y' or
761 G_release = 11) then
762 -- now fetch any existing catchup expense in fa_adjustments
763 -- and account for this when calculating the old deprn values
764 -- only if it is not in period of addition
765
766 select nvl(sum(decode(debit_credit_flag,
767 'DR', adjustment_amount,
768 -adjustment_amount)), 0)
769 into l_deprn_exp_amort_nbv
770 from fa_adjustments
771 where book_type_code = l_asset_hdr_rec.book_type_code
772 and asset_id = l_asset_hdr_rec.asset_id
773 and source_type_code = 'DEPRECIATION'
774 and adjustment_type = 'EXPENSE';
775
776
777 l_asset_deprn_rec_old.deprn_reserve := l_asset_deprn_rec_old.deprn_reserve -
778 l_deprn_exp_amort_nbv;
779 l_asset_deprn_rec_old.ytd_deprn := l_asset_deprn_rec_old.ytd_deprn -
780 l_deprn_exp_amort_nbv;
781 end if;
782
783 -- Set all non-calculated and non-method info
784 -- the amount columns are delta's so take the difference
785 -- between upload value and current value
786
787 if (l_asset_fin_rec_adj.salvage_value is not null) then
788 if (l_asset_fin_rec_old.salvage_type = 'AMT') then
789 l_asset_fin_rec_adj.salvage_value := nvl(l_asset_fin_rec_adj.salvage_value,
790 l_asset_fin_rec_old.salvage_value) -
791 l_asset_fin_rec_old.salvage_value;
792 else
793 l_asset_fin_rec_adj.salvage_value := l_asset_fin_rec_adj.salvage_value;
794 l_asset_fin_rec_adj.salvage_type := 'AMT';
795 end if;
796 end if;
797
798
799 l_asset_fin_rec_adj.production_capacity := nvl(l_asset_fin_rec_adj.production_capacity,
800 l_asset_fin_rec_old.production_capacity) -
801 l_asset_fin_rec_old.production_capacity;
802 l_asset_fin_rec_adj.cost := nvl(l_asset_fin_rec_adj.cost,
803 l_asset_fin_rec_old.cost) -
804 l_asset_fin_rec_old.cost;
805 l_asset_fin_rec_adj.original_cost := nvl(l_asset_fin_rec_adj.original_cost,
806 l_asset_fin_rec_old.original_cost) -
807 l_asset_fin_rec_old.original_cost;
808 if (l_asset_fin_rec_adj.unrevalued_cost is not null) then
809 l_asset_fin_rec_adj.unrevalued_cost := l_asset_fin_rec_adj.unrevalued_cost -
810 l_asset_fin_rec_old.unrevalued_cost;
811 end if;
812
813 l_asset_fin_rec_adj.reval_ceiling := nvl(l_asset_fin_rec_adj.reval_ceiling,
814 l_asset_fin_rec_old.reval_ceiling) -
815 l_asset_fin_rec_old.reval_ceiling;
816 l_asset_deprn_rec_adj.deprn_reserve := nvl(l_asset_deprn_rec_adj.deprn_reserve,
817 l_asset_deprn_rec_old.deprn_reserve) -
818 l_asset_deprn_rec_old.deprn_reserve;
819 l_asset_deprn_rec_adj.ytd_deprn := nvl(l_asset_deprn_rec_adj.ytd_deprn,
820 l_asset_deprn_rec_old.ytd_deprn) -
821 l_asset_deprn_rec_old.ytd_deprn;
822 l_asset_deprn_rec_adj.reval_amortization_basis := nvl(l_asset_deprn_rec_adj.reval_amortization_basis,
823 l_asset_deprn_rec_old.reval_amortization_basis) -
824 l_asset_deprn_rec_old.reval_amortization_basis;
825 l_asset_deprn_rec_adj.reval_deprn_reserve := nvl(l_asset_deprn_rec_adj.reval_deprn_reserve,
826 l_asset_deprn_rec_old.reval_deprn_reserve) -
827 l_asset_deprn_rec_old.reval_deprn_reserve;
828 l_asset_deprn_rec_adj.reval_ytd_deprn := nvl(l_asset_deprn_rec_adj.reval_ytd_deprn,
829 l_asset_deprn_rec_old.reval_ytd_deprn) -
830 l_asset_deprn_rec_old.reval_ytd_deprn;
831
832 -- round those values holding currency amounts
833
834 fa_round_pkg.fa_round(l_asset_fin_rec_adj.salvage_value,
835 p_book_type_code, p_log_level_rec => g_log_level_rec);
836 fa_round_pkg.fa_round(l_asset_fin_rec_adj.cost,
837 p_book_type_code, p_log_level_rec => g_log_level_rec);
838 fa_round_pkg.fa_round(l_asset_fin_rec_adj.original_cost,
839 p_book_type_code, p_log_level_rec => g_log_level_rec);
840 fa_round_pkg.fa_round(l_asset_fin_rec_adj.unrevalued_cost,
841 p_book_type_code, p_log_level_rec => g_log_level_rec);
842 fa_round_pkg.fa_round(l_asset_fin_rec_adj.reval_ceiling,
843 p_book_type_code, p_log_level_rec => g_log_level_rec);
844 fa_round_pkg.fa_round(l_asset_deprn_rec_adj.deprn_reserve,
845 p_book_type_code, p_log_level_rec => g_log_level_rec);
846 fa_round_pkg.fa_round(l_asset_deprn_rec_adj.ytd_deprn,
847 p_book_type_code, p_log_level_rec => g_log_level_rec);
848 fa_round_pkg.fa_round(l_asset_deprn_rec_adj.reval_amortization_basis,
849 p_book_type_code, p_log_level_rec => g_log_level_rec);
850 fa_round_pkg.fa_round(l_asset_deprn_rec_adj.reval_deprn_reserve,
851 p_book_type_code, p_log_level_rec => g_log_level_rec);
852 fa_round_pkg.fa_round(l_asset_deprn_rec_adj.reval_ytd_deprn,
853 p_book_type_code, p_log_level_rec => g_log_level_rec);
854
855
856 -- Changes made as per the ER No.s 6606548 and 6606552
857 -- Bug 8722521 : Moved the validations to FAVCALB.pls
858 if l_japan_tax_reform = 'Y' then
859
860 -- Bug 9244648 : pc_fully_reserved should be populated
861 -- for all the methods
862 if l_period_full_reserve(l_loop_count) is not null then
863 l_period_end_dt := null;
864 l_period_num := null;
865 l_num_fy_yr := null;
866 l_p_rsv_counter := null;
867 open l_period_info(p_book_type_code
868 ,l_period_full_reserve(l_loop_count));
869 fetch l_period_info into l_period_end_dt
870 ,l_fiscal_yr
871 ,l_period_num
872 ,l_num_fy_yr;
873 close l_period_info;
874
875 if l_fiscal_yr is null then
876 raise data_error;
877 end if;
878
879 l_p_rsv_counter := (l_fiscal_yr * l_num_fy_yr) + l_period_num;
880 l_asset_fin_rec_adj.period_counter_fully_reserved := l_p_rsv_counter;
881 IF l_asset_fin_rec_adj.period_counter_life_complete IS NULL AND
882 l_p_rsv_counter IS NOT NULL THEN
883 l_asset_fin_rec_adj.period_counter_life_complete := l_p_rsv_counter;
884 END IF;
885 end if;
886
887 if l_deprn_method_code(l_loop_count)='JP-STL-EXTND' then
888
889 -- Start extd deprn period
890 l_period_end_dt := null;
891 l_period_num := null;
892 l_period_end_dt := null;
893 l_num_fy_yr := null;
894 l_ext_period_counter := null;
895 open l_period_info(p_book_type_code
896 ,l_period_extd_deprn(l_loop_count));
897 fetch l_period_info into l_period_end_dt
898 ,l_fiscal_yr
899 ,l_period_num
900 ,l_num_fy_yr;
901 close l_period_info;
902
903 l_ext_period_counter := (l_fiscal_yr * l_num_fy_yr) + l_period_num; -- end
904 l_asset_fin_rec_adj.extended_depreciation_period := l_ext_period_counter;
905
906 l_asset_fin_rec_adj.deprn_limit_type := 'AMT';
907 l_asset_fin_rec_adj.allowed_deprn_limit_amount := 1;
908 l_asset_fin_rec_adj.allowed_deprn_limit := null;
909 l_asset_fin_rec_adj.extended_deprn_flag := 'Y';
910 -- Bug#9948586: Populating prior_deprn_limit_amount
911 if l_prior_deprn_limit_type(l_loop_count) = 'PCT' then
912 --bug 10231000 called the procedure fa_round_pkg.fa_ceil for correct currency formatting
913 l_limit_amt := nvl(l_old_cost(l_loop_count),0) * ( 1 - l_prior_deprn_limit(l_loop_count));
914 fa_round_pkg.fa_ceil(l_limit_amt,p_book_type_code);
915 l_asset_fin_rec_adj.prior_deprn_limit_amount := l_limit_amt;
916 end if;
917 end if;
918 end if; -- Japan profile option enable if end
919 --Changes made as per the ER No.s 6606548 and 6606552
920
921 -- Bug 6795070 : l_asset_fin_rec_adj.reval_amortization_basis also
922 -- needs to be populated before calling adj api
923 l_asset_fin_rec_adj.reval_amortization_basis := l_asset_deprn_rec_adj.reval_amortization_basis;
924
925 -- set up other needed struct values
926 l_trans_rec.mass_reference_id := l_request_id;
927
928 --Change for 9371739
929 IF ((l_deprn_reserve(l_loop_count) <> 0) OR (l_ytd_deprn(l_loop_count) <> 0)) AND
930 (l_asset_hdr_rec.period_of_addition = 'Y') AND
931 (l_asset_deprn_rec_old.deprn_reserve = l_deprn_reserve(l_loop_count)) AND
932 (l_asset_type(l_loop_count) = 'CAPITALIZED')THEN
933
934 BEGIN
935
936 SELECT ytd_deprn,deprn_reserve
937 INTO l_book_ytd,l_book_rsv
938 FROM fa_deprn_summary
939 WHERE asset_id = l_asset_hdr_rec.asset_id
940 AND book_type_code = l_asset_hdr_rec.book_type_code
941 AND deprn_source_code = 'BOOKS';
942
943 EXCEPTION
944 WHEN OTHERS THEN
945 RAISE data_error;
946 END;
947
948 IF (l_book_ytd = 0 ) OR (l_book_rsv = 0) THEN
949 l_asset_deprn_rec_adj.allow_taxup_flag := TRUE;
950 END IF;
951
952 END IF;
953 --end of change for 9371739
954
955 -- perform the Adjustment
956 fa_adjustment_pub.do_adjustment
957 (p_api_version => l_api_version,
958 p_init_msg_list => l_init_msg_list,
959 p_commit => l_commit,
960 p_validation_level => l_validation_level,
961 x_return_status => l_return_status,
962 x_msg_count => l_mesg_count,
963 x_msg_data => l_mesg,
964 p_calling_fn => l_calling_fn,
965 px_trans_rec => l_trans_rec,
966 px_asset_hdr_rec => l_asset_hdr_rec,
967 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
968 x_asset_fin_rec_new => l_asset_fin_rec_new,
969 x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
970 px_inv_trans_rec => l_inv_trans_rec,
971 px_inv_tbl => l_inv_tbl,
972 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
973 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
974 x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
975 p_group_reclass_options_rec => l_group_reclass_options_rec
976 );
977
978 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
979 raise data_error;
980 end if;
981
982 -- flag interface record as posted
983 update fa_tax_interface
984 set posting_status = 'POSTED',
985 tax_request_id = l_request_id
986 where rowid = l_tax_rowid(l_loop_count);
987
988 -- Increment asset count and dump asset_number to the log file
989 x_success_count := x_success_count + 1;
990 write_message(l_asset_number(l_loop_count),
991 'FA_MCP_ADJUSTMENT_SUCCESS');
992
993 if (l_debug) then
994 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
995 end if;
996
997
998 EXCEPTION -- exceptions for taxupload_records block
999
1000 when data_error then
1001 x_failure_count := x_failure_count + 1;
1002
1003 write_message(l_asset_number(l_loop_count),
1004 l_mesg_name);
1005
1006 if (l_debug) then
1007 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
1008 end if;
1009
1010 rollback to savepoint taxup_savepoint;
1011
1012 -- flag interface record as failed
1013 update fa_tax_interface
1014 set posting_status = 'ERROR',
1015 tax_request_id = l_request_id
1016 where rowid = l_tax_rowid(l_loop_count);
1017 when others then
1018 x_failure_count := x_failure_count + 1;
1019
1020 write_message(l_asset_number(l_loop_count),
1021 'FA_TAXUP_FAIL_TRX');
1022 fa_srvr_msg.add_sql_error(
1023 calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1024
1025 if (l_debug) then
1026 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
1027 end if;
1028
1029 rollback to savepoint taxup_savepoint;
1030
1031 -- flag interface record as failed
1032 update fa_tax_interface
1033 set posting_status = 'ERROR',
1034 tax_request_id = l_request_id
1035 where rowid = l_tax_rowid(l_loop_count);
1036
1037 END; -- end taxupload_records block
1038
1039 -- FND_CONCURRENT.AF_COMMIT every batch and reset the large rollback segment
1040 FND_CONCURRENT.AF_COMMIT;
1041
1042 end loop; -- inner loop to loop through arrays
1043
1044
1045 px_max_asset_id := l_asset_id(l_asset_id.count);
1046 x_return_status := 0;
1047
1048 EXCEPTION
1049 when done_exc then
1050 x_return_status := 0;
1051
1052 when faxtaxup_err then
1053 FND_CONCURRENT.AF_ROLLBACK;
1054 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1055
1056 -- Dump Debug messages when run in debug mode to log file
1057 if (l_debug) then
1058 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
1059 end if;
1060
1061 x_return_status := 2;
1062
1063 when others then
1064 FND_CONCURRENT.AF_ROLLBACK;
1065 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1066
1067 -- Dump Debug messages when run in debug mode to log file
1068 if (l_debug) then
1069 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
1070 end if;
1071
1072 x_return_status := 2;
1073
1074 END faxtaxup; -- end taxupload_main block
1075
1076 -----------------------------------------------------------------------------
1077
1078 PROCEDURE write_message
1079 (p_asset_number in varchar2,
1080 p_message in varchar2) IS
1081
1082 l_message varchar2(30);
1083 l_mesg varchar2(100);
1084 l_string varchar2(512);
1085 l_calling_fn varchar2(40); -- conditionally populated below
1086
1087 BEGIN
1088
1089 -- first dump the message to the output file
1090 -- set/translate/retrieve the mesg from fnd
1091
1092 l_message := nvl(p_message, 'FA_TAXUP_FAIL_TRX');
1093
1094 if (l_message <> 'FA_MCP_ADJUSTMENT_SUCCESS') then
1095 l_calling_fn := 'fa_masschg_pkg.do_mass_change';
1096 end if;
1097
1098
1099
1100 -- now process the messages for the log file
1101 fa_srvr_msg.add_message
1102 (calling_fn => l_calling_fn,
1103 name => l_message, p_log_level_rec => g_log_level_rec);
1104
1105 EXCEPTION
1106 when others then
1107 raise;
1108
1109 END write_message;
1110
1111 ----------------------------------------------------------------
1112
1113 -- This function will select all candidate lines in a single
1114 -- shot (no longer distinguishes between parent / child). The primary
1115 -- cursors have removed MOD logic
1116 -- We will only stripe the worker number based on the following order:
1117 --
1118 -- In the initial phase, we will use a mod as before with precedence:
1119 -- group / non-group
1120 --
1121
1122
1123 PROCEDURE allocate_workers (
1124 p_book_type_code IN VARCHAR2,
1125 p_parent_request_id IN NUMBER,
1126 p_total_requests IN NUMBER,
1127 x_return_status OUT NOCOPY NUMBER) IS
1128
1129 -- Used for bulk fetching
1130 l_batch_size number;
1131
1132 l_rowid_tbl char_tbl_type ;
1133 l_group_asset_id_tbl num_tbl_type ;
1134 l_worker_id_tbl num_tbl_type ;
1135 l_asset_id_tbl num_tbl_type ;
1136 l_asset_type_tbl char_tbl_type ;
1137
1138 cursor c_group_asset is
1139 select tax.rowid,
1140 nvl(tax.group_asset_id,
1141 bk.group_asset_id)
1142 from fa_tax_interface tax,
1143 fa_additions_b ad,
1144 fa_books bk
1145 where tax.book_type_code = p_book_type_code
1146 and tax.posting_status = 'POST'
1147 and tax.asset_number = ad.asset_number
1148 and bk.asset_id = ad.asset_id
1149 and bk.book_type_code = p_book_type_code
1150 and bk.transaction_header_id_out is null;
1151
1152 cursor c_tax_interface is
1153 select tax.rowid,
1154 mod(nvl(tax.group_asset_id,
1155 nvl(ad.asset_id, 1)), p_total_requests) + 1,
1156 nvl(ad.asset_id, 1),
1157 ad.asset_type
1158 from fa_tax_interface tax,
1159 fa_additions_b ad
1160 where tax.book_type_code = p_book_type_code
1161 and tax.posting_status = 'POST'
1162 and ad.asset_number(+) = tax.asset_number;
1163
1164 taxup_err exception;
1165 l_calling_fn varchar2(40) := 'fa_tax_upload_pkg.allocate_workers';
1166
1167 BEGIN
1168
1169
1170 if (not g_log_level_rec.initialized) then
1171 if (NOT fa_util_pub.get_log_level_rec (
1172 x_log_level_rec => g_log_level_rec
1173 )) then
1174 raise taxup_err;
1175 end if;
1176 end if;
1177
1178 if(g_log_level_rec.statement_level) then
1179 fa_debug_pkg.add(l_calling_fn, 'at beginning of', 'worker allocation', p_log_level_rec => g_log_level_rec);
1180 end if;
1181
1182 x_return_status := 0;
1183
1184 -- get corp book information
1185 if not fa_cache_pkg.fazcbc(X_book => p_book_type_code, p_log_level_rec => g_log_level_rec) then
1186 raise taxup_err;
1187 end if;
1188
1189 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
1190
1191
1192 -- update the group asset if applicable and currently null
1193 if (fa_cache_pkg.fazcbc_record.allow_group_deprn_flag = 'Y') then
1194
1195 open c_group_asset;
1196
1197 loop
1198
1199 fetch c_group_asset bulk collect
1200 into l_rowid_tbl,
1201 l_group_asset_id_tbl
1202 limit l_batch_size;
1203
1204 if (l_rowid_tbl.count = 0) then
1205 exit;
1206 end if;
1207
1208 forall i in 1..l_rowid_tbl.count
1209 update fa_tax_interface
1210 set group_asset_id = l_group_asset_id_tbl(i)
1211 where rowid = l_rowid_tbl(i);
1212
1213 end loop;
1214
1215 end if;
1216
1217
1218 -- now assign the workers
1219 open c_tax_interface;
1220
1221 loop
1222
1223 fetch c_tax_interface bulk collect
1224 into l_rowid_tbl,
1225 l_worker_id_tbl,
1226 l_asset_id_tbl,
1227 l_asset_type_tbl
1228 limit l_batch_size;
1229
1230 if (l_rowid_tbl.count = 0) then
1231 exit;
1232 end if;
1233
1234 forall i in 1..l_rowid_tbl.count
1235 update fa_tax_interface
1236 set asset_id = l_asset_id_tbl(i),
1237 asset_type = l_asset_type_tbl(i),
1238 worker_id = l_worker_id_tbl(i),
1239 tax_request_id = p_parent_request_id
1240 where rowid = l_rowid_tbl(i);
1241
1242 -- need to add check for valid asset here...
1243 -- if outer joins fails to find asset, list it here so we
1244 -- don't have to outer join in the first query
1245
1246
1247 end loop;
1248
1249 close c_tax_interface;
1250
1251 if (g_log_level_rec.statement_level) then
1252 fa_debug_pkg.add(l_calling_fn, 'rows updated into fa_tax_interface', sql%rowcount);
1253 end if;
1254
1255 FND_CONCURRENT.AF_COMMIT;
1256
1257 x_return_status := 0;
1258
1259 EXCEPTION
1260 WHEN taxup_err THEN
1261 FND_CONCURRENT.AF_ROLLBACK;
1262 fa_srvr_msg.add_message (calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1263 X_return_status := 2;
1264
1265 WHEN OTHERS THEN
1266 FND_CONCURRENT.AF_ROLLBACK;
1267 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1268 x_return_status := 2;
1269
1270 END allocate_workers;
1271
1272 END FA_TAX_UPLOAD_PKG;