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