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