[Home] [Help]
PACKAGE BODY: APPS.FA_MASSCHG_PKG
Source
1 PACKAGE BODY FA_MASSCHG_PKG as
2 /* $Header: FAMACHB.pls 120.27 2010/10/30 12:06:08 saalampa ship $ */
3
4 g_log_level_rec fa_api_types.log_level_rec_type;
5
6 PROCEDURE do_mass_change (
7 p_mass_change_id IN NUMBER,
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 -- used for bulk fetching
17 l_batch_size number;
18 l_loop_count number;
19
20 -- local variables
21 l_count number;
22 l_request_id number := -1;
23 l_userid number := -1;
24 l_login number := -1;
25 l_trx_approval boolean;
26 l_masschg_status varchar2(10);
27 l_book_type_code varchar2(30);
28 l_uom_change boolean;
29 l_period_of_addition varchar2(1);
30
31 -- used for method cache
32 l_from_rsr VARCHAR2(10);
33 l_to_rsr VARCHAR2(10);
34
35 -- local variables
36 TYPE v30_tbl IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
37 TYPE num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
38 TYPE date_tbl IS TABLE OF DATE INDEX BY BINARY_INTEGER;
39
40
41 l_asset_number v30_tbl;
42 l_asset_id num_tbl;
43 l_conversion_date date_tbl;
44 l_date_effective date_tbl;
45 l_date_placed_in_service date_tbl;
46 l_group_asset_id num_tbl;
47
48 l_from_convention varchar2(10);
49 l_to_convention varchar2(10);
50 l_from_method_code varchar2(12);
51 l_to_method_code varchar2(12);
52 l_from_life_in_months number;
53 l_to_life_in_months number;
54 l_from_bonus_rule varchar2(30);
55 l_to_bonus_rule varchar2(30);
56 l_mass_date_effective date;
57 l_trx_date_entered date;
58 l_from_basic_rate number;
59 l_to_basic_rate number;
60 l_from_adjusted_rate number;
61 l_to_adjusted_rate number;
62 l_from_production_capacity number;
63 l_to_production_capacity number;
64 l_from_uom varchar2(25);
65 l_to_uom varchar2(25);
66 l_from_group_association varchar2(30);
67 l_to_group_association varchar2(30);
68 l_from_group_asset_id number;
69 l_to_group_asset_id number;
70 l_asset_type varchar2(30);
71 l_amortize_flag varchar2(1);
72 l_allow_overlapping_adj_flag varchar2(1);
73
74 l_from_salvage_type varchar2(30);
75 l_to_salvage_type varchar2(30);
76 l_from_percent_salvage_value number;
77 l_to_percent_salvage_value number;
78 l_from_salvage_value number;
79 l_to_salvage_value number;
80 l_from_deprn_limit_type varchar2(30);
81 l_to_deprn_limit_type varchar2(30);
82 l_from_deprn_limit number;
83 l_to_deprn_limit number;
84 l_from_deprn_limit_amount number;
85 l_to_deprn_limit_amount number;
86 l_from_depreciate_flag varchar2(3);
87 l_to_depreciate_flag varchar2(3);
88
89 l_amortization_start_date date;
90 l_old_amortization_start_date date;
91 l_trxs_exist varchar2(1);
92
93
94 -- variables and structs used for api call
95 l_api_version NUMBER := 1.0;
96 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
97 l_commit VARCHAR2(1) := FND_API.G_FALSE;
98 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
99 l_return_status VARCHAR2(1);
100 l_mesg_count number;
101 l_mesg VARCHAR2(4000);
102 l_calling_fn VARCHAR2(30) := 'fa_masschg_pkg.do_mass_change';
103 l_string varchar2(250);
104
105 l_old_salvage_type varchar2(30);
106 l_old_percent_salvage_value number;
107 l_old_salvage_value number;
108 l_old_deprn_limit_type varchar2(30);
109 l_old_deprn_limit number;
110 l_old_deprn_limit_amount number;
111
112
113 l_period_rec FA_API_TYPES.period_rec_type;
114 l_asset_fin_rec_old FA_API_TYPES.asset_fin_rec_type;
115
116 l_trans_rec FA_API_TYPES.trans_rec_type;
117 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
118 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
119 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
120 l_asset_fin_mrc_tbl_new FA_API_TYPES.asset_fin_tbl_type;
121 l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type;
122 l_inv_tbl FA_API_TYPES.inv_tbl_type;
123 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
124 l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
125 l_asset_deprn_mrc_tbl_new FA_API_TYPES.asset_deprn_tbl_type;
126 l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type;
127
128 l_mesg_name VARCHAR2(30);
129
130 -- mass change info
131 cursor c_mass_change_info is
132 select mch.status,
133 mch.from_convention,
134 mch.to_convention,
135 mch.from_method_code,
136 mch.to_method_code,
137 mch.from_life_in_months,
138 mch.to_life_in_months,
139 mch.from_bonus_rule,
140 mch.to_bonus_rule,
141 mch.date_effective,
142 mch.transaction_date_entered,
143 mch.from_basic_rate,
144 mch.to_basic_rate,
145 mch.from_adjusted_rate,
146 mch.to_adjusted_rate,
147 mch.from_production_capacity,
148 mch.to_production_capacity,
149 mch.from_uom,
150 mch.to_uom,
151 mch.from_group_association,
152 mch.to_group_association,
153 mch.from_group_asset_id,
154 mch.to_group_asset_id,
155 mch.asset_type,
156 mch.amortize_flag,
157 mch.book_type_code,
158 -- ,mch.allow_overlapping_adj_flag`
159 mch.from_salvage_type,
160 mch.to_salvage_type,
161 mch.from_percent_salvage_value,
162 mch.to_percent_salvage_value,
163 mch.from_salvage_value,
164 mch.to_salvage_value,
165 mch.from_deprn_limit_type,
166 mch.to_deprn_limit_type,
167 mch.from_deprn_limit,
168 mch.to_deprn_limit,
169 mch.from_deprn_limit_amount,
170 mch.to_deprn_limit_amount,
171 mch.from_depreciate_flag,
172 mch.to_depreciate_flag
173 from fa_mass_changes mch
174 where mch.mass_change_id = p_mass_change_id;
175
176 -- uom
177 cursor c_assets_uom is
178 select ad.asset_number,
179 bk.asset_id
180 from fa_additions_b ad,
181 fa_books bk,
182 fa_mass_changes mch,
183 fa_methods me
184 where mch.mass_change_id = p_mass_change_id
185 and bk.transaction_header_id_out is null
186 and bk.book_type_code = mch.book_type_code
187 and bk.date_placed_in_service >=
188 nvl(mch.from_date_placed_in_service,
189 bk.date_placed_in_service)
190 and bk.date_placed_in_service <=
191 nvl(mch.to_date_placed_in_service,
192 bk.date_placed_in_service)
193 and bk.period_counter_fully_retired is null
194 and nvl(bk.period_counter_fully_reserved, -1) =
195 decode(mch.change_fully_rsvd_assets, 'YES',
196 nvl(bk.period_counter_fully_reserved, -1), -1)
197 and bk.deprn_method_code =
198 nvl(mch.from_method_code, bk.deprn_method_code)
199 and nvl(bk.production_capacity, -1) =
200 nvl(mch.from_production_capacity,
201 nvl(bk.production_capacity, -1))
202 and nvl(bk.unit_of_measure, -1) =
203 nvl(mch.from_uom,nvl(bk.unit_of_measure, -1))
204 and bk.prorate_convention_code =
205 nvl(mch.from_convention, bk.prorate_convention_code)
206 and ad.asset_number >=
207 nvl(mch.from_asset_number, ad.asset_number)
208 and ad.asset_number <=
209 nvl(mch.to_asset_number, ad.asset_number)
210 and ad.asset_type <> 'CIP'
211 and ad.asset_type = nvl(mch.asset_type, ad.asset_type)
212 and ad.asset_id = bk.asset_id
213 and ad.asset_category_id =
214 nvl(mch.category_id,ad.asset_category_id)
215 and me.method_code = mch.from_method_code
216 and me.rate_source_rule = 'PRODUCTION'
217 and ad.asset_id > px_max_asset_id
218 and MOD(nvl(bk.group_asset_id, ad.asset_id), p_total_requests) = (p_request_number - 1)
219 order by ad.asset_id;
220
221
222 -- non uom
223 cursor c_assets is
224 select ad.asset_number,
225 bk.asset_id,
226 bk.conversion_date,
227 bk.date_effective,
228 bk.date_placed_in_service,
229 bk.group_asset_id
230 from fa_additions_b ad,
231 fa_books bk,
232 fa_mass_changes mch,
233 fa_methods mt --Bug 8928436
234 where ad.asset_number >=
235 nvl(mch.from_asset_number,
236 ad.asset_number)
237 and ad.asset_number <=
238 nvl(mch.to_asset_number,
239 ad.asset_number)
240 and ad.asset_type <> 'CIP'
241 and ad.asset_type = nvl(mch.asset_type, ad.asset_type)
242 and ad.asset_id = bk.asset_id
243 and ad.asset_category_id =
244 nvl(mch.category_id,
245 ad.asset_category_id)
246 and mch.mass_change_id = p_mass_change_id
247 and bk.book_type_code = mch.book_type_code
248 and bk.transaction_header_id_out is null
249 and bk.period_counter_fully_retired is null
250 and nvl(bk.disabled_flag, 'N') = 'N' --HH ed.
251 and nvl(bk.period_counter_fully_reserved,99) =
252 decode(mt.rate_source_rule,'PRODUCTION',nvl(bk.period_counter_fully_reserved,99),nvl(bk.period_counter_life_complete,99)) --Bug 8928436
253 and nvl(bk.period_counter_fully_reserved, -1) =
254 decode(mch.change_fully_rsvd_assets, 'YES',
255 nvl(bk.period_counter_fully_reserved, -1), -1)
256 and bk.date_placed_in_service >=
257 nvl(mch.from_date_placed_in_service,
258 bk.date_placed_in_service)
259 and bk.date_placed_in_service <=
260 nvl(mch.to_date_placed_in_service,
261 bk.date_placed_in_service)
262 and bk.deprn_method_code =
263 nvl(mch.from_method_code,
264 bk.deprn_method_code)
265 and nvl(bk.life_in_months, -1) =
266 nvl(mch.from_life_in_months,
267 nvl(bk.life_in_months, -1))
268 and nvl(bk.basic_rate, -1) =
269 nvl(mch.from_basic_rate,
270 nvl(bk.basic_rate, -1))
271 and nvl(bk.adjusted_rate, -1) =
272 nvl(mch.from_adjusted_rate,
273 nvl(bk.adjusted_rate, -1))
274 and nvl(bk.production_capacity, -1) =
275 nvl(mch.from_production_capacity,
276 nvl(bk.production_capacity, -1))
277 and nvl(bk.unit_of_measure, -1) =
278 nvl(mch.from_uom,
279 nvl(bk.unit_of_measure, -1))
280 and bk.prorate_convention_code =
281 nvl(mch.from_convention,
282 bk.prorate_convention_code)
283 and nvl(bk.bonus_rule, -1) =
284 nvl(mch.from_bonus_rule,
285 nvl(bk.bonus_rule,-1))
286 and ((mch.from_group_association is null) or
287 (mch.from_group_association = 'STANDALONE' and
288 bk.group_asset_id is null) or
289 (mch.from_group_association = 'MEMBER' and
290 nvl(bk.group_asset_id, -99) = mch.from_group_asset_id))
291 and nvl(bk.salvage_type, 'XX') =
292 nvl(mch.from_salvage_type,
293 nvl(bk.salvage_type,'XX'))
294 and nvl(bk.salvage_value, -99) =
295 nvl(mch.from_salvage_value,
296 nvl(bk.salvage_value, -99))
297 and nvl(bk.percent_salvage_value, -99) =
298 nvl(mch.from_percent_salvage_value/100,
299 nvl(bk.percent_salvage_value, -99))
300 and nvl(bk.deprn_limit_type, 'XX') =
301 nvl(mch.from_deprn_limit_type,
302 nvl(bk.deprn_limit_type,'XX'))
303 and nvl(bk.allowed_deprn_limit, -99) =
304 nvl(mch.from_deprn_limit/100,
305 nvl(bk.allowed_deprn_limit, -99))
306 and nvl(bk.allowed_deprn_limit_amount, -99) =
307 nvl(mch.from_deprn_limit_amount,
308 nvl(bk.allowed_deprn_limit_amount, -99))
309 and mt.method_code = bk.deprn_method_code --Bug 8928436
310 and ad.asset_id > px_max_asset_id
311 and MOD(nvl(bk.group_asset_id, ad.asset_id), p_total_requests) = (p_request_number - 1)
312 MINUS
313 select ad.asset_number,
314 bk.asset_id,
315 bk.conversion_date,
316 bk.date_effective,
317 bk.date_placed_in_service,
318 bk.group_asset_id
319 from fa_additions_b ad,
320 fa_books bk,
321 fa_mass_changes mch
322 where ad.asset_number >=
323 nvl(mch.from_asset_number,
324 ad.asset_number)
325 and ad.asset_number <=
326 nvl(mch.to_asset_number,
327 ad.asset_number)
328 and ad.asset_type <> 'CIP'
329 and ad.asset_type = nvl(mch.asset_type, ad.asset_type)
330 and ad.asset_id = bk.asset_id
331 and ad.asset_category_id =
332 nvl(mch.category_id,
333 ad.asset_category_id)
334 and mch.mass_change_id = p_mass_change_id
335 and bk.book_type_code = mch.book_type_code
336 and bk.transaction_header_id_out is null
337 and bk.period_counter_fully_retired is null
338 and nvl(bk.disabled_flag, 'N') = 'N' --HH ed.
339 and nvl(bk.period_counter_fully_reserved,99) =
340 nvl(bk.period_counter_life_complete,99)
341 and nvl(bk.period_counter_fully_reserved, -1) =
342 decode(mch.change_fully_rsvd_assets, 'YES',
343 nvl(bk.period_counter_fully_reserved, -1), -1)
344 and bk.date_placed_in_service >=
345 nvl(mch.from_date_placed_in_service,
346 bk.date_placed_in_service)
347 and bk.date_placed_in_service <=
348 nvl(mch.to_date_placed_in_service,
349 bk.date_placed_in_service)
350 and bk.deprn_method_code =
351 nvl(mch.to_method_code,
352 bk.deprn_method_code)
353 and nvl(bk.life_in_months, -1) =
354 nvl(mch.to_life_in_months,
355 nvl(bk.life_in_months, -1))
356 and nvl(bk.basic_rate, -1) =
357 nvl(mch.to_basic_rate,
358 nvl(bk.basic_rate, -1))
359 and nvl(bk.adjusted_rate, -1) =
360 nvl(mch.to_adjusted_rate,
361 nvl(bk.adjusted_rate, -1))
362 and nvl(bk.production_capacity, -1) =
363 nvl(mch.to_production_capacity,
364 nvl(bk.production_capacity, -1))
365 and nvl(bk.unit_of_measure, -1) =
366 nvl(mch.to_uom,
367 nvl(bk.unit_of_measure, -1))
368 and bk.prorate_convention_code =
369 nvl(mch.to_convention,
370 bk.prorate_convention_code)
371 and nvl(bk.bonus_rule, -1) =
372 nvl(mch.to_bonus_rule,
373 nvl(bk.bonus_rule,-1))
374 and nvl (mch.to_group_association,'XXXX') = nvl (mch.from_group_association,'XXXX')
375 and nvl (mch.to_group_asset_id,-99) = nvl (mch.from_group_asset_id,-99)
376 and nvl(bk.salvage_type, 'XX') =
377 nvl(mch.to_salvage_type,
378 nvl(bk.salvage_type,'XX'))
379 and nvl(bk.salvage_value, -99) =
380 nvl(mch.to_salvage_value,
381 nvl(bk.salvage_value, -99))
382 and nvl(bk.percent_salvage_value, -99) =
383 nvl(mch.to_percent_salvage_value/100,
384 nvl(bk.percent_salvage_value, -99))
385 and nvl(bk.deprn_limit_type, 'XX') =
386 nvl(mch.to_deprn_limit_type,
387 nvl(bk.deprn_limit_type,'XX'))
388 and nvl(bk.allowed_deprn_limit, -99) =
389 nvl(mch.to_deprn_limit/100,
390 nvl(bk.allowed_deprn_limit, -99))
391 and nvl(bk.allowed_deprn_limit_amount, -99) =
392 nvl(mch.to_deprn_limit_amount,
393 nvl(bk.allowed_deprn_limit_amount, -99))
394 and nvl(bk.depreciate_flag, -1) =
395 nvl(mch.to_depreciate_flag,
396 nvl(bk.depreciate_flag, -1))
397 and ad.asset_id > px_max_asset_id
398 and MOD(nvl(bk.group_asset_id, ad.asset_id), p_total_requests) = (p_request_number - 1)
399 order by 2;
400
401
402 done_exc EXCEPTION;
403 masschg_err EXCEPTION;
404 adj_err EXCEPTION;
405
406 BEGIN
407
408 px_max_asset_id := nvl(px_max_asset_id, 0);
409 x_success_count := 0;
410 x_failure_count := 0;
411
412
413 if (not g_log_level_rec.initialized) then
414 if (NOT fa_util_pub.get_log_level_rec (
415 x_log_level_rec => g_log_level_rec
416 )) then
417 raise masschg_err;
418 end if;
419 end if;
420
421 if (px_max_asset_id = 0) then
422
423 FND_FILE.put(FND_FILE.output,'');
424 FND_FILE.new_line(FND_FILE.output,1);
425
426 -- dump out the headings
427 fnd_message.set_name('OFA', 'FA_MASSRET_REPORT_COLUMN');
428 l_string := fnd_message.get;
429
430 FND_FILE.put(FND_FILE.output,l_string);
431 FND_FILE.new_line(FND_FILE.output,1);
432
433 fnd_message.set_name('OFA', 'FA_MASSRET_REPORT_LINE');
434 l_string := fnd_message.get;
435
436 FND_FILE.put(FND_FILE.output,l_string);
437 FND_FILE.new_line(FND_FILE.output,1);
438
439 end if;
440
441 -- get the masschg info
442 open c_mass_change_info;
443 fetch c_mass_change_info
444 into l_masschg_status,
445 l_from_convention,
446 l_to_convention,
447 l_from_method_code,
448 l_to_method_code,
449 l_from_life_in_months,
450 l_to_life_in_months,
451 l_from_bonus_rule,
452 l_to_bonus_rule,
453 l_mass_date_effective,
454 l_trx_date_entered,
455 l_from_basic_rate,
456 l_to_basic_rate,
457 l_from_adjusted_rate,
458 l_to_adjusted_rate,
459 l_from_production_capacity,
460 l_to_production_capacity,
461 l_from_uom,
462 l_to_uom,
463 l_from_group_association,
464 l_to_group_association,
465 l_from_group_asset_id,
466 l_to_group_asset_id,
467 l_asset_type,
468 l_amortize_flag,
469 l_book_type_code,
470 l_from_salvage_type,
471 l_to_salvage_type,
472 l_from_percent_salvage_value,
473 l_to_percent_salvage_value,
474 l_from_salvage_value,
475 l_to_salvage_value,
476 l_from_deprn_limit_type,
477 l_to_deprn_limit_type,
478 l_from_deprn_limit,
479 l_to_deprn_limit,
480 l_from_deprn_limit_amount,
481 l_to_deprn_limit_amount,
482 l_from_depreciate_flag,
483 l_to_depreciate_flag;
484 -- l_allow_overlapping_adj_flag;
485
486 if (c_mass_change_info%NOTFOUND) then
487 close c_mass_change_info;
488 raise masschg_err;
489 end if;
490 close c_mass_change_info;
491
492
493 if(l_masschg_status <> 'RUNNING') then
494 fa_srvr_msg.add_message
495 (calling_fn => l_calling_fn,
496 name => 'FA_MASSCHG_WRONG_STATUS', p_log_level_rec => g_log_level_rec); -- NOTE! not placing tokens yet
497 raise masschg_err;
498 end if;
499
500
501 -- call the book controls cache
502 if not fa_cache_pkg.fazcbc(X_book => l_book_type_code, p_log_level_rec => g_log_level_rec) then
503 raise masschg_err;
504 end if;
505
506 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
507
508 -- load the period struct for current period info
509 if not FA_UTIL_PVT.get_period_rec
510 (p_book => l_book_type_code,
511 p_effective_date => NULL,
512 x_period_rec => l_period_rec
513 , p_log_level_rec => g_log_level_rec) then raise adj_err;
514 end if;
515
516 if (l_from_method_code is not null) then
517 -- call the method cache for rate source rule
518 if not fa_cache_pkg.fazccmt
519 (X_method => l_from_method_code,
520 X_life => l_from_life_in_months
521 , p_log_level_rec => g_log_level_rec) then
522 raise masschg_err;
523 end if;
524 end if;
525
526 l_from_rsr := fa_cache_pkg.fazccmt_record.rate_source_rule;
527
528 if (l_to_method_code is not null) then
529 if not fa_cache_pkg.fazccmt
530 (X_method => l_to_method_code,
531 X_life => l_to_life_in_months
532 , p_log_level_rec => g_log_level_rec) then
533 raise masschg_err;
534 end if;
535 end if;
536
537 l_to_rsr := fa_cache_pkg.fazccmt_record.rate_source_rule;
538
539 if (l_from_rsr = 'PRODUCTION' and
540 l_to_rsr = 'PRODUCTION' and
541 nvl(l_from_convention, 'NULL') = nvl(l_to_convention, 'NULL') and
542 l_from_method_code = l_to_method_code and
543 nvl(l_from_production_capacity, -1) = nvl(l_to_production_capacity, -1) and
544 nvl(l_from_uom, 'NULL') <> nvl(l_to_uom, 'NULL') and
545 fa_cache_pkg.fazcbc_record.book_class = 'CORPORATE') then
546
547 l_uom_change := TRUE;
548 else
549 l_uom_change := FALSE;
550 end if;
551
552 -- initial book control validation
553 if (fa_cache_pkg.fazcbc_record.allow_mass_changes <> 'YES') then
554 fa_srvr_msg.add_message
555 (calling_fn => l_calling_fn,
556 name => 'FA_MASSCHG_WRONG_STATUS', p_log_level_rec => g_log_level_rec);
557 raise masschg_err;
558 elsif (fa_cache_pkg.fazcbc_record.date_ineffective is not null) then
559 fa_srvr_msg.add_message
560 (calling_fn => l_calling_fn,
561 name => 'FA_MASSCHG_WRONG_STATUS', p_log_level_rec => g_log_level_rec);
562 raise masschg_err;
563 end if;
564
565 if (l_uom_change) then
566
567 --'FA_MASSCHG_UOM_CHANGE_ONLY'
568 --action_buf = (text *) "FA_SHARED_FETCH_CURSOR";
569
570 OPEN c_assets_uom;
571 FETCH c_assets_uom BULK COLLECT INTO
572 l_asset_number,
573 l_asset_id
574 LIMIT l_batch_size;
575 close c_assets_uom;
576
577 if (l_asset_id.count = 0) then
578 raise done_exc;
579 end if;
580
581 for l_loop_count in 1..l_asset_id.count loop
582
583 -- reset the message level to prevent bogus errors
584 FA_SRVR_MSG.Set_Message_Level(message_level => 10, p_log_level_rec => g_log_level_rec);
585 l_mesg_name := null;
586
587 fa_srvr_msg.add_message(
588 calling_fn => NULL,
589 name => 'FA_SHARED_ASSET_NUMBER',
590 token1 => 'NUMBER',
591 value1 => l_asset_number(l_loop_count),
592 p_log_level_rec => g_log_level_rec);
593
594 BEGIN
595
596 update fa_books bk
597 set bk.unit_of_measure = l_to_uom,
598 bk.last_update_date = sysdate,
599 bk.last_updated_by = l_userid,
600 bk.last_update_login = l_login,
601 bk.annual_deprn_rounding_flag = 'ADJ'
602 where bk.asset_id = l_asset_id(l_loop_count) and
603 bk.date_ineffective is null and
604 bk.book_type_code in
605 (select bc.book_type_code
606 from fa_book_controls bc,
607 fa_methods me
608 where bc.date_ineffective is null
609 and bc.distribution_source_book = l_book_type_code
610 and bc.book_class <> 'BUDGET'
611 and me.method_code = bk.deprn_method_code
612 and me.rate_source_rule = 'PRODUCTION');
613
614 x_success_count := x_success_count + 1;
615
616 write_message(l_asset_number(l_loop_count),
617 'FA_MCP_ADJUSTMENT_SUCCESS');
618
619
620 EXCEPTION
621 when others then
622 FND_CONCURRENT.AF_ROLLBACK;
623 x_failure_count := x_failure_count + 1;
624
625 write_message(l_asset_number(l_loop_count),
626 null);
627 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
628
629 if (g_log_level_rec.statement_level) then
630 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
631 end if;
632
633 END;
634
635 -- FND_CONCURRENT.AF_COMMIT each record
636 FND_CONCURRENT.AF_COMMIT;
637
638 end loop;
639
640 else -- non-uom or group change
641
642 OPEN c_assets;
643 FETCH C_assets BULK COLLECT INTO
644 l_asset_number,
645 l_asset_id,
646 l_conversion_date,
647 l_date_effective,
648 l_date_placed_in_service,
649 l_group_asset_id LIMIT l_batch_size;
650 close c_assets;
651
652 if l_asset_number.count = 0 then
653 raise done_exc;
654 end if;
655
656 for l_loop_count in 1..l_asset_id.count loop
657
658 -- clear the debug stack for each asset
659 FA_DEBUG_PKG.Initialize;
660 -- reset the message level to prevent bogus errors
661 FA_SRVR_MSG.Set_Message_Level(message_level => 10, p_log_level_rec => g_log_level_rec);
662
663 l_mesg_name := null;
664
665 BEGIN
666
667 -- no need to lock books row since it's a mass trx
668 -- Check that assets do not have transactions dated after the
669 -- request was submitted
670
671 if l_date_effective(l_loop_count) >= l_mass_date_effective then
672 l_mesg_name := 'FA_MASSCHG_DATE';
673 raise adj_err;
674 end if;
675
676 if not FA_ASSET_VAL_PVT.validate_period_of_addition
677 (p_asset_id => l_asset_id(l_loop_count),
678 p_book => l_book_type_code,
679 p_mode => 'ABSOLUTE',
680 px_period_of_addition => l_period_of_addition,
681 p_log_level_rec => g_log_level_rec) then
682 raise adj_err;
683 end if;
684
685 -- Prevent adjustments on short tax year assets
686 if (l_period_of_addition = 'Y' and l_conversion_date(l_loop_count) is not null) then
687 l_mesg_name := 'FA_MASSCHG_ASSET_SHORT_TAX';
688 raise adj_err;
689 end if;
690
691 -- Check non-production <=> production changes are legal
692 if (l_from_rsr <> l_to_rsr) then
693 if (l_from_rsr = 'PRODUCTION') then
694 if (l_period_of_addition <> 'Y') then
695 l_mesg_name := 'FA_MASSCHG_ASSET_DEPRED';
696 raise adj_err;
697 end if;
698
699 if (fa_cache_pkg.fazcbc_record.book_class = 'CORPORATE') then
700
701 select count(*)
702 into l_count
703 from fa_book_controls bc,
704 fa_books bk,
705 fa_methods me
706 where bk.book_type_code = bc.book_type_code
707 and bk.asset_id = l_asset_id(l_loop_count)
708 and bk.date_ineffective is null
709 and bc.distribution_source_book = l_book_type_code
710 and bc.date_ineffective is null
711 and bc.book_class = 'TAX'
712 and me.method_code = bk.deprn_method_code
713 and nvl(me.life_in_months, -1) = nvl(bk.life_in_months, -1)
714 and me.rate_source_rule = 'PRODUCTION';
715
716 if (l_count <> 0) then
717 l_mesg_name := 'FA_MASSCHG_PROD_IN_TAX';
718 raise adj_err;
719 end if;
720 end if;
721 elsif (l_to_rsr = 'PRODUCTION') then
722 if (l_period_of_addition <> 'Y') then
723 l_mesg_name := 'FA_MASSCHG_ASSET_DEPRED';
724 raise adj_err;
725 end if;
726
727 if (fa_cache_pkg.fazcbc_record.book_class = 'TAX') then
728 -- first check isn't need (asset in corp book)
729
730 select count(*)
731 into l_count
732 from fa_book_controls bc,
733 fa_books bk,
734 fa_methods me
735 where bk.book_type_code = bc.distribution_source_book
736 and bk.asset_id = l_asset_id(l_loop_count)
737 and bk.date_ineffective is null
738 and bc.book_type_code = l_book_type_code
739 and bc.date_ineffective is null
740 and me.method_code = bk.deprn_method_code
741 and nvl(me.life_in_months, -1) = nvl(bk.life_in_months, -1)
742 and me.rate_source_rule = 'PRODUCTION';
743
744 if (l_count = 0) then
745 l_mesg_name := 'FA_MASSCHG_NOT_PROD_IN_CORP';
746 raise adj_err;
747 end if;
748 end if; -- tax
749 end if; -- production checks
750 end if; -- differing rate source rules
751
752 -- set values - most shouldn't be needed -- DOUBLE CHECK ***
753
754 -- check for current period add (not needed)
755 -- check for exp after amort (done in API)
756 -- check for subsequent trxs (moved below for overlapping adjs)
757
758 -- set some stuff (most not needed)
759 -- calc rem lives for formula and short tax (API)
760
761 -- do adjustment
762
763 -- validation ok, null out then load the structs and process the adjustment
764 l_trans_rec := NULL;
765 l_asset_hdr_rec := NULL;
766 l_asset_fin_rec_adj := NULL;
767 l_asset_fin_rec_new := NULL;
768 l_asset_fin_mrc_tbl_new.delete;
769 l_inv_trans_rec := NULL;
770 l_inv_tbl.delete;
771 l_asset_deprn_rec_adj := NULL;
772 l_asset_deprn_rec_new := NULL;
773 l_asset_deprn_mrc_tbl_new.delete;
774 l_group_reclass_options_rec := NULL;
775
776 -- reset the who info in trans rec
777 l_trans_rec.who_info.last_update_date := sysdate;
778 l_trans_rec.who_info.last_updated_by := FND_GLOBAL.USER_ID;
779 l_trans_rec.who_info.created_by := FND_GLOBAL.USER_ID;
780 l_trans_rec.who_info.creation_date := sysdate;
781 l_trans_rec.who_info.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
782 l_trans_rec.mass_reference_id := p_parent_request_id;
783 l_trans_rec.calling_interface := 'FAMACH';
784 l_trans_rec.mass_transaction_id := p_mass_change_id;
785
786 l_trans_rec.transaction_date_entered := l_trx_date_entered;
787
788 -- asset header struct
789 l_asset_hdr_rec.asset_id := l_asset_id(l_loop_count);
790 l_asset_hdr_rec.book_type_code := l_book_type_code;
791
792 if (l_amortize_flag = 'Y') then
793 l_trans_rec.transaction_subtype := 'AMORTIZED';
794 l_amortization_start_date := l_trx_date_entered;
795
796 -- only validate overlapping adjustments if the
797 -- amortization start data is populated and
798 -- it also falls in a prior period
799 --
800 -- form defaults the date as follows:
801 -- greatest(fadp.calendar_period_open_date,
802 -- least(sysdate, fadp.calendar_period_close_date))
803
804 -- BUG# 2914818 - scrapping this now that we have the
805 -- puristic approach for overlapping adjustments
806
807 /*
808 if (l_amortization_start_date < l_period_rec.calendar_period_open_date) then
809
810 -- get the existing fin info
811 if not FA_UTIL_PVT.get_asset_fin_rec
812 (p_asset_hdr_rec => l_asset_hdr_rec,
813 px_asset_fin_rec => l_asset_fin_rec_old,
814 p_transaction_header_id => NULL,
815 p_mrc_sob_type_code => 'P'
816 , p_log_level_rec => g_log_level_rec) then raise adj_err;
817 end if;
818
819 l_old_amortization_start_date := l_amortization_start_date;
820 if not FA_ASSET_VAL_PVT.validate_amort_start_date
821 (p_transaction_type_code => l_trans_rec.transaction_type_code,
822 p_asset_id => l_asset_hdr_rec.asset_id,
823 p_book_type_code => l_asset_hdr_rec.book_type_code,
824 p_date_placed_in_service => l_asset_fin_rec_old.date_placed_in_service,
825 p_conversion_date => l_asset_fin_rec_old.conversion_date,
826 p_period_rec => l_period_rec,
827 p_amortization_start_date => l_old_amortization_start_date,
828 x_amortization_start_date => l_amortization_start_date,
829 x_trxs_exist => l_trxs_exist,
830 p_calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec) then
831 raise adj_err;
832 end if;
833
834 -- for now we are not allowing overlapping adjs via mass change
835 -- simply reject any such adjustments
836
837 if (l_trxs_exist = 'Y') then
838 -- and nvl(l_allow_overlapping_adj_flag, 'N') = 'N') then
839 l_mesg_name := 'FA_MASSCHG_TDATE';
840 raise adj_err;
841 end if;
842
843 end if;
844
845 */ -- end BUG# 2914818
846
847
848 l_trans_rec.amortization_start_date := l_amortization_start_date;
849 l_trans_rec.transaction_date_entered := l_amortization_start_date;
850
851 else
852 l_trans_rec.transaction_subtype := 'EXPENSED';
853
854 -- Check that the latest transaction_date_entered is on or
855 -- before the transaction_date_entered for the request
856
857 select count(*)
858 into l_count
859 from fa_transaction_headers th
860 where th.asset_id = l_asset_id(l_loop_count)
861 and th.book_type_code = l_book_type_code
862 and th.transaction_date_entered > l_trx_date_entered;
863
864 if (l_count <> 0 ) then
865 l_mesg_name := 'FA_MASSCHG_TDATE';
866 raise adj_err;
867 end if;
868 end if;
869
870 -- fin struct
871 l_asset_fin_rec_adj.production_capacity := l_to_production_capacity - l_from_production_capacity; -- Bug 3147951
872 l_asset_fin_rec_adj.prorate_convention_code := l_to_convention;
873 l_asset_fin_rec_adj.deprn_method_code := l_to_method_code;
874 l_asset_fin_rec_adj.life_in_months := l_to_life_in_months;
875 l_asset_fin_rec_adj.bonus_rule := l_to_bonus_rule;
876 l_asset_fin_rec_adj.basic_rate := l_to_basic_rate;
877 l_asset_fin_rec_adj.adjusted_rate := l_to_adjusted_rate;
878 l_asset_fin_rec_adj.unit_of_measure := l_to_uom;
879 --Sandeep
880 if (l_from_depreciate_flag <> l_to_depreciate_flag) then
881 l_asset_fin_rec_adj.depreciate_flag := l_to_depreciate_flag;
882 end if;
883 if ((l_to_percent_salvage_value/100 is not null) OR
884 (l_to_salvage_value is not null) OR
885 (l_to_deprn_limit is not null) OR
886 (l_to_deprn_limit_amount is not null)) then
887
888 -- Fix for Bug #6707025. In order to find correct value for the
889 -- adj rec, need to get the old rec.
890 select salvage_type,
891 nvl(percent_salvage_value, 0),
892 nvl(salvage_value, 0),
893 deprn_limit_type,
894 nvl(allowed_deprn_limit, 0),
895 nvl(allowed_deprn_limit_amount, 0)
896 into l_old_salvage_type,
897 l_old_percent_salvage_value,
898 l_old_salvage_value,
899 l_old_deprn_limit_type,
900 l_old_deprn_limit,
901 l_old_deprn_limit_amount
902 from fa_books
903 where book_type_code = l_book_type_code
904 and asset_id = l_asset_id(l_loop_count)
905 and transaction_header_id_out is null;
906
907 l_asset_fin_rec_adj.salvage_type :=
908 l_to_salvage_type;
909 l_asset_fin_rec_adj.percent_salvage_value :=
910 l_to_percent_salvage_value/100 - l_old_percent_salvage_value;
911 --Bug# 6956721- start
912 if (l_from_salvage_type <> l_to_salvage_type
913 and l_to_salvage_type = 'AMT') then
914 l_asset_fin_rec_adj.salvage_value :=
915 l_to_salvage_value;
916 else
917 l_asset_fin_rec_adj.salvage_value :=
918 l_to_salvage_value - l_old_salvage_value;
919 end if;
920 --Bug# 6956721- end
921 l_asset_fin_rec_adj.deprn_limit_type :=
922 l_to_deprn_limit_type;
923 l_asset_fin_rec_adj.allowed_deprn_limit :=
924 l_to_deprn_limit/100 - l_old_deprn_limit;
925 --Bug# 6956721- start
926 if (l_from_deprn_limit_type <> l_to_deprn_limit_type
927 and l_to_deprn_limit_type = 'AMT') then
928 l_asset_fin_rec_adj.allowed_deprn_limit_amount :=
929 l_to_deprn_limit_amount;
930 else
931 l_asset_fin_rec_adj.allowed_deprn_limit_amount :=
932 l_to_deprn_limit_amount - l_old_deprn_limit_amount;
933 end if;
934 --Bug# 6956721- end
935 end if;
936 -- group reclass if applicable
937 -- to reclass from or to standalone, we must use G_MISS_NUM
938 -- null will leave the asset untouched
939 if (l_to_group_association is not null) then
940 if (l_to_group_association = 'STANDALONE') then
941 l_asset_fin_rec_adj.group_asset_id := FND_API.G_MISS_NUM;
942 else -- member
943 l_asset_fin_rec_adj.group_asset_id := l_to_group_asset_id;
944 end if;
945
946 -- set amort start to the member's dpis
947 --Bug#8703091 - Don't override the transaction date entered by user.
948 if (l_trans_rec.amortization_start_date is null) then
949 l_trans_rec.amortization_start_date := l_date_placed_in_service(l_loop_count);
950 l_trans_rec.transaction_date_entered := l_date_placed_in_service(l_loop_count);
951 end if;
952 end if;
953 FA_ADJUSTMENT_PUB.do_adjustment
954 (p_api_version => l_api_version,
955 p_init_msg_list => l_init_msg_list,
956 p_commit => l_commit,
957 p_validation_level => l_validation_level,
958 x_return_status => l_return_status,
959 x_msg_count => l_mesg_count,
960 x_msg_data => l_mesg,
961 p_calling_fn => l_calling_fn,
962 px_trans_rec => l_trans_rec,
963 px_asset_hdr_rec => l_asset_hdr_rec,
964 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
965 x_asset_fin_rec_new => l_asset_fin_rec_new,
966 x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
967 px_inv_trans_rec => l_inv_trans_rec,
968 px_inv_tbl => l_inv_tbl,
969 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
970 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
971 x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
972 p_group_reclass_options_rec => l_group_reclass_options_rec
973 );
974
975 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
976 l_mesg_name := null;
977 raise adj_err;
978 end if;
979
980 -- Update UOM in TAX books if PROD method
981 if (nvl(l_from_uom, 'NULL') <> nvl(l_to_uom, 'NULL')) then
982
983 -- 'FA_MASSCHG_UPDATE_TAX_UOM'
984 update fa_books bk
985 set bk.unit_of_measure = l_to_uom,
986 bk.last_update_date = sysdate,
987 bk.last_updated_by = l_userid,
988 bk.last_update_login = l_login,
989 bk.annual_deprn_rounding_flag = 'ADJ'
990 where bk.asset_id = l_asset_id(l_loop_count)
991 and bk.date_ineffective is null
992 and bk.book_type_code in
993 (select bc.book_type_code
994 from fa_book_controls bc,
995 fa_methods me
996 where bc.distribution_source_book = l_book_type_code
997 and bc.book_class = 'TAX'
998 and bc.date_ineffective is null
999 and me.method_code = bk.deprn_method_code
1000 and me.rate_source_rule = 'PRODUCTION');
1001 end if;
1002
1003 x_success_count := x_success_count + 1;
1004 write_message(l_asset_number(l_loop_count),
1005 'FA_MCP_ADJUSTMENT_SUCCESS');
1006
1007 EXCEPTION
1008 when adj_err then
1009 FND_CONCURRENT.AF_ROLLBACK;
1010 x_failure_count := x_failure_count + 1;
1011
1012 write_message(l_asset_number(l_loop_count),
1013 l_mesg_name);
1014 if (g_log_level_rec.statement_level) then
1015 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
1016 end if;
1017
1018 when others then
1019 FND_CONCURRENT.AF_ROLLBACK;
1020 x_failure_count := x_failure_count + 1;
1021
1022 write_message(l_asset_number(l_loop_count),
1023 null);
1024
1025 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1026
1027 if (g_log_level_rec.statement_level) then
1028 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
1029 end if;
1030
1031 END;
1032
1033 -- FND_CONCURRENT.AF_COMMIT each record
1034 FND_CONCURRENT.AF_COMMIT;
1035
1036 end loop; -- main bulk fetch loop
1037
1038 px_max_asset_id := l_asset_id(l_asset_id.count);
1039
1040 end if; --uom_check
1041
1042 x_return_status := 0;
1043
1044 EXCEPTION
1045 when done_exc then
1046 x_return_status := 0;
1047
1048 when masschg_err then
1049 FND_CONCURRENT.AF_ROLLBACK;
1050 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1051 if (g_log_level_rec.statement_level) then
1052 FA_DEBUG_PKG.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
1053 end if;
1054 x_return_status := 2;
1055
1056 when others then
1057 FND_CONCURRENT.AF_ROLLBACK;
1058 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
1059 if (g_log_level_rec.statement_level) then
1060 FA_DEBUG_PKG.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
1061 end if;
1062 x_return_status := 2;
1063
1064 END do_mass_change;
1065
1066 -----------------------------------------------------------------------------
1067
1068 PROCEDURE write_message
1069 (p_asset_number in varchar2,
1070 p_message in varchar2) IS
1071
1072 l_message varchar2(30);
1073 l_mesg varchar2(100);
1074 l_string varchar2(512);
1075 l_calling_fn varchar2(40); -- conditionally populated below
1076
1077 BEGIN
1078
1079 -- first dump the message to the output file
1080 -- set/translate/retrieve the mesg from fnd
1081
1082 l_message := nvl(p_message, 'FA_MASSCHG_FAIL_TRX');
1083
1084 if (l_message <> 'FA_MCP_ADJUSTMENT_SUCCESS') then
1085 l_calling_fn := 'fa_masschg_pkg.do_mass_change';
1086 end if;
1087
1088 fnd_message.set_name('OFA', l_message);
1089 l_mesg := substrb(fnd_message.get, 1, 100);
1090
1091 l_string := rpad(p_asset_number, 15) || ' ' || l_mesg;
1092
1093 FND_FILE.put(FND_FILE.output,l_string);
1094 FND_FILE.new_line(FND_FILE.output,1);
1095
1096 -- now process the messages for the log file
1097 fa_srvr_msg.add_message
1098 (calling_fn => l_calling_fn,
1099 name => l_message, p_log_level_rec => g_log_level_rec);
1100
1101 EXCEPTION
1102 when others then
1103 raise;
1104
1105 END write_message;
1106
1107 END FA_MASSCHG_PKG;