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