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