[Home] [Help]
PACKAGE BODY: APPS.FA_WHATIF_DEPRN2_PKG
Source
1 PACKAGE BODY FA_WHATIF_DEPRN2_PKG as
2 /* $Header: FAWDPR2B.pls 120.18.12020000.3 2012/09/24 06:48:55 dvjoshi ship $ */
3
4
5 function whatif_deprn (
6 X_assets in out nocopy fa_std_types.number_tbl_type,
7 X_num_assets in number,
8 X_method in varchar2,
9 X_life in number,
10 X_adjusted_rate in number,
11 X_prorate_conv in varchar2,
12 X_salvage_pct in number,
13 X_exp_amt in out nocopy varchar2,
14 X_book in varchar2,
15 X_start_per in varchar2,
16 X_num_per in number,
17 X_request_id in number,
18 X_user_id in number,
19 X_hypo in varchar2,
20 X_dpis in date,
21 X_cost in number,
22 X_deprn_rsv in number,
23 X_cat_id in number,
24 X_bonus_rule in varchar2,
25 x_return_status out nocopy number,
26 X_fullresv_flg in varchar2, -- ERnos 6612615 what-if start
27 X_extnd_deprn_flg in varchar2,
28 X_first_period in varchar2) --ERnos 6612615 what-if end
29 return boolean is
30
31
32 -- Implementation Overview:
33 -- Performs a loop, for each asset:
34 -- Run whatif_deprn_asset in NORMAL mode.
35 -- Run whatif_deprn_asset in adjusted mode.
36 -- Run whatif_insert_itf
37
38
39 h_key_struct number;
40 h_loc_struct number;
41 h_cat_struct number;
42 h_acct_struct number;
43 h_precision number;
44 h_login_id number;
45 h_sal number;
46 h_check varchar2(3);
47 h_count number;
48 ret boolean;
49 h_mesg_name varchar2(30);
50 h_mesg_str varchar2(2000);
51 h_cat_segs fa_rx_shared_pkg.Seg_Array;
52 h_concat_str varchar2(200);
53 h_currency varchar2(15);
54 h_basic_rate number;
55 x_errbuf varchar2(200);
56 x_retcode number;
57
58 --ERnos 6612615 what-if start
59 h_cntr1 binary_integer := 1;
60 h_cntr3 binary_integer := 1;
61 TYPE tp_asset IS RECORD ( period VARCHAR2(30),
62 book VARCHAR2(30),
63 asset_id NUMBER,
64 method VARCHAR2(30),
65 adj_cost NUMBER,
66 cost NUMBER,
67 life NUMBER,
68 rate NUMBER,
69 deprn_lmt NUMBER
70 );
71
72 TYPE tp_ast_typ IS TABLE OF tp_asset
73 INDEX BY BINARY_INTEGER;
74 t_ast_typ tp_ast_typ;
75 t_extnd_deprn_asset tp_ast_typ;
76 -- ERnos 6612615 end
77 cursor c_segs is
78 select 'GL_CODE_COMBINATIONS' , c.table_id, g.application_column_name,
79 g.segment_num, s.concatenated_segment_delimiter
80 from fnd_columns c, fnd_id_flex_segments g, fnd_id_flex_structures s
81 where g.application_id = 101
82 and g.id_flex_code = 'GL#'
83 and g.id_flex_num = (
84 select accounting_flex_structure
85 from fa_book_controls where book_type_code = X_book)
86 and g.enabled_flag = 'Y'
87 and c.application_id = g.application_id
88 and c.table_id = (select table_id
89 from fnd_tables
90 where table_name = 'GL_CODE_COMBINATIONS'
91 and application_id = 101)
92 and c.column_name = g.application_column_name
93 and s.application_id = g.application_id
94 and s.id_flex_code = g.id_flex_code
95 and s.id_flex_num = g.id_flex_num
96 union -- asset key
97 select 'FA_ASSET_KEYWORDS', c.table_id,g.application_column_name, g.segment_num, s.concatenated_segment_delimiter
98 from fnd_columns c, fnd_id_flex_segments g, fnd_id_flex_structures s
99 where g.application_id = 140
100 and g.id_flex_code = 'KEY#'
101 and g.id_flex_num = ( select asset_key_flex_structure
102 from fa_system_controls)
103 and g.enabled_flag = 'Y'
104 and c.application_id = 140
105 and c.table_id = (select table_id
106 from fnd_tables
107 where table_name = 'FA_ASSET_KEYWORDS'
108 and application_id = 140)
109 and c.column_name = g.application_column_name
110 and s.application_id = g.application_id
111 and s.id_flex_code = g.id_flex_code
112 and s.id_flex_num = g.id_flex_num
113 union -- location
114 select 'FA_LOCATIONS', c.table_id, g.application_column_name, g.segment_num, s.concatenated_segment_delimiter
115 from fnd_columns c, fnd_id_flex_segments g, fnd_id_flex_structures s
116 where g.application_id = 140
117 and g.id_flex_code = 'LOC#'
118 and g.id_flex_num = ( select location_flex_structure
119 from fa_system_controls)
120 and g.enabled_flag = 'Y'
121 and c.application_id = 140
122 and c.table_id = (select table_id
123 from fnd_tables
124 where table_name = 'FA_LOCATIONS'
125 and application_id = 140)
126 AND c.column_name = g.application_column_name
127 and s.application_id = g.application_id
128 and s.id_flex_code = g.id_flex_code
129 and s.id_flex_num = g.id_flex_num
130 union -- category
131 select 'FA_CATEGORIES_B', c.table_id, g.application_column_name, g.segment_num, s.concatenated_segment_delimiter
132 from fnd_columns c, fnd_id_flex_segments g, fnd_id_flex_structures s
133 where g.application_id = 140
134 and g.id_flex_code = 'CAT#'
135 and g.id_flex_num = (select category_flex_structure
136 from fa_system_controls)
137 and g.enabled_flag = 'Y'
138 and c.application_id = 140
139 and c.table_id = (select table_id
140 from fnd_tables
141 where table_name = 'FA_CATEGORIES_B'
142 and application_id = 140)
143 and c.column_name = g.application_column_name
144 and s.application_id = g.application_id
145 and s.id_flex_code = g.id_flex_code
146 and s.id_flex_num = g.id_flex_num
147 ORDER BY 1, 4;
148
149 --ERnos 6612615 what-if start
150 -- JP_TAX_2012
151 cursor c_aset(x_book_type varchar2, x_asset_id NUMBER) is
152 select to_char(fb.date_placed_in_service) dtin_srv,fb.book_type_code,
153 fb.asset_id,fb.deprn_method_code,fb.adjusted_cost,fb.cost,fb.life_in_months,fb.rate_in_use,
154 fb.allowed_deprn_limit_amount
155 from fa_books fb,
156 fa_methods fam
157 where fb.deprn_method_code = fam.method_code
158 and NVL(fam.guarantee_rate_method_flag,'NO') = 'YES'
159 and fb.date_ineffective is null
160 and period_counter_fully_reserved is null
161 and fb.book_type_code = x_book_type
162 and fb.asset_id = x_asset_id;
163
164 --ERnos 6612615 what-if extnd deprn start
165 cursor c_extnd_asset(x_book_type varchar2, x_asset_id NUMBER) is
166 select fb.book_type_code,
167 fb.asset_id
168 from fa_books fb
169 where fb.date_ineffective is null
170 and fb.book_type_code = x_book_type
171 and fb.asset_id = x_asset_id
172 AND FB.deprn_method_code <> 'JP-STL-EXTND'
173 AND FB.allowed_deprn_limit_amount > 1;
174 --ERnos 6612615 end
175 slask1 varchar2(100);
176 slask2 varchar2(100);
177
178 begin
179
180 --fa_rx_conc_mesg_pkg.log('IN HERE IN HERE 1');
181
182 ret := TRUE;
183
184 fa_whatif_deprn_pkg.t_request_id.delete;
185 fa_whatif_deprn_pkg.t_book_type_code.delete;
186 fa_whatif_deprn_pkg.t_asset_id.delete;
187 fa_whatif_deprn_pkg.t_asset_number.delete;
188 fa_whatif_deprn_pkg.t_description.delete;
189 fa_whatif_deprn_pkg.t_tag_number.delete;
190 fa_whatif_deprn_pkg.t_serial_number.delete;
191 fa_whatif_deprn_pkg.t_period_name.delete;
192 fa_whatif_deprn_pkg.t_fiscal_year.delete;
193 fa_whatif_deprn_pkg.t_expense_acct.delete;
194 fa_whatif_deprn_pkg.t_location.delete;
195 fa_whatif_deprn_pkg.t_units.delete;
196 fa_whatif_deprn_pkg.t_employee_name.delete;
197 fa_whatif_deprn_pkg.t_employee_number.delete;
198 fa_whatif_deprn_pkg.t_asset_key.delete;
199 fa_whatif_deprn_pkg.t_current_cost.delete;
200 fa_whatif_deprn_pkg.t_current_prorate_conv.delete;
201 fa_whatif_deprn_pkg.t_current_method.delete;
202 fa_whatif_deprn_pkg.t_current_life.delete;
203 fa_whatif_deprn_pkg.t_current_basic_rate.delete;
204 fa_whatif_deprn_pkg.t_current_adjusted_rate.delete;
205 fa_whatif_deprn_pkg.t_current_salvage_value.delete;
206 fa_whatif_deprn_pkg.t_depreciation.delete;
207 fa_whatif_deprn_pkg.t_new_depreciation.delete;
208 fa_whatif_deprn_pkg.t_created_by.delete;
209 fa_whatif_deprn_pkg.t_creation_date.delete;
210 fa_whatif_deprn_pkg.t_last_update_date.delete;
211 fa_whatif_deprn_pkg.t_last_updated_by.delete;
212 fa_whatif_deprn_pkg.t_last_update_login.delete;
213 fa_whatif_deprn_pkg.t_date_placed_in_service.delete;
214 fa_whatif_deprn_pkg.t_category.delete;
215 fa_whatif_deprn_pkg.t_accumulated_deprn.delete;
216 fa_whatif_deprn_pkg.t_bonus_depreciation.delete;
217 fa_whatif_deprn_pkg.t_new_bonus_depreciation.delete;
218 fa_whatif_deprn_pkg.t_current_bonus_rule.delete;
219 fa_whatif_deprn_pkg.t_period_num.delete;
220 fa_whatif_deprn_pkg.t_currency_code.delete;
221
222 if (X_exp_amt = 'YES') then X_exp_amt := 'AMORTIZED';
223 elsif (X_exp_amt = 'NO') then X_exp_amt := 'EXPENSED';
224 end if;
225
226 -- MAKE SURE AMORTIZED ADJ ARE ALLOWED FOR THIS BOOK
227
228 if (X_exp_amt = 'AMORTIZED') then
229
230 select amortize_flag into h_check from fa_book_controls
231 where book_type_code = X_book;
232
233 if h_check = 'NO' then
234 fnd_message.set_name('OFA','FA_BOOK_AMORTIZED_NOT_ALLOW');
235 h_mesg_str := fnd_message.get;
236 fa_rx_conc_mesg_pkg.log(h_mesg_str);
237
238 x_return_status := 2;
239 return FALSE;
240 end if;
241 end if;
242
243 --fa_rx_conc_mesg_pkg.log('IN HERE IN HERE 2');
244
245 -- SELECT VALUES THAT WILL REMAIN CONSTANT FOR ALL ASSETS:
246 -- FLEX STRUCTURES, PRECISION, LOGIN_ID
247
248 h_mesg_name := 'FA_FA_LOOKUP_IN_SYSTEM_CTLS';
249
250 select location_flex_structure, category_flex_structure,
251 asset_key_flex_structure
252 into h_loc_struct, h_cat_struct, h_key_struct
253 from fa_system_controls;
254
255 h_mesg_name := 'FA_REC_SQL_ACCT_FLEX';
256
257 select accounting_flex_structure into h_acct_struct
258 from fa_book_controls where book_type_code = X_book;
259
260 h_mesg_name := 'FA_DYN_CURRENCY';
261
262
263 select cur.precision into h_precision
264 from fa_book_controls bc, gl_sets_of_books sob, fnd_currencies cur
265 where bc.book_type_code = X_book
266 --and sob.set_of_books_id = bc.set_of_books_id
267 and sob.set_of_books_id = FARX_C_WD.sob_id -- Enhancement bug 3037321
268 and sob.currency_code = cur.currency_code;
269
270 if (nvl(X_request_id,0) <> 0) then
271 select fcr.last_update_login into h_login_id
272 from fnd_concurrent_requests fcr
273 where fcr.request_id = X_request_id;
274 else
275 h_login_id := 0;
276 end if;
277
278
279 -- Aggregate selects here:
280
281 h_mesg_name := 'FA_REC_SQL_ACCT_FLEX';
282 fa_rx_shared_pkg.g_seg_count := 0; /*Bug#12327960 */
283 open c_segs;
284 loop
285
286 FETCH c_segs into fa_rx_shared_pkg.g_seg_struct.tabname,
287 fa_rx_shared_pkg.g_seg_struct.table_id,
288 fa_rx_shared_pkg.g_seg_struct.colname,
289 fa_rx_shared_pkg.g_seg_struct.segment_num,
290 fa_rx_shared_pkg.g_seg_struct.delimiter;
291
292 if (c_segs%NOTFOUND) then
293 exit;
294 end if;
295
296 -- load the table.
297 if (fa_rx_shared_pkg.g_seg_count = 0) then /* initialize the table. */
298 fa_rx_shared_pkg.g_seg_table.delete;
299 end if;
300 fa_rx_shared_pkg.g_seg_count := fa_rx_shared_pkg.g_seg_count + 1;
301
302 fa_rx_shared_pkg.g_seg_table(fa_rx_shared_pkg.g_seg_count) := fa_rx_shared_pkg.g_seg_struct;
303
304
305 end loop;
306 close c_segs;
307
308 -- end aggregate selects.
309
310 --fa_rx_conc_mesg_pkg.log('IN HERE IN HERE 3');
311
312 if (upper(X_hypo) in ('NO', 'N')) then
313
314 h_count := 0;
315
316 loop -- for each asset
317
318 if h_count >= X_num_assets then exit; end if;
319
320 --ERnos 6612615 what-if start
321 for c_asset in c_aset(X_book,X_assets(h_count))
322 loop
323 t_ast_typ(h_cntr1).period := c_asset.dtin_srv;
324 t_ast_typ(h_cntr1).book := c_asset.book_type_code;
325 t_ast_typ(h_cntr1).asset_id := c_asset.asset_id;
326 t_ast_typ(h_cntr1).method := c_asset.deprn_method_code;
327 t_ast_typ(h_cntr1).adj_cost := c_asset.adjusted_cost;
328 t_ast_typ(h_cntr1).cost := c_asset.cost;
329 t_ast_typ(h_cntr1).life := c_asset.life_in_months;
330 t_ast_typ(h_cntr1).rate := c_asset.rate_in_use;
331 t_ast_typ(h_cntr1).deprn_lmt := c_asset.allowed_deprn_limit_amount;
332 h_cntr1 := h_cntr1 + 1;
333 end loop;
334 for c_extnd_asset_rec in c_extnd_asset(X_book,X_assets(h_count))
335 loop
336 t_extnd_deprn_asset(h_cntr3).asset_id := c_extnd_asset_rec.asset_id;
337 h_cntr3 := h_cntr3 + 1;
338 end loop;
339 --ERnos 6612615 what-if end
340 -- RUN IN NORMAL MODE TO GET DEPRN GIVEN CURRENT STATE
341 -- STORES RESULTS IN FA_WHATIF_DEPRN_PKG.G_DEPRN
342
343 h_mesg_name := 'FA_WHATIF_ASSET_NORMAL_MODE';
344
345 ret := fa_whatif_deprn_pkg.whatif_deprn_asset (
346 X_asset_id => X_assets(h_count),
347 X_mode => 'NORMAL',
348 X_book => X_book,
349 X_start_per => X_start_per,
350 X_num_pers => X_num_per,
351 X_dpis => null,
352 X_prorate_date => null,
353 X_prorate_conv => null,
354 X_deprn_start_date => null,
355 X_ceiling_name => null,
356 X_bonus_rule => null,
357 X_method_code => null,
358 X_cost => null,
359 X_old_cost => null,
360 X_adj_cost => null,
361 X_rec_cost => null,
362 X_raf => null,
363 X_adj_rate => null,
364 X_reval_amo_basis => null,
365 X_capacity => null,
366 X_adj_capacity => null,
367 X_life => null,
368 X_adj_rec_cost => null,
369 X_salvage_value => null,
370 X_salvage_pct => null,
371 X_category_id => null,
372 X_deprn_rnd_flag => null,
373 X_calendar_type => null,
374 X_prior_fy_exp => null,
375 X_deprn_rsv => null,
376 X_reval_rsv => null,
377 X_ytd_deprn => null,
378 X_ltd_prod => null,
379 x_return_status => x_return_status);
380
381 if (ret = FALSE) then
382 x_return_status := 2;
383
384 fa_whatif_deprn_pkg.g_deprn.delete;
385 fnd_message.set_name('OFA','FA_WHATIF_ASSET_NORMAL_MODE');
386 fnd_message.set_token('ASSET_ID',X_assets(h_count),FALSE);
387 h_mesg_str := fnd_message.get;
388 fa_rx_conc_mesg_pkg.log(h_mesg_str);
389 return (FALSE);
390 end if;
391
392
393 -- RUN IN EXPENSED/AMORTIZED MODE TO GET DEPRN IN ADJUSTED STATE
394 -- STORES RESULTS IN FA_WHATIF_DEPRN_PKG.G_DEPRN
395
396 h_mesg_name := 'FA_WHATIF_ASSET_ADJ_MODE';
397
398 if (X_prorate_conv is not null) or
399 (X_bonus_rule is not null) or
400 (X_method is not null) or
401 (X_adjusted_rate is not null) or
402 (X_life is not null) or
403 (X_salvage_pct is not null) then
404 --tk_util.debug('Processing');
405
406 ret := fa_whatif_deprn_pkg.whatif_deprn_asset (
407 X_asset_id => X_assets(h_count),
408 X_mode => X_exp_amt,
409 X_book => X_book,
410 X_start_per => X_start_per,
411 X_num_pers => X_num_per,
412 X_dpis => null,
413 X_prorate_date => null,
414 X_prorate_conv => X_prorate_conv,
415 X_deprn_start_date => null,
416 X_ceiling_name => null,
417 X_bonus_rule => X_bonus_rule,
418 X_method_code => X_method,
419 X_cost => null,
420 X_old_cost => null,
421 X_adj_cost => null,
422 X_rec_cost => null,
423 X_raf => null,
424 X_adj_rate => X_adjusted_rate,
425 X_reval_amo_basis => null,
426 X_capacity => null,
427 X_adj_capacity => null,
428 X_life => X_life,
429 X_adj_rec_cost => null,
430 X_salvage_value => null,
431 X_salvage_pct => X_salvage_pct,
432 X_category_id => null,
433 X_deprn_rnd_flag => null,
434 X_calendar_type => null,
435 X_prior_fy_exp => null,
436 X_deprn_rsv => null,
437 X_reval_rsv => null,
438 X_ytd_deprn => null,
439 X_ltd_prod => null,
440 x_return_status => x_return_status);
441
442 if (ret = FALSE) then
443 x_return_status := 2;
444
445 fa_whatif_deprn_pkg.g_deprn.delete;
446 fnd_message.set_name('OFA','FA_WHATIF_ASSET_ADJ_MODE');
447 fnd_message.set_token('ASSET_ID',X_assets(h_count),FALSE);
448 h_mesg_str := fnd_message.get;
449 fa_rx_conc_mesg_pkg.log(h_mesg_str);
450 return (FALSE);
451 end if;
452 end if; -- (X_prorate_conv is not null) or
453
454
455 -- COMMIT DEPRN RESULTS TO INTERFACE TABLE
456
457 h_mesg_name := 'FA_WHATIF_ASSET_COMMIT';
458
459 --tk_util.debug('h_count:X_num_assets: '||to_char(h_count)||':'||to_char(X_num_assets));
460 ret := fa_whatif_deprn_pkg.whatif_insert_itf (
461 X_asset_id => X_assets(h_count),
462 X_book => X_book,
463 X_request_id => X_request_id,
464 X_num_pers => X_num_per,
465 X_acct_struct => h_acct_struct,
466 X_key_struct => h_key_struct,
467 X_cat_struct => h_cat_struct,
468 X_loc_struct => h_loc_struct,
469 X_precision => h_precision,
470 X_user_id => X_user_id,
471 X_login_id => h_login_id,
472 X_last_asset => (h_count = (X_num_assets - 1)),
473 x_return_status => x_return_status);
474
475 -- X_seg_table => seg_table,
476 if (ret = FALSE) then
477 x_return_status := 2;
478
479 fa_whatif_deprn_pkg.g_deprn.delete;
480 fnd_message.set_name('OFA','FA_WHATIF_ASSET_COMMIT');
481 fnd_message.set_token('ASSET_ID',X_assets(h_count),FALSE);
482 h_mesg_str := fnd_message.get;
483 fa_rx_conc_mesg_pkg.log(h_mesg_str);
484 return (FALSE);
485 end if;
486
487 h_count := h_count + 1;
488 end loop;
489
490 --ERnos 6612615 what-if start
491 -- BUG# 7193797 : Commented the below peice of code to make use of the standard Depreciation Functionality.
492 /*if t_ast_typ.FIRST is not null
493 then
494 for j in 1..t_ast_typ.count
495 loop
496 FA_JP_TAX_EXTN_PVT.calc_jp250db (X_request_id => X_request_id,
497 X_asset_id => t_ast_typ(j).asset_id,
498 X_book => X_book,
499 X_method => t_ast_typ(j).method,
500 X_cost => t_ast_typ(j).adj_cost,
501 X_cur_cost => t_ast_typ(j).cost,
502 X_life => t_ast_typ(j).life,
503 X_rate_in_use => t_ast_typ(j).rate,
504 X_deprn_lmt => t_ast_typ(j).deprn_lmt,
505 X_start_prd => X_start_per,
506 X_dtin_serv => t_ast_typ(j).period,
507 X_num_per => X_num_per
508 );
509
510 end loop;
511 end if; */
512
513 -- BUG# 7193797 : End of the Comment
514
515 if t_extnd_deprn_asset.FIRST is not null
516 then
517 for j in 1..t_extnd_deprn_asset.COUNT
518 loop
519
520 --ERnos 6612615 what-if start
521 FA_JP_TAX_EXTN_PVT.deprn_main ( x_errbuf
522 ,x_retcode
523 ,X_request_id
524 ,X_book
525 ,X_first_period
526 ,X_num_per
527 ,X_start_per
528 ,X_extnd_deprn_flg
529 ,X_fullresv_flg
530 ,t_extnd_deprn_asset(j).asset_id
531 );
532 --ERnos 6612615 what-if end
533 end loop;
534 end if;
535
536 --ERnos 6612615 what-if end
537
538 else
539 -- HYPOTHETICAL ASSET
540
541 --fa_rx_conc_mesg_pkg.log('IN HERE IN HERE');
542
543 ret := fa_whatif_deprn_pkg.whatif_deprn_asset (
544 X_asset_id => 0,
545 X_mode => 'HYPOTHETICAL',
546 X_book => X_book,
547 X_start_per => X_start_per,
548 X_num_pers => X_num_per,
549 X_dpis => X_dpis,
550 X_prorate_date => null,
551 X_prorate_conv => X_prorate_conv,
552 X_deprn_start_date => null,
553 X_ceiling_name => null,
554 X_bonus_rule => X_bonus_rule,
555 X_method_code => X_method,
556 X_cost => X_cost,
557 X_old_cost => X_cost,
558 X_adj_cost => null,
559 X_rec_cost => null,
560 X_raf => null,
561 X_adj_rate => X_adjusted_rate,
562 X_reval_amo_basis => null,
563 X_capacity => null,
564 X_adj_capacity => null,
565 X_life => X_life,
566 X_adj_rec_cost => null,
567 X_salvage_value => null,
568 X_salvage_pct => X_salvage_pct,
569 X_category_id => X_cat_id,
570 X_deprn_rnd_flag => null,
571 X_calendar_type => null,
572 X_prior_fy_exp => null,
573 X_deprn_rsv => X_deprn_rsv,
574 X_reval_rsv => null,
575 X_ytd_deprn => null,
576 X_ltd_prod => null,
577 x_return_status => x_return_status);
578
579 --fa_rx_conc_mesg_pkg.log('OUT');
580
581 if (ret = FALSE) then
582 x_return_status := 2;
583
584 fa_whatif_deprn_pkg.g_deprn.delete;
585 fnd_message.set_name('OFA','FA_WHATIF_ASSET_NORMAL_MODE');
586 h_mesg_str := fnd_message.get;
587 fa_rx_conc_mesg_pkg.log(h_mesg_str);
588 return (FALSE);
589 end if;
590
591 if (X_adjusted_rate is not null) then
592
593 select r.basic_rate
594 into h_basic_rate
595 from fa_methods m, fa_flat_rates r
596 where m.method_code = X_method
597 and m.method_id = r.method_id
598 and r.adjusted_rate = X_adjusted_rate and rownum < 2;
599
600 end if;
601
602 -- Enhancement bug 3037321
603 select currency_code
604 into h_currency
605 from gl_sets_of_books
606 where set_of_books_id = FARX_C_WD.sob_id;
607
608 fa_rx_shared_pkg.concat_category(h_cat_struct,
609 X_cat_id,
610 h_concat_str,
611 h_cat_segs);
612
613 -- seg_table,
614
615 h_sal := X_cost * (X_salvage_pct / 100);
616 h_count := 0;
617 loop -- for each period
618
619 if h_count >= X_num_per then exit; end if;
620
621 -- insert the Currency_code - for Enhancement bug 3037321
622 insert into fa_whatif_itf (
623 request_id, asset_id, asset_number, description, tag_number,
624 serial_number, period_name, fiscal_year, expense_acct,
625 depreciation, new_depreciation, location, units, employee_name,
626 employee_number, asset_key,
627 current_cost, current_prorate_conv, current_method, current_life,
628 current_basic_rate, current_adjusted_rate, current_salvage_value,
629 bonus_depreciation, new_bonus_depreciation,
630 created_by, creation_date, last_update_date,
631 last_updated_by, last_update_login,category,date_placed_in_service,
632 accumulated_deprn,currency_code
633 ) values (
634 X_request_id, NULL, NULL, NULL, NULL, NULL,
635 fa_whatif_deprn_pkg.g_deprn(h_count).period_name,
636 fa_whatif_deprn_pkg.g_deprn(h_count).fiscal_year, NULL,
637 fa_whatif_deprn_pkg.g_deprn(h_count).deprn,
638 fa_whatif_deprn_pkg.g_deprn(h_count).new_deprn,
639 NULL, NULL, NULL, NULL,
640 NULL, X_cost, X_prorate_conv, X_method,
641 X_life, h_basic_rate, X_adjusted_rate, h_sal,
642 fa_whatif_deprn_pkg.g_deprn(h_count).bonus_deprn,
643 fa_whatif_deprn_pkg.g_deprn(h_count).new_bonus_deprn,
644 X_user_id, sysdate, sysdate, X_user_id, h_login_id,
645 h_concat_str, X_dpis,
646 fa_whatif_deprn_pkg.g_deprn(h_count).new_rsv,h_currency);
647
648 h_count := h_count + 1;
649 end loop;
650
651 end if;
652
653 fa_whatif_deprn_pkg.g_deprn.delete;
654
655 -- errbuf := '';
656 return ret;
657
658 exception when others then
659
660 if SQLCODE <> 0 then
661 fa_Rx_conc_mesg_pkg.log(SQLERRM);
662 end if;
663
664 fa_whatif_deprn_pkg.g_deprn.delete;
665 fnd_message.set_name('OFA',h_mesg_name);
666 if h_mesg_name like 'FA_WHATIF_ASSET%' then
667 fnd_message.set_token('ASSET_ID',X_assets(h_count),FALSE);
668 end if;
669 h_mesg_str := fnd_message.get;
670 fa_rx_conc_mesg_pkg.log(h_mesg_str);
671
672 x_return_status := 2;
673 return FALSE;
674
675 end whatif_deprn;
676
677
678 function whatif_get_assets (
679 X_book in varchar2,
680 X_begin_asset in varchar2,
681 X_end_asset in varchar2,
682 X_begin_dpis in date,
683 X_end_dpis in date,
684 X_description in varchar2,
685 X_category_id in number,
686 X_mode in varchar2,
687 X_rsv_flag in varchar2,
688 X_good_assets out nocopy fa_std_types.number_tbl_type,
689 X_num_good out nocopy number,
690 X_start_range in number,
691 X_end_range in number,
692 x_return_status out nocopy number)
693 return boolean is
694
695
696 -- SELECTS ASSETS GIVEN PARAMETERS AS CRITERIA. IF A PARAM IS
697 -- NULL, ASSUME NO CRITERION.
698 --
699 -- FURTHER CRITERIA:
700 -- 1. NO 'PRODUCTION' ASSETS (CURRENTLY NOT SUPPORTED)
701 -- 2. MUST BE CAPITALIZED AND DEPRECIATING.
702 -- 3. CAN'T BE FULLY RETIRED OR HAVE RETIREMENT PENDING
703
704 h_mesg_name varchar2(30);
705 h_mesg_str varchar2(2000);
706
707 cursor assets is
708 select bk.asset_id, bk.deprn_method_code, ad.asset_number
709 from fa_books bk, fa_additions ad, fa_methods mt
710 where ad.asset_category_id = nvl(X_category_id,ad.asset_category_id)
711 and ad.description like nvl(X_description,ad.description)
712 and ad.asset_number >= nvl(X_begin_asset,ad.asset_number)
713 and ad.asset_number <= nvl(X_end_asset,ad.asset_number)
714 and ad.asset_type in ('CAPITALIZED', 'GROUP')
715 and bk.asset_id = ad.asset_id
716 and mt.method_code = bk.deprn_method_code
717 and nvl(mt.life_in_months,-99) = nvl(bk.life_in_months,-99)
718 and bk.asset_id >= nvl(X_start_range,bk.asset_id)
719 and bk.asset_id <= nvl(X_end_range,bk.asset_id)
720 and bk.book_type_code = X_book
721 and mt.rate_source_rule <> 'PRODUCTION'
722 and bk.depreciate_flag = 'YES'
723 and bk.transaction_header_id_out is null
724 and bk.period_counter_fully_retired is null
725 and bk.date_placed_in_service >=
726 nvl(X_begin_dpis,bk.date_placed_in_service)
727 and bk.date_placed_in_service <=
728 nvl(X_end_dpis,bk.date_placed_in_service)
729 and nvl(BK.Period_Counter_Fully_Reserved, -1)
730 = decode(X_rsv_flag, 'YES',
731 nvl(BK.Period_Counter_Fully_Reserved, -1),-1)
732 and bk.group_asset_id is null;
733
734 CURSOR c_check_amortized (c_asset_id number
735 , c_book_type_code varchar2) IS
736 select 1
737 from fa_transaction_headers
738 where asset_id = c_asset_id
739 and book_type_code = c_book_type_code
740 and transaction_subtype = 'AMORTIZED';
741
742
743
744 h_asset_id number;
745 h_asset_number varchar2(15);
746 h_method_code varchar2(25);
747 h_good_ctr number;
748 h_check number;
749 ret boolean;
750
751 l_amortized binary_integer;
752
753 begin
754
755 ret := TRUE;
756 h_good_ctr := 0;
757
758 h_mesg_name := 'FA_DEPRN_SQL_DCUR';
759 open assets;
760
761
762 loop
763
764 h_check := 0;
765
766 h_mesg_name := 'FA_DEPRN_SQL_FCUR';
767
768 fetch assets into h_asset_id, h_method_code, h_asset_number;
769 if (assets%NOTFOUND) then exit; end if;
770
771
772 -- SCREEN OUT ASSETS FOR WHICH AN AMORTIZED ADJUSTMENT HAS ALREADY
773 -- OCCURRED (FOR EXPENSED ONLY)
774
775
776 if (X_mode = 'EXPENSED' and h_check = 0) then
777
778 open c_check_amortized (h_asset_id,X_book);
779 fetch c_check_amortized into l_amortized;
780
781 if (c_check_amortized%FOUND) then
782 close c_check_amortized;
783 h_check := 1;
784 fnd_message.set_name('OFA','FA_WHATIF_ASSET_EXP_AMT');
785 fnd_message.set_token('ASSET_NUM',h_asset_number,FALSE);
786 h_mesg_str := fnd_message.get;
787 fa_rx_conc_mesg_pkg.log(h_mesg_str);
788 else
789 close c_check_amortized;
790 h_check := 0;
791 end if;
792
793 end if;
794
795 -- CAN'T AMORTIZE ADJUST AN ASSET THAT HASN'T DEPRECIATED YET.
796
797 if (X_mode = 'AMORTIZED' and h_check = 0) then
798
799 h_mesg_name := 'FA_WHATIF_ASSET_CHK_DEPRN';
800
801 select count(*) into h_check
802 from fa_deprn_detail
803 where asset_id = h_asset_id
804 and book_type_code = X_book
805 and deprn_source_code = 'B'
806 and not exists (select 1
807 from fa_deprn_detail dd1
808 where asset_id = h_asset_id
809 and book_type_code = X_book
810 and deprn_source_code = 'D'
811 and deprn_amount <> 0 ); -- bugfix 2223451 commented deprn_amount > 0;
812
813 if h_check > 0 then
814 fnd_message.set_name('OFA','FA_WHATIF_ASSET_NO_DEPRN_YET');
815 fnd_message.set_token('ASSET_NUM',h_asset_number,FALSE);
816 h_mesg_str := fnd_message.get;
817 fa_rx_conc_mesg_pkg.log(h_mesg_str);
818 end if;
819 end if;
820
821
822
823 -- SCREEN OUT ASSETS ADDED IN CURRENT PERIOD;
824 -- THESE WOULD JUST GET ADDITION,ADDITION/VOID TRX'S ANYWAYS.
825
826 -- select count(*) into h_check_cur_add
827 -- from fa_books bk, fa_deprn_periods dp, fa_deprn_periods bdp
828 -- where bdp.period_counter = dp.period_counter
829 -- and dp.period_close_date is not null
830 -- and bk.date_placed_in_service between
831 -- bdp.period_open_date and nvl(bdp.period_close_date, sysdate)
832 -- and bk.asset_id = X_asset_id;
833 --
834
835
836 if h_check = 0 then
837 X_good_assets(h_good_ctr) := h_asset_id;
838 h_good_ctr := h_good_ctr + 1;
839 end if;
840
841
842 end loop;
843
844 h_mesg_name := 'FA_DEPRN_SQL_CCUR';
845 close assets;
846
847 X_num_good := h_good_ctr;
848
849 -- errbuf := '';
850 return ret;
851
852
853 exception when others then
854
855 if SQLCODE <> 0 then
856 fa_Rx_conc_mesg_pkg.log(SQLERRM);
857 end if;
858 fnd_message.set_name('OFA',h_mesg_name);
859 if h_mesg_name like 'FA_WHATIF_ASSET%' then
860 fnd_message.set_token('ASSET_ID',h_asset_id,FALSE);
861 end if;
862 h_mesg_str := fnd_message.get;
863 fa_rx_conc_mesg_pkg.log(h_mesg_str);
864
865 x_return_status := 2;
866 return FALSE;
867
868
869 end whatif_get_assets;
870
871
872 END FA_WHATIF_DEPRN2_PKG;