[Home] [Help]
PACKAGE BODY: APPS.FA_MASS_DEPRN_BASIS_PKG
Source
1 PACKAGE BODY FA_MASS_DEPRN_BASIS_PKG as
2 /* $Header: faxmcdbb.pls 120.4.12010000.1 2008/07/28 13:26:59 appldev ship $ */
3
4 g_log_level_rec fa_api_types.log_level_rec_type;
5
6
7 PROCEDURE mass_faxccdb (
8 p_book_type_code IN VARCHAR2,
9 p_period_counter IN NUMBER,
10 p_run_date IN VARCHAR2,
11 p_mrc_sob_type_code IN NUMBER,
12 p_total_requests IN NUMBER,
13 p_request_number IN NUMBER,
14 x_return_status OUT NOCOPY NUMBER
15 ) IS
16
17 c_batch_size constant number := 1000;
18
19 l_rows_processed number;
20
21 -- type for table variable
22 type num_tbl_type is table of number index by binary_integer;
23 type char_tbl_type is table of varchar2(150) index by binary_integer;
24 type date_tbl_type is table of date index by binary_integer;
25 type rowid_tbl_type is table of rowid index by binary_integer;
26
27 -- used for bulk fetching
28 -- main cursor
29 l_bks_rowid_tbl rowid_tbl_type;
30 l_asset_id_tbl num_tbl_type;
31 l_group_asset_id_tbl num_tbl_type;
32 l_asset_type_tbl char_tbl_type;
33 l_depreciate_flag_tbl char_tbl_type;
34 l_method_code_tbl char_tbl_type;
35 l_life_in_months_tbl num_tbl_type;
36 l_method_id_tbl num_tbl_type;
37 l_method_type_tbl char_tbl_type;
38 l_calc_basis_tbl char_tbl_type;
39 l_cost_tbl num_tbl_type;
40 l_salvage_value_tbl num_tbl_type;
41 l_recoverable_cost_tbl num_tbl_type;
42 l_adjusted_cost_tbl num_tbl_type;
43 l_current_total_rsv_tbl num_tbl_type;
44 l_current_rsv_tbl num_tbl_type;
45 l_current_total_ytd_tbl num_tbl_type;
46 l_hyp_total_rsv_tbl num_tbl_type;
47 l_old_adjusted_cost_tbl num_tbl_type;
48 l_old_raf_tbl num_tbl_type;
49 l_old_formula_factor_tbl num_tbl_type;
50 l_new_adjusted_cost_tbl num_tbl_type;
51 l_new_raf_tbl num_tbl_type;
52 l_new_formula_factor_tbl num_tbl_type;
53 l_eofy_reserve_tbl num_tbl_type;
54 l_tracking_method_tbl char_tbl_type;
55 l_eofy_formula_factor_tbl num_tbl_type;
56 l_adjusted_capacity_tbl num_tbl_type;
57 l_new_adjusted_capacity_tbl num_tbl_type;
58 l_exclude_fully_rsv_flag_tbl char_tbl_type;
59 l_deprn_basis_rule_id_tbl num_tbl_type;
60 l_period_update_flag_tbl char_tbl_type;
61 l_use_eofy_reserve_flag char_tbl_type; -- Bug4940246
62 l_polish_rule_tbl num_tbl_type;
63
64 l_fiscal_year number;
65 l_eofy_flag varchar2(1);
66 l_mrc_sob_type_code varchar2(1);
67 l_period_num number;
68 l_run_date date;
69 l_status number;
70 mass_faxccdb_err exception;
71
72
73 l_asset_id number;
74
75 -- Bug 6665510: FP: Japan Tax Reform Project
76 l_guarantee_rate_flag_tbl char_tbl_type;
77 l_rate_in_use_tbl num_tbl_type;
78 l_method_type NUMBER := 0;
79 l_success INTEGER;
80
81 l_calling_fn varchar2(45) := 'fa_mass_deprn_basis_pkg.mass_faxccdb';
82
83 cursor c_assets is
84 select ds.asset_id,
85 bks.rowid,
86 bks.group_asset_id,
87 ad.asset_type,
88 bks.depreciate_flag,
89 bks.deprn_method_code,
90 bks.life_in_months,
91 mt.method_id,
92 mt.rate_source_rule,
93 mt.deprn_basis_rule,
94 bks.cost,
95 bks.salvage_value,
96 bks.recoverable_cost,
97 bks.adjusted_cost,
98 ds.deprn_reserve,
99 ds.deprn_reserve - ds.deprn_amount,
100 ds.ytd_deprn,
101 ds.deprn_reserve,
102 bks.adjusted_cost,
103 bks.rate_adjustment_factor,
104 bks.formula_factor,
105 bks.eofy_reserve,
106 bks.tracking_method,
107 bks.eofy_formula_factor,
108 bks.adjusted_capacity,
109 bks.production_capacity - ds.ltd_production,
110 bks.exclude_fully_rsv_flag,
111 nvl(mt.deprn_basis_rule_id, 0),
112 nvl(drd.period_update_flag, 'N'),
113 nvl(drd.use_eofy_reserve_flag, 'N'), -- Bug4940246
114 decode (drd.rule_name,
115 'POLISH 30% WITH A SWITCH TO DECLINING CLASSICAL AND FLAT RATE',
116 FA_STD_TYPES.FAD_DBR_POLISH_1,
117 'POLISH 30% WITH A SWITCH TO FLAT RATE',
118 FA_STD_TYPES.FAD_DBR_POLISH_2,
119 'POLISH DECLINING MODIFIED WITH A SWITCH TO DECLINING CLASSICAL AND FLAT RATE',
120 FA_STD_TYPES.FAD_DBR_POLISH_3,
121 'POLISH DECLINING MODIFIED WITH A SWITCH TO FLAT RATE',
122 FA_STD_TYPES.FAD_DBR_POLISH_4,
123 'POLISH STANDARD DECLINING WITH A SWITCH TO FLAT RATE',
124 FA_STD_TYPES.FAD_DBR_POLISH_5,
125 FA_STD_TYPES.FAD_DBR_POLISH_NONE)
126 , nvl(mt.guarantee_rate_method_flag, 'NO') -- Bug 6665510: FP: Japan Tax Reform Project
127 from fa_deprn_summary ds,
128 fa_books bks,
129 fa_methods mt,
130 fa_deprn_rule_details drd,
131 fa_additions_b ad
132 where ds.book_type_code = p_book_type_code
133 and ds.period_counter = p_period_counter
134 and MOD(ds.asset_id, p_total_requests) = (p_request_number - 1)
135 and ds.book_type_code = bks.book_type_code
136 and ds.asset_id = bks.asset_id
137 and bks.transaction_header_id_out is null
138 and bks.deprn_method_code = mt.method_code
139 and nvl(bks.life_in_months, -99) = nvl(mt.life_in_months, -99)
140 and mt.deprn_basis_rule_id = drd.deprn_basis_rule_id (+)
141 and mt.rate_source_rule = drd.rate_source_rule (+)
142 and mt.deprn_basis_rule = drd.deprn_basis_rule (+)
143 and ds.asset_id = ad.asset_id;
144 --and rownum < 1050;
145
146 cursor c_mc_assets is
147 select ds.asset_id,
148 bks.rowid,
149 bks.group_asset_id,
150 ad.asset_type,
151 bks.depreciate_flag,
152 bks.deprn_method_code,
153 bks.life_in_months,
154 mt.method_id,
155 mt.rate_source_rule,
156 mt.deprn_basis_rule,
157 bks.cost,
158 bks.salvage_value,
159 bks.recoverable_cost,
160 bks.adjusted_cost,
161 ds.deprn_reserve,
162 ds.deprn_reserve - ds.deprn_amount,
163 ds.ytd_deprn,
164 ds.deprn_reserve,
165 bks.adjusted_cost,
166 bks.rate_adjustment_factor,
167 bks.formula_factor,
168 bks.eofy_reserve,
169 bks.tracking_method,
170 bks.eofy_formula_factor,
171 bks.adjusted_capacity,
172 bks.production_capacity - ds.ltd_production,
173 bks.exclude_fully_rsv_flag,
174 nvl(mt.deprn_basis_rule_id, 0),
175 nvl(drd.period_update_flag, 'N'),
176 nvl(drd.use_eofy_reserve_flag, 'N'), -- Bug4940246
177 decode (drd.rule_name,
178 'POLISH 30% WITH A SWITCH TO DECLINING CLASSICAL AND FLAT RATE',
179 FA_STD_TYPES.FAD_DBR_POLISH_1,
180 'POLISH 30% WITH A SWITCH TO FLAT RATE',
181 FA_STD_TYPES.FAD_DBR_POLISH_2,
182 'POLISH DECLINING MODIFIED WITH A SWITCH TO DECLINING CLASSICAL AND FLAT RATE',
183 FA_STD_TYPES.FAD_DBR_POLISH_3,
184 'POLISH DECLINING MODIFIED WITH A SWITCH TO FLAT RATE',
185 FA_STD_TYPES.FAD_DBR_POLISH_4,
186 'POLISH STANDARD DECLINING WITH A SWITCH TO FLAT RATE',
187 FA_STD_TYPES.FAD_DBR_POLISH_5,
188 FA_STD_TYPES.FAD_DBR_POLISH_NONE)
189 , nvl(mt.guarantee_rate_method_flag, 'NO') -- Bug 6665510: FP: Japan Tax Reform Project
190 from fa_deprn_summary_mrc_v ds,
191 fa_books_mrc_v bks,
192 fa_methods mt,
193 fa_deprn_rule_details drd,
194 fa_additions_b ad
195 where ds.book_type_code = p_book_type_code
196 and ds.period_counter = p_period_counter
197 and MOD(ds.asset_id, p_total_requests) = (p_request_number - 1)
198 and ds.book_type_code = bks.book_type_code
199 and ds.asset_id = bks.asset_id
200 and bks.transaction_header_id_out is null
201 and bks.deprn_method_code = mt.method_code
202 and nvl(bks.life_in_months, -99) = nvl(mt.life_in_months, -99)
203 and mt.deprn_basis_rule_id = drd.deprn_basis_rule_id (+)
204 and mt.rate_source_rule = drd.rate_source_rule (+)
205 and mt.deprn_basis_rule = drd.deprn_basis_rule (+)
206 and ds.asset_id = ad.asset_id;
207
208 BEGIN
209
210 if (g_log_level_rec.statement_level) then
211 fa_debug_pkg.add(l_calling_fn,'BEGIN',1
212 ,p_log_level_rec => g_log_level_rec);
213 end if;
214
215 if p_mrc_sob_type_code = 0 then
216 l_mrc_sob_type_code := 'P';
217 elsif p_mrc_sob_type_code = 1 then
218 l_mrc_sob_type_code := 'R';
219 end if;
220
221 l_run_date := to_date(p_run_date, 'DD/MM/YYYY HH24:MI:SS');
222
223 if (g_log_level_rec.statement_level) then
224 fa_debug_pkg.add(l_calling_fn,'l_run_date',p_run_date
225 ,p_log_level_rec => g_log_level_rec);
226 end if;
227
228 if (l_mrc_sob_type_code = 'R') then
229
230 select dp.fiscal_year,
231 decode (dp.period_num,
232 ct.number_per_fiscal_year, 'Y',
233 'N') eofy_flag
234 into l_fiscal_year,
235 l_eofy_flag
236 from fa_book_controls_mrc_v bc,
237 fa_deprn_periods_mrc_v dp,
238 fa_calendar_types ct
239 where dp.book_type_code = p_book_type_code
240 and dp.period_counter = p_period_counter
241 and bc.book_type_code = p_book_type_code
242 and bc.deprn_calendar = ct.calendar_type;
243
244 else
245
246 select dp.fiscal_year,
247 decode (dp.period_num,
248 ct.number_per_fiscal_year, 'Y',
249 'N') eofy_flag,
250 dp.period_num
251 into l_fiscal_year,
252 l_eofy_flag,
253 l_period_num
254 from fa_book_controls bc,
255 fa_deprn_periods dp,
256 fa_calendar_types ct
257 where dp.book_type_code = p_book_type_code
258 and dp.period_counter = p_period_counter
259 and bc.book_type_code = p_book_type_code
260 and bc.deprn_calendar = ct.calendar_type;
261
262 end if;
263
264 if (g_log_level_rec.statement_level) then
265 fa_debug_pkg.add(l_calling_fn,'BEGIN',2
266 ,p_log_level_rec => g_log_level_rec);
267 end if;
268
269 if (l_mrc_sob_type_code = 'R') then
270 open c_mc_assets;
271 else
272 open c_assets;
273 end if;
274
275 loop
276
277 if (l_mrc_sob_type_code = 'R') then
278 fetch c_mc_assets bulk collect
279 into l_asset_id_tbl,
280 l_bks_rowid_tbl,
281 l_group_asset_id_tbl,
282 l_asset_type_tbl,
283 l_depreciate_flag_tbl,
284 l_method_code_tbl,
285 l_life_in_months_tbl,
286 l_method_id_tbl,
287 l_method_type_tbl,
288 l_calc_basis_tbl,
289 l_cost_tbl,
290 l_salvage_value_tbl,
291 l_recoverable_cost_tbl,
292 l_adjusted_cost_tbl,
293 l_current_total_rsv_tbl,
294 l_current_rsv_tbl,
295 l_current_total_ytd_tbl,
296 l_hyp_total_rsv_tbl,
297 l_old_adjusted_cost_tbl,
298 l_old_raf_tbl,
299 l_old_formula_factor_tbl,
300 l_eofy_reserve_tbl,
301 l_tracking_method_tbl,
302 l_eofy_formula_factor_tbl,
303 l_adjusted_capacity_tbl,
304 l_new_adjusted_capacity_tbl,
305 l_exclude_fully_rsv_flag_tbl,
306 l_deprn_basis_rule_id_tbl,
307 l_period_update_flag_tbl,
308 l_use_eofy_reserve_flag,-- Bug4940246
309 l_polish_rule_tbl,
310 l_guarantee_rate_flag_tbl -- Bug 6665510: FP: Japan Tax Reform Project
311 limit c_batch_size;
312
313 else
314 fetch c_assets bulk collect
315 into l_asset_id_tbl,
316 l_bks_rowid_tbl,
317 l_group_asset_id_tbl,
318 l_asset_type_tbl,
319 l_depreciate_flag_tbl,
320 l_method_code_tbl,
321 l_life_in_months_tbl,
322 l_method_id_tbl,
323 l_method_type_tbl,
324 l_calc_basis_tbl,
325 l_cost_tbl,
326 l_salvage_value_tbl,
327 l_recoverable_cost_tbl,
328 l_adjusted_cost_tbl,
329 l_current_total_rsv_tbl,
330 l_current_rsv_tbl,
331 l_current_total_ytd_tbl,
332 l_hyp_total_rsv_tbl,
333 l_old_adjusted_cost_tbl,
334 l_old_raf_tbl,
335 l_old_formula_factor_tbl,
336 l_eofy_reserve_tbl,
337 l_tracking_method_tbl,
338 l_eofy_formula_factor_tbl,
339 l_adjusted_capacity_tbl,
340 l_new_adjusted_capacity_tbl,
341 l_exclude_fully_rsv_flag_tbl,
342 l_deprn_basis_rule_id_tbl,
343 l_period_update_flag_tbl,
344 l_use_eofy_reserve_flag, -- Bug4940246
345 l_polish_rule_tbl,
346 l_guarantee_rate_flag_tbl -- Bug 6665510: FP: Japan Tax Reform Project
347 limit c_batch_size;
348
349 end if;
350
351 if (g_log_level_rec.statement_level) then
352 fa_debug_pkg.add(l_calling_fn,'BEGIN',3
353 ,p_log_level_rec => g_log_level_rec);
354 end if;
355
356 l_rows_processed := l_asset_id_tbl.count;
357 if l_rows_processed = 0 then
358 exit;
359 end if;
360 for i in 1..l_asset_id_tbl.count loop
361
362 if (g_log_level_rec.statement_level) then
363 fa_debug_pkg.add(l_calling_fn,'l_asset_id',l_asset_id_tbl(i), g_log_level_rec);
364 fa_debug_pkg.add(l_calling_fn,'l_eofy_flag', l_eofy_flag, g_log_level_rec);
365 end if;
366
367 l_asset_id := l_asset_id_tbl(i);
368
369 -- Bug4940246: Added following if statement
370 -- Do not call deprn basis rule if it is not eofy and period update is no or
371 -- it is eofy but period update and use eofy are no.
372 if (g_log_level_rec.statement_level) then
373 fa_debug_pkg.add(l_calling_fn,'l_period_update_flag_tbl',
374 l_period_update_flag_tbl(i), g_log_level_rec);
375 fa_debug_pkg.add(l_calling_fn,'l_use_eofy_reserve_flag',
376 l_use_eofy_reserve_flag(i), g_log_level_rec);
377 end if;
378
379 if ((l_eofy_flag = 'N') and
380 (l_period_update_flag_tbl(i) = 'N')) or
381 ((l_eofy_flag = 'Y') and
382 (l_use_eofy_reserve_flag(i) = 'N') and
383 (l_period_update_flag_tbl(i) = 'N')) then
384
385 l_new_adjusted_cost_tbl(i) := l_old_adjusted_cost_tbl(i);
386 l_new_raf_tbl(i) := l_old_raf_tbl(i);
387 l_new_formula_factor_tbl(i):= l_old_formula_factor_tbl(i);
388
389 if (g_log_level_rec.statement_level) then
390 fa_debug_pkg.add(l_calling_fn,'Not calling deprn basis function',
391 l_new_adjusted_cost_tbl(i), g_log_level_rec);
392 end if;
393
394 else
395
396 l_status := FA_BASIS_OVERRIDE_INIT_PKG.faxccdb(
397 event_type => 'AFTER_DEPRN',
398 asset_id => l_asset_id_tbl(i),
399 group_asset_id => l_group_asset_id_tbl(i),
400 book_type_code => p_book_type_code,
401 asset_type => l_asset_type_tbl(i),
402 depreciate_flag => l_depreciate_flag_tbl(i),
403 method_code => l_method_code_tbl(i),
404 life_in_months => l_life_in_months_tbl(i),
405 method_id => l_method_id_tbl(i),
406 method_type => l_method_type_tbl(i),
407 calc_basis => l_calc_basis_tbl(i),
408 adjustment_amount => 0,
409 transaction_flag => null,
410 cost => l_cost_tbl(i),
411 salvage_value => l_salvage_value_tbl(i),
412 recoverable_cost => l_recoverable_cost_tbl(i),
413 adjusted_cost => l_adjusted_cost_tbl(i),
414 current_total_rsv => l_current_total_rsv_tbl(i),
415 current_rsv => l_current_rsv_tbl(i),
416 current_total_ytd => l_current_total_ytd_tbl(i),
417 current_ytd => 0,
418 hyp_basis => 0,
419 hyp_total_rsv => l_hyp_total_rsv_tbl(i),
420 hyp_rsv => 0,
421 hyp_total_ytd => 0,
422 hyp_ytd => 0,
423 old_adjusted_cost => l_old_adjusted_cost_tbl(i),
424 old_raf => l_old_raf_tbl(i),
425 old_formula_factor => l_old_formula_factor_tbl(i),
426 new_adjusted_cost => l_new_adjusted_cost_tbl(i),
427 new_raf => l_new_raf_tbl(i),
428 new_formula_factor => l_new_formula_factor_tbl(i),
429 p_period_counter => l_period_num,
430 p_fiscal_year => l_fiscal_year,
431 p_eofy_reserve => l_eofy_reserve_tbl(i),
432 p_tracking_method => l_tracking_method_tbl(i),
433 p_eofy_flag => l_eofy_flag,
434 p_polish_rule => l_polish_rule_tbl(i),
435 p_mrc_sob_type_code => l_mrc_sob_type_code
436 );
437 if l_status <> 0 then
438 raise mass_faxccdb_err;
439 end if;
440 if (g_log_level_rec.statement_level) then
441 fa_debug_pkg.add(l_calling_fn,'after faxccdb',1
442 ,p_log_level_rec => g_log_level_rec);
443 fa_debug_pkg.add(l_calling_fn,'new adj_cost',
444 l_new_adjusted_cost_tbl(i));
445 end if;
446
447 end if; -- if ((l_eofy_flag = 'N') and -- Bug4940246
448
449 end loop;
450
451 -- Update fa_books with the new values. Business rules are:
452 -- 1. Do not update fa_books for any value if polish asset
453 -- 2. Only update eop_adj_cost, eop_formula_factor, adjusted_capacity,
454 -- old_adjusted_capacity if period_update_flag or
455 -- exclude_fully_reserve_flag is Yes
456 -- 3. Only update eofy_adj_cost and eofy_formula_factor if eofy_flag
457 -- is Yes
458 -- 4. Update eofy_adj_cost and eofy_formula_factor if the following:
459 -- a. eofy_flag is Yes and period_update_flag is No and
460 -- deprn_basis_rule is NBV and deprn_basis_rule_id exists
461 -- 5. Update eofy_reserve and prior_eofy_reserve if the following:
462 -- a. eofy_flag is Yes and period_update_flag is No and
463 -- deprn_basis_rule is NBV and deprn_basis_rule_id exists
464 -- b. period_update_flag is Yes or exclude_fully_reserve_flag is Yes
465 -- or deprn_basis_rule is COST or deprn_basis_rule_id doesn't exist
466 --
467
468 if (g_log_level_rec.statement_level) then
469 fa_debug_pkg.add(l_calling_fn,'BEGIN',5
470 ,p_log_level_rec => g_log_level_rec);
471 end if;
472
473 if (l_mrc_sob_type_code = 'R') then
474
475 if (l_eofy_flag = 'Y') then
476
477 forall i IN 1..l_asset_id_tbl.count
478 update fa_books_mrc_v
479 set adjusted_cost = decode (l_polish_rule_tbl(i),
480 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
481 nvl(l_new_adjusted_cost_tbl(i), 0),
482 l_old_adjusted_cost_tbl(i)),
483 formula_factor = decode (l_polish_rule_tbl(i),
484 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
485 nvl(l_new_formula_factor_tbl(i), 1),
486 l_old_formula_factor_tbl(i)),
487 eofy_adj_cost = decode (l_polish_rule_tbl(i),
488 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
489 l_old_adjusted_cost_tbl(i),
490 eofy_adj_cost),
491 eofy_formula_factor = decode (l_polish_rule_tbl(i),
492 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
493 l_new_formula_factor_tbl(i),
494 eofy_formula_factor),
495 eofy_reserve = decode (l_polish_rule_tbl(i),
496 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
497 nvl(l_current_total_rsv_tbl(i), 0),
498 eofy_reserve),
499 prior_eofy_reserve = decode (l_polish_rule_tbl(i),
500 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
501 nvl(l_eofy_reserve_tbl(i), 0),
502 prior_eofy_reserve),
503 eop_adj_cost = decode (l_polish_rule_tbl(i),
504 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
505 decode (l_period_update_flag_tbl(i),
506 'Y', nvl(l_old_adjusted_cost_tbl(i), 0),
507 decode (l_exclude_fully_rsv_flag_tbl(i),
508 'Y', nvl(l_old_adjusted_cost_tbl(i), 0),
509 eop_adj_cost)),
510 eop_adj_cost),
511 eop_formula_factor = decode (l_polish_rule_tbl(i),
512 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
513 decode (l_period_update_flag_tbl(i),
514 'Y', nvl(l_eofy_formula_factor_tbl(i), 1),
515 decode (l_exclude_fully_rsv_flag_tbl(i),
516 'Y', nvl(l_eofy_formula_factor_tbl(i), 1),
517 eop_formula_factor)),
518 eop_formula_factor),
519 adjusted_capacity = decode (l_polish_rule_tbl(i),
520 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
521 decode (l_period_update_flag_tbl(i),
522 'Y', nvl(l_new_adjusted_capacity_tbl(i), 0),
523 decode (l_exclude_fully_rsv_flag_tbl(i),
524 'Y', nvl(l_new_adjusted_capacity_tbl(i), 0),
525 adjusted_capacity)),
526 adjusted_capacity)
527 where rowid = l_bks_rowid_tbl(i);
528
529 else
530
531 forall i IN 1..l_asset_id_tbl.count
532 update fa_books_mrc_v
533 set adjusted_cost = decode (l_polish_rule_tbl(i),
534 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
535 nvl(l_new_adjusted_cost_tbl(i), 0),
536 l_old_adjusted_cost_tbl(i)),
537 formula_factor = decode (l_polish_rule_tbl(i),
538 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
539 nvl(l_new_formula_factor_tbl(i), 1),
540 l_old_formula_factor_tbl(i)),
541 prior_eofy_reserve = decode (l_polish_rule_tbl(i),
542 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
543 nvl(l_eofy_reserve_tbl(i), 0),
544 prior_eofy_reserve),
545 eop_adj_cost = decode (l_polish_rule_tbl(i),
546 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
547 decode (l_period_update_flag_tbl(i),
548 'Y', nvl(l_old_adjusted_cost_tbl(i), 0),
549 decode (l_exclude_fully_rsv_flag_tbl(i),
550 'Y', nvl(l_old_adjusted_cost_tbl(i), 0),
551 eop_adj_cost)),
552 eop_adj_cost),
553 eop_formula_factor = decode (l_polish_rule_tbl(i),
554 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
555 decode (l_period_update_flag_tbl(i),
556 'Y', nvl(l_eofy_formula_factor_tbl(i), 1),
557 decode (l_exclude_fully_rsv_flag_tbl(i),
558 'Y', nvl(l_eofy_formula_factor_tbl(i), 1),
559 eop_formula_factor)),
560 eop_formula_factor),
561 adjusted_capacity = decode (l_polish_rule_tbl(i),
562 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
563 decode (l_period_update_flag_tbl(i),
564 'Y', nvl(l_new_adjusted_capacity_tbl(i), 0),
565 decode (l_exclude_fully_rsv_flag_tbl(i),
566 'Y', nvl(l_new_adjusted_capacity_tbl(i), 0),
567 adjusted_capacity)),
568 adjusted_capacity)
569 where rowid = l_bks_rowid_tbl(i);
570
571 end if;
572 else
573
574 if (g_log_level_rec.statement_level) then
575 fa_debug_pkg.add(l_calling_fn,'BEGIN',6
576 ,p_log_level_rec => g_log_level_rec);
577 end if;
578 if (l_eofy_flag = 'Y') then
579
580 forall i IN 1..l_asset_id_tbl.count
581 update fa_books
582 set adjusted_cost = decode (l_polish_rule_tbl(i),
583 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
584 nvl(l_new_adjusted_cost_tbl(i), 0),
585 l_old_adjusted_cost_tbl(i)),
586 formula_factor = decode (l_polish_rule_tbl(i),
587 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
588 nvl(l_new_formula_factor_tbl(i), 1),
589 l_old_formula_factor_tbl(i)),
590 eofy_adj_cost = decode (l_polish_rule_tbl(i),
591 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
592 l_old_adjusted_cost_tbl(i),
593 eofy_adj_cost),
594 eofy_formula_factor = decode (l_polish_rule_tbl(i),
595 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
596 l_new_formula_factor_tbl(i),
597 eofy_formula_factor),
598 eofy_reserve = decode (l_polish_rule_tbl(i),
599 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
600 nvl(l_current_total_rsv_tbl(i), 0),
601 eofy_reserve),
602 prior_eofy_reserve = decode (l_polish_rule_tbl(i),
603 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
604 nvl(l_eofy_reserve_tbl(i), 0),
605 prior_eofy_reserve),
606 eop_adj_cost = decode (l_polish_rule_tbl(i),
607 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
608 decode (l_period_update_flag_tbl(i),
609 'Y', nvl(l_old_adjusted_cost_tbl(i), 0),
610 decode (l_exclude_fully_rsv_flag_tbl(i),
611 'Y', nvl(l_old_adjusted_cost_tbl(i), 0),
612 eop_adj_cost)),
613 eop_adj_cost),
614 eop_formula_factor = decode (l_polish_rule_tbl(i),
615 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
616 decode (l_period_update_flag_tbl(i),
617 'Y', nvl(l_eofy_formula_factor_tbl(i), 1),
618 decode (l_exclude_fully_rsv_flag_tbl(i),
619 'Y', nvl(l_eofy_formula_factor_tbl(i), 1),
620 eop_formula_factor)),
621 eop_formula_factor),
622 adjusted_capacity = decode (l_polish_rule_tbl(i),
623 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
624 decode (l_period_update_flag_tbl(i),
625 'Y', nvl(l_new_adjusted_capacity_tbl(i), 0),
626 decode (l_exclude_fully_rsv_flag_tbl(i),
627 'Y', nvl(l_new_adjusted_capacity_tbl(i), 0),
628 adjusted_capacity)),
629 adjusted_capacity)
630 where rowid = l_bks_rowid_tbl(i);
631
632 else
633
634 if (g_log_level_rec.statement_level) then
635 fa_debug_pkg.add(l_calling_fn,'BEGIN',7
636 ,p_log_level_rec => g_log_level_rec);
637 end if;
638
639 forall i IN 1..l_asset_id_tbl.count
640 update fa_books
641 set adjusted_cost = decode (l_polish_rule_tbl(i),
642 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
643 nvl(l_new_adjusted_cost_tbl(i), 0),
644 l_old_adjusted_cost_tbl(i)),
645 formula_factor = decode (l_polish_rule_tbl(i),
646 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
647 nvl(l_new_formula_factor_tbl(i), 1),
648 l_old_formula_factor_tbl(i)),
649 prior_eofy_reserve = decode (l_polish_rule_tbl(i),
650 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
651 nvl(l_eofy_reserve_tbl(i), 0),
652 prior_eofy_reserve),
653 eop_adj_cost = decode (l_polish_rule_tbl(i),
654 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
655 decode (l_period_update_flag_tbl(i),
656 'Y', nvl(l_old_adjusted_cost_tbl(i), 0),
657 decode (l_exclude_fully_rsv_flag_tbl(i),
658 'Y', nvl(l_old_adjusted_cost_tbl(i), 0),
659 eop_adj_cost)),
660 eop_adj_cost),
661 eop_formula_factor = decode (l_polish_rule_tbl(i),
662 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
663 decode (l_period_update_flag_tbl(i),
664 'Y', nvl(l_eofy_formula_factor_tbl(i), 1),
665 decode (l_exclude_fully_rsv_flag_tbl(i),
666 'Y', nvl(l_eofy_formula_factor_tbl(i), 1),
667 eop_formula_factor)),
668 eop_formula_factor),
669 adjusted_capacity = decode (l_polish_rule_tbl(i),
670 FA_STD_TYPES.FAD_DBR_POLISH_NONE,
671 decode (l_period_update_flag_tbl(i),
672 'Y', nvl(l_new_adjusted_capacity_tbl(i), 0),
673 decode (l_exclude_fully_rsv_flag_tbl(i),
674 'Y', nvl(l_new_adjusted_capacity_tbl(i), 0),
675 adjusted_capacity)),
676 adjusted_capacity)
677 where rowid = l_bks_rowid_tbl(i);
678
679 end if;
680
681 end if;
682
683 -- Bug 6665510: FP: Japan Tax Reform Project (Start)
684 if (l_mrc_sob_type_code <> 'R') then
685
686 if (g_log_level_rec.statement_level) then
687 fa_debug_pkg.add(l_calling_fn,'++++ eofy_flag', l_eofy_flag, p_log_level_rec => g_log_level_rec);
688 end if;
689
690 if (l_eofy_flag = 'Y') then
691
692 for i IN 1..l_asset_id_tbl.count loop
693
694 if (g_log_level_rec.statement_level) then
695 fa_debug_pkg.add(l_calling_fn,'++++ asset_id', l_asset_id_tbl(i), p_log_level_rec => g_log_level_rec);
696 fa_debug_pkg.add(l_calling_fn,'++++ guarantee_rate_method_flag', l_guarantee_rate_flag_tbl(i)
697 , p_log_level_rec => g_log_level_rec);
698 end if;
699
700 if nvl(l_guarantee_rate_flag_tbl(i),'NO') = 'YES' then
701
702 FA_CDE_PKG.faxgfr (X_Book_Type_Code => p_book_type_code,
703 X_Asset_Id => l_asset_id_tbl(i),
704 X_Short_Fiscal_Year_Flag => NULL,
705 X_Conversion_Date => NULL,
706 X_Prorate_Date => NULL,
707 X_Orig_Deprn_Start_Date => NULL,
708 C_Prorate_Date => NULL,
709 C_Conversion_Date => NULL,
710 C_Orig_Deprn_Start_Date => NULL,
711 X_Method_Code => l_method_code_tbl(i),
712 X_Life_In_Months => l_life_in_months_tbl(i),
713 X_Fiscal_Year => -99,
714 X_Current_Period => -99,
715 X_calling_interface => 'DEPRN_END',
716 X_Rate => l_rate_in_use_tbl(i),
717 X_Method_Type => l_method_type,
718 X_Success => l_success);
719
720 if (l_success <= 0) then
721 fa_srvr_msg.add_message(calling_fn => 'FA_MASS_DEPRN_BASIS_PKG.mass_faxccdb');
722 raise mass_faxccdb_err;
723 end if;
724 else
725 l_rate_in_use_tbl(i) := NULL;
726 end if; -- if l_guarantee_rate_flag_tbl
727 end loop;
728
729 forall i IN 1..l_bks_rowid_tbl.count
730 update fa_books
731 set rate_in_use = l_rate_in_use_tbl(i)
732 where rowid = l_bks_rowid_tbl(i)
733 and l_guarantee_rate_flag_tbl(i) = 'YES';
734
735 end if; -- if l_eofy_flag = 'Y'
736 end if; -- if l_mrc_sob_type <> 'R'
737 -- Bug 6665510: FP: Japan Tax Reform Project (End)
738
739 l_bks_rowid_tbl.delete;
740 l_asset_id_tbl.delete;
741 l_group_asset_id_tbl.delete;
742 l_asset_type_tbl.delete;
743 l_depreciate_flag_tbl.delete;
744 l_method_code_tbl.delete;
745 l_life_in_months_tbl.delete;
746 l_method_id_tbl.delete;
747 l_method_type_tbl.delete;
748 l_calc_basis_tbl.delete;
749 l_cost_tbl.delete;
750 l_salvage_value_tbl.delete;
751 l_recoverable_cost_tbl.delete;
752 l_adjusted_cost_tbl.delete;
753 l_current_total_rsv_tbl.delete;
754 l_current_rsv_tbl.delete;
755 l_current_total_ytd_tbl.delete;
756 l_hyp_total_rsv_tbl.delete;
757 l_old_adjusted_cost_tbl.delete;
758 l_old_raf_tbl.delete;
759 l_old_formula_factor_tbl.delete;
760 l_new_adjusted_cost_tbl.delete;
761 l_new_raf_tbl.delete;
762 l_new_formula_factor_tbl.delete;
763 l_eofy_reserve_tbl.delete;
764 l_tracking_method_tbl.delete;
765 l_eofy_formula_factor_tbl.delete;
766 l_adjusted_capacity_tbl.delete;
767 l_new_adjusted_capacity_tbl.delete;
768 l_exclude_fully_rsv_flag_tbl.delete;
769 l_deprn_basis_rule_id_tbl.delete;
770 l_period_update_flag_tbl.delete;
771 l_polish_rule_tbl.delete;
772
773 -- Bug 6665510: FP: Japan Tax Reform Project
774 l_rate_in_use_tbl.delete;
775 l_guarantee_rate_flag_tbl.delete;
776
777 commit;
778
779 if (l_rows_processed < c_batch_size) then exit; end if;
780
781 end loop;
782
783 if (l_mrc_sob_type_code = 'R') then
784 close c_mc_assets;
785 else
786 close c_assets;
787 end if;
788
789
790 x_return_status := 0;
791
792 if (g_log_level_rec.statement_level) then
793 fa_debug_pkg.add(l_calling_fn,'END',1
794 ,p_log_level_rec => g_log_level_rec);
795 end if;
796
797
798 EXCEPTION
799 WHEN mass_faxccdb_err then
800 rollback;
801 x_return_status := -1;
802 WHEN OTHERS THEN
803 fa_srvr_msg.add_sql_error(
804 calling_fn => 'fa_addition_pub.do_all_books'
805 ,p_log_level_rec => g_log_level_rec);
806
807 rollback;
808
809 x_return_status := -1;
810
811 END mass_faxccdb;
812
813 END FA_MASS_DEPRN_BASIS_PKG;
814