[Home] [Help]
PACKAGE BODY: APPS.FA_MASS_DPR_RSV_ADJ_PKG
Source
1 PACKAGE BODY FA_MASS_DPR_RSV_ADJ_PKG AS
2 /* $Header: FAMTRSVB.pls 120.9 2009/06/17 21:37:06 klakshmi ship $ */
3
4 g_log_level_rec fa_api_types.log_level_rec_type;
5
6 g_phase VARCHAR2(100);
7
8 G_success_count NUMBER := 0;
9 G_failure_count NUMBER := 0;
10
11 g_pers_per_yr NUMBER;
12 g_last_pc NUMBER;
13 g_current_pc NUMBER;
14 g_start_pc NUMBER;
15 g_end_pc NUMBER;
16
17 g_adj_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
18 g_ctl_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
19 g_corp_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
20 g_asset_tax_rsv_adj_rec FA_API_TYPES.asset_tax_rsv_adj_rec_type;
21 g_asset_tax_rsv_ctl_rec FA_API_TYPES.asset_tax_rsv_adj_rec_type;
22
23
24 g_fin_info fa_std_types.fin_info_struct;
25 g_dpr_row fa_std_types.fa_deprn_row_struct;
26
27 g_dpr_adj_factor Number;
28
29 g_string varchar2(250);
30 mass_dpr_rsv_adj_err exception;
31
32
33 -- ------------------------------------------------------------
34 -- Private Functions and Procedures
35 -- ------------------------------------------------------------
36
37 ------------------------------------------------------------------------
38 -- PROCEDURE PROCESS_ASSETS
39 --
40 -- NOTE:
41 --
42 ------------------------------------------------------------------------
43
44 /* Bug 4597471 -- Added one more parameter "p_mode" which shows whether called from
45 PREVIEW or RUN . Both have the same calculations but RUN mode updates the core tables
46 whereas PREVIEW only updates the interface table
47 */
48
49 PROCEDURE PROCESS_ASSETS(p_mass_tax_adjustment_id IN NUMBER,
50 p_parent_request_id IN NUMBER,
51 p_mode IN VARCHAR2,
52 p_start_range IN NUMBER,
53 p_end_range IN NUMBER) IS
54
55 l_trans_rec FA_API_TYPES.trans_rec_type;
56
57 l_pers_per_yr NUMBER;
58 l_last_pc NUMBER;
59 l_current_pc NUMBER;
60 l_start_pc NUMBER;
61 l_end_pc NUMBER;
62
63 l_primary_sob_id NUMBER;
64
65 l_deprn_rsv_corp_end NUMBER;
66
67 l_min_deprn_rsv NUMBER;
68 l_max_deprn_rsv NUMBER;
69
70 l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
71
72 l_adj_cost NUMBER;
73 l_ctl_cost NUMBER;
74 l_corp_cost NUMBER;
75 l_adj_rec_cost NUMBER;
76
77 mass_dpr_rsv_adj_err EXCEPTION;
78 dpr_row FA_STD_TYPES.fa_deprn_row_struct;
79
80 l_string VARCHAR2(500);
81 p_init_msg_list VARCHAR2(10); --vmarella check
82
83 p_commit VARCHAR2(10);
84 P_VALIDATION_LEVEL NUMBER;
85 x_return_status VARCHAR2(1);
86 x_msg_count NUMBER := 0;
87 x_mesg_len NUMBER;
88 x_msg_data VARCHAR2(4000);
89 l_calling_fn VARCHAR2(50) := 'fa_mass_dpr_rsv_adj_pkg.process_assets';
90 l_trx_date DATE := sysdate;
91 l_asset_id NUMBER;
92
93 l_dummy_bool BOOLEAN;
94 l_deprn_rsv_ctl_end NUMBER;
95 l_deprn_rsv_adj_end NUMBER;
96 l_ytd_rsv_adj_end NUMBER;
97 l_deprn_rsv_adj_begin NUMBER;
98 asset_ret EXCEPTION;
99 CURSOR C_ASSETS IS
100 SELECT DISTINCT AD.ASSET_ID/*,
101 AD.ASSET_NUMBER,
102 AD.ASSET_TYPE,
103 AD.ASSET_CATEGORY_ID,
104 AD.CURRENT_UNITS */ --vmarella <all I want is an asset id>.
105 FROM FA_DEPRN_SUMMARY DS,
106 FA_BOOKS BK,
107 FA_ADDITIONS AD,
108 FA_TRANSACTION_HEADERS TH
109 WHERE DS.BOOK_TYPE_CODE = g_adj_asset_hdr_rec.book_type_code
110 AND DS.ASSET_ID = AD.ASSET_ID
111 AND (DS.PERIOD_COUNTER BETWEEN g_start_pc AND g_end_pc)
112 AND BK.ASSET_ID = AD.ASSET_ID
113 AND BK.BOOK_TYPE_CODE = g_adj_asset_hdr_rec.book_type_code
114 AND BK.DATE_INEFFECTIVE IS NULL
115 AND TH.ASSET_ID (+) = AD.ASSET_ID
116 AND TH.BOOK_TYPE_CODE (+) = g_adj_asset_hdr_rec.book_type_code
117 AND TH.MASS_TRANSACTION_ID (+) = p_mass_tax_adjustment_id
118 AND TH.MASS_TRANSACTION_ID IS NULL
119 AND AD.ASSET_ID BETWEEN p_start_range AND p_end_range;
120
121 x_transaction_header_id NUMBER;
122
123 BEGIN
124
125 ------------------------------------------------
126 -- Get the Adj and Ctl book details
127 ------------------------------------------------
128
129 SELECT ADJUSTED_BOOK_TYPE_CODE,
130 CONTROL_BOOK_TYPE_CODE,
131 DEPRN_ADJUSTMENT_FACTOR,
132 FISCAL_YEAR
133 INTO g_adj_asset_hdr_rec.book_type_code,
134 g_ctl_asset_hdr_rec.book_type_code,
135 g_dpr_adj_factor,
136 g_asset_tax_rsv_adj_rec.fiscal_year
137 FROM FA_MASS_TAX_ADJUSTMENTS
138 WHERE MASS_TAX_ADJUSTMENT_ID = p_mass_tax_adjustment_id;
139
140
141 /* Get the Adjusted Book from Book Controls Cache */
142
143 if not fa_cache_pkg.fazcbc(X_book => g_adj_asset_hdr_rec.book_type_code) then
144 raise mass_dpr_rsv_adj_err;
145 end if;
146
147 /* Get the Depreciation Calendar from Book Controls Cache */
148
149 -- Get calendar period information from cache
150
151 if not fa_cache_pkg.fazcct(fa_cache_pkg.fazcbc_record.PRORATE_CALENDAR) then --vmarella check
152 raise mass_dpr_rsv_adj_err;
153 end if;
154
155 g_pers_per_yr := fa_cache_pkg.fazcct_record.NUMBER_PER_FISCAL_YEAR;
156 g_last_pc := fa_cache_pkg.fazcbc_record.last_period_counter;
157 g_current_pc := l_last_pc + 1;
158 g_start_pc := g_asset_tax_rsv_adj_rec.fiscal_year * g_pers_per_yr ;
159 g_end_pc := (g_asset_tax_rsv_adj_rec.fiscal_year * g_pers_per_yr) + g_pers_per_yr;
160
161 if not fa_cache_pkg.fazcbcs(g_adj_asset_hdr_rec.book_type_code,
162 fa_cache_pkg.fazcbc_record.set_of_books_id) then
163 raise mass_dpr_rsv_adj_err;
164 end if;
165
166 l_primary_sob_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
167
168 if (g_log_level_rec.statement_level) then
169 fa_debug_pkg.add(l_calling_fn,'opening c_assets cursor','');
170 fa_debug_pkg.add(l_calling_fn,'g_start_pc',g_start_pc);
171 fa_debug_pkg.add(l_calling_fn,'g_end_pc',g_end_pc);
172 end if;
173
174 Open c_assets;
175
176 loop
177
178 if (g_log_level_rec.statement_level) then
179 fa_debug_pkg.add(l_calling_fn,'in main loop ' , '');
180 end if;
181
182 fetch c_assets into l_asset_id;
183 Exit when c_assets%NOTFOUND;
184
185 BEGIN /* Inside Loop */
186
187 if (g_log_level_rec.statement_level) then
188 fa_debug_pkg.add(l_calling_fn,'in main block ' , '');
189 end if;
190
191 g_adj_asset_hdr_rec.asset_id := l_asset_id;
192
193 if not FA_UTIL_PVT.get_asset_desc_rec
194 (p_asset_hdr_rec => g_adj_asset_hdr_rec,
195 px_asset_desc_rec => l_asset_desc_rec,
196 p_log_level_rec => g_log_level_rec
197 ) then
198 raise mass_dpr_rsv_adj_err;
199 end if;
200
201 -- List of validations.
202
203 /*
204 * Check if the asset is fully retired or
205 * had been partially retired during the
206 * fiscal year being adjusted.
207 */
208
209 if (fa_asset_val_pvt.validate_fully_retired
210 (p_asset_id => g_adj_asset_hdr_rec.asset_id,
211 p_book => g_adj_asset_hdr_rec.book_type_code,
212 p_log_level_rec => g_log_level_rec))
213 then
214 raise mass_dpr_rsv_adj_err;
215 end if;
216
217 -- now check for partial ret in current year
218 /*
219 Bug 4597471 --
220 code modified to include the fully retired assets also
221 an exception -- asset_ret will be raised if the asset is either fully retired
222 or partially retired */
223
224 Declare /*editing code to handle the retired assets skchawla*/
225
226 l_is_retired number;
227
228 Begin
229 SELECT
230 DISTINCT 1
231 INTO
232 l_is_retired
233 FROM
234 FA_TRANSACTION_HEADERS TH
235 WHERE
236 TH.ASSET_ID = g_adj_asset_hdr_rec.asset_id AND
237 TH.BOOK_TYPE_CODE = g_adj_asset_hdr_rec.book_type_code AND
238 TH.TRANSACTION_TYPE_CODE = 'FULL RETIREMENT';
239 EXCEPTION
240 when no_data_found then
241 begin
242 select distinct 1
243 into l_is_retired
244 from FA_TRANSACTION_HEADERS TH,
245 FA_DEPRN_PERIODS DP1,
246 FA_DEPRN_PERIODS DP2
247 where TH.asset_id = g_adj_asset_hdr_rec.asset_id
248 and TH.book_type_code = g_adj_asset_hdr_rec.book_type_code
249 and TH.transaction_type_code = 'PARTIAL RETIREMENT'
250 and DP1.period_counter = g_start_pc and
251 DP1.book_type_code = g_adj_asset_hdr_rec.book_type_code and
252 DP1.period_open_date <= TH.date_effective
253 and DP2.period_counter = g_end_pc and
254 DP2.book_type_code = g_adj_asset_hdr_rec.book_type_code and
255 DP2.period_close_date >= TH.date_effective;
256 if(l_is_retired = 1)then
257 raise asset_ret;
258 end if;
259 EXCEPTION
260 when no_data_found then
261 null;
262 when others then
263 raise asset_ret;
264 end;
265 End;
266
267
268 /* Don't process UOP assets */
269 /* Obtain fa_methods.rate_source_rule from cache */
270
271 -- MVK : Find suitable validation.
272
273 /* Don't process an asset with a Polish deprn basis rule */
274 -- MVK : Handled in API as well. ( Need to check w/ brad )
275
276 g_fin_info := Null;
277
278 /* Get the Corporate Book from Book Controls Cache */
279
280 if not fa_cache_pkg.fazcbc(fa_cache_pkg.fazcbc_record.distribution_source_book) then
281 raise mass_dpr_rsv_adj_err;
282 end if;
283
284 /* Get Deprn Calendar for Corp Book from Book Controls Cache */
285
286 if not fa_cache_pkg.fazcct(fa_cache_pkg.fazcbc_record.deprn_calendar) then
287 raise mass_dpr_rsv_adj_err;
288 end if;
289
290 /* Get Number of Periods per Fiscal Year from Calendars Cache */
291
292 l_pers_per_yr := fa_cache_pkg.fazcct_record.NUMBER_PER_FISCAL_YEAR;
293 l_start_pc := g_asset_tax_rsv_adj_rec.fiscal_year * l_pers_per_yr;
294 l_end_pc := (g_asset_tax_rsv_adj_rec.fiscal_year * l_pers_per_yr ) + l_pers_per_yr;
295
296
297 /** Get End of Fiscal Year Depreciation for Corporate Book **/
298
299 dpr_row.asset_id := g_adj_asset_hdr_rec.asset_id;
300 dpr_row.book := fa_cache_pkg.fazcbc_record.book_type_code;
301 dpr_row.dist_id := 0;
302 dpr_row.period_ctr := l_end_pc;
303 dpr_row.adjusted_flag := FALSE;
304 dpr_row.deprn_exp := 0;
305 dpr_row.reval_deprn_exp := 0;
306 dpr_row.reval_amo := 0;
307 dpr_row.prod := 0;
308 dpr_row.ytd_deprn := 0;
309 dpr_row.ytd_reval_deprn_exp := 0;
310 dpr_row.ytd_prod := 0;
311 dpr_row.deprn_rsv := 0;
312 dpr_row.reval_rsv := 0;
313 dpr_row.ltd_prod := 0;
314 dpr_row.cost := 0;
315 dpr_row.add_cost_to_clear := 0;
316 dpr_row.adj_cost := 0;
317 dpr_row.reval_amo_basis := 0;
318 dpr_row.bonus_rate := 0;
319 dpr_row.deprn_source_code := NULL;
320 dpr_row.mrc_sob_type_code := 'P';
321
322 FA_QUERY_BALANCES_PKG.QUERY_BALANCES_INT (
323 dpr_row,
324 'ADJUSTED',
325 FALSE,
326 l_dummy_bool,
327 l_calling_fn,
328 -1,
329 g_log_level_rec);
330
331 l_deprn_rsv_corp_end := dpr_row.deprn_rsv;
332
333 /* Get Deprn Calendar for Control Book from Book Controls Cache */
334
335 if not fa_cache_pkg.fazcbc(g_ctl_asset_hdr_rec.book_type_code) then --vmarella checking
336 raise mass_dpr_rsv_adj_err;
337 end if;
338
339 /* Get Deprn Calendar for Control Book from Book Controls Cache */
340
341 if not fa_cache_pkg.fazcct(fa_cache_pkg.fazcbc_record.deprn_calendar) then
342 raise mass_dpr_rsv_adj_err;
343 end if;
344
345 /* Get Number of Periods per Fiscal Year from Calendars Cache */
346 /* spooyath -- till now g_asset_tax_rsv_ctl_rec.fiscal_year was used which was not populated
347 so l_end_pc was populated as null
348
349 i am using g_asset_tax_rsv_adj_rec.fiscal_year since fiscal year will be same for both
350 control books and adjusted book */
351
352 l_pers_per_yr := fa_cache_pkg.fazcct_record.NUMBER_PER_FISCAL_YEAR;
353 l_start_pc := g_asset_tax_rsv_adj_rec.fiscal_year * l_pers_per_yr;
354 l_end_pc := (g_asset_tax_rsv_adj_rec.fiscal_year * l_pers_per_yr ) + l_pers_per_yr;
355
356 /** Get End of Fiscal Year Depreciation for Control Book **/
357
358 dpr_row.asset_id := g_adj_asset_hdr_rec.asset_id;
359 dpr_row.book := fa_cache_pkg.fazcbc_record.book_type_code;
360 dpr_row.dist_id := 0;
361 dpr_row.period_ctr := l_end_pc;
362 dpr_row.adjusted_flag := FALSE;
363 dpr_row.deprn_exp := 0;
364 dpr_row.reval_deprn_exp := 0;
365 dpr_row.reval_amo := 0;
366 dpr_row.prod := 0;
367 dpr_row.ytd_deprn := 0;
368 dpr_row.ytd_reval_deprn_exp := 0;
369 dpr_row.ytd_prod := 0;
370 dpr_row.deprn_rsv := 0;
371 dpr_row.reval_rsv := 0;
372 dpr_row.ltd_prod := 0;
373 dpr_row.cost := 0;
374 dpr_row.add_cost_to_clear := 0;
375 dpr_row.adj_cost := 0;
376 dpr_row.reval_amo_basis := 0;
377 dpr_row.bonus_rate := 0;
378 dpr_row.deprn_source_code := NULL;
379
380 FA_QUERY_BALANCES_PKG.QUERY_BALANCES_INT (
381 dpr_row,
382 'ADJUSTED',
383 FALSE,
384 l_dummy_bool,
385 l_calling_fn,
386 -1,
387 g_log_level_rec);
388
389 l_deprn_rsv_ctl_end := dpr_row.deprn_rsv;
390
391 /* Reset the cache for the adj. book */
392 /* spooyath --- fa_cache_pkg.fazcbc_record.book_type_code was used for the adjusted book
393 till now with out initializing the cache resulting in the usage of control book as adjusted book */
394
395 if not fa_cache_pkg.fazcbc(X_book => g_adj_asset_hdr_rec.book_type_code) then
396 raise mass_dpr_rsv_adj_err;
397 end if;
398
399
400 /** Get End of Fiscal Year Depreciation for Adjusted Book **/
401
402 dpr_row.asset_id := g_adj_asset_hdr_rec.asset_id;
403 dpr_row.book := fa_cache_pkg.fazcbc_record.book_type_code;
404 dpr_row.dist_id := 0;
405 dpr_row.period_ctr := g_end_pc;
406 dpr_row.adjusted_flag := FALSE;
407 dpr_row.deprn_exp := 0;
408 dpr_row.reval_deprn_exp := 0;
409 dpr_row.reval_amo := 0;
410 dpr_row.prod := 0;
411 dpr_row.ytd_deprn := 0;
412 dpr_row.ytd_reval_deprn_exp := 0;
413 dpr_row.ytd_prod := 0;
414 dpr_row.deprn_rsv := 0;
415 dpr_row.reval_rsv := 0;
416 dpr_row.ltd_prod := 0;
417 dpr_row.cost := 0;
418 dpr_row.add_cost_to_clear := 0;
419 dpr_row.adj_cost := 0;
420 dpr_row.reval_amo_basis := 0;
421 dpr_row.bonus_rate := 0;
422 dpr_row.deprn_source_code := NULL;
423
424 FA_QUERY_BALANCES_PKG.QUERY_BALANCES_INT (
425 dpr_row,
426 'ADJUSTED',
427 FALSE,
428 l_dummy_bool,
429 l_calling_fn,
430 -1,
431 g_log_level_rec);
432
433 l_deprn_rsv_adj_end := dpr_row.deprn_rsv;
434 l_ytd_rsv_adj_end := dpr_row.ytd_deprn;
435
436
437 /** Get Start of Fiscal Year Depreciation for Adjusted Book **/
438
439 dpr_row.asset_id := g_adj_asset_hdr_rec.asset_id;
440 dpr_row.book := fa_cache_pkg.fazcbc_record.book_type_code;
441 dpr_row.dist_id := 0;
442 dpr_row.period_ctr := g_start_pc;
443 dpr_row.adjusted_flag := FALSE;
444 dpr_row.deprn_exp := 0;
445 dpr_row.reval_deprn_exp := 0;
446 dpr_row.reval_amo := 0;
447 dpr_row.prod := 0;
448 dpr_row.ytd_deprn := 0;
449 dpr_row.ytd_reval_deprn_exp := 0;
450 dpr_row.ytd_prod := 0;
451 dpr_row.deprn_rsv := 0;
452 dpr_row.reval_rsv := 0;
453 dpr_row.ltd_prod := 0;
454 dpr_row.cost := 0;
455 dpr_row.add_cost_to_clear := 0;
456 dpr_row.adj_cost := 0;
457 dpr_row.reval_amo_basis := 0;
458 dpr_row.bonus_rate := 0;
459 dpr_row.deprn_source_code := NULL;
460
461 FA_QUERY_BALANCES_PKG.QUERY_BALANCES_INT (
462 dpr_row,
463 'ADJUSTED',
464 FALSE,
465 l_dummy_bool,
466 l_calling_fn,
467 -1,
468 g_log_level_rec);
469
470 l_deprn_rsv_adj_begin := dpr_row.deprn_rsv;
471
472 /* Find cost for all three books, don't process asset if not same */
473
474 select bk1.cost, bk2.cost, bk3.cost, nvl(bk1.adjusted_recoverable_cost, bk1.recoverable_cost)
475 into l_adj_cost, l_ctl_cost, l_corp_cost, l_adj_rec_cost
476 from fa_books bk1,fa_books bk2, fa_books bk3
477 where bk1.book_type_code = g_adj_asset_hdr_rec.book_type_code and
478 bk1.asset_id = g_adj_asset_hdr_rec.asset_id and
479 bk1.date_ineffective is null
480 and bk2.book_type_code = g_ctl_asset_hdr_rec.book_type_code and
481 bk2.date_ineffective is null and
482 bk2.asset_id = bk1.asset_id
483 and bk3.book_type_code = fa_cache_pkg.fazcbc_record.distribution_source_book and
484 bk3.date_ineffective is null and
485 bk3.asset_id = bk1.asset_id;
486
487 If ((l_adj_cost <> l_ctl_cost ) OR
488 (l_corp_cost <> l_adj_cost )) then --vmarella
489
490 /* Bug 4597471 -- added the following messages */
491
492 fa_srvr_msg.add_message (
493 calling_fn => l_calling_fn,
494 name => 'FA_TAX_ASSET_WARN',
495 token1 => 'VARIABLE',
496 value1 => 'ASSET',
497 token2 => 'VALUE',
498 value2 => l_asset_desc_rec.asset_number,
499 translate => FALSE,
500 p_log_level_rec => g_log_level_rec
501 );
502
503 fa_srvr_msg.add_message (
504 calling_fn => l_calling_fn,
505 name => 'FA_TAX_DIFF_COST',
506 translate => FALSE,
507 p_log_level_rec => g_log_level_rec
508 );
509
510 /*
511 fnd_message.set_name('OFA', 'FA_TAX_ASSET_WARN' ||l_asset_desc_rec.asset_number );
512 l_string := fnd_message.get;
513
514 fnd_message.set_name('OFA', 'FA_TAX_DIFF_COST' ||l_asset_desc_rec.asset_number );
515 l_string := fnd_message.get;
516 */
517 raise mass_dpr_rsv_adj_err;
518
519 end if;
520
521 /** Find out minimum depreciation reserve **/
522
523 if nvl(l_adj_cost,0) > 0 then -- { cost > 0 }
524
525 if (l_deprn_rsv_adj_begin > l_deprn_rsv_corp_end )then --vmarella
526 if (l_deprn_rsv_adj_begin > l_deprn_rsv_ctl_end )then
527 l_min_deprn_rsv := l_deprn_rsv_adj_begin;
528 else
529 l_min_deprn_rsv := l_deprn_rsv_ctl_end;
530 end if;
531 else
532 if (l_deprn_rsv_corp_end > l_deprn_rsv_ctl_end ) then --vmarella
533 l_min_deprn_rsv := l_deprn_rsv_corp_end;
534 else
535 l_min_deprn_rsv := l_deprn_rsv_ctl_end;
536 end if;
537 end if;
538
539
540 else -- { cost < 0 }
541
542 if (l_deprn_rsv_adj_begin < l_deprn_rsv_corp_end ) then
543 if (l_deprn_rsv_adj_begin < l_deprn_rsv_ctl_end )then--vmarella
544 l_min_deprn_rsv := l_deprn_rsv_adj_begin;
545 else
546 l_min_deprn_rsv := l_deprn_rsv_ctl_end;
547 end if;
548 else
549 if (l_deprn_rsv_corp_end < l_deprn_rsv_ctl_end )then --vmarella
550 l_min_deprn_rsv := l_deprn_rsv_corp_end;
551 else
552 l_min_deprn_rsv := l_deprn_rsv_ctl_end;
553 end if;
554 end if;
555 end if;
556
557
558 /** Find out maximium depreciation reserve **/
559
560 /* spooyath -- modified the statements because the logic was wrong */
561
562 if nvl(l_adj_cost,0) > 0 then -- { cost > 0 }
563
564 if (l_deprn_rsv_adj_end > l_deprn_rsv_corp_end ) then--vmarella
565 if (l_deprn_rsv_adj_end > l_deprn_rsv_ctl_end )then
566 l_max_deprn_rsv := l_deprn_rsv_adj_end;
567 else
568 l_max_deprn_rsv := l_deprn_rsv_ctl_end;
569 end if;
570 else
571 if (l_deprn_rsv_corp_end > l_deprn_rsv_ctl_end ) then
572 l_max_deprn_rsv := l_deprn_rsv_corp_end;
573 else
574 l_max_deprn_rsv := l_deprn_rsv_ctl_end;
575 end if;
576 end if;
577
578
579 else -- { cost < 0 }
580
581 if (l_deprn_rsv_adj_end < l_deprn_rsv_corp_end ) then--vmarella
582 if (l_deprn_rsv_adj_end < l_deprn_rsv_ctl_end )then
583 l_max_deprn_rsv := l_deprn_rsv_adj_end;
584 else
585 l_max_deprn_rsv := l_deprn_rsv_ctl_end;
586 end if;
587 else
588 if (l_deprn_rsv_corp_end < l_deprn_rsv_ctl_end ) then
589 l_max_deprn_rsv := l_deprn_rsv_corp_end;
590 else
591 l_max_deprn_rsv := l_deprn_rsv_ctl_end;
592 end if;
593 end if;
594 end if;
595
596 /* Make sure min_deprn_rsv <= max_deprn_rsv */
597
598 if (nvl(l_adj_cost,0) > 0 and (l_min_deprn_rsv > l_max_deprn_rsv)) OR
599 (nvl(l_adj_cost,0) < 0 and (l_min_deprn_rsv < l_max_deprn_rsv)) then
600
601 fa_srvr_msg.add_message (
602 calling_fn => l_calling_fn,
603 name => 'FA_TAX_ASSET_WARN',
604 token1 => 'VARIABLE',
605 value1 => 'ASSET',
606 token2 => 'VALUE',
607 value2 => l_asset_desc_rec.asset_number,
608 translate => FALSE,
609 p_log_level_rec => g_log_level_rec
610 );
611
612 fa_srvr_msg.add_message (
613 calling_fn => l_calling_fn,
614 name => 'FA_TAX_MAX_LESS_MIN',
615 translate => FALSE,
616 p_log_level_rec => g_log_level_rec
617 );
618 raise mass_dpr_rsv_adj_err; -- vmarella check
619
620 end if;
621
622 /* Compute Adjusted Depreciation Reserve
623 Bug 4597471 -- added nvl for each value used for calculating the Deprn Reserve */
624
625 /* spooyath -- The formula used was wrong corrected the formula */
626 /* g_asset_tax_rsv_adj_rec.adjusted_ytd_deprn := (nvl(l_min_deprn_rsv,0) +(nvl(g_dpr_adj_factor,0) * (nvl(l_max_deprn_rsv,0) - nvl(l_min_deprn_rsv,0)))) -
627 nvl(l_deprn_rsv_adj_begin,0);*/
628
629 g_asset_tax_rsv_adj_rec.adjusted_ytd_deprn := nvl(l_min_deprn_rsv,0) +
630 ( nvl(g_dpr_adj_factor,0) *
631 (nvl(l_max_deprn_rsv,0) - nvl(l_min_deprn_rsv,0)));
632
633
634 --Bug7630553: fa_tax_rsv_adj_pub.do_tax_rsv_adj is expecting delta reserve between current one and user specified reserve
635 g_asset_tax_rsv_adj_rec.adjusted_ytd_deprn := (g_asset_tax_rsv_adj_rec.adjusted_ytd_deprn - l_deprn_rsv_adj_end);
636
637 if (g_log_level_rec.statement_level) then
638 fa_debug_pkg.add(l_calling_fn,'g_asset_tax_rsv_adj_rec.adjusted_ytd_deprn',
639 g_asset_tax_rsv_adj_rec.adjusted_ytd_deprn, g_log_level_rec);
640 end if;
641
642
643 /* Bug 4597471 -- Populate the run mode for passing it to the public api */
644
645 g_asset_tax_rsv_adj_rec.run_mode := p_mode ;
646
647 /* Call the public api, which process for each Primary and corresponding Reporting SOB's. */
648
649 -- ***** Transaction Header Info ***** --
650 l_trans_rec.mass_reference_id := p_parent_request_id;
651 l_trans_rec.mass_transaction_id := p_mass_tax_adjustment_id;
652 l_trans_rec.calling_interface := 'FATMTA';
653
654 p_init_msg_list := FND_API.G_TRUE;
655 fa_tax_rsv_adj_pub.do_tax_rsv_adj
656 (p_api_version => 1.0,
657 p_init_msg_list => FND_API.G_TRUE,
658 p_commit => FND_API.G_FALSE,
659 p_validation_level => p_validation_level,
660 p_calling_fn => l_calling_fn,
661 x_return_status => x_return_status,
662 x_msg_count => x_msg_count,
663 x_msg_data => x_msg_data,
664 px_trans_rec => l_trans_rec,
665 px_asset_hdr_rec => g_adj_asset_hdr_rec,
666 p_asset_tax_rsv_adj_rec => g_asset_tax_rsv_adj_rec
667 );
668
669 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
670 raise mass_dpr_rsv_adj_err;
671 else
672 -- commit each succes
673 fnd_concurrent.af_commit;
674 G_success_count := G_success_count + 1;
675 end if;
676
677 EXCEPTION
678
679 /* Bug 4597471 -- messages for partially / fully reserved assets */
680 when asset_ret then
681 fa_srvr_msg.add_message(
682 calling_fn => l_calling_fn,
683 name => 'FA_TAX_ASSET_WARN',
684 token1 => 'ASSET_NUMBER',
685 value1 => l_asset_desc_rec.asset_number,
686 p_log_level_rec => g_log_level_rec);
687
688 fa_srvr_msg.add_message(
689 calling_fn => l_calling_fn,
690 name => 'FA_TAX_FULLY_RET');
691 if (g_log_level_rec.statement_level) then
692 fa_debug_pkg.add(l_calling_fn,'asset_ret ','',g_log_level_rec);
693
694 -- Commented for bugfix 4672237
695 -- FA_DEBUG_PKG.dump_debug_messages(max_mesgs => 0);
696
697 end if;
698 when mass_dpr_rsv_adj_err then
699 FND_CONCURRENT.AF_ROLLBACK;
700 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
701 if (g_log_level_rec.statement_level) then
702 fa_debug_pkg.add(l_calling_fn,'mass_dpr_rsv_adj_err','',g_log_level_rec);
703
704 -- Commented for bugfix 4672237
705 -- FA_DEBUG_PKG.dump_debug_messages(max_mesgs => 0);
706
707 end if;
708 G_failure_count := G_failure_count + 1;
709
710
711 when others then
712 FA_SRVR_MSG.ADD_SQL_ERROR(
713 CALLING_FN => l_calling_fn,
714 p_log_level_rec => g_log_level_rec);
715 fnd_concurrent.af_rollback;
716 if (g_log_level_rec.statement_level) then
717 fa_debug_pkg.add(l_calling_fn,'when others','end of main block in loop',g_log_level_rec);
718
719 -- Commented for bugfix 4672237
720 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
721 end if;
722 G_failure_count := G_failure_count + 1;
723
724 END; /* Inside Loop */
725
726 end loop; -- c_assets;
727
728 EXCEPTION
729 when others then
730 FA_SRVR_MSG.ADD_SQL_ERROR(
731 CALLING_FN => l_calling_fn,
732 p_log_level_rec => g_log_level_rec);
733 fnd_concurrent.af_rollback;
734 if (g_log_level_rec.statement_level) then
735 fa_debug_pkg.add(l_calling_fn,'when others','end of process_assets', g_log_level_rec);
736
737 -- Commented for bugfix 4672237
738 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
739
740 end if;
741
742 END PROCESS_ASSETS;
743
744 -- ------------------------------------------------------------
745 -- Public Functions and Procedures
746 -- ------------------------------------------------------------
747
748 /* Bug 4597471 -- Added one more parameter "p_mode" which shows whether called from
749 PREVIEW or RUN . Both have the same calculations but RUN mode updates the core tables
750 whereas PREVIEW only updates the interface table
751 */
752
753 PROCEDURE Do_Deprn_Adjustment
754 (p_mass_tax_adjustment_id IN NUMBER,
755 p_mode IN VARCHAR2,
756 p_parent_request_id IN NUMBER,
757 p_total_requests IN NUMBER,
758 p_request_number IN NUMBER,
759 x_success_count OUT NOCOPY NUMBER,
760 x_failure_count OUT NOCOPY NUMBER,
761 x_worker_jobs OUT NOCOPY NUMBER,
762 x_return_status OUT NOCOPY NUMBER
763 ) IS
764
765
766 return_status BOOLEAN := TRUE;
767 p_number_of_rows NUMBER := 0;
768 p_no_worker NUMBER := 1;
769
770 l_retcode VARCHAR2(3);
771 l_errbuf VARCHAR2(500);
772 l_stmt VARCHAR2(300);
773 l_dir VARCHAR2(100);
774
775 -- Used for bulk fetching
776 l_counter number;
777 l_calling_fn varchar2(60):='fa_mass_dpr_rsv_adj_pkg.Do_Deprn_Adjustment';
778 -- used fort paralization - new method
779 l_unassigned_cnt NUMBER := 0;
780 l_failed_cnt NUMBER := 0;
781 l_wip_cnt NUMBER := 0;
782 l_completed_cnt NUMBER := 0;
783 l_total_cnt NUMBER := 0;
784 l_count NUMBER := 0;
785 l_start_range NUMBER := 0;
786 l_end_range NUMBER := 0;
787
788 l_ret_val BOOLEAN;
789 l_ret_code VARCHAR2(30);
790
791 done_exc exception;
792 error_found exception;
793
794 BEGIN
795
796 if (not g_log_level_rec.initialized) then
797 if (NOT fa_util_pub.get_log_level_rec (
798 x_log_level_rec => g_log_level_rec
799 )) then
800 raise error_found;
801 end if;
802 end if;
803
804 G_success_count := 0;
805 G_failure_count := 0;
806
807 ------------------------------------------------
808 -- Get the Adj and Ctl book details
809 ------------------------------------------------
810
811 SELECT ADJUSTED_BOOK_TYPE_CODE,
812 CONTROL_BOOK_TYPE_CODE,
813 DEPRN_ADJUSTMENT_FACTOR,
814 FISCAL_YEAR
815 INTO g_adj_asset_hdr_rec.book_type_code,
816 g_ctl_asset_hdr_rec.book_type_code,
817 g_dpr_adj_factor,
818 g_asset_tax_rsv_adj_rec.fiscal_year
819 FROM FA_MASS_TAX_ADJUSTMENTS
820 WHERE MASS_TAX_ADJUSTMENT_ID = p_mass_tax_adjustment_id;
821
822 -- ------------------------------------------
823 -- Loop thru job list
824 -- -----------------------------------------
825
826 g_phase := 'Loop thru job list';
827
828 SELECT NVL(sum(decode(status,'UNASSIGNED', 1, 0)),0),
829 NVL(sum(decode(status,'FAILED', 1, 0)),0),
830 NVL(sum(decode(status,'IN PROCESS', 1, 0)),0),
831 NVL(sum(decode(status,'COMPLETED',1 , 0)),0),
832 count(*)
833 INTO l_unassigned_cnt,
834 l_failed_cnt,
835 l_wip_cnt,
836 l_completed_cnt,
837 l_total_cnt
838 FROM FA_WORKER_JOBS
839 WHERE request_Id = p_parent_request_id;
840
841 if (g_log_level_rec.statement_level) then
842 fa_debug_pkg.add(l_calling_fn,'Job status - Unassigned: '||l_unassigned_cnt||
843 ' In Process: '||l_wip_cnt||
844 ' Completed: '||l_completed_cnt||
845 ' Failed: '||l_failed_cnt||
846 ' Total: ', l_total_cnt);
847 fa_debug_pkg.add(l_calling_fn,'p_parent_request_id',p_parent_request_id);
848 end if;
849
850
851 IF (l_failed_cnt > 0) THEN
852 if (g_log_level_rec.statement_level) then
853 fa_debug_pkg.add(l_calling_fn,'','');
854 fa_debug_pkg.add(l_calling_fn,'Another worker have errored out. Stop processing.','');
855 end if;
856 raise error_found; -- ??? this is dbi behavior - we shoudl probably continue
857 ELSIF (l_unassigned_cnt = 0) THEN
858 if (g_log_level_rec.statement_level) then
859 fa_debug_pkg.add(l_calling_fn,'','');
860 fa_debug_pkg.add(l_calling_fn,'No more jobs left. Terminating.','');
861 end if;
862 raise done_exc;
863 ELSIF (l_completed_cnt = l_total_cnt) THEN
864 if (g_log_level_rec.statement_level) then
865 fa_debug_pkg.add(l_calling_fn,'','');
866 fa_debug_pkg.add(l_calling_fn,'All jobs completed, no more job. Terminating','');
867 end if;
868 raise done_exc;
869 ELSIF (l_unassigned_cnt > 0) THEN
870 UPDATE FA_WORKER_JOBS
871 SET status = 'IN PROCESS',
872 worker_num = p_request_number
873 WHERE status = 'UNASSIGNED'
874 AND request_Id = p_parent_request_id
875 AND rownum < 2;
876
877 l_count := sql%rowcount;
878
879 if (g_log_level_rec.statement_level) then
880 fa_debug_pkg.add(l_calling_fn,'Taking job from job queue','');
881 fa_debug_pkg.add(l_calling_fn,'count: ' , l_count);
882 end if;
883
884 FND_CONCURRENT.AF_COMMIT;
885 END IF;
886
887 -- -----------------------------------
888 -- There could be rare situations where
889 -- between Section 30 and Section 50
890 -- the unassigned job gets taken by
891 -- another worker. So, if unassigned
892 -- job no longer exist. Do nothing.
893 -- -----------------------------------
894 IF (l_count > 0) THEN
895 DECLARE
896 BEGIN
897 g_phase := 'Getting ID range from FA_WORKER_JOBS table';
898
899 if (g_log_level_rec.statement_level) then
900 fa_debug_pkg.add(l_calling_fn,g_phase,'');
901 end if;
902
903 SELECT start_range,
904 end_range
905 INTO l_start_range,
906 l_end_range
907 FROM FA_WORKER_JOBS
908 WHERE worker_num = p_request_number
909 AND request_Id = p_parent_request_id
910 AND status = 'IN PROCESS';
911
912
913 --------------------------------------------------
914 -- Calc. deprn expense for the adjusted tax book
915 -- using the start_range and end_range.
916 --------------------------------------------------
917 g_phase := 'Calc. adj. deprn expense ';
918 if (g_log_level_rec.statement_level) then
919 fa_debug_pkg.add(l_calling_fn,g_phase,'');
920 end if;
921
922 PROCESS_ASSETS(p_mass_tax_adjustment_id,
923 p_parent_request_id,
924 p_mode,
925 l_start_range,
926 l_end_range);
927
928 -----------------------------------------------------
929 -- Do other work if necessary to finish the child
930 -- process
931 -- After completing the work, set the job status
932 -- to complete
933 -----------------------------------------------------
934 g_phase:='Updating job status in FA_WORKER_JOBS table';
935 if (g_log_level_rec.statement_level) then
936 fa_debug_pkg.add(l_calling_fn,g_phase,'');
937 end if;
938
939 UPDATE FA_WORKER_JOBS
940 SET status = 'COMPLETED'
941 WHERE request_id = p_parent_request_id
942 AND worker_num = p_request_number
943 AND status = 'IN PROCESS';
944
945 FND_CONCURRENT.AF_COMMIT;
946
947 -- Handle any exception that occured during
948 -- your child process
949
950 EXCEPTION
951 WHEN OTHERS THEN
952
953 FA_SRVR_MSG.ADD_SQL_ERROR(
954 CALLING_FN => l_calling_fn,
955 p_log_level_rec => g_log_level_rec);
956
957 UPDATE FA_WORKER_JOBS
958 SET status = 'FAILED'
959 WHERE request_id = p_parent_request_id
960 AND worker_num = p_request_number
961 AND status = 'IN PROCESS';
962
963 FND_CONCURRENT.AF_COMMIT;
964 Raise error_found;
965
966 END; -- block
967
968 END IF; /* IF (l_count> 0) */
969
970 -- using these as dummys - leave as zero when we've done nothing
971 x_success_count := G_success_count;
972 x_failure_count := G_failure_count;
973
974 -- Commented for bugfix 4672237
975 -- if (g_log_level_rec.statement_level) then
976 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
977 -- end if;
978
979 x_return_status := 0;
980
981 EXCEPTION
982 WHEN done_exc then
983 x_success_count := G_success_count;
984 x_failure_count := G_failure_count;
985
986 -- Commented for bugfix 4672237
987 -- if (g_log_level_rec.statement_level) then
988 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
989 -- end if;
990
991 x_return_status := 0;
992
993 WHEN error_found then
994 x_success_count := G_success_count;
995 x_failure_count := G_failure_count;
996 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
997 p_log_level_rec => g_log_level_rec);
998
999 -- Commented for bugfix 4672237
1000 -- if (g_log_level_rec.statement_level) then
1001 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
1002 -- end if;
1003 x_return_status := 2;
1004
1005 WHEN OTHERS THEN
1006 x_success_count := G_success_count;
1007 x_failure_count := G_failure_count;
1008 FA_SRVR_MSG.ADD_SQL_ERROR(
1009 CALLING_FN => l_calling_fn,
1010 p_log_level_rec => g_log_level_rec);
1011
1012 -- Commented for bugfix 4672237
1013 -- if (g_log_level_rec.statement_level) then
1014 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
1015 -- end if;
1016 x_return_status := 2;
1017
1018 END Do_Deprn_Adjustment;
1019
1020 -----------------------------------------------------------------
1021 -- PROCEDURE LOAD_WORKERS
1022 -----------------------------------------------------------------
1023 PROCEDURE LOAD_WORKERS
1024 (p_mass_tax_adjustment_id IN NUMBER,
1025 p_book_type_code IN VARCHAR2,
1026 p_parent_request_id IN NUMBER,
1027 p_total_requests IN NUMBER,
1028 x_worker_jobs OUT NOCOPY NUMBER,
1029 x_return_status OUT NOCOPY NUMBER) IS
1030
1031 l_max_number NUMBER;
1032 l_start_number NUMBER;
1033 l_end_number NUMBER;
1034 l_count NUMBER := 0;
1035
1036 l_batch_size number;
1037 l_book_type_code FA_BOOKS.book_type_code%type;
1038 l_calling_fn varchar2(60) := 'fa_mass_dpr_rsv_adj_pkg.load_jobs';
1039
1040 error_found exception;
1041 BEGIN
1042
1043 if (not g_log_level_rec.initialized) then
1044 if (NOT fa_util_pub.get_log_level_rec (
1045 x_log_level_rec => g_log_level_rec
1046 )) then
1047 raise error_found;
1048 end if;
1049 end if;
1050
1051 if (g_log_level_rec.statement_level) then
1052 fa_debug_pkg.add(l_calling_fn,'Calling procedure: LOAD_JOBS','');
1053 fa_debug_pkg.add(l_calling_fn,'','');
1054 end if;
1055
1056 if not (fa_cache_pkg.fazcbc(x_book => p_book_type_code
1057 ,p_log_level_rec => g_log_level_rec)) then
1058 raise error_found;
1059 end if;
1060
1061 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 1000);
1062
1063 g_phase := 'Register jobs for workers';
1064 if (g_log_level_rec.statement_level) then
1065 fa_debug_pkg.add(l_calling_fn,'Register jobs for workers','');
1066 fa_debug_pkg.add(l_calling_fn,'p_parent_request_id',p_parent_request_id);
1067 end if;
1068
1069 ------------------------------------------------------------
1070 -- select min and max sequence IDs from your ID Temp table
1071 ------------------------------------------------------------
1072 -- Get the Adj and Ctl book details
1073
1074 g_phase := 'select min and max asset ids';
1075
1076 /*
1077
1078 SELECT NVL(max(asset_id), 0),
1079 nvl(min(asset_id), 1)
1080 INTO l_max_number,
1081 l_start_number
1082 FROM FA_books
1083 WHERE book_type_code = p_book_type_code
1084 AND transaction_header_id_out is null;
1085
1086 WHILE (l_start_number <= l_max_number) LOOP
1087
1088 l_end_number:= l_start_number + l_batch_size;
1089 g_phase := 'Loop to insert into FA_WORKER_JOBS: '
1090 || l_start_number || ', ' || l_end_number;
1091
1092 l_count := l_count + 1;
1093 INSERT INTO FA_WORKER_JOBS (start_range, end_range, status, request_id)
1094 VALUES (l_start_number, least(l_end_number, l_max_number),'UNASSIGNED');
1095
1096
1097 l_start_number := least(l_end_number, l_max_number) + 1;
1098
1099 END LOOP; -- (l_start_number <= l_max_number)
1100
1101 */
1102
1103 INSERT INTO FA_WORKER_JOBS
1104 (START_RANGE, END_RANGE, WORKER_NUM, STATUS,REQUEST_ID)
1105 SELECT MIN(ASSET_ID), MAX(ASSET_ID), 0,
1106 'UNASSIGNED', p_parent_request_id
1107 FROM ( SELECT /*+ parallel(BK) */
1108 ASSET_ID, FLOOR(RANK()
1109 OVER (ORDER BY ASSET_ID)/l_batch_size ) UNIT_ID
1110 FROM FA_BOOKS BK
1111 WHERE BK.BOOK_TYPE_CODE = p_book_type_code
1112 AND BK.TRANSACTION_HEADER_ID_OUT IS NULL )
1113 GROUP BY UNIT_ID;
1114
1115 if (g_log_level_rec.statement_level) then
1116 fa_debug_pkg.add(l_calling_fn,'Inserted ' || SQL%ROWCOUNT || ' jobs into FA_WORKER_JOBS table','');
1117 end if;
1118
1119 fnd_concurrent.af_commit;
1120
1121 x_return_status := 0;
1122
1123 EXCEPTION
1124 WHEN error_found then
1125 fnd_concurrent.af_rollback;
1126
1127 -- Commented for bugfix 4672237
1128 -- if (g_log_level_rec.statement_level) then
1129 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
1130 -- end if;
1131 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
1132 p_log_level_rec => g_log_level_rec);
1133 x_return_status := 2;
1134
1135 WHEN OTHERS THEN
1136 FA_SRVR_MSG.ADD_SQL_ERROR(
1137 CALLING_FN => l_calling_fn,
1138 p_log_level_rec => g_log_level_rec);
1139 fnd_concurrent.af_rollback;
1140
1141 -- Commented for bugfix 4672237
1142 -- if (g_log_level_rec.statement_level) then
1143 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
1144 -- end if;
1145 x_return_status := 2;
1146
1147 END LOAD_WORKERS;
1148
1149 -----------------------------------------------------------------
1150
1151 END FA_MASS_DPR_RSV_ADJ_PKG;